PO Open Purchase Orders Report(by Cost Center)

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Open Purchase Orders Report(by Cost Center) (XML)
Short Name: POXPOPAR_XML
DB package: PO_POXPOPAR_XMLP_PKG
SELECT   pll.shipment_num||'-'||pod.distribution_num  Distribution
,        decode(plt.order_type_lookup_code,
             'AMOUNT',NULL,pll.price_override)                    Unit_Price
,        pll.promised_date                     Promised_Date
,        pol.unit_meas_lookup_code             Unit
,        ROUND (DECODE (POL.order_type_lookup_code,                         
                                       'RATE', POD.amount_ordered,
                                       'FIXED PRICE', POD.amount_ordered,
                                       POD.quantity_ordered),
                       :p_qty_precision) Quantity_Amount_Ordered
,        ROUND (DECODE (POL.order_type_lookup_code,                        
                                       'RATE', POD.amount_billed,
                                       'FIXED PRICE', POD.amount_billed,
                                       POD.quantity_billed),
                       :p_qty_precision) Quantity_Amount_Billed
,        ROUND (DECODE (POL.order_type_lookup_code,                        
                                       'RATE', POD.amount_delivered,
                                       'FIXED PRICE', POD.amount_delivered,
                                       POD.quantity_delivered),
                       :p_qty_precision) Quantity_Amount_Delivered
,        DECODE (POL.order_type_lookup_code,                        
                        'RATE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) / 
                                       DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
                        'FIXED PRICE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, 0)) / 
                                       DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
                        (NVL(POD.quantity_ordered, 0) - NVL(POD.quantity_billed, 0)) / 
                                       DECODE (NVL(POD.quantity_ordered, 0), 0, 1, POD.quantity_ordered)) * 100 Percent_Unbilled
,        DECODE (POL.order_type_lookup_code,                        
                        'RATE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
                        'FIXED PRICE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0),
                        (POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)- NVL(POD.quantity_billed, 0)) * PLL.price_override) C_AMOUNT_OPEN_INV  
,        plc.displayed_field                   Open_For
,        pll.po_line_id							po_line_id2
,       nvl(pll.po_release_id,-1)  release_id
,        &company_name_lex                         Company_name_Q4
,        &cost_center_lex                          Cost_Center_Q4
FROM     po_line_locations                     pll
,        po_distributions                      pod
,        po_lines                              pol
,        po_lookup_codes                       plc
,        po_line_types                         plt
,       gl_code_combinations        glcc
WHERE    pod.line_location_id                  = pll.line_location_id
AND      pol.po_line_id                        = pod.po_line_id
AND      pol.line_type_id                   = plt.line_type_id
AND      nvl(pll.closed_code,'OPEN')  not in ('CLOSED', 'FINALLY CLOSED')
AND      nvl(pol.closed_code,'OPEN')  not in ('CLOSED', 'FINALLY CLOSED')
AND      pll.shipment_type            in ('STANDARD','BLANKET','SCHEDULED')
AND      nvl(pll.closed_code,'OPEN')           = plc.lookup_code
AND      plc.lookup_type                       = 'DOCUMENT STATE'
AND      nvl(pll.cancel_flag,'N')           = 'N'
AND      nvl(pol.cancel_flag,'N')           = 'N'
AND      &company_name_lex                     = :company_name_q3
AND      &cost_center_lex                           =  :cost_center_q3
AND      glcc.code_combination_id              = pod.code_combination_id 
 and pll.po_line_id=:po_line_id 
 and nvl ( pll.po_release_id , - 1 )=:release_id1
ORDER BY pll.shipment_num, pod.distribution_num
Parameter Name SQL text Validation
Sort By
 
LOV Oracle
Dynamic Precision Option
 
LOV Oracle
To
 
LOV Oracle
Cost Centers From
 
LOV Oracle
To
 
LOV Oracle
Companies From
 
LOV Oracle
Title