AMRESH KUMARAN ORACLE APPLICATIONS: Trial Balance Re- Conciliation Query

CREATE OR REPLACE PROCEDURE APPS.trial_balance (
i_fromdate DATE,
i_todate DATE,
i_acct_code NUMBER,
acct_code7 NUMBER,
i_org_code NUMBER
)
IS
/*#########################################################################################
# Module: GENERAL LEDGER #
# #
# File name: TRAIL_BALANCE #
# #
# Function: PROCEDURE to hold the Recipes Details #
# #
# Author: AMRESH KUMARAN #
# #
# Written: 28-DEC-2014 #
# #
# Modification History #
#-----------------------------------------------------------------------------------------#
# Author Date Version Description #
#-----------------------------------------------------------------------------------------#
# AMRESH KUMARAN 28-DEC-2014 1.0 Initial Version #
#########################################################################################*/
BEGIN
INSERT INTO trial_balance_table
(acct_code, je_source, je_category, supplier_code,
supplier_name, NAME, description, batch_name, journal_name,
journal_description, jv_date, voucher_no, voucher_date,
po_number, po_date, narration, debit_amount, credit_amount)
SELECT acct_code, je_source, je_category, supplier_code, supplier_name,
NAME, description, batch_name, journal_name,
journal_description, jv_date, voucher_no, voucher_date,
po_number, po_date, narration, debit_amount, credit_amount
FROM (SELECT DISTINCT ord, acct AS acct_code, je_source, je_category,
supplier_code, supplier_name, NAME, description,
(CASE
WHEN doc_sequence_value IS NOT NULL
THEN doc_sequence_value
ELSE NULL
END
) AS doc_sequence_value,
batch_name, journal_name, journal_description,
jv_date,
(CASE
WHEN user_name IS NOT NULL
THEN user_name
ELSE NULL
END
) AS user_name,
(CASE
WHEN created_by IS NOT NULL
THEN created_by
ELSE 0
END
) AS created_by,
transaction_number AS voucher_no,
transaction_date AS voucher_date,
ctransaction_number AS chq_no,
ctransaction_date AS chq_date,
(CASE
WHEN po_number IS NOT NULL
THEN po_number
ELSE NULL
END
) AS po_number,
(CASE
WHEN po_date IS NOT NULL
THEN po_date
ELSE NULL
END
) AS po_date,
(CASE
WHEN po_discription IS NOT NULL
THEN po_discription
ELSE NULL
END
) AS narration,
(CASE
WHEN fpost_adamount IS NOT NULL
THEN fpost_adamount
ELSE 0
END
) AS debit_amount,
(CASE
WHEN fpost_acamount IS NOT NULL
THEN fpost_acamount
ELSE 0
END
) AS credit_amount,
(CASE
WHEN fpost_adamount IS NOT NULL
THEN fpost_adamount
ELSE 0
END
) AS jfpost_adamount,
(CASE
WHEN fpost_acamount IS NOT NULL
THEN fpost_acamount
ELSE 0
END
) AS jfpost_acamount,
je_header_id, je_line_num, gl_sl_link_id
FROM (SELECT DISTINCT 1 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source,
(CASE
WHEN rct.destination_type_code IN
('EXPENSE')
THEN 'Receiving'
WHEN rct.destination_type_code IN
('RECEIVING')
THEN 'Inventory'
END
) AS je_category,
gjh.NAME, gjh.description,
gir.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
receipt_num
AS transaction_number,
'' AS ctransaction_number,
TO_CHAR
(rct.transaction_date,
'DD-MON-YYYY'
) AS transaction_date,
'' AS ctransaction_date,
'' AS po_number, '' AS po_date,
'' AS po_discription,
xal.accounted_dr
AS fpost_adamount,
xal.accounted_cr
AS fpost_acamount,
(CASE
WHEN CAST
(gir.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gir.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
) AS jfpost_adamount,
(CASE
WHEN CAST
(gir.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gir.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
) AS jfpost_acamount,
gir.je_header_id,
gir.je_line_num,
xal.gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
aps.segment1 supplier_code,
aps.vendor_name supplier_name
FROM apps.po_headers_all pha,
apps.ap_suppliers aps,
apps.ap_supplier_sites_all apss,
apps.rcv_transactions rct,
apps.rcv_shipment_headers rsh,
apps.xla_transaction_entities_upg xte,
apps.xla_ae_headers xah,
apps.xla_ae_lines xal,
apps.gl_import_references gir,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_je_batches_headers_v glv,
apps.gl_code_combinations gcc,
apps.gl_je_lines gjl
WHERE TRUNC (xal.accounting_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL (acct_code7,
gcc.segment7
)
AND gcc.segment1 = i_org_code
AND rct.po_header_id =
pha.po_header_id
AND rct.transaction_type IN
('DELIVER', 'RECEIVE',
'CORRECT')
AND rct.destination_type_code IN
('EXPENSE', 'RECEIVING')
AND pha.vendor_id = aps.vendor_id
AND pha.vendor_site_id =
apss.vendor_site_id
AND apss.vendor_id = aps.vendor_id
AND apss.vendor_id = pha.vendor_id
AND xte.entity_code =
'RCV_ACCOUNTING_EVENTS'
AND rsh.shipment_header_id =
rct.shipment_header_id
AND rct.transaction_id =
xte.source_id_int_1
AND xah.entity_id = xte.entity_id
AND xah.ae_header_id =
xal.ae_header_id
AND gir.gl_sl_link_id =
xal.gl_sl_link_id
AND gir.je_header_id =
lv.je_header_id
AND gir.je_line_num =
lv.je_line_num
AND gir.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gir.je_header_id =
glv.je_header_id
AND gir.je_batch_id =
glv.je_batch_id
AND gcc.code_combination_id =
lv.code_combination_id
AND gir.je_header_id =
gjl.je_header_id
AND gcc.code_combination_id =
gjl.code_combination_id
UNION ALL
SELECT DISTINCT 2 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source, gjh.je_category,
gjh.NAME, gjh.description,
lv.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
'' AS transaction_number,
'' AS ctransaction_number,
'' AS transaction_date,
'' AS ctransaction_date,
'' AS po_number, '' AS po_date,
'' AS po_discription,
(CASE
WHEN CAST
(gg.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
) AS fpost_adamount,
(CASE
WHEN CAST
(gg.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
) AS fpost_acamount,
(CASE
WHEN CAST
(gg.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
) AS jfpost_adamount,
(CASE
WHEN CAST
(gg.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
) AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num,
(CASE
WHEN gg.gl_sl_link_id IS NOT NULL
THEN gg.gl_sl_link_id
ELSE 0
END
) AS gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
NULL supplier_code,
NULL supplier_name
FROM apps.gl_import_references gg,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE gg.je_header_id =
lv.je_header_id
AND gg.je_line_num = lv.je_line_num
AND gg.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND gg.je_header_id =
glv.je_header_id
AND gg.je_batch_id =
glv.je_batch_id
AND gjh.status = 'P'
AND gjh.je_source IN ('Inventory')
AND TRUNC
(gjh.default_effective_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL (acct_code7,
gcc.segment7
)
AND gcc.segment1 = i_org_code
UNION ALL
SELECT DISTINCT 3 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source, gjh.je_category,
gjh.NAME, gjh.description,
gg.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
(CASE
WHEN gjh.je_category =
'Receiving India'
THEN (CASE
WHEN gg.reference_4 =
'jai_rgm_trx_records'
THEN (SELECT rsh.receipt_num
FROM rcv_shipment_headers rsh,
jai_rgm_trx_records jrt,
rcv_transactions rt
WHERE repository_id =
gg.reference_6
AND rt.transaction_id =
jrt.source_document_id
AND rsh.shipment_header_id =
rt.shipment_header_id)
ELSE (SELECT rsh.receipt_num
FROM rcv_transactions rt,
rcv_shipment_headers rsh
WHERE TO_CHAR
(transaction_id
) =
gg.reference_5
AND rt.shipment_header_id =
rsh.shipment_header_id)
END
)
ELSE (SELECT aia.invoice_num
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_invoices_all aia
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aia.doc_sequence_value =
gir.subledger_doc_sequence_value
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
)
BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
END
) AS transaction_number,
(SELECT TO_CHAR
(check_number
)
AS invoice_num
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_checks_all aca,
apps.ap_invoice_payments_all aipa
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aca.doc_sequence_value =
gir.subledger_doc_sequence_value
AND aipa.check_id =
aca.check_id
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
) BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
AS ctransaction_number,
(CASE
WHEN gjh.je_category =
'Receiving India'
THEN (CASE
WHEN gg.reference_4 =
'jai_rgm_trx_records'
THEN (SELECT TO_CHAR
(rsh.creation_date,
'DD-MON-YYYY'
)
FROM rcv_shipment_headers rsh,
jai_rgm_trx_records jrt,
rcv_transactions rt
WHERE repository_id =
gg.reference_6
AND rt.transaction_id =
jrt.source_document_id
AND rsh.shipment_header_id =
rt.shipment_header_id)
ELSE (SELECT TO_CHAR
(rsh.creation_date,
'DD-MON-YYYY'
)
FROM rcv_transactions rt,
rcv_shipment_headers rsh
WHERE TO_CHAR
(transaction_id
) =
gg.reference_5
AND rt.shipment_header_id =
rsh.shipment_header_id)
END
)
ELSE (SELECT TO_CHAR
(aia.invoice_date,
'DD-MON-YYYY'
)
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_invoices_all aia
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aia.doc_sequence_value =
gir.subledger_doc_sequence_value
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
)
BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
END
) AS transaction_date,
(SELECT TO_CHAR
(aca.check_date,
'DD-MON-YYYY'
)
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_checks_all aca,
apps.ap_invoice_payments_all aipa
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aca.doc_sequence_value =
gir.subledger_doc_sequence_value
AND aipa.check_id =
aca.check_id
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
) BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
AS ctransaction_date,
(SELECT pha.segment1
AS po_number
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all aial,
apps.po_headers_all pha
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aia.doc_sequence_value =
gir.subledger_doc_sequence_value
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND aia.invoice_id =
aial.invoice_id
AND aial.po_header_id =
pha.po_header_id
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
) BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND gcc.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
AS po_number,
(SELECT TO_CHAR
(pha.creation_date,
'DD-MON-YYYY'
)
AS po_date
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all aial,
apps.po_headers_all pha
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aia.doc_sequence_value =
gir.subledger_doc_sequence_value
AND aia.invoice_id =
aial.invoice_id
AND aial.po_header_id =
pha.po_header_id
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
) BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
AS po_date,
(SELECT xal.description
|| ' - '
|| sm.vendor_name
AS po_discription
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
xla_ae_lines xal,
apps.gl_je_lines_v l,
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all aial,
apps.ap_suppliers sm
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND xal.gl_sl_link_id =
gir.gl_sl_link_id
AND xal.gl_sl_link_id =
gg.gl_sl_link_id
AND l.code_combination_id =
g.code_combination_id
AND aia.doc_sequence_value =
gir.subledger_doc_sequence_value
AND aia.invoice_id =
aial.invoice_id
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND aia.vendor_id =
sm.vendor_id
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
) BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND g.segment1 =
i_org_code
AND h.status = 'P')
AS po_discription,
(CASE
WHEN CAST
(gg.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
) AS fpost_adamount,
(CASE
WHEN CAST
(gg.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
) AS fpost_acamount,
(CASE
WHEN CAST
(gg.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
) AS jfpost_adamount,
(CASE
WHEN CAST
(gg.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
) AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num, gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
(CASE
WHEN gjh.je_category =
'Payments'
THEN
--(case when (trim(REGEXP_SUBSTR (GJH.DESCRIPTION, '[^,]+', 1, 1)))='Payment Matured' THEN (select aps.segment1 from ap_checks_all aca,ap_suppliers aps where aps.vendor_id=aca.vendor_id and aca.check_number=(trim(REGEXP_SUBSTR (trim(REGEXP_SUBSTR (GJH.DESCRIPTION, '[^,]+', 1, 2)),'[^]+', 1, 2))) AND ROWNUM=1) ELSE
(SELECT aps.segment1
FROM ap_invoice_payments_all apa,
apps.ap_invoices_all aia,
ap_suppliers aps
WHERE apa.invoice_id =
aia.invoice_id
AND aps.vendor_id =
aia.vendor_id
AND accounting_event_id =
gg.reference_6
AND ROWNUM = 1)
WHEN gjh.je_category =
'Receiving India'
THEN (CASE
WHEN gg.reference_4 =
'jai_rgm_trx_records'
THEN (SELECT pv.segment1
FROM po_vendors pv,
jai_rgm_trx_records jrt,
rcv_transactions rt
WHERE repository_id =
gg.reference_6
AND rt.transaction_id =
jrt.source_document_id
AND rt.vendor_id =
pv.vendor_id)
ELSE (SELECT pv.segment1
FROM rcv_transactions rt,
po_vendors pv
WHERE TO_CHAR
(transaction_id
) =
gg.reference_5
AND pv.vendor_id =
rt.vendor_id)
END
)
WHEN gjh.je_category =
'Purchase Invoices'
THEN (SELECT aps.segment1
FROM apps.ap_invoices_all aia,
ap_invoice_distributions_all aid,
ap_suppliers aps
WHERE accounting_event_id =
gg.reference_6
AND aia.invoice_id =
aid.invoice_id
AND aps.vendor_id =
aia.vendor_id
AND ROWNUM = 1)
ELSE NULL
END
) supplier_code,
(CASE
WHEN gjh.je_category =
'Payments'
THEN
--(case when (trim(REGEXP_SUBSTR (GJH.DESCRIPTION, '[^,]+', 1, 1)))='Payment Matured' THEN (select aps.vendor_name from ap_checks_all aca,ap_suppliers aps where aps.vendor_id=aca.vendor_id and aca.check_number=(trim(REGEXP_SUBSTR (trim(REGEXP_SUBSTR (GJH.DESCRIPTION, '[^,]+', 1, 2)),'[^]+', 1, 2))) AND ROWNUM=1)ELSE
(SELECT aps.vendor_name
FROM ap_invoice_payments_all apa,
apps.ap_invoices_all aia,
ap_suppliers aps
WHERE apa.invoice_id =
aia.invoice_id
AND aps.vendor_id =
aia.vendor_id
AND accounting_event_id =
gg.reference_6
AND ROWNUM = 1)
WHEN gjh.je_category =
'Receiving India'
THEN (CASE
WHEN gg.reference_4 =
'jai_rgm_trx_records'
THEN (SELECT pv.vendor_name
FROM po_vendors pv,
jai_rgm_trx_records jrt,
rcv_transactions rt
WHERE repository_id =
gg.reference_6
AND rt.transaction_id =
jrt.source_document_id
AND rt.vendor_id =
pv.vendor_id)
ELSE (SELECT pv.vendor_name
FROM rcv_transactions rt,
po_vendors pv
WHERE TO_CHAR
(transaction_id
) =
gg.reference_5
AND pv.vendor_id =
rt.vendor_id)
END
)
WHEN gjh.je_category =
'Purchase Invoices'
THEN (SELECT aps.vendor_name
FROM apps.ap_invoices_all aia,
ap_invoice_distributions_all aid,
ap_suppliers aps
WHERE accounting_event_id =
gg.reference_6
AND aia.invoice_id =
aid.invoice_id
AND aps.vendor_id =
aia.vendor_id
AND ROWNUM = 1)
ELSE NULL
END
) supplier_name
FROM apps.gl_import_references gg,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE gg.je_header_id =
lv.je_header_id
AND gg.je_line_num = lv.je_line_num
AND gg.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND gg.je_header_id =
glv.je_header_id
AND gg.je_batch_id =
glv.je_batch_id
AND gjh.je_source IN
('Purchasing India',
'Payables')
AND gjh.status = 'P'
AND TRUNC
(gjh.default_effective_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 = acct_code7
AND gcc.segment1 = i_org_code
UNION ALL
SELECT DISTINCT 4 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source, gjh.je_category,
gjh.NAME, gjh.description,
gg.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
(SELECT trx_number
FROM apps.ra_customer_trx_all rcta
WHERE rcta.trx_number =
CAST
(gg.subledger_doc_sequence_value AS VARCHAR2 (50)
)
AND ROWNUM = 1)
AS transaction_number,
'' AS ctransaction_number,
(SELECT TO_CHAR
(trx_date,
'DD-MON-YYYY'
)
FROM apps.ra_customer_trx_all rcta
WHERE rcta.trx_number =
CAST
(gg.subledger_doc_sequence_value AS VARCHAR2 (50)
)
AND ROWNUM = 1)
AS transaction_date,
'' AS ctransaction_date,
'' AS po_number, '' AS po_date,
'' AS po_discription,

--(Case When GG.REFERENCE_9 Is Not Null Then Cast(GG.REFERENCE_9 as Number) Else 0 End) as FPOST_ADAmount,
--(Case When GG.REFERENCE_10 Is Not Null Then Cast(GG.REFERENCE_10 as Number) Else 0 End) as FPOST_ACAmount,
--(Case When GG.REFERENCE_9 Is Not Null Then Cast(GG.REFERENCE_9 as Number) Else 0 End) as JFPOST_ADAmount,
--(Case When GG.REFERENCE_10 Is Not Null Then Cast(GG.REFERENCE_10 as Number) Else 0 End) as JFPOST_ACAmount,
lv.entered_dr
AS fpost_adamount,
lv.entered_cr
AS fpost_acamount,
lv.accounted_dr
AS jfpost_adamount,
lv.accounted_cr
AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num,
gg.gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
(CASE
WHEN gjh.je_category =
'Register India'
THEN (CASE
WHEN gg.reference_4 =
'RA_CUSTOMER_TRX_ALL'
THEN (SELECT ac.customer_number
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_6 =
rctla.customer_trx_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE (SELECT ac.customer_number
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_5 =
rctla.customer_trx_line_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
END
)
--when gjh.je_category='India Tax Collected' then (SELECT ac.customer_name FROM wsh_new_deliveries wsh, apps.ar_customers ac where wsh.customer_id=ac.customer_id and wsh.delivery_id=gg.REFERENCE_5)
WHEN gjh.je_category =
'India Tax Collected'
THEN (CASE
WHEN gg.reference_4 =
'ra_customer_trx_all'
THEN (SELECT ac.customer_number
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_5 =
rctla.customer_trx_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE (SELECT ac.customer_number
FROM wsh_new_deliveries wsh,
apps.ar_customers ac
WHERE wsh.customer_id =
ac.customer_id
AND wsh.delivery_id =
gg.reference_5)
END
)
WHEN gjh.je_category =
'Receipts'
THEN (SELECT ac.customer_number
FROM ar_cash_receipts_all arc,
ar_customers ac
WHERE arc.doc_sequence_value =
CAST
(gg.subledger_doc_sequence_value AS VARCHAR2 (50)
)
AND ROWNUM = 1
AND ac.customer_id =
arc.pay_from_customer)
WHEN gjh.je_category =
'Adjustment'
THEN (SELECT ac.customer_number
FROM ar_adjustments_all adj,
ra_customer_trx_all rcta,
ar_customers ac
WHERE adj.event_id =
gg.reference_6
AND rcta.customer_trx_id =
adj.customer_trx_id
AND ac.customer_id =
rcta.bill_to_customer_id
AND ROWNUM = 1)
WHEN gjh.je_category =
'Sales Invoices'
THEN (SELECT ac.customer_number
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla,
ra_cust_trx_line_gl_dist_all rctd
WHERE 1 = 1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_6 =
event_id
AND ROWNUM = 1
AND rctd.customer_trx_line_id =
rctla.customer_trx_line_id
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE NULL
END
) customer_number,
(CASE
WHEN gjh.je_category =
'Register India'
THEN (CASE
WHEN gg.reference_4 =
'RA_CUSTOMER_TRX_ALL'
THEN (SELECT ac.customer_name
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_6 =
rctla.customer_trx_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE (SELECT ac.customer_name
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_5 =
rctla.customer_trx_line_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
END
)
WHEN gjh.je_category =
'India Tax Collected'
THEN (CASE
WHEN gg.reference_4 =
'ra_customer_trx_all'
THEN (SELECT ac.customer_name
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_5 =
rctla.customer_trx_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE (SELECT ac.customer_name
FROM wsh_new_deliveries wsh,
apps.ar_customers ac
WHERE wsh.customer_id =
ac.customer_id
AND wsh.delivery_id =
gg.reference_5)
END
)
WHEN gjh.je_category =
'Receipts'
THEN (SELECT ac.customer_name
FROM ar_cash_receipts_all arc,
ar_customers ac
WHERE arc.doc_sequence_value =
CAST
(gg.subledger_doc_sequence_value AS VARCHAR2 (50)
)
AND ROWNUM = 1
AND ac.customer_id =
arc.pay_from_customer)
WHEN gjh.je_category =
'Adjustment'
THEN (SELECT ac.customer_name
FROM ar_adjustments_all adj,
ra_customer_trx_all rcta,
ar_customers ac
WHERE adj.event_id =
gg.reference_6
AND rcta.customer_trx_id =
adj.customer_trx_id
AND ac.customer_id =
rcta.bill_to_customer_id
AND ROWNUM = 1)
WHEN gjh.je_category =
'Sales Invoices'
THEN (SELECT ac.customer_name
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla,
ra_cust_trx_line_gl_dist_all rctd
WHERE 1 = 1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_6 =
event_id
AND ROWNUM = 1
AND rctd.customer_trx_line_id =
rctla.customer_trx_line_id
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE NULL
END
) customer_name
FROM apps.gl_import_references gg,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE gg.je_header_id =
lv.je_header_id
AND gg.je_line_num = lv.je_line_num
AND gg.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND gg.je_header_id =
glv.je_header_id
AND gg.je_batch_id =
glv.je_batch_id
AND gjh.status = 'P'
AND gjh.je_source IN
('Receivables',
'Receivables India')
AND TRUNC
(gjh.default_effective_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL (acct_code7,
gcc.segment7
)
AND gcc.segment1 = i_org_code
UNION ALL
SELECT DISTINCT 5 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source, gjh.je_category,
gjh.NAME, gjh.description,
lv.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
'' AS transaction_number,
'' AS ctransaction_number,
'' AS transaction_date,
'' AS ctransaction_date,
'' AS po_number, '' AS po_date,
'' AS po_discription,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
) AS fpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
) AS fpost_acamount,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
) AS jfpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
) AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num,
(SELECT a.gl_sl_link_id
FROM apps.gl_import_references a,
apps.gl_je_lines_v b,
apps.gl_je_headers c
WHERE a.je_header_id =
b.je_header_id
AND a.je_header_id =
c.je_header_id
AND a.je_line_num =
b.je_line_num
AND c.je_header_id =
lv.je_header_id
AND a.je_line_num =
lv.je_line_num
AND segment3 = i_acct_code
AND segment7 = acct_code7
AND ROWNUM = 1)
gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
NULL supplier_code,
NULL supplier_name
FROM apps.gl_je_lines l,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE l.je_header_id =
lv.je_header_id
AND l.je_line_num = lv.je_line_num
AND l.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND l.je_header_id =
glv.je_header_id
AND gjh.je_batch_id =
glv.je_batch_id
AND gjh.status = 'P'
AND gjh.je_source IN
('Cost Management')
AND TRUNC
(gjh.default_effective_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL (acct_code7,
gcc.segment7
)
AND gcc.segment1 = i_org_code
AND (SELECT a.je_line_num
FROM apps.gl_import_references a,
apps.gl_je_lines_v b,
apps.gl_je_headers c
WHERE a.je_header_id =
b.je_header_id
AND a.je_header_id =
c.je_header_id
AND a.je_line_num =
b.je_line_num
AND c.je_header_id =
lv.je_header_id
AND a.je_line_num =
lv.je_line_num
AND segment3 = i_acct_code
AND segment7 = acct_code7
AND ROWNUM = 1) IS NULL
UNION ALL
---- With Gl_Sl_Link_Id Start
SELECT DISTINCT ord, acct_code, je_source,
je_category, NAME, description,
doc_sequence_value, batch_name,
journal_name,
journal_description, jv_date,
user_name, created_by,
transaction_number,
ctransaction_number,
transaction_date,
ctransaction_date, po_number,
po_date, po_discription,
fpost_adamount, fpost_acamount,
jfpost_adamount,
jfpost_acamount, je_header_id,
je_line_num, gl_sl_link_id,
acct, supplier_code,
supplier_name
FROM (SELECT DISTINCT 6 AS ord,
gcc.segment3
AS acct_code,
gjh.je_source,
gjh.je_category,
gjh.NAME,
gjh.description,
lv.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME
AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
)
AS jv_date,
''
AS user_name,
gjh.created_by,
''
AS transaction_number,
''
AS ctransaction_number,
''
AS transaction_date,
''
AS ctransaction_date,
''
AS po_number,
'' AS po_date,
''
AS po_discription,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
)
AS fpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
)
AS fpost_acamount,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
)
AS jfpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
)
AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num,
(SELECT a.gl_sl_link_id
FROM apps.gl_import_references a,
apps.gl_je_lines_v b,
apps.gl_je_headers c
WHERE a.je_header_id =
b.je_header_id
AND a.je_header_id =
c.je_header_id
AND a.je_line_num =
b.je_line_num
AND c.je_header_id =
lv.je_header_id
AND a.je_line_num =
lv.je_line_num
AND segment3 =
i_acct_code
AND segment7 =
acct_code7
AND ROWNUM =
1)
gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
acct,
(SELECT pv.segment1
FROM ap_invoice_payments_all aip,
ap_invoices_all aia,
po_vendors pv,
gl_import_references gg
WHERE invoice_payment_id =
gg.reference_5
AND aia.invoice_id =
aip.invoice_id
AND pv.vendor_id =
aia.vendor_id
AND gg.je_header_id =
lv.je_header_id
AND gg.je_line_num =
lv.je_line_num)
supplier_code,
(SELECT pv.vendor_name
FROM ap_invoice_payments_all aip,
ap_invoices_all aia,
po_vendors pv,
gl_import_references gg
WHERE invoice_payment_id =
gg.reference_5
AND aia.invoice_id =
aip.invoice_id
AND pv.vendor_id =
aia.vendor_id
AND gg.je_header_id =
lv.je_header_id
AND gg.je_line_num =
lv.je_line_num)
supplier_name
FROM apps.gl_je_lines l,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE l.je_header_id =
lv.je_header_id
AND l.je_line_num =
lv.je_line_num
AND l.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND l.je_header_id =
glv.je_header_id
AND gjh.je_batch_id =
glv.je_batch_id
AND gjh.status =
'P'
AND gjh.je_source IN
('Manual',
'AutoCopy',
'Service Tax India',
'Cost Management',
'VAT India',
'Register India',
'Projects')
AND TRUNC
(gjh.default_effective_date
)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL
(i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL
(acct_code7,
gcc.segment7
)
AND gcc.segment1 =
i_org_code)
--Restrict Gl_Sl_Link_Id Start;
WHERE gl_sl_link_id NOT IN (
SELECT gl_sl_link_id
FROM (SELECT DISTINCT 1
AS ord,
gcc.segment3
AS acct_code,
gjh.je_source,
(CASE
WHEN rct.destination_type_code IN
('EXPENSE')
THEN 'Receiving'
WHEN rct.destination_type_code IN
('RECEIVING')
THEN 'Inventory'
END
)
AS je_category,
gjh.NAME,
gjh.description,
gir.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME
AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
)
AS jv_date,
''
AS user_name,
gjh.created_by,
receipt_num
AS transaction_number,
''
AS ctransaction_number,
TO_CHAR
(rct.transaction_date,
'DD-MON-YYYY'
)
AS transaction_date,
''
AS ctransaction_date,
''
AS po_number,
''
AS po_date,
''
AS po_discription,
xal.accounted_dr
AS fpost_adamount,
xal.accounted_cr
AS fpost_acamount,
(CASE
WHEN CAST
(gir.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gir.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
)
AS jfpost_adamount,
(CASE
WHEN CAST
(gir.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gir.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
)
AS jfpost_acamount,
gir.je_header_id,
gir.je_line_num,
xal.gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
acct
FROM apps.po_headers_all pha,
apps.ap_suppliers aps,
apps.ap_supplier_sites_all apss,
apps.rcv_transactions rct,
apps.rcv_shipment_headers rsh,
apps.xla_transaction_entities_upg xte,
apps.xla_ae_headers xah,
apps.xla_ae_lines xal,
apps.gl_import_references gir,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_je_batches_headers_v glv,
apps.gl_code_combinations gcc,
apps.gl_je_lines gjl
WHERE TRUNC
(xal.accounting_date
)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL
(i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL
(acct_code7,
gcc.segment7
)
AND gcc.segment1 =
i_org_code
AND rct.po_header_id =
pha.po_header_id
AND rct.transaction_type IN
('DELIVER',
'RECEIVE',
'CORRECT')
AND rct.destination_type_code IN
('EXPENSE',
'RECEIVING')
AND pha.vendor_id =
aps.vendor_id
AND pha.vendor_site_id =
apss.vendor_site_id
AND apss.vendor_id =
aps.vendor_id
AND apss.vendor_id =
pha.vendor_id
AND xte.entity_code =
'RCV_ACCOUNTING_EVENTS'
AND rsh.shipment_header_id =
rct.shipment_header_id
AND rct.transaction_id =
xte.source_id_int_1
AND xah.entity_id =
xte.entity_id
AND xah.ae_header_id =
xal.ae_header_id
AND gir.gl_sl_link_id =
xal.gl_sl_link_id
AND gir.je_header_id =
lv.je_header_id
AND gir.je_line_num =
lv.je_line_num
AND gir.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gir.je_header_id =
glv.je_header_id
AND gir.je_batch_id =
glv.je_batch_id
AND gcc.code_combination_id =
lv.code_combination_id
AND gir.je_header_id =
gjl.je_header_id
AND gcc.code_combination_id =
gjl.code_combination_id))
--Restrict Gl_Sl_Link_Id End
---- With Gl_Sl_Link_Id ENd
UNION ALL
---- With Gl_Sl_Link_Id Is Null Start
SELECT DISTINCT ord, acct_code, je_source,
je_category, NAME, description,
doc_sequence_value, batch_name,
journal_name,
journal_description, jv_date,
user_name, created_by,
transaction_number,
ctransaction_number,
transaction_date,
ctransaction_date, po_number,
po_date, po_discription,
fpost_adamount, fpost_acamount,
jfpost_adamount,
jfpost_acamount, je_header_id,
je_line_num, gl_sl_link_id,
acct, supplier_code,
supplier_name
FROM (SELECT DISTINCT 6 AS ord,
gcc.segment3
AS acct_code,
gjh.je_source,
gjh.je_category,
gjh.NAME,
gjh.description,
lv.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME
AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
)
AS jv_date,
''
AS user_name,
gjh.created_by,
''
AS transaction_number,
(CASE
WHEN gjh.je_category =
'Receiving'
THEN (SELECT rrs.reference4
FROM xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
rcv_receiving_sub_ledger rrs,
gl_import_references gg
WHERE xah.ae_header_id =
gg.reference_7
AND gg.je_header_id =
l.je_header_id
AND gg.je_line_num =
l.je_line_num
AND xah.ae_header_id =
xal.ae_header_id
AND xdl.ae_header_id =
xal.ae_header_id
AND xdl.ae_line_num =
xal.ae_line_num
AND xdl.source_distribution_id_num_1 =
rrs.rcv_sub_ledger_id
AND xal.gl_sl_link_id =
l.gl_sl_link_id
AND ROWNUM =
1)
WHEN gjh.je_category =
'Inventory'
THEN (SELECT rsh.receipt_num
FROM mtl_transaction_accounts mta,
mtl_material_transactions mmt,
xla_ae_lines xal,
xla_distribution_links xld,
rcv_transactions rt,
rcv_shipment_headers rsh
WHERE xal.gl_sl_link_id =
l.gl_sl_link_id
AND xal.ae_header_id =
xld.ae_header_id
AND xal.ae_line_num =
xld.ae_line_num
AND mta.transaction_id =
mmt.transaction_id
AND mta.inv_sub_ledger_id =
source_distribution_id_num_1
AND mmt.rcv_transaction_id =
rt.transaction_id
AND rt.shipment_header_id =
rsh.shipment_header_id)
ELSE NULL
END
)
AS ctransaction_number,
''
AS transaction_date,
''
AS ctransaction_date,
''
AS po_number,
'' AS po_date,
''
AS po_discription,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
)
AS fpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
)
AS fpost_acamount,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
)
AS jfpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
)
AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num,
(SELECT a.gl_sl_link_id
FROM apps.gl_import_references a,
apps.gl_je_lines_v b,
apps.gl_je_headers c
WHERE a.je_header_id =
b.je_header_id
AND a.je_header_id =
c.je_header_id
AND a.je_line_num =
b.je_line_num
AND c.je_header_id =
lv.je_header_id
AND a.je_line_num =
lv.je_line_num
AND segment3 =
i_acct_code
AND segment7 =
acct_code7
AND ROWNUM =
1)
gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
acct,
(SELECT pv.segment1
FROM ap_invoice_payments_all aip,
ap_invoices_all aia,
po_vendors pv,
gl_import_references gg
WHERE invoice_payment_id =
gg.reference_5
AND aia.invoice_id =
aip.invoice_id
AND pv.vendor_id =
aia.vendor_id
AND gg.je_header_id =
lv.je_header_id
AND gg.je_line_num =
lv.je_line_num)
supplier_code,
(SELECT pv.vendor_name
FROM ap_invoice_payments_all aip,
ap_invoices_all aia,
po_vendors pv,
gl_import_references gg
WHERE invoice_payment_id =
gg.reference_5
AND aia.invoice_id =
aip.invoice_id
AND pv.vendor_id =
aia.vendor_id
AND gg.je_header_id =
lv.je_header_id
AND gg.je_line_num =
lv.je_line_num)
supplier_name
FROM apps.gl_je_lines l,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE l.je_header_id =
lv.je_header_id
AND l.je_line_num =
lv.je_line_num
AND l.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND l.je_header_id =
glv.je_header_id
AND gjh.je_batch_id =
glv.je_batch_id
AND gjh.status =
'P'
AND gjh.je_source IN
('Manual',
'AutoCopy',
'Service Tax India',
'Cost Management',
'VAT India',
'Register India',
'21')
AND TRUNC
(gjh.default_effective_date
)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL
(i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL
(acct_code7,
gcc.segment7
)
AND gcc.segment1 =
i_org_code)
WHERE gl_sl_link_id IS NULL
---- With Gl_Sl_Link_Id Is Null Start
UNION ALL
SELECT DISTINCT 7 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source, gjh.je_category,
gjh.NAME, gjh.description,
lv.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
'' AS transaction_number,
'' AS ctransaction_number,
'' AS transaction_date,
'' AS ctransaction_date,
'' AS po_number, '' AS po_date,
'' AS po_discription,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
) AS fpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
) AS fpost_acamount,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
) AS jfpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
) AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num, gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
NULL supplier_code,
NULL supplier_name
FROM apps.gl_je_lines l,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE l.je_header_id =
lv.je_header_id
AND l.je_line_num = lv.je_line_num
AND l.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND l.je_header_id =
glv.je_header_id
AND gjh.je_batch_id =
glv.je_batch_id
AND gjh.status = 'P'
AND gjh.je_source IN
('Project Accounting',
'Cash Management')
AND TRUNC
(gjh.default_effective_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL (acct_code7,
gcc.segment7
)
AND gcc.segment1 = i_org_code)
WHERE NVL (fpost_adamount, 0) <> 0
OR NVL (fpost_acamount, 0) <> 0
ORDER BY 15);
END;
/

AMRESH KUMARAN ORACLE APPLICATIONS

Monday, 29 December 2014

Trial Balance Re- Conciliation Query

No comments:

Post a Comment

Blog Archive