SQL Scripts to get PO Quotations Details

---To extact PO Quotation  headers
SELECT pha.segment1 Documet_number
      ,pha.type_lookup_code document_type_code
      ,pha.quote_type_lookup_code Document_Sub_Type
      ,pv.segment1 Vendor_Number
      ,pvs.Vendor_Site_Code
      ,s_loc.location_code Ship_to_Location
      ,b_loc.location_code Bill_to_location 
      ,pap.employee_number Buyer
      ,pha.currency_code
      ,decode(status_lookup_code,'I','INCOMPLETE','A','APPROVED','C','CLOSED') Approva_status
      ,approved_date
      ,pha.org_id
      ,pha.rate_type
      ,pha.rate_date
      ,pha.creation_date
      ,pha.created_by
FROM apps.po_headers_all pha,
     apps.Po_vendors pv,
     apps.po_vendor_sites_all pvs,
     apps.per_all_people_f  pap,
     apps.hr_locations_all b_loc,
     apps.hr_locations_all s_loc
WHERE pha.vendor_id=pv.vendor_id
AND   pvs.vendor_site_id=pha.vendor_site_id
AND pap.person_id=pha.agent_id
AND b_loc.location_id=pha.bill_to_location_id
AND  s_loc.location_id=pha.ship_to_location_id
AND pha.type_lookup_code='QUOTATION'
AND pha.org_id            =2661
order by 1;

--To find inventory organizations for OU
select organization_id,organization_name from apps.org_organization_definitions where operating_unit=2661;
--To extact PO Quotation Lines
SELECT pha.segment1 Documet_number
      ,pla.line_num
      ,plt.line_type
      ,msi.segment1 Item
      ,pla.unit_meas_lookup_code Unit_Of_Measure
      ,pla.unit_price
      ,pla.vendor_product_num Supplier_Item
      ,pla.creation_date
      ,pla.created_by
FROM apps.po_lines_all pla
    ,apps.po_headers_all pha
   ,(select distinct segment1,inventory_item_id from apps.MTL_SYSTEM_ITEMS_B msi where organization_id in (3519,3619)) msi
   ,apps.po_line_types_tl plt
WHERE pha.po_header_id     =pla.po_header_id
AND pha.type_lookup_code='QUOTATION'
AND plt.line_type_id=pla.line_type_id
and plt.language='US'
AND msi.inventory_item_id=pla.item_id
AND pha.org_id          =2661
order by 1,2 asc;


--To extact Po Quotation line  Breaks
SELECT pha.segment1 Documet_number ,
  pla.line_num ,
  plla.shipment_num ,
  plla.shipment_type,
  msi.segment1 Item ,
  plla.quantity ,
  plla.PRICE_OVERRIDE Unit_Price ,
  plla.unit_meas_lookup_code Unit_Of_Measure ,
  (SELECT ood.organization_code
  FROM apps.ORG_ORGANIZATION_DEFINITIONS ood
  WHERE ood.organization_id=plla.ship_to_organization_id
  ) ship_to_organization_code ,
  plla.start_date ,
  plla.end_date ,
  plla.po_header_id ,
  plla.po_line_id,
  plla.creation_date,
  plla.created_by
FROM apps.po_line_locations_all plla ,
  apps.po_lines_all pla ,
  apps.po_headers_all pha ,
  (select distinct segment1,inventory_item_id from apps.MTL_SYSTEM_ITEMS_B msi where organization_id in (3519,3619)) msi
WHERE plla.po_line_id=pla.po_line_id
AND pha.po_header_id =pla.po_header_id
AND pha.type_lookup_code='QUOTATION'
AND msi.inventory_item_id=pla.item_id
AND pha.org_id           =2661
ORDER BY 1,2,3 ASC;