PO Purchase Order Commitment By Period

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Purchase Order Commitment By Period Report
Application: Purchasing
Source: Purchase Order Commitment By Period Report (XML)
Short Name: POXPOCOM_XML
DB package: PO_POXPOCOM_XMLP_PKG
Run PO Purchase Order Commitment By Period and other Oracle EBS reports with Blitz Report™ on our demo environment
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(