[Home] [Help]
PACKAGE BODY: APPS.GME_BATCH_HEADER_DBL
Source
1 PACKAGE BODY gme_batch_header_dbl AS
2 /* $Header: GMEVGBHB.pls 120.4.12010000.2 2008/11/11 09:34:46 ssangane noship $ */
3
4 /* Global Variables */
5 g_table_name VARCHAR2 (80) DEFAULT 'GME_BATCH_HEADER';
6 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
7 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_BATCH_HEADER_DBL';
8
9 /* ===========================================================================
10 | Copyright (c) 2001 Oracle Corporation |
11 | TVP, Reading, England |
12 | All rights reserved |
13 ===========================================================================
14 | FILENAME |
15 | GMEVGBHB.pls |
16 | |
17 | DESCRIPTION |
18 | |
19 | Body of package gme_batch_header_dbl |
20 | |
21 | NOTES |
22 | |
23 | HISTORY |
24 | |
25 | 13-Feb-01 Created |
26 | |
27 | - create_row |
28 | - fetch_row |
29 | - update_row |
30 | - delete_row |
31 | 08-JUNE-2005 Pawan added enhanced_pi_ind for GMO. |
32 | |
33 ===========================================================================
34 */
35
36 /* Api start of comments
37 +==========================================================================+
38 | FUNCTION NAME |
39 | insert_row |
40 | |
41 | TYPE |
42 | Private |
43 | |
44 | USAGE |
45 | insert_Row will insert a row in gme_batch_header |
46 | |
47 | |
48 | DESCRIPTION |
49 | insert_Row will insert a row in gme_batch_header |
50 | |
51 | |
52 | PARAMETERS |
53 | p_batch_header IN gme_batch_header%ROWTYPE |
54 | x_batch_header IN OUT NOCOPY gme_batch_header%ROWTYPE |
55 | RETURNS |
56 | BOOLEAN |
57 | |
58 | HISTORY |
59 |
60 | |
61 +==========================================================================+
62 Api end of comments
63 */
64 FUNCTION insert_row (
65 p_batch_header IN gme_batch_header%ROWTYPE
66 ,x_batch_header IN OUT NOCOPY gme_batch_header%ROWTYPE)
67 RETURN BOOLEAN
68 IS
69 l_api_name CONSTANT VARCHAR2 (30) := 'INSERT_ROW';
70 l_wip_entity_id NUMBER;
71 l_prefix VARCHAR2 (32);
72 CURSOR cur_wip_entity
73 IS
74 SELECT wip_entities_s.NEXTVAL
75 FROM DUAL;
76 BEGIN
77 IF g_debug <= gme_debug.g_log_procedure THEN
78 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
79 || l_api_name);
80 END IF;
81
82 x_batch_header := p_batch_header;
83
84 OPEN cur_wip_entity;
85
86 FETCH cur_wip_entity
87 INTO l_wip_entity_id;
88
89 CLOSE cur_wip_entity;
90 IF (p_batch_header.batch_type = 0) THEN
91 l_prefix := FND_PROFILE.VALUE('GME_BATCH_PREFIX');
92 ELSE
93 l_prefix := FND_PROFILE.VALUE('GME_FPO_PREFIX');
94 END IF;
95 INSERT INTO wip_entities
96 (wip_entity_id, organization_id
97 ,last_update_date, last_updated_by
98 ,creation_date, created_by
99 ,wip_entity_name
100 ,entity_type
101 ,gen_object_id)
102 VALUES (l_wip_entity_id, x_batch_header.organization_id
103 ,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
104 ,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
105 ,l_prefix||x_batch_header.batch_no
106 ,DECODE (x_batch_header.batch_type
107 ,0, gme_common_pvt.g_wip_entity_type_batch
108 ,gme_common_pvt.g_wip_entity_type_fpo)
109 ,mtl_gen_object_id_s.NEXTVAL);
110
111 INSERT INTO gme_batch_header
112 (batch_id, batch_no
113 ,batch_type, prod_id
114 ,prod_sequence
115 ,recipe_validity_rule_id
116 ,formula_id, routing_id
117 ,plan_start_date
118 ,actual_start_date, due_date
119 ,plan_cmplt_date
120 ,actual_cmplt_date
121 ,batch_status
122 ,priority_value
123 ,priority_code, print_count
124 ,fmcontrol_class
125 --, WIP_WHSE_CODE
126 , batch_close_date, poc_ind
127 ,actual_cost_ind
128 ,gl_posted_ind
129 ,update_inventory_ind
130 ,last_update_date, last_updated_by
131 ,creation_date, created_by
132 ,last_update_login, delete_mark, text_code
133 ,parentline_id, fpo_id
134 ,attribute1, attribute2
135 ,attribute3, attribute4
136 ,attribute5, attribute6
137 ,attribute7, attribute8
138 ,attribute9, attribute10
139 ,attribute11, attribute12
140 ,attribute13, attribute14
141 ,attribute15, attribute16
142 ,attribute17, attribute18
143 ,attribute19, attribute20
144 ,attribute21, attribute22
145 ,attribute23, attribute24
146 ,attribute25, attribute26
147 ,attribute27, attribute28
148 ,attribute29, attribute30
149 ,attribute31, attribute32
150 ,attribute33, attribute34
151 ,attribute35, attribute36
152 ,attribute37, attribute38
153 ,attribute39, attribute40
154 ,attribute_category
155 ,automatic_step_calculation
156 ,firmed_ind
157 ,finite_scheduled_ind
158 ,order_priority
159 ,migrated_batch_ind
160 ,enforce_step_dependency, terminated_ind
161 ,organization_id
162 ,laboratory_ind
163 ,enhanced_pi_ind
164 ,fixed_process_loss_applied)
165 VALUES (l_wip_entity_id, x_batch_header.batch_no
166 ,x_batch_header.batch_type, x_batch_header.prod_id
167 ,x_batch_header.prod_sequence
168 ,x_batch_header.recipe_validity_rule_id
169 ,x_batch_header.formula_id, x_batch_header.routing_id
170 ,x_batch_header.plan_start_date
171 ,x_batch_header.actual_start_date, x_batch_header.due_date
172 ,x_batch_header.plan_cmplt_date
173 ,x_batch_header.actual_cmplt_date
174 ,x_batch_header.batch_status
175 ,x_batch_header.priority_value
176 ,x_batch_header.priority_code, x_batch_header.print_count
177 ,x_batch_header.fmcontrol_class
178 --, x_batch_header.WIP_WHSE_CODE
179 , x_batch_header.batch_close_date, x_batch_header.poc_ind
180 ,x_batch_header.actual_cost_ind
181 ,x_batch_header.gl_posted_ind
182 ,x_batch_header.update_inventory_ind
183 ,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
184 ,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
185 ,gme_common_pvt.g_login_id, 0, x_batch_header.text_code
186 ,x_batch_header.parentline_id, x_batch_header.fpo_id
187 ,x_batch_header.attribute1, x_batch_header.attribute2
188 ,x_batch_header.attribute3, x_batch_header.attribute4
189 ,x_batch_header.attribute5, x_batch_header.attribute6
190 ,x_batch_header.attribute7, x_batch_header.attribute8
191 ,x_batch_header.attribute9, x_batch_header.attribute10
192 ,x_batch_header.attribute11, x_batch_header.attribute12
193 ,x_batch_header.attribute13, x_batch_header.attribute14
194 ,x_batch_header.attribute15, x_batch_header.attribute16
195 ,x_batch_header.attribute17, x_batch_header.attribute18
196 ,x_batch_header.attribute19, x_batch_header.attribute20
197 ,x_batch_header.attribute21, x_batch_header.attribute22
198 ,x_batch_header.attribute23, x_batch_header.attribute24
199 ,x_batch_header.attribute25, x_batch_header.attribute26
200 ,x_batch_header.attribute27, x_batch_header.attribute28
201 ,x_batch_header.attribute29, x_batch_header.attribute30
202 ,x_batch_header.attribute31, x_batch_header.attribute32
203 ,x_batch_header.attribute33, x_batch_header.attribute34
204 ,x_batch_header.attribute35, x_batch_header.attribute36
205 ,x_batch_header.attribute37, x_batch_header.attribute38
206 ,x_batch_header.attribute39, x_batch_header.attribute40
207 ,x_batch_header.attribute_category
208 ,x_batch_header.automatic_step_calculation
209 ,x_batch_header.firmed_ind
210 ,x_batch_header.finite_scheduled_ind
211 ,x_batch_header.order_priority
212 ,x_batch_header.migrated_batch_ind
213 ,x_batch_header.enforce_step_dependency, 0
214 ,x_batch_header.organization_id
215 ,x_batch_header.laboratory_ind
216 ,x_batch_header.enhanced_pi_ind
217 ,x_batch_header.fixed_process_loss_applied);
218
219 x_batch_header.batch_id := l_wip_entity_id;
220
221 IF g_debug <= gme_debug.g_log_procedure THEN
222 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
223 END IF;
224
225 IF SQL%FOUND THEN
226 RETURN TRUE;
227 ELSE
228 RETURN FALSE;
229 END IF;
230 EXCEPTION
231 --Bug280440
232 WHEN OTHERS THEN
233 IF g_debug <= gme_debug.g_log_unexpected THEN
234 gme_debug.put_line ( 'When others exception in '
235 || g_pkg_name
236 || '.'
237 || l_api_name
238 || ' Error is '
239 || SQLERRM);
240 END IF;
241
242 x_batch_header.batch_id := NULL;
243 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
244 RETURN FALSE;
245 END insert_row;
246
247 /* Api start of comments
248 +==========================================================================+
249 | FUNCTION NAME |
250 | fetch_row |
251 | |
252 | TYPE |
253 | Private |
254 | |
255 | USAGE |
256 | fetch_Row will fetch a row in gme_batch_header |
257 | |
258 | |
259 | DESCRIPTION |
260 | fetch_row will fetch a row in gme_batch_header |
261 | |
262 | |
263 | PARAMETERS |
264 | p_batch_header IN gme_batch_header%ROWTYPE |
265 | x_batch_header IN OUT NOCOPY gme_batch_header%ROWTYPE |
269 | HISTORY |
266 | RETURNS |
267 | BOOLEAN |
268 | |
270 | |
271 +==========================================================================+
272 Api end of comments
273 */
274 FUNCTION fetch_row (
275 p_batch_header IN gme_batch_header%ROWTYPE
276 ,x_batch_header IN OUT NOCOPY gme_batch_header%ROWTYPE)
277 RETURN BOOLEAN
278 IS
279 l_gme_batch_header gme_batch_header%ROWTYPE;
280 l_batch_id NUMBER;
281 l_organization_id NUMBER;
282 l_batch_no VARCHAR2 (32);
283 l_batch_type NUMBER (5);
284 l_api_name CONSTANT VARCHAR2 (30) := 'fetch_row';
285 BEGIN
286 IF g_debug <= gme_debug.g_log_procedure THEN
287 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
288 || l_api_name);
289 END IF;
290
291 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
292 gme_debug.put_line ( 'Retrieving batch id: '
293 || TO_CHAR (p_batch_header.batch_id) );
294 END IF;
295
296 l_batch_id := p_batch_header.batch_id;
297 l_organization_id := p_batch_header.organization_id;
298 l_batch_no := p_batch_header.batch_no;
299 l_batch_type := p_batch_header.batch_type;
300
301 IF (l_batch_id IS NOT NULL) THEN
302 SELECT batch_id
303 --, PLANT_CODE
304 , batch_no
305 ,batch_type, prod_id
306 ,prod_sequence
307 ,recipe_validity_rule_id
308 ,formula_id
309 ,routing_id
310 ,plan_start_date
311 ,actual_start_date
312 ,due_date
313 ,plan_cmplt_date
314 ,actual_cmplt_date
315 ,batch_status
316 ,priority_value
317 ,priority_code
318 ,print_count
319 ,fmcontrol_class
320 --, WIP_WHSE_CODE
321 , batch_close_date
322 ,poc_ind
323 ,actual_cost_ind
324 ,gl_posted_ind
325 ,update_inventory_ind
326 ,last_update_date
327 ,last_updated_by
328 ,creation_date
329 ,created_by
330 ,last_update_login
331 ,delete_mark
332 ,text_code
333 ,parentline_id, fpo_id
334 ,attribute1
335 ,attribute2
336 ,attribute3
337 ,attribute4
338 ,attribute5
339 ,attribute6
340 ,attribute7
341 ,attribute8
342 ,attribute9
343 ,attribute10
344 ,attribute11
345 ,attribute12
346 ,attribute13
347 ,attribute14
348 ,attribute15
349 ,attribute16
350 ,attribute17
351 ,attribute18
352 ,attribute19
353 ,attribute20
354 ,attribute21
355 ,attribute22
356 ,attribute23
357 ,attribute24
358 ,attribute25
359 ,attribute26
360 ,attribute27
361 ,attribute28
362 ,attribute29
363 ,attribute30
364 ,attribute31
365 ,attribute32
366 ,attribute33
367 ,attribute34
368 ,attribute35
369 ,attribute36
370 ,attribute37
371 ,attribute38
372 ,attribute39
373 ,attribute40
374 ,attribute_category
375 ,automatic_step_calculation
376 ,firmed_ind
377 ,finite_scheduled_ind
378 ,order_priority
379 ,migrated_batch_ind
380 ,enforce_step_dependency
381 ,terminated_ind
382 ,organization_id
383 ,laboratory_ind
384 ,enhanced_pi_ind
385 ,move_order_header_id
386 ,terminate_reason_id
387 ,fixed_process_loss_applied
388 INTO l_gme_batch_header.batch_id
389 --, l_gme_batch_header.PLANT_CODE
390 , l_gme_batch_header.batch_no
391 ,l_gme_batch_header.batch_type, l_gme_batch_header.prod_id
392 ,l_gme_batch_header.prod_sequence
393 ,l_gme_batch_header.recipe_validity_rule_id
394 ,l_gme_batch_header.formula_id
395 ,l_gme_batch_header.routing_id
396 ,l_gme_batch_header.plan_start_date
397 ,l_gme_batch_header.actual_start_date
398 ,l_gme_batch_header.due_date
399 ,l_gme_batch_header.plan_cmplt_date
403 ,l_gme_batch_header.priority_code
400 ,l_gme_batch_header.actual_cmplt_date
401 ,l_gme_batch_header.batch_status
402 ,l_gme_batch_header.priority_value
404 ,l_gme_batch_header.print_count
405 ,l_gme_batch_header.fmcontrol_class
406 --, l_gme_batch_header.WIP_WHSE_CODE
407 , l_gme_batch_header.batch_close_date
408 ,l_gme_batch_header.poc_ind
409 ,l_gme_batch_header.actual_cost_ind
410 ,l_gme_batch_header.gl_posted_ind
411 ,l_gme_batch_header.update_inventory_ind
412 ,l_gme_batch_header.last_update_date
413 ,l_gme_batch_header.last_updated_by
414 ,l_gme_batch_header.creation_date
415 ,l_gme_batch_header.created_by
416 ,l_gme_batch_header.last_update_login
417 ,l_gme_batch_header.delete_mark
418 ,l_gme_batch_header.text_code
419 ,l_gme_batch_header.parentline_id, l_gme_batch_header.fpo_id
420 ,l_gme_batch_header.attribute1
421 ,l_gme_batch_header.attribute2
422 ,l_gme_batch_header.attribute3
423 ,l_gme_batch_header.attribute4
424 ,l_gme_batch_header.attribute5
425 ,l_gme_batch_header.attribute6
426 ,l_gme_batch_header.attribute7
427 ,l_gme_batch_header.attribute8
428 ,l_gme_batch_header.attribute9
429 ,l_gme_batch_header.attribute10
430 ,l_gme_batch_header.attribute11
431 ,l_gme_batch_header.attribute12
432 ,l_gme_batch_header.attribute13
433 ,l_gme_batch_header.attribute14
434 ,l_gme_batch_header.attribute15
435 ,l_gme_batch_header.attribute16
436 ,l_gme_batch_header.attribute17
437 ,l_gme_batch_header.attribute18
438 ,l_gme_batch_header.attribute19
439 ,l_gme_batch_header.attribute20
440 ,l_gme_batch_header.attribute21
441 ,l_gme_batch_header.attribute22
442 ,l_gme_batch_header.attribute23
443 ,l_gme_batch_header.attribute24
444 ,l_gme_batch_header.attribute25
445 ,l_gme_batch_header.attribute26
446 ,l_gme_batch_header.attribute27
447 ,l_gme_batch_header.attribute28
448 ,l_gme_batch_header.attribute29
449 ,l_gme_batch_header.attribute30
450 ,l_gme_batch_header.attribute31
451 ,l_gme_batch_header.attribute32
452 ,l_gme_batch_header.attribute33
453 ,l_gme_batch_header.attribute34
454 ,l_gme_batch_header.attribute35
455 ,l_gme_batch_header.attribute36
456 ,l_gme_batch_header.attribute37
457 ,l_gme_batch_header.attribute38
458 ,l_gme_batch_header.attribute39
459 ,l_gme_batch_header.attribute40
460 ,l_gme_batch_header.attribute_category
461 ,l_gme_batch_header.automatic_step_calculation
462 ,l_gme_batch_header.firmed_ind
463 ,l_gme_batch_header.finite_scheduled_ind
464 ,l_gme_batch_header.order_priority
465 ,l_gme_batch_header.migrated_batch_ind
466 ,l_gme_batch_header.enforce_step_dependency
467 ,l_gme_batch_header.terminated_ind
468 ,l_gme_batch_header.organization_id
469 ,l_gme_batch_header.laboratory_ind
470 ,l_gme_batch_header.enhanced_pi_ind
471 ,l_gme_batch_header.move_order_header_id
472 ,l_gme_batch_header.terminate_reason_id
473 ,l_gme_batch_header.fixed_process_loss_applied
474 FROM gme_batch_header
475 WHERE batch_id = p_batch_header.batch_id;
476 ELSIF (l_organization_id IS NOT NULL)
477 AND (l_batch_no IS NOT NULL)
478 AND (l_batch_type IS NOT NULL) THEN
479 SELECT batch_id
480 --, PLANT_CODE
481 , batch_no
482 ,batch_type, prod_id
483 ,prod_sequence
484 ,recipe_validity_rule_id
485 ,formula_id
486 ,routing_id
487 ,plan_start_date
488 ,actual_start_date
489 ,due_date
490 ,plan_cmplt_date
491 ,actual_cmplt_date
492 ,batch_status
493 ,priority_value
494 ,priority_code
495 ,print_count
496 ,fmcontrol_class
497 --, WIP_WHSE_CODE
498 , batch_close_date
499 ,poc_ind
500 ,actual_cost_ind
501 ,gl_posted_ind
502 ,update_inventory_ind
503 ,last_update_date
504 ,last_updated_by
505 ,creation_date
506 ,created_by
507 ,last_update_login
508 ,delete_mark
509 ,text_code
510 ,parentline_id, fpo_id
511 ,attribute1
512 ,attribute2
513 ,attribute3
514 ,attribute4
515 ,attribute5
516 ,attribute6
520 ,attribute10
517 ,attribute7
518 ,attribute8
519 ,attribute9
521 ,attribute11
522 ,attribute12
523 ,attribute13
524 ,attribute14
525 ,attribute15
526 ,attribute16
527 ,attribute17
528 ,attribute18
529 ,attribute19
530 ,attribute20
531 ,attribute21
532 ,attribute22
533 ,attribute23
534 ,attribute24
535 ,attribute25
536 ,attribute26
537 ,attribute27
538 ,attribute28
539 ,attribute29
540 ,attribute30
541 ,attribute31
542 ,attribute32
543 ,attribute33
544 ,attribute34
545 ,attribute35
546 ,attribute36
547 ,attribute37
548 ,attribute38
549 ,attribute39
550 ,attribute40
551 ,attribute_category
552 ,automatic_step_calculation
553 ,firmed_ind
554 ,finite_scheduled_ind
555 ,order_priority
556 ,migrated_batch_ind
557 ,enforce_step_dependency
558 ,terminated_ind
559 ,organization_id
560 ,laboratory_ind
561 ,enhanced_pi_ind
562 ,move_order_header_id
563 ,terminate_reason_id
564 ,fixed_process_loss_applied
565 INTO l_gme_batch_header.batch_id
566 --, l_gme_batch_header.PLANT_CODE
567 , l_gme_batch_header.batch_no
568 ,l_gme_batch_header.batch_type, l_gme_batch_header.prod_id
569 ,l_gme_batch_header.prod_sequence
570 ,l_gme_batch_header.recipe_validity_rule_id
571 ,l_gme_batch_header.formula_id
572 ,l_gme_batch_header.routing_id
573 ,l_gme_batch_header.plan_start_date
574 ,l_gme_batch_header.actual_start_date
575 ,l_gme_batch_header.due_date
576 ,l_gme_batch_header.plan_cmplt_date
577 ,l_gme_batch_header.actual_cmplt_date
578 ,l_gme_batch_header.batch_status
579 ,l_gme_batch_header.priority_value
580 ,l_gme_batch_header.priority_code
581 ,l_gme_batch_header.print_count
582 ,l_gme_batch_header.fmcontrol_class
583 --, l_gme_batch_header.WIP_WHSE_CODE
584 , l_gme_batch_header.batch_close_date
585 ,l_gme_batch_header.poc_ind
586 ,l_gme_batch_header.actual_cost_ind
587 ,l_gme_batch_header.gl_posted_ind
588 ,l_gme_batch_header.update_inventory_ind
589 ,l_gme_batch_header.last_update_date
590 ,l_gme_batch_header.last_updated_by
591 ,l_gme_batch_header.creation_date
592 ,l_gme_batch_header.created_by
593 ,l_gme_batch_header.last_update_login
594 ,l_gme_batch_header.delete_mark
595 ,l_gme_batch_header.text_code
596 ,l_gme_batch_header.parentline_id, l_gme_batch_header.fpo_id
597 ,l_gme_batch_header.attribute1
598 ,l_gme_batch_header.attribute2
599 ,l_gme_batch_header.attribute3
600 ,l_gme_batch_header.attribute4
601 ,l_gme_batch_header.attribute5
602 ,l_gme_batch_header.attribute6
603 ,l_gme_batch_header.attribute7
604 ,l_gme_batch_header.attribute8
605 ,l_gme_batch_header.attribute9
606 ,l_gme_batch_header.attribute10
607 ,l_gme_batch_header.attribute11
608 ,l_gme_batch_header.attribute12
609 ,l_gme_batch_header.attribute13
610 ,l_gme_batch_header.attribute14
611 ,l_gme_batch_header.attribute15
612 ,l_gme_batch_header.attribute16
613 ,l_gme_batch_header.attribute17
614 ,l_gme_batch_header.attribute18
615 ,l_gme_batch_header.attribute19
616 ,l_gme_batch_header.attribute20
617 ,l_gme_batch_header.attribute21
618 ,l_gme_batch_header.attribute22
619 ,l_gme_batch_header.attribute23
620 ,l_gme_batch_header.attribute24
621 ,l_gme_batch_header.attribute25
622 ,l_gme_batch_header.attribute26
623 ,l_gme_batch_header.attribute27
624 ,l_gme_batch_header.attribute28
625 ,l_gme_batch_header.attribute29
626 ,l_gme_batch_header.attribute30
627 ,l_gme_batch_header.attribute31
628 ,l_gme_batch_header.attribute32
629 ,l_gme_batch_header.attribute33
630 ,l_gme_batch_header.attribute34
631 ,l_gme_batch_header.attribute35
632 ,l_gme_batch_header.attribute36
633 ,l_gme_batch_header.attribute37
634 ,l_gme_batch_header.attribute38
635 ,l_gme_batch_header.attribute39
636 ,l_gme_batch_header.attribute40
637 ,l_gme_batch_header.attribute_category
641 ,l_gme_batch_header.order_priority
638 ,l_gme_batch_header.automatic_step_calculation
639 ,l_gme_batch_header.firmed_ind
640 ,l_gme_batch_header.finite_scheduled_ind
642 ,l_gme_batch_header.migrated_batch_ind
643 ,l_gme_batch_header.enforce_step_dependency
644 ,l_gme_batch_header.terminated_ind
645 ,l_gme_batch_header.organization_id
646 ,l_gme_batch_header.laboratory_ind
647 ,l_gme_batch_header.enhanced_pi_ind
648 ,l_gme_batch_header.move_order_header_id
649 ,l_gme_batch_header.terminate_reason_id
650 ,l_gme_batch_header.fixed_process_loss_applied
651 FROM gme_batch_header
652 WHERE organization_id = p_batch_header.organization_id
653 AND batch_no = p_batch_header.batch_no
654 AND batch_type = p_batch_header.batch_type;
655 ELSE
656 gme_common_pvt.log_message ('GME_NO_KEYS'
657 ,'TABLE_NAME'
658 ,g_table_name);
659 RETURN FALSE;
660 END IF;
661
662 IF g_debug <= gme_debug.g_log_procedure THEN
663 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
664 END IF;
665
666 x_batch_header := l_gme_batch_header;
667 RETURN TRUE;
668 EXCEPTION
669 WHEN NO_DATA_FOUND THEN
670 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
671 ,'TABLE_NAME'
672 ,g_table_name);
673 RETURN FALSE;
674 WHEN OTHERS THEN
675 IF g_debug <= gme_debug.g_log_unexpected THEN
676 gme_debug.put_line ( 'When others exception in '
677 || g_pkg_name
678 || '.'
679 || l_api_name
680 || ' Error is '
681 || SQLERRM);
682 END IF;
683
684 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
685 RETURN FALSE;
686 END fetch_row;
687
688 /* Api start of comments
689 +==========================================================================+
690 | FUNCTION NAME |
691 | delete_row |
692 | |
693 | TYPE |
694 | Private |
695 | |
696 | USAGE |
697 | delete_Row will delete a row in gme_batch_header |
698 | |
699 | |
700 | DESCRIPTION |
701 | delete_row will delete a row in gme_batch_header |
702 | |
703 | |
704 | PARAMETERS |
705 | p_batch_header IN gme_batch_header%ROWTYPE |
706 | RETURNS |
707 | BOOLEAN |
708 | |
709 | HISTORY |
710 |
711 +==========================================================================+
712 Api end of comments
713 */
714 FUNCTION delete_row (p_batch_header IN gme_batch_header%ROWTYPE)
715 RETURN BOOLEAN
716 IS
717 l_gme_batch_header gme_batch_header%ROWTYPE;
718 l_batch_id NUMBER;
719 l_organization_id NUMBER;
720 l_batch_no VARCHAR2 (32);
721 l_batch_type NUMBER (5);
722 l_dummy NUMBER (5) := 0;
723 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_ROW';
724 locked_by_other_user EXCEPTION;
725 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
726 BEGIN
727 IF g_debug <= gme_debug.g_log_procedure THEN
728 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
729 || l_api_name);
730 END IF;
731
732 l_batch_id := p_batch_header.batch_id;
733 l_organization_id := p_batch_header.organization_id;
734 l_batch_no := p_batch_header.batch_no;
735 l_batch_type := p_batch_header.batch_type;
736
737 IF l_batch_id IS NOT NULL THEN
738 SELECT 1
739 INTO l_dummy
740 FROM gme_batch_header
741 WHERE batch_id = l_batch_id
742 FOR UPDATE NOWAIT;
743
744 UPDATE gme_batch_header
745 SET delete_mark = 1
746 WHERE batch_id = p_batch_header.batch_id;
747 ELSIF (l_organization_id IS NOT NULL)
751 INTO l_dummy
748 AND (l_batch_no IS NOT NULL)
749 AND (l_batch_type IS NOT NULL) THEN
750 SELECT 1
752 FROM gme_batch_header
753 WHERE organization_id = p_batch_header.organization_id
754 AND batch_no = p_batch_header.batch_no
755 AND batch_type = p_batch_header.batch_type
756 FOR UPDATE NOWAIT;
757
758 UPDATE gme_batch_header
759 SET delete_mark = 1
760 WHERE organization_id = p_batch_header.organization_id
761 AND batch_no = p_batch_header.batch_no
762 AND batch_type = p_batch_header.batch_type;
763 ELSE
764 gme_common_pvt.log_message ('GME_NO_KEYS'
765 ,'TABLE_NAME'
766 ,g_table_name);
767 RETURN FALSE;
768 END IF;
769
770 IF (SQL%FOUND) THEN
771 RETURN TRUE;
772 ELSE
773 IF l_dummy = 0 THEN
774 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
775 ,'TABLE_NAME'
776 ,g_table_name);
777 ELSE
778 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
779 ,'TABLE_NAME'
780 ,g_table_name);
781 RETURN FALSE;
782 END IF;
783 END IF;
784
785 IF g_debug <= gme_debug.g_log_procedure THEN
786 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
787 END IF;
788
789 RETURN TRUE;
790 EXCEPTION
791 WHEN locked_by_other_user THEN
792 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
793 ,'TABLE_NAME'
794 ,g_table_name
795 ,'RECORD'
796 ,'Batch'
797 ,'KEY'
798 ,p_batch_header.batch_no);
799 RETURN FALSE;
800 --Bug2804440
801 WHEN OTHERS THEN
802 IF g_debug <= gme_debug.g_log_unexpected THEN
803 gme_debug.put_line ( 'When others exception in '
804 || g_pkg_name
805 || '.'
806 || l_api_name
807 || ' Error is '
808 || SQLERRM);
809 END IF;
810
811 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
812 RETURN FALSE;
813 END delete_row;
814
815 /* Api start of comments
816 +==========================================================================+
817 | FUNCTION NAME |
818 | update_row |
819 | |
820 | TYPE |
821 | Private |
822 | |
823 | USAGE |
824 | update_row will update a row in gme_batch_header |
825 | |
826 | |
827 | DESCRIPTION |
828 | update_row will update a row in gme_batch_header |
829 | |
830 | |
831 | PARAMETERS |
832 | p_batch_header IN gme_batch_header%ROWTYPE |
833 | RETURNS |
834 | BOOLEAN |
835 | |
836 | HISTORY |
837 |
838 | |
839 +==========================================================================+
840 Api end of comments
841 */
842 FUNCTION update_row (p_batch_header IN gme_batch_header%ROWTYPE)
843 RETURN BOOLEAN
844 IS
845 l_dummy NUMBER := 0;
846 l_batch_id NUMBER;
847 l_organization_id NUMBER;
848 l_batch_no VARCHAR2 (32);
849 l_batch_type NUMBER (5);
850 locked_by_other_user EXCEPTION;
851 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
852 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_ROW';
853 BEGIN
854 IF g_debug <= gme_debug.g_log_procedure THEN
855 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
856 || l_api_name);
857 END IF;
858
859 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
863
860 gme_debug.put_line ( 'Inside update_row for batch_id: '
861 || TO_CHAR (p_batch_header.batch_id) );
862 END IF;
864 l_batch_id := p_batch_header.batch_id;
865 l_organization_id := p_batch_header.organization_id;
866 l_batch_no := p_batch_header.batch_no;
867 l_batch_type := p_batch_header.batch_type;
868
869 IF l_batch_id IS NOT NULL THEN
870 SELECT 1
871 INTO l_dummy
872 FROM gme_batch_header
873 WHERE batch_id = l_batch_id
874 FOR UPDATE NOWAIT;
875
876 UPDATE gme_batch_header
877 SET recipe_validity_rule_id =
878 p_batch_header.recipe_validity_rule_id
879 ,formula_id = p_batch_header.formula_id
880 ,routing_id = p_batch_header.routing_id
881 ,plan_start_date = p_batch_header.plan_start_date
882 ,actual_start_date = p_batch_header.actual_start_date
883 ,due_date = p_batch_header.due_date
884 ,plan_cmplt_date = p_batch_header.plan_cmplt_date
885 ,actual_cmplt_date = p_batch_header.actual_cmplt_date
886 ,batch_status = p_batch_header.batch_status
887 ,print_count = p_batch_header.print_count
888 ,
889 --WIP_WHSE_CODE = p_batch_header.WIP_WHSE_CODE,
890 batch_close_date = p_batch_header.batch_close_date
891 ,poc_ind = p_batch_header.poc_ind
892 ,actual_cost_ind = p_batch_header.actual_cost_ind
893 ,gl_posted_ind = p_batch_header.gl_posted_ind
894 ,update_inventory_ind = p_batch_header.update_inventory_ind
895 ,last_update_date = gme_common_pvt.g_timestamp
896 ,last_updated_by = gme_common_pvt.g_user_ident
897 ,last_update_login = gme_common_pvt.g_login_id
898 ,delete_mark = p_batch_header.delete_mark
899 ,text_code = p_batch_header.text_code
900 ,parentline_id = p_batch_header.parentline_id
901 ,fpo_id = p_batch_header.fpo_id
902 ,attribute1 = p_batch_header.attribute1
903 ,attribute2 = p_batch_header.attribute2
904 ,attribute3 = p_batch_header.attribute3
905 ,attribute4 = p_batch_header.attribute4
906 ,attribute5 = p_batch_header.attribute5
907 ,attribute6 = p_batch_header.attribute6
908 ,attribute7 = p_batch_header.attribute7
909 ,attribute8 = p_batch_header.attribute8
910 ,attribute9 = p_batch_header.attribute9
911 ,attribute10 = p_batch_header.attribute10
912 ,attribute11 = p_batch_header.attribute11
913 ,attribute12 = p_batch_header.attribute12
914 ,attribute13 = p_batch_header.attribute13
915 ,attribute14 = p_batch_header.attribute14
916 ,attribute15 = p_batch_header.attribute15
917 ,attribute16 = p_batch_header.attribute16
918 ,attribute17 = p_batch_header.attribute17
919 ,attribute18 = p_batch_header.attribute18
920 ,attribute19 = p_batch_header.attribute19
921 ,attribute20 = p_batch_header.attribute20
922 ,attribute21 = p_batch_header.attribute21
923 ,attribute22 = p_batch_header.attribute22
924 ,attribute23 = p_batch_header.attribute23
925 ,attribute24 = p_batch_header.attribute24
926 ,attribute25 = p_batch_header.attribute25
927 ,attribute26 = p_batch_header.attribute26
928 ,attribute27 = p_batch_header.attribute27
929 ,attribute28 = p_batch_header.attribute28
930 ,attribute29 = p_batch_header.attribute29
931 ,attribute30 = p_batch_header.attribute30
932 ,attribute31 = p_batch_header.attribute31
933 ,attribute32 = p_batch_header.attribute32
934 ,attribute33 = p_batch_header.attribute33
935 ,attribute34 = p_batch_header.attribute34
936 ,attribute35 = p_batch_header.attribute35
937 ,attribute36 = p_batch_header.attribute36
938 ,attribute37 = p_batch_header.attribute37
939 ,attribute38 = p_batch_header.attribute38
940 ,attribute39 = p_batch_header.attribute39
941 ,attribute40 = p_batch_header.attribute40
942 ,attribute_category = p_batch_header.attribute_category
943 ,automatic_step_calculation =
944 p_batch_header.automatic_step_calculation
945 ,firmed_ind = p_batch_header.firmed_ind
946 ,finite_scheduled_ind = p_batch_header.finite_scheduled_ind
947 ,order_priority = p_batch_header.order_priority
948 ,migrated_batch_ind = p_batch_header.migrated_batch_ind
949 ,enforce_step_dependency =
950 p_batch_header.enforce_step_dependency
951 ,terminated_ind = p_batch_header.terminated_ind
952 ,enhanced_pi_ind = p_batch_header.enhanced_pi_ind
953 ,move_order_header_id = p_batch_header.move_order_header_id
954 ,terminate_reason_id = p_batch_header.terminate_reason_id
955 ,fixed_process_loss_applied = p_batch_header.fixed_process_loss_applied
956 WHERE batch_id = p_batch_header.batch_id
960 AND (l_batch_type IS NOT NULL) THEN
957 AND last_update_date = p_batch_header.last_update_date;
958 ELSIF (l_organization_id IS NOT NULL)
959 AND (l_batch_no IS NOT NULL)
961 SELECT 1
962 INTO l_dummy
963 FROM gme_batch_header
964 WHERE organization_id = p_batch_header.organization_id
965 AND batch_no = p_batch_header.batch_no
966 AND batch_type = p_batch_header.batch_type
967 FOR UPDATE NOWAIT;
968
969 UPDATE gme_batch_header
970 SET organization_id = p_batch_header.organization_id
971 ,batch_no = p_batch_header.batch_no
972 ,batch_type = p_batch_header.batch_type
973 ,prod_id = p_batch_header.prod_id
974 ,prod_sequence = p_batch_header.prod_sequence
975 ,recipe_validity_rule_id =
976 p_batch_header.recipe_validity_rule_id
977 ,formula_id = p_batch_header.formula_id
978 ,routing_id = p_batch_header.routing_id
979 ,plan_start_date = p_batch_header.plan_start_date
980 ,actual_start_date = p_batch_header.actual_start_date
981 ,due_date = p_batch_header.due_date
982 ,plan_cmplt_date = p_batch_header.plan_cmplt_date
983 ,actual_cmplt_date = p_batch_header.actual_cmplt_date
984 ,batch_status = p_batch_header.batch_status
985 ,priority_value = p_batch_header.priority_value
986 ,priority_code = p_batch_header.priority_code
987 ,print_count = p_batch_header.print_count
988 ,fmcontrol_class = p_batch_header.fmcontrol_class
989 ,
990 --WIP_WHSE_CODE = p_batch_header.WIP_WHSE_CODE,
991 batch_close_date = p_batch_header.batch_close_date
992 ,poc_ind = p_batch_header.poc_ind
993 ,actual_cost_ind = p_batch_header.actual_cost_ind
994 ,gl_posted_ind = p_batch_header.gl_posted_ind
995 ,update_inventory_ind = p_batch_header.update_inventory_ind
996 ,last_update_date = gme_common_pvt.g_timestamp
997 ,last_updated_by = gme_common_pvt.g_user_ident
998 ,last_update_login = gme_common_pvt.g_login_id
999 ,delete_mark = p_batch_header.delete_mark
1000 ,text_code = p_batch_header.text_code
1001 ,parentline_id = p_batch_header.parentline_id
1002 ,fpo_id = p_batch_header.fpo_id
1003 ,attribute1 = p_batch_header.attribute1
1004 ,attribute2 = p_batch_header.attribute2
1005 ,attribute3 = p_batch_header.attribute3
1006 ,attribute4 = p_batch_header.attribute4
1007 ,attribute5 = p_batch_header.attribute5
1008 ,attribute6 = p_batch_header.attribute6
1009 ,attribute7 = p_batch_header.attribute7
1010 ,attribute8 = p_batch_header.attribute8
1011 ,attribute9 = p_batch_header.attribute9
1012 ,attribute10 = p_batch_header.attribute10
1013 ,attribute11 = p_batch_header.attribute11
1014 ,attribute12 = p_batch_header.attribute12
1015 ,attribute13 = p_batch_header.attribute13
1016 ,attribute14 = p_batch_header.attribute14
1017 ,attribute15 = p_batch_header.attribute15
1018 ,attribute16 = p_batch_header.attribute16
1019 ,attribute17 = p_batch_header.attribute17
1020 ,attribute18 = p_batch_header.attribute18
1021 ,attribute19 = p_batch_header.attribute19
1022 ,attribute20 = p_batch_header.attribute20
1023 ,attribute21 = p_batch_header.attribute21
1024 ,attribute22 = p_batch_header.attribute22
1025 ,attribute23 = p_batch_header.attribute23
1026 ,attribute24 = p_batch_header.attribute24
1027 ,attribute25 = p_batch_header.attribute25
1028 ,attribute26 = p_batch_header.attribute26
1029 ,attribute27 = p_batch_header.attribute27
1030 ,attribute28 = p_batch_header.attribute28
1031 ,attribute29 = p_batch_header.attribute29
1032 ,attribute30 = p_batch_header.attribute30
1033 ,attribute31 = p_batch_header.attribute31
1034 ,attribute32 = p_batch_header.attribute32
1035 ,attribute33 = p_batch_header.attribute33
1036 ,attribute34 = p_batch_header.attribute34
1037 ,attribute35 = p_batch_header.attribute35
1038 ,attribute36 = p_batch_header.attribute36
1039 ,attribute37 = p_batch_header.attribute37
1040 ,attribute38 = p_batch_header.attribute38
1041 ,attribute39 = p_batch_header.attribute39
1042 ,attribute40 = p_batch_header.attribute40
1043 ,attribute_category = p_batch_header.attribute_category
1044 ,automatic_step_calculation =
1045 p_batch_header.automatic_step_calculation
1046 ,firmed_ind = p_batch_header.firmed_ind
1047 ,finite_scheduled_ind = p_batch_header.finite_scheduled_ind
1048 ,order_priority = p_batch_header.order_priority
1049 ,migrated_batch_ind = p_batch_header.migrated_batch_ind
1050 ,enforce_step_dependency =
1051 p_batch_header.enforce_step_dependency
1052 ,terminated_ind = p_batch_header.terminated_ind
1056 ,fixed_process_loss_applied = p_batch_header.fixed_process_loss_applied
1053 ,enhanced_pi_ind = p_batch_header.enhanced_pi_ind
1054 ,move_order_header_id = p_batch_header.move_order_header_id
1055 ,terminate_reason_id = p_batch_header.terminate_reason_id
1057 WHERE organization_id = p_batch_header.organization_id
1058 AND batch_no = p_batch_header.batch_no
1059 AND batch_type = p_batch_header.batch_type
1060 AND last_update_date = p_batch_header.last_update_date;
1061 ELSE
1062 gme_common_pvt.log_message ('GME_NO_KEYS'
1063 ,'TABLE_NAME'
1064 ,g_table_name);
1065 RETURN FALSE;
1066 END IF;
1067 IF SQL%ROWCOUNT <> 0 THEN
1068 IF g_debug <= gme_debug.g_log_procedure THEN
1069 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1070 END IF;
1071 RETURN TRUE;
1072 ELSE
1073 RAISE NO_DATA_FOUND;
1074 END IF;
1075
1076 RETURN TRUE;
1077 EXCEPTION
1078 WHEN NO_DATA_FOUND THEN
1079 IF l_dummy = 0 THEN
1080 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1081 ,'TABLE_NAME'
1082 ,g_table_name);
1083 ELSE
1084 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
1085 ,'TABLE_NAME'
1086 ,g_table_name);
1087 END IF;
1088
1089 RETURN FALSE;
1090 WHEN locked_by_other_user THEN
1091 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
1092 ,'TABLE_NAME'
1093 ,g_table_name
1094 ,'RECORD'
1095 ,'Batch'
1096 ,'KEY'
1097 ,p_batch_header.batch_no);
1098 RETURN FALSE;
1099 --Bug2804440
1100 WHEN OTHERS THEN
1101 IF g_debug <= gme_debug.g_log_unexpected THEN
1102 gme_debug.put_line ( 'When others exception in '
1103 || g_pkg_name
1104 || '.'
1105 || l_api_name
1106 || ' Error is '
1107 || SQLERRM);
1108 END IF;
1109
1110 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1111 RETURN FALSE;
1112 END update_row;
1113
1114 /*
1115 +============================================================================
1116 | FUNCTION NAME
1117 | lock_row
1118 |
1119 | TYPE
1120 | Private
1121 | USAGE
1122 | Lock_Row will lock a row in gme_batch_header
1123 |
1124 |
1125 | DESCRIPTION
1126 | Lock_Row will lock a row in gme_batch_header
1127 |
1128 |
1129 |
1130 | PARAMETERS
1131 | p_batch_header IN gme_batch_header%ROWTYPE
1132 |
1133 | RETURNS
1134 | BOOLEAN
1135 | HISTORY
1136 | 12-MAR-01 Olivier Daboval Created
1137 |
1138 |
1139 |
1140 +=============================================================================
1141 */
1142 FUNCTION lock_row (p_batch_header IN gme_batch_header%ROWTYPE)
1143 RETURN BOOLEAN
1144 IS
1145 l_dummy NUMBER;
1146 l_api_name CONSTANT VARCHAR2 (30) := 'LOCK_ROW';
1147 BEGIN
1148 IF g_debug <= gme_debug.g_log_procedure THEN
1149 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1150 || l_api_name);
1151 END IF;
1152
1153 IF p_batch_header.batch_id IS NOT NULL THEN
1154 SELECT 1
1155 INTO l_dummy
1156 FROM gme_batch_header
1157 WHERE batch_id = p_batch_header.batch_id
1158 FOR UPDATE NOWAIT;
1159 ELSIF ( p_batch_header.organization_id IS NOT NULL
1160 AND p_batch_header.batch_no IS NOT NULL
1161 AND p_batch_header.batch_type IS NOT NULL) THEN
1162 SELECT 1
1163 INTO l_dummy
1164 FROM gme_batch_header
1165 WHERE organization_id = p_batch_header.organization_id
1166 AND batch_no = p_batch_header.batch_no
1167 AND batch_type = p_batch_header.batch_type
1168 FOR UPDATE NOWAIT;
1169 ELSE
1170 gme_common_pvt.log_message ('GME_NO_KEYS'
1171 ,'TABLE_NAME'
1172 ,g_table_name);
1173 RETURN FALSE;
1174 END IF;
1175
1176 IF g_debug <= gme_debug.g_log_procedure THEN
1177 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1178 END IF;
1179
1180 RETURN TRUE;
1181 EXCEPTION
1182 WHEN app_exception.record_lock_exception THEN
1183 gme_common_pvt.log_message ('GME_RECORD_LOCKED'
1184 ,'TABLE_NAME'
1185 ,g_table_name
1186 ,'RECORD'
1187 ,'Batch'
1188 ,'KEY'
1189 ,p_batch_header.batch_no);
1190 RETURN FALSE;
1191 WHEN OTHERS THEN
1192 IF g_debug <= gme_debug.g_log_unexpected THEN
1193 gme_debug.put_line ( 'When others exception in '
1194 || g_pkg_name
1195 || '.'
1196 || l_api_name
1197 || ' Error is '
1198 || SQLERRM);
1199 END IF;
1200
1201 --Bug2804440
1202 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1203 RETURN FALSE;
1204 END lock_row;
1205 END gme_batch_header_dbl;