PO Purchasing Activity Register

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Purchasing Activity Register
Application: Purchasing
Source: Purchasing Activity Register (XML)
Short Name: POXACTPO_XML
DB package: PO_POXACTPO_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Parameter Name SQL text Validation
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