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