[Home] [Help]
PACKAGE BODY: APPS.POS_WCAPPROVE_PVT
Source
1 PACKAGE BODY POS_WCAPPROVE_PVT AS
2 /* $Header: POSVWCAB.pls 120.31.12020000.5 2013/06/03 11:19:21 ppotnuru ship $ */
3 --
4 -- Purpose: APIs called from the receiving processor to approve WCR document.
5 --
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- pparthas 02/15/05 Created Package
10 --
11 --
12
13 G_PKG_NAME CONSTANT VARCHAR2(30) := 'POS_WCAPPROVE_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(30) := 'POSVWCAS.pls';
15 g_module_prefix CONSTANT VARCHAR2(50) := 'pos.plsql.' || g_pkg_name || '.';
16
17 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
18
19 procedure debug_log(p_level in number,
20 p_api_name in varchar2,
21 p_msg in varchar2);
22
23 procedure debug_log(p_level in number,
24 p_api_name in varchar2,
25 p_msg in varchar2)
26 IS
27 l_module varchar2(2000);
28 BEGIN
29 /* Taken from Package FND_LOG
30 LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
31 LEVEL_ERROR CONSTANT NUMBER := 5;
32 LEVEL_EXCEPTION CONSTANT NUMBER := 4;
33 LEVEL_EVENT CONSTANT NUMBER := 3;
34 LEVEL_PROCEDURE CONSTANT NUMBER := 2;
35 LEVEL_STATEMENT CONSTANT NUMBER := 1;
36 */
37
38 l_module := 'pos.plsql.pos_wcapprove_pvt.'||p_api_name;
39 IF(g_asn_debug = 'Y')THEN
40 IF ( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
41 FND_LOG.string(LOG_LEVEL => p_level,
42 MODULE => l_module,
43 MESSAGE => p_msg);
44 END IF;
45
46 END IF;
47 END debug_log;
48
49 procedure CloseOldNotif(p_itemtype varchar2,
50 p_itemkey varchar2);
51
52 procedure UpdateWorkflowInfo
53 (
54 p_itemtype in varchar2,
55 p_itemkey in varchar2,
56 p_shipment_header_id in varchar2);
57
58 PROCEDURE Upd_ActionHistory_Submit (p_object_id IN NUMBER,
59 p_object_type_code IN VARCHAR2,
60 p_employee_id IN NUMBER,
61 p_sequence_num IN NUMBER,
62 p_action_code IN VARCHAR2,
63 p_user_id IN NUMBER,
64 p_login_id IN NUMBER);
65
66 /* Start_Wf_Process
67 ** Starts a Document Approval workflow process.
68 ** Parameters:
69 ** IN:
70 ** ItemType
71 ** Item Type of the workflow to be started;
72 ** ItemKey
73 ** Item Key for starting the workflow; if NULL, we will construct a new key
74 ** from the sequence
75 ** DocumentID
76 ** Shipment_header_id from rcv_shipment_header for the Work Confirmation
77 ** Document.
78 **PreparerID
79 ** buyer who created the complex work PO
80 */
81
82
83 PROCEDURE Start_WF_Process ( p_itemtype IN VARCHAR2,
84 p_itemkey IN OUT NOCOPY VARCHAR2,
85 p_workflow_process IN VARCHAR2,
86 p_work_confirmation_id IN NUMBER,
87 x_return_status OUT NOCOPY VARCHAR2) IS
88 l_progress varchar2(300);
89 l_seq_for_item_key varchar2(6) :=null;
90 l_itemkey varchar2(60);
91 l_wf_created number;
92 l_work_confirmation_number varchar2(30);
93 l_api_name varchar2(50) := p_itemkey || ' start_wf_process';
94 l_responsibility_id number;
95 l_user_id number;
96 l_application_id number;
97 l_orgid number;
98 l_po_header_id number;
99
100 begin
101 l_progress := 'POS_WCAPPROVE_PVT.start_wf_progress: 01';
102
103 IF (g_asn_debug = 'Y') THEN
104 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
105 'Enter Start_wf_process ' || l_progress);
106 END IF;
107
108 If (p_itemkey is NULL) then
109
110 select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
111 into l_seq_for_item_key
112 from sys.dual;
113
114 l_itemkey := to_char(p_work_confirmation_id) || '-' ||
115 l_seq_for_item_key;
116 else
117 l_itemkey := p_itemkey;
118 end if;
119
120 /* Check to see if process has already been created.
121 * If so, dont create again.
122 */
123
124 l_progress := 'POS_WCAPPROVE_PVT.start_wf_process: 02';
125 If ((p_itemtype is not null) and (l_itemkey is not null) and
126 (p_work_confirmation_id is not null)) then --{
127
128 begin
129 select count(*)
130 into l_wf_created
131 from wf_items
132 where item_type = p_itemtype
133 and item_key = l_itemkey;
134
135 exception
136 when others then
137 IF (g_asn_debug = 'Y') THEN
138 debug_log(FND_LOG.LEVEL_UNEXPECTED,
139 l_api_name,'Exception to check process '
140 || l_progress);
141 END IF;
142 raise;
143 end;
144
145
146 IF (g_asn_debug = 'Y') THEN
147 debug_log(FND_LOG.LEVEL_STATEMENT,
148 l_api_name,'l_wf_created ' || l_wf_created);
149 END IF;
150
151 if l_wf_created = 0 then
152 wf_engine.CreateProcess(
153 ItemType => p_itemtype,
154 ItemKey => l_itemkey,
155 process => p_workflow_process );
156 IF (g_asn_debug = 'Y') THEN
157 debug_log(FND_LOG.LEVEL_STATEMENT,
158 l_api_name,'Workflow process created ' );
159 END IF;
160 end if;
161
162
163 /* Initialize workflow item attributes */
164 IF (g_asn_debug = 'Y') THEN
165 debug_log(FND_LOG.LEVEL_STATEMENT,
166 l_api_name,'Initialize workflow attributes ' );
167 END IF;
168
169 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
170 itemkey => l_itemkey,
171 aname => 'WORK_CONFIRMATION_ID',
172 avalue => p_work_confirmation_id);
173
174
175 IF (g_asn_debug = 'Y') THEN
176 debug_log(FND_LOG.LEVEL_STATEMENT,
177 l_api_name,'p_work_confirmation_id '||p_work_confirmation_id );
178 END IF;
179
180 select shipment_num
181 into l_work_confirmation_number
182 from rcv_shipment_headers
183 where shipment_header_id = p_work_confirmation_id
184 and nvl(asn_type,'STD') = 'WC';
185
186 IF (g_asn_debug = 'Y') THEN
187 debug_log(FND_LOG.LEVEL_STATEMENT,
188 l_api_name,'l_work_confirmation_number '
189 ||l_work_confirmation_number );
190
191 END IF;
192
193 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
194 itemkey => l_itemkey,
195 aname => 'WORK_CONFIRMATION_NUMBER',
196 avalue => l_work_confirmation_number);
197
198
199 l_progress := 'POS_WCAPPROVE_PVT.start_wf_process: 03.';
200 select max(po_header_id)
201 into l_po_header_id
202 from rcv_shipment_lines
203 where shipment_header_id = p_work_confirmation_id;
204
205 IF (g_asn_debug = 'Y') THEN
206 debug_log(FND_LOG.LEVEL_STATEMENT,
207 l_api_name,'l_po_header_id ' || l_po_header_id); END IF;
208
209 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
210 itemkey => l_itemkey,
211 aname => 'PO_DOCUMENT_ID',
212 avalue => l_po_header_id);
213
214 POS_WCAPPROVE_PVT.get_multiorg_context(l_po_header_id,l_orgid);
215 IF l_orgid is NOT NULL THEN
216
217 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
218
219 /* Set the Org_id item attribute. We will use it to get
220 * the context for every activity
221 */
222 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
223 itemkey => l_itemkey,
224 aname => 'ORG_ID',
225 avalue => l_orgid);
226
227 END IF;
228
229 if (l_wf_created = 0) then
230 FND_PROFILE.GET('USER_ID', l_user_id);
231 FND_PROFILE.GET('RESP_ID', l_responsibility_id);
232 FND_PROFILE.GET('RESP_APPL_ID', l_application_id);
233 /* l_application_id := 201; */
234 --
235 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
236 itemkey => l_itemkey,
237 aname => 'USER_ID',
238 avalue => l_user_id);
239 --
240 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
241 itemkey => l_itemkey,
242 aname => 'APPLICATION_ID',
243 avalue => l_application_id);
244 --
245 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
246 itemkey => l_itemkey,
247 aname => 'RESPONSIBILITY_ID',
248 avalue => l_responsibility_id);
249
250 /* Set the context for the doc manager */
251 fnd_global.APPS_INITIALIZE (l_user_id, l_responsibility_id, l_application_id);
252 end if;
253
254 IF (g_asn_debug = 'Y') THEN
255 debug_log(FND_LOG.LEVEL_STATEMENT,
256 l_api_name,'Start process ' );
257 debug_log(FND_LOG.LEVEL_STATEMENT,
258 l_api_name,'itemtype '||p_itemtype );
259 debug_log(FND_LOG.LEVEL_STATEMENT,
260 l_api_name,'l_itemkey '||l_itemkey );
261 END IF;
262 wf_engine.StartProcess(itemtype => p_itemtype,
263 itemkey => l_itemkey );
264
265 end if; --} itemtype, itemkey and work_confirmation_id not null
266
267 IF (g_asn_debug = 'Y') THEN
268 debug_log(FND_LOG.LEVEL_STATEMENT,
269 l_api_name,'Leave start_wf_process ' );
270 End if;
271 Exception
272 when others then
273 IF (g_asn_debug = 'Y') THEN
274 debug_log(FND_LOG.LEVEL_UNEXPECTED,
275 l_api_name,'Exception in Start_wf_process ' || l_progress);
276 END IF;
277 x_return_status := FND_API.G_RET_STS_ERROR;
278 raise;
279 End Start_wf_process;
280
281 /*
282 ** Close_old_notifications
283 ** IN
284 ** itemtype -- itemkey -- actid -- funcmode
285 ** OUT
286 ** Resultout
287 **
288 ** Update the old notifications to closed status for this document.
289 */
290 procedure Close_old_notif
291 (
292 p_itemtype in varchar2,
293 p_itemkey in varchar2,
294 p_actid in number,
295 p_funcmode in varchar2,
296 x_resultout out NOCOPY varchar2 ) IS
297
298 l_work_confirmation_id Number;
299 l_wf_itemkey Varchar2(280);
300 l_progress varchar2(300);
301
302 cursor ship_header_cursor(p_header_id number) is
303 select wf_item_key
304 from rcv_shipment_headers
305 where shipment_header_id = p_header_id;
306
307 l_api_name varchar2(50) := p_itemkey || ' close_old_notif';
308
309 begin
310
311 l_progress := 'POS_WCAPPROVE_PVT.close_old_notifications: 01';
312
313 IF (g_asn_debug = 'Y') THEN
314 debug_log(FND_LOG.LEVEL_STATEMENT,
315 l_api_name,'Enter close_old_notif ' || l_progress);
316 debug_log(FND_LOG.LEVEL_STATEMENT,
317 l_api_name,'p_itemtype ' || p_itemtype);
318 debug_log(FND_LOG.LEVEL_STATEMENT,
319 l_api_name,'p_itemkey ' || p_itemkey);
320 END IF;
321
322 if (p_funcmode <> wf_engine.eng_run) then
323 x_resultout := wf_engine.eng_null;
324 return;
325 end if;
326
327
328 l_work_confirmation_id :=
329 wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
330 itemkey => p_itemkey,
331 aname => 'WORK_CONFIRMATION_ID');
332
333 IF (g_asn_debug = 'Y') THEN
334 debug_log(FND_LOG.LEVEL_STATEMENT,
335 l_api_name,'l_work_confirmation_id ' ||
336 l_work_confirmation_id);
337 END IF;
338 /* If the document has been previously submitted to workflow,
339 * and did not complete because of some error then notifications
340 * may have been issued to users. We need to remove those
341 * notifications once we submit the document to a new workflow run,
342 * so that the user is not confused.
343 */
344
345 open ship_header_cursor(l_work_confirmation_id);
346 fetch ship_header_cursor into l_wf_itemkey;
347 close ship_header_cursor;
348
349 IF (g_asn_debug = 'Y') THEN
350 debug_log(FND_LOG.LEVEL_STATEMENT,
351 l_api_name,'l_wf_itemkey ' || l_wf_itemkey);
352 END IF;
353
354 if (l_wf_itemkey is not null) then
355
356 CloseOldNotif(p_itemtype,l_wf_itemkey);
357
358 end if;
359
360 x_resultout := wf_engine.eng_completed ;
361
362 l_progress := 'POS_WCAPPROVE_PVT.close_old_notif: 02.';
363
364 IF (g_asn_debug = 'Y') THEN
365 debug_log(FND_LOG.LEVEL_STATEMENT,
366 l_api_name,'Leave close_old_notif ' || l_progress);
367 END IF;
368
369 exception
370 when others then
371 IF (g_asn_debug = 'Y') THEN
372 debug_log(FND_LOG.LEVEL_UNEXPECTED,
373 l_api_name,'Exception in close_old_notif ' || l_progress);
374 END IF;
375 raise;
376 end Close_old_notif;
377
378 procedure Set_Startup_Values( p_itemtype in varchar2,
379 p_itemkey in varchar2,
380 p_actid in number,
381 p_funcmode in varchar2,
382 x_resultout out NOCOPY varchar2 ) is
383 l_work_confirmation_id number;
384 l_work_confirmation_number varchar2(30);
385 l_po_number varchar2(30);
386 l_po_header_id number;
387 l_po_preparer_id number;
388 l_wc_preparer_id number;
389 l_wc_preparer_name varchar2(100);
390 l_wc_preparer_display_name varchar2(240);
391 l_po_preparer_name varchar2(100);
392 l_po_preparer_display_name varchar2(240);
393 l_progress varchar2(200);
394 l_api_name varchar2(50) := p_itemkey || ' set_startup_Values';
395 l_view_wc_lines_detail_url varchar2(1000);
396 l_respond_to_wc_url varchar2(1000);
397 l_employee_id NUMBER;
398
399 begin
400 l_progress := 'POS_WCAPPROVE_PVT.set_startup_values: 01.';
401 IF (g_asn_debug = 'Y') THEN
402 debug_log(FND_LOG.LEVEL_STATEMENT,
403 l_api_name,'Enter in set_startup_values ' || l_progress);
404 END IF;
405
406 if (p_funcmode <> wf_engine.eng_run) then
407 x_resultout := wf_engine.eng_null;
408 return;
409 end if;
410
411 l_work_confirmation_id :=
412 wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
413 itemkey => p_itemkey,
414 aname => 'WORK_CONFIRMATION_ID');
415
416
417 IF (g_asn_debug = 'Y') THEN
418 debug_log(FND_LOG.LEVEL_STATEMENT,
419 l_api_name,'l_work_confirmation_id ' ||
420 l_work_confirmation_id);
421 END IF;
422
423 l_progress := 'POS_WCAPPROVE_PVT.set_startup_values: 02.';
424 select created_by
425 into l_wc_preparer_id
426 from rcv_shipment_headers
427 where shipment_header_id= l_work_confirmation_id;
428
429
430 select employee_id
431 into l_employee_id
432 from fnd_user
433 where user_id = l_wc_preparer_id;
434
435 IF (g_asn_debug = 'Y') THEN
436 debug_log(FND_LOG.LEVEL_STATEMENT,
437 l_api_name,'l_wc_preparer_id ' ||
438 l_wc_preparer_id);
439 END IF;
440
441 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
442 itemkey => p_itemkey,
443 aname => 'WC_PREPARER_ID',
444 avalue => l_wc_preparer_id);
445
446 l_progress := 'POS_WCAPPROVE_PVT.set_startup_values: 03.';
447 if (l_employee_id is not null) then
448 get_user_name('PER',l_employee_id, l_wc_preparer_name,
449 l_wc_preparer_display_name);
450 else
451 get_user_name('FND_USR',l_wc_preparer_id, l_wc_preparer_name,
452 l_wc_preparer_display_name);
453 end if;
454
455
456 IF (g_asn_debug = 'Y') THEN
457 debug_log(FND_LOG.LEVEL_STATEMENT,
458 l_api_name,'l_wc_preparer_name ' || l_wc_preparer_name);
459 debug_log(FND_LOG.LEVEL_STATEMENT,
460 l_api_name,'l_wc_preparer_display_name ' ||
461 l_wc_preparer_display_name);
462 END IF;
463 wf_engine.SetItemAttrText ( itemtype => p_itemType,
464 itemkey => p_itemkey,
465 aname => 'WC_PREPARER_NAME' ,
466 avalue => l_wc_preparer_name);
467
468 wf_engine.SetItemAttrText ( itemtype => p_itemType,
469 itemkey => p_itemkey,
470 aname => 'WC_PREPARER_DISPLAY_NAME' ,
471 avalue => l_wc_preparer_display_name);
472
473 l_progress := 'POS_WCAPPROVE_PVT.set_startup_values: 04.';
474 l_po_header_id :=
475 wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
476 itemkey => p_itemkey,
477 aname => 'PO_DOCUMENT_ID');
478
479 IF (g_asn_debug = 'Y') THEN
480 debug_log(FND_LOG.LEVEL_STATEMENT,
481 l_api_name,'l_po_header_id ' || l_po_header_id);
482 END IF;
483
484 /* We need doc_status only if the document is rejected.
485 * This is to show the status of the lines in the
486 * notification screen. So set the default to NOTREJECTED.
487 * We set it to REJECTED in reject_doc.
488 */
489 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
490 itemkey => p_itemkey,
491 aname => 'DOC_STATUS',
492 avalue => 'NOTREJECTED');
493
494 l_progress := 'POS_WCAPPROVE_PVT.set_startup_values: 05.';
495 select poh.segment1,
496 poh.agent_id
497 into l_po_number,
498 l_po_preparer_id
499 from po_headers_all poh
500 where po_header_id = l_po_header_id;
501
502 IF (g_asn_debug = 'Y') THEN
503 debug_log(FND_LOG.LEVEL_STATEMENT,
504 l_api_name,'l_po_number ' || l_po_number);
505 debug_log(FND_LOG.LEVEL_STATEMENT,
506 l_api_name,'l_preparer_id ' || l_po_preparer_id);
507 END IF;
508
509 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
510 itemkey => p_itemkey,
511 aname => 'PO_PREPARER_ID',
512 avalue => l_po_preparer_id);
513
514 l_progress := 'POS_WCAPPROVE_PVT.set_startup_values: 06.';
515 get_user_name('PER',l_po_preparer_id, l_po_preparer_name,
516 l_po_preparer_display_name);
517
518 IF (g_asn_debug = 'Y') THEN
519 debug_log(FND_LOG.LEVEL_STATEMENT,
520 l_api_name,'l_po_preparer_name ' || l_po_preparer_name);
521 debug_log(FND_LOG.LEVEL_STATEMENT,
522 l_api_name,'l_po_preparer_display_name ' ||
523 l_po_preparer_display_name);
524 END IF;
525 wf_engine.SetItemAttrText ( itemtype => p_itemType,
526 itemkey => p_itemkey,
527 aname => 'PO_PREPARER_NAME' ,
528 avalue => l_po_preparer_name);
529
530 wf_engine.SetItemAttrText ( itemtype => p_itemType,
531 itemkey => p_itemkey,
532 aname => 'PO_PREPARER_DISPLAY_NAME' ,
533 avalue => l_po_preparer_display_name);
534
535 wf_engine.SetItemAttrText ( itemtype => p_itemType,
536 itemkey => p_itemkey,
537 aname => 'AME_TRANSACTION_TYPE' ,
538 avalue => 'WCAPPROVE');
539
540 wf_engine.SetItemAttrText ( itemtype => p_itemType,
541 itemkey => p_itemkey,
542 aname => 'DEFAULT_APPROVER' ,
543 avalue => 'NONE');
544
545 /* Since we are starting a fresh one, we need to clear the
546 * transaction approval status state. This happens only when
547 * the document is sent through the workflow for the first time
548 * or when the document is rejected and then sent again.
549 */
550
551 ame_api2.clearAllApprovals( applicationIdIn => 201,
552 transactionIdIn => l_work_confirmation_id,
553 transactionTypeIn => 'WCAPPROVE'
554 );
555
556 l_work_confirmation_number :=
557 wf_engine.GetItemAttrText (itemtype => p_itemtype,
558 itemkey => p_itemkey,
559 aname => 'WORK_CONFIRMATION_NUMBER');
560
561
562 -- bug 10012891 - encoding value of work confirmation number as per http standards
563 l_view_wc_lines_detail_url :=
564 'JSP:/OA_HTML/OAFunc=POS_WC_RESPOND_NTFN' || '&' ||
565 --'WcStatus=PENDING APPROVAL' || '&' ||
566 'PoHeaderId=' || to_char(l_po_header_id) || '&' ||
567 'WcHeaderId=' || to_char(l_work_confirmation_id) || '&' ||
568 'WcNum=' || (wfa_html.encode_url(l_work_confirmation_number)) || '&' ||
569 --'ReadOnly=Y' || '&' ||
570 'addBreadCrumb=Y';
571
572 wf_engine.SetItemAttrText ( itemtype => p_itemType,
573 itemkey => p_itemkey,
574 aname => 'VIEW_WC_LINES_DETAIL_URL' ,
575 avalue => l_view_wc_lines_detail_url);
576
577 -- bug 10012891 - encoding value of work confirmation number as per http standards
578 l_respond_to_wc_url :=
579 'JSP:/OA_HTML/OA.jsp?OAFunc=POS_WC_RESPOND_NTFN' || '&' ||
580 'SrcPage=DetailsInternalPG' || '&' ||
581 'WcStatus=PENDING APPROVAL' || '&' ||
582 'PoHeaderId=' || to_char(l_po_header_id) || '&' ||
583 'WcHeaderId=' || to_char(l_work_confirmation_id) || '&' ||
584 'WcNum=' || (wfa_html.encode_url(l_work_confirmation_number)) || '&' ||
585 'addBreadCrumb=Y';
586
587 wf_engine.SetItemAttrText ( itemtype => p_itemType,
588 itemkey => p_itemkey,
589 aname => 'RESPOND_TO_WC_URL' ,
590 avalue => l_respond_to_wc_url);
591
592 x_resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
593
594 l_progress := 'POS_WCAPPROVE_PVT.set_startup_values: 06.';
595 IF (g_asn_debug = 'Y') THEN
596 debug_log(FND_LOG.LEVEL_STATEMENT,
597 l_api_name,'Leave set_startup_values '
598 || l_progress);
599 END IF;
600 exception
601 when others then
602 IF (g_asn_debug = 'Y') THEN
603 debug_log(FND_LOG.LEVEL_UNEXPECTED,
604 l_api_name,'Exception in set_startup_values '
605 || l_progress);
606 END IF;
607 raise;
608 end set_startup_values;
609
610
611 procedure update_workflow_info( p_itemtype in varchar2,
612 p_itemkey in varchar2,
613 p_actid in number,
614 p_funcmode in varchar2,
615 x_resultout out NOCOPY varchar2) is
616 l_shipment_header_id number;
617 l_progress varchar2(300);
618 l_api_name varchar2(50) := p_itemkey || ' update_workflow_info';
619 begin
620
621 l_progress := 'POS_WCAPPROVE_PVT.update_workflow_info: 01.';
622 IF (g_asn_debug = 'Y') THEN
623 debug_log(FND_LOG.LEVEL_STATEMENT,
624 l_api_name,'Enter in update_workflow_info ' || l_progress);
625 END IF;
626
627 if (p_funcmode <> wf_engine.eng_run) then
628 x_resultout := wf_engine.eng_null;
629 return;
630 end if;
631
632
633 l_shipment_header_id := wf_engine.GetItemAttrNumber
634 (itemtype => p_itemtype,
635 itemkey => p_itemkey,
636 aname => 'WORK_CONFIRMATION_ID');
637
638 l_progress := 'POS_WCAPPROVE_PVT.update_workflow_info: 02.';
639 IF (g_asn_debug = 'Y') THEN
640 debug_log(FND_LOG.LEVEL_STATEMENT,
641 l_api_name,'l_shipment_header_id ' || l_shipment_header_id);
642 END IF;
643
644 UpdateWorkflowInfo(p_itemtype,p_itemkey,l_shipment_header_id);
645
646 x_resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
647
648 l_progress := 'POS_WCAPPROVE_PVT.update_workflow_info: 02.';
649 IF (g_asn_debug = 'Y') THEN
650 debug_log(FND_LOG.LEVEL_STATEMENT,
651 l_api_name,'Leave update_workflow_info ' || l_progress);
652 END IF;
653
654 exception
655 when others then
656 IF (g_asn_debug = 'Y') THEN
657 debug_log(FND_LOG.LEVEL_UNEXPECTED,
658 l_api_name,'Exception in update_workflow_info '
659 || l_progress);
660 END IF;
661 raise;
662 end update_workflow_info;
663
664 procedure Get_WC_Attributes( p_itemtype in varchar2,
665 p_itemkey in varchar2,
666 p_actid in number,
667 p_funcmode in varchar2,
668 x_resultout out NOCOPY varchar2)is
669
670 cursor get_attachments(l_shipment_header_id number) is
671 select pk1_value
672 from fnd_attached_documents
673 where entity_name='RCV_LINES'
674 and pk1_value in (select shipment_line_id
675 from rcv_shipment_lines
676 where shipment_header_id=l_shipment_header_id);
677
678 l_shipment_header_id number;
679 l_shipment_line_id number;
680 l_document_id number;
681 l_attach_count number := 0;
682 l_attachment_id number;
683 l_progress varchar2(300);
684 l_api_name varchar2(50) := p_itemkey || ' get_wc_attributes';
685 begin
686 l_progress := 'POS_WCAPPROVE_PVT.get_wc_attributes: 01.';
687 IF (g_asn_debug = 'Y') THEN
688 debug_log(FND_LOG.LEVEL_STATEMENT,
689 l_api_name,'Enter in get_wc_attributes '
690 || l_progress);
691 end if;
692
693 if (p_funcmode <> wf_engine.eng_run) then
694 x_resultout := wf_engine.eng_null;
695 return;
696 end if;
697
698
699 l_shipment_header_id := wf_engine.GetItemAttrNumber
700 (itemtype => p_itemtype,
701 itemkey => p_itemkey,
702 aname => 'WORK_CONFIRMATION_ID');
703
704 IF (g_asn_debug = 'Y') THEN
705 debug_log(FND_LOG.LEVEL_STATEMENT,
706 l_api_name,'l_shipment_header_id '|| l_shipment_header_id);
707 end if;
708
709 /* See if atleast one shipment have attachments*/
710
711 /*
712 l_progress := 'POS_WCAPPROVE_PVT.get_wc_attributes: 02.';
713 select count(*)
714 into l_attach_count
715 from fnd_attached_documents
716 where entity_name =' RCV_LINES'
717 and pk1_value in (select shipment_line_id
718 from rcv_shipment_lines
719 where shipment_header_id=l_shipment_header_id);
720
721 IF (g_asn_debug = 'Y') THEN
722 debug_log(FND_LOG.LEVEL_STATEMENT,
723 l_api_name,'l_attach_count '||
724 l_attach_count);
725 end if;
726
727
728 If (l_attach_count > 0) then--{
729 open get_attachments(l_shipment_header_id);
730
731 --l_document_id= 'FND:entity=RCV_LINES';
732 loop
733 fetch get_attachments into l_shipment_line_id;
734 exit when get_attachments%notfound;
735
736 IF (g_asn_debug = 'Y') THEN
737 debug_log(FND_LOG.LEVEL_STATEMENT,
738 l_api_name,'l_shipment_line_id '||
739 l_shipment_line_id);
740 end if;
741
742
743 end loop;
744 If get_attachments%isopen then
745 close get_attachments;
746 end if;
747 end if;--}
748 */
749 x_resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
750 exception
751 when others then
752 IF (g_asn_debug = 'Y') THEN
753 debug_log(FND_LOG.LEVEL_UNEXPECTED,
754 l_api_name,'Exception in Get_WC_Attributes '
755 || l_progress);
756 END IF;
757 raise;
758 end Get_WC_Attributes;
759
760
761 /* Insert a submit action row in po_action_history to signal the
762 * submisinon of the document for Approval. Also insert an
763 * additional row with a NULL ACTION_CODE to simulate a
764 * forward-to. We need to do this for each shipment line in this
765 * document.
766 */
767 procedure Ins_actionhist_submit(p_itemtype in varchar2,
768 p_itemkey in varchar2,
769 p_actid in number,
770 p_funcmode in varchar2,
771 x_resultout out NOCOPY varchar2)is
772 l_progress varchar2(200);
773 l_shipment_header_id number;
774 l_shipment_line_id number;
775 l_wc_preparer_id number;
776 l_sequence_num number;
777 l_action_code po_action_history.object_type_code%type;
778 l_api_name varchar2(50) := p_itemkey || ' ins_actionhist_submit';
779
780 /*
781 cursor get_shipment_lines(l_shipment_header_id number) is
782 select shipment_line_id
783 from rcv_shipment_lines
784 where shipment_header_id=l_shipment_header_id;
785 */
786
787 cursor action_hist_cursor (l_shipment_header_id number) is
788 select max(sequence_num)
789 from po_action_history
790 where object_id=l_shipment_header_id
791 and object_type_code = 'WC';
792
793 cursor action_hist_code_cursor(l_shipment_header_id number,l_seq_num number) is
794 select action_code
795 from po_action_history
796 where object_id = l_shipment_header_id
797 and object_type_code='WC'
798 and sequence_num = l_seq_num;
799 BEGIN
800
801 l_progress := 'POS_WCAPPROVE_PVT.Ins_Actionhist_submit: 01.';
802 IF (g_asn_debug = 'Y') THEN
803 debug_log(FND_LOG.LEVEL_STATEMENT,
804 l_api_name,'Enter in Ins_Actionhist_submit '
805 || l_progress);
806 END IF;
807
808 if (p_funcmode <> wf_engine.eng_run) then
809 x_resultout := wf_engine.eng_null;
810 return;
811 end if;
812
813 l_shipment_header_id := wf_engine.GetItemAttrNumber
814 (itemtype => p_itemtype,
815 itemkey => p_itemkey,
816 aname => 'WORK_CONFIRMATION_ID');
817
818 IF (g_asn_debug = 'Y') THEN
819 debug_log(FND_LOG.LEVEL_STATEMENT,
820 l_api_name,'l_shipment_header_id ' || l_shipment_header_id);
821 END IF;
822
823 l_progress := 'POS_WCAPPROVE_PVT.Ins_Actionhist_submit: 02.';
824 l_wc_preparer_id := wf_engine.GetItemAttrNumber
825 (itemtype => p_itemtype,
826 itemkey => p_itemkey,
827 aname => 'WC_PREPARER_ID');
828
829 IF (g_asn_debug = 'Y') THEN
830 debug_log(FND_LOG.LEVEL_STATEMENT,
831 l_api_name,'l_wc_preparer_id ' || l_wc_preparer_id);
832 END IF;
833
834
835 l_progress := 'POS_WCAPPROVE_PVT.Ins_Actionhist_submit: 03.';
836
837 l_progress := 'POS_WCAPPROVE_PVT.Ins_Actionhist_submit: 04.';
838
839
840 /* Check if this document had been submitted to workflow at
841 * some point and somehow kicked out. If that's the case, the
842 * sequence number needs to be incremented by one. Otherwise
843 * start at zero.
844 */
845 l_progress := 'POS_WCAPPROVE_PVT.Ins_Actionhist_submit: 05.';
846
847 Open action_hist_cursor(l_shipment_header_id );
848 Fetch action_hist_cursor into l_sequence_num;
849 close action_hist_cursor;
850
851
852 IF l_sequence_num is NULL THEN
853 l_sequence_num := 0;
854 ELSE
855 Open action_hist_code_cursor(l_shipment_header_id ,
856 l_sequence_num);
857 Fetch action_hist_code_cursor into l_action_code;
858 close action_hist_code_cursor;
859 l_sequence_num := l_sequence_num +1;
860 END IF;
861
862 IF (g_asn_debug = 'Y') THEN
863 debug_log(FND_LOG.LEVEL_STATEMENT,
864 l_api_name,'l_sequence_num ' ||
865 l_sequence_num);
866 END IF;
867
868 l_progress := 'POS_WCAPPROVE_PVT.Ins_Actionhist_submit: 06.';
869
870 IF ((l_sequence_num = 0)
871 OR
872 (l_sequence_num > 0 and l_action_code is NOT NULL)) THEN --{
873
874
875 IF (g_asn_debug = 'Y') THEN
876 debug_log(FND_LOG.LEVEL_STATEMENT,
877 l_api_name,'before call to InsertPOActionHistory ' ||
878 l_sequence_num);
879 END IF;
880
881 /* Only Supplier will be able to do a SUBMIT. Do not
882 * send in the employee_id.
883 */
884 InsertPOActionHistory(
885 p_object_id => l_shipment_header_id,
886 p_object_type_code=>'WC',
887 p_object_sub_type_code => NULL,
888 p_sequence_num =>l_sequence_num ,
889 p_action_code =>'SUBMIT' ,
890 p_action_date =>sysdate ,
891 p_employee_id => null, --l_wc_preparer_id forsupplier
892 p_approval_path_id => NULL ,
893 p_note => NULL,
894 p_object_revision_num => NULL,
895 p_offline_code => '',
896 p_request_id => 0,
897 p_program_application_id => 0,
898 p_program_id =>0 ,
899 p_program_date => sysdate ,
900 p_user_id => fnd_global.user_id ,
901 p_login_id => fnd_global.login_id);
902
903 ELSE --}{
904 l_sequence_num := l_sequence_num - 1;
905
906 IF (g_asn_debug = 'Y') THEN
907 debug_log(FND_LOG.LEVEL_STATEMENT,
908 l_api_name,'before call to Upd_ActionHistory_Submit ' ||
909 l_sequence_num);
910 END IF;
911
912
913 Upd_ActionHistory_Submit
914 (p_object_id =>l_shipment_header_id,
915 p_object_type_code =>'WC',
916 p_employee_id => null, --l_wc_preparer_id,
917 p_sequence_num => l_sequence_num,
918 p_action_code => 'SUBMIT',
919 p_user_id => fnd_global.user_id ,
920 p_login_id =>fnd_global.login_id );
921
922 END IF; --}
923
924 /* Insert the null action code*/
925 IF (g_asn_debug = 'Y') THEN
926 debug_log(FND_LOG.LEVEL_STATEMENT,
927 l_api_name,'insert null action_code ' ||
928 l_sequence_num);
929 END IF;
930
931
932 /*InsertPOActionHistory(
933 p_object_id => l_shipment_header_id,
934 p_object_type_code=>'WC',
935 p_object_sub_type_code => NULL,
936 p_sequence_num =>l_sequence_num+1 ,
937 p_action_code =>NULL ,
938 p_action_date =>sysdate ,
939 p_employee_id => null,--l_wc_preparer_id for supplier
940 p_approval_path_id => NULL ,
941 p_note => NULL,
942 p_object_revision_num => NULL,
943 p_offline_code => '',
944 p_request_id => 0,
945 p_program_application_id => 0,
946 p_program_id =>0 ,
947 p_program_date => sysdate ,
948 p_user_id => fnd_global.user_id ,
949 p_login_id => fnd_global.login_id); */
950
951
952
953
954
955 x_resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
956
957 l_progress := 'POS_WCAPPROVE_PVT.Ins_Actionhist_submit: 07.';
958 IF (g_asn_debug = 'Y') THEN
959 debug_log(FND_LOG.LEVEL_STATEMENT,
960 l_api_name,'Leave Ins_Actionhist_submit ' || l_progress);
961 END IF;
962 exception
963 when others then
964 IF (g_asn_debug = 'Y') THEN
965 debug_log(FND_LOG.LEVEL_UNEXPECTED,
966 l_api_name,'Exception in Ins_Actionhist_submit '
967 || l_progress);
968 END IF;
969 raise;
970 end Ins_Actionhist_Submit;
971
972 procedure Get_Next_Approver(p_itemtype in varchar2,
973 p_itemkey in varchar2,
974 p_actid in number,
975 p_funcmode in varchar2,
976 x_resultout out NOCOPY varchar2) IS
977 l_api_name varchar2(50) := p_itemkey || ' get_next_approver';
978 l_application_id number := 201; -- use PO application id.
979 l_shipment_header_id number;
980 l_po_header_id number;
981 l_transaction_type varchar2(100);
982 l_insertion_type VARCHAR2(30);
983 l_work_confirmation_number varchar2(30);
984 l_authority_type VARCHAR2(30);
985 l_forward_from_user_name varchar2(100);
986 l_wc_preparer_name varchar2(100);
987 l_progress varchar2(300);
988 approver_exception exception;
989 l_next_approver_id number;
990 l_next_approver_name per_employees_current_x.full_name%TYPE;
991 l_next_approver_user_name VARCHAR2(100);
992 l_next_approver_disp_name VARCHAR2(240);
993 l_orig_system VARCHAR2(48);
994 l_completeYNO varchar2(100);
995 l_next_approver ame_util.approversTable2;
996 l_rule_id ame_util.idList;
997 l_po_preparer_id number;
998 l_respond_to_wc_url varchar2(1000);
999 l_default_approver varchar2(30);
1000 BEGIN
1001
1002 l_progress := 'POS_WCAPPROVE_PVT.get_next_approver: 01.';
1003 IF (g_asn_debug = 'Y') THEN
1004 debug_log(FND_LOG.LEVEL_STATEMENT,
1005 l_api_name,'Enter in get_next_approver '
1006 || l_progress);
1007 END IF;
1008
1009 if (p_funcmode <> wf_engine.eng_run) then
1010 IF (g_asn_debug = 'Y') THEN
1011 debug_log(FND_LOG.LEVEL_STATEMENT,
1012 l_api_name,'funcmode in get_next_approver '
1013 || l_progress);
1014 END IF;
1015
1016 x_resultout := wf_engine.eng_null;
1017 return;
1018 end if;
1019
1020 l_shipment_header_id := wf_engine.GetItemAttrNumber
1021 (itemtype => p_itemtype,
1022 itemkey => p_itemkey,
1023 aname => 'WORK_CONFIRMATION_ID');
1024
1025 IF (g_asn_debug = 'Y') THEN
1026 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
1027 'l_shipment_header_id ' || l_shipment_header_id);
1028 END IF;
1029
1030 l_po_header_id := wf_engine.GetItemAttrNumber
1031 (itemtype => p_itemtype,
1032 itemkey => p_itemkey,
1033 aname => 'PO_DOCUMENT_ID');
1034
1035 IF (g_asn_debug = 'Y') THEN
1036 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
1037 'l_po_header_id ' || l_po_header_id);
1038 END IF;
1039
1040 l_work_confirmation_number :=
1041 wf_engine.GetItemAttrText (itemtype => p_itemtype,
1042 itemkey => p_itemkey,
1043 aname => 'WORK_CONFIRMATION_NUMBER');
1044
1045 IF (g_asn_debug = 'Y') THEN
1046 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
1047 'l_work_confirmation_number ' || l_work_confirmation_number);
1048 END IF;
1049
1050 l_transaction_type := wf_engine.GetItemAttrText (itemtype => p_itemtype,
1051 itemkey => p_itemkey,
1052 aname => 'AME_TRANSACTION_TYPE');
1053
1054 IF (g_asn_debug = 'Y') THEN
1055 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
1056 'l_transaction_type ' || l_transaction_type);
1057 END IF;
1058 l_progress := 'POS_WCAPPROVE_PVT.get_next_approver: 02.';
1059 IF (g_asn_debug = 'Y') THEN
1060 debug_log(FND_LOG.LEVEL_STATEMENT,
1061 l_api_name,'in get_next_approver '
1062 || l_progress);
1063 End if;
1064
1065 /* Set the Preparer name as the forward_from_user_name the
1066 * first time. After that approver_user_name would have
1067 * the name of the last approver. Forward_from_user_name
1068 * is used to set the #FROM_ROLE in the notifications
1069 * message.
1070 */
1071 l_forward_from_user_name :=
1072 wf_engine.GetItemAttrText (itemtype => p_itemtype,
1073 itemkey => p_itemkey,
1074 aname => 'APPROVER_USER_NAME');
1075
1076 IF (g_asn_debug = 'Y') THEN
1077 debug_log(FND_LOG.LEVEL_STATEMENT,
1078 l_api_name,'l_forward_from_user_name '
1079 || nvl(l_forward_from_user_name,'NONAME'));
1080 End if;
1081 If (l_forward_from_user_name is null) then
1082 l_wc_preparer_name :=
1083 wf_engine.GetItemAttrText (itemtype => p_itemtype,
1084 itemkey => p_itemkey,
1085 aname => 'WC_PREPARER_NAME');
1086
1087 IF (g_asn_debug = 'Y') THEN
1088 debug_log(FND_LOG.LEVEL_STATEMENT,
1089 l_api_name,'l_wc_preparer_name '
1090 || nvl(l_wc_preparer_name ,'NONAME'));
1091 End if;
1092
1093 wf_engine.SetItemAttrText( itemtype => p_itemType,
1094 itemkey => p_itemkey,
1095 aname => 'FORWARD_FROM_USER_NAME' ,
1096 avalue => l_wc_preparer_name);
1097
1098 else
1099 wf_engine.SetItemAttrText( itemtype => p_itemType,
1100 itemkey => p_itemkey,
1101 aname => 'FORWARD_FROM_USER_NAME' ,
1102 avalue => l_forward_from_user_name);
1103
1104 end if;
1105
1106 -- bug 10012891 - encoding value of work confirmation number as per http standards
1107 l_respond_to_wc_url :=
1108 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/pos/wc/webui/WcRespondPG' || '&' ||
1109 'SrcPage=DetailsInternalPG' || '&' ||
1110 'WcStatus=PENDING APPROVAL' || '&' ||
1111 'PoHeaderId=' || to_char(l_po_header_id) || '&' ||
1112 'WcHeaderId='||to_char(l_shipment_header_id)||'&' ||
1113 'WcNum=' || (wfa_html.encode_url(l_work_confirmation_number)) || '&' ||
1114 'addBreadCrumb=Y';
1115
1116 wf_engine.SetItemAttrText ( itemtype => p_itemType,
1117 itemkey => p_itemkey,
1118 aname => 'RESPOND_TO_WC_URL' ,
1119 avalue => l_respond_to_wc_url);
1120
1121 IF (g_asn_debug = 'Y') THEN
1122 debug_log(FND_LOG.LEVEL_STATEMENT,
1123 l_api_name,'before call to applicablerule ');
1124 End if;
1125 ame_api3.getApplicableRules1(applicationIdIn=>l_application_Id,
1126 transactionIdIn=>l_shipment_header_id,
1127 transactionTypeIn=> l_transaction_type,
1128 ruleIdsOut => l_rule_id);
1129 /* If there are no rules , then this will
1130 * return 0.
1131 * Check whether the workflow attribute DEFAULT_APPROVER
1132 * is BUYER. If it is, then this is already been approved
1133 * by him. So send NO_NEXT_APPROVER.
1134 * If the value is no BUYER then set the attribute with this
1135 * value.
1136 */
1137 if (l_rule_id.count = 0 ) then --{
1138 IF (g_asn_debug = 'Y') THEN
1139 debug_log(FND_LOG.LEVEL_STATEMENT,
1140 l_api_name,'no rule is setup ');
1141 End if;
1142 l_default_approver :=
1143 wf_engine.GetItemAttrText (itemtype => p_itemtype,
1144 itemkey => p_itemkey,
1145 aname => 'DEFAULT_APPROVER');
1146
1147 IF (g_asn_debug = 'Y') THEN
1148 debug_log(FND_LOG.LEVEL_STATEMENT,
1149 l_api_name,'l_default_approver '
1150 || l_default_approver);
1151 END IF;
1152
1153 If l_default_approver = 'BUYER' then --{
1154
1155 IF (g_asn_debug = 'Y') THEN
1156 debug_log(FND_LOG.LEVEL_STATEMENT,
1157 l_api_name,'already approved by buyer ');
1158 End if;
1159
1160 x_resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
1161 return;
1162 elsif(l_default_approver= 'NONE') then --}{
1163 IF (g_asn_debug = 'Y') THEN
1164 debug_log(FND_LOG.LEVEL_STATEMENT,
1165 l_api_name,'Send buyer as default approver ');
1166 End if;
1167
1168 wf_engine.SetItemAttrText ( itemtype => p_itemType,
1169 itemkey => p_itemkey,
1170 aname => 'DEFAULT_APPROVER' ,
1171 avalue => 'BUYER');
1172
1173 l_po_preparer_id := wf_engine.GetItemAttrNumber
1174 (itemtype => p_itemtype,
1175 itemkey => p_itemkey,
1176 aname => 'PO_PREPARER_ID');
1177
1178 IF (g_asn_debug = 'Y') THEN
1179 debug_log(FND_LOG.LEVEL_STATEMENT,
1180 l_api_name,'l_po_preparer_id '
1181 || l_po_preparer_id);
1182 end if;
1183
1184 l_next_approver(1).orig_system := 'PER';
1185 l_next_approver(1).orig_system_id := l_po_preparer_id;
1186
1187 end if; --}
1188
1189
1190 else --}{
1191
1192 IF (g_asn_debug = 'Y') THEN
1193 debug_log(FND_LOG.LEVEL_STATEMENT,
1194 l_api_name,'call ame api ');
1195 End if;
1196
1197 ame_api2.getNextApprovers4(applicationIdIn=>l_application_Id,
1198 transactionIdIn=>l_shipment_header_id,
1199 transactionTypeIn=> l_transaction_type,
1200 approvalProcessCompleteYNOut=>l_completeYNO,
1201 nextApproversOut=>l_next_approver);
1202
1203 end if; --}
1204
1205 if (l_next_approver.count > 1) then -- {
1206 x_resultout:='COMPLETE:'||'INVALID_APPROVER';
1207 raise approver_exception ;
1208 elsif (l_next_approver.count = 0 ) then --}{
1209 x_resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
1210 return;
1211 IF (g_asn_debug = 'Y') THEN
1212 debug_log(FND_LOG.LEVEL_STATEMENT,
1213 l_api_name,'no_next_approver ');
1214 End if;
1215 return;
1216 ELSE --}{
1217
1218 if (l_next_approver(1).orig_system = 'PER') then --{
1219 l_next_approver_id := l_next_approver(1).orig_system_id;
1220 elsif (l_next_approver(1).orig_system = 'POS') then --}{
1221 begin
1222 -- find the persondid from the position_id
1223 SELECT person_id, full_name
1224 into l_next_approver_id,l_next_approver_name
1225 FROM (
1226 SELECT person.person_id, person.full_name
1227 FROM per_all_people_f person, per_all_assignments_f asg
1228 WHERE asg.position_id = l_next_approver(1).orig_system_id
1229 and trunc(sysdate) between person.effective_start_date
1230 and nvl(person.effective_end_date, trunc(sysdate))
1231 and person.person_id = asg.person_id
1232 and asg.primary_flag = 'Y'
1233 and asg.assignment_type in ('E','C')
1234 and ( person.current_employee_flag = 'Y'
1235 or person.current_npw_flag = 'Y' )
1236 and asg.assignment_status_type_id not in
1237 (
1238 SELECT assignment_status_type_id
1239 FROM per_assignment_status_types
1240 WHERE per_system_status = 'TERM_ASSIGN'
1241 )
1242 and trunc(sysdate) between asg.effective_start_date
1243 and asg.effective_end_date
1244 order by person.last_name
1245 ) where rownum = 1;
1246 exception
1247 WHEN NO_DATA_FOUND THEN
1248 RAISE;
1249 END;
1250
1251 elsif (l_next_approver(1).orig_system = 'FND') then --}{
1252 SELECT employee_id
1253 into l_next_approver_id
1254 FROM fnd_user
1255 WHERE user_id = l_next_approver(1).orig_system_id
1256 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
1257 end if; --}
1258
1259
1260 IF (g_asn_debug = 'Y') THEN
1261 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
1262 'person_id ' || l_next_approver_id);
1263 END IF;
1264
1265 IF (g_asn_debug = 'Y') THEN
1266 debug_log(FND_LOG.LEVEL_STATEMENT,
1267 l_api_name,'l_next_approver_id '
1268 || l_next_approver_id);
1269 debug_log(FND_LOG.LEVEL_STATEMENT,
1270 l_api_name,'l_insertion_type '
1271 || l_insertion_type);
1272 debug_log(FND_LOG.LEVEL_STATEMENT,
1273 l_api_name,'l_authority_type '
1274 || l_authority_type);
1275 END IF;
1276
1277 wf_engine.SetItemAttrText( itemtype => p_itemType,
1278 itemkey => p_itemkey,
1279 aname => 'AME_APPROVER_TYPE' ,
1280 avalue => l_next_approver(1).orig_system);
1281 wf_engine.SetItemAttrNumber ( itemtype => p_itemType,
1282 itemkey => p_itemkey,
1283 aname => 'APPROVER_EMPID',
1284 avalue => l_next_approver_id);
1285
1286
1287 wf_engine.SetItemAttrNumber ( itemtype => p_itemType,
1288 itemkey => p_itemkey,
1289 aname => 'FORWARD_TO_ID',
1290 avalue => l_next_approver_id);
1291
1292 wf_engine.SetItemAttrText( itemtype => p_itemType,
1293 itemkey => p_itemkey,
1294 aname => 'AME_INSERTION_TYPE' ,
1295 avalue => l_insertion_type);
1296
1297 wf_engine.SetItemAttrText( itemtype => p_itemType,
1298 itemkey => p_itemkey,
1299 aname => 'AME_AUTHORITY_TYPE' ,
1300 avalue => l_authority_type);
1301
1302 l_orig_system:= 'PER';
1303 l_progress := 'POS_WCAPPROVE_PVT.get_next_approver: 03.';
1304
1305 WF_DIRECTORY.GetUserName(l_orig_system,
1306 l_next_approver_id,
1307 l_next_approver_user_name,
1308 l_next_approver_disp_name);
1309
1310 IF (g_asn_debug = 'Y') THEN
1311 debug_log(FND_LOG.LEVEL_STATEMENT,
1312 l_api_name,'l_next_approver_user_name '
1313 || l_next_approver_user_name);
1314 debug_log(FND_LOG.LEVEL_STATEMENT,
1315 l_api_name,'l_next_approver_disp_name '
1316 || l_next_approver_disp_name);
1317 end if;
1318 wf_engine.SetItemAttrText( itemtype => p_itemType,
1319 itemkey => p_itemkey,
1320 aname => 'APPROVER_USER_NAME' ,
1321 avalue => l_next_approver_user_name);
1322
1323 wf_engine.SetItemAttrText( itemtype => p_itemType,
1324 itemkey => p_itemkey,
1325 aname => 'APPROVER_DISPLAY_NAME' ,
1326 avalue => l_next_approver_disp_name);
1327
1328 wf_engine.SetItemAttrText( itemtype => p_itemType,
1329 itemkey => p_itemkey,
1330 aname => 'FORWARD_TO_USERNAME' ,
1331 avalue => l_next_approver_user_name);
1332
1333 wf_engine.SetItemAttrText( itemtype => p_itemType,
1334 itemkey => p_itemkey,
1335 aname => 'FORWARD_TO_DISPLAY_NAME' ,
1336 avalue => l_next_approver_disp_name);
1337
1338
1339 x_resultout:='COMPLETE:'||'VALID_APPROVER';
1340 return;
1341 END IF; --}
1342
1343
1344 Exception
1345 when approver_exception then
1346 IF (g_asn_debug = 'Y') THEN
1347 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1348 l_api_name,
1349 'Exception in ame_util.getNextApprove '
1350 || l_progress);
1351 END IF;
1352 raise;
1353 when others then
1354 IF (g_asn_debug = 'Y') THEN
1355 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1356 l_api_name,'Exception in get_next_approve '
1357 || l_progress);
1358 END IF;
1359 raise;
1360 end get_next_approver;
1361
1362
1363 procedure Insert_Action_History(p_itemtype in varchar2,
1364 p_itemkey in varchar2,
1365 p_actid in number,
1366 p_funcmode in varchar2,
1367 x_resultout out NOCOPY varchar2) is
1368
1369 l_progress varchar2(200);
1370 l_approver_id number;
1371 l_shipment_header_id number;
1372 l_shipment_line_id number;
1373 l_count number;
1374 l_sequence_num number;
1375 l_api_name varchar2(50) := p_itemkey || ' Insert_Action_History';
1376
1377 /*
1378 cursor get_shipment_lines(l_shipment_header_id number) is
1379 select shipment_line_id
1380 from rcv_shipment_lines
1381 where shipment_header_id=l_shipment_header_id;
1382 */
1383
1384 CURSOR get_action_history IS
1385 SELECT object_id,
1386 object_type_code,
1387 object_sub_type_code,
1388 sequence_num,
1389 object_revision_num,
1390 request_id,
1391 program_application_id,
1392 program_date,
1393 program_id,
1394 last_update_date,
1395 employee_id
1396 FROM PO_ACTION_HISTORY
1397 WHERE object_type_code = 'WC'
1398 AND object_id = l_shipment_header_id
1399 AND sequence_num = l_sequence_num;
1400
1401 Recinfo get_action_history%ROWTYPE;
1402
1403 invalid_data exception;
1404 invalid_action exception;
1405
1406 BEGIN
1407
1408
1409 if (p_funcmode <> wf_engine.eng_run) then
1410 x_resultout := wf_engine.eng_null;
1411 return;
1412 end if;
1413
1414
1415
1416
1417 l_progress := 'POS_WCAPPROVE_PVT.Insert_Action_History: 01.';
1418 IF (g_asn_debug = 'Y') THEN
1419 debug_log(FND_LOG.LEVEL_STATEMENT,
1420 l_api_name,'Enter in Insert_Action_History '
1421 || l_progress);
1422 END IF;
1423
1424
1425
1426 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>p_itemtype,
1427 itemkey=>p_itemkey,
1428 aname=>'APPROVER_EMPID');
1429 IF (g_asn_debug = 'Y') THEN
1430 debug_log(FND_LOG.LEVEL_STATEMENT,
1431 l_api_name,'l_approver_id '
1432 || l_approver_id);
1433 END IF;
1434
1435 l_shipment_header_id:= wf_engine.GetItemAttrNumber(itemtype=>p_itemtype,
1436 itemkey=>p_itemkey,
1437 aname=>'WORK_CONFIRMATION_ID');
1438 IF (g_asn_debug = 'Y') THEN
1439 debug_log(FND_LOG.LEVEL_STATEMENT,
1440 l_api_name,'l_shipment_header_id '
1441 || l_shipment_header_id);
1442 END IF;
1443
1444
1445
1446 -- loop --{
1447 l_progress := 'POS_WCAPPROVE_PVT.Insert_Action_History: 04.';
1448
1449 SELECT count(*)
1450 INTO l_count
1451 FROM PO_ACTION_HISTORY
1452 WHERE object_type_code = 'WC'
1453 AND object_id = l_shipment_header_id
1454 AND action_code IS NULL;
1455
1456 IF (g_asn_debug = 'Y') THEN
1457 debug_log(FND_LOG.LEVEL_STATEMENT,
1458 l_api_name,'l_count '
1459 || l_count);
1460 END IF;
1461
1462
1463 IF (l_count > 1) THEN --{
1464
1465 RAISE invalid_action;
1466
1467 ELSE --}{
1468
1469 SELECT max(sequence_num)
1470 INTO l_sequence_num
1471 FROM PO_ACTION_HISTORY
1472 WHERE object_type_code = 'WC'
1473 AND object_id = l_shipment_header_id;
1474
1475 IF (g_asn_debug = 'Y') THEN
1476 debug_log(FND_LOG.LEVEL_STATEMENT,
1477 l_api_name,'l_sequence_num '
1478 || l_sequence_num);
1479 END IF;
1480
1481 OPEN get_action_history;
1482
1483 FETCH get_action_history INTO Recinfo;
1484
1485 IF (get_action_history%NOTFOUND) then
1486 IF (g_asn_debug = 'Y') THEN
1487 debug_log(FND_LOG.LEVEL_STATEMENT,
1488 l_api_name,
1489 'no_data_round ' );
1490 END IF;
1491
1492 RAISE NO_DATA_FOUND;
1493 END IF;
1494
1495 CLOSE get_action_history;
1496
1497 /*
1498 ** if it is the first call and it gets here
1499 ** it means there is an implicit forward. We
1500 ** want to update the first NULL row in POAH
1501 ** with FORWARD action
1502 */
1503
1504
1505 If l_count = 1 then
1506
1507
1508 IF (g_asn_debug = 'Y') THEN
1509 debug_log(FND_LOG.LEVEL_STATEMENT,
1510 l_api_name,
1511 'update Action history ' );
1512 END IF;
1513
1514 UpdatePOActionHistory(
1515 p_object_id => Recinfo.object_id,
1516 p_object_type_code => Recinfo.object_type_code,
1517 p_employee_id => Recinfo.employee_id,
1518 p_action_code => 'FORWARD',
1519 p_note => NULL,
1520 p_user_id => fnd_global.user_id,
1521 p_login_id => fnd_global.login_id);
1522
1523
1524 End if;
1525
1526
1527 If l_approver_id is null then --{
1528 raise invalid_data;
1529
1530 Else --}{
1531 IF (g_asn_debug = 'Y') THEN
1532 debug_log(FND_LOG.LEVEL_STATEMENT,
1533 l_api_name,
1534 'Insert Action history ' );
1535 END IF;
1536
1537
1538
1539 InsertPOActionHistory(
1540 p_object_id => Recinfo.object_id,
1541 p_object_type_code=>Recinfo.object_type_code,
1542 p_object_sub_type_code => NULL,
1543 p_sequence_num => Recinfo.sequence_num+1 ,
1544 p_action_code =>NULL ,
1545 p_action_date =>NULL ,
1546 p_employee_id => l_approver_id,
1547 p_approval_path_id => NULL ,
1548 p_note => NULL,
1549 p_object_revision_num => NULL,
1550 p_offline_code => NULL,
1551 p_request_id => Recinfo.request_id,
1552 p_program_application_id => Recinfo.program_application_id,
1553 p_program_id =>Recinfo.program_id ,
1554 p_program_date => Recinfo.program_date ,
1555 p_user_id => fnd_global.user_id ,
1556 p_login_id => fnd_global.login_id);
1557
1558 End if;--}
1559
1560
1561 END If; --}
1562
1563
1564
1565 --end loop;--}
1566 IF (g_asn_debug = 'Y') THEN
1567 debug_log(FND_LOG.LEVEL_STATEMENT,
1568 l_api_name,
1569 'Exit Insert Action history ' );
1570 END IF;
1571
1572 exception
1573 when invalid_action then
1574 IF (g_asn_debug = 'Y') THEN
1575 debug_log(FND_LOG.LEVEL_EXCEPTION,
1576 l_api_name,'invalid_action in Insert_Action_History '
1577 || l_progress);
1578 END IF;
1579 raise;
1580 when invalid_data then
1581 IF (g_asn_debug = 'Y') THEN
1582 debug_log(FND_LOG.LEVEL_EXCEPTION,
1583 l_api_name,'invalid_data in Insert_Action_History '
1584 || l_progress);
1585 END IF;
1586 raise;
1587 when others then
1588 IF (g_asn_debug = 'Y') THEN
1589 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1590 l_api_name,'Exception in Insert_Action_History '
1591 || l_progress);
1592 END IF;
1593 raise;
1594 end Insert_Action_History;
1595
1596 /* This procedure will be called when the approver approves the
1597 * document directly from the notification. This means that
1598 * he wants to approve all the shipment lines at one shot.
1599 * So set the approval status of all the shipment lines to
1600 * APPROVED.
1601 */
1602 procedure Approve_shipment_lines(p_itemtype in varchar2,
1603 p_itemkey in varchar2,
1604 p_actid in number,
1605 p_funcmode in varchar2,
1606 x_resultout out NOCOPY varchar2) is
1607 l_progress varchar2(200);
1608 l_shipment_header_id number;
1609 l_api_name varchar2(50) := p_itemkey || ' Approve_shipment_lines';
1610 l_note po_action_history.note%type;
1611 l_result varchar2(3);
1612
1613 l_notification_id number;
1614 CURSOR c_group_id (p_itemtype VARCHAR2,
1615 p_itemkey VARCHAR2,
1616 p_activity_name VARCHAR2) IS
1617 SELECT notification_id
1618 FROM wf_item_activity_statuses_v
1619 WHERE item_type = p_itemtype
1620 AND item_key = p_itemkey
1621 AND activity_name = p_activity_name
1622 ORDER BY activity_end_date DESC;
1623
1624 BEGIN
1625
1626 l_progress := 'POS_WCAPPROVE_PVT.Approve_shipment_lines: 01.';
1627 IF (g_asn_debug = 'Y') THEN
1628 debug_log(FND_LOG.LEVEL_STATEMENT,
1629 l_api_name,'Enter in Approve_shipment_lines '
1630 || l_progress);
1631 END IF;
1632
1633 if (p_funcmode <> wf_engine.eng_run) then
1634 x_resultout := wf_engine.eng_null;
1635 return;
1636 end if;
1637
1638 l_shipment_header_id := wf_engine.GetItemAttrNumber
1639 (itemtype => p_itemtype,
1640 itemkey => p_itemkey,
1641 aname => 'WORK_CONFIRMATION_ID');
1642
1643 OPEN c_group_id(p_itemtype, p_itemkey, 'WC_APPROVE');
1644
1645 FETCH c_group_id INTO l_notification_id;
1646 CLOSE c_group_id;
1647
1648 IF (g_asn_debug = 'Y') THEN
1649 debug_log(FND_LOG.LEVEL_STATEMENT,
1650 l_api_name,
1651 ' l_notification_id ' || l_notification_id);
1652 END IF;
1653
1654
1655 If (l_notification_id is not null) then
1656 SELECT attribute_value
1657 into l_note
1658 FROM wf_notification_attr_resp_v
1659 WHERE group_id = l_notification_id
1660 AND attribute_name = 'NOTE';
1661 end if;
1662
1663 IF (g_asn_debug = 'Y') THEN
1664 debug_log(FND_LOG.LEVEL_STATEMENT,
1665 l_api_name,
1666 ' l_note ' || l_note);
1667 END IF;
1668
1669 update_approval_status(p_shipment_header_id => l_shipment_header_id,
1670 p_note => l_note,
1671 p_approval_status => 'APPROVED',
1672 p_level => 'LINES',
1673 x_resultout => l_result);
1674
1675
1676
1677 IF (g_asn_debug = 'Y') THEN
1678 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_shipment_header_id ' || l_shipment_header_id);
1679 END IF;
1680 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1681 exception
1682 when others then
1683 IF (g_asn_debug = 'Y') THEN
1684 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1685 l_api_name,'Exception in Approve_shipment_lines '
1686 || l_progress);
1687 END IF;
1688 raise;
1689 end Approve_shipment_lines;
1690
1691
1692 /* This procedure will be called when the approver rejects the
1693 * document directly from the notification. This means that
1694 * he wants to approve all the shipment lines at one shot.
1695 * So set the approval status of all the shipment lines to
1696 * REJECTED.
1697 */
1698 procedure Reject_shipment_lines(p_itemtype in varchar2,
1699 p_itemkey in varchar2,
1700 p_actid in number,
1701 p_funcmode in varchar2,
1702 x_resultout out NOCOPY varchar2) is
1703 l_progress varchar2(200);
1704 l_shipment_header_id number;
1705 l_api_name varchar2(50) := p_itemkey || ' Reject_shipment_lines';
1706 l_note po_action_history.note%type;
1707 l_result varchar2(3);
1708
1709 l_notification_id number;
1710 CURSOR c_group_id (p_itemtype VARCHAR2,
1711 p_itemkey VARCHAR2,
1712 p_activity_name VARCHAR2) IS
1713 SELECT notification_id
1714 FROM wf_item_activity_statuses_v
1715 WHERE item_type = p_itemtype
1716 AND item_key = p_itemkey
1717 AND activity_name = p_activity_name
1718 ORDER BY activity_end_date DESC;
1719
1720 BEGIN
1721
1722 l_progress := 'POS_WCAPPROVE_PVT Reject_shipment_lines: 01.';
1723 IF (g_asn_debug = 'Y') THEN
1724 debug_log(FND_LOG.LEVEL_STATEMENT,
1725 l_api_name,'Enter in Reject_shipment_lines '
1726 || l_progress);
1727 END IF;
1728
1729 if (p_funcmode <> wf_engine.eng_run) then
1730 x_resultout := wf_engine.eng_null;
1731 return;
1732 end if;
1733
1734 l_shipment_header_id := wf_engine.GetItemAttrNumber
1735 (itemtype => p_itemtype,
1736 itemkey => p_itemkey,
1737 aname => 'WORK_CONFIRMATION_ID');
1738
1739 OPEN c_group_id(p_itemtype, p_itemkey, 'WC_APPROVE');
1740
1741 FETCH c_group_id INTO l_notification_id;
1742 CLOSE c_group_id;
1743
1744 IF (g_asn_debug = 'Y') THEN
1745 debug_log(FND_LOG.LEVEL_STATEMENT,
1746 l_api_name,
1747 ' l_notification_id ' || l_notification_id);
1748 END IF;
1749
1750
1751 If l_notification_id is not null then
1752 SELECT attribute_value
1753 into l_note
1754 FROM wf_notification_attr_resp_v
1755 WHERE group_id = l_notification_id
1756 AND attribute_name = 'NOTE';
1757 end if;
1758
1759 IF (g_asn_debug = 'Y') THEN
1760 debug_log(FND_LOG.LEVEL_STATEMENT,
1761 l_api_name,
1762 ' l_note ' || l_note);
1763 END IF;
1764
1765
1766
1767 update_approval_status(p_shipment_header_id => l_shipment_header_id,
1768 p_note => l_note,
1769 p_approval_status => 'REJECTED',
1770 p_level => 'LINES',
1771 x_resultout => l_result);
1772
1773
1774
1775 IF (g_asn_debug = 'Y') THEN
1776 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_shipment_header_id ' || l_shipment_header_id);
1777 END IF;
1778 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1779 exception
1780 when others then
1781 IF (g_asn_debug = 'Y') THEN
1782 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1783 l_api_name,'Exception in Reject_shipment_lines '
1784 || l_progress);
1785 END IF;
1786 raise;
1787 end Reject_shipment_lines;
1788
1789
1790 /* Get the status of all the lines of the Work Confirmation.
1791 * Even if one is rejected.
1792 */
1793 procedure Approve_OR_Reject(p_itemtype in varchar2,
1794 p_itemkey in varchar2,
1795 p_actid in number,
1796 p_funcmode in varchar2,
1797 x_resultout out NOCOPY varchar2) is
1798
1799 l_progress varchar2(200);
1800 l_shipment_header_id number;
1801 l_reject_lines number;
1802 l_api_name varchar2(50) := p_itemkey || ' approve_or_reject';
1803
1804 BEGIN
1805
1806 l_progress := 'POS_WCAPPROVE_PVT.Approve_OR_Reject: 01.';
1807 IF (g_asn_debug = 'Y') THEN
1808 debug_log(FND_LOG.LEVEL_STATEMENT,
1809 l_api_name,'Enter in Approve_OR_Reject '
1810 || l_progress);
1811 END IF;
1812
1813 if (p_funcmode <> wf_engine.eng_run) then
1814 x_resultout := wf_engine.eng_null;
1815 return;
1816 end if;
1817
1818 l_shipment_header_id := wf_engine.GetItemAttrNumber
1819 (itemtype => p_itemtype,
1820 itemkey => p_itemkey,
1821 aname => 'WORK_CONFIRMATION_ID');
1822
1823 IF (g_asn_debug = 'Y') THEN
1824 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_shipment_header_id ' || l_shipment_header_id);
1825 END IF;
1826
1827
1828 select count(*)
1829 into l_reject_lines
1830 from rcv_shipment_lines
1831 where shipment_header_id = l_shipment_header_id
1832 and approval_status = 'REJECTED';
1833
1834 IF (g_asn_debug = 'Y') THEN
1835 debug_log(FND_LOG.LEVEL_STATEMENT,
1836 l_api_name,'l_reject_lines ' || l_reject_lines);
1837 END IF;
1838
1839 If (l_reject_lines > 0) then
1840 x_resultout := wf_engine.eng_completed || ':' ||
1841 'REJECT';
1842 else
1843 x_resultout := wf_engine.eng_completed || ':' ||
1844 'APPROVE';
1845
1846 end if;
1847
1848
1849 exception
1850 when others then
1851 IF (g_asn_debug = 'Y') THEN
1852 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1853 l_api_name,'Exception in Approve_OR_Reject '
1854 || l_progress);
1855 END IF;
1856 raise;
1857 end Approve_OR_Reject;
1858
1859 procedure Update_Approval_List_Response
1860 (p_itemtype in varchar2,
1861 p_itemkey in varchar2,
1862 p_actid in number,
1863 p_funcmode in varchar2,
1864 x_resultout out NOCOPY varchar2) IS
1865
1866 CURSOR c_group_id (p_itemtype VARCHAR2,
1867 p_itemkey VARCHAR2,
1868 p_activity_name VARCHAR2) IS
1869 SELECT notification_id
1870 FROM wf_item_activity_statuses_v
1871 WHERE item_type = p_itemtype
1872 AND item_key = p_itemkey
1873 AND activity_name = p_activity_name
1874 ORDER BY activity_end_date DESC;
1875
1876 CURSOR c_canceled_notif (p_notif_id number) IS
1877 SELECT '1'
1878 FROM WF_NOTIFICATIONS
1879 WHERE notification_id = p_notif_id
1880 AND status = 'CANCELED';
1881
1882 CURSOR c_response(p_group_id number) IS
1883 SELECT recipient_role, attribute_value
1884 FROM wf_notification_attr_resp_v
1885 WHERE group_id = p_group_id
1886 AND attribute_name = 'RESULT';
1887
1888 CURSOR c_response_note(p_group_id number) IS
1889 SELECT attribute_value
1890 FROM wf_notification_attr_resp_v
1891 WHERE group_id = p_group_id
1892 AND attribute_name = 'NOTE';
1893
1894 CURSOR c_responderid(p_responder VARCHAR2) IS
1895 SELECT nvl((wfu.orig_system_id), -9996)
1896 FROM wf_users wfu
1897 WHERE wfu.name = p_responder
1898 AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
1899
1900 l_api_name varchar2(50) := p_itemkey || ' update_approval_list_response';
1901 l_responder wf_notifications.responder%TYPE;
1902 l_recipient_role wf_notifications.recipient_role%TYPE;
1903
1904 l_group_id NUMBER;
1905 l_role wf_notifications.recipient_role%TYPE;
1906 l_response VARCHAR2(2000);
1907
1908 l_approver_id NUMBER := NULL;
1909 l_orig_system wf_users.orig_system%TYPE;
1910 l_responder_user_name wf_users.name%TYPE;
1911 l_responder_disp_name wf_users.display_name%TYPE;
1912
1913 is_notif_canceled VARCHAR2(2);
1914 l_doc_string varchar2(200);
1915 l_preparer_user_name wf_users.name%TYPE;
1916 l_response_end_date date;
1917 l_responder_id number;
1918 l_progress varchar2(300);
1919 l_insertion_type VARCHAR2(30);
1920 l_authority_type VARCHAR2(30);
1921 l_forward_to_id NUMBER := NULL;
1922 l_transaction_type varchar2(100);
1923 l_shipment_header_id number;
1924 l_application_id number := '201'; -- use PO application id.
1925 l_current_approver ame_util.approverRecord2;
1926 l_approver_type varchar2(10);
1927 l_default_approver varchar2(30);
1928 l_next_approver_disp_name varchar2(100);
1929 l_reject_lines number;
1930
1931
1932 begin
1933 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response: 01.';
1934 IF (g_asn_debug = 'Y') THEN
1935 debug_log(FND_LOG.LEVEL_STATEMENT,
1936 l_api_name,
1937 'Enter in Update_Approval_List_Response '
1938 || l_progress);
1939 END IF;
1940
1941 if (p_funcmode <> wf_engine.eng_run) then
1942 x_resultout := wf_engine.eng_null;
1943 return;
1944 end if;
1945
1946
1947 wf_engine.SetItemAttrNumber ( itemtype => p_itemType,
1948 itemkey => p_itemkey,
1949 aname => 'RESPONDER_USER_ID',
1950 avalue => fnd_global.USER_ID);
1951
1952 wf_engine.SetItemAttrNumber ( itemtype => p_itemType,
1953 itemkey => p_itemkey,
1954 aname => 'RESPONDER_RESP_ID',
1955 avalue => fnd_global.RESP_ID);
1956
1957 wf_engine.SetItemAttrNumber ( itemtype => p_itemType,
1958 itemkey => p_itemkey,
1959 aname => 'RESPONDER_APPL_ID',
1960 avalue => fnd_global.RESP_APPL_ID);
1961
1962 OPEN c_group_id(p_itemtype, p_itemkey, 'WC_APPROVE');
1963
1964 FETCH c_group_id INTO l_group_id;
1965 CLOSE c_group_id;
1966
1967 IF (g_asn_debug = 'Y') THEN
1968 debug_log(FND_LOG.LEVEL_STATEMENT,
1969 l_api_name,
1970 ' l_group_id ' || l_group_id);
1971 END IF;
1972
1973
1974 IF l_group_id is NOT NULL THEN --{
1975 OPEN c_response(l_group_id);
1976 FETCH c_response INTO l_role, l_response;
1977 CLOSE c_response;
1978
1979 IF (g_asn_debug = 'Y') THEN
1980 debug_log(FND_LOG.LEVEL_STATEMENT,
1981 l_api_name,
1982 ' l_role ' || l_role);
1983 debug_log(FND_LOG.LEVEL_STATEMENT,
1984 l_api_name,
1985 ' l_response '
1986 || substr(l_response,1,50));
1987 END IF;
1988
1989 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
1990 || ': 02.';
1991
1992
1993 SELECT wfn.responder, wfn.recipient_role, wfn.end_date
1994 INTO l_responder, l_recipient_role, l_response_end_date
1995 FROM wf_notifications wfn
1996 WHERE wfn.notification_id = l_group_id;
1997
1998 IF (g_asn_debug = 'Y') THEN
1999 debug_log(FND_LOG.LEVEL_STATEMENT,
2000 l_api_name,
2001 ' l_responder ' || l_responder);
2002 debug_log(FND_LOG.LEVEL_STATEMENT,
2003 l_api_name,
2004 ' l_recipient_role '
2005 || l_recipient_role);
2006 debug_log(FND_LOG.LEVEL_STATEMENT,
2007 l_api_name,
2008 ' l_response_end_date '
2009 || to_char(l_response_end_date,'DD-MON-YYYY'));
2010 end if;
2011
2012 OPEN c_responderid(l_responder);
2013 FETCH c_responderid INTO l_responder_id;
2014
2015 IF (g_asn_debug = 'Y') THEN
2016 debug_log(FND_LOG.LEVEL_STATEMENT,
2017 l_api_name,
2018 ' l_responder_id ' || l_responder_id);
2019 end if;
2020
2021 IF c_responderid%NOTFOUND THEN --{
2022
2023 CLOSE c_responderid;
2024 OPEN c_responderid(l_recipient_role);
2025 FETCH c_responderid INTO l_responder_id;
2026 CLOSE c_responderid;
2027
2028 IF (g_asn_debug = 'Y') THEN
2029 debug_log(FND_LOG.LEVEL_STATEMENT,
2030 l_api_name,
2031 ' l_responder_id1 ' || l_responder_id);
2032 end if;
2033 End if;--}
2034
2035
2036 IF (c_responderid%ISOPEN) THEN
2037 CLOSE c_responderid;
2038 END IF;
2039
2040 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
2041 || ':02.';
2042 wf_engine.SetItemAttrNumber(itemtype => p_itemType,
2043 itemkey => p_itemkey,
2044 aname => 'RESPONDER_ID',
2045 avalue => l_responder_id);
2046
2047 l_orig_system:= 'PER';
2048
2049 WF_DIRECTORY.GetUserName(l_orig_system,
2050 l_responder_Id,
2051 l_responder_user_name,
2052 l_responder_disp_name);
2053
2054 IF (g_asn_debug = 'Y') THEN
2055 debug_log(FND_LOG.LEVEL_STATEMENT,
2056 l_api_name,
2057 ' l_responder_user_name '
2058 || l_responder_user_name);
2059 debug_log(FND_LOG.LEVEL_STATEMENT,
2060 l_api_name,
2061 ' l_responder_disp_name '
2062 || l_responder_disp_name);
2063 end if;
2064
2065 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
2066 || ': 03.';
2067 wf_engine.SetItemAttrText( itemtype => p_itemType,
2068 itemkey => p_itemkey,
2069 aname => 'RESPONDER_USER_NAME' ,
2070 avalue => l_responder_user_name);
2071
2072 wf_engine.SetItemAttrText( itemtype => p_itemType,
2073 itemkey => p_itemkey,
2074 aname => 'RESPONDER_DISPLAY_NAME' ,
2075 avalue => l_responder_disp_name);
2076
2077 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
2078 || ': 04.';
2079
2080 /* We cannot have response as forward. Need to remove it later
2081 IF (INSTR(l_response, 'FORWARD') > 0) THEN
2082 l_forward_to_id :=
2083 wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
2084 itemkey => p_itemkey,
2085 aname => 'FORWARD_TO_ID');
2086 END IF;
2087 IF (g_asn_debug = 'Y') THEN
2088 debug_log(FND_LOG.LEVEL_STATEMENT,
2089 l_api_name,
2090 ' l_forward_to_id '
2091 || l_forward_to_id);
2092 END IF;
2093 */
2094
2095 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
2096 || ': 05.';
2097
2098 END IF; -- }c_group_id
2099
2100
2101 l_shipment_header_id := wf_engine.GetItemAttrNumber
2102 (itemtype => p_itemtype,
2103 itemkey => p_itemkey,
2104 aname => 'WORK_CONFIRMATION_ID');
2105
2106 IF (g_asn_debug = 'Y') THEN
2107 debug_log(FND_LOG.LEVEL_STATEMENT,
2108 l_api_name,
2109 ' l_shipment_header_id '
2110 || l_shipment_header_id);
2111 END IF;
2112
2113 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>p_itemtype,
2114 itemkey=>p_itemkey,
2115 aname=>'APPROVER_EMPID');
2116
2117 IF (g_asn_debug = 'Y') THEN
2118 debug_log(FND_LOG.LEVEL_STATEMENT,
2119 l_api_name,
2120 ' l_approver_id '
2121 || l_approver_id);
2122 END IF;
2123
2124 l_insertion_type := wf_engine.GetItemAttrText(itemtype => p_itemtype,
2125 itemkey => p_itemkey,
2126 aname => 'AME_INSERTION_TYPE');
2127
2128 IF (g_asn_debug = 'Y') THEN
2129 debug_log(FND_LOG.LEVEL_STATEMENT,
2130 l_api_name,
2131 ' l_insertion_type '
2132 || l_insertion_type);
2133 END IF;
2134
2135 l_authority_type := wf_engine.GetItemAttrText(itemtype => p_itemtype,
2136 itemkey => p_itemkey,
2137 aname => 'AME_AUTHORITY_TYPE');
2138
2139 IF (g_asn_debug = 'Y') THEN
2140 debug_log(FND_LOG.LEVEL_STATEMENT,
2141 l_api_name,
2142 ' l_authority_type '
2143 || l_authority_type);
2144 END IF;
2145
2146 l_transaction_type := wf_engine.GetItemAttrText (itemtype => p_itemtype,
2147 itemkey => p_itemkey,
2148 aname => 'AME_TRANSACTION_TYPE');
2149
2150 IF (g_asn_debug = 'Y') THEN
2151 debug_log(FND_LOG.LEVEL_STATEMENT,
2152 l_api_name,
2153 ' l_transaction_type '
2154 || l_transaction_type);
2155 END IF;
2156
2157 l_approver_type := po_wf_util_pkg.GetItemAttrText
2158 (itemtype => p_itemtype,
2159 itemkey => p_itemkey,
2160 aname => 'AME_APPROVER_TYPE');
2161 IF (g_asn_debug = 'Y') THEN
2162 debug_log(FND_LOG.LEVEL_STATEMENT,
2163 l_api_name,
2164 ' l_approver_type '
2165 || l_approver_type);
2166 END IF;
2167
2168 l_default_approver :=
2169 wf_engine.GetItemAttrText (itemtype => p_itemtype,
2170 itemkey => p_itemkey,
2171 aname => 'DEFAULT_APPROVER');
2172
2173 IF (g_asn_debug = 'Y') THEN
2174 debug_log(FND_LOG.LEVEL_STATEMENT,
2175 l_api_name,'l_default_approver '
2176 || l_default_approver);
2177 END IF;
2178
2179 If l_default_approver = 'BUYER' then --{
2180 IF (g_asn_debug = 'Y') THEN
2181 debug_log(FND_LOG.LEVEL_STATEMENT,
2182 l_api_name,'Approved by buyer. Dont call ame api ');
2183 END IF;
2184 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2185 return;
2186
2187 end if; --}
2188
2189 if (l_approver_type = 'POS') then
2190 l_current_approver.orig_system := 'POS';
2191 elsif (l_approver_type = 'FND') then
2192 l_current_approver.orig_system := 'FND';
2193 else
2194 l_current_approver.orig_system := 'PER';
2195 end if;
2196
2197 IF (g_asn_debug = 'Y') THEN
2198 debug_log(FND_LOG.LEVEL_STATEMENT,
2199 l_api_name,
2200 ' orig_system '
2201 || l_current_approver.orig_system);
2202 END IF;
2203
2204 l_current_approver.orig_system_id := l_approver_id;
2205
2206 if( l_response = 'APPROVE') then
2207 l_current_approver.approval_status := ame_util.approvedStatus;
2208 elsif( l_response = 'REJECT') then
2209 l_current_approver.approval_status := ame_util.rejectStatus;
2210 elsif( l_response = 'TIMEOUT') then
2211 l_current_approver.approval_status := ame_util.noResponseStatus;
2212 else /* Can be approved/rejected from iSP UI */
2213 select count(*)
2214 into l_reject_lines
2215 from rcv_shipment_lines
2216 where shipment_header_id = l_shipment_header_id
2217 and approval_status = 'REJECTED';
2218
2219 If (l_reject_lines > 0) then
2220 l_current_approver.approval_status := ame_util.rejectStatus;
2221 else
2222 l_current_approver.approval_status := ame_util.approvedStatus;
2223 end if;
2224
2225
2226 end if;
2227
2228
2229 /*
2230 Bug 7120431 , To get the user name from wf_users we need to pass orig_system as 'PER' irresepective of
2231 whether we user Employee-Supervisor hierarchy or Positional hierarchy .
2232 So passing 'PER' while in the below call.
2233 */
2234
2235 WF_DIRECTORY.GetUserName('PER',
2236 l_current_approver.orig_system_id,
2237 l_current_approver.name,
2238 l_next_approver_disp_name);
2239
2240
2241
2242 IF (g_asn_debug = 'Y') THEN
2243 debug_log(FND_LOG.LEVEL_STATEMENT,
2244 l_api_name,
2245 ' name '
2246 || l_current_approver.name);
2247 END IF;
2248
2249 IF l_current_approver.name IS NULL THEN
2250 raise_application_error(-20001,
2251 'Record Not Found in WF_ROLES for the orig_system_id :' ||
2252 l_current_approver.orig_system_id || ' -- orig_system :' || l_current_approver.orig_system );
2253 END IF;
2254
2255
2256 ame_api2.updateApprovalStatus(applicationIdIn=>l_application_Id, transactionIdIn=>l_shipment_header_id,
2257 approverIn=>l_current_approver,
2258 transactionTypeIn=>l_transaction_type);
2259
2260
2261 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2262 RETURN;
2263 Exception
2264 when others then
2265 IF (g_asn_debug = 'Y') THEN
2266 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2267 l_api_name,'Exception in update_approval_list_response '
2268 || l_progress);
2269 END IF;
2270 raise;
2271 end Update_Approval_List_Response;
2272
2273 procedure Update_Action_History_Approve
2274 (p_itemtype in varchar2,
2275 p_itemkey in varchar2,
2276 p_actid in number,
2277 p_funcmode in varchar2,
2278 x_resultout out NOCOPY varchar2) IS
2279 l_progress varchar2(300);
2280 l_api_name varchar2(50) := p_itemkey || ' update_action_history_approve';
2281 begin
2282 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_History_Approve: 01.';
2283 IF (g_asn_debug = 'Y') THEN
2284 debug_log(FND_LOG.LEVEL_STATEMENT,
2285 l_api_name,
2286 'Enter in Update_Action_History_Approve '
2287 || l_progress);
2288 END IF;
2289
2290 if (p_funcmode <> wf_engine.eng_run) then
2291 x_resultout := wf_engine.eng_null;
2292 return;
2293 end if;
2294
2295 Update_Action_History(p_itemtype => p_itemtype,
2296 p_itemkey => p_itemkey,
2297 p_action_code => 'APPROVE');
2298
2299 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2300
2301 IF (g_asn_debug = 'Y') THEN
2302 debug_log(FND_LOG.LEVEL_STATEMENT,
2303 l_api_name,
2304 'Leave in Update_Action_History_Approve '
2305 || l_progress);
2306 END IF;
2307
2308 return;
2309
2310
2311 Exception
2312 when others then
2313 IF (g_asn_debug = 'Y') THEN
2314 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2315 l_api_name,'Exception in update_action_history_approve '
2316 || l_progress);
2317 END IF;
2318 raise;
2319 end Update_Action_History_Approve;
2320
2321 procedure Update_Action_History_Reject
2322 (p_itemtype in varchar2,
2323 p_itemkey in varchar2,
2324 p_actid in number,
2325 p_funcmode in varchar2,
2326 x_resultout out NOCOPY varchar2) IS
2327 l_progress varchar2(300);
2328 l_api_name varchar2(50) := p_itemkey || ' update_action_history_Reject';
2329 begin
2330 l_progress := 'POS_WCAPPROVE_PVT.Update_action_history_reject: 01.';
2331 IF (g_asn_debug = 'Y') THEN
2332 debug_log(FND_LOG.LEVEL_STATEMENT,
2333 l_api_name,
2334 'Enter in Update_Action_History_Reject '
2335 || l_progress);
2336 END IF;
2337
2338 if (p_funcmode <> wf_engine.eng_run) then
2339 x_resultout := wf_engine.eng_null;
2340 return;
2341 end if;
2342
2343 Update_Action_History(p_itemtype => p_itemtype,
2344 p_itemkey => p_itemkey,
2345 p_action_code => 'REJECT');
2346
2347 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2348
2349 IF (g_asn_debug = 'Y') THEN
2350 debug_log(FND_LOG.LEVEL_STATEMENT,
2351 l_api_name,
2352 'Leave in Update_Action_History_Reject '
2353 || l_progress);
2354 END IF;
2355
2356 return;
2357
2358
2359 Exception
2360 when others then
2361 IF (g_asn_debug = 'Y') THEN
2362 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2363 l_api_name,'Exception in update_action_history_Reject '
2364 || l_progress);
2365 END IF;
2366 raise;
2367 end Update_Action_History_REJECT;
2368
2369
2370
2371 procedure Update_Action_History
2372 (p_itemtype in varchar2,
2373 p_itemkey in varchar2,
2374 p_action_code in varchar2) IS
2375
2376 CURSOR c_responderid(l_responder VARCHAR2) IS
2377 SELECT nvl((wfu.orig_system_id), -9996)
2378 FROM wf_users wfu
2379 WHERE wfu.name = l_responder
2380 AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
2381
2382
2383 /*
2384 cursor get_shipment_lines(l_shipment_header_id number) is
2385 select rsl.shipment_line_id ,
2386 nvl(rsl.comments,rsh.comments),
2387 rsl.approval_status
2388 from rcv_shipment_lines rsl,
2389 rcv_shipment_headers rsh
2390 where rsh.shipment_header_id = l_shipment_header_id
2391 and rsh.shipment_header_id= rsl.shipment_header_id;
2392 */
2393
2394 l_api_name varchar2(50) := p_itemkey || ' update_action_history';
2395 l_shipment_header_id number;
2396 l_shipment_line_id number;
2397 l_progress varchar2(300);
2398 l_notification_id number;
2399 l_comments varchar2(240) := null;
2400 l_original_recipient_id number;
2401 l_original_recipient wf_notifications.original_recipient%TYPE;
2402 l_recipient_role wf_notifications.recipient_role%TYPE;
2403 l_more_info_role wf_notifications.more_info_role%TYPE;
2404 l_responder_id number;
2405 l_more_origsys wf_roles.orig_system%TYPE;
2406 l_more_origsysid wf_roles.orig_system_id%TYPE := null;
2407 l_responder wf_notifications.responder%TYPE;
2408
2409
2410 begin
2411 l_progress := 'POS_WCAPPROVE_PVT.Update_Action_history: 01.';
2412 IF (g_asn_debug = 'Y') THEN
2413 debug_log(FND_LOG.LEVEL_STATEMENT,
2414 l_api_name,
2415 'Enter in Update_Action_History '
2416 || l_progress);
2417 END IF;
2418
2419
2420 l_shipment_header_id := wf_engine.GetItemAttrNumber
2421 (itemtype => p_itemtype,
2422 itemkey => p_itemkey,
2423 aname => 'WORK_CONFIRMATION_ID');
2424
2425 IF (g_asn_debug = 'Y') THEN
2426 debug_log(FND_LOG.LEVEL_STATEMENT,
2427 l_api_name,
2428 ' l_shipment_header_id '
2429 || l_shipment_header_id);
2430 END IF;
2431
2432
2433 l_progress := 'POS_WCAPPROVE_PVT.Update_Action_history: 02.';
2434 SELECT nvl(max(wf.notification_id), -9995)
2435 into l_notification_id
2436 FROM wf_notifications wf,
2437 wf_item_activity_statuses wias
2438 WHERE wias.item_type = p_itemtype
2439 and wias.item_key = p_itemkey
2440 and wias.notification_id = wf.group_id;
2441
2442 IF (g_asn_debug = 'Y') THEN
2443 debug_log(FND_LOG.LEVEL_STATEMENT,
2444 l_api_name,
2445 ' l_notification_id '
2446 || l_notification_id);
2447 END IF;
2448
2449
2450 l_progress := 'POS_WCAPPROVE_PVT.Update_Action_history: 03.';
2451 SELECT wfn.responder, wfn.recipient_role,
2452 wfn.original_recipient, wfn.more_info_role
2453 INTO l_responder, l_recipient_role,
2454 l_original_recipient, l_more_info_role
2455 FROM wf_notifications wfn
2456 WHERE wfn.notification_id = l_notification_id;
2457
2458 IF (g_asn_debug = 'Y') THEN
2459 debug_log(FND_LOG.LEVEL_STATEMENT,
2460 l_api_name,
2461 ' l_responder ' || l_responder);
2462 debug_log(FND_LOG.LEVEL_STATEMENT,
2463 l_api_name,
2464 ' l_recipient_role ' || l_recipient_role);
2465 debug_log(FND_LOG.LEVEL_STATEMENT,
2466 l_api_name,
2467 ' l_original_recipient '||l_original_recipient);
2468 debug_log(FND_LOG.LEVEL_STATEMENT,
2469 l_api_name,
2470 ' l_more_info_role ' || l_more_info_role);
2471 END IF;
2472
2473
2474 OPEN c_responderid(l_responder);
2475 FETCH c_responderid INTO l_responder_id;
2476
2477
2478 IF c_responderid%NOTFOUND THEN
2479
2480 CLOSE c_responderid;
2481 OPEN c_responderid(l_recipient_role);
2482 FETCH c_responderid INTO l_responder_id;
2483 CLOSE c_responderid;
2484
2485 END IF;
2486
2487 IF (c_responderid%ISOPEN) THEN
2488 CLOSE c_responderid;
2489 END IF;
2490
2491
2492 OPEN c_responderid(l_original_recipient);
2493 FETCH c_responderid INTO l_original_recipient_id;
2494
2495 IF c_responderid%NOTFOUND THEN--{
2496
2497 CLOSE c_responderid;
2498
2499 SELECT wfu.orig_system_id
2500 INTO l_original_recipient_id
2501 FROM wf_roles wfu
2502 WHERE wfu.name = l_original_recipient
2503 AND wfu.orig_system NOT IN ('POS', 'ENG_LIST', 'CUST_CONT');
2504
2505 END IF; --}
2506
2507 IF (c_responderid%ISOPEN) THEN
2508 CLOSE c_responderid;
2509 END IF;
2510
2511 if (l_more_info_role is not null) then
2512 Wf_Directory.GetRoleOrigSysInfo
2513 (l_more_info_role,
2514 l_more_origsys,
2515 l_more_origsysid);
2516 end if;
2517
2518 --loop --{
2519 l_progress := 'POS_WCAPPROVE_PVT.update_approval_history: 04.';
2520
2521
2522 IF (g_asn_debug = 'Y') THEN
2523 debug_log(FND_LOG.LEVEL_STATEMENT,
2524 l_api_name,'l_comments ' ||
2525 l_comments);
2526 END IF;
2527
2528
2529 UpdateActionHistory(l_more_origsysid,
2530 l_original_recipient_id,
2531 l_responder_id,
2532 FALSE,
2533 p_action_code,
2534 l_comments,
2535 l_shipment_header_id);
2536
2537 IF (g_asn_debug = 'Y') THEN
2538 debug_log(FND_LOG.LEVEL_STATEMENT,
2539 l_api_name,'After updateactionhistory' ||
2540 l_shipment_header_id);
2541 end if;
2542 --end loop; --}
2543
2544 IF (g_asn_debug = 'Y') THEN
2545 debug_log(FND_LOG.LEVEL_STATEMENT,
2546 l_api_name,'Leave update_action_history' );
2547 end if;
2548
2549 return;
2550
2551 EXCEPTION
2552 when others then
2553 IF (g_asn_debug = 'Y') THEN
2554 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2555 l_api_name,'Exception in Update_Action_History '
2556 || l_progress);
2557 END IF;
2558 raise;
2559
2560 END Update_Action_History;
2561
2562 PROCEDURE UpdateActionHistory(p_more_info_id IN NUMBER,
2563 p_original_recipient_id IN NUMBER,
2564 p_responder_id IN NUMBER,
2565 p_last_approver IN BOOLEAN,
2566 p_action_code IN VARCHAR2,
2567 p_comments IN VARCHAR2,
2568 p_shipment_header_id IN NUMBER)
2569 IS
2570
2571 -- pragma AUTONOMOUS_TRANSACTION;
2572 l_api_name varchar2(50) := 'UpdateActionHistory';
2573 l_progress varchar2(300);
2574 l_sequence_num number;
2575 l_note VARCHAR2(4000);
2576
2577 CURSOR get_action_history(l_sequence_num number) IS
2578 SELECT ph.action_code action_code ,
2579 ph.object_type_code object_type_code ,
2580 ph.sequence_num sequence_num,
2581 ph.approval_path_id approval_path_id,
2582 ph.request_id request_id ,
2583 ph.program_application_id program_application_id,
2584 ph.program_date program_date ,
2585 ph.program_id program_id ,
2586 ph.last_update_date last_update_date,
2587 ph.object_id object_id,
2588 ph.employee_id employee_id
2589 FROM
2590 rcv_shipment_headers rsh,
2591 po_action_history ph
2592 WHERE rsh.shipment_header_id = ph.object_id
2593 and rsh.shipment_header_id=p_shipment_header_id
2594 and ph.sequence_num = l_sequence_num;
2595
2596 Recinfo get_action_history%ROWTYPE;
2597
2598 BEGIN
2599
2600 l_progress := 'POS_WCAPPROVE_PVT.UpdateActionHistory: 01.';
2601 IF (g_asn_debug = 'Y') THEN
2602 debug_log(FND_LOG.LEVEL_STATEMENT,
2603 l_api_name,'Enter in UpdateActionHistory '
2604 || l_progress);
2605 debug_log(FND_LOG.LEVEL_STATEMENT,
2606 l_api_name,'p_shipment_header_id '
2607 || p_shipment_header_id);
2608 END IF;
2609
2610
2611 SELECT max(sequence_num)
2612 INTO l_sequence_num
2613 FROM PO_ACTION_HISTORY
2614 WHERE object_type_code = 'WC'
2615 AND object_id = p_shipment_header_id;
2616
2617 OPEN get_action_history(l_sequence_num);
2618
2619 FETCH get_action_history INTO Recinfo;
2620
2621 IF (get_action_history%NOTFOUND) then
2622 RAISE NO_DATA_FOUND;
2623 END IF;
2624
2625 CLOSE get_action_history;
2626
2627
2628 if ( (Recinfo.action_code is not null) and
2629 (p_action_code not in ('APPROVE','REJECT'))) then --{
2630
2631 /* Add a blank line if the last line is not blank.
2632 */
2633 IF (g_asn_debug = 'Y') THEN
2634 debug_log(FND_LOG.LEVEL_STATEMENT,
2635 l_api_name,
2636 'Before call to InsertPOActionHistory ');
2637 End if;
2638
2639
2640 InsertPOActionHistory(
2641 p_object_id => Recinfo.object_id,
2642 p_object_type_code=>Recinfo.object_type_code,
2643 p_object_sub_type_code => NULL,
2644 p_sequence_num => Recinfo.sequence_num+1 ,
2645 p_action_code =>NULL ,
2646 p_action_date =>NULL ,
2647 p_employee_id => p_original_recipient_id,
2648 p_approval_path_id => NULL ,
2649 p_note => NULL,
2650 p_object_revision_num => NULL,
2651 p_offline_code => NULL,
2652 p_request_id => Recinfo.request_id,
2653 p_program_application_id => Recinfo.program_application_id,
2654 p_program_id =>Recinfo.program_id ,
2655 p_program_date => Recinfo.program_date ,
2656 p_user_id => fnd_global.user_id ,
2657 p_login_id => fnd_global.login_id);
2658
2659
2660 end if; --}
2661
2662 IF (p_responder_id <> -9996) THEN--{
2663
2664 /** the logic to handle re-assignment is in
2665 ** post notification function so that the update
2666 ** to action history can be viewed at the moment of
2667 ** reassignment. The following code is used to handle
2668 ** request for more info:
2669 ** 1. at the moment an approver requests for more info,
2670 ** action history is updated (performed within post
2671 ** notification)
2672 ** 2. if the approver approve/reject the requisition
2673 ** before the more info request is responded
2674 ** then we need to update the action history
2675 ** to reflect 'no action' from the more info
2676 */
2677 IF (p_more_info_id is not null) THEN --{
2678
2679
2680 /*
2681 ** update the original NULL row for the
2682 ** original approver with
2683 ** action code of 'NO ACTION'
2684 */
2685
2686
2687 UpdatePOActionHistory(
2688 p_object_id => Recinfo.object_id,
2689 p_object_type_code => Recinfo.object_type_code,
2690 p_employee_id => p_more_info_id,
2691 p_action_code => 'NO ACTION',
2692 p_note => NULL,
2693 p_user_id => fnd_global.user_id,
2694 p_login_id => fnd_global.login_id);
2695
2696
2697
2698 /*
2699 ** insert a new NULL row into PO_ACTION_HISTORY for
2700 ** the new approver
2701 */
2702
2703
2704 InsertPOActionHistory(
2705 p_object_id => Recinfo.object_id,
2706 p_object_type_code=>Recinfo.object_type_code,
2707 p_object_sub_type_code => NULL,
2708 p_sequence_num => Recinfo.sequence_num+1 ,
2709 p_action_code =>NULL ,
2710 p_action_date =>NULL ,
2711 p_employee_id => p_responder_id,
2712 p_approval_path_id => NULL ,
2713 p_note => NULL,
2714 p_object_revision_num => NULL,
2715 p_offline_code => NULL,
2716 p_request_id => Recinfo.request_id,
2717 p_program_application_id => Recinfo.program_application_id,
2718 p_program_id =>Recinfo.program_id ,
2719 p_program_date => Recinfo.program_date ,
2720 p_user_id => fnd_global.user_id ,
2721 p_login_id => fnd_global.login_id);
2722
2723
2724 end if; --}
2725
2726
2727 end if; --}
2728
2729 l_progress := 'POS_WCAPPROVE_PVT.UpdateActionHistory: 02.';
2730
2731 IF (not p_last_approver) THEN
2732
2733 /**
2734 ** update pending row of action history with approval action
2735 */
2736 IF (g_asn_debug = 'Y') THEN
2737 debug_log(FND_LOG.LEVEL_STATEMENT,
2738 l_api_name,
2739 'Before call to UpdatePOActionHistory '
2740 ||l_progress);
2741 End if;
2742
2743 UpdatePOActionHistory(
2744 p_object_id => Recinfo.object_id,
2745 p_object_type_code => Recinfo.object_type_code,
2746 p_employee_id => Recinfo.employee_id,
2747 p_action_code => p_action_code,
2748 p_note =>substrb(p_comments,1,4000),
2749 p_user_id => fnd_global.user_id,
2750 p_login_id => fnd_global.login_id);
2751
2752
2753 END IF;
2754
2755
2756 EXCEPTION
2757 when no_data_found then
2758 IF (g_asn_debug = 'Y') THEN
2759 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2760 l_api_name,
2761 'No data found in UpdateActionHistory '
2762 || l_progress);
2763 END IF;
2764 raise;
2765
2766 when others then
2767 IF (g_asn_debug = 'Y') THEN
2768 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2769 l_api_name,'Exception in UpdateActionHistory '
2770 || l_progress);
2771 END IF;
2772 raise;
2773
2774 END UpdateActionHistory;
2775
2776 PROCEDURE get_user_name(p_orig_system IN Varchar2,
2777 p_employee_id IN number,
2778 x_username OUT NOCOPY varchar2,
2779 x_user_display_name OUT NOCOPY varchar2) is
2780
2781 l_progress varchar2(200);
2782 l_api_name varchar2(50) := ' get_user_name';
2783
2784 BEGIN
2785
2786 l_progress := 'POS_WCAPPROVE_PVT.get_user_name: 01.';
2787 IF (g_asn_debug = 'Y') THEN
2788 debug_log(FND_LOG.LEVEL_STATEMENT,
2789 l_api_name,'Enter in get_user_name '
2790 || l_progress);
2791 END IF;
2792
2793 WF_DIRECTORY.GetUserName(p_orig_system,
2794 p_employee_id,
2795 x_username,
2796 x_user_display_name);
2797
2798 IF (g_asn_debug = 'Y') THEN
2799 debug_log(FND_LOG.LEVEL_STATEMENT,
2800 l_api_name,'x_username '
2801 || x_username);
2802 debug_log(FND_LOG.LEVEL_STATEMENT,
2803 l_api_name,'x_user_display_name '
2804 || x_user_display_name);
2805 END IF;
2806 EXCEPTION
2807 when others then
2808 IF (g_asn_debug = 'Y') THEN
2809 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2810 l_api_name,'Exception in get_user_name '
2811 || l_progress);
2812 END IF;
2813 raise;
2814
2815 END get_user_name;
2816
2817
2818 PROCEDURE UpdatePOActionHistory (p_object_id IN NUMBER,
2819 p_object_type_code IN VARCHAR2,
2820 p_employee_id IN NUMBER,
2821 p_action_code IN VARCHAR2,
2822 p_note IN VARCHAR2,
2823 p_user_id IN NUMBER,
2824 p_login_id IN NUMBER)
2825 IS
2826 l_progress VARCHAR2(250) := '';
2827 l_employee_id NUMBER ;
2828 invalid_action exception;
2829 l_api_name varchar2(50) := ' UpdatePOActionHistory';
2830 l_note po_action_history.note%type;
2831
2832 BEGIN
2833 l_progress := 'POS_WCAPPROVE_PVT.UpdatePOActionHistory: 01.';
2834 IF (g_asn_debug = 'Y') THEN
2835 debug_log(FND_LOG.LEVEL_STATEMENT,
2836 l_api_name,'Enter in UpdatePOActionHistory '
2837 || l_progress);
2838 debug_log(FND_LOG.LEVEL_STATEMENT,
2839 l_api_name,'object_id '||p_object_id
2840 || 'object_type_code '|| p_object_type_code
2841 || 'p_employee_id '|| nvl(p_employee_id,-9999)
2842 || 'p_action_code '|| nvl(p_action_code,'NO ACTION')
2843 || 'p_note '|| nvl(p_note,'NO NOTE')
2844 || 'p_user_id '|| p_user_id
2845 || 'p_login_id '|| p_login_id);
2846 END IF;
2847
2848 IF (p_object_id IS NOT NULL AND
2849 p_object_type_code IS NOT NULL) THEN --{
2850
2851 /* Employee id should belong to the id of the corresponding
2852 * user taking the action, not the employee id to
2853 * which the work confirmation was forwarded to.
2854 */
2855 IF (g_asn_debug = 'Y') THEN
2856 debug_log(FND_LOG.LEVEL_STATEMENT,
2857 l_api_name,'ohject_id and '
2858 || 'object_type_code not null');
2859 END IF;
2860
2861 /*
2862 If (p_old_employee_id is NULL) then
2863 IF (g_asn_debug = 'Y') THEN
2864 debug_log(FND_LOG.LEVEL_STATEMENT,
2865 l_api_name,'p_employee_id is null '
2866 || l_progress);
2867 END IF;
2868
2869 SELECT HR.EMPLOYEE_ID
2870 INTO l_employee_id
2871 FROM FND_USER FND, HR_EMPLOYEES_CURRENT_V HR
2872 WHERE FND.USER_ID = NVL(p_user_id, fnd_global.user_id)
2873 AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID;
2874 IF (g_asn_debug = 'Y') THEN
2875 debug_log(FND_LOG.LEVEL_STATEMENT,
2876 l_api_name,'l_employee_id '
2877 || l_employee_id);
2878 END IF;
2879
2880 end if;
2881
2882 l_progress := 'POS_WCAPPROVE_PVT.UpdatePOActionHistory: 02.';
2883 IF (g_asn_debug = 'Y') THEN
2884 debug_log(FND_LOG.LEVEL_STATEMENT,
2885 l_api_name,'Before Update '
2886 || l_progress);
2887 END IF;
2888 */
2889
2890
2891 IF (g_asn_debug = 'Y') THEN
2892 debug_log(FND_LOG.LEVEL_STATEMENT,
2893 l_api_name,'l_note '
2894 || l_note);
2895 end if;
2896
2897
2898 begin
2899 if (p_action_code = 'APPROVE' OR p_action_code ='REJECT') then
2900 select comments
2901 into l_note
2902 from rcv_shipment_headers
2903 where shipment_header_id=p_object_id;
2904 end if;
2905 exception
2906 when others then
2907 l_note := null;
2908 end;
2909
2910 UPDATE PO_ACTION_HISTORY
2911 SET last_update_date = sysdate,
2912 last_updated_by = p_user_id,
2913 last_update_login = p_login_id,
2914 employee_id = p_employee_id,
2915 --employee_id = NVL(l_employee_id, employee_id),
2916 action_date = sysdate,
2917 action_code = p_action_code,
2918 note = nvl(p_note,l_note)
2919 WHERE object_id = p_object_id
2920 AND object_type_code = p_object_type_code
2921 AND action_code IS NULL;
2922 --employee_id = NVL(p_old_employee_id, employee_id)
2923
2924 ELSE --}{
2925 l_progress := 'POS_WCAPPROVE_PVT.UpdatePOActionHistory: 02.';
2926 IF (g_asn_debug = 'Y') THEN
2927 debug_log(FND_LOG.LEVEL_STATEMENT,
2928 l_api_name,
2929 'Either object id or code is null '
2930 || l_progress);
2931 END IF;
2932 raise invalid_action;
2933 END IF; --}
2934
2935 EXCEPTION
2936 when invalid_action then
2937 IF (g_asn_debug = 'Y') THEN
2938 debug_log(FND_LOG.LEVEL_EXCEPTION,
2939 l_api_name,'invalid_action in UpdatePOActionHistory '
2940 || l_progress);
2941 END IF;
2942 raise;
2943 when others then
2944 IF (g_asn_debug = 'Y') THEN
2945 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2946 l_api_name,'Exception in UpdatePOActionHistory '
2947 || l_progress);
2948 END IF;
2949 raise;
2950
2951 END UpdatePOActionHistory;
2952
2953
2954 PROCEDURE InsertPOActionHistory (p_object_id IN NUMBER,
2955 p_object_type_code IN VARCHAR2,
2956 p_object_sub_type_code IN VARCHAR2,
2957 p_sequence_num IN NUMBER,
2958 p_action_code IN VARCHAR2,
2959 p_action_date IN DATE,
2960 p_employee_id IN NUMBER,
2961 p_approval_path_id IN NUMBER,
2962 p_note IN VARCHAR2,
2963 p_object_revision_num IN NUMBER,
2964 p_offline_code IN VARCHAR2,
2965 p_request_id IN NUMBER,
2966 p_program_application_id IN NUMBER,
2967 p_program_id IN NUMBER,
2968 p_program_date IN DATE,
2969 p_user_id IN NUMBER,
2970 p_login_id IN NUMBER)
2971 IS
2972 -- pragma AUTONOMOUS_TRANSACTION;
2973 l_progress VARCHAR2(240) ;
2974 l_sequence_num PO_ACTION_HISTORY.sequence_num%TYPE := NULL;
2975 l_api_name varchar2(50) := ' InsertPOActionHistory';
2976 l_note po_action_history.note%type;
2977
2978 BEGIN
2979
2980
2981
2982 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 01.';
2983 IF (g_asn_debug = 'Y') THEN
2984 debug_log(FND_LOG.LEVEL_STATEMENT,
2985 l_api_name,'Enter in InsertPOActionHistory '
2986 || l_progress);
2987 END IF;
2988
2989 l_sequence_num := p_sequence_num;
2990
2991 IF (g_asn_debug = 'Y') THEN
2992 debug_log(FND_LOG.LEVEL_STATEMENT,
2993 l_api_name,'l_sequence_num '
2994 || l_sequence_num);
2995 END IF;
2996
2997 IF (l_sequence_num is NULL ) THEN --{
2998
2999
3000 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 02.';
3001
3002 SELECT MAX(sequence_num)
3003 INTO l_sequence_num
3004 FROM PO_ACTION_HISTORY
3005 WHERE object_id = p_object_id
3006 AND object_type_code = p_object_type_code;
3007
3008 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 03.';
3009
3010 IF (l_sequence_num IS NULL) THEN
3011 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 04.';
3012 l_sequence_num := 0;
3013 ELSE
3014 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 05.';
3015 l_sequence_num := l_sequence_num + 1;
3016 END IF;
3017
3018 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 06.';
3019
3020
3021 END IF; --}
3022
3023 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 07.';
3024
3025 IF (g_asn_debug = 'Y') THEN
3026 debug_log(FND_LOG.LEVEL_STATEMENT,
3027 l_api_name,'l_sequence_num before insert '
3028 || l_sequence_num);
3029 END IF;
3030
3031
3032
3033 INSERT INTO PO_ACTION_HISTORY
3034 (object_id,
3035 object_type_code,
3036 object_sub_type_code,
3037 sequence_num,
3038 last_update_date,
3039 last_updated_by,
3040 employee_id,
3041 action_code,
3042 action_date,
3043 note,
3044 object_revision_num,
3045 last_update_login,
3046 creation_date,
3047 created_by,
3048 request_id,
3049 program_application_id,
3050 program_id,
3051 program_date,
3052 approval_path_id,
3053 offline_code,
3054 program_update_date)
3055 VALUES (p_object_id,
3056 'WC',
3057 'WC',
3058 l_sequence_num,
3059 sysdate,
3060 p_user_id,
3061 p_employee_id,
3062 p_action_code,
3063 p_action_date,
3064 l_note,
3065 p_object_revision_num,
3066 p_login_id,
3067 sysdate,
3068 p_user_id,
3069 p_request_id,
3070 p_program_application_id,
3071 p_program_id,
3072 p_program_date,
3073 p_approval_path_id,
3074 p_offline_code,
3075 sysdate);
3076
3077
3078 EXCEPTION
3079 when others then
3080 IF (g_asn_debug = 'Y') THEN
3081 debug_log(FND_LOG.LEVEL_UNEXPECTED,
3082 l_api_name,'Exception in InsertPOActionHistory '
3083 || l_progress);
3084 END IF;
3085 raise;
3086
3087 END InsertPOActionHistory;
3088
3089
3090 procedure post_approval_notif (p_itemtype in varchar2,
3091 p_itemkey in varchar2,
3092 p_actid in number,
3093 p_funcmode in varchar2,
3094 x_resultout out NOCOPY varchar2) IS
3095
3096 l_api_name varchar2(50) := p_itemkey || ' post_approval_notif';
3097 l_notification_id number;
3098 l_forwardTo varchar2(240);
3099 l_result varchar2(100);
3100 l_forward_to_username_response varchar2(240) :='';
3101 l_action po_action_history.action_code%TYPE;
3102 l_new_recipient_id wf_roles.orig_system_id%TYPE;
3103 l_origsys wf_roles.orig_system%TYPE;
3104 l_shipment_line_id number;
3105 l_shipment_header_id number;
3106 l_sequence_num number;
3107
3108
3109
3110 -- Bug 8479430 - START
3111 -- Declare following context setting variables.
3112 l_responder_id fnd_user.user_id%TYPE;
3113 l_session_user_id NUMBER;
3114 l_session_resp_id NUMBER;
3115 l_session_appl_id NUMBER;
3116 l_preparer_resp_id NUMBER;
3117 l_preparer_appl_id NUMBER;
3118 l_progress VARCHAR2(1000);
3119 l_preserved_ctx VARCHAR2(5);
3120 -- Bug 8479430 - END
3121
3122 /*
3123 cursor get_shipment_lines(l_shipment_header_id number) is
3124 select rsl.shipment_line_id
3125 from rcv_shipment_lines rsl
3126 where rsl.shipment_header_id = l_shipment_header_id;
3127 */
3128
3129 CURSOR get_action_history IS
3130 SELECT object_id,
3131 object_type_code,
3132 object_sub_type_code,
3133 sequence_num,
3134 object_revision_num,
3135 request_id,
3136 program_application_id,
3137 program_date,
3138 program_id,
3139 last_update_date,
3140 employee_id
3141 FROM PO_ACTION_HISTORY
3142 WHERE object_type_code = 'WC'
3143 AND object_id = l_shipment_header_id
3144 AND sequence_num = l_sequence_num;
3145
3146 Recinfo get_action_history%ROWTYPE;
3147
3148 BEGIN
3149
3150 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif: 01.';
3151 IF (g_asn_debug = 'Y') THEN
3152 debug_log(FND_LOG.LEVEL_STATEMENT, l_api_name, 'Enter in post_approval_notif ' || l_progress);
3153 debug_log(FND_LOG.LEVEL_STATEMENT, l_api_name, 'p_itemtype ' || p_itemtype);
3154 debug_log(FND_LOG.LEVEL_STATEMENT, l_api_name, 'p_itemkey ' || p_itemkey);
3155 debug_log(FND_LOG.LEVEL_STATEMENT, l_api_name, 'p_actid ' || p_actid);
3156 debug_log(FND_LOG.LEVEL_STATEMENT, l_api_name, 'p_funcmode ' || p_funcmode);
3157 END IF;
3158
3159 if (p_funcmode IN ('FORWARD', 'QUESTION', 'ANSWER')) THEN
3160 if (p_funcmode = 'FORWARD') then
3161 l_action := 'DELEGATE';
3162 elsif (p_funcmode = 'QUESTION') then
3163 l_action := 'QUESTION';
3164 elsif (p_funcmode = 'ANSWER') then
3165 l_action := 'ANSWER';
3166 end if;
3167
3168 l_shipment_header_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
3169 itemkey => p_itemkey,
3170 aname => 'WORK_CONFIRMATION_ID');
3171 IF (g_asn_debug = 'Y') THEN
3172 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_shipment_header_id ' ||l_shipment_header_id);
3173 END IF;
3174
3175 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_NEW_ROLE, l_origsys, l_new_recipient_id);
3176
3177 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif: 02.';
3178
3179 select max(sequence_num)
3180 into l_sequence_num
3181 from po_action_history
3182 where object_type_code ='WC'
3183 and object_id = l_shipment_header_id;
3184
3185 OPEN get_action_history;
3186 FETCH get_action_history INTO Recinfo;
3187 IF (get_action_history%NOTFOUND) then
3188 IF (g_asn_debug = 'Y') THEN
3189 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'no_data_round');
3190 END IF;
3191 RAISE NO_DATA_FOUND;
3192 END IF;
3193 CLOSE get_action_history;
3194
3195
3196 UpdatePOActionHistory( p_object_id => Recinfo.object_id,
3197 p_object_type_code => Recinfo.object_type_code,
3198 p_employee_id => Recinfo.employee_id,
3199 p_action_code => l_action,
3200 p_note => wf_engine.context_user_comment,
3201 p_user_id => fnd_global.user_id,
3202 p_login_id => fnd_global.login_id);
3203
3204
3205 InsertPOActionHistory( p_object_id => Recinfo.object_id,
3206 p_object_type_code=>Recinfo.object_type_code,
3207 p_object_sub_type_code => NULL,
3208 p_sequence_num => Recinfo.sequence_num+1 ,
3209 p_action_code =>NULL ,
3210 p_action_date =>NULL ,
3211 p_employee_id => l_new_recipient_id,
3212 p_approval_path_id => NULL ,
3213 p_note => NULL,
3214 p_object_revision_num => NULL,
3215 p_offline_code => NULL,
3216 p_request_id => Recinfo.request_id,
3217 p_program_application_id => Recinfo.program_application_id,
3218 p_program_id =>Recinfo.program_id ,
3219 p_program_date => Recinfo.program_date ,
3220 p_user_id => fnd_global.user_id ,
3221 p_login_id => fnd_global.login_id);
3222
3223 x_resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
3224 return;
3225 end if;
3226
3227 if (p_funcmode = 'RESPOND') then
3228 l_notification_id := WF_ENGINE.context_nid;
3229 IF (g_asn_debug = 'Y') THEN
3230 debug_log(FND_LOG.LEVEL_STATEMENT, l_api_name, 'l_notification_id '||l_notification_id );
3231 END IF;
3232
3233 l_result := wf_notification.GetAttrText(l_notification_id, 'RESULT');
3234
3235 IF (g_asn_debug = 'Y') THEN
3236 debug_log(FND_LOG.LEVEL_STATEMENT, l_api_name, 'l_result '||l_result );
3237 END IF;
3238
3239 if (l_result = 'FORWARD') THEN
3240
3241 l_forwardTo := wf_notification.GetAttrText(l_notification_id, 'FORWARD_TO_USERNAME_RESPONSE');
3242 l_forward_to_username_response := wf_engine.GetItemAttrText (itemtype => p_itemtype,
3243 itemkey => p_itemkey,
3244 aname => 'FORWARD_TO_USERNAME_RESPONSE');
3245
3246 if(l_forwardTo is null) then
3247 fnd_message.set_name('ICX', 'ICX_POR_WF_NOTIF_NO_USER');
3248 app_exception.raise_exception;
3249 end if;
3250 end if;
3251
3252
3253 /* Bug 8479430 - START
3254 Set the user context properly before launching the concurrent request*/
3255
3256 if (wf_engine.preserved_context = TRUE) then
3257 l_preserved_ctx := 'TRUE';
3258 else
3259 l_preserved_ctx := 'FALSE';
3260 end if;
3261
3262 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif: 03.' ;
3263
3264 -- <debug start>
3265 IF (g_asn_debug = 'Y') THEN
3266 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_progress ' ||l_progress);
3267 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_preserved_ctx ' ||l_preserved_ctx);
3268 END IF;
3269 -- <debug end>
3270
3271
3272 SELECT fu.USER_ID
3273 INTO l_responder_id
3274 FROM fnd_user fu,
3275 wf_notifications wfn
3276 WHERE wfn.notification_id = l_notification_id
3277 AND wfn.original_recipient = fu.user_name;
3278
3279 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif: 04.' ;
3280
3281 -- <debug start>
3282 IF (g_asn_debug = 'Y') THEN
3283 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_progress ' ||l_progress);
3284 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_responder_id ' ||l_responder_id);
3285 END IF;
3286 -- <debug end>
3287
3288 l_session_user_id := fnd_global.user_id;
3289 l_session_resp_id := fnd_global.resp_id;
3290 l_session_appl_id := fnd_global.resp_appl_id;
3291
3292 -- <debug start>
3293 IF (g_asn_debug = 'Y') THEN
3294 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_session_user_id ' ||l_session_user_id);
3295 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_session_resp_id ' ||l_session_resp_id);
3296 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_session_appl_id ' ||l_session_appl_id);
3297 END IF;
3298 -- <debug end>
3299
3300 IF (l_session_user_id = -1) THEN
3301 l_session_user_id := NULL;
3302 END IF;
3303
3304 IF (l_session_resp_id = -1) THEN
3305 l_session_resp_id := NULL;
3306 END IF;
3307
3308 IF (l_session_appl_id = -1) THEN
3309 l_session_appl_id := NULL;
3310 END IF;
3311
3312 -- <debug start>
3313 IF (g_asn_debug = 'Y') THEN
3314 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_session_user_id ' ||l_session_user_id);
3315 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_session_resp_id ' ||l_session_resp_id);
3316 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_session_appl_id ' ||l_session_appl_id);
3317 END IF;
3318 -- <debug end>
3319
3320 l_preparer_resp_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
3321 itemkey => p_itemkey,
3322 aname => 'RESPONSIBILITY_ID');
3323 l_preparer_appl_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
3324 itemkey => p_itemkey,
3325 aname => 'APPLICATION_ID');
3326
3327 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif: 05.' ;
3328
3329 -- <debug start>
3330 IF (g_asn_debug = 'Y') THEN
3331 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_progress ' ||l_progress);
3332 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_preparer_resp_id ' ||l_preparer_resp_id);
3333 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_preparer_appl_id ' ||l_preparer_appl_id);
3334 END IF;
3335 -- <debug end>
3336
3337 if (l_responder_id is not null) then
3338 if (l_responder_id <> l_session_user_id) then
3339 /* possible in 2 scenarios :
3340 1. when the response is made from email using guest user feature
3341 2. When the response is made from sysadmin login
3342 */
3343
3344 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif: 06.' ;
3345
3346 -- <debug start>
3347 IF (g_asn_debug = 'Y') THEN
3348 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_progress ' ||l_progress || 'When the response is made from email using guest user feature');
3349 END IF;
3350 -- <debug end>
3351
3352 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3353 itemkey => p_itemkey,
3354 aname => 'RESPONDER_USER_ID',
3355 avalue => l_responder_id);
3356 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3357 itemkey => p_itemkey,
3358 aname => 'RESPONDER_RESP_ID',
3359 avalue => l_preparer_resp_id);
3360 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3361 itemkey => p_itemkey,
3362 aname => 'RESPONDER_APPL_ID',
3363 avalue => l_preparer_appl_id);
3364 ELSE
3365 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif: 07.' ;
3366
3367 -- <debug start>
3368 IF (g_asn_debug = 'Y') THEN
3369 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_progress ' ||l_progress || 'When the response is made from sysadmin login');
3370 END IF;
3371 -- <debug end>
3372
3373 if (l_session_resp_id is null) THEN
3374 /* possible when the response is made from the default worklist
3375 without choosing a valid responsibility */
3376 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif: 08.' ;
3377
3378 -- <debug start>
3379 IF (g_asn_debug = 'Y') THEN
3380 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_progress ' ||l_progress || 'When the response is made from the default worklist');
3381 END IF;
3382 -- <debug end>
3383
3384 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3385 itemkey => p_itemkey,
3386 aname => 'RESPONDER_USER_ID',
3387 avalue => l_responder_id);
3388 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3389 itemkey => p_itemkey,
3390 aname => 'RESPONDER_RESP_ID',
3391 avalue => l_preparer_resp_id);
3392 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3393 itemkey => p_itemkey,
3394 aname => 'RESPONDER_APPL_ID',
3395 avalue => l_preparer_appl_id);
3396 else
3397 /* All values available - possible when the response is made after choosing a correct responsibility */
3398 /* If the values of responsibility_id and application
3399 id are available but are incorrect. This may happen when a response is made
3400 through the email or the background process picks the wf up.
3401 This may happen due to the fact that the mailer / background process
3402 carries the context set by the notification /wf it processed last */
3403
3404 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif: 09.' ;
3405
3406 -- <debug start>
3407 IF (g_asn_debug = 'Y') THEN
3408 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_progress ' ||l_progress || 'When the response is made after choosing a correct responsibility');
3409 END IF;
3410 -- <debug end>
3411
3412
3413 if ( l_preserved_ctx = 'TRUE') then
3414 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3415 itemkey => p_itemkey,
3416 aname => 'RESPONDER_USER_ID',
3417 avalue => l_responder_id);
3418 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3419 itemkey => p_itemkey,
3420 aname => 'RESPONDER_RESP_ID',
3421 avalue => l_session_resp_id);
3422 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3423 itemkey => p_itemkey,
3424 aname => 'RESPONDER_APPL_ID',
3425 avalue => l_session_appl_id);
3426 else
3427 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3428 itemkey => p_itemkey,
3429 aname => 'RESPONDER_USER_ID',
3430 avalue => l_responder_id);
3431 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3432 itemkey => p_itemkey,
3433 aname => 'RESPONDER_RESP_ID',
3434 avalue => l_preparer_resp_id);
3435 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
3436 itemkey => p_itemkey,
3437 aname => 'RESPONDER_APPL_ID',
3438 avalue => l_preparer_appl_id);
3439 end if;
3440 end if;
3441 end if;
3442 end if;
3443
3444 -- Bug 8479430 - END
3445
3446 x_resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
3447 return;
3448 end if;
3449
3450
3451 EXCEPTION
3452 when no_data_found then
3453 IF (g_asn_debug = 'Y') THEN
3454 debug_log(FND_LOG.LEVEL_UNEXPECTED, l_api_name, 'No data found in post_approval_notif ' || l_progress);
3455 END IF;
3456 raise;
3457
3458 when others then
3459 IF (g_asn_debug = 'Y') THEN
3460 debug_log(FND_LOG.LEVEL_UNEXPECTED, l_api_name,'Exception in post_approval_notif ' || l_progress);
3461 END IF;
3462 raise;
3463 END post_approval_notif;
3464
3465 procedure reject_doc
3466 (p_itemtype in varchar2,
3467 p_itemkey in varchar2,
3468 p_actid in number,
3469 p_funcmode in varchar2,
3470 x_resultout out NOCOPY varchar2) IS
3471
3472 l_progress varchar2(300);
3473 l_api_name varchar2(50) := p_itemkey ||' reject_doc';
3474 l_approval_status rcv_shipment_headers.approval_status%type := 'REJECTED';
3475 l_result varchar2(3);
3476 l_shipment_header_id number;
3477 l_po_header_id number;
3478 l_work_confirmation_number varchar2(30);
3479 l_note varchar2(4000);
3480 l_view_wc_lines_detail_url varchar2(1000);
3481 begin
3482 l_progress := 'POS_WCAPPROVE_PVT.reject_doc: 01.';
3483 IF (g_asn_debug = 'Y') THEN
3484 debug_log(FND_LOG.LEVEL_STATEMENT,
3485 l_api_name,
3486 'Enter in reject_doc '
3487 || l_progress);
3488 END IF;
3489
3490 if (p_funcmode <> wf_engine.eng_run) then
3491 x_resultout := wf_engine.eng_null;
3492 return;
3493 end if;
3494
3495 l_progress := 'POS_WCAPPROVE_PVT.reject_doc: 02.';
3496 l_shipment_header_id := wf_engine.GetItemAttrNumber
3497 (itemtype => p_itemtype,
3498 itemkey => p_itemkey,
3499 aname => 'WORK_CONFIRMATION_ID');
3500
3501
3502
3503 update_approval_status(p_shipment_header_id => l_shipment_header_id,
3504 p_note => null,
3505 p_approval_status => l_approval_status,
3506 p_level => 'HEADER',
3507 x_resultout => l_result);
3508
3509
3510 l_progress := 'POS_WCAPPROVE_PVT.reject_doc: 03.';
3511 IF (l_result = 'N') THEN
3512
3513 IF (g_asn_debug = 'Y') THEN
3514 debug_log(FND_LOG.LEVEL_STATEMENT,
3515 l_api_name,
3516 'Could not Reject the document '
3517 || l_progress);
3518 END IF;
3519 x_resultout := wf_engine.eng_completed || ':' || 'N';
3520 END IF;
3521
3522 IF (l_result = 'Y') THEN
3523
3524 IF (g_asn_debug = 'Y') THEN
3525 debug_log(FND_LOG.LEVEL_STATEMENT,
3526 l_api_name,
3527 'Rejected the document '
3528 || l_progress);
3529 END IF;
3530 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
3531 itemkey => p_itemkey,
3532 aname => 'DOC_STATUS',
3533 avalue => 'REJECTED');
3534
3535
3536 l_po_header_id := wf_engine.GetItemAttrNumber
3537 (itemtype => p_itemtype,
3538 itemkey => p_itemkey,
3539 aname => 'PO_DOCUMENT_ID');
3540
3541 IF (g_asn_debug = 'Y') THEN
3542 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
3543 'l_po_header_id ' || l_po_header_id);
3544 END IF;
3545
3546 l_work_confirmation_number :=
3547 wf_engine.GetItemAttrText (itemtype => p_itemtype,
3548 itemkey => p_itemkey,
3549 aname => 'WORK_CONFIRMATION_NUMBER');
3550
3551 IF (g_asn_debug = 'Y') THEN
3552 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
3553 'l_work_confirmation_number ' || l_work_confirmation_number);
3554 END IF;
3555
3556 -- bug 10012891 - encoding value of work confirmation number as per http standards
3557 l_view_wc_lines_detail_url :=
3558 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/pos/wc/webui/WcDetailsPG' || '&' ||
3559 'WcStatus=REJECTED' || '&' ||
3560 'PoHeaderId=' || to_char(l_po_header_id) || '&' ||
3561 'WcHeaderId=' || to_char(l_shipment_header_id) || '&' ||
3562 'WcNum=' || (wfa_html.encode_url(l_work_confirmation_number)) || '&' ||
3563 -- 'ReadOnly=Y' || '&' ||
3564 'addBreadCrumb=Y';
3565
3566 wf_engine.SetItemAttrText ( itemtype => p_itemType,
3567 itemkey => p_itemkey,
3568 aname => 'VIEW_WC_LINES_DETAIL_URL' ,
3569 avalue => l_view_wc_lines_detail_url);
3570 x_resultout := wf_engine.eng_completed || ':' || 'Y';
3571 END IF;
3572 EXCEPTION
3573 when others then
3574 IF (g_asn_debug = 'Y') THEN
3575 debug_log(FND_LOG.LEVEL_UNEXPECTED,
3576 l_api_name,'Exception in reject_doc '
3577 || l_progress);
3578 END IF;
3579 raise;
3580
3581 END reject_doc;
3582
3583 procedure Approve_doc
3584 (p_itemtype in varchar2,
3585 p_itemkey in varchar2,
3586 p_actid in number,
3587 p_funcmode in varchar2,
3588 x_resultout out NOCOPY varchar2) IS
3589
3590 l_progress varchar2(300);
3591 l_api_name varchar2(50) := p_itemkey || ' Approve_doc';
3592 l_approval_status rcv_shipment_headers.approval_status%type := 'APPROVED';
3593 l_result varchar2(3);
3594 l_shipment_header_id number;
3595 l_po_header_id number;
3596 l_note varchar2(4000);
3597 l_view_wc_lines_detail_url varchar2(1000);
3598 l_work_confirmation_number varchar2(30);
3599 begin
3600 l_progress := 'POS_WCAPPROVE_PVT.Approve_doc: 01.';
3601 IF (g_asn_debug = 'Y') THEN
3602 debug_log(FND_LOG.LEVEL_STATEMENT,
3603 l_api_name,
3604 'Enter in Approve_doc '
3605 || l_progress);
3606 END IF;
3607
3608 if (p_funcmode <> wf_engine.eng_run) then
3609 x_resultout := wf_engine.eng_null;
3610 return;
3611 end if;
3612
3613 l_progress := 'POS_WCAPPROVE_PVT.Approve_doc: 02.';
3614 l_shipment_header_id := wf_engine.GetItemAttrNumber
3615 (itemtype => p_itemtype,
3616 itemkey => p_itemkey,
3617 aname => 'WORK_CONFIRMATION_ID');
3618
3619
3620 update_approval_status(p_shipment_header_id => l_shipment_header_id,
3621 p_note => null,
3622 p_approval_status => l_approval_status,
3623 p_level => 'HEADER',
3624 x_resultout => l_result);
3625
3626 l_progress := 'POS_WCAPPROVE_PVT.Approve_doc: 03.';
3627 IF (l_result = 'N') THEN
3628
3629 IF (g_asn_debug = 'Y') THEN
3630 debug_log(FND_LOG.LEVEL_STATEMENT,
3631 l_api_name,
3632 'Could not Reject the document '
3633 || l_progress);
3634 END IF;
3635 x_resultout := wf_engine.eng_completed || ':' || 'N';
3636 END IF;
3637
3638 IF (l_result = 'Y') THEN
3639
3640 IF (g_asn_debug = 'Y') THEN
3641 debug_log(FND_LOG.LEVEL_STATEMENT,
3642 l_api_name,
3643 'Approved the document '
3644 || l_progress);
3645 END IF;
3646 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
3647 itemkey => p_itemkey,
3648 aname => 'DOC_STATUS',
3649 avalue => 'APPROVED');
3650
3651 l_po_header_id := wf_engine.GetItemAttrNumber
3652 (itemtype => p_itemtype,
3653 itemkey => p_itemkey,
3654 aname => 'PO_DOCUMENT_ID');
3655
3656 IF (g_asn_debug = 'Y') THEN
3657 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
3658 'l_po_header_id ' || l_po_header_id);
3659 END IF;
3660
3661 l_work_confirmation_number :=
3662 wf_engine.GetItemAttrText (itemtype => p_itemtype,
3663 itemkey => p_itemkey,
3664 aname => 'WORK_CONFIRMATION_NUMBER');
3665
3666 IF (g_asn_debug = 'Y') THEN
3667 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
3668 'l_work_confirmation_number ' || l_work_confirmation_number);
3669 END IF;
3670
3671 -- bug 10012891 - encoding value of work confirmation number as per http standards
3672 l_view_wc_lines_detail_url :=
3673 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/pos/wc/webui/WcDetailsPG' || '&' ||
3674 'WcStatus=APPROVED' || '&' ||
3675 'PoHeaderId=' || to_char(l_po_header_id) || '&' ||
3676 'WcHeaderId=' || to_char(l_shipment_header_id) || '&' ||
3677 'WcNum=' || (wfa_html.encode_url(l_work_confirmation_number)) || '&' ||
3678 --'ReadOnly=Y' || '&' ||
3679 'addBreadCrumb=Y';
3680
3681 wf_engine.SetItemAttrText ( itemtype => p_itemType,
3682 itemkey => p_itemkey,
3683 aname => 'VIEW_WC_LINES_DETAIL_URL' ,
3684 avalue => l_view_wc_lines_detail_url);
3685
3686 x_resultout := wf_engine.eng_completed || ':' || 'Y';
3687 END IF;
3688 EXCEPTION
3689 when others then
3690 IF (g_asn_debug = 'Y') THEN
3691 debug_log(FND_LOG.LEVEL_UNEXPECTED,
3692 l_api_name,'Exception in Approve_doc '
3693 || l_progress);
3694 END IF;
3695 raise;
3696
3697 END Approve_doc;
3698
3699 procedure update_approval_status
3700 (p_shipment_header_id in number,
3701 p_note in varchar2,
3702 p_approval_status in varchar2,
3703 p_level in varchar2,
3704 x_resultout out NOCOPY varchar2) IS
3705
3706 cursor lock_rsh(l_shipment_header_id number) is
3707 select null
3708 from rcv_shipment_headers
3709 where shipment_header_id = l_shipment_header_id
3710 for update of shipment_header_id nowait;
3711
3712 resource_busy_exc EXCEPTION;
3713 -- pragma EXCEPTION_INIT(resource_busy_exc, -00054);
3714
3715 l_locked_doc BOOLEAN := FALSE;
3716 l_shipment_header_id number;
3717 l_progress varchar2(300);
3718 l_api_name varchar2(50) := ' update_approval_status';
3719 l_note po_action_history.note%type;
3720 begin
3721 l_progress := 'POS_WCAPPROVE_PVT.update_approval_status: 01.';
3722 IF (g_asn_debug = 'Y') THEN
3723 debug_log(FND_LOG.LEVEL_STATEMENT,
3724 l_api_name,
3725 'Enter in update_approval_status '
3726 || l_progress);
3727 debug_log(FND_LOG.LEVEL_STATEMENT,
3728 l_api_name,
3729 'p_note '
3730 || p_note);
3731 END IF;
3732
3733
3734
3735
3736 If (p_level = 'HEADER') then
3737 for i in 1..1000
3738 loop
3739 begin
3740 /* Opening the cursor will lock the row */
3741 Open lock_rsh(p_shipment_header_id);
3742 Close lock_rsh;
3743
3744 l_locked_doc := TRUE;
3745
3746 EXIT;
3747
3748 EXCEPTION
3749 WHEN resource_busy_exc THEN
3750 NULL;
3751 END;
3752
3753 end loop;
3754
3755 IF (NOT l_locked_doc) THEN
3756
3757 IF (g_asn_debug = 'Y') THEN
3758 debug_log(FND_LOG.LEVEL_STATEMENT,
3759 l_api_name,'Could not lock row '
3760 || l_progress);
3761 END IF;
3762 ROLLBACK;
3763 x_resultout := 'N';
3764 return;
3765
3766 END IF;
3767
3768 update rcv_shipment_headers
3769 set approval_status = p_approval_status,
3770 comments = nvl(p_note,comments)
3771 where shipment_header_id = p_shipment_header_id;
3772 end if;
3773
3774
3775 If (p_level = 'LINES') then
3776 /* If p_level is lines, then it can
3777 * come directly from notificatiion.
3778 * So update the header with the comments.
3779 */
3780 update rcv_shipment_lines
3781 set approval_status = p_approval_status
3782 where shipment_header_id=p_shipment_header_id ;
3783
3784 update rcv_shipment_headers
3785 set comments = nvl(p_note,comments)
3786 where shipment_header_id = p_shipment_header_id;
3787 end if;
3788
3789 x_resultout := 'Y';
3790 exception
3791 when others then
3792 IF (g_asn_debug = 'Y') THEN
3793 debug_log(FND_LOG.LEVEL_UNEXPECTED,
3794 l_api_name,'Exception in update_approval_status '
3795 || l_progress);
3796 END IF;
3797 raise;
3798
3799 END update_approval_status;
3800
3801 procedure insert_into_rti(p_itemtype in varchar2,
3802 p_itemkey in varchar2,
3803 p_actid in number,
3804 p_funcmode in varchar2,
3805 x_resultout out NOCOPY varchar2) IS
3806
3807 l_progress varchar2(300);
3808 l_api_name varchar2(50) := p_itemkey || ' insert_into_rti';
3809 l_shipment_header_id number;
3810
3811 Cursor get_wcr_info(l_shipment_header_id number) is
3812 SELECT rsl.po_line_location_id,
3813 pll.unit_meas_lookup_code,
3814 rsl.unit_of_measure unit_of_measure,
3815 rsl.unit_of_measure primary_unit_of_measure,
3816 rsl.unit_of_measure source_doc_unit_of_measure,
3817 NVL(pll.promised_date, pll.need_by_date) promised_date,
3818 rsl.to_organization_id ship_to_organization_id,
3819 null quantity_ordered,
3820 null amount_ordered,
3821 null quantity_funded,
3822 null amount_funded,
3823 null partial_funded_flag,
3824 NVL(pll.price_override, pl.unit_price) po_unit_price,
3825 pll.match_option,
3826 rsl.category_id,
3827 rsl.item_description,
3828 pl.po_line_id,
3829 ph.currency_code,
3830 ph.rate_type currency_conversion_type,
3831 ph.segment1 document_num,
3832 null po_distribution_id, --pod.po_distribution_id,
3833 rsl.req_distribution_id,
3834 rsl.requisition_line_id,
3835 rsl.deliver_to_location_id deliver_to_location_id,
3836 rsl.deliver_to_location_id location_id,
3837 rsl.deliver_to_person_id,
3838 null currency_conversion_date, --pod.rate_date currency_conversion_date,
3839 null currency_conversion_rate, --pod.rate currency_conversion_rate,
3840 rsl.destination_type_code destination_type_code,
3841 rsl.destination_type_code destination_context,
3842 null charge_account_id, --pod.code_combination_id ,
3843 null destination_organization_id, --pod.destination_organization_id,
3844 null subinventory, --pod.destination_subinventory ,
3845 rsl.ship_to_location_id,
3846 rsl.comments,
3847 rsl.attribute_category attribute_category,
3848 rsl.attribute1 attribute1,
3849 rsl.attribute2 attribute2,
3850 rsl.attribute3 attribute3,
3851 rsl.attribute4 attribute4,
3852 rsl.attribute5 attribute5,
3853 rsl.attribute6 attribute6,
3854 rsl.attribute7 attribute7,
3855 rsl.attribute8 attribute8,
3856 rsl.attribute9 attribute9,
3857 rsl.attribute10 attribute10,
3858 rsl.attribute11 attribute11,
3859 rsl.attribute12 attribute12,
3860 rsl.attribute13 attribute13,
3861 rsl.attribute14 attribute14,
3862 rsl.attribute15 attribute15,
3863 NVL(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code,
3864 rsl.shipment_line_id,
3865 rsl.item_id,
3866 rsl.item_revision,
3867 rsh.vendor_id,
3868 rsh.shipment_num,
3869 rsh.freight_carrier_code,
3870 rsh.bill_of_lading,
3871 rsh.packing_slip,
3872 rsh.shipped_date,
3873 rsh.expected_receipt_date,
3874 rsh.waybill_airbill_num ,
3875 rsh.vendor_site_id,
3876 rsl.to_organization_id,
3877 rsl.routing_header_id,
3878 rsl.vendor_item_num,
3879 rsl.vendor_lot_num,
3880 rsl.ussgl_transaction_code,
3881 rsl.government_context,
3882 pll.po_header_id,
3883 ph.revision_num po_revision_num,
3884 pl.line_num document_line_num,
3885 pll.shipment_num document_shipment_line_num,
3886 null document_distribution_num , --pod.distribution_num
3887 pll.po_release_id,
3888 pl.job_id,
3889 ph.org_id,
3890 rsl.amount_shipped amount,
3891 rsl.quantity_shipped quantity,
3892 rsl.quantity_shipped source_doc_quantity,
3893 rsl.quantity_shipped primary_quantity,
3894 rsl.quantity_shipped quantity_shipped,
3895 rsl.amount_shipped amount_shipped,
3896 rsl.requested_amount requested_amount,
3897 rsl.material_stored_amount material_stored_amount,
3898 pll.matching_basis,
3899 Nvl(pdsh.clm_flag,'N') clm_flag
3900 FROM
3901 --po_distributions_all pod,
3902 po_line_locations_all pll,
3903 po_lines_all pl,
3904 po_headers_all ph,
3905 rcv_shipment_lines rsl,
3906 rcv_shipment_headers rsh,
3907 po_doc_style_headers pdsh
3908 WHERE
3909 rsh.shipment_header_id = l_shipment_header_id
3910 and rsl.shipment_header_id = rsh.shipment_header_id
3911 and rsl.po_header_id = ph.po_header_id
3912 --and pod.po_header_id = ph.po_header_id
3913 --and pod.line_location_id = pll.line_location_id
3914 and rsl.po_line_id = pl.po_line_id
3915 and rsl.po_line_location_id = pll.line_location_id
3916 and rsh.receipt_source_code = 'VENDOR'
3917 and pll.po_line_id = pl.po_line_id
3918 AND NVL(pll.approved_flag, 'N') = 'Y'
3919 AND NVL(pll.cancel_flag, 'N') = 'N'
3920 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT')
3921 AND NVL(ph.STYLE_ID, 1) = PDSH.STYLE_ID (+)
3922 AND PDSH.STATUS (+) = 'ACTIVE';
3923
3924
3925 wcr_line_info get_wcr_info%rowtype;
3926
3927 /* Bug 6709928 -- Added columns destination_type_code,destination_type_code
3928 to the cursor get_dist_info*/
3929 cursor get_dist_info(l_line_location_id number) is
3930 select pod.po_distribution_id,
3931 pod.rate_date currency_conversion_date,
3932 pod.rate currency_conversion_rate,
3933 pod.code_combination_id charge_account_id,
3934 pod.destination_organization_id,
3935 pod.destination_subinventory subinventory,
3936 pod.distribution_num document_distribution_num,
3937 pod.quantity_ordered,
3938 pod.amount_ordered,
3939 pod.quantity_funded,
3940 pod.amount_funded,
3941 Nvl(pod.partial_funded_flag,'N') partial_funded_flag,
3942 pod.destination_type_code destination_type_code,
3943 pod.destination_type_code destination_context
3944 from po_distributions_all pod
3945 where pod.line_location_id = l_line_location_id
3946 order by pod.distribution_num;
3947
3948 /* Bug 11869822 */
3949
3950
3951 cursor get_shipment_lines(l_shipment_header_id number) is
3952 select rsl.shipment_line_id
3953 from rcv_shipment_lines rsl
3954 where rsl.shipment_header_id = l_shipment_header_id;
3955
3956
3957 X_emp_id number;
3958 X_emp_name per_employees_current_x.full_name%TYPE;
3959 X_location_id number;
3960 X_location_code hr_locations_all.location_code%TYPE;
3961 X_is_buyer BOOLEAN;
3962 X_emp_flag BOOLEAN;
3963 l_emp_ok BOOLEAN;
3964 l_uom_code mtl_units_of_measure.uom_code%type;
3965 l_row_id varchar2(40);
3966 l_interface_id number;
3967 l_group_id number;
3968 l_vendor_id number;
3969 l_vendor_site_id number;
3970 l_ship_to_org_id number;
3971 l_ship_to_location_id number;
3972 l_header_interface_id number;
3973 l_expected_receipt_date date;
3974 l_shipment_num varchar2(50);
3975 l_receipt_num varchar2(50);
3976 l_matching_basis varchar2(35);
3977 l_remaining_amount number;
3978 l_old_remaining_amount number;
3979 l_transacted_amount number;
3980 l_interface_amount number;
3981 l_available_amount number;
3982 l_remaining_quantity number;
3983 l_old_remaining_quantity number;
3984 l_transacted_quantity number;
3985 l_interface_quantity number;
3986 l_available_quantity number;
3987 --l_first_time boolean := TRUE;
3988 l_insert_into_rti boolean := TRUE;
3989 l_max_dist NUMBER;
3990 l_dist_count NUMBER;
3991 l_carry_over_amount NUMBER;
3992 l_carry_over_quantity NUMBER;
3993
3994 -- added for wc correction ER - bug 9414650
3995 l_req_amount_inserted BOOLEAN := FALSE;
3996 l_mat_stored_inserted BOOLEAN := FALSE;
3997
3998 l_primary_quantity_in NUMBER;
3999 l_clm_flag VARCHAR2(1) := 'N';
4000 l_partial_funded_flag VARCHAR2(1) := 'N';
4001 l_full_fund_dist_id NUMBER;
4002 l_int_transaction_id NUMBER;
4003 l_full_fund_amt NUMBER;
4004 l_full_fund_qty NUMBER;
4005
4006 /* Added for the Bug #: 13924722 */
4007 l_currency_conversion_rate po_distributions_all.rate%TYPE;
4008 l_currency_conversion_date po_distributions_all.rate_date%TYPE;
4009 x_sob_Id NUMBER;
4010 /* End of Bug #: 13924722 */
4011
4012 begin
4013 l_progress := 'POS_WCAPPROVE_PVT.insert_into_rti: 01.';
4014 IF (g_asn_debug = 'Y') THEN
4015 debug_log(FND_LOG.LEVEL_STATEMENT,
4016 l_api_name,
4017 'Enter in insert_into_rti '
4018 || l_progress);
4019 END IF;
4020
4021 if (p_funcmode <> wf_engine.eng_run) then
4022 x_resultout := wf_engine.eng_null;
4023 return;
4024 end if;
4025
4026 l_shipment_header_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
4027 itemkey => p_itemkey,
4028 aname => 'WORK_CONFIRMATION_ID');
4029
4030
4031 SELECT rcv_headers_interface_s.NEXTVAL
4032 INTO l_header_interface_id
4033 FROM SYS.DUAL;
4034
4035 select rcv_interface_groups_s.nextval
4036 into l_group_id
4037 from dual;
4038
4039 IF (g_asn_debug = 'Y') THEN
4040 debug_log(FND_LOG.LEVEL_STATEMENT,
4041 l_api_name,'l_header_interface_id ' ||
4042 l_header_interface_id ||
4043 ' l_group_id ' || l_group_id);
4044 END IF;
4045
4046 wf_engine.SetItemAttrNumber (itemtype => p_itemtype,
4047 itemkey => p_itemkey,
4048 aname => 'INTERFACE_GROUP_ID',
4049 avalue => l_group_id);
4050
4051 l_emp_ok := po_employees_sv.get_employee (X_emp_id,
4052 X_emp_name, X_location_id, X_location_code,
4053 X_is_buyer, X_emp_flag );
4054
4055 IF (g_asn_debug = 'Y') THEN
4056 debug_log(FND_LOG.LEVEL_STATEMENT,
4057 l_api_name,'x_emp_id ' ||
4058 x_emp_id);
4059 END IF;
4060
4061 IF (g_asn_debug = 'Y') THEN
4062 debug_log(FND_LOG.LEVEL_STATEMENT,
4063 l_api_name,'before cursor open');
4064 END IF;
4065
4066 /* ******************************************************************
4067 Bug 8810238 - enabling over receiving functionality during creation of
4068 work confirmations for complex Purchase orders.
4069
4070 Modified the earlier logic used to insert data in RTI tables, since it was not populating data correctly in the RTI tables.
4071 Removed the earlier commented code to incorporate the new logic.
4072 Please refer to comments and detailed technical resolution data in bug 88100238 BCT for more information
4073
4074 ******************************************************************** */
4075
4076 -- opening the work confirmation cursor at pay item level
4077 open get_wcr_info(l_shipment_header_id);
4078 -- looping through the pay items associated with the current work confirmation
4079 loop --{
4080
4081 l_progress := 'POS_WCAPPROVE_PVT.insert_into_rti:02.';
4082
4083 IF (g_asn_debug = 'Y') THEN
4084 debug_log(FND_LOG.LEVEL_STATEMENT,
4085 l_api_name,'before cursor fetch');
4086 END IF;
4087
4088 fetch get_wcr_info into wcr_line_info;
4089 exit when get_wcr_info%notfound;
4090
4091 IF (g_asn_debug = 'Y') THEN
4092 debug_log(FND_LOG.LEVEL_STATEMENT,
4093 l_api_name,'l_shipment_line_id ' ||
4094 wcr_line_info.shipment_line_id);
4095 END IF;
4096
4097 l_clm_flag := wcr_line_info.clm_flag;
4098
4099 IF (g_asn_debug = 'Y') THEN
4100 debug_log(FND_LOG.LEVEL_STATEMENT,
4101 l_api_name,'l_clm_flag ' ||
4102 l_clm_flag);
4103 END IF;
4104
4105 If (wcr_line_info.unit_of_measure is not null) then
4106 select muom.uom_code
4107 into l_uom_code
4108 from mtl_units_of_measure muom
4109 WHERE muom.unit_of_measure = wcr_line_info.unit_of_measure;
4110
4111 IF (g_asn_debug = 'Y') THEN
4112 debug_log(FND_LOG.LEVEL_STATEMENT,
4113 l_api_name,'l_uom_code ' ||
4114 l_uom_code);
4115 END IF;
4116
4117 end if;
4118
4119 IF (g_asn_debug = 'Y') THEN
4120 debug_log(FND_LOG.LEVEL_STATEMENT,
4121 l_api_name,'before cursor open');
4122 END IF;
4123
4124 l_matching_basis:= wcr_line_info.matching_basis;
4125
4126 If (l_matching_basis = 'AMOUNT') then
4127 l_remaining_amount:= wcr_line_info.amount_shipped;
4128 end if;
4129
4130 If (l_matching_basis = 'QUANTITY') then
4131 l_remaining_quantity:= wcr_line_info.quantity_shipped;
4132 end if;
4133
4134 -- getting the number of distributions associated at the current pay item level
4135 SELECT Count(*)
4136 INTO l_max_dist
4137 FROM po_distributions_all pod
4138 where pod.line_location_id = wcr_line_info.po_line_location_id;
4139
4140 l_dist_count := 0;
4141
4142 -- added for wc correction ER - bug 9414650
4143 -- the following two attributes take care that the requested amount and material stored values get updated only for the
4144 -- first distribution, and for the subsequent distributions they are entered as null.
4145 -- ( should these be entered as null or zero ?)
4146 -- entering them as null since for amount, quantity we insert null value for unpopulated distributions
4147
4148 l_req_amount_inserted := FALSE;
4149 l_mat_stored_inserted := FALSE;
4150
4151 -- opening the cursor for fetching distribution level information into the wcr record to be inserted into RTI
4152 open get_dist_info(wcr_line_info.po_line_location_id);
4153
4154 -- looping through the distributions cursor to insert data in RTI
4155 loop --{
4156 l_progress := 'POS_WCAPPROVE_PVT.insert_into_rti:03.';
4157
4158 IF (g_asn_debug = 'Y') THEN
4159 debug_log(FND_LOG.LEVEL_STATEMENT,
4160 l_api_name,'before cursor fetch');
4161 END IF;
4162
4163 /* Bug 6709928 ,getting the destination_type_code,destination_type_context*/
4164 fetch get_dist_info into
4165 wcr_line_info.po_distribution_id,
4166 wcr_line_info.currency_conversion_date,
4167 wcr_line_info.currency_conversion_rate,
4168 wcr_line_info.charge_account_id,
4169 wcr_line_info.destination_organization_id,
4170 wcr_line_info.subinventory,
4171 wcr_line_info.document_distribution_num,
4172 wcr_line_info.quantity_ordered,
4173 wcr_line_info.amount_ordered,
4174 wcr_line_info.quantity_funded,
4175 wcr_line_info.amount_funded,
4176 wcr_line_info.partial_funded_flag,
4177 wcr_line_info.destination_type_code,
4178 wcr_line_info.destination_context;
4179 exit when get_dist_info%notfound or
4180 (l_matching_basis = 'AMOUNT' and l_remaining_amount <= 0)
4181 or
4182 (l_matching_basis = 'QUANTITY' and l_remaining_quantity <= 0);
4183
4184 l_dist_count := l_dist_count + 1;
4185
4186 IF (g_asn_debug = 'Y') THEN
4187 debug_log(FND_LOG.LEVEL_STATEMENT,
4188 l_api_name,'l_distribution_id ' ||
4189 wcr_line_info.po_distribution_id);
4190 END IF;
4191
4192 IF (g_asn_debug = 'Y') THEN
4193 debug_log(FND_LOG.LEVEL_STATEMENT,
4194 l_api_name,'matching_basis '||l_matching_basis );
4195 END IF;
4196
4197 l_partial_funded_flag := wcr_line_info.partial_funded_flag;
4198
4199 -- set the work confirmation variables for service based lines
4200 If (l_matching_basis = 'AMOUNT') then--{
4201
4202 /* l_transacted_amount = amount which was transacted earlier than the submission of
4203 current work confirmation for this payitem / distribution */
4204 select nvl(sum(amount),0)
4205 into l_transacted_amount
4206 from rcv_transactions
4207 where po_distribution_id= wcr_line_info.po_distribution_id
4208 and destination_type_code = 'RECEIVING';
4209
4210 IF (g_asn_debug = 'Y') THEN
4211 debug_log(FND_LOG.LEVEL_STATEMENT,
4212 l_api_name,'l_transacted_amount '||
4213 l_transacted_amount );
4214 END IF;
4215
4216 /* l_interface_amount = amount which is in the interface tables /pending to be approved / rejected before the submission of
4217 current work confirmation for this payitem / distribution */
4218 select nvl(sum(amount),0)
4219 into l_interface_amount
4220 from rcv_transactions_interface
4221 where po_distribution_id= wcr_line_info.po_distribution_id
4222 and processing_status_code='PENDING'
4223 and transaction_status_code = 'PENDING'
4224 and transaction_type = 'RECEIVE';
4225
4226 IF (g_asn_debug = 'Y') THEN
4227 debug_log(FND_LOG.LEVEL_STATEMENT,
4228 l_api_name,'l_interface_amount '||
4229 l_interface_amount );
4230 END IF;
4231
4232 IF (l_clm_flag = 'Y' and l_partial_funded_flag = 'Y') THEN
4233 /* l_available_amount = actual amount left to be transacted for the current pay item */
4234 l_available_amount := wcr_line_info.amount_funded - (l_transacted_amount + l_interface_amount);
4235 ELSE
4236 /* l_available_amount = actual amount left to be transacted for the current pay item */
4237 l_available_amount := wcr_line_info.amount_ordered - (l_transacted_amount + l_interface_amount);
4238 END IF;
4239
4240 /* l_carry_over_amount = actual amount left to be transacted over to the next distribution
4241 for the current pay item after the current distribution is considered */
4242 l_carry_over_amount := l_remaining_amount - l_available_amount;
4243
4244
4245 IF (g_asn_debug = 'Y') THEN
4246 debug_log(FND_LOG.LEVEL_STATEMENT,
4247 l_api_name,'l_available_amount '||
4248 l_available_amount );
4249 END IF;
4250
4251 -- check if this is the last distribution for the pay item
4252 IF (l_dist_count >= l_max_dist) THEN
4253 -- last distribution for pay item, insert the l_remaining_amount completely
4254 IF (l_clm_flag = 'Y' and l_partial_funded_flag = 'Y') THEN
4255 IF(l_available_amount > 0) THEN
4256 IF(l_carry_over_amount > 0) THEN
4257 wcr_line_info.amount := l_available_amount;
4258 l_remaining_amount := l_remaining_amount - l_available_amount;
4259 ELSE
4260 wcr_line_info.amount := l_remaining_amount;
4261 l_remaining_amount := 0;
4262 END IF;
4263 l_insert_into_rti := TRUE;
4264 ELSE
4265 l_insert_into_rti := FALSE;
4266 END IF;
4267 ELSE
4268 wcr_line_info.amount := l_remaining_amount;
4269 l_remaining_amount := 0;
4270 l_insert_into_rti := TRUE;
4271 END IF;
4272
4273 -- added this code to correct the population of requested_amount and
4274 -- material stored amount correctly in the RTI table
4275 -- earlier all the distributions were getting populated with same value
4276 -- now only the first distribution which is accessed by the above logic is populated with
4277 -- these two values
4278
4279 IF(l_req_amount_inserted) THEN
4280 wcr_line_info.requested_amount := null;
4281 END IF;
4282
4283 IF(l_mat_stored_inserted) THEN
4284 wcr_line_info.material_stored_amount := null;
4285 END IF;
4286
4287 l_req_amount_inserted := TRUE;
4288 l_mat_stored_inserted := TRUE;
4289
4290 ELSE
4291 -- not the last distribution for the pay item, check if we need to insert or not
4292 IF(l_available_amount > 0) THEN
4293 -- this distribution is not yet completely filled,
4294 -- so we "need to insert" depending on l_remaining_amount and l_available_amount
4295 IF(l_carry_over_amount > 0) THEN
4296 -- the shipped amount is greater than the l_available_amount
4297 wcr_line_info.amount := l_available_amount;
4298 l_remaining_amount := l_remaining_amount - l_available_amount;
4299
4300
4301 ELSE
4302 -- the shipped amount is lesser than the l_available_amount
4303 wcr_line_info.amount := l_remaining_amount;
4304 l_remaining_amount := 0;
4305
4306
4307
4308 END IF;
4309
4310 -- added this code to correct the population of requested_amount and
4311 -- material stored amount correctly in the RTI table
4312 -- earlier all the distributions were getting populated with same value
4313 -- now only the first distribution which is accessed by the above logic is populated with
4314 -- these two values
4315 IF(l_req_amount_inserted) THEN
4316 wcr_line_info.requested_amount := null;
4317 END IF;
4318
4319 IF(l_mat_stored_inserted) THEN
4320 wcr_line_info.material_stored_amount := null;
4321 END IF;
4322
4323 l_req_amount_inserted := TRUE;
4324 l_mat_stored_inserted := TRUE;
4325
4326 l_insert_into_rti := TRUE;
4327
4328 ELSE
4329
4330 -- l_available_amount < 0, so "no need to insert"
4331 l_insert_into_rti := FALSE;
4332
4333 END IF;
4334
4335 END IF;
4336
4337 -- set the work confirmation variables for quantity based lines
4338 elsif (l_matching_basis = 'QUANTITY') then --}{
4339
4340 /* l_transacted_quantity = quantity which was transacted earlier than the submission of
4341 current work confirmation for this payitem / distribution */
4342 select nvl(sum(quantity),0)
4343 into l_transacted_quantity
4344 from rcv_transactions
4345 where po_distribution_id= wcr_line_info.po_distribution_id
4346 and destination_type_code = 'RECEIVING';
4347
4348 IF (g_asn_debug = 'Y') THEN
4349 debug_log(FND_LOG.LEVEL_STATEMENT,
4350 l_api_name,'l_transacted_quantity '||
4351 l_transacted_quantity );
4352 END IF;
4353
4354 /* l_interface_quantity = quantity which is in the interface tables /pending to be approved / rejected before the submission of
4355 current work confirmation for this payitem / distribution */
4356 select nvl(sum(quantity),0)
4357 into l_interface_quantity
4358 from rcv_transactions_interface
4359 where po_distribution_id= wcr_line_info.po_distribution_id
4360 and processing_status_code='PENDING'
4361 and transaction_status_code = 'PENDING'
4362 and transaction_type = 'RECEIVE';
4363
4364 IF (g_asn_debug = 'Y') THEN
4365 debug_log(FND_LOG.LEVEL_STATEMENT,
4366 l_api_name,'l_interface_quantity '||
4367 l_interface_quantity );
4368 END IF;
4369
4370 IF (l_clm_flag = 'Y' and l_partial_funded_flag = 'Y') THEN
4371 /* l_available_quantity = actual quantity left to be transacted for the current pay item */
4372 l_available_quantity := wcr_line_info.quantity_funded - (l_transacted_quantity + l_interface_quantity);
4373 ELSE
4374 /* l_available_quantity = actual quantity left to be transacted for the current pay item */
4375 l_available_quantity := wcr_line_info.quantity_ordered - (l_transacted_quantity + l_interface_quantity);
4376 END IF;
4377
4378 /* l_carry_over_quantity = actual quantity left to be transacted over to the next distribution
4379 for the current pay item after the current distribution is considered */
4380 l_carry_over_quantity := l_remaining_quantity - l_available_quantity;
4381
4382 IF (g_asn_debug = 'Y') THEN
4383 debug_log(FND_LOG.LEVEL_STATEMENT,
4384 l_api_name,'l_available_quantity '||
4385 l_available_quantity );
4386 END IF;
4387
4388 -- check if this is the last distribution for the pay item
4389 IF (l_dist_count >= l_max_dist) THEN
4390 -- last distribution for pay item, insert the l_remaining_quantity completely
4391 IF (l_clm_flag = 'Y' and l_partial_funded_flag = 'Y') THEN
4392 IF(l_available_quantity > 0) THEN
4393 IF(l_carry_over_quantity > 0) THEN
4394 wcr_line_info.quantity := l_available_quantity;
4395 l_remaining_quantity := l_remaining_quantity - l_available_quantity;
4396 ELSE
4397 wcr_line_info.quantity := l_remaining_quantity;
4398 l_remaining_quantity := 0;
4399 END IF;
4400 l_insert_into_rti := TRUE;
4401 ELSE
4402 l_insert_into_rti := FALSE;
4403 END IF;
4404 ELSE
4405 wcr_line_info.quantity := l_remaining_quantity;
4406 l_remaining_quantity := 0;
4407 l_insert_into_rti := TRUE;
4408 END IF;
4409 ELSE
4410 -- not the last distribution for the pay item, check if we need to insert or not
4411 IF(l_available_quantity > 0) THEN
4412 -- this distribution is not yet completely filled,
4413 -- so we "need to insert" depending on l_remaining_quantity and l_available_quantity
4414 IF(l_carry_over_quantity > 0) THEN
4415 -- the shipped quantity is greater than the l_available_quantity
4416 wcr_line_info.quantity := l_available_quantity;
4417 l_remaining_quantity := l_remaining_quantity - l_available_quantity;
4418
4419 ELSE
4420 -- the shipped quantity is lesser than the l_available_quantity
4421 wcr_line_info.quantity := l_remaining_quantity;
4422 l_remaining_quantity := 0;
4423
4424 END IF;
4425
4426 l_insert_into_rti := TRUE;
4427
4428 ELSE
4429 -- l_available_amount < 0, so "no need to insert"
4430 l_insert_into_rti := FALSE;
4431
4432 END IF;
4433
4434 END IF;
4435
4436 end if; --}
4437
4438 /* end of code changes for bug 8810238 */
4439
4440 If (l_insert_into_rti) then --{
4441 select rcv_transactions_interface_s.nextval
4442 into l_interface_id
4443 from dual;
4444
4445 IF (g_asn_debug = 'Y') THEN
4446 debug_log(FND_LOG.LEVEL_STATEMENT,
4447 l_api_name,'l_interface_id ' || l_interface_id);
4448 END IF;
4449
4450 IF(wcr_line_info.matching_basis = 'QUANTITY') THEN
4451
4452 po_uom_s.uom_convert(from_quantity => wcr_line_info.quantity,
4453 from_uom => wcr_line_info.unit_of_measure,
4454 item_id => wcr_line_info.item_id,
4455 to_uom => wcr_line_info.primary_unit_of_measure,
4456 to_quantity => l_primary_quantity_in);
4457
4458 wcr_line_info.primary_quantity := l_primary_quantity_in;
4459
4460 END IF;
4461
4462 /*Added for the Bug #: 13924722 */
4463 IF(wcr_line_info.match_option = 'R') THEN
4464 IF (wcr_line_info.currency_conversion_type = 'User') THEN
4465 l_currency_conversion_date := SYSDATE;
4466 l_currency_conversion_rate := wcr_line_info.currency_conversion_rate;
4467 ELSE
4468 SELECT set_of_books_id
4469 INTO x_sob_Id
4470 FROM financials_system_parameters WHERE org_id = wcr_line_info.org_id;
4471
4472 l_currency_conversion_rate := gl_currency_api.get_rate(x_sob_Id,wcr_line_info.currency_code,sysdate,wcr_line_info.currency_conversion_type);
4473 l_currency_conversion_rate := round(l_currency_conversion_rate,28);
4474 l_currency_conversion_date := SYSDATE;
4475 END IF;
4476 ELSIF (wcr_line_info.match_option = 'P') THEN
4477 l_currency_conversion_rate := wcr_line_info.currency_conversion_rate;
4478 l_currency_conversion_date := wcr_line_info.currency_conversion_date;
4479 END IF;
4480
4481 /* End of changes for Bug #: 13924722 */
4482
4483 rcv_asn_interface_trx_ins_pkg.insert_row
4484 (l_row_id,
4485 l_interface_id,--interface_id
4486 l_group_id, --group_id
4487 sysdate, --last_updated_date
4488 fnd_global.user_id, --last_updated_by,
4489 sysdate, --creation_date,
4490 fnd_global.login_id, --created_by,
4491 fnd_global.login_id, -- last_update_login,
4492 NULL, --request_id,
4493 null, --program_application_id,
4494 null, --program_id,
4495 null, --program_update_date,
4496 'RECEIVE', --transaction_type,
4497 sysdate, --transaction_date,
4498 'PENDING', --processing_status_code,
4499 'IMMEDIATE', --processing_mode_code,
4500 --'BATCH',
4501 null, --processing_request_id,
4502 'PENDING', --.transaction_status_code,
4503 wcr_line_info.category_id,
4504 wcr_line_info.quantity, --quantity
4505 wcr_line_info.unit_of_measure,
4506 'ISP', --.interface_source_code,
4507 NULL, --.interface_source_line_id,
4508 NULL, --.inv_transaction_id,
4509 wcr_line_info.item_id,
4510 wcr_line_info.item_description,
4511 wcr_line_info.item_revision,
4512 l_uom_code, --uom_code,
4513 x_emp_id, --employee_id,
4514 'DELIVER', --auto_transact_code,
4515 l_shipment_header_id, --l_shipment_header_id
4516 wcr_line_info.shipment_line_id,
4517 wcr_line_info.ship_to_location_id,
4518 wcr_line_info.primary_quantity,
4519 wcr_line_info.primary_unit_of_measure,
4520 'VENDOR', --.receipt_source_code,
4521 wcr_line_info.vendor_id,
4522 wcr_line_info.vendor_site_id,
4523 NULL, --from_organization_id,
4524 NULL, --from_subinventory,
4525 wcr_line_info.to_organization_id,
4526 NULL, --.intransit_owning_org_id,
4527 wcr_line_info.routing_header_id,
4528 NULL, --.routing_step_id,
4529 'PO', --source_document_code,
4530 NULL, --.parent_transaction_id,
4531 wcr_line_info.po_header_id,
4532 wcr_line_info.po_revision_num,
4533 wcr_line_info.po_release_id,
4534 wcr_line_info.po_line_id,
4535 wcr_line_info.po_line_location_id,
4536 wcr_line_info.po_unit_price,
4537 wcr_line_info.currency_code,
4538 wcr_line_info.currency_conversion_type,
4539 l_currency_conversion_rate, --Bug #: 13924722 wcr_line_info.currency_conversion_rate
4540 l_currency_conversion_date, --Bug #: 13924722 wcr_line_info.currency_conversion_date
4541 wcr_line_info.po_distribution_id,
4542 wcr_line_info.requisition_line_id,
4543 wcr_line_info.req_distribution_id,
4544 wcr_line_info.charge_account_id,
4545 NULL, --.substitute_unordered_code,
4546 NULL, --.receipt_exception_flag,
4547 NULL, --.accrual_status_code,
4548 'NOT INSPECTED' ,--.inspection_status_code,
4549 NULL, --.inspection_quality_code,
4550 wcr_line_info.destination_type_code,
4551 wcr_line_info.deliver_to_person_id,
4552 wcr_line_info.location_id,
4553 wcr_line_info.deliver_to_location_id,
4554 NULL, --.subinventory,
4555 NULL, --.locator_id,
4556 NULL, --.wip_entity_id,
4557 NULL, --.wip_line_id,
4558 NULL, --.department_code,
4559 NULL, --.wip_repetitive_schedule_id,
4560 NULL, --.wip_operation_seq_num,
4561 NULL, --.wip_resource_seq_num,
4562 NULL, --.bom_resource_id,
4563 wcr_line_info.shipment_num,
4564 wcr_line_info.freight_carrier_code,
4565 wcr_line_info.bill_of_lading,
4566 wcr_line_info.packing_slip,
4567 wcr_line_info.shipped_date,
4568 wcr_line_info.expected_receipt_date,
4569 NULL, --.actual_cost,
4570 NULL, --.transfer_cost,
4571 NULL, --.transportation_cost,
4572 NULL, --.transportation_account_id,
4573 NULL, --.num_of_containers,
4574 wcr_line_info.waybill_airbill_num,
4575 wcr_line_info.vendor_item_num,
4576 wcr_line_info.vendor_lot_num,
4577 NULL,--.rma_reference,
4578 wcr_line_info.comments,
4579 wcr_line_info.attribute_category,
4580 wcr_line_info.attribute1,
4581 wcr_line_info.attribute2,
4582 wcr_line_info.attribute3,
4583 wcr_line_info.attribute4,
4584 wcr_line_info.attribute5,
4585 wcr_line_info.attribute6,
4586 wcr_line_info.attribute7,
4587 wcr_line_info.attribute8,
4588 wcr_line_info.attribute9,
4589 wcr_line_info.attribute10,
4590 wcr_line_info.attribute11,
4591 wcr_line_info.attribute12,
4592 wcr_line_info.attribute13,
4593 wcr_line_info.attribute14,
4594 wcr_line_info.attribute15,
4595 NULL, --.ship_head_attribute_category,
4596 NULL, --.ship_head_attribute1,
4597 NULL, --.ship_head_attribute2,
4598 NULL, --.ship_head_attribute3,
4599 NULL, --.ship_head_attribute4,
4600 NULL, --.ship_head_attribute5,
4601 NULL, --.ship_head_attribute6,
4602 NULL, --.ship_head_attribute7,
4603 NULL, --.ship_head_attribute8,
4604 NULL, --.ship_head_attribute9,
4605 NULL, --.ship_head_attribute10,
4606 NULL, --.ship_head_attribute11,
4607 NULL, --.ship_head_attribute12,
4608 NULL, --.ship_head_attribute13,
4609 NULL, --.ship_head_attribute14,
4610 NULL, --.ship_head_attribute15,
4611 NULL, --.ship_line_attribute_category,
4612 NULL, --.ship_line_attribute1,
4613 NULL, --.ship_line_attribute2,
4614 NULL, --.ship_line_attribute3,
4615 NULL, --.ship_line_attribute4,
4616 NULL, --.ship_line_attribute5,
4617 NULL, --.ship_line_attribute6,
4618 NULL, --.ship_line_attribute7,
4619 NULL, --.ship_line_attribute8,
4620 NULL, --.ship_line_attribute9,
4621 NULL, --.ship_line_attribute10,
4622 NULL, --.ship_line_attribute11,
4623 NULL, --.ship_line_attribute12,
4624 NULL, --.ship_line_attribute13,
4625 NULL, --.ship_line_attribute14,
4626 NULL, --.ship_line_attribute15,
4627 wcr_line_info.ussgl_transaction_code,
4628 wcr_line_info.government_context,
4629 NULL, --.reason_id,
4630 wcr_line_info.destination_context,
4631 wcr_line_info.source_doc_quantity,
4632 wcr_line_info.source_doc_unit_of_measure,
4633 NULL, --.movement_id,
4634 NULL, --l_header_interface_id, --.header_interface_id,
4635 NULL, --.vendor_cum_shipped_qty,
4636 NULL, --.item_num,
4637 wcr_line_info.document_num,
4638 wcr_line_info.document_line_num,
4639 NULL, --.truck_num,
4640 NULL, --.ship_to_location_code,
4641 NULL, --.container_num,
4642 NULL, --.substitute_item_num,
4643 NULL, --.notice_unit_price,
4644 NULL, --.item_category,
4645 NULL, --.location_code,
4646 NULL, --.vendor_name,
4647 NULL, --.vendor_num,
4648 NULL, --.vendor_site_code,
4649 NULL, --.from_organization_code,
4650 NULL, --.to_organization_code,
4651 NULL, --.intransit_owning_org_code,
4652 NULL, --.routing_code,
4653 NULL, --.routing_step,
4654 NULL, --.release_num,
4655 wcr_line_info.document_shipment_line_num,
4656 wcr_line_info.document_distribution_num,
4657 NULL, --.deliver_to_person_name,
4658 NULL, --.deliver_to_location_code,
4659 NULL, --.use_mtl_lot,
4660 NULL, --.use_mtl_serial,
4661 NULL, --.LOCATOR,
4662 NULL, --.reason_name,
4663 NULL, --.validation_flag,
4664 NULL, --.substitute_item_id,
4665 NULL, --.quantity_shipped,
4666 NULL, --.quantity_invoiced,
4667 NULL, --.tax_name,
4668 NULL, --.tax_amount,
4669 NULL, --.req_num,
4670 NULL, --.req_line_num,
4671 NULL, --.req_distribution_num,
4672 NULL, --.wip_entity_name,
4673 NULL, --.wip_line_code,
4674 NULL, --.resource_code,
4675 NULL, --.shipment_line_status_code,
4676 NULL, --.barcode_label,
4677 NULL, --.country_of_origin_code,
4678 NULL, --.from_locator_id, --WMS Change
4679 NULL, --.qa_collection_id,
4680 NULL, --.oe_order_header_id,
4681 NULL, --.oe_order_line_id,
4682 NULL, --.customer_id,
4683 NULL, --.customer_site_id,
4684 NULL, --.customer_item_num,
4685 NULL, --.create_debit_memo_flag,
4686 NULL, --.put_away_rule_id,
4687 NULL, --.put_away_strategy_id,
4688 NULL, --.lpn_id,
4689 NULL, --.transfer_lpn_id,
4690 NULL, --.cost_group_id,
4691 NULL, --.mobile_txn,
4692 NULL, --.mmtt_temp_id,
4693 NULL, --.transfer_cost_group_id,
4694 NULL, --.secondary_quantity,
4695 NULL, --.secondary_unit_of_measure,
4696 NULL, --.secondary_uom_code,
4697 NULL, --.qc_grade,
4698 NULL, --.oe_order_num,
4699 NULL, --.oe_order_line_num,
4700 NULL, --.customer_account_number,
4701 NULL, --.customer_party_name,
4702 NULL, --.source_transaction_num,
4703 NULL, --.parent_source_transaction_num,
4704 NULL, --.parent_interface_txn_id,
4705 NULL, --.customer_item_id,
4706 NULL, --.interface_available_qty,
4707 NULL, --.interface_transaction_qty,
4708 NULL, --.from_locator,
4709 NULL, --.lpn_group_id,
4710 NULL, --.order_transaction_id,
4711 NULL, --.license_plate_number,
4712 NULL, --.transfer_license_plate_number,
4713 wcr_line_info.amount,
4714 wcr_line_info.job_id,
4715 NULL, --.project_id,
4716 NULL, --.task_id,
4717 NULL, --.asn_attach_id,
4718 NULL, --.timecard_id,
4719 NULL, --.timecard_ovn,
4720 NULL, --.interface_available_amt,
4721 NULL, --.interface_transaction_amt
4722 wcr_line_info.org_id, --<R12 MOAC>
4723 wcr_line_info.matching_basis,
4724 NULL, --wcr_line_info.amount_shipped, --amount_shipped
4725 wcr_line_info.requested_amount,
4726 wcr_line_info.material_stored_amount
4727 );
4728
4729 IF (g_asn_debug = 'Y') THEN
4730 debug_log(FND_LOG.LEVEL_STATEMENT,
4731 l_api_name,'After insert ' );
4732
4733 END IF;
4734
4735 END IF; --}
4736
4737 end loop; --}
4738
4739 If get_dist_info%isopen then
4740 Close get_dist_info;
4741 end if;
4742
4743 IF (l_clm_flag = 'Y' AND (l_remaining_amount > 0 OR l_remaining_quantity >0) ) THEN
4744 BEGIN
4745 select min(pod.po_distribution_id)
4746 INTO l_full_fund_dist_id
4747 from po_distributions_all pod
4748 where pod.line_location_id = wcr_line_info.po_line_location_id AND
4749 Nvl(pod.partial_funded_flag,'N') = 'N';
4750
4751 IF(l_full_fund_dist_id IS NOT null) THEN
4752 If (l_matching_basis = 'AMOUNT') then
4753 IF(l_remaining_amount > 0) THEN
4754 SELECT INTERFACE_TRANSACTION_ID,
4755 AMOUNT
4756 INTO l_int_transaction_id,
4757 l_full_fund_amt
4758 FROM RCV_TRANSACTIONS_INTERFACE RTI
4759 WHERE PO_DISTRIBUTION_ID = l_full_fund_dist_id AND
4760 GROUP_ID = l_group_id;
4761
4762 l_full_fund_amt := l_full_fund_amt +l_remaining_amount;
4763 l_remaining_amount := 0;
4764
4765 UPDATE RCV_TRANSACTIONS_INTERFACE RTI
4766 SET AMOUNT = l_full_fund_amt
4767 WHERE INTERFACE_TRANSACTION_ID = l_int_transaction_id AND
4768 GROUP_ID = l_group_id;
4769 END IF;
4770 end if;
4771
4772 If (l_matching_basis = 'QUANTITY') then
4773 IF(l_remaining_quantity >0) THEN
4774 SELECT INTERFACE_TRANSACTION_ID,
4775 QUANTITY
4776 INTO l_int_transaction_id,
4777 l_full_fund_qty
4778 FROM RCV_TRANSACTIONS_INTERFACE RTI
4779 WHERE PO_DISTRIBUTION_ID = l_full_fund_dist_id AND
4780 GROUP_ID = l_group_id;
4781
4782 l_full_fund_qty := l_full_fund_qty +l_remaining_quantity;
4783 l_remaining_quantity := 0;
4784
4785 UPDATE RCV_TRANSACTIONS_INTERFACE RTI
4786 SET QUANTITY = l_full_fund_qty
4787 WHERE INTERFACE_TRANSACTION_ID = l_int_transaction_id AND
4788 GROUP_ID = l_group_id;
4789 END IF;
4790 end if;
4791
4792 END IF;
4793
4794 EXCEPTION WHEN OTHERS THEN
4795 IF (g_asn_debug = 'Y') THEN
4796 debug_log(FND_LOG.LEVEL_UNEXPECTED, l_api_name,'Exception in clm funding assignment'|| l_progress);
4797 END IF;
4798 raise;
4799 END;
4800 END IF;
4801
4802 IF (g_asn_debug = 'Y') THEN
4803 debug_log(FND_LOG.LEVEL_STATEMENT,
4804 l_api_name,'Going to fetch the next shipment line if it exists ' );
4805 END IF;
4806
4807 end loop; --}
4808
4809 If get_wcr_info%isopen then
4810 Close get_wcr_info;
4811 end if;
4812
4813 GenReceiptNum(l_shipment_header_id,l_receipt_num);
4814 IF (g_asn_debug = 'Y') THEN
4815 debug_log(FND_LOG.LEVEL_STATEMENT,
4816 l_api_name,'l_receipt_num '||l_receipt_num);
4817 END IF;
4818
4819 update rcv_shipment_headers
4820 set receipt_num= l_receipt_num,
4821 last_update_date = sysdate,
4822 last_updated_by = fnd_global.user_id,
4823 last_update_login = fnd_global.login_id
4824 where shipment_header_id = l_shipment_header_id;
4825
4826 IF (g_asn_debug = 'Y') THEN
4827 debug_log(FND_LOG.LEVEL_STATEMENT,
4828 l_api_name,'Leave insert_into_rti ' );
4829 END IF;
4830
4831 x_resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
4832
4833 return;
4834
4835 exception
4836
4837 when others then
4838 IF (g_asn_debug = 'Y') THEN
4839 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4840 l_api_name,'Exception in insert_into_rti '
4841 || l_progress);
4842 END IF;
4843 raise;
4844
4845 END insert_into_rti;
4846
4847
4848
4849 procedure Launch_RTP_Immediate
4850 (p_itemtype in varchar2,
4851 p_itemkey in varchar2,
4852 p_actid in number,
4853 p_funcmode in varchar2,
4854 x_resultout out NOCOPY varchar2) IS
4855
4856 l_progress varchar2(300);
4857 l_result_id number;
4858 l_group_id number;
4859 l_api_name varchar2(50) := p_itemkey || ' Launch_RTP_Immediate';
4860 begin
4861 l_progress := 'POS_WCAPPROVE_PVT.Launch_RTP_Immediate: 01.';
4862 IF (g_asn_debug = 'Y') THEN
4863 debug_log(FND_LOG.LEVEL_STATEMENT,
4864 l_api_name,
4865 'Enter in Launch_RTP_Immediate '
4866 || l_progress);
4867 END IF;
4868
4869 if (p_funcmode <> wf_engine.eng_run) then
4870 x_resultout := wf_engine.eng_null;
4871 return;
4872 end if;
4873
4874 l_group_id := wf_engine.GetItemAttrNumber
4875 (itemtype => p_itemtype,
4876 itemkey => p_itemkey,
4877 aname => 'INTERFACE_GROUP_ID');
4878
4879 IF (g_asn_debug = 'Y') THEN
4880 debug_log(FND_LOG.LEVEL_STATEMENT,
4881 l_api_name,
4882 'l_group_id '
4883 || l_group_id);
4884 END IF;
4885 l_result_id :=
4886 fnd_request.submit_request('PO',
4887 'RVCTP',
4888 null,
4889 null,
4890 false,
4891 'IMMEDIATE',
4892 --'BATCH',
4893 l_group_id,
4894 --fnd_char.local_chr(0),
4895 NULL, -- Modified as part of P1 Bug #:16208460
4896 NULL,
4897 NULL,
4898 NULL,
4899 NULL,
4900 NULL, NULL,
4901 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4902 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4903 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4904 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4905 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4906
4907 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4908 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4909 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4910 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4911
4912 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4913 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4914 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4915 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
4916
4917 IF (g_asn_debug = 'Y') THEN
4918 debug_log(FND_LOG.LEVEL_STATEMENT,
4919 l_api_name, 'l_result_id '
4920 || l_result_id);
4921 END IF;
4922 if (l_result_id <= 0 or l_result_id is null) then
4923
4924
4925 UPDATE rcv_transactions_interface
4926 set transaction_status_code = 'ERROR'
4927 where group_id = l_group_id;
4928
4929
4930
4931 end if;
4932
4933 IF (g_asn_debug = 'Y') THEN
4934 debug_log(FND_LOG.LEVEL_STATEMENT,
4935 l_api_name, 'Leave RTP launch ' );
4936 END IF;
4937 x_resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
4938 return;
4939
4940 exception
4941 when others then
4942 IF (g_asn_debug = 'Y') THEN
4943 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4944 l_api_name,'Exception in Launch_RTP_Immediate '
4945 || l_progress);
4946 END IF;
4947 raise;
4948
4949 END Launch_RTP_Immediate;
4950
4951 procedure CloseOldNotif
4952 (
4953 p_itemtype in varchar2,
4954 p_itemkey in varchar2) IS
4955 -- pragma AUTONOMOUS_TRANSACTION;
4956 l_api_name varchar2(50) := p_itemkey || ' CloseOldNotif';
4957 l_progress varchar2(300);
4958 begin
4959
4960 l_progress := 'POS_WCAPPROVE_PVT.CloseOldNotif: 01';
4961
4962 IF (g_asn_debug = 'Y') THEN
4963 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4964 l_api_name,'Enter in CloseOldNotif ' || l_progress);
4965 END IF;
4966 update wf_notifications set status = 'CLOSED'
4967 where notification_id in (
4968 select ias.notification_id
4969 from wf_item_activity_statuses ias,
4970 wf_notifications ntf
4971 where ias.item_type = p_itemtype
4972 and ias.item_key = p_itemkey
4973 and ntf.notification_id = ias.notification_id);
4974
4975
4976 exception
4977 when others then
4978 IF (g_asn_debug = 'Y') THEN
4979 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4980 l_api_name,'Exception in CloseOldNotif ' || l_progress);
4981 END IF;
4982 raise;
4983 end CloseOldNotif;
4984
4985 procedure UpdateWorkflowInfo
4986 (
4987 p_itemtype in varchar2,
4988 p_itemkey in varchar2,
4989 p_shipment_header_id in varchar2) IS
4990 -- pragma AUTONOMOUS_TRANSACTION;
4991
4992 l_api_name varchar2(50) := p_itemkey || ' UpdateWorkflowInfo';
4993 l_progress varchar2(300);
4994 begin
4995
4996 l_progress := 'POS_WCAPPROVE_PVT.UpdateWorkflowInfo: 01';
4997
4998 IF (g_asn_debug = 'Y') THEN
4999 debug_log(FND_LOG.LEVEL_STATEMENT,
5000 l_api_name,'Enter in UpdateWorkflowInfo ' || l_progress);
5001 debug_log(FND_LOG.LEVEL_STATEMENT,
5002 l_api_name,'p_itemtype ' || p_itemtype);
5003 debug_log(FND_LOG.LEVEL_STATEMENT,
5004 l_api_name,'p_itemkey ' || p_itemkey);
5005 debug_log(FND_LOG.LEVEL_STATEMENT,
5006 l_api_name,'p_shipment_header_id ' || p_shipment_header_id);
5007 END IF;
5008
5009 UPDATE rcv_shipment_headers
5010 SET WF_ITEM_TYPE = p_itemtype,
5011 WF_ITEM_KEY = p_itemkey,
5012 last_updated_by = fnd_global.user_id,
5013 last_update_login = fnd_global.login_id,
5014 last_update_date = sysdate
5015 WHERE shipment_header_id = p_shipment_header_id;
5016
5017
5018
5019 debug_log(FND_LOG.LEVEL_STATEMENT,
5020 l_api_name,'do i see this now? ' || p_shipment_header_id);
5021 exception
5022 when others then
5023 IF (g_asn_debug = 'Y') THEN
5024 debug_log(FND_LOG.LEVEL_UNEXPECTED,
5025 l_api_name,'Exception in UpdateWorkflowInfo ' || l_progress);
5026 END IF;
5027 raise;
5028 end UpdateWorkflowInfo;
5029
5030 PROCEDURE Upd_ActionHistory_Submit (p_object_id IN NUMBER,
5031 p_object_type_code IN VARCHAR2,
5032 p_employee_id IN NUMBER,
5033 p_sequence_num IN NUMBER,
5034 p_action_code IN VARCHAR2,
5035 p_user_id IN NUMBER,
5036 p_login_id IN NUMBER)
5037 IS
5038 -- pragma AUTONOMOUS_TRANSACTION;
5039 l_api_name varchar2(50) := ' Upd_ActionHistory_Submit';
5040 l_progress varchar2(280);
5041 begin
5042
5043 l_progress := 'POS_WCAPPROVE_PVT.Upd_ActionHistory_Submit: 01';
5044 IF (g_asn_debug = 'Y') THEN
5045 debug_log(FND_LOG.LEVEL_UNEXPECTED,
5046 l_api_name,'Enter in Upd_ActionHistory_Submit '
5047 || l_progress);
5048 END IF;
5049
5050 UPDATE PO_ACTION_HISTORY
5051 set object_id = p_object_id,
5052 object_type_code = p_object_type_code,
5053 sequence_num = p_sequence_num,
5054 last_update_date = sysdate,
5055 last_updated_by = p_user_id,
5056 creation_date = sysdate,
5057 created_by = p_user_id,
5058 action_code = p_action_code,
5059 action_date = sysdate,
5060 employee_id = p_employee_id,
5061 last_update_login = p_login_id,
5062 request_id = 0,
5063 program_application_id = 0,
5064 program_id = 0,
5065 program_update_date = '',
5066 offline_code = ''
5067 WHERE
5068 object_id= p_object_id and
5069 object_type_code = p_object_type_code and
5070 sequence_num = p_sequence_num;
5071 exception
5072 when others then
5073 IF (g_asn_debug = 'Y') THEN
5074 debug_log(FND_LOG.LEVEL_UNEXPECTED,
5075 l_api_name,'Exception in Upd_ActionHistory_Submit ' || l_progress);
5076 END IF;
5077 raise;
5078 end Upd_ActionHistory_Submit;
5079
5080
5081 PROCEDURE get_multiorg_context(p_document_id number,
5082 x_orgid IN OUT NOCOPY number) IS
5083 cursor get_po_orgid is
5084 select org_id
5085 from po_headers_all
5086 where po_header_id = p_document_id;
5087
5088 l_progress varchar2(300);
5089 l_api_name varchar2(50) := ' get_multiorg_context';
5090 begin
5091 l_progress := 'POS_WCAPPROVE_PVT.get_multiorg_context: 01';
5092 IF (g_asn_debug = 'Y') THEN
5093 debug_log(FND_LOG.LEVEL_STATEMENT,
5094 l_api_name,'Enter in get_multiorg_context '
5095 || l_progress);
5096 END IF;
5097
5098 OPEN get_po_orgid;
5099 FETCH get_po_orgid into x_orgid;
5100 CLOSE get_po_orgid;
5101 exception
5102 when others then
5103 IF (g_asn_debug = 'Y') THEN
5104 debug_log(FND_LOG.LEVEL_UNEXPECTED,
5105 l_api_name,'Exception in get_multiorg_context ' || l_progress);
5106 END IF;
5107 raise;
5108 end get_multiorg_context;
5109
5110
5111 FUNCTION Get_Approver_Name(p_approver_id IN NUMBER)
5112 RETURN VARCHAR2 IS
5113 l_value VARCHAR2(1000) := '';
5114 begin
5115 select distinct full_name
5116 into l_value
5117 from per_all_people_f hre
5118 where hre.person_id = p_approver_id
5119 and trunc(sysdate) BETWEEN effective_start_date
5120 and effective_end_date;
5121
5122 if l_value is null then
5123 l_value := null;
5124 end if;
5125 return l_value;
5126 exception
5127 when others then
5128 return null;
5129
5130 end Get_Approver_Name;
5131
5132 FUNCTION Get_PoHeaderId(p_shipment_header_id IN NUMBER)
5133 RETURN NUMBER IS
5134 l_progress varchar2(500);
5135 l_po_header_id number;
5136 l_api_name varchar2(50) := 'get_PoHeaderId';
5137 begin
5138 l_progress := 'POS_WCAPPROVE_PVT.get_PoHeaderId: 01';
5139 IF (g_asn_debug = 'Y') THEN
5140 debug_log(FND_LOG.LEVEL_UNEXPECTED,
5141 l_api_name,'Enter in get_PoHeaderId ' || l_progress);
5142 END IF;
5143
5144 select max(po_header_id)
5145 into l_po_header_id
5146 from rcv_shipment_lines
5147 where shipment_header_id = p_shipment_header_id;
5148
5149 return l_po_header_id;
5150
5151 exception
5152 when others then
5153 IF (g_asn_debug = 'Y') THEN
5154 debug_log(FND_LOG.LEVEL_UNEXPECTED,
5155 l_api_name,'Exception in get_PoHeaderId ' || l_progress);
5156 END IF;
5157 raise;
5158
5159 end Get_PoHeaderId;
5160
5161 PROCEDURE GenReceiptNum(
5162 p_shipment_header_id IN number,
5163 x_receipt_num IN OUT nocopy Varchar2
5164 ) IS
5165 PRAGMA AUTONOMOUS_TRANSACTION;
5166 l_count number;
5167 l_ship_to_org_id number;
5168 l_api_name varchar2(50) := ' GenReceiptNum';
5169 BEGIN
5170
5171 select ship_to_org_id
5172 into l_ship_to_org_id
5173 from rcv_shipment_headers
5174 where shipment_header_id=p_shipment_header_id;
5175
5176 IF (g_asn_debug = 'Y') THEN
5177 debug_log(FND_LOG.LEVEL_STATEMENT,
5178 l_api_name,'l_ship_to_org_id '
5179 ||l_ship_to_org_id);
5180 END IF;
5181
5182 BEGIN
5183 SELECT (next_receipt_num + 1)
5184 INTO x_receipt_num
5185 FROM rcv_parameters
5186 WHERE organization_id = l_ship_to_org_id
5187 FOR UPDATE OF next_receipt_num;
5188
5189 LOOP
5190 SELECT COUNT(*)
5191 INTO l_count
5192 FROM rcv_shipment_headers
5193 WHERE receipt_num = x_receipt_num
5194 AND ship_to_org_id = l_ship_to_org_id;
5195
5196 IF l_count = 0 THEN
5197 UPDATE rcv_parameters
5198 SET next_receipt_num = x_receipt_num
5199 WHERE organization_id = l_ship_to_org_id;
5200
5201 EXIT;
5202 ELSE
5203 x_receipt_num := TO_CHAR(TO_NUMBER(x_receipt_num) + 1);
5204 END IF;
5205 END LOOP;
5206
5207 COMMIT;
5208 EXCEPTION
5209 WHEN OTHERS THEN
5210 ROLLBACK;
5211 END;
5212 End GenReceiptNum;
5213
5214
5215 FUNCTION GET_PAY_ITEM_PROGRESS (p_wc_id IN NUMBER,
5216 p_wc_stage IN VARCHAR2)
5217 RETURN NUMBER
5218 IS
5219
5220 l_return_status varchar2(1);
5221 l_return_msg varchar2(2000);
5222 l_progress number;
5223 begin
5224
5225 POS_WC_CREATE_UPDATE_PVT.GET_PAY_ITEM_PROGRESS
5226 (p_wc_id,
5227 p_wc_stage,
5228 l_progress,
5229 l_return_status,
5230 l_return_msg);
5231
5232 if (l_return_status = FND_API.G_RET_STS_SUCCESS ) then
5233 return l_progress;
5234 else
5235 /* Some error. So return -999 */
5236 l_progress := -999;
5237 return l_progress;
5238 end if;
5239
5240 END GET_PAY_ITEM_PROGRESS;
5241
5242 FUNCTION GET_AWARD_NUM (p_wc_id IN NUMBER)
5243 RETURN VARCHAR2 IS
5244 l_count number;
5245 l_award_number gms_awards_all.award_number%type;
5246 begin
5247
5248 select count(*)
5249 into l_count
5250 from gms_awards_all awd,
5251 gms_award_distributions adl,
5252 po_distributions_all pod
5253 where adl.award_id = awd.award_id
5254 and adl.adl_line_num = 1
5255 and adl.po_distribution_id =pod.po_distribution_id
5256 and adl.award_set_id = pod.award_id
5257 and pod.po_distribution_id = p_wc_id;
5258
5259 If (l_count = 0) then
5260 return to_char(null);
5261 else
5262 select awd.award_number
5263 into l_award_number
5264 from gms_awards_all awd,
5265 gms_award_distributions adl,
5266 po_distributions_all pod
5267 where adl.award_id = awd.award_id
5268 and adl.adl_line_num = 1
5269 and adl.po_distribution_id =pod.po_distribution_id
5270 and adl.award_set_id = pod.award_id
5271 and pod.po_header_id = p_wc_id;
5272
5273 return l_award_number;
5274 end if;
5275 end GET_AWARD_NUM;
5276
5277
5278 FUNCTION GET_DELIVER_TO_LOCATION (p_wc_id IN NUMBER)
5279 RETURN VARCHAR2 IS
5280 l_count number;
5281 l_deliver_to_location hr_locations_all_tl.location_code%type;
5282 begin
5283
5284 select count(*)
5285 into l_count
5286 from hr_locations_all_tl hl,
5287 po_distributions_all pod
5288 where pod.deliver_to_location_id = hl.location_id and
5289 hl.language (+) =userenv('LANG') and
5290 pod.po_distribution_id = p_wc_id;
5291
5292
5293 If (l_count = 0) then
5294 return to_char(null);
5295 else
5296 select hl.location_code
5297 into l_deliver_to_location
5298 from hr_locations_all_tl hl,
5299 po_distributions_all pod
5300 where pod.deliver_to_location_id = hl.location_id and
5301 hl.language (+) =userenv('LANG') and
5302 pod.po_distribution_id = p_wc_id;
5303
5304
5305 return l_deliver_to_location;
5306 end if;
5307 end GET_DELIVER_TO_LOCATION;
5308
5309 FUNCTION GET_ORDERED_AMOUNT (p_wc_id IN NUMBER)
5310 RETURN number IS
5311 l_count number;
5312 l_amount po_line_locations_all.amount%type;
5313 begin
5314
5315 select count(*)
5316 into l_count
5317 from po_line_locations_all poll
5318 where poll.line_location_id = p_wc_id
5319 and nvl(poll.matching_basis,'QUANTITY')='AMOUNT';
5320
5321
5322 If (l_count = 0) then
5323 return to_number(null);
5324 else
5325
5326 select poll.amount
5327 into l_amount
5328 from po_line_locations_all poll
5329 where poll.line_location_id = p_wc_id
5330 and nvl(poll.matching_basis,'QUANTITY')='AMOUNT';
5331
5332
5333 return l_amount;
5334 end if;
5335 end GET_ORDERED_AMOUNT;
5336
5337
5338 FUNCTION GET_ORDERED_QUANTITY (p_wc_id IN NUMBER)
5339 RETURN number IS
5340 l_count number;
5341 l_quantity po_line_locations_all.quantity%type;
5342 begin
5343
5344 select count(*)
5345 into l_count
5346 from po_line_locations_all poll
5347 where poll.line_location_id = p_wc_id
5348 and nvl(poll.matching_basis,'QUANTITY')='QUANTITY';
5349
5350
5351 If (l_count = 0) then
5352 return to_number(null);
5353 else
5354
5355 select poll.quantity
5356 into l_quantity
5357 from po_line_locations_all poll
5358 where poll.line_location_id = p_wc_id
5359 and nvl(poll.matching_basis,'QUANTITY')='QUANTITY';
5360
5361
5362 return l_quantity;
5363 end if;
5364 end GET_ORDERED_QUANTITY;
5365
5366
5367 FUNCTION GET_PROJECT_NAME (p_wc_id IN NUMBER)
5368 RETURN VARCHAR2 IS
5369 l_count number;
5370 l_project_name pa_projects_all.name%type;
5371 begin
5372
5373 select count(*)
5374 into l_count
5375 from pa_projects_all pa,
5376 po_distributions_all pod
5377 where pod.project_id = pa.project_id and
5378 pod.po_distribution_id = p_wc_id;
5379
5380
5381 If (l_count = 0) then
5382 return to_char(null);
5383 else
5384
5385 select pa.name
5386 into l_project_name
5387 from pa_projects_all pa,
5388 po_distributions_all pod
5389 where pod.project_id = pa.project_id and
5390 pod.po_distribution_id = p_wc_id;
5391
5392
5393 return l_project_name;
5394 end if;
5395 end GET_PROJECT_NAME;
5396
5397 FUNCTION GET_TASK_NAME (p_wc_id IN NUMBER)
5398 RETURN VARCHAR2 IS
5399 l_count number;
5400 l_task_name pa_tasks_expend_v.task_name%type;
5401 begin
5402
5403 select count(*)
5404 into l_count
5405 from pa_tasks_expend_v pa,
5406 po_distributions_all pod
5407 where pod.task_id = pa.task_id and
5408 pod.po_distribution_id = p_wc_id;
5409
5410
5411 If (l_count = 0) then
5412 return to_char(null);
5413 else
5414
5415 select pa.task_name
5416 into l_task_name
5417 from pa_tasks_expend_v pa,
5418 po_distributions_all pod
5419 where pod.task_id = pa.task_id and
5420 pod.po_distribution_id = p_wc_id;
5421
5422 return l_task_name;
5423 end if;
5424 end GET_TASK_NAME;
5425
5426 FUNCTION GET_CHARGE_ACCOUNT (p_wc_id IN NUMBER)
5427 RETURN VARCHAR2 IS
5428 l_count number;
5429 l_segments GL_CODE_COMBINATIONS_KFV.concatenated_segments%type;
5430 begin
5431
5432 select count(*)
5433 into l_count
5434 from gl_code_combinations_kfv glc,
5435 po_distributions_all pod
5436 where pod.code_combination_id = glc.code_combination_id and
5437 pod.po_distribution_id = p_wc_id;
5438
5439
5440 If (l_count = 0) then
5441 return to_char(null);
5442 else
5443
5444 select glc.concatenated_segments
5445 into l_segments
5446 from gl_code_combinations_kfv glc,
5447 po_distributions_all pod
5448 where pod.code_combination_id = glc.code_combination_id and
5449 pod.po_distribution_id = p_wc_id;
5450
5451 return l_segments;
5452 end if;
5453 end GET_CHARGE_ACCOUNT;
5454
5455 FUNCTION GET_EXPENDITURE_ORG (p_wc_id IN NUMBER)
5456 RETURN VARCHAR2 IS
5457 l_count number;
5458 l_org_name org_organization_definitions.organization_name%type;
5459 begin
5460
5461 select count(*)
5462 into l_count
5463 from org_organization_definitions ood,
5464 po_distributions_all pod
5465 where pod.expenditure_organization_id = ood.organization_id
5466 and pod.po_distribution_id = p_wc_id;
5467
5468
5469 If (l_count = 0) then
5470 return to_char(null);
5471 else
5472
5473 select ood.organization_name
5474 into l_org_name
5475 from org_organization_definitions ood,
5476 po_distributions_all pod
5477 where pod.expenditure_organization_id = ood.organization_id
5478 and pod.po_distribution_id = p_wc_id;
5479
5480 return l_org_name;
5481 end if;
5482 end GET_EXPENDITURE_ORG;
5483
5484 /* Bug 8479430.
5485 Added the procedure POWC_SELECTOR to set the user context properly before
5486 launching the concurrent request */
5487 -------------------------------------------------------------------------------
5488 -- Start of Comments
5489 -- Name: POWC_SELECTOR
5490 -- Pre-reqs: None.
5491 -- Modifies:
5492 -- Application user id
5493 -- Application responsibility id
5494 -- Application application id
5495 -- Locks: None.
5496 -- Function:
5497 -- This procedure sets the correct application context when a process is
5498 -- picked up by the workflow background engine. When called in
5499 -- TEST_CTX mode it compares workflow attribute org id with the current
5500 -- org id and workflow attributes user id, responsibility id and
5501 -- application id with their corresponding profile values. It returns TRUE
5502 -- if these values match and FALSE otherwise. When called in SET_CTX mode
5503 -- it sets the correct apps context based on workflow parameters.
5504 -- Parameters:
5505 -- IN:
5506 -- p_itemtype
5507 -- Specifies the itemtype of the workflow process
5508 -- p_itemkey
5509 -- Specifies the itemkey of the workflow process
5510 -- p_actid
5511 -- activity id passed by the workflow
5512 -- p_funcmode
5513 -- Input values can be TEST_CTX or SET_CTX (RUN not implemented)
5514 -- TEST_CTX to test if current context is correct
5515 -- SET_CTX to set the correct context if current context is wrong
5516 -- IN OUT:
5517 -- p_x_result
5518 -- For TEST_CTX a TRUE value means that the context is correct and
5519 -- SET_CTX need not be called. A FALSE value means that current context
5520 -- is incorrect and SET_CTX need to set correct context
5521 -- Testing:
5522 -- There is not script to test this procedure but the correct functioning
5523 -- may be tested by verifying from the debug_message in table po_wf_debug
5524 -- that if at any time the workflow process gets started with a wrong
5525 -- context then the selector is called in TEST_CTX and SET_CTX modes and
5526 -- correct context is set.
5527 -- End of Comments
5528 -------------------------------------------------------------------------------
5529 PROCEDURE POWC_SELECTOR ( p_itemtype IN VARCHAR2,
5530 p_itemkey IN VARCHAR2,
5531 p_actid IN NUMBER,
5532 p_funcmode IN VARCHAR2,
5533 p_x_result IN OUT NOCOPY VARCHAR2) IS
5534
5535 -- Declare context setting variables start
5536 l_session_user_id NUMBER;
5537 l_session_resp_id NUMBER;
5538 l_responder_id NUMBER;
5539 l_user_id_to_set NUMBER;
5540 l_resp_id_to_set NUMBER;
5541 l_appl_id_to_set NUMBER;
5542 l_progress VARCHAR2(1000);
5543 l_preserved_ctx VARCHAR2(5):= 'TRUE';
5544 l_org_id NUMBER;
5545 -- Declare context setting variables End
5546
5547 BEGIN
5548
5549 -- <debug start>
5550 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5551 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','Inside POWC_SELECTOR procedure');
5552 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','p_itemtype : '||p_itemtype);
5553 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','p_itemkey : '||p_itemkey);
5554 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','p_actid : '||p_actid);
5555 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','p_funcmode : '||p_funcmode);
5556 END IF;
5557 -- <debug end>
5558
5559
5560 l_org_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
5561 itemkey => p_itemkey,
5562 aname => 'ORG_ID');
5563 l_session_user_id := fnd_global.user_id;
5564 l_session_resp_id := fnd_global.resp_id;
5565
5566 IF (l_session_user_id = -1) THEN
5567 l_session_user_id := NULL;
5568 END IF;
5569
5570 IF (l_session_resp_id = -1) THEN
5571 l_session_resp_id := NULL;
5572 END IF;
5573
5574 l_responder_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
5575 itemkey => p_itemkey,
5576 aname => 'RESPONDER_USER_ID');
5577
5578 --<debug start>
5579 l_progress :='010 - ses_user_id:'||l_session_user_id ||' ses_resp_id :'||l_session_resp_id||' responder id:' ||l_responder_id||' org id :'||l_org_id;
5580
5581 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5582 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','l_progress1 : '||l_progress);
5583 END IF;
5584 --<debug end>
5585
5586 IF (p_funcmode = 'TEST_CTX') THEN
5587 -- wf shouldn't run without the session user, hence always set the ctx if session user id is null.
5588 if (l_session_user_id is null) then
5589 p_x_result := 'NOTSET';
5590 return;
5591 else
5592 if (l_responder_id is not null) then
5593 if (l_responder_id <> l_session_user_id) then
5594 p_x_result := 'FALSE';
5595 return;
5596 else
5597 if (l_session_resp_id is Null) then
5598 p_x_result := 'NOTSET';
5599 return;
5600 else
5601 -- If the selector fn is called from a background ps
5602 -- notif mailer then force the session to use preparer's or responder
5603 -- context. This is required since the mailer/bckgrnd ps carries the
5604 -- context from the last wf processed and hence even if the context values
5605 -- are present, they might not be correct.
5606
5607 if (wf_engine.preserved_context = TRUE) then
5608 p_x_result := 'TRUE';
5609 else
5610 p_x_result:= 'NOTSET';
5611 end if;
5612
5613 -- introduce an org context setting call here
5614 -- required in the case when the right resonder makes a response
5615 -- from a NON-PO RESP.
5616 IF l_org_id is NOT NULL THEN
5617 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;
5618 END IF;
5619 return;
5620 end if;
5621 end if;
5622 else
5623 -- always setting the ctx at the start of the wf
5624 p_x_result := 'NOTSET';
5625 return;
5626 end if;
5627 end if; -- l_session_user_id is null
5628 ELSIF (p_funcmode = 'SET_CTX') THEN
5629 if l_responder_id is not null then
5630
5631 l_user_id_to_set := l_responder_id;
5632
5633 l_resp_id_to_set := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
5634 itemkey => p_itemkey,
5635 aname => 'RESPONDER_RESP_ID');
5636 l_appl_id_to_set := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
5637 itemkey => p_itemkey,
5638 aname => 'RESPONDER_APPL_ID');
5639 --<debug start>
5640 l_progress := '020 selection fn responder id not null';
5641
5642 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5643 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','l_progress2 : '||l_progress);
5644 END IF;
5645 --<debug end>
5646
5647 --<debug start>
5648 l_progress :='030 selector fn : setting user id :'||l_responder_id ||' resp id '||l_resp_id_to_set||' l_appl id '||l_appl_id_to_set;
5649
5650 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5651 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','l_progress3 : '||l_progress);
5652 END IF;
5653 --<debug end>
5654 else
5655 l_user_id_to_set := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
5656 itemkey => p_itemkey,
5657 aname => 'USER_ID');
5658 l_resp_id_to_set := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
5659 itemkey => p_itemkey,
5660 aname => 'RESPONSIBILITY_ID');
5661 l_appl_id_to_set := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
5662 itemkey => p_itemkey,
5663 aname => 'APPLICATION_ID');
5664 --<debug start>
5665 l_progress := '040 selector fn responder id null';
5666
5667 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5668 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','l_progress4 : '||l_progress);
5669 END IF;
5670 --<debug end>
5671
5672 --<debug start>
5673 l_progress := '050 selector fn : set user '||l_user_id_to_set||' resp id ' ||l_resp_id_to_set||' appl id '||l_appl_id_to_set;
5674
5675 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5676 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','l_progress4 : '||l_progress);
5677 END IF;
5678 --<debug end>
5679 end if;
5680
5681 fnd_global.apps_initialize(l_user_id_to_set, l_resp_id_to_set,l_appl_id_to_set);
5682
5683 -- obvious place to make such a call, since we are using an apps_initialize,
5684 -- this is required since the responsibility might have a different OU attached
5685 -- than what is required.
5686
5687 IF l_org_id is NOT NULL THEN
5688 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;
5689 END IF;
5690
5691 END IF;
5692
5693 EXCEPTION WHEN OTHERS THEN
5694
5695 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5696 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_WCAPPROVE_PVT.POWC_SELECTOR.invoked','Exception in Selector Procedure');
5697 END IF;
5698
5699 WF_CORE.context('POS_WCAPPROVE_PVT', 'POWC_SELECTOR', p_itemtype, p_itemkey, p_actid, p_funcmode);
5700 RAISE;
5701
5702 END POWC_SELECTOR;
5703
5704 END POS_WCAPPROVE_PVT;