1 PACKAGE BODY Pji_Rep_Dflt_Drilldown_Txn AS
2 /* $Header: PJIRX13B.pls 120.2 2007/10/24 04:07:10 paljain ship $ */
3
4
5 /*
6 ** This stored procedure derives all the possible parameters
7 ** (ex.: event_type, GL_date, pa_date, org_id, etc)
8 ** that will be added dynamically to the VO SQL statement.
9 ** To do this we will start from the available input parameters:
10 ** input params (always not null) : p_time_id, p_calendar_id, p_calendar_type,
11 ** project_id
12 ** input params (wbs OR rbs OR both): rbs_element_id, wbs_element_id (task_id)
13 **
14 ** History
15 ** 04-FEB-2004 EPASQUIN Created
16 ** 28-APR-2004 EPASQUIN Updated the pkg to use PA_RBS_ELEMENTS table
17 ** instead of PA_PROJ_ELEM_VER_RBS.
18 */
19 PROCEDURE derive_parameters(
20 p_project_id NUMBER
21 ,p_calendar_type VARCHAR2
22 ,p_calendar_id NUMBER
23 ,p_time_id NUMBER
24 ,p_wbs_element_id NUMBER
25 ,p_rbs_element_id NUMBER
26 ,p_commitment_flag VARCHAR2
27 ,p_time_flag VARCHAR2
28 ,x_start_date OUT NOCOPY DATE
29 ,x_end_date OUT NOCOPY DATE
30 ,x_task_id OUT NOCOPY NUMBER
31 ,x_rev_categ_code OUT NOCOPY VARCHAR2
32 ,x_event_type_id OUT NOCOPY NUMBER
33 ,x_event_type OUT NOCOPY VARCHAR2
34 ,x_inventory_item_ids OUT NOCOPY VARCHAR2
35 ,x_org_id OUT NOCOPY NUMBER
36 ,x_expenditure_category_id OUT NOCOPY NUMBER
37 ,x_expenditure_type_id OUT NOCOPY NUMBER
38 ,x_item_category_id OUT NOCOPY NUMBER
39 ,x_job_id OUT NOCOPY NUMBER
40 ,x_person_type_id OUT NOCOPY NUMBER
41 ,x_person_id OUT NOCOPY NUMBER
42 ,x_non_labor_resource_id OUT NOCOPY NUMBER
43 ,x_bom_equipment_resource_id OUT NOCOPY NUMBER
44 ,x_bom_labor_resource_id OUT NOCOPY NUMBER
45 ,x_vendor_id OUT NOCOPY NUMBER
46 ,x_resource_class_id OUT NOCOPY NUMBER
47 ,x_resource_class_code OUT NOCOPY VARCHAR2
48 ,x_person_type OUT NOCOPY VARCHAR2
49 ,x_expenditure_type OUT NOCOPY VARCHAR2
50 ,x_prg_project_id OUT NOCOPY NUMBER
51 ,x_return_status OUT NOCOPY VARCHAR2
52 ,x_msg_count OUT NOCOPY NUMBER
53 ,x_msg_data OUT NOCOPY VARCHAR2
54 ) AS
55
56 inv_item_id_list number_nestedtb;
57 inv_item_id NUMBER;
58 A NUMBER;
59 i NUMBER;
60 l_object_type VARCHAR2(255);
61
62 l_start_date DATE;
63 l_end_date DATE;
64
65 BEGIN
66
67 NULL;
68
69 -- variables initialization
70 l_start_date := NULL;
71 l_end_date := NULL;
72 x_start_date := NULL;
73 x_end_date := NULL;
74 x_task_id := NULL;
75 x_event_type_id := NULL;
76 x_event_type := NULL;
77 x_org_id := NULL;
78 x_expenditure_category_id := NULL;
79 x_expenditure_type_id := NULL;
80 x_expenditure_type := NULL;
81 x_item_category_id := NULL;
82 x_job_id := NULL;
83 x_person_type_id := NULL;
84 x_person_id := NULL;
85 x_non_labor_resource_id := NULL;
86 x_bom_equipment_resource_id := NULL;
87 x_bom_labor_resource_id := NULL;
88 x_vendor_id := NULL;
89 x_rev_categ_code := NULL;
90 x_resource_class_id := NULL;
91 x_inventory_item_ids := '';
92 inv_item_id := NULL;
93 A := NULL;
94 i := NULL;
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96 x_msg_count := 0;
97
98
99
100 BEGIN
101 x_return_status := FND_API.G_RET_STS_SUCCESS; -- Added for bug 3848087
102
103 -- NULL;
104
105
106 /* Bug4161726, 4159404 - The ITD date was retrieving wrongly, Fixing the ITD issue and also pass the correct
107 start and end date to the transaction page
108
109 If commitment Transaction
110 if PTD then all the transaction which are before the end reporting period.
111 if ITD then all commitment transaction should select.
112
113 If Non Commitment Transaction
114 if PTD then all transaction within the the start and end reporting period.
115 if ITD then all the transaction which are before end reporting period. */
116
117
118 x_start_date := TO_DATE('01-01-1950','DD/MM/YYYY');
119 x_end_date := TO_DATE('31-12-2050','DD/MM/YYYY');
120
121
122 /* Program Changes - Get the project id based on the wbs element id */
123
124 SELECT project_id
125 INTO x_prg_project_id
126 FROM pa_proj_elements
127 WHERE proj_element_id = p_wbs_element_id ;
128
129
130 /* The following SQL will get the reporting period which is based on the calandar type and Time Id */
131
132
133 SELECT start_date, end_date
134 INTO l_start_date, l_end_date
135 FROM
136 (
137 SELECT start_date, end_date
138 FROM pji_time_ent_period_v per
139 ,pji_time_rpt_struct rpt
140 WHERE 1=1
141 AND 'E' = p_calendar_type
142 AND per.ent_period_id = rpt.time_id
143 AND rpt.report_date = TO_DATE(p_time_id,'j')
144 AND rpt.record_type_id = 256
145 UNION ALL
146 SELECT start_date, end_date
147 FROM pji_time_cal_period_V per
148 ,pji_time_cal_rpt_struct rpt
149 WHERE 1=1
150 AND per.calendar_id = p_calendar_id
151 AND 'E' <> p_calendar_type
152 AND per.cal_period_id = rpt.time_id
153 AND rpt.report_date = TO_DATE(p_time_id,'j')
154 AND rpt.record_type_id = 256
155 AND rpt.calendar_id = p_calendar_id
156 );
157
158
159 IF (p_commitment_flag = 'N') THEN
160
161 x_end_date := l_end_date; /* Period end date for ITD and PTD */
162
163 IF (p_time_flag = 'PTD') THEN
164
165 x_start_date := l_start_date;
166
167 ELSIF (p_time_flag = 'QTD') THEN
168
169 SELECT start_date, end_date
170 INTO l_start_date, l_end_date
171 FROM
172 (
173 SELECT start_date, end_date
174 FROM pji_time_ent_QTR_v per
175 ,pji_time_rpt_struct rpt
176 WHERE 1=1
177 AND 'E' = p_calendar_type
178 AND per.ent_qtr_id = rpt.time_id
179 AND rpt.report_date = TO_DATE(p_time_id,'j')
180 AND rpt.record_type_id = 512
181 UNION ALL
182 SELECT start_date, end_date
183 FROM pji_time_cal_qtr_V per
184 ,pji_time_cal_rpt_struct rpt
185 WHERE 1=1
186 AND per.calendar_id = p_calendar_id
187 AND 'E' <> p_calendar_type
188 AND per.cal_qtr_id = rpt.time_id
189 AND rpt.report_date = TO_DATE(p_time_id,'j')
190 AND rpt.record_type_id = 512
191 AND rpt.calendar_id = p_calendar_id
192 );
193
194
195 x_start_date := l_start_date;
196
197 ELSIF (p_time_flag = 'YTD') THEN
198
199 SELECT start_date, end_date
200 INTO l_start_date, l_end_date
201 FROM
202 (
203 SELECT start_date, end_date
204 FROM pji_time_ent_year_v per
205 ,pji_time_rpt_struct rpt
206 WHERE 1=1
207 AND 'E' = p_calendar_type
208 AND per.ent_year_id = rpt.time_id
209 AND rpt.report_date = TO_DATE(p_time_id,'j')
210 AND rpt.record_type_id = 128
211 UNION ALL
212 SELECT start_date, end_date
213 FROM pji_time_cal_year_V per
214 ,pji_time_cal_rpt_struct rpt
215 WHERE 1=1
216 AND per.calendar_id = p_calendar_id
217 AND 'E' <> p_calendar_type
218 AND per.cal_year_id = rpt.time_id
219 AND rpt.report_date = TO_DATE(p_time_id,'j')
220 AND rpt.record_type_id = 512
221 AND rpt.calendar_id = p_calendar_id
222 );
223
224 x_start_date := l_start_date;
225
226
227 END IF;
228
229
230 ELSE /* Commitment_flag = 'Y' */
231
232
233 IF (p_time_flag = 'PTD') THEN /* for PTD set the reporting end date for ITD open dates */
234
235 x_end_date := l_end_date;
236
237 END IF;
238
239
240 END IF;
241
242
243
244
245 /* Bug4161726, 4159404, Commmenting the old logic */
246
247
248 /* IF (p_time_id = -1) THEN
249
250 -- if -1 is received, it means it was requested a drilldown for an ITD value;
251 -- in this case we derive start_date, end_date from pa_projects_all
252 SELECT start_date, closed_date --ACTUAL_FINISH_DATE, ACTUAL_START_DATE,
253 INTO x_start_date, x_end_date
254 FROM pa_projects_all
255 WHERE project_id = p_project_id;
256
257 IF x_end_date IS NULL OR x_end_Date > SYSDATE THEN
258 x_end_date := SYSDATE;
259 END IF;
260
261 ELSE
262
263 -- in all other cases, it is requested a drilldown for a PTD value:
264 -- p_time_id actually contains reporting_date_julian
265 -- (prfPeriodDateJulian) from the Prj perf pages.
266 SELECT start_date, end_date
267 INTO x_start_date, x_end_date
268 FROM
269 (
270 SELECT start_date, end_date
271 FROM
272 pji_time_ent_period_v per
273 , pji_time_rpt_struct rpt
274 WHERE 1=1
275 AND 'E' = p_calendar_type
276 AND per.ent_period_id = rpt.time_id
277 AND rpt.report_date = TO_DATE(p_time_id,'j')
278 AND rpt.record_type_id = 256
279 UNION ALL
280 SELECT start_date, end_date
281 FROM
282 pji_time_cal_period_V per
283 , pji_time_cal_rpt_struct rpt
284 WHERE 1=1
285 AND per.calendar_id = p_calendar_id
286 AND 'E' <> p_calendar_type
287 AND per.cal_period_id = rpt.time_id
288 AND rpt.report_date = TO_DATE(p_time_id,'j')
289 AND rpt.record_type_id = 256
290 AND rpt.calendar_id = p_calendar_id
291 );
292
293 END IF; */
294
295
296
297
298 EXCEPTION
299 WHEN NO_DATA_FOUND THEN
300 x_return_status := FND_API.G_RET_STS_ERROR; -- Added for bug 3848087
301 x_msg_count := x_msg_count + 1;
302 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.derive_parameters');
303 WHEN TOO_MANY_ROWS THEN
304 x_return_status := FND_API.G_RET_STS_ERROR; -- Added for bug 3848087
305 x_msg_count := x_msg_count + 1;
306 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.derive_parameters');
307
308 END;
309
310 BEGIN
311 SELECT object_type
312 INTO l_object_type
313 FROM pa_proj_elements
314 WHERE proj_element_id = p_wbs_element_id;
315 EXCEPTION WHEN OTHERS THEN
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; -- Added for bug 3848087
317 x_msg_count := x_msg_count + 1;
318 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG', p_msg_type=>FND_API.G_RET_STS_UNEXP_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.derive_parameters');
319 END;
320
321 IF l_object_type = 'PA_STRUCTURES' THEN
322 -- in this case the selected proj_element_id is a structure
323 x_task_id := NULL;
324 ELSIF l_object_type = 'PA_TASKS' THEN
325 -- in this case the selected proj_element_id is a task
326 x_task_id := p_wbs_element_id;
327 END IF;
328
329 IF (p_rbs_element_id IS NOT NULL) THEN
330 BEGIN
331
332 BEGIN
333
334 SELECT
335 rbs.event_type_id
336 ,et.event_type
337 ,rbs.organization_id
338 ,rbs.inventory_item_id
339 ,rbs.expenditure_category_id
340 ,rbs.expenditure_type_id
341 ,expt.expenditure_type
342 ,rbs.item_category_id
343 ,rbs.job_id
344 ,rbs.person_type_id
345 ,rbs.person_id
346 ,rbs.non_labor_resource_id
347 ,rbs.bom_equipment_id
348 ,rbs.bom_labor_id
349 ,rbs.supplier_id
350 ,rbs.resource_class_id
351 ,rc.resource_class_code
352 --,rbsn.resourcetype
353 ,et.revenue_category_code
354 INTO
355 x_event_type_id
356 ,x_event_type
357 ,x_org_id
358 ,inv_item_id
359 ,x_expenditure_category_id
360 ,x_expenditure_type_id
361 ,x_expenditure_type
362 ,x_item_category_id
363 ,x_job_id
364 ,x_person_type_id
365 ,x_person_id
366 ,x_non_labor_resource_id
367 ,x_bom_equipment_resource_id
368 ,x_bom_labor_resource_id
369 ,x_vendor_id
370 ,x_resource_class_id
371 ,x_resource_class_code
372 --,x_resourcetype
373 ,x_rev_categ_code
374 FROM
375 PA_RBS_ELEMENTS rbs
376 ,pa_event_types et
377 ,pa_resource_classes_b rc
378 ,pa_expenditure_types expt
379 --,PA_RBS_ELEMENT_NAMES_B rbsn
380 WHERE 1=1
381 AND rbs.rbs_element_id = p_rbs_element_id
382 AND rbs.resource_class_id = rc.resource_class_id (+) -- Added outer joing for bug 3848087
383 -- we want to obtain the rbs records regardless of event_type_id or expenditure_type_id
384 AND rbs.event_type_id = et.event_type_id (+)
385 AND rbs.expenditure_type_id = expt.expenditure_type_id (+)
386 --AND rbs.element_version_id = rbsn.rbs_element_id
387 ;
388
389 EXCEPTION
390 WHEN NO_DATA_FOUND THEN
391 x_return_status := FND_API.G_RET_STS_ERROR; -- Added for bug 3848087
392 x_msg_count := x_msg_count + 1;
393 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.derive_parameters');
394
395 WHEN OTHERS THEN
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; -- Added for bug 3848087
397 x_msg_count := x_msg_count + 1;
398 Pji_Rep_Util.add_message(p_app_short_name=>'PJI',
399 p_msg_name=>'PJI_REP_GENERIC_MSG',
400 p_msg_type=>FND_API.G_RET_STS_UNEXP_ERROR,
401 p_token1=>'PROC_NAME',
402 p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.derive_parameters');
403
404 END;
405
406 BEGIN
407
408 SELECT system_person_type
409 INTO x_person_type
410 FROM per_person_types
411 WHERE person_type_id = x_person_type_id;
412
413 EXCEPTION
414 WHEN NO_DATA_FOUND THEN
415 x_return_status := FND_API.G_RET_STS_ERROR; -- Added for bug 3848087
416 x_msg_count := x_msg_count + 1;
417 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.derive_parameters');
418
419 WHEN OTHERS THEN
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; -- Added for bug 3848087
421 x_msg_count := x_msg_count + 1;
422 Pji_Rep_Util.Add_Message(p_app_short_name=>'PJI',
423 p_msg_name=>'PJI_REP_GENERIC_MSG',
424 p_msg_type=>Pji_Rep_Util.G_RET_STS_WARNING,
425 p_token1=>'PROC_NAME',
426 p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.derive_parameters');
427
428 END;
429
430
431 IF inv_item_id IS NOT NULL THEN
432 x_inventory_item_ids := TO_CHAR(inv_item_id);
433 ELSE
434 --collect all inv_item_id that match the retrieved item_category_id
435 SELECT cat.inventory_item_id
436 BULK COLLECT INTO inv_item_id_list
437 FROM
438 pa_resource_classes_b cls
439 , pa_plan_res_defaults def
440 , mtl_item_categories cat
441 WHERE 1=1
442 AND cls.resource_class_id = def.object_id
443 AND cls.resource_class_code = 'MATERIAL_ITEMS'
444 AND def.object_type = 'CLASS'
445 AND cat.organization_id = def.item_master_id
446 AND cat.category_set_id = def.item_category_set_id
447 AND category_id = x_item_category_id
448 ;
449
450 -- copy the nested table in a comma separated list of values string
451 IF inv_item_id_list.COUNT > 0 THEN
452 -- take care of 1st element so don't need to worry about the last comma
453 x_inventory_item_ids := TO_CHAR(inv_item_id_list(inv_item_id_list.FIRST));
454 A := inv_item_id_list.NEXT(inv_item_id_list.FIRST);
455
456 -- copy the remaining elements
457 FOR i IN A..inv_item_id_list.LAST LOOP
458 IF inv_item_id_list(i) IS NOT NULL THEN
459 x_inventory_item_ids := x_inventory_item_ids || ', ' ||
460 TO_CHAR(inv_item_id_list(i));
461 END IF;
462 END LOOP;
463
464 END IF;
465
466 END IF;
467 --dbms_output.put_line('x_inventory_item_ids='||x_inventory_item_ids);
468
469 EXCEPTION
470 WHEN NO_DATA_FOUND THEN
471 x_return_status := FND_API.G_RET_STS_ERROR; -- Added for bug 3848087
472 x_msg_count := x_msg_count + 1;
473 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.derive_parameters');
474 NULL;
475 WHEN TOO_MANY_ROWS THEN
476 x_return_status := FND_API.G_RET_STS_ERROR; -- Added for bug 3848087
477 x_msg_count := x_msg_count + 1;
478 pji_rep_util.add_message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.derive_parameters');
479 NULL;
480 WHEN OTHERS THEN
481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; -- Added for bug 3848087
482 x_msg_count := x_msg_count + 1;
483 Pji_Rep_Util.Add_Message(p_app_short_name=>'PJI',
484 p_msg_name=>'PJI_REP_GENERIC_MSG',
485 p_msg_type=>FND_API.G_RET_STS_UNEXP_ERROR,
486 p_token1=>'PROC_NAME',
487 p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.derive_parameters');
488
489 END;
490
491 END IF;
492
493 COMMIT;
494
495 END derive_parameters;
496
497
498 /*
499 **
500 ** History
501 ** 13-JUL-2004 EPASQUIN Created
502 */
503 PROCEDURE determine_events_costs_display(
504 p_wbs_element_id NUMBER
505 ,x_task_id OUT NOCOPY NUMBER
506 ,x_show_costs_flag OUT NOCOPY VARCHAR2
507 ,x_show_events_flag OUT NOCOPY VARCHAR2
508 ,x_return_status OUT NOCOPY VARCHAR2
509 ,x_msg_count OUT NOCOPY NUMBER
510 ,x_msg_data OUT NOCOPY VARCHAR2
511 ) AS
512
513 l_object_type VARCHAR2(255);
514 l_chargeable_flag VARCHAR2(255);
515 l_top_task_id VARCHAR2(255);
516
517 BEGIN
518
519 x_show_events_flag := 'N';
520 x_show_costs_flag := 'Y';
521 x_return_status := FND_API.G_RET_STS_SUCCESS;
522 x_msg_count := 0;
523
524 BEGIN
525 SELECT object_type
526 INTO l_object_type
527 FROM pa_proj_elements
528 WHERE proj_element_id = p_wbs_element_id;
529 EXCEPTION WHEN OTHERS THEN
530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
531 x_msg_count := x_msg_count + 1;
532 Pji_Rep_Util.Add_Message(p_app_short_name=>'PJI',
533 p_msg_name=>'PJI_REP_GENERIC_MSG',
534 p_msg_type=>FND_API.G_RET_STS_UNEXP_ERROR,
535 p_token1=>'PROC_NAME',
536 p_token1_value=>'PJI_REP_DFLT_DRILLDOWN_TXN.determine_events_costs_display');
537 END;
538
539 IF l_object_type = 'PA_STRUCTURES' THEN
540
541 -- in this case the selected proj_element_id is a structure
542 x_task_id := NULL;
543 x_show_events_flag := 'Y';
544
545 ELSIF l_object_type = 'PA_TASKS' THEN
546
547 -- in this case the selected proj_element_id is a task
548 x_task_id := p_wbs_element_id;
549
550 BEGIN
551 SELECT chargeable_flag, top_task_id
552 INTO l_chargeable_flag, l_top_task_id
553 FROM pa_tasks
554 WHERE task_id = p_wbs_element_id;
555 EXCEPTION WHEN OTHERS THEN
556 l_top_task_id := NULL;
557 l_chargeable_flag := NULL;
558 END;
559
560 IF l_top_task_id = p_wbs_element_id THEN
561 x_show_events_flag := 'Y';
562 END IF;
563
564 IF l_chargeable_flag = 'Y' THEN
565 x_show_costs_flag := 'Y';
566 END IF;
567
568 END IF;
569
570 END determine_events_costs_display;
571
572
573 END Pji_Rep_Dflt_Drilldown_Txn;
[Home] [Help]