[Home] [Help]
PACKAGE BODY: APPS.CSD_RECALLS_PVT
Source
1 PACKAGE BODY CSD_RECALLS_PVT AS
2 /* $Header: csdvrclb.pls 120.2 2010/10/01 07:08:00 subhat noship $ */
3 -- Start of Comments
4 -- Package name : CSD_RECALLS_PVT
5 -- Purpose : This package will contain all the procedure and functions used by the Recalls.
6 -- Usage of this package is strictly confined to Oracle Depot Repair Development.
7 --
8 -- History : 24/03/2010, Created by Sudheer Bhat
9 -- NOTE :
10 -- End of Comments
11
12 -- logging globals.
13 G_LEVEL_PROCEDURE NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 G_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
15
16 G_RET_STS_SUCCESS VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
17 -- Cache to hold the default bill to and ship to use id's for an customer.
18 -- this prevents re-executing SQL over and over again.
19 g_csd_shipto_billto_cache csd_shipto_billto_tbl;
20
21 g_sr_params_cached BOOLEAN := FALSE;
22 g_sr_urgency NUMBER;
23 g_sr_severity NUMBER;
24 g_sr_owner NUMBER;
25 g_sr_summary VARCHAR2(240);
26 g_sr_status NUMBER;
27 g_sr_owner_type VARCHAR2(80) := FND_PROFILE.value('CS_SR_DEFAULT_OWNER_TYPE');
28
29 g_ro_attribs_cached BOOLEAN := FALSE;
30 g_auto_process_rma VARCHAR2(1);
31 g_business_process_id NUMBER;
32 g_repair_mode VARCHAR2(10);
33
34 g_bill_details_cached BOOLEAN := FALSE;
35 g_bill_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
36 g_alt_bill JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
37 g_routing_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
38 g_alt_routing JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
39 g_completion_subinv JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
40 g_completion_locid JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
41 g_sc_id NUMBER;
42 g_upgrade_item_id NUMBER;
43 g_job_prefix VARCHAR2(30) := fnd_profile.value('CSD_DEFAULT_JOB_PREFIX');
44
45 -- Private procedures.
46 /****************************************************************************************/
47 /* Procedure Name: process_post_wip_massload. */
48 /* Description: Creates the records in the repair history and csd repair job xref */
49 /* tables for the processing group id specified. The program will look for the */
50 /* wip jobs created for the repair line id as specified in the recall lines */
51 /* table. */
52 /*-- History: 26/03/2010, Created by Sudheer Bhat. */
53 /****************************************************************************************/
54
55 PROCEDURE process_post_wip_massload (p_rec_group_id IN NUMBER,p_req_group_id IN NUMBER);
56
57 PROCEDURE process_post_wip_massload (p_rec_group_id IN NUMBER,p_req_group_id IN NUMBER)
58 IS
59
60 l_post_wipml_tbl post_wipml_tbl;
61 x_return_status VARCHAR2(1);
62 x_msg_count NUMBER;
63 x_msg_data VARCHAR2(2000);
64 x_job_xref_id NUMBER;
65 l_user_id NUMBER := fnd_global.user_id;
66 l_rep_hist_id NUMBER;
67 l_operation_seq_num NUMBER;
68 l_department_id NUMBER;
69 l_supply_subinventory VARCHAR2(30);
70 l_op_dtls_tbl CSD_HV_WIP_JOB_PVT.OP_DTLS_TBL_TYPE;
71 l_mtl_txn_dtls_tbl CSD_HV_WIP_JOB_PVT.MTL_TXN_DTLS_TBL_TYPE;
72 x_op_created VARCHAR2(1);
73 lc_api_name CONSTANT VARCHAR2(60) := 'CSD.PLSQL.CSD_RECALLS_PVT.PROCESS_POST_WIP_MASSLOAD';
74 l_default_ro_item VARCHAR2(1);
75
76 BEGIN
77
78 SAVEPOINT process_post_wip_massload;
79
80 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
81 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin PROCESS_POST_WIP_MASSLOAD API');
82 END IF;
83
84 SELECT crl.repair_line_id,
85 wdj.wip_entity_id,
86 wdj.organization_id,
87 wdj.start_quantity,
88 wdj.primary_item_id,
89 crl.inventory_item_id,
90 crl.serial_number,
91 msi.primary_uom_code,
92 we.wip_entity_name,
93 csc.name,
94 csc.service_code_id,
95 cii.quantity
96 BULK COLLECT INTO l_post_wipml_tbl
97 FROM csd_recall_lines crl,
98 wip_entities we,
99 wip_discrete_jobs wdj,
100 csd_service_codes_tl csc,
101 mtl_system_items_b msi,
102 csi_item_instances cii
103 WHERE crl.processing_group_id = p_rec_group_id
104 AND crl.repair_line_id = wdj.source_line_id
105 AND NVL(g_upgrade_item_id,crl.inventory_item_id) = wdj.primary_item_id
106 AND wdj.wip_entity_id = we.wip_entity_id
107 AND csc.service_code_id = g_sc_id
108 AND csc.language = userenv('lang')
109 AND msi.organization_id = wdj.organization_id
110 AND msi.inventory_item_id = crl.inventory_item_id
111 AND crl.instance_id = cii.instance_id;
112
113 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
114 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'After bulk collect, count = '||l_post_wipml_tbl.COUNT);
115 END IF;
116
117 IF l_post_wipml_tbl.COUNT = 0
118 THEN
119 RETURN;
120 END IF;
121
122 -- call csd_to_form_repair_job_xref.validate_and_write in loop.
123 FOR i IN 1 ..l_post_wipml_tbl.COUNT
124 LOOP
125 csd_to_form_repair_job_xref.validate_and_write(
126 p_api_version_number => 1.0,
127 p_init_msg_list => fnd_api.g_false,
128 p_commit => fnd_api.g_false,
129 p_validation_level => NULL,
130 p_action_code => 0,
131 px_repair_job_xref_id => x_job_xref_id,
132 p_created_by => l_user_id,
133 p_creation_date => SYSDATE,
134 p_last_updated_by => l_user_id,
135 p_last_update_date => SYSDATE,
136 p_last_update_login => l_user_id,
137 p_repair_line_id => l_post_wipml_tbl(i).repair_line_id,
138 p_wip_entity_id => l_post_wipml_tbl(i).wip_entity_id,
139 p_group_id => p_req_group_id,
140 p_organization_id => l_post_wipml_tbl(i).organization_id,
141 p_quantity => l_post_wipml_tbl(i).quantity,
142 p_inventory_item_id => l_post_wipml_tbl(i).inventory_item_id,
143 p_item_revision => NULL,
144 p_object_version_number => NULL,
145 p_attribute_category => NULL,
146 p_attribute1 => NULL,
147 p_attribute2 => NULL,
148 p_attribute3 => NULL,
149 p_attribute4 => NULL,
150 p_attribute5 => NULL,
151 p_attribute6 => NULL,
152 p_attribute7 => NULL,
153 p_attribute8 => NULL,
154 p_attribute9 => NULL,
155 p_attribute10 => NULL,
156 p_attribute11 => NULL,
157 p_attribute12 => NULL,
158 p_attribute13 => NULL,
159 p_attribute14 => NULL,
160 p_attribute15 => NULL,
161 p_quantity_completed => NULL,
162 p_job_name => l_post_wipml_tbl(i).job_name,
163 p_source_type_code => l_post_wipml_tbl(i).service_code,
164 p_source_id1 => l_post_wipml_tbl(i).service_code_id,
165 p_ro_service_code_id => l_post_wipml_tbl(i).service_code_id,
166 x_return_status => x_return_status,
167 x_msg_count => x_msg_count,
168 x_msg_data => x_msg_data);
169 IF x_return_status <> g_ret_sts_success
170 THEN
171 RAISE fnd_api.g_exc_error;
172 END IF;
173
174 csd_to_form_repair_history.validate_and_write(
175 p_api_version_number => 1.0,
176 p_init_msg_list => fnd_api.g_false,
177 p_commit => fnd_api.g_false,
178 p_validation_level => NULL,
179 p_action_code => 0,
180 px_repair_history_id => l_rep_hist_id,
181 p_OBJECT_VERSION_NUMBER => NULL,
182 p_request_id => NULL,
183 p_program_id => NULL,
184 p_program_application_id => NULL,
185 p_program_update_date => NULL,
186 p_created_by => l_user_id,
187 p_creation_date => SYSDATE,
188 p_last_updated_by => l_user_id,
189 p_last_update_date => SYSDATE,
190 p_repair_line_id => l_post_wipml_tbl(i).repair_line_id,
191 p_event_code => 'JS',
192 p_event_date => SYSDATE,
193 p_quantity => l_post_wipml_tbl(i).quantity,
194 p_paramn1 => l_post_wipml_tbl(i).wip_entity_id,
195 p_paramn2 => l_post_wipml_tbl(i).organization_id,
196 p_paramn3 => NULL,
197 p_paramn4 => NULL,
198 p_paramn5 => l_post_wipml_tbl(i).quantity,
199 p_paramn6 => NULL,
200 p_paramn8 => NULL,
201 p_paramn9 => NULL,
202 p_paramn10 => NULL,
203 p_paramc1 => l_post_wipml_tbl(i).job_name,
204 p_paramc2 => NULL,
205 p_paramc3 => NULL,
206 p_paramc4 => NULL,
207 p_paramc5 => NULL,
208 p_paramc6 => NULL,
209 p_paramc7 => NULL,
210 p_paramc8 => NULL,
211 p_paramc9 => NULL,
212 p_paramc10 => NULL,
213 p_paramd1 => NULL ,
214 p_paramd2 => NULL ,
215 p_paramd3 => NULL ,
216 p_paramd4 => NULL ,
217 p_paramd5 => SYSDATE,
218 p_paramd6 => NULL ,
219 p_paramd7 => NULL ,
220 p_paramd8 => NULL ,
221 p_paramd9 => NULL ,
222 p_paramd10 => NULL ,
223 p_attribute_category => NULL ,
224 p_attribute1 => NULL ,
225 p_attribute2 => NULL ,
226 p_attribute3 => NULL ,
227 p_attribute4 => NULL ,
228 p_attribute5 => NULL ,
229 p_attribute6 => NULL ,
230 p_attribute7 => NULL ,
231 p_attribute8 => NULL ,
232 p_attribute9 => NULL ,
233 p_attribute10 => NULL ,
234 p_attribute11 => NULL ,
235 p_attribute12 => NULL ,
236 p_attribute13 => NULL ,
237 p_attribute14 => NULL ,
238 p_attribute15 => NULL ,
239 p_last_update_login => l_user_id,
240 x_return_status => x_return_status,
241 x_msg_count => x_msg_count,
242 x_msg_data => x_msg_data);
243
244 IF x_return_status <> g_ret_sts_success
245 THEN
246 RAISE fnd_api.g_exc_error;
247 END IF;
248
249 l_default_ro_item := nvl(FND_PROFILE.VALUE('CSD_DEFAULT_RO_ITEM_AS_MATERIAL_ON_JOB'), 'N');
250
251 IF g_upgrade_item_id IS NOT NULL OR l_default_ro_item = 'Y'
252 THEN
253 -- check if the operation exists.
254 BEGIN
255 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
256 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin upgrade item processing');
257 END IF;
258
259 BEGIN
260 SELECT MIN(operation_seq_num)
261 INTO l_operation_seq_num
262 FROM wip_operations
263 WHERE wip_entity_id = l_post_wipml_tbl(i).wip_entity_id;
264 EXCEPTION
265 WHEN no_data_found THEN
266 l_operation_seq_num := 0;
267 END;
268
269 IF l_operation_seq_num = 0
270 THEN
271 -- create new operation.
272 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
273 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'No operations found, proceeding to create one.');
274 END IF;
275 IF l_department_id IS NULL
276 THEN
277 SELECT department_id, supply_subinventory
278 INTO l_department_id, l_supply_subinventory
279 FROM csd_recall_parameters
280 WHERE organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
281 END IF;
282
283 l_op_dtls_tbl(1).new_row := 'Y';
284 l_op_dtls_tbl(1).wip_entity_id := l_post_wipml_tbl(i).wip_entity_id;
285 l_op_dtls_tbl(1).organization_id:= l_post_wipml_tbl(i).organization_id;
286 l_op_dtls_tbl(1).operation_seq_num := 10;
287 l_op_dtls_tbl(1).backflush_flag := 2;
288 l_op_dtls_tbl(1).count_point_type := 1;
289 l_op_dtls_tbl(1).first_unit_completion_date := sysdate;
290 l_op_dtls_tbl(1).first_unit_start_date := sysdate;
291 l_op_dtls_tbl(1).last_unit_completion_date := sysdate;
292 l_op_dtls_tbl(1).last_unit_start_date := sysdate;
293 l_op_dtls_tbl(1).minimum_transfer_quantity := 0;
294
295 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
296 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_OP_DTLS');
297 END IF;
298
299 CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_OP_DTLS
300 (
301 p_api_version_number => 1.0,
302 p_init_msg_list => fnd_api.g_false,
303 p_Commit => fnd_api.g_false,
304 p_validation_level => 100,
305 x_return_status => x_return_status,
306 x_msg_count => x_msg_count,
307 x_msg_data => x_msg_data,
308 p_op_dtls_tbl => l_op_dtls_tbl
309 );
310
311 l_mtl_txn_dtls_tbl(i).new_row := 'Y';
312 l_mtl_txn_dtls_tbl(i).operation_seq_num := l_op_dtls_tbl(1).operation_seq_num;
313 l_mtl_txn_dtls_tbl(i).wip_entity_id := l_post_wipml_tbl(i).wip_entity_id;
314 l_mtl_txn_dtls_tbl(i).organization_id := l_post_wipml_tbl(i).organization_id;
315 l_mtl_txn_dtls_tbl(i).inventory_item_id := l_post_wipml_tbl(i).recall_inventory_id;
316 l_mtl_txn_dtls_tbl(i).transaction_quantity := l_post_wipml_tbl(i).transaction_qty;
317 l_mtl_txn_dtls_tbl(i).supply_subinventory := l_supply_subinventory;
318
319 ELSE
320 IF l_supply_subinventory IS NULL
321 THEN
322 SELECT department_id, supply_subinventory
323 INTO l_department_id, l_supply_subinventory
324 FROM csd_recall_parameters
325 WHERE organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
326 END IF;
327
328 l_mtl_txn_dtls_tbl(i).new_row := 'Y';
329 l_mtl_txn_dtls_tbl(i).operation_seq_num := l_operation_seq_num;
330 l_mtl_txn_dtls_tbl(i).wip_entity_id := l_post_wipml_tbl(i).wip_entity_id;
331 l_mtl_txn_dtls_tbl(i).organization_id := l_post_wipml_tbl(i).organization_id;
332 l_mtl_txn_dtls_tbl(i).inventory_item_id := l_post_wipml_tbl(i).recall_inventory_id;
333 l_mtl_txn_dtls_tbl(i).transaction_quantity := l_post_wipml_tbl(i).transaction_qty;
334 l_mtl_txn_dtls_tbl(i).supply_subinventory := l_supply_subinventory;
335 END IF;
336 EXCEPTION
337 WHEN no_data_found THEN
338 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
339 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'No data found error. Missing default dept or default supply subinventory');
340 END IF;
341 END;
342 END IF;
343 END LOOP;
344
345 -- call the hvr save mtl_transactions API.
346 CSD_HV_WIP_JOB_PVT.PROCESS_SAVE_MTL_TXN_DTLS
347 (
348 p_api_version_number => 1.0,
349 p_init_msg_list => fnd_api.g_false,
350 p_Commit => fnd_api.g_false,
351 p_validation_level => 100,
352 x_return_status => x_return_status,
353 x_msg_count => x_msg_count,
354 x_msg_data => x_msg_data,
355 p_mtl_txn_dtls_tbl => l_mtl_txn_dtls_tbl,
356 x_op_created => x_op_created
357 );
358
359 EXCEPTION
360 WHEN fnd_api.g_exc_error THEN
361 ROLLBACK TO process_post_wip_massload;
362
363 WHEN OTHERS THEN
364 ROLLBACK TO process_post_wip_massload;
365 RAISE;
366 END process_post_wip_massload;
367
368 /****************************************************************************************/
369 /* Procedure Name: write_to_conc_out */
370 /* Description: Writes the output to concurrent programs standard out. */
371 /*-- History: 29/09/2010, Created by Sudheer Bhat. */
372 /****************************************************************************************/
373
374 PROCEDURE write_to_conc_out(p_group_id IN NUMBER);
375
376 PROCEDURE write_to_conc_out(p_group_id IN NUMBER)
377 IS
378 l_instance_label VARCHAR2(30);
379 l_serial_label VARCHAR2(30);
380 l_item_label VARCHAR2(30);
381 l_qty_label VARCHAR2(30);
382 l_sr_label VARCHAR2(40);
383 l_ro_label VARCHAR2(40);
384 l_recall VARCHAR2(20) := 'Recall';
385 l_recall_number VARCHAR2(30);
386
387 CURSOR recall_details IS
388 SELECT cii.instance_number,
389 crl.serial_number,
390 cr.repair_number,
391 cs.incident_number,
392 cr.quantity,
393 msi.concatenated_segments
394 FROM csd_recall_lines crl,
395 csi_item_instances cii,
396 csd_repairs cr,
397 cs_incidents_all_b cs,
398 mtl_system_items_kfv msi
399 WHERE crl.processing_group_id = p_group_id
400 AND crl.instance_id = cii.instance_id
401 AND crl.repair_line_id = cr.repair_line_id (+)
402 AND crl.incident_id = cs.incident_id (+)
403 AND crl.inventory_item_id = msi.inventory_item_id
404 AND msi.organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
405
406 BEGIN
407
408 -- print the header.
409 SELECT recall_number
410 INTO l_recall_number
411 FROM csd_recall_lines crl,
412 csd_recall_headers_b crh
413 WHERE crl.processing_group_id = p_group_id
414 AND crl.recall_id = crh.recall_id
415 AND rownum < 2;
416
417 fnd_file.put_line(fnd_file.output,l_recall||' : '||l_recall_number);
418 fnd_file.put_line(fnd_file.output,' ');
419
420 fnd_message.set_name('CSD','CSD_BULK_RCV_SERIAL_CONC_LABEL');
421 l_serial_label := fnd_message.get;
422 fnd_message.set_name('CSD','CSD_BULK_RCV_ITEM_CONC_LABEL');
423 l_item_label := fnd_message.get;
424 fnd_message.set_name('CSD','CSD_BULK_RCV_QTY_CONC_LABEL');
425 l_qty_label := fnd_message.get;
426 fnd_message.set_name('CSD','CSD_BULK_RCV_SR_CONC_LABEL');
427 l_sr_label := fnd_message.get;
428 fnd_message.set_name('CSD','CSD_BULK_RCV_RO_CONC_LABEL');
429 l_ro_label := fnd_message.get;
430 fnd_message.set_name('GMO', 'GMO_INSTANCE_NUMBER_FIELD_NAME');
431 l_instance_label := fnd_message.get;
432
433 -- put the output header.
434 Fnd_file.put(fnd_file.output,rpad(l_instance_label,18,' '));
435 fnd_file.put(fnd_file.output,rpad(l_serial_label,18,' '));
436 fnd_file.put(fnd_file.output,rpad(l_item_label,20,' '));
437 fnd_file.put(fnd_file.output,rpad(l_sr_label,25,' '));
438 fnd_file.put(fnd_file.output,rpad(l_ro_label,20,' '));
439 fnd_file.put_line(fnd_file.output,rpad(l_qty_label,10,' '));
440 fnd_file.put_line(fnd_file.output,rpad('-',114,'-'));
441
442 -- write the data.
443 FOR i IN recall_details
444 LOOP
445 fnd_file.put(fnd_file.output,rpad(nvl(i.instance_number,' '),18,' '));
446 fnd_file.put(fnd_file.output,rpad(nvl(i.serial_number,' '),18,' '));
447 fnd_file.put(fnd_file.output,rpad(nvl(i.concatenated_segments,' '),20,' '));
448 fnd_file.put(fnd_file.output,rpad(nvl(i.incident_number,' '),25,' '));
449 fnd_file.put(fnd_file.output,rpad(nvl(i.repair_number,' '),20,' '));
450 fnd_file.put_line(fnd_file.output,rpad(nvl(to_char(i.quantity),' '),10,' '));
451 END LOOP;
452
453 END write_to_conc_out;
454
455 /****************************************************************************************/
456 /* Procedure Name: Generate_Recall_Work. */
457 /* Description: Receives a set of recall lines for which the recall work needs to */
458 /* be generated along with SR, RO and WIP params if any. Prepares these */
459 /* recall lines for concurrent processing and launches the CP to create */
460 /* recall work.Returns the concurrent program Id to the caller if success */
461 /* else an appropriate error message is returned. */
462 /*-- History: 24/03/2010, Created by Sudheer Bhat. */
463 /****************************************************************************************/
464
465 PROCEDURE GENERATE_RECALL_WORK (p_api_version IN NUMBER,
466 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
467 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
468 p_recall_line_ids IN JTF_NUMBER_TABLE,
469 p_sr_type_id IN NUMBER,
470 p_ro_type_id IN NUMBER DEFAULT NULL,
471 p_service_code_id IN NUMBER DEFAULT NULL,
472 p_wip_accounting_class IN VARCHAR2 DEFAULT NULL,
473 p_upgrade_item_id IN VARCHAR2 DEFAULT NULL,
474 p_wip_inv_org_id IN NUMBER DEFAULT NULL,
475 p_recall_number IN VARCHAR2,
476 x_request_id OUT NOCOPY NUMBER,
477 x_msg_count OUT NOCOPY NUMBER,
478 x_msg_data OUT NOCOPY VARCHAR2,
479 x_return_status OUT NOCOPY VARCHAR2)
480 IS
481
482 -- local constants.
483 lc_api_version CONSTANT NUMBER := 1.0;
484 lc_api_name CONSTANT VARCHAR2(60) := 'CSD.PLSQL.CSD_RECALLS_PVT.GENERATE_RECALL_WORK';
485 l_counter NUMBER := 0;
486 l_group_id NUMBER;
487
488 BEGIN
489
490 SAVEPOINT GENERATE_RECALL_WORK;
491
492 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
493 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin GENERATE_RECALL_WORK API');
494 END IF;
495
496 -- standard check for API compatibility.
497 IF NOT Fnd_Api.Compatible_API_Call
498 (lc_api_version,
499 p_api_version,
500 lc_api_name,
501 G_PKG_NAME)
502 THEN
503 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
504 END IF;
505
506 IF Fnd_Api.to_Boolean(p_init_msg_list)
507 THEN
508 Fnd_Msg_Pub.initialize;
509 END IF;
510
511 -- log api params.
512 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
513 fnd_log.string(G_LEVEL_PROCEDURE,lc_api_name,'p_sr_type_id = '||p_sr_type_id);
514 fnd_log.string(G_LEVEL_PROCEDURE,lc_api_name,'p_ro_type_id = '||p_ro_type_id);
515 fnd_log.string(G_LEVEL_PROCEDURE,lc_api_name,'p_service_code_id = '||p_service_code_id);
516 fnd_log.string(G_LEVEL_PROCEDURE,lc_api_name,'p_wip_accounting_class = '||p_wip_accounting_class);
517 fnd_log.string(G_LEVEL_PROCEDURE,lc_api_name,'p_upgrade_item_id = '||p_upgrade_item_id);
518 fnd_log.string(G_LEVEL_PROCEDURE,lc_api_name,'p_wip_inv_org_id = '||p_wip_inv_org_id);
519 fnd_log.string(G_LEVEL_PROCEDURE,lc_api_name,'p_recall_number = '||p_recall_number);
520 FOR l_counter IN 1 ..p_recall_line_ids.COUNT
521 LOOP
522 fnd_log.string(G_LEVEL_PROCEDURE,lc_api_name,'recall_line_id'||l_counter||' = '||p_recall_line_ids(l_counter));
523 END LOOP;
524 END IF;
525
526 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
527 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Update the recall lines as processing');
528 END IF;
529
530 SELECT csd_recall_lines_group_s1.NEXTVAL INTO l_group_id FROM dual;
531
532 -- update the selected recall line id's as processing flag = 'Y';
533 UPDATE CSD_RECALL_LINES crl SET processing_flag = 'Y', processing_group_id = l_group_id
534 WHERE crl.recall_line_id IN (SELECT * FROM TABLE(CAST(p_recall_line_ids as JTF_NUMBER_TABLE)))
535 AND nvl(crl.processing_flag,'N') = 'N';
536
537 COMMIT;
538
539 -- submit the concurrent request.
540 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
541 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Before launching the CP');
542 END IF;
543
544 x_request_id := fnd_request.submit_request(application => 'CSD',
545 program => 'CSDRCLWK',
546 description => null,
547 argument1 => l_group_id,
548 argument2 => p_sr_type_id,
549 argument3 => p_ro_type_id,
550 argument4 => p_service_code_id,
551 argument5 => p_wip_accounting_class,
552 argument6 => p_upgrade_item_id,
553 argument7 => p_wip_inv_org_id );
554 IF (x_request_id IS NULL OR x_request_id <= 0)
555 THEN
556 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL)
557 THEN
558 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in launching CP');
559 END IF;
560
561 UPDATE CSD_RECALL_LINES crl SET processing_flag = 'N'
562 WHERE crl.recall_line_id IN (SELECT * FROM TABLE(CAST(p_recall_line_ids as JTF_NUMBER_TABLE)))
563 AND nvl(crl.processing_flag,'N') = 'Y'
564 AND crl.processing_group_id = l_group_id;
565
566 COMMIT;
567 -- to do.
568 -- add a message.
569 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
570 END IF;
571
572 COMMIT;
573 EXCEPTION
574 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
575 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
576 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Unexpected error, could not submit the request');
577 END IF;
578 -- to do. get the fnd messages.
579 WHEN OTHERS THEN
580 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
581 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'WHEN OTHERS '||SQLERRM);
582 END IF;
583 RAISE;
584 END GENERATE_RECALL_WORK;
585
586 /****************************************************************************************/
587 /* Procedure Name: process_recall_work. */
588 /* Description: This is the concurrent wrapper to process a set of recall lines. */
589 /* Generates SR, RO and WIP jobs based on the params being passed. Logs */
590 /* all the error messages to error log, and will generate a report of all */
591 /* all the successful recall lines. Updates the csd_recall_lines table */
592 /* with the SR id, RO line id and wip entity id when done with the */
593 /* processing. Once done, will reset the processing_flag to N */
594 /* -- History: 24/03/2010, Created by Sudheer Bhat. */
595 /****************************************************************************************/
596
597 PROCEDURE PROCESS_RECALL_WORK (errbuf OUT NOCOPY VARCHAR2,
598 retcode OUT NOCOPY VARCHAR2,
599 p_group_id IN NUMBER,
600 p_sr_type_id IN NUMBER,
601 p_ro_type_id IN NUMBER DEFAULT NULL,
602 p_service_code_id IN NUMBER DEFAULT NULL,
603 p_wip_accounting_class IN VARCHAR2 DEFAULT NULL,
604 p_upgrade_item_id IN NUMBER,
605 p_wip_inv_org_id IN NUMBER )
606 IS
607
608 lc_api_name CONSTANT VARCHAR2(60) := 'CSD.PLSQL.CP.CSD_RECALLS_PVT.CSD_RECALLS_PVT';
609 l_csd_recall_lines_tbl csd_recall_lines_tbl;
610 l_create_ro_flag BOOLEAN := FALSE;
611 l_create_job_flag BOOLEAN := FALSE;
612 l_index NUMBER;
613 l_service_request_rec csd_process_pvt.service_request_rec := csd_process_util.sr_rec;
614 l_sr_notes_tbl cs_servicerequest_pub.notes_table;
615 l_repln_rec csd_repairs_pub.repln_rec_type;
616 l_create_ro_flag BOOLEAN := FALSE;
617 l_ent_contracts oks_entitlements_pub.get_contop_tbl;
618 l_calc_resptime_flag VARCHAR2(1) := 'Y';
619 l_server_tz_id NUMBER;
620 l_contract_pl_id NUMBER;
621 l_profile_pl_id NUMBER;
622 l_currency_code VARCHAR2(5);
623 l_job_header_tbl job_header_tbl;
624 l_job_index NUMBER;
625 l_job_header_index NUMBER := 1;
626 l_group_id NUMBER := 0;
627 l_req_group_id NUMBER;
628 l_rec_group_id NUMBER;
629 x_incident_id NUMBER;
630 x_incident_number VARCHAR2(30);
631 x_return_status VARCHAR2(1);
632 x_msg_data VARCHAR2(2000);
633 x_msg_count NUMBER;
634 x_repair_line_id NUMBER;
635 x_repair_number VARCHAR2(30);
636 x_job_name VARCHAR2(30);
637 x_request_id NUMBER;
638 l_temp VARCHAR2(200);
639 l_job_status NUMBER := 0;
640
641
642 CURSOR eligible_lines IS
643 SELECT crl.recall_line_id,
644 crl.instance_id,
645 crl.owner_account_id,
646 crl.owner_party_id,
647 crl.inventory_item_id,
648 crl.revision,
649 crl.serial_number,
650 crl.lot_number,
651 crl.incident_id,
652 crl.repair_line_id,
653 crl.wip_entity_id,
654 cii.unit_of_measure,
655 cii.quantity
656 FROM csd_recall_lines crl,csi_item_instances cii
657 WHERE crl.processing_group_id = p_group_id
658 AND crl.processing_flag = 'Y'
659 AND crl.instance_id = cii.instance_id;
660
661 BEGIN
662 IF fnd_conc_global.request_data IS NOT NULL
663 THEN
664 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
665 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Post wip mass load');
666 END IF;
667 l_rec_group_id := TO_NUMBER(SUBSTR(fnd_conc_global.request_data,0,INSTR(fnd_conc_global.request_data,',')-1));
668 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
669 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Step1');
670 END IF;
671 l_temp := SUBSTR(fnd_conc_global.request_data,INSTR(fnd_conc_global.request_data,',')+1);
672 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
673 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Step2');
674 END IF;
675 l_req_group_id := TO_NUMBER(SUBSTR(l_temp,0,INSTR(l_temp,',')-1));
676 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
677 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Step3');
678 END IF;
679 l_temp := SUBSTR(l_temp,INSTR(l_temp,',')+1);
680
681 g_sc_id := TO_NUMBER(SUBSTR(l_temp,0,INSTR(l_temp,',')-1));
682 g_upgrade_item_id := TO_NUMBER(SUBSTR(l_temp,INSTR(l_temp,',')+1));
683
684 process_post_wip_massload(l_rec_group_id,l_req_group_id);
685
686 UPDATE csd_recall_lines SET processing_flag = 'N'
687 WHERE processing_group_id = l_rec_group_id;
688 -- write the output.
689 write_to_conc_out(p_group_id);
690 RETURN;
691 END IF;
692
693 SAVEPOINT PROCESS_RECALL_WORK;
694
695 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
696 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin PROCESS_RECALL_WORK API');
697 END IF;
698
699 OPEN eligible_lines;
700 FETCH eligible_lines BULK COLLECT INTO l_csd_recall_lines_tbl;
701 CLOSE eligible_lines;
702 -- processing logic.
703 -- for all the recall lines, determine what all needs to be created.
704 -- till RO creation, we have to go row by row.
705 -- for the wip job creation, we will insert all the eligible records into the interface table
706 -- and call wip mass load just once.
707
708 FOR l_index IN 1 ..l_csd_recall_lines_tbl.COUNT
709 LOOP
710 IF l_csd_recall_lines_tbl(l_index).incident_id IS NULL
711 THEN
712 -- get the bill to site use id and ship to site use id.
713 IF g_csd_shipto_billto_cache.EXISTS(l_csd_recall_lines_tbl(l_index).owner_party_id)
714 THEN
715 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
716 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting the bill to and ship to site from cache');
717 END IF;
718 l_service_request_rec.bill_to_site_use_id :=
719 g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id;
720 l_service_request_rec.ship_to_site_use_id :=
721 g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id;
722 l_service_request_rec.caller_type :=
723 g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type;
724 ELSE
725 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
726 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting bill to and ship to site.');
727 END IF;
728 SELECT hpu.party_site_use_id,
729 hpu1.party_site_use_id
730 INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id,
731 g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id
732 FROM hz_party_sites hps,
733 hz_party_site_uses hpu,
734 hz_party_sites hps1,
735 hz_party_site_uses hpu1
736 WHERE hps.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
737 AND hps.party_site_id = hpu.party_site_id
738 AND hpu.site_use_type = 'BILL_TO'
739 AND hpu.primary_per_type = 'Y'
740 AND hps1.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
741 AND hps1.party_site_id = hpu1.party_site_id
742 AND hpu1.site_use_type = 'SHIP_TO'
743 AND hpu1.primary_per_type = 'Y'
744 AND rownum < 2;
745
746 l_service_request_rec.bill_to_site_use_id :=
747 g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id;
748 l_service_request_rec.ship_to_site_use_id :=
749 g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id;
750
751 SELECT party_type
752 INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type
753 FROM hz_parties
754 WHERE party_id = l_csd_recall_lines_tbl(l_index).owner_party_id;
755
756 l_service_request_rec.caller_type :=
757 g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type;
758 END IF;
759
760 -- Get other SR defaults.
761 l_service_request_rec.type_id := p_sr_type_id;
762 l_service_request_rec.resource_type := g_sr_owner_type;
763 l_service_request_rec.sr_creation_channel := 'PHONE';
764 l_service_request_rec.request_date := sysdate;
765 l_service_request_rec.customer_id := l_csd_recall_lines_tbl(l_index).owner_party_id;
766 l_service_request_rec.account_id := l_csd_recall_lines_tbl(l_index).owner_account_id;
767 l_service_request_rec.customer_number := null;
768 l_service_request_rec.customer_product_id := null;
769 l_service_request_rec.cp_ref_number := null;
770 l_service_request_rec.inv_item_revision := null;
771 l_service_request_rec.inventory_item_id := null;
772 l_service_request_rec.inventory_org_id := null;
773 l_service_request_rec.current_serial_number := null;
774 l_service_request_rec.original_order_number := null;
775 l_service_request_rec.purchase_order_num := null;
776 l_service_request_rec.problem_code := null;
777 l_service_request_rec.exp_resolution_date := null;
778 l_service_request_rec.contract_id := null;
779 l_service_request_rec.cust_po_number := null;
780 l_service_request_rec.cp_revision_id := null;
781 l_service_request_rec.sr_contact_point_id := null;
782 l_service_request_rec.party_id := null;
783 l_service_request_rec.contact_point_id := null;
784 l_service_request_rec.contact_point_type := null;
785 l_service_request_rec.primary_flag := null;
786 l_service_request_rec.contact_type := null;
787 l_service_request_rec.owner_group_id := NULL;
788 l_service_request_rec.publish_flag := '';
789
790 IF g_sr_params_cached
791 THEN
792 l_service_request_rec.status_id := g_sr_status;
793 l_service_request_rec.severity_id := g_sr_severity;
794 l_service_request_rec.urgency_id := g_sr_urgency;
795 l_service_request_rec.owner_id := g_sr_owner;
796 l_service_request_rec.summary := g_sr_summary;
797 ELSE
798 SELECT sr_status,
799 sr_severity,
800 sr_urgency,
801 sr_owner,
802 sr_summary
803 INTO g_sr_status,
804 g_sr_severity,
805 g_sr_urgency,
806 g_sr_owner,
807 g_sr_summary
808 FROM csd_recall_parameters
809 WHERE organization_id = FND_PROFILE.VALUE('CSD_DEF_REP_INV_ORG');
810
811 l_service_request_rec.status_id := g_sr_status;
812 l_service_request_rec.severity_id := g_sr_severity;
813 l_service_request_rec.urgency_id := g_sr_urgency;
814 l_service_request_rec.owner_id := g_sr_owner;
815 l_service_request_rec.summary := g_sr_summary;
816 g_sr_params_cached := true;
817 END IF;
818
819 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
820 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Before calling CSD_PROCESS_PVT.process_service_request');
821 END IF;
822
823 -- call service request API.
824 CSD_PROCESS_PVT.process_service_request
825 ( p_api_version => 1.0,
826 p_commit => fnd_api.g_false,
827 p_init_msg_list => fnd_api.g_true,
828 p_validation_level => fnd_api.g_valid_level_full,
829 p_action => 'CREATE',
830 p_incident_id => NULL,
831 p_service_request_rec => l_service_request_rec,
832 p_notes_tbl => l_sr_notes_tbl,
833 x_incident_id => x_incident_id,
834 x_incident_number => x_incident_number,
835 x_return_status => x_return_status,
836 x_msg_count => x_msg_count,
837 x_msg_data => x_msg_data
838 );
839 IF NOT G_RET_STS_SUCCESS = x_return_status
840 THEN
841 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
842 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in CSD_PROCESS_PVT.process_service_request '||x_msg_data);
843 END IF;
844 -- write the error msg to fnd_log.out stream.
845 csd_bulk_receive_util.write_to_conc_log(p_msg_count => x_msg_count,
846 p_msg_data => x_msg_data);
847 RAISE fnd_api.g_exc_error;
848 END IF;
849 l_csd_recall_lines_tbl(l_index).incident_id := x_incident_id;
850
851 UPDATE csd_recall_lines SET incident_id = x_incident_id
852 WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
853 END IF;
854
855 -- check if RO creation is required.
856 IF l_csd_recall_lines_tbl(l_index).repair_line_id IS NULL AND p_ro_type_id IS NOT NULL
857 THEN
858 -- get the repair attributes.
859 -- get the Pricelist.
860 -- prepare the rec and create RO.
861 IF NOT g_ro_attribs_cached
862 THEN
863 SELECT auto_process_rma,
864 business_process_id,
865 repair_mode
866 INTO g_auto_process_rma,
867 g_business_process_id,
868 g_repair_mode
869 FROM csd_repair_types_b
870 WHERE repair_type_id = p_ro_type_id;
871 END IF;
872
873 -- get the contract
874 fnd_profile.get('SERVER_TIMEZONE_ID', l_server_tz_id);
875
876 -- bug#9808614. if bill to and ship to are not yet cached, do so.
877 IF NOT g_csd_shipto_billto_cache.EXISTS(l_csd_recall_lines_tbl(l_index).owner_party_id)
878 THEN
879 SELECT hpu.party_site_use_id,
880 hpu1.party_site_use_id
881 INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id,
882 g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id
883 FROM hz_party_sites hps,
884 hz_party_site_uses hpu,
885 hz_party_sites hps1,
886 hz_party_site_uses hpu1
887 WHERE hps.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
888 AND hps.party_site_id = hpu.party_site_id
889 AND hpu.site_use_type = 'BILL_TO'
890 AND hpu.primary_per_type = 'Y'
891 AND hps1.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
892 AND hps1.party_site_id = hpu1.party_site_id
893 AND hpu1.site_use_type = 'SHIP_TO'
894 AND hpu1.primary_per_type = 'Y'
895 AND rownum < 2;
896 END IF;
897
898 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
899 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_repairs_util.get_entitlements for contract info');
900 END IF;
901
902 csd_repairs_util.get_entitlements(
903 p_api_version_number => 1.0,
904 p_init_msg_list => fnd_api.g_false,
905 p_commit => fnd_api.g_false,
906 p_contract_number => null,
907 p_service_line_id => null,
908 p_customer_id => l_csd_recall_lines_tbl(l_index).owner_party_id ,
909 p_site_id => g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id,
910 p_customer_account_id => l_csd_recall_lines_tbl(l_index).owner_account_id,
911 p_system_id => null,
912 p_inventory_item_id => l_csd_recall_lines_tbl(l_index).inventory_item_id,
913 p_customer_product_id => l_csd_recall_lines_tbl(l_index).instance_id,
914 p_request_date => trunc(sysdate),
915 p_validate_flag => 'Y',
916 p_business_process_id => g_business_process_id,
917 p_severity_id => g_sr_severity,
918 p_time_zone_id => l_server_tz_id,
919 P_CALC_RESPTIME_FLAG => l_calc_resptime_flag,
920 x_ent_contracts => l_ent_contracts,
921 x_return_status => x_return_status,
922 x_msg_count => x_msg_count,
923 x_msg_data => x_msg_data);
924
925 IF l_ent_contracts.COUNT = 0
926 THEN
927 l_repln_rec.contract_line_id := null;
928 ELSE
929 l_repln_rec.contract_line_id := l_ent_contracts(1).service_line_id;
930 END IF;
931
932 -- get the pricelist and currency.
933
934 csd_process_util.get_ro_default_curr_pl(
935 p_api_version => 1.0,
936 p_init_msg_list => fnd_api.g_false,
937 p_incident_id => x_incident_id,
938 p_repair_type_id => p_ro_type_id,
939 p_ro_contract_line_id => l_repln_rec.contract_line_id,
940 x_contract_pl_id => l_contract_pl_id,
941 x_profile_pl_id => l_profile_pl_id,
942 x_currency_code => l_currency_code,
943 x_return_status => x_return_status,
944 x_msg_count => x_msg_count,
945 x_msg_data => x_msg_data );
946
947 IF l_contract_pl_id IS NOT NULL
948 THEN
949 l_repln_rec.price_list_header_id := l_contract_pl_id;
950 ELSE
951 l_repln_rec.price_list_header_id := l_profile_pl_id;
952 END IF;
953
954 l_repln_rec.currency_code := l_currency_code;
955
956 -- set below attributes as g_miss_*. So that defaulting engine can act in the
957 -- PVT API.
958 l_repln_rec.inventory_org_id := fnd_api.g_miss_num;
959 l_repln_rec.resource_group := fnd_api.g_miss_num;
960 l_repln_rec.ro_priority_code := fnd_api.g_miss_char;
961 l_repln_rec.resource_id := fnd_api.g_miss_num;
962
963 l_repln_rec.incident_id := NVL(x_incident_id,l_csd_recall_lines_tbl(l_index).incident_id);
964 l_repln_rec.customer_product_id := l_csd_recall_lines_tbl(l_index).instance_id;
965 l_repln_rec.auto_process_rma := g_auto_process_rma;
966 l_repln_rec.approval_required_flag := 'Y';
967 l_repln_rec.repair_type_id := p_ro_type_id;
968 l_repln_rec.repair_group_id := null;
969 l_repln_rec.repair_mode := g_repair_mode;
970 l_repln_rec.status := 'O';
971 l_repln_rec.inventory_item_id := l_csd_recall_lines_tbl(l_index).inventory_item_id;
972 l_repln_rec.item_revision := l_csd_recall_lines_tbl(l_index).revision;
973 l_repln_rec.serial_number := l_csd_recall_lines_tbl(l_index).serial_number;
974 l_repln_rec.quantity := l_csd_recall_lines_tbl(l_index).quantity;
975 l_repln_rec.unit_of_measure := l_csd_recall_lines_tbl(l_index).uom_code;
976
977 -- we dont try to default the resolve by date. The create repair order public has an
978 -- issue when resolve by date is not passed.
979 l_repln_rec.resolve_by_date := null;
980 x_repair_line_id := null;
981
982 -- call create repair order PVT API.
983 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
984 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling Create_Repair_Order for creating RO');
985 END IF;
986 CSD_REPAIRS_PVT.Create_Repair_Order(
987 p_api_version_number => 1.0,
988 p_commit => fnd_api.g_false,
989 p_init_msg_list => fnd_api.g_true,
990 p_validation_level => fnd_api.g_valid_level_full,
991 p_repair_line_id => x_repair_line_id,
992 p_Repln_Rec => l_repln_rec,
993 x_repair_line_id => x_repair_line_id,
994 x_repair_number => x_repair_number,
995 x_return_status => x_return_status,
996 x_msg_count => x_msg_count,
997 x_msg_data => x_msg_data );
998
999 IF NOT G_RET_STS_SUCCESS = x_return_status
1000 THEN
1001 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1002 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in Create_Repair_Order '||x_msg_data);
1003 END IF;
1004 -- write the error msg to fnd_log.out stream.
1005 csd_bulk_receive_util.write_to_conc_log(p_msg_count => x_msg_count,
1006 p_msg_data => x_msg_data);
1007 RAISE fnd_api.g_exc_error;
1008 END IF;
1009
1010 l_csd_recall_lines_tbl(l_index).repair_line_id := x_repair_line_id;
1011
1012 UPDATE csd_recall_lines SET repair_line_id = x_repair_line_id
1013 WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
1014 END IF;
1015
1016 -- default logistics lines.
1017 csd_process_pvt.create_default_prod_txn
1018 (p_api_version => 1.0,
1019 p_commit => fnd_api.g_false,
1020 p_init_msg_list => fnd_api.g_true,
1021 p_validation_level => fnd_api.g_valid_level_full,
1022 p_repair_line_id => x_repair_line_id,
1023 x_return_status => x_return_status,
1024 x_msg_count => x_msg_count,
1025 x_msg_data => x_msg_data);
1026 -- we wont check for the status of the logistics lines here. Even if the auto creation fails, we create all
1027 -- other possible entities. The logistics lines can be manually entered in later.
1028 -- WIP job creation
1029
1030 IF l_csd_recall_lines_tbl(l_index).repair_line_id IS NOT NULL AND p_service_code_id IS NOT NULL
1031 THEN
1032 IF l_job_status = 0 THEN
1033 SELECT wip_job_status
1034 INTO l_job_status
1035 FROM csd_recall_parameters
1036 WHERE organization_id = FND_PROFILE.VALUE('CSD_DEF_REP_INV_ORG');
1037 END IF;
1038 -- find the bill and route from the service code provided.
1039 -- prepare the job attributes.
1040 IF NOT g_bill_details_cached
1041 THEN
1042 SELECT bom.assembly_item_id bom_reference_id,
1043 bom.alternate_bom_designator,
1044 bor.assembly_item_id routing_reference_id,
1045 bor.alternate_routing_designator,
1046 bor.completion_subinventory,
1047 bor. completion_locator_id
1048 BULK COLLECT INTO
1049 g_bill_id,
1050 g_alt_bill,
1051 g_routing_id,
1052 g_alt_routing,
1053 g_completion_subinv,
1054 g_completion_locid
1055 FROM csd_sc_work_entities cscwe,
1056 bom_bill_of_materials bom ,
1057 bom_operational_routings bor
1058 WHERE cscwe.service_code_id = p_service_code_id
1059 AND cscwe.work_entity_type_code = 'BOM'
1060 AND cscwe.work_entity_id3 = fnd_profile.value('csd_def_rep_inv_org')
1061 AND cscwe.work_entity_id1 = bom.bill_sequence_id (+)
1062 AND cscwe.work_entity_id2 = bor.routing_sequence_id (+);
1063
1064 g_bill_details_cached := TRUE;
1065 g_sc_id := p_service_code_id;
1066
1067 END IF;
1068
1069 --l_job_header_index := l_job_header_index + 1;
1070 IF g_bill_id.COUNT = 0
1071 THEN
1072 l_job_header_tbl(l_job_header_index).source_code := 'DEPOT_REPAIR';
1073 l_job_header_tbl(l_job_header_index).source_line_id := l_csd_recall_lines_tbl(l_index).repair_line_id;
1074 l_job_header_tbl(l_job_header_index).organization_id := NVL(p_wip_inv_org_id,fnd_profile.value('csd_def_rep_inv_org'));
1075 l_job_header_tbl(l_job_header_index).status_type := NVL(l_job_status,1);
1076 l_job_header_tbl(l_job_header_index).load_type := 4;
1077 l_job_header_tbl(l_job_header_index).process_phase := 2;
1078 l_job_header_tbl(l_job_header_index).process_status := 1;
1079 l_job_header_tbl(l_job_header_index).creation_date := SYSDATE;
1080 l_job_header_tbl(l_job_header_index).last_update_date := SYSDATE;
1081 l_job_header_tbl(l_job_header_index).created_by := fnd_global.user_id;
1082 l_job_header_tbl(l_job_header_index).last_updated_by := fnd_global.user_id;
1083 l_job_header_tbl(l_job_header_index).last_update_login := fnd_global.login_id;
1084 l_job_header_tbl(l_job_header_index).primary_item_id := NVL(p_upgrade_item_id,l_csd_recall_lines_tbl(l_index).inventory_item_id);
1085 l_job_header_tbl(l_job_header_index).start_quantity := l_csd_recall_lines_tbl(l_index).quantity;
1086 l_job_header_tbl(l_job_header_index).first_unit_start_date := sysdate;
1087 l_job_header_tbl(l_job_header_index).last_unit_completion_date := sysdate + 1;
1088
1089 CSD_WIP_JOB_PVT.generate_job_name(g_job_prefix,l_job_header_tbl(l_job_header_index).organization_id,x_job_name);
1090 l_job_header_tbl(l_job_header_index).job_name := x_job_name;
1091 l_job_header_tbl(l_job_header_index).class_code := p_wip_accounting_class;
1092
1093 IF l_group_id = 0
1094 THEN
1095 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
1096 END IF;
1097 l_job_header_tbl(l_job_header_index).group_id := l_group_id;
1098 l_job_header_index := l_job_header_index + 1; -- bug#9754933
1099
1100 ELSE
1101 FOR l_job_index IN 1 ..g_bill_id.COUNT
1102 LOOP
1103 l_job_header_tbl(l_job_header_index).source_code := 'DEPOT_REPAIR';
1104 l_job_header_tbl(l_job_header_index).source_line_id := l_csd_recall_lines_tbl(l_index).repair_line_id;
1105 l_job_header_tbl(l_job_header_index).organization_id := NVL(p_wip_inv_org_id,fnd_profile.value('csd_def_rep_inv_org'));
1106 l_job_header_tbl(l_job_header_index).status_type := NVL(l_job_status,1);
1107 l_job_header_tbl(l_job_header_index).load_type := 4;
1108 l_job_header_tbl(l_job_header_index).process_phase := 2;
1109 l_job_header_tbl(l_job_header_index).process_status := 1;
1110 l_job_header_tbl(l_job_header_index).creation_date := SYSDATE;
1111 l_job_header_tbl(l_job_header_index).last_update_date := SYSDATE;
1112 l_job_header_tbl(l_job_header_index).created_by := fnd_global.user_id;
1113 l_job_header_tbl(l_job_header_index).last_updated_by := fnd_global.user_id;
1114 l_job_header_tbl(l_job_header_index).last_update_login := fnd_global.login_id;
1115 l_job_header_tbl(l_job_header_index).primary_item_id := NVL(p_upgrade_item_id,l_csd_recall_lines_tbl(l_index).inventory_item_id);
1116 l_job_header_tbl(l_job_header_index).start_quantity := l_csd_recall_lines_tbl(l_index).quantity;
1117 l_job_header_tbl(l_job_header_index).routing_reference_id := g_routing_id(l_job_index);
1118 l_job_header_tbl(l_job_header_index).bom_reference_id := g_bill_id(l_job_index);
1119 l_job_header_tbl(l_job_header_index).alternate_routing_designator := g_alt_routing(l_job_index);
1120 l_job_header_tbl(l_job_header_index).alternate_bom_designator := g_alt_bill(l_job_index);
1121 l_job_header_tbl(l_job_header_index).completion_subinventory := g_completion_subinv(l_job_index);
1122 l_job_header_tbl(l_job_header_index).completion_locator_id := g_completion_locid(l_job_index);
1123 l_job_header_tbl(l_job_header_index).first_unit_start_date := sysdate;
1124
1125 CSD_WIP_JOB_PVT.generate_job_name(g_job_prefix,l_job_header_tbl(l_job_header_index).organization_id,x_job_name);
1126 l_job_header_tbl(l_job_header_index).job_name := x_job_name;
1127 l_job_header_tbl(l_job_header_index).class_code := p_wip_accounting_class;
1128
1129 IF l_group_id = 0
1130 THEN
1131 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
1132 END IF;
1133 l_job_header_tbl(l_job_header_index).group_id := l_group_id;
1134 l_job_header_index := l_job_header_index + 1;
1135 END LOOP;
1136 END IF;
1137 END IF;
1138
1139 END LOOP;
1140
1141 IF l_job_header_tbl.COUNT = 0
1142 THEN
1143 -- clean up the processing_flag value. No more processing to be done as part of this program.
1144 UPDATE csd_recall_lines SET processing_flag = 'N'
1145 WHERE processing_group_id = p_group_id;
1146
1147 -- write the output to concurrent program's out file.
1148 -- if WIP mass load is launched, we will defer it till mass load is completed.
1149 write_to_conc_out(p_group_id);
1150 COMMIT WORK;
1151 RETURN;
1152 END IF;
1153 -- insert the job header rec into wip_job_schedule_interface_table and call the CP.
1154 FORALL j IN 1 ..l_job_header_tbl.COUNT
1155 INSERT INTO wip_job_schedule_interface VALUES l_job_header_tbl(j);
1156
1157 COMMIT WORK;
1158 -- submit wip mass load as child request for the entire group.
1159 x_request_id := fnd_request.submit_request (
1160 application => 'WIP',
1161 program => 'WICMLP',
1162 description => NULL,
1163 start_time => NULL,
1164 sub_request => TRUE,
1165 argument1 => TO_CHAR(l_group_id),
1166 argument2 => 0,
1167 argument3 => NULL );
1168
1169 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1170 request_data => TO_CHAR(p_group_id)||','||TO_CHAR(l_group_id)||','||TO_CHAR(p_service_code_id)||','||TO_CHAR(p_upgrade_item_id));
1171
1172 EXCEPTION
1173 WHEN fnd_api.g_exc_error THEN
1174 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1175 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Exc error '||x_msg_data);
1176 END IF;
1177 errbuf := x_msg_data;
1178 retcode := 2;
1179 ROLLBACK TO PROCESS_RECALL_WORK;
1180 -- clean up the processing flag.
1181 UPDATE csd_recall_lines SET processing_flag = 'N'
1182 WHERE processing_group_id = p_group_id;
1183 COMMIT;
1184 WHEN OTHERS THEN
1185 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1186 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'When Others '||SQLERRM);
1187 END IF;
1188 ROLLBACK;
1189 errbuf := x_msg_data;
1190 retcode := 2;
1191 -- clean up the processing flag.
1192 UPDATE csd_recall_lines SET processing_flag = 'N'
1193 WHERE processing_group_id = p_group_id;
1194 COMMIT;
1195 -- write the error msg to fnd_log.out stream.
1196 csd_bulk_receive_util.write_to_conc_log(p_msg_count => x_msg_count,
1197 p_msg_data => x_msg_data);
1198 RAISE;
1199
1200 END PROCESS_RECALL_WORK;
1201
1202 /****************************************************************************************/
1203 /* Procedure Name: refresh_recall_metrics */
1204 /* Description: Refreshes the recall metrics for the recall number if passed, else */
1205 /* refreshes the metrics for all the open recalls. This program runs as */
1206 /* concurrent program. */
1207 /* -- History: 30/03/2010, Created by Sudheer Bhat. */
1208 /****************************************************************************************/
1209 PROCEDURE REFRESH_RECALL_METRICS(errbuf OUT NOCOPY VARCHAR2,
1210 retcode OUT NOCOPY VARCHAR2,
1211 p_recall_number IN VARCHAR2 DEFAULT NULL )
1212 IS
1213 l_exists NUMBER;
1214 l_metric_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1215 l_accumulated_cost JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1216 l_wip_jobs_with_costs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1217 l_wip_jobs_without_costs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1218 l_remaining_cost JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1219 l_gen_num_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1220 l_recall_numbers JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1221 l_recall_numbers_temp JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1222 BEGIN
1223 -- check if the line exists for the recall number if passed else we will bring the metrics
1224 -- table in synch with all the recalls.
1225 IF p_recall_number IS NOT NULL
1226 THEN
1227 l_metric_ids.EXTEND;
1228 BEGIN
1229 SELECT metric_id
1230 INTO l_metric_ids(1)
1231 FROM csd_recall_metrics
1232 WHERE recall_number = p_recall_number;
1233 EXCEPTION
1234 WHEN NO_DATA_FOUND THEN
1235 l_metric_ids(1) := -1;
1236 END;
1237 IF l_metric_ids(1) = -1
1238 THEN
1239 INSERT INTO csd_recall_metrics(metric_id,recall_number) values(csd_recall_metrics_s1.NEXTVAL,p_recall_number)
1240 RETURNING metric_id INTO l_metric_ids(1);
1241 END IF;
1242 ELSE
1243 INSERT INTO csd_recall_metrics(metric_id,recall_number)
1244 (SELECT csd_recall_metrics_s1.NEXTVAL,recall_number
1245 FROM csd_recall_headers_b,csd_recall_statuses_b crs
1246 WHERE recall_flow_status_id = crs.status_id
1247 AND crs.status_code <> 'C'
1248 AND NOT EXISTS ( SELECT 'exists'
1249 FROM csd_recall_metrics crm
1250 WHERE crm.recall_number = recall_number )
1251 AND recall_id in (SELECT MAX(recall_id) FROM csd_recall_headers_b
1252 GROUP BY recall_number ));
1253
1254 SELECT DISTINCT metric_id
1255 BULK COLLECT INTO l_metric_ids
1256 FROM csd_recall_metrics crm,
1257 csd_recall_headers_b crh,
1258 csd_recall_statuses_b crs
1259 WHERE crh.recall_number = crm.recall_number
1260 AND crh.recall_flow_status_id = crs.status_id
1261 AND crs.status_code <> 'C'
1262 AND crm.metric_id NOT IN (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)));
1263 END IF;
1264
1265 -- number of units recalled.
1266 SELECT crh.recall_number,SUM(cii.quantity)
1267 BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1268 FROM csd_recall_metrics crm,
1269 csd_recall_headers_b crh,
1270 csd_recall_lines crl,
1271 csi_item_instances cii
1272 WHERE crm.metric_id IN (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1273 AND crm.recall_number = crh.recall_number
1274 AND crh.recall_id = crl.recall_id
1275 AND crl.instance_id = cii.instance_id
1276 GROUP BY crh.recall_number;
1277
1278 --dbms_output.put_line('first bulk collect');
1279
1280 FORALL i IN 1 ..l_recall_numbers.COUNT
1281 UPDATE csd_recall_metrics SET recalled_units = l_gen_num_tbl(i)
1282 WHERE recall_number = l_recall_numbers(i);
1283
1284 --dbms_output.put_line('after for all update');
1285
1286 SELECT crh.recall_number, COUNT(DISTINCT crl.owner_party_id)
1287 BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1288 FROM csd_recall_metrics crm,
1289 csd_recall_headers_b crh,
1290 csd_recall_lines crl
1291 WHERE crm.metric_id IN (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1292 AND crm.recall_number = crh.recall_number
1293 AND crh.recall_id = crl.recall_id
1294 GROUP BY crh.recall_number;
1295
1296 --dbms_output.put_line('2nd bulk collect');
1297 -- number of customers impacted.
1298 FORALL i IN 1 ..l_recall_numbers.COUNT
1299 UPDATE csd_recall_metrics SET customers_impacted = l_gen_num_tbl(i)
1300 WHERE recall_number = l_recall_numbers(i);
1301
1302 --dbms_output.put_line('after for all update');
1303
1304 SELECT crh.recall_number,
1305 SUM(wpb.tl_resource_in+pl_resource_in+tl_overhead_in+pl_material_in+pl_material_overhead_in+pl_overhead_in)
1306 BULK COLLECT INTO l_recall_numbers, l_accumulated_cost
1307 FROM csd_recall_metrics crm,
1308 csd_recall_headers_b crh,
1309 csd_recall_lines crl,
1310 csd_repair_job_xref crj,
1311 wip_period_balances wpb
1312 WHERE crm.metric_id IN
1313 (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1314 AND crm.recall_number = crh.recall_number
1315 AND crh.recall_id = crl.recall_id
1316 AND crl.repair_line_id = crj.repair_line_id
1317 AND crj.wip_entity_id = wpb.wip_entity_id
1318 GROUP BY crh.recall_number
1319 ORDER BY crh.recall_number;
1320
1321 --dbms_output.put_line('3rd bulk collect');
1322
1323 FORALL i IN 1 ..l_recall_numbers.COUNT
1324 UPDATE csd_recall_metrics SET accumulated_costs = l_accumulated_cost(i)
1325 WHERE recall_number = l_recall_numbers(i);
1326
1327 -- WIP jobs which have not been costed yet.Take the sum of remaining quantity.
1328 SELECT recall_number,
1329 SUM(QUANTITY)
1330 BULK COLLECT INTO l_recall_numbers, l_wip_jobs_without_costs
1331 FROM
1332 (SELECT crh.recall_number,
1333 SUM(crj.quantity) quantity
1334 FROM csd_recall_metrics crm,
1335 csd_recall_headers_b crh,
1336 csd_recall_lines crl,
1337 csd_repair_job_xref crj
1338 WHERE crm.metric_id IN
1339 (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)) )
1340 AND crm.recall_number = crh.recall_number
1341 AND crh.recall_id = crl.recall_id
1342 AND crl.repair_line_id = crj.repair_line_id
1343 AND NOT EXISTS
1344 (SELECT 'exists'
1345 FROM wip_period_balances wpb,
1346 wip_discrete_jobs wdj
1347 WHERE wpb.wip_entity_id = crj.wip_entity_id
1348 AND wpb.wip_entity_id = wdj.wip_entity_id
1349 AND wdj.status_type NOT IN (7,12,4,5)
1350 GROUP BY wpb.wip_entity_id
1351 HAVING SUM(tl_resource_in+pl_resource_in+tl_overhead_in+pl_material_in+pl_material_overhead_in+pl_overhead_in) > 0
1352 )
1353 GROUP BY crh.recall_number
1354
1355 UNION ALL
1356
1357 SELECT crh.recall_number,
1358 SUM(cii.quantity) quantity
1359 FROM csd_recall_metrics crm,
1360 csd_recall_headers_b crh,
1361 csd_recall_lines crl,
1362 csi_item_instances cii,
1363 cs_incidents_all_b sr,
1364 cs_incident_statuses_b cis
1365 WHERE crm.metric_id IN
1366 (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1367 AND crm.recall_number = crh.recall_number
1368 AND crh.recall_id = crl.recall_id
1369 AND crl.instance_id = cii.instance_id
1370 AND crl.incident_id IS NOT NULL
1371 AND crl.incident_id = sr.incident_id
1372 AND sr.incident_status_id = cis.incident_status_id
1373 AND cis.status_code <> 'CLOSED'
1374 AND NOT EXISTS
1375 (SELECT 'exists'
1376 FROM csd_repairs cr,
1377 csd_repair_job_xref crj1
1378 WHERE cr.incident_id = crl.incident_id
1379 AND cr.repair_line_id = crj1.repair_line_id
1380 )
1381 GROUP BY crh.recall_number
1382 )
1383 GROUP BY recall_number
1384 ORDER BY recall_number;
1385
1386 --dbms_output.put_line('4th bulk collect');
1387 -- find out the number of units considered for accrued cost calculation.
1388 l_recall_numbers_temp := l_recall_numbers;
1389 SELECT crh.recall_number, SUM(crj.quantity)
1390 BULK COLLECT INTO l_recall_numbers,l_wip_jobs_with_costs
1391 FROM csd_recall_headers_b crh,
1392 csd_recall_lines crl,
1393 csd_repair_job_xref crj
1394 WHERE crh.recall_number IN
1395 ( SELECT * FROM TABLE(CAST(l_recall_numbers_temp AS JTF_VARCHAR2_TABLE_100)))
1396 AND crh.recall_id = crl.recall_id
1397 AND crl.repair_line_id = crj.repair_line_id
1398 AND EXISTS (
1399 SELECT 'exists'
1400 FROM wip_period_balances
1401 WHERE wip_entity_id = crj.wip_entity_id
1402 GROUP BY wip_entity_id
1403 HAVING SUM(tl_resource_in+pl_resource_in+tl_overhead_in+pl_material_in+pl_material_overhead_in+pl_overhead_in) > 0)
1404 GROUP BY crh.recall_number
1405 ORDER BY crh.recall_number;
1406
1407 -- update the actual cost, and estimated cost remaining.
1408 FORALL i IN 1 ..l_recall_numbers.COUNT
1409 UPDATE csd_recall_metrics SET remaining_cost = (l_wip_jobs_without_costs(i) * (accumulated_costs/l_wip_jobs_with_costs(i)))
1410 WHERE recall_number = l_recall_numbers(i);
1411
1412 -- remediated units.
1413
1414 SELECT crh.recall_number,
1415 SUM(cii.quantity)
1416 BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1417 FROM csd_recall_metrics crm,
1418 csd_recall_headers_b crh,
1419 csd_recall_lines crl,
1420 cs_incidents_all_b sr,
1421 cs_incident_statuses_b cis,
1422 csi_item_instances cii
1423 WHERE crm.metric_id IN
1424 (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1425 AND crm.recall_number = crh.recall_number
1426 AND crh.recall_id = crl.recall_id
1427 AND crl.incident_id = sr.incident_id
1428 AND sr.incident_status_id = cis.incident_status_id
1429 AND cis.status_code = 'CLOSED'
1430 AND crl.instance_id = cii.instance_id
1431 GROUP BY crh.recall_number;
1432
1433 IF l_recall_numbers.COUNT = 0 OR l_recall_numbers.COUNT < l_metric_ids.COUNT
1434 THEN
1435 FORALL i IN 1 ..l_metric_ids.COUNT
1436 UPDATE csd_recall_metrics SET remediated_units = 0
1437 WHERE metric_id = l_metric_ids(i)
1438 AND recall_number NOT IN
1439 (SELECT * FROM TABLE(CAST(l_recall_numbers AS JTF_VARCHAR2_TABLE_100))) ;
1440 END IF;
1441
1442 IF l_recall_numbers.COUNT > 0
1443 THEN
1444 FORALL i IN 1 ..l_recall_numbers.COUNT
1445 UPDATE csd_recall_metrics SET remediated_units = l_gen_num_tbl(i)
1446 WHERE recall_number = l_recall_numbers(i);
1447 END IF;
1448 -- un - remediated units.
1449
1450 FORALL i IN 1 ..l_metric_ids.COUNT
1451 UPDATE csd_recall_metrics SET un_remediated_units = (recalled_units-nvl(remediated_units,0))
1452 WHERE metric_id = l_metric_ids(i);
1453
1454 -- remediated custoemers.
1455
1456 SELECT recall_number, COUNT(party_id)
1457 BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
1458 FROM (
1459 SELECT crh.recall_number,
1460 COUNT(crl.owner_party_id) party_id
1461 FROM csd_recall_metrics crm,
1462 csd_recall_headers_b crh,
1463 csd_recall_lines crl,
1464 cs_incidents_all_b sr,
1465 cs_incident_statuses_b cis
1466 WHERE crm.metric_id IN
1467 (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1468 AND crm.recall_number = crh.recall_number
1469 AND crh.recall_id = crl.recall_id
1470 AND crl.incident_id = sr.incident_id
1471 AND sr.incident_status_id = cis.incident_status_id
1472 AND cis.status_code = 'CLOSED'
1473 AND NOT EXISTS
1474 (SELECT 'exists'
1475 FROM csd_recall_headers_b crh1,
1476 csd_recall_lines crl1,
1477 cs_incidents_all_b sr1,
1478 cs_incident_statuses_b cis1,
1479 csd_recall_metrics crm1
1480 WHERE crm1.metric_id IN
1481 (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
1482 AND crm1.recall_number = crh1.recall_number
1483 AND crh1.recall_id = crl1.recall_id
1484 AND crl1.owner_party_id = crl.owner_party_id
1485 AND crl1.incident_id = sr1.incident_id
1486 AND sr1.incident_status_id = cis1.incident_status_id
1487 AND cis1.status_code <> 'CLOSED'
1488 )
1489 GROUP BY crh.recall_number,crl.owner_party_id
1490 ) GROUP BY recall_number;
1491
1492 IF l_recall_numbers.COUNT = 0 OR l_recall_numbers.COUNT < l_metric_ids.COUNT
1493 THEN
1494 FORALL i IN 1 ..l_metric_ids.COUNT
1495 UPDATE csd_recall_metrics SET customers_remediated = 0
1496 WHERE metric_id = l_metric_ids(i)
1497 AND recall_number NOT IN
1498 (SELECT * FROM TABLE(CAST(l_recall_numbers AS JTF_VARCHAR2_TABLE_100))) ;
1499 END IF;
1500
1501 IF l_recall_numbers.COUNT <> 0
1502 THEN
1503 FORALL i IN 1 ..l_recall_numbers.COUNT
1504 UPDATE csd_recall_metrics SET customers_remediated = NVL(l_gen_num_tbl(i),0)
1505 WHERE recall_number = l_recall_numbers(i);
1506 END IF;
1507
1508 -- customers un remediated. also update the WHO columns.
1509 FORALL i IN 1 ..l_metric_ids.COUNT
1510 UPDATE csd_recall_metrics SET customers_un_remediated = (customers_impacted-nvl(customers_remediated,0)),
1511 last_update_date = sysdate,
1512 last_updated_by = fnd_global.user_id,
1513 last_update_login = fnd_global.user_id,
1514 object_version_number = object_version_number+1
1515 WHERE metric_id = l_metric_ids(i);
1516
1517 EXCEPTION
1518 WHEN OTHERS THEN
1519 retcode := 2;
1520 errbuf := 'Errored';
1521 --dbms_output.put_line(SQLERRM);
1522 RAISE;
1523
1524 END REFRESH_RECALL_METRICS;
1525
1526 END CSD_RECALLS_PVT;