AP Matched and Modified Receipts - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Matched and Modified Receipts Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXMTMRR_XML
DB package: AP_APXMTMRR_XMLP_PKG
SELECT      rsh.receipt_num  C_RECEIPT_NUMBER
                   ,rsh.creation_date  C_RECEIPT_CREATION_DATE
                   ,rsh.last_update_date  C_RECEIPT_LAST_UPDATE_DATE
                   ,pv1.vendor_name C_VENDOR_NAME
                   ,pvs.vendor_site_code C_VENDOR_SITE_CODE
                   ,rsl.line_num C_RECEIPT_LINE_NUM
	,rsl.quantity_received  C_QUANTITY_RECEIVED
                   ,rct1.primary_quantity  C_ADJUSTMENT
  	,rct1.creation_date  C_ADJUST_DATE
	,rct1.transaction_id C_TRANSACTION_ID
                   ,po.segment1  C_PO_NUMBER
	,ai.invoice_num C_INVOICE_NUM
	,pv1.vendor_name C_VENDOR_NAME1
                   ,alc.displayed_field   C_APPROVAL_STATUS
	,ail.line_number C_LINE_NUMBER
	,ail.quantity_invoiced C_QUANTITY_BILLED
	,ail.creation_date C_LINE_CREATION_DATE
FROM         AP_INVOICES AI
	,AP_INVOICE_LINES AIL
	,PO_VENDORS pv1
	,PO_VENDOR_SITES PVS
	,PO_HEADERS PO
 	,RCV_SHIPMENT_HEADERS RSH
	,RCV_SHIPMENT_LINES RSL
	,RCV_TRANSACTIONS RCT
	,RCV_TRANSACTIONS RCT1
                  ,AP_LOOKUP_CODES ALC
WHERE	AI.invoice_id=AIL.invoice_id
AND	RCT.transaction_id = AIL.rcv_transaction_id
AND	pv1.vendor_id = AI.vendor_id
AND	PVS.vendor_site_id = AI.vendor_site_id
AND           PO.po_header_id = RSL.po_header_id
AND	RSL.shipment_line_id = RCT.shipment_line_id
AND           RCT.shipment_header_id = RSH.shipment_header_id
AND	RCT1.shipment_header_id = RCT.shipment_header_id
AND	RCT1.shipment_line_id = RCT.shipment_line_id
AND	RCT1.transaction_type IN
		('CORRECT','RETURN TO VENDOR','REJECT','RETURN TO RECEIVING','ACCEPT')	
AND	RCT1.creation_date > AIL.creation_date
AND         ALC.lookup_type(+) ='NLS TRANSLATION'
AND         ((:p_approval_status IS NOT NULL   AND ALC.lookup_code = :p_approval_status)
                   OR (:p_approval_status IS NULL AND ALC.lookup_code  =  
                           ap_invoices_pkg.get_approval_status(ai.invoice_id,ai.invoice_amount,
                  		ai.payment_status_flag,ai.invoice_type_lookup_code)))
&LP_VENDOR_ID
&LP_VENDOR_SITE
&LP_MODIFY_FROM_DATE
&LP_MODIFY_TO_DATE
&LP_APPROVAL_STATUS
ORDER BY
pv1.vendor_name
,pvs.vendor_site_code
,rsh.receipt_num
,rsl.line_num
,rct1.transaction_id
,ai.invoice_num
,ail.line_number
Ask a question
Parameter Name SQL text Validation
Ledger Id
 
Number
Invoice Status
 
LOV Oracle
Receipt Modify Date To
 
Date
Receipt Modify Date From
 
Date
Supplier Site
 
LOV Oracle
Supplier Name
 
LOV Oracle