[Home] [Help]
PACKAGE BODY: APPS.CSD_BULK_RECEIVE_UTIL
Source
1 PACKAGE BODY CSD_BULK_RECEIVE_UTIL AS
2 /* $Header: csdubrub.pls 120.30.12020000.5 2013/04/10 00:31:26 takwong ship $ */
3
4 -- private procedure declarations.
5 /*-----------------------------------------------------------------------*/
6 /**Procedure Name: after_under_receipt_prcs */
7 /**Description: This routine is called for all under-receipts. */
8 /** The routine will update the existing charge line quantity*/
9 /** and then link it to OM line */
10 /*-----------------------------------------------------------------------*/
11
12 procedure after_under_receipt_prcs (p_repair_line_id IN NUMBER,
13 p_order_header_id IN NUMBER,
14 p_order_line_id IN NUMBER,
15 p_received_qty IN NUMBER
16 );
17
18 procedure after_under_receipt_prcs (
19 p_repair_line_id IN NUMBER,
20 p_order_header_id IN NUMBER,
21 p_order_line_id IN NUMBER,
22 p_received_qty IN NUMBER
23 ) IS
24 l_Charge_Details_rec CS_Charge_Details_PUB.Charges_Rec_Type;
25 l_Charge_Details_rec_upd CS_Charge_Details_PUB.Charges_Rec_Type;
26
27 l_prod_txns_rec CSD_PRODUCT_TRANSACTIONS%ROWTYPE;
28 -- default out params.
29 x_return_status VARCHAR2(5);
30 x_msg_data VARCHAR2(2000);
31 x_msg_count NUMBER;
32 x_object_version_number NUMBER;
33
34 x_line_number number;
35 x_estimate_detail_id number;
36 x_msg_index_out number;
37 l_update_charge_err exception;
38 l_create_charge_err exception;
39 BEGIN
40 -- entered
41 savepoint after_under_receipt_prcs;
42 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
43 fnd_log.STRING (fnd_log.level_procedure,
44 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
45 'Under-receipt: Entered the routine');
46 End if;
47 -- get the charges default values for the line to be updated/created.
48 -- fetch all the details required in a single sql.
49 BEGIN
50 SELECT estimate_detail_id,
51 charge_line_type,
52 org_id,
53 transaction_inventory_org,
54 business_process_id,
55 transaction_type_id,
56 inventory_item_id,
57 return_reason_code,
58 incident_id,
59 no_charge_flag,
60 currency_code,
61 price_list_header_id,
62 contract_id,
63 coverage_id,
64 bill_to_party_id,
65 --bill_to_account_id,
66 ship_to_party_id,
67 ship_to_account_id,
68 ship_to_org_id
69 INTO l_Charge_Details_rec.estimate_detail_id,
70 l_Charge_Details_rec.charge_line_type,
71 l_Charge_Details_rec.org_id,
72 l_Charge_Details_rec.transaction_inventory_org,
73 l_Charge_Details_rec.business_process_id,
74 l_Charge_Details_rec.transaction_type_id,
75 l_Charge_Details_rec.inventory_item_id_in,
76 l_Charge_Details_rec.return_reason_code,
77 l_Charge_Details_rec.incident_id,
78 l_Charge_Details_rec.no_charge_flag,
79 l_Charge_Details_rec.currency_code,
80 l_Charge_Details_rec.price_list_id,
81 l_Charge_Details_rec.contract_id,
82 l_Charge_Details_rec.coverage_id,
83 l_Charge_Details_rec.bill_to_party_id,
84 -- l_Charge_Details_rec.bill_to_account_id,
85 l_Charge_Details_rec.ship_to_party_id,
86 l_Charge_Details_rec.ship_to_account_id,
87 l_Charge_Details_rec.ship_to_org_id
88 FROM cs_estimate_details
89 WHERE source_id = p_repair_line_id
90 AND order_header_id = p_order_header_id
91 AND order_line_id = p_order_line_id;
92 EXCEPTION
93 WHEN NO_DATA_FOUND THEN
94 -- cannot be possible. And we cant get in here.
95 null;
96 END;
97 -- update the estimate_quantity to quantity received.
98 l_Charge_Details_rec_upd.quantity_required := p_received_qty;
99
100 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
101 fnd_log.STRING (fnd_log.level_procedure,
102 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
103 'Under-receipt: before updating order header_id');
104 End if;
105 -- need to fake as if the charges line is not submitted to OM.
106 update cs_estimate_details
107 set order_line_id = null --, order_header_id = null
108 where estimate_detail_id = l_Charge_Details_rec.estimate_detail_id;
109
110 -- from the create rec, copy the details required for update.
111 l_Charge_Details_rec_upd.estimate_detail_id := l_Charge_Details_rec.estimate_detail_id;
112 l_Charge_Details_rec_upd.charge_line_type := l_Charge_Details_rec.charge_line_type;
113 l_Charge_Details_rec_upd.org_id := l_Charge_Details_rec.org_id;
114 l_Charge_Details_rec_upd.transaction_inventory_org := l_Charge_Details_rec.transaction_inventory_org;
115 l_Charge_Details_rec_upd.business_process_id := l_Charge_Details_rec.business_process_id;
116 l_Charge_Details_rec_upd.transaction_type_id := l_Charge_Details_rec.transaction_type_id;
117 l_Charge_Details_rec_upd.inventory_item_id_in := l_Charge_Details_rec.inventory_item_id_in;
118
119 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
120 fnd_log.STRING (fnd_log.level_procedure,
121 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
122 'Under-receipt: Calling update charges API to update the quantity');
123 End if;
124 savepoint update_charge;
125 -- call charges API.
126 CS_Charge_Details_PUB.Update_Charge_Details
127 (
128 p_api_version => 1.0,
129 p_init_msg_list => 'F',
130 p_commit => 'F',
131 p_validation_level => 100,
132 x_return_status => x_return_status,
133 x_msg_count => x_msg_count,
134 x_object_version_number => x_object_version_number,
135 x_msg_data => x_msg_data,
136 p_transaction_control => 'T',
137 p_Charges_Rec => l_Charge_Details_rec_upd
138 --p_update_cost_detail => 'N'
139 );
140 --
141
142 if x_return_status <> 'S' THEN
143 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
144 fnd_log.STRING (fnd_log.level_procedure,
145 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
146 'Under-receipt: Return Status '||x_return_status||' Message '||x_msg_data);
147 End if;
148 raise l_update_charge_err;
149 end if;
150
151 -- reset the order number and order line values.
152 update cs_estimate_details
153 set order_header_id = p_order_header_id, order_line_id = p_order_line_id
154 where estimate_detail_id = l_Charge_Details_rec.estimate_detail_id;
155
156 -- Need to create a new charge line and associate it to existing OM line.
157
158 l_Charge_Details_rec.estimate_detail_id := null;
159
160 -- get the order line id for the new line.
161 begin
162 select oel2.line_id,
163 oel2.ordered_quantity,
164 oel2.order_quantity_uom
165 into l_Charge_Details_rec.order_line_id,
166 l_Charge_Details_rec.quantity_required,
167 l_Charge_Details_rec.unit_of_measure_code
168 from oe_order_lines_all oel1,
169 oe_order_lines_all oel2
170 where oel1.line_id = p_order_line_id
171 and oel1.line_set_id = oel2.line_set_id
172 and oel2.line_id <> p_order_line_id
173 and oel2.flow_status_code = 'AWAITING_RETURN';
174 exception
175 when no_data_found then
176 -- the OM line was not split.
177 null;
178 end;
179 l_Charge_Details_rec.order_header_id := p_order_header_id;
180 l_charge_details_rec.add_to_order_flag := 'Y';
181 l_Charge_Details_rec.source_code := 'DR';
182 l_Charge_Details_rec.original_source_code := 'DR';
183 l_Charge_Details_rec.interface_to_oe_flag := 'Y';
184 l_Charge_Details_rec.original_source_id := p_repair_line_id;
185
186 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
187 fnd_log.STRING (fnd_log.level_procedure,
188 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
189 'Under-receipt: before calling create charge details');
190 End if;
191 -- call the create charges API
192 savepoint create_charge;
193 cs_charge_details_pub.create_charge_details
194 (
195 p_api_version => 1.0,
196 p_init_msg_list => 'F',
197 p_commit => 'F',
198 p_validation_level => 100,
199 x_return_status => x_return_status,
200 x_msg_count => x_msg_count,
201 x_object_version_number => x_object_version_number,
202 x_msg_data => x_msg_data,
203 x_estimate_detail_id => x_estimate_detail_id,
204 x_line_number => x_line_number,
205 p_Charges_Rec => l_Charge_Details_rec
206 );
207
208 if x_return_status <> fnd_api.g_ret_sts_success then
209 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
210 fnd_log.STRING (fnd_log.level_procedure,
211 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
212 'Under-receipt: Error in creating charge lines: '|| x_msg_data);
213 End if;
214 raise l_create_charge_err;
215 end if;
216
217 -- need to create the corresponding record in the CSD_PRODUCT_TRANSACTIONS table.
218 -- Essentially all the values will be same as the existing record minus
219 -- estimate_detail_id and PROD_TXN_STATUS.
220 begin
221 select
222 CSD_PRODUCT_TRANSACTIONS_S1.nextval,
223 REPAIR_LINE_ID,
224 ACTION_TYPE,
225 ACTION_CODE,
226 LOT_NUMBER,
227 SUB_INVENTORY,
228 INTERFACE_TO_OM_FLAG,
229 BOOK_SALES_ORDER_FLAG,
230 RELEASE_SALES_ORDER_FLAG,
231 SHIP_SALES_ORDER_FLAG,
232 'BOOKED',
233 PROD_TXN_CODE,
234 SYSDATE,
235 SYSDATE,
236 FND_GLOBAL.USER_ID,
237 FND_GLOBAL.USER_ID,
238 FND_GLOBAL.USER_ID,
239 ATTRIBUTE1,
240 ATTRIBUTE2,
241 ATTRIBUTE3,
242 ATTRIBUTE4,
243 ATTRIBUTE5,
244 ATTRIBUTE6,
245 ATTRIBUTE7,
246 ATTRIBUTE8,
247 ATTRIBUTE9,
248 ATTRIBUTE10,
249 ATTRIBUTE11,
250 ATTRIBUTE12,
251 ATTRIBUTE13,
252 ATTRIBUTE14,
253 ATTRIBUTE15,
254 CONTEXT,
255 1,
256 REQ_HEADER_ID ,
257 REQ_LINE_ID ,
258 ORDER_HEADER_ID ,
259 SOURCE_SERIAL_NUMBER ,
260 SOURCE_INSTANCE_ID ,
261 NON_SOURCE_SERIAL_NUMBER ,
262 NON_SOURCE_INSTANCE_ID ,
263 LOCATOR_ID ,
264 PICKING_RULE_ID,
265 PROJECT_ID,
266 TASK_ID,
267 UNIT_NUMBER,
268 INTERNAL_PO_HEADER_ID
269
270 into
271 l_prod_txns_rec.product_transaction_id,
272 l_prod_txns_rec.REPAIR_LINE_ID,
273 l_prod_txns_rec.ACTION_TYPE,
274 l_prod_txns_rec.ACTION_CODE,
275 l_prod_txns_rec.LOT_NUMBER,
276 l_prod_txns_rec.SUB_INVENTORY,
277 l_prod_txns_rec.INTERFACE_TO_OM_FLAG,
278 l_prod_txns_rec.BOOK_SALES_ORDER_FLAG,
279 l_prod_txns_rec.RELEASE_SALES_ORDER_FLAG,
280 l_prod_txns_rec.SHIP_SALES_ORDER_FLAG,
281 l_prod_txns_rec.PROD_TXN_STATUS,
282 l_prod_txns_rec.PROD_TXN_CODE,
283 l_prod_txns_rec.LAST_UPDATE_DATE,
284 l_prod_txns_rec.CREATION_DATE,
285 l_prod_txns_rec.LAST_UPDATED_BY,
286 l_prod_txns_rec.CREATED_BY,
287 l_prod_txns_rec.LAST_UPDATE_LOGIN,
288 l_prod_txns_rec.ATTRIBUTE1,
289 l_prod_txns_rec.ATTRIBUTE2,
290 l_prod_txns_rec.ATTRIBUTE3,
291 l_prod_txns_rec.ATTRIBUTE4,
292 l_prod_txns_rec.ATTRIBUTE5,
293 l_prod_txns_rec.ATTRIBUTE6,
294 l_prod_txns_rec.ATTRIBUTE7,
295 l_prod_txns_rec.ATTRIBUTE8,
296 l_prod_txns_rec.ATTRIBUTE9,
297 l_prod_txns_rec.ATTRIBUTE10,
298 l_prod_txns_rec.ATTRIBUTE11,
299 l_prod_txns_rec.ATTRIBUTE12,
300 l_prod_txns_rec.ATTRIBUTE13,
301 l_prod_txns_rec.ATTRIBUTE14,
302 l_prod_txns_rec.ATTRIBUTE15,
303 l_prod_txns_rec.CONTEXT,
304 l_prod_txns_rec.OBJECT_VERSION_NUMBER,
305 l_prod_txns_rec.REQ_HEADER_ID ,
306 l_prod_txns_rec.REQ_LINE_ID ,
307 l_prod_txns_rec.ORDER_HEADER_ID ,
308 l_prod_txns_rec.SOURCE_SERIAL_NUMBER ,
309 l_prod_txns_rec.SOURCE_INSTANCE_ID ,
310 l_prod_txns_rec.NON_SOURCE_SERIAL_NUMBER ,
311 l_prod_txns_rec.NON_SOURCE_INSTANCE_ID ,
312 l_prod_txns_rec.LOCATOR_ID ,
313 l_prod_txns_rec.PICKING_RULE_ID,
314 l_prod_txns_rec.PROJECT_ID,
315 l_prod_txns_rec.TASK_ID,
316 l_prod_txns_rec.UNIT_NUMBER,
317 l_prod_txns_rec.INTERNAL_PO_HEADER_ID
318 from csd_product_transactions
319 where estimate_detail_id = l_charge_details_rec_upd.estimate_detail_id;
320 exception
321 when no_data_found then
322 -- somebody removed the row. should not happen.
323 null;
324 end;
325
326 l_prod_txns_rec.estimate_detail_id := x_estimate_detail_id;
327 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
328 fnd_log.STRING (fnd_log.level_procedure,
329 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
330 'Under-receipt: before calling insert: product txn id :'||l_prod_txns_rec.product_transaction_id);
331 End if;
332 -- insert the values into csd_product_transactions.
333 INSERT INTO CSD_PRODUCT_TRANSACTIONS(
334 PRODUCT_TRANSACTION_ID,
335 REPAIR_LINE_ID,
336 ESTIMATE_DETAIL_ID,
337 ACTION_TYPE,
338 ACTION_CODE,
339 LOT_NUMBER,
340 SUB_INVENTORY,
341 INTERFACE_TO_OM_FLAG,
342 BOOK_SALES_ORDER_FLAG,
343 RELEASE_SALES_ORDER_FLAG,
344 SHIP_SALES_ORDER_FLAG,
345 PROD_TXN_STATUS,
346 PROD_TXN_CODE,
347 LAST_UPDATE_DATE,
348 CREATION_DATE,
349 LAST_UPDATED_BY,
350 CREATED_BY,
351 LAST_UPDATE_LOGIN,
352 ATTRIBUTE1,
353 ATTRIBUTE2,
354 ATTRIBUTE3,
355 ATTRIBUTE4,
356 ATTRIBUTE5,
357 ATTRIBUTE6,
358 ATTRIBUTE7,
359 ATTRIBUTE8,
360 ATTRIBUTE9,
361 ATTRIBUTE10,
362 ATTRIBUTE11,
363 ATTRIBUTE12,
364 ATTRIBUTE13,
365 ATTRIBUTE14,
366 ATTRIBUTE15,
367 CONTEXT,
368 OBJECT_VERSION_NUMBER,
369 REQ_HEADER_ID ,
370 REQ_LINE_ID ,
371 ORDER_HEADER_ID ,
372 SOURCE_SERIAL_NUMBER ,
373 SOURCE_INSTANCE_ID ,
374 NON_SOURCE_SERIAL_NUMBER ,
375 NON_SOURCE_INSTANCE_ID ,
376 LOCATOR_ID ,
377 PICKING_RULE_ID,
378 PROJECT_ID,
379 TASK_ID,
380 UNIT_NUMBER,
381 INTERNAL_PO_HEADER_ID
382 ) VALUES (
383 l_prod_txns_rec.product_transaction_id,
384 l_prod_txns_rec.REPAIR_LINE_ID,
385 l_prod_txns_rec.estimate_detail_id,
386 l_prod_txns_rec.ACTION_TYPE,
387 l_prod_txns_rec.ACTION_CODE,
388 l_prod_txns_rec.LOT_NUMBER,
389 l_prod_txns_rec.SUB_INVENTORY,
390 l_prod_txns_rec.INTERFACE_TO_OM_FLAG,
391 l_prod_txns_rec.BOOK_SALES_ORDER_FLAG,
392 l_prod_txns_rec.RELEASE_SALES_ORDER_FLAG,
393 l_prod_txns_rec.SHIP_SALES_ORDER_FLAG,
394 l_prod_txns_rec.PROD_TXN_STATUS,
395 l_prod_txns_rec.PROD_TXN_CODE,
396 l_prod_txns_rec.LAST_UPDATE_DATE,
397 l_prod_txns_rec.CREATION_DATE,
398 l_prod_txns_rec.LAST_UPDATED_BY,
399 l_prod_txns_rec.CREATED_BY,
400 l_prod_txns_rec.LAST_UPDATE_LOGIN,
401 l_prod_txns_rec.ATTRIBUTE1,
402 l_prod_txns_rec.ATTRIBUTE2,
403 l_prod_txns_rec.ATTRIBUTE3,
404 l_prod_txns_rec.ATTRIBUTE4,
405 l_prod_txns_rec.ATTRIBUTE5,
406 l_prod_txns_rec.ATTRIBUTE6,
407 l_prod_txns_rec.ATTRIBUTE7,
408 l_prod_txns_rec.ATTRIBUTE8,
409 l_prod_txns_rec.ATTRIBUTE9,
410 l_prod_txns_rec.ATTRIBUTE10,
411 l_prod_txns_rec.ATTRIBUTE11,
412 l_prod_txns_rec.ATTRIBUTE12,
413 l_prod_txns_rec.ATTRIBUTE13,
414 l_prod_txns_rec.ATTRIBUTE14,
415 l_prod_txns_rec.ATTRIBUTE15,
416 l_prod_txns_rec.CONTEXT,
417 l_prod_txns_rec.OBJECT_VERSION_NUMBER,
418 l_prod_txns_rec.REQ_HEADER_ID ,
419 l_prod_txns_rec.REQ_LINE_ID ,
420 l_prod_txns_rec.ORDER_HEADER_ID ,
421 l_prod_txns_rec.SOURCE_SERIAL_NUMBER ,
422 l_prod_txns_rec.SOURCE_INSTANCE_ID ,
423 l_prod_txns_rec.NON_SOURCE_SERIAL_NUMBER ,
424 l_prod_txns_rec.NON_SOURCE_INSTANCE_ID ,
425 l_prod_txns_rec.LOCATOR_ID ,
426 l_prod_txns_rec.PICKING_RULE_ID,
427 l_prod_txns_rec.PROJECT_ID,
428 l_prod_txns_rec.TASK_ID,
429 l_prod_txns_rec.UNIT_NUMBER,
430 l_prod_txns_rec.INTERNAL_PO_HEADER_ID) ;
431
432
433 EXCEPTION
434 WHEN l_update_charge_err THEN
435 -- write the error message to bulk receive log.
436 write_to_conc_log(x_msg_count,x_msg_data);
437
438 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
439 FOR j in 1 ..x_msg_count
440 LOOP
441 FND_MSG_PUB.Get(
442 p_msg_index => j,
443 p_encoded => 'F',
444 p_data => x_msg_data,
445 p_msg_index_out => x_msg_index_out);
446 fnd_log.STRING (fnd_log.level_procedure,
447 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
448 'Under-receipt: Return Status '||x_msg_count||' Message '||x_msg_data);
449 END LOOP;
450 End if;
451 rollback to update_charge;
452
453 WHEN l_create_charge_err THEN
454 write_to_conc_log(x_msg_count,x_msg_data);
455
456 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
457 FOR j in 1 ..x_msg_count
458 LOOP
459 FND_MSG_PUB.Get(
460 p_msg_index => j,
461 p_encoded => 'F',
462 p_data => x_msg_data,
463 p_msg_index_out => x_msg_index_out);
464 fnd_log.STRING (fnd_log.level_procedure,
465 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
466 'Under-receipt: create charge err '||x_msg_count||' Message '||x_msg_data);
467 END LOOP;
468 End if;
469 rollback to create_charge;
470 WHEN OTHERS THEN
471 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
472 fnd_log.STRING (fnd_log.level_procedure,
473 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.AFTER_UNDER_RECEIPT_PRCS',
474 'Under-receipt: WHEN OTHERS : Message '||SQLERRM);
475 End if;
476 raise;
477
478 END after_under_receipt_prcs;
479
480 /*-----------------------------------------------------------------*/
481 /* procedure name: validate_bulk_receive_rec */
482 /* description : Validate Bulk Receive record definition */
483 /* */
484 /*-----------------------------------------------------------------*/
485
486 PROCEDURE validate_bulk_receive_rec
487 (
488 p_party_id IN NUMBER,
489 p_quantity IN NUMBER,
490 p_serial_number IN VARCHAR2,
491 p_inventory_item_id IN NUMBER,
492 x_warning_flag OUT NOCOPY VARCHAR2,
493 x_warning_reason_code OUT NOCOPY VARCHAR2,
494 x_change_owner_flag OUT NOCOPY VARCHAR2,
495 x_internal_sr_flag OUT NOCOPY VARCHAR2)
496 IS
497
498 -- Cursor to get item attributes
499 Cursor c_get_item_attributes (p_inventory_item_id in Number) IS
500 select serial_number_control_code,
501 comms_nl_trackable_flag
502 from mtl_system_items_kfv
503 where inventory_item_id = p_inventory_item_id
504 and organization_id = cs_std.get_item_valdn_orgzn_id;
505
506 -- Cursor to derive the Instance and IB Owner
507 Cursor c_get_ib_info ( p_inventory_item_id in Number,p_serial_number in Varchar2) is
508 Select
509 owner_party_id,
510 instance_id
511 from csi_item_instances
512 where serial_number = p_serial_number
513 and inventory_item_id = p_inventory_item_id;
514
515 -- Cursor to verify the Serial number against the Item
516 Cursor c_validate_sn_item (p_inventory_item_id in Number,p_serial_number in Varchar2) is
517 Select
518 inventory_item_id
519 from mtl_serial_numbers
520 where serial_number = p_serial_number
521 and inventory_item_id = p_inventory_item_id;
522
523 -- Local variables
524 l_serial_num_control_code Number;
525 l_install_base_flag Varchar2(1);
526 l_owner_party_id Number;
527 l_instance_id Number;
528 l_inventory_item_id Number;
529 c_serialized_predefined CONSTANT Number := 2;
530 c_non_serialized CONSTANT Number := 1;
531 c_ib CONSTANT Varchar2(1) := 'Y';
532 c_non_ib CONSTANT Varchar2(1) := 'N';
533
534 l_crt_inst_party_relation VARCHAR2(30) := fnd_profile.value('CSD_DEF_INST_PTY_RELTN');
535 l_relationship VARCHAR2(30);
536 l_check_owner BOOLEAN := FALSE;
537 x_msg_count NUMBER;
538 x_msg_data VARCHAR2(2000);
539 x_return_status VARCHAR2(1);
540 x_relationship_created VARCHAR2(1);
541
542 BEGIN
543
544 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
545 fnd_log.STRING (fnd_log.level_procedure,
546 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.VALIDATE_BULK_RECEIVE_REC.BEGIN',
547 'Enter - Validate bulk receive rec');
548 End if;
549 --
550 -- Initialize the flags
551 --
552 x_warning_flag := 'N';
553 x_warning_reason_code := Null;
554 x_internal_sr_flag := 'N';
555 x_change_owner_flag := 'N';
556
557 --
558 -- Derive the Item attributes
559 --
560 If ( p_inventory_item_id is not null ) then
561
562 l_serial_num_control_code := null;
563 l_install_base_flag := null;
564
565 Open c_get_item_attributes (p_inventory_item_id);
566 Fetch c_get_item_attributes into
567 l_serial_num_control_code,
568 l_install_base_flag;
569
570 If c_get_item_attributes%ISOPEN THEN
571 Close c_get_item_attributes;
572 End If;
573 End if;
574
575 -- If Inventory_item_id is NULL
576 If ( p_inventory_item_id is null) then
577
578 x_warning_flag := 'Y';
579 x_warning_reason_code := 'ITEM_NOT_ENTERED';
580 x_internal_sr_flag := 'Y';
581 x_change_owner_flag := 'N';
582
583 End if;
584
585
586 -- If Inventory_item_id is NOT NULL and Serial_Number is NULL
587 If ( p_inventory_item_id is not null and p_serial_number is null ) then
588
589 If ( l_serial_num_control_code <> c_non_serialized and p_quantity > 1) then
590 x_warning_flag := 'Y';
591 x_warning_reason_code := 'CREATE_DRAFT_RO';
592 x_internal_sr_flag := 'N';
593 x_change_owner_flag := 'N';
594 End if;
595
596 End if;
597
598
599 -- If Serial_Number is NOT NULL and Inventory_item_id is NOT NULL
600 If ( p_inventory_item_id is not null and p_serial_number is not null ) then
601
602 -- For NON Serialized Item
603 If ( l_serial_num_control_code = c_non_serialized ) then
604 x_warning_flag := 'Y';
605 x_warning_reason_code := 'NON_SN_ITEM';
606 x_internal_sr_flag := 'Y';
607 x_change_owner_flag := 'N';
608 End if;
609
610 -- For Serialized IB Item
611 -- Verify if Instance exists,if not then Create new Instance Else
612 -- Verify if Change IB Owner is required
613 If ( l_serial_num_control_code <> c_non_serialized and l_install_base_flag = c_ib ) then
614
615 l_owner_party_id := null;
616 l_instance_id := null;
617
618 Open c_get_ib_info(p_inventory_item_id,p_serial_number);
619 Fetch c_get_ib_info into l_owner_party_id,l_instance_id;
620
621 If ( c_get_ib_info%NOTFOUND) then
622
623 x_warning_flag := 'Y';
624 x_warning_reason_code := 'CREATE_IB_INSTANCE';
625 x_internal_sr_flag := 'N';
626 x_change_owner_flag := 'N';
627
628 Else
629 BEGIN
630 SELECT relationship_type_code
631 INTO l_relationship
632 FROM csi_i_parties
633 WHERE instance_id = l_instance_id
634 AND party_id = p_party_id
635 AND SYSDATE BETWEEN NVL(active_start_date, SYSDATE - 1) AND NVL(active_end_date,SYSDATE+1)
636 AND relationship_type_code = NVL(l_crt_inst_party_relation,relationship_type_code)
637 AND ROWNUM < 2;
638
639 EXCEPTION
640 WHEN NO_DATA_FOUND THEN
641 l_relationship := NULL;
642 l_check_owner := TRUE;
643 END;
644
645 IF (l_crt_inst_party_relation IS NOT NULL AND l_relationship IS NULL)
646 THEN
647 csd_repairs_util.create_inst_party_relation
648 (p_api_version_number => 1.0,
649 p_commit => 'F',
650 p_init_msg_list => 'F',
651 x_return_status => x_return_status,
652 x_msg_count => x_msg_count,
653 x_msg_data => x_msg_data,
654 x_relationship_created => x_relationship_created,
655 p_sr_party_id => p_party_id,
656 p_sr_account_id => NULL,
657 p_instance_id => l_instance_id,
658 p_relationship_type => l_crt_inst_party_relation
659 );
660 IF x_return_status <> 'S'
661 THEN
662 l_check_owner := TRUE;
663 END IF;
664 l_check_owner := FALSE;
665 END IF;
666
667 -- If the Owner party <> Entered Party and if the
668 -- Change IB Owner profile is set to Yes then
669 -- Change the IB Owner.
670 If ( l_owner_party_id <> p_party_id ) AND l_check_owner then
671 If ( fnd_profile.value('CSD_BLK_RCV_CHG_IB_OWNER') = 'Y') then
672 x_warning_flag := 'Y';
673 x_warning_reason_code := 'CHANGE_IB_OWNER';
674 x_internal_sr_flag := 'N';
675 x_change_owner_flag := 'Y';
676 End if;
677 End if;
678
679 End if;
680
681 Close c_get_ib_info;
682
683 End if;
684
685 If ( l_serial_num_control_code <> c_non_serialized and p_serial_number is not null) then
686
687 -- check the SN status ( @receipt,@pre-defined,@So issue )
688 l_inventory_item_id := null;
689
690 Open c_validate_sn_item(p_inventory_item_id,p_serial_number);
691 Fetch c_validate_sn_item into l_inventory_item_id;
692
693 If ( c_validate_sn_item%NOTFOUND )then
694
695 If ( l_serial_num_control_code = c_serialized_predefined )then
696
697 x_warning_flag := 'Y';
698 x_warning_reason_code := 'CANNOT_CREATE_PRE_DEFINED_SN';
699 x_internal_sr_flag := 'Y';
700 x_change_owner_flag := 'N';
701
702 Else
703
704 If ( nvl(l_install_base_flag,c_non_ib) = c_non_ib ) then
705 x_warning_flag := 'Y';
706 x_warning_reason_code := 'CREATE_SN';
707 x_internal_sr_flag := 'N';
708 x_change_owner_flag := 'N';
709 End if;
710
711 End if;
712
713 End if;
714
715 Close c_validate_sn_item;
716
717 End if;
718
719 End if;
720
721 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
722 fnd_log.STRING (fnd_log.level_statement,
723 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.VALIDATE_BULK_RECEIVE_REC',
724 'Warning Flag - '||x_warning_flag||
725 ',Warning Reason Code - '||x_warning_reason_code||
726 ',Internal SR Flag - '||x_internal_sr_flag||
727 ',Change Owner Flag - '||x_change_owner_flag);
728 End if;
729
730 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
731 fnd_log.STRING (fnd_log.level_procedure,
732 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.VALIDATE_BULK_RECEIVE_REC.END',
733 'Exit - Validate bulk receive rec');
734 End if;
735
736 END validate_bulk_receive_rec;
737
738
739 /*-----------------------------------------------------------------*/
740 /* procedure name: create_blkrcv_sr */
741 /* description : Procedure to create Service Request */
742 /* */
743 /*-----------------------------------------------------------------*/
744
745 PROCEDURE create_blkrcv_sr
746 (
747 p_bulk_receive_rec IN csd_bulk_receive_util.bulk_receive_rec,
748 p_sr_notes_tbl IN cs_servicerequest_pub.notes_table,
749 x_incident_id OUT NOCOPY NUMBER,
750 x_incident_number OUT NOCOPY VARCHAR2,
751 x_return_status OUT NOCOPY VARCHAR2,
752 x_msg_count OUT NOCOPY NUMBER,
753 x_msg_data OUT NOCOPY VARCHAR2
754
755 )
756 IS
757
758 -- Cursor to derive party type
759 Cursor c_sr_party(p_party_id number) is
760 select party_type from hz_parties
761 where party_id = p_party_id;
762
763 -- Cursor to derive primary bill to site id
764 Cursor c_bill_to_site(p_party_id number) is
765 Select hpu.party_site_use_id
766 from hz_party_sites hps,
767 hz_party_site_uses hpu
768 where
769 hps.party_id = p_party_id
770 and hps.party_site_id = hpu.party_site_id
771 and hpu.site_use_type = 'BILL_TO'
772 and hpu.primary_per_type = 'Y';
773
774 -- Cursor to derive primary ship to site id
775 Cursor c_ship_to_site(p_party_id number) is
776 Select hpu.party_site_use_id
777 from hz_party_sites hps,
778 hz_party_site_uses hpu
779 where
780 hps.party_id = p_party_id
781 and hps.party_site_id = hpu.party_site_id
782 and hpu.site_use_type = 'SHIP_TO'
783 and hpu.primary_per_type = 'Y';
784
785 -- Local variables
786 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_BLKRCV_SR';
787 l_api_version CONSTANT NUMBER := 1.0;
788 l_party_type Varchar2(30);
789 l_bill_to_site_use_id Number;
790 l_ship_to_site_use_id Number;
791 l_service_request_rec CSD_PROCESS_PVT.SERVICE_REQUEST_REC := CSD_PROCESS_UTIL.SR_REC;
792
793 BEGIN
794
795 savepoint create_blkrcv_sr;
796
797 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
798 fnd_log.STRING (fnd_log.level_procedure,
799 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_SR.BEGIN',
800 'Enter - Create Blkrcv SR');
801 End if;
802
803 -- Derive the party type
804 l_party_type := null;
805
806 Open c_sr_party (p_bulk_receive_rec.party_id);
807 Fetch c_sr_party into l_party_type;
808 Close c_sr_party;
809
810 -- Derive the Primary Bill To Site Use Id
811 l_bill_to_site_use_id := null;
812
813 Open c_bill_to_site (p_bulk_receive_rec.party_id);
814 Fetch c_bill_to_site into l_bill_to_site_use_id;
815 Close c_bill_to_site;
816
817 -- Derive the Primary Ship To Site Use Id
818 l_ship_to_site_use_id := null;
819
820 Open c_ship_to_site (p_bulk_receive_rec.party_id);
821 Fetch c_ship_to_site into l_ship_to_site_use_id;
822 Close c_ship_to_site;
823
824 -- Assign / Initialize the Service request Rec
825 l_service_request_rec.request_date := sysdate;
826 l_service_request_rec.type_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_TYPE');
827 l_service_request_rec.status_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_STATUS');
828 l_service_request_rec.severity_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_SEVERITY');
829 l_service_request_rec.urgency_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_URGENCY');
830 l_service_request_rec.closed_date := null;
831 l_service_request_rec.owner_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_OWNER');
832 l_service_request_rec.owner_group_id := NULL;
833 l_service_request_rec.publish_flag := '';
834 l_service_request_rec.summary := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_SUMMARY');
835 l_service_request_rec.caller_type := l_party_type;
836 l_service_request_rec.customer_id := p_bulk_receive_rec.party_id;
837 l_service_request_rec.customer_number := null;
838 l_service_request_rec.customer_product_id := null;
839 l_service_request_rec.cp_ref_number := null;
840 l_service_request_rec.inv_item_revision := null;
841 l_service_request_rec.inventory_item_id := null;
842 l_service_request_rec.inventory_org_id := null;
843 l_service_request_rec.current_serial_number := null;
844 l_service_request_rec.original_order_number := null;
845 l_service_request_rec.purchase_order_num := null;
846 l_service_request_rec.problem_code := null;
847 l_service_request_rec.exp_resolution_date := null;
848 l_service_request_rec.bill_to_site_use_id := l_bill_to_site_use_id;
849 l_service_request_rec.ship_to_site_use_id := l_ship_to_site_use_id;
850 l_service_request_rec.contract_id := null;
851 l_service_request_rec.account_id := p_bulk_receive_rec.cust_account_id;
852 l_service_request_rec.cust_po_number := null;
853 l_service_request_rec.cp_revision_id := null;
854 l_service_request_rec.sr_contact_point_id := null;
855 l_service_request_rec.party_id := null;
856 l_service_request_rec.contact_point_id := null;
857 l_service_request_rec.contact_point_type := null;
858 l_service_request_rec.primary_flag := null;
859 l_service_request_rec.contact_type := null;
860 l_service_request_rec.sr_creation_channel := 'PHONE';
861 l_service_request_rec.resource_type := FND_PROFILE.value('CS_SR_DEFAULT_OWNER_TYPE');
862
863
864 -- Call the Service Request API
865 CSD_PROCESS_PVT.process_service_request
866 ( p_api_version => 1.0,
867 p_commit => fnd_api.g_false,
868 p_init_msg_list => fnd_api.g_true,
869 p_validation_level => fnd_api.g_valid_level_full,
870 p_action => 'CREATE',
871 p_incident_id => NULL,
872 p_service_request_rec => l_service_request_rec,
873 p_notes_tbl => p_sr_notes_tbl,
874 x_incident_id => x_incident_id,
875 x_incident_number => x_incident_number,
876 x_return_status => x_return_status,
877 x_msg_count => x_msg_count,
878 x_msg_data => x_msg_data
879 );
880
881 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
882 RAISE FND_API.G_EXC_ERROR;
883 End If;
884
885 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
886 fnd_log.STRING (fnd_log.level_procedure,
887 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_SR.END',
888 'Exit - Create Blkrcv SR');
889 End if;
890
891 EXCEPTION
892 When FND_API.G_EXC_ERROR then
893 Rollback To create_blkrcv_sr;
894 x_return_status := FND_API.G_RET_STS_ERROR ;
895 FND_MSG_PUB.Count_And_Get
896 (p_count => x_msg_count,
897 p_data => x_msg_data );
898
899 When FND_API.G_EXC_UNEXPECTED_ERROR then
900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
901 ROLLBACK TO create_blkrcv_sr;
902 FND_MSG_PUB.Count_And_Get
903 ( p_count => x_msg_count,
904 p_data => x_msg_data );
905
906 When OTHERS then
907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
908 Rollback To create_blkrcv_sr;
909 If FND_MSG_PUB.Check_Msg_Level
910 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
911 FND_MSG_PUB.Add_Exc_Msg
912 (G_PKG_NAME,
913 l_api_name );
914 End If;
915 FND_MSG_PUB.Count_And_Get
916 (p_count => x_msg_count,
917 p_data => x_msg_data );
918
919 END create_blkrcv_sr;
920
921
922 /*-----------------------------------------------------------------*/
923 /* procedure name: create_blkrcv_ro */
924 /* description : Procedure to create a Repair Order */
925 /* */
926 /*-----------------------------------------------------------------*/
927
928 PROCEDURE create_blkrcv_ro
929 (
930 p_bulk_receive_id IN NUMBER,
931 x_repair_line_id OUT NOCOPY NUMBER,
932 x_repair_number OUT NOCOPY VARCHAR2,
933 x_ro_status OUT NOCOPY VARCHAR2,
934 x_return_status OUT NOCOPY VARCHAR2,
935 x_msg_count OUT NOCOPY NUMBER,
936 x_msg_data OUT NOCOPY VARCHAR2
937
938 )
939 IS
940
941 -- Cursor to derive Bulk Receive record
942 Cursor c_create_blkrcv_ro(p_bulk_receive_id Number) IS
943 Select * from csd_bulk_receive_items_b
944 where bulk_receive_id = p_bulk_receive_id;
945
946 -- Cursor to derive item attributes
947 Cursor c_get_item_attributes(p_inventory_item_id number) IS
948 Select serial_number_control_code,
949 comms_nl_trackable_flag,
950 revision_qty_control_code
951 from mtl_system_items_kfv
952 where inventory_item_id = p_inventory_item_id
953 and organization_id = cs_std.get_item_valdn_orgzn_id;
954
955 -- Fix for bug#6082836
956 -- Added business_process_id
957 -- Cursor to derive repair type attribute
958 Cursor c_get_repair_type_attr(p_repair_type_id number) is
959 Select price_list_header_id,
960 repair_mode,
961 business_process_id
962 from csd_repair_types_b
963 where repair_type_id = p_repair_type_id;
964
965 -- Cursor to get IB details
966 Cursor c_get_ib_info ( p_inventory_item_id in Number,p_serial_number in Varchar2) is
967 Select
968 owner_party_id,
969 instance_id
970 from csi_item_instances
971 where serial_number = p_serial_number
972 and inventory_item_id = p_inventory_item_id;
973
974 -- Cursor to get Item Revision
975 Cursor c_get_item_revision ( p_inventory_item_id in Number,p_serial_number in Varchar2) is
976 Select
977 revision
978 from mtl_serial_numbers
979 where serial_number = p_serial_number
980 and inventory_item_id = p_inventory_item_id;
981
982 -- Cursor to get Party site use id
983 Cursor c_get_party_site_use_id (p_incident_id number )is
984 Select ship_to_site_use_id
985 from cs_incidents_all_b
986 where incident_id = p_incident_id;
987
988 -- Cursor to derive Primary UOM code
989 Cursor c_get_item_uom_code (p_inventory_item_id number) is
990 Select primary_uom_code
991 from mtl_system_items_kfv
992 where inventory_item_id = p_inventory_item_id
993 and organization_id = cs_std.get_item_valdn_orgzn_id;
994
995 -- Fix for bug#6082836
996 -- Cursor to get sr details
997 Cursor c_get_sr_details (p_incident_id in number) is
998 Select customer_id,account_id,incident_date,
999 incident_severity_id,contract_id,contract_service_id
1000 from csd_incidents_v
1001 where incident_id = p_incident_id;
1002
1003 Cursor c_get_install_site_use_id(p_instance_id in number) is
1004 Select location_id
1005 from csi_item_instances
1006 where instance_id = p_instance_id;
1007
1008 l_ent_contracts OKS_ENTITLEMENTS_PUB.GET_CONTOP_TBL;
1009 l_calc_resptime_flag Varchar2(1) := 'Y';
1010 l_server_tz_id Number;
1011 l_customer_id Number;
1012 l_account_id Number;
1013 l_incident_date Date;
1014 l_severity_id Number;
1015 l_sr_contract_id Number;
1016 l_sr_contract_service_id Number;
1017 l_contract_pl_id Number;
1018 l_profile_pl_id Number;
1019 l_install_site_use_id Number;
1020 l_currency_code Varchar2(30);
1021 l_business_process_id Number;
1022
1023 -- Local variables
1024 l_api_name CONSTANT Varchar2(30) := 'CREATE_BLKRCV_RO';
1025 l_api_version CONSTANT Number := 1.0;
1026 l_repair_type_pl Number;
1027 l_serial_number_control_code Number;
1028 l_owner_party_id Number;
1029 l_instance_id Number;
1030 l_revision_qty_control_code Number;
1031 l_install_base_flag Varchar2(1);
1032 l_repln_rec csd_repairs_pub.repln_rec_type;
1033 l_blkrcv_rec csd_bulk_receive_items_b%ROWTYPE;
1034 c_non_serialized CONSTANT Number := 1;
1035 l_instance_rec csd_mass_rcv_pvt.instance_rec_type;
1036 l_revision Varchar2(30);
1037 l_party_site_use_id Number;
1038 l_repair_mode Varchar2(30);
1039 l_repair_type_id Number;
1040 l_uom_code Varchar2(3);
1041 c_ib CONSTANT Varchar2(1) := 'Y';
1042
1043 -- BR ER FP changes, subhat
1044 l_lot_num varchar2(30);
1045
1046 BEGIN
1047
1048 savepoint create_blkrcv_ro;
1049
1050 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1051 fnd_log.STRING (fnd_log.level_procedure,
1052 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO.BEGIN',
1053 'Enter - Create Blkrcv RO');
1054 End if;
1055
1056 Open c_create_blkrcv_ro (p_bulk_receive_id);
1057 Fetch c_create_blkrcv_ro into l_blkrcv_rec;
1058 Close c_create_blkrcv_ro;
1059
1060 l_serial_number_control_code := null;
1061 l_install_base_flag := null;
1062 l_revision_qty_control_code := null;
1063
1064 Open c_get_item_attributes (l_blkrcv_rec.inventory_item_id);
1065 Fetch c_get_item_attributes into l_serial_number_control_code,
1066 l_install_base_flag,
1067 l_revision_qty_control_code;
1068 Close c_get_item_attributes;
1069
1070
1071 -- If the item is Revision control
1072 -- derive the Revision from the entered serial number
1073 If ( l_revision_qty_control_code <> 1) then
1074
1075 l_revision := null;
1076
1077 -- BR ER FP changes, subhat.
1078 begin
1079 select item_revision,lot_number
1080 into l_revision,l_lot_num
1081 from csd_bulk_receive_items_b
1082 where bulk_receive_id = p_bulk_receive_id;
1083
1084 exception
1085 when no_data_found then
1086 l_revision := null;
1087 end;
1088 if l_revision is null then
1089 Open c_get_item_revision(l_blkrcv_rec.inventory_item_id,
1090 l_blkrcv_rec.serial_number);
1091 Fetch c_get_item_revision into l_revision;
1092 Close c_get_item_revision;
1093
1094 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1095 fnd_log.STRING (fnd_log.level_statement,
1096 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO',
1097 'Derived Revision - '||l_revision||
1098 'for Item id - '||l_blkrcv_rec.inventory_item_id);
1099 End if;
1100 end if;
1101 End if;
1102
1103
1104 -- Derive the IB Instance ID
1105 -- for a IB item
1106 If (l_blkrcv_rec.instance_id is null and l_install_base_flag = c_ib
1107 and l_blkrcv_rec.serial_number is not null) then
1108
1109 l_owner_party_id := null;
1110 l_instance_id := null;
1111
1112 Open c_get_ib_info(l_blkrcv_rec.inventory_item_id,
1113 l_blkrcv_rec.serial_number);
1114 Fetch c_get_ib_info into l_owner_party_id,
1115 l_instance_id;
1116 Close c_get_ib_info;
1117
1118 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1119 fnd_log.STRING (fnd_log.level_statement,
1120 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO',
1121 'Derived Instance Id - '||l_instance_id||
1122 'for Item id - '||l_blkrcv_rec.inventory_item_id||
1123 ',Serial number - '||l_blkrcv_rec.serial_number);
1124 End if;
1125
1126 Else
1127 l_instance_id := l_blkrcv_rec.instance_id;
1128 End if;
1129
1130
1131 -- Derive the Primary UOM code if UOM is null
1132 If ( l_blkrcv_rec.uom_code is null ) then
1133
1134 l_uom_code := null;
1135
1136 Open c_get_item_uom_code (l_blkrcv_rec.inventory_item_id);
1137 Fetch c_get_item_uom_code into l_uom_code;
1138 Close c_get_item_uom_code;
1139
1140 If (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1141 fnd_log.STRING (fnd_log.level_statement,
1142 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO',
1143 'Derived Uom code - '||l_uom_code||
1144 'for Item id - '||l_blkrcv_rec.inventory_item_id);
1145 End if;
1146
1147 Else
1148 l_uom_code := l_blkrcv_rec.uom_code;
1149 End if;
1150
1151 -- If instance id is null then call create IB
1152 -- for a IB item
1153 If ( l_instance_id is null and l_install_base_flag = c_ib) then
1154
1155 l_party_site_use_id := null;
1156
1157 Open c_get_party_site_use_id(l_blkrcv_rec.incident_id);
1158 Fetch c_get_party_site_use_id into l_party_site_use_id;
1159 Close c_get_party_site_use_id;
1160
1161 l_instance_rec.inventory_item_id := l_blkrcv_rec.inventory_item_id;
1162 l_instance_rec.instance_id := null;
1163 l_instance_rec.instance_number := null;
1164 l_instance_rec.serial_number := l_blkrcv_rec.serial_number;
1165 l_instance_rec.lot_number := null;
1166 l_instance_rec.quantity := 1;
1167 l_instance_rec.uom := l_uom_code;
1168 l_instance_rec.party_site_use_id := l_party_site_use_id;
1169 l_instance_rec.party_id := l_blkrcv_rec.party_id;
1170 l_instance_rec.account_id := l_blkrcv_rec.cust_account_id;
1171 l_instance_rec.mfg_serial_number_flag := 'N';
1172 -- Bulk Rcv enhancement changes, subhat.
1173 l_instance_rec.external_reference := l_blkrcv_rec.external_reference;
1174 l_instance_rec.item_revision := l_revision;
1175 l_instance_rec.lot_number := l_lot_num;
1176 -- end Bulk Rcv enhancement changes, subhat.
1177
1178 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
1179 fnd_log.STRING (fnd_log.level_event,
1180 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO',
1181 'Calling create item instance api');
1182 End if;
1183
1184
1185 csd_mass_rcv_pvt.create_item_instance (
1186 p_api_version => 1.0,
1187 p_init_msg_list => fnd_api.g_false,
1188 p_commit => fnd_api.g_false,
1189 p_validation_level => fnd_api.g_valid_level_full,
1190 x_return_status => x_return_status,
1191 x_msg_count => x_msg_count,
1192 x_msg_data => x_msg_data,
1193 px_instance_rec => l_instance_rec,
1194 x_instance_id => l_instance_id );
1195
1196 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1197 RAISE FND_API.G_EXC_ERROR;
1198 End if;
1199
1200 End if;
1201
1202 -- swai: bug 7657379 use defaulting rules to get the repair type
1203 --l_repair_type_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_REPAIR_TYPE');
1204 l_repair_type_id := get_bulk_rcv_def_repair_type (
1205 p_incident_id => l_blkrcv_rec.incident_id,
1206 p_ro_inventory_item_id => l_blkrcv_rec.inventory_item_id);
1207
1208 l_repair_type_pl := null;
1209 l_repair_mode := null;
1210
1211 Open c_get_repair_type_attr(l_repair_type_id);
1212 Fetch c_get_repair_type_attr into l_repair_type_pl,l_repair_mode,l_business_process_id;
1213 Close c_get_repair_type_attr;
1214
1215 -- Fix for bug#6082836
1216 -- Derive the Currency code
1217 --If ( l_repair_type_pl is null ) then
1218 -- l_repln_rec.currency_code := CSD_CHARGE_LINE_UTIL.GET_PLCURRCODE(fnd_profile.value('CSD_DEFAULT_PRICE_LIST'));
1219 --Else
1220 -- l_repln_rec.currency_code := CSD_CHARGE_LINE_UTIL.GET_PLCURRCODE(l_repair_type_pl);
1221 --End if;
1222
1223 -- Fix for bug#6082836
1224 -- Default Contract, Price list and Currency
1225
1226 Open c_get_sr_details(l_blkrcv_rec.incident_id);
1227 Fetch c_get_sr_details into l_customer_id,l_account_id,l_incident_date,
1228 l_severity_id,l_sr_contract_id,l_sr_contract_service_id;
1229 Close c_get_sr_details;
1230
1231 Open c_get_install_site_use_id(l_instance_id);
1232 Fetch c_get_install_site_use_id into l_install_site_use_id;
1233 Close c_get_install_site_use_id;
1234
1235 fnd_profile.get('SERVER_TIMEZONE_ID', l_server_tz_id);
1236
1237 CSD_REPAIRS_UTIL.GET_ENTITLEMENTS(
1238 p_api_version_number => 1.0,
1239 p_init_msg_list => fnd_api.g_false,
1240 p_commit => fnd_api.g_false,
1241 p_contract_number => null,
1242 p_service_line_id => null,
1243 p_customer_id => l_customer_id,
1244 p_site_id => l_install_site_use_id,
1245 p_customer_account_id => l_account_id,
1246 p_system_id => null,
1247 p_inventory_item_id => l_blkrcv_rec.inventory_item_id,
1248 p_customer_product_id => l_instance_id,
1249 p_request_date => trunc(l_incident_date),
1250 p_validate_flag => 'Y',
1251 p_business_process_id => l_business_process_id,
1252 p_severity_id => l_severity_id,
1253 p_time_zone_id => l_server_tz_id,
1254 P_CALC_RESPTIME_FLAG => l_calc_resptime_flag,
1255 x_ent_contracts => l_ent_contracts,
1256 x_return_status => x_return_status,
1257 x_msg_count => x_msg_count,
1258 x_msg_data => x_msg_data);
1259
1260 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1261 RAISE FND_API.G_EXC_ERROR;
1262 End if;
1263
1264 If (l_ent_contracts.count = 0 ) then
1265
1266 l_repln_rec.contract_line_id := null;
1267
1268 Else
1269
1270 For l_index in l_ent_contracts.FIRST..l_Ent_contracts.LAST Loop
1271 if (l_sr_contract_id = l_ent_contracts(l_index).contract_id and
1272 l_sr_contract_service_id = l_ent_contracts(l_index).service_line_id) then
1273
1274 l_repln_rec.contract_line_id := l_ent_contracts(l_index).service_line_id;
1275 exit;
1276
1277 end if;
1278 End Loop;
1279
1280 If (l_repln_rec.contract_line_id is null or
1281 l_repln_rec.contract_line_id = fnd_api.g_miss_num) then
1282 l_repln_rec.contract_line_id := l_ent_contracts(1).service_line_id;
1283 End if;
1284
1285 End if;
1286
1287 --
1288 -- Default PL and Currency
1289 --
1290 csd_process_util.get_ro_default_curr_pl
1291 ( p_api_version => 1.0,
1292 p_init_msg_list => fnd_api.g_false,
1293 p_incident_id => l_blkrcv_rec.incident_id,
1294 p_repair_type_id => l_repair_type_id,
1295 p_ro_contract_line_id => l_repln_rec.contract_line_id,
1296 x_contract_pl_id => l_contract_pl_id,
1297 x_profile_pl_id => l_profile_pl_id,
1298 x_currency_code => l_currency_code,
1299 x_return_status => x_return_status,
1300 x_msg_count => x_msg_count,
1301 x_msg_data => x_msg_data );
1302
1303 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1304 RAISE FND_API.G_EXC_ERROR;
1305 End if;
1306
1307 If ( l_contract_pl_id is not null) then
1308 l_repln_rec.price_list_header_id := l_contract_pl_id;
1309 Elsif ( l_profile_pl_id is not null ) then
1310 l_repln_rec.price_list_header_id := l_profile_pl_id;
1311 End if;
1312
1313 l_repln_rec.currency_code := l_currency_code;
1314
1315 -- Set the Repair Order Status
1316 If ((l_blkrcv_rec.serial_number is null) and (l_serial_number_control_code <> c_non_serialized)
1317 and (l_blkrcv_rec.quantity > 1)) then
1318 l_repln_rec.status := 'D';
1319 x_ro_status := 'DRAFT';
1320 Else
1321 l_repln_rec.status := 'O';
1322 x_ro_status := 'OPEN';
1323 End if;
1324
1325 --
1326 -- Inventory org id
1327 --
1328 -- swai: bug 7657379 - use defaulting rules to get the inventory org
1329 -- l_repln_rec.inventory_org_id := fnd_profile.value('CSD_DEF_REP_INV_ORG');
1330 l_repln_rec.inventory_org_id := fnd_api.g_miss_num;
1331
1332 --
1333 -- Initialize / Assign the values to Repair Rec type
1334 --
1335
1336 -- bug#13006845 --13734356
1337 -- Default Repair Org
1338 --
1339 l_repln_rec.resource_group := fnd_profile.value('CSD_DEFAULT_REPAIR_ORG');
1340 -- bug#13006845 --13734356
1341
1342 l_repln_rec.incident_id := l_blkrcv_rec.incident_id;
1343 l_repln_rec.inventory_item_id := l_blkrcv_rec.inventory_item_id;
1344 l_repln_rec.customer_product_id := l_instance_id;
1345 l_repln_rec.unit_of_measure := l_uom_code;
1346 l_repln_rec.serial_number := l_blkrcv_rec.serial_number;
1347 l_repln_rec.quantity := l_blkrcv_rec.quantity;
1348 l_repln_rec.auto_process_rma := 'Y';
1349 l_repln_rec.approval_required_flag := NVL(fnd_profile.value('CSD_CUST_APPROVAL_REQD'),'N');
1350 l_repln_rec.repair_type_id := l_repair_type_id; -- swai: bug 7657379
1351 -- l_repln_rec.repair_type_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_REPAIR_TYPE');
1352 l_repln_rec.repair_group_id := null;
1353 l_repln_rec.item_revision := l_revision;
1354 l_repln_rec.repair_mode := l_repair_mode;
1355
1356 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
1357 fnd_log.STRING (fnd_log.level_event,
1358 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO',
1359 'Calling create repair order api');
1360 End if;
1361
1362
1363 -- Call the Repairs private API
1364 CSD_REPAIRS_PVT.Create_Repair_Order
1365 (p_api_version_number => 1.0,
1366 p_commit => fnd_api.g_false,
1367 p_init_msg_list => fnd_api.g_true,
1368 p_validation_level => fnd_api.g_valid_level_full,
1369 p_repair_line_id => x_repair_line_id,
1370 p_Repln_Rec => l_repln_rec,
1371 x_repair_line_id => x_repair_line_id,
1372 x_repair_number => x_repair_number,
1373 x_return_status => x_return_status,
1374 x_msg_count => x_msg_count,
1375 x_msg_data => x_msg_data
1376 );
1377
1378 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1379 RAISE FND_API.G_EXC_ERROR;
1380 End If;
1381
1382 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1383 fnd_log.STRING (fnd_log.level_procedure,
1384 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_RO.END',
1385 'Exit - Create Blkrcv RO');
1386 End if;
1387
1388 EXCEPTION
1389 When FND_API.G_EXC_ERROR then
1390 Rollback To create_blkrcv_ro;
1391 x_return_status := FND_API.G_RET_STS_ERROR ;
1392 FND_MSG_PUB.Count_And_Get
1393 (p_count => x_msg_count,
1394 p_data => x_msg_data );
1395
1396 When FND_API.G_EXC_UNEXPECTED_ERROR then
1397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1398 ROLLBACK TO create_blkrcv_ro;
1399 FND_MSG_PUB.Count_And_Get
1400 ( p_count => x_msg_count,
1401 p_data => x_msg_data );
1402
1403 When OTHERS then
1404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1405 Rollback To create_blkrcv_ro;
1406 If FND_MSG_PUB.Check_Msg_Level
1407 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
1408 FND_MSG_PUB.Add_Exc_Msg
1409 (G_PKG_NAME,
1410 l_api_name );
1411 End If;
1412 FND_MSG_PUB.Count_And_Get
1413 (p_count => x_msg_count,
1414 p_data => x_msg_data );
1415
1416 END create_blkrcv_ro;
1417
1418
1419 /*-----------------------------------------------------------------*/
1420 /* procedure name: create_blkrcv_default_prod_txn */
1421 /* description : Procedure to create Default product txn */
1422 /* for a Repair Order */
1423 /* */
1424 /*-----------------------------------------------------------------*/
1425
1426 PROCEDURE create_blkrcv_default_prod_txn
1427 (
1428 p_bulk_receive_id IN NUMBER,
1429 x_return_status OUT NOCOPY VARCHAR2,
1430 x_msg_count OUT NOCOPY NUMBER,
1431 x_msg_data OUT NOCOPY VARCHAR2
1432 )
1433 IS
1434
1435 -- Cursor to derive Bulk Receive record
1436 Cursor c_create_blkrcv_prod_txn(p_bulk_receive_id Number) IS
1437 select * from csd_bulk_receive_items_b
1438 where bulk_receive_id = p_bulk_receive_id;
1439
1440 -- Local variables
1441 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_BLKRCV_DEFAULT_PROD_TXN';
1442 l_api_version CONSTANT NUMBER := 1.0;
1443 l_blkrcv_rec csd_bulk_receive_items_b%ROWTYPE;
1444
1445 BEGIN
1446
1447 savepoint create_blkrcv_default_prod_txn;
1448
1449 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1450 fnd_log.STRING (fnd_log.level_procedure,
1451 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_DEFAULT_PROD_TXN.BEGIN',
1452 'Enter - Create Blkrcv Default Prod Txn');
1453 End if;
1454
1455 Open c_create_blkrcv_prod_txn(p_bulk_receive_id );
1456 Fetch c_create_blkrcv_prod_txn into l_blkrcv_rec;
1457 Close c_create_blkrcv_prod_txn;
1458
1459 -- Call the Create default prod txn api
1460 csd_process_pvt.create_default_prod_txn
1461 (p_api_version => 1.0,
1462 p_commit => fnd_api.g_false,
1463 p_init_msg_list => fnd_api.g_true,
1464 p_validation_level => fnd_api.g_valid_level_full,
1465 p_repair_line_id => l_blkrcv_rec.repair_line_id,
1466 x_return_status => x_return_status,
1467 x_msg_count => x_msg_count,
1468 x_msg_data => x_msg_data);
1469
1470 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1471 RAISE FND_API.G_EXC_ERROR;
1472 End If;
1473
1474 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1475 fnd_log.STRING (fnd_log.level_procedure,
1476 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CREATE_BULKRCV_DEFAULT_PROD_TXN.END',
1477 'Exit - Create Blkrcv Default Prod Txn');
1478 End if;
1479
1480 EXCEPTION
1481 When FND_API.G_EXC_ERROR then
1482 Rollback To create_blkrcv_default_prod_txn;
1483 x_return_status := FND_API.G_RET_STS_ERROR ;
1484 FND_MSG_PUB.Count_And_Get
1485 (p_count => x_msg_count,
1486 p_data => x_msg_data );
1487
1488 When FND_API.G_EXC_UNEXPECTED_ERROR then
1489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1490 ROLLBACK TO create_blkrcv_default_prod_txn;
1491 FND_MSG_PUB.Count_And_Get
1492 ( p_count => x_msg_count,
1493 p_data => x_msg_data );
1494
1495 When OTHERS then
1496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1497 Rollback To create_blkrcv_default_prod_txn;
1498 If FND_MSG_PUB.Check_Msg_Level
1499 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
1500 FND_MSG_PUB.Add_Exc_Msg
1501 (G_PKG_NAME,
1502 l_api_name );
1503 End If;
1504 FND_MSG_PUB.Count_And_Get
1505 (p_count => x_msg_count,
1506 p_data => x_msg_data );
1507
1508 END create_blkrcv_default_prod_txn;
1509
1510
1511 /*-----------------------------------------------------------------*/
1512 /* procedure name: change_blkrcv_ib_owner */
1513 /* description : Procedure to Change the Install Base Owner */
1514 /* */
1515 /*-----------------------------------------------------------------*/
1516
1517 PROCEDURE change_blkrcv_ib_owner
1518 (
1519 p_bulk_receive_id IN NUMBER,
1520 x_return_status OUT NOCOPY VARCHAR2,
1521 x_msg_count OUT NOCOPY NUMBER,
1522 x_msg_data OUT NOCOPY VARCHAR2
1523 )
1524 IS
1525
1526 -- Local variables
1527 l_instance_rec csi_datastructures_pub.instance_rec;
1528 l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
1529 l_party_tbl csi_datastructures_pub.party_tbl;
1530 l_account_tbl csi_datastructures_pub.party_account_tbl;
1531 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
1532 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
1533 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
1534 l_txn_rec csi_datastructures_pub.transaction_rec;
1535 x_instance_id_lst csi_datastructures_pub.id_tbl;
1536 l_instance_party_id Number;
1537 l_object_version_number Number;
1538 l_api_name CONSTANT Varchar(30) := 'CHANGE_BLKRCV_IB_OWNER';
1539 l_api_version CONSTANT Number := 1.0;
1540 l_blkrcv_rec csd_bulk_receive_items_b%ROWTYPE;
1541 l_instance_account_id Number;
1542 l_inst_party_obj_ver_num Number;
1543 l_inst_acct_obj_ver_num Number;
1544 l_instance_id Number;
1545
1546 --bug#8508030
1547 l_bill_to_address Number;
1548 l_ship_to_address Number;
1549 --bug#8508030
1550
1551
1552 -- Cursor to select the Instance party id
1553 Cursor c_instance_party(p_instance_id number) IS
1554 Select instance_party_id,
1555 object_version_number
1556 from csi_i_parties
1557 where instance_id = p_instance_id
1558 and relationship_type_code = 'OWNER';
1559
1560 -- Cursor to derive the Instance details
1561 Cursor c_instance_details(p_instance_id number) IS
1562 Select object_version_number from csi_item_instances
1563 where instance_id = p_instance_id;
1564
1565 -- Cursor to derive the Bulk Receive rec
1566 Cursor c_bulk_receive_items(p_bulk_receive_id Number) IS
1567 select * from csd_bulk_receive_items_b
1568 where bulk_receive_id = p_bulk_receive_id;
1569
1570 -- Cursor to derive the Instance Account Id
1571 Cursor c_instance_account(p_instance_party_id number) is
1572 Select ip_account_id,
1573 object_version_number
1574 from csi_ip_accounts
1575 where instance_party_id = p_instance_party_id;
1576
1577 -- Cursor to get IB details
1578 Cursor c_get_ib_info ( p_inventory_item_id in Number,p_serial_number in Varchar2) is
1579 Select instance_id
1580 from csi_item_instances
1581 where serial_number = p_serial_number
1582 and inventory_item_id = p_inventory_item_id;
1583
1584 --bug#8508030
1585 Cursor get_bill_to_ship_to_address(p_instance_id number) IS
1586 SELECT bill_to_address,ship_to_address
1587 FROM CSI_IP_ACCOUNTS
1588 WHERE INSTANCE_PARTY_ID =
1589 (SELECT instance_party_id FROM CSI_I_PARTIES
1590 WHERE INSTANCE_ID=p_instance_id
1591 AND relationship_type_code='OWNER');
1592 --bug#8508030
1593
1594
1595 BEGIN
1596
1597 savepoint change_blkrcv_ib_owner;
1598
1599 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1600 fnd_log.STRING (fnd_log.level_procedure,
1601 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CHANGE_BULKRCV_IB_OWNER.BEGIN',
1602 'Enter - Change Blkrcv IB Owner');
1603 End if;
1604
1605 Open c_bulk_receive_items(p_bulk_receive_id);
1606 Fetch c_bulk_receive_items into l_blkrcv_rec;
1607 Close c_bulk_receive_items;
1608
1609 -- Derive the IB Instance ID
1610 -- for a IB item
1611 If (l_blkrcv_rec.instance_id is null
1612 and l_blkrcv_rec.serial_number is not null) then
1613
1614 l_instance_id := null;
1615
1616 Open c_get_ib_info(l_blkrcv_rec.inventory_item_id,
1617 l_blkrcv_rec.serial_number);
1618 Fetch c_get_ib_info into l_instance_id;
1619 Close c_get_ib_info;
1620 Else
1621 l_instance_id := l_blkrcv_rec.instance_id;
1622 End if;
1623
1624 l_instance_party_id := null;
1625 l_inst_party_obj_ver_num := null;
1626
1627 Open c_instance_party(l_instance_id);
1628 Fetch c_instance_party into l_instance_party_id,
1629 l_inst_party_obj_ver_num;
1630 Close c_instance_party;
1631
1632 l_instance_account_id := null;
1633 l_inst_acct_obj_ver_num := null;
1634
1635 Open c_instance_account(l_instance_party_id);
1636 Fetch c_instance_account into l_instance_account_id,
1637 l_inst_acct_obj_ver_num;
1638 Close c_instance_account;
1639
1640 l_object_version_number := null;
1641
1642 Open c_instance_details(l_instance_id);
1643 Fetch c_instance_details into l_object_version_number;
1644 Close c_instance_details;
1645
1646
1647 -- Assign / Initialize values to the IB Rec type
1648 l_instance_rec.instance_id := l_instance_id;
1649 l_instance_rec.object_version_number := l_object_version_number;
1650
1651 l_party_tbl(1).instance_party_id := l_instance_party_id;
1652 l_party_tbl(1).instance_id := l_instance_id;
1653 l_party_tbl(1).party_source_table := 'HZ_PARTIES';
1654 l_party_tbl(1).party_id := l_blkrcv_rec.orig_party_id;
1655 l_party_tbl(1).relationship_type_code := 'OWNER';
1656 l_party_tbl(1).contact_flag := 'N';
1657 l_party_tbl(1).object_version_number := l_inst_party_obj_ver_num;
1658
1659 l_account_tbl(1).ip_account_id := l_instance_account_id;
1660 l_account_tbl(1).parent_tbl_index := 1;
1661 l_account_tbl(1).instance_party_id := l_instance_party_id;
1662 l_account_tbl(1).party_account_id := l_blkrcv_rec.orig_cust_account_id;
1663 l_account_tbl(1).relationship_type_code := 'OWNER';
1664 l_account_tbl(1).object_version_number := l_inst_acct_obj_ver_num;
1665
1666 --bug#8508030
1667 -- Get existing bill_to and ship_to address of the IB instancee
1668 Open get_bill_to_ship_to_address(l_instance_id);
1669 Fetch get_bill_to_ship_to_address into l_bill_to_address, l_ship_to_address;
1670 Close get_bill_to_ship_to_address;
1671
1672 --pass the original bill_to and ship_to address back. If this not pass,
1673 --it will set the bill to and shipp to address to null value
1674 l_account_tbl(1).bill_to_address := l_bill_to_address;
1675 l_account_tbl(1).ship_to_address := l_ship_to_address;
1676 --bug#8508030
1677
1678
1679 l_txn_rec.transaction_date := sysdate;
1680 l_txn_rec.source_transaction_date := sysdate;
1681 l_txn_rec.transaction_type_id := 1;
1682
1683 -- Call the Update item instance API
1684 csi_item_instance_pub.update_item_instance
1685 (
1686 p_api_version => 1.0,
1687 p_commit => fnd_api.g_false,
1688 p_init_msg_list => fnd_api.g_true,
1689 p_validation_level => fnd_api.g_valid_level_full,
1690 p_instance_rec => l_instance_rec,
1691 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
1692 p_party_tbl => l_party_tbl,
1693 p_account_tbl => l_account_tbl,
1694 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1695 p_org_assignments_tbl => l_org_assignments_tbl,
1696 p_asset_assignment_tbl => l_asset_assignment_tbl,
1697 p_txn_rec => l_txn_rec,
1698 x_instance_id_lst => x_instance_id_lst,
1699 x_return_status => x_return_status,
1700 x_msg_count => x_msg_count,
1701 x_msg_data => x_msg_data
1702 );
1703
1704 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1705 RAISE FND_API.G_EXC_ERROR;
1706 End If;
1707
1708 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1709 fnd_log.STRING (fnd_log.level_procedure,
1710 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.CHANGE_BULKRCV_IB_OWNER.END',
1711 'Exit - Change Blkrcv IB Owner');
1712 End if;
1713
1714 EXCEPTION
1715 When FND_API.G_EXC_ERROR then
1716 Rollback To change_blkrcv_ib_owner;
1717 x_return_status := FND_API.G_RET_STS_ERROR ;
1718 FND_MSG_PUB.Count_And_Get
1719 (p_count => x_msg_count,
1720 p_data => x_msg_data );
1721
1722 When FND_API.G_EXC_UNEXPECTED_ERROR then
1723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1724 ROLLBACK TO change_blkrcv_ib_owner;
1725 FND_MSG_PUB.Count_And_Get
1726 ( p_count => x_msg_count,
1727 p_data => x_msg_data );
1728
1729 When OTHERS then
1730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1731 Rollback To change_blkrcv_ib_owner;
1732 If FND_MSG_PUB.Check_Msg_Level
1733 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
1734 FND_MSG_PUB.Add_Exc_Msg
1735 (G_PKG_NAME,
1736 l_api_name );
1737 End If;
1738 FND_MSG_PUB.Count_And_Get
1739 (p_count => x_msg_count,
1740 p_data => x_msg_data );
1741
1742 END;
1743
1744
1745 /*-----------------------------------------------------------------*/
1746 /* procedure name: bulk_auto_receive */
1747 /* description : Procedure to Auto Receive */
1748 /* */
1749 /*-----------------------------------------------------------------*/
1750 PROCEDURE bulk_auto_receive
1751 (
1752 p_bulk_autorcv_tbl IN OUT NOCOPY csd_bulk_receive_util.bulk_autorcv_tbl,
1753 x_return_status OUT NOCOPY VARCHAR2,
1754 x_msg_count OUT NOCOPY NUMBER,
1755 x_msg_data OUT NOCOPY VARCHAR2
1756 )
1757
1758 IS
1759
1760 -- Local variables
1761 l_msg_count Number;
1762 l_rcv_rec_tbl csd_receive_util.rcv_tbl_type;
1763 l_msg_data Varchar2(2000);
1764 i Number;
1765 l_org_id Number;
1766 l_api_name CONSTANT Varchar(30) := 'BULK_AUTO_RECEIVE';
1767 l_api_version CONSTANT Number := 1.0;
1768 l_header_error Boolean;
1769 l_errored Boolean;
1770 l_rcv_error_msg_tbl csd_receive_util.rcv_error_msg_tbl;
1771 l_item_name Varchar2(40);
1772 l_customer_id Number;
1773 l_account_id Number;
1774 l_estimate_quantity Number;
1775 l_unit_of_measure Varchar2(3);
1776 l_inventory_item_id Number;
1777 l_order_header_id Number;
1778 l_order_line_id Number;
1779 l_order_number Number;
1780 l_serial_number Varchar2(40);
1781 l_return_status Varchar2(3);
1782 l_prod_txn_status Varchar2(30);
1783
1784 -- 12.2 changes, subhat
1785 l_item_revision varchar2(3);
1786 l_lot_number varchar2(30);
1787 l_phase varchar2(30);
1788 l_status varchar2(30);
1789 l_dev_phase varchar2(30);
1790 l_dev_status varchar2(30);
1791 l_message varchar2(500);
1792
1793 Cursor c_ro_prodtxn(p_repair_line_id number,
1794 p_order_header_id number,
1795 p_order_line_id number) is
1796 select
1797 cib.customer_id,
1798 cib.account_id, -- Fix for bug#5848406
1799 cpt.estimate_quantity,
1800 cpt.unit_of_measure,
1801 cpt.inventory_item_id,
1802 cpt.order_header_id,
1803 cpt.order_line_id,
1804 cpt.order_number,
1805 cpt.serial_number,
1806 mtl.concatenated_segments item_name,
1807 -- subhat, 12.2 changes
1808 cpt.revision,
1809 cpt.lot_number
1810 -- end 12.2 changes, subhat
1811 from
1812 csd_product_txns_v cpt,
1813 cs_incidents_all_b cib,
1814 csd_repairs cr,
1815 mtl_system_items_kfv mtl
1816 where cpt.repair_line_id = p_repair_line_id
1817 and cr.repair_line_id = cpt.repair_line_id
1818 and cib.incident_id = cr.incident_id
1819 and cpt.order_header_id = p_order_header_id
1820 and cpt.order_line_id = p_order_line_id
1821 and mtl.inventory_item_id = cpt.inventory_item_id
1822 and mtl.organization_id = cs_std.get_item_valdn_orgzn_id;
1823
1824 Cursor c_get_org (p_order_header_id number) is
1825 Select nvl(b.ship_from_org_id,a.ship_from_org_id)
1826 from oe_order_headers_all a,
1827 oe_order_lines_all b
1828 where a.header_id = b.header_id
1829 and a.header_id = p_order_header_id;
1830
1831 Cursor c_get_prod_txn_status ( p_repair_line_id number ) is
1832 Select prod_txn_status
1833 from csd_product_transactions
1834 where repair_line_id = p_repair_line_id
1835 and action_type = 'RMA';
1836
1837 BEGIN
1838
1839 Savepoint bulk_auto_receive;
1840
1841 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1842 fnd_log.STRING (fnd_log.level_procedure,
1843 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.BULK_AUTO_RECEIVE.BEGIN',
1844 'Enter - Bulk auto receive');
1845 End if;
1846
1847 i := 0;
1848
1849 For l_tbl_id in 1..p_bulk_autorcv_tbl.count
1850 Loop
1851
1852 -- Assign values to the Auto Receive rec table
1853
1854 For c_ro_prdtxn_rec in c_ro_prodtxn( p_bulk_autorcv_tbl(l_tbl_id).repair_line_id,
1855 p_bulk_autorcv_tbl(l_tbl_id).order_header_id,
1856 p_bulk_autorcv_tbl(l_tbl_id).order_line_id)
1857 Loop
1858
1859 -- Derive the Org id
1860 l_org_id := null;
1861
1862 Open c_get_org (c_ro_prdtxn_rec.order_header_id);
1863 Fetch c_get_org into l_org_id;
1864 Close c_get_org;
1865
1866 i := i + 1;
1867 -- l_rcv_rec_tbl(i).customer_id := c_ro_prdtxn_rec.customer_id;
1868 -- Fix for bug#5848406
1869 l_rcv_rec_tbl(i).customer_id := c_ro_prdtxn_rec.account_id;
1870 l_rcv_rec_tbl(i).customer_site_id := null;
1871 l_rcv_rec_tbl(i).employee_id := null;
1872 --l_rcv_rec_tbl(i).quantity := abs(c_ro_prdtxn_rec.estimate_quantity);
1873 -- 12.2 bulk receiving enhancements. subhat
1874 IF NVL(p_bulk_autorcv_tbl(l_tbl_id).under_receipt_flag,'N') = 'Y' THEN
1875 l_rcv_rec_tbl(i).quantity := p_bulk_autorcv_tbl(l_tbl_id).receipt_qty;
1876 ELSE
1877 l_rcv_rec_tbl(i).quantity := abs(c_ro_prdtxn_rec.estimate_quantity);
1878 END IF;
1879 l_rcv_rec_tbl(i).uom_code := c_ro_prdtxn_rec.unit_of_measure;
1880 l_rcv_rec_tbl(i).inventory_item_id := c_ro_prdtxn_rec.inventory_item_id;
1881 -- subhat, 12.2. When the revision is captured, it should be passed on.
1882 --l_rcv_rec_tbl(i).item_revision := null;
1883 l_rcv_rec_tbl(i).item_revision := p_bulk_autorcv_tbl(l_tbl_id).item_revision;
1884 l_rcv_rec_tbl(i).to_organization_id := l_org_id;
1885 l_rcv_rec_tbl(i).destination_type_code := null;
1886 -- swai: bug 7663674
1887 --l_rcv_rec_tbl(i).subinventory := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SUB_INV');
1888 l_rcv_rec_tbl(i).subinventory := get_bulk_rcv_def_sub_inv( p_bulk_autorcv_tbl(l_tbl_id).repair_line_id);
1889 --l_rcv_rec_tbl(i).locator_id := null;
1890 l_rcv_rec_tbl(i).deliver_to_location_id := null;
1891 l_rcv_rec_tbl(i).requisition_number := null;
1892 l_rcv_rec_tbl(i).order_header_id := c_ro_prdtxn_rec.order_header_id;
1893 l_rcv_rec_tbl(i).order_line_id := c_ro_prdtxn_rec.order_line_id;
1894 l_rcv_rec_tbl(i).order_number := c_ro_prdtxn_rec.order_number;
1895 l_rcv_rec_tbl(i).doc_number := c_ro_prdtxn_rec.order_number;
1896 l_rcv_rec_tbl(i).internal_order_flag := 'N';
1897 l_rcv_rec_tbl(i).from_organization_id := null;
1898 l_rcv_rec_tbl(i).expected_receipt_date := sysdate;
1899 l_rcv_rec_tbl(i).transaction_date := sysdate;
1900 l_rcv_rec_tbl(i).ship_to_location_id := null;
1901 if c_ro_prdtxn_rec.serial_number is null and p_bulk_autorcv_tbl(l_tbl_id).serial_number is not null then
1902 l_rcv_rec_tbl(i).serial_number := p_bulk_autorcv_tbl(l_tbl_id).serial_number ;
1903 else
1904 l_rcv_rec_tbl(i).serial_number := c_ro_prdtxn_rec.serial_number;
1905 end if;
1906 -- 12.2 subhat. pass the lot number information also.
1907 l_rcv_rec_tbl(i).lot_number := p_bulk_autorcv_tbl(l_tbl_id).lot_number;
1908 l_rcv_rec_tbl(i).locator_id := p_bulk_autorcv_tbl(l_tbl_id).locator_id;
1909
1910 l_rcv_rec_tbl(i).attribute_category := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute_category;
1911 l_rcv_rec_tbl(i).attribute1 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute1;
1912 l_rcv_rec_tbl(i).attribute2 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute2;
1913 l_rcv_rec_tbl(i).attribute3 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute3;
1914 l_rcv_rec_tbl(i).attribute4 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute4;
1915 l_rcv_rec_tbl(i).attribute5 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute5;
1916 l_rcv_rec_tbl(i).attribute6 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute6;
1917 l_rcv_rec_tbl(i).attribute7 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute7;
1918 l_rcv_rec_tbl(i).attribute8 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute8;
1919 l_rcv_rec_tbl(i).attribute9 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute9;
1920 l_rcv_rec_tbl(i).attribute10 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute10;
1921 l_rcv_rec_tbl(i).attribute11 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute11;
1922 l_rcv_rec_tbl(i).attribute12 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute12;
1923 l_rcv_rec_tbl(i).attribute13 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute13;
1924 l_rcv_rec_tbl(i).attribute14 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute14;
1925 l_rcv_rec_tbl(i).attribute15 := p_bulk_autorcv_tbl(l_tbl_id).rcv_attribute15;
1926
1927
1928 End Loop;
1929
1930 End Loop;
1931
1932 -- Call the Receive API
1933 If (fnd_log.level_event >= fnd_log.g_current_runtime_level) then
1934 fnd_log.STRING (fnd_log.level_event,
1935 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.BULK_AUTO_RECEIVE.BEGIN',
1936 'Call receive item api');
1937 End if;
1938
1939 If(l_rcv_rec_tbl.count > 0 ) then
1940 -- we will mark a global variable to say that this is called from bulk rcv.
1941 csd_bulk_receive_pvt.g_bulk_rcv_conc := 'Y';
1942
1943 csd_receive_pvt.receive_item ( p_api_version => 1.0,
1944 p_init_msg_list => csd_process_util.g_false,
1945 p_commit => csd_process_util.g_false,
1946 p_validation_level => csd_process_util.g_valid_level_full,
1947 x_return_status => x_return_status,
1948 x_msg_count => l_msg_count,
1949 x_msg_data => l_msg_data,
1950 x_rcv_error_msg_tbl => l_rcv_error_msg_tbl,
1951 p_receive_tbl => l_rcv_rec_tbl);
1952
1953 -- reset it here.
1954 csd_bulk_receive_pvt.g_bulk_rcv_conc := 'N';
1955
1956 -- if the request is submitted successfully, no need to do anything here.
1957 if csd_bulk_receive_pvt.g_conc_req_id is not null then
1958 return;
1959 end if;
1960
1961 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then -- Status check If statement
1962
1963 -- Verify if there are any errors in header
1964
1965 csd_bulk_receive_util.write_to_conc_log
1966 ( p_msg_count => l_msg_count,
1967 p_msg_data => l_msg_data);
1968
1969
1970 If ( l_rcv_error_msg_tbl.count > 0 ) then
1971
1972 l_header_error := FALSE;
1973
1974 For i in 1..l_rcv_error_msg_tbl.count
1975 Loop
1976 If ( l_rcv_error_msg_tbl(i).header_interface_id is not null and
1977 l_rcv_error_msg_tbl(i).interface_transaction_id is null ) then
1978
1979 l_header_error := TRUE;
1980
1981 -- Display the message
1982 Fnd_file.put_line(fnd_file.log,'Error:Auto Receive failed - Header');
1983 Fnd_file.put(fnd_file.log,'Column name:');
1984 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).column_name);
1985 Fnd_file.put(fnd_file.log,'Error Message:');
1986 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).error_message);
1987
1988 End if;
1989 End Loop;
1990
1991 -- If there is header error the update all the Auto Receive lines
1992 -- in Bulk Rcv table to Errored
1993 If (l_header_error) then
1994
1995 -- Update all the auto receive records to error
1996 For i in 1..p_bulk_autorcv_tbl.count
1997 Loop
1998
1999 Update csd_bulk_receive_items_b
2000 set status = 'ERRORED'
2001 where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
2002
2003 End loop;
2004
2005 Else
2006
2007 -- If there are no header errors then check for
2008 -- line errors and update the records
2009 For i in 1..p_bulk_autorcv_tbl.count
2010 Loop
2011
2012 l_errored := FALSE;
2013
2014 For j in 1..l_rcv_error_msg_tbl.count
2015 Loop
2016
2017 If ( p_bulk_autorcv_tbl(i).order_header_id = l_rcv_error_msg_tbl(j).order_header_id and
2018 p_bulk_autorcv_tbl(i).order_line_id = l_rcv_error_msg_tbl(j).order_line_id ) then
2019
2020 l_errored := TRUE;
2021
2022 -- Display the error message
2023
2024 l_customer_id := null;
2025 l_estimate_quantity := null;
2026 l_unit_of_measure := null;
2027 l_inventory_item_id := null;
2028 l_order_header_id := null;
2029 l_order_line_id := null;
2030 l_order_number := null;
2031 l_serial_number := null;
2032 l_item_name := null;
2033
2034 Open c_ro_prodtxn(p_bulk_autorcv_tbl(i).repair_line_id,
2035 p_bulk_autorcv_tbl(i).order_header_id,
2036 p_bulk_autorcv_tbl(i).order_line_id);
2037
2038 -- 12.2 changes, subhat, added lot number and item revision
2039 Fetch c_ro_prodtxn into l_customer_id,l_account_id,l_estimate_quantity,
2040 l_unit_of_measure,l_inventory_item_id,l_order_header_id,
2041 l_order_line_id,l_order_number,l_serial_number,l_item_name,l_item_revision,l_lot_number;
2042 Close c_ro_prodtxn;
2043
2044 Fnd_file.put_line(fnd_file.log,'Error:Auto Receive failed - Line');
2045 Fnd_file.put(fnd_file.log,'Serial Number :'||l_serial_number||',');
2046 Fnd_file.put(fnd_file.log,'Inventory Item :'||l_item_name||',');
2047 Fnd_file.put_line(fnd_file.log,'Qty :'||l_estimate_quantity);
2048 Fnd_file.put(fnd_file.log,'Column name:');
2049 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).column_name);
2050 Fnd_file.put(fnd_file.log,'Error Message:');
2051 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).error_message);
2052
2053 End If;
2054
2055 End Loop;
2056
2057 If (l_errored) then
2058
2059 Update csd_bulk_receive_items_b
2060 set status = 'ERRORED'
2061 where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
2062
2063 Else
2064
2065 -- fix for bug 5227347
2066 -- Update csd_bulk_receive_items_b
2067 -- set status = 'PROCESSED'
2068 -- where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
2069 -- 12.2 changes, subhat
2070 if nvl(p_bulk_autorcv_tbl(i).under_receipt_flag,'N') = 'Y' then
2071 after_under_receipt_prcs(p_repair_line_id => p_bulk_autorcv_tbl(i).repair_line_id,
2072 p_order_header_id => p_bulk_autorcv_tbl(i).order_header_id,
2073 p_order_line_id => p_bulk_autorcv_tbl(i).order_line_id,
2074 p_received_qty => p_bulk_autorcv_tbl(i).receipt_qty);
2075 end if;
2076 -- end changes, subhat
2077 -- Call Update receipts program
2078 CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
2079 ( p_api_version => 1.0,
2080 p_commit => fnd_api.g_false,
2081 p_init_msg_list => fnd_api.g_true,
2082 p_validation_level => 0,
2083 x_return_status => l_return_status,
2084 x_msg_count => l_msg_count,
2085 x_msg_data => l_msg_data,
2086 p_internal_order_flag => 'N',
2087 p_order_header_id => null,
2088 p_repair_line_id => p_bulk_autorcv_tbl(i).repair_line_id);
2089
2090 -- fix for bug 5227347
2091 If NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) then
2092
2093 Fnd_file.put_line(fnd_file.log,'Error : CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE failed');
2094 csd_bulk_receive_util.write_to_conc_log
2095 ( p_msg_count => l_msg_count,
2096 p_msg_data => l_msg_data);
2097
2098 Else
2099
2100 -- Get Product Txn Status
2101 Open c_get_prod_txn_status ( p_bulk_autorcv_tbl(i).repair_line_id );
2102 Fetch c_get_prod_txn_status into l_prod_txn_status;
2103 Close c_get_prod_txn_status;
2104
2105 If ( l_prod_txn_status = 'RECEIVED' ) then
2106
2107 Update csd_bulk_receive_items_b
2108 set status = 'PROCESSED'
2109 where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
2110
2111 End if;
2112
2113 End if;
2114
2115 End if;
2116 End Loop;
2117
2118 End if; -- End if of l_header_error
2119
2120 Else
2121 -- Unexpected/Internal Error
2122 For i in 1..p_bulk_autorcv_tbl.count
2123 Loop
2124
2125 Update csd_bulk_receive_items_b
2126 set status = 'ERRORED'
2127 where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
2128
2129 End loop;
2130
2131 End if; -- End if of the l_rcv_error_msg_tbl.count > 0
2132
2133 Else
2134
2135 -- Update all the auto receive records to processed
2136 For i in 1..p_bulk_autorcv_tbl.count
2137 Loop
2138
2139 -- fix for bug 5227347
2140 -- Update csd_bulk_receive_items_b
2141 -- set status = 'PROCESSED'
2142 -- where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
2143
2144 -- 12.2 changes,subhat.
2145 -- process under receipts.
2146 if nvl(p_bulk_autorcv_tbl(i).under_receipt_flag,'N') = 'Y' then
2147 after_under_receipt_prcs(p_repair_line_id => p_bulk_autorcv_tbl(i).repair_line_id,
2148 p_order_header_id => p_bulk_autorcv_tbl(i).order_header_id,
2149 p_order_line_id => p_bulk_autorcv_tbl(i).order_line_id,
2150 p_received_qty => p_bulk_autorcv_tbl(i).receipt_qty);
2151 end if;
2152 -- 12.2 changes,subhat
2153
2154 -- Call Update receipts program
2155 CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
2156 ( p_api_version => 1.0,
2157 p_commit => fnd_api.g_false,
2158 p_init_msg_list => fnd_api.g_true,
2159 p_validation_level => 0,
2160 x_return_status => l_return_status,
2161 x_msg_count => l_msg_count,
2162 x_msg_data => l_msg_data,
2163 p_internal_order_flag => 'N',
2164 p_order_header_id => null,
2165 p_repair_line_id => p_bulk_autorcv_tbl(i).repair_line_id);
2166
2167 -- fix for bug 5227347
2168 If NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) then
2169
2170 Fnd_file.put_line(fnd_file.log,'Error : CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE failed');
2171 csd_bulk_receive_util.write_to_conc_log
2172 ( p_msg_count => l_msg_count,
2173 p_msg_data => l_msg_data);
2174
2175 Else
2176
2177 -- Get Product Txn Status
2178 Open c_get_prod_txn_status ( p_bulk_autorcv_tbl(i).repair_line_id );
2179 Fetch c_get_prod_txn_status into l_prod_txn_status;
2180 Close c_get_prod_txn_status;
2181
2182 If ( l_prod_txn_status = 'RECEIVED' ) then
2183
2184 Update csd_bulk_receive_items_b
2185 set status = 'PROCESSED'
2186 where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
2187
2188 End if;
2189
2190 End if;
2191
2192 End loop;
2193
2194 End if; -- End if of the Status check
2195
2196 End if; -- End if of the l_rcv_rec_tbl.count > 0
2197
2198 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2199 fnd_log.STRING (fnd_log.level_procedure,
2200 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.BULK_AUTO_RECEIVE.END',
2201 'Exit - Bulk auto receive');
2202 End if;
2203
2204 EXCEPTION
2205 When FND_API.G_EXC_ERROR then
2206 Rollback To bulk_auto_receive;
2207 x_return_status := FND_API.G_RET_STS_ERROR ;
2208 FND_MSG_PUB.Count_And_Get
2209 (p_count => x_msg_count,
2210 p_data => x_msg_data );
2211
2212 When FND_API.G_EXC_UNEXPECTED_ERROR then
2213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2214 ROLLBACK TO bulk_auto_receive;
2215 FND_MSG_PUB.Count_And_Get
2216 ( p_count => x_msg_count,
2217 p_data => x_msg_data );
2218
2219 When OTHERS then
2220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2221 Rollback To bulk_auto_receive;
2222 If FND_MSG_PUB.Check_Msg_Level
2223 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
2224 FND_MSG_PUB.Add_Exc_Msg
2225 (G_PKG_NAME,
2226 l_api_name );
2227 End If;
2228 FND_MSG_PUB.Count_And_Get
2229 (p_count => x_msg_count,
2230 p_data => x_msg_data );
2231
2232 END;
2233
2234
2235 /*-----------------------------------------------------------------*/
2236 /* procedure name: write_to_conc_log */
2237 /* description : Procedure to write into Concurrent log */
2238 /* It reads the message from the stack and writes */
2239 /* to Concurrent log. */
2240 /*-----------------------------------------------------------------*/
2241 PROCEDURE write_to_conc_log
2242 (
2243 p_msg_count IN NUMBER,
2244 p_msg_data IN VARCHAR2
2245 )
2246 IS
2247
2248 l_msg Varchar2(2000);
2249
2250 BEGIN
2251
2252 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2253 fnd_log.STRING (fnd_log.level_procedure,
2254 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.WRITE_TO_CONC_LOG.BEGIN',
2255 'Enter - Write to conc log');
2256 End if;
2257
2258 If p_msg_count is not null then
2259
2260 If p_msg_count = 1 then
2261
2262 l_msg := fnd_msg_pub.get(p_msg_index => 1,
2263 p_encoded => 'F' );
2264 Fnd_file.put_line(fnd_file.log,l_msg);
2265
2266 Elsif p_msg_count > 1 then
2267
2268 For i in 1..p_msg_count
2269
2270 Loop
2271 l_msg := fnd_msg_pub.get(p_msg_index => i,
2272 p_encoded => 'F' );
2273 Fnd_file.put_line(fnd_file.log,l_msg);
2274 End loop;
2275
2276 End If;
2277
2278 End If;
2279
2280 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2281 fnd_log.STRING (fnd_log.level_procedure,
2282 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.WRITE_TO_CONC_LOG.END',
2283 'Exit - Write to conc log');
2284 End if;
2285
2286 END;
2287
2288 /*-----------------------------------------------------------------*/
2289 /* procedure name: write_to_conc_output */
2290 /* description : Procedure to write the output to the Concurrent */
2291 /* Output */
2292 /*-----------------------------------------------------------------*/
2293 PROCEDURE write_to_conc_output
2294 (
2295 p_transaction_number IN NUMBER
2296 )
2297 IS
2298
2299 -- Local variables
2300 l_item_desc Varchar2(40);
2301 l_repair_number Varchar2(30);
2302 l_ro_status Varchar2(30);
2303 l_incident_number Varchar2(64);
2304 l_status Varchar2(60);
2305 l_serial_label Varchar2(30);
2306 l_txn_label Varchar2(30);
2307 l_item_label Varchar2(30);
2308 l_qty_label Varchar2(30);
2309 l_sr_label Varchar2(30);
2310 l_ro_label Varchar2(30);
2311 l_status_label Varchar2(30);
2312
2313 -- Cursor to get the Bulk Receive record
2314 Cursor c_get_bulk_receive(p_transaction_number in number) is
2315 Select *
2316 from csd_bulk_receive_items_b
2317 where transaction_number = p_transaction_number;
2318
2319 -- Cursor to get Incident number
2320 Cursor c_get_sr_details(p_incident_id in number) is
2321 Select incident_number
2322 from cs_incidents_all_b
2323 where incident_id = p_incident_id;
2324
2325 -- Cursor to get Repair Order number
2326 Cursor c_get_ro_details(p_repair_line_id in number) is
2327 Select repair_number
2328 ,status
2329 from csd_repairs
2330 where repair_line_id = p_repair_line_id;
2331
2332 -- Cursor to get Item description
2333 Cursor c_get_item_desc(p_inventory_item_id in number) is
2334 Select concatenated_segments
2335 from mtl_system_items_kfv
2336 where inventory_item_id = p_inventory_item_id;
2337
2338
2339 BEGIN
2340
2341 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2342 fnd_log.STRING (fnd_log.level_procedure,
2343 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.WRITE_TO_CONC_OUTPUT.BEGIN',
2344 'Enter - Write to conc output');
2345 End if;
2346
2347 fnd_message.set_name('CSD','CSD_BULK_RCV_SERIAL_CONC_LABEL');
2348 l_serial_label := fnd_message.get;
2349
2350 fnd_message.set_name('CSD','CSD_BULK_RCV_TXN_CONC_LABEL');
2351 l_txn_label := fnd_message.get;
2352
2353 fnd_message.set_name('CSD','CSD_BULK_RCV_ITEM_CONC_LABEL');
2354 l_item_label := fnd_message.get;
2355
2356 fnd_message.set_name('CSD','CSD_BULK_RCV_QTY_CONC_LABEL');
2357 l_qty_label := fnd_message.get;
2358
2359 fnd_message.set_name('CSD','CSD_BULK_RCV_SR_CONC_LABEL');
2360 l_sr_label := fnd_message.get;
2361
2362 fnd_message.set_name('CSD','CSD_BULK_RCV_RO_CONC_LABEL');
2363 l_ro_label := fnd_message.get;
2364
2365 fnd_message.set_name('CSD','CSD_BULK_RCV_STATUS_CONC_LABEL');
2366 l_status_label := fnd_message.get;
2367
2368 Fnd_file.put_line(fnd_file.output,rtrim(l_txn_label)||' : '||p_transaction_number);
2369 Fnd_file.put_line(fnd_file.output,'');
2370 Fnd_file.put(fnd_file.output,rpad(rtrim(l_serial_label),18,' '));
2371 Fnd_file.put(fnd_file.output,rpad(rtrim(l_item_label),14,' '));
2372 Fnd_file.put(fnd_file.output,rpad(rtrim(l_qty_label),13,' '));
2373 Fnd_file.put(fnd_file.output,rpad(rtrim(l_sr_label),25,' '));
2374 Fnd_file.put(fnd_file.output,rpad(rtrim(l_ro_label),25,' '));
2375 -- 12.1.2 changes, subhat.
2376 fnd_file.put(fnd_file.output,rpad('Unplanned Receipt',22,' ') );
2377 fnd_file.put(fnd_file.output,rpad('Over Receipt', 15, ' '));
2378 fnd_file.put(fnd_file.output,rpad('Under Receipt', 15, ' '));
2379 -- end 12.1.2 changes subhat
2380 Fnd_file.put_line(fnd_file.output,rpad(rtrim(l_status_label),28,' '));
2381 --Fnd_file.put_line(fnd_file.output,rpad('-',110,'-'));
2382 Fnd_file.put_line(fnd_file.output,rpad('-',170,'-'));
2383
2384
2385 For c_get_bulk_receive_rec in c_get_bulk_receive( p_transaction_number)
2386 Loop
2387
2388 -- Reinitialize the variable
2389 l_incident_number := null;
2390 l_repair_number := null;
2391 l_ro_status := null;
2392 l_item_desc := null;
2393
2394 Open c_get_sr_details(c_get_bulk_receive_rec.incident_id);
2395 Fetch c_get_sr_details into l_incident_number;
2396 Close c_get_sr_details;
2397
2398 Open c_get_ro_details(c_get_bulk_receive_rec.repair_line_id);
2399 Fetch c_get_ro_details into l_repair_number,l_ro_status;
2400 Close c_get_ro_details;
2401
2402 Open c_get_item_desc(c_get_bulk_receive_rec.inventory_item_id);
2403 Fetch c_get_item_desc into l_item_desc;
2404 Close c_get_item_desc;
2405
2406
2407 Fnd_file.put(fnd_file.output,rpad(nvl(c_get_bulk_receive_rec.serial_number,' '),18));
2408 Fnd_file.put(fnd_file.output,rpad(nvl(l_item_desc,' '),14,' '));
2409 Fnd_file.put(fnd_file.output,rpad(nvl(to_char(c_get_bulk_receive_rec.quantity),' '),13,' '));
2410 Fnd_file.put(fnd_file.output,rpad(nvl(l_incident_number,' '),25,' '));
2411 Fnd_file.put(fnd_file.output,rpad(nvl(l_repair_number,' '),25,' '));
2412
2413 --12.1.2 changes, subhat.
2414 if nvl(c_get_bulk_receive_rec.unplanned_receipt_flag,'N') = 'Y' then
2415 Fnd_file.put(fnd_file.output,rpad('Yes',22,' '));
2416 else
2417 Fnd_file.put(fnd_file.output,rpad('No',22,' '));
2418 end if;
2419
2420 if nvl(c_get_bulk_receive_rec.over_receipt_flag,'N') = 'Y' then
2421 Fnd_file.put(fnd_file.output,rpad('Yes',15,' '));
2422 else
2423 Fnd_file.put(fnd_file.output,rpad('No',15,' '));
2424 end if;
2425
2426 if nvl(c_get_bulk_receive_rec.under_receipt_flag,'N') = 'Y' then
2427 Fnd_file.put(fnd_file.output,rpad('Yes',15,' '));
2428 else
2429 Fnd_file.put(fnd_file.output,rpad('No',15,' '));
2430 end if;
2431 If ( c_get_bulk_receive_rec.status = 'ERRORED' ) then
2432
2433 fnd_message.set_name('CSD','CSD_BULK_RCV_ERROR_STATUS');
2434 l_status := fnd_message.get;
2435
2436 Elsif ( c_get_bulk_receive_rec.status = 'PROCESSED' ) then
2437
2438 If ( c_get_bulk_receive_rec.internal_sr_flag = 'Y') then
2439 fnd_message.set_name('CSD','CSD_BULK_RCV_INTR_SR_STATUS');
2440 l_status := fnd_message.get;
2441 Elsif (l_ro_status = 'D' ) then
2442 fnd_message.set_name('CSD','CSD_BULK_RCV_DRAFT_RO_STATUS');
2443 l_status := fnd_message.get;
2444 Else
2445 fnd_message.set_name('CSD','CSD_BULK_RCV_RECEIVED_STATUS');
2446 l_status := fnd_message.get;
2447 End if;
2448
2449 Elsif ( c_get_bulk_receive_rec.status = 'NEW' ) then
2450
2451 fnd_message.set_name('CSD','CSD_BULK_RCV_NEW_STATUS');
2452 l_status := fnd_message.get;
2453
2454 End if;
2455
2456 Fnd_file.put_line(fnd_file.output,rpad(rtrim(l_status),28,' '));
2457
2458 End Loop;
2459
2460 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2461 fnd_log.STRING (fnd_log.level_procedure,
2462 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.WRITE_TO_CONC_OUTPUT.END',
2463 'Exit - Write to conc output');
2464 End if;
2465
2466 END;
2467
2468 FUNCTION get_bulk_rcv_def_repair_type
2469 (
2470 p_incident_id IN NUMBER,
2471 p_ro_inventory_item_id IN NUMBER
2472 )
2473 return NUMBER
2474 IS
2475 CURSOR c_get_sr_info(p_incident_id number) is
2476 select customer_id,
2477 account_id,
2478 bill_to_site_use_id,
2479 ship_to_site_use_id,
2480 inventory_item_id,
2481 category_id,
2482 contract_id,
2483 problem_code,
2484 customer_product_id
2485 from CS_INCIDENTS_ALL_VL
2486 where incident_id = p_incident_id;
2487
2488 l_return_status VARCHAR2(1);
2489 l_msg_count NUMBER;
2490 l_msg_data VARCHAR2(2000);
2491 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
2492 l_default_val_num NUMBER;
2493 l_default_rule_id NUMBER;
2494 l_repair_org NUMBER; -- repair org id
2495 l_repair_type_id NUMBER := null; -- repair type id
2496
2497 BEGIN
2498 -- Assume SR Incident Id is available to get info for defaulting RO attributes
2499 open c_get_sr_info(p_incident_id);
2500 fetch c_get_sr_info into
2501 l_rule_input_rec.SR_CUSTOMER_ID,
2502 l_rule_input_rec.SR_CUSTOMER_ACCOUNT_ID,
2503 l_rule_input_rec.SR_BILL_TO_SITE_USE_ID,
2504 l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID,
2505 l_rule_input_rec.SR_ITEM_ID,
2506 l_rule_input_rec.SR_ITEM_CATEGORY_ID,
2507 l_rule_input_rec.SR_CONTRACT_ID,
2508 l_rule_input_rec.SR_PROBLEM_CODE,
2509 l_rule_input_rec.SR_INSTANCE_ID;
2510 close c_get_sr_info;
2511
2512 l_rule_input_rec.RO_ITEM_ID := p_ro_inventory_item_id;
2513
2514 l_default_val_num := null;
2515 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2516 p_api_version_number => 1.0,
2517 p_init_msg_list => fnd_api.g_false,
2518 p_commit => fnd_api.g_false,
2519 p_validation_level => fnd_api.g_valid_level_full,
2520 p_entity_attribute_type => 'CSD_DEF_ENTITY_ATTR_RO',
2521 p_entity_attribute_code => 'REPAIR_TYPE',
2522 p_rule_input_rec => l_rule_input_rec,
2523 x_default_value => l_default_val_num,
2524 x_rule_id => l_default_rule_id,
2525 x_return_status => l_return_status,
2526 x_msg_count => l_msg_count,
2527 x_msg_data => l_msg_data
2528 );
2529
2530 -- if default rule id is null, then no defaulting rule was found, and the
2531 -- profile for regular repair types was returned. We want the bulk receive
2532 -- profile option, so need to check default rule id.
2533 if (l_return_status = fnd_api.g_ret_sts_success) and
2534 (l_default_val_num is not null) and
2535 (l_default_rule_id is not null)
2536 then
2537 l_repair_type_id := l_default_val_num;
2538 else
2539 l_repair_type_id := to_number(fnd_profile.value('CSD_BLK_RCV_DEFAULT_REPAIR_TYPE'));
2540 end if;
2541
2542 return l_repair_type_id;
2543
2544 END get_bulk_rcv_def_repair_type;
2545
2546 -- swai: bug 7663674
2547 -- added function to get default rma subinv and use bulk receiving
2548 -- profile option value as backup default value.
2549 /*-----------------------------------------------------------------*/
2550 /* function name: get_bulk_rcv_def_sub_inv */
2551 /* description : Function to get the default rma subinv for */
2552 /* bulk receiving, based on defaulting rules and */
2553 /* bulk receiving profile option. */
2554 /* Output RMA Subinventory Code */
2555 /*-----------------------------------------------------------------*/
2556 FUNCTION get_bulk_rcv_def_sub_inv
2557 (
2558 p_repair_line_id IN NUMBER
2559 )
2560 return VARCHAR2
2561 IS
2562 l_return_status VARCHAR2(1);
2563 l_msg_count NUMBER;
2564 l_msg_data VARCHAR2(2000);
2565 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
2566 l_default_val_str VARCHAR2(30);
2567 l_default_rule_id NUMBER := null;
2568 l_rma_subinv VARCHAR2(30) := null; -- repair type id
2569
2570 BEGIN
2571 l_rule_input_rec.repair_line_id := p_repair_line_id;
2572 l_default_val_str := null;
2573 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2574 p_api_version_number => 1.0,
2575 p_init_msg_list => fnd_api.g_false,
2576 p_commit => fnd_api.g_false,
2577 p_validation_level => fnd_api.g_valid_level_full,
2578 p_entity_attribute_type => 'CSD_DEF_ENTITY_ATTR_RO',
2579 p_entity_attribute_code => 'RMA_RCV_SUBINV',
2580 p_rule_input_rec => l_rule_input_rec,
2581 x_default_value => l_default_val_str,
2582 x_rule_id => l_default_rule_id,
2583 x_return_status => l_return_status,
2584 x_msg_count => l_msg_count,
2585 x_msg_data => l_msg_data
2586 );
2587
2588 -- if default rule id is null, then no defaulting rule was found, and the
2589 -- profile for regular rma subinv was returned. We want the bulk receive
2590 -- profile option, so need to check default rule id.
2591 if (l_return_status = fnd_api.g_ret_sts_success) and
2592 (l_default_val_str is not null) and
2593 (l_default_rule_id is not null)
2594 then
2595 l_rma_subinv := l_default_val_str;
2596 else
2597 l_rma_subinv := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SUB_INV');
2598 end if;
2599
2600 return l_rma_subinv;
2601
2602 END get_bulk_rcv_def_sub_inv;
2603
2604 -- subhat, new procedures.
2605 /*-----------------------------------------------------------------*/
2606 /* procedure name: get_sr_ro_rma_details */
2607 /* description : Procedure to get the existing SR,RO,RMA */
2608 /* combination */
2609 /* Called from link_sr_ro_rma_oa_wrapper. */
2610 /*-----------------------------------------------------------------*/
2611
2612 PROCEDURE get_sr_ro_rma_details
2613 (
2614 p_transaction_number IN NUMBER,
2615 x_sr_ro_rma_tbl IN OUT NOCOPY sr_ro_rma_tbl
2616 ) IS
2617
2618 l_inventory_item_id NUMBER;
2619 l_instance_id NUMBER;
2620 l_counter NUMBER := 0;
2621 l_check_ro VARCHAR2(3);
2622
2623 l_no_exact_match varchar2(3) := 'N';
2624
2625
2626 lc_sql_string_ro varchar2(2000) :=' select sr.incident_id,cr.repair_line_id,cr.quantity,''N'',''N''' ||
2627 ' from cs_incidents_all_b sr,csd_repairs cr,csd_repair_types_b crt '||
2628 ' where cr.inventory_item_id = :p_inv_item_id '||
2629 ' and cr.status = ''O'' '||
2630 ' and cr.incident_id = sr.incident_id '||
2631 ' and sr.account_id = :p_acc_id '||
2632 ' and sr.customer_id = :p_party_id '||
2633 ' AND cr.repair_type_id = crt.repair_type_id '||
2634 ' and not exists ( '||
2635 ' select repair_line_id '||
2636 ' from csd_product_transactions cpt '||
2637 ' where crt.repair_type_ref <> ''ARR'' '||
2638 ' and cpt.repair_line_id = cr.repair_line_id '||
2639 ' and cpt.action_type = ''RMA'' '||
2640 ' and cpt.prod_txn_status in (''CANCELLED'' ,''RECEIVED'') '||
2641 ' UNION ALL '||
2642 ' SELECT repair_line_id '||
2643 ' from csd_product_transactions cpt1' ||
2644 ' where crt.repair_type_ref = ''ARR''' ||
2645 ' AND cpt1.repair_line_id = cr.repair_line_id '||
2646 ' AND cpt1.action_type = ''RMA'' '||
2647 ' AND ((cpt1.action_code = ''LOANER'' AND cpt1.prod_txn_status IN (''CANCELLED'',''RECEIVED''))AND ' ||
2648 ' (cpt1.action_code = ''CUST_PROD'' AND cpt1.prod_txn_status IN (''CANCELLED'',''RECEIVED'')) )) ';
2649
2650 l_ro_query_sql varchar2(2000) := ' select repair_line_id from csd_repairs where '||
2651 ' incident_id := :b_incident_id and '||
2652 ' status := ''O'' ' ;
2653 l_sql_string_tmp varchar2(2000);
2654 l_repair_line_ids_in varchar2(2000);
2655
2656 lc_api_name varchar2(200) := 'csd.plsql.csd_bulk_receive_util.get_sr_ro_rma_details';
2657
2658 l_in_progress_ro JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2659
2660 l_check_loaner_sn varchar2(3) := 'N';
2661 l_check_loaner_non_sn varchar2(3) := 'N';
2662
2663 begin
2664 -- program logic
2665 -- A.find out the records available for processing.
2666 -- find out the item attributes for the records.
2667 -- depending on the type of item fire search for SR, RO and RMA's.
2668 -- populate the rec with relevant details and send back the control to the caller.
2669
2670 -- Need to exclude those repair line_id's which are already picked up for processing,
2671 -- and concurrent manager is processing them.
2672 begin
2673 select repair_line_id
2674 bulk collect into l_in_progress_ro
2675 from csd_bulk_receive_items_b
2676 where transaction_number in
2677 (
2678 select argument1
2679 from fnd_concurrent_requests fcr,
2680 fnd_concurrent_programs fcp
2681 where fcp.concurrent_program_name = 'CSDBLKRCV'
2682 and fcp.application_id = 512
2683 and fcr.program_application_id = fcp.application_id
2684 and fcp.concurrent_program_id = fcr.concurrent_program_id
2685 and fcr.status_code <> 'C'
2686 );
2687 exception
2688 when no_data_found then
2689 null;
2690 end;
2691 -- get the records for the processing.
2692
2693 for i in 1 ..x_sr_ro_rma_tbl.COUNT
2694 loop
2695
2696 l_counter := l_counter + 1;
2697 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2698 fnd_log.STRING (fnd_log.level_procedure,
2699 lc_api_name,'Start processing bulk receive id '||x_sr_ro_rma_tbl(l_counter).bulk_receive_id);
2700 End if;
2701 if x_sr_ro_rma_tbl(l_counter).inventory_item_id is not null then
2702
2703 if (x_sr_ro_rma_tbl(l_counter).serial_number is not null and x_sr_ro_rma_tbl(l_counter).instance_id is not null) then
2704 -- case1.
2705 -- We will look for the exact matching RO. If we find it we will return it else,
2706 -- we will try to find the latest open SR for the customer account and party combination.
2707 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2708 fnd_log.STRING (fnd_log.level_procedure,
2709 lc_api_name,'Searching the match for SN '||x_sr_ro_rma_tbl(l_counter).Serial_number);
2710 End if;
2711 l_sql_string_tmp := 'select repair_line_id,incident_id,''N'',''N'' from (' ||lc_sql_string_ro||
2712 ' and cr.serial_number = :p_serial_number '||
2713 ' and cr.customer_product_id = :p_instance_id '||
2714 ' order by cr.creation_date desc ) where rownum = 1' ;
2715 begin
2716 execute immediate l_sql_string_tmp INTO x_sr_ro_rma_tbl(l_counter).repair_line_id,
2717 x_sr_ro_rma_tbl(l_counter).incident_id,x_sr_ro_rma_tbl(l_counter).create_sr_flag,
2718 x_sr_ro_rma_tbl(l_counter).create_ro_flag
2719 using x_sr_ro_rma_tbl(l_counter).inventory_item_id,x_sr_ro_rma_tbl(l_counter).cust_acct_id,
2720 x_sr_ro_rma_tbl(l_counter).party_id,x_sr_ro_rma_tbl(l_counter).serial_number,
2721 x_sr_ro_rma_tbl(l_counter).instance_id;
2722 exception
2723 when no_data_found then
2724 -- special case.
2725 -- the SN being searched upon may not be on any of the open repair orders.
2726 -- but that SN could possibly be on any of the loaner RMA's.
2727 l_check_loaner_sn := 'Y';
2728
2729 end;
2730
2731 elsif (x_sr_ro_rma_tbl(l_counter).serial_number is not null and x_sr_ro_rma_tbl(l_counter).instance_id is null) then
2732 -- the instance number is null.
2733 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2734 fnd_log.STRING (fnd_log.level_procedure,
2735 lc_api_name,'Searching the match for SN(Non-IB) '||x_sr_ro_rma_tbl(l_counter).Serial_number);
2736 End if;
2737 l_sql_string_tmp := NULL;
2738 l_sql_string_tmp := ' select repair_line_id,incident_id,''N'',''N'' from ( '||
2739 lc_sql_string_ro||' and cr.serial_number = :p_serial_number '||
2740 ' order by cr.creation_date desc ) where rownum = 1 ';
2741 begin
2742 execute immediate l_sql_string_tmp into x_sr_ro_rma_tbl(l_counter).repair_line_id,
2743 x_sr_ro_rma_tbl(l_counter).incident_id,x_sr_ro_rma_tbl(l_counter).create_sr_flag,
2744 x_sr_ro_rma_tbl(l_counter).create_ro_flag
2745 using x_sr_ro_rma_tbl(l_counter).inventory_item_id,x_sr_ro_rma_tbl(l_counter).cust_acct_id,
2746 x_sr_ro_rma_tbl(l_counter).party_id,x_sr_ro_rma_tbl(l_counter).serial_number;
2747 exception
2748 when no_data_found then
2749 -- special case.
2750 -- the SN being searched upon may not be on any of the open repair orders.
2751 -- but that SN could possibly be on any of the loaner RMA's.
2752 l_check_loaner_sn := 'Y';
2753 end;
2754
2755 elsif x_sr_ro_rma_tbl(l_counter).serial_number is null and
2756 x_sr_ro_rma_tbl(l_counter).serial_control_flag <> 1 then
2757 -- item is serial controlled, but no serial number was keyed in.
2758 if x_sr_ro_rma_tbl(l_counter).quantity > 1 then
2759 if x_sr_ro_rma_tbl(l_counter).ui_incident_id is not null then
2760 x_sr_ro_rma_tbl(l_counter).incident_id := x_sr_ro_rma_tbl(l_counter).ui_incident_id;
2761 else
2762 x_sr_ro_rma_tbl(l_counter).incident_id := get_latest_open_sr(x_sr_ro_rma_tbl(l_counter).cust_acct_id,
2763 x_sr_ro_rma_tbl(l_counter).party_id);
2764 end if;
2765
2766 if x_sr_ro_rma_tbl(l_counter).incident_id is null then
2767 x_sr_ro_rma_tbl(l_counter).create_sr_flag := 'Y';
2768 end if;
2769 x_sr_ro_rma_tbl(l_counter).create_ro_flag := 'Y';
2770 end if;
2771
2772 elsif x_sr_ro_rma_tbl(l_counter).serial_number is null then
2773 l_sql_string_tmp := NULL;
2774 l_sql_string_tmp := ' select incident_id,repair_line_id,''N'',''N'' from ('||lc_sql_string_ro||
2775 ' and cr.quantity = :p_quantity order by sr.incident_id desc ) where rownum = 1';
2776 begin
2777 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2778 fnd_log.STRING (fnd_log.level_procedure,
2779 lc_api_name,
2780 'RO-SR Match: begin matching non-serial controlled items');
2781 End if;
2782 select incident_id,repair_line_id, create_sr_flag,create_ro_flag
2783 into x_sr_ro_rma_tbl(l_counter).incident_id,x_sr_ro_rma_tbl(l_counter).repair_line_id,
2784 x_sr_ro_rma_tbl(l_counter).create_sr_flag,x_sr_ro_rma_tbl(l_counter).create_ro_flag
2785 from (
2786 select sr.incident_id,cr.repair_line_id,'N' create_sr_flag,'N' create_ro_flag
2787 from csd_repairs cr,cs_incidents_all_b sr
2788 where cr.inventory_item_id = x_sr_ro_rma_tbl(l_counter).inventory_item_id
2789 and cr.status = 'O'
2790 and cr.incident_id = sr.incident_id
2791 and sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
2792 and sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
2793 and not exists (
2794 select repair_line_id
2795 from csd_product_transactions cpt
2796 where cpt.repair_line_id = cr.repair_line_id
2797 and cpt.action_type = 'RMA'
2798 and cpt.prod_txn_status in ('RECEIVED','CANCELLED'))
2799 and cr.quantity = x_sr_ro_rma_tbl(l_counter).quantity
2800 and cr.repair_line_id not in (
2801 select * from TABLE(cast(get_num_in_list(l_repair_line_ids_in) as JTF_NUMBER_TABLE))
2802 union all select * from table(cast(l_in_progress_ro as JTF_NUMBER_TABLE)))
2803 order by cr.creation_date desc ) where rownum = 1;
2804
2805 l_repair_line_ids_in := x_sr_ro_rma_tbl(l_counter).repair_line_id||',';
2806
2807 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2808 fnd_log.STRING (fnd_log.level_procedure,
2809 lc_api_name,
2810 'RO-SR Match: found a row for non serial controlled item '||l_repair_line_ids_in);
2811 End if;
2812 exception
2813 -- there are no exact match found. Now look for the recent most open RO,RMA for the
2814 -- customer, and Item. Start from RO and proceed.
2815 when no_data_found then
2816 l_check_loaner_non_sn := 'Y';
2817 --l_no_exact_match := 'Y';
2818 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2819 fnd_log.STRING (fnd_log.level_procedure,
2820 lc_api_name,
2821 'RO-SR Match: In no data found');
2822 End if;
2823 end;
2824
2825 if l_check_loaner_non_sn = 'Y' then
2826 begin
2827 SELECT sr.incident_id ,
2828 cr.repair_line_id ,
2829 'N' ,
2830 'N'
2831 into
2832 x_sr_ro_rma_tbl(l_counter).incident_id,
2833 x_sr_ro_rma_tbl(l_counter).repair_line_id,
2834 x_sr_ro_rma_tbl(l_counter).create_sr_flag,
2835 x_sr_ro_rma_tbl(l_counter).create_ro_flag
2836 from csd_repairs cr,
2837 csd_product_transactions cpt,
2838 cs_incidents_all_b sr,
2839 cs_estimate_details ced
2840 where cr.incident_id = sr.incident_id
2841 and sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
2842 and sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
2843 and cpt.repair_line_id = cr.repair_line_id
2844 and cpt.estimate_detail_id = ced.estimate_detail_id
2845 and abs(ced.quantity_required) = x_sr_ro_rma_tbl(l_counter).quantity
2846 and ced.inventory_item_id = x_sr_ro_rma_tbl(l_counter).inventory_item_id
2847 and cpt.action_type = 'SHIP'
2848 and cpt.action_code = 'LOANER'
2849 and cpt.prod_txn_status = 'SHIPPED'
2850 and not exists
2851 ( select 'Y' from csd_product_transactions cpt1
2852 where cpt1.repair_line_id = cpt.repair_line_id
2853 and cpt1.action_type = 'RMA'
2854 and cpt1.action_code = 'LOANER'
2855 and cpt1.prod_txn_status in ('RECEIVED','CANCELLED')
2856 )
2857 and cr.repair_line_id not in (
2858 select * from TABLE(cast(get_num_in_list(l_repair_line_ids_in) as JTF_NUMBER_TABLE))
2859 union all select * from table(cast(l_in_progress_ro as JTF_NUMBER_TABLE)));
2860
2861 l_check_loaner_non_sn := 'N';
2862 l_repair_line_ids_in := x_sr_ro_rma_tbl(l_counter).repair_line_id||',';
2863 exception
2864 when no_data_found then
2865 l_no_exact_match := 'Y';
2866 l_check_loaner_non_sn := 'N';
2867 end;
2868 end if;
2869
2870 if l_no_exact_match = 'Y' then
2871 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2872 fnd_log.STRING (fnd_log.level_procedure,
2873 lc_api_name,
2874 'RO-SR Match: look for the latest match: Ignore quantity');
2875 End if;
2876 /*l_sql_string_tmp := NULL;
2877 l_sql_string_tmp := ' select incident_id,repair_line_id, quantity,''N'',''N'' from ('||lc_sql_string_ro||
2878 ' order by cr.creation_date desc ) where rownum = 1 ';*/
2879 begin
2880 select incident_id,repair_line_id, create_sr_flag,create_ro_flag
2881 into x_sr_ro_rma_tbl(l_counter).incident_id,x_sr_ro_rma_tbl(l_counter).repair_line_id,
2882 x_sr_ro_rma_tbl(l_counter).create_sr_flag,x_sr_ro_rma_tbl(l_counter).create_ro_flag
2883 from(
2884 select sr.incident_id,cr.repair_line_id,'N' create_sr_flag,'N' create_ro_flag
2885 from csd_repairs cr,cs_incidents_all_b sr
2886 where cr.inventory_item_id = x_sr_ro_rma_tbl(l_counter).inventory_item_id
2887 and cr.status = 'O'
2888 and cr.incident_id = sr.incident_id
2889 and sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
2890 and sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
2891 and not exists (
2892 select repair_line_id
2893 from csd_product_transactions cpt
2894 where cpt.repair_line_id = cr.repair_line_id
2895 and cpt.action_type = 'RMA'
2896 and cpt.prod_txn_status in ('RECEIVED','CANCELLED'))
2897 and cr.repair_line_id not in (
2898 select * from TABLE(cast(get_num_in_list(l_repair_line_ids_in) as JTF_NUMBER_TABLE))
2899 union all select * from table(cast(l_in_progress_ro as JTF_NUMBER_TABLE)))
2900 order by cr.creation_date desc) where rownum = 1;
2901 -- bug#8978204, subhat.
2902 l_repair_line_ids_in := x_sr_ro_rma_tbl(l_counter).repair_line_id||',';
2903
2904 exception
2905 when no_data_found then
2906 if x_sr_ro_rma_tbl(l_counter).ui_incident_id is not null then
2907 x_sr_ro_rma_tbl(l_counter).incident_id := x_sr_ro_rma_tbl(l_counter).ui_incident_id;
2908 else
2909 x_sr_ro_rma_tbl(l_counter).incident_id := get_latest_open_sr(x_sr_ro_rma_tbl(l_counter).cust_acct_id,
2910 x_sr_ro_rma_tbl(l_counter).party_id);
2911 end if;
2912 x_sr_ro_rma_tbl(l_counter).incident_id := get_latest_open_sr(x_sr_ro_rma_tbl(l_counter).cust_acct_id,x_sr_ro_rma_tbl(l_counter).party_id);
2913 if x_sr_ro_rma_tbl(l_counter).incident_id is null then
2914 x_sr_ro_rma_tbl(l_counter).create_sr_flag := 'Y';
2915 end if;
2916 x_sr_ro_rma_tbl(l_counter).create_ro_flag := 'Y';
2917 end;
2918 end if;
2919 end if;
2920
2921 end if;
2922
2923 -- special search for the loaner items.
2924 if l_check_loaner_sn = 'Y' THEN
2925 begin
2926 SELECT sr.incident_id ,
2927 cr.repair_line_id ,
2928 'N' ,
2929 'N'
2930 INTO
2931 x_sr_ro_rma_tbl(l_counter).incident_id,
2932 x_sr_ro_rma_tbl(l_counter).repair_line_id,
2933 x_sr_ro_rma_tbl(l_counter).create_sr_flag,
2934 x_sr_ro_rma_tbl(l_counter).create_ro_flag
2935 FROM csd_repairs cr,
2936 csd_product_transactions cpt,
2937 cs_incidents_all_b sr
2938 WHERE cr.incident_id = sr.incident_id
2939 AND sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
2940 AND sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
2941 AND cpt.repair_line_id = cr.repair_line_id
2942 AND cpt.source_serial_number = x_sr_ro_rma_tbl(l_counter).serial_number
2943 AND cpt.action_type = 'SHIP'
2944 AND cpt.action_code = 'LOANER'
2945 AND cpt.prod_txn_status = 'SHIPPED'
2946 AND NOT EXISTS
2947 ( SELECT 'Y' FROM csd_product_transactions cpt1
2948 WHERE cpt1.repair_line_id = cpt.repair_line_id
2949 AND cpt1.action_type = 'RMA'
2950 AND cpt1.action_code = 'LOANER'
2951 AND cpt1.prod_txn_status in ('RECEIVED','CANCELLED')
2952 )
2953 and cr.repair_line_id not in (
2954 select * from TABLE(cast(get_num_in_list(l_repair_line_ids_in) as JTF_NUMBER_TABLE))
2955 union all select * from table(cast(l_in_progress_ro as JTF_NUMBER_TABLE)));
2956
2957 l_check_loaner_sn := 'N';
2958 l_repair_line_ids_in := x_sr_ro_rma_tbl(l_counter).repair_line_id||',';
2959
2960 exception
2961 when no_data_found then
2962 if x_sr_ro_rma_tbl(l_counter).ui_incident_id is not null then
2963 x_sr_ro_rma_tbl(l_counter).incident_id := x_sr_ro_rma_tbl(l_counter).ui_incident_id;
2964 else
2965 x_sr_ro_rma_tbl(l_counter).incident_id := get_latest_open_sr(x_sr_ro_rma_tbl(l_counter).cust_acct_id,
2966 x_sr_ro_rma_tbl(l_counter).party_id);
2967 end if;
2968 if x_sr_ro_rma_tbl(l_counter).incident_id is null then
2969 x_sr_ro_rma_tbl(l_counter).create_sr_flag := 'Y';
2970 end if;
2971 x_sr_ro_rma_tbl(l_counter).create_ro_flag := 'Y';
2972 end;
2973 end if;
2974
2975 -- populate the RO details if its not already populated.
2976 -- logic
2977 -- *
2978 if x_sr_ro_rma_tbl(l_counter).incident_id is not null and x_sr_ro_rma_tbl(l_counter).repair_line_id is null
2979 and nvl(x_sr_ro_rma_tbl(l_counter).create_ro_flag,'N') <> 'Y' then
2980 if x_sr_ro_rma_tbl(l_counter).serial_number is not null then
2981 l_ro_query_sql := l_ro_query_sql ||' and serial_number := :serial_number '||
2982 ' order by creation_date desc';
2983 elsif x_sr_ro_rma_tbl(l_counter).serial_number is null then
2984 l_ro_query_sql := l_ro_query_sql||' order by creation_date desc ';
2985 end if;
2986 l_ro_query_sql := 'select repair_line_id from ( '||l_ro_query_sql||
2987 ' ) where rownum = 1 ';
2988 begin
2989 if x_sr_ro_rma_tbl(l_counter).serial_number is not null then
2990 execute immediate l_ro_query_sql into x_sr_ro_rma_tbl(l_counter).repair_line_id
2991 using x_sr_ro_rma_tbl(l_counter).incident_id,x_sr_ro_rma_tbl(l_counter).serial_number;
2992 else
2993 execute immediate l_ro_query_sql into x_sr_ro_rma_tbl(l_counter).repair_line_id
2994 using x_sr_ro_rma_tbl(l_counter).incident_id;
2995 end if;
2996 exception
2997 when no_data_found then
2998 x_sr_ro_rma_tbl(l_counter).create_ro_flag := 'Y';
2999 x_sr_ro_rma_tbl(l_counter).found_rma_flag := 'Y';
3000 end;
3001 end if;
3002 x_sr_ro_rma_tbl(l_counter).bulk_receive_id := x_sr_ro_rma_tbl(l_counter).bulk_receive_id;
3003 --end if;
3004 end loop;
3005
3006 end get_sr_ro_rma_details;
3007
3008 PROCEDURE matching_rma_found
3009 (
3010 p_repair_line_id IN NUMBER,
3011 p_blk_rec_qty IN NUMBER,
3012 p_blk_rec_serial_number IN VARCHAR2,
3013 p_blk_rec_instance_id IN NUMBER,
3014 p_blk_rec_inv_id IN NUMBER,
3015 x_rma_found OUT NOCOPY VARCHAR2,
3016 x_new_rma OUT NOCOPY VARCHAR2,
3017 x_split_rma_qty OUT NOCOPY NUMBER,
3018 x_new_rma_qty OUT NOCOPY NUMBER,
3019 x_split_rma OUT NOCOPY VARCHAR2,
3020 x_order_header_id OUT NOCOPY NUMBER,
3021 x_order_line_id OUT NOCOPY NUMBER
3022 ) IS
3023
3024 order_booked varchar2(3);
3025 l_matching_rma_tbl MATCHING_RMA_TBL;
3026 l_prod_txn_rec csd_process_pvt.product_txn_rec;
3027 l_msg_count number;
3028 l_msg_data varchar2(2000);
3029 l_return_status varchar2(3);
3030
3031 l_check_loaner varchar2(2) := 'N';
3032
3033 x_msg_index_out number;
3034
3035
3036 begin
3037 -- fetch all applicable records in a one single go.
3038 -- match SN,Quantity etc on the matched records.
3039
3040 --begin
3041 select cpt.prod_txn_status,
3042 cpt.source_serial_number,
3043 cpt.source_instance_id,
3044 abs(ced.quantity_required) rma_quantity,
3045 ool.header_id,
3046 ool.line_id,
3047 ool.inventory_item_id
3048 bulk collect into
3049 l_matching_rma_tbl
3050 from csd_product_transactions cpt,
3051 cs_estimate_details ced,
3052 oe_order_lines_all ool,
3053 csd_repairs cr,
3054 csd_repair_types_b crtb
3055 where
3056 cpt.repair_line_id = p_repair_line_id and
3057 cpt.action_type = 'RMA' and
3058 crtb.repair_type_id = cr.repair_type_id and
3059 cpt.action_code = decode(crtb.repair_type_ref,'RR','CUST_PROD','ARR','CUST_PROD','E','EXCHANGE','AE','EXCHANGE','CUST_PROD') and
3060 cpt.prod_txn_status <> 'RECEIVED' and
3061 cpt.estimate_detail_id = ced.estimate_detail_id and
3062 ced.order_header_id = ool.header_id and
3063 ced.order_line_id = ool.line_id and
3064 cr.repair_line_id = cpt.repair_line_id and
3065 decode(cr.serial_number,null,'-1',cr.serial_number) = decode(cpt.source_serial_number,null,'-1',cpt.source_serial_number);
3066
3067 if l_matching_rma_tbl.COUNT = 0 then
3068 -- no RMA's exist which are already interfaced to OM.
3069 -- there may be lines which are just entered in Charges and Depot. Look for them too.
3070 --dbms_output.put_line('No data found');
3071 begin
3072 select cpt.prod_txn_status,
3073 cpt.product_transaction_id,
3074 ced.estimate_detail_id,
3075 ced.inventory_item_id,
3076 ced.incident_id,
3077 ced.invoice_to_org_id,
3078 ced.ship_to_org_id,
3079 ced.org_id,
3080 ced.transaction_inventory_org
3081
3082 into l_prod_txn_rec.prod_txn_status,
3083 l_prod_txn_rec.product_transaction_id,
3084 l_prod_txn_rec.estimate_detail_id,
3085 l_prod_txn_rec.inventory_item_id,
3086 l_prod_txn_rec.incident_id,
3087 l_prod_txn_rec.invoice_to_org_id,
3088 l_prod_txn_rec.ship_to_org_id,
3089 l_prod_txn_rec.organization_id,
3090 l_prod_txn_rec.inventory_org_id
3091
3092 from csd_product_transactions cpt,
3093 cs_estimate_details ced
3094 where cpt.repair_line_id = p_repair_line_id and
3095 cpt.action_type = 'RMA' and
3096 cpt.action_code in ('CUST_PROD','EXCHANGE') and
3097 cpt.prod_txn_status <> 'RECEIVED' and
3098 cpt.estimate_detail_id = ced.estimate_detail_id and
3099 nvl(cpt.interface_to_om_flag,'N') = 'N' and
3100 rownum < 2;
3101
3102 x_rma_found := 'Y';
3103
3104 exception
3105 when no_data_found then
3106 l_check_loaner := 'Y';
3107 --x_rma_found := 'N';
3108 --return;
3109 end;
3110 end if;
3111
3112 if l_check_loaner = 'Y' then
3113 select cpt.prod_txn_status,
3114 cpt.source_serial_number,
3115 cpt.source_instance_id,
3116 abs(ced.quantity_required) rma_quantity,
3117 ool.header_id,
3118 ool.line_id,
3119 ool.inventory_item_id
3120 bulk collect into
3121 l_matching_rma_tbl
3122 from csd_product_transactions cpt,
3123 cs_estimate_details ced,
3124 oe_order_lines_all ool,
3125 csd_repairs cr
3126 where
3127 cpt.repair_line_id = p_repair_line_id and
3128 cpt.action_type = 'RMA' and
3129 cpt.action_code = 'LOANER' and
3130 cpt.prod_txn_status <> 'RECEIVED' and
3131 cpt.estimate_detail_id = ced.estimate_detail_id and
3132 ced.order_header_id = ool.header_id and
3133 ced.order_line_id = ool.line_id and
3134 cr.repair_line_id = cpt.repair_line_id ;
3135 --decode(cr.serial_number,null,'-1',cr.serial_number) = decode(cpt.source_serial_number,null,'-1',cpt.source_serial_number);
3136
3137 if l_matching_rma_tbl.COUNT = 0 then
3138 begin
3139 select 'Y'
3140 into x_rma_found
3141 from csd_product_transactions cpt,
3142 cs_estimate_details ced
3143 where cpt.repair_line_id = p_repair_line_id and
3144 cpt.action_type = 'RMA' and
3145 cpt.action_code = 'LOANER' and
3146 cpt.prod_txn_status <> 'RECEIVED' and
3147 cpt.estimate_detail_id = ced.estimate_detail_id and
3148 nvl(cpt.interface_to_om_flag,'N') = 'N' and
3149 rownum < 2;
3150 exception
3151 when no_data_found then
3152 x_rma_found := 'N';
3153 return;
3154 end;
3155 end if;
3156 end if;
3157 -- check whether the found RMA matches the bulk receive quantity and SR and Item.
3158
3159 for l_counter in 1 ..l_matching_rma_tbl.COUNT
3160 loop
3161 IF p_blk_rec_serial_number is not null then
3162 IF l_matching_rma_tbl(l_counter).source_serial_number = p_blk_rec_serial_number THEN
3163 x_rma_found := 'Y';
3164 x_order_header_id := l_matching_rma_tbl(l_counter).header_id;
3165 x_order_line_id := l_matching_rma_tbl(l_counter).line_id;
3166 ELSE
3167 x_rma_found := 'N';
3168 END IF;
3169 ELSIF p_blk_rec_serial_number is NULL then
3170 IF l_matching_rma_tbl(l_counter).rma_quantity = p_blk_rec_qty then
3171 x_rma_found := 'Y';
3172 x_order_header_id := l_matching_rma_tbl(l_counter).header_id;
3173 x_order_line_id := l_matching_rma_tbl(l_counter).line_id;
3174 elsif l_matching_rma_tbl(l_counter).rma_quantity > p_blk_rec_qty then
3175 x_split_rma := 'Y';
3176 x_order_header_id := l_matching_rma_tbl(l_counter).header_id;
3177 x_order_line_id := l_matching_rma_tbl(l_counter).line_id;
3178 x_split_rma_qty := l_matching_rma_tbl(l_counter).rma_quantity - p_blk_rec_qty ;
3179 elsif l_matching_rma_tbl(l_counter).rma_quantity < p_blk_rec_qty then
3180 x_new_rma := 'Y';
3181 x_order_header_id := l_matching_rma_tbl(l_counter).header_id;
3182 x_order_line_id := l_matching_rma_tbl(l_counter).line_id;
3183 x_new_rma_qty := p_blk_rec_qty - l_matching_rma_tbl(l_counter).rma_quantity;
3184 else
3185 x_rma_found := 'N';
3186 end if;
3187 END IF;
3188 end loop;
3189
3190 end matching_rma_found;
3191
3192 PROCEDURE create_new_rma
3193 (
3194 p_api_version IN NUMBER DEFAULT 1,
3195 p_init_msg_list IN VARCHAR2 DEFAULT 'F',
3196 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3197 p_order_header_id IN NUMBER,
3198 p_new_rma_qty IN NUMBER,
3199 p_repair_line_id IN NUMBER,
3200 p_incident_id IN NUMBER,
3201 p_rma_quantity IN NUMBER,
3202 x_msg_count OUT NOCOPY NUMBER,
3203 x_msg_data OUT NOCOPY VARCHAR2,
3204 x_return_status OUT NOCOPY VARCHAR2,
3205 x_order_line_id OUT NOCOPY NUMBER,
3206 x_order_header_id OUT NOCOPY NUMBER
3207 ) IS
3208
3209 l_prod_txn_rec CSD_PROCESS_PVT.PRODUCT_TXN_REC;
3210 l_add_to_order boolean default false;
3211 l_add_to_order_ro varchar2(2) default fnd_profile.value('CSD_ADD_TO_SO_WITHIN_RO');
3212 l_add_to_order_sr varchar2(2) default fnd_profile.value('CSD_ADD_TO_SO_WITHIN_SR');
3213
3214 l_add_to_order_id number default p_order_header_id;
3215
3216 BEGIN
3217 -- establish the savepoint.
3218 savepoint create_new_rma;
3219 -- get the default values from the existing RMA line
3220
3221 l_prod_txn_rec.quantity := p_new_rma_qty;
3222 l_prod_txn_rec.process_txn_flag := 'Y';
3223 if l_add_to_order_ro = 'Y' then
3224 l_add_to_order := true;
3225 elsif l_add_to_order_sr = 'Y' and NOT(l_add_to_order) then
3226 l_add_to_order := true;
3227 end if;
3228 if l_add_to_order then
3229 l_prod_txn_rec.order_header_id := l_add_to_order_id;
3230 l_prod_txn_rec.add_to_order_id := l_add_to_order_id;
3231 l_prod_txn_rec.add_to_order_flag := 'T';
3232 l_prod_txn_rec.new_order_flag := 'N';
3233 else
3234 l_prod_txn_rec.order_header_id := null;
3235 l_prod_txn_rec.add_to_order_id := null;
3236 l_prod_txn_rec.add_to_order_flag := 'F';
3237 l_prod_txn_rec.new_order_flag := 'Y';
3238 end if;
3239 l_prod_txn_rec.interface_to_om_flag := 'Y';
3240 l_prod_txn_rec.book_sales_order_flag := 'Y';
3241 l_prod_txn_rec.repair_line_id := p_repair_line_id;
3242 l_prod_txn_rec.organization_id := csd_process_util.get_org_id(p_incident_id);
3243 l_prod_txn_rec.inventory_org_id := csd_process_util.get_inv_org_id;
3244
3245 begin
3246 --bug#8585307, subhat, include contract_line_id
3247
3248 select cpt.action_type,cpt.action_code,
3249 cpt.picking_rule_id,cpt.project_id,
3250 cpt.task_id,cpt.unit_number,
3251 ced.inventory_item_id,ced.unit_of_measure_code,
3252 ced.contract_id,ced.coverage_id,
3253 ced.price_list_header_id,ced.txn_billing_type_id,
3254 ced.business_process_id,ced.currency_code,
3255 ced.ship_to_party_id,ced.ship_to_account_id,
3256 ced.return_reason_code,ced.contract_line_id
3257 into l_prod_txn_rec.action_type,l_prod_txn_rec.action_code,
3258 l_prod_txn_rec.picking_rule_id,l_prod_txn_rec.project_id,
3259 l_prod_txn_rec.task_id,l_prod_txn_rec.unit_number,
3260 l_prod_txn_rec.inventory_item_id,l_prod_txn_rec.unit_of_measure_code,
3261 l_prod_txn_rec.contract_id,l_prod_txn_rec.coverage_id,
3262 l_prod_txn_rec.price_list_id,l_prod_txn_rec.txn_billing_type_id,
3263 l_prod_txn_rec.business_process_id,l_prod_txn_rec.currency_code,
3264 l_prod_txn_rec.ship_to_party_id,l_prod_txn_rec.ship_to_account_id,
3265 l_prod_txn_rec.return_reason,l_prod_txn_rec.contract_line_id
3266 from csd_product_transactions cpt,
3267 cs_estimate_details ced
3268 where cpt.repair_line_id = p_repair_line_id and
3269 ced.estimate_detail_id = cpt.estimate_detail_id and
3270 cpt.action_type IN ('RMA','RMA_THIRD_PARTY') and
3271 abs(ced.quantity_required) = p_rma_quantity
3272 and rownum < 2;
3273 exception
3274 when no_data_found then
3275 -- the program cannot find the existing RMA line. Cannot get in here.
3276 NULL;
3277 end;
3278 l_prod_txn_rec.bill_to_party_id := l_prod_txn_rec.ship_to_party_id;
3279 l_prod_txn_rec.bill_to_account_id := l_prod_txn_rec.ship_to_account_id;
3280 -- before calling the product transactions api, need to update the repair order quantity.
3281 -- otherwise the quantity validations will fail in the prod txn api.
3282
3283 update csd_repairs
3284 set quantity = (p_rma_quantity + p_new_rma_qty)
3285 where repair_line_id = p_repair_line_id
3286 and quantity = p_rma_quantity;
3287
3288 -- call the routine to create the additional RMA.
3289 csd_process_pvt.create_product_txn(
3290 p_api_version => 1.0,
3291 p_commit => 'F',
3292 p_init_msg_list => 'T',
3293 p_validation_level => 100,
3294 x_product_txn_rec => l_prod_txn_rec,
3295 x_return_status => x_return_status,
3296 x_msg_data => x_msg_data,
3297 x_msg_count => x_msg_count
3298 );
3299
3300 if x_return_status <> FND_API.g_ret_sts_success then
3301 -- to do.
3302 -- Need to put a note with the error message.
3303 raise FND_API.G_EXC_ERROR;
3304 end if;
3305 -- bug#8599965, get the order header from DB.
3306 -- x_order_header_id := l_prod_txn_rec.order_header_id;
3307 -- fetch order line id into the l_order_line_id variable.
3308 begin
3309 select ced.order_line_id,
3310 ced.order_header_id
3311 into x_order_line_id,
3312 x_order_header_id
3313 from cs_estimate_details ced,csd_product_transactions cpt
3314 where cpt.product_transaction_id = l_prod_txn_rec.product_transaction_id
3315 and ced.estimate_detail_id = cpt.estimate_detail_id;
3316 exception
3317 when no_data_found then
3318 null;
3319 end;
3320 EXCEPTION
3321 when FND_API.G_EXC_ERROR THEN
3322 -- an error occured during creation of new RMA.
3323 ROLLBACK TO create_new_rma;
3324 when others then
3325 ROLLBACK TO create_new_rma;
3326
3327 END create_new_rma;
3328
3329 PROCEDURE link_sr_ro_rma_oa_wrapper(
3330 p_bulk_rcv_dtls_tbl IN VARCHAR2_TABLE_200,
3331 p_mode IN VARCHAR2,
3332 p_incident_id IN NUMBER DEFAULT NULL,
3333 x_repair_line_id OUT NOCOPY JTF_NUMBER_TABLE,
3334 x_incident_id OUT NOCOPY JTF_NUMBER_TABLE,
3335 x_unplanned_receipt_flag OUT NOCOPY VARCHAR2_TABLE_100,
3336 x_over_receipt_flag OUT NOCOPY VARCHAR2_TABLE_100,
3337 x_under_receipt_flag OUT NOCOPY VARCHAR2_TABLE_100,
3338 x_order_header_id OUT NOCOPY JTF_NUMBER_TABLE,
3339 x_order_line_id OUT NOCOPY JTF_NUMBER_TABLE,
3340 x_over_receipt_qty OUT NOCOPY JTF_NUMBER_TABLE,
3341 x_under_receipt_qty OUT NOCOPY JTF_NUMBER_TABLE
3342 ) IS
3343
3344 x_sr_ro_rma_tbl SR_RO_RMA_TBL;
3345 l_sr_ro_rma_tbl SR_RO_RMA_TBL;
3346 l_bulk_rcv_dtls_tbl VARCHAR2_TABLE_200 := p_bulk_rcv_dtls_tbl;
3347 l_count number;
3348 l_bulk_receive_ids varchar2(2000);
3349
3350 begin
3351 l_count := l_bulk_rcv_dtls_tbl.COUNT;
3352 -- initialize the out collection types;
3353 x_repair_line_id := JTF_NUMBER_TABLE() ;
3354 x_incident_id := JTF_NUMBER_TABLE();
3355 x_unplanned_receipt_flag := VARCHAR2_TABLE_100();
3356 x_over_receipt_flag := VARCHAR2_TABLE_100();
3357 x_under_receipt_flag := VARCHAR2_TABLE_100();
3358 x_order_header_id := JTF_NUMBER_TABLE();
3359 x_order_line_id := JTF_NUMBER_TABLE();
3360 x_under_receipt_qty := JTF_NUMBER_TABLE();
3361 x_over_receipt_qty := JTF_NUMBER_TABLE();
3362
3363 for i in 1 ..l_count
3364 loop
3365 -- extend the collections
3366 x_repair_line_id.extend;
3367 x_incident_id.extend;
3368 x_unplanned_receipt_flag.extend;
3369 x_over_receipt_flag.extend;
3370 x_under_receipt_flag.extend;
3371 x_order_header_id.extend;
3372 x_order_line_id.extend;
3373 x_under_receipt_qty.extend;
3374 x_over_receipt_qty.extend;
3375
3376 -- derive the values using the input rec.
3377 x_sr_ro_rma_tbl(i).bulk_receive_id := to_number(split_varchar2_tbl(l_bulk_rcv_dtls_tbl(i),':'));
3378 x_sr_ro_rma_tbl(i).serial_number := split_varchar2_tbl(l_bulk_rcv_dtls_tbl(i),':');
3379 x_sr_ro_rma_tbl(i).inventory_item_id := to_number(split_varchar2_tbl(l_bulk_rcv_dtls_tbl(i),':'));
3380 x_sr_ro_rma_tbl(i).instance_id := to_number(split_varchar2_tbl(l_bulk_rcv_dtls_tbl(i),':'));
3381 x_sr_ro_rma_tbl(i).quantity := to_number(split_varchar2_tbl(l_bulk_rcv_dtls_tbl(i),':'));
3382 x_sr_ro_rma_tbl(i).party_id := to_number(split_varchar2_tbl(l_bulk_rcv_dtls_tbl(i),':'));
3383 x_sr_ro_rma_tbl(i).cust_acct_id := to_number(split_varchar2_tbl(l_bulk_rcv_dtls_tbl(i),':'));
3384 x_sr_ro_rma_tbl(i).rev_control_flag := split_varchar2_tbl(l_bulk_rcv_dtls_tbl(i),':');
3385 x_sr_ro_rma_tbl(i).revision := l_bulk_rcv_dtls_tbl(i);
3386 x_sr_ro_rma_tbl(i).ui_incident_id := p_incident_id;
3387
3388 l_bulk_receive_ids := x_sr_ro_rma_tbl(i).bulk_receive_id||',';
3389
3390 --dbms_output.put_line(x_sr_ro_rma_tbl(i).bulk_receive_id||x_sr_ro_rma_tbl(i).serial_number||
3391 -- x_sr_ro_rma_tbl(i).instance_id||x_sr_ro_rma_tbl(i).quantity||x_sr_ro_rma_tbl(i).party_id||x_sr_ro_rma_tbl(i).cust_acct_id);
3392 -- need to populate the serial_control_flag in the rec.
3393 if g_serial_control_flag.exists(x_sr_ro_rma_tbl(i).inventory_item_id) then
3394 x_sr_ro_rma_tbl(i).serial_control_flag := g_serial_control_flag(x_sr_ro_rma_tbl(i).inventory_item_id);
3395 else
3396 begin
3397 select serial_number_control_code
3398 into x_sr_ro_rma_tbl(i).serial_control_flag
3399 from mtl_system_items_b
3400 where inventory_item_id = x_sr_ro_rma_tbl(i).inventory_item_id
3401 and organization_id = FND_PROFILE.VALUE('ORG_ID');
3402 -- cache the serial flag for this inventory item id.
3403 g_serial_control_flag(x_sr_ro_rma_tbl(i).inventory_item_id) := x_sr_ro_rma_tbl(i).serial_control_flag;
3404 exception
3405 when no_data_found then
3406 null;
3407 end;
3408 end if;
3409 end loop;
3410 -- if its update mode and the record was unchanged then no need to re-run
3411 -- matching for them
3412 if p_mode = 'UPDATE' then
3413 l_count := 1;
3414 for k in (select bulk_receive_id,serial_number,inventory_item_id,quantity
3415 from csd_bulk_receive_items_b where bulk_receive_id in
3416 (select * from table(cast(get_num_in_list(l_bulk_receive_ids)as JTF_NUMBER_TABLE)) ) )
3417 LOOP
3418 if (k.bulk_receive_id = x_sr_ro_rma_tbl(l_count).bulk_receive_id and
3419 nvl(k.serial_number,1) = nvl(x_sr_ro_rma_tbl(l_count).serial_number,1) and
3420 k.inventory_item_id = x_sr_ro_rma_tbl(l_count).inventory_item_id and
3421 k.quantity = x_sr_ro_rma_tbl(l_count).quantity) THEN
3422
3423 -- in the warning or unplanned receipts UI, no data was changed.
3424 -- no need to call the matching program for this rec.
3425 x_sr_ro_rma_tbl.DELETE(l_count);
3426 l_count := l_count+1;
3427 else
3428 l_count := l_count+1;
3429 end if;
3430 END LOOP;
3431 end if;
3432 -- call the find sr,ro routine to link to existing sr,ro's if any.
3433 if x_sr_ro_rma_tbl.count >= 1 then
3434 get_sr_ro_rma_details(1,x_sr_ro_rma_tbl);
3435 end if;
3436 -- call the match RMA procedure to find the matching RMA's for the RO,RMA's.
3437
3438 for j in 1 ..x_sr_ro_rma_tbl.count
3439 loop
3440
3441 if x_sr_ro_rma_tbl(j).incident_id is not null and x_sr_ro_rma_tbl(j).repair_line_id is not null
3442 then
3443 -- call the procedure to find the matching RMA.
3444 csd_bulk_receive_util.matching_rma_found(
3445 p_repair_line_id => x_sr_ro_rma_tbl(j).repair_line_id,
3446 p_blk_rec_qty => x_sr_ro_rma_tbl(j).quantity,
3447 p_blk_rec_serial_number => x_sr_ro_rma_tbl(j).serial_number,
3448 p_blk_rec_instance_id => x_sr_ro_rma_tbl(j).instance_id,
3449 p_blk_rec_inv_id => x_sr_ro_rma_tbl(j).inventory_item_id,
3450 x_rma_found => x_sr_ro_rma_tbl(j).found_rma_flag,
3451 x_new_rma => x_sr_ro_rma_tbl(j).new_rma,
3452 x_split_rma_qty => x_sr_ro_rma_tbl(j).split_rma_qty,
3453 x_new_rma_qty => x_sr_ro_rma_tbl(j).new_rma_qty,
3454 x_split_rma => x_sr_ro_rma_tbl(j).split_rma,
3455 x_order_header_id => x_sr_ro_rma_tbl(j).order_header_id,
3456 x_order_line_id => x_sr_ro_rma_tbl(j).order_line_id
3457 );
3458 IF NVL(x_sr_ro_rma_tbl(j).found_rma_flag,'N') = 'Y' THEN
3459 if x_sr_ro_rma_tbl(j).order_header_id is null then
3460 -- there is a line which is not yet booked or submitted to OM.
3461 x_unplanned_receipt_flag(j) := 'N';
3462 else
3463 x_order_header_id(j) := x_sr_ro_rma_tbl(j).order_header_id;
3464 x_order_line_id(j) := x_sr_ro_rma_tbl(j).order_line_id;
3465 x_unplanned_receipt_flag(j) := 'N';
3466 end if;
3467
3468 -- over-receipt.
3469 ELSIF NVL(x_sr_ro_rma_tbl(j).new_rma,'N') = 'Y' THEN
3470 x_over_receipt_flag(j) := 'Y';
3471 x_order_header_id(j) := x_sr_ro_rma_tbl(j).order_header_id;
3472 x_order_line_id(j) := x_sr_ro_rma_tbl(j).order_line_id;
3473 x_over_receipt_qty(j) := x_sr_ro_rma_tbl(j).new_rma_qty;
3474 -- under-receipt. Split the RMA.
3475 ELSIF NVL(x_sr_ro_rma_tbl(j).split_rma,'N') = 'Y' THEN
3476 x_under_receipt_flag(j) := 'Y';
3477 x_order_header_id(j) := x_sr_ro_rma_tbl(j).order_header_id;
3478 x_order_line_id(j) := x_sr_ro_rma_tbl(j).order_line_id;
3479 x_under_receipt_qty(j) := x_sr_ro_rma_tbl(j).split_rma_qty;
3480
3481 -- no rma found. Need to create one.
3482 ELSE
3483 x_unplanned_receipt_flag(j) := 'Y';
3484 END IF;
3485 elsif x_sr_ro_rma_tbl(j).incident_id is not null and x_sr_ro_rma_tbl(j).repair_line_id is null then
3486 x_unplanned_receipt_flag(j) := 'Y';
3487 else
3488 x_unplanned_receipt_flag(j) := 'Y';
3489 end if;
3490 x_repair_line_id(j) := x_sr_ro_rma_tbl(j).repair_line_id;
3491 x_incident_id(j) := x_sr_ro_rma_tbl(j).incident_id;
3492 end loop;
3493 end link_sr_ro_rma_oa_wrapper;
3494
3495 /* ************************************************************************************/
3496 /* Procedure Name: after_receipt. */
3497 /* Description: Performs the action after the PO Receipt concurrent program is finished*/
3498 /* Not a public API. Intended to be used by Bulk Receive conc program only*/
3499 /* params: @p_request_group_id: Group Id for receipts submitted. */
3500 /* @p_transaction_number: Bulk Receive Transaction Number. */
3501 /* ************************************************************************************/
3502
3503 procedure after_receipt(p_request_group_id IN NUMBER,
3504 p_transaction_number IN NUMBER
3505 )
3506 IS
3507
3508 Cursor c_ro_prodtxn(p_repair_line_id number,
3509 p_order_header_id number,
3510 p_order_line_id number) is
3511 select
3512 cib.customer_id,
3513 cib.account_id,
3514 cpt.estimate_quantity,
3515 cpt.unit_of_measure,
3516 cpt.inventory_item_id,
3517 cpt.order_header_id,
3518 cpt.order_line_id,
3519 cpt.order_number,
3520 cpt.serial_number,
3521 mtl.concatenated_segments item_name,
3522 cpt.revision,
3523 cpt.lot_number
3524 from
3525 csd_product_txns_v cpt,
3526 cs_incidents_all_b cib,
3527 csd_repairs cr,
3528 mtl_system_items_kfv mtl
3529 where cpt.repair_line_id = p_repair_line_id
3530 and cr.repair_line_id = cpt.repair_line_id
3531 and cib.incident_id = cr.incident_id
3532 and cpt.order_header_id = p_order_header_id
3533 and cpt.order_line_id = p_order_line_id
3534 and mtl.inventory_item_id = cpt.inventory_item_id
3535 and mtl.organization_id = cs_std.get_item_valdn_orgzn_id;
3536
3537 Cursor c_get_org (p_order_header_id number) is
3538 Select nvl(b.ship_from_org_id,a.ship_from_org_id)
3539 from oe_order_headers_all a,
3540 oe_order_lines_all b
3541 where a.header_id = b.header_id
3542 and a.header_id = p_order_header_id;
3543
3544 Cursor c_get_prod_txn_status ( p_repair_line_id number ) is
3545 Select prod_txn_status
3546 from csd_product_transactions
3547 where repair_line_id = p_repair_line_id
3548 and action_type = 'RMA';
3549
3550 l_rcv_error_msg_tbl csd_receive_util.rcv_error_msg_tbl;
3551 l_header_error boolean;
3552
3553 l_bulk_autorcv_tbl csd_bulk_receive_util.BULK_AUTORCV_TBL;
3554
3555 l_msg_count number;
3556 l_msg_data varchar2(2000);
3557 l_return_status varchar2(3);
3558 l_errored boolean;
3559 l_prod_txn_status varchar2(30);
3560
3561 lc_api_name CONSTANT varchar2(100) := 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.after_receipt';
3562
3563 begin
3564
3565 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3566 fnd_log.STRING (fnd_log.level_procedure,
3567 lc_api_name,
3568 'Begin execution, populating bulk receive rec');
3569 End if;
3570 -- build bulk autorecv tbl.
3571 select cbr.bulk_receive_id,
3572 cbr.repair_line_id,
3573 ced.order_line_id,
3574 ced.order_header_id,
3575 cbr.under_receipt_flag,
3576 cbr.quantity,
3577 null,
3578 null,
3579 null,
3580 null,
3581 cbr.serial_number,
3582 cbr.rcv_attribute_category,
3583 cbr.rcv_attribute1,
3584 cbr.rcv_attribute2,
3585 cbr.rcv_attribute3,
3586 cbr.rcv_attribute4,
3587 cbr.rcv_attribute5,
3588 cbr.rcv_attribute6,
3589 cbr.rcv_attribute7,
3590 cbr.rcv_attribute8,
3591 cbr.rcv_attribute9,
3592 cbr.rcv_attribute10,
3593 cbr.rcv_attribute11,
3594 cbr.rcv_attribute12,
3595 cbr.rcv_attribute13,
3596 cbr.rcv_attribute14,
3597 cbr.rcv_attribute15
3598 bulk collect into
3599 l_bulk_autorcv_tbl
3600 from csd_bulk_receive_items_b cbr,
3601 csd_product_transactions cpt,
3602 cs_estimate_details ced
3603 where cbr.transaction_number = p_transaction_number
3604 and cbr.repair_line_id = cpt.repair_line_id
3605 and cpt.action_type = 'RMA'
3606 and cpt.estimate_detail_id = ced.estimate_detail_id;
3607
3608 -- get the errors message table.
3609
3610 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3611 fnd_log.STRING (fnd_log.level_procedure,
3612 lc_api_name,
3613 'Calling csd_receive_util.check_rcv_errors');
3614 End if;
3615 csd_receive_util.check_rcv_errors(x_return_status => l_return_Status,
3616 x_rcv_error_msg_tbl => l_rcv_error_msg_tbl,
3617 p_request_group_id => p_request_group_id
3618 );
3619
3620 If ( l_rcv_error_msg_tbl.count > 0 ) then
3621
3622 l_header_error := FALSE;
3623
3624 For i in 1..l_rcv_error_msg_tbl.count
3625 Loop
3626 If ( l_rcv_error_msg_tbl(i).header_interface_id is not null and
3627 l_rcv_error_msg_tbl(i).interface_transaction_id is null ) then
3628
3629 l_header_error := TRUE;
3630
3631 -- Display the message
3632 Fnd_file.put_line(fnd_file.log,'Error:Auto Receive failed - Header');
3633 Fnd_file.put(fnd_file.log,'Column name:');
3634 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).column_name);
3635 Fnd_file.put(fnd_file.log,'Error Message:');
3636 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).error_message);
3637
3638 End if;
3639 End Loop;
3640
3641 -- If there is header error the update all the Auto Receive lines
3642 -- in Bulk Rcv table to Errored
3643 If (l_header_error) then
3644 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3645 fnd_log.STRING (fnd_log.level_procedure,
3646 lc_api_name,
3647 'Header error, updating all the records to errored ');
3648 End if;
3649 -- Update all the auto receive records to error
3650 Update csd_bulk_receive_items_b
3651 set status = 'ERRORED'
3652 where transaction_number = p_transaction_number;
3653
3654
3655 Else
3656
3657 -- If there are no header errors then check for
3658 -- line errors and update the records
3659 For i in 1..l_bulk_autorcv_tbl.count
3660 Loop
3661
3662 l_errored := FALSE;
3663
3664 For j in 1..l_rcv_error_msg_tbl.count
3665 Loop
3666
3667 If ( l_bulk_autorcv_tbl(i).order_header_id = l_rcv_error_msg_tbl(j).order_header_id and
3668 l_bulk_autorcv_tbl(i).order_line_id = l_rcv_error_msg_tbl(j).order_line_id ) then
3669
3670 l_errored := TRUE;
3671
3672 -- Display the error message
3673
3674 /*l_customer_id := null;
3675 l_estimate_quantity := null;
3676 l_unit_of_measure := null;
3677 l_inventory_item_id := null;
3678 l_order_header_id := null;
3679 l_order_line_id := null;
3680 l_order_number := null;
3681 l_serial_number := null;
3682 l_item_name := null;
3683
3684 Open c_ro_prodtxn(p_bulk_autorcv_tbl(i).repair_line_id,
3685 p_bulk_autorcv_tbl(i).order_header_id,
3686 p_bulk_autorcv_tbl(i).order_line_id);
3687
3688 -- 12.0 changes, subhat, added lot number and item revision
3689 Fetch c_ro_prodtxn into l_customer_id,l_account_id,l_estimate_quantity,
3690 l_unit_of_measure,l_inventory_item_id,l_order_header_id,
3691 l_order_line_id,l_order_number,l_serial_number,l_item_name,l_item_revision,l_lot_number;
3692 Close c_ro_prodtxn;
3693
3694 Fnd_file.put_line(fnd_file.log,'Error:Auto Receive failed - Line');
3695 Fnd_file.put(fnd_file.log,'Serial Number :'||l_serial_number||',');
3696 Fnd_file.put(fnd_file.log,'Inventory Item :'||l_item_name||',');
3697 Fnd_file.put_line(fnd_file.log,'Qty :'||l_estimate_quantity);
3698 Fnd_file.put(fnd_file.log,'Column name:');
3699 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).column_name);
3700 Fnd_file.put(fnd_file.log,'Error Message:');
3701 Fnd_file.put_line(fnd_file.log,l_rcv_error_msg_tbl(i).error_message); */
3702
3703 End If;
3704
3705 End Loop;
3706
3707 If (l_errored) then
3708
3709 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3710 fnd_log.STRING (fnd_log.level_procedure,
3711 lc_api_name,
3712 'Bulk receive line ['||l_bulk_autorcv_tbl(i).bulk_receive_id||' ] is errored');
3713 End if;
3714 Update csd_bulk_receive_items_b
3715 set status = 'ERRORED'
3716 where bulk_receive_id = l_bulk_autorcv_tbl(i).bulk_receive_id;
3717
3718 Else
3719
3720 if nvl(l_bulk_autorcv_tbl(i).under_receipt_flag,'N') = 'Y' then
3721 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3722 fnd_log.STRING (fnd_log.level_procedure,
3723 lc_api_name,
3724 'Calling after_under_receipt_prcs for processing under-receipts');
3725 End if;
3726 after_under_receipt_prcs(p_repair_line_id => l_bulk_autorcv_tbl(i).repair_line_id,
3727 p_order_header_id => l_bulk_autorcv_tbl(i).order_header_id,
3728 p_order_line_id => l_bulk_autorcv_tbl(i).order_line_id,
3729 p_received_qty => l_bulk_autorcv_tbl(i).receipt_qty
3730 );
3731 end if;
3732
3733 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3734 fnd_log.STRING (fnd_log.level_procedure,
3735 lc_api_name,
3736 'Calling CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE');
3737 End if;
3738 -- Call Update receipts program
3739 CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
3740 ( p_api_version => 1.0,
3741 p_commit => fnd_api.g_false,
3742 p_init_msg_list => fnd_api.g_true,
3743 p_validation_level => 0,
3744 x_return_status => l_return_status,
3745 x_msg_count => l_msg_count,
3746 x_msg_data => l_msg_data,
3747 p_internal_order_flag => 'N',
3748 p_order_header_id => null,
3749 p_repair_line_id => l_bulk_autorcv_tbl(i).repair_line_id);
3750
3751
3752 If NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) then
3753 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3754 fnd_log.STRING (fnd_log.level_procedure,
3755 lc_api_name,
3756 'Error in CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE: Return status '||l_return_status);
3757 End if;
3758
3759 Fnd_file.put_line(fnd_file.log,'Error : CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE failed');
3760 csd_bulk_receive_util.write_to_conc_log
3761 ( p_msg_count => l_msg_count,
3762 p_msg_data => l_msg_data);
3763
3764 Else
3765
3766 -- Get Product Txn Status
3767 Open c_get_prod_txn_status ( l_bulk_autorcv_tbl(i).repair_line_id );
3768 Fetch c_get_prod_txn_status into l_prod_txn_status;
3769 Close c_get_prod_txn_status;
3770
3771 If ( l_prod_txn_status = 'RECEIVED' ) then
3772
3773 Update csd_bulk_receive_items_b
3774 set status = 'PROCESSED'
3775 where bulk_receive_id = l_bulk_autorcv_tbl(i).bulk_receive_id;
3776
3777 End if;
3778
3779 End if;
3780
3781 End if;
3782 End Loop;
3783
3784 End if; -- End if of l_header_error
3785
3786 end if; -- end if of check for errors.
3787 -- Update all the auto receive records to processed
3788 For i in 1..l_bulk_autorcv_tbl.count
3789 Loop
3790
3791 -- process under receipts.
3792 if nvl(l_bulk_autorcv_tbl(i).under_receipt_flag,'N') = 'Y' then
3793 after_under_receipt_prcs(p_repair_line_id => l_bulk_autorcv_tbl(i).repair_line_id,
3794 p_order_header_id => l_bulk_autorcv_tbl(i).order_header_id,
3795 p_order_line_id => l_bulk_autorcv_tbl(i).order_line_id,
3796 p_received_qty => l_bulk_autorcv_tbl(i).receipt_qty);
3797 end if;
3798
3799
3800 -- Call Update receipts program
3801 CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
3802 ( p_api_version => 1.0,
3803 p_commit => fnd_api.g_false,
3804 p_init_msg_list => fnd_api.g_true,
3805 p_validation_level => 0,
3806 x_return_status => l_return_status,
3807 x_msg_count => l_msg_count,
3808 x_msg_data => l_msg_data,
3809 p_internal_order_flag => 'N',
3810 p_order_header_id => null,
3811 p_repair_line_id => l_bulk_autorcv_tbl(i).repair_line_id);
3812
3813
3814 If NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) then
3815 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3816 fnd_log.STRING (fnd_log.level_procedure,
3817 lc_api_name,
3818 'Error in CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE: Return status '||l_return_status);
3819 End if;
3820
3821 Fnd_file.put_line(fnd_file.log,'Error : CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE failed');
3822 csd_bulk_receive_util.write_to_conc_log
3823 ( p_msg_count => l_msg_count,
3824 p_msg_data => l_msg_data);
3825
3826 Else
3827
3828 -- Get Product Txn Status
3829 Open c_get_prod_txn_status ( l_bulk_autorcv_tbl(i).repair_line_id );
3830 Fetch c_get_prod_txn_status into l_prod_txn_status;
3831 Close c_get_prod_txn_status;
3832
3833 If ( l_prod_txn_status = 'RECEIVED' ) then
3834 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3835 fnd_log.STRING (fnd_log.level_procedure,
3836 lc_api_name,
3837 'No errors during receiving,update the line as processed: id = '||l_bulk_autorcv_tbl(i).bulk_receive_id);
3838 End if;
3839
3840 Update csd_bulk_receive_items_b
3841 set status = 'PROCESSED'
3842 where bulk_receive_id = l_bulk_autorcv_tbl(i).bulk_receive_id;
3843
3844 End if;
3845
3846 End if;
3847
3848 End loop;
3849
3850 -- bug#9058678. We skipped the SN's that failed validation during validation.
3851 -- those records would be stuck in NEW status.
3852 -- We need to make sure that the records, which were never passed to receiving
3853 -- interface are marked as error too (since they failed validation).
3854
3855 update csd_bulk_receive_items_b
3856 set status = 'ERRORED'
3857 where transaction_number = p_transaction_number
3858 and status = 'NEW';
3859
3860 /* End if; -- End if of the Status check
3861
3862 End if; -- End if of the l_rcv_rec_tbl.count > 0 */
3863
3864 end after_receipt;
3865
3866 /* ***************************************************************************/
3867 /* Procedure Name: pre_process_rma. */
3868 /* Description: Checks if the RMA is ready to be received. If the RMA is in */
3869 /* SUBMITTED status, books the RMA and if the RMA is in ENTERED */
3870 /* status, then it submits the RMA to OM and books it. */
3871 /* ***************************************************************************/
3872
3873 procedure pre_process_rma (p_repair_line_id IN NUMBER,
3874 px_order_header_id IN OUT NOCOPY NUMBER,
3875 px_order_line_id IN OUT NOCOPY NUMBER,
3876 x_return_status OUT NOCOPY VARCHAR2,
3877 x_msg_count OUT NOCOPY NUMBER,
3878 x_msg_data OUT NOCOPY VARCHAR2
3879 )
3880 IS
3881
3882 l_rma_status varchar2(30);
3883 l_book_order_flag varchar2(3) := 'N';
3884 l_prod_txn_rec csd_process_pvt.product_txn_rec;
3885 lc_api_name varchar2(60) := 'CSD.PLSQL.CSD_BULK_RECEIVE_UTIL.PRE_PROCESS_RMA';
3886 l_order_header_id number := px_order_header_id;
3887 l_order_line_id number := px_order_line_id;
3888 l_check_loaner varchar2(2) := 'N';
3889
3890 l_add_to_order boolean := false;
3891 l_add_to_order_ro varchar2(2) := fnd_profile.value('CSD_ADD_TO_SO_WITHIN_RO');
3892 l_add_to_order_sr varchar2(2) := fnd_profile.value('CSD_ADD_TO_SO_WITHIN_SR');
3893
3894 begin
3895
3896 savepoint pre_process_rma;
3897
3898 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3899 fnd_log.STRING (fnd_log.level_procedure,
3900 lc_api_name,
3901 'Begin pre_process_rma');
3902 End if;
3903 -- initialize the return status.
3904 x_return_status := fnd_api.g_ret_sts_success;
3905
3906 if l_order_header_id is not null and l_order_line_id is not null then
3907 select cpt.prod_txn_status
3908 into l_rma_status
3909 from csd_product_transactions cpt,
3910 cs_estimate_details ced
3911 where cpt.repair_line_id = p_repair_line_id
3912 and cpt.estimate_detail_id = ced.estimate_detail_id
3913 and ced.order_header_id = l_order_header_id
3914 and ced.order_line_id = l_order_line_id;
3915
3916 if l_rma_status = 'BOOKED' then
3917 return;
3918 elsif l_rma_status = 'SUBMITTED' then
3919 l_book_order_flag := 'Y';
3920 end if;
3921
3922 elsif px_order_header_id is null and p_repair_line_id is not null then
3923 l_rma_status := 'ENTERED';
3924 end if;
3925
3926 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
3927 fnd_log.STRING (fnd_log.level_procedure,
3928 lc_api_name,
3929 'Finding the existing RMA line');
3930 End if;
3931 begin
3932 select cpt.prod_txn_status,
3933 cpt.product_transaction_id,
3934 ced.estimate_detail_id,
3935 ced.inventory_item_id,
3936 ced.incident_id,
3937 ced.invoice_to_org_id,
3938 ced.ship_to_org_id,
3939 ced.org_id,
3940 ced.transaction_inventory_org,
3941 ced.business_process_id,
3942 ced.txn_billing_type_id,
3943 null bill_to_party_id,
3944 null bill_to_account_id,
3945 ced.order_header_id,
3946 ced.order_line_id,
3947 cpt.project_id,
3948 cpt.unit_number
3949 into l_prod_txn_rec.prod_txn_status,
3950 l_prod_txn_rec.product_transaction_id,
3951 l_prod_txn_rec.estimate_detail_id,
3952 l_prod_txn_rec.inventory_item_id,
3953 l_prod_txn_rec.incident_id,
3954 l_prod_txn_rec.invoice_to_org_id,
3955 l_prod_txn_rec.ship_to_org_id,
3956 l_prod_txn_rec.organization_id,
3957 l_prod_txn_rec.inventory_org_id,
3958 l_prod_txn_rec.business_process_id,
3959 l_prod_txn_rec.txn_billing_type_id,
3960 l_prod_txn_rec.bill_to_party_id,
3961 l_prod_txn_rec.bill_to_account_id,
3962 l_prod_txn_rec.order_header_id,
3963 l_prod_txn_rec.order_line_id,
3964 l_prod_txn_rec.project_id,
3965 l_prod_txn_rec.unit_number
3966 from csd_product_transactions cpt,
3967 cs_estimate_details ced
3968 where cpt.repair_line_id = p_repair_line_id and
3969 cpt.action_type = 'RMA' and
3970 cpt.action_code = 'CUST_PROD' and
3971 cpt.prod_txn_status <> 'RECEIVED' and
3972 cpt.estimate_detail_id = ced.estimate_detail_id and
3973 nvl(ced.order_header_id,-1) = nvl(l_order_header_id,-1) and
3974 nvl(ced.order_line_id,-1) = nvl(l_order_line_id,-1) and
3975 rownum < 2;
3976 exception
3977 when no_data_found then
3978 l_check_loaner := 'Y';
3979 end;
3980
3981 if l_check_loaner = 'Y' then
3982 select cpt.prod_txn_status,
3983 cpt.product_transaction_id,
3984 ced.estimate_detail_id,
3985 ced.inventory_item_id,
3986 ced.incident_id,
3987 ced.invoice_to_org_id,
3988 ced.ship_to_org_id,
3989 ced.org_id,
3990 ced.transaction_inventory_org,
3991 ced.business_process_id,
3992 ced.txn_billing_type_id,
3993 null bill_to_party_id,
3994 null bill_to_account_id,
3995 ced.order_header_id,
3996 ced.order_line_id,
3997 cpt.project_id
3998
3999 into l_prod_txn_rec.prod_txn_status,
4000 l_prod_txn_rec.product_transaction_id,
4001 l_prod_txn_rec.estimate_detail_id,
4002 l_prod_txn_rec.inventory_item_id,
4003 l_prod_txn_rec.incident_id,
4004 l_prod_txn_rec.invoice_to_org_id,
4005 l_prod_txn_rec.ship_to_org_id,
4006 l_prod_txn_rec.organization_id,
4007 l_prod_txn_rec.inventory_org_id,
4008 l_prod_txn_rec.business_process_id,
4009 l_prod_txn_rec.txn_billing_type_id,
4010 l_prod_txn_rec.bill_to_party_id,
4011 l_prod_txn_rec.bill_to_account_id,
4012 l_prod_txn_rec.order_header_id,
4013 l_prod_txn_rec.order_line_id,
4014 l_prod_txn_rec.project_id
4015 from csd_product_transactions cpt,
4016 cs_estimate_details ced
4017 where cpt.repair_line_id = p_repair_line_id and
4018 cpt.action_type = 'RMA' and
4019 cpt.action_code = 'LOANER' and
4020 cpt.prod_txn_status <> 'RECEIVED' and
4021 cpt.estimate_detail_id = ced.estimate_detail_id and
4022 nvl(ced.order_header_id,-1) = nvl(l_order_header_id,-1) and
4023 nvl(ced.order_line_id,-1) = nvl(l_order_line_id,-1) and
4024 rownum < 2;
4025 l_check_loaner := 'N';
4026 end if;
4027
4028 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
4029 fnd_log.STRING (fnd_log.level_procedure,
4030 lc_api_name,
4031 'Populating l_prod_txn_rec with the default values');
4032 End if;
4033 l_prod_txn_rec.source_id := p_repair_line_id;
4034 l_prod_txn_rec.original_source_id := p_repair_line_id;
4035 l_prod_txn_rec.repair_line_id := p_repair_line_id;
4036 l_prod_txn_rec.process_txn_flag := 'Y';
4037 l_prod_txn_rec.interface_to_om_flag := 'Y';
4038 l_prod_txn_rec.book_sales_order_flag := 'Y';
4039
4040 if l_rma_status = 'SUBMITTED' then
4041 -- the order is already interfaced to OM. Just need to Book it.
4042 l_prod_txn_rec.new_order_flag := 'N';
4043 elsif l_rma_status = 'ENTERED' then
4044
4045 if l_add_to_order_ro = 'Y' then
4046 Select max(ced.order_header_id)
4047 into l_prod_txn_rec.add_to_order_id
4048 from csd_product_transactions cpt,
4049 cs_estimate_details ced,
4050 oe_order_headers_all ooh,
4051 oe_order_types_v oot,
4052 cs_incidents_all_b sr
4053 where cpt.estimate_detail_id = ced.estimate_detail_id
4054 and cpt.repair_line_id = p_repair_line_id
4055 and ced.order_header_id is not null
4056 and ced.interface_to_oe_flag = 'Y'
4057 and ooh.open_flag = 'Y'
4058 and nvl(ooh.cancelled_flag,'N') = 'N'
4059 and ooh.header_id = ced.order_header_id
4060 and ooh.order_type_id = oot.order_type_id
4061 and ced.incident_id = sr.incident_id
4062 and ooh.sold_to_org_id = sr.account_id
4063 and oot.order_category_code in ('MIXED','RETURN');
4064 if nvl(l_prod_txn_rec.add_to_order_id,-1) > 0 then
4065 l_add_to_order := true;
4066 else
4067 l_add_to_order := false;
4068 end if;
4069 end if;
4070
4071 if l_add_to_order_sr = 'Y' and NOT(l_add_to_order) then
4072 l_prod_txn_rec.add_to_order_id := csd_process_util.Get_Sr_add_to_order
4073 (p_repair_line_id,'RMA');
4074 if nvl(l_prod_txn_rec.add_to_order_id,-1) > 0 then
4075 l_add_to_order := true;
4076 else
4077 l_add_to_order := false;
4078 end if;
4079 end if;
4080
4081 if l_add_to_order then
4082 l_prod_txn_rec.new_order_flag := 'N';
4083 l_prod_txn_rec.order_header_id := l_prod_txn_rec.add_to_order_id;
4084 l_prod_txn_rec.add_to_order_flag := 'T';
4085 else
4086 l_prod_txn_rec.new_order_flag := 'Y';
4087 end if;
4088 end if;
4089
4090 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
4091 fnd_log.STRING (fnd_log.level_procedure,
4092 lc_api_name,
4093 'Calling csd_process_pvt.update_product_txn to book the order');
4094 End if;
4095
4096 csd_process_pvt.update_product_txn
4097 ( p_api_version => 1,
4098 p_commit => FND_API.G_FALSE,
4099 p_init_msg_list => FND_API.G_TRUE,
4100 p_validation_level=> fnd_api.g_valid_level_full,
4101 x_product_txn_rec => l_prod_txn_rec,
4102 x_return_status => x_return_status,
4103 x_msg_count => x_msg_count,
4104 x_msg_data => x_msg_data
4105 );
4106 if x_return_status <> fnd_api.g_ret_sts_success then
4107 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
4108 fnd_log.STRING (fnd_log.level_procedure,
4109 lc_api_name,
4110 'Error while trying to book the existing line ');
4111 End if;
4112 raise fnd_api.g_exc_error;
4113 end if;
4114
4115 select ced.order_header_id,ced.order_line_id
4116 into px_order_header_id,px_order_line_id
4117 from csd_product_transactions cpt,
4118 cs_estimate_details ced
4119 where cpt.repair_line_id = p_repair_line_id
4120 and ced.estimate_detail_id = cpt.estimate_detail_id
4121 and ced.estimate_detail_id = l_prod_txn_rec.estimate_detail_id
4122 and cpt.action_type = 'RMA';
4123
4124 exception
4125 when fnd_api.g_exc_error then
4126 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
4127 fnd_log.STRING (fnd_log.level_procedure,
4128 lc_api_name,
4129 'In g_exc_error exception, rolling back to pre_process_rma ');
4130 End if;
4131 x_return_status := fnd_api.g_ret_sts_error;
4132 rollback to pre_process_rma;
4133 when no_data_found then
4134 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
4135 fnd_log.STRING (fnd_log.level_procedure,
4136 lc_api_name,
4137 'In no_data_found exception.');
4138 End if;
4139 x_return_status := fnd_api.g_ret_sts_error;
4140 rollback to pre_process_rma;
4141 when others then
4142 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
4143 fnd_log.STRING (fnd_log.level_procedure,
4144 lc_api_name,
4145 'In when others exception and the error message is '||SQLERRM);
4146 End if;
4147 raise;
4148 end pre_process_rma;
4149
4150 /* ***************************************************************************/
4151 /* Procedure Name: create_new_ship_line. */
4152 /* Description : Creates a new Ship line for the over-receipt quantity */
4153 /* ***************************************************************************/
4154
4155 procedure create_new_ship_line
4156 (
4157 p_api_version IN VARCHAR2,
4158 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4159 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4160 p_order_header_id IN NUMBER,
4161 p_new_ship_qty IN NUMBER,
4162 p_repair_line_id IN NUMBER,
4163 p_incident_id IN NUMBER,
4164 x_return_status OUT NOCOPY VARCHAR2,
4165 x_msg_count OUT NOCOPY NUMBER,
4166 x_msg_data OUT NOCOPY VARCHAR2
4167 )
4168 IS
4169
4170 l_prod_txn_rec CSD_PROCESS_PVT.PRODUCT_TXN_REC;
4171 lc_api_name varchar2(100) := 'CSD_BULK_RECEIVE_UTIL.create_new_ship_line';
4172
4173 BEGIN
4174 savepoint create_new_ship_line;
4175 -- get the default values from the existing SHIP line
4176 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
4177 fnd_log.STRING (fnd_log.level_procedure,
4178 lc_api_name,
4179 'Begin creating new ship line');
4180 End if;
4181
4182 l_prod_txn_rec.quantity := p_new_ship_qty;
4183 l_prod_txn_rec.process_txn_flag := 'Y';
4184 l_prod_txn_rec.new_order_flag := 'N';
4185 l_prod_txn_rec.interface_to_om_flag := 'N';
4186 l_prod_txn_rec.book_sales_order_flag := 'N';
4187 l_prod_txn_rec.repair_line_id := p_repair_line_id;
4188 l_prod_txn_rec.organization_id := csd_process_util.get_org_id(p_incident_id);
4189 l_prod_txn_rec.inventory_org_id := csd_process_util.get_inv_org_id;
4190
4191 begin
4192
4193 select cpt.action_type,cpt.action_code,
4194 cpt.picking_rule_id,cpt.project_id,
4195 cpt.task_id,cpt.unit_number,
4196 ced.inventory_item_id,ced.unit_of_measure_code,
4197 ced.contract_id,ced.coverage_id,
4198 ced.price_list_header_id,ced.txn_billing_type_id,
4199 ced.business_process_id,ced.currency_code,
4200 ced.ship_to_party_id,ced.ship_to_account_id,
4201 ced.return_reason_code,ced.contract_line_id
4202 into l_prod_txn_rec.action_type,l_prod_txn_rec.action_code,
4203 l_prod_txn_rec.picking_rule_id,l_prod_txn_rec.project_id,
4204 l_prod_txn_rec.task_id,l_prod_txn_rec.unit_number,
4205 l_prod_txn_rec.inventory_item_id,l_prod_txn_rec.unit_of_measure_code,
4206 l_prod_txn_rec.contract_id,l_prod_txn_rec.coverage_id,
4207 l_prod_txn_rec.price_list_id,l_prod_txn_rec.txn_billing_type_id,
4208 l_prod_txn_rec.business_process_id,l_prod_txn_rec.currency_code,
4209 l_prod_txn_rec.ship_to_party_id,l_prod_txn_rec.ship_to_account_id,
4210 l_prod_txn_rec.return_reason,l_prod_txn_rec.contract_line_id
4211 from csd_product_transactions cpt,
4212 cs_estimate_details ced
4213 where cpt.repair_line_id = p_repair_line_id and
4214 ced.estimate_detail_id = cpt.estimate_detail_id and
4215 cpt.action_type = 'SHIP' and
4216 rownum < 2;
4217 exception
4218 when no_data_found then
4219 -- the program cannot find the existing RMA line. Cannot get in here.
4220 NULL;
4221 end;
4222 l_prod_txn_rec.bill_to_party_id := l_prod_txn_rec.ship_to_party_id;
4223 l_prod_txn_rec.bill_to_account_id := l_prod_txn_rec.ship_to_account_id;
4224
4225 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
4226 fnd_log.STRING (fnd_log.level_procedure,
4227 lc_api_name,
4228 'Before calling csd_process_pvt.create_product_txn');
4229 End if;
4230 -- call the routine to create the additional RMA.
4231 csd_process_pvt.create_product_txn(
4232 p_api_version => 1.0,
4233 p_commit => 'F',
4234 p_init_msg_list => 'T',
4235 p_validation_level => 100,
4236 x_product_txn_rec => l_prod_txn_rec,
4237 x_return_status => x_return_status,
4238 x_msg_data => x_msg_data,
4239 x_msg_count => x_msg_count
4240 );
4241
4242 if x_return_status <> FND_API.g_ret_sts_success then
4243 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
4244 fnd_log.STRING (fnd_log.level_procedure,
4245 lc_api_name,
4246 'Error in csd_process_pvt.create_product_txn');
4247 End if;
4248 raise FND_API.G_EXC_ERROR;
4249 end if;
4250
4251 EXCEPTION
4252 when FND_API.G_EXC_ERROR THEN
4253 -- an error occured during creation of new RMA.
4254 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
4255 fnd_log.STRING (fnd_log.level_procedure,
4256 lc_api_name,
4257 'Error encountered during creation of new ship line.');
4258 fnd_log.STRING (fnd_log.level_procedure,
4259 lc_api_name,
4260 'Message count '||x_msg_count||' message '|| x_msg_data);
4261 End if;
4262 ROLLBACK TO create_new_ship_line;
4263 when others then
4264 ROLLBACK TO create_new_ship_line;
4265 END create_new_ship_line;
4266
4267 --12.2. New functions, subhat
4268 function split_varchar2_tbl (
4269 px_tbl_type IN OUT NOCOPY VARCHAR2,
4270 p_delimiter IN VARCHAR2
4271 ) RETURN VARCHAR2
4272 IS
4273
4274 l_return_value VARCHAR2(200);
4275
4276 begin
4277 l_return_value := SUBSTR(px_tbl_type,1,INSTR(px_tbl_type,p_delimiter)-1);
4278 px_tbl_type := SUBSTR(px_tbl_type,INSTR(px_tbl_type,p_delimiter)+1);
4279 return l_return_value;
4280 end split_varchar2_tbl;
4281
4282 FUNCTION get_latest_open_sr
4283 (
4284 p_account_id in NUMBER,
4285 p_party_id in NUMBER) RETURN NUMBER
4286 IS
4287 l_incident_id NUMBER;
4288
4289 begin
4290 select incident_id
4291 into l_incident_id
4292 from (
4293 select incident_id
4294 from cs_incidents_all_b
4295 where customer_id = p_party_id
4296 and account_id = p_account_id
4297 and status_flag = 'O'
4298 order by incident_id desc
4299 )
4300 where rownum = 1;
4301
4302 return l_incident_id;
4303
4304 exception
4305 when no_data_found then
4306 return l_incident_id;
4307 end get_latest_open_sr;
4308
4309 FUNCTION get_num_in_list(p_in_string IN varchar2)
4310 RETURN JTF_NUMBER_TABLE
4311 IS
4312
4313 l_in_string long default p_in_string;
4314 l_return_type JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
4315 n number;
4316 BEGIN
4317 loop
4318 exit when l_in_string is null;
4319 n := instr(l_in_string,',');
4320 l_return_type.extend;
4321 l_return_type(l_return_type.count) := ltrim(rtrim(substr(l_in_string,1,n-1)));
4322 l_in_string := substr(l_in_string,n+1);
4323 end loop;
4324 return l_return_type;
4325 END get_num_in_list;
4326
4327 END CSD_BULK_RECEIVE_UTIL;