AP Receiving Hold Requestor Notice - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Receiving Hold Requestor Notice (XML) - Not Supported: Reserved For Future Use
Short Name: APXVDRAL_XML
DB package: AP_APXVDRAL_XMLP_PKG
SELECT	inv.invoice_num           C_INVOICE_NUM, 
        inv.invoice_date          C_INVOICE_DATE,
	po_lines.item_description C_DESCRIPTION,
	pov.vendor_name           C_VENDOR_NAME,
    upper(pov.vendor_name) C_UPP_VENDOR_NAME,
	hed.segment1              C_P_O, 
	sum(decode(aid.price_adjustment_flag, 'Y',0,'N',aid.quantity_invoiced)) C_QUANTITY_INV,
	initcap(prefix)                    C_PREFIX,
	first_name                C_NAME,
	middle_name               C_MIDDLE,
	last_name                 C_LAST,
    upper(last_name)      C_UPP_LAST_NAME,
	location_code             C_LINE1,
	address_line_2               C_LINE2,
	address_line_3               C_LINE3,
	town_or_city                      C_CITY,
	nvl(region_3,region_2)     C_STATE,
	postal_code                      C_ZIP,
	nvl(pod.deliver_to_person_id,hed.agent_id) C_REQUESTOR_ID
FROM	ap_invoices inv,
	ap_invoice_distributions aid,
	po_vendors pov,
	po_headers hed,
	po_line_locations pol,
	po_distributions_ap_v pod,
	po_lines,
    ap_holds h,
   	hr_locations locs, hr_employees_current_v emps
WHERE	inv.payment_status_flag in ('N', 'P')
AND	inv.invoice_id = aid.invoice_id
AND	aid.po_distribution_id = pod.po_distribution_id
AND	inv.vendor_id = pov.vendor_id
AND	h.line_location_id = pol.line_location_id
AND	pol.po_header_id = hed.po_header_id
AND pol.po_line_id = po_lines.po_line_id
AND	pod.line_location_id = h.line_location_id
AND	locs.location_id (+) = emps.location_id
AND 	emps.employee_id = nvl(pod.deliver_to_person_id,hed.agent_id)
AND     inv.invoice_id = h.invoice_id
AND     h.hold_lookup_code= 'QTY REC'
AND	h.release_lookup_code is null
GROUP BY  inv.invoice_id,pol.line_location_id,
      first_name,
	  last_name,  
	  inv.invoice_num, inv.invoice_date,
	  po_lines.item_description,
	  pol.quantity_received, pol.quantity_billed,
	  vendor_name,
	  hed.segment1,
      prefix,
	  middle_name,
	  location_code,
	  address_line_2,
	  address_line_3,
	  town_or_city,
	  nvl(region_3,region_2),
	  postal_code,
	  nvl(pod.deliver_to_person_id,hed.agent_id)
ORDER BY location_code, upper(last_name), first_name, middle_name, 
	 upper(vendor_name), inv.invoice_num, hed.segment1
Parameter Name SQL text Validation
Ledger
 
Phone of sender
 
Title of sender
 
Name of sender
 
Location of organization
 
LOV Oracle
Name of organization