[E-Bussiness Suite] Query PR - PO - Vendor

Deskripsi
    Untuk mendapatkan data PR - PO + PO Amount dan Nama Vendor berdasarkan range date create PR dan ID Vendor

Howto
select distinct prha.SEGMENT1 as PR
               ,prha.DESCRIPTION as PR_DESC
               ,prha.AUTHORIZATION_STATUS as PR_STATUS
               ,pha.SEGMENT1 as PO
               ,PO_AMOUNT.amount as PO_Amount
               ,pha.AUTHORIZATION_STATUS as PO_STATUS
               ,pv.VENDOR_NAME
               ,prha.CREATION_DATE as PR_DATE
             
from  po_requisition_headers_all PRHA
     ,po_requisition_lines_all PRLA
     ,po_line_locations_all PLLA
     ,po_headers_all PHA
     ,po_lines_all pla
     ,po_vendors PV
     ,(select  PHA.po_header_id
              ,SUM(PLA.UNIT_PRICE*PLA.QUANTITY) as Amount
              ,pv.vendor_id
       from PO_HEADERS_ALL PHA
           ,PO_LINES_ALL PLA
           ,po_vendors PV
       where PHA.po_header_id = PLA.po_header_id
       and pha.VENDOR_ID = pv.VENDOR_ID
       and pv.VENDOR_ID = :VENDOR_ID
       group by PHA.po_header_id,pv.vendor_id) PO_AMOUNT

where prha.CREATION_DATE >= to_date('01/01/2011','DD/MM/YYYY')
and prha.CREATION_DATE <= to_date('31/12/2013','DD/MM/YYYY')
and prha.REQUISITION_HEADER_ID = prla.REQUISITION_HEADER_ID(+)
and prla.LINE_LOCATION_ID = plla.LINE_LOCATION_ID(+)
and plla.PO_LINE_ID = pla.PO_LINE_ID(+)
and pla.PO_HEADER_ID = pha.PO_HEADER_ID(+)
and pha.PO_HEADER_ID = PO_AMOUNT.po_header_id
and pha.VENDOR_ID = pv.VENDOR_ID
and pv.VENDOR_ID = :VENDOR_ID

group by prha.SEGMENT1
        ,po_amount.amount
        ,prha.DESCRIPTION
        ,prha.AUTHORIZATION_STATUS
        ,pha.SEGMENT1
        ,pha.AUTHORIZATION_STATUS
        ,pv.VENDOR_NAME
        ,prha.CREATION_DATE