1 PACKAGE BODY IGS_CA_COMPUTE_DA_VAL_PKG
2 /* $Header: IGSCA15B.pls 120.1 2005/08/16 22:19:44 appldev noship $ */
3 /*****************************************************
4 || Created By : Navin Sidana
5 || Created On : 10/13/2004
6 || Purpose : Package for computing date alias values.
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || Who When What
10 || nsidana 10/13/2004 Created
11 *****************************************************/
12 AS
13
14 FUNCTION cal_da_elt_val(p_sys_date_type IN VARCHAR2,
15 p_cal_type IN VARCHAR2,
16 p_seq_number IN NUMBER,
17 p_org_unit IN VARCHAR2,
18 p_prog_type IN VARCHAR2,
19 p_prog_ver IN VARCHAR2,
20 p_app_type IN VARCHAR2
21 ) RETURN DATE
22 /*****************************************************
23 || Created By : Navin Sidana
24 || Created On : 10/13/2004
25 || Purpose : Main proc to compute the date alias value.
26 || Known limitations, enhancements or remarks :
27 || Change History :
28 || Who When What
29 || nsidana 10/13/2004 Created
30 *****************************************************/
31 IS
32
33 CURSOR get_site_da(cp_sys_date VARCHAR2)
34 IS
35 SELECT date_alias
36 FROM IGS_CA_DA_CONFIGS
37 WHERE sys_date_type = cp_sys_date;
38
39 l_org_unit VARCHAR2(30) := NULL;
40 l_prg_type VARCHAR2(30) := NULL;
41 l_prd_cd VARCHAR2(30) := NULL;
42 l_app_type VARCHAR2(30) := NULL;
43 l_dt_alias VARCHAR2(30) := NULL;
44 l_dt_val DATE := NULL;
45 l_found BOOLEAN := FALSE;
46 l_prog_label VARCHAR2(100);
47 l_label VARCHAR2(500);
48 l_debug_str VARCHAR2(4000);
49 l_seq_num NUMBER;
50
51 FUNCTION chk_da_ovrd(p_sys_date_type IN VARCHAR2,
52 p_elt_level IN VARCHAR2,
53 p_elt_code IN VARCHAR2,
54 p_dt_alias OUT NOCOPY VARCHAR2) RETURN BOOLEAN
55 /*****************************************************
56 || Created By : Navin Sidana
57 || Created On : 10/13/2004
58 || Purpose : Local function to check if the DA has been
59 || overridden at a level.
60 || Known limitations, enhancements or remarks :
61 || Change History :
62 || Who When What
63 || nsidana 10/13/2004 Created
64 *****************************************************/
65 IS
66
67 CURSOR cur_da_ovrd(cp_sys_date_type VARCHAR2,cp_elt_level VARCHAR2,cp_elt_code VARCHAR2)
68 IS
69 SELECT date_alias
70 FROM IGS_CA_DA_OVD_VALS
71 WHERE sys_date_type = cp_sys_date_type AND
72 element_code = cp_elt_level AND
73 element_code_value = cp_elt_code;
74
75 l_dt_alias VARCHAR2(30) := NULL;
76
77 BEGIN
78
79 OPEN cur_da_ovrd(p_sys_date_type,p_elt_level,p_elt_code);
80 FETCH cur_da_ovrd INTO l_dt_alias;
81 CLOSE cur_da_ovrd;
82
83 IF (l_dt_alias IS NOT NULL)
84 THEN
85 p_dt_alias := l_dt_alias;
86 RETURN TRUE;
87 ELSE
88 p_dt_alias := null;
89 RETURN FALSE;
90 END IF;
91 END chk_da_ovrd;
92
93 FUNCTION get_da_inst(p_dt_alias IN VARCHAR2,
94 p_cal_type IN VARCHAR2,
95 p_seq_num IN NUMBER,
96 p_da_inst_val OUT NOCOPY DATE) RETURN BOOLEAN
97 /*****************************************************
98 || Created By : Navin Sidana
99 || Created On : 10/13/2004
100 || Purpose : Local function to compute the DAI in a
101 || calendar instance.
102 || Known limitations, enhancements or remarks :
103 || Change History :
104 || Who When What
105 || nsidana 10/13/2004 Created
106 || skpandey 17-AUG-2005 Bug:4356272
107 || Added an additional level "APP_TYPE" to find the next available DA value
108 *****************************************************/
109 IS
110
111 CURSOR count_dai_in_ci(cp_dt_alias VARCHAR2, cp_cal_type VARCHAR2, cp_seq_num NUMBER)
112 IS
113 SELECT count(*)
114 FROM IGS_CA_DA_INST
115 WHERE dt_alias = cp_dt_alias AND
116 cal_type = cp_cal_type AND
117 ci_sequence_number = cp_seq_num;
118
119
120 CURSOR get_da_inst_val(cp_dt_alias VARCHAR2, cp_cal_type VARCHAR2, cp_seq_num NUMBER)
121 IS
122 SELECT alias_val
123 FROM IGS_CA_DA_INST_V
124 WHERE dt_alias = cp_dt_alias AND
125 cal_type = cp_cal_type AND
126 ci_sequence_number = cp_seq_num;
127
128 l_count NUMBER;
129 l_da_inst_val DATE;
130
131 BEGIN
132 l_count := 0;
133
134 OPEN count_dai_in_ci(p_dt_alias,p_cal_type,p_seq_num);
135 FETCH count_dai_in_ci INTO l_count;
136 CLOSE count_dai_in_ci;
137
138 IF (l_count > 1) OR (l_count = 0)
139 THEN
140 p_da_inst_val := NULL;
141 RETURN FALSE; -- As multiple DAI exists in the CI and we are not sure which one to return back.
142 ELSE
143 -- One instance exists, return that.
144
145 OPEN get_da_inst_val(p_dt_alias,p_cal_type,p_seq_num);
146 FETCH get_da_inst_val INTO l_da_inst_val;
147 CLOSE get_da_inst_val;
148
149 IF (l_da_inst_val IS NOT NULL)
150 THEN
151 p_da_inst_val := l_da_inst_val;
152 RETURN TRUE;
153 ELSE
154 p_da_inst_val := NULL;
155 RETURN FALSE;
156 END IF;
157 END IF;
158 END get_da_inst; -- <End of local function.>
159
160 BEGIN /* Main Procedure begins here */
161
162 l_prog_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val';
163 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.start';
164
165 l_org_unit := p_org_unit;
166 l_prg_type := p_prog_type;
167 l_prd_cd := p_prog_ver;
168 l_app_type := p_app_type;
169
170 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
171 l_debug_str := 'Starting values : Sys Date Type ='||p_sys_date_type||'Cal Type =' || p_cal_type || 'Seq Num ='||p_seq_number ||
172 'Org Unit ='|| l_org_unit||'Prog Type ='||l_prg_type || 'Prog Ver ='||l_prd_cd || 'App_Type = '|| l_app_type;
173 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
174 END IF;
175
176
177 IF ((l_app_type IS NOT NULL) AND (NOT l_found))
178 THEN
179 l_dt_alias := NULL;
180
181 IF chk_da_ovrd(p_sys_date_type,'APP_TYPE',l_app_type,l_dt_alias)
182 THEN
183 l_found := TRUE;
184 l_dt_val := NULL;
185
186 IF get_da_inst(l_dt_alias,p_cal_type,p_seq_number,l_dt_val)
187 THEN
188 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
189 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.DAI_found_for_overd_DA_at_app_type_lvl';
190 l_debug_str := 'Computed DAI for values : Date Alias ='||l_dt_alias||' Cal Type ='||p_cal_type||' Seq Num ='||p_seq_number;
191 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
192 END IF;
193 RETURN l_dt_val;
194 ELSE
195 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
196 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.DAI_not_found_for_overd_DA_at_prog_type_lvl';
197 l_debug_str := 'Tried computing DAI for values : Date Alias ='||l_dt_alias||' Cal Type ='||p_cal_type||' Seq Num ='||p_seq_number;
198 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
199 END IF;
200 l_found := FALSE; -- Go up the heirarchy and try to find the next available DA value.
201 END IF;
202 END IF;
203 END IF;
204
205
206 IF ((l_prd_cd IS NOT NULL) AND (NOT l_found))
207 THEN
208 l_dt_alias := NULL;
209
210 IF chk_da_ovrd(p_sys_date_type,'PRG_VER',l_prd_cd,l_dt_alias)
211 THEN
212 l_found := TRUE;
213 l_dt_val := NULL;
214
215 IF get_da_inst(l_dt_alias,p_cal_type,p_seq_number,l_dt_val)
216 THEN
217 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
218 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.DAI_found_for_overd_DA_at_prog_ver_lvl';
219 l_debug_str := 'Computed DAI for values : Date Alias ='||l_dt_alias||' Cal Type ='||p_cal_type||' Seq Num ='||p_seq_number;
220 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
221 END IF;
222 RETURN l_dt_val;
223 ELSE
224 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
225 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.DAI_not_found_for_overd_DA_at_prog_ver_lvl';
226 l_debug_str := 'Tried computing DAI for values : Date Alias ='||l_dt_alias||' Cal Type ='||p_cal_type||' Seq Num ='||p_seq_number;
227 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
228 END IF;
229 l_found := FALSE; -- Go up the heirarchy and try to find the next available DA value.
230 END IF;
231 END IF;
232 END IF;
233
234 IF ((l_prg_type IS NOT NULL) AND (NOT l_found))
235 THEN
236 l_dt_alias := NULL;
237
238 IF chk_da_ovrd(p_sys_date_type,'PRG_TYPE',l_prg_type,l_dt_alias)
239 THEN
240 l_found := TRUE;
241 l_dt_val := NULL;
242
243 IF get_da_inst(l_dt_alias,p_cal_type,p_seq_number,l_dt_val)
244 THEN
245 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
246 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.DAI_found_for_overd_DA_at_prog_type_lvl';
247 l_debug_str := 'Computed DAI for values : Date Alias ='||l_dt_alias||' Cal Type ='||p_cal_type||' Seq Num ='||p_seq_number;
248 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
249 END IF;
250 RETURN l_dt_val;
251 ELSE
252 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
253 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.DAI_not_found_for_overd_DA_at_prog_type_lvl';
254 l_debug_str := 'Tried computing DAI for values : Date Alias ='||l_dt_alias||' Cal Type ='||p_cal_type||' Seq Num ='||p_seq_number;
255 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
256 END IF;
257 l_found := FALSE; -- Go up the heirarchy and try to find the next available DA value.
258 END IF;
259 END IF;
260 END IF;
261
262 IF ((l_org_unit IS NOT NULL) AND (NOT l_found))
263 THEN
264 l_dt_alias := NULL;
265
266 IF chk_da_ovrd(p_sys_date_type,'ORG_UNIT',l_org_unit,l_dt_alias)
267 THEN
268 l_found := TRUE;
269 l_dt_val := NULL;
270
271 IF get_da_inst(l_dt_alias,p_cal_type,p_seq_number,l_dt_val)
272 THEN
273 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
274 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.DAI_found_for_overd_DA_at_org_unit_lvl';
275 l_debug_str := 'Computed DAI for values : Date Alias ='||l_dt_alias||' Cal Type ='||p_cal_type||' Seq Num ='||p_seq_number;
276 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
277 END IF;
278 RETURN l_dt_val;
279 ELSE
280 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
281 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.DAI_not_found_for_overd_DA_at_org_unit_lvl';
282 l_debug_str := 'Tried computing DAI for values : Date Alias ='||l_dt_alias||' Cal Type ='||p_cal_type||' Seq Num ='||p_seq_number;
286 END IF;
283 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
284 END IF;
285 l_found := FALSE; -- Go up the heirarchy and try to find the next available DA value.
287 END IF;
288 END IF;
289
290 IF (NOT l_found)
291 THEN
292 -- Get the SITE level default DA for the SDA.
293 l_dt_alias := NULL;
294 OPEN get_site_da(p_sys_date_type);
295 FETCH get_site_da INTO l_dt_alias;
296 CLOSE get_site_da;
297
298 IF (l_dt_alias IS NOT NULL)
299 THEN
300 l_dt_val := NULL;
301
302 IF get_da_inst(l_dt_alias,p_cal_type,p_seq_number,l_dt_val)
303 THEN
304 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
305 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.DAI_found_for_default_DA_at_site_lvl';
306 l_debug_str := 'Computed DAI for values : Date Alias ='||l_dt_alias||' Cal Type ='||p_cal_type||' Seq Num ='||p_seq_number;
307 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
308 END IF;
309 RETURN l_dt_val;
310 ELSE
311 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
312 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.DAI_not_found_for_default_DA_at_site_lvl';
313 l_debug_str := 'Tried computing DAI for values : Date Alias ='||l_dt_alias||' Cal Type ='||p_cal_type||' Seq Num ='||p_seq_number;
314 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
315 END IF;
316 RETURN NULL; -- NO DAI could be found out. Return NULL;
317 END IF;
318 ELSE
319 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
320 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.No_default_DA_found_at_site_lvl';
321 l_debug_str := null;
322 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
323 END IF;
324 RETURN NULL;
325 END IF;
326 END IF;
327 EXCEPTION
328 WHEN OTHERS THEN
329 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
330 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_val.exception_occured';
331 l_debug_str := sqlerrm;
332 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
333 END IF;
334 END cal_da_elt_val;
335
336 FUNCTION cal_da_elt_ofst_val(p_dt_alias IN VARCHAR2,
337 p_da_seq_num IN NUMBER,
338 p_cal_type IN VARCHAR2,
339 p_seq_number IN NUMBER,
340 p_org_unit IN VARCHAR2,
341 p_prog_type IN VARCHAR2,
342 p_prog_ver IN VARCHAR2,
343 p_app_type IN VARCHAR2
344 ) RETURN DATE
345 /*****************************************************
346 || Created By : Navin Sidana
347 || Created On : 10/13/2004
348 || Purpose : Main proc to compute the date alias value
349 || for FA module, considering offsets.
350 || Known limitations, enhancements or remarks :
351 || Change History :
352 || Who When What
353 || nsidana 10/15/2004 Created
354 *****************************************************/
355 IS
356
357 CURSOR cp_get_ofst_dt(cp_dt_alias VARCHAR2,cp_da_seq_num NUMBER,cp_cal_type VARCHAR2,cp_seq_num NUMBER) IS
358 SELECT offset_dt_alias,offset_dai_sequence_number,offset_cal_type,offset_ci_sequence_number,ofst_override
359 FROM IGS_CA_DA_INST_OFST
360 WHERE dt_alias = cp_dt_alias AND
361 dai_sequence_number = cp_da_seq_num AND
362 cal_type = cp_cal_type AND
363 ci_sequence_number = cp_seq_num;
364
365 l_da_ofst_level_rec t_ofst_rec;
366 l_dt_alias VARCHAR2(30);
367 l_cal_type VARCHAR2(30);
368 l_seq_num NUMBER;
369 l_da_used VARCHAR2(30);
370 l_err_msg VARCHAR2(30);
371 l_dai_val DATE := NULL;
372 l_da_seq_num NUMBER;
373 l_msg VARCHAR2(30);
374 l_final_val DATE;
375 l_org_unit VARCHAR2(30);
376 l_prog_type VARCHAR2(30);
377 l_prog_ver VARCHAR2(30);
378 l_app_type VARCHAR2(30);
379 l_prog_label VARCHAR2(100);
380 l_label VARCHAR2(500);
381 l_debug_str VARCHAR2(4000);
382 cp_get_ofst_dt_rec cp_get_ofst_dt%ROWTYPE;
383
384 FUNCTION chk_da_ofst_lvl(p_dt_alias VARCHAR2,
385 p_da_seq_num NUMBER,
386 p_cal_type VARCHAR2,
387 p_seq_num NUMBER) RETURN t_ofst_rec
388 /*****************************************************
389 || Created By : Navin Sidana
390 || Created On : 10/13/2004
391 || Purpose : Checks the level at which the DA has an offset.
392 || Return values : 'DATE_ALIAS_INST' -- Date Alias Instance.
393 || 'DATE_ALIAS' -- Date Alais.
394 || NULL -- no offset defined.
395 ||
396 || Known limitations, enhancements or remarks :
397 || Change History :
398 || Who When What
399 || nsidana 10/15/2004 Created
400 *****************************************************/
401 IS
402
403 -- Cursor to check if the DAI has an offset.
404 CURSOR chk_dai_ofst(cp_dt_alias VARCHAR2,cp_da_seq_num NUMBER,cp_cal_type VARCHAR2,cp_seq_num NUMBER)
405 IS
406 SELECT offset_dt_alias,offset_dai_sequence_number,offset_cal_type,offset_ci_sequence_number,day_offset,week_offset,month_offset,year_offset,ofst_override
407 FROM IGS_CA_DA_INST_OFST
408 WHERE dt_alias = cp_dt_alias AND
409 dai_sequence_number = cp_da_seq_num AND
410 cal_type = cp_cal_type AND
411 ci_sequence_number = cp_seq_num;
412
413 -- Cursor to check if the DA has an offset.
417 FROM IGS_CA_DA_OFST
414 CURSOR chk_da_ofst(cp_dt_alias VARCHAR2)
415 IS
416 SELECT offset_dt_alias,day_offset,week_offset,month_offset,year_offset
418 WHERE dt_alias = cp_dt_alias;
419
420 chk_dai_ofst_rec chk_dai_ofst%ROWTYPE := NULL;
421 chk_da_ofst_rec chk_da_ofst%ROWTYPE := NULL ;
422 l_da_ofst_level_rec t_ofst_rec;
423
424 BEGIN
425 OPEN chk_dai_ofst(p_dt_alias,p_da_seq_num,p_cal_type,p_seq_num);
426 FETCH chk_dai_ofst INTO chk_dai_ofst_rec;
427
428 IF (chk_dai_ofst%FOUND)
429 THEN
430 -- Offset found at DAI level. Record the offset relationship.
431 l_da_ofst_level_rec.ofst_lvl := 'DATE_ALIAS_INST';
432 l_da_ofst_level_rec.dt_alias := chk_dai_ofst_rec.offset_dt_alias;
433 l_da_ofst_level_rec.da_seq_num := chk_dai_ofst_rec.offset_dai_sequence_number;
434 l_da_ofst_level_rec.offset_cal_type := chk_dai_ofst_rec.offset_cal_type;
435 l_da_ofst_level_rec.offset_ci_sequence_number := chk_dai_ofst_rec.offset_ci_sequence_number;
436 l_da_ofst_level_rec.day_offset := chk_dai_ofst_rec.day_offset;
437 l_da_ofst_level_rec.week_offset := chk_dai_ofst_rec.week_offset;
438 l_da_ofst_level_rec.month_offset := chk_dai_ofst_rec.month_offset;
439 l_da_ofst_level_rec.year_offset := chk_dai_ofst_rec.year_offset;
440 l_da_ofst_level_rec.ofst_override := chk_dai_ofst_rec.ofst_override;
441 ELSE
442 OPEN chk_da_ofst(p_dt_alias);
443 FETCH chk_da_ofst INTO chk_da_ofst_rec;
444
445 IF (chk_da_ofst%FOUND)
446 THEN
447 -- Offset found at DA level. Record the offset relationship.
448 l_da_ofst_level_rec.ofst_lvl := 'DATE_ALIAS';
449 l_da_ofst_level_rec.dt_alias := chk_da_ofst_rec.offset_dt_alias;
450 -- l_da_ofst_level_rec.da_seq_num := chk_da_ofst_rec.offset_dai_sequence_number;
451 -- l_da_ofst_level_rec.offset_cal_type := chk_da_ofst_rec.offset_cal_type;
452 -- l_da_ofst_level_rec.offset_ci_sequence_number := chk_da_ofst_rec.offset_ci_sequence_number;
453 l_da_ofst_level_rec.day_offset := chk_da_ofst_rec.day_offset;
454 l_da_ofst_level_rec.week_offset := chk_da_ofst_rec.week_offset;
455 l_da_ofst_level_rec.month_offset := chk_da_ofst_rec.month_offset;
456 l_da_ofst_level_rec.year_offset := chk_da_ofst_rec.year_offset;
457 l_da_ofst_level_rec.ofst_override := null;
458 ELSE
459 -- No offset found at any level.
460 l_da_ofst_level_rec.ofst_lvl := null;
461 l_da_ofst_level_rec.dt_alias := null;
462 l_da_ofst_level_rec.da_seq_num := null;
463 l_da_ofst_level_rec.offset_cal_type := null;
464 l_da_ofst_level_rec.offset_ci_sequence_number := null;
465 l_da_ofst_level_rec.day_offset := null;
466 l_da_ofst_level_rec.week_offset := null;
467 l_da_ofst_level_rec.month_offset := null;
468 l_da_ofst_level_rec.year_offset := null;
469 l_da_ofst_level_rec.ofst_override := null;
470 END IF;
471 END IF;
472
473 IF (chk_dai_ofst%ISOPEN) THEN
474 CLOSE chk_dai_ofst;
475 END IF;
476 IF (chk_da_ofst%ISOPEN) THEN
477 CLOSE chk_da_ofst;
478 END IF;
479 RETURN l_da_ofst_level_rec;
480 END chk_da_ofst_lvl;
481
482 FUNCTION chk_da_alias_used_sda(p_dt_alias IN VARCHAR2) RETURN VARCHAR2
483 /*****************************************************
484 || Created By : Navin Sidana
485 || Created On : 10/13/2004
486 || Purpose : Checks if the DA has been used in a SDA setup.
487 || Return values : 'USED_MORE_THAN_ONE' -- DA used in more than one SDA.
488 || 'NOT_USED_AT_ALL' -- DA not used in any SDA.
489 || l_sys_dt_type -- SDA for which the DA is used in setup.
490 ||
491 || Known limitations, enhancements or remarks :
492 || Change History :
493 || Who When What
494 || nsidana 10/15/2004 Created
495 *****************************************************/
496 AS
497 CURSOR get_sda(cp_dt_alias VARCHAR2)
498 IS
499 SELECT sys_date_type
500 FROM IGS_CA_DA_CONFIGS
501 WHERE date_alias = cp_dt_alias;
502
503 CURSOR get_count(cp_dt_alias VARCHAR2)
504 IS
505 SELECT count(*)
506 FROM IGS_CA_DA_CONFIGS
507 WHERE date_alias = cp_dt_alias;
508
509 l_sys_dt_type VARCHAR2(30);
510 l_count NUMBER;
511
512 BEGIN
513 l_count := 0;
514 OPEN get_count(p_dt_alias);
515 FETCH get_count INTO l_count;
516 CLOSE get_count;
517 IF (l_count = 1)
518 THEN
519 OPEN get_sda(p_dt_alias);
520 FETCH get_sda INTO l_sys_dt_type;
521 CLOSE get_sda;
522 RETURN l_sys_dt_type;
523 ELSIF(l_count = 0)
524 THEN
525 RETURN 'NOT_USED_AT_ALL';
526 ELSE
527 RETURN 'USED_MORE_THAN_ONE';
528 END IF;
529 END chk_da_alias_used_sda;
530
531 FUNCTION add_offset(p_dai_val IN DATE,
532 l_da_ofst_level_rec IN t_ofst_rec) RETURN DATE
533 /*****************************************************
534 || Created By : Navin Sidana
535 || Created On : 10/13/2004
536 || Purpose : Function to add offsets to a DATE.
537 ||
538 || Known limitations, enhancements or remarks :
539 || Change History :
540 || Who When What
541 || nsidana 10/15/2004 Created
542 *****************************************************/
543 IS
544 l_ret_date DATE := NULL;
545 BEGIN
546 l_ret_date := p_dai_val;
547
548 IF (NVL(l_da_ofst_level_rec.year_offset,0) <> 0)
549 THEN
553 IF (NVL(l_da_ofst_level_rec.month_offset,0) <> 0)
550 l_ret_date := add_months(l_ret_date, (l_da_ofst_level_rec.year_offset * 12));
551 END IF;
552
554 THEN
555 l_ret_date := add_months(l_ret_date, l_da_ofst_level_rec.month_offset);
556 END IF;
557
558 IF (NVL(l_da_ofst_level_rec.week_offset,0) <> 0)
559 THEN
560 l_ret_date := l_ret_date + (l_da_ofst_level_rec.week_offset * 7);
561 END IF;
562
563 IF (NVL(l_da_ofst_level_rec.day_offset,0) <> 0)
564 THEN
565 l_ret_date := l_ret_date + l_da_ofst_level_rec.day_offset;
566 END IF;
567
568 RETURN l_ret_date;
569 END;
570
571 BEGIN /* Main */
572
573 l_prog_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val';
574 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.start';
575
576 l_dt_alias := p_dt_alias;
577 l_da_seq_num := p_da_seq_num;
578 l_cal_type := p_cal_type;
579 l_seq_num := p_seq_number;
580 l_org_unit := p_org_unit;
581 l_prog_type := p_prog_type;
582 l_prog_ver := p_prog_ver;
583 l_app_type := p_app_type;
584 l_dai_val := NULL; -- To be returned by the function call.
585
586 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
587 l_debug_str := 'Starting values : Date Alias ='||l_dt_alias||' DAI seq num ='||l_da_seq_num||' Cal Type ='||l_cal_type||' Seq Num ='||l_seq_num||
588 ' Org Unit ='||l_org_unit||' Prog Type ='||l_prog_type||' Prog Ver ='||l_prog_ver || ' App_Type = '|| l_app_type;
589 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
590 END IF;
591
592 l_da_ofst_level_rec := chk_da_ofst_lvl(l_dt_alias,l_da_seq_num,l_cal_type,l_seq_num);
593
594 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
595 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.offset_level_info';
596 l_debug_str := 'Offset Level ='||l_da_ofst_level_rec.ofst_lvl ||
597 'Offset DA = '||l_da_ofst_level_rec.dt_alias||
598 'Offset DAI Seq num ='||l_da_ofst_level_rec.da_seq_num ||
599 'Offset DA Cal Type ='||l_da_ofst_level_rec.offset_cal_type ||
600 'Offset DA Seq Num ='||l_da_ofst_level_rec.offset_ci_sequence_number ||
601 'Day Offset ='||l_da_ofst_level_rec.day_offset ||
602 'Week Offset ='||l_da_ofst_level_rec.week_offset||
603 'Month Offset ='||l_da_ofst_level_rec.month_offset||
604 'Year Offset ='||l_da_ofst_level_rec.year_offset ||
605 'Offset override flag ='||l_da_ofst_level_rec.ofst_override;
606 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
607 END IF;
608
609 IF (l_da_ofst_level_rec.ofst_lvl IS NULL)
610 THEN
611 -- No offset defined at any level.
612 l_da_used := chk_da_alias_used_sda(l_dt_alias);
613
614 IF (l_da_used = 'NOT_USED_AT_ALL')
615 THEN
616 -- Call old API directly and return the value of the DAI.
617 l_dai_val := IGS_CA_GEN_001.calp_get_alias_val(l_dt_alias,l_da_seq_num,l_cal_type,l_seq_num);
618 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
619 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.DA_not_used_in_SDA';
620 l_debug_str := 'Computing DAI using values : Date Alias ='||l_dt_alias||' DAI Seq num ='||l_da_seq_num||' Cal Type ='||l_cal_type||' Seq num ='||l_seq_num;
621 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
622 END IF;
623 RETURN l_dai_val;
624 ELSIF (l_da_used = 'USED_MORE_THAN_ONE')
625 THEN
626 -- Call old API directly and return the value of the DAI.
627 l_dai_val := IGS_CA_GEN_001.calp_get_alias_val(l_dt_alias,l_da_seq_num,l_cal_type,l_seq_num);
628
629 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
630 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.DA_used_in_more_than_one_SDA';
631 l_debug_str := 'Computing DAI for original DAI : Date Alias ='||l_dt_alias||' DAI Seq num ='||l_da_seq_num||' Cal Type ='||l_cal_type||' Seq num ='||l_seq_num;
632 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
633 END IF;
634
635 RETURN l_dai_val;
636 ELSE
637 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
638 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.DA_used_in_single_SDA';
639 l_debug_str := 'Calling generic function with values : Sys Date Type ='||l_da_used||
640 'Cal Type ='||l_cal_type||' Seq Num ='||l_seq_num||' Org Unit ='||l_org_unit||' Prog Type ='||l_prog_type||' Prog Ver ='||l_prog_ver || ' App_Type = '|| l_app_type;
641 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
642 END IF;
643 l_dai_val := cal_da_elt_val(l_da_used,l_cal_type,l_seq_num,l_org_unit,l_prog_type,l_prog_ver, l_app_type);
644 IF (l_dai_val IS NOT NULL)
645 THEN
646 RETURN l_dai_val;
647 ELSE
648 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
649 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.No_value_returned_by_generic_function ';
650 fnd_log.string_with_context( fnd_log.level_statement,l_label,null, NULL,NULL,NULL,NULL,NULL,null);
651 END IF;
652 RETURN null;
653 END IF;
654 END IF;
655 ELSIF (l_da_ofst_level_rec.ofst_lvl = 'DATE_ALIAS') OR (l_da_ofst_level_rec.ofst_lvl = 'DATE_ALIAS_INST')
656 THEN
657 IF ((l_da_ofst_level_rec.ofst_lvl = 'DATE_ALIAS_INST') AND (l_da_ofst_level_rec.ofst_override = 'N' ))
658 THEN
662 l_debug_str := 'Using : Ofst DA ='||l_da_ofst_level_rec.dt_alias||' Ofst DA seq num ='||l_da_ofst_level_rec.da_seq_num||
659 -- Override flag set to 'N', compute the DAI for the offset DA and add the offset and return the value.
660 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
661 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.ofst_ovrd_uncheck_computing_DAI_value_for_passed_DA';
663 'Ofst Cal Type ='||l_da_ofst_level_rec.offset_cal_type||' Ofst CI seq num ='||l_da_ofst_level_rec.offset_ci_sequence_number;
664 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
665 END IF;
666 l_dai_val := IGS_CA_GEN_001.calp_get_alias_val(l_da_ofst_level_rec.dt_alias,l_da_ofst_level_rec.da_seq_num,l_da_ofst_level_rec.offset_cal_type,l_da_ofst_level_rec.offset_ci_sequence_number);
667 l_dai_val := add_offset(l_dai_val,l_da_ofst_level_rec);
668
669 -- Last step : resolve constraints if any, using IGS_CA_GEN_002.calp_clc_daio_cnstrt.
670
671 l_msg := NULL;
672
673 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
674 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.resolving_constraints';
675 l_debug_str := 'Resolving cnstrt on : Date Alias ='||l_dt_alias||'Date Alias seq num ='||l_da_seq_num||'Cal type ='||l_cal_type||'Seq num ='||l_seq_num||'DAI value ='||l_dai_val;
676 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
677 END IF;
678
679 l_final_val := IGS_CA_GEN_002.calp_clc_daio_cnstrt(l_dt_alias,
680 l_da_seq_num,
681 l_cal_type,
682 l_seq_num,
683 l_dai_val,
684 l_msg);
685 IF (l_msg IS NOT NULL)
686 THEN
687 -- Could not resolve constrints, return the computed date value with the constraints unresolved.
688 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
689 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.constraints_not_resolved_returning_unresolved';
690 l_debug_str := l_msg;
691 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
692 END IF;
693 RETURN l_dai_val;
694 ELSE
695 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
696 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.constraints_resolved_returning_value';
697 fnd_log.string_with_context( fnd_log.level_statement,l_label,null, NULL,NULL,NULL,NULL,NULL,null);
698 END IF;
699 RETURN l_final_val;
700 END IF;
701 END IF;
702
703 -- Offset override is 'Y'. Go for the element level override logic.
704
705 l_da_used := chk_da_alias_used_sda(l_da_ofst_level_rec.dt_alias);
706
707 IF (l_da_used = 'NOT_USED_AT_ALL')
708 THEN
709 -- DA not used in any SDA, return the value for the initial DAI passsed.
710 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
711 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.ovrd_DA_not_used_in_sda';
712 l_debug_str := 'Computing DAI for initial values : Date Alias ='||l_dt_alias||'Seq num ='|| l_da_seq_num || ' Cal Type ='||l_cal_type || ' Seq num ='||l_seq_num;
713 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
714 END IF;
715
716 l_dai_val := IGS_CA_GEN_001.calp_get_alias_val(l_dt_alias,l_da_seq_num,l_cal_type,l_seq_num);
717 RETURN l_dai_val;
718 ELSIF (l_da_used = 'USED_MORE_THAN_ONE')
719 THEN
720 -- DA not used in any SDA, return the value for the initial DAI passsed.
721 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
722 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.ovrd_DA_used_in_more_than_one_SDA';
723 l_debug_str := 'Returning DAI for original values : Date Alias ='||l_dt_alias||'Seq num ='|| l_da_seq_num || ' Cal Type ='||l_cal_type || ' Seq num ='||l_seq_num;
724 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
725 END IF;
726 l_dai_val := IGS_CA_GEN_001.calp_get_alias_val(l_dt_alias,l_da_seq_num,l_cal_type,l_seq_num);
727 RETURN l_dai_val;
728 ELSE
729 -- Call generic to return the date value. If it returns add the offset and return.
730 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
731 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.ovrd_DA_used_in_single_SDA';
732 l_debug_str := 'Calling generic function with values : Date Alias ='||l_da_used|| ' Cal Type ='||l_cal_type||' Seq num ='||l_seq_num|| ' Org Unit ='||l_org_unit||
733 'Prog Type ='||l_prog_type||' Prog Ver ='||l_prog_ver||' App Type ='||l_app_type;
734 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
735 END IF;
736
737 l_dai_val := cal_da_elt_val(l_da_used,l_cal_type,l_seq_num,l_org_unit,l_prog_type,l_prog_ver, l_app_type);
738
739 IF (l_dai_val IS NULL)
740 THEN
741 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
742 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.generic_function_returned_null';
743 l_debug_str := null;
744 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
745 END IF;
746 RETURN null;
747 ELSE
748 l_dai_val := add_offset(l_dai_val,l_da_ofst_level_rec);
749 l_msg := NULL;
750
751 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
752 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.got_DAI_resolving_constraints';
753 l_debug_str := 'Resolving cnstrt on : Date Alias ='||l_dt_alias||'Date Alias seq num ='||l_da_seq_num||'Cal type ='||l_cal_type||'Seq num ='||l_seq_num||'DAI value ='||l_dai_val;
754 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
755 END IF;
756 l_final_val := IGS_CA_GEN_002.calp_clc_daio_cnstrt(l_dt_alias,
757 l_da_seq_num,
758 l_cal_type,
759 l_seq_num,
760 l_dai_val,
761 l_msg);
762 IF (l_msg IS NOT NULL)
763 THEN
764 -- Could not resolve constrints, return the computed date value with the constraints unresolved.
765 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
766 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.constraints_not_resolved_returning_unresolved';
767 l_debug_str := l_msg;
768 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
769 END IF;
770 RETURN l_dai_val;
771 ELSE
772 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
773 l_label := 'igs.plsql.igs_ca_compute_da_val_pkg.cal_da_elt_ofst_val.constraints_resolved';
774 l_debug_str := null;
775 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
776 END IF;
777 RETURN l_final_val;
778 END IF;
779 END IF;
780 END IF;
781 END IF;
782 END cal_da_elt_ofst_val;
783 END IGS_CA_COMPUTE_DA_VAL_PKG;
[Home] [Help]