PO Encumbrance Detail

SELECT    (poh.segment1)               PO_NUM
,         (poh.segment1 || DECODE(por.release_num, NULL, '', ' - ' || por.release_num)) PO_REL_Disp
,         (plkc.displayed_field)       Source
,         (pod.gl_encumbered_date)     Reserved_Date
,         (pol.line_num)               Line
,         (pll.shipment_num)               Shipment
,         (pod.distribution_num)               Distribution
,          pod.po_distribution_id               Parent_join_id
,         pol.item_description      Item_Description
,         nvl(pod.quantity_ordered, 0)	quantity_ordered
,         nvl(pod.quantity_cancelled, 0)	quantity_cancelled
,         nvl(pod.quantity_billed, 0)		quantity_billed
,         nvl(pod.quantity_delivered,0) 	quantity_delivered
,         nvl(pll.price_override,0) 		price_override
,         nvl(pod.rate, 1)			rate
,         pod.po_release_id		po_release_id
,         pod.po_header_id		po_header_id
,         pod.po_line_id			po_line_id
,         pod.line_location_id		po_line_location_id
,         pod.gl_encumbered_date		gl_encumbered_date
,         pod.encumbered_amount                       po_enc_amount_func
,        pll.shipment_type	                  po_type
,         (pol.unit_meas_lookup_code)  Unit
,        poh.currency_code                Currency
,        pod.budget_account_id      ccid
,         (pll.price_override)         Unit_Price
,         (poh.currency_code)          C_CURRENCY_PO
,         pod.accrue_on_receipt_flag   accrual_flag
,         pod.accrual_account_id   accrual_ccid
,         pod.destination_type_code dest  
,        nvl(pll.cancel_flag,'N')  cancel 
,        nvl(pod.nonrecoverable_tax,0)  nonrecov_tax, 
	PO_POXPOEDR_XMLP_PKG.adjusted_q_orderedformula(pll.shipment_type, pod.po_header_id, pod.po_line_id, pod.po_distribution_id, nvl ( pod.quantity_ordered , 0 )) Adjusted_q_ordered, 
	PO_POXPOEDR_XMLP_PKG.c_amount_base_poformula(pll.shipment_type, pod.encumbered_amount, pod.accrue_on_receipt_flag, pod.po_distribution_id) C_AMOUNT_BASE_PO, 
	PO_POXPOEDR_XMLP_PKG.c_amount_func_poformula(:C_AMOUNT_BASE_PO, :C_CURRENCY_BASE1, poh.currency_code, nvl ( pod.rate , 1 ), :C_PRECISION) C_AMOUNT_FUNC_PO,
	PO_POXPOEDR_XMLP_PKG.CP_PRECISION_PO_p CP_PRECISION_PO,
	PO_POXPOEDR_XMLP_PKG.CP_OLD_PO_CUR_p CP_OLD_PO_CUR
FROM     po_distributions_all         pod
,        po_releases_all                    por
,        po_lookup_codes                  plkc
,        po_line_locations_all             pll
,        po_lines_all                          pol
,        po_headers_all                     poh
,        po_vendors                          pov
WHERE pod.po_release_id         = por.po_release_id (+)
AND      poh.po_header_id          = pod.po_header_id
AND      pod.po_line_id            = pol.po_line_id
AND      pod.line_location_id      = pll.line_location_id
AND      ( nvl(pod.encumbered_flag , 'N' )      = 'Y'
                      OR  (nvl(pod.encumbered_flag, 'N') = 'N' and nvl(pll.cancel_flag, 'N') = 'Y')   )
AND      pod.gl_encumbered_date    
            BETWEEN nvl(:P_encumbrance_date_from, pod.gl_encumbered_date-1)
            AND     nvl(:P_encumbrance_date_to, pod.gl_encumbered_date+1)
AND     (nvl(:P_ACTIVE_ONLY, 'N') = 'N'
 	OR  (nvl(:P_ACTIVE_ONLY, 'N') = 'Y' and poh.closed_date is null))
AND      pov.vendor_id(+) = poh.vendor_id
AND       nvl(pov.vendor_name,'A') between
                                        nvl(:P_Vendor_From,nvl(pov.vendor_name,'A') )  
                                   and nvl(:P_Vendor_To,nvl(pov.vendor_name,'A') ) 
AND      nvl(:P_TYPE,'PO') != 'REQUISITION'
AND      plkc.lookup_type = 'PO HEADER'
AND       plkc.lookup_code = 'VENDOR'
AND       nvl(pod.prevent_encumbrance_flag,'N') = 'N'
and         nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
and         nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
and        pod.budget_account_id = :GL_Code_Id
and        pod.org_id=po_moac_utils_pvt.get_current_org_id 
 and pod.budget_account_id=:GL_Code_Id
ORDER BY poh.currency_code, poh.segment1, pol.line_num, pll.shipment_num, pod.distribution_num