[Home] [Help]
PACKAGE BODY: APPS.PO_PALT_ASSIGNMENTS
Source
1 PACKAGE BODY PO_PALT_ASSIGNMENTS AS
2 /* $Header: POPTASGB.pls 120.16.12020000.8 2013/03/26 06:08:24 smvinod ship $ */
3
4 Function GetAssignmentNumber
5 (pReqLines IN VARCHAR2)
6 RETURN VARCHAR2 AS
7 sql_stmt VARCHAR2(500);
8 TYPE ReqLinesCurTyp IS REF CURSOR;
9 ReqlinesCur ReqLinesCurTyp;
10
11 l_requisition_line_Id NUMBER;
12 l_Requisition_Details po_palt_assignments.Requisition_Details;
13 i PLS_INTEGER := 0;
14 l_Assignment_Number VARCHAR2(30);
15 l_Assignment_count NUMBER;
16
17 BEGIN
18 sql_stmt := 'SELECT REQUISITION_LINE_ID REQUISITION_ID FROM PO_REQUISITION_LINES_ALL WHERE '||pReqLines||' and 1 = 1 ';
19
20 OPEN ReqlinesCur FOR sql_stmt;
21 LOOP
22 FETCH ReqlinesCur INTO l_requisition_line_Id;
23 i := i + 1;
24 l_Requisition_Details(i).Requisition_line_Id := l_requisition_line_Id;
25 EXIT WHEN ReqlinesCur%NOTFOUND;
26 END LOOP;
27 CLOSE ReqlinesCur;
28
29 l_Assignment_Number := po_palt_assignments_hook.AssignmentNumberCustHook(l_Requisition_Details);
30 IF (l_Assignment_Number is not NULL) THEN
31 SELECT count(*)
32 INTO l_Assignment_count
33 FROM PO_CLM_ASSIGNMENTS
34 WHERE ASSIGNMENT_NUMBER = l_Assignment_Number;
35
36 IF (l_Assignment_count > 0) THEN
37 l_Assignment_Number := PO_CLM_ASSIGNMENT_SEQUENCE.CURRVAL;
38 END IF;
39 ELSE
40 l_Assignment_Number := PO_CLM_ASSIGNMENT_SEQUENCE.CURRVAL;
41 END IF;
42
43 return l_Assignment_Number;
44
45 EXCEPTION
46
47 WHEN OTHERS THEN
48 dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
49
50 END GetAssignmentNumber;
51
52 Procedure ManageAssignementAction
53 (pAssignmentId IN NUMBER,
54 pOldBuyerId IN NUMBER,
55 pnewBuyerId IN NUMBER,
56 pOldStatus IN NUMBER,
57 pNewStatus IN NUMBER,
58 pReason IN VARCHAR2
59 )
60 IS
61
62 l_Assignment_found NUMBER;
63 l_Assignment_Number varchar2(30);
64 l_Action_Taken_By NUMBER;
65
66 BEGIN
67
68 SELECT Count(*) into l_Assignment_found
69 FROM PO_CLM_ASSGNMT_ACTION_HISTORY
70 WHERE ASSIGNMENT_ID = pAssignmentId
71 and ACTION_END_DATE is NULL;
72
73 SELECT ASSIGNMENT_NUMBER
74 INTO l_Assignment_Number
75 FROM PO_CLM_ASSIGNMENTS
76 WHERE ASSIGNMENT_ID = pAssignmentId;
77
78 SELECT EMPLOYEE_ID
79 INTO l_Action_Taken_By
80 FROM FND_USER
81 WHERE USER_ID = fnd_global.user_id;
82
83 IF (l_Assignment_found = 0) then
84
85 INSERT INTO PO_CLM_ASSGNMT_ACTION_HISTORY
86 (ASSIGNMENT_ID ,
87 OLD_STATUS ,
88 NEW_STATUS ,
89 OLD_BUYER_ID ,
90 NEW_BUYER_ID ,
91 REASON ,
92 ACTION_START_DATE ,
93 ACTION_END_DATE ,
94 ACTION_TAKEN_BY ,
95 CREATED_BY ,
96 CREATION_DATE ,
97 LAST_UPDATED_BY ,
98 LAST_UPDATE_DATE ,
99 LAST_UPDATE_LOGIN)
100
101 VALUES (pAssignmentId,
102 pOldStatus ,
103 pNewStatus ,
104 pOldBuyerId,
105 pnewBuyerId,
106 pReason,
107 sysdate,
108 null,
109 l_Action_Taken_By,
110 fnd_global.user_id,
111 sysdate,
112 fnd_global.user_id,
113 sysdate,
114 fnd_global.login_id);
115 ELSE
116 UPDATE PO_CLM_ASSGNMT_ACTION_HISTORY
117 SET ACTION_END_DATE = SYSDATE-1/(24*60*60),
118 last_updated_by = fnd_global.user_id ,
119 last_update_date = SYSDATE ,
120 last_update_login = fnd_global.login_id
121 WHERE ASSIGNMENT_ID = pAssignmentId
122 AND ACTION_END_DATE IS NULL
123 AND (NEW_STATUS = Decode(pOldStatus,1,6,pOldStatus) OR NEW_STATUS = Decode(pOldStatus,1,7,pOldStatus));
124
125 INSERT INTO PO_CLM_ASSGNMT_ACTION_HISTORY
126 (ASSIGNMENT_ID ,
127 OLD_STATUS ,
128 NEW_STATUS ,
129 OLD_BUYER_ID ,
130 NEW_BUYER_ID ,
131 REASON ,
132 ACTION_START_DATE ,
133 ACTION_END_DATE ,
134 ACTION_TAKEN_BY ,
135 CREATED_BY ,
136 CREATION_DATE ,
137 LAST_UPDATED_BY ,
138 LAST_UPDATE_DATE ,
139 LAST_UPDATE_LOGIN)
140 VALUES (pAssignmentId,
141 pOldStatus ,
142 pNewStatus ,
143 pOldBuyerId,
144 pnewBuyerId,
145 pReason,
146 sysdate,
147 null,
148 l_Action_Taken_By,
149 fnd_global.user_id,
150 sysdate,
151 fnd_global.user_id,
152 sysdate,
153 fnd_global.login_id);
154
155 /* IF (pOldBuyerId <> pnewBuyerId) THEN
156
157 UPDATE PO_REQUISITION_LINES_ALL
158 SET SUGGESTED_BUYER_ID = pnewBuyerId,
159 last_updated_by = fnd_global.user_id,
160 last_update_date = SYSDATE ,
161 last_update_login = fnd_global.login_id
162 WHERE ASSIGNMENT_NUMBER = l_Assignment_Number;
163
164 END IF;
165 */
166 END IF;
167
168 IF (pOldStatus <> pNewStatus) THEN
169 IF (pNewStatus = 2 or pNewStatus = 3) THEN /*Suspend or Cancel*/
170
171 UPDATE PO_CLM_ASSIGNMENTS
172 SET ASSIGNMENT_STATUS = pNewStatus ,
173 last_updated_by = fnd_global.user_id,
174 last_update_date = SYSDATE ,
175 last_update_login = fnd_global.login_id
176 WHERE ASSIGNMENT_ID = pAssignmentId;
177
178 IF (pNewStatus = 3) THEN
179
180
181
182 UPDATE PO_REQUISITION_LINES_ALL
183 SET ASSIGNMENT_NUMBER = NULL,
184 last_updated_by = fnd_global.user_id,
185 last_update_date = SYSDATE ,
186 last_update_login = fnd_global.login_id
187 WHERE ASSIGNMENT_NUMBER = l_Assignment_Number;
188
189 END IF;
190
191 ELSIF (pNewStatus = 5) THEN
192
193 UPDATE PO_CLM_ASSIGNMENTS
194 SET ASSIGNMENT_STATUS = pNewStatus ,
195 last_updated_by = fnd_global.user_id,
196 last_update_date = SYSDATE ,
197 last_update_login = fnd_global.login_id,
198 ACTUAL_COMPLETION_DATE = SYSDATE
199 WHERE ASSIGNMENT_ID = pAssignmentId;
200
201 UPDATE PO_CLM_ASSGNMT_MILESTONE_DTLS
202 SET ACTUAL_COMPLETION_DATE = SYSDATE ,
203 last_updated_by = fnd_global.user_id,
204 last_update_date = SYSDATE ,
205 last_update_login = fnd_global.login_id
206 WHERE ASSIGNMENT_ID = pAssignmentId
207 AND ACTUAL_COMPLETION_DATE IS NULL;
208
209 ELSIF (pNewStatus = 6) THEN
210
211 UPDATE PO_CLM_ASSIGNMENTS
212 SET ASSIGNMENT_STATUS = 1 ,
213 last_updated_by = fnd_global.user_id,
214 last_update_date = SYSDATE ,
215 last_update_login = fnd_global.login_id
216 WHERE ASSIGNMENT_ID = pAssignmentId;
217
218 END IF;
219
220 END IF;
221
222 EXCEPTION
223
224 WHEN OTHERS THEN
225 dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
226
227 END ManageAssignementAction;
228
229
230
231 PROCEDURE getAssociatedReqLines(pReqLineId IN NUMBER,
232 x_associated_line_ids OUT NOCOPY PO_TBL_NUMBER)
233 IS
234
235 l_line_ids Dbms_Sql.number_table;
236 l_cnt NUMBER :=0;
237 l_req_header_id NUMBER;
238
239 --Options Enhancemet
240 --Modified the cursor sql to fetch
241 --all the slins in clin-slin structure of the input clin,
242 --the associated clin-slin structure, whose any of the option lines has the base as the input clin line or its slins
243
244 CURSOR C(reqHeaderId NUMBER) IS
245 SELECT requisition_line_id
246 FROM po_requisition_lines_all
247 WHERE requisition_header_id = reqHeaderId
248 and (group_line_id = pReqLineId OR (po_autocreate_util_pvt.is_crosslinked_child(requisition_line_id,pReqLineId) ='T') );
249
250
251
252 BEGIN
253 SELECT requisition_header_id INTO l_req_header_id
254 FROM po_requisition_lines_all
255 WHERE requisition_line_id = pReqLineId;
256
257
258 OPEN C(l_req_header_id);
259 FETCH C BULK COLLECT INTO l_line_ids;
260 CLOSE C;
261
262 IF l_line_ids IS NOT NULL AND l_line_ids.Count > 0 THEN
263
264 x_associated_line_ids := PO_TBL_NUMBER();
265
266 FOR i IN 1..l_line_ids.Count LOOP
267 l_cnt := l_cnt+1;
268 x_associated_line_ids.extend(1);
269 x_associated_line_ids(l_cnt) := l_line_ids(i);
270 END LOOP;
271 END IF;
272
273 EXCEPTION
274 WHEN No_Data_Found THEN
275 x_associated_line_ids :=NULL;
276 END getAssociatedReqLines;
277
278 PROCEDURE removeLinesFromAssignments(pAssignmentNums IN PO_TBL_VARCHAR30
279 ,pLineIds IN PO_TBL_VARCHAR30
280 ,xReturnStatus OUT NOCOPY VARCHAR2
281 ,xErrAssigmentNums OUT NOCOPY PO_TBL_VARCHAR30)
282 IS
283 l_cnt NUMBER;
284 err_ids_cnt NUMBER:=0;
285 l_req_line_ids_str VARCHAR2(1000):='';
286 l_line_ids PO_TBL_NUMBER;
287 l_query VARCHAR2(2000);
288 l_par_query VARCHAR2(3000);
289 BEGIN
290
291 FOR j IN 1..pLineIds.Count LOOP
292 l_req_line_ids_str := l_req_line_ids_str ||','||pLineIds(j);
293
294 l_line_ids := NULL;
295 getAssociatedReqLines(pLineIds(j),l_line_ids);
296
297 IF l_line_ids IS NOT NULL THEN
298 FOR k IN 1..l_line_ids.Count LOOP
299 l_req_line_ids_str := l_req_line_ids_str ||','||l_line_ids(k);
300 END LOOP;
301 END IF;
302 END LOOP;
303
304 l_req_line_ids_str := SubStr(l_req_line_ids_str,2);
305
306 FOR i IN 1..pAssignmentNums.Count LOOP
307
308 SELECT Count(*) INTO l_cnt
309 FROM PO_CLM_ASSGNMT_MILESTONE_DTLS dtls, PO_CLM_ASSIGNMENTS ca
310 WHERE ca.ASSIGNMENT_NUMBER = pAssignmentNums(i)
311 AND ca.ASSIGNMENT_ID = dtls.ASSIGNMENT_ID
312 AND dtls.ACTUAL_COMPLETION_DATE IS NOT NULL;
313
314 IF l_cnt = 0 THEN
315 --None of the milestones have been completed,
316 --safe to unassign the lines
317 l_query := ' UPDATE po_requisition_lines_all ' ||
318 ' SET assignment_number = NULL, suggested_buyer_id = NULL ' ||
319 ' WHERE assignment_number = ''' || pAssignmentNums(i) || '''' ||
320 ' and requisition_line_id in ('|| l_req_line_ids_str || ')';
321
322 EXECUTE IMMEDIATE l_query;
323
324 -- IF assignmnet Type is PAR, the remove corresponding 'ASSIGNED'
325 -- from PAR lINE and set it back to 'COMPLETED'
326
327 l_par_query := 'UPDATE po_lines_draft_all ' ||
328 ' SET draft_line_status = ''COMPLETED''' ||
329 ' WHERE (po_line_id, draft_id) IN (SELECT par_line_id, par_draft_id' ||
330 ' FROM po_requisition_lines_all ' ||
331 ' WHERE assignment_number = ''' || pAssignmentNums(i) || '''' ||
332 ' AND par_line_id IS NOT NULL)';
333
334 EXECUTE IMMEDIATE l_par_query;
335
336
337 ELSE
338 --For this assignment, few/all milestones are completed.
339 --So we cannot unassign the lines
340 IF xErrAssigmentNums IS NULL THEN
341 xErrAssigmentNums := PO_TBL_VARCHAR30();
342 xReturnStatus := 'U';
343 END IF;
344 err_ids_cnt := err_ids_cnt+1;
345 xErrAssigmentNums.extend(1);
346 xErrAssigmentNums(err_ids_cnt) := pAssignmentNums(i);
347
348 END IF;
349 -- For assignment_type= 'PAR', we will have to update draft_line_status
350 -- PAR.
351
352
353 END LOOP;
354
355 IF xReturnStatus IS NULL THEN
356 xReturnStatus:='S';
357 END IF;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 xReturnStatus := 'E';
362
363 END removeLinesFromAssignments;
364
365 PROCEDURE launchNotifyByrWf (pAssignmentId IN NUMBER)
366 IS
367 BEGIN
368 po_palt_assignment_wf.launch_notify_buyer_wf(pAssignmentId);
369 END launchNotifyByrWf;
370
371 PROCEDURE launchNotifyByrWf (pAssignmentId IN NUMBER,
372 pPrevBuyerId IN NUMBER,
373 pReason IN VARCHAR2)
374 IS
375 BEGIN
376 po_palt_assignment_wf.launch_notify_buyer_wf(pAssignmentId, pPrevBuyerId, pReason);
377 END launchNotifyByrWf;
378
379 FUNCTION GetCurrencyCode(pAssignmentNum IN VARCHAR2) RETURN VARCHAR2
380 AS
381
382 x_currCodeCount NUMBER;
383 x_currCode VARCHAR2(12);
384 x_progress VARCHAR2(12);
385
386 BEGIN
387 x_progress := '10';
388 SELECT Count(*) INTO x_currCodeCount FROM (
389 SELECT DISTINCT Nvl(CURRENCY_CODE,PO_PALT_ASSIGNMENTS.GetLineFuncCurrCode(ORG_ID))
390 FROM po_requisition_lines_all WHERE assignment_number = pAssignmentNum);
391
392 x_progress := '20';
393
394 IF x_currCodeCount > 1 THEN
395 x_currCode := 'Multiple';
396 ELSE
397 SELECT DISTINCT Nvl(CURRENCY_CODE,PO_PALT_ASSIGNMENTS.GetLineFuncCurrCode(ORG_ID)) INTO x_currCode
398 FROM po_requisition_lines_all WHERE assignment_number = pAssignmentNum;
399
400 END IF;
401
402 RETURN x_currCode;
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 dbms_output.put_line(x_progress || ' An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
407 x_currCode:= 'Not found';
408 RETURN x_currCode;
409 END GetCurrencyCode;
410
411 FUNCTION GetLineFuncCurrCode(p_org_id IN NUMBER) RETURN VARCHAR2
412 AS
413
414 x_currCode VARCHAR2(12);
415 BEGIN
416
417 SELECT s.CURRENCY_CODE INTO x_currCode
418 FROM GL_SETS_OF_BOOKS s, FINANCIALS_SYSTEM_PARAMS_ALL f
419 WHERE s.SET_OF_BOOKS_ID = f.SET_OF_BOOKS_ID
420 AND f.ORG_ID = p_org_id;
421
422 RETURN x_currCode;
423 EXCEPTION
424 WHEN OTHERS THEN
425 dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
426 x_currCode:= 'Not found';
427 RETURN x_currCode;
428 END GetLineFuncCurrCode;
429
430 FUNCTION GetReqLineFuncCurrCode(p_line_id IN NUMBER) RETURN VARCHAR2
431 AS
432
433 x_currCode VARCHAR2(12);
434 x_org_id NUMBER;
435 BEGIN
436
437 SELECT ORG_ID INTO x_org_id FROM po_requisition_lines_all WHERE REQUISITION_LINE_ID = p_line_id;
438
439 SELECT s.CURRENCY_CODE INTO x_currCode
440 FROM GL_SETS_OF_BOOKS s, FINANCIALS_SYSTEM_PARAMS_ALL f
441 WHERE s.SET_OF_BOOKS_ID = f.SET_OF_BOOKS_ID
442 AND f.ORG_ID = x_org_id;
443
444 RETURN x_currCode;
445 EXCEPTION
446 WHEN OTHERS THEN
447 dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
448 x_currCode:= 'Not found';
449 RETURN x_currCode;
450 END GetReqLineFuncCurrCode;
451
452 Procedure updateSuggestedBuyer
453 (pAssignmentId IN NUMBER,
454 pnewBuyerId IN NUMBER
455 )
456 IS
457
458 l_Assignment_Number varchar2(30);
459
460 BEGIN
461
462 SELECT ASSIGNMENT_NUMBER
463 INTO l_Assignment_Number
464 FROM PO_CLM_ASSIGNMENTS
465 WHERE ASSIGNMENT_ID = pAssignmentId;
466
467 UPDATE PO_REQUISITION_LINES_ALL
468 SET SUGGESTED_BUYER_ID = pnewBuyerId,
469 last_updated_by = fnd_global.user_id,
470 last_update_date = SYSDATE ,
471 last_update_login = fnd_global.login_id
472 WHERE ASSIGNMENT_NUMBER = l_Assignment_Number;
473
474 EXCEPTION
475
476 WHEN OTHERS THEN
477 dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
478
479 END updateSuggestedBuyer;
480
481 --<PAR Project> : Autonomous procedure for creation of new assignment for PAR.
482 PROCEDURE autonomous_create_par_assign( p_par_req_header_id IN NUMBER,
483 p_assignment_buyer_id IN NUMBER,
484 p_draft_id IN NUMBER,
485 p_assignment_no OUT NOCOPY NUMBER)
486 IS
487 PRAGMA AUTONOMOUS_TRANSACTION;
488
489 l_assignment_number NUMBER;
490 TYPE ReqLinesCurTyp IS REF CURSOR;
491 ReqlinesCur ReqLinesCurTyp;
492 sql_stmt VARCHAR2(500);
493 l_requisition_line_Id NUMBER;
494 i PLS_INTEGER := 0;
495 l_Requisition_Details po_palt_assignments.Requisition_Details;
496 BEGIN
497 sql_stmt := 'SELECT REQUISITION_LINE_ID REQUISITION_ID FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID = ' || p_par_req_header_id;
498 OPEN ReqlinesCur FOR sql_stmt;
499 LOOP
500 FETCH ReqlinesCur INTO l_requisition_line_Id;
501 i := i + 1;
502 l_Requisition_Details(i).Requisition_line_Id := l_requisition_line_Id;
503 EXIT WHEN ReqlinesCur%NOTFOUND;
504 END LOOP;
505 CLOSE ReqlinesCur;
506
507 l_assignment_number := PO_PALT_ASSIGNMENTS_HOOK.AssignmentNumberCustHook(l_Requisition_Details);
508 -- If assignment number from hook is null, then create new assignment
509 IF l_assignment_number IS NULL THEN
510 l_assignment_number := PO_CLM_ASSIGNMENT_SEQUENCE.NEXTVAL;
511
512 INSERT INTO po_clm_assignments (assignment_id,
513 assignment_number,
514 created_by,
515 creation_date,
516 last_updated_by,
517 last_update_date,
518 last_update_login,
519 assignment_start_date,
520 buyer_id,
521 assignment_status,
522 assignment_type)
523
524 VALUES ( l_assignment_number,
525 l_assignment_number,
526 fnd_global.user_id,
527 sysdate,
528 fnd_global.user_id,
529 sysdate,
530 fnd_global.login_id,
531 sysdate, -- assignment start date,
532 p_assignment_buyer_id,
533 1, --Satus is 'Assigned'
534 'PAR');
535 END IF;
536
537 UPDATE po_requisition_lines_all
538 SET assignment_number = l_assignment_number,
539 last_updated_by = fnd_global.user_id,
540 last_update_date = SYSDATE ,
541 last_update_login = fnd_global.login_id
542 WHERE requisition_header_id = p_par_req_header_id;
543
544 --Set PAR line status to 'ASSIGNED'
545 UPDATE po_lines_draft_all
546 SET draft_line_status = 'ASSIGNED'
547 WHERE draft_id = p_draft_id;
548
549 COMMIT;
550 p_assignment_no := l_assignment_number;
551 END autonomous_create_par_assign;
552
553
554 --<PAR Project> : Create autoassignmnet for PAR at the end of PAR approval
555 --------------------------------------------------------------------------------
556 --Start of Comments
557 --Name: create_auto_assignment_for_par
558 --Pre-reqs:
559 -- None.
560 --Modifies:
561 -- None.
562 --Locks:
563 -- None.
564 --Function:
565 -- Workflow activity PL/SQL handler.
566 -- This procedure is the wrapper procedure of process_response_internal()
567 --Parameters:
568 --IN:
569 -- Standard workflow IN parameters
570 --OUT:
571 -- Standard workflow OUT parameters
572 --Testing:
573 --
574 --End of Comments
575 -------------------------------------------------------------------------------
576 PROCEDURE create_auto_assignment_for_par(itemtype IN VARCHAR2,
577 itemkey IN VARCHAR2,
578 actid IN NUMBER,
579 funcmode IN VARCHAR2,
580 resultout OUT NOCOPY VARCHAR2)
581 IS
582 l_progress VARCHAR2(1000);
583 l_par_draft_id NUMBER;
584 l_document_id NUMBER;
585 l_par_req_header_id NUMBER;
586 l_assignment_buyer_id NUMBER;
587 l_par_auto_assign_role po_doc_style_headers.par_auto_assign_role%TYPE;
588 l_par_auto_assign_flag VARCHAR2(1);
589 x_assignment_no NUMBER;
590 BEGIN
591
592 l_progress := 'PO_PALT_ASSIGNMENTS.create_auto_assignment_for_par Start';
593 po_wf_debug_pkg.Insert_debug(itemtype,itemkey, l_progress );
594
595 l_par_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
596 itemkey => itemkey,
597 aname => 'DRAFT_ID');
598 l_document_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
599 itemkey => itemkey,
600 aname => 'DOCUMENT_ID');
601 l_par_req_header_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
602 itemkey => itemkey,
603 aname => 'PAR_REQ_HEADER_ID');
604
605 BEGIN
606 SELECT NVL(ds.par_auto_assign_flag, 'N'),
607 ds.par_auto_assign_role
608 INTO l_par_auto_assign_flag,
609 l_par_auto_assign_role
610 FROM po_doc_style_headers ds,
611 po_headers_draft_all pohd
612 WHERE pohd.style_id = ds.style_id
613 AND pohd.draft_id = l_par_draft_id
614 AND pohd.po_header_id = l_document_id;
615 END;
616
617 l_progress := 'Auot assign flag ' || l_par_auto_assign_flag || ' Auto assign Role ' || l_par_auto_assign_role;
618 po_wf_debug_pkg.Insert_debug(itemtype,itemkey, l_progress );
619
620 IF l_par_auto_assign_flag = 'Y' THEN
621 -- If auto assignmnet role exists on set up page, but not on Award, then by defalut assign it to buyer.
622 IF l_par_auto_assign_role = 'AWARD_ADMINISTRATOR' THEN
623 SELECT NVL(clm_award_administrator, agent_id)
624 INTO l_assignment_buyer_id
625 FROM po_headers_all
626 WHERE po_header_id = l_document_id;
627 ELSIF l_par_auto_assign_role = 'CONTRACT_OFFICER' THEN
628 SELECT NVL(clm_contract_officer, agent_id)
629 INTO l_assignment_buyer_id
630 FROM po_headers_all
631 WHERE po_header_id = l_document_id;
632 ELSIF l_par_auto_assign_role = 'BUYER' THEN
633 SELECT agent_id
634 INTO l_assignment_buyer_id
635 FROM po_headers_all
636 WHERE po_header_id = l_document_id;
637 ELSE
638 -- IF auto assign role is null, assign it to in priority to Award Administrator, Contracting
639 -- Officer , or Buyer
640 SELECT Decode (clm_award_administrator,
641 NULL, (Decode (clm_contract_officer,
642 NULL, agent_id,
643 clm_contract_officer)),
644 clm_award_administrator)
645 INTO l_assignment_buyer_id
646 FROM po_headers_all
647 WHERE po_header_id = l_document_id;
648 END IF;
649
650 x_assignment_no := NULL;
651 autonomous_create_par_assign(l_par_req_header_id, l_assignment_buyer_id, l_par_draft_id, x_assignment_no);
652
653 l_progress := 'Assignment no '|| x_assignment_no || ' is created for req header id ' || l_par_req_header_id || ' with suggested buyer id ' || l_assignment_buyer_id;
654 po_wf_debug_pkg.Insert_debug(itemtype,itemkey, l_progress );
655
656 -- Launch notification for assignmnet
657 po_palt_assignment_wf.launch_notify_buyer_wf(x_assignment_no);
658
659 END IF;
660
661 resultout := wf_engine.eng_completed;
662
663 l_progress := 'PO_PALT_ASSIGNMENTS.create_auto_assignment_for_par End';
664 po_wf_debug_pkg.Insert_debug(itemtype,itemkey, l_progress );
665
666 END create_auto_assignment_for_par;
667
668 END PO_PALT_ASSIGNMENTS;