CREATE or REPLACE PROCEDURE SAMPLE02
        (
         PM_BLOCKSHO    IN      NUMBER
        ,PM_DATE    IN      DATE
        )
        AS

/*===========================================================================
   変数
===========================================================================*/
    source_cursor        integer;
    destination_cursor    integer;
    delete_cursor        integer;
    rows_processed        integer;

    WK_DATE        DATE;
    WK_SELECT    VARCHAR2(1024);            -- select 文

    取引先        NUMBER(5,0);
    取引先支店    NUMBER(2,0);
    請求締め日    NUMBER(2,0);
    回収サイクル    NUMBER(1,0);
    回収日        NUMBER(2,0);
    回収予定日    DATE;
    事業部        NUMBER(2,0);
    部        NUMBER(1,0);
    課        NUMBER(1,0);
    手形区分    NUMBER(3,0);
    S手形区分    VARCHAR2(8);
    サイト        NUMBER(3,0);
    回収方法    NUMBER(1,0);
    S回収方法    VARCHAR2(4);
    当月請求額    NUMBER(10,0);
    当月売上額    NUMBER(10,0);
    中間請求額    NUMBER(10,0);
    回収予定額    NUMBER(10,0);
    買掛残高    NUMBER(10,0);

    R振込        NUMBER(3,0);
    振込        NUMBER(10,0);
    R現金        NUMBER(3,0);
    現金        NUMBER(10,0);
    R手形        NUMBER(3,0);
    手形        NUMBER(10,0);
    R期日現金    NUMBER(3,0);
    期日現金    NUMBER(10,0);
    R小切手        NUMBER(3,0);
    小切手        NUMBER(10,0);
    Rその他        NUMBER(3,0);
    その他        NUMBER(10,0);

    S事業部        NUMBER(2,0);
    S部        NUMBER(1,0);
    S課        NUMBER(1,0);
    担当者        NUMBER(4,0);

/*===========================================================================
   ローカル関数
===========================================================================*/
/*=======================================================
   最新請求締年月日取得
   IN           PM_DATE         : 締年月日
   RETURN       WK_DATE         : 最新請求締年月日
=====================================================*/
FUNCTION GET_DATE_SIME (PM_DATE IN DATE) RETURN DATE
        AS
        WK_DATE         DATE;
BEGIN
        WK_DATE         := ADD_MONTHS(PM_DATE,-1);
        WK_DATE         := WK_DATE + 1;
        RETURN          WK_DATE;
END GET_DATE_SIME;

/*
=======================================================
   長い文字列出力
=====================================================*/
-- CREATE or REPLACE
PROCEDURE PUT_LSTR
        (
         PM_STR        IN      VARCHAR2
        )
        AS

BEGIN

    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,1,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,81,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,161,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,241,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,321,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,401,80));
    DBMS_OUTPUT.PUT_LINE(substr(PM_STR,481,80));

END PUT_LSTR;

/*=====================================================
　対象取引先チェック
=====================================================*/
FUNCTION CHECK_TORI
        (
         PM_TORI    IN    NUMBER
        ,PM_TORI_ST    IN    NUMBER
        )
    RETURN BOOLEAN
        AS

    WK_DAY        NUMBER(2,0);
    WK_CMP_DAY    NUMBER(2,0);
    WK_KBN_JITA    NUMBER(1,0);
    WK_SIME        DATE;
BEGIN
        BEGIN
        select DAY_SIME_SEI into WK_DAY from MM_TORISUB
            where CD_TORI = PM_TORI and CD_TORI_ST = PM_TORI_ST and FLG_TOKUI = 1;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN NULL;
        END;
        if SQL%NOTFOUND then  
        return FALSE;
    end if;

    if LAST_DAY(PM_DATE) = PM_DATE then
        WK_CMP_DAY := 99;
    else
        WK_CMP_DAY := TO_NUMBER(TO_CHAR(PM_DATE,'DD'));
    end if;

    if WK_DAY = WK_CMP_DAY then

            BEGIN
            select KBN_JITA into WK_KBN_JITA from MM_TORI 
                where CD_TORI = PM_TORI and CD_TORI_ST = PM_TORI_ST;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN NULL;
            END;
            if SQL%NOTFOUND then  
            return FALSE;
        end if;

        if WK_KBN_JITA = 0 then
                    BEGIN
                select YMD_SIME_SEI into WK_SIME from KF_HSIME_SEI where 
                    CD_BLOCKSHO = PM_BLOCKSHO and 
                    CD_TORI = PM_TORI and 
                    CD_TORI_ST = PM_TORI_ST;
                    EXCEPTION
                            WHEN NO_DATA_FOUND THEN NULL;
                    END;
                    if SQL%NOTFOUND then  
                return TRUE;
            end if;
            if WK_SIME != PM_DATE then
                return TRUE;
            end if;
        end if;
    end if;

        BEGIN
        select KBN_JITA into WK_KBN_JITA from MM_TORI where CD_TORI = PM_TORI and CD_TORI_ST = PM_TORI_ST;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN NULL;
        END;
        if SQL%NOTFOUND then  
        return FALSE;
    end if;

    if WK_KBN_JITA = 0 then
            BEGIN
            select YMD_HSIME_SEI into WK_SIME from KF_HSIME_SEI where 
                CD_BLOCKSHO = PM_BLOCKSHO and 
                CD_TORI = PM_TORI and 
                CD_TORI_ST = PM_TORI_ST;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN NULL;
        END;
        if SQL%NOTFOUND then  
            return FALSE;
        end if;
        if WK_SIME = PM_DATE then
            return TRUE;
        end if;
    end if;
    return FALSE;

END CHECK_TORI;

/*===========================================================================
   主処理
===========================================================================*/
BEGIN

/*=====================================================
   初期処理
=====================================================*/
    WK_DATE        :=    GET_DATE_SIME( PM_DATE );    -- from DATE 取得
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(WK_DATE,'YY/MM/DD') || '〜' || TO_CHAR(PM_DATE,'YY/MM/DD'));

/*=====================================================
   SELECT文作成
=====================================================*/
    WK_SELECT    :=
    'select
        CD_TORI,
        CD_TORI_ST,
        CD_JIGYO,
        CD_BUMON,
        CD_KA,
        nvl(sum(KIN_TOSEI),0),
        nvl(sum(KIN_URIAGE),0),
        nvl(sum(KIN_TYUKAN),0)
    from KF_SEI
    where
        CD_BLOCKSHO = :PM_BLOCKSHO and
        YMD_SIME_SEI = :PM_DATE and
        YMD_FROM_SEI <= YMD_SIME_SEI
    group by
        CD_JIGYO,
        CD_BUMON,
        CD_KA,
        CD_TORI,
        CD_TORI_ST';
/*
    'select
        CD_TORI,
        CD_TORI_ST,
        CD_JIGYO,
        CD_BUMON,
        CD_KA,
        nvl(sum(KIN_TOSEI),0),
        nvl(sum(KIN_URIAGE),0),
        nvl(sum(KIN_TYUKAN),0)
    from KF_SEI
    where
        CD_BLOCKSHO = :PM_BLOCKSHO and
        YMD_SIME_SEI between :WK_DATE and :PM_DATE
    group by
        CD_JIGYO,
        CD_BUMON,
        CD_KA,
        CD_TORI,
        CD_TORI_ST';
*/

--    PUT_LSTR( WK_SELECT );        

/*=====================================================
   入力定義
=====================================================*/
    source_cursor        :=    dbms_sql.open_cursor;
    dbms_sql.parse( source_cursor, WK_SELECT, 2 );

    dbms_sql.define_column( source_cursor,    1,    取引先 );
    dbms_sql.define_column( source_cursor,    2,    取引先支店 );
    dbms_sql.define_column( source_cursor,    3,    事業部 );
    dbms_sql.define_column( source_cursor,    4,    部 );
    dbms_sql.define_column( source_cursor,    5,    課 );
    dbms_sql.define_column( source_cursor,    6,    当月請求額 );
    dbms_sql.define_column( source_cursor,    7,    当月売上額 );
    dbms_sql.define_column( source_cursor,    8,    中間請求額 );

    dbms_sql.bind_variable( source_cursor, 'PM_BLOCKSHO', PM_BLOCKSHO );
--    dbms_sql.bind_variable( source_cursor, 'WK_DATE', WK_DATE );
    dbms_sql.bind_variable( source_cursor, 'PM_DATE', PM_DATE );

    rows_processed        :=    dbms_sql.execute( source_cursor );

/*=====================================================
   入出力
=====================================================*/

    loop
        if dbms_sql.fetch_rows( source_cursor ) > 0 then

            dbms_sql.column_value( source_cursor,    1,    取引先 );
            dbms_sql.column_value( source_cursor,    2,    取引先支店 );
            dbms_sql.column_value( source_cursor,    3,    事業部 );
            dbms_sql.column_value( source_cursor,    4,    部 );
            dbms_sql.column_value( source_cursor,    5,    課 );
            dbms_sql.column_value( source_cursor,    6,    当月請求額 );
            dbms_sql.column_value( source_cursor,    7,    当月売上額 );
            dbms_sql.column_value( source_cursor,    8,    中間請求額 );

            DBMS_OUTPUT.PUT_LINE(TO_CHAR(取引先) || '-' || TO_CHAR(取引先支店));

            if CHECK_TORI(取引先,取引先支店) then
--                DBMS_OUTPUT.PUT_LINE('取引先チェックＯＫ');
                BEGIN
                    select    DAY_SIME_SEI,CYCLE_KAI,DAY_KAI,
                        nvl(RT_FURI_KAI,0),
                        nvl(RT_GENKIN_KAI,0),
                        nvl(RT_TEGATA_KAI,0),
                        nvl(RT_KIJITU_KAI,0),
                        nvl(RT_KOGITTE_KAI,0),
                        nvl(RT_SONOTA_KAI,0),
                        KBN_TEGATA,
                        SAITO_KA,
                        HOUHOU_KAI,
                        CD_JIGYO,
                        CD_BUMON,
                        CD_KA
                    into    請求締め日,回収サイクル,回収日,
                        R振込,
                        R現金,
                        R手形,
                        R期日現金,
                        R小切手,
                        Rその他,
                        手形区分,
                        サイト,
                        回収方法,
                        S事業部,
                        S部,
                        S課
                    from    MM_TORISUB
                    where    CD_TORI = 取引先 and CD_TORI_ST = 取引先支店;
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN NULL;
                    END;
                    if SQL%FOUND then  
                    if 回収サイクル = 1 or 回収サイクル = 2 and 回収日 <= 請求締め日 then
                        回収予定額 := 当月請求額;
                    else
                        回収予定額 := 当月請求額 - 当月売上額 - 中間請求額;
                    end if;

                    振込        := 回収予定額 * R振込        / 100;
                    現金        := 回収予定額 * R現金        / 100;
                    手形        := 回収予定額 * R手形        / 100;
                    期日現金    := 回収予定額 * R期日現金    / 100;
                    小切手        := 回収予定額 * R小切手        / 100;
                    その他        := 回収予定額 * Rその他        / 100;
                    
                    delete from KF_KAI_YOTEI where 
                        CD_TORI = 取引先 and
                        CD_TORI_ST = 取引先支店;
                    
                    BEGIN
                        select    sum(nvl(KIN_MIBARAI,0))
                            +sum(nvl(KIN_SHOHIN,0))
                            +sum(nvl(KIN_GAICHU,0))
                        into    買掛残高
                        from    DF_SIZAN
                        where 
                            CD_TORI = 取引先 and
                            CD_TORI_ST = 取引先支店 and
                            TO_NUMBER(TO_CHAR(YMD_SIHARAI,'DD')) = 請求締め日;
                        EXCEPTION
                            WHEN NO_DATA_FOUND THEN NULL;
                        END;
                        if SQL%NOTFOUND then
                        買掛残高 := 0;
                    end if; 

                    回収予定日 := TO_DATE(TO_CHAR(PM_DATE,'YY') ||
                         '/' || TO_CHAR(PM_DATE,'MM') ||
                         '/' || TO_CHAR(回収日,'99'));
                    if 回収日 <= 請求締め日 then
                        回収予定日 := ADD_MONTHS(回収予定日, 1) ;
                    end if;

                    S手形区分 := NULL;
                    S回収方法 := NULL;

                    if R手形 != 0 then
                        if 手形区分 = 1 then
                            S手形区分 := '自己手形';
                        end if;
                        if 手形区分 = 2 then
                            S手形区分 := '廻り手形';
                        end if;
                    else
                        サイト := 0;                        
                    end if;
                    if 回収方法 = 1 then
                        S回収方法 := '振込';
                    end if;
                    if 回収方法 = 2 then
                        S回収方法 := '郵送';
                    end if;
                    if 回収方法 = 3 then
                        S回収方法 := '集金';
                    end if;

                    BEGIN
                        select CD_TANTO into 担当者 from MM_EITAN where 
                            CD_TORI = 取引先 and 
                            CD_TORI_ST = 取引先支店 and 
                            CD_JIGYO = S事業部 and 
                            CD_BUMON = S部 and 
                            CD_KA = S課;
                        EXCEPTION
                            WHEN NO_DATA_FOUND THEN NULL;
                    END;
                        if SQL%NOTFOUND then  
                        担当者 := 0;
                    end if;

                    insert into KF_KAI_YOTEI
                    (
                    CD_BLOCKSHO,
                    CD_JIGYO,
                    CD_BUMON,
                    CD_KA,
                    CD_TORI,
                    CD_TORI_ST,
                    CD_TANTO,
                    YMD_KAI,
                    KIN_TOSEI,
                    KIN_KAIYO,
                    KIN_Y_FURI,
                    KIN_Y_GENKIN,
                    KIN_Y_TEGATA,
                    KIN_Y_KIJITU,
                    KIN_Y_KOGITE,
                    KIN_Y_SOSAI,
                    KIN_Y_SONOTA,
                    NM_SAI,
                    NM_TEGATA,
                    NM_HOU,
                    KIN_MIBARAI
--                    YMD_UPD
                    ) 
                    values
                    (
                    PM_BLOCKSHO,
                    事業部,
                    部,
                    課,
                    取引先,
                    取引先支店,
                    担当者,
                    回収予定日,
                    当月請求額,
                    回収予定額,
                    振込,
                    現金,
                    手形,
                    期日現金,
                    小切手,
                    0,
                    その他,
                    サイト,
                    S手形区分,
                    S回収方法,
                    買掛残高
--                    sysdate
                    );
                end if;
            end if;
        else
            exit;
        end if;

    end loop;

/*=====================================================
   終了処理
=====================================================*/
    commit;
    dbms_sql.close_cursor( source_cursor );


END SAMPLE02;
/