Generating Aging report as a TEXT file in oracle apps using utl_file in server location
package spec
CREATE OR REPLACE PACKAGE XXRS_AGING_4_BUCKETS_PKG
AS
PROCEDURE XXRS_AGING_4_BUCKETS_PROC(
errbuff OUT VARCHAR2,
retcode OUT NUMBER,
P_REPORTING_LEVEL IN VARCHAR2,
P_REPORTING_ENTITY_ID IN VARCHAR2,
--p_ca_set_of_books_id number,
P_COA IN VARCHAR2,
P_BAL_SEGMENT_LOW IN VARCHAR2,
P_BAL_SEGMENT_HIGH IN VARCHAR2,
P_AS_OF_DATE IN VARCHAR2,
p_order_by IN VARCHAR2,
P_SUMMARY_OPTION_LOW IN VARCHAR2,
P_FORMAT_OPTION_LOW IN VARCHAR2,
P_BUCKET_TYPE_LOW IN VARCHAR2,
P_CREDIT_OPTION IN VARCHAR2,
p_risk_option IN VARCHAR2,
p_curr_code IN VARCHAR2,
p_customer_name_low IN VARCHAR2,
p_customer_name_high IN VARCHAR2,
p_customer_number_low IN VARCHAR2,
p_customer_number_high IN VARCHAR2,
P_AMT_DUE_LOW IN VARCHAR2,
P_AMT_DUE_HIGH IN VARCHAR2,
p_invoice_type_low IN VARCHAR2,
p_invoice_type_high IN VARCHAR2);
END;
package body
CREATE OR REPLACE PACKAGE BODY XXRS_AGING_4_BUCKETS_PKG
AS
PROCEDURE XXRS_AGING_4_BUCKETS_PROC(
--XXRS_AGING_4_BUCKETS_PKG.XXRS_AGING_4_BUCKETS_PROC
errbuff OUT VARCHAR2,
retcode OUT NUMBER,
P_REPORTING_LEVEL IN VARCHAR2,
P_REPORTING_ENTITY_ID IN VARCHAR2,
--p_ca_set_of_books_id number,
P_COA IN VARCHAR2,
P_BAL_SEGMENT_LOW IN VARCHAR2,
P_BAL_SEGMENT_HIGH IN VARCHAR2,
P_AS_OF_DATE IN VARCHAR2,
p_order_by IN VARCHAR2,
P_SUMMARY_OPTION_LOW IN VARCHAR2,
P_FORMAT_OPTION_LOW IN VARCHAR2,
P_BUCKET_TYPE_LOW IN VARCHAR2,
P_CREDIT_OPTION IN VARCHAR2,
p_risk_option IN VARCHAR2,
p_curr_code IN VARCHAR2,
p_customer_name_low IN VARCHAR2,
p_customer_name_high IN VARCHAR2,
p_customer_number_low IN VARCHAR2,
p_customer_number_high IN VARCHAR2,
P_AMT_DUE_LOW IN VARCHAR2,
P_AMT_DUE_HIGH IN VARCHAR2,
p_invoice_type_low IN VARCHAR2,
p_invoice_type_high IN VARCHAR2)
AS
rp_convert_flag VARCHAR2(50);
func_curr_precision VARCHAR2(50);
functional_currency VARCHAR2(50);
rp_bucket_line_type_0 VARCHAR2(50);
rp_bucket_days_from_0 VARCHAR2(20);
rp_bucket_days_from_1 VARCHAR2(20);
rp_bucket_days_to_1 VARCHAR2(20);
rp_bucket_category VARCHAR2(50);
rp_bucket_days_to_0 VARCHAR2(20);
rp_bucket_line_type_1 DATE;
rp_bucket_days_to_2 VARCHAR2(20);
rp_short_unid_phrase VARCHAR2(50);
c_claim_meaning VARCHAR2(50);
c_payment_meaning VARCHAR2(50);
rp_bucket_line_type_2 VARCHAR2(50);
rp_bucket_days_from_2 VARCHAR2(20);
rp_bucket_line_type_3 VARCHAR2(50);
rp_bucket_days_from_3 VARCHAR2(20);
rp_bucket_days_to_3 VARCHAR2(20);
c_risk_meaning VARCHAR2(50);
--fnd_date.canonical_to_date(P_AS_OF_DATE) VARCHAR2(50);
--v_customer_name_inv VARCHAR2(150);
CURSOR c1
IS
SELECT ps.org_id invoice_org_id,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, types.cust_trx_type_id ) dummy_id_inv,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL,types.name) dummy_type_inv,
/* bug2820114 Unidentified payment must be at end of report. */
DECODE(party.party_name, NULL , '2', rtrim(rpad(substrb(
party.party_name,1,50),36) ) ) customer_name_inv,
cust_acct.cust_account_id customer_id_inv,
cust_acct.account_number customer_number_inv,
DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) contact_site_id_inv,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.state,2))) cust_state_inv ,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.city,25))) cust_city_inv ,
DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) addr_id_inv ,
rtrim(rpad(trx.purchase_order,12)) reference_number ,
ps.payment_schedule_id payment_sched_id_inv,
ps.class class_inv,
ps.due_date due_date_inv ,
DECODE( rp_convert_flag, 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining ) amt_due_remaining_inv ,
DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) amt_due_original_inv ,
ps.trx_number invnum ,
types.name invoice_type_inv,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date) days_past_due_inv,
ps.amount_adjusted amount_adjusted_inv,
ps.amount_applied amount_applied_inv,
ps.amount_credited amount_credited_inv,
ps.gl_date gl_date_inv,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) data_converted_inv,
NVL(ps.exchange_rate, 1) ps_exchange_rate_inv,
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36)) customer_name_inv_dsp
FROM ra_cust_trx_types_all types,
hz_cust_accounts cust_acct,
hz_parties party,
--** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
--** -- lp_ra_customer_trx_all trx,
RA_CUSTOMER_TRX_ALL trx, --SV
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
ra_cust_trx_line_gl_dist_all gld,
/* Enhancement 7346488.
xla_distribution_links lk,
xla_ae_lines ae, */
gl_code_combinations cc
WHERE TRUNC(ps.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.customer_trx_id+0 = trx.customer_trx_id
AND ps.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)--SV
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND ps.cust_trx_type_id = types.cust_trx_type_id
-- bug1960831
AND NVL(ps.org_id,-99) = NVL(types.org_id,-99)
AND types.name LIKE NVL(p_invoice_type_low,types.name)--SV
AND types.name LIKE NVL(p_invoice_type_high,types.name)
--** -- lp_invoice_type_low
--** -- lp_invoice_type_high
AND ps.customer_site_use_id+0 = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id (+) = party_site.location_id
AND ps.gl_date_closed > fnd_date.canonical_to_date(P_AS_OF_DATE)
--** -- lp_curr_code
AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
AND ps.customer_trx_id+0 = gld.customer_trx_id
AND gld.account_class = 'REC'
AND gld.latest_rec_flag = 'Y'
/* Enhancement 7346488.
and gld.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1 (+)
and lk.source_distribution_type (+) = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
and lk.application_id (+) = 222
and ae.application_id (+) = 222
and lk.ae_header_id = ae.ae_header_id (+)
and lk.ae_line_num = ae.ae_line_num (+)
and decode(lk.accounting_line_code, 'CM_EXCH_GAIN_LOSS', 'N',
'AUTO_GEN_GAIN_LOSS', 'N', 'Y') = 'Y'
and decode(ae.ledger_id,'',decode(gld.posting_control_id,-3,-999999,gld.code_combination_id),gld.set_of_books_id,ae.code_combination_id,-999999)= cc.code_combination_id
*/
AND gld.code_combination_id = cc.code_combination_id
--** -- lp_bal_segment_low-doubt
--** -- lp_bal_segment_high--doubt
-- Bug# 1233291
-- and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
-- AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id)--doubt
--** -- P_ORG_WHERE_CTT
--** -- P_ORG_WHERE_PS
-- --** -- P_ORG_WHERE_TRX
-- --** -- P_ORG_WHERE_SITE
--** -- P_ORG_WHERE_ADDR
--** -- P_ORG_WHERE_GLD
UNION ALL
SELECT ps.org_id,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, -999) ,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning) ),
/* bug2820114 Unidentified payment must be at end of report */
DECODE(party.party_name , NULL , '2' , rtrim(rpad(substrb(
party.party_name,1,50),36))),
NVL(cust_acct.cust_account_id, -999),
cust_acct.account_number,
DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) ,
DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.state,1,2)) ,
DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.city,1,25)) ,
DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) ,
NULL,
ps.payment_schedule_id,
ps.class,
ps.due_date,
-SUM ( DECODE( rp_convert_flag, 'Y', app.acctd_amount_applied_from, app.amount_applied ) ),
ps.amount_due_original,
ps.trx_number invnum ,
DECODE (UPPER(p_order_by),'XXX',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning)) ,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)),
NVL(ps.exchange_rate, 1),
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
--** -- lp_bal_segment bal_segment_value_inv,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
FROM --** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
ar_cons_inv_all, --SV
--** -- lp_ar_receivable_apps_all app,
AR_RECEIVABLE_APPLICATIONS_ALL app, --SV
gl_code_combinations cc,
hz_cust_accounts cust_acct,
hz_parties party,
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc
WHERE app.gl_date+0 <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.customer_id = cust_acct.cust_account_id(+)
AND cust_acct.party_id = party.party_id(+)
/* Bug fix 5376284
Replacing 2 parameters with 1 parameters */
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND ps.cash_receipt_id +0 = app.cash_receipt_id
AND ps.customer_site_use_id+0 = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND app.code_combination_id = cc.code_combination_id
AND app.status IN ( 'ACC', 'UNAPP', 'UNID', 'OTHER ACC')
AND NVL(app.confirmed_flag, 'Y') = 'Y'
AND ps.gl_date_closed > fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ((app.reversal_gl_date IS NOT NULL
AND ps.gl_date <= fnd_date.canonical_to_date(P_AS_OF_DATE))
OR app.reversal_gl_date IS NULL )
--** -- lp_curr_code
AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
AND NVL( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
-- Bug# 1233291
-- and app.receivable_application_id+0 < :rp_app_max_id
-- and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
--AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id) SV doubt
--** -- p_org_where_ps
--** -- p_org_where_app
-- --** -- p_org_where_site
--** -- p_org_where_addr
GROUP BY ps.org_id,
party.party_name,
site.site_use_id,
loc.state,
loc.city,
addr.cust_acct_site_id,
cust_acct.cust_account_id,
cust_acct.account_number,
ps.payment_schedule_id,
ps.class,
ps.due_date,
ps.amount_due_original,
ps.trx_number,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
--** -- lp_bal_segment ,
DECODE( app.status, 'UNID', 'UNID', 'UNAPP'),
--** -- lp_query_show_bill,
app.applied_payment_schedule_id--10,444 records
UNION ALL
SELECT ps.org_id,
DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, -999) ,
DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, c_risk_meaning) ,
/* bug2820114 Unidentified payment must be at end of report */
DECODE(party.party_name, NULL, '2' , rtrim(rpad(substrb(
party.party_name,1,50),36) )) ,
cust_acct.cust_account_id ,
cust_acct.account_number ,
DECODE(UPPER(p_format_option_low),'B', 0, site.site_use_id) ,
DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.state,2))) ,
DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.city,25))) ,
DECODE(UPPER(p_format_option_low),'B', 0, addr.cust_acct_site_id ) ,
NULL ,
ps.payment_schedule_id ,
DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
ps.due_date ,
DECODE( rp_convert_flag, 'Y', crh.acctd_amount , crh.amount ) ,
DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) ,
ps.trx_number ,
DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
ps.amount_adjusted ,
ps.amount_applied ,
ps.amount_credited ,
crh.gl_date ,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) ,
NVL(ps.exchange_rate, 1) ,
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b3_inv,
--** -- lp_bal_segment ,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
FROM hz_cust_accounts cust_acct,
hz_parties party,
--** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
ar_cons_inv_all, --SV
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
--** -- lp_ar_cash_receipts_all cr,
AR_CASH_RECEIPTS_ALL cr, --SV
--** -- lp_ar_cash_receipt_history_all crh,
AR_CASH_RECEIPT_HISTORY_ALL crh, --SV
gl_code_combinations cc
WHERE TRUNC(crh.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.trx_number IS NOT NULL
AND upper(p_risk_option) != 'NONE'
AND ps.customer_id = cust_acct.cust_account_id(+)
AND cust_acct.party_id = party.party_id(+)
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
AND ps.cash_receipt_id = cr.cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.account_code_combination_id = cc.code_combination_id
AND ps.customer_site_use_id = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
--** -- lp_curr_code
AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
AND ( crh.current_record_flag = 'Y'
OR crh.reversal_gl_date > fnd_date.canonical_to_date(P_AS_OF_DATE) )
AND crh.status NOT IN ( DECODE(crh.factor_flag, 'Y','RISK_ELIMINATED', 'N','CLEARED'), 'REVERSED')
/* Bug 4127480 : exclude receipts applied to short term debt */
AND NOT EXISTS
(SELECT 'x'
FROM --** -- lp_ar_receivable_apps_all ra
AR_RECEIVABLE_APPLICATIONS_ALL ra --SV
WHERE ra.cash_receipt_id = cr.cash_receipt_id
AND ra.status = 'ACTIVITY'
AND applied_payment_schedule_id = -2
)--10,444 records
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
--Bug 2124653
--and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
--AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id) doubt
--** -- p_org_where_ps
-- --** -- p_org_where_site
--** -- p_org_where_addr
--** -- p_org_where_cr
--** -- p_org_where_crh
UNION ALL
SELECT ps.org_id invoice_org_id,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, types.cust_trx_type_id ) dummy_id_inv,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL,types.name) dummy_type_inv,
/* bug2820114 Unidentified payment must be at end of report. */
DECODE(party.party_name, NULL , '2', rtrim(rpad(substrb(
party.party_name,1,50),36) ) ) customer_name_inv,
cust_acct.cust_account_id customer_id_inv,
cust_acct.account_number customer_number_inv,
DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) contact_site_id_inv,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.state,2))) cust_state_inv ,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.city,25))) cust_city_inv ,
DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) addr_id_inv ,
rtrim(rpad(trx.purchase_order,12)) reference_number ,
ps.payment_schedule_id payment_sched_id_inv,
ps.class class_inv,
ps.due_date due_date_inv ,
DECODE( rp_convert_flag, 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining ) amt_due_remaining_inv ,
DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) amt_due_original_inv ,
ps.trx_number invnum ,
types.name invoice_type_inv,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date) days_past_due_inv,
ps.amount_adjusted amount_adjusted_inv,
ps.amount_applied amount_applied_inv,
ps.amount_credited amount_credited_inv,
ps.gl_date gl_date_inv,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) data_converted_inv,
NVL(ps.exchange_rate, 1) ps_exchange_rate_inv,
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
--** -- lp_bal_segment bal_segment_value_inv,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36)) customer_name_inv_dsp
FROM ra_cust_trx_types_all types,
hz_cust_accounts cust_acct,
hz_parties party,
--** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
--** -- lp_ra_customer_trx_all trx,
RA_CUSTOMER_TRX_ALL trx, --SV
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
ra_cust_trx_line_gl_dist_all gld,
/* Enhancement 7346488.
xla_distribution_links lk,
xla_ae_lines ae, */
gl_code_combinations cc
WHERE TRUNC(ps.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.customer_trx_id+0 = trx.customer_trx_id
AND ps.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
AND ps.cust_trx_type_id = types.cust_trx_type_id
-- bug1960831
AND NVL(ps.org_id,-99) = NVL(types.org_id,-99)
--** -- lp_invoice_type_low
--** -- lp_invoice_type_high
AND types.name LIKE NVL(p_invoice_type_low,types.name)
AND types.name LIKE NVL(p_invoice_type_high,types.name)
AND ps.customer_site_use_id+0 = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id (+) = party_site.location_id
AND ps.gl_date_closed > fnd_date.canonical_to_date(P_AS_OF_DATE)
--** -- lp_curr_code
AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
AND ps.customer_trx_id+0 = gld.customer_trx_id
AND gld.account_class = 'REC'
AND gld.latest_rec_flag = 'Y'
/* Enhancement 7346488.
and gld.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1 (+)
and lk.source_distribution_type (+) = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
and lk.application_id (+) = 222
and ae.application_id (+) = 222
and lk.ae_header_id = ae.ae_header_id (+)
and lk.ae_line_num = ae.ae_line_num (+)
and decode(lk.accounting_line_code, 'CM_EXCH_GAIN_LOSS', 'N',
'AUTO_GEN_GAIN_LOSS', 'N', 'Y') = 'Y'
and decode(ae.ledger_id,'',decode(gld.posting_control_id,-3,-999999,gld.code_combination_id),gld.set_of_books_id,ae.code_combination_id,-999999)= cc.code_combination_id
*/
AND gld.code_combination_id = cc.code_combination_id
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
-- Bug# 1233291
-- and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
-- AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id); doubt
--** -- P_ORG_WHERE_CTT
--** -- P_ORG_WHERE_PS
-- --** -- P_ORG_WHERE_TRX
-- --** -- P_ORG_WHERE_SITE
--** -- P_ORG_WHERE_ADDR
--** -- P_ORG_WHERE_GLD
UNION ALL
SELECT ps.org_id,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, -999) ,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning) ),
/* bug2820114 Unidentified payment must be at end of report */
DECODE(party.party_name , NULL , '2' , rtrim(rpad(substrb(
party.party_name,1,50),36))),
NVL(cust_acct.cust_account_id, -999),
cust_acct.account_number,
DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) ,
DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.state,1,2)) ,
DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.city,1,25)) ,
DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) ,
NULL,
ps.payment_schedule_id,
ps.class,
ps.due_date,
-SUM ( DECODE( rp_convert_flag, 'Y', app.acctd_amount_applied_from, app.amount_applied ) ),
ps.amount_due_original,
ps.trx_number invnum ,
DECODE (UPPER(p_order_by),'XXX',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning)) ,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)),
NVL(ps.exchange_rate, 1),
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
--** -- lp_bal_segment bal_segment_value_inv,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
FROM --** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
ar_cons_inv_all, --SV
--** -- lp_ar_receivable_apps_all app,
AR_RECEIVABLE_APPLICATIONS_ALL app, --SV
gl_code_combinations cc,
hz_cust_accounts cust_acct,
hz_parties party,
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc
WHERE app.gl_date+0 <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.customer_id = cust_acct.cust_account_id(+)
AND cust_acct.party_id = party.party_id(+)
/* Bug fix 5376284
Replacing 2 parameters with 1 parameters */
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
AND ps.cash_receipt_id +0 = app.cash_receipt_id
AND ps.customer_site_use_id+0 = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND app.code_combination_id = cc.code_combination_id
AND app.status IN ( 'ACC', 'UNAPP', 'UNID', 'OTHER ACC')
AND NVL(app.confirmed_flag, 'Y') = 'Y'
AND ps.gl_date_closed > fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ((app.reversal_gl_date IS NOT NULL
AND ps.gl_date <= fnd_date.canonical_to_date(P_AS_OF_DATE))
OR app.reversal_gl_date IS NULL )
--** -- lp_curr_code
AND NVL( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
-- Bug# 1233291
-- and app.receivable_application_id+0 < :rp_app_max_id
-- and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
--** -- p_org_where_ps
--** -- p_org_where_app
-- --** -- p_org_where_site
--** -- p_org_where_addr
GROUP BY ps.org_id,
party.party_name,
site.site_use_id,
loc.state,
loc.city,
addr.cust_acct_site_id,
cust_acct.cust_account_id,
cust_acct.account_number,
ps.payment_schedule_id,
ps.class,
ps.due_date,
ps.amount_due_original,
ps.trx_number,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
--** -- lp_bal_segment ,
DECODE( app.status, 'UNID', 'UNID', 'UNAPP'),
--** -- lp_query_show_bill,
app.applied_payment_schedule_id--10,444 records
UNION ALL
SELECT ps.org_id,
DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, -999) ,
DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, c_risk_meaning) ,
/* bug2820114 Unidentified payment must be at end of report */
DECODE(party.party_name, NULL, '2' , rtrim(rpad(substrb(
party.party_name,1,50),36) )) ,
cust_acct.cust_account_id ,
cust_acct.account_number ,
DECODE(UPPER(p_format_option_low),'B', 0, site.site_use_id) ,
DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.state,2))) ,
DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.city,25))) ,
DECODE(UPPER(p_format_option_low),'B', 0, addr.cust_acct_site_id ) ,
NULL ,
ps.payment_schedule_id ,
DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
ps.due_date ,
DECODE( rp_convert_flag, 'Y', crh.acctd_amount , crh.amount ) ,
DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) ,
ps.trx_number ,
DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
ps.amount_adjusted ,
ps.amount_applied ,
ps.amount_credited ,
crh.gl_date ,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) ,
NVL(ps.exchange_rate, 1) ,
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b3_inv,
--** -- lp_bal_segment ,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
FROM hz_cust_accounts cust_acct,
hz_parties party,
--** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
ar_cons_inv_all, --SV
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
--** -- lp_ar_cash_receipts_all cr,
AR_CASH_RECEIPTS_ALL cr, --SV
--** -- lp_ar_cash_receipt_history_all crh,
AR_CASH_RECEIPT_HISTORY_ALL crh, --SV
gl_code_combinations cc
WHERE TRUNC(crh.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.trx_number IS NOT NULL
AND upper(p_risk_option) != 'NONE'
AND ps.customer_id = cust_acct.cust_account_id(+)
AND cust_acct.party_id = party.party_id(+)
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
AND ps.cash_receipt_id = cr.cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.account_code_combination_id = cc.code_combination_id
AND ps.customer_site_use_id = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
--** -- lp_curr_code
AND ( crh.current_record_flag = 'Y'
OR crh.reversal_gl_date > fnd_date.canonical_to_date(P_AS_OF_DATE) )
AND crh.status NOT IN ( DECODE(crh.factor_flag, 'Y','RISK_ELIMINATED', 'N','CLEARED'), 'REVERSED')
/* Bug 4127480 : exclude receipts applied to short term debt */
AND NOT EXISTS
(SELECT 'x'
FROM --** -- lp_ar_receivable_apps_all ra
AR_RECEIVABLE_APPLICATIONS_ALL ra --SV
WHERE ra.cash_receipt_id = cr.cash_receipt_id
AND ra.status = 'ACTIVITY'
AND applied_payment_schedule_id = -2
)--10,444 records
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
--Bug 2124653
--and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
--** -- p_org_where_ps
-- --** -- p_org_where_site
--** -- p_org_where_addr
--** -- p_org_where_cr
--** -- p_org_where_crh
UNION ALL
--------------------------
-- Bills Receivable
-----------------
SELECT ps.org_id,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, types.cust_trx_type_id ) dummy_id_inv,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL,types.name) dummy_type_inv,
/* bug2820114 Unidentified payment must be at end of report */
DECODE(party.party_name , NULL , '2' , rtrim(rpad(substrb(
party.party_name,1,50),36)) ) customer_name_inv,
cust_acct.cust_account_id customer_id_inv,
cust_acct.account_number customer_number_inv,
DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) contact_site_id_inv,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.state,2))) cust_state_inv ,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.city,25))) cust_city_inv ,
DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) addr_id_inv ,
NULL ,
ps.payment_schedule_id payment_sched_id_inv,
ps.class class_inv,
ps.due_date due_date_inv ,
DECODE( rp_convert_flag, 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining ) amt_due_remaining_inv ,
DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) amt_due_original_inv ,
ps.trx_number invnum ,
types.name invoice_type_inv,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
ps.amount_adjusted amount_adjusted_inv,
ps.amount_applied amount_applied_inv,
ps.amount_credited amount_credited_inv,
ps.gl_date gl_date_inv,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) data_converted_inv,
NVL(ps.exchange_rate, 1) ps_exchange_rate_inv,
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
--** -- lp_bal_segment bal_segment_value_inv,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
FROM ra_cust_trx_types_all types,
hz_cust_accounts cust_acct,
hz_parties party,
--** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
ar_cons_inv_all, --SV
--** -- lp_ra_customer_trx_all trx,
RA_CUSTOMER_TRX_ALL trx, --SV
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
ar_transaction_history_all th,
--** -- lp_ar_distributions_all dist,
AR_DISTRIBUTIONS_ALL dist, --SV
gl_code_combinations cc
WHERE TRUNC(ps.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.class = 'BR'
AND ps.customer_trx_id+0 = trx.customer_trx_id
AND ps.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id =party.party_id
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
AND ps.cust_trx_type_id = types.cust_trx_type_id
-- bug1960831
AND NVL(ps.org_id ,-99) = NVL(types.org_id,-99)
--** -- lp_invoice_type_low
--** -- lp_invoice_type_high
AND ps.customer_site_use_id+0 = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND ps.gl_date_closed > fnd_date.canonical_to_date(P_AS_OF_DATE)
--** -- lp_curr_code
AND ps.customer_trx_id+0 = th.customer_trx_id
--4147868 and th.current_accounted_flag = 'Y'
AND th.transaction_history_id = dist.source_id
AND dist.source_table = 'TH'
--4147868 and dist.source_type = 'REC'
/*4147868 : Added following code */
AND th.transaction_history_id =
(SELECT MAX(transaction_history_id)
FROM ar_transaction_history_all th2,
--** -- lp_ar_distributions_all dist2
AR_DISTRIBUTIONS_ALL dist2 --SV
WHERE th2.transaction_history_id = dist2.source_id
AND dist2.source_table = 'TH'
AND th2.gl_date <= fnd_date.canonical_to_date(P_AS_OF_DATE)
/*GSCC Compliance*/
AND dist2.amount_dr IS NOT NULL
AND th2.customer_trx_id = ps.customer_trx_id
)
AND dist.amount_dr IS NOT NULL
/*4147868 end*/
AND dist.source_table_secondary IS NULL
AND dist.code_combination_id = cc.code_combination_id
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
-- Bug# 1233291
-- and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
--** -- P_ORG_WHERE_CTT
--** -- P_ORG_WHERE_PS
-- --** -- P_ORG_WHERE_TRX
-- --** -- P_ORG_WHERE_SITE
--** -- P_ORG_WHERE_ADDR
--** -- lp_order_by_inv
ORDER BY 4,17;
vtext VARCHAR2(2000);
vfilename VARCHAR2(2000);
v_p_order_by VARCHAR2(2000);
file_id utl_file.file_type;
BEGIN
fnd_global.APPS_INITIALIZE(1151,20678,222);
--fnd_date.canonical_to_date(P_AS_OF_DATE) :=fnd_date.canonical_to_date(P_AS_OF_DATE);
fnd_file.put_line(FND_FILE.LOG,'P_REPORTING_LEVEL'||P_REPORTING_LEVEL);
fnd_file.put_line(FND_FILE.LOG,'P_REPORTING_ENTITY_ID'||P_REPORTING_ENTITY_ID);
fnd_file.put_line(FND_FILE.LOG,'P_COA'||P_COA);
fnd_file.put_line(FND_FILE.LOG,'P_BAL_SEGMENT_LOW'||P_BAL_SEGMENT_LOW);
fnd_file.put_line(FND_FILE.LOG,'P_BAL_SEGMENT_HIGH'||P_BAL_SEGMENT_HIGH);
fnd_file.put_line(FND_FILE.LOG,'fnd_date.canonical_to_date(P_AS_OF_DATE)'||fnd_date.canonical_to_date(P_AS_OF_DATE));
fnd_file.put_line(FND_FILE.LOG,'p_order_by'||p_order_by);
fnd_file.put_line(FND_FILE.LOG,'P_SUMMARY_OPTION_LOW'||P_SUMMARY_OPTION_LOW);
fnd_file.put_line(FND_FILE.LOG,'P_FORMAT_OPTION_LOW'||P_FORMAT_OPTION_LOW);
fnd_file.put_line(FND_FILE.LOG,'P_BUCKET_TYPE_LOW'||P_BUCKET_TYPE_LOW);
fnd_file.put_line(FND_FILE.LOG,'P_CREDIT_OPTION'||P_CREDIT_OPTION);
fnd_file.put_line(FND_FILE.LOG,'p_risk_option'||p_risk_option);
fnd_file.put_line(FND_FILE.LOG,'p_curr_code'||p_curr_code);
fnd_file.put_line(FND_FILE.LOG,'p_customer_name_low'||p_customer_name_low);
fnd_file.put_line(FND_FILE.LOG,'p_customer_name_high'||p_customer_name_high);
fnd_file.put_line(FND_FILE.LOG,'p_customer_number_low'||p_customer_number_low);
fnd_file.put_line(FND_FILE.LOG,'p_customer_number_high'||p_customer_number_high);
fnd_file.put_line(FND_FILE.LOG,'P_AMT_DUE_LOW'||P_AMT_DUE_LOW);
fnd_file.put_line(FND_FILE.LOG,'P_AMT_DUE_HIGH'||P_AMT_DUE_HIGH);
fnd_file.put_line(FND_FILE.LOG,'p_invoice_type_low'||p_invoice_type_low);
fnd_file.put_line(FND_FILE.LOG,'p_invoice_type_high'||p_invoice_type_high);
SELECT 'Aging_Output'
-- ||xx_file_name.nextval
||'.txt'
INTO vfilename
FROM dual;
file_id:=utl_file.fopen('/oracle/edi/DEV/OUTBOUND',vfilename,'W');
FOR i IN c1
LOOP
--vtext:=i.customer_name_inv||'~'||i.customer_number_inv||'~'||i.cust_state_inv||'~'||i.invnum||'~'||i.invoice_type_inv||'~'||i.due_date_inv||'~'||i.amt_due_remaining_inv;
vtext:=i.customer_name_inv||'~'||i.customer_number_inv||'~'||i.cust_state_inv||'~'||i.invnum||'~'||i.invoice_type_inv||'~'||i.due_date_inv||'~'||i.amt_due_remaining_inv||'~'||i.b0_inv||'~'||i.b1_inv||'~'||i.b2_inv||'~'||i.b3_inv;
utl_file.put_line(file_id,vtext);
--v_customer_name_inv:=i.customer_name_inv;
END LOOP;
utl_file.fclose(file_id);
fnd_file.put_line(FND_FILE.OUTPUT,'In the log file'||vfilename);
--fnd_file.put_line(FND_FILE.OUTPUT,'v_customer_name_inv'||v_customer_name_inv);
--fnd_file.put_line ('File in the path exists' ||vfilename );
/*EXCEPTION
WHEN UTL_FILE.invalid_path THEN
fnd_file.put_line ('the path does not exists' || SQLERRM);
WHEN UTL_FILE.invalid_operation THEN
fnd_file.put_line ('the operation is not valid' || SQLERRM);*/
COMMIT;
END;
END;
package spec
CREATE OR REPLACE PACKAGE XXRS_AGING_4_BUCKETS_PKG
AS
PROCEDURE XXRS_AGING_4_BUCKETS_PROC(
errbuff OUT VARCHAR2,
retcode OUT NUMBER,
P_REPORTING_LEVEL IN VARCHAR2,
P_REPORTING_ENTITY_ID IN VARCHAR2,
--p_ca_set_of_books_id number,
P_COA IN VARCHAR2,
P_BAL_SEGMENT_LOW IN VARCHAR2,
P_BAL_SEGMENT_HIGH IN VARCHAR2,
P_AS_OF_DATE IN VARCHAR2,
p_order_by IN VARCHAR2,
P_SUMMARY_OPTION_LOW IN VARCHAR2,
P_FORMAT_OPTION_LOW IN VARCHAR2,
P_BUCKET_TYPE_LOW IN VARCHAR2,
P_CREDIT_OPTION IN VARCHAR2,
p_risk_option IN VARCHAR2,
p_curr_code IN VARCHAR2,
p_customer_name_low IN VARCHAR2,
p_customer_name_high IN VARCHAR2,
p_customer_number_low IN VARCHAR2,
p_customer_number_high IN VARCHAR2,
P_AMT_DUE_LOW IN VARCHAR2,
P_AMT_DUE_HIGH IN VARCHAR2,
p_invoice_type_low IN VARCHAR2,
p_invoice_type_high IN VARCHAR2);
END;
package body
CREATE OR REPLACE PACKAGE BODY XXRS_AGING_4_BUCKETS_PKG
AS
PROCEDURE XXRS_AGING_4_BUCKETS_PROC(
--XXRS_AGING_4_BUCKETS_PKG.XXRS_AGING_4_BUCKETS_PROC
errbuff OUT VARCHAR2,
retcode OUT NUMBER,
P_REPORTING_LEVEL IN VARCHAR2,
P_REPORTING_ENTITY_ID IN VARCHAR2,
--p_ca_set_of_books_id number,
P_COA IN VARCHAR2,
P_BAL_SEGMENT_LOW IN VARCHAR2,
P_BAL_SEGMENT_HIGH IN VARCHAR2,
P_AS_OF_DATE IN VARCHAR2,
p_order_by IN VARCHAR2,
P_SUMMARY_OPTION_LOW IN VARCHAR2,
P_FORMAT_OPTION_LOW IN VARCHAR2,
P_BUCKET_TYPE_LOW IN VARCHAR2,
P_CREDIT_OPTION IN VARCHAR2,
p_risk_option IN VARCHAR2,
p_curr_code IN VARCHAR2,
p_customer_name_low IN VARCHAR2,
p_customer_name_high IN VARCHAR2,
p_customer_number_low IN VARCHAR2,
p_customer_number_high IN VARCHAR2,
P_AMT_DUE_LOW IN VARCHAR2,
P_AMT_DUE_HIGH IN VARCHAR2,
p_invoice_type_low IN VARCHAR2,
p_invoice_type_high IN VARCHAR2)
AS
rp_convert_flag VARCHAR2(50);
func_curr_precision VARCHAR2(50);
functional_currency VARCHAR2(50);
rp_bucket_line_type_0 VARCHAR2(50);
rp_bucket_days_from_0 VARCHAR2(20);
rp_bucket_days_from_1 VARCHAR2(20);
rp_bucket_days_to_1 VARCHAR2(20);
rp_bucket_category VARCHAR2(50);
rp_bucket_days_to_0 VARCHAR2(20);
rp_bucket_line_type_1 DATE;
rp_bucket_days_to_2 VARCHAR2(20);
rp_short_unid_phrase VARCHAR2(50);
c_claim_meaning VARCHAR2(50);
c_payment_meaning VARCHAR2(50);
rp_bucket_line_type_2 VARCHAR2(50);
rp_bucket_days_from_2 VARCHAR2(20);
rp_bucket_line_type_3 VARCHAR2(50);
rp_bucket_days_from_3 VARCHAR2(20);
rp_bucket_days_to_3 VARCHAR2(20);
c_risk_meaning VARCHAR2(50);
--fnd_date.canonical_to_date(P_AS_OF_DATE) VARCHAR2(50);
--v_customer_name_inv VARCHAR2(150);
CURSOR c1
IS
SELECT ps.org_id invoice_org_id,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, types.cust_trx_type_id ) dummy_id_inv,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL,types.name) dummy_type_inv,
/* bug2820114 Unidentified payment must be at end of report. */
DECODE(party.party_name, NULL , '2', rtrim(rpad(substrb(
party.party_name,1,50),36) ) ) customer_name_inv,
cust_acct.cust_account_id customer_id_inv,
cust_acct.account_number customer_number_inv,
DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) contact_site_id_inv,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.state,2))) cust_state_inv ,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.city,25))) cust_city_inv ,
DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) addr_id_inv ,
rtrim(rpad(trx.purchase_order,12)) reference_number ,
ps.payment_schedule_id payment_sched_id_inv,
ps.class class_inv,
ps.due_date due_date_inv ,
DECODE( rp_convert_flag, 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining ) amt_due_remaining_inv ,
DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) amt_due_original_inv ,
ps.trx_number invnum ,
types.name invoice_type_inv,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date) days_past_due_inv,
ps.amount_adjusted amount_adjusted_inv,
ps.amount_applied amount_applied_inv,
ps.amount_credited amount_credited_inv,
ps.gl_date gl_date_inv,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) data_converted_inv,
NVL(ps.exchange_rate, 1) ps_exchange_rate_inv,
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36)) customer_name_inv_dsp
FROM ra_cust_trx_types_all types,
hz_cust_accounts cust_acct,
hz_parties party,
--** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
--** -- lp_ra_customer_trx_all trx,
RA_CUSTOMER_TRX_ALL trx, --SV
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
ra_cust_trx_line_gl_dist_all gld,
/* Enhancement 7346488.
xla_distribution_links lk,
xla_ae_lines ae, */
gl_code_combinations cc
WHERE TRUNC(ps.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.customer_trx_id+0 = trx.customer_trx_id
AND ps.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)--SV
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND ps.cust_trx_type_id = types.cust_trx_type_id
-- bug1960831
AND NVL(ps.org_id,-99) = NVL(types.org_id,-99)
AND types.name LIKE NVL(p_invoice_type_low,types.name)--SV
AND types.name LIKE NVL(p_invoice_type_high,types.name)
--** -- lp_invoice_type_low
--** -- lp_invoice_type_high
AND ps.customer_site_use_id+0 = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id (+) = party_site.location_id
AND ps.gl_date_closed > fnd_date.canonical_to_date(P_AS_OF_DATE)
--** -- lp_curr_code
AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
AND ps.customer_trx_id+0 = gld.customer_trx_id
AND gld.account_class = 'REC'
AND gld.latest_rec_flag = 'Y'
/* Enhancement 7346488.
and gld.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1 (+)
and lk.source_distribution_type (+) = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
and lk.application_id (+) = 222
and ae.application_id (+) = 222
and lk.ae_header_id = ae.ae_header_id (+)
and lk.ae_line_num = ae.ae_line_num (+)
and decode(lk.accounting_line_code, 'CM_EXCH_GAIN_LOSS', 'N',
'AUTO_GEN_GAIN_LOSS', 'N', 'Y') = 'Y'
and decode(ae.ledger_id,'',decode(gld.posting_control_id,-3,-999999,gld.code_combination_id),gld.set_of_books_id,ae.code_combination_id,-999999)= cc.code_combination_id
*/
AND gld.code_combination_id = cc.code_combination_id
--** -- lp_bal_segment_low-doubt
--** -- lp_bal_segment_high--doubt
-- Bug# 1233291
-- and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
-- AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id)--doubt
--** -- P_ORG_WHERE_CTT
--** -- P_ORG_WHERE_PS
-- --** -- P_ORG_WHERE_TRX
-- --** -- P_ORG_WHERE_SITE
--** -- P_ORG_WHERE_ADDR
--** -- P_ORG_WHERE_GLD
UNION ALL
SELECT ps.org_id,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, -999) ,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning) ),
/* bug2820114 Unidentified payment must be at end of report */
DECODE(party.party_name , NULL , '2' , rtrim(rpad(substrb(
party.party_name,1,50),36))),
NVL(cust_acct.cust_account_id, -999),
cust_acct.account_number,
DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) ,
DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.state,1,2)) ,
DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.city,1,25)) ,
DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) ,
NULL,
ps.payment_schedule_id,
ps.class,
ps.due_date,
-SUM ( DECODE( rp_convert_flag, 'Y', app.acctd_amount_applied_from, app.amount_applied ) ),
ps.amount_due_original,
ps.trx_number invnum ,
DECODE (UPPER(p_order_by),'XXX',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning)) ,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)),
NVL(ps.exchange_rate, 1),
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
--** -- lp_bal_segment bal_segment_value_inv,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
FROM --** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
ar_cons_inv_all, --SV
--** -- lp_ar_receivable_apps_all app,
AR_RECEIVABLE_APPLICATIONS_ALL app, --SV
gl_code_combinations cc,
hz_cust_accounts cust_acct,
hz_parties party,
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc
WHERE app.gl_date+0 <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.customer_id = cust_acct.cust_account_id(+)
AND cust_acct.party_id = party.party_id(+)
/* Bug fix 5376284
Replacing 2 parameters with 1 parameters */
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND ps.cash_receipt_id +0 = app.cash_receipt_id
AND ps.customer_site_use_id+0 = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND app.code_combination_id = cc.code_combination_id
AND app.status IN ( 'ACC', 'UNAPP', 'UNID', 'OTHER ACC')
AND NVL(app.confirmed_flag, 'Y') = 'Y'
AND ps.gl_date_closed > fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ((app.reversal_gl_date IS NOT NULL
AND ps.gl_date <= fnd_date.canonical_to_date(P_AS_OF_DATE))
OR app.reversal_gl_date IS NULL )
--** -- lp_curr_code
AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
AND NVL( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
-- Bug# 1233291
-- and app.receivable_application_id+0 < :rp_app_max_id
-- and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
--AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id) SV doubt
--** -- p_org_where_ps
--** -- p_org_where_app
-- --** -- p_org_where_site
--** -- p_org_where_addr
GROUP BY ps.org_id,
party.party_name,
site.site_use_id,
loc.state,
loc.city,
addr.cust_acct_site_id,
cust_acct.cust_account_id,
cust_acct.account_number,
ps.payment_schedule_id,
ps.class,
ps.due_date,
ps.amount_due_original,
ps.trx_number,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
--** -- lp_bal_segment ,
DECODE( app.status, 'UNID', 'UNID', 'UNAPP'),
--** -- lp_query_show_bill,
app.applied_payment_schedule_id--10,444 records
UNION ALL
SELECT ps.org_id,
DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, -999) ,
DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, c_risk_meaning) ,
/* bug2820114 Unidentified payment must be at end of report */
DECODE(party.party_name, NULL, '2' , rtrim(rpad(substrb(
party.party_name,1,50),36) )) ,
cust_acct.cust_account_id ,
cust_acct.account_number ,
DECODE(UPPER(p_format_option_low),'B', 0, site.site_use_id) ,
DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.state,2))) ,
DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.city,25))) ,
DECODE(UPPER(p_format_option_low),'B', 0, addr.cust_acct_site_id ) ,
NULL ,
ps.payment_schedule_id ,
DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
ps.due_date ,
DECODE( rp_convert_flag, 'Y', crh.acctd_amount , crh.amount ) ,
DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) ,
ps.trx_number ,
DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
ps.amount_adjusted ,
ps.amount_applied ,
ps.amount_credited ,
crh.gl_date ,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) ,
NVL(ps.exchange_rate, 1) ,
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b3_inv,
--** -- lp_bal_segment ,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
FROM hz_cust_accounts cust_acct,
hz_parties party,
--** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
ar_cons_inv_all, --SV
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
--** -- lp_ar_cash_receipts_all cr,
AR_CASH_RECEIPTS_ALL cr, --SV
--** -- lp_ar_cash_receipt_history_all crh,
AR_CASH_RECEIPT_HISTORY_ALL crh, --SV
gl_code_combinations cc
WHERE TRUNC(crh.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.trx_number IS NOT NULL
AND upper(p_risk_option) != 'NONE'
AND ps.customer_id = cust_acct.cust_account_id(+)
AND cust_acct.party_id = party.party_id(+)
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
AND ps.cash_receipt_id = cr.cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.account_code_combination_id = cc.code_combination_id
AND ps.customer_site_use_id = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
--** -- lp_curr_code
AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
AND ( crh.current_record_flag = 'Y'
OR crh.reversal_gl_date > fnd_date.canonical_to_date(P_AS_OF_DATE) )
AND crh.status NOT IN ( DECODE(crh.factor_flag, 'Y','RISK_ELIMINATED', 'N','CLEARED'), 'REVERSED')
/* Bug 4127480 : exclude receipts applied to short term debt */
AND NOT EXISTS
(SELECT 'x'
FROM --** -- lp_ar_receivable_apps_all ra
AR_RECEIVABLE_APPLICATIONS_ALL ra --SV
WHERE ra.cash_receipt_id = cr.cash_receipt_id
AND ra.status = 'ACTIVITY'
AND applied_payment_schedule_id = -2
)--10,444 records
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
--Bug 2124653
--and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
--AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id) doubt
--** -- p_org_where_ps
-- --** -- p_org_where_site
--** -- p_org_where_addr
--** -- p_org_where_cr
--** -- p_org_where_crh
UNION ALL
SELECT ps.org_id invoice_org_id,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, types.cust_trx_type_id ) dummy_id_inv,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL,types.name) dummy_type_inv,
/* bug2820114 Unidentified payment must be at end of report. */
DECODE(party.party_name, NULL , '2', rtrim(rpad(substrb(
party.party_name,1,50),36) ) ) customer_name_inv,
cust_acct.cust_account_id customer_id_inv,
cust_acct.account_number customer_number_inv,
DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) contact_site_id_inv,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.state,2))) cust_state_inv ,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.city,25))) cust_city_inv ,
DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) addr_id_inv ,
rtrim(rpad(trx.purchase_order,12)) reference_number ,
ps.payment_schedule_id payment_sched_id_inv,
ps.class class_inv,
ps.due_date due_date_inv ,
DECODE( rp_convert_flag, 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining ) amt_due_remaining_inv ,
DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) amt_due_original_inv ,
ps.trx_number invnum ,
types.name invoice_type_inv,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date) days_past_due_inv,
ps.amount_adjusted amount_adjusted_inv,
ps.amount_applied amount_applied_inv,
ps.amount_credited amount_credited_inv,
ps.gl_date gl_date_inv,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) data_converted_inv,
NVL(ps.exchange_rate, 1) ps_exchange_rate_inv,
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
--** -- lp_bal_segment bal_segment_value_inv,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36)) customer_name_inv_dsp
FROM ra_cust_trx_types_all types,
hz_cust_accounts cust_acct,
hz_parties party,
--** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
--** -- lp_ra_customer_trx_all trx,
RA_CUSTOMER_TRX_ALL trx, --SV
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
ra_cust_trx_line_gl_dist_all gld,
/* Enhancement 7346488.
xla_distribution_links lk,
xla_ae_lines ae, */
gl_code_combinations cc
WHERE TRUNC(ps.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.customer_trx_id+0 = trx.customer_trx_id
AND ps.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
AND ps.cust_trx_type_id = types.cust_trx_type_id
-- bug1960831
AND NVL(ps.org_id,-99) = NVL(types.org_id,-99)
--** -- lp_invoice_type_low
--** -- lp_invoice_type_high
AND types.name LIKE NVL(p_invoice_type_low,types.name)
AND types.name LIKE NVL(p_invoice_type_high,types.name)
AND ps.customer_site_use_id+0 = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id (+) = party_site.location_id
AND ps.gl_date_closed > fnd_date.canonical_to_date(P_AS_OF_DATE)
--** -- lp_curr_code
AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
AND ps.customer_trx_id+0 = gld.customer_trx_id
AND gld.account_class = 'REC'
AND gld.latest_rec_flag = 'Y'
/* Enhancement 7346488.
and gld.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1 (+)
and lk.source_distribution_type (+) = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
and lk.application_id (+) = 222
and ae.application_id (+) = 222
and lk.ae_header_id = ae.ae_header_id (+)
and lk.ae_line_num = ae.ae_line_num (+)
and decode(lk.accounting_line_code, 'CM_EXCH_GAIN_LOSS', 'N',
'AUTO_GEN_GAIN_LOSS', 'N', 'Y') = 'Y'
and decode(ae.ledger_id,'',decode(gld.posting_control_id,-3,-999999,gld.code_combination_id),gld.set_of_books_id,ae.code_combination_id,-999999)= cc.code_combination_id
*/
AND gld.code_combination_id = cc.code_combination_id
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
-- Bug# 1233291
-- and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
-- AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id); doubt
--** -- P_ORG_WHERE_CTT
--** -- P_ORG_WHERE_PS
-- --** -- P_ORG_WHERE_TRX
-- --** -- P_ORG_WHERE_SITE
--** -- P_ORG_WHERE_ADDR
--** -- P_ORG_WHERE_GLD
UNION ALL
SELECT ps.org_id,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, -999) ,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning) ),
/* bug2820114 Unidentified payment must be at end of report */
DECODE(party.party_name , NULL , '2' , rtrim(rpad(substrb(
party.party_name,1,50),36))),
NVL(cust_acct.cust_account_id, -999),
cust_acct.account_number,
DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) ,
DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.state,1,2)) ,
DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.city,1,25)) ,
DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) ,
NULL,
ps.payment_schedule_id,
ps.class,
ps.due_date,
-SUM ( DECODE( rp_convert_flag, 'Y', app.acctd_amount_applied_from, app.amount_applied ) ),
ps.amount_due_original,
ps.trx_number invnum ,
DECODE (UPPER(p_order_by),'XXX',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning)) ,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)),
NVL(ps.exchange_rate, 1),
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
--** -- lp_bal_segment bal_segment_value_inv,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
FROM --** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
ar_cons_inv_all, --SV
--** -- lp_ar_receivable_apps_all app,
AR_RECEIVABLE_APPLICATIONS_ALL app, --SV
gl_code_combinations cc,
hz_cust_accounts cust_acct,
hz_parties party,
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc
WHERE app.gl_date+0 <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.customer_id = cust_acct.cust_account_id(+)
AND cust_acct.party_id = party.party_id(+)
/* Bug fix 5376284
Replacing 2 parameters with 1 parameters */
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
AND ps.cash_receipt_id +0 = app.cash_receipt_id
AND ps.customer_site_use_id+0 = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND app.code_combination_id = cc.code_combination_id
AND app.status IN ( 'ACC', 'UNAPP', 'UNID', 'OTHER ACC')
AND NVL(app.confirmed_flag, 'Y') = 'Y'
AND ps.gl_date_closed > fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ((app.reversal_gl_date IS NOT NULL
AND ps.gl_date <= fnd_date.canonical_to_date(P_AS_OF_DATE))
OR app.reversal_gl_date IS NULL )
--** -- lp_curr_code
AND NVL( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
-- Bug# 1233291
-- and app.receivable_application_id+0 < :rp_app_max_id
-- and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
--** -- p_org_where_ps
--** -- p_org_where_app
-- --** -- p_org_where_site
--** -- p_org_where_addr
GROUP BY ps.org_id,
party.party_name,
site.site_use_id,
loc.state,
loc.city,
addr.cust_acct_site_id,
cust_acct.cust_account_id,
cust_acct.account_number,
ps.payment_schedule_id,
ps.class,
ps.due_date,
ps.amount_due_original,
ps.trx_number,
ps.amount_adjusted,
ps.amount_applied,
ps.amount_credited,
ps.gl_date,
ps.amount_in_dispute,
ps.amount_adjusted_pending,
ps.invoice_currency_code,
ps.exchange_rate,
--** -- lp_bal_segment ,
DECODE( app.status, 'UNID', 'UNID', 'UNAPP'),
--** -- lp_query_show_bill,
app.applied_payment_schedule_id--10,444 records
UNION ALL
SELECT ps.org_id,
DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, -999) ,
DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, c_risk_meaning) ,
/* bug2820114 Unidentified payment must be at end of report */
DECODE(party.party_name, NULL, '2' , rtrim(rpad(substrb(
party.party_name,1,50),36) )) ,
cust_acct.cust_account_id ,
cust_acct.account_number ,
DECODE(UPPER(p_format_option_low),'B', 0, site.site_use_id) ,
DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.state,2))) ,
DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.city,25))) ,
DECODE(UPPER(p_format_option_low),'B', 0, addr.cust_acct_site_id ) ,
NULL ,
ps.payment_schedule_id ,
DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
ps.due_date ,
DECODE( rp_convert_flag, 'Y', crh.acctd_amount , crh.amount ) ,
DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) ,
ps.trx_number ,
DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
ps.amount_adjusted ,
ps.amount_applied ,
ps.amount_credited ,
crh.gl_date ,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) ,
NVL(ps.exchange_rate, 1) ,
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b3_inv,
--** -- lp_bal_segment ,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
FROM hz_cust_accounts cust_acct,
hz_parties party,
--** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
ar_cons_inv_all, --SV
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
--** -- lp_ar_cash_receipts_all cr,
AR_CASH_RECEIPTS_ALL cr, --SV
--** -- lp_ar_cash_receipt_history_all crh,
AR_CASH_RECEIPT_HISTORY_ALL crh, --SV
gl_code_combinations cc
WHERE TRUNC(crh.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.trx_number IS NOT NULL
AND upper(p_risk_option) != 'NONE'
AND ps.customer_id = cust_acct.cust_account_id(+)
AND cust_acct.party_id = party.party_id(+)
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
AND ps.cash_receipt_id = cr.cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.account_code_combination_id = cc.code_combination_id
AND ps.customer_site_use_id = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
--** -- lp_curr_code
AND ( crh.current_record_flag = 'Y'
OR crh.reversal_gl_date > fnd_date.canonical_to_date(P_AS_OF_DATE) )
AND crh.status NOT IN ( DECODE(crh.factor_flag, 'Y','RISK_ELIMINATED', 'N','CLEARED'), 'REVERSED')
/* Bug 4127480 : exclude receipts applied to short term debt */
AND NOT EXISTS
(SELECT 'x'
FROM --** -- lp_ar_receivable_apps_all ra
AR_RECEIVABLE_APPLICATIONS_ALL ra --SV
WHERE ra.cash_receipt_id = cr.cash_receipt_id
AND ra.status = 'ACTIVITY'
AND applied_payment_schedule_id = -2
)--10,444 records
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
--Bug 2124653
--and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
--** -- p_org_where_ps
-- --** -- p_org_where_site
--** -- p_org_where_addr
--** -- p_org_where_cr
--** -- p_org_where_crh
UNION ALL
--------------------------
-- Bills Receivable
-----------------
SELECT ps.org_id,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL, types.cust_trx_type_id ) dummy_id_inv,
DECODE (UPPER(p_order_by),'CUSTOMER',NULL,types.name) dummy_type_inv,
/* bug2820114 Unidentified payment must be at end of report */
DECODE(party.party_name , NULL , '2' , rtrim(rpad(substrb(
party.party_name,1,50),36)) ) customer_name_inv,
cust_acct.cust_account_id customer_id_inv,
cust_acct.account_number customer_number_inv,
DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) contact_site_id_inv,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.state,2))) cust_state_inv ,
DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.city,25))) cust_city_inv ,
DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) addr_id_inv ,
NULL ,
ps.payment_schedule_id payment_sched_id_inv,
ps.class class_inv,
ps.due_date due_date_inv ,
DECODE( rp_convert_flag, 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining ) amt_due_remaining_inv ,
DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) amt_due_original_inv ,
ps.trx_number invnum ,
types.name invoice_type_inv,
ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
ps.amount_adjusted amount_adjusted_inv,
ps.amount_applied amount_applied_inv,
ps.amount_credited amount_credited_inv,
ps.gl_date gl_date_inv,
DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) data_converted_inv,
NVL(ps.exchange_rate, 1) ps_exchange_rate_inv,
DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
--** -- lp_bal_segment bal_segment_value_inv,
--** -- lp_query_show_bill cons_billing_number,
rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
FROM ra_cust_trx_types_all types,
hz_cust_accounts cust_acct,
hz_parties party,
--** -- lp_ar_payment_schedules_all ps,
AR_PAYMENT_SCHEDULES_ALL ps, --SV
--** -- lp_table_show_bill
ar_cons_inv_all, --SV
--** -- lp_ra_customer_trx_all trx,
RA_CUSTOMER_TRX_ALL trx, --SV
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
ar_transaction_history_all th,
--** -- lp_ar_distributions_all dist,
AR_DISTRIBUTIONS_ALL dist, --SV
gl_code_combinations cc
WHERE TRUNC(ps.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
AND ps.class = 'BR'
AND ps.customer_trx_id+0 = trx.customer_trx_id
AND ps.customer_id = cust_acct.cust_account_id
AND cust_acct.party_id =party.party_id
--** -- lp_customer_name_low1
--** -- lp_customer_name_high1
--** -- lp_customer_number_low1
--** -- lp_customer_number_high1
AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
AND ps.cust_trx_type_id = types.cust_trx_type_id
-- bug1960831
AND NVL(ps.org_id ,-99) = NVL(types.org_id,-99)
--** -- lp_invoice_type_low
--** -- lp_invoice_type_high
AND ps.customer_site_use_id+0 = site.site_use_id(+)
AND site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND addr.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND ps.gl_date_closed > fnd_date.canonical_to_date(P_AS_OF_DATE)
--** -- lp_curr_code
AND ps.customer_trx_id+0 = th.customer_trx_id
--4147868 and th.current_accounted_flag = 'Y'
AND th.transaction_history_id = dist.source_id
AND dist.source_table = 'TH'
--4147868 and dist.source_type = 'REC'
/*4147868 : Added following code */
AND th.transaction_history_id =
(SELECT MAX(transaction_history_id)
FROM ar_transaction_history_all th2,
--** -- lp_ar_distributions_all dist2
AR_DISTRIBUTIONS_ALL dist2 --SV
WHERE th2.transaction_history_id = dist2.source_id
AND dist2.source_table = 'TH'
AND th2.gl_date <= fnd_date.canonical_to_date(P_AS_OF_DATE)
/*GSCC Compliance*/
AND dist2.amount_dr IS NOT NULL
AND th2.customer_trx_id = ps.customer_trx_id
)
AND dist.amount_dr IS NOT NULL
/*4147868 end*/
AND dist.source_table_secondary IS NULL
AND dist.code_combination_id = cc.code_combination_id
--** -- lp_bal_segment_low
--** -- lp_bal_segment_high
-- Bug# 1233291
-- and ps.payment_schedule_id+0 < :rp_ps_max_id
--** -- lp_where_show_bill
--** -- P_ORG_WHERE_CTT
--** -- P_ORG_WHERE_PS
-- --** -- P_ORG_WHERE_TRX
-- --** -- P_ORG_WHERE_SITE
--** -- P_ORG_WHERE_ADDR
--** -- lp_order_by_inv
ORDER BY 4,17;
vtext VARCHAR2(2000);
vfilename VARCHAR2(2000);
v_p_order_by VARCHAR2(2000);
file_id utl_file.file_type;
BEGIN
fnd_global.APPS_INITIALIZE(1151,20678,222);
--fnd_date.canonical_to_date(P_AS_OF_DATE) :=fnd_date.canonical_to_date(P_AS_OF_DATE);
fnd_file.put_line(FND_FILE.LOG,'P_REPORTING_LEVEL'||P_REPORTING_LEVEL);
fnd_file.put_line(FND_FILE.LOG,'P_REPORTING_ENTITY_ID'||P_REPORTING_ENTITY_ID);
fnd_file.put_line(FND_FILE.LOG,'P_COA'||P_COA);
fnd_file.put_line(FND_FILE.LOG,'P_BAL_SEGMENT_LOW'||P_BAL_SEGMENT_LOW);
fnd_file.put_line(FND_FILE.LOG,'P_BAL_SEGMENT_HIGH'||P_BAL_SEGMENT_HIGH);
fnd_file.put_line(FND_FILE.LOG,'fnd_date.canonical_to_date(P_AS_OF_DATE)'||fnd_date.canonical_to_date(P_AS_OF_DATE));
fnd_file.put_line(FND_FILE.LOG,'p_order_by'||p_order_by);
fnd_file.put_line(FND_FILE.LOG,'P_SUMMARY_OPTION_LOW'||P_SUMMARY_OPTION_LOW);
fnd_file.put_line(FND_FILE.LOG,'P_FORMAT_OPTION_LOW'||P_FORMAT_OPTION_LOW);
fnd_file.put_line(FND_FILE.LOG,'P_BUCKET_TYPE_LOW'||P_BUCKET_TYPE_LOW);
fnd_file.put_line(FND_FILE.LOG,'P_CREDIT_OPTION'||P_CREDIT_OPTION);
fnd_file.put_line(FND_FILE.LOG,'p_risk_option'||p_risk_option);
fnd_file.put_line(FND_FILE.LOG,'p_curr_code'||p_curr_code);
fnd_file.put_line(FND_FILE.LOG,'p_customer_name_low'||p_customer_name_low);
fnd_file.put_line(FND_FILE.LOG,'p_customer_name_high'||p_customer_name_high);
fnd_file.put_line(FND_FILE.LOG,'p_customer_number_low'||p_customer_number_low);
fnd_file.put_line(FND_FILE.LOG,'p_customer_number_high'||p_customer_number_high);
fnd_file.put_line(FND_FILE.LOG,'P_AMT_DUE_LOW'||P_AMT_DUE_LOW);
fnd_file.put_line(FND_FILE.LOG,'P_AMT_DUE_HIGH'||P_AMT_DUE_HIGH);
fnd_file.put_line(FND_FILE.LOG,'p_invoice_type_low'||p_invoice_type_low);
fnd_file.put_line(FND_FILE.LOG,'p_invoice_type_high'||p_invoice_type_high);
SELECT 'Aging_Output'
-- ||xx_file_name.nextval
||'.txt'
INTO vfilename
FROM dual;
file_id:=utl_file.fopen('/oracle/edi/DEV/OUTBOUND',vfilename,'W');
FOR i IN c1
LOOP
--vtext:=i.customer_name_inv||'~'||i.customer_number_inv||'~'||i.cust_state_inv||'~'||i.invnum||'~'||i.invoice_type_inv||'~'||i.due_date_inv||'~'||i.amt_due_remaining_inv;
vtext:=i.customer_name_inv||'~'||i.customer_number_inv||'~'||i.cust_state_inv||'~'||i.invnum||'~'||i.invoice_type_inv||'~'||i.due_date_inv||'~'||i.amt_due_remaining_inv||'~'||i.b0_inv||'~'||i.b1_inv||'~'||i.b2_inv||'~'||i.b3_inv;
utl_file.put_line(file_id,vtext);
--v_customer_name_inv:=i.customer_name_inv;
END LOOP;
utl_file.fclose(file_id);
fnd_file.put_line(FND_FILE.OUTPUT,'In the log file'||vfilename);
--fnd_file.put_line(FND_FILE.OUTPUT,'v_customer_name_inv'||v_customer_name_inv);
--fnd_file.put_line ('File in the path exists' ||vfilename );
/*EXCEPTION
WHEN UTL_FILE.invalid_path THEN
fnd_file.put_line ('the path does not exists' || SQLERRM);
WHEN UTL_FILE.invalid_operation THEN
fnd_file.put_line ('the operation is not valid' || SQLERRM);*/
COMMIT;
END;
END;
No comments:
Post a Comment