[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT FII_AR_RCT_ACCT_NUM,
FII_AR_RCT_NUM,
FII_AR_RCT_DATE,
FII_AR_RCT_GL_DATE,
FII_AR_RCT_PAY_METHOD,
FII_AR_RCT_STATUS,
to_char(sum(receipt_amount),''999,999,999,999'')|| '' '' || FII_AR_RCT_CURRENCY FII_AR_RCT_AMT_RCURR,
sum(FII_AR_RCT_AMT) FII_AR_RCT_AMT ,
sum(FII_AR_RCT_APP_AMT) FII_AR_RCT_APP_AMT,
sum(FII_AR_RCT_EARNED_DCT) FII_AR_RCT_EARNED_DCT,
sum(FII_AR_RCT_UNEARNED_DCT)FII_AR_RCT_UNEARNED_DCT ,
sum(sum(FII_AR_RCT_AMT)) over() FII_AR_GT_RCT_AMT,
sum(sum(FII_AR_RCT_APP_AMT)) over() FII_AR_GT_RCT_APP_AMT,
sum(sum(FII_AR_RCT_EARNED_DCT)) over() FII_AR_GT_RCT_EARNED_DCT,
sum(sum(FII_AR_RCT_UNEARNED_DCT)) over() FII_AR_GT_RCT_UNEARNED_DCT,
decode(FII_AR_RCT_NUM, NULL, NULL, ''' || l_rct_num_url || ''') FII_AR_RCT_NUM_DRILL,
decode(sum(FII_AR_RCT_AMT), 0, NULL, NULL, NULL,'''|| l_rct_amt_url ||''') FII_AR_RCT_AMT_DRILL,
FII_AR_RCT_CURRENCY,
FII_AR_CASH_RECEIPT_ID
FROM (
SELECT
NVL(fact.account_number,'''||l_unid_message||''') FII_AR_RCT_ACCT_NUM,
FII_AR_RCT_NUM FII_AR_RCT_NUM,
FII_AR_RCT_DATE FII_AR_RCT_DATE,
FII_AR_RCT_GL_DATE FII_AR_RCT_GL_DATE,
m.name FII_AR_RCT_PAY_METHOD,
hist.status FII_AR_RCT_STATUS,
receipt_amount receipt_amount,
FII_AR_RCT_AMT FII_AR_RCT_AMT,
FII_AR_RCT_APP_AMT FII_AR_RCT_APP_AMT,
FII_AR_RCT_EARNED_DCT FII_AR_RCT_EARNED_DCT,
FII_AR_RCT_UNEARNED_DCT FII_AR_RCT_UNEARNED_DCT,
FII_AR_RCT_CURRENCY FII_AR_RCT_CURRENCY,
FII_AR_CASH_RECEIPT_ID FII_AR_CASH_RECEIPT_ID,
fact.cust_account_id cust_account_id
FROM(
SELECT /*+ no_merge leading(v) cardinality(v 1) */ f.receipt_number FII_AR_RCT_NUM,
acct.account_number account_number,
f.receipt_date FII_AR_RCT_DATE,
f.gl_date FII_AR_RCT_GL_DATE,
sum(f.amount_applied_rct) receipt_amount,
sum(f.amount_applied_rct' || l_currency || ') FII_AR_RCT_AMT,
CASE WHEN f.application_status = ''APP''
AND f.filter_date <= :ASOF_DATE
AND f.filter_date >= :CURR_PERIOD_START
THEN sum(f.amount_applied_rct' || l_currency || ')
ELSE 0
END FII_AR_RCT_APP_AMT,
sum(f.earned_discount_amount' || l_currency || ') FII_AR_RCT_EARNED_DCT,
sum(f.unearned_discount_amount' || l_currency || ') FII_AR_RCT_UNEARNED_DCT,
f.currency_code FII_AR_RCT_CURRENCY,
f.cash_receipt_id FII_AR_CASH_RECEIPT_ID,
f.collector_bill_to_customer_id cust_account_id ,
f.receipt_method_id receipt_method_id,
f.collector_bill_to_site_use_id collector_bill_to_site_use_id,
v.collector_id collector_id,
acct.account_owner_party_id Account_Owner_Party_ID,
v.class_Category class_category,
v.class_code class_code
FROM fii_ar_receipts_f f,
'||l_from_table || '
'||l_gt_table_name||'
WHERE f.org_id = v.org_id
AND ((f.header_filter_date <= :ASOF_DATE
AND f.header_filter_date >= :CURR_PERIOD_START)
AND
(f.filter_date <= :ASOF_DATE
AND f.filter_date >= :CURR_PERIOD_START))
'|| l_where_clause ||'
GROUP BY f.receipt_number,acct.account_number,f.receipt_date, f.gl_date,
f.currency_code, f.cash_receipt_id,
f.collector_bill_to_customer_id,
f.receipt_method_id,f.filter_date,f.application_status,
f.collector_bill_to_site_use_id ,
v.collector_id ,
acct.account_owner_party_id,
v.class_Category ,
v.class_code)fact,
ar_receipt_methods m,
-- hz_cust_accounts ca,
ar_cash_receipt_history_all hist '||l_dim_from_table||'
WHERE
fact.receipt_method_id = m.receipt_method_id
AND hist.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID
AND hist.cash_receipt_history_id = (select /*+ no_merge */ max(cash_receipt_history_id) from
ar_cash_receipt_history_all hist1 where
hist1.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID)
-- AND fact.cust_account_id = ca.cust_account_id(+)
'||l_dim_where_clause||')
GROUP BY
FII_AR_RCT_ACCT_NUM,
FII_AR_RCT_NUM,
FII_AR_RCT_DATE,
FII_AR_RCT_GL_DATE,
FII_AR_RCT_PAY_METHOD,
FII_AR_RCT_STATUS,
CUST_ACCOUNT_ID,
FII_AR_RCT_CURRENCY,
FII_AR_CASH_RECEIPT_ID
'|| l_order_by ;
l_sqlstmt := 'SELECT
FII_AR_RCT_ACCT_NUM,
FII_AR_RCT_NUM,
FII_AR_RCT_DATE,
FII_AR_RCT_GL_DATE,
FII_AR_RCT_PAY_METHOD,
FII_AR_RCT_STATUS,
to_char(sum(receipt_amount),''999,999,999,999'')|| '' '' || FII_AR_RCT_CURRENCY FII_AR_RCT_AMT_RCURR,
sum(FII_AR_RCT_AMT) FII_AR_RCT_AMT,
sum(FII_AR_RCT_APP_AMT)FII_AR_RCT_APP_AMT ,
sum(FII_AR_RCT_EARNED_DCT) FII_AR_RCT_EARNED_DCT,
sum(FII_AR_RCT_UNEARNED_DCT) FII_AR_RCT_UNEARNED_DCT,
sum(sum(FII_AR_GT_RCT_AMT)) over() FII_AR_GT_RCT_AMT,
sum(sum(FII_AR_GT_RCT_APP_AMT)) over() FII_AR_GT_RCT_APP_AMT,
sum(sum(FII_AR_GT_RCT_EARNED_DCT)) over() FII_AR_GT_RCT_EARNED_DCT,
sum(sum(FII_AR_GT_RCT_UNEARNED_DCT)) over() FII_AR_GT_RCT_UNEARNED_DCT,
decode(FII_AR_RCT_NUM, NULL, NULL, ''' ||
l_rct_num_url || ''') FII_AR_RCT_NUM_DRILL,
decode(sum(FII_AR_RCT_AMT), 0, NULL, NULL, NULL,
''' || l_rct_amt_url ||
''') FII_AR_RCT_AMT_DRILL,
FII_AR_RCT_CURRENCY,
FII_AR_CASH_RECEIPT_ID FROM(
SELECT
NVL(fact.account_number,'''||l_unid_message||''') FII_AR_RCT_ACCT_NUM,
FII_AR_RCT_NUM FII_AR_RCT_NUM,
FII_AR_RCT_DATE FII_AR_RCT_DATE,
FII_AR_RCT_GL_DATE FII_AR_RCT_GL_DATE,
m.name FII_AR_RCT_PAY_METHOD,
hist.status FII_AR_RCT_STATUS,
0 receipt_amount,
0 FII_AR_RCT_AMT,
FII_AR_RCT_APP_AMT FII_AR_RCT_APP_AMT,
0 FII_AR_RCT_EARNED_DCT,
0 FII_AR_RCT_UNEARNED_DCT,
0 FII_AR_GT_RCT_AMT,
FII_AR_GT_RCT_APP_AMT FII_AR_GT_RCT_APP_AMT,
0 FII_AR_GT_RCT_EARNED_DCT,
0 FII_AR_GT_RCT_UNEARNED_DCT,
FII_AR_RCT_CURRENCY FII_AR_RCT_CURRENCY,
FII_AR_CASH_RECEIPT_ID FII_AR_CASH_RECEIPT_ID,
fact.cust_account_id cust_account_id
FROM (
SELECT /*+ no_merge leading(v) cardinality(v 1)*/
acct.account_number account_number,
f.receipt_number FII_AR_RCT_NUM,
f.receipt_date FII_AR_RCT_DATE,
f.gl_date FII_AR_RCT_GL_DATE,
sum(f.amount_applied_rct' || l_currency || ') FII_AR_RCT_APP_AMT,
sum(f.amount_applied_rct' || l_currency || ') FII_AR_GT_RCT_APP_AMT,
f.currency_code FII_AR_RCT_CURRENCY,
f.cash_receipt_id FII_AR_CASH_RECEIPT_ID,
f.collector_bill_to_customer_id cust_account_id,
f.receipt_method_id receipt_method_id,
f.collector_bill_to_site_use_id collector_bill_to_site_use_id,
v.collector_id collector_id,
acct.account_owner_party_id Account_Owner_Party_ID,
v.class_Category class_category,
v.class_code class_code
FROM fii_ar_receipts_f f,
'||l_from_table || '
'||l_gt_table_name||'
WHERE f.org_id = v.org_id
AND f.filter_date <= :ASOF_DATE
AND f.filter_date >= :CURR_PERIOD_START
AND f.application_status = ''APP''
AND (f.applied_payment_schedule_id > 0 OR f.applied_payment_schedule_id IS NULL) '
|| l_where_clause ||
' GROUP BY acct.account_number ,f.receipt_number, f.receipt_date, f.gl_date,
f.currency_code, f.cash_receipt_id,
f.collector_bill_to_customer_id,
f.receipt_method_id,
f.collector_bill_to_site_use_id ,
v.collector_id ,
acct.account_owner_party_id,
v.class_Category ,
v.class_code) fact,
ar_receipt_methods m,
ar_cash_receipt_history_all hist
'||l_dim_from_table||'
WHERE
fact.receipt_method_id = m.receipt_method_id
AND hist.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID
AND hist.cash_receipt_history_id = (select /*+ no_merge */ max(cash_receipt_history_id) from
ar_cash_receipt_history_all hist1 where
hist1.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID)
'||l_dim_where_clause||'
UNION ALL
SELECT NVL(fact.account_number,'''||l_unid_message||''') FII_AR_RCT_ACCT_NUM,
FII_AR_RCT_NUM FII_AR_RCT_NUM,
FII_AR_RCT_DATE FII_AR_RCT_DATE,
FII_AR_RCT_GL_DATE FII_AR_RCT_GL_DATE,
m.name FII_AR_RCT_PAY_METHOD,
hist.status FII_AR_RCT_STATUS,
receipt_amount receipt_amount,
FII_AR_RCT_AMT FII_AR_RCT_AMT,
0 FII_AR_RCT_APP_AMT,
FII_AR_RCT_EARNED_DCT FII_AR_RCT_EARNED_DCT,
FII_AR_RCT_UNEARNED_DCT FII_AR_RCT_UNEARNED_DCT,
FII_AR_GT_RCT_AMT FII_AR_GT_RCT_AMT,
0 FII_AR_GT_RCT_APP_AMT,
FII_AR_GT_RCT_EARNED_DCT FII_AR_GT_RCT_EARNED_DCT,
FII_AR_GT_RCT_UNEARNED_DCT FII_AR_GT_RCT_UNEARNED_DCT,
FII_AR_RCT_CURRENCY FII_AR_RCT_CURRENCY,
FII_AR_CASH_RECEIPT_ID FII_AR_CASH_RECEIPT_ID ,
fact.cust_account_id cust_account_id FROM (
SELECT /*+ no_merge '||l_index_hint||' leading(v) cardinality(v 1)*/
acct.account_number account_number,
f.receipt_number FII_AR_RCT_NUM,
f.receipt_date FII_AR_RCT_DATE,
f.gl_date FII_AR_RCT_GL_DATE,
sum(f.amount_applied_rct) receipt_amount,
sum(f.amount_applied_rct' || l_currency || ') FII_AR_RCT_AMT,
sum(f.earned_discount_amount' || l_currency || ') FII_AR_RCT_EARNED_DCT,
sum(f.unearned_discount_amount'||l_currency||') FII_AR_RCT_UNEARNED_DCT,
sum(f.amount_applied_rct' || l_currency || ') FII_AR_GT_RCT_AMT,
sum(f.earned_discount_amount' || l_currency || ') FII_AR_GT_RCT_EARNED_DCT,
sum(f.unearned_discount_amount'||l_currency||') FII_AR_GT_RCT_UNEARNED_DCT,
f.currency_code FII_AR_RCT_CURRENCY,
f.cash_receipt_id FII_AR_CASH_RECEIPT_ID,
f.collector_bill_to_customer_id cust_account_id,
f.receipt_method_id receipt_method_id,
f.collector_bill_to_site_use_id collector_bill_to_site_use_id,
v.collector_id collector_id,
acct.account_owner_party_id Account_Owner_Party_ID,
v.class_Category class_category,
v.class_code class_code
FROM fii_ar_receipts_f f,
'||l_from_table || '
'||l_gt_table_name||'
WHERE f.org_id = v.org_id
AND f.filter_date <= :ASOF_DATE
and f.cash_receipt_id in
(select /*+ no_merge */ distinct cash_receipt_id from
( select /*+ no_merge leading(v1) cardinality(v1 1)*/ v1.collector_id collector_id,
v1.class_category class_category,
v1.class_code class_code,
acct1.account_owner_party_id account_owner_party_id
,cash_receipt_id
,f1.collector_bill_to_customer_id cust_account_id
,f1.collector_bill_to_site_use_id collector_bill_to_site_use_id
from FII_AR_RECEIPTS_F f1
, '||l_from_table1 || '
'||l_gt_table_name1||'
where f1.org_id = v1.org_id
AND f1.filter_date <= :ASOF_DATE
AND f1.filter_date >= :CURR_PERIOD_START
AND f1.application_status = ''APP''
and (f1.applied_payment_schedule_id > 0 OR f1.applied_payment_schedule_id IS NULL)
'|| l_where_clause1 ||'
) fact1 '||l_dim_from_table1||'
WHERE
'||l_dim_where_clause1||'
)
AND (f.applied_payment_schedule_id > 0 OR f.applied_payment_schedule_id IS NULL)'
|| l_where_clause ||
' GROUP BY acct.account_number,f.receipt_number, f.receipt_date, f.gl_date,
f.currency_code, f.cash_receipt_id,
f.collector_bill_to_customer_id,f.receipt_method_id,
f.collector_bill_to_site_use_id ,
v.collector_id ,
acct.account_owner_party_id,
v.class_Category ,
v.class_code) fact,
ar_receipt_methods m,
ar_cash_receipt_history_all hist
'||l_dim_from_table||'
WHERE
fact.receipt_method_id = m.receipt_method_id
AND hist.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID
AND hist.cash_receipt_history_id = (select /*+ no_merge */ max(cash_receipt_history_id) from
ar_cash_receipt_history_all hist1 where
hist1.cash_receipt_id = fact.FII_AR_CASH_RECEIPT_ID)
'||l_dim_where_clause||' )
GROUP BY
FII_AR_RCT_ACCT_NUM,
FII_AR_RCT_NUM,
FII_AR_RCT_DATE,
FII_AR_RCT_GL_DATE,
FII_AR_RCT_PAY_METHOD,
FII_AR_RCT_STATUS,
FII_AR_RCT_CURRENCY,
FII_AR_CASH_RECEIPT_ID,
CUST_ACCOUNT_ID '
|| l_order_by;