PO Encumbrance Detail
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Encumbrance Detail Report
Application: Purchasing
Source: Encumbrance Detail Report (XML)
Short Name: POXPOEDR_XML
DB package: PO_POXPOEDR_XMLP_PKG
Description: Encumbrance Detail Report
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_PRECISION) 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 | |
---|---|---|---|
Operating Unit |
|
LOV | |
Title |
|
Char | |
Accounts From |
|
Char | |
To |
|
Char | |
Cost Centers From |
|
Char | |
To 2 |
|
Char | |
Encumbrance Dates From |
|
Date | |
To 3 |
|
Date | |
Document Type |
|
LOV Oracle | |
Vendors From |
|
LOV Oracle | |
To 4 |
|
LOV Oracle | |
Dynamic Precision Option |
|
LOV Oracle |