CREATE or REPLACE PROCEDURE SAMPLE03
        (
         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_JIGYO1    NUMBER(2,0);
    WK_JIGYO2    NUMBER(2,0);
    WK_SELECT    VARCHAR2(1024);            -- select 文
    WK_INSERT    VARCHAR2(1024);            -- insert 文
    WK_DELETE    VARCHAR2(256);            -- delete 文

    事業部        NUMBER(2,0);
    部        NUMBER(1,0);
    課        NUMBER(1,0);
    取引先        NUMBER(5,0);
    取引先支店    NUMBER(2,0);
    受注        CHAR(8);

    SUM_INIT    BOOLEAN;
    SV事業部    NUMBER(2,0);
    SV部        NUMBER(1,0);
    SV課        NUMBER(1,0);
    SV取引先    NUMBER(5,0);
    SV取引先支店    NUMBER(2,0);
    SV受注        CHAR(8);

    工事名称    VARCHAR2(48);            -- 現場台帳より
    契約金額    NUMBER(10,0);            -- 現場台帳より

    単前回請求額    NUMBER(10,0);
    単入金金額    NUMBER(10,0);
    単今回請求額    NUMBER(10,0);
    請求日        DATE;

    前回請求額    NUMBER(10,0);
    入金金額    NUMBER(10,0);
    今回請求額    NUMBER(10,0);

    当月売上高    NUMBER(10,0);            -- 売上明細より
    消費税        NUMBER(10,0);            -- 売上明細より
    前受金額    NUMBER(10,0);            -- 中間請求より

/*=====================================================
   中間請求管理テーブル名取得
   IN   PM_DATE         : 締年月日
   OUT  PM_TBLNAME      : 目的テーブル名
=====================================================*/
FUNCTION GET_CHUKANK_NAME
        (
         PM_DATE        IN      DATE
        )
    RETURN VARCHAR2
        AS

        WK_DAY          VARCHAR2(30);
        WK_TBLNAME      VARCHAR2(30);
BEGIN
        WK_DAY := TO_CHAR( PM_DATE, 'DD' );
        WK_TBLNAME := 'KF_CYUKANK' || WK_DAY;
    RETURN WK_TBLNAME;
END GET_CHUKANK_NAME;

/*=====================================================
   長い文字列出力
=====================================================*/
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;

/*=====================================================
   文字列出力
=====================================================*/
PROCEDURE PUT_STR
        (
         PM_STR        IN      VARCHAR2
        )
        AS

BEGIN
    DBMS_OUTPUT.PUT_LINE(PM_STR);
END PUT_STR;

/*=====================================================
   事業部コード取得
   IN   PM_BLOCKSHO     : ﾌﾞﾛｯｸ小ｺｰﾄﾞ
   OUT  PM_JIGYO1       : 対応する事業部ｺｰﾄﾞ1
                          (NULLでエラー)        
   OUT  PM_JIGYO2       : 対応する事業部ｺｰﾄﾞ2
                          (該当無しでNULL)      
=====================================================*/
PROCEDURE GET_JCODE
        (
         PM_BLOCKSHO    IN      NUMBER
        ,PM_JIGYO1      IN OUT  NUMBER
        ,PM_JIGYO2      IN OUT  NUMBER
        )
        AS

        WK_JIGYO       NUMBER(2,0);

        CURSOR jigyo_cur is 
                select
                        CD_JIGYO
                from
                        MM_JIGYO
                where
                        CD_BLOCKSHO = PM_BLOCKSHO;

BEGIN

        PM_JIGYO1       := NULL;             
        PM_JIGYO2       := NULL;             
        WK_JIGYO        := NULL;
        OPEN    jigyo_cur;
        LOOP
                FETCH   jigyo_cur       INTO    WK_JIGYO;
                EXIT    WHEN    jigyo_cur%NOTFOUND;
                EXIT    WHEN    PM_JIGYO1 != WK_JIGYO;
                PM_JIGYO1       :=      WK_JIGYO;
        END LOOP;
        CLOSE   jigyo_cur;
        if WK_JIGYO is not NULL and PM_JIGYO1 != WK_JIGYO THEN
                PM_JIGYO2 := WK_JIGYO;
        end if;

EXCEPTION
        WHEN OTHERS THEN
                NULL;

END GET_JCODE;

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

    WK_DAY        NUMBER(2,0);
    WK_CMP_DAY    NUMBER(2,0);
    WK_KBN_JITA    NUMBER(1,0);
    WK_SIME        DATE;
    WK_SIME_NEW    DATE;            -- 最新請求締年月日
    WK_SIME_NEW2    DATE;            -- 最新請求締年月日
    WK_RET        BOOLEAN;
    WK_TORISUB    BOOLEAN;
    WK_J        NUMBER(2,0);

BEGIN

        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 or WK_KBN_JITA != 0 then  
dbms_output.put_line('取引マスタ無し or 自他区分が 0 で無い');
        return FALSE;
    end if;

        BEGIN                            -- １）取引サブを参照
        select
            CD_JIGYO,
            DAY_SIME_SEI,
            YMD_SEI_NEW
        into
            WK_J,
            WK_DAY,
            WK_SIME_NEW
        from
            MM_TORISUB
        where
            CD_TORI        = PM_TORI and
            CD_TORI_ST    = PM_TORI_ST;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN NULL;
        END;
        if SQL%FOUND then    -- 取引サブに存在した
        WK_SIME_NEW := nvl(WK_SIME_NEW,ADD_MONTHS(PM_DATE,-1));
        WK_TORISUB := TRUE;
        if    WK_JIGYO2 is NULL and
             WK_J = WK_JIGYO1
                or
            WK_JIGYO2 is not NULL and
            WK_J = WK_JIGYO1
                or
            WK_JIGYO2 is not NULL and
            WK_J = WK_JIGYO2        then
            WK_RET := TRUE;
        else
            WK_RET := 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 and WK_RET 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    -- 該当するデータが締め日変更ファイルには無い
                if PM_SDATE is NULL then
                    return FALSE;
                else
                    WK_DATE := WK_SIME_NEW + 1;
                    if WK_DATE <= PM_SDATE and PM_SDATE <= PM_DATE then
                        return TRUE;
                    else
                        dbms_output.put_line('取引SUB,日付範囲外');
                        return FALSE;
                    end if;
                end if;
            else
                if WK_SIME = PM_DATE then    -- 抽出除外条件
                    dbms_output.put_line('抽出除外条件');
                    return FALSE;
                else
                    if PM_SDATE is NULL then
                        WK_RET := FALSE;
                    else
                        WK_DATE := WK_SIME_NEW + 1;
                        if WK_DATE <= PM_SDATE and PM_SDATE <= PM_DATE then
                            WK_RET := TRUE;
                        else
                            dbms_output.put_line('取引SUB,日付範囲外');
                            WK_RET := FALSE;
                        end if;
                    end if;
                end if;
            end if;
        else
            dbms_output.put_line('取引SUB,締め日不一致');
            WK_RET := FALSE;
        end if;
    else        -- 取引サブに存在しなかった
        dbms_output.put_line('取引SUBに無い');
        WK_TORISUB := FALSE;
        WK_RET := FALSE;
    end if;

        BEGIN
        select
            YMD_SEI_NEW,
            YMD_HSIME_SEI
        into
            WK_SIME_NEW2,
            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%FOUND then
        WK_SIME_NEW2 := nvl(WK_SIME_NEW2,ADD_MONTHS(PM_DATE,-1));
        if WK_SIME = PM_DATE then
            if PM_SDATE is NULL then
                return FALSE;
            else
                WK_DATE := WK_SIME_NEW2 + 1;
                if WK_DATE <= PM_SDATE and PM_SDATE <= PM_DATE then    -- 範囲内
                    return TRUE;
                else
                    return WK_RET;
                end if;
            end if;
        else
            return WK_RET;
        end if;
    else
        return WK_RET;
    end if;

END CHECK_TORI;

/*=====================================================
  売上金額・消費税取得
=====================================================*/
PROCEDURE GET_KIN
        (
         PM_JUCHU    IN CHAR
        ,PM_KIN1    IN OUT  NUMBER
        ,PM_KIN2    IN OUT  NUMBER
        )
        AS

BEGIN
    if WK_JIGYO2 is NULL then
        BEGIN
            select nvl(sum(KIN_URIAGE),0),nvl(sum(ZEI_URIAGE),0) 
                into PM_KIN1,PM_KIN2 from PF_DAICHO,UF_URIDT,UF_URIAGE
                where
                     PF_DAICHO.NO_JUCHU = UF_URIDT.NO_JUCHU and
                    UF_URIDT.NO_URIAGE = UF_URIAGE.NO_URIAGE and
                    UF_URIDT.NO_HAKKO = UF_URIAGE.NO_HAKKO and
                    UF_URIDT.KBN_DENPYO = UF_URIAGE.KBN_DENPYO and
                    PF_DAICHO.YMD_URI_KJ between WK_DATE and PM_DATE and
                    UF_URIAGE.CD_JIGYO = WK_JIGYO1 and
                     PF_DAICHO.NO_JUCHU = PM_JUCHU;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN NULL;
            END;
            if SQL%NOTFOUND then  
            PM_KIN1 := 0;
            PM_KIN2 := 0;
        end if;
    else
        BEGIN
            select nvl(sum(KIN_URIAGE),0),nvl(sum(ZEI_URIAGE),0) 
                    into PM_KIN1,PM_KIN2 from PF_DAICHO,UF_URIDT,UF_URIAGE
                where
                     PF_DAICHO.NO_JUCHU = UF_URIDT.NO_JUCHU and
                    UF_URIDT.NO_URIAGE = UF_URIAGE.NO_URIAGE and
                    UF_URIDT.NO_HAKKO = UF_URIAGE.NO_HAKKO and
                    UF_URIDT.KBN_DENPYO = UF_URIAGE.KBN_DENPYO and
                    PF_DAICHO.YMD_URI_KJ between WK_DATE and PM_DATE and
                    UF_URIAGE.CD_JIGYO in (WK_JIGYO1,WK_JIGYO2) and
                     PF_DAICHO.NO_JUCHU = PM_JUCHU;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN NULL;
            END;
            if SQL%NOTFOUND then  
            PM_KIN1 := 0;
            PM_KIN2 := 0;
        end if;
    end if;    

END GET_KIN;

/*=====================================================
  関数名
=====================================================*/
FUNCTION SUM_CHUKAN
    RETURN BOOLEAN
    AS
BEGIN
    if SUM_INIT then
        事業部        := SV事業部;
        部        := SV部;
        課        := SV課;
        取引先        := SV取引先;
        取引先支店    := SV取引先支店;
        受注        := SV受注;
        SUM_INIT    := FALSE;
        if CHECK_TORI(取引先,取引先支店,請求日) then
            前回請求額    := 単前回請求額;
            入金金額    := 単入金金額;
            今回請求額    := 単今回請求額;
        end if;
        RETURN FALSE;
    else
        if    事業部        = SV事業部    and
            部        = SV部        and
            課        = SV課        and
            取引先        = SV取引先    and
            取引先支店    = SV取引先支店    and
            受注        = SV受注            then
            if CHECK_TORI(取引先,取引先支店,請求日) then
                前回請求額    := 前回請求額    + 単前回請求額;
                入金金額    := 入金金額    + 単入金金額;
                今回請求額    := 今回請求額    + 単今回請求額;
            end if;
            RETURN FALSE;
        else
            RETURN TRUE;
        end if;
    end if;
END SUM_CHUKAN;

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

/*=====================================================
   初期処理
=====================================================*/
    GET_JCODE( PM_BLOCKSHO, WK_JIGYO1, WK_JIGYO2 );
    SUM_INIT := TRUE;
    前回請求額    := 0;
    入金金額    := 0;
    今回請求額    := 0;
--    PUT_STR(TO_CHAR(WK_DATE,'YY/MM/DD') || '〜' || TO_CHAR(PM_DATE,'YY/MM/DD'));
--    PUT_STR(TO_CHAR(WK_JIGYO1) || ' and ' || TO_CHAR(WK_JIGYO2));

/*=====================================================
   SELECT文作成
=====================================================*/
    WK_SELECT    :=
    'select
        UF_JUCHU.CD_JIGYO,
        UF_JUCHU.CD_BUMON,
        UF_JUCHU.CD_KA,
        PF_CHUKAN.CD_TOKUI,
        PF_CHUKAN.CD_TOKUI_ST,
        PF_CHUKAN.NO_JUCHU,
        nvl(PF_CHUKAN.KIN_ZENSEI,0),
        nvl(PF_CHUKAN.KIN_NYUKIN,0),
        nvl(PF_CHUKAN.KIN_KONSEI,0),
        PF_CHUKAN.YMD_SEIKYU
    from
        PF_CHUKAN,UF_JUCHU
    where
        PF_CHUKAN.NO_JUCHU = UF_JUCHU.NO_JUCHU';

    if WK_JIGYO2 is NULL then
        if WK_JIGYO1 is not NULL then
            WK_SELECT := CONCAT( WK_SELECT, ' and UF_JUCHU.CD_JIGYO = :WK_JIGYO1 ' );
        end if;
    else
        WK_SELECT := CONCAT( WK_SELECT, ' and UF_JUCHU.CD_JIGYO IN (:WK_JIGYO1,:WK_JIGYO2) ' );
    end if;    
    WK_SELECT := CONCAT( WK_SELECT, ' and PF_CHUKAN.YMD_SEIKYU <= :PM_DATE ' );
    WK_SELECT := CONCAT( WK_SELECT, 
        ' order by CD_JIGYO,CD_BUMON,CD_KA,PF_CHUKAN.CD_TOKUI,PF_CHUKAN.CD_TOKUI_ST,PF_CHUKAN.NO_JUCHU ' );

--    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,    SV事業部 );
    dbms_sql.define_column( source_cursor,    2,    SV部 );
    dbms_sql.define_column( source_cursor,    3,    SV課 );
    dbms_sql.define_column( source_cursor,    4,    SV取引先 );
    dbms_sql.define_column( source_cursor,    5,    SV取引先支店 );
    dbms_sql.define_column( source_cursor,    6,    SV受注, 8 );
    dbms_sql.define_column( source_cursor,    7,    単前回請求額 );
    dbms_sql.define_column( source_cursor,    8,    単入金金額 );
    dbms_sql.define_column( source_cursor,    9,    単今回請求額 );
    dbms_sql.define_column( source_cursor,    10,    請求日 );

    if WK_JIGYO2 is NULL then
        if WK_JIGYO1 is not NULL then
            dbms_sql.bind_variable( source_cursor, 'WK_JIGYO1', WK_JIGYO1);
        end if;
    else
        dbms_sql.bind_variable( source_cursor, 'WK_JIGYO1', WK_JIGYO1);
        dbms_sql.bind_variable( source_cursor, 'WK_JIGYO2', WK_JIGYO2);
    end if;    
    dbms_sql.bind_variable( source_cursor, 'PM_DATE', PM_DATE);

    rows_processed        :=    dbms_sql.execute( source_cursor );

/*=====================================================
   出力定義
=====================================================*/
    destination_cursor    :=    dbms_sql.open_cursor;
    WK_INSERT    :=
    'insert into ' || GET_CHUKANK_NAME(PM_DATE) || ' (
        CD_BLOCKSHO,
        CD_JIGYO,
        CD_BUMON,
        CD_KA,
        CD_TORI,
        CD_TORI_ST,
        NO_JUCHU,
        NM_KOJI,
        KIN_KEIYAKU,
        KIN_ZENSEI,
        KIN_NYUKIN,
        KIN_KONSEI,
        KIN_URIAGE,
        KIN_SYOUHI,
        KIN_MAEUKE
    ) values(
        :CD_BLOCKSHO,
        :CD_JIGYO,
        :CD_BUMON,
        :CD_KA,
        :CD_TORI,
        :CD_TORI_ST,
        :NO_JUCHU,
        :NM_KOJI,
        :KIN_KEIYAKU,
        :KIN_ZENSEI,
        :KIN_NYUKIN,
        :KIN_KONSEI,
        :KIN_URIAGE,
        :KIN_SYOUHI,
        :KIN_MAEUKE
    )';

--    PUT_LSTR(WK_INSERT);
    dbms_sql.parse( destination_cursor, WK_INSERT, 2 );

    delete_cursor    :=    dbms_sql.open_cursor;
    WK_DELETE    := 'delete from ' || GET_CHUKANK_NAME(PM_DATE);
    dbms_sql.parse( delete_cursor, WK_DELETE, 2 );
    rows_processed    :=    dbms_sql.execute( delete_cursor );
    dbms_sql.close_cursor( delete_cursor );

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

    loop
        if dbms_sql.fetch_rows( source_cursor ) > 0 then

            dbms_sql.column_value( source_cursor,    1,    SV事業部 );
            dbms_sql.column_value( source_cursor,    2,    SV部 );
            dbms_sql.column_value( source_cursor,    3,    SV課 );
            dbms_sql.column_value( source_cursor,    4,    SV取引先 );
            dbms_sql.column_value( source_cursor,    5,    SV取引先支店 );
            dbms_sql.column_value( source_cursor,    6,    SV受注 );
            dbms_sql.column_value( source_cursor,    7,    単前回請求額 );
            dbms_sql.column_value( source_cursor,    8,    単入金金額 );
            dbms_sql.column_value( source_cursor,    9,    単今回請求額 );
            dbms_sql.column_value( source_cursor,    10,    請求日 );
dbms_output.put_line('READ');
dbms_output.put_line(単前回請求額);
dbms_output.put_line(単入金金額);
dbms_output.put_line(単今回請求額);

            if SUM_CHUKAN then
                dbms_sql.bind_variable( destination_cursor, 'CD_BLOCKSHO', PM_BLOCKSHO);
                dbms_sql.bind_variable( destination_cursor, 'CD_JIGYO', 事業部);
                dbms_sql.bind_variable( destination_cursor, 'CD_BUMON', 部);
                dbms_sql.bind_variable( destination_cursor, 'CD_KA', 課);
                dbms_sql.bind_variable( destination_cursor, 'CD_TORI', 取引先);
                dbms_sql.bind_variable( destination_cursor, 'CD_TORI_ST', 取引先支店);
                dbms_sql.bind_variable( destination_cursor, 'NO_JUCHU', 受注);

                    BEGIN
                    select NM_KOJI,KIN_KEIYAKU 
                        into 工事名称,契約金額 from PF_DAICHO where NO_JUCHU = 受注;
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN NULL;
                    END;
                    if SQL%NOTFOUND then  
                    工事名称 := NULL;
                    契約金額 := 0;
                end if;
                    BEGIN
                    select nvl(sum(KIN_NYUKIN),0) 
                        into 前受金額 from PF_CHUKAN,PF_DAICHO 
                        where 
                            PF_CHUKAN.NO_JUCHU = PF_DAICHO.NO_JUCHU and 
                            PF_CHUKAN.NO_JUCHU = 受注 and 
                            YMD_URI_KJ between WK_DATE and PM_DATE;
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN NULL;
                    END;
                    if SQL%NOTFOUND then  
                    前受金額 := 0;
                end if;

                dbms_sql.bind_variable( destination_cursor, 'NM_KOJI', 工事名称);
                dbms_sql.bind_variable( destination_cursor, 'KIN_KEIYAKU', 契約金額);
                dbms_sql.bind_variable( destination_cursor, 'KIN_ZENSEI', 前回請求額);
                dbms_sql.bind_variable( destination_cursor, 'KIN_NYUKIN', 入金金額);
                dbms_sql.bind_variable( destination_cursor, 'KIN_KONSEI', 今回請求額);

                GET_KIN(受注,当月売上高,消費税);
                dbms_sql.bind_variable( destination_cursor, 'KIN_URIAGE', 当月売上高);    -- 関数より
                dbms_sql.bind_variable( destination_cursor, 'KIN_SYOUHI', 消費税);    -- 関数より
                dbms_sql.bind_variable( destination_cursor, 'KIN_MAEUKE', 前受金額);
                rows_processed        :=    dbms_sql.execute(destination_cursor);

dbms_output.put_line('INSERT');
dbms_output.put_line(前回請求額);
dbms_output.put_line(入金金額);
dbms_output.put_line(今回請求額);
                前回請求額    := 0;
                入金金額    := 0;
                今回請求額    := 0;
                if CHECK_TORI(SV取引先,SV取引先支店,請求日) then
dbms_output.put_line('CHECKOK');
                    前回請求額    := 単前回請求額;
                    入金金額    := 単入金金額;
                    今回請求額    := 単今回請求額;
                end if;
                事業部        := SV事業部;
                部        := SV部;
                課        := SV課;
                取引先        := SV取引先;
                取引先支店    := SV取引先支店;
                受注        := SV受注;
            end if;
        else
            if SUM_INIT = FALSE then
                dbms_sql.bind_variable( destination_cursor, 'CD_BLOCKSHO', PM_BLOCKSHO);
                dbms_sql.bind_variable( destination_cursor, 'CD_JIGYO', 事業部);
                dbms_sql.bind_variable( destination_cursor, 'CD_BUMON', 部);
                dbms_sql.bind_variable( destination_cursor, 'CD_KA', 課);
                dbms_sql.bind_variable( destination_cursor, 'CD_TORI', 取引先);
                dbms_sql.bind_variable( destination_cursor, 'CD_TORI_ST', 取引先支店);
                dbms_sql.bind_variable( destination_cursor, 'NO_JUCHU', 受注);

                    BEGIN
                    select NM_KOJI,KIN_KEIYAKU into 工事名称,契約金額 
                        from PF_DAICHO where NO_JUCHU = 受注;
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN NULL;
                    END;
                    if SQL%NOTFOUND then  
                    工事名称 := NULL;
                    契約金額 := 0;
                end if;
                    BEGIN
                    select nvl(sum(KIN_NYUKIN),0) 
                        into 前受金額 from PF_CHUKAN,PF_DAICHO 
                        where 
                            PF_CHUKAN.NO_JUCHU = PF_DAICHO.NO_JUCHU and 
                            PF_CHUKAN.NO_JUCHU = 受注 and 
                            YMD_URI_KJ between WK_DATE and PM_DATE;
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN NULL;
                    END;
                    if SQL%NOTFOUND then  
                    前受金額 := 0;
                end if;

                dbms_sql.bind_variable( destination_cursor, 'NM_KOJI', 工事名称);
                dbms_sql.bind_variable( destination_cursor, 'KIN_KEIYAKU', 契約金額);
                dbms_sql.bind_variable( destination_cursor, 'KIN_ZENSEI', 前回請求額);
                dbms_sql.bind_variable( destination_cursor, 'KIN_NYUKIN', 入金金額);
                dbms_sql.bind_variable( destination_cursor, 'KIN_KONSEI', 今回請求額);

                GET_KIN(受注,当月売上高,消費税);
                dbms_sql.bind_variable( destination_cursor, 'KIN_URIAGE', 当月売上高);    -- 関数より
                dbms_sql.bind_variable( destination_cursor, 'KIN_SYOUHI', 消費税);    -- 関数より
                dbms_sql.bind_variable( destination_cursor, 'KIN_MAEUKE', 前受金額);
                rows_processed        :=    dbms_sql.execute(destination_cursor);
            end if;
            exit;
        end if;
    end loop;

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

END SAMPLE03;
/