[Home] [Help]
PACKAGE BODY: APPS.POS_CHANGE_PROM_DATES
Source
1 PACKAGE BODY POS_CHANGE_PROM_DATES AS
2 /* $Header: POSCPDTB.pls 115.14 2002/12/20 03:21:49 mji ship $ */
3
4
5 PROCEDURE initialize(x_employee_id in NUMBER, x_org_id IN NUMBER);
6
7 --
8 -- PARENT WORKFLOW FUNCITONS
9 --
10
11 procedure Add_Shipment_Attribute ( itemtype in varchar2,
12 itemkey in varchar2,
13 line_location_id in number,
14 orig_promised_date in date,
15 new_promised_date in date,
16 orig_NeedBy_date in date,
17 new_NeedBy_date in date,
18 new_reason in varchar2 )
19 is
20 x_progress varchar2(3) := '000';
21 att_name varchar2(60);
22 shipment_count number;
23 begin
24
25 shipment_count := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
26 itemkey => itemkey,
27 aname => 'NUMBER_OF_SHIPMENTS');
28
29 shipment_count := shipment_count + 1;
30
31 -- Create line location id attribute.
32
33 att_name := 'LINE_LOC_ID' || to_char(shipment_count);
34
35 wf_engine.AddItemAttr ( ItemType => ItemType,
36 ItemKey => ItemKey,
37 aname => att_name);
38
39 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
40 itemkey => itemkey,
41 aname => att_name,
42 avalue => line_location_id);
43
44 -- Create new reason attribute
45
46 att_name := 'LINE_LOC_ID' || to_char(shipment_count);
47 att_name := att_name || '_REASON';
48
49 wf_engine.AddItemAttr ( ItemType => ItemType,
50 ItemKey => ItemKey,
51 aname => att_name);
52
53 wf_engine.SetItemAttrText ( itemtype => itemtype,
54 itemkey => itemkey,
55 aname => att_name,
56 avalue => new_reason);
57
58
59
60 -- Create original promised date attribute.
61
62 att_name := 'LINE_LOC_ID' || to_char(shipment_count);
63 att_name := att_name || '_ORIG_PDATE';
64
65 wf_engine.AddItemAttr ( ItemType => ItemType,
66 ItemKey => ItemKey,
67 aname => att_name);
68
69 wf_engine.SetItemAttrDate ( itemtype => itemtype,
70 itemkey => itemkey,
71 aname => att_name,
72 avalue => orig_promised_date);
73
74 -- Create new promised date attribute.
75
76 att_name := 'LINE_LOC_ID' || to_char(shipment_count);
77 att_name := att_name || '_NEW_PDATE';
78
79 wf_engine.AddItemAttr ( ItemType => ItemType,
80 ItemKey => ItemKey,
81 aname => att_name);
82
83 wf_engine.SetItemAttrDate ( itemtype => itemtype,
84 itemkey => itemkey,
85 aname => att_name,
86 avalue => new_promised_date);
87
88 -- Create original promised date attribute.
89
90 att_name := 'LINE_LOC_ID' || to_char(shipment_count);
91 att_name := att_name || '_ORIG_NDATE';
92
93 wf_engine.AddItemAttr ( ItemType => ItemType,
94 ItemKey => ItemKey,
95 aname => att_name);
96
97 wf_engine.SetItemAttrDate ( itemtype => itemtype,
98 itemkey => itemkey,
99 aname => att_name,
100 avalue => orig_needby_date);
101
102 -- Create new promised date attribute.
103
104 att_name := 'LINE_LOC_ID' || to_char(shipment_count);
105 att_name := att_name || '_NEW_NDATE';
106
107 wf_engine.AddItemAttr ( ItemType => ItemType,
108 ItemKey => ItemKey,
109 aname => att_name);
110
111 wf_engine.SetItemAttrDate ( itemtype => itemtype,
112 itemkey => itemkey,
113 aname => att_name,
114 avalue => new_needby_date);
115
116 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
117 itemkey => itemkey,
118 aname => 'NUMBER_OF_SHIPMENTS',
119 avalue => shipment_count);
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 wf_core.context('POS_CHANGE_PROM_DATES','Add_Shipment_Attribute',x_progress);
124 raise;
125 end;
126
127 --
128
129 procedure Set_Parent_Attributes ( itemtype in varchar2,
130 itemkey in varchar2,
131 actid in number,
132 funcmode in varchar2,
133 result out nocopy varchar2 )
134 is
135 x_progress varchar2(3) := '000';
136 x_document_id number;
137 x_agent_id number;
138 x_agent_username varchar2(60);
139 x_agent_display_name varchar2(240);
140 x_document_num varchar2(60);
141 x_document_type_code varchar2(60);
142 x_document_subtype varchar2(60);
143 x_document_type varchar2(80);
144 x_supplier_userid number;
145 x_supplier_username varchar2(60);
146 x_supplier_displayname varchar2(240);
147 x_line_loc_id1 number;
148 x_po_release_id number;
149 x_org_id number;
150
151 begin
152 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
153 itemkey => itemkey,
154 aname => 'DOCUMENT_ID');
155
156 x_line_loc_id1 := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
157 itemkey => itemkey,
158 aname => 'LINE_LOC_ID1');
159
160 select po_release_id into x_po_release_id
161 from po_line_locations_all
162 where line_location_id = X_line_loc_id1;
163
164 if x_po_release_id is not null then
165 -- shipment for a release
166
167 select por.release_type,
168 polc.displayed_field,
169 por.org_id ,
170 poh.segment1||'-'||to_char(por.release_num),
171 por.agent_id
172 into x_Document_SubType,
173 x_document_type,
174 x_org_id,
175 x_document_num,
176 x_agent_id
177 from po_releases_all por,
178 po_lookup_codes polc,
179 po_headers_all poh
180 where po_release_id = x_po_release_id
181 and por.po_header_id=poh.po_header_id
182 and por.release_type = polc.lookup_code
183 and polc.lookup_type = 'DOCUMENT SUBTYPE';
184
185 x_Document_Type_Code := 'RELEASE';
186
187 else
188
189 select poh.segment1, poh.agent_id, poh.type_lookup_code, polc.displayed_field, poh.org_id
190 into x_document_num, x_agent_id, x_document_subtype, x_document_type, x_org_id
191 from po_headers_all poh,
192 po_lookup_codes polc
193 where
194 poh.po_header_id = x_document_id
195 and poh.type_lookup_code = polc.lookup_code
196 and polc.lookup_type = 'PO TYPE';
197
198 if X_Document_SubType in ('BLANKET', 'CONTRACT') then
199 x_Document_Type_Code := 'PA';
200 else
201 x_Document_Type_Code := 'PO';
202 end if;
203 end if;
204
205 -- Set Item Attributes.
206
207 wf_engine.SetItemAttrText ( itemtype => itemtype,
208 itemkey => itemkey,
209 aname => 'DOCUMENT_NUM',
210 avalue => x_document_num);
211
212
213 wf_engine.SetItemAttrText ( itemtype => itemtype,
214 itemkey => itemkey,
215 aname => 'DOCUMENT_TYPE_CODE',
216 avalue => x_document_type_code);
217
218 wf_engine.SetItemAttrText ( itemtype => itemtype,
219 itemkey => itemkey,
220 aname => 'DOCUMENT_TYPE',
221 avalue => x_document_type);
222
223 wf_engine.SetItemAttrText ( itemtype => itemtype,
224 itemkey => itemkey,
225 aname => 'DOCUMENT_SUBTYPE',
226 avalue => x_document_subtype);
227
228 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
229 itemkey => itemkey,
230 aname => 'ORG_ID',
231 avalue => x_org_id);
232
233 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
234 itemkey => itemkey,
235 aname => 'BUYER_ID',
236 avalue => x_agent_id);
237
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 wf_core.context('POS_CHANGE_PROM_DATES','Set_Parent_Attributes',x_progress);
242 raise;
243 end;
244
245 --
246
247 procedure Start_WFs_For_Shipments ( itemtype in varchar2,
248 itemkey in varchar2,
249 actid in number,
250 funcmode in varchar2,
251 result out nocopy varchar2 )
252 is
253 x_progress varchar2(3) := '000';
254 l_seq varchar2(10);
255 x_ItemType varchar2(8);
256 x_ItemKey varchar2(240);
257 x_Process varchar2(80);
258 WorkflowProcess varchar2(80);
259 print_check varchar2(3);
260 shipment_count number;
261 x_base_att_name varchar2(60);
262 x_att_name varchar2(60);
263 x_line_location_id number;
264 document_id number;
265 supplier_user_id number;
266 x_orig_promised_date date;
267 x_new_promised_date date;
268 x_orig_needby_date date;
269 x_new_needby_date date;
270 x_document_type_code varchar2(60);
271 x_document_subtype varchar2(60);
272 x_document_type varchar2(80);
273 x_document_id number;
274 x_document_num varchar2(240);
275 x_org_id number;
276 x_supplier_username varchar2(60);
277 x_new_reason varchar2(2000);
278 x_requester_id number;
279 begin
280
281 shipment_count := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
282 itemkey => itemkey,
283 aname => 'NUMBER_OF_SHIPMENTS');
284
285 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
286 itemkey => itemkey,
287 aname => 'DOCUMENT_ID');
288
289 x_document_num := wf_engine.GetItemAttrText ( itemtype => itemtype,
290 itemkey => itemkey,
291 aname => 'DOCUMENT_NUM');
292
293 supplier_user_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
294 itemkey => itemkey,
295 aname => 'SUPPLIER_USER_ID');
296
297 x_document_type_code := wf_engine.GetItemAttrText ( itemtype => itemtype,
298 itemkey => itemkey,
299 aname => 'DOCUMENT_TYPE_CODE');
300
301 x_document_subtype := wf_engine.GetItemAttrText ( itemtype => itemtype,
302 itemkey => itemkey,
303 aname => 'DOCUMENT_SUBTYPE');
304
305 x_document_type := wf_engine.GetItemAttrText ( itemtype => itemtype,
306 itemkey => itemkey,
307 aname => 'DOCUMENT_TYPE');
308
309 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
310 itemkey => itemkey,
311 aname => 'ORG_ID');
312
313 --
314 -- Start Individual Workflows for Each Shipment to Process the Date Change.
315 --
316
317 x_ItemType := 'POSMPDCH';
318 x_Process := 'MAIN_PROCESS';
319 WHILE shipment_count > 0 LOOP
320
321 x_base_att_name := 'LINE_LOC_ID' || to_char(shipment_count);
322 x_att_name := x_base_att_name;
323
324 x_line_location_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
325 itemkey => itemkey,
326 aname => x_att_name);
327
328 -- get the requestor id from requisition lines if this line was created from
329 -- a requisition
330
331 begin
332 select to_person_id
333 into x_requester_id
334 from po_requisition_lines_all
335 where line_location_id = x_line_location_id;
336
337 exception
338 when no_data_found then
339 null;
340
341 when others then
342 raise;
343 end;
344
345 x_ItemKey := itemkey || '-' || to_char(x_line_location_id);
346
347 wf_engine.createProcess ( ItemType => x_ItemType,
348 ItemKey => x_ItemKey,
349 Process => x_Process );
350
351 -- Set Shipment Level Attributes.
352
353 x_att_name := x_base_att_name || '_ORIG_PDATE';
354 x_orig_promised_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
355 itemkey => itemkey,
356 aname => x_att_name);
357 x_att_name := x_base_att_name || '_NEW_PDATE';
358 x_new_promised_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
359 itemkey => itemkey,
360 aname => x_att_name);
361 x_att_name := x_base_att_name || '_ORIG_NDATE';
362 x_orig_needby_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
363 itemkey => itemkey,
364 aname => x_att_name);
365 x_att_name := x_base_att_name || '_NEW_NDATE';
366 x_new_needby_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
367 itemkey => itemkey,
368 aname => x_att_name);
369 x_att_name := x_base_att_name || '_REASON';
370 x_new_reason := wf_engine.GetItemAttrText ( itemtype => itemtype,
371 itemkey => itemkey,
372 aname => x_att_name);
373
374 wf_engine.SetItemAttrNumber ( itemtype => x_itemtype,
375 itemkey => x_itemkey,
376 aname => 'LINE_LOCATION_ID',
377 avalue => x_line_location_id);
378
379 wf_engine.SetItemAttrNumber ( itemtype => x_itemtype,
380 itemkey => x_itemkey,
381 aname => 'DOCUMENT_ID',
382 avalue => x_document_id);
383
384 wf_engine.SetItemAttrDate ( itemtype => x_itemtype,
385 itemkey => x_itemkey,
386 aname => 'ORIG_PROMISED_DATE',
387 avalue => x_orig_promised_date);
388
389 wf_engine.SetItemAttrDate ( itemtype => x_itemtype,
390 itemkey => x_itemkey,
391 aname => 'NEW_PROMISED_DATE',
392 avalue => x_new_promised_date);
393
394 wf_engine.SetItemAttrDate ( itemtype => x_itemtype,
395 itemkey => x_itemkey,
396 aname => 'ORIG_NEEDBY_DATE',
397 avalue => x_orig_needby_date);
398
399 wf_engine.SetItemAttrDate ( itemtype => x_itemtype,
400 itemkey => x_itemkey,
401 aname => 'NEW_NEEDBY_DATE',
402 avalue => x_new_needby_date);
403
404 wf_engine.SetItemAttrText ( itemtype => x_itemtype,
405 itemkey => x_itemkey,
406 aname => 'REASON',
407 avalue => x_new_reason);
408
409 wf_engine.SetItemAttrText ( itemtype => x_itemtype,
410 itemkey => x_itemkey,
411 aname => 'SUPPLIER_USER_ID',
412 avalue => supplier_user_id);
413
414 wf_engine.SetItemAttrText ( itemtype => x_itemtype,
415 itemkey => x_itemkey,
416 aname => 'PARENT_WF_ITEMKEY',
417 avalue => itemkey);
418
419 wf_engine.SetItemAttrText ( itemtype => x_itemtype,
420 itemkey => x_itemkey,
421 aname => 'DOCUMENT_NUM',
422 avalue => x_document_num);
423
424 wf_engine.SetItemAttrText ( itemtype => x_itemtype,
425 itemkey => x_itemkey,
426 aname => 'DOCUMENT_TYPE_CODE',
427 avalue => x_document_type_code);
428
429 wf_engine.SetItemAttrText ( itemtype => x_itemtype,
430 itemkey => x_itemkey,
431 aname => 'DOCUMENT_TYPE',
432 avalue => x_document_type);
433
434 wf_engine.SetItemAttrText ( itemtype => x_itemtype,
435 itemkey => x_itemkey,
436 aname => 'DOCUMENT_SUBTYPE',
437 avalue => x_document_subtype);
438
439 wf_engine.SetItemAttrNumber ( itemtype => x_itemtype,
440 itemkey => x_itemkey,
441 aname => 'ORG_ID',
442 avalue => x_org_id);
443
444 wf_engine.SetItemAttrNumber ( itemtype => x_itemtype,
445 itemkey => x_itemkey,
446 aname => 'REQUESTER_ID',
447 avalue => x_requester_id);
448
449 wf_engine.StartProcess ( ItemType => x_ItemType,
450 ItemKey => x_ItemKey );
451
452 shipment_count := shipment_count - 1;
453
454 END LOOP;
455
456 EXCEPTION
457 WHEN OTHERS THEN
458 wf_core.context('POS_CHANGE_PROM_DATES','Start_WFs_For_Shipments',x_progress);
459 raise;
460 end;
461
462 --
463
464 --
465
466 procedure reset_doc_status ( itemtype in varchar2,
467 itemkey in varchar2,
468 actid in number,
469 funcmode in varchar2,
470 result out nocopy varchar2 )
471 is
472 x_document_id number;
473 x_progress varchar2(3) := '000';
474 x_document_type_code varchar2(60);
475 x_org_id number;
476 begin
477
478 -- set the org context
479
480 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
481 itemkey => itemkey,
482 aname => 'ORG_ID');
483
484 fnd_client_info.set_org_context(to_char(x_org_id));
485
486 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
487 itemkey => itemkey,
488 aname => 'DOCUMENT_ID');
489
490 x_document_type_code := wf_engine.GetItemAttrText ( itemtype => itemtype,
491 itemkey => itemkey,
492 aname => 'DOCUMENT_TYPE_CODE');
493 if x_document_type_code <> 'RELEASE' then
494 update po_headers_all
495 set authorization_status = 'APPROVED'
496 where po_header_id = x_document_id;
497 else
498 update po_releases_all
499 set authorization_status = 'APPROVED'
500 where po_release_id = x_document_id;
501 end if;
502
503 EXCEPTION
504 WHEN OTHERS THEN
505 wf_core.context('POS_CHANGE_PROM_DATES','reset_doc_status',x_progress);
506 raise;
507 end;
508
509 --
510
511 procedure Change_Order_Approval ( itemtype in varchar2,
512 itemkey in varchar2,
513 actid in number,
514 funcmode in varchar2,
515 result out nocopy varchar2 )
516 is
517 x_progress varchar2(3) := '000';
518
519 l_seq varchar2(10);
520 x_ItemType varchar2(8);
521 x_ItemKey varchar2(240);
522 WorkflowProcess varchar2(80);
523 print_check varchar2(3);
524
525 ActionOriginatedFrom varchar2(30) := 'POS_DATE_CHG';
526 Preparer_ID Number;
527 DocumentTypeCode varchar2(30);
528 DocumentSubtype varchar2(60);
529 DocumentStatus varchar2(60);
530 RequestorAction varchar2(60) := 'APPROVE';
531 forwardToID number;
532 forwardFromID number;
533 DefaultApprovalPathID number;
534 DocumentNote VARCHAR2(240) := '';
535 X_system_message_level varchar2(4);
536 x_po_auth_status VARCHAR2(25) := '';
537 x_po_header_id NUMBER;
538 x_req_status VARCHAR2(25) := '';
539 x_req_status_dsp VARCHAR2(25) := '';
540 x_error_rc VARCHAR2(25) := '';
541 x_doc_header_id number;
542 l_approval_mode VARCHAR2(30);
543 document_id number;
544 document_num varchar2(60);
545 x_po_release_id number;
546 x_employee_id number;
547 x_org_id number;
548 l_responsibility_id number;
549
550 begin
551
552 -- set the org context
553 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
554 itemkey => itemkey,
555 aname => 'ORG_ID');
556
557 x_employee_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
558 itemkey => itemkey,
559 aname => 'BUYER_ID');
560
561
562 fnd_client_info.set_org_context(to_char(x_org_id));
563
564 document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
565 itemkey => itemkey,
566 aname => 'DOCUMENT_ID');
567
568 Document_num := wf_engine.GetItemAttrText ( itemtype => itemtype,
569 itemkey => itemkey,
570 aname => 'DOCUMENT_NUM');
571
572 preparer_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
573 itemkey => itemkey,
574 aname => 'BUYER_ID');
575
576 DocumentTypeCode := wf_engine.GetItemAttrText ( itemtype => itemtype,
577 itemkey => itemkey,
578 aname => 'DOCUMENT_TYPE_CODE');
579
580 documentSubtype := wf_engine.GetItemAttrText ( itemtype => itemtype,
581 itemkey => itemkey,
582 aname => 'DOCUMENT_SUBTYPE');
583
584 select to_char(PO_WF_ITEMKEY_S.NEXTVAL) into l_seq from sys.dual;
585
586 x_ItemKey := to_char(Document_ID) || '-' || l_seq;
587
588 initialize (x_employee_id, x_org_id);
589
590 if NVL(DocumentTypeCode, 'PO') <> 'RELEASE' then
591
592 update po_headers_all set
593 authorization_status = 'IN PROCESS',
594 revision_num = revision_num + 1,
595 revised_date = sysdate,
596 last_update_date = sysdate,
597 last_updated_by = fnd_global.user_id,
598 last_update_login = fnd_global.login_id,
599 request_id = fnd_global.conc_request_id,
600 program_application_id = fnd_global.prog_appl_id,
601 program_id = fnd_global.conc_program_id,
602 program_update_date = sysdate
603 where po_header_id = document_id;
604 else
605 update po_releases_all set
606 authorization_status = 'IN PROCESS',
607 revision_num = revision_num + 1,
608 revised_date = sysdate,
609 last_update_date = sysdate,
610 last_updated_by = fnd_global.user_id,
611 last_update_login = fnd_global.login_id,
612 request_id = fnd_global.conc_request_id,
613 program_application_id = fnd_global.prog_appl_id,
614 program_id = fnd_global.conc_program_id,
615 program_update_date = sysdate
616 where po_release_id = document_id;
617 end if;
618
619 select wf_approval_itemtype, wf_approval_process
620 into x_ItemType, WorkflowProcess
621 from PO_DOCUMENT_TYPES_V
622 where DOCUMENT_TYPE_CODE = DocumentTypeCode
623 and DOCUMENT_SUBTYPE = DocumentSubtype;
624
625 IF x_ItemType IS NULL THEN
626
627 -- return failure;
628 result := 'COMPLETE:FAILURE';
629 ELSE
630
631 print_check := 'N';
632
633
634 PO_REQAPPROVAL_INIT1.Start_WF_Process ( ItemType => x_ItemType,
635 ItemKey => x_ItemKey,
636 WorkflowProcess => WorkflowProcess,
637 ActionOriginatedFrom => ActionOriginatedFrom,
638 DocumentID => Document_ID,
639 DocumentNumber => Document_Num,
640 PreparerID => Preparer_ID,
641 DocumentTypeCode => DocumentTypeCode,
642 DocumentSubtype => DocumentSubtype,
643 SubmitterAction => RequestorAction,
644 forwardToID => forwardToID,
645 forwardFromID => forwardFromID,
646 DefaultApprovalPathID => DefaultApprovalPathID,
647 Note => DocumentNote,
648 printFlag => print_check );
649
650
651 result := 'COMPLETE:SUCCESS';
652
653 END IF;
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 wf_core.context('POS_CHANGE_PROM_DATE','Change_Order_Approval',x_progress);
658 raise;
659 end;
660
661 --
662
663 ----------------------------------------------------------------------------------------------------------
664 ----------------------------------------------------------------------------------------------------------
665 ----------------------------------------------------------------------------------------------------------
666
667 --
668 -- CHILD WORKFLOW FUNCITONS
669 --
670
671 ----------------------------------------------------------------------------------------------------------
672 ----------------------------------------------------------------------------------------------------------
673 ----------------------------------------------------------------------------------------------------------
674
675 --
676
677 procedure Set_Attributes ( itemtype in varchar2,
678 itemkey in varchar2,
679 actid in number,
680 funcmode in varchar2,
681 result out nocopy varchar2 )
682 is
683 x_progress varchar2(3) := '000';
684 x_item_id number;
685 x_line_location_id number;
686 x_document_id number;
687 x_orig_system varchar2(4);
688 x_agent_id number;
689 x_agent_username varchar2(60);
690 x_agent_display_name varchar2(240);
691 x_document_num varchar2(60);
692 x_document_type_code varchar2(60);
693 x_document_type varchar2(80);
694 x_ship_to_organization_id number;
695 x_supplier_userid number;
696 x_supplier_username varchar2(60) ;
697 x_supplier_displayname varchar2(240);
698 DocumentTypeCode varchar2(30);
699 x_org_id number;
700 x_acceptance_result varchar2(30);
701 x_line_num number;
702 x_qty_ordered number;
703 x_item_description varchar2(240);
704 x_uom varchar2(25);
705 x_vendor_name varchar2(240);
706 begin
707 -- set the org context
708 --dbms_output.put_line('Inside Set Attributes');
709
710 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
711 itemkey => itemkey,
712 aname => 'ORG_ID');
713
714 fnd_client_info.set_org_context(to_char(x_org_id));
715
716 x_line_location_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
717 itemkey => itemkey,
718 aname => 'LINE_LOCATION_ID');
719
720 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
721 itemkey => itemkey,
722 aname => 'DOCUMENT_ID');
723
724 x_supplier_username := wf_engine.GetItemAttrText ( itemtype => itemtype,
725 itemkey => itemkey,
726 aname => 'SUPPLIER_USER_NAME');
727
728 DocumentTypeCode := wf_engine.GetItemAttrText ( itemtype => itemtype,
729 itemkey => itemkey,
730 aname => 'DOCUMENT_TYPE_CODE');
731
732 x_supplier_userid := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
733 itemkey => itemkey,
734 aname => 'SUPPLIER_USER_ID');
735
736 fnd_message.set_name ('ICX','POS_PO_WF_REJECTED_VALUE');
737 x_acceptance_result := fnd_message.get;
738
739 wf_engine.SetItemAttrText ( itemtype => itemtype,
740 itemkey => itemkey,
741 aname => 'ACCEPTANCE_RESULT',
742 avalue => nvl(x_acceptance_result, 'Rejected'));
743
744 if DocumentTypeCode <> 'RELEASE' then
745 select poh.agent_id, pol.item_id, poll.ship_to_organization_id,pol.line_num,
746 pol.UNIT_MEAS_LOOKUP_CODE, pol.ITEM_DESCRIPTION,
747 DECODE(poll.SHIPMENT_TYPE, 'PRICE BREAK', NULL,
748 poll.QUANTITY - poll.QUANTITY_CANCELLED),pv.vendor_name
749 into x_agent_id, x_item_id, x_ship_to_organization_id,x_line_num,
750 x_uom,x_item_description,x_qty_ordered,x_vendor_name
751 from po_headers_all poh,
752 po_lines_all pol,
753 po_line_locations_all poll,
754 po_vendors pv
755 where
756 poll.line_location_id = x_line_location_id
757 and pol.po_line_id = poll.po_line_id
758 and poll.po_header_id = poh.po_header_id
759 and poh.vendor_id=pv.vendor_id;
760 else
761 select por.agent_id, pol.item_id, poll.ship_to_organization_id,pol.line_num,
762 pol.UNIT_MEAS_LOOKUP_CODE, POL.ITEM_DESCRIPTION,
763 DECODE(poll.SHIPMENT_TYPE, 'PRICE BREAK', NULL,
764 poll.QUANTITY - poll.QUANTITY_CANCELLED),pv.vendor_name
765 into x_agent_id, x_item_id, x_ship_to_organization_id,x_line_num,
766 x_uom,x_item_description,x_qty_ordered,x_vendor_name
767 from po_releases_all por,
768 po_lines_all pol,
769 po_line_locations_all poll,
770 po_vendors pv,
771 po_headers_all poh
772 where
773 poll.line_location_id = x_line_location_id
774 and pol.po_line_id = poll.po_line_id
775 and poll.po_release_id = por.po_release_id
776 and poh.po_header_id = por.po_header_id
777 and poh.vendor_id = pv.vendor_id;
778 end if;
779
780 -- Set Item Attributes.
781
782
783 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
784 itemkey => itemkey,
785 aname => 'SHIP_TO_ORGANIZATION_ID',
786 avalue => x_ship_to_organization_id);
787
788 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
789 itemkey => itemkey,
790 aname => 'ITEM_ID',
791 avalue => x_item_id);
792
793 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
794 itemkey => itemkey,
795 aname => 'BUYER_USER_ID',
796 avalue => x_agent_id);
797
798 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
799 itemkey => itemkey,
800 aname => 'LINE_NUMBER',
801 avalue => x_line_num);
802
803 wf_engine.SetItemAttrText ( itemtype => itemtype,
804 itemkey => itemkey,
805 aname => 'UOM',
806 avalue => x_uom);
807
808 wf_engine.SetItemAttrText ( itemtype => itemtype,
809 itemkey => itemkey,
810 aname => 'ITEM_DESCRIPTION',
811 avalue => x_item_description);
812
813 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
814 itemkey => itemkey,
815 aname => 'QTY_ORDERED',
816 avalue => x_qty_ordered);
817
818 wf_engine.SetItemAttrText ( itemtype => itemtype,
819 itemkey => itemkey,
820 aname => 'SUPPLIER',
821 avalue => x_vendor_name);
822
823 select user_name
824 into x_supplier_username
825 from fnd_user where user_id = x_supplier_userid;
826
827 if x_supplier_username is null then
828
829 -- get suplier user id
830
831 if x_supplier_userid is not null then
832
833 x_progress := '003';
834
835 -- Get the supplier user name
836
837 x_orig_system := 'PER';
838
839 WF_DIRECTORY.GetUserName( x_orig_system,
840 x_supplier_userid,
841 x_supplier_username,
842 x_supplier_displayname);
843
844 x_progress := '004';
845 end if;
846 end if;
847
848 wf_engine.SetItemAttrText ( itemtype => itemtype,
849 itemkey => itemkey,
850 aname => 'SUPPLIER_USER_NAME',
851 avalue => x_supplier_username);
852
853 EXCEPTION
854 WHEN OTHERS THEN
855 wf_core.context('POS_CHANGE_PROM_DATES','Set_Attributes',x_progress);
856 raise;
857 end;
858
859 --
860
861 procedure Find_Planner ( itemtype in varchar2,
862 itemkey in varchar2,
863 actid in number,
864 funcmode in varchar2,
865 result out nocopy varchar2 )
866 is
867 x_progress varchar2(3) := '000';
868 x_planner_code varchar2(10);
869 x_planner_username varchar2(100);
870 x_planner_display_name varchar2(240);
871 x_employee_id number := null;
872 x_item_id number;
873 x_orig_system varchar2(4);
874 x_ship_to_org_id number;
875 begin
876
877 x_item_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
878 itemkey => itemkey,
879 aname => 'ITEM_ID');
880
881 x_ship_to_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
882 itemkey => itemkey,
883 aname => 'SHIP_TO_ORGANIZATION_ID');
884
885 x_progress := '001';
886
887 begin
888
889 select distinct planner_code
890 into x_planner_code
891 from mtl_system_items
892 where inventory_item_id = x_item_id
893 and organization_id = x_ship_to_org_id;
894
895 x_progress := '002';
896
897 select distinct employee_id
898 into x_employee_id
899 from mtl_planners
900 where planner_code = x_planner_code
901 and organization_id = x_ship_to_org_id;
902
903 exception
904 when no_data_found then
905 null;
906 when others then
907 raise;
908 end;
909
910 if x_employee_id is not null then
911
912 x_progress := '003';
913
914 -- Get the employee user name
915
916 x_orig_system:= 'PER';
917
918 WF_DIRECTORY.GetUserName( x_orig_system,
919 x_employee_id,
920 x_planner_username,
921 x_planner_display_name);
922
923 x_progress := '004';
924
925 if x_planner_username is null then
926
927 -- May want to raise an exception as there is no role defined of the employee.
928 result := 'COMPLETE:NOT_FOUND';
929 else
930 wf_engine.SetItemAttrText ( itemtype => itemtype,
931 itemkey => itemkey,
932 aname => 'PLANNER_USER_NAME',
933 avalue => x_planner_username);
934
935 result := 'COMPLETE:FOUND';
936 end if;
937 else
938 result := 'COMPLETE:NOT_FOUND';
939 end if;
940
941 EXCEPTION
942 WHEN OTHERS THEN
943 wf_core.context('POS_CHANGE_PROM_DATES','Find_Planner',x_progress);
944 raise;
945 end;
946
947 --
948
949 procedure Find_ShopFloor_Mgr ( itemtype in varchar2,
950 itemkey in varchar2,
951 actid in number,
952 funcmode in varchar2,
953 result out nocopy varchar2 )
954 is
955 x_progress varchar2(3) := '000';
956 x_Shopfloor_Mgr_username varchar2(100) := null;
957 x_Shopfloor_Mgr_display_name varchar2(240);
958 x_Shopfloor_Mgr_id number := null;
959 x_item_id number;
960 x_orig_system varchar2(4);
961 x_ship_to_org_id number;
962 begin
963
964 x_ship_to_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
965 itemkey => itemkey,
966 aname => 'SHIP_TO_ORGANIZATION_ID');
967
968 x_progress := '001';
969
970 begin
971
972 x_Shopfloor_Mgr_username := wip_std_wf.GetProductionSchedLogin(x_ship_to_org_id);
973
974 exception
975 when no_data_found then
976 null;
977 when others then
978 raise;
979 end;
980
981 if x_Shopfloor_Mgr_username is not null then
982
983 wf_engine.SetItemAttrText ( itemtype => itemtype,
984 itemkey => itemkey,
985 aname => 'SHOPFLOORMGR_USER_NAME',
986 avalue => x_Shopfloor_Mgr_username);
987
988 result := 'COMPLETE:FOUND';
989 else
990 result := 'COMPLETE:NOT_FOUND';
991 end if;
992
993 EXCEPTION
994 WHEN OTHERS THEN
995 wf_core.context('POS_CHANGE_PROM_DATES','Find_ShopFloor_Mgr',x_progress);
996 raise;
997 end;
998
999 --
1000
1001 procedure Find_Requester ( itemtype in varchar2,
1002 itemkey in varchar2,
1003 actid in number,
1004 funcmode in varchar2,
1005 result out nocopy varchar2 )
1006 is
1007 x_progress varchar2(3) := '000';
1008 x_requester_id number;
1009 x_requester_username varchar2(100);
1010 x_requester_display_name varchar2(240);
1011 x_orig_system varchar2(4);
1012 x_org_id number;
1013 begin
1014
1015 -- set the org context
1016
1017 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1018 itemkey => itemkey,
1019 aname => 'ORG_ID');
1020
1021 fnd_client_info.set_org_context(to_char(x_org_id));
1022
1023 x_requester_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1024 itemkey => itemkey,
1025 aname => 'REQUESTER_ID');
1026
1027 if x_requester_id is not null then
1028
1029 x_progress := '003';
1030
1031 -- Get the employee user name
1032
1033 x_orig_system:= 'PER';
1034
1035 WF_DIRECTORY.GetUserName( x_orig_system,
1036 x_requester_id,
1037 x_requester_username,
1038 x_requester_display_name);
1039
1040 x_progress := '004';
1041
1042 if x_requester_username is null then
1043
1044 -- May want to raise an exception as there is no role defined of the employee.
1045 result := 'COMPLETE:NOT_FOUND';
1046 else
1047 wf_engine.SetItemAttrText ( itemtype => itemtype,
1048 itemkey => itemkey,
1049 aname => 'REQUESTER_USER_NAME',
1050 avalue => x_requester_username);
1051
1052 result := 'COMPLETE:FOUND';
1053 end if;
1054 else
1055 result := 'COMPLETE:NOT_FOUND';
1056 end if;
1057
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 wf_core.context('POS_CHANGE_PROM_DATES','Find_Requester',x_progress);
1061 raise;
1062 end;
1063
1064 --
1065
1066 procedure OSP_Item ( itemtype in varchar2,
1067 itemkey in varchar2,
1068 actid in number,
1069 funcmode in varchar2,
1070 result out nocopy varchar2 )
1071 is
1072 x_progress varchar2(3) := '000';
1073 x_item_id number;
1074 x_osp_item_flag varchar2(1);
1075 x_ship_to_org_id number;
1076 begin
1077
1078 x_item_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1079 itemkey => itemkey,
1080 aname => 'ITEM_ID');
1081
1082 x_ship_to_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1083 itemkey => itemkey,
1084 aname => 'SHIP_TO_ORGANIZATION_ID');
1085
1086 x_progress := '001';
1087
1088 /* bug 1342116 : if item id is null we should not check for osp flag*/
1089 IF x_item_id is null THEN
1090 result := 'COMPLETE:N';
1091 ELSE
1092 select distinct OUTSIDE_OPERATION_FLAG
1093 into x_osp_item_flag
1094 from mtl_system_items
1095 where inventory_item_id = x_item_id
1096 and organization_id = x_ship_to_org_id;
1097
1098 if nvl(x_osp_item_flag, 'N') = 'Y' then
1099 result := 'COMPLETE:Y';
1100 else
1101 result := 'COMPLETE:N';
1102 end if;
1103 END IF;
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 wf_core.context('POS_CHANGE_PROM_DATES','OSP_Item',x_progress);
1107 raise;
1108 end;
1109
1110 --
1111
1112 procedure Planned_Item ( itemtype in varchar2,
1113 itemkey in varchar2,
1114 actid in number,
1115 funcmode in varchar2,
1116 result out nocopy varchar2 )
1117 is
1118 x_progress varchar2(3) := '000';
1119 x_item_id number;
1120 x_planning_item_flag varchar2(1);
1121 x_ship_to_org_id number;
1122 begin
1123
1124 x_item_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1125 itemkey => itemkey,
1126 aname => 'ITEM_ID');
1127
1128 x_ship_to_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1129 itemkey => itemkey,
1130 aname => 'SHIP_TO_ORGANIZATION_ID');
1131
1132 x_progress := '001';
1133
1134 /* bug 1342116 : if item id is null we should not check for planned item flag*/
1135 IF x_item_id is null THEN
1136 result := 'COMPLETE:N';
1137 ELSE
1138 select distinct REPETITIVE_PLANNING_FLAG
1139 into x_planning_item_flag
1140 from mtl_system_items
1141 where inventory_item_id = x_item_id
1142 and organization_id = x_ship_to_org_id;
1143
1144 if nvl(x_planning_item_flag, 'N') = 'Y' then
1145 result := 'COMPLETE:Y';
1146 else
1147 result := 'COMPLETE:N';
1148 end if;
1149 END IF;
1150
1151 EXCEPTION
1152 WHEN OTHERS THEN
1153 wf_core.context('POS_CHANGE_PROM_DATES','Planned_Item',x_progress);
1154 raise;
1155 end;
1156
1157 --
1158
1159 procedure Update_Date ( itemtype in varchar2,
1160 itemkey in varchar2,
1161 actid in number,
1162 funcmode in varchar2,
1163 result out nocopy varchar2 )
1164 is
1165 x_progress varchar2(3) := '000';
1166 x_line_location_id number;
1167 x_new_date date;
1168 x_parent_WF_itemtype varchar2(10) := 'POSMPDPT';
1169 x_parent_WF_itemkey varchar2(240);
1170 x_org_id number;
1171 x_employee_id number;
1172
1173 begin
1174
1175 -- set the org context
1176
1177 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1178 itemkey => itemkey,
1179 aname => 'ORG_ID');
1180
1181 x_employee_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1182 itemkey => itemkey,
1183 aname => 'BUYER_USER_ID');
1184
1185 -- fnd_client_info.set_org_context(to_char(x_org_id));
1186
1187 x_line_location_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1188 itemkey => itemkey,
1189 aname => 'LINE_LOCATION_ID');
1190
1191 x_new_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
1192 itemkey => itemkey,
1193 aname => 'NEW_PROMISED_DATE');
1194
1195 initialize(x_employee_id, x_org_id);
1196
1197 UPDATE po_line_locations_all
1198 SET promised_date = x_new_date,
1199 last_update_date = sysdate,
1200 last_updated_by = fnd_global.user_id,
1201 last_update_login = fnd_global.login_id,
1202 request_id = fnd_global.conc_request_id,
1203 program_application_id = fnd_global.prog_appl_id,
1204 program_id = fnd_global.conc_program_id,
1205 program_update_date = sysdate
1206 WHERE line_location_id = x_line_location_id;
1207
1208 -- dbms_output.put_line ('DONE updating date');
1209
1210 --
1211 -- Update the parent WF to register the acceptance.
1212 -- The document would have to be routed for approval.
1213 --
1214
1215 x_parent_WF_itemkey := wf_engine.GetItemAttrText ( itemtype => itemtype,
1216 itemkey => itemkey,
1217 aname => 'PARENT_WF_ITEMKEY');
1218
1219 wf_engine.SetItemAttrText ( itemtype => x_parent_WF_itemtype,
1220 itemkey => x_parent_WF_itemkey,
1221 aname => 'UPDATE_ACCEPTED_FLAG',
1222 avalue => 'Y');
1223
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 wf_core.context('POS_CHANGE_PROM_DATES','Update_Date',x_progress);
1227 raise;
1228 end;
1229
1230 --
1231
1232 procedure Update_Prom_Needby_Date ( itemtype in varchar2,
1233 itemkey in varchar2,
1234 actid in number,
1235 funcmode in varchar2,
1236 result out nocopy varchar2 )
1237 is
1238 x_progress varchar2(3) := '000';
1239 x_line_location_id number;
1240 x_new_promised_date date;
1241 x_new_needby_date date;
1242 x_parent_WF_itemtype varchar2(10) := 'POSMPDPT';
1243 x_parent_WF_itemkey varchar2(240);
1244 x_org_id number;
1245 begin
1246
1247 -- set the org context
1248
1249 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1250 itemkey => itemkey,
1251 aname => 'ORG_ID');
1252
1253 fnd_client_info.set_org_context(to_char(x_org_id));
1254
1255 x_line_location_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1256 itemkey => itemkey,
1257 aname => 'LINE_LOCATION_ID');
1258
1259 x_new_promised_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
1260 itemkey => itemkey,
1261 aname => 'NEW_PROMISED_DATE');
1262
1263 x_new_needby_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
1264 itemkey => itemkey,
1265 aname => 'NEW_NEEDBY_DATE');
1266
1267 UPDATE po_line_locations_all
1268 SET promised_date = x_new_promised_date,
1269 need_by_date = x_new_needby_date,
1270 last_update_date = sysdate,
1271 last_updated_by = fnd_global.user_id,
1272 last_update_login = fnd_global.login_id,
1273 request_id = fnd_global.conc_request_id,
1274 program_application_id = fnd_global.prog_appl_id,
1275 program_id = fnd_global.conc_program_id,
1276 program_update_date = sysdate
1277 WHERE line_location_id = x_line_location_id;
1278
1279 --
1280 -- Update the parent WF to register the acceptance.
1281 -- The document would have to be routed for approval.
1282 --
1283
1284 x_parent_WF_itemkey := wf_engine.GetItemAttrText ( itemtype => itemtype,
1285 itemkey => itemkey,
1286 aname => 'PARENT_WF_ITEMKEY');
1287
1288 wf_engine.SetItemAttrText ( itemtype => x_parent_WF_itemtype,
1289 itemkey => x_parent_WF_itemkey,
1290 aname => 'UPDATE_ACCEPTED_FLAG',
1291 avalue => 'Y');
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 wf_core.context('POS_CHANGE_PROM_DATES','Update_Prom_Needby_Date',x_progress);
1295 raise;
1296 end;
1297
1298 --
1299
1300 procedure Update_Parent_WF ( itemtype in varchar2,
1301 itemkey in varchar2,
1302 actid in number,
1303 funcmode in varchar2,
1304 result out nocopy varchar2 )
1305 is
1306 x_progress varchar2(3) := '000';
1307 x_parent_WF_itemtype varchar2(10) := 'POSMPDPT';
1308 x_parent_WF_itemkey varchar2(240);
1309 x_num_of_shipments number;
1310 x_org_id number;
1311 begin
1312
1313 -- set the org context
1314
1315 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1316 itemkey => itemkey,
1317 aname => 'ORG_ID');
1318
1319 fnd_client_info.set_org_context(to_char(x_org_id));
1320
1321 x_parent_WF_itemkey := wf_engine.GetItemAttrText ( itemtype => itemtype,
1322 itemkey => itemkey,
1323 aname => 'PARENT_WF_ITEMKEY');
1324
1325 x_num_of_shipments := wf_engine.GetItemAttrNumber ( itemtype => x_parent_WF_itemtype,
1326 itemkey => x_parent_WF_itemkey,
1327 aname => 'NUMBER_OF_SHIPMENTS');
1328
1329 wf_engine.SetItemAttrNumber ( itemtype => x_parent_WF_itemtype,
1330 itemkey => x_parent_WF_itemkey,
1331 aname => 'NUMBER_OF_SHIPMENTS',
1332 avalue => x_num_of_shipments - 1);
1333
1334 -- Complete Parent WF Process.
1335
1336 begin
1337
1338 wf_engine.CompleteActivity(x_parent_WF_itemtype, x_parent_WF_itemkey, 'BLOCK-1', '');
1339 exception
1340 when others then
1341 raise;
1342 end;
1343
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 wf_core.context('POS_CHANGE_PROM_DATES','Update_Parent_WF',x_progress);
1347 raise;
1348 end;
1349
1350 --
1351
1352 procedure All_Requesters_Notified ( itemtype in varchar2,
1353 itemkey in varchar2,
1354 actid in number,
1355 funcmode in varchar2,
1356 result out nocopy varchar2 )
1357 is
1358 x_progress varchar2(3) := '000';
1359 x_item_id number;
1360 x_distribution_id number;
1361 x_line_location_id number;
1362 x_dummy varchar2(20) := 'No Dist';
1363 x_current_distribution_num number;
1364 x_deliver_to_person_id number;
1365 begin
1366
1367 x_current_distribution_num := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1368 itemkey => itemkey,
1369 aname => 'CURRENT_DISTRIBUTION');
1370
1371 x_line_location_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1372 itemkey => itemkey,
1373 aname => 'LINE_LOCATION_ID');
1374
1375 x_progress := '001';
1376
1377 begin
1378 select 'Dist Exists', deliver_to_person_id
1379 into x_dummy, x_deliver_to_person_id
1380 from po_distributions_all
1381 where line_location_id = x_line_location_id
1382 and distribution_num = x_current_distribution_num;
1383
1384 -- increment current distribution number
1385 x_current_distribution_num := x_current_distribution_num + 1;
1386
1387 exception
1388 when no_data_found then
1389 null;
1390 when others then
1391 raise;
1392 end;
1393
1394 if nvl(x_dummy, 'null') = 'Dist Exists' then
1395
1396 -- update dist. num. item attribute
1397
1398 wf_engine.SetItemAttrText ( itemtype => itemtype,
1399 itemkey => itemkey,
1400 aname => 'CURRENT_DISTRIBUTION',
1401 avalue => x_current_distribution_num);
1402
1403 if x_deliver_to_person_id is not null then
1404
1405 -- set requester attribute
1406
1407 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
1408 itemkey => itemkey,
1409 aname => 'REQUESTER_ID',
1410 avalue => x_deliver_to_person_id);
1411
1412 result := 'COMPLETE:N';
1413
1414 end if;
1415 else
1416 result := 'COMPLETE:Y';
1417 end if;
1418
1419 EXCEPTION
1420 WHEN OTHERS THEN
1421 wf_core.context('POS_CHANGE_PROM_DATES','All_Requesters_Notified',x_progress);
1422 raise;
1423 end;
1424
1425 --
1426
1427 procedure Register_acceptance ( itemtype in varchar2,
1428 itemkey in varchar2,
1429 actid in number,
1430 funcmode in varchar2,
1431 result out nocopy varchar2 )
1432 is
1433 x_progress varchar2(3) := '000';
1434 x_acceptance_result varchar2(30);
1435 begin
1436
1437 fnd_message.set_name ('ICX','POS_PO_WF_ACCEPTED_VALUE');
1438 x_acceptance_result := fnd_message.get;
1439
1440 wf_engine.SetItemAttrText ( itemtype => itemtype,
1441 itemkey => itemkey,
1442 aname => 'ACCEPTANCE_RESULT',
1443 avalue => nvl(x_acceptance_result, 'Accepted'));
1444 EXCEPTION
1445 WHEN OTHERS THEN
1446 wf_core.context('POS_CHANGE_PROM_DATES','Register_acceptance',x_progress);
1447 raise;
1448 end;
1449
1450
1451 PROCEDURE initialize(x_employee_id in NUMBER,x_org_id IN NUMBER) IS
1452
1453 x_resp_id NUMBER := -1;
1454 x_user_id NUMBER := -1;
1455 x_resp_appl_id NUMBER := 201;
1456
1457 BEGIN
1458 begin
1459 SELECT FND.user_id
1460 INTO x_user_id
1461 FROM FND_USER FND, HR_EMPLOYEES_CURRENT_V HR
1462 WHERE HR.EMPLOYEE_ID = x_employee_id
1463 AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID
1464 AND ROWNUM = 1;
1465
1466 EXCEPTION
1467 WHEN OTHERS THEN
1468 x_user_id := -1;
1469 END;
1470
1471
1472 BEGIN
1473
1474 FND_PROFILE.GET('RESP_ID', x_resp_id);
1475
1476 if x_resp_id is NULL then
1477
1478 select MIN(fr.responsibility_id)
1479 into x_resp_id
1480 from fnd_user_resp_groups fur,
1481 fnd_responsibility fr,
1482 financials_system_params_all fsp
1483 where fur.user_id = x_user_id
1484 and fur.responsibility_application_id = x_resp_appl_id
1485 and fur.responsibility_id = fr.responsibility_id
1486 and fr.start_date < sysdate
1487 and nvl(fr.end_date, sysdate +1) >= sysdate
1488 and fur.start_date < sysdate
1489 and nvl(fur.end_date, sysdate +1) >= Sysdate
1490 AND nvl(fnd_profile.value_specific('ORG_ID', NULL, fr.responsibility_id, fur.responsibility_application_id),-1) = nvl(x_org_id,-1)
1491 and nvl(fsp.org_id,-1) = nvl(x_org_id,-1)
1492 and nvl(fsp.business_group_id,-1) = nvl(fnd_profile.value_specific('PER_BUSINESS_GROUP_ID', NULL, fr.responsibility_id, fur.responsibility_application_id),-1);
1493
1494 end if;
1495
1496 EXCEPTION
1497 when others then
1498 x_resp_id := -1;
1499 END;
1500
1501 FND_GLOBAL.APPS_INITIALIZE(x_user_id,x_resp_id,x_resp_appl_id);
1502
1503 END initialize;
1504
1505
1506
1507 END POS_CHANGE_PROM_DATES;