AP Discounts Taken and Lost - draft

Description
Categories: BI Publisher
Columns: C Smry Vendor Id, C Smry Uppr Vendor Name, C Smry Vendor Name, C Smry Vendor Site Code, C Smry Vendor Num, C Smry Invoice Amount, C Smry Dscnt Amt Taken, C Smry Dscnt Amt Lost, C Smry Currency, C Smry No Rate Count ...
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 Name SQL text Validation
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
Ledger