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