create table BALANCESTEMP
(
je_line_num number,
headerID number(38),
dr number,
cr number,
BALANCE VARCHAR2(4000) not null,
BANKNAME VARCHAR2(4000) not null,
MM DATE not null,
ID NUMBER not null
)
创建临时表
CREATE OR REPLACE FUNCTION GAB_FUNC(line_num number,
ae_header_id number,
DR NUMBER,
CR NUMBER,
startdate date,
ENDDATE DATE,
accounting_date DATE,
BRANKACCT VARCHAR2) RETURN CHAR IS
PRAGMA AUTONOMOUS_TRANSACTION;
BLtableCUT NUMBER(38);
LStableCUT NUMBER(38);
BL varchar(4000);
STR NUMBER;
CF varchar(4000);
X number;
CT NUMBER;
jishu NUMBER:=0;
c number;
c1 number;
c2 number;
enBL number;
balance number;
BEGIN
/**查询余额表 2010-06-01至用户参数开始日期前的所有数据**/
SELECT count(*) into BLtableCUT
FROM (select distinct xal.accounting_date AS accounting_date,xal.ae_header_id as a,
xah.doc_sequence_value AS doc_sequence_value,
xal.accounted_dr AS accounted_dr,
xal.accounted_cr AS accounted_cr,
SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)) AS CODE_ACCOUNTS
from xla_transaction_entities xte,
xla_entity_types_tl xett,
xle_entity_profiles le,
xla_events xe,
xla_event_types_tl xent,
xla_ae_headers xah,
xla_ae_lines xal,
xla_lookups xlp,
xla_distribution_links xdl,
GL_CODE_COMBINATIONS_KFV GCC_ORI
where 1 = 1
and xte.entity_id = xe.entity_id
and xte.application_id = xe.application_id
and xte.legal_entity_id = le.legal_entity_id(+)
and xah.event_id = xe.event_id
and xah.application_id = xe.application_id(+)
and xent.event_type_code = xe.event_type_code
and xent.application_id(+) = xe.application_id
and xent.language = 'ZHS'
and xah.ae_header_id(+) = xal.ae_header_id
and xlp.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
and xlp.lookup_code(+) = xal.accounting_class_code
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num(+)
and xal.application_id = xdl.application_id(+)
and xett.entity_code = xte.entity_code
and xett.application_id = xte.application_id
AND XAL.CODE_COMBINATION_ID = GCC_ORI.CODE_COMBINATION_ID
and xett.language = 'ZHS'
and SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)) =
NVL(BRANKACCT,
SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)))
AND TO_CHAR(xal.accounting_date, 'yyyy-mm-dd') between
'2010-06-30' and TO_CHAR(startdate-1, 'yyyy-mm-dd')
union all
select headers.default_effective_date as accounting_date,headers.je_header_id as a,
headers.doc_sequence_value as doc_sequence_value,
lines.accounted_dr as accounted_dr,
lines.accounted_cr as accounted_cr,
lines.segment3
from GL_JE_HEADERS_v headers,
GL_JE_LINES_V lines,
GL_CODE_COMBINATIONS_KFV GCC_ORI
where headers.je_header_id = lines.je_header_id
and HEADERS.JE_CATEGORY = '1'
and lines.segment3 = NVL(BRANKACCT, lines.segment3)
and lines.code_combination_id = gcc_ori.code_combination_id
AND TO_CHAR(headers.default_effective_date, 'yyyy-mm-dd') between
'2010-06-30' and TO_CHAR(startdate-1, 'yyyy-mm-dd')
)
ORDER BY accounting_date, doc_sequence_value;
/**查询2010-06-01至用户参数开始时间之间的数据*判断是否有余额*/
SELECT COUNT(*)
INTO LStableCUT
FROM BALANCESTEMP B1
WHERE
B1.BANKNAME=BRANKACCT
AND B1.MM BETWEEN to_date('2010-6-30','yyyy-mm-dd') AND TO_DATE(TO_CHAR(startdate-1, 'yyyy-mm-dd'),'yyyy-mm-dd') ;
IF TO_CHAR(startdate-1, 'yyyy-mm-dd')='2010-06-30' THEN--判断用户参数开始日期是否为开帐日期
LStableCUT:=LStableCUT;
END IF;
IF TO_CHAR(startdate-1, 'yyyy-mm-dd')<>'2010-06-30' THEN
LStableCUT:=LStableCUT-1;
END IF;
SELECT count(*)
INTO jishu
FROM BALANCESTEMP b
WHERE MM = TO_DATE(TO_CHAR(accounting_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') --判断是否已经存在
and b.bankName = BRANKACCT
and b.headerID = ae_header_id
and b.je_line_num = line_num ;
dbms_output.put_line(LStableCUT||':LStableCUTLStableCUT');
dbms_output.put_line(BLtableCUT||':BLtableCUTBLtableCUT');
IF LStableCUT=BLtableCUT THEN --如果临时表科目总记录数与余额表记录数相等;则直接进行余额递减操作
dbms_output.put_line(jishu||':jishu');
IF jishu<=0 THEN
SELECT MAX(id)
INTO X
FROM BALANCESTEMP
WHERE MM <= TO_DATE(TO_CHAR(ENDDATE, 'yyyy-mm-dd'),
'yyyy-mm-dd') and bankName=BRANKACCT;
SELECT BT.BALANCE INTO BL FROM BALANCESTEMP BT WHERE bt.id=x;
STR := TO_NUMBER(BL);
CF := TO_CHAR(STR + NVL(DR, 0) - NVL(CR, 0));
select max(id) into c from BALANCESTEMP;
c1:=c+1;
INSERT INTO BALANCESTEMP (je_line_num,HEADERID,dr,cr,BALANCE, MM,bankName,id) VALUES (line_num,ae_header_id,dr,cr,CF, accounting_date,BRANKACCT,c1);
COMMIT;
RETURN CF;
end if;
END IF;
IF LStableCUT<>BLtableCUT THEN --如果临时表科目总记录数与余额表记录数 不相等; 则将用户参数开始日期前的所有数据初始化
IF jishu<=0 THEN
enBL:= STARTBALANCE(startdate,BRANKACCT );
SELECT MAX(id)
INTO X
FROM BALANCESTEMP
WHERE MM <= TO_DATE(TO_CHAR(ENDDATE, 'yyyy-mm-dd'),
'yyyy-mm-dd') and bankName=BRANKACCT;
SELECT BT.BALANCE INTO BL FROM BALANCESTEMP BT WHERE bt.id=x;
STR := TO_NUMBER(BL);
CF := TO_CHAR(STR + NVL(DR, 0) - NVL(CR, 0));
select max(id) into c from BALANCESTEMP;
c1:=c+1;
INSERT INTO BALANCESTEMP (je_line_num,HEADERID,dr,cr,BALANCE, MM,bankName,id) VALUES (line_num,ae_header_id,dr,cr,CF, accounting_date,BRANKACCT,c1);
COMMIT;
RETURN CF;
END IF;
END IF;
RETURN CF;
END;
CREATE OR REPLACE FUNCTION STARTBALANCE(startdate date,
BRANKACCT VARCHAR2) RETURN CHAR IS
PRAGMA AUTONOMOUS_TRANSACTION;
BL varchar(4000);
X number;
STR NUMBER;
CF varchar(4000);
c number;
c1 number;
c2 number;
CUT number;
enBL number;
begin
declare
cursor cc is
SELECT ae_header_id,
doc_sequence_value,
je_line_num,
accounting_date,
accounted_dr,
accounted_cr,
entered_dr,
entered_cr,
CODE_ACCOUNTS
FROM (select distinct xal.accounting_date AS accounting_date,
xah.doc_sequence_value AS doc_sequence_value,
xal.ae_line_num as je_line_num,
xal.ae_header_id as ae_header_id,
xal.accounted_dr AS accounted_dr,
xal.accounted_cr AS accounted_cr,
xal.entered_dr AS entered_dr,
xal.entered_cr AS entered_cr,
SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS,
'-',
1,
2) + 1,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS,
'-',
1,
2)) AS CODE_ACCOUNTS
from xla_transaction_entities xte,
xla_entity_types_tl xett,
xle_entity_profiles le,
xla_events xe,
xla_event_types_tl xent,
xla_ae_headers xah,
xla_ae_lines xal,
xla_lookups xlp,
xla_distribution_links xdl,
GL_CODE_COMBINATIONS_KFV GCC_ORI
where 1 = 1
and xte.entity_id = xe.entity_id
and xte.application_id = xe.application_id
and xte.legal_entity_id = le.legal_entity_id(+)
and xah.event_id = xe.event_id
and xah.application_id = xe.application_id(+)
and xent.event_type_code = xe.event_type_code
and xent.application_id(+) = xe.application_id
and xent.language = 'ZHS'
and xah.ae_header_id(+) = xal.ae_header_id
and xlp.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
and xlp.lookup_code(+) = xal.accounting_class_code
and xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num(+)
and xal.application_id = xdl.application_id(+)
and xett.entity_code = xte.entity_code
and xett.application_id = xte.application_id
AND XAL.CODE_COMBINATION_ID = GCC_ORI.CODE_COMBINATION_ID
and xett.language = 'ZHS'
and SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)) =
NVL(BRANKACCT,
SUBSTR(GCC_ORI.CONCATENATED_SEGMENTS,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2) + 1,
INSTR(GCC_ORI.CONCATENATED_SEGMENTS, '-', 1, 2)))
AND TO_CHAR(xal.accounting_date, 'yyyy-mm-dd') between
'2010-06-30' and TO_CHAR(startdate - 1, 'yyyy-mm-dd')
union all
select headers.default_effective_date as accounting_date,
headers.doc_sequence_value as doc_sequence_value,
lines.je_line_num as je_line_num,
headers.je_header_id as ae_header_id,
lines.accounted_dr as accounted_dr,
lines.accounted_cr as accounted_cr,
lines.entered_dr as entered_dr,
lines.entered_cr as entered_cr,
lines.segment3
from GL_JE_HEADERS_v headers,
GL_JE_LINES_V lines,
GL_CODE_COMBINATIONS_KFV GCC_ORI
where headers.je_header_id = lines.je_header_id
and HEADERS.JE_CATEGORY = '1'
and lines.segment3 = NVL(BRANKACCT, lines.segment3)
and lines.code_combination_id = gcc_ori.code_combination_id
AND TO_CHAR(headers.default_effective_date, 'yyyy-mm-dd') between
'2010-06-30' and TO_CHAR(startdate - 1, 'yyyy-mm-dd'))
ORDER BY accounting_date, doc_sequence_value;
begin
for i in cc loop
SELECT MAX(id)
INTO X
FROM BALANCESTEMP
WHERE MM < TO_DATE(TO_CHAR(startdate, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and bankName = BRANKACCT;
SELECT BT.BALANCE INTO BL FROM BALANCESTEMP BT WHERE bt.id = x;
STR := TO_NUMBER(BL);
SELECT count(*)
INTO CUT
FROM BALANCESTEMP
WHERE MM = TO_DATE(TO_CHAR(i.accounting_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and bankName = BRANKACCT
and je_line_num=i.je_line_num
and headerID =i.ae_header_id;
IF CUT<=0 THEN
CF := TO_CHAR(STR + NVL(i.accounted_dr, 0) - NVL(i.accounted_cr, 0));
select max(id) into c from BALANCESTEMP;
c1 := c + 1;
INSERT INTO BALANCESTEMP
(je_line_num,headerID, dr, cr, BALANCE, MM, bankName, id)
VALUES
(
i.je_line_num,
i.ae_header_id,
i.accounted_dr,
i.accounted_cr,
CF,
i.accounting_date,
BRANKACCT,
c1);
COMMIT;
END IF;
end loop;
return CF;
end;
end;
调用方法
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24899662/viewspace-678542/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24899662/viewspace-678542/