1 PACKAGE BODY CSD_WIP_JOB_PVT AS
2 /* $Header: csdvwjbb.pls 120.13.12010000.2 2008/10/15 20:39:07 swai ship $*/
3 -- Start of Comments
4 -- Package name : CSD_WIP_JOB_PVT
5 -- Purpose : This package submits and creates WIP jobs using WIP Mass Load.
6 -- Submit_Jobs is the API which uses various helper procedures to
7 -- Submit WIP Mass Load, waits for it to complete successfully, then
8 -- calls WIP_UPDATE API to update CSD_REPAIR_JOB_XREF with the
9 -- newly created wip_entitity_id values.
10 -- Besides these procedure, this package has a helper function
11 -- is_dmf_patchset_level_j which is used by the client application
12 -- to check if the discrete manufacturing patchset level is at 'j' or
13 -- beyond. Based on this, the client application decides how to call
14 -- the WIP completion form.
15 --
16 -- History : 08/20/2003, Created by Shiv Ragunathan
17 -- History :
18 -- History :
19 -- NOTE :
20 -- End of Comments
21
22
23 -- Define Global Variable --
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_WIP_JOB_PVT';
25
26
27
28 -- This procedure accepts job header, bills and routing information and inserts it into
29 -- WIP_JOB_SCHEDULE_INTERFACE table. This procedure inserts one record at a time, hence
30 -- this procedure needs to be called in a loop for multiple jobs being submitted
31 -- to WIP Mass Load. If all the records need to be processed by a single WIP Mass Load
32 -- request, they should all be passed in the the same group_id.
33
34 PROCEDURE insert_job_header
35 (
36 p_job_header_rec IN JOB_HEADER_REC_TYPE,
37 p_job_bill_routing_rec IN JOB_BILL_ROUTING_REC_TYPE,
38 p_group_id IN NUMBER,
39 x_return_status OUT NOCOPY VARCHAR2
40 )
41 IS
42
43 -- Job Record to hold the Job header, bills and routing information being inserted
44 -- into wip_job_schedule_interface
45
46 l_job_header_rec wip_job_schedule_interface%ROWTYPE;
47
48
49 -- variables used for FND_LOG debug messages
50
51 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
52 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
53 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
54
55
56 -- Constants Used for Inserting into wip_job_schedule_interface,
57 -- These are the values needed for WIP Mass Load to pick up the records
58
59 -- Indicates that the process Phase is Validation
60 l_validation_phase CONSTANT NUMBER := 2;
61
62 -- Indicates that the process_status is Pending
63 l_pending_status CONSTANT NUMBER := 1;
64
65 -- Source Code Value of 'Depot_Repair'
66 l_depot_repair_source_code CONSTANT VARCHAR2(30) := 'DEPOT_REPAIR';
67
68 -- Depot repair Application Id passed as source_line_id
69 l_depot_app_source_line_id CONSTANT NUMBER := 512;
70
71 -- Non Standard Discrete Job Load Type
72 l_non_standard_load_type CONSTANT NUMBER := 4;
73
74 l_default_ro_item VARCHAR2(1);
75
76 BEGIN
77
78
79 IF ( l_proc_level >= l_debug_level ) then
80 FND_LOG.STRING( l_proc_level,
81 l_mod_name||'begin',
82 'Entering procedure insert_job_header' );
83 END IF;
84
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87
88 -- Populate the record l_job_header_rec
89
90
91 -- Populate the constant values
92
93 l_job_header_rec.process_phase := l_validation_phase;
94 l_job_header_rec.process_status := l_pending_status;
95 l_job_header_rec.source_code := l_depot_repair_source_code;
96 l_job_header_rec.source_line_id := l_depot_app_source_line_id ;
97 l_job_header_rec.load_type := l_non_standard_load_type;
98
99 l_job_header_rec.group_id := p_group_id;
100
101 l_default_ro_item := nvl(FND_PROFILE.VALUE('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'), 'N');
102
103 if (l_default_ro_item = 'Y') then
104 l_job_header_rec.interface_id := p_group_id;
105 end if;
106
107
108 -- Populate the row who columns
109
110 l_job_header_rec.creation_date := SYSDATE;
111 l_job_header_rec.last_update_date := SYSDATE;
112 l_job_header_rec.created_by := fnd_global.user_id;
113 l_job_header_rec.last_updated_by := fnd_global.user_id;
114 l_job_header_rec.last_update_login := fnd_global.login_id;
115
116
117 l_job_header_rec.job_name := p_job_bill_routing_rec.job_name;
118 l_job_header_rec.organization_id := p_job_header_rec.organization_id;
119 l_job_header_rec.status_type := p_job_header_rec.status_type;
120 l_job_header_rec.first_unit_start_date := p_job_header_rec.scheduled_start_date;
121 l_job_header_rec.last_unit_completion_date := p_job_header_rec.scheduled_end_date;
122 l_job_header_rec.primary_item_id := p_job_header_rec.inventory_item_id;
123
124 -- rfieldma, project integration
125 l_job_header_rec.project_id := p_job_header_rec.project_id;
126 l_job_header_rec.task_id := p_job_header_rec.task_id;
127 l_job_header_rec.end_item_unit_number := p_job_header_rec.unit_number;
128
129
130 -- WIP Accounting Class code
131
132 l_job_header_rec.class_code := p_job_header_rec.class_code;
133 l_job_header_rec.start_quantity := p_job_header_rec.quantity;
134
135 -- Fix for bug# 3109417
136 -- If the profile CSD: Default WIP MRP Net Qty to Zero is set to
137 -- null / 'N' then net_quantity = start_quantity else if the
138 -- profile is set to 'Y' then net_quantity = 0
139 IF ( nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N') = 'N' ) THEN
140 l_job_header_rec.net_quantity := p_job_header_rec.quantity;
141 ELSIF ( fnd_profile.value('CSD_WIP_MRP_NET_QTY') = 'Y' ) THEN
142 l_job_header_rec.net_quantity := 0;
143 END IF;
144
145
146 -- Bill and Routing information
147
148 l_job_header_rec.routing_reference_id := p_job_bill_routing_rec.routing_reference_id ;
149 l_job_header_rec.bom_reference_id := p_job_bill_routing_rec.bom_reference_id ;
150 l_job_header_rec.alternate_routing_designator := p_job_bill_routing_rec.alternate_routing_designator ;
151 l_job_header_rec.alternate_bom_designator := p_job_bill_routing_rec.alternate_bom_designator ;
152 l_job_header_rec.completion_subinventory := p_job_bill_routing_rec.completion_subinventory;
153 l_job_header_rec.completion_locator_id := p_job_bill_routing_rec.completion_locator_id;
154
155
156 --insert into table wip_job_schedule_interface
157 BEGIN
158 INSERT INTO wip_job_schedule_interface
159 (
160 last_update_date,
161 last_updated_by,
162 creation_date,
163 created_by,
164 last_update_login,
165 load_type,
166 process_phase,
167 process_status,
168 group_id,
169 source_code,
170 source_line_id,
171 job_name,
172 organization_id,
173 status_type,
174 first_unit_start_date,
175 last_unit_completion_date,
176 completion_subinventory,
177 completion_locator_id,
178 start_quantity,
179 net_quantity,
180 class_code,
181 primary_item_id,
182 bom_reference_id,
183 routing_reference_id,
184 alternate_routing_designator,
185 alternate_bom_designator,
186 -- rfieldma, project integration
187 project_id,
188 task_id,
189 end_item_unit_number,
190 interface_id
191 )
192 VALUES
193 (
194 l_job_header_rec.last_update_date,
195 l_job_header_rec.last_updated_by,
196 l_job_header_rec.creation_date,
197 l_job_header_rec.created_by,
198 l_job_header_rec.last_update_login,
199 l_job_header_rec.load_type,
200 l_job_header_rec.process_phase,
201 l_job_header_rec.process_status,
202 l_job_header_rec.group_id,
203 l_job_header_rec.source_code,
204 l_job_header_rec.source_line_id,
205 l_job_header_rec.job_name,
206 l_job_header_rec.organization_id,
207 l_job_header_rec.status_type,
208 l_job_header_rec.first_unit_start_date,
209 l_job_header_rec.last_unit_completion_date,
210 l_job_header_rec.completion_subinventory,
211 l_job_header_rec.completion_locator_id,
212 l_job_header_rec.start_quantity,
213 l_job_header_rec.net_quantity,
214 l_job_header_rec.class_code,
215 l_job_header_rec.primary_item_id,
216 l_job_header_rec.bom_reference_id,
217 l_job_header_rec.routing_reference_id,
218 l_job_header_rec.alternate_routing_designator,
219 l_job_header_rec.alternate_bom_designator,
220 -- rfieldma, project integration
221 l_job_header_rec.project_id,
222 l_job_header_rec.task_id,
223 l_job_header_rec.end_item_unit_number,
224 l_job_header_rec.interface_id
225 );
226 EXCEPTION
227 WHEN OTHERS THEN
228 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_HEADER_INSERT_ERR');
229 FND_MESSAGE.SET_TOKEN('JOB_NAME', l_job_header_rec.job_name );
230 FND_MSG_PUB.ADD;
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
232 RETURN;
233 END;
234
235
236 IF ( l_proc_level >= l_debug_level ) then
237 FND_LOG.STRING( l_proc_level,
238 l_mod_name||'end',
239 'Leaving procedure insert_job_header');
240 END IF;
241
242
243 END insert_job_header;
244
245 -- overloaded version of insert job header. receives the job header rec and inserts into the
246 -- wip_job_schedule_interface table.
247 -- The job header rec is created as ROWTYPE of wip_job_schedule_interface.
248 -- 12.1 Create Job from Estimates change, subhat.
249
250 PROCEDURE insert_job_header
251 (
252 p_job_header_rec IN wip_job_schedule_interface%ROWTYPE,
253 x_return_status OUT NOCOPY VARCHAR2
254 )
255 IS
256
257 -- Job Record to hold the Job header, bills and routing information being inserted
258 -- into wip_job_schedule_interface
259
260 l_job_header_rec wip_job_schedule_interface%ROWTYPE := p_job_header_rec;
261
262
263 -- variables used for FND_LOG debug messages
264
265 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
266 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
267 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
268
269
270 -- Constants Used for Inserting into wip_job_schedule_interface,
271 -- These are the values needed for WIP Mass Load to pick up the records
272
273 -- Indicates that the process Phase is Validation
274 lc_validation_phase CONSTANT NUMBER := 2;
275
276 -- Indicates that the process_status is Pending
277 lc_pending_status CONSTANT NUMBER := 1;
278
279 -- Source Code Value of 'Depot_Repair'
280 lc_depot_repair_source_code CONSTANT VARCHAR2(30) := 'DEPOT_REPAIR';
281
282 -- Depot repair Application Id passed as source_line_id
283 lc_depot_app_source_line_id CONSTANT NUMBER := 512;
284
285
286
287
288 BEGIN
289
290
291 IF ( l_proc_level >= l_debug_level ) then
292 FND_LOG.STRING( l_proc_level,
293 l_mod_name||'begin',
294 'Entering procedure insert_job_header' );
295 END IF;
296
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299
300 -- Populate the record l_job_header_rec
301
302
303 -- Populate the constant values
304
305 l_job_header_rec.process_phase := lc_validation_phase;
306 l_job_header_rec.process_status := lc_pending_status;
307 l_job_header_rec.source_code := lc_depot_repair_source_code;
308 l_job_header_rec.source_line_id := lc_depot_app_source_line_id ;
309
310
311 -- Populate the row who columns
312
313 l_job_header_rec.creation_date := SYSDATE;
314 l_job_header_rec.last_update_date := SYSDATE;
315 l_job_header_rec.created_by := fnd_global.user_id;
316 l_job_header_rec.last_updated_by := fnd_global.user_id;
317 l_job_header_rec.last_update_login := fnd_global.login_id;
318
319
320 --insert into table wip_job_schedule_interface
321 BEGIN
322 INSERT INTO wip_job_schedule_interface
323 (
324 wip_entity_id,
325 interface_id,
326 last_update_date,
327 last_updated_by,
328 creation_date,
329 created_by,
330 last_update_login,
331 load_type,
332 process_phase,
333 process_status,
334 group_id,
335 header_id,
336 source_code,
337 source_line_id,
338 job_name,
339 organization_id,
340 status_type,
341 first_unit_start_date,
342 last_unit_completion_date,
343 completion_subinventory,
344 completion_locator_id,
345 start_quantity,
346 net_quantity,
347 class_code,
348 primary_item_id,
349 bom_reference_id,
350 routing_reference_id,
351 alternate_routing_designator,
352 alternate_bom_designator,
353 project_id,
354 task_id,
355 end_item_unit_number
356 )
357 VALUES
358 (
359 l_job_header_rec.wip_entity_id,
360 l_job_header_rec.interface_id,
361 l_job_header_rec.last_update_date,
362 l_job_header_rec.last_updated_by,
363 l_job_header_rec.creation_date,
364 l_job_header_rec.created_by,
365 l_job_header_rec.last_update_login,
366 l_job_header_rec.load_type,
367 l_job_header_rec.process_phase,
368 l_job_header_rec.process_status,
369 l_job_header_rec.group_id,
370 l_job_header_rec.header_id,
371 l_job_header_rec.source_code,
372 l_job_header_rec.source_line_id,
373 l_job_header_rec.job_name,
374 l_job_header_rec.organization_id,
375 l_job_header_rec.status_type,
376 l_job_header_rec.first_unit_start_date,
377 l_job_header_rec.last_unit_completion_date,
378 l_job_header_rec.completion_subinventory,
379 l_job_header_rec.completion_locator_id,
380 l_job_header_rec.start_quantity,
381 l_job_header_rec.net_quantity,
382 l_job_header_rec.class_code,
383 l_job_header_rec.primary_item_id,
384 l_job_header_rec.bom_reference_id,
385 l_job_header_rec.routing_reference_id,
386 l_job_header_rec.alternate_routing_designator,
387 l_job_header_rec.alternate_bom_designator,
388 l_job_header_rec.project_id,
389 l_job_header_rec.task_id,
390 l_job_header_rec.end_item_unit_number
391 );
392 EXCEPTION
393 WHEN OTHERS THEN
394 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_HEADER_INSERT_ERR');
395 FND_MESSAGE.SET_TOKEN('JOB_NAME', l_job_header_rec.job_name );
396 FND_MSG_PUB.ADD;
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
398 RETURN;
399 END;
400
401
402 IF ( l_proc_level >= l_debug_level ) then
403 FND_LOG.STRING( l_proc_level,
404 l_mod_name||'end',
405 'Leaving procedure insert_job_header');
406 END IF;
407
408
409 END insert_job_header;
410
411
412
413 -- This Procedure verifies that both the Internal and Standard Concurrent
414 -- managers are up. If either one is down, it writes an error to the
415 -- message list and returns an error status.
416
417 PROCEDURE verify_conc_manager_status
418 (
419 x_return_status OUT NOCOPY VARCHAR2
420 )
421 IS
422
423 -- Used in the call to check if concurrent managers are up
424
425 l_targetp NUMBER;
426 l_activep NUMBER;
427 l_targetp1 NUMBER;
428 l_activep1 NUMBER;
429 l_pmon_method VARCHAR2(30);
430 l_callstat NUMBER;
431
432 -- Declare the constants
433
434 -- FND Application_id under which the standard and intenral concurrent managers are registered,
435 -- Concurrent Manager Id for Standard and Internal Managers.
436 -- These are used in the call to get_manager_status to see if the standard and internal concurrent
437 -- managers are up
438
439 l_fnd_application_id CONSTANT NUMBER := 0;
440 l_internal_manager_id CONSTANT NUMBER := 1;
441 l_standard_manager_id CONSTANT NUMBER := 0;
442
443
444 BEGIN
445
446 x_return_status := FND_API.G_RET_STS_SUCCESS;
447
448 -- Validate if Internal Concurrent Manager is up
449
450 fnd_concurrent.get_manager_status(applid => l_fnd_application_id,
451 managerid => l_internal_manager_id,
452 targetp => l_targetp1,
453 activep => l_activep1,
454 pmon_method => l_pmon_method,
455 callstat => l_callstat);
456
457
458 -- Validate if Standard Concurrent Manager is up
459
460 fnd_concurrent.get_manager_status(applid => l_fnd_application_id,
461 managerid => l_standard_manager_id,
462 targetp => l_targetp,
463 activep => l_activep,
464 pmon_method => l_pmon_method,
465 callstat => l_callstat);
466
467 -- If the actual number of processes that are up for either the Internal
468 -- or Standard Manager is <= 0, which indicates that the concurrent manager
469 -- is down, then add the message to the message list and exit
470
471 IF (l_activep <= 0 OR l_activep1 <= 0) THEN
472 FND_MESSAGE.SET_NAME('CSD','CSD_CONC_MGR_DOWN');
473 FND_MSG_PUB.ADD;
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
475 END IF;
476
477
478 END verify_conc_manager_status;
479
480
481 -- This procedure checks if the specified Job name exists in the
482 -- specified organization. It checks if it exists in
483 -- wip_entities or wip_job_schedule_interface table.
484 -- If it exists, then an Error status is returned.
485 -- If it does not exist in either of the tables, then
486 -- a Sucess status is returned.
487 -- This procedure is used whenever a job_name is generated, to confirm that
488 -- the newly generated job_name does not already exist and hence can be
489 -- used to submit it to WIP Mass Load.
490
491
492 PROCEDURE validate_job_name
493 (
494 p_job_name IN VARCHAR2,
495 p_organization_id IN NUMBER,
496 x_return_status OUT NOCOPY VARCHAR2
497 )
498 IS
499
500 -- Used to check the existence of the Job_name for the specified organizization,
501 l_job_count NUMBER := 0;
502
503 BEGIN
504
505
506 Select count(*) into l_job_count from wip_entities where wip_entity_name = p_job_name and
507 organization_id = p_organization_id ;
508
509 If l_job_count = 0 Then
510
511 -- Job does not exist in WIP_entities, check if it is already inserted in the interface table by another
512 -- process and so may be in the process of getting into WIP.
513 -- If it exists, do not want to use this job name, so return Error
514
515 Select count(*) into l_job_count from wip_job_schedule_interface where job_name = p_job_name and
516 organization_id = p_organization_id ;
517
518 IF l_job_count = 0 THEN
519
520 -- Generated job name does exist either in the interface or wip_entities table,
521 -- Success is returned
522
523 x_return_status := FND_API.G_RET_STS_SUCCESS;
524 RETURN;
525
526 ELSE
527
528 -- Job exists in wip_job_schedule_interface table, hence return Error status
529
530 x_return_status := FND_API.G_RET_STS_ERROR;
531 RETURN;
532
533
534 END IF;
535
536
537 ELSE
538
539 -- Job exists in wip_entities table, hence return Error status
540
541 x_return_status := FND_API.G_RET_STS_ERROR;
542 RETURN;
543
544
545 END IF;
546
547
548
549 END validate_job_name;
550
551
552
553
554 -- This procedure generates a job name by appending a sequence generated number
555 -- to the passed in Job_Prefix
556 -- It Validates that the generated job name is unique for the specified organization,
557 -- It keeps looping and appending the subsequent sequence generated number, till a
558 -- unique Job name is generated
559
560
561 PROCEDURE generate_job_name
562 (
563 p_job_prefix IN VARCHAR2,
564 p_organization_id IN NUMBER,
565 x_job_name OUT NOCOPY VARCHAR2
566 )
567 IS
568
569 l_return_status VARCHAR2(1);
570
571 BEGIN
572
573 Loop
574
575 -- generate the Job Name by appending a sequence generated number to the passed in
576 -- job_prefix
577
578 Select p_job_prefix || TO_CHAR( CSD_JOB_NAME_S.NEXTVAL ) into
579 x_job_name From Dual;
580
581
582 -- Check if the job name generated is unique for the specified organization,
583 -- if not loop around till a unique job name is generated
584
585 Validate_job_name ( p_job_name => x_job_name,
586 p_organization_id => p_organization_id,
587 x_return_status => l_return_status ) ;
588
589 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
590
591 -- Generated job name does not exist both in the interface and wip_entities table, so exit the loop
592
593 exit;
594
595 END IF;
596
597
598 End Loop;
599
600 END generate_job_name;
601
602 -- private procedure.
603 -- this procedure creates a material transactions table type based on the repair estimate lines.
604 -- all the material estimates with source as manual or repair bom would be passed as repair
605 -- estimates. The procedure takes in repair_line_id,wip_entity_id and the material transactions
606 -- table is out parameter.
607 -- The procedure also converts the repair item as default material requirement if the corresponding -- profile option is set. If the repair order item has serial number entered, the same is passed to
608 -- material requirements.
609 -- 12.1 default material requirements -- subhat.
610
611 PROCEDURE import_estms_to_wipjob(p_repair_line_id IN NUMBER,
612 p_wip_entity_id IN NUMBER,
613 x_mtl_txn_dtls_tab_type OUT NOCOPY CSD_HV_WIP_JOB_PVT.mtl_txn_dtls_tbl_type )
614 IS
615
616 -- local variables
617
618 l_operation_seq_num NUMBER := 1;
619 l_estimate_uom VARCHAR2(25);
620 l_inv_org NUMBER := cs_std.get_item_valdn_orgzn_id;
621 l_counter NUMBER;
622
623 -- cursor to calculate the estimate details.
624
625
626 CURSOR repair_estimate_dtls(p_rep_line_id in number) IS
627 SELECT crl.inventory_item_id,
628 -- bug#7132807, subhat. no need to select estimate quantity.
629 --crl.estimate_quantity,
630 crl.lot_control_code,
631 crl.serial_number_control_code,
632 msi.primary_uom_code,
633 crl.unit_of_measure_code,
634 msi.revision_qty_control_code,
635 msi.new_revision_code,
636 SUM(crl.estimate_quantity) AS quantity
637 FROM csd_repair_estimate_lines_v crl, mtl_system_items_kfv msi
638 WHERE crl.repair_line_id = p_rep_line_id AND
639 crl.inventory_item_id = msi.inventory_item_id AND
640 msi.organization_id = cs_std.get_item_valdn_orgzn_id AND
641 billing_category = 'M' AND
642 est_line_source_type_code IN ('MANUAL','REPAIR_BOM')
643 GROUP BY crl.inventory_item_id,/*crl.estimate_quantity,*/ crl.lot_control_code,crl.serial_number_control_code,msi.primary_uom_code,crl.unit_of_measure_code,msi.revision_qty_control_code, msi.new_revision_code;
644
645 -- cursor to fetch the default operation sequence number
646
647 CURSOR default_operation(p_wip_entity_id IN NUMBER) IS
648 SELECT operation_seq_num
649 FROM wip_operations
650 WHERE wip_entity_id = p_wip_entity_id AND
651 previous_operation_seq_num IS NULL ;
652
653 -- special case ( when CSD: Default Repair Item as Material on Job profile is set to yes)
654 -- cursor to fetch the material details.
655
656 CURSOR repair_item_dtls(p_rep_line_id IN NUMBER) IS
657
658 SELECT cr.inventory_item_id,
659 cr.unit_of_measure,
660 cr.serial_number,
661 cr.quantity,
662 cr.inventory_org_id
663 --msi.serial_number_control_code
664 FROM
665 csd_repairs_v cr
666 WHERE
667 cr.repair_line_id = p_repair_line_id;
668
669 BEGIN
670
671 -- determine the operations to which the materials needs to be issued.
672
673 OPEN default_operation(p_wip_entity_id);
674 FETCH default_operation INTO l_operation_seq_num;
675 CLOSE default_operation;
676
677 -- check the value of l_operation_seq_num if its null make it 1.
678
679 IF l_operation_seq_num IS NULL THEN
680 l_operation_seq_num := 1;
681 END IF;
682
683 l_counter := 1;
684 -- special case
685 -- when the profile CSD: Default Repair Item as Material on Job is set to yes, we need to populate
686 -- the repair item as the material requirement,irrespective if there are any estimate lines or not.
687 -- put the profile value once the profile is created.
688
689 -- clear the collection type.
690
691 x_MTL_TXN_DTLS_TAB_TYPE.DELETE;
692
693 IF nvl(fnd_profile.value('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'),'N') = 'Y' THEN
694 OPEN repair_item_dtls(p_repair_line_id);
695 FETCH repair_item_dtls INTO
696 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).inventory_item_id,
697 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_uom,
698 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).serial_number,
699 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).required_quantity,
700 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).organization_id ;
701 CLOSE repair_item_dtls;
702 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_quantity := x_MTL_TXN_DTLS_TAB_TYPE(l_counter).required_quantity;
703 -- sub inventory is defaulted from the CSD_DEF_HV_SUBINV profile option.
704 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
705 --message(fnd_profile.value('CSD_DEF_HV_SUBINV'));
706 IF x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory IS NULL THEN
707 FND_MESSAGE.SET_NAME('CSD','CSD_DEF_SUB_INV_NULL');
708 FND_MSG_PUB.ADD;
709 RAISE FND_API.G_EXC_ERROR;
710 END IF;
711 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).new_row := 'Y';
712
713 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_locator_id := NULL;
714 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).wip_entity_id := p_wip_entity_id;
715
716 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).operation_seq_num := l_operation_seq_num;
717
718 l_counter := l_counter + 1;
719 END IF;
720
721
722
723 OPEN repair_estimate_dtls(p_repair_line_id);
724
725 LOOP
726
727 FETCH repair_estimate_dtls INTO
728 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).inventory_item_id,
729 -- bug#6903726 subhat
730 --x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_quantity,
731 -- bug#7132807 subhat.
732 --x_MTL_TXN_DTLS_TAB_TYPE(l_counter).required_quantity,
733 -- end bug#6903726 subhat
734 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).lot_control_code,
735 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).serial_number_control_code,
736 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_uom,
737 l_estimate_uom,
738 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).revision_qty_control_code,
739 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).revision,
740 --bug#6903726 subhat
741 --x_MTL_TXN_DTLS_TAB_TYPE(l_counter).required_quantity;
742 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_quantity;
743 --end bug#6903726 subhat
744 EXIT WHEN repair_estimate_dtls%NOTFOUND;
745 -- check if the repair estimate UOM is different from primary UOM for the item.
746 -- if its different then convert the UOM to primary UOM and corrosponding quantity.
747 IF l_estimate_uom <> x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_uom THEN
748 -- if the uom's are different then change convert the quantity.
749 -- for instance if primary uom = 'Ea' and estimate UOM = dozen and estimate quantity = 1
750 -- then the quantity should be changed to 1* uom_Conversion_factor for dozen.
751 NULL;
752 END IF;
753
754 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).organization_id := l_inv_org;
755 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).new_row := 'Y';
756
757 -- sub inventory is defaulted from the CSD_DEF_HV_SUBINV profile option.
758 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
759 --message(fnd_profile.value('CSD_DEF_HV_SUBINV'));
760 IF x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory IS NULL THEN
761 FND_MESSAGE.SET_NAME('CSD','CSD_DEF_SUB_INV_NULL');
762 FND_MSG_PUB.ADD;
763 RAISE FND_API.G_EXC_ERROR;
764 END IF;
765
766 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_locator_id := NULL;
767 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).wip_entity_id := p_wip_entity_id;
768
769 -- check whether the item is serial controlled. If so default in the serial number.
770
771 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).operation_seq_num := l_operation_seq_num;
772
773 l_counter := l_counter + 1;
774 END LOOP;
775
776 IF repair_estimate_dtls%ISOPEN THEN
777 CLOSE repair_estimate_dtls;
778 END IF;
779
780 END import_estms_to_wipjob;
781
782
783 -- API
784 -- SUBMIT_JOBS
785 --
786 -- Purpose
787 -- This API creates WIP Jobs by submitting the passed in Job information to
788 -- WIP Mass Load and updates CSD tables with the newly created jobs information.
789 -- It achieves this by calling helper procedures.
790 --
791 -- This API inserts Job header, Bills and Routing information passed in into
792 -- WIP_JOB_SCHEDULE_INTERFACE table by calling procedure insert_job_header.
793 --
794 -- If job name is not passed in, then it is generated here by appending a
795 -- sequence generated number to the job_name_prefix passed in.
796 -- If job name is passed in, it is validated to make sure that it is unique
797 -- for the specified organization.
798 --
799 -- This API then submits the concurrent request for concurrent
800 -- program 'Depot Repair WIP Job Submission', which submits WIP Mass Load,
801 -- waits for it to complete and then runs the WIP Update program to update WIP
802 -- information in CSD tables.
803 --
804 -- If no routings or bills are passed in, jobs are submitted to WIP Mass Load based
805 -- on the header information to create jobs with no operations, material requirements
806 -- or resource requirements.
807 --
808 -- Arguments
809 -- p_repair_line_id - Repair Line Id of the repair order for which the jobs are being created.
810 -- WIP Update program is run for the specified repair order.
811 -- If jobs are being submitted for more than one repair order, then this is
812 -- passed in as null and the WIP Update program is run for all the eligible
813 -- repair orders.
814 -- p_job_header_rec - Job header Information record. This is the same for all the jobs being created.
815 -- p_x_job_bill_routing_tbl - Table of Bill and Routing information records. Each record results in a
816 -- new job. If a record here has a not null Job Name specified, then the job name
817 -- specified here is used, instead of generating it. This is done only when one job
818 -- is being submitted and the profile option 'Use CSD as Job Prefix' is set to 'N'.
819 -- This is a IN OUT parameter as the generated Job names are passed back to the
820 -- calling program in this table.
821 -- x_group_id - Group_id used for the WIP Mass Load concurrent request submission. This is returned
822 -- to the calling program.
823 -- x_request_id - Concurrent Request id of the concurrent request submitted for concurrent program
824 -- 'Depot Repair WIP Job Submission'. This is passed back to the calling program.
825 --
826 -- Note, p_commit is not specified as a parameter to this API, as for successful submission of a concurrent
827 -- request, a commit is required always, so this API always commits. For the same reason, this API is
828 -- declared as an AUTONOMOUS Transaction. For a AUTONOMOUS Transaction, we cannot rollback to a specified
829 -- SAVEPOINT, hence SAVEPOINT is not specified.
830
831
832 PROCEDURE submit_jobs
833 (
834 p_api_version IN NUMBER,
835 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
836 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
837 x_return_status OUT NOCOPY VARCHAR2,
838 x_msg_count OUT NOCOPY NUMBER,
839 x_msg_data OUT NOCOPY VARCHAR2,
840 p_repair_line_id IN NUMBER,
841 p_job_header_rec IN job_header_rec_type,
842 p_x_job_bill_routing_tbl IN OUT NOCOPY job_bill_routing_tbl_type,
843 x_group_id OUT NOCOPY NUMBER,
844 x_request_id OUT NOCOPY NUMBER
845 )
846 IS
847 PRAGMA AUTONOMOUS_TRANSACTION;
848
849 --This API is an Autonomous Transaction. We have to explicitly commit or rollback the
850 --transactions it or its called procedure contain when it exits. This autonomous
851 --transaction doesn't affect the main transaction in its calling API.
852
853
854 l_api_name CONSTANT VARCHAR2(30) := 'Submit_jobs';
855 l_api_version CONSTANT NUMBER := 1.0;
856
857 l_group_id NUMBER;
858
859
860 -- Bill, routing information for the Job passed to insert_job_header
861 l_job_bill_routing_rec job_bill_routing_rec_type;
862
863
864 -- variables used for FND_LOG debug messages
865
866 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
867 l_stat_level NUMBER := FND_LOG.LEVEL_STATEMENT;
868 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
869 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
870 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.submit_jobs.';
871
872 l_default_ro_item VARCHAR2(1);
873 l_wip_entity_id NUMBER;
874 l_mtl_txn_dtls_tbl CSD_HV_WIP_JOB_PVT.MTL_TXN_DTLS_TBL_TYPE;
875 l_op_created VARCHAR2(10);
876
877
878 CURSOR c_repair_line_info(p_repair_line_id IN NUMBER) IS
879 select inventory_item_id, unit_of_measure, quantity, serial_number, inventory_org_id
880 from csd_repairs
881 where repair_line_id = p_repair_line_id;
882
883 CURSOR c_count_material(p_wip_entity_id NUMBER, l_inventory_item_id NUMBER) IS
884 select 'X'
885 from wip_requirement_operations_v
886 where wip_entity_id = p_wip_entity_id
887 and inventory_item_id = l_inventory_item_id
888 and rownum = 1;
889
890
891 -- Cursor to select the item attributes serial control code and
892 -- lot control code.
893 CURSOR cur_get_item_attribs (
894 p_org_id NUMBER,
895 p_item_id NUMBER
896 )
897 IS
898 SELECT serial_number_control_code
899 FROM mtl_system_items
900 WHERE organization_id = p_org_id AND inventory_item_id = p_item_id;
901
902
903 Cursor c_get_serial_info(p_item_id number, p_serial_number varchar2, p_org_id number) is
904 select current_status, current_subinventory_code from mtl_serial_numbers
905 where inventory_item_id = p_item_id and serial_number = p_serial_number and current_organization_id = p_org_id;
906
907
908 Cursor c_get_min_operation_seq(p_wip_entity_id number) is
909 select min(operation_seq_num) from wip_operations_v where wip_entity_id = p_wip_entity_id;
910
911 l_inventory_item_id NUMBER;
912 l_unit_of_measure VARCHAR2(3);
913 l_quantity NUMBER;
914 l_serial_number VARCHAR2(30);
915 l_inventory_org_id NUMBER;
916 l_subinventory VARCHAR2(30);
917 l_dummy VARCHAR2(1) := null;
918 l_serial_control_code NUMBER;
919 l_current_status NUMBER;
920 l_current_subinventory_code VARCHAR2(10);
921 l_operation_seq_num NUMBER;
922 l_num_other_jobs NUMBER :=0; -- swai: bug 7477845/7483291
923
924 BEGIN
925
926 IF ( l_proc_level >= l_debug_level ) THEN
927 FND_LOG.STRING( l_proc_level,
928 l_mod_name||'begin',
929 'Entering Private API submit_jobs');
930 END IF;
931
932 -- Standard call to check for call compatibility
933 IF Not FND_API.COMPATIBLE_API_CALL( l_api_version,
934 p_api_version,
935 l_api_name,
936 G_PKG_NAME) THEN
937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
938 END If;
939
940 -- Initialize message list if p_init_msg_list is set to TRUE
941 IF FND_API.to_boolean(p_init_msg_list) THEN
942 FND_MSG_PUB.initialize;
943 END IF;
944
945 -- Initialize API return status to success
946 x_return_status:=FND_API.G_RET_STS_SUCCESS;
947
948
949 -- Verify that the Standard and Internal Concurrent Managers are UP
950
951 verify_conc_manager_status ( x_return_status => x_return_status );
952
953 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
955 END IF;
956
957 l_default_ro_item := nvl(FND_PROFILE.VALUE('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'), 'N');
958
959 -- Get the Group_id to be used for WIP Mass Load, All the records inserted into
960 -- wip_job_schedule_interface have the same group_id , so that one WIP Mass Load
961 -- request can process all the records
962
963 if (l_default_ro_item = 'N') then
964 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
965
966 else
967 OPEN c_repair_line_info(p_repair_line_id);
968 FETCH c_repair_line_info into
969 l_inventory_item_id,
970 l_unit_of_measure,
971 l_quantity,
972 l_serial_number,
973 l_inventory_org_id;
974 CLOSE c_repair_line_info;
975 l_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
976
977
978 --Get serial number control code and lot control code
979 OPEN cur_get_item_attribs (l_inventory_org_id,
980 l_inventory_item_id);
981
982 FETCH cur_get_item_attribs
983 INTO l_serial_control_code;
984 CLOSE cur_get_item_attribs;
985
986
987 IF l_serial_control_code IN (2, 5) then
988 OPEN c_get_serial_info (l_inventory_item_id, l_serial_number, l_inventory_org_id);
989 FETCH c_get_serial_info
990 INTO l_current_status,l_current_subinventory_code;
991 CLOSE c_get_serial_info;
992 --current status = 3 is valid serial number
993 if (l_current_status = 3) then
994 l_subinventory := l_current_subinventory_code;
995 else
996 l_serial_number := null;
997 end if;
998 else
999 --don't pass the serial number, it is not valid serial number
1000 l_serial_number := null;
1001 end if;
1002
1003 End if;
1004
1005
1006 IF p_x_job_bill_routing_tbl.COUNT = 0 THEN
1007
1008 if (l_default_ro_item = 'Y') then
1009 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
1010 End if;
1011
1012 p_x_job_bill_routing_tbl(0).group_id := l_group_id;
1013
1014 -- The bill_routings table is empty, Generate the job name using the
1015 -- job_prefix passed in. Here no bills and routings are passed in and only one job
1016 -- will be created for the passed in Job header information.
1017
1018
1019 generate_job_name ( p_job_prefix => p_job_header_rec.job_prefix,
1020 p_organization_id => p_job_header_rec.organization_id,
1021 x_job_name => l_job_bill_routing_rec.job_name );
1022
1023
1024 -- Assign the generated Job name to the first record in job_bill_routing_tbl to be passed back
1025 -- to the calling program. This is passed to the insert_job_header procedure as well.
1026
1027 p_x_job_bill_routing_tbl(0).job_name := l_job_bill_routing_rec.job_name;
1028
1029 IF ( l_event_level >= l_debug_level ) then
1030 FND_LOG.STRING( l_event_level,
1031 l_mod_name||'beforecallinsert',
1032 'Just before calling insert_job_header');
1033 END IF;
1034
1035
1036 -- Call procedure to insert job header and job name information
1037 -- into wip_job_schedule_interface table
1038
1039
1040 insert_job_header( p_job_header_rec => p_job_header_rec,
1041 p_job_bill_routing_rec => l_job_bill_routing_rec,
1042 p_group_id => l_group_id,
1043 x_return_status => x_return_status );
1044
1045
1046 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1048 END IF;
1049
1050 if (l_default_ro_item = 'Y') then
1051
1052 --This code is change to online create the wipjob without use the concurrent program.
1053 WIP_MASSLOAD_PUB.createOneJob( p_interfaceID => l_group_id,
1054 p_validationLevel => p_validation_level,
1055 x_wipEntityID => l_wip_entity_id,
1056 x_returnStatus => x_return_status,
1057 x_errorMsg => x_msg_data );
1058
1059 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1060 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
1061 FND_MSG_PUB.ADD;
1062 RAISE FND_API.G_EXC_ERROR;
1063 end if;
1064
1065 COMMIT;
1066
1067 -- swai: bug 7477845/7483291
1068 -- check if there another job existing for this RO. If so, do not default
1069 -- the RO item as a material. Must compare we.wip_entity_id since
1070 -- crj.wip_entity_id may be null (until wip_update is done).
1071 select count(*)
1072 into l_num_other_jobs
1073 from csd_repair_job_xref crj,
1074 wip_entities we
1075 where crj.job_name = we.wip_entity_name
1076 and crj.organization_id = we.organization_id
1077 and crj.repair_line_id = p_repair_line_id
1078 and we.wip_entity_id <> l_wip_entity_id;
1079
1080 l_dummy := null;
1081 OPEN c_count_material(l_wip_entity_id, l_inventory_item_id);
1082 FETCH c_count_material into l_dummy;
1083 CLOSE c_count_material;
1084
1085
1086 if (l_dummy is null) and (l_num_other_jobs = 0) then
1087 --Default Repair Item as Material on Job
1088 l_mtl_txn_dtls_tbl.delete;
1089
1090 l_mtl_txn_dtls_tbl(0).INVENTORY_ITEM_ID :=l_inventory_item_id;
1091 l_mtl_txn_dtls_tbl(0).WIP_ENTITY_ID :=l_wip_entity_id;
1092 l_mtl_txn_dtls_tbl(0).ORGANIZATION_ID :=l_inventory_org_id;
1093 l_mtl_txn_dtls_tbl(0).OPERATION_SEQ_NUM :=1;
1094 l_mtl_txn_dtls_tbl(0).TRANSACTION_QUANTITY :=l_quantity; --repair order qty
1095 l_mtl_txn_dtls_tbl(0).TRANSACTION_UOM :=l_unit_of_measure; --Repair order UOM
1096 l_mtl_txn_dtls_tbl(0).SERIAL_NUMBER :=l_serial_number;
1097 l_mtl_txn_dtls_tbl(0).SUPPLY_SUBINVENTORY :=l_subinventory;
1098 l_mtl_txn_dtls_tbl(0).OBJECT_VERSION_NUMBER := 1;
1099 l_mtl_txn_dtls_tbl(0).NEW_ROW := 'Y';
1100
1101
1102 -- call API to create Repair Actuals header
1103 CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_MTL_TXN_DTLS
1104 ( p_api_version_number => 1.0,
1105 p_init_msg_list => 'T',
1106 p_commit => 'F',
1107 p_validation_level => 1,
1108 p_mtl_txn_dtls_tbl => l_mtl_txn_dtls_tbl,
1109 x_op_created => l_op_created,
1110 x_return_status => x_return_status,
1111 x_msg_count => x_msg_count,
1112 x_msg_data => x_msg_data);
1113 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1114 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
1115 FND_MSG_PUB.ADD;
1116 RAISE FND_API.G_EXC_ERROR;
1117 END IF;
1118 end if;
1119
1120 End if;
1121
1122 ELSE
1123
1124 -- The bill_routings table is not empty
1125 -- Process each bill routng record passed in to generate unique job names for each job being submitted,
1126 -- if needed.
1127 -- Check each record to see if the job_name is already passed in,
1128 -- If it is passed in, it is validated for uniqueness and an error is raised if not unique and we exit out of
1129 -- the API.
1130 -- If job_name is not passed in, then the job_name is generated
1131 -- by appending a sequence generated number to the job_prefix specified in the job header record.
1132 -- For each bill, routings record, once a job name is found or generated, procedure insert_job_header is called
1133 -- to insert the header, bills and routings information into the WIP interface table.
1134
1135 -- Note, for now, a job_name is passed in, only when one Job is submitted and the profile
1136 -- 'Use CSD as Job Prefix' is set to 'N'. However this API supports job names
1137 -- to be passed in, when more than one jobs are submitted.
1138
1139
1140 FOR rt_ctr in p_x_job_bill_routing_tbl.FIRST.. p_x_job_bill_routing_tbl.LAST
1141
1142 LOOP
1143
1144 if (l_default_ro_item = 'Y') then
1145 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
1146 End if;
1147
1148 p_x_job_bill_routing_tbl(rt_ctr).group_id := l_group_id;
1149
1150 -- Populate the bill, routing record variable to be passed to procedure insert_job_header
1151
1152 l_job_bill_routing_rec := p_x_job_bill_routing_tbl(rt_ctr) ;
1153
1154
1155 IF l_job_bill_routing_rec.job_name is not NULL then
1156
1157 -- job name is passed in, validate it for Uniqueness,
1158 -- Check if it already exists in WIP_ENTITIES or WIP interface table
1159
1160 validate_job_name ( p_job_name => l_job_bill_routing_rec.Job_Name,
1161 p_organization_id => p_job_header_rec.organization_id,
1162 x_return_status => x_return_status ) ;
1163
1164
1165 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1166
1167 -- Job name either exists in WIP_ENTITIES or in the interface table for the specified
1168 -- organization, So raise an error and exit
1169
1170 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_NAME_EXISTS');
1171 FND_MESSAGE.SET_TOKEN('JOB_NAME', l_job_bill_routing_rec.job_name );
1172 FND_MSG_PUB.ADD;
1173 RAISE FND_API.G_EXC_ERROR;
1174 end if;
1175
1176 ELSE
1177
1178 -- job name is not passed in , generate the Job Name
1179
1180 generate_job_name ( p_job_prefix => p_job_header_rec.job_prefix,
1181 p_organization_id => p_job_header_rec.organization_id,
1182 x_job_name => l_job_bill_routing_rec.job_name );
1183
1184
1185
1186 -- Assign the generated Job name to the current record in job_bill_routing_tbl to be passed
1187 -- to procedure insert_job_header and is also passed back
1188 -- to the calling program.
1189
1190 p_x_job_bill_routing_tbl(rt_ctr).job_name := l_job_bill_routing_rec.job_name;
1191
1192 END IF;
1193
1194
1195 IF ( l_event_level >= l_debug_level ) then
1196 FND_LOG.STRING( l_event_level,
1197 l_mod_name||'beforecallinsert',
1198 'Just before calling insert_job_header');
1199 END IF;
1200
1201 -- Call procedure to insert job header and bills, routing information
1202 -- into wip_job_schedule_interface table
1203 -- All the records inserted into the WIP interface table
1204 -- are passed the same group_id and hence will be processed by one WIP Mass Load
1205 -- request.
1206
1207 insert_job_header( p_job_header_rec => p_job_header_rec,
1208 p_job_bill_routing_rec => l_job_bill_routing_rec,
1209 p_group_id => l_group_id,
1210 x_return_status => x_return_status );
1211
1212
1213 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1215 END IF;
1216
1217 if (l_default_ro_item = 'Y') then
1218
1219 --This code is change to online create the wipjob without use the concurrent program.
1220 WIP_MASSLOAD_PUB.createOneJob( p_interfaceID => l_group_id,
1221 p_validationLevel => p_validation_level,
1222 x_wipEntityID => l_wip_entity_id,
1223 x_returnStatus => x_return_status,
1224 x_errorMsg => x_msg_data );
1225
1226 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1227 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
1228 FND_MSG_PUB.ADD;
1229 RAISE FND_API.G_EXC_ERROR;
1230 end if;
1231
1232 COMMIT;
1233
1234 -- swai: bug 7477845/7483291
1235 -- check if there another job existing for this RO. If so, do not default
1236 -- the RO item as a material. Must compare we.wip_entity_id since
1237 -- crj.wip_entity_id may be null (until wip_update is done).
1238 select count(*)
1239 into l_num_other_jobs
1240 from csd_repair_job_xref crj,
1241 wip_entities we
1242 where crj.job_name = we.wip_entity_name
1243 and crj.organization_id = we.organization_id
1244 and crj.repair_line_id = p_repair_line_id
1245 and we.wip_entity_id <> l_wip_entity_id;
1246 -- More than one bill/route could have been submitted at a time.
1247 -- These jobs are processed all at once before rows are inserted into
1248 -- csd_repair_job_xref. Need to count these jobs as well.
1249 l_num_other_jobs := l_num_other_jobs + rt_ctr - p_x_job_bill_routing_tbl.FIRST;
1250 -- end swai: bug 7477845/7483291
1251
1252 l_dummy := null;
1253 OPEN c_count_material(l_wip_entity_id, l_inventory_item_id);
1254 FETCH c_count_material into l_dummy;
1255 CLOSE c_count_material;
1256
1257 if (l_dummy is null) and (l_num_other_jobs = 0) then
1258
1259 --Default Repair Item as Material on Job
1260 l_mtl_txn_dtls_tbl.delete;
1261
1262 OPEN c_get_min_operation_seq(l_wip_entity_id);
1263 FETCH c_get_min_operation_seq into l_operation_seq_num;
1264 CLOSE c_get_min_operation_seq;
1265
1266 if (l_operation_seq_num is null) then
1267 l_operation_seq_num := 1;
1268 end if;
1269
1270 l_mtl_txn_dtls_tbl(0).INVENTORY_ITEM_ID :=l_inventory_item_id;
1271 l_mtl_txn_dtls_tbl(0).WIP_ENTITY_ID :=l_wip_entity_id;
1272 l_mtl_txn_dtls_tbl(0).ORGANIZATION_ID :=l_inventory_org_id;
1273
1274 if (l_job_bill_routing_rec.routing_reference_id is null) then
1275
1276 l_mtl_txn_dtls_tbl(0).OPERATION_SEQ_NUM :=1;
1277 else
1278 l_mtl_txn_dtls_tbl(0).OPERATION_SEQ_NUM :=l_operation_seq_num;
1279 end if;
1280 l_mtl_txn_dtls_tbl(0).TRANSACTION_QUANTITY :=l_quantity; --repair order qty
1281 l_mtl_txn_dtls_tbl(0).TRANSACTION_UOM :=l_unit_of_measure; --Repair order UOM
1282 l_mtl_txn_dtls_tbl(0).SERIAL_NUMBER :=l_serial_number;
1283 l_mtl_txn_dtls_tbl(0).SUPPLY_SUBINVENTORY :=l_subinventory;
1284 l_mtl_txn_dtls_tbl(0).OBJECT_VERSION_NUMBER := 1;
1285 l_mtl_txn_dtls_tbl(0).NEW_ROW := 'Y';
1286
1287
1288 -- call API to create Repair Actuals header
1289 CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_MTL_TXN_DTLS
1290 ( p_api_version_number => 1.0,
1291 p_init_msg_list => 'T',
1292 p_commit => 'F',
1293 p_validation_level => 1,
1294 p_mtl_txn_dtls_tbl => l_mtl_txn_dtls_tbl,
1295 x_op_created => l_op_created,
1296 x_return_status => x_return_status,
1297 x_msg_count => x_msg_count,
1298 x_msg_data => x_msg_data);
1299 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1300 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
1301 FND_MSG_PUB.ADD;
1302 RAISE FND_API.G_EXC_ERROR;
1303 END IF;
1304 end if;
1305 -- COMMIT;
1306 End if;
1307
1308
1309 END LOOP ;
1310
1311 END IF;
1312
1313
1314 -- submit request for 'Depot Repair WIP Job Submission' concurrent program, which in turn
1315 -- submits WIP Mass Load, waits for it to complete and then calls the WIP UPDATE
1316 -- program.
1317 -- Here the repair_line_id specified is used to run the WIP Update program for
1318 -- the specified repair_line_id. If Jobs are submitted for more than one repair
1319 -- order, then p_repair_line_id is NULL, In this case, the WIP Update program runs for
1320 -- all eligible repair orders.
1321
1322 if (l_default_ro_item = 'N') then
1323 x_request_id := fnd_request.submit_request (
1324 application => 'CSD',
1325 program => 'CSDJSWIP',
1326 description => NULL,
1327 start_time => NULL,
1328 sub_request => FALSE,
1329 argument1 => TO_CHAR(l_group_id),
1330 argument2 => p_repair_line_id ) ;
1331
1332 IF ( l_stat_level >= l_debug_level ) then
1333 FND_LOG.STRING( l_stat_level,
1334 l_mod_name||'submitdata',
1335 'When calling submit_request, the group_id is '||to_char(l_group_id));
1336 END IF;
1337
1338
1339 IF ( x_request_id = 0 ) THEN
1340
1341 -- request submission failed,
1342 -- add the error message to the message list and exit
1343
1344 FND_MESSAGE.SET_NAME('CSD','CSD_CSDJSWIP_SUBMIT_FAILURE');
1345 FND_MSG_PUB.ADD;
1346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1347
1348
1349 IF ( l_stat_level >= l_debug_level ) then
1350 FND_LOG.STRING( l_stat_level,
1351 l_mod_name||'requestfail',
1352 'Submit request failed');
1353 END IF;
1354
1355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1356
1357 ELSE
1358 --This commit is a must for the successful submission of the concurrent request above
1359 COMMIT;
1360 -- Populate the Out parameter x_group_id
1361 x_group_id := l_group_id;
1362
1363 END IF;
1364 END IF;
1365
1366 COMMIT;
1367
1368 IF ( l_proc_level >= l_debug_level ) then
1369 FND_LOG.STRING( l_proc_level,
1370 l_mod_name||'end',
1371 'Leaving Private API submit_jobs');
1372 END IF;
1373
1374 EXCEPTION
1375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1376 ROLLBACK ;
1377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1378 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1379 p_count => x_msg_count,
1380 p_data => x_msg_data);
1381
1382 IF ( FND_LOG.LEVEL_EXCEPTION >= l_debug_level ) then
1383 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
1384 l_mod_name||'unx_exception',
1385 'G_EXC_UNEXPECTED_ERROR Exception');
1386 END IF;
1387
1388
1389 WHEN FND_API.G_EXC_ERROR THEN
1390 ROLLBACK ;
1391 x_return_status := FND_API.G_RET_STS_ERROR;
1392 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1393 p_count => x_msg_count,
1394 p_data => x_msg_data);
1395
1396 IF ( FND_LOG.LEVEL_ERROR >= l_debug_level ) then
1397 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
1398 l_mod_name||'exc_exception',
1399 'G_EXC_ERROR Exception');
1400 END IF;
1401
1402 WHEN OTHERS THEN
1403 ROLLBACK ;
1404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1405
1406 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1407
1408 -- Add Unexpected Error to Message List, here SQLERRM is used for
1409 -- getting the error
1410
1411 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'CSD_WIP_JOB_PVT',
1412 p_procedure_name => 'submit_jobs');
1413 END IF;
1414
1415 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1416 p_count => x_msg_count,
1417 p_data => x_msg_data);
1418
1419 IF ( FND_LOG.LEVEL_EXCEPTION >= l_debug_level ) then
1420 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
1421 l_mod_name||'others_exception',
1422 'OTHERS Exception');
1423 END IF;
1424
1425 END submit_jobs;
1426
1427
1428
1429
1430 -- This is the executable procedure for concurrent program
1431 -- 'Depot Repair WIP Job Submission'. This program is submitted from
1432 -- submit_jobs API.
1433 -- This procedure submits WIP Mass Load, waits for it to complete successfully,
1434 -- then calls the WIP_Update API to associate new records
1435 -- created in csd_repair_job_xref table with corresponding newly
1436 -- created wip_entity_ids.
1437 -- This concurrent program is passed in group_id and repair_line_id as
1438 -- parameters. If repair_line_id is null, then the WIP Update program is
1439 -- run for all the eligible repair orders, otherwise the WIP Update porgram
1440 -- is run for the specified repair_line_id.
1441
1442
1443 procedure submit_wip_mass_load_conc
1444 (
1445 errbuf OUT NOCOPY VARCHAR2,
1446 retcode OUT NOCOPY VARCHAR2,
1447 p_group_id IN NUMBER,
1448 p_repair_line_id IN NUMBER
1449 )
1450 IS
1451
1452 -- Declare the constants
1453
1454 l_api_version CONSTANT NUMBER := 1.0;
1455 l_procedure_name CONSTANT VARCHAR2(30) := 'Submit_Wip_Mass_Load_Conc' ;
1456
1457 -- Used for standard concurrent progam parameter 'retcode' value
1458 l_success CONSTANT NUMBER := 0;
1459 l_warning CONSTANT NUMBER := 1;
1460 l_error CONSTANT NUMBER := 2;
1461
1462 -- Parameter to WIP Mass Load Concurrent program, specifying full validation
1463 l_full_validation CONSTANT NUMBER := 0;
1464
1465 -- Concurrenr Request Id
1466 l_req_id NUMBER;
1467
1468 -- used for checking the success or failure of call to wait_for_request procedure
1469 l_boolvar BOOLEAN;
1470
1471 -- Concurrent Request Phase, status, message
1472 l_phase VARCHAR2(80);
1473 l_status VARCHAR2(80);
1474 l_dev_phase VARCHAR2(80);
1475 l_dev_status VARCHAR2(80);
1476 l_message VARCHAR2(255);
1477
1478 l_msg_count NUMBER;
1479 l_msg_data VARCHAR2(2000);
1480 l_return_status VARCHAR2(1);
1481
1482 -- variables used for FND_LOG debug messages
1483
1484 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1485 l_stat_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1486 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1487 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1488 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.submit_wip_mass_load_conc.';
1489
1490
1491
1492
1493 BEGIN
1494
1495 IF ( l_proc_level >= l_debug_level ) then
1496 FND_LOG.STRING( l_proc_level,
1497 l_mod_name||'begin',
1498 'Entering procedure submit_wip_mass_load_conc' );
1499 END IF;
1500
1501
1502 -- submit concurrent request for WIP Mass Load
1503
1504 -- argument3 specifies whether to print report or not, which when passed in as NULL
1505 -- , defaults to the value of 'Yes'.
1506 -- argument2 specifies the validation level for WIP Mass Load
1507 -- argument1 is the group_id
1508
1509 l_req_id := fnd_request.submit_request (
1510 application => 'WIP',
1511 program => 'WICMLP',
1512 description => NULL,
1513 start_time => NULL,
1514 sub_request => FALSE,
1515 argument1 => TO_CHAR(p_group_id),
1516 argument2 => l_full_validation,
1517 argument3 => NULL );
1518
1519
1520
1521 -- If request submission fails, raise an error
1522
1523 IF (l_req_id = 0 ) THEN
1524
1525 l_msg_data := FND_MESSAGE.GET_STRING('CSD','CSD_WICMLP_SUBMIT_FAILURE');
1526 fnd_file.put_line(fnd_file.log, l_msg_data );
1527
1528 IF ( l_stat_level >= l_debug_level ) then
1529 FND_LOG.STRING( l_stat_level,
1530 l_mod_name||'requestfailure',
1531 'WIP Mass Load Submit request failed' );
1532 END IF;
1533
1534 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1535
1536 ELSE
1537
1538 COMMIT; --This commit is a must for the completion of the concurrent request submission
1539
1540 IF ( l_stat_level >= l_debug_level ) then
1541 FND_LOG.STRING( l_stat_level,
1542 l_mod_name||'beforewait',
1543 'After commit and before wait for request' );
1544 END IF;
1545
1546 -- wait for the execution result of WIP Mass Load
1547
1548 -- Interval is specified in seconds and is the number of seconds to wait between checks,
1549 -- max_wait is also specified in seconds and will wait indefinitely, when specified as 0,
1550 -- dev_phase and dev_status are the developer versions of phase and status, which can be used
1551 -- for logic comparisons
1552
1553 l_boolvar:= fnd_concurrent.wait_for_request
1554 (
1555 request_id => l_req_id,
1556 interval => 15,
1557 max_wait => 0,
1558 phase => l_phase,
1559 status => l_status,
1560 dev_phase => l_dev_phase,
1561 dev_status => l_dev_status,
1562 message => l_message);
1563
1564 -- If wait for WIP Mass Load request fails, raise an error
1565
1566 IF NOT l_boolvar THEN
1567
1568 l_msg_data := FND_MESSAGE.GET_STRING('CSD','CSD_WICMLP_WAIT_FAILURE');
1569 fnd_file.put_line(fnd_file.log, l_msg_data );
1570
1571
1572 IF ( l_stat_level >= l_debug_level ) then
1573 FND_LOG.STRING( l_stat_level,
1574 l_mod_name||'waitfailure',
1575 'Wait for request failed');
1576 END IF;
1577
1578 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1579
1580
1581 ELSIF (l_dev_phase = 'COMPLETE' AND l_dev_status = 'NORMAL') THEN
1582
1583 -- WIP Mass Load completed successfully
1584
1585 IF ( l_event_level >= l_debug_level ) then
1586 FND_LOG.STRING( l_event_level,
1587 l_mod_name||'beforeupdatecall',
1588 'Before Call to depot_wip_update');
1589 END IF;
1590
1591
1592 -- Call the WIP Update program
1593
1594 -- When Repair Jobs are submitted to WIP Mass Load, a record is inserted into
1595 -- CSD_REPAIR_JOB_XREF for each combination of repair_line_id and repair Job.
1596 -- Once WIP Mass Load successfully completes, WIP_UPDATE API is called here to update
1597 -- the newly inserted records in CSD_REPAIR_JOB_XREF with the wip_entity_id of the
1598 -- corresponding jobs from WIP.
1599
1600 -- Here p_upd_job_completion is specified as 'N'
1601 -- so that only the WIP Creation Update program is run, the WIP Completion Update program
1602 -- is not run in this case.
1603
1604 -- If p_repair_line_id is passed in as NULL, then WIP_UPDATE is run for all the
1605 -- eligible repair_line_id values. When Repair Jobs are submitted for more than
1606 -- one repair order, then this is the case, that is, p_repair_line_id is null.
1607
1608 CSD_UPDATE_PROGRAMS_PVT.WIP_UPDATE
1609 ( p_api_version => l_api_version,
1610 p_commit => FND_API.G_TRUE,
1611 p_init_msg_list => FND_API.G_TRUE,
1612 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1613 x_return_status => l_return_status,
1614 x_msg_count => l_msg_count,
1615 x_msg_data => l_msg_data,
1616 p_upd_job_completion => 'N',
1617 p_repair_line_id => p_repair_line_id );
1618
1619 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1620
1621 -- If return status is not success, write the error messages to
1622 -- the concurrent request log file and raise exception to exit
1623
1624 IF l_msg_count = 1 THEN
1625
1626 fnd_file.put_line( fnd_file.log,l_msg_data);
1627
1628 ELSIF l_msg_count > 1 THEN
1629
1630 -- If the message count is greater than 1, loop through the
1631 -- message list, retrieve the messages and write it to the log file
1632
1633 FOR l_msg_ctr IN 1..l_msg_count
1634 LOOP
1635 l_msg_data := fnd_msg_pub.get(l_msg_ctr, FND_API.G_FALSE );
1636 fnd_file.put_line( fnd_file.log, l_msg_data);
1637 END LOOP;
1638
1639 END IF;
1640
1641 IF ( l_stat_level >= l_debug_level ) then
1642 FND_LOG.STRING( l_stat_level,
1643 l_mod_name||'updatecallerror',
1644 'CSD_UPDATE_PROGRAMS_PVT.WIP_UPDATE call returned error');
1645 END IF;
1646
1647 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1648
1649 ELSE
1650
1651 -- If return status is success, return concurrent program success code
1652
1653 errbuf := '';
1654 retcode := l_success;
1655
1656 END IF;
1657
1658
1659 ELSE
1660
1661 -- WIP Mass Load did not complete successfully, write error message to log file
1662 -- and raise exception to exit
1663
1664 l_msg_data := FND_MESSAGE.GET_STRING('CSD','CSD_WICMLP_COMPLETION_FAILURE');
1665 fnd_file.put_line(fnd_file.log,l_msg_data );
1666
1667 IF ( l_stat_level >= l_debug_level ) then
1668 FND_LOG.STRING( l_stat_level,
1669 l_mod_name||'completionfailure',
1670 'WIP Mass Load did not Complete Successfully');
1671 END IF;
1672
1673 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1674
1675 END IF;
1676 END IF;
1677
1678 IF ( l_proc_level >= l_debug_level ) then
1679 FND_LOG.STRING( l_proc_level,
1680 l_mod_name||'end',
1681 'Leaving procedure submit_wip_mass_load_conc');
1682 END IF;
1683
1684
1685 EXCEPTION
1686
1687
1688 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1689
1690 -- write message to log file indicating the failure of the concurrent program,
1691 -- return error retcode
1692
1693 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_CSDJSWIP_FAILURE');
1694 retcode := l_error;
1695
1696 WHEN FND_API.G_EXC_ERROR THEN
1697
1698 -- write message to log file indicating the failure of the concurrent program,
1699 -- return error retcode
1700
1701 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_CSDJSWIP_FAILURE');
1702 retcode := l_error;
1703
1704 WHEN OTHERS THEN
1705
1706 -- Add Unexpected Error to Message List, here SQLERRM is used for
1707 -- getting the error
1708
1709 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME ,
1710 p_procedure_name => l_procedure_name );
1711
1712 -- Get the count of the Messages from the message list, if the count is 1
1713 -- get the message as well
1714
1715 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1716 p_count => l_msg_count,
1717 p_data => l_msg_data);
1718
1719 IF l_msg_count = 1 THEN
1720
1721 fnd_file.put_line( fnd_file.log, l_msg_data);
1722
1723 ELSIF l_msg_count > 1 THEN
1724
1725 -- If the message count is greater than 1, loop through the
1726 -- message list, retrieve the messages and write it to the log file
1727
1728 FOR l_msg_ctr IN 1..l_msg_count
1729 LOOP
1730 l_msg_data := fnd_msg_pub.get(l_msg_ctr, FND_API.G_FALSE );
1731 fnd_file.put_line( fnd_file.log, l_msg_data);
1732 END LOOP;
1733
1734 END IF;
1735
1736 -- write message to log file indicating the failure of the concurrent program,
1737 -- return error retcode
1738
1739 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_CSDJSWIP_FAILURE');
1740 retcode := l_error ;
1741
1742 IF ( FND_LOG.LEVEL_EXCEPTION >= l_debug_level ) then
1743 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
1744 l_mod_name||'others_exception',
1745 'OTHERS Exception');
1746 END IF;
1747
1748 end submit_wip_mass_load_conc;
1749
1750
1751
1752
1753 -- This function checks if the discrete manufacturing patchset level is
1754 -- at j or beyond and if so, returns true. For now, this is used from
1755 -- Repair Jobs tab, when COMPLETE_JOB button is pressed. If the patchset level
1756 -- is at j or beyond, then the new WIP Completion form is called, hence
1757 -- new parameters are passed, If not, the old WIP Completion form is called, hence
1758 -- the new parameters are not passed. The new parameters are wip_entity_id and
1759 -- transaction_quantity which are used to default the WIP job information, when the
1760 -- WIP Completion form opens.
1761
1762 FUNCTION is_dmf_patchset_level_j RETURN BOOLEAN IS
1763 BEGIN
1764 IF (wip_constants.dmf_patchset_level >= wip_constants.dmf_patchset_j_value) THEN
1765 RETURN TRUE;
1766 ELSE
1767 RETURN FALSE;
1768 END IF;
1769 END;
1770
1771
1772 -- This procedure creates WIP jobs from estimates tab. The procedure defaults the job header,
1773 -- checks if the repair estimate has a routing associated, if so passes in the routing information
1774 -- to the WIP API. When successfully finished, the procedure will return WIP Entity ID and Job Name
1775 -- to the calling routine.
1776 -- 12.1 create job from estimates -- subhat
1777
1778 PROCEDURE create_job_from_estimate(
1779 p_api_version_number IN NUMBER,
1780 p_init_msg_list IN VARCHAR2 ,
1781 p_commit IN VARCHAR2 ,
1782 p_validation_level IN NUMBER,
1783 x_return_status OUT NOCOPY VARCHAR2,
1784 x_msg_count OUT NOCOPY NUMBER,
1785 x_msg_data OUT NOCOPY VARCHAR2,
1786 x_job_name OUT NOCOPY VARCHAR2,
1787 x_wip_entity_id OUT NOCOPY NUMBER,
1788 p_ESTM_JOB_DETLS_REC_TYPE IN ESTM_JOB_DETLS_REC_TYPE
1789 ) IS
1790
1791 -- Job Record to hold the Job header, bills and routing information being inserted
1792 -- into wip_job_schedule_interface
1793
1794 l_job_header_rec wip_job_schedule_interface%ROWTYPE;
1795
1796 lc_api_name CONSTANT VARCHAR2(30) := 'CREATE_WIP_JOB';
1797 lc_api_version_number CONSTANT NUMBER := 1.0;
1798
1799 -- WIP Job Status Lookup Codes for Released and Unreleased status, --- The Lookup Type is WIP_JOB_STATUS
1800
1801 lc_released_status_code CONSTANT NUMBER := 3;
1802 lc_unreleased_status_code CONSTANT NUMBER := 1;
1803
1804 -- Non Standard Discrete Job Load Type
1805 lc_non_standard_load_type CONSTANT NUMBER := 4;
1806
1807
1808 -- Constants used for FND_LOG debug messages
1809
1810 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.create_job_from_estimate';
1811
1812
1813
1814 l_user_id NUMBER;
1815 l_repair_xref_id NUMBER;
1816 l_rep_hist_id NUMBER;
1817
1818 l_job_prefix VARCHAR2(80);
1819 -- l_wip_entity_id NUMBER;
1820
1821
1822
1823 BEGIN
1824
1825 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1826 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1827 lc_mod_name||'begin',
1828 'Entering Private API create_job_from_estimate');
1829 END IF;
1830
1831 -- Standard Start of API savepoint
1832 SAVEPOINT CREATE_WIP_JOB_PVT;
1833 -- Standard call to check for call compatibility.
1834 IF NOT FND_API.Compatible_API_Call
1835 (lc_api_version_number,
1836 p_api_version_number,
1837 lc_api_name,
1838 G_PKG_NAME)
1839 THEN
1840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1841 END IF;
1842
1843 -- Initialize message list if p_init_msg_list is set to TRUE
1844 IF FND_API.to_boolean(p_init_msg_list) THEN
1845 FND_MSG_PUB.initialize;
1846 END IF;
1847
1848 -- Initialize API return status to success
1849 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1850
1851 -- initialize the job header rec.
1852
1853 l_job_header_rec.organization_id :=
1854 fnd_profile.value('CSD_DEF_REP_INV_ORG');
1855
1856 IF l_job_header_rec.organization_id is NULL THEN
1857
1858 FND_MESSAGE.SET_NAME('CSD','CSD_DEF_REP_INV_NULL');
1859 FND_MSG_PUB.ADD;
1860 RAISE FND_API.G_EXC_ERROR;
1861 END IF;
1862
1863
1864 l_job_prefix := fnd_profile.value('CSD_DEFAULT_JOB_PREFIX');
1865
1866 -- If l_job_prefix is null, throw an error and return;
1867
1868
1869 IF l_job_prefix IS NULL THEN
1870
1871 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_PREFIX_NULL');
1872 FND_MSG_PUB.ADD;
1873 RAISE FND_API.G_EXC_ERROR;
1874 END IF;
1875
1876 l_job_header_rec.class_code :=
1877 fnd_profile.value('CSD_DEF_WIP_ACCOUNTING_CLASS');
1878
1879 IF l_job_header_rec.class_code is NULL THEN
1880
1881 FND_MESSAGE.SET_NAME('CSD','CSD_CLASS_CODE_NULL');
1882 FND_MSG_PUB.ADD;
1883 RAISE FND_API.G_EXC_ERROR;
1884 END IF;
1885
1886 -- Assign the WIP Job Status lookup codes corresponding to Released -- and Unreleased Job status,
1887 -- to be passed for WIP Interface Table
1888
1889 IF fnd_profile.value('CSD_DEFAULT_JOB_STATUS') = 'RELEASED' THEN
1890
1891 l_job_header_rec.status_type := lc_released_status_code ;
1892
1893 ELSIF NVL( fnd_profile.value('CSD_DEFAULT_JOB_STATUS'), 'UNRELEASED' ) = 'UNRELEASED' THEN
1894
1895 l_job_header_rec.status_type := lc_unreleased_status_code;
1896 END IF;
1897
1898
1899 l_job_header_rec.load_type := lc_non_standard_load_type;
1900
1901 l_job_header_rec.first_unit_start_date := SYSDATE;
1902 l_job_header_rec.last_unit_completion_date := SYSDATE;
1903
1904 l_job_header_rec.start_quantity := p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity;
1905
1906 -- If the profile CSD: Default WIP MRP Net Qty to Zero is set to
1907 -- null / 'N' then net_quantity = start_quantity else if the
1908 -- profile is set to 'Y' then net_quantity = 0
1909 IF ( nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N') = 'N' ) THEN
1910 l_job_header_rec.net_quantity := p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity;
1911 ELSIF ( fnd_profile.value('CSD_WIP_MRP_NET_QTY') = 'Y' ) THEN
1912 l_job_header_rec.net_quantity := 0;
1913 END IF;
1914
1915
1916 l_job_header_rec.primary_item_id :=
1917 p_ESTM_JOB_DETLS_REC_TYPE.inventory_item_id ;
1918
1919
1920 -- Get the Group_id to be used for WIP Create Job,
1921
1922 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
1923
1924 l_job_header_rec.interface_id := l_job_header_rec.group_id;
1925
1926 generate_job_name ( p_job_prefix =>l_job_prefix,
1927 p_organization_id => l_job_header_rec.organization_id,
1928 x_job_name => l_job_header_rec.job_name );
1929
1930
1931 x_job_name := l_job_header_rec.job_name;
1932
1933 -- associate the projects integration parameters.
1934
1935 l_job_header_rec.project_id := p_ESTM_JOB_DETLS_REC_TYPE.project_id;
1936 l_job_header_rec.task_id := p_ESTM_JOB_DETLS_REC_TYPE.task_id;
1937 l_job_header_rec.end_item_unit_number := p_ESTM_JOB_DETLS_REC_TYPE.unit_number;
1938
1939 -- check if the estimate has a routing associated with it. If so, associate the routing information.
1940
1941 BEGIN
1942 SELECT assembly_item_id INTO l_job_header_rec.routing_reference_id
1943 FROM csd_repair_estimate cre,bom_operational_routings bor
1944 WHERE cre.repair_line_id = p_ESTM_JOB_DETLS_REC_TYPE.repair_line_id
1945 AND cre.routing_sequence_id = bor.routing_sequence_id;
1946 EXCEPTION
1947 WHEN NO_DATA_FOUND THEN
1948 NULL; -- do nothing. Routing information is not created for the job.
1949 END;
1950
1951 IF l_job_header_rec.routing_reference_id IS NOT NULL THEN
1952
1953 BEGIN
1954 SELECT completion_subinventory,
1955 completion_locator_id
1956 into l_job_header_rec.completion_subinventory,
1957 l_job_header_rec.completion_locator_id
1958 FROM
1959 bom_operational_routings where
1960 assembly_item_id = l_job_header_rec.routing_reference_id
1961 and organization_id = l_job_header_rec.organization_id
1962 and nvl( alternate_routing_designator , -1 ) =
1963 nvl( l_job_header_rec.alternate_routing_designator , -1) ;
1964
1965 EXCEPTION
1966 WHEN NO_DATA_FOUND THEN
1967 NULL;
1968 END;
1969 END IF;
1970
1971 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1972 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
1973 lc_mod_name||'beforecallinsert',
1974 'Just before calling insert_job_header');
1975 END IF;
1976
1977 -- Call procedure to insert job header and bills, routing
1978 -- information
1979 -- into wip_job_schedule_interface table
1980
1981
1982 insert_job_header( p_job_header_rec =>
1983 l_job_header_rec,
1984 x_return_status =>
1985 x_return_status );
1986
1987
1988 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1990 END IF;
1991
1992
1993 -- CALL WIP API to process records in wip interface table,
1994 --If API fails, Raise error, rollback and return
1995
1996 -- Call WIP Mass Load API
1997
1998 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1999 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2000 lc_mod_name||'beforecallcreateonejob',
2001 'Just before calling WIP_MASSLOAD_PUB.createOneJob');
2002 END IF;
2003
2004 WIP_MASSLOAD_PUB.createOneJob( p_interfaceID => l_job_header_rec.group_id,
2005 p_validationLevel => p_validation_level,
2006 x_wipEntityID => x_wip_entity_id,
2007 x_returnStatus => x_return_status,
2008 x_errorMsg => x_msg_data );
2009
2010
2011
2012
2013 If (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2014
2015 ROLLBACK to CREATE_WIP_JOB_PVT ;
2016 RETURN;
2017
2018 END IF;
2019 -- call procedures to insert a row in csd_repair_job_xref
2020 -- and csd_repair_history tables for the job created.
2021
2022 L_user_id := fnd_global.user_id;
2023
2024 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2025 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2026 lc_mod_name||'beforecallxrefwrite',
2027 'Just before calling csd_to_form_repair_job_xref.validate_and_write');
2028 END IF;
2029
2030
2031
2032 csd_to_form_repair_job_xref.validate_and_write(
2033 p_api_version_number => lc_api_version_number,
2034 p_init_msg_list => FND_API.G_FALSE,
2035 p_commit => FND_API.G_FALSE,
2036 p_validation_level => NULL,
2037 p_action_code => 0,
2038 px_repair_job_xref_id => l_repair_xref_id,
2039 p_created_by => l_user_id,
2040 p_creation_date => SYSDATE,
2041 p_last_updated_by => l_user_id,
2042 p_last_update_date => SYSDATE,
2043 p_last_update_login => l_user_id,
2044 p_repair_line_id => p_ESTM_JOB_DETLS_REC_TYPE.repair_line_id,
2045 p_wip_entity_id => x_wip_entity_id,
2046 p_group_id => l_job_header_rec.group_id,
2047 p_organization_id => l_job_header_rec.organization_id,
2048 p_quantity => p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity,
2049 p_INVENTORY_ITEM_ID => l_job_header_rec.primary_item_id,
2050 p_ITEM_REVISION => null,
2051 p_OBJECT_VERSION_NUMBER => NULL,
2052 p_attribute_category => NULL,
2053 p_attribute1 => NULL,
2054 p_attribute2 => NULL,
2055 p_attribute3 => NULL,
2056 p_attribute4 => NULL,
2057 p_attribute5 => NULL,
2058 p_attribute6 => NULL,
2059 p_attribute7 => NULL,
2060 p_attribute8 => NULL,
2061 p_attribute9 => NULL,
2062 p_attribute10 => NULL,
2063 p_attribute11 => NULL,
2064 p_attribute12 => NULL,
2065 p_attribute13 => NULL,
2066 p_attribute14 => NULL,
2067 p_attribute15 => NULL,
2068 p_quantity_completed => NULL,
2069 p_job_name => l_job_header_rec.job_name,
2070 p_source_type_code => 'MANUAL', -- bug fix 5763350
2071 p_source_id1 => NULL,
2072 p_ro_service_code_id => NULL,
2073 x_return_status => x_return_status,
2074 x_msg_count => x_msg_count,
2075 x_msg_data => x_msg_data);
2076
2077
2078 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2079 ROLLBACK to CREATE_WIP_JOB_PVT ;
2080
2081 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2082 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
2083 lc_mod_name||'exc_exception',
2084 'G_EXC_ERROR Exception');
2085 END IF;
2086
2087 RETURN;
2088
2089 END IF;
2090
2091
2092 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2093 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2094 lc_mod_name||'beforecallhistwrite',
2095 'Just before calling csd_to_form_repair_history.validate_and_write');
2096 END IF;
2097
2098
2099
2100 csd_to_form_repair_history.validate_and_write(
2101 p_api_version_number => lc_api_version_number,
2102 p_init_msg_list => FND_API.G_FALSE,
2103 p_commit => FND_API.G_FALSE,
2104 p_validation_level => NULL,
2105 p_action_code => 0,
2106 px_repair_history_id => l_rep_hist_id,
2107 p_OBJECT_VERSION_NUMBER => NULL,
2108 p_request_id => NULL,
2109 p_program_id => NULL,
2110 p_program_application_id => NULL,
2111 p_program_update_date => NULL,
2112 p_created_by => l_user_id,
2113 p_creation_date => SYSDATE,
2114 p_last_updated_by => l_user_id,
2115 p_last_update_date => SYSDATE,
2116 p_repair_line_id => p_ESTM_JOB_DETLS_REC_TYPE.repair_line_id,
2117 p_event_code => 'JS',
2118 p_event_date => SYSDATE,
2119 p_quantity => p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity,
2120 p_paramn1 => x_wip_entity_id,
2121 p_paramn2 => l_job_header_rec.organization_id,
2122 p_paramn3 => NULL,
2123 p_paramn4 => NULL,
2124 p_paramn5 => p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity,
2125 p_paramn6 => NULL,
2126 p_paramn8 => NULL,
2127 p_paramn9 => NULL,
2128 p_paramn10 => NULL,
2129 p_paramc1 => l_job_header_rec.job_name,
2130 p_paramc2 => NULL,
2131 p_paramc3 => NULL,
2132 p_paramc4 => NULL,
2133 p_paramc5 => NULL,
2134 p_paramc6 => NULL,
2135 p_paramc7 => NULL,
2136 p_paramc8 => NULL,
2137 p_paramc9 => NULL,
2138 p_paramc10 => NULL,
2139 p_paramd1 => NULL ,
2140 p_paramd2 => NULL ,
2141 p_paramd3 => NULL ,
2142 p_paramd4 => NULL ,
2143 p_paramd5 => SYSDATE,
2144 p_paramd6 => NULL ,
2145 p_paramd7 => NULL ,
2146 p_paramd8 => NULL ,
2147 p_paramd9 => NULL ,
2148 p_paramd10 => NULL ,
2149 p_attribute_category => NULL ,
2150 p_attribute1 => NULL ,
2151 p_attribute2 => NULL ,
2152 p_attribute3 => NULL ,
2153 p_attribute4 => NULL ,
2154 p_attribute5 => NULL ,
2155 p_attribute6 => NULL ,
2156 p_attribute7 => NULL ,
2157 p_attribute8 => NULL ,
2158 p_attribute9 => NULL ,
2159 p_attribute10 => NULL ,
2160 p_attribute11 => NULL ,
2161 p_attribute12 => NULL ,
2162 p_attribute13 => NULL ,
2163 p_attribute14 => NULL ,
2164 p_attribute15 => NULL ,
2165 p_last_update_login => l_user_id,
2166 x_return_status => x_return_status,
2167 x_msg_count => x_msg_count,
2168 x_msg_data => x_msg_data);
2169
2170 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2171 ROLLBACK to CREATE_WIP_JOB_PVT ;
2172
2173 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2174 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
2175 lc_mod_name||'exc_exception',
2176 'G_EXC_ERROR Exception');
2177 END IF;
2178
2179 RETURN;
2180
2181 END IF;
2182
2183 -- Standard check for p_commit
2184 IF FND_API.to_Boolean( p_commit )
2185 THEN
2186 COMMIT WORK;
2187 END IF;
2188
2189
2190 EXCEPTION
2191 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2192 ROLLBACK to CREATE_WIP_JOB_PVT ;
2193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2194
2195 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2196 p_count => x_msg_count,
2197 p_data => x_msg_data);
2198
2199 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2200 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
2201 lc_mod_name||'unx_exception',
2202 'G_EXC_UNEXPECTED_ERROR Exception');
2203 END IF;
2204
2205
2206 WHEN FND_API.G_EXC_ERROR THEN
2207 ROLLBACK to CREATE_WIP_JOB_PVT ;
2208 x_return_status := FND_API.G_RET_STS_ERROR;
2209
2210
2211 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2212 p_count => x_msg_count,
2213 p_data => x_msg_data);
2214
2215 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2216 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
2217 lc_mod_name||'exc_exception',
2218 'G_EXC_ERROR Exception');
2219 END IF;
2220
2221 WHEN OTHERS THEN
2222 ROLLBACK to CREATE_WIP_JOB_PVT ;
2223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2224
2225 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2226
2227 -- Add Unexpected Error to Message List, here SQLERRM is used for
2228 -- getting the error
2229
2230 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
2231 p_procedure_name => lc_api_name );
2232 END IF;
2233
2234 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2235 p_count => x_msg_count,
2236 p_data => x_msg_data);
2237
2238 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2239 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
2240 lc_mod_name||'others_exception',
2241 'OTHERS Exception');
2242 END IF;
2243
2244
2245 END create_job_from_estimate;
2246
2247 -- This procedure creates the save materials transactions of HVR transactions package.
2248 -- This procedure consolidates the eligible estimate lines to be transferred as the material
2249 -- requirements. The material requirements are created but not issued.
2250
2251 PROCEDURE matrl_reqmnt_from_estms(
2252 p_api_version_number IN NUMBER,
2253 p_init_msg_list IN VARCHAR2,
2254 p_commit IN VARCHAR2,
2255 p_validation_level IN NUMBER,
2256 x_return_status OUT NOCOPY VARCHAR2,
2257 x_msg_count OUT NOCOPY NUMBER,
2258 x_msg_data OUT NOCOPY VARCHAR2,
2259 x_op_created OUT NOCOPY VARCHAR2,
2260 p_rep_line_id IN NUMBER,
2261 p_wip_entity_id IN NUMBER
2262 ) IS
2263
2264 lc_mod_name VARCHAR2(200) := 'csd.plsql.csd_wip_job_pvt.create_matrl_reqmnt_from_estimates';
2265
2266 --table type to hold the material requirement line.
2267
2268 x_mtl_txn_dtls_tab_type CSD_HV_WIP_JOB_PVT.mtl_txn_dtls_tbl_type;
2269
2270
2271 BEGIN
2272
2273 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2274 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
2275 lc_mod_name||'begin',
2276 'Entering Private API create_job_from_estimate');
2277 END IF;
2278
2279 -- Initialize message list if p_init_msg_list is set to TRUE
2280 IF FND_API.to_boolean(p_init_msg_list) THEN
2281 FND_MSG_PUB.initialize;
2282 END IF;
2283
2284 -- Initialize API return status to success
2285 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2286
2287 -- get the material transactions table type
2288
2289 import_estms_to_wipjob(p_rep_line_id,p_wip_entity_id,x_mtl_txn_dtls_tab_type);
2290
2291 -- if the table type contains 1 or more records we will pass it to the
2292 -- hvr API to create the material requirements(we just create material requirements, we dont issue -- materials).
2293
2294 IF x_mtl_txn_dtls_tab_type.COUNT >= 1 THEN
2295
2296 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2297 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
2298 lc_mod_name||'begin',
2299 'Calling HVR API to create material requirements');
2300 END IF;
2301
2302 -- calling HVR api with the material requirments details.
2303 CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_MTL_TXN_DTLS(p_api_version_number => 1.0,
2304 p_init_msg_list => p_init_msg_list,
2305 p_commit => p_commit,
2306 p_validation_level => p_validation_level,
2307 x_return_status => x_return_status,
2308 x_msg_count => x_msg_count,
2309 x_msg_data => x_msg_data,
2310 p_mtl_txn_dtls_tbl => x_mtl_txn_dtls_tab_type,
2311 x_op_created => x_op_created);
2312
2313
2314 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2315 RAISE fnd_api.g_exc_error;
2316 END IF;
2317 END IF;
2318 COMMIT WORK;
2319 EXCEPTION
2320 WHEN FND_API.G_EXC_ERROR THEN
2321 x_return_status := fnd_api.G_RET_STS_ERROR;
2322 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2323 p_count => x_msg_count,
2324 p_data => x_msg_data );
2325 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2327 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2328 p_count => x_msg_count,
2329 p_data => x_msg_data );
2330 WHEN OTHERS THEN
2331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2332 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2333 p_count => x_msg_count,
2334 p_data => x_msg_data );
2335 END matrl_reqmnt_from_estms;
2336
2337 END CSD_WIP_JOB_PVT;