PO Purchase Order Commitment By Period
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
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(: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 |