create or replace package body sdic_mxz is PROCEDURE output(pv_mesg IN VARCHAR2) IS BEGIN fnd_file.put_line(fnd_file.output, pv_mesg); END output; procedure gl_mxz(pv_errbuff OUT VARCHAR2, pn_retcode OUT NUMBER, setofbooksid in number, pass in varchar2, --是否显示未过帐信息 kmcc in number, start_period in varchar2 default null, end_period in varchar2 default null, start_kjkm in varchar2 default null, end_kjkm in varchar2 default null, zcx_type in number, start_segment in varchar2 default null, end_segment in varchar2 default null, company in varchar2 default null) is pl_start_period date; pl_end_period date; pl_start_kjkm number; pl_end_kjkm number; amount number; amount_dr number; amount_cr number; sum_dr number; sum_cr number; i number; j number; cursor c_period is select p.period_set_name, p.period_name, p.period_year, p.quarter_num, p.period_num, p.start_date, p.end_date from apps.gl_periods p where p.period_set_name = 'SDIC Calendar' and to_char(p.period_year * 100 + p.period_num) between substr(start_period, 4, 4) || substr(start_period, 1, 2) and substr(end_period, 4, 4) || substr(end_period, 1, 2) order by p.period_year, p.period_num; cursor c_kjkm(kmlen number) is select vv.FLEX_VALUE, vv.DESCRIPTION from apps.fnd_flex_value_sets vs, apps.fnd_flex_values_vl vv where vs.flex_value_set_name = 'SDIC_ACC_ACC' and vs.flex_value_set_id = vv.flex_value_set_id and vv.ENABLED_FLAG = 'Y' and length(vv.FLEX_VALUE) = kmlen and substr(vv.FLEX_VALUE, 1, 1) not in ('C', 'S') and vv.FLEX_VALUE not like '%CJ%' and vv.FLEX_VALUE between substr(start_kjkm, 1, kmlen) and substr(end_kjkm, 1, kmlen) order by vv.FLEX_VALUE; cursor c_segment is select v.FLEX_VALUE, v.DESCRIPTION from apps.fnd_flex_value_sets vs, apps.fnd_flex_values_vl v where vs.flex_value_set_name = decode(zcx_type,2,'SDIC_ACC_CC',zcx_type,6,'SDIC_ACC_PRJ',7,'SDIC_ACC_REF',null) and vs.flex_value_set_id = v.flex_value_set_id and v.ENABLED_FLAG = 'Y' and v.SUMMARY_FLAG = 'N' and v.flex_value between '000000' and '900000' and v.flex_value between substr(start_segment, 1, 6) and substr(end_segment, 1, 6) order by v.flex_value;
cursor c_begin(kjkm number, kmlen number, bankid varchar2, periodname varchar2) is select nvl(b.begin_balance_dr, 0) - nvl(b.begin_balance_cr, 0) begin_balance from gl_balances b, gl_code_combinations c--, --org_organization_definitions ood where setofbooksid = b.set_of_books_id --and ood.ORGANIZATION_CODE = c.segment1 --and ood.SET_OF_BOOKS_ID = setofbooksid --and pn_org_id = ood.ORGANIZATION_ID and c.code_combination_id = b.code_combination_id and b.actual_flag = 'A' and c.enabled_flag = 'Y' and c.summary_flag = 'N' and substr(c.segment3, 1, kmlen) = substr(to_char(kjkm), 1, kmlen) and b.period_name = periodname and decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1) = decode(bankid, null, decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1), bankid) and c.segment1=nvl(company,c.segment1);
from gl_balances b, gl_code_combinations c where setofbooksid = b.set_of_books_id and c.code_combination_id = b.code_combination_id and b.actual_flag = 'A' and c.enabled_flag = 'Y' and c.summary_flag = 'N' and substr(c.segment3, 1, kmlen) = substr(to_char(kjkm), 1, kmlen) and b.period_year = to_number(substr(periodname, 4, 4)) and b.period_num < to_number(substr(periodname, 1, 2)) and decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1) = decode(bankid, null, decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1), bankid) and c.segment1=nvl(company,c.segment1);
cursor c_mxz(kjkm number, bankid varchar2, periodname varchar2, kmlen number) is select hb.default_effective_date, --时间 b.status, --过帐状态 c.concatenated_segments, --八段组合 hb.doc_sequence_value, --凭证号 b.description, --摘要 hb.name, --说明 b.entered_dr, --本行借贷值 b.entered_cr from gl_je_lines b, gl_je_headers hb, gl_code_combinations_kfv c--, --org_organization_definitions ood where hb.actual_flag = 'A' and b.status in ('P', decode(upper(pass), 'Y', 'U', 'P')) and b.je_header_id = hb.je_header_id and c.summary_flag = 'N' and c.enabled_flag = 'Y' and substr(c.segment3, 1, kmlen) = to_char(kjkm) and b.period_name = periodname and decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1) = decode(bankid, null, decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1), bankid) and setofbooksid = b.set_of_books_id and b.set_of_books_id=hb.set_of_books_id and c.code_combination_id = b.code_combination_id and c.segment1=nvl(company,c.segment1) order by hb.default_effective_date, b.je_header_id, b.je_line_num;
begin output('<HTML><HEAD></HEAD><BODY>'); output('<H1><CENTER>总帐明细帐</CENTER></H1><P>'); output('<hr width="50%">'); output('<TABLE>'); for v in c_kjkm(kmcc) loop i := 0; amount_dr := 0; amount_cr := 0; sum_dr := 0; sum_cr := 0; if (start_segment is null) or (end_segment is null) then for w in c_period loop j := 0; amount := 0; amount_dr := amount_dr + sum_dr; amount_cr := amount_cr + sum_cr; sum_dr := 0; sum_cr := 0; for v1 in c_begin(v.flex_value, kmcc, null, w.period_name) loop amount := amount + v1.begin_balance; end loop; if i = 0 then for v3 in c_sum(v.flex_value, kmcc, null, start_period) loop amount_dr := amount_dr + v3.period_net_dr; amount_cr := amount_cr + v3.period_net_cr; --output('ssssss'||amount_dr||'ssssssssss'); end loop;
i := i + 1; output('<TABLE>'); output('<TR><TR><P>');
BEGIN
fnd_file.put_line(fnd_file.output, pv_mesg);
END output;
procedure gl_mxz(pv_errbuff OUT VARCHAR2,
pn_retcode OUT NUMBER,
setofbooksid in number,
pass in varchar2, --是否显示未过帐信息
kmcc in number,
start_period in varchar2 default null,
end_period in varchar2 default null,
start_kjkm in varchar2 default null,
end_kjkm in varchar2 default null,
zcx_type in number,
start_segment in varchar2 default null,
end_segment in varchar2 default null,
company in varchar2 default null) is
pl_start_period date;
pl_end_period date;
pl_start_kjkm number;
pl_end_kjkm number;
amount number;
amount_dr number;
amount_cr number;
sum_dr number;
sum_cr number;
i number;
j number;
cursor c_period is
select p.period_set_name,
p.period_name,
p.period_year,
p.quarter_num,
p.period_num,
p.start_date,
p.end_date
from apps.gl_periods p
where p.period_set_name = 'SDIC Calendar'
and to_char(p.period_year * 100 + p.period_num) between
substr(start_period, 4, 4) || substr(start_period, 1, 2) and
substr(end_period, 4, 4) || substr(end_period, 1, 2)
order by p.period_year, p.period_num;
cursor c_kjkm(kmlen number) is
select vv.FLEX_VALUE, vv.DESCRIPTION
from apps.fnd_flex_value_sets vs, apps.fnd_flex_values_vl vv
where vs.flex_value_set_name = 'SDIC_ACC_ACC'
and vs.flex_value_set_id = vv.flex_value_set_id
and vv.ENABLED_FLAG = 'Y'
and length(vv.FLEX_VALUE) = kmlen
and substr(vv.FLEX_VALUE, 1, 1) not in ('C', 'S')
and vv.FLEX_VALUE not like '%CJ%'
and vv.FLEX_VALUE between substr(start_kjkm, 1, kmlen) and
substr(end_kjkm, 1, kmlen)
order by vv.FLEX_VALUE;
cursor c_segment is
select v.FLEX_VALUE, v.DESCRIPTION
from apps.fnd_flex_value_sets vs, apps.fnd_flex_values_vl v
where vs.flex_value_set_name = decode(zcx_type,2,'SDIC_ACC_CC',zcx_type,6,'SDIC_ACC_PRJ',7,'SDIC_ACC_REF',null)
and vs.flex_value_set_id = v.flex_value_set_id
and v.ENABLED_FLAG = 'Y'
and v.SUMMARY_FLAG = 'N'
and v.flex_value between '000000' and '900000'
and v.flex_value between substr(start_segment, 1, 6) and
substr(end_segment, 1, 6)
order by v.flex_value;
cursor c_begin(kjkm number, kmlen number, bankid varchar2, periodname varchar2) is
select nvl(b.begin_balance_dr, 0) - nvl(b.begin_balance_cr, 0) begin_balance
from gl_balances b,
gl_code_combinations c--,
--org_organization_definitions ood
where setofbooksid = b.set_of_books_id
--and ood.ORGANIZATION_CODE = c.segment1
--and ood.SET_OF_BOOKS_ID = setofbooksid
--and pn_org_id = ood.ORGANIZATION_ID
and c.code_combination_id = b.code_combination_id
and b.actual_flag = 'A'
and c.enabled_flag = 'Y'
and c.summary_flag = 'N'
and substr(c.segment3, 1, kmlen) = substr(to_char(kjkm), 1, kmlen)
and b.period_name = periodname
and decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1) = decode(bankid, null, decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1), bankid)
and c.segment1=nvl(company,c.segment1);
cursor c_sum(kjkm number, kmlen number, bankid varchar2, periodname varchar2) is
select nvl(b.period_net_dr, 0) period_net_dr,
nvl(b.period_net_cr, 0) period_net_cr
from gl_balances b,
gl_code_combinations c
where setofbooksid = b.set_of_books_id
and c.code_combination_id = b.code_combination_id
and b.actual_flag = 'A'
and c.enabled_flag = 'Y'
and c.summary_flag = 'N'
and substr(c.segment3, 1, kmlen) = substr(to_char(kjkm), 1, kmlen)
and b.period_year = to_number(substr(periodname, 4, 4))
and b.period_num < to_number(substr(periodname, 1, 2))
and decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1) = decode(bankid, null, decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1), bankid)
and c.segment1=nvl(company,c.segment1);
cursor c_mxz(kjkm number, bankid varchar2, periodname varchar2, kmlen number) is
select hb.default_effective_date, --时间
b.status, --过帐状态
c.concatenated_segments, --八段组合
hb.doc_sequence_value, --凭证号
b.description, --摘要
hb.name, --说明
b.entered_dr, --本行借贷值
b.entered_cr
from gl_je_lines b,
gl_je_headers hb,
gl_code_combinations_kfv c--,
--org_organization_definitions ood
where hb.actual_flag = 'A'
and b.status in ('P', decode(upper(pass), 'Y', 'U', 'P'))
and b.je_header_id = hb.je_header_id
and c.summary_flag = 'N'
and c.enabled_flag = 'Y'
and substr(c.segment3, 1, kmlen) = to_char(kjkm)
and b.period_name = periodname
and decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1) = decode(bankid, null, decode(zcx_type,2,c.segment2,6,c.segment6,7,c.segment7,1), bankid)
and setofbooksid = b.set_of_books_id
and b.set_of_books_id=hb.set_of_books_id
and c.code_combination_id = b.code_combination_id
and c.segment1=nvl(company,c.segment1)
order by hb.default_effective_date, b.je_header_id, b.je_line_num;
begin
output('<HTML><HEAD></HEAD><BODY>');
output('<H1><CENTER>总帐明细帐</CENTER></H1><P>');
output('<hr width="50%">');
output('<TABLE>');
for v in c_kjkm(kmcc) loop
i := 0;
amount_dr := 0;
amount_cr := 0;
sum_dr := 0;
sum_cr := 0;
if (start_segment is null) or (end_segment is null) then
for w in c_period loop
j := 0;
amount := 0;
amount_dr := amount_dr + sum_dr;
amount_cr := amount_cr + sum_cr;
sum_dr := 0;
sum_cr := 0;
for v1 in c_begin(v.flex_value, kmcc, null, w.period_name) loop
amount := amount + v1.begin_balance;
end loop;
if i = 0 then
for v3 in c_sum(v.flex_value, kmcc, null, start_period) loop
amount_dr := amount_dr + v3.period_net_dr;
amount_cr := amount_cr + v3.period_net_cr;
--output('ssssss'||amount_dr||'ssssssssss');
end loop;
i := i + 1;
output('<TABLE>');
output('<TR><TR><P>');
output('<TR>' || '<TD>会计科目: </TD>' ||
'<TD>' || v.flex_value || '（' || v.DESCRIPTION ||
'）</TD>' || '<TR><P>');
output('<TR>' || '<TD>起止会计期间: </TD>' ||
'<TD>' || start_period || '至' || end_period || '</TD>' ||
'<TR><P>');
output('</TABLE>');
output('<TABLE BORDER=1>');
output('<TR>' || '<TD align=center><B>日期</B></TD>' ||
'<TD align=center><B>凭证号 </TH>' ||
'<TD align=center><B>日记帐名称 </TH>' ||
'<TD align=center><B>过帐状态</B></TD>' ||
'<TD align=center><B>摘要</B></TD>' ||
'<TD align=center><B>对应科目</B></TD>' ||
'<TD align=center><B>借方</B></TD>' ||
'<TD align=center><B>贷方</B></TD>' ||
'<TD align=center><B>余额</B></TD>' || '</TR>');
output('<TR>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || '期初余额' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || to_char(amount, '999G999G999G999G990D99') ||
'</TD>');
output('</TR>');
end if;
j := j + 1;
amount := amount + nvl(v2.entered_dr, 0) -
nvl(v2.entered_cr, 0);
sum_dr := sum_dr + nvl(v2.entered_dr, 0);
sum_cr := sum_cr + nvl(v2.entered_cr, 0);
output('<TR>');
output('<TD>' ||
nvl(to_char(v2.default_effective_date, 'YYYY-MON-DD'),
' ') || '</TD>');
output('<TD align=RIGHT>' ||
nvl(to_char(v2.doc_sequence_value), ' ') || '</TD>');
output('<TD>' || nvl(v2.name, ' ') || '</TD>');
output('<TD>' || nvl(v2.status, ' ') || '</TD>');
output('<TD>' || nvl(v2.description, ' ') || '</TD>');
output('<TD>' || nvl(v2.concatenated_segments, ' ') ||
'</TD>');
output('<TD>' ||
nvl(to_char(v2.entered_dr, '999G999G999G999G990D99'),
' ') || '</TD>');
output('<TD>' ||
nvl(to_char(v2.entered_cr, '999G999G999G999G990D99'),
' ') || '</TD>');
output('<TD>' ||
nvl(to_char(amount, '999G999G999G999G990D99'), ' ') ||
'</TD>');
output('</TR>');
end loop;
if j > 0 then
output('<TR>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || '本月合计' || '</TD>');
output('<TD>' ||
nvl(to_char(sum_dr, '999G999G999G999G990D99'), ' ') ||
'</TD>');
output('<TD>' ||
nvl(to_char(sum_cr, '999G999G999G999G990D99'), ' ') ||
'</TD>');
output('<TD>' ||
nvl(to_char(amount, '999G999G999G999G990D99'), ' ') ||
'</TD>');
output('</TR>');
output('<TR>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || '本年累计' || '</TD>');
output('<TD>' || nvl(to_char(amount_dr + sum_dr,
'999G999G999G999G990D99'),
' ') || '</TD>');
output('<TD>' || nvl(to_char(amount_cr + sum_cr,
'999G999G999G999G990D99'),
' ') || '</TD>');
output('<TD>' ||
nvl(to_char(amount, '999G999G999G999G990D99'), ' ') ||
'</TD>');
output('</TR>');
end if;
end loop;
else
for vs in c_segment loop
i := 0;
amount_dr := 0;
amount_cr := 0;
sum_dr := 0;
sum_cr := 0;
for w in c_period loop
j := 0;
amount := 0;
amount_dr := amount_dr + sum_dr;
amount_cr := amount_cr + sum_cr;
sum_dr := 0;
sum_cr := 0;
for v1 in c_begin(v.flex_value,
kmcc,
vs.flex_value,
w.period_name) loop
amount := amount + v1.begin_balance;
end loop;
if i = 0 then
for v3 in c_sum(v.flex_value, kmcc, null, start_period) loop
amount_dr := amount_dr + v3.period_net_dr;
amount_cr := amount_cr + v3.period_net_cr;
end loop;
i := i + 1;
output('<TABLE>');
output('<TR><TR><P>');
output('<TR>' || '<TD>会计科目: </TD>' ||
'<TD>' || v.flex_value || '（' || v.DESCRIPTION ||
'）</TD>' || '<TD>辅助科目: </TD>' ||
'<TD>' || vs.flex_value || '（' || vs.DESCRIPTION ||
'）</TD>' || '<TR><P>');
output('<TR>' || '<TD>起止会计期间: </TD>' ||
'<TD>' || start_period || '至' || end_period ||
'</TD>' || '<TR><P>');
output('</TABLE>');
output('<TABLE BORDER=1>');
output('<TR>' || '<TD align=center><B>日期</B></TD>' ||
'<TD align=center><B>凭证号 </TH>' ||
'<TD align=center><B>日记帐名称 </TH>' ||
'<TD align=center><B>过帐状态</B></TD>' ||
'<TD align=center><B>摘要</B></TD>' ||
'<TD align=center><B>对应科目</B></TD>' ||
'<TD align=center><B>借方</B></TD>' ||
'<TD align=center><B>贷方</B></TD>' ||
'<TD align=center><B>余额</B></TD>' || '</TR>');
output('<TR>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || '期初余额' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || ' ' || '</TD>');
output('<TD>' || to_char(amount, '999G999G999G999G990D99') ||
'</TD>');
output('</TR>');
end if;
for v2 in c_mxz(v.flex_value,
vs.flex_value,
w.period_name,
kmcc) loop
j := j + 1;
amount := amount + nvl(v2.entered_dr, 0) -
nvl(v2.entered_cr, 0);
sum_dr := sum_dr + nvl(v2.entered_dr, 0);
sum_cr := sum_cr + nvl(v2.entered_cr, 0);
output('<TR>');
output('<TD>' ||
nvl(to_char(v2.default_effective_date, 'YYYY-MON-DD'),
' ') || '</TD>');
output('<TD align=RIGHT>' ||
nvl(to_char(v2.doc_sequence_value), ' ') ||
'</TD>');
output('<TD>' || nvl(v2.name, ' ') || '</TD>');
output('<TD>' || nvl(v2.status, ' ') || '</TD>');
output('<TD>' || nvl(v2.description, ' ') || '</TD>');
output('<TD>' || nvl(v2.concatenated_segments, ' ') ||
'</TD>');
output('<TD>' ||
nvl(to_char(v2.entered_dr, '999G999G999G999G990D99'),
' ') || '</TD>');
output('<TD>' ||
nvl(to_char(v2.entered_cr, '999G999G999G999G990D99'),
' ') || '</TD>');
output('<TD>' ||
nvl(to_char(amount, '999G999G999G999G990D99'),
' ') || '</TD>');
output('</TR>');
end loop;