[Home] [Help]
PACKAGE BODY: APPS.MSC_X_WFNOTIFY_PKG
Source
1 PACKAGE BODY msc_x_wfnotify_pkg AS
2 /*$Header: MSCXNFPB.pls 120.7 2011/08/18 17:08:59 vjuluri ship $ */
3
4 G_ORDER_FORECAST CONSTANT INTEGER := 2;
5 G_SUPPLY_COMMIT CONSTANT INTEGER := 3;
6
7 /*------------------------------------------------------------------------
8 private function
9 -------------------------------------------------------------------------*/
10
11 --function returns 0 if a user has selected a exception for notifiaction else returns 1
12 function validate_block_notification(p_user_name in varchar2, p_exception_type in number) return number is
13 cursor check_user(p_user in varchar2, p_excep_type number) is
14 select 1
15 from MSC_EXCEPTION_PREFERENCES ep,
16 fnd_user u
17 where ep.user_id = u.user_id
18 and u.user_name = p_user
19 and exception_type_lookup_code = p_excep_type
20 and rank > 0;
21 l_select_flag number; --- Bug # 6242764
22 begin
23 open check_user(p_user_name, p_exception_type);
24 fetch check_user into l_select_flag;
25 if check_user%found then
26 close check_user;
27 return 0; --dont block, validation failed
28 end if;
29 close check_user;
30 return 1; --block notification
31 end validate_block_notification; ---Added for bug # 6175897
32
33 /*------------------------------------------------------------------------
34 Workflow Procedures
35 -------------------------------------------------------------------------*/
36
37 PROCEDURE Launch_WF (p_errbuf OUT NOCOPY Varchar2,
38 p_retcode OUT NOCOPY Number) IS
39 --This cursor selects records inserted into msc_exception_details
40 --in the current planning run. Last update login column is set to -99
41 --to indicate this fact. When plan is selected for netting again, this
42 --column is reset to null in all records inserted into this table in prior runs.
43 --This mechanism prevents us from sending the planner multiple notifications
44 --for the same exception.
45 CURSOR wf_details_c IS
46 select ex.exception_detail_id,
47 ex.sr_instance_id,
48 ex.company_id,
49 ex.company_name,
50 ex.company_site_id,
51 ex.company_site_name,
52 ex.inventory_item_id,
53 ex.item_name,
54 ex.item_description,
55 ex.exception_group,
56 ex.exception_type,
57 ex.exception_type_name,
58 ex.supplier_id,
59 ex.supplier_name,
60 ex.supplier_site_id,
61 ex.supplier_site_name,
62 ex.trading_partner_item_name,
63 ex.customer_id,
64 ex.customer_name,
65 ex.customer_site_id,
66 ex.customer_site_name,
67 ex.trading_partner_item_name,
68 ex.number3, --based item qty
69 ex.transaction_id1, --base item trx id
70 ex.transaction_id2, --pegged item trx id
71 ex.number1, --total supply/total intransit
72 ex.number2, --total demand/total onhand
73 ex.threshold, --lead time/itm min/itm max/threshold
74 ex.lead_time,
75 ex.item_min_qty,
76 ex.item_max_qty,
77 ex.date1, --based item actual dt
78 ex.date2, --pegged item actual dt
79 ex.date3, --today's dt (sysdate)
80 ex.date4,
81 ex.date5,
82 ex.exception_basis,
83 ex.order_creation_date1, --based item creation dt
84 ex.order_creation_date2, --pegged item creation date
85 ex.order_number,
86 ex.release_number,
87 ex.line_number,
88 ex.end_order_number,
89 ex.end_order_rel_number,
90 ex.end_order_line_number
91 from msc_x_exception_details ex
92 where ex.plan_id = -1
93 and ex.exception_group in (1,2,3,4,5,6,7,8,9,10) --exclude user define exceptions
94 and ex.version = 'CURRENT'; --indicate the current run of the netting engine
95
96
97 /*----------------------------------------------
98 Get the planner from the planner code only if
99 the company is seeded which is company_id =1
100 (OEM)
101 -------------------------------------------*/
102 CURSOR planners_c(p_company_id in number,
103 p_company_site_id in Number,
104 p_item_id in Number) IS
105 SELECT distinct pl.user_name
106 FROM msc_system_items msi,
107 msc_company_sites s,
108 msc_trading_partner_maps map,
109 msc_trading_partners part,
110 msc_planners pl
111 WHERE s.company_id = 1
112 AND s.company_site_id = p_company_site_id
113 AND map.map_type = 2
114 AND map.company_key = s.company_site_id
115 AND part.partner_id = map.tp_key
116 AND msi.organization_id = part.sr_tp_id
117 AND msi.sr_instance_id = part.sr_instance_id
118 AND msi.plan_id = -1
119 AND msi.inventory_item_id = p_item_id
120 AND pl.sr_instance_id = part.sr_instance_id
121 AND pl.planner_code = msi.planner_code
122 AND pl.organization_id = part.sr_tp_id;
123
124
125 /*--------------------------------------------------------
126 Look for the partner contact by the site first
127 If the site is not exist, then search by the company
128 ---------------------------------------------------------*/
129 CURSOR partner_con_by_site_c(p_company_id in number,
130 p_company_site_id in Number,p_oem_site_id in NUMBER,p_inventory_item_id in NUMBER) IS
131 SELECT distinct con.name
132 FROM msc_trading_partner_maps map,
133 msc_trading_partner_maps map1,
134 msc_company_sites site,
135 msc_partner_contacts con
136 WHERE map.map_type = 3
137 AND map.company_key = site.company_site_id
138 AND map.tp_key = con.partner_site_id
139 AND map1.company_key =p_oem_site_id
140 AND map1.map_type= 2
141 AND site.company_id = p_company_id
142 AND site.company_site_id = p_company_site_id
143 AND exists ( select 1 from msc_system_items msi, msc_trading_partners mt
144 where msi.plan_id = -1 --- Bug # 6242764
145 and msi.sr_instance_id = con.sr_instance_id
146 and msi.inventory_item_id = p_inventory_item_id
147 and mt.partner_id = map1.tp_key
148 and mt.sr_tp_id = msi.organization_id
149 and mt.partner_type=3); -- Bug #6242828
150
151 /*-------------------------------------------------------------------------------
152 Look for the partner contact by site independent of sr_instance_id clause.
153 ---------------------------------------------------------------------------------*/
154
155 CURSOR partner_con_by_site_c1(p_company_id in NUMBER,
156 p_company_site_id in NUMBER) IS
157 SELECT
158 distinct con.name
159 FROM
160 msc_trading_partner_maps map,
161 msc_company_sites site,
162 msc_partner_contacts con
163 WHERE map.map_type = 3
164 AND map.company_key = site.company_site_id
165 AND map.tp_key = con.partner_site_id
166 AND site.company_id = p_company_id
167 AND site.company_site_id = p_company_site_id; -- Bug #6242828
168
169 CURSOR partner_con_by_comp_c(p_company_id in number, p_oem_id in number,p_inventory_item_id in number) IS
170 SELECT distinct con.name
171 FROM msc_trading_partner_maps map,
172 msc_trading_partner_maps map1,
173 msc_company_relationships rel,
174 msc_companies c,
175 msc_partner_contacts con
176 WHERE map.map_type = 1 --company
177 AND map.company_key = rel.relationship_id
178 AND rel.relationship_type = 2 --supplier
179 AND rel.object_id = c.company_id
180 AND map.tp_key = con.partner_id
181 AND map1.company_key =p_oem_id
182 AND map1.map_type= 2
183 AND con.partner_type = 1 --suplier
184 AND c.company_id = p_company_id
185 AND exists ( select 1 from msc_system_items msi,msc_trading_partners mt
186 where msi.plan_id = -1 --- Bug # 6242764
187 and msi.sr_instance_id = con.sr_instance_id
188 and msi.inventory_item_id = p_inventory_item_id
189 and mt.partner_id = map1.tp_key
190 and mt.sr_tp_id = msi.organization_id
191 and mt.partner_type=3)
192 UNION
193 SELECT distinct con.name
194 FROM msc_trading_partner_maps map,
195 msc_trading_partner_maps map1,
196 msc_company_relationships rel,
197 msc_companies c,
198 msc_partner_contacts con
199 WHERE map.map_type = 1 --company
200 AND map.company_key = rel.relationship_id
201 AND rel.relationship_type = 1 --customer
202 AND rel.object_id = c.company_id
203 AND con.partner_id = map.tp_key
204 AND map1.company_key =p_oem_id
205 AND map1.map_type= 2
206 AND con.partner_type = 2 --customer
207 AND c.company_id = p_company_id
208 AND exists ( select 1 from msc_system_items msi, msc_trading_partners mt
209 where msi.plan_id = -1 --- Bug # 6242764
210 and msi.sr_instance_id = con.sr_instance_id
211 and msi.inventory_item_id = p_inventory_item_id
212 and mt.partner_id = map1.tp_key
213 and mt.sr_tp_id = msi.organization_id
214 and mt.partner_type=3); -- Bug #6242828
215
216 /*-------------------------------------------------------------------------------
217 Look for the partner contact by company independent of sr_instance_id
218 ---------------------------------------------------------------------------------*/
219
220 CURSOR partner_con_by_comp_c1(p_company_id in number) IS
221 SELECT distinct con.name
222 FROM msc_trading_partner_maps map,
223 msc_company_relationships rel,
224 msc_companies c,
225 msc_partner_contacts con
226 WHERE map.map_type = 1 --company
227 AND map.company_key = rel.relationship_id
228 AND rel.relationship_type = 2 --supplier
229 AND rel.object_id = c.company_id
230 AND map.tp_key =con.partner_id
231 AND con.partner_type = 1 --suplier
232 AND c.company_id = p_company_id
233 UNION
234 SELECT distinct con.name
235 FROM msc_trading_partner_maps map,
236 msc_company_relationships rel,
237 msc_companies c,
238 msc_partner_contacts con
239 WHERE map.map_type = 1 --company
240 AND map.company_key = rel.relationship_id
241 AND rel.relationship_type = 1 --customer
242 AND rel.object_id = c.company_id
243 AND con.partner_id = map.tp_key
244 AND con.partner_type = 2 --customer
245 AND c.company_id = p_company_id; -- Bug #6242828
246
247
248 wf_type Varchar2(100);
249 wf_key Varchar2(100);
250 wf_process Varchar2(200);
251 l_user_id Number;
252 l_user_performer Varchar2(240);
253 l_real_name Varchar2(240);
254 l_exception_detail_id Number;
255 l_sr_instance_id Number;
256 l_company_id Number;
257 l_company_name msc_sup_dem_entries.publisher_name%type;
258 l_company_site_id Number;
259 l_company_site_name msc_sup_dem_entries.publisher_site_name%type;
260 l_item_id Number;
261 l_item_name msc_items.item_name%type;
262 l_item_desc msc_x_exception_details.item_description%type;
263 l_exception_group Number;
264 l_exception_type Number;
265 l_exception_type_name fnd_lookup_values.meaning%type;
266 l_supplier_id Number;
267 l_supplier_name msc_sup_dem_entries.supplier_name%type;
268 l_supplier_site_id Number;
269 l_supplier_site_name msc_sup_dem_entries.supplier_site_name%type;
270 l_supplier_item_name msc_sup_dem_entries.trading_partner_item_name%type;
271 l_customer_id Number;
272 l_customer_name msc_sup_dem_entries.customer_name%type;
273 l_customer_site_id Number;
274 l_customer_site_name msc_sup_dem_entries.customer_site_name%type;
275 l_customer_item_name msc_sup_dem_entries.trading_partner_item_name%type;
276 l_order_number msc_sup_dem_entries.order_number%type;
277 l_release_number msc_sup_dem_entries.release_number%type;
278 l_line_number msc_sup_dem_entries.line_number%type;
279 l_end_order_number msc_sup_dem_entries.end_order_number%type;
280 l_end_order_rel_number msc_sup_dem_entries.end_order_rel_number%type;
281 l_end_order_line_number msc_sup_dem_entries.end_order_line_number%type;
282 l_quantity Number;
283 l_transaction_id1 Number;
284 l_transaction_id2 Number;
285 l_quantity1 Number;
286 l_quantity2 Number;
287 l_threshold Number;
288 l_lead_time Number;
289 l_item_min_qty Number;
290 l_item_max_qty Number;
291 l_date1 Date;
292 l_date2 Date;
293 l_date3 Date;
294 l_date4 Date;
295 l_date5 Date;
296 l_order_creation_date1 Date;
297 l_order_creation_date2 Date;
298 l_slogan Varchar2(240);
299 l_error_code Varchar2(1);
300 l_error_msg Varchar2(240);
301 l_operator_id Number;
302 l_language Varchar2(100);
303 l_planner_code Varchar2(500);
304 l_message_name Varchar2(100);
305 l_exist Number;
306 l_independent NUMBER; -- Bug #6242828
307 l_exception_basis msc_x_exception_details.exception_basis%type;
308
309 t_ex_detail_id number_arr;
310 t_sr_ins_id number_arr;
311 t_company_id number_arr;
312 t_company_site_id number_arr;
313 t_item_id number_arr;
314 t_supplier_id number_arr;
315 t_supplier_site_id number_arr;
316 t_customer_id number_arr;
317 t_customer_site_id number_arr;
318 t_trans_id1 number_arr;
319 t_trans_id2 number_arr;
320 t_quantity number_arr;
321 t_qty1 number_arr;
322 t_qty2 number_arr;
323 t_threshold number_arr;
324 t_lead_time number_arr;
325 t_item_min_qty number_arr;
326 t_item_max_qty number_arr;
327 t_date1 date_arr;
328 t_date2 date_arr;
329 t_date3 date_arr;
330 t_date4 date_arr;
331 t_date5 date_arr;
332 t_order_date1 date_arr;
333 t_order_date2 date_arr;
334 t_company_name msc_x_netting_pkg.publisherList;
335 t_company_site_name msc_x_netting_pkg.pubsiteList;
336 t_item_name msc_x_netting_pkg.itemnameList;
337 t_item_desc msc_x_netting_pkg.itemdescList;
338 t_excep_group msc_x_netting_pkg.excepgroupList;
339 t_excep_type msc_x_netting_pkg.number_arr;
340 t_excep_type_name msc_x_netting_pkg.exceptypeList;
341 t_supplier_name msc_x_netting_pkg.supplierList;
342 t_supplier_site_name msc_x_netting_pkg.suppsiteList;
343 t_supplier_item_name msc_x_netting_pkg.tpitemnameList;
344 t_customer_name msc_x_netting_pkg.customerList;
345 t_customer_site_name msc_x_netting_pkg.custsiteList;
346 t_customer_item_name msc_x_netting_pkg.tpitemnameList;
347 t_order_number msc_x_netting_pkg.ordernumberList;
348 t_release_number msc_x_netting_pkg.releasenumList;
349 t_line_number msc_x_netting_pkg.linenumList;
350 t_end_order_num msc_x_netting_pkg.ordernumberList;
351 t_end_order_rel_num msc_x_netting_pkg.releasenumList;
352 t_end_order_line_num msc_x_netting_pkg.linenumList;
353 t_exception_basis msc_x_netting_pkg.exceptbasisList;
354
355 l_inserted_record number;
356 l_oem_site_id number;
357 l_oem_id number; -- Bug #6242828
358 BEGIN
359
360 wf_type := 'MSCSNDNT';
361 wf_process := 'MSC_NOTIFICATION';
362 l_inserted_record := 0;
363 l_exist := 0;
364 l_independent := 0; -- Bug #6242828
365 t_ex_detail_id := number_arr();
366 t_company_id := number_arr();
367 t_company_site_id := number_arr();
368 t_item_id := number_arr();
369 t_supplier_id := number_arr();
370 t_supplier_site_id := number_arr();
371 t_customer_id := number_arr();
372 t_customer_site_id := number_arr();
373 t_trans_id1 := number_arr();
374 t_trans_id2 := number_arr();
375 t_quantity := number_arr();
376 t_qty1 := number_arr();
377 t_qty2 := number_arr();
378 t_threshold := number_arr();
379 t_lead_time := number_arr();
380 t_item_min_qty := number_arr();
381 t_item_max_qty := number_arr();
382 t_date1 := date_arr();
383 t_date2 := date_arr();
384 t_date3 := date_arr();
385 t_date4 := date_arr();
386 t_date5 := date_arr();
387 t_order_date1 := date_arr();
388 t_order_date2 := date_arr();
389
390 --dbms_output.put_line('in workflow ');
391 open wf_details_c;
392
393
394
395 FETCH wf_details_c BULK COLLECT INTO
396 t_ex_detail_id,
397 t_sr_ins_id,
398 t_company_id,
399 t_company_name,
400 t_company_site_id,
401 t_company_site_name,
402 t_item_id,
403 t_item_name,
404 t_item_desc,
405 t_excep_group,
406 t_excep_type,
407 t_excep_type_name,
408 t_supplier_id,
409 t_supplier_name,
410 t_supplier_site_id,
411 t_supplier_site_name,
412 t_supplier_item_name,
413 t_customer_id,
414 t_customer_name,
415 t_customer_site_id,
416 t_customer_site_name,
417 t_customer_item_name,
418 t_quantity,
419 t_trans_id1,
420 t_trans_id2,
421 t_qty1,
422 t_qty2,
423 t_threshold,
424 t_lead_time,
425 t_item_min_qty,
426 t_item_max_qty,
427 t_date1,
428 t_date2,
429 t_date3,
430 t_date4,
431 t_date5,
432 t_exception_basis,
433 t_order_date1,
434 t_order_date2,
435 t_order_number,
436 t_release_number,
437 t_line_number,
438 t_end_order_num,
439 t_end_order_rel_num,
440 t_end_order_line_num;
441
442 CLOSE wf_details_c;
443
444
445 /* Add an if statement to check for records */
446
447 --dbms_output.put_line('wf count ' || t_ex_detail_id.COUNT);
448 IF (t_ex_detail_id is not null and t_ex_detail_id.COUNT > 0) THEN
449
450 FOR j in 1 .. t_ex_detail_id.COUNT
451 LOOP
452
453 l_message_name := getMessage(t_excep_type(j));
454 --dbms_output.put_line('message name ' || l_message_name);
455 IF (t_company_id(j) = 1) THEN --seeded company (OEM)
456 open planners_c(t_company_id(j),
457 t_company_site_id(j),
458 t_item_id(j));
459
460 loop
461 fetch planners_c into l_user_performer;
462 exit when planners_c%NOTFOUND;
463
464 --dbms_output.put_line('user performer ' || l_user_performer);
465
466 IF (l_user_performer is not null) and validate_block_notification(l_user_performer, t_excep_type(j)) = 0 then ---Added for bug # 6175897
467 wf_key := t_excep_group(j) || '-' ||
468 to_char(t_excep_type(j)) || '-' ||
469 to_char(t_item_id(j)) || '-' ||
470 to_char(t_company_id(j)) || '-' ||
471 to_char(t_company_site_id(j)) || '-' ||
472 to_char(t_customer_id(j)) || '-' ||
473 to_char(t_customer_site_id(j)) || '-' ||
474 to_char(t_supplier_id(j)) || '-' ||
475 to_char(t_supplier_site_id(j)) || '-' ||
476 to_char(t_ex_detail_id(j)) || '-' ||
477 l_user_performer;
478 begin
479
480 SELECT substr(display_name,instr(display_name,',')+ 1) || ' ' ||
481 substr(display_name,1, instr(display_name,',') -1)
482 INTO l_real_name
483 FROM wf_users
484 WHERE name = l_user_performer;
485 exception
486 when others then
487 l_real_name := null;
488 end;
489 FND_FILE.PUT_LINE(FND_FILE.LOG,'WF: user ' || l_real_name);
490 --dbms_output.put_line('start 1');
491 wfStart(wf_type,
492 wf_key,
493 wf_process,
494 l_user_performer,
495 l_real_name,
496 l_message_name,
497 t_excep_type(j),
498 t_excep_type_name(j),
499 --slogan,
500 t_item_id(j),
501 t_item_name(j),
502 t_item_desc(j),
503 t_company_id(j),
504 t_company_name(j),
505 t_company_site_id(j),
506 t_company_site_name(j),
507 t_supplier_id(j),
508 t_supplier_name(j),
509 t_supplier_site_id(j),
510 t_supplier_site_name(j),
511 t_supplier_item_name(j),
512 t_customer_id(j),
513 t_customer_name(j),
514 t_customer_site_id(j),
515 t_customer_site_name(j),
516 t_customer_item_name(j),
517 t_trans_id1(j),
518 t_trans_id2(j),
519 t_quantity(j),
520 t_qty1(j),
521 t_qty2(j),
522 t_threshold(j),
523 t_lead_time(j),
524 t_item_min_qty(j),
525 t_item_max_qty(j),
526 t_date1(j),
527 t_date2(j),
528 t_date3(j),
529 t_date4(j),
530 t_date5(j),
531 t_exception_basis(j),
532 t_order_date1(j),
533 t_order_date2(j),
534 t_order_number(j),
535 t_release_number(j),
536 t_line_number(j),
537 t_end_order_num(j),
538 t_end_order_rel_num(j),
539 t_end_order_line_num(j));
540 l_inserted_record := l_inserted_record + 1;
541 END IF; --user name is not null
542
543
544 end loop;
545 close planners_c;
546 ELSE
547 begin
548 SELECT 1 into l_exist from dual
549 WHERE exists (SELECT 1
550 FROM msc_trading_partner_maps map,
551 msc_trading_partner_maps map1,
552 msc_company_sites site,
553 msc_partner_contacts con
554 WHERE map.map_type = 3
555 AND map.company_key = site.company_site_id
556 AND map.tp_key = con.partner_site_id
557 AND map1.company_key =decode(t_supplier_id(j),1,t_supplier_site_id(j),t_customer_site_id(j))
558 AND map1.map_type= 2
559 AND site.company_id = t_company_id(j)
560 AND site.company_site_id = t_company_site_id(j)
561 AND exists ( select 1 from msc_system_items msi, msc_trading_partners mt
562 where msi.plan_id = -1 --- Bug # 6242764
563 and msi.sr_instance_id = con.sr_instance_id
564 and msi.inventory_item_id = t_item_id(j)
565 and mt.partner_id = map1.tp_key
566 and mt.sr_tp_id = msi.organization_id
567 and mt.partner_type=3));
568
569 l_independent := 0 ;
570 EXCEPTION
571 WHEN NO_DATA_FOUND THEN
572 BEGIN
573 SELECT 1 into l_exist
574 FROM dual
575 WHERE exists (SELECT 1
576 FROM msc_trading_partner_maps map,
577 msc_company_sites site,
578 msc_partner_contacts con
579 WHERE map.map_type = 3
580 AND map.company_key = site.company_site_id
581 AND map.tp_key = con.partner_site_id
582 AND site.company_id = t_company_id(j)
583 AND site.company_site_id = t_company_site_id(j)); -- Bug #6242828
584 l_independent := 1;
585
586 exception
587 when no_data_found then
588 l_exist := 0;
589 end;
590 end;
591
592
593
594 IF (l_exist = 1 ) THEN
595 IF l_independent = 0 THEN
596
597 select decode(t_supplier_id(j),1,t_supplier_site_id(j),t_customer_site_id(j))
598 into l_oem_site_id
599 from dual;
600
601 --FND_FILE.PUT_LINE(FND_FILE.LOG,'After the sql----------------inside else part l_oem_site_id'|| l_oem_site_id);
602
603 open partner_con_by_site_c(t_company_id(j), t_company_site_id(j),l_oem_site_id,t_item_id(j));
604 ELSE
605 open partner_con_by_site_c1(t_company_id(j), t_company_site_id(j));
606 END IF;
607
608 loop
609 IF l_independent = 0 THEN
610 fetch partner_con_by_site_c into l_user_performer;
611 exit when partner_con_by_site_c%NOTFOUND;
612 ELSE
613 fetch partner_con_by_site_c1 into l_user_performer;
614 exit when partner_con_by_site_c1%NOTFOUND;
615 END IF;
616
617 --dbms_output.put_line('partner contact ' || l_user_performer);
618
619 IF (l_user_performer is not null) and validate_block_notification(l_user_performer, t_excep_type(j)) = 0 then ---Added for bug # 6175897
620 wf_key := t_excep_group(j) || '-' ||
621 to_char(t_excep_type(j)) || '-' ||
622 to_char(t_item_id(j)) || '-' ||
623 to_char(t_company_id(j)) || '-' ||
624 to_char(t_company_site_id(j)) || '-' ||
625 to_char(t_customer_id(j)) || '-' ||
626 to_char(t_customer_site_id(j)) || '-' ||
627 to_char(t_supplier_id(j)) || '-' ||
628 to_char(t_supplier_site_id(j)) || '-' ||
629 to_char(t_ex_detail_id(j)) || '-' ||
630 l_user_performer;
631
632 begin
633
634 SELECT substr(display_name,instr(display_name,',')+ 1) || ' ' ||
635 substr(display_name,1, instr(display_name,',') -1)
636 INTO l_real_name
637 FROM wf_users
638 WHERE name = l_user_performer;
639 exception
640 when others then
641 l_real_name := null;
642 end;
643
644 FND_FILE.PUT_LINE(FND_FILE.LOG,'WF: user ' || l_real_name);
645 --dbms_output.put_line('start 2');
646
647 wfStart(wf_type,
648 wf_key,
649 wf_process,
650 l_user_performer,
651 l_real_name,
652 l_message_name,
653 t_excep_type(j),
654 t_excep_type_name(j),
655 --slogan,
656 t_item_id(j),
657 t_item_name(j),
658 t_item_desc(j),
659 t_company_id(j),
660 t_company_name(j),
661 t_company_site_id(j),
662 t_company_site_name(j),
663 t_supplier_id(j),
664 t_supplier_name(j),
665 t_supplier_site_id(j),
666 t_supplier_site_name(j),
667 t_supplier_item_name(j),
668 t_customer_id(j),
669 t_customer_name(j),
670 t_customer_site_id(j),
671 t_customer_site_name(j),
672 t_customer_item_name(j),
673 t_trans_id1(j),
674 t_trans_id2(j),
675 t_quantity(j),
676 t_qty1(j),
677 t_qty2(j),
678 t_threshold(j),
679 t_lead_time(j),
680 t_item_min_qty(j),
681 t_item_max_qty(j),
682 t_date1(j),
683 t_date2(j),
684 t_date3(j),
685 t_date4(j),
686 t_date5(j),
687 t_exception_basis(j),
688 t_order_date1(j),
689 t_order_date2(j),
690 t_order_number(j),
691 t_release_number(j),
692 t_line_number(j),
693 t_end_order_num(j),
694 t_end_order_rel_num(j),
695 t_end_order_line_num(j));
696 l_inserted_record := l_inserted_record + 1;
697 end if;
698 end loop;
699 IF l_independent = 0 THEN
700 close partner_con_by_site_c;
701
702 ELSE
703 close partner_con_by_site_c1;
704 END IF;
705
706 ELSE -- Search by company
707
708 BEGIN
709 SELECT 0 into l_independent
710 FROM dual
711 WHERE exists (SELECT 1
712 FROM msc_trading_partner_maps map,
713 msc_trading_partner_maps map1,
714 msc_company_relationships rel,
715 msc_companies c,
716 msc_partner_contacts con
717 WHERE map.map_type = 1 --company
718 AND map.company_key = rel.relationship_id
719 AND (rel.relationship_type = 2 --supplier
720 OR rel.relationship_type = 1) --customer
721 AND rel.object_id = c.company_id
722 AND map.tp_key = con.partner_id
723 AND map1.company_key =decode(t_supplier_id(j),1,t_supplier_id(j),t_customer_id(j))
724 AND map1.map_type= 2
725 AND (con.partner_type = 1 --suplier
726 OR con.partner_type = 2) --customer
727 AND c.company_id = t_company_id(j)
728 AND exists ( SELECT 1
729 FROM msc_system_items msi,
730 msc_trading_partners mt
731 WHERE msi.plan_id = -1 --- Bug # 6242764
732 AND msi.sr_instance_id = con.sr_instance_id
733 AND msi.inventory_item_id = t_item_id(j)
734 AND mt.partner_id = map1.tp_key
735 AND mt.sr_tp_id = msi.organization_id
736 AND mt.partner_type=3) ); -- Bug #6242828
737 l_exist := 1;
738 EXCEPTION
739 WHEN NO_DATA_FOUND THEN
740 BEGIN
741 SELECT 1 into l_independent
742 FROM dual
743 WHERE EXISTS (SELECT 1
744 FROM msc_trading_partner_maps map,
745 msc_company_relationships rel,
746 msc_companies c,
747 msc_partner_contacts con
748 WHERE map.map_type = 1 --company
749 AND map.company_key = rel.relationship_id
750 AND (rel.relationship_type = 2 --supplier
751 OR rel.relationship_type = 1) --customer
752 AND rel.object_id = c.company_id
753 AND map.tp_key =con.partner_id
754 AND c.company_id = t_company_id(j));
755 l_exist := 1;
756 EXCEPTION
757 WHEN NO_DATA_FOUND THEN
758 l_exist :=0;
759 END;
760 END;
761
762 --FND_FILE.PUT_LINE(FND_FILE.LOG,'After the sql*************inside else part supplier id'||t_supplier_id(j) ||' customer_id'|| t_customer_id(j)||' company id'|| t_company_id(j) ||' item id '|| t_item_id(j));
763
764
765 --FND_FILE.PUT_LINE(FND_FILE.LOG,'After the sql***************inside else part l_independent'|| l_independent);
766
767
768 --FND_FILE.PUT_LINE(FND_FILE.LOG,'After the sql***************inside else part l_exist'|| l_exist);
769
770 IF l_exist = 1 THEN
771 IF l_independent = 0 THEN
772 select decode(t_supplier_id(j),1,t_supplier_site_id(j),t_customer_site_id(j))
773 into l_oem_id
774 from dual;
775
776
777 open partner_con_by_comp_c(t_company_id(j),l_oem_id,t_item_id(j));
778 ELSE
779 open partner_con_by_comp_c1(t_company_id(j));
780 END IF;
781 LOOP
782 IF l_independent = 0 THEN
783 fetch partner_con_by_comp_c into l_user_performer;
784 exit when partner_con_by_comp_c%NOTFOUND;
785 ELSE
786 fetch partner_con_by_comp_c1 into l_user_performer;
787 exit when partner_con_by_comp_c1%NOTFOUND;
788 END IF;
789
790 --dbms_output.put_line('partner conact by site ' || l_user_performer);
791 IF (l_user_performer is not null) and validate_block_notification(l_user_performer, t_excep_type(j)) = 0 then ---Added for bug # 6175897
792 wf_key := t_excep_group(j) || '-' ||
793 to_char(t_excep_type(j)) || '-' ||
794 to_char(t_item_id(j)) || '-' ||
795 to_char(t_company_id(j)) || '-' ||
796 to_char(t_company_site_id(j)) || '-' ||
797 to_char(t_customer_id(j)) || '-' ||
798 to_char(t_customer_site_id(j)) || '-' ||
799 to_char(t_supplier_id(j)) || '-' ||
800 to_char(t_supplier_site_id(j)) || '-' ||
801 to_char(t_ex_detail_id(j)) || '-' ||
802 l_user_performer;
803
804 begin
805
806 SELECT substr(display_name,instr(display_name,',')+ 1) || ' ' ||
807 substr(display_name,1, instr(display_name,',') -1)
808 INTO l_real_name
809 FROM wf_users
810 WHERE name = l_user_performer;
811 exception
812 when others then
813 l_real_name := null;
814 end;
815 FND_FILE.PUT_LINE(FND_FILE.LOG,'WF: user ' || l_real_name);
816 --dbms_output.put_line('start 3');
817
818 wfStart(wf_type,
819 wf_key,
820 wf_process,
821 l_user_performer,
822 l_real_name,
823 l_message_name,
824 t_excep_type(j),
825 t_excep_type_name(j),
826 --slogan,
827 t_item_id(j),
828 t_item_name(j),
829 t_item_desc(j),
830 t_company_id(j),
831 t_company_name(j),
832 t_company_site_id(j),
833 t_company_site_name(j),
834 t_supplier_id(j),
835 t_supplier_name(j),
836 t_supplier_site_id(j),
837 t_supplier_site_name(j),
838 t_supplier_item_name(j),
839 t_customer_id(j),
840 t_customer_name(j),
841 t_customer_site_id(j),
842 t_customer_site_name(j),
843 t_customer_item_name(j),
844 t_trans_id1(j),
845 t_trans_id2(j),
846 t_quantity(j),
847 t_qty1(j),
848 t_qty2(j),
849 t_threshold(j),
850 t_lead_time(j),
851 t_item_min_qty(j),
852 t_item_max_qty(j),
853 t_date1(j),
854 t_date2(j),
855 t_date3(j),
856 t_date4(j),
857 t_date5(j),
858 t_exception_basis(j),
859 t_order_date1(j),
860 t_order_date2(j),
861 t_order_number(j),
862 t_release_number(j),
863 t_line_number(j),
864 t_end_order_num(j),
865 t_end_order_rel_num(j),
866 t_end_order_line_num(j));
867 l_inserted_record := l_inserted_record + 1;
868 end if;
869 end loop;
870 if l_independent = 0 then
871 close partner_con_by_comp_c;
872 else
873 close partner_con_by_comp_c1; -- Bug #6242828
874 end if;
875 end if;
876 end if;
877 end if; --end of OEM company
878
879 end loop;
880
881
882 FND_FILE.PUT_LINE(FND_FILE.LOG,'Total WF notifications inserted: ' || l_inserted_record);
883
884
885
886 --Reset the records for which the workflows have been
887 --kicked off, to prevent the create duplicate wf items
888 --dbms_output.put_line('Done with wf notifications');
889 begin
890 update msc_x_exception_details
891 set version = null, last_update_login = null
892 where plan_id = -1
893 and version = 'CURRENT'
894 and exception_group in (1,2,3,4,5,6,7,8,9,10);
895 exception
896 when others then
897 ----dbms_output.put_line('Error in update ' || sqlerrm);
898 null;
899 end;
900 END IF;
901
902 commit;
903 EXCEPTION
904 WHEN others then
905 --dbms_output.put_line('error in launch wf ' || sqlerrm);
906 MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_WFNOTIFY_PKG.launch_wf');
907 MSC_SCE_LOADS_PKG.LOG_MESSAGE(SQLERRM);
908 return;
909 END Launch_WF;
910
911
912 --------------------------------------------------------------
913 --WFSTART
914 -------------------------------------------------------------
915 PROCEDURE wfStart(p_wf_type IN Varchar2,
916 p_wf_key IN Varchar2,
917 p_wf_process IN Varchar2,
918 p_user_performer IN Varchar2,
919 p_user_name IN Varchar2,
920 p_message_name IN Varchar2,
921 p_exception_type In Number,
922 p_exception_type_name IN Varchar2,
923 p_item_id In Number,
924 p_item_name IN Varchar2,
925 p_item_description IN Varchar2,
926 p_company_id In Number,
927 p_company_name IN Varchar2,
928 p_company_site_id IN Number,
929 p_company_site_name In varchar2,
930 p_supplier_id IN Number,
931 p_supplier_name IN Varchar2,
932 p_supplier_site_id IN Number,
933 p_supplier_site_name In varchar2,
934 p_supplier_item_name In varchar2,
935 p_customer_id IN Number,
936 p_customer_name IN Varchar2,
937 p_customer_site_id IN Number,
938 p_customer_site_name IN Varchar2,
939 p_customer_item_name In varchar2,
940 p_transaction_id1 IN Number,
941 p_transaction_id2 IN Number,
942 p_quantity In Number,
943 p_quantity1 IN Number,
944 p_quantity2 IN Number,
945 p_threshold IN Number,
946 p_lead_time In Number,
947 p_item_min_qty In Number,
948 p_item_max_qty In Number,
949 p_date1 IN Date,
950 p_date2 IN Date,
951 p_date3 IN Date,
952 p_date4 IN Date,
953 p_date5 IN Date,
954 p_exception_basis IN Varchar2,
955 p_order_creation_date1 IN Date,
956 p_order_creation_date2 IN Date,
957 p_order_number IN Varchar2,
958 p_release_number IN Varchar2,
959 p_line_number IN Varchar2,
960 p_end_order_number IN Varchar2,
961 p_end_order_rel_number IN Varchar2,
962 p_end_order_line_number IN Varchar2) IS
963
964
965
966
967 l_event_name varchar2(100);
968 l_xml_event_key varchar2(100);
969 l_parameterlist wf_parameter_list_t;
970 l_planner varchar2(100);
971
972 BEGIN
973 l_event_name := 'oracle.apps.msc.processing.netting';
974 l_parameterlist := wf_parameter_list_t();
975 l_planner := p_user_name;
976
977
978
979
980 --dbms_output.put_line('start in ');
981 -- Now build the event key and raise the event
982 l_xml_event_key := p_wf_key;
983
984
985 wf_log_pkg.wf_debug_flag := TRUE;
986
987
988
989 wf_event.AddParameterToList( p_name=>'FORWARD_TO_USERNAME',
990 p_value=>p_user_performer,
991 p_parameterlist=>l_parameterlist);
992
993
994 wf_event.AddParameterToList( p_name=>'PLANNER',
995 p_value=>p_user_name,
996 p_parameterlist=>l_parameterlist);
997
998
999 wf_event.AddParameterToList( p_name=>'MESSAGE_NAME',
1000 p_value=>p_message_name,
1001 p_parameterlist=>l_parameterlist);
1002
1003
1004 wf_event.AddParameterToList( p_name=>'EXCEPTION_TYPE',
1005 p_value=>p_exception_type,
1006 p_parameterlist=>l_parameterlist);
1007
1008
1009 wf_event.AddParameterToList( p_name=>'EXCEPTION_DESC',
1010 p_value=>p_exception_type_name,
1011 p_parameterlist=>l_parameterlist);
1012
1013
1014 wf_event.AddParameterToList( p_name=>'COMPANY_ID',
1015 p_value=> p_company_id,
1016 p_parameterlist=>l_parameterlist);
1017
1018 wf_event.AddParameterToList( p_name=>'COMPANY_NAME',
1019 p_value=> p_company_name,
1020 p_parameterlist=>l_parameterlist);
1021
1022
1023 wf_event.AddParameterToList( p_name=>'COMPANY_SITE_ID',
1024 p_value=> p_company_site_id,
1025 p_parameterlist=>l_parameterlist);
1026
1027
1028 wf_event.AddParameterToList( p_name=>'COMPANY_SITE_NAME',
1029 p_value=> p_company_site_name,
1030 p_parameterlist=>l_parameterlist);
1031
1032 wf_event.AddParameterToList( p_name=>'ITEM_ID',
1033 p_value=> p_item_id,
1034 p_parameterlist=>l_parameterlist);
1035
1036
1037 wf_event.AddParameterToList( p_name=>'ITEM_NAME',
1038 p_value=> p_item_name,
1039 p_parameterlist=>l_parameterlist);
1040
1041 wf_event.AddParameterToList( p_name=>'ITEM_DESC',
1042 p_value=> p_item_description,
1043 p_parameterlist=>l_parameterlist);
1044
1045 wf_event.AddParameterToList( p_name=>'SUPPLIER_ID',
1046 p_value=> p_supplier_id,
1047 p_parameterlist=>l_parameterlist);
1048
1049
1050 wf_event.AddParameterToList( p_name=>'SUPPLIER_NAME',
1051 p_value=> p_supplier_name,
1052 p_parameterlist=>l_parameterlist);
1053
1054
1055 wf_event.AddParameterToList( p_name=>'SUPPLIER_SITE_ID',
1056 p_value=> p_supplier_site_id,
1057 p_parameterlist=>l_parameterlist);
1058
1059 wf_event.AddParameterToList( p_name=>'SUPPLIER_SITE_NAME',
1060 p_value=> p_supplier_site_name,
1061 p_parameterlist=>l_parameterlist);
1062
1063
1064 wf_event.AddParameterToList( p_name=>'SUPPLIER_ITEM_NAME',
1065 p_value=> p_supplier_item_name,
1066 p_parameterlist=>l_parameterlist);
1067
1068 wf_event.AddParameterToList( p_name=>'CUSTOMER_ID',
1069 p_value=> p_customer_id,
1070 p_parameterlist=>l_parameterlist);
1071
1072
1073 wf_event.AddParameterToList( p_name=>'CUSTOMER_NAME',
1074 p_value=> p_customer_name,
1075 p_parameterlist=>l_parameterlist);
1076
1077
1078 wf_event.AddParameterToList( p_name=>'CUSTOMER_SITE_ID',
1079 p_value=> p_customer_site_id,
1080 p_parameterlist=>l_parameterlist);
1081
1082
1083 wf_event.AddParameterToList( p_name=>'CUSTOMER_SITE_NAME',
1084 p_value=> p_customer_site_name,
1085 p_parameterlist=>l_parameterlist);
1086
1087
1088 wf_event.AddParameterToList( p_name=>'CUSTOMER_ITEM_NAME',
1089 p_value=> p_customer_item_name,
1090 p_parameterlist=>l_parameterlist);
1091
1092
1093 wf_event.AddParameterToList( p_name=>'QUANTITY',
1094 p_value=> p_quantity,
1095 p_parameterlist=>l_parameterlist);
1096
1097 wf_event.AddParameterToList( p_name=>'TRANSACTION_ID1',
1098 p_value=> p_transaction_id1,
1099 p_parameterlist=>l_parameterlist);
1100
1101
1102 wf_event.AddParameterToList( p_name=>'TRANSACTION_ID2',
1103 p_value=> p_transaction_id2,
1104 p_parameterlist=>l_parameterlist);
1105
1106
1107 wf_event.AddParameterToList( p_name=>'QUANTITY1',
1108 p_value=> p_quantity1,
1109 p_parameterlist=>l_parameterlist);
1110
1111
1112 wf_event.AddParameterToList( p_name=>'QUANTITY2',
1113 p_value=> p_quantity2,
1114 p_parameterlist=>l_parameterlist);
1115
1116
1117 wf_event.AddParameterToList( p_name=>'THRESHOLD',
1118 p_value=> p_threshold,
1119 p_parameterlist=>l_parameterlist);
1120
1121
1122 wf_event.AddParameterToList( p_name=>'LEAD_TIME',
1123 p_value=> p_lead_time,
1124 p_parameterlist=>l_parameterlist);
1125
1126
1127 wf_event.AddParameterToList( p_name=>'ITEM_MIN_QTY',
1128 p_value=> p_item_min_qty,
1129 p_parameterlist=>l_parameterlist);
1130
1131 wf_event.AddParameterToList( p_name=>'ITEM_MAX_QTY',
1132 p_value=> p_item_max_qty,
1133 p_parameterlist=>l_parameterlist);
1134
1135
1136 wf_event.AddParameterToList( p_name=>'DATE1',
1137 p_value=> to_char(p_date1,wf_core.canonical_date_mask),
1138 p_parameterlist=>l_parameterlist);
1139
1140
1141 wf_event.AddParameterToList( p_name=>'DATE2',
1142 p_value=> to_char(p_date2,wf_core.canonical_date_mask),
1143 p_parameterlist=>l_parameterlist);
1144
1145
1146 wf_event.AddParameterToList( p_name=>'DATE3',
1147 p_value=> to_char(p_date3,wf_core.canonical_date_mask),
1148 p_parameterlist=>l_parameterlist);
1149
1150 wf_event.AddParameterToList( p_name=>'DATE4',
1151 p_value=> to_char(p_date4,wf_core.canonical_date_mask),
1152 p_parameterlist=>l_parameterlist);
1153
1154
1155 wf_event.AddParameterToList( p_name=>'DATE5',
1156 p_value=> to_char(p_date5,wf_core.canonical_date_mask),
1157 p_parameterlist=>l_parameterlist);
1158
1159 wf_event.AddParameterToList( p_name=>'ORDER_CREATION_DATE1',
1160 p_value=> to_char(p_order_creation_date1,wf_core.canonical_date_mask),
1161 p_parameterlist=>l_parameterlist);
1162
1163
1164 wf_event.AddParameterToList( p_name=>'ORDER_CREATION_DATE2',
1165 p_value=> to_char(p_order_creation_date2,wf_core.canonical_date_mask),
1166 p_parameterlist=>l_parameterlist);
1167
1168 wf_event.AddParameterToList( p_name=>'ORDER_NUMBER',
1169 p_value=> p_order_number,
1170 p_parameterlist=>l_parameterlist);
1171
1172 wf_event.AddParameterToList( p_name=>'RELEASE_NUMBER',
1173 p_value=> p_release_number,
1174 p_parameterlist=>l_parameterlist);
1175
1176
1177 wf_event.AddParameterToList( p_name=>'LINE_NUMBER',
1178 p_value=> p_line_number,
1179 p_parameterlist=>l_parameterlist);
1180
1181
1182 wf_event.AddParameterToList( p_name=>'END_ORDER_NUMBER',
1183 p_value=> p_end_order_number,
1184 p_parameterlist=>l_parameterlist);
1185
1186
1187
1188 wf_event.AddParameterToList( p_name=>'END_ORDER_REL_NUMBER',
1189 p_value=> p_end_order_rel_number,
1190 p_parameterlist=>l_parameterlist);
1191
1192
1193 wf_event.AddParameterToList( p_name=>'END_ORDER_LINE_NUMBER',
1194 p_value=> p_end_order_line_number,
1195 p_parameterlist=>l_parameterlist);
1196
1197 wf_event.AddParameterToList( p_name=>'EXCEPTION_BASIS',
1198 p_value=> p_exception_basis,
1199 p_parameterlist=>l_parameterlist);
1200
1201 wf_event.raise(p_event_name => l_event_name,
1202 p_event_key => l_xml_event_key,
1203 p_parameters => l_parameterlist
1204 );
1205
1206
1207 EXCEPTION
1208 when others then
1209 wf_core.context('msc_wfnotify_pub', 'wfStart',
1210 p_wf_type, p_wf_key);
1211 raise;
1212
1213 END wfStart;
1214
1215 ----------------------------------------------------------------------
1216 -- GETMESSGAE
1217 ---------------------------------------------------------------------
1218 FUNCTION getMessage(p_exception_code in Number) RETURN Varchar2 IS
1219 l_message Varchar2(100);
1220 BEGIN
1221
1222 if p_exception_code = 1 then
1223 l_message := 'MSG_EXCEP_1';
1224 elsif p_exception_code = 2 then
1225 l_message := 'MSG_EXCEP_2';
1226 elsif p_exception_code = 3 then
1227 l_message := 'MSG_EXCEP_3';
1228 elsif p_exception_code = 4 then
1229 l_message := 'MSG_EXCEP_4';
1230 elsif p_exception_code = 5 then
1231 l_message := 'MSG_EXCEP_5';
1232 elsif p_exception_code = 6 then
1233 l_message := 'MSG_EXCEP_6';
1234 elsif p_exception_code = 7 then
1235 l_message := 'MSG_EXCEP_7';
1236 elsif p_exception_code = 8 then
1237 l_message := 'MSG_EXCEP_8';
1238 elsif p_exception_code = 9 then
1239 l_message := 'MSG_EXCEP_9';
1240 elsif p_exception_code = 10 then
1241 l_message := 'MSG_EXCEP_10';
1242 elsif p_exception_code = 11 then
1243 l_message := 'MSG_EXCEP_11';
1244 elsif p_exception_code = 12 then
1245 l_message := 'MSG_EXCEP_12';
1246 elsif p_exception_code = 13 then
1247 l_message := 'MSG_EXCEP_13';
1248 elsif p_exception_code = 14 then
1249 l_message := 'MSG_EXCEP_14';
1250 elsif p_exception_code = 15 then
1251 l_message := 'MSG_EXCEP_15';
1252 elsif p_exception_code = 16 then
1253 l_message := 'MSG_EXCEP_16'; -- will not have excep17 and 18
1254 elsif p_exception_code = 19 then
1255 l_message := 'MSG_EXCEP_19';
1256 elsif p_exception_code = 20 then
1257 l_message := 'MSG_EXCEP_20';
1258 elsif p_exception_code = 21 then
1259 l_message := 'MSG_EXCEP_21';
1260 elsif p_exception_code = 22 then
1261 l_message := 'MSG_EXCEP_22';
1262 elsif p_exception_code = 23 then
1263 l_message := 'MSG_EXCEP_23';
1264 elsif p_exception_code = 24 then
1265 l_message := 'MSG_EXCEP_24';
1266 elsif p_exception_code = 25 then
1267 l_message := 'MSG_EXCEP_25';
1268 elsif p_exception_code = 26 then
1269 l_message := 'MSG_EXCEP_26';
1270 elsif p_exception_code = 27 then
1271 l_message := 'MSG_EXCEP_27';
1272 elsif p_exception_code = 28 then
1273 l_message := 'MSG_EXCEP_28';
1274 elsif p_exception_code = 29 then
1275 l_message := 'MSG_EXCEP_29';
1276 elsif p_exception_code = 30 then
1277 l_message := 'MSG_EXCEP_30';
1278 elsif p_exception_code = 31 then
1279 l_message := 'MSG_EXCEP_31';
1280 elsif p_exception_code = 32 then
1281 l_message := 'MSG_EXCEP_32';
1282 elsif p_exception_code = 33 then
1283 l_message := 'MSG_EXCEP_33';
1284 elsif p_exception_code = 34 then
1285 l_message := 'MSG_EXCEP_34';
1286 elsif p_exception_code = 35 then
1287 l_message := 'MSG_EXCEP_35';
1288 elsif p_exception_code = 36 then
1289 l_message := 'MSG_EXCEP_36';
1290 elsif p_exception_code = 39 then
1291 l_message := 'MSG_EXCEP_39';
1292 elsif p_exception_code = 40 then
1293 l_message := 'MSG_EXCEP_40';
1294 elsif p_exception_code = 43 then
1295 l_message := 'MSG_EXCEP_43';
1296 elsif p_exception_code = 44 then
1297 l_message := 'MSG_EXCEP_44';
1298 elsif p_exception_code = 45 then
1299 l_message := 'MSG_EXCEP_45';
1300 elsif p_exception_code = 46 then
1301 l_message := 'MSG_EXCEP_46';
1302 elsif p_exception_code = 47 then
1303 l_message := 'MSG_EXCEP_47';
1304 elsif p_exception_code = 48 then
1305 l_message := 'MSG_EXCEP_48';
1306 elsif p_exception_code = 49 then
1307 l_message := 'MSG_EXCEP_49';
1308 elsif p_exception_code = 50 then
1309 l_message := 'MSG_EXCEP_50';
1310 elsif p_exception_code = 51 then
1311 l_message := 'MSG_EXCEP_51';
1312 end if;
1313 return l_message;
1314 EXCEPTION
1315 WHEN others then
1316 l_message := null;
1317 null;
1318 END getMessage;
1319
1320 /*------------------------------------------------------
1321 send publish notifications
1322 -----------------------------------------------------*/
1323 PROCEDURE Launch_Publish_WF (p_errbuf OUT NOCOPY Varchar2,
1324 p_retcode OUT NOCOPY Number,
1325 p_designator IN Varchar2,
1326 p_version In Number,
1327 p_horizon_start IN date,
1328 p_horizon_end IN date,
1329 p_plan_id IN Number,
1330 p_sr_instance_id IN Number,
1331 p_org_id IN Number,
1332 p_item_id IN Number,
1333 p_supplier_id IN Number,
1334 p_supplier_site_id IN Number,
1335 p_customer_id IN Number,
1336 p_customer_site_id IN Number,
1337 p_planner_code IN Varchar2,
1338 p_abc_class IN Varchar2,
1339 p_planning_gp IN Varchar2,
1340 p_project_id IN Number,
1341 p_task_id IN Number,
1342 p_publish_program_type IN Number) IS
1343
1344
1345 CURSOR wf_publish_of_notify_c ( p_designator IN Varchar2,
1346 p_version In Number,
1347 p_horizon_start IN date,
1348 p_horizon_end IN date,
1349 p_plan_id IN Number,
1350 p_sr_instance_id IN Number,
1351 p_org_id IN Number,
1352 p_item_id IN Number,
1353 p_supplier_id IN Number,
1354 p_supplier_site_id IN Number,
1355 p_planner_code IN Varchar2,
1356 p_abc_class IN Varchar2,
1357 p_planning_gp IN Varchar2,
1358 p_project_id IN Number,
1359 p_task_id IN Number,
1360 p_publish_program_type In Number) IS
1361
1362 SELECT distinct sd.publisher_id,
1363 sd.publisher_name,
1364 sd.publisher_site_id,
1365 sd.publisher_site_name,
1366 sd.supplier_id,
1367 sd.supplier_name,
1368 sd.supplier_site_id,
1369 sd.supplier_site_name,
1370 sd.customer_id,
1371 sd.customer_name,
1372 sd.customer_site_id,
1373 sd.customer_site_name
1374 FROM msc_sup_dem_entries sd
1375 WHERE sd.publisher_order_type = p_publish_program_type and
1376 sd.plan_id = -1 and
1377 sd.publisher_id = 1 and
1378 exists (select cs.company_site_id
1379 from msc_plan_organizations o,
1380 msc_company_sites cs,
1381 msc_trading_partner_maps m,
1382 msc_trading_partners p
1383 where o.plan_id = p_plan_id
1384 AND O.ORGANIZATION_ID = NVL(p_org_id , O.ORGANIZATION_ID)
1385 AND O.SR_INSTANCE_ID = NVL(p_sr_instance_id , O.SR_INSTANCE_ID)
1386 AND P.SR_TP_ID = O.ORGANIZATION_ID
1387 AND P.SR_INSTANCE_ID = O.SR_INSTANCE_ID and
1388 p.partner_type = 3 and
1389 m.tp_key = p.partner_id and
1390 m.map_type = 2 and
1391 cs.company_site_id = m.company_key and
1392 cs.company_id = 1
1393 and sd.publisher_site_id =cs.company_site_id and rownum=1) and
1394 exists (select c.company_id
1395 from msc_companies c,
1396 msc_trading_partner_maps m,
1397 msc_company_relationships r
1398 where m.tp_key = nvl(p_supplier_id, m.tp_key) and
1399 m.map_type = 1 and
1400 r.relationship_id = m.company_key and
1401 r.subject_id = 1 and
1402 r.relationship_type = 2 and
1403 c.company_id = r.object_id and
1404 sd.supplier_id =c.company_id and rownum=1) and
1405 exists (select s.company_site_id
1406 from msc_company_sites s,
1407 msc_trading_partner_maps m
1408 where m.tp_key = nvl(p_supplier_site_id, m.tp_key) and
1409 m.map_type = 3 and
1410 s.company_site_id = m.company_key and
1411 s.company_id = sd.supplier_id and
1412 sd.supplier_site_id=s.company_site_id and rownum=1) and
1413 exists (select nvl(i.base_item_id,i.inventory_item_id)
1414 from msc_system_items i,
1415 msc_plan_organizations o
1416 where o.plan_id = p_plan_id and
1417 i.plan_id = o.plan_id
1418 AND O.ORGANIZATION_ID = NVL(p_org_id , O.ORGANIZATION_ID)
1419 AND O.SR_INSTANCE_ID =NVL(p_sr_instance_id , O.SR_INSTANCE_ID)
1420 AND I.ORGANIZATION_ID = O.ORGANIZATION_ID
1421 AND I.SR_INSTANCE_ID = O.SR_INSTANCE_ID and
1422 NVL(i.planner_code,'-99') = NVL(p_planner_code,
1423 NVL(i.planner_code,'-99')) and
1424 NVL(i.abc_class_name,'-99') = NVL(p_abc_class,
1425 NVL(i.abc_class_name,'-99')) and
1426 i.inventory_item_id = nvl(p_item_id, i.inventory_item_id)
1427 and NVL(sd.base_item_id, sd.inventory_item_id) = nvl(i.base_item_id,i.inventory_item_id)
1428 and rownum=1) and
1429 NVL(sd.planner_code,'-99') = nvl(p_planner_code, NVL(sd.planner_code, '-99')) and
1430 NVL(sd.planning_group,'-99') = nvl(p_planning_gp, NVL(sd.planning_group, '-99')) and
1431 NVL(sd.project_number,'-99') = nvl(p_project_id, NVL(sd.project_number, '-99')) and
1432 NVL(sd.task_number, '-99') = nvl(p_task_id, NVL(sd.task_number, '-99')) and
1433 sd.designator = p_designator and
1434 sd.version = p_version and
1435 key_date between nvl(p_horizon_start, sysdate - 36500) and
1436 nvl(p_horizon_end, sysdate + 36500);
1437
1438
1439 /*---------------------------------------------------------------
1440 Publish order forecast
1441 ---------------------------------------------------------------*/
1442 CURSOR wf_publish_of_item_c ( p_designator IN Varchar2,
1443 p_version In Number,
1444 p_horizon_start IN date,
1445 p_horizon_end IN date,
1446 p_plan_id IN Number,
1447 p_sr_instance_id IN Number,
1448 p_org_id IN Number,
1449 p_company_id IN Number,
1450 p_company_site_id IN Number,
1451 p_tp_company_id IN Number,
1452 p_tp_company_site_id IN Number,
1453 p_item_id IN Number) IS
1454
1455 SELECT distinct sd.inventory_item_id,
1456 sd.item_name,
1457 sd.item_description
1458 FROM msc_sup_dem_entries sd
1459 WHERE sd.publisher_order_type = 2 and
1460 sd.plan_id = -1 and
1461 sd.publisher_id = p_company_id and
1462 sd.publisher_site_id = p_company_site_id and
1463 sd.supplier_id = p_tp_company_id and
1464 sd.supplier_site_id = p_tp_company_site_id and
1465 sd.designator = p_designator and
1466 sd.version = p_version and
1467 sd.inventory_item_id = nvl(p_item_id, sd.inventory_item_id) and
1468 key_date between nvl(p_horizon_start, sysdate - 36500) and
1469 nvl(p_horizon_end, sysdate + 36500);
1470
1471
1472 /*------------------------------------------------------------------------
1473 publish supply commit
1474 ------------------------------------------------------------------------*/
1475 CURSOR wf_publish_sc_notify_c ( p_designator IN Varchar2,
1476 p_version In Number,
1477 p_horizon_start IN date,
1478 p_horizon_end IN date,
1479 p_plan_id IN Number,
1480 p_sr_instance_id IN Number,
1481 p_org_id IN Number,
1482 p_item_id IN Number,
1483 p_customer_id IN Number,
1484 p_customer_site_id IN Number,
1485 p_planner_code IN Varchar2,
1486 p_abc_class IN Varchar2,
1487 p_planning_gp IN Varchar2,
1488 p_project_id IN Number,
1489 p_task_id IN Number,
1490 p_publish_program_type In Number) IS
1491
1492 SELECT distinct sd.publisher_id,
1493 sd.publisher_name,
1494 sd.publisher_site_id,
1495 sd.publisher_site_name,
1496 sd.supplier_id,
1497 sd.supplier_name,
1498 sd.supplier_site_id,
1499 sd.supplier_site_name,
1500 sd.customer_id,
1501 sd.customer_name,
1502 sd.customer_site_id,
1503 sd.customer_site_name
1504 FROM msc_sup_dem_entries sd
1505 WHERE sd.publisher_order_type = p_publish_program_type and
1506 sd.plan_id = -1 and
1507 sd.publisher_id = 1 and
1508 sd.publisher_site_id IN (select cs.company_site_id
1509 from msc_plan_organizations o,
1510 msc_company_sites cs,
1511 msc_trading_partner_maps m,
1512 msc_trading_partners p
1513 where o.plan_id = p_plan_id and
1514 p.sr_tp_id = nvl(p_org_id, o.organization_id) and
1515 p.sr_instance_id = nvl(p_sr_instance_id,
1516 o.sr_instance_id) and
1517 p.partner_type = 3 and
1518 m.tp_key = p.partner_id and
1519 m.map_type = 2 and
1520 cs.company_site_id = m.company_key and
1521 cs.company_id = 1) and
1522 sd.customer_id IN (select distinct c.company_id
1523 from msc_companies c,
1524 msc_trading_partner_maps m,
1525 msc_company_relationships r
1526 where m.tp_key = nvl(p_customer_id, m.tp_key) and
1527 m.map_type = 1 and
1528 r.relationship_id = m.company_key and
1529 r.subject_id = 1 and
1530 r.relationship_type = 1 and
1531 c.company_id = r.object_id) and
1532 sd.customer_site_id IN (select s.company_site_id
1533 from msc_company_sites s,
1534 msc_trading_partner_maps m
1535 where m.tp_key = nvl(p_customer_site_id, m.tp_key) and
1536 m.map_type = 3 and
1537 s.company_site_id = m.company_key and
1538 s.company_id = sd.customer_id) and
1539 NVL(sd.base_item_id, sd.inventory_item_id) IN (select nvl(i.base_item_id,i.inventory_item_id)
1540 from msc_system_items i,
1541 msc_plan_organizations o
1542 where o.plan_id = p_plan_id and
1543 i.plan_id = o.plan_id and
1544 i.organization_id = nvl(p_org_id,
1545 o.organization_id) and
1546 i.sr_instance_id = nvl(p_sr_instance_id,
1547 o.sr_instance_id) and
1548 NVL(i.planner_code,'-99') = NVL(p_planner_code,
1549 NVL(i.planner_code,'-99')) and
1550 NVL(i.abc_class_name,'-99') = NVL(p_abc_class,
1551 NVL(i.abc_class_name,'-99')) and
1552 i.inventory_item_id = nvl(p_item_id, i.inventory_item_id)) and
1553 NVL(sd.planner_code,'-99') = nvl(p_planner_code, NVL(sd.planner_code, '-99')) and
1554 NVL(sd.planning_group,'-99') = nvl(p_planning_gp, NVL(sd.planning_group, '-99')) and
1555 NVL(sd.project_number,'-99') = nvl(p_project_id, NVL(sd.project_number, '-99')) and
1556 NVL(sd.task_number, '-99') = nvl(p_task_id, NVL(sd.task_number, '-99')) and
1557 sd.designator = p_designator and
1558 sd.version = p_version and
1559 key_date between nvl(p_horizon_start, sysdate - 36500) and
1560 nvl(p_horizon_end, sysdate + 36500);
1561
1562
1563 CURSOR wf_publish_sc_item_c ( p_designator IN Varchar2,
1564 p_version In Number,
1565 p_horizon_start IN date,
1566 p_horizon_end IN date,
1567 p_plan_id IN Number,
1568 p_sr_instance_id IN Number,
1569 p_org_id IN Number,
1570 p_company_id IN Number,
1571 p_company_site_id IN Number,
1572 p_tp_company_id IN Number,
1573 p_tp_company_site_id IN Number,
1574 p_item_id IN Number) IS
1575
1576 SELECT distinct sd.inventory_item_id,
1577 sd.item_name,
1578 sd.item_description
1579 FROM msc_sup_dem_entries sd
1580 WHERE sd.publisher_order_type = 3 and
1581 sd.plan_id = -1 and
1582 sd.publisher_id = p_company_id and
1583 sd.publisher_site_id = p_company_site_id and
1584 sd.customer_id = p_tp_company_id and
1585 sd.customer_site_id = p_tp_company_site_id and
1586 sd.designator = p_designator and
1587 sd.version = p_version and
1588 sd.inventory_item_id = nvl(p_item_id, sd.inventory_item_id) and
1589 key_date between nvl(p_horizon_start, sysdate - 36500) and
1590 nvl(p_horizon_end, sysdate + 36500);
1591
1592
1593
1594
1595 /*-------------------------------------------------------------------
1596 get the receipient user name
1597 --------------------------------------------------------------------*/
1598
1599 CURSOR partner_con_by_site_c(p_company_id in number,
1600 p_company_site_id in Number) IS
1601 SELECT distinct con.name
1602 FROM msc_trading_partner_maps map,
1603 msc_company_sites site,
1604 msc_partner_contacts con
1605 WHERE map.map_type = 3
1606 AND map.company_key = site.company_site_id
1607 AND map.tp_key = con.partner_site_id
1608 AND site.company_id = p_company_id
1609 AND site.company_site_id = p_company_site_id;
1610
1611
1612 /*
1613 SELECT distinct con.name
1614 FROM msc_trading_partner_maps map,
1615 msc_company_sites site,
1616 msc_partner_contacts con,
1617 msc_trading_partner_sites tps
1618 WHERE map.map_type = 3
1619 AND map.company_key = site.company_site_id
1620 AND map.tp_key = tps.partner_site_id
1621 AND tps.partner_site_id = con.partner_site_id
1622 AND tps.sr_instance_id = con.sr_instance_id
1623 AND site.company_id = p_company_id
1624 AND site.company_site_id = p_company_site_id;
1625
1626 */
1627
1628 l_wf_type Varchar2(100);
1629 l_wf_key Varchar2(100);
1630 l_wf_process Varchar2(200);
1631 l_user_id Number;
1632 l_user_performer Varchar2(240);
1633 l_real_name Varchar2(240);
1634 l_item_name Varchar2(4000);
1635 l_item_desc Varchar2(4000);
1636
1637
1638 t_company_id number_arr;
1639 t_company_site_id number_arr;
1640 t_item_id number_arr;
1641 t_supplier_id number_arr;
1642 t_supplier_site_id number_arr;
1643 t_customer_id number_arr;
1644 t_customer_site_id number_arr;
1645
1646 t_company_name msc_x_netting_pkg.publisherList;
1647 t_company_site_name msc_x_netting_pkg.pubsiteList;
1648 t_item_name msc_x_netting_pkg.itemnameList;
1649 t_item_desc msc_x_netting_pkg.itemdescList;
1650 t_supplier_name msc_x_netting_pkg.publisherList;
1651 t_supplier_site_name msc_x_netting_pkg.pubsiteList;
1652 t_customer_name msc_x_netting_pkg.publisherList;
1653 t_customer_site_name msc_x_netting_pkg.pubsiteList;
1654 t_version number_arr;
1655 t_designator designatorList;
1656
1657
1658 l_next_number Number;
1659
1660 l_event_name varchar2(100);
1661 l_xml_event_key varchar2(100);
1662 l_parameterlist wf_parameter_list_t;
1663
1664
1665 BEGIN
1666
1667 t_company_id := number_arr();
1668 t_company_site_id := number_arr();
1669 t_item_id := number_arr();
1670 t_supplier_id := number_arr();
1671 t_supplier_site_id := number_arr();
1672 t_customer_id := number_arr();
1673 t_customer_site_id := number_arr();
1674 t_version := number_arr();
1675 l_event_name := 'oracle.apps.msc.notification';
1676 l_parameterlist := wf_parameter_list_t();
1677 l_wf_type := 'MSCPUB';
1678 l_wf_process := 'MSC_PUB_NOTIFY';
1679
1680
1681
1682 select msc_cl_refresh_s.nextval into l_next_number from dual;
1683 IF (p_publish_program_type = G_ORDER_FORECAST) THEN
1684 FND_FILE.PUT_LINE(FND_FILE.LOG,'Launch order forecast notification');
1685 FND_FILE.PUT_LINE(FND_FILE.LOG,'Designator ' || p_designator || ' version ' || p_version);
1686
1687 open wf_publish_of_notify_c( p_designator,
1688 p_version,
1689 p_horizon_start,
1690 p_horizon_end,
1691 p_plan_id,
1692 p_sr_instance_id,
1693 p_org_id,
1694 p_item_id,
1695 p_supplier_id,
1696 p_supplier_site_id,
1697 p_planner_code,
1698 p_abc_class,
1699 p_planning_gp,
1700 p_project_id,
1701 p_task_id,
1702 p_publish_program_type);
1703
1704 FETCH wf_publish_of_notify_c BULK COLLECT INTO
1705 t_company_id,
1706 t_company_name,
1707 t_company_site_id,
1708 t_company_site_name,
1709 t_supplier_id,
1710 t_supplier_name,
1711 t_supplier_site_id,
1712 t_supplier_site_name,
1713 t_customer_id,
1714 t_customer_name,
1715 t_customer_site_id,
1716 t_customer_site_name;
1717
1718 CLOSE wf_publish_of_notify_c;
1719
1720 FND_FILE.PUT_LINE(FND_FILE.LOG,'WF OF record fetched:= ' || t_company_id.COUNT);
1721 --dbms_output.put_line('wf count ' || t_company_id.COUNT);
1722 IF (t_company_id is not null and t_company_id.COUNT > 0) THEN
1723
1724 FOR j in 1 .. t_company_id.COUNT
1725 LOOP
1726 FND_FILE.PUT_LINE(FND_FILE.LOG,'WF: Loop supp ' || t_supplier_id(j) || ' sup site ' || t_supplier_site_id(j));
1727 --dbms_output.put_line('Loop sup company ' || t_supplier_id(j) || ' sup site ' || t_supplier_site_id(j));
1728 /*--------------------------------------------------------------------
1729 append item
1730 --------------------------------------------------------------------*/
1731 open wf_publish_of_item_c( p_designator,
1732 p_version,
1733 p_horizon_start,
1734 p_horizon_end,
1735 p_plan_id,
1736 p_sr_instance_id,
1737 p_org_id,
1738 t_company_id(j),
1739 t_company_site_id(j),
1740 t_supplier_id(j),
1741 t_supplier_site_id(j),
1742 p_item_id);
1743
1744 FETCH wf_publish_of_item_c BULK COLLECT INTO
1745 t_item_id,
1746 t_item_name,
1747 t_item_desc;
1748 CLOSE wf_publish_of_item_c;
1749
1750 l_item_name := null;
1751 l_item_desc := null;
1752
1753 FOR k in 1 .. t_item_id.COUNT LOOP
1754 IF (t_item_id.COUNT = 1 or k = t_item_id.COUNT) THEN
1755 l_item_name := substr((l_item_name || t_item_name(k)),1,1000);
1756 ELSIF (k = t_item_id.COUNT) THEN
1757 l_item_name := substr((l_item_name || t_item_name(k)),1,1000);
1758 ELSE
1759 l_item_name := substr((l_item_name || t_item_name(k) || ','),1,1000);
1760 END IF;
1761 END LOOP;
1762
1763
1764
1765 open partner_con_by_site_c(t_supplier_id(j),
1766 t_supplier_site_id(j));
1767
1768 loop
1769 fetch partner_con_by_site_c into l_user_performer;
1770 exit when partner_con_by_site_c%NOTFOUND;
1771
1772 --dbms_output.put_line('user performer ' || l_user_performer);
1773
1774 IF (l_user_performer is not null) THEN
1775
1776 l_wf_key := 'OF' ||
1777 to_char(t_supplier_id(j)) || '-' ||
1778 to_char(t_supplier_site_id(j)) || '-' ||
1779 to_char(t_company_id(j)) || '-' ||
1780 to_char(t_company_site_id(j)) || '-' ||
1781 l_user_performer ||
1782 to_char(l_next_number) ;
1783 begin
1784
1785 SELECT substr(display_name,instr(display_name,',')+ 1) || ' ' ||
1786 substr(display_name,1, instr(display_name,',') -1)
1787 INTO l_real_name
1788 FROM wf_users
1789 WHERE name = l_user_performer;
1790 exception
1791 when others then
1792 l_real_name := null;
1793 end;
1794 FND_FILE.PUT_LINE(FND_FILE.LOG,'WF: user ' || l_real_name);
1795 /**
1796 dbms_output.put_line('start workflow process');
1797 dbms_output.put_line('user ' || l_real_name);
1798 dbms_output.put_line('company ' || t_company_name(j));
1799 dbms_output.put_line('company site ' || t_company_site_name(j));
1800 dbms_output.put_line('version' || p_version);
1801 dbms_output.put_line('designator ' || p_designator);
1802 -- dbms_output.put_line('item ' || l_item_name);
1803 **/
1804
1805 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Sending notification ');
1806
1807
1808 -- create a Workflow process for the (item/org/supplier)
1809 wf_engine.CreateProcess
1810 ( itemtype => l_wf_type
1811 , itemkey => l_wf_key
1812 , process => l_wf_process
1813 );
1814
1815 wf_engine.SetItemAttrText
1816 ( itemtype => l_wf_type
1817 , itemkey => l_wf_key
1818 , aname => 'FORWARD_TO_USERNAME'
1819 , avalue => l_user_performer
1820 );
1821
1822 wf_engine.SetItemAttrText
1823 ( itemtype => l_wf_type
1824 , itemkey => l_wf_key
1825 , aname => 'MESSAGE_NAME'
1826 , avalue => 'PUBLISH_ORDER_FORECAST'
1827 );
1828
1829 wf_engine.SetItemAttrText
1830 ( itemtype => l_wf_type
1831 , itemkey => l_wf_key
1832 , aname => 'COMPANY_NAME'
1833 , avalue => t_company_name(j)
1834 );
1835
1836 wf_engine.SetItemAttrText
1837 ( itemtype => l_wf_type
1838 , itemkey => l_wf_key
1839 , aname => 'COMPANY_SITE_NAME'
1840 , avalue => t_company_site_name(j)
1841 );
1842
1843 wf_engine.SetItemAttrNumber
1844 ( itemtype => l_wf_type
1845 , itemkey => l_wf_key
1846 , aname => 'VERSION'
1847 , avalue => p_version
1848 );
1849
1850 wf_engine.SetItemAttrText
1851 ( itemtype => l_wf_type
1852 , itemkey => l_wf_key
1853 , aname => 'DESIGNATOR'
1854 , avalue => p_designator
1855 );
1856
1857 wf_engine.SetItemAttrText
1858 ( itemtype => l_wf_type
1859 , itemkey => l_wf_key
1860 , aname => 'ITEM_NAME'
1861 , avalue => l_item_name
1862 );
1863
1864 -- start Workflow process for item/org/supplier
1865 wf_engine.StartProcess
1866 ( itemtype => l_wf_type
1867 , itemkey => l_wf_key
1868 );
1869
1870
1871 END IF;
1872
1873
1874 end loop;
1875 close partner_con_by_site_c;
1876
1877 END LOOP;
1878 END IF;
1879
1880 /*----------------------------------------------------------------------
1881 SUPPLY COMMIT;
1882 -----------------------------------------------------------------------*/
1883 ELSIF (p_publish_program_type = G_SUPPLY_COMMIT) THEN
1884 open wf_publish_sc_notify_c( p_designator,
1885 p_version,
1886 p_horizon_start,
1887 p_horizon_end,
1888 p_plan_id,
1889 p_sr_instance_id,
1890 p_org_id,
1891 p_item_id,
1892 p_customer_id,
1893 p_customer_site_id,
1894 p_planner_code,
1895 p_abc_class,
1896 p_planning_gp,
1897 p_project_id,
1898 p_task_id,
1899 p_publish_program_type);
1900
1901 FETCH wf_publish_sc_notify_c BULK COLLECT INTO
1902 t_company_id,
1903 t_company_name,
1904 t_company_site_id,
1905 t_company_site_name,
1906 t_supplier_id,
1907 t_supplier_name,
1908 t_supplier_site_id,
1909 t_supplier_site_name,
1910 t_customer_id,
1911 t_customer_name,
1912 t_customer_site_id,
1913 t_customer_site_name;
1914
1915 CLOSE wf_publish_sc_notify_c;
1916
1917 FND_FILE.PUT_LINE(FND_FILE.LOG,'WF SC record fetched:= ' || t_company_id.COUNT);
1918 --dbms_output.put_line('wf count ' || t_company_id.COUNT);
1919 IF (t_company_id is not null and t_company_id.COUNT > 0) THEN
1920
1921 FOR j in 1 .. t_company_id.COUNT
1922 LOOP
1923 FND_FILE.PUT_LINE(FND_FILE.LOG, 'WF: Loop cust ' || t_customer_id(j) || ' cust site ' || t_customer_site_id(j));
1924 --dbms_output.put_line('Loop cust company ' || t_customer_id(j) || ' cust site ' || t_customer_site_id(j));
1925 /*--------------------------------------------------------------------
1926 append item
1927 --------------------------------------------------------------------*/
1928 open wf_publish_sc_item_c( p_designator,
1929 p_version,
1930 p_horizon_start,
1931 p_horizon_end,
1932 p_plan_id,
1933 p_sr_instance_id,
1934 p_org_id,
1935 t_company_id(j),
1936 t_company_site_id(j),
1937 t_customer_id(j),
1938 t_customer_site_id(j),
1939 p_item_id);
1940
1941 FETCH wf_publish_sc_item_c BULK COLLECT INTO
1942 t_item_id,
1943 t_item_name,
1944 t_item_desc;
1945 CLOSE wf_publish_sc_item_c;
1946
1947 l_item_name := null;
1948 l_item_desc := null;
1949
1950 FOR k in 1 .. t_item_id.COUNT LOOP
1951 IF (t_item_id.COUNT = 1 or k = t_item_id.COUNT) THEN
1952 l_item_name := substr((l_item_name || t_item_name(k)),1,1000);
1953 ELSIF (k = t_item_id.COUNT) THEN
1954 l_item_name := substr((l_item_name || t_item_name(k)),1,1000);
1955 ELSE
1956 l_item_name := substr((l_item_name || t_item_name(k) || ','),1,1000);
1957 END IF;
1958 END LOOP;
1959
1960
1961
1962 open partner_con_by_site_c(t_customer_id(j),
1963 t_customer_site_id(j));
1964
1965 loop
1966 fetch partner_con_by_site_c into l_user_performer;
1967 exit when partner_con_by_site_c%NOTFOUND;
1968
1969 --dbms_output.put_line('user performer ' || l_user_performer);
1970
1971 IF (l_user_performer is not null) THEN
1972
1973 l_wf_key := 'SC' || to_char(t_customer_id(j)) || '-' ||
1974 to_char(t_customer_site_id(j)) || '-' ||
1975 to_char(t_company_id(j)) || '-' ||
1976 to_char(t_company_site_id(j)) || '-' ||
1977 l_user_performer ||
1978 to_char(l_next_number) ;
1979 begin
1980
1981 SELECT substr(display_name,instr(display_name,',')+ 1) || ' ' ||
1982 substr(display_name,1, instr(display_name,',') -1)
1983 INTO l_real_name
1984 FROM wf_users
1985 WHERE name = l_user_performer;
1986 exception
1987 when others then
1988 l_real_name := null;
1989 end;
1990
1991 FND_FILE.PUT_LINE(FND_FILE.LOG,'WF: user ' || l_real_name);
1992 /*
1993 dbms_output.put_line('start workflow process');
1994 dbms_output.put_line('user ' || l_real_name);
1995 dbms_output.put_line('company ' || t_company_name(j));
1996 dbms_output.put_line('company site ' || t_company_site_name(j));
1997 dbms_output.put_line('version' || p_version);
1998 dbms_output.put_line('designator ' || p_designator);
1999 -- dbms_output.put_line('item ' || l_item_name);
2000 */
2001 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Sending notification ');
2002
2003
2004 -- create a Workflow process for the (item/org/supplier)
2005 wf_engine.CreateProcess
2006 ( itemtype => l_wf_type
2007 , itemkey => l_wf_key
2008 , process => l_wf_process
2009 );
2010
2011 wf_engine.SetItemAttrText
2012 ( itemtype => l_wf_type
2013 , itemkey => l_wf_key
2014 , aname => 'FORWARD_TO_USERNAME'
2015 , avalue => l_user_performer
2016 );
2017
2018 wf_engine.SetItemAttrText
2019 ( itemtype => l_wf_type
2020 , itemkey => l_wf_key
2021 , aname => 'MESSAGE_NAME'
2022 , avalue => 'PUBLISH_SUPPLY_COMMIT'
2023 );
2024
2025 wf_engine.SetItemAttrText
2026 ( itemtype => l_wf_type
2027 , itemkey => l_wf_key
2028 , aname => 'COMPANY_NAME'
2029 , avalue => t_company_name(j)
2030 );
2031
2032 wf_engine.SetItemAttrText
2033 ( itemtype => l_wf_type
2034 , itemkey => l_wf_key
2035 , aname => 'COMPANY_SITE_NAME'
2036 , avalue => t_company_site_name(j)
2037 );
2038
2039 wf_engine.SetItemAttrNumber
2040 ( itemtype => l_wf_type
2041 , itemkey => l_wf_key
2042 , aname => 'VERSION'
2043 , avalue => p_version
2044 );
2045
2046 wf_engine.SetItemAttrText
2047 ( itemtype => l_wf_type
2048 , itemkey => l_wf_key
2049 , aname => 'DESIGNATOR'
2050 , avalue => p_designator
2051 );
2052
2053 wf_engine.SetItemAttrText
2054 ( itemtype => l_wf_type
2055 , itemkey => l_wf_key
2056 , aname => 'ITEM_NAME'
2057 , avalue => l_item_name
2058 );
2059
2060 -- start Workflow process for item/org/supplier
2061 wf_engine.StartProcess
2062 ( itemtype => l_wf_type
2063 , itemkey => l_wf_key
2064 );
2065
2066
2067 END IF;
2068
2069
2070 end loop;
2071 close partner_con_by_site_c;
2072
2073 END LOOP;
2074 END IF;
2075
2076 END IF;
2077
2078 EXCEPTION
2079 when others then
2080 wf_core.context('msc_wfnotify_pub', 'wfStart',
2081 l_wf_type, l_wf_key);
2082
2083 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in msc_x_wfnotify_pkg.Launch_Publish_WF ' || sqlerrm);
2084 raise;
2085
2086 END Launch_Publish_WF;
2087
2088 END msc_x_wfnotify_pkg;