[Home] [Help]
PACKAGE BODY: APPS.POS_WCAPPROVE_PVT
Source
1 PACKAGE BODY POS_WCAPPROVE_PVT AS
2 /* $Header: POSVWCAB.pls 120.19.12010000.3 2008/11/04 04:09:40 puppulur 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
563 l_view_wc_lines_detail_url :=
564 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/pos/wc/webui/WcDetailsPG' || '&' ||
565 --'WcStatus=PENDING APPROVAL' || '&' ||
566 'PoHeaderId=' || to_char(l_po_header_id) || '&' ||
567 'WcHeaderId=' || to_char(l_work_confirmation_id) || '&' ||
568 'WcNum=' || (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
578 l_respond_to_wc_url :=
579 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/pos/wc/webui/WcRespondPG' || '&' ||
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=' || (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 l_respond_to_wc_url :=
1107 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/pos/wc/webui/WcRespondPG' || '&' ||
1108 'SrcPage=DetailsInternalPG' || '&' ||
1109 'WcStatus=PENDING APPROVAL' || '&' ||
1110 'PoHeaderId=' || to_char(l_po_header_id) || '&' ||
1111 'WcHeaderId='||to_char(l_shipment_header_id)||'&' ||
1112 'WcNum=' || (l_work_confirmation_number) || '&' ||
1113 'addBreadCrumb=Y';
1114
1115 wf_engine.SetItemAttrText ( itemtype => p_itemType,
1116 itemkey => p_itemkey,
1117 aname => 'RESPOND_TO_WC_URL' ,
1118 avalue => l_respond_to_wc_url);
1119
1120 IF (g_asn_debug = 'Y') THEN
1121 debug_log(FND_LOG.LEVEL_STATEMENT,
1122 l_api_name,'before call to applicablerule ');
1123 End if;
1124 ame_api3.getApplicableRules1(applicationIdIn=>l_application_Id,
1125 transactionIdIn=>l_shipment_header_id,
1126 transactionTypeIn=> l_transaction_type,
1127 ruleIdsOut => l_rule_id);
1128 /* If there are no rules , then this will
1129 * return 0.
1130 * Check whether the workflow attribute DEFAULT_APPROVER
1131 * is BUYER. If it is, then this is already been approved
1132 * by him. So send NO_NEXT_APPROVER.
1133 * If the value is no BUYER then set the attribute with this
1134 * value.
1135 */
1136 if (l_rule_id.count = 0 ) then --{
1137 IF (g_asn_debug = 'Y') THEN
1138 debug_log(FND_LOG.LEVEL_STATEMENT,
1139 l_api_name,'no rule is setup ');
1140 End if;
1141 l_default_approver :=
1142 wf_engine.GetItemAttrText (itemtype => p_itemtype,
1143 itemkey => p_itemkey,
1144 aname => 'DEFAULT_APPROVER');
1145
1146 IF (g_asn_debug = 'Y') THEN
1147 debug_log(FND_LOG.LEVEL_STATEMENT,
1148 l_api_name,'l_default_approver '
1149 || l_default_approver);
1150 END IF;
1151
1152 If l_default_approver = 'BUYER' then --{
1153
1154 IF (g_asn_debug = 'Y') THEN
1155 debug_log(FND_LOG.LEVEL_STATEMENT,
1156 l_api_name,'already approved by buyer ');
1157 End if;
1158
1159 x_resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
1160 return;
1161 elsif(l_default_approver= 'NONE') then --}{
1162 IF (g_asn_debug = 'Y') THEN
1163 debug_log(FND_LOG.LEVEL_STATEMENT,
1164 l_api_name,'Send buyer as default approver ');
1165 End if;
1166
1167 wf_engine.SetItemAttrText ( itemtype => p_itemType,
1168 itemkey => p_itemkey,
1169 aname => 'DEFAULT_APPROVER' ,
1170 avalue => 'BUYER');
1171
1172 l_po_preparer_id := wf_engine.GetItemAttrNumber
1173 (itemtype => p_itemtype,
1174 itemkey => p_itemkey,
1175 aname => 'PO_PREPARER_ID');
1176
1177 IF (g_asn_debug = 'Y') THEN
1178 debug_log(FND_LOG.LEVEL_STATEMENT,
1179 l_api_name,'l_po_preparer_id '
1180 || l_po_preparer_id);
1181 end if;
1182
1183 l_next_approver(1).orig_system := 'PER';
1184 l_next_approver(1).orig_system_id := l_po_preparer_id;
1185
1186 end if; --}
1187
1188
1189 else --}{
1190
1191 IF (g_asn_debug = 'Y') THEN
1192 debug_log(FND_LOG.LEVEL_STATEMENT,
1193 l_api_name,'call ame api ');
1194 End if;
1195
1196 ame_api2.getNextApprovers4(applicationIdIn=>l_application_Id,
1197 transactionIdIn=>l_shipment_header_id,
1198 transactionTypeIn=> l_transaction_type,
1199 approvalProcessCompleteYNOut=>l_completeYNO,
1200 nextApproversOut=>l_next_approver);
1201
1202 end if; --}
1203
1204 if (l_next_approver.count > 1) then -- {
1205 x_resultout:='COMPLETE:'||'INVALID_APPROVER';
1206 raise approver_exception ;
1207 elsif (l_next_approver.count = 0 ) then --}{
1208 x_resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
1209 return;
1210 IF (g_asn_debug = 'Y') THEN
1211 debug_log(FND_LOG.LEVEL_STATEMENT,
1212 l_api_name,'no_next_approver ');
1213 End if;
1214 return;
1215 ELSE --}{
1216
1217 if (l_next_approver(1).orig_system = 'PER') then --{
1218 l_next_approver_id := l_next_approver(1).orig_system_id;
1219 elsif (l_next_approver(1).orig_system = 'POS') then --}{
1220 begin
1221 -- find the persondid from the position_id
1222 SELECT person_id, full_name
1223 into l_next_approver_id,l_next_approver_name
1224 FROM (
1225 SELECT person.person_id, person.full_name
1226 FROM per_all_people_f person, per_all_assignments_f asg
1227 WHERE asg.position_id = l_next_approver(1).orig_system_id
1228 and trunc(sysdate) between person.effective_start_date
1229 and nvl(person.effective_end_date, trunc(sysdate))
1230 and person.person_id = asg.person_id
1231 and asg.primary_flag = 'Y'
1232 and asg.assignment_type in ('E','C')
1233 and ( person.current_employee_flag = 'Y'
1234 or person.current_npw_flag = 'Y' )
1235 and asg.assignment_status_type_id not in
1236 (
1237 SELECT assignment_status_type_id
1238 FROM per_assignment_status_types
1239 WHERE per_system_status = 'TERM_ASSIGN'
1240 )
1241 and trunc(sysdate) between asg.effective_start_date
1242 and asg.effective_end_date
1243 order by person.last_name
1244 ) where rownum = 1;
1245 exception
1246 WHEN NO_DATA_FOUND THEN
1247 RAISE;
1248 END;
1249
1250 elsif (l_next_approver(1).orig_system = 'FND') then --}{
1251 SELECT employee_id
1252 into l_next_approver_id
1253 FROM fnd_user
1254 WHERE user_id = l_next_approver(1).orig_system_id
1255 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
1256 end if; --}
1257
1258
1259 IF (g_asn_debug = 'Y') THEN
1260 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
1261 'person_id ' || l_next_approver_id);
1262 END IF;
1263
1264 IF (g_asn_debug = 'Y') THEN
1265 debug_log(FND_LOG.LEVEL_STATEMENT,
1266 l_api_name,'l_next_approver_id '
1267 || l_next_approver_id);
1268 debug_log(FND_LOG.LEVEL_STATEMENT,
1269 l_api_name,'l_insertion_type '
1270 || l_insertion_type);
1271 debug_log(FND_LOG.LEVEL_STATEMENT,
1272 l_api_name,'l_authority_type '
1273 || l_authority_type);
1274 END IF;
1275
1276 wf_engine.SetItemAttrText( itemtype => p_itemType,
1277 itemkey => p_itemkey,
1278 aname => 'AME_APPROVER_TYPE' ,
1279 avalue => l_next_approver(1).orig_system);
1280 wf_engine.SetItemAttrNumber ( itemtype => p_itemType,
1281 itemkey => p_itemkey,
1282 aname => 'APPROVER_EMPID',
1283 avalue => l_next_approver_id);
1284
1285
1286 wf_engine.SetItemAttrNumber ( itemtype => p_itemType,
1287 itemkey => p_itemkey,
1288 aname => 'FORWARD_TO_ID',
1289 avalue => l_next_approver_id);
1290
1291 wf_engine.SetItemAttrText( itemtype => p_itemType,
1292 itemkey => p_itemkey,
1293 aname => 'AME_INSERTION_TYPE' ,
1294 avalue => l_insertion_type);
1295
1296 wf_engine.SetItemAttrText( itemtype => p_itemType,
1297 itemkey => p_itemkey,
1298 aname => 'AME_AUTHORITY_TYPE' ,
1299 avalue => l_authority_type);
1300
1301 l_orig_system:= 'PER';
1302 l_progress := 'POS_WCAPPROVE_PVT.get_next_approver: 03.';
1303
1304 WF_DIRECTORY.GetUserName(l_orig_system,
1305 l_next_approver_id,
1306 l_next_approver_user_name,
1307 l_next_approver_disp_name);
1308
1309 IF (g_asn_debug = 'Y') THEN
1310 debug_log(FND_LOG.LEVEL_STATEMENT,
1311 l_api_name,'l_next_approver_user_name '
1312 || l_next_approver_user_name);
1313 debug_log(FND_LOG.LEVEL_STATEMENT,
1314 l_api_name,'l_next_approver_disp_name '
1315 || l_next_approver_disp_name);
1316 end if;
1317 wf_engine.SetItemAttrText( itemtype => p_itemType,
1318 itemkey => p_itemkey,
1319 aname => 'APPROVER_USER_NAME' ,
1320 avalue => l_next_approver_user_name);
1321
1322 wf_engine.SetItemAttrText( itemtype => p_itemType,
1323 itemkey => p_itemkey,
1324 aname => 'APPROVER_DISPLAY_NAME' ,
1325 avalue => l_next_approver_disp_name);
1326
1327 wf_engine.SetItemAttrText( itemtype => p_itemType,
1328 itemkey => p_itemkey,
1329 aname => 'FORWARD_TO_USERNAME' ,
1330 avalue => l_next_approver_user_name);
1331
1332 wf_engine.SetItemAttrText( itemtype => p_itemType,
1333 itemkey => p_itemkey,
1334 aname => 'FORWARD_TO_DISPLAY_NAME' ,
1335 avalue => l_next_approver_disp_name);
1336
1337
1338 x_resultout:='COMPLETE:'||'VALID_APPROVER';
1339 return;
1340 END IF; --}
1341
1342
1343 Exception
1344 when approver_exception then
1345 IF (g_asn_debug = 'Y') THEN
1346 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1347 l_api_name,
1348 'Exception in ame_util.getNextApprove '
1349 || l_progress);
1350 END IF;
1351 raise;
1352 when others then
1353 IF (g_asn_debug = 'Y') THEN
1354 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1355 l_api_name,'Exception in get_next_approve '
1356 || l_progress);
1357 END IF;
1358 raise;
1359 end get_next_approver;
1360
1361
1362 procedure Insert_Action_History(p_itemtype in varchar2,
1363 p_itemkey in varchar2,
1364 p_actid in number,
1365 p_funcmode in varchar2,
1366 x_resultout out NOCOPY varchar2) is
1367
1368 l_progress varchar2(200);
1369 l_approver_id number;
1370 l_shipment_header_id number;
1371 l_shipment_line_id number;
1372 l_count number;
1373 l_sequence_num number;
1374 l_api_name varchar2(50) := p_itemkey || ' Insert_Action_History';
1375
1376 /*
1377 cursor get_shipment_lines(l_shipment_header_id number) is
1378 select shipment_line_id
1379 from rcv_shipment_lines
1380 where shipment_header_id=l_shipment_header_id;
1381 */
1382
1383 CURSOR get_action_history IS
1384 SELECT object_id,
1385 object_type_code,
1386 object_sub_type_code,
1387 sequence_num,
1388 object_revision_num,
1389 request_id,
1390 program_application_id,
1391 program_date,
1392 program_id,
1393 last_update_date,
1394 employee_id
1395 FROM PO_ACTION_HISTORY
1396 WHERE object_type_code = 'WC'
1397 AND object_id = l_shipment_header_id
1398 AND sequence_num = l_sequence_num;
1399
1400 Recinfo get_action_history%ROWTYPE;
1401
1402 invalid_data exception;
1403 invalid_action exception;
1404
1405 BEGIN
1406
1407
1408 if (p_funcmode <> wf_engine.eng_run) then
1409 x_resultout := wf_engine.eng_null;
1410 return;
1411 end if;
1412
1413
1414
1415
1416 l_progress := 'POS_WCAPPROVE_PVT.Insert_Action_History: 01.';
1417 IF (g_asn_debug = 'Y') THEN
1418 debug_log(FND_LOG.LEVEL_STATEMENT,
1419 l_api_name,'Enter in Insert_Action_History '
1420 || l_progress);
1421 END IF;
1422
1423
1424
1425 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>p_itemtype,
1426 itemkey=>p_itemkey,
1427 aname=>'APPROVER_EMPID');
1428 IF (g_asn_debug = 'Y') THEN
1429 debug_log(FND_LOG.LEVEL_STATEMENT,
1430 l_api_name,'l_approver_id '
1431 || l_approver_id);
1432 END IF;
1433
1434 l_shipment_header_id:= wf_engine.GetItemAttrNumber(itemtype=>p_itemtype,
1435 itemkey=>p_itemkey,
1436 aname=>'WORK_CONFIRMATION_ID');
1437 IF (g_asn_debug = 'Y') THEN
1438 debug_log(FND_LOG.LEVEL_STATEMENT,
1439 l_api_name,'l_shipment_header_id '
1440 || l_shipment_header_id);
1441 END IF;
1442
1443
1444
1445 -- loop --{
1446 l_progress := 'POS_WCAPPROVE_PVT.Insert_Action_History: 04.';
1447
1448 SELECT count(*)
1449 INTO l_count
1450 FROM PO_ACTION_HISTORY
1451 WHERE object_type_code = 'WC'
1452 AND object_id = l_shipment_header_id
1453 AND action_code IS NULL;
1454
1455 IF (g_asn_debug = 'Y') THEN
1456 debug_log(FND_LOG.LEVEL_STATEMENT,
1457 l_api_name,'l_count '
1458 || l_count);
1459 END IF;
1460
1461
1462 IF (l_count > 1) THEN --{
1463
1464 RAISE invalid_action;
1465
1466 ELSE --}{
1467
1468 SELECT max(sequence_num)
1469 INTO l_sequence_num
1470 FROM PO_ACTION_HISTORY
1471 WHERE object_type_code = 'WC'
1472 AND object_id = l_shipment_header_id;
1473
1474 IF (g_asn_debug = 'Y') THEN
1475 debug_log(FND_LOG.LEVEL_STATEMENT,
1476 l_api_name,'l_sequence_num '
1477 || l_sequence_num);
1478 END IF;
1479
1480 OPEN get_action_history;
1481
1482 FETCH get_action_history INTO Recinfo;
1483
1484 IF (get_action_history%NOTFOUND) then
1485 IF (g_asn_debug = 'Y') THEN
1486 debug_log(FND_LOG.LEVEL_STATEMENT,
1487 l_api_name,
1488 'no_data_round ' );
1489 END IF;
1490
1491 RAISE NO_DATA_FOUND;
1492 END IF;
1493
1494 CLOSE get_action_history;
1495
1496 /*
1497 ** if it is the first call and it gets here
1498 ** it means there is an implicit forward. We
1499 ** want to update the first NULL row in POAH
1500 ** with FORWARD action
1501 */
1502
1503
1504 If l_count = 1 then
1505
1506
1507 IF (g_asn_debug = 'Y') THEN
1508 debug_log(FND_LOG.LEVEL_STATEMENT,
1509 l_api_name,
1510 'update Action history ' );
1511 END IF;
1512
1513 UpdatePOActionHistory(
1514 p_object_id => Recinfo.object_id,
1515 p_object_type_code => Recinfo.object_type_code,
1516 p_employee_id => Recinfo.employee_id,
1517 p_action_code => 'FORWARD',
1518 p_note => NULL,
1519 p_user_id => fnd_global.user_id,
1520 p_login_id => fnd_global.login_id);
1521
1522
1523 End if;
1524
1525
1526 If l_approver_id is null then --{
1527 raise invalid_data;
1528
1529 Else --}{
1530 IF (g_asn_debug = 'Y') THEN
1531 debug_log(FND_LOG.LEVEL_STATEMENT,
1532 l_api_name,
1533 'Insert Action history ' );
1534 END IF;
1535
1536
1537
1538 InsertPOActionHistory(
1539 p_object_id => Recinfo.object_id,
1540 p_object_type_code=>Recinfo.object_type_code,
1541 p_object_sub_type_code => NULL,
1542 p_sequence_num => Recinfo.sequence_num+1 ,
1543 p_action_code =>NULL ,
1544 p_action_date =>NULL ,
1545 p_employee_id => l_approver_id,
1546 p_approval_path_id => NULL ,
1547 p_note => NULL,
1548 p_object_revision_num => NULL,
1549 p_offline_code => NULL,
1550 p_request_id => Recinfo.request_id,
1551 p_program_application_id => Recinfo.program_application_id,
1552 p_program_id =>Recinfo.program_id ,
1553 p_program_date => Recinfo.program_date ,
1554 p_user_id => fnd_global.user_id ,
1555 p_login_id => fnd_global.login_id);
1556
1557 End if;--}
1558
1559
1560 END If; --}
1561
1562
1563
1564 --end loop;--}
1565 IF (g_asn_debug = 'Y') THEN
1566 debug_log(FND_LOG.LEVEL_STATEMENT,
1567 l_api_name,
1568 'Exit Insert Action history ' );
1569 END IF;
1570
1571 exception
1572 when invalid_action then
1573 IF (g_asn_debug = 'Y') THEN
1574 debug_log(FND_LOG.LEVEL_EXCEPTION,
1575 l_api_name,'invalid_action in Insert_Action_History '
1576 || l_progress);
1577 END IF;
1578 raise;
1579 when invalid_data then
1580 IF (g_asn_debug = 'Y') THEN
1581 debug_log(FND_LOG.LEVEL_EXCEPTION,
1582 l_api_name,'invalid_data in Insert_Action_History '
1583 || l_progress);
1584 END IF;
1585 raise;
1586 when others then
1587 IF (g_asn_debug = 'Y') THEN
1588 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1589 l_api_name,'Exception in Insert_Action_History '
1590 || l_progress);
1591 END IF;
1592 raise;
1593 end Insert_Action_History;
1594
1595 /* This procedure will be called when the approver approves the
1596 * document directly from the notification. This means that
1597 * he wants to approve all the shipment lines at one shot.
1598 * So set the approval status of all the shipment lines to
1599 * APPROVED.
1600 */
1601 procedure Approve_shipment_lines(p_itemtype in varchar2,
1602 p_itemkey in varchar2,
1603 p_actid in number,
1604 p_funcmode in varchar2,
1605 x_resultout out NOCOPY varchar2) is
1606 l_progress varchar2(200);
1607 l_shipment_header_id number;
1608 l_api_name varchar2(50) := p_itemkey || ' Approve_shipment_lines';
1609 l_note po_action_history.note%type;
1610 l_result varchar2(3);
1611
1612 l_notification_id number;
1613 CURSOR c_group_id (p_itemtype VARCHAR2,
1614 p_itemkey VARCHAR2,
1615 p_activity_name VARCHAR2) IS
1616 SELECT notification_id
1617 FROM wf_item_activity_statuses_v
1618 WHERE item_type = p_itemtype
1619 AND item_key = p_itemkey
1620 AND activity_name = p_activity_name
1621 ORDER BY activity_end_date DESC;
1622
1623 BEGIN
1624
1625 l_progress := 'POS_WCAPPROVE_PVT.Approve_shipment_lines: 01.';
1626 IF (g_asn_debug = 'Y') THEN
1627 debug_log(FND_LOG.LEVEL_STATEMENT,
1628 l_api_name,'Enter in Approve_shipment_lines '
1629 || l_progress);
1630 END IF;
1631
1632 if (p_funcmode <> wf_engine.eng_run) then
1633 x_resultout := wf_engine.eng_null;
1634 return;
1635 end if;
1636
1637 l_shipment_header_id := wf_engine.GetItemAttrNumber
1638 (itemtype => p_itemtype,
1639 itemkey => p_itemkey,
1640 aname => 'WORK_CONFIRMATION_ID');
1641
1642 OPEN c_group_id(p_itemtype, p_itemkey, 'WC_APPROVE');
1643
1644 FETCH c_group_id INTO l_notification_id;
1645 CLOSE c_group_id;
1646
1647 IF (g_asn_debug = 'Y') THEN
1648 debug_log(FND_LOG.LEVEL_STATEMENT,
1649 l_api_name,
1650 ' l_notification_id ' || l_notification_id);
1651 END IF;
1652
1653
1654 If (l_notification_id is not null) then
1655 SELECT attribute_value
1656 into l_note
1657 FROM wf_notification_attr_resp_v
1658 WHERE group_id = l_notification_id
1659 AND attribute_name = 'NOTE';
1660 end if;
1661
1662 IF (g_asn_debug = 'Y') THEN
1663 debug_log(FND_LOG.LEVEL_STATEMENT,
1664 l_api_name,
1665 ' l_note ' || l_note);
1666 END IF;
1667
1668 update_approval_status(p_shipment_header_id => l_shipment_header_id,
1669 p_note => l_note,
1670 p_approval_status => 'APPROVED',
1671 p_level => 'LINES',
1672 x_resultout => l_result);
1673
1674
1675
1676 IF (g_asn_debug = 'Y') THEN
1677 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_shipment_header_id ' || l_shipment_header_id);
1678 END IF;
1679 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1680 exception
1681 when others then
1682 IF (g_asn_debug = 'Y') THEN
1683 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1684 l_api_name,'Exception in Approve_shipment_lines '
1685 || l_progress);
1686 END IF;
1687 raise;
1688 end Approve_shipment_lines;
1689
1690
1691 /* This procedure will be called when the approver rejects the
1692 * document directly from the notification. This means that
1693 * he wants to approve all the shipment lines at one shot.
1694 * So set the approval status of all the shipment lines to
1695 * REJECTED.
1696 */
1697 procedure Reject_shipment_lines(p_itemtype in varchar2,
1698 p_itemkey in varchar2,
1699 p_actid in number,
1700 p_funcmode in varchar2,
1701 x_resultout out NOCOPY varchar2) is
1702 l_progress varchar2(200);
1703 l_shipment_header_id number;
1704 l_api_name varchar2(50) := p_itemkey || ' Reject_shipment_lines';
1705 l_note po_action_history.note%type;
1706 l_result varchar2(3);
1707
1708 l_notification_id number;
1709 CURSOR c_group_id (p_itemtype VARCHAR2,
1710 p_itemkey VARCHAR2,
1711 p_activity_name VARCHAR2) IS
1712 SELECT notification_id
1713 FROM wf_item_activity_statuses_v
1714 WHERE item_type = p_itemtype
1715 AND item_key = p_itemkey
1716 AND activity_name = p_activity_name
1717 ORDER BY activity_end_date DESC;
1718
1719 BEGIN
1720
1721 l_progress := 'POS_WCAPPROVE_PVT Reject_shipment_lines: 01.';
1722 IF (g_asn_debug = 'Y') THEN
1723 debug_log(FND_LOG.LEVEL_STATEMENT,
1724 l_api_name,'Enter in Reject_shipment_lines '
1725 || l_progress);
1726 END IF;
1727
1728 if (p_funcmode <> wf_engine.eng_run) then
1729 x_resultout := wf_engine.eng_null;
1730 return;
1731 end if;
1732
1733 l_shipment_header_id := wf_engine.GetItemAttrNumber
1734 (itemtype => p_itemtype,
1735 itemkey => p_itemkey,
1736 aname => 'WORK_CONFIRMATION_ID');
1737
1738 OPEN c_group_id(p_itemtype, p_itemkey, 'WC_APPROVE');
1739
1740 FETCH c_group_id INTO l_notification_id;
1741 CLOSE c_group_id;
1742
1743 IF (g_asn_debug = 'Y') THEN
1744 debug_log(FND_LOG.LEVEL_STATEMENT,
1745 l_api_name,
1746 ' l_notification_id ' || l_notification_id);
1747 END IF;
1748
1749
1750 If l_notification_id is not null then
1751 SELECT attribute_value
1752 into l_note
1753 FROM wf_notification_attr_resp_v
1754 WHERE group_id = l_notification_id
1755 AND attribute_name = 'NOTE';
1756 end if;
1757
1758 IF (g_asn_debug = 'Y') THEN
1759 debug_log(FND_LOG.LEVEL_STATEMENT,
1760 l_api_name,
1761 ' l_note ' || l_note);
1762 END IF;
1763
1764
1765
1766 update_approval_status(p_shipment_header_id => l_shipment_header_id,
1767 p_note => l_note,
1768 p_approval_status => 'REJECTED',
1769 p_level => 'LINES',
1770 x_resultout => l_result);
1771
1772
1773
1774 IF (g_asn_debug = 'Y') THEN
1775 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_shipment_header_id ' || l_shipment_header_id);
1776 END IF;
1777 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1778 exception
1779 when others then
1780 IF (g_asn_debug = 'Y') THEN
1781 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1782 l_api_name,'Exception in Reject_shipment_lines '
1783 || l_progress);
1784 END IF;
1785 raise;
1786 end Reject_shipment_lines;
1787
1788
1789 /* Get the status of all the lines of the Work Confirmation.
1790 * Even if one is rejected.
1791 */
1792 procedure Approve_OR_Reject(p_itemtype in varchar2,
1793 p_itemkey in varchar2,
1794 p_actid in number,
1795 p_funcmode in varchar2,
1796 x_resultout out NOCOPY varchar2) is
1797
1798 l_progress varchar2(200);
1799 l_shipment_header_id number;
1800 l_reject_lines number;
1801 l_api_name varchar2(50) := p_itemkey || ' approve_or_reject';
1802
1803 BEGIN
1804
1805 l_progress := 'POS_WCAPPROVE_PVT.Approve_OR_Reject: 01.';
1806 IF (g_asn_debug = 'Y') THEN
1807 debug_log(FND_LOG.LEVEL_STATEMENT,
1808 l_api_name,'Enter in Approve_OR_Reject '
1809 || l_progress);
1810 END IF;
1811
1812 if (p_funcmode <> wf_engine.eng_run) then
1813 x_resultout := wf_engine.eng_null;
1814 return;
1815 end if;
1816
1817 l_shipment_header_id := wf_engine.GetItemAttrNumber
1818 (itemtype => p_itemtype,
1819 itemkey => p_itemkey,
1820 aname => 'WORK_CONFIRMATION_ID');
1821
1822 IF (g_asn_debug = 'Y') THEN
1823 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,'l_shipment_header_id ' || l_shipment_header_id);
1824 END IF;
1825
1826
1827 select count(*)
1828 into l_reject_lines
1829 from rcv_shipment_lines
1830 where shipment_header_id = l_shipment_header_id
1831 and approval_status = 'REJECTED';
1832
1833 IF (g_asn_debug = 'Y') THEN
1834 debug_log(FND_LOG.LEVEL_STATEMENT,
1835 l_api_name,'l_reject_lines ' || l_reject_lines);
1836 END IF;
1837
1838 If (l_reject_lines > 0) then
1839 x_resultout := wf_engine.eng_completed || ':' ||
1840 'REJECT';
1841 else
1842 x_resultout := wf_engine.eng_completed || ':' ||
1843 'APPROVE';
1844
1845 end if;
1846
1847
1848 exception
1849 when others then
1850 IF (g_asn_debug = 'Y') THEN
1851 debug_log(FND_LOG.LEVEL_UNEXPECTED,
1852 l_api_name,'Exception in Approve_OR_Reject '
1853 || l_progress);
1854 END IF;
1855 raise;
1856 end Approve_OR_Reject;
1857
1858 procedure Update_Approval_List_Response
1859 (p_itemtype in varchar2,
1860 p_itemkey in varchar2,
1861 p_actid in number,
1862 p_funcmode in varchar2,
1863 x_resultout out NOCOPY varchar2) IS
1864
1865 CURSOR c_group_id (p_itemtype VARCHAR2,
1866 p_itemkey VARCHAR2,
1867 p_activity_name VARCHAR2) IS
1868 SELECT notification_id
1869 FROM wf_item_activity_statuses_v
1870 WHERE item_type = p_itemtype
1871 AND item_key = p_itemkey
1872 AND activity_name = p_activity_name
1873 ORDER BY activity_end_date DESC;
1874
1875 CURSOR c_canceled_notif (p_notif_id number) IS
1876 SELECT '1'
1877 FROM WF_NOTIFICATIONS
1878 WHERE notification_id = p_notif_id
1879 AND status = 'CANCELED';
1880
1881 CURSOR c_response(p_group_id number) IS
1882 SELECT recipient_role, attribute_value
1883 FROM wf_notification_attr_resp_v
1884 WHERE group_id = p_group_id
1885 AND attribute_name = 'RESULT';
1886
1887 CURSOR c_response_note(p_group_id number) IS
1888 SELECT attribute_value
1889 FROM wf_notification_attr_resp_v
1890 WHERE group_id = p_group_id
1891 AND attribute_name = 'NOTE';
1892
1893 CURSOR c_responderid(p_responder VARCHAR2) IS
1894 SELECT nvl((wfu.orig_system_id), -9996)
1895 FROM wf_users wfu
1896 WHERE wfu.name = p_responder
1897 AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
1898
1899 l_api_name varchar2(50) := p_itemkey || ' update_approval_list_response';
1900 l_responder wf_notifications.responder%TYPE;
1901 l_recipient_role wf_notifications.recipient_role%TYPE;
1902
1903 l_group_id NUMBER;
1904 l_role wf_notifications.recipient_role%TYPE;
1905 l_response VARCHAR2(2000);
1906
1907 l_approver_id NUMBER := NULL;
1908 l_orig_system wf_users.orig_system%TYPE;
1909 l_responder_user_name wf_users.name%TYPE;
1910 l_responder_disp_name wf_users.display_name%TYPE;
1911
1912 is_notif_canceled VARCHAR2(2);
1913 l_doc_string varchar2(200);
1914 l_preparer_user_name wf_users.name%TYPE;
1915 l_response_end_date date;
1916 l_responder_id number;
1917 l_progress varchar2(300);
1918 l_insertion_type VARCHAR2(30);
1919 l_authority_type VARCHAR2(30);
1920 l_forward_to_id NUMBER := NULL;
1921 l_transaction_type varchar2(100);
1922 l_shipment_header_id number;
1923 l_application_id number := '201'; -- use PO application id.
1924 l_current_approver ame_util.approverRecord2;
1925 l_approver_type varchar2(10);
1926 l_default_approver varchar2(30);
1927 l_next_approver_disp_name varchar2(100);
1928 l_reject_lines number;
1929
1930
1931 begin
1932 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response: 01.';
1933 IF (g_asn_debug = 'Y') THEN
1934 debug_log(FND_LOG.LEVEL_STATEMENT,
1935 l_api_name,
1936 'Enter in Update_Approval_List_Response '
1937 || l_progress);
1938 END IF;
1939
1940 if (p_funcmode <> wf_engine.eng_run) then
1941 x_resultout := wf_engine.eng_null;
1942 return;
1943 end if;
1944
1945
1946 wf_engine.SetItemAttrNumber ( itemtype => p_itemType,
1947 itemkey => p_itemkey,
1948 aname => 'RESPONDER_USER_ID',
1949 avalue => fnd_global.USER_ID);
1950
1951 wf_engine.SetItemAttrNumber ( itemtype => p_itemType,
1952 itemkey => p_itemkey,
1953 aname => 'RESPONDER_RESP_ID',
1954 avalue => fnd_global.RESP_ID);
1955
1956 wf_engine.SetItemAttrNumber ( itemtype => p_itemType,
1957 itemkey => p_itemkey,
1958 aname => 'RESPONDER_APPL_ID',
1959 avalue => fnd_global.RESP_APPL_ID);
1960
1961 OPEN c_group_id(p_itemtype, p_itemkey, 'WC_APPROVE');
1962
1963 FETCH c_group_id INTO l_group_id;
1964 CLOSE c_group_id;
1965
1966 IF (g_asn_debug = 'Y') THEN
1967 debug_log(FND_LOG.LEVEL_STATEMENT,
1968 l_api_name,
1969 ' l_group_id ' || l_group_id);
1970 END IF;
1971
1972
1973 IF l_group_id is NOT NULL THEN --{
1974 OPEN c_response(l_group_id);
1975 FETCH c_response INTO l_role, l_response;
1976 CLOSE c_response;
1977
1978 IF (g_asn_debug = 'Y') THEN
1979 debug_log(FND_LOG.LEVEL_STATEMENT,
1980 l_api_name,
1981 ' l_role ' || l_role);
1982 debug_log(FND_LOG.LEVEL_STATEMENT,
1983 l_api_name,
1984 ' l_response '
1985 || substr(l_response,1,50));
1986 END IF;
1987
1988 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
1989 || ': 02.';
1990
1991
1992 SELECT wfn.responder, wfn.recipient_role, wfn.end_date
1993 INTO l_responder, l_recipient_role, l_response_end_date
1994 FROM wf_notifications wfn
1995 WHERE wfn.notification_id = l_group_id;
1996
1997 IF (g_asn_debug = 'Y') THEN
1998 debug_log(FND_LOG.LEVEL_STATEMENT,
1999 l_api_name,
2000 ' l_responder ' || l_responder);
2001 debug_log(FND_LOG.LEVEL_STATEMENT,
2002 l_api_name,
2003 ' l_recipient_role '
2004 || l_recipient_role);
2005 debug_log(FND_LOG.LEVEL_STATEMENT,
2006 l_api_name,
2007 ' l_response_end_date '
2008 || to_char(l_response_end_date,'DD-MON-YYYY'));
2009 end if;
2010
2011 OPEN c_responderid(l_responder);
2012 FETCH c_responderid INTO l_responder_id;
2013
2014 IF (g_asn_debug = 'Y') THEN
2015 debug_log(FND_LOG.LEVEL_STATEMENT,
2016 l_api_name,
2017 ' l_responder_id ' || l_responder_id);
2018 end if;
2019
2020 IF c_responderid%NOTFOUND THEN --{
2021
2022 CLOSE c_responderid;
2023 OPEN c_responderid(l_recipient_role);
2024 FETCH c_responderid INTO l_responder_id;
2025 CLOSE c_responderid;
2026
2027 IF (g_asn_debug = 'Y') THEN
2028 debug_log(FND_LOG.LEVEL_STATEMENT,
2029 l_api_name,
2030 ' l_responder_id1 ' || l_responder_id);
2031 end if;
2032 End if;--}
2033
2034
2035 IF (c_responderid%ISOPEN) THEN
2036 CLOSE c_responderid;
2037 END IF;
2038
2039 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
2040 || ':02.';
2041 wf_engine.SetItemAttrNumber(itemtype => p_itemType,
2042 itemkey => p_itemkey,
2043 aname => 'RESPONDER_ID',
2044 avalue => l_responder_id);
2045
2046 l_orig_system:= 'PER';
2047
2048 WF_DIRECTORY.GetUserName(l_orig_system,
2049 l_responder_Id,
2050 l_responder_user_name,
2051 l_responder_disp_name);
2052
2053 IF (g_asn_debug = 'Y') THEN
2054 debug_log(FND_LOG.LEVEL_STATEMENT,
2055 l_api_name,
2056 ' l_responder_user_name '
2057 || l_responder_user_name);
2058 debug_log(FND_LOG.LEVEL_STATEMENT,
2059 l_api_name,
2060 ' l_responder_disp_name '
2061 || l_responder_disp_name);
2062 end if;
2063
2064 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
2065 || ': 03.';
2066 wf_engine.SetItemAttrText( itemtype => p_itemType,
2067 itemkey => p_itemkey,
2068 aname => 'RESPONDER_USER_NAME' ,
2069 avalue => l_responder_user_name);
2070
2071 wf_engine.SetItemAttrText( itemtype => p_itemType,
2072 itemkey => p_itemkey,
2073 aname => 'RESPONDER_DISPLAY_NAME' ,
2074 avalue => l_responder_disp_name);
2075
2076 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
2077 || ': 04.';
2078
2079 /* We cannot have response as forward. Need to remove it later
2080 IF (INSTR(l_response, 'FORWARD') > 0) THEN
2081 l_forward_to_id :=
2082 wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
2083 itemkey => p_itemkey,
2084 aname => 'FORWARD_TO_ID');
2085 END IF;
2086 IF (g_asn_debug = 'Y') THEN
2087 debug_log(FND_LOG.LEVEL_STATEMENT,
2088 l_api_name,
2089 ' l_forward_to_id '
2090 || l_forward_to_id);
2091 END IF;
2092 */
2093
2094 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_List_Response'
2095 || ': 05.';
2096
2097 END IF; -- }c_group_id
2098
2099
2100 l_shipment_header_id := wf_engine.GetItemAttrNumber
2101 (itemtype => p_itemtype,
2102 itemkey => p_itemkey,
2103 aname => 'WORK_CONFIRMATION_ID');
2104
2105 IF (g_asn_debug = 'Y') THEN
2106 debug_log(FND_LOG.LEVEL_STATEMENT,
2107 l_api_name,
2108 ' l_shipment_header_id '
2109 || l_shipment_header_id);
2110 END IF;
2111
2112 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>p_itemtype,
2113 itemkey=>p_itemkey,
2114 aname=>'APPROVER_EMPID');
2115
2116 IF (g_asn_debug = 'Y') THEN
2117 debug_log(FND_LOG.LEVEL_STATEMENT,
2118 l_api_name,
2119 ' l_approver_id '
2120 || l_approver_id);
2121 END IF;
2122
2123 l_insertion_type := wf_engine.GetItemAttrText(itemtype => p_itemtype,
2124 itemkey => p_itemkey,
2125 aname => 'AME_INSERTION_TYPE');
2126
2127 IF (g_asn_debug = 'Y') THEN
2128 debug_log(FND_LOG.LEVEL_STATEMENT,
2129 l_api_name,
2130 ' l_insertion_type '
2131 || l_insertion_type);
2132 END IF;
2133
2134 l_authority_type := wf_engine.GetItemAttrText(itemtype => p_itemtype,
2135 itemkey => p_itemkey,
2136 aname => 'AME_AUTHORITY_TYPE');
2137
2138 IF (g_asn_debug = 'Y') THEN
2139 debug_log(FND_LOG.LEVEL_STATEMENT,
2140 l_api_name,
2141 ' l_authority_type '
2142 || l_authority_type);
2143 END IF;
2144
2145 l_transaction_type := wf_engine.GetItemAttrText (itemtype => p_itemtype,
2146 itemkey => p_itemkey,
2147 aname => 'AME_TRANSACTION_TYPE');
2148
2149 IF (g_asn_debug = 'Y') THEN
2150 debug_log(FND_LOG.LEVEL_STATEMENT,
2151 l_api_name,
2152 ' l_transaction_type '
2153 || l_transaction_type);
2154 END IF;
2155
2156 l_approver_type := po_wf_util_pkg.GetItemAttrText
2157 (itemtype => p_itemtype,
2158 itemkey => p_itemkey,
2159 aname => 'AME_APPROVER_TYPE');
2160 IF (g_asn_debug = 'Y') THEN
2161 debug_log(FND_LOG.LEVEL_STATEMENT,
2162 l_api_name,
2163 ' l_approver_type '
2164 || l_approver_type);
2165 END IF;
2166
2167 l_default_approver :=
2168 wf_engine.GetItemAttrText (itemtype => p_itemtype,
2169 itemkey => p_itemkey,
2170 aname => 'DEFAULT_APPROVER');
2171
2172 IF (g_asn_debug = 'Y') THEN
2173 debug_log(FND_LOG.LEVEL_STATEMENT,
2174 l_api_name,'l_default_approver '
2175 || l_default_approver);
2176 END IF;
2177
2178 If l_default_approver = 'BUYER' then --{
2179 IF (g_asn_debug = 'Y') THEN
2180 debug_log(FND_LOG.LEVEL_STATEMENT,
2181 l_api_name,'Approved by buyer. Dont call ame api ');
2182 END IF;
2183 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2184 return;
2185
2186 end if; --}
2187
2188 if (l_approver_type = 'POS') then
2189 l_current_approver.orig_system := 'POS';
2190 elsif (l_approver_type = 'FND') then
2191 l_current_approver.orig_system := 'FND';
2192 else
2193 l_current_approver.orig_system := 'PER';
2194 end if;
2195
2196 IF (g_asn_debug = 'Y') THEN
2197 debug_log(FND_LOG.LEVEL_STATEMENT,
2198 l_api_name,
2199 ' orig_system '
2200 || l_current_approver.orig_system);
2201 END IF;
2202
2203 l_current_approver.orig_system_id := l_approver_id;
2204
2205 if( l_response = 'APPROVE') then
2206 l_current_approver.approval_status := ame_util.approvedStatus;
2207 elsif( l_response = 'REJECT') then
2208 l_current_approver.approval_status := ame_util.rejectStatus;
2209 elsif( l_response = 'TIMEOUT') then
2210 l_current_approver.approval_status := ame_util.noResponseStatus;
2211 else /* Can be approved/rejected from iSP UI */
2212 select count(*)
2213 into l_reject_lines
2214 from rcv_shipment_lines
2215 where shipment_header_id = l_shipment_header_id
2216 and approval_status = 'REJECTED';
2217
2218 If (l_reject_lines > 0) then
2219 l_current_approver.approval_status := ame_util.rejectStatus;
2220 else
2221 l_current_approver.approval_status := ame_util.approvedStatus;
2222 end if;
2223
2224
2225 end if;
2226
2227
2228 /*
2229 Bug 7120431 , To get the user name from wf_users we need to pass orig_system as 'PER' irresepective of
2230 whether we user Employee-Supervisor hierarchy or Positional hierarchy .
2231 So passing 'PER' while in the below call.
2232 */
2233
2234 WF_DIRECTORY.GetUserName('PER',
2235 l_current_approver.orig_system_id,
2236 l_current_approver.name,
2237 l_next_approver_disp_name);
2238
2239
2240
2241 IF (g_asn_debug = 'Y') THEN
2242 debug_log(FND_LOG.LEVEL_STATEMENT,
2243 l_api_name,
2244 ' name '
2245 || l_current_approver.name);
2246 END IF;
2247
2248 IF l_current_approver.name IS NULL THEN
2249 raise_application_error(-20001,
2250 'Record Not Found in WF_ROLES for the orig_system_id :' ||
2251 l_current_approver.orig_system_id || ' -- orig_system :' || l_current_approver.orig_system );
2252 END IF;
2253
2254
2255 ame_api2.updateApprovalStatus(applicationIdIn=>l_application_Id, transactionIdIn=>l_shipment_header_id,
2256 approverIn=>l_current_approver,
2257 transactionTypeIn=>l_transaction_type);
2258
2259
2260 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2261 RETURN;
2262 Exception
2263 when others then
2264 IF (g_asn_debug = 'Y') THEN
2265 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2266 l_api_name,'Exception in update_approval_list_response '
2267 || l_progress);
2268 END IF;
2269 raise;
2270 end Update_Approval_List_Response;
2271
2272 procedure Update_Action_History_Approve
2273 (p_itemtype in varchar2,
2274 p_itemkey in varchar2,
2275 p_actid in number,
2276 p_funcmode in varchar2,
2277 x_resultout out NOCOPY varchar2) IS
2278 l_progress varchar2(300);
2279 l_api_name varchar2(50) := p_itemkey || ' update_action_history_approve';
2280 begin
2281 l_progress := 'POS_WCAPPROVE_PVT.Update_Approval_History_Approve: 01.';
2282 IF (g_asn_debug = 'Y') THEN
2283 debug_log(FND_LOG.LEVEL_STATEMENT,
2284 l_api_name,
2285 'Enter in Update_Action_History_Approve '
2286 || l_progress);
2287 END IF;
2288
2289 if (p_funcmode <> wf_engine.eng_run) then
2290 x_resultout := wf_engine.eng_null;
2291 return;
2292 end if;
2293
2294 Update_Action_History(p_itemtype => p_itemtype,
2295 p_itemkey => p_itemkey,
2296 p_action_code => 'APPROVE');
2297
2298 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2299
2300 IF (g_asn_debug = 'Y') THEN
2301 debug_log(FND_LOG.LEVEL_STATEMENT,
2302 l_api_name,
2303 'Leave in Update_Action_History_Approve '
2304 || l_progress);
2305 END IF;
2306
2307 return;
2308
2309
2310 Exception
2311 when others then
2312 IF (g_asn_debug = 'Y') THEN
2313 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2314 l_api_name,'Exception in update_action_history_approve '
2315 || l_progress);
2316 END IF;
2317 raise;
2318 end Update_Action_History_Approve;
2319
2320 procedure Update_Action_History_Reject
2321 (p_itemtype in varchar2,
2322 p_itemkey in varchar2,
2323 p_actid in number,
2324 p_funcmode in varchar2,
2325 x_resultout out NOCOPY varchar2) IS
2326 l_progress varchar2(300);
2327 l_api_name varchar2(50) := p_itemkey || ' update_action_history_Reject';
2328 begin
2329 l_progress := 'POS_WCAPPROVE_PVT.Update_action_history_reject: 01.';
2330 IF (g_asn_debug = 'Y') THEN
2331 debug_log(FND_LOG.LEVEL_STATEMENT,
2332 l_api_name,
2333 'Enter in Update_Action_History_Reject '
2334 || l_progress);
2335 END IF;
2336
2337 if (p_funcmode <> wf_engine.eng_run) then
2338 x_resultout := wf_engine.eng_null;
2339 return;
2340 end if;
2341
2342 Update_Action_History(p_itemtype => p_itemtype,
2343 p_itemkey => p_itemkey,
2344 p_action_code => 'REJECT');
2345
2346 x_resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2347
2348 IF (g_asn_debug = 'Y') THEN
2349 debug_log(FND_LOG.LEVEL_STATEMENT,
2350 l_api_name,
2351 'Leave in Update_Action_History_Reject '
2352 || l_progress);
2353 END IF;
2354
2355 return;
2356
2357
2358 Exception
2359 when others then
2360 IF (g_asn_debug = 'Y') THEN
2361 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2362 l_api_name,'Exception in update_action_history_Reject '
2363 || l_progress);
2364 END IF;
2365 raise;
2366 end Update_Action_History_REJECT;
2367
2368
2369
2370 procedure Update_Action_History
2371 (p_itemtype in varchar2,
2372 p_itemkey in varchar2,
2373 p_action_code in varchar2) IS
2374
2375 CURSOR c_responderid(l_responder VARCHAR2) IS
2376 SELECT nvl((wfu.orig_system_id), -9996)
2377 FROM wf_users wfu
2378 WHERE wfu.name = l_responder
2379 AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
2380
2381
2382 /*
2383 cursor get_shipment_lines(l_shipment_header_id number) is
2384 select rsl.shipment_line_id ,
2385 nvl(rsl.comments,rsh.comments),
2386 rsl.approval_status
2387 from rcv_shipment_lines rsl,
2388 rcv_shipment_headers rsh
2389 where rsh.shipment_header_id = l_shipment_header_id
2390 and rsh.shipment_header_id= rsl.shipment_header_id;
2391 */
2392
2393 l_api_name varchar2(50) := p_itemkey || ' update_action_history';
2394 l_shipment_header_id number;
2395 l_shipment_line_id number;
2396 l_progress varchar2(300);
2397 l_notification_id number;
2398 l_comments varchar2(240) := null;
2399 l_original_recipient_id number;
2400 l_original_recipient wf_notifications.original_recipient%TYPE;
2401 l_recipient_role wf_notifications.recipient_role%TYPE;
2402 l_more_info_role wf_notifications.more_info_role%TYPE;
2403 l_responder_id number;
2404 l_more_origsys wf_roles.orig_system%TYPE;
2405 l_more_origsysid wf_roles.orig_system_id%TYPE := null;
2406 l_responder wf_notifications.responder%TYPE;
2407
2408
2409 begin
2410 l_progress := 'POS_WCAPPROVE_PVT.Update_Action_history: 01.';
2411 IF (g_asn_debug = 'Y') THEN
2412 debug_log(FND_LOG.LEVEL_STATEMENT,
2413 l_api_name,
2414 'Enter in Update_Action_History '
2415 || l_progress);
2416 END IF;
2417
2418
2419 l_shipment_header_id := wf_engine.GetItemAttrNumber
2420 (itemtype => p_itemtype,
2421 itemkey => p_itemkey,
2422 aname => 'WORK_CONFIRMATION_ID');
2423
2424 IF (g_asn_debug = 'Y') THEN
2425 debug_log(FND_LOG.LEVEL_STATEMENT,
2426 l_api_name,
2427 ' l_shipment_header_id '
2428 || l_shipment_header_id);
2429 END IF;
2430
2431
2432 l_progress := 'POS_WCAPPROVE_PVT.Update_Action_history: 02.';
2433 SELECT nvl(max(wf.notification_id), -9995)
2434 into l_notification_id
2435 FROM wf_notifications wf,
2436 wf_item_activity_statuses wias
2437 WHERE wias.item_type = p_itemtype
2438 and wias.item_key = p_itemkey
2439 and wias.notification_id = wf.group_id;
2440
2441 IF (g_asn_debug = 'Y') THEN
2442 debug_log(FND_LOG.LEVEL_STATEMENT,
2443 l_api_name,
2444 ' l_notification_id '
2445 || l_notification_id);
2446 END IF;
2447
2448
2449 l_progress := 'POS_WCAPPROVE_PVT.Update_Action_history: 03.';
2450 SELECT wfn.responder, wfn.recipient_role,
2451 wfn.original_recipient, wfn.more_info_role
2452 INTO l_responder, l_recipient_role,
2453 l_original_recipient, l_more_info_role
2454 FROM wf_notifications wfn
2455 WHERE wfn.notification_id = l_notification_id;
2456
2457 IF (g_asn_debug = 'Y') THEN
2458 debug_log(FND_LOG.LEVEL_STATEMENT,
2459 l_api_name,
2460 ' l_responder ' || l_responder);
2461 debug_log(FND_LOG.LEVEL_STATEMENT,
2462 l_api_name,
2463 ' l_recipient_role ' || l_recipient_role);
2464 debug_log(FND_LOG.LEVEL_STATEMENT,
2465 l_api_name,
2466 ' l_original_recipient '||l_original_recipient);
2467 debug_log(FND_LOG.LEVEL_STATEMENT,
2468 l_api_name,
2469 ' l_more_info_role ' || l_more_info_role);
2470 END IF;
2471
2472
2473 OPEN c_responderid(l_responder);
2474 FETCH c_responderid INTO l_responder_id;
2475
2476
2477 IF c_responderid%NOTFOUND THEN
2478
2479 CLOSE c_responderid;
2480 OPEN c_responderid(l_recipient_role);
2481 FETCH c_responderid INTO l_responder_id;
2482 CLOSE c_responderid;
2483
2484 END IF;
2485
2486 IF (c_responderid%ISOPEN) THEN
2487 CLOSE c_responderid;
2488 END IF;
2489
2490
2491 OPEN c_responderid(l_original_recipient);
2492 FETCH c_responderid INTO l_original_recipient_id;
2493
2494 IF c_responderid%NOTFOUND THEN--{
2495
2496 CLOSE c_responderid;
2497
2498 SELECT wfu.orig_system_id
2499 INTO l_original_recipient_id
2500 FROM wf_roles wfu
2501 WHERE wfu.name = l_original_recipient
2502 AND wfu.orig_system NOT IN ('POS', 'ENG_LIST', 'CUST_CONT');
2503
2504 END IF; --}
2505
2506 IF (c_responderid%ISOPEN) THEN
2507 CLOSE c_responderid;
2508 END IF;
2509
2510 if (l_more_info_role is not null) then
2511 Wf_Directory.GetRoleOrigSysInfo
2512 (l_more_info_role,
2513 l_more_origsys,
2514 l_more_origsysid);
2515 end if;
2516
2517 --loop --{
2518 l_progress := 'POS_WCAPPROVE_PVT.update_approval_history: 04.';
2519
2520
2521 IF (g_asn_debug = 'Y') THEN
2522 debug_log(FND_LOG.LEVEL_STATEMENT,
2523 l_api_name,'l_comments ' ||
2524 l_comments);
2525 END IF;
2526
2527
2528 UpdateActionHistory(l_more_origsysid,
2529 l_original_recipient_id,
2530 l_responder_id,
2531 FALSE,
2532 p_action_code,
2533 l_comments,
2534 l_shipment_header_id);
2535
2536 IF (g_asn_debug = 'Y') THEN
2537 debug_log(FND_LOG.LEVEL_STATEMENT,
2538 l_api_name,'After updateactionhistory' ||
2539 l_shipment_header_id);
2540 end if;
2541 --end loop; --}
2542
2543 IF (g_asn_debug = 'Y') THEN
2544 debug_log(FND_LOG.LEVEL_STATEMENT,
2545 l_api_name,'Leave update_action_history' );
2546 end if;
2547
2548 return;
2549
2550 EXCEPTION
2551 when others then
2552 IF (g_asn_debug = 'Y') THEN
2553 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2554 l_api_name,'Exception in Update_Action_History '
2555 || l_progress);
2556 END IF;
2557 raise;
2558
2559 END Update_Action_History;
2560
2561 PROCEDURE UpdateActionHistory(p_more_info_id IN NUMBER,
2562 p_original_recipient_id IN NUMBER,
2563 p_responder_id IN NUMBER,
2564 p_last_approver IN BOOLEAN,
2565 p_action_code IN VARCHAR2,
2566 p_comments IN VARCHAR2,
2567 p_shipment_header_id IN NUMBER)
2568 IS
2569
2570 -- pragma AUTONOMOUS_TRANSACTION;
2571 l_api_name varchar2(50) := 'UpdateActionHistory';
2572 l_progress varchar2(300);
2573 l_sequence_num number;
2574 l_note VARCHAR2(4000);
2575
2576 CURSOR get_action_history(l_sequence_num number) IS
2577 SELECT ph.action_code action_code ,
2578 ph.object_type_code object_type_code ,
2579 ph.sequence_num sequence_num,
2580 ph.approval_path_id approval_path_id,
2581 ph.request_id request_id ,
2582 ph.program_application_id program_application_id,
2583 ph.program_date program_date ,
2584 ph.program_id program_id ,
2585 ph.last_update_date last_update_date,
2586 ph.object_id object_id,
2587 ph.employee_id employee_id
2588 FROM
2589 rcv_shipment_headers rsh,
2590 po_action_history ph
2591 WHERE rsh.shipment_header_id = ph.object_id
2592 and rsh.shipment_header_id=p_shipment_header_id
2593 and ph.sequence_num = l_sequence_num;
2594
2595 Recinfo get_action_history%ROWTYPE;
2596
2597 BEGIN
2598
2599 l_progress := 'POS_WCAPPROVE_PVT.UpdateActionHistory: 01.';
2600 IF (g_asn_debug = 'Y') THEN
2601 debug_log(FND_LOG.LEVEL_STATEMENT,
2602 l_api_name,'Enter in UpdateActionHistory '
2603 || l_progress);
2604 debug_log(FND_LOG.LEVEL_STATEMENT,
2605 l_api_name,'p_shipment_header_id '
2606 || p_shipment_header_id);
2607 END IF;
2608
2609
2610 SELECT max(sequence_num)
2611 INTO l_sequence_num
2612 FROM PO_ACTION_HISTORY
2613 WHERE object_type_code = 'WC'
2614 AND object_id = p_shipment_header_id;
2615
2616 OPEN get_action_history(l_sequence_num);
2617
2618 FETCH get_action_history INTO Recinfo;
2619
2620 IF (get_action_history%NOTFOUND) then
2621 RAISE NO_DATA_FOUND;
2622 END IF;
2623
2624 CLOSE get_action_history;
2625
2626
2627 if ( (Recinfo.action_code is not null) and
2628 (p_action_code not in ('APPROVE','REJECT'))) then --{
2629
2630 /* Add a blank line if the last line is not blank.
2631 */
2632 IF (g_asn_debug = 'Y') THEN
2633 debug_log(FND_LOG.LEVEL_STATEMENT,
2634 l_api_name,
2635 'Before call to InsertPOActionHistory ');
2636 End if;
2637
2638
2639 InsertPOActionHistory(
2640 p_object_id => Recinfo.object_id,
2641 p_object_type_code=>Recinfo.object_type_code,
2642 p_object_sub_type_code => NULL,
2643 p_sequence_num => Recinfo.sequence_num+1 ,
2644 p_action_code =>NULL ,
2645 p_action_date =>NULL ,
2646 p_employee_id => p_original_recipient_id,
2647 p_approval_path_id => NULL ,
2648 p_note => NULL,
2649 p_object_revision_num => NULL,
2650 p_offline_code => NULL,
2651 p_request_id => Recinfo.request_id,
2652 p_program_application_id => Recinfo.program_application_id,
2653 p_program_id =>Recinfo.program_id ,
2654 p_program_date => Recinfo.program_date ,
2655 p_user_id => fnd_global.user_id ,
2656 p_login_id => fnd_global.login_id);
2657
2658
2659 end if; --}
2660
2661 IF (p_responder_id <> -9996) THEN--{
2662
2663 /** the logic to handle re-assignment is in
2664 ** post notification function so that the update
2665 ** to action history can be viewed at the moment of
2666 ** reassignment. The following code is used to handle
2667 ** request for more info:
2668 ** 1. at the moment an approver requests for more info,
2669 ** action history is updated (performed within post
2670 ** notification)
2671 ** 2. if the approver approve/reject the requisition
2672 ** before the more info request is responded
2673 ** then we need to update the action history
2674 ** to reflect 'no action' from the more info
2675 */
2676 IF (p_more_info_id is not null) THEN --{
2677
2678
2679 /*
2680 ** update the original NULL row for the
2681 ** original approver with
2682 ** action code of 'NO ACTION'
2683 */
2684
2685
2686 UpdatePOActionHistory(
2687 p_object_id => Recinfo.object_id,
2688 p_object_type_code => Recinfo.object_type_code,
2689 p_employee_id => p_more_info_id,
2690 p_action_code => 'NO ACTION',
2691 p_note => NULL,
2692 p_user_id => fnd_global.user_id,
2693 p_login_id => fnd_global.login_id);
2694
2695
2696
2697 /*
2698 ** insert a new NULL row into PO_ACTION_HISTORY for
2699 ** the new approver
2700 */
2701
2702
2703 InsertPOActionHistory(
2704 p_object_id => Recinfo.object_id,
2705 p_object_type_code=>Recinfo.object_type_code,
2706 p_object_sub_type_code => NULL,
2707 p_sequence_num => Recinfo.sequence_num+1 ,
2708 p_action_code =>NULL ,
2709 p_action_date =>NULL ,
2710 p_employee_id => p_responder_id,
2711 p_approval_path_id => NULL ,
2712 p_note => NULL,
2713 p_object_revision_num => NULL,
2714 p_offline_code => NULL,
2715 p_request_id => Recinfo.request_id,
2716 p_program_application_id => Recinfo.program_application_id,
2717 p_program_id =>Recinfo.program_id ,
2718 p_program_date => Recinfo.program_date ,
2719 p_user_id => fnd_global.user_id ,
2720 p_login_id => fnd_global.login_id);
2721
2722
2723 end if; --}
2724
2725
2726 end if; --}
2727
2728 l_progress := 'POS_WCAPPROVE_PVT.UpdateActionHistory: 02.';
2729
2730 IF (not p_last_approver) THEN
2731
2732 /**
2733 ** update pending row of action history with approval action
2734 */
2735 IF (g_asn_debug = 'Y') THEN
2736 debug_log(FND_LOG.LEVEL_STATEMENT,
2737 l_api_name,
2738 'Before call to UpdatePOActionHistory '
2739 ||l_progress);
2740 End if;
2741
2742 UpdatePOActionHistory(
2743 p_object_id => Recinfo.object_id,
2744 p_object_type_code => Recinfo.object_type_code,
2745 p_employee_id => Recinfo.employee_id,
2746 p_action_code => p_action_code,
2747 p_note =>substrb(p_comments,1,4000),
2748 p_user_id => fnd_global.user_id,
2749 p_login_id => fnd_global.login_id);
2750
2751
2752 END IF;
2753
2754
2755 EXCEPTION
2756 when no_data_found then
2757 IF (g_asn_debug = 'Y') THEN
2758 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2759 l_api_name,
2760 'No data found in UpdateActionHistory '
2761 || l_progress);
2762 END IF;
2763 raise;
2764
2765 when others then
2766 IF (g_asn_debug = 'Y') THEN
2767 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2768 l_api_name,'Exception in UpdateActionHistory '
2769 || l_progress);
2770 END IF;
2771 raise;
2772
2773 END UpdateActionHistory;
2774
2775 PROCEDURE get_user_name(p_orig_system IN Varchar2,
2776 p_employee_id IN number,
2777 x_username OUT NOCOPY varchar2,
2778 x_user_display_name OUT NOCOPY varchar2) is
2779
2780 l_progress varchar2(200);
2781 l_api_name varchar2(50) := ' get_user_name';
2782
2783 BEGIN
2784
2785 l_progress := 'POS_WCAPPROVE_PVT.get_user_name: 01.';
2786 IF (g_asn_debug = 'Y') THEN
2787 debug_log(FND_LOG.LEVEL_STATEMENT,
2788 l_api_name,'Enter in get_user_name '
2789 || l_progress);
2790 END IF;
2791
2792 WF_DIRECTORY.GetUserName(p_orig_system,
2793 p_employee_id,
2794 x_username,
2795 x_user_display_name);
2796
2797 IF (g_asn_debug = 'Y') THEN
2798 debug_log(FND_LOG.LEVEL_STATEMENT,
2799 l_api_name,'x_username '
2800 || x_username);
2801 debug_log(FND_LOG.LEVEL_STATEMENT,
2802 l_api_name,'x_user_display_name '
2803 || x_user_display_name);
2804 END IF;
2805 EXCEPTION
2806 when others then
2807 IF (g_asn_debug = 'Y') THEN
2808 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2809 l_api_name,'Exception in get_user_name '
2810 || l_progress);
2811 END IF;
2812 raise;
2813
2814 END get_user_name;
2815
2816
2817 PROCEDURE UpdatePOActionHistory (p_object_id IN NUMBER,
2818 p_object_type_code IN VARCHAR2,
2819 p_employee_id IN NUMBER,
2820 p_action_code IN VARCHAR2,
2821 p_note IN VARCHAR2,
2822 p_user_id IN NUMBER,
2823 p_login_id IN NUMBER)
2824 IS
2825 l_progress VARCHAR2(250) := '';
2826 l_employee_id NUMBER ;
2827 invalid_action exception;
2828 l_api_name varchar2(50) := ' UpdatePOActionHistory';
2829 l_note po_action_history.note%type;
2830
2831 BEGIN
2832 l_progress := 'POS_WCAPPROVE_PVT.UpdatePOActionHistory: 01.';
2833 IF (g_asn_debug = 'Y') THEN
2834 debug_log(FND_LOG.LEVEL_STATEMENT,
2835 l_api_name,'Enter in UpdatePOActionHistory '
2836 || l_progress);
2837 debug_log(FND_LOG.LEVEL_STATEMENT,
2838 l_api_name,'object_id '||p_object_id
2839 || 'object_type_code '|| p_object_type_code
2840 || 'p_employee_id '|| nvl(p_employee_id,-9999)
2841 || 'p_action_code '|| nvl(p_action_code,'NO ACTION')
2842 || 'p_note '|| nvl(p_note,'NO NOTE')
2843 || 'p_user_id '|| p_user_id
2844 || 'p_login_id '|| p_login_id);
2845 END IF;
2846
2847 IF (p_object_id IS NOT NULL AND
2848 p_object_type_code IS NOT NULL) THEN --{
2849
2850 /* Employee id should belong to the id of the corresponding
2851 * user taking the action, not the employee id to
2852 * which the work confirmation was forwarded to.
2853 */
2854 IF (g_asn_debug = 'Y') THEN
2855 debug_log(FND_LOG.LEVEL_STATEMENT,
2856 l_api_name,'ohject_id and '
2857 || 'object_type_code not null');
2858 END IF;
2859
2860 /*
2861 If (p_old_employee_id is NULL) then
2862 IF (g_asn_debug = 'Y') THEN
2863 debug_log(FND_LOG.LEVEL_STATEMENT,
2864 l_api_name,'p_employee_id is null '
2865 || l_progress);
2866 END IF;
2867
2868 SELECT HR.EMPLOYEE_ID
2869 INTO l_employee_id
2870 FROM FND_USER FND, HR_EMPLOYEES_CURRENT_V HR
2871 WHERE FND.USER_ID = NVL(p_user_id, fnd_global.user_id)
2872 AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID;
2873 IF (g_asn_debug = 'Y') THEN
2874 debug_log(FND_LOG.LEVEL_STATEMENT,
2875 l_api_name,'l_employee_id '
2876 || l_employee_id);
2877 END IF;
2878
2879 end if;
2880
2881 l_progress := 'POS_WCAPPROVE_PVT.UpdatePOActionHistory: 02.';
2882 IF (g_asn_debug = 'Y') THEN
2883 debug_log(FND_LOG.LEVEL_STATEMENT,
2884 l_api_name,'Before Update '
2885 || l_progress);
2886 END IF;
2887 */
2888
2889
2890 IF (g_asn_debug = 'Y') THEN
2891 debug_log(FND_LOG.LEVEL_STATEMENT,
2892 l_api_name,'l_note '
2893 || l_note);
2894 end if;
2895
2896
2897 begin
2898 if (p_action_code = 'APPROVE' OR p_action_code ='REJECT') then
2899 select comments
2900 into l_note
2901 from rcv_shipment_headers
2902 where shipment_header_id=p_object_id;
2903 end if;
2904 exception
2905 when others then
2906 l_note := null;
2907 end;
2908
2909 UPDATE PO_ACTION_HISTORY
2910 SET last_update_date = sysdate,
2911 last_updated_by = p_user_id,
2912 last_update_login = p_login_id,
2913 employee_id = p_employee_id,
2914 --employee_id = NVL(l_employee_id, employee_id),
2915 action_date = sysdate,
2916 action_code = p_action_code,
2917 note = l_note
2918 WHERE object_id = p_object_id
2919 AND object_type_code = p_object_type_code
2920 AND action_code IS NULL;
2921 --employee_id = NVL(p_old_employee_id, employee_id)
2922
2923 ELSE --}{
2924 l_progress := 'POS_WCAPPROVE_PVT.UpdatePOActionHistory: 02.';
2925 IF (g_asn_debug = 'Y') THEN
2926 debug_log(FND_LOG.LEVEL_STATEMENT,
2927 l_api_name,
2928 'Either object id or code is null '
2929 || l_progress);
2930 END IF;
2931 raise invalid_action;
2932 END IF; --}
2933
2934 EXCEPTION
2935 when invalid_action then
2936 IF (g_asn_debug = 'Y') THEN
2937 debug_log(FND_LOG.LEVEL_EXCEPTION,
2938 l_api_name,'invalid_action in UpdatePOActionHistory '
2939 || l_progress);
2940 END IF;
2941 raise;
2942 when others then
2943 IF (g_asn_debug = 'Y') THEN
2944 debug_log(FND_LOG.LEVEL_UNEXPECTED,
2945 l_api_name,'Exception in UpdatePOActionHistory '
2946 || l_progress);
2947 END IF;
2948 raise;
2949
2950 END UpdatePOActionHistory;
2951
2952
2953 PROCEDURE InsertPOActionHistory (p_object_id IN NUMBER,
2954 p_object_type_code IN VARCHAR2,
2955 p_object_sub_type_code IN VARCHAR2,
2956 p_sequence_num IN NUMBER,
2957 p_action_code IN VARCHAR2,
2958 p_action_date IN DATE,
2959 p_employee_id IN NUMBER,
2960 p_approval_path_id IN NUMBER,
2961 p_note IN VARCHAR2,
2962 p_object_revision_num IN NUMBER,
2963 p_offline_code IN VARCHAR2,
2964 p_request_id IN NUMBER,
2965 p_program_application_id IN NUMBER,
2966 p_program_id IN NUMBER,
2967 p_program_date IN DATE,
2968 p_user_id IN NUMBER,
2969 p_login_id IN NUMBER)
2970 IS
2971 -- pragma AUTONOMOUS_TRANSACTION;
2972 l_progress VARCHAR2(240) ;
2973 l_sequence_num PO_ACTION_HISTORY.sequence_num%TYPE := NULL;
2974 l_api_name varchar2(50) := ' InsertPOActionHistory';
2975 l_note po_action_history.note%type;
2976
2977 BEGIN
2978
2979
2980
2981 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 01.';
2982 IF (g_asn_debug = 'Y') THEN
2983 debug_log(FND_LOG.LEVEL_STATEMENT,
2984 l_api_name,'Enter in InsertPOActionHistory '
2985 || l_progress);
2986 END IF;
2987
2988 l_sequence_num := p_sequence_num;
2989
2990 IF (g_asn_debug = 'Y') THEN
2991 debug_log(FND_LOG.LEVEL_STATEMENT,
2992 l_api_name,'l_sequence_num '
2993 || l_sequence_num);
2994 END IF;
2995
2996 IF (l_sequence_num is NULL ) THEN --{
2997
2998
2999 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 02.';
3000
3001 SELECT MAX(sequence_num)
3002 INTO l_sequence_num
3003 FROM PO_ACTION_HISTORY
3004 WHERE object_id = p_object_id
3005 AND object_type_code = p_object_type_code;
3006
3007 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 03.';
3008
3009 IF (l_sequence_num IS NULL) THEN
3010 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 04.';
3011 l_sequence_num := 0;
3012 ELSE
3013 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 05.';
3014 l_sequence_num := l_sequence_num + 1;
3015 END IF;
3016
3017 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 06.';
3018
3019
3020 END IF; --}
3021
3022 l_progress := 'POS_WCAPPROVE_PVT.InsertPOActionHistory: 07.';
3023
3024 IF (g_asn_debug = 'Y') THEN
3025 debug_log(FND_LOG.LEVEL_STATEMENT,
3026 l_api_name,'l_sequence_num before insert '
3027 || l_sequence_num);
3028 END IF;
3029
3030
3031
3032 INSERT INTO PO_ACTION_HISTORY
3033 (object_id,
3034 object_type_code,
3035 object_sub_type_code,
3036 sequence_num,
3037 last_update_date,
3038 last_updated_by,
3039 employee_id,
3040 action_code,
3041 action_date,
3042 note,
3043 object_revision_num,
3044 last_update_login,
3045 creation_date,
3046 created_by,
3047 request_id,
3048 program_application_id,
3049 program_id,
3050 program_date,
3051 approval_path_id,
3052 offline_code,
3053 program_update_date)
3054 VALUES (p_object_id,
3055 'WC',
3056 'WC',
3057 l_sequence_num,
3058 sysdate,
3059 p_user_id,
3060 p_employee_id,
3061 p_action_code,
3062 p_action_date,
3063 l_note,
3064 p_object_revision_num,
3065 p_login_id,
3066 sysdate,
3067 p_user_id,
3068 p_request_id,
3069 p_program_application_id,
3070 p_program_id,
3071 p_program_date,
3072 p_approval_path_id,
3073 p_offline_code,
3074 sysdate);
3075
3076
3077 EXCEPTION
3078 when others then
3079 IF (g_asn_debug = 'Y') THEN
3080 debug_log(FND_LOG.LEVEL_UNEXPECTED,
3081 l_api_name,'Exception in InsertPOActionHistory '
3082 || l_progress);
3083 END IF;
3084 raise;
3085
3086 END InsertPOActionHistory;
3087
3088
3089 procedure post_approval_notif
3090 (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 l_progress varchar2(300);
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 cursor get_shipment_lines(l_shipment_header_id number) is
3110 select rsl.shipment_line_id
3111 from rcv_shipment_lines rsl
3112 where rsl.shipment_header_id = l_shipment_header_id;
3113 */
3114
3115 CURSOR get_action_history IS
3116 SELECT object_id,
3117 object_type_code,
3118 object_sub_type_code,
3119 sequence_num,
3120 object_revision_num,
3121 request_id,
3122 program_application_id,
3123 program_date,
3124 program_id,
3125 last_update_date,
3126 employee_id
3127 FROM PO_ACTION_HISTORY
3128 WHERE object_type_code = 'WC'
3129 AND object_id = l_shipment_header_id
3130 AND sequence_num = l_sequence_num;
3131
3132 Recinfo get_action_history%ROWTYPE;
3133
3134 begin
3135 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif: 01.';
3136 IF (g_asn_debug = 'Y') THEN
3137 debug_log(FND_LOG.LEVEL_STATEMENT,
3138 l_api_name,
3139 'Enter in post_approval_notif '
3140 || l_progress);
3141 debug_log(FND_LOG.LEVEL_STATEMENT,
3142 l_api_name,
3143 'p_funcmode '
3144 || p_funcmode);
3145 END IF;
3146
3147
3148 if (p_funcmode IN ('FORWARD', 'QUESTION', 'ANSWER')) then --{
3149
3150 if (p_funcmode = 'FORWARD') then
3151 l_action := 'DELEGATE';
3152 elsif (p_funcmode = 'QUESTION') then
3153 l_action := 'QUESTION';
3154 elsif (p_funcmode = 'ANSWER') then
3155 l_action := 'ANSWER';
3156 end if;
3157
3158 l_shipment_header_id := wf_engine.GetItemAttrNumber
3159 (itemtype => p_itemtype,
3160 itemkey => p_itemkey,
3161 aname => 'WORK_CONFIRMATION_ID');
3162 IF (g_asn_debug = 'Y') THEN
3163 debug_log(FND_LOG.LEVEL_STATEMENT,
3164 l_api_name,'l_shipment_header_id ' ||
3165 l_shipment_header_id);
3166 END IF;
3167
3168 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_NEW_ROLE,
3169 l_origsys, l_new_recipient_id);
3170
3171
3172
3173 --loop --{
3174 l_progress := 'POS_WCAPPROVE_PVT.post_approval_notif:02.';
3175
3176
3177 select max(sequence_num)
3178 into l_sequence_num
3179 from po_action_history
3180 where object_type_code ='WC'
3181 and object_id = l_shipment_header_id;
3182
3183
3184 OPEN get_action_history;
3185
3186 FETCH get_action_history INTO Recinfo;
3187
3188 IF (get_action_history%NOTFOUND) then
3189 IF (g_asn_debug = 'Y') THEN
3190 debug_log(FND_LOG.LEVEL_STATEMENT,
3191 l_api_name,
3192 'no_data_round ' );
3193 END IF;
3194
3195 RAISE NO_DATA_FOUND;
3196 END IF;
3197
3198 CLOSE get_action_history;
3199
3200
3201 UpdatePOActionHistory(
3202 p_object_id => Recinfo.object_id,
3203 p_object_type_code => Recinfo.object_type_code,
3204 p_employee_id => Recinfo.employee_id,
3205 p_action_code => l_action,
3206 p_note => wf_engine.context_user_comment,
3207 p_user_id => fnd_global.user_id,
3208 p_login_id => fnd_global.login_id);
3209
3210
3211 InsertPOActionHistory(
3212 p_object_id => Recinfo.object_id,
3213 p_object_type_code=>Recinfo.object_type_code, p_object_sub_type_code => NULL,
3214 p_sequence_num => Recinfo.sequence_num+1 ,
3215 p_action_code =>NULL ,
3216 p_action_date =>NULL ,
3217 p_employee_id => l_new_recipient_id,
3218 p_approval_path_id => NULL ,
3219 p_note => NULL,
3220 p_object_revision_num => NULL,
3221 p_offline_code => NULL,
3222 p_request_id => Recinfo.request_id,
3223 p_program_application_id => Recinfo.program_application_id,
3224 p_program_id =>Recinfo.program_id ,
3225 p_program_date => Recinfo.program_date ,
3226 p_user_id => fnd_global.user_id ,
3227 p_login_id => fnd_global.login_id);
3228
3229 -- end loop; --}
3230
3231 x_resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
3232
3233 return;
3234 end if; --}
3235
3236
3237 if (p_funcmode = 'RESPOND') then --{
3238 l_notification_id := WF_ENGINE.context_nid;
3239 IF (g_asn_debug = 'Y') THEN
3240 debug_log(FND_LOG.LEVEL_STATEMENT,
3241 l_api_name,
3242 'l_notification_id '||l_notification_id );
3243 END IF;
3244
3245
3246 l_result := wf_notification.GetAttrText
3247 (l_notification_id, 'RESULT');
3248
3249
3250 IF (g_asn_debug = 'Y') THEN
3251 debug_log(FND_LOG.LEVEL_STATEMENT,
3252 l_api_name,
3253 'l_result '||l_result );
3254 END IF;
3255
3256
3257 if (l_result = 'FORWARD') then --{
3258
3259 l_forwardTo := wf_notification.GetAttrText
3260 (l_notification_id,
3261 'FORWARD_TO_USERNAME_RESPONSE');
3262
3263
3264 l_forward_to_username_response :=
3265 wf_engine.GetItemAttrText
3266 (itemtype => p_itemtype,
3267 itemkey => p_itemkey,
3268 aname => 'FORWARD_TO_USERNAME_RESPONSE');
3269
3270 if(l_forwardTo is null) then
3271 fnd_message.set_name('ICX', 'ICX_POR_WF_NOTIF_NO_USER');
3272 app_exception.raise_exception;
3273 end if;
3274 end if;--}
3275
3276
3277 x_resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
3278 return;
3279 end if; --}
3280
3281
3282 EXCEPTION
3283 when no_data_found then
3284 IF (g_asn_debug = 'Y') THEN
3285 debug_log(FND_LOG.LEVEL_UNEXPECTED,
3286 l_api_name,
3287 'No data found in post_approval_notif '
3288 || l_progress);
3289 END IF;
3290 raise;
3291
3292 when others then
3293 IF (g_asn_debug = 'Y') THEN
3294 debug_log(FND_LOG.LEVEL_UNEXPECTED,
3295 l_api_name,'Exception in post_approval_notif '
3296 || l_progress);
3297 END IF;
3298 raise;
3299
3300 END post_approval_notif;
3301
3302 procedure reject_doc
3303 (p_itemtype in varchar2,
3304 p_itemkey in varchar2,
3305 p_actid in number,
3306 p_funcmode in varchar2,
3307 x_resultout out NOCOPY varchar2) IS
3308
3309 l_progress varchar2(300);
3310 l_api_name varchar2(50) := p_itemkey ||' reject_doc';
3311 l_approval_status rcv_shipment_headers.approval_status%type := 'REJECTED';
3312 l_result varchar2(3);
3313 l_shipment_header_id number;
3314 l_po_header_id number;
3315 l_work_confirmation_number varchar2(30);
3316 l_note varchar2(4000);
3317 l_view_wc_lines_detail_url varchar2(1000);
3318 begin
3319 l_progress := 'POS_WCAPPROVE_PVT.reject_doc: 01.';
3320 IF (g_asn_debug = 'Y') THEN
3321 debug_log(FND_LOG.LEVEL_STATEMENT,
3322 l_api_name,
3323 'Enter in reject_doc '
3324 || l_progress);
3325 END IF;
3326
3327 if (p_funcmode <> wf_engine.eng_run) then
3328 x_resultout := wf_engine.eng_null;
3329 return;
3330 end if;
3331
3332 l_progress := 'POS_WCAPPROVE_PVT.reject_doc: 02.';
3333 l_shipment_header_id := wf_engine.GetItemAttrNumber
3334 (itemtype => p_itemtype,
3335 itemkey => p_itemkey,
3336 aname => 'WORK_CONFIRMATION_ID');
3337
3338
3339
3340 update_approval_status(p_shipment_header_id => l_shipment_header_id,
3341 p_note => null,
3342 p_approval_status => l_approval_status,
3343 p_level => 'HEADER',
3344 x_resultout => l_result);
3345
3346
3347 l_progress := 'POS_WCAPPROVE_PVT.reject_doc: 03.';
3348 IF (l_result = 'N') THEN
3349
3350 IF (g_asn_debug = 'Y') THEN
3351 debug_log(FND_LOG.LEVEL_STATEMENT,
3352 l_api_name,
3353 'Could not Reject the document '
3354 || l_progress);
3355 END IF;
3356 x_resultout := wf_engine.eng_completed || ':' || 'N';
3357 END IF;
3358
3359 IF (l_result = 'Y') THEN
3360
3361 IF (g_asn_debug = 'Y') THEN
3362 debug_log(FND_LOG.LEVEL_STATEMENT,
3363 l_api_name,
3364 'Rejected the document '
3365 || l_progress);
3366 END IF;
3367 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
3368 itemkey => p_itemkey,
3369 aname => 'DOC_STATUS',
3370 avalue => 'REJECTED');
3371
3372
3373 l_po_header_id := wf_engine.GetItemAttrNumber
3374 (itemtype => p_itemtype,
3375 itemkey => p_itemkey,
3376 aname => 'PO_DOCUMENT_ID');
3377
3378 IF (g_asn_debug = 'Y') THEN
3379 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
3380 'l_po_header_id ' || l_po_header_id);
3381 END IF;
3382
3383 l_work_confirmation_number :=
3384 wf_engine.GetItemAttrText (itemtype => p_itemtype,
3385 itemkey => p_itemkey,
3386 aname => 'WORK_CONFIRMATION_NUMBER');
3387
3388 IF (g_asn_debug = 'Y') THEN
3389 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
3390 'l_work_confirmation_number ' || l_work_confirmation_number);
3391 END IF;
3392
3393 l_view_wc_lines_detail_url :=
3394 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/pos/wc/webui/WcDetailsPG' || '&' ||
3395 'WcStatus=REJECTED' || '&' ||
3396 'PoHeaderId=' || to_char(l_po_header_id) || '&' ||
3397 'WcHeaderId=' || to_char(l_shipment_header_id) || '&' ||
3398 'WcNum=' || (l_work_confirmation_number) || '&' ||
3399 -- 'ReadOnly=Y' || '&' ||
3400 'addBreadCrumb=Y';
3401
3402 wf_engine.SetItemAttrText ( itemtype => p_itemType,
3403 itemkey => p_itemkey,
3404 aname => 'VIEW_WC_LINES_DETAIL_URL' ,
3405 avalue => l_view_wc_lines_detail_url);
3406 x_resultout := wf_engine.eng_completed || ':' || 'Y';
3407 END IF;
3408 EXCEPTION
3409 when others then
3410 IF (g_asn_debug = 'Y') THEN
3411 debug_log(FND_LOG.LEVEL_UNEXPECTED,
3412 l_api_name,'Exception in reject_doc '
3413 || l_progress);
3414 END IF;
3415 raise;
3416
3417 END reject_doc;
3418
3419 procedure Approve_doc
3420 (p_itemtype in varchar2,
3421 p_itemkey in varchar2,
3422 p_actid in number,
3423 p_funcmode in varchar2,
3424 x_resultout out NOCOPY varchar2) IS
3425
3426 l_progress varchar2(300);
3427 l_api_name varchar2(50) := p_itemkey || ' Approve_doc';
3428 l_approval_status rcv_shipment_headers.approval_status%type := 'APPROVED';
3429 l_result varchar2(3);
3430 l_shipment_header_id number;
3431 l_po_header_id number;
3432 l_note varchar2(4000);
3433 l_view_wc_lines_detail_url varchar2(1000);
3434 l_work_confirmation_number varchar2(30);
3435 begin
3436 l_progress := 'POS_WCAPPROVE_PVT.Approve_doc: 01.';
3437 IF (g_asn_debug = 'Y') THEN
3438 debug_log(FND_LOG.LEVEL_STATEMENT,
3439 l_api_name,
3440 'Enter in Approve_doc '
3441 || l_progress);
3442 END IF;
3443
3444 if (p_funcmode <> wf_engine.eng_run) then
3445 x_resultout := wf_engine.eng_null;
3446 return;
3447 end if;
3448
3449 l_progress := 'POS_WCAPPROVE_PVT.Approve_doc: 02.';
3450 l_shipment_header_id := wf_engine.GetItemAttrNumber
3451 (itemtype => p_itemtype,
3452 itemkey => p_itemkey,
3453 aname => 'WORK_CONFIRMATION_ID');
3454
3455
3456 update_approval_status(p_shipment_header_id => l_shipment_header_id,
3457 p_note => null,
3458 p_approval_status => l_approval_status,
3459 p_level => 'HEADER',
3460 x_resultout => l_result);
3461
3462 l_progress := 'POS_WCAPPROVE_PVT.Approve_doc: 03.';
3463 IF (l_result = 'N') THEN
3464
3465 IF (g_asn_debug = 'Y') THEN
3466 debug_log(FND_LOG.LEVEL_STATEMENT,
3467 l_api_name,
3468 'Could not Reject the document '
3469 || l_progress);
3470 END IF;
3471 x_resultout := wf_engine.eng_completed || ':' || 'N';
3472 END IF;
3473
3474 IF (l_result = 'Y') THEN
3475
3476 IF (g_asn_debug = 'Y') THEN
3477 debug_log(FND_LOG.LEVEL_STATEMENT,
3478 l_api_name,
3479 'Approved the document '
3480 || l_progress);
3481 END IF;
3482 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
3483 itemkey => p_itemkey,
3484 aname => 'DOC_STATUS',
3485 avalue => 'APPROVED');
3486
3487 l_po_header_id := wf_engine.GetItemAttrNumber
3488 (itemtype => p_itemtype,
3489 itemkey => p_itemkey,
3490 aname => 'PO_DOCUMENT_ID');
3491
3492 IF (g_asn_debug = 'Y') THEN
3493 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
3494 'l_po_header_id ' || l_po_header_id);
3495 END IF;
3496
3497 l_work_confirmation_number :=
3498 wf_engine.GetItemAttrText (itemtype => p_itemtype,
3499 itemkey => p_itemkey,
3500 aname => 'WORK_CONFIRMATION_NUMBER');
3501
3502 IF (g_asn_debug = 'Y') THEN
3503 debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
3504 'l_work_confirmation_number ' || l_work_confirmation_number);
3505 END IF;
3506
3507 l_view_wc_lines_detail_url :=
3508 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/pos/wc/webui/WcDetailsPG' || '&' ||
3509 'WcStatus=APPROVED' || '&' ||
3510 'PoHeaderId=' || to_char(l_po_header_id) || '&' ||
3511 'WcHeaderId=' || to_char(l_shipment_header_id) || '&' ||
3512 'WcNum=' || (l_work_confirmation_number) || '&' ||
3513 --'ReadOnly=Y' || '&' ||
3514 'addBreadCrumb=Y';
3515
3516 wf_engine.SetItemAttrText ( itemtype => p_itemType,
3517 itemkey => p_itemkey,
3518 aname => 'VIEW_WC_LINES_DETAIL_URL' ,
3519 avalue => l_view_wc_lines_detail_url);
3520
3521 x_resultout := wf_engine.eng_completed || ':' || 'Y';
3522 END IF;
3523 EXCEPTION
3524 when others then
3525 IF (g_asn_debug = 'Y') THEN
3526 debug_log(FND_LOG.LEVEL_UNEXPECTED,
3527 l_api_name,'Exception in Approve_doc '
3528 || l_progress);
3529 END IF;
3530 raise;
3531
3532 END Approve_doc;
3533
3534 procedure update_approval_status
3535 (p_shipment_header_id in number,
3536 p_note in varchar2,
3537 p_approval_status in varchar2,
3538 p_level in varchar2,
3539 x_resultout out NOCOPY varchar2) IS
3540
3541 cursor lock_rsh(l_shipment_header_id number) is
3542 select null
3543 from rcv_shipment_headers
3544 where shipment_header_id = l_shipment_header_id
3545 for update of shipment_header_id nowait;
3546
3547 resource_busy_exc EXCEPTION;
3548 -- pragma EXCEPTION_INIT(resource_busy_exc, -00054);
3549
3550 l_locked_doc BOOLEAN := FALSE;
3551 l_shipment_header_id number;
3552 l_progress varchar2(300);
3553 l_api_name varchar2(50) := ' update_approval_status';
3554 l_note po_action_history.note%type;
3555 begin
3556 l_progress := 'POS_WCAPPROVE_PVT.update_approval_status: 01.';
3557 IF (g_asn_debug = 'Y') THEN
3558 debug_log(FND_LOG.LEVEL_STATEMENT,
3559 l_api_name,
3560 'Enter in update_approval_status '
3561 || l_progress);
3562 debug_log(FND_LOG.LEVEL_STATEMENT,
3563 l_api_name,
3564 'p_note '
3565 || p_note);
3566 END IF;
3567
3568
3569
3570
3571 If (p_level = 'HEADER') then
3572 for i in 1..1000
3573 loop
3574 begin
3575 /* Opening the cursor will lock the row */
3576 Open lock_rsh(p_shipment_header_id);
3577 Close lock_rsh;
3578
3579 l_locked_doc := TRUE;
3580
3581 EXIT;
3582
3583 EXCEPTION
3584 WHEN resource_busy_exc THEN
3585 NULL;
3586 END;
3587
3588 end loop;
3589
3590 IF (NOT l_locked_doc) THEN
3591
3592 IF (g_asn_debug = 'Y') THEN
3593 debug_log(FND_LOG.LEVEL_STATEMENT,
3594 l_api_name,'Could not lock row '
3595 || l_progress);
3596 END IF;
3597 ROLLBACK;
3598 x_resultout := 'N';
3599 return;
3600
3601 END IF;
3602
3603 update rcv_shipment_headers
3604 set approval_status = p_approval_status,
3605 comments = nvl(p_note,comments)
3606 where shipment_header_id = p_shipment_header_id;
3607 end if;
3608
3609
3610 If (p_level = 'LINES') then
3611 /* If p_level is lines, then it can
3612 * come directly from notificatiion.
3613 * So update the header with the comments.
3614 */
3615 update rcv_shipment_lines
3616 set approval_status = p_approval_status
3617 where shipment_header_id=p_shipment_header_id ;
3618
3619 update rcv_shipment_headers
3620 set comments = nvl(p_note,comments)
3621 where shipment_header_id = p_shipment_header_id;
3622 end if;
3623
3624 x_resultout := 'Y';
3625 exception
3626 when others then
3627 IF (g_asn_debug = 'Y') THEN
3628 debug_log(FND_LOG.LEVEL_UNEXPECTED,
3629 l_api_name,'Exception in update_approval_status '
3630 || l_progress);
3631 END IF;
3632 raise;
3633
3634 END update_approval_status;
3635
3636 procedure insert_into_rti
3637 (p_itemtype in varchar2,
3638 p_itemkey in varchar2,
3639 p_actid in number,
3640 p_funcmode in varchar2,
3641 x_resultout out NOCOPY varchar2) IS
3642
3643 l_progress varchar2(300);
3644 l_api_name varchar2(50) := p_itemkey || ' insert_into_rti';
3645 l_shipment_header_id number;
3646
3647 Cursor get_wcr_info(l_shipment_header_id number) is
3648 SELECT rsl.po_line_location_id,
3649 pll.unit_meas_lookup_code,
3650 rsl.unit_of_measure unit_of_measure,
3651 rsl.unit_of_measure primary_unit_of_measure,
3652 rsl.unit_of_measure source_doc_unit_of_measure,
3653 NVL(pll.promised_date, pll.need_by_date) promised_date,
3654 rsl.to_organization_id ship_to_organization_id,
3655 null quantity_ordered,
3656 null amount_ordered,
3657 NVL(pll.price_override, pl.unit_price) po_unit_price,
3658 pll.match_option,
3659 rsl.category_id,
3660 rsl.item_description,
3661 pl.po_line_id,
3662 ph.currency_code,
3663 ph.rate_type currency_conversion_type,
3664 ph.segment1 document_num,
3665 null po_distribution_id, --pod.po_distribution_id,
3666 rsl.req_distribution_id,
3667 rsl.requisition_line_id,
3668 rsl.deliver_to_location_id deliver_to_location_id,
3669 rsl.deliver_to_location_id location_id,
3670 rsl.deliver_to_person_id,
3671 null currency_conversion_date, --pod.rate_date currency_conversion_date,
3672 null currency_conversion_rate, --pod.rate currency_conversion_rate,
3673 rsl.destination_type_code destination_type_code,
3674 rsl.destination_type_code destination_context,
3675 null charge_account_id, --pod.code_combination_id ,
3676 null destination_organization_id, --pod.destination_organization_id,
3677 null subinventory, --pod.destination_subinventory ,
3678 rsl.ship_to_location_id,
3679 rsl.comments,
3680 rsl.attribute_category attribute_category,
3681 rsl.attribute1 attribute1,
3682 rsl.attribute2 attribute2,
3683 rsl.attribute3 attribute3,
3684 rsl.attribute4 attribute4,
3685 rsl.attribute5 attribute5,
3686 rsl.attribute6 attribute6,
3687 rsl.attribute7 attribute7,
3688 rsl.attribute8 attribute8,
3689 rsl.attribute9 attribute9,
3690 rsl.attribute10 attribute10,
3691 rsl.attribute11 attribute11,
3692 rsl.attribute12 attribute12,
3693 rsl.attribute13 attribute13,
3694 rsl.attribute14 attribute14,
3695 rsl.attribute15 attribute15,
3696 NVL(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code,
3697 rsl.shipment_line_id,
3698 rsl.item_id,
3699 rsl.item_revision,
3700 rsh.vendor_id,
3701 rsh.shipment_num,
3702 rsh.freight_carrier_code,
3703 rsh.bill_of_lading,
3704 rsh.packing_slip,
3705 rsh.shipped_date,
3706 rsh.expected_receipt_date,
3707 rsh.waybill_airbill_num ,
3708 rsh.vendor_site_id,
3709 rsl.to_organization_id,
3710 rsl.routing_header_id,
3711 rsl.vendor_item_num,
3712 rsl.vendor_lot_num,
3713 rsl.ussgl_transaction_code,
3714 rsl.government_context,
3715 pll.po_header_id,
3716 ph.revision_num po_revision_num,
3717 pl.line_num document_line_num,
3718 pll.shipment_num document_shipment_line_num,
3719 null document_distribution_num , --pod.distribution_num
3720 pll.po_release_id,
3721 pl.job_id,
3722 ph.org_id,
3723 rsl.amount_shipped amount,
3724 rsl.quantity_shipped quantity,
3725 rsl.quantity_shipped source_doc_quantity,
3726 rsl.quantity_shipped primary_quantity,
3727 rsl.quantity_shipped quantity_shipped,
3728 rsl.amount_shipped amount_shipped,
3729 rsl.requested_amount requested_amount,
3730 rsl.material_stored_amount material_stored_amount,
3731 pll.matching_basis
3732 FROM
3733 --po_distributions_all pod,
3734 po_line_locations_all pll,
3735 po_lines_all pl,
3736 po_headers_all ph,
3737 rcv_shipment_lines rsl,
3738 rcv_shipment_headers rsh
3739 WHERE
3740 rsh.shipment_header_id = l_shipment_header_id
3741 and rsl.shipment_header_id = rsh.shipment_header_id
3742 and rsl.po_header_id = ph.po_header_id
3743 --and pod.po_header_id = ph.po_header_id
3744 --and pod.line_location_id = pll.line_location_id
3745 and rsl.po_line_id = pl.po_line_id
3746 and rsl.po_line_location_id = pll.line_location_id
3747 and rsh.receipt_source_code = 'VENDOR'
3748 and pll.po_line_id = pl.po_line_id
3749 AND NVL(pll.approved_flag, 'N') = 'Y'
3750 AND NVL(pll.cancel_flag, 'N') = 'N'
3751 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED','PREPAYMENT');
3752
3753
3754 wcr_line_info get_wcr_info%rowtype;
3755
3756 /* Bug 6709928 -- Added columns destination_type_code,destination_type_code
3757 to the cursor get_dist_info*/
3758 cursor get_dist_info(l_line_location_id number) is
3759 select pod.po_distribution_id,
3760 pod.rate_date currency_conversion_date,
3761 pod.rate currency_conversion_rate,
3762 pod.code_combination_id charge_account_id,
3763 pod.destination_organization_id,
3764 pod.destination_subinventory subinventory,
3765 pod.distribution_num document_distribution_num,
3766 pod.quantity_ordered,
3767 pod.amount_ordered,
3768 pod.destination_type_code destination_type_code,
3769 pod.destination_type_code destination_context
3770 from po_distributions_all pod
3771 where pod.line_location_id = l_line_location_id;
3772 cursor get_shipment_lines(l_shipment_header_id number) is
3773 select rsl.shipment_line_id
3774 from rcv_shipment_lines rsl
3775 where rsl.shipment_header_id = l_shipment_header_id;
3776
3777
3778 X_emp_id number;
3779 X_emp_name per_employees_current_x.full_name%TYPE;
3780 X_location_id number;
3781 X_location_code hr_locations_all.location_code%TYPE;
3782 X_is_buyer BOOLEAN;
3783 X_emp_flag BOOLEAN;
3784 l_emp_ok BOOLEAN;
3785 l_uom_code mtl_units_of_measure.uom_code%type;
3786 l_row_id varchar2(40);
3787 l_interface_id number;
3788 l_group_id number;
3789 l_vendor_id number;
3790 l_vendor_site_id number;
3791 l_ship_to_org_id number;
3792 l_ship_to_location_id number;
3793 l_header_interface_id number;
3794 l_expected_receipt_date date;
3795 l_shipment_num varchar2(50);
3796 l_receipt_num varchar2(50);
3797 l_matching_basis varchar2(35);
3798 l_remaining_amount number;
3799 l_old_remaining_amount number;
3800 l_transacted_amount number;
3801 l_interface_amount number;
3802 l_available_amount number;
3803 l_remaining_quantity number;
3804 l_old_remaining_quantity number;
3805 l_transacted_quantity number;
3806 l_interface_quantity number;
3807 l_available_quantity number;
3808 l_first_time boolean := TRUE;
3809 l_insert_into_rti boolean := TRUE;
3810
3811
3812 begin
3813 l_progress := 'POS_WCAPPROVE_PVT.insert_into_rti: 01.';
3814 IF (g_asn_debug = 'Y') THEN
3815 debug_log(FND_LOG.LEVEL_STATEMENT,
3816 l_api_name,
3817 'Enter in insert_into_rti '
3818 || l_progress);
3819 END IF;
3820
3821 if (p_funcmode <> wf_engine.eng_run) then
3822 x_resultout := wf_engine.eng_null;
3823 return;
3824 end if;
3825
3826 l_shipment_header_id := wf_engine.GetItemAttrNumber
3827 (itemtype => p_itemtype,
3828 itemkey => p_itemkey,
3829 aname => 'WORK_CONFIRMATION_ID');
3830
3831
3832 SELECT rcv_headers_interface_s.NEXTVAL
3833 INTO l_header_interface_id
3834 FROM SYS.DUAL;
3835
3836 select rcv_interface_groups_s.nextval
3837 into l_group_id
3838 from dual;
3839
3840 IF (g_asn_debug = 'Y') THEN
3841 debug_log(FND_LOG.LEVEL_STATEMENT,
3842 l_api_name,'l_header_interface_id ' ||
3843 l_header_interface_id ||
3844 ' l_group_id ' || l_group_id);
3845 END IF;
3846
3847 wf_engine.SetItemAttrNumber ( itemtype => p_itemtype,
3848 itemkey => p_itemkey,
3849 aname => 'INTERFACE_GROUP_ID',
3850 avalue => l_group_id);
3851
3852 l_emp_ok := po_employees_sv.get_employee (X_emp_id,
3853 X_emp_name, X_location_id, X_location_code,
3854 X_is_buyer, X_emp_flag );
3855
3856 IF (g_asn_debug = 'Y') THEN
3857 debug_log(FND_LOG.LEVEL_STATEMENT,
3858 l_api_name,'x_emp_id ' ||
3859 x_emp_id);
3860 END IF;
3861
3862 /*
3863 select vendor_id,
3864 vendor_site_id,
3865 ship_to_org_id,
3866 ship_to_location_id,
3867 expected_receipt_date,
3868 shipment_num
3869 into l_vendor_id,
3870 l_vendor_site_id,
3871 l_ship_to_org_id,
3872 l_ship_to_location_id,
3873 l_expected_receipt_date,
3874 l_shipment_num
3875 from rcv_shipment_headers
3876 where shipment_header_id=l_shipment_header_id;
3877
3878 IF (g_asn_debug = 'Y') THEN
3879 debug_log(FND_LOG.LEVEL_STATEMENT,
3880 l_api_name,'insert into rhi');
3881 END IF;
3882
3883
3884 INSERT INTO rcv_headers_interface( header_interface_id
3885 , group_id
3886 , processing_status_code
3887 , receipt_source_code
3888 , asn_type
3889 , transaction_type
3890 , last_update_date
3891 , last_updated_by
3892 , creation_date
3893 , created_by
3894 , vendor_id
3895 , vendor_site_id
3896 , ship_to_organization_id
3897 , location_id
3898 , expected_receipt_date
3899 , employee_id
3900 , validation_flag
3901 , shipment_num
3902 ) VALUES (
3903 l_header_interface_id,
3904 l_group_id ,
3905 'PENDING', --processing_status_code,
3906 'VENDOR', --receipt_source_code,
3907 'STD',
3908 'NEW', -- transaction_type
3909 sysdate, --last_update_date,
3910 fnd_global.user_id , --last_updated_by
3911 sysdate, --creation_date
3912 fnd_global.login_id , --created_by
3913 l_vendor_id,
3914 l_vendor_site_id,
3915 l_ship_to_org_id,
3916 l_ship_to_location_id,
3917 l_expected_receipt_date,
3918 x_emp_id,
3919 'Y',
3920 l_shipment_num
3921 );
3922
3923 */
3924
3925 /* Update rcv_shipment_headers with receipt_num */
3926 /*
3927 */
3928
3929 IF (g_asn_debug = 'Y') THEN
3930 debug_log(FND_LOG.LEVEL_STATEMENT,
3931 l_api_name,'before cursor open');
3932 END IF;
3933
3934 open get_wcr_info(l_shipment_header_id);
3935 loop --{
3936
3937 l_progress := 'POS_WCAPPROVE_PVT.insert_into_rti:02.';
3938
3939 IF (g_asn_debug = 'Y') THEN
3940 debug_log(FND_LOG.LEVEL_STATEMENT,
3941 l_api_name,'before cursor fetch');
3942 END IF;
3943 fetch get_wcr_info into wcr_line_info;
3944 exit when get_wcr_info%notfound;
3945
3946 IF (g_asn_debug = 'Y') THEN
3947 debug_log(FND_LOG.LEVEL_STATEMENT,
3948 l_api_name,'l_shipment_line_id ' ||
3949 wcr_line_info.shipment_line_id);
3950 END IF;
3951
3952
3953 If (wcr_line_info.unit_of_measure is not null) then
3954 select muom.uom_code
3955 into l_uom_code
3956 from mtl_units_of_measure muom
3957 WHERE muom.unit_of_measure =
3958 wcr_line_info.unit_of_measure;
3959 IF (g_asn_debug = 'Y') THEN
3960 debug_log(FND_LOG.LEVEL_STATEMENT,
3961 l_api_name,'l_uom_code ' ||
3962 l_uom_code);
3963 END IF;
3964
3965 end if;
3966
3967
3968 IF (g_asn_debug = 'Y') THEN
3969 debug_log(FND_LOG.LEVEL_STATEMENT,
3970 l_api_name,'before cursor open');
3971 END IF;
3972
3973
3974 l_matching_basis:= wcr_line_info.matching_basis;
3975 If (l_matching_basis = 'AMOUNT') then
3976 l_remaining_amount:= wcr_line_info.amount_shipped;
3977 end if;
3978 If (l_matching_basis = 'QUANTITY') then
3979 l_remaining_quantity:= wcr_line_info.quantity_shipped;
3980 end if;
3981 l_first_time := TRUE;
3982 open get_dist_info(wcr_line_info.po_line_location_id);
3983 loop --{
3984
3985 l_progress := 'POS_WCAPPROVE_PVT.insert_into_rti:03.';
3986
3987
3988 IF (g_asn_debug = 'Y') THEN
3989 debug_log(FND_LOG.LEVEL_STATEMENT,
3990 l_api_name,'before cursor fetch');
3991 END IF;
3992 /* Bug 6709928 ,getting the destination_type_code,destination_type_context*/
3993 fetch get_dist_info into
3994 wcr_line_info.po_distribution_id,
3995 wcr_line_info.currency_conversion_date,
3996 wcr_line_info.currency_conversion_rate,
3997 wcr_line_info.charge_account_id,
3998 wcr_line_info.destination_organization_id,
3999 wcr_line_info.subinventory,
4000 wcr_line_info.document_distribution_num,
4001 wcr_line_info.quantity_ordered,
4002 wcr_line_info.amount_ordered,
4003 wcr_line_info.destination_type_code,
4004 wcr_line_info.destination_context ;
4005 exit when get_dist_info%notfound or
4006 (l_matching_basis = 'AMOUNT' and l_remaining_amount <= 0)
4007 or
4008 (l_matching_basis = 'QUANTITY' and l_remaining_quantity <= 0);
4009
4010 IF (g_asn_debug = 'Y') THEN
4011 debug_log(FND_LOG.LEVEL_STATEMENT,
4012 l_api_name,'l_distribution_id ' ||
4013 wcr_line_info.po_distribution_id);
4014 END IF;
4015
4016 IF (g_asn_debug = 'Y') THEN
4017 debug_log(FND_LOG.LEVEL_STATEMENT,
4018 l_api_name,'matching_basis '||l_matching_basis );
4019 END IF;
4020 If (l_matching_basis = 'AMOUNT') then--{
4021 select nvl(sum(amount),0)
4022 into l_transacted_amount
4023 from rcv_transactions
4024 where po_distribution_id=
4025 wcr_line_info.po_distribution_id
4026 and transaction_type = 'RECEIVE';
4027
4028 IF (g_asn_debug = 'Y') THEN
4029 debug_log(FND_LOG.LEVEL_STATEMENT,
4030 l_api_name,'l_transacted_amount '||
4031 l_transacted_amount );
4032 END IF;
4033 select nvl(sum(amount),0)
4034 into l_interface_amount
4035 from rcv_transactions_interface
4036 where po_distribution_id=
4037 wcr_line_info.po_distribution_id
4038 and processing_status_code='PENDING'
4039 and transaction_status_code = 'PENDING'
4040 and transaction_type = 'RECEIVE';
4041
4042 IF (g_asn_debug = 'Y') THEN
4043 debug_log(FND_LOG.LEVEL_STATEMENT,
4044 l_api_name,'l_interface_amount '||
4045 l_interface_amount );
4046 END IF;
4047
4048 l_available_amount := wcr_line_info.amount_ordered -
4049 (l_transacted_amount +
4050 l_interface_amount);
4051
4052 IF (g_asn_debug = 'Y') THEN
4053 debug_log(FND_LOG.LEVEL_STATEMENT,
4054 l_api_name,'l_available_amount '||
4055 l_available_amount );
4056 END IF;
4057 If (l_available_amount > 0) then --{
4058 If l_first_time then--{
4059 IF (g_asn_debug = 'Y') THEN
4060 debug_log(FND_LOG.LEVEL_STATEMENT,
4061 l_api_name,'first time '||
4062 l_available_amount );
4063 END IF;
4064 l_first_time := FALSE;
4065 l_old_remaining_amount :=
4066 wcr_line_info.amount_shipped;
4067 l_remaining_amount :=
4068 wcr_line_info.amount_shipped - l_available_amount;
4069
4070 IF (g_asn_debug = 'Y') THEN
4071 debug_log(FND_LOG.LEVEL_STATEMENT,
4072 l_api_name,'l_remaining_amount '||
4073 l_remaining_amount );
4074 END IF;
4075 else --} {
4076 IF (g_asn_debug = 'Y') THEN
4077 debug_log(FND_LOG.LEVEL_STATEMENT,
4078 l_api_name,'not first time '||
4079 l_available_amount );
4080 END IF;
4081 l_old_remaining_amount :=
4082 l_remaining_amount;
4083 l_remaining_amount :=
4084 l_remaining_amount - l_available_amount;
4085
4086 IF (g_asn_debug = 'Y') THEN
4087 debug_log(FND_LOG.LEVEL_STATEMENT,
4088 l_api_name,'l_remaining_amount '||
4089 l_remaining_amount );
4090 END IF;
4091 end if; --}
4092
4093 If (l_remaining_amount >=0) then
4094 wcr_line_info.amount:=
4095 l_available_amount;
4096 else
4097 wcr_line_info.amount :=
4098 l_old_remaining_amount;
4099 end if;
4100
4101 IF (g_asn_debug = 'Y') THEN
4102 debug_log(FND_LOG.LEVEL_STATEMENT,
4103 l_api_name,'amount to be inserted '||
4104 wcr_line_info.amount );
4105 END IF;
4106 l_insert_into_rti := TRUE;
4107 else --}{
4108 l_insert_into_rti := FALSE;
4109 end if; --l_available_amount >0}
4110 elsif (l_matching_basis = 'QUANTITY') then --}{
4111 select nvl(sum(quantity),0)
4112 into l_transacted_quantity
4113 from rcv_transactions
4114 where po_distribution_id=
4115 wcr_line_info.po_distribution_id
4116 and transaction_type = 'RECEIVE';
4117
4118 IF (g_asn_debug = 'Y') THEN
4119 debug_log(FND_LOG.LEVEL_STATEMENT,
4120 l_api_name,'l_transacted_quantity '||
4121 l_transacted_quantity );
4122 END IF;
4123
4124 select nvl(sum(quantity),0)
4125 into l_interface_quantity
4126 from rcv_transactions_interface
4127 where po_distribution_id=
4128 wcr_line_info.po_distribution_id
4129 and processing_status_code='PENDING'
4130 and transaction_status_code = 'PENDING'
4131 and transaction_type = 'RECEIVE';
4132
4133 IF (g_asn_debug = 'Y') THEN
4134 debug_log(FND_LOG.LEVEL_STATEMENT,
4135 l_api_name,'l_interface_quantity '||
4136 l_interface_quantity );
4137 END IF;
4138
4139 l_available_quantity := wcr_line_info.quantity_ordered -
4140 (l_transacted_quantity +
4141 l_interface_quantity);
4142 IF (g_asn_debug = 'Y') THEN
4143 debug_log(FND_LOG.LEVEL_STATEMENT,
4144 l_api_name,'l_available_quantity '||
4145 l_available_quantity );
4146 END IF;
4147
4148 If (l_available_quantity > 0) then --{
4149 If l_first_time then --{
4150 IF (g_asn_debug = 'Y') THEN
4151 debug_log(FND_LOG.LEVEL_STATEMENT,
4152 l_api_name,'first time '||
4153 l_available_quantity );
4154 END IF;
4155
4156 l_first_time := FALSE;
4157 l_old_remaining_quantity :=
4158 wcr_line_info.quantity_shipped;
4159 l_remaining_quantity :=
4160 wcr_line_info.quantity_shipped - l_available_quantity;
4161 IF (g_asn_debug = 'Y') THEN
4162 debug_log(FND_LOG.LEVEL_STATEMENT,
4163 l_api_name,'l_old_remaining_quantity '||
4164 l_old_remaining_quantity );
4165 END IF;
4166 IF (g_asn_debug = 'Y') THEN
4167 debug_log(FND_LOG.LEVEL_STATEMENT,
4168 l_api_name,'l_remaining_quantity '||
4169 l_remaining_quantity );
4170 END IF;
4171 else --} {
4172 IF (g_asn_debug = 'Y') THEN
4173 debug_log(FND_LOG.LEVEL_STATEMENT,
4174 l_api_name,'not first time '||
4175 l_available_quantity );
4176 END IF;
4177 l_old_remaining_quantity :=
4178 l_remaining_quantity;
4179 l_remaining_quantity :=
4180 l_remaining_quantity - l_available_quantity;
4181 IF (g_asn_debug = 'Y') THEN
4182 debug_log(FND_LOG.LEVEL_STATEMENT,
4183 l_api_name,'l_old_remaining_quantity '||
4184 l_old_remaining_quantity );
4185 END IF;
4186 IF (g_asn_debug = 'Y') THEN
4187 debug_log(FND_LOG.LEVEL_STATEMENT,
4188 l_api_name,'l_remaining_quantity '||
4189 l_remaining_quantity );
4190 END IF;
4191
4192 end if; --}
4193
4194 If (l_remaining_quantity >=0) then
4195 wcr_line_info.quantity:=
4196 l_available_quantity;
4197 else
4198 wcr_line_info.quantity :=
4199 l_old_remaining_quantity;
4200 end if;
4201
4202 IF (g_asn_debug = 'Y') THEN
4203 debug_log(FND_LOG.LEVEL_STATEMENT,
4204 l_api_name,'quantity to be inserted '||
4205 wcr_line_info.quantity );
4206 END IF;
4207 l_insert_into_rti := TRUE;
4208 else --}{
4209 l_insert_into_rti := FALSE;
4210 end if; --}l_available_quantity >0
4211 end if; --}
4212
4213 If (l_insert_into_rti) then --{
4214 select rcv_transactions_interface_s.nextval
4215 into l_interface_id
4216 from dual;
4217
4218 IF (g_asn_debug = 'Y') THEN
4219 debug_log(FND_LOG.LEVEL_STATEMENT,
4220 l_api_name,'l_interface_id ' || l_interface_id);
4221 END IF;
4222
4223 rcv_asn_interface_trx_ins_pkg.insert_row
4224 (l_row_id,
4225 l_interface_id,--interface_id
4226 l_group_id, --group_id
4227 sysdate, --last_updated_date
4228 fnd_global.user_id, --last_updated_by,
4229 sysdate, --creation_date,
4230 fnd_global.login_id, --created_by,
4231 fnd_global.login_id, -- last_update_login,
4232 NULL, --request_id,
4233 null, --program_application_id,
4234 null, --program_id,
4235 null, --program_update_date,
4236 'RECEIVE', --transaction_type,
4237 sysdate, --transaction_date,
4238 'PENDING', --processing_status_code,
4239 'IMMEDIATE', --processing_mode_code,
4240 --'BATCH',
4241 null, --processing_request_id,
4242 'PENDING', --.transaction_status_code,
4243 wcr_line_info.category_id,
4244 wcr_line_info.quantity, --quantity
4245 wcr_line_info.unit_of_measure,
4246 'ISP', --.interface_source_code,
4247 NULL, --.interface_source_line_id,
4248 NULL, --.inv_transaction_id,
4249 wcr_line_info.item_id,
4250 wcr_line_info.item_description,
4251 wcr_line_info.item_revision,
4252 l_uom_code, --uom_code,
4253 x_emp_id, --employee_id,
4254 'DELIVER', --auto_transact_code,
4255 l_shipment_header_id, --l_shipment_header_id
4256 wcr_line_info.shipment_line_id,
4257 wcr_line_info.ship_to_location_id,
4258 wcr_line_info.primary_quantity,
4259 wcr_line_info.primary_unit_of_measure,
4260 'VENDOR', --.receipt_source_code,
4261 wcr_line_info.vendor_id,
4262 wcr_line_info.vendor_site_id,
4263 NULL, --from_organization_id,
4264 NULL, --from_subinventory,
4265 wcr_line_info.to_organization_id,
4266 NULL, --.intransit_owning_org_id,
4267 wcr_line_info.routing_header_id,
4268 NULL, --.routing_step_id,
4269 'PO', --source_document_code,
4270 NULL, --.parent_transaction_id,
4271 wcr_line_info.po_header_id,
4272 wcr_line_info.po_revision_num,
4273 wcr_line_info.po_release_id,
4274 wcr_line_info.po_line_id,
4275 wcr_line_info.po_line_location_id,
4276 wcr_line_info.po_unit_price,
4277 wcr_line_info.currency_code,
4278 wcr_line_info.currency_conversion_type,
4279 wcr_line_info.currency_conversion_rate,
4280 wcr_line_info.currency_conversion_date,
4281 wcr_line_info.po_distribution_id,
4282 wcr_line_info.requisition_line_id,
4283 wcr_line_info.req_distribution_id,
4284 wcr_line_info.charge_account_id,
4285 NULL, --.substitute_unordered_code,
4286 NULL, --.receipt_exception_flag,
4287 NULL, --.accrual_status_code,
4288 'NOT INSPECTED' ,--.inspection_status_code,
4289 NULL, --.inspection_quality_code,
4290 wcr_line_info.destination_type_code,
4291 wcr_line_info.deliver_to_person_id,
4292 wcr_line_info.location_id,
4293 wcr_line_info.deliver_to_location_id,
4294 NULL, --.subinventory,
4295 NULL, --.locator_id,
4296 NULL, --.wip_entity_id,
4297 NULL, --.wip_line_id,
4298 NULL, --.department_code,
4299 NULL, --.wip_repetitive_schedule_id,
4300 NULL, --.wip_operation_seq_num,
4301 NULL, --.wip_resource_seq_num,
4302 NULL, --.bom_resource_id,
4303 wcr_line_info.shipment_num,
4304 wcr_line_info.freight_carrier_code,
4305 wcr_line_info.bill_of_lading,
4306 wcr_line_info.packing_slip,
4307 wcr_line_info.shipped_date,
4308 wcr_line_info.expected_receipt_date,
4309 NULL, --.actual_cost,
4310 NULL, --.transfer_cost,
4311 NULL, --.transportation_cost,
4312 NULL, --.transportation_account_id,
4313 NULL, --.num_of_containers,
4314 wcr_line_info.waybill_airbill_num,
4315 wcr_line_info.vendor_item_num,
4316 wcr_line_info.vendor_lot_num,
4317 NULL,--.rma_reference,
4318 wcr_line_info.comments,
4319 wcr_line_info.attribute_category,
4320 wcr_line_info.attribute1,
4321 wcr_line_info.attribute2,
4322 wcr_line_info.attribute3,
4323 wcr_line_info.attribute4,
4324 wcr_line_info.attribute5,
4325 wcr_line_info.attribute6,
4326 wcr_line_info.attribute7,
4327 wcr_line_info.attribute8,
4328 wcr_line_info.attribute9,
4329 wcr_line_info.attribute10,
4330 wcr_line_info.attribute11,
4331 wcr_line_info.attribute12,
4332 wcr_line_info.attribute13,
4333 wcr_line_info.attribute14,
4334 wcr_line_info.attribute15,
4335 NULL, --.ship_head_attribute_category,
4336 NULL, --.ship_head_attribute1,
4337 NULL, --.ship_head_attribute2,
4338 NULL, --.ship_head_attribute3,
4339 NULL, --.ship_head_attribute4,
4340 NULL, --.ship_head_attribute5,
4341 NULL, --.ship_head_attribute6,
4342 NULL, --.ship_head_attribute7,
4343 NULL, --.ship_head_attribute8,
4344 NULL, --.ship_head_attribute9,
4345 NULL, --.ship_head_attribute10,
4346 NULL, --.ship_head_attribute11,
4347 NULL, --.ship_head_attribute12,
4348 NULL, --.ship_head_attribute13,
4349 NULL, --.ship_head_attribute14,
4350 NULL, --.ship_head_attribute15,
4351 NULL, --.ship_line_attribute_category,
4352 NULL, --.ship_line_attribute1,
4353 NULL, --.ship_line_attribute2,
4354 NULL, --.ship_line_attribute3,
4355 NULL, --.ship_line_attribute4,
4356 NULL, --.ship_line_attribute5,
4357 NULL, --.ship_line_attribute6,
4358 NULL, --.ship_line_attribute7,
4359 NULL, --.ship_line_attribute8,
4360 NULL, --.ship_line_attribute9,
4361 NULL, --.ship_line_attribute10,
4362 NULL, --.ship_line_attribute11,
4363 NULL, --.ship_line_attribute12,
4364 NULL, --.ship_line_attribute13,
4365 NULL, --.ship_line_attribute14,
4366 NULL, --.ship_line_attribute15,
4367 wcr_line_info.ussgl_transaction_code,
4368 wcr_line_info.government_context,
4369 NULL, --.reason_id,
4370 wcr_line_info.destination_context,
4371 wcr_line_info.source_doc_quantity,
4372 wcr_line_info.source_doc_unit_of_measure,
4373 NULL, --.movement_id,
4374 NULL, --l_header_interface_id, --.header_interface_id,
4375 NULL, --.vendor_cum_shipped_qty,
4376 NULL, --.item_num,
4377 wcr_line_info.document_num,
4378 wcr_line_info.document_line_num,
4379 NULL, --.truck_num,
4380 NULL, --.ship_to_location_code,
4381 NULL, --.container_num,
4382 NULL, --.substitute_item_num,
4383 NULL, --.notice_unit_price,
4384 NULL, --.item_category,
4385 NULL, --.location_code,
4386 NULL, --.vendor_name,
4387 NULL, --.vendor_num,
4388 NULL, --.vendor_site_code,
4389 NULL, --.from_organization_code,
4390 NULL, --.to_organization_code,
4391 NULL, --.intransit_owning_org_code,
4392 NULL, --.routing_code,
4393 NULL, --.routing_step,
4394 NULL, --.release_num,
4395 wcr_line_info.document_shipment_line_num,
4396 wcr_line_info.document_distribution_num,
4397 NULL, --.deliver_to_person_name,
4398 NULL, --.deliver_to_location_code,
4399 NULL, --.use_mtl_lot,
4400 NULL, --.use_mtl_serial,
4401 NULL, --.LOCATOR,
4402 NULL, --.reason_name,
4403 NULL, --.validation_flag,
4404 NULL, --.substitute_item_id,
4405 NULL, --.quantity_shipped,
4406 NULL, --.quantity_invoiced,
4407 NULL, --.tax_name,
4408 NULL, --.tax_amount,
4409 NULL, --.req_num,
4410 NULL, --.req_line_num,
4411 NULL, --.req_distribution_num,
4412 NULL, --.wip_entity_name,
4413 NULL, --.wip_line_code,
4414 NULL, --.resource_code,
4415 NULL, --.shipment_line_status_code,
4416 NULL, --.barcode_label,
4417 NULL, --.country_of_origin_code,
4418 NULL, --.from_locator_id, --WMS Change
4419 NULL, --.qa_collection_id,
4420 NULL, --.oe_order_header_id,
4421 NULL, --.oe_order_line_id,
4422 NULL, --.customer_id,
4423 NULL, --.customer_site_id,
4424 NULL, --.customer_item_num,
4425 NULL, --.create_debit_memo_flag,
4426 NULL, --.put_away_rule_id,
4427 NULL, --.put_away_strategy_id,
4428 NULL, --.lpn_id,
4429 NULL, --.transfer_lpn_id,
4430 NULL, --.cost_group_id,
4431 NULL, --.mobile_txn,
4432 NULL, --.mmtt_temp_id,
4433 NULL, --.transfer_cost_group_id,
4434 NULL, --.secondary_quantity,
4435 NULL, --.secondary_unit_of_measure,
4436 NULL, --.secondary_uom_code,
4437 NULL, --.qc_grade,
4438 NULL, --.oe_order_num,
4439 NULL, --.oe_order_line_num,
4440 NULL, --.customer_account_number,
4441 NULL, --.customer_party_name,
4442 NULL, --.source_transaction_num,
4443 NULL, --.parent_source_transaction_num,
4444 NULL, --.parent_interface_txn_id,
4445 NULL, --.customer_item_id,
4446 NULL, --.interface_available_qty,
4447 NULL, --.interface_transaction_qty,
4448 NULL, --.from_locator,
4449 NULL, --.lpn_group_id,
4450 NULL, --.order_transaction_id,
4451 NULL, --.license_plate_number,
4452 NULL, --.transfer_license_plate_number,
4453 wcr_line_info.amount,
4454 wcr_line_info.job_id,
4455 NULL, --.project_id,
4456 NULL, --.task_id,
4457 NULL, --.asn_attach_id,
4458 NULL, --.timecard_id,
4459 NULL, --.timecard_ovn,
4460 NULL, --.interface_available_amt,
4461 NULL, --.interface_transaction_amt
4462 wcr_line_info.org_id, --<R12 MOAC>
4463 wcr_line_info.matching_basis,
4464 NULL, --wcr_line_info.amount_shipped, --amount_shipped
4465 wcr_line_info.requested_amount,
4466 wcr_line_info.material_stored_amount
4467
4468 );
4469 IF (g_asn_debug = 'Y') THEN
4470 debug_log(FND_LOG.LEVEL_STATEMENT,
4471 l_api_name,'After insert ' );
4472 END IF;
4473 END IF; --}
4474
4475 end loop; --}
4476 If get_dist_info%isopen then
4477 Close get_dist_info;
4478 end if;
4479
4480 IF (g_asn_debug = 'Y') THEN
4481 debug_log(FND_LOG.LEVEL_STATEMENT,
4482 l_api_name,'Going to fetch the next shipment line if it exists ' );
4483 END IF;
4484 end loop; --}
4485
4486 If get_wcr_info%isopen then
4487 Close get_wcr_info;
4488 end if;
4489
4490 GenReceiptNum(l_shipment_header_id,l_receipt_num);
4491 IF (g_asn_debug = 'Y') THEN
4492 debug_log(FND_LOG.LEVEL_STATEMENT,
4493 l_api_name,'l_receipt_num '||l_receipt_num);
4494 END IF;
4495
4496 update rcv_shipment_headers
4497 set receipt_num= l_receipt_num,
4498 last_update_date = sysdate,
4499 last_updated_by = fnd_global.user_id,
4500 last_update_login = fnd_global.login_id
4501 where shipment_header_id = l_shipment_header_id;
4502 IF (g_asn_debug = 'Y') THEN
4503 debug_log(FND_LOG.LEVEL_STATEMENT,
4504 l_api_name,'Leave insert_into_rti ' );
4505 END IF;
4506
4507 x_resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
4508
4509 return;
4510
4511 exception
4512 when others then
4513 IF (g_asn_debug = 'Y') THEN
4514 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4515 l_api_name,'Exception in insert_into_rti '
4516 || l_progress);
4517 END IF;
4518 raise;
4519
4520 END insert_into_rti;
4521
4522 procedure Launch_RTP_Immediate
4523 (p_itemtype in varchar2,
4524 p_itemkey in varchar2,
4525 p_actid in number,
4526 p_funcmode in varchar2,
4527 x_resultout out NOCOPY varchar2) IS
4528
4529 l_progress varchar2(300);
4530 l_result_id number;
4531 l_group_id number;
4532 l_api_name varchar2(50) := p_itemkey || ' Launch_RTP_Immediate';
4533 begin
4534 l_progress := 'POS_WCAPPROVE_PVT.Launch_RTP_Immediate: 01.';
4535 IF (g_asn_debug = 'Y') THEN
4536 debug_log(FND_LOG.LEVEL_STATEMENT,
4537 l_api_name,
4538 'Enter in Launch_RTP_Immediate '
4539 || l_progress);
4540 END IF;
4541
4542 if (p_funcmode <> wf_engine.eng_run) then
4543 x_resultout := wf_engine.eng_null;
4544 return;
4545 end if;
4546
4547 l_group_id := wf_engine.GetItemAttrNumber
4548 (itemtype => p_itemtype,
4549 itemkey => p_itemkey,
4550 aname => 'INTERFACE_GROUP_ID');
4551
4552 IF (g_asn_debug = 'Y') THEN
4553 debug_log(FND_LOG.LEVEL_STATEMENT,
4554 l_api_name,
4555 'l_group_id '
4556 || l_group_id);
4557 END IF;
4558 l_result_id :=
4559 fnd_request.submit_request('PO',
4560 'RVCTP',
4561 null,
4562 null,
4563 false,
4564 'IMMEDIATE',
4565 --'BATCH',
4566 l_group_id,
4567 --fnd_char.local_chr(0),
4568 0,
4569 NULL,
4570 NULL,
4571 NULL,
4572 NULL,
4573 NULL, NULL,
4574 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4575 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4576 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4577 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4578 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4579
4580 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4581 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4582 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4583 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4584
4585 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4586 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4587 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
4588 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
4589
4590 IF (g_asn_debug = 'Y') THEN
4591 debug_log(FND_LOG.LEVEL_STATEMENT,
4592 l_api_name, 'l_result_id '
4593 || l_result_id);
4594 END IF;
4595 if (l_result_id <= 0 or l_result_id is null) then
4596
4597
4598 UPDATE rcv_transactions_interface
4599 set transaction_status_code = 'ERROR'
4600 where group_id = l_group_id;
4601
4602
4603
4604 end if;
4605
4606 IF (g_asn_debug = 'Y') THEN
4607 debug_log(FND_LOG.LEVEL_STATEMENT,
4608 l_api_name, 'Leave RTP launch ' );
4609 END IF;
4610 x_resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
4611 return;
4612
4613 exception
4614 when others then
4615 IF (g_asn_debug = 'Y') THEN
4616 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4617 l_api_name,'Exception in Launch_RTP_Immediate '
4618 || l_progress);
4619 END IF;
4620 raise;
4621
4622 END Launch_RTP_Immediate;
4623
4624 procedure CloseOldNotif
4625 (
4626 p_itemtype in varchar2,
4627 p_itemkey in varchar2) IS
4628 -- pragma AUTONOMOUS_TRANSACTION;
4629 l_api_name varchar2(50) := p_itemkey || ' CloseOldNotif';
4630 l_progress varchar2(300);
4631 begin
4632
4633 l_progress := 'POS_WCAPPROVE_PVT.CloseOldNotif: 01';
4634
4635 IF (g_asn_debug = 'Y') THEN
4636 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4637 l_api_name,'Enter in CloseOldNotif ' || l_progress);
4638 END IF;
4639 update wf_notifications set status = 'CLOSED'
4640 where notification_id in (
4641 select ias.notification_id
4642 from wf_item_activity_statuses ias,
4643 wf_notifications ntf
4644 where ias.item_type = p_itemtype
4645 and ias.item_key = p_itemkey
4646 and ntf.notification_id = ias.notification_id);
4647
4648
4649 exception
4650 when others then
4651 IF (g_asn_debug = 'Y') THEN
4652 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4653 l_api_name,'Exception in CloseOldNotif ' || l_progress);
4654 END IF;
4655 raise;
4656 end CloseOldNotif;
4657
4658 procedure UpdateWorkflowInfo
4659 (
4660 p_itemtype in varchar2,
4661 p_itemkey in varchar2,
4662 p_shipment_header_id in varchar2) IS
4663 -- pragma AUTONOMOUS_TRANSACTION;
4664
4665 l_api_name varchar2(50) := p_itemkey || ' UpdateWorkflowInfo';
4666 l_progress varchar2(300);
4667 begin
4668
4669 l_progress := 'POS_WCAPPROVE_PVT.UpdateWorkflowInfo: 01';
4670
4671 IF (g_asn_debug = 'Y') THEN
4672 debug_log(FND_LOG.LEVEL_STATEMENT,
4673 l_api_name,'Enter in UpdateWorkflowInfo ' || l_progress);
4674 debug_log(FND_LOG.LEVEL_STATEMENT,
4675 l_api_name,'p_itemtype ' || p_itemtype);
4676 debug_log(FND_LOG.LEVEL_STATEMENT,
4677 l_api_name,'p_itemkey ' || p_itemkey);
4678 debug_log(FND_LOG.LEVEL_STATEMENT,
4679 l_api_name,'p_shipment_header_id ' || p_shipment_header_id);
4680 END IF;
4681
4682 UPDATE rcv_shipment_headers
4683 SET WF_ITEM_TYPE = p_itemtype,
4684 WF_ITEM_KEY = p_itemkey,
4685 last_updated_by = fnd_global.user_id,
4686 last_update_login = fnd_global.login_id,
4687 last_update_date = sysdate
4688 WHERE shipment_header_id = p_shipment_header_id;
4689
4690
4691
4692 debug_log(FND_LOG.LEVEL_STATEMENT,
4693 l_api_name,'do i see this now? ' || p_shipment_header_id);
4694 exception
4695 when others then
4696 IF (g_asn_debug = 'Y') THEN
4697 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4698 l_api_name,'Exception in UpdateWorkflowInfo ' || l_progress);
4699 END IF;
4700 raise;
4701 end UpdateWorkflowInfo;
4702
4703 PROCEDURE Upd_ActionHistory_Submit (p_object_id IN NUMBER,
4704 p_object_type_code IN VARCHAR2,
4705 p_employee_id IN NUMBER,
4706 p_sequence_num IN NUMBER,
4707 p_action_code IN VARCHAR2,
4708 p_user_id IN NUMBER,
4709 p_login_id IN NUMBER)
4710 IS
4711 -- pragma AUTONOMOUS_TRANSACTION;
4712 l_api_name varchar2(50) := ' Upd_ActionHistory_Submit';
4713 l_progress varchar2(280);
4714 begin
4715
4716 l_progress := 'POS_WCAPPROVE_PVT.Upd_ActionHistory_Submit: 01';
4717 IF (g_asn_debug = 'Y') THEN
4718 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4719 l_api_name,'Enter in Upd_ActionHistory_Submit '
4720 || l_progress);
4721 END IF;
4722
4723 UPDATE PO_ACTION_HISTORY
4724 set object_id = p_object_id,
4725 object_type_code = p_object_type_code,
4726 sequence_num = p_sequence_num,
4727 last_update_date = sysdate,
4728 last_updated_by = p_user_id,
4729 creation_date = sysdate,
4730 created_by = p_user_id,
4731 action_code = p_action_code,
4732 action_date = sysdate,
4733 employee_id = p_employee_id,
4734 last_update_login = p_login_id,
4735 request_id = 0,
4736 program_application_id = 0,
4737 program_id = 0,
4738 program_update_date = '',
4739 offline_code = ''
4740 WHERE
4741 object_id= p_object_id and
4742 object_type_code = p_object_type_code and
4743 sequence_num = p_sequence_num;
4744 exception
4745 when others then
4746 IF (g_asn_debug = 'Y') THEN
4747 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4748 l_api_name,'Exception in Upd_ActionHistory_Submit ' || l_progress);
4749 END IF;
4750 raise;
4751 end Upd_ActionHistory_Submit;
4752
4753
4754 PROCEDURE get_multiorg_context(p_document_id number,
4755 x_orgid IN OUT NOCOPY number) IS
4756 cursor get_po_orgid is
4757 select org_id
4758 from po_headers_all
4759 where po_header_id = p_document_id;
4760
4761 l_progress varchar2(300);
4762 l_api_name varchar2(50) := ' get_multiorg_context';
4763 begin
4764 l_progress := 'POS_WCAPPROVE_PVT.get_multiorg_context: 01';
4765 IF (g_asn_debug = 'Y') THEN
4766 debug_log(FND_LOG.LEVEL_STATEMENT,
4767 l_api_name,'Enter in get_multiorg_context '
4768 || l_progress);
4769 END IF;
4770
4771 OPEN get_po_orgid;
4772 FETCH get_po_orgid into x_orgid;
4773 CLOSE get_po_orgid;
4774 exception
4775 when others then
4776 IF (g_asn_debug = 'Y') THEN
4777 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4778 l_api_name,'Exception in get_multiorg_context ' || l_progress);
4779 END IF;
4780 raise;
4781 end get_multiorg_context;
4782
4783
4784 FUNCTION Get_Approver_Name(p_approver_id IN NUMBER)
4785 RETURN VARCHAR2 IS
4786 l_value VARCHAR2(1000) := '';
4787 begin
4788 select distinct full_name
4789 into l_value
4790 from per_all_people_f hre
4791 where hre.person_id = p_approver_id
4792 and trunc(sysdate) BETWEEN effective_start_date
4793 and effective_end_date;
4794
4795 if l_value is null then
4796 l_value := null;
4797 end if;
4798 return l_value;
4799 exception
4800 when others then
4801 return null;
4802
4803 end Get_Approver_Name;
4804
4805 FUNCTION Get_PoHeaderId(p_shipment_header_id IN NUMBER)
4806 RETURN NUMBER IS
4807 l_progress varchar2(500);
4808 l_po_header_id number;
4809 l_api_name varchar2(50) := 'get_PoHeaderId';
4810 begin
4811 l_progress := 'POS_WCAPPROVE_PVT.get_PoHeaderId: 01';
4812 IF (g_asn_debug = 'Y') THEN
4813 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4814 l_api_name,'Enter in get_PoHeaderId ' || l_progress);
4815 END IF;
4816
4817 select max(po_header_id)
4818 into l_po_header_id
4819 from rcv_shipment_lines
4820 where shipment_header_id = p_shipment_header_id;
4821
4822 return l_po_header_id;
4823
4824 exception
4825 when others then
4826 IF (g_asn_debug = 'Y') THEN
4827 debug_log(FND_LOG.LEVEL_UNEXPECTED,
4828 l_api_name,'Exception in get_PoHeaderId ' || l_progress);
4829 END IF;
4830 raise;
4831
4832 end Get_PoHeaderId;
4833
4834 PROCEDURE GenReceiptNum(
4835 p_shipment_header_id IN number,
4836 x_receipt_num IN OUT nocopy Varchar2
4837 ) IS
4838 PRAGMA AUTONOMOUS_TRANSACTION;
4839 l_count number;
4840 l_ship_to_org_id number;
4841 l_api_name varchar2(50) := ' GenReceiptNum';
4842 BEGIN
4843
4844 select ship_to_org_id
4845 into l_ship_to_org_id
4846 from rcv_shipment_headers
4847 where shipment_header_id=p_shipment_header_id;
4848
4849 IF (g_asn_debug = 'Y') THEN
4850 debug_log(FND_LOG.LEVEL_STATEMENT,
4851 l_api_name,'l_ship_to_org_id '
4852 ||l_ship_to_org_id);
4853 END IF;
4854
4855 BEGIN
4856 SELECT (next_receipt_num + 1)
4857 INTO x_receipt_num
4858 FROM rcv_parameters
4859 WHERE organization_id = l_ship_to_org_id
4860 FOR UPDATE OF next_receipt_num;
4861
4862 LOOP
4863 SELECT COUNT(*)
4864 INTO l_count
4865 FROM rcv_shipment_headers
4866 WHERE receipt_num = x_receipt_num
4867 AND ship_to_org_id = l_ship_to_org_id;
4868
4869 IF l_count = 0 THEN
4870 UPDATE rcv_parameters
4871 SET next_receipt_num = x_receipt_num
4872 WHERE organization_id = l_ship_to_org_id;
4873
4874 EXIT;
4875 ELSE
4876 x_receipt_num := TO_CHAR(TO_NUMBER(x_receipt_num) + 1);
4877 END IF;
4878 END LOOP;
4879
4880 COMMIT;
4881 EXCEPTION
4882 WHEN OTHERS THEN
4883 ROLLBACK;
4884 END;
4885 End GenReceiptNum;
4886
4887
4888 FUNCTION GET_PAY_ITEM_PROGRESS (p_wc_id IN NUMBER,
4889 p_wc_stage IN VARCHAR2)
4890 RETURN NUMBER
4891 IS
4892
4893 l_return_status varchar2(1);
4894 l_return_msg varchar2(2000);
4895 l_progress number;
4896 begin
4897
4898 POS_WC_CREATE_UPDATE_PVT.GET_PAY_ITEM_PROGRESS
4899 (p_wc_id,
4900 p_wc_stage,
4901 l_progress,
4902 l_return_status,
4903 l_return_msg);
4904
4905 if (l_return_status = FND_API.G_RET_STS_SUCCESS ) then
4906 return l_progress;
4907 else
4908 /* Some error. So return -999 */
4909 l_progress := -999;
4910 return l_progress;
4911 end if;
4912
4913 END GET_PAY_ITEM_PROGRESS;
4914
4915 FUNCTION GET_AWARD_NUM (p_wc_id IN NUMBER)
4916 RETURN VARCHAR2 IS
4917 l_count number;
4918 l_award_number gms_awards_all.award_number%type;
4919 begin
4920
4921 select count(*)
4922 into l_count
4923 from gms_awards_all awd,
4924 gms_award_distributions adl,
4925 po_distributions_all pod
4926 where adl.award_id = awd.award_id
4927 and adl.adl_line_num = 1
4928 and adl.po_distribution_id =pod.po_distribution_id
4929 and adl.award_set_id = pod.award_id
4930 and pod.po_distribution_id = p_wc_id;
4931
4932 If (l_count = 0) then
4933 return to_char(null);
4934 else
4935 select awd.award_number
4936 into l_award_number
4937 from gms_awards_all awd,
4938 gms_award_distributions adl,
4939 po_distributions_all pod
4940 where adl.award_id = awd.award_id
4941 and adl.adl_line_num = 1
4942 and adl.po_distribution_id =pod.po_distribution_id
4943 and adl.award_set_id = pod.award_id
4944 and pod.po_header_id = p_wc_id;
4945
4946 return l_award_number;
4947 end if;
4948 end GET_AWARD_NUM;
4949
4950
4951 FUNCTION GET_DELIVER_TO_LOCATION (p_wc_id IN NUMBER)
4952 RETURN VARCHAR2 IS
4953 l_count number;
4954 l_deliver_to_location hr_locations_all_tl.location_code%type;
4955 begin
4956
4957 select count(*)
4958 into l_count
4959 from hr_locations_all_tl hl,
4960 po_distributions_all pod
4961 where pod.deliver_to_location_id = hl.location_id and
4962 hl.language (+) =userenv('LANG') and
4963 pod.po_distribution_id = p_wc_id;
4964
4965
4966 If (l_count = 0) then
4967 return to_char(null);
4968 else
4969 select hl.location_code
4970 into l_deliver_to_location
4971 from hr_locations_all_tl hl,
4972 po_distributions_all pod
4973 where pod.deliver_to_location_id = hl.location_id and
4974 hl.language (+) =userenv('LANG') and
4975 pod.po_distribution_id = p_wc_id;
4976
4977
4978 return l_deliver_to_location;
4979 end if;
4980 end GET_DELIVER_TO_LOCATION;
4981
4982 FUNCTION GET_ORDERED_AMOUNT (p_wc_id IN NUMBER)
4983 RETURN number IS
4984 l_count number;
4985 l_amount po_line_locations_all.amount%type;
4986 begin
4987
4988 select count(*)
4989 into l_count
4990 from po_line_locations_all poll
4991 where poll.line_location_id = p_wc_id
4992 and nvl(poll.matching_basis,'QUANTITY')='AMOUNT';
4993
4994
4995 If (l_count = 0) then
4996 return to_number(null);
4997 else
4998
4999 select poll.amount
5000 into l_amount
5001 from po_line_locations_all poll
5002 where poll.line_location_id = p_wc_id
5003 and nvl(poll.matching_basis,'QUANTITY')='AMOUNT';
5004
5005
5006 return l_amount;
5007 end if;
5008 end GET_ORDERED_AMOUNT;
5009
5010
5011 FUNCTION GET_ORDERED_QUANTITY (p_wc_id IN NUMBER)
5012 RETURN number IS
5013 l_count number;
5014 l_quantity po_line_locations_all.quantity%type;
5015 begin
5016
5017 select count(*)
5018 into l_count
5019 from po_line_locations_all poll
5020 where poll.line_location_id = p_wc_id
5021 and nvl(poll.matching_basis,'QUANTITY')='QUANTITY';
5022
5023
5024 If (l_count = 0) then
5025 return to_number(null);
5026 else
5027
5028 select poll.quantity
5029 into l_quantity
5030 from po_line_locations_all poll
5031 where poll.line_location_id = p_wc_id
5032 and nvl(poll.matching_basis,'QUANTITY')='QUANTITY';
5033
5034
5035 return l_quantity;
5036 end if;
5037 end GET_ORDERED_QUANTITY;
5038
5039
5040 FUNCTION GET_PROJECT_NAME (p_wc_id IN NUMBER)
5041 RETURN VARCHAR2 IS
5042 l_count number;
5043 l_project_name pa_projects_all.name%type;
5044 begin
5045
5046 select count(*)
5047 into l_count
5048 from pa_projects_all pa,
5049 po_distributions_all pod
5050 where pod.project_id = pa.project_id and
5051 pod.po_distribution_id = p_wc_id;
5052
5053
5054 If (l_count = 0) then
5055 return to_char(null);
5056 else
5057
5058 select pa.name
5059 into l_project_name
5060 from pa_projects_all pa,
5061 po_distributions_all pod
5062 where pod.project_id = pa.project_id and
5063 pod.po_distribution_id = p_wc_id;
5064
5065
5066 return l_project_name;
5067 end if;
5068 end GET_PROJECT_NAME;
5069
5070 FUNCTION GET_TASK_NAME (p_wc_id IN NUMBER)
5071 RETURN VARCHAR2 IS
5072 l_count number;
5073 l_task_name pa_tasks.task_name%type;
5074 begin
5075
5076 select count(*)
5077 into l_count
5078 from pa_tasks pa,
5079 po_distributions_all pod
5080 where pod.task_id = pa.task_id and
5081 pod.po_distribution_id = p_wc_id;
5082
5083
5084 If (l_count = 0) then
5085 return to_char(null);
5086 else
5087
5088 select pa.task_name
5089 into l_task_name
5090 from pa_tasks pa,
5091 po_distributions_all pod
5092 where pod.task_id = pa.task_id and
5093 pod.po_distribution_id = p_wc_id;
5094
5095 return l_task_name;
5096 end if;
5097 end GET_TASK_NAME;
5098
5099 FUNCTION GET_CHARGE_ACCOUNT (p_wc_id IN NUMBER)
5100 RETURN VARCHAR2 IS
5101 l_count number;
5102 l_segments GL_CODE_COMBINATIONS_KFV.concatenated_segments%type;
5103 begin
5104
5105 select count(*)
5106 into l_count
5107 from gl_code_combinations_kfv glc,
5108 po_distributions_all pod
5109 where pod.code_combination_id = glc.code_combination_id and
5110 pod.po_distribution_id = p_wc_id;
5111
5112
5113 If (l_count = 0) then
5114 return to_char(null);
5115 else
5116
5117 select glc.concatenated_segments
5118 into l_segments
5119 from gl_code_combinations_kfv glc,
5120 po_distributions_all pod
5121 where pod.code_combination_id = glc.code_combination_id and
5122 pod.po_distribution_id = p_wc_id;
5123
5124 return l_segments;
5125 end if;
5126 end GET_CHARGE_ACCOUNT;
5127
5128 FUNCTION GET_EXPENDITURE_ORG (p_wc_id IN NUMBER)
5129 RETURN VARCHAR2 IS
5130 l_count number;
5131 l_org_name org_organization_definitions.organization_name%type;
5132 begin
5133
5134 select count(*)
5135 into l_count
5136 from org_organization_definitions ood,
5137 po_distributions_all pod
5138 where pod.expenditure_organization_id = ood.organization_id
5139 and pod.po_distribution_id = p_wc_id;
5140
5141
5142 If (l_count = 0) then
5143 return to_char(null);
5144 else
5145
5146 select ood.organization_name
5147 into l_org_name
5148 from org_organization_definitions ood,
5149 po_distributions_all pod
5150 where pod.expenditure_organization_id = ood.organization_id
5151 and pod.po_distribution_id = p_wc_id;
5152
5153 return l_org_name;
5154 end if;
5155 end GET_EXPENDITURE_ORG;
5156
5157 END POS_WCAPPROVE_PVT;