1 PACKAGE BODY CSD_WIP_JOB_PVT AS
2 /* $Header: csdvwjbb.pls 120.20.12020000.4 2013/04/10 01:07:37 takwong 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 g_job_prefix VARCHAR2(30) := fnd_profile.value('CSD_DEFAULT_JOB_PREFIX');
26
27 -- This procedure accepts job header, bills and routing information and inserts it into
28 -- WIP_JOB_SCHEDULE_INTERFACE table. This procedure inserts one record at a time, hence
29 -- this procedure needs to be called in a loop for multiple jobs being submitted
30 -- to WIP Mass Load. If all the records need to be processed by a single WIP Mass Load
31 -- request, they should all be passed in the the same group_id.
32
33 PROCEDURE insert_job_header
34 (
35 p_job_header_rec IN JOB_HEADER_REC_TYPE,
36 p_job_bill_routing_rec IN JOB_BILL_ROUTING_REC_TYPE,
37 p_group_id IN NUMBER,
38 x_interface_id OUT NOCOPY NUMBER, -- nnadig: bug 9263438
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
75 BEGIN
76
77
78 IF ( l_proc_level >= l_debug_level ) then
79 FND_LOG.STRING( l_proc_level,
80 l_mod_name||'begin',
81 'Entering procedure insert_job_header' );
82 END IF;
83
84 x_return_status := FND_API.G_RET_STS_SUCCESS;
85
86
87 -- Populate the record l_job_header_rec
88
89
90 -- Populate the constant values
91
92 l_job_header_rec.process_phase := l_validation_phase;
93 l_job_header_rec.process_status := l_pending_status;
94 l_job_header_rec.source_code := l_depot_repair_source_code;
95 l_job_header_rec.source_line_id := l_depot_app_source_line_id ;
96 l_job_header_rec.load_type := l_non_standard_load_type;
97
98 l_job_header_rec.group_id := p_group_id;
99
100 -- nnadig: bug 9263438 - Use wip_interface_s for interface_id, not sequence from group_id
101 -- According to wip_job_schedule_interface documentation, interface ID should be NULL
102 -- and will be populated by WIP later. However, for defaulting material, CreateOneJob
103 -- is used which requires an interface ID, so we will generate an interface ID for this
104 -- case only and pass it back to the calling procedure to use.
105
106 -- end nnadig: bug 9263438
107 SELECT wip_interface_s.NEXTVAL INTO x_interface_id FROM dual;
108 l_job_header_rec.interface_id := x_interface_id;
109 -- end nnadig: bug 9263438
110
111
112
113 -- Populate the row who columns
114
115 l_job_header_rec.creation_date := SYSDATE;
116 l_job_header_rec.last_update_date := SYSDATE;
117 l_job_header_rec.created_by := fnd_global.user_id;
118 l_job_header_rec.last_updated_by := fnd_global.user_id;
119 l_job_header_rec.last_update_login := fnd_global.login_id;
120
121
122 l_job_header_rec.job_name := p_job_bill_routing_rec.job_name;
123 l_job_header_rec.organization_id := p_job_header_rec.organization_id;
124 l_job_header_rec.status_type := p_job_header_rec.status_type;
125 l_job_header_rec.first_unit_start_date := p_job_header_rec.scheduled_start_date;
126 l_job_header_rec.last_unit_completion_date := p_job_header_rec.scheduled_end_date;
127 l_job_header_rec.primary_item_id := p_job_header_rec.inventory_item_id;
128
129 -- rfieldma, project integration
130 l_job_header_rec.project_id := p_job_header_rec.project_id;
131 l_job_header_rec.task_id := p_job_header_rec.task_id;
132 l_job_header_rec.end_item_unit_number := p_job_header_rec.unit_number;
133
134
135 -- WIP Accounting Class code
136
137 l_job_header_rec.class_code := p_job_header_rec.class_code;
138 l_job_header_rec.start_quantity := p_job_header_rec.quantity;
139
140 -- Fix for bug# 3109417
141 -- If the profile CSD: Default WIP MRP Net Qty to Zero is set to
142 -- null / 'N' then net_quantity = start_quantity else if the
143 -- profile is set to 'Y' then net_quantity = 0
144 IF ( nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N') = 'N' ) THEN
145 l_job_header_rec.net_quantity := p_job_header_rec.quantity;
146 ELSIF ( fnd_profile.value('CSD_WIP_MRP_NET_QTY') = 'Y' ) THEN
147 l_job_header_rec.net_quantity := 0;
148 END IF;
149
150
151 -- Bill and Routing information
152
153 l_job_header_rec.routing_reference_id := p_job_bill_routing_rec.routing_reference_id ;
154 l_job_header_rec.bom_reference_id := p_job_bill_routing_rec.bom_reference_id ;
155 l_job_header_rec.alternate_routing_designator := p_job_bill_routing_rec.alternate_routing_designator ;
156 l_job_header_rec.alternate_bom_designator := p_job_bill_routing_rec.alternate_bom_designator ;
157 l_job_header_rec.completion_subinventory := p_job_bill_routing_rec.completion_subinventory;
158 l_job_header_rec.completion_locator_id := p_job_bill_routing_rec.completion_locator_id;
159
160
161 --insert into table wip_job_schedule_interface
162 BEGIN
163 INSERT INTO wip_job_schedule_interface
164 (
165 last_update_date,
166 last_updated_by,
167 creation_date,
168 created_by,
169 last_update_login,
170 load_type,
171 process_phase,
172 process_status,
173 group_id,
174 source_code,
175 source_line_id,
176 job_name,
177 organization_id,
178 status_type,
179 first_unit_start_date,
180 last_unit_completion_date,
181 completion_subinventory,
182 completion_locator_id,
183 start_quantity,
184 net_quantity,
185 class_code,
186 primary_item_id,
187 bom_reference_id,
188 routing_reference_id,
189 alternate_routing_designator,
190 alternate_bom_designator,
191 -- rfieldma, project integration
192 project_id,
193 task_id,
194 end_item_unit_number,
195 interface_id
196 )
197 VALUES
198 (
199 l_job_header_rec.last_update_date,
200 l_job_header_rec.last_updated_by,
201 l_job_header_rec.creation_date,
202 l_job_header_rec.created_by,
203 l_job_header_rec.last_update_login,
204 l_job_header_rec.load_type,
205 l_job_header_rec.process_phase,
206 l_job_header_rec.process_status,
207 l_job_header_rec.group_id,
208 l_job_header_rec.source_code,
209 l_job_header_rec.source_line_id,
210 l_job_header_rec.job_name,
211 l_job_header_rec.organization_id,
212 l_job_header_rec.status_type,
213 l_job_header_rec.first_unit_start_date,
214 l_job_header_rec.last_unit_completion_date,
215 l_job_header_rec.completion_subinventory,
216 l_job_header_rec.completion_locator_id,
217 l_job_header_rec.start_quantity,
218 l_job_header_rec.net_quantity,
219 l_job_header_rec.class_code,
220 l_job_header_rec.primary_item_id,
221 l_job_header_rec.bom_reference_id,
222 l_job_header_rec.routing_reference_id,
223 l_job_header_rec.alternate_routing_designator,
224 l_job_header_rec.alternate_bom_designator,
225 -- rfieldma, project integration
226 l_job_header_rec.project_id,
227 l_job_header_rec.task_id,
228 l_job_header_rec.end_item_unit_number,
229 l_job_header_rec.interface_id
230 );
231 EXCEPTION
232 WHEN OTHERS THEN
233 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_HEADER_INSERT_ERR');
234 FND_MESSAGE.SET_TOKEN('JOB_NAME', l_job_header_rec.job_name );
235 FND_MSG_PUB.ADD;
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 RETURN;
238 END;
239
240
241 IF ( l_proc_level >= l_debug_level ) then
242 FND_LOG.STRING( l_proc_level,
243 l_mod_name||'end',
244 'Leaving procedure insert_job_header');
245 END IF;
246
247
248 END insert_job_header;
249
250 -- overloaded version of insert job header. receives the job header rec and inserts into the
251 -- wip_job_schedule_interface table.
252 -- The job header rec is created as ROWTYPE of wip_job_schedule_interface.
253 -- 12.1 Create Job from Estimates change, subhat.
254
255 PROCEDURE insert_job_header
256 (
257 p_job_header_rec IN wip_job_schedule_interface%ROWTYPE,
258 x_return_status OUT NOCOPY VARCHAR2
259 )
260 IS
261
262 -- Job Record to hold the Job header, bills and routing information being inserted
263 -- into wip_job_schedule_interface
264
265 l_job_header_rec wip_job_schedule_interface%ROWTYPE := p_job_header_rec;
266
267
268 -- variables used for FND_LOG debug messages
269
270 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
271 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
272 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.insert_job_header.';
273
274
275 -- Constants Used for Inserting into wip_job_schedule_interface,
276 -- These are the values needed for WIP Mass Load to pick up the records
277
278 -- Indicates that the process Phase is Validation
279 lc_validation_phase CONSTANT NUMBER := 2;
280
281 -- Indicates that the process_status is Pending
282 lc_pending_status CONSTANT NUMBER := 1;
283
284 -- Source Code Value of 'Depot_Repair'
285 lc_depot_repair_source_code CONSTANT VARCHAR2(30) := 'DEPOT_REPAIR';
286
287 -- Depot repair Application Id passed as source_line_id
288 lc_depot_app_source_line_id CONSTANT NUMBER := 512;
289
290
291
292
293 BEGIN
294
295
296 IF ( l_proc_level >= l_debug_level ) then
297 FND_LOG.STRING( l_proc_level,
298 l_mod_name||'begin',
299 'Entering procedure insert_job_header' );
300 END IF;
301
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304
305 -- Populate the record l_job_header_rec
306
307
308 -- Populate the constant values
309
310 l_job_header_rec.process_phase := lc_validation_phase;
311 l_job_header_rec.process_status := lc_pending_status;
312 l_job_header_rec.source_code := lc_depot_repair_source_code;
313 l_job_header_rec.source_line_id := lc_depot_app_source_line_id ;
314
315
316 -- Populate the row who columns
317
318 l_job_header_rec.creation_date := SYSDATE;
319 l_job_header_rec.last_update_date := SYSDATE;
320 l_job_header_rec.created_by := fnd_global.user_id;
321 l_job_header_rec.last_updated_by := fnd_global.user_id;
322 l_job_header_rec.last_update_login := fnd_global.login_id;
323
324
325 --insert into table wip_job_schedule_interface
326 BEGIN
327 INSERT INTO wip_job_schedule_interface
328 (
329 wip_entity_id,
330 interface_id,
331 last_update_date,
332 last_updated_by,
333 creation_date,
334 created_by,
335 last_update_login,
336 load_type,
337 process_phase,
338 process_status,
339 group_id,
340 header_id,
341 source_code,
342 source_line_id,
343 job_name,
344 organization_id,
345 status_type,
346 first_unit_start_date,
347 last_unit_completion_date,
348 completion_subinventory,
349 completion_locator_id,
350 start_quantity,
351 net_quantity,
352 class_code,
353 primary_item_id,
354 bom_reference_id,
355 routing_reference_id,
356 alternate_routing_designator,
357 alternate_bom_designator,
358 project_id,
359 task_id,
360 end_item_unit_number
361 )
362 VALUES
363 (
364 l_job_header_rec.wip_entity_id,
365 l_job_header_rec.interface_id,
366 l_job_header_rec.last_update_date,
367 l_job_header_rec.last_updated_by,
368 l_job_header_rec.creation_date,
369 l_job_header_rec.created_by,
370 l_job_header_rec.last_update_login,
371 l_job_header_rec.load_type,
372 l_job_header_rec.process_phase,
373 l_job_header_rec.process_status,
374 l_job_header_rec.group_id,
375 l_job_header_rec.header_id,
376 l_job_header_rec.source_code,
377 l_job_header_rec.source_line_id,
378 l_job_header_rec.job_name,
379 l_job_header_rec.organization_id,
380 l_job_header_rec.status_type,
381 l_job_header_rec.first_unit_start_date,
382 l_job_header_rec.last_unit_completion_date,
383 l_job_header_rec.completion_subinventory,
384 l_job_header_rec.completion_locator_id,
385 l_job_header_rec.start_quantity,
386 l_job_header_rec.net_quantity,
387 l_job_header_rec.class_code,
388 l_job_header_rec.primary_item_id,
389 l_job_header_rec.bom_reference_id,
390 l_job_header_rec.routing_reference_id,
391 l_job_header_rec.alternate_routing_designator,
392 l_job_header_rec.alternate_bom_designator,
393 l_job_header_rec.project_id,
394 l_job_header_rec.task_id,
395 l_job_header_rec.end_item_unit_number
396 );
397 EXCEPTION
398 WHEN OTHERS THEN
399 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_HEADER_INSERT_ERR');
400 FND_MESSAGE.SET_TOKEN('JOB_NAME', l_job_header_rec.job_name );
401 FND_MSG_PUB.ADD;
402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
403 RETURN;
404 END;
405
406
407 IF ( l_proc_level >= l_debug_level ) then
408 FND_LOG.STRING( l_proc_level,
409 l_mod_name||'end',
410 'Leaving procedure insert_job_header');
411 END IF;
412
413
414 END insert_job_header;
415
416
417
418 -- This Procedure verifies that both the Internal and Standard Concurrent
419 -- managers are up. If either one is down, it writes an error to the
420 -- message list and returns an error status.
421
422 PROCEDURE verify_conc_manager_status
423 (
424 x_return_status OUT NOCOPY VARCHAR2
425 )
426 IS
427
428 -- Used in the call to check if concurrent managers are up
429
430 l_targetp NUMBER;
431 l_activep NUMBER;
432 l_targetp1 NUMBER;
433 l_activep1 NUMBER;
434 l_pmon_method VARCHAR2(30);
435 l_callstat NUMBER;
436
437 -- Declare the constants
438
439 -- FND Application_id under which the standard and intenral concurrent managers are registered,
440 -- Concurrent Manager Id for Standard and Internal Managers.
441 -- These are used in the call to get_manager_status to see if the standard and internal concurrent
442 -- managers are up
443
444 l_fnd_application_id CONSTANT NUMBER := 0;
445 l_internal_manager_id CONSTANT NUMBER := 1;
446 l_standard_manager_id CONSTANT NUMBER := 0;
447
448
449 BEGIN
450
451 x_return_status := FND_API.G_RET_STS_SUCCESS;
452
453 -- Validate if Internal Concurrent Manager is up
454
455 fnd_concurrent.get_manager_status(applid => l_fnd_application_id,
456 managerid => l_internal_manager_id,
457 targetp => l_targetp1,
458 activep => l_activep1,
459 pmon_method => l_pmon_method,
460 callstat => l_callstat);
461
462
463 -- Validate if Standard Concurrent Manager is up
464
465 fnd_concurrent.get_manager_status(applid => l_fnd_application_id,
466 managerid => l_standard_manager_id,
467 targetp => l_targetp,
468 activep => l_activep,
469 pmon_method => l_pmon_method,
470 callstat => l_callstat);
471
472 -- If the actual number of processes that are up for either the Internal
473 -- or Standard Manager is <= 0, which indicates that the concurrent manager
474 -- is down, then add the message to the message list and exit
475
476 IF (l_activep <= 0 OR l_activep1 <= 0) THEN
477 FND_MESSAGE.SET_NAME('CSD','CSD_CONC_MGR_DOWN');
478 FND_MSG_PUB.ADD;
479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
480 END IF;
481
482
483 END verify_conc_manager_status;
484
485
486 -- This procedure checks if the specified Job name exists in the
487 -- specified organization. It checks if it exists in
488 -- wip_entities or wip_job_schedule_interface table.
489 -- If it exists, then an Error status is returned.
490 -- If it does not exist in either of the tables, then
491 -- a Sucess status is returned.
492 -- This procedure is used whenever a job_name is generated, to confirm that
493 -- the newly generated job_name does not already exist and hence can be
494 -- used to submit it to WIP Mass Load.
495
496
497 PROCEDURE validate_job_name
498 (
499 p_job_name IN VARCHAR2,
500 p_organization_id IN NUMBER,
501 x_return_status OUT NOCOPY VARCHAR2
502 )
503 IS
504
505 -- Used to check the existence of the Job_name for the specified organizization,
506 l_job_count NUMBER := 0;
507
508 BEGIN
509
510
511 Select count(*) into l_job_count from wip_entities where wip_entity_name = p_job_name and
512 organization_id = p_organization_id ;
513
514 If l_job_count = 0 Then
515
516 -- Job does not exist in WIP_entities, check if it is already inserted in the interface table by another
517 -- process and so may be in the process of getting into WIP.
518 -- If it exists, do not want to use this job name, so return Error
519
520 Select count(*) into l_job_count from wip_job_schedule_interface where job_name = p_job_name and
521 organization_id = p_organization_id ;
522
523 IF l_job_count = 0 THEN
524
525 -- Generated job name does exist either in the interface or wip_entities table,
526 -- Success is returned
527
528 x_return_status := FND_API.G_RET_STS_SUCCESS;
529 RETURN;
530
531 ELSE
532
533 -- Job exists in wip_job_schedule_interface table, hence return Error status
534
535 x_return_status := FND_API.G_RET_STS_ERROR;
536 RETURN;
537
538
539 END IF;
540
541
542 ELSE
543
544 -- Job exists in wip_entities table, hence return Error status
545
546 x_return_status := FND_API.G_RET_STS_ERROR;
547 RETURN;
548
549
550 END IF;
551
552
553
554 END validate_job_name;
555
556
557
558
559 -- This procedure generates a job name by appending a sequence generated number
560 -- to the passed in Job_Prefix
561 -- It Validates that the generated job name is unique for the specified organization,
562 -- It keeps looping and appending the subsequent sequence generated number, till a
563 -- unique Job name is generated
564
565
566 PROCEDURE generate_job_name
567 (
568 p_job_prefix IN VARCHAR2,
569 p_organization_id IN NUMBER,
570 x_job_name OUT NOCOPY VARCHAR2
571 )
572 IS
573
574 l_return_status VARCHAR2(1);
575
576 BEGIN
577
578 Loop
579
580 -- generate the Job Name by appending a sequence generated number to the passed in
581 -- job_prefix
582
583 Select p_job_prefix || TO_CHAR( CSD_JOB_NAME_S.NEXTVAL ) into
584 x_job_name From Dual;
585
586
587 -- Check if the job name generated is unique for the specified organization,
588 -- if not loop around till a unique job name is generated
589
590 Validate_job_name ( p_job_name => x_job_name,
591 p_organization_id => p_organization_id,
592 x_return_status => l_return_status ) ;
593
594 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
595
596 -- Generated job name does not exist both in the interface and wip_entities table, so exit the loop
597
598 exit;
599
600 END IF;
601
602
603 End Loop;
604
605 END generate_job_name;
606
607 -- private procedure.
608 -- this procedure creates a material transactions table type based on the repair estimate lines.
609 -- all the material estimates with source as manual or repair bom would be passed as repair
610 -- estimates. The procedure takes in repair_line_id,wip_entity_id and the material transactions
611 -- table is out parameter.
612 -- 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
613 -- material requirements.
614 -- 12.1 default material requirements -- subhat.
615
616 PROCEDURE import_estms_to_wipjob(p_repair_line_id IN NUMBER,
617 p_wip_entity_id IN NUMBER,
618 x_mtl_txn_dtls_tab_type OUT NOCOPY CSD_HV_WIP_JOB_PVT.mtl_txn_dtls_tbl_type )
619 IS
620
621 -- local variables
622
623 l_operation_seq_num NUMBER := 1;
624 l_estimate_uom VARCHAR2(25);
625 l_inv_org NUMBER := fnd_profile.value('CSD_DEF_REP_INV_ORG'); --cs_std.get_item_valdn_orgzn_id; bug#10144772, subhat
626 l_counter NUMBER;
627
628 -- cursor to calculate the estimate details.
629
630
631 CURSOR repair_estimate_dtls(p_rep_line_id in number) IS
632 SELECT crl.inventory_item_id,
633 -- bug#7132807, subhat. no need to select estimate quantity.
634 --crl.estimate_quantity,
635 crl.lot_control_code,
636 crl.serial_number_control_code,
637 msi.primary_uom_code,
638 crl.unit_of_measure_code,
639 msi.revision_qty_control_code,
640 msi.new_revision_code,
641 SUM(crl.estimate_quantity) AS quantity
642 FROM csd_repair_estimate_lines_v crl, mtl_system_items_kfv msi
643 WHERE crl.repair_line_id = p_rep_line_id AND
644 crl.inventory_item_id = msi.inventory_item_id AND
645 msi.organization_id = cs_std.get_item_valdn_orgzn_id AND
646 billing_category = 'M' AND
647 est_line_source_type_code IN ('MANUAL','REPAIR_BOM')
648 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;
649
650 -- cursor to fetch the default operation sequence number
651
652 CURSOR default_operation(p_wip_entity_id IN NUMBER) IS
653 SELECT operation_seq_num
654 FROM wip_operations
655 WHERE wip_entity_id = p_wip_entity_id AND
656 previous_operation_seq_num IS NULL ;
657
658 -- special case ( when CSD: Default Repair Item as Material on Job profile is set to yes)
659 -- cursor to fetch the material details.
660 -- irrespective of organization on RO, we always derive the organization from the profile.
661 CURSOR repair_item_dtls(p_rep_line_id IN NUMBER)
662 IS
663 SELECT cr.inventory_item_id,
664 cr.unit_of_measure,
665 cr.serial_number,
666 cr.quantity,
667 -- cr.inventory_org_id bug#10144772
668 fnd_profile.value('CSD_DEF_REP_INV_ORG')
669 FROM
670 csd_repairs_v cr
671 WHERE
672 cr.repair_line_id = p_repair_line_id;
673
674 BEGIN
675
676 -- determine the operations to which the materials needs to be issued.
677
678 OPEN default_operation(p_wip_entity_id);
679 FETCH default_operation INTO l_operation_seq_num;
680 CLOSE default_operation;
681
682 -- check the value of l_operation_seq_num if its null make it 1.
683
684 IF l_operation_seq_num IS NULL THEN
685 l_operation_seq_num := 1;
686 END IF;
687
688 l_counter := 1;
689 -- special case
690 -- when the profile CSD: Default Repair Item as Material on Job is set to yes, we need to populate
691 -- the repair item as the material requirement,irrespective if there are any estimate lines or not.
692 -- put the profile value once the profile is created.
693
694 -- clear the collection type.
695
696 x_MTL_TXN_DTLS_TAB_TYPE.DELETE;
697
698 IF nvl(fnd_profile.value('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'),'N') = 'Y' THEN
699 OPEN repair_item_dtls(p_repair_line_id);
700 FETCH repair_item_dtls INTO
701 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).inventory_item_id,
702 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_uom,
703 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).serial_number,
704 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).required_quantity,
705 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).organization_id ;
706 CLOSE repair_item_dtls;
707 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_quantity := x_MTL_TXN_DTLS_TAB_TYPE(l_counter).required_quantity;
708 -- sub inventory is defaulted from the CSD_DEF_HV_SUBINV profile option.
709 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
710 --message(fnd_profile.value('CSD_DEF_HV_SUBINV'));
711 IF x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory IS NULL THEN
712 FND_MESSAGE.SET_NAME('CSD','CSD_DEF_SUB_INV_NULL');
713 FND_MSG_PUB.ADD;
714 RAISE FND_API.G_EXC_ERROR;
715 END IF;
716 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).new_row := 'Y';
717
718 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_locator_id := NULL;
719 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).wip_entity_id := p_wip_entity_id;
720
721 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).operation_seq_num := l_operation_seq_num;
722
723 l_counter := l_counter + 1;
724 END IF;
725
726
727
728 OPEN repair_estimate_dtls(p_repair_line_id);
729
730 LOOP
731
732 FETCH repair_estimate_dtls INTO
733 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).inventory_item_id,
734 -- bug#6903726 subhat
735 --x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_quantity,
736 -- bug#7132807 subhat.
737 --x_MTL_TXN_DTLS_TAB_TYPE(l_counter).required_quantity,
738 -- end bug#6903726 subhat
739 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).lot_control_code,
740 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).serial_number_control_code,
741 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_uom,
742 l_estimate_uom,
743 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).revision_qty_control_code,
744 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).revision,
745 --bug#6903726 subhat
746 --x_MTL_TXN_DTLS_TAB_TYPE(l_counter).required_quantity;
747 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_quantity;
748 --end bug#6903726 subhat
749 EXIT WHEN repair_estimate_dtls%NOTFOUND;
750 -- check if the repair estimate UOM is different from primary UOM for the item.
751 -- if its different then convert the UOM to primary UOM and corrosponding quantity.
752 IF l_estimate_uom <> x_MTL_TXN_DTLS_TAB_TYPE(l_counter).transaction_uom THEN
753 -- if the uom's are different then change convert the quantity.
754 -- for instance if primary uom = 'Ea' and estimate UOM = dozen and estimate quantity = 1
755 -- then the quantity should be changed to 1* uom_Conversion_factor for dozen.
756 NULL;
757 END IF;
758
759 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).organization_id := l_inv_org;
760 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).new_row := 'Y';
761
762 -- sub inventory is defaulted from the CSD_DEF_HV_SUBINV profile option.
763 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
764 --message(fnd_profile.value('CSD_DEF_HV_SUBINV'));
765 IF x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_subinventory IS NULL THEN
766 FND_MESSAGE.SET_NAME('CSD','CSD_DEF_SUB_INV_NULL');
767 FND_MSG_PUB.ADD;
768 RAISE FND_API.G_EXC_ERROR;
769 END IF;
770
771 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).supply_locator_id := NULL;
772 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).wip_entity_id := p_wip_entity_id;
773
774 -- check whether the item is serial controlled. If so default in the serial number.
775
776 x_MTL_TXN_DTLS_TAB_TYPE(l_counter).operation_seq_num := l_operation_seq_num;
777
778 l_counter := l_counter + 1;
779 END LOOP;
780
781 IF repair_estimate_dtls%ISOPEN THEN
782 CLOSE repair_estimate_dtls;
783 END IF;
784
785 END import_estms_to_wipjob;
786
787
788 -- API
789 -- SUBMIT_JOBS
790 --
791 -- Purpose
792 -- This API creates WIP Jobs by submitting the passed in Job information to
793 -- WIP Mass Load and updates CSD tables with the newly created jobs information.
794 -- It achieves this by calling helper procedures.
795 --
796 -- This API inserts Job header, Bills and Routing information passed in into
797 -- WIP_JOB_SCHEDULE_INTERFACE table by calling procedure insert_job_header.
798 --
799 -- If job name is not passed in, then it is generated here by appending a
800 -- sequence generated number to the job_name_prefix passed in.
801 -- If job name is passed in, it is validated to make sure that it is unique
802 -- for the specified organization.
803 --
804 -- This API then submits the concurrent request for concurrent
805 -- program 'Depot Repair WIP Job Submission', which submits WIP Mass Load,
806 -- waits for it to complete and then runs the WIP Update program to update WIP
807 -- information in CSD tables.
808 --
809 -- If no routings or bills are passed in, jobs are submitted to WIP Mass Load based
810 -- on the header information to create jobs with no operations, material requirements
811 -- or resource requirements.
812 --
813 -- Arguments
814 -- p_repair_line_id - Repair Line Id of the repair order for which the jobs are being created.
815 -- WIP Update program is run for the specified repair order.
816 -- If jobs are being submitted for more than one repair order, then this is
817 -- passed in as null and the WIP Update program is run for all the eligible
818 -- repair orders.
819 -- p_job_header_rec - Job header Information record. This is the same for all the jobs being created.
820 -- p_x_job_bill_routing_tbl - Table of Bill and Routing information records. Each record results in a
821 -- new job. If a record here has a not null Job Name specified, then the job name
822 -- specified here is used, instead of generating it. This is done only when one job
823 -- is being submitted and the profile option 'Use CSD as Job Prefix' is set to 'N'.
824 -- This is a IN OUT parameter as the generated Job names are passed back to the
825 -- calling program in this table.
826 -- x_group_id - Group_id used for the WIP Mass Load concurrent request submission. This is returned
827 -- to the calling program.
828 -- x_request_id - Concurrent Request id of the concurrent request submitted for concurrent program
829 -- 'Depot Repair WIP Job Submission'. This is passed back to the calling program.
830 --
831 -- Note, p_commit is not specified as a parameter to this API, as for successful submission of a concurrent
832 -- request, a commit is required always, so this API always commits. For the same reason, this API is
833 -- declared as an AUTONOMOUS Transaction. For a AUTONOMOUS Transaction, we cannot rollback to a specified
834 -- SAVEPOINT, hence SAVEPOINT is not specified.
835
836
837 PROCEDURE submit_jobs
838 (
839 p_api_version IN NUMBER,
840 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
841 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_msg_data OUT NOCOPY VARCHAR2,
845 p_repair_line_id IN NUMBER,
846 p_job_header_rec IN job_header_rec_type,
847 p_x_job_bill_routing_tbl IN OUT NOCOPY job_bill_routing_tbl_type,
848 x_group_id OUT NOCOPY NUMBER,
849 x_request_id OUT NOCOPY NUMBER
850 )
851 IS
852 PRAGMA AUTONOMOUS_TRANSACTION;
853
854 --This API is an Autonomous Transaction. We have to explicitly commit or rollback the
855 --transactions it or its called procedure contain when it exits. This autonomous
856 --transaction doesn't affect the main transaction in its calling API.
857
858
859 l_api_name CONSTANT VARCHAR2(30) := 'Submit_jobs';
860 l_api_version CONSTANT NUMBER := 1.0;
861
862 l_group_id NUMBER;
863
864
865 -- Bill, routing information for the Job passed to insert_job_header
866 l_job_bill_routing_rec job_bill_routing_rec_type;
867
868
869 -- variables used for FND_LOG debug messages
870
871 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
872 l_stat_level NUMBER := FND_LOG.LEVEL_STATEMENT;
873 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
874 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
875 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.submit_jobs.';
876
877 l_default_ro_item VARCHAR2(1);
878 l_wip_entity_id NUMBER;
879 l_mtl_txn_dtls_tbl CSD_HV_WIP_JOB_PVT.MTL_TXN_DTLS_TBL_TYPE;
880 l_op_created VARCHAR2(10);
881
882
883 CURSOR c_repair_line_info(p_repair_line_id IN NUMBER) IS
884 select inventory_item_id, unit_of_measure, quantity, serial_number
885 --, inventory_org_id -- swai: bug 9954780
886 from csd_repairs
887 where repair_line_id = p_repair_line_id;
888
889 CURSOR c_count_material(p_wip_entity_id NUMBER, l_inventory_item_id NUMBER) IS
890 select 'X'
891 from wip_requirement_operations_v
892 where wip_entity_id = p_wip_entity_id
893 and inventory_item_id = l_inventory_item_id
894 and rownum = 1;
895
896
897 -- Cursor to select the item attributes serial control code and
898 -- lot control code.
899 CURSOR cur_get_item_attribs (
900 p_org_id NUMBER,
901 p_item_id NUMBER
902 )
903 IS
904 SELECT serial_number_control_code
905 FROM mtl_system_items
906 WHERE organization_id = p_org_id AND inventory_item_id = p_item_id;
907
908
909 Cursor c_get_serial_info(p_item_id number, p_serial_number varchar2, p_org_id number) is
910 select current_status, current_subinventory_code from mtl_serial_numbers
911 where inventory_item_id = p_item_id and serial_number = p_serial_number and current_organization_id = p_org_id;
912
913
914 Cursor c_get_min_operation_seq(p_wip_entity_id number) is
915 select min(operation_seq_num) from wip_operations_v where wip_entity_id = p_wip_entity_id;
916
917 l_inventory_item_id NUMBER;
918 l_unit_of_measure VARCHAR2(3);
919 l_quantity NUMBER;
920 l_serial_number VARCHAR2(30);
921 l_inventory_org_id NUMBER;
922 l_subinventory VARCHAR2(30);
923 l_dummy VARCHAR2(1) := null;
924 l_serial_control_code NUMBER;
925 l_current_status NUMBER;
926 l_current_subinventory_code VARCHAR2(10);
927 l_operation_seq_num NUMBER;
928 l_num_other_jobs NUMBER :=0; -- swai: bug 7477845/7483291
929 l_interface_id NUMBER; -- nnadig: bug 9263438
930
931 BEGIN
932
933 IF ( l_proc_level >= l_debug_level ) THEN
934 FND_LOG.STRING( l_proc_level,
935 l_mod_name||'begin',
936 'Entering Private API submit_jobs');
937 END IF;
938
939 -- Standard call to check for call compatibility
940 IF Not FND_API.COMPATIBLE_API_CALL( l_api_version,
941 p_api_version,
942 l_api_name,
943 G_PKG_NAME) THEN
944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
945 END If;
946
947 -- Initialize message list if p_init_msg_list is set to TRUE
948 IF FND_API.to_boolean(p_init_msg_list) THEN
949 FND_MSG_PUB.initialize;
950 END IF;
951
952 -- Initialize API return status to success
953 x_return_status:=FND_API.G_RET_STS_SUCCESS;
954
955
956 -- Verify that the Standard and Internal Concurrent Managers are UP
957
958 verify_conc_manager_status ( x_return_status => x_return_status );
959
960 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
961 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
962 END IF;
963
964 l_default_ro_item := nvl(FND_PROFILE.VALUE('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'), 'N');
965
966 -- Get the Group_id to be used for WIP Mass Load, All the records inserted into
967 -- wip_job_schedule_interface have the same group_id , so that one WIP Mass Load
968 -- request can process all the records
969 --if (l_default_ro_item = 'N') then
970 --SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
971
972 if (l_default_ro_item = 'Y') then
973 OPEN c_repair_line_info(p_repair_line_id);
974 FETCH c_repair_line_info into
975 l_inventory_item_id,
976 l_unit_of_measure,
977 l_quantity,
978 l_serial_number;
979 -- l_inventory_org_id; -- swai: bug 9954780
980 CLOSE c_repair_line_info;
981 l_inventory_org_id := fnd_profile.value('CSD_DEF_REP_INV_ORG'); -- swai: bug 9954780
982 l_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
983
984
985 --Get serial number control code and lot control code
986 OPEN cur_get_item_attribs (l_inventory_org_id,
987 l_inventory_item_id);
988
989 FETCH cur_get_item_attribs
990 INTO l_serial_control_code;
991 CLOSE cur_get_item_attribs;
992
993
994 IF l_serial_control_code IN (2, 5) then
995 OPEN c_get_serial_info (l_inventory_item_id, l_serial_number, l_inventory_org_id);
996 FETCH c_get_serial_info
997 INTO l_current_status,l_current_subinventory_code;
998 CLOSE c_get_serial_info;
999 --current status = 3 is valid serial number
1000 if (l_current_status = 3) then
1001 l_subinventory := l_current_subinventory_code;
1002 else
1003 l_serial_number := null;
1004 end if;
1005 else
1006 --don't pass the serial number, it is not valid serial number
1007 l_serial_number := null;
1008 end if;
1009
1010 End if;
1011
1012
1013 IF p_x_job_bill_routing_tbl.COUNT = 0 THEN
1014
1015 -- if (l_default_ro_item = 'Y') then
1016 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
1017 -- End if;
1018
1019 p_x_job_bill_routing_tbl(0).group_id := l_group_id;
1020
1021 -- The bill_routings table is empty, Generate the job name using the
1022 -- job_prefix passed in. Here no bills and routings are passed in and only one job
1023 -- will be created for the passed in Job header information.
1024
1025
1026 generate_job_name ( p_job_prefix => p_job_header_rec.job_prefix,
1027 p_organization_id => p_job_header_rec.organization_id,
1028 x_job_name => l_job_bill_routing_rec.job_name );
1029
1030
1031 -- Assign the generated Job name to the first record in job_bill_routing_tbl to be passed back
1032 -- to the calling program. This is passed to the insert_job_header procedure as well.
1033
1034 p_x_job_bill_routing_tbl(0).job_name := l_job_bill_routing_rec.job_name;
1035
1036 IF ( l_event_level >= l_debug_level ) then
1037 FND_LOG.STRING( l_event_level,
1038 l_mod_name||'beforecallinsert',
1039 'Just before calling insert_job_header');
1040 END IF;
1041
1042
1043 -- Call procedure to insert job header and job name information
1044 -- into wip_job_schedule_interface table
1045
1046
1047 insert_job_header( p_job_header_rec => p_job_header_rec,
1048 p_job_bill_routing_rec => l_job_bill_routing_rec,
1049 p_group_id => l_group_id,
1050 x_interface_id => l_interface_id,--nnadig: bug 9263438
1051 x_return_status => x_return_status );
1052
1053
1054 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1055 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1056 END IF;
1057
1058
1059 --This code is change to online create the wipjob without use the concurrent program.
1060 WIP_MASSLOAD_PUB.createOneJob( p_interfaceID => l_interface_id,--nnadig: bug 9263438
1061 p_validationLevel => p_validation_level,
1062 x_wipEntityID => l_wip_entity_id,
1063 x_returnStatus => x_return_status,
1064 x_errorMsg => x_msg_data );
1065
1066 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1067 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
1068 FND_MSG_PUB.ADD;
1069 RAISE FND_API.G_EXC_ERROR;
1070 end if;
1071
1072 COMMIT;
1073
1074 --bug#12316893
1075 Delete_Completed_Wip_Records(l_interface_id);
1076 --bug#12316893
1077
1078 CSD_WARRANTY_CONTRACT_PVT.Default_Warranty_Contract(
1079 P_Api_Version_Number => 1.0,
1080 P_Init_Msg_List => FND_API.G_FALSE,
1081 p_commit => FND_API.G_TRUE,
1082 p_validation_level => fnd_api.g_valid_level_none,
1083 P_Repair_Line_Id => p_repair_line_id,
1084 P_Wip_Entity_Id => l_wip_entity_id,
1085 X_Return_Status => x_return_status,
1086 X_Msg_Count => x_msg_count,
1087 X_Msg_Data => x_msg_data
1088 );
1089 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS)
1090 THEN
1091 RAISE Fnd_Api.G_EXC_ERROR;
1092 END IF;
1093
1094
1095 if (l_default_ro_item = 'Y') then
1096 -- swai: bug 7477845/7483291
1097 -- check if there another job existing for this RO. If so, do not default
1098 -- the RO item as a material. Must compare we.wip_entity_id since
1099 -- crj.wip_entity_id may be null (until wip_update is done).
1100 select count(*)
1101 into l_num_other_jobs
1102 from csd_repair_job_xref crj,
1103 wip_entities we
1104 where crj.job_name = we.wip_entity_name
1105 and crj.organization_id = we.organization_id
1106 and crj.repair_line_id = p_repair_line_id
1107 and we.wip_entity_id <> l_wip_entity_id;
1108
1109 l_dummy := null;
1110 OPEN c_count_material(l_wip_entity_id, l_inventory_item_id);
1111 FETCH c_count_material into l_dummy;
1112 CLOSE c_count_material;
1113
1114
1115 if (l_dummy is null) and (l_num_other_jobs = 0) then
1116 --Default Repair Item as Material on Job
1117 l_mtl_txn_dtls_tbl.delete;
1118
1119 l_mtl_txn_dtls_tbl(0).INVENTORY_ITEM_ID :=l_inventory_item_id;
1120 l_mtl_txn_dtls_tbl(0).WIP_ENTITY_ID :=l_wip_entity_id;
1121 l_mtl_txn_dtls_tbl(0).ORGANIZATION_ID :=l_inventory_org_id;
1122 l_mtl_txn_dtls_tbl(0).OPERATION_SEQ_NUM :=1;
1123 l_mtl_txn_dtls_tbl(0).TRANSACTION_QUANTITY :=l_quantity; --repair order qty
1124 l_mtl_txn_dtls_tbl(0).TRANSACTION_UOM :=l_unit_of_measure; --Repair order UOM
1125 l_mtl_txn_dtls_tbl(0).SERIAL_NUMBER :=l_serial_number;
1126 l_mtl_txn_dtls_tbl(0).SUPPLY_SUBINVENTORY :=l_subinventory;
1127 l_mtl_txn_dtls_tbl(0).OBJECT_VERSION_NUMBER := 1;
1128 l_mtl_txn_dtls_tbl(0).NEW_ROW := 'Y';
1129
1130
1131 -- call API to create Repair Actuals header
1132 CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_MTL_TXN_DTLS
1133 ( p_api_version_number => 1.0,
1134 p_init_msg_list => 'T',
1135 p_commit => 'F',
1136 p_validation_level => 1,
1137 p_mtl_txn_dtls_tbl => l_mtl_txn_dtls_tbl,
1138 x_op_created => l_op_created,
1139 x_return_status => x_return_status,
1140 x_msg_count => x_msg_count,
1141 x_msg_data => x_msg_data);
1142 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1143 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
1144 FND_MSG_PUB.ADD;
1145 RAISE FND_API.G_EXC_ERROR;
1146 END IF;
1147 end if;
1148
1149 End if;
1150
1151 ELSE
1152
1153 -- The bill_routings table is not empty
1154 -- Process each bill routng record passed in to generate unique job names for each job being submitted,
1155 -- if needed.
1156 -- Check each record to see if the job_name is already passed in,
1157 -- If it is passed in, it is validated for uniqueness and an error is raised if not unique and we exit out of
1158 -- the API.
1159 -- If job_name is not passed in, then the job_name is generated
1160 -- by appending a sequence generated number to the job_prefix specified in the job header record.
1161 -- For each bill, routings record, once a job name is found or generated, procedure insert_job_header is called
1162 -- to insert the header, bills and routings information into the WIP interface table.
1163
1164 -- Note, for now, a job_name is passed in, only when one Job is submitted and the profile
1165 -- 'Use CSD as Job Prefix' is set to 'N'. However this API supports job names
1166 -- to be passed in, when more than one jobs are submitted.
1167
1168
1169 FOR rt_ctr in p_x_job_bill_routing_tbl.FIRST.. p_x_job_bill_routing_tbl.LAST
1170
1171 LOOP
1172
1173 --if (l_default_ro_item = 'Y') then
1174 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
1175 --End if;
1176
1177 p_x_job_bill_routing_tbl(rt_ctr).group_id := l_group_id;
1178
1179 -- Populate the bill, routing record variable to be passed to procedure insert_job_header
1180
1181 l_job_bill_routing_rec := p_x_job_bill_routing_tbl(rt_ctr) ;
1182
1183
1184 IF l_job_bill_routing_rec.job_name is not NULL then
1185
1186 -- job name is passed in, validate it for Uniqueness,
1187 -- Check if it already exists in WIP_ENTITIES or WIP interface table
1188
1189 validate_job_name ( p_job_name => l_job_bill_routing_rec.Job_Name,
1190 p_organization_id => p_job_header_rec.organization_id,
1191 x_return_status => x_return_status ) ;
1192
1193
1194 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1195
1196 -- Job name either exists in WIP_ENTITIES or in the interface table for the specified
1197 -- organization, So raise an error and exit
1198
1199 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_NAME_EXISTS');
1200 FND_MESSAGE.SET_TOKEN('JOB_NAME', l_job_bill_routing_rec.job_name );
1201 FND_MSG_PUB.ADD;
1202 RAISE FND_API.G_EXC_ERROR;
1203 end if;
1204
1205 ELSE
1206
1207 -- job name is not passed in , generate the Job Name
1208
1209 generate_job_name ( p_job_prefix => p_job_header_rec.job_prefix,
1210 p_organization_id => p_job_header_rec.organization_id,
1211 x_job_name => l_job_bill_routing_rec.job_name );
1212
1213
1214
1215 -- Assign the generated Job name to the current record in job_bill_routing_tbl to be passed
1216 -- to procedure insert_job_header and is also passed back
1217 -- to the calling program.
1218
1219 p_x_job_bill_routing_tbl(rt_ctr).job_name := l_job_bill_routing_rec.job_name;
1220
1221 END IF;
1222
1223
1224 IF ( l_event_level >= l_debug_level ) then
1225 FND_LOG.STRING( l_event_level,
1226 l_mod_name||'beforecallinsert',
1227 'Just before calling insert_job_header');
1228 END IF;
1229
1230 -- Call procedure to insert job header and bills, routing information
1231 -- into wip_job_schedule_interface table
1232 -- All the records inserted into the WIP interface table
1233 -- are passed the same group_id and hence will be processed by one WIP Mass Load
1234 -- request.
1235
1236 insert_job_header( p_job_header_rec => p_job_header_rec,
1237 p_job_bill_routing_rec => l_job_bill_routing_rec,
1238 p_group_id => l_group_id,
1239 x_interface_id => l_interface_id,--nnadig: bug 9263438
1240 x_return_status => x_return_status );
1241
1242
1243 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1245 END IF;
1246
1247
1248 --This code is change to online create the wipjob without use the concurrent program.
1249 WIP_MASSLOAD_PUB.createOneJob( p_interfaceID => l_interface_id,--nnadig: bug 9263438
1250 p_validationLevel => p_validation_level,
1251 x_wipEntityID => l_wip_entity_id,
1252 x_returnStatus => x_return_status,
1253 x_errorMsg => x_msg_data );
1254
1255 if (x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1256 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
1257 FND_MSG_PUB.ADD;
1258 RAISE FND_API.G_EXC_ERROR;
1259 end if;
1260
1261 COMMIT;
1262
1263 --bug#12316893
1264 Delete_Completed_Wip_Records(l_interface_id);
1265 --bug#12316893
1266
1267 CSD_WARRANTY_CONTRACT_PVT.Default_Warranty_Contract(
1268 P_Api_Version_Number => 1.0,
1269 P_Init_Msg_List => FND_API.G_FALSE,
1270 p_commit => FND_API.G_TRUE,
1271 p_validation_level => fnd_api.g_valid_level_none,
1272 P_Repair_Line_Id => p_repair_line_id,
1273 P_Wip_Entity_Id => l_wip_entity_id,
1274 X_Return_Status => x_return_status,
1275 X_Msg_Count => x_msg_count,
1276 X_Msg_Data => x_msg_data
1277 );
1278 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS)
1279 THEN
1280 RAISE Fnd_Api.G_EXC_ERROR;
1281 END IF;
1282
1283
1284 if (l_default_ro_item = 'Y') then
1285
1286 -- swai: bug 7477845/7483291
1287 -- check if there another job existing for this RO. If so, do not default
1288 -- the RO item as a material. Must compare we.wip_entity_id since
1289 -- crj.wip_entity_id may be null (until wip_update is done).
1290 select count(*)
1291 into l_num_other_jobs
1292 from csd_repair_job_xref crj,
1293 wip_entities we
1294 where crj.job_name = we.wip_entity_name
1295 and crj.organization_id = we.organization_id
1296 and crj.repair_line_id = p_repair_line_id
1297 and we.wip_entity_id <> l_wip_entity_id;
1298 -- More than one bill/route could have been submitted at a time.
1299 -- These jobs are processed all at once before rows are inserted into
1300 -- csd_repair_job_xref. Need to count these jobs as well.
1301 l_num_other_jobs := l_num_other_jobs + rt_ctr - p_x_job_bill_routing_tbl.FIRST;
1302 -- end swai: bug 7477845/7483291
1303
1304 l_dummy := null;
1305 OPEN c_count_material(l_wip_entity_id, l_inventory_item_id);
1306 FETCH c_count_material into l_dummy;
1307 CLOSE c_count_material;
1308
1309 if (l_dummy is null) and (l_num_other_jobs = 0) then
1310
1311 --Default Repair Item as Material on Job
1312 l_mtl_txn_dtls_tbl.delete;
1313
1314 OPEN c_get_min_operation_seq(l_wip_entity_id);
1315 FETCH c_get_min_operation_seq into l_operation_seq_num;
1316 CLOSE c_get_min_operation_seq;
1317
1318 if (l_operation_seq_num is null) then
1319 l_operation_seq_num := 1;
1320 end if;
1321
1322 l_mtl_txn_dtls_tbl(0).INVENTORY_ITEM_ID :=l_inventory_item_id;
1323 l_mtl_txn_dtls_tbl(0).WIP_ENTITY_ID :=l_wip_entity_id;
1324 l_mtl_txn_dtls_tbl(0).ORGANIZATION_ID :=l_inventory_org_id;
1325
1326 if (l_job_bill_routing_rec.routing_reference_id is null) then
1327
1328 l_mtl_txn_dtls_tbl(0).OPERATION_SEQ_NUM :=1;
1329 else
1330 l_mtl_txn_dtls_tbl(0).OPERATION_SEQ_NUM :=l_operation_seq_num;
1331 end if;
1332 l_mtl_txn_dtls_tbl(0).TRANSACTION_QUANTITY :=l_quantity; --repair order qty
1333 l_mtl_txn_dtls_tbl(0).TRANSACTION_UOM :=l_unit_of_measure; --Repair order UOM
1334 l_mtl_txn_dtls_tbl(0).SERIAL_NUMBER :=l_serial_number;
1335 l_mtl_txn_dtls_tbl(0).SUPPLY_SUBINVENTORY :=l_subinventory;
1336 l_mtl_txn_dtls_tbl(0).OBJECT_VERSION_NUMBER := 1;
1337 l_mtl_txn_dtls_tbl(0).NEW_ROW := 'Y';
1338
1339
1340 -- call API to create Repair Actuals header
1341 CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_MTL_TXN_DTLS
1342 ( p_api_version_number => 1.0,
1343 p_init_msg_list => 'T',
1344 p_commit => 'F',
1345 p_validation_level => 1,
1346 p_mtl_txn_dtls_tbl => l_mtl_txn_dtls_tbl,
1347 x_op_created => l_op_created,
1348 x_return_status => x_return_status,
1349 x_msg_count => x_msg_count,
1350 x_msg_data => x_msg_data);
1351 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1352 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_GEN_FAILURE');
1353 FND_MSG_PUB.ADD;
1354 RAISE FND_API.G_EXC_ERROR;
1355 END IF;
1356 end if;
1357 -- COMMIT;
1358 End if;
1359
1360
1361 END LOOP ;
1362
1363 END IF;
1364
1365
1366 -- submit request for 'Depot Repair WIP Job Submission' concurrent program, which in turn
1367 -- submits WIP Mass Load, waits for it to complete and then calls the WIP UPDATE
1368 -- program.
1369 -- Here the repair_line_id specified is used to run the WIP Update program for
1370 -- the specified repair_line_id. If Jobs are submitted for more than one repair
1371 -- order, then p_repair_line_id is NULL, In this case, the WIP Update program runs for
1372 -- all eligible repair orders.
1373 /*
1374 if (l_default_ro_item = 'N') then
1375 x_request_id := fnd_request.submit_request (
1376 application => 'CSD',
1377 program => 'CSDJSWIP',
1378 description => NULL,
1379 start_time => NULL,
1380 sub_request => FALSE,
1381 argument1 => TO_CHAR(l_group_id),
1382 argument2 => p_repair_line_id ) ;
1383
1384 IF ( l_stat_level >= l_debug_level ) then
1385 FND_LOG.STRING( l_stat_level,
1386 l_mod_name||'submitdata',
1387 'When calling submit_request, the group_id is '||to_char(l_group_id));
1388 END IF;
1389
1390
1391 IF ( x_request_id = 0 ) THEN
1392
1393 -- request submission failed,
1394 -- add the error message to the message list and exit
1395
1396 FND_MESSAGE.SET_NAME('CSD','CSD_CSDJSWIP_SUBMIT_FAILURE');
1397 FND_MSG_PUB.ADD;
1398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1399
1400
1401 IF ( l_stat_level >= l_debug_level ) then
1402 FND_LOG.STRING( l_stat_level,
1403 l_mod_name||'requestfail',
1404 'Submit request failed');
1405 END IF;
1406
1407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1408
1409 ELSE
1410 --This commit is a must for the successful submission of the concurrent request above
1411 COMMIT;
1412 -- Populate the Out parameter x_group_id
1413 x_group_id := l_group_id;
1414
1415 END IF;
1416 END IF;
1417 */
1418 COMMIT;
1419
1420 IF ( l_proc_level >= l_debug_level ) then
1421 FND_LOG.STRING( l_proc_level,
1422 l_mod_name||'end',
1423 'Leaving Private API submit_jobs');
1424 END IF;
1425
1426 EXCEPTION
1427 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1428 ROLLBACK ;
1429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1430 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1431 p_count => x_msg_count,
1432 p_data => x_msg_data);
1433
1434 IF ( FND_LOG.LEVEL_EXCEPTION >= l_debug_level ) then
1435 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
1436 l_mod_name||'unx_exception',
1437 'G_EXC_UNEXPECTED_ERROR Exception');
1438 END IF;
1439
1440
1441 WHEN FND_API.G_EXC_ERROR THEN
1442 ROLLBACK ;
1443 x_return_status := FND_API.G_RET_STS_ERROR;
1444 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1445 p_count => x_msg_count,
1446 p_data => x_msg_data);
1447
1448 IF ( FND_LOG.LEVEL_ERROR >= l_debug_level ) then
1449 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
1450 l_mod_name||'exc_exception',
1451 'G_EXC_ERROR Exception');
1452 END IF;
1453
1454 WHEN OTHERS THEN
1455 ROLLBACK ;
1456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1457
1458 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1459
1460 -- Add Unexpected Error to Message List, here SQLERRM is used for
1461 -- getting the error
1462
1463 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'CSD_WIP_JOB_PVT',
1464 p_procedure_name => 'submit_jobs');
1465 END IF;
1466
1467 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1468 p_count => x_msg_count,
1469 p_data => x_msg_data);
1470
1471 IF ( FND_LOG.LEVEL_EXCEPTION >= l_debug_level ) then
1472 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
1473 l_mod_name||'others_exception',
1474 'OTHERS Exception');
1475 END IF;
1476
1477 END submit_jobs;
1478
1479
1480
1481
1482 -- This is the executable procedure for concurrent program
1483 -- 'Depot Repair WIP Job Submission'. This program is submitted from
1484 -- submit_jobs API.
1485 -- This procedure submits WIP Mass Load, waits for it to complete successfully,
1486 -- then calls the WIP_Update API to associate new records
1487 -- created in csd_repair_job_xref table with corresponding newly
1488 -- created wip_entity_ids.
1489 -- This concurrent program is passed in group_id and repair_line_id as
1490 -- parameters. If repair_line_id is null, then the WIP Update program is
1491 -- run for all the eligible repair orders, otherwise the WIP Update porgram
1492 -- is run for the specified repair_line_id.
1493
1494
1495 procedure submit_wip_mass_load_conc
1496 (
1497 errbuf OUT NOCOPY VARCHAR2,
1498 retcode OUT NOCOPY VARCHAR2,
1499 p_group_id IN NUMBER,
1500 p_repair_line_id IN NUMBER
1501 )
1502 IS
1503
1504 -- Declare the constants
1505
1506 l_api_version CONSTANT NUMBER := 1.0;
1507 l_procedure_name CONSTANT VARCHAR2(30) := 'Submit_Wip_Mass_Load_Conc' ;
1508
1509 -- Used for standard concurrent progam parameter 'retcode' value
1510 l_success CONSTANT NUMBER := 0;
1511 l_warning CONSTANT NUMBER := 1;
1512 l_error CONSTANT NUMBER := 2;
1513
1514 -- Parameter to WIP Mass Load Concurrent program, specifying full validation
1515 l_full_validation CONSTANT NUMBER := 0;
1516
1517 -- Concurrenr Request Id
1518 l_req_id NUMBER;
1519
1520 -- used for checking the success or failure of call to wait_for_request procedure
1521 l_boolvar BOOLEAN;
1522
1523 -- Concurrent Request Phase, status, message
1524 l_phase VARCHAR2(80);
1525 l_status VARCHAR2(80);
1526 l_dev_phase VARCHAR2(80);
1527 l_dev_status VARCHAR2(80);
1528 l_message VARCHAR2(255);
1529
1530 l_msg_count NUMBER;
1531 l_msg_data VARCHAR2(2000);
1532 l_return_status VARCHAR2(1);
1533
1534 -- variables used for FND_LOG debug messages
1535
1536 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1537 l_stat_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1538 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1539 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1540 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.submit_wip_mass_load_conc.';
1541 -- subhat, bug#13586864 changes.
1542 l_repair_line_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1543 l_operation_seq_num NUMBER := 0;
1544
1545 CURSOR job_details IS
1546 SELECT cr.repair_line_id,
1547 wdj.wip_entity_id,
1548 we.wip_entity_name,
1549 we.organization_id,
1550 cr.inventory_item_id,
1551 msi.lot_control_code,
1552 msi.serial_number_control_code,
1553 msi.revision_qty_control_code,
1554 msi.primary_uom_code,
1555 cr.quantity,
1556 cr.serial_number
1557 FROM csd_repairs cr,
1558 (SELECT * FROM TABLE(CAST(l_repair_line_ids AS JTF_NUMBER_TABLE))) ro_lines,
1559 csd_repair_job_xref crj,
1560 wip_discrete_jobs wdj,
1561 mtl_system_items_b msi,
1562 wip_entities we
1563 WHERE crj.repair_line_id = ro_lines.column_value
1564 AND wdj.primary_item_id = crj.inventory_item_id
1565 AND crj.job_name = we.wip_entity_name
1566 AND crj.organization_id = we.organization_id
1567 AND wdj.wip_entity_id = we.wip_entity_id
1568 AND wdj.organization_id = msi.organization_id
1569 AND cr.inventory_item_id = msi.inventory_item_id
1570 AND cr.repair_line_id = crj.repair_line_id;
1571
1572 l_def_ro_item VARCHAR2(1) := nvl(FND_PROFILE.VALUE('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'), 'N');
1573 l_mtl_details_tbl csd_hv_wip_job_pvt.mtl_txn_dtls_tbl_type;
1574 l_index NUMBER := 0;
1575 x_op_created VARCHAR2(10);
1576 -- end changes bug#13586864
1577 BEGIN
1578
1579 IF ( l_proc_level >= l_debug_level ) then
1580 FND_LOG.STRING( l_proc_level,
1581 l_mod_name||'begin',
1582 'Entering procedure submit_wip_mass_load_conc' );
1583 END IF;
1584
1585 -- select the source line from the wjsi. Source line here is repair line id
1586 -- the process save mtl line API overwrites it to 512.
1587 -- subhat, bug#13586864.
1588 SELECT source_line_id
1589 BULK COLLECT INTO l_repair_line_ids
1590 FROM wip_job_schedule_interface
1591 WHERE group_id = p_group_id;
1592
1593 -- submit concurrent request for WIP Mass Load
1594
1595 -- argument3 specifies whether to print report or not, which when passed in as NULL
1596 -- , defaults to the value of 'Yes'.
1597 -- argument2 specifies the validation level for WIP Mass Load
1598 -- argument1 is the group_id
1599
1600 l_req_id := fnd_request.submit_request (
1601 application => 'WIP',
1602 program => 'WICMLP',
1603 description => NULL,
1604 start_time => NULL,
1605 sub_request => FALSE,
1606 argument1 => TO_CHAR(p_group_id),
1607 argument2 => l_full_validation,
1608 argument3 => NULL );
1609
1610
1611
1612 -- If request submission fails, raise an error
1613
1614 IF (l_req_id = 0 ) THEN
1615
1616 l_msg_data := FND_MESSAGE.GET_STRING('CSD','CSD_WICMLP_SUBMIT_FAILURE');
1617 fnd_file.put_line(fnd_file.log, l_msg_data );
1618
1619 IF ( l_stat_level >= l_debug_level ) then
1620 FND_LOG.STRING( l_stat_level,
1621 l_mod_name||'requestfailure',
1622 'WIP Mass Load Submit request failed' );
1623 END IF;
1624
1625 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1626
1627 ELSE
1628
1629 COMMIT; --This commit is a must for the completion of the concurrent request submission
1630
1631 IF ( l_stat_level >= l_debug_level ) then
1632 FND_LOG.STRING( l_stat_level,
1633 l_mod_name||'beforewait',
1634 'After commit and before wait for request' );
1635 END IF;
1636
1637 -- wait for the execution result of WIP Mass Load
1638
1639 -- Interval is specified in seconds and is the number of seconds to wait between checks,
1640 -- max_wait is also specified in seconds and will wait indefinitely, when specified as 0,
1641 -- dev_phase and dev_status are the developer versions of phase and status, which can be used
1642 -- for logic comparisons
1643
1644 l_boolvar:= fnd_concurrent.wait_for_request
1645 (
1646 request_id => l_req_id,
1647 interval => 15,
1648 max_wait => 0,
1649 phase => l_phase,
1650 status => l_status,
1651 dev_phase => l_dev_phase,
1652 dev_status => l_dev_status,
1653 message => l_message);
1654
1655 -- If wait for WIP Mass Load request fails, raise an error
1656
1657 IF NOT l_boolvar THEN
1658
1659 l_msg_data := FND_MESSAGE.GET_STRING('CSD','CSD_WICMLP_WAIT_FAILURE');
1660 fnd_file.put_line(fnd_file.log, l_msg_data );
1661
1662
1663 IF ( l_stat_level >= l_debug_level ) then
1664 FND_LOG.STRING( l_stat_level,
1665 l_mod_name||'waitfailure',
1666 'Wait for request failed');
1667 END IF;
1668
1669 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1670
1671
1672 ELSIF (l_dev_phase = 'COMPLETE' AND l_dev_status = 'NORMAL') THEN
1673
1674 -- WIP Mass Load completed successfully
1675
1676 IF ( l_event_level >= l_debug_level ) then
1677 FND_LOG.STRING( l_event_level,
1678 l_mod_name||'beforeupdatecall',
1679 'Before Call to depot_wip_update');
1680 END IF;
1681
1682
1683 -- Call the WIP Update program
1684
1685 -- When Repair Jobs are submitted to WIP Mass Load, a record is inserted into
1686 -- CSD_REPAIR_JOB_XREF for each combination of repair_line_id and repair Job.
1687 -- Once WIP Mass Load successfully completes, WIP_UPDATE API is called here to update
1688 -- the newly inserted records in CSD_REPAIR_JOB_XREF with the wip_entity_id of the
1689 -- corresponding jobs from WIP.
1690
1691 -- Here p_upd_job_completion is specified as 'N'
1692 -- so that only the WIP Creation Update program is run, the WIP Completion Update program
1693 -- is not run in this case.
1694
1695 -- If p_repair_line_id is passed in as NULL, then WIP_UPDATE is run for all the
1696 -- eligible repair_line_id values. When Repair Jobs are submitted for more than
1697 -- one repair order, then this is the case, that is, p_repair_line_id is null.
1698
1699 CSD_UPDATE_PROGRAMS_PVT.WIP_UPDATE
1700 ( p_api_version => l_api_version,
1701 p_commit => FND_API.G_TRUE,
1702 p_init_msg_list => FND_API.G_TRUE,
1703 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1704 x_return_status => l_return_status,
1705 x_msg_count => l_msg_count,
1706 x_msg_data => l_msg_data,
1707 p_upd_job_completion => 'N',
1708 p_repair_line_id => p_repair_line_id );
1709
1710 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1711
1712 -- If return status is not success, write the error messages to
1713 -- the concurrent request log file and raise exception to exit
1714
1715 IF l_msg_count = 1 THEN
1716
1717 fnd_file.put_line( fnd_file.log,l_msg_data);
1718
1719 ELSIF l_msg_count > 1 THEN
1720
1721 -- If the message count is greater than 1, loop through the
1722 -- message list, retrieve the messages and write it to the log file
1723
1724 FOR l_msg_ctr IN 1..l_msg_count
1725 LOOP
1726 l_msg_data := fnd_msg_pub.get(l_msg_ctr, FND_API.G_FALSE );
1727 fnd_file.put_line( fnd_file.log, l_msg_data);
1728 END LOOP;
1729
1730 END IF;
1731
1732 IF ( l_stat_level >= l_debug_level ) then
1733 FND_LOG.STRING( l_stat_level,
1734 l_mod_name||'updatecallerror',
1735 'CSD_UPDATE_PROGRAMS_PVT.WIP_UPDATE call returned error');
1736 END IF;
1737
1738 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1739
1740 ELSE
1741
1742 -- If return status is success, return concurrent program success code
1743
1744 errbuf := '';
1745 retcode := l_success;
1746
1747 -- subhat changes, bug#13586864
1748 -- after the mass load completion, we need to create repair item as default
1749 -- material on the job.
1750
1751 IF ( l_stat_level >= l_debug_level ) then
1752 FND_LOG.STRING(l_stat_level,l_mod_name,
1753 'Checking the profile to default repair item on the job. Profile value is :'||l_def_ro_item);
1754 END IF;
1755
1756 IF l_def_ro_item = 'Y'
1757 THEN
1758 FOR i IN job_details
1759 LOOP
1760 IF ( l_stat_level >= l_debug_level ) then
1761 FND_LOG.STRING(l_stat_level,l_mod_name,
1762 'Inside the cursor for loop');
1763 END IF;
1764 l_index := l_index + 1;
1765
1766 IF l_operation_seq_num = 0
1767 THEN
1768 BEGIN
1769 SELECT operation_seq_num
1770 INTO l_operation_seq_num
1771 FROM wip_operations
1772 WHERE previous_operation_seq_num IS NULL
1773 AND wip_entity_id = i.wip_entity_id;
1774 EXCEPTION
1775 WHEN NO_DATA_FOUND THEN
1776 l_operation_seq_num := 1;
1777 END;
1778 END IF;
1779 l_mtl_details_tbl(l_index).inventory_item_id := i.inventory_item_id;
1780 l_mtl_details_tbl(l_index).lot_control_code := i.lot_control_code;
1781 l_mtl_details_tbl(l_index).serial_number_control_code := i.serial_number_control_code;
1782 l_mtl_details_tbl(l_index).transaction_uom := i.primary_uom_code;
1783 l_mtl_details_tbl(l_index).revision_qty_control_code := i.revision_qty_control_code;
1784 l_mtl_details_tbl(l_index).transaction_quantity := i.quantity;
1785 l_mtl_details_tbl(l_index).organization_id := i.organization_id;
1786 l_mtl_details_tbl(l_index).new_row := 'Y';
1787 l_mtl_details_tbl(l_index).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
1788 l_mtl_details_tbl(l_index).wip_entity_id := i.wip_entity_id;
1789 l_mtl_details_tbl(l_index).operation_seq_num := l_operation_seq_num;
1790 END LOOP;
1791
1792 -- call the API to save the mtl details.
1793 IF ( l_stat_level >= l_debug_level ) then
1794 FND_LOG.STRING(l_stat_level,l_mod_name,
1795 'calling CSD_HV_WIP_JOB_PVT.process_save_mtl_txn_dtls to create the material requirements.');
1796 FND_LOG.STRING(l_stat_level,l_mod_name,
1797 'The number of records to be processed is :'||l_mtl_details_tbl.COUNT);
1798 END IF;
1799 IF l_mtl_details_tbl.COUNT > 0
1800 THEN
1801 CSD_HV_WIP_JOB_PVT.process_save_mtl_txn_dtls
1802 (p_api_version_number => 1.0,
1803 p_init_msg_list => fnd_api.g_false,
1804 p_commit => fnd_api.g_false,
1805 p_validation_level => 100,
1806 x_return_status => l_return_status,
1807 x_msg_count => l_msg_count,
1808 x_msg_data => l_msg_data,
1809 p_mtl_txn_dtls_tbl => l_mtl_details_tbl,
1810 x_op_created => x_op_created);
1811
1812 -- we do not want to raise the error message, thus erroring out the CP.
1813 -- rather we should be possibly writing the errored records into the CP log.
1814 IF ( l_stat_level >= l_debug_level ) then
1815 FND_LOG.STRING(l_stat_level,l_mod_name,
1816 'Return from CSD_HV_WIP_JOB_PVT.process_save_mtl_txn_dtls. Return status '||l_return_status);
1817 FND_LOG.STRING(l_stat_level,l_mod_name,
1818 'Return message: '||l_msg_data);
1819 END IF;
1820 END IF;
1821 END IF;
1822 -- end subhat changes, bug#13586864
1823 END IF;
1824
1825 ELSE
1826
1827 -- WIP Mass Load did not complete successfully, write error message to log file
1828 -- and raise exception to exit
1829
1830 l_msg_data := FND_MESSAGE.GET_STRING('CSD','CSD_WICMLP_COMPLETION_FAILURE');
1831 fnd_file.put_line(fnd_file.log,l_msg_data );
1832
1833 IF ( l_stat_level >= l_debug_level ) then
1834 FND_LOG.STRING( l_stat_level,
1835 l_mod_name||'completionfailure',
1836 'WIP Mass Load did not Complete Successfully');
1837 END IF;
1838
1839 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1840
1841 END IF;
1842 END IF;
1843
1844 IF ( l_proc_level >= l_debug_level ) then
1845 FND_LOG.STRING( l_proc_level,
1846 l_mod_name||'end',
1847 'Leaving procedure submit_wip_mass_load_conc');
1848 END IF;
1849
1850
1851 EXCEPTION
1852
1853
1854 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1855
1856 -- write message to log file indicating the failure of the concurrent program,
1857 -- return error retcode
1858
1859 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_CSDJSWIP_FAILURE');
1860 retcode := l_error;
1861
1862 WHEN FND_API.G_EXC_ERROR THEN
1863
1864 -- write message to log file indicating the failure of the concurrent program,
1865 -- return error retcode
1866
1867 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_CSDJSWIP_FAILURE');
1868 retcode := l_error;
1869
1870 WHEN OTHERS THEN
1871
1872 -- Add Unexpected Error to Message List, here SQLERRM is used for
1873 -- getting the error
1874
1875 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME ,
1876 p_procedure_name => l_procedure_name );
1877
1878 -- Get the count of the Messages from the message list, if the count is 1
1879 -- get the message as well
1880
1881 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1882 p_count => l_msg_count,
1883 p_data => l_msg_data);
1884
1885 IF l_msg_count = 1 THEN
1886
1887 fnd_file.put_line( fnd_file.log, l_msg_data);
1888
1889 ELSIF l_msg_count > 1 THEN
1890
1891 -- If the message count is greater than 1, loop through the
1892 -- message list, retrieve the messages and write it to the log file
1893
1894 FOR l_msg_ctr IN 1..l_msg_count
1895 LOOP
1896 l_msg_data := fnd_msg_pub.get(l_msg_ctr, FND_API.G_FALSE );
1897 fnd_file.put_line( fnd_file.log, l_msg_data);
1898 END LOOP;
1899
1900 END IF;
1901
1902 -- write message to log file indicating the failure of the concurrent program,
1903 -- return error retcode
1904
1905 errbuf := FND_MESSAGE.GET_STRING('CSD','CSD_CSDJSWIP_FAILURE');
1906 retcode := l_error ;
1907
1908 IF ( FND_LOG.LEVEL_EXCEPTION >= l_debug_level ) then
1909 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
1910 l_mod_name||'others_exception',
1911 'OTHERS Exception');
1912 END IF;
1913
1914 end submit_wip_mass_load_conc;
1915
1916
1917
1918
1919 -- This function checks if the discrete manufacturing patchset level is
1920 -- at j or beyond and if so, returns true. For now, this is used from
1921 -- Repair Jobs tab, when COMPLETE_JOB button is pressed. If the patchset level
1922 -- is at j or beyond, then the new WIP Completion form is called, hence
1923 -- new parameters are passed, If not, the old WIP Completion form is called, hence
1924 -- the new parameters are not passed. The new parameters are wip_entity_id and
1925 -- transaction_quantity which are used to default the WIP job information, when the
1926 -- WIP Completion form opens.
1927
1928 FUNCTION is_dmf_patchset_level_j RETURN BOOLEAN IS
1929 BEGIN
1930 IF (wip_constants.dmf_patchset_level >= wip_constants.dmf_patchset_j_value) THEN
1931 RETURN TRUE;
1932 ELSE
1933 RETURN FALSE;
1934 END IF;
1935 END;
1936
1937 /***************
1938 -- 12.1 create job from estimates -- subhat
1939 -- This procedure creates WIP jobs from estimates tab. The procedure defaults the job header,
1940 -- checks if the repair estimate has a routing associated, if so passes in the routing information
1941 -- to the WIP API. When successfully finished, the procedure will return WIP Entity ID and Job Name
1942 -- to the calling routine.
1943 Code change history:
1944 -- 4/2/2010 120.13.12010000.3 nnadig: Bug fix 9263438,
1945 Use wip_interface_s for interface_id instead of wip_job_schedule_interface_s
1946 ***************/
1947 PROCEDURE create_job_from_estimate(
1948 p_api_version_number IN NUMBER,
1949 p_init_msg_list IN VARCHAR2 ,
1950 p_commit IN VARCHAR2 ,
1951 p_validation_level IN NUMBER,
1952 x_return_status OUT NOCOPY VARCHAR2,
1953 x_msg_count OUT NOCOPY NUMBER,
1954 x_msg_data OUT NOCOPY VARCHAR2,
1955 x_job_name OUT NOCOPY VARCHAR2,
1956 x_wip_entity_id OUT NOCOPY NUMBER,
1957 p_ESTM_JOB_DETLS_REC_TYPE IN ESTM_JOB_DETLS_REC_TYPE
1958 ) IS
1959
1960 -- Job Record to hold the Job header, bills and routing information being inserted
1961 -- into wip_job_schedule_interface
1962
1963 l_job_header_rec wip_job_schedule_interface%ROWTYPE;
1964
1965 lc_api_name CONSTANT VARCHAR2(30) := 'CREATE_WIP_JOB';
1966 lc_api_version_number CONSTANT NUMBER := 1.0;
1967
1968 -- WIP Job Status Lookup Codes for Released and Unreleased status, --- The Lookup Type is WIP_JOB_STATUS
1969
1970 lc_released_status_code CONSTANT NUMBER := 3;
1971 lc_unreleased_status_code CONSTANT NUMBER := 1;
1972
1973 -- Non Standard Discrete Job Load Type
1974 lc_non_standard_load_type CONSTANT NUMBER := 4;
1975
1976
1977 -- Constants used for FND_LOG debug messages
1978
1979 lc_mod_name CONSTANT VARCHAR2(2000) := 'csd.plsql.csd_wip_job_pvt.create_job_from_estimate';
1980
1981
1982
1983 l_user_id NUMBER;
1984 l_repair_xref_id NUMBER;
1985 l_rep_hist_id NUMBER;
1986
1987 l_job_prefix VARCHAR2(80);
1988 -- l_wip_entity_id NUMBER;
1989
1990
1991
1992 BEGIN
1993
1994 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1995 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
1996 lc_mod_name||'begin',
1997 'Entering Private API create_job_from_estimate');
1998 END IF;
1999
2000 -- Standard Start of API savepoint
2001 SAVEPOINT CREATE_WIP_JOB_PVT;
2002 -- Standard call to check for call compatibility.
2003 IF NOT FND_API.Compatible_API_Call
2004 (lc_api_version_number,
2005 p_api_version_number,
2006 lc_api_name,
2007 G_PKG_NAME)
2008 THEN
2009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2010 END IF;
2011
2012 -- Initialize message list if p_init_msg_list is set to TRUE
2013 IF FND_API.to_boolean(p_init_msg_list) THEN
2014 FND_MSG_PUB.initialize;
2015 END IF;
2016
2017 -- Initialize API return status to success
2018 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2019
2020 -- initialize the job header rec.
2021
2022 l_job_header_rec.organization_id :=
2023 fnd_profile.value('CSD_DEF_REP_INV_ORG');
2024
2025 IF l_job_header_rec.organization_id is NULL THEN
2026
2027 FND_MESSAGE.SET_NAME('CSD','CSD_DEF_REP_INV_NULL');
2028 FND_MSG_PUB.ADD;
2029 RAISE FND_API.G_EXC_ERROR;
2030 END IF;
2031
2032
2033 l_job_prefix := fnd_profile.value('CSD_DEFAULT_JOB_PREFIX');
2034
2035 -- If l_job_prefix is null, throw an error and return;
2036
2037
2038 IF l_job_prefix IS NULL THEN
2039
2040 FND_MESSAGE.SET_NAME('CSD','CSD_JOB_PREFIX_NULL');
2041 FND_MSG_PUB.ADD;
2042 RAISE FND_API.G_EXC_ERROR;
2043 END IF;
2044
2045 l_job_header_rec.class_code :=
2046 fnd_profile.value('CSD_DEF_WIP_ACCOUNTING_CLASS');
2047
2048 IF l_job_header_rec.class_code is NULL THEN
2049
2050 FND_MESSAGE.SET_NAME('CSD','CSD_CLASS_CODE_NULL');
2051 FND_MSG_PUB.ADD;
2052 RAISE FND_API.G_EXC_ERROR;
2053 END IF;
2054
2055 -- Assign the WIP Job Status lookup codes corresponding to Released -- and Unreleased Job status,
2056 -- to be passed for WIP Interface Table
2057
2058 IF fnd_profile.value('CSD_DEFAULT_JOB_STATUS') = 'RELEASED' THEN
2059
2060 l_job_header_rec.status_type := lc_released_status_code ;
2061
2062 ELSIF NVL( fnd_profile.value('CSD_DEFAULT_JOB_STATUS'), 'UNRELEASED' ) = 'UNRELEASED' THEN
2063
2064 l_job_header_rec.status_type := lc_unreleased_status_code;
2065 END IF;
2066
2067
2068 l_job_header_rec.load_type := lc_non_standard_load_type;
2069
2070 l_job_header_rec.first_unit_start_date := SYSDATE;
2071 l_job_header_rec.last_unit_completion_date := SYSDATE;
2072
2073 l_job_header_rec.start_quantity := p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity;
2074
2075 -- If the profile CSD: Default WIP MRP Net Qty to Zero is set to
2076 -- null / 'N' then net_quantity = start_quantity else if the
2077 -- profile is set to 'Y' then net_quantity = 0
2078 IF ( nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N') = 'N' ) THEN
2079 l_job_header_rec.net_quantity := p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity;
2080 ELSIF ( fnd_profile.value('CSD_WIP_MRP_NET_QTY') = 'Y' ) THEN
2081 l_job_header_rec.net_quantity := 0;
2082 END IF;
2083
2084
2085 l_job_header_rec.primary_item_id :=
2086 p_ESTM_JOB_DETLS_REC_TYPE.inventory_item_id ;
2087
2088
2089 -- Get the Group_id to be used for WIP Create Job,
2090
2091 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_job_header_rec.group_id FROM dual;
2092
2093 -- Bug 9263438: Use wip_interface_s for interface_id instead of wip_job_schedule_interface_s
2094 SELECT wip_interface_s.NEXTVAL INTO l_job_header_rec.interface_id FROM dual;
2095
2096
2097 generate_job_name ( p_job_prefix =>l_job_prefix,
2098 p_organization_id => l_job_header_rec.organization_id,
2099 x_job_name => l_job_header_rec.job_name );
2100
2101
2102 x_job_name := l_job_header_rec.job_name;
2103
2104 -- associate the projects integration parameters.
2105
2106 l_job_header_rec.project_id := p_ESTM_JOB_DETLS_REC_TYPE.project_id;
2107 l_job_header_rec.task_id := p_ESTM_JOB_DETLS_REC_TYPE.task_id;
2108 l_job_header_rec.end_item_unit_number := p_ESTM_JOB_DETLS_REC_TYPE.unit_number;
2109
2110 -- check if the estimate has a routing associated with it. If so, associate the routing information.
2111
2112 BEGIN
2113 SELECT assembly_item_id INTO l_job_header_rec.routing_reference_id
2114 FROM csd_repair_estimate cre,bom_operational_routings bor
2115 WHERE cre.repair_line_id = p_ESTM_JOB_DETLS_REC_TYPE.repair_line_id
2116 AND cre.routing_sequence_id = bor.routing_sequence_id;
2117 EXCEPTION
2118 WHEN NO_DATA_FOUND THEN
2119 NULL; -- do nothing. Routing information is not created for the job.
2120 END;
2121
2122 IF l_job_header_rec.routing_reference_id IS NOT NULL THEN
2123
2124 BEGIN
2125 SELECT completion_subinventory,
2126 completion_locator_id
2127 into l_job_header_rec.completion_subinventory,
2128 l_job_header_rec.completion_locator_id
2129 FROM
2130 bom_operational_routings where
2131 assembly_item_id = l_job_header_rec.routing_reference_id
2132 and organization_id = l_job_header_rec.organization_id
2133 and nvl( alternate_routing_designator , -1 ) =
2134 nvl( l_job_header_rec.alternate_routing_designator , -1) ;
2135
2136 EXCEPTION
2137 WHEN NO_DATA_FOUND THEN
2138 NULL;
2139 END;
2140 END IF;
2141
2142 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2143 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2144 lc_mod_name||'beforecallinsert',
2145 'Just before calling insert_job_header');
2146 END IF;
2147
2148 -- Call procedure to insert job header and bills, routing
2149 -- information
2150 -- into wip_job_schedule_interface table
2151
2152
2153 insert_job_header( p_job_header_rec =>
2154 l_job_header_rec,
2155 x_return_status =>
2156 x_return_status );
2157
2158
2159 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2160 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2161 END IF;
2162
2163
2164 -- CALL WIP API to process records in wip interface table,
2165 --If API fails, Raise error, rollback and return
2166
2167 -- Call WIP Mass Load API
2168
2169 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2170 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2171 lc_mod_name||'beforecallcreateonejob',
2172 'Just before calling WIP_MASSLOAD_PUB.createOneJob');
2173 END IF;
2174
2175 WIP_MASSLOAD_PUB.createOneJob( p_interfaceID => l_job_header_rec.interface_id,--bug 9263438
2176 p_validationLevel => p_validation_level,
2177 x_wipEntityID => x_wip_entity_id,
2178 x_returnStatus => x_return_status,
2179 x_errorMsg => x_msg_data );
2180
2181
2182 If (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2183
2184 ROLLBACK to CREATE_WIP_JOB_PVT ;
2185 RETURN;
2186
2187 END IF;
2188
2189 --bug#12316893
2190 COMMIT;
2191
2192 Delete_Completed_Wip_Records(l_job_header_rec.interface_id);
2193 --bug#12316893
2194
2195
2196 CSD_WARRANTY_CONTRACT_PVT.Default_Warranty_Contract(
2197 P_Api_Version_Number => 1.0,
2198 P_Init_Msg_List => FND_API.G_FALSE,
2199 p_commit => FND_API.G_TRUE,
2200 p_validation_level => fnd_api.g_valid_level_none,
2201 P_Repair_Line_Id => p_ESTM_JOB_DETLS_REC_TYPE.repair_line_id,
2202 P_Wip_Entity_Id => x_wip_entity_id,
2203 X_Return_Status => x_return_status,
2204 X_Msg_Count => x_msg_count,
2205 X_Msg_Data => x_msg_data
2206 );
2207 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS)
2208 THEN
2209 RAISE Fnd_Api.G_EXC_ERROR;
2210 END IF;
2211
2212
2213 -- call procedures to insert a row in csd_repair_job_xref
2214 -- and csd_repair_history tables for the job created.
2215
2216 L_user_id := fnd_global.user_id;
2217
2218 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2219 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2220 lc_mod_name||'beforecallxrefwrite',
2221 'Just before calling csd_to_form_repair_job_xref.validate_and_write');
2222 END IF;
2223
2224
2225
2226 csd_to_form_repair_job_xref.validate_and_write(
2227 p_api_version_number => lc_api_version_number,
2228 p_init_msg_list => FND_API.G_FALSE,
2229 p_commit => FND_API.G_FALSE,
2230 p_validation_level => NULL,
2231 p_action_code => 0,
2232 px_repair_job_xref_id => l_repair_xref_id,
2233 p_created_by => l_user_id,
2234 p_creation_date => SYSDATE,
2235 p_last_updated_by => l_user_id,
2236 p_last_update_date => SYSDATE,
2237 p_last_update_login => l_user_id,
2238 p_repair_line_id => p_ESTM_JOB_DETLS_REC_TYPE.repair_line_id,
2239 p_wip_entity_id => x_wip_entity_id,
2240 p_group_id => l_job_header_rec.group_id,
2241 p_organization_id => l_job_header_rec.organization_id,
2242 p_quantity => p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity,
2243 p_INVENTORY_ITEM_ID => l_job_header_rec.primary_item_id,
2244 p_ITEM_REVISION => null,
2245 p_OBJECT_VERSION_NUMBER => NULL,
2246 p_attribute_category => NULL,
2247 p_attribute1 => NULL,
2248 p_attribute2 => NULL,
2249 p_attribute3 => NULL,
2250 p_attribute4 => NULL,
2251 p_attribute5 => NULL,
2252 p_attribute6 => NULL,
2253 p_attribute7 => NULL,
2254 p_attribute8 => NULL,
2255 p_attribute9 => NULL,
2256 p_attribute10 => NULL,
2257 p_attribute11 => NULL,
2258 p_attribute12 => NULL,
2259 p_attribute13 => NULL,
2260 p_attribute14 => NULL,
2261 p_attribute15 => NULL,
2262 p_quantity_completed => NULL,
2263 p_job_name => l_job_header_rec.job_name,
2264 p_source_type_code => 'MANUAL', -- bug fix 5763350
2265 p_source_id1 => NULL,
2266 p_ro_service_code_id => NULL,
2267 x_return_status => x_return_status,
2268 x_msg_count => x_msg_count,
2269 x_msg_data => x_msg_data);
2270
2271
2272 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2273 ROLLBACK to CREATE_WIP_JOB_PVT ;
2274
2275 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2276 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
2277 lc_mod_name||'exc_exception',
2278 'G_EXC_ERROR Exception');
2279 END IF;
2280
2281 RETURN;
2282
2283 END IF;
2284
2285
2286 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2287 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2288 lc_mod_name||'beforecallhistwrite',
2289 'Just before calling csd_to_form_repair_history.validate_and_write');
2290 END IF;
2291
2292
2293
2294 csd_to_form_repair_history.validate_and_write(
2295 p_api_version_number => lc_api_version_number,
2296 p_init_msg_list => FND_API.G_FALSE,
2297 p_commit => FND_API.G_FALSE,
2298 p_validation_level => NULL,
2299 p_action_code => 0,
2300 px_repair_history_id => l_rep_hist_id,
2301 p_OBJECT_VERSION_NUMBER => NULL,
2302 p_request_id => NULL,
2303 p_program_id => NULL,
2304 p_program_application_id => NULL,
2305 p_program_update_date => NULL,
2306 p_created_by => l_user_id,
2307 p_creation_date => SYSDATE,
2308 p_last_updated_by => l_user_id,
2309 p_last_update_date => SYSDATE,
2310 p_repair_line_id => p_ESTM_JOB_DETLS_REC_TYPE.repair_line_id,
2311 p_event_code => 'JS',
2312 p_event_date => SYSDATE,
2313 p_quantity => p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity,
2314 p_paramn1 => x_wip_entity_id,
2315 p_paramn2 => l_job_header_rec.organization_id,
2316 p_paramn3 => NULL,
2317 p_paramn4 => NULL,
2318 p_paramn5 => p_ESTM_JOB_DETLS_REC_TYPE.repair_quantity,
2319 p_paramn6 => NULL,
2320 p_paramn8 => NULL,
2321 p_paramn9 => NULL,
2322 p_paramn10 => NULL,
2323 p_paramc1 => l_job_header_rec.job_name,
2324 p_paramc2 => NULL,
2325 p_paramc3 => NULL,
2326 p_paramc4 => NULL,
2327 p_paramc5 => NULL,
2328 p_paramc6 => NULL,
2329 p_paramc7 => NULL,
2330 p_paramc8 => NULL,
2331 p_paramc9 => NULL,
2332 p_paramc10 => NULL,
2333 p_paramd1 => NULL ,
2334 p_paramd2 => NULL ,
2335 p_paramd3 => NULL ,
2336 p_paramd4 => NULL ,
2337 p_paramd5 => SYSDATE,
2338 p_paramd6 => NULL ,
2339 p_paramd7 => NULL ,
2340 p_paramd8 => NULL ,
2341 p_paramd9 => NULL ,
2342 p_paramd10 => NULL ,
2343 p_attribute_category => NULL ,
2344 p_attribute1 => NULL ,
2345 p_attribute2 => NULL ,
2346 p_attribute3 => NULL ,
2347 p_attribute4 => NULL ,
2348 p_attribute5 => NULL ,
2349 p_attribute6 => NULL ,
2350 p_attribute7 => NULL ,
2351 p_attribute8 => NULL ,
2352 p_attribute9 => NULL ,
2353 p_attribute10 => NULL ,
2354 p_attribute11 => NULL ,
2355 p_attribute12 => NULL ,
2356 p_attribute13 => NULL ,
2357 p_attribute14 => NULL ,
2358 p_attribute15 => NULL ,
2359 p_last_update_login => l_user_id,
2360 x_return_status => x_return_status,
2361 x_msg_count => x_msg_count,
2362 x_msg_data => x_msg_data);
2363
2364 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2365 ROLLBACK to CREATE_WIP_JOB_PVT ;
2366
2367 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2368 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
2369 lc_mod_name||'exc_exception',
2370 'G_EXC_ERROR Exception');
2371 END IF;
2372
2373 RETURN;
2374
2375 END IF;
2376
2377 -- Standard check for p_commit
2378 IF FND_API.to_Boolean( p_commit )
2379 THEN
2380 COMMIT WORK;
2381 END IF;
2382
2383
2384 EXCEPTION
2385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2386 ROLLBACK to CREATE_WIP_JOB_PVT ;
2387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2388
2389 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2390 p_count => x_msg_count,
2391 p_data => x_msg_data);
2392
2393 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2394 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
2395 lc_mod_name||'unx_exception',
2396 'G_EXC_UNEXPECTED_ERROR Exception');
2397 END IF;
2398
2399
2400 WHEN FND_API.G_EXC_ERROR THEN
2401 ROLLBACK to CREATE_WIP_JOB_PVT ;
2402 x_return_status := FND_API.G_RET_STS_ERROR;
2403
2404
2405 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2406 p_count => x_msg_count,
2407 p_data => x_msg_data);
2408
2409 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2410 FND_LOG.STRING( FND_LOG.LEVEL_ERROR,
2411 lc_mod_name||'exc_exception',
2412 'G_EXC_ERROR Exception');
2413 END IF;
2414
2415 WHEN OTHERS THEN
2416 ROLLBACK to CREATE_WIP_JOB_PVT ;
2417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2418
2419 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2420
2421 -- Add Unexpected Error to Message List, here SQLERRM is used for
2422 -- getting the error
2423
2424 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
2425 p_procedure_name => lc_api_name );
2426 END IF;
2427
2428 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2429 p_count => x_msg_count,
2430 p_data => x_msg_data);
2431
2432 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2433 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION,
2434 lc_mod_name||'others_exception',
2435 'OTHERS Exception');
2436 END IF;
2437
2438
2439 END create_job_from_estimate;
2440
2441 -- This procedure creates the save materials transactions of HVR transactions package.
2442 -- This procedure consolidates the eligible estimate lines to be transferred as the material
2443 -- requirements. The material requirements are created but not issued.
2444
2445 PROCEDURE matrl_reqmnt_from_estms(
2446 p_api_version_number IN NUMBER,
2447 p_init_msg_list IN VARCHAR2,
2448 p_commit IN VARCHAR2,
2449 p_validation_level IN NUMBER,
2450 x_return_status OUT NOCOPY VARCHAR2,
2451 x_msg_count OUT NOCOPY NUMBER,
2452 x_msg_data OUT NOCOPY VARCHAR2,
2453 x_op_created OUT NOCOPY VARCHAR2,
2454 p_rep_line_id IN NUMBER,
2455 p_wip_entity_id IN NUMBER
2456 ) IS
2457
2458 lc_mod_name VARCHAR2(200) := 'csd.plsql.csd_wip_job_pvt.create_matrl_reqmnt_from_estimates';
2459
2460 --table type to hold the material requirement line.
2461
2462 x_mtl_txn_dtls_tab_type CSD_HV_WIP_JOB_PVT.mtl_txn_dtls_tbl_type;
2463
2464
2465 BEGIN
2466
2467 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2468 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
2469 lc_mod_name||'begin',
2470 'Entering Private API create_job_from_estimate');
2471 END IF;
2472
2473 -- Initialize message list if p_init_msg_list is set to TRUE
2474 IF FND_API.to_boolean(p_init_msg_list) THEN
2475 FND_MSG_PUB.initialize;
2476 END IF;
2477
2478 -- Initialize API return status to success
2479 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2480
2481 -- get the material transactions table type
2482
2483 import_estms_to_wipjob(p_rep_line_id,p_wip_entity_id,x_mtl_txn_dtls_tab_type);
2484
2485 -- if the table type contains 1 or more records we will pass it to the
2486 -- hvr API to create the material requirements(we just create material requirements, we dont issue -- materials).
2487
2488 IF x_mtl_txn_dtls_tab_type.COUNT >= 1 THEN
2489
2490 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2491 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE,
2492 lc_mod_name||'begin',
2493 'Calling HVR API to create material requirements');
2494 END IF;
2495
2496 -- calling HVR api with the material requirments details.
2497 CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_MTL_TXN_DTLS(p_api_version_number => 1.0,
2498 p_init_msg_list => p_init_msg_list,
2499 p_commit => p_commit,
2500 p_validation_level => p_validation_level,
2501 x_return_status => x_return_status,
2502 x_msg_count => x_msg_count,
2503 x_msg_data => x_msg_data,
2504 p_mtl_txn_dtls_tbl => x_mtl_txn_dtls_tab_type,
2505 x_op_created => x_op_created);
2506
2507
2508 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2509 RAISE fnd_api.g_exc_error;
2510 END IF;
2511 END IF;
2512 COMMIT WORK;
2513 EXCEPTION
2514 WHEN FND_API.G_EXC_ERROR THEN
2515 x_return_status := fnd_api.G_RET_STS_ERROR;
2516 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2517 p_count => x_msg_count,
2518 p_data => x_msg_data );
2519 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2522 p_count => x_msg_count,
2523 p_data => x_msg_data );
2524 WHEN OTHERS THEN
2525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2526 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2527 p_count => x_msg_count,
2528 p_data => x_msg_data );
2529 END matrl_reqmnt_from_estms;
2530
2531 -- 12.1.3. Mass create jobs functionality for HW and many more customers.
2532 -- Returns a table of concatenated job names delimited by ",". It also
2533 -- returns the concurrent request id for depot repair create jobs program.
2534 -- rest of the paramters are self explanatory. Pass a valid value or null.
2535 -- do NOT pass FND_API.G_MISS_* for unknown values.
2536
2537 PROCEDURE bulk_submit_jobs(
2538 p_api_version IN NUMBER,
2539 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2540 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2541 x_return_status OUT NOCOPY VARCHAR2,
2542 x_msg_count OUT NOCOPY NUMBER,
2543 x_msg_data OUT NOCOPY VARCHAR2,
2544 px_job_names IN OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2545 p_repair_line_ids IN JTF_NUMBER_TABLE,
2546 p_inventory_item_ids IN JTF_NUMBER_TABLE,
2547 p_quantities IN JTF_NUMBER_TABLE,
2548 p_inventory_org_id IN NUMBER,
2549 p_start_date IN DATE,
2550 p_end_date IN DATE,
2551 p_accouting_class IN VARCHAR2,
2552 p_job_type IN NUMBER DEFAULT 1,
2553 p_bill_reference_id IN NUMBER,
2554 p_route_reference_id IN NUMBER,
2555 p_alternate_bom IN VARCHAR2,
2556 p_alternate_route IN VARCHAR2,
2557 p_service_code_id IN NUMBER,
2558 p_upgrade_to_item_id IN NUMBER DEFAULT NULL,
2559 p_job_status IN NUMBER,
2560 x_request_id OUT NOCOPY NUMBER,
2561 p_scheduling_priority IN NUMBER DEFAULT NULL
2562 )
2563 IS
2564 lc_module_name VARCHAR2(80) := 'csd.plsql.csd_wip_job_pvt.bulk_submit_jobs';
2565 lc_api_version NUMBER := 1.0;
2566 l_job_header_tbl job_header_tbl;
2567 l_job_header_index NUMBER := 1;
2568 l_sc_sql_executed BOOLEAN := FALSE;
2569 l_bill_ref_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2570 l_route_ref_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2571 l_alt_bills JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2572 l_alt_routes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2573 l_completion_subinvs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2574 l_completion_locs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2575 lx_job_name VARCHAR2(30);
2576 l_group_id NUMBER := 0;
2577 l_job_status NUMBER;
2578 l_mrp_net_qty_profile VARCHAR2(1) := nvl(fnd_profile.value('CSD_WIP_MRP_NET_QTY'),'N');
2579
2580 BEGIN
2581
2582 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2583 fnd_log.string(fnd_log.level_statement, lc_module_name,'Begin bulk_submit_jobs API');
2584 END IF;
2585
2586 -- standard check for API compatibility.
2587 IF NOT Fnd_Api.Compatible_API_Call
2588 (lc_api_version,
2589 p_api_version,
2590 lc_module_name,
2591 G_PKG_NAME)
2592 THEN
2593 RAISE fnd_api.g_exc_unexpected_error;
2594 END IF;
2595
2596 IF fnd_api.to_boolean(p_init_msg_list)
2597 THEN
2598 fnd_msg_pub.initialize;
2599 END IF;
2600
2601 px_job_names := jtf_varchar2_table_100();
2602
2603 -- initialize the sql executed variable.
2604 l_sc_sql_executed := FALSE;
2605 -- set the return status to success.
2606 x_return_status := fnd_api.g_ret_sts_success;
2607
2608 -- Validation for approval required flag will not be done here, as the java layer does it.
2609 -- anybody calling this API should take care of the same.
2610 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2611 fnd_log.string(fnd_log.level_statement, lc_module_name,'Job status passed is: '||p_job_status);
2612 END IF;
2613
2614 IF p_job_status IS NOT NULL THEN
2615 l_job_status := p_job_status;
2616 ELSE
2617 IF fnd_profile.value('CSD_DEFAULT_JOB_STATUS') = 'RELEASED'
2618 THEN
2619 l_job_status := 3;
2620
2621 ELSIF NVL(fnd_profile.value('CSD_DEFAULT_JOB_STATUS'), 'UNRELEASED' ) = 'UNRELEASED'
2622 THEN
2623
2624 l_job_status := 1;
2625 END IF;
2626
2627 END IF;
2628
2629 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2630 fnd_log.string(fnd_log.level_statement, lc_module_name,'Final job status: '||l_job_status);
2631 END IF;
2632
2633 FOR i IN 1 ..p_repair_line_ids.COUNT
2634 LOOP
2635
2636 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2637 fnd_log.string(fnd_log.level_statement, lc_module_name,'Starting loop to process the repair lines. Repair line id being processed is:'||p_repair_line_ids(i));
2638 END IF;
2639
2640 IF p_service_code_id IS NOT NULL AND p_service_code_id <> fnd_api.g_miss_num
2641 THEN
2642
2643 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2644 fnd_log.string(fnd_log.level_statement, lc_module_name,'Inside the service code block:');
2645 END IF;
2646
2647 IF NOT l_sc_sql_executed
2648 THEN
2649 SELECT bom.assembly_item_id bom_reference_id,
2650 bom.alternate_bom_designator,
2651 bor.assembly_item_id routing_reference_id,
2652 bor.alternate_routing_designator,
2653 bor.completion_subinventory,
2654 bor. completion_locator_id
2655 BULK COLLECT INTO
2656 l_bill_ref_ids,
2657 l_alt_bills,
2658 l_route_ref_ids,
2659 l_alt_routes,
2660 l_completion_subinvs,
2661 l_completion_locs
2662 FROM csd_sc_work_entities cscwe,
2663 bom_bill_of_materials bom ,
2664 bom_operational_routings bor
2665 WHERE cscwe.service_code_id = p_service_code_id
2666 AND cscwe.work_entity_type_code = 'BOM'
2667 AND cscwe.work_entity_id3 = p_inventory_org_id
2668 AND cscwe.work_entity_id1 = bom.bill_sequence_id (+)
2669 AND cscwe.work_entity_id2 = bor.routing_sequence_id (+);
2670
2671 l_sc_sql_executed := TRUE;
2672
2673 END IF;
2674 END IF;
2675
2676 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2677 fnd_log.string(fnd_log.level_statement, lc_module_name,'Found '||l_bill_ref_ids.COUNT||' number of bills/routings associated with SC id:'||p_service_code_id);
2678 END IF;
2679
2680 IF l_bill_ref_ids.COUNT > 0 OR l_route_ref_ids.COUNT > 0
2681 THEN
2682 FOR j IN 1 ..l_bill_ref_ids.COUNT
2683 LOOP
2684 l_job_header_tbl(l_job_header_index).bom_reference_id := l_bill_ref_ids(j);
2685 l_job_header_tbl(l_job_header_index).routing_reference_id := l_route_ref_ids(j);
2686 l_job_header_tbl(l_job_header_index).alternate_bom_designator := l_alt_bills(j);
2687 l_job_header_tbl(l_job_header_index).alternate_routing_designator := l_alt_routes(j);
2688 l_job_header_tbl(l_job_header_index).completion_subinventory := l_completion_subinvs(j);
2689 l_job_header_tbl(l_job_header_index).completion_locator_id := l_completion_locs(j);
2690 l_job_header_tbl(l_job_header_index).source_code := 'DEPOT_REPAIR';
2691 l_job_header_tbl(l_job_header_index).source_line_id := p_repair_line_ids(i);
2692 l_job_header_tbl(l_job_header_index).organization_id := p_inventory_org_id;
2693 l_job_header_tbl(l_job_header_index).status_type := l_job_status;
2694 l_job_header_tbl(l_job_header_index).load_type := 4;
2695 l_job_header_tbl(l_job_header_index).process_phase := 2;
2696 l_job_header_tbl(l_job_header_index).process_status := 1;
2697 l_job_header_tbl(l_job_header_index).creation_date := SYSDATE;
2698 l_job_header_tbl(l_job_header_index).last_update_date := SYSDATE;
2699 l_job_header_tbl(l_job_header_index).created_by := fnd_global.user_id;
2700 l_job_header_tbl(l_job_header_index).last_updated_by := fnd_global.user_id;
2701 l_job_header_tbl(l_job_header_index).last_update_login := fnd_global.login_id;
2702 l_job_header_tbl(l_job_header_index).primary_item_id := NVL(p_upgrade_to_item_id,p_inventory_item_ids(i));
2703 l_job_header_tbl(l_job_header_index).start_quantity := p_quantities(i);
2704 l_job_header_tbl(l_job_header_index).first_unit_start_date := p_start_date;
2705 l_job_header_tbl(l_job_header_index).last_unit_completion_date := p_end_date;
2706 l_job_header_tbl(l_job_header_index).priority := p_scheduling_priority;
2707
2708 IF l_mrp_net_qty_profile = 'N'
2709 THEN
2710 l_job_header_tbl(l_job_header_index).net_quantity := p_quantities(i);
2711 ELSE
2712 l_job_header_tbl(l_job_header_index).net_quantity := 0;
2713 END IF;
2714 -- get the job name.
2715 CSD_WIP_JOB_PVT.generate_job_name(g_job_prefix,p_inventory_org_id,lx_job_name);
2716
2717 l_job_header_tbl(l_job_header_index).job_name := lx_job_name;
2718 l_job_header_tbl(l_job_header_index).class_code := p_accouting_class;
2719
2720 IF j = 1
2721 THEN
2722 px_job_names.extend;
2723 px_job_names(i) := lx_job_name;
2724 ELSE
2725 px_job_names.extend;
2726 px_job_names(i) := px_job_names(i)||','||lx_job_name;
2727 END IF;
2728
2729 IF l_group_id = 0
2730 THEN
2731 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
2732 END IF;
2733
2734 l_job_header_tbl(l_job_header_index).group_id := l_group_id;
2735 l_job_header_index := l_job_header_index + 1;
2736
2737 END LOOP;
2738
2739 ELSE
2740
2741 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2742 fnd_log.string(fnd_log.level_statement, lc_module_name,'Service code is not being passed in. Populating the job header table.');
2743 END IF;
2744
2745 l_job_header_tbl(l_job_header_index).bom_reference_id := p_bill_reference_id;
2746 l_job_header_tbl(l_job_header_index).routing_reference_id := p_route_reference_id;
2747 l_job_header_tbl(l_job_header_index).alternate_bom_designator := p_alternate_bom;
2748 l_job_header_tbl(l_job_header_index).alternate_routing_designator := p_alternate_route;
2749 l_job_header_tbl(l_job_header_index).completion_subinventory := NULL;
2750 l_job_header_tbl(l_job_header_index).completion_locator_id := NULL;
2751 l_job_header_tbl(l_job_header_index).source_code := 'DEPOT_REPAIR';
2752 l_job_header_tbl(l_job_header_index).source_line_id := p_repair_line_ids(i);
2753 l_job_header_tbl(l_job_header_index).organization_id := p_inventory_org_id;
2754 l_job_header_tbl(l_job_header_index).status_type := l_job_status;
2755 l_job_header_tbl(l_job_header_index).load_type := 4;
2756 l_job_header_tbl(l_job_header_index).process_phase := 2;
2757 l_job_header_tbl(l_job_header_index).process_status := 1;
2758 l_job_header_tbl(l_job_header_index).creation_date := SYSDATE;
2759 l_job_header_tbl(l_job_header_index).last_update_date := SYSDATE;
2760 l_job_header_tbl(l_job_header_index).created_by := fnd_global.user_id;
2761 l_job_header_tbl(l_job_header_index).last_updated_by := fnd_global.user_id;
2762 l_job_header_tbl(l_job_header_index).last_update_login := fnd_global.login_id;
2763 l_job_header_tbl(l_job_header_index).primary_item_id := NVL(p_upgrade_to_item_id,p_inventory_item_ids(i));
2764 l_job_header_tbl(l_job_header_index).start_quantity := p_quantities(i);
2765 l_job_header_tbl(l_job_header_index).first_unit_start_date := p_start_date;
2766 l_job_header_tbl(l_job_header_index).last_unit_completion_date := p_end_date;
2767 l_job_header_tbl(l_job_header_index).priority := p_scheduling_priority;
2768
2769 IF l_mrp_net_qty_profile = 'N'
2770 THEN
2771 l_job_header_tbl(l_job_header_index).net_quantity := p_quantities(i);
2772 ELSE
2773 l_job_header_tbl(l_job_header_index).net_quantity := 0;
2774 END IF;
2775 -- get the job name.
2776 CSD_WIP_JOB_PVT.generate_job_name(g_job_prefix,p_inventory_org_id,lx_job_name);
2777
2778 l_job_header_tbl(l_job_header_index).job_name := lx_job_name;
2779 l_job_header_tbl(l_job_header_index).class_code := p_accouting_class;
2780
2781 px_job_names.extend;
2782 px_job_names(i) := lx_job_name;
2783
2784 IF l_group_id = 0
2785 THEN
2786 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
2787 END IF;
2788
2789 l_job_header_tbl(l_job_header_index).group_id := l_group_id;
2790 l_job_header_index := l_job_header_index + 1;
2791
2792 END IF;
2793
2794 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2795 fnd_log.string(fnd_log.level_statement, lc_module_name,'End of iteration. Loop index='||i||' and generated job names are '||px_job_names(i));
2796 END IF;
2797
2798 END LOOP;
2799
2800 -- insert the job header rec into wip_job_schedule_interface_table and call the CP.
2801 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2802 fnd_log.string(fnd_log.level_statement, lc_module_name,'Bulk insert into wip_job_schedule_interface. Total number of records to be inserted is '||l_job_header_tbl.COUNT);
2803 fnd_log.string(fnd_log.level_statement, lc_module_name,'The group id for the batch is = '||l_group_id);
2804 END IF;
2805
2806 FORALL j IN 1 ..l_job_header_tbl.COUNT
2807 INSERT INTO wip_job_schedule_interface VALUES l_job_header_tbl(j);
2808
2809 -- insert the records inot the csd xref table.
2810 -- instead of table handler, I am doing direct table update, since the table handlers
2811 -- process one row at a time. For an online batch processing, this is not ideal.
2812
2813 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2814 fnd_log.string(fnd_log.level_statement, lc_module_name,'Bulk insert into csd_repair_job_xref. Total number of records to be inserted is '||l_job_header_tbl.COUNT);
2815
2816 END IF;
2817
2818 FORALL k IN 1 ..l_job_header_tbl.COUNT
2819 INSERT INTO csd_repair_job_xref(
2820 REPAIR_JOB_XREF_ID,
2821 CREATED_BY,
2822 CREATION_DATE,
2823 LAST_UPDATED_BY,
2824 LAST_UPDATE_DATE,
2825 LAST_UPDATE_LOGIN,
2826 REPAIR_LINE_ID,
2827 WIP_ENTITY_ID,
2828 GROUP_ID,
2829 ORGANIZATION_ID,
2830 QUANTITY,
2831 INVENTORY_ITEM_ID,
2832 ITEM_REVISION,
2833 SOURCE_TYPE_CODE,
2834 SOURCE_ID1 ,
2835 RO_SERVICE_CODE_ID ,
2836 JOB_NAME,
2837 OBJECT_VERSION_NUMBER
2838 )
2839 VALUES
2840 (CSD_REPAIR_JOB_XREF_S1.nextval,
2841 fnd_global.user_id,
2842 sysdate,
2843 fnd_global.user_id,
2844 sysdate,
2845 fnd_global.login_id,
2846 l_job_header_tbl(k).source_line_id,
2847 NULL,
2848 l_group_id,
2849 l_job_header_tbl(k).organization_id,
2850 l_job_header_tbl(k).start_quantity,
2851 l_job_header_tbl(k).primary_item_id,
2852 NULL,
2853 'MANUAL',
2854 p_service_code_id,
2855 p_service_code_id,
2856 l_job_header_tbl(k).job_name,
2857 1
2858 );
2859
2860 -- submit concurrent request to process the mass load.
2861 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2862 fnd_log.string(fnd_log.level_statement, lc_module_name,'Launching the CSDJSWIP CP.');
2863
2864 END IF;
2865
2866 x_request_id := fnd_request.submit_request (
2867 application => 'CSD',
2868 program => 'CSDJSWIP',
2869 description => NULL,
2870 start_time => NULL,
2871 sub_request => FALSE,
2872 argument1 => TO_CHAR(l_group_id),
2873 argument2 => NULL);
2874
2875 -- purposfully no exception block here. Any exception encountered will and should be raised.
2876 -- most likely a bug, or indication of something went nastily wrong.
2877
2878 -- the caller of this API should decide what to do with x_request_id = -1 or a when others exception.
2879 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2880 fnd_log.string(fnd_log.level_statement, lc_module_name,'Return from fnd_request.submit_request. The request_id = '||x_request_id);
2881
2882 END IF;
2883
2884 EXCEPTION
2885 WHEN fnd_api.g_exc_unexpected_error THEN
2886 x_return_status := fnd_api.g_ret_sts_error;
2887
2888 END bulk_submit_jobs;
2889
2890 --bug#12316893
2891 /* Delete_Completed_Wip_Records (interface_id)
2892 This procedure deletes records from the WIP_JOB_SCHEDULE_INTERFACE table
2893 that were successfully loaded by the Mass Load Process
2894 -WIP_MASSLOAD_PUB.createOneJob.
2895 This procedure is similar to WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records
2896 Please check WIP_MASS_LOAD_PROCESSOR.Delete_Completed_Records for
2897 any issue
2898 */
2899
2900 PROCEDURE Delete_Completed_Wip_Records(p_interface_id IN NUMBER) IS
2901
2902 cursor old_requests is
2903 select rowid, interface_id, group_id, header_id
2904 from wip_job_schedule_interface
2905 where
2906 interface_id = p_interface_id and process_status= WIP_CONSTANTS.ML_COMPLETE
2907 for update;
2908
2909 BEGIN
2910
2911 --if profile MRP:Debug Mode set to N, then delete it.
2912
2913 IF (nvl(fnd_profile.value('MRP_DEBUG'),'Y') = 'N') THEN
2914
2915 for old_request in old_requests loop
2916
2917 delete from wip_interface_errors
2918 where interface_id = old_request.interface_id;
2919
2920 delete from wip_job_schedule_interface
2921 where rowid = old_request.rowid;
2922
2923 -- delete from wip_job_dtls_interface
2924 -- where group_id = old_request.group_id
2925 -- and parent_header_id = old_request.header_id;
2926
2927 end loop ;
2928
2929 End If;
2930
2931 END Delete_Completed_Wip_Records;
2932
2933 --bug#12316893
2934
2935 END CSD_WIP_JOB_PVT;
[Home] [Help]