PO Purchase Order and Releases Detail

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Purchase Order and Releases Detail Report (XML)
Short Name: POXBLREL_XML
DB package: PO_POXBLREL_XMLP_PKG
SELECT  distinct poh.segment1         PO_Number
,        por.creation_date            Creation_Date
,        plc.displayed_field          Status
,        por.revision_num             Rev
,        por.printed_date             Printed_Date
,        pdt.type_name                Type
,        hre.full_name                Buyer
,        por.revised_date          Revised_Date
,        poh.currency_code            C_CURRENCY
,        pov.vendor_name              Vendor
,        por.acceptance_due_date      Acceptance_Date
,        pvs.vendor_site_code         Vendor_Site
,        decode(poh.user_hold_flag,'Y',:yes,'N',:no,null)
                                      On_Hold
,        decode(por.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),
                                   NVL(PLL.price_override, 0)*(NVL(PLL.quantity, 0) - NVL(PLL.quantity_cancelled, 0)))) C_AMOUNT_RELEASE
,        poh.po_header_id po_header_id2
,        por.release_num              Release
,        poh.blanket_total_amount     C_AMOUNT_AGREED
,        poh.amount_limit             C_AMOUNT_LIMIT
,        por.release_date             Release_Date
,        pll.po_release_id
,        decode(psp1.manual_po_num_type,'NUMERIC',
                null,poh.segment1)
,        decode(psp1.manual_po_num_type,'NUMERIC',
                to_number(poh.segment1),null)
FROM     po_line_locations      pll
,        po_lines               pol
,        po_releases            por
,        po_headers             poh
,        po_vendor_sites        pvs
,        po_vendors             pov
,        hr_employees        hre
,        po_system_parameters   psp1
,        po_document_types      pdt
,        po_lookup_codes        plc
WHERE    poh.vendor_id          = pov.vendor_id  (+)
AND      poh.agent_id           = hre.employee_id
AND      pvs.vendor_site_id  (+)   = poh.vendor_site_id
AND      poh.po_header_id       = pol.po_header_id
AND      pol.po_line_id         = pll.po_line_id(+)
AND      pll.po_release_id      = por.po_release_id(+)
AND      nvl(pll.shipment_type, 'BLANKET') in ('BLANKET', 'SCHEDULED')
AND      poh.type_lookup_code   in ('BLANKET','PLANNED')
AND      plc.lookup_type        = 'DOCUMENT STATE'
AND      plc.lookup_code        = nvl(poh.authorization_status, 'INCOMPLETE')
AND      pdt.document_type_code in  ('PA', 'PO')
AND      pdt.document_subtype  = poh.type_lookup_code
AND      (:P_buyer              is NULL
         OR :P_buyer            = hre.employee_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)
&P_Where_Query
AND EXISTS      (SELECT 'Category Flexfield Range'
                 FROM   mtl_categories      mca
                 WHERE  pol.category_id   = mca.category_id(+)
                 AND    &P_WHERE_CAT)
AND ( ((pol.item_id  IS NULL ) AND
 ( (:P_item_from IS NULL AND :P_item_to IS NULL)
 OR (:P_item_from IS NULL AND :P_item_to IS NOT NULL)) )
          OR
          EXISTS      (SELECT 'Item Flexfield Range'
                 FROM   mtl_system_items msi
                 WHERE  pol.item_id  = msi.inventory_item_id
	AND    nvl(msi.organization_id, :organization_id) = :organization_id
                 AND    nvl(pll.shipment_type, 'BLANKET') in ('BLANKET','SCHEDULED')
                  AND    &P_WHERE_ITEM))
GROUP BY pov.vendor_name
,        poh.segment1
,        por.creation_date
,        plc.displayed_field
,        por.revision_num
,        por.printed_date
,        pdt.type_name
,        hre.full_name
,        por.revised_date
,        poh.currency_code
,        por.acceptance_due_date
,        pvs.vendor_site_code
,        decode(poh.user_hold_flag,'Y',:yes,'N',:no,null)
,        decode(por.acceptance_required_flag, 'Y',:yes, 'N',:no,null)
,        poh.po_header_id
,        poh.blanket_total_amount
,        poh.amount_limit
,        por.release_num
,        por.release_date
,        pll.po_release_id
,        decode(psp1.manual_po_num_type,'NUMERIC',
                null,poh.segment1)
,        decode(psp1.manual_po_num_type,'NUMERIC',
                to_number(poh.segment1),null)
--ORDER BY &orderby_clause
Order by 22,23,1,2,3,4,5,6,7,9,10,11,13,12,14,18,15,19,8,16,17,20,21
Parameter Name SQL text Validation
P_STRUCT_NUM
 
Number
To
 
LOV Oracle
PO Numbers From
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
Items To
 
Items From
 
To
 
Categories From
 
Buyer Name
 
LOV Oracle
Title