[Home] [Help]
PACKAGE BODY: APPS.GMP_APS_DS_PULL
Source
1 PACKAGE BODY GMP_APS_DS_PULL AS
2 /* $Header: GMPPLDSB.pls 120.46.12020000.6 2013/02/28 11:34:13 vkinduri ship $ */
3
4 /* Define Exceptions */
5 invalid_string_value EXCEPTION;
6 invalid_gmp_uom_profile EXCEPTION;
7
8 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('GMP_DEBUG_ENABLED'),'N'); -- BUG: 8420747
9
10 /* Record definition for the a line in a production order. */
11 TYPE product_typ IS RECORD(
12 batch_no VARCHAR2(32),
13 plant_code VARCHAR2(4),
14 batch_id PLS_INTEGER,
15 x_batch_id PLS_INTEGER, /* B1177070 added encoded key */
16 /* nsinghi INVCONV Start */
17 /* WIP Whse no longer used. */
18 /* wip_whse_code VARCHAR2(4), */
19 /* nsinghi INVCONV End */
20 mtl_org_id PLS_INTEGER,
21 routing_id PLS_INTEGER,
22 start_date DATE,
23 end_date DATE,
24 actual_start_date DATE, -- Bug: 8624913
25 trans_date DATE,
26 batch_status PLS_INTEGER,
27 batch_type PLS_INTEGER,
28 /* nsinghi INVCONV Start */
29 /* organization_id PLS_INTEGER,
30 whse_code VARCHAR2(4),
31 item_id PLS_INTEGER, */ /* Give a Unique Item Id Name */
32 /* nsinghi INVCONV End */
33 line_id PLS_INTEGER,
34 line_no PLS_INTEGER, /* B2919303 */
35 tline_no PLS_INTEGER, /* B2953953 - CoProducts */
36 line_type PLS_INTEGER,
37 tline_type PLS_INTEGER, /* B2953953 - CoProducts */
38 qty NUMBER,
39 uom_conv_factor NUMBER,
40 matl_item_id PLS_INTEGER, /* B1992371 for GME Changes */
41 recipe_item_id PLS_INTEGER, /* B1992371 for GME Changes */
42 poc_ind VARCHAR2(1), /* B1992371, B2239948 for GME Changes */
43 firmed_ind PLS_INTEGER, /* B2821248 - Firmed Ind is added */
44 batchstep_no PLS_INTEGER, /* B2919303 StepNo */
45 -- matl_qty NUMBER,
46 requested_completion_date DATE,
47 schedule_priority PLS_INTEGER,
48 from_op_seq_id PLS_INTEGER,
49 Minimum_Transfer_Qty NUMBER,
50 Minimum_Time_Offset NUMBER,
51 Maximum_Time_Offset NUMBER,
52 from_op_seq_num PLS_INTEGER
53 );
54
55 TYPE product_tbl IS TABLE OF product_typ INDEX by BINARY_INTEGER;
56 prod_tab product_tbl;
57
58 /* definition for the resource data of a production order */
59 TYPE rsrc_rec IS RECORD(
60 batch_id PLS_INTEGER,
61 x_batch_id PLS_INTEGER, /* B1177070 added encoded key */
62 batchstep_no PLS_INTEGER, /* B1224660 added batchstep to record */
63 seq_dep_ind PLS_INTEGER,
64 prim_rsrc_ind_order PLS_INTEGER,
65 resources VARCHAR2(16),
66 instance_number PLS_INTEGER,
67 tran_seq_dep PLS_INTEGER,
68 plan_start_date DATE,
69 /* plant_code VARCHAR2(4), nsinghi INVCONV */
70 organization_id PLS_INTEGER, /* nsinghi INVCONV End */
71 prim_rsrc_ind PLS_INTEGER,
72 resource_id PLS_INTEGER,
73 x_resource_id PLS_INTEGER, /* B1177070 added encoded key */
74 activity VARCHAR2(16), /* NAVIN: Remove this column. */
75 operation_no VARCHAR2(16), /* NAVIN: Remove this column. */
76 oprn_vers NUMBER,
77 plan_rsrc_count PLS_INTEGER,
78 actual_rsrc_count PLS_INTEGER,
79 actual_start_date DATE,
80 plan_cmplt_date DATE,
81 actual_cmplt_date DATE,
82 step_status PLS_INTEGER,
83 resource_usage NUMBER,
84 resource_instance_usage NUMBER,
85 eqp_serial_number VARCHAR2(30), /* Bug 5639879 */
86 scale_type PLS_INTEGER,
87 capacity_constraint PLS_INTEGER ,
88 plan_step_qty NUMBER,
89 min_xfer_qty NUMBER,
90 material_ind PLS_INTEGER,
91 schedule_flag PLS_INTEGER,
92 -- offset_interval NUMBER,
93 act_start_date DATE,
94 utl_eff NUMBER,
95 bs_activity_id PLS_INTEGER,
96 --NAVIN: START new field (added for 11.1.1.3 of Process Execution APS Patchset J.1 TDD)
97 group_sequence_id PLS_INTEGER,
98 group_sequence_number PLS_INTEGER,
99 firm_type PLS_INTEGER,
100 setup_id PLS_INTEGER,
101 minimum_capacity NUMBER,
102 maximum_capacity NUMBER,
103 sequence_dependent_usage NUMBER,
104 original_seq_num NUMBER,
105 org_step_status PLS_INTEGER,
106 plan_charges PLS_INTEGER,
107 plan_rsrc_usage NUMBER,
108 actual_rsrc_usage NUMBER,
109 batchstep_id PLS_INTEGER, /* Navin 6/23/2004 Added for resource charges*/
110 mat_found PLS_INTEGER,
111 breakable_activity_flag PLS_INTEGER,
112 usage_uom VARCHAR2(4), /*Sowmya - FDD changes - Alternate resources */
113 step_qty_uom VARCHAR2(3), /* Sowmya - FDD changes- Step Quantity UOM */
114 equp_item_id PLS_INTEGER , /* Sowmya- FDD changes - Resources Instances */
115 gmd_rsrc_count PLS_INTEGER, /* Sowmya- FDD changes - Resources req and Alt */
116 step_start_date DATE, /* nsinghi- job_operations.reco_start_date */
117 step_end_date DATE, /* nsinghi- job_operations.reco_completion_date */
118 efficiency NUMBER /*B4320561 - sowsubra */
119 );
120
121 TYPE rsrc_dtl_tbl IS TABLE OF rsrc_rec INDEX by BINARY_INTEGER;
122 rsrc_tab rsrc_dtl_tbl;
123
124 /* Record and table definition for the MPS schedule details and the items and
125 orgs that are associated by plant/whse eff. The schedule are used for MDS
126 demand
127 */
128 TYPE sched_dtl_rec IS RECORD(
129 schedule VARCHAR2(16),
130 schedule_id PLS_INTEGER,
131 order_ind PLS_INTEGER,
132 stock_ind PLS_INTEGER,
133 whse_code VARCHAR2(4),
134 orgn_code VARCHAR2(4),
135 organization_id PLS_INTEGER,
136 inventory_item_id PLS_INTEGER);
137
138 TYPE sched_dtl_tbl IS TABLE OF sched_dtl_rec INDEX by BINARY_INTEGER;
139 sched_dtl_tab sched_dtl_tbl;
140
141 /* Record and table definition for forecast detals */
142 TYPE fcst_dtl_rec IS RECORD(
143 inventory_item_id PLS_INTEGER,
144 organization_id PLS_INTEGER,
145 forecast_id PLS_INTEGER,
146 forecast VARCHAR2(17),
147 orgn_code VARCHAR2(4),
148 trans_date DATE,
149 trans_qty NUMBER,
150 consumed_qty NUMBER,
151 use_fcst_flag NUMBER);
152
153 TYPE fcst_dtl_tbl IS TABLE OF fcst_dtl_rec INDEX by BINARY_INTEGER;
154 fcst_dtl_tab fcst_dtl_tbl;
155
156 /* Record and table definition for sales order detals */
157 TYPE sales_dtl_rec IS RECORD(
158 inventory_item_id PLS_INTEGER,
159 organization_id PLS_INTEGER,
160 orgn_code VARCHAR2(4),
161 order_no VARCHAR2(32),
162 line_id PLS_INTEGER,
163 net_price NUMBER,
164 sched_shipdate DATE,
165 request_date DATE, /* B2971996 */
166 trans_qty NUMBER);
167
168 TYPE sales_dtl_tbl IS TABLE OF sales_dtl_rec INDEX by BINARY_INTEGER;
169 sales_dtl_tab sales_dtl_tbl;
170
171 /* Record and table definition for schedule forecast association */
172 TYPE fcst_assoc_rec IS RECORD(
173 schedule_id PLS_INTEGER,
174 forecast_id PLS_INTEGER);
175
176 TYPE fcst_assoc_tbl IS TABLE OF fcst_assoc_rec INDEX by BINARY_INTEGER;
177 SCHD_FCST_DTL_TAB fcst_assoc_tbl;
178
179 /* Record and table definition for designators */
180 TYPE desig_rec IS RECORD(
181 designator VARCHAR2(15),
182 schedule VARCHAR2(17),
183 orgn_code VARCHAR2(4),
184 whse_code VARCHAR2(4),
185 organization_id PLS_INTEGER);
186
187 TYPE desig_tbl IS TABLE OF desig_rec INDEX by BINARY_INTEGER;
188 desig_tab desig_tbl;
189
190 TYPE stp_chg_typ is RECORD(
191 wip_entity_id PLS_INTEGER,
192 operation_seq_id PLS_INTEGER,
193 resource_id PLS_INTEGER,
194 charge_num PLS_INTEGER,
195 organization_id PLS_INTEGER,
196 operation_seq_no PLS_INTEGER,
197 resource_seq_num PLS_INTEGER,
198 charge_quantity NUMBER ,
199 charge_start_dt_time DATE ,
200 charge_end_dt_time DATE
201 );
202
203 TYPE stp_chg_tab IS TABLE OF stp_chg_typ INDEX by BINARY_INTEGER;
204 stp_chg_tbl stp_chg_tab;
205
206 /* NAVIN :- Alternate Resource */
207 /* NAVIN: Alternate Resource selection */
208 TYPE gmp_alt_resource_typ IS RECORD
209 (
210 prim_resource_id PLS_INTEGER,
211 alt_resource_id PLS_INTEGER,
212 runtime_factor NUMBER, /* B2353759,alternate runtime_factor */
213 preference PLS_INTEGER, /* B5688153 Prod spec alternates */
214 inventory_item_id PLS_INTEGER /* B5688153 Prod spec alternates */
215 );
216 TYPE gmp_alt_resource_tbl IS TABLE OF gmp_alt_resource_typ INDEX by BINARY_INTEGER;
217 rtg_alt_rsrc_tab gmp_alt_resource_tbl;
218
219 /* Global Variable definitions */
220 null_value VARCHAR2(2) := NULL;
221 desig_count PLS_INTEGER := 0;
222 gfcst_cnt PLS_INTEGER := 0;
223 gso_cnt PLS_INTEGER := 0;
224 gschd_fcst_cnt PLS_INTEGER := 0;
225 g_instance_id PLS_INTEGER := 0 ;
226 gitem_size PLS_INTEGER := 0;
227 gfcst_size PLS_INTEGER := 0;
228 gso_size PLS_INTEGER := 0;
229 gschd_fcst_size PLS_INTEGER := 0;
230 g_item_tbl_position PLS_INTEGER := 0;
231 gcurrent_designator VARCHAR2(10) := NULL;
232 g_delimiter VARCHAR2(4) ;
233 gprod_size PLS_INTEGER := 0;
234 grsrc_size PLS_INTEGER := 0;
235 g_rsrc_cnt INTEGER ;
236 stp_chg_num PLS_INTEGER ;
237 stp_chg_cursor VARCHAR2(20000);
238 statement_alt_resource VARCHAR2(32000) := NULL; /* NAVIN :- added for alternate resource */
239 alt_rsrc_size PLS_INTEGER; /* NAVIN :- : Number of rows in Alternate Resource */
240
241 /* Sowmya - As per the latest FDD changes */
242 shld_res_passed BOOLEAN;
243 converted_usage NUMBER;
244 l_res_inst_process NUMBER;
245
246 /* ------------------- Requirement declaration ---------------------*/
247
248 TYPE number_idx_tbl IS TABLE OF number INDEX BY BINARY_INTEGER;
249 empty_num_tbl number_idx_tbl;
250 rr_organization_id number_idx_tbl;
251 s_organization_id number_idx_tbl;
252 d_organization_id number_idx_tbl;
253 f_organization_id number_idx_tbl;
254 i_organization_id number_idx_tbl;
255 arr_organization_id number_idx_tbl; /* alternate resource declaration */
256 rr_activity_group_id number_idx_tbl; /* B3995361 rpatangy */
257
258 rr_sr_instance_id number_idx_tbl;
259 s_sr_instance_id number_idx_tbl;
260 d_sr_instance_id number_idx_tbl;
261 f_sr_instance_id number_idx_tbl;
262 i_sr_instance_id number_idx_tbl;
263 stp_instance_id number_idx_tbl;
264 arr_sr_instance_id number_idx_tbl; /* alternate resource declaration */
265
266 rr_supply_id number_idx_tbl;
267
268 rr_resource_seq_num number_idx_tbl;
269
270 rr_resource_id number_idx_tbl;
271
272 TYPE date_idx_tbl IS TABLE OF date INDEX BY BINARY_INTEGER;
273 empty_date_tbl date_idx_tbl;
274 rr_start_date date_idx_tbl;
275 rr_end_date date_idx_tbl;
276
277 rr_opr_hours_required number_idx_tbl;
278 rr_usage_rate number_idx_tbl;
279 rr_assigned_units number_idx_tbl;
280
281 rr_department_id number_idx_tbl;
282 rr_wip_entity_id number_idx_tbl;
283 rr_opr_status number_idx_tbl; -- Vpedarla 9319734
284 d_wip_entity_id number_idx_tbl;
285 f_wip_entity_id number_idx_tbl;
286
287 rr_operation_seq_num number_idx_tbl;
288 s_operation_seq_num number_idx_tbl;
289 d_operation_seq_num number_idx_tbl;
290
291 rr_firm_flag number_idx_tbl;
292 rr_minimum_transfer_quantity number_idx_tbl;
293 rr_parent_seq_num number_idx_tbl;
294 rr_schedule_flag number_idx_tbl;
295 rr_hours_expended number_idx_tbl;
296 rr_breakable_activity_flag number_idx_tbl ;
297 rr_unadjusted_resource_hrs number_idx_tbl ; /* B4320561 - sowsubra */
298 rr_touch_time number_idx_tbl; /* B4320561 - sowsubra */
299 rr_plan_step_qty number_idx_tbl; /*Sowmya - As per latest FDD changes */
300
301 /* B5338598 rpatangy start */
302 TYPE batch_activity IS TABLE OF VARCHAR2(16)
303 INDEX BY BINARY_INTEGER;
304 empty_batch_activity batch_activity ;
305 rr_activity_name batch_activity ;
306 rr_operation_no batch_activity ;
307 /* B5338598 rpatangy End */
308
309 TYPE res_step_qty_uom IS TABLE OF VARCHAR2(3)
310 INDEX BY BINARY_INTEGER;
311 empty_step_qty_uom res_step_qty_uom ;
312 rr_step_qty_uom res_step_qty_uom; /*Sowmya - As per latest FDD changes */
313
314 rr_product_item_id number_idx_tbl ; /* B4777532 - sowsubra */
315
316 rr_gmd_rsrc_cnt number_idx_tbl; /*Sowmya - As per latest FDD changes */
317 rr_operation_sequence_id number_idx_tbl ; /* B5461922 rpatangy */
318 jo_wip_entity_id number_idx_tbl;
319 jo_instance_id number_idx_tbl;
320 jo_operation_seq_num number_idx_tbl;
321 jo_operation_sequence_id number_idx_tbl;
322 jo_organization_id number_idx_tbl;
323 jo_department_id number_idx_tbl;
324 jo_minimum_transfer_quantity number_idx_tbl;
325
326 TYPE recommended_typ IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
327 empty_jo_recommended recommended_typ;
328 jo_recommended recommended_typ;
329 jo_network_start_end recommended_typ;
330
331 jo_reco_start_date date_idx_tbl;
332 jo_reco_completion_date date_idx_tbl;
333
334 rr_index NUMBER := 0 ;
335 arr_index NUMBER := 0 ;
336 si_index NUMBER := 0 ;
337 inst_indx NUMBER := 0 ; /* NAVIN :- - For Resource Instance */
338 jo_index NUMBER := 0; /* NAMIT :- For msc_st_job_operations */
339
340 /* ------------------- Supply declaration ---------------------*/
341
342 s_plan_id number_idx_tbl ;
343
344 s_inventory_item_id number_idx_tbl ;
345 d_inventory_item_id number_idx_tbl ;
346 f_inventory_item_id number_idx_tbl ;
347
348 s_new_schedule_date date_idx_tbl;
349 s_old_schedule_date date_idx_tbl;
350 s_new_wip_start_date date_idx_tbl;
351 s_actual_start_date date_idx_tbl; -- Bug: 8624913
352 s_old_wip_start_date date_idx_tbl;
353 s_lunit_completion_date date_idx_tbl;
354
355 s_disposition_id number_idx_tbl;
356
357 s_order_type number_idx_tbl;
358
359 TYPE order_number IS TABLE OF msc_st_supplies.order_number%TYPE
360 INDEX BY BINARY_INTEGER;
361 s_order_number order_number ;
362 empty_sorder_number order_number ;
363
364 s_new_order_quantity number_idx_tbl;
365 s_old_order_quantity number_idx_tbl;
366 s_firm_planned_type number_idx_tbl;
367 s_process_seq_id number_idx_tbl ; -- Bug 8349005 Vpedarla
368
369 TYPE wip_entity_name IS TABLE OF msc_st_supplies.wip_entity_name%TYPE INDEX BY BINARY_INTEGER;
370 s_wip_entity_name wip_entity_name ;
371 empty_swip_entity_name wip_entity_name ;
372
373 TYPE lot_number IS TABLE OF msc_st_supplies.lot_number%TYPE INDEX BY BINARY_INTEGER;
374 s_lot_number lot_number ;
375
376 s_expiration_date date_idx_tbl;
377 s_firm_quantity number_idx_tbl;
378 s_firm_date date_idx_tbl;
379 s_by_product_using_assy_id number_idx_tbl ;
380 s_requested_completion_date date_idx_tbl;
381 s_schedule_priority number_idx_tbl;
382
383 /*B5100481 - 16 for pending, 3 for wip*/
384 s_wip_status_code number_idx_tbl;
385
386 /* NAVIN: MTQ with Hardlinks */
387 stp_var_itm_instance_id number_idx_tbl;
388
389 stp_var_itm_from_op_seq_id number_idx_tbl;
390 stp_var_itm_wip_entity_id number_idx_tbl;
391 stp_var_itm_from_item_id number_idx_tbl;
392 stp_var_min_tran_qty number_idx_tbl;
393 stp_var_itm_min_tm_off number_idx_tbl;
394 stp_var_itm_max_tm_off number_idx_tbl;
395 stp_var_itm_from_op_seq_num number_idx_tbl;
396 stp_var_itm_organization_id number_idx_tbl;
397
398 s_index NUMBER := 0 ;
399
400 /* ---------------- Demands declaration ----------------------*/
401
402 d_assembly_item_id number_idx_tbl ;
403 f_assembly_item_id number_idx_tbl ;
404
405 d_demand_date date_idx_tbl;
406 f_demand_date date_idx_tbl;
407
408 d_requirement_quantity number_idx_tbl;
409 f_requirement_quantity number_idx_tbl;
410 d_demand_type number_idx_tbl;
411 f_demand_type number_idx_tbl;
412 d_origination_type number_idx_tbl;
413 f_origination_type number_idx_tbl;
414
415 TYPE demand_schedule IS TABLE OF msc_st_demands.demand_schedule_name%TYPE
416 INDEX BY BINARY_INTEGER;
417 empty_demand_schedule demand_schedule;
418 d_demand_schedule demand_schedule;
419 f_demand_schedule demand_schedule;
420
421 TYPE dorder_number IS TABLE OF msc_st_demands.order_number%TYPE INDEX BY BINARY_INTEGER;
422 empty_dorder_number dorder_number ;
423 d_order_number dorder_number ;
424 f_order_number dorder_number ;
425
426 TYPE dwip_entity_name IS TABLE OF msc_st_demands.wip_entity_name%TYPE INDEX BY BINARY_INTEGER;
427 empty_dwip_entity_name dwip_entity_name ;
428 d_wip_entity_name dwip_entity_name ;
429 f_wip_entity_name dwip_entity_name ;
430
431 d_selling_price number_idx_tbl ;
432 f_selling_price number_idx_tbl ;
433
434 d_request_date date_idx_tbl;
435 f_request_date date_idx_tbl;
436
437 TYPE forecast_designator IS TABLE OF msc_st_demands.forecast_designator%TYPE
438 INDEX BY BINARY_INTEGER;
439 f_forecast_designator forecast_designator ;
440
441 f_sales_order_line_id number_idx_tbl;
442
443 /*B5100481 - 16 for pending, 3 for wip*/
444 d_wip_status_code number_idx_tbl;
445
446 d_index NUMBER := 0 ;
447
448 /* ---------------- Designator declaration ----------------------*/
449 TYPE designator IS TABLE OF msc_st_designators.designator%TYPE INDEX BY BINARY_INTEGER;
450 i_designator designator ;
451
452 TYPE forecast_set IS TABLE OF msc_st_designators.forecast_set%TYPE INDEX BY BINARY_INTEGER;
453 i_forecast_set forecast_set;
454
455 TYPE description IS TABLE OF msc_st_designators.description%TYPE INDEX BY BINARY_INTEGER;
456 i_description description ;
457
458 i_disable_date date_idx_tbl;
459 i_consume_forecast number_idx_tbl;
460 i_backward_update_time_fence number_idx_tbl;
461 i_forward_update_time_fence number_idx_tbl;
462
463 i_index NUMBER := 0 ;
464
465 stp_chg_department_id number_idx_tbl;
466 stp_chg_resource_id number_idx_tbl;
467 stp_chg_organization_id number_idx_tbl;
468 stp_chg_wip_entity_id number_idx_tbl;
469 stp_chg_operation_seq_id number_idx_tbl;
470 stp_chg_operation_seq_no number_idx_tbl;
471 stp_chg_resource_seq_num number_idx_tbl;
472 stp_chg_charge_num number_idx_tbl;
473 stp_chg_charge_quanitity number_idx_tbl;
474 stp_chg_charge_start_dt_time date_idx_tbl;
475 stp_chg_charge_end_dt_time date_idx_tbl;
476
477
478 --------------------------NAVIN: Sequence Dependencies--------------------------
479
480 rr_sequence_id number_idx_tbl;
481 rr_sequence_number number_idx_tbl;
482 rr_firm_type number_idx_tbl;
483 rr_setup_id number_idx_tbl;
484 /* NAVIN: new column for Operation Charges*/
485 rr_min_capacity number_idx_tbl;
486 rr_max_capacity number_idx_tbl;
487 rr_original_seq_num number_idx_tbl;
488 rr_sequence_dependent_usage number_idx_tbl;
489 rr_alternate_number number_idx_tbl;
490 rr_basis_type number_idx_tbl;
491
492 /* NAVIN :- Resource Instances start */
493 /* Resource instance */
494
495 rec_inst_supply_id number_idx_tbl;
496 rec_inst_organization_id number_idx_tbl;
497 rec_inst_sr_instance_id number_idx_tbl;
498 rec_inst_rec_resource_seq_num number_idx_tbl;
499 rec_inst_resource_id number_idx_tbl;
500 rec_inst_instance_id number_idx_tbl;
501 rec_inst_start_date date_idx_tbl;
502 rec_inst_end_date date_idx_tbl;
503 rec_inst_rsrc_instance_hours number_idx_tbl;
504 rec_inst_operation_seq_num number_idx_tbl;
505 rec_inst_department_id number_idx_tbl;
506 rec_inst_wip_entity_id number_idx_tbl;
507
508 TYPE rec_serial_number IS TABLE OF msc_st_resource_instance_reqs.serial_number%TYPE
509 INDEX BY BINARY_INTEGER;
510 empty_inst_serial_number rec_serial_number ;
511 rec_inst_serial_number rec_serial_number;
512
513 rec_inst_parent_seq_num number_idx_tbl;
514 rec_inst_original_seq_num number_idx_tbl;
515 rec_inst_equp_item_id number_idx_tbl; /* SOWMYA - As Per latest FDD changes - Resources Instances */
516
517 /* NAVIN :- Resource Instances end */
518
519 /*-------------------------- Alternate Resources -----------------------------*/
520
521 /* Sowmya - As Per the latest FDD changes :- Alternate resources declaration Start */
522 arr_wip_entity_id number_idx_tbl;
523 arr_operation_seq_num number_idx_tbl;
524 arr_res_seq_num number_idx_tbl;
525 arr_resource_id number_idx_tbl;
526 arr_alternate_num number_idx_tbl;
527 arr_usage_rate number_idx_tbl;
528 arr_assigned_units number_idx_tbl;
529 arr_department_id number_idx_tbl;
530 arr_activity_group_id number_idx_tbl;
531 arr_basis_type number_idx_tbl;
532 arr_setup_id number_idx_tbl;
533 arr_schedule_seq_num number_idx_tbl;
534 arr_maximum_assigned_units number_idx_tbl;
535 TYPE alt_resource_varchar_typ IS TABLE OF VARCHAR2(4)
536 INDEX BY BINARY_INTEGER;
537 empty_arr_uom_code alt_resource_varchar_typ;
538 arr_uom_code alt_resource_varchar_typ;
539
540 /* Sowmya - As Per latest FDD changes :- Alternate resources declaration Start */
541
542 /*-------------------------- Operation Charges-----------------------------*/
543
544 v_orgn_id NUMBER;
545 r NUMBER;
546 p NUMBER ;
547 chg_res_index NUMBER; /* NAVIN :- Resource Charges */
548 resource_usage_flag NUMBER;
549 resource_instance_usage_flag NUMBER;
550 old_rsrc_batch_id NUMBER;
551 old_rsrc_resources VARCHAR2(16);
552 old_rsrc_original_seq_num NUMBER;
553 old_instance_number NUMBER;
554 old_rsrc_inst_batch_id NUMBER;
555 old_rsrc_inst_resources VARCHAR2(16);
556 old_rsrc_inst_original_seq_num NUMBER;
557
558
559 /***********************************************************************
560 *
561 * NAME
562 * bsearch_rsrc_chg
563 *
564 * DESCRIPTION
565 * This function will search through the resource charges PL/SQL table
566 * using Binary Search.
567 *
568 * IF p_batch_id Found IN stp_chg_tbl THEN
569 * Return the last record location for p_batch_id in stp_chg_tbl.
570 * ELSE if p_batch_id NOT Found IN the stp_chg_tbl THEN
571 * Return -1
572 * END IF;
573 *
574 * HISTORY
575 * Navin Sinha
576 ************************************************************************/
577 FUNCTION bsearch_rsrc_chg ( p_batch_id IN NUMBER)
578 RETURN INTEGER IS
579
580 top INTEGER ;
581 bottom INTEGER ;
582 mid INTEGER ;
583
584 ret_loc INTEGER ;
585 BEGIN
586 top := 1;
587 bottom := stp_chg_tbl.count;
588 mid := -1 ;
589 ret_loc := -1 ;
590
591 WHILE (top <= bottom )
592 LOOP
593 mid := top + ( ( bottom - top ) / 2 );
594
595 IF p_batch_id < stp_chg_tbl(mid).wip_entity_id THEN
596 bottom := mid -1 ;
597 ELSIF p_batch_id > stp_chg_tbl(mid).wip_entity_id THEN
598 top := mid + 1 ;
599 ELSE
600 ret_loc := mid ;
601 EXIT;
602 END IF ;
603 END LOOP; /* (top <= bottom ) */
604
605 -- Identify the location of the last record for the currently processed p_batch_id in stp_chg_tbl.
606 IF ret_loc > 0 AND ret_loc <= stp_chg_tbl.count THEN
607 LOOP
608 IF ret_loc = stp_chg_tbl.count THEN
609 -- Pointer is at last record of the array.
610 Return ret_loc;
611 END IF ;
612
613 ret_loc := ret_loc + 1;
614 IF p_batch_id <> stp_chg_tbl(ret_loc).wip_entity_id THEN
615 -- Missmatch occurred hence return the previous location.
616 Return (ret_loc - 1);
617 END IF ;
618 END LOOP;
619 ELSE
620 -- Not found
621 Return -1 ;
622 END IF ;
623
624 END bsearch_rsrc_chg ;
625
626 /* **********************************************************************
627 * NAME
628 * inst_stp_chg_tbl
629 *
630 * DESCRIPTION
631 * Inserts Data into step charge staging table.
632 * HISTORY
633 * B4761946, 20-DEC-2005 Rajesh Patangya Changed the while loop logic
634 ************************************************************************/
635
636 PROCEDURE inst_stp_chg_tbl(pinstance_id IN NUMBER, p_batch_loc IN NUMBER)
637 IS
638
639 rsrc_chg_loc NUMBER;
640
641 BEGIN
642 -- Locate the batch in Resource Charge PL/SQL table, i.e stp_chg_tbl
643 -- rsrc_chg_loc will be -1 if NOT found OR it will point to last record
644 -- location for x_batch_id in stp_chg_tbl.
645 rsrc_chg_loc := bsearch_rsrc_chg(rsrc_tab(p_batch_loc).x_batch_id);
646
647 -- IF resource charges found then process....
648 IF rsrc_chg_loc > 0 THEN
649 IF prod_tab(p).firmed_ind = 1 AND
650 stp_chg_tbl(rsrc_chg_loc).charge_start_dt_time IS NULL AND
651 stp_chg_tbl(rsrc_chg_loc).charge_end_dt_time IS NULL THEN
652 -- APS decoded value as per
653 -- DECODE(rsrc_tab(p_batch_loc).scale_type,0,2,1,1,2,3);
654 rsrc_tab(p_batch_loc).scale_type := 1;
655 ELSE
656 -- Insert all the resource charge records untill the batch_id,
657 -- batchstep_id and resource_id
658 -- are same as currently processed resource record.
659 /* B4761946, Rajesh Patangya Changed the while loop logic */
660 LOOP
661 IF (rsrc_tab(p_batch_loc).x_batch_id =
662 stp_chg_tbl(rsrc_chg_loc).wip_entity_id) AND
663 (rsrc_tab(p_batch_loc).batchstep_id =
664 stp_chg_tbl(rsrc_chg_loc).operation_seq_id) AND
665 (rsrc_tab(p_batch_loc).x_resource_id =
666 stp_chg_tbl(rsrc_chg_loc).resource_id) THEN
667
668 log_message(rsrc_tab(p_batch_loc).x_batch_id || ' -- ' ||
669 stp_chg_tbl(rsrc_chg_loc).operation_seq_id || ' --'||
670 stp_chg_tbl(rsrc_chg_loc).resource_id || ' --'|| rsrc_chg_loc || '--' ||
671 stp_chg_tbl(rsrc_chg_loc).wip_entity_id );
672
673 chg_res_index := chg_res_index + 1 ;
674 stp_chg_resource_id(chg_res_index) := stp_chg_tbl(rsrc_chg_loc).resource_id ;
675 stp_chg_organization_id(chg_res_index) := stp_chg_tbl(rsrc_chg_loc).organization_id ;
676 stp_chg_department_id(chg_res_index) := ((v_orgn_id * 2) + 1) ;
677 stp_chg_wip_entity_id(chg_res_index) := stp_chg_tbl(rsrc_chg_loc).wip_entity_id ;
678 stp_chg_operation_seq_id(chg_res_index) := stp_chg_tbl(rsrc_chg_loc).operation_seq_id ;
679 stp_chg_operation_seq_no(chg_res_index) := stp_chg_tbl(rsrc_chg_loc).operation_seq_no ;
680 stp_chg_resource_seq_num(chg_res_index) := stp_chg_tbl(rsrc_chg_loc).resource_seq_num ;
681 stp_chg_charge_num(chg_res_index) := stp_chg_tbl(rsrc_chg_loc).charge_num ;
682 stp_chg_charge_quanitity(chg_res_index) := stp_chg_tbl(rsrc_chg_loc).charge_quantity ;
683 stp_chg_charge_start_dt_time(chg_res_index) := stp_chg_tbl(rsrc_chg_loc).charge_start_dt_time ;
684 stp_chg_charge_end_dt_time(chg_res_index) := stp_chg_tbl(rsrc_chg_loc).charge_end_dt_time ;
685 stp_instance_id (chg_res_index) := pinstance_id ;
686 END IF;
687
688 rsrc_chg_loc := rsrc_chg_loc - 1 ;
689
690 IF ((rsrc_chg_loc = 0) OR (rsrc_tab(p_batch_loc).x_batch_id <>
691 stp_chg_tbl(rsrc_chg_loc).wip_entity_id)) THEN
692 -- No more records to process in Step Charge PL/SQL table.
693 EXIT;
694 END IF;
695 END LOOP;
696 END IF;
697 END IF; /* rsrc_chg_loc > 0 */
698 END inst_stp_chg_tbl;
699
700 /***********************************************************************
701 *
702 * NAME
703 * Enh_bsearch_alternate_rsrc
704 *
705 *
706 * IF pprim_resource_id Found IN rtg_alt_rsrc_tab THEN
707 * Return the first record location for pprim_resource_id in rtg_alt_rsrc_tab.
708 * ELSE IF pprim_resource_id NOT Found IN rtg_alt_rsrc_tab THEN
709 * Return -1
710 * END IF;
711 *
712 * DESCRIPTION
713 * This function will search throught the alternate resource PL/SQL table
714 * using Binary Search. It is a modified Binary Search, as after finding a hit
715 * it loops back to find the first row that gave the hit.
716 * HISTORY
717 * Navin Sinha
718 ************************************************************************/
719
720 FUNCTION Enh_bsearch_alternate_rsrc ( pprim_resource_id IN NUMBER)
721 RETURN INTEGER IS
722
723 top INTEGER ;
724 bottom INTEGER ;
725 mid INTEGER ;
726
727 ret_loc INTEGER ;
728 BEGIN
729 top := 1;
730 bottom := alt_rsrc_size ;
731 mid := -1 ;
732 ret_loc := -1 ;
733
734 WHILE (top <= bottom )
735 LOOP
736 mid := top + ( ( bottom - top ) / 2 );
737
738 IF pprim_resource_id < rtg_alt_rsrc_tab(mid).prim_resource_id THEN
739 bottom := mid -1 ;
740 ELSIF pprim_resource_id > rtg_alt_rsrc_tab(mid).prim_resource_id THEN
741 top := mid + 1 ;
742 ELSE
743 ret_loc := mid ;
744 EXIT;
745 END IF ;
746 END LOOP; /* (top <= bottom ) */
747
748 -- Bring back the pointer to the first location from where the Primary resource data starts.
749 IF ret_loc >= 1 THEN
750 LOOP
751 IF ret_loc = 1 THEN
752 -- Pointer is at first location of the array.
753 Return ret_loc;
754 END IF ;
755
756 ret_loc := ret_loc - 1;
757 IF pprim_resource_id <> rtg_alt_rsrc_tab(ret_loc).prim_resource_id THEN
758 -- Missmatch occurred hence return the previous location.
759 Return (ret_loc +1);
760 END IF ;
761 END LOOP;
762 ELSE
763 -- Not found
764 Return -1 ;
765 END IF ; /* ret_loc >= 1 */
766
767 END Enh_bsearch_alternate_rsrc ;
768
769 /***********************************************************************
770 *
771 * NAME
772 * production_orders
773 *
774 * DESCRIPTION
775 * This procedure will take the production orders, batches and FPOs,
776 * that have valid item/warehouse definitions as defined in the
777 * the plant/whse eff and write them to the table msc_std_demands and \
778 * msc_st_supplies. The products and byproducts will be written as
779 * supplies and ingredients as demands
780 * HISTORY
781 * M Craig
782 * 04/03/2000 - Using mtl_organization_id instead of organization_id from
783 * - sy_orgn_mst , Bug# 1252322
784 * Sridhar 31-DEC-01 B2159482 - Added Alcoa Cursor Changes to the
785 * latest version of the package
786 * Sridhar 15-JAN-02 B1992371 Modified the Cursor with GME Changes
787 * Sridhar 27-FEB-2002 B2239948 Added correction for poc_ind comparisons
788 * Sridhar 15-MAY-2002 B2363117 Added nvl Statement for If statements with
789 * Actual_cmplt_date and Start Date
790 * Sridhar 10-JUL-2002 B2383692 Added code to take care of the last record
791 * Sridhar 10-JUL-2002 B1522576 Added code to differentiate FPO Batches
792 * Sridhar 19-MAR-2003 B2858929 Added Code to take resolve the Bug
793 * Resource Seq is incremented only if the
794 * activity is Changed
795 * Sridhar 31-MAR-2003 B2882286 Ensuring the Order so that if the last batch
796 * resource requirements are written
797 * Sridhar 30-APR-2003 B2919303 Added Operation Seq Number in msc_st_supplies
798 * and in msc_st_demands table
799 * Sridhar 09-MAY-2003 B2919303 Added line_no and included in order by clause
800 * Sridhar 12-MAY-2003 B2953953 Populated BY_PRODUCT_USING_ASSY_ID with
801 * product_line which is the assembly_item_id
802 * Navin 21-APR-2003 B3577871 ST:OSFME2: collections failing in planning data pull.
803 * Added handling of NO_DATA_FOUND Exception.
804 * And return the return_status as TRUE.
805 ************************************************************************/
806
807 PROCEDURE production_orders(
808 pdblink IN VARCHAR2,
809 pinstance_id IN NUMBER,
810 prun_date IN DATE,
811 pdelimiter IN VARCHAR2,
812 return_status IN OUT NOCOPY BOOLEAN)
813
814 IS
815
816 /* Defining the dynamic cursors to be used to retrieve data later. Production
817 details, resource details, resource warehouse, and warehouse organization */
818
819 TYPE gmp_cursor_typ IS REF CURSOR;
820 c_prod_dtl gmp_cursor_typ;
821 rsrc_dtl gmp_cursor_typ;
822 rsrc_whse gmp_cursor_typ;
823 cur_alt_resource gmp_cursor_typ; /* NAVIN :- Alternate Resource */
824 cur_rs_intance gmp_cursor_typ; /* NAVIN :- Resource Intance */
825 c_chg_cursor gmp_cursor_typ; /* NAVIN :- Resource Charges */
826 rsrc_uoms_cur gmp_cursor_typ; /* Sowmya - As per latest FDD Changes */
827 uom_code_ref gmp_cursor_typ; /* NAMIT - UOM Class */
828
829 v_prod_cursor VARCHAR2(32000) ;
830 v_rsrc_cursor VARCHAR2(32000) ;
831 sql_stmt VARCHAR2(32000) ;
832 uom_code_cursor VARCHAR2(32000);
833
834 l_charges_remaining NUMBER;
835 res_whse BOOLEAN ;
836 res_whse_id PLS_INTEGER ;
837 supply_type PLS_INTEGER ;
838 old_batch_id PLS_INTEGER;
839 product_line PLS_INTEGER ;
840 opm_product_line NUMBER ;
841 prod_line_id NUMBER ;
842 prod_plant VARCHAR2(4) ;
843 order_no VARCHAR2(37) ;
844 v_inflate_wip NUMBER ;
845 found_mtl NUMBER ;
846 i PLS_INTEGER ;
847 old_step_no NUMBER ;
848 prod_count PLS_INTEGER ;
849 resource_count PLS_INTEGER ;
850 stp_chg_count PLS_INTEGER ;
851 /* B1224660 added locals to develop resource sequence numbers */
852 v_resource_usage NUMBER ;
853 v_res_seq NUMBER ;
854 v_schedule_flag PLS_INTEGER ;
855 v_parent_seq_num NUMBER ;
856 v_seq_dep_usage NUMBER ; /* NAVIN :- Sequence Dependency */
857 found_chrg_rsrc NUMBER ; /* NAVIN :- Chargeable Resource */
858 chrg_activity NUMBER; /* NAVIN :- Chargeable Activity */
859 v_rsrc_cnt PLS_INTEGER ;
860 v_start_date DATE ;
861 v_end_date DATE ;
862 old_activity NUMBER ;
863 v_alternate NUMBER ; /* NAVIN :- added for alternate resource */
864 alternate_rsrc_loc NUMBER ; /* NAVIN :- added for alternate resource */
865 alt_cnt NUMBER ; /* NAVIN :- added for alternate resource */
866 row_count NUMBER ;
867 start_loc NUMBER ;
868 l_gmp_um_code VARCHAR2(25);
869 l_gmp_uom_class VARCHAR2(10); /* UOM Class */
870 /*Sowmya - As per latest FDD changes - Start*/
871 v_max_rsrcs NUMBER; --for collecting the max resources
872 /*Sowmya - As per latest FDD changes - End*/
873 v_activity_group_id PLS_INTEGER ; /* B3995361 rpatangy */
874 mk_alt_grp NUMBER ; /* B3995361 rpatangy */
875
876 l_process_seq_id PLS_INTEGER ; -- B8349005 Vpedarla
877
878 uom_conv_cursor VARCHAR2(32000); -- Bug: 8647592 Vpedarla
879 c_uom_conv gmp_cursor_typ ; -- Bug: 8647592 Vpedarla
880 v_new_res_usage NUMBER; -- Bug: 8647592 Vpedarla
881 v_Overyielded_wip NUMBER;
882
883 BEGIN
884 /* Initialize the values */
885 v_Overyielded_wip := 1 ;
886 v_activity_group_id := 0; /* B3995361 rpatangy */
887 mk_alt_grp := 0 ; /* B3995361 rpatangy */
888 v_prod_cursor := NULL;
889 v_rsrc_cursor := NULL;
890
891 res_whse := FALSE;
892 res_whse_id := 0;
893 supply_type := 0;
894 product_line := 0;
895 opm_product_line := 0;
896 prod_line_id := 0;
897 prod_plant := NULL;
898 order_no := NULL;
899 v_inflate_wip := 0;
900 found_mtl := 0;
901 i := 0;
902 p := 0;
903 r := 0;
904 old_step_no := 0;
905 prod_count := 1;
906 resource_count := 1;
907 stp_chg_count := 1;
908
909 /* B1224660 added locals to develop resource sequence numbers */
910 v_resource_usage := 0;
911 v_res_seq := 0;
912 v_schedule_flag := 0;
913 v_parent_seq_num := 0;
914 v_seq_dep_usage := 0; /* NAVIN :- Sequence Dependency */
915 found_chrg_rsrc := 0; /* NAVIN :- Chargeable Resource */
916 chrg_activity := -1; /* NAVIN :- Chargeable Activity */
917 chg_res_index := 0; /* NAVIN :- Resource Charges */
918 v_rsrc_cnt := 0;
919 v_start_date := NULL;
920 v_end_date := NULL;
921 old_activity := 0;
922 v_alternate := 0; /* NAVIN :- added for alternate resource */
923 alternate_rsrc_loc := 0; /* NAVIN :- added for alternate resource */
924 alt_cnt := 0; /* NAVIN :- added for alternate resource */
925
926 d_index := 0 ;
927 s_index := 0 ;
928 rr_index := 0 ;
929 arr_index := 0 ;
930 jo_index := 0;
931 gprod_size := 0 ;
932 grsrc_size := 0;
933 g_rsrc_cnt := 1;
934 si_index := 1;
935 inst_indx := 0;
936 row_count := 1; /* NAVIN :- Maintains the row count. From set of repetitive rows, only one row is inserted. */
937 start_loc := 1;
938 shld_res_passed := FALSE;
939 l_res_inst_process := 0;
940 converted_usage := 0;
941 v_max_rsrcs := 0;
942
943 l_process_seq_id := 0; -- B8349005 Vpedarla
944
945 IF return_status THEN
946 v_cp_enabled := TRUE;
947 ELSE
948 v_cp_enabled := FALSE;
949 END IF;
950
951 /* populate the org_string */
952 IF gmp_calendar_pkg.org_string(pinstance_id) THEN
953 NULL ;
954 ELSE
955 RAISE invalid_string_value ;
956 END IF;
957
958 /* Disable Formula Security Functionality */
959
960 v_sql_stmt := 'BEGIN '
961 || ' gmd_p_fs_context.set_additional_attr' || pdblink
962 || ';END;' ;
963 EXECUTE IMMEDIATE v_sql_stmt ;
964
965 BEGIN -- B8349005 Vpedarla
966 select NVL(max(process_seq_id),0) into l_process_seq_id from msc_st_supplies
967 where sr_instance_id = pinstance_id ;
968 log_message('production_orders pinstance_id = ' || pinstance_id || ' AND l_process_seq_id = ' || l_process_seq_id );
969 EXCEPTION
970 WHEN no_data_found then
971 l_process_seq_id := 0 ;
972 WHEN others then
973 l_process_seq_id := 0 ;
974 END; -- B8349005 Vpedarla
975
976
977 /* Get the profile value for inflating usage by the utilization and
978 efficiency */
979 IF NVL(fnd_profile.value('MSC_INFLATE_WIP') ,'N')= 'N' THEN
980 v_inflate_wip := 0 ;
981 ELSE
982 v_inflate_wip := 1 ;
983 END IF;
984
985 -- For alcoa Issue 13097786 Starts
986 -- Profile: Collect WiP Batches when Output is Over OR Fully yielded
987 -- Default Value = 'Yes'
988
989 IF NVL(FND_PROFILE.VALUE('GMP_WIP_BATCH_YIELD'),'N') = 'N' THEN
990 -- Do not collect ('N'), Aloca need to set it to 'No'
991 v_Overyielded_wip := 0 ;
992 log_message('GMP:Collect WiP Batches when Output is Over OR Fully yielded = No' );
993 ELSE
994 -- collect by defualt ('Y')
995 v_Overyielded_wip := 1 ;
996 log_message('GMP:Collect WiP Batches when Output is Over OR Fully yielded = Yes ' );
997 END IF;
998
999 -- For alcoa Issue 13097786 Ends
1000
1001 /* Not pick the "GMP:UOM for Hour" Profile and pick "BOM:UOM for Hour" profile. */
1002 /* bug:6710684 Vpedarla made changes to fetch the profile value from source server*/
1003 -- l_gmp_um_code := fnd_profile.VALUE('BOM:HOUR_UOM_CODE'); /* OPM UOM */
1004 l_gmp_um_code := GMP_BOM_ROUTING_PKG.get_profile_value('BOM:HOUR_UOM_CODE', pdblink );
1005 /* bug: 6710684 end of changes */
1006
1007 IF l_gmp_um_code IS NOT NULL THEN
1008 /* Get the UOM code and UOM Class corresponding to "GMP: UOM for Hour" Profile */
1009 uom_code_cursor :=
1010 ' select uom_class '
1011 ||' from mtl_units_of_measure'||pdblink
1012 ||' where uom_code = :gmp_um_code ';
1013
1014 OPEN uom_code_ref FOR uom_code_cursor USING l_gmp_um_code;
1015 FETCH uom_code_ref INTO l_gmp_uom_class;
1016 CLOSE uom_code_ref;
1017 ELSE
1018 RAISE invalid_gmp_uom_profile ;
1019 END IF;
1020 IF (l_gmp_uom_class IS NULL) THEN
1021 RAISE invalid_gmp_uom_profile ;
1022 END IF;
1023
1024 /* B2919303 - The following cursor has been modified to include
1025 batchstep_no for material txns which has release type as 3 ( auto by step )
1026 , if the rows are not release type as 3 then batchstep is taken as 0
1027 */
1028 /* B2953953 Added two temporary columns so that we can get the correct
1029 Order , Note that the Line_type is decoded so that Line_type - prod
1030 becomes 3 and is ordered first and product row is made into line_no 0
1031 */
1032 /* B2964633 - Added t.trans_date also in the Order by Clause to make sure
1033 Product comes in the first row, because product is always in the last
1034 step and therefore ordering by trans_date in the descending order */
1035 /* B3054460 - OPM/APS TO CATER FOR CHANGE TO TIME PHASED PLANNING
1036 OF MANUAL CONSUMPTION TYPE, - Considered release_type 1 also
1037 */
1038
1039 v_prod_cursor := 'SELECT'
1040 || ' h.batch_no,'
1041 || ' gp.organization_code, '
1042 || ' h.batch_id,'
1043 || ' ((h.batch_id * 2) + 1), '
1044 || ' h.organization_id, '
1045 || ' h.routing_id,'
1046 || ' h.plan_start_date, '
1047 || ' h.plan_cmplt_date end_date,'
1048 || ' h.ACTUAL_START_DATE, ' -- bug: 8624913
1049 || ' d.material_requirement_date, '
1050 || ' h.batch_status,'
1051 || ' h.batch_type,'
1052 || ' d.material_detail_id,'
1053 || ' d.line_no ,' /* B2919303 */
1054 || ' DECODE(d.inventory_item_id ,v.inventory_item_id,0,d.line_no) t_line_no,' /* B2953953 */
1055 || ' d.line_type,'
1056 || ' DECODE(d.line_type,1,3,d.line_type) t_line_type,' /* B2953953 */
1057 || ' (nvl(d.wip_plan_qty,plan_qty) - d.actual_qty ), '
1058 || ' DECODE(d.original_qty,0,Inv_Convert.Inv_Um_Convert'||pdblink
1059 || ' (d.inventory_item_id, 0,d.organization_id, NULL,1, '
1060 ||' d.dtl_um, msi.primary_uom_code,NULL,NULL),'
1061 || ' (d.original_primary_qty /d.original_qty)), '
1062 || ' d.inventory_item_id matl_item_id, '
1063 || ' v.inventory_item_id recipe_item_id, '
1064 || ' h.poc_ind, '
1065 || ' DECODE(h.firmed_ind,1,1,2), '
1066 || ' decode(d.release_type,0, -1, nvl(gbs.batchstep_no,-1)) batchstep_no,'
1067 || ' h.due_date,'
1068 || ' h.order_priority,'
1069 ||' ((gbsi.batchstep_id*2)+1) from_op_seq_id, ' /* B5461922 */
1070 || ' DECODE(d.line_type,1,gbsi.minimum_transfer_qty, NULL) , '
1071 || ' DECODE(d.line_type,1,gbsi.minimum_delay, NULL) t_minimum_delay, '
1072 || ' DECODE(d.line_type,1,gbsi.maximum_delay, NULL) t_maximum_delay,'
1073 || ' gbs.batchstep_no'
1074 || ' FROM'
1075 || ' gme_batch_header'||pdblink||' h,'
1076 || ' gme_material_details'||pdblink||' d,'
1077 || ' gme_batch_step_items'||pdblink||' gbsi,' /* 2919303 */
1078 || ' gme_batch_steps'||pdblink||' gbs,' /* 2919303 */
1079 || ' gmd_recipe_validity_rules'||pdblink||' v,'
1080 || ' mtl_parameters'||pdblink||' gp, ' -- Added this table to get the plant code
1081 || ' mtl_system_items'||pdblink||' msi '
1082 || ' WHERE'
1083 || ' h.batch_id = d.batch_id'
1084 || ' AND h.recipe_validity_rule_id = v.recipe_validity_rule_id'
1085 || ' AND EXISTS (SELECT '
1086 || ' 1 '
1087 || ' FROM '
1088 || ' gme_material_details'||pdblink||' gmd '
1089 || ' WHERE '
1090 || ' gmd.batch_id = h.batch_id '
1091 || ' AND gmd.inventory_item_id = v.inventory_item_id) '
1092 || ' AND h.organization_id = gp.organization_id '
1093 || ' AND gp.process_enabled_flag = '||''''||'Y'||'''' --invconv :- sowmya added
1094 || ' AND d.organization_id = msi.organization_id '
1095 || ' AND d.inventory_item_id = msi.inventory_item_id '
1096 || ' AND msi.process_execution_enabled_flag = '||''''||'Y'||''''
1097 || ' AND h.batch_type IN (0,10) '
1098 || ' AND d.material_detail_id = gbsi.material_detail_id (+)' /* 2919303 */
1099 || ' AND d.batch_id = gbsi.batch_id (+) ' /* 2919303 */
1100 /* Bug 8614604 Vpedarla removed the whole check for product qty */
1101 /* B3625247 - Sowmya -
1102 * When a batch that is in WIP status in which the product is completed
1103 * manually, observed that the ingredient and the resouce requirements were not
1104 * collected on to the APS. Although in WIP the batch hasn't progressed.
1105 * When a product is completed manually the transaction in ic_tran_pnd
1106 * is updated where completed_ind = 1 and trans_qty = batch_output_qty.
1107 * */
1108 /*So in this case a new transaction for the item completed manully is
1109 * inserted into ic_tran_pnd by GME. This new transaction for the product has
1110 * the completed_ind = 0 and trans_qty = 0. */
1111 /*To fetch this record added the condition in the whsere caluse and
1112 * ensured that this condition works on the product transactions alone. */
1113 -- B8342619 Rajesh Patangya
1114 ||' AND ( '
1115 || ' ( (nvl(d.wip_plan_qty,plan_qty) - nvl(d.actual_qty,0) ) > 0 ) '
1116 || ' OR '
1117 || ' ( d.inventory_item_id = v.inventory_item_id ) '
1118 || ' ) '
1119 -- || ' AND (nvl(d.wip_plan_qty,plan_qty) - d.actual_qty ) > 0 ' /*B5100675*/
1120 /*B5100675 - sowsubra - the demand for an ingredient in a batch, which has been consumed by a step
1121 that has already completed should not be passed.And hence added a where clause to filter these rows*/
1122 || ' AND gbsi.batch_id = gbs.batch_id (+) ' /* 2919303 */
1123 || ' AND gbsi.batchstep_id = gbs.batchstep_id (+)'; /* 2919303 */
1124
1125 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
1126 v_prod_cursor := v_prod_cursor
1127 ||' AND h.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
1128 END IF;
1129
1130 -- For alcoa Issue 13097786 Starts
1131 IF v_Overyielded_wip = 0 THEN
1132 v_prod_cursor := v_prod_cursor
1133 || ' AND NOT EXISTS (SELECT 1 '
1134 || ' FROM '
1135 || ' gme_material_details'||pdblink||' gmd '
1136 || ' WHERE '
1137 || ' gmd.batch_id = h.batch_id '
1138 || ' AND gmd.inventory_item_id = v.inventory_item_id '
1139 || ' AND (NVL(gmd.wip_plan_qty,gmd.plan_qty) - gmd.actual_qty ) <= 0 '
1140 || ' AND gmd.line_type = 1 ) ' ;
1141 END IF;
1142 -- For alcoa Issue 13097786 Ends
1143
1144 v_prod_cursor := v_prod_cursor
1145 || ' AND h.batch_status in (1, 2)'
1146 || ' ORDER BY h.batch_id ,t_line_type DESC ,t_line_no , d.material_requirement_date DESC ' ;
1147
1148 gmp_debug_message('v_prod_cursor - '|| v_prod_cursor);
1149
1150 OPEN c_prod_dtl FOR v_prod_cursor;
1151 LOOP
1152 FETCH c_prod_dtl INTO prod_tab(prod_count);
1153 EXIT WHEN c_prod_dtl%NOTFOUND ;
1154 prod_count := prod_count + 1;
1155 END LOOP;
1156 CLOSE c_prod_dtl ;
1157 gprod_size := prod_count - 1;
1158 log_message('Batches size is = '|| to_char(gprod_size) );
1159 time_stamp ;
1160
1161 v_rsrc_cursor := 'SELECT'
1162 || ' h.batch_id,'
1163 || ' ((r.batch_id * 2) + 1), '
1164 || ' r.batchstep_no,'
1165 || ' NVL(o.sequence_dependent_ind, -1),' /* NAVIN: Moved this column up for order by clause and changed from NVL(o.sequence_dependent_ind,0) */
1166 || ' DECODE(gs.prim_rsrc_ind, 1,1,2,2,0,3),' /* This will ensure that ordering will always have primary first */
1167 || ' gs.resources,'
1168 || ' ((gri.instance_id * 2) + 1) , ' /* SOWMYA - As Per latest FDD */
1169 || ' NVL(t.sequence_dependent_ind,0), '
1170 || ' gs.plan_start_date,'
1171 || ' h.organization_id, '
1172 || ' gs.prim_rsrc_ind,'
1173 || ' c.resource_id,'
1174 || ' ((c.resource_id * 2) + 1),'
1175 || ' o.activity, '
1176 || ' go.oprn_no, '
1177 || ' go.oprn_vers, '
1178 || ' gs.plan_rsrc_count,'
1179 || ' gs.actual_rsrc_count,'
1180 || ' gs.actual_start_date,'
1181 || ' gs.plan_cmplt_date,'
1182 || ' gs.actual_cmplt_date,'
1183 || ' r.step_status, ' /* B3995361 */
1184 || ' SUM(t.resource_usage) OVER (PARTITION BY t.doc_id, t.resources, t.line_id) resource_usage, ' -- summarized usage for the step resource
1185 || ' SUM(t.resource_usage) OVER (PARTITION BY t.doc_id, t.resources, t.line_id, t.instance_id) resource_instance_usage, ' -- summarized usage for the step resource instances
1186 || ' nvl(gri.eqp_serial_number,to_char(gri.instance_number)), '
1187 || ' DECODE(gs.scale_type,0,2,1,1,2,3), '
1188 || ' c.capacity_constraint , '
1189 || ' r.plan_step_qty, '
1190 || ' NVL(r.minimum_transfer_qty,-1), '
1191 || ' NVL(o.material_ind,0), '
1192 || ' 1 schedule_flag, '
1193 || ' o.plan_start_date, '
1194 || ' (DECODE(c.utilization,0,100,NVL(c.utilization,100))/100) * '
1195 || ' (DECODE(c.efficiency,0,100,NVL(c.efficiency,100))/100), '
1196 || ' o.batchstep_activity_id, '
1197 || ' gs.group_sequence_id,'
1198 || ' gs.group_sequence_number,'
1199 || ' nvl(gs.firm_type,0),' /*Sowmya - If null then pass 0*/
1200 || ' gs.sequence_dependent_id setup_id,'
1201 -- In the situation that value of calculate_charges at Step Resource has been
1202 -- set to 0 or NULL the values will need to be adjusted for min and max capacity
1203 -- at the resource level. min capacity will be set to 0 and the max capacity
1204 -- will be set to 99999999999999999
1205 || ' DECODE(NVL(gs.calculate_charges,0), 0, 0, gs.min_capacity) t_min_capacity,'
1206 || ' DECODE(NVL(gs.calculate_charges,0), 0, 99999999999999999, gs.max_capacity) t_max_capacity,'
1207 || ' gs.sequence_dependent_usage, '
1208 || ' gs.batchstep_resource_id,'
1209 /* NAVIN: for calculating WIP Charges */
1210 || ' r.step_status, '
1211 || ' r.plan_charges,'
1212 || ' gs.plan_rsrc_usage,'
1213 -- Bug: 6925112 Vpedarla modified the actual_rsrc_usage column inserted a NVl funtion
1214 || ' nvl(gs.actual_rsrc_usage,0) actual_rsrc_usage,'
1215 || ' ((r.batchstep_id*2)+1),' /* Navin 6/23/2004 Added for resource charges*/
1216 || ' SUM(NVL(o.material_ind,0)) OVER (PARTITION BY '
1217 || ' o.batch_id, r.batchstep_id) mat_found, '
1218 -- OPM break_ind values 0 and NULL maps to value 2 of MSC breakable_activity_flag
1219 -- and 1 maps with 1.
1220 || ' DECODE(NVL(o.break_ind,0), 1, 1, 2) breakable_activity_flag , '
1221 || ' gs.usage_um ,' --invconv :- sowmya changed this to usage um
1222 || ' r.step_qty_um ,' --invconv :- sowmya changed this to step_qty_um
1223 || ' gri.equipment_item_id ,' /* SOWMYA - As Per latest FDD changes */
1224 || ' gs.plan_rsrc_count gmd_rsrc_count,' /*passed on msc_st_resource_requirements*/
1225 || ' r.plan_start_date, ' /* populate msc_st_job_operations.reco_start_date */
1226 || ' r.plan_cmplt_date, ' /* populate msc_st_job_operations.reco_completion_date */
1227 || ' DECODE(nvl(c.efficiency,0),0,100) ' /*B4320561 - If null then resource is 100%efficient */
1228 || ' FROM'
1229 || ' mtl_units_of_measure'||pdblink||' uom, '
1230 || ' mtl_units_of_measure'||pdblink||' uom2, '
1231 || ' gme_batch_header'||pdblink||' h,'
1232 || ' gme_batch_steps'||pdblink||' r,'
1233 || ' gme_batch_step_activities'||pdblink||' o,'
1234 || ' gme_batch_step_resources'||pdblink||' gs,'
1235 || ' gme_resource_txns'||pdblink||' t , '
1236 || ' gmp_resource_instances'||pdblink||' gri, '
1237 || ' gmd_operations'||pdblink||' go, '
1238 || ' cr_rsrc_dtl'||pdblink||' c'
1239 || ' WHERE'
1240 || ' h.batch_id = r.batch_id '
1241 || ' AND r.batch_id = o.batch_id'
1242 || ' AND r.batchstep_id = o.batchstep_id'
1243 || ' AND o.batchstep_activity_id = gs.batchstep_activity_id'
1244 || ' AND o.batch_id = t.doc_id'
1245 || ' AND gs.batchstep_resource_id = t.line_id'
1246 || ' AND t.completed_ind = 0 '
1247 || ' AND NVL(t.sequence_dependent_ind,0) = 0 ' /* B4900503, Rajesh Patangya */
1248 || ' AND t.delete_mark = 0 '
1249 || ' AND t.instance_id = gri.instance_id (+) '
1250 || ' AND nvl(gri.inactive_ind,0) = 0 '
1251 || ' AND c.organization_id = h.organization_id '
1252 || ' AND c.resources = gs.resources'
1253 || ' AND c.delete_mark = 0 '
1254 || ' AND nvl(c.inactive_ind,0) = 0 '
1255 /*B4313202 COLLECTING DATA FOR COMPLETED OPERATIONS:Included a chk for step status = 3*/
1256 || ' AND r.step_status in (1, 2, 3)'
1257 || ' AND c.Schedule_Ind <> 3 ' /* NAVIN: gs.prim_rsrc_ind in (1,2) */
1258 || ' AND uom.uom_class = :gmp_uom_class '
1259 || ' AND uom.uom_code = gs.usage_um ' /* Sowmya - Alternate Resources */
1260 || ' AND uom2.uom_code = r.step_qty_um ' ;
1261
1262 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
1263 v_rsrc_cursor := v_rsrc_cursor
1264 ||' AND h.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
1265 END IF;
1266
1267 -- For alcoa Issue 13097786 Starts
1268 IF v_Overyielded_wip = 0 THEN
1269 v_rsrc_cursor := v_rsrc_cursor
1270 || ' AND NOT EXISTS (SELECT 1 '
1271 || ' FROM '
1272 || ' gme_material_details'||pdblink||' gmd '
1273 || ' WHERE '
1274 || ' gmd.batch_id = h.batch_id '
1275 || ' AND (NVL(gmd.wip_plan_qty,gmd.plan_qty) - gmd.actual_qty ) <= 0 '
1276 || ' AND gmd.line_type = 1 ) ' ;
1277 END IF;
1278 -- For alcoa Issue 13097786 Ends
1279
1280 v_rsrc_cursor := v_rsrc_cursor
1281 || ' AND go.oprn_id = r.oprn_id '
1282 || ' AND go.delete_mark = 0 '
1283 || ' ORDER BY '
1284 ||' 1,2,3,4,5,6,7,8 DESC,9'; /* NAVIN: converted to position notation in Order By*/
1285
1286 gmp_debug_message('l_gmp_uom_class - '|| l_gmp_uom_class);
1287 gmp_debug_message('v_rsrc_cursor - '|| v_rsrc_cursor);
1288
1289 /* RAJESH PATANGYA open and fetch the all the batch details */
1290 OPEN rsrc_dtl FOR v_rsrc_cursor USING l_gmp_uom_class;
1291 LOOP
1292 FETCH rsrc_dtl INTO rsrc_tab(resource_count);
1293 EXIT WHEN rsrc_dtl%NOTFOUND;
1294 resource_count := resource_count + 1;
1295 END LOOP;
1296 CLOSE rsrc_dtl ;
1297 grsrc_size := resource_count - 1;
1298 log_message('Batches Resource size is = '|| to_char(grsrc_size) );
1299 time_stamp ;
1300
1301 -- NAVIN: START Operation Charges Data needs to be transferred to APS in to
1302 -- Msc_st_resource_charges
1303 stp_chg_cursor:=
1304 ' SELECT '
1305 ||' ((gbsc.batch_id*2)+1) x_batch_id,'
1306 ||' ((gbsc.batchstep_id*2)+1),' /* B5461922 */
1307 || ' ((crd.resource_id * 2) + 1),'
1308 ||' gbsc.charge_number,'
1309 ||' h.organization_id, '
1310 ||' gbs.batchstep_no,'
1311 ||' gbsc.activity_sequence_number,'
1312 ||' gbsc.charge_quantity, '
1313 ||' gbsc.plan_start_date, '
1314 ||' gbsc.plan_cmplt_date'
1315 ||' FROM'
1316 ||' gme_batch_step_charges'||pdblink||' gbsc,'
1317 ||' cr_rsrc_dtl'||pdblink||' crd,'
1318 ||' gmd_recipe_validity_rules'||pdblink||' v,'
1319 ||' gme_batch_steps'||pdblink||' gbs,'
1320 ||' gme_batch_header'||pdblink||' h'
1321 ||' WHERE '
1322 ||' h.batch_id = gbs.batch_id '
1323 ||' AND gbsc.batch_id = gbs.batch_id '
1324 ||' AND gbsc.batchstep_id = gbs.batchstep_id '
1325 ||' AND h.recipe_validity_rule_id = v.recipe_validity_rule_id'
1326 ||' AND EXISTS (SELECT '
1327 ||' 1 '
1328 ||' FROM '
1329 ||' gme_material_details'||pdblink||' gmd '
1330 ||' WHERE '
1331 ||' gmd.batch_id = h.batch_id '
1332 ||' AND gmd.inventory_item_id = v.inventory_item_id) '
1333 ||' AND crd.resources = gbsc.resources '
1334 ||' AND crd.organization_id = h.organization_id '
1335 ||' AND gbs.step_status in (1, 2) ';
1336
1337 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
1338 stp_chg_cursor := stp_chg_cursor
1339 ||' AND h.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
1340 END IF;
1341
1342 -- For alcoa Issue 13097786 Starts
1343 IF v_Overyielded_wip = 0 THEN
1344 stp_chg_cursor := stp_chg_cursor
1345 || ' AND NOT EXISTS (SELECT 1 '
1346 || ' FROM '
1347 || ' gme_material_details'||pdblink||' gmd '
1348 || ' WHERE '
1349 || ' gmd.batch_id = h.batch_id '
1350 || ' AND (NVL(gmd.wip_plan_qty,gmd.plan_qty) - gmd.actual_qty ) <= 0 '
1351 || ' AND gmd.line_type = 1 ) ' ;
1352 END IF;
1353 -- For alcoa Issue 13097786 Ends
1354
1355 stp_chg_cursor := stp_chg_cursor
1356 ||' ORDER BY 1, 2, 3, 4 ' ;
1357
1358 gmp_debug_message('stp_chg_cursor - '|| stp_chg_cursor);
1359
1360 OPEN c_chg_cursor FOR stp_chg_cursor ;
1361 LOOP
1362 FETCH c_chg_cursor INTO stp_chg_tbl(stp_chg_count);
1363 EXIT WHEN c_chg_cursor%NOTFOUND;
1364 stp_chg_count := stp_chg_count + 1;
1365 END LOOP;
1366 CLOSE c_chg_cursor ;
1367 stp_chg_count := stp_chg_count - 1;
1368 log_message('Batch Step charge size is = '|| to_char(stp_chg_count) );
1369 time_stamp ;
1370 log_message(gmp_calendar_pkg.g_in_str_org);
1371
1372 /* NAVIN :- alternate resource */
1373 /* NAVIN: In Procedure production_orders just before starting the looping for prod_dtl cursor
1374 try to get all the alternate Resources.*/
1375
1376 /* Alternate Resource selection */
1377 /* B5688153, Rajesh Patangya prod spec alt*/
1378 statement_alt_resource :=
1379 ' SELECT pcrd.resource_id, acrd.resource_id, '
1380 ||' cam.runtime_factor, '
1381 /*prod spec alt*/ ||' nvl(cam.preference,-1), nvl(prod.inventory_item_id,-1) '
1382 ||' FROM cr_rsrc_dtl'||pdblink||' acrd, '
1383 ||' cr_rsrc_dtl'||pdblink||' pcrd, '
1384 ||' cr_ares_mst'||pdblink||' cam, '
1385 ||' gmp_altresource_products'||pdblink||' prod'
1386 ||' WHERE cam.alternate_resource = acrd.resources '
1387 ||' AND cam.primary_resource = pcrd.resources '
1388 ||' AND acrd.organization_id = pcrd.organization_id '
1389 ||' AND cam.primary_resource = prod.primary_resource(+) '
1390 ||' AND cam.alternate_resource = prod.alternate_resource(+) '
1391 ||' AND acrd.delete_mark = 0 '
1392 ||' ORDER BY pcrd.resource_id, '
1393 ||' DECODE(cam.preference,NULL,cam.runtime_factor,cam.preference),'
1394 ||' prod.inventory_item_id ' ;
1395
1396 gmp_debug_message('statement_alt_resource - '|| statement_alt_resource);
1397
1398 -- Retrive the Details of all the Alternate Resources.
1399 alt_rsrc_size := 1;
1400 OPEN cur_alt_resource FOR statement_alt_resource ;
1401 LOOP
1402 FETCH cur_alt_resource INTO rtg_alt_rsrc_tab(alt_rsrc_size);
1403 EXIT WHEN cur_alt_resource%NOTFOUND;
1404 alt_rsrc_size := alt_rsrc_size + 1;
1405 END LOOP;
1406 CLOSE cur_alt_resource;
1407 alt_rsrc_size := alt_rsrc_size -1 ;
1408 log_message('alternate resource size is = '|| to_char(alt_rsrc_size) );
1409
1410 old_batch_id := -1;
1411 p := 1 ;
1412 FOR p IN 1..gprod_size LOOP /* Batch loop starts */
1413
1414 /* Multiply plan_qty with UOM conv factor. Factor will be 1 when the
1415 plan_qty and primary UOM is same. */
1416 -- gmp_debug_message('Production material loop - '|| p);
1417 -- gmp_debug_message('Batch Id - '|| prod_tab(p).batch_id );
1418
1419 prod_tab(p).qty := prod_tab(p).qty * prod_tab(p).uom_conv_factor;
1420 prod_tab(p).Minimum_Transfer_Qty := prod_tab(p).Minimum_Transfer_Qty * prod_tab(p).uom_conv_factor;
1421 /*Sowmya - As per the latest FDD changes - Modified as per Matt's review commet.
1422 The minimum tranfer qty should be passed in the primary uom*/
1423
1424 IF old_batch_id <> prod_tab(p).batch_id THEN
1425
1426 old_batch_id := prod_tab(p).batch_id;
1427 product_line := -1;
1428 opm_product_line := -1;
1429 prod_line_id := -1;
1430
1431 /* create a logical number by combining the plant and batch number */
1432 order_no := prod_tab(p).plant_code || pdelimiter ||
1433 prod_tab(p).batch_no;
1434
1435 IF prod_tab(p).batch_type = 10 THEN
1436 order_no := 'F/'||order_no ;
1437 END IF;
1438 -- gmp_debug_message('order_no - '|| order_no);
1439 /* nsinghi INVCONV Start */
1440 /* Commented out the code for org specific collections as Org Check is now
1441 directly done in each cursors. */
1442
1443 v_orgn_id := prod_tab(p).mtl_org_id;
1444 /*
1445 IF prod_tab(p).plant_code = prod_plant THEN
1446 IF (res_whse) THEN
1447 v_orgn_id := res_whse_id;
1448 ELSE
1449 v_orgn_id := prod_tab(p).mtl_org_id;
1450 END IF;
1451 ELSE
1452 prod_plant := prod_tab(p).plant_code;
1453 v_sql_stmt :=
1454 'SELECT '
1455 || ' iwm.mtl_organization_id '
1456 || 'FROM '
1457 || ' sy_orgn_mst' ||pdblink|| ' sy, '
1458 || ' ic_whse_mst' ||pdblink|| ' iwm '
1459 || 'WHERE '
1460 || ' sy.orgn_code = :p1'
1461 || ' AND sy.resource_whse_code = iwm.whse_code';
1462
1463 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
1464 v_sql_stmt := v_sql_stmt
1465 ||' AND iwm.mtl_organization_id ' || gmp_calendar_pkg.g_in_str_org ;
1466 END IF;
1467
1468 OPEN rsrc_whse FOR v_sql_stmt USING prod_tab(p).plant_code;
1469 FETCH rsrc_whse INTO res_whse_id;
1470 IF rsrc_whse%NOTFOUND THEN
1471 v_orgn_id := prod_tab(p).mtl_org_id;
1472 res_whse := FALSE;
1473 ELSE
1474 v_orgn_id := res_whse_id;
1475 res_whse := TRUE;
1476 END IF;
1477 CLOSE rsrc_whse;
1478
1479 END IF; */ /* for Plant code */
1480 /* nsinghi INVCONV End */
1481
1482 END IF; /* Batch Changes */
1483
1484 IF ( prod_tab(p).matl_item_id = prod_tab(p).recipe_item_id) AND (product_line = -1) THEN
1485 /* nsinghi INVCONV Start */
1486 /* product_line := prod_tab(p).item_id; *//* Product */
1487 product_line := prod_tab(p).matl_item_id; /* Product */
1488 opm_product_line := prod_tab(p).recipe_item_id; /* opm_Product */
1489 /* nsinghi INVCONV End */
1490
1491 prod_line_id := prod_tab(p).line_id;
1492 old_step_no := -1;
1493 i := 1;
1494
1495 /*Sowmya - Doubt - res_whse will be false when it enters this loop. Do we need to hav e res_whse??*/
1496 IF prod_tab(p).routing_id IS NOT NULL AND NVL(prod_tab(p).poc_ind, 'N') = 'Y' THEN -- AND
1497 -- (res_whse) THEN
1498 -- log_message( ' Entry -- ' || g_rsrc_cnt );
1499 r := 1 ;
1500 resource_usage_flag := 0 ;
1501 resource_instance_usage_flag := 0 ;
1502 old_rsrc_batch_id := -999;
1503 old_rsrc_resources := -999;
1504 old_rsrc_original_seq_num := -999;
1505 old_instance_number := -999;
1506 old_rsrc_inst_batch_id := -999;
1507 old_rsrc_inst_resources := -999;
1508 old_rsrc_inst_original_seq_num := -999;
1509
1510 FOR r IN g_rsrc_cnt..grsrc_size LOOP /* Resource Cursor */
1511 -- gmp_debug_message('Resource Loop - '|| r);
1512 /* ------------- Navin: START Process Resource Requirements ------------- */
1513 IF old_rsrc_batch_id <> rsrc_tab(r).batch_id
1514 OR old_rsrc_resources <> rsrc_tab(r).resources
1515 OR old_rsrc_original_seq_num <> rsrc_tab(r).original_seq_num THEN
1516 -- Reset the flags.
1517 resource_usage_flag := 0 ;
1518 END IF;
1519
1520 IF rsrc_tab(r).resource_usage > 0 AND resource_usage_flag = 0 THEN
1521 -- Process and insert the very first resource record
1522 resource_usage_flag := 1 ;
1523 -- Populate flags
1524 old_rsrc_batch_id := rsrc_tab(r).batch_id ;
1525 old_rsrc_resources := rsrc_tab(r).resources ;
1526 old_rsrc_original_seq_num := rsrc_tab(r).original_seq_num ;
1527
1528 /*Sowmya - As per the latest FDD changes - process this resource only
1529 if the class type of this is same as the one defined in profile*/
1530 l_res_inst_process := 1;
1531
1532 IF prod_tab(p).batch_id > rsrc_tab(r).batch_id THEN --- MAIN IF
1533 NULL ;
1534 ELSIF prod_tab(p).batch_id < rsrc_tab(r).batch_id THEN
1535 g_rsrc_cnt := r ;
1536 /* Initialize for the change of batch */
1537 v_resource_usage := 0;
1538 v_res_seq := 0;
1539 v_schedule_flag := 0;
1540 v_parent_seq_num := 0;
1541 v_rsrc_cnt := 0;
1542 v_start_date := NULL;
1543 v_end_date := NULL;
1544 old_activity := 0;
1545 V_ACTIVITY_GROUP_ID := 0; /* B3995361 rpatangy */
1546 v_seq_dep_usage := 0;
1547 found_chrg_rsrc := 0;
1548 chrg_activity := -1;
1549
1550 EXIT;
1551 ELSIF prod_tab(p).batch_id = rsrc_tab(r).batch_id THEN
1552 IF old_step_no <> rsrc_tab(r).batchstep_no THEN /* Step change */
1553 v_res_seq := 0;
1554 old_activity := -1;
1555 v_resource_usage := 0;
1556 v_res_seq := 0;
1557 v_schedule_flag := 0;
1558 v_parent_seq_num := 0;
1559 v_rsrc_cnt := 0;
1560 v_start_date := NULL;
1561 v_end_date := NULL;
1562 V_ACTIVITY_GROUP_ID := 0; /* B3995361 rpatangy */
1563 v_seq_dep_usage := 0;
1564 found_chrg_rsrc := 0;
1565 chrg_activity := -1;
1566 /* nsinghi APSK - Insert Step related information in msc_st_job_operations
1567 every time step changes. */
1568
1569 jo_index := jo_index + 1;
1570 jo_wip_entity_id(jo_index) := rsrc_tab(r).x_batch_id;
1571 jo_instance_id(jo_index) := pinstance_id;
1572 jo_operation_seq_num(jo_index) := rsrc_tab(r).batchstep_no;
1573 jo_recommended(jo_index) := 'Y';
1574 jo_network_start_end(jo_index) := null_value;
1575 jo_reco_start_date(jo_index) := rsrc_tab(r).step_start_date;
1576 jo_reco_completion_date(jo_index) := rsrc_tab(r).step_end_date;
1577 jo_operation_sequence_id(jo_index) := rsrc_tab(r).batchstep_id;
1578 jo_organization_id(jo_index) := v_orgn_id;
1579 jo_department_id(jo_index) := ((v_orgn_id*2) + 1);
1580
1581 /* Bug:6407939 -KBANDDYO assignement changed from prod_tab to rsrc_tab */
1582 -- jo_minimum_transfer_quantity(jo_index) := prod_tab(p).minimum_transfer_qty;
1583 jo_minimum_transfer_quantity(jo_index) := rsrc_tab(r).min_xfer_qty ;
1584
1585 END IF; /* Step change */
1586
1587 IF rsrc_tab(r).seq_dep_ind <> -1 THEN /* NAVIN :- Process Rows only if
1588 Sequence Dependent is not -1 */
1589
1590 IF (old_activity <> rsrc_tab(r).bs_activity_id) OR (old_activity = -1) THEN
1591 v_res_seq := v_res_seq + 1;
1592 old_activity := rsrc_tab(r).bs_activity_id;
1593
1594 /* B3421856, If materail indicator activity then previous = 3, Next = 4 */
1595 IF rsrc_tab(r).mat_found > 0 THEN
1596
1597 IF rsrc_tab(r).material_ind = 1 THEN
1598 v_schedule_flag := 4;
1599 ELSE
1600 IF v_schedule_flag < 4 THEN
1601 v_schedule_flag := 3 ;
1602 END IF ;
1603 END IF; /* Material Indicator */
1604 END IF; /* Mat_found */
1605 END IF; /* old_activity */
1606
1607 IF (rsrc_tab(r).material_ind = 0) AND (rsrc_tab(r).mat_found > 0) THEN
1608 rsrc_tab(r).schedule_flag := v_schedule_flag;
1609 END IF;
1610
1611 IF NVL(rsrc_tab(r).actual_cmplt_date,v_null_date) = v_null_date THEN
1612 /* when the actual start is null the resource has not started
1613 and the plan start will be used. */
1614
1615 /* bug: 6713691 vpedarla - made changes to add the partial
1616 resource transaction back to the resource usage
1617 which will be cut-off during the ODS LOAD(APS) */
1618 IF rsrc_tab(r).actual_start_date is not null and
1619 rsrc_tab(r).actual_cmplt_date is null THEN
1620 rsrc_tab(r).resource_usage := rsrc_tab(r).resource_usage
1621 + rsrc_tab(r).actual_rsrc_usage ;
1622 END IF;
1623
1624 -- Bug: 8647592 Vpedarla
1625 IF ( l_gmp_um_code <> rsrc_tab(r).usage_uom ) THEN
1626 uom_conv_cursor := 'SELECT '
1627 ||' inv_convert.inv_um_convert'||pdblink
1628 ||' (:pitem, '
1629 ||' NULL, '
1630 ||' :orgid, '
1631 ||' 5 , '
1632 ||' :pqty, '
1633 ||' :pfrom_um, '
1634 ||' :pto_um , '
1635 ||' NULL , '
1636 ||' NULL '
1637 ||' ) '
1638 ||' FROM dual';
1639 v_new_res_usage := -1;
1640 OPEN c_uom_conv FOR uom_conv_cursor USING
1641 product_line,
1642 v_orgn_id, --sowmya added.
1643 rsrc_tab(r).resource_usage,
1644 rsrc_tab(r).usage_uom ,
1645 l_gmp_um_code;
1646
1647 FETCH c_uom_conv INTO v_new_res_usage;
1648 CLOSE c_uom_conv;
1649 IF v_new_res_usage > 0 THEN
1650 rsrc_tab(r).resource_usage := v_new_res_usage ;
1651 END IF;
1652 END IF;
1653 -- Bug: 8647592 end
1654
1655 IF rsrc_tab(r).tran_seq_dep = 1 THEN
1656 v_parent_seq_num := v_res_seq;
1657 v_resource_usage := rsrc_tab(r).resource_usage;
1658 v_start_date := rsrc_tab(r).act_start_date;
1659 v_end_date := rsrc_tab(r).plan_start_date;
1660 /* NAVIN :- added Sequence Dependency */
1661 v_seq_dep_usage := rsrc_tab(r).sequence_dependent_usage;
1662 ELSE
1663 v_seq_dep_usage := 0;
1664 v_parent_seq_num := TO_NUMBER(NULL);
1665 v_start_date := rsrc_tab(r).plan_start_date;
1666 v_end_date := rsrc_tab(r).plan_cmplt_date;
1667 IF v_inflate_wip = 1 THEN
1668 v_resource_usage := rsrc_tab(r).resource_usage / rsrc_tab(r).utl_eff;
1669 ELSE
1670 v_resource_usage := rsrc_tab(r).resource_usage;
1671 END IF;
1672 END IF; /* tran_seq_ind */
1673
1674 /*Sowmya - As per the latest FDD changes - Start*/
1675 /*For a Pending batch if the original resoucre count is less than the plan
1676 resource count then pass pln resource count otherwise the original resource
1677 count is passed*/
1678 IF rsrc_tab(r).org_step_status = 1 THEN
1679 /* B4349002 Resource Count is same as Plan resource count */
1680 v_max_rsrcs := rsrc_tab(r).gmd_rsrc_count;
1681 ELSIF rsrc_tab(r).org_step_status = 2 THEN
1682 IF rsrc_tab(r).actual_rsrc_count IS NULL THEN
1683 v_max_rsrcs := rsrc_tab(r).plan_rsrc_count;
1684 ELSE
1685 v_max_rsrcs := rsrc_tab(r).actual_rsrc_count;
1686 END IF;
1687 END IF;
1688 /*Sowmya - As per the latest FDD changes - End*/
1689
1690 /* If no actual resource exists then the resource has not
1691 started and the planned value will be used */
1692 IF rsrc_tab(r).actual_rsrc_count IS NULL THEN
1693 v_rsrc_cnt := rsrc_tab(r).plan_rsrc_count;
1694 ELSE
1695 v_rsrc_cnt := rsrc_tab(r).actual_rsrc_count;
1696 END IF;
1697
1698 /* write the current resource detail row asscoiating it with the
1699 batch through the product line */
1700
1701 /* NAVIN :- If there are more than 1 activities in a step having
1702 chargeable resources and scale_type = 3 and scheduled, then
1703 change scale_type for all activities after the 1st is changed
1704 to linear */
1705
1706 IF rsrc_tab(r).mat_found = 0 OR rsrc_tab(r).material_ind = 1 THEN
1707 IF rsrc_tab(r).scale_type = 3 -- APS decoded value as per DECODE(rsrc_tab(r).scale_type,0,2,1,1,2,3);
1708 AND rsrc_tab(r).capacity_constraint = 1
1709 AND found_chrg_rsrc = 0 THEN
1710 found_chrg_rsrc := 1;
1711 chrg_activity := rsrc_tab(r).bs_activity_id;
1712 /* if the rtg_scale_type is 3 but another activity was found
1713 with 2 then this row will be assigned scale_type = 1. */
1714 ELSIF rsrc_tab(r).scale_type = 3
1715 AND rsrc_tab(r).capacity_constraint = 1
1716 AND found_chrg_rsrc = 1
1717 AND chrg_activity <> rsrc_tab(r).bs_activity_id THEN
1718 rsrc_tab(r).scale_type := 1;
1719 END IF;
1720 END IF;
1721
1722 IF rsrc_tab(r).scale_type = 3 AND found_chrg_rsrc = 1 THEN -- APS decoded value as per DECODE(rsrc_tab(r).scale_type,0,2,1,1,2,3);
1723 /* NAVIN: END Operation Charges Data needs to be transferred
1724 to APS in to Msc_st_resource_charges */
1725 IF rsrc_tab(r).org_step_status = 2 THEN
1726 l_charges_remaining := CEIL(((rsrc_tab(r).plan_rsrc_usage -
1727 rsrc_tab(r).actual_rsrc_usage) * rsrc_tab(r).plan_charges) /
1728 rsrc_tab(r).plan_rsrc_usage);
1729 -- HW B4761811- Calculate the remaining charged
1730 ELSE
1731 l_charges_remaining := rsrc_tab(r).plan_charges ;
1732
1733 END IF;
1734
1735 IF rsrc_tab(r).org_step_status = 1 OR (l_charges_remaining > 0 AND rsrc_tab(r).org_step_status = 2) THEN
1736 /* Batch step status is pending OR there are some remaining charges for a WIP batch */
1737
1738 inst_stp_chg_tbl(pinstance_id, r);
1739 END IF;
1740 END IF ;
1741
1742 /* B3995361 rpatangy start */
1743 IF rsrc_tab(r).prim_rsrc_ind = 1 THEN
1744 v_activity_group_id := rsrc_tab(r).x_resource_id ;
1745 END IF;
1746 /* B3995361 rpatangy end */
1747
1748 IF v_resource_usage > 0 THEN
1749
1750 /* Bulk Insert for insert_resource_requirements */
1751 rr_index := rr_index + 1 ;
1752 rr_organization_id(rr_index) := v_orgn_id ;
1753 rr_sr_instance_id(rr_index) := pinstance_id ;
1754 rr_supply_id(rr_index) := rsrc_tab(r).x_batch_id ; /* B1177070 encoded key */
1755 rr_resource_seq_num(rr_index) := rsrc_tab(r).seq_dep_ind ;
1756 rr_resource_id(rr_index) := rsrc_tab(r).x_resource_id ; /* B1177070 encoded key */
1757 rr_start_date(rr_index) := v_start_date ;
1758 rr_end_date(rr_index) := v_end_date ;
1759 rr_opr_hours_required(rr_index) := v_resource_usage ;
1760 /* Bug 4431718 populate usage_rate column */
1761 IF rsrc_tab(r).scale_type = 1 THEN /*linearly scaled */
1762 IF rsrc_tab(r).plan_step_qty > 0 THEN
1763 rr_usage_rate(rr_index) :=
1764 v_resource_usage / rsrc_tab(r).plan_step_qty ;
1765 ELSE
1766 rr_usage_rate(rr_index) := v_resource_usage ;
1767 END IF ;
1768 ELSIF rsrc_tab(r).scale_type = 2 THEN /*fix scaled*/
1769 rr_usage_rate(rr_index) := v_resource_usage ;
1770 ELSIF rsrc_tab(r).scale_type = 3 THEN /* Charge Scaled */
1771 IF l_charges_remaining > 0 THEN
1772 rr_usage_rate(rr_index) :=
1773 v_resource_usage /l_charges_remaining ;
1774 ELSE
1775 rr_usage_rate(rr_index) := v_resource_usage ;
1776 END IF ;
1777 END IF ;
1778 rr_assigned_units(rr_index) := v_rsrc_cnt ;
1779 rr_department_id(rr_index) := ((v_orgn_id * 2) + 1) ; /* B1177070 encoded key */
1780 rr_wip_entity_id(rr_index) := rsrc_tab(r).x_batch_id ; /* B1177070 encoded key */
1781 /* B1224660 write the step number for oper seq num */
1782 rr_operation_seq_num(rr_index) := rsrc_tab(r).batchstep_no ;
1783 /* B3995361 */
1784 /* Bug 6739913 OPM BATCHES IN WIP STATE HAVE INCORRECT FIRM FLAG VALUE.
1785 1. Discrete/OSFM models- when the operation is in running state, running_qty > 0
1786 and firm flag is 2. This looks good in PS as well.
1787 2. OSFM models - when the operation is in WIP state, running_qty is NULL and
1788 firm flag is 7. Looks like OPM collections are populating the value 7 in ODS.
1789 If this is the case, PS can read the firm flag value of 7 and display the wip
1790 operations as ACTIVE for opm models. Teva asked for such a requirement as
1791 well for Version 1.
1792 This is because you were directly interacting with HLS team. They see the firm_flag
1793 as 7 as the MBP module will convert the 1 sent by discrete as 7 to them. So,
1794 ideally OPM should be sending us 1 and MBP will convert this as 7 before
1795 giving it to HLS.
1796 */
1797 IF rsrc_tab(r).step_status = 2 THEN
1798 /* vpedarla Bug: 6739913 made firm_type flag to be 1 when the
1799 step status is WIP */
1800 -- rr_firm_flag(rr_index) := 1 ;
1801 rr_firm_flag(rr_index) := 7 ; /* Bug 10223411 ASCP discussed */
1802 ELSE
1803 rr_firm_flag(rr_index) := rsrc_tab(r).firm_type ;
1804 END IF;
1805
1806 -- Vpedarla 9319734
1807 IF rsrc_tab(r).step_status = 1 THEN
1808 rr_opr_status(rr_index) := 1 ;
1809 ELSIF rsrc_tab(r).step_status = 2 THEN
1810 rr_opr_status(rr_index) := 2 ;
1811 ELSE
1812 rr_opr_status(rr_index) := 3 ;
1813 END IF;
1814
1815 rr_minimum_transfer_quantity(rr_index) := 0 ;
1816 rr_parent_seq_num(rr_index) := TO_NUMBER(NULL) ;
1817 rr_schedule_flag(rr_index) := rsrc_tab(r).schedule_flag ;
1818 /* NAVIN :- start */
1819 rr_sequence_id(rr_index) := rsrc_tab(r).group_sequence_id ;
1820 rr_sequence_number(rr_index) := rsrc_tab(r).group_sequence_number ;
1821 rr_firm_type(rr_index) := rsrc_tab(r).firm_type ;
1822 rr_setup_id(rr_index) := rsrc_tab(r).setup_id ;
1823 rr_original_seq_num (rr_index) := rsrc_tab(r).original_seq_num;
1824 rr_min_capacity(rr_index) := rsrc_tab(r).minimum_capacity;
1825 rr_max_capacity(rr_index) := rsrc_tab(r).maximum_capacity;
1826 rr_alternate_number(rr_index) := 0 ;
1827 rr_basis_type(rr_index) := rsrc_tab(r).scale_type;
1828 rr_hours_expended(rr_index) := rsrc_tab(r).actual_rsrc_usage;
1829 rr_breakable_activity_flag(rr_index) := rsrc_tab(r).breakable_activity_flag;
1830 /*B4777532 - sowsubra - the product item id should be populated
1831 used the product_line which is populated with the product id, everytime for
1832 a new batch.*/
1833 rr_product_item_id(rr_index) := product_line ;
1834
1835 /* Sowmya - As per the latest FDD changes - Start */
1836 rr_plan_step_qty(rr_index) := rsrc_tab(r).plan_step_qty ;
1837 rr_step_qty_uom(rr_index) := rsrc_tab(r).step_qty_uom ;
1838 rr_gmd_rsrc_cnt(rr_index) := v_max_rsrcs;
1839 /* Sowmya - As per the latest FDD changes - End */
1840 /* B3995361 rpatangy */
1841 rr_activity_group_id(rr_index) := v_activity_group_id ;
1842 /* B5338598 rpatangy starts */
1843 -- rr_activity_name(rr_index) := rsrc_tab(r).activity ;
1844 rr_operation_no(rr_index) := rsrc_tab(r).operation_no ||pdelimiter||rsrc_tab(r).oprn_vers;
1845 /* B5338598 rpatangy Ends */
1846 rr_operation_sequence_id(rr_index) := rsrc_tab(r).batchstep_id ; /* B5461922 rpatangy */
1847
1848 /*B4320561 - sowsubra - start*/
1849 rr_unadjusted_resource_hrs(rr_index) := rsrc_tab(r).resource_usage ;
1850 rr_touch_time(rr_index) := rr_unadjusted_resource_hrs(rr_index)/ rsrc_tab(r).efficiency ;
1851 /*B4320561 - sowsubra - end*/
1852
1853 /* NAVIN :- START - Logic To Handle Alternate Resources */
1854 /*
1855 Now check if the above resource inserted is a Primary. If it is
1856 Primary then find its Alternates if existing, and then insert its rows
1857 into msc_st_operation_resources table. Also keep track of number of
1858 times alternates are inserted.
1859 */
1860
1861 IF rsrc_tab(r).prim_rsrc_ind = 1 THEN
1862 ---------------------------------------------------------------------
1863 -- Use Bsearch technique to identify if any Alternate exists for the primary.
1864 -- Enh_bsearch_alternate_rsrc is a new procedure to locate the Alternate Resource
1865 -- for a given Primary resource in the PL/SQl table.
1866 ---------------------------------------------------------------------
1867 alternate_rsrc_loc := Enh_bsearch_alternate_rsrc (rsrc_tab(r).resource_id);
1868 v_alternate := 0;
1869
1870 IF alternate_rsrc_loc > 0 THEN /* Alternate resource location */
1871 /*Sowmya - As per latest FDD changes - Included chks that determine
1872 when the alternate resources will be passed */
1873 IF prod_tab(p).firmed_ind <> 1 THEN /* Batch firm chk */
1874 /*If batch not firmed then pass on the alternate resource data*/
1875 IF rsrc_tab(r).org_step_status <> 2 THEN /* Batch Step not in WIP */
1876 /*Pass on the alternate resource data when the batch step is not in
1877 WIP status*/
1878 IF ( rsrc_tab(r).firm_type <> 3 ) AND ( rsrc_tab(r).firm_type <> 5 )
1879 AND ( rsrc_tab(r).firm_type <> 6 ) AND ( rsrc_tab(r).firm_type <> 7 ) THEN
1880 /* Batch resources not firmed */
1881 /*0 - UnFrim , 1 - Firm Start Date ,
1882 2 - Firm End Date , 3 - Firm Resource ,
1883 4 - Firm Start Date and End Date ,
1884 5 - Firm Start Date and Resource ,
1885 6 - Firm End Date and Resource ,
1886 7 - Firm All*/
1887 alt_cnt := 1 ;
1888 -- Loop through the Alternate resources for the Primary Resource
1889 /*Sowmya - As per the latest FDD changes - Start */
1890 FOR alt_cnt IN alternate_rsrc_loc..alt_rsrc_size
1891 LOOP
1892 /* B5688153, Rajesh Patangya prod spec alt*/
1893 IF ( rtg_alt_rsrc_tab(alt_cnt).prim_resource_id =
1894 rsrc_tab(r).resource_id
1895 AND (rtg_alt_rsrc_tab(alt_cnt).inventory_item_id = -1 OR
1896 rtg_alt_rsrc_tab(alt_cnt).inventory_item_id =
1897 opm_product_line )) THEN
1898
1899 -- IF ( rtg_alt_rsrc_tab(alt_cnt).prim_resource_id = rsrc_tab(r).resource_id ) THEN
1900 v_alternate := v_alternate + 1;
1901 /* Bulk Insert for Alternate_resource_requirements */
1902
1903 arr_index := arr_index + 1 ;
1904 arr_organization_id(arr_index) := v_orgn_id ;
1905 arr_sr_instance_id(arr_index) := pinstance_id;
1906 arr_res_seq_num(arr_index) := rsrc_tab(r).original_seq_num ;
1907 arr_assigned_units(arr_index) := v_rsrc_cnt ;
1908 arr_department_id(arr_index) :=
1909 ((v_orgn_id * 2) + 1) ;
1910
1911 arr_wip_entity_id(arr_index) :=
1912 rsrc_tab(r).x_batch_id ;
1913 /* B1224660 write the step number for oper
1914 seq num */
1915 arr_operation_seq_num(arr_index) :=
1916 rsrc_tab(r).batchstep_no ;
1917 arr_setup_id(arr_index) :=
1918 rsrc_tab(r).setup_id ;
1919 arr_schedule_seq_num(arr_index) :=
1920 rsrc_tab(r).seq_dep_ind;
1921 arr_maximum_assigned_units(arr_index) :=
1922 v_max_rsrcs;
1923 arr_activity_group_id(arr_index) :=
1924 ((rtg_alt_rsrc_tab(alt_cnt).alt_resource_id * 2) + 1);
1925 arr_basis_type(arr_index):=
1926 rsrc_tab(r).scale_type;
1927 arr_resource_id(arr_index) :=
1928 ((rtg_alt_rsrc_tab(alt_cnt).alt_resource_id * 2) + 1) ;
1929
1930 -- BUg: 8393507 Vpedarla Modified the below code as mentioned below. Divide resource usage by step qty simillar to primary resource.
1931 -- arr_usage_rate(arr_index) := v_resource_usage * rtg_alt_rsrc_tab(alt_cnt).runtime_factor;
1932 arr_usage_rate(arr_index) := rr_usage_rate(rr_index) * rtg_alt_rsrc_tab(alt_cnt).runtime_factor;
1933 arr_alternate_num(arr_index) := v_alternate ;
1934 arr_uom_code(arr_index) :=
1935 rsrc_tab(r).usage_uom;
1936 -- arr_gmd_rsrc_cnt(rr_index) := v_max_rsrcs;
1937 ELSIF ( rtg_alt_rsrc_tab(alt_cnt).prim_resource_id > rsrc_tab(r).resource_id ) THEN
1938 EXIT ;
1939 END IF; /* End if for alternate resource and orgn code match */
1940 END LOOP; /* Alternate loop */
1941 END IF; /* Batch resources not firmed */
1942 END IF;/* Batch Step not in WIP */
1943 ELSE
1944 gmp_debug_message(' batch firmed - No alternate resources loaded ');
1945 END IF; /* Batch firm chk */
1946 END IF ; /* Alternate resource location */
1947 END IF; /* rsrc_tab(r).prim_rsrc_ind = 1 */
1948
1949 /* NAVIN:
1950 Below logic is to create the resource group pattern with different
1951 values of Alternate_Number. Variable v_alternate holds the count of
1952 alternate resources that has been inserted for the Primary resource
1953 of the group. Now insert all the resource records other than primary
1954 with a value of Alternate_Number from 1 to v_alternate, to complete
1955 the pattern of resource group.
1956 NAVIN: */
1957
1958 IF rsrc_tab(r).prim_rsrc_ind <> 1 AND v_alternate > 0 THEN
1959 /* B3995361 rpatangy start */
1960 mk_alt_grp := 0 ;
1961 FOR alt_cnt IN alternate_rsrc_loc..alt_rsrc_size
1962 LOOP
1963 IF rtg_alt_rsrc_tab(alt_cnt).prim_resource_id =
1964 ((v_activity_group_id - 1)/2) THEN
1965 arr_index := arr_index + 1 ;
1966 mk_alt_grp := mk_alt_grp + 1 ;
1967 arr_organization_id(arr_index) := v_orgn_id ;
1968 arr_sr_instance_id(arr_index) := pinstance_id ;
1969 arr_res_seq_num(arr_index) := rsrc_tab(r).original_seq_num ;
1970 arr_resource_id(arr_index) := rsrc_tab(r).x_resource_id ;
1971 arr_assigned_units(arr_index) := v_rsrc_cnt ;
1972 arr_department_id(arr_index) := ((v_orgn_id * 2) + 1) ;
1973 arr_wip_entity_id(arr_index) := rsrc_tab(r).x_batch_id ;
1974 arr_operation_seq_num(arr_index) := rsrc_tab(r).batchstep_no ;
1975 arr_setup_id(arr_index) := rsrc_tab(r).setup_id ;
1976 arr_schedule_seq_num(arr_index) := rsrc_tab(r).seq_dep_ind;
1977 arr_maximum_assigned_units(arr_index) := v_max_rsrcs;
1978 arr_activity_group_id(arr_index) :=
1979 ((rtg_alt_rsrc_tab(alt_cnt).alt_resource_id * 2) + 1);
1980 arr_basis_type(arr_index):= rsrc_tab(r).scale_type;
1981 arr_usage_rate(arr_index) := rr_usage_rate(rr_index) ;
1982 -- v_resource_usage ;
1983 arr_alternate_num(arr_index) := mk_alt_grp ;
1984 arr_uom_code(arr_index) := rsrc_tab(r).usage_uom;
1985 ELSIF rtg_alt_rsrc_tab(alt_cnt).prim_resource_id >
1986 ((v_activity_group_id - 1)/2) THEN
1987 EXIT ;
1988 END IF; /* End if for alternate resource and orgn code match */
1989 /* B3995361 rpatangy End */
1990 END LOOP; /* mk_alt_grp loop */
1991 END IF; /* End if for Check in Primary Resource Indicator and v_alternate > 0*/
1992 /* NAVIN :- END - Logic To Handle Alternate Resources */
1993
1994 /* NAVIN :- Logic to Handle Additional row For Sequence Dependency Start */
1995 IF v_seq_dep_usage > 0 THEN
1996 rr_index := rr_index + 1 ;
1997 rr_organization_id(rr_index) := v_orgn_id ;
1998 rr_sr_instance_id(rr_index) := pinstance_id ;
1999 rr_supply_id(rr_index) := rsrc_tab(r).x_batch_id ; /* B1177070 encoded key */
2000 /* B1224660 new value to write resource seq num */
2001 rr_resource_seq_num(rr_index) := rsrc_tab(r).seq_dep_ind ;
2002 rr_resource_id(rr_index) := rsrc_tab(r).x_resource_id ; /* B1177070 encoded key */
2003 rr_start_date(rr_index) := v_start_date ;
2004 rr_end_date(rr_index) := v_end_date ;
2005 rr_opr_hours_required(rr_index) := rsrc_tab(r).sequence_dependent_usage;-- * converted_usage;
2006 /* B4637398, We will treat This extra usage row as fixed and provide
2007 the same reosurce usage in usage_rate column */
2008 rr_usage_rate(rr_index) := v_resource_usage ;
2009
2010 -- Vpedarla 9319734
2011 IF rsrc_tab(r).step_status = 1 THEN
2012 rr_opr_status(rr_index) := 1 ;
2013 ELSIF rsrc_tab(r).step_status = 2 THEN
2014 rr_opr_status(rr_index) := 2 ;
2015 ELSE
2016 rr_opr_status(rr_index) := 3 ;
2017 END IF;
2018
2019
2020 /* Sowmya - As per the latest FDD changes - multiply the usage with the conveted factor */
2021 rr_assigned_units(rr_index) := v_rsrc_cnt ;
2022 rr_department_id(rr_index) := ((v_orgn_id * 2) + 1) ; /* B1177070 encoded key */
2023 rr_wip_entity_id(rr_index) := rsrc_tab(r).x_batch_id ; /* B1177070 encoded key */
2024 /* B1224660 write the step number for oper seq num */
2025 rr_operation_seq_num(rr_index) := rsrc_tab(r).batchstep_no ;
2026 rr_operation_sequence_id(rr_index) := rsrc_tab(r).batchstep_id ; /* B5461922 rpatangy */
2027 rr_firm_flag(rr_index) := rsrc_tab(r).firm_type ;
2028 rr_minimum_transfer_quantity(rr_index) := 0 ;
2029 rr_parent_seq_num(rr_index) := rsrc_tab(r).original_seq_num;
2030 rr_schedule_flag(rr_index) := rsrc_tab(r).schedule_flag ;
2031 rr_sequence_id(rr_index) := rsrc_tab(r).group_sequence_id ;
2032 rr_sequence_number(rr_index) := rsrc_tab(r).group_sequence_number ;
2033 rr_firm_type(rr_index) := rsrc_tab(r).firm_type ;
2034 rr_setup_id(rr_index) := rsrc_tab(r).setup_id ;
2035 rr_original_seq_num (rr_index) := TO_NUMBER(NULL) ;
2036 rr_min_capacity(rr_index) := rsrc_tab(r).minimum_capacity;
2037 rr_max_capacity(rr_index) := rsrc_tab(r).maximum_capacity;
2038 rr_alternate_number(rr_index) := 0 ;
2039 rr_basis_type(rr_index) := rsrc_tab(r).scale_type; -- Added 7/14/2004
2040 rr_hours_expended(rr_index) := rsrc_tab(r).actual_rsrc_usage;
2041 rr_breakable_activity_flag(rr_index) := rsrc_tab(r).breakable_activity_flag;
2042 /*B4777532 - sowsubra - the product item id should be populated
2043 used the product_line which is populated with the product id, everytime for
2044 a new batch.*/
2045 rr_product_item_id(rr_index) := product_line ;
2046
2047 /* Sowmya - As per the latest FDD changes - Start */
2048 rr_plan_step_qty(rr_index) := rsrc_tab(r).plan_step_qty ;
2049 rr_step_qty_uom(rr_index) := rsrc_tab(r).step_qty_uom ;
2050 rr_gmd_rsrc_cnt(rr_index) := v_max_rsrcs;
2051 /* Sowmya - As per the latest FDD changes - End */
2052
2053 /* B3995361 rpatangy */
2054 rr_activity_group_id(rr_index) := v_activity_group_id ;
2055
2056 /*B4320561 - sowsubra - start*/
2057 rr_unadjusted_resource_hrs(rr_index) := rsrc_tab(r).resource_usage ;
2058 rr_touch_time(rr_index) := rr_unadjusted_resource_hrs(rr_index)/ rsrc_tab(r).efficiency ;
2059 /*B4320561 - sowsubra - end*/
2060
2061 gmp_debug_message('Resource '|| rr_resource_id(rr_index) ||' resource_count hours '||rr_opr_hours_required(rr_index));
2062
2063 END IF;
2064 END IF; /* resource usage */ -- v_resource_usage > 0
2065 END IF; /* actual completion date */ -- NVL(rsrc_tab(r).actual_cmplt_date,v_null_date) = v_null_date
2066 END IF; /* NAVIN :- End If condition for seq_dep_ind <> -1 */ -- rsrc_tab(r).seq_dep_ind <> -1
2067
2068 old_step_no := rsrc_tab(r).batchstep_no;
2069 END IF ; /* entry/Exit Logic */ --- MAIN IF
2070
2071 END IF; /* rsrc_tab(r).resource_usage > 0 AND resource_usage_flag = 0 */
2072
2073 /* ------------- Navin: END Process Resource Requirements ------------- */
2074
2075 /* ------------- Navin: START Process Resource Instances Requirements ------------- */
2076 IF rsrc_tab(r).instance_number <> -1 THEN
2077 IF old_rsrc_inst_batch_id <> rsrc_tab(r).batch_id
2078 OR old_rsrc_inst_resources <> rsrc_tab(r).resources
2079 OR old_rsrc_inst_original_seq_num <> rsrc_tab(r).original_seq_num
2080 OR old_instance_number <> rsrc_tab(r).instance_number THEN
2081 -- Reset the flags.
2082 resource_instance_usage_flag := 0 ;
2083 END IF;
2084
2085 IF rsrc_tab(r).resource_instance_usage > 0 AND resource_instance_usage_flag = 0 AND l_res_inst_process = 1 THEN
2086 -- Process and insert the very first resource_instance_usage record
2087 resource_instance_usage_flag := 1 ;
2088
2089 /* Sowmya - As per the latest FDD changes - Reinitialise the variable*/
2090 l_res_inst_process := 0 ;
2091
2092 -- Populate flags
2093 old_rsrc_inst_batch_id := rsrc_tab(r).batch_id ;
2094 old_rsrc_inst_resources := rsrc_tab(r).resources ;
2095 old_rsrc_inst_original_seq_num := rsrc_tab(r).original_seq_num ;
2096 old_instance_number := rsrc_tab(r).instance_number;
2097
2098 -- Insert the very first resource_instance_usage record
2099 inst_indx := inst_indx + 1 ;
2100 rec_inst_supply_id(inst_indx) := rsrc_tab(r).x_batch_id ;
2101 rec_inst_organization_id(inst_indx) := v_orgn_id ;
2102 rec_inst_sr_instance_id(inst_indx) := pinstance_id ;
2103 rec_inst_rec_resource_seq_num(inst_indx) := rsrc_tab(r).seq_dep_ind ;
2104 rec_inst_resource_id(inst_indx) := rsrc_tab(r).x_resource_id ;
2105 rec_inst_instance_id(inst_indx) := rsrc_tab(r).instance_number ;
2106 rec_inst_start_date(inst_indx) := v_start_date ;
2107 rec_inst_end_date(inst_indx) := v_end_date ;
2108 rec_inst_rsrc_instance_hours(inst_indx) := rsrc_tab(r).resource_instance_usage;-- * converted_usage;
2109 /* Sowmya - As per the latest FDD changes - multiply the usage with the conveted factor */
2110 rec_inst_operation_seq_num(inst_indx) := rsrc_tab(r).batchstep_no ;
2111 rec_inst_department_id(inst_indx) := ((v_orgn_id * 2) + 1) ;
2112 rec_inst_wip_entity_id(inst_indx) := rsrc_tab(r).x_batch_id ;
2113 rec_inst_serial_number(inst_indx) := rsrc_tab(r).eqp_serial_number ;
2114 rec_inst_original_seq_num(inst_indx) := rsrc_tab(r).original_seq_num ;
2115 rec_inst_parent_seq_num(inst_indx) := TO_NUMBER(NULL) ;
2116 rec_inst_equp_item_id(inst_indx) := rsrc_tab(r).equp_item_id;
2117 /*Sowmya - As per the latest FDD changes - Resource Instances */
2118
2119 IF v_seq_dep_usage > 0 THEN
2120 /* Bulk Insert for insert_resource_requirements */
2121 inst_indx := inst_indx + 1 ;
2122
2123 rec_inst_supply_id(inst_indx) := rsrc_tab(r).x_batch_id ;
2124 rec_inst_organization_id(inst_indx) := v_orgn_id ;
2125 rec_inst_sr_instance_id(inst_indx) := pinstance_id ;
2126 rec_inst_rec_resource_seq_num(inst_indx) := rsrc_tab(r).seq_dep_ind ;
2127 rec_inst_resource_id(inst_indx) := rsrc_tab(r).x_resource_id ;
2128 rec_inst_instance_id(inst_indx) := rsrc_tab(r).instance_number ;
2129 rec_inst_start_date(inst_indx) := v_start_date ;
2130 rec_inst_end_date(inst_indx) := v_end_date ;
2131 /* NAVIN: Divide the seq dep usage equally amongst the instances. */
2132 rec_inst_rsrc_instance_hours(inst_indx) := rsrc_tab(r).sequence_dependent_usage;-- * converted_usage;
2133 /* Sowmya - As per the latest FDD changes - multiply the usage with the conveted factor */
2134 rec_inst_operation_seq_num(inst_indx) := rsrc_tab(r).batchstep_no ;
2135 rec_inst_department_id(inst_indx) := ((v_orgn_id * 2) + 1) ;
2136 rec_inst_wip_entity_id(inst_indx) := rsrc_tab(r).x_batch_id ;
2137 rec_inst_serial_number(inst_indx) := rsrc_tab(r).eqp_serial_number ;
2138 rec_inst_original_seq_num(inst_indx) := TO_NUMBER(NULL) ;
2139 rec_inst_parent_seq_num(inst_indx) := rsrc_tab(r).original_seq_num;
2140 rec_inst_equp_item_id(inst_indx) := rsrc_tab(r).equp_item_id;
2141 /*Sowmya - As per the latest FDD changes - Resource Instances */
2142
2143 END IF; /* Sequence Dependency Row */
2144 END IF; /* rsrc_tab(r).resource_instance_usage > 0 AND resource_instance_usage_flag = 0 */
2145 END IF; /* rsrc_tab(r).instance_number <> -1 */
2146 /* ------------- Navin: END Process Resource Instances Requirements ------------- */
2147
2148 END LOOP; /* Resource Cursor */
2149
2150
2151 END IF; /* Routing_id is not null */
2152 END IF; /* item should be product */
2153
2154 IF prod_tab(p).line_id = prod_line_id THEN
2155 supply_type := 3; /* Product */
2156 ELSE
2157 supply_type := 14; /* Co Product or a by-Product */
2158 END IF;
2159
2160 /* ingredient get written to the demands. the quantity needs to be
2161 positive so we reverse it */
2162 IF prod_tab(p).line_type = -1 THEN
2163 IF prod_tab(p).batchstep_no = -1 /* 2919303 */
2164 THEN
2165 prod_tab(p).batchstep_no := TO_NUMBER(NULL);
2166 END IF;
2167 -- ----------------
2168 /* B3267522, Rajesh Patangya Do not insert demands, if ingradient is same as product
2169 (single level circular reference) */
2170
2171 /* nsinghi INVCONV Start */
2172 /* IF prod_tab(p).item_id <> product_line THEN */
2173 IF prod_tab(p).matl_item_id <> product_line THEN
2174 /* nsinghi INVCONV End */
2175 -- gmp_debug_message('Demand Item '|| prod_tab(p).matl_item_id ||' Qty '||prod_tab(p).qty);
2176 /* Demands Bulk inserts */
2177 d_index := d_index + 1 ;
2178 d_organization_id(d_index) := v_orgn_id ;
2179
2180 /* nsinghi INVCONV Start */
2181 /* d_inventory_item_id(d_index) := prod_tab(p).item_id ; */
2182 d_inventory_item_id(d_index) := prod_tab(p).matl_item_id ;
2183 /* nsinghi INVCONV End */
2184
2185 d_sr_instance_id(d_index) := pinstance_id ;
2186 d_assembly_item_id(d_index) := product_line ;
2187 d_demand_date(d_index) := prod_tab(p).trans_date ;
2188 /* Reverse sign to make positive */
2189 /*B4619070 - sowsura - With convergence ic_tran_pnd no longer exist and the ingredient
2190 demand quantity is picked from gme_material_details table. The ingredient qty as
2191 stored by GME is +ve. So we dont have to convert the qty into positive which
2192 was done pre-convergence.*/
2193 -- d_requirement_quantity(d_index) := (prod_tab(p).qty * -1);
2194 d_requirement_quantity(d_index) := prod_tab(p).qty;
2195 d_demand_type(d_index) := 1 ;
2196 d_origination_type(d_index) := 3 ;
2197 /* B1177070 encoded key */
2198 d_wip_entity_id(d_index) := prod_tab(p).x_batch_id ;
2199 d_demand_schedule(d_index) := null_value ;
2200 d_order_number(d_index) := order_no ;
2201 d_wip_entity_name(d_index) := null_value ;
2202 d_operation_seq_num(d_index) := prod_tab(p).batchstep_no; /* B2919303 Batchstep */
2203 d_selling_price(d_index) := null_value ;
2204
2205 /*B5100481 - sowsubra - WIP STATUS OF BATCHES NOT SHOWN*/
2206 IF prod_tab(p).batch_status = 1 THEN
2207 d_wip_status_code(d_index) := 16 ; /* batch status -> pending */
2208 ELSE
2209 d_wip_status_code(d_index) := 3 ; /* batch status -> WIP */
2210 END IF;
2211
2212 END IF; /* Circular reference */
2213 -- ----------------
2214
2215 /* If the line is a product or byproduct write to the supplies */
2216 ELSE
2217 IF prod_tab(p).batchstep_no = -1 /* 2919303 */
2218 THEN
2219 prod_tab(p).batchstep_no := TO_NUMBER(NULL);
2220 END IF;
2221
2222 -- gmp_debug_message('Supply Item '|| prod_tab(p).matl_item_id ||' Qty '||prod_tab(p).qty);
2223 /* Supply Bulk Insert Assignments */
2224 s_index := s_index + 1 ;
2225 /* nsinghi INVCONV Start */
2226 /* s_inventory_item_id(s_index) := prod_tab(p).item_id ; */
2227 s_inventory_item_id(s_index) := prod_tab(p).matl_item_id ;
2228 /* nsinghi INVCONV End */
2229 s_organization_id(s_index) := v_orgn_id ;
2230 s_sr_instance_id(s_index) := pinstance_id;
2231 s_new_schedule_date(s_index) := prod_tab(p).trans_date ;
2232 s_old_schedule_date(s_index) := prod_tab(p).trans_date ;
2233
2234 -- Bug: 8624913
2235 IF prod_tab(p).actual_start_date IS NOT NULL THEN
2236 s_new_wip_start_date(s_index) := prod_tab(p).actual_start_date ;
2237 s_actual_start_date(s_index) := prod_tab(p).actual_start_date ;
2238 ELSE
2239 s_new_wip_start_date(s_index) := prod_tab(p).start_date ;
2240 s_actual_start_date(s_index) := NULL ;
2241 END IF;
2242
2243 s_old_wip_start_date(s_index) := prod_tab(p).start_date ;
2244 s_lunit_completion_date(s_index) := prod_tab(p).end_date ;
2245 /* B1177070 encoded key */
2246 s_disposition_id(s_index) := prod_tab(p).x_batch_id ;
2247
2248 /* B8349005 Vpedarla From ASCP planning perspective, we only need a
2249 unique process_sequence_id in the msc_supplies (plan_id=-1, ODS data)
2250 for WIP jobs. It is requirement from our new engine code, starting
2251 from 11510 in other words for order_type 3,14.
2252 */
2253 s_process_seq_id(s_index) := l_process_seq_id + s_index ;
2254
2255 /*B5100481 - sowsubra - WIP STATUS OF BATCHES NOT SHOWN*/
2256 IF prod_tab(p).batch_status = 1 THEN
2257 s_wip_status_code(s_index) := 16 ; /* batch status -> pending */
2258 ELSE
2259 s_wip_status_code(s_index) := 3 ; /* batch status -> WIP */
2260 END IF;
2261
2262 IF supply_type IS NOT NULL THEN
2263 s_order_type(s_index) := supply_type ;
2264 ELSE
2265 s_order_type(s_index) := null_value ;
2266 END IF ;
2267
2268 IF order_no IS NOT NULL THEN
2269 s_order_number(s_index) := order_no ;
2270 ELSE
2271 s_order_number(s_index) := null_value ;
2272 END IF ;
2273
2274 -- Bug: 8614604 Vpedarla
2275 IF prod_tab(p).qty < 0 THEN
2276 prod_tab(p).qty := 0;
2277 END IF;
2278
2279 s_new_order_quantity(s_index) := prod_tab(p).qty ;
2280 s_old_order_quantity(s_index) := prod_tab(p).qty ;
2281 s_firm_planned_type(s_index) := prod_tab(p).firmed_ind; /* 2821248 Firmed Indicator */
2282 s_firm_quantity(s_index) := prod_tab(p).qty ; /* B2821248 Firmed Batches Qty - */
2283 s_firm_date(s_index) := prod_tab(p).trans_date; /* B2821248 Firmed Batches Date - */
2284
2285 s_requested_completion_date(s_index) := prod_tab(p).requested_completion_date; /* Navin : APS K Enh */
2286 s_schedule_priority(s_index) := prod_tab(p).schedule_priority; /* Navin : APS K Enh */
2287
2288 IF order_no IS NOT NULL THEN
2289 s_wip_entity_name(s_index) := order_no ;
2290 ELSE
2291 s_wip_entity_name(s_index) := null_value ;
2292 END IF ;
2293 -- lot_number := null_value ;
2294 -- expiration_date := null_value ;
2295 s_operation_seq_num(s_index) := prod_tab(p).batchstep_no; /* B2919303 Batchstep */
2296
2297 IF supply_type = 3 THEN
2298 s_by_product_using_assy_id(s_index) := to_number(NULL) ;
2299 ELSE
2300 s_by_product_using_assy_id(s_index) := product_line ;
2301 END IF;
2302
2303 /* Section 11.1.1.2 MTQ with Hardlinks */
2304 IF (prod_tab(p).Minimum_Time_Offset IS NOT NULL) THEN
2305 stp_var_itm_instance_id(si_index) := pinstance_id;
2306 stp_var_itm_from_op_seq_id(si_index) := prod_tab(p).from_op_seq_id ;
2307 stp_var_itm_wip_entity_id (si_index) := prod_tab(p).x_batch_id;
2308 /* nsinghi INVCONV Start */
2309 /* stp_var_itm_FROM_item_ID(si_index) := prod_tab(p).item_id; */
2310 stp_var_itm_FROM_item_ID(si_index) := prod_tab(p).matl_item_id;
2311 /* nsinghi INVCONV End */
2312
2313 stp_var_min_tran_qty(si_index) := prod_tab(p).Minimum_Transfer_Qty;
2314 stp_var_itm_min_tm_off(si_index) := prod_tab(p).Minimum_Time_Offset;
2315 stp_var_itm_max_tm_off(si_index) := prod_tab(p).Maximum_Time_Offset;
2316 stp_var_itm_from_op_seq_num(si_index) := prod_tab(p).from_op_seq_num;
2317 stp_var_itm_organization_id(si_index) := v_orgn_id ;
2318 si_index := si_index+1;
2319 END IF;
2320 END IF;
2321 END LOOP; /* all the details are retrieved so close the cursor */
2322 --close prod_dtl;
2323 -- =====================================Inserts =======================
2324 i := 1 ;
2325 log_message(rr_organization_id.FIRST || ' *rr*' || rr_organization_id.LAST );
2326 IF rr_organization_id.FIRST > 0 THEN
2327 FORALL i IN rr_organization_id.FIRST..rr_organization_id.LAST
2328 INSERT INTO msc_st_resource_requirements (
2329 organization_id,
2330 sr_instance_id,
2331 supply_id,
2332 supply_type, /* kbanddyo B6407864 Need to populate supply_type field */
2333 resource_seq_num,
2334 resource_id,
2335 start_date,
2336 end_date,
2337 operation_hours_required,
2338 usage_rate, /* B4637398 Rajesh Patangya */
2339 assigned_units,
2340 department_id,
2341 wip_entity_id,
2342 operation_seq_num,
2343 deleted_flag,
2344 firm_flag,
2345 minimum_transfer_quantity,
2346 parent_seq_num,
2347 schedule_flag,
2348 basis_type,
2349 setup_id,
2350 group_sequence_id,
2351 group_sequence_number,
2352 minimum_capacity,
2353 maximum_capacity,
2354 orig_resource_seq_num,
2355 alternate_number,
2356 hours_expended,
2357 breakable_activity_flag,
2358 inventory_item_id, /* B4777532 - product_item_id populated */
2359 step_quantity, /* Sowmya - As per latest FDD changes*/
2360 step_quantity_uom , /* Sowmya - As per latest FDD changes*/
2361 maximum_assigned_units, /* Sowmya - As per latest FDD changes*/
2362 unadjusted_resource_hours, /*B4320561 - Same as in wip (without eff and util) */
2363 touch_time, /* B4320561 - Unadjusted res. hrs / efficiency.*/
2364 activity_group_id, /* B3995361 rpatangy */
2365 -- activity_name, /* B5338598 rpatangy */
2366 operation_name, /* B5338598 rpatangy */
2367 operation_sequence_id, /* B5461922 rpatangy */
2368 operation_status -- Vpedarla 9319734
2369 )
2370 VALUES (
2371 rr_organization_id(i),
2372 rr_sr_instance_id(i),
2373 rr_supply_id(i),
2374 1, /* kbanddyo B6407864 supply_type = 1 for OPM batches*/
2375 rr_resource_seq_num(i),
2376 rr_resource_id(i),
2377 rr_start_date(i),
2378 rr_end_date(i),
2379 rr_opr_hours_required(i),
2380 nvl(rr_usage_rate(i),0), /* B4637398 Rajesh Patangya */
2381 rr_assigned_units(i),
2382 rr_department_id(i),
2383 rr_wip_entity_id(i),
2384 rr_operation_seq_num(i),
2385 2,
2386 rr_firm_flag(i),
2387 rr_minimum_transfer_quantity(i),
2388 rr_parent_seq_num(i),
2389 rr_schedule_flag(i),
2390 rr_basis_type(i),
2391 rr_setup_id(i),
2392 rr_sequence_id(i), -- group_sequence_id
2393 rr_sequence_number(i), -- group_sequence_number
2394 rr_min_capacity(i),
2395 rr_max_capacity(i),
2396 rr_original_seq_num(i),
2397 rr_alternate_number(i),
2398 rr_hours_expended(i),
2399 rr_breakable_activity_flag(i),
2400 rr_product_item_id(i), /* B4777532 - product_item_id populated */
2401 rr_plan_step_qty(i), /* Sowmya - As per the latest FDD changes*/
2402 rr_step_qty_uom(i) , /* Sowmya - As per the latest FDD changes*/
2403 rr_gmd_rsrc_cnt(i),
2404 rr_unadjusted_resource_hrs(i), /*B4320561 - sowsubra*/
2405 rr_touch_time(i), /*B4320561 - sowsubra*/
2406 rr_activity_group_id(i), /* B3995361 rpatangy */
2407 -- rr_activity_name(i), /* B5338598 rpatangy */
2408 rr_operation_no(i), /* B5338598 rpatangy */
2409 rr_operation_sequence_id(i), /* B5461922 rpatangy */
2410 rr_opr_status(i) -- Vpedarla 9319734
2411 ) ;
2412
2413 -- =============== memory release ====================
2414 rr_organization_id := empty_num_tbl ;
2415 rr_sr_instance_id := empty_num_tbl ;
2416 rr_supply_id := empty_num_tbl ;
2417 rr_resource_seq_num := empty_num_tbl ;
2418 rr_resource_id := empty_num_tbl ;
2419 rr_start_date := empty_date_tbl ;
2420 rr_end_date := empty_date_tbl ;
2421 rr_opr_hours_required := empty_num_tbl ;
2422 rr_usage_rate := empty_num_tbl ;
2423 rr_assigned_units := empty_num_tbl ;
2424 rr_department_id := empty_num_tbl ;
2425 rr_wip_entity_id := empty_num_tbl ;
2426 rr_operation_seq_num := empty_num_tbl ;
2427 rr_firm_flag := empty_num_tbl ;
2428 rr_minimum_transfer_quantity := empty_num_tbl ;
2429 rr_parent_seq_num := empty_num_tbl ;
2430 rr_schedule_flag := empty_num_tbl ;
2431 rr_basis_type := empty_num_tbl ;
2432 rr_setup_id := empty_num_tbl ;
2433 rr_sequence_id := empty_num_tbl ;
2434 rr_sequence_number := empty_num_tbl ;
2435 rr_min_capacity := empty_num_tbl ;
2436 rr_max_capacity := empty_num_tbl ;
2437 rr_original_seq_num := empty_num_tbl ;
2438 rr_alternate_number := empty_num_tbl ;
2439 rr_hours_expended := empty_num_tbl ;
2440 rr_breakable_activity_flag := empty_num_tbl ;
2441 rr_product_item_id := empty_num_tbl ;
2442 rr_plan_step_qty := empty_num_tbl ;
2443 rr_step_qty_uom := empty_step_qty_uom ;
2444 rr_gmd_rsrc_cnt := empty_num_tbl ;
2445 rr_unadjusted_resource_hrs := empty_num_tbl ;
2446 rr_touch_time := empty_num_tbl ;
2447 rr_activity_group_id := empty_num_tbl ;
2448 -- rr_activity_name := empty_batch_activity;
2449 -- rr_operation_no := empty_batch_activity;
2450 rr_operation_sequence_id := empty_num_tbl ; /* B5461922 rpatangy */
2451 rr_opr_status := empty_num_tbl ; -- Vpedarla Bug: 9319734
2452 -- =============== memory release ====================
2453 END IF;
2454 /* ----------------------- Supply Insert --------------------- */
2455 i := 1 ;
2456 log_message(s_organization_id.FIRST || ' *s*' || s_organization_id.LAST );
2457 IF s_organization_id.FIRST > 0 THEN
2458 FORALL i IN s_organization_id.FIRST..s_organization_id.LAST
2459 INSERT INTO msc_st_supplies (
2460 plan_id,
2461 inventory_item_id,
2462 organization_id,
2463 sr_instance_id,
2464 new_schedule_date,
2465 old_schedule_date,
2466 new_wip_start_date,
2467 old_wip_start_date,
2468 last_unit_completion_date,
2469 disposition_id,
2470 order_type,
2471 order_number,
2472 new_order_quantity,
2473 old_order_quantity,
2474 firm_planned_type,
2475 firm_quantity,
2476 firm_date,
2477 wip_entity_name,
2478 lot_number,
2479 expiration_date,
2480 operation_seq_num,
2481 by_product_using_assy_id,
2482 deleted_flag,
2483 requested_completion_date,
2484 wip_status_code, /*B5100481*/
2485 schedule_priority,
2486 process_seq_id, /* B8349005 */
2487 actual_start_date -- Bug: 8624913
2488 )
2489 VALUES (
2490 -1,
2491 s_inventory_item_id(i),
2492 s_organization_id(i),
2493 s_sr_instance_id(i),
2494 s_new_schedule_date(i),
2495 s_old_schedule_date(i),
2496 s_new_wip_start_date(i),
2497 s_old_wip_start_date(i),
2498 s_lunit_completion_date(i),
2499 s_disposition_id(i),
2500 s_order_type(i),
2501 s_order_number(i),
2502 s_new_order_quantity(i),
2503 s_old_order_quantity(i),
2504 s_firm_planned_type(i), /* 2 */
2505 s_firm_quantity(i),
2506 s_firm_date(i),
2507 s_wip_entity_name(i), /* Order Number */
2508 null_value,
2509 null_value,
2510 s_operation_seq_num(i),
2511 s_by_product_using_assy_id(i),
2512 2, /* Deleted Flag */
2513 s_requested_completion_date(i),
2514 s_wip_status_code(i), /*B5100481 - 16 for pending, 3 for wip */
2515 s_schedule_priority(i),
2516 s_process_seq_id(i), /* B8349005 */
2517 s_actual_start_date(i) -- Bug: 8624913
2518 ) ;
2519
2520 --====================== Memory release======================
2521 s_inventory_item_id := empty_num_tbl ;
2522 s_organization_id := empty_num_tbl ;
2523 s_sr_instance_id := empty_num_tbl ;
2524 s_new_schedule_date := empty_date_tbl ;
2525 s_old_schedule_date := empty_date_tbl ;
2526 s_new_wip_start_date := empty_date_tbl ;
2527 s_old_wip_start_date := empty_date_tbl ;
2528 s_lunit_completion_date := empty_date_tbl ;
2529 s_disposition_id := empty_num_tbl ;
2530 s_order_type := empty_num_tbl ;
2531 s_order_number := empty_sorder_number ;
2532 s_new_order_quantity := empty_num_tbl ;
2533 s_old_order_quantity := empty_num_tbl ;
2534 s_firm_planned_type := empty_num_tbl ;
2535 s_firm_quantity := empty_num_tbl ;
2536 s_firm_date := empty_date_tbl ;
2537 s_wip_entity_name := empty_swip_entity_name ;
2538 s_operation_seq_num := empty_num_tbl ;
2539 s_by_product_using_assy_id := empty_num_tbl ;
2540 s_requested_completion_date := empty_date_tbl ;
2541 s_wip_status_code := empty_num_tbl ;
2542 s_schedule_priority := empty_num_tbl ;
2543 s_process_seq_id := empty_num_tbl ; /* B8349005*/
2544 s_actual_start_date := empty_date_tbl ; -- Bug: 8624913
2545
2546 END IF;
2547 /* ----------------------- Demands Insert --------------------- */
2548 i := 1 ;
2549 log_message(d_organization_id.FIRST || '*' || d_index || '*' || d_organization_id.LAST );
2550 IF d_organization_id.FIRST > 0 THEN
2551 FORALL i IN d_organization_id.FIRST..d_organization_id.LAST
2552 INSERT INTO msc_st_demands (
2553 organization_id,
2554 inventory_item_id,
2555 sr_instance_id,
2556 using_assembly_item_id,
2557 using_assembly_demand_date,
2558 using_requirement_quantity,
2559 demand_type,
2560 origination_type,
2561 wip_entity_id,
2562 demand_schedule_name,
2563 order_number,
2564 wip_entity_name,
2565 selling_price,
2566 operation_seq_num,
2567 wip_status_code, /*B5100481*/
2568 deleted_flag )
2569 VALUES (
2570 d_organization_id(i),
2571 d_inventory_item_id(i),
2572 d_sr_instance_id(i),
2573 d_assembly_item_id(i),
2574 d_demand_date(i),
2575 d_requirement_quantity(i),
2576 d_demand_type(i),
2577 d_origination_type(i),
2578 d_wip_entity_id(i),
2579 d_demand_schedule(i),
2580 d_order_number(i),
2581 d_wip_entity_name(i),
2582 d_selling_price(i),
2583 d_operation_seq_num(i),
2584 d_wip_status_code(i), /*B5100481*/
2585 2 ) ;
2586 --================== Memory Release ========================
2587 d_organization_id := empty_num_tbl ;
2588 d_inventory_item_id := empty_num_tbl ;
2589 d_sr_instance_id := empty_num_tbl ;
2590 d_assembly_item_id := empty_num_tbl ;
2591 d_demand_date := empty_date_tbl ;
2592 d_requirement_quantity := empty_num_tbl ;
2593 d_demand_type := empty_num_tbl ;
2594 d_origination_type := empty_num_tbl ;
2595 d_wip_entity_id := empty_num_tbl ;
2596 d_demand_schedule := empty_demand_schedule ;
2597 d_order_number := empty_dorder_number ;
2598 d_wip_entity_name := empty_dwip_entity_name ;
2599 d_selling_price := empty_num_tbl ;
2600 d_operation_seq_num := empty_num_tbl ;
2601 d_wip_status_code := empty_num_tbl ;
2602 -- =============================================================
2603 END IF;
2604
2605 s_index := 0 ;
2606 d_index := 0 ;
2607 rr_index := 0 ;
2608
2609 /* NAVIN: ------------ START: Complex Route -- Collect Batch Step Dependencies in one insert-select ------------*/
2610 sql_stmt :=
2611 ' INSERT INTO msc_st_job_operation_networks '
2612 || ' ( '
2613 || ' from_op_seq_id, '
2614 || ' to_op_seq_id, '
2615 || ' wip_entity_id, '
2616 || ' dependency_type, '
2617 || ' transition_type, '
2618 || ' sr_instance_id, '
2619 || ' deleted_flag, '
2620 || ' minimum_time_offset, '
2621 || ' maximum_time_offset, '
2622 || ' transfer_pct, '
2623 || ' from_op_seq_num, '
2624 || ' to_op_seq_num, '
2625 || ' apply_to_charges, '
2626 || ' organization_id '
2627 || ' ) '
2628 || ' SELECT '
2629 ||' ((gbsd.dep_step_id*2)+1), ' /* B5461922 */
2630 ||' ((gbsd.batchstep_id*2)+1),' /* B5461922 */
2631 ||' ((gbsd.batch_id * 2) + 1) x_batch_id, '
2632 ||' decode(gbsd.dep_type,0,1,2) dependency_type, '
2633 ||' 1, '
2634 ||' :1, '
2635 ||' 2, '
2636 ||' gbsd.standard_delay, '
2637 ||' gbsd.max_delay, '
2638 ||' gbsd.transfer_percent, '
2639 ||' gbs1.batchstep_no, '
2640 ||' gbs2.batchstep_no, '
2641 ||' DECODE(NVL(gbsd.chargeable_ind,0),1,1,2), ' /* convert a Null or 0 to a 2, a 1 remains a 1 */
2642 /* nsinghi INVCONV Start */
2643 /* ||' iwm.mtl_organization_id ' */
2644 ||' h.organization_id '
2645 /* nsinghi INVCONV End */
2646
2647 ||' FROM '
2648 ||' gme_batch_step_dependencies'||pdblink||' gbsd, '
2649 ||' gme_batch_header'||pdblink||' h,'
2650 ||' gme_batch_steps'||pdblink||' gbs1, '
2651 ||' gme_batch_steps'||pdblink||' gbs2 '
2652 /* nsinghi INVCONV Start */
2653 /* ||' ic_whse_mst'||pdblink||' iwm, '
2654 ||' sy_orgn_mst'||pdblink||' som ' */
2655 /* nsinghi INVCONV End */
2656
2657 ||' WHERE '
2658 ||' h.batch_id = gbsd.batch_id '
2659 ||' AND gbs1.batch_id = gbsd.batch_id '
2660 ||' AND gbs1.batchstep_id = gbsd.dep_step_id '
2661 ||' AND gbs2.batch_id = gbsd.batch_id '
2662 ||' AND gbs2.batchstep_id = gbsd.batchstep_id '
2663 ||' AND h.batch_status in (1, 2) ';
2664
2665 -- For alcoa Issue 13097786 Starts
2666 IF v_Overyielded_wip = 0 THEN
2667 sql_stmt := sql_stmt
2668 || ' AND NOT EXISTS (SELECT 1 '
2669 || ' FROM '
2670 || ' gme_material_details'||pdblink||' gmd '
2671 || ' WHERE '
2672 || ' gmd.batch_id = h.batch_id '
2673 || ' AND (NVL(gmd.wip_plan_qty,gmd.plan_qty) - gmd.actual_qty ) <= 0 '
2674 || ' AND gmd.line_type = 1 ) ' ;
2675 END IF;
2676 -- For alcoa Issue 13097786 Ends
2677
2678 /* nsinghi INVCONV Start */
2679 /* ||' AND h.plant_code = som.orgn_code '
2680 ||' AND som.delete_mark = 0 '
2681 ||' AND som.resource_whse_code = iwm.whse_code ' ; */
2682 /* nsinghi INVCONV End */
2683
2684 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
2685 sql_stmt := sql_stmt
2686 /* nsinghi INVCONV Start */
2687 /* ||' AND iwm.mtl_organization_id ' || gmp_calendar_pkg.g_in_str_org ; */
2688 ||' AND h.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
2689 /* nsinghi INVCONV End */
2690 END IF;
2691
2692 EXECUTE IMMEDIATE sql_stmt USING pinstance_id;
2693 /* NAVIN: ------------ END: Complex Route -- Collect Batch Step Dependencies in one insert-select ------------*/
2694
2695 /* NAVIN: ----------------------- MTQ with Hardlinks --------------------- */
2696 i := 1 ;
2697 IF stp_var_itm_from_op_seq_id.FIRST > 0 THEN
2698 FORALL i IN stp_var_itm_from_op_seq_id.FIRST..stp_var_itm_from_op_seq_id.LAST
2699 INSERT INTO msc_st_job_operation_networks(
2700 from_op_seq_id,
2701 wip_entity_id,
2702 dependency_type,
2703 transition_type,
2704 sr_instance_id,
2705 deleted_flag,
2706 from_item_id,
2707 organization_id,
2708 minimum_time_offset,
2709 maximum_time_offset,
2710 from_op_seq_num,
2711 minimum_transfer_qty
2712 )
2713 VALUES
2714 (
2715 stp_var_itm_from_op_seq_id(i),
2716 stp_var_itm_wip_entity_id(i),
2717 5, -- dependency_type for mtq with hardlink
2718 1, -- transition_type: primary
2719 stp_var_itm_instance_id(i),
2720 2,
2721 stp_var_itm_FROM_item_ID(i),
2722 stp_var_itm_organization_id(i),
2723 stp_var_itm_min_tm_off(i),
2724 stp_var_itm_max_tm_off(i),
2725 stp_var_itm_from_op_seq_num(i),
2726 stp_var_min_tran_qty(i)
2727 );
2728 -- ================== Memory Release ===============================
2729 stp_var_itm_from_op_seq_id := empty_num_tbl ;
2730 stp_var_itm_wip_entity_id := empty_num_tbl ;
2731 stp_var_itm_instance_id := empty_num_tbl ;
2732 stp_var_itm_from_item_id := empty_num_tbl ;
2733 stp_var_itm_organization_id := empty_num_tbl ;
2734 stp_var_itm_min_tm_off := empty_num_tbl ;
2735 stp_var_itm_max_tm_off := empty_num_tbl ;
2736 stp_var_itm_from_op_seq_num := empty_num_tbl ;
2737 stp_var_min_tran_qty := empty_num_tbl ;
2738 -- ================== Memory Release ===============================
2739 END IF ;
2740 /* ----------------------- MTQ with Hardlinks --------------------- */
2741
2742
2743 /* ----------------------- Operation Charges --------------------- */
2744 /* NAVIN: Operation Charges */
2745 i := 1 ;
2746 IF stp_chg_resource_id.FIRST > 0 THEN
2747 FORALL i IN stp_chg_resource_id.FIRST..stp_chg_resource_id.LAST
2748 INSERT INTO msc_st_resource_charges
2749 (
2750 sr_instance_id ,
2751 resource_id ,
2752 organization_id ,
2753 department_id ,
2754 wip_entity_id ,
2755 operation_sequence_id ,
2756 operation_seq_num ,
2757 resource_seq_num ,
2758 charge_number ,
2759 charge_quantity ,
2760 deleted_flag ,
2761 charge_start_datetime ,
2762 charge_end_datetime
2763 )
2764
2765 VALUES
2766
2767 (
2768 stp_instance_id(i) ,
2769 stp_chg_resource_id(i) ,
2770 stp_chg_organization_id(i),
2771 stp_chg_department_id(i),
2772 stp_chg_wip_entity_id(i),
2773 stp_chg_operation_seq_id(i),
2774 stp_chg_operation_seq_no(i),
2775 stp_chg_resource_seq_num(i),
2776 stp_chg_charge_num(i),
2777 stp_chg_charge_quanitity(i),
2778 2,
2779 stp_chg_charge_start_dt_time(i) ,
2780 stp_chg_charge_end_dt_time(i)
2781 );
2782 --======================== Memory Release====================
2783 stp_instance_id := empty_num_tbl ;
2784 stp_chg_resource_id := empty_num_tbl ;
2785 stp_chg_organization_id := empty_num_tbl ;
2786 stp_chg_department_id := empty_num_tbl ;
2787 stp_chg_wip_entity_id := empty_num_tbl ;
2788 stp_chg_operation_seq_id := empty_num_tbl ;
2789 stp_chg_operation_seq_no := empty_num_tbl ;
2790 stp_chg_resource_seq_num := empty_num_tbl ;
2791 stp_chg_charge_num := empty_num_tbl ;
2792 stp_chg_charge_quanitity := empty_num_tbl ;
2793 stp_chg_charge_start_dt_time := empty_date_tbl ;
2794 stp_chg_charge_end_dt_time := empty_date_tbl ;
2795 --======================== Memory Release====================
2796 END IF ;
2797 /* ----------------------- Operation Charges --------------------- */
2798
2799 /* ----------------------- Resource Instances --------------------- */
2800
2801 i := 1 ;
2802 log_message(rec_inst_organization_id.FIRST || ' *rir*' || rec_inst_organization_id.LAST );
2803 IF rec_inst_organization_id.FIRST > 0 THEN
2804 FORALL i IN rec_inst_organization_id.FIRST..rec_inst_organization_id.LAST
2805 INSERT INTO msc_st_resource_instance_reqs (
2806 supply_id,
2807 organization_id,
2808 sr_instance_id,
2809 resource_seq_num,
2810 resource_id,
2811 res_instance_id,
2812 start_date,
2813 end_date,
2814 resource_instance_hours,
2815 /* NAVIN :- CHECK Should This be Included. It is
2816 mentioned in FDD, but not included in APS script file. */
2817 -- schedule_flag,
2818 operation_seq_num,
2819 department_id,
2820 wip_entity_id,
2821 serial_number,
2822 deleted_flag,
2823 parent_seq_num, /* Sowmya - as the column was changed from parent_seq_number to parent_seq_num */
2824 orig_resource_seq_num,
2825 equipment_item_id /*Sowmya - As per the latest FDD changes - End*/
2826 )
2827 VALUES (
2828 rec_inst_supply_id(i) ,
2829 rec_inst_organization_id(i) ,
2830 rec_inst_sr_instance_id(i) ,
2831 rec_inst_rec_resource_seq_num(i) ,
2832 rec_inst_resource_id(i) ,
2833 rec_inst_instance_id(i) ,
2834 rec_inst_start_date(i) ,
2835 rec_inst_end_date(i) ,
2836 rec_inst_rsrc_instance_hours(i) ,
2837 -- 1 , /* Schedule Flag 1 = Scheduled */
2838 rec_inst_operation_seq_num(i) ,
2839 rec_inst_department_id(i) ,
2840 rec_inst_wip_entity_id(i) ,
2841 rec_inst_serial_number(i) ,
2842 2 , /* Delete Flag */
2843 rec_inst_parent_seq_num(i) ,
2844 rec_inst_original_seq_num(i),
2845 rec_inst_equp_item_id(i) /*Sowmya - As per the latest FDD changes - End*/
2846 ) ;
2847 --=================== Memory Release====================
2848 rec_inst_supply_id := empty_num_tbl ;
2849 rec_inst_organization_id := empty_num_tbl ;
2850 rec_inst_sr_instance_id := empty_num_tbl ;
2851 rec_inst_rec_resource_seq_num := empty_num_tbl ;
2852 rec_inst_resource_id := empty_num_tbl ;
2853 rec_inst_instance_id := empty_num_tbl ;
2854 rec_inst_start_date := empty_date_tbl ;
2855 rec_inst_end_date := empty_date_tbl ;
2856 rec_inst_rsrc_instance_hours := empty_num_tbl ;
2857 rec_inst_operation_seq_num := empty_num_tbl ;
2858 rec_inst_department_id := empty_num_tbl ;
2859 rec_inst_wip_entity_id := empty_num_tbl ;
2860 rec_inst_serial_number := empty_inst_serial_number ;
2861 rec_inst_parent_seq_num := empty_num_tbl ;
2862 rec_inst_original_seq_num := empty_num_tbl ;
2863 rec_inst_equp_item_id := empty_num_tbl ;
2864 --=================== Memory Release====================
2865 END IF;
2866 /* ----------------------- Resource Instances --------------------- */
2867
2868 /* ----------------------- Alternate Resources --------------------- */
2869 /*Sowmya - As per the latest FDD changes - Start*/
2870 i := 1 ;
2871 log_message(arr_organization_id.FIRST || ' *rir*' || arr_organization_id.LAST );
2872 IF arr_organization_id.FIRST > 0 THEN
2873 FORALL i IN arr_organization_id.FIRST..arr_organization_id.LAST
2874
2875 INSERT INTO msc_st_job_op_resources
2876 (
2877 wip_entity_id,
2878 organization_id,
2879 sr_instance_id ,
2880 operation_seq_num ,
2881 resource_seq_num ,
2882 resource_id ,
2883 alternate_num ,
2884 reco_start_date ,
2885 reco_completion_date ,
2886 usage_rate_or_amount ,
2887 assigned_units ,
2888 schedule_flag ,
2889 parent_seq_num ,
2890 recommended ,
2891 department_id ,
2892 uom_code ,
2893 activity_group_id ,
2894 basis_type ,
2895 firm_flag ,
2896 setup_id ,
2897 schedule_seq_num ,
2898 group_sequence_id ,
2899 group_sequence_number ,
2900 -- resource_batch_id ,
2901 maximum_assigned_units ,
2902 deleted_flag ,
2903 batch_number
2904 )
2905 VALUES
2906 (
2907 arr_wip_entity_id(i),
2908 arr_organization_id(i),
2909 arr_sr_instance_id(i),
2910 arr_operation_seq_num(i),
2911 arr_res_seq_num(i),
2912 arr_resource_id(i),
2913 arr_alternate_num(i),
2914 null_value,
2915 null_value,
2916 arr_usage_rate(i),
2917 arr_assigned_units(i),
2918 1,
2919 null_value,
2920 1,
2921 arr_department_id(i),
2922 arr_uom_code(i),
2923 arr_activity_group_id(i),
2924 arr_basis_type(i),
2925 null_value,
2926 arr_setup_id(i),
2927 arr_schedule_seq_num(i),
2928 null_value,
2929 null_value,
2930 -- null_value,
2931 arr_maximum_assigned_units(i),
2932 2,
2933 null_value
2934 );
2935 --================= Memory Release ==============
2936 arr_wip_entity_id := empty_num_tbl ;
2937 arr_organization_id := empty_num_tbl ;
2938 arr_sr_instance_id := empty_num_tbl ;
2939 arr_operation_seq_num := empty_num_tbl ;
2940 arr_res_seq_num := empty_num_tbl ;
2941 arr_resource_id := empty_num_tbl ;
2942 arr_alternate_num := empty_num_tbl ;
2943 arr_usage_rate := empty_num_tbl ;
2944 arr_assigned_units := empty_num_tbl ;
2945 arr_department_id := empty_num_tbl ;
2946 arr_uom_code := empty_arr_uom_code ;
2947 arr_activity_group_id := empty_num_tbl ;
2948 arr_basis_type := empty_num_tbl ;
2949 arr_setup_id := empty_num_tbl ;
2950 arr_schedule_seq_num := empty_num_tbl ;
2951 arr_maximum_assigned_units := empty_num_tbl ;
2952 --====================Memory Release=============
2953
2954 END IF;
2955 /*Sowmya - As per the latest FDD changes - End*/
2956 /* ----------------------- Alternate Resources --------------------- */
2957
2958 /* nsinghi : Populate Msc_Job_Operations Table. */
2959 /* ----------------------- Job Operations --------------------- */
2960 i := 1 ;
2961 log_message(jo_wip_entity_id.FIRST || ' *jo*' || jo_wip_entity_id.LAST );
2962 IF jo_wip_entity_id.FIRST > 0 THEN
2963 FORALL i IN jo_wip_entity_id.FIRST..jo_wip_entity_id.LAST
2964 INSERT INTO msc_st_job_operations
2965 (
2966 wip_entity_id,
2967 sr_instance_id,
2968 operation_seq_num,
2969 recommended,
2970 network_start_end,
2971 reco_start_date,
2972 reco_completion_date,
2973 operation_sequence_id,
2974 organization_id,
2975 department_id,
2976 minimum_transfer_quantity,
2977 effectivity_date,
2978 deleted_flag
2979 )
2980 VALUES
2981 (
2982 jo_wip_entity_id(i),
2983 jo_instance_id(i),
2984 jo_operation_seq_num(i),
2985 jo_recommended(i),
2986 jo_network_start_end(i),
2987 jo_reco_start_date(i),
2988 jo_reco_completion_date(i),
2989 jo_operation_sequence_id(i),
2990 jo_organization_id(i),
2991 jo_department_id(i),
2992 jo_minimum_transfer_quantity(i),
2993 SYSDATE-100,
2994 2
2995 );
2996 --================ Memory Release ========================
2997 jo_wip_entity_id := empty_num_tbl ;
2998 jo_instance_id := empty_num_tbl ;
2999 jo_operation_seq_num := empty_num_tbl ;
3000 jo_recommended := empty_jo_recommended ;
3001 jo_network_start_end := empty_jo_recommended ;
3002 jo_reco_start_date := empty_date_tbl ;
3003 jo_reco_completion_date := empty_date_tbl ;
3004 jo_operation_sequence_id := empty_num_tbl ;
3005 jo_organization_id := empty_num_tbl ;
3006 jo_department_id := empty_num_tbl ;
3007 jo_minimum_transfer_quantity := empty_num_tbl ;
3008 --================= Memory Release =========================
3009
3010 END IF;
3011 /* ----------------------- Job Operations --------------------- */
3012 DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
3013
3014 return_status := TRUE;
3015
3016 EXCEPTION
3017 WHEN invalid_string_value THEN
3018 log_message('Organization string is Invalid ' );
3019 return_status := FALSE;
3020
3021 WHEN invalid_gmp_uom_profile THEN
3022 log_message('Profile "GMP: UOM for Hour" is Invalid ' );
3023 return_status := FALSE;
3024
3025 WHEN NO_DATA_FOUND THEN /* B3577871 */
3026 log_message(' NO_DATA_FOUND exception raised in Procedure: Gmp_aps_ds_pull.Production_orders ' );
3027 return_status := TRUE;
3028
3029 WHEN OTHERS THEN
3030 return_status := FALSE;
3031 log_message('Failure occured during Production Orders extract' || sqlerrm);
3032 log_message(sqlerrm);
3033
3034 END production_orders;
3035
3036 /***********************************************************************
3037 *
3038 * NAME
3039 * insert_supplies
3040 *
3041 * DESCRIPTION
3042 * This procedure will take the parameter values and insert a row into
3043 * the table msc_st_supplies
3044 * HISTORY
3045 * M Craig
3046 * 2/10/2000 - Populating Order number column with Wip Entity Name ( porder_no )
3047 * 2/24/2003 - populating Firmed batches Indicator, Qty and Date
3048 ************************************************************************/
3049 PROCEDURE insert_supplies(
3050 pitem_id PLS_INTEGER,
3051 porganization_id PLS_INTEGER,
3052 pinstance_id PLS_INTEGER,
3053 pdate DATE,
3054 pstart_date DATE,
3055 pend_date DATE,
3056 pbatch_id PLS_INTEGER,
3057 pqty NUMBER,
3058 pfirmed_ind NUMBER,
3059 pbatchstep_no NUMBER, /* Added pbatchstep_no - B2919303 */
3060 porder_no VARCHAR2,
3061 plot_number VARCHAR2,
3062 pexpire_date DATE,
3063 psupply_type NUMBER,
3064 pproduct_item_id PLS_INTEGER) /* B2953953 - CoProduct */
3065
3066 AS
3067 st_supplies VARCHAR2(4000) ;
3068 vproduct_item_id NUMBER ; /* B2953953 - CoProduct */
3069 BEGIN
3070
3071 st_supplies :=
3072 ' INSERT INTO msc_st_supplies ( '
3073 ||' plan_id, inventory_item_id, organization_id, sr_instance_id, '
3074 ||' new_schedule_date, old_schedule_date, new_wip_start_date, '
3075 ||' old_wip_start_date, last_unit_completion_date, disposition_id, '
3076 ||' order_type, order_number, new_order_quantity, old_order_quantity, '
3077 ||' firm_planned_type,firm_quantity,firm_date, wip_entity_name, '
3078 ||' lot_number, expiration_date,operation_seq_num, by_product_using_assy_id, '
3079 ||' deleted_flag ) '
3080 ||' VALUES '
3081 ||' (:p1, :p2, :p3, :p4, '
3082 ||' :p5, :p6, :p7, '
3083 ||' :p8, :p9, :p10, '
3084 ||' :p11,:p12,:p13,:p14,'
3085 ||' :p15,:p16,:p17,:p18,'
3086 ||' :p19,:p20,:p21,'
3087 ||' :p22,:p23 ) ' ;
3088
3089 /* B2953953 The by_product_assy_id should not be written for Products ,
3090 but should be written for co-products and by-products */
3091
3092 IF psupply_type = 3 THEN
3093 vproduct_item_id := to_number(NULL) ;
3094 ELSE
3095 vproduct_item_id := pproduct_item_id ;
3096 END IF;
3097
3098 EXECUTE IMMEDIATE st_supplies USING
3099 -1,
3100 pitem_id,
3101 porganization_id,
3102 pinstance_id,
3103 pdate,
3104 pdate,
3105 pstart_date,
3106 pstart_date,
3107 pend_date,
3108 pbatch_id,
3109 psupply_type,
3110 porder_no, /* Populating Order no column - bug#1152778 */
3111 pqty,
3112 pqty,
3113 /* 2, */
3114 pfirmed_ind, /* B2821248 Firmed Batches Indicator - */
3115 pqty, /* B2821248 Firmed Batches Qty - */
3116 pdate, /* B2821248 Firmed Batches Date - */
3117 porder_no,
3118 plot_number,
3119 pexpire_date,
3120 pbatchstep_no, /* B2919303 */
3121 vproduct_item_id, /* B2953953 - Co-Product - */
3122 2 ;
3123
3124 EXCEPTION
3125 WHEN OTHERS THEN
3126 log_message('Failure occured during the insert into msc_st_supplies');
3127 log_message(sqlerrm);
3128 RAISE;
3129
3130 END insert_supplies;
3131
3132 /***********************************************************************
3133 *
3134 * NAME
3135 * insert_resource_requirements
3136 *
3137 * DESCRIPTION
3138 * This procedure wil insert a row into the table
3139 * msc_st_resource_requirements using the parameters passed in
3140 * HISTORY
3141 * M Craig
3142 * 10/13/99 - Added deleted_flag in the insert statement
3143 * 13-SEP-2002 - firm_flag = 1 for WIP steps B2266934
3144 ************************************************************************/
3145 PROCEDURE insert_resource_requirements(
3146 porganization_id IN PLS_INTEGER,
3147 pinstance_id IN PLS_INTEGER,
3148 pseq_num IN PLS_INTEGER,
3149 presource_id IN PLS_INTEGER,
3150 pstart_date IN DATE,
3151 pend_date IN DATE,
3152 presource_usage IN NUMBER,
3153 prsrc_cnt IN NUMBER,
3154 pbatchstep_no IN NUMBER, /* B1224660 new parm to write step number */
3155 pbatch_id IN PLS_INTEGER,
3156 pstep_status IN NUMBER,
3157 pschedule_flag IN NUMBER,
3158 pparent_seq_num IN NUMBER,
3159 pmin_xfer_qty IN NUMBER)
3160
3161 AS
3162 st_resource_requirements VARCHAR2(2000) ;
3163
3164 BEGIN
3165 st_resource_requirements :=
3166 ' INSERT INTO msc_st_resource_requirements ( '
3167 ||' organization_id, sr_instance_id, supply_id, resource_seq_num,'
3168 ||' resource_id, start_date, end_date, operation_hours_required,'
3169 ||' assigned_units, department_id, wip_entity_id, operation_seq_num, '
3170 ||' deleted_flag, firm_flag, minimum_transfer_quantity, '
3171 ||' parent_seq_num, schedule_flag ) '
3172 ||' VALUES '
3173 ||' ( :p1, :p2, :p3, :p4, '
3174 ||' :p5, :p6, :p7, :p8, '
3175 ||' :p9, :p10,:p11,:p12, '
3176 ||' :p13,:p14, :p15, '
3177 ||' :p16, :p17 ) ';
3178
3179 EXECUTE IMMEDIATE st_resource_requirements USING
3180 porganization_id,
3181 pinstance_id,
3182 pbatch_id,
3183 pseq_num,
3184 presource_id,
3185 pstart_date,
3186 pend_date,
3187 presource_usage,
3188 prsrc_cnt,
3189 ((porganization_id * 2) + 1), /* B1177070 encoded key */
3190 pbatch_id,
3191 pbatchstep_no, /* B1224660 write the step number for oper seq num */
3192 2,
3193 pstep_status,
3194 pmin_xfer_qty,
3195 pparent_seq_num,
3196 pschedule_flag ;
3197
3198 EXCEPTION
3199 WHEN OTHERS THEN
3200 log_message('Failure occured during the insert into msc_st_resource_requirements');
3201 log_message(sqlerrm);
3202 RAISE;
3203
3204 END insert_resource_requirements;
3205
3206 /***********************************************************************
3207 *
3208 * NAME
3209 * insert_demands
3210 *
3211 * DESCRIPTION
3212 * This procedure will take the parameter values and insert a row into
3213 * the table msc_st_demands
3214 * HISTORY
3215 * M Craig
3216 * 10/13/99 - Added deleted_flag in the insert statement
3217 * P Dong
3218 * 09/14/01 - added api_mode and pschedule_id parameters
3219 ************************************************************************/
3220 PROCEDURE insert_demands(
3221 pitem_id PLS_INTEGER,
3222 porganization_id PLS_INTEGER,
3223 pinstance_id PLS_INTEGER,
3224 pbatch_id PLS_INTEGER,
3225 pproduct_item_id PLS_INTEGER,
3226 pdate DATE,
3227 pqty NUMBER,
3228 pbatchstep_no NUMBER, /* B2919303 - BatchStep */
3229 porder_no VARCHAR2,
3230 pdesignator VARCHAR2,
3231 pnet_price NUMBER, /* B1200400 added net price */
3232 porigination_type NUMBER,
3233 api_mode BOOLEAN,
3234 pschedule_id NUMBER )
3235
3236 AS
3237
3238 statement_demands_api VARCHAR2(3000) ;
3239 statement_demands VARCHAR2(3000) ;
3240 t_order_number VARCHAR2(70) ;
3241 t_wip_entity_name VARCHAR2(70) ;
3242
3243 BEGIN
3244 t_order_number := NULL ;
3245 t_wip_entity_name := NULL ;
3246
3247 /* mfc 11-30-99 changed to write batch_id to wip_entity_id */
3248
3249 IF api_mode
3250 THEN
3251 BEGIN
3252 statement_demands_api :=
3253 ' INSERT INTO gmp_demands_api ( '
3254 ||' organization_id, schedule_id, inventory_item_id, demand_date, '
3255 ||' demand_quantity, origination_type, doc_id, selling_price ) '
3256 ||' VALUES '
3257 ||' ( :p1, :p2, :p3, :p4, '
3258 ||' :p5, :p6, :p7, :p8 ) ';
3259
3260 EXECUTE IMMEDIATE statement_demands_api USING
3261 porganization_id,
3262 pschedule_id,
3263 pitem_id,
3264 pdate,
3265 pqty,
3266 porigination_type,
3267 pbatch_id,
3268 pnet_price;
3269
3270 EXCEPTION
3271 WHEN OTHERS THEN
3272 log_message('Failure occured during the insert into gmp_demands_api');
3273 log_message(sqlerrm);
3274 RAISE;
3275 END;
3276 ELSE
3277 BEGIN
3278
3279 SELECT DECODE(porigination_type,1,NULL,porder_no) ,
3280 DECODE(porigination_type,1,porder_no,NULL)
3281 INTO t_order_number, t_wip_entity_name
3282 FROM dual ;
3283
3284 statement_demands :=
3285 ' INSERT INTO msc_st_demands ( '
3286 ||' organization_id, inventory_item_id, sr_instance_id, '
3287 ||' using_assembly_item_id, using_assembly_demand_date, '
3288 ||' using_requirement_quantity, demand_type, origination_type, '
3289 ||' wip_entity_id, demand_schedule_name, order_number, '
3290 ||' wip_entity_name, selling_price,operation_seq_num,deleted_flag ) '
3291 ||' VALUES '
3292 ||' ( :p1, :p2, :p3, '
3293 ||' :p4, :p5, '
3294 ||' :p6, :p7, :p8 , '
3295 ||' :p9, :p10,:p11, '
3296 ||' :p12,:p13,:p14,:p15 )' ;
3297
3298 EXECUTE IMMEDIATE statement_demands USING
3299 porganization_id,
3300 pitem_id,
3301 pinstance_id,
3302 pproduct_item_id,
3303 pdate,
3304 pqty,
3305 1,
3306 porigination_type,
3307 pbatch_id,
3308 pdesignator,
3309 t_order_number,
3310 t_wip_entity_name,
3311 pnet_price, /* B1200400 added for net price */
3312 pbatchstep_no, /* B2919303 */
3313 2 ;
3314 EXCEPTION
3315 WHEN OTHERS THEN
3316 log_message('Failure occured during the insert into msc_st_demands');
3317 log_message(sqlerrm);
3318 RAISE;
3319 END;
3320
3321 END IF;
3322
3323 END insert_demands;
3324
3325 /***********************************************************************
3326 *
3327 * NAME
3328 * onhand_inventory
3329 *
3330 * DESCRIPTION
3331 * This procedure will insert records into the table msc_st_supplies
3332 * for the onhand balances in inventory. The insert is split into 3 parts
3333 * one for non-lot controlled, lot controlled, and lot and status
3334 * controlled item. Each inserted will need touse a distnct list from
3335 * the table gmp_item_aps. The table may contain multiple values for
3336 * the item/whse combination
3337 * HISTORY
3338 * M Craig
3339 * M Craig B1332662 changed to call two new procs to collect onhand and
3340 * Inventory transfers
3341 * Navin 21-APR-2003 B3577871 ST:OSFME2: collections failing in planning data pull.
3342 * Added handling of NO_DATA_FOUND Exception.
3343 * And return the return_status as TRUE.
3344 ************************************************************************/
3345 PROCEDURE onhand_inventory(
3346 pdblink IN VARCHAR2,
3347 pinstance_id IN PLS_INTEGER,
3348 prun_date IN DATE,
3349 pdelimiter IN VARCHAR2,
3350 return_status IN OUT NOCOPY BOOLEAN)
3351 AS
3352
3353 local_ret_status1 BOOLEAN := TRUE;
3354 local_ret_status2 BOOLEAN := TRUE;
3355 onhand_balances_failure EXCEPTION ;
3356 inv_transfer_failure EXCEPTION ;
3357
3358 BEGIN
3359
3360 IF return_status THEN
3361 v_cp_enabled := TRUE;
3362 ELSE
3363 v_cp_enabled := FALSE;
3364 END IF;
3365
3366 extract_onhand_balances( pdblink, pinstance_id, prun_date, pdelimiter,
3367 local_ret_status1);
3368
3369 IF local_ret_status1 = TRUE THEN
3370 return_status := TRUE;
3371 ELSE
3372 return_status := FALSE;
3373 RAISE onhand_balances_failure ;
3374 END IF;
3375
3376 /* B 2756431 Changed the call to new proceudre */
3377
3378 /* nsinghi INVCONV Start */
3379 /* As in converged inventory we cannot pending inventory transfers, hence commenting
3380 the call to this procedure. */
3381
3382 /*
3383 extract_inv_transfer_supplies(pdblink, pinstance_id, prun_date,
3384 pdelimiter, local_ret_status2);
3385
3386 IF local_ret_status2 = TRUE THEN
3387 return_status := TRUE ;
3388 ELSE
3389 return_status := FALSE;
3390 RAISE inv_transfer_failure ;
3391 END IF;
3392 */
3393 /* nsinghi INVCONV End */
3394
3395 EXCEPTION
3396 WHEN onhand_balances_failure THEN
3397 log_message(' extract_onhand_balances_failure raised in Procedure: Gmp_aps_ds_pull.Onhand_inventory ' );
3398 return_status := FALSE;
3399 WHEN inv_transfer_failure THEN
3400 log_message(' extract_inv_transfer_supplies_failure raised in Procedure: Gmp_aps_ds_pull.Onhand_inventory ' );
3401 return_status := FALSE;
3402 WHEN NO_DATA_FOUND THEN /* B3577871 */
3403 log_message(' NO_DATA_FOUND exception raised in Procedure: Gmp_aps_ds_pull.Onhand_inventory ' );
3404 return_status := TRUE;
3405
3406 END onhand_inventory; /* end onhand_inventory */
3407
3408 /***********************************************************************
3409 *
3410 * NAME
3411 * extract_onhand_balances
3412 *
3413 * DESCRIPTION
3414 * This procedure will insert records into the table msc_st_supplies
3415 * for the onhand balances in inventory. The insert is split into 3 parts
3416 * one for non-lot controlled, lot controlled, and lot and status
3417 * controlled item. Each inserted will need touse a distnct list from
3418 * the table gmp_item_aps. The table may contain multiple values for
3419 * the item/whse combination
3420 * HISTORY
3421 * M Craig
3422 * 10/13/99 - Added deleted_flag in the insert statement
3423 * 2/10/2000 - Populating sub inventory code with whse code - bug# 1172875
3424 * M Craig B1332662 created a new function to just collect onhand inventory
3425 * Sgidugu B2251375 - Changed Substr Function to substrb Function
3426 ************************************************************************/
3427 PROCEDURE extract_onhand_balances(
3428 pdblink IN VARCHAR2,
3429 pinstance_id IN PLS_INTEGER,
3430 prun_date IN DATE,
3431 pdelimiter IN VARCHAR2,
3432 return_status IN OUT NOCOPY BOOLEAN)
3433 AS
3434
3435 BEGIN
3436
3437 /* nsinghi INVCONV Start */
3438 /* Previously the logic to insert onhand information was split into the following 3 cursors:
3439 1. Insert onhand information for items non lot and non status control. View ic_summ_inv_onhand_v
3440 stores information of onhand and information retrieved from this view.
3441 2. Insert onhand information for items Lot controlled and non status controlled. ic_loct_inv contains
3442 onhand information of items in different lots
3443 3. Insert onhand information for items Lot controlled and status controlled. ic_loct_inv contains
3444 onhand information of items in different lots
3445
3446 The three select statements are replaced by a single select statement. */
3447
3448 v_sql_stmt := ' INSERT into msc_st_supplies ( '
3449 || ' plan_id, '
3450 || ' inventory_item_id, '
3451 || ' organization_id, '
3452 || ' sr_instance_id, '
3453 || ' new_schedule_date, '
3454 || ' new_dock_date, ' /* Confirm if this column is required */
3455 || ' order_type, '
3456 || ' lot_number, '
3457 || ' expiration_date, '
3458 || ' firm_planned_type, '
3459 || ' deleted_flag, '
3460 || ' subinventory_code, '/* Added new column subinventory Code */
3461 || ' new_order_quantity) '
3462 || ' SELECT '
3463 || ' -1, '
3464 || ' mon.inventory_item_id, '
3465 || ' mon.organization_id, '
3466 || ' :pinstance_id, '
3467 || ' NVL(mln.hold_date, :prun_date), ' /* Confirm : should we have hold date here. */
3468 || ' :prun_date, '
3469 || ' 18, ' /* onhand inventory value */
3470 /* Discrete Lot and parent lot are now 80 chars long. Lot_number in msc_st_supplies is 30 chars long.
3471 Hence there could be a problem as the lot number is the pkey in mtl_lot_numbers. */
3472 || ' substrb(DECODE(mln.parent_lot_number, NULL, '', mln.parent_lot_number||:pdelimiter) '
3473 || ' ||mln.lot_number, 1, 30), '
3474 || ' mln.expiration_date, '
3475 || ' 2, '
3476 || ' 2, '
3477 || ' mon.subinventory_code, ' /* Populating subinventory with whse code B1172875 */
3478 || ' INV_CONSIGNED_VALIDATIONS.GET_PLANNING_QUANTITY(2, 1, mon.organization_id, '
3479 || ' NULL, mon.inventory_item_id) '
3480 || ' FROM '
3481 || ' mtl_onhand_net'||pdblink||' mon, '
3482 || ' mtl_lot_numbers'||pdblink||' mln, '
3483 || ' mtl_parameters'||pdblink||' gp ' --invconv :- sowmya changed from gmd_parameters to mtl_parameters
3484 || ' WHERE '
3485 || ' mon.lot_number = mln.lot_number (+) '
3486 || ' AND mon.inventory_item_id = mln.inventory_item_id (+) '
3487 || ' AND mon.organization_id = mln.organization_id (+) '
3488 || ' AND mon.organization_id = gp.organization_id '; --sowmya changed
3489
3490 EXECUTE IMMEDIATE v_sql_stmt USING pinstance_id, prun_date, pdelimiter;
3491
3492
3493 /* Commented out all the code after this */
3494
3495
3496 /* Query to select the production order details where the batch/fpo is pending
3497 the balances from ic_summ for the item/whse that are not lot controlled
3498 are inserted */
3499 /*
3500 v_sql_stmt := 'INSERT into msc_st_supplies ('
3501 || ' plan_id,'
3502 || ' inventory_item_id,'
3503 || ' organization_id,'
3504 || ' sr_instance_id,'
3505 || ' new_schedule_date,'
3506 || ' order_type,'
3507 || ' firm_planned_type,'
3508 || ' deleted_flag,'
3509 || ' subinventory_code,' *//* New change , added subinventory Code column */
3510 /* || ' new_order_quantity)'
3511 || ' SELECT '
3512 || ' -1,'
3513 || ' i.aps_item_id,'
3514 || ' i.organization_id,'
3515 || ' :pinstance_id, '
3516 || ' :prun_date, '
3517 || ' 18,' */ /* onhand inventory value */
3518 /* || ' 2,'
3519 || ' 2,'
3520 || ' s.whse_code,' *//* Populating subinventory with Whse code B1172875 */
3521 /* || ' s.onhand_qty'
3522 || ' FROM '
3523 || ' ic_summ_inv_onhand_v' ||pdblink|| ' s,'
3524 || ' (select distinct aps_item_id, item_id, whse_code, organization_id, '
3525 || ' lot_control from'
3526 || ' gmp_item_aps'||pdblink||') i'
3527 || ' WHERE '
3528 || ' s.item_id = i.item_id '
3529 || ' and s.whse_code = i.whse_code '
3530 || ' and i.lot_control = 0'
3531 || ' and s.onhand_qty <> 0';
3532
3533 EXECUTE IMMEDIATE v_sql_stmt USING pinstance_id, prun_date;
3534
3535 */
3536 /* Get onhand balances from the location inventory table for lot controlled
3537 items. The lot can not be status controlled, that will be in the next
3538 insert the lot number is the combo of lot and sublot
3539 */
3540 /*
3541 v_sql_stmt := 'INSERT into msc_st_supplies ('
3542 || ' plan_id,'
3543 || ' inventory_item_id,'
3544 || ' organization_id,'
3545 || ' sr_instance_id,'
3546 || ' new_schedule_date,'
3547 || ' order_type,'
3548 || ' lot_number,'
3549 || ' expiration_date,'
3550 || ' firm_planned_type,'
3551 || ' deleted_flag,'
3552 || ' subinventory_code,' *//* Added new column subinventory Code */
3553 /* || ' new_order_quantity)'
3554 || ' SELECT'
3555 || ' -1,'
3556 || ' i.aps_item_id,'
3557 || ' i.organization_id,'
3558 || ' :pinstance_id,'
3559 || ' :prun_date,'
3560 || ' 18,' *//* onhand inventory value */
3561 /* || ' substrb(l.lot_no||DECODE(l.sublot_no, NULL,NULL ,:pdelimiter || '
3562 || ' l.sublot_no),1,30),'
3563 || ' l.expire_date,'
3564 || ' 2,'
3565 || ' 2,'
3566 || ' s.whse_code,' *//* Populating subinventory with whse code B1172875 */
3567 /* || ' s.loct_onhand'
3568 || ' FROM'
3569 || ' ic_loct_inv'||pdblink||' s,'
3570 || ' ic_lots_mst'||pdblink||' l,'
3571 || ' ic_item_mst'||pdblink||' m,'
3572 || ' (select distinct aps_item_id, item_id, whse_code, organization_id, '
3573 || 'lot_control from gmp_item_aps'||pdblink||') i'
3574 || ' WHERE'
3575 || ' s.item_id = i.item_id'
3576 || ' and s.item_id = m.item_id'
3577 || ' and s.whse_code = i.whse_code'
3578 || ' and i.lot_control = 1'
3579 || ' and m.status_ctl = 0'
3580 || ' and s.lot_id = l.lot_id'
3581 || ' and s.lot_id > 0'
3582 || ' and l.delete_mark = 0'
3583 || ' and s.loct_onhand <> 0';
3584
3585 EXECUTE IMMEDIATE v_sql_stmt USING pinstance_id, prun_date, pdelimiter;
3586 */
3587 /* Get the onhand balances for items that are lot and status controlled. The
3588 balances come from the location inventory table but the status must be
3589 nettable on the lots.
3590 B3177516 Rajesh D. Patangya 05-Oct-2003
3591 PPLT Logical change:
3592 If Hold release date is null then
3593 new schedule date = prun_date;
3594 new_dock_date=prun_date;
3595 order type = 18;
3596 Else
3597 new schedule date = hold release date ;
3598 new_dock_date=prun_date;
3599 order type = 8
3600 End if;
3601 */
3602
3603 /* B2623374 -- Rajesh Patangya PORT BUG FOR 2446925 (OM ATP CHECK TO
3604 RECOGNIZE THE ORDER PROCESSING FLAG) */
3605 /*
3606 v_sql_stmt := 'INSERT into msc_st_supplies ('
3607 || ' plan_id,'
3608 || ' inventory_item_id,'
3609 || ' organization_id,'
3610 || ' sr_instance_id,'
3611 || ' new_schedule_date,'
3612 || ' new_dock_date,'
3613 || ' order_type,'
3614 || ' lot_number,'
3615 || ' expiration_date,'
3616 || ' firm_planned_type,'
3617 || ' deleted_flag,'
3618 || ' subinventory_code,' */ /* added new column sub inventory code */
3619 /* || ' new_order_quantity,'
3620 || ' NON_NETTABLE_QTY)' *//* (OM ATP CHECK TO RECOGNIZE THE ORDER PROCESSING FLAG)*/
3621 /* || ' SELECT'
3622 || ' -1,'
3623 || ' i.aps_item_id,'
3624 || ' i.organization_id,'
3625 || ' :pinstance_id,'
3626 || ' DECODE(c.ic_hold_date,NULL,:prun_date,c.ic_hold_date),'
3627 || ' :prun_date,'
3628 || ' DECODE(c.ic_hold_date,NULL,18,8),' *//* onhand inventory value */
3629 /* || ' substrb(l.lot_no||DECODE(l.sublot_no, NULL,NULL ,:pdelimiter || '
3630 || ' l.sublot_no),1,30),'
3631 || ' l.expire_date,'
3632 || ' 2,'
3633 || ' 2,'
3634 || ' s.whse_code,' *//* Populating subinventory code with whse code B1172875 */
3635 /* || ' s.loct_onhand, '
3636 || ' decode(t.order_proc_ind,0,s.loct_onhand,0)'
3637 || ' FROM'
3638 || ' ic_loct_inv'||pdblink||' s,'
3639 || ' ic_lots_mst'||pdblink||' l,'
3640 || ' ic_item_mst'||pdblink||' m,'
3641 || ' (select distinct aps_item_id, item_id, whse_code, organization_id, '
3642 || ' lot_control from gmp_item_aps'||pdblink||') i,'
3643 || ' ic_lots_sts'||pdblink||' t,'
3644 || ' ic_lots_cpg'||pdblink||' c'
3645 || ' WHERE'
3646 || ' s.item_id = i.item_id'
3647 || ' and s.item_id = m.item_id'
3648 || ' and s.whse_code = i.whse_code'
3649 || ' and i.lot_control = 1'
3650 || ' and s.lot_id = l.lot_id'
3651 || ' and s.lot_id > 0'
3652 || ' and l.delete_mark = 0'
3653 || ' and m.status_ctl = 1'
3654 || ' and s.lot_status = t.lot_status'
3655 || ' and t.rejected_ind = 0'
3656 || ' and t.nettable_ind = 1'
3657 || ' and s.loct_onhand <> 0'
3658 || ' and c.item_id (+) = l.item_id'
3659 || ' and c.lot_id (+) = l.lot_id'
3660 || ' and c.ic_hold_date (+) > :run_date' ;
3661
3662 EXECUTE IMMEDIATE v_sql_stmt USING pinstance_id, prun_date, prun_date, pdelimiter,
3663 prun_date;
3664 */
3665 /* nsinghi INVCONV End */
3666
3667 return_status := TRUE;
3668
3669 EXCEPTION
3670 WHEN OTHERS THEN
3671 log_message('Failure occured during the Onhand Balances extract');
3672 log_message(sqlerrm);
3673 return_status := FALSE;
3674
3675 END extract_onhand_balances; /* end extract_onhand_balances */
3676
3677 /***********************************************************************
3678 *
3679 * NAME
3680 * extract_inv_transfer_demands
3681 *
3682 * DESCRIPTION
3683 * This procedure will insert records into the table msc_st_demands
3684 * According to APS team (Sam Tupe < prganesh Shah etc.
3685 * The inventory transfer demand is similar to Internal Sales Order
3686 * demand hence should be added to each of the demand schedule
3687 * The specifics are
3688 * demand_type = 6
3689 * origination_type = 6
3690 * disposition_id = same transfer_id This should match with the
3691 * corresponding transaction_id of the supply created
3692 * by the same transfer
3693 * demand_schedule_name = OPM specific demand_schedule name - The
3694 * MDS names used in forecast/SO extraction
3695 * HISTORY
3696 * 25-Jan-2003 B2756431
3697 * Note : Old procedure extract_inv_transfers is now removed
3698 * and replaced with these two new procedures
3699 ************************************************************************/
3700 PROCEDURE extract_inv_transfer_demands(
3701 pdblink IN VARCHAR2,
3702 pinstance_id IN PLS_INTEGER,
3703 prun_date IN DATE,
3704 pdelimiter IN VARCHAR2,
3705 pwhse_code IN VARCHAR2,
3706 pdesignator IN VARCHAR2,
3707 return_status IN OUT NOCOPY BOOLEAN)
3708 AS
3709
3710 pdoc_type VARCHAR2(4) ;
3711
3712 BEGIN
3713 pdoc_type := 'XFER';
3714
3715 return_status := TRUE ;
3716
3717 v_sql_stmt := 'INSERT into msc_st_demands ('
3718 || ' organization_id,'
3719 || ' inventory_item_id,'
3720 || ' sr_instance_id,'
3721 || ' using_assembly_item_id,'
3722 || ' using_assembly_demand_date,'
3723 || ' using_requirement_quantity,'
3724 || ' demand_type,'
3725 || ' origination_type,'
3726 || ' order_number,'
3727 || ' demand_schedule_name,'
3728 || ' disposition_id,' /* B2756431 */
3729 || ' demand_source_type,' /* B2756431 */
3730 || ' original_system_reference,' /* B2756431 */
3731 || ' original_system_line_reference,' /* being added for B2756431 */
3732 || ' deleted_flag)'
3733 || ' SELECT '
3734 || ' i.organization_id,'
3735 || ' i.aps_item_id,'
3736 || ' :pinstance_id, '
3737 || ' i.aps_item_id,'
3738 || ' s.scheduled_release_date,'
3739 || ' s.release_quantity1,'
3740 || ' 1,' /* Discrete , other demands types are interpreted as continuous */
3741 || ' 6,' /* Orig_type should br 6 per Sam Tupe so change from 11 */
3742 || ' :pdoc_type || :pdelimiter || s.orgn_code ||'
3743 || ' :pdelimiter2 || s.transfer_no, '
3744 || ' :pdesignator,'
3745 || ' s.transfer_id,'
3746 || ' 8,' /* B2756431 Demand_source_type */
3747 || ' s.transfer_id,' /* B2756431 original_system_reference */
3748 || ' s.transfer_id,' /* B2756431 original_system_line_reference */
3749 || ' 2'
3750 || ' FROM '
3751 || ' ic_xfer_mst' ||pdblink|| ' s,'
3752 || ' (select distinct aps_item_id, item_id, whse_code, organization_id '
3753 || ' from gmp_item_aps'||pdblink||') i'
3754 || ' WHERE '
3755 || ' s.item_id = i.item_id '
3756 || ' and s.from_warehouse = i.whse_code '
3757 || ' and s.transfer_status IN (1) '
3758 || ' and s.from_warehouse = :pwhse_code '
3759 || ' and s.release_quantity1 <> 0';
3760
3761 EXECUTE IMMEDIATE v_sql_stmt USING pinstance_id, pdoc_type, pdelimiter, pdelimiter , pdesignator, pwhse_code ;
3762
3763 EXCEPTION
3764 WHEN OTHERS THEN
3765 log_message('Failure occured during the Inventory Transfer extract');
3766 log_message(sqlerrm);
3767 return_status := FALSE;
3768
3769 END extract_inv_transfer_demands;/* end extract_inv_transfer_dem */
3770
3771 /***********************************************************************
3772 *
3773 * NAME
3774 * Extract_inventory_transfer_supplies
3775 *
3776 * DESCRIPTION
3777 * This procedure will insert records into the table msc_st_supplies
3778 * and msc_st_demands for pending inventory transfers.
3779 * HISTORY
3780 * 25-Jan-2003 B1332662 Created New procedure to insert supplies
3781 * Per discussions with APS team the specifics are
3782 * Order_type = 2
3783 * Transaction_id = transafer_id of the transfer in OPM
3784 ************************************************************************/
3785 PROCEDURE extract_inv_transfer_supplies(
3786 pdblink IN VARCHAR2,
3787 pinstance_id IN PLS_INTEGER,
3788 prun_date IN DATE,
3789 pdelimiter IN VARCHAR2,
3790 return_status IN OUT NOCOPY BOOLEAN)
3791 AS
3792
3793 pdoc_type VARCHAR2(4) ;
3794
3795 BEGIN
3796 pdoc_type := 'XFER';
3797
3798 return_status := TRUE ;
3799
3800 v_sql_stmt := 'INSERT into msc_st_supplies ('
3801 || ' plan_id,'
3802 || ' inventory_item_id,'
3803 || ' organization_id,'
3804 || ' sr_instance_id,'
3805 || ' source_sr_instance_id,'
3806 || ' new_schedule_date,'
3807 || ' order_type,'
3808 || ' order_number,'
3809 || ' lot_number,'
3810 || ' firm_planned_type,'
3811 || ' deleted_flag,'
3812 || ' subinventory_code,'
3813 || ' transaction_id,' /* being added for B2756431 */
3814 || ' disposition_id,' /* being added for B2756431 */
3815 || ' po_line_id,' /* being added for B2756431 */
3816 || ' source_organization_id,' /* being added for B2756431 */
3817 || ' new_order_quantity)'
3818 || ' SELECT '
3819 || ' -1,'
3820 || ' i.aps_item_id,'
3821 || ' i.organization_id,'
3822 || ' :pinstance_id, '
3823 || ' :pinstance_id, '
3824 || ' s.scheduled_receive_date, '
3825 || ' 2,' /* po requisition value */
3826 || ' :pdoc_type || :pdelimiter || s.orgn_code ||'
3827 || ' :pdelimiter2 || s.transfer_no, '
3828 || ' DECODE(s.lot_id, 0, NULL, '
3829 || ' substrb(l.lot_no||DECODE(l.sublot_no, NULL,NULL ,:pdelimiter3 || '
3830 || ' l.sublot_no),1,30)),'
3831 || ' 2,'
3832 || ' 2,'
3833 || ' s.to_warehouse,'
3834 || ' s.transfer_id,' /* B2756431 transaction_id */
3835 || ' s.transfer_id,' /* B2756431 disposition_id */
3836 || ' s.transfer_id,' /* B2756431 po_line_id */
3837 || ' w.mtl_organization_id,' /* B2756431 source_organization_id */
3838 || ' s.release_quantity1'
3839 || ' FROM '
3840 || ' ic_xfer_mst' ||pdblink|| ' s,'
3841 || ' ic_whse_mst' ||pdblink|| ' w,'
3842 || ' ic_lots_mst'||pdblink||' l,'
3843 || ' (select distinct aps_item_id, item_id, whse_code, organization_id '
3844 || ' from gmp_item_aps'||pdblink||') i'
3845 || ' WHERE '
3846 || ' s.item_id = i.item_id '
3847 || ' and s.to_warehouse = i.whse_code '
3848 || ' and s.from_warehouse = w.whse_code '
3849 || ' and s.transfer_status IN (1,2) '
3850 || ' and s.lot_id = l.lot_id'
3851 || ' and s.item_id = l.item_id'
3852 || ' and s.release_quantity1 <> 0';
3853
3854
3855 EXECUTE IMMEDIATE v_sql_stmt USING pinstance_id,pinstance_id, pdoc_type, pdelimiter,
3856 pdelimiter, pdelimiter;
3857
3858 EXCEPTION
3859 WHEN OTHERS THEN
3860 log_message('Failure occured during the Inventory Transfer supplies ');
3861 log_message(sqlerrm);
3862 return_status := FALSE;
3863
3864 END extract_inv_transfer_supplies;/* end extract_inv_transfer_sup */
3865
3866
3867 /***********************************************************************
3868 *
3869 * NAME
3870 * build_designator
3871 *
3872 * DESCRIPTION
3873 * This procedure will create a new row in the pl/sql table if one does
3874 * for the current schedule/whse. The rows will be inserted into the
3875 * database in the procedure sales_forecast which calls this procedure.
3876 * A unique designator must be created for each schedule/whse otherwise a
3877 * number is added to make it unique. If the row exists already the value
3878 * is returned otherwise the table is added to and the new value is returned
3879 * in the out parameter
3880 * HISTORY
3881 * M Craig
3882 ************************************************************************/
3883 PROCEDURE build_designator(
3884 poccur IN NUMBER,
3885 pdelimiter IN VARCHAR2,
3886 pdesignator OUT NOCOPY VARCHAR2)
3887 AS
3888
3889 temp_designator VARCHAR2(10);
3890 i NUMBER;
3891 j NUMBER;
3892 k NUMBER;
3893 found PLS_INTEGER;
3894 j_char VARCHAR2(5);
3895
3896 BEGIN
3897 found := 0 ;
3898 /* The default name generation is the first 5 chars of the schedule and the
3899 four chars of the warehouse
3900 */
3901 temp_designator := substrb(sched_dtl_tab(poccur).schedule,1,5) || pdelimiter
3902 || sched_dtl_tab(poccur).whse_code;
3903
3904 pdesignator := NULL;
3905 found := 0;
3906
3907 /* if there are existing rows search them for the key values */
3908 IF desig_count > 0 THEN
3909 /* {
3910 loop through the existing designator rows */
3911 FOR i IN 1..desig_count LOOP
3912 /* {
3913
3914 if a row has alreday been inserted for the schedule and warehouse
3915 use the value from that row and stop the loop
3916 */
3917 IF desig_tab(i).schedule = sched_dtl_tab(poccur).schedule and
3918 desig_tab(i).whse_code = sched_dtl_tab(poccur).whse_code THEN
3919
3920 pdesignator := desig_tab(i).designator;
3921 found := 1;
3922 EXIT;
3923
3924 END IF;
3925
3926 /* } */
3927 END LOOP;
3928
3929 /* when the schedule and warehouse are not represented we need to find
3930 a unique name for the designator
3931 */
3932 IF found = 0 THEN
3933 /* { */
3934
3935 k := 5;
3936 j := 0;
3937 j_char := NULL;
3938
3939 /* the loop will try the default value then change it if necessary and
3940 until we have exhasted all of the values of 0-99999 (5 chars of numbers)
3941 */
3942 LOOP
3943 /* { */
3944 temp_designator := j_char || SUBSTR(sched_dtl_tab(poccur).schedule,1,k) ||
3945 pdelimiter || sched_dtl_tab(poccur).whse_code;
3946 /* this loop goes through the current list to see if there is a duplicate
3947 if found we stop and generate a new value then try again
3948 */
3949 FOR i IN 1..desig_count LOOP
3950 /* { */
3951 IF desig_tab(i).designator = temp_designator THEN
3952 EXIT;
3953 END IF;
3954 IF i = desig_count THEN
3955 found := 1;
3956 pdesignator := temp_designator;
3957 END IF;
3958 /* } */
3959 END LOOP;
3960
3961 /* if we found a value or reached the max we stop */
3962 IF found = 1 or j = 99999 THEN
3963 EXIT;
3964 END IF;
3965
3966 /* to get a unique value we keep taking one char at a time from the
3967 the schedule leaving the warehouse intact.
3968 */
3969 j := j + 1;
3970 IF j < 10 THEN
3971 k := 4;
3972 ELSIF j < 100 THEN
3973 k := 3;
3974 ELSIF j < 1000 THEN
3975 k := 2;
3976 ELSIF j < 10000 THEN
3977 k := 1;
3978 ELSE
3979 k := 0;
3980 END IF;
3981
3982 j_char := TO_CHAR(j);
3983
3984 /* } */
3985 END LOOP;
3986
3987
3988 /* put a new row in for the value that was found */
3989 IF found = 1 and pdesignator = temp_designator THEN
3990
3991 desig_count := desig_count + 1;
3992 desig_tab(desig_count).designator := temp_designator;
3993 desig_tab(desig_count).schedule := sched_dtl_tab(poccur).schedule;
3994 desig_tab(desig_count).orgn_code := sched_dtl_tab(poccur).orgn_code;
3995 desig_tab(desig_count).whse_code := sched_dtl_tab(poccur).whse_code;
3996 desig_tab(desig_count).organization_id :=
3997 sched_dtl_tab(poccur).organization_id;
3998
3999 END IF;
4000
4001 /* } */
4002 END IF;
4003
4004 /* if no rows are in the table yet just put a new one in */
4005 ELSE
4006
4007 desig_tab(1).designator := temp_designator;
4008 desig_tab(1).schedule := sched_dtl_tab(poccur).schedule;
4009 desig_tab(1).orgn_code := sched_dtl_tab(poccur).orgn_code;
4010 desig_tab(1).whse_code := sched_dtl_tab(poccur).whse_code;
4011 desig_tab(1).organization_id := sched_dtl_tab(poccur).organization_id;
4012 pdesignator := temp_designator;
4013 desig_count := 1;
4014
4015 /* } */
4016 END IF;
4017
4018 END build_designator;
4019
4020 /***********************************************************************
4021 *
4022 * NAME
4023 * sales_forecast_api
4024 *
4025 * DESCRIPTION
4026 * This procedure is a wrapper for the preexisting sales_forecast procedure.
4027 * This version is set up with the proper parameters to be called as from the
4028 * concurrent manager. In addition, the main difference is the table into
4029 * which demands are inserted. The standard procedure inserts into
4030 * msc_st_demands.
4031 * This new procedure inserts into gmp_demands_api. The difference between
4032 * the two tables is the addition of a schedule_id column in
4033 * gmp_demands_api. Also, this version of sales_forecast begins by
4034 * truncating gmp_demands_api and leaves it populated after
4035 * it completes. By contrast, msc_st_demands (which is an APS staging table)
4036 * is immediately truncated after APS reads its data. This difference allows
4037 * gmp_demands_api to be a general purpose version of msc_st_demands.
4038 *
4039 * HISTORY
4040 * P. Dong
4041 * 09/14/01 - Created
4042 * 12/21/01 - Replaced TRUNCATE with DELETE
4043 ************************************************************************/
4044 PROCEDURE sales_forecast_api(
4045 errbuf OUT NOCOPY VARCHAR2,
4046 retcode OUT NOCOPY VARCHAR2,
4047 p_cp_enabled IN BOOLEAN ,
4048 p_run_date IN DATE )
4049 AS
4050 lv_cp_enabled BOOLEAN;
4051 BEGIN
4052
4053 lv_cp_enabled := p_cp_enabled;
4054
4055 gmp_bom_routing_pkg.extract_items(
4056 at_apps_link => NULL,
4057 instance => NULL,
4058 run_date => p_run_date,
4059 return_status => lv_cp_enabled );
4060
4061 DELETE FROM gmp_demands_api;
4062
4063 lv_cp_enabled := p_cp_enabled;
4064
4065 sales_forecast(
4066 pdblink => NULL,
4067 pinstance_id => NULL,
4068 prun_date => p_run_date,
4069 pdelimiter => '/',
4070 return_status => lv_cp_enabled,
4071 api_mode => TRUE);
4072
4073 errbuf := NULL;
4074 retcode := NULL;
4075
4076 EXCEPTION
4077 WHEN OTHERS THEN
4078 errbuf := SUBSTRB(SQLERRM,1,100);
4079 retcode := SQLCODE;
4080
4081 END sales_forecast_api;
4082
4083 /***********************************************************************
4084 *
4085 * NAME
4086 * sales_forecast
4087 *
4088 * DESCRIPTION
4089 * This procedure will retrieve all of the sales order lines and forecast
4090 * details for their respective schedules. The forecast will be consumed
4091 * and the all of the rows will be written to msc_st_demands. Each demand
4092 * is applied to an MDS aka designator.
4093 * HISTORY
4094 * M Craig
4095 * 10/13/99 - Sridhar Added Designator Type column in the insert statement
4096 * 12/17/99 - Changes made to the insert statement for designators,
4097 * changed desig_tab(1).schedule and desig_tab(1).whse_code to
4098 * desig_tab(i).schedule and desig_tab(i).whse_code
4099 * 04/01/00 - Code Fix for Bug# 1137597.
4100 * 07/01/00 - Code Fix for Error in Designators Insert
4101 *
4102 * 02-MAY-2002 Re-engineered By : Abhay Satpute, Rajesh Patangya
4103 * Brief Logic of the new code
4104 * Fetch the following data into PL/SQL tables
4105 * a. Distinct schd/item/whse combinations
4106 * b. Sales order details
4107 * c. Forecast details
4108 * d. Schedule forecast associations
4109 * For each item combination loop through and
4110 * For each change of schedule change mark reuqired
4111 * forecast rows as well note down the stock and ord ind.
4112 * For each item insert sales orders, unconsumed forecast
4113 * or the forecast , based on the indicators
4114 * P Dong
4115 * 09/14/01 - Added api_mode to pass to insert_demands
4116 *
4117 * Navin 21-APR-2003 B3577871 ST:OSFME2: collections failing in planning data pull.
4118 * Added handling of NO_DATA_FOUND Exception.
4119 * And return the return_status as TRUE.
4120 ****************************************************************************/
4121
4122 PROCEDURE sales_forecast( pdblink IN VARCHAR2,
4123 pinstance_id IN PLS_INTEGER,
4124 prun_date IN DATE,
4125 pdelimiter IN VARCHAR2,
4126 return_status IN OUT NOCOPY BOOLEAN,
4127 api_mode IN BOOLEAN)
4128
4129 AS
4130
4131 TYPE gmp_cursor_typ IS REF CURSOR;
4132 cur_gmp_schd_items gmp_cursor_typ;
4133 cur_fcst_dtl gmp_cursor_typ;
4134 cur_sales_dtl gmp_cursor_typ;
4135 cur_schd_fcst gmp_cursor_typ;
4136
4137 so_ind BOOLEAN ;
4138 fcst_ind BOOLEAN ;
4139 log_mesg VARCHAR2(100) ;
4140 i NUMBER ;
4141 j NUMBER ;
4142 old_schedule_id NUMBER ;
4143 item_count NUMBER ;
4144 fcst_count NUMBER ;
4145 so_count NUMBER ;
4146 schd_fcst_cnt NUMBER ;
4147 local_ret_status BOOLEAN ;
4148
4149 BEGIN
4150 g_delimiter := '/';
4151 so_ind := FALSE ;
4152 fcst_ind := FALSE ;
4153 log_mesg := NULL;
4154 i := 0;
4155 j := 0;
4156 old_schedule_id := 0 ;
4157 item_count := 1;
4158 fcst_count := 1;
4159 so_count := 1;
4160 schd_fcst_cnt := 1;
4161
4162 gitem_size := 0;
4163 gfcst_size := 0;
4164 gso_size := 0;
4165 gschd_fcst_size := 0;
4166
4167 gfcst_cnt := 0;
4168 gso_cnt := 0;
4169 gschd_fcst_cnt := 0;
4170 g_item_tbl_position := 0;
4171 local_ret_status := return_status ;
4172
4173 log_message('Start gmp_aps_ds_pull.sales forecast');
4174 time_stamp;
4175
4176 IF return_status THEN
4177 v_cp_enabled := TRUE;
4178 ELSE
4179 v_cp_enabled := FALSE;
4180 END IF;
4181 g_delimiter := pdelimiter ;
4182 g_instance_id := pinstance_id ;
4183
4184 IF api_mode THEN
4185 /* If forecast and sales order select queries have joins with gmp_item_aps
4186 we need to select only schedules and warehouses here
4187 ORDERED By Schedule , Aps_Item, Organization_id(Warehouse) */
4188
4189 /* Extract Schedule Details */
4190 v_item_sql_stmt := 'SELECT DISTINCT'
4191 || ' h.schedule,'
4192 || ' h.schedule_id,'
4193 || ' h.order_ind,'
4194 || ' h.stock_ind,'
4195 || ' a.whse_code,'
4196 || ' d.orgn_code,'
4197 || ' a.organization_id, '
4198 || ' a.aps_item_id inventory_item_id'
4199 || ' FROM'
4200 || ' ps_schd_hdr'||pdblink||' h,'
4201 || ' ps_schd_dtl'||pdblink||' d,'
4202 || ' gmp_item_aps'||pdblink||' a'
4203 || ' WHERE'
4204 || ' h.schedule_id = d.schedule_id'
4205 || ' and d.orgn_code = a.plant_code'
4206 || ' and h.active_ind = 1'
4207 || ' and a.replen_ind = 1'
4208 || ' and (h.order_ind = 1 or h.stock_ind = 1)'
4209 || ' and h.delete_mark = 0'
4210 || ' and a.item_id > 0 '
4211 || ' ORDER BY'
4212 || ' h.schedule_id ASC,'
4213 || ' a.aps_item_id, '
4214 || ' a.organization_id ' ;
4215
4216 -- B2596464, Order changed for inv_item and organization_id
4217 -- B2973249, undershipped or overshipped sales orders have shipped_qty
4218 -- populated by OM and as per APS this lines can not be selected, as OM
4219 -- split original line and keep the open line without any shipped qty.
4220 /* Extract Sales Order */
4221 v_sales_sql_stmt := 'SELECT '
4222 || ' msi.inventory_item_id, '
4223 || ' msi.organization_id, '
4224 || ' h.orgn_code, '
4225 || ' h.order_no, '
4226 || ' d.line_id, '
4227 || ' d.net_price, '
4228 || ' d.sched_shipdate, '
4229 || ' d.requested_shipdate, ' /* B2971996 */
4230 || ' (sum(t.trans_qty) * -1) trans_qty '
4231 || ' FROM '
4232 || ' mtl_system_items'||pdblink||' msi, '
4233 || ' ic_item_mst'||pdblink||' iim,'
4234 || ' ic_whse_mst'||pdblink||' wm, '
4235 || ' op_ordr_hdr'||pdblink||' h, '
4236 || ' op_ordr_dtl'||pdblink||' d, '
4237 || ' ic_tran_pnd'||pdblink||' t '
4238 || ' WHERE '
4239 || ' msi.organization_id = wm.mtl_organization_id '
4240 || ' AND msi.segment1 = iim.item_no '
4241 || ' and wm.delete_mark = 0 '
4242 || ' and h.order_id = d.order_id '
4243 || ' and h.order_status = 0 '
4244 || ' and h.delete_mark = 0 '
4245 || ' and h.order_id = t.doc_id '
4246 || ' and d.line_status >= 0 '
4247 || ' and d.line_status < 20 '
4248 || ' and h.from_whse = wm.whse_code '
4249 || ' and t.line_id = d.line_id '
4250 || ' and t.item_id = d.item_id '
4251 || ' and iim.item_id = t.item_id '
4252 || ' and iim.delete_mark = 0 '
4253 || ' AND iim.inactive_ind = 0 '
4254 || ' and t.trans_qty <> 0 '
4255 || ' and t.completed_ind = 0 '
4256 || ' and t.delete_mark = 0 '
4257 || ' and t.doc_type = :popso '
4258 || ' GROUP BY '
4259 || ' msi.inventory_item_id, '
4260 || ' msi.organization_id, '
4261 || ' h.orgn_code, '
4262 || ' h.order_no, '
4263 || ' d.line_id, '
4264 || ' d.net_price, '
4265 || ' d.sched_shipdate, '
4266 || ' d.requested_shipdate ' /* B2971996 */
4267 || ' UNION ALL '
4268 || ' SELECT '
4269 || ' items.inventory_item_id, '
4270 || ' items.organization_id, '
4271 || ' org.organization_code, '
4272 || ' TO_CHAR(hdr.order_number), '
4273 || ' TO_NUMBER(NULL), '
4274 || ' TO_NUMBER(NULL), '
4275 || ' mtl.requirement_date, '
4276 || ' dtl.request_date, ' /* B2971996 */
4277 || ' mtl.primary_uom_quantity '
4278 || ' FROM '
4279 || ' mtl_demand_omoe'||pdblink||' mtl, '
4280 || ' mtl_system_items'||pdblink||' items, '
4281 || ' oe_order_headers_all'||pdblink||' hdr, '
4282 || ' oe_order_lines_all'||pdblink||' dtl, '
4283 || ' mtl_parameters'||pdblink||' org '
4284 || ' WHERE '
4285 || ' items.organization_id = mtl.organization_id '
4286 || ' and items.inventory_item_id = mtl.inventory_item_id '
4287 || ' and NVL(mtl.completed_quantity,0) = 0 '
4288 || ' and mtl.open_flag = ' || '''Y'''
4289 || ' and mtl.available_to_mrp = 1 '
4290 || ' and mtl.parent_demand_id is NULL '
4291 || ' and mtl.demand_source_type IN (2,8) '
4292 || ' and mtl.demand_id = dtl.line_id '
4293 || ' and dtl.header_id = hdr.header_id '
4294 -- B2743626, Changed the join to take process sales order (OMSO)
4295 || ' and dtl.ship_from_org_id = org.organization_id '
4296 || ' and org.process_enabled_flag = ' || '''Y'''
4297 || ' and NOT EXISTS '
4298 || ' (SELECT 1 '
4299 || ' FROM so_lines_all'||pdblink||' sl,'
4300 || ' so_lines_all'||pdblink||' slp,'
4301 || ' mtl_demand_omoe'||pdblink||' dem'
4302 || ' WHERE '
4303 || ' slp.line_id(+) = nvl(sl.parent_line_id,sl.line_id) '
4304 || ' and to_number(dem.demand_source_line) = sl.line_id(+) '
4305 || ' and dem.demand_source_type in (2,8) '
4306 || ' and sl.end_item_unit_number IS NULL '
4307 || ' and slp.end_item_unit_number IS NULL '
4308 || ' and dem.demand_id = mtl.demand_id '
4309 || ' and items.effectivity_control = 2) '
4310 || ' ORDER BY 1,2,7 DESC ' ;
4311
4312 /* Extract Forecast details */
4313 v_forecast_sql_stmt := 'SELECT '
4314 || ' msi.inventory_item_id, '
4315 || ' msi.organization_id, '
4316 || ' h.forecast_id, '
4317 || ' h.forecast, '
4318 || ' d.orgn_code, '
4319 || ' d.trans_date, '
4320 || ' (sum(d.trans_qty * -1) ) trans_qty, '
4321 || ' (sum(d.trans_qty * -1) ) consumed_qty ,'
4322 || ' 0 use_fcst_flag '
4323 || ' FROM '
4324 || ' mtl_system_items'||pdblink||' msi, '
4325 || ' ic_item_mst'||pdblink||' iim, '
4326 || ' ic_whse_mst'||pdblink||' wm, '
4327 || ' fc_fcst_hdr'||pdblink||' h, '
4328 || ' fc_fcst_dtl'||pdblink||' d '
4329 || ' WHERE '
4330 || ' msi.organization_id = wm.mtl_organization_id '
4331 || ' and msi.segment1 = iim.item_no '
4332 || ' and wm.delete_mark = 0 '
4333 || ' and h.forecast_id = d.forecast_id '
4334 || ' and d.forecast_id > 0 '
4335 || ' and d.item_id = iim.item_id '
4336 || ' and d.whse_code = wm.whse_code '
4337 || ' and d.orgn_code = wm.orgn_code '
4338 || ' and h.delete_mark = 0 '
4339 || ' and d.delete_mark = 0 '
4340 || ' and d.trans_qty <> 0 '
4341 || ' and d.trans_date >= sysdate '
4342 || ' and EXISTS (SELECT 1 FROM '
4343 || ' ps_schd_for'||pdblink||' sf, '
4344 || ' ps_schd_hdr'||pdblink||' sh '
4345 || ' WHERE sh.schedule_id = sf.schedule_id '
4346 || ' and sh.delete_mark = 0 '
4347 || ' and sh.active_ind = 1 '
4348 || ' and sf.forecast_id = h.forecast_id) '
4349 || ' GROUP BY '
4350 || ' msi.inventory_item_id, '
4351 || ' msi.organization_id, '
4352 || ' h.forecast, '
4353 || ' h.forecast_id, '
4354 || ' d.orgn_code, '
4355 || ' d.trans_date '
4356 || ' ORDER BY msi.inventory_item_id,msi.organization_id, '
4357 || ' d.trans_date DESC ' ;
4358
4359 /* Extract Schedule Forecast Association SQL selection */
4360 v_association_sql_stmt := 'SELECT '
4361 || ' schedule_id, forecast_id '
4362 || ' from ps_schd_for'||pdblink
4363 || ' ORDER BY 1,2 ' ;
4364
4365 /* Start Fetching the schedule, forecast, sales order and association
4366 data for above queries */
4367
4368 OPEN cur_gmp_schd_items FOR v_item_sql_stmt;
4369 LOOP
4370 FETCH cur_gmp_schd_items INTO sched_dtl_tab(item_count);
4371 EXIT WHEN cur_gmp_schd_items%NOTFOUND;
4372 item_count := item_count + 1;
4373 END LOOP;
4374 CLOSE cur_gmp_schd_items;
4375 gitem_size := item_count -1 ;
4376 time_stamp ;
4377 log_message('Schedule Items size is = ' || to_char(gitem_size)) ;
4378
4379 OPEN cur_fcst_dtl FOR v_forecast_sql_stmt;
4380 LOOP
4381 FETCH cur_fcst_dtl INTO fcst_dtl_tab(fcst_count);
4382 EXIT WHEN cur_fcst_dtl%NOTFOUND;
4383 fcst_count := fcst_count + 1;
4384 END LOOP;
4385 CLOSE cur_fcst_dtl ;
4386 gfcst_size := fcst_count -1 ;
4387 time_stamp ;
4388 log_message('Fcst size is = '|| to_char(gfcst_size) );
4389
4390 OPEN cur_sales_dtl FOR v_sales_sql_stmt USING v_doc_opso;
4391 LOOP
4392 FETCH cur_sales_dtl INTO sales_dtl_tab(so_count);
4393 EXIT WHEN cur_sales_dtl%NOTFOUND;
4394 so_count := so_count + 1;
4395 END LOOP;
4396 CLOSE cur_sales_dtl ;
4397 gso_size := so_count -1 ;
4398 time_stamp ;
4399 log_message ('SO size is = '||to_char(gso_size));
4400
4401 OPEN cur_schd_fcst FOR v_association_sql_stmt;
4402 LOOP
4403 FETCH cur_schd_fcst INTO SCHD_FCST_DTL_TAB(schd_fcst_cnt);
4404 EXIT WHEN cur_schd_fcst%NOTFOUND;
4405 schd_fcst_cnt := schd_fcst_cnt + 1;
4406 END LOOP;
4407 CLOSE cur_schd_fcst ;
4408 gschd_fcst_size := schd_fcst_cnt -1 ;
4409 time_stamp ;
4410 log_message('Schedule Forecast Assoc size is ='||to_char(gschd_fcst_size));
4411
4412 gschd_fcst_cnt := 1;
4413 so_ind := FALSE ;
4414 fcst_ind := FALSE ;
4415
4416 FOR i IN 1..gitem_size LOOP
4417 g_item_tbl_position := i ;
4418 IF old_schedule_id <> sched_dtl_tab(i).schedule_id THEN
4419 -- Keep commiting the data to avoid Rollback segment growing problem
4420 COMMIT ;
4421 time_stamp ;
4422 gfcst_cnt := 1 ;
4423 gso_cnt := 1 ;
4424 so_ind := FALSE ;
4425 fcst_ind := FALSE ;
4426
4427 IF sched_dtl_tab(i).order_ind = 1 THEN
4428 so_ind := TRUE ;
4429 END IF;
4430 IF sched_dtl_tab(i).stock_ind = 1 THEN
4431 fcst_ind := TRUE ;
4432 END IF;
4433
4434 /* If there is no forecast associated to current schedule
4435 then set FCST_IND = FALSE */
4436 IF sched_dtl_tab(i).stock_ind = 1 AND
4437 NOT (associate_forecasts(gschd_fcst_cnt,sched_dtl_tab(i).schedule_id))
4438 THEN
4439 fcst_ind := FALSE;
4440 /* Note that we are not Dis-associating the forecasts detail
4441 rows when stock_ind is turned OFF. Make sure that the
4442 forecast table is not used at all in such cases */
4443 END IF ; /* Stock Indicator */
4444
4445 old_schedule_id := sched_dtl_tab(i).schedule_id ;
4446 END IF; /* Schedule ID match */
4447
4448 -- If both stock_ind and order_ind are 0 , we should simply continue to
4449 -- the next record , the easiest method may be <<goto>>
4450
4451 IF (fcst_ind) THEN
4452 IF (so_ind) THEN
4453 consume_forecast(sched_dtl_tab(i).inventory_item_id,
4454 sched_dtl_tab(i).organization_id,api_mode) ;
4455 ELSE
4456 write_forecast(gfcst_cnt,sched_dtl_tab(i).inventory_item_id,
4457 sched_dtl_tab(i).organization_id,api_mode ) ;
4458 END IF;
4459 ELSE
4460 IF (so_ind) THEN
4461 write_so(gso_cnt,sched_dtl_tab(i).inventory_item_id,
4462 sched_dtl_tab(i).organization_id,api_mode ) ;
4463 END IF;
4464 END IF;
4465
4466 END LOOP ; /* Main Loop for Schedule, item, Warehouse */
4467
4468 /* Bug 2756431 Moved the call to this function here per thisbug */
4469 /* the transfer demands and supplies need to be put under EACH of the
4470 demand schedules - Note that the supplies should NOT be replicated */
4471 FOR i IN 1..desig_tab.COUNT LOOP
4472 extract_inv_transfer_demands(pdblink, pinstance_id, prun_date,
4473 pdelimiter, desig_tab(i).whse_code,desig_tab(i).designator,
4474 local_ret_status);
4475 END LOOP ;
4476
4477 return_status := local_ret_status ;
4478
4479 Insert_Designator;
4480
4481 log_message('End of gmp_aps_ds_pull.sales forecast') ;
4482 time_stamp ;
4483 return_status := TRUE;
4484 ELSE
4485 extract_forecasts( pdblink ,
4486 pinstance_id ,
4487 prun_date ,
4488 pdelimiter ,
4489 return_status );
4490
4491 END IF ; -- if NOT api_mode
4492 EXCEPTION
4493 WHEN NO_DATA_FOUND THEN /* B3577871 */
4494 log_message(' NO_DATA_FOUND exception raised in Procedure: Gmp_aps_ds_pull.Sales_forecast ' );
4495 return_status := TRUE;
4496
4497 WHEN OTHERS THEN
4498 log_message('Failure occured during the Sales_Forecast extract');
4499 log_message(sqlerrm);
4500 return_status := FALSE;
4501 END sales_forecast;
4502
4503 /************************************************************************
4504 * NAME
4505 * extract_forecasts
4506 *
4507 * DESCRIPTION
4508 *
4509 *
4510 *
4511 * HISTORY
4512 * Created By : Abhay Satpute
4513 * 24-Oct-2003 Chnaged origincation_type to 29
4514 ************************************************************************/
4515 PROCEDURE extract_forecasts ( pdblink IN VARCHAR2,
4516 pinstance_id IN PLS_INTEGER,
4517 prun_date IN DATE,
4518 pdelimiter IN VARCHAR2,
4519 return_status IN OUT NOCOPY BOOLEAN)
4520 IS
4521
4522 TYPE gmp_cursor_typ IS REF CURSOR;
4523 fcst_hdr gmp_cursor_typ;
4524 cur_fcst_dtl gmp_cursor_typ;
4525
4526 TYPE fcst_hdr_rec IS RECORD (
4527 fcst_id PLS_INTEGER,
4528 orig_forecast VARCHAR2(16),
4529 fcst_name VARCHAR2(10),
4530 fcst_set VARCHAR2(10),
4531 desgn_ind PLS_INTEGER,
4532 consumption_ind NUMBER,
4533 backward_time_fence NUMBER,
4534 forward_time_fence NUMBER
4535 );
4536 TYPE fcst_dtl_rec_typ IS RECORD
4537 (
4538 inventory_item_id PLS_INTEGER,
4539 organization_id PLS_INTEGER,
4540 forecast_id PLS_INTEGER,
4541 line_id PLS_INTEGER,
4542 forecast VARCHAR2(16),
4543 forecast_set VARCHAR2(10),
4544 trans_date DATE,
4545 orgn_code VARCHAR2(4),
4546 trans_qty NUMBER,
4547 use_fcst_flag NUMBER
4548 );
4549 fcst_dtl_rec fcst_dtl_rec_typ ;
4550
4551 TYPE fcst_hdr_tab_typ IS TABLE OF fcst_hdr_rec
4552 INDEX BY BINARY_INTEGER ;
4553
4554 fcst_hdr_tbl fcst_hdr_tab_typ ;
4555
4556 cnt PLS_INTEGER := 0 ;
4557 l_cnt PLS_INTEGER := 1 ;
4558 curr_cnt PLS_INTEGER := 0 ;
4559 temp_name VARCHAR2(10) := NULL ;
4560 i PLS_INTEGER := 1 ;
4561 j PLS_INTEGER := 10 ;
4562 k PLS_INTEGER := 0;
4563 x PLS_INTEGER := 1;
4564 duplicate_found BOOLEAN := FALSE ;
4565 prev_org_id PLS_INTEGER := 0 ;
4566 prev_fcst_id PLS_INTEGER := 0 ;
4567 prev_fcst_set VARCHAR2(10);
4568 prev_fcst VARCHAR2(10);
4569 write_fcst BOOLEAN ;
4570 write_fcst_set BOOLEAN ;
4571 fcst_locn PLS_INTEGER ;
4572
4573 l_design_stmt VARCHAR2(2000) ;
4574 l_fcst_stmt VARCHAR2(2000) ;
4575 l_demands_stmt VARCHAR2(2000) ;
4576 l_insert_set_stmt VARCHAR2(2000);
4577
4578 BEGIN
4579
4580 d_index := 0 ;
4581 i_index := 0 ;
4582 prev_fcst_set := '-1' ;
4583 prev_fcst := '-1';
4584
4585 /* populate the org_string */
4586 IF gmp_calendar_pkg.org_string(pinstance_id) THEN
4587 NULL ;
4588 ELSE
4589 RAISE invalid_string_value ;
4590 END IF;
4591
4592 l_fcst_stmt := 'SELECT '
4593 || ' msi.inventory_item_id, '
4594 || ' msi.organization_id, '
4595 || ' h.forecast_id, '
4596 || ' d.line_id, '
4597 || ' h.forecast, '
4598 || ' h.forecast_set FSET , '
4599 || ' d.trans_date, '
4600 || ' d.orgn_code, '
4601 || ' (d.trans_qty * -1) trans_qty, '
4602 || ' 0 use_fcst_flag '
4603 || ' FROM '
4604 || ' mtl_system_items'||pdblink||' msi, '
4605 || ' ic_item_mst'||pdblink||' iim, '
4606 || ' ic_whse_mst'||pdblink||' wm, '
4607 || ' fc_fcst_hdr'||pdblink||' h, '
4608 || ' fc_fcst_dtl'||pdblink||' d '
4609 || ' WHERE '
4610 || ' msi.organization_id = wm.mtl_organization_id ' ;
4611
4612 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
4613 l_fcst_stmt := l_fcst_stmt
4614 || ' and msi.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
4615 END IF;
4616
4617 l_fcst_stmt := l_fcst_stmt
4618 || ' and msi.segment1 = iim.item_no '
4619 || ' and wm.delete_mark = 0 '
4620 || ' and h.forecast_id = d.forecast_id '
4621 || ' and d.forecast_id > 0 '
4622 || ' and d.item_id = iim.item_id '
4623 || ' and d.whse_code = wm.whse_code '
4624 || ' and d.orgn_code = wm.orgn_code '
4625 || ' and h.forecast_set is NOT NULL '
4626 || ' and h.delete_mark = 0 '
4627 || ' and d.delete_mark = 0 '
4628 || ' and d.trans_qty <> 0 '
4629 || ' ORDER BY wm.mtl_organization_id ,FSET DESC,h.forecast_id ' ;
4630
4631 l_insert_set_stmt :=
4632 ' INSERT INTO msc_st_designators ( '
4633 ||' designator,forecast_set, organization_id, sr_instance_id, '
4634 ||' description, mps_relief, inventory_atp_flag, '
4635 ||' designator_type,disable_date,consume_forecast, '
4636 ||' update_type,backward_update_time_fence,forward_update_time_fence, '
4637 ||' bucket_type,deleted_flag,refresh_id ) '
4638 ||' VALUES '
4639 ||' ( :p1, :p2, :p3,:p4, '
4640 ||' :p5, :p6, :p7, '
4641 ||' :p8, :p9, :p10, '
4642 ||' :p11, :p12, :p13, '
4643 ||' :p14,:p15,:p16 ) ';
4644
4645 l_demands_stmt :=
4646 ' INSERT INTO msc_st_demands ( '
4647 ||' organization_id, inventory_item_id, sr_instance_id, '
4648 ||' using_assembly_item_id, using_assembly_demand_date, '
4649 ||' using_requirement_quantity,demand_class,bucket_type, '
4650 ||' demand_type, origination_type, wip_entity_id, '
4651 ||' demand_schedule_name,forecast_designator, order_number,'
4652 ||' wip_entity_name,sales_order_line_id, selling_price, deleted_flag ) '
4653 ||' VALUES '
4654 ||' ( :p1, :p2, :p3, '
4655 ||' :p4, :p5, :p6, '
4656 ||' :p7, :p8, :p9, '
4657 ||' :p10,:p11,:p12, '
4658 ||' :p13,:p14,:p15, '
4659 ||' :p16,:p17,:p18 )' ;
4660
4661 -- ===+++++++====++++ build designator++++=======++++=======
4662 l_design_stmt := 'SELECT '||
4663 ' forecast_id, '||
4664 ' forecast, '||
4665 ' substr(forecast,1,10) DESGN, '||
4666 ' nvl(forecast_set ,substr(forecast,1,10)) FSET, '||
4667 ' 1 DESGN_IND ,' ||
4668 ' consumption_ind, '||
4669 ' backward_time_fence, '||
4670 ' forward_time_fence '||
4671 ' FROM fc_fcst_hdr'||pdbLink ||
4672 ' WHERE delete_mark = 0 '||
4673 ' UNION ALL '||
4674 -- Add forecast_sets to the list
4675 ' SELECT '||
4676 ' -1 , '||
4677 ' min(forecast), '||
4678 ' forecast_set DESGN , '||
4679 ' to_char(NULL) FSET, '||
4680 ' 3 DESGN_IND, ' ||
4681 ' to_number(NULL), '||
4682 ' to_number(NULL), '||
4683 ' to_number(NULL) '||
4684 ' FROM fc_fcst_hdr'||pdblink ||
4685 ' WHERE delete_mark = 0 '||
4686 ' AND forecast_set is NOT NULL '||
4687 ' GROUP BY forecast_set ' ||
4688 ' ORDER BY FSET, 1 DESC , DESGN_IND ' ;
4689 -- Add fabricated forecast-set to the list
4690 /* Per discussions with Sam Tupe Forecast set name is NOT allowed to be changed
4691 Hence we should NOT collect the forecasts that do NOT have a forecast set */
4692 /*
4693 ' UNION ALL '||
4694 ' SELECT '||
4695 ' -1, '||
4696 ' forecast, '||
4697 ' substr(forecast,1,10) DESGN_IND , '||
4698 ' to_char(NULL) FSET, '||
4699 ' 2 DESGN_IND, '||
4700 ' to_number(NULL), '||
4701 ' to_number(NULL), '||
4702 ' to_number(NULL) '||
4703 ' FROM fc_fcst_hdr'||pdblink ||
4704 ' WHERE delete_mark = 0 '||
4705 ' AND forecast_set is NULL '||
4706 -- With these changes some logic in designator generation has become redundant
4707 */
4708
4709 OPEN fcst_hdr for l_design_stmt ;
4710 LOOP
4711 FETCH fcst_hdr INTO fcst_hdr_tbl(l_cnt);
4712 EXIT WHEN fcst_hdr%NOTFOUND ;
4713 l_cnt := l_cnt + 1 ;
4714 END LOOP ;
4715 CLOSE fcst_hdr ;
4716 -- ===================== Logic ==============================
4717 LOOP
4718 EXIT WHEN cnt + 1 > fcst_hdr_tbl.COUNT ;
4719
4720 IF duplicate_found THEN
4721 cnt := cnt ;
4722 duplicate_found := FALSE ;
4723 ELSE
4724 IF temp_name is NOT NULL THEN
4725 IF (fcst_hdr_tbl(cnt).desgn_ind = 1
4726 AND fcst_hdr_tbl(cnt).fcst_name <> temp_name )THEN
4727 -- fcst_hdr_tbl(cnt).fcst_set := temp_name ;
4728 NULL ;
4729 ELSIF (fcst_hdr_tbl(cnt).desgn_ind = 3
4730 AND fcst_hdr_tbl(cnt).fcst_name <> temp_name )THEN
4731 -- This means we changed a set name
4732 -- Now change the name in all resords of fcst that used this as set
4733 FOR y in 1..fcst_hdr_tbl.COUNT
4734 LOOP
4735 IF (fcst_hdr_tbl(y).fcst_set = fcst_hdr_tbl(cnt).fcst_name
4736 AND fcst_hdr_tbl(y).desgn_ind = 1 ) THEN
4737 fcst_hdr_tbl(y).fcst_set := temp_name ;
4738 END IF ;
4739 END LOOP;
4740 ELSIF (fcst_hdr_tbl(cnt).desgn_ind = 2
4741 AND fcst_hdr_tbl(cnt).fcst_name <> temp_name )THEN
4742 -- This means we changed a set name that was "generated"
4743 -- Now change the name in the resord of fcst that used itself as set
4744 FOR y in 1..fcst_hdr_tbl.COUNT
4745 LOOP
4746 IF (fcst_hdr_tbl(y).orig_forecast = fcst_hdr_tbl(cnt).orig_forecast
4747 AND fcst_hdr_tbl(y).desgn_ind = 1 )THEN
4748 fcst_hdr_tbl(y).fcst_set := temp_name ;
4749 END IF ;
4750 END LOOP;
4751 END IF ; -- desgn_ind check
4752 fcst_hdr_tbl(cnt).fcst_name := temp_name ;
4753
4754 END IF ;
4755
4756 cnt := cnt + 1 ;
4757 j := 10 ;
4758 k := 0 ;
4759 END IF ;
4760
4761 IF j < 10 THEN
4762 temp_name := substr(fcst_hdr_tbl(cnt).fcst_name,1,j)||to_char(k) ;
4763 ELSE
4764 temp_name := fcst_hdr_tbl(cnt).fcst_name ;
4765 END IF ;
4766
4767 curr_cnt := cnt ;
4768
4769 i := 1 ;
4770
4771 LOOP
4772 EXIT WHEN i > fcst_hdr_tbl.COUNT ;
4773
4774 IF i <> curr_cnt THEN
4775 -- so that record is not compared to itself
4776 IF temp_name = fcst_hdr_tbl(i).fcst_name THEN
4777 duplicate_found := TRUE ;
4778 k := k + 1 ;
4779
4780 IF k < 10 THEN
4781 j := 9 ;
4782 ELSIF k < 100 THEN
4783 j := 8 ;
4784 ELSIF k < 1000 THEN
4785 j := 7 ;
4786 ELSIF k < 10000 THEN
4787 j := 6 ;
4788 ELSIF k < 100000 THEN
4789 j := 5 ;
4790 END IF ;
4791
4792 EXIT ;
4793
4794 END IF ;
4795 END IF ; -- i <> curr_cnt
4796
4797 i := i + 1 ;
4798 END LOOP ;
4799
4800
4801 END LOOP ; -- Outer loop
4802
4803 /*
4804 FOR x in 1..fcst_hdr_tbl.COUNT
4805 LOOP
4806 log_message(fcst_hdr_tbl(x).fcst_id||
4807 '='||fcst_hdr_tbl(x).orig_forecast ||
4808 '='||fcst_hdr_tbl(x).desgn_ind ||
4809 '='||fcst_hdr_tbl(x).fcst_name ||
4810 '='||fcst_hdr_tbl(x).fcst_set ) ;
4811 END LOOP;
4812 */
4813 -- ===+++++++====++++ build designator++++=======++++=======
4814
4815 OPEN cur_fcst_dtl FOR l_fcst_stmt;
4816 LOOP
4817 write_fcst := FALSE ;
4818 write_fcst_set := FALSE ;
4819
4820 FETCH cur_fcst_dtl INTO fcst_dtl_rec;
4821 EXIT WHEN cur_fcst_dtl%NOTFOUND;
4822 IF fcst_dtl_rec.organization_id <> prev_org_id THEN
4823 -- Write an entry for forecast
4824 write_fcst := TRUE ;
4825 write_fcst_set := TRUE ;
4826 prev_org_id := fcst_dtl_rec.organization_id ;
4827 END IF ;
4828 -- also check if the set has changed ,if so write an entry for set
4829 IF fcst_dtl_rec.forecast_id <> prev_fcst_id THEN
4830 write_fcst := TRUE ;
4831 -- get designator, forecast_name
4832 -- Temporarily putting a code - inefficient
4833 FOR i in 1..fcst_hdr_tbl.COUNT
4834 LOOP
4835 IF fcst_dtl_rec.forecast_id = fcst_hdr_tbl(i).fcst_id THEN
4836 fcst_locn := i ;
4837 EXIT ;
4838 END IF ;
4839 END LOOP ;
4840 IF fcst_hdr_tbl(fcst_locn).fcst_set <> prev_fcst_set THEN
4841 -- insert set name for currrent org
4842 write_fcst_set := TRUE ;
4843 END IF ; -- end if for change of fcst_set
4844 END IF ; -- endif of fcst_is change
4845
4846 prev_fcst := nvl(fcst_hdr_tbl(fcst_locn).fcst_name ,'-2');
4847 prev_fcst_id := fcst_dtl_rec.forecast_id ;
4848
4849 IF write_fcst_set THEN
4850
4851 i_index := i_index + 1 ;
4852 i_designator(i_index) := fcst_hdr_tbl(fcst_locn).fcst_set ;
4853 i_forecast_set(i_index) := to_char(NULL) ;
4854 i_organization_id(i_index) := fcst_dtl_rec.organization_id ;
4855 i_sr_instance_id(i_index) := pinstance_id ;
4856 i_description(i_index) := fcst_hdr_tbl(fcst_locn).fcst_set ;
4857 -- mps_relief(i_index) := 0; /* mps relief */
4858 -- inventory_atp_flag(i_index) := 0; /* inventory atp flag */
4859 -- designator_type(i_index) := 6; /* designator type */
4860 i_disable_date(i_index) := TO_DATE(NULL); /* disable date */
4861 i_consume_forecast(i_index) := fcst_hdr_tbl(fcst_locn).consumption_ind ;
4862 -- update_type(i_index) := 6; /* Update type */
4863 i_backward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).backward_time_fence ;
4864 i_forward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).forward_time_fence ;
4865 -- bucket_type(i_index) := 1 ; /* bucket type */ ;
4866 -- deleted_flag(i_index) := 2 ;
4867 -- refresh_id := 0 ; /* Refresh id */
4868
4869 prev_fcst_set := fcst_hdr_tbl(fcst_locn).fcst_set ;
4870
4871 END IF ;
4872
4873 IF write_fcst THEN
4874
4875 i_index := i_index + 1 ;
4876 i_designator(i_index) := fcst_hdr_tbl(fcst_locn).fcst_name ;
4877 i_forecast_set(i_index) := fcst_hdr_tbl(fcst_locn).fcst_set ;
4878 i_organization_id(i_index) := fcst_dtl_rec.organization_id ;
4879 i_sr_instance_id(i_index) := pinstance_id ;
4880 i_description(i_index) := fcst_hdr_tbl(fcst_locn).fcst_name ;
4881 -- mps_relief(i_index) := 0; /* mps relief */
4882 -- inventory_atp_flag(i_index) := 0; /* inventory atp flag */
4883 -- designator_type(i_index) := 6; /* designator type,For forecast the value will be 6 */
4884 i_disable_date(i_index) := TO_DATE(NULL); /* disable date */
4885 i_consume_forecast(i_index) := fcst_hdr_tbl(fcst_locn).consumption_ind ;
4886 -- update_type(i_index) := 6; /* Update Type,For Process value will be 6 */
4887 i_backward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).backward_time_fence ;
4888 i_forward_update_time_fence(i_index) := fcst_hdr_tbl(fcst_locn).forward_time_fence ;
4889 -- bucket_type(i_index) := 1 ; /* bucket type */ ;
4890 -- deleted_flag(i_index) := 2 ;
4891 -- refresh_id := 0 ; /* Refresh id */
4892
4893 END IF ;
4894
4895 -- and now write the forecast details entry also.
4896 /* Demands Bulk inserts */
4897 d_index := d_index + 1 ;
4898 f_organization_id(d_index) := fcst_dtl_rec.organization_id ;
4899 f_inventory_item_id(d_index) := fcst_dtl_rec.inventory_item_id ;
4900 f_sr_instance_id(d_index) := pinstance_id ;
4901 f_assembly_item_id(d_index) := fcst_dtl_rec.inventory_item_id ;
4902 f_demand_date(d_index) := fcst_dtl_rec.trans_date ;
4903 f_requirement_quantity(d_index) := fcst_dtl_rec.trans_qty ;
4904 -- demand_class := null_value ; /* Demand Class */
4905 -- bucket_type(d_index) := 1 ; /* Bucket type */
4906 -- demand_type(d_index) := 1 ; /* demand type */
4907 -- origination_type(d_index) := 29 ; /* origination type */
4908 -- wip_entity_id(d_index) := null_value ; /* wip_entity id */
4909 -- demand_schedule(d_index) := null_value ; /* demand Schedule name */
4910 f_forecast_designator(d_index) :=
4911 fcst_hdr_tbl(fcst_locn).fcst_name ; /* forecast designator */
4912 f_order_number(d_index) := fcst_hdr_tbl(fcst_locn).fcst_name; /* Order Number */
4913 -- wip_entity_name(d_index) := null_value ; /* wip entity name */
4914 f_sales_order_line_id(d_index) := fcst_dtl_rec.line_id ; /* Sales Order line Id */
4915 -- selling_price(d_index) := null_value ; /* Selling Price */
4916 -- deleted_flag := 2 ;
4917
4918 END LOOP ;
4919 CLOSE cur_fcst_dtl;
4920
4921 /* ----------------------- Demands Insert --------------------- */
4922 i := 1 ;
4923 log_message(f_organization_id.FIRST || ' *forecast*' || f_organization_id.LAST );
4924 IF f_organization_id.FIRST > 0 THEN
4925 FORALL i IN f_organization_id.FIRST..f_organization_id.LAST
4926 INSERT INTO msc_st_demands (
4927 organization_id,
4928 inventory_item_id,
4929 sr_instance_id,
4930 using_assembly_item_id,
4931 using_assembly_demand_date,
4932 using_requirement_quantity,
4933 demand_class,
4934 bucket_type,
4935 demand_type,
4936 origination_type,
4937 wip_entity_id,
4938 demand_schedule_name,
4939 forecast_designator,
4940 order_number,
4941 wip_entity_name,
4942 sales_order_line_id,
4943 selling_price,
4944 deleted_flag )
4945 VALUES (
4946 f_organization_id(i),
4947 f_inventory_item_id(i),
4948 f_sr_instance_id(i),
4949 f_assembly_item_id(i),
4950 f_demand_date(i),
4951 f_requirement_quantity(i),
4952 null_value, /* demand_class */
4953 1, /* bucket_type */
4954 1, /* demand_type */
4955 29, /* origination_type */
4956 null_value, /* wip_entity_id */
4957 null_value, /* demand_schedule_name */
4958 f_forecast_designator(i),
4959 f_order_number(i),
4960 null_value, /* wip_entity_name */
4961 f_sales_order_line_id(i),
4962 null_value, /* selling_price */
4963 2 /* deleted_flag */
4964 ) ;
4965 END IF ;
4966
4967 /* ----------------------- Designator Insert --------------------- */
4968 i := 1 ;
4969 log_message(i_organization_id.FIRST || ' *Designator*' || i_organization_id.LAST );
4970 IF i_organization_id.FIRST > 0 THEN
4971 FORALL i IN i_organization_id.FIRST..i_organization_id.LAST
4972 INSERT INTO msc_st_designators (
4973 designator,
4974 forecast_set,
4975 organization_id,
4976 sr_instance_id,
4977 description,
4978 mps_relief,
4979 inventory_atp_flag,
4980 designator_type,
4981 disable_date,
4982 consume_forecast,
4983 update_type,
4984 backward_update_time_fence,
4985 forward_update_time_fence,
4986 bucket_type,
4987 deleted_flag,
4988 refresh_id
4989 )
4990 VALUES (
4991 i_designator(i) ,
4992 i_forecast_set(i) ,
4993 i_organization_id(i),
4994 i_sr_instance_id(i) ,
4995 i_description(i) ,
4996 0, /* mps relief */
4997 0, /* inventory atp flag */
4998 6, /* designator type,For forecast the value will be 6 */
4999 i_disable_date(i) ,
5000 i_consume_forecast(i),
5001 6, /* Update Type,For Process value will be 6 */
5002 i_backward_update_time_fence(i),
5003 i_forward_update_time_fence(i) ,
5004 1, /* bucket_type */
5005 2, /* deleted_flag */
5006 0 /* refresh_id */
5007 ) ;
5008 END IF ;
5009
5010 return_status := TRUE ;
5011
5012 EXCEPTION
5013 WHEN invalid_string_value THEN
5014 log_message('Organization string is Invalid ' );
5015 return_status := FALSE;
5016
5017 WHEN OTHERS THEN
5018 log_message('Failure occured during the Forecast_extract');
5019 log_message(sqlerrm);
5020 return_status := FALSE;
5021
5022 END extract_forecasts ;
5023
5024
5025 /************************************************************************
5026 * NAME
5027 * Log_message
5028 *
5029 * DESCRIPTION
5030 * Put the debug message in log file.
5031 * HISTORY
5032 * Created By : Rajesh Patangya
5033 ************************************************************************/
5034 PROCEDURE LOG_MESSAGE(pBUFF IN VARCHAR2) IS
5035 BEGIN
5036 IF v_cp_enabled THEN
5037 IF fnd_global.conc_request_id > 0 THEN
5038 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
5039 ELSE
5040 null;
5041 END IF;
5042 ELSE
5043 null ;
5044 END IF;
5045 EXCEPTION
5046 WHEN OTHERS THEN
5047 RETURN;
5048 END LOG_MESSAGE;
5049
5050 /* **************************************************************************
5051 * NAME
5052 * associate_forecasts
5053 *
5054 * DESCRIPTION
5055 * For each schedule forecast combination, mark the forecast table
5056 * HISTORY
5057 * Created By : Rajesh Patangya
5058 ************************************************************************/
5059 FUNCTION associate_forecasts ( pschd_fcst_cnt IN NUMBER,
5060 pschd_id IN PLS_INTEGER ) return BOOLEAN
5061 IS
5062 found_fcst BOOLEAN := FALSE ;
5063 schd_cnt NUMBER := 1 ;
5064 i NUMBER := 1 ;
5065 f1 NUMBER := 1 ;
5066 BEGIN
5067 -- Clean the earlier associations
5068 FOR f1 in 1..gfcst_size
5069 LOOP
5070 fcst_dtl_tab(f1).use_fcst_flag := 0 ;
5071 END LOOP;
5072
5073 FOR schd_cnt in pschd_fcst_cnt..gschd_fcst_size
5074 LOOP
5075 IF pschd_id > schd_fcst_dtl_tab(schd_cnt).schedule_id THEN
5076 NULL ;
5077 ELSIF pschd_id = schd_fcst_dtl_tab(schd_cnt).schedule_id THEN
5078 FOR i in 1..gfcst_size
5079 LOOP
5080 IF fcst_dtl_tab(i).forecast_id =
5081 schd_fcst_dtl_tab(schd_cnt).forecast_id THEN
5082 fcst_dtl_tab(i).use_fcst_flag := 1 ;
5083 found_fcst := TRUE ;
5084 END IF;
5085 END LOOP;
5086 ELSE
5087 /* pschd_id < schd_fcst_dtl_tab(schd_cnt).schedule_id THEN */
5088 gschd_fcst_cnt := schd_cnt ;
5089 EXIT ;
5090 END IF;
5091 END LOOP ;
5092 RETURN found_fcst ;
5093
5094 END associate_forecasts;
5095
5096 /* **************************************************************************
5097 * NAME
5098 * check_forecast
5099 *
5100 * DESCRIPTION
5101 * Inventory item, Warehouse combination check, hence reached to the
5102 * record for further processing
5103 * HISTORY
5104 * Created By : Rajesh Patangya
5105 ************************************************************************/
5106 FUNCTION check_forecast(pfcst_counter IN NUMBER,
5107 pinventory_item_id IN PLS_INTEGER,
5108 porganization_id IN PLS_INTEGER) return BOOLEAN
5109 IS
5110 fcst_i NUMBER := 1 ;
5111 BEGIN
5112 /* Loop through the forecast table for the matching inventory_item_id
5113 and organization_id (Process warehouse) */
5114
5115 FOR fcst_i in pfcst_counter..gfcst_size
5116 LOOP
5117 IF (fcst_dtl_tab(fcst_i).use_fcst_flag = 1) THEN
5118
5119 IF fcst_dtl_tab(fcst_i).inventory_item_id > pinventory_item_id THEN
5120 return FALSE ;
5121 ELSIF fcst_dtl_tab(fcst_i).inventory_item_id = pinventory_item_id THEN
5122 IF fcst_dtl_tab(fcst_i).organization_id > porganization_id THEN
5123 return FALSE ;
5124 ELSIF fcst_dtl_tab(fcst_i).organization_id = porganization_id THEN
5125 return TRUE ;
5126 END IF;
5127 END IF;
5128
5129 END IF; /* Use Flag If */
5130 END LOOP;
5131 -- If no rows were found after looping the whole table, return false
5132 return FALSE ;
5133
5134 END check_forecast ;
5135
5136 /* **************************************************************************
5137 * NAME
5138 * check_so
5139 *
5140 * DESCRIPTION
5141 * Inventory item, Warehouse combination check, hence reached to the
5142 * record for further processing
5143 * HISTORY
5144 * Created By : Rajesh Patangya
5145 ************************************************************************/
5146 FUNCTION check_so( pso_counter IN NUMBER,
5147 pinventory_item_id IN PLS_INTEGER,
5148 porganization_id IN PLS_INTEGER) return BOOLEAN
5149 IS
5150
5151 so_i NUMBER := 0;
5152 BEGIN
5153 /* Loop through the Sales order table for the matching inventroy item_id
5154 and organization_id(whse) */
5155
5156 FOR so_i in pso_counter..gso_size
5157 LOOP
5158 IF sales_dtl_tab(so_i).inventory_item_id > pinventory_item_id THEN
5159 return FALSE ;
5160 ELSIF sales_dtl_tab(so_i).inventory_item_id = pinventory_item_id THEN
5161 IF sales_dtl_tab(so_i).organization_id > porganization_id THEN
5162 return FALSE ;
5163 ELSIF sales_dtl_tab(so_i).organization_id = porganization_id THEN
5164 return TRUE ;
5165 END IF;
5166 END IF;
5167 END LOOP ;
5168 -- If no rows were found after looping the whole table, return false
5169 return FALSE ;
5170
5171 END check_so ;
5172
5173 /* **************************************************************************
5174 * NAME
5175 * consume_forecast
5176 *
5177 * DESCRIPTION
5178 * This procedure will consume the forecast for the values that are
5179 * are loaded into the sales and forecast pl/sql tables. The occurences
5180 * are passed in as paramaters. The sales orders that fall on or after
5181 * a forecast for the same item/whse but before the next forecast for the
5182 * same will decrease the value of the forecast by the amount of the
5183 * sales order line until it is zero.
5184 * HISTORY
5185 * Created By : Rajesh Patangya
5186 * P Dong
5187 * 09/14/01 - Added api_mode to pass to insert_demands
5188 ************************************************************************/
5189 PROCEDURE consume_forecast( pinventory_item_id IN PLS_INTEGER,
5190 porganization_id IN PLS_INTEGER,
5191 papi_mode IN BOOLEAN )
5192 AS
5193 cfcst_cnt PLS_INTEGER := 0 ;
5194 cso_cnt PLS_INTEGER := 0 ;
5195 found_forecast BOOLEAN := FALSE ;
5196 BEGIN
5197 FOR cfcst_cnt in gfcst_cnt..gfcst_size
5198 LOOP
5199
5200 IF (fcst_dtl_tab(cfcst_cnt).use_fcst_flag = 1 ) THEN
5201
5202 IF fcst_dtl_tab(cfcst_cnt).inventory_item_id = pinventory_item_id AND
5203 fcst_dtl_tab(cfcst_cnt).organization_id = porganization_id THEN
5204 found_forecast := TRUE ; /* B2922488 */
5205 FOR cso_cnt in gso_cnt..gso_size
5206 LOOP
5207 IF fcst_dtl_tab(cfcst_cnt).inventory_item_id =
5208 sales_dtl_tab(cso_cnt).inventory_item_id AND
5209 fcst_dtl_tab(cfcst_cnt).organization_id =
5210 sales_dtl_tab(cso_cnt).organization_id THEN
5211
5212 IF fcst_dtl_tab(cfcst_cnt).trans_date <=
5213 sales_dtl_tab(cso_cnt).sched_shipdate THEN
5214
5215 IF fcst_dtl_tab(cfcst_cnt).consumed_qty > 0 THEN
5216 fcst_dtl_tab(cfcst_cnt).consumed_qty :=
5217 fcst_dtl_tab(cfcst_cnt).consumed_qty -
5218 sales_dtl_tab(cso_cnt).trans_qty ;
5219 END IF ; /* consumed_qty match */
5220 write_this_so(cso_cnt,papi_mode) ;
5221
5222 ELSE /* The fcst date is greater than so date, therefore write fcst */
5223 IF fcst_dtl_tab(cfcst_cnt).consumed_qty > 0 THEN
5224 write_this_fcst (cfcst_cnt,papi_mode);
5225 -- B2596464, Modified by Rajesh Patangya 26-SEP-2002
5226 -- Once forecast is written, make the quantity = 0,
5227 -- so that outside the loop it will not be written again.
5228 fcst_dtl_tab(cfcst_cnt).consumed_qty := 0 ;
5229 END IF ;
5230 EXIT ;
5231 END IF ; /* trans_date match */
5232
5233 ELSIF (fcst_dtl_tab(cfcst_cnt).inventory_item_id <
5234 sales_dtl_tab(cso_cnt).inventory_item_id ) OR
5235 ( fcst_dtl_tab(cfcst_cnt).inventory_item_id =
5236 sales_dtl_tab(cso_cnt).inventory_item_id AND
5237 fcst_dtl_tab(cfcst_cnt).organization_id <
5238 sales_dtl_tab(cso_cnt).organization_id
5239 ) THEN
5240 EXIT ;
5241 END IF ;
5242 END LOOP; /* SO loop */
5243 -- After Looping through all SO , if the forecast remains
5244 -- unconsumed, write it to the table
5245 IF fcst_dtl_tab(cfcst_cnt).consumed_qty > 0 THEN
5246 write_this_fcst (cfcst_cnt,papi_mode);
5247 END IF ;
5248 ELSIF
5249 (fcst_dtl_tab(cfcst_cnt).inventory_item_id > pinventory_item_id) OR
5250 (fcst_dtl_tab(cfcst_cnt).inventory_item_id = pinventory_item_id AND
5251 fcst_dtl_tab(cfcst_cnt).organization_id > porganization_id ) THEN
5252 gfcst_cnt := cfcst_cnt ;
5253 write_so(gso_cnt,pinventory_item_id,porganization_id,papi_mode);
5254 EXIT ;
5255 END IF ;
5256 END IF ; /* use_fcst_flag */
5257 END LOOP ; /* FCST loop */
5258
5259 IF NOT (found_forecast) THEN
5260 -- At last, if there is no forecast at all, then you have to write
5261 -- all the sales orders
5262 write_so(gso_cnt,pinventory_item_id,porganization_id,papi_mode);
5263 END IF;
5264
5265 EXCEPTION
5266 WHEN OTHERS THEN
5267 log_message('Failure occured during write_forecast');
5268 log_message(sqlerrm);
5269 RAISE;
5270 END consume_forecast ;
5271
5272 /* **************************************************************************
5273 * NAME
5274 * write_forecast
5275 *
5276 * DESCRIPTION
5277 * Loop through the forecast table for the matching inventory item_id
5278 * and organization_id(whse)
5279 * and insert into the destination table
5280 * exit when item_id changes after noting down the counter position
5281 * HISTORY
5282 * Created By : Rajesh Patangya
5283 * P Dong
5284 * 09/14/01 - Added api_mode to pass to insert_demands
5285 ************************************************************************/
5286 PROCEDURE write_forecast( pfcst_counter IN NUMBER,
5287 pinventory_item_id IN PLS_INTEGER,
5288 porganization_id IN PLS_INTEGER,
5289 papi_mode IN BOOLEAN)
5290 AS
5291 fcst_i PLS_INTEGER := 0 ;
5292
5293 BEGIN
5294 -- A safety can be installed here
5295 IF gfcst_size >= pfcst_counter THEN
5296
5297
5298 FOR fcst_i in pfcst_counter..gfcst_size
5299 LOOP
5300 IF (fcst_dtl_tab(fcst_i).use_fcst_flag = 1 ) THEN
5301
5302 IF fcst_dtl_tab(fcst_i).inventory_item_id > pinventory_item_id THEN
5303 gfcst_cnt := fcst_i ;
5304 EXIT ;
5305 ELSIF fcst_dtl_tab(fcst_i).inventory_item_id = pinventory_item_id THEN
5306 IF fcst_dtl_tab(fcst_i).organization_id > porganization_id THEN
5307 gfcst_cnt := fcst_i ;
5308 EXIT ;
5309 ELSIF fcst_dtl_tab(fcst_i).organization_id = porganization_id THEN
5310 IF fcst_dtl_tab(fcst_i).consumed_qty > 0 THEN
5311 write_this_fcst(fcst_i,papi_mode) ;
5312 END IF ;
5313 END IF;
5314 END IF;
5315
5316 END IF; /* Use Flag If */
5317 END LOOP;
5318
5319 END IF; /* Safety feature */
5320
5321 EXCEPTION
5322 WHEN OTHERS THEN
5323 log_message('Failure occured during write_forecast');
5324 log_message(sqlerrm);
5325 RAISE;
5326 END write_forecast ;
5327
5328 /* **************************************************************************
5329 * NAME
5330 * write_so
5331 *
5332 * DESCRIPTION
5333 * Loop through the Sales order table for the matching inventory item_id
5334 * and organization_id(whse)
5335 * and insert into the destination table
5336 * exit when item_id changes after noting down the counter position
5337 * HISTORY
5338 * Created By : Rajesh Patangya
5339 * P Dong
5340 * 09/14/01 - Added api_mode to pass to insert_demands
5341 ************************************************************************/
5342 PROCEDURE write_so( pso_counter IN NUMBER,
5343 pinventory_item_id IN PLS_INTEGER,
5344 porganization_id IN PLS_INTEGER,
5345 papi_mode IN BOOLEAN)
5346 AS
5347 so_i PLS_INTEGER := 0 ;
5348
5349 BEGIN
5350 -- A safety can be installed here
5351 IF gso_size >= pso_counter THEN
5352
5353 FOR so_i in pso_counter..gso_size
5354 LOOP
5355 IF sales_dtl_tab(so_i).inventory_item_id > pinventory_item_id THEN
5356 gso_cnt := so_i ;
5357 EXIT ;
5358 ELSIF sales_dtl_tab(so_i).inventory_item_id = pinventory_item_id THEN
5359 IF sales_dtl_tab(so_i).organization_id > porganization_id THEN
5360 gso_cnt := so_i ;
5361 EXIT ;
5362 ELSIF sales_dtl_tab(so_i).organization_id = porganization_id THEN
5363 write_this_so(so_i,papi_mode) ;
5364 END IF;
5365 END IF;
5366 END LOOP ;
5367
5368 END IF; /* Safety feature */
5369
5370 EXCEPTION
5371 WHEN OTHERS THEN
5372 log_message('Failure occured during write_so');
5373 log_message(sqlerrm);
5374 RAISE;
5375 END write_so ;
5376
5377 /* **************************************************************************
5378 * NAME
5379 * write_this_so
5380 *
5381 * DESCRIPTION
5382 * Call to build designator to get unique designator,
5383 * insert sales order into msc_st_demand
5384 * HISTORY
5385 * Created By : Rajesh Patangya
5386 * P Dong
5387 * 09/14/01 - Added api_mode to pass to insert_demands
5388 * 05/21/03 - B2971996 - Populating request_date in msc_st_demands table
5389 ************************************************************************/
5390 PROCEDURE write_this_so(pcounter IN NUMBER,
5391 sapi_mode IN BOOLEAN)
5392 AS
5393 statement_demands_api VARCHAR2(3000) := NULL ;
5394 statement_demands VARCHAR2(3000) := NULL ;
5395
5396 BEGIN
5397 g_delimiter := '/';
5398 build_designator(g_item_tbl_position, g_delimiter, gcurrent_designator);
5399
5400 IF sapi_mode
5401 THEN
5402 BEGIN
5403 statement_demands_api :=
5404 ' INSERT INTO gmp_demands_api ( '
5405 ||' organization_id, schedule_id, inventory_item_id, demand_date, '
5406 ||' demand_quantity, origination_type, doc_id, selling_price ) '
5407 ||' VALUES '
5408 ||' ( :p1, :p2, :p3, :p4, '
5409 ||' :p5, :p6, :p7, :p8 ) ';
5410
5411 EXECUTE IMMEDIATE statement_demands_api USING
5412 sales_dtl_tab(pcounter).organization_id,
5413 sched_dtl_tab(g_item_tbl_position).schedule_id,
5414 sales_dtl_tab(pcounter).inventory_item_id,
5415 sales_dtl_tab(pcounter).sched_shipdate,
5416 sales_dtl_tab(pcounter).trans_qty,
5417 6, /* origination type */
5418 null_value, /* wip_entity id */
5419 sales_dtl_tab(pcounter).net_price ;
5420
5421 /* Global vairable Updation to next record */
5422 /* B2929759, Rajesh Patangya 28-APR-2003 */
5423 gso_cnt := pcounter + 1 ;
5424
5425 EXCEPTION
5426 WHEN OTHERS THEN
5427 log_message('Failure occured during the insert into gmp_demands_api');
5428 log_message(sqlerrm);
5429 RAISE;
5430 END;
5431 ELSE
5432 BEGIN
5433
5434 statement_demands :=
5435 ' INSERT INTO msc_st_demands ( '
5436 ||' organization_id, inventory_item_id, sr_instance_id, '
5437 ||' using_assembly_item_id, using_assembly_demand_date, '
5438 ||' using_requirement_quantity, demand_type, origination_type, '
5439 ||' wip_entity_id, demand_schedule_name, order_number, '
5440 ||' wip_entity_name, selling_price,request_date,deleted_flag ) ' /*B2971996*/
5441 ||' VALUES '
5442 ||' ( :p1, :p2, :p3, '
5443 ||' :p4, :p5, '
5444 ||' :p6, :p7, :p8 , '
5445 ||' :p9, :p10,:p11, '
5446 ||' :p12,:p13,:p14,:p15 )' ;
5447
5448 EXECUTE IMMEDIATE statement_demands USING
5449 sales_dtl_tab(pcounter).organization_id,
5450 sales_dtl_tab(pcounter).inventory_item_id,
5451 g_instance_id,
5452 sales_dtl_tab(pcounter).inventory_item_id,
5453 sales_dtl_tab(pcounter).sched_shipdate,
5454 sales_dtl_tab(pcounter).trans_qty,
5455 1, /* demand type */
5456 6, /* origination type */
5457 null_value, /* wip_entity id */
5458 gcurrent_designator,
5459 sales_dtl_tab(pcounter).orgn_code || g_delimiter ||
5460 sales_dtl_tab(pcounter).order_no,
5461 null_value, /* wip entity name */
5462 sales_dtl_tab(pcounter).net_price,
5463 sales_dtl_tab(pcounter).request_date, /* B2971996 */
5464 2 ;
5465
5466 /* Global vairable Updation to next record */
5467 /* B2929759, Rajesh Patangya 28-APR-2003 */
5468 gso_cnt := pcounter + 1 ;
5469
5470 EXCEPTION
5471 WHEN OTHERS THEN
5472 log_message('Failure occured during write_this_so');
5473 log_message(sqlerrm);
5474 RAISE;
5475 END;
5476
5477 END IF;
5478 END write_this_so ;
5479
5480 /* **************************************************************************
5481 * NAME
5482 * write_this_fcst
5483 *
5484 * DESCRIPTION
5485 * Call to build designator to get unique designator,
5486 * insert forecast into msc_st_demand
5487 * HISTORY
5488 * Created By : Rajesh Patangya
5489 * P Dong
5490 * 09/14/01 - Added api_mode to pass to insert_demands
5491 ************************************************************************/
5492 PROCEDURE write_this_fcst(pcounter IN NUMBER,
5493 fapi_mode IN BOOLEAN)
5494 AS
5495
5496 statement_demands_api VARCHAR2(3000) := NULL ;
5497 statement_demands VARCHAR2(3000) := NULL ;
5498
5499 BEGIN
5500
5501 g_delimiter := '/';
5502 build_designator(g_item_tbl_position, g_delimiter, gcurrent_designator);
5503
5504 IF fapi_mode
5505 THEN
5506 BEGIN
5507 statement_demands_api :=
5508 ' INSERT INTO gmp_demands_api ( '
5509 ||' organization_id, schedule_id, inventory_item_id, demand_date, '
5510 ||' demand_quantity, origination_type, doc_id, selling_price ) '
5511 ||' VALUES '
5512 ||' ( :p1, :p2, :p3, :p4, '
5513 ||' :p5, :p6, :p7, :p8 ) ';
5514
5515 EXECUTE IMMEDIATE statement_demands_api USING
5516 fcst_dtl_tab(pcounter).organization_id,
5517 sched_dtl_tab(g_item_tbl_position).schedule_id,
5518 fcst_dtl_tab(pcounter).inventory_item_id,
5519 fcst_dtl_tab(pcounter).trans_date,
5520 fcst_dtl_tab(pcounter).consumed_qty,
5521 7, /* origination type */
5522 null_value, /* wip_entity id */
5523 null_value ;
5524
5525 /* Global vairable Updation to next record */
5526 /* B2929759, Rajesh Patangya 28-APR-2003 */
5527 gfcst_cnt := pcounter + 1 ;
5528
5529 EXCEPTION
5530 WHEN OTHERS THEN
5531 log_message('Failure occured during the insert into gmp_demands_api');
5532 log_message(sqlerrm);
5533 RAISE;
5534 END;
5535 ELSE
5536 BEGIN
5537 statement_demands :=
5538 ' INSERT INTO msc_st_demands ( '
5539 ||' organization_id, inventory_item_id, sr_instance_id, '
5540 ||' using_assembly_item_id, using_assembly_demand_date, '
5541 ||' using_requirement_quantity, demand_type, origination_type, '
5542 ||' wip_entity_id, demand_schedule_name, order_number, '
5543 ||' wip_entity_name, selling_price, deleted_flag ) '
5544 ||' VALUES '
5545 ||' ( :p1, :p2, :p3, '
5546 ||' :p4, :p5, '
5547 ||' :p6, :p7, :p8 , '
5548 ||' :p9, :p10,:p11, '
5549 ||' :p12,:p13,:p14 )' ;
5550
5551 EXECUTE IMMEDIATE statement_demands USING
5552 fcst_dtl_tab(pcounter).organization_id,
5553 fcst_dtl_tab(pcounter).inventory_item_id,
5554 g_instance_id,
5555 fcst_dtl_tab(pcounter).inventory_item_id,
5556 fcst_dtl_tab(pcounter).trans_date,
5557 fcst_dtl_tab(pcounter).consumed_qty,
5558 1, /* demand type */
5559 7, /* origination type */
5560 null_value, /* wip_entity id */
5561 gcurrent_designator,
5562 fcst_dtl_tab(pcounter).forecast ,
5563 null_value, /* wip entity name */
5564 null_value,
5565 2 ;
5566
5567 /* Global vairable Updation to next record */
5568 /* B2929759, Rajesh Patangya 28-APR-2003 */
5569 gfcst_cnt := pcounter + 1 ;
5570
5571 EXCEPTION
5572 WHEN OTHERS THEN
5573 log_message('Failure occured during write_this_fcst');
5574 log_message(sqlerrm);
5575 RAISE;
5576 END;
5577
5578 END IF;
5579 END write_this_fcst ;
5580
5581 /* **************************************************************************
5582 * NAME
5583 * time_stamp
5584 *
5585 * DESCRIPTION
5586 * Put the time stamp, whenever in prgroma required
5587 * HISTORY
5588 * Created By : Rajesh Patangya
5589 ************************************************************************/
5590 PROCEDURE time_stamp IS
5591
5592 cur_time VARCHAR2(25) := NULL ;
5593 BEGIN
5594 SELECT to_char(sysdate,'DD-MON-RRRR HH24:MI:SS')
5595 INTO cur_time FROM sys.dual ;
5596
5597 log_message(cur_time);
5598 EXCEPTION
5599 WHEN OTHERS THEN
5600 log_message('Failure occured in time_stamp');
5601 log_message(sqlerrm);
5602 RAISE;
5603 END time_stamp ;
5604
5605 /* **************************************************************************
5606 * NAME
5607 * insert_designator
5608 *
5609 * DESCRIPTION
5610 * Insert all the designator for schedule/item/warehouse combination
5611 * HISTORY
5612 * Created By : Rajesh Patangya
5613 ************************************************************************/
5614 PROCEDURE insert_designator IS
5615
5616 i PLS_INTEGER := 1 ;
5617 st_designators VARCHAR2(3000) := NULL ;
5618
5619 BEGIN
5620
5621 g_delimiter := '/';
5622 st_designators :=
5623 ' INSERT INTO msc_st_designators ( '
5624 ||' designator, organization_id, sr_instance_id, '
5625 ||' description, mps_relief, inventory_atp_flag, '
5626 ||' designator_type ) '
5627 ||' VALUES '
5628 ||' ( :p1, :p2, :p3, '
5629 ||' :p4, :p5, :p6, '
5630 ||' :p7 ) ';
5631
5632 FOR i IN 1..desig_tab.COUNT LOOP
5633
5634 EXECUTE IMMEDIATE st_designators USING
5635 desig_tab(i).designator,
5636 desig_tab(i).organization_id,
5637 g_instance_id,
5638 desig_tab(i).orgn_code || g_delimiter || desig_tab(i).schedule
5639 || g_delimiter || desig_tab(i).whse_code,
5640 2,
5641 2,
5642 1 ;
5643
5644 END LOOP;
5645 EXCEPTION
5646 WHEN OTHERS THEN
5647 log_message('Failure occured in insert_designator');
5648 log_message(sqlerrm);
5649 RAISE;
5650 END insert_designator;
5651
5652 /***********************************************************************
5653 *
5654 * NAME
5655 * process_resource_rows
5656 *
5657 * DESCRIPTION
5658 * This procedure will process al of the resource rows for a step then
5659 * call the insert for resource requirements.
5660 * HISTORY
5661 * M Craig
5662 ************************************************************************/
5663 PROCEDURE process_resource_rows(
5664 pfirst_row IN NUMBER,
5665 plast_row IN NUMBER,
5666 pfound_mtl IN NUMBER,
5667 porgn_id IN PLS_INTEGER,
5668 pinstance_id IN PLS_INTEGER,
5669 pinflate_wip IN NUMBER,
5670 pmin_xfer_qty IN NUMBER)
5671 IS
5672
5673 v_resource_usage PLS_INTEGER := 0;
5674 v_res_seq PLS_INTEGER := 0;
5675 v_schedule_flag PLS_INTEGER := 0;
5676 v_parent_seq_num PLS_INTEGER := 0;
5677 v_rsrc_cnt PLS_INTEGER := 0;
5678 v_start_date DATE := NULL;
5679 v_end_date DATE := NULL;
5680 old_activity PLS_INTEGER := 0;
5681 j PLS_INTEGER := 0;
5682
5683 BEGIN
5684 v_res_seq := 0;
5685 old_activity := -1;
5686
5687 FOR j IN pfirst_row..plast_row
5688 LOOP
5689 /* if the actual completion date is null then the resource
5690 is pending or WIP and needs to be written. otherwise the
5691 resource is completed and does not need to be reported. */
5692
5693 IF old_activity <> rsrc_tab(j).bs_activity_id OR
5694 old_activity = -1 THEN
5695 v_res_seq := v_res_seq + 1;
5696 old_activity := rsrc_tab(j).bs_activity_id;
5697
5698 /* B3421856 , Schedule flag needs to be populated correctly */
5699
5700 IF pfound_mtl = 1 THEN
5701
5702 IF rsrc_tab(j).material_ind = 1 THEN
5703 v_schedule_flag := 4;
5704 ELSE
5705 IF v_schedule_flag < 4 THEN
5706 v_schedule_flag := 3 ;
5707 END IF ;
5708 END IF ;
5709
5710 END IF; /* pfound_mtl */
5711 END IF; /* old_activity */
5712
5713 IF rsrc_tab(j).material_ind = 0 AND pfound_mtl = 1 THEN
5714 rsrc_tab(j).schedule_flag := v_schedule_flag;
5715 END IF;
5716
5717 IF NVL(rsrc_tab(j).actual_cmplt_date,v_null_date) = v_null_date THEN
5718
5719 /* when the actual start is null the resource has not started
5720 and the plan start will be used. */
5721 IF rsrc_tab(j).tran_seq_dep = 1 THEN
5722 v_parent_seq_num := v_res_seq;
5723 v_resource_usage := rsrc_tab(j).resource_usage;
5724 v_start_date := rsrc_tab(j).act_start_date;
5725 v_end_date := rsrc_tab(j).plan_start_date;
5726 ELSE
5727 v_parent_seq_num := TO_NUMBER(NULL);
5728 v_start_date := rsrc_tab(j).plan_start_date;
5729 v_end_date := rsrc_tab(j).plan_cmplt_date;
5730 IF pinflate_wip = 1 THEN
5731 v_resource_usage := rsrc_tab(j).resource_usage / rsrc_tab(j).utl_eff;
5732 ELSE
5733 v_resource_usage := rsrc_tab(j).resource_usage;
5734 END IF;
5735 END IF;
5736
5737 /* If no actual resource exists then the resource has not
5738 started and the planned value will be used */
5739
5740 IF rsrc_tab(j).actual_rsrc_count IS NULL THEN
5741 v_rsrc_cnt := rsrc_tab(j).plan_rsrc_count;
5742 ELSE
5743 v_rsrc_cnt := rsrc_tab(j).actual_rsrc_count;
5744 END IF;
5745
5746 /* write the current resource detail row asscoiating it with the
5747 batch through the product line */
5748
5749 IF v_resource_usage > 0 THEN
5750
5751 /* Bulk Insert for insert_resource_requirements */
5752 rr_index := rr_index + 1 ;
5753 rr_organization_id(rr_index) := porgn_id ;
5754 rr_sr_instance_id(rr_index) := pinstance_id ;
5755 rr_supply_id(rr_index) := rsrc_tab(j).x_batch_id ; /* B1177070 encoded key */
5756 /* B1224660 new value to write resource seq num */
5757 rr_resource_seq_num(rr_index) := v_res_seq ;
5758 rr_resource_id(rr_index) := rsrc_tab(j).x_resource_id ; /* B1177070 encoded key */
5759 rr_start_date(rr_index) := v_start_date ;
5760 rr_end_date(rr_index) := v_end_date ;
5761 rr_opr_hours_required(rr_index) := v_resource_usage ;
5762 rr_assigned_units(rr_index) := v_rsrc_cnt ;
5763 rr_department_id(rr_index) := ((porgn_id * 2) + 1) ; /* B1177070 encoded key */
5764 rr_wip_entity_id(rr_index) := rsrc_tab(j).x_batch_id ; /* B1177070 encoded key */
5765 /* B1224660 write the step number for oper seq num */
5766 rr_operation_seq_num(rr_index) := rsrc_tab(j).batchstep_no ;
5767 rr_firm_flag(rr_index) := rsrc_tab(j).firm_type ;
5768 rr_minimum_transfer_quantity(rr_index) := pmin_xfer_qty ;
5769 rr_parent_seq_num(rr_index) := v_parent_seq_num ;
5770 rr_schedule_flag(rr_index) := rsrc_tab(j).schedule_flag ;
5771 END IF;
5772 END IF;
5773 END LOOP;
5774
5775 END process_resource_rows;
5776
5777 /*Sowmya - As Per latest FDD changes - Start*/
5778 /***********************************************************************
5779 *
5780 * NAME
5781 * production_reservations
5782 *
5783 * DESCRIPTION
5784 * This procedure will fetch all salesorders against which production
5785 * batches are reserved.
5786 * HISTORY
5787 *
5788 ************************************************************************/
5789 /* INVCONV nsinghi Start */
5790 /* ToDo: Need to make the changes */
5791 /* INVCONV nsinghi End */
5792 PROCEDURE production_reservations ( pdblink IN VARCHAR2,
5793 pinstance_id IN PLS_INTEGER,
5794 prun_date IN DATE,
5795 pdelimiter IN VARCHAR2,
5796 return_status IN OUT NOCOPY BOOLEAN)
5797 IS
5798 v_stmt_alt_rsrc VARCHAR2(4000);
5799 BEGIN
5800
5801 v_stmt_alt_rsrc := 'INSERT INTO MSC_ST_RESERVATIONS'
5802 ||' ( '
5803 ||' TRANSACTION_ID , '
5804 ||' INVENTORY_ITEM_ID , '
5805 ||' ORGANIZATION_ID, '
5806 ||' SR_INSTANCE_ID , '
5807 ||' REQUIREMENT_DATE , '
5808 ||' PARENT_DEMAND_ID , '
5809 ||' REVISION , '
5810 ||' DISPOSITION_ID , '
5811 ||' RESERVED_QUANTITY , '
5812 ||' DISPOSITION_TYPE , '
5813 ||' SUBINVENTORY , '
5814 ||' RESERVATION_TYPE , '
5815 ||' DEMAND_CLASS , '
5816 ||' AVAILABLE_TO_MRP , '
5817 ||' RESERVATION_FLAG , '
5818 ||' PROJECT_ID , '
5819 ||' TASK_ID , '
5820 ||' PLANNING_GROUP , '
5821 ||' SUPPLY_SOURCE_HEADER_ID , '
5822 ||' SUPPLY_SOURCE_TYPE_ID , '
5823 ||' DELETED_FLAG '
5824 ||' ) '
5825 ||' SELECT '
5826 ||' ((gbo.batch_res_id * 2) + 1), '
5827 /*Sowmya - INVCONV - Start*/
5828 -- ||' gia.aps_item_id , '
5829 /*Sowmya - INVCONV - Start*/
5830 ||' gbo.organization_id, '
5831 ||' :p1, '
5832 ||' gbo.scheduled_ship_date, '
5833 ||' gbo.so_line_id , '
5834 ||' NULL , '
5835 ||' gbo.order_id , '
5836 ||' gbo.reserved_qty , '
5837 ||' :p2 ,'
5838 ||' NULL , '
5839 ||' :p3 ,'
5840 ||' ool.demand_class_code , '
5841 ||' NULL , '
5842 ||' :p4 ,'
5843 ||' ool.project_id, '
5844 ||' ool.task_id, '
5845 ||' ppp.planning_group, '
5846 ||' ((gbo.batch_id * 2) + 1) , '
5847 ||' :p5 ,'
5848 ||' :p6 '
5849 ||' FROM '
5850 ||' gml_batch_so_reservations'||pdblink||' gbo, '
5851 /*Sowmya - INVCONV - start*/
5852 /* ||' (SELECT '
5853 ||' DISTINCT item_id, aps_item_id, organization_id , whse_code '
5854 ||' FROM gmp_item_aps'||pdblink||') gia, '*/
5855 ||' mtl_system_items'||pdblink||' msi, '
5856 /*Sowmya - INVCONV - End*/
5857 ||' oe_order_lines_all'||pdblink||' ool, '
5858 ||' pjm_project_parameters'||pdblink||' ppp '
5859 ||' WHERE '
5860 /*Sowmya - INVCONV - Start*/
5861 -- ||' gbo.item_id = gia.item_id '
5862 /*doubt : gml_batch_so_reservations does not have inventory item id ?? - to include join
5863 for gml table and msi table*/
5864 /*Sowmya - INVCONV - End*/
5865 ||' AND gbo.organization_id = gia.organization_id '
5866 ||' AND gbo.delete_mark = 0 '
5867 ||' AND gbo.so_line_id = ool.line_id '
5868 ||' AND ool.project_id = ppp.project_id (+) ';
5869
5870 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
5871 v_stmt_alt_rsrc := v_stmt_alt_rsrc
5872 -- ||'
5873 /*Sowmya - INVCONV - Start*/
5874 /* ||' AND EXISTS ( SELECT 1 FROM sy_orgn_mst'||pdblink||' som '
5875 ||' WHERE gia.whse_code = som.resource_whse_code )' ;
5876 */
5877 ||' AND msi.organization_id '|| gmp_calendar_pkg.g_in_str_org;
5878 /*Sowmya - INVCONV - End*/
5879 END IF;
5880
5881 EXECUTE IMMEDIATE v_stmt_alt_rsrc USING
5882 pinstance_id,2,1,2,5,2 ; /*Sowmya - As per latest FDD changes -
5883 Changed the supply source id from 13 to 5 */
5884 EXCEPTION
5885 WHEN OTHERS THEN
5886 log_message('Failure occured during the insert into msc_st_reservations');
5887 log_message(sqlerrm);
5888 return_status := FALSE;
5889
5890 END production_reservations;
5891 /*Sowmya - As Per latest FDD changes - End*/
5892
5893 /***********************************************************************
5894 *
5895 * NAME
5896 * update_last_setup_id
5897 *
5898 * DESCRIPTION
5899 * This procedure is triggered by the concurrent program for
5900 * updating the last setup id.
5901 *
5902 * HISTORY
5903 * Namit 14-09-2004 Procedure Created
5904 ************************************************************************/
5905 /* INVCONV nsinghi Start */
5906 /* ToDo: Need to make the changes */
5907 /* INVCONV nsinghi End */
5908
5909 PROCEDURE update_last_setup_id (
5910 effbuf OUT NOCOPY VARCHAR2,
5911 retcode OUT NOCOPY NUMBER,
5912 f_orgn_code IN NUMBER,
5913 t_orgn_code IN NUMBER
5914 )
5915 IS
5916 TYPE ref_cursor_typ IS REF CURSOR;
5917 cur_lsetup_id ref_cursor_typ;
5918 resources VARCHAR2(30);
5919 v_last_setup_id NUMBER;
5920 v_resource_id NUMBER;
5921 /*Sowmya - INVCONV - Start*/
5922 -- v_plant_code VARCHAR2(10);
5923 v_org_id NUMBER;
5924 /*Sowmya - INVCONV - End*/
5925 v_batch_id NUMBER;
5926 v_instance_id NUMBER;
5927 x_select VARCHAR2(1000);
5928 old_resource_id NUMBER;
5929 old_instance_id NUMBER;
5930 lsetup_updated BOOLEAN;
5931 l_user_id NUMBER;
5932
5933 BEGIN
5934
5935
5936 x_select := NULL;
5937 old_resource_id := -1;
5938 old_instance_id := -1;
5939 lsetup_updated := TRUE;
5940
5941 l_user_id := to_number(FND_PROFILE.VALUE('USER_ID'));
5942
5943 X_select := ' SELECT '
5944 ||' gbsr.sequence_dependent_id, '
5945 ||' crd.resource_id, '
5946 ||' grt.instance_id, '
5947 /*Sowmya - INVCONV - Start*/
5948 -- ||' crd.orgn_code, '
5949 ||' crd.organization_id, '
5950 /*Sowmya - INVCONV - End*/
5951 ||' gbsr.batch_id '
5952 ||' FROM gme_batch_step_resources gbsr, '
5953 ||' gme_resource_txns grt, '
5954 /*Sowmya - INVCONV - Start*/
5955 /*doubt - which table should be used to fetch the current user id*/
5956 -- ||' sy_orgn_usr sou, '
5957 /*Sowmya - INVCONV - End*/
5958 ||' cr_rsrc_dtl crd, '
5959 ||' gme_batch_header gbh, '
5960 ||' mtl_parameters mp ' /* sowmya added to pick the organization code in the
5961 concurrent pgm*/
5962 ||' WHERE gbsr.batch_id = grt.doc_id '
5963 ||' AND gbh.batch_id = gbsr.batch_id '
5964 /*Sowmya - INVCONV - Start*/
5965 /* ||' AND gbh.plant_code = crd.orgn_code '
5966 ||' AND crd.orgn_code = sou.orgn_code '
5967 ||' AND sou.user_id = :user_id ' */
5968 ||' AND gbh.organization_id = crd.organization_id '
5969 ||' AND mp.organization_id = crd.organization_id '
5970 /* doubt - complete the join the for the user tables*/
5971 /*Sowmya - INVCONV - End*/
5972 ||' AND gbsr.batchstep_resource_id = grt.line_id '
5973 ||' AND grt.completed_ind = 1 '
5974 ||' AND crd.resources = gbsr.resources '
5975 ||' AND crd.resources = grt.resources '
5976 ||' AND crd.schedule_ind = 2 '
5977 ||' AND grt.instance_id IS NOT NULL '
5978 ||' AND crd.delete_mark = 0 ';
5979 IF f_orgn_code IS NOT NULL THEN
5980 x_select := x_select
5981 /*Sowmya - INVCONV - Start*/
5982 -- ||' AND crd.orgn_code >= :frm_orgn ' ;
5983 ||' AND mp.organization_id >= :frm_orgn ' ;
5984 END IF;
5985 IF t_orgn_code IS NOT NULL THEN
5986 x_select := x_select
5987 -- ||' AND crd.orgn_code <= :to_orgn ' ;
5988 ||' AND mp.organization_id <= :to_orgn ' ;
5989 /*Sowmya - INVCONV - End*/
5990 END IF;
5991 x_select := x_select
5992 ||' ORDER BY grt.resources, grt.instance_id, '
5993 ||' grt.end_date DESC, grt.poc_trans_id ' ;
5994
5995 IF f_orgn_code IS NOT NULL AND t_orgn_code IS NOT NULL THEN
5996 OPEN cur_lsetup_id FOR x_select USING /*l_user_id,*/ f_orgn_code, t_orgn_code;
5997 ELSIF f_orgn_code IS NOT NULL AND t_orgn_code IS NULL THEN
5998 OPEN cur_lsetup_id FOR x_select USING /*l_user_id,*/ f_orgn_code;
5999 ELSIF f_orgn_code IS NULL AND t_orgn_code IS NOT NULL THEN
6000 OPEN cur_lsetup_id FOR x_select USING /*l_user_id,*/ t_orgn_code;
6001 ELSE
6002 OPEN cur_lsetup_id FOR x_select /*USING l_user_id*/;
6003 END IF;
6004
6005 LOOP
6006 FETCH cur_lsetup_id INTO v_last_setup_id, v_resource_id, v_instance_id,
6007 -- v_plant_code, v_batch_id;
6008 /*Sowmya - INVCONV - Start*/
6009 v_org_id, v_batch_id;
6010 /*Sowmya - INVCONV - End*/
6011 EXIT WHEN cur_lsetup_id%NOTFOUND;
6012
6013 IF (old_resource_id <> v_resource_id OR old_instance_id <> v_instance_id) THEN
6014 old_resource_id := v_resource_id;
6015 old_instance_id := v_instance_id;
6016 lsetup_updated := FALSE;
6017 END IF;
6018
6019 IF NOT (lsetup_updated) THEN
6020 lsetup_updated := TRUE;
6021 -- IF v_last_setup_id IS NOT NULL
6022 -- THEN
6023 UPDATE
6024 gmp_resource_instances gri
6025 SET gri.last_setup_id = v_last_setup_id
6026
6027 WHERE
6028 gri.resource_id = v_resource_id
6029 AND gri.instance_id = v_instance_id;
6030 -- END IF;
6031 END IF;
6032 END LOOP;
6033 CLOSE cur_lsetup_id ;
6034 COMMIT;
6035 EXCEPTION
6036 WHEN NO_DATA_FOUND THEN
6037 log_message(' NO_DATA_FOUND exception raised in Procedure: gmp_aps_ds_pull.update_last_setup_id ' );
6038 RAISE;
6039
6040 WHEN OTHERS THEN
6041 log_message('Error in Last Setup ID Program: '||SQLERRM);
6042 RAISE;
6043
6044 END update_last_setup_id;
6045
6046 /*
6047 REM+=========================================================================+
6048 REM| FUNCTION NAME |
6049 REM| gmp_debug_message |
6050 REM| DESCRIPTION |
6051 REM| This procedure is created to enable more debug messages |
6052 REM| HISTORY |
6053 REM| Vpedarla Bug: 8420747 created this procedure |
6054 REM+=========================================================================+
6055 */
6056 PROCEDURE gmp_debug_message(pBUFF IN VARCHAR2) IS
6057 BEGIN
6058 IF (l_debug = 'Y') then
6059 LOG_MESSAGE(pBUFF);
6060 END IF;
6061 END gmp_debug_message;
6062
6063 END gmp_aps_ds_pull;