AP Accrual Queries

Cost Management Source

select gh.je_source

      ,gh.period_name

      ,gcc.segment4

      ,gcc.segment1

      ,gcc.CONCATENATED_SEGMENTS ACCOUNT_CODE     

      ,xl.accounted_dr

      ,xl.accounted_cr

      ,nvl(xl.accounted_dr,0) - nvl(xl.accounted_cr,0) NET

      ,ood.ORGANIZATION_CODE

      ,ood.OPERATING_UNIT     

      ,mtt.TRANSACTION_TYPE_NAME

      ,Case when mmt.transaction_type_id in (18,36) then

            (select segment1 from po_headers_all pha where pha.po_header_id=mmt.TRANSACTION_SOURCE_ID)

       end PO_Number   

      ,to_char(xte.SOURCE_ID_INT_1)

      ,msi.SEGMENT1

      ,msi.CONCATENATED_SEGMENTS

      ,mmt.TRANSACTION_QUANTITY

--      ,xte.SECURITY_ID_INT_1 Inventory_Org

--      ,xte.SECURITY_ID_INT_2 Operating_Unit

--      ,gir.GL_SL_LINK_TABLE

--      ,gir.GL_SL_LINK_ID

--      ,gh.name jv_name

--      ,gb.name batch_name

--      ,gh.je_category

--      ,xte.ENTITY_CODE

--      ,decode(mtt.TRANSACTION_TYPE_NAME,'PO Receipt',pha.segment1,null) PO_Number

from  gl_je_headers gh

     ,gl_je_lines gl

     ,gl_import_references gir

     ,gl_je_batches gb

     ,gl_code_combinations_kfv gcc

     ,xla_ae_lines xl

     ,xla_ae_headers xh

     ,xla.xla_transaction_entities xte

     ,org_organization_definitions ood

     ,MTL_MATERIAL_TRANSACTIONS mmt

     ,mtl_transaction_types mtt

--     ,po_headers_all pha

     ,mtl_system_items_kfv msi

where gh.je_header_id   = gl.je_header_id

--and gh.je_header_id     = 518282

and gir.je_header_id    = gh.je_header_id

and gir.JE_LINE_NUM     = gl.JE_LINE_NUM

and gir .je_batch_id  = gb.je_batch_id

and gl.code_combination_id = gcc.code_combination_id

and xl.ae_header_id     = xh.ae_header_id

and xl.GL_SL_LINK_ID    = gir.GL_SL_LINK_ID

and xl.GL_SL_LINK_TABLE = gir.GL_SL_LINK_TABLE

and gir.GL_SL_LINK_TABLE = 'XLAJEL'

AND xte.entity_id = xh.entity_id

and ood.organization_id = xte.SECURITY_ID_INT_1

--and gh.period_name      = :P_PERIOD --'JUN-11'

and trunc(gl.EFFECTIVE_DATE) between :P_date_from and :P_date_to

and gcc.segment1 = nvl(:P_Segment1, gcc.segment1)

and gcc.segment2 = nvl(:P_Segment2, gcc.segment2)

and gcc.segment4 = nvl(:P_Segment4, gcc.segment4)

and xte.SOURCE_ID_INT_1 = mmt.transaction_id

and mtt.transaction_type_id = mmt.transaction_type_id

--and mmt.TRANSACTION_SOURCE_ID = pha.po_header_id (+)

and msi.inventory_item_id = mmt.inventory_item_id 

and msi.organization_id = 87

and gh.ledger_id = nvl(:P_ledger_id, gh.ledger_id)

and xte.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'

UNION ALL

select gh.je_source

      ,gh.period_name

      ,gcc.segment4

      ,gcc.segment1

      ,gcc.CONCATENATED_SEGMENTS ACCOUNT_CODE     

      ,xl.accounted_dr

      ,xl.accounted_cr

      ,nvl(xl.accounted_dr,0) - nvl(xl.accounted_cr,0) NET

      ,ood.ORGANIZATION_CODE

      ,ood.OPERATING_UNIT

      ,rt.SOURCE_DOCUMENT_CODE||'-'||rt.TRANSACTION_TYPE TRANSACTION_TYPE_NAME

      ,pha.segment1 PO_Number

      ,to_char(xte.SOURCE_ID_INT_1)

      ,msi.SEGMENT1

      ,msi.CONCATENATED_SEGMENTS

      ,rt.quantity

--      ,xte.SECURITY_ID_INT_1 Inventory_Org

--      ,xte.SECURITY_ID_INT_2 Operating_Unit

--      ,gir.GL_SL_LINK_TABLE

--      ,gir.GL_SL_LINK_ID   

--      ,gh.name jv_name

--      ,gb.name batch_name

--      ,gh.je_category

--      ,xte.ENTITY_CODE

--      ,xte.* 

from  gl_je_headers gh

     ,gl_je_lines gl

     ,gl_import_references gir

     ,gl_je_batches gb

     ,gl_code_combinations_kfv gcc

     ,xla_ae_lines xl

     ,xla_ae_headers xh

     ,xla.xla_transaction_entities xte

     ,org_organization_definitions ood

     ,rcv_transactions rt

     ,po_headers_all pha

     ,po_lines_all pla

     ,mtl_system_items_kfv msi

where gh.je_header_id   = gl.je_header_id

--and gh.je_header_id     = 2212350

and gir.je_header_id    = gh.je_header_id

and gir.JE_LINE_NUM     = gl.JE_LINE_NUM

and gir .je_batch_id  = gb.je_batch_id

and gl.code_combination_id = gcc.code_combination_id

and xl.ae_header_id     = xh.ae_header_id

and xl.GL_SL_LINK_ID    = gir.GL_SL_LINK_ID

and xl.GL_SL_LINK_TABLE = gir.GL_SL_LINK_TABLE

and gir.GL_SL_LINK_TABLE = 'XLAJEL'

AND xte.entity_id = xh.entity_id

and ood.organization_id = xte.SECURITY_ID_INT_1

--and gh.period_name      = :P_PERIOD --'JUN-11'

and trunc(gl.EFFECTIVE_DATE) between :P_date_from and :P_date_to

and gcc.segment1 = nvl(:P_Segment1, gcc.segment1)

and gcc.segment2 = nvl(:P_Segment2, gcc.segment2)

and gcc.segment4 = nvl(:P_Segment4, gcc.segment4)

and gh.ledger_id = nvl(:P_ledger_id, gh.ledger_id)

and rt.transaction_id = xte.SOURCE_ID_INT_1

and rt.organization_id   = xte.SECURITY_ID_INT_1

and rt.po_header_id = pha.po_header_id

and pla.po_header_id = pha.po_header_id

and rt.po_line_id = pla.po_line_id

and xte.ENTITY_CODE ='RCV_ACCOUNTING_EVENTS'

and pla.item_id = msi.inventory_item_id

and msi.organization_id = 87

 Payables Source

SELECT gh.je_source

           ,gh.period_name

           ,gh.je_category

           ,gh.NAME

           ,gc.segment4

           ,gc.segment1

           ,gc.CONCATENATED_SEGMENTS ACCOUNT_CODE     

         ,xdl.UNROUNDED_ACCOUNTED_DR

         ,xdl.UNROUNDED_ACCOUNTED_CR

         ,nvl(xdl.UNROUNDED_ACCOUNTED_DR,0) - nvl(xdl.UNROUNDED_ACCOUNTED_CR,0) NET

         ,(select ood.ORGANIZATION_CODE from  org_organization_definitions ood where ood.organization_id = rt.organization_id) ORGANIZATION_CODE

         ,aia.org_id

         ,xl.accounting_class_code

         ,(select segment1 from po_headers_all pha where pha.po_header_id = rt.po_header_id) PO_Number

         ,(select rvh.RECEIPT_NUM from RCV_VRC_HDS_V rvh where rvh.SHIPMENT_HEADER_ID= rt.SHIPMENT_HEADER_ID) GRN

         ,to_char(aia.invoice_num)

         ,(select msi.SEGMENT1 from po_lines_all pla, mtl_system_items_kfv msi where msi.organization_id = 87 and msi.inventory_item_id = pla.item_id  and pla.PO_LINE_ID = rt.PO_LINE_ID) PREFIX

         ,(select CONCATENATED_SEGMENTS from po_lines_all pla, mtl_system_items_kfv msi where msi.organization_id = 87 and msi.inventory_item_id = pla.item_id  and pla.PO_LINE_ID = rt.PO_LINE_ID) Item_Code

         ,rt.QUANTITY_BILLED

          ,pv.vendor_name

    FROM gl_je_lines gl, gl_je_headers gh, gl_code_combinations_kfv gc

        ,xla_ae_lines xl

        ,xla_ae_headers xh

        ,xla.xla_transaction_entities xte 

        ,xla.xla_distribution_links xdl

        ,ap_invoice_distributions_all aida

        ,rcv_transactions rt

        ,ap_invoices_all aia

        ,po_vendors pv

WHERE   gh.je_header_id = gl.je_header_id

     and gh.ledger_id = nvl(:P_ledger_id, gh.ledger_id)

     and gc.code_combination_id = gl.code_combination_id

     and xh.ae_header_id = xl.ae_header_id

     and xte.entity_id = xh.entity_id

     and xte.entity_code = 'AP_INVOICES'

     and xte.source_id_int_1 = aia.invoice_id

     and aia.vendor_id = pv.vendor_id

     and gl.GL_SL_LINK_ID = xl.GL_SL_LINK_ID --(+)

     and gh.status = 'P'

     and xl.AE_HEADER_ID = xdl.AE_HEADER_ID

     and xl.AE_LINE_NUM  = xdl.AE_LINE_NUM

     and aida.invoice_id = aia.invoice_id

     and aida.INVOICE_DISTRIBUTION_ID = xdl.SOURCE_DISTRIBUTION_ID_NUM_1

     and aida.RCV_TRANSACTION_ID = rt.TRANSACTION_ID(+)

    AND TRUNC (gl.effective_date) BETWEEN :P_date_from and :P_date_to

     and gh.actual_flag = 'A'

     and gc.segment4 =  '1340101001'

     and gc.segment1 =  '20'

     and gc.segment4 =  '23'

     and gh.je_source = 'Payables'

 Periodic Source

select gh.je_source

      ,gh.period_name

      ,gcc.segment4 Natural_Code

      ,gcc.segment1 Division_Code

      ,gcc.CONCATENATED_SEGMENTS ACCOUNT_CODE     

      ,gl.accounted_dr

      ,gl.accounted_cr

      ,nvl(gl.accounted_dr,0) - nvl(gl.accounted_cr,0) NET

      ,ood.ORGANIZATION_CODE

      ,ood.OPERATING_UNIT

      ,mtt.TRANSACTION_TYPE_NAME

      ,Case when mmt.transaction_type_id in (18,36) then

            (select segment1 from po_headers_all pha where pha.po_header_id=mmt.TRANSACTION_SOURCE_ID)

       end PO_Number   

      ,to_char(mmt.transaction_id)

      ,msi.SEGMENT1  PREFIX

      ,msi.CONCATENATED_SEGMENTS 

      ,mmt.TRANSACTION_QUANTITY

--      ,gh.name jv_name

--      ,gb.name batch_name

--      ,gh.je_category

--      ,gir.GL_SL_LINK_TABLE

--      ,gir.GL_SL_LINK_ID     

--      ,cal.*

from  gl_je_headers gh

     ,gl_je_lines gl

     ,gl_import_references gir

     ,gl_je_batches gb

     ,gl_code_combinations_kfv gcc

     ,cst_ae_lines cal

     ,cst_ae_headers cah

     ,mtl_material_transactions mmt

     ,mtl_transaction_types mtt

     ,mtl_system_items_kfv msi

     ,org_organization_definitions ood

where gh.je_header_id   = gl.je_header_id

--and gh.je_header_id     = 2296647 --2296591

and gir.je_header_id    = gh.je_header_id

and gir.JE_LINE_NUM     = gl.JE_LINE_NUM

and gir .je_batch_id  = gb.je_batch_id

and gl.code_combination_id = gcc.code_combination_id

and gir.GL_SL_LINK_TABLE = 'CSTECL'

and JE_CATEGORY = 'MTL'

and cal.GL_SL_LINK_ID    = gir.GL_SL_LINK_ID

AND cah.ae_header_id = cal.ae_header_id

--and gh.period_name      = 'JUN-11'

and trunc(gl.EFFECTIVE_DATE) between :P_date_from and :P_date_to

--and gcc.segment4 = :P_Segment4

and gcc.segment4 = nvl(:P_Segment4, gcc.segment4)

and gcc.segment1 = nvl(:P_Segment1, gcc.segment1)

and gcc.segment2 = nvl(:P_Segment2, gcc.segment2)

AND (cah.accounting_event_id = mmt.transaction_id)

and mtt.transaction_type_id = mmt.transaction_type_id

and msi.inventory_item_id = mmt.inventory_item_id 

and msi.organization_id = 87

and gh.ledger_id = nvl(:P_ledger_id, gh.ledger_id)

and mmt.organization_id = ood.organization_id

UNION ALL

select gh.je_source

      ,gh.period_name

      ,gcc.segment4

      ,gcc.segment1

      ,gcc.CONCATENATED_SEGMENTS ACCOUNT_CODE     

      ,gl.accounted_dr

      ,gl.accounted_cr

      ,nvl(gl.accounted_dr,0) - nvl(gl.accounted_cr,0) NET

      ,ood.ORGANIZATION_CODE

      ,ood.OPERATING_UNIT

      ,rt.SOURCE_DOCUMENT_CODE||'-'||rt.TRANSACTION_TYPE TRANSACTION_TYPE_NAME

      ,pha.segment1 PO_Number

      ,to_char(rt.transaction_id)

      ,msi.SEGMENT1

      ,msi.CONCATENATED_SEGMENTS

      ,rt.quantity     

--      ,gir.GL_SL_LINK_TABLE

--      ,gir.GL_SL_LINK_ID

--      ,gh.name jv_name

--      ,gb.name batch_name

--      ,gh.je_category

--      ,cah.ACCT_EVENT_SOURCE_TABLE

from  gl_je_headers gh

     ,gl_je_lines gl

     ,gl_import_references gir

     ,gl_je_batches gb

     ,gl_code_combinations_kfv gcc

     ,cst_ae_lines cal

     ,cst_ae_headers cah

     ,org_organization_definitions ood

     ,rcv_transactions rt

     ,po_headers_all pha

     ,po_lines_all pla

     ,mtl_system_items_kfv msi

where gh.je_header_id   = gl.je_header_id

--and gh.je_header_id     = 2296647 --2296591

and gir.je_header_id    = gh.je_header_id

and gir.JE_LINE_NUM     = gl.JE_LINE_NUM

and gir .je_batch_id  = gb.je_batch_id

and gl.code_combination_id = gcc.code_combination_id

and gh.ledger_id = nvl(:P_ledger_id, gh.ledger_id)

and gir.GL_SL_LINK_TABLE = 'CSTECL'

and JE_CATEGORY = 'Receiving'

and cal.GL_SL_LINK_ID    = gir.GL_SL_LINK_ID

AND cah.ae_header_id = cal.ae_header_id

--and gh.period_name      = 'JUN-11'

and trunc(gl.EFFECTIVE_DATE) between :P_date_from and :P_date_to

and gcc.segment4 = nvl(:P_Segment4, gcc.segment4)

and gcc.segment1 = nvl(:P_Segment1, gcc.segment1)

and gcc.segment2 = nvl(:P_Segment2, gcc.segment2)

and cah.organization_id = rt.organization_id 

AND (cah.accounting_event_id = rt.transaction_id)

and rt.organization_id   = ood.organization_id

and rt.po_header_id = pha.po_header_id

and pla.po_header_id = pha.po_header_id

and rt.po_line_id = pla.po_line_id

and pla.item_id = msi.inventory_item_id

and msi.organization_id = 87