CREATE or REPLACE PROCEDURE SAMPLE01
(
PM_BLOCKSHO IN NUMBER
,PM_DATE IN DATE
)
AS
/*===========================================================================
変数
===========================================================================*/
source_cursor integer;
destination_cursor integer;
rows_processed integer;
WK_DATE DATE;
WK_SELECT VARCHAR2(1024); -- select 文
WK_INSERT VARCHAR2(1024); -- insert 文
売上通常 NUMBER(10,0); -- (E) 1
売上通常消費税 NUMBER(10,0); -- (F) 1
売上中間請求 NUMBER(10,0); -- (A) 2
売上中間請求消費税 NUMBER(10,0); -- (B) 2
売上最終請求 NUMBER(10,0); -- (I) 3
売上最終請求消費税 NUMBER(10,0); -- (J) 3
売上値引 NUMBER(10,0); -- (C) 4
売上値引消費税 NUMBER(10,0); -- (D) 4
売上修正 NUMBER(10,0); -- (G) 5
売上修正消費税 NUMBER(10,0); -- (H) 5
ﾌﾞﾛｯｸ小ｺｰﾄﾞ NUMBER(4,0);
取引先ｺｰﾄﾞ NUMBER(5,0);
取引先支店ｺｰﾄﾞ NUMBER(2,0);
ｿｰﾄ区分 NUMBER(1,0);
金額 NUMBER(10,0);
消費税 NUMBER(10,0);
SVﾌﾞﾛｯｸ小ｺｰﾄﾞ NUMBER(4,0);
SV取引先ｺｰﾄﾞ NUMBER(5,0);
SV取引先支店ｺｰﾄﾞ NUMBER(2,0);
事業部 NUMBER(2,0);
部 NUMBER(1,0);
課 NUMBER(1,0);
前月請求額 NUMBER(10,0);
入金金額 NUMBER(10,0);
最新請求年月日 DATE;
/*===========================================================================
カーソル
===========================================================================*/
/*===========================================================================
ローカル関数
===========================================================================*/
/*=======================================================
最新請求締年月日取得
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;
/*=======================================================
請求明細テーブル名取得
IN PM_DATE : 締年月日
RETURN WK_TBLNAME : 目的テーブル名
=====================================================*/
FUNCTION GET_SEIDT_NAME (PM_DATE IN DATE) RETURN VARCHAR2
AS
WK_DATE DATE;
WK_DAY VARCHAR2(30);
WK_TBLNAME VARCHAR2(30);
BEGIN
WK_DATE := LAST_DAY(PM_DATE);
if WK_DATE = PM_DATE then
WK_TBLNAME := 'KF_SEIDT99';
else
WK_DAY := TO_CHAR( PM_DATE, 'DD' );
WK_TBLNAME := 'KF_SEIDT' || WK_DAY;
end if;
RETURN WK_TBLNAME;
dbms_output.put_line( WK_TBLNAME );
END GET_SEIDT_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;
/*=======================================================
請求ファイルの前レコード当月請求額を取得
=====================================================*/
FUNCTION GET_BACK_SEIKYU
(
PM_BLOCKSHO IN NUMBER
,PM_TORI IN NUMBER
,PM_TORI_ST IN NUMBER
)
RETURN NUMBER
AS
WK_KINGAKU NUMBER(10,0);
CURSOR KF_SEI_cur is
select
KIN_TOSEI
from
KF_SEI
where
CD_BLOCKSHO = PM_BLOCKSHO and
CD_TORI = PM_TORI and
CD_TORI_ST = PM_TORI_ST
order by
YMD_SIME_SEI DESC;
BEGIN
BEGIN
OPEN KF_SEI_cur;
FETCH KF_SEI_cur INTO WK_KINGAKU;
if KF_SEI_cur%NOTFOUND then
WK_KINGAKU := 0;
end if;
EXCEPTION
WHEN OTHERS THEN
WK_KINGAKU := 0;
END;
RETURN WK_KINGAKU;
END GET_BACK_SEIKYU;
/*=======================================================
回収Ｆより入金額を取得
=====================================================*/
FUNCTION GET_KAISYU
(
PM_BLOCKSHO IN NUMBER
,PM_TORI IN NUMBER
,PM_TORI_ST IN NUMBER
)
RETURN NUMBER
AS
WK_KINGAKU1 NUMBER(10,0);
WK_KINGAKU2 NUMBER(10,0);
WK_NYUKIN NUMBER(10,0);
BEGIN
WK_KINGAKU1 := 0;
WK_KINGAKU2 := 0;
select
nvl(sum(KIN_KAI),0)
into
WK_KINGAKU1
from
KF_TOKU_KAI
where
CD_BLOCKSHO = PM_BLOCKSHO and
CD_TORI = PM_TORI and
CD_TORI_ST = PM_TORI_ST and
NO_KIN = 1 and
NO_SYU not in (23,26) and
KIN_KAI is not NULL;
select
nvl(sum(KIN_KAI),0)
into
WK_KINGAKU2
from
KF_TOKU_KAI
where
CD_BLOCKSHO = PM_BLOCKSHO and
CD_TORI = PM_TORI and
CD_TORI_ST = PM_TORI_ST and
NO_KIN = 2 and
NO_SYU not in (23,26) and
YMD_KAI between WK_DATE and PM_DATE and
KIN_KAI is not NULL;
WK_NYUKIN := WK_KINGAKU1 - WK_KINGAKU2;
RETURN WK_NYUKIN;
END GET_KAISYU;
/*=====================================================
取引サブＭより部門コードを取得して
最新請求締年月日を更新し、
請求締日変更ファイルより該当レコードを削除する
=====================================================*/
PROCEDURE GET_TORISUB
(
PM_BLOCKSHO IN NUMBER
,PM_TORI IN NUMBER
,PM_TORI_ST IN NUMBER
,PM_DATE IN DATE
,PM_JIGYO IN OUT NUMBER
,PM_BUMON IN OUT NUMBER
,PM_KA IN OUT NUMBER
)
AS
WK_KBN_JITA NUMBER(1,0);
WK_SIME DATE;
BEGIN
BEGIN
select -- 取引サブＭより部門コードを取得
CD_JIGYO,
CD_BUMON,
CD_KA,
YMD_SEI_NEW
into
PM_JIGYO,
PM_BUMON,
PM_KA,
最新請求年月日
from
MM_TORISUB
where
CD_TORI = PM_TORI and
CD_TORI_ST = PM_TORI_ST;
if 最新請求年月日 is NULL then
WK_DATE := ADD_MONTHS(PM_DATE,-1);
WK_DATE := WK_DATE + 1;
else
WK_DATE := 最新請求年月日 + 1;
end if;
-- update
-- MM_TORISUB
-- set
-- YMD_SEI_NEW = PM_DATE
-- where
-- CD_TORI = PM_TORI and
-- CD_TORI_ST = PM_TORI_ST;
select
KBN_JITA
into
WK_KBN_JITA
from
MM_TORI
where
CD_TORI = PM_TORI and
CD_TORI_ST = PM_TORI_ST;
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%FOUND then
if WK_SIME = PM_DATE then
delete
from
KF_HSIME_SEI
where
CD_BLOCKSHO = PM_BLOCKSHO and
CD_TORI = PM_TORI and
CD_TORI_ST = PM_TORI_ST;
end if;
end if;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
PM_JIGYO := NULL;
PM_BUMON := NULL;
PM_KA := NULL;
WK_DATE := ADD_MONTHS(PM_DATE,-1);
WK_DATE := WK_DATE + 1;
WHEN OTHERS THEN
PM_JIGYO := NULL;
PM_BUMON := NULL;
PM_KA := NULL;
WK_DATE := ADD_MONTHS(PM_DATE,-1);
WK_DATE := WK_DATE + 1;
END;
END GET_TORISUB;
/*===========================================================================
単純サブルーチン
===========================================================================*/
/*=====================================================
ワーク変数の初期化
=====================================================*/
PROCEDURE ZERO_CLEAR
AS
BEGIN
売上通常 := 0;
売上通常消費税 := 0;
売上中間請求 := 0;
売上中間請求消費税 := 0;
売上最終請求 := 0;
売上最終請求消費税 := 0;
売上値引 := 0;
売上値引消費税 := 0;
売上修正 := 0;
売上修正消費税 := 0;
END ZERO_CLEAR;
/*=====================================================
データの登録
=====================================================*/
PROCEDURE INSERT_DATA
AS
BEGIN
dbms_sql.bind_variable(destination_cursor, 'CD_BLOCKSHO', PM_BLOCKSHO);
GET_TORISUB(PM_BLOCKSHO,SV取引先ｺｰﾄﾞ,SV取引先支店ｺｰﾄﾞ,PM_DATE,事業部,部,課);
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', SV取引先ｺｰﾄﾞ);
dbms_sql.bind_variable(destination_cursor, 'CD_TORI_ST', SV取引先支店ｺｰﾄﾞ);
dbms_sql.bind_variable(destination_cursor, 'YMD_FROM_SEI', WK_DATE);
dbms_sql.bind_variable(destination_cursor, 'YMD_SIME_SEI', PM_DATE);
前月請求額 := GET_BACK_SEIKYU(SVﾌﾞﾛｯｸ小ｺｰﾄﾞ,SV取引先ｺｰﾄﾞ,SV取引先支店ｺｰﾄﾞ);
dbms_sql.bind_variable(destination_cursor, 'KIN_ZENSEI', 前月請求額);
入金金額 := GET_KAISYU(SVﾌﾞﾛｯｸ小ｺｰﾄﾞ,SV取引先ｺｰﾄﾞ,SV取引先支店ｺｰﾄﾞ);
dbms_sql.bind_variable(destination_cursor, 'KIN_NYUKIN', 入金金額);
dbms_sql.bind_variable(destination_cursor, 'KIN_ZENSYU', 売上修正+売上修正消費税);
dbms_sql.bind_variable(destination_cursor, 'KIN_URIAGE', 売上通常+売上最終請求+売上値引);
dbms_sql.bind_variable(destination_cursor, 'KIN_TYUKAN', 売上中間請求);
dbms_sql.bind_variable(destination_cursor, 'ZEI_URIAGE', 売上中間請求消費税
+売上値引消費税
+売上通常消費税
+売上最終請求消費税);
dbms_sql.bind_variable(destination_cursor, 'KIN_TOSEI', 前月請求額
-入金金額
+売上通常+売上最終請求+売上値引
+売上中間請求
+売上中間請求消費税+売上値引消費税+売上通常消費税+売上最終請求消費税);
-- dbms_sql.bind_variable(destination_cursor, 'YMD_UPD', sysdate);
rows_processed := dbms_sql.execute(destination_cursor);
END INSERT_DATA;
/*===========================================================================
主処理
===========================================================================*/
BEGIN
/*=====================================================
初期処理
=====================================================*/
-- WK_DATE := GET_DATE_SIME(PM_DATE); -- 今回の締年月日よりfrom年月日を取得
/*=====================================================
SELECT文作成(請求明細**読込用)
金額と数量をサマリ
=====================================================*/
WK_SELECT :=
'select
CD_BLOCKSHO,
CD_TORI,
CD_TORI_ST,
sum(KIN_URIAGE),
sum(ZEI_URIAGE),
KBN_SORT
from ' || GET_SEIDT_NAME(PM_DATE) ||
' group by
CD_BLOCKSHO,
CD_TORI,
CD_TORI_ST,
KBN_SORT';
/*
'select
CD_BLOCKSHO,
CD_TORI,
CD_TORI_ST,
sum(KIN_URIAGE),
sum(ZEI_URIAGE),
KBN_SORT
from ' || GET_SEIDT_NAME(PM_DATE) ||
' where
CD_BLOCKSHO = :PM_BLOCKSHO and
YMD_URI_KJ between :WK_DATE and :PM_DATE
group by
CD_BLOCKSHO,
CD_TORI,
CD_TORI_ST,
KBN_SORT';
*/
/*=====================================================
入力定義
=====================================================*/
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.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);
/*=====================================================
出力定義(請求Ｆ)
=====================================================*/
destination_cursor := dbms_sql.open_cursor;
WK_INSERT :=
'insert into KF_SEI (
CD_BLOCKSHO,
CD_JIGYO,
CD_BUMON,
CD_KA,
CD_TORI,
CD_TORI_ST,
YMD_FROM_SEI,
YMD_SIME_SEI,
KIN_ZENSEI,
KIN_NYUKIN,
KIN_ZENSYU,
KIN_URIAGE,
KIN_TYUKAN,
ZEI_URIAGE,
KIN_TOSEI
-- YMD_UPD
)
values (
:CD_BLOCKSHO,
:CD_JIGYO,
:CD_BUMON,
:CD_KA,
:CD_TORI,
:CD_TORI_ST,
:YMD_FROM_SEI,
:YMD_SIME_SEI,
:KIN_ZENSEI,
:KIN_NYUKIN,
:KIN_ZENSYU,
:KIN_URIAGE,
:KIN_TYUKAN,
:ZEI_URIAGE,
:KIN_TOSEI
-- :YMD_UPD
)';
dbms_sql.parse( destination_cursor, WK_INSERT, 2 );
SVﾌﾞﾛｯｸ小ｺｰﾄﾞ := -1;
ZERO_CLEAR;
/*=====================================================
入出力
=====================================================*/
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_OUTPUT.PUT_LINE(
'取引先ｺｰﾄﾞ:' || TO_CHAR(取引先ｺｰﾄﾞ) ||
' 取引先支店ｺｰﾄﾞ:' || TO_CHAR(取引先支店ｺｰﾄﾞ) ||
' ｿｰﾄ区分:' || TO_CHAR(ｿｰﾄ区分) ||
' 金額:' || TO_CHAR(金額) ||
' 消費税:' || TO_CHAR(消費税)
);
if SVﾌﾞﾛｯｸ小ｺｰﾄﾞ = -1 then
NULL;
else
if SVﾌﾞﾛｯｸ小ｺｰﾄﾞ != ﾌﾞﾛｯｸ小ｺｰﾄﾞ or
SV取引先ｺｰﾄﾞ != 取引先ｺｰﾄﾞ or
SV取引先支店ｺｰﾄﾞ != 取引先支店ｺｰﾄﾞ then
BEGIN
INSERT_DATA;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
delete from KF_SEI where
CD_BLOCKSHO = PM_BLOCKSHO and
CD_JIGYO = 事業部 and
CD_BUMON = 部 and
CD_KA = 課 and
CD_TORI = SV取引先ｺｰﾄﾞ and
CD_TORI_ST = SV取引先支店ｺｰﾄﾞ and
YMD_SIME_SEI = PM_DATE;
INSERT_DATA;
WHEN OTHERS THEN
NULL;
END;
ZERO_CLEAR;
end if;
end if;
if ｿｰﾄ区分 = 1 then
売上通常 := 金額;
売上通常消費税 := 消費税;
elsif ｿｰﾄ区分 = 2 then
売上中間請求 := 金額;
売上中間請求消費税 := 消費税;
elsif ｿｰﾄ区分 = 3 then
売上最終請求 := 金額;
売上最終請求消費税 := 消費税;
elsif ｿｰﾄ区分 = 4 then
売上値引 := 金額;
売上値引消費税 := 消費税;
elsif ｿｰﾄ区分 = 5 then
売上修正 := 金額;
売上修正消費税 := 消費税;
end if;
SVﾌﾞﾛｯｸ小ｺｰﾄﾞ := ﾌﾞﾛｯｸ小ｺｰﾄﾞ;
SV取引先ｺｰﾄﾞ := 取引先ｺｰﾄﾞ;
SV取引先支店ｺｰﾄﾞ := 取引先支店ｺｰﾄﾞ;
else
if SVﾌﾞﾛｯｸ小ｺｰﾄﾞ = -1 then
NULL;
else
BEGIN
INSERT_DATA;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
delete from KF_SEI where
CD_BLOCKSHO = PM_BLOCKSHO and
CD_JIGYO = 事業部 and
CD_BUMON = 部 and
CD_KA = 課 and
CD_TORI = SV取引先ｺｰﾄﾞ and
CD_TORI_ST = SV取引先支店ｺｰﾄﾞ and
YMD_SIME_SEI = PM_DATE;
INSERT_DATA;
WHEN OTHERS THEN
NULL;
END;
end if;
exit;
end if;
end loop;
/*=====================================================
終了処理
=====================================================*/
commit;
dbms_sql.close_cursor(source_cursor);
dbms_sql.close_cursor(destination_cursor);
dbms_output.put_line( 'NEC7' );
/*=====================================================
例外処理
=====================================================*/
EXCEPTION
WHEN OTHERS THEN
if dbms_sql.is_open(source_cursor) then
dbms_sql.close_cursor(source_cursor);
end if;
if dbms_sql.is_open(destination_cursor) then
dbms_sql.close_cursor(destination_cursor);
end if;
END SAMPLE01;
/