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