PO Purchase Order Distribution Detail

Description
Categories: BI Publisher
Application: Purchasing
Source: Purchase Order Distribution Detail Report (XML)
Short Name: POXPODDR_XML
DB package: PO_POXPODDR_XMLP_PKG
select Q1.*,
PO_POXPODDR_XMLP_PKG.C_amount_cur_round_p(Q1.C_dist_amt_cur,:p_qty_precision) C_amount_cur_round,
PO_POXPODDR_XMLP_PKG.C_amount_fun_round_p(Q1.C_dist_amt_func,:p_qty_precision) C_amount_fun_round
from
(SELECT   pol.line_num||'-'||pll.shipment_num                        	Line_Ship
,                por.release_num                                		Release
,               null                                   		C_FLEX_ITEM
,               pol.item_revision                              		Rev
,               pol.item_description                           		Description
,               pol.unit_meas_lookup_code                      		Unit
,               pol.order_type_lookup_code                                       ORDER_TYPE_LOOKUP_CODE 
,               round((pll.quantity - nvl(pll.quantity_cancelled, 0)),:p_qty_precision)       ship_qty_ordered
,               round((pll.quantity - nvl(pll.quantity_cancelled, 0)),:p_qty_precision)       C_Quantity_Ordered
,               pll.price_override                                 		Unit_Price
,              null                                    		C_FLEX_ACC
,              to_char(pod.gl_encumbered_date,'DD-MON-YY')                         		Reserved_Date
,              pod.quantity_ordered - nvl(pod.quantity_cancelled, 0) dist_quantity_ordered
,              pll.line_location_id
,              pll.source_shipment_id
,              pll.shipment_type
,              pod.po_distribution_id
,              pod.rate					rate
,              pod.source_distribution_id
,              POD.amount_ordered - NVL(POD.amount_cancelled, 0) DIST_AMOUNT_ORDERED 
&select_failed_f, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_DISP, 
	PO_POXPODDR_XMLP_PKG.get_ship_quantity(pll.shipment_type, round ( ( pll.quantity - nvl ( pll.quantity_cancelled , 0 ) ) , :p_qty_precision ), :C_ship_rls_qty) C_ship_quantity, 
	PO_POXPODDR_XMLP_PKG.get_dist_cur_amount(pll.shipment_type, pod.quantity_ordered - nvl ( pod.quantity_cancelled , 0 ), :C_dist_rls_qty, pll.price_override, pol.order_type_lookup_code, POD.amount_ordered - NVL ( POD.amount_cancelled , 0 )) C_dist_amt_cur, 
	PO_POXPODDR_XMLP_PKG.get_dist_func_amount(pll.shipment_type, pod.quantity_ordered - nvl ( pod.quantity_cancelled , 0 ), :C_dist_rls_qty, pll.price_override, pod.rate, pol.order_type_lookup_code, POD.amount_ordered - NVL ( POD.amount_cancelled , 0 )) C_dist_amt_func, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_sort', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_FLEX_ACC_SORT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_acc_disp', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ACC_DISP
FROM     po_lines                       pol
,               po_releases                 por
,               mtl_system_items        msi
,               po_line_locations        pll
,               &lp_po_distributions          pod      
&from_failed_f
WHERE    pod.po_release_id                     = por.po_release_id (+)
AND         pol.item_id                                  = msi.inventory_item_id (+)
AND         nvl(msi.organization_id, :c_organization_id) = :c_organization_id 
AND         pol.po_line_id                            = pll.po_line_id
AND         pll.line_location_id                    = pod.line_location_id
AND         pll.shipment_type                      in ('BLANKET','STANDARD','PLANNED','SCHEDULED')
AND         DECODE (POL.order_type_lookup_code,
                               'RATE',
                               POD.amount_ordered - NVL(POD.amount_cancelled, 0),
                               'FIXED PRICE',
                               POD.amount_ordered - NVL(POD.amount_cancelled, 0),
                               POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)) > 0  
&where_failed_f 
 and pol.po_header_id = :po_header_id1
ORDER BY 2 ASC,4 ASC,5 ASC,6 ASC,10 ASC,7 ASC,21 ASC,3 ASC,8 ASC,1 ASC,14 ASC,15 ASC,19 ASC,16 ASC,18 ASC,9 ASC , pol.line_num
,                   &P_FLEX_ACC) Q1
Parameter Name SQL text Validation
Ledger Currency
 
LOV Oracle
Title
 
PO Numbers From
 
LOV Oracle
To
 
LOV Oracle
Creation Dates From
 
Date
To
 
Date
Vendors From
 
LOV Oracle
To
 
LOV Oracle
Buyer Name
 
LOV Oracle
Failed Funds Only
 
LOV Oracle
Sort By
 
LOV Oracle