[Home] [Help]
PACKAGE BODY: APPS.GME_GANTT_PKG
Source
1 PACKAGE BODY gme_gantt_pkg AS
2 /* $Header: GMEGNTB.pls 120.11.12020000.3 2012/09/20 03:34:08 mtou ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_GANTT_PKG';
4 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
5 g_bulk_fetch_limit NUMBER := 45;
6 g_product_no VARCHAR2 (32);
7 g_ingredient_no VARCHAR2 (32);
8 g_org_code VARCHAR2(3);
9 g_organization_id NUMBER (10);
10 g_mfg_calendar_code VARCHAR2 (10);
11 g_calendar_desc VARCHAR2 (240);
12 g_calendar_start DATE;
13 g_calendar_end DATE;
14
15
16 CURSOR Get_Batches_From_Date IS
17 Select DISTINCT
18 BATCH_ID ,
19 BATCH_NO ,
20 BATCH_STATUS ,
21 BATCH_TYPE ,
22 START_DATE ,
23 END_DATE ,
24 PLAN_START_DATE ,
25 PLAN_CMPLT_DATE ,
26 ACTUAL_START ,
27 ACTUAL_END ,
28 FORMULA_ID ,
29 ROUTING_ID ,
30 ENFORCE_STEP_DEPENDENCY,
31 TERMINATED_IND ,
32 FINITE_SCHEDULED_IND ,
33 PARENTLINE_ID
34 from GME_GANTT_GTMP
35 order by START_DATE;
36
37 -- CURSOR get_batches_from_date (
38 -- p_organization_id NUMBER
39 -- ,p_batch_no VARCHAR2
40 -- ,p_to_batch_no VARCHAR2 --Bug#4867640
41 -- ,p_batch_type NUMBER
42 -- ,p_pending_status NUMBER
43 -- ,p_released_status NUMBER
44 -- ,p_certified_status NUMBER
45 -- ,p_fpo_type NUMBER
46 -- ,p_from_date DATE
47 -- ,p_resource VARCHAR2
48 -- ,p_prim_rsrc_ind NUMBER
49 -- ,p_sec_rsrc_ind NUMBER
50 -- ,p_aux_rsrc_ind NUMBER)
51 -- IS
52 -- SELECT b.batch_id, b.batch_no, batch_status, batch_type
53 -- ,
54 -- --Bug3315440
55 -- DECODE (batch_status
56 -- ,1, date_to_clientdt (plan_start_date)
57 -- ,date_to_clientdt (actual_start_date) ) start_date
58 -- ,DECODE (batch_status
59 -- ,3, date_to_clientdt (actual_cmplt_date)
60 -- ,date_to_clientdt (plan_cmplt_date) ) end_date
61 -- ,date_to_clientdt (plan_start_date)
62 -- ,date_to_clientdt (plan_cmplt_date)
63 -- ,DECODE (date_to_clientdt (actual_start_date)
64 -- ,NULL, date_to_clientdt (plan_start_date)
65 -- ,date_to_clientdt (actual_start_date) ) actual_start
66 -- ,DECODE (date_to_clientdt (actual_cmplt_date)
67 -- ,NULL, date_to_clientdt (plan_cmplt_date)
68 -- ,date_to_clientdt (actual_cmplt_date) ) actual_end
69 -- ,NVL(b.formula_id,0), NVL (routing_id, 0)
70 -- ,NVL (enforce_step_dependency, 0), NVL (terminated_ind, 0)
71 -- ,
72 -- --Bug#2479773 Swapna Kommineni 02-FEB-2005
73 -- NVL (finite_scheduled_ind, 0),
74 -- NVL(parentline_id,0) --Bug#5550337
75 -- FROM gme_batch_header b
76 -- WHERE organization_id = p_organization_id
77 -- --Bug#4867640 check for batch_no within the given range
78 -- AND ((p_to_batch_no IS NULL AND batch_no LIKE p_batch_no) OR
79 -- (lpad(batch_no,32,'0') >= lpad(p_batch_no,32,'0') AND
80 -- lpad(batch_no,32,'0') <= lpad(p_to_batch_no,32,'0') )
81 -- )
82 -- AND ( ( batch_type = p_batch_type
83 -- AND ( batch_status = p_pending_status
84 -- OR batch_status = p_released_status
85 -- OR batch_status = p_certified_status) )
86 -- OR (batch_type = p_fpo_type AND batch_status = 1) )
87 -- AND ( ( batch_status = 1
88 -- AND ( plan_start_date >= p_from_date
89 -- OR plan_cmplt_date >= p_from_date) )
90 -- OR ( batch_status = 2
91 -- AND ( actual_start_date >= p_from_date
92 -- OR plan_cmplt_date >= p_from_date) )
93 -- OR ( batch_status = 3
94 -- AND ( actual_start_date >= p_from_date
95 -- OR actual_cmplt_date >= p_from_date) ) )
96 -- AND ( ( p_resource = '%'
97 -- AND p_prim_rsrc_ind = 1
98 -- AND p_aux_rsrc_ind = 2
99 -- AND p_sec_rsrc_ind = 0)
100 -- OR (EXISTS (
101 -- SELECT 1
102 -- FROM gme_batch_step_resources d2
103 -- WHERE d2.batch_id = b.batch_id
104 -- AND d2.resources LIKE p_resource
105 -- AND ( d2.prim_rsrc_ind = p_prim_rsrc_ind
106 -- OR d2.prim_rsrc_ind = p_aux_rsrc_ind
107 -- OR d2.prim_rsrc_ind = p_sec_rsrc_ind) ) ) )
108 -- ORDER BY 5;
109
110 CURSOR GET_BATCHES_FROM_TO_DATE IS
111 Select DISTINCT
112 BATCH_ID ,
113 BATCH_NO ,
114 BATCH_STATUS ,
115 BATCH_TYPE ,
116 START_DATE ,
117 END_DATE ,
118 PLAN_START_DATE ,
119 PLAN_CMPLT_DATE ,
120 ACTUAL_START ,
121 ACTUAL_END ,
122 FORMULA_ID ,
123 ROUTING_ID ,
124 ENFORCE_STEP_DEPENDENCY,
125 TERMINATED_IND ,
126 FINITE_SCHEDULED_IND ,
127 PARENTLINE_ID
128 from GME_GANTT_GTMP
129 order by START_DATE;
130
131 -- CURSOR get_batches_from_to_date (
132 -- p_organization_id NUMBER
133 -- ,p_batch_no VARCHAR2
134 -- ,p_to_batch_no VARCHAR2 --Bug#4867640
135 -- ,p_batch_type NUMBER
136 -- ,p_pending_status NUMBER
137 -- ,p_released_status NUMBER
138 -- ,p_certified_status NUMBER
139 -- ,p_fpo_type NUMBER
140 -- ,p_from_date DATE
141 -- ,p_to_date DATE
142 -- ,p_resource VARCHAR2
143 -- ,p_prim_rsrc_ind NUMBER
144 -- ,p_sec_rsrc_ind NUMBER
145 -- ,p_aux_rsrc_ind NUMBER)
146 -- IS
147 -- SELECT b.batch_id, batch_no, batch_status, batch_type
148 -- ,
149 -- --Bug3315440
150 -- DECODE (batch_status
151 -- ,1, date_to_clientdt (plan_start_date)
152 -- ,date_to_clientdt (actual_start_date) ) start_date
153 -- ,DECODE (batch_status
154 -- ,3, date_to_clientdt (actual_cmplt_date)
155 -- ,date_to_clientdt (plan_cmplt_date) ) end_date
156 -- ,date_to_clientdt (plan_start_date)
157 -- ,date_to_clientdt (plan_cmplt_date)
158 -- ,DECODE (date_to_clientdt (actual_start_date)
159 -- ,NULL, date_to_clientdt (plan_start_date)
160 -- ,date_to_clientdt (actual_start_date) ) actual_start
161 -- ,DECODE (date_to_clientdt (actual_cmplt_date)
162 -- ,NULL, date_to_clientdt (plan_cmplt_date)
163 -- ,date_to_clientdt (actual_cmplt_date) ) actual_end
164 -- ,NVL(formula_id,0), NVL (routing_id, 0)
165 -- ,NVL (enforce_step_dependency, 0), NVL (terminated_ind, 0)
166 -- ,
167 -- --Bug#2479773 Swapna Kommineni 02-FEB-2005
168 -- NVL (finite_scheduled_ind, 0),
169 -- NVL(parentline_id,0) --Bug#5550337
170 -- FROM gme_batch_header b
171 -- WHERE organization_id = p_organization_id
172 -- AND ((p_to_batch_no IS NULL AND batch_no LIKE p_batch_no) OR
173 -- (lpad(batch_no,32,'0') >= lpad(p_batch_no,32,'0') AND
174 -- lpad(batch_no,32,'0') <= lpad(p_to_batch_no,32,'0') )
175 -- )
176 -- AND ( ( batch_type = p_batch_type
177 -- AND ( batch_status = p_pending_status
178 -- OR batch_status = p_released_status
179 -- OR batch_status = p_certified_status) )
180 -- OR (batch_type = p_fpo_type AND batch_status = 1) )
181 -- AND ( ( batch_status = 1
182 -- AND ( ( plan_start_date >= p_from_date
183 -- AND plan_start_date <= p_to_date)
184 -- OR ( plan_cmplt_date >= p_from_date
185 -- AND plan_start_date <= p_to_date) ) )
186 -- OR ( batch_status = 2
187 -- AND ( ( actual_start_date >= p_from_date
188 -- AND actual_start_date <= p_to_date)
189 -- OR ( plan_cmplt_date >= p_from_date
190 -- AND actual_start_date <= p_to_date) ) )
191 -- OR ( batch_status = 3
192 -- AND ( ( actual_start_date >= p_from_date
193 -- AND actual_start_date <= p_to_date)
194 -- OR ( actual_cmplt_date >= p_from_date
195 -- AND actual_start_date <= p_to_date) ) ) )
196 -- AND ( ( p_resource = '%'
197 -- AND p_prim_rsrc_ind = 1
198 -- AND p_aux_rsrc_ind = 2
199 -- AND p_sec_rsrc_ind = 0)
200 -- OR (EXISTS (
201 -- SELECT 1
202 -- FROM gme_batch_step_resources d2
203 -- WHERE d2.batch_id = b.batch_id
204 -- AND d2.resources LIKE p_resource
205 -- AND ( d2.prim_rsrc_ind = p_prim_rsrc_ind
206 -- OR d2.prim_rsrc_ind = p_aux_rsrc_ind
207 -- OR d2.prim_rsrc_ind = p_sec_rsrc_ind) ) ) )
208 -- ORDER BY 5;
209
210 /* Moved these cursors to global space, as these are used in
211 multiple procedures */
212 CURSOR get_product_info (p_batch_id NUMBER)
213 IS
214 SELECT i.inventory_item_id, i.concatenated_segments, i.description
215 FROM mtl_system_items_kfv i
216 ,gme_batch_header b
217 ,gmd_recipe_validity_rules v
218 WHERE b.batch_id = p_batch_id
219 AND b.recipe_validity_rule_id = v.recipe_validity_rule_id
220 AND v.inventory_item_id = i.inventory_item_id
221 AND b.organization_id = i.organization_id;
222 /* This cursor is defined for the LCF batch */
223 CURSOR get_product_info_lcf (p_batch_id NUMBER)
224 IS
225 SELECT i.inventory_item_id, i.concatenated_segments, i.description
226 FROM mtl_system_items_kfv i
227 ,gme_batch_header b
228 ,gme_material_details m
229 WHERE b.batch_id = p_batch_id
230 AND b.batch_id = m.batch_id
231 AND m.organization_id = i.organization_id
232 AND m.inventory_item_id = i.inventory_item_id
233 AND m.line_type = 1
234 AND m.line_no = 1;
235
236 CURSOR get_product_qty (p_batch_id NUMBER, p_inventory_item_id NUMBER)
237 IS
238 SELECT SUM (plan_qty), NVL (SUM (actual_qty), 0), dtl_um
239 FROM gme_material_details
240 WHERE batch_id = p_batch_id
241 AND line_type = 1
242 AND inventory_item_id = p_inventory_item_id
243 GROUP BY dtl_um;
244
245
246 /*======================================================================
247 # Return the given string padded on the right with blank characters.
248 #======================================================================*/
249 FUNCTION r_pad (p_str VARCHAR2, p_display_length INTEGER)
250 RETURN VARCHAR2
251 IS
252 l_actual_length INTEGER;
253 l_api_name CONSTANT VARCHAR2 (30) := 'R_PAD';
254 BEGIN
255 IF p_str IS NULL THEN
256 RETURN RPAD (' ', p_display_length);
257 END IF;
258
259 l_actual_length := LENGTH (RPAD (p_str, p_display_length) );
260
261 IF (l_actual_length < p_display_length) THEN
262 l_actual_length :=
263 p_display_length
264 + (p_display_length - l_actual_length);
265 END IF;
266
267 RETURN RPAD (p_str, l_actual_length);
268 --Bug2804440
269 EXCEPTION
270 WHEN OTHERS THEN
271 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
272 --End Bug2804440
273 END r_pad;
274
275 FUNCTION check_batch(p_batch_type IN INTEGER ) RETURN BOOLEAN AS
276 BEGIN
277 IF p_batch_type = 0 THEN
278 RETURN TRUE;
279 ELSE
280 RETURN FALSE;
281 END IF;
282 END check_batch;
283
284 FUNCTION check_fpo(p_fpo_type IN INTEGER ) RETURN BOOLEAN AS
285 BEGIN
286 IF p_fpo_type = 10 THEN
287 RETURN TRUE;
288 ELSE
289 RETURN FALSE;
290 END IF;
291 END check_fpo;
292
293 FUNCTION check_pending_status(p_pending_status IN INTEGER ) RETURN BOOLEAN AS
294 BEGIN
295 IF p_pending_status = 1 THEN
296 RETURN TRUE;
297 ELSE
298 RETURN FALSE;
299 END IF;
300 END check_pending_status;
301
302 FUNCTION check_released_status(p_released_status IN INTEGER ) RETURN BOOLEAN AS
303 BEGIN
304 IF p_released_status = 2 THEN
305 RETURN TRUE;
306 ELSE
307 RETURN FALSE;
308 END IF;
309 END check_released_status;
310
311 FUNCTION check_certified_status(p_certified_status IN INTEGER ) RETURN BOOLEAN AS
312 BEGIN
313 IF p_certified_status = 3 THEN
314 RETURN TRUE;
315 ELSE
316 RETURN FALSE;
317 END IF;
318 END check_certified_status;
319
320 PROCEDURE set_where_clause(p_batch_type IN INTEGER ,
321 p_fpo_type IN INTEGER,
322 p_pending_status IN INTEGER,
323 p_released_status IN INTEGER,
324 p_certified_status IN INTEGER,
325 p_from_date IN DATE,
326 p_to_date IN DATE DEFAULT NULL,
327 p_to_batch_no IN VARCHAR2 DEFAULT NULL,
328 p_string_to_exec IN OUT NOCOPY DBMS_SQL.VARCHAR2S) AS
329
330 l_query_count NUMBER := 0;
331
332 l_column_select_str1 VARCHAR2(200) := ' SELECT B.BATCH_ID BATCH_ID, B.BATCH_NO BATCH_NO, BATCH_STATUS, BATCH_TYPE, PLAN_START_DATE, ACTUAL_START_DATE, ';
333 l_column_select_str2 VARCHAR2(200) := ' ACTUAL_CMPLT_DATE, PLAN_CMPLT_DATE, B.FORMULA_ID FORMULA_ID, NVL(ROUTING_ID, 0) ROUTING_ID, NVL(ENFORCE_STEP_DEPENDENCY, 0) ENFORCE_STEP_DEPENDENCY, ';
334 l_column_select_str3 VARCHAR2(200) := ' NVL(TERMINATED_IND, 0) TERMINATED_IND , NVL(FINITE_SCHEDULED_IND, 0) FINITE_SCHEDULED_IND, NVL(PARENTLINE_ID,0) PARENTLINE_ID FROM GME_BATCH_HEADER B ';
335 l_column_select_str4 VARCHAR2(200) := ' WHERE organization_id = :p_organization_id AND ';
336 l_column_select_str5 VARCHAR2(200) := NULL;
337 BEGIN
338 IF p_to_batch_no IS NULL THEN
339 l_column_select_str5 := ' BATCH_NO LIKE :p_batch_no ';
340 ELSE
341 l_column_select_str5 := ' (lpad(batch_no,32,''0'') >= lpad(:p_batch_no,32,''0'') AND lpad(batch_no,32,''0'') <= lpad(:p_to_batch_no,32,''0'')) ';
342 END IF;
343
344 IF p_to_date IS NULL THEN
345 -- Process Batch
346 IF check_batch(p_batch_type) THEN
347 -- process pending status
348 IF check_pending_status(p_pending_status) THEN
349 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str1;
350 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
351 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
352 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
353 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
354 l_query_count := l_query_count + 1;
355 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 1 AND PLAN_START_DATE >= :p_from_date ';
356
357 -- Add the plan_comp_date check here if the released status is not chosen
358 -- else add it later.
359 IF NOT check_released_status(p_released_status) THEN
360 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
361 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
362 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
363 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
364 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
365 l_query_count := l_query_count + 1;
366 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 1 AND PLAN_CMPLT_DATE >= :p_from_date ';
367 END IF;
368 END IF;
369
370 -- process released status
371 IF check_released_status(p_released_status) THEN
372 -- if pending status was not chosen
373 IF NOT check_pending_status(p_pending_status) THEN
374 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str1;
375 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
376 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
377 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
378 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
379 l_query_count := l_query_count + 1;
380 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 2 AND PLAN_CMPLT_DATE >= :p_from_date ';
381 ELSE
382 --pending status was also chosen
383 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
384 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
385 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
386 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
387 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
388 l_query_count := l_query_count + 1;
389 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS IN (1,2) AND PLAN_CMPLT_DATE >= :p_from_date ';
390 END IF;
391
392 -- Add the actual start date check here if the certified status is not chosen
393 -- else add it later.
394 IF NOT check_certified_status(p_certified_status) THEN
395 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
396 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
397 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
398 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
399 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
400 l_query_count := l_query_count + 1;
401 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 2 AND ACTUAL_START_DATE >= :p_from_date ';
402 END IF;
403 END IF;
404
405 -- process certified status
406 IF check_certified_status(p_certified_status) THEN
407 -- if released status was not chosen
408 IF NOT check_released_status(p_released_status) THEN
409 -- no query so far
410 IF l_query_count = 0 THEN
411 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str1;
412 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
413 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
414 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
415 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
416 -- prior query so add union all
417 ELSE
418 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
419 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
420 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
421 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
422 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
423 END IF;
424
425 l_query_count := l_query_count + 1;
426 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 3 AND ACTUAL_START_DATE >= :p_from_date ';
427 ELSE
428 --released status was also chosen
429 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
430 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
431 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
432 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
433 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
434 l_query_count := l_query_count + 1;
435 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS IN (2,3) AND ACTUAL_START_DATE >= :p_from_date ';
436 END IF;
437
438 -- Add the actual completion date check
439 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
440 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
441 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
442 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
443 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
444 l_query_count := l_query_count + 1;
445 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 3 AND ACTUAL_CMPLT_DATE >= :p_from_date ';
446 END IF;
447 END IF;
448
449 -- process FPO
450 IF check_fpo(p_fpo_type) THEN
451 IF l_query_count = 0 THEN
452 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str1;
453 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
454 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
455 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
456 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
457 ELSE
458 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
459 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
460 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
461 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
462 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
463 END IF;
464
465 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 10 AND BATCH_STATUS = 1 AND PLAN_START_DATE >= :p_from_date ';
466
467 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
468 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
469 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
470 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
471 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
472
473 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 10 AND BATCH_STATUS = 1 AND PLAN_CMPLT_DATE >= :p_from_date ';
474 END IF;
475 ELSE
476 -- Process Batch
477 IF check_batch(p_batch_type) THEN
478 -- process pending status
479 IF check_pending_status(p_pending_status) THEN
480 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str1;
481 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
482 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
483 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
484 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
485
486 l_query_count := l_query_count + 1;
487 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 1 AND PLAN_START_DATE >= :p_from_date AND PLAN_START_DATE <= :p_to_date ';
488
489 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
490 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
491 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
492 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
493 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
494
495 l_query_count := l_query_count + 1;
496 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 1 AND PLAN_CMPLT_DATE >= :p_from_date AND PLAN_START_DATE <= :p_to_date ';
497 END IF;
498
499 -- process released status
500 IF check_released_status(p_released_status) THEN
501 -- if pending status was not chosen
502 IF l_query_count = 0 THEN
503 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str1;
504 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
505 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
506 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
507 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
508 ELSE
509 --pending status was also chosen
510 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
511 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
512 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
513 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
514 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
515 END IF;
516
517 l_query_count := l_query_count + 1;
518 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 2 AND PLAN_CMPLT_DATE >= :p_from_date AND ACTUAL_START_DATE <= :p_to_date ';
519
520 -- Add the actual start date check here if the certified status is not chosen
521 -- else add it later.
522 IF NOT check_certified_status(p_certified_status) THEN
523 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
524 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
525 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
526 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
527 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
528 l_query_count := l_query_count + 1;
529 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 2 AND ACTUAL_START_DATE >= :p_from_date AND ACTUAL_START_DATE <= :p_to_date ';
530 END IF;
531 END IF;
532
533 -- process certified status
534 IF check_certified_status(p_certified_status) THEN
535 -- if released status was not chosen
536 IF NOT check_released_status(p_released_status) THEN
537 -- no query so far
538 IF l_query_count = 0 THEN
539 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str1;
540 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
541 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
542 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
543 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
544 -- prior query so add union all
545 ELSE
546 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
547 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
548 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
549 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
550 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
551 END IF;
552
553 l_query_count := l_query_count + 1;
554 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 3 AND ACTUAL_START_DATE >= :p_from_date AND ACTUAL_START_DATE <= :p_to_date ';
555 ELSE
556 --released status was also chosen
557 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
558 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
559 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
560 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
561 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
562
563 l_query_count := l_query_count + 1;
564 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS IN (2,3) AND ACTUAL_START_DATE >= :p_from_date AND ACTUAL_START_DATE <= :p_to_date ';
565 END IF;
566
567 -- Add the actual completion date check
568 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
569 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
570 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
571 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
572 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
573
574 l_query_count := l_query_count + 1;
575 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 0 AND BATCH_STATUS = 3 AND ACTUAL_CMPLT_DATE >= :p_from_date AND ACTUAL_START_DATE <= :p_to_date ';
576 END IF;
577 END IF;
578
579 -- process FPO
580 IF check_fpo(p_fpo_type) THEN
581 IF l_query_count = 0 THEN
582 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str1;
583 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
584 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
585 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
586 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
587 ELSE
588 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
589 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
590 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
591 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
592 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
593 END IF;
594
595 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 10 AND BATCH_STATUS = 1 AND PLAN_START_DATE >= :p_from_date AND PLAN_START_DATE <= :p_to_date ';
596
597 p_string_to_exec(p_string_to_exec.LAST+1) := 'UNION ALL '|| l_column_select_str1;
598 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str2;
599 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str3;
600 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str4;
601 p_string_to_exec(p_string_to_exec.LAST+1) := l_column_select_str5;
602
603 p_string_to_exec(p_string_to_exec.LAST+1) := ' AND BATCH_TYPE = 10 AND BATCH_STATUS = 1 AND PLAN_CMPLT_DATE >= :p_from_date AND PLAN_START_DATE <= :p_to_date ';
604 END IF;
605 END IF;
606 END set_where_clause;
607
608 PROCEDURE init_session (
609 p_organization_id IN NUMBER
610 ,p_org_code IN VARCHAR2
611 ,p_batch_no IN VARCHAR2
612 ,p_to_batch_no IN VARCHAR2 --Bug#4867640
613 ,p_from_date IN DATE
614 ,p_to_date IN DATE
615 ,p_resource IN VARCHAR2
616 ,p_product_no IN VARCHAR2
617 ,p_ingredient_no IN VARCHAR2
618 ,p_prim_rsrc_ind IN INTEGER
619 ,p_sec_rsrc_ind IN INTEGER
620 ,p_aux_rsrc_ind IN INTEGER
621 ,p_batch_type IN INTEGER
622 ,p_fpo_type IN INTEGER
623 ,p_released_status IN INTEGER
624 ,p_pending_status IN INTEGER
625 ,p_certified_status IN INTEGER)
626 IS
627 l_api_name CONSTANT VARCHAR2 (30) := 'INIT_SESSION';
628
629 string_to_exec DBMS_SQL.VARCHAR2S;
630 dynamic_cur PLS_INTEGER := dbms_sql.open_cursor;
631 l_ret NUMBER;
632 BEGIN
633 GME_DEBUG.LOG_INITIALIZE('ProdScheduler');
634
635 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
636 gme_debug.put_line('Inside init session ');
637 END IF;
638 close_cursors;
639
640 /*
641 IF p_to_date IS NULL THEN
642 OPEN get_batches_from_date (p_organization_id
643 ,p_batch_no
644 ,p_to_batch_no --Bug#4867640
645 ,p_batch_type
646 ,p_pending_status
647 ,p_released_status
648 ,p_certified_status
649 ,p_fpo_type
650 ,p_from_date
651 ,p_resource
652 ,p_prim_rsrc_ind
653 ,p_sec_rsrc_ind
654 ,p_aux_rsrc_ind);
655 ELSE
656 OPEN get_batches_from_to_date (p_organization_id
657 ,p_batch_no
658 ,p_to_batch_no --Bug#4867640
659 ,p_batch_type
660 ,p_pending_status
661 ,p_released_status
662 ,p_certified_status
663 ,p_fpo_type
664 ,p_from_date
665 ,p_to_date
666 ,p_resource
667 ,p_prim_rsrc_ind
668 ,p_sec_rsrc_ind
669 ,p_aux_rsrc_ind);
670 END IF;
671 */
672
673 DELETE FROM GME_GANTT_GTMP;
674
675 string_to_exec(1) := 'INSERT INTO GME_GANTT_GTMP ';
676 string_to_exec(2) := '( SELECT BATCH_ID, BATCH_NO, BATCH_STATUS, BATCH_TYPE, ';
677 string_to_exec(3) := ' DECODE(BATCH_STATUS,1, GME_GANTT_PKG.DATE_TO_CLIENTDT(PLAN_START_DATE), GME_GANTT_PKG.DATE_TO_CLIENTDT(ACTUAL_START_DATE)) START_DATE, ';
678 string_to_exec(4) := ' DECODE(BATCH_STATUS,3, GME_GANTT_PKG.DATE_TO_CLIENTDT(ACTUAL_CMPLT_DATE), GME_GANTT_PKG.DATE_TO_CLIENTDT(PLAN_CMPLT_DATE)) END_DATE, ';
679 string_to_exec(5) := ' GME_GANTT_PKG.DATE_TO_CLIENTDT(PLAN_START_DATE), GME_GANTT_PKG.DATE_TO_CLIENTDT(PLAN_CMPLT_DATE), ';
680 string_to_exec(6) := ' DECODE(GME_GANTT_PKG.DATE_TO_CLIENTDT(ACTUAL_START_DATE), NULL, GME_GANTT_PKG.DATE_TO_CLIENTDT(PLAN_START_DATE), GME_GANTT_PKG.DATE_TO_CLIENTDT(ACTUAL_START_DATE)) ACTUAL_START, ';
681 string_to_exec(7) := ' DECODE(GME_GANTT_PKG.DATE_TO_CLIENTDT(ACTUAL_CMPLT_DATE), NULL , GME_GANTT_PKG.DATE_TO_CLIENTDT(PLAN_CMPLT_DATE), GME_GANTT_PKG.DATE_TO_CLIENTDT(ACTUAL_CMPLT_DATE)) ACTUAL_END, ';
682 string_to_exec(8) := ' FORMULA_ID, ROUTING_ID, ENFORCE_STEP_DEPENDENCY, TERMINATED_IND, FINITE_SCHEDULED_IND, PARENTLINE_ID FROM (';
683
684 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
685 gme_debug.put_line('Calling set_where_clause');
686 END IF;
687
688 set_where_clause(p_batch_type => p_batch_type,
689 p_fpo_type => p_fpo_type,
690 p_pending_status => p_pending_status,
691 p_released_status => p_released_status,
692 p_certified_status => p_certified_status,
693 p_from_date => p_from_date,
694 p_to_date => p_to_date,
695 p_to_batch_no => p_to_batch_no,
696 p_string_to_exec => string_to_exec);
697
698 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
699 gme_debug.put_line('After executing set_where_clause');
700 END IF;
701
702 string_to_exec(string_to_exec.LAST +1) := ' ) A WHERE ( (:p_resource = ''%'' AND :p_prim_rsrc_ind = 1 AND :p_aux_rsrc_ind = 2 ';
703 string_to_exec(string_to_exec.LAST +1) := ' AND :p_sec_rsrc_ind = 0) OR (EXISTS (SELECT 1 FROM GME_BATCH_STEP_RESOURCES D2 ';
704 string_to_exec(string_to_exec.LAST +1) := ' WHERE D2.BATCH_ID = A.BATCH_ID AND D2.RESOURCES LIKE :p_resource AND (D2.PRIM_RSRC_IND = ';
705 string_to_exec(string_to_exec.LAST +1) := ' :p_prim_rsrc_ind OR D2.PRIM_RSRC_IND = :p_aux_rsrc_ind OR D2.PRIM_RSRC_IND = :p_sec_rsrc_ind )))))';
706
707 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
708 gme_debug.put_line('Dynamic SQL query is ');
709 gme_debug.put_line('===============================================================');
710 for cntr in 1..string_to_exec.last loop
711 gme_debug.put_line(string_to_exec(cntr));
712 end loop;
713 gme_debug.put_line('===============================================================');
714 END IF;
715
716 DBMS_SQL.PARSE(dynamic_cur, string_to_exec, string_to_exec.first, string_to_exec.last, TRUE, DBMS_SQL.NATIVE);
717
718 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
719 gme_debug.put_line('After parsing dynamic SQL query');
720 END IF;
721
722 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
723 gme_debug.put_line('Bind variable values');
724 gme_debug.put_line('-------------------------------------------');
725 gme_debug.put_line('p_organization_id = '|| p_organization_id);
726 gme_debug.put_line('p_batch_no = '|| p_batch_no);
727 gme_debug.put_line('p_to_batch_no = '|| p_to_batch_no);
728 gme_debug.put_line('p_from_date = '|| to_char(p_from_date, 'DD-MON-YYYY HH24:MI:SS'));
729 gme_debug.put_line('p_resource = '|| p_resource);
730 gme_debug.put_line('p_prim_rsrc_ind = '|| p_prim_rsrc_ind);
731 gme_debug.put_line('p_aux_rsrc_ind = '|| p_aux_rsrc_ind);
732 gme_debug.put_line('p_sec_rsrc_ind = '|| p_sec_rsrc_ind);
733 END IF;
734
735 DBMS_SQL.BIND_VARIABLE(dynamic_cur, ':p_organization_id' , p_organization_id);
736 DBMS_SQL.BIND_VARIABLE(dynamic_cur, ':p_batch_no' , p_batch_no);
737 IF p_to_batch_no IS NOT NULL THEN
738 DBMS_SQL.BIND_VARIABLE(dynamic_cur, ':p_to_batch_no' , p_to_batch_no);
739 END IF;
740 DBMS_SQL.BIND_VARIABLE(dynamic_cur, ':p_from_date' , p_from_date);
741 DBMS_SQL.BIND_VARIABLE(dynamic_cur, ':p_resource' , p_resource);
742 DBMS_SQL.BIND_VARIABLE(dynamic_cur, ':p_prim_rsrc_ind' , p_prim_rsrc_ind);
743 DBMS_SQL.BIND_VARIABLE(dynamic_cur, ':p_aux_rsrc_ind' , p_aux_rsrc_ind);
744 DBMS_SQL.BIND_VARIABLE(dynamic_cur, ':p_sec_rsrc_ind' , p_sec_rsrc_ind);
745
746 IF p_to_date IS NOT NULL THEN
747 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
748 gme_debug.put_line('p_to_date = '|| to_char(p_to_date, 'DD-MON-YYYY HH24:MI:SS'));
749 END IF;
750
751 DBMS_SQL.BIND_VARIABLE(dynamic_cur, ':p_to_date', p_to_date);
752 END IF;
753
754 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
755 gme_debug.put_line('-------------------------------------------');
756 gme_debug.put_line('After binding variables');
757 END IF;
758
759 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
760 gme_debug.put_line('Executing the dynamic SQL');
761 END IF;
762
763 l_ret := dbms_sql.execute(dynamic_cur);
764
765 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
766 gme_debug.put_line('After executing dynamic SQL rows returned := '||l_ret);
767 END IF;
768
769 IF p_to_date IS NULL THEN
770 OPEN Get_Batches_From_Date;
771 ELSE
772 OPEN Get_Batches_From_To_Date;
773 END IF;
774
775 g_product_no := p_product_no;
776 g_ingredient_no := p_ingredient_no;
777 g_organization_id := p_organization_id;
778 g_org_code := p_org_code;
779 EXCEPTION
780 WHEN OTHERS THEN
781 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
782 gme_debug.put_line ('Exception occurs = ' || SQLERRM);
783 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
784 END IF;
785 END init_session;
786
787 /*======================================================================
788 # Eddie Oumerretane 10-APR-02 Bug # 2313926. Fix the test that ends the
789 # the retrieval of batches/FPOs when the pre-determined
790 # number of documents are loaded (Get_Next_Batch_Set).
791 #======================================================================*/
792 PROCEDURE get_next_batch_set (
793 x_nb_batches OUT NOCOPY NUMBER
794 ,x_start_of_first_batch OUT NOCOPY DATE
795 ,x_batch_table OUT NOCOPY gantttableofbatch)
796 IS
797 l_product_type NUMBER (1) := 1;
798 l_ingredient_type NUMBER (1) := -1;
799 batch_id batchidtab;
800 batch_no batchnotab;
801 batch_status batchstatustab;
802 batch_type batchtypetab;
803 start_date batchdatetab;
804 end_date batchdatetab;
805 plan_start_date batchdatetab;
806 plan_cmplt_date batchdatetab;
807 actual_start batchdatetab;
808 actual_end batchdatetab;
809 formula_id batchidtab;
810 routing_id batchidtab;
811 item_no itemnotab;
812 item_um batchumtab;
813 plan_qty batchqtytab;
814 actual_qty batchqtytab;
815 enforce_step_dep batchtypetab;
816 --Bug3746919 bsatpute
817 terminated_ind batchtypetab;
818 --Bug#2479773 Swapna Kommineni 02-FEB-2005
819 finite_scheduled_ind batchtypetab;
820 parentline_id batchidtab; --Bug#5550337
821 l_nb_record BINARY_INTEGER := 0;
822 l_record_ind BINARY_INTEGER := 1;
823 l_batch_id_str VARCHAR2 (32);
824 l_parent_line_id_str VARCHAR2 (32); --Bug#5550337
825 l_formula_id_str VARCHAR2 (32);
826 l_routing_id_str VARCHAR2 (32);
827 --l_batch_status_str VARCHAR2(32);
828 --l_batch_type_str VARCHAR2(32);
829 --l_enforce_step_dep_str VARCHAR2(32);
830 l_batch_no_str VARCHAR2 (128);
831 l_batch_record VARCHAR2 (700);
832 l_batch_record_tbl VARCHAR2 (32000);
833 l_continue BOOLEAN;
834 l_inventory_item_id NUMBER (15);
835 l_item_no VARCHAR2 (128);
836 l_item_desc VARCHAR2 (280);
837 l_item_um VARCHAR2 (16);
838 l_plan_qty NUMBER;
839 l_actual_qty NUMBER;
840 l_plan_qty_str VARCHAR2 (40);
841 l_actual_qty_str VARCHAR2 (40);
842 l_api_name CONSTANT VARCHAR2 (30) := 'GET_NEXT_BATCH_SET';
843
844 /**
845 * Check whether the given batch consumes or produce the given item based on the line type.
846 */
847 FUNCTION filter_batch_item (
848 p_batch_id NUMBER
849 ,p_item_no VARCHAR2
850 ,p_line_type NUMBER)
851 RETURN BOOLEAN
852 IS
853 CURSOR get_ingredient
854 IS
855 SELECT COUNT (*)
856 FROM mtl_system_items_kfv i, gme_material_details m
857 WHERE m.batch_id = p_batch_id
858 AND m.inventory_item_id = i.inventory_item_id
859 AND m.line_type = -1
860 AND m.organization_id = i.organization_id
861 AND i.concatenated_segments LIKE p_item_no;
862
863 CURSOR get_product
864 IS
865 SELECT COUNT (*)
866 FROM mtl_system_items_kfv i, gme_material_details m
867 WHERE m.batch_id = p_batch_id
868 AND m.inventory_item_id = i.inventory_item_id
869 AND m.organization_id = i.organization_id
870 AND m.line_type = 1
871 AND i.concatenated_segments LIKE p_item_no;
872
873 l_dummy NUMBER;
874 l_return BOOLEAN;
875 l_api_name CONSTANT VARCHAR2 (30) := 'FILTER_BATCH_ITEM';
876 BEGIN
877 l_return := TRUE;
878 l_dummy := 0;
879
880 IF p_line_type = 1 THEN
881 OPEN get_product;
882
883 FETCH get_product
884 INTO l_dummy;
885
886 IF (get_product%NOTFOUND OR l_dummy = 0) THEN
887 l_return := FALSE;
888 END IF;
889
890 CLOSE get_product;
891 ELSE
892 OPEN get_ingredient;
893
894 FETCH get_ingredient
895 INTO l_dummy;
896
897 IF (get_ingredient%NOTFOUND OR l_dummy = 0) THEN
898 l_return := FALSE;
899 END IF;
900
901 CLOSE get_ingredient;
902 END IF;
903
904 RETURN l_return;
905 --Bug2804440
906 EXCEPTION
907 WHEN OTHERS THEN
908 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
909 --End Bug2804440
910 END filter_batch_item;
911
912 /**
913 * Sub-program used to populate the batch detail table.
914 */
915 FUNCTION filter_batch (p_batch_id NUMBER)
916 RETURN BOOLEAN
917 IS
918 l_is_batch_valid BOOLEAN;
919 l_api_name CONSTANT VARCHAR2 (30) := 'FILTER_BATCH';
920 BEGIN
921 l_is_batch_valid := TRUE;
922
923 IF g_ingredient_no <> '%' THEN
924 -- Check whether this batch consumes the specified ingredient
925 l_is_batch_valid :=
926 filter_batch_item (p_batch_id
927 ,g_ingredient_no
928 ,l_ingredient_type);
929 END IF;
930
931 IF g_product_no <> '%' AND l_is_batch_valid THEN
932 -- Check whether this batch produces the specified product
933 l_is_batch_valid :=
934 filter_batch_item (p_batch_id, g_product_no, l_product_type);
935 END IF;
936
937 RETURN l_is_batch_valid;
938 --Bug2804440
939 EXCEPTION
940 WHEN OTHERS THEN
941 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
942 --End Bug2804440
943 END filter_batch;
944 BEGIN
945 x_nb_batches := 0;
946 x_start_of_first_batch := NULL;
947
948 /* Bug: 6375968 Initialized the variables */
949 l_plan_qty := 0;
950 l_actual_qty := 0;
951 l_item_um := ' ';
952 l_inventory_item_id := 0;
953 l_item_no := ' ';
954
955
956 -- To date has not been entered in the Filter window
957 IF get_batches_from_date%ISOPEN THEN
958 l_continue := TRUE;
959
960 WHILE (l_continue) LOOP
961 IF batch_id.EXISTS (1) THEN
962 batch_id.DELETE;
963 END IF;
964
965 FETCH get_batches_from_date
966 BULK COLLECT INTO batch_id, batch_no, batch_status, batch_type
967 ,start_date, end_date, plan_start_date, plan_cmplt_date
968 ,actual_start, actual_end, formula_id, routing_id
969 ,enforce_step_dep,
970 --bug3746919 bsatpute
971 terminated_ind,
972 --Bug#2479773 Swapna Kommineni 02-FEB-2005
973 finite_scheduled_ind, parentline_id LIMIT g_bulk_fetch_limit;
974
975 IF batch_id.EXISTS (1) AND batch_id.COUNT > 0 THEN
976 FOR e IN 1 .. batch_id.COUNT LOOP
977 --- Eddie Oumerretane 07-MAR-03 Bug # 2831884.
978 --- Removed tests on start/end dates below in order to allow WIP batches
979 --- with plan end date < actual start date to display in the tree.
980 IF filter_batch (batch_id (e) ) THEN
981 OPEN get_product_info (batch_id (e) );
982
983 FETCH get_product_info
984 INTO l_inventory_item_id, l_item_no, l_item_desc;
985
986 IF get_product_info%NOTFOUND THEN
987 /* This must be an LCF batch */
988 OPEN get_product_info_lcf (batch_id (e) );
989
990 FETCH get_product_info_lcf
991 INTO l_inventory_item_id, l_item_no, l_item_desc;
992
993 CLOSE get_product_info_lcf;
994 END IF;
995
996 CLOSE get_product_info;
997
998 OPEN get_product_qty (batch_id (e)
999 ,l_inventory_item_id);
1000
1001 FETCH get_product_qty
1002 INTO l_plan_qty, l_actual_qty, l_item_um;
1003
1004 CLOSE get_product_qty;
1005
1006
1007 l_plan_qty_str := ROUND (l_plan_qty, 2);
1008 l_actual_qty_str := ROUND (l_actual_qty, 2);
1009 l_batch_id_str := batch_id (e);
1010 l_formula_id_str := formula_id (e);
1011 l_routing_id_str := routing_id (e);
1012 --l_batch_type_str := batch_type(e);
1013 l_batch_no_str := batch_no (e);
1014 l_parent_line_id_str := parentline_id (e); --Bug#5550337
1015 -- l_batch_status_str := batch_status(e);
1016 -- l_enforce_step_dep_str := enforce_step_dep(e);
1017 l_batch_record :=
1018 LPAD (l_batch_id_str, 32, '0')
1019 || r_pad (l_batch_no_str, 32)
1020 || LPAD (batch_type (e), 5, '0')
1021 || R_PAD (g_org_code, 32)
1022 || LPAD (batch_status (e), 5, '0')
1023 || LPAD (TO_CHAR (plan_start_date (e)
1024 ,'YYYYMMDDHH24MISS')
1025 ,32
1026 ,'0')
1027 || LPAD (TO_CHAR (plan_cmplt_date (e)
1028 ,'YYYYMMDDHH24MISS')
1029 ,32
1030 ,'0')
1031 || LPAD (TO_CHAR (actual_start (e)
1032 ,'YYYYMMDDHH24MISS')
1033 ,32
1034 ,'0')
1035 || LPAD (TO_CHAR (actual_end (e), 'YYYYMMDDHH24MISS')
1036 ,32
1037 ,'0')
1038 || LPAD (TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
1039 ,32
1040 ,'0')
1041 || LPAD (TO_CHAR (end_date (e), 'YYYYMMDDHH24MISS')
1042 ,32
1043 ,'0')
1044 || LPAD (l_formula_id_str, 32, '0')
1045 || LPAD (l_routing_id_str, 32, '0')
1046 || r_pad (l_item_no, 100)
1047 || LPAD (l_actual_qty_str, 40, '0')
1048 || LPAD (l_plan_qty_str, 40, '0')
1049 || r_pad (l_item_um, 4)
1050 || LPAD (enforce_step_dep (e), 5, '0')
1051 --Bug3746919 bsatpute
1052 || LPAD (terminated_ind (e), 5, '0')
1053 || LPAD (finite_scheduled_ind (e), 5, '0')
1054 || LPAD (l_parent_line_id_str, 32, '0'); --Bug#5550337
1055
1056 IF l_nb_record = 0 THEN
1057 IF x_start_of_first_batch IS NULL THEN
1058 x_start_of_first_batch := start_date (e);
1059 END IF;
1060
1061 l_batch_record_tbl := l_batch_record;
1062 l_nb_record := l_nb_record + 1;
1063 ELSE
1064 l_batch_record_tbl :=
1065 l_batch_record_tbl || l_batch_record;
1066 l_nb_record := l_nb_record + 1;
1067
1068 IF l_nb_record >= 40 THEN
1069 x_batch_table (l_record_ind) := l_batch_record_tbl;
1070 l_record_ind := l_record_ind + 1;
1071 l_nb_record := 0;
1072 END IF;
1073 END IF;
1074
1075 x_nb_batches := x_nb_batches + 1;
1076 END IF;
1077 END LOOP;
1078 END IF;
1079
1080 IF NOT batch_id.EXISTS (1)
1081 OR ( batch_id.COUNT <= x_nb_batches
1082 OR batch_id.COUNT < g_bulk_fetch_limit) THEN
1083 -- All selected batches meet the filter criteria or this was the last
1084 -- set of batches. In this case, send the batches to the gantt.
1085 -- Otherwise, continue fetching ...
1086 l_continue := FALSE;
1087 END IF;
1088 END LOOP;
1089
1090 IF l_nb_record > 0 THEN
1091 x_batch_table (l_record_ind) := l_batch_record_tbl;
1092 END IF;
1093 -- To date has been entered in the Filter window
1094 ELSIF get_batches_from_to_date%ISOPEN THEN
1095 l_continue := TRUE;
1096
1097 WHILE (l_continue) LOOP
1098 IF batch_id.EXISTS (1) THEN
1099 batch_id.DELETE;
1100 END IF;
1101
1102 FETCH get_batches_from_to_date
1103 BULK COLLECT INTO batch_id, batch_no, batch_status, batch_type
1104 ,start_date, end_date, plan_start_date, plan_cmplt_date
1105 ,actual_start, actual_end, formula_id, routing_id
1106 ,enforce_step_dep,
1107 --bug3746919 bsatpute
1108 terminated_ind,
1109 --Bug#2479773 Swapna Kommineni 02-FEB-2005
1110 finite_scheduled_ind, parentline_id LIMIT g_bulk_fetch_limit;
1111
1112 --Bug3746919
1113 IF batch_id.EXISTS (1) AND batch_id.COUNT > 0 THEN
1114 FOR e IN 1 .. batch_id.COUNT LOOP
1115 IF filter_batch (batch_id (e) ) THEN
1116 OPEN get_product_info (batch_id (e) );
1117
1118 FETCH get_product_info
1119 INTO l_inventory_item_id, l_item_no, l_item_desc;
1120
1121 IF get_product_info%NOTFOUND THEN
1122 FETCH get_product_info_lcf
1123 INTO l_inventory_item_id, l_item_no, l_item_desc;
1124
1125 CLOSE get_product_info_lcf;
1126 END IF;
1127
1128 CLOSE get_product_info;
1129
1130 OPEN get_product_qty (batch_id (e)
1131 ,l_inventory_item_id);
1132
1133 FETCH get_product_qty
1134 INTO l_plan_qty, l_actual_qty, l_item_um;
1135
1136 CLOSE get_product_qty;
1137
1138 l_plan_qty_str := ROUND (l_plan_qty, 2);
1139 l_actual_qty_str := ROUND (l_actual_qty, 2);
1140 l_batch_id_str := batch_id (e);
1141 l_formula_id_str := formula_id (e);
1142 l_routing_id_str := routing_id (e);
1143 --l_batch_type_str := batch_type(e);
1144 l_batch_no_str := batch_no (e);
1145 l_parent_line_id_str := parentline_id (e); --Bug#5550337
1146 --l_batch_status_str := batch_status(e);
1147 --l_enforce_step_dep_str := enforce_step_dep(e);
1148 l_batch_record :=
1149 LPAD (l_batch_id_str, 32, '0')
1150 || r_pad (l_batch_no_str, 32)
1151 || LPAD (batch_type (e), 5, '0')
1152 || LPAD (g_organization_id, 32, '0')
1153 || LPAD (batch_status (e), 5, '0')
1154 || LPAD (TO_CHAR (plan_start_date (e)
1155 ,'YYYYMMDDHH24MISS')
1156 ,32
1157 ,'0')
1158 || LPAD (TO_CHAR (plan_cmplt_date (e)
1159 ,'YYYYMMDDHH24MISS')
1160 ,32
1161 ,'0')
1162 || LPAD (TO_CHAR (actual_start (e)
1163 ,'YYYYMMDDHH24MISS')
1164 ,32
1165 ,'0')
1166 || LPAD (TO_CHAR (actual_end (e), 'YYYYMMDDHH24MISS')
1167 ,32
1168 ,'0')
1169 || LPAD (TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
1170 ,32
1171 ,'0')
1172 || LPAD (TO_CHAR (end_date (e), 'YYYYMMDDHH24MISS')
1173 ,32
1174 ,'0')
1175 || LPAD (l_formula_id_str, 32, '0')
1176 || LPAD (l_routing_id_str, 32, '0')
1177 || r_pad (l_item_no, 100)
1178 || LPAD (l_actual_qty_str, 40, '0')
1179 || LPAD (l_plan_qty_str, 40, '0')
1180 || r_pad (l_item_um, 4)
1181 || LPAD (enforce_step_dep (e), 5, '0')
1182 --bug3746919 bsatpute
1183 || LPAD (terminated_ind (e), 5, '0')
1184 || LPAD (finite_scheduled_ind (e), 5, '0')
1185 || LPAD (l_parent_line_id_str, 32, '0'); --Bug#5550337
1186
1187 IF l_nb_record = 0 THEN
1188 IF x_start_of_first_batch IS NULL THEN
1189 x_start_of_first_batch := start_date (e);
1190 END IF;
1191
1192 l_batch_record_tbl := l_batch_record;
1193 l_nb_record := l_nb_record + 1;
1194 ELSE
1195 l_batch_record_tbl :=
1196 l_batch_record_tbl || l_batch_record;
1197 l_nb_record := l_nb_record + 1;
1198
1199 IF l_nb_record >= 40 THEN
1200 x_batch_table (l_record_ind) := l_batch_record_tbl;
1201 l_record_ind := l_record_ind + 1;
1202 l_nb_record := 0;
1203 END IF;
1204 END IF;
1205
1206 x_nb_batches := x_nb_batches + 1;
1207 END IF;
1208 END LOOP;
1209 END IF;
1210
1211 IF NOT batch_id.EXISTS (1)
1212 OR ( batch_id.COUNT <= x_nb_batches
1213 OR batch_id.COUNT < g_bulk_fetch_limit) THEN
1214 -- All selected batches meet the filter criteria or this was the last
1215 -- set of batches. In this case, send the batches to the gantt.
1216 -- Otherwise, continue fetching ...
1217 l_continue := FALSE;
1218 END IF;
1219 END LOOP;
1220
1221 IF l_nb_record > 0 THEN
1222 x_batch_table (l_record_ind) := l_batch_record_tbl;
1223 END IF;
1224 END IF;
1225 EXCEPTION
1226 WHEN NO_DATA_FOUND THEN
1227 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1228 gme_debug.put_line ('no data found ');
1229 END IF;
1230 --Bug2804440
1231 WHEN OTHERS THEN
1232 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1233 --End Bug2804440
1234 close_cursors;
1235 END get_next_batch_set;
1236
1237 /*======================================================================
1238 # Eddie Oumerretane 28-MAR-02 Bug # 2284342. Removed tests on start/end
1239 # dates when retrieving operations/activities and resources.
1240 # Bharati Satpute Bug3746919. Replaced the NVL insted of DECODE
1241 # in the get_operation_steps cursor.
1242 #======================================================================*/
1243 PROCEDURE get_operations (
1244 p_batch_id IN NUMBER
1245 ,p_resource IN VARCHAR2
1246 ,p_prim_rsrc_ind IN NUMBER
1247 ,p_sec_rsrc_ind IN NUMBER
1248 ,p_aux_rsrc_ind IN NUMBER
1249 ,x_nb_operation OUT NOCOPY NUMBER
1250 ,x_operations_table OUT NOCOPY gantttableofoperation)
1251 IS
1252 start_date batchdatetab;
1253 batchstep_no batchstepnotab;
1254 step_status batchstatustab;
1255 plan_step_qty batchqtytab;
1256 actual_step_qty batchqtytab;
1257 plan_start_date batchdatetab;
1258 actual_start_date batchdatetab;
1259 expct_cmplt_date batchdatetab;
1260 actual_cmplt_date batchdatetab;
1261 step_close_date batchdatetab;
1262 oprn_id batchidtab;
1263 oprn_no batchnotab;
1264 oprn_vers batchverstab;
1265 oprn_desc batchdesctab;
1266 oper_class batchclasstab;
1267 process_qty_um batchumtab;
1268 end_date batchdatetab;
1269 --bsatpute bug3746919
1270 terminated_ind batchtypetab;
1271
1272 CURSOR get_operation_steps
1273 IS
1274 SELECT DISTINCT
1275 --Bug3746919 bsatpute
1276 DECODE
1277 (step_status
1278 ,1, date_to_clientdt (r.plan_start_date)
1279 ,5, date_to_clientdt (b.actual_start_date)
1280 ,date_to_clientdt (r.actual_start_date) )
1281 start_date
1282 --NVL(date_to_clientDT(r.actual_start_date),date_to_clientDT(r.plan_start_date)) start_date
1283 ,r.batchstep_no, r.step_status, r.plan_step_qty
1284 ,DECODE (r.actual_step_qty
1285 ,NULL, 0
1286 ,r.actual_step_qty) actual_step_qty
1287 ,date_to_clientdt (r.plan_start_date)
1288 ,NVL (date_to_clientdt (r.actual_start_date)
1289 ,date_to_clientdt (r.plan_start_date) )
1290 ,date_to_clientdt (r.plan_cmplt_date)
1291 ,NVL (date_to_clientdt (r.actual_cmplt_date)
1292 ,date_to_clientdt (r.plan_cmplt_date) )
1293 ,NVL (date_to_clientdt (r.step_close_date)
1294 ,date_to_clientdt (SYSDATE) )
1295 ,o.oprn_id, o.oprn_no, NVL (o.oprn_vers, 0)
1296 ,o.oprn_desc
1297 ,DECODE (o.oprn_class
1298 ,NULL, '?'
1299 ,o.oprn_class) oper_class
1300 ,o.process_qty_um
1301 ,DECODE
1302 (step_status
1303 ,3, date_to_clientdt (r.actual_cmplt_date)
1304 ,date_to_clientdt (r.plan_cmplt_date) ) end_date
1305 --bug3746919 bsatpute
1306 ,NVL (r.terminated_ind, 0)
1307 FROM gme_batch_steps r
1308 ,gme_batch_step_resources d
1309 ,gmd_operations o
1310 ,gme_batch_header b
1311 WHERE r.batch_id = p_batch_id
1312 AND r.batch_id = d.batch_id
1313 AND r.batch_id = b.batch_id
1314 AND r.oprn_id = o.oprn_id
1315 AND r.batchstep_id = d.batchstep_id
1316 AND d.resources LIKE p_resource
1317 AND ( d.prim_rsrc_ind = p_prim_rsrc_ind
1318 OR d.prim_rsrc_ind = p_aux_rsrc_ind
1319 OR d.prim_rsrc_ind = p_sec_rsrc_ind)
1320 ORDER BY 1;
1321
1322 CURSOR get_oper_class_desc (p_oper_class VARCHAR2)
1323 IS
1324 SELECT oprn_class_desc
1325 FROM gmd_operation_class
1326 WHERE oprn_class = p_oper_class;
1327
1328 l_nb_record BINARY_INTEGER := 0;
1329 l_record_ind BINARY_INTEGER := 1;
1330 l_batch_id_str VARCHAR2 (10);
1331 l_plan_qty_str VARCHAR2 (40);
1332 l_actual_qty_str VARCHAR2 (40);
1333 l_batchstep_no_str VARCHAR2 (10);
1334 l_step_status_str VARCHAR2 (3);
1335 l_oprn_id_str VARCHAR2 (10);
1336 l_oprn_no_str VARCHAR2 (64);
1337 ---l_oprn_vers_str VARCHAR2(5);
1338 l_oprn_desc_str VARCHAR2 (240);--bug14480509
1339 l_class_str VARCHAR2 (16);
1340 l_class_desc_str VARCHAR2 (160);
1341 l_oper_record VARCHAR2 (737);
1342 l_oper_record_tbl VARCHAR2 (32000);
1343 l_api_name CONSTANT VARCHAR2 (30) := 'GET_OPERATIONS';
1344 BEGIN
1345 x_nb_operation := 0;
1346
1347 OPEN get_operation_steps;
1348
1349 IF oprn_id.EXISTS (1) THEN
1350 oprn_id.DELETE;
1351 END IF;
1352
1353 FETCH get_operation_steps
1354 BULK COLLECT INTO start_date, batchstep_no, step_status, plan_step_qty
1355 ,actual_step_qty, plan_start_date, actual_start_date
1356 ,expct_cmplt_date, actual_cmplt_date, step_close_date, oprn_id
1357 ,oprn_no, oprn_vers, oprn_desc, oper_class, process_qty_um
1358 ,end_date,
1359 --bug3746919 bsatpute
1360 terminated_ind LIMIT g_bulk_fetch_limit;
1361
1362 IF oprn_id.EXISTS (1) AND oprn_id.COUNT > 0 THEN
1363 FOR e IN 1 .. oprn_id.COUNT LOOP
1364 l_class_desc_str := '?';
1365
1366 IF (oper_class (e) <> '?') THEN
1367 OPEN get_oper_class_desc (oper_class (e) );
1368
1369 FETCH get_oper_class_desc
1370 INTO l_class_desc_str;
1371
1372 CLOSE get_oper_class_desc;
1373 END IF;
1374
1375 l_batch_id_str := p_batch_id;
1376 l_oprn_id_str := oprn_id (e);
1377 l_oprn_no_str := oprn_no (e);
1378 --- l_oprn_vers_str := oprn_vers(e);
1379 l_oprn_desc_str := oprn_desc (e);
1380 l_class_str := oper_class (e);
1381 l_batchstep_no_str := batchstep_no (e);
1382 l_step_status_str := step_status (e);
1383 l_plan_qty_str := ROUND (plan_step_qty (e), 3);
1384 l_actual_qty_str := ROUND (actual_step_qty (e), 3);
1385 l_oper_record :=
1386 LPAD (l_batch_id_str, 10, '0')
1387 || LPAD (l_oprn_id_str, 10, '0')
1388 || r_pad (l_oprn_no_str, 16)
1389 || r_pad (l_oprn_desc_str, 240)--bug14480509
1390 || r_pad (l_class_str, 4)
1391 || r_pad (l_class_desc_str, 40)
1392 || LPAD (l_batchstep_no_str, 10, '0')
1393 || LPAD (l_step_status_str, 3, '0')
1394 || LPAD (l_plan_qty_str, 40, '0')
1395 || LPAD (l_actual_qty_str, 40, '0')
1396 || r_pad (process_qty_um (e), 4)
1397 || TO_CHAR (plan_start_date (e), 'YYYYMMDDHH24MISS')
1398 || TO_CHAR (actual_start_date (e), 'YYYYMMDDHH24MISS')
1399 || TO_CHAR (expct_cmplt_date (e), 'YYYYMMDDHH24MISS')
1400 || TO_CHAR (actual_cmplt_date (e), 'YYYYMMDDHH24MISS')
1401 || TO_CHAR (step_close_date (e), 'YYYYMMDDHH24MISS')
1402 || TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
1403 || TO_CHAR (end_date (e), 'YYYYMMDDHH24MISS')
1404 || LPAD (oprn_vers (e), 5, '0')
1405 --bsatpute bug3746919
1406 || LPAD (terminated_ind (e), 5, '0');
1407
1408 IF l_nb_record = 0 THEN
1409 l_oper_record_tbl := l_oper_record;
1410 l_nb_record := l_nb_record + 1;
1411 ELSE
1412 l_oper_record_tbl := l_oper_record_tbl || l_oper_record;
1413 l_nb_record := l_nb_record + 1;
1414
1415 IF l_nb_record >= 45 THEN
1416 x_operations_table (l_record_ind) := l_oper_record_tbl;
1417 l_record_ind := l_record_ind + 1;
1418 l_nb_record := 0;
1419 END IF;
1420 END IF;
1421
1422 x_nb_operation := x_nb_operation + 1;
1423 END LOOP;
1424
1425 IF l_nb_record > 0 THEN
1426 x_operations_table (l_record_ind) := l_oper_record_tbl;
1427 END IF;
1428 END IF;
1429
1430 CLOSE get_operation_steps;
1431 --Bug2804440
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1435 --End Bug2804440
1436 END get_operations;
1437
1438 /*======================================================================
1439 # Eddie Oumerretane 28-MAR-02 Bug # 2284342. Removed tests on start/end
1440 # dates when retrieving operations/activities and resources.
1441 #======================================================================*/
1442 PROCEDURE get_activities (
1443 p_batch_id IN NUMBER
1444 ,p_batchstep_no IN NUMBER
1445 ,p_resource IN VARCHAR2
1446 ,p_prim_rsrc_ind IN NUMBER
1447 ,p_sec_rsrc_ind IN NUMBER
1448 ,p_aux_rsrc_ind IN NUMBER
1449 ,x_nb_activity OUT NOCOPY NUMBER
1450 ,x_activity_table OUT NOCOPY gantttableofactivity)
1451 IS
1452 activity activitycodetab;
1453 activity_desc batchdesctab;
1454 cost_analysis_code costancodetab;
1455 cost_analysis_desc batchdesctab;
1456 start_date batchdatetab;
1457 end_date batchdatetab;
1458
1459 CURSOR get_activities
1460 IS
1461 SELECT a.activity, a.activity_desc, a.cost_analysis_code
1462 ,c.cost_analysis_desc
1463 ,MIN
1464 (DECODE (r.step_status
1465 ,1, date_to_clientdt (o.plan_start_date)
1466 ,5, date_to_clientdt (b.actual_start_date)
1467 ,date_to_clientdt (o.actual_start_date) ) )
1468 start_date
1469 ,MAX
1470 (DECODE (r.step_status
1471 ,3, date_to_clientdt (o.actual_cmplt_date)
1472 ,date_to_clientdt (o.plan_cmplt_date) ) )
1473 end_date
1474 FROM gme_batch_step_resources o
1475 ,gme_batch_steps r
1476 ,gme_batch_step_activities s
1477 ,gmd_activities a
1478 ,gme_batch_header b
1479 ,cm_alys_mst c
1480 WHERE o.batch_id = p_batch_id
1481 AND r.batch_id = o.batch_id
1482 AND s.batch_id = r.batch_id
1483 AND b.batch_id = r.batch_id
1484 AND s.delete_mark = 0
1485 AND o.batchstep_activity_id = s.batchstep_activity_id
1486 AND r.batchstep_no = p_batchstep_no
1487 AND o.batchstep_id = r.batchstep_id
1488 AND s.activity = a.activity
1489 AND c.cost_analysis_code = a.cost_analysis_code
1490 AND o.resources LIKE p_resource
1491 AND ( o.prim_rsrc_ind = p_prim_rsrc_ind
1492 OR o.prim_rsrc_ind = p_aux_rsrc_ind
1493 OR o.prim_rsrc_ind = p_sec_rsrc_ind)
1494 GROUP BY a.activity
1495 ,a.activity_desc
1496 ,a.cost_analysis_code
1497 ,c.cost_analysis_desc
1498 ORDER BY 5;
1499
1500 l_nb_record BINARY_INTEGER := 0;
1501 l_record_ind BINARY_INTEGER := 1;
1502 l_batch_id_str VARCHAR2 (10);
1503 l_batchstep_no_str VARCHAR2 (10);
1504 l_activity_str VARCHAR2 (64);
1505 l_activity_desc_str VARCHAR2 (160);
1506 -- bug14480540
1507 l_cost_ana_code_str CM_ALYS_MST.COST_ANALYSIS_CODE%TYPE;
1508 l_cost_ana_desc_str CM_ALYS_MST.COST_ANALYSIS_DESC%TYPE;
1509 l_activity_record VARCHAR2 (600);
1510 l_activity_record_tbl VARCHAR2 (32000);
1511 l_api_name CONSTANT VARCHAR2 (30) := 'GET_ACTIVITIES';
1512 BEGIN
1513 x_nb_activity := 0;
1514
1515 OPEN get_activities;
1516
1517 IF activity.EXISTS (1) THEN
1518 activity.DELETE;
1519 END IF;
1520
1521 FETCH get_activities
1522 BULK COLLECT INTO activity, activity_desc, cost_analysis_code
1523 ,cost_analysis_desc, start_date, end_date;
1524
1525 IF activity.EXISTS (1) AND activity.COUNT > 0 THEN
1526 FOR e IN 1 .. activity.COUNT LOOP
1527 l_batch_id_str := p_batch_id;
1528 l_batchstep_no_str := p_batchstep_no;
1529 l_activity_str := activity (e);
1530 l_activity_desc_str := activity_desc (e);
1531 l_cost_ana_code_str := cost_analysis_code (e);
1532 l_cost_ana_desc_str := cost_analysis_desc (e);
1533 l_activity_record :=
1534 LPAD (l_batch_id_str, 10, '0')
1535 || LPAD (l_batchstep_no_str, 10, '0')
1536 || r_pad (l_activity_str, 16)
1537 || r_pad (l_activity_desc_str, 40)
1538 || r_pad (l_cost_ana_code_str, 4)
1539 || r_pad (l_cost_ana_desc_str, 40)
1540 || TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
1541 || TO_CHAR (end_date (e), 'YYYYMMDDHH24MISS');
1542
1543 IF l_nb_record = 0 THEN
1544 l_activity_record_tbl := l_activity_record;
1545 l_nb_record := l_nb_record + 1;
1546 ELSE
1547 l_activity_record_tbl :=
1548 l_activity_record_tbl || l_activity_record;
1549 l_nb_record := l_nb_record + 1;
1550
1551 IF l_nb_record >= 50 THEN
1552 x_activity_table (l_record_ind) := l_activity_record_tbl;
1553 l_record_ind := l_record_ind + 1;
1554 l_nb_record := 0;
1555 END IF;
1556 END IF;
1557
1558 x_nb_activity := x_nb_activity + 1;
1559 END LOOP;
1560
1561 IF l_nb_record > 0 THEN
1562 x_activity_table (l_record_ind) := l_activity_record_tbl;
1563 END IF;
1564 END IF;
1565
1566 CLOSE get_activities;
1567 --Bug2804440
1568 EXCEPTION
1569 WHEN OTHERS THEN
1570 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1571 --End Bug2804440
1572 END get_activities;
1573
1574 /*======================================================================
1575 # Eddie Oumerretane 28-MAR-02 Bug # 2284342. Removed tests on start/end
1576 # dates when retrieving operations/activities and resources.
1577 #======================================================================*/
1578 PROCEDURE get_resources (
1579 p_batch_id IN NUMBER
1580 ,p_batchstep_no IN NUMBER
1581 ,p_activity IN VARCHAR2
1582 ,p_resource IN VARCHAR2
1583 ,p_prim_rsrc_ind IN NUMBER
1584 ,p_sec_rsrc_ind IN NUMBER
1585 ,p_aux_rsrc_ind IN NUMBER
1586 ,x_nb_resource OUT NOCOPY NUMBER
1587 ,x_resource_table OUT NOCOPY gantttableofresource)
1588 IS
1589 resources batchresourcestab;
1590 resource_desc batchdesctab;
1591 prim_rsrc_ind batchintindtab;
1592 scale_type batchtypetab;
1593 plan_rsrc_count batchqtytab;
1594 actual_res_count batchqtytab;
1595 plan_rsrc_qty batchqtytab;
1596 actual_res_qty batchqtytab;
1597 plan_rsrc_usage batchqtytab;
1598 actual_res_usage batchqtytab;
1599 std_usage_um batchumtab;
1600 offset_interval batchqtytab;
1601 start_date batchdatetab;
1602 end_date batchdatetab;
1603 plan_start_date batchdatetab;
1604 plan_cmplt_date batchdatetab;
1605 actual_start_date batchdatetab;
1606 actual_cmplt_date batchdatetab;
1607 process_qty_um batchumtab;
1608 step_status batchstatustab;
1609
1610 CURSOR get_resources
1611 IS
1612 SELECT o.resources, c.resource_desc, o.prim_rsrc_ind
1613 ,o.scale_type, NVL (o.plan_rsrc_count, 0)
1614 ,DECODE (o.actual_rsrc_count
1615 ,NULL, 0
1616 ,o.actual_rsrc_count) actual_res_count
1617 ,NVL (o.plan_rsrc_qty, 0)
1618 ,DECODE (o.actual_rsrc_qty
1619 ,NULL, 0
1620 ,o.actual_rsrc_qty) actual_res_qty
1621 ,NVL (o.plan_rsrc_usage, 0)
1622 ,DECODE (o.actual_rsrc_usage
1623 ,NULL, 0
1624 ,o.actual_rsrc_usage) actual_res_usage
1625 ,NVL (c.std_usage_um, ' '), NVL (o.offset_interval, 0)
1626 --Swapna K Bug#2479773 17-MAR-2005 Rework
1627 /* Null actual start date is sent as planned start date */
1628 ,DECODE
1629 (r.step_status
1630 ,1, date_to_clientdt (o.plan_start_date)
1631 ,5, date_to_clientdt (b.actual_start_date)
1632 ,NVL (date_to_clientdt (o.actual_start_date)
1633 ,date_to_clientdt (o.plan_start_date) ) )
1634 start_date
1635 ,DECODE (r.step_status
1636 ,3, date_to_clientdt (o.actual_cmplt_date)
1637 ,date_to_clientdt (o.plan_cmplt_date) ) end_date
1638 ,date_to_clientdt (o.plan_start_date)
1639 ,date_to_clientdt (o.plan_cmplt_date)
1640 ,NVL (date_to_clientdt (o.actual_start_date)
1641 ,date_to_clientdt (o.plan_start_date) )
1642 ,NVL (date_to_clientdt (o.actual_cmplt_date)
1643 ,date_to_clientdt (o.plan_cmplt_date) )
1644 ,NVL (f.process_qty_um, ' '), NVL (r.step_status, 0)
1645 FROM gme_batch_steps r
1646 ,gme_batch_step_activities a
1647 ,gme_batch_step_resources o
1648 ,gmd_operations f
1649 ,gme_batch_header b
1650 ,cr_rsrc_mst c
1651 WHERE r.batch_id = p_batch_id
1652 AND b.batch_id = r.batch_id
1653 AND r.batchstep_no = p_batchstep_no
1654 AND a.batchstep_id = r.batchstep_id
1655 AND a.delete_mark = 0
1656 AND o.batchstep_id = a.batchstep_id
1657 AND o.batchstep_activity_id = a.batchstep_activity_id
1658 AND a.activity = p_activity
1659 AND f.oprn_id = r.oprn_id
1660 AND r.batch_id = o.batch_id
1661 AND o.resources = c.resources
1662 AND o.resources LIKE p_resource
1663 AND ( o.prim_rsrc_ind = p_prim_rsrc_ind
1664 OR o.prim_rsrc_ind = p_aux_rsrc_ind
1665 OR o.prim_rsrc_ind = p_sec_rsrc_ind)
1666 ORDER BY 13;
1667
1668 l_nb_record BINARY_INTEGER := 0;
1669 l_record_ind BINARY_INTEGER := 1;
1670 l_batch_id_str VARCHAR2 (10);
1671 l_batchstep_no_str VARCHAR2 (10);
1672 l_resources_str VARCHAR2 (64);
1673 l_resource_desc_str VARCHAR2 (240);--bug14480509
1674 l_prim_rsrc_ind_str VARCHAR2 (2);
1675 l_scale_type_str VARCHAR2 (2);
1676 l_plan_rsrc_count_str VARCHAR2 (40);
1677 l_actual_res_count_str VARCHAR2 (40);
1678 l_plan_rsrc_qty_str VARCHAR2 (40);
1679 l_actual_rsrc_qty_str VARCHAR2 (40);
1680 l_plan_rsrc_usage_str VARCHAR2 (40);
1681 l_actual_rsrc_usage_str VARCHAR2 (40);
1682 l_std_usage_um_str VARCHAR2 (16);
1683 l_offset_interval_str VARCHAR2 (40);
1684 l_process_qty_um_str VARCHAR2 (16);
1685 l_step_status_str VARCHAR2 (3);
1686 l_resource_record VARCHAR2 (792);
1687 l_resource_record_tbl VARCHAR2 (32000);
1688 l_api_name CONSTANT VARCHAR2 (30) := 'GET_RESOURCES';
1689 BEGIN
1690 x_nb_resource := 0;
1691
1692 OPEN get_resources;
1693
1694 IF resources.EXISTS (1) THEN
1695 resources.DELETE;
1696 END IF;
1697
1698 FETCH get_resources
1699 BULK COLLECT INTO resources, resource_desc, prim_rsrc_ind, scale_type
1700 ,plan_rsrc_count, actual_res_count, plan_rsrc_qty, actual_res_qty
1701 ,plan_rsrc_usage, actual_res_usage, std_usage_um, offset_interval
1702 ,start_date, end_date, plan_start_date, plan_cmplt_date
1703 ,actual_start_date, actual_cmplt_date, process_qty_um
1704 ,step_status;
1705
1706 IF resources.EXISTS (1) AND resources.COUNT > 0 THEN
1707 FOR e IN 1 .. resources.COUNT LOOP
1708 l_batch_id_str := p_batch_id;
1709 l_batchstep_no_str := p_batchstep_no;
1710 l_resources_str := resources (e);
1711 l_resource_desc_str := resource_desc (e);
1712 l_prim_rsrc_ind_str := prim_rsrc_ind (e);
1713 l_scale_type_str := scale_type (e);
1714 l_plan_rsrc_count_str := plan_rsrc_count (e);
1715 l_actual_res_count_str := actual_res_count (e);
1716 l_plan_rsrc_qty_str := ROUND (plan_rsrc_qty (e), 3);
1717 l_actual_rsrc_qty_str := ROUND (actual_res_qty (e), 3);
1718 l_plan_rsrc_usage_str := ROUND (plan_rsrc_usage (e), 3);
1719 l_actual_rsrc_usage_str := ROUND (actual_res_usage (e), 3);
1720 l_std_usage_um_str := std_usage_um (e);
1721 l_offset_interval_str := offset_interval (e);
1722 l_step_status_str := step_status (e);
1723 l_process_qty_um_str := process_qty_um (e);
1724 l_resource_record :=
1725 LPAD (l_batch_id_str, 10, '0')
1726 || LPAD (l_batchstep_no_str, 10, '0')
1727 || r_pad (l_resources_str, 16)
1728 || r_pad (l_resource_desc_str, 240)--bug14480509
1729 || LPAD (l_prim_rsrc_ind_str, 2, '0')
1730 || LPAD (l_scale_type_str, 2, '0')
1731 || LPAD (l_plan_rsrc_count_str, 40, 0)
1732 || LPAD (l_actual_res_count_str, 40, 0)
1733 || LPAD (l_plan_rsrc_qty_str, 40, 0)
1734 || LPAD (l_actual_rsrc_qty_str, 40, 0)
1735 || LPAD (l_plan_rsrc_usage_str, 40, 0)
1736 || LPAD (l_actual_rsrc_usage_str, 40, 0)
1737 || r_pad (l_std_usage_um_str, 4)
1738 || LPAD (l_offset_interval_str, 40, 0)
1739 || LPAD (l_step_status_str, 3, '0')
1740 || r_pad (l_process_qty_um_str, 4)
1741 || TO_CHAR (start_date (e), 'YYYYMMDDHH24MISS')
1742 || TO_CHAR (end_date (e), 'YYYYMMDDHH24MISS')
1743 || TO_CHAR (plan_start_date (e), 'YYYYMMDDHH24MISS')
1744 || TO_CHAR (plan_cmplt_date (e), 'YYYYMMDDHH24MISS')
1745 || TO_CHAR (actual_start_date (e), 'YYYYMMDDHH24MISS')
1746 || TO_CHAR (actual_cmplt_date (e), 'YYYYMMDDHH24MISS');
1747
1748 IF l_nb_record = 0 THEN
1749 l_resource_record_tbl := l_resource_record;
1750 l_nb_record := l_nb_record + 1;
1751 ELSE
1752 l_resource_record_tbl :=
1753 l_resource_record_tbl || l_resource_record;
1754 l_nb_record := l_nb_record + 1;
1755
1756 IF l_nb_record >= 40 THEN
1757 x_resource_table (l_record_ind) := l_resource_record_tbl;
1758 l_record_ind := l_record_ind + 1;
1759 l_nb_record := 0;
1760 END IF;
1761 END IF;
1762
1763 x_nb_resource := x_nb_resource + 1;
1764 END LOOP;
1765
1766 IF l_nb_record > 0 THEN
1767 x_resource_table (l_record_ind) := l_resource_record_tbl;
1768 END IF;
1769 END IF;
1770
1771 CLOSE get_resources;
1772 --Bug2804440
1773 EXCEPTION
1774 WHEN OTHERS THEN
1775 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1776 --End Bug2804440
1777 END get_resources;
1778
1779 /*======================================================================
1780 # Eddie Oumerretane 21-MAR-02 Bug # 2189705. Retrieve the UOM from
1781 # the GME_MATERIAL_DETAILS in Get_Batch_Properties
1782 #======================================================================*/
1783 PROCEDURE get_batch_properties (
1784 p_batch_id IN NUMBER
1785 ,x_batch_properties_str OUT NOCOPY VARCHAR2)
1786 IS
1787 CURSOR get_ids (p_batch_id NUMBER)
1788 IS
1789 SELECT b.formula_id, b.routing_id, b.recipe_validity_rule_id
1790 ,v.recipe_id
1791 FROM gme_batch_header b, gmd_recipe_validity_rules v
1792 WHERE b.batch_id = p_batch_id
1793 AND b.recipe_validity_rule_id = v.recipe_validity_rule_id(+);
1794
1795 CURSOR get_routing_info (p_routing_id NUMBER)
1796 IS
1797 SELECT routing_no, routing_vers, routing_desc
1798 FROM gmd_routings
1799 WHERE routing_id = p_routing_id;
1800
1801 CURSOR get_formula_info (p_formula_id NUMBER)
1802 IS
1803 SELECT formula_no, formula_vers, formula_desc1
1804 FROM fm_form_mst
1805 WHERE formula_id = p_formula_id;
1806
1807 CURSOR get_recipe_info (p_recipe_id NUMBER)
1808 IS
1809 SELECT recipe_no, recipe_version, recipe_description
1810 FROM gmd_recipes
1811 WHERE recipe_id = p_recipe_id;
1812
1813 l_routing_id NUMBER (10);
1814 l_formula_id NUMBER (10);
1815 l_recipe_id NUMBER (15);
1816 l_recipe_vr_id NUMBER (15);
1817 l_inventory_item_id NUMBER (15);
1818 l_routing_no VARCHAR2 (128) := '?';
1819 l_routing_vers NUMBER (5) := 0;
1820 l_formula_no VARCHAR2 (128) := '?';
1821 l_recipe_no VARCHAR2 (128) := '?';
1822 l_formula_vers NUMBER (5) := 0;
1823 l_recipe_vers NUMBER (5) := 0;
1824 l_item_no VARCHAR2 (128) := '?';
1825 l_item_desc VARCHAR2 (280) := '?';
1826 l_recipe_desc VARCHAR2 (280) := '?';
1827 l_formula_desc VARCHAR2 (280) := '?';
1828 l_routing_desc VARCHAR2 (240) := '?'; --bug14480509
1829 l_item_um VARCHAR2 (16) := '?';
1830 l_plan_qty NUMBER := 0;
1831 l_actual_qty NUMBER := 0;
1832 l_routing_vers_str VARCHAR2 (5);
1833 l_formula_vers_str VARCHAR2 (5);
1834 l_plan_qty_str VARCHAR2 (40);
1835 l_actual_qty_str VARCHAR2 (40);
1836 l_api_name CONSTANT VARCHAR2 (30) := 'GET_BATCH_PROPERTIES';
1837 BEGIN
1838 OPEN get_ids (p_batch_id);
1839
1840 FETCH get_ids
1841 INTO l_formula_id, l_routing_id, l_recipe_vr_id, l_recipe_id;
1842
1843 CLOSE get_ids;
1844
1845 IF (l_recipe_id IS NOT NULL AND l_recipe_id > 0) THEN
1846 OPEN get_recipe_info (l_recipe_id);
1847
1848 FETCH get_recipe_info
1849 INTO l_recipe_no, l_recipe_vers, l_recipe_desc;
1850
1851 CLOSE get_recipe_info;
1852 END IF;
1853
1854 IF (l_routing_id IS NOT NULL AND l_routing_id > 0) THEN
1855 OPEN get_routing_info (l_routing_id);
1856
1857 FETCH get_routing_info
1858 INTO l_routing_no, l_routing_vers, l_routing_desc;
1859
1860 CLOSE get_routing_info;
1861 END IF;
1862
1863 IF (l_formula_id IS NOT NULL AND l_formula_id > 0) THEN
1864 OPEN get_formula_info (l_formula_id);
1865
1866 FETCH get_formula_info
1867 INTO l_formula_no, l_formula_vers, l_formula_desc;
1868
1869 CLOSE get_formula_info;
1870 END IF;
1871
1872 OPEN get_product_info (p_batch_id);
1873
1874 FETCH get_product_info
1875 INTO l_inventory_item_id, l_item_no, l_item_desc;
1876
1877 IF get_product_info%NOTFOUND THEN
1878 /* This must be an LCF batch */
1879 OPEN get_product_info_lcf (p_batch_id);
1880
1881 FETCH get_product_info_lcf
1882 INTO l_inventory_item_id, l_item_no, l_item_desc;
1883
1884 CLOSE get_product_info_lcf;
1885 END IF;
1886
1887 CLOSE get_product_info;
1888
1889 OPEN get_product_qty (p_batch_id, l_inventory_item_id);
1890
1891 FETCH get_product_qty
1892 INTO l_plan_qty, l_actual_qty, l_item_um;
1893
1894 CLOSE get_product_qty;
1895
1896 l_routing_vers_str := l_routing_vers;
1897 l_formula_vers_str := l_formula_vers;
1898 l_plan_qty_str := ROUND (l_plan_qty, 3);
1899 l_actual_qty_str := ROUND (l_actual_qty, 3);
1900 x_batch_properties_str :=
1901 r_pad (l_item_no, 32)
1902 || r_pad (l_item_desc, 70)
1903 || LPAD (l_plan_qty_str, 40, '0')
1904 || LPAD (l_actual_qty_str, 40, '0')
1905 || r_pad (l_item_um, 4)
1906 || r_pad (l_formula_no, 32)
1907 || LPAD (l_formula_vers, 5, '0')
1908 || r_pad (l_routing_no, 32)
1909 || LPAD (l_routing_vers, 5, '0')
1910 || r_pad (l_recipe_no, 32)
1911 || LPAD (l_recipe_vers, 5, '0')
1912 || r_pad (l_recipe_desc, 240)--bug14480509
1913 || r_pad (l_formula_desc, 240)--bug14480509
1914 || r_pad (l_routing_desc, 240);--bug14480509
1915 --Bug2804440
1916 EXCEPTION
1917 WHEN OTHERS THEN
1918 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1919 --End Bug2804440
1920 END get_batch_properties;
1921
1922 PROCEDURE close_cursors
1923 IS
1924 l_api_name CONSTANT VARCHAR2 (30) := 'CLOSE_CURSORS';
1925 BEGIN
1926 IF get_batches_from_date%ISOPEN THEN
1927 CLOSE get_batches_from_date;
1928 END IF;
1929
1930 IF get_batches_from_to_date%ISOPEN THEN
1931 CLOSE get_batches_from_to_date;
1932 END IF;
1933 --Bug2804440
1934 EXCEPTION
1935 WHEN OTHERS THEN
1936 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1937 --End Bug2804440
1938 END close_cursors;
1939
1940 /*======================================================================
1941 # Retrieve Shop calendar data for the given organization
1942 #======================================================================*/
1943 FUNCTION get_mfg_calendar (p_organization_id IN NUMBER)
1944 RETURN BOOLEAN
1945 IS
1946 CURSOR get_mfg_cal_code
1947 IS
1948 SELECT p.calendar_code, c.description
1949 FROM mtl_parameters p, bom_calendars c
1950 WHERE p.organization_id = p_organization_id
1951 AND c.calendar_code = p.calendar_code;
1952 BEGIN
1953 OPEN get_mfg_cal_code;
1954
1955 FETCH get_mfg_cal_code
1956 INTO g_mfg_calendar_code, g_calendar_desc;
1957
1958 IF (get_mfg_cal_code%NOTFOUND OR g_mfg_calendar_code IS NULL) THEN
1959 CLOSE get_mfg_cal_code;
1960
1961 RETURN FALSE;
1962 ELSE
1963 CLOSE get_mfg_cal_code;
1964
1965 RETURN TRUE;
1966 END IF;
1967 END get_mfg_calendar;
1968
1969 /*======================================================================
1970 # Retrieve Shop calendar assigned to the given organization
1971 #======================================================================*/
1972 PROCEDURE fetch_shop_calendar (
1973 p_organization_id IN NUMBER
1974 ,p_date IN DATE
1975 ,x_calendar_no OUT NOCOPY VARCHAR2
1976 ,x_calendar_desc OUT NOCOPY VARCHAR2
1977 ,x_calendar_start OUT NOCOPY VARCHAR2
1978 ,x_calendar_end OUT NOCOPY VARCHAR2
1979 ,x_calendar_assigned OUT NOCOPY VARCHAR2
1980 ,x_return_code OUT NOCOPY VARCHAR2
1981 ,x_error_msg OUT NOCOPY VARCHAR2)
1982 IS
1983 -- Get Calendar Date Range
1984 CURSOR get_calendar_date_range (p_calendar_code VARCHAR2)
1985 IS
1986 SELECT MIN (date_to_clientdt (calendar_start_date) )
1987 ,MAX (date_to_clientdt (calendar_end_date) )
1988 FROM bom_calendars
1989 WHERE calendar_code = p_calendar_code;
1990
1991 l_api_name CONSTANT VARCHAR2 (30) := 'Fetch_Shop_Calendar';
1992 inv_calendar_sel EXCEPTION;
1993 no_calendar_assigned EXCEPTION;
1994 BEGIN
1995 x_return_code := 'S';
1996 x_error_msg := ' ';
1997
1998 IF NOT get_mfg_calendar (p_organization_id) THEN
1999 RAISE no_calendar_assigned;
2000 ELSE
2001 x_calendar_assigned := 'Y';
2002
2003 OPEN get_calendar_date_range (g_mfg_calendar_code);
2004
2005 FETCH get_calendar_date_range
2006 INTO g_calendar_start, g_calendar_end;
2007
2008 CLOSE get_calendar_date_range;
2009 END IF;
2010
2011 IF (p_date IS NOT NULL)
2012 AND (p_date < g_calendar_start OR p_date > g_calendar_end) THEN
2013 RAISE inv_calendar_sel;
2014 END IF;
2015
2016 x_calendar_start := TO_CHAR (g_calendar_start, 'YYYYMMDDHH24MISS');
2017 x_calendar_end := TO_CHAR (g_calendar_end, 'YYYYMMDDHH24MISS');
2018 x_calendar_no := r_pad (g_mfg_calendar_code, 16);
2019 x_calendar_desc := r_pad (g_calendar_desc, 40);
2020 EXCEPTION
2021 WHEN inv_calendar_sel THEN
2022 fnd_message.set_name ('GME', 'GME_DATE_OUTSIDE_SHOP_CALENDAR');
2023 --Bug3315440
2024 --FND_MESSAGE.SET_TOKEN('SEL_DATE', TO_CHAR(p_date,'DD-MON-YYYY'));
2025 --FND_MESSAGE.SET_TOKEN('CAL_BEGIN_DATE', TO_CHAR(g_calendar_start,'DD-MON-YYYY'));
2026 --FND_MESSAGE.SET_TOKEN('CAL_END_DATE', TO_CHAR(g_calendar_end,'DD-MON-YYYY'));
2027 --Bug4068469: removed trunc from the set_token call
2028 --FND_MESSAGE.SET_TOKEN('SEL_DATE', trunc(fnd_date.date_to_displayDT(p_date),'DD-MON-YYYY'));
2029 fnd_message.set_token ('SEL_DATE'
2030 ,fnd_date.date_to_displaydt (p_date,2) );
2031 fnd_message.set_token ('CAL_BEGIN_DATE'
2032 ,fnd_date.date_to_displaydt (g_calendar_start,2) );
2033 fnd_message.set_token ('CAL_END_DATE'
2034 ,fnd_date.date_to_displaydt (g_calendar_end,2) );
2035 x_return_code := 'F';
2036 x_error_msg := r_pad (fnd_message.get, 1000);
2037 WHEN no_calendar_assigned THEN
2038 x_calendar_assigned := 'N';
2039 fnd_message.set_name ('GMP', 'GME_NO_CAL_ASSIGNED_TO_PLANT');
2040 x_return_code := 'F';
2041 x_error_msg := r_pad (fnd_message.get, 1000);
2042 WHEN OTHERS THEN
2043 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2044 END fetch_shop_calendar;
2045
2046 /*======================================================================
2047 # Retrieve working and non working days for a given date range
2048 #======================================================================*/
2049 PROCEDURE fetch_work_non_work_days (
2050 p_organization_id IN NUMBER
2051 ,p_from_date IN DATE
2052 ,p_to_date IN DATE
2053 ,x_calendar_no OUT NOCOPY VARCHAR2
2054 ,x_calendar_desc OUT NOCOPY VARCHAR2
2055 ,x_calendar_start OUT NOCOPY VARCHAR2
2056 ,x_calendar_end OUT NOCOPY VARCHAR2
2057 ,x_rec_count OUT NOCOPY NUMBER
2058 ,x_shop_cal_tbl OUT NOCOPY shopcalendartabletype
2059 ,x_calendar_assigned OUT NOCOPY VARCHAR2
2060 ,x_return_code OUT NOCOPY VARCHAR2
2061 ,x_error_msg OUT NOCOPY VARCHAR2)
2062 IS
2063 l_output_tbl gmp_calendar_api.date_tbl;
2064 l_nb_record BINARY_INTEGER := 0;
2065 l_record_ind BINARY_INTEGER := 1;
2066 l_cal_rec VARCHAR2 (15);
2067 l_cal_rec_tbl VARCHAR2 (32000);
2068 l_api_name CONSTANT VARCHAR2 (30) := 'Fetch_Work_Non_Work_Days';
2069 l_max_records CONSTANT NUMBER := 2100;
2070 inv_calendar_sel EXCEPTION;
2071 fetch_cal_error EXCEPTION;
2072 no_cal_assigned_to_org EXCEPTION;
2073 BEGIN
2074 x_rec_count := 0;
2075 x_return_code := 'S';
2076 x_error_msg := ' ';
2077 x_calendar_assigned := 'Y';
2078 --- Get the calendar associated to the given organization
2079 fetch_shop_calendar (p_organization_id => p_organization_id
2080 ,p_date => NULL
2081 ,x_calendar_no => x_calendar_no
2082 ,x_calendar_desc => x_calendar_desc
2083 ,x_calendar_start => x_calendar_start
2084 ,x_calendar_end => x_calendar_end
2085 ,x_calendar_assigned => x_calendar_assigned
2086 ,x_return_code => x_return_code
2087 ,x_error_msg => x_error_msg);
2088
2089 IF x_return_code <> 'S' THEN
2090 IF x_calendar_assigned = 'N' THEN
2091 RAISE no_cal_assigned_to_org;
2092 ELSE
2093 RAISE inv_calendar_sel;
2094 END IF;
2095 END IF;
2096
2097 gmp_calendar_api.get_all_dates (p_api_version => 1
2098 ,p_init_msg_list => FALSE
2099 ,p_calendar_code => g_mfg_calendar_code
2100 ,p_start_date => p_from_date
2101 ,p_end_date => p_to_date
2102 ,p_output_tbl => l_output_tbl
2103 ,x_return_status => x_return_code);
2104
2105 IF x_return_code <> 'S' THEN
2106 RAISE fetch_cal_error;
2107 END IF;
2108
2109 IF l_output_tbl.COUNT > 0 THEN
2110 x_rec_count := l_output_tbl.COUNT;
2111
2112 FOR e IN 1 .. l_output_tbl.COUNT LOOP
2113 l_cal_rec :=
2114 TO_CHAR (l_output_tbl (e).cal_date, 'YYYYMMDDHH24MISS')
2115 || l_output_tbl (e).is_workday;
2116
2117 IF l_nb_record = 0 THEN
2118 l_cal_rec_tbl := l_cal_rec;
2119 l_nb_record := l_nb_record + 1;
2120 ELSE
2121 l_cal_rec_tbl := l_cal_rec_tbl || l_cal_rec;
2122 l_nb_record := l_nb_record + 1;
2123
2124 IF l_nb_record >= l_max_records THEN
2125 x_shop_cal_tbl (l_record_ind) := l_cal_rec_tbl;
2126 l_record_ind := l_record_ind + 1;
2127 l_nb_record := 0;
2128 END IF;
2129 END IF;
2130 END LOOP;
2131
2132 IF l_nb_record > 0 THEN
2133 x_shop_cal_tbl (l_record_ind) := l_cal_rec_tbl;
2134 END IF;
2135 END IF;
2136 EXCEPTION
2137 WHEN inv_calendar_sel THEN
2138 fnd_message.set_name ('GMD', 'GMD_UNEXPECTED_ERROR');
2139 fnd_message.set_token ('ERROR', SQLERRM);
2140 x_return_code := 'F';
2141 x_error_msg := fnd_message.get;
2142 WHEN fetch_cal_error THEN
2143 x_error_msg := r_pad (fnd_message.get, 1000);
2144 WHEN no_cal_assigned_to_org THEN
2145 x_return_code := 'F';
2146 WHEN OTHERS THEN
2147 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2148 END fetch_work_non_work_days;
2149
2150 /*======================================================================
2151 # Check whether batch/FPO/Step can be re-scheduled based on shop calendar.
2152 # Returns:
2153 # S : Batch/Step can be rescheduled
2154 # C : Prompt the user to reschedule without using shop calendar
2155 # P : Prompt the user to reschedule ignoring contiguity constraints
2156 # F : Batch/Step cannot be reschedule
2157 #======================================================================*/
2158 PROCEDURE validate_reschedule_event (
2159 p_batch_id IN NUMBER
2160 ,p_organization_id IN NUMBER
2161 ,p_primary_prod_no IN VARCHAR2
2162 ,p_start_date IN DATE
2163 ,p_end_date IN DATE
2164 ,p_entity_type IN VARCHAR2
2165 ,x_return_code OUT NOCOPY VARCHAR2
2166 ,x_error_msg OUT NOCOPY VARCHAR2)
2167 IS
2168 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Reschedule_Event';
2169 l_is_working_day BOOLEAN;
2170 l_contig_period_tbl gmp_calendar_api.contig_period_tbl;
2171 l_batch_duration NUMBER;
2172 l_contiguity_ind NUMBER;
2173 l_batch_no VARCHAR2 (32);
2174 non_work_time EXCEPTION;
2175 reschedule_error EXCEPTION;
2176 no_calendar_assigned EXCEPTION;
2177 BEGIN
2178 l_is_working_day := TRUE;
2179 x_return_code := 'S';
2180 x_error_msg := ' ';
2181
2182 IF g_mfg_calendar_code IS NULL THEN
2183 IF NOT get_mfg_calendar (p_organization_id) THEN
2184 RAISE no_calendar_assigned;
2185 END IF;
2186 END IF;
2187
2188 IF p_start_date IS NOT NULL THEN
2189 l_is_working_day :=
2190 gmp_calendar_api.is_working_daytime
2191 (p_api_version => 1
2192 ,p_init_msg_list => TRUE
2193 ,p_calendar_code => g_mfg_calendar_code
2194 ,p_date => p_start_date
2195 ,p_ind => 0
2196 , -- Start date
2197 x_return_status => x_return_code);
2198
2199 /* For now let's treat this condition as non-working day
2200 So the folowing code is commented. If the API returns
2201 Error or if the date is not working time, give same error
2202 IF x_return_code <> FND_API.G_RET_STS_SUCCESS THEN
2203 RAISE RESCHEDULE_ERROR;
2204 END IF; */
2205
2206 --- Warn the user that start date is a non working time
2207 IF NOT l_is_working_day THEN
2208 fnd_message.set_name ('GME', 'GME_SHOP_NON_WKG');
2209 --Bug3315440
2210 -- FND_MESSAGE.SET_TOKEN('PDATE', TO_CHAR(p_start_date,'DD-MON-YYYY HH24 MI SS'));
2211 --Bug4068469: removed trunc from the set_token call
2212 fnd_message.set_token ('PDATE'
2213 ,fnd_date.date_to_displaydt (p_start_date,2) );
2214 RAISE non_work_time;
2215 END IF;
2216 END IF;
2217
2218 IF p_end_date IS NOT NULL THEN
2219 l_is_working_day :=
2220 gmp_calendar_api.is_working_daytime
2221 (p_api_version => 1
2222 ,p_init_msg_list => TRUE
2223 ,p_calendar_code => g_mfg_calendar_code
2224 ,p_date => p_end_date
2225 ,p_ind => 1
2226 , -- End date
2227 x_return_status => x_return_code);
2228
2229 /* For now let's treat this condition as non-working day
2230 So the folowing code is commented. If the API returns
2231 Error or if the date is not working time, give same error
2232 IF x_return_code <> FND_API.G_RET_STS_SUCCESS THEN
2233 RAISE RESCHEDULE_ERROR;
2234 END IF; */
2235
2236 --- Warn the user that end date is a non working time
2237 IF NOT l_is_working_day THEN
2238 fnd_message.set_name ('GME', 'GME_SHOP_NON_WKG');
2239 --Bug3315440
2240 -- FND_MESSAGE.SET_TOKEN('PDATE', TO_CHAR(p_end_date,'DD-MON-YYYY HH24 MI SS'));
2241 --Bug4068469: removed trunc from the set_token call
2242 fnd_message.set_token ('PDATE'
2243 ,fnd_date.date_to_displaydt (p_end_date,2) );
2244 RAISE non_work_time;
2245 END IF;
2246 END IF;
2247 EXCEPTION
2248 WHEN non_work_time THEN
2249 x_return_code := 'C';
2250 x_error_msg := r_pad (fnd_message.get, 1000);
2251 WHEN reschedule_error THEN
2252 x_return_code := 'F';
2253 x_error_msg := r_pad (fnd_message.get, 1000);
2254 WHEN no_calendar_assigned THEN
2255 fnd_message.set_name ('GMP', 'GME_NO_CAL_ASSIGNED_TO_PLANT');
2256 x_return_code := 'F';
2257 x_error_msg := r_pad (fnd_message.get, 1000);
2258 WHEN OTHERS THEN
2259 --Bug4068469: set x_return_code and the x_error_msg
2260 x_return_code := 'U';
2261 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2262 x_error_msg := r_pad (fnd_message.get, 1000);
2263 END validate_reschedule_event;
2264
2265 /*======================================================================
2266 # For timezone changes.
2267 # Returns:
2268 # date : date in the client timezone
2269 # HISTORY
2270 # Bharati Satpute Bug3315440 21-JAN-2004
2271 #======================================================================*/
2272 FUNCTION date_to_clientdt (dateval DATE)
2273 RETURN DATE
2274 IS
2275 t_dateval DATE;
2276 tz_code VARCHAR2 (50);
2277 l_api_name CONSTANT VARCHAR2 (30) := 'date_to_clientDT';
2278 BEGIN
2279 t_dateval := dateval;
2280
2281 IF fnd_date.timezones_enabled THEN
2282 tz_code := fnd_date.client_timezone_code;
2283
2284 IF tz_code <> fnd_date.server_timezone_code THEN
2285 t_dateval :=
2286 fnd_timezones_pvt.adjust_datetime
2287 (dateval
2288 ,fnd_date.server_timezone_code
2289 ,fnd_date.client_timezone_code);
2290 END IF;
2291 END IF;
2292
2293 RETURN (t_dateval);
2294 EXCEPTION
2295 WHEN OTHERS THEN
2296 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2297 END date_to_clientdt;
2298 END;