[Home] [Help]
PACKAGE BODY: APPS.PO_MASS_UPDATE_REQ_PVT
Source
1 PACKAGE BODY PO_Mass_Update_Req_PVT AS
2 /* $Header: PO_Mass_Update_Req_PVT.plb 120.8.12020000.3 2013/03/12 23:16:28 yuewliu ship $*/
3
4 --------------------------------------------------------------------------------------------------
5
6 -- Call is made such that the sql file POXMUR.sql calls the procedure
7 -- PO_Mass_Update_Req_GRP.Update_Persons
8 -- PO_Mass_Update_Req_GRP.Update_Persons calls the procedure PO_Mass_Update_Req_PVT.DO_Update
9
10 --------------------------------------------------------------------------------------------------
11
12 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
13 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
14 g_pkg_name CONSTANT VARCHAR2(100) := 'PO_Mass_Update_Req_PVT';
15 g_log_head CONSTANT VARCHAR2(1000) := 'po.plsql.' || g_pkg_name || '.';
16
17 TYPE g_req IS REF CURSOR;
18
19 TYPE g_req_approver is REF CURSOR;
20
21 --------------------------------------------------------------------------------------------------
22 -- Start of Comments
23
24 -- API Name : Do_Update
25 -- Type : Private
26 -- Pre-reqs : None
27 -- Function : Calls the procedure Update_Preparer/Update_Approver/Update_Requestor
28 -- or All of the above to update the Preparer/Approver/Requestor
29 -- accordingly to the input received from the Update_Person parameter value set.
30 -- Parameters :
31
32 -- IN : p_update_person Person needs to be updated(Preparer/Approver/Requestor).
33 -- p_old_personid Id of the old person.
34 -- p_new_personid Id of the new person.
35 -- p_document_type Type of the document(INTERNAL AND PURCHASE).
36 -- p_document_no_from Document number from.
37 -- p_document_no_to Document number to.
38 -- p_date_from Date from.
39 -- p_date_to Date to.
40 -- p_commit_interval Commit interval.
41
42 -- OUT : p_msg_data Actual message in encoded format.
43 -- p_msg_count Holds the number of messages in the API list.
44 -- p_return_status Return status of the API (Includes 'S','E','U').
45
46 -- End of Comments
47 --------------------------------------------------------------------------------------------------
48
49 PROCEDURE DO_Update(p_update_person IN VARCHAR2,
50 p_old_personid IN NUMBER,
51 p_new_personid IN NUMBER,
52 p_document_type IN VARCHAR2,
53 p_document_no_from IN VARCHAR2,
54 p_document_no_to IN VARCHAR2,
55 p_date_from IN DATE,
56 p_date_to IN DATE,
57 p_commit_interval IN NUMBER,
58 p_msg_data OUT NOCOPY VARCHAR2,
59 p_msg_count OUT NOCOPY NUMBER,
60 p_return_status OUT NOCOPY VARCHAR2) IS
61
62 l_progress VARCHAR2(3) := '000';
63 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Do_Update';
64 l_return_status VARCHAR2(1);
65
66 BEGIN
67
68 IF g_debug_stmt THEN
69
70 PO_DEBUG.debug_begin(l_log_head);
71 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
72 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid', p_old_personid);
73 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
74 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
75 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
76 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
77 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
78 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
79
80 END IF;
81
82 SAVEPOINT Do_Update_SP;
83
84 l_progress := '001';
85
86 IF (p_update_person = 'PREPARER' OR p_update_person = 'ALL') THEN
87
88 BEGIN
89
90 l_progress := '002';
91
92 IF g_debug_stmt THEN
93
94 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
95
96 END IF;
97
98 SAVEPOINT PO_Mass_Update_Preparer_SP;
99
100 Update_Preparer(p_update_person,
101 p_old_personid,
102 p_new_personid,
103 p_document_type,
104 p_document_no_from,
105 p_document_no_to,
106 p_date_from,
107 p_date_to,
108 p_commit_interval,
109 p_msg_data,
110 p_msg_count,
111 l_return_status);
112
113 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
114 ROLLBACK TO PO_Mass_Update_Preparer_SP;
115 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
116 END IF;
117
118 EXCEPTION
119
120 WHEN OTHERS THEN
121
122 ROLLBACK TO PO_Mass_Update_Preparer_SP;
123
124 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
125
126 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress || SQLCODE || SUBSTR(SQLERRM,1,200));
127
128 END IF;
129
130 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131
132 FND_MSG_PUB.Count_And_Get(
133 p_count => p_msg_count,
134 p_data => p_msg_data);
135
136 END;
137
138 END IF;
139
140 IF (p_update_person = 'APPROVER' OR p_update_person = 'ALL') THEN
141
142 BEGIN
143
144 l_progress := '003';
145
146 IF g_debug_stmt THEN
147
148 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
149
150 END IF;
151
152 SAVEPOINT PO_Mass_Update_Approver_SP;
153
154 Update_Approver(p_update_person,
155 p_old_personid,
156 p_new_personid,
157 p_document_type,
158 p_document_no_from,
159 p_document_no_to,
160 p_date_from,
161 p_date_to,
162 p_commit_interval,
163 p_msg_data,
164 p_msg_count,
165 l_return_status);
166
167 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
168 ROLLBACK TO PO_Mass_Update_Approver_SP;
169 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170 END IF;
171
172 EXCEPTION
173
174 WHEN OTHERS THEN
175
176 ROLLBACK TO PO_Mass_Update_Approver_SP;
177
178 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
179
180 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress || SQLCODE || SUBSTR(SQLERRM,1,200));
181
182 END IF;
183
184 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
185
186 FND_MSG_PUB.Count_And_Get(
187 p_count => p_msg_count,
188 p_data => p_msg_data);
189
190 END;
191
192 END IF;
193
194 IF (p_update_person = 'REQUESTOR' OR p_update_person = 'ALL') THEN
195
196 BEGIN
197
198 l_progress := '004';
199
200 IF g_debug_stmt THEN
201
202 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
203
204 END IF;
205
206 SAVEPOINT PO_Mass_Update_Requestor_SP;
207
208 Update_Requestor(p_update_person,
209 p_old_personid,
210 p_new_personid,
211 p_document_type,
212 p_document_no_from,
213 p_document_no_to,
214 p_date_from,
215 p_date_to,
216 p_commit_interval,
217 p_msg_data,
218 p_msg_count,
219 l_return_status);
220
221 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
222 ROLLBACK TO PO_Mass_Update_Requestor_SP;
223 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 END IF;
225
226
227
228 EXCEPTION
229
230 WHEN OTHERS THEN
231
232 ROLLBACK TO PO_Mass_Update_Requestor_SP;
233
234 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
235
236 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress || SQLCODE || SUBSTR(SQLERRM,1,200));
237
238 END IF;
239
240 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241
242 FND_MSG_PUB.Count_And_Get(
243 p_count => p_msg_count,
244 p_data => p_msg_data);
245
246 END;
247
248 END IF;
249
250 EXCEPTION
251
252 WHEN OTHERS THEN
253
254 ROLLBACK TO Do_Update_SP;
255
256 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
257
258 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress || SQLCODE || SUBSTR(SQLERRM,1,200));
259
260 END IF;
261
262 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263
264 FND_MSG_PUB.Count_And_Get(
265 p_count => p_msg_count,
266 p_data => p_msg_data);
267
268 END DO_Update;
269
270 --------------------------------------------------------------------------------------------------
271 -- Start of Comments
272
273 -- API Name : Update_Preparer
274 -- Type : Private
275 -- Pre-reqs : None
276 -- Function : Updates the old preparer with the new preparer provided and also updates the
277 -- worklfow attributes when the requisitions are in Inprocess and Pre-approved
278 -- status.
279
280 -- Parameters :
281
282 -- IN : p_update_person Person needs to be updated(Preparer/Approver/Requestor).
283 -- p_old_personid Id of the old person.
284 -- p_new_personid Id of the new person.
285 -- p_document_type Type of the document(INTERNAL AND PURCHASE).
286 -- p_document_no_from Document number from.
287 -- p_document_no_to Document number to.
288 -- p_date_from Date from.
289 -- p_date_to Date to.
290 -- p_commit_interval Commit interval.
291
292 -- OUT : p_msg_data Actual message in encoded format.
293 -- p_msg_count Holds the number of messages in the API list.
294 -- p_return_status Return status of the API (Includes 'S','E','U').
295
296 -- End of Comments
297 --------------------------------------------------------------------------------------------------
298
299 PROCEDURE Update_Preparer (p_update_person IN VARCHAR2,
300 p_old_personid IN NUMBER,
301 p_new_personid IN NUMBER,
302 p_document_type IN VARCHAR2,
303 p_document_no_from IN VARCHAR2,
304 p_document_no_to IN VARCHAR2,
305 p_date_from IN DATE,
306 p_date_to IN DATE,
307 p_commit_interval IN NUMBER,
308 p_msg_data OUT NOCOPY VARCHAR2,
309 p_msg_count OUT NOCOPY NUMBER,
310 p_return_status OUT NOCOPY VARCHAR2) IS
311
312 c_req g_req;
313
314 stmt_req VARCHAR2(4000);
315 req_num_type VARCHAR2(100);
316 l_commit_count NUMBER := 0;
317 l_progress VARCHAR2(3) := '000';
318 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Update_Preparer';
319
320 -- Cursor Output Variables.
321 l_req_rowid ROWID;
322 l_req_num po_requisition_headers.segment1%TYPE;
323 l_doc_type po_document_types_all.type_name%TYPE;
324 l_auth_status po_headers.authorization_status%TYPE;
325 l_itemtype wf_items.item_type%TYPE;
326 l_itemkey wf_items.item_key%TYPE;
327
328 -- Local Variables used in the same procedure.
329
330 l_preparer_id NUMBER;
331 l_buyer_user_id NUMBER;
332 l_forward_from_id NUMBER;
333 l_msg15 VARCHAR2(240);
334
335 BEGIN
336
337 g_old_personid := p_old_personid;
338 g_document_type := p_document_type;
339 g_document_no_from := p_document_no_from;
340 g_document_no_to := p_document_no_to;
341 g_date_from := p_date_from;
342 g_date_to := p_date_to;
343
344 IF g_debug_stmt THEN
345
346 PO_DEBUG.debug_begin(l_log_head);
347 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
348 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid', p_old_personid);
349 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
350 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
351 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
352 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
353 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
354 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
355
356 END IF;
357
358 SAVEPOINT PO_Mass_Update_Preparer_SP;
359
360 l_progress := '001';
361
362 IF g_debug_stmt THEN
363
364 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Preparer_Info','Before Calling Preparer_Info' );
365
366 END IF;
367
368 BEGIN
369
370 SAVEPOINT Update_Preparer_SP;
371
372 Preparer_Info(p_old_personid,
373 p_new_personid,
374 p_old_preparer_name,
375 p_new_preparer_name,
376 p_old_username,
377 p_new_username,
378 p_new_user_display_name,
379 p_org_name,
380 p_msg_data,
381 p_msg_count,
382 p_return_status);
383
384 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
385 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386 RETURN;
387 END IF;
388
389
390 l_progress := '002';
391
392 IF g_debug_stmt THEN
393
394 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Print_Output','Before Calling Print_Output' );
395
396 END IF;
397
398 IF (p_update_person = 'PREPARER' OR p_update_person = 'ALL') THEN
399
400 Print_Output(p_update_person,
401 p_old_preparer_name,
402 p_new_preparer_name,
403 p_org_name,
404 p_document_type,
405 p_document_no_from,
406 p_document_no_to,
407 p_date_from,
408 p_date_to,
409 p_msg_data,
410 p_msg_count,
411 p_return_status);
412
413 END IF;
414
415 SELECT manual_req_num_type
416 INTO req_num_type
417 FROM po_system_parameters;
418
419 fnd_message.set_name('PO','PO_MUB_MSG_PREPARER');
420 l_msg15 := fnd_message.get;
421
422 l_progress := '003';
423
424 IF g_debug_stmt THEN
425
426 PO_DEBUG.debug_var(l_log_head,l_progress,'req_num_type',req_num_type );
427
428 END IF;
429
430 EXCEPTION
431
432 WHEN OTHERS THEN
433
434 ROLLBACK TO Update_Preparer_SP;
435
436 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
437
438 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress || SQLCODE || SUBSTR(SQLERRM,1,200));
439
440 END IF;
441
442 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443
444 FND_MSG_PUB.Count_And_Get(
445 p_count => p_msg_count,
446 p_data => p_msg_data);
447
448 END;
449
450 stmt_req := 'SELECT por.ROWID,
451 por.segment1,
452 pdt.type_name,
453 por.authorization_status,
454 por.wf_item_type,
455 por.wf_item_key
456 FROM po_requisition_headers por,
457 po_document_types_vl pdt
458 WHERE por.preparer_id = PO_MASS_UPDATE_REQ_PVT.get_old_personid
459 AND pdt.document_type_code IN (''REQUISITION'')
460 AND pdt.document_subtype = por.type_lookup_code
461 AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'',''RETURNED'')
462 AND Nvl(por.cancel_flag,''N'') = ''N''';
463
464 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
465
466 stmt_req := stmt_req || ' AND por.type_lookup_code = PO_MASS_UPDATE_REQ_PVT.get_document_type';
467 END IF;
468
469
470 IF ( req_num_type = 'NUMERIC' ) THEN
471
472 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
473
474 stmt_req := stmt_req || ' AND 1 = 1 ';
475
476 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
477
478 stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_from)';
479
480 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
481
482 stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_to)';
483
484 ELSE
485
486 stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL )
487
488 BETWEEN to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_to )';
489
490 END IF;
491
492 ELSE
493
494 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
495
496 stmt_req := stmt_req || ' AND 1 = 1 ';
497
498 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
499
500 stmt_req := stmt_req || ' AND POR.SEGMENT1 >= PO_MASS_UPDATE_REQ_PVT.get_document_no_from';
501
502 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
503
504 stmt_req := stmt_req || ' AND POR.SEGMENT1 <= PO_MASS_UPDATE_REQ_PVT.get_document_no_to';
505
506 ELSE
507
508 stmt_req := stmt_req || ' AND POR.SEGMENT1 BETWEEN PO_MASS_UPDATE_REQ_PVT.get_document_no_from AND PO_MASS_UPDATE_REQ_PVT.get_document_no_to';
509
510 END IF;
511
512 END IF; /* req_num_type = 'NUMERIC' */
513
514 /* Bug 6899092 Added Trunc condition in validating the date ranges */
515
516 IF p_date_from IS NULL AND p_date_to IS NULL THEN
517
518 stmt_req := stmt_req || ' AND 1 = 1 ';
519
520 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
521
522 stmt_req := stmt_req || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_from)';
523
524 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
525
526 stmt_req := stmt_req || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_to)';
527
528 ELSE
529 stmt_req := stmt_req || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_from)
530 AND POR.creation_date < Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_to)+1';
531
532 END IF;
533
534 stmt_req := stmt_req || ' ORDER BY por.segment1';
535
536 IF (p_document_type IS NULL OR p_document_type IN ('PURCHASE','INTERNAL','ALL')) THEN -- <BUG 6988269>
537
538 OPEN c_req for stmt_req;
539
540 LOOP
541
542 FETCH c_req INTO l_req_rowid,
543 l_req_num,
544 l_doc_type,
545 l_auth_status,
546 l_itemtype,
547 l_itemkey;
548
549 EXIT when c_req%NOTFOUND;
550
551 BEGIN
552
553 SAVEPOINT Update_Preparer_RECREQ_SP;
554
555 l_progress := '004';
556
557 IF g_debug_stmt THEN
558
559 PO_DEBUG.debug_var(l_log_head,l_progress,'l_req_rowid',l_req_rowid );
560 PO_DEBUG.debug_var(l_log_head,l_progress,'l_req_num',l_req_num );
561 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type );
562 PO_DEBUG.debug_var(l_log_head,l_progress,'l_auth_status',l_auth_status );
563 PO_DEBUG.debug_var(l_log_head,l_progress,'l_itemtype',l_itemtype );
564 PO_DEBUG.debug_var(l_log_head,l_progress,'l_itemkey',l_itemkey );
565
566 END IF;
567
568 UPDATE po_requisition_headers_all
569 SET preparer_id = p_new_personid,
570 last_update_date = sysdate,
571 last_updated_by = fnd_global.user_id,
572 last_update_login = fnd_global.login_id
573 WHERE rowid = l_req_rowid;
574
575 l_progress := '005';
576
577 IF g_debug_stmt THEN
578
579 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
580
581 END IF;
582
583 IF ( (l_auth_status='PRE-APPROVED') OR (l_auth_status='IN PROCESS') ) THEN
584
585 l_preparer_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
586 itemkey => l_itemkey,
587 aname => 'PREPARER_ID');
588
589 IF (l_preparer_id = p_old_personid) THEN
590
591 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
592 itemkey => l_itemkey,
593 aname => 'PREPARER_USER_NAME' ,
594 avalue => p_new_username);
595
596 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
597 itemkey => l_itemkey,
598 aname => 'PREPARER_DISPLAY_NAME' ,
599 avalue => p_new_user_display_name);
600
601 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
602 itemkey => l_itemkey,
603 aname => 'PREPARER_ID' ,
604 avalue => p_new_personid);
605
606 END IF;
607
608 l_forward_from_id := po_wf_util_pkg.GetItemAttrText ( itemtype => l_itemtype,
609 itemkey => l_itemkey,
610 aname => 'FORWARD_FROM_ID');
611
612
613 IF (l_forward_from_id = p_old_personid) THEN
614
615 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
616 itemkey => l_itemkey,
617 aname => 'FORWARD_FROM_DISP_NAME' ,
618 avalue => p_new_user_display_name);
619
620 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
621 itemkey => l_itemkey,
622 aname => 'FORWARD_FROM_ID' ,
623 avalue => p_new_personid);
624
625 po_wf_util_pkg.SetItemAttrText ( itemtype => l_itemtype,
626 itemkey => l_itemkey,
627 aname => 'FORWARD_FROM_USER_NAME' ,
628 avalue => p_new_username);
629
630 END IF;
631
632 END IF; /* End of l_auth_status = 'PRE APPROVED' */
633
634 l_commit_count := l_commit_count + 1;
635
636 IF l_commit_count = p_commit_interval THEN
637
638 COMMIT;
639 l_commit_count := 0;
640
641 END IF;
642
643 l_progress := '006';
644
645 IF g_debug_stmt THEN
646
647 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
648
649 END IF;
650
651 fnd_file.put_line(fnd_file.output, rpad(l_req_num,26) || RPad(l_doc_type,26) || l_msg15);
652
653 EXCEPTION
654
655 WHEN OTHERS THEN
656
657 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
658
659 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
660
661 END IF;
662
663 ROLLBACK TO Update_Preparer_RECREQ_SP;
664
665 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
666
667 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
668
669 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
670
671 END IF;
672
673 END;
674
675 END LOOP;
676
677 CLOSE c_req;
678
679 END IF; -- <End of p_document_type>
680
681 EXCEPTION
682
683 WHEN OTHERS THEN
684
685 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
686
687 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
688
689 END IF;
690
691 ROLLBACK TO PO_Mass_Update_Buyer_SP;
692
693 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
694
695 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
696
697 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
698
699 END IF;
700
701 END Update_Preparer;
702
703 --------------------------------------------------------------------------------------------------
704 -- Start of Comments
705
706 -- API Name : Update_Approver
707 -- Type : Private
708 -- Pre-reqs : None
709 -- Function : Updates the old approver with the new approver provided and also forwards
710 -- the notification from old approver to new approver in case of In process
711 -- and Pre-approved requisitions.
712
713 -- Parameters :
714
715 -- IN : p_update_person Person needs to be updated(Preparer/Approver/Requestor).
716 -- p_old_personid Id of the old person.
717 -- p_new_personid Id of the new person.
718 -- p_document_type Type of the document(INTERNAL AND PURCHASE).
719 -- p_document_no_from Document number from.
720 -- p_document_no_to Document number to.
721 -- p_date_from Date from.
722 -- p_date_to Date to.
723 -- p_commit_interval Commit interval.
724
725 -- OUT : p_msg_data Actual message in encoded format.
726 -- p_msg_count Holds the number of messages in the API list.
727 -- p_return_status Return status of the API (Includes 'S','E','U').
728
729 -- End of Comments
730 --------------------------------------------------------------------------------------------------
731
732 PROCEDURE Update_Approver(p_update_person IN VARCHAR2,
733 p_old_personid IN NUMBER,
734 p_new_personid IN NUMBER,
735 p_document_type IN VARCHAR2,
736 p_document_no_from IN VARCHAR2,
737 p_document_no_to IN VARCHAR2,
738 p_date_from IN DATE,
739 p_date_to IN DATE,
740 p_commit_interval IN NUMBER,
741 p_msg_data OUT NOCOPY VARCHAR2,
742 p_msg_count OUT NOCOPY NUMBER,
743 p_return_status OUT NOCOPY VARCHAR2) IS
744
745 c_req_approver g_req_approver;
746 stmt_req VARCHAR2(4000);
747 --Bug 14393408 Start
748 stmt_req1 VARCHAR2(4000);
749 stmt_req2 VARCHAR2(4000);
750 --Bug 14393408 End
751 l_req_rowid ROWID;
752 l_req_num po_requisition_headers.segment1%TYPE;
753 req_num_type VARCHAR2(100);
754 l_commit_count NUMBER := 0;
755 l_progress VARCHAR2(3) := '000';
756 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Update_Approver';
757 l_doc_type po_document_types_all.type_name%TYPE;
758 l_auth_status po_headers.authorization_status%TYPE;
759 l_notification_id wf_notifications.notification_id%TYPE;
760 l_msg16 VARCHAR2(240);
761 l_msg17 VARCHAR2(240);
762
763 BEGIN
764
765 --package variable intialization
766
767 g_old_personid := p_old_personid;
768 g_document_type := p_document_type;
769 g_document_no_from := p_document_no_from;
770 g_document_no_to := p_document_no_to;
771 g_date_from := p_date_from;
772 g_date_to := p_date_to;
773
774 IF g_debug_stmt THEN
775
776 PO_DEBUG.debug_begin(l_log_head);
777 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
778 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid', p_old_personid);
779 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
780 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
781 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
782 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
783 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
784 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
785
786 END IF;
787
788 SAVEPOINT PO_Mass_Update_Approver_SP;
789
790 l_progress := '001';
791
792 IF g_debug_stmt THEN
793
794 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Preparer_Info','Before Calling Preparer_Info' );
795
796 END IF;
797
798 BEGIN
799
800 SAVEPOINT Update_Approver_SP;
801
802 Preparer_Info(p_old_personid,
803 p_new_personid,
804 p_old_preparer_name,
805 p_new_preparer_name,
806 p_old_username,
807 p_new_username,
808 p_new_user_display_name,
809 p_org_name,
810 p_msg_data,
811 p_msg_count,
812 p_return_status);
813
814 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
815 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
816 RETURN;
817 END IF;
818
819
820 g_old_username := p_old_username;
821
822
823 l_progress := '002';
824
825 IF g_debug_stmt THEN
826
827 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Print_Output','Before Calling Print_Output' );
828
829 END IF;
830
831 IF (p_update_person = 'APPROVER' ) THEN
832
833 Print_Output(p_update_person,
834 p_old_preparer_name,
835 p_new_preparer_name,
836 p_org_name,
837 p_document_type,
838 p_document_no_from,
839 p_document_no_to,
840 p_date_from,
841 p_date_to,
842 p_msg_data,
843 p_msg_count,
844 p_return_status);
845
846
847 END IF;
848
849 SELECT manual_req_num_type
850 INTO req_num_type
851 FROM po_system_parameters;
852
853 fnd_message.set_name('PO','PO_MUB_MSG_NEW_APPROVER');
854 l_msg16 := fnd_message.get;
855
856 fnd_message.set_name('PO','PO_MUB_MSG_APPROVER');
857 l_msg17 := fnd_message.get;
858
859 l_progress := '003';
860
861 IF g_debug_stmt THEN
862
863 PO_DEBUG.debug_var(l_log_head,l_progress,'req_num_type',req_num_type );
864
865 END IF;
866
867 EXCEPTION
868
869 WHEN OTHERS THEN
870
871 ROLLBACK TO Update_Approver_SP;
872
873 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
874
875 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress || SQLCODE || SUBSTR(SQLERRM,1,200));
876
877 END IF;
878
879 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
880
881 FND_MSG_PUB.Count_And_Get(
882 p_count => p_msg_count,
883 p_data => p_msg_data);
884
885 END;
886
887
888 --Bug 14393408 Start
889 --Changed the query for bug 14393408 to handle AME Approval
890 stmt_req1 := 'SELECT wfn.notification_id,
891 por.segment1,
892 pdt.type_name
893 FROM wf_notifications wfn,
894 wf_item_activity_statuses wfa,
895 po_requisition_headers por,
896 po_document_types_vl pdt,
897 wf_items wfi
898 WHERE wfn.notification_id = wfa.notification_id
899 AND wfi.item_key = wfa.item_key
900 and wfi.item_type=wfa.item_type
901 AND por.wf_item_type=wfi.parent_item_type
902 AND por.wf_item_key=wfi.parent_item_key
903 and wfi.parent_item_key is not null
904 AND wfn.status NOT IN (''CLOSED'',''CANCELED'')
905 AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''IN PROCESS'',''PRE-APPROVED'')
906 AND wfn.recipient_role = PO_Mass_Update_Req_PVT.get_old_username
907 AND pdt.document_type_code in (''REQUISITION'')
908 AND pdt.document_subtype = por.type_lookup_code';
909
910 stmt_req2 := 'SELECT wfn.notification_id,
911 por.segment1,
912 pdt.type_name
913 FROM wf_notifications wfn,
914 wf_item_activity_statuses wfa,
915 po_requisition_headers por,
916 po_document_types_vl pdt
917 WHERE wfn.notification_id = wfa.notification_id
918 AND wfa.item_type = por.wf_item_type
919 AND wfa.item_key = por.wf_item_key
920 AND wfn.status NOT IN (''CLOSED'',''CANCELED'')
921 AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''IN PROCESS'',''PRE-APPROVED'')
922 AND wfn.recipient_role = PO_Mass_Update_Req_PVT.get_old_username
923 AND pdt.document_type_code in (''REQUISITION'')
924 AND pdt.document_subtype = por.type_lookup_code';
925
926 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
927
928 stmt_req1 := stmt_req1 || ' AND por.type_lookup_code = PO_Mass_Update_Req_PVT.get_document_type';
929 stmt_req2 := stmt_req2 || ' AND por.type_lookup_code = PO_Mass_Update_Req_PVT.get_document_type';
930 END IF;
931
932 IF ( req_num_type = 'NUMERIC' ) THEN
933
934 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
935
936 stmt_req1 := stmt_req1 || ' AND 1 = 1 ';
937 stmt_req2 := stmt_req2 || ' AND 1 = 1 ';
938
939 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
940
941 stmt_req1 := stmt_req1 || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) >= to_number(PO_Mass_Update_Req_PVT.get_document_no_from)';
942 stmt_req2 := stmt_req2 || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) >= to_number(PO_Mass_Update_Req_PVT.get_document_no_from)';
943
944 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
945
946 stmt_req1 := stmt_req1 || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) <= to_number(PO_Mass_Update_Req_PVT.get_document_no_to)';
947 stmt_req2 := stmt_req2 || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) <= to_number(PO_Mass_Update_Req_PVT.get_document_no_to)';
948
949 ELSE
950
951 stmt_req1 := stmt_req1 || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL )
952
953 BETWEEN to_number(PO_Mass_Update_Req_PVT.get_document_no_from) AND to_number(PO_Mass_Update_Req_PVT.get_document_no_to)';--Bug 12652093, removed '||'
954
955 stmt_req2 := stmt_req2 || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL )
956
957 BETWEEN to_number(PO_Mass_Update_Req_PVT.get_document_no_from) AND to_number(PO_Mass_Update_Req_PVT.get_document_no_to)';
958
959 END IF;
960
961 ELSE
962
963 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
964
965 stmt_req1 := stmt_req1|| ' AND 1 = 1 ';
966 stmt_req2 := stmt_req2 || ' AND 1 = 1 ';
967
968 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
969
970 stmt_req1 := stmt_req1 || ' AND POR.SEGMENT1 >= PO_Mass_Update_Req_PVT.get_document_no_from';
971 stmt_req2 := stmt_req2 || ' AND POR.SEGMENT1 >= PO_Mass_Update_Req_PVT.get_document_no_from';
972
973 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
974
975 stmt_req1 := stmt_req1 || ' AND POR.SEGMENT1 <= PO_Mass_Update_Req_PVT.get_document_no_to';
976 stmt_req2 := stmt_req2 || ' AND POR.SEGMENT1 <= PO_Mass_Update_Req_PVT.get_document_no_to';
977
978 ELSE
979
980 stmt_req1 := stmt_req1 || ' AND POR.SEGMENT1 BETWEEN PO_Mass_Update_Req_PVT.get_document_no_from AND PO_Mass_Update_Req_PVT.get_document_no_to';
981 stmt_req2 := stmt_req2 || ' AND POR.SEGMENT1 BETWEEN PO_Mass_Update_Req_PVT.get_document_no_from AND PO_Mass_Update_Req_PVT.get_document_no_to';
982
983 END IF;
984
985 END IF; /* req_num_type = 'NUMERIC' */
986
987 /* Bug 6899092 Added Trunc condition in validating the date ranges */
988
989 IF p_date_from IS NULL AND p_date_to IS NULL THEN
990
991 stmt_req1 := stmt_req1 || ' AND 1 = 1 ';
992 stmt_req2 := stmt_req2 || ' AND 1 = 1 ';
993
994 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
995
996 stmt_req1 := stmt_req1 || ' AND POR.creation_date >= Trunc(PO_Mass_Update_Req_PVT.get_date_from)';
997 stmt_req2 := stmt_req2 || ' AND POR.creation_date >= Trunc(PO_Mass_Update_Req_PVT.get_date_from)';
998
999 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
1000
1001 stmt_req1 := stmt_req1 || ' AND POR.creation_date <= Trunc(PO_Mass_Update_Req_PVT.get_date_to)';
1002 stmt_req2 := stmt_req2 || ' AND POR.creation_date <= Trunc(PO_Mass_Update_Req_PVT.get_date_to)';
1003
1004 ELSE
1005 stmt_req1 := stmt_req1 || ' AND POR.creation_date >= Trunc(PO_Mass_Update_Req_PVT.get_date_from)
1006 AND POR.creation_date < Trunc(PO_Mass_Update_Req_PVT.get_date_to)+1';
1007
1008 stmt_req2 := stmt_req2 || ' AND POR.creation_date >= Trunc(PO_Mass_Update_Req_PVT.get_date_from)
1009 AND POR.creation_date < Trunc(PO_Mass_Update_Req_PVT.get_date_to)+1';
1010
1011 END IF;
1012
1013 stmt_req := stmt_req1 || ' UNION ALL ' || stmt_req2 || ' ORDER BY 2';
1014 --Bug 14393408 End
1015
1016 IF (p_document_type IS NULL OR p_document_type IN ('PURCHASE','INTERNAL','ALL')) THEN -- <BUG 6988269>
1017
1018 OPEN c_req_approver for stmt_req;
1019
1020 LOOP
1021
1022 FETCH c_req_approver INTO l_notification_id,
1023 l_req_num,
1024 l_doc_type;
1025
1026 EXIT WHEN c_req_approver%NOTFOUND;
1027
1028 l_progress := '004';
1029
1030 IF g_debug_stmt THEN
1031
1032 PO_DEBUG.debug_var(l_log_head,l_progress,'l_notification_id',l_notification_id );
1033 PO_DEBUG.debug_var(l_log_head,l_progress,'l_req_num',l_req_num );
1034 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type', l_doc_type);
1035
1036 END IF;
1037
1038 BEGIN
1039
1040 SAVEPOINT Update_Req_Forward_SP;
1041
1042 l_progress := '005';
1043
1044 Wf_Notification.Forward(l_notification_id, p_new_username,l_msg16);
1045
1046 IF g_debug_stmt THEN
1047
1048 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_username',p_new_username );
1049
1050 END IF;
1051
1052 l_commit_count := l_commit_count + 1;
1053
1054 IF l_commit_count = p_commit_interval THEN
1055
1056 COMMIT;
1057 l_commit_count := 0;
1058
1059 END IF;
1060
1061 l_progress := '006';
1062
1063 IF g_debug_stmt THEN
1064
1065 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
1066
1067 END IF;
1068
1069 fnd_file.put_line(fnd_file.output, rpad(l_req_num,26) || RPad(l_doc_type,26) || l_msg17);
1070
1071 EXCEPTION
1072
1073 WHEN OTHERS THEN
1074
1075 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1076
1077 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1078
1079 END IF;
1080
1081 ROLLBACK TO Update_Req_Forward_SP;
1082
1083 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1084
1085 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1086
1087 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1088
1089 END IF;
1090
1091 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1092
1093 END;
1094
1095
1096 END LOOP;
1097
1098 CLOSE c_req_approver;
1099
1100 END IF; -- <End of p_document_type>
1101
1102 EXCEPTION
1103
1104 WHEN OTHERS THEN
1105
1106 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1107
1108 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1109
1110 END IF;
1111
1112 ROLLBACK TO PO_Mass_Update_Approver_SP;
1113
1114 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1115
1116 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1117
1118 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1119
1120 END IF;
1121
1122 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1123
1124 END Update_Approver;
1125
1126 --------------------------------------------------------------------------------------------------
1127 -- Start of Comments
1128
1129 -- API Name : Update_Requestor
1130 -- Type : Private
1131 -- Pre-reqs : None
1132 -- Function : Updates the old requestor with the new requestor provided.
1133
1134 -- Parameters :
1135
1136 -- IN : p_update_person Person needs to be updated(Preparer/Approver/Requestor).
1137 -- p_old_personid Id of the old person.
1138 -- p_new_personid Id of the new person.
1139 -- p_document_type Type of the document(INTERNAL AND PURCHASE).
1140 -- p_document_no_from Document number from.
1141 -- p_document_no_to Document number to.
1142 -- p_date_from Date from.
1143 -- p_date_to Date to.
1144 -- p_commit_interval Commit interval.
1145
1146 -- OUT : p_msg_data Actual message in encoded format.
1147 -- p_msg_count Holds the number of messages in the API list.
1148 -- p_return_status Return status of the API (Includes 'S','E','U').
1149
1150 -- End of Comments
1151 --------------------------------------------------------------------------------------------------
1152
1153 PROCEDURE Update_Requestor(p_update_person IN VARCHAR2,
1154 p_old_personid IN NUMBER,
1155 p_new_personid IN NUMBER,
1156 p_document_type IN VARCHAR2,
1157 p_document_no_from IN VARCHAR2,
1158 p_document_no_to IN VARCHAR2,
1159 p_date_from IN DATE,
1160 p_date_to IN DATE,
1161 p_commit_interval IN NUMBER,
1162 p_msg_data OUT NOCOPY VARCHAR2,
1163 p_msg_count OUT NOCOPY NUMBER,
1164 p_return_status OUT NOCOPY VARCHAR2) IS
1165
1166 c_req g_req;
1167 stmt_req VARCHAR2(4000);
1168 req_num_type VARCHAR2(100);
1169 l_req_rowid ROWID;
1170 l_req_num po_requisition_headers.segment1%TYPE;
1171 l_commit_count NUMBER := 0;
1172 l_progress VARCHAR2(3) := '000';
1173 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Update_Requestor';
1174 l_doc_type po_document_types_all.type_name%TYPE;
1175 l_auth_status po_headers.authorization_status%TYPE;
1176 l_msg18 VARCHAR2(240);
1177 --Bug 14153104
1178 l_requester_email po_requisition_lines.requester_email%TYPE;
1179 l_requester_phone po_requisition_lines.requester_phone%TYPE;
1180 l_requester_fax po_requisition_lines.requester_fax%TYPE;
1181
1182 BEGIN
1183
1184 -- Intializing package variables
1185
1186 g_old_personid := p_old_personid;
1187 g_document_type := p_document_type;
1188 g_document_no_from := p_document_no_from;
1189 g_document_no_to := p_document_no_to;
1190 g_date_from := p_date_from;
1191 g_date_to := p_date_to;
1192
1193 IF g_debug_stmt THEN
1194
1195 PO_DEBUG.debug_begin(l_log_head);
1196 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
1197 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid', p_old_personid);
1198 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid', p_new_personid);
1199 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
1200 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
1201 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from',p_date_from);
1202 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to',p_date_to);
1203 PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit_interval',p_commit_interval);
1204
1205 END IF;
1206
1207 SAVEPOINT PO_Mass_Update_Requestor_SP;
1208
1209 l_progress := '001';
1210
1211 IF g_debug_stmt THEN
1212
1213 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Preparer_Info','Before Calling Preparer_Info' );
1214
1215 END IF;
1216
1217 BEGIN
1218
1219 SAVEPOINT Update_Requestor_SP;
1220
1221 Preparer_Info(p_old_personid,
1222 p_new_personid,
1223 p_old_preparer_name,
1224 p_new_preparer_name,
1225 p_old_username,
1226 p_new_username,
1227 p_new_user_display_name,
1228 p_org_name,
1229 p_msg_data,
1230 p_msg_count,
1231 p_return_status);
1232
1233 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1234 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1235 RETURN;
1236 END IF;
1237
1238
1239 l_progress := '002';
1240
1241 IF g_debug_stmt THEN
1242
1243 PO_DEBUG.debug_var(l_log_head,l_progress,'Before Calling Print_Output','Before Calling Print_Output' );
1244
1245 END IF;
1246
1247 IF (p_update_person = 'REQUESTOR' ) THEN
1248
1249 Print_Output(p_update_person,
1250 p_old_preparer_name,
1251 p_new_preparer_name,
1252 p_org_name,
1253 p_document_type,
1254 p_document_no_from,
1255 p_document_no_to,
1256 p_date_from,
1257 p_date_to,
1258 p_msg_data,
1259 p_msg_count,
1260 p_return_status);
1261
1262 END IF;
1263
1264 SELECT manual_req_num_type
1265 INTO req_num_type
1266 FROM po_system_parameters;
1267
1268 fnd_message.set_name('PO','PO_MUB_MSG_REQUESTOR');
1269 l_msg18 := fnd_message.get;
1270
1271 l_progress := '003';
1272
1273 IF g_debug_stmt THEN
1274
1275 PO_DEBUG.debug_var(l_log_head,l_progress,'req_num_type',req_num_type );
1276
1277 END IF;
1278
1279 EXCEPTION
1280
1281 WHEN OTHERS THEN
1282
1283 ROLLBACK TO Update_Requestor_SP;
1284
1285 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1286
1287 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress || SQLCODE || SUBSTR(SQLERRM,1,200));
1288
1289 END IF;
1290
1291 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1292
1293 FND_MSG_PUB.Count_And_Get(
1294 p_count => p_msg_count,
1295 p_data => p_msg_data);
1296
1297 END;
1298
1299
1300
1301 stmt_req := 'SELECT prl.ROWID,
1302 por.segment1,
1303 pdt.type_name
1304 FROM po_requisition_headers por,
1305 po_document_types_vl pdt,
1306 po_requisition_lines_all prl
1307 WHERE prl.to_person_id = PO_MASS_UPDATE_REQ_PVT.get_old_personid
1308 AND por.requisition_header_id = prl.requisition_header_id
1309 AND pdt.document_type_code IN (''REQUISITION'')
1310 AND pdt.document_subtype = por.type_lookup_code
1311 AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'',''RETURNED'')
1312 AND Nvl(por.cancel_flag,''N'') = ''N''';
1313
1314 IF p_document_type IS NOT NULL AND p_document_type <> 'ALL' THEN -- <BUG 6988269>
1315
1316 stmt_req := stmt_req || ' AND por.type_lookup_code = PO_MASS_UPDATE_REQ_PVT.get_document_type';
1317 END IF;
1318
1319 IF ( req_num_type = 'NUMERIC' ) THEN
1320
1321 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
1322
1323 stmt_req := stmt_req || ' AND 1 = 1 ';
1324
1325 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
1326
1327 stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_from';
1328
1329 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
1330
1331 stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_to)';
1332
1333 ELSE
1334
1335 stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL )
1336
1337 BETWEEN to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_to)';
1338
1339 END IF;
1340
1341 ELSE
1342
1343 IF p_document_no_from IS NULL AND p_document_no_to IS NULL THEN
1344
1345 stmt_req := stmt_req || ' AND 1 = 1 ';
1346
1347 ELSIF p_document_no_from IS NOT NULL AND p_document_no_to IS NULL THEN
1348
1349 stmt_req := stmt_req || ' AND POR.SEGMENT1 >= PO_MASS_UPDATE_REQ_PVT.get_document_no_from';
1350
1351 ELSIF p_document_no_from IS NULL AND p_document_no_to IS NOT NULL THEN
1352
1353 stmt_req := stmt_req || ' AND POR.SEGMENT1 <= PO_MASS_UPDATE_REQ_PVT.get_document_no_to';
1354
1355 ELSE
1356
1357 stmt_req := stmt_req || ' AND POR.SEGMENT1 BETWEEN PO_MASS_UPDATE_REQ_PVT.get_document_no_from AND PO_MASS_UPDATE_REQ_PVT.get_document_no_to';
1358
1359 END IF;
1360
1361 END IF; /* req_num_type = 'NUMERIC' */
1362
1363 /* Bug 6899092 Added Trunc condition in validating the date ranges */
1364
1365 IF p_date_from IS NULL AND p_date_to IS NULL THEN
1366
1367 stmt_req := stmt_req || ' AND 1 = 1 ';
1368
1369 ELSIF p_date_from IS NOT NULL AND p_date_to IS NULL THEN
1370
1371 stmt_req := stmt_req || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_from)';
1372
1373 ELSIF p_date_from IS NULL AND p_date_to IS NOT NULL THEN
1374
1375 stmt_req := stmt_req || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_to)';
1376
1377 ELSE
1378 stmt_req := stmt_req || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_from)
1379 AND POR.creation_date < Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_to)+1';
1380
1381 END IF;
1382
1383 stmt_req := stmt_req || ' ORDER BY por.segment1';
1384
1385 IF (p_document_type IS NULL OR p_document_type IN ('PURCHASE','INTERNAL','ALL')) THEN -- <BUG 6988269>
1386 -- Bug 14153104 start
1387 BEGIN
1388 SELECT pap.email_address,
1389 hr_general.get_work_phone(pap.person_id),
1390 hr_general.get_phone_number(pap.person_id, 'WF')
1391 INTO l_requester_email, l_requester_phone, l_requester_fax
1392 FROM per_all_people_f pap,
1393 per_all_assignments_f asgn
1394 WHERE pap.person_id = p_new_personid
1395 AND asgn.person_id = pap.person_id
1396 AND asgn.primary_flag = 'Y'
1397 AND asgn.assignment_type IN ('E','C')
1398 AND pap.effective_end_date in
1399 ( SELECT min(pap2.effective_end_date)
1400 FROM per_all_people_f pap2
1401 WHERE pap2.person_id = pap.person_id
1402 AND TRUNC(sysdate) <= pap2.effective_end_date )
1403 AND asgn.effective_end_date in
1404 ( select min(asgn2.effective_end_date)
1405 from per_all_assignments_f asgn2
1406 where asgn2.person_id = asgn.person_id
1407 and asgn2.primary_flag = 'Y'
1408 and asgn2.assignment_type IN ('E','C')
1409 and TRUNC(sysdate) <= asgn2.effective_end_date);
1410 EXCEPTION
1411 WHEN NO_DATA_FOUND THEN
1412 l_requester_email := null;
1413 l_requester_phone := null;
1414 l_requester_fax := null;
1415 END;
1416 -- Bug 14153104 end
1417 OPEN c_req for stmt_req;
1418
1419 LOOP
1420
1421 FETCH c_req INTO l_req_rowid,
1422 l_req_num,
1423 l_doc_type;
1424
1425 EXIT when c_req%NOTFOUND;
1426
1427 BEGIN
1428
1429 SAVEPOINT Update_Requestor_RECREQ_SP;
1430
1431 l_progress := '004';
1432
1433 IF g_debug_stmt THEN
1434
1435 PO_DEBUG.debug_var(l_log_head,l_progress,'l_req_rowid',l_req_rowid );
1436 PO_DEBUG.debug_var(l_log_head,l_progress,'l_req_num',l_req_num );
1437 PO_DEBUG.debug_var(l_log_head,l_progress,'l_doc_type',l_doc_type );
1438
1439 END IF;
1440
1441 UPDATE po_requisition_lines_all
1442 SET to_person_id = p_new_personid,
1443 requester_email = l_requester_email,-- Bug 14153104
1444 requester_phone = l_requester_phone,
1445 requester_fax = l_requester_fax,
1446 last_update_date = sysdate,
1447 last_updated_by = fnd_global.user_id,
1448 last_update_login = fnd_global.login_id
1449 WHERE rowid = l_req_rowid;
1450
1451
1452 l_progress := '005';
1453
1454 IF g_debug_stmt THEN
1455
1456 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
1457
1458 END IF;
1459
1460
1461 l_commit_count := l_commit_count + 1;
1462
1463 IF l_commit_count = p_commit_interval THEN
1464
1465 COMMIT;
1466 l_commit_count := 0;
1467
1468 END IF;
1469
1470 l_progress := '006';
1471
1472 IF g_debug_stmt THEN
1473
1474 PO_DEBUG.debug_var(l_log_head,l_progress,'l_commit_count',l_commit_count );
1475
1476 END IF;
1477
1478 fnd_file.put_line(fnd_file.output, rpad(l_req_num,26) || rpad(l_doc_type,26) || l_msg18 );
1479
1480 EXCEPTION
1481
1482 WHEN OTHERS THEN
1483
1484 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1485
1486 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1487
1488 END IF;
1489
1490 ROLLBACK TO Update_Requestor_RECREQ_SP;
1491
1492 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1493
1494 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1495
1496 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1497
1498 END IF;
1499
1500 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1501
1502 END;
1503
1504 END LOOP;
1505
1506 CLOSE c_req;
1507
1508 END IF; -- <End of p_document_type>
1509
1510 EXCEPTION
1511
1512 WHEN OTHERS THEN
1513
1514 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1515
1516 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1517
1518 END IF;
1519
1520 ROLLBACK TO PO_Mass_Update_Requestor_SP;
1521
1522 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1523
1524 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1525
1526 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1527
1528 END IF;
1529
1530 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1531
1532 END Update_Requestor;
1533
1534 --------------------------------------------------------------------------------------------------
1535 -- Start of Comments
1536
1537 -- API Name : Print_Output
1538 -- Type : Private
1539 -- Pre-reqs : None
1540 -- Function : Prints the header and body of the output file showing the documents and
1541 -- document types updated along with the person who have been updated in the
1542 -- document.
1543
1544 -- Parameters :
1545
1546 -- IN : p_update_person Person needs to be updated(Preparer/Approver/Requestor).
1547 -- p_old_preparer_name Preparer name of the old person.
1548 -- p_new_preparer_name Preparer name of the new person.
1549 -- p_org_name Operating unit name.
1550 -- p_document_type Type of the document(INTERNAL AND PURCHASE).
1551 -- p_document_no_from Document number from.
1552 -- p_document_no_to Document number to.
1553 -- p_date_from Date from.
1554 -- p_date_to Date to.
1555
1556 -- OUT : p_msg_data Actual message in encoded format.
1557 -- p_msg_count Holds the number of messages in the API list.
1558 -- p_return_status Return status of the API (Includes 'S','E','U').
1559
1560 -- End of Comments
1561 --------------------------------------------------------------------------------------------------
1562
1563 PROCEDURE Print_Output(p_update_person IN VARCHAR2,
1564 p_old_preparer_name IN VARCHAR2,
1565 p_new_preparer_name IN VARCHAR2,
1566 p_org_name IN VARCHAR2,
1567 p_document_type IN VARCHAR2,
1568 p_document_no_from IN VARCHAR2,
1569 p_document_no_to IN VARCHAR2,
1570 p_date_from IN DATE,
1571 p_date_to IN DATE,
1572 p_msg_data OUT NOCOPY VARCHAR2,
1573 p_msg_count OUT NOCOPY NUMBER,
1574 p_return_status OUT NOCOPY VARCHAR2) IS
1575
1576 l_msg1 VARCHAR2(240);
1577 l_msg2 VARCHAR2(240);
1578 l_msg3 VARCHAR2(240);
1579 l_msg4 VARCHAR2(240);
1580 l_msg5 VARCHAR2(240);
1581 l_msg6 VARCHAR2(240);
1582 l_msg7 VARCHAR2(240);
1583 l_msg8 VARCHAR2(240);
1584 l_msg9 VARCHAR2(240);
1585 l_msg10 VARCHAR2(240);
1586 l_msg11 VARCHAR2(240);
1587 l_msg12 VARCHAR2(240);
1588 l_msg13 VARCHAR2(240);
1589 l_msg14 VARCHAR2(240);
1590
1591 l_progress VARCHAR2(3);
1592 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Print_Output';
1593
1594 BEGIN
1595
1596 l_progress := '000';
1597
1598 IF g_debug_stmt THEN
1599
1600 PO_DEBUG.debug_begin(l_log_head);
1601 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
1602 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_preparer_name',p_old_preparer_name );
1603 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_preparer_name',p_new_preparer_name );
1604 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_name',p_org_name );
1605 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_type',p_document_type );
1606 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_from',p_document_no_from );
1607 PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_no_to',p_document_no_to );
1608 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_from', p_date_from);
1609 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_to', p_date_to);
1610
1611 END IF;
1612
1613 fnd_message.set_name('PO','PO_MUB_MSG_PREPARER_HEADER1');
1614 l_msg1 := fnd_message.get;
1615
1616 fnd_message.set_name('PO','PO_MUB_MSG_DATE');
1617 l_msg2 := fnd_message.get;
1618
1619 fnd_message.set_name('PO','PO_MUB_MSG_OU');
1620 l_msg3 := fnd_message.get;
1621
1622 fnd_message.set_name('PO','PO_MUB_MSG_OLD_PERSON');
1623 l_msg4 := fnd_message.get;
1624
1625
1626 fnd_message.set_name('PO','PO_MUB_MSG_NEW_PERSON');
1627 l_msg5 := fnd_message.get;
1628
1629 fnd_message.set_name('PO','PO_MUB_MSG_DOC_SUB_TYPE');
1630 l_msg6 := fnd_message.get;
1631
1632 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM_FROM');
1633 l_msg7 := fnd_message.get;
1634
1635 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM_TO');
1636 l_msg8 := fnd_message.get;
1637
1638 fnd_message.set_name('PO','PO_MUB_MSG_DATE_FROM');
1639 l_msg9 := fnd_message.get;
1640
1641 fnd_message.set_name('PO','PO_MUB_MSG_DATE_TO');
1642 l_msg10 := fnd_message.get;
1643
1644 SAVEPOINT Print_SP;
1645
1646 IF (p_update_person = 'PREPARER') THEN
1647
1648 fnd_message.set_name('PO','PO_MUB_MSG_PREPARER_HEADER2');
1649 fnd_message.set_token('OLD_PREPARER',p_old_preparer_name);
1650 fnd_message.set_token('NEW_PREPARER',p_new_preparer_name);
1651
1652 ELSIF (p_update_person = 'APPROVER') THEN
1653
1654 fnd_message.set_name('PO','PO_MUB_MSG_PREPARER_HEADER3');
1655 fnd_message.set_token('OLD_APPROVER',p_old_preparer_name);
1656 fnd_message.set_token('NEW_APPROVER',p_new_preparer_name);
1657
1658 ELSIF (p_update_person = 'REQUESTOR') THEN
1659
1660 fnd_message.set_name('PO','PO_MUB_MSG_PREPARER_HEADER4');
1661 fnd_message.set_token('OLD_REQUESTOR',p_old_preparer_name);
1662 fnd_message.set_token('NEW_REQUESTOR',p_new_preparer_name);
1663
1664 ELSIF (p_update_person = 'ALL') THEN
1665
1666 fnd_message.set_name('PO','PO_MUB_MSG_PREPARER_HEADER5');
1667 fnd_message.set_token('OLD_PERSON',p_old_preparer_name);
1668 fnd_message.set_token('NEW_PERSON',p_new_preparer_name);
1669
1670 END IF;
1671
1672 l_progress := '001';
1673
1674 IF g_debug_stmt THEN
1675
1676 PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );
1677
1678 END IF;
1679
1680 l_msg11 := fnd_message.get;
1681
1682 fnd_message.set_name('PO','PO_MUB_MSG_DOC_NUM');
1683 l_msg12 := fnd_message.get;
1684
1685 fnd_message.set_name('PO','PO_MUB_MSG_DOC_TYPE');
1686 l_msg13 := fnd_message.get;
1687
1688 fnd_message.set_name('PO','PO_MUB_MSG_PERSON');
1689 l_msg14 := fnd_message.get;
1690
1691 l_progress := '002';
1692
1693 fnd_file.put_line(fnd_file.output, l_msg1);
1694 fnd_file.put_line(fnd_file.output, ' ');
1695 fnd_file.put_line(fnd_file.output, rpad(l_msg2,21) || ' : ' || sysdate);
1696 fnd_file.put_line(fnd_file.output, rpad(l_msg3,21) || ' : ' || p_org_name);
1697 fnd_file.put_line(fnd_file.output, rpad(l_msg4,21) || ' : ' || p_old_preparer_name);
1698 fnd_file.put_line(fnd_file.output, rpad(l_msg5,21) || ' : ' || p_new_preparer_name);
1699 fnd_file.put_line(fnd_file.output, rpad(l_msg6,21) || ' : ' || p_document_type);
1700 l_progress := '003';
1701 fnd_file.put_line(fnd_file.output, rpad(l_msg7,21) || ' : ' || p_document_no_from);
1702 fnd_file.put_line(fnd_file.output, rpad(l_msg8,21) || ' : ' || p_document_no_to);
1703 fnd_file.put_line(fnd_file.output, rpad(l_msg9,21) || ' : ' || p_date_from);
1704 fnd_file.put_line(fnd_file.output, rpad(l_msg10,21) || ' : ' || p_date_to);
1705 l_progress := '004';
1706 fnd_file.put_line(fnd_file.output, ' ');
1707 fnd_file.put_line(fnd_file.output, l_msg11);
1708 fnd_file.put_line(fnd_file.output, ' ');
1709
1710 fnd_file.put_line(fnd_file.output, rpad(l_msg12,26) || RPad(l_msg13,26) || l_msg14);
1711 fnd_file.put_line(fnd_file.output, rpad('-',60,'-'));
1712
1713
1714 EXCEPTION
1715
1716 WHEN OTHERS THEN
1717
1718 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1719
1720 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1721
1722 END IF;
1723
1724 ROLLBACK TO Print_SP;
1725
1726 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1727
1728 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1729
1730 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1731
1732 END IF;
1733
1734 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1735
1736 END Print_Output;
1737
1738 --------------------------------------------------------------------------------------------------
1739 -- Start of Comments
1740
1741 -- API Name : Preparer_Info
1742 -- Type : Private
1743 -- Pre-reqs : None
1744 -- Function : Gets the Preparer Information.
1745
1746 -- Parameters :
1747
1748 -- IN : p_old_personid Id of the old person.
1749 -- p_new_personid Id of the new person.
1750
1751 -- OUT : p_old_preparer_name Preparer name of the old person.
1752 -- p_new_preparer_name Preparer name of the new person.
1753 -- p_old_username User name of the old person.
1754 -- p_new_username User name of the new person.
1755 -- p_new_user_display_name Display name of the new person.
1756 -- p_org_name Operating unit name.
1757 -- p_msg_data Actual message in encoded format.
1758 -- p_msg_count Holds the number of messages in the API list.
1759 -- p_return_status Return status of the API (Includes 'S','E','U').
1760
1761 -- End of Comments
1762 --------------------------------------------------------------------------------------------------
1763
1764 PROCEDURE Preparer_Info(p_old_personid IN NUMBER,
1765 p_new_personid IN NUMBER,
1766 p_old_preparer_name OUT NOCOPY VARCHAR2,
1767 p_new_preparer_name OUT NOCOPY VARCHAR2,
1768 p_old_username OUT NOCOPY VARCHAR2,
1769 p_new_username OUT NOCOPY VARCHAR2,
1770 p_new_user_display_name OUT NOCOPY VARCHAR2,
1771 p_org_name OUT NOCOPY VARCHAR2,
1772 p_msg_data OUT NOCOPY VARCHAR2,
1773 p_msg_count OUT NOCOPY NUMBER,
1774 p_return_status OUT NOCOPY VARCHAR2) IS
1775
1776 l_progress VARCHAR2(3);
1777 l_log_head CONSTANT VARCHAR2(1000) := g_log_head||'Preparer_Info';
1778 l_org_id NUMBER;
1779
1780 BEGIN
1781
1782 l_progress := '000';
1783
1784 IF g_debug_stmt THEN
1785
1786 PO_DEBUG.debug_begin(l_log_head);
1787 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_personid',p_old_personid );
1788 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_personid',p_new_personid );
1789
1790 END IF;
1791
1792 l_progress := '001';
1793
1794 SAVEPOINT Prep_Info_SP;
1795
1796 p_old_preparer_name := PO_EMPLOYEES_SV.get_emp_name(p_old_personid);
1797
1798 p_new_preparer_name := PO_EMPLOYEES_SV.get_emp_name(p_new_personid);
1799
1800 WF_DIRECTORY.GetUserName('PER',p_old_personid, p_old_username,p_old_user_display_name);
1801
1802 WF_DIRECTORY.GetUserName('PER',p_new_personid, p_new_username,p_new_user_display_name);
1803
1804 SELECT org_id
1805 INTO l_org_id
1806 FROM po_system_parameters;
1807
1808 SELECT hou.name
1809 INTO p_org_name
1810 FROM hr_all_organization_units hou,
1811 hr_all_organization_units_tl hout
1812 WHERE hou.organization_id = hout.organization_id
1813 AND hout.LANGUAGE = UserEnv('LANG')
1814 AND hou.organization_id = l_org_id;
1815
1816 IF g_debug_stmt THEN
1817 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_preparer_name', p_old_preparer_name);
1818 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_preparer_name', p_new_preparer_name);
1819 PO_DEBUG.debug_var(l_log_head,l_progress,'p_old_username', p_old_username);
1820 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_username', p_new_username);
1821 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_user_display_name', p_new_user_display_name);
1822 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_name ', p_org_name);
1823 END IF;
1824
1825 EXCEPTION
1826
1827 WHEN OTHERS THEN
1828
1829 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1830
1831 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_log_head,l_progress|| SQLCODE || SUBSTR(SQLERRM,1,200));
1832
1833 END IF;
1834
1835 ROLLBACK TO Prep_Info_SP;
1836
1837 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1838
1839 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1840
1841 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_log_head );
1842
1843 END IF;
1844
1845 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => p_msg_count, p_data => p_msg_data );
1846
1847 END Preparer_Info;
1848
1849 --------------------------------------------------------------------------------------------------
1850
1851 -- Functions declared to return the value of the parameters passed in this API.
1852
1853 --------------------------------------------------------------------------------------------------
1854
1855 FUNCTION get_old_personid RETURN NUMBER
1856 IS
1857 BEGIN
1858 RETURN g_old_personid;
1859 END;
1860
1861 FUNCTION get_document_type RETURN VARCHAR2
1862 IS
1863 BEGIN
1864 RETURN g_document_type;
1865 END;
1866
1867 FUNCTION get_document_no_from RETURN VARCHAR2
1868 IS
1869 BEGIN
1870 RETURN g_document_no_from;
1871 END;
1872
1873 FUNCTION get_document_no_to RETURN VARCHAR2
1874 IS
1875 BEGIN
1876 RETURN g_document_no_to;
1877 END;
1878
1879 FUNCTION get_date_from RETURN DATE
1880 IS
1881 BEGIN
1882 RETURN g_date_from;
1883 END;
1884
1885 FUNCTION get_date_to RETURN DATE
1886 IS
1887 BEGIN
1888 RETURN g_date_to;
1889 END;
1890
1891 FUNCTION get_old_username RETURN VARCHAR2
1892 IS
1893 BEGIN
1894 RETURN g_old_username;
1895 END;
1896
1897 END PO_Mass_Update_Req_PVT;