1 PACKAGE BODY Csd_Logistics_Util AS
2 /* $Header: csdulogb.pls 120.39 2010/09/22 01:04:05 takwong ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_LOGISTICS_UTIL';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdulogs.plb';
6 g_debug NUMBER := Csd_Gen_Utility_Pvt.g_debug_level;
7
8 -- Define constants here
9 C_ACTION_TYPE_SHIP CONSTANT VARCHAR2(4) := 'SHIP';
10 C_ACTION_TYPE_RMA CONSTANT VARCHAR2(3) := 'RMA';
11 C_ACTION_TYPE_WALK_IN_ISSUE CONSTANT VARCHAR2(16) := 'WALK_IN_ISSUE';
12 C_ACTION_TYPE_WALK_IN_RECPT CONSTANT VARCHAR2(16) := 'WALK_IN_RECEIPTS';
13
14 C_ACTION_CODE_LOANER CONSTANT VARCHAR2(6) := 'LOANER';
15
16 C_PROD_TXN_STS_ENTERED CONSTANT VARCHAR2(30) := 'ENTERED';
17 C_PROD_TXN_STS_SUBMITTED CONSTANT VARCHAR2(30) := 'SUBMITTED';
18 C_PROD_TXN_STS_BOOKED CONSTANT VARCHAR2(30) := 'BOOKED';
19 C_PROD_TXN_STS_RELEASED CONSTANT VARCHAR2(30) := 'RELEASED';
20 C_PROD_TXN_STS_SHIPPED CONSTANT VARCHAR2(30) := 'SHIPPED';
21 C_PROD_TXN_STS_RECEIVED CONSTANT VARCHAR2(30) := 'RECEIVED';
22
23 C_STATUS_INSTORES CONSTANT NUMBER := 3;
24 C_STATUS_INTRANSIT CONSTANT NUMBER := 5;
25 C_STATUS_OUT_OF_STORES CONSTANT NUMBER := 4;
26
27 C_SITE_USE_TYPE_BILL_TO CONSTANT VARCHAR2(30) := 'BILL_TO';
28 C_SITE_USE_TYPE_SHIP_TO CONSTANT VARCHAR2(30) := 'SHIP_TO';
29
30 /* R12 Srl reservation changes, begin */
31 C_RESERVABLE CONSTANT NUMBER := 1;
32 C_SERIAL_CONTROL_AT_RECEIPT CONSTANT NUMBER := 5;
33 C_SERIAL_CONTROL_PREDEFINED CONSTANT NUMBER := 2;
34 /* R12 Srl reservation changes, end */
35
36 -- Global variable for storing the debug level
37 G_debug_level NUMBER := Fnd_Log.G_CURRENT_RUNTIME_LEVEL;
38
39 /*---------------------------------------------------------------------------*/
40 /* procedure name: Validate_PriceListID */
41 /* description : Validate Price List for a given Price List Id */
42 /* SU: Please comment this helper routine as this validation is */
43 /* done by charges API. */
44 /*---------------------------------------------------------------------------*/
45 -- Procedure Validate_PriceListID
46 -- ( p_Price_List_Id IN NUMBER
47 -- ) IS
48 --
49 -- -- Local variables
50 -- l_price_list_id NUMBER;
51 --
52 -- BEGIN
53 --
54 -- -- Get Price List Id
55 -- SELECT price_list_id
56 -- INTO l_price_list_id
57 -- FROM oe_price_lists
58 -- WHERE price_list_id = p_price_list_id;
59 --
60 -- EXCEPTION
61 --
62 -- WHEN NO_DATA_FOUND THEN
63 --
64 -- IF (g_debug > 0 ) THEN
65 -- debug('price list Not found');
66 -- END IF;
67 --
68 -- FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_PRICE_LIST_ID');
69 -- FND_MESSAGE.SET_TOKEN('PRICE_LIST_ID',p_price_list_id);
70 -- FND_MSG_PUB.ADD;
71 --
72 -- RAISE FND_API.G_EXC_ERROR;
73 --
74 -- WHEN TOO_MANY_ROWS THEN
75 --
76 -- IF (g_debug > 0 ) THEN
77 -- debug('Too many price lists found');
78 -- END IF;
79 --
80 -- FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_PRICE_LIST_ID');
81 -- FND_MESSAGE.SET_TOKEN('PRICE_LIST_ID',p_price_list_id);
82 -- FND_MSG_PUB.ADD;
83 --
84 -- RAISE FND_API.G_EXC_ERROR;
85 --
86 -- END Validate_PriceListID;
87 /*---------------------------------------------------------------------------*/
88
89 PROCEDURE DEBUG(p_message IN VARCHAR2,
90 p_mod_name IN VARCHAR2,
91 p_severity_level IN NUMBER) IS
92
93 -- Variables used in FND Log
94 l_stat_level NUMBER := Fnd_Log.LEVEL_STATEMENT;
95 l_proc_level NUMBER := Fnd_Log.LEVEL_PROCEDURE;
96 l_event_level NUMBER := Fnd_Log.LEVEL_EVENT;
97 l_excep_level NUMBER := Fnd_Log.LEVEL_EXCEPTION;
98 l_error_level NUMBER := Fnd_Log.LEVEL_ERROR;
99 l_unexp_level NUMBER := Fnd_Log.LEVEL_UNEXPECTED;
100
101 BEGIN
102
103 IF p_severity_level = 1
104 THEN
105 IF (l_stat_level >= G_debug_level)
106 THEN
107 Fnd_Log.STRING(l_stat_level, p_mod_name, p_message);
108 END IF;
109 ELSIF p_severity_level = 2
110 THEN
111 IF (l_proc_level >= G_debug_level)
112 THEN
113 Fnd_Log.STRING(l_proc_level, p_mod_name, p_message);
114 END IF;
115 ELSIF p_severity_level = 3
116 THEN
117 IF (l_event_level >= G_debug_level)
118 THEN
119 Fnd_Log.STRING(l_event_level, p_mod_name, p_message);
120 END IF;
121 ELSIF p_severity_level = 4
122 THEN
123 IF (l_excep_level >= G_debug_level)
124 THEN
125 Fnd_Log.STRING(l_excep_level, p_mod_name, p_message);
126 END IF;
127 ELSIF p_severity_level = 5
128 THEN
129 IF (l_error_level >= G_debug_level)
130 THEN
131 Fnd_Log.STRING(l_error_level, p_mod_name, p_message);
132 END IF;
133 ELSIF p_severity_level = 6
134 THEN
135 IF (l_unexp_level >= G_debug_level)
136 THEN
137 Fnd_Log.STRING(l_unexp_level, p_mod_name, p_message);
138 END IF;
139 END IF;
140
141 END DEBUG;
142 /*---------------------------------------------------------------------------*/
143 /* procedure name: Validate_InventoryItemID */
144 /* description : Helper routine that Validates item for a given item ID */
145 /* in the mtl system items table */
146 /* Parameters Required: */
147 /* p_Inventory_Item_Id IN Item identifier */
148 /* x_return_status OUT Standard API paramater */
149 /* x_msg_count OUT Standard API paramater */
150 /* x_msg_data OUT Standard API paramater */
151 /*---------------------------------------------------------------------------*/
152 PROCEDURE Validate_InventoryItemID(p_Inventory_Item_ID IN NUMBER,
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_msg_count OUT NOCOPY NUMBER,
155 x_msg_data OUT NOCOPY VARCHAR2) IS
156 l_Inventory_Item_ID NUMBER;
157 BEGIN
158
159 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
160
161 -- Validate given item id against following sql query.
162 SELECT m.inventory_item_id
163 INTO l_Inventory_Item_ID
164 FROM mtl_system_items_b m
165 WHERE inventory_item_Id = p_Inventory_Item_Id
166 AND m.enabled_flag = 'Y'
167 AND NVL(m.service_item_flag, 'N') = 'N'
168 AND m.serv_req_enabled_code = 'E'
169 AND m.organization_id =
170 Fnd_Profile.value('CS_INV_VALIDATION_ORG')
171 AND TRUNC(SYSDATE) BETWEEN
172 TRUNC(NVL(m.start_date_active, SYSDATE)) AND
173 TRUNC(NVL(m.end_date_active, SYSDATE));
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 Fnd_Message.SET_NAME('CSD', 'CSD_INVALID_ITEM');
178 Fnd_Message.SET_TOKEN('ITEM_ID', p_Inventory_Item_ID);
179 Fnd_Msg_Pub.ADD;
180 IF (g_debug > 0)
181 THEN
182 Csd_Gen_Utility_Pvt.ADD('Inventory_Item_id is invalid');
183 END IF;
184
185 x_return_status := Fnd_Api.G_Ret_Sts_Error;
186
187 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
188 p_data => x_msg_data);
189
190 END Validate_InventoryItemID;
191 /*---------------------------------------------------------------------------*/
192
193 /*---------------------------------------------------------------------------*/
194 /* procedure name: Validate_TxnBillingTypeID */
195 /* description : Helper rutine that validates Billing type for a given Txn */
196 /* Billing Type ID, */
197 /* SU:02/24 Business Process Id, Line Category code, operating Unit */
198 /* Parameters Required: */
199 /* p_Txn_Billing_Type_Id IN Txn billing type identifier */
200 /* p_BusinessProcessID IN Business process id */
201 /* p_LineOrderCategoryCode IN Line Order Category Code */
202 /* p_Operating_Unit_Id IN Org_ID */
203 /*---------------------------------------------------------------------------*/
204 PROCEDURE Validate_TxnBillingTypeID(p_Txn_Billing_Type_Id IN NUMBER,
205 p_BusinessProcessID IN NUMBER,
206 p_LineOrderCategoryCode IN VARCHAR2,
207 p_Operating_Unit_Id IN NUMBER) IS
208
209 -- Local variables here
210 l_Txn_Billing_Type_Id NUMBER;
211
212 BEGIN
213
214 -- Validate given Txn Billing Type ID
215 SELECT tbo.Txn_Billing_Type_ID
216 INTO l_Txn_Billing_Type_Id
217 FROM cs_transaction_Types_Vl tt,
218 cs_Txn_Billing_Types tbt,
219 cs_bus_process_txns bpt,
220 cs_Txn_Billing_OETxn_All tbo
221 WHERE tbt.txn_billing_type_id = p_Txn_Billing_Type_Id
222 AND tbt.transaction_type_id = tt.transaction_type_id
223 AND tbt.Billing_Type = 'M'
224 -- Changing To_Date TO Trunc
225 AND TRUNC(SYSDATE) BETWEEN
226 TRUNC(NVL(tbt.start_date_active, SYSDATE)) AND
227 TRUNC(NVL(tbt.end_date_active, SYSDATE))
228 AND TRUNC(SYSDATE) BETWEEN
229 TRUNC(NVL(tt.start_date_active, SYSDATE)) AND
230 TRUNC(NVL(tt.end_date_active, SYSDATE))
231 AND tt.depot_repair_flag = 'Y'
232 AND tt.line_order_Category_code = p_LineOrderCategoryCode
233 AND tt.transaction_type_Id = bpt.transaction_type_Id
234 AND bpt.business_process_id = p_BusinessProcessID
235 AND tbt.txn_billing_Type_Id = tbo.txn_billing_Type_Id
236 AND tbo.org_id = p_Operating_Unit_Id;
237
238 EXCEPTION
239
240 WHEN NO_DATA_FOUND THEN
241 --JG:02/25: Corrected message code.
242 Fnd_Message.SET_NAME('CSD', 'CSD_INVALID_TXNBILLING_TYPE_ID');
243 Fnd_Message.SET_TOKEN('TXN_BILLING_TYPE_ID',
244 p_txn_billing_type_id);
245 Fnd_Msg_Pub.ADD;
246
247 IF (g_debug > 0)
248 THEN
249 Csd_Gen_Utility_Pvt.ADD('Txn_Billing_Type_id is invalid');
250 END IF;
251
252 RAISE Fnd_Api.G_EXC_ERROR;
253
254 END Validate_TxnBillingTypeID;
255 /*---------------------------------------------------------------------------*/
256
257 /*---------------------------------------------------------------------------*/
258 /* procedure name: Validate_Revision */
259 /* description : Define helper routine that validates Revision for a given */
260 /* Inventory Item Id */
261 /* Parameters Required: */
262 /* p_Inventory_Item_Id IN Item identifier */
263 /* p_Revision IN Revision from mtl serial numbers */
264 /*---------------------------------------------------------------------------*/
265 PROCEDURE Validate_Revision(p_Inventory_Item_Id IN NUMBER,
266 p_Revision IN VARCHAR2) IS
267
268 -- l_Concatenated_Segments VARCHAR2(40);
269 l_revision VARCHAR2(3);
270
271 BEGIN
272
273 SELECT revision
274 INTO l_revision
275 FROM mtl_item_revisions
276 WHERE inventory_item_id = p_inventory_item_id
277 AND organization_id = Fnd_Profile.value('CS_INV_VALIDATION_ORG')
278 AND revision = p_Revision;
279
280 EXCEPTION
281 WHEN NO_DATA_FOUND THEN
282
283 -- Get Concatenated Segments value
284 -- Comment this code since there is not need to call it so many
285 -- times, using global variable g_Concatenated_Segments instead
286 -- Get_Concatenated_Segments
287 -- ( p_inventory_item_Id => p_inventory_item_Id,
288 -- x_Concatenated_Segments => l_Concatenated_Segments ) ;
289
290 IF (g_debug > 0)
291 THEN
292 Csd_Gen_Utility_Pvt.ADD('revision Not found');
293 END IF;
294
295 Fnd_Message.SET_NAME('CSD', 'CSD_INVALID_REVISION_1');
296 -- FND_MESSAGE.SET_TOKEN('ITEM',l_Concatenated_Segments);
297 Fnd_Message.SET_TOKEN('ITEM', g_Concatenated_Segments);
298 Fnd_Message.SET_TOKEN('REVISION', p_revision);
299 Fnd_Msg_Pub.ADD;
300
301 RAISE Fnd_Api.G_EXC_ERROR;
302
303 END Validate_Revision;
304 /*---------------------------------------------------------------------------*/
305
306 /*---------------------------------------------------------------------------*/
307 /* procedure name: Validate_Instance_ID */
308 /* description : Get the serial number and instance number for a given */
309 /* Instance Id, Inventory Item Id, party id and account id */
310 /* SU:02/24 and returns serial number and instance number */
311 /* Parameters Required: */
312 /* p_Inventory_Item_Id IN Item identifier */
313 /* p_Instance_ID IN Instance ID to be validated */
314 /* p_Party_Id IN owner party identifier */
315 /* p_Account_ID IN owner account identifier */
316 /* x_Instance_Number OUT Instance number from Item instances */
317 /* x_Serial_Number OUT Serial number from Item instances */
318 /*---------------------------------------------------------------------------*/
319 PROCEDURE Validate_Instance_ID(p_Inventory_Item_Id IN NUMBER,
320 p_Instance_ID IN NUMBER,
321 p_Party_Id IN NUMBER,
322 p_Account_ID IN NUMBER,
323 x_Instance_Number OUT NOCOPY VARCHAR2,
324 x_Serial_Number OUT NOCOPY VARCHAR2) IS
325
326 BEGIN
327
328 SELECT a.serial_number, a.Instance_number
329 INTO x_serial_number, x_Instance_number
330 FROM csi_item_instances a,
331 mtl_system_items_b b,
332 csi_i_parties cip,
333 csi_install_parameters ip
334 WHERE TRUNC(SYSDATE) BETWEEN
335 TRUNC(NVL(a.active_start_date, SYSDATE)) AND
336 TRUNC(NVL(a.active_end_date, SYSDATE))
337 AND b.enabled_flag = 'Y'
338 -- SU Commented following statement as following where clause depends on profile value
339 -- AND a.location_type_code in ('HZ_PARTY_SITES', 'HZ_LOCATIONS')
340 AND a.owner_party_source_table = 'HZ_PARTIES'
341 AND a.instance_id = cip.instance_id
342 AND cip.party_source_table = 'HZ_PARTIES'
343 AND b.inventory_item_id = a.inventory_item_id
344 AND b.contract_item_type_code IS NULL
345 AND b.serv_req_enabled_code = 'E'
346 AND TRUNC(SYSDATE) BETWEEN
347 TRUNC(NVL(b.start_date_active, SYSDATE)) AND
348 TRUNC(NVL(b.end_date_active, SYSDATE))
349 AND b.organization_id = Cs_Std.get_item_valdn_orgzn_id
350 AND (cip.party_id = NVL(ip.internal_party_id, a.owner_party_id) OR
351 (cip.party_id = NVL(p_Party_ID, a.owner_party_id) AND
352 a.owner_party_account_id =
353 NVL(p_Account_id, a.owner_party_account_id)))
354 AND a.inventory_item_id = p_inventory_item_id
355 AND a.Instance_Id = p_Instance_Id
356 AND cip.relationship_type_code = 'OWNER';
357
358 EXCEPTION
359
360 WHEN NO_DATA_FOUND THEN
361
362 IF (g_debug > 0)
363 THEN
364 Csd_Gen_Utility_Pvt.ADD('instance number Not found');
365 END IF;
366
367 --JG:02/25: Corrected message code. Removed space at the end.
368 Fnd_Message.SET_NAME('CSD', 'CSD_INVALID_INSTANCE_ID');
369 -- Using concatenated segments instead of item ID
370 -- FND_MESSAGE.SET_TOKEN('ITEM_ID',p_inventory_item_id);
371 Fnd_Message.SET_TOKEN('ITEM', g_Concatenated_Segments);
372 Fnd_Message.SET_TOKEN('INSTANCE_ID', p_Instance_ID);
373 Fnd_Msg_Pub.ADD;
374
375 RAISE Fnd_Api.G_EXC_ERROR;
376
377 END Validate_Instance_ID;
378 /*---------------------------------------------------------------------------*/
379
380 /*---------------------------------------------------------------------------*/
381 /* procedure name: Validate_LotNumber */
382 /* description : Validate Lot Number for a given Inventory Item Id and Lot */
383 /* Parameters Required: */
384 /* p_Inventory_Item_Id IN Item identifier */
385 /* p_Lot_Number IN Lot number to be validated */
386 /*---------------------------------------------------------------------------*/
387 PROCEDURE Validate_LotNumber(p_Inventory_Item_Id IN NUMBER,
388 p_Lot_Number IN VARCHAR2) IS
389
390 -- l_Concatenated_Segments VARCHAR2(40);
391 l_lot_number VARCHAR2(80); --fix for bug#4625226
392 BEGIN
393
394 SELECT Lot_Number
395 INTO l_lot_number
396 FROM MTL_LOT_NUMBERS
397 WHERE Inventory_Item_Id = p_inventory_item_id
398 AND Organization_Id = Cs_Std.get_item_valdn_orgzn_id
399 AND Lot_Number = p_Lot_Number;
400
401 EXCEPTION
402
403 WHEN NO_DATA_FOUND THEN
404
405 -- Get Concatenated Segments value
406 -- Comment this code since there is not need to call it so many
407 -- times, using global variable g_Concatenated_Segments instead
408 -- Get_Concatenated_Segments
409 -- ( p_inventory_item_Id => p_inventory_item_Id,
410 -- x_Concatenated_Segments => l_Concatenated_Segments ) ;
411
412 IF (g_debug > 0)
413 THEN
414 Csd_Gen_Utility_Pvt.ADD('Lot Number Not found');
415 END IF;
416
417 Fnd_Message.SET_NAME('CSD', 'CSD_INVALID_LOTNUMBER');
418 -- FND_MESSAGE.SET_TOKEN('ITEM',l_Concatenated_Segments);
419 Fnd_Message.SET_TOKEN('ITEM', g_Concatenated_Segments);
420 Fnd_Message.SET_TOKEN('LOT_NUMBER', p_Lot_Number);
421 Fnd_Msg_Pub.ADD;
422
423 RAISE Fnd_Api.G_EXC_ERROR;
424
425 END Validate_LotNumber;
426 /*---------------------------------------------------------------------------*/
427
428 /*---------------------------------------------------------------------------*/
429 /* procedure name: Validate_SerialNumber */
430 /* description : Validate Serial Number for a given Inv Item Id */
431 /* Parameters Required: */
432 /* p_Inventory_Item_Id IN Item identifier */
433 /* p_Serial_Number IN Serial_Number from mtl serial numbers */
434 /*---------------------------------------------------------------------------*/
435 PROCEDURE Validate_SerialNumber(p_Inventory_Item_Id IN NUMBER,
436 p_Serial_Number IN VARCHAR2) IS
437
438 -- Local Variables
439 l_Current_Status NUMBER;
440 -- l_Concatenated_Segments VARCHAR2(40);
441
442 BEGIN
443
444 SELECT Current_Status
445 INTO l_Current_Status
446 FROM mtl_serial_numbers
447 WHERE inventory_item_id = p_inventory_item_id
448 -- SU Should not check for current organization
449 -- AND current_organization_id = cs_std.get_item_valdn_orgzn_id
450 AND serial_number = p_Serial_Number;
451
452 IF l_Current_Status NOT IN
453 (C_STATUS_OUT_OF_STORES, C_STATUS_INTRANSIT)
454 THEN
455
456 -- Get Concatenated Segments value
457 -- Comment this code since there is not need to call it so many
458 -- times, using global variable g_Concatenated_Segments instead
459 -- Get_Concatenated_Segments
460 -- ( p_inventory_item_Id => p_inventory_item_Id,
461 -- x_Concatenated_Segments => l_Concatenated_Segments ) ;
462
463 Fnd_Message.SET_NAME('CSD', 'CSD_SERNUM_STATUS_INVALID');
464 -- FND_MESSAGE.SET_TOKEN('ITEM',l_Concatenated_Segments);
465 Fnd_Message.SET_TOKEN('ITEM', g_Concatenated_Segments);
466 Fnd_Message.SET_TOKEN('SERIAL_NUM', p_Serial_Number);
467 Fnd_Msg_Pub.ADD;
468
469 IF (g_debug > 0)
470 THEN
471 Csd_Gen_Utility_Pvt.ADD('Serial Number status invalid');
472 END IF;
473
474 RAISE Fnd_Api.G_EXC_ERROR;
475
476 END IF;
477
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 -- SU: It is possible to receive serial Numbers that are not defined in
481 -- the system.
482 NULL;
483
484 END Validate_SerialNumber;
485 /*---------------------------------------------------------------------------*/
486
487 /*---------------------------------------------------------------------------*/
488 /* procedure name: Validate_ReasonCode */
489 /* description : Helper routing to validate Reason Code against the List */
490 /* of values in fnd lookups */
491 /* Parameters Required: */
492 /* p_ReasonCode -> Lookup value to validate */
493 /*---------------------------------------------------------------------------*/
494 PROCEDURE Validate_ReasonCode(p_ReasonCode IN VARCHAR2) IS
495
496 -- Local Variables
497 l_ReasonCode VARCHAR2(30);
498
499 BEGIN
500 -- SU:02/25 : Following sql statement is picked up from RET_REASON record group definition
501 SELECT lookup_code
502 INTO l_ReasonCode
503 FROM ar_lookups
504 WHERE lookup_type = 'CREDIT_MEMO_REASON'
505 AND lookup_code = p_ReasonCode
506 AND TRUNC(SYSDATE) BETWEEN
507 TRUNC(NVL(start_date_active, SYSDATE)) AND
508 TRUNC(NVL(end_date_active, SYSDATE))
509 AND NVL(enabled_flag, 'Y') = 'Y';
510 --SU 02/25: Following sql statement is not correct
511 /*********
512 SELECT lookup_code
513 INTO l_ReasonCode
514 FROM fnd_lookups
515 WHERE lookup_type = 'CSD_REASON'
516 AND Lookup_Code = p_ReasonCode
517 AND enabled_flag = 'Y'
518 AND sysdate BETWEEN nvl(start_date_active,sysdate-1)
519 AND nvl(end_date_active,sysdate+1) ;
520 **************/
521
522 EXCEPTION
523
524 WHEN NO_DATA_FOUND THEN
525
526 IF (g_debug > 0)
527 THEN
528 Csd_Gen_Utility_Pvt.ADD('Reason Code Not found');
529 END IF;
530
531 Fnd_Message.SET_NAME('CSD', 'CSD_INVALID_REASONCODE');
532 Fnd_Message.SET_TOKEN('REASON_CODE', p_ReasonCode);
533 Fnd_Msg_Pub.ADD;
534
535 RAISE Fnd_Api.G_EXC_ERROR;
536
537 END Validate_ReasonCode;
538 /*---------------------------------------------------------------------------*/
539
540 /*---------------------------------------------------------------------------*/
541 /* procedure name: Validate_UOM */
542 /* description : Helper routine used to validate Unit Of Measure of an */
543 /* inventory item id */
544 /* Parameters Required: */
545 /* p_Inventory_Item_Id IN Item identifier */
546 /* p_Unit_Of_Measure IN Unit of Measure */
547 /*---------------------------------------------------------------------------*/
548 PROCEDURE Validate_UOM(p_Inventory_Item_Id IN NUMBER,
549 p_Unit_Of_Measure IN VARCHAR2) IS
550
551 -- Local Variables
552 l_Unit_Of_Measure VARCHAR2(25);
553 -- l_Concatenated_Segments VARCHAR2(40);
554
555 BEGIN
556
557 --SELECT UOM_Code
558 SELECT Unit_of_measure
559 INTO l_Unit_Of_Measure
560 FROM mtl_item_uoms_view
561 WHERE inventory_item_id = p_inventory_item_id
562 AND organization_id = Cs_Std.get_item_valdn_orgzn_id
563 AND UOM_Code = p_Unit_Of_Measure
564 AND uom_type =
565 (SELECT allowed_units_lookup_code
566 FROM mtl_system_items_b
567 WHERE organization_id = Cs_Std.get_item_valdn_orgzn_id
568 AND inventory_item_id = p_inventory_item_id);
569
570 EXCEPTION
571
572 WHEN NO_DATA_FOUND THEN
573
574 -- Get Concatenated Segments value
575 -- Comment this code since there is not need to call it so many
576 -- times, using global variable g_Concatenated_Segments instead
577 -- Get_Concatenated_Segments
578 -- ( p_inventory_item_Id => p_inventory_item_Id,
579 -- x_Concatenated_Segments => l_Concatenated_Segments ) ;
580
581 IF (g_debug > 0)
582 THEN
583 Csd_Gen_Utility_Pvt.ADD('Unit Of Measure Not found');
584 END IF;
585
586 Fnd_Message.SET_NAME('CSD', 'CSD_API_INVALID_UOM');
587 --FND_MESSAGE.SET_TOKEN('ITEM',l_Concatenated_Segments);
588 Fnd_Message.SET_TOKEN('ITEM', g_Concatenated_Segments);
589 Fnd_Message.SET_TOKEN('UOM', p_Unit_Of_Measure);
590 Fnd_Msg_Pub.ADD;
591
592 RAISE Fnd_Api.G_EXC_ERROR;
593
594 END Validate_UOM;
595 /*---------------------------------------------------------------------------*/
596
597 /*---------------------------------------------------------------------------*/
598 /* procedure name: Validate_PartySiteID */
599 /* description : Define Helper routine to validate Party_Site_Id for a */
600 /* SU:02/24: given party, party site and party use type */
601 /* Parameters Required: */
602 /* p_Party_ID IN Unique party identifier */
603 /* p_Party_Site_Id IN unique party site identifier */
604 /* p_Site_Use_type IN i.e. SHIP_TO and BILL_TO */
605 /*---------------------------------------------------------------------------*/
606 PROCEDURE Validate_PartySiteID(p_Party_ID IN NUMBER,
607 p_Party_Site_Id IN NUMBER,
608 p_Site_Use_type IN VARCHAR2) IS
609
610 CURSOR PS_Cur_Type(p_Party_ID NUMBER, p_Party_Site_Id NUMBER, p_Site_Use_type VARCHAR2) IS
611 SELECT ps.party_site_id
612 FROM csd_party_sites_v ps
613 WHERE ps.site_use_type = p_Site_Use_Type
614 AND ps.site_status = 'A'
615 AND ps.site_use_status = 'A'
616 AND ps.party_id = p_Party_Id
617 AND ps.Party_Site_ID = p_Party_Site_ID
618 UNION ALL
619 SELECT ps.party_site_id
620 FROM csd_party_sites_v ps
621 WHERE ps.site_use_type = p_Site_Use_Type
622 AND ps.Party_Site_Id = p_Party_Site_ID
623 AND ps.site_status = 'A'
624 AND ps.site_use_status = 'A'
625 AND ps.party_id IN
626 (SELECT d.sub_party_id
627 FROM csd_hz_rel_v d
628 WHERE d.obj_party_id = p_Party_ID
629 AND d.sub_status = 'A'
630 AND d.sub_party_type IN ('PERSON', 'ORGANIZATION'));
631
632 -- Define local variables here
633 l_party_site_id NUMBER(15);
634
635 BEGIN
636
637 -- Open PS_Cur_Type and fetch values into local variables.
638 OPEN PS_Cur_Type(p_Party_ID, p_Party_Site_Id, p_Site_Use_type);
639 FETCH PS_Cur_Type
640 INTO l_party_site_id;
641
642 IF PS_Cur_Type%NOTFOUND
643 THEN
644
645 CLOSE PS_Cur_Type;
646
647 IF (g_debug > 0)
648 THEN
649 Csd_Gen_Utility_Pvt.ADD('Party Site ID Not found');
650 END IF;
651
652 Fnd_Message.SET_NAME('CSD', 'CSD_API_INVALID_SITE_USE_ID');
653 --SU: Following tokens are added as they are necessary for complete message.
654 Fnd_Message.SET_TOKEN('PARTY_ID', p_Party_ID);
655 Fnd_Message.SET_TOKEN('PARTY_SITE_ID', p_Party_Site_ID);
656 Fnd_Message.SET_TOKEN('SITE_USE_TYPE', p_Site_Use_Type);
657 Fnd_Msg_Pub.ADD;
658
659 RAISE Fnd_Api.G_EXC_ERROR;
660
661 END IF;
662
663 IF PS_Cur_Type%ISOPEN
664 THEN
665
666 CLOSE PS_Cur_Type;
667
668 END IF;
669
670 END Validate_PartySiteID;
671 /*---------------------------------------------------------------------------*/
672
673 /*---------------------------------------------------------------------------*/
674 /* procedure name: Build_ProductTxnRec */
675 /* description : */
676 /* SU : Build Product_Txn_Rec from input record for wrapper API */
677 /* p_UpdateProductTrxn_Rec, Logic behind building product txn */
678 /* rec is that user may pass G_MISS_CHAR value for a varchar2 */
679 /* column in case user does not want to change existing value */
680 /* in such cases it is necessary to get database value for */
681 /* further processing of column value. Similarly for number */
682 /* and date columns. */
683 /* Parameters Required: */
684 /* p_UpdateProductTrxn_Rec IN user input values are stored in this record */
685 /* x_Product_Txn_Rec IN OUT database values are stored in this record*/
686 /*---------------------------------------------------------------------------*/
687 PROCEDURE Build_ProductTxnRec(p_Upd_ProdTxn_Rec IN Csd_Logistics_Pub.Upd_ProdTxn_Rec_Type,
688 x_Product_Txn_Rec IN OUT NOCOPY Csd_Process_Pvt.Product_Txn_Rec) IS
689
690 BEGIN
691
692 -- Action_Code
693 IF (p_Upd_ProdTxn_Rec.action_code <>
694 x_Product_Txn_Rec.action_code AND
695 p_Upd_ProdTxn_Rec.action_code <> Fnd_Api.G_MISS_CHAR)
696 THEN
697 x_Product_Txn_Rec.action_code := p_Upd_ProdTxn_Rec.action_code;
698 END IF;
699
700 -- Action_Type
701 IF (p_Upd_ProdTxn_Rec.Action_Type <>
702 x_Product_Txn_Rec.Action_Type AND
703 p_Upd_ProdTxn_Rec.Action_Type <> Fnd_Api.G_MISS_CHAR)
704 THEN
705 x_Product_Txn_Rec.action_type := p_Upd_ProdTxn_Rec.action_type;
706 END IF;
707
708 -- Attributes
709 IF (p_Upd_ProdTxn_Rec.attribute1 <>
710 x_Product_Txn_Rec.attribute1 AND
711 p_Upd_ProdTxn_Rec.attribute1 <> Fnd_Api.G_MISS_CHAR)
712 THEN
713 x_Product_Txn_Rec.attribute1 := p_Upd_ProdTxn_Rec.attribute1;
714 END IF;
715
716 IF (p_Upd_ProdTxn_Rec.attribute10 <>
717 x_Product_Txn_Rec.attribute10 AND
718 p_Upd_ProdTxn_Rec.attribute10 <> Fnd_Api.G_MISS_CHAR)
719 THEN
720 x_Product_Txn_Rec.attribute10 := p_Upd_ProdTxn_Rec.attribute10;
721 END IF;
722
723 IF (p_Upd_ProdTxn_Rec.attribute11 <>
724 x_Product_Txn_Rec.attribute11 AND
725 p_Upd_ProdTxn_Rec.attribute11 <> Fnd_Api.G_MISS_CHAR)
726 THEN
727 x_Product_Txn_Rec.attribute11 := p_Upd_ProdTxn_Rec.attribute11;
728 END IF;
729
730 IF (p_Upd_ProdTxn_Rec.attribute12 <>
731 x_Product_Txn_Rec.attribute12 AND
732 p_Upd_ProdTxn_Rec.attribute12 <> Fnd_Api.G_MISS_CHAR)
733 THEN
734 x_Product_Txn_Rec.attribute12 := p_Upd_ProdTxn_Rec.attribute12;
735 END IF;
736
737 IF (p_Upd_ProdTxn_Rec.attribute13 <>
738 x_Product_Txn_Rec.attribute13 AND
739 p_Upd_ProdTxn_Rec.attribute13 <> Fnd_Api.G_MISS_CHAR)
740 THEN
741 x_Product_Txn_Rec.attribute13 := p_Upd_ProdTxn_Rec.attribute13;
742 END IF;
743
744 IF (p_Upd_ProdTxn_Rec.attribute14 <>
745 x_Product_Txn_Rec.attribute14 AND
746 p_Upd_ProdTxn_Rec.attribute14 <> Fnd_Api.G_MISS_CHAR)
747 THEN
748 x_Product_Txn_Rec.attribute14 := p_Upd_ProdTxn_Rec.attribute14;
749 END IF;
750
751 IF (p_Upd_ProdTxn_Rec.attribute15 <>
752 x_Product_Txn_Rec.attribute15 AND
753 p_Upd_ProdTxn_Rec.attribute15 <> Fnd_Api.G_MISS_CHAR)
754 THEN
755 x_Product_Txn_Rec.attribute15 := p_Upd_ProdTxn_Rec.attribute15;
756 END IF;
757
758 IF (p_Upd_ProdTxn_Rec.attribute2 <>
759 x_Product_Txn_Rec.attribute2 AND
760 p_Upd_ProdTxn_Rec.attribute2 <> Fnd_Api.G_MISS_CHAR)
761 THEN
762 x_Product_Txn_Rec.attribute2 := p_Upd_ProdTxn_Rec.attribute2;
763 END IF;
764
765 IF (p_Upd_ProdTxn_Rec.attribute3 <>
766 x_Product_Txn_Rec.attribute3 AND
767 p_Upd_ProdTxn_Rec.attribute3 <> Fnd_Api.G_MISS_CHAR)
768 THEN
769 x_Product_Txn_Rec.attribute3 := p_Upd_ProdTxn_Rec.attribute3;
770 END IF;
771
772 IF (p_Upd_ProdTxn_Rec.attribute4 <>
773 x_Product_Txn_Rec.attribute4 AND
774 p_Upd_ProdTxn_Rec.attribute4 <> Fnd_Api.G_MISS_CHAR)
775 THEN
776 x_Product_Txn_Rec.attribute4 := p_Upd_ProdTxn_Rec.attribute4;
777 END IF;
778
779 IF (p_Upd_ProdTxn_Rec.attribute5 <>
780 x_Product_Txn_Rec.attribute5 AND
781 p_Upd_ProdTxn_Rec.attribute5 <> Fnd_Api.G_MISS_CHAR)
782 THEN
783 x_Product_Txn_Rec.attribute5 := p_Upd_ProdTxn_Rec.attribute5;
784 END IF;
785
786 IF (p_Upd_ProdTxn_Rec.attribute6 <>
787 x_Product_Txn_Rec.attribute6 AND
788 p_Upd_ProdTxn_Rec.attribute6 <> Fnd_Api.G_MISS_CHAR)
789 THEN
790 x_Product_Txn_Rec.attribute6 := p_Upd_ProdTxn_Rec.attribute6;
791 END IF;
792
793 IF (p_Upd_ProdTxn_Rec.attribute7 <>
794 x_Product_Txn_Rec.attribute7 AND
795 p_Upd_ProdTxn_Rec.attribute7 <> Fnd_Api.G_MISS_CHAR)
796 THEN
797 x_Product_Txn_Rec.attribute7 := p_Upd_ProdTxn_Rec.attribute7;
798 END IF;
799
800 IF (p_Upd_ProdTxn_Rec.attribute8 <>
801 x_Product_Txn_Rec.attribute8 AND
802 p_Upd_ProdTxn_Rec.attribute8 <> Fnd_Api.G_MISS_CHAR)
803 THEN
804 x_Product_Txn_Rec.attribute8 := p_Upd_ProdTxn_Rec.attribute8;
805 END IF;
806
807 IF (p_Upd_ProdTxn_Rec.attribute9 <>
808 x_Product_Txn_Rec.attribute9 AND
809 p_Upd_ProdTxn_Rec.attribute9 <> Fnd_Api.G_MISS_CHAR)
810 THEN
811 x_Product_Txn_Rec.attribute9 := p_Upd_ProdTxn_Rec.attribute9;
812 END IF;
813
814 -- DFF Context
815 IF (p_Upd_ProdTxn_Rec.context <> x_Product_Txn_Rec.context AND
816 p_Upd_ProdTxn_Rec.context <> Fnd_Api.G_MISS_CHAR)
817 THEN
818 x_Product_Txn_Rec.context := p_Upd_ProdTxn_Rec.context;
819 END IF;
820
821 -- Instance_Id
822 IF (p_Upd_ProdTxn_Rec.source_instance_id <>
823 x_Product_Txn_Rec.source_instance_id AND p_Upd_ProdTxn_Rec.source_instance_id <>
824 Fnd_Api.G_MISS_NUM)
825 THEN
826 x_Product_Txn_Rec.source_instance_id := p_Upd_ProdTxn_Rec.source_instance_id;
827 END IF;
828 -- non source instance
829 IF (p_Upd_ProdTxn_Rec.non_source_instance_id <>
830 x_Product_Txn_Rec.non_source_instance_id AND p_Upd_ProdTxn_Rec.non_source_instance_id <>
831 Fnd_Api.G_MISS_NUM)
832 THEN
833 x_Product_Txn_Rec.non_source_instance_id := p_Upd_ProdTxn_Rec.non_source_instance_id;
834 END IF;
835
836 -- Inventory_Item_Id
837 IF (p_Upd_ProdTxn_Rec.inventory_item_id <>
838 x_Product_Txn_Rec.inventory_item_id AND
839 p_Upd_ProdTxn_Rec.inventory_item_id <> Fnd_Api.G_MISS_NUM)
840 THEN
841 x_Product_Txn_Rec.inventory_item_id := p_Upd_ProdTxn_Rec.inventory_item_id;
842 END IF;
843
844 -- Invoice_To_Org_Id
845 IF (p_Upd_ProdTxn_Rec.invoice_to_org_id <>
846 x_Product_Txn_Rec.invoice_to_org_id AND
847 p_Upd_ProdTxn_Rec.invoice_to_org_id <> Fnd_Api.G_MISS_NUM)
848 THEN
849 x_Product_Txn_Rec.invoice_to_org_id := p_Upd_ProdTxn_Rec.invoice_to_org_id;
850 END IF;
851
852 -- Lot_Number
853 IF (p_Upd_ProdTxn_Rec.lot_number <>
854 x_Product_Txn_Rec.lot_number AND
855 p_Upd_ProdTxn_Rec.lot_number <> Fnd_Api.G_MISS_CHAR)
856 THEN
857 x_Product_Txn_Rec.lot_number := p_Upd_ProdTxn_Rec.lot_number;
858 END IF;
859
860 -- object_version_number
861 IF (p_Upd_ProdTxn_Rec.object_version_number <>
862 x_Product_Txn_Rec.object_version_number AND p_Upd_ProdTxn_Rec.object_version_number <>
863 Fnd_Api.G_MISS_NUM)
864 THEN
865 x_Product_Txn_Rec.object_version_number := p_Upd_ProdTxn_Rec.object_version_number;
866 END IF;
867
868 -- PO_Number
869 IF (p_Upd_ProdTxn_Rec.po_number <>
870 x_Product_Txn_Rec.po_number AND
871 p_Upd_ProdTxn_Rec.po_number <> Fnd_Api.G_MISS_CHAR)
872 THEN
873 x_Product_Txn_Rec.po_number := p_Upd_ProdTxn_Rec.po_number;
874 END IF;
875
876 -- Price_List_Id
877 IF (p_Upd_ProdTxn_Rec.price_list_id <>
878 x_Product_Txn_Rec.price_list_id AND
879 p_Upd_ProdTxn_Rec.price_list_id <> Fnd_Api.G_MISS_NUM)
880 THEN
881 x_Product_Txn_Rec.price_list_id := p_Upd_ProdTxn_Rec.price_list_id;
882 END IF;
883
884 -- Quantity
885 IF (p_Upd_ProdTxn_Rec.quantity <> x_Product_Txn_Rec.quantity AND
886 p_Upd_ProdTxn_Rec.quantity <> Fnd_Api.G_MISS_NUM)
887 THEN
888 x_Product_Txn_Rec.quantity := p_Upd_ProdTxn_Rec.quantity;
889 END IF;
890
891 -- Return_By_Date
892 IF (p_Upd_ProdTxn_Rec.return_by_date <>
893 x_Product_Txn_Rec.return_by_date AND
894 p_Upd_ProdTxn_Rec.return_by_date <> Fnd_Api.G_MISS_DATE)
895 THEN
896 x_Product_Txn_Rec.return_by_date := p_Upd_ProdTxn_Rec.return_by_date;
897 END IF;
898
899 -- Return_Reason
900 IF (p_Upd_ProdTxn_Rec.return_reason <>
901 x_Product_Txn_Rec.return_reason AND
902 p_Upd_ProdTxn_Rec.return_reason <> Fnd_Api.G_MISS_CHAR)
903 THEN
904 x_Product_Txn_Rec.return_reason := p_Upd_ProdTxn_Rec.return_reason;
905 END IF;
906
907 -- Revision
908 IF (p_Upd_ProdTxn_Rec.revision <> x_Product_Txn_Rec.revision AND
909 p_Upd_ProdTxn_Rec.revision <> Fnd_Api.G_MISS_CHAR)
910 THEN
911 x_Product_Txn_Rec.revision := p_Upd_ProdTxn_Rec.revision;
912 END IF;
913
914 -- Serial_Number
915 IF (p_Upd_ProdTxn_Rec.source_serial_number <>
916 Fnd_Api.G_MISS_CHAR) AND
917 NVL(p_Upd_ProdTxn_Rec.source_serial_number, '-') <>
918 NVL(x_Product_Txn_Rec.source_serial_number, '-')
919 THEN
920 x_Product_Txn_Rec.source_serial_number := p_Upd_ProdTxn_Rec.source_serial_number;
921 END IF;
922 -- non_source_Serial_Number
923 IF (p_Upd_ProdTxn_Rec.non_source_serial_number <>
924 Fnd_Api.G_MISS_CHAR) AND
925 NVL(p_Upd_ProdTxn_Rec.non_source_serial_number, '-') <>
926 NVL(x_Product_Txn_Rec.non_source_serial_number, '-')
927 THEN
928 x_Product_Txn_Rec.non_source_serial_number := p_Upd_ProdTxn_Rec.non_source_serial_number;
929 END IF;
930
931 -- Ship_To_Org_Id
932 IF (p_Upd_ProdTxn_Rec.ship_to_org_id <>
933 x_Product_Txn_Rec.ship_to_org_id AND
934 p_Upd_ProdTxn_Rec.ship_to_org_id <> Fnd_Api.G_MISS_NUM)
935 THEN
936 x_Product_Txn_Rec.ship_to_org_id := p_Upd_ProdTxn_Rec.ship_to_org_id;
937 END IF;
938
939 -- Sub_Inventory
940 IF (p_Upd_ProdTxn_Rec.sub_inventory <>
941 x_Product_Txn_Rec.sub_inventory AND
942 p_Upd_ProdTxn_Rec.sub_inventory <> Fnd_Api.G_MISS_CHAR)
943 THEN
944 x_Product_Txn_Rec.sub_inventory := p_Upd_ProdTxn_Rec.sub_inventory;
945 END IF;
946
947 -- Txn_Billing_Type_ID
948 IF (p_Upd_ProdTxn_Rec.txn_billing_type_id <>
949 x_Product_Txn_Rec.txn_billing_type_id AND p_Upd_ProdTxn_Rec.txn_billing_type_id <>
950 Fnd_Api.G_MISS_NUM)
951 THEN
952 x_Product_Txn_Rec.txn_billing_type_id := p_Upd_ProdTxn_Rec.txn_billing_type_id;
953 END IF;
954
955 -- Unit_Of_Measure
956 IF (p_Upd_ProdTxn_Rec.unit_of_measure_code <>
957 x_Product_Txn_Rec.unit_of_measure_code AND p_Upd_ProdTxn_Rec.unit_of_measure_code <>
958 Fnd_Api.G_MISS_CHAR)
959 THEN
960 x_Product_Txn_Rec.unit_of_measure_code := p_Upd_ProdTxn_Rec.unit_of_measure_code;
961 END IF;
962 --SU:02/28 Pass G_MISS_NUM when contract_Id is NULL
963 IF x_Product_Txn_Rec.Contract_ID IS NULL
964 THEN
965 x_Product_Txn_REc.Contract_Id := Fnd_Api.G_MISS_NUM;
966 END IF;
967
968 -- Set values for WHO columns
969 x_Product_Txn_Rec.Last_Updated_By := Fnd_Global.User_Id;
970 x_Product_Txn_Rec.Last_Update_Date := SYSDATE;
971 x_Product_Txn_Rec.Last_Update_Login := Fnd_Global.Login_Id;
972
973 END Build_ProductTxnRec;
974 /*---------------------------------------------------------------------------*/
975
976 /*---------------------------------------------------------------------------*/
977 /* procedure name: Get_Concatenated_Segments */
978 /* description : Define helper routine to get concatenated segments name */
979 /* for a given Inventory Item Id */
980 /* Parameters Required: */
981 /* p_Inventory_Item_Id IN Item identifier */
982 /* x_Concatenated_Segments OUT Concatenated segments from mtl system ites */
983 /* Notes: Once the Inventory_Item_Id is validated the global variable */
984 /* g_Concatenated_Segments is populated and then is going to be used by */
985 /* different helper routines to report error messages. */
986 /*---------------------------------------------------------------------------*/
987 PROCEDURE Get_Concatenated_Segments(p_inventory_item_Id IN NUMBER,
988 x_Concatenated_Segments OUT NOCOPY VARCHAR2) IS
989
990 -- Local variables
991 --SU:02/24: Local variable is not required as we are using out variable in our code.
992 -- l_Concatenated_Segments VARCHAR2(40);
993
994 BEGIN
995
996 SELECT Concatenated_Segments
997 INTO x_Concatenated_Segments
998 FROM mtl_system_items_kfv
999 WHERE Inventory_Item_Id = p_Inventory_item_Id
1000 AND Organization_Id = Fnd_Profile.value('CS_INV_VALIDATION_ORG');
1001
1002 --SU:02/24: Following statement can be commented.
1003 --x_Concatenated_Segments := l_Concatenated_Segments;
1004
1005 EXCEPTION
1006
1007 WHEN NO_DATA_FOUND THEN
1008
1009 IF (g_debug > 0)
1010 THEN
1011 Csd_Gen_Utility_Pvt.ADD('Concatenated_Segments Not found');
1012 END IF;
1013
1014 x_Concatenated_Segments := NULL;
1015
1016 END Get_Concatenated_Segments;
1017 /*---------------------------------------------------------------------------*/
1018
1019 /*---------------------------------------------------------------------------*/
1020 /* procedure name: Validate_source_SerialNumber */
1021 /* description : Helper Routine to validate Shipped_Serial_Number for a */
1022 /* given serial number */
1023 /* Parameters Required: */
1024 /* p_Inventory_Item_Id IN Item identifier */
1025 /* p_Serial_Number IN Serial Number of the Item */
1026 /* p_Serial_Control_Code IN Serial control code of the item */
1027 /*---------------------------------------------------------------------------*/
1028 PROCEDURE Validate_source_SerialNumber(p_Inventory_Item_ID IN NUMBER,
1029 p_Serial_Number IN VARCHAR2,
1030 p_Serial_Control_Code IN NUMBER) IS
1031
1032 -- Local constants
1033 C_Status_Out_Of_Stores CONSTANT NUMBER := 4;
1034 C_Status_Intransit CONSTANT NUMBER := 5;
1035 C_Status_In_Stores CONSTANT NUMBER := 3;
1036
1037 -- Local Variables
1038 l_Current_Status NUMBER;
1039
1040 BEGIN
1041
1042 SELECT Current_Status
1043 INTO l_Current_Status
1044 FROM mtl_serial_numbers
1045 WHERE inventory_item_id = p_inventory_item_id
1046 -- SU:02/24: While doing Serial Number validation current organization should not be
1047 -- hard coded to item validation organization. So please comment following statement
1048 --AND current_organization_id = cs_std.get_item_valdn_orgzn_id
1049 AND serial_number = p_Serial_Number;
1050
1051 IF l_Current_Status <> (C_Status_In_Stores)
1052 THEN
1053
1054 Fnd_Message.SET_NAME('CSD', 'CSD_SERNUM_STATUS_INVALID');
1055 Fnd_Message.SET_TOKEN('ITEM', p_inventory_item_id);
1056 Fnd_Message.SET_TOKEN('SERIAL_NUM', p_Serial_Number);
1057 Fnd_Msg_Pub.ADD;
1058
1059 RAISE Fnd_Api.G_EXC_ERROR;
1060
1061 END IF;
1062
1063 EXCEPTION
1064
1065 WHEN NO_DATA_FOUND THEN
1066 -- Serial_Control_Code = 2 @ Receipt, Serial Number should exist in system.
1067 -- Seial_Control_Code = 5@ Pre Defined, Serial Number should exist in system
1068 IF p_Serial_Control_Code IN (2, 5)
1069 THEN
1070
1071 IF (g_debug > 0)
1072 THEN
1073 Csd_Gen_Utility_Pvt.ADD('Shipped Serial Number Not found');
1074 END IF;
1075
1076 Fnd_Message.SET_NAME('CSD', 'CSD_INVALID_SERIAL_NUMBER');
1077 -- Using concatenated segments instead of item ID
1078 -- FND_MESSAGE.SET_TOKEN('ITEM_ID',p_inventory_item_id);
1079 Fnd_Message.SET_TOKEN('ITEM', g_Concatenated_Segments);
1080 Fnd_Message.SET_TOKEN('SERIAL_NUM', p_Serial_Number);
1081 Fnd_Msg_Pub.ADD;
1082
1083 RAISE Fnd_Api.G_EXC_ERROR;
1084
1085 END IF;
1086
1087 END Validate_source_SerialNumber;
1088 /*---------------------------------------------------------------------------*/
1089
1090 /*---------------------------------------------------------------------------*/
1091 /* procedure name: Set_ProductTrxnRec_Flags */
1092 /* description : */
1093 /* SU: This procedure is a helper routine to read the values from record */
1094 /* structure UpdateProductTrxn_rec, which is an input parameter for */
1095 /* wrapper API CSD_Process_PVt.Update_Product_Txn_Wrapr and set values */
1096 /* in record structure Product_Txn_Rec which is an out parameter */
1097 /* On Error: This procedure is built not to raise any exceptions, as no */
1098 /* exceptions are expected in the body. */
1099 /* Parameters Required: */
1100 /* p_Upd_ProdTxn_Rec IN user input values are stored in this record */
1101 /* x_Product_Txn_Rec IN OUT database values are stored in this record*/
1102 /*---------------------------------------------------------------------------*/
1103 PROCEDURE Set_ProductTrxnRec_Flags(p_Upd_ProdTxn_Rec IN Csd_Logistics_Pub.Upd_ProdTxn_Rec_Type,
1104 x_Product_Txn_Rec IN OUT NOCOPY Csd_Process_Pvt.Product_Txn_Rec) IS
1105
1106 -- Define Local CONSTANTS
1107 C_YES CONSTANT VARCHAR2(1) := 'Y';
1108 C_NO CONSTANT VARCHAR2(1) := 'N';
1109
1110 BEGIN
1111
1112 -- Set values based on Book_Sales_Order_Flag value
1113 IF UPPER(p_Upd_ProdTxn_Rec.Book_Sales_Order_Flag) = C_YES
1114 THEN
1115 x_Product_Txn_Rec.Interface_To_OM_Flag := C_YES;
1116 x_Product_Txn_Rec.Book_Sales_Order_Flag := C_YES;
1117 ELSE
1118 x_Product_Txn_Rec.Book_Sales_Order_Flag := C_NO;
1119 END IF;
1120
1121 IF UPPER(p_Upd_ProdTxn_Rec.Interface_TO_OM_Flag) <> C_YES
1122 THEN
1123
1124 x_Product_Txn_Rec.Interface_TO_OM_Flag := C_NO;
1125
1126 -- SU following Else statement is added
1127 ELSE
1128 x_Product_Txn_Rec.Interface_TO_OM_Flag := C_YES;
1129
1130 END IF;
1131
1132 -- Set values for New Order Flag
1133 IF UPPER(x_Product_Txn_Rec.New_Order_Flag) <> C_YES
1134 THEN
1135 X_Product_Txn_Rec.New_Order_Flag := C_NO;
1136 ELSE
1137 X_Product_Txn_Rec.New_Order_Flag := C_YES;
1138 END IF;
1139
1140 -- Process Transaction flag should be always be set to True
1141 x_Product_Txn_Rec.Process_Txn_Flag := C_YES;
1142
1143 -- Set value for no_Charge_Flag
1144 IF UPPER(x_Product_Txn_Rec.No_Charge_Flag) = C_YES
1145 THEN
1146 x_Product_Txn_Rec.After_Warranty_Cost := NULL;
1147 x_Product_Txn_Rec.No_Charge_Flag := C_YES;
1148 ELSE
1149 -- SU: When NO_Charge_Flag is set to NO then charge should be copied to affter_warranty_Cost
1150 x_Product_Txn_Rec.After_Warranty_Cost := p_Upd_ProdTxn_Rec.Charge;
1151 x_Product_Txn_Rec.No_Charge_Flag := C_NO;
1152 END IF;
1153
1154 END Set_ProductTrxnRec_Flags;
1155 /*---------------------------------------------------------------------------*/
1156
1157 /*---------------------------------------------------------------------------*/
1158 /* procedure name: Validate_ProductTrxnRec */
1159 /* description : */
1160 /* SU: This procedure is a helper routine to validate input values from */
1161 /* record structure UpdateProductTrxn_Rec to make sure that values passed */
1162 /* are valid values. This procedure should be called when it is determined */
1163 /* that a specific attribute value can be changed by user. */
1164 /* On Error: X_Return_Status variable will have the return status value */
1165 /* X_Msg_Count will have the count of messages in message stack */
1166 /* X_Msg_Data will have a value if X_Msg_Count has value 1 */
1167 /* Parameters Required: */
1168 /* p_Upd_ProdTxn_Rec IN user input values are stored in this record */
1169 /* x_Product_Txn_Rec IN OUT database values are stored in this record*/
1170 /* x_return_status OUT Standard API paramater */
1171 /* x_msg_count OUT Standard API paramater */
1172 /* x_msg_data OUT Standard API paramater */
1173 /*---------------------------------------------------------------------------*/
1174 PROCEDURE Validate_ProductTrxnRec(p_Upd_ProdTxn_Rec IN Csd_Logistics_Pub.Upd_ProdTxn_Rec_Type,
1175 p_Product_Txn_Rec IN Csd_Process_Pvt.Product_Txn_Rec,
1176 x_return_status OUT NOCOPY VARCHAR2,
1177 x_msg_count OUT NOCOPY NUMBER,
1178 x_msg_data OUT NOCOPY VARCHAR2) IS
1179 -- Define local Variables
1180 l_ItemAttributes Csd_Logistics_Util.ItemAttributes_Rec_Type;
1181 l_api_name CONSTANT VARCHAR2(30) := 'Validate_ProductTrxnRec';
1182 l_Customer_Id NUMBER;
1183 l_Currency_Code VARCHAR2(30);
1184 l_Serial_Number VARCHAR2(30);
1185 l_non_src_Serial_Number VARCHAR2(30);
1186 l_Instance_Number VARCHAR2(30);
1187 l_non_src_Instance_Number VARCHAR2(30);
1188 l_Line_Order_Category_Code VARCHAR2(30);
1189 l_Account_Id NUMBER;
1190 l_Operating_Unit NUMBER;
1191
1192 -- Define a cursor that gets customer_id AND currency_Code for a given repair_line_Id
1193 CURSOR RO_Cur_Type(p_Repair_Line_Id NUMBER) IS
1194 SELECT sr.Customer_Id, dra.Currency_Code
1195 FROM cs_incidents_b_sec sr, csd_repairs dra
1196 WHERE dra.incident_id = sr.incident_id and dra.Repair_Line_Id = p_Repair_Line_Id;
1197
1198 -- Define SR Cursor Type
1199 CURSOR SR_Cur_Type(p_Incident_id NUMBER) IS
1200 SELECT Account_Id
1201 FROM CS_INCIDENTS_VL_SEC
1202 --- Csd_Incidents_V
1203 WHERE Incident_Id = p_Incident_Id;
1204
1205 BEGIN
1206
1207 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1208
1209 -- Get Item attributes in local variable
1210 Get_ItemAttributes(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
1211 p_inv_org_id => Cs_Std.get_item_valdn_orgzn_id,
1212 x_ItemAttributes => l_ItemAttributes);
1213
1214 -- Open RO_Cur_Type AND fetch values into local variables.
1215 OPEN RO_Cur_Type(p_Product_Txn_Rec.Repair_line_Id);
1216 FETCH RO_Cur_Type
1217 INTO l_Customer_Id, l_Currency_Code;
1218 CLOSE RO_Cur_Type;
1219
1220 -- Fetch SR cursor information in to local variable
1221 OPEN SR_Cur_Type(p_Product_Txn_Rec.Incident_Id);
1222 FETCH SR_Cur_Type
1223 INTO l_Account_ID;
1224 CLOSE SR_Cur_Type;
1225
1226 IF p_product_Txn_Rec.Prod_Txn_Status = C_PROD_TXN_STS_ENTERED
1227 THEN
1228
1229 -- Action_Type is required value, if null is passed then raise error
1230 IF p_Upd_ProdTxn_Rec.Action_Type IS NULL
1231 THEN
1232 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.Action_Type,
1233 p_param_name => 'ACTION_TYPE',
1234 p_api_name => l_api_name);
1235 END IF;
1236
1237 IF p_Upd_ProdTxn_Rec.Action_Code IS NULL
1238 THEN
1239 -- Action_Code is required value, if null is passed then raise error
1240 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.Action_Code,
1241 p_param_name => 'ACTION_CODE',
1242 p_api_name => l_api_name);
1243 END IF;
1244
1245 IF p_Upd_ProdTxn_Rec.Inventory_item_id IS NULL
1246 THEN
1247 -- Inventory_Item_Id is required, if Null is passed then raise error.
1248 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.Inventory_Item_Id,
1249 p_param_name => 'INVENTORY_ITEM_ID',
1250 p_api_name => l_api_name);
1251 END IF;
1252 -- IF value is found then Validate Inventory_Item_Id
1253 IF p_Upd_ProdTxn_Rec.Inventory_Item_Id <>
1254 Fnd_Api.G_MISS_NUM
1255 THEN
1256
1257 Validate_InventoryItemId(p_Inventory_Item_ID => p_Product_Txn_Rec.Inventory_Item_Id,
1258 x_Return_Status => x_Return_Status,
1259 x_Msg_Data => x_Msg_Data,
1260 x_Msg_Count => x_Msg_Count);
1261
1262 IF x_Return_Status <> Fnd_Api.G_RET_STS_SUCCESS
1263 THEN
1264
1265 RAISE Fnd_Api.G_EXC_ERROR;
1266
1267 END IF;
1268
1269 END IF;
1270
1271 Get_Concatenated_Segments(p_inventory_item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
1272 x_Concatenated_Segments => g_Concatenated_Segments);
1273
1274 IF p_Upd_ProdTxn_Rec.Txn_Billing_Type_Id IS NULL
1275 THEN
1276 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.Txn_Billing_type_id,
1277 p_param_name => 'TXN_BILLING_TYPE_ID',
1278 p_api_name => l_api_name);
1279 END IF;
1280
1281 IF p_Upd_ProdTxn_Rec.Txn_Billing_Type_Id <>
1282 Fnd_Api.G_MISS_NUM
1283 THEN
1284 -- IF value is found then Validate Txn_Billing_Type_ID value
1285
1286 -- Line_Order_Category_Code can have one of the two valus 'RETURN'
1287 -- or 'ORDER', if action_type says it is RMA then 'RETURN' value should
1288 -- passed else 'ORDER' value should be passed
1289 -- NVL check for Action_Code is not required.
1290 -- Include one more case C_ACTION_TYPE_WALK_IN_RECPT
1291 IF p_Product_Txn_Rec.action_type IN
1292 (C_ACTION_TYPE_RMA, C_ACTION_TYPE_WALK_IN_RECPT)
1293 THEN
1294 l_Line_Order_Category_Code := 'RETURN';
1295 ELSE
1296 l_Line_Order_Category_Code := 'ORDER';
1297 END IF;
1298
1299 -- Get the Operating Unit parameter
1300 l_Operating_Unit := Csd_Process_Util.get_org_id(p_incident_id => p_Product_Txn_Rec.incident_id);
1301
1302 Validate_TxnBillingTypeID(p_Txn_Billing_Type_Id => p_Product_Txn_Rec.Txn_Billing_Type_Id,
1303 p_BusinessProcessID => p_Product_Txn_Rec.Business_Process_Id,
1304 p_LineOrderCategoryCode => l_Line_Order_Category_Code,
1305 p_Operating_Unit_Id => l_Operating_Unit);
1306
1307 END IF;
1308
1309 IF p_Upd_ProdTxn_Rec.Price_List_id IS NULL
1310 THEN
1311 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.price_list_id,
1312 p_param_name => 'PRICE_LIST_ID',
1313 p_api_name => l_api_name);
1314 END IF;
1315
1316 -- IF value is found then validate Price List Id value
1317 IF p_Upd_ProdTxn_Rec.Quantity IS NULL
1318 THEN
1319 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.Quantity,
1320 p_param_name => 'QUANTITY',
1321 p_api_name => l_api_name);
1322
1323 END IF;
1324 IF l_ItemAttributes.Revision_Code > 1
1325 THEN
1326
1327 IF p_Upd_ProdTxn_Rec.Revision IS NULL
1328 THEN
1329 -- If item is revision controlled then Revision_Code is required column
1330 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.Revision,
1331 p_param_name => 'REVISION',
1332 p_api_name => l_api_name);
1333 END IF;
1334
1335 -- If value is not Null then validate Revision Value
1336 IF p_Upd_ProdTxn_Rec.Revision <> Fnd_Api.G_MISS_CHAR
1337 THEN
1338
1339 Validate_Revision(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
1340 p_Revision => p_product_Txn_Rec.Revision);
1341
1342 END IF;
1343
1344 ELSE
1345
1346 -- Check if value is passed to Revision Code
1347 IF NVL(p_Upd_ProdTxn_Rec.Revision,
1348 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR
1349 THEN
1350
1351 IF (g_debug > 0)
1352 THEN
1353 Csd_Gen_Utility_Pvt.ADD('Revision column should be Null');
1354 END IF;
1355
1356 Fnd_Message.SET_NAME('CSD',
1357 'CSD_ATTRB_VALUE_NOT_EXPECTED');
1358
1359 Fnd_Message.SET_TOKEN('ATTRIBUTE', 'Revision');
1360 Fnd_Msg_Pub.ADD;
1361
1362 RAISE Fnd_Api.G_EXC_ERROR;
1363
1364 END IF;
1365
1366 END IF;
1367
1368 -- Reason_Code column should be Null for Ship line.
1369 IF (p_Product_Txn_Rec.action_type IN
1370 (C_ACTION_TYPE_SHIP, C_ACTION_TYPE_WALK_IN_ISSUE) AND
1371 p_Upd_ProdTxn_Rec.Return_Reason <>
1372 Fnd_Api.G_MISS_CHAR)
1373 THEN
1374
1375 IF (g_debug > 0)
1376 THEN
1377 Csd_Gen_Utility_Pvt.ADD('Reason_Code column should be Null for Ship line');
1378 END IF;
1379
1380 Fnd_Message.SET_NAME('CSD', 'CSD_ATTRB_VALUE_NOT_EXPECTED');
1381 Fnd_Message.SET_TOKEN('ATTRIBUTE', 'Return Reason Code');
1382 Fnd_Msg_Pub.ADD;
1383
1384 RAISE Fnd_Api.G_EXC_ERROR;
1385
1386 END IF;
1387
1388 -- Reason Code is required for RMA line. Check if Reason Code value is NULL, if so then raise error.
1389 IF p_Upd_ProdTxn_Rec.action_type IN
1390 (C_ACTION_TYPE_RMA, C_ACTION_TYPE_WALK_IN_RECPT)
1391 THEN
1392
1393 IF p_Upd_ProdTxn_Rec.Return_Reason IS NULL
1394 THEN
1395 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.Return_Reason,
1396 p_param_name => 'REASON_CODE',
1397 p_api_name => l_api_name);
1398 END IF;
1399
1400 -- If value is not Null then validate Revision Value
1401 IF p_Upd_ProdTxn_Rec.Return_Reason <>
1402 Fnd_Api.G_MISS_CHAR
1403 THEN
1404
1405 Validate_ReasonCode(p_ReasonCode => p_Product_Txn_Rec.Return_Reason);
1406
1407 END IF;
1408
1409 END IF;
1410
1411 -- non_source_Serial_Number should be Null for RMA line.
1412 IF p_Product_Txn_Rec.action_type IN
1413 (C_ACTION_TYPE_RMA, C_ACTION_TYPE_WALK_IN_RECPT) AND
1414 NVL(p_Upd_ProdTxn_Rec.non_source_Serial_Number,
1415 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR
1416 THEN
1417
1418 IF (g_debug > 0)
1419 THEN
1420 Csd_Gen_Utility_Pvt.ADD('non_source_Serial_Number column should be Null for RMA line');
1421 END IF;
1422
1423 Fnd_Message.SET_NAME('CSD', 'CSD_ATTRB_VALUE_NOT_EXPECTED');
1424 Fnd_Message.SET_TOKEN('ATTRIBUTE',
1425 'non source Serial Number');
1426 Fnd_Msg_Pub.ADD;
1427 RAISE Fnd_Api.G_EXC_ERROR;
1428
1429 END IF;
1430 -- non_source_instance_id should be Null for RMA line.
1431 IF p_Product_Txn_Rec.action_type IN
1432 (C_ACTION_TYPE_RMA, C_ACTION_TYPE_WALK_IN_RECPT) AND
1433 NVL(p_Upd_ProdTxn_Rec.non_source_instance_id,
1434 Fnd_Api.G_MISS_NUM) <> Fnd_Api.G_MISS_NUM
1435 THEN
1436
1437 IF (g_debug > 0)
1438 THEN
1439 Csd_Gen_Utility_Pvt.ADD('non_source_instance_id column should be Null for RMA line');
1440 END IF;
1441
1442 Fnd_Message.SET_NAME('CSD', 'CSD_ATTRB_VALUE_NOT_EXPECTED');
1443 Fnd_Message.SET_TOKEN('ATTRIBUTE',
1444 'non source instance_id');
1445 Fnd_Msg_Pub.ADD;
1446 RAISE Fnd_Api.G_EXC_ERROR;
1447
1448 END IF;
1449
1450 -- Return_By_Date should be Null for RMA line.
1451 -- NVL check for Action Type is not required.
1452 IF p_Product_Txn_Rec.action_type IN
1453 (C_ACTION_TYPE_RMA, C_ACTION_TYPE_WALK_IN_RECPT) AND
1454 NVL(p_Upd_ProdTxn_Rec.Return_By_Date,
1455 Fnd_Api.G_MISS_DATE) <> Fnd_Api.G_MISS_DATE
1456 THEN
1457
1458 IF (g_debug > 0)
1459 THEN
1460 Csd_Gen_Utility_Pvt.ADD('Return_By_Date column should be Null for RMA line');
1461 END IF;
1462
1463 Fnd_Message.SET_NAME('CSD', 'CSD_ATTRB_VALUE_NOT_EXPECTED');
1464 Fnd_Message.SET_TOKEN('ATTRIBUTE', 'Return By Date');
1465 Fnd_Msg_Pub.ADD;
1466
1467 RAISE Fnd_Api.G_EXC_ERROR;
1468
1469 END IF;
1470
1471 -- Validate source_Serial Number
1472 IF l_ItemAttributes.Serial_Code > 1
1473 THEN
1474 IF NVL(p_Upd_ProdTxn_Rec.source_Serial_Number,
1475 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR AND
1476 p_Product_Txn_Rec.Action_Type IN
1477 (C_ACTION_TYPE_RMA, C_ACTION_TYPE_WALK_IN_RECPT)
1478 THEN
1479
1480 Validate_SerialNumber(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
1481 p_Serial_Number => p_Product_Txn_Rec.source_Serial_Number);
1482 END IF;
1483
1484 IF NVL(p_Upd_ProdTxn_Rec.source_Serial_Number,
1485 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR AND
1486 p_Product_Txn_Rec.Action_Type IN
1487 (C_ACTION_TYPE_SHIP, C_ACTION_TYPE_WALK_IN_ISSUE)
1488 THEN
1489
1490 Validate_source_SerialNumber(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
1491 p_Serial_Number => p_Product_Txn_Rec.source_Serial_Number,
1492 p_serial_control_code => l_ItemAttributes.Serial_Code);
1493 END IF;
1494
1495 IF NVL(p_Upd_ProdTxn_Rec.non_source_Serial_Number,
1496 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR
1497 THEN
1498
1499 Validate_SerialNumber(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
1500 p_Serial_Number => p_Product_Txn_Rec.non_source_Serial_Number);
1501 END IF;
1502
1503 ELSE
1504 --Serial Number column should be NULL else raise exception
1505 -- attribute value not expected.
1506 IF NVL(p_Upd_ProdTxn_Rec.source_Serial_Number,
1507 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR
1508 THEN
1509 Fnd_Message.SET_NAME('CSD',
1510 'CSD_ATTRB_VALUE_NOT_EXPECTED');
1511 Fnd_Message.SET_TOKEN('ATTRIBUTE',
1512 'source_Serial Number');
1513 Fnd_Msg_Pub.ADD;
1514 RAISE Fnd_Api.G_EXC_ERROR;
1515 END IF;
1516 IF NVL(p_Upd_ProdTxn_Rec.non_source_Serial_Number,
1517 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR
1518 THEN
1519 Fnd_Message.SET_NAME('CSD',
1520 'CSD_ATTRB_VALUE_NOT_EXPECTED');
1521 Fnd_Message.SET_TOKEN('ATTRIBUTE',
1522 'non_source_Serial Number');
1523 Fnd_Msg_Pub.ADD;
1524 RAISE Fnd_Api.G_EXC_ERROR;
1525 END IF;
1526
1527 END IF;
1528
1529 -- Validate IB ref id
1530 IF l_ItemAttributes.IB_Flag = 'Y'
1531 THEN
1532
1533 IF NVL(p_Upd_ProdTxn_Rec.source_Instance_id,
1534 Fnd_Api.G_MISS_NUM) <> Fnd_Api.G_MISS_NUM
1535 THEN
1536 Validate_Instance_ID(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
1537 p_Instance_Id => p_Product_Txn_Rec.source_Instance_ID,
1538 p_Party_Id => l_Customer_ID,
1539 p_Account_ID => l_Account_Id,
1540 x_Instance_Number => l_Instance_Number,
1541 x_Serial_Number => l_Serial_Number);
1542 END IF;
1543 --non source
1544 IF NVL(p_Upd_ProdTxn_Rec.non_source_Instance_id,
1545 Fnd_Api.G_MISS_NUM) <> Fnd_Api.G_MISS_NUM
1546 THEN
1547 Validate_Instance_ID(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
1548 p_Instance_Id => p_Product_Txn_Rec.non_source_Instance_ID,
1549 p_Party_Id => l_Customer_ID,
1550 p_Account_ID => l_Account_Id,
1551 x_Instance_Number => l_non_src_Instance_Number,
1552 x_Serial_Number => l_non_src_Serial_Number);
1553 END IF;
1554 -- If item is not IB trackable then value is not
1555 -- expected for instance_Id
1556 ELSE
1557 IF NVL(p_Upd_ProdTxn_Rec.source_Instance_id,
1558 Fnd_Api.G_MISS_NUM) <> Fnd_Api.G_MISS_NUM
1559 THEN
1560 Fnd_Message.SET_NAME('CSD',
1561 'CSD_ATTRB_VALUE_NOT_EXPECTED');
1562 Fnd_Message.SET_TOKEN('ATTRIBUTE', 'Instance Id');
1563 Fnd_Msg_Pub.ADD;
1564 RAISE Fnd_Api.G_EXC_ERROR;
1565 END IF;
1566
1567 END IF;
1568
1569 -- If Item is Serial Controlled validate
1570 -- Serial Number AND Instance Number belongs to the same item.
1571 IF (l_ItemAttributes.Serial_Code > 1) AND
1572 (l_ItemAttributes.IB_Flag = 'Y')
1573 THEN
1574
1575 IF NVL(p_Product_Txn_Rec.source_Serial_Number, '-') <>
1576 NVL(l_Serial_Number, '-')
1577 THEN
1578
1579 Fnd_Message.SET_NAME('CSD',
1580 'CSD_SRNUM_INST_NUM_MISMATCH');
1581 -- Using concatenated segments instead of item ID
1582 Fnd_Message.SET_TOKEN('ITEM', g_Concatenated_Segments);
1583 Fnd_Message.SET_TOKEN('SERIAL_NUM', l_Serial_Number);
1584 Fnd_Message.SET_TOKEN('INSTANCE_NUM',
1585 l_Instance_Number);
1586 Fnd_Msg_Pub.ADD;
1587
1588 RAISE Fnd_Api.G_EXC_ERROR;
1589
1590 END IF;
1591 -- non source
1592 IF NVL(p_Product_Txn_Rec.non_source_Serial_Number, '-') <>
1593 NVL(l_non_src_Serial_Number, '-')
1594 THEN
1595
1596 Fnd_Message.SET_NAME('CSD',
1597 'CSD_SRNUM_INST_NUM_MISMATCH');
1598 -- Using concatenated segments instead of item ID
1599 Fnd_Message.SET_TOKEN('ITEM', g_Concatenated_Segments);
1600 Fnd_Message.SET_TOKEN('SERIAL_NUM',
1601 l_non_src_Serial_Number);
1602 Fnd_Message.SET_TOKEN('INSTANCE_NUM',
1603 l_non_src_Instance_Number);
1604 Fnd_Msg_Pub.ADD;
1605
1606 RAISE Fnd_Api.G_EXC_ERROR;
1607
1608 END IF;
1609
1610 END IF;
1611
1612 -- Validate Lot Number
1613 IF l_ItemAttributes.Lot_Code > 1
1614 THEN
1615
1616 IF NVL(p_Upd_ProdTxn_Rec.Lot_Number,
1617 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR
1618 THEN
1619
1620 Validate_LotNumber(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
1621 p_Lot_Number => p_Product_Txn_Rec.Lot_Number);
1622
1623 END IF;
1624
1625 END IF;
1626
1627 IF p_Upd_ProdTxn_Rec.Invoice_To_Org_Id IS NULL
1628 THEN
1629 -- Bill_TO_Address is required column
1630 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.Invoice_To_Org_ID,
1631 p_param_name => 'INVOICE_TO_ORG_ID',
1632 p_api_name => l_api_name);
1633 END IF;
1634
1635 -- Validate Bill to org ID
1636 IF NVL(p_Upd_ProdTxn_Rec.Invoice_To_Org_Id,
1637 Fnd_Api.G_MISS_NUM) <> Fnd_Api.G_MISS_NUM
1638 THEN
1639
1640 Validate_PartySiteID(p_Party_ID => l_Customer_Id,
1641 p_Party_Site_Id => p_Product_Txn_Rec.Invoice_To_Org_ID,
1642 p_Site_Use_type => C_SITE_USE_TYPE_BILL_TO);
1643
1644 END IF;
1645
1646 IF p_Upd_ProdTxn_Rec.Ship_To_Org_ID IS NULL
1647 THEN
1648 -- Ship TO Address is Required Column
1649 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.Ship_To_Org_ID,
1650 p_param_name => 'SHIP_TO_ORG_ID',
1651 p_api_name => l_api_name);
1652 END IF;
1653 -- Validate Ship to org ID
1654 IF NVL(p_Upd_ProdTxn_Rec.Ship_To_Org_Id,
1655 Fnd_Api.G_MISS_NUM) <> Fnd_Api.G_MISS_NUM
1656 THEN
1657
1658 Validate_PartySiteID(p_Party_ID => l_Customer_Id,
1659 p_Party_Site_Id => p_Product_Txn_Rec.Ship_To_Org_ID,
1660 p_Site_Use_type => C_SITE_USE_TYPE_SHIP_TO);
1661
1662 END IF;
1663
1664 -- Unit_Of_Measure_Code is required column
1665 -- Check for Null value, if so raise error.
1666 IF p_Upd_ProdTxn_Rec.Unit_Of_Measure_Code IS NULL
1667 THEN
1668 Csd_Process_Util.Check_Reqd_Param(p_param_value => p_Product_Txn_Rec.Unit_Of_Measure_Code,
1669 p_param_name => 'UNIT_OF_MEASURE_CODE',
1670 p_api_name => l_api_name);
1671 END IF;
1672
1673 -- Validate Unit of Measure
1674 IF NVL(p_Upd_ProdTxn_Rec.Unit_Of_Measure_Code,
1675 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR
1676 THEN
1677
1678 Validate_UOM(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
1679 p_Unit_Of_Measure => p_Product_Txn_Rec.Unit_Of_Measure_Code);
1680
1681 END IF;
1682
1683 END IF; --
1684
1685 EXCEPTION
1686
1687 WHEN Fnd_Api.G_Exc_Error THEN
1688 x_return_status := Fnd_Api.G_Ret_Sts_Error;
1689 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
1690 p_data => x_msg_data);
1691
1692 WHEN OTHERS THEN
1693 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1694 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_Msg_Lvl_Unexp_Error)
1695 THEN
1696 Fnd_Msg_Pub.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1697 END IF;
1698 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
1699 p_data => x_msg_data);
1700
1701 END Validate_ProductTrxnRec;
1702 /*---------------------------------------------------------------------------*/
1703
1704 /*---------------------------------------------------------------------------*/
1705 /* procedure name: Compare_ProductTrxnRec */
1706 /* description : compares all the input values with database values */
1707 /* SU: This API will compare user passed input values in record structure */
1708 /* UpdateProductTrxn_Rec and Database values captured in Record structure */
1709 /* Product_Txn_rec.This is because whether attributes values can be updated*/
1710 /* depends on product transaction status value and action type values. */
1711 /* These validations are done in the following API and error message is */
1712 /* raised when an attribute value is not supposed to be changed. */
1713 /* On Error : X_Return_Status variable will have the return status value */
1714 /* X_Msg_Count will have the count of messages in message stack */
1715 /* X_Msg_Data will have a value if X_Msg_Count has value 1 */
1716 /* Parameters Required: */
1717 /* p_Upd_ProdTxn_Rec IN user input values are stored in this record */
1718 /* p_Product_Txn_Rec IN database values are stored in this record */
1719 /* x_return_status OUT Standard API paramater */
1720 /* x_msg_count OUT Standard API paramater */
1721 /* x_msg_data OUT Standard API paramater */
1722 /*---------------------------------------------------------------------------*/
1723 PROCEDURE Compare_ProductTrxnRec(p_Upd_ProdTxn_Rec IN Csd_Logistics_Pub.Upd_ProdTxn_Rec_Type,
1724 p_Product_Txn_Rec IN Csd_Process_Pvt.Product_Txn_Rec,
1725 x_return_status OUT NOCOPY VARCHAR2,
1726 x_msg_count OUT NOCOPY NUMBER,
1727 x_msg_data OUT NOCOPY VARCHAR2) IS
1728
1729 -- Define local variables
1730 l_ProdTxnStatus_Meaning VARCHAR2(80);
1731 l_Attribute VARCHAR2(40);
1732
1733 BEGIN
1734
1735 -- Get translated meaning for prod txn status code
1736 l_ProdTxnStatus_Meaning := Get_ProdTrxnStatus_Meaning(p_product_Txn_Rec.Prod_Txn_Status);
1737
1738 -- IF status is different to ENTERED some attributes cannot be changed
1739 IF p_product_Txn_Rec.Prod_Txn_Status <> C_PROD_TXN_STS_ENTERED
1740 THEN
1741
1742 -- Following Attributes cannot be changed.
1743 -- Action Type cannot be changed
1744 IF (p_Upd_ProdTxn_Rec.Action_Type <>
1745 p_Product_Txn_Rec.Action_Type AND
1746 p_Upd_ProdTxn_Rec.Action_Type <> Fnd_Api.G_MISS_CHAR)
1747 THEN
1748 l_Attribute := 'Action Type';
1749 RAISE Fnd_Api.G_EXC_ERROR;
1750 END IF;
1751
1752 -- Action Code cannot be changed
1753 IF (p_Upd_ProdTxn_Rec.Action_Code <>
1754 p_Product_Txn_Rec.Action_Code AND
1755 p_Upd_ProdTxn_Rec.Action_Code <> Fnd_Api.G_MISS_CHAR)
1756 THEN
1757 l_Attribute := 'Action Code';
1758 RAISE Fnd_Api.G_EXC_ERROR;
1759 END IF;
1760
1761 -- Item cannot be changed
1762 IF (p_Upd_ProdTxn_Rec.Inventory_Item_Id <>
1763 p_Product_Txn_Rec.Inventory_Item_Id AND p_Upd_ProdTxn_Rec.Inventory_Item_Id <>
1764 Fnd_Api.G_MISS_NUM)
1765 THEN
1766 l_Attribute := 'Product';
1767 RAISE Fnd_Api.G_EXC_ERROR;
1768 END IF;
1769
1770 -- Txn Billing Type cannot be changed
1771 IF (p_Upd_ProdTxn_Rec.Txn_Billing_Type_Id <>
1772 p_Product_Txn_Rec.Txn_Billing_Type_Id AND
1773 p_Upd_ProdTxn_Rec.Txn_Billing_Type_Id <>
1774 Fnd_Api.G_MISS_NUM)
1775 THEN
1776 l_Attribute := 'Service Activity';
1777 RAISE Fnd_Api.G_EXC_ERROR;
1778 END IF;
1779
1780 -- Price List cannot be changed
1781 IF (p_Upd_ProdTxn_Rec.Price_List_Id <>
1782 p_Product_Txn_Rec.Price_List_Id AND
1783 p_Upd_ProdTxn_Rec.Price_List_Id <> Fnd_Api.G_MISS_NUM)
1784 THEN
1785 l_Attribute := 'Price List';
1786 RAISE Fnd_Api.G_EXC_ERROR;
1787 END IF;
1788
1789 -- Quantity cannot be changed
1790 IF (p_Upd_ProdTxn_Rec.Quantity <>
1791 p_Product_Txn_Rec.Quantity AND
1792 p_Upd_ProdTxn_Rec.Quantity <> Fnd_Api.G_MISS_NUM)
1793 THEN
1794 l_Attribute := 'Quantity';
1795 RAISE Fnd_Api.G_EXC_ERROR;
1796 END IF;
1797
1798 -- Return Reason cannot be changed
1799 IF (p_Upd_ProdTxn_Rec.Return_Reason <>
1800 NVL(p_Product_Txn_Rec.Return_Reason, Fnd_Api.G_MISS_CHAR) AND
1801 p_Upd_ProdTxn_Rec.Return_Reason <>
1802 Fnd_Api.G_MISS_CHAR)
1803 THEN
1804 l_Attribute := 'Return_Reason';
1805 RAISE Fnd_Api.G_EXC_ERROR;
1806 END IF;
1807
1808 -- Return By Date cannot be changed
1809 IF (p_Upd_ProdTxn_Rec.Return_By_Date <>
1810 NVL(p_Product_Txn_Rec.Return_By_Date, Fnd_Api.G_MISS_DATE) AND
1811 p_Upd_ProdTxn_Rec.Return_By_Date <>
1812 Fnd_Api.G_MISS_DATE)
1813 THEN
1814 l_Attribute := 'Return_By_Date';
1815 RAISE Fnd_Api.G_EXC_ERROR;
1816 END IF;
1817
1818 -- PO Number cannot be changed
1819 IF (p_Upd_ProdTxn_Rec.PO_Number <>
1820 NVL(p_Product_Txn_Rec.PO_Number, Fnd_Api.G_MISS_CHAR) AND
1821 p_Upd_ProdTxn_Rec.PO_Number <> Fnd_Api.G_MISS_CHAR)
1822 THEN
1823 l_Attribute := 'PO Number';
1824 RAISE Fnd_Api.G_EXC_ERROR;
1825 END IF;
1826
1827 -- Bill TO Address cannot be changed
1828 IF (p_Upd_ProdTxn_Rec.Invoice_To_Org_ID <>
1829 p_Product_Txn_Rec.Invoice_To_Org_ID AND p_Upd_ProdTxn_Rec.Invoice_To_Org_ID <>
1830 Fnd_Api.G_MISS_NUM)
1831 THEN
1832 l_Attribute := 'Bill_To_Address';
1833 RAISE Fnd_Api.G_EXC_ERROR;
1834 END IF;
1835
1836 -- Ship TO Address cannot be changed
1837 -- SU: Remove NVL function as Ship To Address is required column
1838 IF (p_Upd_ProdTxn_Rec.Ship_To_Org_ID <>
1839 p_Product_Txn_Rec.Ship_To_Org_ID AND p_Upd_ProdTxn_Rec.Ship_To_Org_ID <>
1840 Fnd_Api.G_MISS_NUM)
1841 THEN
1842 l_Attribute := 'Ship_To_Address';
1843 RAISE Fnd_Api.G_EXC_ERROR;
1844 END IF;
1845
1846 -- Unit of Measure cannot be changed
1847 IF (p_Upd_ProdTxn_Rec.Unit_Of_Measure_Code <>
1848 p_Product_Txn_Rec.Unit_Of_Measure_Code AND
1849 p_Upd_ProdTxn_Rec.Unit_Of_Measure_Code <>
1850 Fnd_Api.G_MISS_CHAR)
1851 THEN
1852 l_Attribute := 'Unit Of Measure';
1853 RAISE Fnd_Api.G_EXC_ERROR;
1854 END IF;
1855
1856 -- Charge cannot be changed
1857 IF (p_Upd_ProdTxn_Rec.Charge <>
1858 NVL(p_Product_Txn_Rec.After_Warranty_Cost,
1859 Fnd_Api.G_MISS_NUM) AND
1860 p_Upd_ProdTxn_Rec.Charge <> Fnd_Api.G_MISS_NUM)
1861 THEN
1862 l_Attribute := 'Charge';
1863 RAISE Fnd_Api.G_EXC_ERROR;
1864 END IF;
1865
1866 -- SU: Move this validation in if statement status <> ENTERED
1867 IF (p_Upd_ProdTxn_Rec.Revision <>
1868 NVL(p_Product_Txn_Rec.Revision, Fnd_Api.G_MISS_CHAR) AND
1869 p_Upd_ProdTxn_Rec.Revision <> Fnd_Api.G_MISS_CHAR)
1870 THEN
1871 l_Attribute := 'Revision';
1872 RAISE Fnd_Api.G_EXC_ERROR;
1873 END IF;
1874
1875 -- IB Ref Number cannot be changed once product transaction line is interfaced
1876 IF (p_Upd_ProdTxn_Rec.source_Instance_Id <>
1877 NVL(p_Product_Txn_Rec.source_Instance_Id,
1878 Fnd_Api.G_MISS_NUM) AND p_Upd_ProdTxn_Rec.source_Instance_Id <>
1879 Fnd_Api.G_MISS_NUM)
1880 THEN
1881 l_Attribute := 'Source IB Ref Num';
1882 RAISE Fnd_Api.G_EXC_ERROR;
1883 END IF;
1884
1885 -- Lot Number cannot be changed
1886 IF (p_Upd_ProdTxn_Rec.Lot_Number <>
1887 NVL(p_Product_Txn_Rec.Lot_Number, 'NULL') AND
1888 NVL(p_Upd_ProdTxn_Rec.Lot_Number, Fnd_Api.G_MISS_CHAR) <>
1889 Fnd_Api.G_MISS_CHAR)
1890 THEN
1891 l_Attribute := 'Lot_Number';
1892 RAISE Fnd_Api.G_EXC_ERROR;
1893 END IF;
1894
1895 -- Serial Number cannot be changed
1896 IF (p_Upd_ProdTxn_Rec.source_Serial_Number <>
1897 NVL(p_Product_Txn_Rec.source_Serial_Number,
1898 Fnd_Api.G_MISS_CHAR) AND p_Upd_ProdTxn_Rec.source_Serial_Number <>
1899 Fnd_Api.G_MISS_CHAR)
1900 THEN
1901 l_Attribute := 'Serial_Number';
1902 RAISE Fnd_Api.G_EXC_ERROR;
1903 END IF;
1904
1905 END IF; -- End IF status is different to ENTERED
1906
1907 IF (p_Product_Txn_Rec.Action_Type IN
1908 (C_ACTION_TYPE_RMA, C_ACTION_TYPE_WALK_IN_RECPT) AND
1909 p_Product_Txn_Rec.Order_Header_Id IS NOT NULL)
1910 THEN
1911 -- Sub Inventory cannot be changed
1912 IF (p_Upd_ProdTxn_Rec.Sub_Inventory <>
1913 NVL(p_Product_Txn_Rec.Sub_Inventory, Fnd_Api.G_MISS_CHAR) AND
1914 p_Upd_ProdTxn_Rec.Sub_Inventory <>
1915 Fnd_Api.G_MISS_CHAR)
1916 THEN
1917 l_Attribute := 'Sub Inventory';
1918 RAISE Fnd_Api.G_EXC_ERROR;
1919 END IF;
1920
1921 ELSIF p_Product_Txn_Rec.Prod_Txn_Status = C_PROD_TXN_STS_RELEASED
1922 THEN
1923
1924 IF (p_Upd_ProdTxn_Rec.Sub_Inventory <>
1925 NVL(p_Product_Txn_Rec.Sub_Inventory, Fnd_Api.G_MISS_CHAR) AND
1926 p_Upd_ProdTxn_Rec.Sub_Inventory <>
1927 Fnd_Api.G_MISS_CHAR)
1928 THEN
1929 l_Attribute := 'Sub Inventory';
1930 RAISE Fnd_Api.G_EXC_ERROR;
1931
1932 END IF;
1933
1934 END IF; -- Product Transaction Statu is Released
1935
1936 IF p_product_Txn_Rec.Prod_Txn_Status = C_PROD_TXN_STS_SHIPPED
1937 THEN
1938
1939 --SU:02/24 Following validation is added today. Since sub Inventory is also not update able once item is shipped
1940 IF (p_Upd_ProdTxn_Rec.Sub_Inventory <>
1941 NVL(p_Product_Txn_Rec.Sub_Inventory, Fnd_Api.G_MISS_CHAR) AND
1942 p_Upd_ProdTxn_Rec.Sub_Inventory <>
1943 Fnd_Api.G_MISS_CHAR)
1944 THEN
1945 l_Attribute := 'Sub Inventory';
1946 RAISE Fnd_Api.G_EXC_ERROR;
1947 END IF;
1948
1949 END IF; --
1950
1951 EXCEPTION
1952 WHEN Fnd_Api.G_EXC_ERROR THEN
1953 --JG:02/25: Corrected message code. Removed space at the end.
1954 Fnd_Message.SET_NAME('CSD', 'CSD_PRODTXN_ATTRB_CHANGED');
1955 Fnd_Message.SET_TOKEN('PRODTXN_STATUS',
1956 l_ProdTxnStatus_Meaning);
1957 Fnd_Message.SET_TOKEN('ATTRB', l_Attribute);
1958 Fnd_Msg_Pub.ADD;
1959 x_return_status := Fnd_Api.G_Ret_Sts_Error;
1960 Fnd_Msg_Pub.Count_AND_Get(p_count => x_msg_count,
1961 p_data => x_msg_data);
1962
1963 END Compare_ProductTrxnRec;
1964 /*---------------------------------------------------------------------------*/
1965
1966 /*---------------------------------------------------------------------------*/
1967 /* procedure name: Get_ProdTrxnStatus_Meaning */
1968 /* description : gets prod txn status meaning for a prod txn status code */
1969 /* in fnd lookups */
1970 /* Parameters Required: */
1971 /* p_ProdTxnStatus_Code IN Lookup code for product transaction status */
1972 /*---------------------------------------------------------------------------*/
1973 FUNCTION Get_ProdTrxnStatus_Meaning(p_ProdTxnStatus_Code IN VARCHAR2)
1974 RETURN VARCHAR2 IS
1975
1976 -- Define Local Variables
1977 l_ProdTxn_Status_Meaning VARCHAR2(80);
1978
1979 CURSOR ProdTxnStatus_Meaning_Cur_Type(p_ProdTxnStatus_Code IN VARCHAR2) IS
1980 SELECT Meaning
1981 FROM Fnd_Lookups
1982 WHERE Lookup_Type = 'CSD_PRODUCT_TXN_STATUS'
1983 AND Lookup_Code = p_ProdTxnStatus_Code;
1984
1985 BEGIN
1986
1987 OPEN ProdTxnStatus_Meaning_Cur_Type(p_ProdTxnStatus_Code);
1988 FETCH ProdTxnStatus_Meaning_Cur_Type
1989 INTO L_ProdTxn_Status_Meaning;
1990 RETURN L_ProdTxn_Status_Meaning;
1991
1992 END Get_ProdTrxnStatus_Meaning;
1993 /*---------------------------------------------------------------------------*/
1994
1995 /*---------------------------------------------------------------------------*/
1996 /* procedure name: Get_ItemAttributes */
1997 /* description : */
1998 /* SU: Gets item attributes like serial number control code, revision */
1999 /* qty control code, lot number control code, IB Flag for a givent item */
2000 /* in service validation organzation */
2001 /* Parameters Required: */
2002 /* p_Inventory_Item_Id IN Item identifier */
2003 /* x_ItemAttributes OUT returned values include serial_code, */
2004 /* Revision_Code, Lot_Code and IB_Flag for a given Item */
2005 /*---------------------------------------------------------------------------*/
2006 PROCEDURE Get_ItemAttributes(p_Inventory_Item_Id IN NUMBER,p_inv_org_id IN NUMBER,
2007 x_ItemAttributes OUT NOCOPY ItemAttributes_Rec_Type) IS
2008 BEGIN
2009
2010 SELECT serial_number_control_code,
2011 Revision_Qty_Control_Code,
2012 Lot_Control_Code,
2013 NVL(Comms_NL_Trackable_Flag, 'N'),
2014 RESERVABLE_TYPE
2015 INTO x_ItemAttributes.serial_code,
2016 x_ItemAttributes.Revision_Code,
2017 x_ItemAttributes.Lot_Code,
2018 x_ItemAttributes.IB_Flag,
2019 x_itemAttributes.reservable_type
2020 FROM mtl_system_items
2021 WHERE inventory_item_id = p_Inventory_Item_Id
2022 AND organization_id = p_Inv_Org_id;
2023
2024 EXCEPTION
2025 WHEN OTHERS THEN
2026 Fnd_Message.SET_NAME('CSD', 'CSD_INVALID_ITEM');
2027 Fnd_Message.SET_TOKEN('ITEM_ID', p_Inventory_Item_Id);
2028 Fnd_Msg_Pub.ADD;
2029
2030 RAISE Fnd_Api.G_EXC_ERROR;
2031
2032 END Get_ItemAttributes;
2033
2034
2035 /*************************************************************************/
2036 /* procedure : get_order_rec */
2037 /* Desc: Get acct, party details into order rec */
2038 /*************************************************************************/
2039
2040 FUNCTION get_order_rec (p_repair_line_id IN NUMBER)
2041 RETURN Csd_Process_Pvt.om_interface_rec
2042 IS
2043 l_incident_id NUMBER;
2044 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.get_order_rec';
2045 x_order_rec Csd_Process_Pvt.om_interface_rec;
2046
2047 CURSOR cur_order_rec(p_incident_id IN NUMBER) IS
2048 SELECT customer_id, account_id
2049 FROM cs_incidents_all_b
2050 WHERE incident_id = p_incident_id;
2051
2052 BEGIN
2053
2054 /*---------------------------------------------------------------------------*/
2055 -- Get the incident Id for the repair line
2056 BEGIN
2057 SELECT incident_id
2058 INTO l_incident_id
2059 FROM CSD_REPAIRS
2060 WHERE repair_line_id = p_repair_line_id;
2061 EXCEPTION
2062 WHEN OTHERS THEN
2063 Fnd_Message.SET_NAME('CSD', 'CSD_INV_REP_LINE_ID');
2064 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID', p_repair_line_id);
2065 Fnd_Msg_Pub.ADD;
2066 Debug('Invalid repair line id =' || p_repair_line_id,
2067 l_mod_name,
2068 1);
2069 RAISE Fnd_Api.G_EXC_ERROR;
2070 END;
2071
2072 IF l_incident_id IS NOT NULL
2073 THEN
2074 OPEN cur_order_rec(l_incident_id);
2075 FETCH cur_order_rec
2076 INTO x_order_rec.party_id, x_order_rec.account_id;
2077 CLOSE cur_order_rec;
2078 ELSE
2079 Fnd_Message.SET_NAME('CSD', 'CSD_API_INV_SR_ID');
2080 Fnd_Message.SET_TOKEN('INCIDENT_ID', l_incident_id);
2081 Fnd_Msg_Pub.ADD;
2082 Debug('incident Id missing ', l_mod_name, 1);
2083 RAISE Fnd_Api.G_EXC_ERROR;
2084 END IF;
2085
2086 -- assigning values for the order record
2087 x_order_rec.incident_id := l_incident_id;
2088 x_order_rec.org_id := Cs_Std.get_item_valdn_orgzn_id;
2089
2090 RETURN x_order_rec;
2091
2092
2093 END get_order_rec;
2094
2095 /*************************************************************************/
2096 /* procedure : get_prodtxn_db_attr */
2097 /* Desc: Gets the product txn record attributes from database */
2098 /*************************************************************************/
2099
2100 FUNCTION get_prodtxn_db_attr (p_product_txn_id IN NUMBER)
2101 RETURN Csd_Logistics_Util.PRODTXN_DB_ATTR_REC
2102 IS
2103
2104 CURSOR prod_txn(p_prod_txn_id IN NUMBER) IS
2105 SELECT estimate_detail_id,
2106 repair_line_id,
2107 interface_to_om_flag,
2108 book_sales_order_flag,
2109 release_sales_order_flag,
2110 ship_sales_order_flag,
2111 object_version_number
2112 FROM CSD_PRODUCT_TRANSACTIONS
2113 WHERE product_transaction_id = p_prod_txn_id;
2114
2115 x_prodtxn_db_attr Csd_Logistics_Util.PRODTXN_DB_ATTR_REC;
2116
2117
2118 BEGIN
2119 IF NVL(p_product_txn_id, Fnd_Api.G_MISS_NUM) <> Fnd_Api.G_MISS_NUM
2120 THEN
2121
2122 OPEN prod_txn(p_product_txn_id);
2123 FETCH prod_txn
2124 INTO x_prodtxn_db_attr.est_detail_id,
2125 x_prodtxn_db_attr.repair_line_id,
2126 x_prodtxn_db_attr.curr_submit_order_flag,
2127 x_prodtxn_db_attr.curr_book_order_flag,
2128 x_prodtxn_db_attr.curr_release_order_flag,
2129 x_prodtxn_db_attr.curr_ship_order_flag,
2130 x_prodtxn_db_attr.object_version_num;
2131 IF( prod_txn%NOTFOUND) THEN
2132 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2133 END IF;
2134 CLOSE prod_txn;
2135
2136 END IF;
2137
2138 RETURN x_prodtxn_db_attr;
2139
2140 END get_prodtxn_db_attr;
2141
2142
2143 /*------------------------------------------------------------------------*/
2144 /* procedure name: upd_prodtxn_n_chrgline */
2145 /* description : */
2146 /* Updates the prod txn record in Depot schema and charge line */
2147 /* Parameters Required: */
2148 /* p_product_txn_rec IN product transaction record */
2149 /* x_estimate_detail_id OUT return status */
2150 /*------------------------------------------------------------------------*/
2151 PROCEDURE upd_prodtxn_n_chrgline
2152 (
2153 p_product_txn_rec IN OUT NOCOPY Csd_Process_Pvt.PRODUCT_TXN_REC,
2154 p_prodtxn_db_attr IN Csd_Logistics_Util.PRODTXN_DB_ATTR_REC,
2155 x_estimate_detail_id OUT NOCOPY NUMBER,
2156 x_repair_line_id OUT NOCOPY NUMBER,
2157 x_add_to_order_flag OUT NOCOPY VARCHAR2,
2158 x_add_to_order_id OUT NOCOPY NUMBER,
2159 x_transaction_type_id OUT NOCOPY NUMBER
2160 ) IS
2161
2162
2163 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.upd_prodtxn_n_chrgline';
2164 C_STATEMENT_LEVEL CONSTANT NUMBER := 4; -- temporarily changed to 4 from 1 since the fnd profile can not be changed.
2165 C_EXCEPTION_LEVEL CONSTANT NUMBER := 4;
2166 l_tmp_char VARCHAR2(1) ;
2167 l_est_detail_id NUMBER;
2168 l_check VARCHAR2(1);
2169 l_upd_charge_flag VARCHAR2(1);
2170 l_bus_process_id NUMBER;
2171
2172 l_Charges_Rec Cs_Charge_Details_Pub.charges_rec_type;
2173 l_return_status VARCHAR2(1);
2174 l_msg_data VARCHAR2(2000);
2175 l_msg_count NUMBER;
2176 l_serial_flag BOOLEAN;
2177 l_repair_line_id NUMBER;
2178 l_tmp_id NUMBER;
2179
2180
2181 --R12 Development Changes Begin
2182 CURSOR cur_pick_rules(p_pick_rule_id NUMBER) IS
2183 SELECT 'x'
2184 FROM wsh_picking_rules
2185 WHERE picking_rule_id = p_pick_rule_id
2186 AND SYSDATE BETWEEN NVL(start_Date_Active, SYSDATE) AND
2187 NVL(end_Date_active, SYSDATE + 1);
2188 --R12 Development Changes End
2189
2190
2191 BEGIN
2192 Debug('At the Beginning of update_depot_prod_txn', l_mod_name, C_STATEMENT_LEVEL);
2193
2194
2195 Debug('Product Txn Id =' ||
2196 p_product_txn_rec.product_transaction_id,
2197 l_mod_name,
2198 C_STATEMENT_LEVEL);
2199 Debug('Validate Product txn id', l_mod_name, C_STATEMENT_LEVEL);
2200
2201
2202 l_repair_line_id := p_product_txn_rec.repair_line_id;
2203 l_est_detail_id := p_product_txn_rec.estimate_detail_id;
2204
2205 -- Validate the prod_txn_id if it exists in csd_product_transactions
2206 IF NOT
2207 (Csd_Process_Util.Validate_prod_txn_id(p_prod_txn_id => p_product_txn_rec.product_transaction_id))
2208 THEN
2209 RAISE Fnd_Api.G_EXC_ERROR;
2210 END IF;
2211
2212 Debug('Validate product txn status', l_mod_name, C_STATEMENT_LEVEL);
2213 Debug('p_product_txn_rec.PROD_TXN_STATUS =' ||
2214 p_product_txn_rec.PROD_TXN_STATUS,
2215 l_mod_name,
2216 C_STATEMENT_LEVEL);
2217
2218 -- Validate the PROD_TXN_STATUS
2219 IF NVL(p_product_txn_rec.PROD_TXN_STATUS, Fnd_Api.G_MISS_CHAR) <>
2220 Fnd_Api.G_MISS_CHAR
2221 THEN
2222 BEGIN
2223 SELECT 'X'
2224 INTO l_check
2225 FROM fnd_lookups
2226 WHERE lookup_type = 'CSD_PRODUCT_TXN_STATUS'
2227 AND lookup_code = p_product_txn_rec.PROD_TXN_STATUS;
2228 EXCEPTION
2229 WHEN NO_DATA_FOUND THEN
2230 Fnd_Message.SET_NAME('CSD', 'CSD_ERR_PROD_TXN_STATUS');
2231 Fnd_Msg_Pub.ADD;
2232 RAISE Fnd_Api.G_EXC_ERROR;
2233 END;
2234 END IF;
2235
2236 Debug('Validate action type', l_mod_name, C_STATEMENT_LEVEL);
2237
2238 IF NVL(p_product_txn_rec.action_type, Fnd_Api.G_MISS_CHAR) <>
2239 Fnd_Api.G_MISS_CHAR
2240 THEN
2241 -- Validate the Action Type
2242 IF NOT
2243 (Csd_Process_Util.Validate_action_type(p_action_type => p_product_txn_rec.action_type))
2244 THEN
2245 RAISE Fnd_Api.G_EXC_ERROR;
2246 END IF;
2247 END IF;
2248
2249 Debug('Validate action code', l_mod_name, C_STATEMENT_LEVEL);
2250
2251 IF NVL(p_product_txn_rec.action_code, Fnd_Api.G_MISS_CHAR) <>
2252 Fnd_Api.G_MISS_CHAR
2253 THEN
2254 -- Validate the Action code
2255 IF NOT
2256 (Csd_Process_Util.Validate_action_code(p_action_code => p_product_txn_rec.action_code))
2257 THEN
2258 RAISE Fnd_Api.G_EXC_ERROR;
2259 END IF;
2260 END IF;
2261
2262 -- swai bug 6903344
2263 -- Derive the line type and line category code
2264 -- from the transaction billing type
2265 Csd_Process_Util.GET_LINE_TYPE(p_txn_billing_type_id => p_product_txn_rec.txn_billing_type_id,
2266 p_org_id => p_product_txn_rec.organization_id,
2267 x_line_type_id => p_product_txn_rec.line_type_id,
2268 x_line_category_code => p_product_txn_rec.line_category_code,
2269 x_return_status => l_return_status);
2270 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
2271 THEN
2272 Debug('csd_process_util.get_line_type failed',
2273 l_mod_name,
2274 C_STATEMENT_LEVEL);
2275 RAISE Fnd_Api.G_EXC_ERROR;
2276 END IF;
2277
2278 --R12 Development pick rule changes begin
2279 Debug('Validating picking rule if passed[' ||
2280 p_product_txn_rec.picking_rule_id || ']',
2281 l_mod_name,
2282 C_STATEMENT_LEVEL);
2283 IF (p_product_txn_rec.picking_rule_id <> NULL)
2284 THEN
2285 OPEN cur_pick_rules(p_product_txn_rec.picking_rule_id);
2286 FETCH cur_pick_rules
2287 INTO l_tmp_char;
2288 IF (cur_pick_rules%NOTFOUND)
2289 THEN
2290 Fnd_Message.SET_NAME('CSD', 'CSD_INV_PICK_RULE');
2291 Fnd_Msg_Pub.ADD;
2292 RAISE Fnd_Api.G_EXC_ERROR;
2293 END IF;
2294 END IF;
2295 --R12 Development pick rule changes End
2296
2297
2298 IF NVL(l_est_detail_id, Fnd_Api.G_MISS_NUM) <>
2299 Fnd_Api.G_MISS_NUM
2300 THEN
2301 IF l_est_detail_id <> p_prodtxn_db_attr.est_detail_id
2302 THEN
2303 Debug('The estimate detail id cannot to changed',
2304 l_mod_name,
2305 C_STATEMENT_LEVEL);
2306 RAISE Fnd_Api.G_EXC_ERROR;
2307 END IF;
2308 ELSE
2309 l_est_detail_id := p_prodtxn_db_attr.est_detail_id;
2310 END IF;
2311
2312 IF NVL(l_repair_line_id, Fnd_Api.G_MISS_NUM) <>
2313 Fnd_Api.G_MISS_NUM
2314 THEN
2315 IF l_repair_line_id <> p_prodtxn_db_attr.repair_line_id
2316 THEN
2317 Debug('The repair line id cannot to changed',
2318 l_mod_name,
2319 C_STATEMENT_LEVEL);
2320 RAISE Fnd_Api.G_EXC_ERROR;
2321 END IF;
2322 ELSE
2323 l_repair_line_id := p_prodtxn_db_attr.repair_line_id;
2324 END IF;
2325
2326 BEGIN
2327 SELECT 'x'
2328 INTO l_check
2329 FROM cs_estimate_details
2330 WHERE estimate_detail_id = l_est_detail_id
2331 AND order_header_id IS NULL;
2332 l_upd_charge_flag := 'Y';
2333 EXCEPTION
2334 WHEN NO_DATA_FOUND THEN
2335 l_upd_charge_flag := 'N';
2336 WHEN TOO_MANY_ROWS THEN
2337 Debug('Too many business processes ', l_mod_name, C_STATEMENT_LEVEL);
2338 END;
2339
2340 BEGIN
2341 SELECT business_process_id
2342 INTO l_bus_process_id
2343 FROM cs_estimate_details
2344 WHERE estimate_detail_id = l_est_detail_id;
2345 EXCEPTION
2346 WHEN NO_DATA_FOUND THEN
2347 Debug('No Business business_process_id', l_mod_name, C_STATEMENT_LEVEL);
2348 RAISE Fnd_Api.G_EXC_ERROR;
2349 WHEN TOO_MANY_ROWS THEN
2350 Debug('Too many business_process_id', l_mod_name, C_STATEMENT_LEVEL);
2351 RAISE Fnd_Api.G_EXC_ERROR;
2352 END;
2353
2354 Debug('contract_line_id =' || p_product_txn_rec.contract_id,
2355 l_mod_name,
2356 C_STATEMENT_LEVEL);
2357 Debug('l_bus_process_id =' || l_bus_process_id, l_mod_name, C_STATEMENT_LEVEL);
2358
2359 IF ((p_product_txn_rec.transaction_type_id IS NULL) OR
2360 (p_product_txn_rec.transaction_type_id = Fnd_Api.G_MISS_NUM)) AND
2361 (p_product_txn_rec.txn_billing_type_id IS NOT NULL)
2362 THEN
2363 BEGIN
2364 SELECT transaction_type_id
2365 INTO x_transaction_type_id
2366 FROM cs_txn_billing_types
2367 WHERE txn_billing_type_id =
2368 p_product_txn_rec.txn_billing_type_id;
2369 --
2370 -- Fix for bug#6215270
2371 --
2372 p_product_txn_rec.transaction_type_id := x_transaction_type_id;
2373
2374 EXCEPTION
2375 WHEN NO_DATA_FOUND THEN
2376 Debug('No Row found for the txn_billing_type_id=' ||
2377 TO_CHAR(p_product_txn_rec.txn_billing_type_id),
2378 l_mod_name,
2379 C_STATEMENT_LEVEL);
2380 WHEN OTHERS THEN
2381 Debug('When others exception at - Transaction type id',
2382 l_mod_name,
2383 C_STATEMENT_LEVEL);
2384 END;
2385 Debug('transaction_type_id :' ||
2386 TO_CHAR(x_transaction_type_id),
2387 l_mod_name,
2388 C_STATEMENT_LEVEL);
2389 END IF;
2390
2391 IF NVL(p_product_txn_rec.contract_id, Fnd_Api.G_MISS_NUM) <>
2392 Fnd_Api.G_MISS_NUM
2393 THEN
2394 NULL;
2395 /*********************************
2396 Gettting the coverage details code is removed from here.
2397 This is because the contracts no longer needs coverage
2398 details for getting the discount. Contract line id
2399 is enough to get the discounted price.
2400 *********************************/
2401 END IF;
2402
2403 -- swai: bug 5931926 - 12.0.2 3rd party logistics
2404 -- Instead of adding 3rd party action types to if-then statement,
2405 -- we are commenting the code out altogether. Currently, the
2406 -- if conditions do not allow any product transaction lines
2407 -- through except walk-in-receipt, which is no longer supported.
2408 -- We should allow RMA line creation without Serial number for
2409 -- all action types.
2410 /***********
2411 IF NVL(p_product_txn_rec.inventory_item_id, Fnd_Api.G_MISS_NUM) <>
2412 Fnd_Api.G_MISS_NUM
2413 THEN
2414
2415 l_serial_flag := Csd_Process_Util.Is_item_serialized(p_product_txn_rec.inventory_item_id);
2416
2417 IF l_serial_flag AND
2418 -- Changing it from serial_number to source_serial_number 11.5.10
2419 NVL(p_product_txn_rec.source_serial_number,
2420 Fnd_Api.G_MISS_CHAR) = Fnd_Api.G_MISS_CHAR AND
2421 p_product_txn_rec.action_type NOT IN
2422 ('SHIP', 'WALK_IN_ISSUE') AND
2423 (p_product_txn_rec.action_code <> 'LOANER' AND
2424 p_product_txn_rec.action_type <> 'RMA')
2425 THEN
2426 Fnd_Message.SET_NAME('CSD', 'CSD_API_SERIAL_NUM_MISSING');
2427 Fnd_Message.SET_TOKEN('INVENTORY_ITEM_ID',
2428 p_product_txn_rec.inventory_item_id);
2429 Fnd_Msg_Pub.ADD;
2430 Debug('Serial Number missing for inventory_item_id =' ||
2431 p_product_txn_rec.inventory_item_id,
2432 l_mod_name,
2433 C_STATEMENT_LEVEL);
2434 RAISE Fnd_Api.G_EXC_ERROR;
2435 END IF;
2436 END IF;
2437 ***********/
2438
2439 Debug('l_upd_charge_flag =' || l_upd_charge_flag, l_mod_name, C_STATEMENT_LEVEL);
2440
2441
2442 IF l_upd_charge_flag = 'Y'
2443 THEN
2444 IF (p_product_txn_rec.new_order_flag = 'N')
2445 THEN
2446 x_add_to_order_flag := 'Y';
2447 x_add_to_order_id := p_product_txn_rec.add_to_order_id;
2448 -- Fix for bug#4051707
2449 p_product_txn_rec.add_to_order_flag := 'Y';
2450 p_product_txn_rec.order_header_id := p_product_txn_rec.add_to_order_id;
2451
2452 ELSIF (p_product_txn_rec.new_order_flag = 'Y')
2453 THEN
2454 x_add_to_order_flag := 'F';
2455 x_add_to_order_id := Fnd_Api.G_MISS_NUM;
2456 -- Fix for bug#4051707
2457 p_product_txn_rec.add_to_order_flag := 'F';
2458 p_product_txn_rec.order_header_id := Fnd_Api.G_MISS_NUM;
2459 END IF;
2460
2461 Debug('l_upd_charge_flag =' || l_upd_charge_flag,
2462 l_mod_name,
2463 C_STATEMENT_LEVEL);
2464 Debug('p_product_txn_rec.new_order_flag =' ||
2465 p_product_txn_rec.new_order_flag,
2466 l_mod_name,
2467 C_STATEMENT_LEVEL);
2468 Debug('p_product_txn_rec.add_to_order_flag =' ||
2469 p_product_txn_rec.add_to_order_flag,
2470 l_mod_name,
2471 C_STATEMENT_LEVEL);
2472 Debug('p_product_txn_rec.order_header_id =' ||
2473 p_product_txn_rec.order_header_id,
2474 l_mod_name,
2475 C_STATEMENT_LEVEL);
2476
2477 Csd_Process_Util.CONVERT_TO_CHG_REC(p_prod_txn_rec => p_product_txn_rec,
2478 x_charges_rec => l_Charges_Rec,
2479 x_return_status => l_return_status);
2480
2481 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
2482 THEN
2483 Debug('csd_process_util.convert_to_chg_rec failed',
2484 l_mod_name,
2485 C_STATEMENT_LEVEL);
2486 RAISE Fnd_Api.G_EXC_ERROR;
2487 END IF;
2488
2489 l_Charges_Rec.estimate_detail_id := l_est_detail_id;
2490 l_Charges_Rec.business_process_id := l_bus_process_id;
2491
2492 Debug('Call process_charge_lines to update charge lines ',
2493 l_mod_name,
2494 C_STATEMENT_LEVEL);
2495 Debug('Estimate Detail Id = ' ||
2496 l_Charges_Rec.estimate_detail_id,
2497 l_mod_name,
2498 C_STATEMENT_LEVEL);
2499
2500 Csd_Process_Pvt.PROCESS_CHARGE_LINES(p_api_version => 1.0,
2501 p_commit => Fnd_Api.g_false,
2502 p_init_msg_list => Fnd_Api.g_false,
2503 p_validation_level => Fnd_Api.g_valid_level_full,
2504 p_action => 'UPDATE',
2505 p_Charges_Rec => l_Charges_Rec,
2506 x_estimate_detail_id => l_tmp_id,
2507 x_return_status => l_return_status,
2508 x_msg_count => l_msg_count,
2509 x_msg_data => l_msg_data);
2510
2511 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
2512 THEN
2513 Debug('process_charge_lines failed ', l_mod_name, C_STATEMENT_LEVEL);
2514 RAISE Fnd_Api.G_EXC_ERROR;
2515 END IF;
2516
2517 END IF;
2518
2519 Debug('Call csd_product_transactions_pkg.update_row to update the prod txn',
2520 l_mod_name,
2521 C_STATEMENT_LEVEL);
2522
2523 Debug('estimate_details_id =['||l_est_detail_id||']',
2524 l_mod_name,
2525 C_STATEMENT_LEVEL);
2526
2527
2528 Csd_Product_Transactions_Pkg.UPDATE_ROW(p_PRODUCT_TRANSACTION_ID => p_product_txn_rec.PRODUCT_TRANSACTION_ID,
2529 p_REPAIR_LINE_ID => l_REPAIR_LINE_ID,
2530 p_ESTIMATE_DETAIL_ID => l_est_detail_id,
2531 p_ACTION_TYPE => p_product_txn_rec.ACTION_TYPE,
2532 p_ACTION_CODE => p_product_txn_rec.ACTION_CODE,
2533 p_LOT_NUMBER => p_product_txn_rec.LOT_NUMBER,
2534 p_SUB_INVENTORY => p_product_txn_rec.SUB_INVENTORY,
2535 p_INTERFACE_TO_OM_FLAG => Fnd_Api.G_MISS_CHAR,
2536 p_BOOK_SALES_ORDER_FLAG => Fnd_Api.G_MISS_CHAR,
2537 p_RELEASE_SALES_ORDER_FLAG => Fnd_Api.G_MISS_CHAR,
2538 p_SHIP_SALES_ORDER_FLAG => Fnd_Api.G_MISS_CHAR,
2539 p_PROD_TXN_STATUS => Fnd_Api.G_MISS_CHAR,
2540 p_PROD_TXN_CODE => p_product_txn_rec.PROD_TXN_CODE,
2541 p_LAST_UPDATE_DATE => SYSDATE,
2542 p_CREATION_DATE => SYSDATE,
2543 p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
2544 p_CREATED_BY => Fnd_Global.USER_ID,
2545 p_LAST_UPDATE_LOGIN => Fnd_Global.USER_ID,
2546 p_ATTRIBUTE1 => p_product_txn_rec.ATTRIBUTE1,
2547 p_ATTRIBUTE2 => p_product_txn_rec.ATTRIBUTE2,
2548 p_ATTRIBUTE3 => p_product_txn_rec.ATTRIBUTE3,
2549 p_ATTRIBUTE4 => p_product_txn_rec.ATTRIBUTE4,
2550 p_ATTRIBUTE5 => p_product_txn_rec.ATTRIBUTE5,
2551 p_ATTRIBUTE6 => p_product_txn_rec.ATTRIBUTE6,
2552 p_ATTRIBUTE7 => p_product_txn_rec.ATTRIBUTE7,
2553 p_ATTRIBUTE8 => p_product_txn_rec.ATTRIBUTE8,
2554 p_ATTRIBUTE9 => p_product_txn_rec.ATTRIBUTE9,
2555 p_ATTRIBUTE10 => p_product_txn_rec.ATTRIBUTE10,
2556 p_ATTRIBUTE11 => p_product_txn_rec.ATTRIBUTE11,
2557 p_ATTRIBUTE12 => p_product_txn_rec.ATTRIBUTE12,
2558 p_ATTRIBUTE13 => p_product_txn_rec.ATTRIBUTE13,
2559 p_ATTRIBUTE14 => p_product_txn_rec.ATTRIBUTE14,
2560 p_ATTRIBUTE15 => p_product_txn_rec.ATTRIBUTE15,
2561 p_CONTEXT => p_product_txn_rec.CONTEXT,
2562 p_OBJECT_VERSION_NUMBER => p_prodtxn_db_attr.object_version_num,
2563 P_SOURCE_SERIAL_NUMBER => p_product_txn_rec.source_serial_number,
2564 P_SOURCE_INSTANCE_ID => p_product_txn_rec.source_instance_id,
2565 P_NON_SOURCE_SERIAL_NUMBER => p_product_txn_rec.non_source_serial_number,
2566 P_NON_SOURCE_INSTANCE_ID => p_product_txn_rec.non_source_Instance_id,
2567 P_REQ_HEADER_ID => p_product_txn_rec.Req_Header_Id,
2568 P_REQ_LINE_ID => p_product_txn_rec.Req_Line_Id,
2569 P_ORDER_HEADER_ID => p_product_txn_rec.Order_Header_Id,
2570 P_ORDER_LINE_ID => p_product_txn_rec.Order_Line_Id,
2571 P_PRD_TXN_QTY_RECEIVED => p_product_txn_rec.Prd_Txn_Qty_Received,
2572 P_PRD_TXN_QTY_SHIPPED => p_product_txn_rec.Prd_Txn_Qty_Shipped,
2573 P_SUB_INVENTORY_RCVD => p_product_txn_rec.Sub_Inventory_Rcvd,
2574 P_LOT_NUMBER_RCVD => p_product_txn_rec.Lot_Number_Rcvd,
2575 P_LOCATOR_ID => p_product_txn_rec.Locator_Id,
2576 --R12 Development Changes
2577 p_picking_rule_id => p_product_txn_rec.picking_rule_id,
2578 P_PROJECT_ID => p_product_txn_rec.project_id,
2579 P_TASK_ID => p_product_txn_rec.task_id,
2580 P_UNIT_NUMBER => p_product_txn_rec.unit_number,
2581 P_INTERNAL_PO_HEADER_ID => p_product_txn_rec.internal_po_header_id); -- swai: bug 6148019
2582
2583 Debug('Updated the prod txn id =' ||
2584 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
2585 l_mod_name,
2586 C_STATEMENT_LEVEL);
2587
2588
2589 x_repair_line_id := l_repair_line_id;
2590 x_estimate_detail_id := l_est_detail_id;
2591
2592
2593
2594 END upd_prodtxn_n_chrgline;
2595
2596
2597
2598 /*---------------------------------------------------------------------------*/
2599 /* procedure name: interface_prodtxn */
2600 /* description : */
2601 /* interfaces a given product transaction including all the prod txns */
2602 /* under that incident id. */
2603 /* Parameters Required: */
2604 /* p_product_txn_id IN product transaction record */
2605 /* x_return_status OUT return status */
2606 /*---------------------------------------------------------------------------*/
2607 PROCEDURE interface_prodtxn
2608 (
2609 x_return_status OUT NOCOPY VARCHAR2,
2610 p_product_txn_rec IN Csd_Process_Pvt.PRODUCT_TXN_REC,
2611 p_prodtxn_db_attr IN Csd_Logistics_Util.PRODTXN_DB_ATTR_REC,
2612 px_order_rec IN OUT NOCOPY Csd_Process_Pvt.om_interface_rec
2613 ) IS
2614
2615
2616
2617 l_incident_id NUMBER;
2618 l_party_id NUMBER;
2619 l_account_id NUMBER;
2620 l_rev_ctrl_code NUMBER;
2621 l_return_status VARCHAR2(1);
2622 l_msg_count NUMBER;
2623 l_msg_data VARCHAR2(2000);
2624 l_dummy VARCHAR2(1);
2625 l_rev_ctl_code NUMBER;
2626
2627 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.interface_prodtxn';
2628
2629 l_order_line_id NUMBER;
2630 l_sr_account_id NUMBER; -- swai: bug 6001057
2631
2632 --taklam
2633 l_Line_Tbl_Type OE_ORDER_PUB.Line_Tbl_Type;
2634 x_Line_Tbl_Type OE_ORDER_PUB.Line_Tbl_Type;
2635 l_p_ship_from_org_id NUMBER;
2636 l_project_count NUMBER;
2637
2638 --taklam
2639 CURSOR project_cu(l_project_id NUMBER, l_p_ship_from_org_id NUMBER) IS
2640 SELECT COUNT(*) p_count
2641 FROM PJM_PROJECTS_ORG_V
2642 WHERE project_id = l_project_id and inventory_organization_id = l_p_ship_from_org_id;
2643
2644 CURSOR order_line_cu(l_est_detail_id NUMBER) is
2645 SELECT b.order_line_id, a.ship_from_org_id
2646 FROM oe_order_lines_all a, cs_estimate_details b
2647 WHERE a.line_id = b.order_line_id
2648 AND b.estimate_detail_id = l_est_detail_id;
2649
2650 -- swai: bug 6001057
2651 CURSOR sr_account_cu (l_repair_line_id NUMBER) is
2652 SELECT account_id
2653 FROM cs_incidents_all_b cs, csd_repairs csd
2654 WHERE cs.incident_id = csd.incident_id
2655 AND repair_line_id = l_repair_line_id;
2656
2657
2658 BEGIN
2659
2660 x_return_status := Fnd_Api.G_Ret_Sts_SUCCESS;
2661
2662 IF p_prodtxn_db_attr.curr_submit_order_flag <>
2663 p_product_txn_rec.interface_to_om_flag AND
2664 p_product_txn_rec.interface_to_om_flag = 'Y'
2665 THEN
2666
2667 Debug('l_est_detail_id = ' || p_prodtxn_db_attr.est_detail_id,
2668 l_mod_name,
2669 1);
2670
2671 BEGIN
2672 SELECT 'X'
2673 INTO l_dummy
2674 FROM cs_estimate_details
2675 WHERE estimate_detail_id = p_prodtxn_db_attr.est_detail_id
2676 AND order_line_id IS NULL;
2677 EXCEPTION
2678 WHEN NO_DATA_FOUND THEN
2679 FND_MESSAGE.SET_NAME('CSD','CSD_API_INTERFACE_FAILED'); /*Fixed for bug#5147030 message changed*/
2680
2681 /*Fnd_Message.SET_NAME('CSD',
2682 'CSD_API_INV_EST_DETAIL_ID');
2683 Fnd_Message.SET_TOKEN('ESTIMATE_DETAIL_ID',
2684 p_prodtxn_db_attr.est_detail_id);
2685 */
2686 Fnd_Msg_Pub.ADD;
2687 Debug('Sales Order may be interfaced already',
2688 l_mod_name,
2689 1);
2690 RAISE CREATE_ORDER;
2691 WHEN TOO_MANY_ROWS THEN
2692 Debug('Too many from cs_estimate_details',
2693 l_mod_name,
2694 1);
2695 RAISE CREATE_ORDER;
2696 END;
2697
2698 IF p_product_txn_rec.action_type IN
2699 ('SHIP', 'WALK_IN_ISSUE') AND
2700 p_product_txn_rec.action_code = 'CUST_PROD'
2701 THEN
2702
2703 Debug('Call Validate_wip_task', l_mod_name, 1);
2704 Debug('product_transaction_id = ' ||
2705 p_product_txn_rec.product_transaction_id,
2706 l_mod_name,
2707 1);
2708
2709 Csd_Process_Util.Validate_wip_task(p_prod_txn_id => p_product_txn_rec.product_transaction_id,
2710 x_return_status => l_return_status);
2711
2712 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
2713 THEN
2714 Debug('Validate_wip_task failed',
2715 l_mod_name,
2716 1);
2717 RAISE CREATE_ORDER;
2718 END IF;
2719 Debug('Validate wip/tasks are complete ',
2720 l_mod_name,
2721 1);
2722 END IF;
2723
2724 BEGIN
2725 SELECT revision_qty_control_code
2726 INTO l_rev_ctl_code
2727 FROM mtl_system_items
2728 WHERE organization_id =
2729 Cs_Std.get_item_valdn_orgzn_id
2730 AND inventory_item_id =
2731 p_product_txn_rec.inventory_item_id;
2732 EXCEPTION
2733 WHEN NO_DATA_FOUND THEN
2734 Fnd_Message.SET_NAME('CSD',
2735 'CSD_INVALID_INVENTORY_ITEM');
2736 Fnd_Msg_Pub.ADD;
2737 RAISE CREATE_ORDER;
2738 END;
2739
2740 IF l_rev_ctl_code = 2
2741 THEN
2742 BEGIN
2743 SELECT 'x'
2744 INTO l_dummy
2745 FROM mtl_item_revisions
2746 WHERE inventory_item_id =
2747 p_product_txn_rec.inventory_item_id
2748 AND organization_id =
2749 Cs_Std.get_item_valdn_orgzn_id
2750 AND revision = p_product_txn_rec.revision;
2751 EXCEPTION
2752 WHEN NO_DATA_FOUND THEN
2753 Fnd_Message.SET_NAME('CSD',
2754 'CSD_INVALID_REVISION');
2755 Fnd_Msg_Pub.ADD;
2756 RAISE CREATE_ORDER;
2757 END;
2758 END IF;
2759
2760 Debug('Call process_sales_order to create SO',
2761 l_mod_name,
2762 1);
2763
2764 --taklam
2765 if (p_product_txn_rec.unit_number) is not null then
2766 FND_PROFILE.PUT('CSD_UNIT_NUMBER', p_product_txn_rec.unit_number);
2767 end if;
2768
2769 Csd_Process_Pvt.PROCESS_SALES_ORDER(p_api_version => 1.0,
2770 p_commit => Fnd_Api.g_false,
2771 p_init_msg_list => Fnd_Api.g_true,
2772 p_validation_level => Fnd_Api.g_valid_level_full,
2773 p_action => 'CREATE',
2774 p_order_rec => px_order_rec,
2775 x_return_status => l_return_status,
2776 x_msg_count => l_msg_count,
2777 x_msg_data => l_msg_data);
2778
2779 --taklam
2780 if (p_product_txn_rec.unit_number) is not null then
2781 FND_PROFILE.PUT('CSD_UNIT_NUMBER',null);
2782 end if;
2783
2784 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
2785 THEN
2786 Debug('process_sales_order failed', l_mod_name, 1);
2787 RAISE CREATE_ORDER;
2788 END IF;
2789
2790 Debug('Created Sales order for Prod Txn Id =' ||
2791 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
2792 l_mod_name,
2793 1);
2794
2795 UPDATE CSD_PRODUCT_TRANSACTIONS
2796 SET prod_txn_status = 'SUBMITTED',
2797 interface_to_om_flag = 'Y'
2798 WHERE product_transaction_id =
2799 p_product_txn_rec.PRODUCT_TRANSACTION_ID;
2800 IF SQL%NOTFOUND
2801 THEN
2802 Fnd_Message.SET_NAME('CSD',
2803 'CSD_ERR_PRD_TXN_UPDATE');
2804 Fnd_Message.SET_TOKEN('PRODUCT_TRANSACTION_ID',
2805 p_product_txn_rec.PRODUCT_TRANSACTION_ID);
2806 Fnd_Msg_Pub.ADD;
2807 RAISE CREATE_ORDER;
2808 END IF;
2809
2810 UPDATE CSD_REPAIRS
2811 SET ro_txn_status = 'OM_SUBMITTED'
2812 WHERE repair_line_id =
2813 p_product_txn_rec.REPAIR_LINE_ID;
2814 IF SQL%NOTFOUND
2815 THEN
2816 Fnd_Message.SET_NAME('CSD',
2817 'CSD_ERR_REPAIRS_UPDATE');
2818 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
2819 p_product_txn_rec.repair_line_id);
2820 Fnd_Msg_Pub.ADD;
2821 RAISE CREATE_ORDER;
2822 END IF;
2823
2824 -- swai: bug 6001057
2825 -- rearranged code so that call to OM API can be used to update
2826 -- project, unit number, or 3rd party end_customer
2827 if (((p_product_txn_rec.project_id is not null)
2828 OR (p_product_txn_rec.unit_number is not null)
2829 OR (p_product_txn_rec.sub_inventory is not null) ----bug#9955988
2830 OR (p_product_txn_rec.action_type in ('RMA_THIRD_PTY', 'SHIP_THIRD_PTY')))
2831 and ((NVL(p_product_txn_rec.project_id,-1)) <> FND_API.G_MISS_NUM)) then --bug#6075825
2832
2833 OPEN order_line_cu(p_prodtxn_db_attr.est_detail_id);
2834 FETCH order_line_cu into l_order_line_id, l_p_ship_from_org_id;
2835 CLOSE order_line_cu;
2836
2837 if (l_order_line_id) is not null then
2838 l_Line_Tbl_Type(1) := OE_Order_PUB.G_MISS_LINE_REC;
2839 l_Line_Tbl_Type(1).line_id := l_order_line_id;
2840 l_Line_Tbl_Type(1).operation := OE_GLOBALS.G_OPR_UPDATE;
2841
2842 -- taklam: update project and unit number fields
2843 if ((p_product_txn_rec.project_id is not null) or (p_product_txn_rec.unit_number is not null)) then
2844
2845 l_Line_Tbl_Type(1).end_item_unit_number := p_product_txn_rec.unit_number;
2846
2847 if (p_product_txn_rec.project_id is not null) then
2848 OPEN project_cu(p_product_txn_rec.project_id,l_p_ship_from_org_id);
2849 FETCH project_cu into l_project_count;
2850 CLOSE project_cu;
2851
2852 if (l_project_count >= 1) then
2853 l_Line_Tbl_Type(1).project_id := p_product_txn_rec.project_id;
2854 l_Line_Tbl_Type(1).task_id := p_product_txn_rec.task_id;
2855 else
2856 FND_MESSAGE.SET_NAME('CSD','CSD_ERR_PROJECT_UPDATE');
2857 FND_MESSAGE.SET_TOKEN('project_id',p_product_txn_rec.project_id);
2858 FND_MESSAGE.SET_TOKEN('ship_from_org_id',l_p_ship_from_org_id);
2859 FND_MSG_PUB.ADD;
2860 RAISE CREATE_ORDER;
2861 end if;
2862 end if;
2863 end if; -- end update project and unit number fields
2864
2865 --bug#9955988
2866 if (p_product_txn_rec.sub_inventory is not null) then
2867 l_Line_Tbl_Type(1).subinventory := p_product_txn_rec.sub_inventory ;
2868 end if;
2869 --bug#9955988
2870
2871 -- swai: update 3rd party fields.
2872 -- IB Owner must be set to END_CUSTOMER and end_custoemr_id mustbe
2873 -- set to the SR customer account id in order for 3rd party lines to
2874 -- avoid changing IB ownership during material transactions.
2875 if (p_product_txn_rec.action_type in ('RMA_THIRD_PTY', 'SHIP_THIRD_PTY')) then
2876 -- get SR customer account
2877 OPEN sr_account_cu (p_product_txn_rec.repair_line_id);
2878 FETCH sr_account_cu into l_sr_account_id;
2879 CLOSE sr_account_cu;
2880 if (l_sr_account_id) is not null then
2881 l_Line_Tbl_Type(1).ib_owner := 'END_CUSTOMER';
2882 l_Line_Tbl_Type(1).end_customer_id := l_sr_account_id;
2883 end if;
2884 end if; -- end update 3rd party fields
2885
2886 OE_ORDER_PUB.Process_Line(
2887 p_line_tbl => l_Line_Tbl_Type,
2888 x_line_out_tbl => x_Line_Tbl_Type,
2889 x_return_status => x_return_status,
2890 x_msg_count => l_msg_count,
2891 x_msg_data => l_msg_data
2892 );
2893
2894 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2895 FND_MESSAGE.SET_NAME('CSD','CSD_ERR_OM_PROCESS_LINE');
2896 FND_MSG_PUB.ADD;
2897 RAISE CREATE_ORDER;
2898 END IF;
2899
2900 end if; -- order line is not null
2901 end if;
2902 -- end swai: bug 6001057
2903
2904
2905 END IF;
2906
2907 EXCEPTION
2908 WHEN CREATE_ORDER THEN
2909 Debug('In Create_order exception while submitting the charge line =' ||
2910 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
2911 l_mod_name,
2912 1);
2913 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
2914 WHEN OTHERS THEN
2915 Debug('In OTHERS exception while submitting the charge line =' ||
2916 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
2917 l_mod_name,
2918 1);
2919 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
2920
2921 END interface_prodtxn;
2922
2923
2924
2925 /*------------------------------------------------------------------------*/
2926 /* procedure name: book_prodtxn */
2927 /* description : */
2928 /* Books the prod txn record in Depot schema */
2929 /* Parameters Required: */
2930 /* p_product_txn_rec IN product transaction record */
2931 /* x_return_status OUT return status */
2932 /*------------------------------------------------------------------------*/
2933 PROCEDURE book_prodtxn
2934 (
2935 x_return_status OUT NOCOPY VARCHAR2,
2936 p_product_txn_rec IN Csd_Process_Pvt.PRODUCT_TXN_REC,
2937 p_prodtxn_db_attr IN Csd_Logistics_Util.PRODTXN_DB_ATTR_REC,
2938 px_order_rec IN OUT NOCOPY Csd_Process_Pvt.om_interface_rec
2939
2940 ) IS
2941
2942 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.book_prodtxn';
2943 l_return_status VARCHAR2(1);
2944 l_order_line_id NUMBER;
2945 l_booked_flag VARCHAR2(1);
2946 l_ship_from_org_id NUMBER;
2947 l_unit_selling_price oe_order_lines_all.unit_selling_price%TYPE;
2948
2949 l_msg_count NUMBER;
2950 l_msg_data VARCHAR2(2000);
2951 book_order EXCEPTION;
2952
2953 l_sr_account_id NUMBER; -- swai: bug 6001057
2954
2955 --taklam
2956 l_Line_Tbl_Type OE_ORDER_PUB.Line_Tbl_Type;
2957 x_Line_Tbl_Type OE_ORDER_PUB.Line_Tbl_Type;
2958 l_p_ship_from_org_id NUMBER;
2959 l_project_count NUMBER;
2960
2961 --taklam
2962 CURSOR project_cu(l_project_id NUMBER, l_p_ship_from_org_id NUMBER) IS
2963 SELECT COUNT(*) p_count
2964 FROM PJM_PROJECTS_ORG_V
2965 WHERE project_id = l_project_id and inventory_organization_id = l_p_ship_from_org_id;
2966
2967 -- swai: bug 6001057
2968 CURSOR sr_account_cu (l_repair_line_id NUMBER) is
2969 SELECT account_id
2970 FROM cs_incidents_all_b cs, csd_repairs csd
2971 WHERE cs.incident_id = csd.incident_id
2972 AND repair_line_id = l_repair_line_id;
2973
2974 BEGIN
2975
2976
2977 Debug('l_est_detail_id = ' || p_product_txn_rec.estimate_detail_id,
2978 l_mod_name,
2979 1);
2980
2981 x_return_status := Fnd_Api.G_Ret_Sts_SUCCESS;
2982 BEGIN
2983 SELECT b.order_header_id,
2984 b.order_line_id,
2985 a.booked_flag
2986 INTO px_order_rec.order_header_id,
2987 l_order_line_id,
2988 l_booked_flag
2989 FROM oe_order_lines_all a, cs_estimate_details b
2990 WHERE a.line_id = b.order_line_id
2991 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;
2992 EXCEPTION
2993 WHEN NO_DATA_FOUND THEN
2994 FND_MESSAGE.SET_NAME('CSD','CSD_API_BOOKING_FAILED'); /*Fixed for bug#5147030 message changed*/
2995 /*
2996 Fnd_Message.SET_NAME('CSD',
2997 'CSD_INV_EST_DETAIL_ID');
2998 Fnd_Message.SET_TOKEN('ESTIMATE_DETAIL_ID',
2999 p_product_txn_rec.estimate_detail_id); */
3000 Fnd_Msg_Pub.ADD;
3001 Debug('Invalid estimate detail id = ' ||
3002 p_product_txn_rec.estimate_detail_id,
3003 l_mod_name,
3004 1);
3005 RAISE BOOK_ORDER;
3006 WHEN TOO_MANY_ROWS THEN
3007 Debug('Too many from book_sales_order1',
3008 l_mod_name,
3009 1);
3010 RAISE BOOK_ORDER;
3011 END;
3012
3013
3014 --bug#6071005
3015 px_order_rec.order_line_id := l_order_line_id;
3016
3017 Debug('order_header_id = ' ||
3018 px_order_rec.order_header_id,
3019 l_mod_name,
3020 1);
3021 Debug('l_booked_flag = ' || l_booked_flag,
3022 l_mod_name,
3023 1);
3024
3025 BEGIN
3026 -- To Book an Order Sales Rep and ship_from_org_id is reqd
3027 -- so check if the Order header has it
3028 SELECT ship_from_org_id, unit_selling_price, org_id
3029 INTO l_ship_from_org_id,
3030 l_unit_selling_price,
3031 px_order_rec.org_id
3032 FROM oe_order_lines_all
3033 WHERE line_id = l_order_line_id;
3034 EXCEPTION
3035 WHEN NO_DATA_FOUND THEN
3036 Fnd_Message.SET_NAME('CSD',
3037 'CSD_API_SALES_REP_MISSING');
3038 Fnd_Message.SET_TOKEN('ORDER_LINE_ID',
3039 l_order_line_id);
3040 Fnd_Msg_Pub.ADD;
3041 Debug('Sales rep missing for Line Id=' ||
3042 l_order_line_id,
3043 l_mod_name,
3044 1);
3045 RAISE BOOK_ORDER;
3046 WHEN TOO_MANY_ROWS THEN
3047 Debug('Too many from book_sales_order2',
3048 l_mod_name,
3049 1);
3050 END;
3051
3052 IF l_ship_from_org_id IS NULL
3053 THEN
3054 Fnd_Message.SET_NAME('CSD',
3055 'CSD_API_SHIP_FROM_ORG_MISSING');
3056 Fnd_Message.SET_TOKEN('ORDER_LINE_ID',
3057 l_order_line_id);
3058 Fnd_Msg_Pub.ADD;
3059 Debug('Ship from Org Id missing for Line id=' ||
3060 l_order_line_id,
3061 l_mod_name,
3062 1);
3063 RAISE BOOK_ORDER;
3064 END IF;
3065
3066 IF l_unit_selling_price IS NULL
3067 THEN
3068 Fnd_Message.SET_NAME('CSD',
3069 'CSD_API_PRICE_MISSING');
3070 Fnd_Message.SET_TOKEN('ORDER_LINE_ID',
3071 l_order_line_id);
3072 Fnd_Msg_Pub.ADD;
3073 Debug('Unit selling Price missing for Line id=' ||
3074 l_order_line_id,
3075 l_mod_name,
3076 1);
3077 RAISE BOOK_ORDER;
3078 END IF;
3079
3080 IF l_booked_flag = 'N'
3081 THEN
3082 -- swai: bug 6001057
3083 -- rearranged code so that call to OM API can be used to update
3084 -- project, unit number, or 3rd party end_customer
3085 if (((p_product_txn_rec.project_id is not null)
3086 OR (p_product_txn_rec.unit_number is not null)
3087 OR (p_product_txn_rec.sub_inventory is not null) ----bug#9955988
3088 OR (p_product_txn_rec.action_type in ('RMA_THIRD_PTY', 'SHIP_THIRD_PTY')))
3089 and ((NVL(p_product_txn_rec.project_id,-1)) <> FND_API.G_MISS_NUM)) then --bug#6075825
3090
3091 if (l_order_line_id) is not null then
3092 l_Line_Tbl_Type(1) := OE_Order_PUB.G_MISS_LINE_REC;
3093 l_Line_Tbl_Type(1).line_id := l_order_line_id;
3094 l_Line_Tbl_Type(1).operation := OE_GLOBALS.G_OPR_UPDATE;
3095
3096 -- taklam: update projects fields
3097 if ((p_product_txn_rec.project_id is not null) or (p_product_txn_rec.unit_number is not null)) then
3098
3099 l_Line_Tbl_Type(1).end_item_unit_number := p_product_txn_rec.unit_number;
3100
3101 if (p_product_txn_rec.project_id is not null) then
3102 OPEN project_cu(p_product_txn_rec.project_id,l_ship_from_org_id);
3103 FETCH project_cu into l_project_count;
3104 CLOSE project_cu;
3105
3106 if (l_project_count >= 1) then
3107 l_Line_Tbl_Type(1).project_id := p_product_txn_rec.project_id;
3108 l_Line_Tbl_Type(1).task_id := p_product_txn_rec.task_id;
3109 else
3110 FND_MESSAGE.SET_NAME('CSD','CSD_ERR_PROJECT_UPDATE');
3111 FND_MESSAGE.SET_TOKEN('project_id',p_product_txn_rec.project_id);
3112 FND_MESSAGE.SET_TOKEN('ship_from_org_id',l_ship_from_org_id);
3113 FND_MSG_PUB.ADD;
3114 RAISE BOOK_ORDER;
3115 end if;
3116 end if;
3117 end if; -- end update projects fields
3118
3119 --bug#9955988
3120 if (p_product_txn_rec.sub_inventory is not null) then
3121 l_Line_Tbl_Type(1).subinventory := p_product_txn_rec.sub_inventory ;
3122 end if;
3123 --bug#9955988
3124
3125 -- swai: update 3rd party fields.
3126 -- IB Owner must be set to END_CUSTOMER and end_custoemr_id mustbe
3127 -- set to the SR customer account id in order for 3rd party lines to
3128 -- avoid changing IB ownership during material transactions.
3129 if (p_product_txn_rec.action_type in ('RMA_THIRD_PTY', 'SHIP_THIRD_PTY')) then
3130 -- get SR customer account
3131 OPEN sr_account_cu (p_product_txn_rec.repair_line_id);
3132 FETCH sr_account_cu into l_sr_account_id;
3133 CLOSE sr_account_cu;
3134 if (l_sr_account_id) is not null then
3135 l_Line_Tbl_Type(1).ib_owner := 'END_CUSTOMER';
3136 l_Line_Tbl_Type(1).end_customer_id := l_sr_account_id;
3137 end if;
3138 end if; -- end update 3rd party fields
3139
3140 OE_ORDER_PUB.Process_Line(
3141 p_line_tbl => l_Line_Tbl_Type,
3142 x_line_out_tbl => x_Line_Tbl_Type,
3143 x_return_status => x_return_status,
3144 x_msg_count => l_msg_count,
3145 x_msg_data => l_msg_data
3146 );
3147
3148 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3149 FND_MESSAGE.SET_NAME('CSD','CSD_ERR_OM_PROCESS_LINE');
3150 FND_MSG_PUB.ADD;
3151 RAISE BOOK_ORDER;
3152 END IF;
3153 end if; -- order line is not null
3154 end if; -- update OM line criteria
3155 -- end swai: bug 6001057
3156
3157 Debug('Call process_sales_order to Book SO',
3158 l_mod_name,
3159 1);
3160 Debug('l_order_rec.org_id' || px_order_rec.org_id,
3161 l_mod_name,
3162 1);
3163
3164 Csd_Process_Pvt.PROCESS_SALES_ORDER(p_api_version => 1.0,
3165 p_commit => Fnd_Api.g_false,
3166 p_init_msg_list => Fnd_Api.g_false,
3167 p_validation_level => Fnd_Api.g_valid_level_full,
3168 p_action => 'BOOK',
3169 p_order_rec => px_order_rec,
3170 x_return_status => l_return_status,
3171 x_msg_count => l_msg_count,
3172 x_msg_data => l_msg_data);
3173
3174 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
3175 THEN
3176 Debug('Process_sales_order failed',
3177 l_mod_name,
3178 1);
3179 RAISE BOOK_ORDER;
3180 END IF;
3181
3182 Debug('Update the prod txn status to BOOKED',
3183 l_mod_name,
3184 1);
3185
3186 -- UPDATE csd_product_transactions
3187 -- SET prod_txn_status = 'BOOKED',
3188 -- book_sales_order_flag = 'Y'
3189 -- WHERE product_transaction_id = x_product_txn_rec.PRODUCT_TRANSACTION_ID;
3190 -- IF SQL%NOTFOUND then
3191 -- FND_MESSAGE.SET_NAME('CSD','CSD_ERR_PRD_TXN_UPDATE');
3192 -- FND_MESSAGE.SET_TOKEN('PRODUCT_TRANSACTION_ID',x_product_txn_rec.PRODUCT_TRANSACTION_ID);
3193 -- FND_MSG_PUB.ADD;
3194 -- RAISE BOOK_ORDER;
3195 -- END IF;
3196
3197 -- Fix for bug#4020651
3198 Csd_Update_Programs_Pvt.prod_txn_status_upd(p_repair_line_id => p_product_txn_rec.repair_line_id,
3199 p_commit => Fnd_Api.g_false);
3200
3201 UPDATE CSD_REPAIRS
3202 SET ro_txn_status = 'OM_BOOKED'
3203 WHERE repair_line_id =
3204 p_product_txn_rec.REPAIR_LINE_ID;
3205 IF SQL%NOTFOUND
3206 THEN
3207 Fnd_Message.SET_NAME('CSD',
3208 'CSD_ERR_REPAIRS_UPDATE');
3209 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3210 p_product_txn_rec.repair_line_id);
3211 Fnd_Msg_Pub.ADD;
3212 RAISE BOOK_ORDER;
3213 END IF;
3214
3215 ELSIF l_booked_flag = 'Y'
3216 THEN
3217
3218 Debug('Update the prod txn status to BOOKED',
3219 l_mod_name,
3220 1);
3221
3222 -- UPDATE csd_product_transactions
3223 -- SET prod_txn_status = 'BOOKED',
3224 -- book_sales_order_flag = 'Y'
3225 -- WHERE product_transaction_id = x_product_txn_rec.PRODUCT_TRANSACTION_ID;
3226 -- IF SQL%NOTFOUND then
3227 -- FND_MESSAGE.SET_NAME('CSD','CSD_ERR_PRD_TXN_UPDATE');
3228 -- FND_MESSAGE.SET_TOKEN('PRODUCT_TRANSACTION_ID',x_product_txn_rec.PRODUCT_TRANSACTION_ID);
3229 -- FND_MSG_PUB.ADD;
3230 -- RAISE BOOK_ORDER;
3231 -- END IF;
3232
3233 -- Fix for bug#4020651
3234 Csd_Update_Programs_Pvt.prod_txn_status_upd(p_repair_line_id => p_product_txn_rec.repair_line_id,
3235 p_commit => Fnd_Api.g_false);
3236
3237 UPDATE CSD_REPAIRS
3238 SET ro_txn_status = 'OM_BOOKED'
3239 WHERE repair_line_id =
3240 p_product_txn_rec.REPAIR_LINE_ID;
3241 IF SQL%NOTFOUND
3242 THEN
3243 Fnd_Message.SET_NAME('CSD',
3244 'CSD_ERR_REPAIRS_UPDATE');
3245 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3246 p_product_txn_rec.repair_line_id);
3247 Fnd_Msg_Pub.ADD;
3248 RAISE BOOK_ORDER;
3249 END IF;
3250
3251 END IF; -- l_booked_flag if condition
3252
3253 EXCEPTION
3254 WHEN BOOK_ORDER THEN
3255 Debug('In Book_order exception while booking the order line =' ||
3256 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3257 l_mod_name,
3258 1);
3259 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3260 WHEN OTHERS THEN
3261 Debug('In OTHERS exception while booking the order line =' ||
3262 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3263 l_mod_name,
3264 1);
3265 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3266
3267 END book_prodtxn;
3268
3269
3270 /*------------------------------------------------------------------------*/
3271 /* procedure name: pickrelease_prodtxn */
3272 /* description : */
3273 /* pick releases the prod txn record in Depot schema */
3274 /* Parameters Required: */
3275 /* p_product_txn_rec IN product transaction record */
3276 /* */
3277 /*------------------------------------------------------------------------*/
3278 PROCEDURE pickrelease_prodtxn
3279 (
3280 x_return_status OUT NOCOPY VARCHAR2,
3281 p_product_txn_rec IN Csd_Process_Pvt.PRODUCT_TXN_REC,
3282 p_prodtxn_db_attr IN Csd_Logistics_Util.PRODTXN_DB_ATTR_REC,
3283 px_order_rec IN OUT NOCOPY Csd_Process_Pvt.om_interface_rec
3284 ) IS
3285 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.pickrelease_prodtxn';
3286 l_ship_from_org_id NUMBER;
3287 l_picking_rule_id NUMBER;
3288 l_released_status wsh_delivery_details.released_status%TYPE;
3289 l_order_header_id NUMBER;
3290 l_return_status VARCHAR2(1);
3291 l_msg_count NUMBER;
3292 l_msg_data VARCHAR2(2000);
3293
3294 release_order EXCEPTION;
3295
3296 l_eligible_lines_pick_release NUMBER; /*Bug#4992402 */
3297
3298 /* R12 SN reservations integration change Begin */
3299 l_ItemAttributes Csd_Logistics_Util.ItemAttributes_Rec_Type;
3300 l_auto_reserve_profile VARCHAR2(10);
3301 l_srl_reservation_id NUMBER;
3302 l_serial_rsv_rec CSD_SERIAL_RESERVE_REC_TYPE ;
3303 l_order_line_id NUMBER;
3304 /* R12 SN reservations integration change End */
3305
3306
3307 BEGIN
3308 x_return_status := Fnd_Api.G_Ret_Sts_SUCCESS;
3309
3310 BEGIN
3311 /* Adding order_header_id and order_line_id in the select list
3312 for serial reservations change for R12, Vijay June 9th 2006 */
3313 SELECT ship_from_org_id, header_id, line_id
3314 INTO l_ship_from_org_id, l_order_header_id, l_order_line_id
3315 FROM oe_order_lines_all oel,
3316 cs_estimate_details ced
3317 WHERE oel.line_id = ced.order_line_id
3318 AND ced.estimate_detail_id =
3319 p_product_txn_rec.estimate_detail_id;
3320 EXCEPTION
3321 WHEN NO_DATA_FOUND THEN
3322 Debug('Order Line not found ', l_mod_name, 1);
3323 RAISE RELEASE_ORDER;
3324 END;
3325
3326 IF NVL(p_product_txn_rec.sub_inventory,
3327 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR
3328 THEN
3329 px_order_rec.PICK_FROM_SUBINVENTORY := p_product_txn_rec.sub_inventory;
3330 END IF;
3331
3332
3333 /* R12 SN reservations change Begin */
3334 -- Get Item attributes in local variable
3335 Get_ItemAttributes(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
3336 p_inv_org_id => p_Product_Txn_Rec.inventory_org_id,
3337 x_ItemAttributes => l_ItemAttributes);
3338 -- Get the default pick rule id
3339 Fnd_Profile.Get('CSD_AUTO_SRL_RESERVE',
3340 l_auto_reserve_profile);
3341 if(l_auto_reserve_profile is null) then
3342 l_auto_reserve_profile := 'N';
3343 end if;
3344
3345 Debug('Going to process reservation..', l_mod_name, 1);
3346 Debug(l_auto_reserve_profile, l_mod_name,1);
3347 Debug(p_Product_Txn_Rec.source_Serial_number, l_mod_name,1);
3348 Debug(p_Product_Txn_Rec.sub_inventory, l_mod_name,1);
3349 Debug(p_Product_Txn_Rec.action_type, l_mod_name,1);
3350 Debug(to_char(l_itemAttributes.reservable_type), l_mod_name,1);
3351 Debug(to_char(l_itemAttributes.serial_Code), l_mod_name,1);
3352
3353
3354 IF( l_auto_reserve_profile = 'Y'
3355 AND p_Product_Txn_Rec.source_Serial_number is not null
3356 AND p_Product_Txn_Rec.sub_inventory is not null
3357 AND p_product_txn_rec.action_type IN ('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY') -- swai: 5931926 12.0.2
3358 AND l_ItemAttributes.reservable_type = C_RESERVABLE
3359 AND (l_ItemAttributes.serial_code = C_SERIAL_CONTROL_AT_RECEIPT
3360 OR
3361 l_ItemAttributes.serial_code = C_SERIAL_CONTROL_PREDEFINED) ) THEN
3362
3363 Debug('Checking reservation id for serial number..['
3364 ||p_Product_Txn_Rec.source_Serial_number||']', l_mod_name, 1);
3365
3366 l_serial_rsv_rec.inventory_item_id := p_Product_Txn_Rec.inventory_item_id;
3367 l_serial_rsv_rec.inv_organization_id := p_Product_Txn_Rec.inventory_org_id;
3368 l_serial_rsv_rec.order_header_id := l_order_header_id;
3369 l_serial_rsv_rec.order_line_id := l_order_line_Id;
3370 l_serial_rsv_rec.order_schedule_date := sysdate;
3371 l_serial_rsv_rec.serial_number := p_Product_Txn_Rec.source_serial_number;
3372 l_serial_rsv_rec.locator_id := p_Product_Txn_Rec.locator_id;
3373 l_serial_rsv_rec.revision := p_Product_Txn_Rec.revision;
3374 l_serial_rsv_rec.lot_number := p_Product_Txn_Rec.lot_number;
3375 l_serial_rsv_rec.subinventory_code := p_Product_Txn_Rec.sub_inventory;
3376 l_serial_rsv_rec.reservation_uom_code := p_Product_Txn_Rec.Unit_Of_Measure_Code;
3377
3378 Debug('Calling reserve serial..', l_mod_name, 1);
3379 Reserve_serial_number(l_serial_rsv_rec, l_return_status);
3380
3381 if(l_return_status = FND_API.G_RET_STS_ERROR) THEN
3382 Fnd_Message.SET_NAME('CSD',
3383 'CSD_SRL_RESERVE_FAILED');
3384 Fnd_Msg_Pub.ADD;
3385 RAISE RELEASE_ORDER;
3386 END IF;
3387
3388 END IF;
3389
3390 /* R12 SN reservations change End */
3391
3392 -- R12 development changes
3393 -- Added the code to get the picking rule from profile only if the product_txn_rec does
3394 -- not have it.
3395 IF (p_product_txn_rec.picking_rule_id IS NULL)
3396 THEN
3397 -- Get the default pick rule id
3398 Fnd_Profile.Get('CSD_DEF_PICK_RELEASE_RULE',
3399 l_picking_rule_id);
3400
3401 Debug('l_picking_rule_id =' || l_picking_rule_id,
3402 l_mod_name,
3403 1);
3404 ELSE
3405
3406 l_picking_rule_id := p_product_txn_rec.picking_rule_id;
3407
3408 END IF; -- End of if for input pick_rule_id check.
3409
3410 BEGIN
3411 SELECT PICKING_RULE_ID
3412 INTO l_picking_rule_id
3413 FROM WSH_PICKING_RULES
3414 WHERE picking_rule_id = l_picking_rule_id
3415 AND SYSDATE BETWEEN
3416 NVL(START_DATE_ACTIVE, SYSDATE) AND
3417 NVL(END_DATE_ACTIVE, SYSDATE + 1);
3418 px_order_rec.picking_rule_id := l_picking_rule_id;
3419 EXCEPTION
3420 WHEN NO_DATA_FOUND THEN
3421 Fnd_Message.SET_NAME('CSD',
3422 'CSD_API_INV_PICKING_RULE_ID');
3423 Fnd_Message.SET_TOKEN('PICKING_RULE_ID',
3424 px_order_rec.picking_rule_id);
3425 Fnd_Msg_Pub.ADD;
3426 RAISE RELEASE_ORDER;
3427 WHEN TOO_MANY_ROWS THEN
3428 Debug('Too many from release_sales_order1',
3429 l_mod_name,
3430 1);
3431 RAISE RELEASE_ORDER;
3432 END;
3433
3434 Debug('l_order_rec.pick_from_subinventory =' ||
3435 px_order_rec.PICK_FROM_SUBINVENTORY,
3436 l_mod_name,
3437 1);
3438 Debug('l_order_rec.picking_rule_id =' ||
3439 px_order_rec.picking_rule_id,
3440 l_mod_name,
3441 1);
3442
3443 BEGIN
3444 Debug('l_est_detail_id = ' || p_product_txn_rec.estimate_detail_id,
3445 l_mod_name,
3446 1);
3447 /*Bug#5049102
3448 Query given below is commented because this will return more than one row
3449 if more than one delivery exist for a given line id.
3450 This can happen in following cases:
3451 1) When ship line is created for more than 1 qty and user manually split the line
3452 in OM. After doing this if user tries to do the pick release from Depot it
3453 fails with error ORA-01422: exact fetch returns more than requested number of rows
3454 2) When ship line is created for more than 1 qty and user do the pick release for
3455 some qty and rest of qty is backordered.
3456 */
3457 /*
3458 SELECT a.released_status,
3459 b.order_header_id
3460 INTO l_released_status,
3461 l_order_header_id
3462 FROM wsh_delivery_details a,
3463 cs_estimate_details b
3464 WHERE a.source_line_id = b.order_line_id
3465 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id; */
3466
3467 /*Bug#5049102
3468 Select order header id from estimate table directly
3469 */
3470 SELECT b.order_header_id
3471 INTO l_order_header_id
3472 FROM cs_estimate_details b
3473 WHERE b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;
3474
3475 /*Bug#5049102
3476 The query given below will find if there is any
3477 delivery available for pick release or not.
3478 If there is no delivery eligible for pick-release
3479 then it does not call API for pick-release
3480 */
3481 l_eligible_lines_pick_release:=0;
3482
3483 SELECT count(*)
3484 INTO l_eligible_lines_pick_release
3485 FROM wsh_delivery_details a,
3486 cs_estimate_details b
3487 WHERE a.source_line_id = b.order_line_id
3488 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id
3489 /*Fixed for bug#5846054
3490 Added condition SOURCE_CODE = 'OE' while selecting
3491 delivery details from wsh_delivery_details. As per
3492 shipping team there can be multiple delivery lines
3493 with different source code can be created from
3494 inbound deliveries (WSH) and other is from order
3495 management (OE). While doing the pick release Depot
3496 should consider the source code as well.
3497 */
3498 AND a.SOURCE_CODE = 'OE'
3499 AND a.released_status in ('R','B');
3500
3501 EXCEPTION
3502 WHEN NO_DATA_FOUND THEN
3503 Fnd_Message.SET_NAME('CSD',
3504 'CSD_INV_EST_DETAIL_ID');
3505 Fnd_Message.SET_TOKEN('ESTIMATE_DETAIL_ID',
3506 p_product_txn_rec.estimate_detail_id);
3507 Fnd_Msg_Pub.ADD;
3508 Debug('Invalid estimate detail ID = ' ||
3509 p_product_txn_rec.estimate_detail_id,
3510 l_mod_name,
3511 1);
3512 RAISE RELEASE_ORDER;
3513 WHEN TOO_MANY_ROWS THEN
3514 Debug('Too many from release_sales_order2',
3515 l_mod_name,
3516 1);
3517 RAISE RELEASE_ORDER;
3518 END;
3519
3520 Debug('l_released_status =' || l_released_status,
3521 l_mod_name,
3522 1);
3523 Debug('l_order_header_id =' || l_order_header_id,
3524 l_mod_name,
3525 1);
3526
3527 px_order_rec.order_header_id := l_order_header_id;
3528 px_order_rec.org_id := l_ship_from_org_id;
3529
3530 -- Fix for Enh Req#3948563
3531 px_order_rec.locator_id := p_product_txn_rec.locator_id;
3532
3533
3534 /* IF (l_released_status = 'R') THEN */
3535 IF (l_eligible_lines_pick_release > 0 ) then /*bug#5049102 call API to pick release only if there are some eligible delivery */
3536 Debug('Call process_sales_order to Release SO',
3537 l_mod_name,
3538 1);
3539 Csd_Process_Pvt.PROCESS_SALES_ORDER(p_api_version => 1.0,
3540 p_commit => Fnd_Api.g_false,
3541 p_init_msg_list => Fnd_Api.g_false,
3542 p_validation_level => Fnd_Api.g_valid_level_full,
3543 p_action => 'PICK-RELEASE',
3544 p_order_rec => px_order_rec,
3545 p_product_txn_rec => p_product_txn_rec,
3546 x_return_status => l_return_status,
3547 x_msg_count => l_msg_count,
3548 x_msg_data => l_msg_data);
3549
3550 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
3551 THEN
3552 Debug('process_sales_order failed, x_msg_data['||l_msg_data||']',
3553 l_mod_name,
3554 1);
3555 RAISE RELEASE_ORDER;
3556 END IF;
3557 Debug('Released the SO for Prod Txn Id =' ||
3558 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3559 l_mod_name,
3560 1);
3561 END IF;
3562
3563 BEGIN
3564 /*Bug#5049102
3565 Query given below is commented because this will return more than one row
3566 if more than one delivery exist for a given line id.
3567 */
3568 /* SELECT a.released_status
3569 INTO l_released_status
3570 FROM wsh_delivery_details a,
3571 cs_estimate_details b
3572 WHERE a.source_line_id = b.order_line_id
3573 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;*/
3574
3575 /*Bug#5049102
3576 The query given below will find if there is any
3577 delivery available for pick release or not.
3578 If there is no delivery eligible for pick-release
3579 then it updates the ship line status
3580 */
3581
3582 l_eligible_lines_pick_release:=0;
3583
3584 SELECT count(*)
3585 INTO l_eligible_lines_pick_release
3586 FROM wsh_delivery_details a,
3587 cs_estimate_details b
3588 WHERE a.source_line_id = b.order_line_id
3589 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id
3590 AND a.released_status in ('R','B','S');
3591
3592 EXCEPTION
3593 WHEN NO_DATA_FOUND THEN
3594 Fnd_Message.SET_NAME('CSD',
3595 'CSD_INV_EST_DETAIL_ID');
3596 Fnd_Message.SET_TOKEN('ESTIMATE_DETAIL_ID',
3597 p_product_txn_rec.estimate_detail_id);
3598 Fnd_Msg_Pub.ADD;
3599 Debug('Invalid estimate detail ID = ' ||
3600 p_product_txn_rec.estimate_detail_id,
3601 l_mod_name,
3602 1);
3603 RAISE RELEASE_ORDER;
3604 WHEN TOO_MANY_ROWS THEN
3605 Debug('Too many from release_sales_order2',
3606 l_mod_name,
3607 1);
3608 RAISE RELEASE_ORDER;
3609 END;
3610
3611 /* IF (l_released_status = 'Y') THEN */
3612 IF (l_eligible_lines_pick_release = 0) THEN /*Bug#5049102 if all delivery are pick released then only update status */
3613
3614 IF (p_product_txn_rec.ACTION_TYPE IN
3615 ('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY')) -- swai: 5931926 12.0.2
3616 THEN
3617 UPDATE CSD_PRODUCT_TRANSACTIONS
3618 SET prod_txn_status = 'RELEASED',
3619 release_sales_order_flag = 'Y'
3620 WHERE product_transaction_id =
3621 p_product_txn_rec.PRODUCT_TRANSACTION_ID;
3622 IF SQL%NOTFOUND
3623 THEN
3624 Fnd_Message.SET_NAME('CSD',
3625 'CSD_ERR_PRD_TXN_UPDATE');
3626 Fnd_Message.SET_TOKEN('PRODUCT_TRANSACTION_ID',
3627 p_product_txn_rec.PRODUCT_TRANSACTION_ID);
3628 Fnd_Msg_Pub.ADD;
3629 RAISE RELEASE_ORDER;
3630 END IF;
3631 END IF;
3632
3633 UPDATE CSD_REPAIRS
3634 SET ro_txn_status = 'OM_RELEASED'
3635 WHERE repair_line_id =
3636 p_product_txn_rec.REPAIR_LINE_ID;
3637 IF SQL%NOTFOUND
3638 THEN
3639 Fnd_Message.SET_NAME('CSD',
3640 'CSD_ERR_REPAIRS_UPDATE');
3641 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3642 p_product_txn_rec.repair_line_id);
3643 Fnd_Msg_Pub.ADD;
3644 RAISE RELEASE_ORDER;
3645 END IF;
3646
3647 END IF;
3648
3649 EXCEPTION
3650 WHEN RELEASE_ORDER THEN
3651 Debug('In Release_order exception while releasing SO =' ||
3652 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3653 l_mod_name,
3654 1);
3655 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3656 WHEN OTHERS THEN
3657 Debug('In OTHERS exception while releasing SO =' ||
3658 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3659 l_mod_name,
3660 1);
3661 Debug('In OTHERS exception while releasing SO sqlerr=' ||
3662 sqlerrm,
3663 l_mod_name,
3664 1);
3665 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3666
3667
3668 END pickrelease_prodtxn;
3669
3670 /*------------------------------------------------------------------------*/
3671 /* procedure name: ship_prodtxn */
3672 /* description : */
3673 /* ships the prod txn record */
3674 /* Parameters Required: */
3675 /* p_product_txn_rec IN product transaction record */
3676 /* x_return_status OUT return status */
3677 /*------------------------------------------------------------------------*/
3678 PROCEDURE ship_prodtxn
3679 (
3680 x_return_status OUT NOCOPY VARCHAR2,
3681 p_product_txn_rec IN Csd_Process_Pvt.PRODUCT_TXN_REC,
3682 p_prodtxn_db_attr IN Csd_Logistics_Util.PRODTXN_DB_ATTR_REC,
3683 px_order_rec IN OUT NOCOPY Csd_Process_Pvt.om_interface_rec
3684 ) IS
3685 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.ship_prodtxn';
3686 l_ship_from_org_id NUMBER;
3687 l_picking_rule_id NUMBER;
3688 l_released_status wsh_delivery_details.released_status%TYPE;
3689 l_return_status VARCHAR2(1);
3690 l_msg_count NUMBER;
3691 l_msg_data VARCHAR2(2000);
3692 ship_order EXCEPTION;
3693
3694 BEGIN
3695 Debug('l_est_detail_id = ' || p_product_txn_rec.estimate_detail_id,
3696 l_mod_name,
3697 1);
3698 x_return_status := Fnd_Api.G_Ret_Sts_SUCCESS;
3699 BEGIN
3700 SELECT b.order_header_id,
3701 b.order_line_id,
3702 c.source_serial_number,
3703 a.ordered_quantity
3704 INTO px_order_rec.order_header_id,
3705 px_order_rec.order_line_id,
3706 px_order_rec.serial_number,
3707 px_order_rec.shipped_quantity
3708 FROM oe_order_lines_all a,
3709 cs_estimate_details b,
3710 CSD_PRODUCT_TRANSACTIONS c
3711 WHERE a.line_id = b.order_line_id
3712 AND b.estimate_detail_id = c.estimate_detail_id
3713 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;
3714 EXCEPTION
3715 WHEN NO_DATA_FOUND THEN
3716 FND_MESSAGE.SET_NAME('CSD','CSD_API_SHIPPING_FAILD'); /*Fixed for bug#5147030 message changed*/
3717 /*
3718 Fnd_Message.SET_NAME('CSD',
3719 'CSD_API_INV_EST_DETAIL_ID');
3720 Fnd_Message.SET_TOKEN('ESTIMATE_DETAIL_ID',
3721 p_product_txn_rec.estimate_detail_id);
3722 */
3723 Fnd_Msg_Pub.ADD;
3724 Debug('Invalid Estimate Detail Id = ' ||
3725 p_product_txn_rec.estimate_detail_id,
3726 l_mod_name,
3727 1);
3728 RAISE SHIP_ORDER;
3729 WHEN TOO_MANY_ROWS THEN
3730 Debug('Too many found for the estimate detail id',
3731 l_mod_name,
3732 1);
3733 END;
3734
3735 Debug('order_header_id = ' ||
3736 px_order_rec.order_header_id,
3737 l_mod_name,
3738 1);
3739 Debug('serial_number = ' ||
3740 px_order_rec.serial_number,
3741 l_mod_name,
3742 1);
3743 Debug('shipped_quantity= ' ||
3744 px_order_rec.shipped_quantity,
3745 l_mod_name,
3746 1);
3747
3748 BEGIN
3749 SELECT released_status
3750 INTO l_released_status
3751 FROM wsh_delivery_details
3752 WHERE source_header_id =
3753 px_order_rec.order_header_id
3754 AND source_line_id = px_order_rec.order_line_id;
3755 EXCEPTION
3756 WHEN NO_DATA_FOUND THEN
3757 Fnd_Message.SET_NAME('CSD',
3758 'CSD_RELEASE_FAILED');
3759 Fnd_Message.SET_TOKEN('ORDER_LINE_ID',
3760 px_order_rec.order_line_id);
3761 Fnd_Msg_Pub.ADD;
3762 RAISE SHIP_ORDER;
3763 WHEN TOO_MANY_ROWS THEN
3764 Debug('Too many from ship_sales_order',
3765 l_mod_name,
3766 1);
3767 END;
3768
3769 Debug('l_released_status =' || l_released_status,
3770 l_mod_name,
3771 1);
3772
3773 IF l_released_status = 'Y'
3774 THEN
3775
3776 Debug('Call Process_sales_order to ship SO',
3777 l_mod_name,
3778 1);
3779 Csd_Process_Pvt.PROCESS_SALES_ORDER(p_api_version => 1.0,
3780 p_commit => Fnd_Api.g_false,
3781 p_init_msg_list => Fnd_Api.g_false,
3782 p_validation_level => Fnd_Api.g_valid_level_full,
3783 p_action => 'SHIP',
3784 /*Fixed for bug#4433942 passing product
3785 txn record as in parameter*/
3786 p_product_txn_rec => p_product_txn_rec,
3787 p_order_rec => px_order_rec,
3788 x_return_status => l_return_status,
3789 x_msg_count => l_msg_count,
3790 x_msg_data => l_msg_data);
3791
3792 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
3793 THEN
3794 Debug('Process_sales_order failed',
3795 l_mod_name,
3796 1);
3797 RAISE SHIP_ORDER;
3798 END IF;
3799
3800 IF (p_product_txn_rec.ACTION_TYPE IN
3801 ('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY')) -- swai: 5931926 12.0.2
3802 THEN
3803 UPDATE CSD_PRODUCT_TRANSACTIONS
3804 SET prod_txn_status = 'SHIPPED',
3805 ship_sales_order_flag = 'Y'
3806 WHERE product_transaction_id =
3807 p_product_txn_rec.PRODUCT_TRANSACTION_ID;
3808 IF SQL%NOTFOUND
3809 THEN
3810 Fnd_Message.SET_NAME('CSD',
3811 'CSD_ERR_PRD_TXN_UPDATE');
3812 Fnd_Message.SET_TOKEN('PRODUCT_TRANSACTION_ID',
3813 p_product_txn_rec.PRODUCT_TRANSACTION_ID);
3814 Fnd_Msg_Pub.ADD;
3815 RAISE SHIP_ORDER;
3816 END IF;
3817 END IF;
3818
3819 UPDATE CSD_REPAIRS
3820 SET ro_txn_status = 'OM_SHIPPED'
3821 WHERE repair_line_id =
3822 p_product_txn_rec.REPAIR_LINE_ID;
3823 IF SQL%NOTFOUND
3824 THEN
3825 Fnd_Message.SET_NAME('CSD',
3826 'CSD_ERR_REPAIRS_UPDATE');
3827 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3828 p_product_txn_rec.repair_line_id);
3829 Fnd_Msg_Pub.ADD;
3830 RAISE SHIP_ORDER;
3831 END IF;
3832
3833 ELSIF l_released_status IN ('I', 'C')
3834 THEN
3835
3836 IF (p_product_txn_rec.ACTION_TYPE IN
3837 ('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY')) -- swai: 5931926 12.0.2
3838 THEN
3839 UPDATE CSD_PRODUCT_TRANSACTIONS
3840 SET prod_txn_status = 'SHIPPED',
3841 ship_sales_order_flag = 'Y'
3842 WHERE product_transaction_id =
3843 p_product_txn_rec.PRODUCT_TRANSACTION_ID;
3844 IF SQL%NOTFOUND
3845 THEN
3846 Fnd_Message.SET_NAME('CSD',
3847 'CSD_ERR_PRD_TXN_UPDATE');
3848 Fnd_Message.SET_TOKEN('PRODUCT_TRANSACTION_ID',
3849 p_product_txn_rec.PRODUCT_TRANSACTION_ID);
3850 Fnd_Msg_Pub.ADD;
3851 RAISE SHIP_ORDER;
3852 END IF;
3853 END IF;
3854
3855 UPDATE CSD_REPAIRS
3856 SET ro_txn_status = 'OM_SHIPPED'
3857 WHERE repair_line_id =
3858 p_product_txn_rec.REPAIR_LINE_ID;
3859 IF SQL%NOTFOUND
3860 THEN
3861 Fnd_Message.SET_NAME('CSD',
3862 'CSD_ERR_REPAIRS_UPDATE');
3863 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3864 p_product_txn_rec.repair_line_id);
3865 Fnd_Msg_Pub.ADD;
3866 RAISE SHIP_ORDER;
3867 END IF;
3868
3869 ELSIF l_released_status = 'S'
3870 THEN
3871
3872 IF (p_product_txn_rec.ACTION_TYPE IN
3873 ('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY')) -- swai: 5931926 12.0.2
3874 THEN
3875 UPDATE CSD_PRODUCT_TRANSACTIONS
3876 SET prod_txn_status = 'BOOKED',
3877 book_sales_order_flag = 'Y'
3878 WHERE product_transaction_id =
3879 p_product_txn_rec.PRODUCT_TRANSACTION_ID;
3880 IF SQL%NOTFOUND
3881 THEN
3882 Fnd_Message.SET_NAME('CSD',
3883 'CSD_ERR_PRD_TXN_UPDATE');
3884 Fnd_Message.SET_TOKEN('PRODUCT_TRANSACTION_ID',
3885 p_product_txn_rec.PRODUCT_TRANSACTION_ID);
3886 Fnd_Msg_Pub.ADD;
3887 RAISE SHIP_ORDER;
3888 END IF;
3889 END IF;
3890
3891 UPDATE CSD_REPAIRS
3892 SET ro_txn_status = 'OM_BOOKED'
3893 WHERE repair_line_id =
3894 p_product_txn_rec.REPAIR_LINE_ID;
3895 IF SQL%NOTFOUND
3896 THEN
3897 Fnd_Message.SET_NAME('CSD',
3898 'CSD_ERR_REPAIRS_UPDATE');
3899 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3900 p_product_txn_rec.repair_line_id);
3901 Fnd_Msg_Pub.ADD;
3902 RAISE SHIP_ORDER;
3903 END IF;
3904
3905 END IF;
3906
3907 EXCEPTION
3908 WHEN SHIP_ORDER THEN
3909 Debug('In ship_order exception while shipping SO =' ||
3910 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3911 l_mod_name,
3912 1);
3913 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3914 WHEN OTHERS THEN
3915 Debug('In OTHERS exception while shipping SO =' ||
3916 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3917 l_mod_name,
3918 1);
3919 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3920 END ship_prodtxn;
3921
3922
3923 --bug#7551068
3924 /*------------------------------------------------------------------------*/
3925 /* procedure name: cancel_prodtxn */
3926 /* description : */
3927 /* Cancels the prod txn record */
3928 /* Parameters Required: */
3929 /* p_order_header_id IN order header id */
3930 /* p_order_line_id IN order line id */
3931 /*------------------------------------------------------------------------*/
3932 PROCEDURE cancel_prodtxn
3933 ( p_api_version IN NUMBER,
3934 p_commit IN VARCHAR2,
3935 p_init_msg_list IN VARCHAR2,
3936 x_return_status OUT NOCOPY VARCHAR2,
3937 x_msg_count OUT NOCOPY NUMBER,
3938 x_msg_data OUT NOCOPY VARCHAR2,
3939 p_prod_txn_id IN NUMBER,
3940 p_order_header_id IN NUMBER,
3941 p_order_line_id IN NUMBER
3942 ) IS
3943
3944 l_api_name CONSTANT VARCHAR2(30) := 'CANCEL_PRODTXN';
3945 l_api_version CONSTANT NUMBER := 1.0;
3946 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.cancel_prodtxn';
3947 l_org_id NUMBER;
3948
3949 CURSOR C_cancel_reason IS
3950 SELECT lookup_code
3951 FROM oe_lookups
3952 WHERE lookup_type = 'CANCEL_CODE'
3953 AND lookup_code = 'Not provided';
3954
3955
3956 CURSOR c_get_org_id (p_header_id in Number) IS
3957 SELECT org_id
3958 FROM oe_order_headers_all
3959 WHERE header_id = p_header_id;
3960
3961
3962
3963 l_Line_Tbl_Type OE_ORDER_PUB.Line_Tbl_Type;
3964 x_Line_Tbl_Type OE_ORDER_PUB.Line_Tbl_Type;
3965
3966 BEGIN
3967
3968 SAVEPOINT CANCEL_PRODTXN_PVT;
3969
3970 IF (Fnd_Log.level_procedure >= Fnd_Log.g_current_runtime_level)
3971 THEN
3972 Fnd_Log.STRING(Fnd_Log.level_procedure,
3973 'csd.plsql.csd_logistics_util.cancel_prodtxn.begin',
3974 'Entering cancel_prodtxn');
3975 --dbms_output.put_line('Entering');
3976 END IF;
3977
3978 IF NOT Fnd_Api.Compatible_API_Call(l_api_version,
3979 p_api_version,
3980 l_api_name,
3981 G_PKG_NAME)
3982 THEN
3983 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3984 END IF;
3985
3986
3987 -- Initialize message list if p_init_msg_list is set to TRUE.
3988 IF Fnd_Api.to_Boolean(p_init_msg_list)
3989 THEN
3990 Fnd_Msg_Pub.initialize;
3991 oe_Msg_Pub.initialize;
3992 END IF;
3993 -- Initialize API return status to success
3994 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3995 -- ---------------
3996
3997 OPEN c_get_org_id (p_order_header_id);
3998 FETCH c_get_org_id INTO l_org_id;
3999 CLOSE c_get_org_id;
4000
4001
4002 -- Set the Policy context as required for MOAC Uptake, Bug#4270709
4003 mo_global.set_policy_context('S',l_org_id);
4004
4005 --dbms_output.put_line('calling SAVE_MESSAGES_OFF');
4006
4007 Oe_Standard_Wf.SAVE_MESSAGES_OFF;
4008
4009 --dbms_output.put_line('Calling OE_Order_GRP.Process_Order');
4010 l_Line_Tbl_Type(1) := OE_Order_PUB.G_MISS_LINE_REC;/*Fixed for bug#5968687*/
4011
4012 OPEN C_cancel_reason;
4013 FETCH C_cancel_reason INTO l_Line_Tbl_Type(1).change_reason;
4014 CLOSE C_cancel_reason;
4015
4016 /*Fixed for bug#5968687
4017 Initialization of line table type record is moved up.
4018 Initialization should be done before assigning any value to record.
4019 */
4020 /* l_Line_Tbl_Type(1) := OE_Order_PUB.G_MISS_LINE_REC; */
4021 l_Line_Tbl_Type(1).header_id := p_order_header_id;
4022 l_Line_Tbl_Type(1).line_id := p_order_line_id;
4023 l_Line_Tbl_Type(1).cancelled_flag := 'Y';
4024 l_Line_Tbl_Type(1).ordered_quantity := 0;
4025
4026
4027 l_Line_Tbl_Type(1).operation := OE_GLOBALS.G_OPR_UPDATE;
4028 --bug#7551068
4029 OE_ORDER_PUB.Process_Line(
4030 p_line_tbl => l_Line_Tbl_Type,
4031 x_line_out_tbl => x_Line_Tbl_Type,
4032 x_return_status => x_return_status,
4033 x_msg_count => x_msg_count,
4034 x_msg_data => x_msg_data
4035 );
4036
4037 -- Change the Policy context back to multiple
4038 mo_global.set_policy_context('M',null);
4039
4040
4041 --dbms_output.put_line('ret status=['||x_return_status||']');
4042 ----dbms_output.put_line('ret msg=['||x_msg_data||']');
4043 --dbms_output.put_line('ret msg count=['||x_msg_count||']');
4044
4045 -- Check return status from the above procedure call
4046 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
4047 RAISE Fnd_Api.G_EXC_ERROR;
4048 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
4049 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4050 END IF;
4051
4052 --Update the product transactions table with the cancelled status.
4053 --
4054 UPDATE CSD_PRODUCT_TRANSACTIONS
4055 SET prod_txn_status = 'CANCELLED',
4056 LAST_UPDATE_DATE = SYSDATE,
4057 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
4058 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
4059 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
4060 WHERE product_transaction_id = p_prod_txn_id;
4061
4062 IF SQL%NOTFOUND THEN
4063 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4064 END IF;
4065 -- -------------------
4066 -- Api body ends here
4067 -- -------------------
4068 -- Standard check of p_commit.
4069 IF Fnd_Api.To_Boolean(p_commit)
4070 THEN
4071 COMMIT WORK;
4072 END IF;
4073
4074 IF (Fnd_Log.level_procedure >= Fnd_Log.g_current_runtime_level)
4075 THEN
4076 Fnd_Log.STRING(Fnd_Log.level_procedure,
4077 'csd.plsql.csd_logistics_util.cancel_prodtxn.end',
4078 'cancel_prodtxn completed');
4079 END IF;
4080
4081 -- Standard call to get message count and IF count is get message info.
4082 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
4083 p_data => x_msg_data);
4084 if(x_msg_count = 0) then
4085 oe_Msg_Pub.Count_And_Get(p_count => x_msg_count,
4086 p_data => x_msg_data);
4087 end if;
4088
4089 EXCEPTION
4090 WHEN Fnd_Api.g_exc_error THEN
4091 x_return_status := Fnd_Api.g_ret_sts_error;
4092 ROLLBACK TO CANCEL_PRODTXN_PVT;
4093 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
4094 p_data => x_msg_data);
4095 if(x_msg_count = 0) then
4096 oe_Msg_Pub.Count_And_Get(p_count => x_msg_count,
4097 p_data => x_msg_data);
4098 end if;
4099
4100 IF (Fnd_Log.level_error >= Fnd_Log.g_current_runtime_level)
4101 THEN
4102 Fnd_Log.STRING(Fnd_Log.level_error,
4103 'csd.plsql.csd_logistics_util.cancel_prodtxn',
4104 'EXC_ERROR[' || x_msg_data || ']');
4105 END IF;
4106 --dbms_output.put_line('exec error raised');
4107 WHEN Fnd_Api.g_exc_unexpected_error THEN
4108 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4109 ROLLBACK TO CANCEL_PRODTXN_PVT;
4110 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
4111 p_data => x_msg_data);
4112 if(x_msg_count = 0) then
4113 oe_Msg_Pub.Count_And_Get(p_count => x_msg_count,
4114 p_data => x_msg_data);
4115 end if;
4116
4117 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
4118 THEN
4119 Fnd_Log.STRING(Fnd_Log.level_exception,
4120 'csd.plsql.csd_logistics_util.cancel_prodtxn',
4121 'EXC_UNEXP_ERROR[' || x_msg_data || ']');
4122 END IF;
4123 --dbms_output.put_line('unexpected error raised');
4124 WHEN OTHERS THEN
4125 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4126 ROLLBACK TO CANCEL_PRODTXN_PVT;
4127
4128 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_unexp_error)
4129 THEN
4130 Fnd_Msg_Pub.add_exc_msg(g_pkg_name, l_api_name);
4131 END IF;
4132
4133 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
4134 p_data => x_msg_data);
4135 if(x_msg_count = 0) then
4136 oe_Msg_Pub.Count_And_Get(p_count => x_msg_count,
4137 p_data => x_msg_data);
4138 end if;
4139
4140 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
4141 THEN
4142 Fnd_Log.STRING(Fnd_Log.level_exception,
4143 'csd.plsql.csd_logistics_util.cancel_prodtxn',
4144 'SQL MEssage[' || SQLERRM || ']');
4145 END IF;
4146 --dbms_output.put_line('other exception raised');
4147 --dbms_output.put_line('sqlerrm'||SQLERRM);
4148 END cancel_prodtxn;
4149
4150
4151 procedure dbg_print(p_msg varchar2) is
4152 begin
4153 --dbms_output.put_line('['||p_msg||']');
4154 null;
4155 end dbg_print;
4156 procedure dbg_print_stack(p_msg_count number) is
4157 l_msg varchar2(2000);
4158
4159 begin
4160 IF p_MSG_COUNT > 1 THEN
4161 FOR i IN 1..p_MSG_COUNT LOOP
4162 l_msg := apps.FND_MSG_PUB.Get(i,apps.FND_API.G_FALSE) ;
4163 --dbms_output.put_line('Msg Data : ' || l_msg ) ;
4164 END LOOP ;
4165 ELSE
4166 l_msg := apps.FND_MSG_PUB.Get(1,apps.FND_API.G_FALSE) ;
4167 --dbms_output.put_line('Msg Data : ' || l_msg ) ;
4168 END IF ;
4169
4170 end dbg_print_stack;
4171
4172 ---------------------------------------------------------------------------------------------------------------------
4173 -- Declare Procedures --
4174 ---------------------------------------------------------------------------------------------------------------------
4175 -- Start of Comments --
4176 -- Procedure name : SET_RSV_REC
4177 -- Type : Private
4178 -- Function : To initialize the record that is to be passed into INV api
4179 -- Pre-reqs :
4180 -- Standard IN Parameters :
4181 -- Standard OUT Parameters :
4182 -- SET_CREATE_REC Parameters:
4183 -- p_rsv_serial_number :
4184 -- x_rsv_rec :
4185 -- End of Comments.
4186 ---------------------------------------------------------------------------------------------------------------------
4187 PROCEDURE SET_RSV_REC (
4188 p_rsv_serial_rec IN CSD_SERIAL_RESERVE_REC_TYPE,
4189 x_rsv_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type,
4190 x_return_status OUT NOCOPY VARCHAR2
4191 )
4192 IS
4193
4194 -- Declare local variables
4195 l_api_name CONSTANT VARCHAR2(30) := 'set_rsv_rec';
4196 l_debug_module CONSTANT VARCHAR2(100) := 'csd.plsql.'||G_PKG_NAME||'.'||l_api_name;
4197 -- Variables to check the log level according to the coding standards
4198 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
4199
4200 -- Declare cursors
4201
4202 BEGIN
4203
4204 -- Log API entry point
4205 IF (l_proc_level >= g_debug_level)THEN
4206 fnd_log.string
4207 (
4208 fnd_log.level_procedure,
4209 l_debug_module||'.start',
4210 'At the start of PLSQL procedure'
4211 );
4212 END IF;
4213
4214 x_rsv_rec.reservation_id := NULL;
4215 --x_rsv_rec.requirement_date := ?
4216 x_rsv_rec.demand_source_name := null;
4217 x_rsv_rec.primary_uom_id := NULL;
4218 x_rsv_rec.reservation_uom_code := NULL;
4219 x_rsv_rec.reservation_uom_id := NULL;
4220 x_rsv_rec.reservation_quantity := NULL;
4221 x_rsv_rec.primary_reservation_quantity := NULL;
4222 x_rsv_rec.autodetail_group_id := NULL;
4223 x_rsv_rec.external_source_code := 'CSD';
4224 x_rsv_rec.external_source_line_id := NULL;
4225 x_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
4226 x_rsv_rec.supply_source_header_id := NULL;
4227 x_rsv_rec.supply_source_line_id := NULL;
4228 x_rsv_rec.supply_source_name := NULL;
4229 x_rsv_rec.supply_source_line_detail := NULL;
4230 x_rsv_rec.subinventory_id := NULL;
4231
4232 x_rsv_rec.requirement_date := p_rsv_serial_Rec.order_schedule_date;
4233 x_RSV_REC.subinventory_code := p_rsv_serial_Rec.subinventory_code;
4234 x_Rsv_rec.locator_id := p_rsv_serial_Rec.locator_id;
4235 x_RSV_REC.serial_reservation_quantity := 1 ;
4236 x_RSV_REC.serial_number := p_rsv_serial_Rec.serial_number;
4237 x_rsv_rec.revision := p_rsv_serial_Rec.revision;
4238 x_rsv_rec.lot_number := p_rsv_serial_Rec.lot_number;
4239 x_rsv_rec.demand_source_header_id := INV_salesorder.GET_SALESORDER_FOR_OEHEADER(p_rsv_serial_rec.order_header_id);
4240 x_rsv_rec.demand_source_line_id := p_rsv_serial_rec.order_line_id;
4241 x_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_oe;
4242 x_rsv_rec.inventory_item_id := p_rsv_serial_rec.inventory_item_id;
4243 x_rsv_rec.organization_id := p_rsv_serial_rec.inv_organization_id;
4244 x_rsv_rec.reservation_uom_code := p_rsv_serial_rec.reservation_uom_code;
4245
4246 x_rsv_rec.lot_number_id := NULL;
4247 x_rsv_rec.pick_slip_number := NULL;
4248 x_rsv_rec.lpn_id := NULL;
4249 x_rsv_rec.ship_ready_flag := NULL;
4250 x_rsv_rec.demand_source_delivery := NULL;
4251
4252 x_rsv_rec.attribute_category := NULL;
4253 x_rsv_rec.attribute1 := NULL;
4254 x_rsv_rec.attribute2 := NULL;
4255 x_rsv_rec.attribute3 := NULL;
4256 x_rsv_rec.attribute4 := NULL;
4257 x_rsv_rec.attribute5 := NULL;
4258 x_rsv_rec.attribute6 := NULL;
4259 x_rsv_rec.attribute7 := NULL;
4260 x_rsv_rec.attribute8 := NULL;
4261 x_rsv_rec.attribute9 := NULL;
4262 x_rsv_rec.attribute10 := NULL;
4263 x_rsv_rec.attribute11 := NULL;
4264 x_rsv_rec.attribute12 := NULL;
4265 x_rsv_rec.attribute13 := NULL;
4266 x_rsv_rec.attribute14 := NULL;
4267 x_rsv_rec.attribute15 := NULL;
4268
4269
4270 -- Log API exit point
4271 IF (l_proc_level >= g_debug_level)THEN
4272 fnd_log.string
4273 (
4274 fnd_log.level_procedure,
4275 l_debug_module||'.end',
4276 'At the end of PLSQL procedure'
4277 );
4278 END IF;
4279 END SET_RSV_REC;
4280
4281
4282 /*------------------------------------------------------------------------*/
4283 /* procedure name: Reserve_Serial_Number */
4284 /* description : */
4285 /* Reserves a given serial numbers for the given order */
4286 /* Parameters Required: */
4287 /* p_serial_reserve_rec IN CSD_SERIAL_RESERVE_REC_TYPE */
4288 /* p_return_status OUT VARCHAR2(1) */
4289 /*------------------------------------------------------------------------*/
4290 PROCEDURE Reserve_Serial_Number
4291 ( p_serial_reserve_Rec IN CSD_SERIAL_RESERVE_REC_TYPE,
4292 x_return_status OUT NOCOPY VARCHAR2
4293 )
4294 IS
4295 -- Declare local variables
4296 l_api_name CONSTANT VARCHAR2(30) := 'reserve_serial_number';
4297 l_debug_module CONSTANT VARCHAR2(100) := 'csd.plsql.'||G_PKG_NAME||'.'||l_api_name;
4298 -- Variables to check the log level according to the coding standards
4299 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
4300 l_rsv_qry_Inp inv_reservation_global.mtl_reservation_rec_type;
4301 l_msg_Count NUMBER;
4302 l_msg_data VARCHAR2(4000);
4303 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
4304 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4305 l_serial_rsv_tbl inv_reservation_global.serial_number_tbl_type;
4306 l_orig_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4307 l_mtl_reservation_tbl_count NUMBER;
4308 lx_serial_rsv_tbl inv_reservation_global.serial_number_tbl_type;
4309 l_from_serial_rsv_tbl inv_reservation_global.serial_number_tbl_type;
4310 l_srl_rsv_match BOOLEAN;
4311 l_create_reservation BOOLEAN;
4312 l_highlevel_rsv BOOLEAN;
4313 l_serial_number MTL_SERIAL_NUMBERS.SERIAL_NUMBER%type;
4314 l_quantity_reserved NUMBER;
4315 l_reservation_id NUMBER;
4316 l_error_code VARCHAR2(2000);
4317
4318 --Define cursors
4319 --Cursor to get the serial numbers for the given reservation
4320 CURSOR Cur_Srl_Nums(p_inv_item_id NUMBER, p_Inv_Org_id NUMBER, p_reservation_Id number) is
4321 SELECT Serial_Number from MTL_SERIAL_NUMBERS
4322 WHERE INVENTORY_ITEM_ID = p_inv_item_id AND
4323 CURRENT_ORGANIZATION_ID = p_inv_org_id AND
4324 RESERVATION_ID = p_reservation_Id;
4325
4326
4327 BEGIN
4328
4329
4330 -- Log API entry point
4331 IF (l_proc_level >= g_debug_level)THEN
4332 fnd_log.string
4333 (
4334 fnd_log.level_procedure,
4335 l_debug_module||'.start',
4336 'At the start of PLSQL procedure'
4337 );
4338 END IF;
4339
4340 -- Populate the query input with the sales order id and oe_order lineid
4341 -- sales order id will be got from the api get_salesorder_for_oeheader
4342 -- private function.
4343
4344 l_rsv_qry_Inp.demand_source_header_id
4345 := INV_salesorder.GET_SALESORDER_FOR_OEHEADER(p_serial_reserve_Rec.Order_Header_Id);
4346 l_rsv_qry_Inp.demand_source_line_id := p_serial_reserve_Rec.Order_Line_Id;
4347
4348
4349 IF (l_proc_level >= g_debug_level)THEN
4350 fnd_log.string
4351 (
4352 fnd_log.level_procedure,
4353 l_debug_module,
4354 'Calling reservation api, QUERY_RESERVATION_OM_HDR_LINE, hdr id['
4355 ||to_char(p_serial_reserve_Rec.Order_Header_Id) ||']line id['
4356 ||to_char(p_serial_reserve_Rec.Order_Line_Id)||']'
4357 );
4358 END IF;
4359
4360 -- Call the query_reservation api to find the existing reservations.
4361 INV_RESERVATION_PUB.QUERY_RESERVATION_OM_HDR_LINE (
4362 P_API_VERSION_NUMBER => 1,
4363 P_INIT_MSG_LST => FND_API.G_FALSE,
4364 X_RETURN_STATUS => X_RETURN_STATUS,
4365 X_MSG_COUNT => l_MSG_COUNT,
4366 X_MSG_DATA => l_MSG_DATA,
4367 p_query_input => l_rsv_qry_inp,
4368 x_mtl_reservation_tbl => l_mtl_reservation_tbl,
4369 x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count,
4370 X_error_code => l_error_Code
4371 );
4372
4373 dbg_print_stack(l_msg_count);
4374
4375 IF(l_mtl_reservation_tbl_count > 0) THEN
4376
4377 IF (l_proc_level >= g_debug_level)THEN
4378 fnd_log.string
4379 (
4380 fnd_log.level_procedure,
4381 l_debug_module,
4382 'Reservations exist for the order header/line'
4383 );
4384 END IF;
4385
4386 l_srl_rsv_match := false;
4387 l_highlevel_rsv := false;
4388 -- Initialize the original serial number record.
4389 l_from_serial_rsv_tbl.delete;
4390
4391
4392 -- Loop through the existing reservations and then serial number
4393 -- for each reservation. If the serial number is found then
4394 -- set a flag, if the serial number does not exist on a reservation
4395 -- keep that reservation record so that it can be updated with
4396 -- the serial number later.
4397 -- l_from_serial_rsv_tbl with the existing reservation.
4398 FOR i in l_mtl_reservation_tbl.FIRST..l_mtl_reservation_tbl.LAST
4399 LOOP
4400
4401 --Fetch the serial numbers for the reservation
4402 OPEN Cur_Srl_Nums(l_rsv_rec.inventory_item_id, l_rsv_rec.organization_id,
4403 l_rsv_rec.reservation_id);
4404 FETCH Cur_Srl_Nums into l_serial_number;
4405
4406 IF(Cur_Srl_Nums%NOTFOUND) THEN
4407 -- This condiiton represents the case where there are
4408 -- reservations without any serial number. In this case
4409 -- update the reservation with serial number.
4410 l_rsv_rec := l_mtl_reservation_tbl(i);
4411 l_from_serial_rsv_tbl.DELETE;
4412 l_highlevel_rsv := true;
4413 END IF;
4414
4415 WHILE (Cur_Srl_Nums%FOUND) LOOP
4416 IF(p_serial_reserve_Rec.serial_number = l_serial_number) THEN
4417 l_srl_rsv_match := true;
4418 EXIT ;
4419 END IF;
4420 FETCH Cur_Srl_Nums into l_serial_number;
4421 END LOOP;
4422 IF(l_srl_rsv_match) then
4423 EXIT ;
4424 END IF;
4425
4426 if(NOT l_highlevel_rsv) THEN
4427 -- This condition represents the case where there are serial
4428 -- reservation but no match serial number; In this case update the
4429 -- last serial number with the current serial number.
4430 l_rsv_rec := l_mtl_reservation_tbl(i);
4431 l_from_serial_rsv_tbl(1).inventory_item_id := l_rsv_rec.inventory_item_Id;
4432 l_from_serial_rsv_tbl(1).serial_number := l_rsv_rec.serial_number;
4433 END IF;
4434
4435 END LOOP;
4436
4437 IF( NOT l_srl_rsv_match ) THEN
4438 l_orig_rsv_Rec := l_rsv_rec;
4439 --Populate the reservation record and update the reservation.
4440 l_rsv_rec.requirement_date := p_serial_reserve_Rec.order_schedule_date;
4441 l_RSV_REC.subinventory_code := p_serial_reserve_Rec.subinventory_code;
4442 l_RSV_REC.serial_reservation_quantity := 1 ;
4443 l_RSV_REC.serial_number := p_serial_reserve_Rec.serial_number;
4444 l_rsv_rec.revision := p_serial_reserve_Rec.revision;
4445 l_rsv_rec.lot_number := p_serial_reserve_Rec.lot_number;
4446
4447 -- Populate the serial number record
4448 l_serial_rsv_tbl(1).inventory_item_id := p_serial_reserve_Rec.inventory_item_id;
4449 l_serial_rsv_tbl(1).serial_number := p_serial_reserve_Rec.serial_number;
4450
4451
4452 IF (l_proc_level >= g_debug_level)THEN
4453 fnd_log.string
4454 (
4455 fnd_log.level_procedure,
4456 l_debug_module,
4457 'Calling update reservation api'
4458 );
4459 END IF;
4460
4461 INV_RESERVATION_PUB.UPDATE_RESERVATION (
4462 P_API_VERSION_NUMBER => 1,
4463 P_INIT_MSG_LST => FND_API.G_TRUE,
4464 X_RETURN_STATUS => X_RETURN_STATUS,
4465 X_MSG_COUNT => l_MSG_COUNT,
4466 X_MSG_DATA => l_MSG_DATA,
4467 p_original_rsv_rec => l_orig_rsv_rec,
4468 p_to_rsv_rec => l_rsv_rec,
4469 p_original_serial_number => l_from_serial_rsv_tbl,
4470 p_to_serial_number => l_serial_rsv_tbl
4471 );
4472 dbg_print_stack(l_msg_count);
4473 END IF;-- End if for no srl_rsv_match found
4474 ELSE
4475 l_create_reservation := true;
4476 END IF ; -- End if for rsv_count >0
4477
4478 if(l_create_reservation ) THEN
4479
4480 --l_rsv_rec.delete;
4481 set_rsv_Rec(p_serial_reserve_Rec, l_rsv_rec, x_return_status);
4482 l_serial_rsv_tbl.delete;
4483 l_serial_rsv_tbl(1).inventory_item_id := p_serial_reserve_rec.inventory_item_id;
4484 l_serial_rsv_tbl(1).serial_number := p_serial_reserve_rec.serial_number;
4485
4486 lx_serial_rsv_tbl.delete;
4487
4488 INV_RESERVATION_PUB.CREATE_RESERVATION (
4489 P_API_VERSION_NUMBER => 1,
4490 P_INIT_MSG_LST => FND_API.G_FALSE,
4491 X_RETURN_STATUS => x_RETURN_STATUS,
4492 X_MSG_COUNT => l_MSG_COUNT,
4493 X_MSG_DATA => l_MSG_DATA,
4494 P_RSV_REC => l_RSV_REC,
4495 P_SERIAL_NUMBER => l_serial_rsv_tbl,
4496 X_SERIAL_NUMBER => lx_serial_rsv_tbl,
4497 X_QUANTITY_RESERVED => l_QUANTITY_RESERVED,
4498 X_RESERVATION_ID => l_RESERVATION_ID
4499 );
4500 dbg_print_stack(l_msg_count);
4501 END IF;
4502
4503 EXCEPTION
4504 WHEN Fnd_Api.g_exc_error THEN
4505 x_return_status := Fnd_Api.g_ret_sts_error;
4506
4507 IF (Fnd_Log.level_error >= Fnd_Log.g_current_runtime_level)
4508 THEN
4509 Fnd_Log.STRING(Fnd_Log.level_error,
4510 'csd.plsql.csd_logistics_util.reserve_serial_number',
4511 'EXC_ERROR[' || l_msg_data || ']');
4512 END IF;
4513 --dbms_output.put_line('exec error raised');
4514 WHEN Fnd_Api.g_exc_unexpected_error THEN
4515 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4516 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
4517 THEN
4518 Fnd_Log.STRING(Fnd_Log.level_exception,
4519 'csd.plsql.csd_logistics_util.reserve_serial_number',
4520 'EXC_UNEXP_ERROR[' || l_msg_data || ']');
4521 END IF;
4522 --dbms_output.put_line('unexpected error raised');
4523 WHEN OTHERS THEN
4524 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4525 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
4526 THEN
4527 Fnd_Log.STRING(Fnd_Log.level_exception,
4528 'csd.plsql.csd_logistics_util.reserve_serial_number',
4529 'SQL MEssage[' || SQLERRM || ']');
4530 END IF;
4531
4532
4533 END Reserve_Serial_Number;
4534
4535 /*------------------------------------------------------------------------*/
4536 /* procedure name: Unreserve_Serial_Number */
4537 /* description : */
4538 /* Removes a reservation for the given order */
4539 /* Parameters Required: */
4540 /* p_serial_reserve_rec IN CSD_SERIAL_RESERVE_REC_TYPE */
4541 /* p_return_status OUT VARCHAR2(1) */
4542 /*------------------------------------------------------------------------*/
4543 PROCEDURE Unreserve_Serial_Number
4544 ( p_serial_reserve_Rec IN CSD_SERIAL_RESERVE_REC_TYPE,
4545 x_return_status OUT NOCOPY VARCHAR2
4546 ) IS
4547 -- Declare local variables
4548 l_api_name CONSTANT VARCHAR2(30) := 'unreserve_serial_number';
4549 l_debug_module CONSTANT VARCHAR2(100) := 'csd.plsql.'||G_PKG_NAME||'.'||l_api_name;
4550 -- Variables to check the log level according to the coding standards
4551 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
4552 l_rsv_qry_Inp inv_reservation_global.mtl_reservation_rec_type;
4553 l_msg_Count NUMBER;
4554 l_msg_data VARCHAR2(4000);
4555 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
4556 l_error_code varchar2(2000);
4557 l_mtl_reservation_tbl_count NUMBER;
4558 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4559 l_serial_rsv_tbl inv_reservation_global.serial_number_tbl_type;
4560
4561 l_serial_number MTL_SERIAL_NUMBERS.SERIAL_NUMBER%type;
4562 --Define cursors
4563 --Cursor to get the serial numbers for the given reservation
4564 CURSOR Cur_Srl_Nums(p_inv_item_id NUMBER, p_Inv_Org_id NUMBER, p_reservation_Id number) is
4565 SELECT Serial_Number from MTL_SERIAL_NUMBERS
4566 WHERE INVENTORY_ITEM_ID = p_inv_item_id AND
4567 CURRENT_ORGANIZATION_ID = p_inv_org_id AND
4568 RESERVATION_ID = p_reservation_Id;
4569
4570 BEGIN
4571 -- Log API entry point
4572 IF (l_proc_level >= g_debug_level)THEN
4573 fnd_log.string
4574 (
4575 fnd_log.level_procedure,
4576 l_debug_module||'.start',
4577 'At the start of PLSQL procedure'
4578 );
4579 END IF;
4580
4581 -- Populate the query input with the sales order id and oe_order lineid
4582 -- sales order id will be got from the api get_salesorder_for_oeheader
4583 -- private function.
4584
4585 l_rsv_qry_Inp.demand_source_header_id
4586 := INV_salesorder.GET_SALESORDER_FOR_OEHEADER(p_serial_reserve_Rec.Order_Header_Id);
4587 l_rsv_qry_Inp.demand_source_line_id := p_serial_reserve_Rec.Order_Line_Id;
4588
4589
4590 IF (l_proc_level >= g_debug_level)THEN
4591 fnd_log.string
4592 (
4593 fnd_log.level_procedure,
4594 l_debug_module,
4595 'Calling reservation api, QUERY_RESERVATION_OM_HDR_LINE'
4596 );
4597 END IF;
4598
4599 -- Call the query_reservation api to find the existing reservations.
4600 INV_RESERVATION_PUB.QUERY_RESERVATION_OM_HDR_LINE (
4601 P_API_VERSION_NUMBER => 1,
4602 P_INIT_MSG_LST => FND_API.G_FALSE,
4603 X_RETURN_STATUS => X_RETURN_STATUS,
4604 X_MSG_COUNT => l_MSG_COUNT,
4605 X_MSG_DATA => l_MSG_DATA,
4606 p_query_input => l_rsv_qry_inp,
4607 x_mtl_reservation_tbl => l_mtl_reservation_tbl,
4608 x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count,
4609 X_error_code => l_error_Code
4610 );
4611 dbg_print_stack(l_msg_count);
4612 dbg_print('After query..');
4613
4614
4615 IF(l_mtl_reservation_tbl_count > 0) THEN
4616
4617
4618
4619 IF (l_proc_level >= g_debug_level)THEN
4620 fnd_log.string
4621 (
4622 fnd_log.level_procedure,
4623 l_debug_module,
4624 'Reservations exist for the order header/line'
4625 );
4626 END IF;
4627
4628
4629 -- Loop through the existing reservations and then serial number
4630 -- for each reservation. If the serial number is found
4631 -- and if the serial number matches with the existing reservation
4632 -- delete
4633 FOR i in l_mtl_reservation_tbl.FIRST..l_mtl_reservation_tbl.LAST
4634 LOOP
4635
4636 l_rsv_rec := l_mtl_reservation_tbl(i);
4637 --Fetch the serial numbers for the reservation
4638 OPEN Cur_Srl_Nums(l_rsv_rec.inventory_item_id, l_rsv_rec.organization_id,
4639 l_rsv_rec.reservation_id);
4640 FETCH Cur_Srl_Nums into l_serial_number;
4641
4642 WHILE (Cur_Srl_Nums%FOUND) LOOP
4643 IF(p_serial_reserve_Rec.serial_number = l_serial_number) THEN
4644 l_serial_rsv_tbl.delete;
4645 l_serial_rsv_tbl(1).inventory_item_id := p_serial_reserve_rec.inventory_item_id;
4646 l_serial_rsv_tbl(1).serial_number := p_serial_reserve_rec.serial_number;
4647 -- Call the delete_reservation api to remove the existing reservations.
4648 INV_RESERVATION_PUB.DELETE_RESERVATION (
4649 P_API_VERSION_NUMBER => 1,
4650 P_INIT_MSG_LST => FND_API.G_FALSE,
4651 X_RETURN_STATUS => X_RETURN_STATUS,
4652 X_MSG_COUNT => l_MSG_COUNT,
4653 X_MSG_DATA => l_MSG_DATA,
4654 p_rsv_rec => l_rsv_rec,
4655 p_serial_number => l_serial_rsv_tbl
4656 );
4657 dbg_print_stack(l_msg_count);
4658 dbg_print('After delete..');
4659 EXIT;
4660 END IF;
4661 FETCH Cur_Srl_Nums into l_Serial_number;
4662 END LOOP;
4663 END LOOP;
4664
4665 END If;
4666
4667
4668 END Unreserve_Serial_Number;
4669
4670
4671 END Csd_Logistics_Util;
[Home] [Help]