--@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)
is
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)
is
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)
is
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;
cId varchar2(20);
startDate date;
endDate date;
sen 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;
for rec_jig in cur_jig(cId, endDate)
loop
end loop;
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)
is
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;
startDate date;
endDate date;
sen number;
work pkg_Account.workTable;
begin
Cnv_From2End(cId, theDate, startDate, endDate);
delete from 受注先請求書残高チェック
where 日付 = endDate
and 顧客ID = cId;
Gen_Seikyuu_Print_Flag(cid); --受注先請求書印刷フラグ作成
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;
for rec_
jig in cur_
jig(cId, endDate)
loop
end loop;
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)); --繰越残高
-- if work(4) != 0 then
insert into 受注先請求書残高チェック
values(endDate, cId, work(4), 1);
-- end if;
COMMIT;
end Calc_Seikyuu_TouZan2;
/
show errors
create or replace procedure Calc_Seikyuu_TouZan_Ack(cId varchar2, endDate date)
is
begin
update 受注先請求書残高チェック
set 確認 = DECODE(確認, 1, 0, 1)
where 日付 = endDate
and 顧客ID = cId;
COMMIT;
end Calc_Seikyuu_TouZan_Ack;
/
show errors;