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