P2P, O2C Flow SQL Query Link

P2P Flow SQL Query Link

select prh.segment1 "PO Requisition Number",
       pha.segment1 "PO Number",
       aps.SEGMENT1 "Supplier Number",
       aps.vendor_name,
       apss.vendor_site_code,
       apsc.first_name,
       apsc.last_name,
       pla.item_id,
       plla.ship_to_organization_id,
       plla.ship_to_location_id,
       rt.transaction_type,
       rt.destination_type_code,
       rsh.receipt_num "PO Receipt Number",
       aia.invoice_num,
       aida.dist_code_combination_id,
       aca.check_number,gjh.ledger_id,
       gjh.name
  from po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       po_headers_all pha,
       po_lines_all pla,
       po_distributions_all pda,
       po_line_locations_all plla,
       ap_suppliers aps,
       ap_supplier_sites_all apss,
       ap_supplier_contacts apsc,
       rcv_transactions rt,
       rcv_shipment_headers rsh,
       rcv_shipment_lines rsl,
       ap_invoices_all aia,
       ap_invoice_lines_all aila,
       ap_invoice_distributions_all aida,
       ap_invoice_payments_all aipa,
       ap_checks_all aca,
       xla.xla_transaction_entities xte,
       xla_events xe,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_distribution_links xdl,
       gl_import_references gir,
       gl_je_batches gjb,
       gl_je_headers gjh,
       gl_je_lines gjl
 where prh.segment1 = :RequitionNumber --Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
   and aps.vendor_id = pha.vendor_id
   and apss.vendor_id = aps.vendor_id
   and apss.vendor_site_id (+) = pha.vendor_site_id
   and apss.vendor_site_id  = aca.vendor_site_id
   and apsc.vendor_site_id = apss.vendor_site_id
   and apsc.vendor_contact_id = pha.vendor_contact_id
   and prl.requisition_header_id = prh.requisition_header_id
   and prd.requisition_line_id = prl.requisition_line_id
   and pda.req_distribution_id = prd.distribution_id
   and pla.po_header_id = pda.po_header_id
   and pla.po_line_id = pda.po_line_id
   and pha.po_header_id = pla.po_header_id
   and pha.org_id = 204
   and plla.po_header_id = pla.po_header_id
   and plla.po_line_id = pla.po_line_id
   and rt.transaction_type = 'DELIVER'
   and rt.po_header_id = pha.po_header_id
   and rt.po_line_id = pla.po_line_id
   and rsh.shipment_header_id = rt.shipment_header_id  
   and rsl.shipment_header_id = rsh.shipment_header_id
   and rsl.shipment_line_id = rt.shipment_line_id
   and aila.po_header_id = pha.po_header_id
   and aila.po_line_id = pla.po_line_id
   and aia.invoice_id = aila.invoice_id
   and aida.invoice_id = aila.invoice_id
   and aida.invoice_line_number = aila.line_number
   and aipa.invoice_id = aia.invoice_id
   and aca.check_id = aipa.check_id
   and xte.entity_code = 'AP_PAYMENTS'
   and xte.transaction_number = aca.check_number
   and xte.source_id_int_1 = aipa.check_id
   and xte.security_id_int_1 = aia.org_id
   and xe.entity_id = xte.entity_id
   and xah.event_id = xe.event_id
   and xal.ae_header_id = xah.ae_header_id
   and xal.ae_line_num = aida.invoice_line_number
   and xdl.ae_header_id = xah.ae_header_id
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.applied_to_dist_id_num_1 = aida.invoice_distribution_id
   and gir.reference_5 = xte.entity_id  -- Entity Id
   and gir.reference_6 = to_char(xe.event_id) --Event Id
   and gir.reference_7 = to_char (xah.ae_header_id) -- AE Header Id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   --and gir.created_by = 1318
   and gjb.je_batch_id = gir.je_batch_id
   and gjh.je_batch_id=gjb.je_batch_id
   and gjh.je_header_id = gir.je_header_id
   and gjl.je_header_id=gjh.je_header_id
   and gjl.je_line_num= gir.je_line_num

O2C Flow SQL Query Link

Posted by Veluruga 0 comments

select ooha.order_number,ooha.org_id,
       hca.account_name,
       hp.party_name "Customer Name",
       hcasab.orig_system_reference      BILL_TO_ORIG_REF,
       hpsb.status                       BILL_TO_STATUS,
       'ADDRESS1 - '||bill_loc.address1||','||CHR(10)|| 'ADDRESS2 - '||bill_loc.address2||','||CHR(10)||
       'ADDRESS3 - '||bill_loc.address3||','||CHR(10)|| 'CITY     - '||bill_loc.city||','||CHR(10)||
       'POSTAL CD- '||bill_loc.postal_code||','||CHR(10)|| 'COUNTRY  - '|| bill_loc.country  BILL_TO_ADDRESS,
       hcasas.orig_system_reference      SHIP_TO_ORIG_REF,
       hpss.status SHIP_TO_STATUS,
       'ADDRESS1 - '||ship_loc.address1||','||CHR(10)|| 'ADDRESS2 - '||ship_loc.address2||','||CHR(10)||
       'ADDRESS3 - '||ship_loc.address3||','||CHR(10)|| 'CITY     - '||ship_loc.city||','||CHR(10)||
       'POSTAL CD- '||ship_loc.postal_code||','||CHR(10)|| 'COUNTRY  - '|| ship_loc.country  SHIP_TO_ADDRESS,
       oola.inventory_item_id,oola.ordered_item,
       msib.description item_description,
       wnd.name delivery_number,
       rct.trx_number "AR Invoice Number",
       acr.receipt_number "AR Receipt Number",
       gjh.ledger_id,
       gjh.name
  from oe_order_headers_all ooha,
       oe_order_lines_all oola,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hpss,
       hz_party_sites hpsb,
       hz_locations bill_loc,
       hz_locations ship_loc,
       hz_cust_acct_sites_all hcasab,
       hz_cust_acct_sites_all hcasas,
       hz_cust_site_uses_all hzsuab,
       hz_cust_site_uses_all hzsuas,
       mtl_system_items_b msib,
       wsh_delivery_details wdd,
       wsh_new_deliveries wnd,
       wsh_delivery_assignments wda,
       ra_customer_trx_all rct,
       ra_customer_trx_lines_all rctl,
       ra_cust_trx_line_gl_dist_all rctld,        
       ar_cash_receipts_all acr,
       xla.xla_transaction_entities xte,
       xla_events xe,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_distribution_links xdl,
       gl_import_references gir,
       gl_je_batches gjb,
       gl_je_headers gjh,
       gl_je_lines gjl
 where ooha.order_number = :SalesOrderNumber --Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
   and ooha.org_id = 204
   and hca.cust_account_id    = ooha.sold_to_org_id
   and hp.party_id            = hca.party_id
   and hpss.party_id            = hca.party_id
   and hpsb.party_id            = hca.party_id
   and bill_loc.location_id = hpss.location_id
   and ship_loc.location_id = hpsb.location_id
   AND hcasas.cust_account_id  = hca.cust_account_id
   AND hcasab.cust_account_id  = hca.cust_account_id
   AND hcasas.party_site_id    = hpss.party_site_id
   AND hcasab.party_site_id    = hpsb.party_site_id
   and hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
   and hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
   and hzsuas.site_use_id = ooha.ship_to_org_id
   and hzsuab.site_use_id = ooha.invoice_to_org_id            
   and wda.delivery_id        = wnd.delivery_id(+)
   and wdd.delivery_detail_id = wda.delivery_detail_id
   and wdd.source_header_id   = ooha.header_id
   and wdd.source_line_id     = oola.line_id
   and wdd.organization_id    = msib.organization_id(+)
   and wdd.inventory_item_id  =msib.inventory_item_id(+)
   and rct.interface_header_attribute1 = to_char(ooha.order_number)
   and rct.org_id = ooha.org_id
   and rctl.customer_trx_id = rct.customer_trx_id
   and rctl.sales_order = to_char(ooha.order_number)
   and rctld.customer_trx_id = rct.customer_trx_id
   and rctld.customer_trx_line_id = rctl.customer_trx_line_id
   and acr.receipt_number = 'G-1001'
   and acr.pay_from_customer = rct.sold_to_customer_id
   and acr.org_id = ooha.org_id
   and acr.customer_site_use_id = rct.bill_to_site_use_id
   and xte.transaction_number = acr.receipt_number
   and xte.entity_code = 'RECEIPTS'
   and xe.entity_id = xte.entity_id  
   and xah.event_id = xe.event_id
   and xal.ae_header_id = xah.ae_header_id
   and xal.accounting_class_code = 'CASH'
   and xdl.ae_header_id = xah.ae_header_id
   and xdl.ae_line_num = xal.ae_line_num
   --and xdl.source_distribution_id_num_1
   and gir.reference_5 = xte.entity_id  -- Entity Id
   and gir.reference_6 = to_char(xe.event_id) --Event Id
   and gir.reference_7 = to_char (xah.ae_header_id) -- AE Header Id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and gir.created_by = 1318
   and gjb.je_batch_id = gir.je_batch_id
   and gjh.je_batch_id=gjb.je_batch_id
   and gjh.je_header_id = gir.je_header_id
   and gjl.je_header_id=gjh.je_header_id
   and gjl.je_line_num= gir.je_line_num