PO Purchase Order Detail

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Purchase Order Detail Report (XML)
Short Name: POXPOSTD_XML
DB package: PO_POXPOSTD_XMLP_PKG
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)
Parameter Name SQL text Validation
P_STRUCT_NUM
 
Number
Dynamic Precision Option
 
LOV Oracle
Status
 
LOV Oracle
To
 
LOV Oracle
PO Numbers From
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
To
 
Categories From
 
To
 
Items From
 
Buyer Name
 
LOV Oracle
Title