Query for General Ledger (180 Char)

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;

/