PO Purchase Order Detail

SELECT   poh.segment1                 PO_Number
,        poh.creation_date            Creation_Date
,        plc_sta.displayed_field ||
         decode(poh.cancel_flag,
                'Y',', '||plc_can.displayed_field,NULL) ||
         decode(nvl(poh.closed_code, 'OPEN'),
                       'OPEN', NULL, ', '||plc_clo.displayed_field) ||
         decode(poh.frozen_flag,
                'Y',', '||plc_fro.displayed_field,NULL) ||
         decode(poh.user_hold_flag, 'Y',
                       ', '||plc_hld.displayed_field, NULL)    Status
,        poh.revision_num             Rev
,        poh.printed_date             Printed_Date
,        pdt.type_name                Type
,        ppf.full_name                Buyer
,        poh.revised_date             Revised_Date
,        poh.currency_code            C_CURRENCY
,        pov.vendor_name              Vendor
,        poh.acceptance_due_date      Acceptance_Date
,        decode(poh.user_hold_flag,'Y',:yes,'N',:no,null)
                                      On_Hold
,        pvs.vendor_site_code         Vendor_Site
,        decode(poh.acceptance_required_flag, 'Y',:yes, 'N',:no,null)
                                      Acceptance_Required
,        SUM (DECODE (POL.order_type_lookup_code,               
                                  'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
                                  'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
                                  PLL.price_override * (PLL.quantity - NVL(PLL.quantity_cancelled, 0))))
                            C_AMOUNT_AGR
,        poh.po_header_id
,        fc.precision	c_fnd_precision, 
	SUM ( DECODE ( POL.order_type_lookup_code , 'RATE' , PO_POXPOSTD_XMLP_PKG.c_amount_agr_round( PLL.amount - NVL ( PLL.amount_cancelled , 0 ), fc.precision ), 'FIXED PRICE' , PO_POXPOSTD_XMLP_PKG.c_amount_agr_round( PLL.amount - NVL ( PLL.amount_cancelled , 0 ), fc.precision ), PO_POXPOSTD_XMLP_PKG.c_amount_agr_round( PLL.price_override * ( PLL.quantity - NVL ( PLL.quantity_cancelled , 0 ) ), fc.precision ) ) ) C_AMOUNT_AGR_ROUND
FROM     po_line_locations      pll
,        fnd_currencies         fc
,        po_headers             poh
,        po_lines                  POL                 
,        po_vendors             pov
,        po_vendor_sites        pvs
,        per_people_f              ppf
,        po_system_parameters   psp
,        po_lookup_codes        plc_sta
,        po_lookup_codes        plc_can
,        po_lookup_codes        plc_clo
,        po_lookup_codes        plc_fro
,        po_lookup_codes        plc_hld
,        po_document_types      pdt
WHERE    poh.vendor_id          = pov.vendor_id (+)
AND      poh.currency_code = fc.currency_code
AND      pdt.document_type_code = 'PO'
AND      pdt.document_subtype   = poh.type_lookup_code
AND      plc_sta.lookup_code     = nvl(poh.authorization_status, 'INCOMPLETE')
AND      POL.po_header_id (+) = POH.po_header_id            
AND      PLL.po_line_id (+) = POL.po_line_id                       
AND      nvl(pll.shipment_type,'STANDARD')      in ('STANDARD', 'PLANNED')
AND      poh.agent_id           = ppf.person_id
AND      poh.vendor_site_id     = pvs.vendor_site_id(+) 
AND      poh.type_lookup_code   in ('STANDARD','PLANNED')
AND      trunc(sysdate)
         BETWEEN ppf.effective_start_date
         AND ppf.effective_end_date
 AND      plc_sta.lookup_type in ('PO APPROVAL','DOCUMENT STATE')
AND      plc_can.lookup_code    = 'CANCELLED'
AND      plc_can.lookup_type    = 'DOCUMENT STATE'
AND      plc_clo.lookup_code    = nvl(poh.closed_code,'OPEN')
AND      plc_clo.lookup_type    = 'DOCUMENT STATE'
AND      plc_fro.lookup_code    = 'FROZEN'
AND      plc_fro.lookup_type    = 'DOCUMENT STATE'
AND      plc_hld.lookup_code    = 'ON HOLD'
AND      plc_hld.lookup_type    = 'DOCUMENT STATE'
AND      (:P_status             is NULL
         OR :P_status         = nvl(poh.authorization_status,'INCOMPLETE')
         OR (:P_status  = 'RESERVED'
                AND EXISTS 
                         ( select  'do we have uncancelled/not_f_closed shipments'
                     FROM   PO_LINE_LOCATIONS POLL1
                     WHERE  POLL1.po_header_id = poh.po_header_id 
                     AND    nvl(POLL1.cancel_flag, 'N') = 'N'
                     AND    nvl(POLL1.closed_code, 'OPEN') != 'FINALLY CLOSED' )
               AND NOT EXISTS
                   ( SELECT 'Do we have uncancelled/not f_closed shipments'
                     FROM   PO_LINE_LOCATIONS POLL2
                     WHERE  POLL2.po_header_id = poh.po_header_id 
                     AND    nvl(POLL2.encumbered_flag, 'N') = 'N' 
                     AND    POLL2.shipment_type in ('STANDARD', 'PLANNED')
                     AND    nvl(POLL2.cancel_flag, 'N') != 'Y'
                     AND    nvl(POLL2.closed_code, 'OPEN') !=  'FINALLY CLOSED')))
AND      (:P_buyer              is NULL
         OR :P_buyer            = ppf.person_id)
AND      (:P_vendor_from        is NULL
         OR pov.vendor_name     >= :P_vendor_from)
AND      (:P_vendor_to          is NULL
OR       pov.vendor_name        <= :P_vendor_to) 
&WHERE_PERFORMANCE 
AND      EXISTS       (SELECT   'Category Flexfield Range'
                       FROM     po_lines            pol
                       ,        mtl_categories      mca
                       WHERE    pol.category_id     = mca.category_id
                       AND      &P_WHERE_CAT
	     AND      poh.po_header_id = pol.po_header_id)
AND      EXISTS       (SELECT   'Item Flexfield Range'
                       FROM     po_lines            pol
                       ,        mtl_system_items    msi
                       WHERE    pol.item_id         = msi.inventory_item_id (+)
                       AND      nvl(msi.organization_id,:c_organization_id) 
                                    = :c_organization_id
                       AND      &P_WHERE_ITEM
	     AND       poh.po_header_id  = pol.po_header_id)
GROUP BY poh.segment1
,        poh.creation_date      
,        poh.revision_num      
,        poh.printed_date       
,        pdt.type_name        
,        ppf.full_name       
,        poh.revised_date          
,        poh.currency_code       
,        pov.vendor_name        
,        poh.acceptance_due_date   
,        decode(poh.user_hold_flag,'Y',:yes,'N',:no,null)
,        pvs.vendor_site_code        
,        decode(poh.acceptance_required_flag, 'Y',:yes, 'N',:no,null)
,        poh.po_header_id
,        decode(psp.manual_po_num_type,'NUMERIC',
                null,poh.segment1)
,        decode(psp.manual_po_num_type,'NUMERIC',
	decode(rtrim(poh.segment1,'0123456789'),NULL,
                to_number(poh.segment1),null),null)
,        plc_sta.displayed_field  ||              
          decode(poh.cancel_flag,                                         
                 'Y',', '||plc_can.displayed_field,NULL) ||
          decode(nvl(poh.closed_code,'OPEN'),
                        'OPEN', NULL, ', '||plc_clo.displayed_field) ||
          decode(poh.frozen_flag,                                         
                 'Y',', '||plc_fro.displayed_field,NULL) ||              
          decode(poh.user_hold_flag, 'Y', ', '||plc_hld.displayed_field, NULL)
,            fc.precision
ORDER BY pov.vendor_name
,        decode(psp.manual_po_num_type,'NUMERIC',
                null,poh.segment1)
,        decode(psp.manual_po_num_type,'NUMERIC',
	decode(rtrim(poh.segment1,'0123456789'),NULL,
                to_number(poh.segment1),null),null)