Hi Friends.
I have to try this Query.. Let me see which query will be work.
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 (XAH.EVENT_TYPE_CODE,'PAYMENT CANCELLED',AMOUNT* NVL(EXCHANGE_RATE,1),'REFUND RECORDED',XAL.ACCOUNTED_DR,
-- 0) RECEIPT,
-- DECODE (XAH.EVENT_TYPE_CODE,'PAYMENT CREATED',AMOUNT* NVL(EXCHANGE_RATE,1),0) PAYMENT
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 --------------------------------
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.PERIOD_NAME IN ('APR-11-12','MAY-11-12','JUN-11-12','JUL-11-12') AND
GJH.STATUS='P' AND
GJH.JE_SOURCE='Cash Management' AND
GJH.JE_CATEGORY='Bank Transfers'
UNION ALL
-------------------Data from Receivable --------------------------------
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 -----------------------
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----------
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')
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
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;
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
AP-SLA-GL LINK QUERY
AP-SLA-GL QUERY
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;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Hi,
Below is the SQL for AP->PO->SLA->GL for for *R12*.
========================
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
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
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;
Lets see what you have missed out:
1. You didn't use xla.xla_transaction_entities or xla_transaction_entities_upg.
You will hit the issue mentioned here.
2. Have ledger_id join in between xla.xla_transaction_entities and ap_invoices_all.
3. Use entity_code filter and nvl over source_id_int_1 (as mentioned in the link) for xla.xla_transaction_entities.
Also,
4. You may use application_id filter on all mentioned XLA tables as 200, as it is for invoice.
5. If you have transferred journal entries to GL in Summary mode, XLA will have the specific accounting entry of invoice than GL.
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
GL to AP (Payable) Query....
Q1:--------------------------------------------------------------------------------------------
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'
-------------------------------------------------------------------------------------------------------
Q2:- --------------------------------------------------------------------------------------------------
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'
