[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select report_heading1,
report_heading2
from ar_aging_bucket_lines lines,
ar_aging_buckets buckets
where lines.aging_bucket_id = buckets.aging_bucket_id
and UPPER(buckets.bucket_name) = UPPER(pg_in_bucket_type_low)
and NVL(buckets.status,'A') = 'A'
order by lines.bucket_sequence_num;
SELECT parent_request_id
INTO l_request_id
FROM fnd_concurrent_requests child
WHERE child.request_id = p_request_id;
select RTRIM(RPAD(substrb(party.person_first_name,1,40), 1)) ||
decode( substrb(party.person_first_name,1,40),
NULL, NULL,
decode( substrb(party.person_last_name,1,50),
NULL, NULL,
'. ' )) ||
RTRIM(RPAD( substrb(party.person_last_name,1,50), 17)),
cont_point.phone_area_code ||
' ' ||
RTRIM(RPAD( decode(cont_point.contact_point_type,
'TLX', cont_point.telex_number,
cont_point.phone_number) , 15)),
decode(substrb(party.person_first_name,1,40),
NULL, decode( substrb(party.person_last_name,1,50),
NULL, decode( cont_point.phone_area_code,
NULL, NULL,
'Y' ),
decode( decode(cont_point.contact_point_type,
'TLX', cont_point.telex_number,
cont_point.phone_number),
NULL, NULL,
'Y' ),
'Y' ),
'Y')
into pg_temp_contact_name,
pg_temp_contact_phone,
pg_temp_contacts
from hz_cust_account_roles acct_role,
hz_parties party,
hz_relationships rel,
hz_contact_points cont_point,
hz_cust_account_roles car,
hz_cust_site_uses site_uses
where site_uses.site_use_id = p_site_use_id
and site_uses.cust_acct_site_id = acct_role.cust_acct_site_id(+)
and acct_role.party_id = rel.party_id(+)
and rel.subject_table_name(+) = 'HZ_PARTIES'
and rel.object_table_name(+) = 'HZ_PARTIES'
and rel.directional_flag(+) = 'F'
and acct_role.role_type = 'CONTACT'
and rel.subject_id = party.party_id(+)
and acct_role.cust_account_role_id = car.cust_account_role_id(+)
and car.party_id = cont_point.owner_table_id(+)
and cont_point.owner_table_name(+) = 'HZ_PARTIES'
and NVL(cont_point.contact_point_type(+),'N') not in ('EDI','EMAIL','WEB')
and nvl( nvl(cont_point.phone_line_type(+),
cont_point.contact_point_type(+)), 'GEN') = 'GEN'
and nvl(acct_role.status,'A') = 'A'
and nvl(cont_point.status(+),'A') = 'A'
and rownum = 1;
select customer_id,
customer_number,
short_customer_name customer_name,
sort_field1,
inv_tid_inv,
contact_site_id,
customer_state,
customer_city,
cust_acct_site_id,
sum(customer_amount ) customer_total,
sum( risk_amount ) risk_total,
sum( pmt_amount ) pmt_total,
sum( cm_amount ) cm_total,
sum( claim_amount ) claim_total,
sum( inv_amount ) inv_total,
data_converted_flag,
SUM( bucket_0 ) b_0,
SUM( bucket_1 ) b_1,
SUM( bucket_2 ) b_2,
SUM( bucket_3 ) b_3,
SUM( bucket_4 ) b_4,
SUM( bucket_5 ) b_5,
SUM( bucket_6 ) b_6,
bal_segment_value,
contact_name,
contact_phone
from
( select
ext.customer_id,
ext.customer_number,
ext.short_customer_name,
NVL(mfar.sort_field1,ext.sort_field1) sort_field1,
ext.sort_field2,
ext.inv_tid_inv,
ext.contact_site_id,
ext.customer_state,
ext.customer_city,
ext.cust_acct_site_id,
mfar.rec_aging_amount,
ext.amt_due_remaining,
nvl(mfar.rec_aging_amount,ext.amt_due_remaining) customer_amount,
decode(class,'''|| pg_risk_meaning ||''',nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0) risk_amount,
decode(class,''PMT'',nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0) pmt_amount,
decode(class,''CM'',nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0) cm_amount,
decode(class,''CLAIM'',nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0) claim_amount,
decode(class,'''|| pg_risk_meaning ||''',0,
''PMT'',0,
''CM'',0,
''CLAIM'',0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) inv_amount,
ext.data_converted_flag,
ext.exchange_rate,
DECODE(bucket_0,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE( '''|| pg_risk_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''PMT'', DECODE( '''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CM'', DECODE( '''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_0,
DECODE(bucket_1,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_1,
DECODE(bucket_2,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_2,
DECODE(bucket_3,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_3,
DECODE(bucket_4,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_4,
DECODE(bucket_5,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_5,
DECODE(bucket_6,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
nvl(mfar.rec_aging_amount,ext.amt_due_remaining),0),
nvl(mfar.rec_aging_amount,ext.amt_due_remaining) )) bucket_6,
DECODE(mfar.bal_segment_value,null,ext.bal_segment_value,
mfar.bal_segment_value) bal_segment_value,
ext.contact_name,
ext.contact_phone
from ar_aging_extract ext,
( select mfar.*,
'||pg_accounting_flexfield||' sort_field1,
'||pg_acct_flex_bal_seg||' bal_segment_value
from ar_aging_mfar_extract mfar,
gl_code_combinations c
where c.code_combination_id = mfar.code_combination_id
) mfar
where ext.parent_request_id = mfar.parent_request_id(+)
and ext.payment_schedule_id = mfar.payment_schedule_id(+)
'||l_filter_criteria||'
)
group by customer_id,
customer_number,
short_customer_name,
sort_field1,
inv_tid_inv,
contact_site_id,
customer_state,
customer_city,
cust_acct_site_id,
data_converted_flag,
contact_name,
contact_phone,
bal_segment_value,
rec_aging_amount
having sum(nvl(rec_aging_amount,amt_due_remaining)) <> 0';
SELECT ext.customer_id,
ext.customer_number,
ext.short_customer_name customer_name,
NVL(mfar.sort_field1,ext.sort_field1) sort_field1,
ext.sort_field2,
ext.inv_tid_inv,
ext.contact_site_id,
ext.customer_state,
ext.customer_city,
ext.cust_acct_site_id,
ext.payment_schedule_id,
ext.class,
TO_CHAR(ext.due_date,''YYYY-MM-DD'') due_date,
nvl(mfar.rec_aging_amount,ext.amt_due_remaining) amt_due_remaining,
ext.trx_number,
ext.days_past_due,
TO_CHAR(ext.gl_date,''YYYY-MM-DD'') gl_date,
ext.data_converted_flag,
ext.exchange_rate,
DECODE(bucket_0,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_0,
DECODE(bucket_1,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_1,
DECODE(bucket_2,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_2,
DECODE(bucket_3,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_3,
DECODE(bucket_4,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_4,
DECODE(bucket_5,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_5,
DECODE(bucket_6,0,0,nvl(mfar.rec_aging_amount,ext.amt_due_remaining)) b_6,
DECODE(mfar.bal_segment_value,null,ext.bal_segment_value,
mfar.bal_segment_value) bal_segment_value,
ext.invoice_type,
ext.cons_billing_number,
ext.contact_name,
ext.contact_phone
from ar_aging_extract ext,
( select mfar.*,
'||pg_accounting_flexfield||' sort_field1,
'||pg_acct_flex_bal_seg||' bal_segment_value
from ar_aging_mfar_extract mfar,
gl_code_combinations c
where c.code_combination_id = mfar.code_combination_id ) mfar
where ext.parent_request_id = mfar.parent_request_id(+)
and ext.payment_schedule_id = mfar.payment_schedule_id(+)
and nvl(mfar.rec_aging_amount,ext.amt_due_remaining) <> 0
'||l_filter_criteria;
select customer_id,
customer_number,
short_customer_name customer_name,
sort_field1,
inv_tid_inv,
contact_site_id,
customer_state,
customer_city,
cust_acct_site_id,
sum(customer_amount ) customer_total,
sum( risk_amount ) risk_total,
sum( pmt_amount ) pmt_total,
sum( cm_amount ) cm_total,
sum( claim_amount ) claim_total,
sum( inv_amount ) inv_total,
data_converted_flag,
SUM( bucket_0 ) b_0,
SUM( bucket_1 ) b_1,
SUM( bucket_2 ) b_2,
SUM( bucket_3 ) b_3,
SUM( bucket_4 ) b_4,
SUM( bucket_5 ) b_5,
SUM( bucket_6 ) b_6,
bal_segment_value,
contact_name,
contact_phone
from
( select
ext.customer_id,
ext.customer_number,
ext.short_customer_name,
ext.sort_field1 sort_field1,
ext.sort_field2,
ext.inv_tid_inv,
ext.contact_site_id,
ext.customer_state,
ext.customer_city,
ext.cust_acct_site_id,
ext.amt_due_remaining,
ext.amt_due_remaining customer_amount,
decode(class,'''|| pg_risk_meaning ||''',ext.amt_due_remaining,0) risk_amount,
decode(class,''PMT'',ext.amt_due_remaining,0) pmt_amount,
decode(class,''CM'',ext.amt_due_remaining,0) cm_amount,
decode(class,''CLAIM'',ext.amt_due_remaining,0) claim_amount,
decode(class,'''|| pg_risk_meaning ||''',0,
''PMT'',0,
''CM'',0,
''CLAIM'',0,ext.amt_due_remaining) inv_amount,
ext.data_converted_flag,
ext.exchange_rate,
DECODE(bucket_0,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE( '''|| pg_risk_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''PMT'', DECODE( '''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CM'', DECODE( '''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
ext.amt_due_remaining )) bucket_0,
DECODE(bucket_1,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
ext.amt_due_remaining )) bucket_1,
DECODE(bucket_2,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
ext.amt_due_remaining )) bucket_2,
DECODE(bucket_3,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
ext.amt_due_remaining )) bucket_3,
DECODE(bucket_4,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
ext.amt_due_remaining )) bucket_4,
DECODE(bucket_5,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
ext.amt_due_remaining )) bucket_5,
DECODE(bucket_6,0,0,
DECODE(class,'''|| pg_risk_meaning ||''',
DECODE('''|| pg_risk_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''PMT'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CM'', DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
''CLAIM'',DECODE('''|| pg_credit_option||''',''DETAIL'',
ext.amt_due_remaining,0),
ext.amt_due_remaining )) bucket_6,
bal_segment_value,
ext.contact_name,
ext.contact_phone
from ar_aging_extract ext
where 1 = 1
'||l_filter_criteria||'
)
group by customer_id,
customer_number,
short_customer_name,
sort_field1,
inv_tid_inv,
contact_site_id,
customer_state,
customer_city,
cust_acct_site_id,
data_converted_flag,
contact_name,
contact_phone,
bal_segment_value
having sum(amt_due_remaining) <> 0';
SELECT customer_id,
customer_number,
short_customer_name customer_name,
sort_field1,
sort_field2,
inv_tid_inv,
contact_site_id,
customer_state,
customer_city,
cust_acct_site_id,
payment_schedule_id,
class,
TO_CHAR(due_date,''YYYY-MM-DD'') due_date,
amt_due_remaining,
trx_number,
days_past_due,
TO_CHAR(gl_date,''YYYY-MM-DD'') gl_date,
gl_date,
data_converted_flag,
exchange_rate,
DECODE(bucket_0,0,0,amt_due_remaining) b_0,
DECODE(bucket_1,0,0,amt_due_remaining) b_1,
DECODE(bucket_2,0,0,amt_due_remaining) b_2,
DECODE(bucket_3,0,0,amt_due_remaining) b_3,
DECODE(bucket_4,0,0,amt_due_remaining) b_4,
DECODE(bucket_5,0,0,amt_due_remaining) b_5,
DECODE(bucket_6,0,0,amt_due_remaining) b_6,
bal_segment_value,
invoice_type,
cons_billing_number,
contact_name,
contact_phone
from ar_aging_extract ext
where 1=1 '||l_filter_criteria;
SELECT param.org_id,
sob.name,
sob.chart_of_accounts_id,
sob.currency_code,
cur.precision,
decode(:p_in_currency,NULL,''Y'',NULL),
param.set_of_books_id
FROM gl_sets_of_books sob,
ar_system_parameters param,
fnd_currencies cur
WHERE sob.set_of_books_id = param.set_of_books_id
AND sob.currency_code = cur.currency_code
AND rownum = 1
'||pg_org_where_sys_param;
p_mode => 'SELECT',
p_qualifier => 'ALL');
p_mode => 'SELECT',
p_qualifier => 'GL_BALANCING');
DELETE
FROM ar_aging_payment_schedules
WHERE parent_request_id = pg_request_id;
DELETE
FROM ar_aging_extract
WHERE parent_request_id = pg_request_id;
DELETE
FROM ar_aging_mfar_extract
WHERE parent_request_id = pg_request_id;
| b) populate interim table with selected payment schedule records |
| c) query also allocates the payment schedule to child workers |
| |
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
| |
| PARAMETERS |
| NONE |
| |
| KNOWN ISSUES |
| |
| NOTES |
| filters p_in_amt_due_low and p_in_amt_due_high are not handled in this |
| routine to avoid any possible read consistency issues as mentioned in |
| bug 3487101 |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 10-JUL-2009 Naveen Prodduturi Created |
*==========================================================================*/
PROCEDURE alloc_aging_payment_schedules IS
l_insert_stmt VARCHAR2(2000);
l_select_caluse VARCHAR2(2000);
l_insert_stmt :=
'INSERT /*HINT*/ INTO ar_aging_payment_schedules a
( payment_schedule_id,
source_type,
parent_request_id,
worker_id
) ';
l_select_caluse :=
' SELECT payment_schedule_id,
source_type,'
||pg_request_id||','||
' DECODE('||pg_worker_count||',1,'||pg_worker_id||', MOD(ROWNUM, '||pg_worker_count||' ) + 1) ';
select ps.payment_schedule_id,
''INV'' source_type
from ar_payment_schedules ps '||l_from_clause||'
WHERE ps.gl_date_closed > :as_of_date
AND ps.gl_date <= :as_of_date
AND DECODE(UPPER(:pg_in_currency),NULL, ps.invoice_currency_code,
UPPER(:pg_in_currency)) = ps.invoice_currency_code
AND ps.class <> ''PMT'''||pg_org_where_ps||l_where_clause;
select /*+ leading(crh) index(ps AR_PAYMENT_SCHEDULES_U2)*/
distinct ps.payment_schedule_id,
''CRH'' source_type
from ar_cash_receipt_history crh,
ar_payment_schedules ps '||l_from_clause||'
where crh.gl_date <= :as_of_date
and ( crh.current_record_flag = ''Y'' OR
crh.reversal_gl_date > :as_of_date )
and crh.status NOT IN
( DECODE(crh.factor_flag, ''Y'',''RISK_ELIMINATED'',
''N'',''CLEARED''), ''REVERSED'')
and ps.cash_receipt_id = crh.cash_receipt_id
and ps.class = ''PMT''
AND DECODE(UPPER(:pg_in_currency),NULL, ps.invoice_currency_code,
UPPER(:pg_in_currency)) = ps.invoice_currency_code
and not exists
( SELECT ''x''
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = crh.cash_receipt_id
AND ra.status = ''ACTIVITY''
AND applied_payment_schedule_id = -2
)'||pg_org_where_crh||l_where_clause;
select /*+ leading(ps) index(ps AR_PAYMENT_SCHEDULES_N9) index(app AR_RECEIVABLE_APPLICATIONS_N1)*/
distinct ps.payment_schedule_id,
''RA'' source_type
from ar_receivable_applications app,
ar_payment_schedules ps '||l_from_clause||'
where app.gl_date <= :as_of_date
AND app.status IN ( ''ACC'' ,
''UNAPP'',
''UNID'' ,
''OTHER ACC'')
AND NVL(app.confirmed_flag, ''Y'') = ''Y''
AND app.reversal_gl_date IS NULL
AND ps.cash_receipt_id = app.cash_receipt_id
AND ps.class = ''PMT''
AND DECODE(UPPER(:pg_in_currency),NULL, ps.invoice_currency_code,
UPPER(:pg_in_currency)) = ps.invoice_currency_code
AND ps.gl_date_closed > :as_of_date
AND NVL( ps.receipt_confirmed_flag, ''Y'' ) = ''Y'''||pg_org_where_ra||l_where_clause;
l_insert_stmt := REPLACE( l_insert_stmt,'/*HINT*/','/*+ parallel(a) append */');
l_final_stmt := l_insert_stmt ||
l_select_caluse ||
l_from_clause ;
arp_standard.debug( 'rows inserted into staging table:'||l_rows_processed);
| b) populate interim table with selected payment schedule records |
| c) query also allocates the payment schedule to child workers |
| |
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
| |
| PARAMETERS |
| NONE |
| |
| KNOWN ISSUES |
| |
| NOTES |
| filters p_in_amt_due_low and p_in_amt_due_high are not handled in this |
| routine to avoid any possible read consistency issues as mentioned in |
| bug 3487101 |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 10-JUL-2009 Naveen Prodduturi Created |
*==========================================================================*/
PROCEDURE get_report_specific_info( p_qry_category IN VARCHAR2,
p_rep_specific_cols OUT NOCOPY VARCHAR2,
p_rep_from_info OUT NOCOPY VARCHAR2,
p_rep_where_cls OUT NOCOPY VARCHAR2,
p_rep_spec_sub_query OUT NOCOPY VARCHAR2,
p_rep_spec_grp_cols OUT NOCOPY VARCHAR2) IS
BEGIN
/*Only verified the flow related to aging by account report,need verify and replace the
lexical parameters and test the complete flow when we decide to migrate all these
reports(reports like ARXAGR ,ARXAGL and ARXAGS)*/
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'AR_AGING_BUCKETS_PKG.get_report_specific_info()+');
| select statements that make use of bucket_function. |
| |
| Procedure does the following |
| a) binds all the info related to the current bucket |
| |
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
| |
| PARAMETERS |
| NONE |
| |
| KNOWN ISSUES |
| |
| NOTES |
| The cursor ensures that we always bind all the 7 bucket info. |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 10-JUL-2009 Naveen Prodduturi Created |
*==========================================================================*/
PROCEDURE bind_bucket_parameters( p_cursor INTEGER) IS
CURSOR buc_info_cur IS
select *
from
( select lines.bucket_sequence_num buc_number,
days_start,
days_to,
report_heading1,
report_heading2,
type,
DECODE(type,'DISPUTE_ONLY',type,
'PENDADJ_ONLY',type,
'DISPUTE_PENDADJ',type,null) bucket_category
from ar_aging_bucket_lines lines,
ar_aging_buckets buckets
where lines.aging_bucket_id = buckets.aging_bucket_id
and upper(buckets.bucket_name) = upper(pg_in_bucket_type_low)
and nvl(buckets.status,'A') = 'A'
) buckets,
( select rownum-1 sequence_number
from dual
connect by
rownum < 8 ) dummy
where dummy.sequence_number = buckets.buc_number(+);
l_insert_stmt VARCHAR2(32000);
l_insert_stmt := '
insert into ar_aging_extract
( customer_id,
customer_number,
short_customer_name,
sort_field1,
sort_field2,
inv_tid_inv,
contact_site_id,
customer_state,
customer_city,
cust_acct_site_id,
payment_schedule_id,
class,
due_date,
amt_due_remaining,
trx_number,
days_past_due,
amount_adjusted,
amount_applied,
amount_credited,
gl_date,
data_converted_flag,
exchange_rate,
contact_name,
contact_phone,
bucket_0,
bucket_1,
bucket_2,
bucket_3,
bucket_4,
bucket_5,
bucket_6,
bal_segment_value,
invoice_type,
cons_billing_number,
category,
parent_request_id,
worker_id)
select customer_id,
customer_number,
short_customer_name,
sort_field1,
sort_field2,
inv_tid_inv,
contact_site_id,
customer_state,
customer_city,
cust_acct_site_id,
payment_schedule_id,
class,
due_date,
amt_due_remaining,
trx_number,
days_past_due,
amount_adjusted,
amount_applied,
amount_credited,
gl_date,
data_converted_flag,
exchange_rate,
AR_AGING_BUCKETS_PKG.get_contact_information( contact_site_id,
''NAME'') contact_name,
AR_AGING_BUCKETS_PKG.get_contact_information( contact_site_id,
''PHONE'') contact_phone,
bucket_0,
bucket_1,
bucket_2,
bucket_3,
bucket_4,
bucket_5,
bucket_6,
bal_segment_value,
invoice_type,
cons_billing_number
,'''||p_qry_category||'''
,'||pg_parent_request_id||'
,'||pg_worker_id||'
from (';
dbms_sql.parse(l_cursor,l_insert_stmt||p_in_report_query||')',DBMS_SQL.NATIVE);
| PRIVATE PROCEDURE build_select_stmt |
| |
| DESCRIPTION |
| construct and return various queries to extract the aging info |
| |
| |
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body) |
| |
| PARAMETERS |
| NONE |
| |
| KNOWN ISSUES |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Date Author Description of Changes |
| 10-JUL-2009 Naveen Prodduturi Created |
*==========================================================================*/
PROCEDURE build_select_stmt( p_out_invoice_query OUT NOCOPY VARCHAR2,
p_out_receipt_query OUT NOCOPY VARCHAR2,
p_out_riskinfo_query OUT NOCOPY VARCHAR2,
p_out_br_query OUT NOCOPY VARCHAR2) IS
l_inv_app_act_query VARCHAR2(32000);
arp_standard.debug( 'AR_AGING_BUCKETS_PKG.build_select_stmt()+');
SELECT a.customer_id,
a.customer_site_use_id ,
a.customer_trx_id,
a.payment_schedule_id,
a.class ,
sum(a.primary_salesrep_id) primary_salesrep_id,
a.due_date ,
sum(a.amount_due_remaining) amt_due_remaining_inv,
a.trx_number,
a.amount_adjusted,
a.amount_applied ,
a.amount_credited ,
a.amount_adjusted_pending,
a.gl_date ,
a.cust_trx_type_id,
a.org_id,
a.invoice_currency_code,
a.exchange_rate,
sum(a.cons_inv_id) cons_inv_id
FROM
( SELECT /*HINT*/
ps.customer_id,
ps.customer_site_use_id ,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
0 primary_salesrep_id,
ps.due_date ,
nvl(sum ( decode( :c_convert_flag, ''Y'',
nvl(adj.acctd_amount, 0),
adj.amount )
),0) * (-1) amount_due_remaining,
ps.trx_number,
ps.amount_adjusted ,
ps.amount_applied ,
ps.amount_credited ,
ps.amount_adjusted_pending,
ps.gl_date ,
ps.cust_trx_type_id,
ps.org_id,
ps.invoice_currency_code,
nvl(ps.exchange_rate,1) exchange_rate,
0 cons_inv_id
FROM ar_aging_payment_schedules aging,
ar_payment_schedules ps,
ar_adjustments adj
WHERE aging.parent_request_id = :parent_request_id
AND aging.worker_id = :worker_id
AND aging.source_type = ''INV''
AND aging.payment_schedule_id = ps.payment_schedule_id
AND ps.gl_date <= :as_of_date
AND ps.customer_id > 0
AND ps.gl_date_closed > :as_of_date
AND adj.payment_schedule_id = ps.payment_schedule_id
AND adj.status = ''A''
AND adj.gl_date > :as_of_date
GROUP BY
ps.customer_id,
ps.customer_site_use_id ,
ps.customer_trx_id,
ps.class ,
ps.due_date,
ps.trx_number,
ps.amount_adjusted ,
ps.amount_applied ,
ps.amount_credited ,
ps.amount_adjusted_pending,
ps.gl_date ,
ps.cust_trx_type_id,
ps.org_id,
ps.invoice_currency_code,
nvl(ps.exchange_rate,1),
ps.payment_schedule_id
UNION ALL
SELECT /*HINT*/
ps.customer_id,
ps.customer_site_use_id ,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class ,
0 primary_salesrep_id,
ps.due_date ,
nvl(sum ( decode
( :c_convert_flag, ''Y'',
(decode(ps.class, ''CM'',
decode ( app.application_type, ''CM'',
app.acctd_amount_applied_from,
app.acctd_amount_applied_to
),
app.acctd_amount_applied_to)+
nvl(app.acctd_earned_discount_taken,0) +
nvl(app.acctd_unearned_discount_taken,0))
,
( app.amount_applied +
nvl(app.earned_discount_taken,0) +
nvl(app.unearned_discount_taken,0) )
) *
decode
( ps.class, ''CM'',
decode(app.application_type, ''CM'', -1, 1), 1 )
), 0) amount_due_remaining_inv,
ps.trx_number ,
ps.amount_adjusted,
ps.amount_applied ,
ps.amount_credited ,
ps.amount_adjusted_pending,
ps.gl_date gl_date_inv,
ps.cust_trx_type_id,
ps.org_id,
ps.invoice_currency_code,
nvl(ps.exchange_rate, 1) exchange_rate,
0 cons_inv_id
FROM ar_aging_payment_schedules aging,
ar_payment_schedules ps,
ar_receivable_applications app
WHERE aging.parent_request_id = :parent_request_id
AND aging.worker_id = :worker_id
AND aging.source_type = ''INV''
AND aging.payment_schedule_id = ps.payment_schedule_id
AND ps.gl_date <= :as_of_date
AND ps.customer_id > 0
AND ps.gl_date_closed > :as_of_date
AND (app.applied_payment_schedule_id = ps.payment_schedule_id
OR
app.payment_schedule_id = ps.payment_schedule_id)
AND app.status IN (''APP'', ''ACTIVITY'')
AND nvl( app.confirmed_flag, ''Y'' ) = ''Y''
AND app.gl_date > :as_of_date
GROUP BY
ps.customer_id,
ps.customer_site_use_id ,
ps.customer_trx_id,
ps.class ,
ps.due_date,
ps.trx_number,
ps.amount_adjusted ,
ps.amount_applied ,
ps.amount_credited ,
ps.amount_adjusted_pending,
ps.gl_date ,
ps.cust_trx_type_id,
ps.org_id,
ps.invoice_currency_code,
nvl(ps.exchange_rate, 1),
ps.payment_schedule_id
UNION ALL
SELECT /*HINT*/
ps.customer_id,
ps.customer_site_use_id ,
ps.customer_trx_id,
ps.payment_schedule_id,
ps.class class_inv,
nvl(ct.primary_salesrep_id, -3) primary_salesrep_id,
ps.due_date due_date_inv,
decode( :c_convert_flag, ''Y'',
ps.acctd_amount_due_remaining,
ps.amount_due_remaining) amt_due_remaining_inv,
ps.trx_number,
ps.amount_adjusted ,
ps.amount_applied ,
ps.amount_credited ,
ps.amount_adjusted_pending,
ps.gl_date ,
ps.cust_trx_type_id,
ps.org_id,
ps.invoice_currency_code,
nvl(ps.exchange_rate, 1) exchange_rate,
ps.cons_inv_id
FROM ar_aging_payment_schedules aging,
ar_payment_schedules ps,
ra_customer_trx ct
WHERE aging.parent_request_id = :parent_request_id
AND aging.worker_id = :worker_id
AND aging.source_type = ''INV''
AND aging.payment_schedule_id = ps.payment_schedule_id
AND ps.gl_date <= :as_of_date
AND ps.gl_date_closed > :as_of_date
AND ps.customer_trx_id = ct.customer_trx_id
AND DECODE(:pg_rep_type,''ARXAGR'',ps.class,''NULL'') <> ''CB''
'||nvl(l_rep_spec_sub_query,CHR(0)) || '
) a
GROUP BY a.customer_id,
a.customer_site_use_id ,
a.customer_trx_id,
a.payment_schedule_id,
a.class ,
a.due_date ,
a.trx_number,
a.amount_adjusted,
a.amount_applied ,
a.amount_credited ,
a.amount_adjusted_pending,
a.gl_date ,
a.cust_trx_type_id,
a.org_id,
a.invoice_currency_code,
a.exchange_rate) ps, ';
select /*+ LEADING(ps) */
nvl(cust_acct.cust_account_id,-999) customer_id,
cust_acct.account_number customer_number,
substrb(party.party_name,1,50) short_customer_name,
arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) sort_field2,
site.site_use_id contact_site_id,
loc.state customer_state,
loc.city customer_city,
decode(:format_detailed,NULL,-1,acct_site.cust_acct_site_id) cust_acct_site_id,
ps.payment_schedule_id payment_schedule_id,
ps.class class,
ps.due_date due_date,
amt_due_remaining_inv amt_due_remaining,
ps.trx_number trx_number,
ceil(:as_of_date - ps.due_date) days_past_due,
ps.amount_adjusted amount_adjusted,
ps.amount_applied amount_applied,
ps.amount_credited amount_credited,
ps.gl_date gl_date,
decode(ps.invoice_currency_code, :functional_currency, NULL,
decode(ps.exchange_rate, NULL, ''*'', NULL)) data_converted_flag,
nvl(ps.exchange_rate, 1) exchange_rate,
arpt_sql_func_util.bucket_function(:bucket_line_type_0,
dh.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_0,:bucket_days_to_0,
ps.due_date,:bucket_category_0,:as_of_date) bucket_0,
arpt_sql_func_util.bucket_function(:bucket_line_type_1,
dh.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_1,:bucket_days_to_1,
ps.due_date,:bucket_category_1,:as_of_date) bucket_1,
arpt_sql_func_util.bucket_function(:bucket_line_type_2,
dh.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_2,:bucket_days_to_2,
ps.due_date,:bucket_category_2,:as_of_date) bucket_2,
arpt_sql_func_util.bucket_function(:bucket_line_type_3,
dh.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_3,:bucket_days_to_3,
ps.due_date,:bucket_category_3,:as_of_date) bucket_3,
arpt_sql_func_util.bucket_function(:bucket_line_type_4,
dh.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_4,:bucket_days_to_4,
ps.due_date,:bucket_category_4,:as_of_date) bucket_4,
arpt_sql_func_util.bucket_function(:bucket_line_type_5,
dh.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_5,:bucket_days_to_5,
ps.due_date,:bucket_category_5,:as_of_date) bucket_5,
arpt_sql_func_util.bucket_function(:bucket_line_type_6,
dh.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_6,:bucket_days_to_6,
ps.due_date,:bucket_category_6,:as_of_date) bucket_6, '||pg_acct_flex_bal_seg||'
bal_segment_value,
arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id)
invoice_type '|| l_rep_specific_cols || '
from '||l_inv_act_sub_query|| '
hz_cust_accounts cust_acct,
hz_parties party,
hz_cust_site_uses site,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
ra_cust_trx_line_gl_dist gld,
ar_dispute_history dh,
gl_code_combinations c '||l_rep_spec_from_list ||'
where ps.customer_site_use_id = site.site_use_id
and ps.customer_id = cust_acct.cust_account_id
and ps.customer_trx_id = gld.customer_trx_id
and site.cust_acct_site_id = acct_site.cust_acct_site_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and gld.account_class = ''REC''
and gld.latest_rec_flag = ''Y''
and ps.payment_schedule_id = dh. payment_schedule_id(+)
and :as_of_date >= nvl(dh.start_date(+), :as_of_date)
and :as_of_date < nvl(dh.end_date(+), :as_of_date + 1)
and cust_acct.party_id = party.party_id '||l_rep_spec_where_cls;
select /*+ LEADING(aging) */
substrb(nvl(party.party_name, '''||pg_short_unid_phrase||'''),1,50) short_customer_name,
cust_acct.account_number customer_number,
site.site_use_id contact_site_id,
loc.state customer_state,
loc.city customer_city,
decode(:format_detailed,NULL,-1,acct_site.cust_acct_site_id) cust_acct_site_id,
nvl(cust_acct.cust_account_id, -999) customer_id,
ps.payment_schedule_id payment_schedule_id,
DECODE(app.applied_payment_schedule_id,-4,''CLAIM'',ps.class) class,
ps.due_date due_date,
decode ( :c_convert_flag, ''Y'', nvl(-sum(app.acctd_amount_applied_from),0) ,
nvl(-sum(app.amount_applied),0)) amt_due_remaining,
ps.trx_number trx_number,
ceil(:as_of_date - ps.due_date) days_past_due,
ps.amount_adjusted amount_adjusted,
ps.amount_applied amount_applied,
ps.amount_credited amount_credited,
ps.gl_date gl_date,
decode(ps.invoice_currency_code, :functional_currency, NULL,
decode(ps.exchange_rate, NULL, ''*'', NULL) ) data_converted_flag,
nvl(ps.exchange_rate, 1) exchange_rate,
arpt_sql_func_util.bucket_function(:bucket_line_type_0,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_0,:bucket_days_to_0,
ps.due_date,:bucket_category_0,:as_of_date) bucket_0,
arpt_sql_func_util.bucket_function(:bucket_line_type_1,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_1,:bucket_days_to_1,
ps.due_date,:bucket_category_1,:as_of_date) bucket_1,
arpt_sql_func_util.bucket_function(:bucket_line_type_2,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_2,:bucket_days_to_2,
ps.due_date,:bucket_category_2,:as_of_date) bucket_2,
arpt_sql_func_util.bucket_function(:bucket_line_type_3,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_3,:bucket_days_to_3,
ps.due_date,:bucket_category_3,:as_of_date) bucket_3,
arpt_sql_func_util.bucket_function(:bucket_line_type_4,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_4,:bucket_days_to_4,
ps.due_date,:bucket_category_4,:as_of_date) bucket_4,
arpt_sql_func_util.bucket_function(:bucket_line_type_5,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_5,:bucket_days_to_5,
ps.due_date,:bucket_category_5,:as_of_date) bucket_5,
arpt_sql_func_util.bucket_function(:bucket_line_type_6,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_6,:bucket_days_to_6,
ps.due_date,:bucket_category_6,:as_of_date) bucket_6,
'||pg_acct_flex_bal_seg||'
bal_segment_value,
'''|| pg_payment_meaning ||''' sort_field2,
'''|| pg_payment_meaning ||''' invoice_type '|| l_rep_specific_cols ||'
from hz_cust_accounts cust_acct,
hz_parties party,
ar_aging_payment_schedules aging,
ar_payment_schedules ps,
hz_cust_site_uses site,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
ar_receivable_applications app,
gl_code_combinations c '||l_rep_spec_from_list ||'
where aging.parent_request_id = :parent_request_id
AND aging.worker_id = :worker_id
AND aging.source_type = ''RA''
AND aging.payment_schedule_id = ps.payment_schedule_id
AND app.gl_date <= :as_of_date
and ps.trx_number is not null
and ps.customer_id = cust_acct.cust_account_id(+)
and cust_acct.party_id = party.party_id (+)
and ps.cash_receipt_id = app.cash_receipt_id
and app.code_combination_id = c.code_combination_id
and app.status in ( ''ACC'', ''UNAPP'', ''UNID'',''OTHER ACC'')
and nvl(app.confirmed_flag, ''Y'') = ''Y''
and ps.customer_site_use_id = site.site_use_id(+)
and site.cust_acct_site_id = acct_site.cust_acct_site_id(+)
and acct_site.party_site_id = party_site.party_site_id(+)
and loc.location_id(+) = party_site.location_id
and ps.gl_date_closed > :as_of_date
and ((app.reversal_gl_date is not null AND
ps.gl_date <= :as_of_date) OR
app.reversal_gl_date is null )
and nvl( ps.receipt_confirmed_flag, ''Y'' ) = ''Y''
'||l_rep_spec_where_cls||'
GROUP BY party.party_name,
cust_acct.account_number,
site.site_use_id,
loc.state,
loc.city,
acct_site.cust_acct_site_id,
cust_acct.cust_account_id,
ps.payment_schedule_id,
ps.due_date,
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,
DECODE(app.applied_payment_schedule_id,-4,''CLAIM'',ps.class),
'||pg_acct_flex_bal_seg||',
decode( app.status, ''UNID'', ''UNID'',''OTHER ACC'',''OTHER ACC'',''UNAPP''),
'''|| pg_payment_meaning||''''|| l_rep_spec_grp_cols;
select /*+ LEADING(aging) */
substrb(nvl(party.party_name, '''||pg_short_unid_phrase||'''),1,50) short_customer_name,
cust_acct.account_number customer_number,
site.site_use_id contact_site_id,
loc.state customer_state,
loc.city customer_city,
decode(:format_detailed,NULL,-1,acct_site.cust_acct_site_id) cust_acct_site_id,
nvl(cust_acct.cust_account_id, -999) customer_id,
ps.payment_schedule_id payment_schedule_id,
'''|| pg_risk_meaning ||''' class,
ps.due_date due_date ,
decode( :c_convert_flag, ''Y'', crh.acctd_amount, crh.amount) amt_due_remaining,
ps.trx_number trx_number,
ceil(:as_of_date - ps.due_date) days_past_due,
ps.amount_adjusted amount_adjusted,
ps.amount_applied amount_applied,
ps.amount_credited amount_credited,
crh.gl_date gl_date,
decode(ps.invoice_currency_code, :functional_currency, NULL,
decode(crh.exchange_rate, NULL, ''*'', NULL)) data_converted_flag,
nvl(crh.exchange_rate, 1) exchange_rate,
arpt_sql_func_util.bucket_function(:bucket_line_type_0,
0,0,:bucket_days_from_0,:bucket_days_to_0,
ps.due_date,:bucket_category_0,:as_of_date) bucket_0,
arpt_sql_func_util.bucket_function(:bucket_line_type_1,
0,0,:bucket_days_from_1,:bucket_days_to_1,
ps.due_date,:bucket_category_1,:as_of_date) bucket_1,
arpt_sql_func_util.bucket_function(:bucket_line_type_2,
0,0,:bucket_days_from_2,:bucket_days_to_2,
ps.due_date,:bucket_category_2,:as_of_date) bucket_2,
arpt_sql_func_util.bucket_function(:bucket_line_type_3,
0,0,:bucket_days_from_3,:bucket_days_to_3,
ps.due_date,:bucket_category_3,:as_of_date) bucket_3,
arpt_sql_func_util.bucket_function(:bucket_line_type_4,
0,0,:bucket_days_from_4,:bucket_days_to_4,
ps.due_date,:bucket_category_4,:as_of_date) bucket_4,
arpt_sql_func_util.bucket_function(:bucket_line_type_5,
0,0,:bucket_days_from_5,:bucket_days_to_5,
ps.due_date,:bucket_category_5,:as_of_date) bucket_5,
arpt_sql_func_util.bucket_function(:bucket_line_type_6,
0,0,:bucket_days_from_6,:bucket_days_to_6,
ps.due_date,:bucket_category_6,:as_of_date) bucket_6,
'||pg_acct_flex_bal_seg||'
bal_segment_value,
'''|| pg_risk_meaning ||''' sort_field2,
'''|| pg_risk_meaning ||''' invoice_type '|| l_rep_specific_cols ||'
from hz_cust_accounts cust_acct,
hz_parties party,
ar_aging_payment_schedules aging,
ar_payment_schedules ps,
hz_cust_site_uses site,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
gl_code_combinations c '||l_rep_spec_from_list ||'
where aging.parent_request_id = :parent_request_id
and aging.worker_id = :worker_id
and aging.source_type = ''CRH''
and aging.payment_schedule_id = ps.payment_schedule_id
and crh.gl_date <= :as_of_date
and ps.trx_number is not null
and ps.customer_id = cust_acct.cust_account_id(+)
and cust_acct.party_id = party.party_id(+)
and ps.cash_receipt_id = cr.cash_receipt_id
and cr.cash_receipt_id = crh.cash_receipt_id
and crh.account_code_combination_id = c.code_combination_id
and ps.customer_site_use_id = site.site_use_id(+)
and site.cust_acct_site_id = acct_site.cust_acct_site_id(+)
and acct_site.party_site_id = party_site.party_site_id(+)
and loc.location_id(+) = party_site.location_id
and ( crh.current_record_flag = ''Y''
or crh.reversal_gl_date > :as_of_date )
and crh.status not in ( decode(crh.factor_flag,
''Y'',''RISK_ELIMINATED'',
''N'',''CLEARED''),
''REVERSED'')
and not exists (select ''x''
from ar_receivable_applications ra
where ra.cash_receipt_id = cr.cash_receipt_id
and ra.status = ''ACTIVITY''
and applied_payment_schedule_id = -2)
'||l_rep_spec_where_cls;
select /*+ LEADING(aging) */
substrb(party.party_name,1,50) short_customer_name,
cust_acct.account_number customer_number,
arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) sort_field2,
site.site_use_id contact_site_id,
loc.state customer_state,
loc.city customer_city,
decode(:format_detailed,NULL,-1,acct_site.cust_acct_site_id) cust_acct_site_id,
nvl(cust_acct.cust_account_id,-999) customer_id,
ps.payment_schedule_id payment_schedule_id,
ps.class class,
ps.due_date due_date,
decode( :c_convert_flag, ''Y'',
ps.acctd_amount_due_remaining,
ps.amount_due_remaining) amt_due_remaining,
ps.trx_number trx_number,
ceil(:as_of_date - ps.due_date) days_past_due,
ps.amount_adjusted amount_adjusted,
ps.amount_applied amount_applied,
ps.amount_credited amount_credited,
ps.gl_date gl_date,
decode(ps.invoice_currency_code, :functional_currency, NULL,
decode(ps.exchange_rate, NULL, ''*'', NULL)) data_converted_flag,
nvl(ps.exchange_rate, 1) exchange_rate,
arpt_sql_func_util.bucket_function(:bucket_line_type_0,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_0,:bucket_days_to_0,
ps.due_date,:bucket_category_0,:as_of_date) bucket_0,
arpt_sql_func_util.bucket_function(:bucket_line_type_1,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_1,:bucket_days_to_1,
ps.due_date,:bucket_category_1,:as_of_date) bucket_1,
arpt_sql_func_util.bucket_function(:bucket_line_type_2,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_2,:bucket_days_to_2,
ps.due_date,:bucket_category_2,:as_of_date) bucket_2,
arpt_sql_func_util.bucket_function(:bucket_line_type_3,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_3,:bucket_days_to_3,
ps.due_date,:bucket_category_3,:as_of_date) bucket_3,
arpt_sql_func_util.bucket_function(:bucket_line_type_4,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_4,:bucket_days_to_4,
ps.due_date,:bucket_category_4,:as_of_date) bucket_4,
arpt_sql_func_util.bucket_function(:bucket_line_type_5,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_5,:bucket_days_to_5,
ps.due_date,:bucket_category_5,:as_of_date) bucket_5,
arpt_sql_func_util.bucket_function(:bucket_line_type_6,
ps.amount_in_dispute,ps.amount_adjusted_pending,
:bucket_days_from_6,:bucket_days_to_6,
ps.due_date,:bucket_category_6,:as_of_date) bucket_6,
'||pg_acct_flex_bal_seg||'
bal_segment_value,
arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) invoice_type
'|| l_rep_specific_cols ||'
from hz_cust_accounts cust_acct,
hz_parties party,
ar_aging_payment_schedules aging,
ar_payment_schedules ps,
hz_cust_site_uses site,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
ar_transaction_history th,
gl_code_combinations c,
'||l_accting_source ||' dist '||l_rep_spec_from_list ||'
where aging.parent_request_id = :parent_request_id
and aging.worker_id = :worker_id
and aging.source_type = ''INV''
and aging.payment_schedule_id = ps.payment_schedule_id
and ps.gl_date <= :as_of_date
and ps.customer_site_use_id = site.site_use_id
and site.cust_acct_site_id = acct_site.cust_acct_site_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and ps.gl_date_closed > :as_of_date
and ps.class = ''BR''
and th.transaction_history_id = dist.source_id
and ps.customer_id=cust_acct.cust_account_id
and ps.customer_trx_id = th.customer_trx_id
and dist.source_table = ''TH''
and dist.amount_dr is not null
and dist.source_table_secondary is NULL
and dist.code_combination_id = c.code_combination_id
and cust_acct.party_id = party.party_id
and th.transaction_history_id =
(select max(transaction_history_id)
from ar_transaction_history th2,
'||l_accting_source ||' dist2
where th2.transaction_history_id = dist2.source_id
and dist2.source_table = ''TH''
and th2.gl_date <= :as_of_date
and dist2.amount_dr is not null
and th2.customer_trx_id = ps.customer_trx_id)';
arp_standard.debug( 'AR_AGING_BUCKETS_PKG.build_select_stmt()-');
arp_standard.debug( 'Exception AR_AGING_BUCKETS_PKG.build_select_stmt()');
END build_select_stmt;
select /*+leading(age_ps) */
pg_parent_request_id,
payment_schedule_id,
code_combination_id,
currency_code,
rec_amount,
0 rec_aging_amount,
SUM(rec_amount) OVER (PARTITION BY payment_schedule_id) receivable_total,
amt_due_remaining,
p_category category
from
( select /*+ leading(age_ps)*/
ae.code_combination_id,
sum(nvl(xdl.unrounded_entered_dr,0)-nvl(xdl.unrounded_entered_cr,0)) rec_amount,
age_ps.payment_schedule_id,
age_ps.amt_due_remaining,
ae.currency_code
from xla_ae_headers hdr,
xla_ae_lines ae,
xla_distribution_links xdl,
( select /*+ leading(ext) index(ra AR_RECEIVABLE_APPLICATIONS_N3)*/
ra.event_id,
ext.payment_schedule_id,
ext.amt_due_remaining,
'RA_APPLIED_FROM' source_identifier
from ar_aging_extract ext,
ar_receivable_applications ra
where ext.parent_request_id = pg_parent_request_id
and ext.worker_id = pg_worker_id
and ext.payment_schedule_id = ra.payment_schedule_id
and ra.gl_date <= pg_in_as_of_date_low
and ra.status in ('APP','ACTIVITY')
group by ra.event_id,
ext.payment_schedule_id,
ext.amt_due_remaining
UNION ALL
select /*+ leading(ext) index(ra AR_RECEIVABLE_APPLICATIONS_N8)*/
ra.event_id,
ext.payment_schedule_id,
ext.amt_due_remaining,
'RA_APPLIED_TO' source_identifier
from ar_aging_extract ext,
ar_receivable_applications ra
where ext.parent_request_id = pg_parent_request_id
and ext.worker_id = pg_worker_id
and ext.payment_schedule_id = ra.applied_payment_schedule_id
and ra.gl_date <= pg_in_as_of_date_low
and ra.status in ('APP','ACTIVITY')
group by ra.event_id,
ext.payment_schedule_id,
ext.amt_due_remaining
UNION ALL
select /*+ leading(ext) index(adj AR_ADJUSTMENTS_N3)*/
adj.event_id,
ext.payment_schedule_id,
ext.amt_due_remaining,
'ADJ' source_identifier
from ar_aging_extract ext,
ar_adjustments adj
where ext.parent_request_id = pg_parent_request_id
and ext.worker_id = pg_worker_id
and ext.payment_schedule_id = adj.payment_schedule_id
and adj.gl_date <= pg_in_as_of_date_low
and nvl(postable,'Y') = 'Y'
group by adj.event_id,
ext.payment_schedule_id,
ext.amt_due_remaining
UNION ALL
select /*+ leading(ext) index(ctlgd RA_CUST_TRX_LINE_GL_DIST_N6)*/
ctlgd.event_id,
ext.payment_schedule_id,
ext.amt_due_remaining,
'CTLGD' source_identifier
from ar_aging_extract ext,
ar_payment_schedules ps,
ra_cust_trx_line_gl_dist ctlgd
where ext.parent_request_id = pg_parent_request_id
and ext.worker_id = pg_worker_id
and ext.payment_schedule_id = ps.payment_schedule_id
and ps.customer_trx_id = ctlgd.customer_trx_id
and ctlgd.gl_date <= pg_in_as_of_date_low
group by ctlgd.event_id,
ext.payment_schedule_id,
ext.amt_due_remaining
) age_ps
where hdr.application_id = 222
and ae.application_id = 222
and xdl.application_id = 222
and hdr.ledger_id = pg_set_of_books_id
and ae.ae_header_id = hdr.ae_header_id
and ae.accounting_class_code = 'RECEIVABLE'
and hdr.accounting_entry_status_code = 'F'
and hdr.event_id = age_ps.event_id
and xdl.ae_header_id = hdr.ae_header_id
and xdl.event_id = hdr.event_id
and xdl.ae_line_num = ae.ae_line_num
and ( age_ps.source_identifier <> 'RA_APPLIED_TO' OR
xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL') --to restrict CM accounting records
group by ae.code_combination_id,
age_ps.payment_schedule_id,
age_ps.amt_due_remaining,
ae.currency_code
order by payment_schedule_id
);
INSERT INTO ar_aging_mfar_extract
VALUES l_aging_mfar_tab(i);
SELECT value INTO l_nls_numeric_char
FROM v$NLS_PARAMETERS
WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
build_select_stmt( p_out_invoice_query => l_out_invoice_query,
p_out_receipt_query => l_out_unapp_query,
p_out_riskinfo_query => l_out_riskinfo_query,
p_out_br_query => l_out_br_query);