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