[Home] [Help]
PACKAGE BODY: APPS.MSC_EXP_WF
Source
1 PACKAGE BODY msc_exp_wf AS
2 /*$Header: MSCEXWFB.pls 120.5 2007/12/14 21:41:33 eychen 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,
297 null,null,null),
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),
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,
405 17, msc_get_name.task(
402 md2.plan_id,
403 md2.sr_instance_id), null), 'N/A'),
404 nvl(decode(exp.exception_type,
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,
501 decode(exp.exception_type, 12, null, 13, null, 14, null,
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),
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);
626 l_utilization_rate number;
623 l_end_order_number varchar2(1000);
624 l_department_line_code varchar2(10);
625 l_resource_code varchar2(30);
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,
786 l_sup_project_id ,
783 l_transaction_id,
784 l_order_type_code,
785 l_qty_related_values,
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
942 l_cursor := 'End of launch_workflow';
939 msc_util.msc_debug('Completed:'|| to_char(l_counter -1));
940 retcode := 0;
941
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
1069 and med.exception_detail_id = l_exception_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
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
1234 avalue => compile_designator);
1231 wf_engine.SetItemAttrText( itemtype => item_type,
1232 itemkey => item_key,
1233 aname => 'PLAN_NAME',
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
1359 avalue => end_item_segments);
1356 wf_engine.SetItemAttrText( itemtype => item_type,
1357 itemkey => item_key,
1358 aname => 'END_ITEM_DISPLAY_NAME',
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,
1499 itemkey => item_key,
1496 aname => 'DEPARTMENT_LINE_CODE',
1497 avalue => so_rec.to_org);
1498 wf_engine.SetItemAttrDate( itemtype => item_type,
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
1648
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;
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
1795 return 'MSG_6_REQ_COMP';
1792 if (p_order_type = 1) then
1793 return 'MSG_6_PO_COMP';
1794 elsif (p_order_type = 2) then
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(
1950 null,
1947 'MSC',
1948 'MSCWFRES',
1949 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),
2068 implement_supplier_site_id = NULL,
2065 implement_status_code),
2066 implement_source_org_id = NULL,
2067 implement_supplier_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
2190 ' AND item_key like '''|| to_char(arg_plan_id) || '-%''';
2187 sql_stmt := ' SELECT item_key, item_type ' ||
2188 ' FROM wf_items' || l_db_link ||
2189 ' WHERE item_type in (''MSCEXPWF'',''MRPEXWFS'') '||
2191
2192 OPEN delete_activities_c for sql_stmt;
2193 LOOP
2194
2195 FETCH DELETE_ACTIVITIES_C INTO l_item_key, l_item_type;
2196 EXIT WHEN DELETE_ACTIVITIES_C%NOTFOUND;
2197
2198 msc_util.msc_debug('DELETING dblink:' || l_db_link || ':' || l_item_key ||','||l_item_type);
2199 update
2200 wf_notifications
2201 set end_date = sysdate
2202 where group_id in
2203 (select notification_id
2204 from wf_item_activity_statuses
2205 where item_type = l_item_type
2206 and item_key = l_item_key
2207 union
2208 select notification_id
2209 from wf_item_activity_statuses_h
2210 where item_type = l_item_type
2211 and item_key = l_item_key);
2212
2213 update wf_items
2214 set end_date = sysdate
2215 where item_type = l_item_type
2216 and item_key = l_item_key;
2217
2218 update wf_item_activity_statuses
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_h
2224 set end_date = sysdate
2225 where item_type = l_item_type
2226 and item_key = l_item_key;
2227
2228 wf_purge.total(l_item_type,l_item_key,sysdate);
2229
2230
2231 END LOOP; -- for the itemkey loop
2232 CLOSE delete_activities_c;
2233 end if;
2234 END LOOP; -- for the instance loop
2235 CLOSE instance_c;
2236 commit work;
2237 return;
2238
2239 EXCEPTION
2240 when others then
2241 msc_util.msc_debug('Error in delete activities:'|| to_char(sqlcode) || ':'
2242 || substr(sqlerrm,1,100));
2243
2244 return;
2245 END DeleteActivities;
2246
2247 FUNCTION SupplierCapacity(arg_plan_id in number,
2248 arg_exception_id in number)
2249 return number
2250 IS
2251 total_cap number := 0;
2252 p_partner_id number;
2253 p_site_id number;
2254 p_item_id number;
2255 p_org_id number;
2256 p_inst_id number;
2257 p_cap number;
2258 days_between number;
2259 l_from date;
2260 l_to date;
2261 p_from_date date;
2262 p_to_date date;
2263 p_percent number;
2264 p_uom varchar2(30);
2265 p_puom varchar2(30);
2266 curr_date date;
2267 temp_date date;
2268 suptol SupplierToleranceRecord;
2269 /*
2270 CURSOR uom(partner_id in number,partner_site_id in number,
2271 item_id in number, org_id in number, inst_id in number) IS
2272 SELECT DISTINCT convd.conversion_rate/convs.conversion_rate
2273 FROM msc_uom_conversions convs,msc_uom_conversions convd,
2274 msc_system_items msi, msc_item_suppliers sup
2275 WHERE sup.supplier_id = partner_id
2276 AND sup.supplier_site_id = partner_site_id
2277 AND sup.inventory_item_id = item_id
2278 AND sup.organization_id = org_id
2279 AND sup.sr_instance_id = inst_id
2280 AND sup.plan_id = arg_plan_id
2281 AND sup.using_organization_id = -1
2282 AND msi.plan_id = arg_plan_id
2283 AND msi.inventory_item_id = item_id
2284 AND msi.organization_id = org_id
2285 AND msi.sr_instance_id = inst_id
2286 AND convs.sr_instance_id = inst_id
2287 AND convs.inventory_item_id = 0
2288 AND convs.uom_code = msi.uom_code
2289 AND convd.sr_instance_id = inst_id
2290 AND convd.inventory_item_id = 0
2291 AND convd.uom_code = sup.purchasing_unit_of_measure;
2292 */
2293
2294 CURSOR sup_cap(partner_id in number,partner_site_id in number,
2295 p_from_date in date,p_to_date in date, item_id in number,
2296 p_org_id in number, inst_id in number) IS
2297 SELECT DISTINCT capacity,from_date,NVL(to_date,from_date)
2298 FROM msc_supplier_capacities
2299 WHERE plan_id = arg_plan_id
2300 AND supplier_id = partner_id
2301 AND supplier_site_id = partner_site_id
2302 AND inventory_item_id = item_id
2303 AND organization_id = p_org_id
2304 AND sr_instance_id = inst_id
2305 AND from_date <= NVL(p_to_date,p_from_date)
2306 AND to_date > p_from_date;
2307 BEGIN
2308 -- Get the reference data for future SQL
2309 --dbms_output.put_Line('In procedure');
2310 SELECT DISTINCT from_date,NVL(to_date,from_date),
2311 NVL(utilization_rate,quantity),supplier_id,
2312 supplier_site_id, inventory_item_id, organization_id, sr_instance_id
2313 INTO p_from_date,p_to_date,p_percent,p_partner_id,p_site_id,p_item_id,
2314 p_org_id,p_inst_id
2315 FROM msc_exception_details_v
2316 WHERE plan_id = arg_plan_id
2317 AND exception_id = arg_exception_id;
2318
2319 -- Get the tolerance information
2320 --dbms_output.put_line('Before the tolerance information');
2321 SELECT DISTINCT fence_days, tolerance_percentage
2322 BULK COLLECT INTO suptol.fence,suptol.tolerance
2323 FROM msc_supplier_flex_fences
2327 AND sr_instance_id = p_inst_id
2324 WHERE plan_id = arg_plan_id
2325 AND inventory_item_id = p_item_id
2326 AND organization_id = p_org_id
2328 AND supplier_id = p_partner_id
2329 AND supplier_site_id = p_site_id
2330 ORDER BY fence_days;
2331
2332 -- This obtains the capacity rows
2333 -- For each of them we need to apply the tolerances, to find the actual
2334 -- capacity promised
2335 curr_date := MSC_CALENDAR.NEXT_WORK_DAY(
2336 p_org_id,p_inst_id,MSC_CALENDAR.TYPE_DAILY_BUCKET,
2337 sysdate);
2338 --dbms_output.put_line('p_to_date:'|| to_char(p_to_date,'DD-MON-YYYY'));
2339 --dbms_output.put_line('p_from_date:'|| to_char(p_from_date,'DD-MON-YYYY'));
2340 --dbms_output.put_line('Curr date:' || to_char(curr_date,'DD-MON-YYYY'));
2341 --dbms_output.put_line('p_percent:' || to_char(p_percent));
2342 OPEN sup_cap(p_partner_id,p_site_id,p_from_date,p_to_date,p_item_id,
2343 p_org_id,p_inst_id);
2344 LOOP
2345 FETCH sup_cap INTO p_cap,l_from,l_to;
2346 EXIT WHEN sup_cap%NOTFOUND;
2347 -- Now for each record, calculate the net capacity
2348
2349 --dbms_output.put_line('l_from:' || to_char(l_from,'DD-MON-YYYY'));
2350 --dbms_output.put_line('l_to:' || to_char(l_to,'DD-MON-YYYY'));
2351 --dbms_output.put_line('p_cap:' || to_char(p_cap));
2352
2353 if (p_from_date >= l_from) then
2354 if ((p_from_date - l_to) > 0) then
2355 if ((curr_date - l_from) > 0) then
2356 days_between := l_to - curr_date +1;
2357 else
2358 if (trunc(p_to_date) = trunc(p_from_date)) then
2359 days_between := 1;
2360 else
2361 days_between := l_to - l_from +1;
2362 end if;
2363 end if;
2364 else
2365 if ((curr_date - l_from) > 0) then
2366 days_between := p_from_date - curr_date;
2367 else
2368 if (trunc(p_to_date) = trunc(p_from_date)) then
2369 days_between := 1;
2370 else
2371 days_between := p_from_date - l_from;
2372 end if;
2373 end if;
2374 end if;
2375 --dbms_output.put_line('days_between:' || to_char(days_between));
2376 if (days_between > 0) then
2377 total_cap := total_cap + p_cap * days_between;
2378 end if;
2379 -- Now the tolerance
2380 if (p_from_date > l_to) then
2381 temp_date := l_to +1;
2382 else
2383 temp_date := p_from_date;
2384 end if;
2385 --dbms_output.put_line('temp date:' || to_char(temp_date,'DD-MON-YYYY'));
2386 -- Check tolerances only if they exist
2387 IF (suptol.fence.COUNT > 0) THEN
2388 FOR i in suptol.fence.FIRST..suptol.fence.LAST LOOP
2389 if (l_from > (curr_date + suptol.fence(i))) then
2390 days_between := temp_date - l_from;
2391 else
2392 days_between := temp_date - curr_date - suptol.fence(i);
2393 end if;
2394 if (days_between > 0) then
2395 total_cap := total_cap + p_cap * (suptol.tolerance(i)/100) *
2396 days_between;
2397 temp_date := temp_date - days_between;
2398 end if;
2399 END LOOP;
2400 END IF;
2401 end if; -- (p_from_date > l_from)
2402 --total_cap := total_cap + p_cap;
2403 END LOOP;
2404 CLOSE sup_cap;
2405 -- Note that the capacity is already in the vendor UOM, so we are okay
2406 --dbms_output.put_line('Final total_cap:' || to_char(total_cap));
2407
2408 return(total_cap * p_percent/100.0);
2409 EXCEPTION
2410 WHEN OTHERS THEN
2411 --dbms_output.put_line('Error:'|| to_char(sqlcode) || ':' ||
2412 -- substr(sqlerrm,1,50));
2413 return(0);
2414 END SupplierCapacity;
2415
2416
2417 PROCEDURE IsCallback(itemtype in varchar2,
2418 itemkey in varchar2,
2419 actid in number,
2420 funcmode in varchar2,
2421 resultout out NOCOPY varchar2) is
2422 l_is_callback varchar2(3) :=
2423 wf_engine.GetItemAttrText( itemtype => itemtype,
2424 itemkey => itemkey,
2425 aname => 'IS_CALL_BACK');
2426
2427 BEGIN
2428 if (funcmode = 'RUN') then
2429 resultout := 'COMPLETE:' || l_is_callback;
2430 return;
2431 end if;
2432
2433 if (funcmode = 'CANCEL') then
2434 resultout := 'COMPLETE:';
2435 return;
2436 end if;
2437
2438 if (funcmode = 'TIMEOUT') then
2439 resultout := 'COMPLETE:';
2440 return;
2441 end if;
2442
2443 EXCEPTION
2444
2445 when others then
2446 wf_core.context('MSC_EXP_WF', 'IsCallback', itemtype, itemkey, actid, funcmode);
2447 raise;
2448
2449 END IsCallback;
2450
2451 PROCEDURE SelectSrUsers(itemtype in varchar2,
2452 itemkey in varchar2,
2453 actid in number,
2454 funcmode in varchar2,
2455 resultout out NOCOPY varchar2) is
2456
2457 CURSOR BUYER_C(p_inventory_item_id in number,
2458 p_plan_id in number,
2459 p_org_id in number,
2463 WHERE sys.inventory_item_id = p_inventory_item_id
2460 p_instance in number) IS
2461 SELECT cont.name
2462 FROM msc_partner_contacts cont, msc_system_items sys
2464 AND sys.organization_id = p_org_id
2465 AND sys.sr_instance_id = p_instance
2466 AND sys.plan_id = p_plan_id
2467 AND cont.partner_id = sys.buyer_id
2468 AND cont.sr_instance_id = sys.sr_instance_id
2469 AND cont.partner_type = 4;
2470
2471 CURSOR SUPCNT_C(p_exception_id in number,p_plan_id in number) IS
2472 SELECT name
2473 FROM msc_partner_contacts cont,
2474 msc_exception_details exp,
2475 msc_supplies ms
2476 WHERE exp.exception_detail_id = p_exception_id
2477 AND exp.plan_id = p_plan_id
2478 AND cont.partner_site_id = nvl(exp.supplier_site_id,ms.supplier_site_id)
2479 AND cont.partner_type = 1
2480 AND cont.sr_instance_id = nvl(exp.sr_instance_id,ms.sr_instance_id)
2481 AND ms.plan_id(+) = exp.plan_id
2482 AND ms.transaction_id(+) = exp.number1;
2483
2484 CURSOR SALESREP_C(p_exception_id in number,
2485 p_plan_id in number) IS
2486 SELECT so.salesrep_id
2487 FROM msc_sales_orders so,
2488 msc_demands mgr,
2489 msc_exception_details exp
2490 WHERE so.sales_order_number = mgr.order_number
2491 AND so.sr_instance_id = mgr.sr_instance_id
2492 AND mgr.plan_id = exp.plan_id
2493 AND mgr.demand_id = exp.number1
2494 AND exp.exception_detail_id = p_exception_id
2495 AND exp.plan_id = p_plan_id;
2496
2497 CURSOR SALESREP_C2(p_demand_id in number,
2498 p_plan_id in number) IS
2499 SELECT so.salesrep_id
2500 FROM msc_sales_orders so,
2501 msc_demands mgr
2502 WHERE so.sales_order_number = mgr.order_number
2503 AND so.sr_instance_id = mgr.sr_instance_id
2504 AND mgr.plan_id = p_plan_id
2505 AND mgr.demand_id = p_demand_id;
2506
2507 CURSOR CUSTCNT_C(p_exception_id in number, p_plan_id in number) IS
2508 SELECT name
2509 FROM msc_partner_contacts cont, msc_demands mgr,
2510 msc_exception_details exp
2511 WHERE exp.exception_detail_id = p_exception_id
2512 AND exp.plan_id = p_plan_id
2513 AND cont.partner_id = mgr.customer_id
2514 AND cont.partner_site_id = mgr.customer_site_id
2515 AND cont.partner_type = 2
2516 and cont.sr_instance_id = mgr.sr_instance_id
2517 AND mgr.demand_id = exp.number1
2518 AND mgr.plan_id = exp.plan_id;
2519
2520 CURSOR CUSTCNT_C2(p_demand_id in number, p_plan_id in number) IS
2521 SELECT name
2522 FROM msc_partner_contacts cont, msc_demands mgr
2523 where cont.partner_id = mgr.customer_id
2524 AND cont.partner_site_id = mgr.customer_site_id
2525 AND cont.partner_type = 2
2526 and cont.sr_instance_id = mgr.sr_instance_id
2527 AND mgr.demand_id = p_demand_id
2528 AND mgr.plan_id = p_plan_id;
2529
2530
2531
2532 l_exception_type number :=
2533 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2534 itemkey => itemkey,
2535 aname => 'EXCEPTION_TYPE_ID');
2536
2537 l_demand_id number :=
2538 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2539 itemkey => itemkey,
2540 aname => 'TRANSACTION_ID');
2541
2542 l_order_type number :=
2543 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2544 itemkey => itemkey,
2545 aname => 'ORDER_TYPE_CODE');
2546
2547 l_inventory_item_id number :=
2548 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2549 itemkey => itemkey,
2550 aname => 'INVENTORY_ITEM_ID');
2551
2552 l_plan_id number :=
2553 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2554 itemkey => itemkey,
2555 aname => 'PLAN_ID');
2556
2557 l_org_id number :=
2558 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2559 itemkey => itemkey,
2560 aname => 'ORGANIZATION_ID');
2561
2562 l_instance_id number :=
2563 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2564 itemkey => itemkey,
2565 aname => 'INSTANCE_ID');
2566
2567 l_from_project_number varchar2(100) :=
2568 wf_engine.GetItemAttrText( itemtype => itemtype,
2569 itemkey => itemkey,
2570 aname => 'PROJECT_NUMBER');
2571
2572 l_to_project_number varchar2(100) :=
2573 wf_engine.GetItemAttrText( itemtype => itemtype,
2574 itemkey => itemkey,
2575 aname => 'TO_PROJECT_NUMBER');
2576
2577 l_from_task_number varchar2(100) :=
2578 wf_engine.GetItemAttrText( itemtype => itemtype,
2579 itemkey => itemkey,
2580 aname => 'TASK_NUMBER');
2581
2585 aname => 'TO_TASK_NUMBER');
2582 l_to_task_number varchar2(100) :=
2583 wf_engine.GetItemAttrText( itemtype => itemtype,
2584 itemkey => itemkey,
2586
2587 l_exception_id number :=
2588 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2589 itemkey => itemkey,
2590 aname => 'EXCEPTION_ID');
2591
2592 l_buyer varchar2(30);
2593 l_supcnt varchar2(30);
2594 l_salesrep varchar2(30);
2595 l_from_prj_mgr varchar2(100);
2596 l_to_prj_mgr varchar2(100);
2597 l_custcnt varchar2(30);
2598
2599 CURSOR PRJ_MGR_C(p_project_number in varchar2) IS
2600 SELECT proj.manager_contact
2601 FROM msc_projects proj
2602 where proj.project_number = p_project_number
2603 and proj.organization_id = l_org_id
2604 and proj.sr_instance_id = l_instance_id
2605 AND proj.plan_id = -1;
2606
2607 CURSOR TSK_MGR_C(p_project_number in varchar2, p_task_number in varchar2) IS
2608 SELECT NVL(tasks.manager_contact,proj.manager_contact)
2609 FROM msc_projects proj, msc_project_tasks tasks
2610 WHERE tasks.task_number = p_task_number
2611 AND proj.project_id = tasks.project_id
2612 AND proj.project_number = p_project_number
2613 and proj.organization_id = tasks.organization_id
2614 and proj.sr_instance_id = tasks.sr_instance_id
2615 AND proj.plan_id = tasks.plan_id
2616 and proj.organization_id = l_org_id
2617 and proj.sr_instance_id = l_instance_id
2618 AND proj.plan_id = -1;
2619
2620 BEGIN
2621 if (funcmode = 'RUN') then
2622 if l_exception_type in (28, 37) OR
2623 (l_exception_type in (6, 7, 8, 9, 10) and l_order_type in (1,2)) then
2624
2625 OPEN BUYER_C(l_inventory_item_id, l_plan_id, l_org_id,l_instance_id);
2626 FETCH BUYER_C INTO l_buyer;
2627 CLOSE BUYER_C;
2628
2629 --l_buyer := 'MFG';
2630
2631 if (l_buyer is not null) then
2632 wf_engine.setItemAttrText( itemtype => itemtype,
2633 itemkey => itemkey,
2634 aname => 'BUYER',
2635 avalue => l_buyer);
2636 END IF;
2637 END IF;
2638
2639 if (l_exception_type in (6, 7, 8, 9, 10) and l_order_type = 1) or
2640 l_exception_type in (37, 28) then
2641
2642 OPEN SUPCNT_C(l_exception_id,l_plan_id);
2643 FETCH SUPCNT_C INTO l_supcnt;
2644 CLOSE SUPCNT_C;
2645
2646 -- l_supcnt := 'MFG';
2647
2648 if (l_supcnt is not null) then
2649 wf_engine.setItemAttrText( itemtype => itemtype,
2650 itemkey => itemkey,
2651 aname => 'SUPCNT',
2652 avalue => l_supcnt);
2653 END IF;
2654 END IF;
2655
2656 if (l_exception_type in (13, 15, 24, 25, 49, 70)) then
2657 if l_exception_type = 70 then
2658 OPEN SALESREP_C2(l_demand_id,l_plan_id);
2659 FETCH SALESREP_C2 INTO l_salesrep;
2660 CLOSE SALESREP_C2;
2661 else
2662 OPEN SALESREP_C(l_exception_id,l_plan_id);
2663 FETCH SALESREP_C INTO l_salesrep;
2664 CLOSE SALESREP_C;
2665 end if;
2666
2667 -- l_salesrep := '1208';
2668 if (l_salesrep is not null) then
2669 wf_engine.setItemAttrText( itemtype => itemtype,
2670 itemkey => itemkey,
2671 aname => 'SALESREP',
2672 avalue => l_salesrep);
2673 END IF;
2674
2675 if l_exception_type = 70 then
2676 OPEN CUSTCNT_C2(l_demand_id,l_plan_id);
2677 FETCH CUSTCNT_C2 INTO l_custcnt;
2678 CLOSE CUSTCNT_C2;
2679 else
2680 OPEN CUSTCNT_C(l_exception_id,l_plan_id);
2681 FETCH CUSTCNT_C INTO l_custcnt;
2682 CLOSE CUSTCNT_C;
2683 end if;
2684 -- l_custcnt := 'MFG';
2685
2686 if (l_custcnt is not null) then
2687 wf_engine.setItemAttrText( itemtype => itemtype,
2688 itemkey => itemkey,
2689 aname => 'CUSTCNT',
2690 avalue => l_custcnt);
2691 END IF;
2692 end if;
2693
2694 if (l_exception_type in (17, 18, 19)) then
2695 if l_from_project_number is not null and
2696 l_from_task_number is not null then
2697 OPEN TSK_MGR_C(l_from_project_number,l_from_task_number);
2698 FETCH TSK_MGR_C INTO l_from_prj_mgr;
2699 CLOSE TSK_MGR_C;
2700 elsif l_from_project_number is not null then
2701
2702 OPEN PRJ_MGR_C(l_from_project_number);
2703 FETCH PRJ_MGR_C INTO l_from_prj_mgr;
2704 CLOSE PRJ_MGR_C;
2705 end if;
2706 --l_from_prj_mgr := 'MFG';
2707
2708 if (l_from_prj_mgr is not null) then
2709 wf_engine.setItemAttrText( itemtype => itemtype,
2710 itemkey => itemkey,
2711 aname => 'FROM_PRJ_MGR',
2712 avalue => l_from_prj_mgr);
2716 l_to_task_number is not null then
2713 END IF;
2714
2715 if l_to_project_number is not null and
2717 OPEN TSK_MGR_C(l_to_project_number,l_to_task_number);
2718 FETCH TSK_MGR_C INTO l_to_prj_mgr;
2719 CLOSE TSK_MGR_C;
2720 elsif l_to_project_number is not null then
2721
2722 OPEN PRJ_MGR_C(l_to_project_number);
2723 FETCH PRJ_MGR_C INTO l_to_prj_mgr;
2724 CLOSE PRJ_MGR_C;
2725 end if;
2726
2727 --l_to_prj_mgr := 'MFG';
2728 if (l_to_prj_mgr is not null) then
2729 wf_engine.setItemAttrText( itemtype => itemtype,
2730 itemkey => itemkey,
2731 aname => 'TO_PRJ_MGR',
2732 avalue => l_to_prj_mgr);
2733 END IF;
2734
2735 end if;
2736
2737 resultout := 'COMPLETE:';
2738 return;
2739 end if;
2740
2741 if (funcmode = 'CANCEL') then
2742 resultout := 'COMPLETE:';
2743 return;
2744 end if;
2745
2746 if (funcmode = 'TIMEOUT') then
2747 resultout := 'COMPLETE:';
2748 return;
2749 end if;
2750
2751 EXCEPTION
2752
2753 when others then
2754 wf_core.context('MSC_EXP_WF', 'IsCallback', itemtype, itemkey, actid, funcmode);
2755 raise;
2756
2757 END SelectSrUsers;
2758
2759 PROCEDURE CheckBuyer(itemtype in varchar2,
2760 itemkey in varchar2,
2761 actid in number,
2762 funcmode in varchar2,
2763 resultout out NOCOPY varchar2) is
2764
2765 l_planner varchar2(20) :=
2766 wf_engine.GetItemAttrText( itemtype => itemtype,
2767 itemkey => itemkey,
2768 aname => 'PLANNER');
2769 l_buyer varchar2(50) :=
2770 wf_engine.GetItemAttrText( itemtype => itemtype,
2771 itemkey => itemkey,
2772 aname => 'BUYER');
2773 BEGIN
2774 if (funcmode = 'RUN') then
2775 if (l_buyer is null) then
2776 resultout := 'COMPLETE:NOT_FOUND';
2777 return;
2778 /*
2779 elsif l_buyer = l_planner then
2780 resultout := 'COMPLETE:IS_PLANNER';
2781 return;
2782 */
2783 else
2784 resultout := 'COMPLETE:FOUND';
2785 return;
2786 end if;
2787 end if;
2788 if (funcmode = 'CANCEL') then
2789 resultout := 'COMPLETE:';
2790 return;
2791 end if;
2792
2793 if (funcmode = 'TIMEOUT') then
2794 resultout := 'COMPLETE:';
2795 return;
2796 end if;
2797 END CheckBuyer;
2798
2799 -- call back a wf process at destition instance for completion
2800 PROCEDURE StartSrWF(itemtype in varchar2,
2801 itemkey in varchar2,
2802 actid in number,
2803 funcmode in varchar2,
2804 resultout out NOCOPY varchar2) is
2805
2806 l_db_link varchar2(30) :=
2807 wf_engine.GetItemAttrText( itemtype => itemtype,
2808 itemkey => itemkey,
2809 aname => 'DB_LINK');
2810
2811 l_exception_type number :=
2812 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2813 itemkey => itemkey,
2814 aname => 'EXCEPTION_TYPE_ID');
2815
2816 l_transaction_id number :=
2817 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2818 itemkey => itemkey,
2819 aname => 'TRANSACTION_ID');
2820
2821 l_order_type number :=
2822 wf_engine.GetItemAttrNumber( itemtype => itemtype,
2823 itemkey => itemkey,
2824 aname => 'ORDER_TYPE_CODE');
2825
2826 l_sr_item_type varchar2(50) := 'MRPEXWFS';
2827 l_sr_item_key varchar2(100);
2828 l_text varchar2(100);
2829 l_numb number;
2830 l_date Date;
2831 l_sr_process varchar2(50);
2832 sql_stmt varchar2(2000);
2833 p_request_id number :=0;
2834 p_result boolean;
2835 BEGIN
2836 if (funcmode = 'RUN') then
2837 -- now find out sr process, and start it.
2838 if (l_exception_type in (28, 37)) then
2839 l_sr_process := 'MSC_SUPCAP_SR_PROCESS';
2840 elsif (l_exception_type in (6, 7, 8, 9, 10)) then
2841 if (l_order_type = 1) then
2842 l_sr_process := 'MSC_PO_SR_PROCESS';
2843 else
2844 l_sr_process := 'MSC_REQ_SR_PROCESS';
2845 END IF;
2846 elsif (l_exception_type in (13, 15, 24, 25, 49, 70)) then
2847 l_sr_process := 'MSC_SO_SR_PROCESS';
2848 elsif (l_exception_type in (17, 18, 19)) then
2849 l_sr_process := 'MSC_PRJ_SR_PROCESS';
2850 end if;
2851 l_sr_item_key := itemkey || '-' || l_sr_process;
2852
2853 sql_stmt := 'begin wf_engine.CreateProcess' || l_db_link ||
2854 '( itemtype => :l_itemtype,' ||
2855 'itemkey => :l_itemkey, ' ||
2856 'process => :l_process);end;';
2857 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type, l_sr_item_key,
2861 -- we could only copy those insterested attributes,
2858 l_sr_process;
2859
2860 -- now copy attributes to sr wf process
2862 -- but we copy all for debug purpose.
2863
2864 -- ORDER_TYPE_CODE
2865 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
2866 '(itemtype => :item_type,' ||
2867 ' itemkey => :item_key,' ||
2868 ' aname => ''ORDER_TYPE_CODE'',' ||
2869 ' avalue => :l_numb);end;';
2870 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_order_type;
2871
2872 -- EXCEPTION_TYPE_ID.
2873 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
2874 '(itemtype => :item_type,' ||
2875 ' itemkey => :item_key,' ||
2876 ' aname => ''EXCEPTION_TYPE_ID'',' ||
2877 ' avalue => :l_numb);end;';
2878 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_exception_type;
2879
2880
2881 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
2882 '(itemtype => :item_type,' ||
2883 ' itemkey => :item_key,' ||
2884 ' aname => ''TRANSACTION_ID'',' ||
2885 ' avalue => :l_numb);end;';
2886 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_transaction_id;
2887
2888 -- APPS_PS_DBLINK
2889 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2890 '(itemtype => :item_type,' ||
2891 ' itemkey => :item_key,' ||
2892 ' aname => ''APPS_PS_DBLINK'',' ||
2893 ' avalue => :l_text);end;';
2894 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_db_link;
2895
2896 --BUYER. we don't need to set back BUYER, set it for debug
2897 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2898 itemkey => itemkey,
2899 aname => 'BUYER');
2900 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2901 '(itemtype => :item_type,' ||
2902 ' itemkey => :item_key,' ||
2903 ' aname => ''BUYER'',' ||
2904 ' avalue => :l_text);end;';
2905 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2906
2907 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2908 itemkey => itemkey,
2909 aname => 'SALESREP');
2910 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2911 '(itemtype => :item_type,' ||
2912 ' itemkey => :item_key,' ||
2913 ' aname => ''SALESREP'',' ||
2914 ' avalue => :l_text);end;';
2915 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2916
2917 -- we don't need to set back CUSTCNT, for debug only
2918 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2919 itemkey => itemkey,
2920 aname => 'CUSTCNT');
2921 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2922 '(itemtype => :item_type,' ||
2923 ' itemkey => :item_key,' ||
2924 ' aname => ''CUSTCNT'',' ||
2925 ' avalue => :l_text);end;';
2926 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2927
2928 -- customer_name
2929 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2930 itemkey => itemkey,
2931 aname => 'CUSTOMER_NAME');
2932 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2933 '(itemtype => :item_type,' ||
2934 ' itemkey => :item_key,' ||
2935 ' aname => ''CUSTOMER_NAME'',' ||
2936 ' avalue => :l_text);end;';
2937 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2938
2939 -- customer_ID.
2940 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2941 itemkey => itemkey,
2942 aname => 'CUSTOMER_ID');
2943 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
2944 '(itemtype => :item_type,' ||
2945 ' itemkey => :item_key,' ||
2946 ' aname => ''CUSTOMER_ID'',' ||
2947 ' avalue => :l_numb);end;';
2948 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
2949
2950
2951 -- Days_compressed
2952 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2953 itemkey => itemkey,
2954 aname => 'DAYS_COMPRESSED');
2955 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
2956 '(itemtype => :item_type,' ||
2957 ' itemkey => :item_key,' ||
2958 ' aname => ''DAYS_COMPRESSED'',' ||
2959 ' avalue => :l_numb);end;';
2960 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
2961
2962 -- APPS_PS_DBLINK. we may not need.
2963 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2964 itemkey => itemkey,
2965 aname => 'APPS_PS_DBLINK');
2969 ' aname => ''APPS_PS_DBLINK'',' ||
2966 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2967 '(itemtype => :item_type,' ||
2968 ' itemkey => :item_key,' ||
2970 ' avalue => :l_text);end;';
2971 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2972
2973 -- DEPARTMENT_LINE_CODE.
2974 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2975 itemkey => itemkey,
2976 aname => 'DEPARTMENT_LINE_CODE');
2977 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
2978 '(itemtype => :item_type,' ||
2979 ' itemkey => :item_key,' ||
2980 ' aname => ''DEPARTMENT_LINE_CODE'',' ||
2981 ' avalue => :l_text);end;';
2982 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
2983
2984 -- due_date.
2985 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
2986 itemkey => itemkey,
2987 aname => 'DUE_DATE');
2988 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
2989 '(itemtype => :item_type,' ||
2990 ' itemkey => :item_key,' ||
2991 ' aname => ''DUE_DATE'',' ||
2992 ' avalue => :l_date);end;';
2993 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
2994
2995 -- end_item_display_name.
2996 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
2997 itemkey => itemkey,
2998 aname => 'END_ITEM_DISPLAY_NAME');
2999 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3000 '(itemtype => :item_type,' ||
3001 ' itemkey => :item_key,' ||
3002 ' aname => ''END_ITEM_DISPLAY_NAME'',' ||
3003 ' avalue => :l_text);end;';
3004 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3005
3006 -- end_item_description
3007
3008 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3009 itemkey => itemkey,
3010 aname => 'END_ITEM_DESCRIPTION');
3011 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3012 '(itemtype => :item_type,' ||
3013 ' itemkey => :item_key,' ||
3014 ' aname => ''END_ITEM_DESCRIPTION'',' ||
3015 ' avalue => :l_text);end;';
3016 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3017
3018
3019 --END_ORDER_NUMBER
3020 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3021 itemkey => itemkey,
3022 aname => 'END_ORDER_NUMBER');
3023 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3024 '(itemtype => :item_type,' ||
3025 ' itemkey => :item_key,' ||
3026 ' aname => ''END_ORDER_NUMBER'',' ||
3027 ' avalue => :l_text);end;';
3028 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3029
3030 -- EXCEPTION_DESCRIPTION
3031 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3032 itemkey => itemkey,
3033 aname => 'EXCEPTION_DESCRIPTION');
3034 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3035 '(itemtype => :item_type,' ||
3036 ' itemkey => :item_key,' ||
3037 ' aname => ''EXCEPTION_DESCRIPTION'',' ||
3038 ' avalue => :l_text);end;';
3039 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3040
3041 --EXCEPTION_ID
3042 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3043 itemkey => itemkey,
3044 aname => 'EXCEPTION_ID');
3045 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3046 '(itemtype => :item_type,' ||
3047 ' itemkey => :item_key,' ||
3048 ' aname => ''EXCEPTION_ID'',' ||
3049 ' avalue => :l_numb);end;';
3050 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3051
3052 -- FROM_DATE
3053 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3054 itemkey => itemkey,
3055 aname => 'FROM_DATE');
3056 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3057 '(itemtype => :item_type,' ||
3058 ' itemkey => :item_key,' ||
3059 ' aname => ''FROM_DATE'',' ||
3060 ' avalue => :l_date);end;';
3061 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3062
3063 -- FROM_PRJ_MGR
3064 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3065 itemkey => itemkey,
3066 aname => 'FROM_PRJ_MGR');
3067 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3068 '(itemtype => :item_type,' ||
3069 ' itemkey => :item_key,' ||
3070 ' aname => ''FROM_PRJ_MGR'',' ||
3071 ' avalue => :l_text);end;';
3072 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3073
3074 --INSTANCE_ID
3078 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3075 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3076 itemkey => itemkey,
3077 aname => 'INSTANCE_ID');
3079 '(itemtype => :item_type,' ||
3080 ' itemkey => :item_key,' ||
3081 ' aname => ''INSTANCE_ID'',' ||
3082 ' avalue => :l_numb);end;';
3083 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3084
3085 --INVENTORY_ITEM_ID
3086 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3087 itemkey => itemkey,
3088 aname => 'INVENTORY_ITEM_ID');
3089 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3090 '(itemtype => :item_type,' ||
3091 ' itemkey => :item_key,' ||
3092 ' aname => ''INVENTORY_ITEM_ID'',' ||
3093 ' avalue => :l_numb);end;';
3094 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3095
3096 -- ITEM_DISPLAY_NAME
3097 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3098 itemkey => itemkey,
3099 aname => 'ITEM_DISPLAY_NAME');
3100 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3101 '(itemtype => :item_type,' ||
3102 ' itemkey => :item_key,' ||
3103 ' aname => ''ITEM_DISPLAY_NAME'',' ||
3104 ' avalue => :l_text);end;';
3105 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3106
3107 -- ITEM DESCRIPTION
3108
3109 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3110 itemkey => itemkey,
3111 aname => 'ITEM_DESCRIPTION');
3112 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3113 '(itemtype => :item_type,' ||
3114 ' itemkey => :item_key,' ||
3115 ' aname => ''ITEM_DESCRIPTION'',' ||
3116 ' avalue => :l_text);end;';
3117 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3118
3119
3120 -- LOT_NUMBER
3121 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3122 itemkey => itemkey,
3123 aname => 'LOT_NUMBER');
3124 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3125 '(itemtype => :item_type,' ||
3126 ' itemkey => :item_key,' ||
3127 ' aname => ''LOT_NUMBER'',' ||
3128 ' avalue => :l_text);end;';
3129 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3130
3131 -- ORDER_NUMBER
3132 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3133 itemkey => itemkey,
3134 aname => 'ORDER_NUMBER');
3135 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3136 '(itemtype => :item_type,' ||
3137 ' itemkey => :item_key,' ||
3138 ' aname => ''ORDER_NUMBER'',' ||
3139 ' avalue => :l_text);end;';
3140 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3141
3142 -- ORGANIZATION_CODE
3143 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3144 itemkey => itemkey,
3145 aname => 'ORGANIZATION_CODE');
3146 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3147 '(itemtype => :item_type,' ||
3148 ' itemkey => :item_key,' ||
3149 ' aname => ''ORGANIZATION_CODE'',' ||
3150 ' avalue => :l_text);end;';
3151 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3152
3153 --ORGANIZATION_ID
3154 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3155 itemkey => itemkey,
3156 aname => 'ORGANIZATION_ID');
3157 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3158 '(itemtype => :item_type,' ||
3159 ' itemkey => :item_key,' ||
3160 ' aname => ''ORGANIZATION_ID'',' ||
3161 ' avalue => :l_numb);end;';
3162 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3163
3164 --PLAN_ID
3165 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3166 itemkey => itemkey,
3167 aname => 'PLAN_ID');
3168 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3169 '(itemtype => :item_type,' ||
3170 ' itemkey => :item_key,' ||
3171 ' aname => ''PLAN_ID'',' ||
3172 ' avalue => :l_numb);end;';
3173 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3174
3175 -- PLAN_NAME
3176 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3177 itemkey => itemkey,
3178 aname => 'PLAN_NAME');
3179 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3180 '(itemtype => :item_type,' ||
3181 ' itemkey => :item_key,' ||
3182 ' aname => ''PLAN_NAME'',' ||
3183 ' avalue => :l_text);end;';
3184 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3185
3189 aname => 'PLANNER');
3186 -- PLANNER
3187 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3188 itemkey => itemkey,
3190 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3191 '(itemtype => :item_type,' ||
3192 ' itemkey => :item_key,' ||
3193 ' aname => ''PLANNER'',' ||
3194 ' avalue => :l_text);end;';
3195 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3196
3197 -- PLANNING_GROUP
3198 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3199 itemkey => itemkey,
3200 aname => 'PLANNING_GROUP');
3201 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3202 '(itemtype => :item_type,' ||
3203 ' itemkey => :item_key,' ||
3204 ' aname => ''PLANNING_GROUP'',' ||
3205 ' avalue => :l_text);end;';
3206 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3207
3208 -- PROJECT_NUMBER
3209 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3210 itemkey => itemkey,
3211 aname => 'PROJECT_NUMBER');
3212 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3213 '(itemtype => :item_type,' ||
3214 ' itemkey => :item_key,' ||
3215 ' aname => ''PROJECT_NUMBER'',' ||
3216 ' avalue => :l_text);end;';
3217 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3218
3219 --PRE_PROCESSING_LEAD_TIME
3220 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3221 itemkey => itemkey,
3222 aname => 'PRE_PRSNG_LEAD_TIME');
3223 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3224 '(itemtype => :item_type,' ||
3225 ' itemkey => :item_key,' ||
3226 ' aname => ''PRE_PRSNG_LEAD_TIME'',' ||
3227 ' avalue => :l_text);end;';
3228 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3229
3230 --PROCESSING_LEAD_TIME
3231 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3232 itemkey => itemkey,
3233 aname => 'PRSNG_LEAD_TIME');
3234 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3235 '(itemtype => :item_type,' ||
3236 ' itemkey => :item_key,' ||
3237 ' aname => ''PRSNG_LEAD_TIME'',' ||
3238 ' avalue => :l_text);end;';
3239 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3240
3241 --POST_PROCESSING_LEAD_TIME
3242 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3243 itemkey => itemkey,
3244 aname => 'POST_PRSNG_LEAD_TIME');
3245 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3246 '(itemtype => :item_type,' ||
3247 ' itemkey => :item_key,' ||
3248 ' aname => ''POST_PRSNG_LEAD_TIME'',' ||
3249 ' avalue => :l_text);end;';
3250 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3251
3252
3253
3254 -- QUANTITY
3255 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3256 itemkey => itemkey,
3257 aname => 'QUANTITY');
3258 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3259 '(itemtype => :item_type,' ||
3260 ' itemkey => :item_key,' ||
3261 ' aname => ''QUANTITY'',' ||
3262 ' avalue => :l_text);end;';
3263 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3264
3265 -- RESOURCE_CODE
3266 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3267 itemkey => itemkey,
3268 aname => 'RESOURCE_CODE');
3269 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3270 '(itemtype => :item_type,' ||
3271 ' itemkey => :item_key,' ||
3272 ' aname => ''RESOURCE_CODE'',' ||
3273 ' avalue => :l_text);end;';
3274 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3275
3276 -- SUPCNT
3277 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3278 itemkey => itemkey,
3279 aname => 'SUPCNT');
3280 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3281 '(itemtype => :item_type,' ||
3282 ' itemkey => :item_key,' ||
3283 ' aname => ''SUPCNT'',' ||
3284 ' avalue => :l_text);end;';
3285 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3286
3287 --SUPPLIER_ID
3288 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3289 itemkey => itemkey,
3290 aname => 'SUPPLIER_ID');
3291 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3292 '(itemtype => :item_type,' ||
3293 ' itemkey => :item_key,' ||
3294 ' aname => ''SUPPLIER_ID'',' ||
3295 ' avalue => :l_numb);end;';
3299 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3296 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3297
3298 -- SUPPLIER_NAME
3300 itemkey => itemkey,
3301 aname => 'SUPPLIER_NAME');
3302 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3303 '(itemtype => :item_type,' ||
3304 ' itemkey => :item_key,' ||
3305 ' aname => ''SUPPLIER_NAME'',' ||
3306 ' avalue => :l_text);end;';
3307 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3308
3309 -- SUPPLIER_SITE_CODE
3310 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3311 itemkey => itemkey,
3312 aname => 'SUPPLIER_SITE_CODE');
3313 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3314 '(itemtype => :item_type,' ||
3315 ' itemkey => :item_key,' ||
3316 ' aname => ''SUPPLIER_SITE_CODE'',' ||
3317 ' avalue => :l_text);end;';
3318 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3319
3320 --SUPPLIER_SITE_ID
3321 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3322 itemkey => itemkey,
3323 aname => 'SUPPLIER_SITE_ID');
3324 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3325 '(itemtype => :item_type,' ||
3326 ' itemkey => :item_key,' ||
3327 ' aname => ''SUPPLIER_SITE_ID'',' ||
3328 ' avalue => :l_numb);end;';
3329 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3330
3331 -- SUPPLy_TYPE
3332 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3333 itemkey => itemkey,
3334 aname => 'SUPPLY_TYPE');
3335 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3336 '(itemtype => :item_type,' ||
3337 ' itemkey => :item_key,' ||
3338 ' aname => ''SUPPLY_TYPE'',' ||
3339 ' avalue => :l_text);end;';
3340 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3341
3342 -- TASK_NUMBER
3343 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3344 itemkey => itemkey,
3345 aname => 'TASK_NUMBER');
3346 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3347 '(itemtype => :item_type,' ||
3348 ' itemkey => :item_key,' ||
3349 ' aname => ''TASK_NUMBER'',' ||
3350 ' avalue => :l_text);end;';
3351 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3352
3353 -- TO_DATE
3354 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3355 itemkey => itemkey,
3356 aname => 'TO_DATE');
3357 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3358 '(itemtype => :item_type,' ||
3359 ' itemkey => :item_key,' ||
3360 ' aname => ''TO_DATE'',' ||
3361 ' avalue => :l_date);end;';
3362 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3363
3364 -- TO_PRJ_MGR
3365 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3366 itemkey => itemkey,
3367 aname => 'TO_PRJ_MGR');
3368 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3369 '(itemtype => :item_type,' ||
3370 ' itemkey => :item_key,' ||
3371 ' aname => ''TO_PRJ_MGR'',' ||
3372 ' avalue => :l_text);end;';
3373 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3374
3375 -- TO_PROJECT_NUMBER
3376 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3377 itemkey => itemkey,
3378 aname => 'TO_PROJECT_NUMBER');
3379 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3380 '(itemtype => :item_type,' ||
3381 ' itemkey => :item_key,' ||
3382 ' aname => ''TO_PROJECT_NUMBER'',' ||
3383 ' avalue => :l_text);end;';
3384 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3385
3386 -- TO_TASK_NUMBER
3387 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3388 itemkey => itemkey,
3389 aname => 'TO_TASK_NUMBER');
3390 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3391 '(itemtype => :item_type,' ||
3392 ' itemkey => :item_key,' ||
3393 ' aname => ''TO_TASK_NUMBER'',' ||
3394 ' avalue => :l_text);end;';
3395 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3396
3397 -- URL1
3398 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
3399 itemkey => itemkey,
3400 aname => 'URL1');
3401 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
3402 '(itemtype => :item_type,' ||
3403 ' itemkey => :item_key,' ||
3404 ' aname => ''URL1'',' ||
3405 ' avalue => :l_text);end;';
3409 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3406 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_text;
3407
3408 -- UTILIZATION_RATE
3410 itemkey => itemkey,
3411 aname => 'UTILIZATION_RATE');
3412 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3413 '(itemtype => :item_type,' ||
3414 ' itemkey => :item_key,' ||
3415 ' aname => ''UTILIZATION_RATE'',' ||
3416 ' avalue => :l_numb);end;';
3417 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3418
3419
3420 -- CAPACITY_REQUIREMENT
3421 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3422 itemkey => itemkey,
3423 aname => 'CAPACITY_REQUIREMENT');
3424 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3425 '(itemtype => :item_type,' ||
3426 ' itemkey => :item_key,' ||
3427 ' aname => ''CAPACITY_REQUIREMENT'',' ||
3428 ' avalue => :l_numb);end;';
3429 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3430
3431
3432 --REQUIRED_QUANTITY
3433 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3434 itemkey => itemkey,
3435 aname => 'REQUIRED_QUANTITY');
3436 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3437 '(itemtype => :item_type,' ||
3438 ' itemkey => :item_key,' ||
3439 ' aname => ''REQUIRED_QUANTITY'',' ||
3440 ' avalue => :l_numb);end;';
3441 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3442
3443
3444 --PROJECTED_AVAILABLE_BALANCE
3445 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3446 itemkey => itemkey,
3447 aname => 'PROJECTED_AVAILABLE_BALANCE');
3448 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3449 '(itemtype => :item_type,' ||
3450 ' itemkey => :item_key,' ||
3451 ' aname => ''PROJECTED_AVAILABLE_BALANCE'',' ||
3452 ' avalue => :l_numb);end;';
3453 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3454
3455 --AVAILABLE_QUANTITY
3456 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3457 itemkey => itemkey,
3458 aname => 'AVAILABLE_QUANTITY');
3459 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3460 '(itemtype => :item_type,' ||
3461 ' itemkey => :item_key,' ||
3462 ' aname => ''AVAILABLE_QUANTITY'',' ||
3463 ' avalue => :l_numb);end;';
3464 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3465
3466 --QTY_RELATED_VALUES
3467 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
3468 itemkey => itemkey,
3469 aname => 'QTY_RELATED_VALUES');
3470 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
3471 '(itemtype => :item_type,' ||
3472 ' itemkey => :item_key,' ||
3473 ' aname => ''QTY_RELATED_VALUES'',' ||
3474 ' avalue => :l_numb);end;';
3475 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_numb;
3476
3477
3478
3479 if (l_exception_type =70 ) then
3480 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3481 itemkey => itemkey,
3482 aname => 'DATE1');
3483 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3484 '(itemtype => :item_type,' ||
3485 ' itemkey => :item_key,' ||
3486 ' aname => ''DATE1'',' ||
3487 ' avalue => :l_date);end;';
3488 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3489
3490 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3491 itemkey => itemkey,
3492 aname => 'DATE2');
3493 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3494 '(itemtype => :item_type,' ||
3495 ' itemkey => :item_key,' ||
3496 ' aname => ''DATE2'',' ||
3497 ' avalue => :l_date);end;';
3498 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3499 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3500 itemkey => itemkey,
3501 aname => 'DATE3');
3502 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3503 '(itemtype => :item_type,' ||
3504 ' itemkey => :item_key,' ||
3505 ' aname => ''DATE3'',' ||
3506 ' avalue => :l_date);end;';
3507 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3508 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3509 itemkey => itemkey,
3510 aname => 'DATE4');
3511 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3512 '(itemtype => :item_type,' ||
3513 ' itemkey => :item_key,' ||
3514 ' aname => ''DATE4'',' ||
3515 ' avalue => :l_date);end;';
3519 aname => 'DATE5');
3516 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3517 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3518 itemkey => itemkey,
3520 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3521 '(itemtype => :item_type,' ||
3522 ' itemkey => :item_key,' ||
3523 ' aname => ''DATE5'',' ||
3524 ' avalue => :l_date);end;';
3525 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3526 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
3527 itemkey => itemkey,
3528 aname => 'DATE6');
3529 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
3530 '(itemtype => :item_type,' ||
3531 ' itemkey => :item_key,' ||
3532 ' aname => ''DATE6'',' ||
3533 ' avalue => :l_date);end;';
3534 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key,l_date;
3535 end if; -- end of if (l_exception_type = 70)
3536 -- now start wf process at destination instance
3537
3538 sql_stmt := 'begin wf_engine.StartProcess'|| l_db_link ||
3539 '( itemtype => :itemtype,' ||
3540 ' itemkey => :itemkey);end;';
3541 EXECUTE IMMEDIATE sql_stmt USING l_sr_item_type,l_sr_item_key;
3542
3543 if l_db_link is not null and l_db_link <> ' ' then
3544 begin
3545 commit;
3546 execute immediate 'alter session close database link '||
3547 ltrim(l_db_link,'@');
3548 exception
3549 when others then
3550 wf_engine.SetItemAttrNumber( itemtype => itemtype,
3551 itemkey => itemkey,
3552 aname => 'SR_REQUEST_ID',
3553 avalue => -1);
3554 end;
3555 end if;
3556 wf_engine.SetItemAttrNumber( itemtype => itemtype,
3557 itemkey => itemkey,
3558 aname => 'SR_REQUEST_ID',
3559 avalue => p_request_id);
3560
3561
3562 resultout := 'COMPLETE:';
3563
3564
3565 RETURN;
3566 END IF;
3567
3568 IF (funcmode = 'CANCEL') THEN
3569 resultout := 'COMPLETE:';
3570 RETURN;
3571 END IF;
3572
3573 IF (funcmode = 'TIMEOUT') THEN
3574 resultout := 'COMPLETE:';
3575 RETURN;
3576 END IF;
3577 EXCEPTION
3578 when others then
3579 wf_core.context('MSC_EXP_WF', 'StartSrWF', itemtype, itemkey, actid, funcmode);
3580 raise;
3581 END StartSrWF;
3582
3583 Procedure launch_background_program(p_planner in varchar2,
3584 p_item_type in varchar2,
3585 p_item_key in varchar2,
3586 p_request_id out NOCOPY number) IS
3587 p_result boolean;
3588 Begin
3589 msc_rel_wf.init_db(p_planner);
3590 p_result := fnd_request.set_mode(true);
3591
3592 -- this will call start_deferred_activity
3593 p_request_id := fnd_request.submit_request(
3594 'MSC',
3595 'MSCWFBG',
3596 null,
3597 null,
3598 false,
3599 p_item_type,
3600 p_item_key);
3601
3602 exception when others then
3603 p_request_id :=0;
3604 raise;
3605 End launch_background_program;
3606
3607 Procedure start_deferred_activity(
3608 errbuf OUT NOCOPY VARCHAR2,
3609 retcode OUT NOCOPY NUMBER,
3610 p_item_type varchar2,
3611 p_item_key varchar2) IS
3612 CURSOR status_cur IS
3613 select 1
3614 from wf_item_activity_statuses
3615 where item_type = p_item_type
3616 and item_key = p_item_key
3617 and activity_status = 'DEFERRED';
3618 v_dummy number;
3619 v_time_elapsed number := 0;
3620 BEGIN
3621 while (v_dummy is null and v_time_elapsed < 120) loop
3622 OPEN status_cur;
3623 FETCH status_cur INTO v_dummy;
3624 CLOSE status_cur;
3625 FND_FILE.PUT_LINE(FND_FILE.LOG,'check deferred for '||p_item_key);
3626 dbms_lock.sleep(10);
3627 v_time_elapsed := v_time_elapsed + 10;
3628 end loop;
3629 if v_dummy = 1 then
3630 FND_FILE.PUT_LINE(FND_FILE.LOG,
3631 'start backgroud process for'||p_item_type);
3632 wf_engine.background(p_item_type);
3633 end if;
3634 END start_deferred_activity;
3635
3636 FUNCTION demand_order_type (p_plan_id number,
3637 p_inst_id number,
3638 p_demand_id NUMBER) return number is
3639 CURSOR dmd_cur IS
3640 SELECT origination_type
3641 FROM msc_demands
3642 WHERE plan_id = p_plan_id
3643 and sr_instance_id = p_inst_id
3644 and demand_id = p_demand_id;
3645 p_order_type number;
3646 BEGIN
3647 if ( p_plan_id is null OR p_inst_id is null or p_demand_id is null ) then
3648 return to_number(null);
3649 end if;
3650
3651 OPEN dmd_cur;
3652 FETCH dmd_cur INTO p_order_type;
3653 CLOSE dmd_cur;
3654 return p_order_type;
3655 END demand_order_type;
3656
3657 FUNCTION demand_order_date (p_plan_id number,
3658 p_inst_id number,
3659 p_demand_id NUMBER) return date is
3660 CURSOR dmd_cur IS
3661 SELECT using_assembly_demand_date
3662 FROM msc_demands
3663 WHERE plan_id = p_plan_id
3664 and sr_instance_id = p_inst_id
3665 and demand_id = p_demand_id;
3666 p_order_date date;
3667 BEGIN
3668 if ( p_plan_id is null OR p_inst_id is null or p_demand_id is null ) then
3669 return to_date(null);
3670 end if;
3671
3672 OPEN dmd_cur;
3673 FETCH dmd_cur INTO p_order_date;
3674 CLOSE dmd_cur;
3675 return p_order_date;
3676 END demand_order_date;
3677
3678 /*Function to return the substitute_supply_date for exception 49.*/
3679 FUNCTION substitute_supply_date (p_plan_id number,
3680 p_inst_id number,
3681 p_demand_id NUMBER) return date is
3682 CURSOR dmd_cur IS
3683 SELECT DMD_SATISFIED_DATE
3684 FROM msc_demands
3685 WHERE plan_id = p_plan_id
3686 and sr_instance_id = p_inst_id
3687 and demand_id = p_demand_id;
3688 p_supply_date date;
3689 BEGIN
3690 if ( p_plan_id is null OR p_inst_id is null or p_demand_id is null ) then
3691 return to_date(null);
3692 end if;
3693
3694 OPEN dmd_cur;
3695 FETCH dmd_cur INTO p_supply_date;
3696 CLOSE dmd_cur;
3697 return p_supply_date;
3698 END substitute_supply_date;
3699
3700 END msc_exp_wf;