AR Discount Projection

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Discount Projection Report (XML)
Short Name: ARXDPR_XML
DB package: AR_ARXDPR_XMLP_PKG
SELECT  &ACCT_FLEX_BAL_SEG bal_seg,
ps.invoice_currency_code  currency_code,
to_char(ps.trx_date ,'DD-MON-RR') trx_date,
ps.customer_id customer_id,
cust.ACCOUNT_NUMBER customer_number,
SUBSTRB(PARTY.PARTY_NAME,1,50) customer_name,
ps.trx_number trx_number,
ps.due_date due_date,
nvl(ps.discount_taken_earned ,0) disc_taken_earned,
nvl(ps.discount_taken_unearned ,0) disc_taken_unearned,
nvl(ps. amount_line_items_original,0) lio,
ps.payment_schedule_id psi,
l.meaning invoice_class,
ps.term_id term_id,
term.name term_name,
term.calc_discount_on_lines_flag discount_on_lines,
term.partial_discount_flag partial_discount,
ps.terms_sequence_number seq_number,
ps.amount_due_original ado,
nvl(nvl(cp_site.discount_grace_days,cp_cust.discount_grace_days),0) discount_grace_days,
ps.amount_due_remaining adr,
ps.cash_receipt_id,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_bal_seg', 'SQLGL', 'GL#', comp.CHART_OF_ACCOUNTS_ID, NULL, comp.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') d_bal_seg,
	&currency_code curr,
	&out_disc_to_take out_disc_to_take,
	AR_ARXDPR_XMLP_PKG.unearnd_disc_pctformula(:UNEARNED_DISCOUNT) Unearnd_disc_pct,
	AR_ARXDPR_XMLP_PKG.abs_discformula() abs_disc,
	AR_ARXDPR_XMLP_PKG.out_discount_date_p out_discount_date,
	AR_ARXDPR_XMLP_PKG.out_amt_to_apply_p out_amt_to_apply,
	AR_ARXDPR_XMLP_PKG.earned_disc_pct_p earned_disc_pct,
	AR_ARXDPR_XMLP_PKG.out_earned_disc_p out_earned_disc,
	AR_ARXDPR_XMLP_PKG.out_unearned_disc_p out_unearned_disc,
	AR_ARXDPR_XMLP_PKG.best_disc_pct_p best_disc_pct
FROM
ar_lookups l,
HZ_customer_profiles cp_cust,
HZ_customer_profiles cp_site,
ra_terms term,
gl_code_combinations comp,
ra_customer_trx trx,
ar_xla_ctlgd_lines_v dist,
HZ_CUST_ACCOUNTS cust,
HZ_PARTIES PARTY,
ar_payment_schedules ps
WHERE   ps.status	            = 'OP'
AND     ps.class	            != 'GUAR'
AND     cust.CUST_ACCOUNT_ID           =  ps.customer_id
AND     CUST.PARTY_ID = PARTY.PARTY_ID
AND     trx.customer_trx_id         = ps.customer_trx_id
AND     trx.customer_trx_id        = dist.customer_trx_id
AND     dist.account_class          = 'REC'
AND     dist.account_set_flag      = decode(trx.invoicing_rule_id,						     null,'N','Y')	AND     dist.code_combination_id    = comp.code_combination_id(+)
AND     term.term_id(+)	            = ps.term_id
AND     ps.customer_id              = cp_cust.CUST_ACCOUNT_ID
AND     cp_cust.site_use_id         is null
AND     ps.customer_site_use_id     = cp_site.site_use_id(+)
AND     l.lookup_type               = 'INV/CM'
AND     l.lookup_code               = ps.class
AND     PARTY.PARTY_NAME
            BETWEEN NVL(:p_cust_name_low,PARTY.PARTY_NAME)
            AND          NVL(:p_cust_name_high,PARTY.PARTY_NAME)
AND     cust.ACCOUNT_NUMBER
            BETWEEN NVL(:p_cust_num_low,cust.ACCOUNT_NUMBER)
            AND         NVL(:p_cust_num_high,cust.ACCOUNT_NUMBER)
AND     ps.invoice_currency_code
            BETWEEN NVL(:p_currency_low,ps.invoice_currency_code)
            AND           NVL(:p_currency_high,ps.invoice_currency_code)
AND    &ACCT_BAL_WHERE_CLAUSE
--ORDER BY 2,1,4,5,6,22,7,13,15,8
ORDER BY 2 ASC,1 ASC,4 ASC,6 ASC,5 ASC
Parameter Name SQL text Validation
Chart of Accounts Id
 
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Company Segment High
 
Company Segment Low
 
Currency High
 
LOV Oracle
Currency Low
 
LOV Oracle
As of Date
 
Date
Ask a question