[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 /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ v.viewby VIEWBY,
v.viewby_code VIEWBYID,
-sum(f.unapp_amount) FII_AR_NET_REC_AMT,
NULL FII_AR_BILLED_AMT,
NULL FII_AR_VIEW_BY_DRILL,
NULL FII_AR_NET_REC_AMT_DRILL
FROM fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
(
SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) '||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_customer_where
|| l_child_party_where
|| l_customer_acc_where
|| l_industry_where || '
GROUP BY v.viewby_code, v.VIEWBY' || l_group_by || ' , v.record_type_id';
SELECT VIEWBY,
VIEWBYID,
round(sum(FII_AR_NET_REC_AMT) * :DSO_PERIOD / NULLIF(sum(FII_AR_BILLED_AMT),0)) FII_AR_DSO,
sum(FII_AR_NET_REC_AMT) FII_AR_NET_REC_AMT,
sum(FII_AR_BILLED_AMT) FII_AR_BILLED_AMT,
max(FII_AR_VIEW_BY_DRILL) FII_AR_VIEW_BY_DRILL,
max(FII_AR_NET_REC_AMT_DRILL) FII_AR_NET_REC_AMT_DRILL,
(sum(sum(FII_AR_NET_REC_AMT)) over() * :DSO_PERIOD / NULLIF(sum(sum(FII_AR_BILLED_AMT)) over(),0)) FII_AR_GT_DSO,
sum(sum(FII_AR_NET_REC_AMT)) over() FII_AR_GT_NET_REC_AMT,
sum(sum(FII_AR_BILLED_AMT)) over() FII_AR_GT_BILLED_AMT
FROM
(
SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
v.viewby VIEWBY,
v.viewby_code VIEWBYID,
CASE WHEN bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
' || l_net_rec_column || '
ELSE
NULL
END FII_AR_NET_REC_AMT,
CASE WHEN bitand(v.record_type_id, :DSO_BITAND) = :DSO_BITAND THEN
sum(f.billed_amount)
ELSE
NULL
END FII_AR_BILLED_AMT,
' || l_cust_drill || ' FII_AR_VIEW_BY_DRILL,
' || l_net_rec_sum_drill || ' FII_AR_NET_REC_AMT_DRILL
FROM fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
(
SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) '||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
OR bitand(cal.record_type_id, :DSO_BITAND) = :DSO_BITAND)
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_customer_where
|| l_child_party_where
|| l_customer_acc_where
|| l_industry_where || '
GROUP BY v.viewby_code, v.VIEWBY' || l_group_by || ' , v.record_type_id
' || l_unapp_query || '
) inline_query
GROUP BY VIEWBYID, VIEWBY
' || l_order_by;
SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
v.sequence FII_EFFECTIVE_NUM,
CASE WHEN bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
' || l_net_rec_column || '
ELSE
NULL
END FII_AR_NET_REC_AMT,
NULL FII_AR_NET_REC_PRIOR_AMT,
CASE WHEN bitand(v.record_type_id, :DSO_BITAND) = :DSO_BITAND THEN
sum(f.billed_amount)
ELSE
NULL
END FII_AR_BILLED_AMT,
NULL FII_AR_BILLED_PRIOR_AMT,
sum(f.inv_amount) inv_amount, sum(f.dm_amount) dm_amount, sum(f.cb_amount) cb_amount, sum(f.br_amount) br_amount, sum(f.dep_amount) dep_amount
FROM
fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
(
SELECT /*+ no_merge cardinality(gt 1)*/ *
FROM fii_ar_summary_gt gt,
(
SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) */ per.sequence, cal.time_id, cal.period_type_id, per.start_date, per.end_date, cal.record_type_id
FROM fii_time_ent_period per, fii_time_structures cal
WHERE per.end_date = last_day(:ASOF_DATE)
AND cal.report_date = :ASOF_DATE
AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
OR bitand(cal.record_type_id, :DSO_BITAND) = :DSO_BITAND)
) cal_per
) 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_child_party_where || '
' || l_industry_where || '
GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id';
SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
v.sequence FII_EFFECTIVE_NUM,
-sum(f.unapp_amount) FII_AR_NET_REC_AMT,
NULL FII_AR_NET_REC_PRIOR_AMT,
NULL FII_AR_BILLED_AMT,
NULL FII_AR_BILLED_PRIOR_AMT,
NULL inv_amount, NULL dm_amount, NULL cb_amount, NULL br_amount, NULL dep_amount
FROM
fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
(
SELECT /*+ no_merge cardinality(gt 1) */ *
FROM fii_ar_summary_gt gt,
(
SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) */ per.sequence, cal.time_id, cal.period_type_id, per.start_date, per.end_date, cal.record_type_id
FROM fii_time_ent_period per, fii_time_structures cal
WHERE per.end_date = last_day(:ASOF_DATE)
AND cal.report_date = :ASOF_DATE
AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE)
) cal_per
) 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_child_party_where || '
' || l_industry_where || '
GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id';
SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
v.sequence FII_EFFECTIVE_NUM,
CASE WHEN v.start_date > :SD_PRIOR THEN
-sum(f.unapp_amount) ELSE NULL END FII_AR_NET_REC_AMT,
CASE WHEN v.end_date <= last_day(:SD_PRIOR) THEN
-sum(f.unapp_amount) ELSE NULL END FII_AR_NET_REC_PRIOR_AMT,
NULL FII_AR_BILLED_AMT,
NULL FII_AR_BILLED_PRIOR_AMT,
NULL inv_amount, NULL dm_amount, NULL cb_amount, NULL br_amount, NULL dep_amount
FROM
fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
(
SELECT /*+ no_merge cardinality(gt 1) */ *
FROM fii_ar_summary_gt gt,
(
SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) */ per.sequence, cal.time_id, cal.period_type_id, per.start_date, per.end_date, cal.record_type_id
FROM fii_time_ent_period per, fii_time_structures cal
WHERE per.start_date > :SD_PRIOR_PRIOR AND per.end_date <= :ASOF_DATE
AND cal.report_date = per.end_date
AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE)
) cal_per
) 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_child_party_where || '
' || l_industry_where || '
GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id';
SELECT
cy_per.name VIEWBY,
round(sum(FII_AR_NET_REC_AMT) * :DSO_PERIOD / NULLIF(sum(FII_AR_BILLED_AMT),0)) FII_AR_DSO,
sum(FII_AR_NET_REC_AMT) FII_AR_NET_REC_AMT,
sum(FII_AR_BILLED_AMT) FII_AR_BILLED_AMT,
round(sum(FII_AR_NET_REC_AMT) * :DSO_PERIOD / NULLIF(sum(FII_AR_BILLED_AMT),0)) FII_AR_DSO_G,
round(sum(FII_AR_NET_REC_PRIOR_AMT) * :DSO_PERIOD / NULLIF(sum(FII_AR_BILLED_PRIOR_AMT),0)) FII_AR_PRIOR_DSO_G,
round(sum(FII_AR_NET_REC_AMT) * :DSO_PERIOD / NULLIF(sum(FII_AR_BILLED_AMT),0)) FII_AR_CURRENT_DSO_G,
CASE WHEN :ASOF_DATE >= cy_per.start_date AND :ASOF_DATE <= cy_per.end_date THEN
DECODE(' || g_open_rec_column_dsot || ', 0, '''', ''' || l_net_rec_sum_drill || ''')
ELSE
''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_NET_REC_SUM&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''
END FII_AR_NET_REC_AMT_DRILL
FROM
fii_time_ent_period cy_per,
(
-- Query to return data for months other than current month
SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
v.sequence FII_EFFECTIVE_NUM,
CASE WHEN v.start_date > :SD_PRIOR AND bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
' || l_net_rec_column || '
ELSE
NULL
END FII_AR_NET_REC_AMT,
CASE WHEN v.end_date <= last_day(:SD_PRIOR) AND bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
' || l_net_rec_column || '
ELSE
NULL
END FII_AR_NET_REC_PRIOR_AMT,
CASE WHEN v.start_date > :SD_PRIOR AND bitand(v.record_type_id, :DSO_BITAND) = :DSO_BITAND THEN
sum(f.billed_amount)
ELSE
NULL
END FII_AR_BILLED_AMT,
CASE WHEN v.end_date <= last_day(:SD_PRIOR) AND bitand(v.record_type_id, :DSO_BITAND) = :DSO_BITAND THEN
sum(f.billed_amount)
ELSE
NULL
END FII_AR_BILLED_PRIOR_AMT,
sum(f.inv_amount) inv_amount, sum(f.dm_amount) dm_amount, sum(f.cb_amount) cb_amount, sum(f.br_amount) br_amount, sum(f.dep_amount) dep_amount
FROM
fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
(
SELECT /*+ no_merge cardinality(gt 1) */ *
FROM fii_ar_summary_gt gt,
(
SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) */ per.sequence, cal.time_id, cal.period_type_id, per.start_date, per.end_date, cal.record_type_id
FROM fii_time_ent_period per, fii_time_structures cal
WHERE per.start_date > :SD_PRIOR_PRIOR AND per.end_date <= :ASOF_DATE
AND cal.report_date = per.end_date
AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
OR bitand(cal.record_type_id, :DSO_BITAND) = :DSO_BITAND)
) cal_per
) 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_child_party_where || '
' || l_industry_where || '
GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id
' || l_curr_query || '
' || l_unapp_amount_query || '
' || l_curr_unapp_query || '
) inline_query
WHERE
cy_per.start_date <= :ASOF_DATE
AND cy_per.start_date > :SD_PRIOR
AND cy_per.sequence = inline_query.fii_effective_num (+)
GROUP BY inline_query.fii_effective_num, cy_per.sequence, cy_per.start_date, cy_per.name, cy_per.end_date
ORDER BY cy_per.start_date';