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

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