WITH gl_cmn_tbls
AS (SELECT DISTINCT GL.name AS Ledger_name,
GJS.je_source_name AS JE_SOURCE_NAME,
GJH.je_batch_id AS BATCH_ID,
GJB.name AS BATCH_NAME,
GJH.period_name AS PERIOD_NAME,
GCC.segment1 AS Code_comb_segment1,
GCC.segment2 AS Code_comb_segment2,
GCC.segment3 AS Code_comb_segment3,
GCC.segment4 AS Code_comb_segment4,
GCC.segment5 AS Code_comb_segment5,
GCC.segment6 AS Code_comb_segment6,
GCC.segment7 AS Code_comb_segment7,
GCC.segment8 AS Code_comb_segment8
--,GJB.POSTED_DATE AS gl_posted_date
,
XAH.accounting_date AS gl_posted_date,
Decode(GJB.status, 'P', GJB.last_updated_by,
NULL) "POSTED_BY",
GJL.currency_code AS JOURNAL_CURRENCY,
GJL.entered_dr AS ENTERED_DEBIT,
GJL.entered_cr AS ENTERED_CREDIT,
GJL.accounted_dr AS ACCOUNT_DEBIT,
GJL.accounted_cr AS ACCOUNT_CREDIT,
GJH.currency_conversion_rate,
GJH.currency_conversion_date,
xal.entered_dr AS XLA_ENTERED_DEBIT,
xal.entered_cr AS XLA_ENTERED_CREDIT,
xal.accounted_dr AS XLA_ACCOUNTED_ENTERED_DEBIT
,
xal.accounted_cr AS
XLA_ACCOUNTED_ENTERED_CREDIT,
xah.event_id,
xev.event_type_code,
NULL "TRADING_PARTNER",
NULL "SOURCE_ID_INT_1",
NULL "SUPPLIER_NUM",
NULL "SUPPLIER_SITE",
NULL "INVOICE_DATE",
NULL "INVOICE_NUM",
NULL "INVOICE_CURRENCY_CODE",
NULL "INVOICE_AMOUNT",
NULL "TOTAL_TAX_AMOUNT",
NULL "DISTRIBUTIONS_LINE_TYPE",
NULL "DISTRIBUTIONS_DESCRIPTION",
NULL "VENDOR_ID",
NULL "PARTY_NAME",
NULL "ASSET_ID",
NULL "PROJECT_NUMBER",
NULL "CUSTOMER_TRX_ID",
NULL "TRX_NUMBER",
NULL "AR_BATCH_NUMBER",
NULL "AR_RECEIPT_NUMBER",
NULL "AR_APPLIED_TRANS_NUMBER",
NULL "RECEIVABLE_APPLICATION_ID",
NULL "LOCATION",
NULL "LOCATION_SOURCE"
--,GJS.JE_SOURCE_NAME
,
xev.entity_id,
gjh.je_category,
gjh.legal_entity_id,
xah.description,
xah.ae_header_id,
xal.ae_line_num,
xte.transaction_number
FROM gl_je_headers GJH,
gl_ledgers GL,
gl_je_batches GJB,
gl_je_lines GJL,
gl_periods GP,
gl_code_combinations GCC,
gl_je_sources_b GJS,
xla_events xev,
xla_ae_headers xah,
xla_ae_lines xal,
gl_ledger_le_v gllv,
gl_import_references gir,
xla_transaction_entities xte
WHERE 1 = 1
AND xev.entity_id = xte.entity_id
AND GJH.je_header_id = GJL.je_header_id
AND GJH.ledger_id = GL.ledger_id
AND GJH.period_name = GP.period_name
AND GJB.je_batch_id = GJH.je_batch_id
AND GJL.code_combination_id = GCC.code_combination_id
AND GJS.JE_SOURCE_NAME IN ('Payables','Project Accounting','Receipt Accounting')
--AND GJS.JE_SOURCE_NAME IN ('Assets','Manual','Revaluation','Cross-Charging','Payables','Payroll','Project Accounting','Receivables')
AND gjh.status = 'P'
AND gjh.actual_flag = 'A'
AND GJH.je_source = GJS.je_source_name
AND xah.event_id = xev.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xah.gl_transfer_status_code = 'Y'
AND xal.gl_sl_link_id = gir.gl_sl_link_id(+)
AND xal.gl_sl_link_table = gir.gl_sl_link_table(+)
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 GJL.ATTRIBUTE2 is NOT null
AND gcc.code_combination_id = XAL.code_combination_id
AND gllv.ledger_id = GL.ledger_id(+)
AND ( gllv.legal_entity_name IN ( :P_LEGAL_ENTITY )
OR Coalesce (:P_LEGAL_ENTITY, NULL) IS NULL )
AND ( GL.name IN ( :P_LEDGER_NAME )
OR Coalesce (:P_LEDGER_NAME, NULL) IS NULL )
AND GP.period_name BETWEEN Nvl(:PERIOD_FROM, GP.period_name) AND
Nvl(:PERIOD_TO, gp.period_name)
AND ( GCC.segment1 IN ( :COMPANY )
OR Coalesce (:COMPANY, NULL) IS NULL )
AND ( GCC.segment2 IN ( :BUSINESS_UNIT )
OR Coalesce (:BUSINESS_UNIT, NULL) IS NULL )
AND ( GCC.segment3 IN ( :DEPARTMENT )
OR Coalesce (:DEPARTMENT, NULL) IS NULL )
AND ( GCC.segment4 IN ( :ACCOUNT )
OR Coalesce (:ACCOUNT, NULL) IS NULL )
AND ( GCC.segment5 IN ( :INTER_COMPANY )
OR Coalesce (:INTER_COMPANY, NULL) IS NULL )
AND ( GCC.segment6 IN ( :FUTURE1 )
OR Coalesce (:FUTURE1, NULL) IS NULL )
AND ( GCC.segment7 IN ( :FUTURE2 )
OR Coalesce (:FUTURE2, NULL) IS NULL ))
SELECT code_comb_segment1,
code_comb_segment2,
code_comb_segment3,
code_comb_segment4,
code_comb_segment5,
code_comb_segment6,
code_comb_segment7,
je_source_name,
je_category,
gl_date,
transaction_number,
supplier,
invoice_number,
payment_status_flag,
po_number,
project_number,
project_name,
description,
entered_debit,
entered_credit
FROM (/*SELECT DISTINCT gct.code_comb_segment1,
gct.code_comb_segment2,
gct.code_comb_segment3,
gct.code_comb_segment4,
gct.code_comb_segment5,
gct.code_comb_segment6,
gct.code_comb_segment7,
gct.je_source_name,
gct.je_category,
To_char(gct.gl_posted_date, 'MM/DD/YYYY') gl_date,
GCT.transaction_number
Transaction_Number,
NULL supplier,
NULL Invoice_number
,
NULL
PAYMENT_STATUS_FLAG,
NULL po_number,
NULL project_number
,
NULL
Project_name,
gct.description Description,
gct.xla_accounted_entered_debit entered_debit,
gct.xla_accounted_entered_credit entered_credit
FROM gl_cmn_tbls gct
WHERE gct.je_source_name NOT IN (
'Payables', 'Receivables', 'Project Accounting',
'Receipt Accounting' )
----END GL query
UNION ALL*/
---AP Invoice query ---------
SELECT DISTINCT gct.code_comb_segment1,
gct.code_comb_segment2,
gct.code_comb_segment3,
gct.code_comb_segment4,
gct.code_comb_segment5,
gct.code_comb_segment6,
gct.code_comb_segment7,
gct.je_source_name,
gct.je_category,
To_char(gct.gl_posted_date, 'MM/DD/YYYY')
gl_date,
GCT.transaction_number
Transaction_Number,
aps.vendor_name
supplier,
aia.invoice_num
Invoice_number,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'INVOICE PAYMENT STATUS'
AND enabled_flag = 'Y'
AND lookup_code = aia.payment_status_flag) AS
PAYMENT_STATUS_FLAG,
(SELECT segment1
FROM po_headers_all
WHERE po_header_id = aia.po_header_id
AND aps.vendor_id = vendor_id)
po_number,
(SELECT DISTINCT ppab.segment1 project_number
FROM ap_invoice_lines_all aila,
pjf_projects_all_b ppab,
pjf_projects_all_tl ppat
WHERE ppab.project_id = ppat.project_id
AND ppab.project_id = aila.pjc_project_id
AND aila.invoice_id = aia.invoice_id
AND rownum=1)
project_number,
(SELECT DISTINCT ppat.name Project_name
FROM ap_invoice_lines_all aila,
pjf_projects_all_b ppab,
pjf_projects_all_tl ppat
WHERE ppab.project_id = ppat.project_id
AND ppab.project_id = aila.pjc_project_id
AND aila.invoice_id = aia.invoice_id
AND rownum=1)
Project_name
,
gct.description
Description,
gct.xla_accounted_entered_debit
entered_debit
,
gct.xla_accounted_entered_credit
entered_credit
FROM gl_cmn_tbls gct,
ap_invoices_all aia,
poz_suppliers_v aps,
poz_supplier_sites_all_m poss,
xla_transaction_entities xte
WHERE 1 = 1
AND aia.invoice_id = xte.source_id_int_1
AND aia.vendor_id = aps.vendor_id(+)
AND aia.vendor_site_id = poss.vendor_site_id(+)
AND aps.vendor_id = poss.vendor_id(+)
AND gct.entity_id = xte.entity_id
AND xte.entity_code = 'AP_INVOICES'
AND gct.je_source_name IN ( 'Payables' )
AND gct.je_category = 'Purchase Invoices'
UNION ALL
SELECT DISTINCT gct.code_comb_segment1,
gct.code_comb_segment2,
gct.code_comb_segment3,
gct.code_comb_segment4,
gct.code_comb_segment5,
gct.code_comb_segment6,
gct.code_comb_segment7,
gct.je_source_name,
gct.je_category,
To_char(gct.gl_posted_date, 'MM/DD/YYYY')
gl_date,
GCT.transaction_number
Transaction_Number,
(SELECT DISTINCT psv.vendor_name
FROM poz_suppliers_v psv
WHERE psv.vendor_id = peia.vendor_id)
supplier,
(SELECT aia.invoice_num
FROM ap_invoices_all aia
WHERE aia.invoice_id = peia.original_header_id)
Invoice_number,
(SELECT DISTINCT flv.meaning
FROM fnd_lookup_values flv,
ap_invoices_all aia
WHERE flv.lookup_type = 'INVOICE PAYMENT STATUS'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = aia.payment_status_flag
AND aia.invoice_id = peia.original_header_id)
PAYMENT_STATUS_FLAG,
/*(SELECT DISTINCT pha.segment1
FROM po_distributions_all pda,
po_headers_all pha
WHERE 1 = 1
AND ppab.project_id = pda.pjc_project_id
AND pda.po_header_id = pha.po_header_id
AND ROWNUM < 2)*/ NULL
po_number
,
ppab.segment1
project_number,
ppat.name
Project_name,
gct.description
Description,
gct.xla_accounted_entered_debit
entered_debit,
gct.xla_accounted_entered_credit
entered_credit
FROM gl_cmn_tbls gct,
xla_distribution_links xda,
pjc_cost_dist_lines_all pcdl,
xla_transaction_entities XTE,
pjc_exp_items_all peia,
pjf_projects_all_b ppab,
pjf_projects_all_tl ppat
WHERE xda.ae_header_id = gct.ae_header_id(+)
AND xda.ae_line_num = gct.ae_line_num(+)
AND gct.entity_id = XTE.entity_id
AND Gct.je_source_name = 'Project Accounting'
AND pcdl.acct_event_id = xda.event_id(+)
AND pcdl.expenditure_item_id =
xda.source_distribution_id_num_1(+)
AND pcdl.line_num = xda.source_distribution_id_num_2(+)
AND peia.expenditure_item_id = pcdl.expenditure_item_id
AND peia.project_id = ppab.project_id
AND ppab.project_id = ppat.project_id
UNION ALL
SELECT DISTINCT gct.code_comb_segment1,
gct.code_comb_segment2,
gct.code_comb_segment3,
gct.code_comb_segment4,
gct.code_comb_segment5,
gct.code_comb_segment6,
gct.code_comb_segment7,
gct.je_source_name,
gct.je_category,
To_char(gct.gl_posted_date, 'MM/DD/YYYY')
gl_date
,
gct.transaction_number
Transaction_Number,
aps.vendor_name
supplier
,
(SELECT DISTINCT aia.invoice_num
FROM rcv_shipment_lines rsl,
ap_invoices_all aia,
ap_invoice_lines_all aila
WHERE 1 = 1
AND rcv.shipment_header_id =
rsl.shipment_header_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND aila.po_header_id = rsl.po_header_id
AND aia.invoice_id = aila.invoice_id
AND rownum=1)
Invoice_number,
(SELECT DISTINCT flv.meaning
FROM fnd_lookup_values flv,
ap_invoice_lines_all aila,
ap_invoices_all aia,
rcv_shipment_lines rsl
WHERE flv.lookup_type = 'INVOICE PAYMENT STATUS'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = aia.payment_status_flag
AND aila.po_header_id = rsl.po_header_id
AND aila.invoice_id = aia.invoice_id
AND rcv.shipment_header_id =
rsl.shipment_header_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rownum=1)
PAYMENT_STATUS_FLAG,
(SELECT DISTINCT pha.segment1
FROM rcv_shipment_lines rsl,
po_lines_all pla,
po_headers_all pha
WHERE 1 = 1
AND rcv.shipment_header_id =
rsl.shipment_header_id
AND rsl.po_header_id = pla.po_header_id
AND rsl.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rownum=1)
po_number,
(SELECT DISTINCT ppab.segment1
FROM rcv_shipment_lines rsl,
pjf_projects_all_b ppab,
pjf_projects_all_tl ppat,
po_distributions_all pda
WHERE 1 = 1
AND rcv.shipment_header_id =
rsl.shipment_header_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND ppab.project_id = pda.pjc_project_id
AND pda.po_header_id = rsl.po_header_id
AND ppab.project_id = ppat.project_id
AND rownum=1)
project_number,
(SELECT DISTINCT ppat.name
FROM rcv_shipment_lines rsl,
pjf_projects_all_b ppab,
pjf_projects_all_tl ppat,
po_distributions_all pda
WHERE 1 = 1
AND rcv.shipment_header_id =
rsl.shipment_header_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND ppab.project_id = pda.pjc_project_id
AND pda.po_header_id = rsl.po_header_id
AND ppab.project_id = ppat.project_id
AND rownum=1)
Project_name,
gct.description
Description,
gct.xla_accounted_entered_debit
entered_debit,
gct.xla_accounted_entered_credit
entered_credit
FROM gl_cmn_tbls gct,
xla_transaction_entities xte,
rcv_shipment_headers rcv,
rcv_transactions RT,
poz_suppliers_v aps,
poz_supplier_sites_all_m poss
WHERE 1 = 1
AND To_char(rcv.receipt_num) = To_char(xte.source_id_int_1)
AND gct.transaction_number = xte.transaction_number
AND RT.shipment_header_id = rcv.shipment_header_id
AND rcv.vendor_id = APS.vendor_id(+)
AND aps.vendor_id = poss.vendor_id(+)
AND rcv.vendor_site_id = poss.vendor_site_id(+)
AND gct.je_source_name = 'Receipt Accounting')
ORDER BY je_source_name,
gl_date,
transaction_number,
je_category,
code_comb_segment1,
code_comb_segment2,
code_comb_segment3,
code_comb_segment4,
code_comb_segment5,
code_comb_segment6,
code_comb_segment7
No comments:
Post a Comment