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