PO Purchasing Activity Register
Description
- Categories
- BI Publisher
- Application
- Purchasing
- Source
- Purchasing Activity Register (XML)
- Short Name
- POXACTPO_XML
- DB package
- PO_POXACTPO_XMLP_PKG
Imported from BI Publisher
Purchasing Activity Register
Purchasing Activity Register
SQL 176 lines
select trunc(poh.creation_date) Creation_date , pdt.type_name PO_TYPE , poh.type_lookup_code PO_TYPE_CODE , poh.segment1 PO_NUMBER , ppf.full_name BUYER , POV.vendor_name VENDOR , poh.currency_code PO_CURRENCY , NVL(SUM(DECODE(POH.type_lookup_code, 'STANDARD', DECODE ( NVL (POLL.consigned_flag, 'N'), 'N', DECODE (POL.order_type_lookup_code, 'RATE', POLL.amount - NVL(POLL.amount_cancelled, 0), 'FIXED PRICE', POLL.amount - NVL(POLL.amount_cancelled, 0), (POLL.quantity - NVL(POLL.quantity_cancelled, 0)) * POLL.price_override), 0), 0)), 0) C_AMOUNT , NVL(SUM(DECODE(POH.type_lookup_code, 'STANDARD', DECODE ( NVL (POLL.consigned_flag, 'N'), 'N', DECODE (POL.order_type_lookup_code, 'RATE', POLL.amount - NVL(POLL.amount_cancelled, 0), 'FIXED PRICE', POLL.amount - NVL(POLL.amount_cancelled, 0), (POLL.quantity - NVL(POLL.quantity_cancelled, 0)) * POLL.price_override) * NVL(POH.rate, 1), 0), 0)), 0) C_BASE_AMOUNT , poh.po_header_id PO_HEAD_ID , decode(psp.manual_po_num_type, 'NUMERIC', to_number(poh.segment1), null) , decode(psp.manual_po_num_type, 'NUMERIC', null, poh.segment1) , to_number(NULL), PO_POXACTPO_XMLP_PKG.round_amount(NVL ( SUM ( DECODE ( POH.type_lookup_code , 'STANDARD' , DECODE ( NVL ( POLL.consigned_flag , 'N' ) , 'N' , DECODE ( POL.order_type_lookup_code , 'RATE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , 'FIXED PRICE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , ( POLL.quantity - NVL ( POLL.quantity_cancelled , 0 ) ) * POLL.price_override ) , 0 ) , 0 ) ) , 0 ), :C_po_currency_precision) C_amount_round, PO_POXACTPO_XMLP_PKG.base_amount_round(NVL ( SUM ( DECODE ( POH.type_lookup_code , 'STANDARD' , DECODE ( NVL ( POLL.consigned_flag , 'N' ) , 'N' , DECODE ( POL.order_type_lookup_code , 'RATE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , 'FIXED PRICE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , ( POLL.quantity - NVL ( POLL.quantity_cancelled , 0 ) ) * POLL.price_override ) * NVL ( POH.rate , 1 ) , 0 ) , 0 ) ) , 0 ), :C_PRECISION) C_base_amount_round from &lp_po_headers poh , po_line_locations poll , po_lines POL , per_people_f ppf , po_vendors pov , po_system_parameters psp , po_document_types pdt where poh.type_lookup_code in ('STANDARD', 'PLANNED') and pdt.document_type_code in ('PO', 'PA') and pdt.document_subtype = poh.type_lookup_code and poh.type_lookup_code = nvl(:P_type, poh.type_lookup_code) &p_poh_creation_date_clause and poll.po_header_id = poh.po_header_id and POL.po_header_id = POH.po_header_id and POLL.po_line_id = POL.po_line_id and poll.shipment_type not in ('SCHEDULED') and pov.vendor_id = poh.vendor_id and pov.vendor_name = nvl(:P_vendor, pov.vendor_name) and ppf.person_id = poh.agent_id and ppf.full_name = nvl(:P_BUYER, ppf.full_name) and sysdate between nvl(ppf.effective_start_date,sysdate) and nvl(effective_end_date,sysdate) AND DECODE (POL.order_type_lookup_code, 'RATE', POLL.amount - NVL(POLL.amount_cancelled, 0), 'FIXED PRICE', POLL.amount - NVL(POLL.amount_cancelled, 0), POLL.quantity - NVL(POLL.quantity_cancelled, 0)) > 0 group by trunc(poh.creation_date) , pdt.type_name , poh.type_lookup_code , poh.segment1 , ppf.full_name , pov.vendor_name , poh.currency_code , poh.po_header_id , psp.manual_po_num_type UNION select trunc(por.creation_date) Creation_date , plc.displayed_field PO_TYPE , 'RELEASE' PO_TYPE_CODE , poh.segment1 || ' - ' || por.release_num PO_NUMBER , ppf.full_name BUYER , pov.vendor_name VENDOR , poh.currency_code PO_CURRENCY , NVL ( SUM ( DECODE (POL.order_type_lookup_code, 'RATE', POLL.amount - NVL(POLL.amount_cancelled, 0), 'FIXED PRICE', POLL.amount - NVL(POLL.amount_cancelled, 0), (POLL.quantity - NVL (POLL.quantity_cancelled, 0)) * POLL.price_override)), 0) C_AMOUNT , NVL ( SUM ( DECODE (POL.order_type_lookup_code, 'RATE', POLL.amount - NVL(POLL.amount_cancelled, 0), 'FIXED PRICE', POLL.amount - NVL(POLL.amount_cancelled, 0), (POLL.quantity - NVL (POLL.quantity_cancelled, 0)) * POLL.price_override) * NVL(POH.rate, 1)), 0) C_BASE_AMOUNT , poh.po_header_id PO_HEAD_ID , decode(psp.manual_po_num_type, 'NUMERIC', to_number(poh.segment1), null) , decode(psp.manual_po_num_type, 'NUMERIC', null, poh.segment1) , por.release_num, /*PO_POXACTPO_XMLP_PKG.round_amount(NVL ( SUM ( DECODE ( POH.type_lookup_code , 'STANDARD' , DECODE ( NVL ( POLL.consigned_flag , 'N' ) , 'N' , DECODE ( POL.order_type_lookup_code , 'RATE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , 'FIXED PRICE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , ( POLL.quantity - NVL ( POLL.quantity_cancelled , 0 ) ) * POLL.price_override ) , 0 ) , 0 ) ) , 0 ), :C_po_currency_precision) C_amount_round, */ PO_POXACTPO_XMLP_PKG.round_amount(NVL ( SUM ( DECODE (POL.order_type_lookup_code, 'RATE', POLL.amount - NVL(POLL.amount_cancelled, 0), 'FIXED PRICE', POLL.amount - NVL(POLL.amount_cancelled, 0), (POLL.quantity - NVL (POLL.quantity_cancelled, 0)) * POLL.price_override)), 0), :C_po_currency_precision) C_amount_round, /*PO_POXACTPO_XMLP_PKG.base_amount_round(NVL ( SUM ( DECODE ( POH.type_lookup_code , 'STANDARD' , DECODE ( NVL ( POLL.consigned_flag , 'N' ) , 'N' , DECODE ( POL.order_type_lookup_code , 'RATE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , 'FIXED PRICE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , ( POLL.quantity - NVL ( POLL.quantity_cancelled , 0 ) ) * POLL.price_override ) * NVL ( POH.rate , 1 ) , 0 ) , 0 ) ) , 0 ), :C_PRECISION) C_base_amount_round*/ PO_POXACTPO_XMLP_PKG.base_amount_round(NVL ( SUM ( DECODE (POL.order_type_lookup_code, 'RATE', POLL.amount - NVL(POLL.amount_cancelled, 0), 'FIXED PRICE', POLL.amount - NVL(POLL.amount_cancelled, 0), (POLL.quantity - NVL (POLL.quantity_cancelled, 0)) * POLL.price_override) * NVL(POH.rate, 1)), 0), :C_PRECISION) C_base_amount_round from po_releases por , &lp_po_headers poh , po_lines POL , po_line_locations poll , per_people_f ppf , po_vendors pov , po_system_parameters psp , po_lookup_codes plc where por.po_header_id = poh.po_header_id and poh.type_lookup_code = nvl(:P_type, poh.type_lookup_code) &p_por_creation_date_clause and poh.type_lookup_code in ('BLANKET', 'PLANNED') and poll.po_release_id = por.po_release_id and POL.po_header_id = POH.po_header_id and POL.po_line_id = POLL.po_line_id and poll.shipment_type in ('BLANKET', 'SCHEDULED') and ppf.person_id = por.agent_id and ppf.full_name = nvl(:P_BUYER, ppf.full_name) and sysdate between nvl(ppf.effective_start_date,sysdate) and nvl(ppf.effective_end_date,sysdate) and pov.vendor_id = poh.vendor_id and pov.vendor_name = nvl(:P_vendor, pov.vendor_name) and plc.lookup_code = 'RELEASE' and plc.lookup_type = 'DOCUMENT TYPE' AND DECODE (POL.order_type_lookup_code, 'RATE', POLL.amount - NVL(POLL.amount_cancelled, 0), 'FIXED PRICE', POLL.amount - NVL(POLL.amount_cancelled, 0), POLL.quantity - NVL(POLL.quantity_cancelled, 0)) > 0 group by trunc(por.creation_date) , plc.displayed_field , poh.segment1 , por.release_num , ppf.full_name , pov.vendor_name , poh.currency_code , poh.po_header_id , psp.manual_po_num_type UNION select trunc(poh.creation_date) Creation_date , pdt.type_name PO_TYPE , poh.type_lookup_code PO_TYPE_CODE , poh.segment1 PO_NUMBER , ppf.full_name BUYER , pov.vendor_name VENDOR , poh.currency_code PO_CURRENCY , 0 C_AMOUNT , 0 C_BASE_AMOUNT , poh.po_header_id PO_HEAD_ID , decode(psp.manual_po_num_type, 'NUMERIC', to_number(poh.segment1), null) , decode(psp.manual_po_num_type, 'NUMERIC', null, poh.segment1) , to_number(NULL), /*PO_POXACTPO_XMLP_PKG.round_amount(NVL ( SUM ( DECODE ( POH.type_lookup_code , 'STANDARD' , DECODE ( NVL ( POLL.consigned_flag , 'N' ) , 'N' , DECODE ( POL.order_type_lookup_code , 'RATE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , 'FIXED PRICE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , ( POLL.quantity - NVL ( POLL.quantity_cancelled , 0 ) ) * POLL.price_override ) , 0 ) , 0 ) ) , 0 ), :C_po_currency_precision) C_amount_round, PO_POXACTPO_XMLP_PKG.base_amount_round(NVL ( SUM ( DECODE ( POH.type_lookup_code , 'STANDARD' , DECODE ( NVL ( POLL.consigned_flag , 'N' ) , 'N' , DECODE ( POL.order_type_lookup_code , 'RATE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , 'FIXED PRICE' , POLL.amount - NVL ( POLL.amount_cancelled , 0 ) , ( POLL.quantity - NVL ( POLL.quantity_cancelled , 0 ) ) * POLL.price_override ) * NVL ( POH.rate , 1 ) , 0 ) , 0 ) ) , 0 ), :C_PRECISION) C_base_amount_round*/ PO_POXACTPO_XMLP_PKG.round_amount(0,:C_po_currency_precision) C_amount_round, PO_POXACTPO_XMLP_PKG.base_amount_round(0,:C_PRECISION) C_base_amount_round from &lp_po_headers poh , per_people_f ppf , po_vendors pov , po_system_parameters psp , po_document_types pdt where poh.type_lookup_code in ('BLANKET', 'CONTRACT') and pdt.document_type_code in ('PO', 'PA') AND pdt.document_subtype = poh.type_lookup_code and poh.type_lookup_code = nvl(:P_type, poh.type_lookup_code) &p_poh_creation_date_clause and poh.agent_id = ppf.person_id and ppf.full_name = nvl(:P_BUYER, ppf.full_name) and sysdate between nvl(ppf.effective_start_date,sysdate) and nvl(ppf.effective_end_date,sysdate) and pov.vendor_name = nvl(:P_vendor, pov.vendor_name) and poh.vendor_id = pov.vendor_id order by &orderby_clause, 10, 11 |
Parameters 10
| Parameter | Type | List of values SQL |
|---|---|---|
| Operating Unit | LOV | |
| Ledger Currency | LOV Oracle | |
| Title | Char | |
| Creation Dates From | Date | |
| To | Date | |
| PO Type | LOV Oracle | |
| Supplier | LOV Oracle | |
| Buyer Name | LOV Oracle | |
| Sort By | LOV Oracle | |
| Dynamic Precision Option | LOV Oracle |