/* Formatted on 2013/11/28 16:07 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, asup.vendor_name,
TO_CHAR (aca.check_number), aca.check_date,
aca.doc_sequence_value voucher_number, aca.creation_date voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc,
ap_suppliers asup,
ap_checks_all aca
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND asup.vendor_id(+) = xal.party_id
AND aca.doc_sequence_id(+) = xah.doc_sequence_id
AND aca.doc_sequence_value(+) = xah.doc_sequence_value
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source = 'Payables'
UNION ALL
------ DATA FROM CASH MANAGEMENT --------------------------------
/* Formatted on 2013/11/28 16:16 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, '' vendor_name, '' check_number,
NULL check_date, NULL voucher_number, NULL voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source = 'Cash Management'
AND gjh.je_category = 'Bank Transfers'
UNION ALL
-------------------Data from Receivable --------------------------------
/* Formatted on 2013/11/28 16:07 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date,
(SELECT ac.customer_name
FROM ar_customers ac
WHERE ac.customer_id = xal.party_id) customer_name,
(SELECT acr.receipt_number
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value)
receipt_number,
(SELECT acr.receipt_date
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value) receipt_date,
(SELECT acr.doc_sequence_value
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value)
voucher_number,
(SELECT acr.creation_date
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value) voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla.xla_transaction_entities xte
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.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 xte.application_id = xah.application_id
AND xte.entity_id = xah.entity_id
AND gjl.status = 'P'
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.je_source = 'Receivables'
UNION ALL
---------------- Manual -----------------------
/* Formatted on 2013/11/28 16:08 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (gjl.accounted_dr, 0) accounted_dr,
NVL (gjl.accounted_cr, 0) accounted_cr,
gjl.description jv_line_description, '' event_type_code,
'' sla_description, NULL ae_line_num,
gjh.default_effective_date gl_date, '' vendor_name, '' check_number,
NULL check_date, NULL voucher_number, NULL voucher_date,
NVL (gjl.accounted_dr, 0) receipt, NVL (gjl.accounted_cr, 0) payment
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.status = 'P'
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.je_source = 'Manual'
UNION ALL
-----ALL OTHER SOURCES OTHER THAN ABOVE----------
/* Formatted on 2013/11/28 16:08 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, '' vendor_name, '' check_number,
NULL check_date, NULL voucher_number, NULL voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source NOT IN ('Receivables', 'Payables', 'Cash Management')
/* Formatted on 2013/11/28 16:08 (Formatter Plus v4.8.8) */
SELECT DISTINCT ai.invoice_num, ai.gl_date, xah.accounting_date,
gjh.je_category, gjh.je_source, gjh.period_name, gjh.status,
aid.invoice_line_number, aid.line_type_lookup_code,
ail.description, aid.amount, aid.dist_code_combination_id
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_import_references gir,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_events xe,
apps.xla_event_types_tl xet,
apps.xla_event_classes_tl xect,
apps.xla_distribution_links xdl,
apps.ap_invoice_distributions_all aid,
apps.ap_invoices_all ai,
apps.ap_invoice_lines_all ail
WHERE gjh.je_header_id = gjl.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 gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND xe.application_id = xet.application_id
AND xet.LANGUAGE = USERENV ('LANG')
AND xect.event_class_code = xet.event_class_code
AND xect.application_id = xe.application_id
AND xect.LANGUAGE = USERENV ('LANG')
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
AND ai.invoice_id = aid.invoice_id
AND ai.invoice_id = ail.invoice_id
AND ail.invoice_id = aid.invoice_id
AND aid.invoice_line_number = ail.line_number
AND xah.event_type_code <> ' MANUAL'
AND gjh.je_source = 'Payables'
AND ai.org_id = p_org_id
AND xah.accounting_date BETWEEN p_period_start_date
AND p_period_end_date;
/* Formatted on 2013/11/28 16:09 (Formatter Plus v4.8.8) */
SELECT aia.invoice_id "Invoice Id", aia.invoice_num "Invoice Number",
aia.invoice_date "Invoice Date", aia.invoice_amount "Amount",
xal.entered_dr "Entered DR in SLA", xal.entered_cr "Entered CR in SLA",
xal.accounted_dr "Accounted DR in SLA",
xal.accounted_cr "Accounted CR in SLA", gjh.je_source,
gjl.entered_dr "Entered DR in GL",
gjl.accounted_dr "Accounted DR in GL",
xal.accounting_class_code "Accounting Class",
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 "Code Combination",
aia.invoice_currency_code "Inv Curr Code",
aia.payment_currency_code "Pay Curr Code", aia.gl_date "GL Date",
xah.period_name "Period", aia.payment_method_code "Payment Method",
aia.vendor_id "Vendor Id", aps.vendor_name "Vendor Name",
xah.je_category_name "JE Category Name"
FROM apps.ap_invoices_all aia,
xla.xla_transaction_entities xte,
apps.xla_events xev,
apps.xla_ae_headers xah,
apps.xla_ae_lines xal,
apps.gl_import_references gir,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations gcc,
apps.ap_suppliers aps,
(SELECT aid1.invoice_id, pa.project_id,
NVL (pa.segment1, 'NO PROJECT') project
FROM apps.ap_invoice_distributions_all aid1,
apps.pa_projects_all pa
WHERE aid1.ROWID IN (SELECT MAX (ROWID)
FROM apps.ap_invoice_distributions_all aid2
WHERE aid1.invoice_id = aid2.invoice_id
GROUP BY aid1.invoice_id)
AND aid1.project_id = pa.project_id(+)) sql1,
(SELECT aid1.invoice_id, pt.task_id,
NVL (pt.task_number, 'NO TASK') task
FROM apps.ap_invoice_distributions_all aid1, apps.pa_tasks pt
WHERE aid1.ROWID IN (SELECT MAX (ROWID)
FROM apps.ap_invoice_distributions_all aid2
WHERE aid1.invoice_id = aid2.invoice_id
GROUP BY aid1.invoice_id)
AND aid1.task_id = pt.task_id(+)) sql2
WHERE aia.invoice_id = xte.source_id_int_1
AND aia.invoice_id = sql1.invoice_id
AND aia.invoice_id = sql2.invoice_id
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 XAH.je_category_name = 'Purchase Invoices'
AND xah.gl_transfer_status_code = 'Y'
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 gcc.code_combination_id = xal.code_combination_id
AND gcc.code_combination_id = gjl.code_combination_id
AND aia.vendor_id = aps.vendor_id
AND gjh.status = 'P'
AND gjh.actual_flag = 'A'
AND gjh.currency_code = 'USD'
AND aia.invoice_id = &invoice_id;
/* Formatted on 2013/11/28 16:10 (Formatter Plus v4.8.8) */
SELECT ph.segment1 po_number, aps.vendor_name, msi.segment1 item_number,
msi.description item_description, 'A/P PO Match' accrual_transaction,
'AP*' SOURCE,
(SELECT organization_name
FROM apps.org_organization_definitions
WHERE organization_id = pd.destination_organization_id) org,
ai.invoice_num doc_number, ai.invoice_date doc_date,
aid.invoice_line_number line, pl.unit_meas_lookup_code uom,
aid.quantity_invoiced quantity, aid.unit_price,
( NVL (xdl.unrounded_accounted_dr, 0)
- NVL (xdl.unrounded_accounted_cr, 0)
) accounted_amount,
(NVL (xdl.unrounded_entered_cr, 0) - NVL (xdl.unrounded_entered_cr, 0)
) entered_amount,
xal.currency_code
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_import_references gir,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_events xe,
apps.xla_event_types_tl xet,
apps.xla_event_classes_tl xect,
apps.xla_distribution_links xdl,
apps.ap_invoice_distributions_all aid,
apps.ap_invoices_all ai,
apps.po_headers_all ph,
apps.po_distributions_all pd,
apps.po_lines_all pl,
apps.ap_suppliers aps,
apps.mtl_system_items_b msi
WHERE gjh.je_header_id = gjl.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 gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND xe.application_id = xet.application_id
AND xet.LANGUAGE = USERENV ('LANG')
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.application_id = xe.application_id
AND xect.LANGUAGE = USERENV ('LANG')
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
--AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xdl.source_distribution_id_num_1 aid.invoice_distribution_id
AND ai.invoice_id = aid.invoice_id
AND aid.po_distribution_id = pd.po_distribution_id
AND gjh.je_source = 'Payables'
AND ph.po_header_id = pd.po_header_id
AND pl.po_header_id = ph.po_header_id
AND pd.po_line_id = pl.po_line_id
AND pd.org_id = 83
AND ph.org_id = 83
AND ai.org_id = 83
AND aid.org_id = 83
AND gjl.code_combination_id = 1011
AND ph.vendor_id = aps.vendor_id
AND msi.inventory_item_id = pl.item_id
AND msi.organization_id = pd.destination_organization_id
SELECT distinct
aia.INVOICE_ID "R12_Invoice_Id",
AIA.DOC_SEQUENCE_VALUE,
aia.INVOICE_NUM ,
aia.attribute6 "11i_INVOICE_ID",
aia.GL_DATE,
aia.INVOICE_AMOUNT,
xal.ACCOUNTED_DR "Accounted DR IN SLA",
xal.ACCOUNTED_CR "Accounted CR IN SLA",
gjl.ACCOUNTED_CR "ACCOUNTED_CR IN GL",
gjl.ACCOUNTED_DR "Accounted DR IN GL",
xev.event_type_code,
gcc.SEGMENT1
|| '.'
|| gcc.SEGMENT2
|| '.'
|| gcc.SEGMENT3
|| '.'
|| gcc.SEGMENT4
|| '.'
|| gcc.SEGMENT5
|| '.'
|| gcc.SEGMENT6
|| '.'
|| gcc.SEGMENT7
"CODE_COMBINATION",
aia.GL_DATE,
xah.PERIOD_NAME,
aia.VENDOR_ID "Vendor Id",
aps.VENDOR_NAME "Vendor Name",
xah.JE_CATEGORY_NAME "JE Category Name",
GJH.JE_SOURCE
FROM ap_invoices_all aia,
xla.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,
ap_suppliers aps
WHERE aia.INVOICE_ID = xte.source_id_int_1
and aia.ACCTS_PAY_CODE_COMBINATION_ID = gcc.code_combination_id
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 XAH.je_category_name = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND XAL.GL_SL_LINK_ID = gir.GL_SL_LINK_ID
and gir.GL_SL_LINK_ID = gjl.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 gjl.ledger_id = gjh.ledger_id
and xah.ledger_id = gjh.ledger_id
AND gjh.JE_HEADER_ID = gir.JE_HEADER_ID
and aia.set_of_books_id = gjh.ledger_id
AND gjl.JE_HEADER_ID = gir.JE_HEADER_ID
AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM
AND gcc.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID
AND gcc.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID
AND aia.VENDOR_ID = aps.VENDOR_ID
AND gjh.PERIOD_NAME BETWEEN NVL (:PERIOD_FROM, gjh.PERIOD_NAME)
AND NVL (:PERIOD_TO, gjh.PERIOD_NAME)
AND gcc.SEGMENT1 = NVL (:seg1, gcc.SEGMENT1)
AND gcc.SEGMENT3 = NVL (:seg, gcc.SEGMENT3)
ORDER BY 1, aia.GL_DATE
/* Formatted on 2013/11/28 16:17 (Formatter Plus v4.8.8) */
SELECT glcc.concatenated_segments account_number, invoice.gl_date gl_date,
je_lines.entered_dr entered_dr, je_lines.entered_cr entered_cr,
je_lines.accounted_dr accounted_dr, je_lines.accounted_cr accounted_cr
FROM gl_je_lines je_lines,
gl_import_references gl_ref,
xla_ae_lines xla_lines,
xla_ae_headers xla_headers,
xla_events xla_events,
xla_transaction_entities xla_trans,
ap_invoices_all invoice,
gl_je_headers h,
gl_code_combinations_kfv glcc
WHERE je_lines.je_header_id = gl_ref.je_header_id
AND je_lines.je_line_num = gl_ref.je_line_num
AND gl_ref.gl_sl_link_table = xla_lines.gl_sl_link_table
AND gl_ref.gl_sl_link_id = xla_lines.gl_sl_link_id
AND xla_lines.application_id = xla_headers.application_id
AND xla_lines.ae_header_id = xla_headers.ae_header_id
AND xla_headers.application_id = xla_events.application_id
AND xla_headers.event_id = xla_events.event_id
AND h.actual_flag = 'A'
AND xla_trans.application_id = xla_events.application_id
AND xla_trans.entity_id = xla_events.entity_id
--and invoice.invoice_num = xla_trans.transaction_number
AND invoice.invoice_id = xla_trans.source_id_int_1
AND invoice.invoice_id = 10020
AND h.je_source = 'Payables'
AND xla_lines.code_combination_id = glcc.code_combination_id
AND je_lines.je_header_id = h.je_header_id;
/* Formatted on 2013/11/28 16:12 (Formatter Plus v4.8.8) */
SELECT gjh.NAME, gjh.description,
TO_CHAR (gjh.default_effective_date, 'dd-MON-yyyy') eff_date,
DECODE (xte.entity_code,
'AP_INVOICES', pv.vendor_name,
(SELECT ac.vendor_name
FROM ap_checks_all ac
WHERE xte.source_id_int_1 = ac.check_id)
) party,
aia.invoice_num doc_sequence_value, gjh.je_category,
xal.accounted_dr accounted_dr, xal.accounted_cr accounted_cr,
gjl.je_header_id, xal.party_type_code, gjh.je_source, gjh.period_name,
gcc.segment5, gjl.je_line_num, gjh.default_effective_date
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla.xla_transaction_entities xte,
ap_invoices_all aia,
po_vendors pv
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.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 xte.application_id = xah.application_id
AND xte.entity_id = xah.entity_id
AND aia.invoice_id(+) = xte.source_id_int_1
AND aia.vendor_id = pv.vendor_id(+)
AND gjl.status = 'P'
AND gcc.segment5 = NVL (:account_id, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:period_from, TRUNC (gjh.default_effective_date))
AND NVL (:period_to, TRUNC (gjh.default_effective_date))
AND gjh.je_source = 'Payables'
/* Formatted on 2013/11/28 16:12 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, asup.vendor_name,
TO_CHAR (aca.check_number), aca.check_date,
aca.doc_sequence_value voucher_number, aca.creation_date voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc,
ap_suppliers asup,
ap_checks_all aca
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND asup.vendor_id(+) = xal.party_id
AND aca.doc_sequence_id(+) = xah.doc_sequence_id
AND aca.doc_sequence_value(+) = xah.doc_sequence_value
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source = 'Payables'
---------------------------------------------------------------------------------------------------------
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, asup.vendor_name,
TO_CHAR (aca.check_number), aca.check_date,
aca.doc_sequence_value voucher_number, aca.creation_date voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc,
ap_suppliers asup,
ap_checks_all aca
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND asup.vendor_id(+) = xal.party_id
AND aca.doc_sequence_id(+) = xah.doc_sequence_id
AND aca.doc_sequence_value(+) = xah.doc_sequence_value
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source = 'Payables'
UNION ALL
------ DATA FROM CASH MANAGEMENT --------------------------------
/* Formatted on 2013/11/28 16:16 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, '' vendor_name, '' check_number,
NULL check_date, NULL voucher_number, NULL voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source = 'Cash Management'
AND gjh.je_category = 'Bank Transfers'
UNION ALL
-------------------Data from Receivable --------------------------------
/* Formatted on 2013/11/28 16:07 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date,
(SELECT ac.customer_name
FROM ar_customers ac
WHERE ac.customer_id = xal.party_id) customer_name,
(SELECT acr.receipt_number
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value)
receipt_number,
(SELECT acr.receipt_date
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value) receipt_date,
(SELECT acr.doc_sequence_value
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value)
voucher_number,
(SELECT acr.creation_date
FROM ar_cash_receipts_all acr
WHERE acr.doc_sequence_id = xah.doc_sequence_id
AND acr.doc_sequence_value = xah.doc_sequence_value) voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla.xla_transaction_entities xte
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.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 xte.application_id = xah.application_id
AND xte.entity_id = xah.entity_id
AND gjl.status = 'P'
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.je_source = 'Receivables'
UNION ALL
---------------- Manual -----------------------
/* Formatted on 2013/11/28 16:08 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (gjl.accounted_dr, 0) accounted_dr,
NVL (gjl.accounted_cr, 0) accounted_cr,
gjl.description jv_line_description, '' event_type_code,
'' sla_description, NULL ae_line_num,
gjh.default_effective_date gl_date, '' vendor_name, '' check_number,
NULL check_date, NULL voucher_number, NULL voucher_date,
NVL (gjl.accounted_dr, 0) receipt, NVL (gjl.accounted_cr, 0) payment
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.status = 'P'
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.je_source = 'Manual'
UNION ALL
-----ALL OTHER SOURCES OTHER THAN ABOVE----------
/* Formatted on 2013/11/28 16:08 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, '' vendor_name, '' check_number,
NULL check_date, NULL voucher_number, NULL voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source NOT IN ('Receivables', 'Payables', 'Cash Management')
/* Formatted on 2013/11/28 16:08 (Formatter Plus v4.8.8) */
SELECT DISTINCT ai.invoice_num, ai.gl_date, xah.accounting_date,
gjh.je_category, gjh.je_source, gjh.period_name, gjh.status,
aid.invoice_line_number, aid.line_type_lookup_code,
ail.description, aid.amount, aid.dist_code_combination_id
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_import_references gir,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_events xe,
apps.xla_event_types_tl xet,
apps.xla_event_classes_tl xect,
apps.xla_distribution_links xdl,
apps.ap_invoice_distributions_all aid,
apps.ap_invoices_all ai,
apps.ap_invoice_lines_all ail
WHERE gjh.je_header_id = gjl.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 gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND xe.application_id = xet.application_id
AND xet.LANGUAGE = USERENV ('LANG')
AND xect.event_class_code = xet.event_class_code
AND xect.application_id = xe.application_id
AND xect.LANGUAGE = USERENV ('LANG')
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
AND ai.invoice_id = aid.invoice_id
AND ai.invoice_id = ail.invoice_id
AND ail.invoice_id = aid.invoice_id
AND aid.invoice_line_number = ail.line_number
AND xah.event_type_code <> ' MANUAL'
AND gjh.je_source = 'Payables'
AND ai.org_id = p_org_id
AND xah.accounting_date BETWEEN p_period_start_date
AND p_period_end_date;
/* Formatted on 2013/11/28 16:09 (Formatter Plus v4.8.8) */
SELECT aia.invoice_id "Invoice Id", aia.invoice_num "Invoice Number",
aia.invoice_date "Invoice Date", aia.invoice_amount "Amount",
xal.entered_dr "Entered DR in SLA", xal.entered_cr "Entered CR in SLA",
xal.accounted_dr "Accounted DR in SLA",
xal.accounted_cr "Accounted CR in SLA", gjh.je_source,
gjl.entered_dr "Entered DR in GL",
gjl.accounted_dr "Accounted DR in GL",
xal.accounting_class_code "Accounting Class",
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 "Code Combination",
aia.invoice_currency_code "Inv Curr Code",
aia.payment_currency_code "Pay Curr Code", aia.gl_date "GL Date",
xah.period_name "Period", aia.payment_method_code "Payment Method",
aia.vendor_id "Vendor Id", aps.vendor_name "Vendor Name",
xah.je_category_name "JE Category Name"
FROM apps.ap_invoices_all aia,
xla.xla_transaction_entities xte,
apps.xla_events xev,
apps.xla_ae_headers xah,
apps.xla_ae_lines xal,
apps.gl_import_references gir,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations gcc,
apps.ap_suppliers aps,
(SELECT aid1.invoice_id, pa.project_id,
NVL (pa.segment1, 'NO PROJECT') project
FROM apps.ap_invoice_distributions_all aid1,
apps.pa_projects_all pa
WHERE aid1.ROWID IN (SELECT MAX (ROWID)
FROM apps.ap_invoice_distributions_all aid2
WHERE aid1.invoice_id = aid2.invoice_id
GROUP BY aid1.invoice_id)
AND aid1.project_id = pa.project_id(+)) sql1,
(SELECT aid1.invoice_id, pt.task_id,
NVL (pt.task_number, 'NO TASK') task
FROM apps.ap_invoice_distributions_all aid1, apps.pa_tasks pt
WHERE aid1.ROWID IN (SELECT MAX (ROWID)
FROM apps.ap_invoice_distributions_all aid2
WHERE aid1.invoice_id = aid2.invoice_id
GROUP BY aid1.invoice_id)
AND aid1.task_id = pt.task_id(+)) sql2
WHERE aia.invoice_id = xte.source_id_int_1
AND aia.invoice_id = sql1.invoice_id
AND aia.invoice_id = sql2.invoice_id
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 XAH.je_category_name = 'Purchase Invoices'
AND xah.gl_transfer_status_code = 'Y'
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 gcc.code_combination_id = xal.code_combination_id
AND gcc.code_combination_id = gjl.code_combination_id
AND aia.vendor_id = aps.vendor_id
AND gjh.status = 'P'
AND gjh.actual_flag = 'A'
AND gjh.currency_code = 'USD'
AND aia.invoice_id = &invoice_id;
/* Formatted on 2013/11/28 16:10 (Formatter Plus v4.8.8) */
SELECT ph.segment1 po_number, aps.vendor_name, msi.segment1 item_number,
msi.description item_description, 'A/P PO Match' accrual_transaction,
'AP*' SOURCE,
(SELECT organization_name
FROM apps.org_organization_definitions
WHERE organization_id = pd.destination_organization_id) org,
ai.invoice_num doc_number, ai.invoice_date doc_date,
aid.invoice_line_number line, pl.unit_meas_lookup_code uom,
aid.quantity_invoiced quantity, aid.unit_price,
( NVL (xdl.unrounded_accounted_dr, 0)
- NVL (xdl.unrounded_accounted_cr, 0)
) accounted_amount,
(NVL (xdl.unrounded_entered_cr, 0) - NVL (xdl.unrounded_entered_cr, 0)
) entered_amount,
xal.currency_code
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_import_references gir,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_events xe,
apps.xla_event_types_tl xet,
apps.xla_event_classes_tl xect,
apps.xla_distribution_links xdl,
apps.ap_invoice_distributions_all aid,
apps.ap_invoices_all ai,
apps.po_headers_all ph,
apps.po_distributions_all pd,
apps.po_lines_all pl,
apps.ap_suppliers aps,
apps.mtl_system_items_b msi
WHERE gjh.je_header_id = gjl.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 gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND xe.application_id = xet.application_id
AND xet.LANGUAGE = USERENV ('LANG')
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.application_id = xe.application_id
AND xect.LANGUAGE = USERENV ('LANG')
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
--AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xdl.source_distribution_id_num_1 aid.invoice_distribution_id
AND ai.invoice_id = aid.invoice_id
AND aid.po_distribution_id = pd.po_distribution_id
AND gjh.je_source = 'Payables'
AND ph.po_header_id = pd.po_header_id
AND pl.po_header_id = ph.po_header_id
AND pd.po_line_id = pl.po_line_id
AND pd.org_id = 83
AND ph.org_id = 83
AND ai.org_id = 83
AND aid.org_id = 83
AND gjl.code_combination_id = 1011
AND ph.vendor_id = aps.vendor_id
AND msi.inventory_item_id = pl.item_id
AND msi.organization_id = pd.destination_organization_id
SELECT distinct
aia.INVOICE_ID "R12_Invoice_Id",
AIA.DOC_SEQUENCE_VALUE,
aia.INVOICE_NUM ,
aia.attribute6 "11i_INVOICE_ID",
aia.GL_DATE,
aia.INVOICE_AMOUNT,
xal.ACCOUNTED_DR "Accounted DR IN SLA",
xal.ACCOUNTED_CR "Accounted CR IN SLA",
gjl.ACCOUNTED_CR "ACCOUNTED_CR IN GL",
gjl.ACCOUNTED_DR "Accounted DR IN GL",
xev.event_type_code,
gcc.SEGMENT1
|| '.'
|| gcc.SEGMENT2
|| '.'
|| gcc.SEGMENT3
|| '.'
|| gcc.SEGMENT4
|| '.'
|| gcc.SEGMENT5
|| '.'
|| gcc.SEGMENT6
|| '.'
|| gcc.SEGMENT7
"CODE_COMBINATION",
aia.GL_DATE,
xah.PERIOD_NAME,
aia.VENDOR_ID "Vendor Id",
aps.VENDOR_NAME "Vendor Name",
xah.JE_CATEGORY_NAME "JE Category Name",
GJH.JE_SOURCE
FROM ap_invoices_all aia,
xla.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,
ap_suppliers aps
WHERE aia.INVOICE_ID = xte.source_id_int_1
and aia.ACCTS_PAY_CODE_COMBINATION_ID = gcc.code_combination_id
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 XAH.je_category_name = 'Purchase Invoices'
AND GJH.JE_SOURCE = 'Payables'
AND XAL.GL_SL_LINK_ID = gir.GL_SL_LINK_ID
and gir.GL_SL_LINK_ID = gjl.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 gjl.ledger_id = gjh.ledger_id
and xah.ledger_id = gjh.ledger_id
AND gjh.JE_HEADER_ID = gir.JE_HEADER_ID
and aia.set_of_books_id = gjh.ledger_id
AND gjl.JE_HEADER_ID = gir.JE_HEADER_ID
AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM
AND gcc.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID
AND gcc.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID
AND aia.VENDOR_ID = aps.VENDOR_ID
AND gjh.PERIOD_NAME BETWEEN NVL (:PERIOD_FROM, gjh.PERIOD_NAME)
AND NVL (:PERIOD_TO, gjh.PERIOD_NAME)
AND gcc.SEGMENT1 = NVL (:seg1, gcc.SEGMENT1)
AND gcc.SEGMENT3 = NVL (:seg, gcc.SEGMENT3)
ORDER BY 1, aia.GL_DATE
/* Formatted on 2013/11/28 16:17 (Formatter Plus v4.8.8) */
SELECT glcc.concatenated_segments account_number, invoice.gl_date gl_date,
je_lines.entered_dr entered_dr, je_lines.entered_cr entered_cr,
je_lines.accounted_dr accounted_dr, je_lines.accounted_cr accounted_cr
FROM gl_je_lines je_lines,
gl_import_references gl_ref,
xla_ae_lines xla_lines,
xla_ae_headers xla_headers,
xla_events xla_events,
xla_transaction_entities xla_trans,
ap_invoices_all invoice,
gl_je_headers h,
gl_code_combinations_kfv glcc
WHERE je_lines.je_header_id = gl_ref.je_header_id
AND je_lines.je_line_num = gl_ref.je_line_num
AND gl_ref.gl_sl_link_table = xla_lines.gl_sl_link_table
AND gl_ref.gl_sl_link_id = xla_lines.gl_sl_link_id
AND xla_lines.application_id = xla_headers.application_id
AND xla_lines.ae_header_id = xla_headers.ae_header_id
AND xla_headers.application_id = xla_events.application_id
AND xla_headers.event_id = xla_events.event_id
AND h.actual_flag = 'A'
AND xla_trans.application_id = xla_events.application_id
AND xla_trans.entity_id = xla_events.entity_id
--and invoice.invoice_num = xla_trans.transaction_number
AND invoice.invoice_id = xla_trans.source_id_int_1
AND invoice.invoice_id = 10020
AND h.je_source = 'Payables'
AND xla_lines.code_combination_id = glcc.code_combination_id
AND je_lines.je_header_id = h.je_header_id;
/* Formatted on 2013/11/28 16:12 (Formatter Plus v4.8.8) */
SELECT gjh.NAME, gjh.description,
TO_CHAR (gjh.default_effective_date, 'dd-MON-yyyy') eff_date,
DECODE (xte.entity_code,
'AP_INVOICES', pv.vendor_name,
(SELECT ac.vendor_name
FROM ap_checks_all ac
WHERE xte.source_id_int_1 = ac.check_id)
) party,
aia.invoice_num doc_sequence_value, gjh.je_category,
xal.accounted_dr accounted_dr, xal.accounted_cr accounted_cr,
gjl.je_header_id, xal.party_type_code, gjh.je_source, gjh.period_name,
gcc.segment5, gjl.je_line_num, gjh.default_effective_date
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla.xla_transaction_entities xte,
ap_invoices_all aia,
po_vendors pv
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.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 xte.application_id = xah.application_id
AND xte.entity_id = xah.entity_id
AND aia.invoice_id(+) = xte.source_id_int_1
AND aia.vendor_id = pv.vendor_id(+)
AND gjl.status = 'P'
AND gcc.segment5 = NVL (:account_id, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:period_from, TRUNC (gjh.default_effective_date))
AND NVL (:period_to, TRUNC (gjh.default_effective_date))
AND gjh.je_source = 'Payables'
/* Formatted on 2013/11/28 16:12 (Formatter Plus v4.8.8) */
SELECT gjh.description jv_header_description, gjh.NAME jv_name,
gjh.je_category, gjh.je_source, gjh.period_name,
NVL (xal.accounted_cr, 0) gl_cr, NVL (xal.accounted_dr, 0) gl_dr,
gjl.description jv_line_description, xah.event_type_code,
xah.description sla_description, xal.ae_line_num,
xal.accounting_date gl_date, asup.vendor_name,
TO_CHAR (aca.check_number), aca.check_date,
aca.doc_sequence_value voucher_number, aca.creation_date voucher_date,
DECODE (xal.accounted_cr, NULL, xal.accounted_dr, 0) receipt,
DECODE (xal.accounted_dr, NULL, xal.accounted_cr, 0) payment
FROM xla_ae_headers xah,
xla_ae_lines xal,
gl_je_lines gjl,
gl_import_references gir,
gl_je_headers gjh,
gl_code_combinations gcc,
ap_suppliers asup,
ap_checks_all aca
WHERE xah.ae_header_id = xal.ae_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.je_header_id = gir.je_header_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND asup.vendor_id(+) = xal.party_id
AND aca.doc_sequence_id(+) = xah.doc_sequence_id
AND aca.doc_sequence_value(+) = xah.doc_sequence_value
AND gcc.segment5 = NVL (:p_acc_num, gcc.segment5)
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (:p_from_date, TRUNC (gjh.default_effective_date))
AND NVL (:p_to_date, TRUNC (gjh.default_effective_date))
AND gjh.status = 'P'
AND gjh.je_source = 'Payables'
---------------------------------------------------------------------------------------------------------
No comments:
Post a Comment