[Home] [Help]
PACKAGE BODY: APPS.MSC_EXP_WF
Source
1 PACKAGE BODY msc_exp_wf AS
2 /*$Header: MSCEXWFB.pls 120.6 2010/09/08 09:06:26 skakani ship $ */
3
4 CURSOR PLANNER_C( p_plan_id in number, p_inventory_item_id in number,
5 p_organization_id in number, p_instance_id in number) IS
6 SELECT distinct pl.user_name
7 FROM msc_planners pl,
8 msc_system_items sys
9 WHERE sys.plan_id = p_plan_id
10 AND sys.organization_id = p_organization_id
11 AND sys.sr_instance_id = p_instance_id
12 AND sys.inventory_item_id = p_inventory_item_id
13 AND pl.organization_id = sys.organization_id
14 AND pl.sr_instance_id = sys.sr_instance_id
15 AND pl.planner_code = sys.planner_code;
16
17 PROCEDURE launch_workflow(errbuf OUT NOCOPY VARCHAR2,
18 retcode OUT NOCOPY NUMBER,
19 p_plan_id IN NUMBER,
20 p_exception_id IN NUMBER DEFAULT NULL,
21 p_query_id IN NUMBER DEFAULT NULL) IS
22
23
24 CURSOR EXCEPTION_DETAILS_C1 (p_plan_id in NUMBER,
25 p_exception_id in NUMBER) IS
26 SELECT exp.exception_detail_id,
27 exp.organization_id,
28 exp.sr_instance_id,
29 exp.inventory_item_id,
30 exp.exception_type,
31 mtp.organization_code,
32 mi.item_name,mi.description,
33 ml.meaning,
34 nvl(decode(exp.exception_type,
35 17, msc_get_name.project(
36 exp.number1,
37 exp.organization_id,
38 exp.plan_id,
39 exp.sr_instance_id),
40 18, msc_get_name.project(
41 exp.number1,
42 exp.organization_id,
43 exp.plan_id,
44 exp.sr_instance_id),
45 msc_get_name.project(
46 sup.project_id,
47 sup.organization_id,
48 sup.plan_id,
49 sup.sr_instance_id)), 'N/A'),
50 nvl(decode(exp.exception_type,
51 19, msc_get_name.project(
52 md2.project_id,
53 md2.organization_id,
54 md2.plan_id,
55 md2.sr_instance_id), null), 'N/A'),
56 nvl(decode(exp.exception_type,
57 17, msc_get_name.task(
58 exp.number2,
59 exp.number1,
60 exp.organization_id,
61 exp.plan_id,
62 exp.sr_instance_id),
63 18, msc_get_name.task(
64 exp.number2,
65 exp.number1,
66 exp.organization_id,
67 exp.plan_id,
68 exp.sr_instance_id),
69 msc_get_name.task(
70 sup.task_id,
71 sup.project_id,
72 sup.organization_id,
73 sup.plan_id,
74 sup.sr_instance_id)), 'N/A'),
75 nvl(decode(exp.exception_type,
76 19, msc_get_name.task(
77 md2.task_id,
78 md2.project_id,
79 md2.organization_id,
80 md2.plan_id,
81 md2.sr_instance_id), null), 'N/A'),
82 decode(exp.exception_type, 17, char1, 18, char1,
83 sup.planning_group),
84 DECODE(exp.EXCEPTION_TYPE, 2,NULL, 3, NULL, 6, NULL, 7, NULL,
85 10, sup.new_schedule_date, 18, NULL, 20, NULL,
86 27, md.using_assembly_demand_date,28, NULL,37, NULL,
87 24, md.using_assembly_demand_date,
88 25, md.using_assembly_demand_date,
89 26, md.using_assembly_demand_date,
90 49,msc_exp_wf.substitute_supply_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
91 exp.DATE1),
92 DECODE(EXP.EXCEPTION_TYPE,1,EXP.DATE1, 2, EXP.DATE1, 3, EXP.DATE1,
93 6, EXP.DATE1, 7,EXP.DATE1, 17, EXP.DATE1, 18, EXP.DATE1,
94 20, EXP.DATE1, 28, EXP.DATE1, 37, EXP.DATE1,15, md2.using_assembly_demand_date,16, md2.using_assembly_demand_date,49,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
95 NULL),
96 DECODE(exp.EXCEPTION_TYPE, 15, msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),16,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),exp.date2),
97 DECODE(exp.EXCEPTION_TYPE, 9, sup.SCHEDULE_COMPRESS_DAYS,NULL),
98 decode(exp.exception_type,
99 37,nvl(exp.number4,0)-nvl(exp.quantity,0),
100 28,MSC_EXP_WF.SupplierCapacity(p_plan_id,exp.exception_detail_id),
101 49,msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,
102 exp.supplier_id),
103 exp.quantity),
104 decode(exp.exception_type, 12, sup.lot_number),
105 decode(exp.exception_type, 1, NULL, 2, NULL, 3, NULL,
106 9, NVL(sup.order_number,exp.number1),12, NULL,
107 13, md.order_number,
108 14, NVL(md.order_number,msc_get_name.designator(
109 md.schedule_designator_id)),
110 16, nvl(sup.order_number,sup.transaction_id),
111 17, NULL, 18, NULL , 20, NULL,
112 24, md.order_number, 25, md.order_number,
113 26, msc_get_name.designator(md.schedule_designator_id),
114 27, msc_get_name.designator(md.schedule_designator_id),
115 28, NULL, 37, NULL, 70,md.order_number,
116 sup.order_number),
117 sup_ml.meaning,
118 msc_get_name.item_name(
119 decode(exp.exception_type,
120 15, md2.inventory_item_id, 16, md2.inventory_item_id,
121 19, md2.inventory_item_id, 24, md.inventory_item_id,
122 25, md.inventory_item_id, 26, md.inventory_item_id,
123 27, md.inventory_item_id, 49, exp.number1,null),
124 null,null,null),
125 msc_get_name.item_desc(
126 decode(exp.exception_type,
127 15, md2.inventory_item_id, 16, md2.inventory_item_id,
128 19, md2.inventory_item_id, 24, md.inventory_item_id,
129 25, md.inventory_item_id, 26, md.inventory_item_id,
130 27, md.inventory_item_id, 49, exp.number1,null),
131 exp.organization_id,p_plan_id,exp.sr_instance_id),
132 decode(exp.exception_type,
133 15,md2.order_number, 19,md2.order_number,
134 24,md.order_number,25,md.order_number,
135 26,msc_get_name.designator(md.schedule_designator_id),
136 27,msc_get_name.designator(md.schedule_designator_id),
137 49,msc_get_name.demand_order_number(exp.plan_id,
138 exp.sr_instance_id, exp.supplier_id),
139 NULL) ,
140 null,
141 decode(exp.exception_type, 49,
142 msc_get_name.org_code(exp.number2, exp.sr_instance_id),
143 15,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),
144 16,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),null),
145 decode(exp.exception_type, 28, exp.quantity, 37, exp.quantity,
146 49, exp.quantity,
147 15, md.using_requirement_quantity,
148 16, md.using_requirement_quantity,
149 17, md.using_requirement_quantity,
150 18, md.using_requirement_quantity,
151 2, md.using_requirement_quantity,
152 3, msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,md2.demand_id),null),
153 decode(exp.exception_type, 12, null, 13, null, 14, null,
154 17, null, 18, null, 24, null, 25, null, 26, null,
155 27, null, 28, null, 37, exp.number2, exp.number1),
156 decode(exp.exception_type, 49, msc_exp_wf.demand_order_type(exp.plan_id,exp.sr_instance_id,exp.supplier_id),sup.order_type),
157 decode(exp.exception_type, 37, exp.number4,null),
158 exp.number1,
159 exp.number2
160 FROM msc_supplies sup,
161 msc_full_pegging mfp,
162 msc_demands md2,
163 msc_demands md,
164 msc_exception_details exp,
165 msc_system_items mi,
166 msc_trading_partners mtp,
167 mfg_lookups ml,
168 mfg_lookups sup_ml
169 WHERE exp.exception_type in
170 (1,2,3,6,7,8,9,10,12,13,14,15,16,17,18,19,20,24,25,26,27,28,37,49,70)
171 AND exp.plan_id = p_plan_id
172 AND exp.exception_detail_id = p_exception_id
173 AND sup.plan_id (+) = exp.plan_id
174 AND sup.transaction_id (+) = exp.number1
175 and sup.sr_instance_id(+) = exp.sr_instance_id
176 AND mfp.plan_id (+) = exp.plan_id
177 AND mfp.pegging_id (+) = exp.number2
178 and mfp.sr_instance_id(+) = exp.sr_instance_id
179 AND md2.plan_id (+) = mfp.plan_id
180 AND md2.demand_id (+) = mfp.demand_id
181 and md2.sr_instance_id(+) = mfp.sr_instance_id
182 AND md.plan_id (+) = exp.plan_id
183 AND md.demand_id (+) = exp.number1
184 and md.sr_instance_id(+) = exp.sr_instance_id
185 AND mi.inventory_item_id = exp.inventory_item_id
186 AND mi.organization_id = exp.organization_id
187 AND mi.plan_id = exp.plan_id
188 AND mi.sr_instance_id = exp.sr_instance_id
189 AND mtp.sr_tp_id = exp.organization_id
190 AND mtp.sr_instance_id = exp.sr_instance_id
191 AND mtp.partner_type = 3
192 and ml.lookup_type = 'MRP_EXCEPTION_CODE_TYPE'
193 and ml.lookup_code = exp.exception_type
194 and sup_ml.lookup_type(+) = 'MRP_ORDER_TYPE'
195 and sup_ml.lookup_code(+) = sup.order_type;
196
197 CURSOR EXCEPTION_DETAILS_C2 (p_plan_id in NUMBER,
198 p_query_id in NUMBER) IS
199 SELECT exp.exception_detail_id,
200 exp.organization_id,
201 exp.sr_instance_id,
202 exp.inventory_item_id,
203 exp.exception_type,
204 mtp.organization_code,
205 mi.item_name,mi.description,
206 ml.meaning,
207 nvl(decode(exp.exception_type,
208 17, msc_get_name.project(
209 exp.number1,
210 exp.organization_id,
211 exp.plan_id,
212 exp.sr_instance_id),
213 18, msc_get_name.project(
214 exp.number1,
215 exp.organization_id,
216 exp.plan_id,
217 exp.sr_instance_id),
218 msc_get_name.project(
219 sup.project_id,
220 sup.organization_id,
221 sup.plan_id,
222 sup.sr_instance_id)), 'N/A'),
223 nvl(decode(exp.exception_type,
224 19, msc_get_name.project(
225 md2.project_id,
226 md2.organization_id,
227 md2.plan_id,
228 md2.sr_instance_id), null), 'N/A'),
229 nvl(decode(exp.exception_type,
230 17, msc_get_name.task(
231 exp.number2,
232 exp.number1,
233 exp.organization_id,
234 exp.plan_id,
235 exp.sr_instance_id),
236 18, msc_get_name.task(
237 exp.number2,
238 exp.number1,
239 exp.organization_id,
240 exp.plan_id,
241 exp.sr_instance_id),
242 msc_get_name.task(
243 sup.task_id,
244 sup.project_id,
245 sup.organization_id,
246 sup.plan_id,
247 sup.sr_instance_id)), 'N/A'),
248 nvl(decode(exp.exception_type,
249 19, msc_get_name.task(
250 md2.task_id,
251 md2.project_id,
252 md2.organization_id,
253 md2.plan_id,
254 md2.sr_instance_id), null), 'N/A'),
255 decode(exp.exception_type, 17, char1, 18, char1,
256 sup.planning_group),
257 DECODE(exp.EXCEPTION_TYPE, 2,NULL, 3, NULL, 6, NULL, 7, NULL,
258 10, sup.new_schedule_date, 18, NULL, 20, NULL,
259 27, md.using_assembly_demand_date,28, NULL,37, NULL,
260 24, md.using_assembly_demand_date,
261 25, md.using_assembly_demand_date,
262 26, md.using_assembly_demand_date,
263 49,msc_exp_wf.substitute_supply_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
264 exp.DATE1),
265 DECODE(EXP.EXCEPTION_TYPE,1,EXP.DATE1, 2, EXP.DATE1, 3, EXP.DATE1,
266 6, EXP.DATE1, 7,EXP.DATE1, 17, EXP.DATE1, 18, EXP.DATE1,
267 20, EXP.DATE1, 28, EXP.DATE1, 37, EXP.DATE1,
268 15, md2.using_assembly_demand_date,16, md2.using_assembly_demand_date,49,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
269 NULL),
270 DECODE(EXP.EXCEPTION_TYPE,15,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),16,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),exp.date2),
271 DECODE(exp.EXCEPTION_TYPE, 9, sup.SCHEDULE_COMPRESS_DAYS,NULL),
272 decode(exp.exception_type,
273 37,nvl(exp.number4,0)-nvl(exp.quantity,0),
274 28,MSC_EXP_WF.SupplierCapacity(p_plan_id,exp.exception_detail_id),
275 49,msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,
276 exp.supplier_id),
277 exp.quantity),
278 decode(exp.exception_type, 12, sup.lot_number),
279 decode(exp.exception_type, 1, NULL, 2, NULL, 3, NULL,
280 9, NVL(sup.order_number,exp.number1),12, NULL,
281 13, md.order_number,
282 14, NVL(md.order_number,msc_get_name.designator(
283 md.schedule_designator_id)),
284 16, nvl(sup.order_number,sup.transaction_id),
285 17, NULL, 18, NULL , 20, NULL,
286 24, md.order_number, 25, md.order_number,
287 26, msc_get_name.designator(md.schedule_designator_id),
288 27, msc_get_name.designator(md.schedule_designator_id),
289 28, NULL, 37, NULL, 70,md.order_number, sup.order_number),
290 sup_ml.meaning,
291 msc_get_name.item_name(
292 decode(exp.exception_type,
293 15, md2.inventory_item_id, 16, md2.inventory_item_id,
294 19, md2.inventory_item_id, 24, md.inventory_item_id,
295 25, md.inventory_item_id, 26, md.inventory_item_id,
296 27, md.inventory_item_id, 49, exp.number1, null),
297 null,null,null),
298 msc_get_name.item_desc(
299 decode(exp.exception_type,
300 15, md2.inventory_item_id, 16, md2.inventory_item_id,
301 19, md2.inventory_item_id, 24, md.inventory_item_id,
302 25, md.inventory_item_id, 26, md.inventory_item_id,
303 27, md.inventory_item_id, 49, exp.number1, null),
304 exp.organization_id,p_plan_id,exp.sr_instance_id),
305 decode(exp.exception_type,
306 15,md2.order_number, 19,md2.order_number,
307 24,md.order_number,25,md.order_number,
308 26,msc_get_name.designator(md.schedule_designator_id),
309 27,msc_get_name.designator(md.schedule_designator_id),
310 49,msc_get_name.demand_order_number(exp.plan_id,
311 exp.sr_instance_id, exp.supplier_id),
312 NULL) ,
313 null,
314 decode(exp.exception_type, 49,
315 msc_get_name.org_code(exp.number2, exp.sr_instance_id),
316 15,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),
317 16,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),null),
318 decode(exp.exception_type, 28, exp.quantity, 37, exp.quantity,
319 49, exp.quantity,
320 15, md.using_requirement_quantity,
321 16, md.using_requirement_quantity,
322 17, md.using_requirement_quantity,
323 18, md.using_requirement_quantity,
324 2, md.using_requirement_quantity,
325 3, msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,md2.demand_id),null),
326 decode(exp.exception_type, 12, null, 13, null, 14, null,
327 17, null, 18, null, 24, null, 25, null, 26, null,
328 27, null, 28, null, 37, exp.number2, exp.number1),
329 decode(exp.exception_type, 49, msc_exp_wf.demand_order_type(exp.plan_id,exp.sr_instance_id,exp.supplier_id),sup.order_type),
330 decode(exp.exception_type, 37, exp.number4,null),
331 exp.number1,
332 exp.number2
333 FROM msc_supplies sup,
334 msc_full_pegging mfp,
335 msc_demands md2,
336 msc_demands md,
337 msc_exception_details exp,
338 msc_system_items mi,
339 msc_trading_partners mtp,
340 mfg_lookups ml,
341 mfg_lookups sup_ml
342 WHERE exp.exception_type in
343 (1,2,3,6,7,8,9,10,12,13,14,15,16,17,18,19,20,24,25,26,27,28,37,49,70)
344 AND exp.plan_id = p_plan_id
345 AND exp.exception_detail_id in (SELECT number1
346 FROM msc_form_query
347 WHERE query_id = p_query_id)
348 AND sup.plan_id (+) = exp.plan_id
349 AND sup.transaction_id (+) = exp.number1
350 and sup.sr_instance_id(+) = exp.sr_instance_id
351 AND mfp.plan_id (+) = exp.plan_id
352 AND mfp.pegging_id (+) = exp.number2
353 and mfp.sr_instance_id(+) = exp.sr_instance_id
354 AND md2.plan_id (+) = mfp.plan_id
355 AND md2.demand_id (+) = mfp.demand_id
356 and md2.sr_instance_id(+) = mfp.sr_instance_id
357 AND md.plan_id (+) = exp.plan_id
358 AND md.demand_id (+) = exp.number1
359 and md.sr_instance_id(+) = exp.sr_instance_id
360 AND mi.inventory_item_id = exp.inventory_item_id
361 AND mi.organization_id = exp.organization_id
362 AND mi.plan_id = exp.plan_id
363 AND mi.sr_instance_id = exp.sr_instance_id
364 AND mtp.sr_tp_id = exp.organization_id
365 AND mtp.sr_instance_id = exp.sr_instance_id
366 AND mtp.partner_type = 3
367 and ml.lookup_type = 'MRP_EXCEPTION_CODE_TYPE'
368 and ml.lookup_code = exp.exception_type
369 and sup_ml.lookup_type(+) = 'MRP_ORDER_TYPE'
370 and sup_ml.lookup_code(+) = sup.order_type;
371
372
373 CURSOR EXCEPTION_DETAILS_C3 (p_plan_id in NUMBER) IS
374 SELECT exp.exception_detail_id,
375 exp.organization_id,
376 exp.sr_instance_id,
377 exp.inventory_item_id,
378 exp.exception_type,
379 mtp.organization_code,
380 mi.item_name,mi.description,
381 ml.meaning,
382 nvl(decode(exp.exception_type,
383 17, msc_get_name.project(
384 exp.number1,
385 exp.organization_id,
386 exp.plan_id,
387 exp.sr_instance_id),
388 18, msc_get_name.project(
389 exp.number1,
390 exp.organization_id,
391 exp.plan_id,
392 exp.sr_instance_id),
393 msc_get_name.project(
394 sup.project_id,
395 sup.organization_id,
396 sup.plan_id,
397 sup.sr_instance_id)), 'N/A'),
398 nvl(decode(exp.exception_type,
399 19, msc_get_name.project(
400 md2.project_id,
401 md2.organization_id,
402 md2.plan_id,
403 md2.sr_instance_id), null), 'N/A'),
404 nvl(decode(exp.exception_type,
405 17, msc_get_name.task(
406 exp.number2,
407 exp.number1,
408 exp.organization_id,
409 exp.plan_id,
410 exp.sr_instance_id),
411 18, msc_get_name.task(
412 exp.number2,
413 exp.number1,
414 exp.organization_id,
415 exp.plan_id,
416 exp.sr_instance_id),
417 msc_get_name.task(
418 sup.task_id,
419 sup.project_id,
420 sup.organization_id,
421 sup.plan_id,
422 sup.sr_instance_id)), 'N/A'),
423 nvl(decode(exp.exception_type,
424 19, msc_get_name.task(
425 md2.task_id,
426 md2.project_id,
427 md2.organization_id,
428 md2.plan_id,
429 md2.sr_instance_id), null), 'N/A'),
430 decode(exp.exception_type, 17, char1, 18, char1,
431 sup.planning_group),
432 DECODE(exp.EXCEPTION_TYPE, 2,NULL, 3, NULL, 6, NULL, 7, NULL,
433 10, sup.new_schedule_date, 18, NULL, 20, NULL,
434 27, md.using_assembly_demand_date,28, NULL,37, NULL,
435 24, md.using_assembly_demand_date,
436 25, md.using_assembly_demand_date,
437 26, md.using_assembly_demand_date,
438 49,msc_exp_wf.substitute_supply_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
439 exp.DATE1),
440 DECODE(EXP.EXCEPTION_TYPE,1,EXP.DATE1, 2, EXP.DATE1, 3, EXP.DATE1,
441 6, EXP.DATE1, 7,EXP.DATE1, 17, EXP.DATE1, 18, EXP.DATE1,
442 20, EXP.DATE1, 28, EXP.DATE1, 37, EXP.DATE1,
443 15, md2.using_assembly_demand_date,16, md2.using_assembly_demand_date,49,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
444 NULL),
445 DECODE(EXP.EXCEPTION_TYPE,15,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),16,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),exp.date2),
446 DECODE(exp.EXCEPTION_TYPE, 9, sup.SCHEDULE_COMPRESS_DAYS,NULL),
447 decode(exp.exception_type,
448 37,nvl(exp.number4,0)-nvl(exp.quantity,0),
449 28,MSC_EXP_WF.SupplierCapacity(p_plan_id,exp.exception_detail_id),
450 49,msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,
451 exp.supplier_id),
452 exp.quantity),
453 decode(exp.exception_type, 12, sup.lot_number),
454 decode(exp.exception_type, 1, NULL, 2, NULL, 3, NULL,
455 9, NVL(sup.order_number,exp.number1),12, NULL,
456 13, md.order_number,
457 14, NVL(md.order_number,msc_get_name.designator(
458 md.schedule_designator_id)),
459 16, nvl(sup.order_number,sup.transaction_id),
460 17, NULL, 18, NULL , 20, NULL,
461 24, md.order_number, 25, md.order_number,
462 26, msc_get_name.designator(md.schedule_designator_id),
463 27, msc_get_name.designator(md.schedule_designator_id),
464 28, NULL, 37, NULL, 70,md.order_number, sup.order_number),
465 sup_ml.meaning,
466 msc_get_name.item_name(
467 decode(exp.exception_type,
468 15, md2.inventory_item_id, 16, md2.inventory_item_id,
469 19, md2.inventory_item_id, 24, md.inventory_item_id,
470 25, md.inventory_item_id, 26, md.inventory_item_id,
471 27, md.inventory_item_id, 49, exp.number1,null),
472 null,null,null),
473 msc_get_name.item_desc(
474 decode(exp.exception_type,
475 15, md2.inventory_item_id, 16, md2.inventory_item_id,
476 19, md2.inventory_item_id, 24, md.inventory_item_id,
477 25, md.inventory_item_id, 26, md.inventory_item_id,
478 27, md.inventory_item_id, 49, exp.number1,null),
479 exp.organization_id,p_plan_id,exp.sr_instance_id),
480 decode(exp.exception_type,
481 15,md2.order_number, 19,md2.order_number,
482 24,md.order_number,25,md.order_number,
483 26,msc_get_name.designator(md.schedule_designator_id),
484 27,msc_get_name.designator(md.schedule_designator_id),
485 49,msc_get_name.demand_order_number(exp.plan_id,
486 exp.sr_instance_id, exp.supplier_id),
487 NULL) ,
488 null,
489 decode(exp.exception_type, 49,
490 msc_get_name.org_code(exp.number2, exp.sr_instance_id),
491 15,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),
492 16,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),null),
493 decode(exp.exception_type, 28, exp.quantity, 37, exp.quantity,
494 49, exp.quantity,
495 15, md.using_requirement_quantity,
496 16, md.using_requirement_quantity,
497 17, md.using_requirement_quantity,
498 18, md.using_requirement_quantity,
499 2, md.using_requirement_quantity,
500 3, msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,md2.demand_id),null),
501 decode(exp.exception_type, 12, null, 13, null, 14, null,
502 17, null, 18, null, 24, null, 25, null, 26, null,
503 27, null, 28, null, 37, exp.number2, exp.number1),
504 decode(exp.exception_type, 49, msc_exp_wf.demand_order_type(exp.plan_id,exp.sr_instance_id,exp.supplier_id),sup.order_type),
505 decode(exp.exception_type, 37, exp.number4,null),
506 exp.number1,
507 exp.number2
508 FROM msc_supplies sup,
509 msc_full_pegging mfp,
510 msc_demands md2,
511 msc_demands md,
512 msc_exception_details exp,
513 msc_system_items mi,
514 msc_trading_partners mtp,
515 mfg_lookups ml,
516 mfg_lookups sup_ml
517 WHERE exp.exception_type in
518 (1,2,3,6,7,8,9,10,12,13,14,15,16,17,18,19,20,24,25,26,27,28,37,49,70)
519 AND exp.plan_id = p_plan_id
520 AND sup.plan_id (+) = exp.plan_id
521 AND sup.transaction_id (+) = exp.number1
522 and sup.sr_instance_id(+) = exp.sr_instance_id
523 AND mfp.plan_id (+) = exp.plan_id
524 AND mfp.pegging_id (+) = exp.number2
525 and mfp.sr_instance_id(+) = exp.sr_instance_id
526 AND md2.plan_id (+) = mfp.plan_id
527 AND md2.demand_id (+) = mfp.demand_id
528 and md2.sr_instance_id(+) = mfp.sr_instance_id
529 AND md.plan_id (+) = exp.plan_id
530 AND md.demand_id (+) = exp.number1
531 and md.sr_instance_id(+) = exp.sr_instance_id
532 AND mi.inventory_item_id = exp.inventory_item_id
533 AND mi.organization_id = exp.organization_id
534 AND mi.plan_id = exp.plan_id
535 AND mi.sr_instance_id = exp.sr_instance_id
536 AND mtp.sr_tp_id = exp.organization_id
537 AND mtp.sr_instance_id = exp.sr_instance_id
538 AND mtp.partner_type = 3
539 and ml.lookup_type = 'MRP_EXCEPTION_CODE_TYPE'
540 and ml.lookup_code = exp.exception_type
541 and sup_ml.lookup_type(+) = 'MRP_ORDER_TYPE'
542 and sup_ml.lookup_code(+) = sup.order_type;
543
544 CURSOR SUPPLIER_C(p_exception_id in number,
545 p_plan_id in number) IS
546 SELECT vend.partner_id,
547 vend.partner_name
548 FROM msc_trading_partners vend,
549 msc_exception_details exp,
550 msc_supplies ms
551 WHERE vend.partner_id = nvl(exp.supplier_id, ms.supplier_id)
552 AND vend.partner_type = 1
553 AND ms.plan_id (+) = exp.plan_id
554 AND ms.transaction_id (+) = exp.number1
555 AND exp.exception_detail_id = p_exception_id
556 AND exp.plan_id = p_plan_id;
557
558 CURSOR SUPPLIER_SITE_C(p_exception_id in number,
559 p_plan_id in number) IS
560 SELECT vend.partner_id,
561 vend.partner_name,
562 site.partner_site_id,
563 site.tp_site_code
564 FROM msc_trading_partners vend,
565 msc_trading_partner_sites site,
566 msc_exception_details exp,
567 msc_supplies ms
568 WHERE site.partner_site_id = nvl(exp.supplier_site_id,ms.supplier_site_id)
569 AND vend.partner_id = nvl(exp.supplier_id, ms.supplier_id)
570 AND vend.partner_type = 1
571 AND ms.plan_id (+) = exp.plan_id
572 AND ms.transaction_id (+) = exp.number1
573 AND exp.exception_detail_id = p_exception_id
574 AND exp.plan_id = p_plan_id;
575
576 CURSOR CUSTOMER_C(p_exception_id in number,
577 p_plan_id in number) IS
578 SELECT vend.partner_id,
579 vend.partner_name
580 FROM msc_trading_partners vend,
581 msc_demands rec,
582 msc_exception_details exp
583 WHERE vend.sr_tp_id = rec.customer_id
584 AND vend.sr_instance_id = rec.sr_instance_id
585 AND vend.partner_type = 2
586 AND rec.demand_id = exp.number1
587 AND rec.plan_id = exp.plan_id
588 AND exp.exception_detail_id = p_exception_id
589 AND exp.plan_id = p_plan_id;
590
591 CURSOR DB_LINK_C(sr_instance_id in number) IS
592 SELECT decode(M2A_dblink,null,' ','@'||M2A_dblink),
593 decode(A2M_dblink,null,' ','@'||A2M_dblink)
594 FROM msc_apps_instances
595 WHERE instance_id = sr_instance_id;
596
597 l_cursor varchar2(30);
598
599 l_exception_id number;
600 l_organization_id number;
601 l_inventory_item_id number;
602 l_exception_type number;
603 l_organization_code varchar2(7);
604 l_item_segments varchar2(40);
605 l_item_description varchar2(240);
606 l_exception_type_text varchar2(1000);
607 l_project_number varchar2(1000);
608 l_to_project_number varchar2(1000);
609 l_task_number varchar2(1000);
610 l_to_task_number varchar2(1000);
611 l_planning_group varchar2(80);
612 l_due_date date;
613 l_from_date date;
614 l_to_date date;
615 l_days_compressed number;
616 l_quantity varchar2(40);
617 l_lot_number varchar2(80);
618 l_order_number varchar2(1000);
619 l_order_type_code number := to_number(NULL);
620 l_supply_type varchar2(80);
621 l_end_item_segments varchar2(40);
622 l_end_item_description varchar2(240);
623 l_end_order_number varchar2(1000);
624 l_department_line_code varchar2(10);
625 l_resource_code varchar2(30);
626 l_utilization_rate number;
627 l_vendor_id number := to_number(NULL);
628 l_vendor_name varchar2(80) := 'N/A';
629 l_vendor_site_id number := to_number(NULL);
630 l_vendor_site_code varchar2(15) := 'N/A';
631 l_customer_id number := to_number(NULL);
632 l_customer_name varchar2(80) := 'N/A';
633 l_workflow_process varchar2(40);
634 l_plan_type number;
635 l_org_selection number;
636 l_workbench_function varchar2(30);
637 l_counter number := 1;
638 l_planner_code varchar2(20);
639 l_sr_instance_id number;
640 l_db_link varchar2(40);
641 l_a2m_db_link varchar2(40);
642 l_transaction_id number;
643 l_prev_excep_id number := -1;
644 junk number;
645 sql_stmt varchar2(1000);
646 var_debug varchar2(2);
647 l_sr_vers number;
648 l_qty_related_values number;
649 l_sup_project_id number;--stores number1
650 l_sup_task_id number;--stores number2
651
652 BEGIN
653 -- Cancel notifications from previous plan run and force completion of
654 -- workflows.
655 msc_util.msc_debug('****** Start of Program MSC_EXP_WF******');
656 msc_util.msc_debug('PlanId: ' || to_char(p_plan_id));
657 msc_util.msc_debug('ExceptionId: ' || to_char(p_exception_id));
658
659 DeleteActivities(p_plan_id);
660 msc_util.msc_debug('deleted entries');
661
662 l_cursor := 'EXCEPTION_DETAILS_C';
663 msc_util.msc_debug('before the exception open');
664 if p_exception_id is null and p_query_id is null then
665
666 OPEN EXCEPTION_DETAILS_C3(p_plan_id);
667
668 elsif p_exception_id is not null then
669 OPEN EXCEPTION_DETAILS_C1(p_plan_id,p_exception_id);
670
671 elsif p_query_id is not null then
672 OPEN EXCEPTION_DETAILS_C2(p_plan_id,p_query_id);
673 end if;
674
675 LOOP
676 msc_util.msc_debug('before the exception fetch');
677 if p_exception_id is null and p_query_id is null then
678 FETCH EXCEPTION_DETAILS_C3 INTO
679 l_exception_id,
680 l_organization_id,
681 l_sr_instance_id,
682 l_inventory_item_id,
683 l_exception_type,
684 l_organization_code,
685 l_item_segments,
686 l_item_description,
687 l_exception_type_text,
688 l_project_number,
689 l_to_project_number,
690 l_task_number,
691 l_to_task_number,
692 l_planning_group,
693 l_due_date,
694 l_from_date,
695 l_to_date,
696 l_days_compressed,
697 l_quantity,
698 l_lot_number,
699 l_order_number,
700 l_supply_type,
701 l_end_item_segments,
702 l_end_item_description,
703 l_end_order_number,
704 l_department_line_code,
705 l_resource_code,
706 l_utilization_rate,
707 l_transaction_id,
708 l_order_type_code,
709 l_qty_related_values,
710 l_sup_project_id ,
711 l_sup_task_id ;
712 EXIT WHEN EXCEPTION_DETAILS_C3%NOTFOUND;
713
714 elsif p_exception_id is not null then
715
716 FETCH EXCEPTION_DETAILS_C1 INTO
717 l_exception_id,
718 l_organization_id,
719 l_sr_instance_id,
720 l_inventory_item_id,
721 l_exception_type,
722 l_organization_code,
723 l_item_segments,
724 l_item_description,
725 l_exception_type_text,
726 l_project_number,
727 l_to_project_number,
728 l_task_number,
729 l_to_task_number,
730 l_planning_group,
731 l_due_date,
732 l_from_date,
733 l_to_date,
734 l_days_compressed,
735 l_quantity,
736 l_lot_number,
737 l_order_number,
738 l_supply_type,
739 l_end_item_segments,
740 l_end_item_description,
741 l_end_order_number,
742 l_department_line_code,
743 l_resource_code,
744 l_utilization_rate,
745 l_transaction_id,
746 l_order_type_code,
747 l_qty_related_values,
748 l_sup_project_id ,
749 l_sup_task_id ;
750 EXIT WHEN EXCEPTION_DETAILS_C1%NOTFOUND;
751
752 elsif p_query_id is not null then
753
754 FETCH EXCEPTION_DETAILS_C2 INTO
755 l_exception_id,
756 l_organization_id,
757 l_sr_instance_id,
758 l_inventory_item_id,
759 l_exception_type,
760 l_organization_code,
761 l_item_segments,
762 l_item_description,
763 l_exception_type_text,
764 l_project_number,
765 l_to_project_number,
766 l_task_number,
767 l_to_task_number,
768 l_planning_group,
769 l_due_date,
770 l_from_date,
771 l_to_date,
772 l_days_compressed,
773 l_quantity,
774 l_lot_number,
775 l_order_number,
776 l_supply_type,
777 l_end_item_segments,
778 l_end_item_description,
779 l_end_order_number,
780 l_department_line_code,
781 l_resource_code,
782 l_utilization_rate,
783 l_transaction_id,
784 l_order_type_code,
785 l_qty_related_values,
786 l_sup_project_id ,
787 l_sup_task_id ;
788 EXIT WHEN EXCEPTION_DETAILS_C2%NOTFOUND;
789 end if;
790
791 -- Determine the database link
792 OPEN DB_LINK_C(l_sr_instance_id);
793 FETCH DB_LINK_C INTO l_db_link, l_a2m_db_link;
794 CLOSE DB_LINK_C;
795
796 l_vendor_id := NULL;
797 l_vendor_name := NULL;
798 l_vendor_site_id := NULL;
799 l_vendor_site_code := NULL;
800 l_customer_id := NULL;
801 l_customer_name := NULL;
802
803 if (l_exception_type in (1, 2, 3, 12, 14, 16, 20, 26, 27)) then
804 l_workflow_process := 'EXCEPTION_PROCESS1';
805 elsif (l_exception_type in (28, 37)) then
806 msc_util.msc_debug('Within the 37 logic');
807 l_workflow_process := 'EXCEPTION_PROCESS5';
808 l_cursor := 'SUPPLIER_SITE_C';
809 OPEN SUPPLIER_SITE_C(l_exception_id,p_plan_id);
810 LOOP
811 FETCH SUPPLIER_SITE_C INTO
812 l_vendor_id,
813 l_vendor_name,
814 l_vendor_site_id,
815 l_vendor_site_code;
816 EXIT WHEN SUPPLIER_SITE_C%NOTFOUND;
817 END LOOP;
818 CLOSE SUPPLIER_SITE_C;
819
820 elsif (l_exception_type in (6, 7, 8, 9, 10)) then
821 l_workflow_process := 'EXCEPTION_PROCESS2';
822
823 -- Purchase Order
824 if (l_order_type_code = 1) then
825 l_cursor := 'SUPPLIER_SITE_C';
826 OPEN SUPPLIER_SITE_C(l_exception_id,p_plan_id);
827 LOOP
828 FETCH SUPPLIER_SITE_C INTO
829 l_vendor_id,
830 l_vendor_name,
831 l_vendor_site_id,
832 l_vendor_site_code;
833 EXIT WHEN SUPPLIER_SITE_C%NOTFOUND;
834 END LOOP;
835 CLOSE SUPPLIER_SITE_C;
836
837 -- Purchase Requisition
838 elsif (l_order_type_code = 2) then
839 l_cursor := 'SUPPLIER_C';
840 OPEN SUPPLIER_C(l_exception_id,p_plan_id);
841 LOOP
842 FETCH SUPPLIER_C INTO
843 l_vendor_id,
844 l_vendor_name;
845 EXIT WHEN SUPPLIER_C%NOTFOUND;
846 END LOOP;
847 CLOSE SUPPLIER_C;
848 end if;
849
850 elsif (l_exception_type in (13, 15, 24, 25, 49, 70)) then
851 l_workflow_process := 'EXCEPTION_PROCESS3';
852 l_cursor := 'CUSTOMER_C';
853 OPEN CUSTOMER_C(l_exception_id,p_plan_id);
854 LOOP
855 FETCH CUSTOMER_C INTO
856 l_customer_id,
857 l_customer_name;
858 EXIT WHEN CUSTOMER_C%NOTFOUND;
859 END LOOP;
860 CLOSE CUSTOMER_C;
861
862 elsif (l_exception_type in (17, 18, 19)) then
863 l_workflow_process := 'EXCEPTION_PROCESS4';
864 end if;
865
866 l_workbench_function := 'MSCFNSCW-SCP';
867
868 l_cursor := 'StartWFProcess';
869
870 StartWFProcess( 'MSCEXPWF',
871 to_char(p_plan_id) || '-' ||to_char(l_exception_id),
872 l_exception_id,
873 l_organization_id,
874 l_sr_instance_id,
875 l_inventory_item_id,
876 l_exception_type,
877 l_organization_code,
878 l_item_segments,
879 l_item_description,
880 l_exception_type_text,
881 l_project_number,
882 l_to_project_number,
883 l_task_number,
884 l_to_task_number,
885 l_planning_group,
886 l_due_date,
887 l_from_date,
888 l_to_date,
889 l_days_compressed,
890 l_quantity,
891 l_lot_number,
892 l_order_number,
893 l_order_type_code,
894 l_supply_type,
895 l_end_item_segments,
896 l_end_item_description,
897 l_end_order_number,
898 l_department_line_code,
899 l_resource_code,
900 l_utilization_rate,
901 l_vendor_id,
902 l_vendor_name,
903 l_vendor_site_id,
904 l_vendor_site_code,
905 l_customer_id,
906 l_customer_name,
907 l_workbench_function,
908 l_workflow_process,
909 l_planner_code,
910 p_plan_id,
911 l_db_link,
912 l_a2m_db_link,
913 l_transaction_id,
914 l_qty_related_values,
915 l_sup_project_id,
916 l_sup_task_id );
917
918 msc_util.msc_debug('After the start process');
919
920 if l_counter >1000 then
921 commit;
922 msc_util.msc_debug('commit now');
923 l_counter :=1;
924 else
925 l_counter := l_counter+1;
926 end if;
927
928 END LOOP;
929 msc_util.msc_debug('After loop');
930
931 if p_exception_id is null and p_query_id is null then
932 CLOSE EXCEPTION_DETAILS_C3;
933 elsif p_exception_id is not null then
934 CLOSE EXCEPTION_DETAILS_C1;
935 elsif p_query_id is not null then
936 CLOSE EXCEPTION_DETAILS_C2;
937 end if;
938
939 msc_util.msc_debug('Completed:'|| to_char(l_counter -1));
940 retcode := 0;
941
942 l_cursor := 'End of launch_workflow';
943
944 EXCEPTION
945 WHEN NO_DATA_FOUND THEN
946 null;
947
948 WHEN OTHERS THEN
949 msc_util.msc_debug('Error in launch wkflow:'|| sqlerrm);
950 errbuf := 'Error in msc_exp_wf.launch_workflow function' ||
951 ' Cursor: ' || l_cursor || ' Exception ID: '
952 || l_exception_id ||
953 ' SQL error: ' || sqlerrm;
954 retcode := 2;
955
956 END launch_workflow;
957
958 -- PROCEDURE
959 -- StartWFProcess
960 --
961 -- DESCRIPTION
962 -- Initiate workflow for exception message handling
963 --
964 -- IN
965 -- itemtype - type of the current item
966 -- itemkey - key of the current item
967 -- actid - process activity instance id
968 -- funcmode - function execution mode. this is set by the engine
969 -- as either 'RUN', 'CANCEL', 'TIMEOUT'
970 -- OUT
971 -- result - Name of workflow process to run
972 --
973
974 PROCEDURE StartWFProcess ( item_type in varchar2 default null,
975 item_key in varchar2,
976 l_exception_id in number,
977 organization_id in number,
978 instance_id in number,
979 inventory_item_id in number,
980 exception_type in number,
981 organization_code in varchar2,
982 item_segments in varchar2,
983 item_description in varchar2,
984 exception_type_text in varchar2,
985 project_number in varchar2,
986 to_project_number in varchar2,
987 task_number in varchar2,
988 to_task_number in varchar2,
989 planning_group in varchar2,
990 due_date in date,
991 from_date in date,
992 p_to_date in date,
993 days_compressed in number,
994 quantity in varchar2,
995 lot_number in varchar2,
996 order_number in varchar2,
997 order_type_code in number,
998 supply_type in varchar2,
999 end_item_segments in varchar2,
1000 end_item_description in varchar2,
1001 end_order_number in varchar2,
1002 department_line_code in varchar2,
1003 resource_code in varchar2,
1004 utilization_rate in number,
1005 supplier_id in number,
1006 supplier_name in varchar2,
1007 supplier_site_id in number,
1008 supplier_site_code in varchar2,
1009 customer_id in number,
1010 customer_name in varchar2,
1011 workbench_function in varchar2,
1012 workflow_process in varchar2 default null,
1013 planner_code in varchar2,
1014 p_plan_id in number,
1015 db_link in varchar2,
1016 l_a2m_db_link in varchar2,
1017 transaction_id in number,
1018 qty_related_values in number,
1019 sup_project_id in number,
1020 sup_task_id in number) is
1021 compile_designator varchar(15) := null;
1022 pre_prsng_lead_time number := 0;
1023 prsng_lead_time number := 0;
1024 post_prsng_lead_time number := 0;
1025 l_url varchar2(200);
1026 lv_organization_id NUMBER;
1027 lv_inventory_item_id NUMBER;
1028 lv_exception_type NUMBER;
1029 lv_cap_req NUMBER := 0;-- required capacity
1030 lv_req_quantity NUMBER := 0;--required quantity
1031 lv_ava_quantity NUMBER := 0;--available quantity
1032 lv_ava_quantity_temp NUMBER := 0;--temp variable
1033 lv_pab NUMBER := 0;--Projected available balance
1034
1035 CURSOR LEADTIME_C IS
1036 select nvl(PREPROCESSING_LEAD_TIME,0),nvl(FIXED_LEAD_TIME,0),nvl(POSTPROCESSING_LEAD_TIME,0)
1037 from msc_system_items msi
1038 where msi.sr_instance_id = instance_id
1039 and msi.plan_id = p_plan_id
1040 and msi.organization_id = lv_organization_id
1041 and msi.inventory_item_id = lv_inventory_item_id;
1042
1043 CURSOR SO_C is
1044 select md.schedule_ship_date old_ship_date,
1045 md.schedule_arrival_date old_arrival_date,
1046 md.dmd_satisfied_date new_ship_date,
1047 md.planned_arrival_date new_arrival_date,
1048 md.request_date request_arrival_date,
1049 md.request_ship_date,
1050 md.promise_date promise_arrival_date,
1051 md.promise_ship_date,
1052 md.shipping_method_code new_ship_method,
1053 md.orig_shipping_method_code old_ship_method,
1054 md.orig_intransit_lead_time old_lead_time,
1055 md.intransit_lead_time new_lead_time,
1056 msc_get_name.customer_site(md.customer_site_id) customer_site,
1057 msc_get_name.org_code(md.original_org_id,
1058 md.original_inst_id) org_code,
1059 msc_get_name.org_code(md.organization_id,md.sr_instance_id) to_org,
1060 md.demand_id,
1061 md.latest_acceptable_date,
1062 msc_get_name.lookup_meaning('SYS_YES_NO',md.atp_override_flag) atp_override_flag
1063 from msc_demands md,
1064 msc_exception_details med
1065 where med.plan_id = md.plan_id
1066 and med.number1 = md.demand_id
1067 and med.sr_instance_id = md.sr_instance_id
1068 and med.plan_id = p_plan_id
1069 and med.exception_detail_id = l_exception_id;
1070
1071 so_rec so_c%ROWTYPE;
1072 BEGIN
1073
1074 lv_organization_id := organization_id;
1075 lv_inventory_item_id :=inventory_item_id;
1076 lv_exception_type := exception_type;
1077 msc_util.msc_debug('Inside the start process');
1078 -- Note that with MSC the unique key is plan_id || exception_id
1079 --wf_engine.threshold := -1;
1080 wf_engine.CreateProcess( itemtype => item_type,
1081 itemkey => item_key,
1082 process => workflow_process);
1083
1084 wf_engine.SetItemAttrNumber( itemtype => item_type,
1085 itemkey => item_key,
1086 aname => 'EXCEPTION_ID',
1087 avalue => l_exception_id);
1088
1089 if l_exception_id in (37, 28) then
1090 wf_engine.SetItemAttrText( itemtype => item_type,
1091 itemkey => item_key,
1092 aname => 'URL1',
1093 avalue => l_url);
1094 end if;
1095
1096 select compile_designator
1097 into compile_designator
1098 from msc_plans
1099 where plan_id = p_plan_id;
1100
1101
1102 if (lv_exception_type = 9) then
1103 OPEN LEADTIME_C ;
1104 FETCH LEADTIME_C into pre_prsng_lead_time,prsng_lead_time,post_prsng_lead_time;
1105 CLOSE LEADTIME_C;
1106
1107 end if;
1108
1109
1110 --included to find Required/available quantity/PAB.
1111
1112 if(lv_exception_type = 2 or lv_exception_type = 3 or lv_exception_type=20 ) then
1113
1114 --Available quantity
1115 select
1116 nvl(sum(new_order_quantity),0)
1117 into
1118 lv_ava_quantity
1119 from
1120 msc_supplies
1121 where
1122 organization_id=lv_organization_id
1123 and inventory_item_id=lv_inventory_item_id
1124 and plan_id=p_plan_id
1125 and sr_instance_id=instance_id
1126 and nvl(disposition_status_type,-99)<>2
1127 and new_order_quantity > 0
1128 and to_date(new_schedule_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
1129
1130
1131
1132 select
1133 nvl(sum(new_order_quantity*(-1)),0)
1134 into
1135 lv_ava_quantity_temp
1136 from
1137 msc_supplies
1138 where
1139 organization_id=lv_organization_id
1140 and inventory_item_id=lv_inventory_item_id
1141 and plan_id=p_plan_id
1142 and sr_instance_id=instance_id
1143 and nvl(disposition_status_type,-99)<>2
1144 and new_order_quantity < 0
1145 and to_date(new_schedule_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
1146
1147
1148 --Required quantity
1149 select
1150 nvl(sum(using_requirement_quantity),0)
1151 into
1152 lv_req_quantity
1153 from
1154 msc_demands
1155 where
1156 organization_id=lv_organization_id
1157 and inventory_item_id=lv_inventory_item_id
1158 and plan_id=p_plan_id
1159 and sr_instance_id=instance_id
1160 and to_date(using_assembly_demand_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
1161
1162 lv_req_quantity:=lv_req_quantity+lv_ava_quantity_temp;
1163
1164 lv_pab:=lv_ava_quantity-lv_req_quantity;
1165
1166
1167 end if;
1168
1169
1170 --included to find Required/available quantity/PAB for Projects
1171 if(lv_exception_type = 17 or lv_exception_type = 18) then
1172
1173 --Available quantity
1174 select
1175 nvl(sum(new_order_quantity),0)
1176 into
1177 lv_ava_quantity
1178 from
1179 msc_supplies
1180 where
1181 organization_id=lv_organization_id
1182 and inventory_item_id=lv_inventory_item_id
1183 and plan_id=p_plan_id
1184 and sr_instance_id=instance_id
1185 and project_id=sup_project_id
1186 and nvl(task_id,-99)=nvl(sup_task_id,-99)
1187 and nvl(disposition_status_type,-99)<>2
1188 and new_order_quantity > 0
1189 and to_date(new_schedule_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
1190
1191
1192 select
1193 nvl(sum(new_order_quantity*(-1)),0)
1194 into
1195 lv_ava_quantity_temp
1196 from
1197 msc_supplies
1198 where
1199 organization_id=lv_organization_id
1200 and inventory_item_id=lv_inventory_item_id
1201 and plan_id=p_plan_id
1202 and sr_instance_id=instance_id
1203 and project_id=sup_project_id
1204 and nvl(task_id,-99)=nvl(sup_task_id,-99)
1205 and nvl(disposition_status_type,-99)<>2
1206 and new_order_quantity < 0
1207 and to_date(new_schedule_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
1208
1209 --Required quantity
1210 select
1211 nvl(sum(using_requirement_quantity),0)
1212 into
1213 lv_req_quantity
1214 from
1215 msc_demands
1216 where
1217 organization_id=lv_organization_id
1218 and inventory_item_id=lv_inventory_item_id
1219 and plan_id=p_plan_id
1220 and sr_instance_id=instance_id
1221 and project_id=sup_project_id
1222 and nvl(task_id,-99)=nvl(sup_task_id,-99)
1223 and to_date(using_assembly_demand_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
1224
1225 lv_req_quantity:=lv_req_quantity+lv_ava_quantity_temp;
1226
1227
1228 end if;
1229
1230
1231 wf_engine.SetItemAttrText( itemtype => item_type,
1232 itemkey => item_key,
1233 aname => 'PLAN_NAME',
1234 avalue => compile_designator);
1235
1236 wf_engine.SetItemAttrText( itemtype => item_type,
1237 itemkey => item_key,
1238 aname => 'PRE_PRSNG_LEAD_TIME',
1239 avalue => pre_prsng_lead_time);
1240
1241 wf_engine.SetItemAttrText( itemtype => item_type,
1242 itemkey => item_key,
1243 aname => 'PRSNG_LEAD_TIME',
1244 avalue => prsng_lead_time);
1245
1246 wf_engine.SetItemAttrText( itemtype => item_type,
1247 itemkey => item_key,
1248 aname => 'POST_PRSNG_LEAD_TIME',
1249 avalue => post_prsng_lead_time);
1250
1251 wf_engine.SetItemAttrNumber( itemtype => item_type,
1252 itemkey => item_key,
1253 aname => 'ORGANIZATION_ID',
1254 avalue => organization_id);
1255 wf_engine.SetItemAttrNumber( itemtype => item_type,
1256 itemkey => item_key,
1257 aname => 'INSTANCE_ID',
1258 avalue => instance_id);
1259 wf_engine.SetItemAttrNumber( itemtype => item_type,
1260 itemkey => item_key,
1261 aname => 'INVENTORY_ITEM_ID',
1262 avalue => inventory_item_id);
1263 wf_engine.SetItemAttrNumber( itemtype => item_type,
1264 itemkey => item_key,
1265 aname => 'EXCEPTION_TYPE_ID',
1266 avalue => exception_type);
1267 wf_engine.SetItemAttrNumber( itemtype => item_type,
1268 itemkey => item_key,
1269 aname => 'TRANSACTION_ID',
1270 avalue => transaction_id);
1271 wf_engine.SetItemAttrText( itemtype => item_type,
1272 itemkey => item_key,
1273 aname => 'ORGANIZATION_CODE',
1274 avalue => organization_code);
1275 wf_engine.SetItemAttrText( itemtype => item_type,
1276 itemkey => item_key,
1277 aname => 'ITEM_DISPLAY_NAME',
1278 avalue => item_segments);
1279 wf_engine.SetItemAttrText( itemtype => item_type,
1280 itemkey => item_key,
1281 aname => 'ITEM_DESCRIPTION',
1282 avalue => item_description);
1283 wf_engine.SetItemAttrText( itemtype => item_type,
1284 itemkey => item_key,
1285 aname => 'EXCEPTION_DESCRIPTION',
1286 avalue => exception_type_text);
1287 wf_engine.SetItemAttrText( itemtype => item_type,
1288 itemkey => item_key,
1289 aname => 'PROJECT_NUMBER',
1290 avalue => project_number);
1291 wf_engine.SetItemAttrText( itemtype => item_type,
1292 itemkey => item_key,
1293 aname => 'TO_PROJECT_NUMBER',
1294 avalue => to_project_number);
1295 wf_engine.SetItemAttrText( itemtype => item_type,
1296 itemkey => item_key,
1297 aname => 'TASK_NUMBER',
1298 avalue => task_number);
1299 wf_engine.SetItemAttrText( itemtype => item_type,
1300 itemkey => item_key,
1301 aname => 'TO_TASK_NUMBER',
1302 avalue => to_task_number);
1303 wf_engine.SetItemAttrText( itemtype => item_type,
1304 itemkey => item_key,
1305 aname => 'PLANNING_GROUP',
1306 avalue => planning_group);
1307 wf_engine.SetItemAttrNumber( itemtype => item_type,
1308 itemkey => item_key,
1309 aname => 'QTY_RELATED_VALUES',
1310 avalue => qty_related_values);
1311
1312 wf_engine.SetItemAttrDate( itemtype => item_type,
1313 itemkey => item_key,
1314 aname => 'DUE_DATE',
1315 avalue => due_date);
1316
1317 wf_engine.SetItemAttrDate( itemtype => item_type,
1318 itemkey => item_key,
1319 aname => 'FROM_DATE',
1320 avalue => from_date);
1321
1322 wf_engine.SetItemAttrDate( itemtype => item_type,
1323 itemkey => item_key,
1324 aname => 'TO_DATE',
1325 avalue => p_to_date);
1326
1327 wf_engine.SetItemAttrNumber( itemtype => item_type,
1328 itemkey => item_key,
1329 aname => 'DAYS_COMPRESSED',
1330 avalue => days_compressed);
1331 wf_engine.SetItemAttrText( itemtype => item_type,
1332 itemkey => item_key,
1333 aname => 'QUANTITY',
1334 avalue => quantity);
1335
1336 wf_engine.SetItemAttrText( itemtype => item_type,
1337 itemkey => item_key,
1338 aname => 'LOT_NUMBER',
1339 avalue => lot_number);
1340
1341 wf_engine.SetItemAttrText( itemtype => item_type,
1342 itemkey => item_key,
1343 aname => 'ORDER_NUMBER',
1344 avalue => order_number);
1345
1346 wf_engine.SetItemAttrNumber( itemtype => item_type,
1347 itemkey => item_key,
1348 aname => 'ORDER_TYPE_CODE',
1349 avalue => order_type_code);
1350
1351 wf_engine.SetItemAttrText( itemtype => item_type,
1352 itemkey => item_key,
1353 aname => 'SUPPLY_TYPE',
1354 avalue => supply_type);
1355
1356 wf_engine.SetItemAttrText( itemtype => item_type,
1357 itemkey => item_key,
1358 aname => 'END_ITEM_DISPLAY_NAME',
1359 avalue => end_item_segments);
1360
1361 wf_engine.SetItemAttrText( itemtype => item_type,
1362 itemkey => item_key,
1363 aname => 'END_ITEM_DESCRIPTION',
1364 avalue => end_item_description);
1365
1366 wf_engine.SetItemAttrText( itemtype => item_type,
1367 itemkey => item_key,
1368 aname => 'END_ORDER_NUMBER',
1369 avalue => end_order_number);
1370
1371 wf_engine.SetItemAttrText( itemtype => item_type,
1372 itemkey => item_key,
1373 aname => 'DEPARTMENT_LINE_CODE',
1374 avalue => department_line_code);
1375
1376 wf_engine.SetItemAttrText( itemtype => item_type,
1377 itemkey => item_key,
1378 aname => 'RESOURCE_CODE',
1379 avalue => resource_code);
1380
1381 wf_engine.SetItemAttrNumber( itemtype => item_type,
1382 itemkey => item_key,
1383 aname => 'UTILIZATION_RATE',
1384 avalue => utilization_rate);
1385
1386 --Calculation of Required Capacity
1387
1388 if(quantity >0) then
1389 lv_cap_req:=(quantity*utilization_rate)/100;
1390 else
1391 lv_cap_req:=utilization_rate/100;
1392 end if;
1393
1394
1395 wf_engine.SetItemAttrNumber( itemtype => item_type,
1396 itemkey => item_key,
1397 aname => 'CAPACITY_REQUIREMENT',
1398 avalue => lv_cap_req);
1399
1400
1401 wf_engine.SetItemAttrNumber( itemtype => item_type,
1402 itemkey => item_key,
1403 aname => 'REQUIRED_QUANTITY',
1404 avalue => lv_req_quantity);
1405
1406
1407 wf_engine.SetItemAttrNumber( itemtype => item_type,
1408 itemkey => item_key,
1409 aname => 'AVAILABLE_QUANTITY',
1410 avalue => lv_ava_quantity);
1411
1412 wf_engine.SetItemAttrNumber( itemtype => item_type,
1413 itemkey => item_key,
1414 aname => 'PROJECTED_AVAILABLE_BALANCE',
1415 avalue => lv_pab);
1416
1417
1418 wf_engine.SetItemAttrNumber( itemtype => item_type,
1419 itemkey => item_key,
1420 aname => 'SUPPLIER_ID',
1421 avalue => supplier_id);
1422
1423 wf_engine.SetItemAttrText( itemtype => item_type,
1424 itemkey => item_key,
1425 aname => 'SUPPLIER_NAME',
1426 avalue => supplier_name);
1427
1428 wf_engine.SetItemAttrNumber( itemtype => item_type,
1429 itemkey => item_key,
1430 aname => 'SUPPLIER_SITE_ID',
1431 avalue => supplier_site_id);
1432
1433 wf_engine.SetItemAttrText( itemtype => item_type,
1434 itemkey => item_key,
1435 aname => 'SUPPLIER_SITE_CODE',
1436 avalue => supplier_site_code);
1437
1438 wf_engine.SetItemAttrNumber( itemtype => item_type,
1439 itemkey => item_key,
1440 aname => 'CUSTOMER_ID',
1441 avalue => customer_id);
1442
1443 wf_engine.SetItemAttrText( itemtype => item_type,
1444 itemkey => item_key,
1445 aname => 'CUSTOMER_NAME',
1446 avalue => customer_name);
1447
1448 wf_engine.SetItemAttrText( itemtype => item_type,
1449 itemkey => item_key,
1450 aname => 'PLANNER_WORKBENCH',
1451 avalue => (workbench_function||
1452 ': instance_id=' || instance_id ||
1453 ' org_id=' ||
1454 to_char(organization_id)) );
1455
1456 wf_engine.SetItemAttrNumber( itemtype => item_type,
1457 itemkey => item_key,
1458 aname => 'PLAN_ID',
1459 avalue => p_plan_id);
1460
1461 wf_engine.SetItemAttrText( itemtype => item_type,
1462 itemkey => item_key,
1463 aname => 'DB_LINK',
1464 avalue => db_link);
1465
1466 wf_engine.SetItemAttrText( itemtype => item_type,
1467 itemkey => item_key,
1468 aname => 'APPS_PS_DBLINK',
1469 avalue => l_a2m_db_link);
1470
1471 if (lv_exception_type = 70) then
1472 OPEN SO_C;
1473 FETCH SO_C INTO so_rec;
1474 CLOSE SO_C;
1475
1476 wf_engine.SetItemAttrNumber( itemtype => item_type,
1477 itemkey => item_key,
1478 aname => 'TRANSACTION_ID',
1479 avalue => so_rec.demand_id);
1480 wf_engine.SetItemAttrNumber( itemtype => item_type,
1481 itemkey => item_key,
1482 aname => 'PRE_PRSNG_LEAD_TIME',
1483 avalue => so_rec.old_lead_time);
1484
1485 wf_engine.SetItemAttrNumber( itemtype => item_type,
1486 itemkey => item_key,
1487 aname => 'POST_PRSNG_LEAD_TIME',
1488 avalue => so_rec.new_lead_time);
1489
1490 wf_engine.SetItemAttrText( itemtype => item_type,
1491 itemkey => item_key,
1492 aname => 'ORGANIZATION_CODE',
1493 avalue => so_rec.org_code);
1494 wf_engine.SetItemAttrText( itemtype => item_type,
1495 itemkey => item_key,
1496 aname => 'DEPARTMENT_LINE_CODE',
1497 avalue => so_rec.to_org);
1498 wf_engine.SetItemAttrDate( itemtype => item_type,
1499 itemkey => item_key,
1500 aname => 'FROM_DATE',
1501 avalue => so_rec.old_ship_date);
1502
1503 wf_engine.SetItemAttrDate( itemtype => item_type,
1504 itemkey => item_key,
1505 aname => 'TO_DATE',
1506 avalue => so_rec.new_ship_date);
1507
1508 wf_engine.SetItemAttrDate( itemtype => item_type,
1509 itemkey => item_key,
1510 aname => 'DATE1',
1511 avalue => so_rec.old_arrival_date);
1512
1513 wf_engine.SetItemAttrDate( itemtype => item_type,
1514 itemkey => item_key,
1515 aname => 'DATE2',
1516 avalue => so_rec.new_arrival_date);
1517
1518
1519 wf_engine.SetItemAttrDate( itemtype => item_type,
1520 itemkey => item_key,
1521 aname => 'DATE3',
1522 avalue => so_rec.promise_arrival_date);
1523
1524 wf_engine.SetItemAttrDate( itemtype => item_type,
1525 itemkey => item_key,
1526 aname => 'DATE4',
1527 avalue => so_rec.promise_ship_date);
1528
1529 wf_engine.SetItemAttrDate( itemtype => item_type,
1530 itemkey => item_key,
1531 aname => 'DATE5',
1532 avalue => so_rec.request_arrival_date);
1533
1534 wf_engine.SetItemAttrDate( itemtype => item_type,
1535 itemkey => item_key,
1536 aname => 'DATE6',
1537 avalue => so_rec.request_ship_date);
1538
1539
1540 wf_engine.SetItemAttrDate( itemtype => item_type,
1541 itemkey => item_key,
1542 aname => 'DUE_DATE',
1543 avalue => so_rec.latest_acceptable_date);
1544 wf_engine.SetItemAttrText( itemtype => item_type,
1545 itemkey => item_key,
1546 aname => 'RESOURCE_CODE',
1547 avalue => so_rec.customer_site);
1548
1549 wf_engine.SetItemAttrText( itemtype => item_type,
1550 itemkey => item_key,
1551 aname => 'SUPPLIER_NAME',
1552 avalue => so_rec.old_ship_method);
1553
1554 wf_engine.SetItemAttrText( itemtype => item_type,
1555 itemkey => item_key,
1556 aname => 'SUPPLIER_SITE_CODE',
1557 avalue => so_rec.new_ship_method);
1558
1559 wf_engine.SetItemAttrText( itemtype => item_type,
1560 itemkey => item_key,
1561 aname => 'PLANNING_GROUP',
1562 avalue => so_rec.atp_override_flag);
1563
1564 end if; -- end of if (lv_exception_type = 70)
1565 msc_util.msc_debug('Before start process:'|| item_type || ':' || item_key);
1566
1567 wf_engine.StartProcess( itemtype => item_type,
1568 itemkey => item_key);
1569
1570
1571 EXCEPTION
1572
1573 when others then
1574 wf_core.context('MSC_EXP_WF', 'StartWFProcess', item_key, to_char(p_plan_id),
1575 organization_code, item_segments, to_char(exception_type));
1576 raise;
1577
1578 END StartWFProcess;
1579
1580
1581
1582 PROCEDURE SelectPlanner( itemtype in varchar2,
1583 itemkey in varchar2,
1584 actid in number,
1585 funcmode in varchar2,
1586 resultout out NOCOPY varchar2 ) is
1587
1588 l_sr_status varchar2(10) :=
1589 wf_engine.GetItemAttrText( itemtype => itemtype,
1590 itemkey => itemkey,
1591 aname => 'SR_RESULT');
1592
1593 l_organization_id number :=
1594 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1595 itemkey => itemkey,
1596 aname => 'ORGANIZATION_ID');
1597
1598 l_inventory_item_id number :=
1599 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1600 itemkey => itemkey,
1601 aname => 'INVENTORY_ITEM_ID');
1602
1603 l_plan_id number :=
1604 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1605 itemkey => itemkey,
1606 aname => 'PLAN_ID');
1607
1608 l_instance_id number :=
1609 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1610 itemkey => itemkey,
1611 aname => 'INSTANCE_ID');
1612
1613 l_exception_type number :=
1614 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1615 itemkey => itemkey,
1616 aname => 'EXCEPTION_TYPE_ID');
1617
1618 l_order_type number :=
1619 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1620 itemkey => itemkey,
1621 aname => 'ORDER_TYPE_CODE');
1622
1623 l_stage number :=
1624 wf_engine.GetActivityAttrNumber( itemtype => itemtype,
1625 itemkey => itemkey,
1626 actid => actid,
1627 aname => 'STAGE');
1628
1629 sql_stmt varchar2(1000);
1630 l_planner varchar2(100);
1631 l_msg varchar2(100);
1632 BEGIN
1633
1634 msc_util.msc_debug('In the planner select logic');
1635
1636 if (funcmode = 'RUN') then
1637
1638 if (l_stage = 1) then
1639 OPEN PLANNER_C(l_plan_id,l_inventory_item_id,l_organization_id,
1640 l_instance_id);
1641 FETCH PLANNER_C INTO l_planner;
1642 CLOSE PLANNER_C;
1643 -- l_planner := 'MFG';
1644 if l_planner is null then
1645 l_planner := FND_GLOBAL.USER_NAME;
1646 FND_FILE.PUT_LINE(FND_FILE.LOG,'no planner defined for this item, sent notification to '||l_planner);
1647 end if;
1648
1649 wf_engine.SetItemAttrText( itemtype => itemtype,
1650 itemkey => itemkey,
1651 aname => 'PLANNER',
1652 avalue => l_planner);
1653 else
1654 l_planner :=
1655 wf_engine.GetItemAttrText( itemtype => itemtype,
1656 itemkey => itemkey,
1657 aname => 'PLANNER');
1658 end if;
1659 msc_util.msc_debug('planner:'||l_planner);
1660
1661 if l_planner is not null THEN
1662 l_msg := GetPlannerMsgName(l_exception_type,
1663 l_order_type,
1664 l_stage,
1665 l_sr_status);
1666 wf_engine.SetItemAttrText( itemtype => itemtype,
1667 itemkey => itemkey,
1668 aname => 'MESSAGE_NAME',
1669 avalue => l_msg);
1670 msc_util.msc_debug('msg name:'||l_msg);
1671
1672 resultout := 'COMPLETE:FOUND';
1673 else
1674 resultout := 'COMPLETE:NOT_FOUND';
1675 end if;
1676 return;
1677 end if;
1678
1679 if (funcmode = 'CANCEL') then
1680 resultout := 'COMPLETE:';
1681 return;
1682 end if;
1683
1684 if (funcmode = 'TIMEOUT') then
1685 resultout := 'COMPLETE:';
1686 return;
1687 end if;
1688
1689 EXCEPTION
1690
1691 when others then
1692 wf_core.context('MSC_EXP_WF', 'SelectPlanner', itemtype, itemkey, actid, funcmode);
1693 raise;
1694
1695 END SelectPlanner;
1696
1697
1698 FUNCTION GetPlannerMsgName(p_exception_type in number,
1699 p_order_type in number,
1700 p_stage in number,
1701 p_result in varchar2) RETURN varchar2 IS
1702 BEGIN
1703
1704 if (p_stage = 1) then -- first msg to planner
1705 if (p_exception_type = 1) then
1706 return 'MSG_1';
1707 elsif (p_exception_type = 2) then
1708 return 'MSG_2';
1709 elsif (p_exception_type = 3) then
1710 return 'MSG_3';
1711 elsif (p_exception_type = 20) then
1712 return 'MSG_20';
1713 elsif (p_exception_type = 6) then
1714 if (p_order_type = 1) then
1715 return 'MSG_6_PO';
1716 elsif (p_order_type = 2) then
1717 return 'MSG_6_REQ';
1718 elsif (p_order_type in (3, 5, 7, 18)) then -- work order
1719 return 'MSG_6_WORK';
1720 end if;
1721 elsif (p_exception_type = 7) then
1722 if (p_order_type = 1) then
1723 return 'MSG_7_PO';
1724 elsif (p_order_type = 2) then
1725 return 'MSG_7_REQ';
1726 elsif (p_order_type in (3, 5, 7, 18)) then -- work order
1727 return 'MSG_7_WORK';
1728 end if;
1729 elsif (p_exception_type = 8 ) then
1730 if (p_order_type = 1) then
1731 return 'MSG_8_PO';
1732 elsif (p_order_type = 2) then
1733 return 'MSG_8_REQ';
1734 elsif (p_order_type in (3, 5, 7, 18)) then -- work order
1735 return 'MSG_8_WORK';
1736 end if;
1737 elsif (p_exception_type = 10 ) then
1738 if (p_order_type IN (1,2,5)) then --buy planned order,purchase requisition,PO
1739 return 'MSG_10';
1740 else -- others
1741 return 'MSG_10_OTHER';
1742 end if;
1743 elsif (p_exception_type = 9) then
1744 if (p_order_type = 1) then
1745 return 'MSG_9_PO';
1746 elsif (p_order_type = 2) then
1747 return 'MSG_9_REQ';
1748 elsif (p_order_type in (3, 5, 7, 18)) then -- work order
1749 return 'MSG_9_WORK';
1750 end if;
1751 elsif (p_exception_type = 12) then
1752 return 'MSG_12';
1753 elsif (p_exception_type = 13) then
1754 return 'MSG_13';
1755 elsif (p_exception_type = 14) then
1756 return 'MSG_14';
1757 elsif (p_exception_type in (15,24,25)) then
1758 return 'MSG_15';
1759 elsif (p_exception_type in (16,26,27)) then
1760 return 'MSG_16';
1761 elsif (p_exception_type = 17) then
1762 return 'MSG_17';
1763 elsif (p_exception_type = 18) then
1764 return 'MSG_18';
1765 elsif (p_exception_type = 19) then
1766 return 'MSG_19';
1767 elsif (p_exception_type = 28) then
1768 return 'MSG_28';
1769 elsif (p_exception_type = 37) then
1770 return 'MSG_37';
1771 elsif (p_exception_type = 49) then
1772 if(p_order_type=30) then
1773 return 'MSG_49_SO';
1774 elsif (p_order_type=29) then
1775 return 'MSG_49_FORECAST';
1776 end if;
1777 elsif (p_exception_type = 70) then
1778 if(p_order_type=-30) then -- from release sales order
1779 return 'MSG_RL_SO';
1780 else
1781 return 'MSG_70';
1782 end if;
1783 end if;
1784 else --stage =2 2nd mesg to planner
1785 if (p_exception_type in (37, 28)) then
1786 if (p_result = 'SUCCEED') then
1787 return 'MSG_37_COMP';
1788 else
1789 return 'MSG_37_DECLINE';
1790 end if;
1791 elsif p_exception_type = 6 then -- 6-7
1792 if (p_order_type = 1) then
1793 return 'MSG_6_PO_COMP';
1794 elsif (p_order_type = 2) then
1795 return 'MSG_6_REQ_COMP';
1796 elsif (p_order_type in (3, 5, 7, 18)) then -- work order
1797 return 'MSG_6_WORK_COMP';
1798 end if;
1799 elsif p_exception_type = 7 then -- 6-7
1800 if (p_order_type = 1) then
1801 return 'MSG_7_PO_COMP';
1802 elsif (p_order_type = 2) then
1803 return 'MSG_7_REQ_COMP';
1804 elsif (p_order_type in (3, 5, 7, 18)) then -- work order
1805 return 'MSG_7_WORK_COMP';
1806 end if;
1807 elsif p_exception_type = 8 then
1808 if (p_order_type = 1) then
1809 return 'MSG_8_PO_COMP';
1810 elsif (p_order_type = 2) then
1811 return 'MSG_8_REQ_COMP';
1812 elsif (p_order_type in (3, 5, 7, 18)) then -- work order
1813 return 'MSG_8_WORK_COMP';
1814 end if;
1815 elsif p_exception_type = 10 then
1816 if (p_order_type IN (1,2,5)) then --buy planned order,purchase requisition,PO
1817 return 'MSG_10_COMP';
1818 else -- others
1819 return 'MSG_10_OTHER_COMP';
1820 end if;
1821 elsif p_exception_type = 9 then
1822 if (p_order_type = 1) then
1823 return 'MSG_7_PO_COMP';
1824 elsif (p_order_type = 2) then
1825 return 'MSG_7_REQ_COMP';
1826 elsif (p_order_type in (3, 5, 7, 18)) then -- work order
1827 return 'MSG_7_WORK_COMP';
1828 end if;
1829 end if;
1830 end if;
1831
1832 EXCEPTION
1833
1834 when others then
1835 wf_core.context('MSC_EXP_WF', 'GetPlannerMsgName', to_char(p_exception_type), to_char(p_order_type));
1836 raise;
1837
1838 END GetPlannerMsgName;
1839
1840 PROCEDURE DetermineOrderType( itemtype in varchar2,
1841 itemkey in varchar2,
1842 actid in number,
1843 funcmode in varchar2,
1844 resultout out NOCOPY varchar2) is
1845
1846 l_exception_id number :=
1847 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1848 itemkey => itemkey,
1849 aname => 'EXCEPTION_ID');
1850
1851 l_order_type number :=
1852 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1853 itemkey => itemkey,
1854 aname => 'ORDER_TYPE_CODE');
1855
1856
1857 BEGIN
1858
1859 msc_util.msc_debug('In determineordertype:'|| l_order_type);
1860
1861 if (funcmode = 'RUN') then
1862
1863 -- Purchase Order
1864 if (l_order_type = 1) then
1865
1866 resultout := 'COMPLETE:PURCHASE_ORDER';
1867
1868 -- Purchase Requisition
1869 elsif (l_order_type = 2) then
1870
1871 resultout := 'COMPLETE:PURCHASE_REQUISITION';
1872
1873 -- Discrete Job, Planned Order, Non-standard Job, Flow Schedule
1874 elsif (l_order_type in (3, 5, 7, 18)) then
1875
1876 resultout := 'COMPLETE:WORK_ORDER';
1877
1878 else
1879
1880 resultout := 'COMPLETE:OTEHR_ORDER_TYPES';
1881
1882 end if;
1883
1884 return;
1885
1886 end if;
1887
1888 if (funcmode = 'CANCEL') then
1889
1890 resultout := 'COMPLETE:';
1891 return;
1892
1893 end if;
1894
1895 if (funcmode = 'TIMEOUT') then
1896
1897 resultout := 'COMPLETE:';
1898 return;
1899
1900 end if;
1901
1902 EXCEPTION
1903
1904 when others then
1905 wf_core.context('MSC_EXP_WF', 'DetermineOrderType', itemtype, itemkey, actid, funcmode);
1906 raise;
1907
1908 END DetermineOrderType;
1909
1910 PROCEDURE Reschedule( itemtype in varchar2,
1911 itemkey in varchar2,
1912 actid in number,
1913 funcmode in varchar2,
1914 resultout out NOCOPY varchar2) is
1915
1916 l_plan_id number :=
1917 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1918 itemkey => itemkey,
1919 aname => 'PLAN_ID');
1920
1921 l_transaction_id number :=
1922 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1923 itemkey => itemkey,
1924 aname => 'TRANSACTION_ID');
1925 l_exception_type number :=
1926 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1927 itemkey => itemkey,
1928 aname => 'EXCEPTION_TYPE_ID');
1929
1930 l_order_type number :=
1931 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1932 itemkey => itemkey,
1933 aname => 'ORDER_TYPE_CODE');
1934 l_planner varchar2(200) :=
1935 wf_engine.GetItemAttrText( itemtype => itemtype,
1936 itemkey => itemkey,
1937 aname => 'PLANNER');
1938 p_request_id number;
1939 begin
1940
1941 if (funcmode = 'RUN') then
1942 -- This is the new call to the reschedule procedure
1943 if (l_order_type in (1, 2, 3)) then -- po, req, discrete
1944 begin
1945 msc_rel_wf.init_db(l_planner);
1946 p_request_id := fnd_request.submit_request(
1947 'MSC',
1948 'MSCWFRES',
1949 null,
1950 null,
1951 false,
1952 l_plan_id,
1953 l_transaction_id,
1954 l_exception_type);
1955 commit;
1956 exception when others then
1957 p_request_id :=0;
1958 end;
1959 wf_engine.SetItemAttrNumber( itemtype => itemtype,
1960 itemkey => itemkey,
1961 aname => 'SR_REQUEST_ID',
1962 avalue => p_request_id);
1963
1964 end if;
1965 if p_request_id is not null and p_request_id <> 0 then
1966 resultout := 'COMPLETE:1';
1967 else
1968 resultout := 'COMPLETE:0';
1969 end if;
1970 return;
1971 end if;
1972
1973 if (funcmode = 'CANCEL') then
1974 resultout := 'COMPLETE:';
1975 return;
1976 end if;
1977
1978 if (funcmode = 'TIMEOUT') then
1979 resultout := 'COMPLETE:';
1980 return;
1981 end if;
1982
1983 EXCEPTION
1984 when others then
1985 wf_core.context('MSC_EXP_WF', 'Reschedule', itemtype, itemkey, actid, funcmode);
1986 resultout := 'COMPLETE:0';
1987 raise;
1988
1989 end Reschedule;
1990
1991 PROCEDURE Reschedule_program(
1992 errbuf OUT NOCOPY VARCHAR2,
1993 retcode OUT NOCOPY NUMBER,
1994 l_plan_id in number,
1995 l_transaction_id in number,
1996 l_exception_type in number) is
1997
1998 l_plan_name varchar2(30) := 'dummy';
1999 l_order_type number;
2000 l_sr_instance_id number;
2001 l_org_id number;
2002 l_user_id number := fnd_global.user_id;
2003 l_po_group_by number := fnd_profile.value('MSC_LOAD_REQ_GROUP_BY');
2004 l_po_batch_number number;
2005 l_wip_group_id number;
2006
2007 l_po_header_id number;
2008 l_po_line_id number;
2009 l_po_number number;
2010 l_return_code boolean;
2011 l_new_need_by_date date;
2012 l_old_need_by_date date;
2013 l_reschedule_result boolean;
2014 l_load_type number;
2015
2016 var_loaded_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2017 var_loaded_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2018 var_loaded_scheds MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2019 var_resched_jobs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2020 var_resched_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2021 var_wip_req_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2022 var_req_load_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2023 var_req_reschd_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2024 var_released_inst MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2025 var_loaded_int_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2026 var_resched_int_reqs MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2027 var_int_req_load_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2028 var_int_req_reschd_id MSC_Rel_Plan_PUB.NumTblTyp:= MSC_Rel_Plan_PUB.NumTblTyp(0);
2029 var_loaded_lot_jobs MSC_Rel_Plan_PUB.NumTblTyp:=
2030 MSC_Rel_Plan_PUB.NumTblTyp(0);
2031 var_resched_lot_jobs MSC_Rel_Plan_PUB.NumTblTyp:=
2032 MSC_Rel_Plan_PUB.NumTblTyp(0);
2033 var_osfm_req_id MSC_Rel_Plan_PUB.NumTblTyp:=
2034 MSC_Rel_Plan_PUB.NumTblTyp(0);
2035 var_resched_eam_jobs MSC_Rel_Plan_PUB.NumTblTyp:=
2036 MSC_Rel_Plan_PUB.NumTblTyp(0);
2037 var_eam_req_id MSC_Rel_Plan_PUB.NumTblTyp:=
2038 MSC_Rel_Plan_PUB.NumTblTyp(0);
2039 var_loaded_int_repair_orders MSC_Rel_Plan_PUB.NumTblTyp:=
2040 MSC_Rel_Plan_PUB.NumTblTyp(0);
2041 var_int_repair_orders_id MSC_Rel_Plan_PUB.NumTblTyp:=
2042 MSC_Rel_Plan_PUB.NumTblTyp(0);
2043 var_loaded_ext_repair_orders MSC_Rel_Plan_PUB.NumTblTyp:=
2044 MSC_Rel_Plan_PUB.NumTblTyp(0);
2045 var_ext_repair_orders_id MSC_Rel_Plan_PUB.NumTblTyp:=
2046 MSC_Rel_Plan_PUB.NumTblTyp(0);
2047 p_po_res_id msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
2048 p_po_res_count msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
2049 p_po_pwb_count msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
2050 p_released_inst msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
2051 p_res_po_count number :=0;
2052 p_request_id number;
2053 BEGIN
2054
2055 UPDATE MSC_SUPPLIES
2056 SET old_order_quantity = new_order_quantity,
2057 quantity_in_process = new_order_quantity,
2058 implement_date = new_schedule_date,
2059 implement_dock_date = new_dock_date,
2060 implement_quantity =
2061 decode(l_exception_type, 8, 0, new_order_quantity),
2062 implement_status_code =
2063 decode(order_type, 3,
2064 decode(l_exception_type, 8, 7, implement_status_code),
2065 implement_status_code),
2066 implement_source_org_id = NULL,
2067 implement_supplier_id = NULL,
2068 implement_supplier_site_id = NULL,
2069 implement_project_id = project_id,
2070 implement_task_id = task_id,
2071 implement_demand_class = demand_class,
2072 load_type =
2073 decode(order_type, 3,
2074 decode(nvl(cfm_routing_flag,0), 3, 6, 4),
2075 2, 16, 1, 20, NULL),
2076 last_update_date = sysdate,
2077 last_updated_by = l_user_id
2078 WHERE transaction_id = l_transaction_id
2079 AND plan_id = l_plan_id
2080 returning organization_id, sr_instance_id, order_type into
2081 l_org_id, l_sr_instance_id, l_order_type;
2082
2083 if l_order_type = 1 then -- po
2084 FND_FILE.PUT_LINE(FND_FILE.LOG,
2085 'start to launch PO Reschedule for '||l_transaction_id);
2086 msc_rel_wf.reschedule_purchase_orders(
2087 l_plan_id,
2088 l_org_id,
2089 l_sr_instance_id,
2090 l_org_id,
2091 l_sr_instance_id,
2092 p_res_po_count,
2093 p_released_inst,
2094 p_po_res_id,
2095 p_po_res_count,
2096 p_po_pwb_count);
2097 p_request_id := p_po_res_id(1);
2098 else
2099 FND_FILE.PUT_LINE(FND_FILE.LOG,
2100 'start msc_rel_plan_pub.msc_release_plan_sc for '||l_transaction_id);
2101 msc_rel_plan_pub.msc_release_plan_sc
2102 ( arg_plan_id => l_plan_id
2103 , arg_log_org_id => l_org_id
2104 , arg_log_sr_instance => l_sr_instance_id
2105 , arg_org_id => l_org_id
2106 , arg_sr_instance => l_sr_instance_id
2107 , arg_compile_desig => l_plan_name
2108 , arg_user_id => l_user_id
2109 , arg_po_group_by => l_po_group_by
2110 , arg_po_batch_number => l_po_batch_number
2111 , arg_wip_group_id => l_wip_group_id -- 111
2112 , arg_loaded_jobs => var_loaded_jobs
2113 , arg_loaded_reqs => var_loaded_reqs
2114 , arg_loaded_scheds => var_loaded_scheds
2115 , arg_resched_jobs => var_resched_jobs
2116 , arg_resched_reqs => var_resched_reqs
2117 , arg_wip_req_id => var_wip_req_id
2118 , arg_req_load_id => var_req_load_id
2119 , arg_req_resched_id => var_req_reschd_id
2120 , arg_released_instance => var_released_inst
2121 , arg_mode => 'WF'
2122 , arg_transaction_id => l_transaction_id
2123 , arg_loaded_lot_jobs => var_loaded_lot_jobs
2124 , arg_resched_lot_jobs => var_resched_lot_jobs
2125 , arg_osfm_req_id => var_osfm_req_id
2126 , arg_resched_eam_jobs => var_resched_eam_jobs
2127 , arg_eam_req_id => var_eam_req_id
2128 , arg_loaded_int_reqs => var_loaded_int_reqs
2129 , arg_resched_int_reqs => var_resched_int_reqs
2130 , arg_int_req_load_id => var_int_req_load_id
2131 , arg_int_req_resched_id => var_int_req_reschd_id
2132 , arg_loaded_int_repair_orders => var_loaded_int_repair_orders
2133 , arg_int_repair_orders_id => var_int_repair_orders_id
2134 , arg_loaded_ext_repair_orders => var_loaded_ext_repair_orders
2135 , arg_ext_repair_orders_id => var_ext_repair_orders_id
2136 );
2137 p_request_id := nvl(var_wip_req_id(1),
2138 nvl(var_osfm_req_id(1),
2139 nvl(var_req_reschd_id(1),var_int_req_reschd_id(1))));
2140 end if;
2141 FND_FILE.PUT_LINE(FND_FILE.LOG,
2142 'request id is '||p_request_id);
2143
2144 END Reschedule_program;
2145
2146
2147 PROCEDURE DeleteActivities( arg_plan_id in number) IS
2148
2149 TYPE DelExpType is REF CURSOR;
2150 delete_activities_c DelExpType;
2151 -- Note that the null is important for this instance
2152 CURSOR instance_c(p_plan_id in varchar2) IS
2153 SELECT DEcode(m2a_dblink, null, ' ', '@' || m2a_dblink)
2154 FROM msc_apps_instances
2155 WHERE instance_id in (select sr_instance_id
2156 from msc_plan_organizations
2157 where plan_id = p_plan_id)
2158 UNION
2159 select ' '
2160 from dual;
2161
2162 l_item_key varchar2(240);
2163 l_db_link varchar2(80);
2164 sql_stmt varchar2(1000);
2165 l_item_type varchar2(8);
2166
2167 BEGIN
2168
2169 -- First loop for each instance and in the inner loop for each
2170 -- exception id
2171
2172 OPEN instance_c(arg_plan_id);
2173 LOOP
2174 FETCH instance_c INTO l_db_link;
2175 EXIT WHEN instance_c%NOTFOUND;
2176
2177 if l_db_link <> ' ' then -- purge wf in the source
2178
2179 sql_stmt :=
2180 'begin mrp_msc_exp_wf.deleteActivities'||l_db_link||
2181 '(:arg_plan_id); end;';
2182
2183 EXECUTE IMMEDIATE sql_stmt USING arg_plan_id;
2184
2185 else
2186
2187 --sql_stmt := ' SELECT item_key, item_type ' ||
2188 -- ' FROM wf_items' || l_db_link ||
2189 -- ' WHERE item_type in (''MSCEXPWF'',''MRPEXWFS'') '||
2190 -- ' AND item_key like '''|| to_char(arg_plan_id) || '-%''';
2191 sql_stmt:= ' SELECT ITEM_KEY, ITEM_TYPE '||
2192 ' FROM WF_ITEM_ATTRIBUTE_VALUES '||
2193 ' WHERE ITEM_TYPE in (''MSCEXPWF'',''MRPEXWFS'') '||
2194 ' AND NAME=''PLAN_ID'' '||
2195 ' AND NUMBER_VALUE='||arg_plan_id;
2196
2197 OPEN delete_activities_c for sql_stmt;
2198 LOOP
2199
2200 FETCH DELETE_ACTIVITIES_C INTO l_item_key, l_item_type;
2201 EXIT WHEN DELETE_ACTIVITIES_C%NOTFOUND;
2202
2203 msc_util.msc_debug('DELETING dblink:' || l_db_link || ':' || l_item_key ||','||l_item_type);
2204 update
2205 wf_notifications
2206 set end_date = sysdate
2207 where group_id in
2208 (select notification_id
2209 from wf_item_activity_statuses
2210 where item_type = l_item_type
2211 and item_key = l_item_key
2212 union
2213 select notification_id
2214 from wf_item_activity_statuses_h
2215 where item_type = l_item_type
2216 and item_key = l_item_key);
2217
2218 update wf_items
2219 set end_date = sysdate
2220 where item_type = l_item_type
2221 and item_key = l_item_key;
2222
2223 update wf_item_activity_statuses
2224 set end_date = sysdate
2225 where item_type = l_item_type
2226 and item_key = l_item_key;
2227
2228 update wf_item_activity_statuses_h
2229 set end_date = sysdate
2230 where item_type = l_item_type
2231 and item_key = l_item_key;
2232
2233 --wf_purge.total(l_item_type,l_item_key,sysdate);
2234 wf_purge.items(l_item_type,l_item_key,sysdate,true,true);
2235
2236
2237 END LOOP; -- for the itemkey loop
2238 CLOSE delete_activities_c;
2239 end if;
2240 END LOOP; -- for the instance loop
2241 CLOSE instance_c;
2242 commit work;
2243 return;
2244
2245 EXCEPTION
2246 when others then
2247 msc_util.msc_debug('Error in delete activities:'|| to_char(sqlcode) || ':'
2248 || substr(sqlerrm,1,100));
2249
2250 return;
2251 END DeleteActivities;
2252
2253 FUNCTION SupplierCapacity(arg_plan_id in number,
2254 arg_exception_id in number)
2255 return number
2256 IS
2257 total_cap number := 0;
2258 p_partner_id number;
2259 p_site_id number;
2260 p_item_id number;
2261 p_org_id number;
2262 p_inst_id number;
2263 p_cap number;
2264 days_between number;
2265 l_from date;
2266 l_to date;
2267 p_from_date date;
2268 p_to_date date;
2269 p_percent number;
2270 p_uom varchar2(30);
2271 p_puom varchar2(30);
2272 curr_date date;
2273 temp_date date;
2274 suptol SupplierToleranceRecord;
2275 /*
2276 CURSOR uom(partner_id in number,partner_site_id in number,
2277 item_id in number, org_id in number, inst_id in number) IS
2278 SELECT DISTINCT convd.conversion_rate/convs.conversion_rate
2279 FROM msc_uom_conversions convs,msc_uom_conversions convd,
2280 msc_system_items msi, msc_item_suppliers sup
2281 WHERE sup.supplier_id = partner_id
2282 AND sup.supplier_site_id = partner_site_id
2283 AND sup.inventory_item_id = item_id
2284 AND sup.organization_id = org_id
2285 AND sup.sr_instance_id = inst_id
2286 AND sup.plan_id = arg_plan_id
2287 AND sup.using_organization_id = -1
2288 AND msi.plan_id = arg_plan_id
2289 AND msi.inventory_item_id = item_id
2290 AND msi.organization_id = org_id
2291 AND msi.sr_instance_id = inst_id
2292 AND convs.sr_instance_id = inst_id
2293 AND convs.inventory_item_id = 0
2294 AND convs.uom_code = msi.uom_code
2295 AND convd.sr_instance_id = inst_id
2296 AND convd.inventory_item_id = 0
2297 AND convd.uom_code = sup.purchasing_unit_of_measure;
2298 */
2299
2300 CURSOR sup_cap(partner_id in number,partner_site_id in number,
2301 p_from_date in date,p_to_date in date, item_id in number,
2302 p_org_id in number, inst_id in number) IS
2303 SELECT DISTINCT capacity,from_date,NVL(to_date,from_date)
2304 FROM msc_supplier_capacities
2305 WHERE plan_id = arg_plan_id
2306 AND supplier_id = partner_id
2307 AND supplier_site_id = partner_site_id
2308 AND inventory_item_id = item_id
2309 AND organization_id = p_org_id
2310 AND sr_instance_id = inst_id
2311 AND from_date <= NVL(p_to_date,p_from_date)
2312 AND to_date > p_from_date;
2313 BEGIN
2314 -- Get the reference data for future SQL
2315 --dbms_output.put_Line('In procedure');
2316 SELECT DISTINCT from_date,NVL(to_date,from_date),
2317 NVL(utilization_rate,quantity),supplier_id,
2318 supplier_site_id, inventory_item_id, organization_id, sr_instance_id
2319 INTO p_from_date,p_to_date,p_percent,p_partner_id,p_site_id,p_item_id,
2320 p_org_id,p_inst_id
2321 FROM msc_exception_details_v
2322 WHERE plan_id = arg_plan_id
2323 AND exception_id = arg_exception_id;
2324
2325 -- Get the tolerance information
2326 --dbms_output.put_line('Before the tolerance information');
2327 SELECT DISTINCT fence_days, tolerance_percentage
2328 BULK COLLECT INTO suptol.fence,suptol.tolerance
2329 FROM msc_supplier_flex_fences
2330 WHERE plan_id = arg_plan_id
2331 AND inventory_item_id = p_item_id
2332 AND organization_id = p_org_id
2333 AND sr_instance_id = p_inst_id
2334 AND supplier_id = p_partner_id
2335 AND supplier_site_id = p_site_id
2336 ORDER BY fence_days;
2337
2338 -- This obtains the capacity rows
2339 -- For each of them we need to apply the tolerances, to find the actual
2340 -- capacity promised
2341 curr_date := MSC_CALENDAR.NEXT_WORK_DAY(
2342 p_org_id,p_inst_id,MSC_CALENDAR.TYPE_DAILY_BUCKET,
2343 sysdate);
2344 --dbms_output.put_line('p_to_date:'|| to_char(p_to_date,'DD-MON-YYYY'));
2345 --dbms_output.put_line('p_from_date:'|| to_char(p_from_date,'DD-MON-YYYY'));
2346 --dbms_output.put_line('Curr date:' || to_char(curr_date,'DD-MON-YYYY'));
2347 --dbms_output.put_line('p_percent:' || to_char(p_percent));
2348 OPEN sup_cap(p_partner_id,p_site_id,p_from_date,p_to_date,p_item_id,
2349 p_org_id,p_inst_id);
2350 LOOP
2351 FETCH sup_cap INTO p_cap,l_from,l_to;
2352 EXIT WHEN sup_cap%NOTFOUND;
2353 -- Now for each record, calculate the net capacity
2354
2355 --dbms_output.put_line('l_from:' || to_char(l_from,'DD-MON-YYYY'));
2356 --dbms_output.put_line('l_to:' || to_char(l_to,'DD-MON-YYYY'));
2357 --dbms_output.put_line('p_cap:' || to_char(p_cap));
2358
2359 if (p_from_date >= l_from) then
2360 if ((p_from_date - l_to) > 0) then
2361 if ((curr_date - l_from) > 0) then
2362 days_between := l_to - curr_date +1;
2363 else
2364 if (trunc(p_to_date) = trunc(p_from_date)) then
2365 days_between := 1;
2366 else
2367 days_between := l_to - l_from +1;
2368 end if;
2369 end if;
2370 else
2371 if ((curr_date - l_from) > 0) then
2372 days_between := p_from_date - curr_date;
2373 else
2374 if (trunc(p_to_date) = trunc(p_from_date)) then
2375 days_between := 1;
2376 else
2377 days_between := p_from_date - l_from;
2378 end if;
2379 end if;
2380 end if;
2381 --dbms_output.put_line('days_between:' || to_char(days_between));
2382 if (days_between > 0) then
2383 total_cap := total_cap + p_cap * days_between;
2384 end if;
2385 -- Now the tolerance
2386 if (p_from_date > l_to) then
2387 temp_date := l_to +1;
2388 else
2389 temp_date := p_from_date;
2390 end if;
2391 --dbms_output.put_line('temp date:' || to_char(temp_date,'DD-MON-YYYY'));
2392 -- Check tolerances only if they exist
2393 IF (suptol.fence.COUNT > 0) THEN
2394 FOR i in suptol.fence.FIRST..suptol.fence.LAST LOOP
2395 if (l_from > (curr_date + suptol.fence(i))) then
2396 days_between := temp_date - l_from;
2397 else
2398 days_between := temp_date - curr_date - suptol.fence(i);
2399 end if;
2400 if (days_between > 0) then
2401 total_cap := total_cap + p_cap * (suptol.tolerance(i)/100) *
2402 days_between;
2403 temp_date := temp_date - days_between;
2404 end if;
2405 END LOOP;
2406 END IF;
2407 end if; -- (p_from_date > l_from)
2408 --total_cap := total_cap + p_cap;
2409 END LOOP;
2410 CLOSE sup_cap;
2411 -- Note that the capacity is already in the vendor UOM, so we are okay
2412 --dbms_output.put_line('Final total_cap:' || to_char(total_cap));
2413
2414 return(total_cap * p_percent/100.0);
2415 EXCEPTION
2416 WHEN OTHERS THEN
2417 --dbms_output.put_line('Error:'|| to_char(sqlcode) || ':' ||
2418 -- substr(sqlerrm,1,50));
2419 return(0);
2420 END SupplierCapacity;
2421
2422
2423 PROCEDURE IsCallback(itemtype in varchar2,
2424 itemkey in varchar2,
2425 actid in number,
2426 funcmode in varchar2,
2427 resultout out NOCOPY varchar2) is
2428 l_is_callback varchar2(3) :=
2429 wf_engine.GetItemAttrText( itemtype => itemtype,
2430 itemkey => itemkey,
2431 aname => 'IS_CALL_BACK');
2432
2433 BEGIN
2434 if (funcmode = 'RUN') then
2435 resultout := 'COMPLETE:' || l_is_callback;
2436 return;
2437 end if;
2438
2439 if (funcmode = 'CANCEL') then
2440 resultout := 'COMPLETE:';
2441 return;
2442 end if;
2443
2444 if (funcmode = 'TIMEOUT') then
2445 resultout := 'COMPLETE:';
2446 return;
2447 end if;
2448
2449 EXCEPTION
2450
2451 when others then
2452 wf_core.context('MSC_EXP_WF', 'IsCallback', itemtype, itemkey, actid, funcmode);
2453 raise;
2454
2455 END IsCallback;
2456
2457 PROCEDURE SelectSrUsers(itemtype in varchar2,
2458 itemkey in varchar2,
2459 actid in number,
2460 funcmode in varchar2,
2461 resultout out NOCOPY varchar2) is
2462
2463 CURSOR BUYER_C(p_inventory_item_id in number,
2464 p_plan_id in number,
2465 p_org_id in number,
2466 p_instance in number) IS
2467 SELECT cont.name
2468 FROM msc_partner_contacts cont, msc_system_items sys
2469 WHERE sys.inventory_item_id = p_inventory_item_id
2470 AND sys.organization_id = p_org_id
2471 AND sys.sr_instance_id = p_instance
2472 AND sys.plan_id = p_plan_id
2473 AND cont.partner_id = sys.buyer_id
2474 AND cont.sr_instance_id = sys.sr_instance_id
2475 AND cont.partner_type = 4;
2476
2477 CURSOR SUPCNT_C(p_exception_id in number,p_plan_id in number) IS
2478 SELECT name
2479 FROM msc_partner_contacts cont,
2480 msc_exception_details exp,
2481 msc_supplies ms
2482 WHERE exp.exception_detail_id = p_exception_id
2483 AND exp.plan_id = p_plan_id
2484 AND cont.partner_site_id = nvl(exp.supplier_site_id,ms.supplier_site_id)
2485 AND cont.partner_type = 1
2486 AND cont.sr_instance_id = nvl(exp.sr_instance_id,ms.sr_instance_id)
2487 AND ms.plan_id(+) = exp.plan_id
2488 AND ms.transaction_id(+) = exp.number1;
2489
2490 CURSOR SALESREP_C(p_exception_id in number,
2491 p_plan_id in number) IS
2492 SELECT so.salesrep_id
2493 FROM msc_sales_orders so,
2494 msc_demands mgr,
2495 msc_exception_details exp
2496 WHERE so.sales_order_number = mgr.order_number
2497 AND so.sr_instance_id = mgr.sr_instance_id
2498 AND mgr.plan_id = exp.plan_id
2499 AND mgr.demand_id = exp.number1
2500 AND exp.exception_detail_id = p_exception_id
2501 AND exp.plan_id = p_plan_id;
2502
2503 CURSOR SALESREP_C2(p_demand_id in number,
2504 p_plan_id in number) IS
2505 SELECT so.salesrep_id
2506 FROM msc_sales_orders so,
2507 msc_demands mgr
2508 WHERE so.sales_order_number = mgr.order_number
2509 AND so.sr_instance_id = mgr.sr_instance_id
2510 AND mgr.plan_id = p_plan_id
2511 AND mgr.demand_id = p_demand_id;
2512
2513 CURSOR CUSTCNT_C(p_exception_id in number, p_plan_id in number) IS
2514 SELECT name
2515 FROM msc_partner_contacts cont, msc_demands mgr,
2516 msc_exception_details exp
2517 WHERE exp.exception_detail_id = p_exception_id
2518 AND exp.plan_id = p_plan_id
2519 AND cont.partner_id = mgr.customer_id
2520 AND cont.partner_site_id = mgr.customer_site_id
2521 AND cont.partner_type = 2
2522 and cont.sr_instance_id = mgr.sr_instance_id
2523 AND mgr.demand_id = exp.number1
2524 AND mgr.plan_id = exp.plan_id;
2525
2526 CURSOR CUSTCNT_C2(p_demand_id in number, p_plan_id in number) IS
2527 SELECT name
2528 FROM msc_partner_contacts cont, msc_demands mgr
2529 where cont.partner_id = mgr.customer_id
2530 AND cont.partner_site_id = mgr.customer_site_id
2531 AND cont.partner_type = 2
2532 and cont.sr_instance_id = mgr.sr_instance_id
2533 AND mgr.demand_id = p_demand_id
2534 AND mgr.plan_id = p_plan_id;
2535
2536
2537
2538 l_exception_type number :=
2539 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2540 itemkey => itemkey,
2541 aname => 'EXCEPTION_TYPE_ID');
2542
2543 l_demand_id number :=
2544 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2545 itemkey => itemkey,
2546 aname => 'TRANSACTION_ID');
2547
2548 l_order_type number :=
2549 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2550 itemkey => itemkey,
2551 aname => 'ORDER_TYPE_CODE');
2552
2553 l_inventory_item_id number :=
2554 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2555 itemkey => itemkey,
2556 aname => 'INVENTORY_ITEM_ID');
2557
2558 l_plan_id number :=
2559 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2560 itemkey => itemkey,
2561 aname => 'PLAN_ID');
2562
2563 l_org_id number :=
2564 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2565 itemkey => itemkey,
2566 aname => 'ORGANIZATION_ID');
2567
2568 l_instance_id number :=
2569 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2570 itemkey => itemkey,
2571 aname => 'INSTANCE_ID');
2572
2573 l_from_project_number varchar2(100) :=
2574 wf_engine.GetItemAttrText( itemtype => itemtype,
2575 itemkey => itemkey,
2576 aname => 'PROJECT_NUMBER');
2577
2578 l_to_project_number varchar2(100) :=
2579 wf_engine.GetItemAttrText( itemtype => itemtype,
2580 itemkey => itemkey,
2581 aname => 'TO_PROJECT_NUMBER');
2582
2583 l_from_task_number varchar2(100) :=
2584 wf_engine.GetItemAttrText( itemtype => itemtype,
2585 itemkey => itemkey,
2586 aname => 'TASK_NUMBER');
2587
2588 l_to_task_number varchar2(100) :=
2589 wf_engine.GetItemAttrText( itemtype => itemtype,
2590 itemkey => itemkey,
2591 aname => 'TO_TASK_NUMBER');
2592
2593 l_exception_id number :=
2594 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2595 itemkey => itemkey,
2596 aname => 'EXCEPTION_ID');
2597
2598 l_buyer varchar2(30);
2599 l_supcnt varchar2(30);
2600 l_salesrep varchar2(30);
2601 l_from_prj_mgr varchar2(100);
2602 l_to_prj_mgr varchar2(100);
2603 l_custcnt varchar2(30);
2604
2605 CURSOR PRJ_MGR_C(p_project_number in varchar2) IS
2606 SELECT proj.manager_contact
2607 FROM msc_projects proj
2608 where proj.project_number = p_project_number
2609 and proj.organization_id = l_org_id
2610 and proj.sr_instance_id = l_instance_id
2611 AND proj.plan_id = -1;
2612
2613 CURSOR TSK_MGR_C(p_project_number in varchar2, p_task_number in varchar2) IS
2614 SELECT NVL(tasks.manager_contact,proj.manager_contact)
2615 FROM msc_projects proj, msc_project_tasks tasks
2616 WHERE tasks.task_number = p_task_number
2617 AND proj.project_id = tasks.project_id
2618 AND proj.project_number = p_project_number
2619 and proj.organization_id = tasks.organization_id
2620 and proj.sr_instance_id = tasks.sr_instance_id
2621 AND proj.plan_id = tasks.plan_id
2622 and proj.organization_id = l_org_id
2623 and proj.sr_instance_id = l_instance_id
2624 AND proj.plan_id = -1;
2625
2626 BEGIN
2627 if (funcmode = 'RUN') then
2628 if l_exception_type in (28, 37) OR
2629 (l_exception_type in (6, 7, 8, 9, 10) and l_order_type in (1,2)) then
2630
2631 OPEN BUYER_C(l_inventory_item_id, l_plan_id, l_org_id,l_instance_id);
2632 FETCH BUYER_C INTO l_buyer;
2633 CLOSE BUYER_C;
2634
2635 --l_buyer := 'MFG';
2636
2637 if (l_buyer is not null) then
2638 wf_engine.setItemAttrText( itemtype => itemtype,
2639 itemkey => itemkey,
2640 aname => 'BUYER',
2641 avalue => l_buyer);
2642 END IF;
2643 END IF;
2644
2645 if (l_exception_type in (6, 7, 8, 9, 10) and l_order_type = 1) or
2646 l_exception_type in (37, 28) then
2647
2648 OPEN SUPCNT_C(l_exception_id,l_plan_id);
2649 FETCH SUPCNT_C INTO l_supcnt;
2650 CLOSE SUPCNT_C;
2651
2652 -- l_supcnt := 'MFG';
2653
2654 if (l_supcnt is not null) then
2655 wf_engine.setItemAttrText( itemtype => itemtype,
2656 itemkey => itemkey,
2657 aname => 'SUPCNT',
2658 avalue => l_supcnt);
2659 END IF;
2660 END IF;
2661
2662 if (l_exception_type in (13, 15, 24, 25, 49, 70)) then
2663 if l_exception_type = 70 then
2664 OPEN SALESREP_C2(l_demand_id,l_plan_id);
2665 FETCH SALESREP_C2 INTO l_salesrep;
2666 CLOSE SALESREP_C2;
2667 else
2668 OPEN SALESREP_C(l_exception_id,l_plan_id);
2669 FETCH SALESREP_C INTO l_salesrep;
2670 CLOSE SALESREP_C;
2671 end if;
2672
2673 -- l_salesrep := '1208';
2674 if (l_salesrep is not null) then
2675 wf_engine.setItemAttrText( itemtype => itemtype,
2676 itemkey => itemkey,
2677 aname => 'SALESREP',
2678 avalue => l_salesrep);
2679 END IF;
2680
2681 if l_exception_type = 70 then
2682 OPEN CUSTCNT_C2(l_demand_id,l_plan_id);
2683 FETCH CUSTCNT_C2 INTO l_custcnt;
2684 CLOSE CUSTCNT_C2;
2685 else
2686 OPEN CUSTCNT_C(l_exception_id,l_plan_id);
2687 FETCH CUSTCNT_C INTO l_custcnt;
2688 CLOSE CUSTCNT_C;
2689 end if;
2690 -- l_custcnt := 'MFG';
2691
2692 if (l_custcnt is not null) then
2693 wf_engine.setItemAttrText( itemtype => itemtype,
2694 itemkey => itemkey,
2695 aname => 'CUSTCNT',
2696 avalue => l_custcnt);
2697 END IF;
2698 end if;
2699
2700 if (l_exception_type in (17, 18, 19)) then
2701 if l_from_project_number is not null and
2702 l_from_task_number is not null then
2703 OPEN TSK_MGR_C(l_from_project_number,l_from_task_number);
2704 FETCH TSK_MGR_C INTO l_from_prj_mgr;
2705 CLOSE TSK_MGR_C;
2706 elsif l_from_project_number is not null then
2707
2708 OPEN PRJ_MGR_C(l_from_project_number);
2709 FETCH PRJ_MGR_C INTO l_from_prj_mgr;
2710 CLOSE PRJ_MGR_C;
2711 end if;
2712 --l_from_prj_mgr := 'MFG';
2713
2714 if (l_from_prj_mgr is not null) then
2715 wf_engine.setItemAttrText( itemtype => itemtype,
2716 itemkey => itemkey,
2717 aname => 'FROM_PRJ_MGR',
2718 avalue => l_from_prj_mgr);
2719 END IF;
2720
2721 if l_to_project_number is not null and
2722 l_to_task_number is not null then
2723 OPEN TSK_MGR_C(l_to_project_number,l_to_task_number);
2724 FETCH TSK_MGR_C INTO l_to_prj_mgr;
2725 CLOSE TSK_MGR_C;
2726 elsif l_to_project_number is not null then
2727
2728 OPEN PRJ_MGR_C(l_to_project_number);
2729 FETCH PRJ_MGR_C INTO l_to_prj_mgr;
2730 CLOSE PRJ_MGR_C;
2731 end if;
2732
2733 --l_to_prj_mgr := 'MFG';
2734 if (l_to_prj_mgr is not null) then
2735 wf_engine.setItemAttrText( itemtype => itemtype,
2736 itemkey => itemkey,
2737 aname => 'TO_PRJ_MGR',
2738 avalue => l_to_prj_mgr);
2739 END IF;
2740
2741 end if;
2742
2743 resultout := 'COMPLETE:';
2744 return;
2745 end if;
2746
2747 if (funcmode = 'CANCEL') then
2748 resultout := 'COMPLETE:';
2749 return;
2750 end if;
2751
2752 if (funcmode = 'TIMEOUT') then
2753 resultout := 'COMPLETE:';
2754 return;
2755 end if;
2756
2757 EXCEPTION
2758
2759 when others then
2760 wf_core.context('MSC_EXP_WF', 'IsCallback', itemtype, itemkey, actid, funcmode);
2761 raise;
2762
2763 END SelectSrUsers;
2764
2765 PROCEDURE CheckBuyer(itemtype in varchar2,
2766 itemkey in varchar2,
2767 actid in number,
2768 funcmode in varchar2,
2769 resultout out NOCOPY varchar2) is
2770
2771 l_planner varchar2(20) :=
2772 wf_engine.GetItemAttrText( itemtype => itemtype,
2773 itemkey => itemkey,
2774 aname => 'PLANNER');
2775 l_buyer varchar2(50) :=
2776 wf_engine.GetItemAttrText( itemtype => itemtype,
2777 itemkey => itemkey,
2778 aname => 'BUYER');
2779 BEGIN
2780 if (funcmode = 'RUN') then
2781 if (l_buyer is null) then
2782 resultout := 'COMPLETE:NOT_FOUND';
2783 return;
2784 /*
2785 elsif l_buyer = l_planner then
2786 resultout := 'COMPLETE:IS_PLANNER';
2787 return;
2788 */
2789 else
2790 resultout := 'COMPLETE:FOUND';
2791 return;
2792 end if;
2793 end if;
2794 if (funcmode = 'CANCEL') then
2795 resultout := 'COMPLETE:';
2796 return;
2797 end if;
2798
2799 if (funcmode = 'TIMEOUT') then
2800 resultout := 'COMPLETE:';
2801 return;
2802 end if;
2803 END CheckBuyer;
2804
2805 -- call back a wf process at destition instance for completion
2806 PROCEDURE StartSrWF(itemtype in varchar2,
2807 itemkey in varchar2,
2808 actid in number,
2809 funcmode in varchar2,
2810 resultout out NOCOPY varchar2) is
2811
2812 l_db_link varchar2(30) :=
2813 wf_engine.GetItemAttrText( itemtype => itemtype,
2814 itemkey => itemkey,
2815 aname => 'DB_LINK');
2816
2817 l_exception_type number :=
2818 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2819 itemkey => itemkey,
2820 aname => 'EXCEPTION_TYPE_ID');
2821
2822 l_transaction_id number :=
2823 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2824 itemkey => itemkey,
2825 aname => 'TRANSACTION_ID');
2826
2827 l_order_type number :=
2828 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2829 itemkey => itemkey,
2830 aname => 'ORDER_TYPE_CODE');
2831
2832 l_sr_item_type varchar2(50) := 'MRPEXWFS';
2833 l_sr_item_key varchar2(100);
2834 l_text varchar2(100);
2835 l_numb number;
2836 l_date Date;
2837 l_sr_process varchar2(50);
2838 sql_stmt varchar2(2000);
2839 p_request_id number :=0;
2840 p_result boolean;
2841 BEGIN
2842 if (funcmode = 'RUN') then
2843 -- now find out sr process, and start it.
2844 if (l_exception_type in (28, 37)) then
2845 l_sr_process := 'MSC_SUPCAP_SR_PROCESS';
2846 elsif (l_exception_type in (6, 7, 8, 9, 10)) then
2847 if (l_order_type = 1) then
2848 l_sr_process := 'MSC_PO_SR_PROCESS';
2849 else
2850 l_sr_process := 'MSC_REQ_SR_PROCESS';
2851 END IF;
2852 elsif (l_exception_type in (13, 15, 24, 25, 49, 70)) then
2853 l_sr_process := 'MSC_SO_SR_PROCESS';
2854 elsif (l_exception_type in (17, 18, 19)) then
2855 l_sr_process := 'MSC_PRJ_SR_PROCESS';
2856 end if;
2857 l_sr_item_key := itemkey || '-' || l_sr_process;
2858
2859 sql_stmt := 'begin wf_engine.CreateProcess' || l_db_link ||
2860 '( itemtype => :l_itemtype,' ||
2861 'itemkey => :l_itemkey, ' ||
2862 'process => :l_process);end;';
2863 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type, l_sr_item_key,
2864 l_sr_process;
2865
2866 -- now copy attributes to sr wf process
2867 -- we could only copy those insterested attributes,
2868 -- but we copy all for debug purpose.
2869
2870 -- ORDER_TYPE_CODE
2871 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
2872 '(itemtype => :item_type,' ||
2873 ' itemkey => :item_key,' ||
2874 ' aname => ''ORDER_TYPE_CODE'',' ||
2875 ' avalue => :l_numb);end;';
2876 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_order_type;
2877
2878 -- EXCEPTION_TYPE_ID.
2879 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
2880 '(itemtype => :item_type,' ||
2881 ' itemkey => :item_key,' ||
2882 ' aname => ''EXCEPTION_TYPE_ID'',' ||
2883 ' avalue => :l_numb);end;';
2884 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_exception_type;
2885
2886
2887 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
2888 '(itemtype => :item_type,' ||
2889 ' itemkey => :item_key,' ||
2890 ' aname => ''TRANSACTION_ID'',' ||
2891 ' avalue => :l_numb);end;';
2892 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_transaction_id;
2893
2894 -- APPS_PS_DBLINK
2895 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2896 '(itemtype => :item_type,' ||
2897 ' itemkey => :item_key,' ||
2898 ' aname => ''APPS_PS_DBLINK'',' ||
2899 ' avalue => :l_text);end;';
2900 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_db_link;
2901
2902 --BUYER. we don't need to set back BUYER, set it for debug
2903 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2904 itemkey => itemkey,
2905 aname => 'BUYER');
2906 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2907 '(itemtype => :item_type,' ||
2908 ' itemkey => :item_key,' ||
2909 ' aname => ''BUYER'',' ||
2910 ' avalue => :l_text);end;';
2911 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2912
2913 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2914 itemkey => itemkey,
2915 aname => 'SALESREP');
2916 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2917 '(itemtype => :item_type,' ||
2918 ' itemkey => :item_key,' ||
2919 ' aname => ''SALESREP'',' ||
2920 ' avalue => :l_text);end;';
2921 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2922
2923 -- we don't need to set back CUSTCNT, for debug only
2924 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2925 itemkey => itemkey,
2926 aname => 'CUSTCNT');
2927 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2928 '(itemtype => :item_type,' ||
2929 ' itemkey => :item_key,' ||
2930 ' aname => ''CUSTCNT'',' ||
2931 ' avalue => :l_text);end;';
2932 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2933
2934 -- customer_name
2935 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2936 itemkey => itemkey,
2937 aname => 'CUSTOMER_NAME');
2938 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2939 '(itemtype => :item_type,' ||
2940 ' itemkey => :item_key,' ||
2941 ' aname => ''CUSTOMER_NAME'',' ||
2942 ' avalue => :l_text);end;';
2943 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2944
2945 -- customer_ID.
2946 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2947 itemkey => itemkey,
2948 aname => 'CUSTOMER_ID');
2949 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
2950 '(itemtype => :item_type,' ||
2951 ' itemkey => :item_key,' ||
2952 ' aname => ''CUSTOMER_ID'',' ||
2953 ' avalue => :l_numb);end;';
2954 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
2955
2956
2957 -- Days_compressed
2958 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2959 itemkey => itemkey,
2960 aname => 'DAYS_COMPRESSED');
2961 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
2962 '(itemtype => :item_type,' ||
2963 ' itemkey => :item_key,' ||
2964 ' aname => ''DAYS_COMPRESSED'',' ||
2965 ' avalue => :l_numb);end;';
2966 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
2967
2968 -- APPS_PS_DBLINK. we may not need.
2969 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2970 itemkey => itemkey,
2971 aname => 'APPS_PS_DBLINK');
2972 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2973 '(itemtype => :item_type,' ||
2974 ' itemkey => :item_key,' ||
2975 ' aname => ''APPS_PS_DBLINK'',' ||
2976 ' avalue => :l_text);end;';
2977 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2978
2979 -- DEPARTMENT_LINE_CODE.
2980 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2981 itemkey => itemkey,
2982 aname => 'DEPARTMENT_LINE_CODE');
2983 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2984 '(itemtype => :item_type,' ||
2985 ' itemkey => :item_key,' ||
2986 ' aname => ''DEPARTMENT_LINE_CODE'',' ||
2987 ' avalue => :l_text);end;';
2988 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2989
2990 -- due_date.
2991 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
2992 itemkey => itemkey,
2993 aname => 'DUE_DATE');
2994 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
2995 '(itemtype => :item_type,' ||
2996 ' itemkey => :item_key,' ||
2997 ' aname => ''DUE_DATE'',' ||
2998 ' avalue => :l_date);end;';
2999 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3000
3001 -- end_item_display_name.
3002 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3003 itemkey => itemkey,
3004 aname => 'END_ITEM_DISPLAY_NAME');
3005 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3006 '(itemtype => :item_type,' ||
3007 ' itemkey => :item_key,' ||
3008 ' aname => ''END_ITEM_DISPLAY_NAME'',' ||
3009 ' avalue => :l_text);end;';
3010 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3011
3012 -- end_item_description
3013
3014 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3015 itemkey => itemkey,
3016 aname => 'END_ITEM_DESCRIPTION');
3017 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3018 '(itemtype => :item_type,' ||
3019 ' itemkey => :item_key,' ||
3020 ' aname => ''END_ITEM_DESCRIPTION'',' ||
3021 ' avalue => :l_text);end;';
3022 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3023
3024
3025 --END_ORDER_NUMBER
3026 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3027 itemkey => itemkey,
3028 aname => 'END_ORDER_NUMBER');
3029 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3030 '(itemtype => :item_type,' ||
3031 ' itemkey => :item_key,' ||
3032 ' aname => ''END_ORDER_NUMBER'',' ||
3033 ' avalue => :l_text);end;';
3034 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3035
3036 -- EXCEPTION_DESCRIPTION
3037 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3038 itemkey => itemkey,
3039 aname => 'EXCEPTION_DESCRIPTION');
3040 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3041 '(itemtype => :item_type,' ||
3042 ' itemkey => :item_key,' ||
3043 ' aname => ''EXCEPTION_DESCRIPTION'',' ||
3044 ' avalue => :l_text);end;';
3045 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3046
3047 --EXCEPTION_ID
3048 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3049 itemkey => itemkey,
3050 aname => 'EXCEPTION_ID');
3051 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3052 '(itemtype => :item_type,' ||
3053 ' itemkey => :item_key,' ||
3054 ' aname => ''EXCEPTION_ID'',' ||
3055 ' avalue => :l_numb);end;';
3056 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3057
3058 -- FROM_DATE
3059 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3060 itemkey => itemkey,
3061 aname => 'FROM_DATE');
3062 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3063 '(itemtype => :item_type,' ||
3064 ' itemkey => :item_key,' ||
3065 ' aname => ''FROM_DATE'',' ||
3066 ' avalue => :l_date);end;';
3067 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3068
3069 -- FROM_PRJ_MGR
3070 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3071 itemkey => itemkey,
3072 aname => 'FROM_PRJ_MGR');
3073 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3074 '(itemtype => :item_type,' ||
3075 ' itemkey => :item_key,' ||
3076 ' aname => ''FROM_PRJ_MGR'',' ||
3077 ' avalue => :l_text);end;';
3078 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3079
3080 --INSTANCE_ID
3081 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3082 itemkey => itemkey,
3083 aname => 'INSTANCE_ID');
3084 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3085 '(itemtype => :item_type,' ||
3086 ' itemkey => :item_key,' ||
3087 ' aname => ''INSTANCE_ID'',' ||
3088 ' avalue => :l_numb);end;';
3089 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3090
3091 --INVENTORY_ITEM_ID
3092 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3093 itemkey => itemkey,
3094 aname => 'INVENTORY_ITEM_ID');
3095 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3096 '(itemtype => :item_type,' ||
3097 ' itemkey => :item_key,' ||
3098 ' aname => ''INVENTORY_ITEM_ID'',' ||
3099 ' avalue => :l_numb);end;';
3100 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3101
3102 -- ITEM_DISPLAY_NAME
3103 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3104 itemkey => itemkey,
3105 aname => 'ITEM_DISPLAY_NAME');
3106 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3107 '(itemtype => :item_type,' ||
3108 ' itemkey => :item_key,' ||
3109 ' aname => ''ITEM_DISPLAY_NAME'',' ||
3110 ' avalue => :l_text);end;';
3111 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3112
3113 -- ITEM DESCRIPTION
3114
3115 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3116 itemkey => itemkey,
3117 aname => 'ITEM_DESCRIPTION');
3118 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3119 '(itemtype => :item_type,' ||
3120 ' itemkey => :item_key,' ||
3121 ' aname => ''ITEM_DESCRIPTION'',' ||
3122 ' avalue => :l_text);end;';
3123 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3124
3125
3126 -- LOT_NUMBER
3127 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3128 itemkey => itemkey,
3129 aname => 'LOT_NUMBER');
3130 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3131 '(itemtype => :item_type,' ||
3132 ' itemkey => :item_key,' ||
3133 ' aname => ''LOT_NUMBER'',' ||
3134 ' avalue => :l_text);end;';
3135 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3136
3137 -- ORDER_NUMBER
3138 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3139 itemkey => itemkey,
3140 aname => 'ORDER_NUMBER');
3141 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3142 '(itemtype => :item_type,' ||
3143 ' itemkey => :item_key,' ||
3144 ' aname => ''ORDER_NUMBER'',' ||
3145 ' avalue => :l_text);end;';
3146 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3147
3148 -- ORGANIZATION_CODE
3149 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3150 itemkey => itemkey,
3151 aname => 'ORGANIZATION_CODE');
3152 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3153 '(itemtype => :item_type,' ||
3154 ' itemkey => :item_key,' ||
3155 ' aname => ''ORGANIZATION_CODE'',' ||
3156 ' avalue => :l_text);end;';
3157 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3158
3159 --ORGANIZATION_ID
3160 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3161 itemkey => itemkey,
3162 aname => 'ORGANIZATION_ID');
3163 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3164 '(itemtype => :item_type,' ||
3165 ' itemkey => :item_key,' ||
3166 ' aname => ''ORGANIZATION_ID'',' ||
3167 ' avalue => :l_numb);end;';
3168 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3169
3170 --PLAN_ID
3171 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3172 itemkey => itemkey,
3173 aname => 'PLAN_ID');
3174 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3175 '(itemtype => :item_type,' ||
3176 ' itemkey => :item_key,' ||
3177 ' aname => ''PLAN_ID'',' ||
3178 ' avalue => :l_numb);end;';
3179 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3180
3181 -- PLAN_NAME
3182 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3183 itemkey => itemkey,
3184 aname => 'PLAN_NAME');
3185 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3186 '(itemtype => :item_type,' ||
3187 ' itemkey => :item_key,' ||
3188 ' aname => ''PLAN_NAME'',' ||
3189 ' avalue => :l_text);end;';
3190 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3191
3192 -- PLANNER
3193 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3194 itemkey => itemkey,
3195 aname => 'PLANNER');
3196 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3197 '(itemtype => :item_type,' ||
3198 ' itemkey => :item_key,' ||
3199 ' aname => ''PLANNER'',' ||
3200 ' avalue => :l_text);end;';
3201 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3202
3203 -- PLANNING_GROUP
3204 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3205 itemkey => itemkey,
3206 aname => 'PLANNING_GROUP');
3207 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3208 '(itemtype => :item_type,' ||
3209 ' itemkey => :item_key,' ||
3210 ' aname => ''PLANNING_GROUP'',' ||
3211 ' avalue => :l_text);end;';
3212 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3213
3214 -- PROJECT_NUMBER
3215 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3216 itemkey => itemkey,
3217 aname => 'PROJECT_NUMBER');
3218 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3219 '(itemtype => :item_type,' ||
3220 ' itemkey => :item_key,' ||
3221 ' aname => ''PROJECT_NUMBER'',' ||
3222 ' avalue => :l_text);end;';
3223 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3224
3225 --PRE_PROCESSING_LEAD_TIME
3226 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3227 itemkey => itemkey,
3228 aname => 'PRE_PRSNG_LEAD_TIME');
3229 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3230 '(itemtype => :item_type,' ||
3231 ' itemkey => :item_key,' ||
3232 ' aname => ''PRE_PRSNG_LEAD_TIME'',' ||
3233 ' avalue => :l_text);end;';
3234 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3235
3236 --PROCESSING_LEAD_TIME
3237 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3238 itemkey => itemkey,
3239 aname => 'PRSNG_LEAD_TIME');
3240 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3241 '(itemtype => :item_type,' ||
3242 ' itemkey => :item_key,' ||
3243 ' aname => ''PRSNG_LEAD_TIME'',' ||
3244 ' avalue => :l_text);end;';
3245 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3246
3247 --POST_PROCESSING_LEAD_TIME
3248 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3249 itemkey => itemkey,
3250 aname => 'POST_PRSNG_LEAD_TIME');
3251 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3252 '(itemtype => :item_type,' ||
3253 ' itemkey => :item_key,' ||
3254 ' aname => ''POST_PRSNG_LEAD_TIME'',' ||
3255 ' avalue => :l_text);end;';
3256 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3257
3258
3259
3260 -- QUANTITY
3261 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3262 itemkey => itemkey,
3263 aname => 'QUANTITY');
3264 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3265 '(itemtype => :item_type,' ||
3266 ' itemkey => :item_key,' ||
3267 ' aname => ''QUANTITY'',' ||
3268 ' avalue => :l_text);end;';
3269 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3270
3271 -- RESOURCE_CODE
3272 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3273 itemkey => itemkey,
3274 aname => 'RESOURCE_CODE');
3275 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3276 '(itemtype => :item_type,' ||
3277 ' itemkey => :item_key,' ||
3278 ' aname => ''RESOURCE_CODE'',' ||
3279 ' avalue => :l_text);end;';
3280 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3281
3282 -- SUPCNT
3283 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3284 itemkey => itemkey,
3285 aname => 'SUPCNT');
3286 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3287 '(itemtype => :item_type,' ||
3288 ' itemkey => :item_key,' ||
3289 ' aname => ''SUPCNT'',' ||
3290 ' avalue => :l_text);end;';
3291 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3292
3293 --SUPPLIER_ID
3294 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3295 itemkey => itemkey,
3296 aname => 'SUPPLIER_ID');
3297 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3298 '(itemtype => :item_type,' ||
3299 ' itemkey => :item_key,' ||
3300 ' aname => ''SUPPLIER_ID'',' ||
3301 ' avalue => :l_numb);end;';
3302 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3303
3304 -- SUPPLIER_NAME
3305 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3306 itemkey => itemkey,
3307 aname => 'SUPPLIER_NAME');
3308 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3309 '(itemtype => :item_type,' ||
3310 ' itemkey => :item_key,' ||
3311 ' aname => ''SUPPLIER_NAME'',' ||
3312 ' avalue => :l_text);end;';
3313 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3314
3315 -- SUPPLIER_SITE_CODE
3316 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3317 itemkey => itemkey,
3318 aname => 'SUPPLIER_SITE_CODE');
3319 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3320 '(itemtype => :item_type,' ||
3321 ' itemkey => :item_key,' ||
3322 ' aname => ''SUPPLIER_SITE_CODE'',' ||
3323 ' avalue => :l_text);end;';
3324 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3325
3326 --SUPPLIER_SITE_ID
3327 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3328 itemkey => itemkey,
3329 aname => 'SUPPLIER_SITE_ID');
3330 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3331 '(itemtype => :item_type,' ||
3332 ' itemkey => :item_key,' ||
3333 ' aname => ''SUPPLIER_SITE_ID'',' ||
3334 ' avalue => :l_numb);end;';
3335 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3336
3337 -- SUPPLy_TYPE
3338 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3339 itemkey => itemkey,
3340 aname => 'SUPPLY_TYPE');
3341 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3342 '(itemtype => :item_type,' ||
3343 ' itemkey => :item_key,' ||
3344 ' aname => ''SUPPLY_TYPE'',' ||
3345 ' avalue => :l_text);end;';
3346 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3347
3348 -- TASK_NUMBER
3349 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3350 itemkey => itemkey,
3351 aname => 'TASK_NUMBER');
3352 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3353 '(itemtype => :item_type,' ||
3354 ' itemkey => :item_key,' ||
3355 ' aname => ''TASK_NUMBER'',' ||
3356 ' avalue => :l_text);end;';
3357 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3358
3359 -- TO_DATE
3360 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3361 itemkey => itemkey,
3362 aname => 'TO_DATE');
3363 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3364 '(itemtype => :item_type,' ||
3365 ' itemkey => :item_key,' ||
3366 ' aname => ''TO_DATE'',' ||
3367 ' avalue => :l_date);end;';
3368 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3369
3370 -- TO_PRJ_MGR
3371 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3372 itemkey => itemkey,
3373 aname => 'TO_PRJ_MGR');
3374 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3375 '(itemtype => :item_type,' ||
3376 ' itemkey => :item_key,' ||
3377 ' aname => ''TO_PRJ_MGR'',' ||
3378 ' avalue => :l_text);end;';
3379 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3380
3381 -- TO_PROJECT_NUMBER
3382 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3383 itemkey => itemkey,
3384 aname => 'TO_PROJECT_NUMBER');
3385 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3386 '(itemtype => :item_type,' ||
3387 ' itemkey => :item_key,' ||
3388 ' aname => ''TO_PROJECT_NUMBER'',' ||
3389 ' avalue => :l_text);end;';
3390 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3391
3392 -- TO_TASK_NUMBER
3393 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3394 itemkey => itemkey,
3395 aname => 'TO_TASK_NUMBER');
3396 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3397 '(itemtype => :item_type,' ||
3398 ' itemkey => :item_key,' ||
3399 ' aname => ''TO_TASK_NUMBER'',' ||
3400 ' avalue => :l_text);end;';
3401 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3402
3403 -- URL1
3404 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3405 itemkey => itemkey,
3406 aname => 'URL1');
3407 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3408 '(itemtype => :item_type,' ||
3409 ' itemkey => :item_key,' ||
3410 ' aname => ''URL1'',' ||
3411 ' avalue => :l_text);end;';
3412 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3413
3414 -- UTILIZATION_RATE
3415 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3416 itemkey => itemkey,
3417 aname => 'UTILIZATION_RATE');
3418 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3419 '(itemtype => :item_type,' ||
3420 ' itemkey => :item_key,' ||
3421 ' aname => ''UTILIZATION_RATE'',' ||
3422 ' avalue => :l_numb);end;';
3423 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3424
3425
3426 -- CAPACITY_REQUIREMENT
3427 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3428 itemkey => itemkey,
3429 aname => 'CAPACITY_REQUIREMENT');
3430 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3431 '(itemtype => :item_type,' ||
3432 ' itemkey => :item_key,' ||
3433 ' aname => ''CAPACITY_REQUIREMENT'',' ||
3434 ' avalue => :l_numb);end;';
3435 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3436
3437
3438 --REQUIRED_QUANTITY
3439 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3440 itemkey => itemkey,
3441 aname => 'REQUIRED_QUANTITY');
3442 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3443 '(itemtype => :item_type,' ||
3444 ' itemkey => :item_key,' ||
3445 ' aname => ''REQUIRED_QUANTITY'',' ||
3446 ' avalue => :l_numb);end;';
3447 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3448
3449
3450 --PROJECTED_AVAILABLE_BALANCE
3451 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3452 itemkey => itemkey,
3453 aname => 'PROJECTED_AVAILABLE_BALANCE');
3454 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3455 '(itemtype => :item_type,' ||
3456 ' itemkey => :item_key,' ||
3457 ' aname => ''PROJECTED_AVAILABLE_BALANCE'',' ||
3458 ' avalue => :l_numb);end;';
3459 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3460
3461 --AVAILABLE_QUANTITY
3462 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3463 itemkey => itemkey,
3464 aname => 'AVAILABLE_QUANTITY');
3465 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3466 '(itemtype => :item_type,' ||
3467 ' itemkey => :item_key,' ||
3468 ' aname => ''AVAILABLE_QUANTITY'',' ||
3469 ' avalue => :l_numb);end;';
3470 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3471
3472 --QTY_RELATED_VALUES
3473 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3474 itemkey => itemkey,
3475 aname => 'QTY_RELATED_VALUES');
3476 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3477 '(itemtype => :item_type,' ||
3478 ' itemkey => :item_key,' ||
3479 ' aname => ''QTY_RELATED_VALUES'',' ||
3480 ' avalue => :l_numb);end;';
3481 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3482
3483
3484
3485 if (l_exception_type =70 ) then
3486 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3487 itemkey => itemkey,
3488 aname => 'DATE1');
3489 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3490 '(itemtype => :item_type,' ||
3491 ' itemkey => :item_key,' ||
3492 ' aname => ''DATE1'',' ||
3493 ' avalue => :l_date);end;';
3494 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3495
3496 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3497 itemkey => itemkey,
3498 aname => 'DATE2');
3499 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3500 '(itemtype => :item_type,' ||
3501 ' itemkey => :item_key,' ||
3502 ' aname => ''DATE2'',' ||
3503 ' avalue => :l_date);end;';
3504 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3505 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3506 itemkey => itemkey,
3507 aname => 'DATE3');
3508 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3509 '(itemtype => :item_type,' ||
3510 ' itemkey => :item_key,' ||
3511 ' aname => ''DATE3'',' ||
3512 ' avalue => :l_date);end;';
3513 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3514 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3515 itemkey => itemkey,
3516 aname => 'DATE4');
3517 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3518 '(itemtype => :item_type,' ||
3519 ' itemkey => :item_key,' ||
3520 ' aname => ''DATE4'',' ||
3521 ' avalue => :l_date);end;';
3522 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3523 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3524 itemkey => itemkey,
3525 aname => 'DATE5');
3526 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3527 '(itemtype => :item_type,' ||
3528 ' itemkey => :item_key,' ||
3529 ' aname => ''DATE5'',' ||
3530 ' avalue => :l_date);end;';
3531 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3532 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3533 itemkey => itemkey,
3534 aname => 'DATE6');
3535 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3536 '(itemtype => :item_type,' ||
3537 ' itemkey => :item_key,' ||
3538 ' aname => ''DATE6'',' ||
3539 ' avalue => :l_date);end;';
3540 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3541 end if; -- end of if (l_exception_type = 70)
3542 -- now start wf process at destination instance
3543
3544 sql_stmt := 'begin wf_engine.StartProcess'|| l_db_link ||
3545 '( itemtype => :itemtype,' ||
3546 ' itemkey => :itemkey);end;';
3547 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key;
3548
3549 if l_db_link is not null and l_db_link <> ' ' then
3550 begin
3551 commit;
3552 execute immediate 'alter session close database link '||
3553 ltrim(l_db_link,'@');
3554 exception
3555 when others then
3556 wf_engine.SetItemAttrNumber( itemtype => itemtype,
3557 itemkey => itemkey,
3558 aname => 'SR_REQUEST_ID',
3559 avalue => -1);
3560 end;
3561 end if;
3562 wf_engine.SetItemAttrNumber( itemtype => itemtype,
3563 itemkey => itemkey,
3564 aname => 'SR_REQUEST_ID',
3565 avalue => p_request_id);
3566
3567
3568 resultout := 'COMPLETE:';
3569
3570
3571 RETURN;
3572 END IF;
3573
3574 IF (funcmode = 'CANCEL') THEN
3575 resultout := 'COMPLETE:';
3576 RETURN;
3577 END IF;
3578
3579 IF (funcmode = 'TIMEOUT') THEN
3580 resultout := 'COMPLETE:';
3581 RETURN;
3582 END IF;
3583 EXCEPTION
3584 when others then
3585 wf_core.context('MSC_EXP_WF', 'StartSrWF', itemtype, itemkey, actid, funcmode);
3586 raise;
3587 END StartSrWF;
3588
3589 Procedure launch_background_program(p_planner in varchar2,
3590 p_item_type in varchar2,
3591 p_item_key in varchar2,
3592 p_request_id out NOCOPY number) IS
3593 p_result boolean;
3594 Begin
3595 msc_rel_wf.init_db(p_planner);
3596 p_result := fnd_request.set_mode(true);
3597
3598 -- this will call start_deferred_activity
3599 p_request_id := fnd_request.submit_request(
3600 'MSC',
3601 'MSCWFBG',
3602 null,
3603 null,
3604 false,
3605 p_item_type,
3606 p_item_key);
3607
3608 exception when others then
3609 p_request_id :=0;
3610 raise;
3611 End launch_background_program;
3612
3613 Procedure start_deferred_activity(
3614 errbuf OUT NOCOPY VARCHAR2,
3615 retcode OUT NOCOPY NUMBER,
3616 p_item_type varchar2,
3617 p_item_key varchar2) IS
3618 CURSOR status_cur IS
3619 select 1
3620 from wf_item_activity_statuses
3621 where item_type = p_item_type
3622 and item_key = p_item_key
3623 and activity_status = 'DEFERRED';
3624 v_dummy number;
3625 v_time_elapsed number := 0;
3626 BEGIN
3627 while (v_dummy is null and v_time_elapsed < 120) loop
3628 OPEN status_cur;
3629 FETCH status_cur INTO v_dummy;
3630 CLOSE status_cur;
3631 FND_FILE.PUT_LINE(FND_FILE.LOG,'check deferred for '||p_item_key);
3632 dbms_lock.sleep(10);
3633 v_time_elapsed := v_time_elapsed + 10;
3634 end loop;
3635 if v_dummy = 1 then
3636 FND_FILE.PUT_LINE(FND_FILE.LOG,
3637 'start backgroud process for'||p_item_type);
3638 wf_engine.background(p_item_type);
3639 end if;
3640 END start_deferred_activity;
3641
3642 FUNCTION demand_order_type (p_plan_id number,
3643 p_inst_id number,
3644 p_demand_id NUMBER) return number is
3645 CURSOR dmd_cur IS
3646 SELECT origination_type
3647 FROM msc_demands
3648 WHERE plan_id = p_plan_id
3649 and sr_instance_id = p_inst_id
3650 and demand_id = p_demand_id;
3651 p_order_type number;
3652 BEGIN
3653 if ( p_plan_id is null OR p_inst_id is null or p_demand_id is null ) then
3654 return to_number(null);
3655 end if;
3656
3657 OPEN dmd_cur;
3658 FETCH dmd_cur INTO p_order_type;
3659 CLOSE dmd_cur;
3660 return p_order_type;
3661 END demand_order_type;
3662
3663 FUNCTION demand_order_date (p_plan_id number,
3664 p_inst_id number,
3665 p_demand_id NUMBER) return date is
3666 CURSOR dmd_cur IS
3667 SELECT using_assembly_demand_date
3668 FROM msc_demands
3669 WHERE plan_id = p_plan_id
3670 and sr_instance_id = p_inst_id
3671 and demand_id = p_demand_id;
3672 p_order_date date;
3673 BEGIN
3674 if ( p_plan_id is null OR p_inst_id is null or p_demand_id is null ) then
3675 return to_date(null);
3676 end if;
3677
3678 OPEN dmd_cur;
3679 FETCH dmd_cur INTO p_order_date;
3680 CLOSE dmd_cur;
3681 return p_order_date;
3682 END demand_order_date;
3683
3684 /*Function to return the substitute_supply_date for exception 49.*/
3685 FUNCTION substitute_supply_date (p_plan_id number,
3686 p_inst_id number,
3687 p_demand_id NUMBER) return date is
3688 CURSOR dmd_cur IS
3689 SELECT DMD_SATISFIED_DATE
3690 FROM msc_demands
3691 WHERE plan_id = p_plan_id
3692 and sr_instance_id = p_inst_id
3693 and demand_id = p_demand_id;
3694 p_supply_date date;
3695 BEGIN
3696 if ( p_plan_id is null OR p_inst_id is null or p_demand_id is null ) then
3697 return to_date(null);
3698 end if;
3699
3700 OPEN dmd_cur;
3701 FETCH dmd_cur INTO p_supply_date;
3702 CLOSE dmd_cur;
3703 return p_supply_date;
3704 END substitute_supply_date;
3705
3706 END msc_exp_wf;