PO Quotation Action Required

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Quotation Action Required Report (XML)
Short Name: POXQTQAR_XML
DB package: PO_POXQTQAR_XMLP_PKG
SELECT distinct papf.full_name                            Buyer
,      poh.segment1                             Quotation_Number
,      pov.vendor_name
,      pov.vendor_name||' ['||pov.segment1||']' Vendor_Name_Vendor_Number
,      pvs.vendor_site_code                     Vendor_Site
,      decode(pvc.last_name||', '||pvc.first_name,', ','',
        pvc.last_name||', '||pvc.first_name)    Vendor_Contact
,      pvc.phone                                Telephone_Number
,      poh.quote_vendor_quote_number            Vendor_Quotation_Number
,      poh.end_date                             Expiration_Date
,      nvl(poh.quote_warning_delay,0)           Warning_Delay
,      poh.comments                             Description
,       plc.displayed_field            Quotation_Type
,      poh.currency_code                        Currency
,      poh.po_header_id	Parent_po_header_id
FROM po_vendor_sites        pvs
,      po_vendor_contacts     pvc
,      per_all_people_f      papf
,       po_lookup_codes  plc
,      po_headers             poh
,      po_vendors             pov
WHERE  pov.vendor_id          = poh.vendor_id
AND    poh.vendor_site_id     = pvs.vendor_site_id(+)
AND    poh.vendor_contact_id  = pvc.vendor_contact_id(+)
AND    poh.type_lookup_code   = 'QUOTATION'
AND    poh.agent_id           = papf.person_id
AND   PAPF.BUSINESS_GROUP_ID = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID),0) 
FROM FINANCIALS_SYSTEM_PARAMETERS FSP) 
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    pov.vendor_name >= nvl(:P_vendor_from , pov.vendor_name )
AND    pov.vendor_name <= nvl(:P_vendor_to , pov.vendor_name)
AND    papf.full_name    =  nvl(:P_buyer,papf.full_name) 
AND    nvl(:P_date_from, nvl(poh.end_date,sysdate)) <= decode(:P_date_from,NULL,nvl(poh.end_date,sysdate),poh.end_date)
AND     nvl(:P_date_to, (nvl(poh.end_date,sysdate)-nvl(poh.quote_warning_delay,0)))  >= 
				decode(:P_date_to,NULL,nvl(poh.end_date,sysdate) -nvl(poh.quote_warning_delay,0)
						       , poh.end_date - nvl(poh.quote_warning_delay,0))
AND     plc.lookup_type (+) = 'QUOTATION CLASS'
AND      plc.lookup_code(+) = poh.quote_type_lookup_code   
AND EXISTS (select 'lines and shipments exist' 
            from   po_line_locations pll
            ,      po_lines          pol
           ,     mtl_categories               mca
            where  poh.po_header_id = pol.po_header_id
            and    pol.po_line_id   = pll.po_line_id
             AND nvl(:P_date_from,nvl(pll.end_date,nvl(poh.end_date,sysdate))) <=  decode(:P_date_from,NULL,nvl(pll.end_date,nvl(poh.end_date,sysdate)),
										nvl(pll.end_date,poh.end_date))
            and    nvl(:P_date_to,nvl(pll.end_date,nvl(poh.end_date,sysdate)) - nvl(poh.quote_warning_delay,0)) >= 
			decode(:P_date_to,NULL,nvl(pll.end_date,nvl(poh.end_date,sysdate))-nvl(poh.quote_warning_delay,0),
					         nvl(pll.end_date,poh.end_date) - nvl(poh.quote_warning_delay,0))
	AND   pol.category_id      = mca.category_id
	and  &P_where_cat
)
ORDER BY papf.full_name
,        pov.vendor_name
Parameter Name SQL text Validation
P_STRUCT_NUM
 
Number
Dynamic Precision Option
 
LOV Oracle
To
 
Categories From
 
To
 
Date
Quotation Expiration Dates From
 
Date
Buyer Name
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
Title