[Home] [Help]
PACKAGE BODY: APPS.POS_WF_PO_ACK
Source
1 PACKAGE BODY POS_WF_PO_ACK AS
2 /* $Header: POSAKPOB.pls 120.1 2006/07/26 14:54:14 jbalakri noship $ */
3
4
5 --
6
7 /*
8 Private Procedure
9 */
10
11 Procedure Insert_Acc_Rejection_Row(itemtype in varchar2,
12 itemkey in varchar2,
13 actid in number,
14 flag in varchar2);
15
16
17 --
18 --
19 --
20
21 procedure acceptance_required ( itemtype in varchar2,
22 itemkey in varchar2,
23 actid in number,
24 funcmode in varchar2,
25 result out NOCOPY varchar2 )
26 is
27 x_doc_header_id number;
28 x_acceptance_flag varchar2(1) := null;
29 x_acceptance_due_date date;
30 x_progress varchar2(3) := '000';
31 begin
32 x_progress := '001';
33
34 x_acceptance_due_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
35 itemkey => itemkey,
36 aname => 'ACCEPTANCE_DUE_DATE');
37
38 x_acceptance_flag := wf_engine.GetItemAttrText ( itemtype => itemtype,
39 itemkey => itemkey,
40 aname => 'ACCEPTANCE_REQUIRED');
41
42 if (x_acceptance_due_date is NULL or nvl(x_acceptance_flag, 'N') <> 'Y') then
43
44 wf_engine.SetItemAttrText ( itemtype => itemtype,
45 itemkey => itemkey,
46 aname => 'BY',
47 avalue => '');
48 end if;
49
50 result := 'COMPLETE:' || nvl(x_acceptance_flag, 'N');
51
52 exception
53 WHEN OTHERS THEN
54 wf_core.context('POS_WF_PO_ACK','acceptance_required',x_progress);
55 raise;
56 end;
57
58 --
59
60 procedure Register_acceptance ( itemtype in varchar2,
61 itemkey in varchar2,
62 actid in number,
63 funcmode in varchar2,
64 result out NOCOPY varchar2 )
65 is
66 x_progress varchar2(3) := '000';
67 x_acceptance_result varchar2(30);
68 x_org_id number;
69 begin
70
71 -- set the org context
72 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
73 itemkey => itemkey,
74 aname => 'ORG_ID');
75
76 fnd_client_info.set_org_context(to_char(x_org_id));
77
78 fnd_message.set_name ('ICX','POS_PO_WF_ACCEPTED_VALUE');
79 x_acceptance_result := fnd_message.get;
80
81 wf_engine.SetItemAttrText ( itemtype => itemtype,
82 itemkey => itemkey,
83 aname => 'ACCEPTANCE_RESULT',
84 avalue => nvl(x_acceptance_result, 'Accepted'));
85
86 -- insert acceptance record.
87
88 Insert_Acc_Rejection_Row(itemtype, itemkey, actid, 'Y');
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 wf_core.context('POS_WF_PO_ACK','Register_acceptance',x_progress);
93 raise;
94 end;
95
96 --
97
98 procedure Register_rejection ( itemtype in varchar2,
99 itemkey in varchar2,
100 actid in number,
101 funcmode in varchar2,
102 result out NOCOPY varchar2 )
103 is
104 x_progress varchar2(3) := '000';
105 x_acceptance_result varchar2(30);
106 x_org_id number;
107 begin
108
109 -- set the org context
110 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
111 itemkey => itemkey,
112 aname => 'ORG_ID');
113
114 fnd_client_info.set_org_context(to_char(x_org_id));
115
116 fnd_message.set_name ('ICX','POS_PO_WF_REJECTED_VALUE');
117 x_acceptance_result := fnd_message.get;
118
119 wf_engine.SetItemAttrText ( itemtype => itemtype,
120 itemkey => itemkey,
121 aname => 'ACCEPTANCE_RESULT',
122 avalue => nvl(x_acceptance_result, 'Rejected'));
123
124 -- insert rejection record.
125
126 Insert_Acc_Rejection_Row(itemtype, itemkey, actid, 'N');
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 wf_core.context('POS_WF_PO_ACK','Register_rejection',x_progress);
131 raise;
132 end;
133
134 --
135
136 procedure Initialize_Attributes( itemtype in varchar2,
137 itemkey in varchar2,
138 actid in number,
139 funcmode in varchar2,
140 result out NOCOPY varchar2 )
141 is
142 x_document_id number;
143 x_document_type_code varchar2(60);
144 x_document_num varchar2(60);
145 x_document_type varchar2(80);
146 x_release_num number := null;
147 x_agent_id number;
148 x_acceptance_required_flag varchar2(1);
149 x_acceptance_due_date date := null;
150 x_progress varchar2(3) := '000';
151 x_org_id number;
152 p_rowid VARCHAR2(2000);
153 l_param VARCHAR2(2000);
154 begin
155 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
156 itemkey => itemkey,
157 aname => 'DOCUMENT_ID');
158
159 x_document_type_code := wf_engine.GetItemAttrText ( itemtype => itemtype,
160 itemkey => itemkey,
161 aname => 'DOCUMENT_TYPE_CODE');
162 --dbms_output.put_line('Document Code is ' || x_document_type_code );
163 if x_document_type_code <> 'RELEASE' then
164
165 select poh.segment1 || '-' || poh.revision_num, polc.displayed_field, poh.agent_id,
166 poh.acceptance_required_flag, poh.acceptance_due_date, poh.org_id
167 into x_document_num, x_document_type, x_agent_id, x_acceptance_required_flag,
168 x_acceptance_due_date, x_org_id
169 from po_headers_all poh,
170 po_lookup_codes polc
171 where poh.po_header_id = x_document_id
172 and poh.type_lookup_code = polc.lookup_code
173 and polc.lookup_type = 'PO TYPE';
174
175 wf_engine.SetItemAttrText ( itemtype => itemtype,
176 itemkey => itemkey,
177 aname => 'FOR',
178 avalue => '');
179
180 else
181 select por.release_num, por.agent_id, poh.segment1 || '-' || poh.revision_num, polc.displayed_field,
182 por.acceptance_required_flag, por.acceptance_due_date, por.agent_id
183 into x_release_num, x_agent_id, x_document_num, x_document_type, x_acceptance_required_flag,
184 x_acceptance_due_date, x_agent_id
185 from po_releases_all por,
186 po_headers_all poh,
187 po_lookup_codes polc
188 where por.po_release_id = x_document_id
189 and por.po_header_id = poh.po_header_id
190 and polc.lookup_type = 'DOCUMENT TYPE'
191 and polc.lookup_code = 'RELEASE';
192 end if;
193
194 -- Set Item Attributes.
195
196 wf_engine.SetItemAttrText ( itemtype => itemtype,
197 itemkey => itemkey,
198 aname => 'DOCUMENT_NUM',
199 avalue => x_document_num);
200
201 wf_engine.SetItemAttrText ( itemtype => itemtype,
202 itemkey => itemkey,
203 aname => 'DOCUMENT_TYPE',
204 avalue => x_document_type);
205
206 wf_engine.SetItemAttrText ( itemtype => itemtype,
207 itemkey => itemkey,
208 aname => 'ACCEPTANCE_REQUIRED',
209 avalue => x_acceptance_required_flag);
210
211 wf_engine.SetItemAttrDate ( itemtype => itemtype,
212 itemkey => itemkey,
213 aname => 'ACCEPTANCE_DUE_DATE',
214 avalue => x_acceptance_due_date);
215
216 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
217 itemkey => itemkey,
218 aname => 'RELEASE_NUM',
219 avalue => x_release_num);
220
221 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
222 itemkey => itemkey,
223 aname => 'BUYER_USER_ID',
224 avalue => x_agent_id);
225
226 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
227 itemkey => itemkey,
228 aname => 'ORG_ID',
229 avalue => x_org_id);
230
231 wf_engine.SetItemAttrText( itemtype => itemtype,
232 itemkey => itemkey,
233 aname => 'PO_DETAILS_URL',
234 avalue => 'PLSQL:POS_COMMON_APIS.GET_PO_DETAILS_URL/' ||
235 itemtype || ':' || itemkey);
236 EXCEPTION
237 WHEN OTHERS THEN
238 wf_core.context('POS_WF_PO_ACK','Initialize_Attributes',x_progress);
239 raise;
240 end;
241
242
243 --
244
245
246 Procedure Insert_Acc_Rejection_Row(itemtype in varchar2,
247 itemkey in varchar2,
248 actid in number,
249 flag in varchar2)
250 is
251
252 x_row_id varchar2(30);
253 x_Acceptance_id number;
254 x_Last_Update_Date date := TRUNC(SYSDATE);
255 x_Last_Updated_By number := fnd_global.user_id;
256 x_Creation_Date date := TRUNC(SYSDATE);
257 x_Created_By number := fnd_global.user_id;
258 x_Po_Header_Id number;
259 x_Po_Release_Id number;
260 x_Action varchar2(240) := 'NEW';
261 x_Action_Date date := TRUNC(SYSDATE);
262 x_Employee_Id number;
263 x_Revision_Num number;
264 x_Accepted_Flag varchar2(1) := flag;
265 x_Acceptance_Lookup_Code varchar2(25);
266 x_Attribute_Category varchar2(30);
267 x_Attribute1 varchar2(150);
268 x_Attribute2 varchar2(150);
269 x_Attribute3 varchar2(150);
270 x_Attribute4 varchar2(150);
271 x_Attribute5 varchar2(150);
272 x_Attribute6 varchar2(150);
273 x_Attribute7 varchar2(150);
274 x_Attribute8 varchar2(150);
275 x_Attribute9 varchar2(150);
276 x_Attribute10 varchar2(150);
277 x_Attribute11 varchar2(150);
278 x_Attribute12 varchar2(150);
279 x_Attribute13 varchar2(150);
280 x_Attribute14 varchar2(150);
281 x_Attribute15 varchar2(150);
282 x_document_id number;
283 x_document_type_code varchar2(30);
284 begin
285
286 SELECT po_acceptances_s.nextval into x_Acceptance_id FROM sys.dual;
287
288 if flag = 'Y' then
289 x_Acceptance_Lookup_Code := 'Accepted Terms';
290 else
291 x_Acceptance_Lookup_Code := 'REJECTED';
292 end if;
293
294 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
295 itemkey => itemkey,
296 aname => 'DOCUMENT_ID');
297
298 x_document_type_code := wf_engine.GetItemAttrText ( itemtype => itemtype,
299 itemkey => itemkey,
300 aname => 'DOCUMENT_TYPE_CODE');
301
302 -- abort any outstanding acceptance notifications for any previous revision of the document.
303
304 if x_document_type_code <> 'RELEASE' then
305 x_Po_Header_Id := x_document_id;
306
307 select revision_num
308 into x_revision_num
309 from po_headers
310 where po_header_id = x_document_id;
311 else
312 x_Po_Release_Id := x_document_id;
313
314 select po_header_id, revision_num
315 into x_Po_Header_Id, x_revision_num
316 from po_releases
317 where po_release_id = x_document_id;
318 end if;
319
320 INSERT INTO PO_ACCEPTANCES(acceptance_id,
321 last_update_date,
322 last_updated_by,
323 creation_date,
324 created_by,
325 po_header_id,
326 po_release_id,
327 action,
328 action_date,
329 employee_id,
330 revision_num,
331 accepted_flag,
332 acceptance_lookup_code,
333 attribute_category,
334 attribute1,
335 attribute2,
336 attribute3,
337 attribute4,
338 attribute5,
339 attribute6,
340 attribute7,
341 attribute8,
342 attribute9,
343 attribute10,
344 attribute11,
345 attribute12,
346 attribute13,
347 attribute14,
348 attribute15
349 )
350 VALUES
351 (x_Acceptance_id,
352 x_last_update_date,
353 x_Last_Updated_By,
354 x_Creation_Date,
355 x_Created_By,
356 x_Po_Header_Id,
357 x_Po_Release_Id,
358 x_Action,
359 x_Action_Date,
360 x_Employee_Id,
361 x_Revision_Num,
362 x_Accepted_Flag,
363 x_Acceptance_Lookup_Code,
364 x_Attribute_Category,
365 x_Attribute1,
366 x_Attribute2,
367 x_Attribute3,
368 x_Attribute4,
369 x_Attribute5,
370 x_Attribute6,
371 x_Attribute7,
372 x_Attribute8,
373 x_Attribute9,
374 x_Attribute10,
375 x_Attribute11,
376 x_Attribute12,
377 x_Attribute13,
378 x_Attribute14,
379 x_Attribute15);
380
381 exception
382 when others then
383 raise;
384 end;
385
386 --
387
388 procedure abort_notification ( document_id in number, document_rev in number, document_type varchar2)
389 is
390 x_progress varchar2(3) := '000';
391 l_item_type varchar2(10) := 'POSPOACK';
392 l_item_key varchar2(240);
393 x_document_type_code varchar2(60);
394 x_acceptance_required varchar2(1) := 'N';
395 x_org_id number;
396 begin
397
398 l_item_key := 'POS_ACK_' || to_char (document_id) || '_' || to_char(nvl(document_rev, 0));
399
400 -- set the org context
401 begin
402 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
403 itemkey => l_item_key,
404 aname => 'ORG_ID');
405
406 fnd_client_info.set_org_context(to_char(x_org_id));
407
408 if document_type = 'RELEASE' then
409 select nvl(acceptance_required_flag, 'N')
410 into x_acceptance_required
411 from po_releases
412 where po_release_id = document_id;
413 else
414 select nvl(acceptance_required_flag, 'N')
415 into x_acceptance_required
416 from po_headers
417 where po_header_id = document_id;
418 end if;
419 exception
420 when others then
421 null;
422 end;
423
424 if x_acceptance_required = 'Y' then
425 begin
426 -- Abort the notification - workflow will take the default transition.
427
428 wf_engine.completeActivity ( l_item_type, l_item_key, 'NOTIFY_SUPPLIER', 'Abort' );
429
430 exception
431 when others then
432 null;
433 end;
434 end if;
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 wf_core.context('POS_WF_PO_ACK','abort_notifications',x_progress);
439 raise;
440 end;
441
442 --
443
444 procedure Initialize_AckAttributes(
445 itemtype in varchar2,
446 itemkey in varchar2,
447 actid in number,
448 funcmode in varchar2,
449 result out NOCOPY varchar2 )
450 is
451 x_document_id number;
452 x_document_type_code varchar2(60);
453 x_document_num varchar2(60);
454 x_document_type varchar2(80);
455 x_release_num number := null;
456 x_agent_id number;
457 x_acceptance_required_flag varchar2(1);
458 x_acceptance_due_date date := null;
459 x_progress varchar2(3) := '000';
460 x_org_id number;
461 p_rowid VARCHAR2(2000);
462 l_param VARCHAR2(2000);
463 begin
464 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
465 itemkey => itemkey,
466 aname => 'DOCUMENT_ID');
467
468 x_document_type_code := wf_engine.GetItemAttrText ( itemtype => itemtype,
469 itemkey => itemkey,
470 aname => 'DOCUMENT_TYPE_CODE');
471
472 --dbms_output.put_line('Org Id is ' || to_char(x_org_id));
473 --dbms_output.put_line('Doc Id is ' || to_char(x_document_id));
474 --dbms_output.put_line('Doc Type is ' || x_document_type_code);
475 --fnd_client_info.set_org_context(to_char(x_org_id));
476
477
478 if x_document_type_code <> 'RELEASE' then
479
480 select poh.segment1 || ',' || poh.revision_num, polc.displayed_field,
481 poh.agent_id, poh.acceptance_required_flag, poh.acceptance_due_date,
482 poh.org_id
483 into x_document_num, x_document_type, x_agent_id,
484 x_acceptance_required_flag,
485 x_acceptance_due_date, x_org_id
486 from po_headers_all poh,
487 po_lookup_codes polc
488 where poh.po_header_id = x_document_id
489 and poh.type_lookup_code = polc.lookup_code
490 and polc.lookup_type = 'PO TYPE';
491
492 wf_engine.SetItemAttrText ( itemtype => itemtype,
493 itemkey => itemkey,
494 aname => 'FOR',
495 avalue => '');
496
497 else
498 select por.release_num, por.agent_id,
499 poh.segment1 || ',' || poh.revision_num, polc.displayed_field,
500 por.acceptance_required_flag, por.acceptance_due_date, por.agent_id
501 into x_release_num, x_agent_id, x_document_num, x_document_type,
502 x_acceptance_required_flag,
503 x_acceptance_due_date, x_agent_id
504 from po_releases_all por,
505 po_headers_all poh,
506 po_lookup_codes polc
507 where por.po_release_id = x_document_id
508 and por.po_header_id = poh.po_header_id
509 and polc.lookup_type = 'DOCUMENT TYPE'
510 and polc.lookup_code = 'RELEASE';
511 end if;
512
513 --dbms_output.put_line('After Select');
514
515 -- Set Item Attributes.
516
517 wf_engine.SetItemAttrText ( itemtype => itemtype,
518 itemkey => itemkey,
519 aname => 'DOCUMENT_NUM',
520 avalue => x_document_num);
521
522 wf_engine.SetItemAttrText ( itemtype => itemtype,
523 itemkey => itemkey,
524 aname => 'DOCUMENT_TYPE',
525 avalue => x_document_type);
526
527 wf_engine.SetItemAttrText ( itemtype => itemtype,
528 itemkey => itemkey,
529 aname => 'ACCEPTANCE_REQUIRED',
530 avalue => x_acceptance_required_flag);
531
532 wf_engine.SetItemAttrDate ( itemtype => itemtype,
533 itemkey => itemkey,
534 aname => 'ACCEPTANCE_DUE_DATE',
535 avalue => x_acceptance_due_date);
536
537 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
538 itemkey => itemkey,
539 aname => 'RELEASE_NUM',
540 avalue => x_release_num);
541
542 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
543 itemkey => itemkey,
544 aname => 'BUYER_USER_ID',
545 avalue => x_agent_id);
546
547 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
548 itemkey => itemkey,
549 aname => 'ORG_ID',
550 avalue => x_org_id);
551 /*
552 wf_engine.SetItemAttrText( itemtype => itemtype,
553 itemkey => itemkey,
554 aname => 'PO_DETAILS_URL',
555 avalue => 'PLSQL:POS_WF_PO_COMMON_APIS.GET_PO_DETAILS_URL/'||
556 itemtype || ':' || itemkey);
557 */
558 EXCEPTION
559 WHEN OTHERS THEN
560 wf_core.context('POS_WF_PO_ACKNOWLEDGE','Initialize_Attributes',x_progress);
561 raise;
562 end;
563
564 END POS_WF_PO_ACK;