[Home] [Help]
PACKAGE BODY: APPS.CSE_UTIL_PKG
Source
1 PACKAGE BODY CSE_UTIL_PKG as
2 -- $Header: CSEUTILB.pls 120.11 2006/05/31 07:30:59 brmanesh ship $
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('CSE_DEBUG_OPTION'),'N');
5
6
7 l_Sysdate DATE := SYSDATE;
8 PROCEDURE Check_item_Trackable(
9 p_inventory_item_id IN NUMBER,
10 p_nl_trackable_flag OUT NOCOPY VARCHAR2
11 )
12 IS
13 -- Enter the procedure variables here. As shown below
14 -- variable_name datatype NOT NULL DEFAULT default_value ;
15 yes_or_no VARCHAR2(2) := 'N';
16 l_err_text VARCHAR2(2000);
17 CURSOR NL_TRACK_CUR(P_Item_Id IN NUMBER) IS
18 SELECT DISTINCT 'Y'
19 FROM mtl_system_items
20 WHERE inventory_item_id = p_item_id
21 AND organization_id =
22 (select organization_id
23 from mtl_system_items
24 where inventory_item_id=P_inventory_item_id
25 and rownum =1)
26 AND enabled_flag = 'Y'
27 AND nvl (start_date_active, l_sysdate) <= l_sysdate
28 AND nvl (end_date_active, l_sysdate+1) > l_sysdate
29 AND comms_nl_trackable_flag = 'Y';
30 BEGIN
31 OPEN NL_Track_Cur(P_Inventory_Item_Id);
32 FETCH NL_Track_Cur INTO Yes_Or_No;
33 CLOSE NL_Track_Cur;
34 IF (yes_or_no = 'Y') THEN
35 p_nl_trackable_flag := 'TRUE';
36 ELSE
37 p_nl_trackable_flag := 'FALSE';
38 END IF;
39 EXCEPTION
40 WHEN OTHERS THEN
41 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
42 fnd_message.set_token('ERR_MSG', sqlerrm);
43 l_err_text := fnd_message.get;
44 -- CSE_DEBUG_PUB.ADD('API CSE_UTIL_PKG.check_nl_trackable other exception: ' || l_err_text);
45 END check_item_trackable;
46
47 PROCEDURE check_lot_control(
48 p_inventory_item_id IN NUMBER,
49 p_organization_id IN NUMBER,
50 p_lot_control OUT NOCOPY VARCHAR2
51 )
52 IS
53 -- variable_name datatype NOT NULL DEFAULT default_value ;
54 yes_or_no VARCHAR2(2) := 'N';
55 l_err_text VARCHAR2(2000);
56 CURSOR Lot_Cur (P_Item_ID IN NUMBER,
57 P_Org_ID IN NUMBER) IS
58 SELECT DISTINCT 'Y'
59 FROM mtl_system_items
60 WHERE inventory_item_id = P_item_id
61 AND organization_id = P_org_id
62 AND enabled_flag = 'Y'
63 AND nvl (start_date_active, l_sysdate) <= l_sysdate
64 AND nvl (end_date_active, l_sysdate+1) > l_sysdate
65 AND lot_control_code <> 1;
66 BEGIN
67 OPEN Lot_Cur(P_Inventory_Item_Id, P_Organization_ID);
68 FETCH Lot_Cur INTO Yes_Or_No;
69 CLOSE Lot_Cur;
70 IF (yes_or_no = 'Y') THEN
71 p_lot_control := 'TRUE';
72 ELSIF (yes_or_no = 'N') THEN
73 p_lot_control := 'FALSE';
74 END IF;
75 EXCEPTION
76 WHEN OTHERS THEN
77 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
78 fnd_message.set_token('ERR_MSG', sqlerrm);
79 l_err_text := fnd_message.get;
80 raise;
81 END check_lot_control;
82
83 PROCEDURE check_serial_control(
84 p_inventory_item_id IN NUMBER,
85 p_organization_id IN NUMBER,
86 p_serial_control OUT NOCOPY VARCHAR2
87 )
88 IS
89 -- variable_name datatype NOT NULL DEFAULT default_value ;
90 yes_or_no VARCHAR2(2) := 'N';
91 l_err_text VARCHAR2(2000);
92 CURSOR Serial_CUR(P_Item_Id IN NUMBER,
93 P_Org_Id IN NUMBER) IS
94 SELECT DISTINCT 'Y'
95 FROM mtl_system_items
96 WHERE inventory_item_id = p_item_id
97 AND organization_id = p_org_id
98 AND enabled_flag = 'Y'
99 AND nvl (start_date_active, l_sysdate) <= l_sysdate
100 AND nvl (end_date_active, l_sysdate+1) > l_sysdate
101 AND serial_number_control_code <> 1;
102 BEGIN
103 OPEN Serial_Cur(P_Inventory_Item_Id, P_Organization_ID);
104 FETCH Serial_Cur INTO Yes_Or_No;
105 CLOSE Serial_Cur;
106 IF (yes_or_no = 'Y') THEN
107 p_serial_control := 'TRUE';
108 ELSIF (yes_or_no = 'N') THEN
109 p_serial_control := 'FALSE';
110 END IF;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 p_serial_control := 'FALSE';
114 WHEN OTHERS THEN
115 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
116 fnd_message.set_token('ERR_MSG', sqlerrm);
117 l_err_text := fnd_message.get;
118 IF (l_debug = 'Y') THEN
119 cse_debug_pub.add('API CSE_UTIL_PKG.check_serial_control other exception: ' || l_err_text);
120 END IF;
121 raise;
122 END check_serial_control;
123
124 PROCEDURE check_depreciable_subinv(
125 p_subinventory IN VARCHAR2,
126 p_organization_id IN NUMBER,
127 p_depreciable OUT NOCOPY VARCHAR2
128 )
129 IS
130 -- Enter the procedure variables here. As shown below
131 -- variable_name datatype NOT NULL DEFAULT default_value ;
132 yes_or_no VARCHAR2(2) := 'N';
133 l_err_text VARCHAR2(2000);
134 CURSOR SubInv_Cur(P_Subinv IN VARCHAR2,
135 P_Org_Id IN NUMBER) IS
136 SELECT DISTINCT 'Y'
137 FROM mtl_secondary_inventories
138 WHERE secondary_inventory_name = p_subinv
139 AND organization_id = p_org_id
140 AND disable_date IS NULL
141 AND depreciable_flag = 1;
142 BEGIN
143 OPEN SubInv_Cur(P_SubInventory, P_Organization_ID);
144 FETCH SubInv_Cur INTO Yes_Or_No;
145 CLOSE SubInv_Cur;
146 IF (yes_or_no = 'Y') THEN
147 p_depreciable := 'TRUE';
148 ELSE
149 p_depreciable := 'FALSE';
150 END IF;
151 EXCEPTION
152 WHEN OTHERS THEN
153 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
154 fnd_message.set_token('ERR_MSG', sqlerrm);
155 l_err_text := fnd_message.get;
156 raise;
157 END check_depreciable_subinv;
158
159 PROCEDURE get_asset_creation_code(
160 p_inventory_item_id IN NUMBER,
161 p_asset_creation_code OUT NOCOPY VARCHAR2
162 )
163 IS
164 -- Enter the procedure variables here. As shown below
165 -- variable_name datatype NOT NULL DEFAULT default_value ;
166 l_err_text VARCHAR2(2000);
167 CURSOR Asset_CC_Cur (P_Item_Id IN NUMBER) IS
168 SELECT DISTINCT asset_creation_code
169 FROM mtl_system_items
170 WHERE inventory_item_id = p_inventory_item_id
171 AND organization_id =
172 (select organization_id
173 from mtl_system_items
174 where inventory_item_id=p_inventory_item_id
175 and rownum=1)
176 AND enabled_flag = 'Y'
177 AND nvl (start_date_active, l_sysdate) <= l_sysdate
178 AND nvl (end_date_active, l_sysdate+1) > l_sysdate;
179 BEGIN
180 P_Asset_Creation_Code := NULL;
181 OPEN Asset_CC_Cur(P_inventory_item_id);
182 FETCH Asset_CC_Cur INTO P_Asset_Creation_Code;
183 IF NOT Asset_CC_Cur%FOUND THEN
184 P_Asset_Creation_Code := NULL;
185 END IF;
186 CLOSE Asset_CC_Cur;
187 EXCEPTION
188 WHEN OTHERS THEN
189 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
190 fnd_message.set_token('ERR_MSG', sqlerrm);
191 l_err_text := fnd_message.get;
192 raise;
193 END get_asset_creation_code;
194
195 PROCEDURE check_depreciable(
196 p_inventory_item_id IN NUMBER,
197 p_depreciable OUT NOCOPY VARCHAR2
198 )
199 IS
200 -- Enter the procedure variables here. As shown below
201 -- variable_name datatype NOT NULL DEFAULT default_value ;
202 l_asset_creation_code VARCHAR2(1);
203 l_err_text VARCHAR2(2000);
204 BEGIN
205 CSE_UTIL_PKG.Get_Asset_Creation_Code(
206 p_inventory_item_id,
207 l_asset_creation_code);
208 IF l_asset_creation_code NOT IN ('1','Y') OR
209 l_asset_creation_code IS NULL
210 THEN
211 p_depreciable := 'N';
212 ELSE
213 p_depreciable := 'Y';
214 END IF;
215 EXCEPTION
216 WHEN OTHERS THEN
217 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
218 fnd_message.set_token('ERR_MSG', sqlerrm);
219 l_err_text := fnd_message.get;
220 raise;
221 END check_depreciable;
222
223 PROCEDURE get_combine_segments(
224 p_short_name IN VARCHAR2,
225 p_flex_code IN VARCHAR2,
226 p_concat_segments IN VARCHAR2,
227 x_combine_segments OUT NOCOPY VARCHAR2,
228 x_Return_Status OUT NOCOPY VARCHAR2,
229 x_Error_Message OUT NOCOPY VARCHAR2)
230
231 IS
232 struct_num NUMBER := 101;
233 l_err_text VARCHAR2(2000);
234 delimiter VARCHAR2(1);
235 segs FND_FLEX_EXT.SegmentArray;
236 nsegs NUMBER;
237 concat_segments varchar2(150);
238 temp_segs varchar2(150);
239 DLT_NOT_FOUND EXCEPTION;
240 BEGIN
241
242 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
243 X_Error_Message := Null;
244
245 delimiter := fnd_flex_ext.get_delimiter(p_short_name, p_flex_code, struct_num);
246 if delimiter is null then
247 raise DLT_NOT_FOUND;
248 end if;
249 nsegs := fnd_flex_ext.breakup_segments(p_concat_segments, delimiter, segs);
250 for i in 1..nsegs
251 loop
252 temp_segs := RTRIM(LTRIM(temp_segs)) || segs(i);
253 end loop;
254 x_combine_segments := temp_segs;
255 EXCEPTION
256 WHEN DLT_NOT_FOUND THEN
257
258 fnd_message.set_name('CSE', 'CSE_DELIMITER_NOT_FOUND');
259 l_err_text := fnd_message.get;
260 IF (l_debug = 'Y') THEN
261 cse_debug_pub.add('API CSE_UTIL_PKG.get_combine_segments Exception: ' || l_err_text);
262 END IF;
263 x_Error_Message := l_err_text;
264 x_Return_Status := FND_API.G_RET_STS_ERROR;
265 -- return;
266 WHEN OTHERS THEN
267 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
268 fnd_message.set_token('ERR_MSG', sqlerrm);
269 l_err_text := fnd_message.get;
270 IF (l_debug = 'Y') THEN
271 cse_debug_pub.add('API CSE_UTIL_PKG.get_combine_segments other exception: ' || l_err_text);
272 END IF;
273 x_Error_Message :=l_err_text;
274 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
275 END get_combine_segments;
276
277 PROCEDURE get_concat_segments(
278 p_short_name IN VARCHAR2,
279 p_flex_code IN VARCHAR2,
280 p_combination_id IN NUMBER,
281 x_concat_segments OUT NOCOPY VARCHAR2,
282 x_Return_Status OUT NOCOPY VARCHAR2,
283 x_Error_Message OUT NOCOPY VARCHAR2)
284
285 IS
286 struct_num NUMBER := 101;
287 l_err_text VARCHAR2(2000);
288 delimiter VARCHAR2(1);
289 segs FND_FLEX_EXT.SegmentArray;
290 nsegs NUMBER;
291 concat_segments varchar2(150);
292 temp_segs varchar2(150);
293 tf boolean DEFAULT TRUE;
294 DLT_NOT_FOUND EXCEPTION;
295 SEGS_NOT_FOUND EXCEPTION;
296 BEGIN
297 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
298 X_Error_Message := Null;
299
300 delimiter := fnd_flex_ext.get_delimiter(p_short_name, p_flex_code, struct_num);
301 if delimiter is null then
302 raise DLT_NOT_FOUND;
303 end if;
304 tf := fnd_flex_ext.get_segments(p_short_name, p_flex_code, struct_num, p_combination_id, nsegs, segs);
305
306 if NOT tf then
307 raise SEGS_NOT_FOUND;
308 end if;
309
310 x_concat_segments := fnd_flex_ext.concatenate_segments(nsegs, segs, delimiter);
311 EXCEPTION
312 WHEN SEGS_NOT_FOUND THEN
313 fnd_message.set_name('CSE', 'CSE_FLEX_SEGMENTS_NOT_FOUND');
314 fnd_message.set_token('COMBINATION_ID',P_Combination_Id);
315 l_err_text := fnd_message.get;
316 IF (l_debug = 'Y') THEN
317 cse_debug_pub.add('API CSE_UTIL_PKG.get_combine_segments Exception: ' || l_err_text);
318 END IF;
319 x_Return_Status := FND_API.G_RET_STS_ERROR;
320 X_Error_Message :=l_err_text;
321 WHEN DLT_NOT_FOUND THEN
322 fnd_message.set_name('CSE', 'CSE_DELIMITER_NOT_FOUND');
323 l_err_text := fnd_message.get;
324 IF (l_debug = 'Y') THEN
325 cse_debug_pub.add('API CSE_UTIL_PKG.get_combine_segments Exception: ' || l_err_text);
326 END IF;
327 x_Return_Status := FND_API.G_RET_STS_ERROR;
328 X_Error_Message :=l_err_text;
329 WHEN OTHERS THEN
330 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
331 fnd_message.set_token('ERR_MSG', sqlerrm);
332 l_err_text := fnd_message.get;
333 IF (l_debug = 'Y') THEN
334 cse_debug_pub.add('API CSE_UTIL_PKG.get_combine_segments other exception: ' || l_err_text);
335 END IF;
336 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
337 X_Error_Message :=l_err_text;
338 END get_concat_segments;
339
340 FUNCTION is_eib_installed RETURN VARCHAR2
341 IS
342 l_eib_installed VARCHAR2(1) := 'N' ;
343 dummy VARCHAR2(40);
344 ret BOOLEAN;
345 BEGIN
346 IF (CSE_UTIL_PKG.x_cse_install is NULL)
347 THEN
348 ret := fnd_installation.get_app_info('CSE',
349 CSE_UTIL_PKG.x_cse_install, dummy, dummy);
350 END IF;
351
352 IF (CSE_UTIL_PKG.x_cse_install = 'I')
353 THEN
354 l_eib_installed := 'Y';
355 ELSE
356 l_eib_installed := 'N';
357 END IF;
358 RETURN l_eib_installed ;
359 END is_eib_installed ;
360
361
362 FUNCTION bypass_event_queue RETURN boolean
363 IS
364 ret BOOLEAN;
365 l_flag VARCHAR2(1);
366
367 BEGIN
368 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL
369 THEN
370 csi_gen_utility_pvt.populate_install_param_rec;
371 END IF;
372 l_flag := NVL(csi_datastructures_pub.g_install_param_rec.sfm_queue_bypass_flag,'N');
373
374 IF l_flag = 'Y'
375 THEN
376 ret := TRUE;
377 ELSE
378 ret:= FALSE;
379 END IF;
380
381 RETURN ret;
382 END bypass_event_queue;
383
384
385 FUNCTION get_neg_inv_code (p_org_id in NUMBER) RETURN NUMBER IS
386
387 l_neg_code NUMBER := 0;
388
389 cursor c_code (pc_org_id in NUMBER) is
390 SELECT negative_inv_receipt_Code
391 FROM mtl_parameters
392 WHERE organization_id = pc_org_id;
393
394 r_code c_code%rowtype;
395
396 BEGIN
397 OPEN c_code (p_org_id);
398 FETCH c_code into r_code;
399 IF c_code%found THEN
400 l_neg_code := r_code.negative_inv_receipt_code;
401 END IF;
402 CLOSE c_code;
403 RETURN l_neg_code ;
404 END get_neg_inv_code;
405
406 PROCEDURE get_destination_instance(
407 P_Dest_Instance_tbl IN csi_datastructures_pub.instance_header_tbl,
408 X_Instance_Rec OUT NOCOPY csi_datastructures_pub.Instance_Rec,
409 X_Return_Status OUT NOCOPY VARCHAR2,
410 X_Error_Message OUT NOCOPY VARCHAR2) IS
411
412 l_Api_Name CONSTANT VARCHAR2(50) :='CSE_UTIL_PKG.GET_DESTINATION_INSTANCE';
413 l_Active_Rec_Count NUMBER:=0;
414 l_Zero_Exp_Rec_Count NUMBER:=0;
415 l_Point_Active_Rec VARCHAR2(1) DEFAULT FND_API.G_FALSE;
416 l_Point_Zero_Exp_Rec VARCHAR2(1) DEFAULT FND_API.G_FALSE;
417 l_Dest_Instance_Found VARCHAR2(1) DEFAULT FND_API.G_FALSE;
418 i PLS_INTEGER;
419 Multiple_Active_Exp EXCEPTION;
420 a NUMBER := 0;
421 l_instance_status_id NUMBER;
422 CURSOR inst_status_cur IS
423 SELECT instance_status_id
424 FROM csi_instance_statuses
425 WHERE upper(name) ='CREATED';
426 e_Nothing EXCEPTION;
427 BEGIN
428 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
429 X_Error_Message := Null;
430 X_Instance_Rec := Null;
431 --count active instances and expired instances with zero quantity
432 IF P_Dest_Instance_Tbl.COUNT = 0
433 THEN
434 RAISE e_Nothing;
435 END IF;
436 FOR inst_status_cur_rec IN inst_status_cur
437 LOOP
438 l_instance_status_id := inst_status_cur_rec.instance_status_id;
439 END LOOP;
440
441 FOR i IN P_Dest_Instance_Tbl.FIRST ..P_Dest_Instance_Tbl.LAST
442 LOOP
443 IF (P_Dest_Instance_Tbl(i).Active_End_Date IS NULL ) THEN
444 l_Active_Rec_Count:= (l_Active_Rec_Count +1);
445 ELSIF((P_Dest_Instance_Tbl(i).Active_End_Date IS NOT NULL ) AND
446 ( P_Dest_Instance_Tbl(i).Quantity = 0)) THEN
447 l_Zero_Exp_Rec_Count:= (l_Zero_Exp_Rec_Count +1);
448 END IF;
449 END LOOP;
450
451 --Raise exception if there exist multiple active destination instances
452
453 IF(l_Active_Rec_Count>1) THEN
454 RAISE Multiple_Active_Exp;
455
456 --Check if there exist active destination instance or expired dest instance
457 --with zero quantity
458
459 ELSIF(l_Active_Rec_Count=1) THEN
460 l_Point_Active_Rec :=FND_API.G_TRUE;
461 l_Dest_Instance_Found :=FND_API.G_TRUE;
462
463 ELSIF(l_Active_Rec_Count=0 AND l_Zero_Exp_Rec_Count>0) THEN
464 l_Point_Zero_Exp_Rec :=FND_API.G_TRUE;
465 l_Dest_Instance_Found :=FND_API.G_TRUE;
466
467 ELSIF(l_Active_Rec_Count=0 AND l_Zero_Exp_Rec_Count=0) THEN
468 l_Dest_Instance_Found :=FND_API.G_FALSE;
469
470 END IF;
471
472 -- get the record pointer
473
474 FOR i IN P_Dest_Instance_Tbl.FIRST ..P_Dest_Instance_Tbl.LAST
475 LOOP
476 IF ((l_Point_Active_Rec=FND_API.G_TRUE) AND
477 (P_Dest_Instance_Tbl(i).Active_End_Date IS NULL)) THEN
478 a := i;
479 EXIT;
480 ELSIF((l_Point_Zero_Exp_Rec=FND_API.G_TRUE) AND
481 (P_Dest_Instance_Tbl(i).Active_End_Date IS NOT NULL AND
482 P_Dest_Instance_Tbl(i).Quantity = 0)) THEN
483 a := i;
484 EXIT;
485 END IF;
486 END LOOP;
487
488 IF (l_Dest_Instance_Found =FND_API.G_TRUE) THEN
489
490 X_Instance_Rec.INSTANCE_ID := P_Dest_Instance_Tbl(a).INSTANCE_ID;
491 X_Instance_Rec.INSTANCE_NUMBER := P_Dest_Instance_Tbl(a).INSTANCE_NUMBER;
492 X_Instance_Rec.EXTERNAL_REFERENCE := P_Dest_Instance_Tbl(a).EXTERNAL_REFERENCE;
493 X_Instance_Rec.INVENTORY_ITEM_ID := P_Dest_Instance_Tbl(a).INVENTORY_ITEM_ID;
494 X_Instance_Rec.INVENTORY_REVISION := P_Dest_Instance_Tbl(a).INVENTORY_REVISION;
495 X_Instance_Rec.INV_MASTER_ORGANIZATION_ID:= P_Dest_Instance_Tbl(a).INV_MASTER_ORGANIZATION_ID;
496 X_Instance_Rec.SERIAL_NUMBER := P_Dest_Instance_Tbl(a).SERIAL_NUMBER;
497 X_Instance_Rec.MFG_SERIAL_NUMBER_FLAG := P_Dest_Instance_Tbl(a).MFG_SERIAL_NUMBER_FLAG;
498 X_Instance_Rec.LOT_NUMBER := P_Dest_Instance_Tbl(a).LOT_NUMBER;
499 X_Instance_Rec.QUANTITY := P_Dest_Instance_Tbl(a).QUANTITY;
500 X_Instance_Rec.UNIT_OF_MEASURE := P_Dest_Instance_Tbl(a).UNIT_OF_MEASURE;
501 X_Instance_Rec.ACCOUNTING_CLASS_CODE := P_Dest_Instance_Tbl(a).ACCOUNTING_CLASS_CODE;
502 X_Instance_Rec.INSTANCE_CONDITION_ID := P_Dest_Instance_Tbl(a).INSTANCE_CONDITION_ID;
503 X_Instance_Rec.INSTANCE_USAGE_CODE := P_Dest_Instance_Tbl(a).INSTANCE_USAGE_CODE;
504 X_Instance_Rec.INSTANCE_STATUS_ID := P_Dest_Instance_Tbl(a).INSTANCE_STATUS_ID;
505 X_Instance_Rec.CUSTOMER_VIEW_FLAG := P_Dest_Instance_Tbl(a).CUSTOMER_VIEW_FLAG ;
506 X_Instance_Rec.MERCHANT_VIEW_FLAG := P_Dest_Instance_Tbl(a).MERCHANT_VIEW_FLAG ;
507 X_Instance_Rec.SELLABLE_FLAG := P_Dest_Instance_Tbl(a).SELLABLE_FLAG;
508 X_Instance_Rec.SYSTEM_ID := P_Dest_Instance_Tbl(a).SYSTEM_ID;
509 X_Instance_Rec.INSTANCE_TYPE_CODE := P_Dest_Instance_Tbl(a).INSTANCE_TYPE_CODE;
510 X_Instance_Rec.ACTIVE_START_DATE := P_Dest_Instance_Tbl(a).ACTIVE_START_DATE;
511 X_Instance_Rec.ACTIVE_END_DATE := Null;
512 X_Instance_Rec.LOCATION_TYPE_CODE := P_Dest_Instance_Tbl(a).LOCATION_TYPE_CODE;
513 X_Instance_Rec.LOCATION_ID := P_Dest_Instance_Tbl(a).LOCATION_ID;
514 X_Instance_Rec.INV_ORGANIZATION_ID := P_Dest_Instance_Tbl(a).INV_ORGANIZATION_ID;
515 X_Instance_Rec.INV_SUBINVENTORY_NAME := P_Dest_Instance_Tbl(a).INV_SUBINVENTORY_NAME;
516 X_Instance_Rec.INV_LOCATOR_ID := P_Dest_Instance_Tbl(a).INV_LOCATOR_ID;
517 X_Instance_Rec.PA_PROJECT_ID := P_Dest_Instance_Tbl(a).PA_PROJECT_ID;
518 X_Instance_Rec.PA_PROJECT_TASK_ID := P_Dest_Instance_Tbl(a).PA_PROJECT_TASK_ID;
519 X_Instance_Rec.IN_TRANSIT_ORDER_LINE_ID := P_Dest_Instance_Tbl(a).IN_TRANSIT_ORDER_LINE_ID;
520 X_Instance_Rec.WIP_JOB_ID := P_Dest_Instance_Tbl(a).WIP_JOB_ID ;
521 X_Instance_Rec.PO_ORDER_LINE_ID := P_Dest_Instance_Tbl(a).PO_ORDER_LINE_ID;
522 X_Instance_Rec.LAST_OE_ORDER_LINE_ID := P_Dest_Instance_Tbl(a).LAST_OE_ORDER_LINE_ID;
523 X_Instance_Rec.LAST_OE_RMA_LINE_ID := P_Dest_Instance_Tbl(a).LAST_OE_RMA_LINE_ID;
524 X_Instance_Rec.LAST_PO_PO_LINE_ID := P_Dest_Instance_Tbl(a).LAST_PO_PO_LINE_ID;
525 X_Instance_Rec.LAST_OE_PO_NUMBER := P_Dest_Instance_Tbl(a).LAST_OE_PO_NUMBER;
526 X_Instance_Rec.LAST_PA_PROJECT_ID := P_Dest_Instance_Tbl(a).LAST_PA_PROJECT_ID;
527 X_Instance_Rec.LAST_PA_TASK_ID := P_Dest_Instance_Tbl(a).LAST_PA_TASK_ID;
528 X_Instance_Rec.LAST_OE_AGREEMENT_ID := P_Dest_Instance_Tbl(a).LAST_OE_AGREEMENT_ID;
529 X_Instance_Rec.INSTALL_DATE := P_Dest_Instance_Tbl(a).INSTALL_DATE;
530 X_Instance_Rec.MANUALLY_CREATED_FLAG := P_Dest_Instance_Tbl(a).MANUALLY_CREATED_FLAG ;
531 X_Instance_Rec.RETURN_BY_DATE := P_Dest_Instance_Tbl(a).RETURN_BY_DATE;
532 X_Instance_Rec.ACTUAL_RETURN_DATE := P_Dest_Instance_Tbl(a).ACTUAL_RETURN_DATE;
533 X_Instance_Rec.CREATION_COMPLETE_FLAG := P_Dest_Instance_Tbl(a).CREATION_COMPLETE_FLAG;
534 X_Instance_Rec.COMPLETENESS_FLAG := P_Dest_Instance_Tbl(a).COMPLETENESS_FLAG;
535 -- X_Instance_Rec.VERSION_LABEL := P_Dest_Instance_Tbl(a).VERSION_LABEL;
536 -- X_Instance_Rec.VERSION_LABEL_DESCRIPTION := P_Dest_Instance_Tbl(a).VERSION_LABEL_DESCRIPTION;
537 X_Instance_Rec.CONTEXT := P_Dest_Instance_Tbl(a).CONTEXT ;
538 X_Instance_Rec.ATTRIBUTE1 := P_Dest_Instance_Tbl(a).ATTRIBUTE1;
539 X_Instance_Rec.ATTRIBUTE2 := P_Dest_Instance_Tbl(a).ATTRIBUTE2;
540 X_Instance_Rec.ATTRIBUTE3 := P_Dest_Instance_Tbl(a).ATTRIBUTE3;
541 X_Instance_Rec.ATTRIBUTE4 := P_Dest_Instance_Tbl(a).ATTRIBUTE4;
542 X_Instance_Rec.ATTRIBUTE5 := P_Dest_Instance_Tbl(a).ATTRIBUTE5;
543 X_Instance_Rec.ATTRIBUTE6 := P_Dest_Instance_Tbl(a).ATTRIBUTE6;
544 X_Instance_Rec.ATTRIBUTE7 := P_Dest_Instance_Tbl(a).ATTRIBUTE7;
545 X_Instance_Rec.ATTRIBUTE8 := P_Dest_Instance_Tbl(a).ATTRIBUTE8;
546 X_Instance_Rec.ATTRIBUTE9 := P_Dest_Instance_Tbl(a).ATTRIBUTE9;
547 X_Instance_Rec.ATTRIBUTE10 := P_Dest_Instance_Tbl(a).ATTRIBUTE10;
548 X_Instance_Rec.ATTRIBUTE11 := P_Dest_Instance_Tbl(a).ATTRIBUTE11;
549 X_Instance_Rec.ATTRIBUTE12 := P_Dest_Instance_Tbl(a).ATTRIBUTE12;
550 X_Instance_Rec.ATTRIBUTE13 := P_Dest_Instance_Tbl(a).ATTRIBUTE13;
551 X_Instance_Rec.ATTRIBUTE14 := P_Dest_Instance_Tbl(a).ATTRIBUTE14;
552 X_Instance_Rec.ATTRIBUTE15 := P_Dest_Instance_Tbl(a).ATTRIBUTE15;
553 X_Instance_Rec.OBJECT_VERSION_NUMBER := P_Dest_Instance_Tbl(a).OBJECT_VERSION_NUMBER;
554
555
556 END IF;
557 EXCEPTION
558 WHEN e_Nothing THEN
559 NULL;
560 WHEN Multiple_Active_Exp THEN
561 fnd_message.set_name('CSE','CSE_MULTIPLE_ACT_INST_FOUND');
562 fnd_message.set_token('INV_ITEM',P_Dest_Instance_Tbl(1).INVENTORY_ITEM_ID);
563 x_error_message := fnd_message.get;
564 x_return_status :=FND_API.G_RET_STS_ERROR;
565 WHEN OTHERS THEN
566 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
567 fnd_message.set_token('API_NAME',l_api_name);
568 fnd_message.set_token('SQL_ERROR',SQLERRM);
569 x_error_message := fnd_message.get;
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
571
572 END get_destination_instance;
573
574 PROCEDURE get_hz_location (p_network_location_code IN VARCHAR2,
575 x_hz_location_id OUT NOCOPY NUMBER,
576 x_return_status OUT NOCOPY VARCHAR2,
577 x_error_message OUT NOCOPY VARCHAR2)
578 IS
579
580 cursor hz_loc is
581 select location_id
582 from hz_locations
583 where clli_code=p_network_location_code;
584
585 l_api_name CONSTANT VARCHAR2(30) := 'GET_HZ_LOCATION';
586 l_loop_count NUMBER:=0;
587 hz_loc_not_found EXCEPTION;
588
589 BEGIN
590 x_return_status:=FND_API.G_RET_STS_SUCCESS;
591 x_error_message:=null;
592
593 FOR hz_loc_rec in hz_loc
594 LOOP
595
596 x_hz_location_id:=hz_loc_rec.location_id;
597 l_loop_count:=l_loop_count+1;
598
599 END LOOP;
600
601 IF l_loop_count=0 THEN
602 RAISE hz_loc_not_found;
603 END IF;
604
605 EXCEPTION
606 WHEN hz_loc_not_found THEN
607 fnd_message.set_name('CSE','CSE_HZ_LOC_ID_NOTFOUND');
608 fnd_message.set_token('NETWORK_LOC_CODE',p_network_location_code);
609 x_error_message := fnd_message.get;
610 x_return_status :=FND_API.G_RET_STS_ERROR;
611 WHEN others THEN
612 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
613 fnd_message.set_token('ERR_MSG',l_Api_Name||'='|| SQLERRM);
614 x_error_message := fnd_message.get;
615 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
616
617 END get_hz_location;
618
619 PROCEDURE get_hz_location (p_party_site_id IN NUMBER,
620 x_hz_location_id OUT NOCOPY NUMBER,
621 x_return_status OUT NOCOPY VARCHAR2,
622 x_error_message OUT NOCOPY VARCHAR2)
623 IS
624
625 cursor hz_loc is
626 select location_id
627 from hz_party_sites
628 where party_site_id=p_party_site_id;
629
630 l_api_name CONSTANT VARCHAR2(30) := 'GET_HZ_LOCATION';
631 l_loop_count NUMBER:=0;
632 hz_loc_not_found EXCEPTION;
633
634 BEGIN
635 x_return_status:=FND_API.G_RET_STS_SUCCESS;
636 x_error_message:=null;
637
638 FOR hz_loc_rec in hz_loc
639 LOOP
640
641 x_hz_location_id:=hz_loc_rec.location_id;
642 l_loop_count:=l_loop_count+1;
643
644 END LOOP;
645
646 IF l_loop_count=0 THEN
647 RAISE hz_loc_not_found;
648 END IF;
649
650 EXCEPTION
651 WHEN hz_loc_not_found THEN
652 fnd_message.set_name('CSE','CSE_PARTY_SITE_NOTFOUND');
653 fnd_message.set_token('PARTY_SITE',p_party_site_id);
654 x_error_message := fnd_message.get;
655 x_return_status :=FND_API.G_RET_STS_ERROR;
656 WHEN others THEN
657 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
658 fnd_message.set_token('ERR_MSG',l_Api_Name||'='|| SQLERRM);
659 x_error_message := fnd_message.get;
660 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
661
662 END get_hz_location;
663
664 PROCEDURE get_fa_location(p_hz_location_id IN NUMBER,
665 p_loc_type_code IN VARCHAR2,
666 x_fa_location_id OUT NOCOPY NUMBER,
667 x_return_status OUT NOCOPY VARCHAR2,
668 x_error_message OUT NOCOPY VARCHAR2)
669
670 IS
671 cursor fa_loc is
672 select fa_location_id
673 from csi_a_locations
674 where location_id =p_hz_location_id;
675
676 l_loop_count NUMBER:=0;
677 fa_loc_not_found EXCEPTION;
678 l_api_name CONSTANT VARCHAR2(30) := 'GET_FA_LOCATION';
679
680 BEGIN
681 x_return_status:=FND_API.G_RET_STS_SUCCESS;
682 x_error_message:=null;
683 FOR fa_loc_rec in fa_loc
684 LOOP
685
686 x_fa_location_id:=fa_loc_rec.fa_location_id;
687 l_loop_count:=l_loop_count+1;
688
689 END LOOP;
690
691 IF l_loop_count=0 THEN
692 RAISE fa_loc_not_found;
693 END IF;
694
695 EXCEPTION
696 WHEN fa_loc_not_found THEN
697 fnd_message.set_name('CSE','CSE_FA_LOC_ID_NOTFOUND');
698 fnd_message.set_token('HZ_LOCATION_ID',p_hz_location_id);
699 x_error_message := fnd_message.get;
700 x_return_status :=FND_API.G_RET_STS_ERROR;
701 WHEN others THEN
702 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
703 fnd_message.set_token('ERR_MSG',l_Api_Name||'='|| SQLERRM);
704 x_error_message := fnd_message.get;
705 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
706 END;
707
708 PROCEDURE get_master_organization(p_organization_id IN NUMBER,
709 p_master_organization_id OUT NOCOPY NUMBER,
710 x_return_status OUT NOCOPY VARCHAR2,
711 x_error_message OUT NOCOPY VARCHAR2)
712 IS
713
714 l_sql_error VARCHAR2(500);
715 l_org_code VARCHAR2(3);
716 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
717 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
718 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
719 l_error_message VARCHAR2(2000);
720 e_procedure_error EXCEPTION;
721
722 CURSOR c_name is
723 SELECT organization_code
724 FROM mtl_parameters
725 WHERE organization_id = p_organization_id;
726
727 r_name c_name%rowtype;
728
729 CURSOR c_id IS
730 SELECT master_organization_id
731 FROM mtl_parameters
732 WHERE organization_id = p_organization_id;
733
734 r_id c_id%rowtype;
735
736 BEGIN
737
738 l_error_message := NULL;
739 x_return_status := l_fnd_success;
740
741 OPEN c_id;
742 FETCH c_id into r_id;
743 IF c_id%found then
744 p_master_organization_id := r_id.master_organization_id;
745 ELSE
746 OPEN c_name;
747 FETCH c_name into r_name;
748 if c_name%found then
749 l_org_code := r_name.organization_code;
750 end if;
751 RAISE e_procedure_error;
752 END IF;
753
754 EXCEPTION
755 WHEN e_procedure_error THEN
756 fnd_message.set_name('CSE','CSE_MSTR_ORG_NOTFOUND');
757 fnd_message.set_token('ORGANIZATION_ID',p_organization_id);
758 fnd_message.set_token('ORGANIZATION_CODE',l_org_code);
759 x_error_message := fnd_message.get;
760 x_return_status := l_fnd_error;
761
762 WHEN others THEN
763 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
764 fnd_message.set_token('SQL_ERROR',SQLERRM);
765 x_error_message := fnd_message.get;
766 x_return_status := l_fnd_unexpected;
767 END get_master_organization;
768
769 PROCEDURE build_error_string (
770 p_string IN OUT NOCOPY VARCHAR2,
771 p_attribute IN VARCHAR2,
772 p_value IN VARCHAR2) IS
773
774 BEGIN
775 p_string := p_string || '<' || p_attribute || '>' ;
776 p_string := p_string || p_value ;
777 p_string := p_string || '</' || p_attribute || '>' ;
778
779 END build_error_string;
780
781 PROCEDURE get_string_value (
782 p_string IN VARCHAR2,
783 p_attribute IN VARCHAR2,
784 x_value OUT NOCOPY VARCHAR2) IS
785
786 tag_pos INTEGER := 0 ;
787 token VARCHAR2(1024) := '' ;
788 token_delimeter VARCHAR2(1024) := '' ;
789 tag_delimeter_pos INTEGER := 0 ;
790
791 BEGIN
792
793 token := '<' || p_attribute || '>' ;
794 token_delimeter := '</' || p_attribute || '>' ;
795 tag_pos := INSTR( p_string, token, 1 ) ;
796
797 IF (tag_pos = 0)
798 THEN
799 x_value := NULL ;
800 RETURN ;
801 END IF ;
802
803 tag_delimeter_pos := INSTR( p_string, token_delimeter, 1 ) ;
804
805 IF (tag_delimeter_pos = 0)
806 THEN
807 x_value := NULL ;
808 RETURN ;
809 END IF ;
810
811 x_value := SUBSTR(p_string, tag_pos + LENGTH(token),
812 tag_delimeter_pos - (tag_pos + LENGTH(token))) ;
813
814 END get_string_value;
815
816 --This procedure tries to acquire a lock for infinite time for
817 --the given lockname.
818 --Also this procedure releases the lock on COMMIT ;
819 FUNCTION Init_Instance_Query_Rec RETURN CSI_DATASTRUCTURES_PUB.Instance_Query_Rec IS
820 l_Instance_Query_Rec CSI_DataStructures_Pub.Instance_Query_Rec;
821 BEGIN
822 RETURN l_Instance_Query_Rec;
823 END Init_Instance_Query_Rec;
824
825 FUNCTION Init_Instance_Create_Rec RETURN CSI_DATASTRUCTURES_PUB.Instance_Rec IS
826 l_Instance_Rec CSI_DATASTRUCTURES_PUB.Instance_Rec;
827 BEGIN
828 l_instance_rec.version_label := 'AS-CREATED';
829 l_instance_rec.creation_complete_flag := NULL;
830 RETURN l_Instance_Rec;
831 END Init_Instance_Create_Rec;
832
833 FUNCTION Init_Instance_Update_Rec RETURN CSI_DATASTRUCTURES_PUB.Instance_Rec IS
834 l_Instance_Rec CSI_DATASTRUCTURES_PUB.Instance_Rec;
835 BEGIN
836 RETURN l_Instance_Rec;
837 END Init_Instance_Update_Rec;
838
839
840
841 FUNCTION Init_Party_Tbl RETURN CSI_DATASTRUCTURES_PUB.Party_Tbl IS
842 l_Party_Tbl CSI_DATASTRUCTURES_PUB.Party_Tbl;
843 l_Party_Id NUMBER;
844
845 BEGIN
846
847 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL
848 THEN
849 csi_gen_utility_pvt.populate_install_param_rec;
850 END IF;
851 l_Party_ID := csi_datastructures_pub.g_install_param_rec.Internal_Party_Id;
852
853 l_Party_Tbl(1).party_source_table := 'HZ_PARTIES' ;
854 l_Party_Tbl(1).party_id := l_Party_Id;
855 l_Party_Tbl(1).relationship_type_code := 'OWNER';
856 l_Party_Tbl(1).contact_flag := 'N';
857
858 RETURN l_Party_Tbl;
859 END Init_Party_Tbl;
860
861 FUNCTION Init_Account_Tbl RETURN CSI_DATASTRUCTURES_PUB.Party_Account_Tbl IS
862 l_Account_Tbl CSI_DATASTRUCTURES_PUB.Party_Account_Tbl;
863 BEGIN
864 RETURN l_Account_Tbl;
865 END Init_Account_Tbl;
866
867 FUNCTION Init_ext_attrib_values_tbl RETURN CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl IS
868 l_extend_attrib_values_tbl CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl;
869 BEGIN
870 RETURN l_extend_attrib_values_tbl;
871 END Init_ext_attrib_values_tbl;
872
873 FUNCTION Init_Pricing_Attribs_Tbl RETURN CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl IS
874 l_Pricing_Attribs_Tbl CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl;
875 BEGIN
876 RETURN l_Pricing_Attribs_Tbl;
877 END Init_Pricing_Attribs_Tbl;
878
879 FUNCTION Init_Org_Assignments_Tbl RETURN CSI_DATASTRUCTURES_PUB.organization_units_tbl IS
880 l_Org_Assignments_Tbl CSI_DATASTRUCTURES_PUB.organization_units_tbl;
881 BEGIN
882 RETURN l_Org_Assignments_Tbl;
883 END Init_Org_Assignments_Tbl;
884
885 FUNCTION Init_Asset_Assignment_Tbl RETURN CSI_DATASTRUCTURES_PUB.instance_asset_tbl IS
886 l_Asset_Assignment_Tbl CSI_DATASTRUCTURES_PUB.instance_asset_tbl;
887 BEGIN
888 RETURN l_Asset_Assignment_Tbl;
889 END Init_Asset_Assignment_Tbl;
890
891 FUNCTION Init_Instance_Asset_Query_Rec RETURN CSI_DATASTRUCTURES_PUB.instance_asset_Query_Rec IS
892 l_instance_asset_Query_Rec CSI_DATASTRUCTURES_PUB.instance_asset_Query_Rec;
893 BEGIN
894 RETURN l_instance_asset_Query_Rec;
895 END Init_Instance_Asset_Query_Rec;
896
897 FUNCTION Init_Instance_Asset_Rec RETURN CSI_DATASTRUCTURES_PUB.instance_asset_Rec IS
898 l_instance_asset_Rec CSI_DATASTRUCTURES_PUB.instance_asset_Rec;
899 BEGIN
900 RETURN l_instance_asset_Rec;
901 END Init_Instance_Asset_Rec;
902
903 FUNCTION Get_Txn_Type_Id(P_Txn_Type IN VARCHAR2,
904 P_App_Short_Name IN VARCHAR2) RETURN NUMBER IS
905 l_Txn_Type_Id NUMBER;
906 CURSOR Txn_Type_Cur IS
907 SELECT ctt.Transaction_Type_Id Transaction_Type_Id
908 FROM CSI_Txn_Types ctt,
909 FND_Application fa
910 WHERE ctt.Source_Transaction_Type = P_Txn_Type
911 AND fa.application_id = ctt.Source_Application_ID
912 AND fa.Application_Short_Name = P_App_Short_Name;
913 BEGIN
914 OPEN Txn_Type_Cur;
915 FETCH Txn_Type_Cur INTO l_Txn_Type_Id;
916 CLOSE Txn_Type_Cur;
917 RETURN l_Txn_Type_Id;
918 END Get_Txn_Type_Id;
919
920 FUNCTION Get_Txn_Type_Code(P_Txn_Id IN NUMBER) RETURN VARCHAR2 IS
921 l_Txn_Type_Code VARCHAR2(100);
922 CURSOR Txn_Type_Id_Cur IS
923 SELECT Source_Transaction_Type
924 FROM CSI_Txn_Types
925 WHERE Transaction_Type_Id = P_Txn_Id;
926 BEGIN
927 OPEN Txn_Type_Id_Cur;
928 FETCH Txn_Type_Id_Cur INTO l_Txn_Type_Code;
929 CLOSE Txn_Type_Id_Cur;
930 RETURN l_Txn_Type_Code;
931 END Get_Txn_Type_Code;
932
933 FUNCTION Get_Txn_Status_Code(P_Txn_Status IN VARCHAR2) RETURN VARCHAR2 IS
934 l_Txn_Status_Code VARCHAR2(30) ;
935 BEGIN
936 l_Txn_Status_Code := FND_API.G_MISS_CHAR;
937 RETURN l_Txn_Status_Code;
938 END Get_Txn_Status_Code;
939
940 FUNCTION Get_Location_Type_Code(P_Location_Meaning in VARCHAR2) RETURN VARCHAR2 IS
941
942 l_location_type_code VARCHAR2(50);
943
944 CURSOR c_code IS
945 SELECT lookup_code
946 FROM csi_lookups
947 WHERE lookup_type = 'CSI_INST_LOCATION_SOURCE_CODE'
948 AND lookup_code = upper(P_Location_Meaning);
949
950 r_code c_code%rowtype;
951
952 BEGIN
953 OPEN c_code;
954 FETCH c_code into r_code;
955 IF c_code%found THEN
956 l_location_type_code := r_code.lookup_code;
957 ELSE
958 l_location_type_code := NULL;
959 END IF;
960 CLOSE c_code;
961 RETURN l_location_type_code;
962 END Get_Location_Type_Code;
963
964
965 FUNCTION Get_Dflt_Project_Location_Id RETURN NUMBER IS
966
967 l_project_location_id NUMBER :=0;
968
969 BEGIN
970 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL
971 THEN
972 csi_gen_utility_pvt.populate_install_param_rec;
973 END IF;
974 l_project_location_id := csi_datastructures_pub.g_install_param_rec.project_location_id;
975 RETURN l_project_location_id;
976 END Get_Dflt_Project_Location_Id;
977
978 FUNCTION Get_Default_Status_Id (p_transaction_id in number) RETURN NUMBER IS
979
980 l_transaction_id NUMBER;
981
982 CURSOR c_id IS
983 SELECT src_status_id
984 FROM csi_txn_sub_types
985 WHERE transaction_type_id = p_transaction_id
986 AND default_flag = 'Y';
987
988 r_id c_id%rowtype;
989
990 BEGIN
991 OPEN c_id;
992 FETCH c_id into r_id;
993 IF c_id%found THEN
994 l_transaction_id := r_id.src_status_id;
995 ELSE
996 l_transaction_id := NULL;
997 END IF;
998 CLOSE c_id;
999 RETURN l_transaction_id;
1000 END Get_Default_Status_id;
1001
1002 FUNCTION Get_Txn_Action_Code(P_Txn_Action IN VARCHAR2) RETURN VARCHAR2 IS
1003 l_Txn_Action_Code VARCHAR2(30) ;
1004
1005 BEGIN
1006 l_Txn_Action_Code := FND_API.G_MISS_CHAR;
1007 RETURN l_Txn_Action_Code;
1008 END Get_Txn_Action_Code;
1009
1010 FUNCTION Get_Fnd_Employee_Id(P_Last_Updated IN NUMBER) RETURN NUMBER IS
1011
1012 l_employee_id NUMBER;
1013
1014 CURSOR c_id IS
1015 SELECT employee_id
1016 FROM fnd_user
1017 WHERE user_id = p_last_updated;
1018
1019 r_id c_id%rowtype;
1020
1021 BEGIN
1022 OPEN c_id;
1023 FETCH c_id into r_id;
1024 IF c_id%found THEN
1025 l_employee_id := r_id.employee_id;
1026 ELSE
1027 l_employee_id := -1;
1028 END IF;
1029 CLOSE c_id;
1030 RETURN l_employee_id;
1031 END Get_Fnd_Employee_Id;
1032
1033 FUNCTION Init_Txn_Rec RETURN CSI_DATASTRUCTURES_PUB.TRANSACTION_Rec IS
1034 l_Txn_Rec CSI_DATASTRUCTURES_PUB.TRANSACTION_Rec;
1035 BEGIN
1036 RETURN l_Txn_Rec;
1037 END Init_Txn_Rec;
1038
1039 FUNCTION Init_Txn_Error_Rec RETURN CSI_DATASTRUCTURES_PUB.TRANSACTION_Error_Rec IS
1040 l_Txn_Error_Rec CSI_DATASTRUCTURES_PUB.TRANSACTION_Error_Rec;
1041 BEGIN
1042 l_Txn_Error_Rec.processed_flag := CSE_DATASTRUCTURES_PUB.G_TXN_ERROR;
1043 RETURN l_Txn_Error_Rec;
1044 END Init_Txn_Error_Rec;
1045
1046 FUNCTION Init_Party_Query_Rec RETURN CSI_DATASTRUCTURES_PUB.Party_Query_Rec IS
1047 l_Party_Query_Rec CSI_DATASTRUCTURES_PUB.Party_Query_Rec;
1048 l_Party_Id NUMBER;
1049
1050 BEGIN
1051
1052 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL
1053 THEN
1054 csi_gen_utility_pvt.populate_install_param_rec;
1055 END IF;
1056
1057 l_Party_ID := csi_datastructures_pub.g_install_param_rec.Internal_Party_Id;
1058
1059
1060 l_Party_Query_Rec.party_id := l_Party_Id;
1061 l_Party_Query_Rec.relationship_type_code := 'OWNER';
1062
1063 RETURN l_Party_Query_Rec;
1064
1065 END Init_Party_Query_Rec;
1066 FUNCTION Is_Conc_Prg_Running(P_Request_ID IN NUMBER,
1067 P_Executable IN VARCHAR2)
1068 RETURN BOOLEAN IS
1069 l_Return BOOLEAN := TRUE;
1070 l_Dummy VARCHAR2(1);
1071 CURSOR Conc_Cur IS
1072 SELECT 'X'
1073 FROM Fnd_Concurrent_Requests fcr,
1074 Fnd_Concurrent_Programs fcp,
1075 fnd_executables fe
1076 WHERE fcr.Program_Application_Id = fcp.Application_Id
1077 AND fcr.Concurrent_Program_Id = fcp.Concurrent_Program_Id
1078 AND fcr.Phase_Code = 'R'
1079 AND fcr.Request_Id <> P_Request_Id
1080 AND fcp.Executable_Application_Id = fe.Application_Id
1081 AND fcp.Executable_Id = fe.Executable_Id
1082 AND fcp.application_id = 873
1083 AND fe.Executable_Name = P_Executable;
1084 BEGIN
1085 OPEN Conc_Cur;
1086 FETCH Conc_Cur INTO l_Dummy;
1087 IF NOT Conc_Cur%FOUND
1088 THEN l_Return := FALSE;
1089 ELSE l_Return := TRUE;
1090 END IF;
1091 CLOSE Conc_Cur;
1092 RETURN l_Return;
1093 EXCEPTION
1094 WHEN OTHERS THEN
1095 RETURN l_Return;
1096 END Is_Conc_Prg_Running;
1097
1098
1099 PROCEDURE write_log (P_Message IN VARCHAR2) IS
1100 l_debug varchar2(1);
1101 BEGIN
1102 l_debug := NVL(FND_PROFILE.VALUE('CSE_DEBUG_OPTION'), 'Y');
1103 IF l_debug = 'Y'
1104 THEN
1105 FND_File.Put_Line(Fnd_File.LOG,P_Message);
1106 END IF ;
1107 EXCEPTION
1108 WHEN OTHERS THEN
1109 RAISE;
1110 END write_log;
1111
1112 FUNCTION get_inv_name (p_transaction_id IN NUMBER) RETURN VARCHAR2 IS
1113
1114 l_transaction_type_id NUMBER;
1115 l_inv_name VARCHAR2(30);
1116
1117 CURSOR x is
1118 SELECT transaction_type_id
1119 FROM mtl_material_transactions
1120 WHERE transaction_id = p_transaction_id;
1121
1122 BEGIN
1123
1124 OPEN x;
1125 FETCH x into l_transaction_type_id;
1126 CLOSE x;
1127
1128 IF l_transaction_type_id = 1 THEN -- Account issue
1129 l_inv_name := 'ACCT_ISSUE';
1130 ELSIF l_transaction_type_id = 2 THEN -- Subinventory Transfer
1131 l_inv_name := 'SUBINVENTORY_TRANSFER';
1132 ELSIF l_transaction_type_id = 3 THEN -- Direct Org Transfer
1133 l_inv_name := 'INTERORG_DIRECT_SHIP';
1134 ELSIF l_transaction_type_id = 4 THEN -- Cycle Count Adjust
1135 l_inv_name := 'CYCLE_COUNT';
1136 ELSIF l_transaction_type_id = 5 THEN -- Cycle Count Transfer
1137 l_inv_name := 'CYCLE_COUNT_TRANSFER';
1138 ELSIF l_transaction_type_id = 8 THEN -- Physical Inv Adjust
1139 l_inv_name := 'PHYSICAL_INVENTORY';
1140 ELSIF l_transaction_type_id = 9 THEN -- Physical Inv Transfer
1141 l_inv_name := 'PHYSICAL_INV_TRANSFER';
1142 ELSIF l_transaction_type_id = 12 THEN -- Intransit Receipt
1143 l_inv_name := 'INTERORG_TRANS_RECEIPT';
1144 ELSIF l_transaction_type_id = 15 THEN -- RMA Receipt
1145 l_inv_name := 'RMA_RECEIPT';
1146 ELSIF l_transaction_type_id = 17 THEN -- WIP Assembly Return
1147 l_inv_name := 'WIP_ISSUE';
1148 ELSIF l_transaction_type_id = 18 THEN -- PO Receipt
1149 l_inv_name := 'PO_RECEIPT_INTO_INVENTORY';
1150 ELSIF l_transaction_type_id = 21 THEN -- Intransit Shipment
1151 l_inv_name := 'INTERORG_TRANS_SHIPMENT';
1152 --ELSIF l_transaction_type_id = 25 THEN -- WIP cost update
1153 --ELSIF l_transaction_type_id = 26 THEN -- Periodic Cost Update
1154 --ELSIF l_transaction_type_id = 28 THEN -- Layer Cost Update
1155 ELSIF l_transaction_type_id = 31 THEN -- Account alias issue
1156 l_inv_name := 'ACCT_ALIAS_ISSUE';
1157 ELSIF l_transaction_type_id = 32 THEN -- Miscellaneous issue
1158 l_inv_name := 'MISC_ISSUE';
1159 ELSIF l_transaction_type_id = 33 THEN -- Sales order issue
1160 l_inv_name := 'OM_SHIPMENT';
1161 ELSIF l_transaction_type_id = 34 THEN -- Internal order issue
1162 l_inv_name := 'ISO_ISSUE';
1163 ELSIF l_transaction_type_id = 35 THEN -- WIP component issue
1164 l_inv_name := 'WIP_ISSUE';
1165 ELSIF l_transaction_type_id = 36 THEN -- Return to Vendor
1166 l_inv_name := 'RETURN_TO_VENDOR';
1167 --ELSIF l_transaction_type_id = 37 THEN -- RMA Return
1168 ELSIF l_transaction_type_id = 38 THEN -- WIP Neg Comp Issue
1169 l_inv_name := 'WIP_RECEIPT';
1170 ELSIF l_transaction_type_id = 40 THEN -- Account receipt
1171 l_inv_name := 'ACCT_RECEIPT';
1172 ELSIF l_transaction_type_id = 41 THEN -- Account alias receipt
1173 l_inv_name := 'ACCT_ALIAS_RECEIPT';
1174 ELSIF l_transaction_type_id = 42 THEN -- Miscellaneous receipt
1175 l_inv_name := 'MISC_RECEIPT';
1176 ELSIF l_transaction_type_id = 43 THEN -- WIP Component Return
1177 l_inv_name := 'WIP_RECEIPT';
1178 ELSIF l_transaction_type_id = 44 THEN -- WIP Assy Completion
1179 l_inv_name := 'WIP_ASSEMBLY_COMPLETION';
1180 ELSIF l_transaction_type_id = 48 THEN -- WIP Neg Comp Return
1181 l_inv_name := 'WIP_ISSUE';
1182 ELSIF l_transaction_type_id = 50 THEN -- Internal Order Xfer
1183 l_inv_name := 'ISO_TRANSFER';
1184 ELSIF l_transaction_type_id = 51 THEN -- Backflush Transfer
1185 l_inv_name := 'BACKFLUSH_TRANSFER';
1186 ELSIF l_transaction_type_id = 52 THEN -- Sales Order Pick
1187 l_inv_name := 'SALES_ORDER_PICK';
1188 ELSIF l_transaction_type_id = 53 THEN -- Internal Order Pick
1189 l_inv_name := 'ISO_PICK';
1190 ELSIF l_transaction_type_id = 54 THEN -- Int Order Direct Ship
1191 l_inv_name := 'ISO_DIRECT_SHIP';
1192 --ELSIF l_transaction_type_id = 55 THEN -- WIP Lot Split
1193 --ELSIF l_transaction_type_id = 56 THEN -- WIP Lot Merge
1194 --ELSIF l_transaction_type_id = 57 THEN -- Lot Bonus
1195 --ELSIF l_transaction_type_id = 58 THEN -- Lot Update Quantity
1196 ELSIF l_transaction_type_id = 61 THEN -- Int Req Intr Rcpt
1197 l_inv_name := 'ISO_REQUISITION_RECEIPT';
1198 ELSIF l_transaction_type_id = 62 THEN -- Int Order Intr Ship
1199 l_inv_name := 'ISO_SHIPMENT';
1200 ELSIF l_transaction_type_id = 63 THEN -- Move Order Issue
1201 l_inv_name := 'MOVE_ORDER_ISSUE';
1202 ELSIF l_transaction_type_id = 64 THEN -- Move Order Transfer
1203 l_inv_name := 'MOVE_ORDER_TRANSFER';
1204 ELSIF l_transaction_type_id = 66 THEN -- Project Borrow
1205 l_inv_name := 'PROJECT_BORROW';
1206 ELSIF l_transaction_type_id = 67 THEN -- Project Transfer
1207 l_inv_name := 'PROJECT_TRANSFER';
1208 ELSIF l_transaction_type_id = 68 THEN -- Project Payback
1209 l_inv_name := 'PROJECT_PAYBACK';
1210 ELSIF l_transaction_type_id = 70 THEN -- Shipment Rcpt Adjust
1211 l_inv_name := 'SHIPMENT_RCPT_ADJUSTMENT';
1212 ELSIF l_transaction_type_id = 71 THEN -- PO Rcpt Adjust
1213 l_inv_name := 'PO_RCPT_ADJUSTMENT';
1214 ELSIF l_transaction_type_id = 72 THEN -- Int Req Rcpt Adjust
1215 l_inv_name := 'INT_REQ_RCPT_ADJUSTMENT';
1216 --ELSIF l_transaction_type_id = 73 THEN -- Planning Transfer
1217 ELSIF l_transaction_type_id = 77 THEN -- ProjectContract Issue
1218 l_inv_name := 'OKE_SHIPMENT';
1219 --ELSIF l_transaction_type_id = 80 THEN -- Average cost update
1220 --ELSIF l_transaction_type_id = 82 THEN -- Inventory Lot Split
1221 --ELSIF l_transaction_type_id = 83 THEN -- Inventory Lot Merge
1222 --ELSIF l_transaction_type_id = 84 THEN -- Inventory Lot Translate
1223 --ELSIF l_transaction_type_id = 86 THEN -- Cost Group Transfer
1224 --ELSIF l_transaction_type_id = 87 THEN -- Container Pack
1225 --ELSIF l_transaction_type_id = 88 THEN -- Container Unpack
1226 --ELSIF l_transaction_type_id = 89 THEN -- Container Split
1227 --ELSIF l_transaction_type_id = 90 THEN -- WIP assembly scrap
1228 --ELSIF l_transaction_type_id = 91 THEN -- WIP return from scrap
1229 --ELSIF l_transaction_type_id = 92 THEN -- WIP estimated scrap
1230 ELSE
1231 l_inv_name := NULL;
1232 END IF;
1233
1234 RETURN l_inv_name;
1235 END get_inv_name;
1236 PROCEDURE Check_if_top_assembly(p_instance_id IN NUMBER,
1237 x_yes_top_assembly OUT NOCOPY BOOLEAN,
1238 x_return_status OUT NOCOPY VARCHAR2,
1239 x_error_message OUT NOCOPY VARCHAR2) IS
1240 CURSOR check_top_assembly(p_id IN NUMBER) IS
1241 SELECT 1
1242 FROM csi_ii_relationships ciir1
1243 WHERE ciir1.object_id = p_id
1244 AND ciir1.relationship_type_code = 'COMPONENT-OF'
1245 AND NOT EXISTS (select ciir2.subject_id
1246 from csi_ii_relationships ciir2
1247 where ciir2.relationship_type_code = 'COMPONENT-OF'
1248 and ciir2.subject_id = p_id);
1249
1250 l_dummy PLS_INTEGER;
1251 BEGIN
1252 x_return_status := FND_API.G_RET_STS_SUCCESS;
1253 x_yes_top_assembly := FALSE;
1254
1255 OPEN check_top_assembly(p_instance_id);
1256 FETCH check_top_assembly INTO l_dummy;
1257 CLOSE check_top_assembly;
1258
1259 IF l_dummy = 1
1260 THEN x_yes_top_assembly := TRUE;
1261 END IF;
1262
1263 EXCEPTION
1264 WHEN OTHERS THEN
1265 IF check_top_assembly%ISOPEN
1266 THEN CLOSE check_top_assembly;
1267 END IF;
1268 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
1269 fnd_message.set_token('ERR_MSG', sqlerrm);
1270 x_return_status := fnd_api.g_ret_sts_unexp_error;
1271 x_error_message := fnd_message.get;
1272 -- CSE_DEBUG_PUB.ADD('API CSE_UTIL_PKG.check_top_assembly others exception: ' || l_err_text);
1273 END check_if_top_assembly;
1274
1275 ------------------------------------------------------------------------------
1276 ---
1277 --- Added for Redeployment functionality.
1278 --- This procedure returns x_redeploy_flag as 'Y'
1279 --- If there exists a OUT-OF-SERVICE' transaction
1280 --- previous to the p_transaction_date (by default, it is SYSDATE
1281 ---
1282 ------------------------------------------------------------------------------
1283 PROCEDURE get_redeploy_flag(
1284 p_inventory_item_id IN NUMBER
1285 ,p_serial_number IN VARCHAR2
1286 ,p_transaction_date IN DATE
1287 ,x_redeploy_flag OUT NOCOPY VARCHAR2
1288 ,x_return_status OUT NOCOPY VARCHAR2
1289 ,x_error_message OUT NOCOPY VARCHAR2)
1290 IS
1291 l_out_of_sev NUMBER;
1292 l_proj_insev NUMBER;
1293 l_issue_hz NUMBER;
1294 l_misc_issue_hz NUMBER;
1295
1296 CURSOR get_redeploy_flag_cur
1297 IS
1298 SELECT 'Y' redeploy_flag
1299 FROM csi_transactions ct
1300 ,csi_item_instances_h ciih
1301 ,csi_item_instances cii
1302 WHERE ct.transaction_id = ciih.transaction_id
1303 AND ciih.instance_id = cii.instance_id
1304 AND cii.inventory_item_id = p_inventory_item_id
1305 AND cii.serial_number = p_serial_number
1306 AND ct.transaction_date < NVL(p_transaction_date, SYSDATE)
1307 AND ct.transaction_type_id IN (l_out_of_sev, l_proj_insev, l_issue_hz, l_misc_issue_hz) ;
1308
1309 BEGIN
1310 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
1311 x_redeploy_flag := 'N' ;
1312
1313 l_out_of_sev := get_txn_type_id('OUT_OF_SERVICE','CSE');
1314 l_proj_insev := get_txn_type_id('PROJECT_ITEM_IN_SERVICE','CSE');
1315 l_issue_hz := get_txn_type_id('ISSUE_TO_HZ_LOC','INV');
1316 l_misc_issue_hz := get_txn_type_id('MISC_ISSUE_HZ_LOC','INV');
1317
1318 OPEN get_redeploy_flag_cur ;
1319 FETCH get_redeploy_flag_cur INTO x_redeploy_flag ;
1320 CLOSE get_redeploy_flag_cur ;
1321
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 x_return_status := fnd_api.G_RET_STS_ERROR ;
1325 x_error_message := SQLERRM ;
1326 END get_redeploy_flag ;
1327
1328
1329 ------------------------------------------------------------------------------
1330
1331 PROCEDURE get_inst_n_comp_dtls(
1332 p_instance_id IN NUMBER
1333 ,p_transaction_id IN NUMBER
1334 ,p_transaction_date IN DATE
1335 ,x_inst_dtls_tbl OUT NOCOPY csi_datastructures_pub.instance_header_tbl
1336 ,x_return_status OUT NOCOPY VARCHAR2
1337 ,x_error_message OUT NOCOPY VARCHAR2)
1338 IS
1339
1340 -------------------------------------------------------------------------------
1341 -- Logic for get_inst_n_comp_dtls
1342 -- Get all the Components of the given instance
1343 -- For each component and the instance itself get the instance details
1344 -- Populate the out table
1345 -------------------------------------------------------------------------------
1346 l_api_version NUMBER DEFAULT 1.0;
1347 l_commit VARCHAR2(1) DEFAULT FND_API.G_FALSE;
1348 l_init_msg_list VARCHAR2(1) DEFAULT FND_API.G_TRUE;
1349 l_validation_level NUMBER := fnd_api.g_valid_level_full;
1350 l_instance_header_tbl_out csi_datastructures_pub.instance_header_tbl;
1351 l_resolve_id_columns VARCHAR2(1) DEFAULT FND_API.G_FALSE;
1352 l_msg_index NUMBER;
1353 l_msg_data VARCHAR2(2000);
1354 l_msg_count NUMBER;
1355 l_return_status VARCHAR2(1);
1356 l_error_message VARCHAR2(2000);
1357 l_instance_rec csi_datastructures_pub.instance_header_rec ;
1358 l_party_header_tbl csi_datastructures_pub.party_header_tbl ;
1359 l_account_header_tbl csi_datastructures_pub.party_account_header_tbl ;
1360 l_org_header_tbl csi_datastructures_pub.org_units_header_tbl ;
1361 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl ;
1362 l_ext_attrib_tbl csi_datastructures_pub.extend_attrib_values_tbl ;
1363 l_ext_attrib_def_tbl csi_datastructures_pub.extend_attrib_tbl ;
1364 l_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
1365 l_relationship_query_rec csi_datastructures_pub.relationship_query_rec ;
1366 l_ii_relationship_tbl csi_datastructures_pub.ii_relationship_tbl ;
1367 e_error EXCEPTION ;
1368 i INTEGER ;
1369 j INTEGER ;
1370 k INTEGER ;
1371
1372 BEGIN
1373 l_instance_rec := NULL ;
1374 l_instance_rec.instance_id := p_instance_id ;
1375 i:= 0;
1376 j := 0;
1377 k := 0;
1378 cse_util_pkg.write_log('Call get item instance details');
1379 csi_item_instance_pub.get_item_instance_details(
1380 p_api_version => l_api_version,
1381 p_commit => l_commit,
1382 p_init_msg_list => l_init_msg_list,
1383 p_validation_level => l_Validation_Level,
1384 p_instance_rec => l_instance_rec,
1385 p_get_parties => NULL,
1386 p_party_header_tbl => l_party_header_tbl,
1387 p_get_accounts => NULL,
1388 p_account_header_tbl => l_account_header_tbl,
1389 p_get_org_assignments => NULL,
1390 p_org_header_tbl => l_org_header_tbl,
1391 p_get_pricing_attribs => NULL,
1392 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1393 p_get_ext_attribs => NULL,
1394 p_ext_attrib_tbl => l_ext_attrib_tbl,
1395 p_ext_attrib_def_tbl => l_ext_attrib_def_tbl,
1396 p_get_asset_assignments => NULL,
1397 p_asset_header_tbl => l_asset_header_tbl,
1398 p_resolve_id_columns => l_resolve_id_columns,
1399 p_time_stamp => p_transaction_date ,
1400 x_return_status => l_return_status,
1401 x_msg_count => l_Msg_Count,
1402 x_msg_data => l_Msg_Data );
1403
1404 cse_util_pkg.write_log('Return Status : '|| l_return_status);
1405
1406
1407 IF l_return_status = fnd_api.g_ret_sts_error
1408 THEN
1409 l_msg_index := 1;
1410 l_error_message:=l_msg_data;
1411 WHILE l_msg_count > 0
1412 LOOP
1413 l_error_message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE)||l_error_message;
1414 l_msg_index := l_msg_index + 1;
1415 l_msg_count := l_msg_count - 1;
1416 END LOOP;
1417 RAISE e_error;
1418 END IF ;
1419 ---Populate Instnace Details table with objcet(top assembly) instance details
1420 cse_util_pkg.write_log('Instance Rec after calling get inst dtls :'||l_instance_rec.instance_id);
1421 i:= 1;
1422 x_inst_dtls_tbl(i) := l_instance_rec ;
1423
1424 cse_util_pkg.write_log('Count :' || x_inst_dtls_tbl.COUNT);
1425 l_relationship_query_rec.object_id := p_instance_id ;
1426 l_relationship_query_rec.relationship_type_code := 'COMPONENT-OF';
1427
1428 csi_ii_relationships_pub.get_relationships(
1429 p_api_version => l_api_version ,
1430 p_commit => l_commit ,
1431 p_init_msg_list => l_init_msg_list ,
1432 p_validation_level => l_validation_level ,
1433 p_relationship_query_rec => l_relationship_query_rec ,
1434 p_depth => NULL,
1435 p_time_stamp => p_transaction_date,
1436 p_active_relationship_only => fnd_api.g_true,
1437 x_relationship_tbl => l_ii_relationship_tbl ,
1438 x_return_status => l_return_status ,
1439 x_msg_count => l_msg_count ,
1440 x_msg_data => l_msg_data);
1441
1442 IF l_return_status = fnd_api.g_ret_sts_error
1443 THEN
1444 l_msg_index := 1;
1445 l_error_message:=l_msg_data;
1446 WHILE l_msg_count > 0
1447 LOOP
1448 l_error_message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE)||l_error_message;
1449 l_msg_index := l_msg_index + 1;
1450 l_msg_count := l_msg_count - 1;
1451 END LOOP;
1452 RAISE e_error;
1453 END IF ;
1454
1455 IF l_ii_relationship_tbl.COUNT > 0
1456 THEN
1457 FOR j IN l_ii_relationship_tbl.FIRST .. l_ii_relationship_tbl.LAST
1458 LOOP
1459 i := i+1 ;
1460 l_instance_rec := NULL ;
1461 l_instance_rec.instance_id := l_ii_relationship_tbl(j).subject_id ;
1462
1463 csi_item_instance_pub.get_item_instance_details(
1464 p_api_version => l_api_version,
1465 p_commit => l_commit,
1466 p_init_msg_list => l_init_msg_list,
1467 p_validation_level => l_Validation_Level,
1468 p_instance_rec => l_instance_rec,
1469 p_get_parties => NULL,
1470 p_party_header_tbl => l_party_header_tbl,
1471 p_get_accounts => NULL,
1472 p_account_header_tbl => l_account_header_tbl,
1473 p_get_org_assignments => NULL,
1474 p_org_header_tbl => l_org_header_tbl,
1475 p_get_pricing_attribs => NULL,
1476 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1477 p_get_ext_attribs => NULL,
1478 p_ext_attrib_tbl => l_ext_attrib_tbl,
1479 p_ext_attrib_def_tbl => l_ext_attrib_def_tbl,
1480 p_get_asset_assignments => NULL,
1481 p_asset_header_tbl => l_asset_header_tbl,
1482 p_resolve_id_columns => l_resolve_id_columns,
1483 p_time_stamp => p_transaction_date ,
1484 x_return_status => l_return_status,
1485 x_msg_count => l_Msg_Count,
1486 x_msg_data => l_Msg_Data );
1487
1488 IF l_return_status = fnd_api.g_ret_sts_error
1489 THEN
1490 l_msg_index := 1;
1491 l_error_message:=l_msg_data;
1492 WHILE l_msg_count > 0
1493 LOOP
1494 l_error_message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE)||l_error_message;
1495 l_msg_index := l_msg_index + 1;
1496 l_msg_count := l_msg_count - 1;
1497 END LOOP;
1498 RAISE e_error;
1499 END IF ;
1500 ---populate the instance distance table.
1501 x_inst_dtls_tbl(i) := l_instance_rec ;
1502
1503 END LOOP ;-- l_ii_relationship_tbl.FIRST .. l_ii_relationship_tbl.LAST
1504 END IF ; --l_ii_relationship_tbl.COUNT > 0
1505
1506 FOR k IN x_inst_dtls_tbl.FIRST .. x_inst_dtls_tbl.LAST
1507 LOOP
1508 cse_util_pkg.write_log(' Instance ID : '|| x_inst_dtls_tbl(k).instance_id);
1509 cse_util_pkg.write_log(' Item ID : '|| x_inst_dtls_tbl(k).inventory_item_id);
1510 cse_util_pkg.write_log(' Location ID : ' || x_inst_dtls_tbl(k).location_id);
1511 cse_util_pkg.write_log(' Location Type Code : '|| x_inst_dtls_tbl(k).location_type_code);
1512 END LOOP ;
1513
1514 EXCEPTION
1515 WHEN e_error THEN
1516 x_return_status := fnd_api.G_RET_STS_ERROR ;
1517 x_error_message := l_error_message ;
1518 cse_util_pkg.write_log('In error :'||substr(x_error_message,1,200));
1519 WHEN OTHERS THEN
1520 x_return_status := fnd_api.G_RET_STS_ERROR ;
1521 x_error_message := SQLERRM ;
1522 cse_util_pkg.write_log('In other exception :'||x_error_message);
1523 END get_inst_n_comp_dtls ;
1524
1525
1526
1527 ------------------------------------------------------------------------------
1528 FUNCTION dump_error_stack RETURN varchar2
1529 IS
1530 l_msg_count number;
1531 l_msg_data varchar2(2000);
1532 l_msg_index_out number;
1533 x_msg_data varchar2(4000);
1534 BEGIN
1535 x_msg_data := null;
1536 fnd_msg_pub.count_and_get(
1537 p_count => l_msg_count,
1538 p_data => l_msg_data);
1539
1540 FOR l_ind IN 1..l_msg_count
1541 LOOP
1542 fnd_msg_pub.get(
1543 p_msg_index => l_ind,
1544 p_encoded => fnd_api.g_false,
1545 p_data => l_msg_data,
1546 p_msg_index_out => l_msg_index_out);
1547
1548 x_msg_data := ltrim(x_msg_data||' '||l_msg_data);
1549 IF length(x_msg_data) > 1999 THEN
1550 x_msg_data := substr(x_msg_data, 1, 1999);
1551 exit;
1552 END IF;
1553 END LOOP;
1554 RETURN x_msg_data;
1555 EXCEPTION
1556 when others then
1557 RETURN x_msg_data;
1558 END dump_error_stack;
1559
1560 PROCEDURE set_debug IS
1561 l_file VARCHAR2(500);
1562 l_sysdate DATE := sysdate;
1563 l_cse varchar2(3) := 'cse';
1564 BEGIN
1565 IF l_debug = 'Y' THEN
1566 cse_debug_pub.g_dir := nvl(fnd_profile.value('cse_debug_log_directory'), '/tmp');
1567 cse_debug_pub.g_file := NULL;
1568 l_file := cse_debug_pub.set_debug_file(l_cse||'.'||to_char(l_sysdate,'DDMONYYYY')||'.dbg');
1569 cse_debug_pub.debug_on;
1570 END IF;
1571 EXCEPTION
1572 WHEN others THEN
1573 null;
1574 END set_debug;
1575
1576
1577 ------------------------------------------------------------------------------
1578
1579 END CSE_UTIL_PKG;