AP Discounts Taken and Lost - draft

Description
Categories: BI Publisher, Financials, Procurement
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
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 NameSQL textValidation
Ledger
 
Summarize Invoices by Site?
 
LOV Oracle
Supplier Name (skip for All)
 
LOV Oracle
Supplier Type (skip for All)
 
LOV Oracle
End Payment Date
 
Date
Start Payment Date
 
Date