Procedure
------------------------------
CREATE OR REPLACE PROCEDURE xx_gl_trail_balance_pro (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_access_set_id IN NUMBER,
p_ledger_name IN VARCHAR2,
p_ledger_id IN NUMBER,
p_chart_of_accounts_id IN NUMBER,
p_ledger_currency IN VARCHAR2,
p_currency_type IN VARCHAR2,
p_entered_currency IN VARCHAR2,
p_pagebreak_segment IN VARCHAR2,
p_pagebreak_segment_low IN VARCHAR2,
p_pagebreak_segment_high IN VARCHAR2,
p_period_name IN VARCHAR2,
p_amount_type IN VARCHAR2
)
IS
v_concatenated_segments VARCHAR2 (155);
v_segment4 VARCHAR2 (25);
v_end_balance NUMBER;
v_message VARCHAR2 (1000);
v_year NUMBER;
CURSOR account_cur
IS
SELECT GLB.code_combination_id, GLB.period_year,DECODE (p_pagebreak_segment,
1, gcck.segment1,
2, gcck.segment2,
3, gcck.segment3,
4, gcck.segment5,
5, gcck.segment6
) "segment",
gcck.concatenated_segments, gcck.segment4,
REPLACE (ffvt.description, '&', '&') description
FROM gl_balances GLB,
gl_code_combinations_kfv gcck,
fnd_flex_values_tl ffvt,
fnd_flex_values ffvs
WHERE GLB.period_name = p_period_name
AND GLB.actual_flag = 'A'
AND gcck.summary_flag = 'N'
AND gcck.template_id IS NULL
AND GLB.code_combination_id = gcck.code_combination_id
AND GLB.ledger_id = p_ledger_id
AND GLB.currency_code = p_ledger_currency
AND ffvs.FLEX_VALUE_SET_ID=1009939
AND ffvs.FLEX_VALUE_ID=ffvt.FLEX_VALUE_ID
AND ffvs.flex_value= gcck.segment4
AND ffvt.flex_value_meaning = gcck.segment4
AND DECODE (p_pagebreak_segment,
1, gcck.segment1,
2, gcck.segment2,
3, gcck.segment3,
4, gcck.segment5,
5, gcck.segment6
) BETWEEN p_pagebreak_segment_low
AND p_pagebreak_segment_high
AND NVL ( GLB.begin_balance_dr
+ GLB.period_net_dr
- GLB.begin_balance_cr
- GLB.period_net_cr,
0
) <> 0
ORDER BY 3,5,4;--gcck.segment4, gcck.concatenated_segments;
CURSOR period_cur
IS
SELECT period_name, period_num, DECODE (period_num, 16, 1) "name"
FROM gl_periods
WHERE period_name LIKE 'MAR%ADJ%' AND period_year = v_year - 1
UNION
SELECT DISTINCT period_name, period_num,
DECODE (period_num, 16, 1) "name"
FROM gl_periods
WHERE period_year = v_year
AND period_num <= (SELECT period_num
FROM gl_periods
WHERE period_name = p_period_name)
ORDER BY 3, 2;
BEGIN
fnd_file.put_line (fnd_file.output, '<?xml version="1.0"?>');
fnd_file.put_line (fnd_file.output, '<P_MONNAME>');
FOR a_cur IN account_cur
LOOP
fnd_file.put_line (fnd_file.output, '<G_ACCNUM>');
v_year := a_cur.period_year;
fnd_file.put_line (fnd_file.output,
'<account>' || a_cur.segment4 || '</account>'
);
fnd_file.put_line (fnd_file.output,
'<DESCRIPTION>'
|| a_cur.description
|| '</DESCRIPTION>'
);
fnd_file.put_line (fnd_file.output,
'<CONCATENATED_SEGMENTS>'
|| a_cur.concatenated_segments
|| '</CONCATENATED_SEGMENTS>'
);
fnd_file.put_line (fnd_file.output,
'<CODE_COMBINATION>'
|| a_cur.code_combination_id
|| '</CODE_COMBINATION>'
);
FOR p_cur IN period_cur
LOOP
BEGIN
SELECT NVL (begin_balance_dr, 0)
+ NVL (period_net_dr, 0)
- NVL (begin_balance_cr, 0)
- NVL (period_net_cr, 0)
INTO v_end_balance
FROM gl_balances
WHERE code_combination_id = a_cur.code_combination_id
AND period_name = p_cur.period_name
AND ledger_id = p_ledger_id
AND currency_code = p_ledger_currency;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_end_balance := 0.00;
WHEN OTHERS
THEN
v_message := SQLERRM;
END;
fnd_file.put_line (fnd_file.output, '<G_PERIOD width="20">');
fnd_file.put_line (fnd_file.output,
'<MONNAME>' || p_cur.period_name || '</MONNAME>'
);
fnd_file.put_line (fnd_file.output,
'<ENDBAL>' || v_end_balance || '</ENDBAL>'
);
fnd_file.put_line (fnd_file.output, '</G_PERIOD>');
END LOOP;
fnd_file.put_line (fnd_file.output, '</G_ACCNUM>');
END LOOP;
fnd_file.put_line (fnd_file.output, '</P_MONNAME>');
------------ ************ Testing ******** --------------------
--nvl(sum(begin_balance_dr - begin_balance_cr),0) "Begin Balance",
-- nvl(sum(period_net_dr),0) - nvl(sum(period_net_cr),0) "Period Activity" ,
--NVL (BEGIN_BALANCE_DR, 0)+ NVL (PERIOD_NET_DR, 0)- NVL (BEGIN_BALANCE_CR, 0) - NVL (PERIOD_NET_CR, 0)
-- DECODE(:P_TYPE,'PTD',
-- SUM (DECODE (:P_CURRENCY_TYPE,'T',
-- NVL (BEGIN_BALANCE_DR, 0)+ NVL (PERIOD_NET_DR, 0)- NVL (BEGIN_BALANCE_CR, 0) - NVL (PERIOD_NET_CR, 0),
-- 'S', NVL (BEGIN_BALANCE_DR, 0)+ NVL (PERIOD_NET_DR, 0) - NVL (BEGIN_BALANCE_CR, 0) - NVL (PERIOD_NET_CR, 0),
-- 'E', DECODE (GLB.TRANSLATED_FLAG, 'R',
-- NVL (BEGIN_BALANCE_DR, 0) + NVL (PERIOD_NET_DR, 0)- NVL (BEGIN_BALANCE_CR, 0) - NVL (PERIOD_NET_CR, 0),NVL (BEGIN_BALANCE_DR_BEQ, 0) + NVL (PERIOD_NET_DR_BEQ, 0)- NVL (BEGIN_BALANCE_CR_BEQ, 0) - NVL (PERIOD_NET_CR_BEQ, 0) ), 'C', NVL (BEGIN_BALANCE_DR_BEQ, 0)+ NVL (PERIOD_NET_DR_BEQ, 0)- NVL (BEGIN_BALANCE_CR_BEQ, 0) - NVL (PERIOD_NET_CR_BEQ, 0))),'PJTD', SUM (DECODE (:P_CURRENCY_TYPE, 'T', NVL (PROJECT_TO_DATE_DR, 0)+ NVL (PERIOD_NET_DR, 0) - NVL (PROJECT_TO_DATE_CR, 0) - NVL (PERIOD_NET_CR, 0), 'S', NVL (PROJECT_TO_DATE_DR, 0) + NVL (PERIOD_NET_DR, 0) - NVL (PROJECT_TO_DATE_CR, 0) - NVL (PERIOD_NET_CR, 0), 'E', DECODE (GLB.TRANSLATED_FLAG,'R', NVL (PROJECT_TO_DATE_DR, 0) + NVL (PERIOD_NET_DR, 0)- NVL (PROJECT_TO_DATE_CR, 0) - NVL (PERIOD_NET_CR, 0), NVL (PROJECT_TO_DATE_DR_BEQ, 0) + NVL (PERIOD_NET_DR_BEQ, 0) - NVL (PROJECT_TO_DATE_CR_BEQ, 0) - NVL (PERIOD_NET_CR_BEQ, 0) ), 'C', NVL (PROJECT_TO_DATE_DR_BEQ, 0) + NVL (PERIOD_NET_DR_BEQ, 0) - NVL (PROJECT_TO_DATE_CR_BEQ, 0) - NVL (PERIOD_NET_CR_BEQ, 0)) ), 'YTD', DECODE (:P_CURRENCY_TYPE,'T', SUM (DECODE (GLB.PERIOD_NAME, :P_PERIOD_NAME, NVL(PERIOD_NET_DR,0 ) - NVL (PERIOD_NET_CR, 0) + NVL (BEGIN_BALANCE_DR, 0) - NVL (BEGIN_BALANCE_CR, 0), 0)), 'S', SUM (DECODE (GLB.PERIOD_NAME, :P_PERIOD_NAME, NVL (PERIOD_NET_DR,0)- NVL (PERIOD_NET_CR, 0) + NVL (BEGIN_BALANCE_DR, 0)- NVL (BEGIN_BALANCE_CR, 0),0)), 'E', SUM (DECODE (GLB.TRANSLATED_FLAG, 'R', DECODE(GLB.PERIOD_NAME, :P_PERIOD_NAME, NVL(PERIOD_NET_DR,0)- NVL (PERIOD_NET_CR, 0)+ NVL (BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0 ), 0 ), DECODE (GLB.PERIOD_NAME, :P_PERIOD_NAME, NVL(PERIOD_NET_DR_BEQ, 0 ) - NVL (PERIOD_NET_CR_BEQ, 0)+ NVL(BEGIN_BALANCE_DR_BEQ, 0)- NVL (BEGIN_BALANCE_CR_BEQ,0), 0) ) ),'C', SUM (DECODE (GLB.PERIOD_NAME,:P_PERIOD_NAME, NVL(PERIOD_NET_DR_BEQ, 0) - NVL (PERIOD_NET_CR_BEQ, 0) + NVL (BEGIN_BALANCE_DR_BEQ, 0) - NVL (BEGIN_BALANCE_CR_BEQ, 0),0)))) END_BAL_H
-----------******* Tesing End **********-------------------
END;
RTF
--------
Account
|
Description
|
Account
|
||
width
|
Width----- <?for-each:G_ACCNUM?> <?account?>
Description-----<?DESCRIPTION?>
Account --------<?CONCATENATED_SEGMENTS?>
MONNAME------
<?split-column-header:G_PERIOD?>
<?split-column-width:@width?>
<?MONNAME?>
0.00--------<?split-column-data:G_PERIOD?> <?ENDBAL?> <?end?>
No comments:
Post a Comment