Post date: Jun 05, 2013 4:38:49 AM
SELECT gcc.segment2, gp.period_name, gp.period_name period_name2,
(NVL (begin_balance_dr, 0) - NVL (begin_balance_cr, 0)
) begin_balance,
( NVL (begin_balance_dr, 0)
- NVL (begin_balance_cr, 0)
+ NVL (eb.selisih_db_cr, 0)
) ending_balance,
gp.period_year,
NVL (src.user_je_source_name, '**************') SOURCE,
NVL (cat.user_je_category_name, '**************') CATEGORY,
gjl.period_name period_name, gjl.effective_date effective_date,
gjl.description description, seq.NAME sequence_name,
NVL (gjl.accounted_dr, 0) accounted_dr,
NVL (gjl.accounted_cr, 0) accounted_cr,
NVL (gjl.accounted_dr, gjl.accounted_cr) amount,
DECODE (gjl.accounted_cr,
NULL, ' ',
'CR'
) "CR\DR"
FROM gl_code_combinations gcc,
gl_balances gb,
gl_periods gp,
gl_je_headers gjh,
gl_je_lines gjl,
gl_je_sources src,
fnd_document_sequences seq,
gl_je_categories cat,
(SELECT gcc.segment2, gjl.period_name, gjl.ledger_id,
( NVL (SUM (NVL (gjl.accounted_dr, 0)), 0)
- NVL (SUM (NVL (gjl.accounted_cr, 0)), 0)
) selisih_db_cr
FROM gl_je_lines gjl,
gl_code_combinations gcc
WHERE gcc.code_combination_id = gjl.code_combination_id
GROUP BY gcc.segment2,
gjl.period_name,
gjl.ledger_id) eb
WHERE 1 = 1
AND gjl.je_header_id = gjh.je_header_id
AND gp.period_set_name = 'RSPI Calendar'
AND gjh.period_name = gp.period_name
AND src.je_source_name = gjh.je_source
AND seq.doc_sequence_id(+) = gjh.doc_sequence_id
AND cat.je_category_name = gjh.je_category
AND gjl.status = 'P'
AND gjh.actual_flag = :p_actual_flag
AND ( ( NVL (accounted_dr, 0) != 0
OR NVL (accounted_cr, 0) != 0)
OR ( NVL (accounted_dr, 0) = 0
AND NVL (accounted_cr, 0) = 0
AND stat_amount IS NOT NULL
)
)
AND gcc.code_combination_id = gjl.code_combination_id
AND gcc.segment2 BETWEEN :p_account1 AND :p_account2
AND gcc.code_combination_id = gb.code_combination_id
AND gp.period_set_name = 'RSPI Calendar'
AND gb.period_name = gp.period_name
AND gp.period_year = :p_tahun
AND gb.ledger_id = :p_ledger_id
AND gp.period_num BETWEEN :p_period1 AND :p_period2
AND eb.segment2 = gcc.segment2
AND eb.ledger_id = gb.ledger_id
AND eb.period_name = gb.period_name
ORDER BY gcc.segment2,
gp.period_num,
effective_date;
Query above similar like account analysis, it has beginning balance, ending balance, debit and credit mutation, period from and to and account from and to.
Query above is made by Albert Johannes and modified by Raisa Fatrin Pudiyanto and used by Mr Adi Priyo in RSPI for tax need especially for audit.
Below is the procedure created:
CREATE OR REPLACE PROCEDURE APPS.xrspi_gl_seg2 (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_tahun IN VARCHAR2,
p_periode1 IN VARCHAR2,
p_periode2 IN VARCHAR2,
p_ledger_id IN VARCHAR2,
p_account1 IN VARCHAR2,
p_account2 IN VARCHAR2,
p_actual_flag IN VARCHAR2,
p_segment1 IN VARCHAR2
)
AS
v_ledger VARCHAR2 (150);
v_period1 NUMBER;
v_period2 NUMBER;
v_company VARCHAR2 (150);
v_bal_type VARCHAR2 (50);
v_end_balance NUMBER;
v_sum_dr NUMBER;
v_sum_cr NUMBER;
v_error VARCHAR2 (32000);
BEGIN
BEGIN
SELECT NAME
INTO v_ledger
FROM gl_ledgers gl
WHERE 1 = 1
AND gl.ledger_id = p_ledger_id;
EXCEPTION
WHEN OTHERS
THEN
v_ledger := '';
v_error := v_error || ' Ledger Error : '
|| SQLERRM;
END;
BEGIN
SELECT period_num
INTO v_period1
FROM gl_periods
WHERE period_name = p_periode1;
EXCEPTION
WHEN OTHERS
THEN
v_period1 := 0;
END;
BEGIN
SELECT period_num
INTO v_period2
FROM gl_periods
WHERE period_name = p_periode2;
EXCEPTION
WHEN OTHERS
THEN
v_period2 := 0;
END;
BEGIN
SELECT p_segment1 || ' ' || ffvt.description
INTO v_company
FROM fnd_flex_value_sets ffvs,
fnd_flex_values ffv,
fnd_flex_values_tl ffvt
WHERE 1 = 1
AND ffvs.flex_value_set_name = 'RSPI_Company'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.flex_value_meaning = p_segment1;
EXCEPTION
WHEN OTHERS
THEN
v_company := '';
v_error := v_error
|| ' Company Error : '
|| SQLERRM;
END;
BEGIN
SELECT description
INTO v_bal_type
FROM gl_lookups
WHERE 1 = 1
AND lookup_type = 'GL_DSS_BALANCE_TYPE'
AND lookup_code = p_actual_flag;
EXCEPTION
WHEN OTHERS
THEN
v_bal_type := '';
v_error := v_error
|| ' Balance Type Error : '
|| SQLERRM;
END;
fnd_file.put_line (fnd_file.output, 'Print Date : ' || SYSDATE);
fnd_file.put_line (fnd_file.output, 'General Ledger');
fnd_file.put_line (fnd_file.output,
'Period : ' || p_periode1 || ' to ' || p_periode2);
fnd_file.put_line (fnd_file.output, 'Ledger : ' || v_ledger);
fnd_file.put_line (fnd_file.output, 'Company : ' || v_company);
fnd_file.put_line (fnd_file.output, 'Balance Type : ' || v_bal_type);
fnd_file.put_line (fnd_file.output,
'Source'
|| CHR (9)
|| 'Category'
|| CHR (9)
|| 'Name'
|| CHR (9)
|| 'Description'
|| CHR (9)
|| 'Line Item'
|| CHR (9)
|| 'Date'
|| CHR (9)
|| 'Journal Amount');
FOR glh IN (SELECT gcc.segment2, gcc.segment1, gb.ledger_id,
gp.period_num, gp.period_name,
( SUM (NVL (begin_balance_dr, 0))
- SUM (NVL (begin_balance_cr, 0))
) begin_balance
FROM gl_code_combinations gcc,
gl_balances gb,
gl_periods gp
WHERE 1 = 1
AND gcc.segment2 BETWEEN p_account1 AND p_account2
AND gcc.code_combination_id = gb.code_combination_id
AND gp.period_set_name = 'RSPI Calendar'
AND gb.period_name = gp.period_name
AND gp.period_year = p_tahun
AND gb.ledger_id = p_ledger_id
AND gp.period_num BETWEEN v_period1 AND v_period2
AND gcc.segment1 = p_segment1
GROUP BY gcc.segment2,
gcc.segment1,
gb.ledger_id,
gp.period_num,
gp.period_name
ORDER BY gcc.segment2,
gp.period_num)
LOOP
fnd_file.put_line (fnd_file.output,
'Account : '
|| glh.segment2
|| CHR (9)
|| CHR (9)
|| CHR (9)
|| CHR (9)
|| 'Beginning Balance '
|| glh.period_name
|| ' : '
|| CHR (9)
|| CHR (9)
|| glh.begin_balance);
v_sum_dr := 0;
v_sum_cr := 0;
FOR gld IN (SELECT NVL (src.user_je_source_name,
'**************') SOURCE,
NVL (cat.user_je_category_name,
'**************') CATEGORY,
gjh.NAME NAME, gjl.code_combination_id ccid,
gjl.period_name period_name,
gjl.effective_date effective_date,
gjl.description description,
seq.NAME sequence_name,
gjh.doc_sequence_value document_number,
gjl.ledger_id ledger_id,
NVL (gjl.accounted_dr, 0) accounted_dr,
NVL (gjl.accounted_cr, 0) accounted_cr,
NVL (gjl.accounted_dr, gjl.accounted_cr) amount,
DECODE (gjl.accounted_cr,
NULL, ' ',
'CR'
) "CR\DR"
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_periods gp,
gl_je_sources src,
fnd_document_sequences seq,
gl_je_categories cat,
gl_code_combinations gcc
WHERE 1 = 1
AND gjl.je_header_id = gjh.je_header_id
AND gp.period_set_name = 'RSPI Calendar'
AND gjh.period_name = gp.period_name
AND src.je_source_name = gjh.je_source
AND seq.doc_sequence_id(+) = gjh.doc_sequence_id
AND cat.je_category_name = gjh.je_category
AND gjl.status = 'P'
AND gjh.actual_flag = p_actual_flag
AND ( ( NVL (accounted_dr, 0) != 0
OR NVL (accounted_cr, 0) != 0
)
OR ( NVL (accounted_dr, 0) = 0
AND NVL (accounted_cr, 0) = 0
AND stat_amount IS NOT NULL
)
)
AND gp.period_name = glh.period_name
AND gjl.ledger_id = glh.ledger_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment2 = glh.segment2
ORDER BY gjl.effective_date)
LOOP
fnd_file.put_line (fnd_file.output,
gld.SOURCE
|| CHR (9)
|| gld.CATEGORY
|| CHR (9)
|| gld.NAME
|| CHR (9)
|| gld.description
|| CHR (9)
|| gld.document_number
|| CHR (9)
|| gld.effective_date
|| CHR (9)
|| gld.amount
|| CHR (9)
|| gld."CR\DR");
v_sum_dr := v_sum_dr + gld.accounted_dr;
v_sum_cr := v_sum_cr + gld.accounted_cr;
END LOOP;
v_end_balance := glh.begin_balance + v_sum_dr - v_sum_cr;
fnd_file.put_line (fnd_file.output,
CHR (9)
|| CHR (9)
|| CHR (9)
|| CHR (9)
|| 'Ending Balance '
|| glh.period_name
|| ' : '
|| CHR (9)
|| CHR (9)
|| v_end_balance);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
v_error := 'XRSPI_GL_SEG2 Report Error : '
|| SQLERRM
|| ' '
|| v_error;
fnd_file.put_line (fnd_file.output, v_error);
END;
/