[Home] [Help]
PACKAGE BODY: APPS.PO_NEGOTIATION_REQ_NOTIF
Source
1 PACKAGE BODY po_negotiation_req_notif AS
2 /* $Header: POXNEG2B.pls 120.0 2005/06/02 00:27:09 appldev noship $*/
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), ' ') || '</TD>' || NL;
489
490
491 x_progress := '009';
492 if l_line.cancel_flag = 'Y' then
493 po_headers_sv4.get_lookup_code_dsp ('DOCUMENT STATE',
494 'CANCELLED',
495 l_status);
496 elsif l_line.closed_code = 'FINALLY CLOSED' then
497 po_headers_sv4.get_lookup_code_dsp ('DOCUMENT STATE',
498 'FINALLY CLOSED',
499 l_status);
500
501 end if;
502
503 l_document := l_document || '<TD class=tabledata align=left>' ||
504 nvl(l_status, ' ') || '</TD>' || NL;
505
506 l_document := l_document || '</TR>' ;
507
508 /* writing the body into a clob variable */
509 WF_NOTIFICATION.WriteToClob(document, l_document);
510 l_document := null;
511
512 end loop;
513
514 IF l_source = 'REQ HEADER' THEN
515 close line_csr_h;
516 ELSIF l_source = 'REQ LINE' THEN
517 close line_csr_l;
518 END IF;
519
520 x_progress := '010';
521
522 if l_document is null then
523 l_document := l_document || '</TABLE>';
524
525 l_document := l_document || '<br><p>' || l_message_text1 || '</p><br>' ;
526
527 -- document := l_document;
528 WF_NOTIFICATION.WriteToClob(document, l_document);
529 end if;
530
531 end if;
532
533 EXCEPTION
534 WHEN OTHERS THEN
535 po_message_s.sql_error('In Exception of Req_Change_workflow_startup()', x_progress, sqlcode);
536 END;
537
538 /*============================================================================
539 Procedure to build the message body when called from MRP reschedule
540 ==============================================================================*/
541 -- Bug 3346038
542 -- PROCEDURE get_req_line_details_mrp_wd(itemtype in varchar2,
543 PROCEDURE set_req_line_details_mrp_wd(itemtype in varchar2,
544 itemkey in varchar2,
545 x_document in out NOCOPY varchar2) IS
546
547 CURSOR resc_csr(v_negotiation_num VARCHAR2,
548 v_process_id NUMBER) IS
549 SELECT rql.requisition_line_id,
550 rql.line_num,
551 rql.auction_line_number,
552 rqh.segment1,
553 msi.concatenated_segments,
554 rql.item_description,
555 rql.unit_meas_lookup_code,
556 rql.quantity,
557 rql.need_by_date,
558 rql.cancel_flag,
559 null,
560 resc.orig_quantity,
561 resc.orig_need_by_date
562 ,rql.auction_header_id --Bug 4107528
563 FROM po_reschedule_interface resc,
564 po_requisition_lines rql,
565 po_requisition_headers_all rqh, -- <R12 MOAC>
566 mtl_system_items_kfv msi
567 WHERE resc.auction_display_number = v_negotiation_num
568 and resc.process_id = v_process_id
569 and resc.line_id = rql.requisition_line_id
570 and rql.requisition_header_id = rqh.requisition_header_id
571 AND rql.item_id = msi.inventory_item_id(+)
572 AND nvl(msi.organization_id, rql.destination_organization_id) =
573 rql.destination_organization_id;
574
575 CURSOR wdraw_csr(v_negotiation_num VARCHAR2,
576 v_document_id NUMBER) IS
577 SELECT rql.requisition_line_id,
578 rql.line_num,
579 rql.auction_line_number,
580 rqh.segment1,
581 msi.concatenated_segments,
582 rql.item_description,
583 rql.unit_meas_lookup_code,
584 rql.quantity,
585 rql.need_by_date,
586 rql.cancel_flag,
587 null,
588 null,
589 null
590 ,rql.auction_header_id --Bug 4107528
591 FROM po_requisition_lines rql,
592 po_requisition_headers_all rqh, -- <R12 MOAC>
593 mtl_system_items_kfv msi
594 WHERE rql.requisition_header_id = rqh.requisition_header_id
595 and rql.requisition_header_id = v_document_id
596 and rql.auction_display_number = v_negotiation_num
597 AND at_sourcing_flag = 'Y' --<REQINPOOL>
598 AND rql.item_id = msi.inventory_item_id(+)
599 AND nvl(msi.organization_id, rql.destination_organization_id) =
600 rql.destination_organization_id
601 ORDER BY rql.auction_line_number;
602
603 l_status varchar2(60) :=null;
604 l_line line_record;
605 NL VARCHAR2(1) := fnd_global.newline;
606 l_document varchar2(32000) := '';
607 l_document_id number;
608 l_item_type wf_items.item_type%TYPE;
609 l_item_key wf_items.item_key%TYPE;
610 l_message_text varchar2(2000) := null;
611 l_message_text1 varchar2(2000) := null;
612 l_message_ct varchar2(240);
613 l_negotiation_num varchar2(50);
614 l_source varchar2(60);
615 l_org_id number;
616 l_process_id number;
617 x_progress varchar2(3) := null;
618 i number := 0;
619 l_display_neg_line_num VARCHAR2(25); --Bug 4107528
620
621 BEGIN
622
623 l_item_type := itemtype;
624 l_item_key := itemkey;
625
626 x_progress := '000';
627 l_org_id := wf_engine.GetItemAttrNumber
628 (itemtype => l_item_type,
629 itemkey => l_item_key,
630 aname => 'ORG_ID');
631
632 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
633
634 x_progress := '002';
635 l_negotiation_num := wf_engine.GetItemAttrText
636 (itemtype => l_item_type,
637 itemkey => l_item_key,
638 aname => 'NEGOTIATION_NUM');
639
640 x_progress := '003';
641 l_source := wf_engine.GetItemAttrText
642 (itemtype => l_item_type,
643 itemkey => l_item_key,
644 aname => 'SOURCE');
645
646 x_progress := '004';
647 l_process_id := wf_engine.GetItemAttrNumber
648 (itemtype => l_item_type,
649 itemkey => l_item_key,
650 aname => 'MRP_PROCESSID');
651
652 x_progress := '005';
653 l_document_id := wf_engine.GetItemAttrNumber
654 (itemtype => l_item_type,
655 itemkey => l_item_key,
656 aname => 'DOCUMENT_ID');
657
658 x_progress := '006';
659
660 IF l_source = 'MRP' THEN
661 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_BM');
662 fnd_message.set_token('NEG_NUM', l_negotiation_num);
663 l_message_text := fnd_message.get;
664 ELSIF l_source = 'WITHDRAW' THEN
665 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_BW');
666 fnd_message.set_token('NEG_NUM', l_negotiation_num);
667 l_message_text := fnd_message.get;
668 END IF;
669
670 fnd_message.set_name ('PO','PO_SOURCING_NOTIF_MSG_B1');
671 fnd_message.set_token('NEG_NUM', l_negotiation_num);
672 l_message_text1 := fnd_message.get;
673
674 l_document := l_document || '<p>' || l_message_text || '</p><br>' || NL;
675
676 l_document := l_document || '<B>' || fnd_message.get_string('PO', 'PO_SOURCING_REQ_TABLE_TITLE') || '</B>' || NL;
677
678 l_document := l_document || '<TABLE WIDTH=100% border=1 cellpadding=2 cellspacing=1>'|| NL;
679
680 l_document := l_document || '<TR align=left>'|| NL;
681
682 l_document := l_document || '<TH class="tableheader" nowrap>' ||
683 fnd_message.get_string('PO', 'PO_SOURCING_NEG_LINE_NUMBER') || '</TH>' || NL;
684
685 IF l_source = 'MRP' THEN
686 l_document := l_document || '<TH class="tableheader" nowrap>'
687 || fnd_message.get_string('PO', 'PO_SOURCING_REQ_NUMBER') || '</TH>' || NL;
688 END IF;
689
690 l_document := l_document || '<TH class="tableheader" nowrap>' ||
691 fnd_message.get_string('PO', 'PO_SOURCING_LINE_NUMBER') || '</TH>'|| NL;
692
693 l_document := l_document || '<TH class="tableheader" nowrap>' ||
694 fnd_message.get_string('PO', 'PO_SOURCING_ITEM_NUMBER') || '</TH>'|| NL;
695
696 l_document := l_document || '<TH class="tableheader" nowrap>' ||
697 fnd_message.get_string('PO', 'PO_SOURCING_ITEM_DESC') || '</TH>'|| NL;
698
699 l_document := l_document || '<TH class="tableheader" nowrap>' ||
700 fnd_message.get_string('PO', 'PO_SOURCING_UOM') || '</TH>' || NL;
701
702 l_document := l_document || '<TH class="tableheader" nowrap>' ||
703 fnd_message.get_string('PO', 'PO_SOURCING_QUANTITY') || '</TH>' || NL;
704
705 l_document := l_document || '<TH class="tableheader" nowrap>' ||
706 fnd_message.get_string('PO', 'PO_SOURCING_NEED_BY_DATE') ||'</TH>' || NL;
707
708 IF l_source = 'MRP' THEN
709
710 l_document := l_document || '<TH class="tableheader" nowrap>' ||
711 fnd_message.get_string('PO', 'PO_SOURCING_NEW_QUANTITY') || '</TH>' || NL;
712
713 l_document := l_document || '<TH class="tableheader" nowrap>' ||
714 fnd_message.get_string('PO', 'PO_SOURCING_NEW_NEED_BY_DATE') ||'</TH>' || NL;
715 END IF;
716
717 l_document := l_document || '<TH class="tableheader" nowrap>' ||
718 fnd_message.get_string('PO', 'PO_SOURCING_STATUS') || '</TH>' || NL;
719
720 l_document := l_document || '</TR>'|| NL;
721
722 x_progress := '007';
723
724 IF l_source = 'MRP' THEN
725 open resc_csr(l_negotiation_num,l_process_id);
726 ELSIF l_source = 'WITHDRAW' THEN
727 open wdraw_csr(l_negotiation_num,l_document_id);
728 END IF;
729
730 loop
731 IF l_source = 'MRP' THEN
732 fetch resc_csr into l_line;
733 ELSIF l_source = 'WITHDRAW' THEN
734 fetch wdraw_csr into l_line;
735 END IF;
736
737 i := i + 1;
738
739 IF l_source = 'MRP' THEN
740 exit when resc_csr%notfound;
741 ELSIF l_source = 'WITHDRAW' THEN
742 exit when wdraw_csr%notfound;
743 END IF;
744
745 x_progress := '008';
746
747 l_document := l_document || '<TR>' || NL;
748
749 --Bug 4107528 Start: retrieve the displayed auction line number
750 PO_NEGOTIATIONS_SV1.get_auction_display_line_num(
751 p_auction_header_id => l_line.auction_header_id,
752 p_auction_line_number => l_line.neg_line_num,
753 x_auction_display_line_num => l_display_neg_line_num);
754
755 l_document := l_document || '<TD class=tabledata align=left>' ||
756 nvl(to_char(l_display_neg_line_num), ' ') || '</TD>' || NL;
757 --Bug 4107528 End
758
759 --l_document := l_document || '<TD class=tabledata align=left>' ||
760 -- nvl(to_char(l_line.neg_line_num), ' ') || '</TD>' || NL;
761
762 IF l_source = 'MRP' THEN
763 l_document := l_document || '<TD class=tabledata align=left>' ||
764 nvl(l_line.req_num, ' ') || '</TD>' || NL;
765 END IF;
766
767 l_document := l_document || '<TD class=tabledata align=left>' ||
768 nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
769
770 l_document := l_document || '<TD class=tabledata align=left>' ||
771 nvl(l_line.item_num, ' ') || '</TD>' || NL;
772
773 l_document := l_document || '<TD class=tabledata align=left>' ||
774 nvl(l_line.item_desc, ' ') || '</TD>' || NL;
775
776 l_document := l_document || '<TD class=tabledata align=left>' ||
777 nvl(l_line.uom, ' ') || '</TD>' || NL;
778
779 IF l_source = 'MRP' THEN
780 l_document := l_document || '<TD class=tabledata align=left>' ||
781 nvl(to_char(l_line.old_qty), ' ') || '</TD>' || NL;
782
783 l_document := l_document || '<TD class=tabledata align=left>' ||
784 nvl(to_char(l_line.old_need_by_date), ' ') || '</TD>' || NL;
785
786 END IF;
787
788 l_document := l_document || '<TD class=tabledata align=left>' ||
789 nvl(to_char(l_line.quantity), ' ') || '</TD>' || NL;
790
791 l_document := l_document || '<TD class=tabledata align=left>' ||
792 nvl(to_char(l_line.need_by_date), ' ') || '</TD>' || NL;
793
794 x_progress := '009';
795
796 IF l_source = 'MRP' THEN
797 if l_line.cancel_flag = 'Y' then
798 po_headers_sv4.get_lookup_code_dsp ('DOCUMENT STATE',
799 'CANCELLED',
800 l_status);
801 else
802 fnd_message.set_name ('PO','PO_SOURCING_RESCHEDULE');
803 l_status := fnd_message.get;
804 end if;
805
806 ELSIF l_source = 'WITHDRAW' THEN
807 fnd_message.set_name ('PO','PO_SOURCING_WITHDRAW');
808 l_status := fnd_message.get;
809 END IF;
810
811
812 l_document := l_document || '<TD class=tabledata align=left>' ||
813 nvl(l_status, ' ') || '</TD>' || NL;
814
815 l_document := l_document || '</TR>' || NL;
816 x_progress := '010';
817
818 exit when i >= 5; -- Bug 2395868 (anhuang)
819 -- Workflow attribute can hold a maximum of just 6 lines.
820 -- Set exit condition to 5 lines to be cleaner.
821 end loop;
822
823 IF l_source = 'MRP' THEN
824 close resc_csr;
825 ELSIF l_source = 'WITHDRAW' THEN
826 close wdraw_csr;
827 END IF;
828
829 l_document := l_document || '</TABLE>'|| NL;
830
831 l_document := l_document || '<br><p>' || l_message_text1 || '</p><br>' || NL;
832
833 x_document := l_document;
834
835 exception
836 when others then
837 null;
838
839 END;
840
841 -- Bug 3346038
842 /*============================================================================
843 Procedure to build the message body when called from MRP reschedule
844 ==============================================================================*/
845 PROCEDURE get_req_line_details_mrp_wd(document_id in varchar2,
846 display_type in Varchar2,
847 document in out NOCOPY clob,
848 document_type in out NOCOPY varchar2) IS
849
850 NL VARCHAR2(1) := fnd_global.newline;
851 l_document varchar2(32000) := '';
852 l_item_type wf_items.item_type%TYPE;
853 l_item_key wf_items.item_key%TYPE;
854 l_message_text varchar2(30000) := null;
855 x_progress varchar2(3) := null;
856
857 BEGIN
858 x_progress := '005';
859 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
860 l_item_key := substr(document_id, instr(document_id, ':') + 1,
861 length(document_id) - 2);
862
863 x_progress := '010';
864 l_message_text := wf_engine.GetItemAttrText
865 (itemtype => l_item_type,
866 itemkey => l_item_key,
867 aname => 'PO_REQ_CHN_MSG_BODY_TMP');
868
869 x_progress := '020';
870 IF (display_type = 'text/html') THEN
871
872 l_document := l_document || l_message_text || NL;
873
874 x_progress := '030';
875 WF_NOTIFICATION.WriteToClob(document, l_document);
876
877 -- Bug 3346038, Should use PLSQLCLOB
878 END IF; /* IF (display_type = 'text/html') */
879
880 x_progress := '040';
881 exception
882 when others then
883 po_message_s.sql_error('In Exception of get_req_line_details_mrp_wd()', x_progress, sqlcode);
884
885 END;
886
887
888 /*============================================================================
889 Wrapper to group the requisition lines by negotiation and call the WF
890 ==============================================================================*/
891 PROCEDURE call_negotiation_wf(x_control_level IN VARCHAR2,
892 x_document_id IN NUMBER) is
893
894 cursor c1(x_doc_id in number) is
895 select auction_header_id,
896 auction_display_number
897 from po_requisition_lines
898 where requisition_line_id = x_doc_id
899 AND at_sourcing_flag = 'Y'; --<REQINPOOL>
900
901 cursor c2(x_doc_id in number) is
902 select distinct auction_header_id ,
903 auction_display_number
904 from po_requisition_lines
905 where requisition_header_id = x_doc_id
906 AND at_sourcing_flag = 'Y' --<REQINPOOL>
907 and (cancel_flag = 'Y' or closed_code = 'FINALLY CLOSED')
908 and (trunc(cancel_date) = trunc(sysdate) or trunc(closed_date) = trunc(sysdate));
909
910 cursor c3(x_doc_id in number) is
911 select distinct auction_header_id ,
912 auction_display_number
913 from po_requisition_lines
914 where requisition_header_id = x_doc_id
915 AND at_sourcing_flag = 'Y'; --<REQINPOOL>
916
917 cursor interface(v_process_id in number) is
918 select distinct auction_header_id,
919 auction_display_number
920 from po_reschedule_interface
921 where auction_header_id is not null
922 and process_id = v_process_id;
923
924 x_auction_num varchar2(60);
925 x_auction_header_id number;
926 x_process_id number;
927 x_sourcing_install_status varchar2(1);
928
929 BEGIN
930
931 /* check if sourcing is installed */
932 PO_SETUP_S1.GET_SOURCING_STARTUP(x_sourcing_install_status);
933 if x_sourcing_install_status <> 'I' then
934 return;
935 end if;
936
937 /* Depending on the control level open the correct cursor to group the req
938 lines by negotiation and call the wf process */
939
940 if (x_control_level = 'REQ LINE') then
941
942 open c1(x_document_id);
943 loop
944 fetch c1 into x_auction_header_id,
945 x_auction_num ;
946
947 EXIT WHEN c1%NOTFOUND;
948
949 po_negotiation_req_notif.req_change_workflow_startup(x_control_level,
950 x_auction_header_id ,
951 x_auction_num,
952 x_document_id);
953
954 end loop;
955 close c1;
956
957 elsif (x_control_level = 'REQ HEADER') then
958
959 open c2(x_document_id);
960 loop
961 fetch c2 into x_auction_header_id,
962 x_auction_num ;
963 EXIT WHEN c2%NOTFOUND;
964
965 po_negotiation_req_notif.req_change_workflow_startup(x_control_level,
966 x_auction_header_id ,
967 x_auction_num,
968 x_document_id);
969
970 end loop;
971 close c2;
972
973 elsif (x_control_level = 'WITHDRAW') then
974
975 open c3(x_document_id);
976 loop
977 fetch c3 into x_auction_header_id,
978 x_auction_num ;
979 EXIT WHEN c3%NOTFOUND;
980
981 po_negotiation_req_notif.req_change_workflow_startup(x_control_level,
982 x_auction_header_id ,
983 x_auction_num,
984 x_document_id);
985 end loop;
986 close c3;
987
988 elsif (x_control_level = 'MRP') then
989
990 x_process_id := x_document_id;
991 open interface(x_process_id);
992
993 loop
994
995 fetch interface into x_auction_header_id,
996 x_auction_num ;
997 exit when interface%notfound;
998
999 po_negotiation_req_notif.req_change_workflow_startup(x_control_level,
1000 x_auction_header_id,
1001 x_auction_num,
1002 null,
1003 x_process_id);
1004
1005
1006
1007
1008 end loop;
1009
1010 close interface;
1011
1012 end if;
1013
1014 exception
1015 when others then
1016 null;
1017
1018 END;
1019
1020 /*============================================================================
1021 Procedure tocheck where the wf is being called from so as to decide
1022 the correct notification to be sent
1023 ==============================================================================*/
1024 procedure Check_Source( itemtype in varchar2,
1025 itemkey in varchar2,
1026 actid in number,
1027 funcmode in varchar2,
1028 resultout out NOCOPY varchar2 ) is
1029 l_source varchar2(30);
1030 begin
1031 if (funcmode <> wf_engine.eng_run) then
1032
1033 resultout := wf_engine.eng_null;
1034 return;
1035
1036 end if;
1037
1038 l_source := wf_engine.GetItemAttrText
1039 (itemtype => itemtype,
1040 itemkey => itemkey,
1041 aname => 'SOURCE');
1042
1043 if l_source in ('MRP','WITHDRAW') then
1044 resultout := wf_engine.eng_completed || ':' || 'MRP';
1045 else
1046 resultout := wf_engine.eng_completed || ':' || 'OTHERS';
1047 end if;
1048 end;
1049 END po_negotiation_req_notif;