1 PACKAGE BODY Csd_Logistics_Util AS
2 /* $Header: csdulogb.pls 120.35.12010000.3 2008/11/11 23:20:34 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.action_type in ('RMA_THIRD_PTY', 'SHIP_THIRD_PTY')))
2830 and (p_product_txn_rec.project_id <> FND_API.G_MISS_NUM)) then --bug#6075825
2831
2832 OPEN order_line_cu(p_prodtxn_db_attr.est_detail_id);
2833 FETCH order_line_cu into l_order_line_id, l_p_ship_from_org_id;
2834 CLOSE order_line_cu;
2835
2836 if (l_order_line_id) is not null then
2837 l_Line_Tbl_Type(1) := OE_Order_PUB.G_MISS_LINE_REC;
2838 l_Line_Tbl_Type(1).line_id := l_order_line_id;
2839 l_Line_Tbl_Type(1).operation := OE_GLOBALS.G_OPR_UPDATE;
2840
2841 -- taklam: update project and unit number fields
2842 if ((p_product_txn_rec.project_id is not null) or (p_product_txn_rec.unit_number is not null)) then
2843
2844 l_Line_Tbl_Type(1).end_item_unit_number := p_product_txn_rec.unit_number;
2845
2846 if (p_product_txn_rec.project_id is not null) then
2847 OPEN project_cu(p_product_txn_rec.project_id,l_p_ship_from_org_id);
2848 FETCH project_cu into l_project_count;
2849 CLOSE project_cu;
2850
2851 if (l_project_count >= 1) then
2852 l_Line_Tbl_Type(1).project_id := p_product_txn_rec.project_id;
2853 l_Line_Tbl_Type(1).task_id := p_product_txn_rec.task_id;
2854 else
2855 FND_MESSAGE.SET_NAME('CSD','CSD_ERR_PROJECT_UPDATE');
2856 FND_MESSAGE.SET_TOKEN('project_id',p_product_txn_rec.project_id);
2857 FND_MESSAGE.SET_TOKEN('ship_from_org_id',l_p_ship_from_org_id);
2858 FND_MSG_PUB.ADD;
2859 RAISE CREATE_ORDER;
2860 end if;
2861 end if;
2862 end if; -- end update project and unit number fields
2863
2864 -- swai: update 3rd party fields.
2865 -- IB Owner must be set to END_CUSTOMER and end_custoemr_id mustbe
2866 -- set to the SR customer account id in order for 3rd party lines to
2867 -- avoid changing IB ownership during material transactions.
2868 if (p_product_txn_rec.action_type in ('RMA_THIRD_PTY', 'SHIP_THIRD_PTY')) then
2869 -- get SR customer account
2870 OPEN sr_account_cu (p_product_txn_rec.repair_line_id);
2871 FETCH sr_account_cu into l_sr_account_id;
2872 CLOSE sr_account_cu;
2873 if (l_sr_account_id) is not null then
2874 l_Line_Tbl_Type(1).ib_owner := 'END_CUSTOMER';
2875 l_Line_Tbl_Type(1).end_customer_id := l_sr_account_id;
2876 end if;
2877 end if; -- end update 3rd party fields
2878
2879 OE_ORDER_PUB.Process_Line(
2880 p_line_tbl => l_Line_Tbl_Type,
2881 x_line_out_tbl => x_Line_Tbl_Type,
2882 x_return_status => x_return_status,
2883 x_msg_count => l_msg_count,
2884 x_msg_data => l_msg_data
2885 );
2886
2887 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2888 FND_MESSAGE.SET_NAME('CSD','CSD_ERR_OM_PROCESS_LINE');
2889 FND_MSG_PUB.ADD;
2890 RAISE CREATE_ORDER;
2891 END IF;
2892
2893 end if; -- order line is not null
2894 end if;
2895 -- end swai: bug 6001057
2896
2897
2898 END IF;
2899
2900 EXCEPTION
2901 WHEN CREATE_ORDER THEN
2902 Debug('In Create_order exception while submitting the charge line =' ||
2903 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
2904 l_mod_name,
2905 1);
2906 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
2907 WHEN OTHERS THEN
2908 Debug('In OTHERS exception while submitting the charge line =' ||
2909 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
2910 l_mod_name,
2911 1);
2912 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
2913
2914 END interface_prodtxn;
2915
2916
2917
2918 /*------------------------------------------------------------------------*/
2919 /* procedure name: book_prodtxn */
2920 /* description : */
2921 /* Books the prod txn record in Depot schema */
2922 /* Parameters Required: */
2923 /* p_product_txn_rec IN product transaction record */
2924 /* x_return_status OUT return status */
2925 /*------------------------------------------------------------------------*/
2926 PROCEDURE book_prodtxn
2927 (
2928 x_return_status OUT NOCOPY VARCHAR2,
2929 p_product_txn_rec IN Csd_Process_Pvt.PRODUCT_TXN_REC,
2930 p_prodtxn_db_attr IN Csd_Logistics_Util.PRODTXN_DB_ATTR_REC,
2931 px_order_rec IN OUT NOCOPY Csd_Process_Pvt.om_interface_rec
2932
2933 ) IS
2934
2935 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.book_prodtxn';
2936 l_return_status VARCHAR2(1);
2937 l_order_line_id NUMBER;
2938 l_booked_flag VARCHAR2(1);
2939 l_ship_from_org_id NUMBER;
2940 l_unit_selling_price oe_order_lines_all.unit_selling_price%TYPE;
2941
2942 l_msg_count NUMBER;
2943 l_msg_data VARCHAR2(2000);
2944 book_order EXCEPTION;
2945
2946 l_sr_account_id NUMBER; -- swai: bug 6001057
2947
2948 --taklam
2949 l_Line_Tbl_Type OE_ORDER_PUB.Line_Tbl_Type;
2950 x_Line_Tbl_Type OE_ORDER_PUB.Line_Tbl_Type;
2951 l_p_ship_from_org_id NUMBER;
2952 l_project_count NUMBER;
2953
2954 --taklam
2955 CURSOR project_cu(l_project_id NUMBER, l_p_ship_from_org_id NUMBER) IS
2956 SELECT COUNT(*) p_count
2957 FROM PJM_PROJECTS_ORG_V
2958 WHERE project_id = l_project_id and inventory_organization_id = l_p_ship_from_org_id;
2959
2960 -- swai: bug 6001057
2961 CURSOR sr_account_cu (l_repair_line_id NUMBER) is
2962 SELECT account_id
2963 FROM cs_incidents_all_b cs, csd_repairs csd
2964 WHERE cs.incident_id = csd.incident_id
2965 AND repair_line_id = l_repair_line_id;
2966
2967 BEGIN
2968
2969
2970 Debug('l_est_detail_id = ' || p_product_txn_rec.estimate_detail_id,
2971 l_mod_name,
2972 1);
2973
2974 x_return_status := Fnd_Api.G_Ret_Sts_SUCCESS;
2975 BEGIN
2976 SELECT b.order_header_id,
2977 b.order_line_id,
2978 a.booked_flag
2979 INTO px_order_rec.order_header_id,
2980 l_order_line_id,
2981 l_booked_flag
2982 FROM oe_order_lines_all a, cs_estimate_details b
2983 WHERE a.line_id = b.order_line_id
2984 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;
2985 EXCEPTION
2986 WHEN NO_DATA_FOUND THEN
2987 FND_MESSAGE.SET_NAME('CSD','CSD_API_BOOKING_FAILED'); /*Fixed for bug#5147030 message changed*/
2988 /*
2989 Fnd_Message.SET_NAME('CSD',
2990 'CSD_INV_EST_DETAIL_ID');
2991 Fnd_Message.SET_TOKEN('ESTIMATE_DETAIL_ID',
2992 p_product_txn_rec.estimate_detail_id); */
2993 Fnd_Msg_Pub.ADD;
2994 Debug('Invalid estimate detail id = ' ||
2995 p_product_txn_rec.estimate_detail_id,
2996 l_mod_name,
2997 1);
2998 RAISE BOOK_ORDER;
2999 WHEN TOO_MANY_ROWS THEN
3000 Debug('Too many from book_sales_order1',
3001 l_mod_name,
3002 1);
3003 RAISE BOOK_ORDER;
3004 END;
3005
3006
3007 --bug#6071005
3008 px_order_rec.order_line_id := l_order_line_id;
3009
3010 Debug('order_header_id = ' ||
3011 px_order_rec.order_header_id,
3012 l_mod_name,
3013 1);
3014 Debug('l_booked_flag = ' || l_booked_flag,
3015 l_mod_name,
3016 1);
3017
3018 BEGIN
3019 -- To Book an Order Sales Rep and ship_from_org_id is reqd
3020 -- so check if the Order header has it
3021 SELECT ship_from_org_id, unit_selling_price, org_id
3022 INTO l_ship_from_org_id,
3023 l_unit_selling_price,
3024 px_order_rec.org_id
3025 FROM oe_order_lines_all
3026 WHERE line_id = l_order_line_id;
3027 EXCEPTION
3028 WHEN NO_DATA_FOUND THEN
3029 Fnd_Message.SET_NAME('CSD',
3030 'CSD_API_SALES_REP_MISSING');
3031 Fnd_Message.SET_TOKEN('ORDER_LINE_ID',
3032 l_order_line_id);
3033 Fnd_Msg_Pub.ADD;
3034 Debug('Sales rep missing for Line Id=' ||
3035 l_order_line_id,
3036 l_mod_name,
3037 1);
3038 RAISE BOOK_ORDER;
3039 WHEN TOO_MANY_ROWS THEN
3040 Debug('Too many from book_sales_order2',
3041 l_mod_name,
3042 1);
3043 END;
3044
3045 IF l_ship_from_org_id IS NULL
3046 THEN
3047 Fnd_Message.SET_NAME('CSD',
3048 'CSD_API_SHIP_FROM_ORG_MISSING');
3049 Fnd_Message.SET_TOKEN('ORDER_LINE_ID',
3050 l_order_line_id);
3051 Fnd_Msg_Pub.ADD;
3052 Debug('Ship from Org Id missing for Line id=' ||
3053 l_order_line_id,
3054 l_mod_name,
3055 1);
3056 RAISE BOOK_ORDER;
3057 END IF;
3058
3059 IF l_unit_selling_price IS NULL
3060 THEN
3061 Fnd_Message.SET_NAME('CSD',
3062 'CSD_API_PRICE_MISSING');
3063 Fnd_Message.SET_TOKEN('ORDER_LINE_ID',
3064 l_order_line_id);
3065 Fnd_Msg_Pub.ADD;
3066 Debug('Unit selling Price missing for Line id=' ||
3067 l_order_line_id,
3068 l_mod_name,
3069 1);
3070 RAISE BOOK_ORDER;
3071 END IF;
3072
3073 IF l_booked_flag = 'N'
3074 THEN
3075 -- swai: bug 6001057
3076 -- rearranged code so that call to OM API can be used to update
3077 -- project, unit number, or 3rd party end_customer
3078 if (((p_product_txn_rec.project_id is not null)
3079 OR (p_product_txn_rec.unit_number is not null)
3080 OR (p_product_txn_rec.action_type in ('RMA_THIRD_PTY', 'SHIP_THIRD_PTY')))
3081 and (p_product_txn_rec.project_id <> FND_API.G_MISS_NUM)) then --bug#6075825
3082
3083 if (l_order_line_id) is not null then
3084 l_Line_Tbl_Type(1) := OE_Order_PUB.G_MISS_LINE_REC;
3085 l_Line_Tbl_Type(1).line_id := l_order_line_id;
3086 l_Line_Tbl_Type(1).operation := OE_GLOBALS.G_OPR_UPDATE;
3087
3088 -- taklam: update projects fields
3089 if ((p_product_txn_rec.project_id is not null) or (p_product_txn_rec.unit_number is not null)) then
3090
3091 l_Line_Tbl_Type(1).end_item_unit_number := p_product_txn_rec.unit_number;
3092
3093 if (p_product_txn_rec.project_id is not null) then
3094 OPEN project_cu(p_product_txn_rec.project_id,l_ship_from_org_id);
3095 FETCH project_cu into l_project_count;
3096 CLOSE project_cu;
3097
3098 if (l_project_count >= 1) then
3099 l_Line_Tbl_Type(1).project_id := p_product_txn_rec.project_id;
3100 l_Line_Tbl_Type(1).task_id := p_product_txn_rec.task_id;
3101 else
3102 FND_MESSAGE.SET_NAME('CSD','CSD_ERR_PROJECT_UPDATE');
3103 FND_MESSAGE.SET_TOKEN('project_id',p_product_txn_rec.project_id);
3104 FND_MESSAGE.SET_TOKEN('ship_from_org_id',l_ship_from_org_id);
3105 FND_MSG_PUB.ADD;
3106 RAISE BOOK_ORDER;
3107 end if;
3108 end if;
3109 end if; -- end update projects fields
3110
3111 -- swai: update 3rd party fields.
3112 -- IB Owner must be set to END_CUSTOMER and end_custoemr_id mustbe
3113 -- set to the SR customer account id in order for 3rd party lines to
3114 -- avoid changing IB ownership during material transactions.
3115 if (p_product_txn_rec.action_type in ('RMA_THIRD_PTY', 'SHIP_THIRD_PTY')) then
3116 -- get SR customer account
3117 OPEN sr_account_cu (p_product_txn_rec.repair_line_id);
3118 FETCH sr_account_cu into l_sr_account_id;
3119 CLOSE sr_account_cu;
3120 if (l_sr_account_id) is not null then
3121 l_Line_Tbl_Type(1).ib_owner := 'END_CUSTOMER';
3122 l_Line_Tbl_Type(1).end_customer_id := l_sr_account_id;
3123 end if;
3124 end if; -- end update 3rd party fields
3125
3126 OE_ORDER_PUB.Process_Line(
3127 p_line_tbl => l_Line_Tbl_Type,
3128 x_line_out_tbl => x_Line_Tbl_Type,
3129 x_return_status => x_return_status,
3130 x_msg_count => l_msg_count,
3131 x_msg_data => l_msg_data
3132 );
3133
3134 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3135 FND_MESSAGE.SET_NAME('CSD','CSD_ERR_OM_PROCESS_LINE');
3136 FND_MSG_PUB.ADD;
3137 RAISE BOOK_ORDER;
3138 END IF;
3139 end if; -- order line is not null
3140 end if; -- update OM line criteria
3141 -- end swai: bug 6001057
3142
3143 Debug('Call process_sales_order to Book SO',
3144 l_mod_name,
3145 1);
3146 Debug('l_order_rec.org_id' || px_order_rec.org_id,
3147 l_mod_name,
3148 1);
3149
3150 Csd_Process_Pvt.PROCESS_SALES_ORDER(p_api_version => 1.0,
3151 p_commit => Fnd_Api.g_false,
3152 p_init_msg_list => Fnd_Api.g_false,
3153 p_validation_level => Fnd_Api.g_valid_level_full,
3154 p_action => 'BOOK',
3155 p_order_rec => px_order_rec,
3156 x_return_status => l_return_status,
3157 x_msg_count => l_msg_count,
3158 x_msg_data => l_msg_data);
3159
3160 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
3161 THEN
3162 Debug('Process_sales_order failed',
3163 l_mod_name,
3164 1);
3165 RAISE BOOK_ORDER;
3166 END IF;
3167
3168 Debug('Update the prod txn status to BOOKED',
3169 l_mod_name,
3170 1);
3171
3172 -- UPDATE csd_product_transactions
3173 -- SET prod_txn_status = 'BOOKED',
3174 -- book_sales_order_flag = 'Y'
3175 -- WHERE product_transaction_id = x_product_txn_rec.PRODUCT_TRANSACTION_ID;
3176 -- IF SQL%NOTFOUND then
3177 -- FND_MESSAGE.SET_NAME('CSD','CSD_ERR_PRD_TXN_UPDATE');
3178 -- FND_MESSAGE.SET_TOKEN('PRODUCT_TRANSACTION_ID',x_product_txn_rec.PRODUCT_TRANSACTION_ID);
3179 -- FND_MSG_PUB.ADD;
3180 -- RAISE BOOK_ORDER;
3181 -- END IF;
3182
3183 -- Fix for bug#4020651
3184 Csd_Update_Programs_Pvt.prod_txn_status_upd(p_repair_line_id => p_product_txn_rec.repair_line_id,
3185 p_commit => Fnd_Api.g_false);
3186
3187 UPDATE CSD_REPAIRS
3188 SET ro_txn_status = 'OM_BOOKED'
3189 WHERE repair_line_id =
3190 p_product_txn_rec.REPAIR_LINE_ID;
3191 IF SQL%NOTFOUND
3192 THEN
3193 Fnd_Message.SET_NAME('CSD',
3194 'CSD_ERR_REPAIRS_UPDATE');
3195 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3196 p_product_txn_rec.repair_line_id);
3197 Fnd_Msg_Pub.ADD;
3198 RAISE BOOK_ORDER;
3199 END IF;
3200
3201 ELSIF l_booked_flag = 'Y'
3202 THEN
3203
3204 Debug('Update the prod txn status to BOOKED',
3205 l_mod_name,
3206 1);
3207
3208 -- UPDATE csd_product_transactions
3209 -- SET prod_txn_status = 'BOOKED',
3210 -- book_sales_order_flag = 'Y'
3211 -- WHERE product_transaction_id = x_product_txn_rec.PRODUCT_TRANSACTION_ID;
3212 -- IF SQL%NOTFOUND then
3213 -- FND_MESSAGE.SET_NAME('CSD','CSD_ERR_PRD_TXN_UPDATE');
3214 -- FND_MESSAGE.SET_TOKEN('PRODUCT_TRANSACTION_ID',x_product_txn_rec.PRODUCT_TRANSACTION_ID);
3215 -- FND_MSG_PUB.ADD;
3216 -- RAISE BOOK_ORDER;
3217 -- END IF;
3218
3219 -- Fix for bug#4020651
3220 Csd_Update_Programs_Pvt.prod_txn_status_upd(p_repair_line_id => p_product_txn_rec.repair_line_id,
3221 p_commit => Fnd_Api.g_false);
3222
3223 UPDATE CSD_REPAIRS
3224 SET ro_txn_status = 'OM_BOOKED'
3225 WHERE repair_line_id =
3226 p_product_txn_rec.REPAIR_LINE_ID;
3227 IF SQL%NOTFOUND
3228 THEN
3229 Fnd_Message.SET_NAME('CSD',
3230 'CSD_ERR_REPAIRS_UPDATE');
3231 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3232 p_product_txn_rec.repair_line_id);
3233 Fnd_Msg_Pub.ADD;
3234 RAISE BOOK_ORDER;
3235 END IF;
3236
3237 END IF; -- l_booked_flag if condition
3238
3239 EXCEPTION
3240 WHEN BOOK_ORDER THEN
3241 Debug('In Book_order exception while booking the order line =' ||
3242 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3243 l_mod_name,
3244 1);
3245 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3246 WHEN OTHERS THEN
3247 Debug('In OTHERS exception while booking the order line =' ||
3248 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3249 l_mod_name,
3250 1);
3251 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3252
3253 END book_prodtxn;
3254
3255
3256 /*------------------------------------------------------------------------*/
3257 /* procedure name: pickrelease_prodtxn */
3258 /* description : */
3259 /* pick releases the prod txn record in Depot schema */
3260 /* Parameters Required: */
3261 /* p_product_txn_rec IN product transaction record */
3262 /* */
3263 /*------------------------------------------------------------------------*/
3264 PROCEDURE pickrelease_prodtxn
3265 (
3266 x_return_status OUT NOCOPY VARCHAR2,
3267 p_product_txn_rec IN Csd_Process_Pvt.PRODUCT_TXN_REC,
3268 p_prodtxn_db_attr IN Csd_Logistics_Util.PRODTXN_DB_ATTR_REC,
3269 px_order_rec IN OUT NOCOPY Csd_Process_Pvt.om_interface_rec
3270 ) IS
3271 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.pickrelease_prodtxn';
3272 l_ship_from_org_id NUMBER;
3273 l_picking_rule_id NUMBER;
3274 l_released_status wsh_delivery_details.released_status%TYPE;
3275 l_order_header_id NUMBER;
3276 l_return_status VARCHAR2(1);
3277 l_msg_count NUMBER;
3278 l_msg_data VARCHAR2(2000);
3279
3280 release_order EXCEPTION;
3281
3282 l_eligible_lines_pick_release NUMBER; /*Bug#4992402 */
3283
3284 /* R12 SN reservations integration change Begin */
3285 l_ItemAttributes Csd_Logistics_Util.ItemAttributes_Rec_Type;
3286 l_auto_reserve_profile VARCHAR2(10);
3287 l_srl_reservation_id NUMBER;
3288 l_serial_rsv_rec CSD_SERIAL_RESERVE_REC_TYPE ;
3289 l_order_line_id NUMBER;
3290 /* R12 SN reservations integration change End */
3291
3292
3293 BEGIN
3294 x_return_status := Fnd_Api.G_Ret_Sts_SUCCESS;
3295
3296 BEGIN
3297 /* Adding order_header_id and order_line_id in the select list
3298 for serial reservations change for R12, Vijay June 9th 2006 */
3299 SELECT ship_from_org_id, header_id, line_id
3300 INTO l_ship_from_org_id, l_order_header_id, l_order_line_id
3301 FROM oe_order_lines_all oel,
3302 cs_estimate_details ced
3303 WHERE oel.line_id = ced.order_line_id
3304 AND ced.estimate_detail_id =
3305 p_product_txn_rec.estimate_detail_id;
3306 EXCEPTION
3307 WHEN NO_DATA_FOUND THEN
3308 Debug('Order Line not found ', l_mod_name, 1);
3309 RAISE RELEASE_ORDER;
3310 END;
3311
3312 IF NVL(p_product_txn_rec.sub_inventory,
3313 Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR
3314 THEN
3315 px_order_rec.PICK_FROM_SUBINVENTORY := p_product_txn_rec.sub_inventory;
3316 END IF;
3317
3318
3319 /* R12 SN reservations change Begin */
3320 -- Get Item attributes in local variable
3321 Get_ItemAttributes(p_Inventory_Item_Id => p_Product_Txn_Rec.Inventory_Item_Id,
3322 p_inv_org_id => p_Product_Txn_Rec.inventory_org_id,
3323 x_ItemAttributes => l_ItemAttributes);
3324 -- Get the default pick rule id
3325 Fnd_Profile.Get('CSD_AUTO_SRL_RESERVE',
3326 l_auto_reserve_profile);
3327 if(l_auto_reserve_profile is null) then
3328 l_auto_reserve_profile := 'N';
3329 end if;
3330
3331 Debug('Going to process reservation..', l_mod_name, 1);
3332 Debug(l_auto_reserve_profile, l_mod_name,1);
3333 Debug(p_Product_Txn_Rec.source_Serial_number, l_mod_name,1);
3334 Debug(p_Product_Txn_Rec.sub_inventory, l_mod_name,1);
3335 Debug(p_Product_Txn_Rec.action_type, l_mod_name,1);
3336 Debug(to_char(l_itemAttributes.reservable_type), l_mod_name,1);
3337 Debug(to_char(l_itemAttributes.serial_Code), l_mod_name,1);
3338
3339
3340 IF( l_auto_reserve_profile = 'Y'
3341 AND p_Product_Txn_Rec.source_Serial_number is not null
3342 AND p_Product_Txn_Rec.sub_inventory is not null
3343 AND p_product_txn_rec.action_type IN ('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY') -- swai: 5931926 12.0.2
3344 AND l_ItemAttributes.reservable_type = C_RESERVABLE
3345 AND (l_ItemAttributes.serial_code = C_SERIAL_CONTROL_AT_RECEIPT
3346 OR
3347 l_ItemAttributes.serial_code = C_SERIAL_CONTROL_PREDEFINED) ) THEN
3348
3349 Debug('Checking reservation id for serial number..['
3350 ||p_Product_Txn_Rec.source_Serial_number||']', l_mod_name, 1);
3351
3352 l_serial_rsv_rec.inventory_item_id := p_Product_Txn_Rec.inventory_item_id;
3353 l_serial_rsv_rec.inv_organization_id := p_Product_Txn_Rec.inventory_org_id;
3354 l_serial_rsv_rec.order_header_id := l_order_header_id;
3355 l_serial_rsv_rec.order_line_id := l_order_line_Id;
3356 l_serial_rsv_rec.order_schedule_date := sysdate;
3357 l_serial_rsv_rec.serial_number := p_Product_Txn_Rec.source_serial_number;
3358 l_serial_rsv_rec.locator_id := p_Product_Txn_Rec.locator_id;
3359 l_serial_rsv_rec.revision := p_Product_Txn_Rec.revision;
3360 l_serial_rsv_rec.lot_number := p_Product_Txn_Rec.lot_number;
3361 l_serial_rsv_rec.subinventory_code := p_Product_Txn_Rec.sub_inventory;
3362 l_serial_rsv_rec.reservation_uom_code := p_Product_Txn_Rec.Unit_Of_Measure_Code;
3363
3364 Debug('Calling reserve serial..', l_mod_name, 1);
3365 Reserve_serial_number(l_serial_rsv_rec, l_return_status);
3366
3367 if(l_return_status = FND_API.G_RET_STS_ERROR) THEN
3368 Fnd_Message.SET_NAME('CSD',
3369 'CSD_SRL_RESERVE_FAILED');
3370 Fnd_Msg_Pub.ADD;
3371 RAISE RELEASE_ORDER;
3372 END IF;
3373
3374 END IF;
3375
3376 /* R12 SN reservations change End */
3377
3378 -- R12 development changes
3379 -- Added the code to get the picking rule from profile only if the product_txn_rec does
3380 -- not have it.
3381 IF (p_product_txn_rec.picking_rule_id IS NULL)
3382 THEN
3383 -- Get the default pick rule id
3384 Fnd_Profile.Get('CSD_DEF_PICK_RELEASE_RULE',
3385 l_picking_rule_id);
3386
3387 Debug('l_picking_rule_id =' || l_picking_rule_id,
3388 l_mod_name,
3389 1);
3390 ELSE
3391
3392 l_picking_rule_id := p_product_txn_rec.picking_rule_id;
3393
3394 END IF; -- End of if for input pick_rule_id check.
3395
3396 BEGIN
3397 SELECT PICKING_RULE_ID
3398 INTO l_picking_rule_id
3399 FROM WSH_PICKING_RULES
3400 WHERE picking_rule_id = l_picking_rule_id
3401 AND SYSDATE BETWEEN
3402 NVL(START_DATE_ACTIVE, SYSDATE) AND
3403 NVL(END_DATE_ACTIVE, SYSDATE + 1);
3404 px_order_rec.picking_rule_id := l_picking_rule_id;
3405 EXCEPTION
3406 WHEN NO_DATA_FOUND THEN
3407 Fnd_Message.SET_NAME('CSD',
3408 'CSD_API_INV_PICKING_RULE_ID');
3409 Fnd_Message.SET_TOKEN('PICKING_RULE_ID',
3410 px_order_rec.picking_rule_id);
3411 Fnd_Msg_Pub.ADD;
3412 RAISE RELEASE_ORDER;
3413 WHEN TOO_MANY_ROWS THEN
3414 Debug('Too many from release_sales_order1',
3415 l_mod_name,
3416 1);
3417 RAISE RELEASE_ORDER;
3418 END;
3419
3420 Debug('l_order_rec.pick_from_subinventory =' ||
3421 px_order_rec.PICK_FROM_SUBINVENTORY,
3422 l_mod_name,
3423 1);
3424 Debug('l_order_rec.picking_rule_id =' ||
3425 px_order_rec.picking_rule_id,
3426 l_mod_name,
3427 1);
3428
3429 BEGIN
3430 Debug('l_est_detail_id = ' || p_product_txn_rec.estimate_detail_id,
3431 l_mod_name,
3432 1);
3433 /*Bug#5049102
3434 Query given below is commented because this will return more than one row
3435 if more than one delivery exist for a given line id.
3436 This can happen in following cases:
3437 1) When ship line is created for more than 1 qty and user manually split the line
3438 in OM. After doing this if user tries to do the pick release from Depot it
3439 fails with error ORA-01422: exact fetch returns more than requested number of rows
3440 2) When ship line is created for more than 1 qty and user do the pick release for
3441 some qty and rest of qty is backordered.
3442 */
3443 /*
3444 SELECT a.released_status,
3445 b.order_header_id
3446 INTO l_released_status,
3447 l_order_header_id
3448 FROM wsh_delivery_details a,
3449 cs_estimate_details b
3450 WHERE a.source_line_id = b.order_line_id
3451 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id; */
3452
3453 /*Bug#5049102
3454 Select order header id from estimate table directly
3455 */
3456 SELECT b.order_header_id
3457 INTO l_order_header_id
3458 FROM cs_estimate_details b
3459 WHERE b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;
3460
3461 /*Bug#5049102
3462 The query given below will find if there is any
3463 delivery available for pick release or not.
3464 If there is no delivery eligible for pick-release
3465 then it does not call API for pick-release
3466 */
3467 l_eligible_lines_pick_release:=0;
3468
3469 SELECT count(*)
3470 INTO l_eligible_lines_pick_release
3471 FROM wsh_delivery_details a,
3472 cs_estimate_details b
3473 WHERE a.source_line_id = b.order_line_id
3474 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id
3475 /*Fixed for bug#5846054
3476 Added condition SOURCE_CODE = 'OE' while selecting
3477 delivery details from wsh_delivery_details. As per
3478 shipping team there can be multiple delivery lines
3479 with different source code can be created from
3480 inbound deliveries (WSH) and other is from order
3481 management (OE). While doing the pick release Depot
3482 should consider the source code as well.
3483 */
3484 AND a.SOURCE_CODE = 'OE'
3485 AND a.released_status in ('R','B');
3486
3487 EXCEPTION
3488 WHEN NO_DATA_FOUND THEN
3489 Fnd_Message.SET_NAME('CSD',
3490 'CSD_INV_EST_DETAIL_ID');
3491 Fnd_Message.SET_TOKEN('ESTIMATE_DETAIL_ID',
3492 p_product_txn_rec.estimate_detail_id);
3493 Fnd_Msg_Pub.ADD;
3494 Debug('Invalid estimate detail ID = ' ||
3495 p_product_txn_rec.estimate_detail_id,
3496 l_mod_name,
3497 1);
3498 RAISE RELEASE_ORDER;
3499 WHEN TOO_MANY_ROWS THEN
3500 Debug('Too many from release_sales_order2',
3501 l_mod_name,
3502 1);
3503 RAISE RELEASE_ORDER;
3504 END;
3505
3506 Debug('l_released_status =' || l_released_status,
3507 l_mod_name,
3508 1);
3509 Debug('l_order_header_id =' || l_order_header_id,
3510 l_mod_name,
3511 1);
3512
3513 px_order_rec.order_header_id := l_order_header_id;
3514 px_order_rec.org_id := l_ship_from_org_id;
3515
3516 -- Fix for Enh Req#3948563
3517 px_order_rec.locator_id := p_product_txn_rec.locator_id;
3518
3519
3520 /* IF (l_released_status = 'R') THEN */
3521 IF (l_eligible_lines_pick_release > 0 ) then /*bug#5049102 call API to pick release only if there are some eligible delivery */
3522 Debug('Call process_sales_order to Release SO',
3523 l_mod_name,
3524 1);
3525 Csd_Process_Pvt.PROCESS_SALES_ORDER(p_api_version => 1.0,
3526 p_commit => Fnd_Api.g_false,
3527 p_init_msg_list => Fnd_Api.g_false,
3528 p_validation_level => Fnd_Api.g_valid_level_full,
3529 p_action => 'PICK-RELEASE',
3530 p_order_rec => px_order_rec,
3531 p_product_txn_rec => p_product_txn_rec,
3532 x_return_status => l_return_status,
3533 x_msg_count => l_msg_count,
3534 x_msg_data => l_msg_data);
3535
3536 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
3537 THEN
3538 Debug('process_sales_order failed, x_msg_data['||l_msg_data||']',
3539 l_mod_name,
3540 1);
3541 RAISE RELEASE_ORDER;
3542 END IF;
3543 Debug('Released the SO for Prod Txn Id =' ||
3544 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3545 l_mod_name,
3546 1);
3547 END IF;
3548
3549 BEGIN
3550 /*Bug#5049102
3551 Query given below is commented because this will return more than one row
3552 if more than one delivery exist for a given line id.
3553 */
3554 /* SELECT a.released_status
3555 INTO l_released_status
3556 FROM wsh_delivery_details a,
3557 cs_estimate_details b
3558 WHERE a.source_line_id = b.order_line_id
3559 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;*/
3560
3561 /*Bug#5049102
3562 The query given below will find if there is any
3563 delivery available for pick release or not.
3564 If there is no delivery eligible for pick-release
3565 then it updates the ship line status
3566 */
3567
3568 l_eligible_lines_pick_release:=0;
3569
3570 SELECT count(*)
3571 INTO l_eligible_lines_pick_release
3572 FROM wsh_delivery_details a,
3573 cs_estimate_details b
3574 WHERE a.source_line_id = b.order_line_id
3575 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id
3576 AND a.released_status in ('R','B','S');
3577
3578 EXCEPTION
3579 WHEN NO_DATA_FOUND THEN
3580 Fnd_Message.SET_NAME('CSD',
3581 'CSD_INV_EST_DETAIL_ID');
3582 Fnd_Message.SET_TOKEN('ESTIMATE_DETAIL_ID',
3583 p_product_txn_rec.estimate_detail_id);
3584 Fnd_Msg_Pub.ADD;
3585 Debug('Invalid estimate detail ID = ' ||
3586 p_product_txn_rec.estimate_detail_id,
3587 l_mod_name,
3588 1);
3589 RAISE RELEASE_ORDER;
3590 WHEN TOO_MANY_ROWS THEN
3591 Debug('Too many from release_sales_order2',
3592 l_mod_name,
3593 1);
3594 RAISE RELEASE_ORDER;
3595 END;
3596
3597 /* IF (l_released_status = 'Y') THEN */
3598 IF (l_eligible_lines_pick_release = 0) THEN /*Bug#5049102 if all delivery are pick released then only update status */
3599
3600 IF (p_product_txn_rec.ACTION_TYPE IN
3601 ('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY')) -- swai: 5931926 12.0.2
3602 THEN
3603 UPDATE CSD_PRODUCT_TRANSACTIONS
3604 SET prod_txn_status = 'RELEASED',
3605 release_sales_order_flag = 'Y'
3606 WHERE product_transaction_id =
3607 p_product_txn_rec.PRODUCT_TRANSACTION_ID;
3608 IF SQL%NOTFOUND
3609 THEN
3610 Fnd_Message.SET_NAME('CSD',
3611 'CSD_ERR_PRD_TXN_UPDATE');
3612 Fnd_Message.SET_TOKEN('PRODUCT_TRANSACTION_ID',
3613 p_product_txn_rec.PRODUCT_TRANSACTION_ID);
3614 Fnd_Msg_Pub.ADD;
3615 RAISE RELEASE_ORDER;
3616 END IF;
3617 END IF;
3618
3619 UPDATE CSD_REPAIRS
3620 SET ro_txn_status = 'OM_RELEASED'
3621 WHERE repair_line_id =
3622 p_product_txn_rec.REPAIR_LINE_ID;
3623 IF SQL%NOTFOUND
3624 THEN
3625 Fnd_Message.SET_NAME('CSD',
3626 'CSD_ERR_REPAIRS_UPDATE');
3627 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3628 p_product_txn_rec.repair_line_id);
3629 Fnd_Msg_Pub.ADD;
3630 RAISE RELEASE_ORDER;
3631 END IF;
3632
3633 END IF;
3634
3635 EXCEPTION
3636 WHEN RELEASE_ORDER THEN
3637 Debug('In Release_order exception while releasing SO =' ||
3638 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3639 l_mod_name,
3640 1);
3641 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3642 WHEN OTHERS THEN
3643 Debug('In OTHERS exception while releasing SO =' ||
3644 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3645 l_mod_name,
3646 1);
3647 Debug('In OTHERS exception while releasing SO sqlerr=' ||
3648 sqlerrm,
3649 l_mod_name,
3650 1);
3651 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3652
3653
3654 END pickrelease_prodtxn;
3655
3656 /*------------------------------------------------------------------------*/
3657 /* procedure name: ship_prodtxn */
3658 /* description : */
3659 /* ships the prod txn record */
3660 /* Parameters Required: */
3661 /* p_product_txn_rec IN product transaction record */
3662 /* x_return_status OUT return status */
3663 /*------------------------------------------------------------------------*/
3664 PROCEDURE ship_prodtxn
3665 (
3666 x_return_status OUT NOCOPY VARCHAR2,
3667 p_product_txn_rec IN Csd_Process_Pvt.PRODUCT_TXN_REC,
3668 p_prodtxn_db_attr IN Csd_Logistics_Util.PRODTXN_DB_ATTR_REC,
3669 px_order_rec IN OUT NOCOPY Csd_Process_Pvt.om_interface_rec
3670 ) IS
3671 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.ship_prodtxn';
3672 l_ship_from_org_id NUMBER;
3673 l_picking_rule_id NUMBER;
3674 l_released_status wsh_delivery_details.released_status%TYPE;
3675 l_return_status VARCHAR2(1);
3676 l_msg_count NUMBER;
3677 l_msg_data VARCHAR2(2000);
3678 ship_order EXCEPTION;
3679
3680 BEGIN
3681 Debug('l_est_detail_id = ' || p_product_txn_rec.estimate_detail_id,
3682 l_mod_name,
3683 1);
3684 x_return_status := Fnd_Api.G_Ret_Sts_SUCCESS;
3685 BEGIN
3686 SELECT b.order_header_id,
3687 b.order_line_id,
3688 c.source_serial_number,
3689 a.ordered_quantity
3690 INTO px_order_rec.order_header_id,
3691 px_order_rec.order_line_id,
3692 px_order_rec.serial_number,
3693 px_order_rec.shipped_quantity
3694 FROM oe_order_lines_all a,
3695 cs_estimate_details b,
3696 CSD_PRODUCT_TRANSACTIONS c
3697 WHERE a.line_id = b.order_line_id
3698 AND b.estimate_detail_id = c.estimate_detail_id
3699 AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;
3700 EXCEPTION
3701 WHEN NO_DATA_FOUND THEN
3702 FND_MESSAGE.SET_NAME('CSD','CSD_API_SHIPPING_FAILD'); /*Fixed for bug#5147030 message changed*/
3703 /*
3704 Fnd_Message.SET_NAME('CSD',
3705 'CSD_API_INV_EST_DETAIL_ID');
3706 Fnd_Message.SET_TOKEN('ESTIMATE_DETAIL_ID',
3707 p_product_txn_rec.estimate_detail_id);
3708 */
3709 Fnd_Msg_Pub.ADD;
3710 Debug('Invalid Estimate Detail Id = ' ||
3711 p_product_txn_rec.estimate_detail_id,
3712 l_mod_name,
3713 1);
3714 RAISE SHIP_ORDER;
3715 WHEN TOO_MANY_ROWS THEN
3716 Debug('Too many found for the estimate detail id',
3717 l_mod_name,
3718 1);
3719 END;
3720
3721 Debug('order_header_id = ' ||
3722 px_order_rec.order_header_id,
3723 l_mod_name,
3724 1);
3725 Debug('serial_number = ' ||
3726 px_order_rec.serial_number,
3727 l_mod_name,
3728 1);
3729 Debug('shipped_quantity= ' ||
3730 px_order_rec.shipped_quantity,
3731 l_mod_name,
3732 1);
3733
3734 BEGIN
3735 SELECT released_status
3736 INTO l_released_status
3737 FROM wsh_delivery_details
3738 WHERE source_header_id =
3739 px_order_rec.order_header_id
3740 AND source_line_id = px_order_rec.order_line_id;
3741 EXCEPTION
3742 WHEN NO_DATA_FOUND THEN
3743 Fnd_Message.SET_NAME('CSD',
3744 'CSD_RELEASE_FAILED');
3745 Fnd_Message.SET_TOKEN('ORDER_LINE_ID',
3746 px_order_rec.order_line_id);
3747 Fnd_Msg_Pub.ADD;
3748 RAISE SHIP_ORDER;
3749 WHEN TOO_MANY_ROWS THEN
3750 Debug('Too many from ship_sales_order',
3751 l_mod_name,
3752 1);
3753 END;
3754
3755 Debug('l_released_status =' || l_released_status,
3756 l_mod_name,
3757 1);
3758
3759 IF l_released_status = 'Y'
3760 THEN
3761
3762 Debug('Call Process_sales_order to ship SO',
3763 l_mod_name,
3764 1);
3765 Csd_Process_Pvt.PROCESS_SALES_ORDER(p_api_version => 1.0,
3766 p_commit => Fnd_Api.g_false,
3767 p_init_msg_list => Fnd_Api.g_false,
3768 p_validation_level => Fnd_Api.g_valid_level_full,
3769 p_action => 'SHIP',
3770 /*Fixed for bug#4433942 passing product
3771 txn record as in parameter*/
3772 p_product_txn_rec => p_product_txn_rec,
3773 p_order_rec => px_order_rec,
3774 x_return_status => l_return_status,
3775 x_msg_count => l_msg_count,
3776 x_msg_data => l_msg_data);
3777
3778 IF NOT (l_return_status = Fnd_Api.G_RET_STS_SUCCESS)
3779 THEN
3780 Debug('Process_sales_order failed',
3781 l_mod_name,
3782 1);
3783 RAISE SHIP_ORDER;
3784 END IF;
3785
3786 IF (p_product_txn_rec.ACTION_TYPE IN
3787 ('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY')) -- swai: 5931926 12.0.2
3788 THEN
3789 UPDATE CSD_PRODUCT_TRANSACTIONS
3790 SET prod_txn_status = 'SHIPPED',
3791 ship_sales_order_flag = 'Y'
3792 WHERE product_transaction_id =
3793 p_product_txn_rec.PRODUCT_TRANSACTION_ID;
3794 IF SQL%NOTFOUND
3795 THEN
3796 Fnd_Message.SET_NAME('CSD',
3797 'CSD_ERR_PRD_TXN_UPDATE');
3798 Fnd_Message.SET_TOKEN('PRODUCT_TRANSACTION_ID',
3799 p_product_txn_rec.PRODUCT_TRANSACTION_ID);
3800 Fnd_Msg_Pub.ADD;
3801 RAISE SHIP_ORDER;
3802 END IF;
3803 END IF;
3804
3805 UPDATE CSD_REPAIRS
3806 SET ro_txn_status = 'OM_SHIPPED'
3807 WHERE repair_line_id =
3808 p_product_txn_rec.REPAIR_LINE_ID;
3809 IF SQL%NOTFOUND
3810 THEN
3811 Fnd_Message.SET_NAME('CSD',
3812 'CSD_ERR_REPAIRS_UPDATE');
3813 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3814 p_product_txn_rec.repair_line_id);
3815 Fnd_Msg_Pub.ADD;
3816 RAISE SHIP_ORDER;
3817 END IF;
3818
3819 ELSIF l_released_status IN ('I', 'C')
3820 THEN
3821
3822 IF (p_product_txn_rec.ACTION_TYPE IN
3823 ('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY')) -- swai: 5931926 12.0.2
3824 THEN
3825 UPDATE CSD_PRODUCT_TRANSACTIONS
3826 SET prod_txn_status = 'SHIPPED',
3827 ship_sales_order_flag = 'Y'
3828 WHERE product_transaction_id =
3829 p_product_txn_rec.PRODUCT_TRANSACTION_ID;
3830 IF SQL%NOTFOUND
3831 THEN
3832 Fnd_Message.SET_NAME('CSD',
3833 'CSD_ERR_PRD_TXN_UPDATE');
3834 Fnd_Message.SET_TOKEN('PRODUCT_TRANSACTION_ID',
3835 p_product_txn_rec.PRODUCT_TRANSACTION_ID);
3836 Fnd_Msg_Pub.ADD;
3837 RAISE SHIP_ORDER;
3838 END IF;
3839 END IF;
3840
3841 UPDATE CSD_REPAIRS
3842 SET ro_txn_status = 'OM_SHIPPED'
3843 WHERE repair_line_id =
3844 p_product_txn_rec.REPAIR_LINE_ID;
3845 IF SQL%NOTFOUND
3846 THEN
3847 Fnd_Message.SET_NAME('CSD',
3848 'CSD_ERR_REPAIRS_UPDATE');
3849 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3850 p_product_txn_rec.repair_line_id);
3851 Fnd_Msg_Pub.ADD;
3852 RAISE SHIP_ORDER;
3853 END IF;
3854
3855 ELSIF l_released_status = 'S'
3856 THEN
3857
3858 IF (p_product_txn_rec.ACTION_TYPE IN
3859 ('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY')) -- swai: 5931926 12.0.2
3860 THEN
3861 UPDATE CSD_PRODUCT_TRANSACTIONS
3862 SET prod_txn_status = 'BOOKED',
3863 book_sales_order_flag = 'Y'
3864 WHERE product_transaction_id =
3865 p_product_txn_rec.PRODUCT_TRANSACTION_ID;
3866 IF SQL%NOTFOUND
3867 THEN
3868 Fnd_Message.SET_NAME('CSD',
3869 'CSD_ERR_PRD_TXN_UPDATE');
3870 Fnd_Message.SET_TOKEN('PRODUCT_TRANSACTION_ID',
3871 p_product_txn_rec.PRODUCT_TRANSACTION_ID);
3872 Fnd_Msg_Pub.ADD;
3873 RAISE SHIP_ORDER;
3874 END IF;
3875 END IF;
3876
3877 UPDATE CSD_REPAIRS
3878 SET ro_txn_status = 'OM_BOOKED'
3879 WHERE repair_line_id =
3880 p_product_txn_rec.REPAIR_LINE_ID;
3881 IF SQL%NOTFOUND
3882 THEN
3883 Fnd_Message.SET_NAME('CSD',
3884 'CSD_ERR_REPAIRS_UPDATE');
3885 Fnd_Message.SET_TOKEN('REPAIR_LINE_ID',
3886 p_product_txn_rec.repair_line_id);
3887 Fnd_Msg_Pub.ADD;
3888 RAISE SHIP_ORDER;
3889 END IF;
3890
3891 END IF;
3892
3893 EXCEPTION
3894 WHEN SHIP_ORDER THEN
3895 Debug('In ship_order exception while shipping SO =' ||
3896 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3897 l_mod_name,
3898 1);
3899 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3900 WHEN OTHERS THEN
3901 Debug('In OTHERS exception while shipping SO =' ||
3902 p_product_txn_rec.PRODUCT_TRANSACTION_ID,
3903 l_mod_name,
3904 1);
3905 x_return_status := Fnd_Api.G_Ret_Sts_ERROR;
3906 END ship_prodtxn;
3907
3908
3909 --bug#7551068
3910 /*------------------------------------------------------------------------*/
3911 /* procedure name: cancel_prodtxn */
3912 /* description : */
3913 /* Cancels the prod txn record */
3914 /* Parameters Required: */
3915 /* p_order_header_id IN order header id */
3916 /* p_order_line_id IN order line id */
3917 /*------------------------------------------------------------------------*/
3918 PROCEDURE cancel_prodtxn
3919 ( p_api_version IN NUMBER,
3920 p_commit IN VARCHAR2,
3921 p_init_msg_list IN VARCHAR2,
3922 x_return_status OUT NOCOPY VARCHAR2,
3923 x_msg_count OUT NOCOPY NUMBER,
3924 x_msg_data OUT NOCOPY VARCHAR2,
3925 p_prod_txn_id IN NUMBER,
3926 p_order_header_id IN NUMBER,
3927 p_order_line_id IN NUMBER
3928 ) IS
3929
3930 l_api_name CONSTANT VARCHAR2(30) := 'CANCEL_PRODTXN';
3931 l_api_version CONSTANT NUMBER := 1.0;
3932 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.cancel_prodtxn';
3933 l_org_id NUMBER;
3934
3935 CURSOR C_cancel_reason IS
3936 SELECT lookup_code
3937 FROM oe_lookups
3938 WHERE lookup_type = 'CANCEL_CODE'
3939 AND lookup_code = 'Not provided';
3940
3941
3942 CURSOR c_get_org_id (p_header_id in Number) IS
3943 SELECT org_id
3944 FROM oe_order_headers_all
3945 WHERE header_id = p_header_id;
3946
3947
3948
3949 l_Line_Tbl_Type OE_ORDER_PUB.Line_Tbl_Type;
3950 x_Line_Tbl_Type OE_ORDER_PUB.Line_Tbl_Type;
3951
3952 BEGIN
3953
3954 SAVEPOINT CANCEL_PRODTXN_PVT;
3955
3956 IF (Fnd_Log.level_procedure >= Fnd_Log.g_current_runtime_level)
3957 THEN
3958 Fnd_Log.STRING(Fnd_Log.level_procedure,
3959 'csd.plsql.csd_logistics_util.cancel_prodtxn.begin',
3960 'Entering cancel_prodtxn');
3961 --dbms_output.put_line('Entering');
3962 END IF;
3963
3964 IF NOT Fnd_Api.Compatible_API_Call(l_api_version,
3965 p_api_version,
3966 l_api_name,
3967 G_PKG_NAME)
3968 THEN
3969 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3970 END IF;
3971
3972
3973 -- Initialize message list if p_init_msg_list is set to TRUE.
3974 IF Fnd_Api.to_Boolean(p_init_msg_list)
3975 THEN
3976 Fnd_Msg_Pub.initialize;
3977 oe_Msg_Pub.initialize;
3978 END IF;
3979 -- Initialize API return status to success
3980 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3981 -- ---------------
3982
3983 OPEN c_get_org_id (p_order_header_id);
3984 FETCH c_get_org_id INTO l_org_id;
3985 CLOSE c_get_org_id;
3986
3987
3988 -- Set the Policy context as required for MOAC Uptake, Bug#4270709
3989 mo_global.set_policy_context('S',l_org_id);
3990
3991 --dbms_output.put_line('calling SAVE_MESSAGES_OFF');
3992
3993 Oe_Standard_Wf.SAVE_MESSAGES_OFF;
3994
3995 --dbms_output.put_line('Calling OE_Order_GRP.Process_Order');
3996 l_Line_Tbl_Type(1) := OE_Order_PUB.G_MISS_LINE_REC;/*Fixed for bug#5968687*/
3997
3998 OPEN C_cancel_reason;
3999 FETCH C_cancel_reason INTO l_Line_Tbl_Type(1).change_reason;
4000 CLOSE C_cancel_reason;
4001
4002 /*Fixed for bug#5968687
4003 Initialization of line table type record is moved up.
4004 Initialization should be done before assigning any value to record.
4005 */
4006 /* l_Line_Tbl_Type(1) := OE_Order_PUB.G_MISS_LINE_REC; */
4007 l_Line_Tbl_Type(1).header_id := p_order_header_id;
4008 l_Line_Tbl_Type(1).line_id := p_order_line_id;
4009 l_Line_Tbl_Type(1).cancelled_flag := 'Y';
4010 l_Line_Tbl_Type(1).ordered_quantity := 0;
4011
4012
4013 l_Line_Tbl_Type(1).operation := OE_GLOBALS.G_OPR_UPDATE;
4014 --bug#7551068
4015 OE_ORDER_PUB.Process_Line(
4016 p_line_tbl => l_Line_Tbl_Type,
4017 x_line_out_tbl => x_Line_Tbl_Type,
4018 x_return_status => x_return_status,
4019 x_msg_count => x_msg_count,
4020 x_msg_data => x_msg_data
4021 );
4022
4023 -- Change the Policy context back to multiple
4024 mo_global.set_policy_context('M',null);
4025
4026
4027 --dbms_output.put_line('ret status=['||x_return_status||']');
4028 ----dbms_output.put_line('ret msg=['||x_msg_data||']');
4029 --dbms_output.put_line('ret msg count=['||x_msg_count||']');
4030
4031 -- Check return status from the above procedure call
4032 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
4033 RAISE Fnd_Api.G_EXC_ERROR;
4034 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
4035 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4036 END IF;
4037
4038 --Update the product transactions table with the cancelled status.
4039 --
4040 UPDATE CSD_PRODUCT_TRANSACTIONS
4041 SET prod_txn_status = 'CANCELLED',
4042 LAST_UPDATE_DATE = SYSDATE,
4043 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
4044 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
4045 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
4046 WHERE product_transaction_id = p_prod_txn_id;
4047
4048 IF SQL%NOTFOUND THEN
4049 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4050 END IF;
4051 -- -------------------
4052 -- Api body ends here
4053 -- -------------------
4054 -- Standard check of p_commit.
4055 IF Fnd_Api.To_Boolean(p_commit)
4056 THEN
4057 COMMIT WORK;
4058 END IF;
4059
4060 IF (Fnd_Log.level_procedure >= Fnd_Log.g_current_runtime_level)
4061 THEN
4062 Fnd_Log.STRING(Fnd_Log.level_procedure,
4063 'csd.plsql.csd_logistics_util.cancel_prodtxn.end',
4064 'cancel_prodtxn completed');
4065 END IF;
4066
4067 -- Standard call to get message count and IF count is get message info.
4068 Fnd_Msg_Pub.Count_And_Get(p_count => x_msg_count,
4069 p_data => x_msg_data);
4070 if(x_msg_count = 0) then
4071 oe_Msg_Pub.Count_And_Get(p_count => x_msg_count,
4072 p_data => x_msg_data);
4073 end if;
4074
4075 EXCEPTION
4076 WHEN Fnd_Api.g_exc_error THEN
4077 x_return_status := Fnd_Api.g_ret_sts_error;
4078 ROLLBACK TO CANCEL_PRODTXN_PVT;
4079 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
4080 p_data => x_msg_data);
4081 if(x_msg_count = 0) then
4082 oe_Msg_Pub.Count_And_Get(p_count => x_msg_count,
4083 p_data => x_msg_data);
4084 end if;
4085
4086 IF (Fnd_Log.level_error >= Fnd_Log.g_current_runtime_level)
4087 THEN
4088 Fnd_Log.STRING(Fnd_Log.level_error,
4089 'csd.plsql.csd_logistics_util.cancel_prodtxn',
4090 'EXC_ERROR[' || x_msg_data || ']');
4091 END IF;
4092 --dbms_output.put_line('exec error raised');
4093 WHEN Fnd_Api.g_exc_unexpected_error THEN
4094 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4095 ROLLBACK TO CANCEL_PRODTXN_PVT;
4096 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
4097 p_data => x_msg_data);
4098 if(x_msg_count = 0) then
4099 oe_Msg_Pub.Count_And_Get(p_count => x_msg_count,
4100 p_data => x_msg_data);
4101 end if;
4102
4103 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
4104 THEN
4105 Fnd_Log.STRING(Fnd_Log.level_exception,
4106 'csd.plsql.csd_logistics_util.cancel_prodtxn',
4107 'EXC_UNEXP_ERROR[' || x_msg_data || ']');
4108 END IF;
4109 --dbms_output.put_line('unexpected error raised');
4110 WHEN OTHERS THEN
4111 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4112 ROLLBACK TO CANCEL_PRODTXN_PVT;
4113
4114 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_unexp_error)
4115 THEN
4116 Fnd_Msg_Pub.add_exc_msg(g_pkg_name, l_api_name);
4117 END IF;
4118
4119 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count,
4120 p_data => x_msg_data);
4121 if(x_msg_count = 0) then
4122 oe_Msg_Pub.Count_And_Get(p_count => x_msg_count,
4123 p_data => x_msg_data);
4124 end if;
4125
4126 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
4127 THEN
4128 Fnd_Log.STRING(Fnd_Log.level_exception,
4129 'csd.plsql.csd_logistics_util.cancel_prodtxn',
4130 'SQL MEssage[' || SQLERRM || ']');
4131 END IF;
4132 --dbms_output.put_line('other exception raised');
4133 --dbms_output.put_line('sqlerrm'||SQLERRM);
4134 END cancel_prodtxn;
4135
4136
4137 procedure dbg_print(p_msg varchar2) is
4138 begin
4139 --dbms_output.put_line('['||p_msg||']');
4140 null;
4141 end dbg_print;
4142 procedure dbg_print_stack(p_msg_count number) is
4143 l_msg varchar2(2000);
4144
4145 begin
4146 IF p_MSG_COUNT > 1 THEN
4147 FOR i IN 1..p_MSG_COUNT LOOP
4148 l_msg := apps.FND_MSG_PUB.Get(i,apps.FND_API.G_FALSE) ;
4149 --dbms_output.put_line('Msg Data : ' || l_msg ) ;
4150 END LOOP ;
4151 ELSE
4152 l_msg := apps.FND_MSG_PUB.Get(1,apps.FND_API.G_FALSE) ;
4153 --dbms_output.put_line('Msg Data : ' || l_msg ) ;
4154 END IF ;
4155
4156 end dbg_print_stack;
4157
4158 ---------------------------------------------------------------------------------------------------------------------
4159 -- Declare Procedures --
4160 ---------------------------------------------------------------------------------------------------------------------
4161 -- Start of Comments --
4162 -- Procedure name : SET_RSV_REC
4163 -- Type : Private
4164 -- Function : To initialize the record that is to be passed into INV api
4165 -- Pre-reqs :
4166 -- Standard IN Parameters :
4167 -- Standard OUT Parameters :
4168 -- SET_CREATE_REC Parameters:
4169 -- p_rsv_serial_number :
4170 -- x_rsv_rec :
4171 -- End of Comments.
4172 ---------------------------------------------------------------------------------------------------------------------
4173 PROCEDURE SET_RSV_REC (
4174 p_rsv_serial_rec IN CSD_SERIAL_RESERVE_REC_TYPE,
4175 x_rsv_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type,
4176 x_return_status OUT NOCOPY VARCHAR2
4177 )
4178 IS
4179
4180 -- Declare local variables
4181 l_api_name CONSTANT VARCHAR2(30) := 'set_rsv_rec';
4182 l_debug_module CONSTANT VARCHAR2(100) := 'csd.plsql.'||G_PKG_NAME||'.'||l_api_name;
4183 -- Variables to check the log level according to the coding standards
4184 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
4185
4186 -- Declare cursors
4187
4188 BEGIN
4189
4190 -- Log API entry point
4191 IF (l_proc_level >= g_debug_level)THEN
4192 fnd_log.string
4193 (
4194 fnd_log.level_procedure,
4195 l_debug_module||'.start',
4196 'At the start of PLSQL procedure'
4197 );
4198 END IF;
4199
4200 x_rsv_rec.reservation_id := NULL;
4201 --x_rsv_rec.requirement_date := ?
4202 x_rsv_rec.demand_source_name := null;
4203 x_rsv_rec.primary_uom_id := NULL;
4204 x_rsv_rec.reservation_uom_code := NULL;
4205 x_rsv_rec.reservation_uom_id := NULL;
4206 x_rsv_rec.reservation_quantity := NULL;
4207 x_rsv_rec.primary_reservation_quantity := NULL;
4208 x_rsv_rec.autodetail_group_id := NULL;
4209 x_rsv_rec.external_source_code := 'CSD';
4210 x_rsv_rec.external_source_line_id := NULL;
4211 x_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
4212 x_rsv_rec.supply_source_header_id := NULL;
4213 x_rsv_rec.supply_source_line_id := NULL;
4214 x_rsv_rec.supply_source_name := NULL;
4215 x_rsv_rec.supply_source_line_detail := NULL;
4216 x_rsv_rec.subinventory_id := NULL;
4217
4218 x_rsv_rec.requirement_date := p_rsv_serial_Rec.order_schedule_date;
4219 x_RSV_REC.subinventory_code := p_rsv_serial_Rec.subinventory_code;
4220 x_Rsv_rec.locator_id := p_rsv_serial_Rec.locator_id;
4221 x_RSV_REC.serial_reservation_quantity := 1 ;
4222 x_RSV_REC.serial_number := p_rsv_serial_Rec.serial_number;
4223 x_rsv_rec.revision := p_rsv_serial_Rec.revision;
4224 x_rsv_rec.lot_number := p_rsv_serial_Rec.lot_number;
4225 x_rsv_rec.demand_source_header_id := INV_salesorder.GET_SALESORDER_FOR_OEHEADER(p_rsv_serial_rec.order_header_id);
4226 x_rsv_rec.demand_source_line_id := p_rsv_serial_rec.order_line_id;
4227 x_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_oe;
4228 x_rsv_rec.inventory_item_id := p_rsv_serial_rec.inventory_item_id;
4229 x_rsv_rec.organization_id := p_rsv_serial_rec.inv_organization_id;
4230 x_rsv_rec.reservation_uom_code := p_rsv_serial_rec.reservation_uom_code;
4231
4232 x_rsv_rec.lot_number_id := NULL;
4233 x_rsv_rec.pick_slip_number := NULL;
4234 x_rsv_rec.lpn_id := NULL;
4235 x_rsv_rec.ship_ready_flag := NULL;
4236 x_rsv_rec.demand_source_delivery := NULL;
4237
4238 x_rsv_rec.attribute_category := NULL;
4239 x_rsv_rec.attribute1 := NULL;
4240 x_rsv_rec.attribute2 := NULL;
4241 x_rsv_rec.attribute3 := NULL;
4242 x_rsv_rec.attribute4 := NULL;
4243 x_rsv_rec.attribute5 := NULL;
4244 x_rsv_rec.attribute6 := NULL;
4245 x_rsv_rec.attribute7 := NULL;
4246 x_rsv_rec.attribute8 := NULL;
4247 x_rsv_rec.attribute9 := NULL;
4248 x_rsv_rec.attribute10 := NULL;
4249 x_rsv_rec.attribute11 := NULL;
4250 x_rsv_rec.attribute12 := NULL;
4251 x_rsv_rec.attribute13 := NULL;
4252 x_rsv_rec.attribute14 := NULL;
4253 x_rsv_rec.attribute15 := NULL;
4254
4255
4256 -- Log API exit point
4257 IF (l_proc_level >= g_debug_level)THEN
4258 fnd_log.string
4259 (
4260 fnd_log.level_procedure,
4261 l_debug_module||'.end',
4262 'At the end of PLSQL procedure'
4263 );
4264 END IF;
4265 END SET_RSV_REC;
4266
4267
4268 /*------------------------------------------------------------------------*/
4269 /* procedure name: Reserve_Serial_Number */
4270 /* description : */
4271 /* Reserves a given serial numbers for the given order */
4272 /* Parameters Required: */
4273 /* p_serial_reserve_rec IN CSD_SERIAL_RESERVE_REC_TYPE */
4274 /* p_return_status OUT VARCHAR2(1) */
4275 /*------------------------------------------------------------------------*/
4276 PROCEDURE Reserve_Serial_Number
4277 ( p_serial_reserve_Rec IN CSD_SERIAL_RESERVE_REC_TYPE,
4278 x_return_status OUT NOCOPY VARCHAR2
4279 )
4280 IS
4281 -- Declare local variables
4282 l_api_name CONSTANT VARCHAR2(30) := 'reserve_serial_number';
4283 l_debug_module CONSTANT VARCHAR2(100) := 'csd.plsql.'||G_PKG_NAME||'.'||l_api_name;
4284 -- Variables to check the log level according to the coding standards
4285 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
4286 l_rsv_qry_Inp inv_reservation_global.mtl_reservation_rec_type;
4287 l_msg_Count NUMBER;
4288 l_msg_data VARCHAR2(4000);
4289 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
4290 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4291 l_serial_rsv_tbl inv_reservation_global.serial_number_tbl_type;
4292 l_orig_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4293 l_mtl_reservation_tbl_count NUMBER;
4294 lx_serial_rsv_tbl inv_reservation_global.serial_number_tbl_type;
4295 l_from_serial_rsv_tbl inv_reservation_global.serial_number_tbl_type;
4296 l_srl_rsv_match BOOLEAN;
4297 l_create_reservation BOOLEAN;
4298 l_highlevel_rsv BOOLEAN;
4299 l_serial_number MTL_SERIAL_NUMBERS.SERIAL_NUMBER%type;
4300 l_quantity_reserved NUMBER;
4301 l_reservation_id NUMBER;
4302 l_error_code VARCHAR2(2000);
4303
4304 --Define cursors
4305 --Cursor to get the serial numbers for the given reservation
4306 CURSOR Cur_Srl_Nums(p_inv_item_id NUMBER, p_Inv_Org_id NUMBER, p_reservation_Id number) is
4307 SELECT Serial_Number from MTL_SERIAL_NUMBERS
4308 WHERE INVENTORY_ITEM_ID = p_inv_item_id AND
4309 CURRENT_ORGANIZATION_ID = p_inv_org_id AND
4310 RESERVATION_ID = p_reservation_Id;
4311
4312
4313 BEGIN
4314
4315
4316 -- Log API entry point
4317 IF (l_proc_level >= g_debug_level)THEN
4318 fnd_log.string
4319 (
4320 fnd_log.level_procedure,
4321 l_debug_module||'.start',
4322 'At the start of PLSQL procedure'
4323 );
4324 END IF;
4325
4326 -- Populate the query input with the sales order id and oe_order lineid
4327 -- sales order id will be got from the api get_salesorder_for_oeheader
4328 -- private function.
4329
4330 l_rsv_qry_Inp.demand_source_header_id
4331 := INV_salesorder.GET_SALESORDER_FOR_OEHEADER(p_serial_reserve_Rec.Order_Header_Id);
4332 l_rsv_qry_Inp.demand_source_line_id := p_serial_reserve_Rec.Order_Line_Id;
4333
4334
4335 IF (l_proc_level >= g_debug_level)THEN
4336 fnd_log.string
4337 (
4338 fnd_log.level_procedure,
4339 l_debug_module,
4340 'Calling reservation api, QUERY_RESERVATION_OM_HDR_LINE, hdr id['
4341 ||to_char(p_serial_reserve_Rec.Order_Header_Id) ||']line id['
4342 ||to_char(p_serial_reserve_Rec.Order_Line_Id)||']'
4343 );
4344 END IF;
4345
4346 -- Call the query_reservation api to find the existing reservations.
4347 INV_RESERVATION_PUB.QUERY_RESERVATION_OM_HDR_LINE (
4348 P_API_VERSION_NUMBER => 1,
4349 P_INIT_MSG_LST => FND_API.G_FALSE,
4350 X_RETURN_STATUS => X_RETURN_STATUS,
4351 X_MSG_COUNT => l_MSG_COUNT,
4352 X_MSG_DATA => l_MSG_DATA,
4353 p_query_input => l_rsv_qry_inp,
4354 x_mtl_reservation_tbl => l_mtl_reservation_tbl,
4355 x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count,
4356 X_error_code => l_error_Code
4357 );
4358
4359 dbg_print_stack(l_msg_count);
4360
4361 IF(l_mtl_reservation_tbl_count > 0) THEN
4362
4363 IF (l_proc_level >= g_debug_level)THEN
4364 fnd_log.string
4365 (
4366 fnd_log.level_procedure,
4367 l_debug_module,
4368 'Reservations exist for the order header/line'
4369 );
4370 END IF;
4371
4372 l_srl_rsv_match := false;
4373 l_highlevel_rsv := false;
4374 -- Initialize the original serial number record.
4375 l_from_serial_rsv_tbl.delete;
4376
4377
4378 -- Loop through the existing reservations and then serial number
4379 -- for each reservation. If the serial number is found then
4380 -- set a flag, if the serial number does not exist on a reservation
4381 -- keep that reservation record so that it can be updated with
4382 -- the serial number later.
4383 -- l_from_serial_rsv_tbl with the existing reservation.
4384 FOR i in l_mtl_reservation_tbl.FIRST..l_mtl_reservation_tbl.LAST
4385 LOOP
4386
4387 --Fetch the serial numbers for the reservation
4388 OPEN Cur_Srl_Nums(l_rsv_rec.inventory_item_id, l_rsv_rec.organization_id,
4389 l_rsv_rec.reservation_id);
4390 FETCH Cur_Srl_Nums into l_serial_number;
4391
4392 IF(Cur_Srl_Nums%NOTFOUND) THEN
4393 -- This condiiton represents the case where there are
4394 -- reservations without any serial number. In this case
4395 -- update the reservation with serial number.
4396 l_rsv_rec := l_mtl_reservation_tbl(i);
4397 l_from_serial_rsv_tbl.DELETE;
4398 l_highlevel_rsv := true;
4399 END IF;
4400
4401 WHILE (Cur_Srl_Nums%FOUND) LOOP
4402 IF(p_serial_reserve_Rec.serial_number = l_serial_number) THEN
4403 l_srl_rsv_match := true;
4404 EXIT ;
4405 END IF;
4406 FETCH Cur_Srl_Nums into l_serial_number;
4407 END LOOP;
4408 IF(l_srl_rsv_match) then
4409 EXIT ;
4410 END IF;
4411
4412 if(NOT l_highlevel_rsv) THEN
4413 -- This condition represents the case where there are serial
4414 -- reservation but no match serial number; In this case update the
4415 -- last serial number with the current serial number.
4416 l_rsv_rec := l_mtl_reservation_tbl(i);
4417 l_from_serial_rsv_tbl(1).inventory_item_id := l_rsv_rec.inventory_item_Id;
4418 l_from_serial_rsv_tbl(1).serial_number := l_rsv_rec.serial_number;
4419 END IF;
4420
4421 END LOOP;
4422
4423 IF( NOT l_srl_rsv_match ) THEN
4424 l_orig_rsv_Rec := l_rsv_rec;
4425 --Populate the reservation record and update the reservation.
4426 l_rsv_rec.requirement_date := p_serial_reserve_Rec.order_schedule_date;
4427 l_RSV_REC.subinventory_code := p_serial_reserve_Rec.subinventory_code;
4428 l_RSV_REC.serial_reservation_quantity := 1 ;
4429 l_RSV_REC.serial_number := p_serial_reserve_Rec.serial_number;
4430 l_rsv_rec.revision := p_serial_reserve_Rec.revision;
4431 l_rsv_rec.lot_number := p_serial_reserve_Rec.lot_number;
4432
4433 -- Populate the serial number record
4434 l_serial_rsv_tbl(1).inventory_item_id := p_serial_reserve_Rec.inventory_item_id;
4435 l_serial_rsv_tbl(1).serial_number := p_serial_reserve_Rec.serial_number;
4436
4437
4438 IF (l_proc_level >= g_debug_level)THEN
4439 fnd_log.string
4440 (
4441 fnd_log.level_procedure,
4442 l_debug_module,
4443 'Calling update reservation api'
4444 );
4445 END IF;
4446
4447 INV_RESERVATION_PUB.UPDATE_RESERVATION (
4448 P_API_VERSION_NUMBER => 1,
4449 P_INIT_MSG_LST => FND_API.G_TRUE,
4450 X_RETURN_STATUS => X_RETURN_STATUS,
4451 X_MSG_COUNT => l_MSG_COUNT,
4452 X_MSG_DATA => l_MSG_DATA,
4453 p_original_rsv_rec => l_orig_rsv_rec,
4454 p_to_rsv_rec => l_rsv_rec,
4455 p_original_serial_number => l_from_serial_rsv_tbl,
4456 p_to_serial_number => l_serial_rsv_tbl
4457 );
4458 dbg_print_stack(l_msg_count);
4459 END IF;-- End if for no srl_rsv_match found
4460 ELSE
4461 l_create_reservation := true;
4462 END IF ; -- End if for rsv_count >0
4463
4464 if(l_create_reservation ) THEN
4465
4466 --l_rsv_rec.delete;
4467 set_rsv_Rec(p_serial_reserve_Rec, l_rsv_rec, x_return_status);
4468 l_serial_rsv_tbl.delete;
4469 l_serial_rsv_tbl(1).inventory_item_id := p_serial_reserve_rec.inventory_item_id;
4470 l_serial_rsv_tbl(1).serial_number := p_serial_reserve_rec.serial_number;
4471
4472 lx_serial_rsv_tbl.delete;
4473
4474 INV_RESERVATION_PUB.CREATE_RESERVATION (
4475 P_API_VERSION_NUMBER => 1,
4476 P_INIT_MSG_LST => FND_API.G_FALSE,
4477 X_RETURN_STATUS => x_RETURN_STATUS,
4478 X_MSG_COUNT => l_MSG_COUNT,
4479 X_MSG_DATA => l_MSG_DATA,
4480 P_RSV_REC => l_RSV_REC,
4481 P_SERIAL_NUMBER => l_serial_rsv_tbl,
4482 X_SERIAL_NUMBER => lx_serial_rsv_tbl,
4483 X_QUANTITY_RESERVED => l_QUANTITY_RESERVED,
4484 X_RESERVATION_ID => l_RESERVATION_ID
4485 );
4486 dbg_print_stack(l_msg_count);
4487 END IF;
4488
4489 EXCEPTION
4490 WHEN Fnd_Api.g_exc_error THEN
4491 x_return_status := Fnd_Api.g_ret_sts_error;
4492
4493 IF (Fnd_Log.level_error >= Fnd_Log.g_current_runtime_level)
4494 THEN
4495 Fnd_Log.STRING(Fnd_Log.level_error,
4496 'csd.plsql.csd_logistics_util.reserve_serial_number',
4497 'EXC_ERROR[' || l_msg_data || ']');
4498 END IF;
4499 --dbms_output.put_line('exec error raised');
4500 WHEN Fnd_Api.g_exc_unexpected_error THEN
4501 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4502 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
4503 THEN
4504 Fnd_Log.STRING(Fnd_Log.level_exception,
4505 'csd.plsql.csd_logistics_util.reserve_serial_number',
4506 'EXC_UNEXP_ERROR[' || l_msg_data || ']');
4507 END IF;
4508 --dbms_output.put_line('unexpected error raised');
4509 WHEN OTHERS THEN
4510 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4511 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level)
4512 THEN
4513 Fnd_Log.STRING(Fnd_Log.level_exception,
4514 'csd.plsql.csd_logistics_util.reserve_serial_number',
4515 'SQL MEssage[' || SQLERRM || ']');
4516 END IF;
4517
4518
4519 END Reserve_Serial_Number;
4520
4521 /*------------------------------------------------------------------------*/
4522 /* procedure name: Unreserve_Serial_Number */
4523 /* description : */
4524 /* Removes a reservation for the given order */
4525 /* Parameters Required: */
4526 /* p_serial_reserve_rec IN CSD_SERIAL_RESERVE_REC_TYPE */
4527 /* p_return_status OUT VARCHAR2(1) */
4528 /*------------------------------------------------------------------------*/
4529 PROCEDURE Unreserve_Serial_Number
4530 ( p_serial_reserve_Rec IN CSD_SERIAL_RESERVE_REC_TYPE,
4531 x_return_status OUT NOCOPY VARCHAR2
4532 ) IS
4533 -- Declare local variables
4534 l_api_name CONSTANT VARCHAR2(30) := 'unreserve_serial_number';
4535 l_debug_module CONSTANT VARCHAR2(100) := 'csd.plsql.'||G_PKG_NAME||'.'||l_api_name;
4536 -- Variables to check the log level according to the coding standards
4537 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
4538 l_rsv_qry_Inp inv_reservation_global.mtl_reservation_rec_type;
4539 l_msg_Count NUMBER;
4540 l_msg_data VARCHAR2(4000);
4541 l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
4542 l_error_code varchar2(2000);
4543 l_mtl_reservation_tbl_count NUMBER;
4544 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4545 l_serial_rsv_tbl inv_reservation_global.serial_number_tbl_type;
4546
4547 l_serial_number MTL_SERIAL_NUMBERS.SERIAL_NUMBER%type;
4548 --Define cursors
4549 --Cursor to get the serial numbers for the given reservation
4550 CURSOR Cur_Srl_Nums(p_inv_item_id NUMBER, p_Inv_Org_id NUMBER, p_reservation_Id number) is
4551 SELECT Serial_Number from MTL_SERIAL_NUMBERS
4552 WHERE INVENTORY_ITEM_ID = p_inv_item_id AND
4553 CURRENT_ORGANIZATION_ID = p_inv_org_id AND
4554 RESERVATION_ID = p_reservation_Id;
4555
4556 BEGIN
4557 -- Log API entry point
4558 IF (l_proc_level >= g_debug_level)THEN
4559 fnd_log.string
4560 (
4561 fnd_log.level_procedure,
4562 l_debug_module||'.start',
4563 'At the start of PLSQL procedure'
4564 );
4565 END IF;
4566
4567 -- Populate the query input with the sales order id and oe_order lineid
4568 -- sales order id will be got from the api get_salesorder_for_oeheader
4569 -- private function.
4570
4571 l_rsv_qry_Inp.demand_source_header_id
4572 := INV_salesorder.GET_SALESORDER_FOR_OEHEADER(p_serial_reserve_Rec.Order_Header_Id);
4573 l_rsv_qry_Inp.demand_source_line_id := p_serial_reserve_Rec.Order_Line_Id;
4574
4575
4576 IF (l_proc_level >= g_debug_level)THEN
4577 fnd_log.string
4578 (
4579 fnd_log.level_procedure,
4580 l_debug_module,
4581 'Calling reservation api, QUERY_RESERVATION_OM_HDR_LINE'
4582 );
4583 END IF;
4584
4585 -- Call the query_reservation api to find the existing reservations.
4586 INV_RESERVATION_PUB.QUERY_RESERVATION_OM_HDR_LINE (
4587 P_API_VERSION_NUMBER => 1,
4588 P_INIT_MSG_LST => FND_API.G_FALSE,
4589 X_RETURN_STATUS => X_RETURN_STATUS,
4590 X_MSG_COUNT => l_MSG_COUNT,
4591 X_MSG_DATA => l_MSG_DATA,
4592 p_query_input => l_rsv_qry_inp,
4593 x_mtl_reservation_tbl => l_mtl_reservation_tbl,
4594 x_mtl_reservation_tbl_count => l_mtl_reservation_tbl_count,
4595 X_error_code => l_error_Code
4596 );
4597 dbg_print_stack(l_msg_count);
4598 dbg_print('After query..');
4599
4600
4601 IF(l_mtl_reservation_tbl_count > 0) THEN
4602
4603
4604
4605 IF (l_proc_level >= g_debug_level)THEN
4606 fnd_log.string
4607 (
4608 fnd_log.level_procedure,
4609 l_debug_module,
4610 'Reservations exist for the order header/line'
4611 );
4612 END IF;
4613
4614
4615 -- Loop through the existing reservations and then serial number
4616 -- for each reservation. If the serial number is found
4617 -- and if the serial number matches with the existing reservation
4618 -- delete
4619 FOR i in l_mtl_reservation_tbl.FIRST..l_mtl_reservation_tbl.LAST
4620 LOOP
4621
4622 l_rsv_rec := l_mtl_reservation_tbl(i);
4623 --Fetch the serial numbers for the reservation
4624 OPEN Cur_Srl_Nums(l_rsv_rec.inventory_item_id, l_rsv_rec.organization_id,
4625 l_rsv_rec.reservation_id);
4626 FETCH Cur_Srl_Nums into l_serial_number;
4627
4628 WHILE (Cur_Srl_Nums%FOUND) LOOP
4629 IF(p_serial_reserve_Rec.serial_number = l_serial_number) THEN
4630 l_serial_rsv_tbl.delete;
4631 l_serial_rsv_tbl(1).inventory_item_id := p_serial_reserve_rec.inventory_item_id;
4632 l_serial_rsv_tbl(1).serial_number := p_serial_reserve_rec.serial_number;
4633 -- Call the delete_reservation api to remove the existing reservations.
4634 INV_RESERVATION_PUB.DELETE_RESERVATION (
4635 P_API_VERSION_NUMBER => 1,
4636 P_INIT_MSG_LST => FND_API.G_FALSE,
4637 X_RETURN_STATUS => X_RETURN_STATUS,
4638 X_MSG_COUNT => l_MSG_COUNT,
4639 X_MSG_DATA => l_MSG_DATA,
4640 p_rsv_rec => l_rsv_rec,
4641 p_serial_number => l_serial_rsv_tbl
4642 );
4643 dbg_print_stack(l_msg_count);
4644 dbg_print('After delete..');
4645 EXIT;
4646 END IF;
4647 FETCH Cur_Srl_Nums into l_Serial_number;
4648 END LOOP;
4649 END LOOP;
4650
4651 END If;
4652
4653
4654 END Unreserve_Serial_Number;
4655
4656
4657 END Csd_Logistics_Util;
[Home] [Help]