[Home] [Help]
PACKAGE BODY: APPS.OKL_REQ_WF
Source
1 PACKAGE BODY OKL_REQ_WF AS
2 /* $Header: OKLRRQWB.pls 120.2 2006/07/11 09:57:10 dkagrawa noship $ */
3
4 --------------------------------------------------------------------------
5 ---- Concurrent API to invoke the workflow for requesting approval of a Cure
6 ---- Currently not used as a concurrent process
7 ---- Instead The call is made through the OKL_VENDOR_REFUND_PVT.REFUND API
8 --- which also calls another Cure and Repurchase workflow
9 ---------------------------------------------------------------------------
10
11 PROCEDURE invoke_workflow(
12 errbuf OUT NOCOPY VARCHAR2,
13 retcode OUT NOCOPY NUMBER)
14
15 AS
16
17 lx_msg_count NUMBER ;
18 lx_msg_data VARCHAR2(2000);
19 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
20 l_api_version CONSTANT NUMBER := 1;
21 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
22
23
24 -- Get Report data
25 CURSOR get_request_csr
26 IS
27 SELECT cure_report_id
28 FROM OKL_CURE_REPORTS
29 WHERE approval_status='COMPLETE';
30
31
32 BEGIN
33 /* Processing Starts */
34
35 FOR i IN get_request_csr LOOP
36 raise_request_business_event(i.cure_report_id);
37 END LOOP;
38
39 /* Processing Ends */
40
41 EXCEPTION
42 WHEN OTHERS THEN
43 errbuf := substr(SQLERRM, 1, 200);
44 retcode := 1;
45 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'SQL ERROR : SQLCODE = ' || SQLCODE);
46 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, ' MESSAGE = ' || SQLERRM);
47 ROLLBACK;
48 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
49 END invoke_workflow;
50
51 -------------------------------------------------------------------------
52 -- PROCEDURE raise_request_business_event
53 ---------------------------------------------------------------------------
54 PROCEDURE raise_request_business_event (p_trans_id IN NUMBER)
55 AS
56 l_parameter_list wf_parameter_list_t;
57 l_key varchar2(240);
58 l_event_name varchar2(240) := 'oracle.apps.OKL.requestapproval';
59 l_seq NUMBER;
60
61 CURSOR OKL_key_csr IS
62 SELECT OKL_CURE_WF_S.nextval
63 FROM dual;
64
65 BEGIN
66 SAVEPOINT raise_request_business_event;
67
68 OPEN OKL_key_csr;
69 FETCH OKL_key_csr INTO l_seq;
70 CLOSE OKL_key_csr;
71
72 l_key := l_event_name ||l_seq ;
73
74 --'TRANS_ID' is the internal name of the attribute passed to WF
75 wf_event.AddParameterToList('TRANS_ID', p_trans_id,l_parameter_list);
76
80 ,p_parameters => l_parameter_list);
77 -- Raise Event
78 wf_event.raise(p_event_name => l_event_name
79 ,p_event_key => l_key
81
82 l_parameter_list.DELETE;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
87 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
88 FND_MSG_PUB.ADD;
89 ROLLBACK TO raise_request_business_event;
90 END raise_request_business_event;
91
92
93 -------------------------------------------------------------------------
94 -- PROCEDURE populate_notif_attributes
95 ---------------------------------------------------------------------------
96 PROCEDURE populate_notif_attributes(itemtype in varchar2,
97 itemkey in varchar2,
98 actid in number,
99 funcmode in varchar2,
100 resultout out nocopy varchar2) AS
101
102 l_dummy varchar(1) ;
103 l_trans_id NUMBER ;
104 l_return_status VARCHAR2(100);
105 l_api_version NUMBER := 1.0;
106 l_msg_count NUMBER;
107 l_msg_data VARCHAR2(2000);
108 l_obj_version NUMBER ;
109 l_report_number VARCHAR2(150) ;
110 l_vendor_id NUMBER;
111 l_report_date DATE;
112 l_vendor_contact_id NUMBER;
113 l_vendor_site_id NUMBER;
114 l_created_by VARCHAR2(80);
115 l_name VARCHAR2(80);
116 l_address VARCHAR2(2000);
117 l_contact VARCHAR2(20);
118
119 CURSOR report_dtls_csr (a_trans_id NUMBER)
120 IS
121 SELECT REPORT_NUMBER,REPORT_DATE,VENDOR_ID,VENDOR_CONTACT_ID,VENDOR_SITE_ID,CREATED_BY
122 FROM OKL_CURE_REPORTS
123 WHERE CURE_REPORT_ID= a_trans_id;
124
125 CURSOR vendor_csr (a_vendor_id NUMBER)
126 IS
127 SELECT VENDOR_NAME
128 FROM PO_VENDORS
129 WHERE VENDOR_ID = a_vendor_id;
130
131 CURSOR location_csr (a_vendor_id NUMBER, a_vendor_site_id NUMBER)
132 IS
133 SELECT ADDRESS_LINE1 || ' '||
134 ADDRESS_LINE2 || ' '||
135 ADDRESS_LINE3 || ' '||
136 CITY || ' '||
137 STATE || ' '||
138 ZIP || ' '||
139 PROVINCE || ' '||
140 COUNTRY AS Address
141 FROM PO_VENDOR_SITES_ALL
142 WHERE VENDOR_ID = a_vendor_id
143 AND VENDOR_SITE_ID = a_vendor_site_id;
144
145 CURSOR contact_csr (a_vendor_contact_id NUMBER)
146 IS
147 SELECT PREFIX || ' '||
148 FIRST_NAME || ' '||
149 LAST_NAME AS VENDOR_CONTACT
150 FROM PO_VENDOR_CONTACTS
151 WHERE VENDOR_CONTACT_ID = a_vendor_contact_id;
152
153 CURSOR notif_mgr_csr(a_trans_id NUMBER)
154 IS
155 SELECT b.user_name
156 FROM JTF_RS_RESOURCE_EXTNS a,
157 JTF_RS_RESOURCE_EXTNS b
158 WHERE b.source_id = a.source_mgr_id
159 AND a.user_id
160 IN
161 (SELECT created_by
162 FROM OKL_CURE_REPORTS
163 WHERE CURE_REPORT_ID = a_trans_id);
164
165 BEGIN
166 IF (funcmode = 'RUN') then
167 l_trans_id := wf_engine.GetItemAttrText(itemtype => itemtype,
168 itemkey => itemkey,
169 aname =>'TRANS_ID');
170
171
172 OPEN report_dtls_csr(l_trans_id);
173 FETCH report_dtls_csr INTO l_report_number,l_report_date,l_vendor_id,l_vendor_contact_id,l_vendor_site_id,l_created_by;
174 CLOSE report_dtls_csr;
175
176 OPEN vendor_csr(l_vendor_id);
177 FETCH vendor_csr INTO l_name;
178 CLOSE vendor_csr;
179
180 OPEN location_csr(l_vendor_id,l_vendor_site_id);
181 FETCH location_csr INTO l_address;
182 CLOSE location_csr;
183
184
185 OPEN contact_csr(l_vendor_contact_id);
186 FETCH contact_csr INTO l_contact;
187 CLOSE contact_csr;
188
189 OPEN notif_mgr_csr(l_trans_id);
190 FETCH notif_mgr_csr INTO l_created_by;
191 CLOSE notif_mgr_csr;
192
193
194 IF(l_return_status <> Fnd_Api.G_RET_STS_SUCCESS) THEN
195 raise G_EXCEPTION_HALT_VALIDATION;
196 END IF;
197
198 wf_engine.SetItemAttrDate (itemtype=> itemtype,
199 itemkey => itemkey,
200 aname => 'REPORT_DATE',
201 avalue => l_report_date);
202
203 wf_engine.SetItemAttrText (itemtype=> itemtype,
204 itemkey => itemkey,
205 aname => 'REPORT_NUMBER',
206 avalue => l_report_number);
207
208 wf_engine.SetItemAttrText (itemtype=> itemtype,
209 itemkey => itemkey,
210 aname => 'VENDOR_NAME',
211 avalue => l_name);
212
213 wf_engine.SetItemAttrText (itemtype=> itemtype,
214 itemkey => itemkey,
215 aname => 'VENDOR_LOCATION',
216 avalue => l_address);
217
218 wf_engine.SetItemAttrText (itemtype=> itemtype,
219 itemkey => itemkey,
220 aname => 'VENDOR_CONTACT',
221 avalue => l_contact);
222
226 avalue => l_created_by );
223 wf_engine.SetItemAttrText(itemtype => itemtype,
224 itemkey => itemkey,
225 aname => 'NOTIFICATION_MGRNAME',
227
228
229 resultout := 'COMPLETE:YES';
230 RETURN;
231 END IF;
232 --
233 -- CANCEL mode
234 --
235 if (funcmode = 'CANCEL') then
236 --
237 resultout := 'COMPLETE:';
238 return;
239 --
240 end if;
241 --
242 -- TIMEOUT mode
243 --
244 if (funcmode = 'TIMEOUT') then
245 --
246 resultout := 'COMPLETE:';
247 return;
248 --
249 end if;
250
251 EXCEPTION
252 when others then
253 --resultout := wf_engine.eng_completed ||':'||wf_no;
254 wf_core.context('OKL_CO_WF','populate_notif_attributes',itemtype,
255 itemkey,to_char(actid),funcmode);
256 raise;
257 END populate_notif_attributes;
258
259 ---------------------------------------------------------------------------
260 -- PROCEDURE Request_Rejected
261 ---------------------------------------------------------------------------
262
263 PROCEDURE Request_Rejected(itemtype in varchar2,
264 itemkey in varchar2,
265 actid in number,
266 funcmode in varchar2,
267 resultout out nocopy varchar2)
268 IS
269 l_dummy varchar(1) ;
270 l_trans_id NUMBER ;
271 l_return_status VARCHAR2(100);
272 l_api_version NUMBER := 1.0;
273 l_msg_count NUMBER;
274 l_msg_data VARCHAR2(2000);
275 l_obj_version NUMBER ;
276 l_report VARCHAR2(150) ;
277 l_vendor_id NUMBER;
278 l_date DATE;
279
280
281 --A PL/SQl table type
282 l_crtv_rec OKL_crt_pvt.crtv_rec_type;
283 lx_crtv_rec OKL_crt_pvt.crtv_rec_type;
284
285
286 CURSOR approval_csr (a_trans_id NUMBER)
287 IS
288 SELECT REPORT_NUMBER,REPORT_DATE,VENDOR_ID,OBJECT_VERSION_NUMBER
289 FROM OKL_CURE_REPORTS
290 WHERE CURE_REPORT_ID= a_trans_id;
291
292
293 BEGIN
294
295 IF (funcmode = 'RUN') then
296 l_trans_id := wf_engine.GetItemAttrText(itemtype => itemtype,
297 itemkey => itemkey,
298 aname =>'TRANS_ID');
299
300 OPEN approval_csr(l_trans_id);
301 FETCH approval_csr INTO l_report,l_date,l_vendor_id,l_obj_version;
302 CLOSE approval_csr;
303
304 -- This will set the status
305 l_crtv_rec.APPROVAL_STATUS := 'REJECTED';
306 l_crtv_rec.CURE_REPORT_ID := l_trans_id;
307 l_crtv_rec.OBJECT_VERSION_NUMBER := l_obj_version;
308 l_crtv_rec.REPORT_NUMBER := l_report ;
309 l_crtv_rec.VENDOR_ID := l_vendor_id ;
310 l_crtv_rec.REPORT_DATE := l_date ;
311
312 --Now inserting the populated table of records above
313 OKL_cure_reports_pub.update_cure_reports(
314 p_api_version => l_api_version
315 ,p_init_msg_list => fnd_api.g_false
316 ,x_return_status => l_return_status
317 ,x_msg_count => l_msg_count
318 ,x_msg_data => l_msg_data
319 ,p_crtv_rec => l_crtv_rec
320 ,x_crtv_rec => lx_crtv_rec);
321
322
323
324 resultout := 'COMPLETE:YES';
325 RETURN;
326 END IF;
327
328 IF (funcmode = 'CANCEL') then
329 resultout := 'COMPLETE:NO';
330 END IF;
331
332 -- TIMEOUT mode
333 IF (funcmode = 'TIMEOUT') then
334 resultout := 'COMPLETE:YES';
335 return ;
336 END IF;
337
338 EXCEPTION
339 when others then
340 wf_core.context('OKL_REQ_WF',' Request_Rejected ',
341 itemtype,
342 itemkey,
343 to_char(actid),
344 funcmode);
345 RAISE;
346 END Request_Rejected;
347
348 ---------------------------------------------------------------------------
349 -- PROCEDURE Request_Approved
350 ---------------------------------------------------------------------------
351
352 PROCEDURE Request_Approved(itemtype in varchar2,
353 itemkey in varchar2,
354 actid in number,
355 funcmode in varchar2,
356 resultout out nocopy varchar2)
357 IS
358 l_dummy varchar(1) ;
359 l_trans_id NUMBER ;
360 l_return_status VARCHAR2(100);
361 l_api_version NUMBER := 1.0;
362 l_msg_count NUMBER;
363 l_msg_data VARCHAR2(2000);
364 l_obj_version NUMBER ;
365 l_report VARCHAR2(150) ;
366 l_vendor_id NUMBER;
367 l_date DATE;
368
369
370 --A PL/SQl table type
371 l_crtv_rec OKL_crt_pvt.crtv_rec_type;
372 lx_crtv_rec OKL_crt_pvt.crtv_rec_type;
373
374
375 CURSOR approval_csr (a_trans_id NUMBER)
376 IS
377 SELECT REPORT_NUMBER,REPORT_DATE,VENDOR_ID,OBJECT_VERSION_NUMBER
378 FROM OKL_CURE_REPORTS
379 WHERE CURE_REPORT_ID= a_trans_id;
380
381
382 BEGIN
383 IF (funcmode = 'RUN') then
384 l_trans_id := wf_engine.GetItemAttrText(itemtype => itemtype,
385 itemkey => itemkey,
386 aname =>'TRANS_ID');
387
388 OPEN approval_csr(l_trans_id);
389 FETCH approval_csr INTO l_report,l_date,l_vendor_id,l_obj_version;
390 CLOSE approval_csr;
391
392 -- This will set the status
393 l_crtv_rec.APPROVAL_STATUS := 'APPROVED';
394 l_crtv_rec.CURE_REPORT_ID := l_trans_id;
395 l_crtv_rec.OBJECT_VERSION_NUMBER := l_obj_version;
396 l_crtv_rec.REPORT_NUMBER := l_report ;
397 l_crtv_rec.VENDOR_ID := l_vendor_id ;
398 l_crtv_rec.REPORT_DATE := l_date ;
399
400
401 --Now inserting the populated table of records above
402 OKL_cure_reports_pub.update_cure_reports(
403 p_api_version => l_api_version
404 ,p_init_msg_list => fnd_api.g_false
405 ,x_return_status => l_return_status
406 ,x_msg_count => l_msg_count
407 ,x_msg_data => l_msg_data
408 ,p_crtv_rec => l_crtv_rec
409 ,x_crtv_rec => lx_crtv_rec);
410
411 resultout := 'COMPLETE:YES';
412 RETURN;
413 END IF;
414
415 IF (funcmode = 'CANCEL') then
416 resultout := 'COMPLETE:NO';
417 END IF;
418
419 -- TIMEOUT mode
420 IF (funcmode = 'TIMEOUT') then
421 resultout := 'COMPLETE:YES';
422 return ;
423 END IF;
424
425 EXCEPTION
426 when others then
427 wf_core.context('OKL_REQ_WF',' Request_Approved ',
428 itemtype,
429 itemkey,
430 to_char(actid),
431 funcmode);
432 RAISE;
433 END Request_Approved;
434
435 END OKL_REQ_WF;
436