11 Replies Latest reply on Aug 19, 2014 4:34 AM by Jagadekara

    Oracle Purchase Order Query


      Hi All,


      I have been assigned a task wherein I have to get all the values appearing on the form for a particular PO(Headers,Lines,Shipments,Distributions)

      for simplification I am giving the navigation


      Nav:Purchasing Super User-->Purchase Orders-->PO Summary

      Upon clicking enter the following PO Number 20100001 and click on Open then you shall find the Header and Lines info, on that that same form click on Shipments and on Shipment form click on Distributions there you will find 2nd column as Type whose value for this po is 'Expense', now I am not able to determine correctly as to where this column comes from, my best guess was destination_type_code from po_distributions_all but the value coming from there is in CAPS as EXPENSE.


      Please tell me how to get the value for that, I am posting my query for reference.


      SELECT hout.name operating_unit                                      ,
                   pha.segment1 po_number                                        ,
                   to_char(pha.creation_date, 'DD-MON-RRRR HH24:MI:SS') creation_date,
                   asa.vendor_name supplier                                      ,
                   hrla.location_code ship_to                                    ,
                   hrlb.location_code bill_to                                    ,
                   pha.revision_num rev                                          ,
                   ppf.full_name buyer                                           ,
                   pvs.vendor_site_code site                                     ,
                   podt.type_name type                                           ,
                   po_headers_sv3.get_po_status(pha.po_header_id) status         ,
                   pol.line_num num                                              ,
                   plt.line_type type                                            ,
                   mcb.segment1||mcb.segment2||mcb.segment3||mcb.segment4 category   ,
                   pol.item_description description                              ,
                   pol.unit_meas_lookup_code uom                                 ,
                   to_char(pll.need_by_date,'DD-MON-RRRR HH24:MI:SS') need_by    ,
                   pol.quantity  Quantity                                        ,
                   pha.currency_code currency                                    ,
                   pol.unit_price price                                          ,
                   pol.quantity*pol.unit_price amount                            ,
                   gcc.segment9 charge_account                                   ,
                   pll.shipment_num                                              ,
                   ood.organization_code                                         ,
                   (select location_code from hr_locations_all where ship_to_location_id = 468) Ship_to  ,
                   pda.distribution_num distribution_num                         ,
        FROM po_headers_all pha ,
                   hr_operating_units hout,
                  ap_suppliers asa ,
                  hr_locations_all hrla,
                  hr_locations_all hrlb,
                  per_all_people_f ppf ,
                  po_vendor_sites_all pvs ,
                  po_document_types_all podt,
                  po_lines_all pol ,
                  po_line_types  plt ,
                  mtl_categories_b  mcb ,
                  po_distributions_all pda ,
                  gl_code_combinations gcc ,
                  po_line_locations_all pll ,
                  org_organization_definitions ood

      WHERE 1 = 1

         AND podt.org_id = pha.org_id

         AND pvs.vendor_site_id = pha.vendor_site_id

         AND pha.agent_id = ppf.person_id

         AND hrlb.location_id = pha.bill_to_location_id

         AND hrla.location_id = pha.ship_to_location_id

         AND hout.organization_id = pha.org_id

         AND asa.vendor_id = pha.vendor_id

         AND TRUNC(SYSDATE) BETWEEN TRUNC(ppf.effective_start_date) AND


         AND pha.segment1 = '20100001'

         AND pha.type_lookup_code = 'STANDARD'

         AND pha.org_id = 214

         AND podt.document_type_code = 'PO'

         AND podt.document_subtype = 'STANDARD'

         AND plt.line_type_id = pol.line_type_id

         AND pha.po_header_id = pol.po_header_id

         AND mcb.category_id = pol.category_id

         AND pda.code_combination_id = gcc.code_combination_id

         AND pda.po_header_id = pha.po_header_id

         AND pll.po_header_id = pll.po_header_id

         AND pll.po_line_id = pol.po_line_id

         AND pll.ship_to_organization_id = ood.organization_id