-------Account Analysis Query
------------------------------------
SELECT tab1.period_name,
ledger_name,
tab1.ledger_id,
account,
project,
product_group,
department,
employee,
emp_name,
product,
entity,
inter_entity,
spare1,
spare2,
combinations,
je_source,
je_category,
party_name,
party_number,
transaction_type,
transaction_currency,
transaction_number,
transaction_date,
gl_date,
subledger_doc_no,
doc_sequence_value,
transaction_description,
entered_currency,
entered_dr,
entered_cr,
accounted_currency,
accounted_dr,
accounted_cr,
tab1.code_combination_id,
xx.open_balance open_balance,
xx.sum_debit sum_debit,
xx.sum_credit sum_credit,
xx.closing_balance closing_balance,
gl_cr_date
-- ROWNUM
FROM (SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
pv.vendor_name party_name,
pv.segment1 party_number,
NULL transaction_type,
xal.currency_code transaction_currency,
xte.transaction_number,
TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY')
transaction_date,
xga.gl_date,
gir.subledger_doc_sequence_value subledger_doc_no,
xga.doc_sequence_value,
xal.description transaction_description,
xal.currency_code entered_currency,
NVL (xal.entered_dr, 0) entered_dr,
NVL (xal.entered_cr, 0) entered_cr,
xga.ledger_currency accounted_currency,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_dr, 0),
NVL (xal.accounted_dr, 0))
accounted_dr,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_cr, 0),
NVL (xal.accounted_cr, 0))
accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla.xla_transaction_entities xte,
po_vendors pv
WHERE xga.je_batch_id = gir.je_batch_id
AND xga.je_header_id = gir.je_header_id
AND xal.ledger_id = xah.ledger_id
AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
AND xga.je_header_id = gir.je_header_id
AND xga.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xal.party_id = pv.vendor_id(+)
AND UPPER (xga.je_source) = 'PAYABLES'
UNION ALL
SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
hp.party_name party_name,
hca.account_number party_number,
NULL transaction_type,
xal.currency_code transaction_currency,
xte.transaction_number,
TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY')
transaction_date,
xga.gl_date,
gir.subledger_doc_sequence_value subledger_doc_no,
xga.doc_sequence_value,
xal.description transaction_description,
xal.currency_code entered_currency,
NVL (xal.entered_dr, 0) entered_dr,
NVL (xal.entered_cr, 0) entered_cr,
xga.ledger_currency accounted_currency,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_dr, 0),
NVL (xal.accounted_dr, 0))
accounted_dr,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_cr, 0),
NVL (xal.accounted_cr, 0))
accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla.xla_transaction_entities xte,
hz_cust_accounts hca,
hz_parties hp
WHERE xga.je_batch_id = gir.je_batch_id
AND xga.je_header_id = gir.je_header_id
AND xal.ledger_id = xah.ledger_id
AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
AND xga.je_header_id = gir.je_header_id
AND xga.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xal.party_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
AND UPPER (xga.je_source) = 'RECEIVABLES'
UNION ALL
SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
pv.vendor_name party_name,
pv.segment1 party_number,
rt.transaction_type transaction_type,
rt.currency_code transaction_currency,
rsh.receipt_num transaction_number,
TO_CHAR (rt.transaction_date, 'DD-Mon-YYYY') transaction_date,
xga.gl_date,
NULL subledger_doc_no,
xga.doc_sequence_value,
xal.description transaction_description,
xal.currency_code entered_currency,
NVL (xal.entered_dr, 0) entered_dr,
NVL (xal.entered_cr, 0) entered_cr,
xga.ledger_currency accounted_currency,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_dr, 0),
NVL (xal.accounted_dr, 0))
accounted_dr,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_cr, 0),
NVL (xal.accounted_cr, 0))
accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla.xla_transaction_entities xte,
rcv_transactions rt,
rcv_shipment_headers rsh,
po_vendors pv
WHERE xga.je_batch_id = gir.je_batch_id
AND xga.je_header_id = gir.je_header_id
AND xal.ledger_id = xah.ledger_id
AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
AND xga.je_header_id = gir.je_header_id
AND xga.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xte.source_id_int_1 = rt.transaction_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id(+)
AND rsh.vendor_id = pv.vendor_id(+)
AND UPPER (xga.je_source) = 'COST MANAGEMENT'
UNION ALL
SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
cba.bank_account_name party_name,
cba.bank_account_num party_number,
ccf.cashflow_direction transaction_type,
ccf.cashflow_currency_code transaction_currency,
TO_CHAR (ccf.trxn_reference_number) transaction_number,
TO_CHAR (ccf.cashflow_date, 'DD-Mon-RRRR')
transaction_date,
xga.gl_date,
NULL subledger_doc_no,
xga.doc_sequence_value,
ccf.description transction_description,
xal.currency_code entered_currency,
NVL (xal.entered_dr, 0) entered_dr,
NVL (xal.entered_cr, 0) entered_cr,
xga.ledger_currency accounted_currency,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_dr, 0),
NVL (xal.accounted_dr, 0))
accounted_dr,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_cr, 0),
NVL (xal.accounted_cr, 0))
accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla.xla_transaction_entities xte,
ce_cashflows ccf,
ce_bank_accounts cba
WHERE xga.je_batch_id = gir.je_batch_id
AND xga.je_header_id = gir.je_header_id
AND xal.ledger_id = xah.ledger_id
AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
AND xga.je_header_id = gir.je_header_id
AND xga.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xte.source_id_int_1 = ccf.cashflow_id(+)
AND ccf.cashflow_bank_account_id = cba.bank_account_id(+)
AND UPPER (xga.je_source) = 'CASH MANAGEMENT'
UNION ALL
SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
ppa.name party_name,
NULL party_number,
ppa.project_rate_type transaction_type,
ppa.project_currency_code transaction_currency,
pei.project_number transaction_number,
TO_CHAR (ppa.start_date, 'DD-Mon-RRRR') transaction_date,
xga.gl_date,
gir.subledger_doc_sequence_value subledger_doc_no,
xga.doc_sequence_value,
NVL (pei.expenditure_comment, xal.description)
transction_description,
xal.currency_code entered_currency,
NVL (xal.entered_dr, 0) entered_dr,
NVL (xal.entered_cr, 0) entered_cr,
xga.ledger_currency accounted_currency,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_dr, 0),
NVL (xal.accounted_dr, 0))
accounted_dr,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_cr, 0),
NVL (xal.accounted_cr, 0))
accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla.xla_transaction_entities xte,
pa_expend_items_adjust2_v pei,
pa_projects_all ppa
WHERE xga.je_batch_id = gir.je_batch_id
AND xga.je_header_id = gir.je_header_id
AND xal.ledger_id = xah.ledger_id
AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
AND xga.je_header_id = gir.je_header_id
AND xga.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xte.source_id_int_1 = pei.expenditure_item_id(+)
AND pei.project_id = ppa.project_id(+)
AND UPPER (xga.je_source) = 'PROJECT ACCOUNTING'
UNION ALL
SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
(SELECT pv.vendor_name
FROM apps.fa_invoice_details_v fid, apps.po_vendors pv
WHERE fid.po_vendor_id = pv.vendor_id
AND fid.asset_id = fth.asset_id) party_name,
SELECT pv.segment1
FROM apps.fa_invoice_details_v fid, apps.po_vendors pv
WHERE fid.po_vendor_id = pv.vendor_id
AND fid.asset_id = fth.asset_id) party_number,
fth.transaction_type transaction_type,
xal.currency_code transaction_currency,
fth.asset_number transaction_number,
TO_CHAR (fth.date_effective, 'DD-Mon-RRRR')
transaction_date,
xga.gl_date,
NULL subledger_doc_no,
xga.doc_sequence_value,
fth.asset_number_desc transction_description,
xal.currency_code entered_currency,
NVL (xal.entered_dr, 0) entered_dr,
NVL (xal.entered_cr, 0) entered_cr,
xga.ledger_currency accounted_currency,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_dr, 0),
NVL (xal.accounted_dr, 0))
accounted_dr,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_cr, 0),
NVL (xal.accounted_cr, 0))
accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla_transaction_entities xte,
fa_transaction_history_trx_v fth
WHERE xga.je_batch_id = gir.je_batch_id
AND xga.je_header_id = gir.je_header_id
AND xal.ledger_id = xah.ledger_id
AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
AND xga.je_header_id = gir.je_header_id
AND xga.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xte.source_id_int_1 = fth.transaction_header_id
AND UPPER (xga.je_source) = 'ASSETS'
UNION ALL
SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
SELECT pv.vendor_name
FROM apps.fa_invoice_details_v fid, apps.po_vendors pv
WHERE fid.po_vendor_id = pv.vendor_id
AND fid.asset_id =a.asset_id) party_name, (SELECT pv.segment1
FROM apps.fa_invoice_details_v fid, apps.po_vendors pv
WHERE fid.po_vendor_id = pv.vendor_id
AND fid.asset_id = fa.asset_id) party_number, fa.asset_type transaction_type,
xal.currency_code transaction_currency,
fa.asset_number transaction_number,
TO_CHAR (xe.transaction_date, 'DD-Mon-RRRR') transaction_date,
xga.gl_date,
NULL subledger_doc_no,
xga.doc_sequence_value,
fa.description transction_description,
xal.currency_code entered_currency,
NVL (xal.entered_dr, 0) entered_dr,
NVL (xal.entered_cr, 0) entered_cr,
xga.ledger_currency accounted_currency,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_dr, 0),
NVL (xal.accounted_dr, 0))
accounted_dr,
DECODE (xga.ledger_id,
2425, NVL (xga.accounted_cr, 0),
NVL (xal.accounted_cr, 0))
accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla_transaction_entities xte,
fa_additions fa
WHERE xga.je_batch_id = gir.je_batch_id
AND xga.je_header_id = gir.je_header_id
AND xal.ledger_id = xah.ledger_id
AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
AND xga.je_header_id = gir.je_header_id
AND xga.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
AND xte.source_id_int_1 = fa.asset_id
AND UPPER (xga.je_source) = 'ASSETS'
UNION ALL
SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
ppf.full_name party_name,
ppf.employee_number party_number,
'Payroll' transaction_type,
pgi.currency_code transaction_currency,
TO_CHAR (paf.payroll_id) transaction_number,
xga.reference_4 transaction_date,
xga.gl_date,
paf.payroll_id subledger_doc_no,
xga.doc_sequence_value,
( SELECT DISTINCT pc.element_name
FROM apps.pay_costs_v pc
WHERE pc.cost_allocation_keyflex_id = xga.reference_2)
transction_description,
pgi.currency_code entered_currency,
NVL (pgi.entered_dr, 0) entered_dr,
NVL (pgi.entered_cr, 0) entered_cr,
xga.ledger_currency accounted_currency,
NVL (pgi.entered_dr, 0) accounted_dr,
NVL (pgi.entered_cr, 0) accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga,
pay_assignment_actions paa,
pay_gl_interface pgi,
per_assignments_f paf,
per_people_f ppf,
apps.per_periods_of_service ppos
WHERE xga.accounted_dr != 0
AND pgi.entered_dr != 0
AND xga.reference_1 = paa.payroll_action_id
AND paa.assignment_action_id = pgi.assignment_action_id
AND xga.reference_2 = pgi.cost_allocation_keyflex_id
AND xga.reference_5 = pgi.run_payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND ppf.person_id = paf.person_id
AND ppf.person_id = ppos.person_id
AND ( ( ppos.actual_termination_date IS NOT NULL
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date
AND paf.effective_end_date)
OR ( ppos.actual_termination_date IS NULL
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date))
AND UPPER (xga.je_source) = 'PAYROLL'
UNION ALL
SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
NULL party_name,
NULL party_number,
'Payroll' transaction_type,
NULL transaction_currency,
NULL transaction_number,
xga.reference_4 transaction_date,
xga.gl_date,
NULL subledger_doc_no,
xga.doc_sequence_value,
( SELECT DISTINCT pc.element_name
FROM apps.pay_costs_v pc
WHERE pc.cost_allocation_keyflex_id = xga.reference_2)
transction_description,
NULL entered_currency,
entered_dr,
entered_cr,
xga.ledger_currency accounted_currency,
accounted_dr,
accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga
WHERE (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
AND NOT EXISTS
(SELECT 1
FROM pay_gl_interface pgi
WHERE pgi.cost_allocation_keyflex_id =
xga.reference_2)
AND UPPER (xga.je_source) = 'PAYROLL'
UNION ALL
SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
ppf.full_name party_name,
ppf.employee_number party_number,
'Payroll' transaction_type,
pgi.currency_code transaction_currency,
TO_CHAR (paf.payroll_id) transaction_number,
xga.reference_4 transaction_date,
xga.gl_date,
paf.payroll_id subledger_doc_no,
xga.doc_sequence_value,
( SELECT DISTINCT pc.element_name
FROM apps.pay_costs_v pc
WHERE pc.cost_allocation_keyflex_id = xga.reference_2) transction_description,
pgi.currency_code entered_currency,
NVL (pgi.entered_dr, 0) entered_dr,
NVL (pgi.entered_cr, 0) entered_cr,
xga.ledger_currency accounted_currency,
NVL (pgi.entered_dr, 0) accounted_dr,
NVL (pgi.entered_cr, 0) accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga,
pay_assignment_actions paa,
pay_gl_interface pgi,
per_assignments_f paf,
per_people_f ppf,
apps.per_periods_of_service ppos
WHERE xga.accounted_cr != 0
AND pgi.entered_cr != 0
AND xga.reference_1 = paa.payroll_action_id
AND paa.assignment_action_id = pgi.assignment_action_id
AND xga.reference_2 = pgi.cost_allocation_keyflex_id
AND xga.reference_5 = pgi.run_payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND ppf.person_id = paf.person_id
AND ppf.person_id = ppos.person_id
AND ( ( ppos.actual_termination_date IS NOT NULL
AND ppos.actual_termination_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppos.actual_termination_date BETWEEN paf.effective_start_date
AND paf.effective_end_date)
OR ( ppos.actual_termination_date IS NULL
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date))
AND UPPER (xga.je_source) = 'PAYROLL'
UNION ALL
SELECT xga.period_name,
xga.ledger_name,
xga.ledger_id,
xga.account,
xga.project,
xga.product_group,
xga.department,
xga.employee,
xga.emp_name,
xga.product,
xga.entity,
xga.inter_entity,
xga.spare1,
xga.spare2,
xga.combinations,
xga.je_source,
xga.je_category,
NULL party_name,
NULL party_number,
NULL transaction_type,
NULL transaction_currency,
NULL transaction_number,
NULL transaction_date,
xga.gl_date,
NULL subledger_doc_no,
xga.doc_sequence_value,
xga.description transction_description,
xga.entered_currency entered_currency,
xga.entered_dr entered_dr,
xga.entered_cr entered_cr,
xga.ledger_currency accounted_currency,
accounted_dr,
accounted_cr,
xga.code_combination_id,
xga.gl_cr_date
FROM XXJG_GL_ACC_V xga
WHERE (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
AND UPPER (xga.je_source) NOT IN ('PAYABLES',
'RECEIVABLES',
'COST MANAGEMENT',
'CASH MANAGEMENT',
'PROJECT ACCOUNTING',
'ASSETS',
'PAYROLL',
'1')
UNION ALL
SELECT DISTINCT
gb.period_name,
gl.name ledger_name,
gb.ledger_id,
gcc.segment2 account,
gcc.segment6 project,
gcc.segment3 product_group,
gcc.segment4 department,
gcc.segment7 employee,
(SELECT ffl.DESCRIPTION
FROM FND_ID_FLEX_SEGMENTS_VL fil,
FND_FLEX_VALUES_VL ffl
WHERE 1 = 1
AND gcc.segment7 = ffl.flex_value
AND ffl.FLEX_VALUE_SET_ID = fil.FLEX_VALUE_SET_ID
AND fil.segment_name = 'Employee'
AND fil.ID_FLEX_CODE = 'GL#')
emp_name,
gcc.segment5 product,
gcc.segment1 entity,
gcc.segment8 inter_entity,
gcc.segment9 spare1,
gcc.segment10 spare2,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
|| '.'
|| gcc.segment9
|| '.'
|| gcc.segment10
combinations,
NULL je_source,
NULL je_category,
NULL party_name,
NULL party_number,
NULL transaction_type,
NULL transaction_currency,
NULL transaction_number,
NULL transaction_date,
gps.start_date gl_date,
NULL subledger_doc_no,
NULL doc_sequence_value,
NULL transction_description,
NULL entered_currency,
NULL entered_dr,
NULL entered_cr,
gl.currency_code accounted_currency,
NULL accounted_dr,
NULL accounted_cr,
gb.code_combination_id,
gb.last_update_date gl_cr_date
FROM gl_balances gb,
gl_code_combinations gcc,
gl_ledgers gl,
gl_period_statuses gps
WHERE gb.code_combination_id = gcc.code_combination_id
AND gb.ledger_id = gl.ledger_id
AND gps.application_id = 101
AND gb.period_name = gps.period_name
AND gl.ledger_id = gps.ledger_id
AND gb.code_combination_id NOT IN (SELECT DISTINCT
gjl.code_combination_id
FROM gl_ledgers gl,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE gl.ledger_id = gjh.ledger_id
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
AND gjh.status = 'P'
AND gjl.code_combination_id = gcc.code_combination_id
AND ( gjl.accounted_dr != 0
OR gjl.accounted_cr != 0)
AND gjl.period_name = gb.period_name
AND gjl.ledger_id = gb.ledger_id)
-------------------------added by JG----------------
/*UNION ALL
SELECT gjl.period_name,
gl.name ledger_name,
gjl.ledger_id,
gcc.segment2 account,
gcc.segment6 project,
gcc.segment3 product_group,
gcc.segment4 department,
gcc.segment7 employee,
(SELECT ffl.DESCRIPTION
FROM FND_ID_FLEX_SEGMENTS_VL fil,
FND_FLEX_VALUES_VL ffl
WHERE 1 = 1
AND gcc.segment7 = ffl.flex_value
AND ffl.FLEX_VALUE_SET_ID = fil.FLEX_VALUE_SET_ID
AND fil.segment_name = 'Employee'
AND fil.ID_FLEX_CODE = 'GL#')
emp_name,
gcc.segment5 product,
gcc.segment1 entity,
gcc.segment8 inter_entity,
gcc.segment9 spare1,
gcc.segment10 spare2,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
|| '.'
|| gcc.segment9
|| '.'
|| gcc.segment10
combinations,
gjh.Je_source,
gjh.je_category,
NULL party_name,
NULL party_number,
'Reversed' transaction_type,
NULL transaction_currency,
NULL transaction_number,
NULL transaction_date,
gjl.EFFECTIVE_DATE gl_date,
NULL subledger_doc_no,
NULL doc_sequence_value,
NULL transction_description,
gjh.CURRENCY_CODE entered_currency,
NVL (gjl.entered_dr, 0) entered_dr,
NVL (gjl.entered_cr, 0) entered_cr,
gjh.CURRENCY_CODE accounted_currency,
NVL (gjl.accounted_dr, 0) accounted_dr,
NVL (gjl.accounted_cr, 0) accounted_cr,
gjl.code_combination_id,
gjl.last_update_date gl_cr_date
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_ledgers gl,
gl_code_combinations gcc
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
AND gjh.status = 'P'
AND gjh.period_name = gjl.period_name
AND gjh.ledger_id = gjl.ledger_id
AND gl.ledger_id = gjh.ledger_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.reversed_je_header_id IS NOT NULL --Reversed data only
*/ -------------added by JG----------------
) tab1,
( SELECT ledger_id,
period_name,
code_combination_id,
CURRENCY_CODE,
( SUM (NVL (gb.begin_balance_dr, 0))
- SUM (NVL (gb.begin_balance_cr, 0)))
open_balance,
SUM (NVL (gb.period_net_dr_beq, 0)) sum_debit,
SUM (NVL (gb.period_net_cr_beq, 0)) sum_credit,
( ( SUM (NVL (gb.begin_balance_dr, 0))
- SUM (NVL (gb.begin_balance_cr, 0)))
+ ( SUM (NVL (gb.period_net_dr, 0))
- SUM (NVL (gb.period_net_cr, 0))))
closing_balance
FROM apps.gl_balances gb
WHERE 1 = 1
GROUP BY ledger_id,
period_name,
code_combination_id,
CURRENCY_CODE) xx
WHERE tab1.ledger_id = xx.ledger_id
AND tab1.accounted_currency = xx.currency_code
AND tab1.period_name = xx.period_name
AND tab1.code_combination_id = xx.code_combination_id
---End Query
--Below view
-----------------------------------------------------------------------------------------
Create OR Replace View XXJG_GL_ACC_V
AS
SELECT gjh.period_name,
gl.name ledger_name,
gl.ledger_id,
gjh.je_source,
gjh.je_category,
gjh.default_effective_date gl_date,
gjh.doc_sequence_value,
gjl.code_combination_id,
gjh.je_batch_id,
gjl.je_line_num,
gjh.posted_date gl_cr_date,
gjh.je_header_id,
gcc.segment1 entity, --account,
gcc.segment2 account, --project,
gcc.segment3 product_group, --resource_org,
gcc.segment4 department, --work_location,
gcc.segment5 product, --client_vertical,
gcc.segment6 project, --product,
gcc.segment7 employee, --entity,
gcc.segment8 inter_entity, --inter_entity,
gcc.segment9 spare1, --spare1,
gcc.segment10 spare2, --spare2,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
|| '.'
|| gcc.segment9
|| '.'
|| gcc.segment10
combinations,
gjl.reference_2,
gjl.reference_5,
gjl.reference_1,
gjl.reference_4,
NVL (gjl.entered_dr, 0) entered_dr,
NVL (gjl.entered_cr, 0) entered_cr,
NVL (gjl.accounted_dr, 0) accounted_dr,
NVL (gjl.accounted_cr, 0) accounted_cr,
gl.currency_code ledger_currency,
gjh.currency_code entered_currency,
gjl.description
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_ledgers gl,
gl_code_combinations gcc
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = 'A'
AND gjh.status = 'P'
AND gjh.period_name = gjl.period_name
AND gjh.ledger_id = gjl.ledger_id
AND gl.ledger_id = gjh.ledger_id
AND gjl.code_combination_id = gcc.code_combination_id