[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_view_by VARCHAR2(240); -- Variable to store the viewby based on viewby selected in the report
SELECT
inline_query.viewby VIEWBY,
inline_query.viewby_code VIEWBYID,
sum(inline_query.inv_amount) + sum(inline_query.dm_amount) + sum(inline_query.cb_amount) + sum(inline_query.br_amount)
+ sum(inline_query.dep_amount) + sum(inline_query.on_account_credit_amount) - sum(inline_query.unapp_dep_amount)
- nvl(sum(inline_query.unapp_amount), 0) - sum(inline_query.on_account_cash_amount) - sum(inline_query.claim_amount)
- sum(inline_query.prepayment_amount) FII_AR_NET_REC_AMT,
sum(inline_query.inv_amount) FII_AR_INV_AMT,
sum(inline_query.dm_amount) FII_AR_DEB_MEMO_AMT,
sum(inline_query.cb_amount) FII_AR_CHARGEBACK_AMT,
sum(inline_query.br_amount) FII_AR_BILLS_REC_AMT,
sum(inline_query.dep_amount) FII_AR_UNP_DEP_AMT,
sum(inline_query.on_account_credit_amount) FII_AR_ON_ACC_CREDIT_AMT,
sum(inline_query.unapp_dep_amount) FII_AR_UNAPP_DEP_AMT,
nvl(sum(inline_query.unapp_amount), 0) FII_AR_UNAPP_AMT,
sum(inline_query.on_account_cash_amount) FII_AR_ON_ACC_CASH_AMT,
sum(inline_query.claim_amount) FII_AR_CLAIMS_AMT,
sum(inline_query.prepayment_amount) FII_AR_PREPAYMENT_AMT,
' || l_viewby_drill || ' FII_AR_VIEW_BY_DRILL,
' || l_amt_inv_drill || ' FII_AR_INV_AMT_DRILL,
' || l_amt_dm_drill || ' FII_AR_DEB_MEMO_AMT_DRILL,
' || l_amt_cb_drill || ' FII_AR_CHARGEBACK_AMT_DRILL,
' || l_amt_unapp_drill || ' FII_AR_UNAPP_AMT_DRILL,
sum(sum(inline_query.inv_amount)) over() + sum(sum(inline_query.dm_amount)) over()
+ sum(sum(inline_query.cb_amount)) over() + sum(sum(inline_query.br_amount)) over()
+ sum(sum(inline_query.dep_amount)) over() + sum(sum(inline_query.on_account_credit_amount)) over()
- sum(sum(inline_query.unapp_dep_amount)) over() - nvl(sum(sum(inline_query.unapp_amount)) over(), 0)
- sum(sum(inline_query.on_account_cash_amount)) over() - sum(sum(inline_query.claim_amount)) over()
- sum(sum(inline_query.prepayment_amount)) over() FII_AR_GT_NET_REC_AMT,
sum(sum(inline_query.inv_amount)) over() FII_AR_GT_INV_AMT,
sum(sum(inline_query.dm_amount)) over() FII_AR_GT_DEB_MEMO_AMT,
sum(sum(inline_query.cb_amount)) over() FII_AR_GT_CHARGEBACK_AMT,
sum(sum(inline_query.br_amount)) over() FII_AR_GT_BILLS_REC_AMT,
sum(sum(inline_query.dep_amount)) over() FII_AR_GT_UNP_DEP_AMT,
sum(sum(inline_query.on_account_credit_amount)) over() FII_AR_GT_ON_ACC_CREDIT_AMT,
sum(sum(inline_query.unapp_dep_amount)) over() FII_AR_GT_UNAPP_DEP_AMT,
nvl(sum(sum(inline_query.unapp_amount)) over(), 0) FII_AR_GT_UNAPP_AMT,
sum(sum(inline_query.on_account_cash_amount)) over() FII_AR_GT_ON_ACC_CASH_AMT,
sum(sum(inline_query.claim_amount)) over() FII_AR_GT_CLAIMS_AMT,
sum(sum(inline_query.prepayment_amount)) over() FII_AR_GT_PREPAYMENT_AMT
FROM
(
SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
v.viewby, v.viewby_code,
f.inv_amount, f.dm_amount, f.cb_amount, f.br_amount, f.dep_amount,
f.on_account_credit_amount, f.unapp_dep_amount, NULL unapp_amount,
f.on_account_cash_amount, f.claim_amount, f.prepayment_amount
' || l_self_flag_where || ', v.is_leaf_flag, f.party_id
FROM
fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
(
SELECT /*+ no_merge '||l_gt_hint|| ' */ *
FROM fii_time_structures cal,
' || fii_ar_util_pkg.get_from_statement || ' gt
WHERE cal.report_date = :ASOF_DATE
AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
AND ' || fii_ar_util_pkg.get_where_statement || '
) v
WHERE
f.time_id = v.time_id
AND f.period_type_id = v.period_type_id
AND f.org_id = v.org_id
AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_collector_where
|| l_customer_where
|| l_child_party_where
|| l_customer_acc_where || '
UNION ALL
SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
v.viewby, v.viewby_code,
NULL inv_amount, NULL dm_amount, NULL cb_amount, NULL br_amount, NULL dep_amount,
NULL on_account_credit_amount, NULL unapp_dep_amount, f.unapp_amount,
NULL on_account_cash_amount, NULL claim_amount, NULL prepayment_amount
' || l_self_flag_where_d || ', NULL is_leaf_flag, NULL party_id
FROM
fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
(
SELECT /*+ no_merge '||l_gt_hint|| ' */ *
FROM fii_time_structures cal,
' || fii_ar_util_pkg.get_from_statement || ' gt
WHERE cal.report_date = :ASOF_DATE
AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
AND ' || fii_ar_util_pkg.get_where_statement || '
) v
WHERE
f.time_id = v.time_id
AND f.period_type_id = v.period_type_id
AND f.org_id = v.org_id
AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' '
|| l_collector_where
|| l_customer_where
|| l_child_party_where
|| l_customer_acc_where || '
) inline_query
GROUP BY inline_query.viewby_code, inline_query.viewby'
|| l_order_by;