R12 Oracle Apps

Monday, September 3, 2012

AP TO GL AND AR TO GL Reconsolation Query

SELECT distinct ps.period_name,
gjl.reference_2,
TO_CHAR (NULL) po_order_number,
       TO_CHAR (NULL) "Receipt Number",
       TO_DATE (NULL) "Receipt Date",
       TO_CHAR (NULL) "Invoice Number",
       TO_DATE (NULL) "Invoice Date",
       TO_CHAR (NULL) "Vendor Name",
       TO_CHAR (NULL) "Vendor Number",
       je_source "Source",
       gjh.ledger_id "set_of_books_id" ,
        fnd_flex_ext.get_segs('SQLGL','GL#',gscv.chart_of_accounts_id, gscv.code_combination_id) Segment,
        fnd_flex_ext.get_segs('SQLGL','GL#',gscv.chart_of_accounts_id, gscv.code_combination_id) Segment2,
         gjh.currency_code,
       gjl.entered_dr ,
        gjl.entered_cr,
      TO_NUMBER(DECODE ( lr.relationship_type_code, 'BALANCE', NULL, gjl.accounted_dr )) accounted_dr,
    TO_NUMBER(DECODE ( lr.relationship_type_code, 'BALANCE', NULL, gjl.accounted_cr )) accounted_cr,
    gjh.JE_HEADER_ID,
      gjl.je_line_num,
    gjl.code_combination_id ,
       gjh.DOC_SEQUENCE_VALUE "Vocher Number",
    gjh.default_effective_date gl_date,
      gjl.attribute1 "Staff Number",
   gjl.attribute2 "Project",
      gjl.attribute3 "Customer Num/Name" ,
    gjl.DESCRIPTION description,
     null attribute_category,
null project_name,
null task_name,
null expenditure_item_date,
null expenditure_type,
null expenditure_org,
null customer_name,
null bpo_project_name,
null staff_num
FROM gl_je_lines gjl , gl_je_headers gjh, gl_summary_combinations_v gscv, gl_ledgers,gl_period_statuses ps,gl_je_batches b,
gl_ledger_relationships lr
WHERE gscv.code_combination_id =
                                gjl.code_combination_id
   AND gjh.je_header_id=gjl.je_header_id
   AND gjl.period_name       = ps.period_name
   AND ps.ledger_id            = gjl.ledger_id
   AND b.je_batch_id           = gjh.je_batch_id
   AND gjh.ledger_id             = lr.source_ledger_id
   AND lr.source_ledger_id = lr.target_ledger_id
    AND b.actual_flag = 'A'
   AND b.status || '' = 'P'
   AND gjh.currency_code != 'STAT'
AND je_source NOT IN ('Payables','Cost Management','Consolidation','Receivables')
   AND gjh.ledger_id = gl_ledgers.ledger_id
--- AND gl_ledgers.suspense_allowed_flag = 'P'
AND (    NVL (ps.effective_period_num, 0) >= :cf_period_from
        AND (NVL (ps.effective_period_num, 0) <= :cf_period_to)
       )
     AND (    NVL (gscv.segment5, 0) >= :p_account_from
        AND (NVL (gscv.segment5, 0) <= :p_account_to)
       )
     AND (    NVL (gscv.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (gscv.segment1, 0) <= :P_COMPANY_TO)
       )
and gjh.Ledger_ID not in ( 1014,1015,1016 )
AND gjh.currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',gjh.currency_code,
                                              'NON INR',decode(gjh.currency_code,'INR','###',gjh.currency_code))

UNION ALL

SELECT distinct b.period_name, d.reference_2, aaa.po_order_number, aaa.rcv_receipt_num "Receipt Number",
       aaa.trx_date "Receipt Date", aaa.trx_number_displayed "Invoice Number",
       aaa.accounting_date "Invoice Date", aaa.third_party_name "Vendor Name",
       aaa.third_party_number "Vendor Number", b.je_source "Source", b.ledger_id "set_of_books_id" ,
        fnd_flex_ext.get_segs('SQLGL','GL#',ccc.chart_of_accounts_id, ccc.code_combination_id) Segment,
        fnd_flex_ext.get_segs('SQLGL','GL#',ccc.chart_of_accounts_id, ccc.code_combination_id) segment2,
       aaa.currency_code, aaa.entered_dr, aaa.entered_cr, aaa.accounted_dr,
       aaa.accounted_cr, aaa.je_header_id, aaa.je_line_num, aaa.code_combination_id,
       aaa.doc_sequence_value "Vocher Number", aaa.gl_date "GL_Date",
       aaa.attribute1 "Staff Number", aaa.attribute2 "Project",
       aaa.attribute3 "Customer Num/Name", aaa.description "Description" ,null attribute_category,
null project_name,
null task_name,
null expenditure_item_date,
null expenditure_type,
null expenditure_org,
null customer_name,
null bpo_project_name,
null staff_num
FROM (SELECT distinct poh.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
               rct.transaction_date trx_date, api.invoice_num trx_number_displayed,
               rrs.accounting_date accounting_date,
               pov.vendor_name third_party_name,
               pov.segment1 third_party_number, rrs.currency_code,
               rrs.entered_dr, rrs.entered_cr, rrs.accounted_dr,
               rrs.accounted_cr, r.je_header_id, r.je_line_num,
               rrs.code_combination_id, 201 application_id,
               TO_DATE (NULL) gl_date, TO_NUMBER (NULL) doc_sequence_value,
               TO_CHAR (NULL) description, TO_CHAR (NULL) attribute1,
               TO_CHAR (NULL) attribute2, TO_CHAR (NULL) attribute3
               --poh.org_id
          FROM   po_headers_all poh, po_lines_all pl, po_distributions_all pod
, rcv_shipment_lines rsl, rcv_shipment_headers rsh, rcv_transactions rct, rcv_receiving_sub_ledger rrs
, ap_invoice_distributions_all aida,ap_invoices_all api , xla_ae_headers xah , xla_ae_lines xal
,gl_import_references r
,ap_suppliers POV
     WHERE 1=1
AND poh.po_header_id=pl.po_header_id
AND poh.po_header_id=pod.po_header_id
AND poh.po_header_id= rsl.po_header_id(+)
AND rsl.shipment_header_id=rsh.shipment_header_id(+)
AND rsh.shipment_header_id=rct.shipment_header_id(+)
AND pod.po_distribution_id= aida.po_distribution_id(+)
AND api.invoice_id(+)=aida.invoice_id
AND pod.po_distribution_id = rrs.reference3
AND rct.transaction_id = rrs.rcv_transaction_id
AND aida.accounting_event_id=xah.event_id(+)
AND xah.ae_header_id=xal.ae_header_id(+)
AND xal.gl_sl_link_id= r.gl_sl_link_id(+)
and r.reference_8=xal.ae_line_num
AND poh.vendor_id=pov.vendor_id
AND aida.line_type_lookup_code(+)='ACCRUAL'
AND rrs.accounting_line_type='Accrual'
AND xal.accounting_class_code(+)='LIABILITY'
AND pod.destination_type_code='EXPENSE'
           and exists (select 1
                 from ap_invoice_distributions_all aid,ap_invoices_all ai
        where aid.po_distribution_id=pod.po_distribution_id
        AND ai.invoice_id = aid.invoice_id
                       AND ai.vendor_id = pov.vendor_id) )           aaa,
       gl_je_headers b, gl_je_lines d,gl_je_batches e, gl_Period_statuses ps,
       gl_summary_combinations_v         ccc
WHERE b.je_header_id = aaa.je_header_id
   AND d.je_line_num = aaa.je_line_num
   AND b.je_header_id=d.je_header_id
   AND d.code_combination_id = ccc.code_combination_id
   AND aaa.code_combination_id = d.code_combination_id
   AND ps.ledger_id            = d.ledger_id
   AND b.actual_flag = 'A'
   AND b.status || '' = 'P'
   AND b.currency_code != 'STAT'
     AND je_source NOT IN ('Payables', 'Purchasing','Consolidation')
--AND     aaa.org_id=:P_ORG
AND (    NVL (ps.effective_period_num, 0) >= :cf_period_from
        AND (NVL (ps.effective_period_num, 0) <= :cf_period_to)
       )
      AND (    NVL (ccc.segment5, 0) >= :p_account_from
        AND (NVL (ccc.segment5, 0) <= :p_account_to)
       )

     AND (    NVL (ccc.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (ccc.segment1, 0) <= :P_COMPANY_TO)
       )
and b.ledger_ID not in ( 1014,1015,1016 )
AND b.currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',b.currency_code,
                                              'NON INR',decode(b.currency_code,'INR','###',b.currency_code))

UNION ALL

--PO-RECEIPT-GL
select gps.period_name,
        l.reference_2,
        pha.segment1 po_order_number,
        rsh.receipt_num "Receipt Number",
        rt.transaction_date "Receipt Date",
        null   "Invoice Number",
        null   "Invoice Date",
        pv.vendor_name "Vendor Name",
        pv.segment1 "Vendor Number",
        h.je_source "Source",
        h.ledger_id "set_of_books_id" ,
        gcc.concatenated_segments Segment,
        gcc.concatenated_segments segment2,
        pha.currency_code,
--       pla.line_num,
--       plla.shipment_num,
--       pla.item_description,
--       pla.unit_price,
--       plla.quantity,
--       plla.quantity_received,
--       plla.quantity_billed,
--       rt.transaction_type,
       --rt.transaction_date,
       xal.entered_dr,
       xal.entered_cr,
       xal.accounted_dr,
       xal.accounted_cr,
       gir.je_header_id,
       gir.je_line_num,
       rrsl.code_combination_id,
       --gps.application_id,
       --
       TO_NUMBER (NULL) "Vocher Number",
TO_DATE (NULL) gl_date,
                TO_CHAR (NULL) "Staff Number",
               TO_CHAR (NULL) "Project",
               TO_CHAR (NULL) "Customer Num/Name" ,
               --pha.org_id,
--               TO_CHAR (NULL) description
               rsl.item_description "Description",null attribute_category,
null project_name,
null task_name,
null expenditure_item_date,
null expenditure_type,
null expenditure_org,
null customer_name,
null bpo_project_name,
null staff_num
from gl.gl_je_headers            h,
       gl.gl_je_lines                l,
       gl_code_combinations_kfv     gcc,
       gl.GL_IMPORT_REFERENCES     gir,
       apps.xla_ae_lines             xal,
       apps.XLA_DISTRIBUTION_LINKS   xdl,
       po.RCV_RECEIVING_SUB_LEDGER rrsl,
       po.rcv_transactions rt,
       po.po_headers_all pha,
       po.po_lines_all pla,
       po.po_line_locations_all plla,
       apps.po_vendors pv,
       rcv_shipment_headers rsh,
       rcv_shipment_lines rsl,
       gl_period_statuses gps
where 1=1
--and pha.org_id=:P_ORG
and h.je_header_id = l.je_header_id
   and l.code_combination_id = gcc.code_combination_id
   --and gcc.segment1 = '111'
     AND (    NVL (gcc.segment5, 0) >=:p_account_from
        AND (NVL (gcc.segment5, 0) <= :p_account_to)
       )

     AND (    NVL (gcc.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (gcc.segment1, 0) <= :P_COMPANY_TO)
       )
   --and gcc.segment2 in ('21200101', '21200201')
   and l.je_header_id = gir.je_header_id
   and l.je_line_num = gir.je_line_num
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xal.ae_header_id = xdl.ae_header_id
   and xal.ae_line_num = xdl.ae_line_num
   and xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id
   and rrsl.rcv_transaction_id = rt.transaction_id
   and rt.po_header_id = pha.po_header_id
   and rt.po_header_id = pla.po_header_id
   and rt.po_line_id = pla.po_line_id
   and pla.po_header_id = plla.po_header_id
   and pla.po_line_id = plla.po_line_id
   and pha.vendor_id = pv.vendor_id
   --and h.ledger_id = 2041--UTSC
   and h.je_source = 'Cost Management'
   and rt.shipment_header_id=rsh.shipment_header_id
   and rsh.shipment_header_id=rsl.shipment_header_id
   and rt.shipment_line_id =rsl.shipment_line_id
   and l.period_name       = gps.period_name
   and gps.ledger_id            = l.ledger_id
   and gps.application_id       = 101
AND (    NVL (gps.effective_period_num, 0) >=:cf_period_from
        AND (NVL (gps.effective_period_num, 0) <=:cf_period_to)
             )

   --and h.je_category = 'Receiving'
   --and h.status = 'P'
   --and h.period_name in ('MAY-12')
and h.ledger_ID not in ( 1014,1015,1016 )
AND   h.currency_code != 'STAT'
AND   h.currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',h.currency_code,
                                              'NON INR',decode(h.currency_code,'INR','###',h.currency_code))

union all

select
gps.period_name,
gjl.reference_2,
(select distinct pha.segment1 from ap_invoice_lines_all ailla, po_headers_all pha where ailla.invoice_id=aia.invoice_id and ailla.po_header_id=pha.po_header_id) po_order_number,

(select distinct rsh.receipt_num
from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
where rsh.shipment_header_id=rsl.shipment_header_id
and rsl.shipment_header_id=rt.shipment_header_id
and rsl.shipment_line_id=rt.shipment_line_id
and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null) ) "Receipt Number",

(select distinct rt.transaction_date
from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
where rsh.shipment_header_id=rsl.shipment_header_id
and rsl.shipment_header_id=rt.shipment_header_id
and rsl.shipment_line_id=rt.shipment_line_id
and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null)) "Receipt Date",

        aia.invoice_num   "Invoice Number",
        aia.gl_date   "Invoice Date",
        aps.vendor_name "Vendor Name",
        aps.segment1 "Vendor Number",
        gjh.je_source "Source",
        gjh.ledger_id "set_of_books_id" ,
        cc.concatenated_segments Segment,
        cc.concatenated_segments segment2,
        aia.payment_currency_code currency_code,
       xal.entered_dr,
       xal.entered_cr,
       xal.accounted_dr,
       xal.accounted_cr,
       gir.je_header_id,
       gir.je_line_num,
       cc.code_combination_id,
       --gps.application_id,
       --
       aia.doc_sequence_value "Vocher Number"
          ,aia.gl_date "GL_Date"
          ,NULL "Staff Number"
          ,null "Project"
          ,null "Customer Num/Name"
          ,xal.description "Description",

          (SELECT distinct aida.attribute_category FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) attribute_category,

(SELECT distinct ppa.name FROM ap_invoice_distributions_all aida,xla_distribution_links xdl,pa_projects_all ppa
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and aida.project_id=ppa.project_id
) project_name,

(SELECT distinct pt.task_name FROM ap_invoice_distributions_all aida,xla_distribution_links xdl,pa_tasks pt
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and aida.project_id=pt.project_id
and aida.task_id=pt.task_id
) task_name,
(SELECT distinct aida.expenditure_item_date FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) expenditure_item_date,
(SELECT distinct aida.expenditure_type FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) expenditure_type,
(SELECT distinct hou.name FROM ap_invoice_distributions_all aida,xla_distribution_links xdl,hr_operating_units hou
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and aida.expenditure_organization_id= hou.organization_id

) expenditure_org,
(SELECT distinct aida.attribute2 FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) customer_name,

(SELECT distinct aida.attribute3 FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) bpo_project_name,

(SELECT distinct aida.attribute1 FROM ap_invoice_distributions_all aida,xla_distribution_links xdl
where aida.invoice_id=aia.invoice_id
and aida.invoice_distribution_id=xdl.source_distribution_id_num_1
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num

) staff_num
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_kfv cc,
    apps.ap_suppliers aps,
    gl_period_statuses gps

WHERE aia.INVOICE_ID = xte.source_id_int_1
    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 gjh.STATUS = 'P'
    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 cc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
    and cc.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.Ledger_ID not in ( 1014,1015,1016 )
    --and gjl.EFFECTIVE_DATE between to_date(:P_START_DATE) and to_date(:P_END_DATE)
    --and xal.accounting_class_code in ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
    --and cc.segment1=111
    AND gjh.ledger_id=gps.ledger_id
AND 101=gps.application_id
and gps.set_of_books_id=gjh.ledger_id
     and gjh.je_source ='Payables'
and gjh.Ledger_ID not in ( 1014,1015,1016 )
AND gjh.period_name       = gps.period_name
    --and cc.segment5 in (:P_MIN_FLEX,:P_MAX_FLEX)
    and ( nvl(xal.accounted_cr,0)<>0 or nvl(xal.accounted_dr,0)<>0)

    --and aia.org_id=:P_ORG
    and xah.je_category_name='Purchase Invoices'
AND (    NVL (gps.effective_period_num, 0) >=:cf_period_from
        AND (NVL (gps.effective_period_num, 0) <=:cf_period_to)
             )
--           and 24116=:p_account_from
--           and 24116=:p_account_to
                AND (    NVL (cc.segment5, 0) >= :p_account_from
        AND (NVL (cc.segment5, 0) <= :p_account_to)
       )

     AND (    NVL (cc.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (cc.segment1, 0) <= :P_COMPANY_TO)
       )
       AND aia.payment_currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',aia.payment_currency_code,
                                              'NON INR',decode(aia.payment_currency_code,'INR','###',aia.payment_currency_code))



union all

SELECT
              gjh.period_name,
              null reference_2,
              null po_order_number,
              null "Receipt Number",
              null "Receipt Date",
          ent.transaction_number "Invoice Number",
          aia.check_date "Invoice Date",
          av.vendor_name "Vendor Name",
          av.segment1 "Vendor Number",
          gjh.je_source "Source",
          gjh.ledger_id "set_of_books_id" ,
          gcck.concatenated_segments segment,
          gcck.concatenated_segments segment21,
          aia.currency_code currency_code
          ,ael.entered_dr,
          ael.entered_cr,
          ael.accounted_dr,
          ael.accounted_cr,
          gir.je_header_id,
          gir.je_line_num,
          gcck.code_combination_id
          ,gir.subledger_doc_sequence_value "Vocher Number"
          ,aeh.accounting_date "GL_Date"
          ,NULL "Staff Number"
          ,null "Project"
          ,null "Customer Num/Name"
          ,ael.description "Description",
          null attribute_category,
null project_name,
null task_name,
null expenditure_item_date,
null expenditure_type,
null expenditure_org,
null customer_name,
null bpo_project_name,
null staff_num
          FROM
          xla_ae_headers                   aeh
         ,xla_ae_lines                     ael
         ,xla_events                       xle
         ,xla_event_types_tl               xet
         ,xla_transaction_entities         ent
         ,gl_ledgers                       glg
         ,gl_periods                       glp
         ,xla_subledgers                   xls
         ,gl_code_combinations_kfv         gcck
               ,gl_import_references             gir
          ,gl_je_lines                      gjl
          ,gl_je_headers                    gjh
          ,gl_je_batches                    gjb
          --,ap_invoice_distributions_all aid
--          ,ap_invoice_lines_all aila
          ,ap_checks_all aia
          ,gl_period_statuses gps
          ,ap_suppliers av
          --,pa_project_customers_v ppcv
          --,pa_projects_all ppa
           WHERE 1=1
           AND aeh.ledger_id              = glg.ledger_id
           AND ael.application_id         = aeh.application_id
           AND ael.ae_header_id           = aeh.ae_header_id
           AND xle.application_id         = aeh.application_id
           AND xle.event_id               = aeh.event_id
           AND xet.application_id         = xle.application_id
           AND xet.event_type_code        = xle.event_type_code
           AND xet.LANGUAGE               = USERENV('LANG')
           AND ent.application_id         = aeh.application_id
           AND ent.entity_id              = aeh.entity_id
           AND glp.period_name            = aeh.period_name
           AND glp.period_set_name        = glg.period_set_name
           AND xls.application_id         = aeh.application_id
           AND gcck.code_combination_id   = ael.code_combination_id

           AND aeh.accounting_entry_status_code <> 'N' AND
aeh.application_id = 200 AND aeh.balance_type_code = 'A' AND (
NVL(ael.accounted_cr,0) <> 0
                                       OR NVL(ael.accounted_dr,0) <> 0 )
           AND gir.gl_sl_link_id(+)       = ael.gl_sl_link_id
           AND gir.gl_sl_link_table(+)    = ael.gl_sl_link_table
           AND gjl.je_header_id(+)        = gir.je_header_id
           AND gjl.je_line_num(+)         = gir.je_line_num
           AND gjh.je_header_id(+)        = gir.je_header_id
           AND gjb.je_batch_id (+)        = gir.je_batch_id
           AND decode(gjh.je_header_id,null,'Y',gjh.je_from_sla_flag) in ('U', 'Y')
           --and ael.accounting_class_code   in ('ITEM EXPENSE','CASH_CLEARING','LIABILITY','PREPAID_EXPENSE','RTAX','INTRA')
AND ent.source_id_int_1 = aia.check_id
          -- and aia.doc_sequence_value=gir.subledger_doc_sequence_value
           and aia.vendor_id=av.vendor_id
           and gjh.je_source ='Payables'
           AND aeh.je_category_name='Payments'

AND gjh.ledger_id=gps.ledger_id
AND 101=gps.application_id
and gps.set_of_books_id=gjh.ledger_id
and gjh.Ledger_ID not in ( 1014,1015,1016 )
AND gjh.period_name = gps.period_name

--and aia.org_id=:P_ORG

AND (    NVL (gps.effective_period_num, 0) >=:cf_period_from
        AND (NVL (gps.effective_period_num, 0) <=:cf_period_to)
             )

                AND (    NVL (gcck.segment5, 0) >= :p_account_from
        AND (NVL (gcck.segment5, 0) <= :p_account_to)
       )

     AND (    NVL (gcck.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (gcck.segment1, 0) <= :P_COMPANY_TO)
       )
       AND aia.currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',aia.currency_code,
                                              'NON INR',decode(aia.currency_code,'INR','###',aia.currency_code))



union all

SELECT     gjh.period_name,
             null reference_2,
             null po_order_number,
             case
             WHEN (gjh.je_category in ('Receipts','Misc Receipts')) THEN
             xte.transaction_number
             ELSE
               NULL
              END     "Receipt Number",
              case
             WHEN (gjh.je_category in ('Receipts','Misc Receipts')) THEN
             xah.accounting_date
             ELSE
               NULL
              END     "Receipt Date",
              case
              WHEN (gjh.je_category not in ('Receipts','Misc Receipts')) THEN
              xte.transaction_number ELSE
             NULL
             END    "Invoice Number",
             case
              WHEN (gjh.je_category not in ('Receipts','Misc Receipts')) THEN
               xah.accounting_date
               ELSE
             NULL
             END    "Invoice Date",
          CASE
              WHEN xal.party_type_code = 'S' THEN
                 (SELECT         aps.segment1
                        ||'|'||aps.vendor_name
                        ||'|'||hzp.jgzz_fiscal_code
                        ||'|'||hzp.tax_reference
                        ||'|'||hps.party_site_number
                        ||'|'||hps.party_site_name
                        ||'|'||NULL
                   FROM ap_suppliers          aps
                        ,ap_supplier_sites_all apss
                        ,hz_parties            hzp
                        ,hz_party_sites        hps
                        ,xla_ae_lines          ael2
                  WHERE aps.vendor_id          = ael2.party_id
                    AND hzp.party_id           = aps.party_id
                    AND apss.vendor_site_id(+) = ael2.party_site_id
                    AND hps.party_site_id(+)   = apss.party_site_id
                    AND ael2.application_id    = xal.application_id
                    AND ael2.ae_header_id      = xal.ae_header_id
                    AND ael2.ae_line_num       = xal.ae_line_num )
              WHEN (xal.party_type_code = 'C' and xal.party_id is not null) THEN
                 (SELECT         --hca.account_number||'|'||
                 hzp.party_name
                        --||'|'||hzp.jgzz_fiscal_code
                        --||'|'||hzp.tax_reference
                        --||'|'||hps.party_site_number
                        --||'|'||hps.party_site_name
                        --||'|'||hzcu.tax_reference
                   FROM hz_cust_accounts        hca
                        ,hz_cust_acct_sites_all hcas
                        ,hz_cust_site_uses_all   hzcu
                        ,hz_parties              hzp
                        ,hz_party_sites          hps
                        ,xla_ae_lines            ael2
                  WHERE hca.cust_account_id       = ael2.party_id
                    AND hzp.party_id              = hca.party_id
                    AND hzcu.site_use_id(+)       = ael2.party_site_id
                    AND hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id
                    AND hps.party_site_id(+)      = hcas.party_site_id
                    AND ael2.application_id       = xal.application_id
                    AND ael2.ae_header_id         = xal.ae_header_id
                    AND ael2.ae_line_num          = xal.ae_line_num )
              ELSE
                NULL
              END       "Vendor Name",
              case
              WHEN (xal.party_type_code = 'C' and xal.party_id is not null) THEN
                 (SELECT         hca.account_number
                 --||'|'|| hzp.party_name
                        --||'|'||hzp.jgzz_fiscal_code
                        --||'|'||hzp.tax_reference
                        --||'|'||hps.party_site_number
                        --||'|'||hps.party_site_name
                        --||'|'||hzcu.tax_reference
                   FROM hz_cust_accounts        hca
                        ,hz_cust_acct_sites_all hcas
                        ,hz_cust_site_uses_all   hzcu
                        ,hz_parties              hzp
                        ,hz_party_sites          hps
                        ,xla_ae_lines            ael2
                  WHERE hca.cust_account_id       = ael2.party_id
                    AND hzp.party_id              = hca.party_id
                    AND hzcu.site_use_id(+)       = ael2.party_site_id
                    AND hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id
                    AND hps.party_site_id(+)      = hcas.party_site_id
                    AND ael2.application_id       = xal.application_id
                    AND ael2.ae_header_id         = xal.ae_header_id
                    AND ael2.ae_line_num          = xal.ae_line_num )
              ELSE
                NULL
              END       "Vendor Number",
              gjh.je_source"Source",
              gjh.ledger_id "set_of_books_id" ,
                        cc.concatenated_segments segment,
            cc.concatenated_segments segment21,
              xal.currency_code currency_code,
              xal.entered_dr,xal.entered_cr,
              xal.accounted_dr,xal.accounted_cr,
              gir.je_header_id,
            gir.je_line_num,
            cc.code_combination_id
            ,gir.subledger_doc_sequence_value "Vocher Number"
            ,xah.accounting_date "GL_Date"
            ,NULL "Staff Number"
            ,null "Project"
            ,null "Customer Num/Name"
            ,xah.description "Description",null attribute_category,
null project_name,
null task_name,
null expenditure_item_date,
null expenditure_type,
null expenditure_org,
null customer_name,
null bpo_project_name,
null staff_num

FROM 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_kfv cc,
      --apps.ap_suppliers aps,
      gl_period_statuses gps


WHERE 1=1--aia.INVOICE_ID = xte.source_id_int_1
      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 gjh.STATUS = 'P'
      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 cc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
      and cc.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.Ledger_ID not in ( 1014,1015,1016 )
      --and gjl.EFFECTIVE_DATE between to_date(:P_START_DATE) and to_date(:P_END_DATE)
      --and xal.accounting_class_code in ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
      --and cc.segment1=111
      AND gjh.ledger_id=gps.ledger_id
    AND 101=gps.application_id
    and gps.set_of_books_id=gjh.ledger_id
       and gjh.je_source ='Receivables'
    and gjh.Ledger_ID not in ( 1014,1015,1016 )
    --and xal.entered_dr='136547.55'
AND gjh.period_name       = gps.period_name
      --and cc.segment5 in (:P_MIN_FLEX,:P_MAX_FLEX)
      and ( nvl(xal.accounted_cr,0)<>0 or nvl(xal.accounted_dr,0)<>0)

      --and aia.org_id=:P_ORG
      --and xah.je_category_name='Purchase Invoices'
    AND (    NVL (gps.effective_period_num, 0) >=:cf_period_from
          AND (NVL (gps.effective_period_num, 0) <=:cf_period_to)
               )
                   AND (    NVL (cc.segment5, 0) >= :p_account_from
          AND (NVL (cc.segment5, 0) <= :p_account_to)
         )

       AND (    NVL (cc.segment1, 0) >=:P_COMPANY_FROM
          AND (NVL (cc.segment1, 0) <= :P_COMPANY_TO)
         )
         --and xte.transaction_number='101914 - ZPF TDS April 12'
         AND xal.currency_code =decode(:p_currency_code,'INR','INR',
                                                'ALL',xal.currency_code,
                                                'NON INR',decode(xal.currency_code,'INR','###',xal.currency_code));

Monday, July 2, 2012

11i GL Query

11i AP-GL Links

SELECT distinct b.period_name, b.line_reference_2, a.po_order_number, a.rcv_receipt_num "Receipt Number",
a.trx_date "Receipt Date", a.trx_number_displayed "Invoice Number",
a.accounting_date "Invoice Date", a.third_party_name "Vendor Name",
a.third_party_number "Vendor Number", b.je_source "Source", b.SET_OF_BOOKS_ID "set_of_books_id" ,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 SEGMENT,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 segment2,
a.currency_code, a.entered_dr, a.entered_cr, a.accounted_dr,
a.accounted_cr, a.je_header_id, a.je_line_num, a.code_combination_id,
a.doc_sequence_value "Vocher Number", a.gl_date "GL_Date",
a.attribute1 "Staff Number", a.attribute2 "Project",
a.attribute3 "Customer Num/Name", a.description "Description"
FROM
(SELECT ph.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
i.invoice_date trx_date, i.invoice_num trx_number_displayed,
aeh.accounting_date accounting_date,
v.vendor_name third_party_name, v.segment1 third_party_number,
ael.currency_code currency_code, ael.entered_dr entered_dr,
ael.entered_cr entered_cr, ael.accounted_dr accounted_dr,
ael.accounted_cr accounted_cr, r.je_header_id je_header_id,
r.je_line_num je_line_num,
ael.code_combination_id code_combination_id,
200 application_id, i.gl_date, i.doc_sequence_value,
d.description, d.attribute1, d.attribute2, d.attribute3
FROM gl_import_references r,
gl_je_categories jc,
ap_ae_lines_all ael,
ap_ae_headers_all aeh,
ap_invoice_distributions_all d,
ap_invoices_all i,
ap_accounting_events_all ae,
po_releases_all pr,
po_headers_all ph,
po_distributions_all pd,
rcv_transactions rct,
rcv_shipment_headers rsh,
po_vendor_sites_all vs,
po_vendors v,
ap_tax_codes_all AT,
gl_daily_conversion_types glct,
fnd_document_sequences fd,
ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6,
ap_lookup_codes l7,
ap_lookup_codes l8,
ap_lookup_codes l9,
ap_lookup_codes l10,
ap_lookup_codes l11,
ap_lookup_codes l12
WHERE l2.lookup_code(+) = d.line_type_lookup_code
AND l2.lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
AND l3.lookup_code = ael.ae_line_type_code
AND l3.lookup_type = 'AE LINE TYPE'
AND l4.lookup_code = aeh.gl_transfer_flag || ''
AND l4.lookup_code = aeh.gl_transfer_flag || ''
AND l4.lookup_type = 'POSTING STATUS'
AND l5.lookup_code = ae.event_type_code
AND l5.lookup_type = 'EVENT TYPE'
AND l6.lookup_code(+) = aeh.accounting_error_code
AND l6.lookup_type(+) = 'ACCOUNTING ERROR TYPE'
AND l7.lookup_code(+) = ael.accounting_error_code
AND l7.lookup_type(+) = 'ACCOUNTING ERROR TYPE'
AND l8.lookup_code = 'Invoices'
AND l8.lookup_type = 'POSTING CATEGORY'
AND l9.lookup_code(+) = ael.gl_transfer_error_code
AND l9.lookup_type(+) = 'POSTING EXCEPTIONS'
AND l10.lookup_code = 'EVENT'
AND l10.lookup_type = 'VIEW_ACCOUNTING'
AND l11.lookup_code = 'LINE'
AND l11.lookup_type = 'VIEW_ACCOUNTING'
AND l12.lookup_code = 'DIST_LINE_NUM'
AND l12.lookup_type = 'VIEW_ACCOUNTING'
AND jc.je_category_name = aeh.ae_category
AND rsh.shipment_header_id(+) = rct.shipment_header_id
AND d.rcv_transaction_id = rct.transaction_id(+)
AND pr.po_release_id(+) = pd.po_release_id
AND ph.po_header_id(+) = pd.po_header_id
AND pd.po_distribution_id(+) = d.po_distribution_id
AND ael.currency_conversion_type = glct.conversion_type(+)
AND ael.subledger_doc_sequence_id = fd.doc_sequence_id(+)
AND ael.third_party_sub_id = vs.vendor_site_id
AND ael.third_party_id = v.vendor_id
-- AND l1.lookup_code = i.invoice_type_lookup_code
-- AND l1.lookup_type = 'INVOICE TYPE'
AND AT.tax_id(+) = ael.tax_code_id
AND ae.accounting_event_id = aeh.accounting_event_id
AND i.invoice_id(+) = ae.source_id
--- AND ae.source_table = 'AP_INVOICES'
AND (ae.source_table = 'AP_INVOICES'
OR ae.source_table = 'AP_CHECKS'
OR ae.source_table = 'AP_INVOICE_PAYMENTS')

AND aeh.ae_header_id = ael.ae_header_id
AND DECODE (ael.source_table,
'AP_INVOICE_DISTRIBUTIONS', ael.source_id,
NULL
) = d.invoice_distribution_id(+)
AND ael.gl_sl_link_id = r.gl_sl_link_id
UNION ALL
SELECT poh.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
rct.transaction_date trx_date, NULL trx_number_displayed,
rrs.accounting_date accounting_date,
pov.vendor_name third_party_name,
pov.segment1 third_party_number, rrs.currency_code,
rrs.entered_dr, rrs.entered_cr, rrs.accounted_dr,
rrs.accounted_cr, r.je_header_id, r.je_line_num,
rrs.code_combination_id, 201 application_id,null, --ai.gl_date,
null,poh.COMMENTS DESCRIPTION,null,-- ai.doc_sequence_value, aid.description, aid.attribute1,
null,null-- aid.attribute2, aid.attribute3
FROM gl_import_references r,
rcv_receiving_sub_ledger rrs,
--ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
po_distributions_all pod,
po_headers_all poh,
rcv_transactions rct,
rcv_shipment_headers rsh,
po_vendors pov
WHERE rrs.gl_sl_link_id = r.gl_sl_link_id
AND rct.transaction_id = rrs.rcv_transaction_id
AND rsh.shipment_header_id = rct.shipment_header_id
AND pod.po_distribution_id = rrs.reference3
AND poh.po_header_id = pod.po_header_id
-- AND aid.po_distribution_id(+) = pod.po_distribution_id
-- AND ai.invoice_id = aid.invoice_id
-- AND ai.vendor_id = pov.vendor_id
and exists (select 1
from ap_invoice_distributions_all aid,ap_invoices_all ai
where aid.po_distribution_id=pod.po_distribution_id
AND ai.invoice_id = aid.invoice_id
AND ai.vendor_id = pov.vendor_id ) ) a,
gl_je_journal_lines_v b,
gl_summary_combinations_v c
WHERE b.je_header_id = a.je_header_id
AND b.line_je_line_num = a.je_line_num
AND b.line_code_combination_id = c.code_combination_id
AND a.code_combination_id = b.line_code_combination_id
AND b.actual_flag = 'A'
AND b.batch_status || '' = 'P'
AND b.currency_code != 'STAT'
AND ( NVL (b.effective_period_num, 0) >= :cf_period_from
AND (NVL (b.effective_period_num, 0) <= :cf_period_to)
)
AND ( NVL (segment1, 0) >= :p_org_from
AND (NVL (segment1, 0) <= :p_org_to)
)
AND ( NVL (segment5, 0) >= :p_account_from
AND (NVL (segment5, 0) <= :p_account_to)
)
and b.SET_OF_BOOKS_ID not in ( 1014,1015,1016 )
AND a.currency_code=decode(:p_currency_code,'INR','INR',
'ALL',a.currency_code,
'NON INR',decode(a.currency_code,'INR','###',a.currency_code))
UNION ALL
SELECT period_name, gl_je_journal_lines_v.line_reference_2, TO_CHAR (NULL) po_order_number,
TO_CHAR (NULL) rcv_receipt_num, TO_DATE (NULL) trx_date,
TO_CHAR (NULL) trx_number_displayed, TO_DATE (NULL) accounting_date,
TO_CHAR (NULL) third_party_name, TO_CHAR (NULL) third_party_number,
je_source "Source", gl_je_journal_lines_v.SET_OF_BOOKS_ID "set_of_books_id" ,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 SEGMENT,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 segment2,
gl_je_journal_lines_v.currency_code, line_entered_dr, line_entered_cr,
line_accounted_dr, line_accounted_cr,
JE_HEADER_ID,--TO_NUMBER (NULL) je_header_id,
LINE_JE_LINE_NUM,--TO_NUMBER (NULL) je_line_num,
line_code_combination_id,
DOC_SEQUENCE_VALUE,--TO_NUMBER (NULL) doc_sequence_value,
HEADER_EFFECTIVE_DATE,--TO_DATE (NULL) gl_date,
LINE_ATTRIBUTE1,--TO_CHAR (NULL) attribute1,
LINE_ATTRIBUTE2,--TO_CHAR (NULL) attribute2,
LINE_ATTRIBUTE3,--TO_CHAR (NULL) attribute3,
LINE_DESCRIPTION description--TO_CHAR (NULL) description
FROM gl_je_journal_lines_v , gl_summary_combinations_v, gl_sets_of_books
WHERE gl_summary_combinations_v.code_combination_id =
gl_je_journal_lines_v.line_code_combination_id
AND actual_flag = 'A'
AND batch_status || '' = 'P'
AND gl_je_journal_lines_v.currency_code != 'STAT'
AND je_source NOT IN ('Payables', 'Purchasing')
AND gl_je_journal_lines_v.set_of_books_id =
gl_sets_of_books.set_of_books_id
AND gl_sets_of_books.mrc_sob_type_code = 'P'
AND ( NVL (effective_period_num, 0) >= :cf_period_from
AND (NVL (effective_period_num, 0) <= :cf_period_to)
)
AND ( NVL (segment1, 0) >= :p_org_from
AND (NVL (segment1, 0) <= :p_org_to)
)
AND ( NVL (segment5, 0) >= :p_account_from
AND (NVL (segment5, 0) <= :p_account_to)
)
and gl_je_journal_lines_v.SET_OF_BOOKS_ID not in ( 1014,1015,1016 ) -- Added by RAVINDER on 4 Apr 07
AND gl_je_journal_lines_v.currency_code =decode(:p_currency_code,'INR','INR',
'ALL',gl_je_journal_lines_v.currency_code,
'NON INR',decode(gl_je_journal_lines_v.currency_code,'INR','###',gl_je_journal_lines_v.currency_code)) --Added by Jayshree.D on 10-Dec-10
UNION ALL
SELECT b.period_name, b.line_reference_2, a.po_order_number, a.rcv_receipt_num "Receipt Number",
a.trx_date "Receipt Date", a.trx_number_displayed "Invoice Number",
a.accounting_date "Invoice Date", a.third_party_name "Vendor Name",
a.third_party_number "Vendor Number", b.je_source "Source", b.SET_OF_BOOKS_ID "set_of_books_id" ,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 SEGMENT,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 segment2,
a.currency_code, a.entered_dr, a.entered_cr, a.accounted_dr,
a.accounted_cr, a.je_header_id, a.je_line_num, a.code_combination_id,
a.doc_sequence_value "Vocher Number", a.gl_date "GL_Date",
a.attribute1 "Staff Number", a.attribute2 "Project",
a.attribute3 "Customer Num/Name", a.description "Description"
FROM (SELECT poh.segment1 po_order_number, rsh.receipt_num rcv_receipt_num,
rct.transaction_date trx_date, NULL trx_number_displayed,
rrs.accounting_date accounting_date,
pov.vendor_name third_party_name,
pov.segment1 third_party_number, rrs.currency_code,
rrs.entered_dr, rrs.entered_cr, rrs.accounted_dr,
rrs.accounted_cr, r.je_header_id, r.je_line_num,
rrs.code_combination_id, 201 application_id,
TO_DATE (NULL) gl_date, TO_NUMBER (NULL) doc_sequence_value,
TO_CHAR (NULL) description, TO_CHAR (NULL) attribute1,
TO_CHAR (NULL) attribute2, TO_CHAR (NULL) attribute3
FROM gl_import_references r,
rcv_receiving_sub_ledger rrs,
po_distributions_all pod,
po_headers_all poh,
rcv_transactions rct,
rcv_shipment_headers rsh,
po_vendors pov
WHERE rrs.gl_sl_link_id = r.gl_sl_link_id
AND rct.transaction_id = rrs.rcv_transaction_id
AND rsh.shipment_header_id = rct.shipment_header_id
AND pod.po_distribution_id = rrs.reference3
AND poh.po_header_id = pod.po_header_id
AND NOT EXISTS (SELECT 1
FROM ap_invoice_distributions_all
WHERE po_distribution_id = pod.po_distribution_id)
AND poh.vendor_id = pov.vendor_id) a,
gl_je_journal_lines_v b,
gl_summary_combinations_v c
WHERE b.je_header_id = a.je_header_id
AND b.line_je_line_num = a.je_line_num
AND b.line_code_combination_id = c.code_combination_id
AND a.code_combination_id = b.line_code_combination_id
AND b.actual_flag = 'A'
AND b.batch_status || '' = 'P'
AND b.currency_code != 'STAT'
AND ( NVL (b.effective_period_num, 0) >= :cf_period_from
AND (NVL (b.effective_period_num, 0) <= :cf_period_to)
)
AND ( NVL (segment1, 0) >= :p_org_from
AND (NVL (segment1, 0) <= :p_org_to)
)
AND ( NVL (segment5, 0) >= :p_account_from
AND (NVL (segment5, 0) <= :p_account_to)
)
and b.SET_OF_BOOKS_ID not in ( 1014,1015,1016 )
AND a.currency_code =decode(:p_currency_code,'INR','INR',
'ALL',a.currency_code,
'NON INR',decode(a.currency_code,'INR','###',a.currency_code))
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNION --Added by Saurabh on 06-Mar-08
SELECT period_name, gl_je_journal_lines_v.line_reference_2, TO_CHAR (NULL) po_order_number,
TO_CHAR (NULL) rcv_receipt_num, TO_DATE (NULL) trx_date,
TO_CHAR (NULL) trx_number_displayed, TO_DATE (NULL) accounting_date,
TO_CHAR (NULL) third_party_name, TO_CHAR (NULL) third_party_number,
je_source "Source",gl_je_journal_lines_v.SET_OF_BOOKS_ID "set_of_books_id" ,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 SEGMENT,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8 segment2,
gl_je_journal_lines_v.currency_code, line_entered_dr, line_entered_cr,
line_accounted_dr, line_accounted_cr,
JE_HEADER_ID,--TO_NUMBER (NULL) je_header_id,
LINE_JE_LINE_NUM,--TO_NUMBER (NULL) je_line_num,
line_code_combination_id,
DOC_SEQUENCE_VALUE,--TO_NUMBER (NULL) doc_sequence_value,
HEADER_EFFECTIVE_DATE,--TO_DATE (NULL) gl_date,
LINE_ATTRIBUTE1,--TO_CHAR (NULL) attribute1,
LINE_ATTRIBUTE2,--TO_CHAR (NULL) attribute2,
LINE_ATTRIBUTE3,--TO_CHAR (NULL) attribute3,
LINE_DESCRIPTION description--TO_CHAR (NULL) description
FROM gl_je_journal_lines_v , gl_summary_combinations_v, gl_sets_of_books
WHERE gl_summary_combinations_v.code_combination_id = gl_je_journal_lines_v.line_code_combination_id
AND actual_flag = 'A'
AND batch_status || '' = 'P'
AND gl_je_journal_lines_v.currency_code != 'STAT'
AND je_source = 'Purchasing'
AND gl_je_journal_lines_v.set_of_books_id =
gl_sets_of_books.set_of_books_id
AND gl_sets_of_books.mrc_sob_type_code = 'P'
AND ( NVL (effective_period_num, 0) >= :cf_period_from
AND (NVL (effective_period_num, 0) <= :cf_period_to)
)
--AND AID.ACCOUNTING_DATE between NVL (:P_Start_Date,sysdate) and NVL (:P_End_Date,sysdate)
AND ( NVL (segment1, 0) >= :p_org_from
AND (NVL (segment1, 0) <= :p_org_to)
)
AND ( NVL (segment5, 0) >= :p_account_from
AND (NVL (segment5, 0) <= :p_account_to)
)
and gl_je_journal_lines_v.je_header_id in (select distinct je_header_id from gl_je_batches_headers_v where batch_name like 'Reverses%')
and gl_je_journal_lines_v.SET_OF_BOOKS_ID not in ( 1014,1015,1016 )
AND gl_je_journal_lines_v.currency_code =decode(:p_currency_code,'INR','INR',
'ALL',gl_je_journal_lines_v.currency_code,
'NON INR',decode(gl_je_journal_lines_v.currency_code,'INR','###',gl_je_journal_lines_v.currency_code))

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'