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