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

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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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