PO ReqExpress Templates Listing

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: ReqExpress Template Listing
Application: Purchasing
Source: ReqExpress Templates Listing (XML)
Short Name: POXRQEXP_XML
DB package: PO_POXRQEXP_XMLP_PKG
Run PO ReqExpress Templates Listing and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT distinct prxl.sequence_num                         Sequence
,      null                                       C_FLEX_ITEM
,      null                                        C_FLEX_CAT
,      prxl.item_revision                                 Rev
,      prxl.unit_meas_lookup_code                         Unit
,      prxl.unit_price                                    Unit_Price
,      decode(prxl.source_type_code,
              'INVENTORY',(decode(nvl(ood.organization_name,'A')
                                ,'A',ood.organization_name
                                ,ood.organization_name||', ')||
                          decode(nvl(prxl.source_subinventory,'')
                                ,'',prxl.source_subinventory
                                ,prxl.source_subinventory))
             ,'VENDOR'   ,(decode(nvl(pov.vendor_name,'')
                                ,'',pov.vendor_name
                                ,pov.vendor_name||', '||
                          decode(nvl(pvs.vendor_site_code,'')
                                ,'',pvs.vendor_site_code
                                ,pvs.vendor_site_code||', '||
                          decode(nvl(pvc.first_name,'')
                                ,'',pvc.first_name
                                ,pvc.first_name||', '||
                          decode(nvl(papf.first_name,'')
                                ,'',papf.first_name
                               ,papf.first_name))))))    Source
,      prxl.express_name                                  Express_Name
,      t.type_name                                     Requisition_Type
,      polc.displayed_field                               Source_Type
,      prxl.item_description                               Item_Description, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_DISP, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP
FROM   mtl_system_items                  msi
,      mtl_categories                    mca
,      po_vendors                        pov
,      po_vendor_sites                   pvs
,      po_vendor_contacts                pvc
,      PER_ALL_PEOPLE_F      PAPF
,      po_reqexpress_lines               prxl
,      po_reqexpress_headers             prxh
,      PO_DOCUMENT_TYPES_ALL_TL T
,      PO_DOCUMENT_TYPES_ALL_B B
,      po_lookup_codes                   polc
,      org_organization_definitions      ood
WHERE  prxl.item_id                      = msi.inventory_item_id (+)
AND    prxl.suggested_vendor_id          = pov.vendor_id         (+)
AND    prxl.suggested_vendor_site_id     = pvs.vendor_site_id    (+)
AND    prxl.suggested_vendor_contact_id  = pvc.vendor_contact_id (+)
AND    prxl.suggested_buyer_id           = papf.person_id       (+)
AND    prxl.category_id                  = mca.category_id
AND    prxl.express_name                 = prxh.express_name 
AND    B.document_type_code           = 'REQUISITION'
AND    B.document_subtype             = prxh.type_lookup_code
AND    polc.lookup_type                  = 'REQUISITION SOURCE TYPE'
AND    polc.lookup_code                  = prxl.source_type_code   
AND    prxl.express_name                 = decode(:P_EXPRESS_NAME,'',
                                                   prxl.express_name,
                                                   :P_EXPRESS_NAME)
AND    prxl.source_organization_id       = ood.organization_id (+)
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE (+) AND
PAPF.EFFECTIVE_END_DATE (+)
AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F',PAPF.PERSON_ID (+),
PAPF.PERSON_TYPE_ID (+),
PAPF.EMPLOYEE_NUMBER (+),PAPF.APPLICANT_NUMBER (+) 
,PAPF.NPW_NUMBER(+) )) = 'TRUE'               
AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF.BUSINESS_GROUP_ID (+),
HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF.BUSINESS_GROUP_ID (+)
and   B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE
AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
AND B.ORG_ID = T.ORG_ID  
AND B.ORG_ID = prxh.ORG_ID  
AND T.LANGUAGE = USERENV('LANG') 
 and prxl.express_name=:Express_Name_header
ORDER BY prxl.express_name
,        fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')
,        fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')    
,        prxl.sequence_num
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
Title 
 
 Char
Express Name 
 
 LOV Oracle
Download
Related Blitz Reports
ECC Project Manufacturing, Procurement Activities, SQL3 ECC Procurement, Agreements ECC Procurement, Purchase Orders PO Blanket and Planned PO Status PO Receipt Adjustments PO Receiving Exceptions PO Overshipments PO Headers and Lines 11i PO Receiving Transactions Pipeline
Blitz Report™

Blitz Report™ provides multiple benefits: