[Home] [Help]
PACKAGE BODY: APPS.PO_NEGOTIATION_REQ_NOTIF
Source
1 PACKAGE BODY po_negotiation_req_notif AS
2 /* $Header: POXNEG2B.pls 120.2 2010/10/08 10:44:24 dashah ship $*/
3
4 TYPE line_record IS RECORD (
5 req_line_id po_requisition_lines.requisition_line_id%TYPE,
6 line_num po_requisition_lines.line_num%TYPE,
7 neg_line_num po_requisition_lines.auction_line_number%TYPE,
8 req_num po_requisition_headers.segment1%TYPE,
9 item_num mtl_system_items_kfv.concatenated_segments%TYPE,
10 item_desc po_requisition_lines.item_description%TYPE,
11 uom po_requisition_lines.unit_meas_lookup_code%TYPE,
12 quantity po_requisition_lines.quantity%TYPE,
13 need_by_date po_requisition_lines.need_by_date%TYPE,
14 cancel_flag po_requisition_lines.cancel_flag%TYPE,
15 closed_code po_requisition_lines.closed_code%TYPE,
16 old_qty po_reschedule_interface.orig_quantity%TYPE,
17 old_need_by_date po_reschedule_interface.orig_need_by_date%TYPE
18 ,auction_header_id po_requisition_lines.auction_header_id%TYPE --Bug 4107528
19 );
20
21
22 /*============================================================================
23 Name: Req_Change_workflow_startup
24 DESC: notifications to sourcing professional when req details are changed
25 or cancelled
26 ==============================================================================*/
27 PROCEDURE req_change_workflow_startup(x_calling_program IN VARCHAR2,
28 x_negotiation_id IN NUMBER ,
29 x_negotiation_num IN VARCHAR2,
30 x_requisition_doc_id IN NUMBER,
31 x_process_id IN NUMBER DEFAULT NULL)
32 IS
33
34
35 x_progress varchar2(3) := null;
36 OwnerName varchar2(200) := null;
37 x_error_code varchar2(200) := null;
38 x_error_msg varchar2(200) := null;
39 x_result number := 0;
40 ItemKey varchar2(80);
41 ItemType varchar2(80);
42 WorkflowProcess varchar2(80);
43 l_seq varchar2(80);
44
45 BEGIN
46
47 /* Call the Sourcing side API to get the user name for the sourcing
48 professional to whom the notification is to be sent */
49 x_progress := '001';
50 PON_AUCTION_INTERFACE_PKG.Get_Negotiation_Owner(x_negotiation_id,
51 OwnerName,
52 x_result,
53 x_error_code,
54 x_error_msg);
55
56
57 /* Get the item type and item key */
58 x_progress := '002';
59 select to_char(PO_WF_ITEMKEY_S.NEXTVAL) into l_seq from sys.dual;
60
61 ItemKey := to_char(nvl(x_requisition_doc_id,x_process_id)) || '-' || l_seq;
62 ItemType := 'PONGRQCH' ;
63 WorkflowProcess := 'PO_NEG_REQ_CHANGE';
64
65 /* Start the workflow */
66 x_progress := '003';
67
68 Start_WF_Process ( ItemType,
69 ItemKey,
70 WorkflowProcess,
71 x_calling_program,
72 x_requisition_doc_id,
73 x_negotiation_num,
74 OwnerName ,
75 x_process_id);
76
77 commit;
78
79 EXCEPTION
80 WHEN OTHERS THEN
81
82 po_message_s.sql_error('In Exception of Req_Change_workflow_startup()', x_progress, sqlcode);
83
84 END;
85
86 /*============================================================================
87 Name: Start_wf_process
88 DESC: notifications to sourcing professional when req details are changed
89 or cancelled procedure to start the wf
90 ==============================================================================*/
91 PROCEDURE Start_WF_Process ( ItemType IN VARCHAR2,
92 ItemKey IN VARCHAR2,
93 WorkflowProcess IN VARCHAR2,
94 Source IN VARCHAR2,
95 DocumentId IN NUMBER,
96 NegotiationNum IN VARCHAR2,
97 OwnerName IN VARCHAR2,
98 ProcessId IN NUMBER) IS
99
100 x_progress varchar2(300);
101 x_wf_created number;
102 l_message_sub varchar2(2000);
103 l_message_sub1 varchar2(2000);
104 l_message_body varchar2(2000);
105 x_user_display_name varchar2(240) := null;
106 x_document VARCHAR2(32000) := '';
107 x_source varchar2(60);
108 x_org_id number;
109 x_req_num varchar2(20);
110
111 BEGIN
112
113 IF ( ItemType is NOT NULL ) AND
114 ( ItemKey is NOT NULL) THEN
115
116 -- check to see if process has already been created
117 -- if it has, don't create process again.
118 begin
119 select count(*)
120 into x_wf_created
121 from wf_items
122 where item_type = ItemType
123 and item_key = ItemKey;
124 exception
125 when others then
126 null;
127 end;
128
129 commit;
130
131 if x_wf_created = 0 then
132 wf_engine.CreateProcess( ItemType => ItemType,
133 ItemKey => ItemKey,
134 process => WorkflowProcess);
135
136
137 end if;
138
139 -- get the message subject to be sent
140 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_S');
141 fnd_message.set_token('NEG_NUM', NegotiationNum);
142 l_message_sub := fnd_message.get;
143
144 IF Source = 'MRP' THEN
145 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_SM');
146 fnd_message.set_token('NEG_NUM', NegotiationNum);
147 l_message_sub1 := fnd_message.get;
148 ELSIF Source = 'WITHDRAW' THEN
149 select segment1 into x_req_num
150 from po_requisition_headers_all
151 where requisition_header_id = DocumentId;
152
153 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_SW');
154 fnd_message.set_token('NEG_NUM', NegotiationNum);
155 fnd_message.set_token('REQ_NUM', x_req_num);
156 l_message_sub1 := fnd_message.get;
157 END IF;
158
159 /* get the current org_id */
160 begin
161 select org_id
162 into x_org_id
163 from po_system_parameters;
164 exception
165 when others then
166 null;
167 end;
168
169 PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ; -- <R12 MOAC>
170
171 -- Initialize workflow item attributes
172 wf_engine.SetItemAttrNumber ( itemtype => ItemType,
173 itemkey => ItemKey,
174 aname => 'ORG_ID',
175 avalue => x_org_id);
176
177 --
178 wf_engine.SetItemAttrText ( itemtype => ItemType,
179 itemkey => ItemKey,
180 aname => 'NEGOTIATION_NUM',
181 avalue => NegotiationNum);
182 --
183 wf_engine.SetItemAttrNumber ( itemtype => ItemType,
184 itemkey => ItemKey,
185 aname => 'DOCUMENT_ID',
186 avalue => DocumentId);
187 --
188 wf_engine.SetItemAttrText ( itemtype => ItemType,
189 itemkey => ItemKey,
190 aname => 'SOURCE',
191 avalue => Source);
192
193 --
194 wf_engine.SetItemAttrNumber ( itemtype => ItemType,
195 itemkey => ItemKey,
196 aname => 'MRP_PROCESSID',
197 avalue => ProcessId);
198 --
199 wf_engine.SetItemAttrText ( itemtype => ItemType,
200 itemkey => ItemKey,
201 aname => 'USER_NAME' ,
202 avalue => OwnerName);
203
204 --
205 wf_engine.SetItemAttrText ( itemtype => ItemType,
206 itemkey => ItemKey,
207 aname => 'USER_DISPLAY_NAME' ,
208 avalue => x_user_display_name);
209
210
211 /* build the message body and set the attribute */
212 IF Source in ('MRP','WITHDRAW') THEN
213
214 -- Bug 3346038
215 po_negotiation_req_notif.set_req_line_details_mrp_wd(ItemType,ItemKey,x_document);
216
217 wf_engine.SetItemAttrText(itemtype => Itemtype,
218 itemkey => ItemKey,
219 aname => 'PO_REQ_CHN_MSG_SUB_MRP',
220 avalue => l_message_sub1);
221
222 -- Bug 3346038
223 wf_engine.SetItemAttrText(itemtype => Itemtype,
224 itemkey => ItemKey,
225 aname => 'PO_REQ_CHN_MSG_BODY_TMP',
226 avalue => x_document);
227
228 wf_engine.SetItemAttrText(itemtype => Itemtype,
229 itemkey => ItemKey,
230 aname => 'PO_REQ_CHN_MSG_BODY_MRP',
231 -- Bug 3346038, Should use PLSQLCLOB
232 -- avalue => x_document);
233 avalue =>
234 'PLSQLCLOB:PO_NEGOTIATION_REQ_NOTIF.GET_REQ_LINE_DETAILS_MRP_WD/'||
235 itemtype||':'||itemkey);
236 ELSE
237
238 wf_engine.SetItemAttrText(itemtype => Itemtype,
239 itemkey => ItemKey,
240 aname => 'PO_REQ_CHN_MSG_SUB',
241 avalue => l_message_sub);
242
243 wf_engine.SetItemAttrText(itemtype => itemtype,
244 itemkey => itemkey,
245 aname => 'PO_REQ_CHN_MSG_BODY',
246 avalue =>
247 'PLSQLCLOB:PO_NEGOTIATION_REQ_NOTIF.GET_REQ_LINE_DETAILS/'||
248 itemtype||':'||
249 itemkey);
250
251 END IF;
252
253 /* Start the workflow process */
254
255 wf_engine.StartProcess(itemtype => itemtype,
256 itemkey => itemkey );
257
258
259
260 END IF;
261
262 EXCEPTION
263 WHEN OTHERS THEN
264
265 po_message_s.sql_error('In Exception of Req_Change_workflow_startup()', x_progress, sqlcode);
266
267 END;
268
269 /*============================================================================
270 Procedure to build the message body
271 ==============================================================================*/
272 PROCEDURE get_req_line_details(document_id in varchar2,
273 display_type in Varchar2,
274 document in out NOCOPY clob,
275 document_type in out NOCOPY varchar2) IS
276
277 CURSOR line_csr_h(v_document_id NUMBER,
278 v_negotiation_num varchar2 ) IS
279 SELECT rql.requisition_line_id,
280 rql.line_num,
281 rql.auction_line_number,
282 rqh.segment1,
283 msi.concatenated_segments,
284 rql.item_description,
285 rql.unit_meas_lookup_code,
286 rql.quantity,
287 rql.need_by_date,
288 rql.cancel_flag,
289 rql.closed_code,
290 null,
291 null
292 ,rql.auction_header_id --Bug 4107528
293 FROM po_requisition_lines rql,
294 po_requisition_headers_all rqh, -- <R12 MOAC>
295 mtl_system_items_kfv msi
296 WHERE rql.requisition_header_id = rqh.requisition_header_id
297 and rql.requisition_header_id = v_document_id
298 and rql.auction_display_number = v_negotiation_num
299 AND rql.at_sourcing_flag = 'Y' --<REQINPOOL>
300 and (rql.cancel_flag = 'Y' or rql.closed_code = 'FINALLY CLOSED')
301 and (trunc(rql.cancel_date) = trunc(sysdate) or trunc(rql.closed_date) = trunc(sysdate))
302 AND rql.item_id = msi.inventory_item_id(+)
303 AND nvl(msi.organization_id, rql.destination_organization_id) =
304 rql.destination_organization_id
305 ORDER BY rql.auction_line_number;
306
307 CURSOR line_csr_l(v_document_id NUMBER) IS
308 SELECT rql.requisition_line_id,
309 rql.line_num,
310 rql.auction_line_number,
311 rqh.segment1,
312 msi.concatenated_segments,
313 rql.item_description,
314 rql.unit_meas_lookup_code,
315 rql.quantity,
316 rql.need_by_date,
317 rql.cancel_flag,
318 rql.closed_code,
319 null,
320 null
321 ,rql.auction_header_id --Bug 4107528
322 FROM po_requisition_lines rql,
323 po_requisition_headers_all rqh, -- <R12 MOAC>
324 mtl_system_items_kfv msi
325 WHERE rql.requisition_header_id = rqh.requisition_header_id
326 and rql.requisition_line_id = v_document_id
327 AND rql.item_id = msi.inventory_item_id(+)
328 AND nvl(msi.organization_id, rql.destination_organization_id) =
329 rql.destination_organization_id;
330
331 l_status varchar2(60) :=null;
332 l_line line_record;
333 NL VARCHAR2(1) := fnd_global.newline;
334 l_document varchar2(32000) := '';
335 l_document_id number;
336 l_item_type wf_items.item_type%TYPE;
337 l_item_key wf_items.item_key%TYPE;
338 l_message_text varchar2(2000);
339 l_message_text1 varchar2(2000);
340 l_negotiation_num varchar2(50);
341 l_source varchar2(60);
342 l_org_id number;
343 l_process_id number;
344 x_progress varchar2(3) := null;
345 l_display_neg_line_num VARCHAR2(25); --Bug 4107528
346
347 BEGIN
348
349 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
350 l_item_key := substr(document_id, instr(document_id, ':') + 1,
351 length(document_id) - 2);
352
353 x_progress := '000';
354
355 /* Get all the attribute values needed to build the body */
356 l_org_id := wf_engine.GetItemAttrNumber
357 (itemtype => l_item_type,
358 itemkey => l_item_key,
359 aname => 'ORG_ID');
360
361 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
362
363 x_progress := '001';
364 l_document_id := wf_engine.GetItemAttrNumber
365 (itemtype => l_item_type,
366 itemkey => l_item_key,
367 aname => 'DOCUMENT_ID');
368
369 x_progress := '002';
370 l_negotiation_num := wf_engine.GetItemAttrText
371 (itemtype => l_item_type,
372 itemkey => l_item_key,
373 aname => 'NEGOTIATION_NUM');
374
375 x_progress := '003';
376 l_source := wf_engine.GetItemAttrText
377 (itemtype => l_item_type,
378 itemkey => l_item_key,
379 aname => 'SOURCE');
380
381 x_progress := '004';
382
383 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_B');
384 fnd_message.set_token('NEG_NUM', l_negotiation_num);
385 l_message_text := fnd_message.get;
386
387 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_B1');
388 fnd_message.set_token('NEG_NUM', l_negotiation_num);
389 l_message_text1 := fnd_message.get;
390
391 x_progress := '006';
392 if (display_type = 'text/html') then
393
394 /* Construct the table header */
395
396 l_document := l_document || '<p>' || l_message_text || '</p><br>' ;
397
398 l_document := l_document || '<B>' || fnd_message.get_string('PO', 'PO_SOURCING_REQ_TABLE_TITLE') || '</B>' ;
399
400 l_document := l_document || '<TABLE WIDTH=100% border=1 cellpadding=2 cellspacing=1>';
401
402 l_document := l_document || '<TR align=left>';
403
404 l_document := l_document || '<TH class="tableheader" nowrap>' ||
405 fnd_message.get_string('PO', 'PO_SOURCING_NEG_LINE_NUMBER') || '</TH>' || NL;
406
407 l_document := l_document || '<TH class="tableheader" nowrap>'
408 || fnd_message.get_string('PO', 'PO_SOURCING_REQ_NUMBER') || '</TH>' || NL;
409
410 l_document := l_document || '<TH class="tableheader" nowrap>' ||
411 fnd_message.get_string('PO', 'PO_SOURCING_LINE_NUMBER') || '</TH>' || NL;
412
413 l_document := l_document || '<TH class="tableheader" nowrap>' ||
414 fnd_message.get_string('PO', 'PO_SOURCING_ITEM_NUMBER') || '</TH>' || NL;
415
416 l_document := l_document || '<TH class="tableheader" nowrap>' ||
417 fnd_message.get_string('PO', 'PO_SOURCING_ITEM_DESC') || '</TH>' || NL;
418
419 l_document := l_document || '<TH class="tableheader" nowrap>' ||
420 fnd_message.get_string('PO', 'PO_SOURCING_UOM') || '</TH>' || NL;
421
422 l_document := l_document || '<TH class="tableheader" nowrap>' ||
423 fnd_message.get_string('PO', 'PO_SOURCING_QUANTITY') || '</TH>' || NL;
424
425 l_document := l_document || '<TH class="tableheader" nowrap>' ||
426 fnd_message.get_string('PO', 'PO_SOURCING_NEED_BY_DATE') ||'</TH>' || NL;
427
428 l_document := l_document || '<TH class="tableheader" nowrap>' ||
429 fnd_message.get_string('PO', 'PO_SOURCING_STATUS') || '</TH>' || NL;
430
431 l_document := l_document || '</TR>';
432
433 /* open the relevent cursor to get the requisition data */
434 x_progress := '007';
435 IF l_source = 'REQ HEADER' THEN
436 open line_csr_h(l_document_id,l_negotiation_num);
437 ELSIF l_source = 'REQ LINE' THEN
438 open line_csr_l(l_document_id);
439 END IF;
440
441 loop
442
443 IF l_source = 'REQ HEADER' THEN
444 fetch line_csr_h into l_line;
445 exit when line_csr_h%notfound;
446 ELSIF l_source = 'REQ LINE' THEN
447 fetch line_csr_l into l_line;
448 exit when line_csr_l%notfound;
449 END IF;
450
451 x_progress := '008';
452 /* Construct the table body */
453
454 l_document := l_document || '<TR>' || NL;
455
456 --Bug 4107528 Start: retrieve the displayed auction line number
457 PO_NEGOTIATIONS_SV1.get_auction_display_line_num(
458 p_auction_header_id => l_line.auction_header_id,
459 p_auction_line_number => l_line.neg_line_num,
460 x_auction_display_line_num => l_display_neg_line_num);
461
462 l_document := l_document || '<TD class=tabledata align=left>' ||
463 nvl(to_char(l_display_neg_line_num), ' ') || '</TD>' || NL;
464 --Bug 4107528 End
465
466 --l_document := l_document || '<TD class=tabledata align=left>' ||
467 -- nvl(to_char(l_line.neg_line_num), ' ') || '</TD>' || NL ;
468
469 l_document := l_document || '<TD class=tabledata align=left>' ||
470 nvl(l_line.req_num, ' ') || '</TD>' || NL;
471
472 l_document := l_document || '<TD class=tabledata align=left>' ||
473 nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
474
475 l_document := l_document || '<TD class=tabledata align=left>' ||
476 nvl(l_line.item_num, ' ') || '</TD>' || NL;
477
478 l_document := l_document || '<TD class=tabledata align=left>' ||
479 nvl(l_line.item_desc, ' ') || '</TD>' || NL;
480
481 l_document := l_document || '<TD class=tabledata align=left>' ||
482 nvl(l_line.uom, ' ') || '</TD>' || NL;
483
484 l_document := l_document || '<TD class=tabledata align=left>' ||
485 nvl(to_char(l_line.quantity), ' ') || '</TD>' || NL;
486
487 l_document := l_document || '<TD class=tabledata align=left>' ||
488 nvl(to_char(l_line.need_by_date , FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
489 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''), ' ') || '</TD>' || NL;
490
491
492 x_progress := '009';
493 if l_line.cancel_flag = 'Y' then
494 po_headers_sv4.get_lookup_code_dsp ('DOCUMENT STATE',
495 'CANCELLED',
496 l_status);
497 elsif l_line.closed_code = 'FINALLY CLOSED' then
498 po_headers_sv4.get_lookup_code_dsp ('DOCUMENT STATE',
499 'FINALLY CLOSED',
500 l_status);
501
502 end if;
503
504 l_document := l_document || '<TD class=tabledata align=left>' ||
505 nvl(l_status, ' ') || '</TD>' || NL;
506
507 l_document := l_document || '</TR>' ;
508
509 /* writing the body into a clob variable */
510 WF_NOTIFICATION.WriteToClob(document, l_document);
511 l_document := null;
512
513 end loop;
514
515 IF l_source = 'REQ HEADER' THEN
516 close line_csr_h;
517 ELSIF l_source = 'REQ LINE' THEN
518 close line_csr_l;
519 END IF;
520
521 x_progress := '010';
522
523 if l_document is null then
524 l_document := l_document || '</TABLE>';
525
526 l_document := l_document || '<br><p>' || l_message_text1 || '</p><br>' ;
527
528 -- document := l_document;
529 WF_NOTIFICATION.WriteToClob(document, l_document);
530 end if;
531
532 end if;
533
534 EXCEPTION
535 WHEN OTHERS THEN
536 po_message_s.sql_error('In Exception of Req_Change_workflow_startup()', x_progress, sqlcode);
537 END;
538
539 /*============================================================================
540 Procedure to build the message body when called from MRP reschedule
541 ==============================================================================*/
542 -- Bug 3346038
543 -- PROCEDURE get_req_line_details_mrp_wd(itemtype in varchar2,
544 PROCEDURE set_req_line_details_mrp_wd(itemtype in varchar2,
545 itemkey in varchar2,
546 x_document in out NOCOPY varchar2) IS
547
548 CURSOR resc_csr(v_negotiation_num VARCHAR2,
549 v_process_id NUMBER) IS
550 SELECT rql.requisition_line_id,
551 rql.line_num,
552 rql.auction_line_number,
553 rqh.segment1,
554 msi.concatenated_segments,
555 rql.item_description,
556 rql.unit_meas_lookup_code,
557 rql.quantity,
558 rql.need_by_date,
559 rql.cancel_flag,
560 null,
561 resc.orig_quantity,
562 resc.orig_need_by_date
563 ,rql.auction_header_id --Bug 4107528
564 FROM po_reschedule_interface resc,
565 po_requisition_lines rql,
566 po_requisition_headers_all rqh, -- <R12 MOAC>
567 mtl_system_items_kfv msi
568 WHERE resc.auction_display_number = v_negotiation_num
569 and resc.process_id = v_process_id
570 and resc.line_id = rql.requisition_line_id
571 and rql.requisition_header_id = rqh.requisition_header_id
572 AND rql.item_id = msi.inventory_item_id(+)
573 AND nvl(msi.organization_id, rql.destination_organization_id) =
574 rql.destination_organization_id;
575
576 CURSOR wdraw_csr(v_negotiation_num VARCHAR2,
577 v_document_id NUMBER) IS
578 SELECT rql.requisition_line_id,
579 rql.line_num,
580 rql.auction_line_number,
581 rqh.segment1,
582 msi.concatenated_segments,
583 rql.item_description,
584 rql.unit_meas_lookup_code,
585 rql.quantity,
586 rql.need_by_date,
587 rql.cancel_flag,
588 null,
589 null,
590 null
591 ,rql.auction_header_id --Bug 4107528
592 FROM po_requisition_lines rql,
593 po_requisition_headers_all rqh, -- <R12 MOAC>
594 mtl_system_items_kfv msi
595 WHERE rql.requisition_header_id = rqh.requisition_header_id
596 and rql.requisition_header_id = v_document_id
597 and rql.auction_display_number = v_negotiation_num
598 AND at_sourcing_flag = 'Y' --<REQINPOOL>
599 AND rql.item_id = msi.inventory_item_id(+)
600 AND nvl(msi.organization_id, rql.destination_organization_id) =
601 rql.destination_organization_id
602 ORDER BY rql.auction_line_number;
603
604 l_status varchar2(60) :=null;
605 l_line line_record;
606 NL VARCHAR2(1) := fnd_global.newline;
607 l_document varchar2(32000) := '';
608 l_document_id number;
609 l_item_type wf_items.item_type%TYPE;
610 l_item_key wf_items.item_key%TYPE;
611 l_message_text varchar2(2000) := null;
612 l_message_text1 varchar2(2000) := null;
613 l_message_ct varchar2(240);
614 l_negotiation_num varchar2(50);
615 l_source varchar2(60);
616 l_org_id number;
617 l_process_id number;
618 x_progress varchar2(3) := null;
619 i number := 0;
620 l_display_neg_line_num VARCHAR2(25); --Bug 4107528
621
622 BEGIN
623
624 l_item_type := itemtype;
625 l_item_key := itemkey;
626
627 x_progress := '000';
628 l_org_id := wf_engine.GetItemAttrNumber
629 (itemtype => l_item_type,
630 itemkey => l_item_key,
631 aname => 'ORG_ID');
632
633 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
634
635 x_progress := '002';
636 l_negotiation_num := wf_engine.GetItemAttrText
637 (itemtype => l_item_type,
638 itemkey => l_item_key,
639 aname => 'NEGOTIATION_NUM');
640
641 x_progress := '003';
642 l_source := wf_engine.GetItemAttrText
643 (itemtype => l_item_type,
644 itemkey => l_item_key,
645 aname => 'SOURCE');
646
647 x_progress := '004';
648 l_process_id := wf_engine.GetItemAttrNumber
649 (itemtype => l_item_type,
650 itemkey => l_item_key,
651 aname => 'MRP_PROCESSID');
652
653 x_progress := '005';
654 l_document_id := wf_engine.GetItemAttrNumber
655 (itemtype => l_item_type,
656 itemkey => l_item_key,
657 aname => 'DOCUMENT_ID');
658
659 x_progress := '006';
660
661 IF l_source = 'MRP' THEN
662 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_BM');
663 fnd_message.set_token('NEG_NUM', l_negotiation_num);
664 l_message_text := fnd_message.get;
665 ELSIF l_source = 'WITHDRAW' THEN
666 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_BW');
667 fnd_message.set_token('NEG_NUM', l_negotiation_num);
668 l_message_text := fnd_message.get;
669 END IF;
670
671 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_B1');
672 fnd_message.set_token('NEG_NUM', l_negotiation_num);
673 l_message_text1 := fnd_message.get;
674
675 l_document := l_document || '<p>' || l_message_text || '</p><br>' || NL;
676
677 l_document := l_document || '<B>' || fnd_message.get_string('PO', 'PO_SOURCING_REQ_TABLE_TITLE') || '</B>' || NL;
678
679 l_document := l_document || '<TABLE WIDTH=100% border=1 cellpadding=2 cellspacing=1>'|| NL;
680
681 l_document := l_document || '<TR align=left>'|| NL;
682
683 l_document := l_document || '<TH class="tableheader" nowrap>' ||
684 fnd_message.get_string('PO', 'PO_SOURCING_NEG_LINE_NUMBER') || '</TH>' || NL;
685
686 IF l_source = 'MRP' THEN
687 l_document := l_document || '<TH class="tableheader" nowrap>'
688 || fnd_message.get_string('PO', 'PO_SOURCING_REQ_NUMBER') || '</TH>' || NL;
689 END IF;
690
691 l_document := l_document || '<TH class="tableheader" nowrap>' ||
692 fnd_message.get_string('PO', 'PO_SOURCING_LINE_NUMBER') || '</TH>'|| NL;
693
694 l_document := l_document || '<TH class="tableheader" nowrap>' ||
695 fnd_message.get_string('PO', 'PO_SOURCING_ITEM_NUMBER') || '</TH>'|| NL;
696
697 l_document := l_document || '<TH class="tableheader" nowrap>' ||
698 fnd_message.get_string('PO', 'PO_SOURCING_ITEM_DESC') || '</TH>'|| NL;
699
700 l_document := l_document || '<TH class="tableheader" nowrap>' ||
701 fnd_message.get_string('PO', 'PO_SOURCING_UOM') || '</TH>' || NL;
702
703 l_document := l_document || '<TH class="tableheader" nowrap>' ||
704 fnd_message.get_string('PO', 'PO_SOURCING_QUANTITY') || '</TH>' || NL;
705
706 l_document := l_document || '<TH class="tableheader" nowrap>' ||
707 fnd_message.get_string('PO', 'PO_SOURCING_NEED_BY_DATE') ||'</TH>' || NL;
708
709 IF l_source = 'MRP' THEN
710
711 l_document := l_document || '<TH class="tableheader" nowrap>' ||
712 fnd_message.get_string('PO', 'PO_SOURCING_NEW_QUANTITY') || '</TH>' || NL;
713
714 l_document := l_document || '<TH class="tableheader" nowrap>' ||
715 fnd_message.get_string('PO', 'PO_SOURCING_NEW_NEED_BY_DATE') ||'</TH>' || NL;
716 END IF;
717
718 l_document := l_document || '<TH class="tableheader" nowrap>' ||
719 fnd_message.get_string('PO', 'PO_SOURCING_STATUS') || '</TH>' || NL;
720
721 l_document := l_document || '</TR>'|| NL;
722
723 x_progress := '007';
724
725 IF l_source = 'MRP' THEN
726 open resc_csr(l_negotiation_num,l_process_id);
727 ELSIF l_source = 'WITHDRAW' THEN
728 open wdraw_csr(l_negotiation_num,l_document_id);
729 END IF;
730
731 loop
732 IF l_source = 'MRP' THEN
733 fetch resc_csr into l_line;
734 ELSIF l_source = 'WITHDRAW' THEN
735 fetch wdraw_csr into l_line;
736 END IF;
737
738 i := i + 1;
739
740 IF l_source = 'MRP' THEN
741 exit when resc_csr%notfound;
742 ELSIF l_source = 'WITHDRAW' THEN
743 exit when wdraw_csr%notfound;
744 END IF;
745
746 x_progress := '008';
747
748 l_document := l_document || '<TR>' || NL;
749
750 --Bug 4107528 Start: retrieve the displayed auction line number
751 PO_NEGOTIATIONS_SV1.get_auction_display_line_num(
752 p_auction_header_id => l_line.auction_header_id,
753 p_auction_line_number => l_line.neg_line_num,
754 x_auction_display_line_num => l_display_neg_line_num);
755
756 l_document := l_document || '<TD class=tabledata align=left>' ||
757 nvl(to_char(l_display_neg_line_num), ' ') || '</TD>' || NL;
758 --Bug 4107528 End
759
760 --l_document := l_document || '<TD class=tabledata align=left>' ||
761 -- nvl(to_char(l_line.neg_line_num), ' ') || '</TD>' || NL;
762
763 IF l_source = 'MRP' THEN
764 l_document := l_document || '<TD class=tabledata align=left>' ||
765 nvl(l_line.req_num, ' ') || '</TD>' || NL;
766 END IF;
767
768 l_document := l_document || '<TD class=tabledata align=left>' ||
769 nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
770
771 l_document := l_document || '<TD class=tabledata align=left>' ||
772 nvl(l_line.item_num, ' ') || '</TD>' || NL;
773
774 l_document := l_document || '<TD class=tabledata align=left>' ||
775 nvl(l_line.item_desc, ' ') || '</TD>' || NL;
776
777 l_document := l_document || '<TD class=tabledata align=left>' ||
778 nvl(l_line.uom, ' ') || '</TD>' || NL;
779
780 IF l_source = 'MRP' THEN
781 l_document := l_document || '<TD class=tabledata align=left>' ||
782 nvl(to_char(l_line.old_qty), ' ') || '</TD>' || NL;
783
784 l_document := l_document || '<TD class=tabledata align=left>' ||
785 nvl(to_char(l_line.old_need_by_date , FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
786 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''), ' ') || '</TD>' || NL;
787
788 END IF;
789
790 l_document := l_document || '<TD class=tabledata align=left>' ||
791 nvl(to_char(l_line.quantity), ' ') || '</TD>' || NL;
792
793 l_document := l_document || '<TD class=tabledata align=left>' ||
794 nvl(to_char(l_line.need_by_date , FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
795 'NLS_CALENDAR = ''' || FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) || ''''), ' ') || '</TD>' || NL;
796
797 x_progress := '009';
798
799 IF l_source = 'MRP' THEN
800 if l_line.cancel_flag = 'Y' then
801 po_headers_sv4.get_lookup_code_dsp ('DOCUMENT STATE',
802 'CANCELLED',
803 l_status);
804 else
805 fnd_message.set_name ('PO','PO_SOURCING_RESCHEDULE');
806 l_status := fnd_message.get;
807 end if;
808
809 ELSIF l_source = 'WITHDRAW' THEN
810 fnd_message.set_name ('PO','PO_SOURCING_WITHDRAW');
811 l_status := fnd_message.get;
812 END IF;
813
814
815 l_document := l_document || '<TD class=tabledata align=left>' ||
816 nvl(l_status, ' ') || '</TD>' || NL;
817
818 l_document := l_document || '</TR>' || NL;
819 x_progress := '010';
820
821 exit when i >= 5; -- Bug 2395868 (anhuang)
822 -- Workflow attribute can hold a maximum of just 6 lines.
823 -- Set exit condition to 5 lines to be cleaner.
824 end loop;
825
826 IF l_source = 'MRP' THEN
827 close resc_csr;
828 ELSIF l_source = 'WITHDRAW' THEN
829 close wdraw_csr;
830 END IF;
831
832 l_document := l_document || '</TABLE>'|| NL;
833
834 l_document := l_document || '<br><p>' || l_message_text1 || '</p><br>' || NL;
835
836 x_document := l_document;
837
838 exception
839 when others then
840 null;
841
842 END;
843
844 -- Bug 3346038
845 /*============================================================================
846 Procedure to build the message body when called from MRP reschedule
847 ==============================================================================*/
848 PROCEDURE get_req_line_details_mrp_wd(document_id in varchar2,
849 display_type in Varchar2,
850 document in out NOCOPY clob,
851 document_type in out NOCOPY varchar2) IS
852
853 NL VARCHAR2(1) := fnd_global.newline;
854 l_document varchar2(32000) := '';
855 l_item_type wf_items.item_type%TYPE;
856 l_item_key wf_items.item_key%TYPE;
857 l_message_text varchar2(30000) := null;
858 x_progress varchar2(3) := null;
859
860 BEGIN
861 x_progress := '005';
862 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
863 l_item_key := substr(document_id, instr(document_id, ':') + 1,
864 length(document_id) - 2);
865
866 x_progress := '010';
867 l_message_text := wf_engine.GetItemAttrText
868 (itemtype => l_item_type,
869 itemkey => l_item_key,
870 aname => 'PO_REQ_CHN_MSG_BODY_TMP');
871
872 x_progress := '020';
873 IF (display_type = 'text/html') THEN
874
875 l_document := l_document || l_message_text || NL;
876
877 x_progress := '030';
878 WF_NOTIFICATION.WriteToClob(document, l_document);
879
880 -- Bug 3346038, Should use PLSQLCLOB
881 END IF; /* IF (display_type = 'text/html') */
882
883 x_progress := '040';
884 exception
885 when others then
886 po_message_s.sql_error('In Exception of get_req_line_details_mrp_wd()', x_progress, sqlcode);
887
888 END;
889
890
891 /*============================================================================
892 Wrapper to group the requisition lines by negotiation and call the WF
893 ==============================================================================*/
894 PROCEDURE call_negotiation_wf(x_control_level IN VARCHAR2,
895 x_document_id IN NUMBER) is
896
897 cursor c1(x_doc_id in number) is
898 select auction_header_id,
899 auction_display_number
900 from po_requisition_lines
901 where requisition_line_id = x_doc_id
902 AND at_sourcing_flag = 'Y'; --<REQINPOOL>
903
904 cursor c2(x_doc_id in number) is
905 select distinct auction_header_id ,
906 auction_display_number
907 from po_requisition_lines
908 where requisition_header_id = x_doc_id
909 AND at_sourcing_flag = 'Y' --<REQINPOOL>
910 and (cancel_flag = 'Y' or closed_code = 'FINALLY CLOSED')
911 and (trunc(cancel_date) = trunc(sysdate) or trunc(closed_date) = trunc(sysdate));
912
913 cursor c3(x_doc_id in number) is
914 select distinct auction_header_id ,
915 auction_display_number
916 from po_requisition_lines
917 where requisition_header_id = x_doc_id
918 AND at_sourcing_flag = 'Y'; --<REQINPOOL>
919
920 cursor interface(v_process_id in number) is
921 select distinct auction_header_id,
922 auction_display_number
923 from po_reschedule_interface
924 where auction_header_id is not null
925 and process_id = v_process_id;
926
927 x_auction_num varchar2(60);
928 x_auction_header_id number;
929 x_process_id number;
930 x_sourcing_install_status varchar2(1);
931
932 BEGIN
933
934 /* check if sourcing is installed */
935 PO_SETUP_S1.GET_SOURCING_STARTUP(x_sourcing_install_status);
936 if x_sourcing_install_status <> 'I' then
937 return;
938 end if;
939
940 /* Depending on the control level open the correct cursor to group the req
941 lines by negotiation and call the wf process */
942
943 if (x_control_level = 'REQ LINE') then
944
945 open c1(x_document_id);
946 loop
947 fetch c1 into x_auction_header_id,
948 x_auction_num ;
949
950 EXIT WHEN c1%NOTFOUND;
951
952 po_negotiation_req_notif.req_change_workflow_startup(x_control_level,
953 x_auction_header_id ,
954 x_auction_num,
955 x_document_id);
956
957 end loop;
958 close c1;
959
960 elsif (x_control_level = 'REQ HEADER') then
961
962 open c2(x_document_id);
963 loop
964 fetch c2 into x_auction_header_id,
965 x_auction_num ;
966 EXIT WHEN c2%NOTFOUND;
967
968 po_negotiation_req_notif.req_change_workflow_startup(x_control_level,
969 x_auction_header_id ,
970 x_auction_num,
971 x_document_id);
972
973 end loop;
974 close c2;
975
976 elsif (x_control_level = 'WITHDRAW') then
977
978 open c3(x_document_id);
979 loop
980 fetch c3 into x_auction_header_id,
981 x_auction_num ;
982 EXIT WHEN c3%NOTFOUND;
983
984 po_negotiation_req_notif.req_change_workflow_startup(x_control_level,
985 x_auction_header_id ,
986 x_auction_num,
987 x_document_id);
988 end loop;
989 close c3;
990
991 elsif (x_control_level = 'MRP') then
992
993 x_process_id := x_document_id;
994 open interface(x_process_id);
995
996 loop
997
998 fetch interface into x_auction_header_id,
999 x_auction_num ;
1000 exit when interface%notfound;
1001
1002 po_negotiation_req_notif.req_change_workflow_startup(x_control_level,
1003 x_auction_header_id,
1004 x_auction_num,
1005 null,
1006 x_process_id);
1007
1008
1009
1010
1011 end loop;
1012
1013 close interface;
1014
1015 end if;
1016
1017 exception
1018 when others then
1019 null;
1020
1021 END;
1022
1023 /*============================================================================
1024 Procedure tocheck where the wf is being called from so as to decide
1025 the correct notification to be sent
1026 ==============================================================================*/
1027 procedure Check_Source( itemtype in varchar2,
1028 itemkey in varchar2,
1029 actid in number,
1030 funcmode in varchar2,
1031 resultout out NOCOPY varchar2 ) is
1032 l_source varchar2(30);
1033 begin
1034 if (funcmode <> wf_engine.eng_run) then
1035
1036 resultout := wf_engine.eng_null;
1037 return;
1038
1039 end if;
1040
1041 l_source := wf_engine.GetItemAttrText
1042 (itemtype => itemtype,
1043 itemkey => itemkey,
1044 aname => 'SOURCE');
1045
1046 if l_source in ('MRP','WITHDRAW') then
1047 resultout := wf_engine.eng_completed || ':' || 'MRP';
1048 else
1049 resultout := wf_engine.eng_completed || ':' || 'OTHERS';
1050 end if;
1051 end;
1052 END po_negotiation_req_notif;