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

Line 8: period := fii_ar_util_pkg.get_dso_period_profile;

4: -- Function to return the DSO period for displaying in the parameter portlet
5: FUNCTION get_dso_period_param RETURN VARCHAR2 IS
6: period VARCHAR2(50);
7: BEGIN
8: period := fii_ar_util_pkg.get_dso_period_profile;
9:
10: IF period <> -1 THEN
11: period := period || ' Days';
12: END IF;

Line 22: fii_ar_util_pkg.get_dso_table_values;

18: FUNCTION get_net_rec_column RETURN VARCHAR2 IS
19: net_rec_col VARCHAR2(500);
20: BEGIN
21: -- Flags from Receivable Setup
22: fii_ar_util_pkg.get_dso_table_values;
23:
24: -- Adding columns that are enabled in the Receivables Setup page
25: net_rec_col := '';
26: g_open_rec_column_dso := '';

Line 29: FOR a IN fii_ar_util_pkg.g_dso_table.FIRST..fii_ar_util_pkg.g_dso_table.LAST LOOP

25: net_rec_col := '';
26: g_open_rec_column_dso := '';
27: g_open_rec_column_dsot := '';
28: g_hit_rct_aging := 'N';
29: FOR a IN fii_ar_util_pkg.g_dso_table.FIRST..fii_ar_util_pkg.g_dso_table.LAST LOOP
30: CASE
31: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'INV' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
32: net_rec_col := net_rec_col || ' + sum(f.inv_amount)';
33: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.inv_amount)';

Line 31: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'INV' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

27: g_open_rec_column_dsot := '';
28: g_hit_rct_aging := 'N';
29: FOR a IN fii_ar_util_pkg.g_dso_table.FIRST..fii_ar_util_pkg.g_dso_table.LAST LOOP
30: CASE
31: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'INV' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
32: net_rec_col := net_rec_col || ' + sum(f.inv_amount)';
33: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.inv_amount)';
34: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.inv_amount)';
35: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'DM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

Line 35: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'DM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

31: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'INV' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
32: net_rec_col := net_rec_col || ' + sum(f.inv_amount)';
33: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.inv_amount)';
34: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.inv_amount)';
35: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'DM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
36: net_rec_col := net_rec_col || ' + sum(f.dm_amount)';
37: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.dm_amount)';
38: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.dm_amount)';
39: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

Line 39: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

35: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'DM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
36: net_rec_col := net_rec_col || ' + sum(f.dm_amount)';
37: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.dm_amount)';
38: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.dm_amount)';
39: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
40: net_rec_col := net_rec_col || ' + sum(f.cb_amount)';
41: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.cb_amount)';
42: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.cb_amount)';
43: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'BR' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

Line 43: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'BR' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

39: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
40: net_rec_col := net_rec_col || ' + sum(f.cb_amount)';
41: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.cb_amount)';
42: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.cb_amount)';
43: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'BR' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
44: net_rec_col := net_rec_col || ' + sum(f.br_amount)';
45: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.br_amount)';
46: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.br_amount)';
47: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'DEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

Line 47: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'DEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

43: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'BR' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
44: net_rec_col := net_rec_col || ' + sum(f.br_amount)';
45: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.br_amount)';
46: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.br_amount)';
47: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'DEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
48: net_rec_col := net_rec_col || ' + sum(f.dep_amount)';
49: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.dep_amount)';
50: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.dep_amount)';
51: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

Line 51: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

47: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'DEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
48: net_rec_col := net_rec_col || ' + sum(f.dep_amount)';
49: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.dep_amount)';
50: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.dep_amount)';
51: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
52: net_rec_col := net_rec_col || ' + sum(f.on_account_credit_amount)';
53: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNDEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
54: net_rec_col := net_rec_col || ' - sum(f.unapp_dep_amount)';
55: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNREC' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

Line 53: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNDEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

49: g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.dep_amount)';
50: g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.dep_amount)';
51: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
52: net_rec_col := net_rec_col || ' + sum(f.on_account_credit_amount)';
53: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNDEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
54: net_rec_col := net_rec_col || ' - sum(f.unapp_dep_amount)';
55: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNREC' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
56: g_hit_rct_aging := 'Y';
57: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OACB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

Line 55: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNREC' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

51: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
52: net_rec_col := net_rec_col || ' + sum(f.on_account_credit_amount)';
53: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNDEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
54: net_rec_col := net_rec_col || ' - sum(f.unapp_dep_amount)';
55: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNREC' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
56: g_hit_rct_aging := 'Y';
57: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OACB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
58: net_rec_col := net_rec_col || ' - sum(f.on_account_cash_amount)';
59: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OCB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

Line 57: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OACB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

53: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNDEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
54: net_rec_col := net_rec_col || ' - sum(f.unapp_dep_amount)';
55: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNREC' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
56: g_hit_rct_aging := 'Y';
57: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OACB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
58: net_rec_col := net_rec_col || ' - sum(f.on_account_cash_amount)';
59: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OCB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
60: net_rec_col := net_rec_col || ' - sum(f.claim_amount)';
61: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'PREPAY' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

Line 59: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OCB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

55: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNREC' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
56: g_hit_rct_aging := 'Y';
57: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OACB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
58: net_rec_col := net_rec_col || ' - sum(f.on_account_cash_amount)';
59: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OCB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
60: net_rec_col := net_rec_col || ' - sum(f.claim_amount)';
61: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'PREPAY' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
62: net_rec_col := net_rec_col || ' - sum(f.prepayment_amount)';
63: ELSE

Line 61: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'PREPAY' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN

57: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OACB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
58: net_rec_col := net_rec_col || ' - sum(f.on_account_cash_amount)';
59: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OCB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
60: net_rec_col := net_rec_col || ' - sum(f.claim_amount)';
61: WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'PREPAY' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
62: net_rec_col := net_rec_col || ' - sum(f.prepayment_amount)';
63: ELSE
64: NULL;
65: END CASE;

Line 112: fii_ar_util_pkg.reset_globals;

108: BEGIN
109:
110: -- Clear global parameters AND read the new parameters
111: -- Sets all g_% variables to its default values
112: fii_ar_util_pkg.reset_globals;
113:
114: -- Reads the parameters from the parameter portlet
115: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
116:

Line 115: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);

111: -- Sets all g_% variables to its default values
112: fii_ar_util_pkg.reset_globals;
113:
114: -- Reads the parameters from the parameter portlet
115: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
116:
117: -- Populates the security related global temporary tables (fii_ar_summary_gt)
118: fii_ar_util_pkg.populate_summary_gt_tables;
119:

Line 118: fii_ar_util_pkg.populate_summary_gt_tables;

114: -- Reads the parameters from the parameter portlet
115: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
116:
117: -- Populates the security related global temporary tables (fii_ar_summary_gt)
118: fii_ar_util_pkg.populate_summary_gt_tables;
119:
120: -- Gets the view by
121: l_view_by := fii_ar_util_pkg.g_view_by;
122:

Line 121: l_view_by := fii_ar_util_pkg.g_view_by;

117: -- Populates the security related global temporary tables (fii_ar_summary_gt)
118: fii_ar_util_pkg.populate_summary_gt_tables;
119:
120: -- Gets the view by
121: l_view_by := fii_ar_util_pkg.g_view_by;
122:
123: -- Adding columns that are enabled in the Receivables Setup page
124: l_net_rec_column := get_net_rec_column;
125:

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

140: l_group_by := ', v.is_self_flag, v.is_leaf_flag';
141: l_customer_where := ' AND f.parent_party_id = v.parent_party_id';
142: l_cust_drill := 'DECODE(v.is_leaf_flag, ''Y'', '''', DECODE(v.is_self_flag, ''Y'', '''', ''pFunctionName=FII_AR_DSO&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY''))';
143: ELSE
144: IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
145: l_net_rec_sum_drill := 'DECODE(' || g_open_rec_column_dso || ', 0, '''', ''pFunctionName=FII_AR_NET_REC_SUM&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID'')';
146: ELSE
147: l_net_rec_sum_drill := 'DECODE(' || g_open_rec_column_dso || ', 0, '''', ''pFunctionName=FII_AR_NET_REC_SUM&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID'')';
148: END IF;

Line 152: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND l_view_by <> 'CUSTOMER+FII_CUSTOMERS')

148: END IF;
149: END IF;
150:
151: -- Defining industry where clause for specific industry or viewby is Industry
152: IF (fii_ar_util_pkg.g_industry_id <> '-111' AND l_view_by <> 'CUSTOMER+FII_CUSTOMERS')
153: OR l_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
154: l_industry_where := ' AND v.class_code = f.class_code AND v.class_category = f.class_category';
155: ELSE
156: l_industry_where := '';

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

156: l_industry_where := '';
157: END IF;
158:
159: -- Adding Filter on party_id
160: IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
161: l_child_party_where := ' AND f.party_id = v.party_id ';
162: ELSE
163: l_child_party_where := '';
164: END IF;

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

163: l_child_party_where := '';
164: END IF;
165:
166: -- Constructing the ORDER BY clause
167: IF instr(fii_ar_util_pkg.g_order_by,',') <> 0 THEN
168: IF instr(fii_ar_util_pkg.g_order_by,'VIEWBY') <> 0 THEN
169: l_order_by := ' ORDER BY ' || fii_ar_util_pkg.g_order_by;
170: ELSE
171: l_order_by := ' ORDER BY NVL(FII_AR_DSO, -999999999) DESC';

Line 168: IF instr(fii_ar_util_pkg.g_order_by,'VIEWBY') <> 0 THEN

164: END IF;
165:
166: -- Constructing the ORDER BY clause
167: IF instr(fii_ar_util_pkg.g_order_by,',') <> 0 THEN
168: IF instr(fii_ar_util_pkg.g_order_by,'VIEWBY') <> 0 THEN
169: l_order_by := ' ORDER BY ' || fii_ar_util_pkg.g_order_by;
170: ELSE
171: l_order_by := ' ORDER BY NVL(FII_AR_DSO, -999999999) DESC';
172: END IF;

Line 169: l_order_by := ' ORDER BY ' || fii_ar_util_pkg.g_order_by;

165:
166: -- Constructing the ORDER BY clause
167: IF instr(fii_ar_util_pkg.g_order_by,',') <> 0 THEN
168: IF instr(fii_ar_util_pkg.g_order_by,'VIEWBY') <> 0 THEN
169: l_order_by := ' ORDER BY ' || fii_ar_util_pkg.g_order_by;
170: ELSE
171: l_order_by := ' ORDER BY NVL(FII_AR_DSO, -999999999) DESC';
172: END IF;
173: ELSIF instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0 THEN

Line 173: ELSIF instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0 THEN

169: l_order_by := ' ORDER BY ' || fii_ar_util_pkg.g_order_by;
170: ELSE
171: l_order_by := ' ORDER BY NVL(FII_AR_DSO, -999999999) DESC';
172: END IF;
173: ELSIF instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0 THEN
174: l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
175: l_order_by := ' ORDER BY NVL(' || l_order_column || ', -999999999) DESC';
176: ELSE
177: l_order_by := ' &ORDER_BY_CLAUSE';

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

170: ELSE
171: l_order_by := ' ORDER BY NVL(FII_AR_DSO, -999999999) DESC';
172: END IF;
173: ELSIF instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0 THEN
174: l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
175: l_order_by := ' ORDER BY NVL(' || l_order_column || ', -999999999) DESC';
176: ELSE
177: l_order_by := ' &ORDER_BY_CLAUSE';
178: END IF;

Line 183: SELECT /+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)/ v.viewby VIEWBY,

179:
180: IF g_hit_rct_aging = 'Y' THEN
181: l_unapp_query := '
182: UNION ALL
183: SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ v.viewby VIEWBY,
184: v.viewby_code VIEWBYID,
185: -sum(f.unapp_amount) FII_AR_NET_REC_AMT,
186: NULL FII_AR_BILLED_AMT,
187: NULL FII_AR_VIEW_BY_DRILL,

Line 189: FROM fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,

185: -sum(f.unapp_amount) FII_AR_NET_REC_AMT,
186: NULL FII_AR_BILLED_AMT,
187: NULL FII_AR_VIEW_BY_DRILL,
188: NULL FII_AR_NET_REC_AMT_DRILL
189: FROM fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
190: (
191: SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) '||l_gt_hint|| ' */ *
192: FROM fii_time_structures cal,
193: ' || fii_ar_util_pkg.get_from_statement || ' gt

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

189: FROM fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
190: (
191: SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) '||l_gt_hint|| ' */ *
192: FROM fii_time_structures cal,
193: ' || fii_ar_util_pkg.get_from_statement || ' gt
194: WHERE cal.report_date = :ASOF_DATE
195: AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE)
196: AND ' || fii_ar_util_pkg.get_where_statement || '
197: ) v

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

192: FROM fii_time_structures cal,
193: ' || fii_ar_util_pkg.get_from_statement || ' gt
194: WHERE cal.report_date = :ASOF_DATE
195: AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE)
196: AND ' || fii_ar_util_pkg.get_where_statement || '
197: ) v
198: WHERE f.time_id = v.time_id
199: AND f.period_type_id = v.period_type_id
200: AND f.org_id = v.org_id

Line 201: AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' '

197: ) v
198: WHERE f.time_id = v.time_id
199: AND f.period_type_id = v.period_type_id
200: AND f.org_id = v.org_id
201: AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' '
202: || l_customer_where
203: || l_child_party_where
204: || l_customer_acc_where
205: || l_industry_where || '

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

221: sum(sum(FII_AR_NET_REC_AMT)) over() FII_AR_GT_NET_REC_AMT,
222: sum(sum(FII_AR_BILLED_AMT)) over() FII_AR_GT_BILLED_AMT
223: FROM
224: (
225: SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
226: v.viewby VIEWBY,
227: v.viewby_code VIEWBYID,
228: CASE WHEN bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
229: ' || l_net_rec_column || '

Line 240: FROM fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,

236: NULL
237: END FII_AR_BILLED_AMT,
238: ' || l_cust_drill || ' FII_AR_VIEW_BY_DRILL,
239: ' || l_net_rec_sum_drill || ' FII_AR_NET_REC_AMT_DRILL
240: FROM fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
241: (
242: SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) '||l_gt_hint|| ' */ *
243: FROM fii_time_structures cal,
244: ' || fii_ar_util_pkg.get_from_statement || ' gt

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

240: FROM fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
241: (
242: SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) '||l_gt_hint|| ' */ *
243: FROM fii_time_structures cal,
244: ' || fii_ar_util_pkg.get_from_statement || ' gt
245: WHERE cal.report_date = :ASOF_DATE
246: AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
247: OR bitand(cal.record_type_id, :DSO_BITAND) = :DSO_BITAND)
248: AND ' || fii_ar_util_pkg.get_where_statement || '

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

244: ' || fii_ar_util_pkg.get_from_statement || ' gt
245: WHERE cal.report_date = :ASOF_DATE
246: AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
247: OR bitand(cal.record_type_id, :DSO_BITAND) = :DSO_BITAND)
248: AND ' || fii_ar_util_pkg.get_where_statement || '
249: ) v
250: WHERE f.time_id = v.time_id
251: AND f.period_type_id = v.period_type_id
252: AND f.org_id = v.org_id

Line 253: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_customer_where

249: ) v
250: WHERE f.time_id = v.time_id
251: AND f.period_type_id = v.period_type_id
252: AND f.org_id = v.org_id
253: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_customer_where
254: || l_child_party_where
255: || l_customer_acc_where
256: || l_industry_where || '
257: GROUP BY v.viewby_code, v.VIEWBY' || l_group_by || ' , v.record_type_id

Line 264: fii_ar_util_pkg.bind_variable(

260: GROUP BY VIEWBYID, VIEWBY
261: ' || l_order_by;
262:
263: -- Calling the bind_variable API
264: fii_ar_util_pkg.bind_variable(
265: p_sqlstmt => sqlstmt,
266: p_Page_parameter_tbl => p_page_parameter_tbl,
267: p_sql_output => p_dso_sql,
268: p_bind_output_table => p_dso_output

Line 294: fii_ar_util_pkg.reset_globals;

290: BEGIN
291:
292: -- Clear global parameters AND read the new parameters
293: -- Sets all g_% variables to its default values
294: fii_ar_util_pkg.reset_globals;
295:
296: -- Reads the parameters from the parameter portlet
297: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
298:

Line 297: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);

293: -- Sets all g_% variables to its default values
294: fii_ar_util_pkg.reset_globals;
295:
296: -- Reads the parameters from the parameter portlet
297: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
298:
299: -- Populates the security related global temporary tables (fii_ar_summary_gt)
300: fii_ar_util_pkg.populate_summary_gt_tables;
301:

Line 300: fii_ar_util_pkg.populate_summary_gt_tables;

296: -- Reads the parameters from the parameter portlet
297: fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
298:
299: -- Populates the security related global temporary tables (fii_ar_summary_gt)
300: fii_ar_util_pkg.populate_summary_gt_tables;
301:
302: l_net_rec_sum_drill := 'pFunctionName=FII_AR_NET_REC_SUM&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
303:
304: -- Adding Filter on party_id when Customer is NOT All

Line 305: IF fii_ar_util_pkg.g_party_id <> '-111' THEN

301:
302: l_net_rec_sum_drill := 'pFunctionName=FII_AR_NET_REC_SUM&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
303:
304: -- Adding Filter on party_id when Customer is NOT All
305: IF fii_ar_util_pkg.g_party_id <> '-111' THEN
306: l_child_party_where := ' AND f.party_id = v.party_id ';
307: ELSE
308: l_child_party_where := '';
309: END IF;

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

308: l_child_party_where := '';
309: END IF;
310:
311: -- Defining industry where clause for specific industry when Industry is NOT All
312: IF fii_ar_util_pkg.g_industry_id <> '-111' THEN
313: l_industry_where := ' AND v.class_code = f.class_code AND v.class_category = f.class_category';
314: ELSE
315: l_industry_where := '';
316: END IF;

Line 321: IF fii_ar_util_pkg.g_as_of_date <> LAST_DAY(fii_ar_util_pkg.g_as_of_date) THEN

317:
318: -- Adding columns that are enabled in the Receivables Setup page
319: l_net_rec_column := get_net_rec_column;
320:
321: IF fii_ar_util_pkg.g_as_of_date <> LAST_DAY(fii_ar_util_pkg.g_as_of_date) THEN
322: l_curr_query := '
323: UNION ALL
324: -- Query to return data for current month
325: SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/

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

321: IF fii_ar_util_pkg.g_as_of_date <> LAST_DAY(fii_ar_util_pkg.g_as_of_date) THEN
322: l_curr_query := '
323: UNION ALL
324: -- Query to return data for current month
325: SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
326: v.sequence FII_EFFECTIVE_NUM,
327: CASE WHEN bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
328: ' || l_net_rec_column || '
329: ELSE

Line 341: fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,

337: END FII_AR_BILLED_AMT,
338: NULL FII_AR_BILLED_PRIOR_AMT,
339: 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
340: FROM
341: fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
342: (
343: SELECT /*+ no_merge cardinality(gt 1)*/ *
344: FROM fii_ar_summary_gt gt,
345: (

Line 358: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_child_party_where || '

354: WHERE
355: f.time_id = v.time_id
356: AND f.period_type_id = v.period_type_id
357: AND f.org_id = v.org_id
358: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_child_party_where || '
359: ' || l_industry_where || '
360: GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id';
361: l_curr_unapp_query := '
362: UNION ALL

Line 364: SELECT /+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)/

360: GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id';
361: l_curr_unapp_query := '
362: UNION ALL
363: -- Query to return unapplied amount for current month
364: SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
365: v.sequence FII_EFFECTIVE_NUM,
366: -sum(f.unapp_amount) FII_AR_NET_REC_AMT,
367: NULL FII_AR_NET_REC_PRIOR_AMT,
368: NULL FII_AR_BILLED_AMT,

Line 372: fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,

368: NULL FII_AR_BILLED_AMT,
369: NULL FII_AR_BILLED_PRIOR_AMT,
370: NULL inv_amount, NULL dm_amount, NULL cb_amount, NULL br_amount, NULL dep_amount
371: FROM
372: fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
373: (
374: SELECT /*+ no_merge cardinality(gt 1) */ *
375: FROM fii_ar_summary_gt gt,
376: (

Line 388: AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' ' || l_child_party_where || '

384: WHERE
385: f.time_id = v.time_id
386: AND f.period_type_id = v.period_type_id
387: AND f.org_id = v.org_id
388: AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' ' || l_child_party_where || '
389: ' || l_industry_where || '
390: GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id';
391: ELSE
392: l_curr_query := '';

Line 400: SELECT /+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)/

396: IF g_hit_rct_aging = 'Y' THEN
397: l_unapp_amount_query := '
398: UNION ALL
399: -- Query to return unapplied amount for months other than current month
400: SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
401: v.sequence FII_EFFECTIVE_NUM,
402: CASE WHEN v.start_date > :SD_PRIOR THEN
403: -sum(f.unapp_amount) ELSE NULL END FII_AR_NET_REC_AMT,
404: CASE WHEN v.end_date <= last_day(:SD_PRIOR) THEN

Line 410: fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,

406: NULL FII_AR_BILLED_AMT,
407: NULL FII_AR_BILLED_PRIOR_AMT,
408: NULL inv_amount, NULL dm_amount, NULL cb_amount, NULL br_amount, NULL dep_amount
409: FROM
410: fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
411: (
412: SELECT /*+ no_merge cardinality(gt 1) */ *
413: FROM fii_ar_summary_gt gt,
414: (

Line 426: AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' ' || l_child_party_where || '

422: WHERE
423: f.time_id = v.time_id
424: AND f.period_type_id = v.period_type_id
425: AND f.org_id = v.org_id
426: AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' ' || l_child_party_where || '
427: ' || l_industry_where || '
428: GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id';
429: ELSE
430: l_unapp_amount_query := '';

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

447: FROM
448: fii_time_ent_period cy_per,
449: (
450: -- Query to return data for months other than current month
451: SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
452: v.sequence FII_EFFECTIVE_NUM,
453: CASE WHEN v.start_date > :SD_PRIOR AND bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
454: ' || l_net_rec_column || '
455: ELSE

Line 475: fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,

471: NULL
472: END FII_AR_BILLED_PRIOR_AMT,
473: 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
474: FROM
475: fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
476: (
477: SELECT /*+ no_merge cardinality(gt 1) */ *
478: FROM fii_ar_summary_gt gt,
479: (

Line 492: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_child_party_where || '

488: WHERE
489: f.time_id = v.time_id
490: AND f.period_type_id = v.period_type_id
491: AND f.org_id = v.org_id
492: AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_child_party_where || '
493: ' || l_industry_where || '
494: GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id
495: ' || l_curr_query || '
496: ' || l_unapp_amount_query || '

Line 507: fii_ar_util_pkg.bind_variable(

503: GROUP BY inline_query.fii_effective_num, cy_per.sequence, cy_per.start_date, cy_per.name, cy_per.end_date
504: ORDER BY cy_per.start_date';
505:
506: -- Calling the bind_variable API
507: fii_ar_util_pkg.bind_variable(
508: p_sqlstmt => sqlstmt,
509: p_Page_parameter_tbl => p_page_parameter_tbl,
510: p_sql_output => p_dso_sql,
511: p_bind_output_table => p_dso_output