[Home] [Help]
PACKAGE BODY: APPS.CSE_OUT_OF_SERVICE_PKG
Source
1 package body CSE_OUT_OF_SERVICE_PKG AS
2 -- $Header: CSEOUTSB.pls 120.1 2010/11/22 11:00:48 dnema ship $
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('CSE_DEBUG_OPTION'),'N');
5
6
7 PROCEDURE Decode_Message(
8 P_Msg_Header IN XNP_MESSAGE.Msg_Header_Rec_Type,
9 P_Msg_Text IN VARCHAR2,
10 X_Out_Of_Service_Attr_Rec OUT NOCOPY Out_Of_Service_Attr_Rec_Type,
11 X_Return_Status OUT NOCOPY VARCHAR2,
12 X_Error_Message OUT NOCOPY VARCHAR2) IS
13
14 l_Api_Name CONSTANT VARCHAR2(30) := 'CSE_OUT_OF_SERVICE_PKG';
15 l_Item_Id NUMBER;
16 l_Revision VARCHAR2(3);
17 --l_Lot_Number VARCHAR2(30);
18 l_Lot_Number VARCHAR2(80); --bug 10322621
19 l_Serial_Number VARCHAR2(30);
20 l_Quantity NUMBER;
21 l_Netwk_Locn_Id NUMBER;
22 l_Party_Site_Id NUMBER;
23 l_Work_Order_Number VARCHAR2(30);
24 --l_Project_Id NUMBER;
25 --l_Task_Id NUMBER;
26 l_Transaction_Date DATE;
27 l_Transaction_Date_Str VARCHAR2(30);
28 l_Transacted_By NUMBER;
29
30 BEGIN
31
32 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
33 X_Error_Message := Null;
34
35 Xnp_Xml_Utils.Decode(P_Msg_Text,'ITEM_ID',l_Item_Id);
36 Xnp_Xml_Utils.Decode(P_Msg_Text,'REVISION',l_Revision);
37 Xnp_Xml_Utils.Decode(P_Msg_Text,'LOT_NUMBER',l_Lot_Number);
38 Xnp_Xml_Utils.Decode(P_Msg_Text,'SERIAL_NUMBER',l_Serial_Number);
39 Xnp_Xml_Utils.Decode(P_Msg_Text,'QUANTITY',l_Quantity);
40 Xnp_Xml_Utils.Decode(P_Msg_Text,'NETWORK_LOC_ID',l_Netwk_Locn_Id);
41 Xnp_Xml_Utils.Decode(P_Msg_Text,'PARTY_SITE_ID',l_Party_Site_Id);
42 Xnp_Xml_Utils.Decode(P_Msg_Text,'WORK_ORDER_NUMBER',l_Work_Order_Number);
43 -- Xnp_Xml_Utils.Decode(P_Msg_Text,'PROJECT_ID',l_Project_Id);
44 -- Xnp_Xml_Utils.Decode(P_Msg_Text,'TASK_ID',l_Task_Id);
45 Xnp_Xml_Utils.Decode(P_Msg_Text,'TRANSACTION_DATE',l_Transaction_Date_Str);
46 Xnp_Xml_Utils.Decode(P_Msg_Text,'TRANSACTED_BY',l_Transacted_By);
47
48
49 X_Out_Of_Service_Attr_Rec.Item_Id := l_Item_Id;
50 X_Out_Of_Service_Attr_Rec.Revision := l_Revision;
51 X_Out_Of_Service_Attr_Rec.Lot_Number := l_Lot_Number;
52 X_Out_Of_Service_Attr_Rec.Serial_Number := l_Serial_Number;
53 X_Out_Of_Service_Attr_Rec.Quantity := l_Quantity;
54 X_Out_Of_Service_Attr_Rec.Network_Location_Id := l_Netwk_Locn_Id;
55 X_Out_Of_Service_Attr_Rec.Party_Site_Id := l_Party_Site_Id;
56 X_Out_Of_Service_Attr_Rec.Work_Order_Number := l_Work_Order_Number;
57 --X_Out_Of_Service_Attr_Rec.Project_Id := l_Project_Id;
58 -- X_Out_Of_Service_Attr_Rec.Task_Id := l_Task_Id;
59 X_Out_Of_Service_Attr_Rec.Transaction_Date := TO_DATE(l_Transaction_Date_Str, 'YYYY/MM/DD HH24:MI:SS');
60 X_Out_Of_Service_Attr_Rec.Transacted_By := l_Transacted_By;
61 X_Out_Of_Service_Attr_Rec.Message_id := P_Msg_Header.Message_Id;
62
63 EXCEPTION
64
65 WHEN OTHERS THEN
66 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
67 fnd_message.set_token('ERR_MSG',l_Api_Name||'='|| SQLERRM);
68 x_error_message := fnd_message.get;
69 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
70 END Decode_Message;
71
72 PROCEDURE Update_Ib_Repository(
73 P_Out_Of_Service_Attr_Rec IN Out_Of_Service_Attr_Rec_Type,
74 X_Return_Status OUT NOCOPY VARCHAR2,
75 X_Error_Message OUT NOCOPY VARCHAR2) IS
76
77 l_Api_Name CONSTANT VARCHAR2(30) :='CSE_OUT_OF_SERVICE_PKG';
78 l_api_version NUMBER DEFAULT 1.0;
79 l_Commit VARCHAR2(1);
80 l_Init_Msg_List VARCHAR2(1);
81 l_Resolve_Id_Columns VARCHAR2(1);
82 l_Create_Dest_Inst_Flag VARCHAR2(1);
83 l_Validation_Level NUMBER;
84 l_Active_Instance_Only VARCHAR2(1);
85 l_Resolve_Id_Column VARCHAR2(1);
86 l_Instance_Header_Tbl_Out csi_datastructures_pub.instance_header_tbl;
87 l_Instance_Rec csi_datastructures_pub.Instance_Rec;
88 l_Dest_Instance_Rec csi_datastructures_pub.Instance_Rec;
89 l_Instance_Query_Rec csi_datastructures_pub.Instance_Query_Rec;
90 l_txn_rec csi_datastructures_pub.transaction_rec;
91 l_upd_txn_rec csi_datastructures_pub.transaction_rec;
92 l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
93 l_Party_Query_Rec csi_datastructures_pub.party_query_rec;
94 l_party_tbl csi_datastructures_pub.party_tbl;
95 l_account_tbl csi_datastructures_pub.party_account_tbl;
96 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
97 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
98 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
99 l_Instance_Id_Lst csi_datastructures_pub.Id_Tbl;
100 l_msg_index NUMBER;
101 i PLS_INTEGER;
102 l_S_object_version_number NUMBER :=1;
103 l_D_object_version_number NUMBER :=1;
104 l_object_version_number NUMBER :=1;
105 l_s_mfg_serial_number_flag VARCHAR2(1);
106 l_Msg_Count NUMBER;
107 l_Msg_Data VARCHAR2(2000);
108 l_Account_Query_Rec csi_datastructures_pub.party_account_query_rec;
109 l_s_last_vld_organization_id NUMBER;
110 l_Transaction_Id NUMBER;
111 l_transaction_type_id NUMBER;
112 l_Return_Status VARCHAR2(1);
113 l_Error_Message VARCHAR2(2000);
114 l_Hz_Location_Id NUMBER;
115 l_Location_Type_Code VARCHAR2(30);
116 l_s_install_location_type_code VARCHAR2(30);
117 l_s_install_location_id NUMBER;
118 l_Source_Quantity NUMBER;
119 l_Source_Instance_Id NUMBER;
120 l_Dest_Transaction_Id NUMBER;
121 l_Inv_Organization_Id NUMBER;
122 l_sysdate DATE:=sysdate;
123 l_S_Inv_Master_Organization_Id NUMBER;
124 l_Source_Unit_Of_Measure VARCHAR2(3);
125 l_App_Short_Name CONSTANT VARCHAR2(10):='CSE';
126 l_Active_Rec_Count Number:=0;
127 l_Zero_Exp_Rec_Count Number:=0;
128 l_Txn_Status VARCHAR2(20);
129 l_Source_Transaction_Id NUMBER;
130 l_Destination_Transaction_Id NUMBER;
131 l_file VARCHAR2(500);
132 l_s_status_id NUMBER;
133 t_transaction_id NUMBER;
134 CURSOR inst_status_cur IS
135 SELECT instance_status_id
136 FROM csi_instance_statuses
137 WHERE upper(name) ='EXPIRED';
138 Update_Ib_Exp EXCEPTION;
139
140 BEGIN
141
142 l_Commit := fnd_api.g_false;
143 l_Init_Msg_List := fnd_api.g_true;
144 l_Resolve_Id_Columns := fnd_api.g_false;
145 l_Create_Dest_Inst_Flag := fnd_api.g_false;
146 l_Validation_Level := fnd_api.g_valid_level_full;
147 l_Active_Instance_Only := fnd_api.g_false;
148 l_Resolve_Id_Column := fnd_api.g_false;
149 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
150 X_Error_Message := Null;
151 t_transaction_id := -1;
152 IF (l_debug = 'Y') THEN
153 CSE_DEBUG_PUB.ADD('***** OUT NOCOPY OF SERVICE TRANSACTION ******');
154 CSE_DEBUG_PUB.ADD('-----------------------------------------------');
155 END IF;
156
157 -- If the option is set turn on the debug log.
158
159 IF (l_debug = 'Y') THEN
160 cse_debug_pub.g_dir := nvl(FND_PROFILE.VALUE('CSE_DEBUG_LOG_DIRECTORY'), '/tmp');
161 cse_debug_pub.g_file := NULL;
162 l_file := cse_debug_pub.set_debug_file('cse' || to_char(l_sysdate, 'DD-MON-YYYY') || '.log');
163 cse_debug_pub.debug_on;
164 END IF;
165
166 -- l_Network_Location_Code := P_Out_Of_Service_Attr_Rec.Network_Location_Code;
167 If P_Out_Of_Service_Attr_Rec.Network_Location_id is not null then
168 l_hz_location_id :=P_Out_Of_Service_Attr_Rec.Network_Location_id ;
169 l_location_type_code:='HZ_LOCATIONS';
170 elsif P_Out_Of_Service_Attr_Rec.party_site_id is not null then
171 l_hz_location_id :=P_Out_Of_Service_Attr_Rec.party_site_id ;
172 l_location_type_code:='HZ_PARTY_SITES';
173 end if;
174
175 l_Transaction_Type_Id := CSE_UTIL_PKG.Get_Txn_Type_ID
176 ('OUT_OF_SERVICE',
177 l_App_Short_Name);
178
179 --Step 2, Query for the existence of the source record
180
181 IF (l_debug = 'Y') THEN
182 CSE_DEBUG_PUB.ADD('Initailizing Query Record for the source item instance - step2');
183 END IF;
184
185 l_Instance_Query_Rec := CSE_UTIL_PKG.Init_Instance_Query_Rec;
186 l_Instance_Query_Rec.Inventory_Item_Id := P_Out_Of_Service_Attr_Rec.Item_Id;
187 l_Instance_Query_Rec.Inventory_Revision:= P_Out_Of_Service_Attr_Rec.Revision;
188 l_Instance_Query_Rec.Lot_Number := P_Out_Of_Service_Attr_Rec.Lot_Number;
189 l_Instance_Query_Rec.Serial_Number:= P_Out_Of_Service_Attr_Rec.Serial_Number;
190 l_Instance_Query_Rec.Location_Id := l_Hz_Location_Id;
191 l_Instance_Query_Rec.Location_Type_Code:= l_Location_Type_Code;
192 l_Instance_Query_Rec.Instance_Usage_Code:=CSE_DATASTRUCTURES_PUB.G_IN_SERVICE;
193
194 IF (l_debug = 'Y') THEN
195 CSE_DEBUG_PUB.ADD(l_Api_Name||' Before CSI_Item_Instance_Pub.Get_Item_Instance');
196 END IF;
197
198 CSI_Item_Instance_Pub.Get_Item_Instances(
199 p_api_version => l_api_version,
200 p_commit => l_Commit,
201 p_init_msg_list => l_Init_Msg_List,
202 p_validation_level => l_Validation_Level,
203 p_instance_Query_rec => l_Instance_Query_Rec,
204 p_party_query_rec => l_Party_Query_Rec,
205 p_account_query_rec => l_account_query_rec,
206 p_active_instance_only => l_Active_Instance_Only,
207 p_transaction_id => l_transaction_id,
208 p_resolve_id_columns => l_resolve_id_columns,
209 x_Instance_Header_Tbl => l_Instance_Header_Tbl_Out,
210 x_return_status => l_Return_Status,
211 x_msg_count => l_Msg_Count,
212 x_msg_data => l_Msg_Data );
213
214
215
216 -- get the error message from the stack if there is any error
217
218 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
219 l_msg_index := 1;
220 l_Error_Message:=l_msg_data;
221 WHILE l_msg_count > 0
222 LOOP
223 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
224 l_msg_index := l_msg_index + 1;
225 l_Msg_Count := l_Msg_Count - 1;
226 END LOOP;
227 RAISE Update_Ib_Exp;
228 END IF;
229
230 --raise exception if the source record is not found
231
232 IF(l_Instance_Header_Tbl_Out.COUNT=0) THEN
233
234 fnd_message.set_name('CSE','CSE_SRC_RECORD_NOTFOUND');
235 fnd_message.set_token('ITEM',P_Out_Of_Service_Attr_Rec.Item_Id);
236 l_error_message := fnd_message.get;
237 l_Return_Status := FND_API.G_RET_STS_ERROR;
238 RAISE Update_Ib_Exp;
239 END IF;
240
241 -- check if there exist multiple source instances
242
243 IF(l_Instance_Header_Tbl_Out.COUNT>1) THEN
244
245 fnd_message.set_name('CSE','CSE_SRC_MULTIPLE_ITM_INSTANCES');
246 fnd_message.set_token('ITEM',P_Out_Of_Service_Attr_Rec.Item_Id);
247 l_error_message := fnd_message.get;
248 l_Return_Status := FND_API.G_RET_STS_ERROR;
249 RAISE Update_Ib_Exp;
250 END IF;
251
252 -- continue if there exist only one source item instance
253
254 IF(l_Instance_Header_Tbl_Out.COUNT=1) THEN
255 i :=l_Instance_Header_Tbl_Out.FIRST;
256 l_Source_Quantity :=l_Instance_Header_Tbl_Out(i).Quantity;
257 l_Source_Instance_id :=l_Instance_Header_Tbl_Out(i).Instance_Id;
258 l_S_Inv_Master_Organization_Id:=l_Instance_Header_Tbl_Out(i).Inv_Master_Organization_Id;
259 l_source_unit_of_measure :=l_Instance_Header_Tbl_Out(i).Unit_Of_Measure;
260 l_Inv_Organization_Id :=l_Instance_Header_Tbl_Out(i).Inv_Organization_Id;
261 l_S_object_version_number :=l_Instance_Header_Tbl_Out(i).object_version_number;
262 l_s_mfg_serial_number_flag :=l_Instance_Header_Tbl_Out(i).mfg_serial_number_flag;
263 l_s_install_location_type_code:=l_Instance_Header_Tbl_Out(i).install_location_type_code;
264 l_s_install_location_id:=l_Instance_Header_Tbl_Out(i).install_location_id;
265 l_s_status_id :=l_Instance_Header_Tbl_Out(i).instance_status_id;
266 l_s_last_vld_organization_id :=l_Instance_Header_Tbl_out(i).vld_organization_id;
267
268 -- Step 3,Check if the txn_qty is more than source item instance qty
269
270 IF(P_Out_Of_Service_Attr_Rec.Quantity >
271 l_Instance_Header_Tbl_Out(i).Quantity) THEN
272 fnd_message.set_name('CSE','CSE_SRC_ITEM_QUANTITY');
273 fnd_message.set_token('ITEM',P_Out_Of_Service_Attr_Rec.Item_Id);
274 l_error_message := fnd_message.get;
275 l_Return_Status := FND_API.G_RET_STS_ERROR;
276 RAISE Update_Ib_Exp;
277 END IF;
278
279
280 --Step 4,If the Item is serialized,
281
282 IF(P_Out_Of_Service_Attr_Rec.Serial_Number IS NOT NULL) THEN
283
284 IF (l_debug = 'Y') THEN
285 CSE_DEBUG_PUB.ADD('Initailizing instance Record for update of serialized item inst- step4');
286 END IF;
287
288 l_Instance_Rec := CSE_UTIL_PKG.Init_Instance_Update_Rec;
289 l_Instance_Rec.Instance_Id :=l_Source_Instance_Id;
290 l_Instance_Rec.Instance_Usage_code:=CSE_DATASTRUCTURES_PUB.G_OUT_OF_SERVICE;
291 l_Instance_Rec.object_version_number:=l_s_object_version_number;
292 l_Txn_Rec := CSE_UTIL_PKG.Init_Txn_Rec;
293 l_Txn_Rec.Transaction_Date := l_sysdate;
294 l_Txn_Rec.Source_Transaction_Date:= P_Out_Of_Service_Attr_Rec.Transaction_Date;
295 l_Txn_Rec.Transaction_Type_Id := l_Transaction_Type_Id;
296 l_Txn_Rec.Source_Group_Ref:= P_Out_Of_Service_Attr_Rec.Work_Order_Number;
297 l_Txn_Rec.Transaction_Quantity := P_Out_Of_Service_Attr_Rec.Quantity;
298 l_Txn_Rec.Transacted_By := P_Out_Of_Service_Attr_Rec.Transacted_By;
299 l_Txn_Rec.Transaction_Status_Code:= CSE_DATASTRUCTURES_PUB.G_PENDING;
300 l_Txn_Rec.Message_Id := P_Out_Of_Service_Attr_Rec.Message_Id;
301
302 IF NOT t_transaction_id = -1 THEN
303 l_txn_rec.transaction_id := t_transaction_id;
304 END IF;
305 IF (l_debug = 'Y') THEN
306 CSE_DEBUG_PUB.ADD('Before update of serialized item instanace');
307 END IF;
308
309
310 CSI_Item_Instance_Pub.Update_Item_Instance(
311 p_api_version => l_api_version,
312 p_commit => l_Commit,
313 p_validation_level => l_Validation_Level,
314 p_init_msg_list => l_Init_Msg_List,
315 p_instance_rec => l_Instance_Rec,
316 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
317 p_party_tbl => l_party_tbl,
318 p_account_tbl => l_account_tbl,
319 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
320 p_org_assignments_tbl => l_org_assignments_tbl,
321 p_txn_rec => l_Txn_Rec,
322 p_asset_assignment_tbl => l_asset_assignment_tbl,
323 x_instance_id_lst => l_instance_id_lst,
324 x_return_status => l_Return_Status,
325 x_msg_count => l_Msg_Count,
326 x_msg_data => l_Msg_Data );
327
328
329 -- get the error message from the stack if there is any error
330
331 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
332 l_msg_index := 1;
333 l_Error_Message:=l_msg_data;
334 WHILE l_msg_count > 0 LOOP
335 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
336 l_msg_index := l_msg_index + 1;
337 l_Msg_Count := l_Msg_Count - 1;
338 END LOOP;
339 RAISE Update_Ib_Exp;
340 ELSIF l_Return_Status = FND_API.G_Ret_Sts_Success THEN
341 IF t_transaction_id =-1 THEN
342 t_transaction_id := l_txn_rec.transaction_id;
343 END IF;
344 END IF;
345 END IF;
346
347 -- Step 5 ,if the item is non serialized
348
349 IF(P_Out_Of_Service_Attr_Rec.Serial_Number IS NULL) THEN
350
351 --Step 5.1 ,Check for the existence of the destination record
352
353 IF (l_debug = 'Y') THEN
354 CSE_DEBUG_PUB.ADD('Initailizing Query Record for the destination instance - step5.1');
355 END IF;
356
357 l_Instance_Query_Rec := CSE_UTIL_PKG.Init_Instance_Query_Rec;
358 l_Instance_Query_Rec.Inventory_Item_Id := P_Out_Of_Service_Attr_Rec.Item_Id;
359 l_Instance_Query_Rec.Inventory_Revision:= P_Out_Of_Service_Attr_Rec.Revision;
360 l_Instance_Query_Rec.Lot_Number := P_Out_Of_Service_Attr_Rec.Lot_Number;
361 l_Instance_Query_Rec.Serial_Number:= P_Out_Of_Service_Attr_Rec.Serial_Number;
362 l_Instance_Query_Rec.Location_Id := l_Hz_Location_Id;
363 l_Instance_Query_Rec.Location_Type_Code:= l_Location_Type_Code;
364 l_Instance_Query_Rec.Instance_Usage_Code:=CSE_DATASTRUCTURES_PUB.G_OUT_OF_SERVICE;
365 l_Active_Instance_Only:= FND_API.G_FALSE;
366
367 IF (l_debug = 'Y') THEN
368 CSE_DEBUG_PUB.ADD('Before querying for the existence of destination record');
369 END IF;
370
371 CSI_Item_Instance_Pub.Get_Item_Instances(
372 p_api_version => l_api_version,
373 p_commit => l_Commit,
374 p_init_msg_list => l_Init_Msg_List,
375 p_validation_level => l_Validation_Level,
376 p_instance_Query_rec => l_Instance_Query_Rec,
377 p_party_query_rec => l_Party_Query_Rec,
378 p_account_query_rec => l_account_query_rec,
379 p_active_instance_only => l_Active_Instance_Only,
380 x_Instance_Header_Tbl => l_Instance_Header_Tbl_Out,
381 p_transaction_id => l_transaction_id,
382 p_resolve_id_columns => l_resolve_id_columns,
383 x_return_status => l_Return_Status,
384 x_msg_count => l_Msg_Count,
385 x_msg_data => l_Msg_Data );
386
387
388
389 -- get the error message from the stack if there is any error
390
391 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
392 l_msg_index := 1;
393 l_Error_Message:=l_Msg_Data;
394 WHILE l_msg_count > 0
395 LOOP
396 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
397 l_msg_index := l_msg_index + 1;
398 l_Msg_Count := l_Msg_Count - 1;
399 END LOOP;
400 RAISE Update_Ib_Exp;
401 END IF;
402 -- update source instance quantity
403
404
405 IF (l_debug = 'Y') THEN
406 CSE_DEBUG_PUB.ADD('Initailizing instance Record for update of source qty - step5.3');
407 END IF;
408
409 l_Instance_Rec := CSE_UTIL_PKG.Init_Instance_Update_Rec;
410 l_Instance_Rec.Instance_Id:= l_Source_instance_id;
411 l_Instance_Rec.Quantity := (l_Source_Quantity -
412 P_Out_Of_Service_Attr_Rec.Quantity);
413 l_Instance_Rec.Active_End_Date :=Null;
414 l_Instance_Rec.Object_version_number :=l_S_object_version_number;
415 l_Party_tbl.delete;
416 l_Txn_Rec := CSE_UTIL_PKG.Init_Txn_Rec;
417 l_Txn_Rec.Transaction_Date := l_sysdate;
418 l_Txn_Rec.Transaction_Type_Id := l_Transaction_Type_Id;
419 l_Txn_Rec.Source_Transaction_Date:= P_Out_Of_Service_Attr_Rec.Transaction_Date;
420 l_Txn_Rec.Source_Group_Ref:= P_Out_Of_Service_Attr_Rec.Work_Order_Number;
421 l_Txn_Rec.Transaction_Quantity:= P_Out_Of_Service_Attr_Rec.Quantity;
422 l_Txn_Rec.Transacted_By := P_Out_Of_Service_Attr_Rec.Transacted_By;
423 l_Txn_Rec.Transaction_Status_Code := CSE_DATASTRUCTURES_PUB.G_PENDING;
424 l_Txn_Rec.Transaction_Action_Code := Null;
425 l_Txn_Rec.Message_Id := P_Out_Of_Service_Attr_Rec.Message_Id;
426
427 IF NOT t_transaction_id = -1 THEN
428 l_txn_rec.transaction_id := t_transaction_id;
429 END IF;
430 IF (l_debug = 'Y') THEN
431 CSE_DEBUG_PUB.ADD('Before update of source instanace qty - step 5.3');
432 END IF;
433
434 CSI_Item_Instance_Pub.Update_Item_Instance(
435 p_api_version => l_api_version,
436 p_commit => l_Commit,
437 p_validation_level => l_Validation_Level,
438 p_init_msg_list => l_Init_Msg_List,
439 p_instance_rec => l_Instance_Rec,
440 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
441 p_party_tbl => l_party_tbl,
442 p_account_tbl => l_account_tbl,
443 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
444 p_org_assignments_tbl => l_org_assignments_tbl,
445 p_txn_rec => l_Txn_Rec,
446 p_asset_assignment_tbl => l_asset_assignment_tbl,
447 x_instance_id_lst => l_instance_id_lst,
448 x_return_status => l_Return_Status,
449 x_msg_count => l_Msg_Count,
450 x_msg_data => l_Msg_Data );
451
452
453
454 -- get the error message from the stack if there is any error
455
456 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
457 l_msg_index := 1;
458 l_Error_Message:=l_msg_data;
459 WHILE l_msg_count > 0 LOOP
460 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
461 l_msg_index := l_msg_index + 1;
462 l_Msg_Count := l_Msg_Count - 1;
463 END LOOP;
464 RAISE Update_Ib_Exp;
465 ELSIF l_Return_Status = FND_API.G_Ret_Sts_Success THEN
466 IF t_transaction_id =-1 THEN
467 t_transaction_id := l_txn_rec.transaction_id;
468 END IF;
469 END IF;
470
471 IF(l_Instance_Header_Tbl_Out.COUNT>0) THEN
472
473 cse_util_pkg.Get_Destination_Instance(
474 P_Dest_Instance_tbl => l_Instance_Header_Tbl_Out,
475 X_Instance_Rec => l_Dest_Instance_Rec,
476 X_Return_Status => l_Return_Status,
477 X_Error_Message => l_Error_Message);
478
479 IF NOT(l_Return_Status = FND_API.G_RET_STS_SUCCESS) THEN
480 RAISE Update_Ib_Exp;
481 END IF;
482
483
484
485 -- If destination item instance is found
486
487 IF l_Dest_Instance_Rec.instance_id IS NOT NULL THEN
488 l_D_object_version_number:=l_Dest_Instance_Rec.object_version_number;
489
490 -- CSE_DEBUG_PUB.ADD('Initailizing instance Record for update Of dest inst - step5.1');
491
492 l_Instance_Rec := CSE_UTIL_PKG.Init_Instance_Update_Rec;
493 l_Instance_Rec.Instance_Id := l_Dest_Instance_Rec.Instance_Id;
494 l_Instance_Rec.Quantity := (P_Out_Of_Service_Attr_Rec.Quantity +
495 l_Dest_Instance_Rec.Quantity );
496 l_Instance_Rec.Active_End_Date := Null;
497 l_Instance_Rec.Object_version_Number:=l_D_Object_Version_Number;
498
499 -- check if destination instance is expired ,if expired then set the status same as source.
500
501 FOR inst_status_cur_rec in inst_status_cur
502 LOOP
503 IF (inst_status_cur_rec.instance_status_id=l_Dest_Instance_Rec.Instance_status_id)
504 THEN
505 l_Instance_Rec.instance_status_id :=l_s_status_id;
506 END IF;
507 END LOOP;
508 l_Txn_Rec := CSE_UTIL_PKG.Init_Txn_Rec;
509 l_Txn_Rec.Transaction_Date := l_sysdate;
510 l_Txn_Rec.Source_Transaction_Date:= P_Out_Of_Service_Attr_Rec.Transaction_Date;
511 l_Txn_Rec.Transaction_Type_Id := l_transaction_type_id;
512 l_Txn_Rec.Source_Group_Ref := P_Out_Of_Service_Attr_Rec.Work_Order_Number;
513 l_Txn_Rec.Transaction_Quantity := P_Out_Of_Service_Attr_Rec.Quantity;
514 l_Txn_Rec.Transacted_By := P_Out_Of_Service_Attr_Rec.Transacted_By;
515 l_Txn_Rec.Transaction_Status_Code:= CSE_DATASTRUCTURES_PUB.G_PENDING;
516 l_Txn_Rec.Message_Id := P_Out_Of_Service_Attr_Rec.Message_Id;
517
518 IF NOT t_transaction_id = -1 THEN
519 l_txn_rec.transaction_id := t_transaction_id;
520 END IF;
521
522 IF (l_debug = 'Y') THEN
523 CSE_DEBUG_PUB.ADD('Before update of destination instanace');
524 END IF;
525
526 CSI_Item_Instance_Pub.Update_Item_Instance(
527 p_api_version => l_api_version,
528 p_commit => l_Commit,
529 p_validation_level => l_Validation_Level,
530 p_init_msg_list => l_Init_Msg_List,
531 p_instance_rec => l_Instance_Rec,
532 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
533 p_party_tbl => l_party_tbl,
534 p_account_tbl => l_account_tbl,
535 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
536 p_org_assignments_tbl => l_org_assignments_tbl,
537 p_txn_rec => l_Txn_Rec,
538 p_asset_assignment_tbl => l_asset_assignment_tbl,
539 x_instance_id_lst => l_instance_id_lst,
540 x_return_status => l_Return_Status,
541 x_msg_count => l_Msg_Count,
542 x_msg_data => l_Msg_Data );
543
544 -- get the error message from the stack if there is any error
545
546 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
547 l_msg_index := 1;
548 l_Error_Message:=l_msg_data;
549 WHILE l_msg_count > 0 LOOP
550 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
551 l_msg_index := l_msg_index + 1;
552 l_Msg_Count := l_Msg_Count - 1;
553 END LOOP;
554 RAISE Update_Ib_Exp;
555 ELSIF l_Return_Status = FND_API.G_Ret_Sts_Success THEN
556 IF t_transaction_id =-1 THEN
557 t_transaction_id := l_txn_rec.transaction_id;
558 END IF;
559 END IF;
560 ELSIF l_Dest_Instance_Rec.instance_id IS NULL THEN
561 l_Create_Dest_Inst_Flag :=FND_API.G_TRUE;
562 END IF;
563
564 ELSIF (l_Instance_Header_Tbl_Out.COUNT=0) THEN
565 l_Create_Dest_Inst_Flag :=FND_API.G_TRUE;
566 END IF;
567
568 --create destination item instance ,if the dest item instance is not found
569
570 IF (l_Create_Dest_Inst_Flag = FND_API.G_TRUE ) THEN
571
572 IF (l_debug = 'Y') THEN
573 CSE_DEBUG_PUB.ADD('Initailizing instance Record for the creation Of dest inst - step5.1');
574 END IF;
575
576 l_Instance_Rec := CSE_UTIL_PKG.Init_Instance_Create_Rec;
577 l_Instance_Rec.Inventory_Item_id := P_Out_Of_Service_Attr_Rec.Item_id;
578 l_Instance_Rec.Inventory_Revision := P_Out_Of_Service_Attr_Rec.Revision;
579 l_Instance_Rec.Inv_Master_Organization_Id := l_s_Inv_Master_Organization_Id;
580 l_Instance_Rec.Vld_organization_id := l_s_last_vld_organization_id ;
581 l_Instance_Rec.Serial_Number := P_Out_Of_Service_Attr_Rec.Serial_Number;
582 l_Instance_Rec.Mfg_Serial_Number_Flag:= l_s_mfg_serial_number_flag ;
583 l_Instance_Rec.Customer_View_Flag := 'N';
584 l_Instance_Rec.Merchant_View_Flag := 'Y';
585 l_Instance_Rec.Lot_Number := P_Out_Of_Service_Attr_Rec.Lot_Number;
586 l_Instance_Rec.Quantity := P_Out_Of_Service_Attr_Rec.Quantity;
587 l_Instance_Rec.Unit_Of_measure := l_Source_Unit_Of_Measure;
588 l_Instance_Rec.Instance_Usage_Code := CSE_DATASTRUCTURES_PUB.G_OUT_OF_SERVICE;
589 l_Instance_Rec.instance_status_id := l_s_status_id;
590 l_Instance_Rec.Location_Type_Code := l_Location_Type_Code;
591 l_Instance_Rec.Location_Id := l_Hz_Location_Id;
592 l_Instance_Rec.Install_Location_Type_Code := l_s_Install_Location_Type_Code;
593 l_Instance_Rec.Install_Location_Id := l_s_Install_Location_Id;
594 l_Instance_Rec.object_version_number := l_object_version_number;
595 l_ext_attrib_values_tbl := CSE_UTIL_PKG.Init_ext_attrib_values_tbl;
596 l_party_tbl := CSE_UTIL_PKG.Init_party_tbl;
597 l_account_tbl := CSE_UTIL_PKG.Init_account_tbl;
598 l_pricing_attrib_tbl := CSE_UTIL_PKG.Init_pricing_attribs_tbl;
599 l_org_assignments_tbl := CSE_UTIL_PKG.Init_org_assignments_tbl;
600 l_asset_assignment_tbl:= CSE_UTIL_PKG.Init_asset_assignment_tbl;
601 l_Txn_Rec := CSE_UTIL_PKG.Init_Txn_Rec;
602 l_Txn_Rec.Transaction_Date :=l_sysdate;
603 l_Txn_Rec.Source_Transaction_Date :=P_Out_Of_Service_Attr_Rec.Transaction_Date;
604 l_Txn_Rec.Transaction_Type_Id :=l_transaction_type_id;
605 l_Txn_Rec.Source_Group_Ref := P_Out_Of_Service_Attr_Rec.Work_Order_Number;
606 l_Txn_Rec.source_dist_ref_id2 :=l_upd_txn_rec.transaction_id;
607 l_Txn_Rec.Transaction_Quantity := P_Out_Of_Service_Attr_Rec.Quantity;
608 l_Txn_Rec.Transacted_By := P_Out_Of_Service_Attr_Rec.Transacted_By;
609 l_Txn_Rec.Transaction_Status_Code := CSE_DATASTRUCTURES_PUB.G_PENDING;
610 l_Txn_Rec.Message_Id := P_Out_Of_Service_Attr_Rec.Message_Id;
611
612 IF NOT t_transaction_id = -1 THEN
613 l_txn_rec.transaction_id := t_transaction_id;
614 END IF;
615 IF (l_debug = 'Y') THEN
616 CSE_DEBUG_PUB.ADD('Before creation of destination instanace');
617 END IF;
618
619
620 CSI_Item_Instance_Pub.Create_Item_Instance(
621 p_api_version =>l_api_version,
622 p_commit => l_Commit,
623 p_validation_level => l_Validation_Level,
624 p_init_msg_list =>l_Init_Msg_List,
625 p_instance_rec => l_Instance_Rec,
626 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
627 p_party_tbl => l_party_tbl,
628 p_account_tbl => l_account_tbl,
629 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
630 p_org_assignments_tbl => l_org_assignments_tbl,
631 p_txn_rec =>l_Txn_Rec,
632 p_asset_assignment_tbl => l_asset_assignment_tbl,
633 x_return_status =>l_Return_Status,
634 x_msg_count =>l_Msg_Count,
635 x_msg_data =>l_Msg_Data );
636
637 -- get the error message from the stack if there is any error
638
639 IF NOT (l_Return_Status = FND_API.G_Ret_Sts_Success) THEN
640 l_msg_index := 1;
641 l_Error_Message:=l_msg_data;
642 WHILE l_msg_count > 0 LOOP
643 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
644 l_msg_index := l_msg_index + 1;
645 l_Msg_Count := l_Msg_Count - 1;
646 END LOOP;
647 RAISE Update_Ib_Exp;
648 ELSIF l_Return_Status = FND_API.G_Ret_Sts_Success THEN
649 IF t_transaction_id =-1 THEN
650 t_transaction_id := l_txn_rec.transaction_id;
651 END IF;
652 END IF;
653 END IF;
654 END IF;
655 END IF;
656
657 EXCEPTION
658 WHEN Update_Ib_Exp THEN
659 X_Return_Status := l_Return_Status;
660 x_Error_Message := l_Error_Message;
661
662 WHEN OTHERS THEN
663 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
664 fnd_message.set_token('ERR_MSG',l_Api_Name||'='|| SQLERRM);
665 x_error_message := fnd_message.get;
666 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
667
668 END Update_Ib_Repository;
669 PROCEDURE Update_eib_instances(
670 P_Out_Of_Service_Attr_tbl IN Out_Of_Service_Attr_tbl_Type,
671 X_Return_Status OUT NOCOPY VARCHAR2,
672 X_Error_Message OUT NOCOPY VARCHAR2) IS
673 BEGIN
674 IF NOT P_Out_Of_Service_Attr_tbl.COUNT = 0 THEN
675 FOR i IN P_Out_Of_Service_Attr_tbl.FIRST .. P_Out_Of_Service_Attr_tbl.LAST LOOP
676 IF p_out_of_service_attr_tbl.EXISTS(i) THEN
677 update_ib_repository(p_out_of_service_attr_tbl(i),
678 x_return_status,
679 x_error_message);
680 END IF;
681 END LOOP;
682 END IF;
683 END update_eib_instances;
684 END CSE_OUT_OF_SERVICE_PKG ;