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_COLL_EFF_IND_PKG dependencies on FII_AR_UTIL_PKG

Line 21: IF(instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN

17:
18: BEGIN
19:
20: --Frame the order by clause for the report sql
21: IF(instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN
22:
23: /*This means a particular sort column is clicked to have descending order
24: in which case we would want all the null values to appear last in the
25: report so add an NVL to that column*/

Line 27: l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));

23: /*This means a particular sort column is clicked to have descending order
24: in which case we would want all the null values to appear last in the
25: report so add an NVL to that column*/
26:
27: l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
28: l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
29:
30: ELSE
31:

Line 53: IF (fii_ar_util_pkg.g_curr_per_end = fii_ar_util_pkg.g_as_of_date) THEN

49: --The billed amount needs to be scaled to a month before calculating the CEI.
50:
51: -- These global variables will be used in Collection Effectiveness Index and Collection Effectiveness Reports
52: -- and trend report for the current period
53: IF (fii_ar_util_pkg.g_curr_per_end = fii_ar_util_pkg.g_as_of_date) THEN
54: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
55: g_scaling_factor := 4.35;
56: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
57: g_scaling_factor := 3;

Line 54: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN

50:
51: -- These global variables will be used in Collection Effectiveness Index and Collection Effectiveness Reports
52: -- and trend report for the current period
53: IF (fii_ar_util_pkg.g_curr_per_end = fii_ar_util_pkg.g_as_of_date) THEN
54: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
55: g_scaling_factor := 4.35;
56: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
57: g_scaling_factor := 3;
58: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN

Line 56: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN

52: -- and trend report for the current period
53: IF (fii_ar_util_pkg.g_curr_per_end = fii_ar_util_pkg.g_as_of_date) THEN
54: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
55: g_scaling_factor := 4.35;
56: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
57: g_scaling_factor := 3;
58: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
59: g_scaling_factor := 12;
60: ELSE

Line 58: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN

54: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
55: g_scaling_factor := 4.35;
56: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
57: g_scaling_factor := 3;
58: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
59: g_scaling_factor := 12;
60: ELSE
61: g_scaling_factor := 1;
62: END IF;

Line 64: g_scaling_factor := to_char((((fii_ar_util_pkg.g_as_of_date - fii_ar_util_pkg.g_curr_per_start) + 1) /30), '99999D9999999999999','NLS_NUMERIC_CHARACTERS=''.,');

60: ELSE
61: g_scaling_factor := 1;
62: END IF;
63: ELSE
64: g_scaling_factor := to_char((((fii_ar_util_pkg.g_as_of_date - fii_ar_util_pkg.g_curr_per_start) + 1) /30), '99999D9999999999999','NLS_NUMERIC_CHARACTERS=''.,');
65: END IF;
66:
67: g_scale_sign := '/';
68:

Line 70: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN

66:
67: g_scale_sign := '/';
68:
69: -- This will be used in the trend report as there would be months, weeks, quarters and years with full period
70: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
71: g_scaling_factor_cons := 4.35;
72: g_scale_sign_cons := '/';
73: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
74: g_scaling_factor_cons := 3;

Line 73: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN

69: -- This will be used in the trend report as there would be months, weeks, quarters and years with full period
70: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
71: g_scaling_factor_cons := 4.35;
72: g_scale_sign_cons := '/';
73: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
74: g_scaling_factor_cons := 3;
75: g_scale_sign_cons := '/';
76: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
77: g_scaling_factor_cons := 12;

Line 76: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN

72: g_scale_sign_cons := '/';
73: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
74: g_scaling_factor_cons := 3;
75: g_scale_sign_cons := '/';
76: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
77: g_scaling_factor_cons := 12;
78: g_scale_sign_cons := '/';
79: ELSE
80: g_scaling_factor_cons := 1;

Line 85: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN

81: g_scale_sign_cons := '/';
82: END IF;
83:
84: -- To get the sequence of the current period. It will always be the maximum sequence displayed in the trend report
85: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
86: select sequence into g_current_sequence from fii_time_week where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
87:
88: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
89: select sequence into g_current_sequence from FII_TIME_ENT_QTR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

Line 86: select sequence into g_current_sequence from fii_time_week where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

82: END IF;
83:
84: -- To get the sequence of the current period. It will always be the maximum sequence displayed in the trend report
85: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
86: select sequence into g_current_sequence from fii_time_week where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
87:
88: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
89: select sequence into g_current_sequence from FII_TIME_ENT_QTR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
90:

Line 88: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN

84: -- To get the sequence of the current period. It will always be the maximum sequence displayed in the trend report
85: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
86: select sequence into g_current_sequence from fii_time_week where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
87:
88: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
89: select sequence into g_current_sequence from FII_TIME_ENT_QTR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
90:
91: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
92: select sequence into g_current_sequence from FII_TIME_ENT_YEAR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

Line 89: select sequence into g_current_sequence from FII_TIME_ENT_QTR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

85: IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
86: select sequence into g_current_sequence from fii_time_week where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
87:
88: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
89: select sequence into g_current_sequence from FII_TIME_ENT_QTR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
90:
91: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
92: select sequence into g_current_sequence from FII_TIME_ENT_YEAR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
93:

Line 91: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN

87:
88: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
89: select sequence into g_current_sequence from FII_TIME_ENT_QTR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
90:
91: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
92: select sequence into g_current_sequence from FII_TIME_ENT_YEAR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
93:
94: ELSE
95: select sequence into g_current_sequence from FII_TIME_ENT_PERIOD where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

Line 92: select sequence into g_current_sequence from FII_TIME_ENT_YEAR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

88: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
89: select sequence into g_current_sequence from FII_TIME_ENT_QTR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
90:
91: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
92: select sequence into g_current_sequence from FII_TIME_ENT_YEAR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
93:
94: ELSE
95: select sequence into g_current_sequence from FII_TIME_ENT_PERIOD where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
96:

Line 95: select sequence into g_current_sequence from FII_TIME_ENT_PERIOD where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

91: ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
92: select sequence into g_current_sequence from FII_TIME_ENT_YEAR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
93:
94: ELSE
95: select sequence into g_current_sequence from FII_TIME_ENT_PERIOD where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
96:
97: END IF;
98:
99: END get_scaling_factor;

Line 148: fii_ar_util_pkg.reset_globals;

144: l_customer_select varchar2(500);
145: l_gt_hint varchar2(500);
146: BEGIN
147: --Call to reset the parameter variables
148: fii_ar_util_pkg.reset_globals;
149:
150: --Call to get all the parameters in the report
151: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
152:

Line 151: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);

147: --Call to reset the parameter variables
148: fii_ar_util_pkg.reset_globals;
149:
150: --Call to get all the parameters in the report
151: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
152:
153: --Get the order by clause for the report
154: l_order_by := get_order_by;
155:

Line 157: fii_ar_util_pkg.populate_summary_gt_tables;

153: --Get the order by clause for the report
154: l_order_by := get_order_by;
155:
156: --This call will populate fii_ar_summary_gt table
157: fii_ar_util_pkg.populate_summary_gt_tables;
158:
159: --Call to set up the global variables for scaling factor and sign for billed amount
160: get_scaling_factor;
161: l_gt_hint := ' leading(gt) cardinality(gt 1) ';

Line 163: l_curr_per_start := to_char(trunc(fii_ar_util_pkg.g_curr_per_start), 'DD/MM/YYYY');

159: --Call to set up the global variables for scaling factor and sign for billed amount
160: get_scaling_factor;
161: l_gt_hint := ' leading(gt) cardinality(gt 1) ';
162: -- Date to be passed in the drill on Beginning Open Receivables amount
163: l_curr_per_start := to_char(trunc(fii_ar_util_pkg.g_curr_per_start), 'DD/MM/YYYY');
164:
165: --Amount Drills
166: l_beg_open_rec_drill := 'AS_OF_DATE='||l_curr_per_start||'&pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
167:

Line 173: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN

169:
170: l_end_curr_rec_drill := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
171:
172:
173: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
174:
175: l_beg_open_rec_drill_2 := 'AS_OF_DATE='||l_curr_per_start||'&pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
176:
177: l_end_open_rec_drill_2 := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';

Line 193: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN

189: END IF;
190:
191:
192: --Detail drills require Customer and Account as parameter in case the drill is from view by Customer Account
193: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
194:
195: l_beg_open_rec_drill_l := 'AS_OF_DATE='||l_curr_per_start||'&pFunctionName=FII_AR_OPEN_REC_DTL&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
196:
197: l_end_open_rec_drill_l := 'pFunctionName=FII_AR_OPEN_REC_DTL&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';

Line 214: IF (fii_ar_util_pkg.g_collector_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') THEN

210: l_self_drill_select := '''''';
211:
212: --Setting up the where clauses based on the Parameter and viewby
213: --for Collector Dimension
214: IF (fii_ar_util_pkg.g_collector_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') THEN
215: l_collector_where := 'AND f.collector_id = t.collector_id';
216: END IF;
217:
218: --Customer Dimension where clause

Line 219: IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN

215: l_collector_where := 'AND f.collector_id = t.collector_id';
216: END IF;
217:
218: --Customer Dimension where clause
219: IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
220: l_child_party_where := ' AND f.party_id = t.party_id ';
221: END IF;
222:
223: -- Select, where, group by clauses based on viewby

Line 224: If((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS') OR (fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR')) THEN

220: l_child_party_where := ' AND f.party_id = t.party_id ';
221: END IF;
222:
223: -- Select, where, group by clauses based on viewby
224: If((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS') OR (fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR')) THEN
225: l_select_sql1 := ''''||l_beg_open_rec_drill_2||'''';
226:
227: l_select_sql2 := ''''||l_end_open_rec_drill_2||'''';
228:

Line 231: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN

227: l_select_sql2 := ''''||l_end_open_rec_drill_2||'''';
228:
229: l_select_sql3 := ''''||l_end_curr_rec_drill_2||'''';
230:
231: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
232: l_select_sql1 := ''''||l_beg_open_rec_drill_l||'''';
233:
234: l_select_sql2 := ''''||l_end_open_rec_drill_l||'''';
235:

Line 243: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN

239:
240: l_customer_select := ' t.party_id party_id, ';
241: l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
242:
243: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
244: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
245: l_select_sql1 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_beg_open_rec_drill_l||''', DECODE(is_leaf_flag,''Y'', '''||l_beg_open_rec_drill_l||''', '''||l_beg_open_rec_drill||'''))';
246:
247: l_select_sql2 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_end_open_rec_drill_l||''', DECODE(is_leaf_flag,''Y'', '''||l_end_open_rec_drill_l||''', '''||l_end_open_rec_drill||'''))';

Line 244: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN

240: l_customer_select := ' t.party_id party_id, ';
241: l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
242:
243: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
244: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
245: l_select_sql1 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_beg_open_rec_drill_l||''', DECODE(is_leaf_flag,''Y'', '''||l_beg_open_rec_drill_l||''', '''||l_beg_open_rec_drill||'''))';
246:
247: l_select_sql2 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_end_open_rec_drill_l||''', DECODE(is_leaf_flag,''Y'', '''||l_end_open_rec_drill_l||''', '''||l_end_open_rec_drill||'''))';
248:

Line 276: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN

272: l_party_where := 'AND f.parent_party_id = t.parent_party_id';
273:
274: END IF;
275:
276: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
277: l_inner_cst_group := l_inner_cst_group || ' ,t.party_id';
278: END IF;
279:
280: -- Report Query

Line 351: SELECT /+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)/ VIEWBY,

347: NULLIF((SUM(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC))OVER()
348: +SUM(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
349: -SUM(DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)) OVER()),0)) * 100 FII_AR_GT_PRIO_COLL_EFF_INDEX
350: FROM (
351: SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ VIEWBY,
352: viewby_code viewby_id,
353: '||l_inner_cst_select||l_customer_select||'
354: SUM(DECODE(t.report_date, :CURR_PERIOD_START ,
355: (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE

Line 387: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix

383: THEN current_open_amount ELSE NULL END) ) ) FII_AR_PRIOR_END_CURR_REC,
384: SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE, /*This date will be the prior as-of-date*/
385: (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
386: THEN billed_amount ELSE NULL END) ) ) FII_AR_PRIOR_BILLED_AMOUNT
387: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
388: ||' f,( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
389: FROM fii_time_structures cal,
390: '||fii_ar_util_pkg.get_from_statement||' gt
391: WHERE report_date IN ( :CURR_PERIOD_START ,

Line 390: '||fii_ar_util_pkg.get_from_statement||' gt

386: THEN billed_amount ELSE NULL END) ) ) FII_AR_PRIOR_BILLED_AMOUNT
387: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
388: ||' f,( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
389: FROM fii_time_structures cal,
390: '||fii_ar_util_pkg.get_from_statement||' gt
391: WHERE report_date IN ( :CURR_PERIOD_START ,
392: :ASOF_DATE,
393: :PREVIOUS_ASOF_DATE,
394: :PRIOR_PERIOD_START

Line 399: AND '||fii_ar_util_pkg.get_where_statement||'

395: )
396: AND ( bitand(cal.record_type_id, :BITAND) = :BITAND OR
397: bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
398: )
399: AND '||fii_ar_util_pkg.get_where_statement||'
400: ) t
401: WHERE f.time_id = t.time_id
402: AND f.period_type_id = t.period_type_id '||l_child_party_where||'
403: AND f.org_id = t.org_id

Line 404: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' '|| l_cust_acct_where ||'

400: ) t
401: WHERE f.time_id = t.time_id
402: AND f.period_type_id = t.period_type_id '||l_child_party_where||'
403: AND f.org_id = t.org_id
404: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' '|| l_cust_acct_where ||'
405: GROUP BY viewby_code, VIEWBY '||l_inner_cst_group||') inline_view
406: '||l_order_by;
407:
408: -- Call Util package to bind the variables

Line 409: fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, coll_eff_sql, coll_eff_output);

405: GROUP BY viewby_code, VIEWBY '||l_inner_cst_group||') inline_view
406: '||l_order_by;
407:
408: -- Call Util package to bind the variables
409: fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, coll_eff_sql, coll_eff_output);
410:
411: END get_coll_eff_index;
412:
413: ----------------------------------------------------------------------------------------------

Line 453: fii_ar_util_pkg.reset_globals;

449: l_customer_select varchar2(500);
450: l_gt_hint varchar2(500);
451: BEGIN
452:
453: fii_ar_util_pkg.reset_globals;
454:
455: --Call to get all the parameters in the report
456: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
457:

Line 456: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);

452:
453: fii_ar_util_pkg.reset_globals;
454:
455: --Call to get all the parameters in the report
456: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
457:
458: --Get the order by clause for the report
459: l_order_by := get_order_by;
460:

Line 462: fii_ar_util_pkg.populate_summary_gt_tables;

458: --Get the order by clause for the report
459: l_order_by := get_order_by;
460:
461: --This call will populate fii_ar_summary_gt table
462: fii_ar_util_pkg.populate_summary_gt_tables;
463: l_gt_hint := ' leading(gt) cardinality(gt 1) ';
464: --Amount Drills
465:
466: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN

Line 466: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN

462: fii_ar_util_pkg.populate_summary_gt_tables;
463: l_gt_hint := ' leading(gt) cardinality(gt 1) ';
464: --Amount Drills
465:
466: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
467: l_rec_amt_drill_2 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
468: l_wadp_drill_1 := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
469: ELSE
470: l_rec_amt_drill_2 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';

Line 477: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN

473:
474: l_rec_amt_drill := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
475:
476: --Customer Account Parameter is to be sent to the detail report only in case of viewby Customer Account
477: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
478: l_rec_amt_drill_1 := 'pFunctionName=FII_AR_RCT_ACT_DTL&BIS_PMV_DRILL_CODE_FII_CUSTOMER_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
479: l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
480: ELSE
481: l_rec_amt_drill_1 := 'pFunctionName=FII_AR_RCT_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';

Line 492: IF (fii_ar_util_pkg.g_collector_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') THEN

488: l_self_drill_select := '''''';
489:
490: --Setting up the where clauses based on the Parameter and viewby
491: --for Collector Dimension
492: IF (fii_ar_util_pkg.g_collector_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') THEN
493: l_collector_where := 'AND f.collector_id = t.collector_id';
494: END IF;
495:
496: --Customer Dimension where clause

Line 497: IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN

493: l_collector_where := 'AND f.collector_id = t.collector_id';
494: END IF;
495:
496: --Customer Dimension where clause
497: IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
498: l_child_party_where := ' AND f.party_id = t.party_id ';
499: END IF;
500:
501: -- Defining industry where clause for specific industry (when view by is not Customer) or when viewby is Industry

Line 502: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR

498: l_child_party_where := ' AND f.party_id = t.party_id ';
499: END IF;
500:
501: -- Defining industry where clause for specific industry (when view by is not Customer) or when viewby is Industry
502: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR
503: fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
504:
505: l_industry_where := ' AND t.class_code = f.class_code AND t.class_category = f.class_category';
506: END IF;

Line 503: fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN

499: END IF;
500:
501: -- Defining industry where clause for specific industry (when view by is not Customer) or when viewby is Industry
502: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR
503: fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
504:
505: l_industry_where := ' AND t.class_code = f.class_code AND t.class_category = f.class_category';
506: END IF;
507:

Line 509: If((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS')

505: l_industry_where := ' AND t.class_code = f.class_code AND t.class_category = f.class_category';
506: END IF;
507:
508: -- Select, where, group by clauses based on viewby
509: If((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS')
510: OR (fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR')
511: OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN
512:
513: l_select_sql1 := ''''||l_rec_amt_drill_2||'''';

Line 510: OR (fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR')

506: END IF;
507:
508: -- Select, where, group by clauses based on viewby
509: If((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS')
510: OR (fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR')
511: OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN
512:
513: l_select_sql1 := ''''||l_rec_amt_drill_2||'''';
514: l_select_sql2 := ''''||l_wadp_drill_1||'''';

Line 511: OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN

507:
508: -- Select, where, group by clauses based on viewby
509: If((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS')
510: OR (fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR')
511: OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN
512:
513: l_select_sql1 := ''''||l_rec_amt_drill_2||'''';
514: l_select_sql2 := ''''||l_wadp_drill_1||'''';
515:

Line 516: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN

512:
513: l_select_sql1 := ''''||l_rec_amt_drill_2||'''';
514: l_select_sql2 := ''''||l_wadp_drill_1||'''';
515:
516: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
517:
518: l_select_sql1 := ''''||l_rec_amt_drill_1||'''';
519: l_select_sql2 := '''''';
520:

Line 524: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN

520:
521: l_cust_acct_where := 'AND f.cust_account_id = t.cust_account_id';
522: l_customer_select := ' t.party_id party_id, ';
523:
524: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
525:
526: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
527: l_select_sql1 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_rec_amt_drill_1||''', DECODE(is_leaf_flag,''Y'', '''||l_rec_amt_drill_1||''', '''||l_rec_amt_drill||'''))';
528: l_select_sql2 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''', DECODE(is_leaf_flag,''Y'', '''', '''||l_wadp_drill||'''))';

Line 526: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN

522: l_customer_select := ' t.party_id party_id, ';
523:
524: ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
525:
526: IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
527: l_select_sql1 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_rec_amt_drill_1||''', DECODE(is_leaf_flag,''Y'', '''||l_rec_amt_drill_1||''', '''||l_rec_amt_drill||'''))';
528: l_select_sql2 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''', DECODE(is_leaf_flag,''Y'', '''', '''||l_wadp_drill||'''))';
529:
530: --Self drill. This is reqd only in case of Viewby Customer

Line 551: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN

547:
548:
549: END IF;
550:
551: IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
552: l_inner_cst_group := l_inner_cst_group || ' ,t.party_id';
553: END IF;
554:
555: --Call to set up the global variables for scaling factor and sign for billed amount

Line 638: SELECT /+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)/ VIEWBY,

634: NVL((SUM(FII_AR_PRIOR_WTD_DP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0)),0) FII_AR_GT_PRIOR_WTD_AVG_DP,
635: NVL( ((SUM(FII_AR_PRIOR_WTD_DP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0))
636: - (SUM(FII_AR_PRIOR_WTD_TP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0))),0) FII_AR_GT_PRIOR_WTD_AVG_DD
637: FROM (
638: SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ VIEWBY,
639: viewby_code viewby_id,
640: '|| l_inner_cst_select || l_customer_select ||'
641: SUM(DECODE(t.report_date, :CURR_PERIOD_START ,
642: (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE

Line 691: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix

687: SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
688: (CASE WHEN (f.header_filter_date <= :PREVIOUS_ASOF_DATE) and
689: (f.header_filter_date >= :PRIOR_PERIOD_START) and (bitand(t.record_type_id,:BITAND) = :BITAND)
690: THEN total_receipt_amount ELSE NULL END) ) ) FII_AR_PRIOR_REC_AMT
691: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
692: ||' f,( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
693: FROM fii_time_structures cal,
694: '||fii_ar_util_pkg.get_from_statement||' gt
695: WHERE report_date in ( :CURR_PERIOD_START ,

Line 694: '||fii_ar_util_pkg.get_from_statement||' gt

690: THEN total_receipt_amount ELSE NULL END) ) ) FII_AR_PRIOR_REC_AMT
691: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
692: ||' f,( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
693: FROM fii_time_structures cal,
694: '||fii_ar_util_pkg.get_from_statement||' gt
695: WHERE report_date in ( :CURR_PERIOD_START ,
696: :ASOF_DATE,
697: :PREVIOUS_ASOF_DATE,
698: :PRIOR_PERIOD_START

Line 703: AND '||fii_ar_util_pkg.get_where_statement||'

699: )
700: AND ( bitand(cal.record_type_id, :BITAND) = :BITAND OR
701: bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
702: )
703: AND '||fii_ar_util_pkg.get_where_statement||'
704: ) t
705: WHERE f.time_id = t.time_id
706: AND f.period_type_id = t.period_type_id '||l_child_party_where||'
707: AND f.org_id = t.org_id

Line 708: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' '|| l_cust_acct_where ||' '|| l_industry_where ||'

704: ) t
705: WHERE f.time_id = t.time_id
706: AND f.period_type_id = t.period_type_id '||l_child_party_where||'
707: AND f.org_id = t.org_id
708: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' '|| l_cust_acct_where ||' '|| l_industry_where ||'
709: GROUP BY viewby_code, VIEWBY '||l_inner_cst_group||' ) inline_view
710: '||l_order_by;
711:
712: fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, coll_eff_sql, coll_eff_output);

Line 712: fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, coll_eff_sql, coll_eff_output);

708: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' '|| l_cust_acct_where ||' '|| l_industry_where ||'
709: GROUP BY viewby_code, VIEWBY '||l_inner_cst_group||' ) inline_view
710: '||l_order_by;
711:
712: fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, coll_eff_sql, coll_eff_output);
713:
714: END get_coll_eff;
715:
716: -- This procedure will provide sql statement to retrieve data for Collection Effectiveness Report

Line 741: fii_ar_util_pkg.reset_globals;

737: l_current_prior_column varchar2(5000); --Prior columns select clause for current period
738:
739: BEGIN
740:
741: fii_ar_util_pkg.reset_globals;
742: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
743: fii_ar_util_pkg.g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';
744:
745: --Call to set up the global variables for scaling factor and sign for billed amount

Line 742: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);

738:
739: BEGIN
740:
741: fii_ar_util_pkg.reset_globals;
742: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
743: fii_ar_util_pkg.g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';
744:
745: --Call to set up the global variables for scaling factor and sign for billed amount
746: get_scaling_factor;

Line 743: fii_ar_util_pkg.g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';

739: BEGIN
740:
741: fii_ar_util_pkg.reset_globals;
742: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
743: fii_ar_util_pkg.g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';
744:
745: --Call to set up the global variables for scaling factor and sign for billed amount
746: get_scaling_factor;
747:

Line 749: fii_ar_util_pkg.populate_summary_gt_tables;

745: --Call to set up the global variables for scaling factor and sign for billed amount
746: get_scaling_factor;
747:
748: --This call will populate fii_ar_summary_gt table
749: fii_ar_util_pkg.populate_summary_gt_tables;
750:
751: --Decide which time table needs to be hit
752: IF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
753: l_time_table := 'FII_TIME_WEEK';

Line 752: IF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN

748: --This call will populate fii_ar_summary_gt table
749: fii_ar_util_pkg.populate_summary_gt_tables;
750:
751: --Decide which time table needs to be hit
752: IF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
753: l_time_table := 'FII_TIME_WEEK';
754: ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD') THEN
755: l_time_table := 'FII_TIME_ENT_PERIOD';
756: ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN

Line 754: ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD') THEN

750:
751: --Decide which time table needs to be hit
752: IF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
753: l_time_table := 'FII_TIME_WEEK';
754: ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD') THEN
755: l_time_table := 'FII_TIME_ENT_PERIOD';
756: ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
757: l_time_table := 'FII_TIME_ENT_QTR';
758: ELSE

Line 756: ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN

752: IF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
753: l_time_table := 'FII_TIME_WEEK';
754: ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD') THEN
755: l_time_table := 'FII_TIME_ENT_PERIOD';
756: ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
757: l_time_table := 'FII_TIME_ENT_QTR';
758: ELSE
759: l_time_table := 'FII_TIME_ENT_YEAR';
760: END IF;

Line 764: IF (fii_ar_util_pkg.g_collector_id <> '-111' ) THEN

760: END IF;
761:
762: --Setting up the where clauses based on the Parameter
763: --for Collector Dimension
764: IF (fii_ar_util_pkg.g_collector_id <> '-111' ) THEN
765: l_collector_where := 'AND f.collector_id = t.collector_id';
766: END IF;
767:
768: --Customer Dimension where clause

Line 769: IF (fii_ar_util_pkg.g_party_id <> '-111' ) THEN

765: l_collector_where := 'AND f.collector_id = t.collector_id';
766: END IF;
767:
768: --Customer Dimension where clause
769: IF (fii_ar_util_pkg.g_party_id <> '-111' ) THEN
770: l_party_where := ' AND f.party_id = t.party_id ';
771: END IF;
772:
773: --Industry where clause

Line 774: IF fii_ar_util_pkg.g_industry_id <> '-111' THEN

770: l_party_where := ' AND f.party_id = t.party_id ';
771: END IF;
772:
773: --Industry where clause
774: IF fii_ar_util_pkg.g_industry_id <> '-111' THEN
775: l_industry_where := ' AND t.class_code = f.class_code AND t.class_category = f.class_category';
776: END IF;
777:
778: -------------------------------------------------------------------------------

Line 809: IF fii_ar_util_pkg.g_time_comp = 'SEQUENTIAL' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN

805: --and when COMPARE TO <> PRIOR PERIOD/'SEQUENTIAL' then
806: --Prior Data is to be shown with an exception of period type Year
807: -------------------------------------------------------------------
808:
809: IF fii_ar_util_pkg.g_time_comp = 'SEQUENTIAL' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
810: l_prior_column:= ' NULL FII_PRIOR_AR_BEG_OPEN_REC,
811: NULL FII_PRIOR_AR_END_OPEN_REC,
812: NULL FII_PRIOR_AR_END_CURR_REC,
813: NULL FII_PRIOR_AR_BILLED_AMOUNT, ';

Line 891: IF( fii_ar_util_pkg.g_as_of_date = fii_ar_util_pkg.g_curr_per_end) THEN

887: --IF as of date = current period end then
888: --union all is not required
889: --else union all part will be used for the current period
890: ----------------------------------------------------------------
891: IF( fii_ar_util_pkg.g_as_of_date = fii_ar_util_pkg.g_curr_per_end) THEN
892: l_date_bind := ':ASOF_DATE';
893:
894: l_curr_sql_stmt := ' ';
895:

Line 958: SELECT /+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)/

954:
955: ELSE
956: l_date_bind := ':CURR_PERIOD_START';
957: l_curr_sql_stmt := ' UNION ALL
958: SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
959: per.name,
960: per.sequence sequence,
961: SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
962: CASE

Line 1028: FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,

1024: ELSE null
1025: END
1026: )) FII_AR_AVG_DD_NUM
1027: FROM '||l_time_table||' per,
1028: FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
1029: (
1030: SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ *
1031: FROM fii_time_structures cal,
1032: fii_ar_summary_gt gt

Line 1044: AND '||fii_ar_util_pkg.get_mv_where_statement||'

1040: WHERE f.time_id = t.time_id
1041: AND f.period_type_id = t.period_type_id
1042: AND f.org_id = t.org_id '|| l_party_where ||' ' || l_collector_where ||' ' || l_industry_where || '
1043: AND per.end_date = :CURR_PERIOD_END
1044: AND '||fii_ar_util_pkg.get_mv_where_statement||'
1045: GROUP BY t.report_date, per.sequence, name';
1046:
1047: /*Upper select clause. This is common for all period types*/
1048: -- In case when Union All is used which means that the current period should use a different scaling factor compared to

Line 1120: IF fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR' THEN

1116:
1117: END IF;
1118:
1119:
1120: IF fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR' THEN
1121: sqlstmt := l_select_clause || '
1122: SELECT /*+ no_merge INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
1123: t.report_date,
1124: SUM(DECODE((SELECT ''Y'' from '||l_time_table||'

Line 1122: SELECT /+ no_merge INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)/

1118:
1119:
1120: IF fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR' THEN
1121: sqlstmt := l_select_clause || '
1122: SELECT /*+ no_merge INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
1123: t.report_date,
1124: SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1125: WHERE start_date = t.report_date), ''Y'' , null,
1126: CASE

Line 1204: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix

1200: AND bitand(t.record_type_id, :BITAND) = :BITAND
1201: THEN f.avg_dd_num
1202: ELSE NULL
1203: END )) FII_AR_AVG_DD_NUM
1204: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
1205: ||' f,
1206: (
1207: SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) leading(gt) cardinality(gt 1)*/ *
1208: FROM fii_time_structures cal,

Line 1228: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' ' || l_industry_where || '

1224: t
1225: WHERE f.time_id = t.time_id
1226: AND f.period_type_id = t.period_type_id
1227: AND f.org_id = t.org_id
1228: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' ' || l_industry_where || '
1229: GROUP BY t.report_date) mv
1230: WHERE per.start_date >= :SD_PRIOR_PRIOR
1231: and ( per.end_date = mv.report_date
1232: OR per.start_date = mv.report_date)

Line 1249: /+ no_merge INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)/

1245:
1246:
1247: sqlstmt := l_select_clause || '
1248: SELECT
1249: /*+ no_merge INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
1250: t.report_date,
1251: SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1252: WHERE start_date = t.report_date), ''Y'' , null,
1253: CASE

Line 1322: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix

1318: WHEN bitand(t.record_type_id, :BITAND) = :BITAND
1319: THEN f.avg_dd_num
1320: ELSE NULL
1321: END )) FII_AR_AVG_DD_NUM
1322: FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
1323: ||' f,
1324: (
1325: SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) leading(gt) cardinality(gt 1) */ *
1326: FROM fii_time_structures cal,

Line 1345: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||'

1341: ) t
1342: WHERE f.time_id = t.time_id
1343: AND f.period_type_id = t.period_type_id
1344: AND f.org_id = t.org_id
1345: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||'
1346: ' || l_collector_where ||'
1347: ' || l_industry_where || '
1348: GROUP BY t.report_date) mv
1349: WHERE per.start_date >= :SD_PRIOR_PRIOR

Line 1364: fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, coll_eff_trend_sql, coll_eff_trend_output);

1360: ORDER BY cy_per.start_date ';
1361:
1362: END IF;
1363:
1364: fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, coll_eff_trend_sql, coll_eff_trend_output);
1365:
1366: END get_coll_eff_trend;
1367:
1368: END fii_ar_coll_eff_ind_pkg;