Thursday, February 25, 2016

Oracle P2P cycle Queries



Joining between RFQ,Quotation and Purchase order

SELECT   R.SEGMENT1 AS RFQ_NUM, Q.SEGMENT1 AS QUOTATION_NUM
  FROM   PO_HEADERS_ALL R, PO_HEADERS_ALL Q, PO_HEADERS_ALL P
 WHERE       R.PO_HEADER_ID = Q.FROM_HEADER_ID
         AND Q.PO_HEADER_ID = P.FROM_HEADER_ID
         AND P.SEGMENT1 = '6128'(Purchase Order number)

Joining between PO Requisitions and Purchase Orders

SELECT   prha.segment1 AS pr_number
  FROM   po_requisition_headers_all prha,
         po_requisition_lines_all prla,
         po_req_distributions_all prda,
         po_distributions_all pda,
         po_headers_all pha
 WHERE       prha.requisition_header_id = prla.requisition_header_id
         AND prla.requisition_line_id = prda.requisition_line_id
         AND prda.distribution_id = pda.req_distribution_id
         AND pda.po_header_id = pha.po_header_id
         AND pha.segment1 = '6129';


Joining between AP_INVOICES AND PO_HEADERS
select pha.segment1,apa.invoice_id
from
ap_invoices_all apa
,ap_invoice_lines_all aila
,po_headers_all   pha
, ap_suppliers as1
where apa.invoice_id=aila.invoice_id
and aila.po_header_id=pha.po_header_id
and apa.invoice_num='10012'
and apa.vendor_id=as1.vendor_id
and trunc(apa.creation_date)=trunc(sysdate)-2


Joining between AP_INVOICES and xla tables

SELECT DISTINCT xe.*
FROM   ap_invoices_all ai,
       xla_events xe,
       xla.xla_transaction_entities xte
WHERE  xte.application_id = 200
AND    xte.application_id   = xe.application_id
AND    ai.invoice_id        = '212200'---Invoice id is dervied from above query
AND    xte.entity_code      = 'AP_INVOICES'
AND    xte.source_id_int_1  = ai.invoice_id
AND    xte.entity_id        = xe.entity_id
ORDER BY
       xe.entity_id,
       xe.event_number;

xla_transaction_entities (xte).entity_code =
'TRANSACTIONS' or 'RECEIPTS' or 'ADJUSTMENTS' or 'PURCHASE_ORDER' or
'AP_INVOICES' or 'AP_PAYMENTS' or 'MTL_ACCOUNTING_EVENTS' or
'WIP_ACCOUNTING_EVENTS'

xte.source_id_int_1 = 'INVOICE_ID' or 'CHECK_ID' or'TRX_NUMBER'

No comments:

Post a Comment