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