[Home] [Help]
PACKAGE BODY: APPS.OKL_EQUIPMENT_EXCHANGE_PVT
Source
1 package body okl_equipment_exchange_pvt AS
2 /* $Header: OKLREQXB.pls 120.5.12010000.2 2009/06/14 08:35:56 racheruv ship $ */
3 -------------------------------------------------------------------------------------------------
4 -- GLOBAL MESSAGE CONSTANTS
5 -------------------------------------------------------------------------------------------------
6 G_FND_APP CONSTANT VARCHAR2(200) := OKL_API.G_FND_APP;
7 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
8 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_PARENT_TABLE_TOKEN;
9 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_CHILD_TABLE_TOKEN;
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_UNEXPECTED_ERROR';
11 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
13 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'NO_PARENT_RECORD';
14 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'REQUIRED_VALUE';
15
16 ------------------------------------------------------------------------------------
17 -- GLOBAL EXCEPTION
18 ------------------------------------------------------------------------------------
19 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
20 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
21 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
22 G_API_VERSION CONSTANT NUMBER := 1.0;
23 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
24
25 -----------------------------------------------------------------------------------
26 -- GLOBAL VARIABLES
27 -----------------------------------------------------------------------------------
28 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_EQUIPMENT_EXCHANGE';
29 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
30
31
32
33
34 SUBTYPE instance_rec IS CSI_DATASTRUCTURES_PUB.instance_rec;
35 SUBTYPE extend_attrib_values_tbl IS CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl;
36 SUBTYPE party_tbl IS CSI_DATASTRUCTURES_PUB.party_tbl;
37 SUBTYPE account_tbl IS CSI_DATASTRUCTURES_PUB.party_account_tbl;
38 SUBTYPE pricing_attribs_tbl IS CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl;
39 SUBTYPE organization_units_tbl IS CSI_DATASTRUCTURES_PUB.organization_units_tbl;
40 SUBTYPE instance_asset_tbl IS CSI_DATASTRUCTURES_PUB.instance_asset_tbl;
41 SUBTYPE transaction_rec IS CSI_DATASTRUCTURES_PUB.transaction_rec;
42 SUBTYPE id_tbl IS CSI_DATASTRUCTURES_PUB.id_tbl;
43 SUBTYPE cplv_rec_type IS OKL_CREATE_KLE_PVT.cplv_rec_type;
44
45
46 --l_instance_rec instance_rec;
47 l_ext_attrib_values_tbl extend_attrib_values_tbl;
48 l_party_tbl party_tbl;
49 l_account_tbl account_tbl;
50 l_pricing_attrib_tbl pricing_attribs_tbl;
51 l_org_assignments_tbl organization_units_tbl;
52 l_asset_assignment_tbl instance_asset_tbl;
53 l_txn_rec transaction_rec;
54 l_instance_id_lst id_tbl;
55
56
57
58
59 FUNCTION GET_TAS_HDR_REC
60 (p_thpv_tbl IN thpv_tbl_type
61 ,p_no_data_found OUT NOCOPY BOOLEAN
62 ) RETURN thpv_tbl_type aS
63 CURSOR okl_tasv_pk_csr (p_id IN NUMBER) IS
64 SELECT
65 ID,
66 OBJECT_VERSION_NUMBER,
67 ICA_ID,
68 ATTRIBUTE_CATEGORY,
69 ATTRIBUTE1,
70 ATTRIBUTE2,
71 ATTRIBUTE3,
72 ATTRIBUTE4,
73 ATTRIBUTE5,
74 ATTRIBUTE6,
75 ATTRIBUTE7,
76 ATTRIBUTE8,
77 ATTRIBUTE9,
78 ATTRIBUTE10,
79 ATTRIBUTE11,
80 ATTRIBUTE12,
81 ATTRIBUTE13,
82 ATTRIBUTE14,
83 ATTRIBUTE15,
84 TAS_TYPE,
85 CREATED_BY,
86 CREATION_DATE,
87 LAST_UPDATED_BY,
88 LAST_UPDATE_DATE,
89 LAST_UPDATE_LOGIN,
90 TSU_CODE,
91 TRY_ID,
92 DATE_TRANS_OCCURRED
93 ,TRANS_NUMBER
94 ,COMMENTS
95 FROM OKL_TRX_ASSETS
96 WHERE OKL_TRX_ASSETS.id = p_id;
97 l_okl_tasv_pk okl_tasv_pk_csr%ROWTYPE;
98 l_thpv_tbl thpv_tbl_type;
99 BEGIN
100 p_no_data_found := TRUE;
101 -- Get current database values
102 OPEN okl_tasv_pk_csr (p_thpv_tbl(1).id);
103 FETCH okl_tasv_pk_csr INTO
104 l_thpv_tbl(1).ID,
105 l_thpv_tbl(1).OBJECT_VERSION_NUMBER,
106 l_thpv_tbl(1).ICA_ID,
107 l_thpv_tbl(1).ATTRIBUTE_CATEGORY,
108 l_thpv_tbl(1).ATTRIBUTE1,
109 l_thpv_tbl(1).ATTRIBUTE2,
110 l_thpv_tbl(1).ATTRIBUTE3,
111 l_thpv_tbl(1).ATTRIBUTE4,
112 l_thpv_tbl(1).ATTRIBUTE5,
113 l_thpv_tbl(1).ATTRIBUTE6,
114 l_thpv_tbl(1).ATTRIBUTE7,
115 l_thpv_tbl(1).ATTRIBUTE8,
116 l_thpv_tbl(1).ATTRIBUTE9,
117 l_thpv_tbl(1).ATTRIBUTE10,
118 l_thpv_tbl(1).ATTRIBUTE11,
119 l_thpv_tbl(1).ATTRIBUTE12,
120 l_thpv_tbl(1).ATTRIBUTE13,
121 l_thpv_tbl(1).ATTRIBUTE14,
122 l_thpv_tbl(1).ATTRIBUTE15,
123 l_thpv_tbl(1).TAS_TYPE,
124 l_thpv_tbl(1).CREATED_BY,
125 l_thpv_tbl(1).CREATION_DATE,
126 l_thpv_tbl(1).LAST_UPDATED_BY,
127 l_thpv_tbl(1).LAST_UPDATE_DATE,
128 l_thpv_tbl(1).LAST_UPDATE_LOGIN,
129 l_thpv_tbl(1).TSU_CODE,
130 l_thpv_tbl(1).TRY_ID,
131 l_thpv_tbl(1).DATE_TRANS_OCCURRED,
132 l_thpv_tbl(1).TRANS_NUMBER,
133 l_thpv_tbl(1).COMMENTS;
134 p_no_data_found := okl_tasv_pk_csr%NOTFOUND;
135 CLOSE okl_tasv_pk_csr;
136 RETURN(l_thpv_tbl);
137 END GET_TAS_HDR_REC;
138
139 FUNCTION get_status
140 (p_status_code IN VARCHAR2)
141 RETURN VARCHAR2 aS
142 CURSOR okl_status_lkp_csr(p_st_code IN VARCHAR2) IS
143 SELECT MEANING
144 FROM FND_LOOKUPS
145 WHERE LOOKUP_TYPE='OKL_TRANSACTION_STATUS'
146 AND LOOKUP_CODE=p_st_Code;
147 l_status_meaning VARCHAR2(80);
148 BEGIN
149 OPEN okl_status_lkp_csr(p_status_code);
150 FETCH okl_status_lkp_csr INTO l_status_meaning;
151 CLOSE okl_status_lkp_csr;
152 RETURN l_status_meaning;
153 END get_status;
154
155 FUNCTION get_instance_id (
156 p_instance_number IN VARCHAR2)
157 RETURN NUMBER AS
158
159 CURSOR okl_inst_id_csr(l_instance_number IN VARCHAR2)
160 IS
161 SELECT instance_id
162 FROM okx_install_items_v
163 where instance_number = l_instance_number;
164
165 l_instance_id NUMBER;
166
167 BEGIN
168
169 OPEN okl_inst_id_csr(p_instance_number);
170 FETCH okl_inst_id_csr INTO l_instance_id;
171 CLOSE okl_inst_id_csr;
172
173 RETURN l_instance_id;
174
175 END get_instance_id;
176
177 ---------------------------------------------------------------------------
178 -- FUNCTION get_rec for: OKL_TXL_ASSETS_V
179 ---------------------------------------------------------------------------
180 FUNCTION get_tal_rec (
181 p_talv_tbl IN talv_tbl_type,
182 x_no_data_found OUT NOCOPY BOOLEAN
183 ) RETURN talv_tbl_type aS
184 CURSOR okl_talv_pk_csr (p_tas_id IN NUMBER
185 ,p_tal_type IN VARCHAR2) IS
186 SELECT ID,
187 OBJECT_VERSION_NUMBER,
188 SFWT_FLAG,
189 TAS_ID,
190 ILO_ID,
191 ILO_ID_OLD,
192 IAY_ID,
193 IAY_ID_NEW,
194 KLE_ID,
195 DNZ_KHR_ID,
196 LINE_NUMBER,
197 ORG_ID,
198 TAL_TYPE,
199 ASSET_NUMBER,
200 DESCRIPTION,
201 FA_LOCATION_ID,
202 ORIGINAL_COST,
203 CURRENT_UNITS,
204 MANUFACTURER_NAME,
205 YEAR_MANUFACTURED,
206 SUPPLIER_ID,
207 USED_ASSET_YN,
208 TAG_NUMBER,
209 MODEL_NUMBER,
210 CORPORATE_BOOK,
211 DATE_PURCHASED,
212 DATE_DELIVERY,
213 IN_SERVICE_DATE,
214 LIFE_IN_MONTHS,
215 DEPRECIATION_ID,
216 DEPRECIATION_COST,
217 DEPRN_METHOD,
218 DEPRN_RATE,
219 SALVAGE_VALUE,
220 PERCENT_SALVAGE_VALUE,
221 ATTRIBUTE_CATEGORY,
222 ATTRIBUTE1,
223 ATTRIBUTE2,
224 ATTRIBUTE3,
225 ATTRIBUTE4,
226 ATTRIBUTE5,
227 ATTRIBUTE6,
228 ATTRIBUTE7,
229 ATTRIBUTE8,
230 ATTRIBUTE9,
231 ATTRIBUTE10,
232 ATTRIBUTE11,
233 ATTRIBUTE12,
234 ATTRIBUTE13,
235 ATTRIBUTE14,
236 ATTRIBUTE15,
237 CREATED_BY,
238 CREATION_DATE,
239 LAST_UPDATED_BY,
240 LAST_UPDATE_DATE,
241 LAST_UPDATE_LOGIN,
242 DEPRECIATE_YN,
243 HOLD_PERIOD_DAYS,
244 OLD_SALVAGE_VALUE,
245 NEW_RESIDUAL_VALUE,
246 OLD_RESIDUAL_VALUE,
247 UNITS_RETIRED,
248 COST_RETIRED,
249 SALE_PROCEEDS,
250 REMOVAL_COST,
251 DNZ_ASSET_ID
252 ,DATE_DUE
253 FROM Okl_Txl_Assets_V
254 WHERE okl_txl_assets_v.tas_id = p_tas_id
255 AND okl_txl_assets_v.tal_type = p_tal_type;
256 l_okl_talv_pk okl_talv_pk_csr%ROWTYPE;
257 l_talv_tbl talv_tbl_type;
258 BEGIN
259 x_no_data_found := TRUE;
260 -- Get current database values
261 OPEN okl_talv_pk_csr (p_talv_tbl(1).tas_id,p_talv_tbl(1).tal_type);
262 FETCH okl_talv_pk_csr INTO
263 l_talv_tbl(1).ID,
264 l_talv_tbl(1).OBJECT_VERSION_NUMBER,
265 l_talv_tbl(1).SFWT_FLAG,
266 l_talv_tbl(1).TAS_ID,
267 l_talv_tbl(1).ILO_ID,
268 l_talv_tbl(1).ILO_ID_OLD,
269 l_talv_tbl(1).IAY_ID,
270 l_talv_tbl(1).IAY_ID_NEW,
271 l_talv_tbl(1).KLE_ID,
272 l_talv_tbl(1).DNZ_KHR_ID,
273 l_talv_tbl(1).LINE_NUMBER,
274 l_talv_tbl(1).ORG_ID,
275 l_talv_tbl(1).TAL_TYPE,
276 l_talv_tbl(1).ASSET_NUMBER,
277 l_talv_tbl(1).DESCRIPTION,
278 l_talv_tbl(1).FA_LOCATION_ID,
279 l_talv_tbl(1).ORIGINAL_COST,
280 l_talv_tbl(1).CURRENT_UNITS,
281 l_talv_tbl(1).MANUFACTURER_NAME,
282 l_talv_tbl(1).YEAR_MANUFACTURED,
283 l_talv_tbl(1).SUPPLIER_ID,
284 l_talv_tbl(1).USED_ASSET_YN,
285 l_talv_tbl(1).TAG_NUMBER,
286 l_talv_tbl(1).MODEL_NUMBER,
287 l_talv_tbl(1).CORPORATE_BOOK,
288 l_talv_tbl(1).DATE_PURCHASED,
289 l_talv_tbl(1).DATE_DELIVERY,
290 l_talv_tbl(1).IN_SERVICE_DATE,
291 l_talv_tbl(1).LIFE_IN_MONTHS,
292 l_talv_tbl(1).DEPRECIATION_ID,
293 l_talv_tbl(1).DEPRECIATION_COST,
294 l_talv_tbl(1).DEPRN_METHOD,
295 l_talv_tbl(1).DEPRN_RATE,
296 l_talv_tbl(1).SALVAGE_VALUE,
297 l_talv_tbl(1).PERCENT_SALVAGE_VALUE,
298 l_talv_tbl(1).ATTRIBUTE_CATEGORY,
299 l_talv_tbl(1).ATTRIBUTE1,
300 l_talv_tbl(1).ATTRIBUTE2,
301 l_talv_tbl(1).ATTRIBUTE3,
302 l_talv_tbl(1).ATTRIBUTE4,
303 l_talv_tbl(1).ATTRIBUTE5,
304 l_talv_tbl(1).ATTRIBUTE6,
305 l_talv_tbl(1).ATTRIBUTE7,
306 l_talv_tbl(1).ATTRIBUTE8,
307 l_talv_tbl(1).ATTRIBUTE9,
308 l_talv_tbl(1).ATTRIBUTE10,
309 l_talv_tbl(1).ATTRIBUTE11,
310 l_talv_tbl(1).ATTRIBUTE12,
311 l_talv_tbl(1).ATTRIBUTE13,
312 l_talv_tbl(1).ATTRIBUTE14,
313 l_talv_tbl(1).ATTRIBUTE15,
314 l_talv_tbl(1).CREATED_BY,
315 l_talv_tbl(1).CREATION_DATE,
316 l_talv_tbl(1).LAST_UPDATED_BY,
317 l_talv_tbl(1).LAST_UPDATE_DATE,
318 l_talv_tbl(1).LAST_UPDATE_LOGIN,
319 l_talv_tbl(1).DEPRECIATE_YN,
320 l_talv_tbl(1).HOLD_PERIOD_DAYS,
321 l_talv_tbl(1).OLD_SALVAGE_VALUE,
322 l_talv_tbl(1).NEW_RESIDUAL_VALUE,
323 l_talv_tbl(1).OLD_RESIDUAL_VALUE,
324 l_talv_tbl(1).UNITS_RETIRED,
325 l_talv_tbl(1).COST_RETIRED,
326 l_talv_tbl(1).SALE_PROCEEDS,
327 l_talv_tbl(1).REMOVAL_COST,
328 l_talv_tbl(1).DNZ_ASSET_ID,
329 l_talv_tbl(1).DATE_DUE;
330 x_no_data_found := okl_talv_pk_csr%NOTFOUND;
331 CLOSE okl_talv_pk_csr;
332 RETURN(l_talv_tbl);
333 END get_tal_rec;
334
335 FUNCTION get_vendor_name
336 (p_vendor_id IN VARCHAR2)
337 RETURN VARCHAR2 aS
338 CURSOR okl_vendor_lkp_csr(p_vendor_id IN VARCHAR2) IS
339 SELECT NAME
340 FROM OKX_VENDORS_V
341 WHERE ID1=p_vendor_id;
342 l_vendor_name VARCHAR2(240);
343 BEGIN
344 OPEN okl_vendor_lkp_csr(p_vendor_id);
345 FETCH okl_vendor_lkp_csr INTO l_vendor_name;
346 CLOSE okl_vendor_lkp_csr;
347 RETURN l_vendor_name;
348 END get_vendor_name;
349
350 FUNCTION get_item_rec (
351 p_itiv_tbl IN itiv_tbl_type,
352 x_no_data_found OUT NOCOPY BOOLEAN
353 ) RETURN itiv_tbl_type aS
354 CURSOR okl_itiv_pk_csr (p_id IN NUMBER,p_tal_type IN VARCHAR2) IS
355 SELECT ID,
356 OBJECT_VERSION_NUMBER,
357 TAS_ID,
358 TAL_ID,
359 KLE_ID,
360 TAL_TYPE,
361 LINE_NUMBER,
362 INSTANCE_NUMBER_IB,
363 OBJECT_ID1_NEW,
364 OBJECT_ID2_NEW,
365 JTOT_OBJECT_CODE_NEW,
366 OBJECT_ID1_OLD,
367 OBJECT_ID2_OLD,
368 JTOT_OBJECT_CODE_OLD,
369 INVENTORY_ORG_ID,
370 SERIAL_NUMBER,
371 MFG_SERIAL_NUMBER_YN,
372 INVENTORY_ITEM_ID,
373 INV_MASTER_ORG_ID,
374 ATTRIBUTE_CATEGORY,
375 ATTRIBUTE1,
376 ATTRIBUTE2,
377 ATTRIBUTE3,
378 ATTRIBUTE4,
379 ATTRIBUTE5,
380 ATTRIBUTE6,
381 ATTRIBUTE7,
382 ATTRIBUTE8,
383 ATTRIBUTE9,
384 ATTRIBUTE10,
385 ATTRIBUTE11,
386 ATTRIBUTE12,
387 ATTRIBUTE13,
388 ATTRIBUTE14,
389 ATTRIBUTE15,
390 CREATED_BY,
391 CREATION_DATE,
392 LAST_UPDATED_BY,
393 LAST_UPDATE_DATE,
394 LAST_UPDATE_LOGIN
395 FROM OKL_TXL_ITM_INSTS iti
396 WHERE iti.tas_id = p_id
397 AND iti.tal_type = p_tal_type;
398 l_okl_itiv_pk okl_itiv_pk_csr%ROWTYPE;
399 l_itiv_tbl itiv_tbl_type;
400 BEGIN
401 x_no_data_found := TRUE;
402 -- Get current database values
403 OPEN okl_itiv_pk_csr (p_itiv_tbl(1).tas_id,p_itiv_tbl(1).tal_type);
404 FETCH okl_itiv_pk_csr INTO
405 l_itiv_tbl(1).ID,
406 l_itiv_tbl(1).OBJECT_VERSION_NUMBER,
407 l_itiv_tbl(1).TAS_ID,
408 l_itiv_tbl(1).TAL_ID,
409 l_itiv_tbl(1).KLE_ID,
410 l_itiv_tbl(1).TAL_TYPE,
411 l_itiv_tbl(1).LINE_NUMBER,
412 l_itiv_tbl(1).INSTANCE_NUMBER_IB,
413 l_itiv_tbl(1).OBJECT_ID1_NEW,
414 l_itiv_tbl(1).OBJECT_ID2_NEW,
415 l_itiv_tbl(1).JTOT_OBJECT_CODE_NEW,
416 l_itiv_tbl(1).OBJECT_ID1_OLD,
417 l_itiv_tbl(1).OBJECT_ID2_OLD,
418 l_itiv_tbl(1).JTOT_OBJECT_CODE_OLD,
419 l_itiv_tbl(1).INVENTORY_ORG_ID,
420 l_itiv_tbl(1).SERIAL_NUMBER,
421 l_itiv_tbl(1).MFG_SERIAL_NUMBER_YN,
422 l_itiv_tbl(1).INVENTORY_ITEM_ID,
423 l_itiv_tbl(1).INV_MASTER_ORG_ID,
424 l_itiv_tbl(1).ATTRIBUTE_CATEGORY,
425 l_itiv_tbl(1).ATTRIBUTE1,
426 l_itiv_tbl(1).ATTRIBUTE2,
427 l_itiv_tbl(1).ATTRIBUTE3,
428 l_itiv_tbl(1).ATTRIBUTE4,
429 l_itiv_tbl(1).ATTRIBUTE5,
430 l_itiv_tbl(1).ATTRIBUTE6,
431 l_itiv_tbl(1).ATTRIBUTE7,
432 l_itiv_tbl(1).ATTRIBUTE8,
433 l_itiv_tbl(1).ATTRIBUTE9,
434 l_itiv_tbl(1).ATTRIBUTE10,
435 l_itiv_tbl(1).ATTRIBUTE11,
436 l_itiv_tbl(1).ATTRIBUTE12,
437 l_itiv_tbl(1).ATTRIBUTE13,
438 l_itiv_tbl(1).ATTRIBUTE14,
439 l_itiv_tbl(1).ATTRIBUTE15,
440 l_itiv_tbl(1).CREATED_BY,
441 l_itiv_tbl(1).CREATION_DATE,
442 l_itiv_tbl(1).LAST_UPDATED_BY,
443 l_itiv_tbl(1).LAST_UPDATE_DATE,
444 l_itiv_tbl(1).LAST_UPDATE_LOGIN;
445 x_no_data_found := okl_itiv_pk_csr%NOTFOUND;
446 CLOSE okl_itiv_pk_csr;
447 RETURN(l_itiv_tbl);
448 END get_item_rec;
449
450 FUNCTION get_exchange_type
451 (p_tas_id IN NUMBER) RETURN VARCHAR2
452 aS
453 l_exchange_type VARCHAR2(60);
454
455 CURSOR c_exch_type IS
456 SELECT TAS_TYPE
457 FROM OKL_TRX_ASSETS
458 WHERE ID=p_tas_id;
459 BEGIN
460
461 OPEN c_exch_type;
462 FETCH c_exch_type into l_exchange_type;
463 CLOSE c_exch_type;
464 RETURN l_exchange_type;
465 END get_exchange_type;
466
467 -----------------------------------------------------------------------------
468 --Start of comments
469 --
470 --Procedure Name : get_trx_rec
471 --Purpose : Gets source transaction record for IB interface
472 --Modification History :
473 --15-Jun-2001 ashish.singh Created
474 --Notes : Assigns values to transaction_type_id and source_line_ref_id
475 --End of Comments
476 ------------------------------------------------------------------------------
477 PROCEDURE get_trx_rec
478 (p_api_version IN NUMBER,
479 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
480 x_return_status OUT NOCOPY VARCHAR2,
481 x_msg_count OUT NOCOPY NUMBER,
482 x_msg_data OUT NOCOPY VARCHAR2,
483 p_cle_id IN NUMBER,
484 p_transaction_type IN VARCHAR2,
485 x_trx_rec OUT NOCOPY transaction_rec) is
486
487 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
488 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRX_REC';
489 l_api_version CONSTANT NUMBER := 1.0;
490
491 --Following cursor assumes that a transaction type called
492 --'OKL LINE ACTIVATION' and 'OKL SPLIT ASSET' will be seeded in IB
493 -- Bug# 8459840 - Cursor changed to retrieve from base tables
494 -- commenting the below, added the changed cursor.
495 /*
496 Cursor okl_trx_type_curs(p_transaction_type IN VARCHAR2)is
497 select transaction_type_id
498 from CS_TRANSACTION_TYPES_V
499 where Name = p_transaction_type;
500 */
501 -- Note: Not using Name column in tt as it can be null.
502 Cursor okl_trx_type_curs(p_transaction_type IN VARCHAR2)is
503 SELECT tt.transaction_type_id
504 FROM cs_transaction_types_b tt,
505 cs_transaction_types_tl ttl
506 WHERE tt.transaction_type_id = ttl.transaction_type_id
507 AND ttl.language = 'US'
508 AND ttl.NAME = p_transaction_type;
509 -- end bug 8459840
510
511 l_trx_type_id NUMBER;
512 Begin
513 -- Bug# 8459840 - Start actvity
514 x_return_status := OKL_API.START_ACTIVITY(
515 l_api_name
516 ,p_init_msg_list
517 ,'_PVT'
518 ,x_return_status);
519 --Check if activity started successfully
520 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
521 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
522 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
523 RAISE OKL_API.G_EXCEPTION_ERROR;
524 END IF;
525 -- end bug 8459840
526
527 open okl_trx_type_curs(p_transaction_type);
528 Fetch okl_trx_type_curs
529 into l_trx_type_id;
530 If okl_trx_type_curs%NotFound Then
531 --OKL LINE ACTIVATION not seeded as a source transaction in IB
532 Raise OKL_API.G_EXCEPTION_ERROR;
533 End If;
534 close okl_trx_type_curs;
535 --Assign transaction Type id to seeded value in cs_lookups
536 x_trx_rec.transaction_type_id := l_trx_type_id;
537 --Assign Source Line Ref id to contract line id of IB instance line
538 x_trx_rec.source_line_ref_id := p_cle_id;
539 x_trx_rec.transaction_date := sysdate;
540 x_trx_rec.source_transaction_date := sysdate;
541 Exception
542 When OKL_API.G_EXCEPTION_ERROR Then
543 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
544 (
545 l_api_name,
546 G_PKG_NAME,
547 'OKL_API.G_RET_STS_ERROR',
548 x_msg_count,
549 x_msg_data,
550 '_PVT'
551 );
552 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
553 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
554 (
555 l_api_name,
556 G_PKG_NAME,
557 'OKL_API.G_RET_STS_UNEXP_ERROR',
558 x_msg_count,
559 x_msg_data,
560 '_PVT'
561 );
562 WHEN OTHERS THEN
563 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
564 (
565 l_api_name,
566 G_PKG_NAME,
567 'OTHERS',
568 x_msg_count,
569 x_msg_data,
570 '_PVT'
571 );
572 END get_trx_rec;
573
574
575 ---------------------------------------------------------------------------------------------
576
577
578 PROCEDURE init_ib_rec(l_instance_rec OUT NOCOPY instance_rec)
579 AS
580 BEGIN
581 l_instance_rec.INSTANCE_ID := OKL_API.G_MISS_NUM;
582 l_instance_rec.INSTANCE_NUMBER := OKL_API.G_MISS_CHAR;
583 l_instance_rec.EXTERNAL_REFERENCE := OKL_API.G_MISS_CHAR;
584 l_instance_rec.INVENTORY_ITEM_ID := OKL_API.G_MISS_NUM;
585 l_instance_rec.VLD_ORGANIZATION_ID := OKL_API.G_MISS_NUM;
586 l_instance_rec.INVENTORY_REVISION := OKL_API.G_MISS_CHAR;
587 l_instance_rec.INV_MASTER_ORGANIZATION_ID := OKL_API.G_MISS_NUM;
588 l_instance_rec.SERIAL_NUMBER := OKL_API.G_MISS_CHAR;
589 l_instance_rec.MFG_SERIAL_NUMBER_FLAG := OKL_API.G_MISS_CHAR;
590 l_instance_rec.LOT_NUMBER := OKL_API.G_MISS_CHAR;
591 l_instance_rec.QUANTITY := OKL_API.G_MISS_NUM;
592 l_instance_rec.UNIT_OF_MEASURE := OKL_API.G_MISS_CHAR;
593 l_instance_rec.ACCOUNTING_CLASS_CODE := OKL_API.G_MISS_CHAR;
594 l_instance_rec.INSTANCE_CONDITION_ID := OKL_API.G_MISS_NUM;
595 l_instance_rec.INSTANCE_STATUS_ID := OKL_API.G_MISS_NUM;
596 l_instance_rec.CUSTOMER_VIEW_FLAG := OKL_API.G_MISS_CHAR;
597 l_instance_rec.MERCHANT_VIEW_FLAG := OKL_API.G_MISS_CHAR;
598 l_instance_rec.SELLABLE_FLAG := OKL_API.G_MISS_CHAR;
599 l_instance_rec.SYSTEM_ID := OKL_API.G_MISS_NUM;
600 l_instance_rec.INSTANCE_TYPE_CODE := OKL_API.G_MISS_CHAR;
601 l_instance_rec.ACTIVE_START_DATE := OKL_API.G_MISS_DATE;
602 l_instance_rec.ACTIVE_END_DATE := OKL_API.G_MISS_DATE;
603 l_instance_rec.LOCATION_TYPE_CODE := OKL_API.G_MISS_CHAR;
604 l_instance_rec.LOCATION_ID := OKL_API.G_MISS_NUM;
605 l_instance_rec.INV_ORGANIZATION_ID := OKL_API.G_MISS_NUM;
606 l_instance_rec.INV_SUBINVENTORY_NAME := OKL_API.G_MISS_CHAR;
607 l_instance_rec.INV_LOCATOR_ID := OKL_API.G_MISS_NUM;
608 l_instance_rec.PA_PROJECT_ID := OKL_API.G_MISS_NUM;
609 l_instance_rec.PA_PROJECT_TASK_ID := OKL_API.G_MISS_NUM;
610 l_instance_rec.IN_TRANSIT_ORDER_LINE_ID := OKL_API.G_MISS_NUM;
611 l_instance_rec.WIP_JOB_ID := OKL_API.G_MISS_NUM;
612 l_instance_rec.PO_ORDER_LINE_ID := OKL_API.G_MISS_NUM;
613 l_instance_rec.LAST_OE_ORDER_LINE_ID := OKL_API.G_MISS_NUM;
614 l_instance_rec.LAST_OE_RMA_LINE_ID := OKL_API.G_MISS_NUM;
615 l_instance_rec.LAST_PO_PO_LINE_ID := OKL_API.G_MISS_NUM;
616 l_instance_rec.LAST_OE_PO_NUMBER := OKL_API.G_MISS_CHAR;
617 l_instance_rec.LAST_WIP_JOB_ID := OKL_API.G_MISS_NUM;
618 l_instance_rec.LAST_PA_PROJECT_ID := OKL_API.G_MISS_NUM;
619 l_instance_rec.LAST_PA_TASK_ID := OKL_API.G_MISS_NUM;
620 l_instance_rec.LAST_OE_AGREEMENT_ID := OKL_API.G_MISS_NUM;
621 l_instance_rec.INSTALL_DATE := OKL_API.G_MISS_DATE;
622 l_instance_rec.MANUALLY_CREATED_FLAG := OKL_API.G_MISS_CHAR;
623 l_instance_rec.RETURN_BY_DATE := OKL_API.G_MISS_DATE;
624 l_instance_rec.ACTUAL_RETURN_DATE := OKL_API.G_MISS_DATE;
625 l_instance_rec.CREATION_COMPLETE_FLAG := OKL_API.G_MISS_CHAR;
626 l_instance_rec.COMPLETENESS_FLAG := OKL_API.G_MISS_CHAR;
627 l_instance_rec.VERSION_LABEL := OKL_API.G_MISS_CHAR;
628 l_instance_rec.VERSION_LABEL_DESCRIPTION := OKL_API.G_MISS_CHAR;
629 l_instance_rec.CONTEXT := OKL_API.G_MISS_CHAR;
630 l_instance_rec.ATTRIBUTE1 := OKL_API.G_MISS_CHAR;
631 l_instance_rec.ATTRIBUTE2 := OKL_API.G_MISS_CHAR;
632 l_instance_rec.ATTRIBUTE3 := OKL_API.G_MISS_CHAR;
633 l_instance_rec.ATTRIBUTE4 := OKL_API.G_MISS_CHAR;
634 l_instance_rec.ATTRIBUTE5 := OKL_API.G_MISS_CHAR;
635 l_instance_rec.ATTRIBUTE6 := OKL_API.G_MISS_CHAR;
636 l_instance_rec.ATTRIBUTE7 := OKL_API.G_MISS_CHAR;
637 l_instance_rec.ATTRIBUTE8 := OKL_API.G_MISS_CHAR;
638 l_instance_rec.ATTRIBUTE9 := OKL_API.G_MISS_CHAR;
639 l_instance_rec.ATTRIBUTE10 := OKL_API.G_MISS_CHAR;
640 l_instance_rec.ATTRIBUTE11 := OKL_API.G_MISS_CHAR;
641 l_instance_rec.ATTRIBUTE12 := OKL_API.G_MISS_CHAR;
642 l_instance_rec.ATTRIBUTE13 := OKL_API.G_MISS_CHAR;
643 l_instance_rec.ATTRIBUTE14 := OKL_API.G_MISS_CHAR;
644 l_instance_rec.ATTRIBUTE15 := OKL_API.G_MISS_CHAR;
645 l_instance_rec.OBJECT_VERSION_NUMBER := OKL_API.G_MISS_NUM;
646 l_instance_rec.LAST_TXN_LINE_DETAIL_ID := OKL_API.G_MISS_NUM;
647 l_instance_rec.INSTALL_LOCATION_TYPE_CODE := OKL_API.G_MISS_CHAR;
648 l_instance_rec.INSTALL_LOCATION_ID := OKL_API.G_MISS_NUM;
649 l_instance_rec.INSTANCE_USAGE_CODE := OKL_API.G_MISS_CHAR;
650 l_instance_rec.CHECK_FOR_INSTANCE_EXPIRY := OKL_API.G_TRUE;
651 END init_ib_rec;
652
653
654 PROCEDURE update_serial_number(
655 p_api_version IN NUMBER,
656 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
657 p_instance_id IN NUMBER,
658 p_instance_name IN VARCHAR2,
659 p_serial_number IN VARCHAR2,
660 p_inventory_item_id IN NUMBER,
661 x_return_status OUT NOCOPY VARCHAR2,
662 x_msg_count OUT NOCOPY NUMBER,
663 x_msg_data OUT NOCOPY VARCHAR2)
664 AS
665
666
667 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SERIAL_NUMBER';
668 l_object_version_number NUMBER;
669 l_instance_rec instance_rec;
670 BEGIN
671 x_return_status := OKL_API.G_RET_STS_SUCCESS;
672
673 --Call start_activity to create savepoint, check compatibility and initialize message list
674
675 x_return_status := OKL_API.START_ACTIVITY(
676 l_api_name
677 ,p_init_msg_list
678 ,'_PUB'
679 ,x_return_status);
680
681 --Check if activity started successfully
682
683 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
684 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
685 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
686 RAISE OKL_API.G_EXCEPTION_ERROR;
687 END IF;
688
689 --Doing this temporarily, need to talk Ashish about some patch to be installed.
690
691 select object_version_number into l_object_version_number from csi_item_instances
692 where instance_id = p_instance_id;
693 init_ib_rec(l_instance_rec);
694
695 l_instance_rec.instance_id := to_number(p_instance_id);
696 l_instance_rec.serial_number := p_serial_number;
697 l_instance_rec.object_version_number := l_object_version_number;
698 l_instance_rec.MFG_SERIAL_NUMBER_FLAG := 'N';
699
700
701 get_trx_rec(p_api_version => p_api_version,
702 p_init_msg_list => p_init_msg_list,
703 x_return_status => x_return_status,
704 x_msg_count => x_msg_count,
705 x_msg_data => x_msg_data,
706 p_cle_id => NULL,
707 p_transaction_type => 'New',
708 x_trx_rec => l_txn_rec);
709
710 csi_item_instance_pub.update_item_instance(p_api_version => p_api_version,
711 p_commit => fnd_api.g_false,
712 p_init_msg_list => p_init_msg_list,
713 p_instance_rec => l_instance_rec,
714 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
715 p_party_tbl => l_party_tbl,
716 p_account_tbl => l_account_tbl,
717 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
718 p_org_assignments_tbl => l_org_assignments_tbl,
719 p_asset_assignment_tbl => l_asset_assignment_tbl,
720 p_txn_rec => l_txn_rec,
721 x_instance_id_lst => l_instance_id_lst,
722 x_return_status => x_return_status,
723 x_msg_count => x_msg_count,
724 x_msg_data => x_msg_data);
725
726 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
727 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
728 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
729 RAISE OKL_API.G_EXCEPTION_ERROR;
730 END IF;
731
732 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
733 EXCEPTION
734 WHEN OKL_API.G_EXCEPTION_ERROR THEN
735 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
736 l_api_name,
737 G_PKG_NAME,
738 'OKL_API.G_RET_STS_ERROR',
739 x_msg_count,
740 x_msg_data,
741 '_PUB');
742 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
743 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
744 l_api_name,
745 G_PKG_NAME,
746 'OKL_API.G_RET_STS_UNEXP_ERROR',
747 x_msg_count,
748 x_msg_data,
749 '_PUB');
750 WHEN OTHERS THEN
751 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
752 l_api_name,
753 G_PKG_NAME,
754 'OTHERS',
755 x_msg_count,
756 x_msg_data,
757 '_PUB');
758
759 END update_serial_number;
760
761
762
763
764 PROCEDURE Update_item_description(
765 p_api_version IN NUMBER,
766 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
767 x_return_status OUT NOCOPY VARCHAR2,
768 x_msg_count OUT NOCOPY NUMBER,
769 x_msg_data OUT NOCOPY VARCHAR2,
770 p_dnz_chr_id IN NUMBER,
771 p_parent_line_id IN NUMBER,
772 p_item_description IN VARCHAR2) AS
773
774 subtype klev_rec_type is okl_CONTRACT_PVT.klev_rec_type;
775
776 l_klev_rec klev_rec_type;
777 l_clev_rec okl_okc_migration_pvt.clev_rec_type;
778 lx_klev_rec klev_rec_type;
779 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
780
781 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ITEM_DESCRIPTION';
782
783 --akrangan bug 5362977 start
784 CURSOR cr_parent_line_id (c_line_id IN NUMBER)
785 IS
786 SELECT parent_line_id
787 FROM OKX_ASSET_LINES_V
788 WHERE id1=c_line_id;
789 l_parent_line_id NUMBER;
790 --akrangan bug 5362977 end
791
792 BEGIN
793 x_return_status := OKL_API.G_RET_STS_SUCCESS;
794
795
796 --Call start_activity to create savepoint, check compatibility and initialize message list
797
798 x_return_status := OKL_API.START_ACTIVITY(
799 l_api_name
800 ,p_init_msg_list
801 ,'_PUB'
802 ,x_return_status);
803
804 --Check if activity started successfully
805
806 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
807 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
808 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
809 RAISE OKL_API.G_EXCEPTION_ERROR;
810 END IF;
811
812 l_klev_rec.id := p_parent_line_id;
813
814 l_clev_rec.id := p_parent_line_id;
815 l_clev_rec.dnz_chr_id := p_dnz_chr_id;
816 l_clev_rec.item_description := p_item_description;
817
818 OKL_CONTRACT_PUB.update_contract_line(
819 p_api_version => p_api_version,
820 p_init_msg_list => p_init_msg_list,
821 x_return_status => x_return_status,
822 x_msg_count => x_msg_count,
823 x_msg_data => x_msg_data,
824 p_clev_rec => l_clev_rec,
825 p_klev_rec => l_klev_rec,
826 x_clev_rec => lx_clev_rec,
827 x_klev_rec => lx_klev_rec);
828
829 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
830 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
831 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
832 RAISE OKL_API.G_EXCEPTION_ERROR;
833 END IF;
834 --akrangan bug 5362977 start
835 OPEN cr_parent_line_id(p_parent_line_id);
836 FETCH cr_parent_line_id INTO l_parent_line_id;
837 CLOSE cr_parent_line_id;
838
839 l_klev_rec.id := l_parent_line_id;
840 l_clev_rec.id := l_parent_line_id;
841
842 OKL_CONTRACT_PUB.update_contract_line(
843 p_api_version => p_api_version,
844 p_init_msg_list => p_init_msg_list,
845 x_return_status => x_return_status,
846 x_msg_count => x_msg_count,
847 x_msg_data => x_msg_data,
848 p_clev_rec => l_clev_rec,
849 p_klev_rec => l_klev_rec,
850 x_clev_rec => lx_clev_rec,
851 x_klev_rec => lx_klev_rec);
852
853 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
854 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
855 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
856 RAISE OKL_API.G_EXCEPTION_ERROR;
857 END IF;
858 --akrangan bug 5362977 end
859
860 OKL_API.END_ACTIVITY (x_msg_count,
861 x_msg_data );
862
863
864 EXCEPTION
865 WHEN OKL_API.G_EXCEPTION_ERROR THEN
866 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
867 l_api_name,
868 G_PKG_NAME,
869 'OKL_API.G_RET_STS_ERROR',
870 x_msg_count,
871 x_msg_data,
872 '_PUB');
873 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
874 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
875 l_api_name,
876 G_PKG_NAME,
877 'OKL_API.G_RET_STS_UNEXP_ERROR',
878 x_msg_count,
879 x_msg_data,
880 '_PUB');
881 WHEN OTHERS THEN
882 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
883 l_api_name,
884 G_PKG_NAME,
885 'OTHERS',
886 x_msg_count,
887 x_msg_data,
888 '_PUB');
889
890 END Update_item_description;
891
892 PROCEDURE parse_desc(
893 p_desc IN VARCHAR2
894 ,p_asset_desc OUT NOCOPY VARCHAR2
895 ,p_item_desc OUT NOCOPY VARCHAR2)
896 AS
897 l_token VARCHAR2(5) := '|||';
898 BEGIN
899
900 p_asset_desc := substr(p_desc,0,instr(p_desc,l_token,1,1) - 1);
901 p_item_desc := substr(p_desc,-(length(p_desc)- instr(p_desc,l_token,1,1) - 2));
902
903 END parse_desc;
904
905
906
907 ---------------------------------------------------------------
908
909 PROCEDURE store_exchange_details (
910 p_api_version IN NUMBER,
911 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
912 p_thpv_tbl IN thpv_tbl_type,
913 p_old_tlpv_tbl IN tlpv_tbl_type,
914 p_new_tlpv_tbl IN tlpv_tbl_type,
915 p_old_iipv_tbl IN iipv_tbl_type,
916 p_new_iipv_tbl IN iipv_tbl_type,
917 x_thpv_tbl OUT NOCOPY thpv_tbl_type,
918 x_old_tlpv_tbl OUT NOCOPY tlpv_tbl_type,
919 x_new_tlpv_tbl OUT NOCOPY tlpv_tbl_type,
920 x_old_iipv_tbl OUT NOCOPY iipv_tbl_type,
921 x_new_iipv_tbl OUT NOCOPY iipv_tbl_type,
922 x_return_status OUT NOCOPY VARCHAR2,
923 x_msg_count OUT NOCOPY NUMBER,
924 x_msg_data OUT NOCOPY VARCHAR2)
925 AS
926 l_api_name CONSTANT VARCHAR2(30) := 'STORE_EXCHANGE_DETAILS';
927 l_old_tlpv_tbl tlpv_tbl_type := p_old_tlpv_tbl;
928 l_new_tlpv_tbl tlpv_tbl_type := p_new_tlpv_tbl;
929 l_old_iipv_tbl iipv_tbl_type := p_old_iipv_tbl;
930 l_new_iipv_tbl iipv_tbl_type := p_new_iipv_tbl;
931 l_thpv_tbl thpv_tbl_type := p_thpv_tbl;
932
933 --dkagrawa added for Bug# 4723820 starts
934 l_conv_type OKL_K_HEADERS.CURRENCY_CONVERSION_TYPE%TYPE;
935 l_conv_rate OKL_K_HEADERS.CURRENCY_CONVERSION_RATE%TYPE;
936 CURSOR l_conv_rate_csr ( cp_khr_id IN NUMBER ) IS
937 SELECT currency_conversion_type,
938 currency_conversion_rate
939 FROM OKL_K_HEADERS
940 WHERE id = cp_khr_id;
941 --dkagrawa for Bug# 4723820 ends
942
943 CURSOR okl_trn_c IS
944 SELECT okl_trn_seq.nextval
945 FROM dual;
946
947
948 BEGIN
949 x_return_status := OKL_API.G_RET_STS_SUCCESS;
950
951 --Call start_activity to create savepoint, check compatibility and initialize message list
952
953 x_return_status := OKL_API.START_ACTIVITY(
954 l_api_name
955 ,p_init_msg_list
956 ,'_PUB'
957 ,x_return_status);
958
959 --Check if activity started successfully
960
961 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
962 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
963 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
964 RAISE OKL_API.G_EXCEPTION_ERROR;
965 END IF;
966
967 -- Create the Header Row
968
969 OPEN okl_trn_c;
970 FETCH okl_trn_c INTO l_thpv_tbl(1).trans_number;
971 CLOSE okl_trn_c;
972 okl_trx_assets_pub.create_trx_ass_h_def(
973 p_api_version => p_api_version,
974 p_init_msg_list => p_init_msg_list,
975 x_return_status => x_return_status,
976 x_msg_count => x_msg_count,
977 x_msg_data => x_msg_data,
978 p_thpv_tbl => l_thpv_tbl,
979 x_thpv_tbl => x_thpv_tbl);
980
981 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
982 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
983 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
984 RAISE OKL_API.G_EXCEPTION_ERROR;
985 END IF;
986
987 --populate the TAS_Id to the old lines plsql table.
988 l_old_tlpv_tbl(1).TAS_ID := x_thpv_tbl(1).ID;
989
990 --dkagrawa added for Bug# 4723820 starts
991 OPEN l_conv_rate_csr (l_old_tlpv_tbl(1).dnz_khr_id);
992 FETCH l_conv_rate_csr INTO l_conv_type,l_conv_rate;
993 CLOSE l_conv_rate_csr;
994 IF l_conv_type = 'User' THEN
995 FOR i IN 1..l_old_tlpv_tbl.COUNT LOOP
996 l_old_tlpv_tbl(i).currency_conversion_rate := l_conv_rate;
997 END LOOP;
998 END IF;
999 --dkagrawa for Bug# 4723820 ends
1000
1001 --Create the old line in the okl_txl_assets table
1002 okl_txl_assets_pub.create_txl_asset_def(
1003 p_api_version => p_api_version,
1004 p_init_msg_list => p_init_msg_list,
1005 x_return_status => x_return_status,
1006 x_msg_count => x_msg_count,
1007 x_msg_data => x_msg_data,
1008 p_tlpv_tbl => l_old_tlpv_tbl,
1009 x_tlpv_tbl => x_old_tlpv_tbl);
1010
1011 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1012 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1013 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1014 RAISE OKL_API.G_EXCEPTION_ERROR;
1015 END IF;
1016
1017 --Change done for making serial number optional.
1018 IF l_old_iipv_tbl(1).serial_number is not NULL THEN
1019
1020 --populate the TAS_Id to the old items plsql table.
1021 l_old_iipv_tbl(1).TAS_ID := x_thpv_tbl(1).ID;
1022
1023 --Create a old line in item instances also.
1024 okl_txl_itm_insts_pub.create_txl_itm_insts(
1025 p_api_version => p_api_version,
1026 p_init_msg_list => p_init_msg_list,
1027 x_return_status => x_return_status,
1028 x_msg_count => x_msg_count,
1029 x_msg_data => x_msg_data,
1030 p_iipv_tbl => l_old_iipv_tbl,
1031 x_iipv_tbl => x_old_iipv_tbl);
1032
1033 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1034 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1035 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1036 RAISE OKL_API.G_EXCEPTION_ERROR;
1037 END IF;
1038
1039 END IF;
1040 --populate the TAS_Id to the new lines plsql table.
1041 l_new_tlpv_tbl(1).TAS_ID := x_thpv_tbl(1).ID;
1042
1043 --dkagrawa added for Bug# 4723820 starts
1044 IF l_conv_type = 'User' THEN
1045 FOR i IN 1..l_new_tlpv_tbl.COUNT LOOP
1046 l_new_tlpv_tbl(i).currency_conversion_rate := l_conv_rate;
1047 END LOOP;
1048 END IF;
1049 --dkagrawa for Bug# 4723820 ends
1050
1051 --Create the new line in the okl_txl_assets table
1052 okl_txl_assets_pub.create_txl_asset_def(
1053 p_api_version => p_api_version,
1054 p_init_msg_list => p_init_msg_list,
1055 x_return_status => x_return_status,
1056 x_msg_count => x_msg_count,
1057 x_msg_data => x_msg_data,
1058 p_tlpv_tbl => l_new_tlpv_tbl,
1059 x_tlpv_tbl => x_new_tlpv_tbl);
1060
1061 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1062 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1063 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1064 RAISE OKL_API.G_EXCEPTION_ERROR;
1065 END IF;
1066
1067
1068 --Change done for making serial number optional
1069 IF l_new_iipv_tbl(1).serial_number is not NULL THEN
1070
1071 --populate the TAS_Id to the new items plsql table.
1072 l_new_iipv_tbl(1).TAS_ID := x_thpv_tbl(1).ID;
1073
1074 --Create a new line in item instances also.
1075 okl_txl_itm_insts_pub.create_txl_itm_insts(
1076 p_api_version => p_api_version,
1077 p_init_msg_list => p_init_msg_list,
1078 x_return_status => x_return_status,
1079 x_msg_count => x_msg_count,
1080 x_msg_data => x_msg_data,
1081 p_iipv_tbl => l_new_iipv_tbl,
1082 x_iipv_tbl => x_new_iipv_tbl);
1083
1084 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1085 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1086 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1087 RAISE OKL_API.G_EXCEPTION_ERROR;
1088 END IF;
1089
1090 END IF;
1091
1092 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1093 EXCEPTION
1094 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1095 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1096 l_api_name,
1097 G_PKG_NAME,
1098 'OKL_API.G_RET_STS_ERROR',
1099 x_msg_count,
1100 x_msg_data,
1101 '_PUB');
1102 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1103 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1104 l_api_name,
1105 G_PKG_NAME,
1106 'OKL_API.G_RET_STS_UNEXP_ERROR',
1107 x_msg_count,
1108 x_msg_data,
1109 '_PUB');
1110 WHEN OTHERS THEN
1111 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1112 l_api_name,
1113 G_PKG_NAME,
1114 'OTHERS',
1115 x_msg_count,
1116 x_msg_data,
1117 '_PUB');
1118
1119 END store_exchange_details;
1120
1121
1122
1123
1124
1125
1126
1127 PROCEDURE exchange(
1128 p_api_version IN NUMBER,
1129 p_init_msg_list IN VARCHAR2 := OKL_API.G_FALSE,
1130 p_tas_id IN NUMBER,
1131 x_return_status OUT NOCOPY VARCHAR2,
1132 x_msg_count OUT NOCOPY NUMBER,
1133 x_msg_data OUT NOCOPY VARCHAR2)
1134 AS
1135
1136 p_talv_tbl talv_tbl_type;
1137 x_talv_tbl talv_tbl_type;
1138
1139 p_thpv_tbl thpv_tbl_type;
1140 x_thpv_tbl thpv_tbl_type;
1141 p_no_data_found BOOLEAN;
1142
1143 p_itiv_tbl itiv_tbl_type;
1144 x_itiv_tbl itiv_tbl_type;
1145
1146 p_cplv_rec cplv_rec_type;
1147 x_cplv_rec cplv_rec_type;
1148
1149 p_cvmv_rec cvmv_rec_type;
1150 x_cvmv_rec cvmv_rec_type;
1151
1152 x_year NUMBER;
1153 l_api_name CONSTANT VARCHAR2(30) := 'EXCHANGE';
1154
1155 l_ct_line_id NUMBER;
1156 l_vendor_id1 NUMBER;
1157 l_vendor_id2 VARCHAR2(30);
1158 l_okc_party_roles_id NUMBER;
1159 l_instance_id NUMBER;
1160 l_asset_desc VARCHAR2(80);
1161 l_item_desc VARCHAR2(1995);
1162 l_id1_okx_asset_lines NUMBER;
1163
1164 CURSOR c_model_line(c_parent_line_id NUMBER) IS
1165 SELECT A.ID
1166 FROM OKC_K_LINES_V A,
1167 OKC_LINE_STYLES_B B
1168 WHERE A.CLE_ID = c_parent_line_id
1169 AND A.LSE_ID = B.ID
1170 AND B.LTY_CODE = 'ITEM';
1171
1172 CURSOR c_vendor(c_model_line_id NUMBER) IS
1173 SELECT B.ID
1174 FROM AP_SUPPLIERS A,
1175 OKC_K_PARTY_ROLES_B B
1176 WHERE A.VENDOR_ID = B.OBJECT1_ID1
1177 AND B.RLE_CODE = 'OKL_VENDOR'
1178 AND B.CLE_ID = c_model_line_id;
1179
1180 CURSOR c_new_vendor(c_id NUMBER) IS
1181 SELECT ID2
1182 FROM OKX_VENDORS_V
1183 where id1=c_id;
1184
1185 CURSOR c_asset_id1 (c_khr_id NUMBER,c_asset_id NUMBER,c_Asset_number VARCHAR2) IS
1186 SELECT CLE.ID ID1
1187 FROM OKC_K_LINES_B CLE,
1188 OKC_K_ITEMS CIM
1189 WHERE CLE.DNZ_CHR_ID=c_khr_id
1190 AND CIM.CLE_ID = CLE.ID
1191 AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
1192 AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID
1193 AND CIM.Object1_id1 = c_asset_id
1194 AND CLE.STS_CODE <> 'ABANDONED';
1195
1196
1197 BEGIN
1198
1199 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1200
1201 --Call start_activity to create savepoint, check compatibility and initialize message list
1202
1203 x_return_status := OKL_API.START_ACTIVITY(
1204 l_api_name
1205 ,p_init_msg_list
1206 ,'_PUB'
1207 ,x_return_status);
1208
1209 --Check if activity started successfully
1210
1211 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1212 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1213 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1214 RAISE OKL_API.G_EXCEPTION_ERROR;
1215 END IF;
1216
1217 -- probably i might have to take the transaction id/request id and
1218 -- use it to get the relevant asset details for the new asset and then
1219 -- use this info to update the FA tables
1220 -- update all the asset details
1221 p_talv_tbl(1).tas_id := p_tas_id;
1222 p_talv_tbl(1).tal_type := 'NAS';
1223 x_talv_tbl := OKL_EQUIPMENT_EXCHANGE_PVT.GET_TAL_REC(p_talv_tbl,p_no_data_found);
1224 parse_desc(x_talv_tbl(1).description,l_asset_desc,l_item_desc);
1225
1226 --Have to version the contract before doing any of the following tasks.
1227 p_cvmv_rec.chr_id := x_talv_tbl(1).dnz_khr_id;
1228
1229 okl_version_pub.version_contract(p_api_version => p_api_version,
1230 p_init_msg_list => p_init_msg_list,
1231 x_return_status => x_return_status,
1232 x_msg_count => x_msg_count,
1233 x_msg_data => x_msg_data,
1234 p_cvmv_rec => p_cvmv_rec,
1235 x_cvmv_rec => x_cvmv_rec,
1236 p_commit => 'T');
1237
1238 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1239 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1240 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1241 RAISE OKL_API.G_EXCEPTION_ERROR;
1242 END IF;
1243
1244
1245 -- This piece of code is added to fix Bug 2477684
1246 -- FA has changed their APIs as per the new standards of treating NULL as FND_API.G_MISS
1247 -- So we have to pass these constants if we want to update the values to NULL.
1248
1249 If (x_talv_tbl(1).model_number is NULL) THEN
1250 x_talv_tbl(1).model_number := FND_API.G_MISS_CHAR;
1251 END IF;
1252
1253 If (x_talv_tbl(1).manufacturer_name is NULL) THEN
1254 x_talv_tbl(1).manufacturer_name := FND_API.G_MISS_CHAR;
1255 END IF;
1256
1257
1258 okl_asset_details_pub.update_asset(p_api_version => p_api_version,
1259 p_init_msg_list => p_init_msg_list,
1260 x_return_status => x_return_status,
1261 x_msg_count => x_msg_count,
1262 x_msg_data => x_msg_data,
1263 p_asset_id => to_number(x_talv_tbl(1).dnz_asset_id),
1264 p_asset_number => x_talv_tbl(1).asset_number,
1265 px_asset_desc => l_asset_desc,
1266 px_model_no => x_talv_tbl(1).model_number,
1267 px_manufacturer => x_talv_tbl(1).manufacturer_name);
1268 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1269 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1270 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1271 RAISE OKL_API.G_EXCEPTION_ERROR;
1272 END IF;
1273
1274
1275 OPEN c_asset_id1(x_talv_tbl(1).dnz_khr_id,x_talv_tbl(1).dnz_asset_id,x_talv_tbl(1).asset_number);
1276 FETCH c_asset_id1 into l_id1_okx_asset_lines;
1277 CLOSE c_asset_id1;
1278
1279 -- This piece of code is changed to fix Bug 2477684
1280 -- The year should be updated even if it is NULL.. so remove this check.
1281
1282 -- IF x_talv_tbl(1).year_manufactured IS NOT NULL then
1283 okl_asset_details_pub.update_year(p_api_version => p_api_version,
1284 p_init_msg_list => p_init_msg_list,
1285 x_return_status => x_return_status,
1286 x_msg_count => x_msg_count,
1287 x_msg_data => x_msg_data,
1288 p_dnz_chr_id => x_talv_tbl(1).dnz_khr_id,
1289 p_parent_line_id => l_id1_okx_asset_lines,
1290 p_year => x_talv_tbl(1).year_manufactured,
1291 x_year => x_year);
1292 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1293 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1294 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1295 RAISE OKL_API.G_EXCEPTION_ERROR;
1296 END IF;
1297 -- END IF;
1298
1299
1300 update_item_description(p_api_version => p_api_version,
1301 p_init_msg_list => p_init_msg_list,
1302 x_return_status => x_return_status,
1303 x_msg_count => x_msg_count,
1304 x_msg_data => x_msg_data,
1305 p_dnz_chr_id => x_talv_tbl(1).dnz_khr_id,
1306 --p_parent_line_id => x_itiv_tbl(1).kle_id,
1307 p_parent_line_id => l_id1_okx_asset_lines,
1308 --akrangan bug 5362977 start
1309 --p_item_description => l_item_desc);
1310 p_item_description => l_asset_desc);
1311 --akrangan bug 5362977 end
1312 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1313 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1314 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1315 RAISE OKL_API.G_EXCEPTION_ERROR;
1316 END IF;
1317
1318
1319 --change for making sno optional
1320 p_itiv_tbl(1).tas_id := p_tas_id;
1321 p_itiv_tbl(1).tal_type := 'NAS';
1322 x_itiv_tbl := OKL_EQUIPMENT_EXCHANGE_PVT.GET_ITEM_REC(p_itiv_tbl,p_no_data_found);
1323 If not p_no_data_found then
1324
1325 l_instance_id := get_instance_id( x_itiv_tbl(1).instance_number_ib);
1326 update_serial_number(p_api_version => p_api_version,
1327 p_init_msg_list => p_init_msg_list,
1328 p_instance_id => l_instance_id,
1329 p_instance_name => x_itiv_tbl(1).instance_number_ib,
1330 p_serial_number => x_itiv_tbl(1).serial_number,
1331 p_inventory_item_id => x_itiv_tbl(1).inventory_item_id,
1332 x_return_status => x_return_status,
1333 x_msg_count => x_msg_count,
1334 x_msg_data => x_msg_data);
1335
1336 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1337 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1338 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1339 RAISE OKL_API.G_EXCEPTION_ERROR;
1340 END IF;
1341 end if; -- for p_no_data_found
1342 ---------------------------------------------------------
1343 OPEN c_model_line(x_talv_tbl(1).kle_id);
1344 FETCH c_model_line INTO l_ct_line_id;
1345 CLOSE c_model_line;
1346
1347 IF l_ct_line_id IS NOT NULL THEN
1348 OPEN c_vendor(l_ct_line_id);
1349 FETCH c_vendor INTO l_okc_party_roles_id;
1350 CLOSE c_vendor;
1351 END IF;
1352
1353 l_vendor_id1 := x_talv_tbl(1).supplier_id;
1354 IF l_okc_party_roles_id IS NOT NULL AND l_vendor_id1 IS NOT NULL THEN
1355
1356 OPEN c_new_vendor(x_talv_tbl(1).supplier_id);
1357 FETCH c_new_vendor INTO l_vendor_id2;
1358 CLOSE c_new_vendor;
1359
1360 p_cplv_rec.id := l_okc_party_roles_id;
1361 p_cplv_rec.object1_id1 := l_vendor_id1;
1362 p_cplv_rec.object1_id2 := l_vendor_id2;
1363
1364 okl_create_kle_pub.Update_party_roles_rec(p_api_version => p_api_version,
1365 p_init_msg_list => p_init_msg_list,
1366 x_return_status => x_return_status,
1367 x_msg_count => x_msg_count,
1368 x_msg_data => x_msg_data,
1369 p_cplv_rec => p_cplv_rec,
1370 x_cplv_rec => x_cplv_rec);
1371
1372
1373
1374 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1375 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1376 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1377 RAISE OKL_API.G_EXCEPTION_ERROR;
1378 END IF;
1379 ELSE
1380 NULL;
1381
1382 END IF;
1383
1384
1385 -- update the transaction table and set the status to processed or whatever
1386 --If the trx failed then i have to record the reason for failure and send message to the
1387 --concerned person abt this. How do we do this?
1388 IF x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1389 p_thpv_tbl(1).id := p_tas_id;
1390 p_thpv_tbl(1).tsu_code := 'PROCESSED';
1391 okl_trx_assets_pub.update_trx_ass_h_def(
1392 p_api_version => p_api_version,
1393 p_init_msg_list => p_init_msg_list,
1394 x_return_status => x_return_status,
1395 x_msg_count => x_msg_count,
1396 x_msg_data => x_msg_data,
1397 p_thpv_tbl => p_thpv_tbl,
1398 x_thpv_tbl => x_thpv_tbl);
1399 END IF;
1400
1401
1402
1403 EXCEPTION
1404 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1405 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1406 l_api_name,
1407 G_PKG_NAME,
1408 'OKL_API.G_RET_STS_ERROR',
1409 x_msg_count,
1410 x_msg_data,
1411 '_PUB');
1412 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1413 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1414 l_api_name,
1415 G_PKG_NAME,
1416 'OKL_API.G_RET_STS_UNEXP_ERROR',
1417 x_msg_count,
1418 x_msg_data,
1419 '_PUB');
1420 WHEN OTHERS THEN
1421 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1422 l_api_name,
1423 G_PKG_NAME,
1424 'OTHERS',
1425 x_msg_count,
1426 x_msg_data,
1427 '_PUB');
1428
1429 END exchange;
1430
1431 END okl_equipment_exchange_pvt;
1432