P2P Technical Flow(Complete Query)..........

-

P2P TECHNICAL FLOW

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

Comments

Post a Comment

Popular posts from this blog

Oracle Purchasing Module Step by Step in R12

-
Image
PURCHASING  All the Purchasing related activity information will be declared in Purchasing              Procure 2 Pay Cycle : Requisition à RFQ à Quotation à Quotation Analysis à Purchasing à Receiving à Invoice  à  Payment à Reconciliation PO-Integration :  PO  is integrated by GL, AP, I Supplier, Procurement, and Project Accounting  PO  Responsibility             Step1: Define  PO  Responsibility                         Nav: System administrator à Security à Responsibility à Define             Step2: Assign  PO  options to the PO Responsibility                         Nav: System administrator à Profile à system             Step3: Assign  PO  Responsibility to User                         Nav: System administrator à security à user à define Note:  PO  is including some of i.e. Options, Supplier, Item, Expenses, and Purchasing . Options : Options nothing but a Controlling future at the operating unit level.                         Th
Read more

Sub Ledger Accounting SLA (Complete Functional Information)

-
Image
SubLegder Accounting in R12 SLA Part -1 In these article, we will explore the very basics of SLA and why it exists. Firstly, what exactly does SLA do? SLA is a module which now sits between the SubLedgers like AP/AR etc and the General Ledger. Have a look at this diagram below.  As you will notice, SLA can act as a mediator between the subledgers and Oracle General Ledger. Before we progress, some terminologies of R12 must be revisited. In 11i we had set of books, and in R12 we call them Ledgers. Likewise in R12 we also have secondary ledgers and reporting ledgers. Hence from 11i perspective think of Ledger as Set of Books. As for Subledger, a Subledger is nothing but a module like AP/AR/PO/Inventory etc. In the diagram below, the second scenario is explained whereby let's say that payables module generates a charge account segment combination for an invoice distribution line as A.B.C.D. In such case, if SLA module is not customized, then the very same A.B.C.D
Read more

Difference between ATO and PTO in oracle apps

-
Difference between ATO and PTO in oracle apps 1) MAKE TO ORDER: This is based on Customer specification, you make the product and supply it to them. This is applicable for Engineering items or any Product which you can make based on Customer order Example: Engineering Items 2) ASSEMBLE TO ORDER: This is you have items in parts and it needs to be assembled according to customer requirements For example you have Laptop coming with standard configuration of 160GB Hard disk and 1 GB RAM. But there are customer orders coming in asking for 200GB Hard Disk and 2 GB RAM and if you can assemble them to suit Customer Requirement 3) CONFIGURE TO ORDER: This is Configuring the Product itself to suit Customer requirements Example. The Thread Manufacturing unit Machineries are configured. I have seen Laxmi Machine Works in INDIA which manufactures textile Machineries configure the machineries based on No.of spindles 4) PICK TO ORDER: This is same as Computer Example in ATO. But the  difference is
Read more