Saturday, June 16, 2012

AP-XLA-GL I Have to Try .

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'

No comments:

Post a Comment