[Home] [Help]
PACKAGE BODY: APPS.CSD_HV_WIP_JOB_PVT
Source
1 PACKAGE BODY CSD_HV_WIP_JOB_PVT as
2 /* $Header: csdvhvjb.pls 120.30.12010000.3 2008/10/15 20:37:33 swai ship $ */
3 -- Start of Comments
4 -- Package name : CSD_HV_WIP_JOB_PVT
5 -- Purpose : This package is used for High Volume Repair Execution flow
6 --
7 --
8 -- History : 05/01/2005, Created by Shiv Ragunathan
9 -- History :
10 -- History :
11 -- NOTE :
12 -- End of Comments
13
14
15 -- Define Global Variable --
16 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_HV_WIP_JOB_PVT';
17
18 -- swai: bug 6995498/7182047 wrapper function to get the default item revision
19 -- Depending on the transaction type, check the corresponding profile option
20 -- and return null if the profile is No.
21 -- Transaction types are 'MAT_ISSUE' and 'JOB_COMP'. Passing null for
22 -- transaction type will always return a default from bom_revsions API.
23 FUNCTION get_default_item_revision
24 (
25 p_org_id IN NUMBER,
26 p_inventory_item_id IN NUMBER,
27 p_transaction_date IN DATE,
28 p_mat_transaction_type IN VARCHAR2 := null
29 ) RETURN VARCHAR2
30 IS
31 -- variables --
32 l_revision VARCHAR2(3) := null;
33 l_get_default VARCHAR2(1) := FND_API.G_TRUE;
34 BEGIN
35 if (p_mat_transaction_type = 'MAT_ISSUE') then -- material issue
36 --swai: bug 6654197 - allow user to specify revision based on profile option
37 if (nvl(fnd_profile.value('CSD_DEF_CUR_REVISION_MTL_TXN'), 'Y') = 'N' ) then
38 l_get_default := FND_API.G_FALSE;
39 end if;
40 elsif (p_mat_transaction_type = 'JOB_COMP') then -- job completion
41 -- swai: bug 6654197 - allow user to specify revision based on profile option
42 if (nvl(fnd_profile.value('CSD_DEF_CUR_REVISION_JOB_COMP'), 'Y') = 'N' ) then
43 l_get_default := FND_API.G_FALSE;
44 end if;
45 end if;
46 if (l_get_default = FND_API.G_TRUE) then
47 l_revision := bom_revisions.get_item_revision_fn
48 ('EXCLUDE_OPEN_HOLD', -- eco_status
49 'ALL', -- examine_type
50 p_org_id, -- org_id
51 p_inventory_item_id, -- item_id
52 p_transaction_date -- rev_date
53 ) ;
54 end if;
55 return l_revision;
56 END get_default_item_revision;
57
58 FUNCTION get_pending_quantity( p_wip_entity_id NUMBER,
59 p_operation_seq_num NUMBER,
60 p_resource_seq_num NUMBER,
61 p_primary_uom VARCHAR2 )
62 RETURN NUMBER IS
63
64
65 Cursor get_pending_qty_uom is
66 select wcti.transaction_quantity,
67 wcti.transaction_uom from
68 wip_cost_txn_interface wcti
69 where
70 wcti.wip_entity_id = p_wip_entity_id and
71 wcti.operation_seq_num = p_operation_seq_num and
72 wcti.resource_seq_num = p_resource_seq_num and
73 process_phase = 1 and
74 process_status = 1;
75
76 l_sum_pending_qty NUMBER := 0;
77 l_conversion_rate NUMBER;
78 l_primary_qty NUMBER;
79 BEGIN
80
81 FOR pending_qty_rec in get_pending_qty_uom
82 LOOP
83
84
85
86 l_conversion_rate :=
87 inv_convert.inv_um_convert(
88 item_id => 0,
89 precision => 38,
90 from_quantity => 1,
91 from_unit => p_primary_uom,
92 to_unit => pending_qty_rec.transaction_uom ,
93 from_name => NULL,
94 to_name => NULL);
95
96
97
98 -- perform UOM conversion
99 l_primary_qty := pending_qty_rec.transaction_quantity/l_conversion_rate;
100
101 l_sum_pending_qty := l_sum_pending_qty + l_primary_qty;
102
103 END LOOP;
104
105
106 RETURN l_sum_pending_qty;
107
108
109 END;
110
111
112 FUNCTION ml_error_exists( p_group_id NUMBER )
113 RETURN BOOLEAN IS
114
115 lc_error_process_status CONSTANT NUMBER := 3;
116
117
118 Cursor check_ml_interface_errors IS
119 select 'exists' from wip_job_schedule_interface where
120 group_id = p_group_id
121 and process_status = lc_error_process_status;
122
123 l_error_exists VARCHAR2(6);
124
125 BEGIN
126
127 open check_ml_interface_errors ;
128 fetch check_ml_interface_errors into l_error_exists;
129 close check_ml_interface_errors ;
130
131 If l_error_exists is null then
132 RETURN FALSE;
133 else
134 RETURN TRUE;
135 end if;
136
137 END;
138
139 FUNCTION txn_int_error_exists( p_transaction_header_id NUMBER )
140 RETURN BOOLEAN IS
141
142 lc_error_process_status CONSTANT NUMBER := 3;
143
144
145 Cursor check_txn_int_interface_errors IS
146 select 'exists' from mtl_transactions_interface where
147 transaction_header_id = p_transaction_header_id
148 and process_flag = lc_error_process_status;
149
150 l_error_exists VARCHAR2(6);
151
152 BEGIN
153
154 open check_txn_int_interface_errors ;
155 fetch check_txn_int_interface_errors into l_error_exists;
156 close check_txn_int_interface_errors ;
157
158 If l_error_exists is null then
159 RETURN FALSE;
160 else
161 RETURN TRUE;
162 end if;
163
164 END;
165
166
167 -- this procedure adds any errors from the wip_interface_errors table
168 -- to the fnd_message stack.
169 -- assumption: each group has one type of transaction (op, material, resource)
170 --
171 PROCEDURE add_wip_interface_errors(p_group_id NUMBER, p_txn_type NUMBER) IS
172 -- CONSTANTS --
173 lc_txn_type_operations CONSTANT NUMBER := 1;
174 lc_txn_type_materials CONSTANT NUMBER := 2;
175 lc_txn_type_resources CONSTANT NUMBER := 3;
176
177 -- LOCAL VARIABLES --
178 l_resource_name VARCHAR2(10);
179 l_item_name VARCHAR2(10);
180
181 -- CURSORS --
182 Cursor c_wip_interface_errors is
183 select wie.error,
184 wie.error_type,
185 wie.error_type_meaning,
186 wjsi.wip_entity_id,
187 wjsi.organization_id,
188 we.wip_entity_name,
189 wjdi.operation_seq_num,
190 wjdi.inventory_item_id_new,
191 wjdi.resource_id_new
192 from
193 wip_interface_errors_v wie,
194 wip_job_schedule_interface wjsi,
195 wip_entities we,
196 wip_job_dtls_interface wjdi
197 where wie.interface_id = wjsi.interface_id
198 and we.wip_entity_id = wjsi.wip_entity_id
199 and wjsi.group_id = wjdi.group_id
200 and wjsi.group_id = p_group_id;
201
202 Cursor c_resource_name (p_resource_id_new number) is
203 select bom.resource_code
204 from bom_resources bom
205 where bom.resource_id = p_resource_id_new;
206
207 Cursor c_item_name (p_inventory_item_id_new number,
208 p_organization_id number) is
209 select mtl.concatenated_segments
210 from mtl_system_items_kfv mtl
211 where mtl.inventory_item_id = p_inventory_item_id_new
212 and mtl.organization_id = p_organization_id;
213 BEGIN
214
215 FOR wip_interface_rec in c_wip_interface_errors
216 LOOP
217 IF (p_txn_type = lc_txn_type_operations) THEN
218 FND_MESSAGE.SET_NAME('CSD','CSD_WIP_INTERFACE_OP_ERR');
219
220 ELSIF (p_txn_type = lc_txn_type_materials) THEN
221 FND_MESSAGE.SET_NAME('CSD','CSD_WIP_INTERFACE_MTL_ERR');
222 open c_item_name(wip_interface_rec.inventory_item_id_new,
223 wip_interface_rec.organization_id);
224 fetch c_item_name into l_item_name;
225 close c_item_name;
226 FND_MESSAGE.SET_TOKEN('ITEM_NAME', l_item_name);
227
228 ELSIF (p_txn_type = lc_txn_type_resources) THEN
229 FND_MESSAGE.SET_NAME('CSD','CSD_WIP_INTERFACE_RES_ERR');
230 open c_resource_name(wip_interface_rec.resource_id_new);
231 fetch c_resource_name into l_resource_name;
232 close c_resource_name;
233 FND_MESSAGE.SET_TOKEN('RES_NAME', l_resource_name);
234 END IF;
235
236 FND_MESSAGE.SET_TOKEN('JOB_NAME', wip_interface_rec.wip_entity_name);
237 FND_MESSAGE.SET_TOKEN('OP_SEQ', wip_interface_rec.operation_seq_num);
238 FND_MESSAGE.SET_TOKEN('ERROR_TYPE', wip_interface_rec.error_type_meaning);
239 FND_MESSAGE.SET_TOKEN('ERROR_MSG', wip_interface_rec.error);
240 FND_MSG_PUB.ADD;
241 END LOOP;
242 END add_wip_interface_errors;
243
244 -- This procedure checks if the specified Job name exists in the
245 -- specified organization. It checks if it exists in
246 -- wip_entities or wip_job_schedule_interface table.
247 -- If it exists, then an Error status is returned.
248 -- If it does not exist in either of the tables, then
249 -- a Sucess status is returned.
250 -- This procedure is used whenever a job_name is generated, to confirm that
251 -- the newly generated job_name does not already exist and hence can be
252 -- used to submit it to WIP Mass Load.
253
254
255 PROCEDURE validate_job_name
256 (
257 p_job_name IN VARCHAR2,
258 p_organization_id IN NUMBER,
259 x_return_status OUT NOCOPY VARCHAR2
260 )
261 IS
262
263 -- Used to check the existence of the Job_name for the specified organizization,
264 l_job_count NUMBER := 0;
265
266 BEGIN
267
268
269 Select count(*) into l_job_count from wip_entities where wip_entity_name = p_job_name and
270 organization_id = p_organization_id ;
271
272 If l_job_count = 0 Then
273
274 -- Job does not exist in WIP_entities, check if it is already inserted in the interface table by another
275 -- process and so may be in the process of getting into WIP.
276 -- If it exists, do not want to use this job name, so return Error
277
278 Select count(*) into l_job_count from wip_job_schedule_interface where job_name = p_job_name and
279 organization_id = p_organization_id ;
280
281 IF l_job_count = 0 THEN
282
283 -- Generated job name does exist either in the interface or wip_entities table,
284 -- Success is returned
285
286 x_return_status := FND_API.G_RET_STS_SUCCESS;
287 RETURN;
288
289 ELSE
290
291 -- Job exists in wip_job_schedule_interface table, hence return Error status
292
293 x_return_status := FND_API.G_RET_STS_ERROR;
294 RETURN;
295
296
297 END IF;
298
299
300 ELSE
301
302 -- Job exists in wip_entities table, hence return Error status
303
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 RETURN;
306
307
308 END IF;
309
310
311
312 END validate_job_name;
313
314
315 -- This procedure generates a job name by appending a sequence generated number
316 -- to the passed in Job_Prefix
317 -- It Validates that the generated job name is unique for the specified organization,
318 -- It keeps looping and appending the subsequent sequence generated number, till a
319 -- unique Job name is generated
320
321
322 PROCEDURE generate_job_name
323 (
324 p_job_prefix IN VARCHAR2,
325 p_organization_id IN NUMBER,
326 x_job_name OUT NOCOPY VARCHAR2
327 )
328 IS
329
330 l_return_status VARCHAR2(1);
331
332 BEGIN
333
334 Loop
335
336 -- generate the Job Name by appending a sequence generated number to the passed in
337 -- job_prefix
338
339 Select p_job_prefix || TO_CHAR( CSD_JOB_NAME_S.NEXTVAL ) into
340 x_job_name From Dual;
341
342
343 -- Check if the job name generated is unique for the specified organization,
344 -- if not loop around till a unique job name is generated
345
346 Validate_job_name ( p_job_name => x_job_name,
347 p_organization_id => p_organization_id,
348 x_return_status => l_return_status ) ;
349
350 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
351
352 -- Generated job name does not exist both in the interface and wip_entities table, so exit the loop
353
354 exit;
355
356 END IF;
357
358
359 End Loop;
360
361 END generate_job_name;
362
363
364 -- This procedure accepts job header, bills and routing information and inserts it into
365 -- WIP_JOB_SCHEDULE_INTERFACE table.
366
367 PROCEDURE insert_job_header
368 (
369 p_job_header_rec IN wip_job_schedule_interface%ROWTYPE,
370 x_return_status OUT NOCOPY VARCHAR2
371 )
372 IS
373
374 -- Job Record to hold the Job header, bills and routing information being inserted
375 -- into wip_job_schedule_interface
376
377 l_job_header_rec wip_job_schedule_interface%ROWTYPE := p_job_header_rec;
378
379
380 -- variables used for FND_LOG debug messages
381
382 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
383 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
384 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
385
386
387 -- Constants Used for Inserting into wip_job_schedule_interface,
388 -- These are the values needed for WIP Mass Load to pick up the records
389
390 -- Indicates that the process Phase is Validation
391 lc_validation_phase CONSTANT NUMBER := 2;
392
393 -- Indicates that the process_status is Pending
394 lc_pending_status CONSTANT NUMBER := 1;
395
396 -- Source Code Value of 'Depot_Repair'
397 lc_depot_repair_source_code CONSTANT VARCHAR2(30) := 'DEPOT_REPAIR';
398
399 -- Depot repair Application Id passed as source_line_id
400 lc_depot_app_source_line_id CONSTANT NUMBER := 512;
401
402
403
404
405 BEGIN
406
407
408 IF ( l_proc_level >= l_debug_level ) then
409 FND_LOG.STRING( l_proc_level,
410 l_mod_name||'begin',
411 'Entering procedure insert_job_header' );
412 END IF;
413
414 x_return_status := FND_API.G_RET_STS_SUCCESS;
415
416
417 -- Populate the record l_job_header_rec
418
419
420 -- Populate the constant values
421
422 l_job_header_rec.process_phase := lc_validation_phase;
423 l_job_header_rec.process_status := lc_pending_status;
424 l_job_header_rec.source_code := lc_depot_repair_source_code;
425 l_job_header_rec.source_line_id := lc_depot_app_source_line_id ;
426
427
428 -- Populate the row who columns
429
430 l_job_header_rec.creation_date := SYSDATE;
431 l_job_header_rec.last_update_date := SYSDATE;
432 l_job_header_rec.created_by := fnd_global.user_id;
433 l_job_header_rec.last_updated_by := fnd_global.user_id;
434 l_job_header_rec.last_update_login := fnd_global.login_id;
435
436
437 --insert into table wip_job_schedule_interface
438 BEGIN
439 INSERT INTO wip_job_schedule_interface
440 (
441 wip_entity_id,
442 interface_id,
443 last_update_date,
444 last_updated_by,
445 creation_date,
446 created_by,
447 last_update_login,
448 load_type,
449 process_phase,
450 process_status,
451 group_id,
452 header_id,
453 source_code,
454 source_line_id,
455 job_name,
456 organization_id,
457 status_type,
458 first_unit_start_date,
459 last_unit_completion_date,
460 completion_subinventory,
461 completion_locator_id,
462 start_quantity,
463 net_quantity,
464 class_code,
465 primary_item_id,
466 bom_reference_id,
467 routing_reference_id,
468 alternate_routing_designator,
469 alternate_bom_designator
470 )
471 VALUES
472 (
473 l_job_header_rec.wip_entity_id,
474 l_job_header_rec.interface_id,
475 l_job_header_rec.last_update_date,
476 l_job_header_rec.last_updated_by,
477 l_job_header_rec.creation_date,
478 l_job_header_rec.created_by,
479 l_job_header_rec.last_update_login,
480 l_job_header_rec.load_type,
481 l_job_header_rec.process_phase,
482 l_job_header_rec.process_status,
483 l_job_header_rec.group_id,
484 l_job_header_rec.header_id,
485 l_job_header_rec.source_code,
486 l_job_header_rec.source_line_id,
487 l_job_header_rec.job_name,
488 l_job_header_rec.organization_id,
489 l_job_header_rec.status_type,
490 l_job_header_rec.first_unit_start_date,
491 l_job_header_rec.last_unit_completion_date,
492 l_job_header_rec.completion_subinventory,
493 l_job_header_rec.completion_locator_id,
494 l_job_header_rec.start_quantity,
495 l_job_header_rec.net_quantity,
496 l_job_header_rec.class_code,
497 l_job_header_rec.primary_item_id,
498 l_job_header_rec.bom_reference_id,
499 l_job_header_rec.routing_reference_id,
500 l_job_header_rec.alternate_routing_designator,
501 l_job_header_rec.alternate_bom_designator
502 );
503 EXCEPTION
504 WHEN OTHERS THEN
505 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_HEADER_INSERT_ERR');
506 FND_MESSAGE.SET_TOKEN('JOB_NAME', l_job_header_rec.job_name );
507 FND_MSG_PUB.ADD;
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509 RETURN;
510 END;
511
512
513 IF ( l_proc_level >= l_debug_level ) then
514 FND_LOG.STRING( l_proc_level,
515 l_mod_name||'end',
516 'Leaving procedure insert_job_header');
517 END IF;
518
519
520 END insert_job_header;
521
522
523 -- This procedure accepts job details information and inserts it into
524 -- wip_job_dtls_interface table.
525
526 PROCEDURE insert_job_details
527 (
528 p_job_details_rec IN wip_job_dtls_interface%ROWTYPE,
529 x_return_status OUT NOCOPY VARCHAR2
530 )
531 IS
532
533 -- Job Record to hold the Job Details information being inserted
534 -- into wip_job_dtls_interface
535
536 l_job_details_rec wip_job_dtls_interface%ROWTYPE := p_job_details_rec;
537
538
539 -- variables used for FND_LOG debug messages
540
541 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
542 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
543 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
544
545
546 -- Constants Used for Inserting into wip_job_schedule_interface,
547 -- These are the values needed for WIP Mass Load to pick up the records
548
549 -- Indicates that the process Phase is Validation
550 lc_validation_phase CONSTANT NUMBER := 2;
551
552 -- Indicates that the process_status is Pending
553 lc_pending_status CONSTANT NUMBER := 1;
554
555 -- lc_change_type CONSTANT NUMBER := 3;
556
557
558
559
560
561 BEGIN
562
563 -- dbms_output.put_line('Resource Seq Num is ' || l_job_details_rec.resource_seq_num );
564
565 -- dbms_output.put_line('usage_rate_or_amount is ' || l_job_details_rec.usage_rate_or_amount );
566
567 IF ( l_proc_level >= l_debug_level ) then
568 FND_LOG.STRING( l_proc_level,
569 l_mod_name||'begin',
570 'Entering procedure insert_job_header' );
571 END IF;
572
573 x_return_status := FND_API.G_RET_STS_SUCCESS;
574
575
576 -- Populate the record l_job_header_rec
577
578
579 -- Populate the constant values
580
581 l_job_details_rec.process_phase := lc_validation_phase;
582 l_job_details_rec.process_status := lc_pending_status;
583 -- l_job_details_rec.substitution_type := lc_change_type;
584
585 -- Populate the row who columns
586
587 l_job_details_rec.creation_date := SYSDATE;
588 l_job_details_rec.last_update_date := SYSDATE;
589 l_job_details_rec.created_by := fnd_global.user_id;
590 l_job_details_rec.last_updated_by := fnd_global.user_id;
591 l_job_details_rec.last_update_login := fnd_global.login_id;
592
593
594 BEGIN
595
596 INSERT INTO wip_job_dtls_interface
597 (last_updated_by,
598 last_update_date,
599 last_update_login,
600 created_by,
601 creation_date,
602 date_required,
603 start_date,
604 group_id,
605 parent_header_id,
606 inventory_item_id_old,
607 inventory_item_id_new,
608 resource_id_old,
609 resource_id_new,
610 resource_seq_num,
611 load_type,
612 mrp_net_flag,
613 operation_seq_num,
614 organization_id,
615 process_phase,
616 process_status,
617 -- quantity_issued,
618 quantity_per_assembly,
619 required_quantity,
620 uom_code,
621 usage_rate_or_amount,
622 assigned_units,
623 wip_entity_id,
624 wip_supply_type,
625 autocharge_type,
626 basis_type,
627 completion_date,
628 scheduled_flag,
629 standard_rate_flag,
630 substitution_type,
631 supply_subinventory,
632 -- swai: add columns for operations
633 backflush_flag,
634 count_point_type,
635 department_id,
636 first_unit_completion_date,
637 first_unit_start_date,
638 last_unit_completion_date,
639 last_unit_start_date,
640 minimum_transfer_quantity,
641 standard_operation_id,
642 description
643 )
644 Values
645 (
646 l_job_details_rec.last_updated_by,
647 l_job_details_rec.last_update_date,
648 l_job_details_rec.last_update_login,
649 l_job_details_rec.created_by,
650 l_job_details_rec.creation_date, -- sysdate,
651 l_job_details_rec.date_required,
652 l_job_details_rec.start_date,
653 l_job_details_rec.group_id,
654 l_job_details_rec.parent_header_id,
655 l_job_details_rec.inventory_item_id_old,
656 l_job_details_rec.inventory_item_id_new, -- 'WIP Completion',
657 l_job_details_rec.resource_id_old,
658 l_job_details_rec.resource_id_new,
659 l_job_details_rec.resource_seq_num,
660 l_job_details_rec.load_type,
661 l_job_details_rec.mrp_net_flag,
662 l_job_details_rec.operation_seq_num,
663 l_job_details_rec.organization_id,
664 l_job_details_rec.process_phase,
665 l_job_details_rec.process_status,
666 -- l_job_details_rec.quantity_issued,
667 -- null,
668 l_job_details_rec.quantity_per_assembly,
669 l_job_details_rec.required_quantity,
670 l_job_details_rec.uom_code,
671 l_job_details_rec.usage_rate_or_amount,
672 l_job_details_rec.assigned_units,
673 l_job_details_rec.wip_entity_id,
674 l_job_details_rec.wip_supply_type,
675 l_job_details_rec.autocharge_type,
676 l_job_details_rec.basis_type,
677 l_job_details_rec.completion_date,
678 l_job_details_rec.scheduled_flag,
679 l_job_details_rec.standard_rate_flag,
680 l_job_details_rec.substitution_type,
681 l_job_details_rec.supply_subinventory,
682 -- swai: add columns for operations
683 l_job_details_rec.backflush_flag,
684 l_job_details_rec.count_point_type,
685 l_job_details_rec.department_id,
686 l_job_details_rec.first_unit_completion_date,
687 l_job_details_rec.first_unit_start_date,
688 l_job_details_rec.last_unit_completion_date,
689 l_job_details_rec.last_unit_start_date,
690 l_job_details_rec.minimum_transfer_quantity,
691 l_job_details_rec.standard_operation_id,
692 l_job_details_rec.description
693 );
694
695
696 EXCEPTION
697 WHEN OTHERS THEN
698 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_DETAILS_INSERT_ERR');
699 FND_MESSAGE.SET_TOKEN('JOB_NAME', l_job_details_rec.wip_entity_id);
700 FND_MSG_PUB.ADD;
701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
702 RETURN;
703 END;
704
705
706 IF ( l_proc_level >= l_debug_level ) then
707 FND_LOG.STRING( l_proc_level,
708 l_mod_name||'end',
709 'Leaving procedure insert_job_header');
710 END IF;
711
712
713 END insert_job_details;
714
715
716 PROCEDURE insert_transactions_header
717 (
718 p_transactions_interface_rec IN mtl_transactions_interface%ROWTYPE,
719 x_return_status OUT NOCOPY VARCHAR2
720 )
721 IS
722
723 -- Job Record to hold the Job Details information being inserted
724 -- into wip_job_dtls_interface
725
726 l_transactions_interface_rec mtl_transactions_interface%ROWTYPE := p_transactions_interface_rec;
727
728
729 -- constant used for FND_LOG debug messages
730
731 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_transactions_header.';
732
733
734 -- Constants Used for Inserting into mtl_transactions_interface,
735
736 lc_concurrent_mode CONSTANT NUMBER := 1;
737 lc_yes_process_flag CONSTANT NUMBER := 1;
738
739
740
741
742 BEGIN
743
744
745 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
746 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
747 lc_mod_name||'begin',
748 'Entering procedure insert_job_header' );
749 END IF;
750
751 x_return_status := FND_API.G_RET_STS_SUCCESS;
752
753
754
755
756 -- Populate the constant values
757
758
759 l_transactions_interface_rec.transaction_mode := lc_concurrent_mode;
760 l_transactions_interface_rec.process_flag := lc_yes_process_flag;
761
762 -- Populate the row who columns
763
764 l_transactions_interface_rec.creation_date := SYSDATE;
765 l_transactions_interface_rec.last_update_date := SYSDATE;
766 l_transactions_interface_rec.created_by := fnd_global.user_id;
767 l_transactions_interface_rec.last_updated_by := fnd_global.user_id;
768 l_transactions_interface_rec.last_update_login := fnd_global.login_id;
769
770
771 --insert into table mtl_transactions_interface
772 BEGIN
773
774 INSERT INTO mtl_transactions_interface
775 (last_updated_by,
776 last_update_date,
777 last_update_login,
778 created_by,
779 creation_date,
780 transaction_header_id,
781 source_code,
782 completion_transaction_id,
783 inventory_item_id,
784 subinventory_code,
785 locator_id,
786 transaction_quantity,
787 transaction_uom,
788 primary_quantity,
789 transaction_date,
790 organization_id,
791 transaction_source_id,
792 transaction_source_type_id,
793 transaction_type_id,
794 wip_entity_type,
795 operation_seq_num,
796 revision,
797 transaction_mode,
798 process_flag,
799 source_header_id,
800 source_line_id,
801 transaction_interface_id,
802 reason_id, -- swai: bug 6841113
803 final_completion_flag
804 )
805 Values
806 (
807 l_transactions_interface_rec.last_updated_by,
808 l_transactions_interface_rec.last_update_date,
809 l_transactions_interface_rec.last_update_login,
810 l_transactions_interface_rec.created_by,
811 l_transactions_interface_rec.creation_date, -- sysdate,
812 l_transactions_interface_rec.transaction_header_id,
813 l_transactions_interface_rec.source_code, -- 'WIP Issue',
814 l_transactions_interface_rec.completion_transaction_id,
815 l_transactions_interface_rec.inventory_item_id, --8229,
816 l_transactions_interface_rec.subinventory_code,
817 l_transactions_interface_rec.locator_id,
818 l_transactions_interface_rec.transaction_quantity, -- 1,
819 l_transactions_interface_rec.transaction_uom, --'Ea',
820 l_transactions_interface_rec.primary_quantity, -- 1,
821 l_transactions_interface_rec.transaction_date, -- sysdate,
822 l_transactions_interface_rec.organization_id, --207,
823 l_transactions_interface_rec.transaction_source_id, --124743,
824 l_transactions_interface_rec.transaction_source_type_id, -- 5,
825 l_transactions_interface_rec.transaction_type_id, -- 35,
826 l_transactions_interface_rec.wip_entity_type, -- 3,
827 l_transactions_interface_rec.operation_seq_num,
828 l_transactions_interface_rec.revision, --null, -- ,
829 l_transactions_interface_rec.transaction_mode,
830 l_transactions_interface_rec.process_flag,
831 l_transactions_interface_rec.source_header_id, -- 124743, -- ,
832 l_transactions_interface_rec.source_line_id, -- -1, --10,
833 l_transactions_interface_rec.transaction_interface_id, -- null, -- mtl_material_transactions_s.nextval, --l_transaction_interface_id,
834 l_transactions_interface_rec.reason_id, -- swai: bug 6841113
835 l_transactions_interface_rec.final_completion_flag ); -- 'N' ) ;
836
837
838
839 EXCEPTION
840 WHEN OTHERS THEN
841 FND_MESSAGE.SET_NAME('CSD','CSD_TXNS_HEADER_INSERT_ERR');
842 FND_MESSAGE.SET_TOKEN('JOB_NAME', l_transactions_interface_rec.transaction_source_id );
843 FND_MSG_PUB.ADD;
844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
845 RETURN;
846 END;
847
848
849
850 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
851 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
852 lc_mod_name||'end',
853 'Leaving procedure insert_transactions_header');
854 END IF;
855
856
857 END insert_transactions_header;
858
859
860 PROCEDURE update_transactions_header
861 (
862 p_transactions_interface_rec IN mtl_transactions_interface%ROWTYPE,
863 x_return_status OUT NOCOPY VARCHAR2
864 )
865 IS
866
867
868
869 -- constant used for FND_LOG debug messages
870
871 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.update_transactions_header.';
872
873
874
875
876 BEGIN
877
878
879 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
880 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
881 lc_mod_name||'begin',
882 'Entering procedure update_transactions_header' );
883 END IF;
884
885 x_return_status := FND_API.G_RET_STS_SUCCESS;
886
887
888
889 --update table mtl_transactions_interface
890 BEGIN
891
892 /* dbms_output.put_line( 'p_transactions_interface_rec.transaction_interface_id is ' ||
893 p_transactions_interface_rec.transaction_interface_id ); */
894
895 UPDATE mtl_transactions_interface
896 SET
897 subinventory_code = p_transactions_interface_rec.subinventory_code,
898 locator_id = p_transactions_interface_rec.locator_id,
899 revision = p_transactions_interface_rec.revision,
900 reason_id = p_transactions_interface_rec.reason_id -- swai: bug 6841113
901 where
902 transaction_interface_id = p_transactions_interface_rec.transaction_interface_id;
903
904
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 FND_MESSAGE.SET_NAME('CSD','CSD_TXNS_HEADER_UPDATE_ERR');
909 FND_MSG_PUB.ADD;
910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 RETURN;
912 END;
913
914
915
916 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
917 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
918 lc_mod_name||'end',
919 'Leaving procedure update_transactions_header');
920 END IF;
921
922
923 END update_transactions_header;
924
925
926 PROCEDURE insert_transaction_lots
927 (
928 p_txn_lots_interface_rec IN mtl_transaction_lots_interface%ROWTYPE,
929 x_return_status OUT NOCOPY VARCHAR2
930 )
931 IS
932
933
934 -- constant used for FND_LOG debug messages
935
936 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_transaction_lots';
937
938
939 l_creation_date DATE;
940 l_last_update_date DATE;
941 l_created_by NUMBER;
942 l_last_updated_by NUMBER;
943 l_last_updated_by_name VARCHAR2(100);
944 l_last_update_login NUMBER;
945
946 BEGIN
947
948
949 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
950 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
951 lc_mod_name||'begin',
952 'Entering procedure insert_transaction_lots' );
953 END IF;
954
955 x_return_status := FND_API.G_RET_STS_SUCCESS;
956
957
958 -- Populate the row who columns
959
960 l_creation_date := SYSDATE;
961 l_last_update_date := SYSDATE;
962 l_created_by := fnd_global.user_id;
963 l_last_updated_by := fnd_global.user_id;
964 l_last_update_login := fnd_global.login_id;
965
966
967 --insert into table mtl_transactions_interface
968 BEGIN
969
970 INSERT INTO mtl_transaction_lots_interface
971 (last_updated_by,
972 last_update_date,
973 last_update_login,
974 created_by,
975 creation_date,
976 transaction_interface_id,
977 lot_number,
978 transaction_quantity,
979 serial_transaction_temp_id
980 )
981 Values
982 (
983 l_last_updated_by,
984 l_last_update_date,
985 l_last_update_login,
986 l_created_by,
987 l_creation_date, -- sysdate,
988 p_txn_lots_interface_rec.transaction_interface_id,
989 p_txn_lots_interface_rec.lot_number,
990 p_txn_lots_interface_rec.transaction_quantity,
991 p_txn_lots_interface_rec.serial_transaction_temp_id
992 ); -- 'N' ) ;
993
994
995 EXCEPTION
996 WHEN OTHERS THEN
997 FND_MESSAGE.SET_NAME('CSD','CSD_TXN_LOTS_INSERT_ERR');
998 FND_MSG_PUB.ADD;
999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1000 RETURN;
1001 END;
1002
1003
1004
1005 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1006 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1007 lc_mod_name||'end',
1008 'Leaving procedure insert_transaction_lots');
1009 END IF;
1010
1011
1012 END insert_transaction_lots;
1013
1014
1015 PROCEDURE insert_upd_serial_numbers
1016 (
1017 p_srl_nmbrs_interface_rec IN mtl_serial_numbers_interface%ROWTYPE,
1018 x_return_status OUT NOCOPY VARCHAR2
1019 )
1020 IS
1021
1022
1023 -- constant used for FND_LOG debug messages
1024
1025 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_upd_serial_numbers.';
1026
1027
1028 l_creation_date DATE;
1029 l_last_update_date DATE;
1030 l_created_by NUMBER;
1031 l_last_updated_by NUMBER;
1032 l_last_updated_by_name VARCHAR2(100);
1033 l_last_update_login NUMBER;
1034 l_row_exists NUMBER := 0;
1035
1036 BEGIN
1037
1038
1039 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1040 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1041 lc_mod_name||'begin',
1042 'Entering procedure insert_upd_serial_numbers' );
1043 END IF;
1044
1045 x_return_status := FND_API.G_RET_STS_SUCCESS;
1046
1047 Select count(*) into l_row_exists from mtl_serial_numbers_interface
1048 where transaction_interface_id =
1049 p_srl_nmbrs_interface_rec.transaction_interface_id;
1050
1051 IF l_row_exists = 1 THEN
1052
1053 BEGIN
1054
1055 UPDATE mtl_serial_numbers_interface
1056 SET
1057 fm_serial_number = p_srl_nmbrs_interface_rec.fm_serial_number
1058 where transaction_interface_id =
1059 p_srl_nmbrs_interface_rec.transaction_interface_id;
1060
1061 EXCEPTION
1062 WHEN OTHERS THEN
1063 FND_MESSAGE.SET_NAME('CSD','CSD_SRL_NMBRS_UPDATE_ERR');
1064 FND_MSG_PUB.ADD;
1065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1066 RETURN;
1067 END;
1068
1069 ELSE
1070
1071 -- Populate the row who columns
1072
1073 l_creation_date := SYSDATE;
1074 l_last_update_date := SYSDATE;
1075 l_created_by := fnd_global.user_id;
1076 l_last_updated_by := fnd_global.user_id;
1077 l_last_update_login := fnd_global.login_id;
1078
1079
1080 --insert into table mtl_transactions_interface
1081 BEGIN
1082
1083 INSERT INTO mtl_serial_numbers_interface
1084 ( last_updated_by,
1085 last_update_date,
1086 last_update_login,
1087 created_by,
1088 creation_date,
1089 transaction_interface_id,
1090 fm_serial_number
1091 )
1092 Values
1093 (
1094 l_last_updated_by,
1095 l_last_update_date,
1096 l_last_update_login,
1097 l_created_by,
1098 l_creation_date, -- sysdate,
1099 p_srl_nmbrs_interface_rec.transaction_interface_id,
1100 p_srl_nmbrs_interface_rec.fm_serial_number
1101 ); -- 'N' ) ;
1102
1103
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 FND_MESSAGE.SET_NAME('CSD','CSD_SRL_NMBRS_INSERT_ERR');
1107 FND_MSG_PUB.ADD;
1108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109 RETURN;
1110 END;
1111
1112 END IF;
1113
1114 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1115 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1116 lc_mod_name||'end',
1117 'Leaving procedure insert_upd_serial_numbers');
1118 END IF;
1119
1120
1121 END insert_upd_serial_numbers;
1122
1123
1124 PROCEDURE insert_wip_cost_txn
1125 (
1126 p_wip_cost_txn_interface_rec IN wip_cost_txn_interface%ROWTYPE,
1127 x_return_status OUT NOCOPY VARCHAR2
1128 )
1129 IS
1130
1131
1132
1133 -- constant used for FND_LOG debug messages
1134
1135 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_transactions_header.';
1136
1137
1138 -- Constants Used for Inserting into mtl_transactions_interface,
1139
1140 lc_concurrent_mode CONSTANT NUMBER := 1;
1141 lc_yes_process_flag CONSTANT NUMBER := 1;
1142
1143 lc_res_validation_phase CONSTANT NUMBER := 1;
1144 lc_res_pending_status CONSTANT NUMBER := 1;
1145 lc_discrete_entity_type CONSTANT NUMBER := 1;
1146
1147
1148 l_creation_date DATE;
1149 l_last_update_date DATE;
1150 l_created_by_name VARCHAr2(100);
1151 l_last_updated_by NUMBER;
1152 l_last_updated_by_name VARCHAR2(100);
1153 l_last_update_login NUMBER;
1154 l_process_phase NUMBER;
1155 l_process_status NUMBER;
1156 l_entity_type NUMBER;
1157
1158
1159
1160 BEGIN
1161
1162
1163 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1164 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1165 lc_mod_name||'begin',
1166 'Entering procedure insert_job_header' );
1167 END IF;
1168
1169 x_return_status := FND_API.G_RET_STS_SUCCESS;
1170
1171
1172 -- Populate the constant values
1173
1174
1175 l_process_phase := lc_res_validation_phase;
1176 l_process_status := lc_res_pending_status;
1177 l_entity_type := lc_discrete_entity_type;
1178
1179 -- Populate the row who columns
1180
1181 l_creation_date := SYSDATE;
1182 l_last_update_date := SYSDATE;
1183 l_created_by_name := fnd_global.user_name;
1184 l_last_updated_by := fnd_global.user_id;
1185 l_last_updated_by_name := fnd_global.user_name;
1186 l_last_update_login := fnd_global.login_id;
1187
1188
1189 --insert into table wip_cost_txn_interface
1190 BEGIN
1191
1192 INSERT INTO wip_cost_txn_interface
1193 (last_updated_by_name,
1194 last_updated_by,
1195 last_update_date,
1196 last_update_login,
1197 created_by_name,
1198 creation_date,
1199 operation_seq_num,
1200 organization_id,
1201 organization_code,
1202 process_phase,
1203 process_status,
1204 resource_seq_num,
1205 transaction_date,
1206 transaction_quantity,
1207 transaction_type,
1208 transaction_uom,
1209 wip_entity_name,
1210 wip_entity_id,
1211 employee_id,
1212 employee_num,
1213 entity_type
1214 )
1215 Values
1216 (
1217 l_last_updated_by_name,
1218 l_last_updated_by,
1219 l_last_update_date,
1220 l_last_update_login,
1221 l_created_by_name,
1222 l_creation_date, -- sysdate,
1223 p_wip_cost_txn_interface_rec.operation_seq_num,
1224 p_wip_cost_txn_interface_rec.organization_id,
1225 p_wip_cost_txn_interface_rec.organization_code,
1226 l_process_phase,
1227 l_process_status,
1228 p_wip_cost_txn_interface_rec.resource_seq_num,
1229 p_wip_cost_txn_interface_rec.transaction_date,
1230 p_wip_cost_txn_interface_rec.transaction_quantity,
1231 p_wip_cost_txn_interface_rec.transaction_type,
1232 p_wip_cost_txn_interface_rec.transaction_uom,
1233 p_wip_cost_txn_interface_rec.wip_entity_name,
1234 p_wip_cost_txn_interface_rec.wip_entity_id,
1235 p_wip_cost_txn_interface_rec.employee_id,
1236 p_wip_cost_txn_interface_rec.employee_num,
1237 l_entity_type
1238 ) ;
1239
1240
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 FND_MESSAGE.SET_NAME('CSD','CSD_WIP_COST_TXN_INSERT_ERR');
1244 FND_MSG_PUB.ADD;
1245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1246 RETURN;
1247
1248 END;
1249
1250
1251 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1252 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1253 lc_mod_name||'end',
1254 'Leaving procedure insert_transactions_header');
1255 END IF;
1256
1257
1258 END insert_wip_cost_txn;
1259
1260
1261 PROCEDURE insert_wip_move_txn
1262 (
1263 p_wip_move_txn_interface_rec IN wip_move_txn_interface%ROWTYPE,
1264 x_return_status OUT NOCOPY VARCHAR2
1265 )
1266 IS
1267
1268
1269
1270 -- constant used for FND_LOG debug messages
1271
1272 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_wip_move_txn';
1273
1274
1275 -- Indicates that the process Phase is Validation
1276 lc_validation_phase CONSTANT NUMBER := 1;
1277
1278
1279 -- Indicates that the process_status is Running
1280 lc_running_status CONSTANT NUMBER := 2;
1281
1282
1283 l_creation_date DATE;
1284 l_last_update_date DATE;
1285 l_created_by NUMBER;
1286 l_last_updated_by NUMBER;
1287 l_last_updated_by_name VARCHAR2(100);
1288 l_created_by_name VARCHAR2(100);
1289 l_last_update_login NUMBER;
1290 l_process_phase NUMBER;
1291 l_process_status NUMBER;
1292
1293
1294
1295 BEGIN
1296
1297
1298 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1299 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1300 lc_mod_name||'begin',
1301 'Entering procedure insert_wip_move_txn' );
1302 END IF;
1303
1304 x_return_status := FND_API.G_RET_STS_SUCCESS;
1305
1306
1307 -- Populate the constant values
1308
1309
1310 l_process_phase := lc_validation_phase;
1311 l_process_status := lc_running_status;
1312
1313 -- Populate the row who columns
1314
1315 l_creation_date := SYSDATE;
1316 l_last_update_date := SYSDATE;
1317 l_created_by := fnd_global.user_id;
1318 l_created_by_name := fnd_global.user_name;
1319 l_last_updated_by := fnd_global.user_id;
1320 l_last_updated_by_name := fnd_global.user_name;
1321 l_last_update_login := fnd_global.login_id;
1322
1323
1324
1325 --insert into table wip_move_txn_interface
1326 BEGIN
1327 insert into wip_move_txn_interface(
1328 transaction_id,
1329 last_update_date,
1330 last_updated_by,
1331 last_updated_by_name,
1332 creation_date,
1333 created_by,
1334 created_by_name,
1335 group_id,
1336 process_phase,
1337 process_status,
1338 organization_id,
1339 wip_entity_name,
1340 transaction_date,
1341 fm_operation_seq_num,
1342 fm_intraoperation_step_type,
1343 to_operation_seq_num,
1344 to_intraoperation_step_type,
1345 transaction_quantity,
1346 transaction_uom
1347 ) values (
1348 p_wip_move_txn_interface_rec.transaction_id,
1349 l_last_update_date, /* last_update_date */
1350 l_last_updated_by, /* last_updated_by */
1351 l_last_updated_by_name, /* last_updated_by_name */
1352 l_creation_date, /* creation_date */
1353 l_created_by, /* created_by */
1354 l_created_by_name, /* created_by_name */
1355 p_wip_move_txn_interface_rec.group_id, /* group_id */
1356 l_process_phase, /* process phase */
1357 l_process_status, /* process status */
1358 p_wip_move_txn_interface_rec.organization_id,
1359 p_wip_move_txn_interface_rec.wip_entity_name,
1360 p_wip_move_txn_interface_rec.transaction_date,
1361 p_wip_move_txn_interface_rec.fm_operation_seq_num,
1362 p_wip_move_txn_interface_rec.fm_intraoperation_step_type,
1363 p_wip_move_txn_interface_rec.to_operation_seq_num,
1364 p_wip_move_txn_interface_rec.to_intraoperation_step_type,
1365 p_wip_move_txn_interface_rec.transaction_quantity,
1366 p_wip_move_txn_interface_rec.transaction_uom
1367 );
1368
1369
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 FND_MESSAGE.SET_NAME('CSD','CSD_WIP_MOVE_TXN_INSERT_ERR');
1373 FND_MSG_PUB.ADD;
1374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1375 RETURN;
1376
1377 END;
1378
1379
1380 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1381 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1382 lc_mod_name||'end',
1383 'Leaving procedure insert_wip_move_txn');
1384 END IF;
1385
1386
1387 END insert_wip_move_txn;
1388
1389 --
1390 -- Inserts the transaction line(s) for job completion and then
1391 -- processes the transaction lines if there are no details needed
1392 -- OUT param:
1393 -- x_transaction_header_id: If details are needed, the transaction
1394 -- header ID will be populated. Otherwise
1395 -- parameter is null.
1396 --
1397 PROCEDURE process_job_comp_txn
1398 (
1399 p_api_version_number IN NUMBER,
1400 p_init_msg_list IN VARCHAR2 ,
1401 p_commit IN VARCHAR2 ,
1402 p_validation_level IN NUMBER,
1403 x_return_status OUT NOCOPY VARCHAR2,
1404 x_msg_count OUT NOCOPY NUMBER,
1405 x_msg_data OUT NOCOPY VARCHAR2,
1406 p_comp_job_dtls_rec IN JOB_DTLS_REC_TYPE,
1407 x_transaction_header_id OUT NOCOPY NUMBER
1408 )
1409 IS
1410 lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_JOB_COMP_TXN';
1411 lc_api_version_number CONSTANT NUMBER := 1.0;
1412
1413 -- constants used for FND_LOG debug messages
1414 lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_job_comp_txn';
1415
1416 l_need_details_flag VARCHAR2(1) := 'F';
1417 l_transaction_header_id NUMBER;
1418
1419 BEGIN
1420 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1421 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1422 lc_mod_name||'begin',
1423 'Entering private API process_job_comp_txn' );
1424 END IF;
1425
1426 SAVEPOINT PROCESS_JOB_COMP_TXN_PVT;
1427
1428 -- Standard call to check for call compatibility.
1429 IF NOT FND_API.Compatible_API_Call(lc_api_version_number,
1430 p_api_version_number,
1431 lc_api_name,
1432 G_PKG_NAME) THEN
1433 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1434 END IF;
1435
1436 IF FND_API.to_boolean(p_init_msg_list) THEN
1437 FND_MSG_PUB.initialize;
1438 END IF;
1439
1440 x_return_status := FND_API.G_RET_STS_SUCCESS;
1441
1442 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1443 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
1444 lc_mod_name||'beforecallinsertjobcomptxn',
1445 'Just before calling insert_job_comp_txn');
1446 END IF;
1447 insert_job_comp_txn (
1448 p_api_version_number => lc_api_version_number,
1449 p_init_msg_list => fnd_api.g_false ,
1450 p_commit => fnd_api.g_false,
1451 p_validation_level => p_validation_level,
1452 x_return_status => x_return_status,
1453 x_msg_count => x_msg_count,
1454 x_msg_data => x_msg_data,
1455 p_comp_job_dtls_rec => p_comp_job_dtls_rec,
1456 x_need_details_flag => l_need_details_flag,
1457 x_transaction_header_id => l_transaction_header_id
1458 );
1459
1460 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1461 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_COMP_TXN_FAILURE');
1462 FND_MSG_PUB.ADD;
1463 RAISE FND_API.G_EXC_ERROR;
1464 END IF;
1465
1466 -- if no need for details, then we can process transactions and commit
1467 IF l_need_details_flag = 'F' THEN
1468 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1469 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
1470 lc_mod_name||'beforecallprocesstxn',
1471 'Just before calling process_mti_transactions');
1472 END IF;
1473 process_mti_transactions(
1474 p_api_version_number => lc_api_version_number,
1475 p_init_msg_list => fnd_api.g_false ,
1476 p_commit => fnd_api.g_false,
1477 p_validation_level => p_validation_level,
1478 x_return_status => x_return_status,
1479 x_msg_count => x_msg_count,
1480 x_msg_data => x_msg_data,
1481 p_txn_header_id => l_transaction_header_id
1482 -- p_txn_type IN VARCHAR2
1483 );
1484 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1485 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_COMP_TXN_FAILURE');
1486 FND_MSG_PUB.ADD;
1487 RAISE FND_API.G_EXC_ERROR;
1488 END IF;
1489 ELSE
1490 --if we need details, pass back the transaction header id
1491 x_transaction_header_id := l_transaction_header_id;
1492 END IF;
1493
1494 -- Check before commit
1495 IF l_need_details_flag = 'F' and FND_API.to_Boolean( p_commit )
1496 THEN
1497 COMMIT WORK;
1498 END IF;
1499
1500 EXCEPTION
1501 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1502 ROLLBACK to PROCESS_JOB_COMP_TXN_PVT ;
1503 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1504
1505 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1506 p_count => x_msg_count,
1507 p_data => x_msg_data);
1508
1509 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1510 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
1511 lc_mod_name||'unx_exception',
1512 'G_EXC_UNEXPECTED_ERROR Exception');
1513 END IF;
1514
1515
1516 WHEN FND_API.G_EXC_ERROR THEN
1517 ROLLBACK to PROCESS_JOB_COMP_TXN_PVT ;
1518 x_return_status := FND_API.G_RET_STS_ERROR;
1519
1520 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1521 p_count => x_msg_count,
1522 p_data => x_msg_data);
1523
1524 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1525 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
1526 lc_mod_name||'exc_exception',
1527 'G_EXC_ERROR Exception');
1528 END IF;
1529
1530 WHEN OTHERS THEN
1531 ROLLBACK to PROCESS_JOB_COMP_TXN_PVT ;
1532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1533
1534 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1535 -- Add Unexpected Error to Message List, here SQLERRM is used for
1536 -- getting the error
1537 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
1538 p_procedure_name => lc_api_name );
1539 END IF;
1540
1541 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1542 p_count => x_msg_count,
1543 p_data => x_msg_data);
1544
1545 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1546 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
1547 lc_mod_name||'others_exception',
1548 'OTHERS Exception');
1549 END IF;
1550 END process_job_comp_txn;
1551
1552 --
1553 -- Inserts the transaction line(s) for job completion
1554 -- Does NOT process the transaction lines
1555 -- OUT params:
1556 -- x_need_details_flag: set to 'T' if details are neede, otherwise 'F'
1557 -- x_transaction_header_id: Transaction header ID always passed back
1558 -- regardless of need details param
1559 --
1560 PROCEDURE insert_job_comp_txn
1561 (
1562 p_api_version_number IN NUMBER,
1563 p_init_msg_list IN VARCHAR2 ,
1564 p_commit IN VARCHAR2 ,
1565 p_validation_level IN NUMBER,
1566 x_return_status OUT NOCOPY VARCHAR2,
1567 x_msg_count OUT NOCOPY NUMBER,
1568 x_msg_data OUT NOCOPY VARCHAR2,
1569 p_comp_job_dtls_rec IN JOB_DTLS_REC_TYPE,
1570 x_need_details_flag OUT NOCOPY VARCHAR2,
1571 x_transaction_header_id OUT NOCOPY NUMBER
1572 )
1573 IS
1574
1575 lc_api_name CONSTANT VARCHAR2(30) := 'INSERT_JOB_COMP_TXN';
1576 lc_api_version_number CONSTANT NUMBER := 1.0;
1577
1578 -- constants used for FND_LOG debug messages
1579 lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.insert_job_comp_txn';
1580
1581 lc_revision_controlled CONSTANT NUMBER := 2;
1582 lc_full_lot_control CONSTANT NUMBER := 2;
1583 lc_predefined_serial_control CONSTANT NUMBER := 2;
1584 lc_inven_rct_srl_control CONSTANT NUMBER := 5;
1585 lc_predfn_loc_cntrl CONSTANT NUMBER := 2;
1586 lc_dyn_loc_cntrl CONSTANT NUMBER := 3;
1587 lc_subinv_lv_loc_cntrl CONSTANT NUMBER := 4;
1588 lc_inv_lv_loc_cntrl CONSTANT NUMBER := 5;
1589
1590 -- Constants used for inserting into mtl_transactions_interface
1591 lc_completion_source_code CONSTANT VARCHAR2(30) := 'WIP Completion';
1592 lc_wip_txn_source_type_id CONSTANT NUMBER := 5;
1593 lc_comp_txn_type CONSTANT NUMBER := 44;
1594 lc_non_std_wip_ent_type CONSTANT NUMBER := 3;
1595 lc_dummy_source_line_id CONSTANT NUMBER := -2;
1596 lc_n_final_completion_flag CONSTANT VARCHAR2(1) := 'N' ;
1597
1598 -- Records to hold the mtl_transactions_interface data
1599 l_transactions_interface_rec mtl_transactions_interface%ROWTYPE;
1600
1601 l_locator_controlled VARCHAR2(1) := 'F';
1602 l_revision_qty_control_code NUMBER;
1603 l_transaction_quantity NUMBER;
1604 l_lot_control_code NUMBER;
1605 l_SERIAL_NUMBER_CONTROL_CODE NUMBER;
1606
1607 l_last_op_move_quantity NUMBER;
1608 l_last_move_allowed VARCHAR2(1);
1609 l_location_control_code NUMBER;
1610
1611 Cursor get_job_details IS
1612 SELECT wdj.organization_id, wdj.primary_item_id,
1613 (wdj.start_quantity - wdj.quantity_completed - wdj.quantity_scrapped)
1614 transaction_quantity,
1615 wdj.completion_subinventory, wdj.completion_locator_id,
1616 msi.primary_uom_code, msi.revision_qty_control_code,
1617 msi.SERIAL_NUMBER_CONTROL_CODE, msi.LOT_CONTROL_CODE
1618 from wip_discrete_jobs wdj, mtl_system_items_kfv msi
1619 where wdj.wip_entity_id = p_comp_job_dtls_rec.wip_entity_id and
1620 wdj.primary_item_id = msi.inventory_item_id and
1621 wdj.organization_id = msi.organization_id;
1622
1623 CURSOR get_mtl_header_id IS
1624 SELECT mtl_material_transactions_s.nextval from dual;
1625
1626 CURSOR get_last_operation_dtls(c_org_id NUMBER) IS
1627 SELECT
1628 wo.quantity_waiting_to_move,
1629 'Y' allow_moves
1630 FROM
1631 wip_operations wo
1632 WHERE wo.operation_seq_num =
1633 (select max(operation_seq_num)
1634 from wip_operations wo1
1635 where wo1.organization_id = wo.organization_id
1636 and wo1.wip_entity_id = wo.wip_entity_id
1637 and wo1.repetitive_schedule_id is NULL)
1638 AND wo.organization_id = c_org_id
1639 AND wo.wip_entity_id = p_comp_job_dtls_rec.wip_entity_id
1640 AND wo.repetitive_schedule_id is NULL
1641 AND not exists
1642 (select 'No move status exists'
1643 from wip_shop_floor_statuses ws,
1644 wip_shop_floor_status_codes wsc
1645 where wsc.organization_id = wo.organization_id
1646 and ws.organization_id = wo.organization_id
1647 and ws.wip_entity_id = wo.wip_entity_id
1648 and ws.line_id is NULL
1649 and ws.operation_seq_num = wo.operation_seq_num
1650 and ws.intraoperation_step_type = 3
1651 and ws.shop_floor_status_code = wsc.shop_floor_status_code
1652 and wsc.status_move_flag = 2
1653 and nvl(wsc.disable_date, SYSDATE + 1) > SYSDATE)
1654 UNION
1655 SELECT
1656 wo.quantity_waiting_to_move,
1657 'N' allow_moves
1658 FROM
1659 wip_operations wo
1660 WHERE wo.operation_seq_num =
1661 (select max(operation_seq_num)
1662 from wip_operations wo1
1663 where wo1.organization_id = wo.organization_id
1664 and wo1.wip_entity_id = wo.wip_entity_id
1665 and wo1.repetitive_schedule_id is NULL)
1666 AND wo.organization_id = c_org_id
1667 AND wo.wip_entity_id = p_comp_job_dtls_rec.wip_entity_id
1668 AND wo.repetitive_schedule_id is NULL
1669 AND exists
1670 (select 'Move status exists'
1671 from wip_shop_floor_statuses ws,
1672 wip_shop_floor_status_codes wsc
1673 where wsc.organization_id = wo.organization_id
1674 and ws.organization_id = wo.organization_id
1675 and ws.wip_entity_id = wo.wip_entity_id
1676 and ws.line_id is NULL
1677 and ws.operation_seq_num = wo.operation_seq_num
1678 and ws.intraoperation_step_type = 3
1679 and ws.shop_floor_status_code = wsc.shop_floor_status_code
1680 and wsc.status_move_flag = 2
1681 and nvl(wsc.disable_date, SYSDATE + 1) > SYSDATE);
1682
1683
1684 CURSOR get_org_locator_control_code(p_organization_id NUMBER) IS
1685 SELECT stock_locator_control_code
1686 from mtl_parameters
1687 where organization_id = p_organization_id;
1688
1689 CURSOR get_si_locator_control_code ( p_organization_id NUMBER,
1690 p_secondary_inventory_name VARCHAR2 ) IS
1691 SELECT locator_type
1692 from mtl_secondary_inventories
1693 where
1694 organization_id = p_organization_id and
1695 secondary_inventory_name = p_secondary_inventory_name;
1696
1697 CURSOR get_inv_location_control_code ( p_organization_id NUMBER,
1698 p_inventory_item_id NUMBER ) IS
1699 SELECT location_control_code
1700 from mtl_system_items_b
1701 where
1702 organization_id = p_organization_id and
1703 inventory_item_id = p_inventory_item_id;
1704
1705 BEGIN
1706 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1707 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1708 lc_mod_name||'begin',
1709 'Entering private API insert_job_comp_txn' );
1710 END IF;
1711
1712 SAVEPOINT INSERT_JOB_COMP_TXN_PVT;
1713
1714 -- Standard call to check for call compatibility.
1715 IF NOT FND_API.Compatible_API_Call(lc_api_version_number,
1716 p_api_version_number,
1717 lc_api_name,
1718 G_PKG_NAME) THEN
1719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1720 END IF;
1721
1722
1723 IF FND_API.to_boolean(p_init_msg_list) THEN
1724 FND_MSG_PUB.initialize;
1725 END IF;
1726
1727 -- initialize out params
1728 x_return_status := FND_API.G_RET_STS_SUCCESS;
1729 x_need_details_flag := 'F';
1730
1731 -- generate transaction_id
1732 open get_mtl_header_id;
1733 fetch get_mtl_header_id into l_transactions_interface_rec.transaction_header_id;
1734 close get_mtl_header_id;
1735
1736 open get_job_details;
1737 fetch get_job_details into
1738 l_transactions_interface_rec.organization_id,
1739 l_transactions_interface_rec.inventory_item_id,
1740 l_transaction_quantity,
1741 l_transactions_interface_rec.subinventory_code,
1742 l_transactions_interface_rec.locator_id,
1743 l_transactions_interface_rec.transaction_uom,
1744 l_revision_qty_control_code,
1745 l_SERIAL_NUMBER_CONTROL_CODE,
1746 l_lot_control_code;
1747 close get_job_details;
1748
1749 open get_last_operation_dtls(
1750 c_org_id => l_transactions_interface_rec.organization_id);
1751 fetch get_last_operation_dtls into
1752 l_last_op_move_quantity,
1753 l_last_move_allowed;
1754 IF (get_last_operation_dtls%FOUND) THEN
1755 l_transaction_quantity := l_last_op_move_quantity;
1756 IF l_last_move_allowed = 'N' THEN
1757 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_COMP_MV_NOT_ALL');
1758 FND_MSG_PUB.ADD;
1759 close get_last_operation_dtls;
1760 RAISE FND_API.G_EXC_ERROR;
1761 END IF;
1762 END IF;
1763 close get_last_operation_dtls;
1764
1765 IF l_transaction_quantity <= 0 THEN
1766 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_COMP_ZER_QTY');
1767 FND_MSG_PUB.ADD;
1768 RAISE FND_API.G_EXC_ERROR;
1769 END IF;
1770
1771 l_transactions_interface_rec.source_code := lc_completion_source_code;
1772 l_transactions_interface_rec.transaction_date := sysdate;
1773 l_transactions_interface_rec.transaction_source_type_id := lc_wip_txn_source_type_id;
1774 l_transactions_interface_rec.transaction_type_id:= lc_comp_txn_type;
1775 l_transactions_interface_rec.wip_entity_type := lc_non_std_wip_ent_type;
1776 l_transactions_interface_rec.source_line_id := lc_dummy_source_line_id;
1777 l_transactions_interface_rec.final_completion_flag := lc_n_final_completion_flag;
1778
1779 IF l_revision_qty_control_code = lc_revision_controlled THEN
1780 -- swai: bug 6995498/7182047 - move revision code to common wraper function
1781 l_transactions_interface_rec.revision := get_default_item_revision
1782 (
1783 p_org_id=>l_transactions_interface_rec.organization_id,
1784 p_inventory_item_id=> l_transactions_interface_rec.inventory_item_id,
1785 p_transaction_date=> l_transactions_interface_rec.transaction_date,
1786 p_mat_transaction_type=> 'JOB_COMP'
1787 ) ;
1788 IF l_transactions_interface_rec.revision is null THEN
1789 x_need_details_flag := 'T';
1790 END IF;
1791 END IF;
1792
1793 IF l_transactions_interface_rec.subinventory_code is null THEN
1794
1795 IF fnd_profile.value('CSD_DEF_REP_INV_ORG') = l_transactions_interface_rec.organization_id and
1796 fnd_profile.value('CSD_DEF_HV_CMP_SUBINV') is not null THEN
1797 l_transactions_interface_rec.subinventory_code := fnd_profile.value('CSD_DEF_HV_CMP_SUBINV');
1798 ELSE
1799 x_need_details_flag := 'T'; -- swai: bug 5262927
1800 END IF;
1801 END IF;
1802
1803 -- Get Locator Control
1804 open get_org_locator_control_code ( l_transactions_interface_rec.organization_id ) ;
1805 fetch get_org_locator_control_code into l_location_control_code ;
1806 close get_org_locator_control_code;
1807
1808 IF l_location_control_code = lc_subinv_lv_loc_cntrl THEN
1809 IF l_transactions_interface_rec.subinventory_code is not null THEN
1810 open get_si_locator_control_code ( l_transactions_interface_rec.organization_id ,
1811 l_transactions_interface_rec.subinventory_code ) ;
1812 fetch get_si_locator_control_code into l_location_control_code ;
1813 close get_si_locator_control_code;
1814
1815 IF l_location_control_code = lc_inv_lv_loc_cntrl THEN
1816 open get_inv_location_control_code ( l_transactions_interface_rec.organization_id ,
1817 l_transactions_interface_rec.inventory_item_id ) ;
1818 fetch get_inv_location_control_code into l_location_control_code ;
1819 close get_inv_location_control_code;
1820 END IF;
1821 END IF;
1822 END IF;
1823
1824
1825 IF l_location_control_code in (lc_predfn_loc_cntrl,
1826 lc_dyn_loc_cntrl ) THEN
1827 l_locator_controlled := 'T' ;
1828 END IF;
1829
1830 IF l_locator_controlled = 'T' THEN
1831 IF l_transactions_interface_rec.locator_id is null THEN
1832 x_need_details_flag := 'T';
1833 END IF;
1834 END IF;
1835
1836 -- Lot Contrrolled Check
1837 -- Later Need to handle it here as well - based on profile
1838 -- Value
1839 IF l_lot_control_code = lc_full_lot_control THEN
1840 x_need_details_flag := 'T' ;
1841 END IF;
1842
1843 l_transactions_interface_rec.transaction_quantity := l_transaction_quantity;
1844 l_transactions_interface_rec.primary_quantity := l_transactions_interface_rec.transaction_quantity;
1845 l_transactions_interface_rec.transaction_source_id := p_comp_job_dtls_rec.wip_entity_id;
1846 l_transactions_interface_rec.source_header_id := p_comp_job_dtls_rec.wip_entity_id;
1847
1848 -- generate transaction_interface_id
1849 open get_mtl_header_id;
1850 fetch get_mtl_header_id into l_transactions_interface_rec.transaction_interface_id;
1851 close get_mtl_header_id;
1852
1853 IF l_serial_number_control_code in (lc_predefined_serial_control,
1854 lc_inven_rct_srl_control) THEN
1855
1856 x_need_details_flag := 'T' ;
1857
1858 -- -1 identifies rows which are queried up in the details UI
1859 l_transactions_interface_rec.source_line_id := -1;
1860
1861 IF ( l_transaction_quantity > 1 ) THEN
1862 l_transactions_interface_rec.transaction_quantity := 1;
1863
1864 --insert into table mtl_transactions_interface
1865 FOR l_qty_ctr IN 1..l_transaction_quantity
1866 LOOP
1867 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1868 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
1869 lc_mod_name||'beforecallinserttxnshdr',
1870 'Just before calling insert_transactions_header');
1871 END IF;
1872
1873 insert_transactions_header( p_transactions_interface_rec => l_transactions_interface_rec,
1874 x_return_status => x_return_status );
1875
1876 IF l_qty_ctr <> l_transaction_quantity THEN
1877 -- generate transaction_interface_id for the next record
1878 open get_mtl_header_id;
1879 fetch get_mtl_header_id into l_transactions_interface_rec.transaction_interface_id;
1880 close get_mtl_header_id;
1881 END IF;
1882
1883 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1885 END IF;
1886 END LOOP;
1887 ELSE -- Quantity = 1
1888 --insert into table mtl_transactions_interface
1889 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1890 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
1891 lc_mod_name||'beforecallinserttxnshdr',
1892 'Just before calling insert_transactions_header');
1893 END IF;
1894
1895 insert_transactions_header(p_transactions_interface_rec => l_transactions_interface_rec,
1896 x_return_status => x_return_status );
1897
1898 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1899 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1900 END IF;
1901 END IF;
1902
1903 ELSE -- l_serial_number_control_code check
1904 IF x_need_details_flag = 'T' THEN
1905 -- -1 identifies rows which are queried up in the details UI
1906 l_transactions_interface_rec.source_line_id := -1;
1907 END IF;
1908
1909 --insert into table mtl_transactions_interface
1910 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1911 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
1912 lc_mod_name||'beforecallinserttxnshdr',
1913 'Just before calling insert_transactions_header');
1914 END IF;
1915
1916 insert_transactions_header(p_transactions_interface_rec => l_transactions_interface_rec,
1917 x_return_status => x_return_status );
1918
1919 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1921 END IF;
1922 END IF; -- l_serial_number_control_code check
1923
1924 -- Regardless of whether or not details are needed, pass back
1925 -- the transaction header ID.
1926 x_transaction_header_id := l_transactions_interface_rec.transaction_header_id;
1927
1928 -- Standard check for p_commit
1929 IF FND_API.to_Boolean( p_commit )
1930 THEN
1931 COMMIT WORK;
1932 END IF;
1933
1934 EXCEPTION
1935 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1936 ROLLBACK to INSERT_JOB_COMP_TXN_PVT ;
1937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1938
1939 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1940 p_count => x_msg_count,
1941 p_data => x_msg_data);
1942
1943 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1944 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
1945 lc_mod_name||'unx_exception',
1946 'G_EXC_UNEXPECTED_ERROR Exception');
1947 END IF;
1948
1949
1950 WHEN FND_API.G_EXC_ERROR THEN
1951 ROLLBACK to INSERT_JOB_COMP_TXN_PVT ;
1952 x_return_status := FND_API.G_RET_STS_ERROR;
1953
1954
1955 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1956 p_count => x_msg_count,
1957 p_data => x_msg_data);
1958
1959 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1960 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
1961 lc_mod_name||'exc_exception',
1962 'G_EXC_ERROR Exception');
1963 END IF;
1964
1965 WHEN OTHERS THEN
1966 ROLLBACK to INSERT_JOB_COMP_TXN_PVT ;
1967 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1968
1969 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1970
1971 -- Add Unexpected Error to Message List, here SQLERRM is used for
1972 -- getting the error
1973
1974 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
1975 p_procedure_name => lc_api_name );
1976 END IF;
1977
1978 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1979 p_count => x_msg_count,
1980 p_data => x_msg_data);
1981
1982 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1983 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
1984 lc_mod_name||'others_exception',
1985 'OTHERS Exception');
1986 END IF;
1987 END insert_job_comp_txn;
1988
1989 PROCEDURE process_mti_transactions
1990 (
1991 p_api_version_number IN NUMBER,
1992 p_init_msg_list IN VARCHAR2,
1993 p_commit IN VARCHAR2,
1994 p_validation_level IN NUMBER,
1995 x_return_status OUT NOCOPY VARCHAR2,
1996 x_msg_count OUT NOCOPY NUMBER,
1997 x_msg_data OUT NOCOPY VARCHAR2,
1998 p_txn_header_id IN NUMBER
1999 )
2000 IS
2001 lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_MTI_TRANSACTIONS';
2002 lc_api_version_number CONSTANT NUMBER := 1.0;
2003
2004 -- constants used for FND_LOG debug messages
2005 lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_mti_transactions';
2006
2007 lc_MTI_source_table CONSTANT NUMBER := 1;
2008
2009 l_table NUMBER;
2010 l_trans_count NUMBER;
2011 l_return_count NUMBER;
2012 BEGIN
2013 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2014 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
2015 lc_mod_name||'begin',
2016 'Entering private API process_mti_transactions' );
2017 END IF;
2018
2019 SAVEPOINT PROCESS_MTI_TRANSACTIONS_PVT;
2020
2021 -- Standard call to check for call compatibility.
2022 IF NOT FND_API.Compatible_API_Call(lc_api_version_number,
2023 p_api_version_number,
2024 lc_api_name,
2025 G_PKG_NAME) THEN
2026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2027 END IF;
2028
2029
2030 IF FND_API.to_boolean(p_init_msg_list) THEN
2031 FND_MSG_PUB.initialize;
2032 END IF;
2033 x_return_status := FND_API.G_RET_STS_SUCCESS;
2034
2035 -- Populate the constant values
2036 l_table := lc_MTI_source_table;
2037
2038 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2039 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2040 lc_mod_name||'beforecallprocesstxns',
2041 'Just before calling INV_TXN_MANAGER_PUB.process_Transactions');
2042 END IF;
2043
2044 l_return_count := INV_TXN_MANAGER_PUB.process_Transactions(
2045 p_api_version => lc_api_version_number, --1.0, -- ,
2046 p_init_msg_list => fnd_api.g_false, --'T', -- fnd_api.g_false ,
2047 p_commit => fnd_api.g_false, --'T', -- fnd_api.g_false ,
2048 p_validation_level => p_validation_level ,
2049 x_return_status => x_return_status,
2050 x_msg_count => x_msg_count,
2051 x_msg_data => x_msg_data,
2052 x_trans_count => l_trans_count,
2053 p_table => l_table,
2054 p_header_id => p_txn_header_id );
2055
2056 IF ( txn_int_error_exists( p_txn_header_id) or
2057 x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2058 FND_MESSAGE.SET_NAME('CSD','CSD_PROCESS_MTI_TXN_FAILURE');
2059 FND_MSG_PUB.ADD;
2060 RAISE FND_API.G_EXC_ERROR;
2061 END IF;
2062
2063 -- Standard check for p_commit
2064 IF FND_API.to_Boolean( p_commit )
2065 THEN
2066 COMMIT WORK;
2067 END IF;
2068
2069 EXCEPTION
2070 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2071 ROLLBACK to PROCESS_MTI_TRANSACTIONS_PVT ;
2072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2073
2074 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2075 p_count => x_msg_count,
2076 p_data => x_msg_data);
2077
2078 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2079 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
2080 lc_mod_name||'unx_exception',
2081 'G_EXC_UNEXPECTED_ERROR Exception');
2082 END IF;
2083
2084
2085 WHEN FND_API.G_EXC_ERROR THEN
2086 ROLLBACK to PROCESS_MTI_TRANSACTIONS_PVT ;
2087 x_return_status := FND_API.G_RET_STS_ERROR;
2088
2089 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2090 p_count => x_msg_count,
2091 p_data => x_msg_data);
2092
2093 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2094 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
2095 lc_mod_name||'exc_exception',
2096 'G_EXC_ERROR Exception');
2097 END IF;
2098
2099 WHEN OTHERS THEN
2100 ROLLBACK to PROCESS_MTI_TRANSACTIONS_PVT ;
2101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2102
2103 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2104 -- Add Unexpected Error to Message List, here SQLERRM is used for
2105 -- getting the error
2106 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
2107 p_procedure_name => lc_api_name );
2108 END IF;
2109
2110 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2111 p_count => x_msg_count,
2112 p_data => x_msg_data);
2113
2114 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2115 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
2116 lc_mod_name||'others_exception',
2117 'OTHERS Exception');
2118 END IF;
2119 END process_mti_transactions;
2120
2121 PROCEDURE process_oper_comp_txn
2122 (
2123 p_api_version_number IN NUMBER,
2124 p_init_msg_list IN VARCHAR2,
2125 p_commit IN VARCHAR2,
2126 p_validation_level IN NUMBER,
2127 x_return_status OUT NOCOPY VARCHAR2,
2128 x_msg_count OUT NOCOPY NUMBER,
2129 x_msg_data OUT NOCOPY VARCHAR2,
2130 p_mv_txn_dtls_tbl IN MV_TXN_DTLS_TBL_TYPE
2131 )
2132 IS
2133
2134 lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_OPER_COMP_TXN';
2135 lc_api_version_number CONSTANT NUMBER := 1.0;
2136
2137 -- constants used for FND_LOG debug messages
2138
2139 lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_oper_comp_txn';
2140
2141 -- Indicates that the process Phase is Validation
2142 -- lc_validation_phase CONSTANT NUMBER := 1;
2143
2144
2145
2146 -- Constants used for inserting into wip_move_txn_interface
2147
2148 lc_queue CONSTANT NUMBER := 1;
2149 lc_to_move CONSTANT NUMBER := 3;
2150 lc_error_process_status CONSTANT NUMBER := 3;
2151
2152
2153 -- Record to hold wip_move_txn_interface
2154
2155 l_wip_move_txn_interface_rec wip_move_txn_interface%ROWTYPE;
2156
2157
2158 /* l_process_phase NUMBER;
2159 l_material_transaction_id NUMBER; */
2160
2161 l_prev_wip_entity_id NUMBER := -1;
2162 l_prev_to_operation_seq_num NUMBER := -1;
2163 l_prev_transaction_quantity NUMBER := 0;
2164 l_error_exists VARCHAR2(6);
2165
2166 -- swai: bug 5330060
2167 -- temporary storgae of it move and in queue quantities
2168 l_qty_to_move NUMBER := 0;
2169 l_qty_in_queue NUMBER := 0;
2170 -- end swai: bug 5330060
2171
2172 CURSOR get_transaction_id IS
2173 SELECT wip_transactions_s.nextval from dual;
2174
2175
2176 Cursor check_mv_interface_errors ( c_group_id NUMBER ) IS
2177 select 'exists' from wip_move_txn_interface where
2178 group_id = c_group_id
2179 and process_status = lc_error_process_status;
2180
2181 -- swai: bug 5330060
2182 -- Get the previous operation for this job that has qty > 1
2183 -- for either in queue or waiting to move
2184 Cursor get_valid_previous_op (c_wip_entity_id NUMBER, c_op_seq_num NUMBER) IS
2185 select *
2186 from
2187 (select operation_seq_num,
2188 quantity_in_queue,
2189 quantity_waiting_to_move
2190 from wip_operations
2191 where wip_entity_id = c_wip_entity_id
2192 and operation_seq_num < c_op_seq_num
2193 and quantity_in_queue + quantity_waiting_to_move > 0
2194 order by operation_seq_num desc)
2195 where rownum=1;
2196 -- end swai: bug 5330060
2197
2198 /* CURSOR get_material_transaction_id IS
2199 SELECT mtl_material_transactions_s.nextval from dual; */
2200
2201
2202 BEGIN
2203
2204 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2205 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
2206 lc_mod_name||'begin',
2207 'Entering private API process_oper_comp_txn' );
2208 END IF;
2209
2210 SAVEPOINT PROCESS_OPER_COMP_TXN_PVT;
2211 -- Standard call to check for call compatibility.
2212 IF NOT FND_API.Compatible_API_Call
2213 (lc_api_version_number,
2214 p_api_version_number,
2215 lc_api_name,
2216 G_PKG_NAME)
2217 THEN
2218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2219 END IF;
2220
2221
2222 IF FND_API.to_boolean(p_init_msg_list) THEN
2223 FND_MSG_PUB.initialize;
2224 END IF;
2225
2226
2227
2228
2229 x_return_status := FND_API.G_RET_STS_SUCCESS;
2230
2231
2232 -- Populate the constant values
2233
2234
2235 -- l_process_phase := lc_validation_phase;
2236
2237
2238 -- generate transaction_id
2239 open get_transaction_id;
2240 -- fetch get_transaction_id into l_wip_move_txn_interface_rec.transaction_id;
2241 fetch get_transaction_id into l_wip_move_txn_interface_rec.group_id;
2242 close get_transaction_id;
2243
2244 -- l_wip_move_txn_interface_rec.group_id := l_wip_move_txn_interface_rec.transaction_id;
2245 l_wip_move_txn_interface_rec.transaction_date := sysdate;
2246 l_wip_move_txn_interface_rec.fm_intraoperation_step_type := lc_queue;
2247
2248
2249 -- swai: bug 5330060
2250 -- updated logic within for loop
2251 FOR mv_ctr in p_mv_txn_dtls_tbl.FIRST.. p_mv_txn_dtls_tbl.LAST
2252 LOOP
2253
2254 l_wip_move_txn_interface_rec.organization_id := p_mv_txn_dtls_tbl(mv_ctr).organization_id;
2255 l_wip_move_txn_interface_rec.wip_entity_name := p_mv_txn_dtls_tbl(mv_ctr).wip_entity_name;
2256 l_wip_move_txn_interface_rec.transaction_uom := p_mv_txn_dtls_tbl(mv_ctr).transaction_uom;
2257 l_wip_move_txn_interface_rec.to_operation_seq_num := nvl(p_mv_txn_dtls_tbl(mv_ctr).to_operation_seq_num,
2258 p_mv_txn_dtls_tbl(mv_ctr).fm_operation_seq_num ) ;
2259 -- if the to operation seq is 0, then it's the last operation,
2260 -- make sure the to step type is to_move, otherwise, it's in queue.
2261 if (p_mv_txn_dtls_tbl(mv_ctr).to_operation_seq_num is null) then
2262 l_wip_move_txn_interface_rec.to_intraoperation_step_type := lc_to_move;
2263 else
2264 l_wip_move_txn_interface_rec.to_intraoperation_step_type := lc_queue;
2265 end if;
2266
2267 -- if we are completing more than one operation for the same job, make
2268 -- the quantities are passed forward. Currently, do not allow the user
2269 -- to skip operations for the same job. (eg, complete 10, skip 20, complete 30)
2270 IF l_prev_wip_entity_id = p_mv_txn_dtls_tbl(mv_ctr).wip_entity_id THEN
2271 IF l_prev_to_operation_seq_num <> p_mv_txn_dtls_tbl(mv_ctr).fm_operation_seq_num THEN
2272 FND_MESSAGE.SET_NAME('CSD','CSD_OP_COMP_SEQ_ERROR');
2273 FND_MSG_PUB.ADD;
2274 RAISE FND_API.G_EXC_ERROR;
2275 ELSE
2276 l_wip_move_txn_interface_rec.transaction_quantity := p_mv_txn_dtls_tbl(mv_ctr).transaction_quantity +
2277 l_prev_transaction_quantity;
2278 END IF;
2279 ELSE
2280 l_wip_move_txn_interface_rec.transaction_quantity := p_mv_txn_dtls_tbl(mv_ctr).transaction_quantity;
2281 END IF;
2282
2283 -- use the values passed in if there are items to be transacted. Otherwise,
2284 -- we will attempt to find the appropriate from operation with an item that
2285 -- can be moved
2286 IF (l_wip_move_txn_interface_rec.transaction_quantity > 0) then
2287 l_wip_move_txn_interface_rec.fm_operation_seq_num := p_mv_txn_dtls_tbl(mv_ctr).fm_operation_seq_num;
2288 ELSE
2289 -- find the operation with a qty to complete, set the from operation to this
2290 open get_valid_previous_op (p_mv_txn_dtls_tbl(mv_ctr).wip_entity_id,
2291 p_mv_txn_dtls_tbl(mv_ctr).fm_operation_seq_num);
2292 fetch get_valid_previous_op into
2293 l_wip_move_txn_interface_rec.fm_operation_seq_num,
2294 l_qty_in_queue,
2295 l_qty_to_move;
2296 close get_valid_previous_op;
2297
2298 -- depending on where the item is, set the qty and from step type accordingly.
2299 if l_qty_in_queue > 0 then
2300 l_wip_move_txn_interface_rec.fm_intraoperation_step_type := lc_queue;
2301 l_wip_move_txn_interface_rec.transaction_quantity := l_qty_in_queue;
2302 elsif (l_qty_to_move > 0) then
2303 l_wip_move_txn_interface_rec.fm_intraoperation_step_type := lc_to_move;
2304 l_wip_move_txn_interface_rec.transaction_quantity := l_qty_to_move;
2305 end if;
2306 END IF;
2307
2308 -- set the variables to compare in the next loop iteration
2309 l_prev_wip_entity_id := p_mv_txn_dtls_tbl(mv_ctr).wip_entity_id;
2310 l_prev_to_operation_seq_num := l_wip_move_txn_interface_rec.to_operation_seq_num;
2311 l_prev_transaction_quantity := l_wip_move_txn_interface_rec.transaction_quantity;
2312
2313 --insert into table wip_move_txn_interface
2314 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2315 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2316 lc_mod_name||'beforecallinsertwipmvtxn',
2317 'Just before calling insert_wip_move_txn');
2318 END IF;
2319
2320 insert_wip_move_txn( p_wip_move_txn_interface_rec => l_wip_move_txn_interface_rec,
2321 x_return_status => x_return_status );
2322
2323 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2325 END IF;
2326
2327 END LOOP;
2328 -- end swai: bug 5330060
2329
2330
2331 /* open get_material_transaction_id;
2332 fetch get_material_transaction_id into l_material_transaction_id;
2333 close get_material_transaction_id;
2334 */
2335
2336 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2337 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2338 lc_mod_name||'beforecallprocesstxns',
2339 'Just before calling INV_TXN_MANAGER_PUB.process_Transactions');
2340 END IF;
2341
2342 /*
2343 wip_movProc_grp.processInterface(p_movTxnID => l_wip_move_txn_interface_rec.transaction_id,
2344 p_procPhase => l_process_phase,
2345 p_txnHdrID => l_material_transaction_id,
2346 p_mtlMode => WIP_CONSTANTS.ONLINE,
2347 p_cplTxnID => NULL,
2348 p_commit => FND_API.G_FALSE,
2349 x_returnStatus => x_return_status,
2350 x_errorMsg => x_msg_data);
2351
2352 */
2353
2354 wip_movProc_grp.processInterface(p_groupID => l_wip_move_txn_interface_rec.group_id,
2355 p_commit => FND_API.G_FALSE,
2356 x_returnStatus => x_return_status ) ;
2357
2358 -- Need to get errors from error table and pass it back
2359
2360
2361 open check_mv_interface_errors ( l_wip_move_txn_interface_rec.group_id );
2362 fetch check_mv_interface_errors into l_error_exists;
2363 close check_mv_interface_errors ;
2364
2365
2366 If ( l_error_exists is not null or
2367 x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2368
2369 -- ROLLBACK to PROCESS_OPER_COMP_TXN_PVT ;
2370
2371
2372 FND_MESSAGE.SET_NAME('CSD','CSD_MOVE_TXN_FAILURE');
2373 FND_MSG_PUB.ADD;
2374 RAISE FND_API.G_EXC_ERROR;
2375 -- Need to rollback Raise exception -
2376 -- once commit is removed from above call
2377
2378 end if;
2379
2380
2381
2382
2383
2384 /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2385 ROLLBACK to PROCESS_OPER_COMP_TXN_PVT ;
2386
2387
2388 FND_MESSAGE.SET_NAME('CSD','CSD_MOVE_TXN_FAILURE');
2389 FND_MSG_PUB.ADD;
2390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
2391
2392
2393 /* IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2394 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
2395 lc_mod_name||'exc_exception',
2396 'G_EXC_ERROR Exception');
2397 END IF;
2398
2399 RETURN; */
2400
2401 -- END IF;
2402
2403
2404 -- Standard check for p_commit
2405 IF FND_API.to_Boolean( p_commit )
2406 THEN
2407 COMMIT WORK;
2408 END IF;
2409
2410 EXCEPTION
2411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2412 ROLLBACK to PROCESS_OPER_COMP_TXN_PVT ;
2413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2414
2415 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2416 p_count => x_msg_count,
2417 p_data => x_msg_data);
2418
2419 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2420 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
2421 lc_mod_name||'unx_exception',
2422 'G_EXC_UNEXPECTED_ERROR Exception');
2423 END IF;
2424
2425
2426 WHEN FND_API.G_EXC_ERROR THEN
2427 ROLLBACK to PROCESS_OPER_COMP_TXN_PVT ;
2428 x_return_status := FND_API.G_RET_STS_ERROR;
2429
2430
2431 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2432 p_count => x_msg_count,
2433 p_data => x_msg_data);
2434
2435 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2436 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
2437 lc_mod_name||'exc_exception',
2438 'G_EXC_ERROR Exception');
2439 END IF;
2440
2441 WHEN OTHERS THEN
2442 ROLLBACK to PROCESS_OPER_COMP_TXN_PVT ;
2443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2444
2445 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2446
2447 -- Add Unexpected Error to Message List, here SQLERRM is used for
2448 -- getting the error
2449
2450 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
2451 p_procedure_name => lc_api_name );
2452 END IF;
2453
2454 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2455 p_count => x_msg_count,
2456 p_data => x_msg_data);
2457
2458 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2459 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
2460 lc_mod_name||'others_exception',
2461 'OTHERS Exception');
2462 END IF;
2463
2464 END process_oper_comp_txn;
2465
2466
2467
2468 PROCEDURE process_issue_mtl_txn
2469 (
2470 p_api_version_number IN NUMBER,
2471 p_init_msg_list IN VARCHAR2,
2472 p_commit IN VARCHAR2,
2473 p_validation_level IN NUMBER,
2474 x_return_status OUT NOCOPY VARCHAR2,
2475 x_msg_count OUT NOCOPY NUMBER,
2476 x_msg_data OUT NOCOPY VARCHAR2,
2477 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
2478 -- p_ro_quantity IN NUMBER,
2479 x_transaction_header_id OUT NOCOPY NUMBER
2480 )
2481 IS
2482
2483 lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ISSUE_MTL_TXN';
2484 lc_api_version_number CONSTANT NUMBER := 1.0;
2485
2486 -- constants used for FND_LOG debug messages
2487
2488 lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_issue_mtl_txn';
2489
2490
2491 lc_revision_controlled CONSTANT NUMBER := 2;
2492 lc_full_lot_control CONSTANT NUMBER := 2;
2493 lc_predefined_serial_control CONSTANT NUMBER := 2;
2494 lc_inven_rct_srl_control CONSTANT NUMBER := 5;
2495
2496 lc_predfn_loc_cntrl CONSTANT NUMBER := 2;
2497 lc_dyn_loc_cntrl CONSTANT NUMBER := 3;
2498 lc_subinv_lv_loc_cntrl CONSTANT NUMBER := 4;
2499 lc_inv_lv_loc_cntrl CONSTANT NUMBER := 5;
2500 lc_MTI_source_table CONSTANT NUMBER := 1;
2501
2502
2503 -- Constants Used for Inserting into wip_job_schedule_interface,
2504 -- and details interface tables
2505
2506 lc_non_std_update_load_type CONSTANT NUMBER := 3;
2507 --lc_non_std_update_load_type CONSTANT NUMBER := 9;
2508 lc_load_mtl_type CONSTANT NUMBER := 2;
2509 lc_substitution_change_type CONSTANT NUMBER := 3;
2510 -- lc_mrp_net_flag CONSTANT NUMBER := 1;
2511 -- 11/7/05
2512 -- lc_push_wip_supply_type CONSTANT NUMBER := 1;
2513
2514 -- Constants used for inserting into mtl_transactions_interface
2515
2516 lc_issue_source_code CONSTANT VARCHAR2(30) := 'WIP Issue';
2517 lc_wip_txn_source_type_id CONSTANT NUMBER := 5;
2518 lc_issue_txn_type CONSTANT NUMBER := 35;
2519 lc_wip_comp_return_txn_type CONSTANT NUMBER := 43;
2520 lc_non_std_wip_ent_type CONSTANT NUMBER := 3;
2521 lc_n_final_completion_flag CONSTANT VARCHAR2(1) := 'N' ;
2522
2523
2524 -- Records to hold the Job header,details
2525 -- and mtl_transactions_interface data
2526
2527 l_transactions_interface_rec mtl_transactions_interface%ROWTYPE;
2528 l_srl_nmbrs_interface_rec mtl_serial_numbers_interface%ROWTYPE;
2529 l_job_header_rec wip_job_schedule_interface%ROWTYPE;
2530 l_job_details_rec wip_job_dtls_interface%ROWTYPE;
2531
2532
2533
2534 l_table NUMBER;
2535 l_trans_count NUMBER;
2536 l_return_count NUMBER;
2537
2538 l_last_update_date DATE;
2539 l_last_updated_by NUMBER;
2540 l_last_update_login NUMBER;
2541
2542 l_locator_controlled VARCHAR2(1) := 'F';
2543
2544 l_wip_update_needed VARCHAR2(1) := 'F';
2545
2546 l_row_need_details_flag VARCHAR2(1) := 'F';
2547 l_need_details_flag VARCHAR2(1) := 'F';
2548 l_location_control_code NUMBER;
2549 l_primary_qty NUMBER;
2550
2551
2552
2553 CURSOR get_mtl_header_id IS
2554 SELECT mtl_material_transactions_s.nextval from dual;
2555
2556 CURSOR get_org_locator_control_code(p_organization_id NUMBER) IS
2557 SELECT stock_locator_control_code from mtl_parameters
2558 where organization_id = p_organization_id;
2559
2560 CURSOR get_si_locator_control_code ( p_organization_id NUMBER,
2561 p_secondary_inventory_name VARCHAR2 ) IS
2562 SELECT locator_type from mtl_secondary_inventories where
2563 organization_id = p_organization_id and
2564 secondary_inventory_name = p_secondary_inventory_name;
2565
2566 CURSOR get_inv_location_control_code ( p_organization_id NUMBER,
2567 p_inventory_item_id NUMBER ) IS
2568 select location_control_code from mtl_system_items_b where
2569 organization_id = p_organization_id and
2570 inventory_item_id = p_inventory_item_id;
2571
2572
2573
2574
2575 BEGIN
2576
2577 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2578 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
2579 lc_mod_name||'begin',
2580 'Entering private API process_issue_mtl_txn' );
2581 END IF;
2582
2583 SAVEPOINT PROCESS_ISSUE_MTL_TXN_PVT;
2584 -- Standard call to check for call compatibility.
2585 IF NOT FND_API.Compatible_API_Call
2586 (lc_api_version_number,
2587 p_api_version_number,
2588 lc_api_name,
2589 G_PKG_NAME)
2590 THEN
2591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2592 END IF;
2593
2594
2595 IF FND_API.to_boolean(p_init_msg_list) THEN
2596 FND_MSG_PUB.initialize;
2597 END IF;
2598
2599
2600
2601
2602 x_return_status := FND_API.G_RET_STS_SUCCESS;
2603
2604 -- x_need_details_flag := 'F';
2605
2606
2607
2608 -- Populate the constant values
2609
2610
2611 l_table := lc_MTI_source_table;
2612
2613
2614 -- Populate the row who columns
2615
2616 l_last_update_date := SYSDATE;
2617 l_last_updated_by := fnd_global.user_id;
2618 l_last_update_login := fnd_global.login_id;
2619
2620
2621 -- generate transaction_header_id
2622 open get_mtl_header_id;
2623 fetch get_mtl_header_id into l_transactions_interface_rec.transaction_header_id;
2624 close get_mtl_header_id;
2625
2626
2627 l_transactions_interface_rec.source_code := lc_issue_source_code;
2628
2629 l_transactions_interface_rec.transaction_date := sysdate;
2630 l_transactions_interface_rec.transaction_source_type_id := lc_wip_txn_source_type_id;
2631 --check/verify above again
2632
2633
2634 l_transactions_interface_rec.wip_entity_type := lc_non_std_wip_ent_type;
2635 l_transactions_interface_rec.final_completion_flag := lc_n_final_completion_flag;
2636
2637
2638 -- Populate the constant values
2639
2640 l_job_header_rec.load_type := lc_non_std_update_load_type;
2641
2642 l_job_details_rec.date_required := sysdate;
2643 l_job_details_rec.load_type := lc_load_mtl_type;
2644
2645 l_job_details_rec.substitution_type := lc_substitution_change_type;
2646
2647 -- l_job_details_rec.mrp_net_flag := lc_mrp_net_flag;
2648 -- 11/7/05
2649 -- l_job_details_rec.wip_supply_type := lc_push_wip_supply_type;
2650 l_job_details_rec.wip_supply_type := null;
2651 -- Get the Group_id to be used for WIP Mass Load,
2652
2653 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
2654
2655 -- l_job_header_rec.group_id := l_job_header_rec.header_id;
2656
2657 l_job_details_rec.group_id := l_job_header_rec.group_id;
2658 /* l_job_details_rec.parent_header_id := l_job_header_rec.group_id;
2659
2660 */
2661 -- l_completion_subinv := nvl( p_completion_subinv, fnd_profile.value('CSD_HV_COMP_SUBINV')) ;
2662 -- l_completion_loc_id : nvl( p_completion_loc_id, fnd_profile.value('CSD_HV_COMP_LOC_ID'));
2663
2664
2665
2666 FOR mtl_ctr in p_mtl_txn_dtls_tbl.FIRST.. p_mtl_txn_dtls_tbl.LAST
2667
2668 LOOP
2669
2670 l_transactions_interface_rec.transaction_quantity := (-1) * p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity;
2671
2672 If l_transactions_interface_rec.transaction_quantity = 0 then
2673 FND_MESSAGE.SET_NAME('CSD','CSD_ISS_QTY_ZERO');
2674 FND_MSG_PUB.ADD;
2675 RAISE FND_API.G_EXC_ERROR;
2676 end if;
2677
2678
2679 l_transactions_interface_rec.transaction_uom := p_mtl_txn_dtls_tbl(mtl_ctr).transaction_uom;
2680 -- l_transactions_interface_rec.primary_quantity
2681 -- Need to check later for above
2682 l_transactions_interface_rec.organization_id := p_mtl_txn_dtls_tbl(mtl_ctr).organization_id;
2683 l_transactions_interface_rec.transaction_source_id := p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id;
2684 l_transactions_interface_rec.source_header_id := p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id;
2685 l_transactions_interface_rec.source_line_id := p_mtl_txn_dtls_tbl(mtl_ctr).operation_seq_num;
2686 l_transactions_interface_rec.operation_seq_num := p_mtl_txn_dtls_tbl(mtl_ctr).operation_seq_num;
2687
2688 l_transactions_interface_rec.reason_id := p_mtl_txn_dtls_tbl(mtl_ctr).reason_id; -- swai bug 6841113
2689
2690 -- generate transaction_interface_id
2691 open get_mtl_header_id;
2692 fetch get_mtl_header_id into l_transactions_interface_rec.transaction_interface_id;
2693 close get_mtl_header_id;
2694
2695
2696 If p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity > 0 then
2697
2698 l_transactions_interface_rec.transaction_type_id:= lc_issue_txn_type;
2699 else
2700
2701 l_transactions_interface_rec.transaction_type_id:= lc_wip_comp_return_txn_type;
2702
2703 end if;
2704
2705
2706 -- above needs to be issue or return from job - based on quantity
2707 -- entered . If negative quantity entered, then a negative issue
2708 -- ,meaning Return Job needs to be done
2709
2710 -- Need to do validation in the client, such that
2711 -- if the return quantity is > issued quantity, there
2712 -- should be an error message
2713
2714
2715 If p_mtl_txn_dtls_tbl(mtl_ctr).revision_qty_control_code = lc_revision_controlled then
2716 -- swai: bug 6995498/7182047 - revision is defaulted in the UI, so do not default
2717 -- behind the user's back in the API. Just get value from record.
2718 l_transactions_interface_rec.revision := p_mtl_txn_dtls_tbl(mtl_ctr).revision;
2719
2720 -- dbms_output.put_line( 'revision is ' || l_transactions_interface_rec.revision );
2721 If l_transactions_interface_rec.revision is null then
2722 l_row_need_details_flag := 'T';
2723 end if;
2724
2725 end if;
2726
2727 l_transactions_interface_rec.inventory_item_id := p_mtl_txn_dtls_tbl(mtl_ctr).inventory_item_id;
2728
2729 If p_mtl_txn_dtls_tbl(mtl_ctr).supply_subinventory is not null THEN
2730
2731 l_transactions_interface_rec.subinventory_code := p_mtl_txn_dtls_tbl(mtl_ctr).supply_subinventory;
2732
2733 ELSE
2734
2735 IF fnd_profile.value('CSD_DEF_REP_INV_ORG') = p_mtl_txn_dtls_tbl(mtl_ctr).organization_id and
2736 fnd_profile.value('CSD_DEF_HV_SUBINV') is not null THEN
2737 l_transactions_interface_rec.subinventory_code := fnd_profile.value('CSD_DEF_HV_SUBINV');
2738 ELSE
2739 l_row_need_details_flag := 'T';
2740 END IF;
2741 END IF;
2742
2743 -- l_locator_controlled := Call Inventory procedure to get this
2744
2745 -- Get Locator Control
2746
2747 open get_org_locator_control_code ( l_transactions_interface_rec.organization_id ) ;
2748 fetch get_org_locator_control_code into l_location_control_code ;
2749 close get_org_locator_control_code;
2750
2751 If l_location_control_code = lc_subinv_lv_loc_cntrl THEN
2752
2753 If l_transactions_interface_rec.subinventory_code is not null THEN
2754 open get_si_locator_control_code ( l_transactions_interface_rec.organization_id ,
2755 l_transactions_interface_rec.subinventory_code ) ;
2756 fetch get_si_locator_control_code into l_location_control_code ;
2757 close get_si_locator_control_code;
2758
2759 If l_location_control_code = lc_inv_lv_loc_cntrl THEN
2760
2761
2762 open get_inv_location_control_code ( l_transactions_interface_rec.organization_id ,
2763 l_transactions_interface_rec.inventory_item_id ) ;
2764 fetch get_inv_location_control_code into l_location_control_code ;
2765 close get_inv_location_control_code;
2766
2767 end if;
2768
2769
2770 end If;
2771
2772 end if;
2773
2774
2775 If l_location_control_code in ( lc_predfn_loc_cntrl ,
2776 lc_dyn_loc_cntrl ) THEN
2777
2778 l_locator_controlled := 'T' ;
2779
2780 end if;
2781
2782
2783 -- dbms_output.put_line( 'l_locator_contrl is'
2784 -- || l_locator_controlled );
2785
2786
2787
2788 If l_locator_controlled = 'T' THEN
2789
2790 IF p_mtl_txn_dtls_tbl(mtl_ctr).supply_locator_id is not null THEN
2791
2792 -- dbms_output.put_line( 'p_mtl_txn_dtls_tbl(mtl_ctr).supply_locator_id is'
2793 -- || p_mtl_txn_dtls_tbl(mtl_ctr).supply_locator_id );
2794
2795 l_transactions_interface_rec.locator_id := p_mtl_txn_dtls_tbl(mtl_ctr).supply_locator_id;
2796
2797 ELSE
2798
2799 /* IF fnd_profile.value('CSD_DEF_REP_INV_ORG') = p_mtl_txn_dtls_tbl(mtl_ctr).organization_id and
2800 fnd_profile.value('CSD_DEF_HV_LOC_ID') is not null THEN
2801
2802
2803 l_transactions_interface_rec.locator_id := fnd_profile.value('CSD_DEF_HV_LOC_ID');
2804 dbms_output.put_line( 'l_transactions_interface_rec.locator_id is'
2805 || l_transactions_interface_rec.locator_id );
2806
2807 ELSE */
2808
2809 l_row_need_details_flag := 'T';
2810 -- END IF;
2811 END IF;
2812 END IF;
2813
2814 -- Lot Contrrolled Check
2815 -- Later Need to handle it here as well - based on profile
2816 -- Value
2817
2818 IF p_mtl_txn_dtls_tbl(mtl_ctr).lot_control_code
2819 = lc_full_lot_control THEN
2820
2821 l_row_need_details_flag := 'T' ;
2822
2823 END IF;
2824
2825
2826
2827 IF p_mtl_txn_dtls_tbl(mtl_ctr).serial_number_control_code
2828 in ( lc_predefined_serial_control, lc_inven_rct_srl_control) THEN
2829
2830 IF ( abs(p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity) > 1 ) THEN
2831
2832
2833 l_row_need_details_flag := 'T' ;
2834
2835
2836 If p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity > 0 then
2837
2838 l_transactions_interface_rec.transaction_quantity := -1;
2839 else
2840
2841 l_transactions_interface_rec.transaction_quantity := 1;
2842 end if;
2843
2844
2845 FOR l_qty_ctr IN 1..abs(p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity)
2846 LOOP
2847
2848
2849 --insert into table mtl_transactions_interface
2850
2851 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2852 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2853 lc_mod_name||'beforecallinserttxnshdr',
2854 'Just before calling insert_transactions_header');
2855 END IF;
2856
2857 l_transactions_interface_rec.source_line_id := -1;
2858 -- -1 identifies rows which are queried up in the details UI
2859
2860
2861 insert_transactions_header( p_transactions_interface_rec => l_transactions_interface_rec,
2862 x_return_status => x_return_status );
2863
2864
2865 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2867 END IF;
2868
2869 IF l_qty_ctr <> p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity THEN
2870 -- generate transaction_interface_id for the next record
2871 open get_mtl_header_id;
2872 fetch get_mtl_header_id into l_transactions_interface_rec.transaction_interface_id;
2873 close get_mtl_header_id;
2874 END IF;
2875
2876
2877 END LOOP;
2878
2879 ELSE -- quantity =1, serial controlled
2880
2881 IF p_mtl_txn_dtls_tbl(mtl_ctr).lot_control_code
2882 <> lc_full_lot_control THEN
2883
2884
2885 IF ( p_mtl_txn_dtls_tbl(mtl_ctr).serial_number is not null ) then
2886
2887
2888 l_srl_nmbrs_interface_rec.transaction_interface_id := l_transactions_interface_rec.transaction_interface_id;
2889
2890 l_srl_nmbrs_interface_rec.fm_serial_number := p_mtl_txn_dtls_tbl(mtl_ctr).serial_number;
2891
2892 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2893 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2894 lc_mod_name||'beforecallinsertsrlnmbrs',
2895 'Just before calling insert_upd_serial_numbers');
2896 END IF;
2897
2898
2899 insert_upd_serial_numbers( p_srl_nmbrs_interface_rec => l_srl_nmbrs_interface_rec,
2900 x_return_status => x_return_status );
2901
2902
2903 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2905 END IF;
2906
2907 ELSE
2908
2909 l_row_need_details_flag := 'T';
2910
2911 END IF;
2912
2913
2914 END IF;
2915
2916
2917 If l_row_need_details_flag = 'T' then
2918
2919 l_transactions_interface_rec.source_line_id := -1;
2920 -- -1 identifies rows which are queried up in the details UI
2921 end if;
2922
2923 --insert into table mtl_transactions_interface
2924
2925 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2926 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2927 lc_mod_name||'beforecallinserttxnshdr',
2928 'Just before calling insert_transactions_header');
2929 END IF;
2930
2931
2932
2933 insert_transactions_header( p_transactions_interface_rec => l_transactions_interface_rec,
2934 x_return_status => x_return_status );
2935
2936
2937 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2939 END IF;
2940
2941
2942
2943 END IF;
2944
2945
2946 ELSE -- not serial controlled
2947
2948
2949 IF l_row_need_details_flag = 'T' THEN
2950
2951 l_transactions_interface_rec.source_line_id := -1;
2952 -- -1 identifies rows which are queried up in the details UI
2953 END IF;
2954
2955
2956 --insert into table mtl_transactions_interface
2957
2958 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2959 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2960 lc_mod_name||'beforecallinserttxnshdr',
2961 'Just before calling insert_transactions_header');
2962 END IF;
2963
2964
2965 insert_transactions_header( p_transactions_interface_rec => l_transactions_interface_rec,
2966 x_return_status => x_return_status );
2967
2968
2969 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2970 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2971 END IF;
2972
2973 END IF;
2974
2975 -- Here, check if required_quantity for the material is
2976 -- equal to (issued_quantity + transaction_quantity) ,
2977 -- Only if it is different, need to populate and call wip
2978 -- update program
2979
2980 l_primary_qty := INV_CONVERT.INV_UM_CONVERT
2981 ( item_id => l_transactions_interface_rec.inventory_item_id
2982 , lot_number => null
2983 , organization_id => l_transactions_interface_rec.organization_id
2984 , precision => 5
2985 , from_quantity => p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity
2986 , from_unit => l_transactions_interface_rec.transaction_uom
2987 , to_unit => p_mtl_txn_dtls_tbl(mtl_ctr).uom_code
2988 , from_name => NULL
2989 , to_name => NULL);
2990
2991 -- dbms_output.put_line('primary qty is ' || l_primary_qty );
2992
2993 l_job_details_rec.required_quantity :=
2994 nvl(p_mtl_txn_dtls_tbl(mtl_ctr).issued_quantity, 0)+
2995 l_primary_qty;
2996 -- p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity;
2997
2998 -- l_job_details_rec.required_quantity := 60;
2999
3000 If l_job_details_rec.required_quantity <> p_mtl_txn_dtls_tbl(mtl_ctr).required_quantity THEN
3001 l_wip_update_needed := 'T' ;
3002
3003
3004 -- Get the Group_id to be used for WIP Mass Load,
3005
3006 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.header_id FROM dual;
3007
3008 -- l_job_header_rec.group_id := l_job_header_rec.header_id;
3009
3010 -- l_job_details_rec.group_id := l_job_header_rec.header_id;
3011 l_job_details_rec.parent_header_id := l_job_header_rec.header_id;
3012
3013
3014
3015 -- SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.interface_id FROM dual;
3016
3017
3018 --Commenting out as don't think this is needed
3019 -- Need to remove ro_quantity as well, later on if not neede
3020 l_job_details_rec.quantity_per_assembly := l_job_details_rec.required_quantity / p_mtl_txn_dtls_tbl(mtl_ctr).job_quantity;
3021
3022 -- l_job_details_rec.quantity_per_assembly := 3;
3023
3024 l_job_header_rec.wip_entity_id := p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id;
3025 l_job_header_rec.organization_id := p_mtl_txn_dtls_tbl(mtl_ctr).organization_id;
3026
3027 l_job_details_rec.inventory_item_id_old := p_mtl_txn_dtls_tbl(mtl_ctr).inventory_item_id;
3028 l_job_details_rec.operation_seq_num := p_mtl_txn_dtls_tbl(mtl_ctr).operation_seq_num;
3029 l_job_details_rec.organization_id := p_mtl_txn_dtls_tbl(mtl_ctr).organization_id;
3030
3031 l_job_details_rec.wip_entity_id := p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id;
3032
3033 -- Call procedures to insert job header and job details information
3034 -- into wip interface tables
3035
3036 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3037 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3038 lc_mod_name||'beforecallinsertjob',
3039 'Just before calling insert_job_header');
3040 END IF;
3041
3042
3043 insert_job_header( p_job_header_rec => l_job_header_rec,
3044 x_return_status => x_return_status );
3045
3046
3047 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3049 END IF;
3050
3051
3052 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3053 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3054 lc_mod_name||'beforecallinsertjobdtls',
3055 'Just before calling insert_job_details');
3056 END IF;
3057
3058 /* dbms_output.put_line('ll_job_details_rec.required_quantity is '
3059 || l_job_details_rec.required_quantity );
3060
3061 dbms_output.put_line('ll_job_details_rec.quantity_per_assembly is '
3062 || l_job_details_rec.quantity_per_assembly ); */
3063
3064 insert_job_details( p_job_details_rec => l_job_details_rec,
3065 x_return_status => x_return_status );
3066
3067 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3069 END IF;
3070
3071
3072 END IF;
3073
3074
3075 ---- Update CSD_WIP_TRANSACTION_DETAILS rows to have null values
3076
3077 If p_mtl_txn_dtls_tbl(mtl_ctr).WIP_TRANSACTION_DETAIL_ID is not null then
3078
3079 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3080 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3081 lc_mod_name||'beforecallupdaterow',
3082 'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row');
3083 END IF;
3084
3085
3086
3087 /* CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row(
3088 p_WIP_TRANSACTION_DETAIL_ID => p_mtl_txn_dtls_tbl(mtl_ctr).WIP_TRANSACTION_DETAIL_ID
3089 ,p_CREATED_BY => null
3090 ,p_CREATION_DATE => null
3091 ,p_LAST_UPDATED_BY => l_last_updated_by
3092 ,p_LAST_UPDATE_DATE => l_last_update_date
3093 ,p_LAST_UPDATE_LOGIN => l_last_update_login
3094 ,p_INVENTORY_ITEM_ID => null
3095 ,p_WIP_ENTITY_ID => null
3096 ,p_OPERATION_SEQ_NUM => null
3097 ,p_RESOURCE_SEQ_NUM => null
3098 ,p_INSTANCE_ID => null
3099 ,p_TRANSACTION_QUANTITY => FND_API.G_MISS_NUM
3100 ,p_TRANSACTION_UOM => FND_API.G_MISS_CHAR
3101 ,p_SERIAL_NUMBER => FND_API.G_MISS_CHAR
3102 ,p_OBJECT_VERSION_NUMBER => p_mtl_txn_dtls_tbl(mtl_ctr).object_version_number
3103 ); */
3104
3105 CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row(
3106 p_WIP_TRANSACTION_DETAIL_ID => p_mtl_txn_dtls_tbl(mtl_ctr).WIP_TRANSACTION_DETAIL_ID );
3107
3108
3109 end if;
3110
3111 IF l_row_need_details_flag = 'T' THEN
3112
3113 l_row_need_details_flag := 'F';
3114 l_need_details_flag := 'T' ;
3115
3116 END IF;
3117
3118 END LOOP;
3119
3120 IF l_need_details_flag = 'T' THEN
3121
3122 x_transaction_header_id := l_transactions_interface_rec.transaction_header_id;
3123
3124 END IF;
3125
3126 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3127 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3128 lc_mod_name||'beforecallprocesstxns',
3129 'Just before calling INV_TXN_MANAGER_PUB.process_Transactions');
3130 END IF;
3131
3132 -- dbms_output.put_line('Before call to INV_TXN_MANAGER_PUB.process_Transactions');
3133
3134 -- If transaction_header_id is null, then details are not needed
3135 IF l_need_details_flag = 'F' THEN
3136
3137 l_return_count := INV_TXN_MANAGER_PUB.process_Transactions(
3138 p_api_version => lc_api_version_number, --1.0, -- ,
3139 p_init_msg_list => fnd_api.g_false, --'T', -- fnd_api.g_false ,
3140 p_commit => fnd_api.g_false, --'T', -- fnd_api.g_false ,
3141 p_validation_level => p_validation_level ,
3142 x_return_status => x_return_status,
3143 x_msg_count => x_msg_count,
3144 x_msg_data => x_msg_data,
3145 x_trans_count => l_trans_count,
3146 p_table => l_table,
3147 p_header_id => l_transactions_interface_rec.transaction_header_id );
3148
3149
3150 If ( txn_int_error_exists( l_transactions_interface_rec.transaction_header_id ) or
3151 x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3152 -- x_msg_data := x_msg_data||' after pt ' ;
3153 -- ROLLBACK to PROCESS_ISSUE_MTL_TXN_PVT ;
3154
3155
3156 -- IF nvl( x_msg_count, 0 ) = 0 THEN
3157
3158 FND_MESSAGE.SET_NAME('CSD','CSD_MAT_TXN_FAILURE');
3159 FND_MSG_PUB.ADD;
3160
3161 RAISE FND_API.G_EXC_ERROR;
3162
3163 -- END IF;
3164
3165
3166
3167 -- RETURN;
3168
3169 END IF;
3170
3171 END IF;
3172
3173
3174
3175 -- Call WIP Mass Load API
3176
3177 -- Comment out for now - till WIP API works - Uncomment later
3178
3179 IF l_wip_update_needed = 'T' THEN
3180
3181 -- dbms_output.put_line('before WIP Update Call');
3182
3183 BEGIN
3184
3185 WIP_MASSLOAD_PUB.massLoadJobs(p_groupID => l_job_header_rec.group_id,
3186 p_validationLevel => p_validation_level,
3187 p_commitFlag => 1,-- needs to be changed to 0 later, once WIP works
3188 x_returnStatus => x_return_status,
3189 x_errorMsg => x_msg_data );
3190
3191
3192 If ( ml_error_exists( l_job_header_rec.group_id ) or
3193 x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3194
3195
3196 FND_MESSAGE.SET_NAME('CSD','CSD_MTL_ISS_MASS_LD_FAILURE');
3197 FND_MSG_PUB.ADD;
3198 x_return_status := FND_API.G_RET_STS_ERROR;
3199
3200 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3201 p_count => x_msg_count,
3202 p_data => x_msg_data);
3203
3204 RETURN;
3205 -- Need to rollback Raise exception -
3206 -- once commit is removed from above call
3207
3208 end if;
3209
3210 EXCEPTION
3211 WHEN OTHERS THEN
3212
3213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3214
3215 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3216
3217 -- Add Unexpected Error to Message List, here SQLERRM is used for
3218 -- getting the error
3219
3220 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
3221 p_procedure_name => lc_api_name );
3222 END IF;
3223
3224 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3225 p_count => x_msg_count,
3226 p_data => x_msg_data);
3227
3228 END;
3229
3230
3231 /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3232 -- ROLLBACK to PROCESS_ISSUE_MTL_TXN_PVT ;
3233 -- dbms_output.put_line('WIP Update Error');
3234
3235
3236 FND_MESSAGE.SET_NAME('CSD','CSD_MAT_TXN_FAILURE');
3237 FND_MSG_PUB.ADD;
3238 RETURN; */
3239 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3240
3241
3242 /* IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3243 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
3244 lc_mod_name||'exc_exception',
3245 'G_EXC_ERROR Exception');
3246 END IF;
3247
3248 RETURN; */
3249
3250 -- END IF;
3251 END IF;
3252
3253
3254
3255
3256
3257 -- Standard check for p_commit
3258 IF l_need_details_flag = 'F' and FND_API.to_Boolean( p_commit )
3259 THEN
3260 COMMIT WORK;
3261 END IF;
3262
3263 EXCEPTION
3264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3265 -- dbms_output.put_line( 'FND_API.G_EXC_UNEXPECTED_ERROR error'|| sqlerrm);
3266 ROLLBACK to PROCESS_ISSUE_MTL_TXN_PVT ;
3267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3268
3269 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3270 p_count => x_msg_count,
3271 p_data => x_msg_data);
3272
3273 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3274 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
3275 lc_mod_name||'unx_exception',
3276 'G_EXC_UNEXPECTED_ERROR Exception');
3277 END IF;
3278
3279
3280 WHEN FND_API.G_EXC_ERROR THEN
3281 ROLLBACK to PROCESS_ISSUE_MTL_TXN_PVT ;
3282 x_return_status := FND_API.G_RET_STS_ERROR;
3283
3284
3285 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3286 p_count => x_msg_count,
3287 p_data => x_msg_data);
3288
3289 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3290 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
3291 lc_mod_name||'exc_exception',
3292 'G_EXC_ERROR Exception');
3293 END IF;
3294
3295 WHEN OTHERS THEN
3296 -- dbms_output.put_line( 'OTHERS error' || sqlerrm );
3297 ROLLBACK to PROCESS_ISSUE_MTL_TXN_PVT ;
3298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3299
3300 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3301
3302 -- Add Unexpected Error to Message List, here SQLERRM is used for
3303 -- getting the error
3304
3305 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
3306 p_procedure_name => lc_api_name );
3307 -- END IF;
3308
3309 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3310 p_count => x_msg_count,
3311 p_data => x_msg_data);
3312
3313 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3314 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
3315 lc_mod_name||'others_exception',
3316 'OTHERS Exception');
3317 END IF;
3318
3319
3320
3321 END process_issue_mtl_txn;
3322
3323 --
3324 -- Updates the transaction lines with lot and serial numbers
3325 -- and the processes the transaction lines
3326 --
3327 PROCEDURE process_issue_mtl_txns_lot_srl
3328 (
3329 p_api_version_number IN NUMBER,
3330 p_init_msg_list IN VARCHAR2,
3331 p_commit IN VARCHAR2,
3332 p_validation_level IN NUMBER,
3333 x_return_status OUT NOCOPY VARCHAR2,
3334 x_msg_count OUT NOCOPY NUMBER,
3335 x_msg_data OUT NOCOPY VARCHAR2,
3336 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
3337 p_transaction_header_id IN NUMBER
3338 )
3339 IS
3340 lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ISSUE_MTL_TXNS_LOT_SRL';
3341 lc_api_version_number CONSTANT NUMBER := 1.0;
3342
3343 -- constants used for FND_LOG debug messages
3344 lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_issue_mtl_txns_lot_srl';
3345
3346 BEGIN
3347 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3348 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3349 lc_mod_name||'begin',
3350 'Entering private API process_issue_mtl_txns_lot_srl' );
3351 END IF;
3352
3353 SAVEPOINT PROCESS_MTL_TXNS_LOT_SRL_PVT;
3354 -- Standard call to check for call compatibility.
3355 IF NOT FND_API.Compatible_API_Call
3356 (lc_api_version_number,
3357 p_api_version_number,
3358 lc_api_name,
3359 G_PKG_NAME)
3360 THEN
3361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3362 END IF;
3363
3364 IF FND_API.to_boolean(p_init_msg_list) THEN
3365 FND_MSG_PUB.initialize;
3366 END IF;
3367
3368 x_return_status := FND_API.G_RET_STS_SUCCESS;
3369
3370 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3371 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3372 lc_mod_name||'beforecallinsertjobcomptxn',
3373 'Just before calling insert_job_comp_txn');
3374 END IF;
3375 update_mtl_txns_lot_srl (
3376 p_api_version_number => lc_api_version_number,
3377 p_init_msg_list => fnd_api.g_false ,
3378 p_commit => fnd_api.g_false,
3379 p_validation_level => p_validation_level,
3380 x_return_status => x_return_status,
3381 x_msg_count => x_msg_count,
3382 x_msg_data => x_msg_data,
3383 p_mtl_txn_dtls_tbl => p_mtl_txn_dtls_tbl,
3384 p_transaction_header_id => p_transaction_header_id
3385 );
3386 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3387 FND_MESSAGE.SET_NAME('CSD','CSD_MAT_TXN_FAILURE');
3388 FND_MSG_PUB.ADD;
3389 RAISE FND_API.G_EXC_ERROR;
3390 END IF;
3391
3392 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3393 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3394 lc_mod_name||'beforecallprocesstxn',
3395 'Just before calling process_mti_transactions');
3396 END IF;
3397 process_mti_transactions(
3398 p_api_version_number => lc_api_version_number,
3399 p_init_msg_list => fnd_api.g_false ,
3400 p_commit => fnd_api.g_false,
3401 p_validation_level => p_validation_level,
3402 x_return_status => x_return_status,
3403 x_msg_count => x_msg_count,
3404 x_msg_data => x_msg_data,
3405 p_txn_header_id => p_transaction_header_id
3406 -- p_txn_type IN VARCHAR2
3407 );
3408 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3409 FND_MESSAGE.SET_NAME('CSD','CSD_MAT_TXN_FAILURE');
3410 FND_MSG_PUB.ADD;
3411 RAISE FND_API.G_EXC_ERROR;
3412 END IF;
3413
3414 -- Standard check for p_commit
3415 IF FND_API.to_Boolean( p_commit )
3416 THEN
3417 COMMIT WORK;
3418 END IF;
3419
3420 EXCEPTION
3421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3422 ROLLBACK to PROCESS_MTL_TXNS_LOT_SRL_PVT ;
3423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3424
3425 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3426 p_count => x_msg_count,
3427 p_data => x_msg_data);
3428
3429 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3430 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
3431 lc_mod_name||'unx_exception',
3432 'G_EXC_UNEXPECTED_ERROR Exception');
3433 END IF;
3434
3435 WHEN FND_API.G_EXC_ERROR THEN
3436 ROLLBACK to PROCESS_MTL_TXNS_LOT_SRL_PVT ;
3437 x_return_status := FND_API.G_RET_STS_ERROR;
3438
3439 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3440 p_count => x_msg_count,
3441 p_data => x_msg_data);
3442
3443 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3444 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
3445 lc_mod_name||'exc_exception',
3446 'G_EXC_ERROR Exception');
3447 END IF;
3448
3449 WHEN OTHERS THEN
3450 ROLLBACK to PROCESS_MTL_TXNS_LOT_SRL_PVT ;
3451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3452
3453 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3454 -- Add Unexpected Error to Message List, here SQLERRM is used for
3455 -- getting the error
3456 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
3457 p_procedure_name => lc_api_name );
3458 END IF;
3459
3460 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3461 p_count => x_msg_count,
3462 p_data => x_msg_data);
3463
3464 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3465 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
3466 lc_mod_name||'others_exception',
3467 'OTHERS Exception');
3468 END IF;
3469 END process_issue_mtl_txns_lot_srl;
3470
3471 --
3472 -- Updates the material transaction lines with lot and serial numbers only
3473 -- Does NOT process the transaction lines
3474 --
3475 PROCEDURE update_mtl_txns_lot_srl
3476 (
3477 p_api_version_number IN NUMBER,
3478 p_init_msg_list IN VARCHAR2,
3479 p_commit IN VARCHAR2,
3480 p_validation_level IN NUMBER,
3481 x_return_status OUT NOCOPY VARCHAR2,
3482 x_msg_count OUT NOCOPY NUMBER,
3483 x_msg_data OUT NOCOPY VARCHAR2,
3484 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
3485 p_transaction_header_id IN NUMBER
3486 )
3487 IS
3488 lc_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MTL_TXNS_LOT_SRL';
3489 lc_api_version_number CONSTANT NUMBER := 1.0;
3490
3491 -- constants used for FND_LOG debug messages
3492 lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.update_mtl_txns_lot_srl';
3493
3494 lc_revision_controlled CONSTANT NUMBER := 2;
3495 lc_full_lot_control CONSTANT NUMBER := 2;
3496 lc_predefined_serial_control CONSTANT NUMBER := 2;
3497 lc_inven_rct_srl_control CONSTANT NUMBER := 5;
3498
3499 -- Records to hold mtl_transactions_interface data
3500 l_transactions_interface_rec mtl_transactions_interface%ROWTYPE;
3501 l_txn_lots_interface_rec mtl_transaction_lots_interface%ROWTYPE;
3502 l_srl_nmbrs_interface_rec mtl_serial_numbers_interface%ROWTYPE;
3503
3504 CURSOR get_mtl_header_id IS
3505 SELECT mtl_material_transactions_s.nextval from dual;
3506
3507 BEGIN
3508
3509 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3510 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3511 lc_mod_name||'begin',
3512 'Entering private API update_mtl_txns_lot_srl' );
3513 END IF;
3514
3515 SAVEPOINT UPDATE_MTL_TXNS_LOT_SRL_PVT;
3516 -- Standard call to check for call compatibility.
3517 IF NOT FND_API.Compatible_API_Call
3518 (lc_api_version_number,
3519 p_api_version_number,
3520 lc_api_name,
3521 G_PKG_NAME)
3522 THEN
3523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3524 END IF;
3525
3526 IF FND_API.to_boolean(p_init_msg_list) THEN
3527 FND_MSG_PUB.initialize;
3528 END IF;
3529
3530 x_return_status := FND_API.G_RET_STS_SUCCESS;
3531
3532 FOR mtl_ctr in p_mtl_txn_dtls_tbl.FIRST.. p_mtl_txn_dtls_tbl.LAST
3533 LOOP
3534 l_transactions_interface_rec.subinventory_code := p_mtl_txn_dtls_tbl(mtl_ctr).supply_subinventory;
3535 l_transactions_interface_rec.locator_id := p_mtl_txn_dtls_tbl(mtl_ctr).supply_locator_id;
3536 l_transactions_interface_rec.revision := p_mtl_txn_dtls_tbl(mtl_ctr).revision;
3537 l_transactions_interface_rec.transaction_interface_id := p_mtl_txn_dtls_tbl(mtl_ctr).transaction_interface_id;
3538 l_transactions_interface_rec.source_line_id := p_mtl_txn_dtls_tbl(mtl_ctr).operation_seq_num;
3539 l_transactions_interface_rec.reason_id := p_mtl_txn_dtls_tbl(mtl_ctr).reason_id; -- swai bug 6841113
3540
3541 --Update table mtl_transactions_interface
3542 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3543 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3544 lc_mod_name||'beforecallupdtxnhdr',
3545 'Just before calling update_transactions_header');
3546 END IF;
3547
3548 update_transactions_header(p_transactions_interface_rec => l_transactions_interface_rec,
3549 x_return_status => x_return_status );
3550
3551 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3553 END IF;
3554
3555 -- Check for Lot Control
3556 IF p_mtl_txn_dtls_tbl(mtl_ctr).lot_control_code = lc_full_lot_control THEN
3557 l_txn_lots_interface_rec.transaction_interface_id := l_transactions_interface_rec.transaction_interface_id;
3558 l_txn_lots_interface_rec.lot_number := p_mtl_txn_dtls_tbl(mtl_ctr).lot_number;
3559 l_txn_lots_interface_rec.transaction_quantity := p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity;
3560
3561 IF p_mtl_txn_dtls_tbl(mtl_ctr).SERIAL_NUMBER_CONTROL_CODE in ( lc_predefined_serial_control , lc_inven_rct_srl_control ) THEN
3562 -- generate transaction_id
3563 open get_mtl_header_id;
3564 fetch get_mtl_header_id into l_txn_lots_interface_rec.serial_transaction_temp_id;
3565 close get_mtl_header_id;
3566
3567 l_srl_nmbrs_interface_rec.transaction_interface_id := l_txn_lots_interface_rec.serial_transaction_temp_id;
3568 l_srl_nmbrs_interface_rec.fm_serial_number := p_mtl_txn_dtls_tbl(mtl_ctr).serial_number;
3569
3570 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3571 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
3572 lc_mod_name||'beforecallinsertsrlnmbrs',
3573 'Just before calling insert_upd_serial_numbers');
3574 END IF;
3575
3576 insert_upd_serial_numbers(p_srl_nmbrs_interface_rec => l_srl_nmbrs_interface_rec,
3577 x_return_status => x_return_status);
3578
3579 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3581 END IF;
3582 END IF;
3583
3584 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3585 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
3586 lc_mod_name||'beforecallinserttxnslots',
3587 'Just before calling insert_transaction_lots');
3588 END IF;
3589
3590 insert_transaction_lots(p_txn_lots_interface_rec => l_txn_lots_interface_rec,
3591 x_return_status => x_return_status );
3592
3593 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3595 END IF;
3596 ELSE -- not lc_full_lot_control
3597 IF p_mtl_txn_dtls_tbl(mtl_ctr).SERIAL_NUMBER_CONTROL_CODE in ( lc_predefined_serial_control , lc_inven_rct_srl_control ) THEN
3598 l_srl_nmbrs_interface_rec.transaction_interface_id := l_transactions_interface_rec.transaction_interface_id;
3599 l_srl_nmbrs_interface_rec.fm_serial_number := p_mtl_txn_dtls_tbl(mtl_ctr).serial_number;
3600
3601 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3602 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
3603 lc_mod_name||'beforecallinsertsrlnbrs',
3604 'Just before calling insert_upd_serial_numbers');
3605 END IF;
3606
3607 insert_upd_serial_numbers(p_srl_nmbrs_interface_rec => l_srl_nmbrs_interface_rec,
3608 x_return_status => x_return_status );
3609
3610 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3612 END IF;
3613 END IF;
3614 END IF; -- end lot control condition
3615 END LOOP;
3616
3617 -- Standard check for p_commit
3618 IF FND_API.to_Boolean( p_commit )
3619 THEN
3620 COMMIT WORK;
3621 END IF;
3622
3623 EXCEPTION
3624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3625 ROLLBACK to UPDATE_MTL_TXNS_LOT_SRL_PVT ;
3626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3627
3628 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3629 p_count => x_msg_count,
3630 p_data => x_msg_data);
3631
3632 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3633 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
3634 lc_mod_name||'unx_exception',
3635 'G_EXC_UNEXPECTED_ERROR Exception');
3636 END IF;
3637
3638 WHEN FND_API.G_EXC_ERROR THEN
3639 ROLLBACK to UPDATE_MTL_TXNS_LOT_SRL_PVT ;
3640 x_return_status := FND_API.G_RET_STS_ERROR;
3641
3642 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3643 p_count => x_msg_count,
3644 p_data => x_msg_data);
3645
3646 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3647 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
3648 lc_mod_name||'exc_exception',
3649 'G_EXC_ERROR Exception');
3650 END IF;
3651
3652 WHEN OTHERS THEN
3653 ROLLBACK to UPDATE_MTL_TXNS_LOT_SRL_PVT ;
3654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3655
3656 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3657 -- Add Unexpected Error to Message List, here SQLERRM is used for
3658 -- getting the error
3659 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
3660 p_procedure_name => lc_api_name );
3661 END IF;
3662
3663 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3664 p_count => x_msg_count,
3665 p_data => x_msg_data);
3666
3667 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3668 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
3669 lc_mod_name||'others_exception',
3670 'OTHERS Exception');
3671 END IF;
3672 END update_mtl_txns_lot_srl;
3673
3674
3675 PROCEDURE process_transact_res_txn
3676 (
3677 p_api_version_number IN NUMBER,
3678 p_init_msg_list IN VARCHAR2,
3679 p_commit IN VARCHAR2,
3680 p_validation_level IN NUMBER,
3681 x_return_status OUT NOCOPY VARCHAR2,
3682 x_msg_count OUT NOCOPY NUMBER,
3683 x_msg_data OUT NOCOPY VARCHAR2,
3684 p_res_txn_dtls_tbl IN RES_TXN_DTLS_TBL_TYPE
3685 -- p_ro_quantity IN NUMBER
3686 )
3687 IS
3688
3689 lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_TRANSACT_RES_TXN';
3690 lc_api_version_number CONSTANT NUMBER := 1.0;
3691
3692 -- constants used for FND_LOG debug messages
3693
3694 lc_mod_name VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_transact_res_txn';
3695
3696
3697 -- Constants Used for Inserting into wip_job_schedule_interface,
3698 -- and details interface tables
3699
3700 lc_non_std_update_load_type CONSTANT NUMBER := 3;
3701 lc_load_res_type CONSTANT NUMBER := 1;
3702 lc_substitution_change_type CONSTANT NUMBER := 3;
3703
3704
3705 -- Constants used for inserting into wip_cost_txn_interface
3706
3707 lc_res_transaction_type CONSTANT NUMBER := 1;
3708
3709
3710 -- Records to hold the Job header,details
3711 -- and wip_cost_txn_interface data
3712
3713 l_wip_cost_txn_interface_rec wip_cost_txn_interface%ROWTYPE;
3714 l_job_header_rec wip_job_schedule_interface%ROWTYPE;
3715 l_job_details_rec wip_job_dtls_interface%ROWTYPE;
3716
3717
3718 l_last_update_date DATE;
3719 l_last_updated_by NUMBER;
3720 l_last_update_login NUMBER;
3721
3722 l_required_quantity NUMBER;
3723
3724 l_wip_update_needed VARCHAR2(1) := 'F';
3725
3726 conversion_rate NUMBER;
3727 primary_qty NUMBER;
3728
3729
3730
3731 BEGIN
3732
3733 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3734 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
3735 lc_mod_name||'begin',
3736 'Entering private API process_transact_res_txn' );
3737 END IF;
3738
3739 SAVEPOINT PROCESS_TRANSACT_RES_TXN_PVT;
3740 -- Standard call to check for call compatibility.
3741 IF NOT FND_API.Compatible_API_Call
3742 (lc_api_version_number,
3743 p_api_version_number,
3744 lc_api_name,
3745 G_PKG_NAME)
3746 THEN
3747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3748 END IF;
3749
3750
3751 IF FND_API.to_boolean(p_init_msg_list) THEN
3752 FND_MSG_PUB.initialize;
3753 END IF;
3754
3755
3756
3757
3758 x_return_status := FND_API.G_RET_STS_SUCCESS;
3759
3760
3761 -- Populate the row who columns
3762
3763 l_last_update_date := SYSDATE;
3764 l_last_updated_by := fnd_global.user_id;
3765 l_last_update_login := fnd_global.login_id;
3766
3767
3768 -- Populate the constant values
3769
3770 l_job_header_rec.load_type := lc_non_std_update_load_type;
3771
3772 l_job_details_rec.completion_date := sysdate;
3773
3774 l_job_details_rec.load_type := lc_load_res_type;
3775
3776 l_job_details_rec.substitution_type := lc_substitution_change_type;
3777
3778
3779 -- l_job_details_rec.autocharge_type := fnd_profile.value('CSD_HV_ATCHG_TYP');
3780
3781 -- If autocharge_type is null, throw an error and return;
3782 -- Uncomment following later - once profiles are defined
3783 /* IF l_job_details_rec.autocharge_type is NULL THEN
3784
3785 FND_MESSAGE.SET_NAME('CSD','CSD_ATCHG_TYP_NULL');
3786 FND_MSG_PUB.ADD;
3787 RAISE FND_API.G_EXC_ERROR;
3788 end if;
3789
3790
3791 l_job_details_rec.basis_type := fnd_profile.value('CSD_HV_BASIS_TYP');
3792
3793 -- If basis_type is null, throw an error and return;
3794
3795 IF l_job_details_rec.basis_type is NULL THEN
3796
3797 FND_MESSAGE.SET_NAME('CSD','CSD_BASIS_TYP_NULL');
3798 FND_MSG_PUB.ADD;
3799 RAISE FND_API.G_EXC_ERROR;
3800 end if;
3801
3802 l_job_details_rec.scheduled_flag := fnd_profile.value('CSD_HV_SCD_FLG');
3803
3804 -- If scheduled_flag is null, throw an error and return;
3805
3806 IF l_job_details_rec.scheduled_flag is NULL THEN
3807
3808 FND_MESSAGE.SET_NAME('CSD','CSD_SCD_FLG_NULL');
3809 FND_MSG_PUB.ADD;
3810 RAISE FND_API.G_EXC_ERROR;
3811 end if;
3812
3813 l_job_details_rec.standard_rate_flag := fnd_profile.value('CSD_HV_STD_FLG');
3814
3815 -- If standard_rate_flag is null, throw an error and return;
3816
3817 IF l_job_details_rec.standard_rate_flag is NULL THEN
3818
3819 FND_MESSAGE.SET_NAME('CSD','CSD_STD_FLG_NULL');
3820 FND_MSG_PUB.ADD;
3821 RAISE FND_API.G_EXC_ERROR;
3822 end if; */
3823
3824 l_wip_cost_txn_interface_rec.transaction_date := sysdate;
3825 l_wip_cost_txn_interface_rec.transaction_type := lc_res_transaction_type;
3826
3827
3828 -- Get the Group_id to be used for WIP Mass Load,
3829
3830 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
3831
3832 -- l_job_header_rec.group_id := l_job_header_rec.header_id;
3833
3834 l_job_details_rec.group_id := l_job_header_rec.group_id;
3835 -- l_job_details_rec.parent_header_id := l_job_header_rec.group_id;
3836
3837
3838
3839 FOR res_ctr in p_res_txn_dtls_tbl.FIRST.. p_res_txn_dtls_tbl.LAST
3840
3841 LOOP
3842
3843 l_wip_cost_txn_interface_rec.operation_seq_num := p_res_txn_dtls_tbl(res_ctr).operation_seq_num;
3844 l_wip_cost_txn_interface_rec.organization_id := p_res_txn_dtls_tbl(res_ctr).organization_id;
3845 l_wip_cost_txn_interface_rec.organization_code := p_res_txn_dtls_tbl(res_ctr).organization_code;
3846 l_wip_cost_txn_interface_rec.resource_seq_num := p_res_txn_dtls_tbl(res_ctr).resource_seq_num;
3847 l_wip_cost_txn_interface_rec.transaction_quantity := p_res_txn_dtls_tbl(res_ctr).transaction_quantity;
3848
3849 If l_wip_cost_txn_interface_rec.transaction_quantity = 0 then
3850 FND_MESSAGE.SET_NAME('CSD','CSD_TRX_QTY_ZERO');
3851 FND_MSG_PUB.ADD;
3852 RAISE FND_API.G_EXC_ERROR;
3853 end if;
3854
3855 l_wip_cost_txn_interface_rec.transaction_uom := p_res_txn_dtls_tbl(res_ctr).transaction_uom;
3856 l_wip_cost_txn_interface_rec.wip_entity_name := p_res_txn_dtls_tbl(res_ctr).wip_entity_name;
3857 l_wip_cost_txn_interface_rec.wip_entity_id := p_res_txn_dtls_tbl(res_ctr).wip_entity_id;
3858
3859 -- l_wip_cost_txn_interface_rec.employee_id := p_res_txn_dtls_tbl(res_ctr).employee_id;
3860
3861 l_wip_cost_txn_interface_rec.employee_id := p_res_txn_dtls_tbl(res_ctr).employee_id;
3862 l_wip_cost_txn_interface_rec.employee_num := p_res_txn_dtls_tbl(res_ctr).employee_num;
3863
3864 --insert into table wip_cost_txn_interface
3865
3866 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3867 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3868 lc_mod_name||'beforecallinsertwipcosttxn',
3869 'Just before calling insert_wip_cost_txn');
3870 END IF;
3871
3872 -- get conversion rate based on UOM
3873 conversion_rate :=
3874 inv_convert.inv_um_convert(
3875 item_id => 0,
3876 precision => 38,
3877 from_quantity => 1,
3878 from_unit => p_res_txn_dtls_tbl(res_ctr).uom_code,
3879 to_unit => l_wip_cost_txn_interface_rec.transaction_uom ,
3880 from_name => NULL,
3881 to_name => NULL);
3882
3883
3884
3885 -- perform UOM conversion
3886 primary_qty := p_res_txn_dtls_tbl(res_ctr).transaction_quantity / conversion_rate;
3887 -- to_number(name_in(RESOURCE_TRANSACTIONS.APP_TRANSACTION_QUANTITY)) /
3888 -- res_transactions_uom.conversion_rate;
3889
3890
3891
3892 insert_wip_cost_txn( p_wip_cost_txn_interface_rec => l_wip_cost_txn_interface_rec,
3893 x_return_status => x_return_status );
3894
3895
3896 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3898 END IF;
3899
3900
3901
3902
3903
3904
3905
3906 -- Here, check if required_quantity for the material is
3907 -- equal to (issued_quantity + transaction_quantity) ,
3908 -- Only if it is different, need to populate and call wip
3909 -- update program
3910
3911 l_required_quantity :=
3912 nvl(p_res_txn_dtls_tbl(res_ctr).applied_quantity, 0)+
3913 -- nvl(p_res_txn_dtls_tbl(res_ctr).required_quantity, 0)+
3914 primary_qty;
3915 -- p_res_txn_dtls_tbl(res_ctr).transaction_quantity +
3916 -- nvl(p_res_txn_dtls_tbl(res_ctr).pending_quantity, 0);
3917
3918 -- l_job_details_rec.required_quantity := 60;
3919
3920 If l_required_quantity <> p_res_txn_dtls_tbl(res_ctr).required_quantity THEN
3921 l_wip_update_needed := 'T' ;
3922
3923 --Commenting out as don't think this is needed
3924 -- Need to remove ro_quantity as well, later on if not neede
3925 -- l_job_details_rec.quantity_per_assembly := l_required_quantity / p_ro_quantity;
3926
3927 -- Get the Group_id to be used for WIP Mass Load,
3928
3929 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.header_id FROM dual;
3930
3931 -- l_job_header_rec.group_id := l_job_header_rec.header_id;
3932
3933 -- l_job_details_rec.group_id := l_job_header_rec.group_id;
3934 l_job_details_rec.parent_header_id := l_job_header_rec.header_id;
3935
3936 l_job_header_rec.wip_entity_id := p_res_txn_dtls_tbl(res_ctr).wip_entity_id;
3937 l_job_header_rec.organization_id := p_res_txn_dtls_tbl(res_ctr).organization_id;
3938
3939 l_job_details_rec.resource_id_old := p_res_txn_dtls_tbl(res_ctr).resource_id;
3940 l_job_details_rec.resource_id_new := p_res_txn_dtls_tbl(res_ctr).resource_id;
3941
3942 l_job_details_rec.resource_seq_num := p_res_txn_dtls_tbl(res_ctr).resource_seq_num;
3943 l_job_details_rec.operation_seq_num := p_res_txn_dtls_tbl(res_ctr).operation_seq_num;
3944 l_job_details_rec.organization_id := p_res_txn_dtls_tbl(res_ctr).organization_id;
3945 -- l_job_details_rec.uom_code := p_res_txn_dtls_tbl(res_ctr).uom_code;
3946 If p_res_txn_dtls_tbl(res_ctr).basis_type = 1 then
3947 l_job_details_rec.usage_rate_or_amount := l_required_quantity / p_res_txn_dtls_tbl(res_ctr).op_scheduled_quantity ;
3948 else
3949 l_job_details_rec.usage_rate_or_amount := l_required_quantity;
3950 END IF;
3951 -- l_job_details_rec.usage_rate_or_amount := l_required_quantity / p_res_txn_dtls_tbl(res_ctr).job_quantity;
3952 l_job_details_rec.wip_entity_id := p_res_txn_dtls_tbl(res_ctr).wip_entity_id;
3953
3954
3955
3956 -- Call procedures to insert job header and job details information
3957 -- into wip interface tables
3958
3959 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3960 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3961 lc_mod_name||'beforecallinsertjob',
3962 'Just before calling insert_job_header');
3963 END IF;
3964
3965
3966 insert_job_header( p_job_header_rec => l_job_header_rec,
3967 x_return_status => x_return_status );
3968
3969
3970 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3972 END IF;
3973
3974
3975 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3976 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3977 lc_mod_name||'beforecallinsertjobdtls',
3978 'Just before calling insert_job_details');
3979 END IF;
3980
3981
3982 insert_job_details( p_job_details_rec => l_job_details_rec,
3983 x_return_status => x_return_status );
3984
3985 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3987 END IF;
3988
3989 END IF;
3990
3991
3992 ---- Update CSD_WIP_TRANSACTION_DETAILS rows to have null values
3993
3994 If p_res_txn_dtls_tbl(res_ctr).WIP_TRANSACTION_DETAIL_ID is not null then
3995
3996 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3997 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
3998 lc_mod_name||'beforecallupdaterow',
3999 'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row');
4000 END IF;
4001
4002
4003
4004 /* CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row(
4005 p_WIP_TRANSACTION_DETAIL_ID => p_res_txn_dtls_tbl(res_ctr).WIP_TRANSACTION_DETAIL_ID
4006 ,p_CREATED_BY => null
4007 ,p_CREATION_DATE => null
4008 ,p_LAST_UPDATED_BY => l_last_updated_by
4009 ,p_LAST_UPDATE_DATE => l_last_update_date
4010 ,p_LAST_UPDATE_LOGIN => l_last_update_login
4011 ,p_INVENTORY_ITEM_ID => null
4012 ,p_WIP_ENTITY_ID => null
4013 ,p_OPERATION_SEQ_NUM => null
4014 ,p_RESOURCE_SEQ_NUM => null
4015 ,p_INSTANCE_ID => null
4016 ,p_TRANSACTION_QUANTITY => FND_API.G_MISS_NUM
4017 ,p_TRANSACTION_UOM => FND_API.G_MISS_CHAR
4018 ,p_SERIAL_NUMBER => FND_API.G_MISS_CHAR
4019 ,p_OBJECT_VERSION_NUMBER => p_res_txn_dtls_tbl(res_ctr).object_version_number
4020 ); */
4021
4022
4023 CSD_WIP_TRANSACTION_DTLS_PKG.Delete_Row(
4024 p_WIP_TRANSACTION_DETAIL_ID => p_res_txn_dtls_tbl(res_ctr).WIP_TRANSACTION_DETAIL_ID );
4025
4026 end if;
4027
4028
4029 END LOOP;
4030
4031
4032 -- Call WIP Mass Load API
4033 -- Uncomment Later
4034
4035 IF l_wip_update_needed = 'T' THEN
4036
4037 BEGIN
4038
4039 WIP_MASSLOAD_PUB.massLoadJobs(p_groupID => l_job_header_rec.group_id,
4040 p_validationLevel => p_validation_level,
4041 p_commitFlag => 1, -- make it 0 later, once WIP works
4042 x_returnStatus => x_return_status,
4043 x_errorMsg => x_msg_data );
4044
4045 If ( ml_error_exists( l_job_header_rec.group_id ) or
4046 x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
4047
4048 FND_MESSAGE.SET_NAME('CSD','CSD_RES_TXN_MASS_LD_FAILURE');
4049 FND_MSG_PUB.ADD;
4050 x_return_status := FND_API.G_RET_STS_ERROR;
4051
4052 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4053 p_count => x_msg_count,
4054 p_data => x_msg_data);
4055
4056 RETURN;
4057 -- Need to rollback Raise exception -
4058 -- once commit is removed from above call
4059
4060 end if;
4061
4062 EXCEPTION
4063 WHEN OTHERS THEN
4064
4065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4066
4067 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4068
4069 -- Add Unexpected Error to Message List, here SQLERRM is used for
4070 -- getting the error
4071
4072 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
4073 p_procedure_name => lc_api_name );
4074 END IF;
4075
4076 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4077 p_count => x_msg_count,
4078 p_data => x_msg_data);
4079
4080 END;
4081
4082
4083 /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4084 -- ROLLBACK to PROCESS_TRANSACT_RES_TXN_PVT ;
4085
4086 FND_MESSAGE.SET_NAME('CSD','CSD_RES_TXN_FAILURE');
4087 FND_MSG_PUB.ADD;
4088 RETURN; -- later - once wip works - can remove this
4089 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4090 */
4091
4092
4093 /* IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4094 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
4095 lc_mod_name||'exc_exception',
4096 'G_EXC_ERROR Exception');
4097 END IF;
4098
4099 RETURN; */
4100
4101 -- END IF;
4102
4103 END IF;
4104
4105
4106 -- Standard check for p_commit
4107 IF FND_API.to_Boolean( p_commit )
4108 THEN
4109 COMMIT WORK;
4110 END IF;
4111
4112 EXCEPTION
4113 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4114 ROLLBACK to PROCESS_TRANSACT_RES_TXN_PVT ;
4115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4116
4117 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4118 p_count => x_msg_count,
4119 p_data => x_msg_data);
4120
4121 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4122 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
4123 lc_mod_name||'unx_exception',
4124 'G_EXC_UNEXPECTED_ERROR Exception');
4125 END IF;
4126
4127
4128 WHEN FND_API.G_EXC_ERROR THEN
4129 ROLLBACK to PROCESS_TRANSACT_RES_TXN_PVT ;
4130 x_return_status := FND_API.G_RET_STS_ERROR;
4131
4132
4133 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4134 p_count => x_msg_count,
4135 p_data => x_msg_data);
4136
4137 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4138 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
4139 lc_mod_name||'exc_exception',
4140 'G_EXC_ERROR Exception');
4141 END IF;
4142
4143 WHEN OTHERS THEN
4144 ROLLBACK to PROCESS_TRANSACT_RES_TXN_PVT ;
4145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4146
4147 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4148
4149 -- Add Unexpected Error to Message List, here SQLERRM is used for
4150 -- getting the error
4151
4152 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
4153 p_procedure_name => lc_api_name );
4154 END IF;
4155
4156 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4157 p_count => x_msg_count,
4158 p_data => x_msg_data);
4159
4160 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4161 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
4162 lc_mod_name||'others_exception',
4163 'OTHERS Exception');
4164 END IF;
4165
4166
4167
4168 END process_transact_res_txn;
4169
4170
4171
4172 PROCEDURE PROCESS_SAVE_MTL_TXN_DTLS
4173 (
4174 p_api_version_number IN NUMBER,
4175 p_init_msg_list IN VARCHAR2 ,
4176 p_commit IN VARCHAR2 ,
4177 p_validation_level IN NUMBER ,
4178 x_return_status OUT NOCOPY VARCHAR2,
4179 x_msg_count OUT NOCOPY NUMBER,
4180 x_msg_data OUT NOCOPY VARCHAR2,
4181 p_mtl_txn_dtls_tbl IN MTL_TXN_DTLS_TBL_TYPE,
4182 x_op_created OUT NOCOPY VARCHAR
4183 -- p_ro_quantity IN NUMBER
4184 )
4185 IS
4186 lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_SAVE_CSD_MTL_TXN_DTLS';
4187 lc_api_version_number CONSTANT NUMBER := 1.0;
4188
4189 -- constants used for FND_LOG debug messages
4190 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.process_save_mtl_txn_dtls';
4191
4192 -- Constants Used for Inserting into wip_job_schedule_interface,
4193 -- These are the values needed for WIP Mass Load to pick up the records
4194
4195 -- Non Standard Update Discrete Job Load Type
4196 lc_non_std_update_load_type CONSTANT NUMBER := 3;
4197
4198
4199 lc_load_mtl_type CONSTANT NUMBER := 2;
4200
4201 lc_substitution_add_type CONSTANT NUMBER := 2;
4202 -- 11/7/05
4203 -- lc_mrp_net_flag CONSTANT NUMBER := 1;
4204 -- 11/7/05
4205 -- lc_push_wip_supply_type CONSTANT NUMBER := 1;
4206
4207
4208 -- Job Records to hold the Job header and details information
4209 l_job_header_rec wip_job_schedule_interface%ROWTYPE;
4210 l_job_details_rec wip_job_dtls_interface%ROWTYPE;
4211
4212
4213 l_creation_date DATE;
4214 l_last_update_date DATE;
4215 l_created_by NUMBER;
4216 l_created_by_name VARCHAr2(100);
4217 l_last_updated_by NUMBER;
4218 l_last_updated_by_name VARCHAR2(100);
4219 l_last_update_login NUMBER;
4220
4221
4222 l_mtl_load_type NUMBER;
4223 l_mrp_net_flag NUMBER;
4224 -- l_quantity_issued NUMBER;
4225
4226 l_WIP_TRANSACTION_DETAIL_ID NUMBER;
4227
4228 l_job_quantity NUMBER;
4229
4230 l_op_seq_num NUMBER;
4231 l_op_exists VARCHAR2(10);
4232 l_op_dtls_tbl OP_DTLS_TBL_TYPE;
4233
4234 CURSOR get_job_quantity ( p_wip_entity_id NUMBER ) IS
4235 SELECT start_quantity from wip_discrete_jobs where
4236 wip_entity_id = p_wip_entity_id ;
4237
4238 CURSOR get_operation_exists(p_wip_entity_id NUMBER ) IS
4239 SELECT 'exists'
4240 from wip_operations
4241 where wip_entity_id = p_wip_entity_id
4242 and rownum = 1;
4243
4244 BEGIN
4245
4246 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4247 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
4248 lc_mod_name||'begin',
4249 'Entering private API process_save_mtl_txn_dtls' );
4250 END IF;
4251
4252 -- Standard Start of API savepoint
4253 SAVEPOINT PROCESS_SAVE_MTL_TXN_DTLS_PVT;
4254 -- Standard call to check for call compatibility.
4255 IF NOT FND_API.Compatible_API_Call
4256 (lc_api_version_number,
4257 p_api_version_number,
4258 lc_api_name,
4259 G_PKG_NAME)
4260 THEN
4261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4262 END IF;
4263
4264 -- Initialize message list if p_init_msg_list is set to TRUE
4265 IF FND_API.to_boolean(p_init_msg_list) THEN
4266 FND_MSG_PUB.initialize;
4267 END IF;
4268
4269
4270 x_return_status := FND_API.G_RET_STS_SUCCESS;
4271 x_op_created := FND_API.G_FALSE;
4272
4273 -- Populate the constant values
4274
4275 l_job_header_rec.load_type := lc_non_std_update_load_type;
4276
4277 l_job_details_rec.date_required := sysdate;
4278 l_job_details_rec.load_type := lc_load_mtl_type;
4279
4280 l_job_details_rec.substitution_type := lc_substitution_add_type;
4281
4282 -- 11/7/05
4283 -- l_job_details_rec.mrp_net_flag := lc_mrp_net_flag;
4284 l_job_details_rec.mrp_net_flag := null;
4285 -- 11/7/05
4286 -- l_job_details_rec.wip_supply_type := lc_push_wip_supply_type;
4287 l_job_details_rec.wip_supply_type := null;
4288
4289
4290 -- l_quantity_issued := 0;
4291
4292
4293 -- Populate the row who columns
4294
4295 l_creation_date := SYSDATE;
4296 l_last_update_date := SYSDATE;
4297 l_created_by := fnd_global.user_id;
4298 l_last_updated_by := fnd_global.user_id;
4299 l_last_update_login := fnd_global.login_id;
4300
4301
4302
4303
4304 FOR mtl_ctr in p_mtl_txn_dtls_tbl.FIRST.. p_mtl_txn_dtls_tbl.LAST
4305
4306 LOOP
4307 -- use l_op_seq_num throughout this loop, since op seq num may
4308 -- change if value 1 was originally passed in.
4309 l_op_seq_num := p_mtl_txn_dtls_tbl(mtl_ctr).operation_seq_num;
4310
4311 If p_mtl_txn_dtls_tbl(mtl_ctr).new_row = 'Y' then
4312 -- swai: check if there is an existing operation to add the material to.
4313 -- Otherwise, we will have to create an operation first.
4314 if (l_op_seq_num = 1) then
4315 if (p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id is not null) then
4316 open get_operation_exists(l_job_details_rec.wip_entity_id);
4317 fetch get_operation_exists into l_op_exists;
4318 close get_operation_exists;
4319 end if;
4320 if (l_op_exists is null) then
4321 -- create operation, but only if default department is specified
4322 l_op_dtls_tbl(1).department_id := fnd_profile.value('CSD_DEF_HV_OP_DEPT');
4323 if (l_op_dtls_tbl(1).department_id is null) then
4324 -- No operations exist for the job and no default
4325 -- department has been specified, so throw an error.
4326 -- we cannot add the material to operation 1 since
4327 -- it would force a wip_supply_type of pull, which
4328 -- required backflush.
4329 FND_MESSAGE.SET_NAME('CSD','CSD_DEF_OP_DEPT_NULL');
4330 FND_MSG_PUB.ADD;
4331 RAISE FND_API.G_EXC_ERROR;
4332 else
4333 l_op_dtls_tbl(1).new_row := 'Y';
4334 l_op_dtls_tbl(1).wip_entity_id := p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id;
4335 l_op_dtls_tbl(1).organization_id := p_mtl_txn_dtls_tbl(mtl_ctr).organization_id;
4336 l_op_dtls_tbl(1).operation_seq_num := 10; -- default operation seq 10
4337 l_op_dtls_tbl(1).backflush_flag := 2; -- default backflush
4338 l_op_dtls_tbl(1).count_point_type := 1; -- default count point
4339 l_op_dtls_tbl(1).first_unit_completion_date := sysdate;
4340 l_op_dtls_tbl(1).first_unit_start_date := sysdate;
4341 l_op_dtls_tbl(1).last_unit_completion_date := sysdate;
4342 l_op_dtls_tbl(1).last_unit_start_date := sysdate;
4343 l_op_dtls_tbl(1).minimum_transfer_quantity := 0;
4344
4345 PROCESS_SAVE_OP_DTLS
4346 (
4347 p_api_version_number => 1.0,
4348 p_init_msg_list => fnd_api.g_false,
4349 p_Commit => fnd_api.g_false,
4350 p_validation_level => p_validation_level,
4351 x_return_status => x_return_status,
4352 x_msg_count => x_msg_count,
4353 x_msg_data => x_msg_data,
4354 p_op_dtls_tbl => l_op_dtls_tbl
4355 );
4356 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
4357 l_op_seq_num := l_op_dtls_tbl(1).operation_seq_num;
4358 x_op_created := FND_API.G_TRUE;
4359 else
4360 FND_MESSAGE.SET_NAME('CSD','CSD_OP_AUTO_CREATE_FAILURE');
4361 FND_MSG_PUB.ADD;
4362 RAISE FND_API.G_EXC_ERROR;
4363 end if;
4364 end if; -- department profile
4365 end if; -- operation does not exist
4366 end if;
4367
4368
4369 -- Get the Group_id to be used for WIP Mass Load,
4370 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
4371
4372
4373 -- get job_quantity
4374 open get_job_quantity ( p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id);
4375 fetch get_job_quantity into l_job_quantity;
4376 close get_job_quantity;
4377
4378
4379 l_job_header_rec.header_id := l_job_header_rec.group_id;
4380 l_job_header_rec.wip_entity_id := p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id;
4381 l_job_header_rec.organization_id := p_mtl_txn_dtls_tbl(mtl_ctr).organization_id;
4382
4383 l_job_details_rec.group_id := l_job_header_rec.group_id;
4384 l_job_details_rec.parent_header_id := l_job_header_rec.group_id;
4385 l_job_details_rec.inventory_item_id_new := p_mtl_txn_dtls_tbl(mtl_ctr).inventory_item_id;
4386 l_job_details_rec.operation_seq_num := l_op_seq_num; -- p_mtl_txn_dtls_tbl(mtl_ctr).operation_seq_num;
4387 l_job_details_rec.organization_id := p_mtl_txn_dtls_tbl(mtl_ctr).organization_id;
4388 l_job_details_rec.quantity_per_assembly := p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity / l_job_quantity;
4389 l_job_details_rec.required_quantity := p_mtl_txn_dtls_tbl(mtl_ctr).transaction_quantity;
4390 l_job_details_rec.wip_entity_id := p_mtl_txn_dtls_tbl(mtl_ctr).wip_entity_id;
4391 l_job_details_rec.supply_subinventory := p_mtl_txn_dtls_tbl(mtl_ctr).supply_subinventory;
4392
4393 If l_job_details_rec.supply_subinventory is null then
4394 FND_MESSAGE.SET_NAME('CSD','CSD_DEF_SUB_INV_NULL');
4395 FND_MSG_PUB.ADD;
4396 RAISE FND_API.G_EXC_ERROR;
4397 end if;
4398
4399
4400
4401 -- Call procedures to insert job header and job details information
4402 -- into wip interface tables
4403
4404 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4405 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
4406 lc_mod_name||'beforecallinsertjob',
4407 'Just before calling insert_job_header');
4408 END IF;
4409
4410
4411 insert_job_header( p_job_header_rec => l_job_header_rec,
4412 x_return_status => x_return_status );
4413
4414
4415 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4417 END IF;
4418
4419
4420 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4421 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
4422 lc_mod_name||'beforecallinsertjobdtls',
4423 'Just before calling insert_job_details');
4424 END IF;
4425
4426
4427 insert_job_details( p_job_details_rec => l_job_details_rec,
4428 x_return_status => x_return_status );
4429
4430 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4432 END IF;
4433
4434
4435
4436 -- Call WIP Mass Load API
4437
4438 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4439 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
4440 lc_mod_name||'beforecallwipmassload',
4441 'Just before calling WIP_MASSLOAD_PUB.massLoadJobs');
4442 END IF;
4443
4444 BEGIN
4445 WIP_MASSLOAD_PUB.massLoadJobs(p_groupID => l_job_header_rec.group_id,
4446 p_validationLevel => p_validation_level,
4447 p_commitFlag => 0,
4448 x_returnStatus => x_return_status,
4449 x_errorMsg => x_msg_data );
4450
4451 If ( ml_error_exists( l_job_header_rec.group_id ) or
4452 x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
4453
4454 FND_MESSAGE.SET_NAME('CSD','CSD_MTL_ADD_MASS_LD_FAILURE');
4455 FND_MSG_PUB.ADD;
4456 x_return_status := FND_API.G_RET_STS_ERROR;
4457
4458 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4459 p_count => x_msg_count,
4460 p_data => x_msg_data);
4461 -- Need to rollback Raise exception -
4462 -- once commit is removed from above call
4463 -- raise FND_API.G_EXC_ERROR;
4464 RETURN;
4465 end if;
4466
4467 EXCEPTION
4468 WHEN OTHERS THEN
4469 add_wip_interface_errors(l_job_header_rec.group_id,
4470 2 /* 2 = materials */);
4471
4472 -- when rollback for WIP works, remove x_return_status, count_and_get,
4473 -- and return then reinstate raise exception above
4474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4475 /*
4476 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4477 -- Add Unexpected Error to Message List, here SQLERRM is used for
4478 -- getting the error
4479
4480 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
4481 p_procedure_name => lc_api_name );
4482 END IF;
4483 */
4484 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4485 p_count => x_msg_count,
4486 p_data => x_msg_data);
4487
4488 END;
4489 end if;
4490
4491 If p_mtl_txn_dtls_tbl(mtl_ctr).WIP_TRANSACTION_DETAIL_ID is null then
4492 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4493 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
4494 lc_mod_name||'beforecallinsertrow',
4495 'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row');
4496 END IF;
4497
4498 l_WIP_TRANSACTION_DETAIL_ID := null;
4499
4500 CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row(
4501 px_WIP_TRANSACTION_DETAIL_ID => l_WIP_TRANSACTION_DETAIL_ID
4502 ,p_CREATED_BY => l_created_by
4503 ,p_CREATION_DATE => l_creation_date
4504 ,p_LAST_UPDATED_BY => l_last_updated_by
4505 ,p_LAST_UPDATE_DATE => l_last_update_date
4506 ,p_LAST_UPDATE_LOGIN => l_last_update_login
4507 ,p_INVENTORY_ITEM_ID => p_mtl_txn_dtls_tbl(mtl_ctr).INVENTORY_ITEM_ID
4508 ,p_WIP_ENTITY_ID => p_mtl_txn_dtls_tbl(mtl_ctr).WIP_ENTITY_ID
4509 ,p_OPERATION_SEQ_NUM => l_op_seq_num -- p_mtl_txn_dtls_tbl(mtl_ctr).OPERATION_SEQ_NUM
4510 ,p_RESOURCE_SEQ_NUM => null
4511 ,p_employee_id => null
4512 ,p_TRANSACTION_QUANTITY => p_mtl_txn_dtls_tbl(mtl_ctr).TRANSACTION_QUANTITY
4513 ,p_TRANSACTION_UOM => p_mtl_txn_dtls_tbl(mtl_ctr).TRANSACTION_UOM
4514 ,p_SERIAL_NUMBER => p_mtl_txn_dtls_tbl(mtl_ctr).SERIAL_NUMBER
4515 ,p_REVISION => p_mtl_txn_dtls_tbl(mtl_ctr).REVISION -- swai: bug 6995498/7182047
4516 ,p_REASON_ID => p_mtl_txn_dtls_tbl(mtl_ctr).REASON_ID -- swai bug 6841113
4517 ,p_BACKFLUSH_FLAG => null
4518 ,p_COUNT_POINT_TYPE => null
4519 ,p_DEPARTMENT_ID => null
4520 ,p_DESCRIPTION => null
4521 ,p_FIRST_UNIT_COMPLETION_DATE => null
4522 ,p_FIRST_UNIT_START_DATE => null
4523 ,p_LAST_UNIT_COMPLETION_DATE => null
4524 ,p_LAST_UNIT_START_DATE => null
4525 ,p_MINIMUM_TRANSFER_QUANTITY => null
4526 ,p_STANDARD_OPERATION_ID => null
4527 );
4528
4529 else
4530
4531 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4532 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
4533 lc_mod_name||'beforecallupdaterow',
4534 'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row');
4535 END IF;
4536
4537 CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row(
4538 p_WIP_TRANSACTION_DETAIL_ID => p_mtl_txn_dtls_tbl(mtl_ctr).WIP_TRANSACTION_DETAIL_ID
4539 ,p_CREATED_BY => null
4540 ,p_CREATION_DATE => null
4541 ,p_LAST_UPDATED_BY => l_last_updated_by
4542 ,p_LAST_UPDATE_DATE => l_last_update_date
4543 ,p_LAST_UPDATE_LOGIN => l_last_update_login
4544 ,p_INVENTORY_ITEM_ID => null
4545 ,p_WIP_ENTITY_ID => null
4546 ,p_OPERATION_SEQ_NUM => null
4547 ,p_RESOURCE_SEQ_NUM => null
4548 ,p_employee_id => null
4549 ,p_TRANSACTION_QUANTITY => p_mtl_txn_dtls_tbl(mtl_ctr).TRANSACTION_QUANTITY
4550 ,p_TRANSACTION_UOM => p_mtl_txn_dtls_tbl(mtl_ctr).TRANSACTION_UOM
4551 ,p_SERIAL_NUMBER => p_mtl_txn_dtls_tbl(mtl_ctr).SERIAL_NUMBER
4552 ,p_REVISION => p_mtl_txn_dtls_tbl(mtl_ctr).REVISION -- swai: bug 6995498/7182047
4553 ,p_REASON_ID => p_mtl_txn_dtls_tbl(mtl_ctr).REASON_ID -- swai bug 6841113
4554 ,p_BACKFLUSH_FLAG => null
4555 ,p_COUNT_POINT_TYPE => null
4556 ,p_DEPARTMENT_ID => null
4557 ,p_DESCRIPTION => null
4558 ,p_FIRST_UNIT_COMPLETION_DATE => null
4559 ,p_FIRST_UNIT_START_DATE => null
4560 ,p_LAST_UNIT_COMPLETION_DATE => null
4561 ,p_LAST_UNIT_START_DATE => null
4562 ,p_MINIMUM_TRANSFER_QUANTITY => null
4563 ,p_STANDARD_OPERATION_ID => null
4564 ,p_OBJECT_VERSION_NUMBER => p_mtl_txn_dtls_tbl(mtl_ctr).object_version_number
4565 );
4566
4567 end if;
4568
4569 END LOOP;
4570
4571 -- Standard check for p_commit
4572 IF FND_API.to_Boolean( p_commit )
4573 THEN
4574 COMMIT WORK;
4575 END IF;
4576
4577 EXCEPTION
4578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4579 ROLLBACK to PROCESS_SAVE_MTL_TXN_DTLS_PVT ;
4580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4581
4582 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4583 p_count => x_msg_count,
4584 p_data => x_msg_data);
4585
4586 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4587 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
4588 lc_mod_name||'unx_exception',
4589 'G_EXC_UNEXPECTED_ERROR Exception');
4590 END IF;
4591
4592
4593 WHEN FND_API.G_EXC_ERROR THEN
4594 ROLLBACK to PROCESS_SAVE_MTL_TXN_DTLS_PVT ;
4595 x_return_status := FND_API.G_RET_STS_ERROR;
4596
4597
4598 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4599 p_count => x_msg_count,
4600 p_data => x_msg_data);
4601
4602 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4603 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
4604 lc_mod_name||'exc_exception',
4605 'G_EXC_ERROR Exception');
4606 END IF;
4607
4608 WHEN OTHERS THEN
4609 ROLLBACK to PROCESS_SAVE_MTL_TXN_DTLS_PVT ;
4610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4611
4612 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4613
4614 -- Add Unexpected Error to Message List, here SQLERRM is used for
4615 -- getting the error
4616
4617 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
4618 p_procedure_name => lc_api_name );
4619 END IF;
4620
4621 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4622 p_count => x_msg_count,
4623 p_data => x_msg_data);
4624
4625 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4626 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
4627 lc_mod_name||'others_exception',
4628 'OTHERS Exception');
4629 END IF;
4630
4631
4632
4633 END PROCESS_SAVE_MTL_TXN_DTLS;
4634
4635
4636
4637 PROCEDURE PROCESS_SAVE_RES_TXN_DTLS
4638 (
4639 p_api_version_number IN NUMBER,
4640 p_init_msg_list IN VARCHAR2,
4641 p_Commit IN VARCHAR2,
4642 p_validation_level IN NUMBER,
4643 x_return_status OUT NOCOPY VARCHAR2,
4644 x_msg_count OUT NOCOPY NUMBER,
4645 x_msg_data OUT NOCOPY VARCHAR2,
4646 p_res_txn_dtls_tbl IN res_TXN_DTLS_TBL_TYPE
4647 -- p_ro_quantity IN NUMBER
4648 )
4649 IS
4650 lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_SAVE_RES_TXN_DTLS';
4651 lc_api_version_number CONSTANT NUMBER := 1.0;
4652
4653 -- constants used for FND_LOG debug messages
4654
4655 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
4656
4657
4658 -- Constants Used for Inserting into wip_job_schedule_interface,
4659 -- These are the values needed for WIP Mass Load to pick up the records
4660
4661 -- Non Standard Update Discrete Job Load Type
4662 lc_non_std_update_load_type CONSTANT NUMBER := 3;
4663
4664
4665 lc_load_res_type CONSTANT NUMBER := 1;
4666 lc_substitution_add_type CONSTANT NUMBER := 2;
4667
4668 -- 11/7/05
4669 -- lc_manual_autocharge_type CONSTANT NUMBER := 2;
4670 -- lc_item_basis_type CONSTANT NUMBER := 1;
4671 -- lc_no_scheduled_flag CONSTANT NUMBER := 2;
4672 -- lc_yes_standard_rate_flag CONSTANT NUMBER := 1;
4673
4674
4675 -- Job Records to hold the Job header and details information
4676
4677 l_job_header_rec wip_job_schedule_interface%ROWTYPE;
4678 l_job_details_rec wip_job_dtls_interface%ROWTYPE;
4679
4680
4681 l_creation_date DATE;
4682 l_last_update_date DATE;
4683 l_created_by NUMBER;
4684 l_created_by_name VARCHAr2(100);
4685 l_last_updated_by NUMBER;
4686 l_last_updated_by_name VARCHAR2(100);
4687 l_last_update_login NUMBER;
4688
4689
4690 l_WIP_TRANSACTION_DETAIL_ID NUMBER;
4691 -- l_validation_level NUMBER;
4692
4693 l_job_quantity NUMBER;
4694
4695 l_resource_seq_num NUMBER;
4696
4697
4698 CURSOR get_job_quantity ( p_wip_entity_id NUMBER ) IS
4699 SELECT start_quantity from wip_discrete_jobs where
4700 wip_entity_id = p_wip_entity_id ;
4701
4702 -- swai: bug 7017062 nvl the max(resource_seq_num)
4703 cursor get_next_resource_seq_num ( p_wip_entity_id NUMBER,
4704 p_operation_seq_num NUMBER ) IS
4705 select nvl(MAX(RESOURCE_SEQ_NUM),0)+ 10 from
4706 wip_operation_resources where wip_entity_id
4707 = p_wip_entity_id and operation_seq_num =
4708 p_operation_seq_num;
4709
4710
4711
4712
4713 BEGIN
4714
4715 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4716 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
4717 lc_mod_name||'begin',
4718 'Entering private API process_save_res_txn_dtls' );
4719 END IF;
4720
4721 -- Standard Start of API savepoint
4722 SAVEPOINT PROCESS_SAVE_RES_TXN_DTLS_PVT;
4723 -- Standard call to check for call compatibility.
4724 IF NOT FND_API.Compatible_API_Call
4725 (lc_api_version_number,
4726 p_api_version_number,
4727 lc_api_name,
4728 G_PKG_NAME)
4729 THEN
4730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4731 END IF;
4732
4733
4734 -- Initialize message list if p_init_msg_list is set to TRUE
4735 IF FND_API.to_boolean(p_init_msg_list) THEN
4736 FND_MSG_PUB.initialize;
4737 END IF;
4738
4739 x_return_status := FND_API.G_RET_STS_SUCCESS;
4740
4741
4742
4743 -- Populate the constant values
4744
4745 l_job_header_rec.load_type := lc_non_std_update_load_type;
4746
4747 l_job_details_rec.start_date := sysdate;
4748 l_job_details_rec.load_type := lc_load_res_type;
4749
4750 l_job_details_rec.substitution_type := lc_substitution_add_type;
4751
4752 -- l_job_details_rec.autocharge_type := lc_manual_autocharge_type;
4753 -- l_job_details_rec.basis_type := lc_item_basis_type;
4754 l_job_details_rec.completion_date := sysdate;
4755 -- l_job_details_rec.scheduled_flag := lc_no_scheduled_flag;
4756 -- l_job_details_rec.standard_rate_flag := lc_yes_standard_rate_flag;
4757
4758
4759
4760 -- Populate the row who columns
4761
4762 l_creation_date := SYSDATE;
4763 l_last_update_date := SYSDATE;
4764 l_created_by := fnd_global.user_id;
4765 l_last_updated_by := fnd_global.user_id;
4766 l_last_update_login := fnd_global.login_id;
4767
4768 FOR res_ctr in p_res_txn_dtls_tbl.FIRST.. p_res_txn_dtls_tbl.LAST
4769
4770 LOOP
4771
4772 l_resource_seq_num := p_res_txn_dtls_tbl(res_ctr).resource_seq_num;
4773
4774
4775 If p_res_txn_dtls_tbl(res_ctr).new_row = 'Y' then
4776
4777 -- Get the Group_id to be used for WIP Mass Load,
4778
4779 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
4780
4781 -- get job_quantity
4782 open get_job_quantity ( p_res_txn_dtls_tbl(res_ctr).wip_entity_id);
4783 fetch get_job_quantity into l_job_quantity;
4784 close get_job_quantity;
4785
4786
4787
4788
4789 l_job_header_rec.header_id := l_job_header_rec.group_id;
4790 l_job_header_rec.wip_entity_id := p_res_txn_dtls_tbl(res_ctr).wip_entity_id;
4791 l_job_header_rec.organization_id := p_res_txn_dtls_tbl(res_ctr).organization_id;
4792
4793 l_job_details_rec.group_id := l_job_header_rec.group_id;
4794 l_job_details_rec.parent_header_id := l_job_header_rec.group_id;
4795 -- l_job_details_rec.resource_id_old := p_res_txn_dtls_tbl(res_ctr).resource_id;
4796
4797 l_job_details_rec.resource_id_new := p_res_txn_dtls_tbl(res_ctr).resource_id;
4798 l_job_details_rec.operation_seq_num := p_res_txn_dtls_tbl(res_ctr).operation_seq_num;
4799
4800
4801 open get_next_resource_seq_num ( p_res_txn_dtls_tbl(res_ctr).wip_entity_id,
4802 p_res_txn_dtls_tbl(res_ctr).operation_seq_num );
4803
4804 fetch get_next_resource_seq_num into l_job_details_rec.resource_seq_num;
4805
4806 close get_next_resource_seq_num;
4807
4808 l_resource_seq_num := l_job_details_rec.resource_seq_num;
4809
4810
4811
4812
4813 l_job_details_rec.organization_id := p_res_txn_dtls_tbl(res_ctr).organization_id;
4814 l_job_details_rec.uom_code := p_res_txn_dtls_tbl(res_ctr).transaction_uom;
4815 l_job_details_rec.usage_rate_or_amount := p_res_txn_dtls_tbl(res_ctr).transaction_quantity / l_job_quantity;
4816 l_job_details_rec.assigned_units := 1; --p_res_txn_dtls_tbl(res_ctr).transaction_quantity;
4817 l_job_details_rec.wip_entity_id := p_res_txn_dtls_tbl(res_ctr).wip_entity_id;
4818
4819
4820 -- Call procedures to insert job header and job details information
4821 -- into wip interface tables
4822
4823 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4824 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
4825 lc_mod_name||'beforecallinsertjob',
4826 'Just before calling insert_job_header');
4827 END IF;
4828
4829
4830 insert_job_header( p_job_header_rec => l_job_header_rec,
4831 x_return_status => x_return_status );
4832
4833
4834 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4836 END IF;
4837
4838
4839 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4840 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
4841 lc_mod_name||'beforecallinsertjobdtls',
4842 'Just before calling insert_job_details');
4843 END IF;
4844
4845
4846 insert_job_details( p_job_details_rec => l_job_details_rec,
4847 x_return_status => x_return_status );
4848
4849 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4851 END IF;
4852
4853
4854
4855 -- Call WIP Mass Load API
4856
4857 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4858 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
4859 lc_mod_name||'beforecallwipmassload',
4860 'Just before calling WIP_MASSLOAD_PUB.massLoadJobs');
4861 END IF;
4862
4863 BEGIN
4864 WIP_MASSLOAD_PUB.massLoadJobs(p_groupID => l_job_header_rec.group_id,
4865 p_validationLevel => p_validation_level,
4866 p_commitFlag => 0,
4867 x_returnStatus => x_return_status,
4868 x_errorMsg => x_msg_data );
4869
4870 If ( ml_error_exists( l_job_header_rec.group_id ) or
4871 x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
4872
4873 FND_MESSAGE.SET_NAME('CSD','CSD_RES_ADD_MASS_LD_FAILURE');
4874 FND_MSG_PUB.ADD;
4875 x_return_status := FND_API.G_RET_STS_ERROR;
4876
4877 FND_MSG_PUB.count_and_get(p_encoded => FND_API.G_FALSE,
4878 p_count => x_msg_count,
4879 p_data => x_msg_data);
4880
4881 -- Need to rollback Raise exception -
4882 -- once commit is removed from above call
4883 -- raise FND_API.G_EXC_ERROR;
4884 RETURN;
4885 end if;
4886
4887 EXCEPTION
4888 WHEN OTHERS THEN
4889 add_wip_interface_errors(l_job_header_rec.group_id,
4890 3 /* 3 = resources */);
4891 -- raise FND_API.G_EXC_UNEXPECTED_ERROR;
4892
4893 -- when rollback for WIP works, remove x_return_status, count_and_get,
4894 -- and return then reinstate raise exception above
4895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4896 /*
4897 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4898 -- Add Unexpected Error to Message List, here SQLERRM is used for
4899 -- getting the error
4900 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
4901 p_procedure_name => lc_api_name );
4902 END IF;
4903 */
4904 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4905 p_count => x_msg_count,
4906 p_data => x_msg_data);
4907 return;
4908 END;
4909 end if;
4910
4911 If p_res_txn_dtls_tbl(res_ctr).WIP_TRANSACTION_DETAIL_ID is null then
4912
4913 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4914 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
4915 lc_mod_name||'beforecallinsertrow',
4916 'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row');
4917 END IF;
4918
4919 l_WIP_TRANSACTION_DETAIL_ID := null;
4920
4921 CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row(
4922 px_WIP_TRANSACTION_DETAIL_ID => l_WIP_TRANSACTION_DETAIL_ID
4923 ,p_CREATED_BY => l_created_by
4924 ,p_CREATION_DATE => l_creation_date
4925 ,p_LAST_UPDATED_BY => l_last_updated_by
4926 ,p_LAST_UPDATE_DATE => l_last_update_date
4927 ,p_LAST_UPDATE_LOGIN => l_last_update_login
4928 ,p_INVENTORY_ITEM_ID => null
4929 ,p_WIP_ENTITY_ID => p_res_txn_dtls_tbl(res_ctr).WIP_ENTITY_ID
4930 ,p_OPERATION_SEQ_NUM => p_res_txn_dtls_tbl(res_ctr).OPERATION_SEQ_NUM
4931 ,p_RESOURCE_SEQ_NUM => l_RESOURCE_SEQ_NUM
4932 ,p_employee_id => p_res_txn_dtls_tbl(res_ctr).employee_id
4933 ,p_TRANSACTION_QUANTITY => p_res_txn_dtls_tbl(res_ctr).TRANSACTION_QUANTITY
4934 ,p_TRANSACTION_UOM => p_res_txn_dtls_tbl(res_ctr).TRANSACTION_UOM
4935 ,p_SERIAL_NUMBER => NULL
4936 ,p_REVISION => NULL -- swai: bug 6995498/7182047
4937 ,p_REASON_ID => null -- swai bug 6841113
4938 ,p_BACKFLUSH_FLAG => null
4939 ,p_COUNT_POINT_TYPE => null
4940 ,p_DEPARTMENT_ID => null
4941 ,p_DESCRIPTION => null
4942 ,p_FIRST_UNIT_COMPLETION_DATE => null
4943 ,p_FIRST_UNIT_START_DATE => null
4944 ,p_LAST_UNIT_COMPLETION_DATE => null
4945 ,p_LAST_UNIT_START_DATE => null
4946 ,p_MINIMUM_TRANSFER_QUANTITY => null
4947 ,p_STANDARD_OPERATION_ID => null
4948 );
4949 else
4950
4951 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4952 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
4953 lc_mod_name||'beforecallupdaterow',
4954 'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row');
4955 END IF;
4956
4957
4958 CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row(
4959 p_WIP_TRANSACTION_DETAIL_ID => p_res_txn_dtls_tbl(res_ctr).WIP_TRANSACTION_DETAIL_ID
4960 ,p_CREATED_BY => null
4961 ,p_CREATION_DATE => null
4962 ,p_LAST_UPDATED_BY => l_last_updated_by
4963 ,p_LAST_UPDATE_DATE => l_last_update_date
4964 ,p_LAST_UPDATE_LOGIN => l_last_update_login
4965 ,p_INVENTORY_ITEM_ID => null
4966 ,p_WIP_ENTITY_ID => null
4967 ,p_OPERATION_SEQ_NUM => null
4968 ,p_RESOURCE_SEQ_NUM => null
4969 ,p_employee_id => p_res_txn_dtls_tbl(res_ctr).employee_id
4970 ,p_TRANSACTION_QUANTITY => p_res_txn_dtls_tbl(res_ctr).TRANSACTION_QUANTITY
4971 ,p_TRANSACTION_UOM => p_res_txn_dtls_tbl(res_ctr).TRANSACTION_UOM
4972 ,p_SERIAL_NUMBER => null
4973 ,p_REVISION => null -- swai: bug 6995498/7182047
4974 ,p_REASON_ID => null -- swai bug 6841113
4975 ,p_BACKFLUSH_FLAG => null
4976 ,p_COUNT_POINT_TYPE => null
4977 ,p_DEPARTMENT_ID => null
4978 ,p_DESCRIPTION => null
4979 ,p_FIRST_UNIT_COMPLETION_DATE => null
4980 ,p_FIRST_UNIT_START_DATE => null
4981 ,p_LAST_UNIT_COMPLETION_DATE => null
4982 ,p_LAST_UNIT_START_DATE => null
4983 ,p_MINIMUM_TRANSFER_QUANTITY => null
4984 ,p_STANDARD_OPERATION_ID => null
4985 ,p_OBJECT_VERSION_NUMBER => p_res_txn_dtls_tbl(res_ctr).object_version_number
4986 );
4987
4988 end if;
4989
4990 END LOOP;
4991
4992 -- Standard check for p_commit
4993 IF FND_API.to_Boolean( p_commit )
4994 THEN
4995 COMMIT WORK;
4996 END IF;
4997
4998 EXCEPTION
4999 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5000 ROLLBACK to PROCESS_SAVE_RES_TXN_DTLS_PVT ;
5001 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5002
5003 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5004 p_count => x_msg_count,
5005 p_data => x_msg_data);
5006
5007 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5008 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
5009 lc_mod_name||'unx_exception',
5010 'G_EXC_UNEXPECTED_ERROR Exception');
5011 END IF;
5012
5013
5014 WHEN FND_API.G_EXC_ERROR THEN
5015 ROLLBACK to PROCESS_SAVE_RES_TXN_DTLS_PVT ;
5016 x_return_status := FND_API.G_RET_STS_ERROR;
5017
5018
5019 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5020 p_count => x_msg_count,
5021 p_data => x_msg_data);
5022
5023 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5024 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
5025 lc_mod_name||'exc_exception',
5026 'G_EXC_ERROR Exception');
5027 END IF;
5028
5029 WHEN OTHERS THEN
5030 ROLLBACK to PROCESS_SAVE_RES_TXN_DTLS_PVT ;
5031 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5032
5033 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5034
5035 -- Add Unexpected Error to Message List, here SQLERRM is used for
5036 -- getting the error
5037
5038 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
5039 p_procedure_name => lc_api_name );
5040 END IF;
5041
5042 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5043 p_count => x_msg_count,
5044 p_data => x_msg_data);
5045
5046 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5047 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
5048 lc_mod_name||'others_exception',
5049 'OTHERS Exception');
5050 END IF;
5051
5052
5053 END PROCESS_SAVE_RES_TXN_DTLS;
5054
5055
5056 PROCEDURE PROCESS_SAVE_OP_DTLS
5057 (
5058 p_api_version_number IN NUMBER,
5059 p_init_msg_list IN VARCHAR2,
5060 p_Commit IN VARCHAR2,
5061 p_validation_level IN NUMBER,
5062 x_return_status OUT NOCOPY VARCHAR2,
5063 x_msg_count OUT NOCOPY NUMBER,
5064 x_msg_data OUT NOCOPY VARCHAR2,
5065 p_op_dtls_tbl IN OP_DTLS_TBL_TYPE
5066 )
5067 IS
5068 lc_api_name CONSTANT VARCHAR2(30) := 'PROCESS_SAVE_OP_DTLS';
5069 lc_api_version_number CONSTANT NUMBER := 1.0;
5070
5071 -- constants used for FND_LOG debug messages
5072 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.process_save_op_dtls.';
5073
5074 -- Constants Used for Inserting into wip_job_schedule_interface,
5075 -- These are the values needed for WIP Mass Load to pick up the records
5076 lc_non_std_update_load_type CONSTANT NUMBER := 3; -- load type for update non standard discrete job
5077
5078 -- Constants Used for Inserting into wip_job_dtls_interface
5079 lc_load_op_type CONSTANT NUMBER := 3; -- load type for operations
5080 lc_substitution_add_type CONSTANT NUMBER := 2; -- indicates add record (vs. change=3 or delete=1)
5081 lc_substitution_change_type CONSTANT NUMBER := 3; -- indicates change record (vs. add=2 or delete=1)
5082 lc_process_validation_phase CONSTANT NUMBER := 2; -- must be 2 for WIP to pick up record
5083 lc_process_pending_status CONSTANT NUMBER := 1; -- must be 1 for WIP to pick up record
5084
5085 -- Job Records to hold the Job header and details information
5086 l_job_header_rec wip_job_schedule_interface%ROWTYPE;
5087 l_job_details_rec wip_job_dtls_interface%ROWTYPE;
5088
5089 -- variables for WHO columns
5090 l_creation_date DATE;
5091 l_last_update_date DATE;
5092 l_created_by NUMBER;
5093 l_last_updated_by NUMBER;
5094 l_last_update_login NUMBER;
5095
5096 -- primary key for CSD_WIP_TRANSACTION_DETAILS table
5097 l_wip_transaction_detail_id NUMBER;
5098
5099 BEGIN
5100
5101 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5102 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
5103 lc_mod_name||'begin',
5104 'Entering private API process_save_op_dtls' );
5105 END IF;
5106
5107 -- Standard Start of API savepoint
5108 SAVEPOINT PROCESS_SAVE_OP_DTLS_PVT;
5109
5110 -- Standard call to check for call compatibility.
5111 IF NOT FND_API.Compatible_API_Call
5112 (lc_api_version_number,
5113 p_api_version_number,
5114 lc_api_name,
5115 G_PKG_NAME)
5116 THEN
5117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5118 END IF;
5119
5120
5121 -- Initialize message list if p_init_msg_list is set to TRUE
5122 IF FND_API.to_boolean(p_init_msg_list) THEN
5123 FND_MSG_PUB.initialize;
5124 END IF;
5125
5126 x_return_status := FND_API.G_RET_STS_SUCCESS;
5127
5128 -- Populate the constant values for job header
5129 l_job_header_rec.load_type := lc_non_std_update_load_type;
5130
5131 -- Populate the constant values for job details
5132 l_job_details_rec.start_date := sysdate;
5133 l_job_details_rec.load_type := lc_load_op_type;
5134
5135 -- Get the data for the WHO columns
5136 l_creation_date := SYSDATE;
5137 l_last_update_date := SYSDATE;
5138 l_created_by := fnd_global.user_id;
5139 l_last_updated_by := fnd_global.user_id;
5140 l_last_update_login := fnd_global.login_id;
5141
5142 FOR op_ctr in p_op_dtls_tbl.FIRST.. p_op_dtls_tbl.LAST
5143 LOOP
5144 -- l_operation_seq_num := p_op_dtls_tbl(op_ctr).operation_seq_num;
5145
5146 IF p_op_dtls_tbl(op_ctr).operation_seq_num is not null THEN
5147 -- Get the Group_id to be used for WIP Mass Load,
5148
5149 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
5150
5151 -- set job header info
5152 l_job_header_rec.header_id := l_job_header_rec.group_id;
5153 l_job_header_rec.wip_entity_id := p_op_dtls_tbl(op_ctr).wip_entity_id;
5154 l_job_header_rec.organization_id := p_op_dtls_tbl(op_ctr).organization_id;
5155
5156 -- set job details (operations) info - required columns in wip_job_dtls_interface table
5157 l_job_details_rec.group_id := l_job_header_rec.group_id;
5158 l_job_details_rec.parent_header_id := l_job_header_rec.group_id;
5159 l_job_details_rec.operation_seq_num := p_op_dtls_tbl(op_ctr).operation_seq_num;
5160 l_job_details_rec.backflush_flag := p_op_dtls_tbl(op_ctr).backflush_flag;
5161 l_job_details_rec.count_point_type := p_op_dtls_tbl(op_ctr).count_point_type;
5162 l_job_details_rec.first_unit_completion_date := p_op_dtls_tbl(op_ctr).first_unit_completion_date;
5163 l_job_details_rec.first_unit_start_date := p_op_dtls_tbl(op_ctr).first_unit_start_date;
5164 l_job_details_rec.last_unit_completion_date := p_op_dtls_tbl(op_ctr).last_unit_completion_date;
5165 l_job_details_rec.last_unit_start_date := p_op_dtls_tbl(op_ctr).last_unit_start_date;
5166 l_job_details_rec.minimum_transfer_quantity := p_op_dtls_tbl(op_ctr).minimum_transfer_quantity;
5167 l_job_details_rec.process_phase := lc_process_validation_phase;
5168 l_job_details_rec.process_status := lc_process_pending_status;
5169
5170 -- set job details (operations) info - optional columns in wip_job_dtls_interface table
5171 l_job_details_rec.description := p_op_dtls_tbl(op_ctr).description;
5172
5173 -- set job details (operations) info - not set in columns in wip_job_dtls_interface table
5174 l_job_details_rec.organization_id := p_op_dtls_tbl(op_ctr).organization_id;
5175 l_job_details_rec.wip_entity_id := p_op_dtls_tbl(op_ctr).wip_entity_id;
5176
5177 IF p_op_dtls_tbl(op_ctr).new_row = 'Y' THEN
5178 l_job_details_rec.substitution_type := lc_substitution_add_type;
5179 l_job_details_rec.department_id := p_op_dtls_tbl(op_ctr).department_id;
5180 l_job_details_rec.standard_operation_id := p_op_dtls_tbl(op_ctr).standard_operation_id;
5181 ELSE
5182 l_job_details_rec.substitution_type := lc_substitution_change_type;
5183 END IF;
5184
5185 -- Call procedures to insert job header and job details information
5186 -- into wip interface tables
5187 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5188 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
5189 lc_mod_name||'beforecallinsertjob',
5190 'Just before calling insert_job_header');
5191 END IF;
5192
5193 insert_job_header( p_job_header_rec => l_job_header_rec,
5194 x_return_status => x_return_status );
5195
5196 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5198 END IF;
5199
5200 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5201 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
5202 lc_mod_name||'beforecallinsertjobdtls',
5203 'Just before calling insert_job_details');
5204 END IF;
5205
5206 insert_job_details( p_job_details_rec => l_job_details_rec,
5207 x_return_status => x_return_status );
5208
5209 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5211 END IF;
5212
5213 -- Call WIP Mass Load API
5214 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5215 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
5216 lc_mod_name||'beforecallwipmassload',
5217 'Just before calling WIP_MASSLOAD_PUB.massLoadJobs');
5218 END IF;
5219
5220 BEGIN
5221 WIP_MASSLOAD_PUB.massLoadJobs(p_groupID => l_job_header_rec.group_id,
5222 p_validationLevel => p_validation_level,
5223 p_commitFlag => 0,
5224 x_returnStatus => x_return_status,
5225 x_errorMsg => x_msg_data );
5226
5227 If ( ml_error_exists( l_job_header_rec.group_id ) or
5228 x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
5229
5230 FND_MESSAGE.SET_NAME('CSD','CSD_OP_ADD_MASS_LD_FAILURE');
5231 FND_MSG_PUB.ADD;
5232 x_return_status := FND_API.G_RET_STS_ERROR;
5233
5234 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5235 p_count => x_msg_count,
5236 p_data => x_msg_data);
5237
5238 -- Need to rollback Raise exception -
5239 -- once commit is removed from above call
5240 -- raise FND_API.G_EXC_ERROR;
5241 RETURN;
5242 end if;
5243 EXCEPTION
5244 WHEN OTHERS THEN
5245 add_wip_interface_errors(l_job_header_rec.group_id,
5246 1 /* 1 = operations */);
5247 -- raise FND_API.G_EXC_UNEXPECTED_ERROR;
5248
5249 -- when rollback for WIP works, remove x_return_status, count_and_get,
5250 -- and return then reinstate raise exception above
5251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5252 /*
5253 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5254
5255 -- Add Unexpected Error to Message List, here SQLERRM is used for
5256 -- getting the error
5257
5258 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
5259 p_procedure_name => lc_api_name );
5260 END IF;
5261 */
5262 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5263 p_count => x_msg_count,
5264 p_data => x_msg_data);
5265 return;
5266 END;
5267 end if;
5268
5269 If p_op_dtls_tbl(op_ctr).WIP_TRANSACTION_DETAIL_ID is null then
5270 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5271 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
5272 lc_mod_name||'beforecallinsertrow',
5273 'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row');
5274 END IF;
5275
5276 l_WIP_TRANSACTION_DETAIL_ID := null;
5277
5278 CSD_WIP_TRANSACTION_DTLS_PKG.Insert_Row(
5279 px_WIP_TRANSACTION_DETAIL_ID => l_WIP_TRANSACTION_DETAIL_ID
5280 ,p_CREATED_BY => l_created_by
5281 ,p_CREATION_DATE => l_creation_date
5282 ,p_LAST_UPDATED_BY => l_last_updated_by
5283 ,p_LAST_UPDATE_DATE => l_last_update_date
5284 ,p_LAST_UPDATE_LOGIN => l_last_update_login
5285 ,p_INVENTORY_ITEM_ID => null
5286 ,p_WIP_ENTITY_ID => p_op_dtls_tbl(op_ctr).WIP_ENTITY_ID
5287 ,p_OPERATION_SEQ_NUM => p_op_dtls_tbl(op_ctr).OPERATION_SEQ_NUM
5288 ,p_RESOURCE_SEQ_NUM => null
5289 ,p_employee_id => null
5290 ,p_TRANSACTION_QUANTITY => null
5291 ,p_TRANSACTION_UOM => null
5292 ,p_SERIAL_NUMBER => NULL
5293 ,p_REVISION => NULL -- swai: bug 6995498/7182047
5294 ,p_REASON_ID => null -- swai bug 6841113
5295 ,p_BACKFLUSH_FLAG => p_op_dtls_tbl(op_ctr).BACKFLUSH_FLAG
5296 ,p_COUNT_POINT_TYPE => p_op_dtls_tbl(op_ctr).COUNT_POINT_TYPE
5297 ,p_DEPARTMENT_ID => p_op_dtls_tbl(op_ctr).DEPARTMENT_ID
5298 ,p_DESCRIPTION => p_op_dtls_tbl(op_ctr).DESCRIPTION
5299 ,p_FIRST_UNIT_COMPLETION_DATE => p_op_dtls_tbl(op_ctr).FIRST_UNIT_COMPLETION_DATE
5300 ,p_FIRST_UNIT_START_DATE => p_op_dtls_tbl(op_ctr).FIRST_UNIT_START_DATE
5301 ,p_LAST_UNIT_COMPLETION_DATE => p_op_dtls_tbl(op_ctr).LAST_UNIT_COMPLETION_DATE
5302 ,p_LAST_UNIT_START_DATE => p_op_dtls_tbl(op_ctr).LAST_UNIT_START_DATE
5303 ,p_MINIMUM_TRANSFER_QUANTITY => p_op_dtls_tbl(op_ctr).MINIMUM_TRANSFER_QUANTITY
5304 ,p_STANDARD_OPERATION_ID => p_op_dtls_tbl(op_ctr).STANDARD_OPERATION_ID
5305
5306 );
5307
5308 else
5309 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5310 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
5311 lc_mod_name||'beforecallupdaterow',
5312 'Just before calling CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row');
5313 END IF;
5314
5315 CSD_WIP_TRANSACTION_DTLS_PKG.Update_Row(
5316 p_WIP_TRANSACTION_DETAIL_ID => p_op_dtls_tbl(op_ctr).WIP_TRANSACTION_DETAIL_ID
5317 ,p_CREATED_BY => null
5318 ,p_CREATION_DATE => null
5319 ,p_LAST_UPDATED_BY => l_last_updated_by
5320 ,p_LAST_UPDATE_DATE => l_last_update_date
5321 ,p_LAST_UPDATE_LOGIN => l_last_update_login
5322 ,p_INVENTORY_ITEM_ID => null
5323 ,p_WIP_ENTITY_ID => null
5324 ,p_OPERATION_SEQ_NUM => null
5325 ,p_RESOURCE_SEQ_NUM => null
5326 ,p_employee_id => null
5327 ,p_TRANSACTION_QUANTITY => null
5328 ,p_TRANSACTION_UOM => null
5329 ,p_SERIAL_NUMBER => null
5330 ,p_REVISION => null -- swai: bug 6995498/7182047
5331 ,p_REASON_ID => null -- swai bug 6841113
5332 ,p_BACKFLUSH_FLAG => p_op_dtls_tbl(op_ctr).BACKFLUSH_FLAG
5333 ,p_COUNT_POINT_TYPE => p_op_dtls_tbl(op_ctr).COUNT_POINT_TYPE
5334 ,p_DEPARTMENT_ID => p_op_dtls_tbl(op_ctr).DEPARTMENT_ID
5335 ,p_DESCRIPTION => p_op_dtls_tbl(op_ctr).DESCRIPTION
5336 ,p_FIRST_UNIT_COMPLETION_DATE => p_op_dtls_tbl(op_ctr).FIRST_UNIT_COMPLETION_DATE
5337 ,p_FIRST_UNIT_START_DATE => p_op_dtls_tbl(op_ctr).FIRST_UNIT_START_DATE
5338 ,p_LAST_UNIT_COMPLETION_DATE => p_op_dtls_tbl(op_ctr).LAST_UNIT_COMPLETION_DATE
5339 ,p_LAST_UNIT_START_DATE => p_op_dtls_tbl(op_ctr).LAST_UNIT_START_DATE
5340 ,p_MINIMUM_TRANSFER_QUANTITY => p_op_dtls_tbl(op_ctr).MINIMUM_TRANSFER_QUANTITY
5341 ,p_STANDARD_OPERATION_ID => p_op_dtls_tbl(op_ctr).STANDARD_OPERATION_ID
5342 ,p_OBJECT_VERSION_NUMBER => p_op_dtls_tbl(op_ctr).object_version_number
5343 );
5344
5345 end if;
5346 END LOOP;
5347
5348 -- Standard check for p_commit
5349 IF FND_API.to_Boolean( p_commit )
5350 THEN
5351 COMMIT WORK;
5352 END IF;
5353
5354 EXCEPTION
5355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5356 ROLLBACK to PROCESS_SAVE_OP_DTLS_PVT ;
5357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5358
5359 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5360 p_count => x_msg_count,
5361 p_data => x_msg_data);
5362
5363 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5364 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
5365 lc_mod_name||'unx_exception',
5366 'G_EXC_UNEXPECTED_ERROR Exception');
5367 END IF;
5368
5369
5370 WHEN FND_API.G_EXC_ERROR THEN
5371 ROLLBACK to PROCESS_SAVE_OP_DTLS_PVT ;
5372 x_return_status := FND_API.G_RET_STS_ERROR;
5373
5374
5375 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5376 p_count => x_msg_count,
5377 p_data => x_msg_data);
5378
5379 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5380 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
5381 lc_mod_name||'exc_exception',
5382 'G_EXC_ERROR Exception');
5383 END IF;
5384
5385 WHEN OTHERS THEN
5386 ROLLBACK to PROCESS_SAVE_OP_DTLS_PVT ;
5387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5388
5389 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5390
5391 -- Add Unexpected Error to Message List, here SQLERRM is used for
5392 -- getting the error
5393
5394 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
5395 p_procedure_name => lc_api_name );
5396 END IF;
5397
5398 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5399 p_count => x_msg_count,
5400 p_data => x_msg_data);
5401
5402 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5403 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
5404 lc_mod_name||'others_exception',
5405 'OTHERS Exception');
5406 END IF;
5407 END PROCESS_SAVE_OP_DTLS;
5408
5409
5410 PROCEDURE create_wip_job
5411 (
5412 p_api_version_number IN NUMBER,
5413 p_init_msg_list IN VARCHAR2 ,
5414 p_commit IN VARCHAR2 ,
5415 p_validation_level IN NUMBER,
5416 x_return_status OUT NOCOPY VARCHAR2,
5417 x_msg_count OUT NOCOPY NUMBER,
5418 x_msg_data OUT NOCOPY VARCHAR2,
5419 x_job_name OUT NOCOPY VARCHAR2,
5420 p_repair_line_id IN NUMBER,
5421 p_repair_quantity IN NUMBER,
5422 p_inventory_item_Id IN NUMBER
5423 )
5424 IS
5425
5426 -- Job Record to hold the Job header, bills and routing information being inserted
5427 -- into wip_job_schedule_interface
5428
5429 l_job_header_rec wip_job_schedule_interface%ROWTYPE;
5430
5431 lc_api_name CONSTANT VARCHAR2(30) := 'CREATE_WIP_JOB';
5432 lc_api_version_number CONSTANT NUMBER := 1.0;
5433
5434 -- WIP Job Status Lookup Codes for Released and Unreleased status, --- The Lookup Type is WIP_JOB_STATUS
5435
5436 lc_released_status_code CONSTANT NUMBER := 3;
5437 lc_unreleased_status_code CONSTANT NUMBER := 1;
5438
5439 -- Non Standard Discrete Job Load Type
5440 lc_non_standard_load_type CONSTANT NUMBER := 4;
5441
5442
5443 -- COnstants used for FND_LOG debug messages
5444
5445 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.create_wip_job';
5446
5447
5448
5449 l_user_id NUMBER;
5450 l_repair_xref_id NUMBER;
5451 l_rep_hist_id NUMBER;
5452
5453 l_job_prefix VARCHAR2(80);
5454 l_wip_entity_id NUMBER;
5455
5456
5457 --*****Below are the code to Default Repair Item as Material on Job**********
5458 l_default_ro_item VARCHAR2(1);
5459 -- l_wip_entity_id NUMBER;
5460 l_mtl_txn_dtls_tbl CSD_HV_WIP_JOB_PVT.MTL_TXN_DTLS_TBL_TYPE;
5461 l_op_created VARCHAR2(10);
5462 l_num_other_jobs NUMBER :=0; -- swai: bug 7477845/7483291
5463
5464
5465 CURSOR c_repair_line_info(p_repair_line_id IN NUMBER) IS
5466 select inventory_item_id, unit_of_measure, quantity, serial_number, inventory_org_id
5467 from csd_repairs
5468 where repair_line_id = p_repair_line_id;
5469
5470 CURSOR c_count_material(p_wip_entity_id NUMBER, l_inventory_item_id NUMBER) IS
5471 select 'X'
5472 from wip_requirement_operations_v
5473 where wip_entity_id = p_wip_entity_id
5474 and inventory_item_id = l_inventory_item_id
5475 and rownum = 1;
5476
5477
5478 -- Cursor to select the item attributes serial control code and
5479 -- lot control code.
5480 CURSOR cur_get_item_attribs (
5481 p_org_id NUMBER,
5482 p_item_id NUMBER
5483 )
5484 IS
5485 SELECT serial_number_control_code
5486 FROM mtl_system_items
5487 WHERE organization_id = p_org_id AND inventory_item_id = p_item_id;
5488
5489
5490 Cursor c_get_serial_info(p_item_id number, p_serial_number varchar2, p_org_id number) is
5491 select current_status, current_subinventory_code from mtl_serial_numbers
5492 where inventory_item_id = p_item_id and serial_number = p_serial_number and current_organization_id = p_org_id;
5493
5494
5495 -- Cursor c_get_min_operation_seq(p_wip_entity_id number) is
5496 -- select min(operation_seq_num) from wip_operations_v where wip_entity_id = p_wip_entity_id;
5497
5498 l_inventory_item_id NUMBER;
5499 l_unit_of_measure VARCHAR2(3);
5500 l_quantity NUMBER;
5501 l_serial_number VARCHAR2(30);
5502 l_inventory_org_id NUMBER;
5503 l_subinventory VARCHAR2(30);
5504 l_dummy VARCHAR2(1) := null;
5505 l_serial_control_code NUMBER;
5506 l_current_status NUMBER;
5507 l_current_subinventory_code VARCHAR2(10);
5508 l_operation_seq_num NUMBER;
5509
5510
5511
5512 BEGIN
5513
5514 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
5515 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
5516 lc_mod_name||'begin',
5517 'Entering Private API create_wip_job');
5518 END IF;
5519
5520 -- Standard Start of API savepoint
5521 SAVEPOINT CREATE_WIP_JOB_PVT;
5522 -- Standard call to check for call compatibility.
5523 IF NOT FND_API.Compatible_API_Call
5524 (lc_api_version_number,
5525 p_api_version_number,
5526 lc_api_name,
5527 G_PKG_NAME)
5528 THEN
5529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5530 END IF;
5531
5532 -- Initialize message list if p_init_msg_list is set to TRUE
5533 IF FND_API.to_boolean(p_init_msg_list) THEN
5534 FND_MSG_PUB.initialize;
5535 END IF;
5536
5537 -- Initialize API return status to success
5538 x_return_status:=FND_API.G_RET_STS_SUCCESS;
5539
5540
5541 l_job_header_rec.organization_id :=
5542 fnd_profile.value('CSD_DEF_REP_INV_ORG');
5543
5544 -- l_job_header_rec.organization_id := 207;
5545
5546 IF l_job_header_rec.organization_id is NULL THEN
5547
5548 FND_MESSAGE.SET_NAME('CSD','CSD_DEF_REP_INV_NULL');
5549 FND_MSG_PUB.ADD;
5550 RAISE FND_API.G_EXC_ERROR;
5551 end if;
5552
5553
5554 l_job_prefix := fnd_profile.value('CSD_DEFAULT_JOB_PREFIX');
5555
5556 -- If l_job_prefix is null, throw an error and return;
5557
5558 -- l_job_prefix := 'SR';
5559
5560 IF l_job_prefix is NULL THEN
5561
5562 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_PREFIX_NULL');
5563 FND_MSG_PUB.ADD;
5564 RAISE FND_API.G_EXC_ERROR;
5565 end if;
5566
5567
5568
5569 l_job_header_rec.class_code :=
5570 fnd_profile.value('CSD_DEF_WIP_ACCOUNTING_CLASS');
5571
5572 -- l_job_header_rec.class_code := 'Rework';
5573
5574 IF l_job_header_rec.class_code is NULL THEN
5575
5576 FND_MESSAGE.SET_NAME('CSD','CSD_CLASS_CODE_NULL');
5577 FND_MSG_PUB.ADD;
5578 RAISE FND_API.G_EXC_ERROR;
5579 end if;
5580
5581
5582
5583 -- Assign the WIP Job Status lookup codes corresponding to Released -- and Unreleased Job status,
5584 -- to be passed for WIP Interface Table
5585
5586
5587 if fnd_profile.value('CSD_DEFAULT_JOB_STATUS') = 'RELEASED' then
5588
5589
5590 l_job_header_rec.status_type := lc_released_status_code ;
5591
5592 elsif nvl( fnd_profile.value('CSD_DEFAULT_JOB_STATUS'), 'UNRELEASED' ) = 'UNRELEASED' then
5593
5594 l_job_header_rec.status_type := lc_unreleased_status_code;
5595 end if;
5596
5597
5598 l_job_header_rec.load_type := lc_non_standard_load_type;
5599
5600
5601
5602 l_job_header_rec.first_unit_start_date := sysdate;
5603 l_job_header_rec.last_unit_completion_date := sysdate;
5604
5605
5606 l_job_header_rec.start_quantity := p_repair_quantity;
5607
5608 -- If the profile CSD: Default WIP MRP Net Qty to Zero is set to
5609 -- null / 'N' then net_quantity = start_quantity else if the
5610 -- profile is set to 'Y' then net_quantity = 0
5611 IF ( nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N') = 'N' ) THEN
5612 l_job_header_rec.net_quantity := p_repair_quantity;
5613 ELSIF ( fnd_profile.value('CSD_WIP_MRP_NET_QTY') = 'Y' ) THEN
5614 l_job_header_rec.net_quantity := 0;
5615 END IF;
5616
5617
5618 l_job_header_rec.primary_item_id :=
5619 p_inventory_item_id ;
5620
5621
5622 -- Get the Group_id to be used for WIP Create Job,
5623
5624 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
5625
5626 l_job_header_rec.interface_id := l_job_header_rec.group_id;
5627
5628 generate_job_name ( p_job_prefix =>l_job_prefix,
5629 p_organization_id => l_job_header_rec.organization_id,
5630 x_job_name => l_job_header_rec.job_name );
5631
5632
5633 x_job_name := l_job_header_rec.job_name;
5634
5635
5636 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5637 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
5638 lc_mod_name||'beforecallinsert',
5639 'Just before calling insert_job_header');
5640 END IF;
5641
5642 -- Call procedure to insert job header and bills, routing
5643 -- information
5644 -- into wip_job_schedule_interface table
5645
5646
5647 insert_job_header( p_job_header_rec =>
5648 l_job_header_rec,
5649 x_return_status =>
5650 x_return_status );
5651
5652
5653 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5655 END IF;
5656
5657
5658 -- CALL WIP API to process records in wip interface table,
5659 --If API fails, Raise error, rollback and return
5660
5661 -- Call WIP Mass Load API
5662
5663 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5664 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
5665 lc_mod_name||'beforecallcreateonejob',
5666 'Just before calling WIP_MASSLOAD_PUB.createOneJob');
5667 END IF;
5668 -- dbms_output.put_line('Before calling createonejob');
5669
5670 WIP_MASSLOAD_PUB.createOneJob( p_interfaceID => l_job_header_rec.group_id,
5671 p_validationLevel => p_validation_level,
5672 x_wipEntityID => l_wip_entity_id,
5673 x_returnStatus => x_return_status,
5674 x_errorMsg => x_msg_data );
5675
5676 -- dbms_output.put_line('After calling createonejob');
5677
5678
5679
5680 If ( ml_error_exists( l_job_header_rec.group_id ) or
5681 x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
5682
5683 -- ROLLBACK to CREATE_WIP_JOB_PVT ;
5684
5685
5686 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_CREAT_FAILURE');
5687 FND_MSG_PUB.ADD;
5688 RAISE FND_API.G_EXC_ERROR;
5689
5690 -- Need to rollback Raise exception -
5691 -- once commit is removed from above call
5692
5693 end if;
5694
5695
5696 --*****Below are the code to Default Repair Item as Material on Job**********
5697
5698 l_default_ro_item := nvl(FND_PROFILE.VALUE('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'), 'N');
5699 --taklam
5700 if (l_default_ro_item = 'Y') then
5701 -- swai: bug 7477845/7483291
5702 -- check if there another job existing for this RO. If so, do not default
5703 -- the RO item as a material. Must compare we.wip_entity_id since
5704 -- crj.wip_entity_id may be null (until wip_update is done).
5705 select count(*)
5706 into l_num_other_jobs
5707 from csd_repair_job_xref crj,
5708 wip_entities we
5709 where crj.job_name = we.wip_entity_name
5710 and crj.organization_id = we.organization_id
5711 and crj.repair_line_id = p_repair_line_id
5712 and we.wip_entity_id <> l_wip_entity_id;
5713
5714 if (l_num_other_jobs = 0) then
5715 OPEN c_repair_line_info(p_repair_line_id);
5716 FETCH c_repair_line_info into
5717 l_inventory_item_id,
5718 l_unit_of_measure,
5719 l_quantity,
5720 l_serial_number,
5721 l_inventory_org_id;
5722 CLOSE c_repair_line_info;
5723 l_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
5724
5725 --Get serial number control code and lot control code
5726 OPEN cur_get_item_attribs (l_inventory_org_id,
5727 l_inventory_item_id);
5728
5729 FETCH cur_get_item_attribs
5730 INTO l_serial_control_code;
5731 CLOSE cur_get_item_attribs;
5732
5733
5734 IF l_serial_control_code IN (2, 5) then
5735 OPEN c_get_serial_info (l_inventory_item_id, l_serial_number, l_inventory_org_id);
5736 FETCH c_get_serial_info
5737 INTO l_current_status,l_current_subinventory_code;
5738 CLOSE c_get_serial_info;
5739 --current status = 3 is valid serial number
5740 if (l_current_status = 3) then
5741 l_subinventory := l_current_subinventory_code;
5742 else
5743 l_serial_number := null;
5744 end if;
5745 else
5746 --don't pass the serial number, it is not valid serial number
5747 l_serial_number := null;
5748 end if;
5749
5750
5751 l_dummy := null;
5752 OPEN c_count_material(l_wip_entity_id, l_inventory_item_id);
5753 FETCH c_count_material into l_dummy;
5754 CLOSE c_count_material;
5755
5756
5757 if (l_dummy is null) then
5758 --Default Repair Item as Material on Job
5759 l_mtl_txn_dtls_tbl.delete;
5760
5761 l_mtl_txn_dtls_tbl(0).INVENTORY_ITEM_ID :=l_inventory_item_id;
5762 l_mtl_txn_dtls_tbl(0).WIP_ENTITY_ID :=l_wip_entity_id;
5763 l_mtl_txn_dtls_tbl(0).ORGANIZATION_ID :=l_inventory_org_id;
5764 l_mtl_txn_dtls_tbl(0).OPERATION_SEQ_NUM :=1;
5765 l_mtl_txn_dtls_tbl(0).TRANSACTION_QUANTITY :=l_quantity; --repair order qty
5766 l_mtl_txn_dtls_tbl(0).TRANSACTION_UOM :=l_unit_of_measure; --Repair order UOM
5767 l_mtl_txn_dtls_tbl(0).SERIAL_NUMBER :=l_serial_number;
5768 l_mtl_txn_dtls_tbl(0).SUPPLY_SUBINVENTORY :=l_subinventory;
5769 l_mtl_txn_dtls_tbl(0).OBJECT_VERSION_NUMBER := 1;
5770 l_mtl_txn_dtls_tbl(0).NEW_ROW := 'Y';
5771
5772
5773 -- call API to create Repair Actuals header
5774 CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_MTL_TXN_DTLS
5775 ( p_api_version_number => 1.0,
5776 p_init_msg_list => 'T',
5777 p_commit => 'F',
5778 p_validation_level => 1,
5779 p_mtl_txn_dtls_tbl => l_mtl_txn_dtls_tbl,
5780 x_op_created => l_op_created,
5781 x_return_status => x_return_status,
5782 x_msg_count => x_msg_count,
5783 x_msg_data => x_msg_data);
5784 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
5785 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
5786 FND_MSG_PUB.ADD;
5787 RAISE FND_API.G_EXC_ERROR;
5788 END IF;
5789 end if;
5790 end if; -- swai: bug 7477845/7483291 num other jobs = 0
5791 end if;
5792 --*****End of the code to Default Repair Item as Material on Job**********
5793
5794
5795
5796 /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5797 ROLLBACK to CREATE_WIP_JOB_PVT ;
5798
5799
5800 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_CREAT_FAILURE');
5801 FND_MSG_PUB.ADD;
5802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
5803
5804
5805 -- dbms_output.put_line('In Error');
5806
5807 /* IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5808 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
5809 lc_mod_name||'exc_exception',
5810 'G_EXC_ERROR Exception');
5811 END IF;
5812
5813 RETURN; */
5814
5815 -- END IF;
5816
5817
5818 -- call procedures to insert a row in csd_repair_job_xref
5819 -- and csd_repair_history tables for the job created.
5820
5821 L_user_id := fnd_global.user_id;
5822
5823 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5824 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
5825 lc_mod_name||'beforecallxrefwrite',
5826 'Just before calling csd_to_form_repair_job_xref.validate_and_write');
5827 END IF;
5828
5829
5830
5831 csd_to_form_repair_job_xref.validate_and_write(
5832 p_api_version_number => lc_api_version_number,
5833 p_init_msg_list => FND_API.G_FALSE,
5834 p_commit => FND_API.G_FALSE,
5835 p_validation_level => NULL,
5836 p_action_code => 0,
5837 px_repair_job_xref_id => l_repair_xref_id,
5838 p_created_by => l_user_id,
5839 p_creation_date => SYSDATE,
5840 p_last_updated_by => l_user_id,
5841 p_last_update_date => SYSDATE,
5842 p_last_update_login => l_user_id,
5843 p_repair_line_id => p_repair_line_id,
5844 p_wip_entity_id => l_wip_entity_id,
5845 p_group_id => l_job_header_rec.group_id,
5846 p_organization_id => l_job_header_rec.organization_id,
5847 p_quantity => p_repair_quantity,
5848 p_INVENTORY_ITEM_ID => l_job_header_rec.primary_item_id,
5849 p_ITEM_REVISION => null,
5850 p_OBJECT_VERSION_NUMBER => NULL,
5851 p_attribute_category => NULL,
5852 p_attribute1 => NULL,
5853 p_attribute2 => NULL,
5854 p_attribute3 => NULL,
5855 p_attribute4 => NULL,
5856 p_attribute5 => NULL,
5857 p_attribute6 => NULL,
5858 p_attribute7 => NULL,
5859 p_attribute8 => NULL,
5860 p_attribute9 => NULL,
5861 p_attribute10 => NULL,
5862 p_attribute11 => NULL,
5863 p_attribute12 => NULL,
5864 p_attribute13 => NULL,
5865 p_attribute14 => NULL,
5866 p_attribute15 => NULL,
5867 p_quantity_completed => NULL,
5868 p_job_name => l_job_header_rec.job_name,
5869 p_source_type_code => 'MANUAL', -- bug fix 5763350
5870 p_source_id1 => NULL,
5871 p_ro_service_code_id => NULL,
5872 x_return_status => x_return_status,
5873 x_msg_count => x_msg_count,
5874 x_msg_data => x_msg_data);
5875
5876
5877 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5878 ROLLBACK to CREATE_WIP_JOB_PVT ;
5879
5880 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5881 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
5882 lc_mod_name||'exc_exception',
5883 'G_EXC_ERROR Exception');
5884 END IF;
5885
5886 RETURN;
5887
5888 END IF;
5889
5890
5891 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5892 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
5893 lc_mod_name||'beforecallhistwrite',
5894 'Just before calling csd_to_form_repair_history.validate_and_write');
5895 END IF;
5896
5897
5898
5899 csd_to_form_repair_history.validate_and_write(
5900 p_api_version_number => lc_api_version_number,
5901 p_init_msg_list => FND_API.G_FALSE,
5902 p_commit => FND_API.G_FALSE,
5903 p_validation_level => NULL,
5904 p_action_code => 0,
5905 px_repair_history_id => l_rep_hist_id,
5906 p_OBJECT_VERSION_NUMBER => NULL,
5907 p_request_id => NULL,
5908 p_program_id => NULL,
5909 p_program_application_id => NULL,
5910 p_program_update_date => NULL,
5911 p_created_by => l_user_id,
5912 p_creation_date => SYSDATE,
5913 p_last_updated_by => l_user_id,
5914 p_last_update_date => SYSDATE,
5915 p_repair_line_id => p_repair_line_id,
5916 p_event_code => 'JS',
5917 p_event_date => SYSDATE,
5918 p_quantity => p_repair_quantity,
5919 p_paramn1 => l_wip_entity_id,
5920 p_paramn2 => l_job_header_rec.organization_id,
5921 p_paramn3 => NULL,
5922 p_paramn4 => NULL,
5923 p_paramn5 => p_repair_quantity,
5924 p_paramn6 => NULL,
5925 p_paramn8 => NULL,
5926 p_paramn9 => NULL,
5927 p_paramn10 => NULL,
5928 p_paramc1 => l_job_header_rec.job_name,
5929 p_paramc2 => NULL,
5930 p_paramc3 => NULL,
5931 p_paramc4 => NULL,
5932 p_paramc5 => NULL,
5933 p_paramc6 => NULL,
5934 p_paramc7 => NULL,
5935 p_paramc8 => NULL,
5936 p_paramc9 => NULL,
5937 p_paramc10 => NULL,
5938 p_paramd1 => NULL ,
5939 p_paramd2 => NULL ,
5940 p_paramd3 => NULL ,
5941 p_paramd4 => NULL ,
5942 p_paramd5 => SYSDATE,
5943 p_paramd6 => NULL ,
5944 p_paramd7 => NULL ,
5945 p_paramd8 => NULL ,
5946 p_paramd9 => NULL ,
5947 p_paramd10 => NULL ,
5948 p_attribute_category => NULL ,
5949 p_attribute1 => NULL ,
5950 p_attribute2 => NULL ,
5951 p_attribute3 => NULL ,
5952 p_attribute4 => NULL ,
5953 p_attribute5 => NULL ,
5954 p_attribute6 => NULL ,
5955 p_attribute7 => NULL ,
5956 p_attribute8 => NULL ,
5957 p_attribute9 => NULL ,
5958 p_attribute10 => NULL ,
5959 p_attribute11 => NULL ,
5960 p_attribute12 => NULL ,
5961 p_attribute13 => NULL ,
5962 p_attribute14 => NULL ,
5963 p_attribute15 => NULL ,
5964 p_last_update_login => l_user_id,
5965 x_return_status => x_return_status,
5966 x_msg_count => x_msg_count,
5967 x_msg_data => x_msg_data);
5968
5969 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5970 ROLLBACK to CREATE_WIP_JOB_PVT ;
5971
5972 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5973 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
5974 lc_mod_name||'exc_exception',
5975 'G_EXC_ERROR Exception');
5976 END IF;
5977
5978 RETURN;
5979
5980 END IF;
5981
5982 -- Standard check for p_commit
5983 IF FND_API.to_Boolean( p_commit )
5984 THEN
5985 COMMIT WORK;
5986 END IF;
5987
5988
5989 EXCEPTION
5990 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5991 ROLLBACK to CREATE_WIP_JOB_PVT ;
5992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5993
5994 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5995 p_count => x_msg_count,
5996 p_data => x_msg_data);
5997
5998 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
5999 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
6000 lc_mod_name||'unx_exception',
6001 'G_EXC_UNEXPECTED_ERROR Exception');
6002 END IF;
6003
6004
6005 WHEN FND_API.G_EXC_ERROR THEN
6006 ROLLBACK to CREATE_WIP_JOB_PVT ;
6007 x_return_status := FND_API.G_RET_STS_ERROR;
6008
6009
6010 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6011 p_count => x_msg_count,
6012 p_data => x_msg_data);
6013
6014 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6015 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
6016 lc_mod_name||'exc_exception',
6017 'G_EXC_ERROR Exception');
6018 END IF;
6019
6020 WHEN OTHERS THEN
6021 ROLLBACK to CREATE_WIP_JOB_PVT ;
6022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6023
6024 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6025
6026 -- Add Unexpected Error to Message List, here SQLERRM is used for
6027 -- getting the error
6028
6029 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
6030 p_procedure_name => lc_api_name );
6031 END IF;
6032
6033 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6034 p_count => x_msg_count,
6035 p_data => x_msg_data);
6036
6037 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6038 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
6039 lc_mod_name||'others_exception',
6040 'OTHERS Exception');
6041 END IF;
6042
6043
6044 END create_wip_job;
6045
6046
6047 PROCEDURE generate_wip_jobs_from_scs
6048 (
6049 p_api_version_number IN NUMBER,
6050 p_init_msg_list IN VARCHAR2 ,
6051 p_commit IN VARCHAR2 ,
6052 p_validation_level IN NUMBER,
6053 x_return_status OUT NOCOPY VARCHAR2,
6054 x_msg_count OUT NOCOPY NUMBER,
6055 x_msg_data OUT NOCOPY VARCHAR2,
6056 p_repair_line_id IN NUMBER,
6057 p_repair_quantity IN NUMBER,
6058 p_service_code_tbl IN service_code_tbl_type
6059 )
6060 IS
6061 -- Job Record to hold the Job header, bills and routing information being inserted
6062 -- into wip_job_schedule_interface
6063 l_job_header_rec wip_job_schedule_interface%ROWTYPE;
6064
6065 lc_api_name CONSTANT VARCHAR2(30) := 'GENERATE_WIP_JOBS_FROM_SCS';
6066 lc_api_version_number CONSTANT NUMBER := 1.0;
6067
6068 -- WIP Job Status Lookup Codes for Released and Unreleased status,
6069 -- The Lookup Type is WIP_JOB_STATUS
6070 lc_released_status_code CONSTANT NUMBER := 3;
6071 lc_unreleased_status_code CONSTANT NUMBER := 1;
6072
6073 -- Non Standard Discrete Job Load Type
6074 lc_non_standard_load_type CONSTANT NUMBER := 4;
6075
6076 lc_service_code CONSTANT VARCHAR2(30) := 'SERVICE_CODE';
6077
6078
6079 -- Constants used for FND_LOG debug messages
6080 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_hv_wip_job_pvt.generate_wip_jobs_from_scs';
6081
6082 l_object_version_number NUMBER;
6083 l_user_id NUMBER;
6084 l_repair_xref_id NUMBER;
6085 l_rep_hist_id NUMBER;
6086 l_error_msg VARCHAR2(2000);
6087
6088 l_job_prefix VARCHAR2(80);
6089 l_wip_entity_id NUMBER;
6090
6091 -- swai: bug 5239301
6092 l_bills_routes_count NUMBER := 0;
6093 l_show_messages_flag VARCHAR2(1) := 'F';
6094 l_service_code VARCHAR2(30);
6095 -- end swai: bug 5239301
6096
6097 l_ro_service_code_rec CSD_RO_SERVICE_CODES_PVT.RO_SERVICE_CODE_REC_TYPE;
6098
6099 -- Counter used for populating l_x_job_bill_routing_tbl table with
6100 -- Bills and Routing information
6101 -- Also tracks the number of jobs being submitted
6102
6103 CURSOR c_get_bills_routes(c_p_service_code_id number, c_p_organization_id number )
6104 IS
6105 SELECT bom.assembly_item_id bom_reference_id,
6106 bom.alternate_bom_designator,
6107 bor.assembly_item_id routing_reference_id,
6108 bor.alternate_routing_designator,
6109 bor.completion_subinventory,
6110 bor. completion_locator_id
6111 FROM csd_sc_work_entities cscwe,
6112 bom_bill_of_materials bom , bom_operational_routings bor
6113 WHERE cscwe.service_code_id = c_p_service_code_id
6114 And cscwe.work_entity_type_code = 'BOM'
6115 and cscwe.work_entity_id3 = c_p_organization_id
6116 and cscwe.work_entity_id1 = bom.bill_sequence_id (+)
6117 and cscwe.work_entity_id2 = bor.routing_sequence_id (+);
6118
6119 -- swai: bug 5239301
6120 -- Cursor to get the service code details
6121 CURSOR c_get_service_code_details (c_p_service_code_id number)
6122 IS
6123 SELECT service_code
6124 FROM csd_service_codes_b
6125 WHERE service_code_id = c_p_service_code_id;
6126 -- end swai: bug 5239301
6127
6128
6129
6130 --*****Below are the code to Default Repair Item as Material on Job**********
6131 l_default_ro_item VARCHAR2(1);
6132 -- l_wip_entity_id NUMBER;
6133 l_mtl_txn_dtls_tbl CSD_HV_WIP_JOB_PVT.MTL_TXN_DTLS_TBL_TYPE;
6134 l_op_created VARCHAR2(10);
6135 l_num_other_jobs NUMBER :=0; -- swai: bug 7477845/7483291
6136
6137
6138 CURSOR c_repair_line_info(p_repair_line_id IN NUMBER) IS
6139 select inventory_item_id, unit_of_measure, quantity, serial_number, inventory_org_id
6140 from csd_repairs
6141 where repair_line_id = p_repair_line_id;
6142
6143 CURSOR c_count_material(p_wip_entity_id NUMBER, l_inventory_item_id NUMBER) IS
6144 select 'X'
6145 from wip_requirement_operations_v
6146 where wip_entity_id = p_wip_entity_id
6147 and inventory_item_id = l_inventory_item_id
6148 and rownum = 1;
6149
6150
6151 -- Cursor to select the item attributes serial control code and
6152 -- lot control code.
6153 CURSOR cur_get_item_attribs (
6154 p_org_id NUMBER,
6155 p_item_id NUMBER
6156 )
6157 IS
6158 SELECT serial_number_control_code
6159 FROM mtl_system_items
6160 WHERE organization_id = p_org_id AND inventory_item_id = p_item_id;
6161
6162
6163 Cursor c_get_serial_info(p_item_id number, p_serial_number varchar2, p_org_id number) is
6164 select current_status, current_subinventory_code from mtl_serial_numbers
6165 where inventory_item_id = p_item_id and serial_number = p_serial_number and current_organization_id = p_org_id;
6166
6167
6168 Cursor c_get_min_operation_seq(p_wip_entity_id number) is
6169 select min(operation_seq_num) from wip_operations_v where wip_entity_id = p_wip_entity_id;
6170
6171 l_inventory_item_id NUMBER;
6172 l_unit_of_measure VARCHAR2(3);
6173 l_quantity NUMBER;
6174 l_serial_number VARCHAR2(30);
6175 l_inventory_org_id NUMBER;
6176 l_subinventory VARCHAR2(30);
6177 l_dummy VARCHAR2(1) := null;
6178 l_serial_control_code NUMBER;
6179 l_current_status NUMBER;
6180 l_current_subinventory_code VARCHAR2(10);
6181 l_operation_seq_num NUMBER;
6182
6183
6184 BEGIN
6185
6186 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
6187 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
6188 lc_mod_name||'begin',
6189 'Entering Private API generate_wip_jobs_from_scs');
6190 END IF;
6191
6192 -- Standard Start of API savepoint
6193 SAVEPOINT GENERATE_WIP_JOBS_FROM_SCS_PVT;
6194 -- Standard call to check for call compatibility.
6195 IF NOT FND_API.Compatible_API_Call
6196 (lc_api_version_number,
6197 p_api_version_number,
6198 lc_api_name,
6199 G_PKG_NAME)
6200 THEN
6201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6202 END IF;
6203
6204 -- Initialize message list if p_init_msg_list is set to TRUE
6205 IF FND_API.to_boolean(p_init_msg_list) THEN
6206 FND_MSG_PUB.initialize;
6207 END IF;
6208
6209 -- Initialize API return status to success
6210 x_return_status:=FND_API.G_RET_STS_SUCCESS;
6211
6212 l_job_header_rec.organization_id :=
6213 fnd_profile.value('CSD_DEF_REP_INV_ORG');
6214
6215
6216 IF l_job_header_rec.organization_id is NULL THEN
6217
6218 FND_MESSAGE.SET_NAME('CSD','CSD_DEF_REP_INV_NULL');
6219 FND_MSG_PUB.ADD;
6220 RAISE FND_API.G_EXC_ERROR;
6221 END IF;
6222
6223 l_job_prefix := fnd_profile.value('CSD_DEFAULT_JOB_PREFIX');
6224
6225 -- If l_job_prefix is null, throw an error and return;
6226 IF l_job_prefix is NULL THEN
6227 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_PREFIX_NULL');
6228 FND_MSG_PUB.ADD;
6229 RAISE FND_API.G_EXC_ERROR;
6230 END IF;
6231
6232 l_job_header_rec.class_code :=
6233 fnd_profile.value('CSD_DEF_WIP_ACCOUNTING_CLASS');
6234
6235 IF l_job_header_rec.class_code is NULL THEN
6236 FND_MESSAGE.SET_NAME('CSD','CSD_CLASS_CODE_NULL');
6237 FND_MSG_PUB.ADD;
6238 RAISE FND_API.G_EXC_ERROR;
6239 END IF;
6240
6241 -- Populate the Job Header Record
6242
6243 -- Assign the WIP Job Status lookup codes corresponding to Released
6244 -- and Unreleased Job status,
6245 -- to be passed for WIP Interface Table
6246
6247
6248 if fnd_profile.value('CSD_DEFAULT_JOB_STATUS') = 'RELEASED' then
6249 l_job_header_rec.status_type := lc_released_status_code ;
6250
6251 elsif nvl( fnd_profile.value('CSD_DEFAULT_JOB_STATUS'), 'UNRELEASED' ) = 'UNRELEASED' then
6252 l_job_header_rec.status_type := lc_unreleased_status_code;
6253 end if;
6254
6255
6256 l_job_header_rec.load_type := lc_non_standard_load_type;
6257 l_job_header_rec.first_unit_start_date := sysdate;
6258 l_job_header_rec.last_unit_completion_date := sysdate;
6259 l_job_header_rec.start_quantity := p_repair_quantity;
6260
6261 -- Fix for bug# 3109417
6262 -- If the profile CSD: Default WIP MRP Net Qty to Zero is set to
6263 -- null / 'N' then net_quantity = start_quantity else if the
6264 -- profile is set to 'Y' then net_quantity = 0
6265 IF ( nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N') = 'N' ) THEN
6266 l_job_header_rec.net_quantity := p_repair_quantity;
6267 ELSIF ( fnd_profile.value('CSD_WIP_MRP_NET_QTY') = 'Y' ) THEN
6268 l_job_header_rec.net_quantity := 0;
6269 END IF;
6270
6271 -- dbms_output.put_line('Before Loop');
6272 FOR sc_ctr in p_service_code_tbl.FIRST..
6273 p_service_code_tbl.LAST
6274
6275 LOOP
6276
6277 -- dbms_output.put_line('Inside Loop');
6278 l_job_header_rec.primary_item_id :=
6279 p_service_code_tbl(sc_ctr).inventory_item_id ;
6280
6281 l_bills_routes_count := 0; -- swai: bug 5239301
6282 FOR bills_routes_rec in c_get_bills_routes(
6283 p_service_code_tbl(sc_ctr).service_code_id, l_job_header_rec.organization_id )
6284 LOOP
6285 -- dbms_output.put_line('Inside 2nd loop');
6286
6287 l_bills_routes_count := l_bills_routes_count + 1; -- swai: bug 5239301
6288
6289 -- Populate the Bill and Routing information
6290 -- table. This is passed to the insert_job_header
6291 -- procedure
6292
6293 -- Get the Group_id to be used for WIP Create Job,
6294 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
6295
6296 l_job_header_rec.interface_id := l_job_header_rec.group_id;
6297 l_job_header_rec.bom_reference_id := bills_routes_rec.bom_reference_id ;
6298 l_job_header_rec.routing_reference_id := bills_routes_rec.routing_reference_id ;
6299 l_job_header_rec. alternate_bom_designator:= bills_routes_rec. alternate_bom_designator;
6300 l_job_header_rec. alternate_routing_designator:= bills_routes_rec. alternate_routing_designator;
6301 l_job_header_rec.completion_subinventory := bills_routes_rec.completion_subinventory;
6302 l_job_header_rec.completion_locator_id := bills_routes_rec. completion_locator_id;
6303 generate_job_name ( p_job_prefix =>l_job_prefix,
6304 p_organization_id => l_job_header_rec.organization_id,
6305 x_job_name => l_job_header_rec.job_name );
6306 -- dbms_output.put_line('After generate job name');
6307
6308
6309 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6310 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
6311 lc_mod_name||'beforecallinsert',
6312 'Just before calling insert_job_header');
6313 END IF;
6314
6315 -- Call procedure to insert job header and bills, routing
6316 -- information into wip_job_schedule_interface table
6317
6318 insert_job_header( p_job_header_rec => l_job_header_rec,
6319 x_return_status => x_return_status );
6320
6321
6322 -- dbms_output.put_line('After insert_job_header');
6323
6324
6325 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6327 END IF;
6328
6329 -- CALL WIP API to process records in wip interface table,
6330 -- If API fails, Raise error, rollback and return
6331
6332 -- Call WIP Mass Load API
6333
6334 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6335 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
6336 lc_mod_name||'beforecallcreateonejob',
6337 'Just before calling WIP_MASSLOAD_PUB.createOneJob');
6338 END IF;
6339
6340 WIP_MASSLOAD_PUB.createOneJob( p_interfaceID => l_job_header_rec.group_id,
6341 p_validationLevel => p_validation_level,
6342 x_wipEntityID => l_wip_entity_id,
6343 x_returnStatus => x_return_status,
6344 x_errorMsg => x_msg_data );
6345
6346
6347 If ( ml_error_exists( l_job_header_rec.group_id ) or
6348 x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
6349
6350 -- ROLLBACK to GENERATE_WIP_JOBS_FROM_SCS_PVT ;
6351
6352
6353 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
6354 FND_MSG_PUB.ADD;
6355 RAISE FND_API.G_EXC_ERROR;
6356
6357 -- Need to rollback Raise exception -
6358 -- once commit is removed from above call
6359
6360 end if;
6361
6362
6363 --*****Below are the code to Default Repair Item as Material on Job**********
6364
6365 l_default_ro_item := nvl(FND_PROFILE.VALUE('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'), 'N');
6366 --taklam
6367 if (l_default_ro_item = 'Y') then
6368
6369 -- swai: bug 7477845/7483291
6370 -- check if there another job existing for this RO. If so, do not default
6371 -- the RO item as a material. Must compare we.wip_entity_id since
6372 -- crj.wip_entity_id may be null (until wip_update is done).
6373 select count(*)
6374 into l_num_other_jobs
6375 from csd_repair_job_xref crj,
6376 wip_entities we
6377 where crj.job_name = we.wip_entity_name
6378 and crj.organization_id = we.organization_id
6379 and crj.repair_line_id = p_repair_line_id
6380 and we.wip_entity_id <> l_wip_entity_id;
6381
6382 if (l_num_other_jobs = 0) then
6383 OPEN c_repair_line_info(p_repair_line_id);
6384 FETCH c_repair_line_info into
6385 l_inventory_item_id,
6386 l_unit_of_measure,
6387 l_quantity,
6388 l_serial_number,
6389 l_inventory_org_id;
6390 CLOSE c_repair_line_info;
6391 l_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
6392
6393 --Get serial number control code and lot control code
6394 OPEN cur_get_item_attribs (l_inventory_org_id,
6395 l_inventory_item_id);
6396
6397 FETCH cur_get_item_attribs
6398 INTO l_serial_control_code;
6399 CLOSE cur_get_item_attribs;
6400
6401
6402 IF l_serial_control_code IN (2, 5) then
6403 OPEN c_get_serial_info (l_inventory_item_id, l_serial_number, l_inventory_org_id);
6404 FETCH c_get_serial_info
6405 INTO l_current_status,l_current_subinventory_code;
6406 CLOSE c_get_serial_info;
6407 --current status = 3 is valid serial number
6408 if (l_current_status = 3) then
6409 l_subinventory := l_current_subinventory_code;
6410 else
6411 l_serial_number := null;
6412 end if;
6413 else
6414 --don't pass the serial number, it is not valid serial number
6415 l_serial_number := null;
6416 end if;
6417
6418
6419 l_dummy := null;
6420 OPEN c_count_material(l_wip_entity_id, l_inventory_item_id);
6421 FETCH c_count_material into l_dummy;
6422 CLOSE c_count_material;
6423
6424
6425 if (l_dummy is null) then
6426 --Default Repair Item as Material on Job
6427 l_mtl_txn_dtls_tbl.delete;
6428
6429 OPEN c_get_min_operation_seq(l_wip_entity_id);
6430 FETCH c_get_min_operation_seq into l_operation_seq_num;
6431 CLOSE c_get_min_operation_seq;
6432
6433 if (l_operation_seq_num is null) then
6434 l_operation_seq_num := 1;
6435 end if;
6436
6437 l_mtl_txn_dtls_tbl(0).INVENTORY_ITEM_ID :=l_inventory_item_id;
6438 l_mtl_txn_dtls_tbl(0).WIP_ENTITY_ID :=l_wip_entity_id;
6439 l_mtl_txn_dtls_tbl(0).ORGANIZATION_ID :=l_inventory_org_id;
6440 l_mtl_txn_dtls_tbl(0).OPERATION_SEQ_NUM :=l_operation_seq_num;
6441 l_mtl_txn_dtls_tbl(0).TRANSACTION_QUANTITY :=l_quantity; --repair order qty
6442 l_mtl_txn_dtls_tbl(0).TRANSACTION_UOM :=l_unit_of_measure; --Repair order UOM
6443 l_mtl_txn_dtls_tbl(0).SERIAL_NUMBER :=l_serial_number;
6444 l_mtl_txn_dtls_tbl(0).SUPPLY_SUBINVENTORY :=l_subinventory;
6445 l_mtl_txn_dtls_tbl(0).OBJECT_VERSION_NUMBER := 1;
6446 l_mtl_txn_dtls_tbl(0).NEW_ROW := 'Y';
6447
6448
6449 -- call API to create Repair Actuals header
6450 CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_MTL_TXN_DTLS
6451 ( p_api_version_number => 1.0,
6452 p_init_msg_list => 'T',
6453 p_commit => 'F',
6454 p_validation_level => 1,
6455 p_mtl_txn_dtls_tbl => l_mtl_txn_dtls_tbl,
6456 x_op_created => l_op_created,
6457 x_return_status => x_return_status,
6458 x_msg_count => x_msg_count,
6459 x_msg_data => x_msg_data);
6460 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
6461 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
6462 FND_MSG_PUB.ADD;
6463 RAISE FND_API.G_EXC_ERROR;
6464 END IF;
6465 end if;
6466 end if; -- swai: bug 7477845/7483291 l_num_other_jobs = 0
6467 end if;
6468
6469 --*****End of the code to Default Repair Item as Material on Job**********
6470
6471 -- dbms_output.put_line('After createOneJob');
6472
6473 -- call procedures to insert a row in csd_repair_job_xref
6474 -- and csd_repair_history tables for the job created.
6475
6476 l_user_id := fnd_global.user_id;
6477
6478 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6479 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
6480 lc_mod_name||'beforecallxrefwrite',
6481 'Just before calling csd_to_form_repair_job_xref.validate_and_write');
6482 END IF;
6483
6484 csd_to_form_repair_job_xref.validate_and_write(
6485 p_api_version_number => lc_api_version_number,
6486 p_init_msg_list => FND_API.G_FALSE,
6487 p_commit => FND_API.G_FALSE,
6488 p_validation_level => NULL,
6489 p_action_code => 0,
6490 px_repair_job_xref_id => l_repair_xref_id,
6491 p_created_by => l_user_id,
6492 p_creation_date => SYSDATE,
6493 p_last_updated_by => l_user_id,
6494 p_last_update_date => SYSDATE,
6495 p_last_update_login => l_user_id,
6496 p_repair_line_id => p_repair_line_id,
6497 p_wip_entity_id => l_wip_entity_id,
6498 p_group_id => l_job_header_rec.group_id,
6499 p_organization_id => l_job_header_rec.organization_id,
6500 p_quantity => p_repair_quantity,
6501 p_INVENTORY_ITEM_ID => l_job_header_rec.primary_item_id,
6502 p_ITEM_REVISION => null,
6503 p_OBJECT_VERSION_NUMBER => NULL,
6504 p_attribute_category => NULL,
6505 p_attribute1 => NULL,
6506 p_attribute2 => NULL,
6507 p_attribute3 => NULL,
6508 p_attribute4 => NULL,
6509 p_attribute5 => NULL,
6510 p_attribute6 => NULL,
6511 p_attribute7 => NULL,
6512 p_attribute8 => NULL,
6513 p_attribute9 => NULL,
6514 p_attribute10 => NULL,
6515 p_attribute11 => NULL,
6516 p_attribute12 => NULL,
6517 p_attribute13 => NULL,
6518 p_attribute14 => NULL,
6519 p_attribute15 => NULL,
6520 p_quantity_completed => NULL,
6521 p_job_name => l_job_header_rec.job_name,
6522 p_source_type_code => lc_service_code,
6523 p_source_id1 => p_service_code_tbl(sc_ctr).service_code_id,
6524 p_ro_service_code_id => p_service_code_tbl(sc_ctr).ro_service_code_id,
6525 x_return_status => x_return_status,
6526 x_msg_count => x_msg_count,
6527 x_msg_data => x_msg_data);
6528
6529 -- dbms_output.put_line('After call to csd_to_form_repair_job_xref.validate_and_write');
6530
6531
6532 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6533 ROLLBACK to GENERATE_WIP_JOBS_FROM_SCS_PVT ;
6534 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6535 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
6536 lc_mod_name||'exc_exception',
6537 'G_EXC_ERROR Exception');
6538 END IF;
6539 RETURN;
6540 END IF;
6541
6542 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6543 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
6544 lc_mod_name||'beforecallhistwrite',
6545 'Just before calling csd_to_form_repair_history.validate_and_write');
6546 END IF;
6547
6548 csd_to_form_repair_history.validate_and_write(
6549 p_api_version_number => lc_api_version_number,
6550 p_init_msg_list => FND_API.G_FALSE,
6551 p_commit => FND_API.G_FALSE,
6552 p_validation_level => NULL,
6553 p_action_code => 0,
6554 px_repair_history_id => l_rep_hist_id,
6555 p_OBJECT_VERSION_NUMBER => NULL,
6556 p_request_id => NULL,
6557 p_program_id => NULL,
6558 p_program_application_id => NULL,
6559 p_program_update_date => NULL,
6560 p_created_by => l_user_id,
6561 p_creation_date => SYSDATE,
6562 p_last_updated_by => l_user_id,
6563 p_last_update_date => SYSDATE,
6564 p_repair_line_id => p_repair_line_id,
6565 p_event_code => 'JS',
6566 p_event_date => SYSDATE,
6567 p_quantity => p_repair_quantity,
6568 p_paramn1 => l_wip_entity_id,
6569 p_paramn2 => l_job_header_rec.organization_id,
6570 p_paramn3 => NULL,
6571 p_paramn4 => NULL,
6572 p_paramn5 => p_repair_quantity,
6573 p_paramn6 => NULL,
6574 p_paramn8 => NULL,
6575 p_paramn9 => NULL,
6576 p_paramn10 => NULL,
6577 p_paramc1 => l_job_header_rec.job_name,
6578 p_paramc2 => NULL,
6579 p_paramc3 => NULL,
6580 p_paramc4 => NULL,
6581 p_paramc5 => NULL,
6582 p_paramc6 => NULL,
6583 p_paramc7 => NULL,
6584 p_paramc8 => NULL,
6585 p_paramc9 => NULL,
6586 p_paramc10 => NULL,
6587 p_paramd1 => NULL ,
6588 p_paramd2 => NULL ,
6589 p_paramd3 => NULL ,
6590 p_paramd4 => NULL ,
6591 p_paramd5 => SYSDATE,
6592 p_paramd6 => NULL ,
6593 p_paramd7 => NULL ,
6594 p_paramd8 => NULL ,
6595 p_paramd9 => NULL ,
6596 p_paramd10 => NULL ,
6597 p_attribute_category => NULL ,
6598 p_attribute1 => NULL ,
6599 p_attribute2 => NULL ,
6600 p_attribute3 => NULL ,
6601 p_attribute4 => NULL ,
6602 p_attribute5 => NULL ,
6603 p_attribute6 => NULL ,
6604 p_attribute7 => NULL ,
6605 p_attribute8 => NULL ,
6606 p_attribute9 => NULL ,
6607 p_attribute10 => NULL ,
6608 p_attribute11 => NULL ,
6609 p_attribute12 => NULL ,
6610 p_attribute13 => NULL ,
6611 p_attribute14 => NULL ,
6612 p_attribute15 => NULL ,
6613 p_last_update_login => l_user_id,
6614 x_return_status => x_return_status,
6615 x_msg_count => x_msg_count,
6616 x_msg_data => x_msg_data);
6617
6618 -- dbms_output.put_line('after call to csd_to_form_repair_history.validate_and_write');
6619
6620 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6621 ROLLBACK to GENERATE_WIP_JOBS_FROM_SCS_PVT ;
6622 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6623 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
6624 lc_mod_name||'exc_exception',
6625 'G_EXC_ERROR Exception');
6626 END IF;
6627 RETURN;
6628 END IF;
6629 END LOOP; -- bills and routes
6630
6631 -- swai: bug 5239301
6632 -- if there are no bills or routes for this service code, get the
6633 -- service code name and log a warning message
6634 if (l_bills_routes_count = 0) then
6635 l_show_messages_flag := 'T';
6636 open c_get_service_code_details(p_service_code_tbl(sc_ctr).service_code_id);
6637 fetch c_get_service_code_details into l_service_code;
6638 close c_get_service_code_details;
6639 FND_MESSAGE.SET_NAME('CSD', 'CSD_NO_BILLS_ROUTES_FOR_SC');
6640 FND_MESSAGE.set_token('SERVICE_CODE', l_service_code);
6641 FND_MSG_PUB.add_detail(p_message_type => FND_MSG_PUB.G_WARNING_MSG);
6642 end if;
6643 -- swai: end bug 5239301
6644
6645 l_ro_service_code_rec.ro_service_code_id := p_service_code_tbl(sc_ctr).ro_service_code_id;
6646 l_ro_service_code_rec.applied_to_work_flag := 'Y' ;
6647 l_ro_service_code_rec.object_version_number := p_service_code_tbl(sc_ctr).object_version_number;
6648
6649
6650 -- l_object_version_number := p_service_code_tbl(sc_ctr).object_version_number;
6651
6652 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6653 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
6654 lc_mod_name||'beforecallupdatesc',
6655 'Just before calling CSD_RO_SERVICE_CODES_PVT.Update_RO_Service_Code');
6656 END IF;
6657
6658
6659
6660 CSD_RO_SERVICE_CODES_PVT.Update_RO_Service_Code(
6661 p_api_version => lc_api_version_number,
6662 p_commit => FND_API.G_FALSE,
6663 p_init_msg_list => FND_API.G_FALSE,
6664 p_validation_level => 100,
6665 p_ro_service_code_rec => l_ro_service_code_rec,
6666 x_obj_ver_number => l_object_version_number,
6667 x_return_status => x_return_status,
6668 x_msg_count => x_msg_count,
6669 x_msg_data => x_msg_data ) ;
6670
6671
6672 -- dbms_output.put_line('after call to CSD_RO_SERVICE_CODES_PVT.Update_RO_Service_Code');
6673 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6674 -- dbms_output.put_line('inside return status CSD_RO_SERVICE_CODES_PVT.Update_RO_Service_Code');
6675 ROLLBACK to GENERATE_WIP_JOBS_FROM_SCS_PVT ;
6676 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6677 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
6678 lc_mod_name||'exc_exception',
6679 'G_EXC_ERROR Exception');
6680 END IF;
6681 RETURN;
6682 END IF;
6683
6684 END LOOP; -- service codes
6685
6686 -- swai: bug 5239301
6687 -- if there are messages to show, then set the return status in order
6688 -- to flag this, but do not rollback.
6689 if l_show_messages_flag = 'T' then
6690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6691 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6692 p_count => x_msg_count,
6693 p_data => x_msg_data);
6694 end if;
6695 -- swai: end bug 5239301
6696
6697 -- Standard check for p_commit
6698 IF FND_API.to_Boolean( p_commit )
6699 THEN
6700 COMMIT WORK;
6701 END IF;
6702
6703
6704 EXCEPTION
6705 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6706 ROLLBACK to GENERATE_WIP_JOBS_FROM_SCS_PVT ;
6707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6708
6709 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6710 p_count => x_msg_count,
6711 p_data => x_msg_data);
6712
6713 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6714 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
6715 lc_mod_name||'unx_exception',
6716 'G_EXC_UNEXPECTED_ERROR Exception');
6717 END IF;
6718
6719
6720 WHEN FND_API.G_EXC_ERROR THEN
6721 ROLLBACK to GENERATE_WIP_JOBS_FROM_SCS_PVT ;
6722 x_return_status := FND_API.G_RET_STS_ERROR;
6723
6724
6725 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6726 p_count => x_msg_count,
6727 p_data => x_msg_data);
6728
6729 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6730 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
6731 lc_mod_name||'exc_exception',
6732 'G_EXC_ERROR Exception');
6733 END IF;
6734
6735 WHEN OTHERS THEN
6736 ROLLBACK to GENERATE_WIP_JOBS_FROM_SCS_PVT ;
6737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6738
6739 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6740
6741 -- Add Unexpected Error to Message List, here SQLERRM is used for
6742 -- getting the error
6743
6744 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
6745 p_procedure_name => lc_api_name );
6746 END IF;
6747
6748 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6749 p_count => x_msg_count,
6750 p_data => x_msg_data);
6751
6752 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
6753 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
6754 lc_mod_name||'others_exception',
6755 'OTHERS Exception');
6756 END IF;
6757
6758
6759 END generate_wip_jobs_from_scs;
6760
6761
6762
6763 END CSD_HV_WIP_JOB_PVT;