PO Purchasing Activity Register

➜ Back to Library
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
Run PO Purchasing Activity Register and other Oracle EBS reports with Blitz Report™ on our demo environment
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