harukaze-sonataの日記

--@V:\ORCL2\local\seikyuu7\TouZan.sql

/**

create table 受注先請求書出力チェック (

constraint pk_受注先請求書出力チェック primary key(日付, 締日, 顧客ID)

,日付 varchar2(8)

,締日 varchar2(4)

,顧客ID varchar2(20)

);

/**

create table 受注先請求書残高チェック (

constraint pk_受注先請求書残高チェック primary key(日付, 顧客ID)

,日付 date

,顧客ID varchar2(20)

,残高 number

,確認 number(1) --0: NO, 1: OK

);

create table 受注先請求書印刷フラグ (

constraint pk_受注先請求書印刷フラグ primary key(顧客ID)

,顧客ID varchar2(20)

,印刷フラグ number(1) default 1 --0: NO, 1: OK

);

create or replace procedure Gen_Seikyuu_Print_Flag(pcid varchar2)

cnt number;

begin

select count(*) into cnt from 受注先請求書印刷フラグ

where 顧客ID = pcid;

if cnt = 0 then

insert into 受注先請求書印刷フラグ values(pcid, 1);

end if;

end Gen_Seikyuu_Print_Flag;

show errors;

create or replace procedure Update_Seikyuu_Print_Flag(pcid varchar2)

w number(2);

begin

select 印刷フラグ into w from 受注先請求書印刷フラグ

where 顧客ID = pcid;

if w = 1 then

w := 0;

else

w := 1;

end if;

update 受注先請求書印刷フラグ set 印刷フラグ = w

where 顧客ID = pcid;

end Update_Seikyuu_Print_Flag;

show errors;

--exec Calc_Seikyuu_TouZan('97/7/1', '20')

--exec Calc_Seikyuu_TouZan('98/7/1', '15')

create or replace procedure

Calc_Seikyuu_TouZan(theDate date, simeDay varchar2)

cursor cur_Client_Master(p_simeDay varchar2) is

select 顧客ID, 締日

from 顧客マスタ

where 締日 = p_simeDay;

rec_Client_Master cur_Client_Master%ROWTYPE;

cursor cur_sen(cId varchar2, endDate date) is

select sum(M.個数*M.数量*M.単価) 先月繰越

from 請求書先月繰越 M

where M.締切日付 = endDate

and M.店名ID = cId;

rec_sen cur_sen%ROWTYPE;

cursor cur_jig(cId varchar2, endDate date) is

select sum(M.個数*M.数量*M.単価) 次月繰越

from 請求書次月繰越 M

where M.締切日付 = endDate

and M.店名ID = cId;

rec_jig cur_jig%ROWTYPE;

cId varchar2(20);

startDate date;

endDate date;

sen number;

jig number;

work pkg_Account.workTable;

begin

endDate := getLimitDate(simeDay, theDate);

delete from 受注先請求書残高チェック

where 日付 = endDate;

for rec_Client_Master in cur_Client_Master(simeDay)

loop

cId := rec_Client_Master.顧客ID;

Gen_Seikyuu_Print_Flag(cid); --受注先請求書印刷フラグ作成

Cnv_From2End(cId, theDate, startDate, endDate);

sen := 0;

for rec_sen in cur_sen(cId, endDate)

loop

sen := rec_sen.先月繰越;

end loop;

if sen is null then

sen := 0;

end if;

jig := 0;

for rec_jig in cur_jig(cId, endDate)

loop

jig := rec_jig.次月繰越;

end loop;

if jig is null then

jig := 0;

end if;

pkg_Account.acc_sum_by_ClientS(414000, cId, startDate, endDate, work); --売上値引

pkg_Account.acc_sum_by_ClientS(430000, cId, startDate, endDate, work); --売上相殺

pkg_Account.acc_sum_by_ClientS(413000, cId, startDate, endDate, work); --手数料

pkg_Account.acc_sum_by_ClientS(104000, cId, startDate, endDate, work); --振込

pkg_Account.acc_sum_by_clientS(130010, cId, startDate, endDate, work); --普通_大和_寝

pkg_Account.acc_sum_by_clientS(130070, cId, startDate, endDate, work); --普通_さくら

pkg_Account.acc_sum_by_ClientS(103000, cId, startDate, endDate, work); --受取手形

pkg_Account.acc_sum_by_ClientS(101000, cId, startDate, endDate, work); --現金

pkg_Account.acc_sum_by_ClientS(102000, cId, startDate, endDate, work); --当座預金

pkg_Account.acc_sum_by_ClientS(500000, cId, startDate, endDate, work); --売上

pkg_Account.acc_sum_by_ClientS(420000, cId, startDate, endDate, work); --良品返品

pkg_Account.acc_sum_by_ClientS(421000, cId, startDate, endDate, work); --不良返品

work(1) := work(104000)+work(103000)+work(130070)+work(101000)+work(102000)

+work(130010); --入金額

work(2) := work(500000)-work(420000)-work(421000); --当月売掛

work(3) := ROUND(work(2)*Generate_Current_TaxRate_(0)); --消費税

work(4) := sen-(work(1)+work(414000)+work(430000)+work(413000))+work(2)+work(3); --当月残高

work(5) := sen-(work(1)+work(414000)+work(430000)+work(413000)); --繰越残高

-- delete from 受注先請求書残高チェック

-- where 日付 = endDate

-- and 顧客ID = cId;

if work(4) != 0 then

insert into 受注先請求書残高チェック

values(endDate, cId, work(4), 1);

end if;

end loop;

COMMIT;

end Calc_Seikyuu_TouZan;

show errors

create or replace procedure Calc_Seikyuu_TouZan2(cId varchar2, theDate date)

cursor cur_sen(cId varchar2, endDate date) is

select sum(M.個数*M.数量*M.単価) 先月繰越

from 請求書先月繰越 M

where M.締切日付 = endDate

and M.店名ID = cId;

rec_sen cur_sen%ROWTYPE;

cursor cur_jig(cId varchar2, endDate date) is

select sum(M.個数*M.数量*M.単価) 次月繰越

from 請求書次月繰越 M

where M.締切日付 = endDate

and M.店名ID = cId;

rec_jig cur_jig%ROWTYPE;

startDate date;

endDate date;

sen number;

jig number;

work pkg_Account.workTable;

begin

Cnv_From2End(cId, theDate, startDate, endDate);

delete from 受注先請求書残高チェック

where 日付 = endDate

and 顧客ID = cId;