[Home] [Help]
PACKAGE BODY: APPS.FLM_SEQ_READER_WRITER
Source
1 PACKAGE BODY flm_seq_reader_writer AS
2 /* $Header: FLMSQRWB.pls 120.3.12010000.2 2008/08/08 07:39:44 bgaddam ship $ */
3
4
5 /******************************************************************
6 * To get a list of working days for a period (start, end) *
7 * and put them in a global pl/sql table - g_days *
8 * The day will be in Julian format *
9 ******************************************************************/
10 PROCEDURE Init_Working_Days(p_organization_id IN NUMBER,
11 p_start_date IN NUMBER,
12 p_end_date IN NUMBER,
13 x_err_code OUT NOCOPY NUMBER,
14 x_err_msg OUT NOCOPY VARCHAR
15 ) IS
16 l_cnt NUMBER := 0;
17 l_start_date DATE;
18 l_end_date DATE;
19 l_date DATE;
20
21 Begin
22
23 x_err_code := 0;
24
25 l_start_date := wip_datetimes.float_to_dt(p_start_date);
26 l_end_date := wip_datetimes.float_to_dt(p_end_date);
27
28 if (not flm_timezone.is_init) then
29 flm_timezone.init_timezone(p_organization_id);
30 end if;
31
32 l_start_date := flm_timezone.server_to_calendar(l_start_date);
33 l_end_date := flm_timezone.server_to_calendar(l_end_date);
34
35 l_start_date := MRP_CALENDAR.NEXT_WORK_DAY(p_organization_id, 1, l_start_date);
36
37 l_cnt := MRP_CALENDAR.DAYS_BETWEEN(p_organization_id,
38 1,
39 l_start_date,
40 l_end_date
41 );
42 l_cnt := l_cnt + 1;
43
44 l_date := l_start_date-1;
45
46 FOR l_index IN 1..l_cnt LOOP
47 l_date := MRP_CALENDAR.NEXT_WORK_DAY(p_organization_id, 1, l_date+1);
48 g_days(l_index) := wip_datetimes.dt_to_float(l_date);
49 END LOOP;
50
51 g_days_index := 1;
52 x_err_code := l_cnt;
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 x_err_msg := 'Unexpected SQL Error: '||sqlerrm;
57 x_err_code := -1;
58
59 End Init_Working_Days;
60
61
62
63 /******************************************************************
64 * To get a working day list at a size of p_batch_size *
65 ******************************************************************/
66 PROCEDURE Get_Working_Days(
67 p_batch_size IN NUMBER,
68 x_days OUT NOCOPY number_tbl_type,
69 x_found IN OUT NOCOPY NUMBER,
70 x_done_flag OUT NOCOPY INTEGER,
71 x_err_code OUT NOCOPY NUMBER,
72 x_err_msg OUT NOCOPY VARCHAR
73 ) IS
74 l_size NUMBER := 0;
75 Begin
76
77 x_err_code := 0;
78 x_found := 0;
79 x_done_flag := 0;
80
81 WHILE (g_days_index <= g_days.COUNT and l_size < p_batch_size) LOOP
82 x_days(l_size+1) := g_days(g_days_index);
83 g_days_index := g_days_index + 1;
84 l_size := l_size + 1;
85 END LOOP;
86
87 x_found := l_size;
88
89 if (g_days_index > g_days.COUNT) then
90 x_done_flag := 1;
91 end if;
92
93 EXCEPTION
94 WHEN OTHERS THEN
95 x_err_msg := 'Unexpected SQL Error: '||sqlerrm;
96 x_err_code := -1;
97
98 End Get_Working_Days;
99
100
101
102 /******************************************************************
103 * To get available build sequence range for a period (start,end) *
107 p_organization_id IN NUMBER,
104 * The range (start_seq, end_seq) is an open interval (exclusive) *
105 ******************************************************************/
106 PROCEDURE Get_BuildSeq_Range(p_line_id IN NUMBER,
108 p_start_date IN NUMBER,
109 p_end_date IN NUMBER,
110 x_start_seq OUT NOCOPY NUMBER,
111 x_end_seq OUT NOCOPY NUMBER,
112 x_err_code OUT NOCOPY NUMBER,
113 x_err_msg OUT NOCOPY VARCHAR
114 ) IS
115
116 l_min NUMBER;
117 l_max NUMBER;
118
119 l_date1 DATE;
120 l_date2 DATE;
121
122 l_start_date DATE;
123 l_end_date DATE;
124
125 l_found INTEGER;
126 l_days INTEGER;
127
128 CURSOR max_seq_cursor IS
129 SELECT
130 max(BUILD_SEQUENCE)
131 FROM
132 WIP_FLOW_SCHEDULES
133 WHERE
134 ORGANIZATION_ID = p_organization_id AND
135 LINE_ID = p_line_id AND
136 SCHEDULED_COMPLETION_DATE <= l_date1;
137
138 CURSOR min_seq_cursor IS
139 SELECT
140 min(BUILD_SEQUENCE)
141 FROM
142 WIP_FLOW_SCHEDULES
143 WHERE
144 ORGANIZATION_ID = p_organization_id AND
145 LINE_ID = p_line_id AND
146 SCHEDULED_COMPLETION_DATE > l_date2;
147
148
149 Begin
150
151 x_err_code := 0;
152 x_start_seq := -1;
153 x_end_seq := -1;
154
155 l_start_date := wip_datetimes.float_to_dt(p_start_date);
156 l_date1 := l_start_date;
157
158
159 l_found := 0;
160
161 OPEN max_seq_cursor;
162
163 FETCH max_seq_cursor INTO l_max;
164
165 if (max_seq_cursor%FOUND and l_max is not null) then
166 l_found := 1;
167 end if;
168
169 CLOSE max_seq_cursor;
170
171 if (l_found <> 0 and l_max is not null) then
172 x_start_seq := l_max;
173 else
174 x_start_seq := -1;
175 end if;
176
177 l_end_date := wip_datetimes.float_to_dt(p_end_date);
178 l_date2 := l_end_date;
179
180
181 l_found := 0;
182
183 OPEN min_seq_cursor;
184
185 FETCH min_seq_cursor INTO l_min;
186
187 if (min_seq_cursor%FOUND and l_min is not null) then
188 l_found := 1;
189 end if;
190
191 CLOSE min_seq_cursor;
192
193 if (l_found <> 0 and l_min is not null) then
194 x_end_seq := l_min;
195 else
196 x_end_seq := -1;
197 end if;
198
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 x_err_msg := 'Unexpected SQL Error: '||sqlerrm;
203 x_err_code := -1;
204 x_start_seq := -1;
205 x_end_seq := -1;
206
207 End Get_BuildSeq_Range;
208
209 /******************************************************************
210 * To initialize globals used by db writer *
211 ******************************************************************/
212 FUNCTION initialize_globals return NUMBER IS
213 l_return_status NUMBER;
214 BEGIN
215 g_job_prefix := substr(FND_PROFILE.value('WIP_JOB_PREFIX'),1,20);
216 g_login_id := FND_GLOBAL.login_id;
217 g_user_id := FND_GLOBAL.user_id;
218 sch_rec_tbl.DELETE;
219 g_cto_line_tbl.DELETE;
220
221 BEGIN
222 MRP_WFS_Form_Flow_Schedule.get_default_dff(l_return_status,
223 g_attribute1,g_attribute2,g_attribute3,
224 g_attribute4,g_attribute5,g_attribute6,
225 g_attribute7,g_attribute8,g_attribute9,
226 g_attribute10,g_attribute11,g_attribute12,
227 g_attribute13,g_attribute14,g_attribute15);
228 EXCEPTION
229 when others then
230 null;
231 END;
232
233 return 0;
234
235 EXCEPTION
236 when others then
237 return 1;
238
239 END;
240
241
242 /******************************************************************
243 * To add a schedule in schedules table and return wip_id and *
244 * schedule number *
245 ******************************************************************/
246 PROCEDURE add_sch_rec(i_org_id NUMBER,
247 i_primary_item_id NUMBER,
248 i_line_id NUMBER,
252 i_alt_rtg_designator VARCHAR2,
249 i_sch_start_date DATE,
250 i_sch_completion_date DATE,
251 i_planned_quantity NUMBER,
253 i_build_sequence NUMBER,
254 i_schedule_group_id NUMBER,
255 i_demand_type NUMBER,
256 i_demand_id NUMBER,
257 x_wip_entity_id IN OUT NOCOPY NUMBER,
258 x_schedule_number IN OUT NOCOPY VARCHAR2,
259 o_return_code OUT NOCOPY NUMBER
260 )IS
261 l_index NUMBER;
262 l_wip_entity_id NUMBER;
263 l_schedule_number VARCHAR2(30);
264
265 l_schedule_group_id NUMBER;
266 BEGIN
267 o_return_code := 0;
268
269 --get the current number of elements in table
270 l_index := sch_rec_tbl.COUNT;
271
272 --get the position to insert current record
273 l_index := l_index + 1;
274
275 if( i_schedule_group_id = -1 ) then
276 l_schedule_group_id := null;
277 else
278 l_schedule_group_id := i_schedule_group_id;
279 end if;
280
281 sch_rec_tbl(l_index).org_id := i_org_id;
282 sch_rec_tbl(l_index).primary_item_id := i_primary_item_id;
283 sch_rec_tbl(l_index).line_id := i_line_id;
284 sch_rec_tbl(l_index).planned_quantity := i_planned_quantity;
285 sch_rec_tbl(l_index).alt_rtg_designator := i_alt_rtg_designator;
286 sch_rec_tbl(l_index).sch_start_date := i_sch_start_date;
287 sch_rec_tbl(l_index).sch_completion_date := i_sch_completion_date;
288 sch_rec_tbl(l_index).sch_group_id := l_schedule_group_id;
289 sch_rec_tbl(l_index).build_sequence := i_build_sequence;
290 sch_rec_tbl(l_index).demand_type := i_demand_type;
291 sch_rec_tbl(l_index).demand_id := i_demand_id;
292
293 --get the wip_entity_id and schedule_number for current schedule
294 get_wip_id_and_sch_num(l_wip_entity_id, l_schedule_number);
295
296 --populate current record with wip_id and schedule_number
297 sch_rec_tbl(l_index).wip_entity_id := l_wip_entity_id;
298 sch_rec_tbl(l_index).schedule_number := l_schedule_number;
299
300 --prepare out values with wip_id and schdule_number for this schedule
301 x_wip_entity_id := l_wip_entity_id;
302 x_schedule_number := l_schedule_number;
303
304 o_return_code := 0;
305 return;
306
307 EXCEPTION
308 when others then
309 o_return_code := 1;
310 return;
311
312 END add_sch_rec;
313
314
315 /******************************************************************
316 * To default the schedule columns and inserting the schedules *
317 ******************************************************************/
318 PROCEDURE create_schedules (o_return_code OUT NOCOPY NUMBER) IS
319 l_return_code NUMBER;
320 l_sch_tbl_to_insert wip_flow_schedule_tbl;
321 BEGIN
322 o_return_code := 0;
323
324 default_attributes(sch_rec_tbl, l_sch_tbl_to_insert, l_return_code);
325 if(l_return_code = 1) then
326 o_return_code := 1;
327 return;
328 end if;
329
330 insert_schedules(l_sch_tbl_to_insert, l_return_code);
331 if(l_return_code = 1) then
332 o_return_code := 1;
333 return;
334 end if;
335
336 explode_all_items(l_sch_tbl_to_insert,l_return_code);
337 if(l_return_code = 1) then
338 o_return_code := 1;
339 return;
340 end if;
341
342 update_mrp_recommendations(l_sch_tbl_to_insert,l_return_code);
343 if(l_return_code = 1) then
344 o_return_code := 1;
345 return;
346 end if;
347
348 call_cto_api(l_return_code);
349 if(l_return_code = 1) then
350 o_return_code := 1;
351 return;
352 end if;
353
354 o_return_code := 0;
355 return;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 o_return_code := 1;
360 return;
361
362 END create_schedules;
363
364
365 /******************************************************************
366 * To default/derive the attribute which are not passed for this *
367 * schedule and copy the attributes which are passed *
368 ******************************************************************/
369 PROCEDURE default_attributes(sch_rec_tbl IN OUT NOCOPY schedule_rec_tbl_type,
370 l_sch_tbl_to_insert IN OUT NOCOPY wip_flow_schedule_tbl,
371 o_return_code OUT NOCOPY NUMBER) IS
372 l_index NUMBER;
373 l_wip_entity_id NUMBER;
374 l_sch_number VARCHAR2(30);
375 l_class_code VARCHAR2(10);
376 BEGIN
377 o_return_code := 0;
378 l_index := sch_rec_tbl.FIRST;
379
380 if(sch_rec_tbl.COUNT > 0) then LOOP
381 --first copy the passed parameters
382 l_sch_tbl_to_insert(l_index).primary_item_id :=
383 sch_rec_tbl(l_index).primary_item_id;
384
385 l_sch_tbl_to_insert(l_index).organization_id :=
386 sch_rec_tbl(l_index).org_id;
387
388 l_sch_tbl_to_insert(l_index).planned_quantity :=
389 sch_rec_tbl(l_index).planned_quantity;
390
391 l_sch_tbl_to_insert(l_index).alternate_routing_designator :=
392 sch_rec_tbl(l_index).alt_rtg_designator;
393
394 l_sch_tbl_to_insert(l_index).scheduled_start_date :=
395 sch_rec_tbl(l_index).sch_start_date;
396
397 l_sch_tbl_to_insert(l_index).scheduled_completion_date :=
398 sch_rec_tbl(l_index).sch_completion_date;
399
403 if(sch_rec_tbl(l_index).build_sequence = -1) then
400 l_sch_tbl_to_insert(l_index).schedule_group_id :=
401 sch_rec_tbl(l_index).sch_group_id;
402
404 l_sch_tbl_to_insert(l_index).build_sequence := null;
405 else
406 l_sch_tbl_to_insert(l_index).build_sequence :=
407 sch_rec_tbl(l_index).build_sequence;
408 end if;
409
410 l_sch_tbl_to_insert(l_index).line_id :=
411 sch_rec_tbl(l_index).line_id;
412
413 l_sch_tbl_to_insert(l_index).demand_source_type :=
414 sch_rec_tbl(l_index).demand_type;
415
416 if( (sch_rec_tbl(l_index).demand_type = g_demand_type_SO ) OR
417 (sch_rec_tbl(l_index).demand_type = g_demand_type_PO ) ) then
418 l_sch_tbl_to_insert(l_index).demand_source_line :=
419 to_char(sch_rec_tbl(l_index).demand_id);
420 end if;
421
422 l_sch_tbl_to_insert(l_index).wip_entity_id :=
423 sch_rec_tbl(l_index).wip_entity_id;
424
425 l_sch_tbl_to_insert(l_index).schedule_number :=
426 sch_rec_tbl(l_index).schedule_number;
427
428 --default who_columns
429 l_sch_tbl_to_insert(l_index).last_update_date := sysdate;
430 l_sch_tbl_to_insert(l_index).last_updated_by := g_user_id;
431 l_sch_tbl_to_insert(l_index).creation_date := sysdate;
432 l_sch_tbl_to_insert(l_index).created_by := g_user_id;
433 l_sch_tbl_to_insert(l_index).last_update_login := g_login_id;
434
435 --default columns with constant values
436 l_sch_tbl_to_insert(l_index).scheduled_flag := 1;
437 l_sch_tbl_to_insert(l_index).status := 1;
438 l_sch_tbl_to_insert(l_index).quantity_completed := 0;
439 l_sch_tbl_to_insert(l_index).quantity_scrapped := 0;
440
441 --default columns which will be null
442 l_sch_tbl_to_insert(l_index).date_closed := null;
443 /* Commented out for bug 6358519 .
444 This would be derived from the demand
445 l_sch_tbl_to_insert(l_index).project_id := null;
446 l_sch_tbl_to_insert(l_index).task_id := null;*/
447 l_sch_tbl_to_insert(l_index).kanban_card_id := null;
448
449 --default the descriptive flex columns
450 l_sch_tbl_to_insert(l_index).attribute1 := g_attribute1;
451 l_sch_tbl_to_insert(l_index).attribute2 := g_attribute2;
452 l_sch_tbl_to_insert(l_index).attribute3 := g_attribute3;
453 l_sch_tbl_to_insert(l_index).attribute4 := g_attribute4;
454 l_sch_tbl_to_insert(l_index).attribute5 := g_attribute5;
455 l_sch_tbl_to_insert(l_index).attribute6 := g_attribute6;
456 l_sch_tbl_to_insert(l_index).attribute7 := g_attribute7;
457 l_sch_tbl_to_insert(l_index).attribute8 := g_attribute8;
458 l_sch_tbl_to_insert(l_index).attribute9 := g_attribute9;
459 l_sch_tbl_to_insert(l_index).attribute10 := g_attribute10;
460 l_sch_tbl_to_insert(l_index).attribute11 := g_attribute11;
461 l_sch_tbl_to_insert(l_index).attribute12 := g_attribute12;
462 l_sch_tbl_to_insert(l_index).attribute13 := g_attribute13;
463 l_sch_tbl_to_insert(l_index).attribute14 := g_attribute14;
464 l_sch_tbl_to_insert(l_index).attribute15 := g_attribute15;
465
466 --get class code
467 l_class_code := get_class_code(l_sch_tbl_to_insert(l_index).organization_id,
468 l_sch_tbl_to_insert(l_index).primary_item_id);
469 l_sch_tbl_to_insert(l_index).class_code := l_class_code;
470
471 --once class code is obtained, get account_ids
472 get_account_ids (l_sch_tbl_to_insert(l_index).organization_id,
473 l_sch_tbl_to_insert(l_index).class_code,
474 l_sch_tbl_to_insert(l_index).material_account,
475 l_sch_tbl_to_insert(l_index).material_overhead_account,
476 l_sch_tbl_to_insert(l_index).resource_account,
477 l_sch_tbl_to_insert(l_index).outside_processing_account,
478 l_sch_tbl_to_insert(l_index).material_variance_account,
479 l_sch_tbl_to_insert(l_index).resource_variance_account,
480 l_sch_tbl_to_insert(l_index).outside_proc_variance_account,
481 l_sch_tbl_to_insert(l_index).std_cost_adjustment_account,
482 l_sch_tbl_to_insert(l_index).overhead_account,
483 l_sch_tbl_to_insert(l_index).overhead_variance_account);
484
485 --get the bom_revision and bom_revision_date
486 get_bom_rev_and_date(l_sch_tbl_to_insert(l_index).organization_id,
487 l_sch_tbl_to_insert(l_index).primary_item_id,
488 l_sch_tbl_to_insert(l_index).scheduled_completion_date,
489 l_sch_tbl_to_insert(l_index).bom_revision,
490 l_sch_tbl_to_insert(l_index).bom_revision_date);
491
492 --get the routing_revision and routing_revision_date
493 get_rtg_rev_and_date(l_sch_tbl_to_insert(l_index).organization_id,
494 l_sch_tbl_to_insert(l_index).primary_item_id,
495 l_sch_tbl_to_insert(l_index).scheduled_completion_date,
496 l_sch_tbl_to_insert(l_index).routing_revision,
497 l_sch_tbl_to_insert(l_index).routing_revision_date);
498
499 --get the alternate bom_designator
500 get_alt_bom_designator(l_sch_tbl_to_insert(l_index).organization_id,
501 l_sch_tbl_to_insert(l_index).primary_item_id,
502 l_sch_tbl_to_insert(l_index).alternate_routing_designator,
503 l_sch_tbl_to_insert(l_index).alternate_bom_designator);
504
505 --fix bug#4045737 (forward port of bug#4011166)
506 --added missing defaulting
507 --get the completion subinventory and locator
508 get_completion_subinv_and_loc(l_sch_tbl_to_insert(l_index).organization_id,
509 l_sch_tbl_to_insert(l_index).primary_item_id,
513 --end of fix bug#4045737
510 l_sch_tbl_to_insert(l_index).alternate_routing_designator,
511 l_sch_tbl_to_insert(l_index).completion_subinventory,
512 l_sch_tbl_to_insert(l_index).completion_locator_id);
514
515 --get the demand class and demand header
516 get_demand_class(sch_rec_tbl(l_index).demand_type,
517 sch_rec_tbl(l_index).demand_id,
518 l_sch_tbl_to_insert(l_index).demand_class,
519 l_sch_tbl_to_insert(l_index).demand_source_header_id);
520
521 -- bug 6358519
522 -- Added this method to get the project and task
523 -- get the project and task
524 get_project_task(sch_rec_tbl(l_index).demand_type ,
525 sch_rec_tbl(l_index).demand_id,
526 l_sch_tbl_to_insert(l_index).project_id,
527 l_sch_tbl_to_insert(l_index).task_id);
528 --end of fix bug#6358519
529
530 l_sch_tbl_to_insert(l_index).request_id := USERENV('SESSIONID');
531
532 EXIT WHEN l_index = sch_rec_tbl.LAST;
536 end if;
533 l_index := sch_rec_tbl.next(l_index);
534
535 END LOOP;
537
538 EXCEPTION
539 WHEN OTHERS THEN
540 o_return_code := 1;
541 return;
542
543 END default_attributes;
544
545
546 /******************************************************************
547 * gets the wip_entity_id and schedule_number from sequence *
548 ******************************************************************/
549 PROCEDURE get_wip_id_and_sch_num (o_wip_entity_id OUT NOCOPY NUMBER,
550 o_schedule_number OUT NOCOPY VARCHAR2)IS
551 l_wip_entity_id NUMBER;
552 l_schedule_number_out VARCHAR2(30);
553 l_error NUMBER;
554
555 BEGIN
556 SELECT wip_entities_s.nextval
557 INTO l_wip_entity_id
558 FROM dual;
559
560 o_wip_entity_id := l_wip_entity_id;
561
562 l_schedule_number_out := NULL;
563 l_error := wip_flow_derive.schedule_number(l_schedule_number_out);
564 if (l_error = 1) then
565 o_schedule_number := l_schedule_number_out;
566 else
567 raise NO_DATA_FOUND;
568 end if;
569
570 EXCEPTION when others then
571 raise;
572
573
574
575 END get_wip_id_and_sch_num;
576
577
578 /******************************************************************
579 * gets the demand class based on demand type *
580 ******************************************************************/
581 PROCEDURE get_demand_class(i_demand_type NUMBER,
582 i_demand_id NUMBER,
583 o_demand_class IN OUT NOCOPY VARCHAR2,
584 o_demand_header IN OUT NOCOPY NUMBER ) IS
585 l_demand_class VARCHAR2(30) := NULL;
586 l_demand_header NUMBER;
587 l_header NUMBER;
588 BEGIN
589 /*
590 for planned order: can pass null for class code
591 for sales order: get oe_order_lines_all.demand_class_code
592 for existing:
593 */
594 if(i_demand_type = g_demand_type_SO) then
595 SELECT demand_class_code,header_id
596 INTO l_demand_class, l_demand_header
597 FROM OE_ORDER_LINES_ALL
598 WHERE line_id = i_demand_id;
599
600 if(l_demand_header IS NOT NULL) then
601 l_header := inv_salesorder.get_salesorder_for_oeheader(l_demand_header);
602 end if;
603 o_demand_class := l_demand_class;
604 o_demand_header := l_header;
605 end if;
606
607 END get_demand_class;
608
609 /******************************************************************
610 * gets the project id based on demand id Added for Bug 6358519 *
611 ******************************************************************/
612 PROCEDURE get_project_task(i_demand_type NUMBER,
613 i_demand_id NUMBER,
614 o_project_id IN OUT NOCOPY NUMBER,
615 o_task_id IN OUT NOCOPY NUMBER ) IS
616 l_project_id NUMBER;
617 l_task_id NUMBER;
618
619 BEGIN
620
621 if(i_demand_type = g_demand_type_SO) then
622 SELECT project_id,task_id
623 INTO l_project_id, l_task_id
624 FROM OE_ORDER_LINES_ALL
625 WHERE line_id = i_demand_id;
626
627 o_project_id := l_project_id;
628 o_task_id := l_task_id;
629
630 end if;
631
632 EXCEPTION
633 WHEN NO_DATA_FOUND THEN
634
635 o_project_id := NULL;
636 o_task_id := NULL;
637
638 END get_project_task;
639
643 PROCEDURE get_completion_subinv_and_loc (i_org_id NUMBER,
640 /******************************************************************
641 * To get the completion subinventory and locator *
642 ******************************************************************/
644 i_primary_item_id NUMBER,
645 i_alt_rtg_designator VARCHAR2,
646 o_completion_subinv OUT NOCOPY VARCHAR2,
647 o_completion_locator_id OUT NOCOPY NUMBER) IS
648 l_alt_routing VARCHAR(10) := NULL;
649 l_subinventory VARCHAR(10) := NULL;
650 l_locator_id NUMBER := NULL;
651 l_error_number NUMBER := 1;
652 BEGIN
653
654 IF(i_alt_rtg_designator IS NULL)
655 THEN
656 l_alt_routing := NULL;
657 ELSE
658 l_alt_routing := i_alt_rtg_designator;
659 END IF;
660
661 l_error_number := WIP_FLOW_DERIVE.Routing_Completion_Sub_Loc(
662 l_subinventory,
663 l_locator_id,
664 i_primary_item_id,
665 i_org_id,
666 l_alt_routing
667 );
668
669 IF(l_error_number = 1) THEN
670 o_completion_subinv := l_subinventory;
671 o_completion_locator_id := l_locator_id;
672 END IF;
673
674 END get_completion_subinv_and_loc;
675
676
677 /******************************************************************
678 * To get alternate bom designator *
679 ******************************************************************/
680 PROCEDURE get_alt_bom_designator(i_org_id NUMBER,
681 i_primary_item_id NUMBER,
682 i_alt_rtg_designator VARCHAR2,
683 o_alt_bom_designator OUT NOCOPY VARCHAR2) IS
684 l_bill_count NUMBER;
685 BEGIN
686
687 IF(i_alt_rtg_designator IS NULL) THEN
688 o_alt_bom_designator := NULL;
689 ELSE
690 SELECT count(bill_sequence_id)
691 INTO l_bill_count
692 FROM BOM_BILL_OF_MATERIALS
693 WHERE organization_id = i_org_id AND
694 assembly_item_id = i_primary_item_id AND
695 alternate_bom_designator = i_alt_rtg_designator;
696 IF (l_bill_count > 0) THEN
697 o_alt_bom_designator := i_alt_rtg_designator;
698 ELSE
699 o_alt_bom_designator := NULL;
700 END IF;
701 END IF;
702 END get_alt_bom_designator;
703
704
705
706 /******************************************************************
707 * To get bom revision and bom revision date *
708 ******************************************************************/
709 PROCEDURE get_bom_rev_and_date (i_org_id NUMBER,
710 i_primary_item_id NUMBER,
711 i_sch_completion_date DATE,
712 o_bom_revision OUT NOCOPY VARCHAR,
713 o_bom_revision_date OUT NOCOPY DATE) IS
714
718 l_revision_date DATE := NULL;
715 l_bom_revision VARCHAR(3) := NULL;
716 l_revision VARCHAR(3) := NULL;
717 l_error_number NUMBER := 1;
719 BEGIN
720 l_error_number := WIP_FLOW_DERIVE.Bom_Revision(
721 l_bom_revision,
722 l_revision,
723 l_revision_date,
724 i_primary_item_id,
725 i_sch_completion_date,
726 i_org_id
727 );
728
729 IF(l_error_number = 1) THEN
730 o_bom_revision := l_bom_revision;
731 o_bom_revision_date := l_revision_date;
732 END IF;
733
734 END get_bom_rev_and_date;
735
736
737 /******************************************************************
738 * To get routing revision and routing revision date *
739 ******************************************************************/
740 PROCEDURE get_rtg_rev_and_date (i_org_id NUMBER,
741 i_primary_item_id NUMBER,
742 i_sch_completion_date DATE,
743 o_rtg_revision OUT NOCOPY VARCHAR,
747 l_error_number NUMBER := 1;
744 o_rtg_revision_date OUT NOCOPY DATE) IS
745
746 l_rtg_revision VARCHAR(3) := NULL;
748 l_revision_date DATE := NULL;
749 BEGIN
750 l_error_number := WIP_FLOW_DERIVE.Routing_Revision(
751 l_rtg_revision,
752 l_revision_date,
753 i_primary_item_id,
754 i_sch_completion_date,
755 i_org_id
756 );
757 IF(l_error_number = 1) THEN
758 o_rtg_revision := l_rtg_revision;
759 o_rtg_revision_date := l_revision_date;
760 END IF;
761
762 END get_rtg_rev_and_date;
763
764
765 /******************************************************************
766 * To get all account id based on class code *
767 ******************************************************************/
768 PROCEDURE get_account_ids (i_org_id NUMBER, i_class_code VARCHAR2,
769 i_material_act IN OUT NOCOPY NUMBER,
770 i_material_overhead_act IN OUT NOCOPY NUMBER,
771 i_resource_act IN OUT NOCOPY NUMBER,
772 i_outside_processing_act IN OUT NOCOPY NUMBER,
773 i_material_variance_act IN OUT NOCOPY NUMBER,
774 i_resource_variance_act IN OUT NOCOPY NUMBER,
775 i_outside_proc_variance_act IN OUT NOCOPY NUMBER,
776 i_std_cost_adjustment_act IN OUT NOCOPY NUMBER,
777 i_overhead_act IN OUT NOCOPY NUMBER,
778 i_overhead_variance_act IN OUT NOCOPY NUMBER) IS
779 l_material_act NUMBER;
780 l_material_overhead_act NUMBER;
781 l_resource_act NUMBER;
782 l_outside_processing_act NUMBER;
783 l_material_variance_act NUMBER;
784 l_resource_variance_act NUMBER;
785 l_outside_proc_variance_act NUMBER;
786 l_std_cost_adjustment_act NUMBER;
787 l_overhead_act NUMBER;
788 l_overhead_variance_act NUMBER;
789
790 BEGIN
791 SELECT MATERIAL_ACCOUNT, MATERIAL_OVERHEAD_ACCOUNT,
792 RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
793 MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT,
794 OUTSIDE_PROC_VARIANCE_ACCOUNT, STD_COST_ADJUSTMENT_ACCOUNT,
795 OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT
796 INTO
797 l_material_act, l_material_overhead_act,
798 l_resource_act, l_outside_processing_act,
799 l_material_variance_act, l_resource_variance_act,
800 l_outside_proc_variance_act, l_std_cost_adjustment_act,
801 l_overhead_act, l_overhead_variance_act
802 FROM WIP_ACCOUNTING_CLASSES
803 WHERE ORGANIZATION_ID = i_org_id AND
804 CLASS_CODE = i_class_code;
805
806 i_material_act := l_material_act;
807 i_material_overhead_act := l_material_overhead_act;
808 i_resource_act := l_resource_act;
809 i_outside_processing_act := l_outside_processing_act;
810 i_material_variance_act := l_material_variance_act;
811 i_resource_variance_act := l_resource_variance_act;
812 i_outside_proc_variance_act := l_outside_proc_variance_act;
813 i_std_cost_adjustment_act := l_std_cost_adjustment_act;
814 i_overhead_act := l_overhead_act;
815 i_overhead_variance_act := l_overhead_variance_act;
816
817 EXCEPTION when others then
818 return;
819
820 END get_account_ids;
821
822
823 /******************************************************************
824 * To get class code based on item and organization *
825 ******************************************************************/
826 FUNCTION get_class_code(i_org_id NUMBER, i_item_id NUMBER )RETURN VARCHAR IS
827 l_error_number NUMBER := 1;
828 l_error_mesg VARCHAR2(80) := null;
829 l_class_code VARCHAR2(10) := null;
830 BEGIN
831 l_error_number := WIP_FLOW_DERIVE.Class_Code(l_class_code,
832 l_error_mesg,
833 i_org_id,
834 i_item_id,
835 4, --entity_type
836 null);
837
838 if(l_error_number <> 1) then
839 SELECT default_discrete_class
840 INTO l_class_code
841 FROM wip_parameters
842 WHERE organization_id = i_org_id;
843 end if;
844
845 return l_class_code;
846
847 END get_class_code;
848
849
850 /******************************************************************
851 * To explode the item bom *
852 ******************************************************************/
853 PROCEDURE explode_items (i_item_id IN NUMBER,
854 i_org_id IN NUMBER,
855 i_alt_bom IN VARCHAR2,
856 x_error_msg IN OUT NOCOPY VARCHAR2,
857 x_error_code IN OUT NOCOPY NUMBER) IS
858 BEGIN
859 BOM_OE_EXPLODER_PKG.be_exploder(
860 arg_org_id => i_org_id,
861 arg_starting_rev_date => sysdate - 3,
862 arg_expl_type => 'ALL',
863 arg_order_by => 1,
864 arg_levels_to_explode => 20,
865 arg_item_id => i_item_id,
866 arg_comp_code => '',
867 arg_user_id => 0,
868 arg_err_msg => x_error_msg,
869 arg_error_code => x_error_code,
870 arg_alt_bom_desig => i_alt_bom
871 );
872
876 elsif x_error_code <> 0 then
873 if x_error_code = 9998 then
874 -- Do nothing, there was just no bill to explode
875 x_error_code := 0;
877 return;
878 end if;
879
880 END explode_items;
881
882
883 /******************************************************************
884 * This procedure loops through schedules table, find out *
885 * unique item and alternate bom combinations, and call *
886 * explode for each unique combination *
887 ******************************************************************/
888 PROCEDURE explode_all_items(i_schedules_tbl IN OUT NOCOPY
889 wip_flow_schedule_tbl,
890 o_return_code OUT NOCOPY NUMBER) IS
891 l_index NUMBER;
892 l_item_tbl_to_explode wip_flow_schedule_tbl;
893 item_alt_already_exist BOOLEAN;
894 l_error_msg VARCHAR2(2000);
895 l_error_code NUMBER;
896 iLine NUMBER;
897
898 BEGIN
899 o_return_code := 0;
900
901 FOR i in i_schedules_tbl.FIRST .. i_schedules_tbl.LAST
902 LOOP
903 item_alt_already_exist := false;
904
905 if(l_item_tbl_to_explode.COUNT > 1) then
906 for j in l_item_tbl_to_explode.FIRST .. l_item_tbl_to_explode.LAST
907 LOOP
908 if(
909 (l_item_tbl_to_explode(j).organization_id =
910 i_schedules_tbl(i).organization_id) AND
911 (l_item_tbl_to_explode(j).primary_item_id =
912 i_schedules_tbl(i).primary_item_id) AND
913 (nvl(l_item_tbl_to_explode(j).alternate_bom_designator,'$$$') =
914 nvl(i_schedules_tbl(i).alternate_bom_designator,'$$$') )
915 ) then
916 item_alt_already_exist := true;
917 end if;
918 END LOOP;
919 end if;
920
921 if(item_alt_already_exist = false) then
922 --get the current number of elements in items table
923 l_index := l_item_tbl_to_explode.COUNT;
924 --get the position to insert current record
925 l_index := l_index + 1;
926 l_item_tbl_to_explode(l_index).organization_id :=
927 i_schedules_tbl(i).organization_id;
928 l_item_tbl_to_explode(l_index).primary_item_id :=
929 i_schedules_tbl(i).primary_item_id;
930 l_item_tbl_to_explode(l_index).alternate_bom_designator :=
931 i_schedules_tbl(i).alternate_bom_designator;
932 end if;
933
934 --while looping to find out unique items, we also build the
935 --table for unique so line, that will be used for call to CTO
936 if(i_schedules_tbl(i).demand_source_type = g_demand_type_SO) then
937 if(i_schedules_tbl(i).demand_source_line is not null) then
938 g_cto_line_tbl(to_number(i_schedules_tbl(i).demand_source_line)).
939 demand_source_line := i_schedules_tbl(i).demand_source_line;
940 g_cto_line_tbl(to_number(i_schedules_tbl(i).demand_source_line)).
941 primary_item_id := i_schedules_tbl(i).primary_item_id;
942 g_cto_line_tbl(to_number(i_schedules_tbl(i).demand_source_line)).
943 organization_id := i_schedules_tbl(i).organization_id;
944 end if;
945 end if;
946
947 END LOOP;
948
949 --now all the unique item, alternate combination have been identified
950 --call explode for each combination
951 if(l_item_tbl_to_explode.COUNT > 0) then
952 for k in l_item_tbl_to_explode.FIRST .. l_item_tbl_to_explode.LAST
953 LOOP
954 explode_items (l_item_tbl_to_explode(k).primary_item_id,
955 l_item_tbl_to_explode(k).organization_id,
956 l_item_tbl_to_explode(k).alternate_bom_designator,
957 l_error_msg,
958 l_error_code);
959 if(l_error_code <> 0) then
960 o_return_code := 1;
961 end if;
962 END LOOP;
963 end if;
964
965 EXCEPTION
966 WHEN OTHERS THEN
967 o_return_code := 1;
968 return;
969
970 END explode_all_items;
971
972
973 /******************************************************************
974 * To call the CTO API for each so line *
975 ******************************************************************/
976 PROCEDURE call_cto_api(o_return_code IN OUT NOCOPY NUMBER) IS
977 TYPE item_detail_rec_type IS RECORD
978 (
979 primary_item_id NUMBER,
980 organization_id NUMBER,
981 replenish_to_order_flag VARCHAR2(1),
982 build_in_wip_flag VARCHAR2(1)
983 );
984 TYPE item_detail_tbl_type IS TABLE OF item_detail_rec_type INDEX BY BINARY_INTEGER;
985
986 l_item_dtl_tbl item_detail_tbl_type;
987 iLine NUMBER;
988 l_primary_item_id NUMBER;
989 l_replenish_to_order_flag VARCHAR2(1);
990 l_build_in_wip_flag VARCHAR2(1);
991 l_org_id NUMBER;
992 l_return_status VARCHAR2(1);
993 l_msg_data VARCHAR2(240);
994 l_msg_count NUMBER;
995
996 BEGIN
997 o_return_code := 0;
998
999 IF(g_cto_line_tbl.COUNT > 0) THEN
1000 iLine := g_cto_line_tbl.FIRST;
1001 WHILE iLine IS NOT NULL LOOP
1002 l_build_in_wip_flag := 'N';
1003 l_replenish_to_order_flag := 'N';
1004
1005 l_primary_item_id := g_cto_line_tbl(iLine).primary_item_id;
1006 l_org_id := g_cto_line_tbl(iLine).organization_id;
1007
1008 --find out if this item and attributes already exist in local pls table
1009 --if exist use those,
1010 --if not exist, then query and save the record in local pls table for further use
1014 l_build_in_wip_flag :=
1011 if(l_item_dtl_tbl.EXISTS(l_primary_item_id)) then
1012 l_replenish_to_order_flag :=
1013 l_item_dtl_tbl(l_primary_item_id).replenish_to_order_flag;
1015 l_item_dtl_tbl(l_primary_item_id).build_in_wip_flag;
1016 else
1017 select msi.build_in_wip_flag, msi.replenish_to_order_flag
1018 into l_build_in_wip_flag, l_replenish_to_order_flag
1019 from mtl_system_items msi
1020 where msi.inventory_item_id = l_primary_item_id
1021 and msi.organization_id = l_org_id;
1022 l_item_dtl_tbl(l_primary_item_id).primary_item_id := l_primary_item_id;
1023 l_item_dtl_tbl(l_primary_item_id).replenish_to_order_flag :=
1024 l_replenish_to_order_flag;
1025 l_item_dtl_tbl(l_primary_item_id).build_in_wip_flag :=
1026 l_build_in_wip_flag;
1027 end if;
1028
1029 if( (l_build_in_wip_flag = 'Y') AND (l_replenish_to_order_flag = 'Y')) then
1030 CTO_WIP_WORKFLOW_API_PK.flow_creation(g_cto_line_tbl(iLine).demand_source_line,
1031 l_return_status,
1035
1032 l_msg_count,
1033 l_msg_data);
1034 end if;
1036 iLine := g_cto_line_tbl.NEXT(iLine);
1037 END LOOP;
1038 END IF;
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 o_return_code := 1;
1043 return;
1044
1045 END call_cto_api;
1046
1047
1048 /******************************************************************
1049 * To update the mrp_recommendations based on schedules inserted *
1050 ******************************************************************/
1051 PROCEDURE update_mrp_recommendations(i_schedules_tbl IN wip_flow_schedule_tbl,
1052 o_return_code IN OUT NOCOPY NUMBER) IS
1053
1054 l_index NUMBER;
1055 l_item_tbl_to_update_rec wip_flow_schedule_tbl;
1056 item_already_exist BOOLEAN;
1057 l_error_msg VARCHAR2(2000);
1058 l_error_code NUMBER;
1059 BEGIN
1060 o_return_code := 0;
1061
1062 FOR i in i_schedules_tbl.FIRST .. i_schedules_tbl.LAST
1063 LOOP
1064 item_already_exist := false;
1065 if(l_item_tbl_to_update_rec.COUNT > 1) then
1066 for j in l_item_tbl_to_update_rec.FIRST .. l_item_tbl_to_update_rec.LAST
1067 LOOP
1068 if(
1069 (l_item_tbl_to_update_rec(j).organization_id =
1070 i_schedules_tbl(i).organization_id) AND
1071 (l_item_tbl_to_update_rec(j).demand_source_line =
1072 i_schedules_tbl(i).demand_source_line) AND
1073 (i_schedules_tbl(i).demand_source_header_id IS NULL )
1074 ) then
1075 l_item_tbl_to_update_rec(j).planned_quantity :=
1076 l_item_tbl_to_update_rec(j).planned_quantity +
1077 i_schedules_tbl(i).planned_quantity;
1078 item_already_exist := true;
1079 end if;
1080 END LOOP;
1081 end if;
1082
1083 if( (item_already_exist = false) AND
1084 ( i_schedules_tbl(i).demand_source_header_id IS NULL)
1085 ) then
1086
1087 --get the current number of elements in items table
1088 l_index := l_item_tbl_to_update_rec.COUNT;
1089 --get the position to insert current record
1090 l_index := l_index + 1;
1091 l_item_tbl_to_update_rec(l_index).organization_id :=
1092 i_schedules_tbl(i).organization_id;
1093 l_item_tbl_to_update_rec(l_index).demand_source_line :=
1094 i_schedules_tbl(i).demand_source_line;
1095 l_item_tbl_to_update_rec(l_index).planned_quantity :=
1096 i_schedules_tbl(i).planned_quantity;
1097 end if;
1098
1099 END LOOP;
1100 if(l_item_tbl_to_update_rec.COUNT > 0) then
1101 for k in l_item_tbl_to_update_rec.FIRST .. l_item_tbl_to_update_rec.LAST
1102 LOOP
1103 UPDATE mrp_recommendations
1104 SET quantity_in_process =
1105 nvl(quantity_in_process,0) + l_item_tbl_to_update_rec(k).planned_quantity
1106 WHERE transaction_id = l_item_tbl_to_update_rec(k).demand_source_line;
1107 END LOOP;
1108 end if;
1109
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 o_return_code := 1;
1113 return;
1114
1115 END update_mrp_recommendations;
1116
1117
1118 /******************************************************************
1119 * Used to insert all the schedule in the table *
1120 ******************************************************************/
1121 PROCEDURE insert_schedules (
1122 i_schedules_tbl IN wip_flow_schedule_tbl,
1126 BEGIN
1123 o_return_code OUT NOCOPY NUMBER) IS
1124 l_index NUMBER;
1125
1127
1128 if(i_schedules_tbl.COUNT > 0) then
1129 l_index := i_schedules_tbl.FIRST;
1130 LOOP
1131 insert into wip_flow_schedules
1132 (
1133 scheduled_flag,
1134 wip_entity_id,
1135 organization_id,
1136 last_update_date,
1137 last_updated_by,
1138 creation_date,
1139 created_by,
1140 last_update_login,
1141 request_id,
1142 program_application_id,
1143 program_id,
1144 program_update_date,
1145 primary_item_id,
1146 class_code,
1147 scheduled_start_date,
1148 date_closed,
1149 planned_quantity,
1150 quantity_completed,
1151 mps_scheduled_completion_date,
1152 mps_net_quantity,
1153 bom_revision,
1154 routing_revision,
1155 bom_revision_date,
1156 routing_revision_date,
1157 alternate_bom_designator,
1158 alternate_routing_designator,
1159 completion_subinventory,
1160 completion_locator_id,
1161 material_account,
1162 material_overhead_account,
1163 resource_account,
1164 outside_processing_account,
1165 material_variance_account,
1166 resource_variance_account,
1167 outside_proc_variance_account,
1168 std_cost_adjustment_account,
1169 overhead_account,
1170 overhead_variance_account,
1171 demand_class,
1172 scheduled_completion_date,
1173 schedule_group_id,
1174 build_sequence,
1175 line_id,
1176 project_id,
1177 task_id,
1178 status,
1179 schedule_number,
1180 attribute_category,
1181 attribute1,
1182 attribute2,
1183 attribute3,
1184 attribute4,
1185 attribute5,
1186 attribute6,
1187 attribute7,
1188 attribute8,
1189 attribute9,
1190 attribute10,
1191 attribute11,
1192 attribute12,
1193 attribute13,
1194 attribute14,
1195 attribute15,
1196 demand_source_header_id,
1197 demand_source_line,
1198 demand_source_delivery,
1199 demand_source_type,
1200 kanban_card_id,
1201 quantity_scrapped
1202 )
1203 values
1204 (
1205 i_schedules_tbl(l_index).scheduled_flag,
1206 i_schedules_tbl(l_index).wip_entity_id,
1207 i_schedules_tbl(l_index).organization_id,
1208 i_schedules_tbl(l_index).last_update_date,
1209 i_schedules_tbl(l_index).last_updated_by,
1210 i_schedules_tbl(l_index).creation_date,
1211 i_schedules_tbl(l_index).created_by,
1212 i_schedules_tbl(l_index).last_update_login,
1213 i_schedules_tbl(l_index).request_id,
1214 i_schedules_tbl(l_index).program_application_id,
1215 i_schedules_tbl(l_index).program_id,
1216 i_schedules_tbl(l_index).program_update_date,
1217 i_schedules_tbl(l_index).primary_item_id,
1218 i_schedules_tbl(l_index).class_code,
1222 i_schedules_tbl(l_index).quantity_completed,
1219 i_schedules_tbl(l_index).scheduled_start_date,
1220 i_schedules_tbl(l_index).date_closed,
1221 i_schedules_tbl(l_index).planned_quantity,
1223 i_schedules_tbl(l_index).mps_scheduled_completion_date,
1224 i_schedules_tbl(l_index).mps_net_quantity,
1225 i_schedules_tbl(l_index).bom_revision,
1226 i_schedules_tbl(l_index).routing_revision,
1227 i_schedules_tbl(l_index).bom_revision_date,
1228 i_schedules_tbl(l_index).routing_revision_date,
1229 i_schedules_tbl(l_index).alternate_bom_designator,
1230 i_schedules_tbl(l_index).alternate_routing_designator,
1231 i_schedules_tbl(l_index).completion_subinventory,
1232 i_schedules_tbl(l_index).completion_locator_id,
1233 i_schedules_tbl(l_index).material_account,
1234 i_schedules_tbl(l_index).material_overhead_account,
1235 i_schedules_tbl(l_index).resource_account,
1236 i_schedules_tbl(l_index).outside_processing_account,
1237 i_schedules_tbl(l_index).material_variance_account,
1238 i_schedules_tbl(l_index).resource_variance_account,
1239 i_schedules_tbl(l_index).outside_proc_variance_account,
1240 i_schedules_tbl(l_index).std_cost_adjustment_account,
1241 i_schedules_tbl(l_index).overhead_account,
1242 i_schedules_tbl(l_index).overhead_variance_account,
1246 i_schedules_tbl(l_index).build_sequence,
1243 i_schedules_tbl(l_index).demand_class,
1244 i_schedules_tbl(l_index).scheduled_completion_date,
1245 i_schedules_tbl(l_index).schedule_group_id,
1247 i_schedules_tbl(l_index).line_id,
1248 i_schedules_tbl(l_index).project_id,
1249 i_schedules_tbl(l_index).task_id,
1250 i_schedules_tbl(l_index).status,
1251 i_schedules_tbl(l_index).schedule_number,
1252 i_schedules_tbl(l_index).attribute_category,
1253 i_schedules_tbl(l_index).attribute1,
1254 i_schedules_tbl(l_index).attribute2,
1255 i_schedules_tbl(l_index).attribute3,
1256 i_schedules_tbl(l_index).attribute4,
1257 i_schedules_tbl(l_index).attribute5,
1258 i_schedules_tbl(l_index).attribute6,
1259 i_schedules_tbl(l_index).attribute7,
1260 i_schedules_tbl(l_index).attribute8,
1261 i_schedules_tbl(l_index).attribute9,
1262 i_schedules_tbl(l_index).attribute10,
1263 i_schedules_tbl(l_index).attribute11,
1264 i_schedules_tbl(l_index).attribute12,
1265 i_schedules_tbl(l_index).attribute13,
1266 i_schedules_tbl(l_index).attribute14,
1267 i_schedules_tbl(l_index).attribute15,
1268 i_schedules_tbl(l_index).demand_source_header_id,
1269 i_schedules_tbl(l_index).demand_source_line,
1270 i_schedules_tbl(l_index).demand_source_delivery,
1271 i_schedules_tbl(l_index).demand_source_type,
1272 i_schedules_tbl(l_index).kanban_card_id,
1273 i_schedules_tbl(l_index).quantity_scrapped
1274 );
1275 EXIT WHEN l_index = i_schedules_tbl.LAST;
1276 l_index := i_schedules_tbl.next(l_index);
1277 END LOOP;
1278 end if;
1279 o_return_code := 0;
1280 return;
1281
1282
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 o_return_code := 1;
1286
1287 END insert_schedules;
1288
1289
1290 /******************************************************************
1291 * To get component availability of a sequencing task *
1292 ******************************************************************/
1293 PROCEDURE Init_Component_Avail(p_seq_task_id IN NUMBER,
1294 p_organization_id IN NUMBER,
1295 p_from_date IN DATE,
1296 p_to_date IN DATE,
1297 x_err_code OUT NOCOPY NUMBER,
1298 x_err_msg OUT NOCOPY VARCHAR
1299 ) IS
1300 l_status VARCHAR2(2);
1301 l_msg_count NUMBER;
1302 l_msg_data VARCHAR2(240);
1303 l_index NUMBER:=1;
1304 l_ignore_line_id_tbl flm_supply_demand.number_tbl_type;
1305
1306 -- Modified for Lot Based Material Support.
1307 -- We read the list of component in this procedure and pass
1308 -- the list of component to flm_supply_demand.read_comp_avail.
1309 -- Cursor for getting component_id for CONSTRAINT_TYPE = 7
1310 -- (Component Availability constraint)
1311 CURSOR item_list IS
1312 SELECT distinct(ATTRIBUTE_VALUE1_NUM) inventory_item_id
1313 FROM FLM_SEQ_TASK_CONSTRAINTS
1314 WHERE SEQ_TASK_ID = p_seq_task_id
1315 AND ORGANIZATION_ID = p_organization_id
1316 AND CONSTRAINT_TYPE = 7;
1317
1318 CURSOR line_id_list IS
1319 SELECT line_id
1320 FROM FLM_SEQ_TASK_LINES
1321 WHERE SEQ_TASK_ID = p_seq_task_id;
1322
1323 Begin
1324 x_err_code := 0;
1325
1326 l_index := 1;
1327 FOR item_list_rec in item_list LOOP
1328 g_components(l_index) := item_list_rec.inventory_item_id;
1329 l_index := l_index+1;
1330 END LOOP;
1331
1332 -- Added to ignore demand that comes from the given line_id.
1333 l_index := 1;
1334 FOR line_id_list_rec IN line_id_list LOOP
1335 l_ignore_line_id_tbl(l_index) := line_id_list_rec.line_id;
1336 l_index := l_index+1;
1337 END LOOP;
1338
1339 flm_supply_demand.read_comp_avail(
1340 g_components,
1341 p_organization_id,
1342 p_from_date,
1343 p_to_date,
1344 l_ignore_line_id_tbl,
1345 g_qtys,
1346 l_status,
1347 l_msg_count,
1348 l_msg_data
1349 );
1350
1351 g_components_index := 1;
1352
1353 EXCEPTION
1354 WHEN OTHERS THEN
1355 x_err_msg := 'Unexpected SQL Error: '||sqlerrm;
1356 x_err_code := -1;
1357
1358 End Init_Component_Avail;
1359
1360
1361
1362 /******************************************************************
1363 * To get component availability list at a size of p_batch_size *
1364 ******************************************************************/
1365 PROCEDURE Get_Component_Avail(
1366 p_batch_size IN NUMBER,
1367 x_ids OUT NOCOPY number_tbl_type,
1368 x_qtys OUT NOCOPY number_tbl_type,
1369 x_found IN OUT NOCOPY NUMBER,
1370 x_done_flag OUT NOCOPY INTEGER,
1371 x_err_code OUT NOCOPY NUMBER,
1372 x_err_msg OUT NOCOPY VARCHAR
1373 ) IS
1374 l_size NUMBER := 0;
1375 Begin
1376 x_err_code := 0;
1377 x_found := 0;
1378 x_done_flag := 0;
1379
1380 WHILE (g_components_index <= g_components.COUNT and l_size < p_batch_size) LOOP
1381 x_ids(l_size+1) := g_components(g_components_index);
1382 x_qtys(l_size+1) := g_qtys(g_components_index);
1383 g_components_index := g_components_index + 1;
1384 l_size := l_size + 1;
1385 END LOOP;
1386
1387 x_found := l_size;
1388
1392
1389 if (g_components_index > g_components.COUNT) then
1390 x_done_flag := 1;
1391 end if;
1393 EXCEPTION
1394 WHEN OTHERS THEN
1395 x_err_msg := 'Unexpected SQL Error: '||sqlerrm;
1396 x_err_code := -1;
1397
1398 End Get_Component_Avail;
1399
1400
1401 END flm_seq_reader_writer;