eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FII_AR_TRAN_DETAIL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 12


l_select		VARCHAR2(15000);

Line: 21


l_bal_select		VARCHAR2(1000);

Line: 86


l_bal_select :=
' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';

Line: 106


l_bal_select :=
' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';

Line: 124


l_bal_select :=
' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';

Line: 141


l_bal_select :=
' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';

Line: 158


l_bal_select :=
' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';

Line: 174


l_bal_select :=
' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';

Line: 188


l_bal_select :=
' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';

Line: 201


l_bal_select :=
' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
+r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';

Line: 212


l_bal_select := NULL;

Line: 246


l_source_drill := ' DECODE(f.order_ref_number,NULL,'''', ''pFunctionName=ONT_PORTAL_ORDERDETAILS&HeaderId=''||(select ooh.header_id
				from oe_order_headers_all ooh
				where f.order_ref_number    =
to_char(ooh.order_number) and rownum=1  ) ) ';

Line: 276


l_select := '
SELECT
FII_AR_CUST_TRX_ID,
FII_AR_ACCT_NUM,
FII_AR_TRAN_NUM,
FII_AR_TERM_SEQ_NUM,
NULL FII_AR_ORD_HDR_ID,
FII_AR_TRAN_CLASS,
FII_AR_TRAN_CLASS_CODE,
FII_AR_TRAN_TYPE,
FII_AR_TRAN_DATE,
FII_AR_GL_DATE,
FII_AR_FIRST_DUE_DATE,
FII_AR_TRAN_CURR,
FII_AR_TRAN_AMT,
FII_AR_ORIG_AMT,
FII_AR_INAGE_RNG_AMT,
FII_AR_BALANCE_AMT,
FII_AR_DISPUTE_AMT,
FII_AR_TERMS,
FII_AR_SOURCE,
SUM(FII_AR_ORIG_AMT) over()     FII_AR_ORIG_AMT_GT,
SUM(FII_AR_INAGE_RNG_AMT) over() FII_AR_INAGE_RNG_AMT_GT,
SUM(FII_AR_BALANCE_AMT) over()  FII_AR_BALANCE_AMT_GT,
SUM(FII_AR_DISPUTE_AMT) over()  FII_AR_DISPUTE_AMT_GT,
FII_AR_TRAN_NUM_DRILL,
FII_AR_FIRST_DUE_DATE_DRILL,
FII_AR_BALANCE_AMT_DRILL,
FII_AR_SOURCE_DRILL
FROM
(
SELECT
f.customer_trx_id 	FII_AR_CUST_TRX_ID,
FII_AR_ACCT_NUM,
FII_AR_TRAN_NUM,
rlk.meaning             FII_AR_TRAN_CLASS,
f.class			FII_AR_TRAN_CLASS_CODE,
ctype.description       FII_AR_TRAN_TYPE,
FII_AR_TERM_SEQ_NUM,
FII_AR_TRAN_DATE,
FII_AR_GL_DATE,
FII_AR_FIRST_DUE_DATE,
FII_AR_TRAN_CURR,
FII_AR_TRAN_AMT,
FII_AR_ORIG_AMT,
FII_AR_INAGE_RNG_AMT,
FII_AR_BALANCE_AMT,
FII_AR_DISPUTE_AMT,
rterm.description       FII_AR_TERMS,
rsource.description     FII_AR_SOURCE,
'||l_tran_num_drill||' FII_AR_TRAN_NUM_DRILL,
'''||l_first_due_date_drill||''' FII_AR_FIRST_DUE_DATE_DRILL,
'||l_balance_drill||' FII_AR_BALANCE_AMT_DRILL,
'||l_source_drill||' FII_AR_SOURCE_DRILL
FROM
(SELECT customer_trx_id,SUM(current_dispute_amount_prim+past_due_dispute_amount_prim) FII_AR_DISPUTE_AMT
FROM fii_ar_aging_disputes d
WHERE event_date <= :ASOF_DATE GROUP BY customer_trx_id) d,
ra_cust_trx_types_all ctype,
ar_lookups rlk,
ra_terms_tl rterm,
ra_batch_sources_all rsource,
(select /*+ leading(gt) cardinality(gt 1) */
f.customer_trx_id,
hz_cust.account_number          FII_AR_ACCT_NUM,
f.transaction_number            FII_AR_TRAN_NUM,
f.class,
1 				FII_AR_TERM_SEQ_NUM,
f.order_ref_number,
f.cust_trx_type_id,
MIN(f.trx_date)                      FII_AR_TRAN_DATE,
MIN(f.gl_date)                       FII_AR_GL_DATE,
MIN(f.due_date)                 FII_AR_FIRST_DUE_DATE,
f.invoice_currency_code  	FII_AR_TRAN_CURR,
f.invoice_currency_code||'' ''||TO_CHAR(SUM(case when aging_flag = ''N'' and action = '#'Transaction''
                                                   then f.amount_due_original_trx
                                                 else 0 end),''999,999,999,999'') FII_AR_TRAN_AMT,
SUM(case when aging_flag = ''N'' and action = '#'Transaction''
             then f.amount_due_original'||fii_ar_util_pkg.g_col_curr_suffix||'
         else 0 end) FII_AR_ORIG_AMT,
 '||l_inag_rng||' FII_AR_INAGE_RNG_AMT,
SUM( '||l_bal_select||') FII_AR_BALANCE_AMT,
f.term_id,
f.batch_source_id,
f.bill_to_customer_id,
f.org_id
FROM
fii_ar_pmt_schedules_f f,
fii_ar_aging_receivables r,
hz_cust_accounts hz_cust,
fii_ar_summary_gt gt
'||l_collector_from||l_industry_from||'
WHERE f.bill_to_customer_id= hz_cust.cust_account_id
AND f.filter_date <=  :ASOF_DATE
AND r.event_date  <=  :ASOF_DATE
AND f.payment_schedule_id = r.payment_schedule_id
AND f.org_id = r.org_id
AND f.org_id = gt.org_id
'||l_party_where||l_cust_acc_where||l_collector_where||l_industry_where||l_func_where||'
group by f.customer_trx_id,f.org_id, f.transaction_number, f.class,
f.order_ref_number,
f.cust_trx_type_id,
f.invoice_currency_code,
f.term_id, f.batch_source_id,
f.bill_to_customer_id, hz_cust.account_number
 ) f
WHERE f.cust_trx_type_id = ctype.cust_trx_type_id
 '||l_bal_where||'
AND f.org_id=ctype.org_id
AND f.customer_trx_id = d.customer_trx_id(+)
AND f.class = rlk.lookup_code
AND rlk.lookup_type= ''INV/CM/ADJ''
AND f.term_id = rterm.term_id(+)
AND DECODE(rterm.term_id, NULL, USERENV(''LANG''),rterm.language) = USERENV(''LANG'')
AND f.batch_source_id = rsource.batch_source_id
AND f.org_id=rsource.org_id
) '||l_order_by||'
';

Line: 395


fii_ar_util_pkg.bind_variable(l_select, p_page_parameter_tbl, tran_detail_sql, tran_detail_output);