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