4 -- Read the profile option that enables/disables the debug log
1 PACKAGE BODY PO_REQAPPROVAL_LAUNCH AS
2 /* $Header: POXWPA5B.pls 120.7.12020000.2 2013/02/10 12:36:39 vegajula ship $ */
3
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7
8 /*=======================================================================+
9 | FILENAME
10 | xxx.sql
11 |
12 | DESCRIPTION
13 | PL/SQL body for package: PO_REQAPPROVAL_LAUNCH
14 |
15 | NOTES Ben Chihaoui Created 6/15/97
16 | MODIFIED (MM/DD/YY)
17 *=======================================================================*/
18
19 -- The following are local/Private procedure that support the workflow APIs:
20
21 PROCEDURE LaunchCreatePOWF(itemtype in varchar2, itemkey in varchar2);
22 --
23
24 PROCEDURE CreateWFInstance( ItemType varchar2,
25 ItemKey varchar2,
26 p_requisition_header_id number,
27 p_emergency_po_num varchar2,
28 p_is_federal varchar2); -- Bug 14314684
29
30 /****************************************************************************/
31
32
33 procedure Launch_CreatePO_WF( itemtype in varchar2,
34 itemkey in varchar2,
38
35 actid in number,
36 funcmode in varchar2,
37 resultout out NOCOPY varchar2 ) is
39 x_progress varchar2(100);
40
41 l_doc_string varchar2(200);
42 l_preparer_user_name varchar2(100);
43
44 BEGIN
45
46 -- x_progress := 'PO_REQAPPROVAL_LAUNCH.Launch_CreatePO_WF: 01';
47 -- /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
48
49
50 -- Do nothing in cancel or timeout mode
51 --
52 if (funcmode <> wf_engine.eng_run) then
53
54 resultout := wf_engine.eng_null;
55 return;
56
57 end if;
58
59
60 LaunchCreatePOWF(itemtype, itemkey);
61
62 --
63 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
64 --
65
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
70 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
71 WF_CORE.context('PO_REQAPPROVAL_LAUNCH' , 'Launch_CreatePO_WF', itemtype, itemkey,x_progress);
72 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_LAUNCH.LAUNCH_CREATEPO_WF');
73 raise;
74
75 END Launch_CreatePO_WF;
76
77
78 /*****************************************************************************
79 * The following are local/Private procedure that support the workflow APIs:
80 *****************************************************************************/
81
82 PROCEDURE LaunchCreatePOWF(itemtype in varchar2, itemkey in varchar2) is
83
84 l_requisition_header_id NUMBER;
85 l_emergency_po_num VARCHAR2(20);
86 l_is_federal VARCHAR2(2);
87
88 x_progress varchar2(100);
89
90 BEGIN
91
92 x_progress := 'PO_REQAPPROVAL_LAUNCH.LaunchCreatePOWF:01';
93 IF (g_po_wf_debug = 'Y') THEN
94 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
95 END IF;
96
97
98 l_requisition_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
99 itemkey => itemkey,
100 aname => 'DOCUMENT_ID');
101
102 l_is_federal := wf_engine.GetItemAttrText (itemtype => itemtype,
103 itemkey => itemkey,
104 aname => 'FEDERAL_FLAG');
105
106 /* This is used for integration with Web Requisitions. Web requisition has
107 ** a feature that allows users to get PO number, before the PO is created
108 ** This can happen in emergency cases, where the vendor/provider of the
109 ** service will not perform the service unless they get a PO number. The
110 ** user will then get a PO#, create the requisition and submit it for
111 ** approval. If the requisition is approved and we get to this point, then
112 ** we pass this PO NUMBER to the CREATE_PO workflow, which will create a
113 ** STANDARD PO using this PO NUMBER as SEGMENT1.
114 */
115 l_emergency_po_num := wf_engine.GetItemAttrText (itemtype => itemtype,
116 itemkey => itemkey,
117 aname => 'EMERGENCY_PO_NUMBER');
118
119 CreateWFInstance( ItemType,
120 ItemKey,
121 l_requisition_header_id,
122 l_emergency_po_num,
123 l_is_federal); -- Bug 14314684 Passing additional parameter to
124 -- if it is CLM enabled
125
126 EXCEPTION
127
128 WHEN OTHERS THEN
129 wf_core.context('PO_REQAPPROVAL_LAUNCH','LaunchCreatePOWF',
130 x_progress);
131 raise;
132
133
134 END LaunchCreatePOWF;
135
136
137 --
138 PROCEDURE CreateWFInstance( ItemType varchar2,
139 ItemKey varchar2,
140 p_requisition_header_id number,
141 p_emergency_po_num varchar2,
142 p_is_federal VARCHAR2) is -- Bug 14314684
143
144 x_progress varchar2(200);
145
146 l_ItemType varchar2(8);
147 l_ItemKey varchar2(80);
148 l_workflow_process varchar2(30);
149 l_dummy varchar2(38);
150 l_orgid number;
151 l_interface_source varchar2(30);
152
153 l_user_id number;
154 l_resp_id number;
155 l_appl_id number;
156
157 --bug 14807370:Store the session context before wf changed.
158 l_session_user_id number;
159 l_session_resp_id number;
160 l_session_resp_appl_id number;
161 l_session_org_id number;
162
163 cursor C1 is
164 select WF_CREATEDOC_ITEMTYPE,WF_CREATEDOC_PROCESS
165 from po_document_types
166 where DOCUMENT_TYPE_CODE= 'REQUISITION'
167 and DOCUMENT_SUBTYPE = 'PURCHASE';
168
169 BEGIN
170
171
172 /* Get the org context */
173 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
174 itemkey => itemkey,
175 aname => 'ORG_ID');
176
177 IF l_orgid is NOT NULL THEN
178
179 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
180
184 itemkey => itemkey,
181 END IF;
182
183 l_user_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
185 aname => 'USER_ID');
186
187 l_resp_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
188 itemkey => itemkey,
189 aname => 'RESPONSIBILITY_ID');
190
191 l_appl_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
192 itemkey => itemkey,
193 aname => 'APPLICATION_ID');
194
195 --bug 14807370:Store the session context before wf changed.
196
197 l_session_user_id := fnd_global.user_id;
198 l_session_resp_id := fnd_global.resp_id;
199 l_session_resp_appl_id := fnd_global.resp_appl_id;
200 l_session_org_id := fnd_global.org_id;
201
202
203 /* Since the call may be started from background engine (new seesion),
204 * need to ensure the fnd context is correct
205 */
206
207 if (l_user_id is not null and
208 l_resp_id is not null and
209 l_appl_id is not null )then
210
211 -- Bug 4290541
212 -- replaced apps init call with set doc mgr context
213
214 PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(itemtype, itemkey);
215 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
216 end if;
217
218
219 x_progress := 'PO_REQAPPROVAL_LAUNCH.CreateWFInstance:01';
220 IF (g_po_wf_debug = 'Y') THEN
221 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
222 END IF;
223
224 /* Create the ItemKey: Use the PO workflow sequence */
225 select to_char(PO_WF_ITEMKEY_S.nextval) into l_dummy from sys.dual;
226
227
228 OPEN C1;
229 FETCH C1 into l_ItemType, l_workflow_process;
230
231 IF C1%NOTFOUND THEN
232 close C1;
233 raise NO_DATA_FOUND;
234 END IF;
235
236 CLOSE C1;
237 -- l_ItemType:= 'CREATEPO';
238 -- l_workflow_process := 'OVERALL_AUTOCREATE_PROCESS';
239
240 l_ItemKey := to_char(p_requisition_header_id) || '-' || l_dummy;
241
242
243
244 x_progress := 'PO_REQAPPROVAL_LAUNCH.CreateWFInstance:02 ItemType=' ||
245 l_ItemType || ' ItemKey=' || l_ItemKey;
246 IF (g_po_wf_debug = 'Y') THEN
247 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
248 END IF;
249
250 /* Call the procedure that creates the WF instance and starts the process */
251
252 /* gtummala. 8/29/97.
253 * We also need to pass in the interface_source_code into the
254 * start_wf_process for the create document process.
255 */
256
257 l_interface_source := wf_engine.GetItemAttrNumber
258 (itemtype => itemtype,
259 itemkey => itemkey,
260 aname => 'INTERFACE_SOURCE_CODE');
261
262 --Bug 14314684 Passing additional parameter p_is_federal which is introduced in
263 --Create Doc Workflow Project
264 PO_AUTOCREATE_DOC.start_WF_process(l_ItemType,
265 l_ItemKey,
266 l_workflow_process,
267 p_requisition_header_id,
268 p_emergency_po_num,
269 l_interface_source,
270 l_orgid,
271 p_is_federal); -- Bug 14314684
272 --bug 14807370:after invoking workflow, set back the original session context using
273 fnd_global.apps_initialize(l_session_user_id, l_session_resp_id , l_session_resp_appl_id);
274 PO_MOAC_UTILS_PVT.set_org_context(l_session_org_id) ;
275
276
277 EXCEPTION
278
279 WHEN OTHERS THEN
280 wf_core.context('PO_REQAPPROVAL_LAUNCH','CreateWFInstance',
281 x_progress);
282 raise;
283
284
285 END CreateWFInstance;
286
287 -------------------------------------------------------------------------------
288 --Start of Comments
289 --Name: POREQ_SELECTOR
290 --Pre-reqs:
291 -- None.
292 --Modifies:
293 -- Application user id
294 -- Application responsibility id
295 -- Application application id
296 --Locks:
297 -- None.
298 --Function:
299 -- This procedure sets the correct application context when a process is
300 -- picked up by the workflow background engine. When called in
301 -- TEST_CTX mode it compares workflow attribute org id with the current
302 -- org id and workflow attributes user id, responsibility id and
303 -- application id with their corresponding profile values. It returns TRUE
304 -- if these values match and FALSE otherwise. When called in SET_CTX mode
305 -- it sets the correct apps context based on workflow parameters.
306 --Parameters:
307 --IN:
308 --p_itemtype
309 -- Specifies the itemtype of the workflow process
310 --p_itemkey
311 -- Specifies the itemkey of the workflow process
312 --p_actid
313 -- activity id passed by the workflow
314 --p_funcmode
315 -- Input values can be TEST_CTX or SET_CTX (RUN not implemented)
316 -- TEST_CTX to test if current context is correct
317 -- SET_CTX to set the correct context if current context is wrong
318 --IN OUT:
319 --p_x_result
320 -- For TEST_CTX a TRUE value means that the context is correct and
324 -- There is not script to test this procedure but the correct functioning
321 -- SET_CTX need not be called. A FALSE value means that current context
322 -- is incorrect and SET_CTX need to set correct context
323 --Testing:
325 -- may be tested by verifying from the debug_message in table po_wf_debug
326 -- that if at any time the workflow process gets started with a wrong
327 -- context then the selector is called in TEST_CTX and SET_CTX modes and
328 -- correct context is set.
329 --End of Comments
330 -------------------------------------------------------------------------------
331 PROCEDURE POREQ_SELECTOR ( -- Added as a part of bug 3540107
332 p_itemtype IN VARCHAR2,
333 p_itemkey IN VARCHAR2,
334 p_actid IN NUMBER,
335 p_funcmode IN VARCHAR2,
336 p_x_result IN OUT NOCOPY VARCHAR2
337 ) IS
338
339 -- Context setting revamp <declare variables start>
343 l_user_id_to_set NUMBER;
340 l_session_user_id NUMBER;
341 l_session_resp_id NUMBER;
342 l_responder_id NUMBER;
344 l_resp_id_to_set NUMBER;
345 l_appl_id_to_set NUMBER;
346 l_progress VARCHAR2(1000);
347 l_preserved_ctx VARCHAR2(5):= 'TRUE';
348 l_org_id NUMBER;
349 l_is_supplier_context VARCHAR2(10); -- Bug 6144768
350 -- Context setting revamp <declare variables end>
351
352 BEGIN
353 --Context setting revamp <start>
354
355 -- <debug start>
356 IF (g_po_wf_debug = 'Y') THEN
357 PO_WF_DEBUG_PKG.insert_debug(
358 itemtype => p_itemtype,
359 itemkey => p_itemkey,
360 x_progress => 'POREQ_SELECTOR called with mode: '
361 ||p_funcmode||' itemtype: '||p_itemtype
362 ||' itemkey: '||p_itemkey);
363
364 END IF;
365 -- <debug end>
366
367
368 l_org_id := PO_WF_UTIL_PKG.GetItemAttrNumber (
369 itemtype => p_itemtype,
370 itemkey => p_itemkey,
371 aname => 'ORG_ID');
372 -- Bug 6144768
373 l_is_supplier_context := PO_WF_UTIL_PKG.GetItemAttrText(
374 itemtype => p_itemtype,
375 itemkey => p_itemkey,
376 aname => 'IS_SUPPLIER_CONTEXT');
377 --Bug 5389914
378 --Fnd_Profile.Get('USER_ID',l_session_user_id);
379 --Fnd_Profile.Get('RESP_ID',l_session_resp_id);
380 l_session_user_id := fnd_global.user_id;
381 l_session_resp_id := fnd_global.resp_id;
382
383 IF (l_session_user_id = -1) THEN
384 l_session_user_id := NULL;
385 END IF;
386
387 IF (l_session_resp_id = -1) THEN
388 l_session_resp_id := NULL;
389 END IF;
390
391 l_responder_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
392 itemtype => p_itemtype,
393 itemkey => p_itemkey,
394 aname => 'RESPONDER_USER_ID');
395
396 --<debug start>
397 l_progress :='010 selector fn - sess_user_id:'||l_session_user_id
398 ||' ses_resp_id '||l_session_resp_id||' responder id '
399 ||l_responder_id;
400
401 IF (g_po_wf_debug = 'Y') THEN
402 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
403 END IF;
404 --<debug end>
405
406
407
408 IF (p_funcmode = 'TEST_CTX') THEN
409 -- Bug 6144768
410 IF (g_po_wf_debug = 'Y') THEN
411 PO_WF_DEBUG_PKG.insert_debug(itemtype => p_itemtype,
412 itemkey => p_itemkey,
413 x_progress => 'POREQ_SELECTOR: inside Test Ctx ');
414 PO_WF_DEBUG_PKG.insert_debug(itemtype => p_itemtype,
415 itemkey => p_itemkey,
416 x_progress => 'l_is_supplier_context: ' || l_is_supplier_context);
417 END IF;
418
419 --<Bug 6144768 Begin>
420 -- When Supplier responds from iSP then the responder should show
421 -- as supplier and also supplier acknowledgement notifications
422 -- should be available in the To-Do Notification full list.
423 IF l_is_supplier_context = 'Y' THEN
424 p_x_result := 'TRUE';
425 RETURN;
426 END IF;
427 --<Bug 6144768 End>
428 -- we cannot afford to run the wf without the session user, hence
429 -- always set the ctx if session user id is null.
430 if (l_session_user_id is null) then
431 p_x_result := 'NOTSET';
432 return;
436 p_x_result := 'FALSE';
433 else
434 if (l_responder_id is not null) then
435 if (l_responder_id <> l_session_user_id) then
437 return;
438 else
439 if (l_session_resp_id is Null) then
440 p_x_result := 'NOTSET';
441 return;
442 else
443 -- bug 5333226 : if the selector fn is called from a background ps/
444 -- notif mailer then force the session to use preparer's or responder
445 -- context. This is required since the mailer/bckgrnd ps carries the
446 -- context from the last wf processed and hence even if the context values
447 -- are present, they might not be correct.
448
449 if (wf_engine.preserved_context = TRUE) then
450 p_x_result := 'TRUE';
451 else
452 p_x_result:= 'NOTSET';
453 end if;
454
455 -- introduce an org context setting call here-
456 -- required in the case when the right resonder makes a response
457 -- from a NON-PO RESP.
458 IF l_org_id is NOT NULL THEN
459 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;
460 END IF;
461
462 return;
463 end if;
464 end if;
465 else
466 -- always setting the ctx at the start of the wf
467 p_x_result := 'NOTSET';
468 return;
469 end if;
470 end if; -- l_session_user_id is null
471
472 ELSIF (p_funcmode = 'SET_CTX') THEN
473 if l_responder_id is not null then
474 l_user_id_to_set := l_responder_id;
475 l_resp_id_to_set :=wf_engine.GetItemAttrNumber (
476 itemtype => p_itemtype,
477 itemkey => p_itemkey,
478 aname => 'RESPONDER_RESP_ID');
479 l_appl_id_to_set :=wf_engine.GetItemAttrNumber (
480 itemtype => p_itemtype,
481 itemkey => p_itemkey,
482 aname => 'RESPONDER_APPL_ID');
483 --<debug start>
484 l_progress := '020 selection fn responder id not null';
485 IF (g_po_wf_debug = 'Y') THEN
486 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
487 END IF;
488 --<debug end>
489
490 --<debug start>
491 l_progress :='030 selector fn : setting user id :'||l_responder_id
492 ||' resp id '||l_resp_id_to_set||' l_appl id '||l_appl_id_to_set;
493 IF (g_po_wf_debug = 'Y') THEN
494 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
495 END IF;
496 --<debug end>
497
498 else
499 l_user_id_to_set := wf_engine.GetItemAttrNumber (
500 itemtype => p_itemtype,
501 itemkey => p_itemkey,
502 aname => 'USER_ID');
503 l_resp_id_to_set :=wf_engine.GetItemAttrNumber (
504 itemtype => p_itemtype,
505 itemkey => p_itemkey,
506 aname => 'RESPONSIBILITY_ID');
507 l_appl_id_to_set :=wf_engine.GetItemAttrNumber (
508 itemtype => p_itemtype,
509 itemkey => p_itemkey,
510 aname => 'APPLICATION_ID');
511 --<debug start>
512 l_progress := '040 selector fn responder id null';
513 IF (g_po_wf_debug = 'Y') THEN
514 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
515 END IF;
516 --<debug end>
517
518 --<debug start>
519 l_progress := '050 selector fn : set user '||l_user_id_to_set||' resp id '
520 ||l_resp_id_to_set||' appl id '||l_appl_id_to_set;
521 IF (g_po_wf_debug = 'Y') THEN
522 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
523 END IF;
524 --<debug end>
525 end if;
526
530 -- this is required since the responsibility might have a different OU attached
527 fnd_global.apps_initialize(l_user_id_to_set, l_resp_id_to_set,l_appl_id_to_set);
528
529 -- obvious place to make such a call, since we are using an apps_initialize,
531 -- than what is required.
532
533 IF l_org_id is NOT NULL THEN
534 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;
535 END IF;
536
537
538 -- Need to set the sub ledger security also, requirement
539 -- comes from bug 3571038
540 igi_sls_context_pkg.set_sls_context;
541
542 END IF;
543 -- Context setting revamp <end>
544
545 EXCEPTION
546
547 WHEN OTHERS THEN
548
549 IF (g_po_wf_debug = 'Y') THEN
550 PO_WF_DEBUG_PKG.insert_debug(itemtype => p_itemtype,
551 itemkey => p_itemkey,
552 x_progress => 'Exception in Selector');
553 END IF;
554
555 WF_CORE.context('PO_REQAPPROVAL_LAUNCH',
556 'POREQ_SELECTOR',
557 p_itemtype,
558 p_itemkey,
559 p_actid,
560 p_funcmode);
561 RAISE;
562
563 END POREQ_SELECTOR;
564
565 end PO_REQAPPROVAL_LAUNCH;
[Home] [Help]