AP Discounts Taken and Lost Report- Not Supported: Reserved For Future Use

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Discounts Taken and Lost Report
Application: Payables
Source: Discounts Taken and Lost Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXINDTL_XML
DB package: AP_APXINDTL_XMLP_PKG
Run AP Discounts Taken and Lost Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT	i.vendor_id C_SMRY_VENDOR_ID,
	upper(v1.vendor_name) C_SMRY_UPPR_VENDOR_NAME,
	v1.vendor_name C_SMRY_VENDOR_NAME, 
	v2.vendor_site_code C_SMRY_VENDOR_SITE_CODE, 
	segment1 C_SMRY_VENDOR_NUM,
                  SUM(DECODE(f.minimum_accountable_unit, NULL,
	                           round((p.amount / nvl(i.amount_paid,1) * i.invoice_amount) 
                                                          , f.precision),
                                             round((p.amount / nvl(i.amount_paid,1) * i.invoice_amount)
                                                         / f.minimum_accountable_unit)
                                                       * f.minimum_accountable_unit)
                  ) C_SMRY_INVOICE_AMOUNT ,
	SUM(DECODE(f.minimum_accountable_unit, NULL,
	                          round((nvl(p.discount_taken,0)/nvl(i.payment_cross_rate,1))
                                                          , f.precision),
                                             round((nvl(p.discount_taken,0)/nvl(i.payment_cross_rate,1))
                                                         / f.minimum_accountable_unit)
                                                       * f.minimum_accountable_unit)
                   ) C_SMRY_DSCNT_AMT_TAKEN, 
	decode((sign(SUM(DECODE(f.minimum_accountable_unit, NULL,
	                          round((nvl(p.discount_lost,0)/nvl(i.payment_cross_rate,1))
                                                          , f.precision),
                                             round((nvl(p.discount_lost,0)/nvl(i.payment_cross_rate,1))
                                                         / f.minimum_accountable_unit)
                                                       * f.minimum_accountable_unit)
                   ))*sign(invoice_amount)),-1,0, SUM(DECODE(f.minimum_accountable_unit, NULL,
	                          round((nvl(p.discount_lost,0)/nvl(i.payment_cross_rate,1))
                                                          , f.precision),
                                             round((nvl(p.discount_lost,0)/nvl(i.payment_cross_rate,1))
                                                         / f.minimum_accountable_unit)
                                                       * f.minimum_accountable_unit)
                   ))C_SMRY_DSCNT_AMT_LOST,    
	i.invoice_currency_code  C_SMRY_CURRENCY,
	sum(decode (p.exchange_rate,null,1,0)) C_SMRY_NO_RATE_COUNT, 
                  SUM(DECODE(f2.minimum_accountable_unit, NULL,
	                           round((p.amount / nvl(i.amount_paid,1) * i.base_amount) 
                                                          , f2.precision),
                                             round((p.amount / nvl(i.amount_paid,1) * i.base_amount)
                                                         / f2.minimum_accountable_unit)
                                                       * f2.minimum_accountable_unit)
                  ) C_SMRY_BASE_CURR_AMT ,
	SUM(DECODE(f2.minimum_accountable_unit, NULL,
	                          round((nvl(i.exchange_rate,1)*
                                                            (nvl(p.discount_taken,0)/nvl(i.payment_cross_rate,1)) 
                                                        ), f2.precision),
	                          round((nvl(i.exchange_rate,1)*
                                                            (nvl(p.discount_taken,0)/nvl(i.payment_cross_rate,1))
                                                        ) / f2.minimum_accountable_unit) *
                                            f2.minimum_accountable_unit)   
                           ) C_SMRY_BASE_CURR_DTKN,
	decode ((sign(SUM(DECODE(f2.minimum_accountable_unit, NULL,
	                          round((nvl(i.exchange_rate,1)*
                                                            (nvl(p.discount_lost,0)/nvl(i.payment_cross_rate,1)) 
                                                        ), f2.precision),
	                          round((nvl(i.exchange_rate,1)*
                                                            (nvl(p.discount_lost,0)/nvl(i.payment_cross_rate,1))
                                                        ) / f2.minimum_accountable_unit) *
                                            f2.minimum_accountable_unit)   
                           ))*sign(invoice_amount)),-1,0, SUM(DECODE(f2.minimum_accountable_unit, NULL,
	                          round((nvl(i.exchange_rate,1)*
                                                            (nvl(p.discount_lost,0)/nvl(i.payment_cross_rate,1)) 
                                                        ), f2.precision),
	                          round((nvl(i.exchange_rate,1)*
                                                            (nvl(p.discount_lost,0)/nvl(i.payment_cross_rate,1))
                                                        ) / f2.minimum_accountable_unit) *
                                            f2.minimum_accountable_unit)   
                           ))C_SMRY_BASE_CURR_DLST 
FROM	po_vendors v1, 
	po_vendor_sites v2, 
	ap_invoices i,
	ap_invoice_payments p, 	
	ap_checks c, 
	fnd_currencies_vl F,
	fnd_currencies_vl f2
WHERE	v1.vendor_id = i.vendor_id
AND	v2.vendor_site_id = i.vendor_site_id
AND	c.check_id = p.check_id
AND	i.invoice_id = p.invoice_id
AND          i.invoice_currency_code = F.currency_code
AND	(c.check_date BETWEEN :P_start_date AND :P_end_date)
AND	c.void_date IS NULL
AND	i.payment_status_flag != 'N'
AND	i.invoice_amount != 0
AND           i.amount_paid != 0
AND	nvl(v1.vendor_type_lookup_code, '-1') =
		DECODE(:P_vendor_type_lookup_code,  
                       NULL, nvl(v1.vendor_type_lookup_code, '-1'), 
		       'All', nvl(v1.vendor_type_lookup_code, '-1'), 
		       :P_vendor_type_lookup_code)
AND          f2.currency_code = :C_base_currency_code
&C_VENDOR_ID_PRED
AND	(nvl(p.discount_taken,0) != 0
	   or nvl(discount_lost,0) != 0)
GROUP BY  i.invoice_currency_code, 
	i.vendor_id,
	v1.vendor_name,
	segment1, 
	v2.vendor_site_code, 
	f2.precision, 
	f2.minimum_accountable_unit
	,invoice_amount 
ORDER BY i.invoice_currency_code, 
	upper(v1.vendor_name),
	v2.vendor_site_code
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
Start Payment Date 
 
 Date
End Payment Date 
 
 Date
Supplier Type (skip for All) 
 
 LOV Oracle
Supplier Name (skip for All) 
 
 LOV Oracle
Summarize Invoices by Site? 
 
 LOV Oracle
Download
Related Blitz Reports
AP Withholding Tax by Invoice JE Israeli Withholding Annual Certificate To Vendors AP Invoice History Report- Not Supported: Reserved For Future Use JE Israeli Withholding Tax File to Tax Authority JE Finnish Payables Account by Detail- Not Supported: Reserved For Future Use JE Israeli Withholding Tax Reconciliation JE Israeli Withholding Tax Summary/Detail GL Account Analysis (Distributions) 11g AP Invoices and Lines
Blitz Report™

Blitz Report™ provides multiple benefits: