GL Report - GL,AP,AR /Excel
Parameter :
- Ledger Name
- BU
- Account
- Division
- From Date
- To Date
SELECT ac.*,
Nvl(ac.opening_balance, 0)
+ SUM(net_amount) over (PARTITION BY account ORDER BY
account, ac.row_num)
RUNNING_CLOSING,
CASE
WHEN Nvl(ac.opening_balance, 0)
+ SUM(Nvl(ac.amount_dr, 0) - Nvl(amount_cr, 0)) over (PARTITION BY
account ORDER
BY ledger_name, bu_code, division, account) > 0 THEN 'Dr'
ELSE 'Cr'
END balanceindicator
FROM (SELECT bb.*,
Nvl((SELECT SUM(Nvl (( Nvl(gjl.accounted_dr, 0) -
Nvl(gjl.accounted_dr, 0) ), 0)
)
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations GCC
WHERE 1 = 1
AND GCC.code_combination_id = gjl.code_combination_id
AND gjl.ledger_id = bb.ledger_id
AND gcc.segment3 = bb.account
AND gjh.status = 'P'
AND gjl.je_header_id = gjh.je_header_id
AND gjl.effective_date <= :p_from_date), 0)
opening_balance,
Row_number()
over (
PARTITION BY bb.account
ORDER BY bb.account, bb.transaction_date
, bb.transaction_number) AS
ROW_NUM
FROM (SELECT ledger_id,
ledger_name,
legal_entity,
account_description,
bu_code,
bu_nmae,
account,
division,
je_category_desc,
transaction_number,
transaction_date,
party_number,
party_name,
bill_no,
bill_date,
remarks,
SUM(amount_dr) amount_dr,
SUM(amount_cr) amount_cr,
SUM(net_amount) net_amount
FROM (SELECT 'Q1'
query,
gjl.ledger_id,
gl.name
ledger_name,
xe.name
legal_entity,
gl_flexfields_pkg.Get_description_sql (
gcc.chart_of_accounts_id,
3,
gcc.segment3
)
account_description,
gcc.segment1
bu_code,
gl_flexfields_pkg.Get_description_sql (
gcc.chart_of_accounts_id,
1
, gcc.segment1
)
bu_nmae,
gcc.segment3
account,
gcc.segment6
division
,
gjc.je_category_name
je_category_desc
,
gjh.name
transaction_number,
To_char(gjh.date_created, 'DD-MON-RR')
transaction_date
,
NULL
party_number,
NULL
party_name,
NULL
bill_no,
To_char(gjh.date_created, 'DD-MON-RR')
bill_date,
gjl.description
remarks,
Nvl(gjl.accounted_dr, 0)
amount_dr,
Nvl(gjl.accounted_cr, 0)
amount_cr,
( Nvl(gjl.accounted_dr, 0) -
Nvl(gjl.accounted_cr, 0) )
net_amount
FROM gl_code_combinations gcc,
gl_ledgers gl,
gl_je_lines gjl,
gl_je_headers gjh,
gl_je_categories gjc,
gl_je_sources gjs,
xle_entity_profiles xe
WHERE 1 = 1
AND gjl.code_combination_id =
gcc.code_combination_id
AND gl.ledger_id = gjl.ledger_id
AND gjl.je_header_id = gjh.je_header_id
AND gjh.je_category = gjc.je_category_name
AND gjh.je_source = gjs.je_source_name
AND gjh.legal_entity_id = xe.legal_entity_id(+)
AND gjs.je_source_name IN ( 'Manual' )
AND gjh.status = 'P'
--AND gjh.NAME = 'Journal GL 01'
---------------------- PARAMETERS ---------------------
AND ( gl.name IN ( :p_led )
OR Coalesce(:p_led, NULL) IS NULL )
AND ( gcc.segment1 IN ( :p_bu )
OR Coalesce(:p_bu, NULL) IS NULL )
--AND ( xe.name IN (:P_LEGAL_ENTITY)
-- OR COALESCE(:P_LEGAL_ENTITY,NULL) IS NULL)
AND ( gcc.segment3 IN ( :p_account )
OR Coalesce(:p_account, NULL) IS NULL )
AND ( gcc.segment6 IN ( :p_division )
OR Coalesce(:p_division, NULL) IS NULL )
AND ( gjl.effective_date >= :p_from_date
OR :p_from_date IS NULL )
AND ( gjl.effective_date <= :p_to_date
OR :p_to_date IS NULL )
UNION
SELECT 'Q2'
query,
gjl.ledger_id,
gl.name
ledger_name,
xe.name
legal_entity
,
gl_flexfields_pkg.Get_description_sql (
gcc.chart_of_accounts_id, 3,
gcc.segment3
)
account_description,
gcc.segment1
bu_code,
gl_flexfields_pkg.Get_description_sql (
gcc.chart_of_accounts_id,
1
, gcc.segment1
)
bu_nmae,
gcc.segment3
account,
gcc.segment6
division,
gjc.je_category_name
je_category_desc,
rcta.trx_number
transaction_number,
To_char(rcta.trx_date, 'DD-MON-RR')
transaction_date,
hp.party_number
party_Number
,
hp.party_name
PartyName,
rcta.trx_number
bill_no,
To_char(rcta.trx_date, 'DD-MON-RR')
bill_date,
gjl.description
remarks,
Nvl(gjl.accounted_dr, 0)
amount_dr,
Nvl(gjl.accounted_cr, 0)
amount_cr,
Nvl(gjl.accounted_dr, 0) -
Nvl(gjl.accounted_cr, 0)
net_amount
FROM ra_customer_trx_all rcta,
hz_cust_accounts hcaa,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
hz_parties hp,
hz_party_sites hps,
xla_transaction_entities xte,
xla_events xev,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gir,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_je_categories gjc,
gl_je_sources gjs,
gl_ledgers gl,
xle_entity_profiles xe,
hr_operating_units hou
WHERE 1 = 1
AND gjl.code_combination_id =
gcc.code_combination_id
AND gl.ledger_id = gjl.ledger_id
AND rcta.bill_to_customer_id =
hcaa.cust_account_id
AND hcaa.party_id = hp.party_id
AND hcaa.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id =
hcsua.cust_acct_site_id
AND hcsua.site_use_id = rcta.bill_to_site_use_id
AND hcasa.party_site_id = hps.party_site_id
AND rcta.customer_trx_id = xte.source_id_int_1
AND xte.application_id = 222
AND xev.entity_id = xte.entity_id
AND xah.entity_id = xte.entity_id
AND xah.event_id = xev.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gjl.je_header_id = gjh.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjh.je_category = gjc.je_category_name
AND gjh.je_source = gjs.je_source_name
AND GJC.je_category_name = 'Sales Invoices'
AND gjs.je_source_name = 'Receivables'
AND ( gjl.accounted_cr <> 0
OR gjl.accounted_dr <> 0 )
AND ( xal.accounted_cr <> 0
OR xal.accounted_dr <> 0 )
AND gjh.status = 'P'
AND gjh.legal_entity_id = xe.legal_entity_id
AND rcta.org_id = hou.organization_id
-- AND RCTA.trx_number = '211000010000090'
---------------------- PARAMETERS ---------------------
AND ( gl.name IN ( :p_led )
OR Coalesce(:p_led, NULL) IS NULL )
AND ( gcc.segment1 IN ( :p_bu )
OR Coalesce(:p_bu, NULL) IS NULL )
AND ( xe.name IN ( :P_LEGAL_ENTITY )
OR Coalesce(:P_LEGAL_ENTITY, NULL) IS NULL
)
AND ( gcc.segment3 IN ( :p_account )
OR Coalesce(:p_account, NULL) IS NULL )
AND ( gcc.segment6 IN ( :p_division )
OR Coalesce(:p_division, NULL) IS NULL )
AND ( gjl.effective_date >= :p_from_date
OR :p_from_date IS NULL )
AND ( gjl.effective_date <= :p_to_date
OR :p_to_date IS NULL )) aa
GROUP BY ledger_id,
ledger_name,
legal_entity,
account_description,
bu_code,
bu_nmae,
account,
division,
je_category_desc,
transaction_number,
transaction_date,
party_number,
party_name,
bill_no,
bill_date,
remarks) bb) ac
Nvl(ac.opening_balance, 0)
+ SUM(net_amount) over (PARTITION BY account ORDER BY
account, ac.row_num)
RUNNING_CLOSING,
CASE
WHEN Nvl(ac.opening_balance, 0)
+ SUM(Nvl(ac.amount_dr, 0) - Nvl(amount_cr, 0)) over (PARTITION BY
account ORDER
BY ledger_name, bu_code, division, account) > 0 THEN 'Dr'
ELSE 'Cr'
END balanceindicator
FROM (SELECT bb.*,
Nvl((SELECT SUM(Nvl (( Nvl(gjl.accounted_dr, 0) -
Nvl(gjl.accounted_dr, 0) ), 0)
)
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations GCC
WHERE 1 = 1
AND GCC.code_combination_id = gjl.code_combination_id
AND gjl.ledger_id = bb.ledger_id
AND gcc.segment3 = bb.account
AND gjh.status = 'P'
AND gjl.je_header_id = gjh.je_header_id
AND gjl.effective_date <= :p_from_date), 0)
opening_balance,
Row_number()
over (
PARTITION BY bb.account
ORDER BY bb.account, bb.transaction_date
, bb.transaction_number) AS
ROW_NUM
FROM (SELECT ledger_id,
ledger_name,
legal_entity,
account_description,
bu_code,
bu_nmae,
account,
division,
je_category_desc,
transaction_number,
transaction_date,
party_number,
party_name,
bill_no,
bill_date,
remarks,
SUM(amount_dr) amount_dr,
SUM(amount_cr) amount_cr,
SUM(net_amount) net_amount
FROM (SELECT 'Q1'
query,
gjl.ledger_id,
gl.name
ledger_name,
xe.name
legal_entity,
gl_flexfields_pkg.Get_description_sql (
gcc.chart_of_accounts_id,
3,
gcc.segment3
)
account_description,
gcc.segment1
bu_code,
gl_flexfields_pkg.Get_description_sql (
gcc.chart_of_accounts_id,
1
, gcc.segment1
)
bu_nmae,
gcc.segment3
account,
gcc.segment6
division
,
gjc.je_category_name
je_category_desc
,
gjh.name
transaction_number,
To_char(gjh.date_created, 'DD-MON-RR')
transaction_date
,
NULL
party_number,
NULL
party_name,
NULL
bill_no,
To_char(gjh.date_created, 'DD-MON-RR')
bill_date,
gjl.description
remarks,
Nvl(gjl.accounted_dr, 0)
amount_dr,
Nvl(gjl.accounted_cr, 0)
amount_cr,
( Nvl(gjl.accounted_dr, 0) -
Nvl(gjl.accounted_cr, 0) )
net_amount
FROM gl_code_combinations gcc,
gl_ledgers gl,
gl_je_lines gjl,
gl_je_headers gjh,
gl_je_categories gjc,
gl_je_sources gjs,
xle_entity_profiles xe
WHERE 1 = 1
AND gjl.code_combination_id =
gcc.code_combination_id
AND gl.ledger_id = gjl.ledger_id
AND gjl.je_header_id = gjh.je_header_id
AND gjh.je_category = gjc.je_category_name
AND gjh.je_source = gjs.je_source_name
AND gjh.legal_entity_id = xe.legal_entity_id(+)
AND gjs.je_source_name IN ( 'Manual' )
AND gjh.status = 'P'
--AND gjh.NAME = 'Journal GL 01'
---------------------- PARAMETERS ---------------------
AND ( gl.name IN ( :p_led )
OR Coalesce(:p_led, NULL) IS NULL )
AND ( gcc.segment1 IN ( :p_bu )
OR Coalesce(:p_bu, NULL) IS NULL )
--AND ( xe.name IN (:P_LEGAL_ENTITY)
-- OR COALESCE(:P_LEGAL_ENTITY,NULL) IS NULL)
AND ( gcc.segment3 IN ( :p_account )
OR Coalesce(:p_account, NULL) IS NULL )
AND ( gcc.segment6 IN ( :p_division )
OR Coalesce(:p_division, NULL) IS NULL )
AND ( gjl.effective_date >= :p_from_date
OR :p_from_date IS NULL )
AND ( gjl.effective_date <= :p_to_date
OR :p_to_date IS NULL )
UNION
SELECT 'Q2'
query,
gjl.ledger_id,
gl.name
ledger_name,
xe.name
legal_entity
,
gl_flexfields_pkg.Get_description_sql (
gcc.chart_of_accounts_id, 3,
gcc.segment3
)
account_description,
gcc.segment1
bu_code,
gl_flexfields_pkg.Get_description_sql (
gcc.chart_of_accounts_id,
1
, gcc.segment1
)
bu_nmae,
gcc.segment3
account,
gcc.segment6
division,
gjc.je_category_name
je_category_desc,
rcta.trx_number
transaction_number,
To_char(rcta.trx_date, 'DD-MON-RR')
transaction_date,
hp.party_number
party_Number
,
hp.party_name
PartyName,
rcta.trx_number
bill_no,
To_char(rcta.trx_date, 'DD-MON-RR')
bill_date,
gjl.description
remarks,
Nvl(gjl.accounted_dr, 0)
amount_dr,
Nvl(gjl.accounted_cr, 0)
amount_cr,
Nvl(gjl.accounted_dr, 0) -
Nvl(gjl.accounted_cr, 0)
net_amount
FROM ra_customer_trx_all rcta,
hz_cust_accounts hcaa,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
hz_parties hp,
hz_party_sites hps,
xla_transaction_entities xte,
xla_events xev,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gir,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_je_categories gjc,
gl_je_sources gjs,
gl_ledgers gl,
xle_entity_profiles xe,
hr_operating_units hou
WHERE 1 = 1
AND gjl.code_combination_id =
gcc.code_combination_id
AND gl.ledger_id = gjl.ledger_id
AND rcta.bill_to_customer_id =
hcaa.cust_account_id
AND hcaa.party_id = hp.party_id
AND hcaa.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id =
hcsua.cust_acct_site_id
AND hcsua.site_use_id = rcta.bill_to_site_use_id
AND hcasa.party_site_id = hps.party_site_id
AND rcta.customer_trx_id = xte.source_id_int_1
AND xte.application_id = 222
AND xev.entity_id = xte.entity_id
AND xah.entity_id = xte.entity_id
AND xah.event_id = xev.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gjl.je_header_id = gjh.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjh.je_category = gjc.je_category_name
AND gjh.je_source = gjs.je_source_name
AND GJC.je_category_name = 'Sales Invoices'
AND gjs.je_source_name = 'Receivables'
AND ( gjl.accounted_cr <> 0
OR gjl.accounted_dr <> 0 )
AND ( xal.accounted_cr <> 0
OR xal.accounted_dr <> 0 )
AND gjh.status = 'P'
AND gjh.legal_entity_id = xe.legal_entity_id
AND rcta.org_id = hou.organization_id
-- AND RCTA.trx_number = '211000010000090'
---------------------- PARAMETERS ---------------------
AND ( gl.name IN ( :p_led )
OR Coalesce(:p_led, NULL) IS NULL )
AND ( gcc.segment1 IN ( :p_bu )
OR Coalesce(:p_bu, NULL) IS NULL )
AND ( xe.name IN ( :P_LEGAL_ENTITY )
OR Coalesce(:P_LEGAL_ENTITY, NULL) IS NULL
)
AND ( gcc.segment3 IN ( :p_account )
OR Coalesce(:p_account, NULL) IS NULL )
AND ( gcc.segment6 IN ( :p_division )
OR Coalesce(:p_division, NULL) IS NULL )
AND ( gjl.effective_date >= :p_from_date
OR :p_from_date IS NULL )
AND ( gjl.effective_date <= :p_to_date
OR :p_to_date IS NULL )) aa
GROUP BY ledger_id,
ledger_name,
legal_entity,
account_description,
bu_code,
bu_nmae,
account,
division,
je_category_desc,
transaction_number,
transaction_date,
party_number,
party_name,
bill_no,
bill_date,
remarks) bb) ac
No comments:
Post a Comment