PO Encumbrance Detail

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Encumbrance Detail Report (XML)
Short Name: POXPOEDR_XML
DB package: PO_POXPOEDR_XMLP_PKG
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
Parameter Name SQL text Validation
P_CHART_OF_ACCOUNTS
 
Number
Dynamic Precision Option
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
Document Type
 
LOV Oracle
To
 
Date
Encumbrance Dates From
 
Date
To
 
Cost Centers From
 
To
 
Accounts From
 
Title