[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select VARCHAR2(15000);
l_select_curr_end_prd VARCHAR2(5000);
SELECT fii_time_api.ent_cper_end(fii_ar_util_pkg.g_as_of_date) INTO fii_ar_util_pkg.g_curr_per_end FROM DUAL;
SELECT fii_time_api.ent_pper_end(fii_ar_util_pkg.g_as_of_date) INTO fii_ar_util_pkg.g_prior_per_end FROM DUAL;
l_select_curr_end_prd :=' ';
l_select_curr_end_prd := ' UNION ALL
/* The select statment will return data for current month asofdate, if asofdate<> last day of month*/
SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
per.sequence sequence,
sum(f.total_open_amount) FII_AR_OPEN_REC,
sum(f.past_due_open_amount) FII_AR_PDUE_REC,
NULL FII_AR_PRIOR_PDUE_REC
FROM fii_time_ent_period per,
FII_AR_NET_REC'||fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
( SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ * FROM fii_time_structures cal, fii_ar_summary_gt gt
WHERE report_date = :ASOF_DATE
AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE ) t
WHERE f.time_id = t.time_id
AND f.period_type_id = t.period_type_id
AND f.org_id = t.org_id
AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_party_where||l_collector_where||'
AND per.end_date = :CURR_PERIOD_END
GROUP BY report_date, per.sequence
';
l_select :=
'
SELECT cy_per.name VIEWBY,
to_char(cy_per.end_date,''DD/MM/YYYY'') FII_AR_MONTH_END_DATE,
SUM(FII_AR_OPEN_REC) FII_AR_OPEN_REC,
SUM(FII_AR_PDUE_REC) FII_AR_PDUE_REC,
SUM(FII_AR_PRIOR_PDUE_REC) FII_AR_PRIOR_PDUE_REC,
SUM(FII_AR_PDUE_REC) FII_AR_PDUE_REC_G,
DECODE(SUM(FII_AR_PDUE_REC),NULL,NULL,0,NULL,DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
''&pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
''AS_OF_DATE=FII_AR_MONTH_END_DATE&pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'')) FII_AR_PDUE_REC_DRILL,
DECODE(SUM(FII_AR_OPEN_REC),NULL,NULL,0,NULL,DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
''&pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
''AS_OF_DATE=FII_AR_MONTH_END_DATE&pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'')) FII_AR_OPEN_REC_DRILL
FROM
fii_time_ent_period cy_per,
(SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
t.sequence sequence,
CASE WHEN t.report_date >= :SD_SDATE THEN
sum(f.total_open_amount) ELSE NULL END FII_AR_OPEN_REC,
CASE WHEN t.report_date >= :SD_SDATE THEN
sum(f.past_due_open_amount) ELSE NULL END FII_AR_PDUE_REC,
CASE WHEN t.report_date < :SD_SDATE THEN
sum(f.past_due_open_amount) ELSE NULL END FII_AR_PRIOR_PDUE_REC
FROM FII_AR_NET_REC'||fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
( SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ * FROM fii_time_structures cal, fii_ar_summary_gt gt,fii_time_ent_period per
WHERE
cal.report_date = per.end_date
and per.start_date <= '||l_end_date||'
AND per.start_date >= :SD_PRIOR_PRIOR
AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE ) t
WHERE f.time_id = t.time_id
AND f.period_type_id = t.period_type_id
AND f.org_id = t.org_id
AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_party_where||l_collector_where||'
GROUP BY report_date, t.sequence
'||l_select_curr_end_prd||'
) inline_view
WHERE cy_per.start_date <= :ASOF_DATE
AND cy_per.start_date > :SD_PRIOR
AND cy_per.sequence = inline_view.sequence (+)
GROUP BY cy_per.name,cy_per.end_date,cy_per.start_date
ORDER BY cy_per.start_date
';
fii_ar_util_pkg.bind_variable(l_select, p_page_parameter_tbl, open_rec_sql, open_rec_output);