[Home] [Help]
PACKAGE BODY: APPS.FII_PMV_UTIL
Source
1 PACKAGE BODY FII_PMV_UTIL AS
2 /* $Header: FIIPMVUB.pls 120.10 2006/09/15 05:24:17 sajgeo ship $ */
3
4 g_prim_global_currency_code VARCHAR2(15) := get_prim_global_currency_code;
5 g_sec_global_currency_code VARCHAR2(15) := get_sec_global_currency_code;
6 g_sec_profile NUMBER := nvl(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'), -1);
7
8 g_all_operating_unit VARCHAR2(240) ;
9 g_operating_unit VARCHAR2(240);
10 g_common_functional_currency VARCHAR2(3) ;
11 g_functional_currency_code VARCHAR2(3) ;
12
13 g_det_ou_lov NUMBER;
14 g_security_profile_id NUMBER;
15 g_security_org_id NUMBER;
16 g_business_group_id NUMBER;
17 g_p_as_of_date DATE;
18 g_gid NUMBER;
19 g_previous_date DATE;
20
21
22 FUNCTION get_msg (p_page_id IN VARCHAR2
23 ,p_user_id IN VARCHAR2
24 ,p_session_id IN VARCHAR2
25 ,p_function_name IN VARCHAR2
26 )RETURN VARCHAR2 IS
27 stmt VARCHAR2(20);
28 BEGIN
29 stmt := BIS_PMV_PORTAL_UTIL_PUB.getTimeLevelLabel(p_page_id, p_user_id,
30 p_session_id, p_function_name);
31 RETURN stmt;
32 END get_msg;
33
34 FUNCTION get_msg1 (p_page_id IN VARCHAR2
35 ,p_user_id IN VARCHAR2
36 ,p_session_id IN VARCHAR2
37 ,p_function_name IN VARCHAR2
38 )RETURN VARCHAR2 IS
39 stmt VARCHAR2(20);
40 BEGIN
41 stmt := fnd_message.get_string('FII', 'FII_GL_PMV')||' ' ||BIS_PMV_PORTAL_UTIL_PUB.getTimeLevelLabel(p_page_id, p_user_id, p_session_id, p_function_name);
42 RETURN stmt;
43 END get_msg1;
44
45 FUNCTION get_curr RETURN VARCHAR2 IS
46 stmt VARCHAR2(20);
47 BEGIN
48 --fix for repository bug 4945663
49 --select id into stmt from fii_currencies_v where id = 'FII_GLOBAL1';
50 stmt := 'FII_GLOBAL1';
51 RETURN stmt;
52 END get_curr;
53
54 FUNCTION get_manager RETURN NUMBER IS
55 stmt NUMBER(10);
56 BEGIN
57 --fix for repository bug 4945663
58 --select distinct id into stmt from HRI_CL_PER_CCMGR_V where id = fnd_global.employee_id;
59 stmt := -1;
60 RETURN stmt;
61 END get_manager;
62
63 FUNCTION get_dbi_params(region_id IN VARCHAR2) RETURN VARCHAR2 IS
64 employee_id NUMBER(10);
65 employee_name VARCHAR2(240);
66 currency FII_CURRENCIES_V.ID%TYPE;
67 period_id NUMBER;
68 BEGIN
69 employee_id := fnd_global.employee_id;
70 --fix for repository bug 4945663
71 --select id into currency from fii_currencies_v where id = 'FII_GLOBAL1';
72 currency := 'FII_GLOBAL1';
73 select ENT_PERIOD_ID into period_id from fii_time_ent_period where sysdate
74 between START_DATE and END_DATE;
75 IF (region_id = 'FII_PMV_MGR_PARAMETER_PORTLET') THEN
76 RETURN '&'||'AS_OF_DATE='||TO_CHAR(TRUNC(sysdate),'DD-MON-YYYY')||
77 '&'||'BIS_MANAGER='||employee_id||
78 '&'||'CURRENCY='||currency||
79 '&'||'YEARLY=TIME_COMPARISON_TYPE+YEARLY&PERIOD_MONTH_FROM='||period_id||'&PERIOD_MONTH_TO='||period_id;
80 ELSE
81 RETURN NULL;
82 END IF;
83 END get_dbi_params;
84
85 FUNCTION get_sec_profile RETURN NUMBER IS
86 stmt NUMBER;
87 BEGIN
88 stmt := nvl(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'), -1);
89 RETURN stmt;
90 END get_sec_profile;
91
92 FUNCTION get_prim_global_currency_code RETURN VARCHAR2 IS
93 BEGIN
94 RETURN bis_common_parameters.get_currency_code;
95 END get_prim_global_currency_code;
96
97 FUNCTION get_sec_global_currency_code RETURN VARCHAR2 IS
98 BEGIN
99 RETURN bis_common_parameters.get_secondary_currency_code;
100 END get_sec_global_currency_code;
101
102 FUNCTION get_display_currency(p_selected_operating_unit IN VARCHAR2) RETURN VARCHAR2 IS
103 BEGIN
104 IF g_sec_profile is null then
105 g_sec_profile := nvl(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'), -1);
106 END IF;
107
108 IF g_det_ou_lov IS NULL THEN
109 g_det_ou_lov := determine_OU_LOV;
110 END IF;
111
112 IF g_business_group_id IS NULL THEN
113 g_business_group_id := fii_pmv_util.get_business_group;
114 END IF;
115
116 IF(p_selected_operating_unit <> 'ALL') then
117 IF (g_operating_unit is null or g_operating_unit <> p_selected_operating_unit) THEN
118 g_operating_unit := p_selected_operating_unit;
119
120 select currency_code
121 into g_functional_currency_code
122 from financials_system_params_all fsp,
123 gl_ledgers_public_v gsob
124 where fsp.org_id = p_selected_operating_unit
125 and fsp.set_of_books_id = gsob.ledger_id;
126 END IF;
127
128 /* Bug 3890938. Added condition that if functional currency = primary/secondary global
129 currency, return NULL
130 */
131 IF (g_functional_currency_code = g_prim_global_currency_code) OR
132 (g_functional_currency_code = g_sec_global_currency_code) THEN
133 return NULL;
134 ELSE
135 return g_functional_currency_code;
136 END IF;
137
138 ELSE -- operating unit is 'All'
139 IF g_all_operating_unit is null THEN -- subsequent runs are prevented
140 g_all_operating_unit := p_selected_operating_unit; ---gets set a value for the first run
141
142 select distinct currency_code
143 into g_common_functional_currency
144 from financials_system_params_all fsp,
145 gl_ledgers_public_v gsob
146 where fsp.set_of_books_id = gsob.ledger_id
147 AND (
148 (
149 g_det_ou_lov=1 AND fsp.org_id = fsp.org_id
150 )
151 OR (
152 g_det_ou_lov=2
153 AND fsp.org_id in (
154 SELECT organization_id
155 FROM hr_operating_units
156 WHERE business_group_id = fii_pmv_util.g_business_group_id
157 )
158 )
159 OR (
160 g_det_ou_lov=3
161 AND fsp.org_id in (
162 SELECT organization_id
163 FROM per_organization_list
164 WHERE security_profile_id = g_sec_profile
165 )
166 )
167 OR(
168 g_det_ou_lov=4 AND fsp.org_id = nvl(fnd_profile.value('ORG_ID'), -1)
169 )
170 );
171
172 END IF;
173
174 /* Bug 3890938. Added condition that if functional currency = primary/secondary global
175 currency, return NULL
176 */
177 IF (g_common_functional_currency = g_prim_global_currency_code) OR
178 (g_common_functional_currency = g_sec_global_currency_code) THEN
179 return NULL;
180 ELSE
181 return g_common_functional_currency;
182 END IF;
183
184 END IF;
185
186 EXCEPTION
187 when too_many_rows then
188 g_common_functional_currency := 'N/A';
189 return 'N/A';
190 when others then
191 return 'N/A';
192 END get_display_currency;
193
194 PROCEDURE get_parameters (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
195 p_as_of_date OUT NOCOPY Date,
196 p_operating_unit OUT NOCOPY Varchar2,
197 p_supplier OUT NOCOPY Varchar2,
198 p_invoice_number OUT NOCOPY Number,
199 p_period_type OUT NOCOPY Varchar2,
200 p_record_type_id OUT NOCOPY NUMBER,
201 p_view_by OUT NOCOPY Varchar2,
202 p_currency OUT NOCOPY Varchar2,
203 p_column_name OUT NOCOPY VARCHAR2,
204 p_table_name OUT NOCOPY VARCHAR2,
205 p_gid OUT NOCOPY NUMBER,
206 p_org_where OUT NOCOPY Varchar2,
207 p_supplier_where OUT NOCOPY Varchar2
208 ) IS
209 l_currency Varchar2(50);
210 -- l_invoice_number Varchar2(50);
211 l_org_list Varchar2(240);
212 l_org_count Number;
213 -- l_security_profile_id Number;
214 -- l_security_org_id NUMBER;
215 l_all_org_flag VARCHAR2(30);
216 l_business_group_id NUMBER;
217 l_org_id NUMBER;
218
219
220 BEGIN
221 IF (p_page_parameter_tbl.count > 0) THEN
222 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
223 IF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
224 p_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value, 'DD-MM-YYYY');
225 --added by vkazhipu
226 g_p_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value, 'DD-MM-YYYY');
227 g_previous_date := add_months (p_as_of_date, -11);
228 END IF;
229 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
230 p_operating_unit := p_page_parameter_tbl(i).parameter_value;
231 END IF;
232 IF p_page_parameter_tbl(i).parameter_name = 'SUPPLIER+POA_SUPPLIERS' THEN
233 p_supplier := p_page_parameter_tbl(i).parameter_value;
234 END IF;
235 IF p_page_parameter_tbl(i).parameter_name= 'FII_INVOICE_ID' OR p_page_parameter_tbl(i).parameter_name= 'FII_AP_INVOICE_ID' THEN
236 -- Removed on 30-May as part of enhancement 4234120
237 -- l_invoice_number := p_page_parameter_tbl(i).parameter_value;
238 get_invoice_id(p_page_parameter_tbl,p_invoice_number);
239 END IF;
240 IF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' THEN
241 l_currency := p_page_parameter_tbl(i).parameter_id;
242 END IF;
243 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
244 p_period_type := p_page_parameter_tbl(i).parameter_value;
245 END IF;
246 IF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
247 p_view_by := p_page_parameter_tbl(i).parameter_value;
248 END IF;
249 END LOOP;
250 END IF;
251
252 If p_period_type is not null then
253 CASE p_period_type
254 WHEN 'FII_TIME_WEEK' THEN p_record_type_id := 32;
255 WHEN 'FII_TIME_ENT_PERIOD' THEN p_record_type_id := 64;
256 WHEN 'FII_TIME_ENT_QTR' THEN p_record_type_id := 128;
257 WHEN 'FII_TIME_ENT_YEAR' THEN p_record_type_id := 256;
258 END CASE;
259 End if;
260 If l_currency is not null then
261 IF substr(l_currency,2,11) = 'FII_GLOBAL1' THEN
262 p_currency := '_prim_g';
263 ELSIF substr(l_currency,2,11) = 'FII_GLOBAL2' THEN
264 p_currency := '_sec_g';
265 ELSE
266 p_currency := '_b';
267 END IF;
268 End if;
269 If p_view_by is not null then
270 IF p_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' then
271 p_column_name := 'ORG_ID';
272 p_table_name := '(select organization_id id, name value from hr_all_organization_units)';
273 Elsif p_view_by = 'SUPPLIER+POA_SUPPLIERS' then
274 p_column_name := 'SUPPLIER_ID';
275 p_table_name := '(select id, value from POA_SUPPLIERS_V)';
276 End if;
277 End if;
278 IF (p_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' AND p_supplier = 'All') THEN
279 p_gid := 4;
280 ELSE
281 p_gid := 0;
282 END IF;
283
284 --added by vkazhipu
285
286 IF p_supplier is not null and p_supplier <> 'All' then
287 g_gid := 0;
288 ELSE
289 g_gid := 4;
290 END IF;
291
292
293 /* Added for R12 MOAC */
294
295 IF g_security_profile_id IS NULL THEN
296 g_security_profile_id := fii_pmv_util.get_sec_profile;
297 END IF;
298
299 IF g_security_org_id IS NULL THEN
300 g_security_org_id := fnd_profile.value('ORG_ID');
301 END IF;
302
303 IF g_business_group_id IS NULL THEN
304 g_business_group_id := fii_pmv_util.get_business_group;
305 END IF;
306
307 IF p_operating_unit = 'All' THEN
308
309 /* Security is dictated by 'MO: Security Profile'. */
310 IF g_security_profile_id is not null AND g_security_profile_id <> -1 THEN
311
312 SELECT view_all_organizations_flag, business_group_id
313 INTO l_all_org_flag, l_business_group_id
314 FROM per_security_profiles
315 WHERE security_profile_id = g_security_profile_id;
316
317 /* Case 1: User has access to all organizations. */
318 IF l_all_org_flag = 'Y' and l_business_group_id is NULL THEN
319 p_org_where := ' ';
320 ELSIF l_all_org_flag = 'Y' and l_business_group_id is NOT NULL THEN
321
322 SELECT COUNT(1) INTO l_org_count
323 FROM hr_operating_units hr, ap_system_parameters_all ap
324 WHERE hr.business_group_id = l_business_group_id
325 AND hr.organization_id = ap.org_id;
326
327 /* Case 2: User has access to more than 5 organizations. */
328 IF l_org_count > 5 THEN
329 p_org_where := ' and f.org_id in (select organization_id from hr_operating_units hr, ap_system_parameters_all ap where hr.business_group_id = :BUSINESS_GROUP and hr.organization_id = ap.org_id) ';
330
331 /* Case 3: User has access to 2-5 organizations. */
332 ELSIF (l_org_count >= 2 and l_org_count <= 5) THEN
333 FOR C1_Rec in (select organization_id from hr_operating_units hr, ap_system_parameters_all ap where hr.business_group_id = l_business_group_id and hr.organization_id = ap.org_id)
334 LOOP
335 l_org_list := l_org_list||C1_Rec.organization_id||',';
336 END LOOP;
337 l_org_list := substr(l_org_list, 1, length(l_org_list)-1);
338 p_org_where := ' and f.org_id in ('||l_org_list||') ';
339
340 /* CASE 4: User has access to a single organization. */
341 ELSIF l_org_count = 1 THEN
342 SELECT organization_id INTO l_org_id FROM hr_operating_units hr, ap_system_parameters_all ap WHERE hr.business_group_id = l_business_group_id AND hr.organization_id = ap.org_id;
343 p_org_where := ' and f.org_id = ' || l_org_id;
344
345 /* CASE 5: User has access to no organizations. */
346 ELSIF l_org_count = 0 THEN
347 p_org_where := ' and f.org_id = -1 ';
348
349 END IF;
350
351 ELSE
352
353 SELECT COUNT(1)
354 INTO l_org_count
355 FROM per_organization_list per, ap_system_parameters_all ap
356 WHERE per.security_profile_id = g_security_profile_id
357 AND per.organization_id = ap.org_id;
358
359 /* Case 2: User has access to more than 5 organizations. */
360 IF l_org_count > 5 THEN
361 p_org_where := ' and f.org_id in (select organization_id from per_organization_list per, ap_system_parameters_all ap where per.security_profile_id = :SEC_ID and per.organization_id = ap.org_id) ';
362
363 /* Case 3: User has access to 2-5 organizations. */
364 ELSIF (l_org_count >= 2 and l_org_count <= 5) THEN
365 FOR C1_Rec in (select organization_id from per_organization_list per, ap_system_parameters_all ap where per.security_profile_id = g_security_profile_id and per.organization_id = ap.org_id)
366 LOOP
367 l_org_list := l_org_list||C1_Rec.organization_id||',';
368 END LOOP;
369 l_org_list := substr(l_org_list, 1, length(l_org_list)-1);
370 p_org_where := ' and f.org_id in ('||l_org_list||') ';
371
372 /* CASE 4: User has access to a single organization. */
373 ELSIF l_org_count = 1 THEN
374 SELECT organization_id INTO l_org_id FROM per_organization_list per, ap_system_parameters_all ap WHERE security_profile_id = g_security_profile_id AND per.organization_id = ap.org_id;
375 p_org_where := ' and f.org_id = ' || l_org_id;
376 /* CASE 5: User has access to no organizations. */
377 ELSIF l_org_count = 0 THEN
378 p_org_where := ' and f.org_id = -1 ';
379 END IF;
380
381 END IF;
382
383 --Security is dictated by 'MO: Security Profile'.
384 ELSIF g_security_org_id is not null THEN
385 -- CASE 4: User has access to a single organization.
386 p_org_where := 'and f.org_id = :SEC_ORG_ID';
387 ELSE
388 -- CASE 5: User has access to no organizations.
389 p_org_where := ' and f.org_id = -1 ';
390 END IF;
391
392 END IF;
393 IF p_operating_unit is not null and p_operating_unit <> 'All' then
394 p_org_where := ' and f.org_id = &ORGANIZATION+FII_OPERATING_UNITS ';
395 END IF;
396 IF p_supplier is not null and p_supplier <> 'All' then
397 p_supplier_where := ' and f.supplier_id = &SUPPLIER+POA_SUPPLIERS ';
398 END IF;
399
400 END get_parameters;
401
402 /*public procedure. binding variables is done here.*/
403 PROCEDURE Bind_Variable
404 (p_sqlstmt IN Varchar2,
405 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
406 p_sql_output OUT NOCOPY Varchar2,
407 p_bind_output_table OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL,
408 p_invoice_number IN Varchar2 Default null,
409 p_record_type_id IN Number Default Null,
410 p_view_by IN Varchar2 Default Null,
411 p_gid IN Number Default Null,
412 p_period_start IN Date Default null,
413 p_check_id IN Number Default null,
414
415 p_created IN Varchar2 Default null,
416 p_stopped IN Varchar2 Default null,
417 p_stop_released IN Varchar2 Default null,
418 p_cleared IN Varchar2 Default null,
419 p_reconciled IN Varchar2 Default null,
420 p_unreconciled IN Varchar2 Default null,
421 p_uncleared IN Varchar2 Default null,
422 p_voided IN Varchar2 Default null ,
423
424 p_entry IN Varchar2 Default null,
425 p_hold_placed IN Varchar2 Default null,
426 p_hold_released IN Varchar2 Default null,
427 p_prepay_applied IN Varchar2 Default null,
428 p_prepay_unapplied IN Varchar2 Default null,
429 p_payment IN Varchar2 Default null,
430 p_paymt_void IN Varchar2 Default null,
431 p_paymt_stop IN Varchar2 Default null,
432 p_paymt_release IN Varchar2 Default null,
433 p_line_number IN Number Default null,
434
435 p_fiibind1 IN Varchar2 Default null,
436 p_fiibind2 IN Varchar2 Default null,
437 p_fiibind3 IN Varchar2 Default null,
438 p_fiibind4 IN Varchar2 Default null,
439 p_fiibind5 IN Varchar2 Default null,
440 p_fiibind6 IN Varchar2 Default null
441 ) IS
442 l_bind_rec BIS_QUERY_ATTRIBUTES;
443
444 BEGIN
445 p_bind_output_table := BIS_QUERY_ATTRIBUTES_TBL();
446 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
447 p_sql_output := p_sqlstmt;
448
449 p_bind_output_table.EXTEND;
450 l_bind_rec.attribute_name := ':FIIBIND1';
451 l_bind_rec.attribute_value := to_char(p_fiibind1);
452 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
453 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
454 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
455 p_bind_output_table.EXTEND;
456 l_bind_rec.attribute_name := ':FIIBIND2';
457 l_bind_rec.attribute_value := to_char(p_fiibind2);
458 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
459 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
460 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
461 p_bind_output_table.EXTEND;
462 l_bind_rec.attribute_name := ':FIIBIND3';
463 l_bind_rec.attribute_value := to_char(p_fiibind3);
464 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
465 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
466 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
467 p_bind_output_table.EXTEND;
468 l_bind_rec.attribute_name := ':FIIBIND4';
469 l_bind_rec.attribute_value := to_char(p_fiibind4);
470 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
471 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
472 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
473 p_bind_output_table.EXTEND;
474 l_bind_rec.attribute_name := ':FIIBIND5';
475 l_bind_rec.attribute_value := to_char(p_fiibind5);
476 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
477 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
478 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
479 p_bind_output_table.EXTEND;
480 l_bind_rec.attribute_name := ':FIIBIND6';
481 l_bind_rec.attribute_value := to_char(p_fiibind6);
482 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
483 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
484 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
485 p_bind_output_table.EXTEND;
486 l_bind_rec.attribute_name := ':ENTRY';
487 l_bind_rec.attribute_value := to_char(p_entry);
488 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
489 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
490 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
491 p_bind_output_table.EXTEND;
492 l_bind_rec.attribute_name := ':HOLD_PLACED';
493 l_bind_rec.attribute_value := to_char(p_hold_placed);
494 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
495 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
496 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
497 p_bind_output_table.EXTEND;
498 l_bind_rec.attribute_name := ':HOLD_RELEASED';
499 l_bind_rec.attribute_value := to_char(p_hold_released);
500 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
501 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
502 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
503 p_bind_output_table.EXTEND;
504 l_bind_rec.attribute_name := ':PREPAY_APPLIED';
505 l_bind_rec.attribute_value := to_char(p_prepay_applied);
506 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
507 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
508 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
509 p_bind_output_table.EXTEND;
510 l_bind_rec.attribute_name := ':PREPAY_UNAPPLIED';
511 l_bind_rec.attribute_value := to_char(p_prepay_unapplied);
512 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
513 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
514 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
515 p_bind_output_table.EXTEND;
516 l_bind_rec.attribute_name := ':PAYMT';
517 l_bind_rec.attribute_value := to_char(p_payment);
518 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
519 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
520 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
521 p_bind_output_table.EXTEND;
522 l_bind_rec.attribute_name := ':PAYMT_VOID';
523 l_bind_rec.attribute_value := to_char(p_paymt_void);
524 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
525 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
526 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
527 p_bind_output_table.EXTEND;
528 l_bind_rec.attribute_name := ':PAYMT_STOP';
529 l_bind_rec.attribute_value := to_char(p_paymt_stop);
530 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
531 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
532 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
533 p_bind_output_table.EXTEND;
534 l_bind_rec.attribute_name := ':PAYMT_RELEASE';
535 l_bind_rec.attribute_value := to_char(p_paymt_release);
536 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
537 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
538 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
539
540 p_bind_output_table.EXTEND;
541 l_bind_rec.attribute_name := ':CREATED';
542 l_bind_rec.attribute_value := to_char(p_created);
543 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
544 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
545 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
546 p_bind_output_table.EXTEND;
547 l_bind_rec.attribute_name := ':STOPPED';
548 l_bind_rec.attribute_value := to_char(p_stopped);
549 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
550 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
551 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
552 p_bind_output_table.EXTEND;
553 l_bind_rec.attribute_name := ':STOP_RELEASED';
554 l_bind_rec.attribute_value := to_char(p_stop_released);
555 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
556 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
557 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
558 p_bind_output_table.EXTEND;
559 l_bind_rec.attribute_name := ':CLEARED';
560 l_bind_rec.attribute_value := to_char(p_cleared);
561 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
562 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
563 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
564 p_bind_output_table.EXTEND;
565 l_bind_rec.attribute_name := ':RECONCILED';
566 l_bind_rec.attribute_value := to_char(p_reconciled);
567 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
568 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
569 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
570 p_bind_output_table.EXTEND;
571 l_bind_rec.attribute_name := ':UNRECONCILED';
572 l_bind_rec.attribute_value := to_char(p_unreconciled);
573 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
574 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
575 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
576 p_bind_output_table.EXTEND;
577 l_bind_rec.attribute_name := ':UNCLEARED';
578 l_bind_rec.attribute_value := to_char(p_uncleared);
579 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
580 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
581 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
582 p_bind_output_table.EXTEND;
583 l_bind_rec.attribute_name := ':VOIDED';
584 l_bind_rec.attribute_value := to_char(p_voided);
585 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
586 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
587 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
588
589 p_bind_output_table.EXTEND;
590 l_bind_rec.attribute_name := ':INVOICE_ID';
591 l_bind_rec.attribute_value := to_char(p_invoice_number);
592 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
593 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
594 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
595 p_bind_output_table.EXTEND;
596 l_bind_rec.attribute_name := ':VIEW_BY';
597 l_bind_rec.attribute_value := to_char(p_view_by);
598 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
599 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
600 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
601 p_bind_output_table.EXTEND;
602 l_bind_rec.attribute_name := ':RECORD_TYPE_ID';
603 l_bind_rec.attribute_value := to_char(p_record_type_id);
604 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
605 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
606 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
607 p_bind_output_table.EXTEND;
608 l_bind_rec.attribute_name := ':GID';
609 l_bind_rec.attribute_value := to_char(p_gid);
610 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
611 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
612 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
613 p_bind_output_table.EXTEND;
614 l_bind_rec.attribute_name := ':SEC_ID';
615 l_bind_rec.attribute_value := fii_pmv_util.get_sec_profile;
616 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
617 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
618 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
619 p_bind_output_table.EXTEND;
620 l_bind_rec.attribute_name := ':PERIOD_START';
621 l_bind_rec.attribute_value := to_char(p_period_start, 'DD-MM-YYYY');
622 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
623 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
624 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
625 p_bind_output_table.EXTEND;
626 l_bind_rec.attribute_name := ':CHECK_ID';
627 l_bind_rec.attribute_value := to_char(p_check_id);
628 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
629 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
630 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
631 p_bind_output_table.EXTEND;
632
633 l_bind_rec.attribute_name := ':LINE_NUMBER';
634 l_bind_rec.attribute_value := to_char(p_line_number);
635 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
636 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
637 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
638 p_bind_output_table.EXTEND;
639
640 l_bind_rec.attribute_name := ':BUSINESS_GROUP';
641 l_bind_rec.attribute_value := to_char(g_business_group_id);
642 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
643 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
644 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
645 p_bind_output_table.EXTEND;
646
647 l_bind_rec.attribute_name := ':SEC_ORG_ID';
648 l_bind_rec.attribute_value := g_security_org_id;
649 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
650 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
651 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
652 p_bind_output_table.EXTEND;
653
654 --added by vkazhipu
655 l_bind_rec.attribute_name := ':ASOF_DATE_JULIEN';
656 l_bind_rec.attribute_value := to_number(to_char(g_p_as_of_date,'J'));
657 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
658 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
659 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
660 p_bind_output_table.EXTEND;
661
662 l_bind_rec.attribute_name := ':GID2';
663 l_bind_rec.attribute_value := to_char(g_gid);
664 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
665 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
666 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
667 p_bind_output_table.EXTEND;
668
669 l_bind_rec.attribute_name := ':PREVIOUS_DATE';
670 l_bind_rec.attribute_value := to_char(g_previous_date, 'DD-MM-YYYY');
671 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
672 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
673 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
674 p_bind_output_table.EXTEND;
675
676
677 END;
678
679 /*public function which gets invoice id for a given invoice number */
680 PROCEDURE get_invoice_id(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
681 p_invoice_id OUT NOCOPY Number)
682 IS
683 BEGIN
684 IF (p_page_parameter_tbl.count > 0) THEN
685 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
686 IF p_page_parameter_tbl(i).parameter_name = 'FII_INVOICE_ID' OR p_page_parameter_tbl(i).parameter_name= 'FII_AP_INVOICE_ID' THEN
687 p_invoice_id := p_page_parameter_tbl(i).parameter_id;
688 END IF;
689 END LOOP;
690 END IF;
691 END;
692 /*
693
694 PROCEDURE get_period_start(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
695 p_period_start OUT NOCOPY Date,
696 p_days_into_period OUT NOCOPY Number,
697 p_cur_period OUT NOCOPY Number,
698 p_id_column OUT NOCOPY Varchar2)
699 IS
700 l_as_of_date Date;
701 BEGIN
702 IF (p_page_parameter_tbl.count > 0) THEN
703 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
704 IF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
705 l_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value, 'DD-MM-YYYY');
706 END IF;
707 IF p_page_parameter_tbl(i).parameter_name = 'FII_TIME_WEEK_FROM' THEN
708 p_cur_period := to_number(p_page_parameter_tbl(i).parameter_id);
709 p_id_column := 'week_id';
710 select (to_date(l_as_of_date,'DD-MM-YYYY') - start_date) into p_days_into_period from fii_time_week where week_id = p_cur_period;
711 p_period_start := fii_time_api.cwk_start(l_as_of_date);
712 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_TIME_ENT_PERIOD_FROM' THEN
713 p_cur_period := p_page_parameter_tbl(i).parameter_id;
714 p_id_column := 'ent_period_id';
715 select (l_as_of_date - start_date) into p_days_into_period from fii_time_ent_period where ent_period_id = p_cur_period;
716 p_period_start := fii_time_api.ent_cper_start(l_as_of_date);
717 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_TIME_ENT_QTR_FROM' THEN
718 p_cur_period := p_page_parameter_tbl(i).parameter_id;
719 p_id_column := 'ent_qtr_id';
720 select (l_as_of_date - start_date) into p_days_into_period from fii_time_ent_qtr where ent_qtr_id = p_cur_period;
721 p_period_start := fii_time_api.ent_cqtr_start(l_as_of_date);
722 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_TIME_ENT_YEAR_FROM' THEN
723 p_cur_period := p_page_parameter_tbl(i).parameter_id;
724 p_id_column := 'ent_year_id';
725 select (l_as_of_date - start_date) into p_days_into_period from fii_time_ent_year where ent_year_id = p_cur_period;
726 p_period_start := fii_time_api.ent_cyr_start(l_as_of_date);
727 END IF;
728 END LOOP;
729 END IF;
730 p_days_into_period := l_as_of_date - p_period_start;
731 END;
732 */
733
734 PROCEDURE get_period_start(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
735 p_period_start OUT NOCOPY Date,
736 p_days_into_period OUT NOCOPY Number,
737 p_cur_period OUT NOCOPY Number,
738 p_id_column OUT NOCOPY Varchar2)
739 IS
740 l_as_of_date DATE;
741 l_record_type_id NUMBER;
742 l_start_date date;
743 l_period_type varchar2(200);
744
745 BEGIN
746 IF (p_page_parameter_tbl.count > 0) THEN
747 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
748 IF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
749 l_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value, 'DD-MM-YYYY');
750 END IF;
751 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
752 l_period_type := p_page_parameter_tbl(i).parameter_value;
753 END IF;
754 IF p_page_parameter_tbl(i).parameter_name= 'TIME+FII_TIME_WEEK_FROM' THEN
755 p_cur_period := p_page_parameter_tbl(i).parameter_id;
756 p_id_column := 'week_id';
757 END IF;
758 IF p_page_parameter_tbl(i).parameter_name= 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
759 p_cur_period := p_page_parameter_tbl(i).parameter_id;
760 p_id_column := 'ent_period_id';
761 END IF;
762 IF p_page_parameter_tbl(i).parameter_name= 'TIME+FII_TIME_ENT_QTR_FROM' THEN
763 p_cur_period := p_page_parameter_tbl(i).parameter_id;
764 p_id_column := 'ent_qtr_id';
765 END IF;
766 IF p_page_parameter_tbl(i).parameter_name= 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
767 p_cur_period := p_page_parameter_tbl(i).parameter_id;
768 p_id_column := 'ent_year_id';
769 END IF;
770 IF p_page_parameter_tbl(i).parameter_name= 'BIS_CURRENT_REPORT_START_DATE' THEN
771 p_period_start := to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
772 END IF;
773
774 END LOOP;
775 END IF;
776
777 CASE l_period_type
778 WHEN 'FII_TIME_WEEK' THEN
779 select (l_as_of_date - start_date) into p_days_into_period from fii_time_week where week_id = p_cur_period;
780 WHEN 'FII_TIME_ENT_PERIOD' THEN
781 select (l_as_of_date - start_date) into p_days_into_period from fii_time_ent_period where ent_period_id = p_cur_period;
782 WHEN 'FII_TIME_ENT_QTR' THEN
783 select (l_as_of_date - start_date) into p_days_into_period from fii_time_ent_qtr where ent_qtr_id = p_cur_period;
784 WHEN 'FII_TIME_ENT_YEAR' THEN
785 select (l_as_of_date - start_date) into p_days_into_period from fii_time_ent_year where ent_year_id = p_cur_period;
786 ELSE
787 select (l_as_of_date - start_date) into p_days_into_period from fii_time_ent_year where ent_year_id = p_cur_period;
788 END CASE;
789
790 END;
791
792 PROCEDURE get_period_strt(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
793 p_period_start OUT NOCOPY Date,
794 p_days_into_period OUT NOCOPY Number,
795 p_cur_period OUT NOCOPY Number,
796 p_id_column OUT NOCOPY Varchar2)
797 IS
798 l_as_of_date DATE;
799 l_period_type VARCHAR2(32000);
800
801 BEGIN
802 IF (p_page_parameter_tbl.count > 0) THEN
803 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
804 IF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
805 l_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value, 'DD-MM-YYYY');
806 END IF;
807 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
808 l_period_type := p_page_parameter_tbl(i).parameter_value;
809 END IF;
810 END LOOP;
811
812 IF l_as_of_date is not null then
813 If l_period_type is not null then
814 CASE l_period_type
815 WHEN 'FII_TIME_WEEK' THEN
816 select week_id into p_cur_period from fii_time_day where report_date=l_as_of_date;
817 p_id_column := 'week_id';
818 BEGIN
819 p_period_start := fii_time_api.cwk_start(l_as_of_date);
820 EXCEPTION
821 When no_data_found then
822 p_period_start := fii_time_api.cwk_start(sysdate);
823 END;
824 WHEN 'FII_TIME_ENT_PERIOD' THEN
825 select ent_period_id into p_cur_period from fii_time_day where report_date=l_as_of_date;
826 p_id_column := 'ent_period_id';
827 BEGIN
828 p_period_start := fii_time_api.ent_cper_start(l_as_of_date);
829 EXCEPTION
830 When no_data_found then
831 p_period_start := fii_time_api.ent_cper_start(sysdate);
832 END;
833 WHEN 'FII_TIME_ENT_QTR' THEN
834 select ent_qtr_id into p_cur_period from fii_time_day where report_date=l_as_of_date;
835 p_id_column := 'ent_qtr_id';
836 BEGIN
837 p_period_start := fii_time_api.ent_cqtr_start(l_as_of_date);
838 EXCEPTION
839 When no_data_found then
840 p_period_start := fii_time_api.ent_cqtr_start(sysdate);
841 END;
842 WHEN 'FII_TIME_ENT_YEAR' THEN
843 select ent_year_id into p_cur_period from fii_time_day where report_date=l_as_of_date;
844 p_id_column := 'ent_year_id';
845 BEGIN
846 p_period_start := fii_time_api.ent_cyr_start(l_as_of_date);
847 EXCEPTION
848 When no_data_found then
849 p_period_start := fii_time_api.ent_cyr_start(sysdate);
850 END;
851 END CASE;
852 End if;
853 END IF;
854 END IF;
855 p_days_into_period := l_as_of_date - p_period_start;
856 END;
857
858 PROCEDURE get_report_source(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
859 p_report_source OUT NOCOPY Varchar2)
860 IS
861 BEGIN
862 IF (p_page_parameter_tbl.count > 0) THEN
863 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
864 IF p_page_parameter_tbl(i).parameter_name = 'FII_REPORT_SOURCE' THEN
865 p_report_source := p_page_parameter_tbl(i).parameter_id;
866 END IF;
867 END LOOP;
868 END IF;
869 END;
870
871 PROCEDURE get_check_id(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
872 p_check_id OUT NOCOPY Number)
873 IS
874 l_check_number Number;
875 BEGIN
876 IF (p_page_parameter_tbl.count > 0) THEN
877 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
878 IF p_page_parameter_tbl(i).parameter_name = 'FII_CHECK_ID' THEN
879 p_check_id := p_page_parameter_tbl(i).parameter_id;
880 END IF;
881 END LOOP;
882 END IF;
883 END;
884
885 FUNCTION get_base_curr_colname(p_currency IN Varchar2, p_column_name IN Varchar2) return Varchar2
886 IS
887 p_curr_amt_col VARCHAR2(100);
888 BEGIN
889 If p_currency is not null then
890 IF p_currency = '_prim_g' THEN
891 p_curr_amt_col := 'prim_'||p_column_name;
892 ELSIF p_currency = '_sec_g' THEN
893 p_curr_amt_col := 'sec_'||p_column_name;
894 ELSE
895 p_curr_amt_col := p_column_name||'_b';
896 END IF;
897 End if;
898 RETURN p_curr_amt_col;
899 END;
900
901 FUNCTION get_period_type_suffix (p_period_type IN Varchar2) return Varchar2
902 IS
903 l_per_type Varchar2(100);
904 BEGIN
905 IF p_period_type = 'FII_TIME_WEEK' then
906 l_per_type := '_wtd';
907 ELSIF p_period_type = 'FII_TIME_ENT_PERIOD' then
908 l_per_type := '_mtd';
909 ELSIF p_period_type = 'FII_TIME_ENT_QTR' then
910 l_per_type := '_qtd';
911 ELSIF p_period_type = 'FII_TIME_ENT_YEAR' then
912 l_per_type := '_ytd';
913 END IF;
914 return l_per_type;
915 END;
916
917 PROCEDURE get_yes_no_msg(p_yes OUT NOCOPY Varchar2, p_no OUT NOCOPY Varchar2)
918 IS
919 BEGIN
920 p_yes := FND_MESSAGE.get_string('FND', 'FND_DEFAULT_CUST_YES');
921 p_no := FND_MESSAGE.get_string('FND', 'FND_DEFAULT_CUST_NO');
922 END;
923
924 PROCEDURE get_format_mask(p_date_format_mask OUT NOCOPY Varchar2)
925 IS
926 BEGIN
927 p_date_format_mask := FND_DATE.output_mask;
928 END;
929
930 FUNCTION determine_OU_LOV RETURN NUMBER IS
931 -- l_security_profile_id Number;
932 -- l_security_org_id Number;
933 l_all_org_flag VARCHAR2(30);
934 l_business_group_id NUMBER;
935
936 BEGIN
937 IF g_security_profile_id IS NULL THEN
938 g_security_profile_id := fii_pmv_util.get_sec_profile;
939 END IF;
940
941 IF g_security_org_id IS NULL THEN
942 g_security_org_id := fnd_profile.value('ORG_ID');
943 END IF;
944
945 -- Bug 5527135: Added the condition to also handle g_security_profile_id <> -1
946 IF g_security_profile_id is NOT NULL AND g_security_profile_id <> -1 THEN
947 SELECT view_all_organizations_flag, business_group_id
948 INTO l_all_org_flag, l_business_group_id
949 FROM per_security_profiles
950 WHERE security_profile_id = g_security_profile_id;
951
952 /* 'MO: Security Profile' is defined with a global view all security profile.*/
953 IF l_all_org_flag = 'Y' and l_business_group_id is NULL THEN
954 return 1;
955 /* 'MO: Security Profile' is defined with a business group view all security profile.*/
956 ELSIF l_all_org_flag = 'Y' and l_business_group_id is NOT NULL THEN
957 return 2;
958 ELSE
959 /* 'MO: Security Profile' is not defined with a view all security profile.*/
960 return 3;
961 END IF;
962 ELSE
963 /* 'MO: Security Profile' is not defined. */
964 return 4;
965 END IF;
966
967 END;
968
969 FUNCTION get_business_group RETURN NUMBER IS
970 -- l_security_profile_id NUMBER;
971 l_business_group_id NUMBER;
972 BEGIN
973 g_security_profile_id := fii_pmv_util.get_sec_profile;
974
975 SELECT business_group_id
976 INTO l_business_group_id
977 FROM per_security_profiles
978 WHERE security_profile_id = g_security_profile_id;
979
980 return NVL(l_business_group_id,-1);
981 EXCEPTION
982 when too_many_rows then
983 return -1;
984 when others then
985 return -1;
986
987 END;
988
989 END fii_pmv_util;