Tuesday, September 11, 2012

PO HEADER QUERY 11i/R12

select pha.segment1 PONUMBER,
        hou.name ORGANIZATION_CODE,
       PHA.type_lookup_code POTYPE,
       trunc(pha.creation_date) CDATE,
       pv.vendor_name supplier,
       pv.segment1 supplier_number,
       pvs.vendor_site_code suppliersite,
       --(pvc.first_name|| pvc.last_name) Contact,
       hl1.location_code Shipto,
       hl2.location_code BILLTO,
       pha.currency_code CURRENCY,
       papf.full_name BUYER,
       pha.authorization_status postatus,
       --sum(pla.quantity*pla.unit_price) Linelevelprice,
       pha.comments COMMENTS,
       atl.name TERMS,
       plla.need_by_date,
       plla.promised_date,
       pha.closed_code
from apps.po_headers_all pha,
apps.ap_suppliers pv,
apps.po_vendor_sites_all pvs,
--apps.po_vendor_contacts pvc,
hr_locations hl1,
hr_locations hl2,
apps.per_all_people_f papf,
apps.po_lines_all pla,
hr_operating_units hou,
apps.ap_terms_tl atl,
apps.po_line_locations_all plla
--po_distributions_all pda
where  pha.vendor_id=pv.vendor_id
and pha.vendor_site_id=pvs.vendor_site_id
and pha.ship_to_location_id= hl1.location_id
and pha.bill_to_location_id= hl2.location_id
and pha.agent_id=papf.person_id
and pha.po_header_id=pla.po_header_id
and pha.org_id= hou.organization_id
and pha.terms_id=atl.term_id
and plla.po_header_id=pha.po_header_id
and pla.po_line_id=plla.po_line_id
and pha.org_id=plla.org_id
--and pla.po_line_id=pda.po_line_id
--and pha.closed_code='OPEN'
--and pha.AUTHORIZATION_STATUS ='APPROVED'
--and pha.AUTHORIZATION_STATUS !='Rejected'
group by  pha.segment1,
        hou.name,
       PHA.type_lookup_code ,
       trunc(pha.creation_date) ,
       pv.vendor_name ,
       pv.segment1 ,
       pvs.vendor_site_code ,
       --(pvc.first_name|| pvc.last_name) Contact,
       hl1.location_code ,
       hl2.location_code ,
       pha.currency_code ,
       papf.full_name ,
       pha.authorization_status ,
       --sum(pla.quantity*pla.unit_price) Linelevelprice,
       pha.comments ,
       atl.name,
       plla.need_by_date,
       plla.promised_date,
       pha.closed_code

No comments:

Post a Comment