PO Purchase Order Distribution Detail

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