1 package body psp_efforts_pkg AS
2 /* $Header: PSPEREFB.pls 120.2.12010000.2 2008/08/05 10:11:27 ubhat ship $ */
3 /***********************************************************************************
4 ** NAME: psp_efforts_pkg
5 ** CONTENTS: Package Body for psp_efforts_pkg
6 ** PURPOSE: This Package Body contains two procedures .
7 ** 1. PROCEDURE crt
8 ** This procedure takes in as input the various input criteria
9 ** entered by the user during Effort Report Creation. The input
10 ** criteria are then matched with the actual disbursements of labor
11 ** charges from the Distribution Lines and the actual efforts based
12 ** on a person , against all his assignments, for a period range, is
13 ** summarized and added to Effort Report Tables. The procedure
14 ** also does lot of bookkeeping like updating Distribution Lines,
15 ** retrieving GL and POETA values from appropriate tables, checking
16 ** active element types and so on. This procedure also populates an
17 ** Error Table for Effort Report Already processed. This procedure
18 ** is run by Concurrent Manager.
19 **
20 ** 2. PROCEDURE INIT_WORKFLOW
21 ** This procedure is to initiate workflow process. This procedure fetches
22 ** all Report Ids for a given template id and Initiates workflow process
23 ** for each report id
24 **
25 ** NOTES: Workflow : Added by Venkat.
26 ** Enhanced Workflow: Added by Shu Lei.
27 ** AUTHOR: Abhijit Prasad
28 **
29 ** History
30 ** Ravindra Cheruvu 26-JUL-1999 Included the auto-pop details : gl_ccid and expenditure_type.
31 ** Ravindra Cheruvu 06-Aug-1999 Changed the code for cursor t_warning_report_exists to
32 ** handle a rejected ER.
33 ** Now it checks for status 'R' also and allows to create
34 ** a new ER for the person.
35 ** Dinesh Dubey 12-Jul-2001 Perf. Fixes for bug 1874615
36 ** Subha Ramachandran 23-AUG-2001 Functional Fixes for Bug 1952627
37 ** Ritesh Kumar 12-SEP-2001 Bug fix:1988747. Added additional conditions to t_all_PERSON_id
38 ** cursor to take care of gl, project, award and exp org submission
39 ** conditions when creating an effort report.
40 ** Subha Ramachandran 10-Jul-02 Bug fix 2307100
41 ** Dinesh Dubey 30-Dec-2002 Modified for bug 2624259.
42 ** Tulasi Krishna 17-Apr-2003 Modified for Bug fix 2892637.
43 ** spchakra 02-Sep-2003 Bug 3098050: Introduced BG/SOB columns.
44 ** spchakra 14-Nov-2003 Bug 3063762: Commented out POTEA/GL check on distributions
45 ** as its for filtering persons only.
46 ** vdharmap 11-Apr-2006 Bug 5080403: autopop for suspense.
47 ** amakrish 29-Apr-2008 Bug 7004679 Added wf_engine.setitemowner
48 ************************************************************************************
49 ***********************************************************************************/
50 FUNCTION p_template_exists(a_template_id IN NUMBER)
51 --- Function To Check whether Template Exists.
52 RETURN NUMBER IS
53 BEGIN
54 SELECT *
55 INTO g_template_row
56 FROM psp_effort_report_templates
57 WHERE template_id = a_template_id;
58 RETURN(0);
59 EXCEPTION
60 WHEN OTHERS THEN
61 RETURN(-1);
62 END;
63 ---
64 FUNCTION p_project_exists(a_template_id IN NUMBER,t_project_id IN NUMBER)
65 RETURN NUMBER IS
66 t_dummy CHAR(1);
67 BEGIN
68 BEGIN
69 SELECT 'x'
70 INTO t_dummy
71 FROM psp_template_projects
72 WHERE template_id = a_template_id AND
73 ROWNUM = 1;
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76 RETURN(0);
77 END;
78 ---
79 SELECT 'x'
80 INTO t_dummy
81 FROM psp_template_projects
82 WHERE template_id = a_template_id AND
83 project_id = t_project_id;
84 RETURN(1);
85 EXCEPTION
86 WHEN OTHERS THEN
87 RETURN(2);
88 END;
89 ---
90 FUNCTION p_award_exists(a_template_id IN NUMBER, t_award_id IN NUMBER)
91 RETURN NUMBER IS
92 t_dummy CHAR(1);
93 BEGIN
94 BEGIN
95 SELECT 'x'
96 INTO t_dummy
97 FROM psp_template_awards
98 WHERE template_id = a_template_id AND
99 ROWNUM = 1;
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 RETURN(0);
103 END;
104 ---
105 SELECT 'x'
106 INTO t_dummy
107 FROM psp_template_awards
108 WHERE template_id = a_template_id AND
109 award_id = t_award_id;
110 RETURN(1);
111 EXCEPTION
112 WHEN OTHERS THEN
113 RETURN(2);
114 END;
115 ---
116 FUNCTION p_org_exists(a_template_id IN NUMBER, t_expenditure_organization_id IN NUMBER)
117 RETURN NUMBER IS
118 t_dummy CHAR(1);
119 BEGIN
120 BEGIN
121 SELECT 'x'
122 INTO t_dummy
123 FROM psp_template_organizations
124 WHERE template_id = a_template_id AND
125 ROWNUM = 1;
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 RETURN(0);
129 END;
130 ---
131 SELECT 'x'
132 INTO t_dummy
133 FROM psp_template_organizations
134 WHERE template_id = a_template_id AND
135 expenditure_organization_id = t_expenditure_organization_id;
136 RETURN(1);
137 EXCEPTION
138 WHEN OTHERS THEN
139 RETURN(2);
140 END;
141 ---
142 FUNCTION p_get_POETA(template_id1 IN NUMBER,person_id1 IN NUMBER, ssdseo IN g_ssdseo%ROWTYPE)
143 RETURN g_gl_poeta%ROWTYPE IS
144 t_poeta g_gl_poeta%ROWTYPE;
145 BEGIN
146 BEGIN
147 SELECT gl_code_combination_id,
148 project_id,
149 expenditure_organization_id,
150 expenditure_type,
151 task_id,
152 award_id
153 INTO t_poeta
154 FROM psp_organization_accounts
155 WHERE organization_account_id = NVL(ssdseo.suspense_org_account_id,-955);
156 RETURN(t_poeta);
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 NULL;
160 END;
161 ---
162 IF ssdseo.suspense_org_account_id IS NOT NULL THEN
163 -- dbms_output.put_line('Account Reference For Suspense_org_account_id = ' || ssdseo.suspense_org_account_id || ' Not Found');
164 log_errors(template_id1,person_id1, 'Account Reference For Suspense_org_account_id = ' ||
165 to_char(ssdseo.suspense_org_account_id )|| ' Not Found.',NULL,NULL);
166 END IF;
167 ---
168 BEGIN
169 SELECT gl_code_combination_id,
170 project_id,
171 expenditure_organization_id,
172 expenditure_type,
173 task_id,
174 award_id
175 INTO t_poeta
176 FROM psp_organization_accounts
177 WHERE organization_account_id = NVL(ssdseo.default_org_account_id,-955);
178 RETURN(t_poeta);
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 NULL;
182 END;
183 ---
184 BEGIN
185 SELECT gl_code_combination_id,
186 project_id,
187 expenditure_organization_id,
188 expenditure_type,
189 task_id,
190 award_id
191 INTO t_poeta
192 FROM psp_schedule_lines
193 WHERE schedule_line_id = NVL(ssdseo.schedule_line_id,-955);
194 RETURN(t_poeta);
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 NULL;
198 END;
199 ---
200 BEGIN
201 SELECT gl_code_combination_id,
202 project_id,
203 expenditure_organization_id,
204 expenditure_type,
205 task_id,
206 award_id
207 INTO t_poeta
208 FROM psp_element_type_accounts
209 WHERE element_account_id = NVL(ssdseo.element_account_id,-955);
210 RETURN(t_poeta);
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 NULL;
214 END;
215 ---
216 BEGIN
217 SELECT gl_code_combination_id,
218 project_id,
219 expenditure_organization_id,
220 expenditure_type,
221 task_id,
222 award_id
223 INTO t_poeta
224 FROM psp_default_labor_schedules
225 WHERE org_schedule_id = NVL(ssdseo.org_schedule_id,-955);
226 RETURN(t_poeta);
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 RETURN(t_poeta);
230 END;
231 ---
232 EXCEPTION
233 WHEN OTHERS THEN
234 --dbms_output.put_line('POETA retrieval failed');
235 FND_MESSAGE.set_name('PSP','PSP_EFFORTS_PKG.CRT_POETA_FAIL');
236 log_errors(template_id1,person_id1,'GL_POETA Selection Criteria failed' ,NULL,NULL);
237 ---RAISE FND_API.g_exc_unexpected_error;
238 END;
239 -----
240 FUNCTION p_element_exists(element_type_id1 IN NUMBER,dist_dt1 IN DATE,
241 p_business_group_id IN NUMBER, -- Introduced for bug fix 3098050
242 p_set_of_books_id IN NUMBER) -- Introduced for bug fix 3098050
243 RETURN NUMBER IS
244 t_dummy CHAR(1);
245 BEGIN
246 BEGIN
247 SELECT 'x'
248 INTO t_dummy
249 FROM psp_effort_report_elements
250 WHERE element_type_id = element_type_id1
251 -- Introduced BG/SOB check for bug fix 3098050
252 AND business_group_id = p_business_group_id
253 AND set_of_books_id = p_set_of_books_id
254 AND NVL(use_in_effort_report,'N') = 'Y'
255 AND ROWNUM = 1;
256 g_element_flag := 'Y';
257 RETURN(0);
258 EXCEPTION
259 WHEN OTHERS THEN
260 RETURN(-6);
261 END;
262 END;
263 ---
264 FUNCTION p_effort_details_exists(effort_report_id1 IN NUMBER, version_number1 IN NUMBER)
265 RETURN NUMBER IS
266 t_dummy CHAR(1);
267 BEGIN
268 BEGIN
269 SELECT 'x'
270 INTO t_dummy
271 FROM psp_effort_report_details
272 WHERE effort_report_id = effort_report_id1 AND
273 version_num = version_number1 AND
274 ROWNUM = 1;
275 RETURN(0);
276 EXCEPTION
277 WHEN OTHERS THEN
278 RETURN(-7);
279 END;
280 END;
281 ---
282 PROCEDURE p_old_adhoc_delete(template_id1 IN NUMBER, person_id1 IN NUMBER,
283 begin_date1 IN DATE, end_date1 IN DATE)
284 --- The following private procedure checks to see whether an earlier Adhoc report(s) exists
285 --- for a given Adhoc report, with an exact match of Person_id, Start date And End
286 --- Date. If, yes, it deletes all record(s) from all concerned tables.
287 IS
288 CURSOR t_old_templates(template_id2 IN NUMBER, person_id2 IN NUMBER,
289 begin_date2 IN DATE, end_date2 IN DATE) IS
290 SELECT distinct E.template_id
291 FROM PSP_EFFORT_REPORTS E,PSP_EFFORT_REPORT_TEMPLATES T
292 WHERE E.person_id = person_id2 AND
293 E.template_id = T.template_id AND
294 begin_date2 = T.begin_date AND
295 end_date2 = T.end_date AND
296 T.report_type = 'A'
297 MINUS
298 SELECT template_id2
299 FROM DUAL;
300 t_old_template_id NUMBER(15);
301 BEGIN
302 OPEN t_old_templates(template_id1,person_id1,begin_date1,end_date1);
303 LOOP
304 FETCH t_old_templates INTO t_old_template_id;
305 EXIT WHEN t_old_templates%NOTFOUND;
306 BEGIN
307 PSP_TEMPLATE_AWARDS_PKG.delete_row(t_old_template_id);
308 IF (SQL%NOTFOUND) THEN
309 NULL;
310 END IF;
311 PSP_TEMPLATE_PROJECTS_PKG.delete_row(t_old_template_id);
312 IF (SQL%NOTFOUND) THEN
313 NULL;
314 END IF;
315 PSP_TEMPLATE_ORGANIZATIONS_PKG.delete_row(t_old_template_id);
316 IF (SQL%NOTFOUND) THEN
317 NULL;
318 END IF;
319 PSP_EFT_REPORT_TEMPLATES_PKG.delete_row(t_old_template_id);
320 IF (SQL%NOTFOUND) THEN
321 NULL;
322 END IF;
323 DELETE FROM PSP_EFFORT_REPORT_DETAILS D
324 WHERE exists ( SELECT 'x'
325 FROM PSP_EFFORT_REPORTS R
326 WHERE R.effort_report_id = D.effort_report_id AND
327 R.version_num = D.version_num AND
328 template_id = t_old_template_id );
329 IF (SQL%NOTFOUND) THEN
330 NULL;
331 END IF;
332 DELETE FROM PSP_EFFORT_REPORTS
333 WHERE template_id = t_old_template_id;
334 IF (SQL%NOTFOUND) THEN
335 NULL;
336 END IF;
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 NULL;
340 END;
341 END LOOP;
342 CLOSE t_old_templates;
343 END;
344 ---
345 ---
346 FUNCTION p_sync_details(effort_report_id1 IN NUMBER,version_num1 IN NUMBER,
347 assignment_id1 IN NUMBER,element_type_id1 IN NUMBER,
348 gl_poeta1 IN g_gl_poeta%ROWTYPE, total_amount1 IN NUMBER)
349 --- The following private function checks to see if combination of person, date range,
350 --- Assignment, element type, GL, POETA already exists, for a particular version. If yes,
351 --- the function adds distribution amount already fetched from Distribution lines to
352 --- the TOTAL_AMOUNT column of PSP_EFFORT_REPORT_DETAILS, instead of creating a new row.
353 RETURN NUMBER IS
354 BEGIN
355 BEGIN
356 SELECT *
357 INTO g_details_row
358 FROM psp_effort_report_details
359 WHERE effort_report_id = effort_report_id1 AND
360 version_num = version_num1 AND
361 assignment_id = assignment_id1 AND
362 element_type_id = element_type_id1 AND
363 NVL(gl_code_combination_id,-99) = NVL(gl_poeta1.gl_code_combination_id,-99) AND
364 NVL(project_id,-99) = NVL(gl_poeta1.project_id,-99) AND
365 NVL(expenditure_organization_id,-99) = NVL(gl_poeta1.expenditure_organization_id,-99) AND
366 NVL(expenditure_type,'-99') = NVL(gl_poeta1.expenditure_type,'-99') AND
367 NVL(task_id,-99) = NVL(gl_poeta1.task_id,-99) AND
368 NVL(award_id,-99) = NVL(gl_poeta1.award_id,-99) AND
369 ROWNUM = 1;
370 ---
371 g_details_row.TOTAL_AMOUNT := g_details_row.TOTAL_AMOUNT + total_amount1;
372 ---
373 UPDATE PSP_EFFORT_REPORT_DETAILS
374 SET total_amount = g_details_row.TOTAL_AMOUNT
375 WHERE effort_report_id = g_details_row.effort_report_id AND
376 version_num = g_details_row.version_num AND
377 effort_report_line_num = g_details_row.effort_report_line_num;
378 IF (SQL%NOTFOUND) THEN
379 NULL;
380 END IF;
381 ---
382 RETURN(-1);
383 EXCEPTION WHEN NO_DATA_FOUND THEN
384 RETURN(0);
385 END;
386 END;
387 ---
388 PROCEDURE log_errors(template_id1 IN NUMBER,person_id1 IN NUMBER,errmsg VARCHAR2,
389 effort_report_id1 IN NUMBER, version_num1 IN NUMBER)
390 --- Error Logging Procedure
391 IS
392 BEGIN
393 INSERT INTO PSP_EFFORT_ERRORS(template_id,person_id,message,prev_effort_report_id,
397 UPDATE PSP_EFFORT_REPORT_TEMPLATES
394 prev_version_num) values (template_id1,person_id1,errmsg,effort_report_id1,version_num1);
395 -- enumerated the INSERT list for bug fix 2307100
396 ---
398 SET error_date_time = SYSDATE
399 WHERE template_id = template_id1;
400 ---
401 IF (SQL%NOTFOUND) THEN
402 NULL;
403 END IF;
404 ---
405 END;
406 ---
407 ---
408 --- MAIN FUNCTION
409 --- The following concept is included.
410 --- Report Hdr Report Dtls Effort Errors
411 --- ADHOC N 1 M 0
412 --- ADHOC W 1 M 1
413 --- NORMAL N 1 M 0
414 --- NORMAL W 0 0 1
415 ---
416 ---
417 PROCEDURE crt(errbuf OUT NOCOPY VARCHAR2,
418 retcode OUT NOCOPY NUMBER,
419 a_template_id IN NUMBER,
420 p_business_group_id IN VARCHAR2,
421 p_set_of_books_id IN VARCHAR2)
422 IS
423
424 CURSOR t_DIST (person_id1 NUMBER, sdate DATE, edate DATE) IS
425 /* This cursor is based on PST_DISTRIBUTION_LINES_HISTORY ,PSP_PRE_GEN_DIST_LINES_HISTORY and
426 PSP_ADJUSTMENT_LINES_HISTORY */
427 /*commented out and replaced by new cursor for perf. fix bug 1874615 -- ddubey 12-JUl-2001.
428 SELECT D.distribution_line_id, --- N(10)
429 L.person_id person_id, --- N(9)
430 L.assignment_id assignment_id, --- N(9)
431 L.element_type_id, --- N(9)
432 D.distribution_date, --- Date
433 NVL(D.distribution_amount,0) distribution_amount, --- N(22)
434 D.effort_report_id, --- N(9)
435 D.status_code, --- N(9)
436 ---D.version_num, --- N(9)
437 NVL(AUTO_GL_CODE_COMBINATION_ID, TO_NUMBER(NULL)) gl_code_combination_id, -- Ravindra
438 TO_NUMBER(NULL) project_id,
439 TO_NUMBER(NULL) expenditure_organization_id,
440 NVL(AUTO_EXPENDITURE_TYPE, NULL) expenditure_type, -- Ravindra
441 TO_NUMBER(NULL) task_id,
442 TO_NUMBER(NULL) award_id,
443 D.schedule_line_id, --- N(9)
444 D.summary_line_id, --- N(10)
445 D.default_org_account_id, --- N(9)
446 D.suspense_org_account_id, --- N(9)
447 element_account_id, --- N(9)
448 org_schedule_id, --- N(9)
449 'distribution_lines' source
450 FROM psp_distribution_lines_history D,psp_payroll_sub_lines S, psp_payroll_lines L
451 WHERE D.payroll_sub_line_id = S.payroll_sub_line_id AND
452 S.payroll_line_id = L.payroll_line_id AND
453 D.distribution_DATE between sdate AND edate AND
454 L.person_id = person_id1 AND
455 NVL(include_in_er_flag,'Y') = 'Y' AND
456 NVL(reversal_entry_flag,'N') = 'N'
457 UNION
458 SELECT pre_gen_dist_line_id,
459 person_id, --- N(9)
460 assignment_id, --- N(9)
461 element_type_id,
462 distribution_date,
463 NVL(distribution_amount,0), --- N(22)
464 effort_report_id,
465 status_code,
466 gl_code_combination_id,
467 project_id,
468 expenditure_organization_id,
469 expenditure_type,
470 task_id,
471 award_id,
472 TO_NUMBER(NULL), --- N(9)
473 TO_NUMBER(NULL), --- N(10)
474 TO_NUMBER(NULL), --- N(9)
475 TO_NUMBER(NULL), --- N(9)
476 TO_NUMBER(NULL), --- N(9)
477 TO_NUMBER(NULL), --- N(9)
478 'pre_gen_dist_lines'
479 FROM psp_pre_gen_dist_lines_history
480 WHERE distribution_date between sdate AND edate AND
481 person_id = person_id1 AND
482 NVL(include_in_er_flag,'Y') = 'Y' AND
483 NVL(reversal_entry_flag,'N') = 'N'
484 UNION
485 SELECT adjustment_line_id,
486 person_id, --- N(9)
487 assignment_id, --- N(9)
488 element_type_id,
489 distribution_date,
490 NVL(distribution_amount,0), --- N(22)
491 effort_report_id,
492 status_code,
493 gl_code_combination_id,
494 project_id,
495 expenditure_organization_id,
496 expenditure_type,
497 task_id,
498 award_id,
499 TO_NUMBER(NULL), --- N(9)
500 TO_NUMBER(NULL), --- N(10)
501 TO_NUMBER(NULL), --- N(9)
502 TO_NUMBER(NULL), --- N(9)
503 TO_NUMBER(NULL), --- N(9)
504 TO_NUMBER(NULL), --- N(9)
505 'adjustment_lines'
506 FROM psp_adjustment_lines_history
510 NVL(reversal_entry_flag,'N') = 'N'
507 WHERE distribution_date between sdate AND edate AND
508 person_id = person_id1 AND
509 NVL(include_in_er_flag,'Y') = 'Y' AND
511 ---ORDER BY person_id,assignment_id; Rel 11 Requirement
512 ORDER BY 2,3; */
513 SELECT pdlh.distribution_line_id,
514 psl.person_id,
515 psl.assignment_id,
516 ppl.element_type_id,
517 pdlh.distribution_date,
518 decode(ppl.dr_cr_flag, 'C', NVL(-pdlh.distribution_amount,0),
519 nvl(pdlh.distribution_amount,0))distribution_amount, -- bug fix 1952627
520 pdlh.effort_report_id,
521 pdlh.status_code,
522 psl.gl_code_combination_id, ---NVL(AUTO_GL_CODE_COMBINATION_ID, TO_NUMBER(NULL)) gl_code_combination_id,
523 psl.project_id, ---TO_NUMBER(NULL) project_id, commented for 5080403
524 psl.expenditure_organization_id, ----TO_NUMBER(NULL) expenditure_organization_id,
525 psl.expenditure_type, ----NVL(AUTO_EXPENDITURE_TYPE, NULL) expenditure_type,
526 psl.task_id, ---TO_NUMBER(NULL) task_id,
527 psl.award_id, ---TO_NUMBER(NULL) award_id,
528 pdlh.schedule_line_id,
529 pdlh.summary_line_id,
530 pdlh.default_org_account_id,
531 pdlh.suspense_org_account_id,
532 element_account_id,
533 org_schedule_id,
534 'distribution_lines' source
535 FROM psp_distribution_lines_history pdlh,
536 psp_payroll_sub_lines pps,
537 psp_payroll_lines ppl,
538 psp_summary_lines psl
539 WHERE
540 psl.person_id = person_id1 AND
541 pdlh.summary_line_id = psl.summary_line_id AND
542 psl.status_code||''='A' and
543 pdlh.distribution_DATE between sdate AND edate
544 and NVL(pdlh.include_in_er_flag,'Y') = 'Y' AND
545 NVL(pdlh.reversal_entry_flag,'N') = 'N' AND
546 pdlh.adjustment_batch_name is null
547 and pdlh.payroll_sub_line_id= pps.payroll_sub_line_id
548 and pps.payroll_line_id=ppl.payroll_line_id
549 UNION
550 SELECT ppgh.pre_gen_dist_line_id,
551 ppgh.person_id,
552 ppgh.assignment_id,
553 ppgh.element_type_id,
554 ppgh.distribution_date,
555 decode(ppgh.dr_cr_flag,'C', NVL(-ppgh.distribution_amount,0),
556 nvl(ppgh.distribution_amount,0))distribution_amount,-- bug fix 1952627
557 ppgh.effort_report_id,
558 ppgh.status_code,
559 psl.gl_code_combination_id, --- changed from ppgh prefix to
560 ---psl.. for this line and below 4 lines... for 5080403
561 psl.project_id,
562 psl.expenditure_organization_id,
563 psl.expenditure_type,
564 psl.task_id,
565 psl.award_id,
566 TO_NUMBER(NULL),
567 TO_NUMBER(NULL),
568 TO_NUMBER(NULL),
569 TO_NUMBER(NULL),
570 TO_NUMBER(NULL),
571 TO_NUMBER(NULL),
572 'pre_gen_dist_lines'
573 FROM psp_pre_gen_dist_lines_history ppgh,
574 psp_summary_lines psl
575 WHERE
576 psl.person_id=person_id1 and
577 ppgh.summary_line_id=psl.summary_line_id
578 and psl.status_code||''='A' and
579 ppgh.distribution_date between sdate AND edate AND
580 NVL(ppgh.include_in_er_flag,'Y') = 'Y' AND
581 NVL(ppgh.reversal_entry_flag,'N') = 'N' AND
582 ppgh.adjustment_batch_name is null
583 UNION
584 SELECT palh.adjustment_line_id,
585 palh.person_id,
586 palh.assignment_id,
587 palh.element_type_id,
588 palh.distribution_date,
589 decode(palh.dr_cr_flag, 'C',NVL(-palh.distribution_amount,0),
590 NVL(palh.distribution_amount, 0))distribution_amount, -- bug fix 1952627
591 palh.effort_report_id,
592 palh.status_code,
593 palh.gl_code_combination_id,
594 palh.project_id,
595 palh.expenditure_organization_id,
596 palh.expenditure_type,
597 palh.task_id,
598 palh.award_id,
599 TO_NUMBER(NULL),
600 TO_NUMBER(NULL),
601 TO_NUMBER(NULL),
602 TO_NUMBER(NULL),
603 TO_NUMBER(NULL),
604 TO_NUMBER(NULL),
605 'adjustment_lines'
606 FROM psp_adjustment_lines_history palh,
607 psp_summary_lines psl
608 WHERE
609 psl.person_id=person_id1 and
610 palh.summary_line_id=psl.summary_line_id and
611 psl.status_code||''='A' and
612 palh.distribution_date between sdate AND edate AND
613 NVL(palh.include_in_er_flag,'Y') = 'Y' AND
614 NVL(palh.reversal_entry_flag,'N') = 'N' AND
615 nvl(original_line_flag, 'N') = 'N'
616 and palh.adjustment_batch_name is null
617 ORDER BY 2,3;
618 t_DIST_row t_DIST%ROWTYPE;
619 ---
620 -- This cursor finds list of all the persons in a people group, or under a supervisor
621 -- or charged to a particular gl account, project, award or expenditure organization
622 -- for a given effort report period
623 --
627 FROM psp_effort_report_templates E,
624 CURSOR t_all_person_id IS
625 /* replaced by new cursor for perf. fix for bug 1874615
626 SELECT distinct F.person_id,to_date(NULL) effective_start_date,to_date(NULL) effective_end_date
628 per_assignments_f F
629 WHERE NVL(E.people_group_id, NVL(F.people_group_id,-99)) = NVL(F.people_group_id,-99) AND
630 NVL(E.supervisor_id, NVL(F.supervisor_id,-99)) = NVL(F.supervisor_id,-99) AND
631 E.template_id = a_template_id AND
632 E.person_id IS NULL AND
633 exists (SELECT 'x'
634 FROM PSP_DISTRIBUTION_COMBO_V V
635 WHERE V.person_id = F.person_id AND
636 V.distribution_date between E.begin_date and E.end_date )
637 UNION
638 SELECT P.person_id,P.effective_start_date,P.effective_end_date
639 FROM psp_effort_report_templates P
640 WHERE template_id = a_template_id AND
641 P.person_id IS NOT NULL AND
642 exists (SELECT 'x'
643 FROM PSP_DISTRIBUTION_COMBO_V V
644 WHERE V.person_id = P.person_id AND
645 V.distribution_date between P.begin_date and P.end_date ); */
646 SELECT DISTINCT
647 psl.person_id,
648 to_date(NULL)
649 effective_start_date,
650 to_date(NULL) effective_end_date
651 FROM
652 per_all_assignments_f paf,
653 psp_Summary_lines psl,
654 psp_effort_report_templates pet
655 WHERE
656 (pet.template_id=a_template_id and
657 pet.person_id is null and
658 (
659 ((paf.people_group_id = pet.people_group_id
660 OR paf.supervisor_id = pet.supervisor_id)
661 -- Added for Bug Fix 2892637 by tbalacha
662 AND (paf.effective_start_date <= pet.end_date AND paf.effective_end_date >= pet.begin_date))
663 --End of code for Bug 2892637
664 -- Start bug fix:1988747 by Ritesh on Sep 12, 2001
665 OR psl.gl_code_combination_id = pet.gl_code_combination_id
666 OR psl.project_id IN (SELECT project_id FROM psp_template_projects
667 WHERE template_id = a_template_id)
668 OR psl.award_id IN (SELECT award_id FROM psp_template_awards
669 WHERE template_id = a_template_id)
670 OR psl.expenditure_organization_id IN (SELECT expenditure_organization_id FROM psp_template_organizations
671 WHERE template_id = a_template_id)
672 -- End bug fix:1988747
673 )
674 and
675 psl.person_id =paf.person_id
676 AND paf.assignment_type ='E' --Added for bug 2624259.
677 and psl.status_code||''='A'
678 and
679 exists
680 (
681 SELECT summary_line_id
682 FROM psp_distribution_lines_history pdlh
683 WHERE
684 pdlh.summary_line_id=psl.summary_line_id and
685 pdlh.distribution_date
686 between
687 pet.begin_date
688 and
689 pet.end_date
690 and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
691 nvl(REVERSAL_ENTRY_FLAG,'N')='N'
692 UNION ALL
693 SELECT summary_line_id
694 FROM psp_adjustment_lineS_history palh
695 WHERE
696 palh.summary_line_id=psl.summary_line_id and
697 palh.distribution_date
698 BETWEEN pet.begin_date AND pet.end_date
699 and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
700 nvl(REVERSAL_ENTRY_FLAG,'N')='N'
701 UNION ALL
702 SELECT summary_line_id
703 FROM psp_pre_gen_dist_lines_history ppgh where
704 ppgh.summary_line_id=psl.summary_line_id and
705 ppgh.distribution_date
706 BETWEEN pet.begin_date AND pet.end_date
707 and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
708 nvl(REVERSAL_ENTRY_FLAG,'N')='N'))
709
710 UNION
711
712 SELECT
713 pet.person_id,
714 pet.effective_start_date,
715 pet.effective_end_date
716 FROM psp_Summary_lines psl,
717 psp_effort_report_templates pet
718 WHERE
719 pet.template_id=a_template_id and
720 pet.person_id=psl.person_id and
721 pet.person_id is not null
722 and psl.status_code||''='A'
723 and exists
724 (
725 SELECT summary_line_id
726 FROM psp_distribution_lines_history pdlh
727 WHERE
728 pdlh.summary_line_id=psl.summary_line_id and
729 pdlh.distribution_date
730 BETWEEN
731 pet.begin_date
732 and
733 pet.end_date
734 and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
735 nvl(REVERSAL_ENTRY_FLAG,'N')='N'
736 UNION ALL
737 SELECT summary_line_id
738 FROM psp_adjustment_lines_history palh
739 WHERE
740 palh.summary_line_id=psl.summary_line_id and
741 palh.distribution_date
742 BETWEEN pet.begin_date and pet.end_date
743 and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
744 nvl(REVERSAL_ENTRY_FLAG,'N')='N'
745 UNION ALL
746 SELECT summary_line_id
747 FROM psp_pre_gen_dist_lines_history ppgh
748 WHERE
749 ppgh.summary_line_id=psl.summary_line_id and
750 ppgh.distribution_date
751 BETWEEN pet.begin_date AND pet.end_date
752 and nvl(INCLUDE_IN_ER_FLAG,'Y')='Y' and
753 nvl(REVERSAL_ENTRY_FLAG,'N')='N');
754 ----
755 CURSOR determine_version_num(person_id1 IN NUMBER,begin_date1 IN DATE,
756 end_date1 IN DATE,report_type1 IN VARCHAR2) IS
760 P.template_id = T.template_id AND
757 SELECT version_num + 1,effort_report_id ----NVL(MAX(version_num) + 1,1)
758 FROM psp_effort_reports P, psp_effort_report_templates T
759 WHERE P.person_id = person_id1 AND ---person_id has no effective dt in Assignments
761 ((begin_date1 BETWEEN T.begin_date AND T.end_date) OR
762 (end_date1 BETWEEN T.begin_date AND T.end_date) OR
763 (begin_date1 <= T.begin_date AND end_date1 >= T.end_date)) AND
764 T.report_type = report_type1
765 ORDER BY version_num + 1 DESC;
766 ----
767 CURSOR t_warning_report_exists(person_id1 IN NUMBER,begin_date1 IN DATE, end_date1 IN DATE) IS
768 SELECT decode(upper(status_code),'S','N','R', 'N', 'W'),
769 E.effort_report_id,E.version_num
770 ---INTO t_status_code,t_prev_effort_report_id,t_prev_version_num
771 FROM PSP_EFFORT_REPORTS E, PSP_EFFORT_REPORT_TEMPLATES T
772 WHERE E.person_id = person_id1 AND --- person_id has no effective dt in Assignments
773 ((begin_date1 BETWEEN T.begin_date AND T.end_date) OR
774 (end_date1 BETWEEN T.begin_date AND T.end_date) OR
775 (begin_date1 <= T.begin_date AND end_date1 >= T.end_date)) AND
776 E.template_id = T.template_id AND
777 ---g_template_row.report_type = 'N' AND
778 T.report_type = 'N'
779 ORDER BY status_code;
780 ---
781 CURSOR t_get_no_csr IS
782 SELECT COUNT(*)
783 FROM psp_effort_reports
784 WHERE template_id = a_template_id;
785 ---
786 CURSOR t_errors IS
787 SELECT message
788 FROM PSP_EFFORT_ERRORS
789 WHERE template_id = a_template_id AND
790 person_id = 0
791 UNION
792 SELECT message
793 FROM PSP_EFFORT_ERRORS
794 WHERE template_id = a_template_id AND
795 message not like 'Effort Rep%' AND
796 message not like 'Status Code%';
797 ---
798 t_dummy VARCHAR2(1);
799 t_no_records NUMBER(6);
800 t_rowid VARCHAR2(30);
801 t_reqid NUMBER(15);
802 t_prev_effort_report_id NUMBER(9);
803 t_version_number NUMBER(2);
804 t_prev_version_num NUMBER(2);
805 t_effort_report_id NUMBER(9);
806 t_effort_report_line_num NUMBER(15);
807 t_authorized_person_id NUMBER(15);
808 t_status_code VARCHAR2(30);
809 t_ERROR_set VARCHAR2(1);
810 t_total_errors NUMBER(2);
811 t_message VARCHAR2(255);
812 t_project NUMBER(2);
813 t_award NUMBER(2);
814 t_org NUMBER(2);
815 nc NUMBER;
816 errbuf1 VARCHAR2(1999);
817 NO_GL_MATCHES_FOUND EXCEPTION;
818 NO_POETA_MATCHES_FOUND EXCEPTION;
819 ELEMENT_TYPE_NO_INCLUDE EXCEPTION;
820 STATUS_CODE_IS_T EXCEPTION;
821 ---
822
823 BEGIN
824 -- dbms_output.enable(1000000);
825 IF p_template_exists(a_template_id) <> 0 THEN
826 log_errors(a_template_id,0,'Template '||TO_CHAR(a_template_id) || ' Does Not Exist',0,0);
827 ---RAISE FND_API.g_exc_unexpected_error;
828 END IF;
829 ---
830 ---
831 FOR t_PERS_rec IN t_all_person_id LOOP
832 --- Check if a Normal Effort report has already been taken for that Template period,
833 --- For that person.
834 t_status_code := NULL;
835 OPEN t_warning_report_exists(t_PERS_rec.person_id ,g_template_row.begin_date,
836 g_template_row.end_date);
837 FETCH t_warning_report_exists INTO t_status_code,t_prev_effort_report_id,t_prev_version_num;
838 CLOSE t_warning_report_exists;
839 ---
840 /*
841 SELECT decode(upper(status_code),'S','N','W'),
842 E.effort_report_id,E.version_num
843 INTO t_status_code,t_prev_effort_report_id,t_prev_version_num
844 FROM PSP_EFFORT_REPORTS E, PSP_EFFORT_REPORT_TEMPLATES T
845 WHERE E.person_id = t_PERS_rec.person_id AND --- person_id has no effective dt in Assignments
846 ((g_template_row.begin_date BETWEEN T.begin_date AND T.end_date) OR
847 (g_template_row.end_date BETWEEN T.begin_date AND T.end_date) OR
848 (g_template_row.begin_date <= T.begin_date AND g_template_row.end_date >= T.end_date)) AND
849 E.template_id = T.template_id AND
850 ---g_template_row.report_type = 'N' AND
851 T.report_type = 'N' AND
852 ROWNUM = 1;
853 */
854 IF t_status_code IS NOT NULL THEN
855 ---
856 --- If , For A Given Adhoc Report, old Adhoc Report(s) Exists For An Exact Match Of
857 --- Person_id, Report Begin And End Dates, delete all corresponding row(s), from
858 --- all concerned tables.
859 ---
860 IF g_template_row.report_type = 'A' THEN
861 p_old_adhoc_delete(a_template_id,t_PERS_rec.person_id,
862 g_template_row.begin_date,g_template_row.end_date);
863 END IF;
864 ---
868 END IF;
865 IF t_status_code = 'W' THEN
866 --- Set the Warning Flag to 'Y', if any one of the status code was not 'N'ew.
867 t_ERROR_set := 'Y';
869 ---
870 --- Generate Warning Reports For Normal And Adhoc Both. 4/28/98
871 ---
872 FND_MESSAGE.set_name('PSP','PSP_EFT_REPORT_EXISTS');
873 FND_MESSAGE.set_token('PERSID', to_char(t_PERS_rec.person_id));
874 FND_MESSAGE.set_token('ASSIGN', to_char(t_DIST_row.assignment_id));
875 FND_MESSAGE.set_token('DISTDT', to_char(t_DIST_row.distribution_date,'MM/DD/YY'));
876 FND_MESSAGE.set_token('REPID',to_char(t_DIST_row.effort_report_id));
877 log_errors(a_template_id,t_PERS_rec.person_id,FND_MESSAGE.GET,
878 t_prev_effort_report_id,t_prev_version_num);
879 ELSE
880 t_status_code := 'N'; --- See High Level Design Doc.
881 END IF;
882 ---
883 --- Insert One row in PSP_EFFORT_REPORTS for each person selected.
884 --- Increment Version No. by 1 if for a given person and date range, there is an
885 --- existing report . Do Not Create new records , if the report type is (N)ormal
886 --- and the status is 'W'arning. */
887 ---
888 IF NOT(g_template_row.report_type = 'N' AND t_status_code = 'W') THEN
889 t_version_number := 1;
890 OPEN determine_version_num(t_PERS_rec.person_id,g_template_row.begin_date,
891 g_template_row.end_date,g_template_row.report_type);
892 FETCH determine_version_num INTO t_version_number,t_effort_report_id;
893 CLOSE determine_version_num;
894 ----
895 ----
896 IF t_version_number = 1 THEN
897 SELECT psp_effort_reports_s.NEXTVAL
898 INTO t_effort_report_id
899 FROM DUAL;
900 END IF;
901 ---
902 PSP_EFFORT_REPORTS_PKG.insert_row (
903 t_rowid,
904 t_effort_report_id, --- in NUMBER,
905 t_version_number, --- in NUMBER,
906 t_PERS_rec.person_id, --- in NUMBER,
907 t_PERS_rec.effective_start_date,
908 t_PERS_rec.effective_end_date,
909 SYSDATE, --- X_VERSION_CREATION_DATE in DATE,
910 NULL, --- X_VERSION_REASON_CODE in VARCHAR2,
911 NULL, --- X_MESSAGE_ID
912 NULL, --- X_REPORT_COMMENT in VARCHAR2,
913 SYSDATE,
914 t_status_code, --- X_STATUS_CODE in VARCHAR2,
915 a_template_id,
916 t_prev_effort_report_id,
917 p_business_group_id,
918 p_set_of_books_id,
919 'R');
920 END IF;
921 ---
922 /* Open Distribution Line Cursor and Select rows for given criterias */
923 ---
924 OPEN t_DIST(t_PERS_rec.person_id,g_template_row.begin_date,g_template_row.end_date);
925 LOOP
926 FETCH t_DIST INTO t_DIST_row;
927 EXIT WHEN t_DIST%NOTFOUND;
928 g_dist_flag := 'Y';
929 --- Derive POETA logic
930 /* --- commentee for 5080403
931 IF t_DIST_row.source = 'distribution_lines' THEN
932 g_ssdseo_row.schedule_line_id := t_DIST_row.schedule_line_id;
933 g_ssdseo_row.default_org_account_id := t_DIST_row.default_org_account_id;
934 g_ssdseo_row.suspense_org_account_id := t_DIST_row.suspense_org_account_id;
935 g_ssdseo_row.element_account_id := t_DIST_row.element_account_id;
936 g_ssdseo_row.org_schedule_id := t_DIST_row.org_schedule_id;
937 ---
938 ----g_poeta_row := p_get_POETA(a_template_id,t_PERS_rec.person_id,g_ssdseo_row); commented for 5080403
939
940
941 -- Bug fix 4511284
942 If g_poeta_row.gl_code_combination_id is not null then
943 g_poeta_row.gl_code_combination_id:= nvl(t_DIST_row.gl_code_combination_id,g_poeta_row.gl_code_combination_id);
944 ELSE
945 g_poeta_row.expenditure_type:= nvl(t_DIST_row.expenditure_type,g_poeta_row.expenditure_type);
946 END IF;
947 -- End of Bug fix 4511284
948
949
950 ELSE */
951 g_poeta_row.gl_code_combination_id := t_DIST_row.gl_code_combination_id;
952 g_poeta_row.project_id := t_DIST_row.project_id;
953 g_poeta_row.expenditure_organization_id := t_DIST_row.expenditure_organization_id;
954 g_poeta_row.expenditure_type := t_DIST_row.expenditure_type;
955 g_poeta_row.task_id := t_DIST_row.task_id;
956 g_poeta_row.award_id := t_DIST_row.award_id;
957 ----END IF; commented for 5080403
958 ---
959 --- Check Whether Status Code In Distribution Lines = 'T'.
960 --- Check From psp_element_types whether to include Element Type Or Not.
961 --- Do matching of GL, POETA with templates
962 BEGIN
963 --- If The Status Code In Distribution Lines Is T, Then Generate Warning
967 WHERE person_id = t_PERS_rec.person_id AND
964 --- Report For That Person.
965 IF NVL(t_DIST_row.status_code,'0') = 'T' THEN
966 DELETE FROM PSP_EFFORT_REPORTS
968 template_id = a_template_id;
969 IF (SQL%NOTFOUND) THEN
970 NULL;
971 END IF;
972 ---
973 BEGIN
974 SELECT 'x'
975 INTO t_dummy
976 FROM PSP_EFFORT_ERRORS
977 WHERE template_id = a_template_id AND
978 person_id = t_PERS_rec.person_id AND
979 ROWNUM = 1;
980 EXCEPTION
981 WHEN NO_DATA_FOUND THEN
982 log_errors(a_template_id,t_PERS_rec.person_id,
983 'Status Code = T ',NULL,NULL);
984 END;
985 RAISE STATUS_CODE_IS_T;
986 END IF;
987 ---
988 -- Introduced BG/SOB parameters in p_element_exists call for bug fix 3098050
989 IF p_element_exists(t_DIST_row.element_type_id,t_DIST_row.distribution_date,
990 p_business_group_id, p_set_of_books_id) <> 0 THEN
991 RAISE ELEMENT_TYPE_NO_INCLUDE;
992 END IF;
993 ----
994
995 /***** Commented the following for bug fix 3063762, as POETA/GL check is for filtering persons and not distributions.
996 IF g_template_row.gl_code_combination_id IS NOT NULL THEN -- TEMPLATE DIST RESULT
997 IF NOT(NVL(g_template_row.gl_code_combination_id, -- NULL NULL Select
998 NVL(g_poeta_row.gl_code_combination_id,-99)) = -- NULL Value Select
999 NVL(g_poeta_row.gl_code_combination_id,-99)) THEN -- Value NULL NoSelect
1000 -- Value = Value Select
1001 RAISE NO_GL_MATCHES_FOUND; -- Value <> Value NoSelect
1002 END IF;
1003 ---
1004 ELSE
1005 t_project := p_project_exists(a_template_id,g_poeta_row.project_id);
1006 t_award := p_award_exists(a_template_id,g_poeta_row.award_id);
1007 t_org := p_org_exists(a_template_id,g_poeta_row.expenditure_organization_id) ;
1008 ----
1009 IF NOT ((t_project = 0 AND t_org = 0 AND t_award = 0) OR
1010 (t_project = 1 AND t_org = 0 AND t_award = 0) OR
1011 (t_project = 0 AND t_org = 1 AND t_award = 0) OR
1012 (t_project = 0 AND t_org = 0 AND t_award = 1) OR
1013 (t_project = 1 AND t_org = 1 AND t_award = 0) OR
1014 (t_project = 0 AND t_org = 1 AND t_award = 1) OR
1015 (t_project = 1 AND t_org = 0 AND t_award = 1) OR
1016 (t_project = 1 AND t_org = 1 AND t_award = 1))THEN
1017 RAISE NO_POETA_MATCHES_FOUND;
1018 END IF;
1019 END IF;
1020
1021 End of bug fix 3063762 *****/
1022
1023 ---
1024 ---
1025 --- Check to see whether Effort Report Already taken
1026 ---
1027 IF t_DIST_row.effort_report_id IS NOT NULL THEN
1028 ----
1029 --- Do Not use PSP_EFFORT_REPORTS_PKG.update_row Table Handler For The
1030 --- Following Update Statement , as it has a implicit COMMIT, used by
1031 --- PSPERCER Form.
1032 ---
1033 UPDATE psp_effort_reports
1034 SET prev_effort_report_id = t_DIST_row.effort_report_id
1035 WHERE effort_report_id = t_effort_report_id AND
1036 version_num = t_version_number;
1037 ---
1038 IF (SQL%NOTFOUND) THEN
1039 NULL;
1040 END IF;
1041 END IF;
1042 ---
1043 --- Insert in psp_effort_report_details rows which passed the criteria
1044 ---
1045 IF p_sync_details(t_effort_report_id,t_version_number,
1046 t_DIST_row.assignment_id,t_DIST_row.element_type_id,
1047 g_poeta_row,
1048 t_DIST_row.distribution_amount) <> 0 THEN
1049 RAISE NO_DATA_FOUND;
1050 END IF;
1051 /*
1052 if g_poeta_row.project_id is not null then
1053 --dbms_output.put_line('GL='||to_char(g_poeta_row.gl_code_combination_id)||
1054 'P='||to_char(g_poeta_row.project_id) ||
1055 'O='||to_char(g_poeta_row.expenditure_organization_id)||
1056 'E='|| g_poeta_row.expenditure_type ||
1057 'T='|| to_char(g_poeta_row.task_id) ||
1058 'A='||to_char(g_poeta_row.award_id) ||
1059 'PERSON='||t_pers_rec.person_id ||
1060 'source='||t_dist_row.source ||
1064 ---
1061 'dist_id='||t_dist_row.distribution_line_id);
1062 end if;
1063 */
1065 --- Do not create rows in Report Details, if the report type is (N)ormal
1066 --- and status_code is 'W'arning.
1067 ---
1068 IF NOT(g_template_row.report_type = 'N' AND t_status_code = 'W') THEN
1069 SELECT NVL(MAX(effort_report_line_num) + 1,1)
1070 INTO t_effort_report_line_num
1071 FROM psp_effort_report_details
1072 WHERE effort_report_id = t_effort_report_id AND
1073 version_num = t_version_number;
1074 ---
1075 PSP_EFFORT_REPORT_DETAILS_PKG.insert_row (
1076 t_rowid, --- in out NOCOPY VARCHAR2,
1077 t_effort_report_id, --- in NUMBER,
1078 t_version_number, --- in NUMBER,
1079 t_effort_report_line_num, --- in NUMBER,
1080 t_DIST_row.assignment_id, --- in NUMBER,
1081 t_DIST_row.element_type_id,
1082 g_poeta_row.gl_code_combination_id, --- in NUMBER,
1083 g_poeta_row.project_id, --- in NUMBER,
1084 g_poeta_row.expenditure_organization_id, --- in NUMBER,
1085 g_poeta_row.expenditure_type, --- X_EXPENDITURE_TYPE in VARCHAR2,
1086 g_poeta_row.task_id, --- X_TASK_ID in NUMBER,
1087 g_poeta_row.award_id, --- in NUMBER,
1088 t_DIST_row.distribution_amount, --- in NUMBER,
1089 'R');
1090 END IF;
1091 ---
1092 IF t_DIST_row.source = 'distribution_lines' and g_template_row.report_type = 'N' THEN
1093 UPDATE psp_distribution_lines_history
1094 SET effort_report_id = NVL(t_effort_report_id,t_prev_effort_report_id)
1095 WHERE distribution_line_id = t_DIST_row.distribution_line_id;
1096 ELSIF t_DIST_row.source = 'pre_gen_dist_lines' and g_template_row.report_type = 'N' THEN
1097 UPDATE psp_pre_gen_dist_lines_history
1098 SET effort_report_id = NVL(t_effort_report_id,t_prev_effort_report_id)
1099 WHERE pre_gen_dist_line_id = t_DIST_row.distribution_line_id;
1100 ELSIF t_DIST_row.source = 'adjustment_lines' and g_template_row.report_type = 'N' THEN
1101 UPDATE psp_adjustment_lines_history
1102 SET effort_report_id = NVL(t_effort_report_id,t_prev_effort_report_id)
1103 WHERE adjustment_line_id = t_DIST_row.distribution_line_id;
1104 END IF;
1105 ---
1106 IF (SQL%NOTFOUND) THEN
1107 NULL;
1108 END IF;
1109 ---
1110 EXCEPTION
1111 WHEN ELEMENT_TYPE_NO_INCLUDE THEN
1112 --dbms_output.put_line('ELEMENT_TYPE_NO_INCLUDE Exception Raised');
1113 NULL;
1114 WHEN NO_DATA_FOUND THEN
1115 -- dbms_output.put_line('NO_DATA_FOUND Exception Raised');
1116 NULL;
1117 WHEN NO_GL_MATCHES_FOUND THEN
1118 -- dbms_output.put_line('NO_GL_MATCHES_FOUND Exception Raised');
1119 NULL;
1120 WHEN NO_POETA_MATCHES_FOUND THEN
1121 -- dbms_output.put_line('NO_POETA_MATCHES_FOUND Exception Raised');
1122 NULL;
1123 WHEN STATUS_CODE_IS_T THEN
1124 -- dbms_output.put_line('STATUS_CODE_IS_T Exception Raised');
1125 NULL;
1126 END;
1127 END LOOP;
1128 CLOSE t_DIST;
1129 ---
1130 --- Delete Header in case there were no details selected for a row in PSP_EFFORT_REPORTS
1131 ---
1132 IF p_effort_details_exists(t_effort_report_id,t_version_number) = -7 THEN
1133 DELETE FROM PSP_EFFORT_REPORTS
1134 WHERE effort_report_id = t_effort_report_id AND
1135 version_num = t_version_number;
1136 IF (SQL%NOTFOUND) THEN
1137 NULL;
1138 END IF;
1139 END IF;
1140 ---
1141 END LOOP;
1142 ---
1143 --- Logging All Errors Encountered into ERRBUF.
1144 ---
1145 OPEN t_errors;
1146 LOOP
1147 FETCH t_errors INTO t_message;
1148 EXIT WHEN t_errors%NOTFOUND;
1149 IF NVL(length(errbuf1),0) <= 1700 THEN
1150 errbuf1 := SUBSTR((errbuf1 ||fnd_global.local_chr(10) || t_message),1,1700);
1151 END IF;
1152 END LOOP;
1153 CLOSE t_errors;
1154 ---
1155 --- Report Errors :::
1156 ---
1157 IF NVL(length(errbuf1),0) <> 0 THEN
1158 errbuf := SUBSTR(errbuf1,1,230); --- More than 230 , it gives value error
1159 retcode := 2; --- in Oracle Applications.
1160 RETURN;
1164 errbuf := SUBSTR(errbuf1,1,230); --- More than 230 , it gives value error
1161 ELSIF g_element_flag = 'N' AND g_dist_flag = 'Y' THEN
1162 FND_MESSAGE.set_name('PSP','PSP_NO_MATCHING_ELEMENTS');
1163 errbuf1 := FND_MESSAGE.get;
1165 --dbms_output.put_line('The Element Type(s) Are Not Set Up in PSP_EFFORT_REPORT_ELEMENTS' );
1166 retcode := 2;
1167 ---RETURN;
1168 ELSIF g_dist_flag = 'N' THEN
1169 FND_MESSAGE.set_name('PSP','PSP_NO_DISTRIBUTIONS');
1170 errbuf1 := FND_MESSAGE.get;
1171 errbuf := SUBSTR(errbuf1,1,230); --- More than 230 , it gives value error
1172 --dbms_output.put_line('No Distribution Lines were picked up for chosen criteria' );
1173 retcode := 2;
1174 ---RETURN;
1175 ELSE
1176 retcode := 0;
1177 END IF;
1178 ---
1179 COMMIT;
1180 ---
1181 --- Submit Warning Report Program Concurrent Request.
1182 ---
1183 IF NVL(t_ERROR_set,'N') = 'Y' THEN
1184 t_reqid := FND_REQUEST.submit_request('PSP',
1185 'PSPERERR',NULL,NULL,NULL,
1186 a_template_id);
1187 --dbms_output.put_line('Warning Report Kicked...');
1188 END IF;
1189 ---
1190 --- Initiate Workflow ::: Introduced by Venkat.
1191 ---
1192 OPEN t_get_no_csr;
1193 FETCH t_get_no_csr INTO t_no_records;
1194 IF t_get_no_csr%NOTFOUND or NVL(t_no_records,0) = 0 then
1195 NULL;
1196 ELSE
1197 IF NVL(g_template_row.enable_workflow_flag,'N') = 'Y' THEN
1198 --------------------------------------------------------------------------------------------------------
1199 --4/15/99 Shu Lei added.
1200 --If user option PSP:Effort Cert. Enhanced Workflow is YES, then invoke workflow engine, else use the
1201 --old workflow.
1202 --------------------------------------------------------------------------------------------------------
1203 -- dbms_output.put_line('user profile PSP_EFFORT_REPORT_WORKFLOW ='||p_workflow_option);
1204 -- IF (p_workflow_option = 'V1') THEN
1205 -- dbms_output.put_line('Version 1 of Workflow......');
1206 -- nc := INIT_WORKFLOW(a_template_id);
1207 -- ELSIF (p_workflow_option = 'ENHANCED') THEN
1208 -- dbms_output.put_line('Enhanced Workflow......');
1209 nc := psp_wf_eff_pkg.INIT_WORKFLOW(a_template_id);
1210 -- END IF;
1211 ---------------------------------------------------------------------------------------------------------
1212 IF nc = -1 THEN
1213 FND_MESSAGE.set_name('PSP','PSP_WORKFLOW_FAILED');
1214 ---errbuf1 := SUBSTR(errbuf1 || chr(10) ||FND_MESSAGE.get,1999);
1215 errbuf1 := SUBSTR(FND_MESSAGE.get,1,1999);
1216 errbuf := SUBSTR(errbuf1,1,230);
1217 retcode := 2;
1218 --dbms_output.put_line('WorkFlow Process Failed :: NO_DATA_FOUND');
1219 RETURN;
1220 ELSIF nc = -2 THEN
1221 FND_MESSAGE.set_name('PSP','PSP_WORKFLOW_FAILED');
1222 ---errbuf1 := SUBSTR(errbuf1 || chr(10) ||FND_MESSAGE.get,1999);
1223 errbuf1 := SUBSTR(FND_MESSAGE.get,1,1999);
1224 errbuf := SUBSTR(errbuf1,1,230);
1225 retcode := 2;
1226 --dbms_output.put_line('Workflow Process Failed :: FATAL ERROR');
1227 RETURN;
1228 END IF;
1229 END IF;
1230 END IF;
1231 CLOSE t_get_no_csr;
1232 END;
1233 ---
1234 ---
1235 FUNCTION get_gl_description(a_code_combination_id IN NUMBER) RETURN VARCHAR2
1236 IS
1237 t_name VARCHAR2(1000);
1238 set_of_bks_id varchar2(15);
1239 BEGIN
1240 set_of_bks_id := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
1241 t_name := PSP_GENERAL.get_gl_description(set_of_bks_id, a_code_combination_id);
1242 RETURN(t_name);
1243 END;
1244 ---
1245 ---
1246 FUNCTION INIT_WORKFLOW(a_template_id IN NUMBER)
1247 --- This procedure is to initiate workflow process
1248 --- This procedure fetches all reportids for a given template id ad
1249 --- Initiates workflow process for each report id
1250 RETURN NUMBER
1251 IS
1252 CURSOR get_report_id_csr IS
1253 SELECT effort_report_id , max(version_num)
1254 FROM psp_effort_reports
1255 WHERE template_id = a_template_id
1256 GROUP BY effort_report_id;
1257
1258 l_user_name VARCHAR2(240); -- Bug 7004679
1259
1260 l_report_id NUMBER;
1261 l_wf_report_id VARCHAR2(30);
1262 -- Adding these variables and make the effort report id sent to wf unique 08.31.99
1263 l_report_id1 NUMBER;
1264 l_max_ver NUMBER;
1265 --End 8.31.99
1266
1267 BEGIN
1268 OPEN get_report_id_csr;
1269 LOOP
1270 FETCH get_report_id_csr INTO l_report_id, l_max_ver;
1271 EXIT WHEN get_report_id_csr%NOTFOUND;
1272 -- 8.31.99
1273 l_report_id1 := to_char(l_report_id) || to_char(l_max_ver);
1274 l_wf_report_id := l_report_id1;
1275 -- End 8.31.99
1276
1277 l_user_name := fnd_global.user_name; -- Bug 7004679
1278
1279 wf_engine.createprocess('INF_EMP',
1280 l_wf_report_id,
1281 'PROC_EMP');
1282
1283 /*Added for bug 7004679 */
1284 wf_engine.setitemowner('INF_EMP',
1285 l_wf_report_id,
1286 l_user_name);
1287
1288
1289 wf_engine.startprocess('INF_EMP',
1290 l_wf_report_id);
1291 END LOOP;
1292 CLOSE get_report_id_csr;
1293 RETURN(0);
1294 EXCEPTION
1295 WHEN NO_DATA_FOUND THEN
1296 RETURN(-1);
1297 WHEN OTHERS THEN
1298 RETURN(-2);
1299 END init_workflow;
1300 END;
[Home] [Help]