PO Purchase Order Commitment By Period

SELECT  DECODE(:P_SORT, 'VENDOR', pov.vendor_name, 'BUYER', hre.full_name, pov.vendor_name)
		break_group_column
,               DECODE(:P_SORT, 'VENDOR', hre.full_name, 'BUYER', pov.vendor_name,hre.full_name )
		other_group_column,
poh.segment1||decode(por.release_num,null,'','-'||por.release_num)  PO_Number,
decode( mod(glp.period_num,12), mod(:P_period_num,12),                       
             decode(poh.closed_code,
                         'FINALLY CLOSED',  
                         SUM (DECODE (POL.order_type_lookup_code,
                                                  'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  'FIXED PRICE',  GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
                         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.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1)
            ) 
        C_AMOUNT_PER1,
decode( mod(glp.period_num,12), mod(:P_period_num + 1,12),                         
             decode(poh.closed_code, 
                         'FINALLY CLOSED',  
                         SUM (DECODE (POL.order_type_lookup_code,
                                                  'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  'FIXED PRICE',  GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
                         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.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1)
              )          
         C_AMOUNT_PER2,
decode( mod(glp.period_num,12), mod(:P_period_num + 2,12),                         
             decode(poh.closed_code, 
                         'FINALLY CLOSED',  
                         SUM (DECODE (POL.order_type_lookup_code,
                                                  'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  'FIXED PRICE',  GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
                         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.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1)
              )
         C_AMOUNT_PER3,
 decode( mod(glp.period_num,12), mod(:P_period_num + 3,12),                                    
                     decode(poh.closed_code, 
                         'FINALLY CLOSED', 
                         SUM (DECODE (POL.order_type_lookup_code,
                                                  'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  'FIXED PRICE',  GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
                         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.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1),
                mod(:P_period_num + 4,12),  
                     decode(poh.closed_code, 
                         'FINALLY CLOSED', 
                         SUM (DECODE (POL.order_type_lookup_code,
                                                  'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  'FIXED PRICE',  GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
                         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.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1),
                mod(:P_period_num + 5,12),  
                     decode(poh.closed_code, 
                        'FINALLY CLOSED',
                        SUM (DECODE (POL.order_type_lookup_code,
                                                  'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  'FIXED PRICE',  GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
                         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.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1)
                )
          C_AMOUNT_PER4,
mca.segment1  seg1, 
	PO_POXPOCOM_XMLP_PKG.c_sum_allformula(:C_break_per1, :C_break_per2, :C_break_per3, :C_break_per4, :C_PRECISION) C_sum_all, 
	PO_POXPOCOM_XMLP_PKG.c_break_per1_round(:C_break_per1, :C_PRECISION) C_break_per1_round, 
	PO_POXPOCOM_XMLP_PKG.c_break_per2_round(:C_break_per2, :C_PRECISION) C_break_per2_round, 
	PO_POXPOCOM_XMLP_PKG.c_break_per3_round(:C_break_per3, :C_PRECISION) C_break_per3_round, 
	PO_POXPOCOM_XMLP_PKG.c_break_per4_round(:C_break_per4, :C_PRECISION) C_break_per4_round, 
	PO_POXPOCOM_XMLP_PKG.c_amt_per1_round(decode ( mod ( glp.period_num , 12 ) , mod ( :P_period_num , 12 ) , decode ( poh.closed_code , 'FINALLY CLOSED' , SUM ( DECODE ( POL.order_type_lookup_code , 'RATE' , GREATEST ( PLL.amount_received , PLL.amount_billed ) , 'FIXED PRICE' , GREATEST ( PLL.amount_received , PLL.amount_billed ) , GREATEST ( PLL.quantity_received , PLL.quantity_billed ) * PLL.price_override ) ) , 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.quantity - NVL ( PLL.quantity_cancelled , 0 ) ) * PLL.price_override ) ) ) * NVL ( POH.rate , 1 ) ), :C_PRECISION) C_amount_per1_round, 
	PO_POXPOCOM_XMLP_PKG.c_amt_per2_round(decode ( mod ( glp.period_num , 12 ) , mod ( :P_period_num + 1 , 12 ) , decode ( poh.closed_code , 'FINALLY CLOSED' , SUM ( DECODE ( POL.order_type_lookup_code , 'RATE' , GREATEST ( PLL.amount_received , PLL.amount_billed ) , 'FIXED PRICE' , GREATEST ( PLL.amount_received , PLL.amount_billed ) , GREATEST ( PLL.quantity_received , PLL.quantity_billed ) * PLL.price_override ) ) , 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.quantity - NVL ( PLL.quantity_cancelled , 0 ) ) * PLL.price_override ) ) ) * NVL ( POH.rate , 1 ) ), :C_PRECISION) C_amount_per2_round, 
	PO_POXPOCOM_XMLP_PKG.c_amt_per3_round(decode ( mod ( glp.period_num , 12 ) , mod ( :P_period_num + 2 , 12 ) , decode ( poh.closed_code , 'FINALLY CLOSED' , SUM ( DECODE ( POL.order_type_lookup_code , 'RATE' , GREATEST ( PLL.amount_received , PLL.amount_billed ) , 'FIXED PRICE' , GREATEST ( PLL.amount_received , PLL.amount_billed ) , GREATEST ( PLL.quantity_received , PLL.quantity_billed ) * PLL.price_override ) ) , 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.quantity - NVL ( PLL.quantity_cancelled , 0 ) ) * PLL.price_override ) ) ) * NVL ( POH.rate , 1 ) ), :C_PRECISION) C_amount_per3_round, 
	PO_POXPOCOM_XMLP_PKG.c_amt_per4_round( decode( mod(glp.period_num,12), mod(:P_period_num + 3,12),                                    
                     decode(poh.closed_code, 
                         'FINALLY CLOSED', 
                         SUM (DECODE (POL.order_type_lookup_code,
                                                  'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  'FIXED PRICE',  GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
                         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.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1),
                mod(:P_period_num + 4,12),  
                     decode(poh.closed_code, 
                         'FINALLY CLOSED', 
                         SUM (DECODE (POL.order_type_lookup_code,
                                                  'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  'FIXED PRICE',  GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
                         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.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1),
                mod(:P_period_num + 5,12),  
                     decode(poh.closed_code, 
                        'FINALLY CLOSED',
                        SUM (DECODE (POL.order_type_lookup_code,
                                                  'RATE', GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  'FIXED PRICE',  GREATEST (PLL.amount_received, PLL.amount_billed),
                                                  GREATEST (PLL.quantity_received, PLL.quantity_billed) * PLL.price_override)),
                         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.quantity - NVL(PLL.quantity_cancelled, 0)) * PLL.price_override))) * NVL(POH.rate, 1)
                ),:C_PRECISION)
  C_amount_per4_round
FROM   po_line_locations pll
,      po_lines             pol
,      po_releases       por
,      po_headers        poh
,      po_vendors        pov
,      hr_employees      hre
,      gl_period_statuses        glp
,      financials_system_parameters fsp
,      po_system_parameters         psp
,      mtl_categories mca
WHERE  poh.po_header_id      = pll.po_header_id
AND    por.po_release_id (+) = pll.po_release_id
AND    pll.po_line_id           =  pol.po_line_id
AND    pol.category_id       =  mca.category_id
AND    poh.vendor_id         = pov.vendor_id
AND    hre.employee_id       = poh.agent_id
AND    nvl(nvl(pll.promised_date,pll.need_by_date),sysdate) 
            BETWEEN  nvl(glp.start_date, nvl(nvl(pll.promised_date,pll.need_by_date),sysdate) - 1 )
            AND      nvl(glp.end_date, nvl(nvl(pll.promised_date,pll.need_by_date),sysdate) + 1) 
AND    application_id = 201
AND    glp.set_of_books_id = fsp.set_of_books_id
AND    (( (glp.period_num   
                 BETWEEN  :P_period_num  AND decode ( sign (((:P_period_num +5)/12)-1), -1, :P_period_num+5, 12))  
                 AND  glp.period_year = :P_period_year)
              OR
               ((glp.period_num
                  BETWEEN  1 AND decode ( sign (((:P_period_num+5)/12)-1), -1,0, mod(:P_period_num+5,12)))
                  AND glp.period_year = :P_period_year + 1))
AND    pll.shipment_type in ('STANDARD', 'SCHEDULED', 'BLANKET')
AND    pov.vendor_name 
       BETWEEN  nvl(:P_vendor_from,pov.vendor_name) AND nvl(:P_vendor_to, pov.vendor_name)
AND  ((decode(psp.manual_po_num_type,'NUMERIC',poh.segment1,NULL)
       BETWEEN decode(psp.manual_po_num_type,'NUMERIC',
                       to_number(nvl(:P_po_number_from, poh.segment1)),NULL)
         AND     decode(psp.manual_po_num_type,'NUMERIC',  
                       to_number(nvl(:P_po_number_to, poh.segment1)),NULL))
OR       (poh.segment1
         BETWEEN decode(psp.manual_po_num_type,'ALPHANUMERIC',
                       nvl(:P_po_number_from,poh.segment1),NULL) 
         AND     decode(psp.manual_po_num_type,'ALPHANUMERIC',
                       nvl(:P_po_number_to,poh.segment1),NULL)))
AND    hre.employee_id = nvl(:P_buyer, hre.employee_id)
AND ( ( NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED' 
        AND 
        ( DECODE  (POL.order_type_lookup_code,
                   'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
                   'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
                   PLL.quantity - NVL(PLL.quantity_cancelled,0)) > 0)
      )
      OR
      ( POH.closed_code = 'FINALLY CLOSED'
        AND
        ( DECODE  (POL.order_type_lookup_code,
                   'RATE', NVL(GREATEST (PLL.amount_received, PLL.amount_billed), 0),
                   'FIXED PRICE', NVL(GREATEST (PLL.amount_received, PLL.amount_billed), 0),
                   NVL(GREATEST (PLL.quantity_received,  PLL.quantity_billed), 0)) > 0)
      )
    )       
AND ( DECODE (POL.order_type_lookup_code,
              'RATE',
              PLL.amount - NVL(PLL.amount_cancelled, 0) -
                NVL( GREATEST (PLL.amount_received, PLL.amount_billed), 0),
              'FIXED PRICE',
              PLL.amount - NVL(PLL.amount_cancelled, 0) -
                NVL( GREATEST (PLL.amount_received, PLL.amount_billed), 0),
              PLL.quantity - NVL(PLL.quantity_cancelled, 0) -
                NVL( GREATEST (PLL.quantity_received, PLL.quantity_billed), 0)) > 0)
AND    &P_WHERE_CAT
GROUP BY 
         pov.vendor_name
,        hre.full_name
,        poh.segment1||decode(por.release_num,null,'','-'||por.release_num) 
,        glp.period_year,  glp.period_num
,        glp.period_year,  glp.period_num
,        glp.period_year,  glp.period_num
,        glp.period_year, glp.period_num
,        decode(psp.manual_po_num_type,'NUMERIC',
                       to_number(poh.segment1),NULL),
                       decode(psp.manual_po_num_type,'NUMERIC',
                                     NULL,poh.segment1), 
	  por.release_num ,
 mca.segment1 ,
 poh.closed_code,
NVL(poh.rate, 1)
ORDER BY 1 ASC,2 ASC , decode ( psp.manual_po_num_type , 'NUMERIC' , to_number ( poh.segment1 ) , NULL ) , decode ( psp.manual_po_num_type , 'NUMERIC' , NULL , poh.segment1 ) , por.release_num