[Home] [Help]
PACKAGE BODY: APPS.OKL_EQUIPMENT_EXCHANGE_PVT
Source
1 package body okl_equipment_exchange_pvt AS
2 /* $Header: OKLREQXB.pls 120.5 2006/12/05 05:16:42 akrangan noship $ */
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 Cursor okl_trx_type_curs(p_transaction_type IN VARCHAR2)is
494 select transaction_type_id
495 from CS_TRANSACTION_TYPES_V
496 where Name = p_transaction_type;
497 l_trx_type_id NUMBER;
498 Begin
499 open okl_trx_type_curs(p_transaction_type);
500 Fetch okl_trx_type_curs
501 into l_trx_type_id;
502 If okl_trx_type_curs%NotFound Then
503 --OKL LINE ACTIVATION not seeded as a source transaction in IB
504 Raise OKL_API.G_EXCEPTION_ERROR;
505 End If;
506 close okl_trx_type_curs;
507 --Assign transaction Type id to seeded value in cs_lookups
508 x_trx_rec.transaction_type_id := l_trx_type_id;
509 --Assign Source Line Ref id to contract line id of IB instance line
510 x_trx_rec.source_line_ref_id := p_cle_id;
511 x_trx_rec.transaction_date := sysdate;
512 x_trx_rec.source_transaction_date := sysdate;
513 Exception
514 When OKL_API.G_EXCEPTION_ERROR Then
515 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
516 (
517 l_api_name,
518 G_PKG_NAME,
519 'OKL_API.G_RET_STS_ERROR',
520 x_msg_count,
521 x_msg_data,
522 '_PVT'
523 );
524 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
525 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
526 (
527 l_api_name,
528 G_PKG_NAME,
529 'OKL_API.G_RET_STS_UNEXP_ERROR',
530 x_msg_count,
531 x_msg_data,
532 '_PVT'
533 );
534 WHEN OTHERS THEN
535 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
536 (
537 l_api_name,
538 G_PKG_NAME,
539 'OTHERS',
540 x_msg_count,
541 x_msg_data,
542 '_PVT'
543 );
544 END get_trx_rec;
545
546
547 ---------------------------------------------------------------------------------------------
548
549
550 PROCEDURE init_ib_rec(l_instance_rec OUT NOCOPY instance_rec)
551 AS
552 BEGIN
553 l_instance_rec.INSTANCE_ID := OKL_API.G_MISS_NUM;
554 l_instance_rec.INSTANCE_NUMBER := OKL_API.G_MISS_CHAR;
555 l_instance_rec.EXTERNAL_REFERENCE := OKL_API.G_MISS_CHAR;
556 l_instance_rec.INVENTORY_ITEM_ID := OKL_API.G_MISS_NUM;
557 l_instance_rec.VLD_ORGANIZATION_ID := OKL_API.G_MISS_NUM;
558 l_instance_rec.INVENTORY_REVISION := OKL_API.G_MISS_CHAR;
559 l_instance_rec.INV_MASTER_ORGANIZATION_ID := OKL_API.G_MISS_NUM;
560 l_instance_rec.SERIAL_NUMBER := OKL_API.G_MISS_CHAR;
561 l_instance_rec.MFG_SERIAL_NUMBER_FLAG := OKL_API.G_MISS_CHAR;
562 l_instance_rec.LOT_NUMBER := OKL_API.G_MISS_CHAR;
563 l_instance_rec.QUANTITY := OKL_API.G_MISS_NUM;
564 l_instance_rec.UNIT_OF_MEASURE := OKL_API.G_MISS_CHAR;
565 l_instance_rec.ACCOUNTING_CLASS_CODE := OKL_API.G_MISS_CHAR;
566 l_instance_rec.INSTANCE_CONDITION_ID := OKL_API.G_MISS_NUM;
567 l_instance_rec.INSTANCE_STATUS_ID := OKL_API.G_MISS_NUM;
568 l_instance_rec.CUSTOMER_VIEW_FLAG := OKL_API.G_MISS_CHAR;
569 l_instance_rec.MERCHANT_VIEW_FLAG := OKL_API.G_MISS_CHAR;
570 l_instance_rec.SELLABLE_FLAG := OKL_API.G_MISS_CHAR;
571 l_instance_rec.SYSTEM_ID := OKL_API.G_MISS_NUM;
572 l_instance_rec.INSTANCE_TYPE_CODE := OKL_API.G_MISS_CHAR;
573 l_instance_rec.ACTIVE_START_DATE := OKL_API.G_MISS_DATE;
574 l_instance_rec.ACTIVE_END_DATE := OKL_API.G_MISS_DATE;
575 l_instance_rec.LOCATION_TYPE_CODE := OKL_API.G_MISS_CHAR;
576 l_instance_rec.LOCATION_ID := OKL_API.G_MISS_NUM;
577 l_instance_rec.INV_ORGANIZATION_ID := OKL_API.G_MISS_NUM;
578 l_instance_rec.INV_SUBINVENTORY_NAME := OKL_API.G_MISS_CHAR;
579 l_instance_rec.INV_LOCATOR_ID := OKL_API.G_MISS_NUM;
580 l_instance_rec.PA_PROJECT_ID := OKL_API.G_MISS_NUM;
581 l_instance_rec.PA_PROJECT_TASK_ID := OKL_API.G_MISS_NUM;
582 l_instance_rec.IN_TRANSIT_ORDER_LINE_ID := OKL_API.G_MISS_NUM;
583 l_instance_rec.WIP_JOB_ID := OKL_API.G_MISS_NUM;
584 l_instance_rec.PO_ORDER_LINE_ID := OKL_API.G_MISS_NUM;
585 l_instance_rec.LAST_OE_ORDER_LINE_ID := OKL_API.G_MISS_NUM;
586 l_instance_rec.LAST_OE_RMA_LINE_ID := OKL_API.G_MISS_NUM;
587 l_instance_rec.LAST_PO_PO_LINE_ID := OKL_API.G_MISS_NUM;
588 l_instance_rec.LAST_OE_PO_NUMBER := OKL_API.G_MISS_CHAR;
589 l_instance_rec.LAST_WIP_JOB_ID := OKL_API.G_MISS_NUM;
590 l_instance_rec.LAST_PA_PROJECT_ID := OKL_API.G_MISS_NUM;
591 l_instance_rec.LAST_PA_TASK_ID := OKL_API.G_MISS_NUM;
592 l_instance_rec.LAST_OE_AGREEMENT_ID := OKL_API.G_MISS_NUM;
593 l_instance_rec.INSTALL_DATE := OKL_API.G_MISS_DATE;
594 l_instance_rec.MANUALLY_CREATED_FLAG := OKL_API.G_MISS_CHAR;
595 l_instance_rec.RETURN_BY_DATE := OKL_API.G_MISS_DATE;
596 l_instance_rec.ACTUAL_RETURN_DATE := OKL_API.G_MISS_DATE;
597 l_instance_rec.CREATION_COMPLETE_FLAG := OKL_API.G_MISS_CHAR;
598 l_instance_rec.COMPLETENESS_FLAG := OKL_API.G_MISS_CHAR;
599 l_instance_rec.VERSION_LABEL := OKL_API.G_MISS_CHAR;
600 l_instance_rec.VERSION_LABEL_DESCRIPTION := OKL_API.G_MISS_CHAR;
601 l_instance_rec.CONTEXT := OKL_API.G_MISS_CHAR;
602 l_instance_rec.ATTRIBUTE1 := OKL_API.G_MISS_CHAR;
603 l_instance_rec.ATTRIBUTE2 := OKL_API.G_MISS_CHAR;
604 l_instance_rec.ATTRIBUTE3 := OKL_API.G_MISS_CHAR;
605 l_instance_rec.ATTRIBUTE4 := OKL_API.G_MISS_CHAR;
606 l_instance_rec.ATTRIBUTE5 := OKL_API.G_MISS_CHAR;
607 l_instance_rec.ATTRIBUTE6 := OKL_API.G_MISS_CHAR;
608 l_instance_rec.ATTRIBUTE7 := OKL_API.G_MISS_CHAR;
609 l_instance_rec.ATTRIBUTE8 := OKL_API.G_MISS_CHAR;
610 l_instance_rec.ATTRIBUTE9 := OKL_API.G_MISS_CHAR;
611 l_instance_rec.ATTRIBUTE10 := OKL_API.G_MISS_CHAR;
612 l_instance_rec.ATTRIBUTE11 := OKL_API.G_MISS_CHAR;
613 l_instance_rec.ATTRIBUTE12 := OKL_API.G_MISS_CHAR;
614 l_instance_rec.ATTRIBUTE13 := OKL_API.G_MISS_CHAR;
615 l_instance_rec.ATTRIBUTE14 := OKL_API.G_MISS_CHAR;
616 l_instance_rec.ATTRIBUTE15 := OKL_API.G_MISS_CHAR;
617 l_instance_rec.OBJECT_VERSION_NUMBER := OKL_API.G_MISS_NUM;
618 l_instance_rec.LAST_TXN_LINE_DETAIL_ID := OKL_API.G_MISS_NUM;
619 l_instance_rec.INSTALL_LOCATION_TYPE_CODE := OKL_API.G_MISS_CHAR;
620 l_instance_rec.INSTALL_LOCATION_ID := OKL_API.G_MISS_NUM;
621 l_instance_rec.INSTANCE_USAGE_CODE := OKL_API.G_MISS_CHAR;
622 l_instance_rec.CHECK_FOR_INSTANCE_EXPIRY := OKL_API.G_TRUE;
623 END init_ib_rec;
624
625
626 PROCEDURE update_serial_number(
627 p_api_version IN NUMBER,
628 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
629 p_instance_id IN NUMBER,
630 p_instance_name IN VARCHAR2,
631 p_serial_number IN VARCHAR2,
632 p_inventory_item_id IN NUMBER,
633 x_return_status OUT NOCOPY VARCHAR2,
634 x_msg_count OUT NOCOPY NUMBER,
635 x_msg_data OUT NOCOPY VARCHAR2)
636 AS
637
638
639 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SERIAL_NUMBER';
640 l_object_version_number NUMBER;
641 l_instance_rec instance_rec;
642 BEGIN
643 x_return_status := OKL_API.G_RET_STS_SUCCESS;
644
645 --Call start_activity to create savepoint, check compatibility and initialize message list
646
647 x_return_status := OKL_API.START_ACTIVITY(
648 l_api_name
649 ,p_init_msg_list
650 ,'_PUB'
651 ,x_return_status);
652
653 --Check if activity started successfully
654
655 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
656 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
657 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
658 RAISE OKL_API.G_EXCEPTION_ERROR;
659 END IF;
660
661 --Doing this temporarily, need to talk Ashish about some patch to be installed.
662
663 select object_version_number into l_object_version_number from csi_item_instances
664 where instance_id = p_instance_id;
665 init_ib_rec(l_instance_rec);
666
667 l_instance_rec.instance_id := to_number(p_instance_id);
668 l_instance_rec.serial_number := p_serial_number;
669 l_instance_rec.object_version_number := l_object_version_number;
670 l_instance_rec.MFG_SERIAL_NUMBER_FLAG := 'N';
671
672
673 get_trx_rec(p_api_version => p_api_version,
674 p_init_msg_list => p_init_msg_list,
675 x_return_status => x_return_status,
676 x_msg_count => x_msg_count,
677 x_msg_data => x_msg_data,
678 p_cle_id => NULL,
679 p_transaction_type => 'New',
680 x_trx_rec => l_txn_rec);
681
682 csi_item_instance_pub.update_item_instance(p_api_version => p_api_version,
683 p_commit => fnd_api.g_false,
684 p_init_msg_list => p_init_msg_list,
685 p_instance_rec => l_instance_rec,
686 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
687 p_party_tbl => l_party_tbl,
688 p_account_tbl => l_account_tbl,
689 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
690 p_org_assignments_tbl => l_org_assignments_tbl,
691 p_asset_assignment_tbl => l_asset_assignment_tbl,
692 p_txn_rec => l_txn_rec,
693 x_instance_id_lst => l_instance_id_lst,
694 x_return_status => x_return_status,
695 x_msg_count => x_msg_count,
696 x_msg_data => x_msg_data);
697
698 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
699 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
700 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
701 RAISE OKL_API.G_EXCEPTION_ERROR;
702 END IF;
703
704 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
705 EXCEPTION
706 WHEN OKL_API.G_EXCEPTION_ERROR THEN
707 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
708 l_api_name,
709 G_PKG_NAME,
710 'OKL_API.G_RET_STS_ERROR',
711 x_msg_count,
712 x_msg_data,
713 '_PUB');
714 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
715 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
716 l_api_name,
717 G_PKG_NAME,
718 'OKL_API.G_RET_STS_UNEXP_ERROR',
719 x_msg_count,
720 x_msg_data,
721 '_PUB');
722 WHEN OTHERS THEN
723 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
724 l_api_name,
725 G_PKG_NAME,
726 'OTHERS',
727 x_msg_count,
728 x_msg_data,
729 '_PUB');
730
731 END update_serial_number;
732
733
734
735
736 PROCEDURE Update_item_description(
737 p_api_version IN NUMBER,
738 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
739 x_return_status OUT NOCOPY VARCHAR2,
740 x_msg_count OUT NOCOPY NUMBER,
741 x_msg_data OUT NOCOPY VARCHAR2,
742 p_dnz_chr_id IN NUMBER,
743 p_parent_line_id IN NUMBER,
744 p_item_description IN VARCHAR2) AS
745
746 subtype klev_rec_type is okl_CONTRACT_PVT.klev_rec_type;
747
748 l_klev_rec klev_rec_type;
749 l_clev_rec okl_okc_migration_pvt.clev_rec_type;
750 lx_klev_rec klev_rec_type;
751 lx_clev_rec okl_okc_migration_pvt.clev_rec_type;
752
753 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ITEM_DESCRIPTION';
754
755 --akrangan bug 5362977 start
756 CURSOR cr_parent_line_id (c_line_id IN NUMBER)
757 IS
758 SELECT parent_line_id
759 FROM OKX_ASSET_LINES_V
760 WHERE id1=c_line_id;
761 l_parent_line_id NUMBER;
762 --akrangan bug 5362977 end
763
764 BEGIN
765 x_return_status := OKL_API.G_RET_STS_SUCCESS;
766
767
768 --Call start_activity to create savepoint, check compatibility and initialize message list
769
770 x_return_status := OKL_API.START_ACTIVITY(
771 l_api_name
772 ,p_init_msg_list
773 ,'_PUB'
774 ,x_return_status);
775
776 --Check if activity started successfully
777
778 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
779 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
780 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
781 RAISE OKL_API.G_EXCEPTION_ERROR;
782 END IF;
783
784 l_klev_rec.id := p_parent_line_id;
785
786 l_clev_rec.id := p_parent_line_id;
787 l_clev_rec.dnz_chr_id := p_dnz_chr_id;
788 l_clev_rec.item_description := p_item_description;
789
790 OKL_CONTRACT_PUB.update_contract_line(
791 p_api_version => p_api_version,
792 p_init_msg_list => p_init_msg_list,
793 x_return_status => x_return_status,
794 x_msg_count => x_msg_count,
795 x_msg_data => x_msg_data,
796 p_clev_rec => l_clev_rec,
797 p_klev_rec => l_klev_rec,
798 x_clev_rec => lx_clev_rec,
799 x_klev_rec => lx_klev_rec);
800
801 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
802 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
803 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
804 RAISE OKL_API.G_EXCEPTION_ERROR;
805 END IF;
806 --akrangan bug 5362977 start
807 OPEN cr_parent_line_id(p_parent_line_id);
808 FETCH cr_parent_line_id INTO l_parent_line_id;
809 CLOSE cr_parent_line_id;
810
811 l_klev_rec.id := l_parent_line_id;
812 l_clev_rec.id := l_parent_line_id;
813
814 OKL_CONTRACT_PUB.update_contract_line(
815 p_api_version => p_api_version,
816 p_init_msg_list => p_init_msg_list,
817 x_return_status => x_return_status,
818 x_msg_count => x_msg_count,
819 x_msg_data => x_msg_data,
820 p_clev_rec => l_clev_rec,
821 p_klev_rec => l_klev_rec,
822 x_clev_rec => lx_clev_rec,
823 x_klev_rec => lx_klev_rec);
824
825 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
826 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
827 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
828 RAISE OKL_API.G_EXCEPTION_ERROR;
829 END IF;
830 --akrangan bug 5362977 end
831
832 OKL_API.END_ACTIVITY (x_msg_count,
833 x_msg_data );
834
835
836 EXCEPTION
837 WHEN OKL_API.G_EXCEPTION_ERROR THEN
838 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
839 l_api_name,
840 G_PKG_NAME,
841 'OKL_API.G_RET_STS_ERROR',
842 x_msg_count,
843 x_msg_data,
844 '_PUB');
845 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
846 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
847 l_api_name,
848 G_PKG_NAME,
849 'OKL_API.G_RET_STS_UNEXP_ERROR',
850 x_msg_count,
851 x_msg_data,
852 '_PUB');
853 WHEN OTHERS THEN
854 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
855 l_api_name,
856 G_PKG_NAME,
857 'OTHERS',
858 x_msg_count,
859 x_msg_data,
860 '_PUB');
861
862 END Update_item_description;
863
864 PROCEDURE parse_desc(
865 p_desc IN VARCHAR2
866 ,p_asset_desc OUT NOCOPY VARCHAR2
867 ,p_item_desc OUT NOCOPY VARCHAR2)
868 AS
869 l_token VARCHAR2(5) := '|||';
870 BEGIN
871
872 p_asset_desc := substr(p_desc,0,instr(p_desc,l_token,1,1) - 1);
873 p_item_desc := substr(p_desc,-(length(p_desc)- instr(p_desc,l_token,1,1) - 2));
874
875 END parse_desc;
876
877
878
879 ---------------------------------------------------------------
880
881 PROCEDURE store_exchange_details (
882 p_api_version IN NUMBER,
883 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
884 p_thpv_tbl IN thpv_tbl_type,
885 p_old_tlpv_tbl IN tlpv_tbl_type,
886 p_new_tlpv_tbl IN tlpv_tbl_type,
887 p_old_iipv_tbl IN iipv_tbl_type,
888 p_new_iipv_tbl IN iipv_tbl_type,
889 x_thpv_tbl OUT NOCOPY thpv_tbl_type,
890 x_old_tlpv_tbl OUT NOCOPY tlpv_tbl_type,
891 x_new_tlpv_tbl OUT NOCOPY tlpv_tbl_type,
892 x_old_iipv_tbl OUT NOCOPY iipv_tbl_type,
893 x_new_iipv_tbl OUT NOCOPY iipv_tbl_type,
894 x_return_status OUT NOCOPY VARCHAR2,
895 x_msg_count OUT NOCOPY NUMBER,
896 x_msg_data OUT NOCOPY VARCHAR2)
897 AS
898 l_api_name CONSTANT VARCHAR2(30) := 'STORE_EXCHANGE_DETAILS';
899 l_old_tlpv_tbl tlpv_tbl_type := p_old_tlpv_tbl;
900 l_new_tlpv_tbl tlpv_tbl_type := p_new_tlpv_tbl;
901 l_old_iipv_tbl iipv_tbl_type := p_old_iipv_tbl;
902 l_new_iipv_tbl iipv_tbl_type := p_new_iipv_tbl;
903 l_thpv_tbl thpv_tbl_type := p_thpv_tbl;
904
905 --dkagrawa added for Bug# 4723820 starts
906 l_conv_type OKL_K_HEADERS.CURRENCY_CONVERSION_TYPE%TYPE;
907 l_conv_rate OKL_K_HEADERS.CURRENCY_CONVERSION_RATE%TYPE;
908 CURSOR l_conv_rate_csr ( cp_khr_id IN NUMBER ) IS
909 SELECT currency_conversion_type,
910 currency_conversion_rate
911 FROM OKL_K_HEADERS
912 WHERE id = cp_khr_id;
913 --dkagrawa for Bug# 4723820 ends
914
915 CURSOR okl_trn_c IS
916 SELECT okl_trn_seq.nextval
917 FROM dual;
918
919
920 BEGIN
921 x_return_status := OKL_API.G_RET_STS_SUCCESS;
922
923 --Call start_activity to create savepoint, check compatibility and initialize message list
924
925 x_return_status := OKL_API.START_ACTIVITY(
926 l_api_name
927 ,p_init_msg_list
928 ,'_PUB'
929 ,x_return_status);
930
931 --Check if activity started successfully
932
933 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
934 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
935 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
936 RAISE OKL_API.G_EXCEPTION_ERROR;
937 END IF;
938
939 -- Create the Header Row
940
941 OPEN okl_trn_c;
942 FETCH okl_trn_c INTO l_thpv_tbl(1).trans_number;
943 CLOSE okl_trn_c;
944 okl_trx_assets_pub.create_trx_ass_h_def(
945 p_api_version => p_api_version,
946 p_init_msg_list => p_init_msg_list,
947 x_return_status => x_return_status,
948 x_msg_count => x_msg_count,
949 x_msg_data => x_msg_data,
950 p_thpv_tbl => l_thpv_tbl,
951 x_thpv_tbl => x_thpv_tbl);
952
953 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
954 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
955 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
956 RAISE OKL_API.G_EXCEPTION_ERROR;
957 END IF;
958
959 --populate the TAS_Id to the old lines plsql table.
960 l_old_tlpv_tbl(1).TAS_ID := x_thpv_tbl(1).ID;
961
962 --dkagrawa added for Bug# 4723820 starts
963 OPEN l_conv_rate_csr (l_old_tlpv_tbl(1).dnz_khr_id);
964 FETCH l_conv_rate_csr INTO l_conv_type,l_conv_rate;
965 CLOSE l_conv_rate_csr;
966 IF l_conv_type = 'User' THEN
967 FOR i IN 1..l_old_tlpv_tbl.COUNT LOOP
968 l_old_tlpv_tbl(i).currency_conversion_rate := l_conv_rate;
969 END LOOP;
970 END IF;
971 --dkagrawa for Bug# 4723820 ends
972
973 --Create the old line in the okl_txl_assets table
974 okl_txl_assets_pub.create_txl_asset_def(
975 p_api_version => p_api_version,
976 p_init_msg_list => p_init_msg_list,
977 x_return_status => x_return_status,
978 x_msg_count => x_msg_count,
979 x_msg_data => x_msg_data,
980 p_tlpv_tbl => l_old_tlpv_tbl,
981 x_tlpv_tbl => x_old_tlpv_tbl);
982
983 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
984 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
985 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
986 RAISE OKL_API.G_EXCEPTION_ERROR;
987 END IF;
988
989 --Change done for making serial number optional.
990 IF l_old_iipv_tbl(1).serial_number is not NULL THEN
991
992 --populate the TAS_Id to the old items plsql table.
993 l_old_iipv_tbl(1).TAS_ID := x_thpv_tbl(1).ID;
994
995 --Create a old line in item instances also.
996 okl_txl_itm_insts_pub.create_txl_itm_insts(
997 p_api_version => p_api_version,
998 p_init_msg_list => p_init_msg_list,
999 x_return_status => x_return_status,
1000 x_msg_count => x_msg_count,
1001 x_msg_data => x_msg_data,
1002 p_iipv_tbl => l_old_iipv_tbl,
1003 x_iipv_tbl => x_old_iipv_tbl);
1004
1005 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1006 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1007 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1008 RAISE OKL_API.G_EXCEPTION_ERROR;
1009 END IF;
1010
1011 END IF;
1012 --populate the TAS_Id to the new lines plsql table.
1013 l_new_tlpv_tbl(1).TAS_ID := x_thpv_tbl(1).ID;
1014
1015 --dkagrawa added for Bug# 4723820 starts
1016 IF l_conv_type = 'User' THEN
1017 FOR i IN 1..l_new_tlpv_tbl.COUNT LOOP
1018 l_new_tlpv_tbl(i).currency_conversion_rate := l_conv_rate;
1019 END LOOP;
1020 END IF;
1021 --dkagrawa for Bug# 4723820 ends
1022
1023 --Create the new line in the okl_txl_assets table
1024 okl_txl_assets_pub.create_txl_asset_def(
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_tlpv_tbl => l_new_tlpv_tbl,
1031 x_tlpv_tbl => x_new_tlpv_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
1040 --Change done for making serial number optional
1041 IF l_new_iipv_tbl(1).serial_number is not NULL THEN
1042
1043 --populate the TAS_Id to the new items plsql table.
1044 l_new_iipv_tbl(1).TAS_ID := x_thpv_tbl(1).ID;
1045
1046 --Create a new line in item instances also.
1047 okl_txl_itm_insts_pub.create_txl_itm_insts(
1048 p_api_version => p_api_version,
1049 p_init_msg_list => p_init_msg_list,
1050 x_return_status => x_return_status,
1051 x_msg_count => x_msg_count,
1052 x_msg_data => x_msg_data,
1053 p_iipv_tbl => l_new_iipv_tbl,
1054 x_iipv_tbl => x_new_iipv_tbl);
1055
1056 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1057 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1058 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1059 RAISE OKL_API.G_EXCEPTION_ERROR;
1060 END IF;
1061
1062 END IF;
1063
1064 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1065 EXCEPTION
1066 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1067 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1068 l_api_name,
1069 G_PKG_NAME,
1070 'OKL_API.G_RET_STS_ERROR',
1071 x_msg_count,
1072 x_msg_data,
1073 '_PUB');
1074 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1075 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1076 l_api_name,
1077 G_PKG_NAME,
1078 'OKL_API.G_RET_STS_UNEXP_ERROR',
1079 x_msg_count,
1080 x_msg_data,
1081 '_PUB');
1082 WHEN OTHERS THEN
1083 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1084 l_api_name,
1085 G_PKG_NAME,
1086 'OTHERS',
1087 x_msg_count,
1088 x_msg_data,
1089 '_PUB');
1090
1091 END store_exchange_details;
1092
1093
1094
1095
1096
1097
1098
1099 PROCEDURE exchange(
1100 p_api_version IN NUMBER,
1101 p_init_msg_list IN VARCHAR2 := OKL_API.G_FALSE,
1102 p_tas_id IN NUMBER,
1103 x_return_status OUT NOCOPY VARCHAR2,
1104 x_msg_count OUT NOCOPY NUMBER,
1105 x_msg_data OUT NOCOPY VARCHAR2)
1106 AS
1107
1108 p_talv_tbl talv_tbl_type;
1109 x_talv_tbl talv_tbl_type;
1110
1111 p_thpv_tbl thpv_tbl_type;
1112 x_thpv_tbl thpv_tbl_type;
1113 p_no_data_found BOOLEAN;
1114
1115 p_itiv_tbl itiv_tbl_type;
1116 x_itiv_tbl itiv_tbl_type;
1117
1118 p_cplv_rec cplv_rec_type;
1119 x_cplv_rec cplv_rec_type;
1120
1121 p_cvmv_rec cvmv_rec_type;
1122 x_cvmv_rec cvmv_rec_type;
1123
1124 x_year NUMBER;
1125 l_api_name CONSTANT VARCHAR2(30) := 'EXCHANGE';
1126
1127 l_ct_line_id NUMBER;
1128 l_vendor_id1 NUMBER;
1129 l_vendor_id2 VARCHAR2(30);
1130 l_okc_party_roles_id NUMBER;
1131 l_instance_id NUMBER;
1132 l_asset_desc VARCHAR2(80);
1133 l_item_desc VARCHAR2(1995);
1134 l_id1_okx_asset_lines NUMBER;
1135
1136 CURSOR c_model_line(c_parent_line_id NUMBER) IS
1137 SELECT A.ID
1138 FROM OKC_K_LINES_V A,
1139 OKC_LINE_STYLES_B B
1140 WHERE A.CLE_ID = c_parent_line_id
1141 AND A.LSE_ID = B.ID
1142 AND B.LTY_CODE = 'ITEM';
1143
1144 CURSOR c_vendor(c_model_line_id NUMBER) IS
1145 SELECT B.ID
1146 FROM AP_SUPPLIERS A,
1147 OKC_K_PARTY_ROLES_B B
1148 WHERE A.VENDOR_ID = B.OBJECT1_ID1
1149 AND B.RLE_CODE = 'OKL_VENDOR'
1150 AND B.CLE_ID = c_model_line_id;
1151
1152 CURSOR c_new_vendor(c_id NUMBER) IS
1153 SELECT ID2
1154 FROM OKX_VENDORS_V
1155 where id1=c_id;
1156
1157 CURSOR c_asset_id1 (c_khr_id NUMBER,c_asset_id NUMBER,c_Asset_number VARCHAR2) IS
1158 SELECT CLE.ID ID1
1159 FROM OKC_K_LINES_B CLE,
1160 OKC_K_ITEMS CIM
1161 WHERE CLE.DNZ_CHR_ID=c_khr_id
1162 AND CIM.CLE_ID = CLE.ID
1163 AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
1164 AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID
1165 AND CIM.Object1_id1 = c_asset_id
1166 AND CLE.STS_CODE <> 'ABANDONED';
1167
1168
1169 BEGIN
1170
1171 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1172
1173 --Call start_activity to create savepoint, check compatibility and initialize message list
1174
1175 x_return_status := OKL_API.START_ACTIVITY(
1176 l_api_name
1177 ,p_init_msg_list
1178 ,'_PUB'
1179 ,x_return_status);
1180
1181 --Check if activity started successfully
1182
1183 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1184 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1185 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1186 RAISE OKL_API.G_EXCEPTION_ERROR;
1187 END IF;
1188
1189 -- probably i might have to take the transaction id/request id and
1190 -- use it to get the relevant asset details for the new asset and then
1191 -- use this info to update the FA tables
1192 -- update all the asset details
1193 p_talv_tbl(1).tas_id := p_tas_id;
1194 p_talv_tbl(1).tal_type := 'NAS';
1195 x_talv_tbl := OKL_EQUIPMENT_EXCHANGE_PVT.GET_TAL_REC(p_talv_tbl,p_no_data_found);
1196 parse_desc(x_talv_tbl(1).description,l_asset_desc,l_item_desc);
1197
1198 --Have to version the contract before doing any of the following tasks.
1199 p_cvmv_rec.chr_id := x_talv_tbl(1).dnz_khr_id;
1200
1201 okl_version_pub.version_contract(p_api_version => p_api_version,
1202 p_init_msg_list => p_init_msg_list,
1203 x_return_status => x_return_status,
1204 x_msg_count => x_msg_count,
1205 x_msg_data => x_msg_data,
1206 p_cvmv_rec => p_cvmv_rec,
1207 x_cvmv_rec => x_cvmv_rec,
1208 p_commit => 'T');
1209
1210 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1211 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1212 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1213 RAISE OKL_API.G_EXCEPTION_ERROR;
1214 END IF;
1215
1216
1217 -- This piece of code is added to fix Bug 2477684
1218 -- FA has changed their APIs as per the new standards of treating NULL as FND_API.G_MISS
1219 -- So we have to pass these constants if we want to update the values to NULL.
1220
1221 If (x_talv_tbl(1).model_number is NULL) THEN
1222 x_talv_tbl(1).model_number := FND_API.G_MISS_CHAR;
1223 END IF;
1224
1225 If (x_talv_tbl(1).manufacturer_name is NULL) THEN
1226 x_talv_tbl(1).manufacturer_name := FND_API.G_MISS_CHAR;
1227 END IF;
1228
1229
1230 okl_asset_details_pub.update_asset(p_api_version => p_api_version,
1231 p_init_msg_list => p_init_msg_list,
1232 x_return_status => x_return_status,
1233 x_msg_count => x_msg_count,
1234 x_msg_data => x_msg_data,
1235 p_asset_id => to_number(x_talv_tbl(1).dnz_asset_id),
1236 p_asset_number => x_talv_tbl(1).asset_number,
1237 px_asset_desc => l_asset_desc,
1238 px_model_no => x_talv_tbl(1).model_number,
1239 px_manufacturer => x_talv_tbl(1).manufacturer_name);
1240 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1241 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1242 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1243 RAISE OKL_API.G_EXCEPTION_ERROR;
1244 END IF;
1245
1246
1247 OPEN c_asset_id1(x_talv_tbl(1).dnz_khr_id,x_talv_tbl(1).dnz_asset_id,x_talv_tbl(1).asset_number);
1248 FETCH c_asset_id1 into l_id1_okx_asset_lines;
1249 CLOSE c_asset_id1;
1250
1251 -- This piece of code is changed to fix Bug 2477684
1252 -- The year should be updated even if it is NULL.. so remove this check.
1253
1254 -- IF x_talv_tbl(1).year_manufactured IS NOT NULL then
1255 okl_asset_details_pub.update_year(p_api_version => p_api_version,
1256 p_init_msg_list => p_init_msg_list,
1257 x_return_status => x_return_status,
1258 x_msg_count => x_msg_count,
1259 x_msg_data => x_msg_data,
1260 p_dnz_chr_id => x_talv_tbl(1).dnz_khr_id,
1261 p_parent_line_id => l_id1_okx_asset_lines,
1262 p_year => x_talv_tbl(1).year_manufactured,
1263 x_year => x_year);
1264 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1265 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1266 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1267 RAISE OKL_API.G_EXCEPTION_ERROR;
1268 END IF;
1269 -- END IF;
1270
1271
1272 update_item_description(p_api_version => p_api_version,
1273 p_init_msg_list => p_init_msg_list,
1274 x_return_status => x_return_status,
1275 x_msg_count => x_msg_count,
1276 x_msg_data => x_msg_data,
1277 p_dnz_chr_id => x_talv_tbl(1).dnz_khr_id,
1278 --p_parent_line_id => x_itiv_tbl(1).kle_id,
1279 p_parent_line_id => l_id1_okx_asset_lines,
1280 --akrangan bug 5362977 start
1281 --p_item_description => l_item_desc);
1282 p_item_description => l_asset_desc);
1283 --akrangan bug 5362977 end
1284 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1285 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1286 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1287 RAISE OKL_API.G_EXCEPTION_ERROR;
1288 END IF;
1289
1290
1291 --change for making sno optional
1292 p_itiv_tbl(1).tas_id := p_tas_id;
1293 p_itiv_tbl(1).tal_type := 'NAS';
1294 x_itiv_tbl := OKL_EQUIPMENT_EXCHANGE_PVT.GET_ITEM_REC(p_itiv_tbl,p_no_data_found);
1295 If not p_no_data_found then
1296
1297 l_instance_id := get_instance_id( x_itiv_tbl(1).instance_number_ib);
1298 update_serial_number(p_api_version => p_api_version,
1299 p_init_msg_list => p_init_msg_list,
1300 p_instance_id => l_instance_id,
1301 p_instance_name => x_itiv_tbl(1).instance_number_ib,
1302 p_serial_number => x_itiv_tbl(1).serial_number,
1303 p_inventory_item_id => x_itiv_tbl(1).inventory_item_id,
1304 x_return_status => x_return_status,
1305 x_msg_count => x_msg_count,
1306 x_msg_data => x_msg_data);
1307
1308 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1309 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1310 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1311 RAISE OKL_API.G_EXCEPTION_ERROR;
1312 END IF;
1313 end if; -- for p_no_data_found
1314 ---------------------------------------------------------
1315 OPEN c_model_line(x_talv_tbl(1).kle_id);
1316 FETCH c_model_line INTO l_ct_line_id;
1317 CLOSE c_model_line;
1318
1319 IF l_ct_line_id IS NOT NULL THEN
1320 OPEN c_vendor(l_ct_line_id);
1321 FETCH c_vendor INTO l_okc_party_roles_id;
1322 CLOSE c_vendor;
1323 END IF;
1324
1325 l_vendor_id1 := x_talv_tbl(1).supplier_id;
1326 IF l_okc_party_roles_id IS NOT NULL AND l_vendor_id1 IS NOT NULL THEN
1327
1328 OPEN c_new_vendor(x_talv_tbl(1).supplier_id);
1329 FETCH c_new_vendor INTO l_vendor_id2;
1330 CLOSE c_new_vendor;
1331
1332 p_cplv_rec.id := l_okc_party_roles_id;
1333 p_cplv_rec.object1_id1 := l_vendor_id1;
1334 p_cplv_rec.object1_id2 := l_vendor_id2;
1335
1336 okl_create_kle_pub.Update_party_roles_rec(p_api_version => p_api_version,
1337 p_init_msg_list => p_init_msg_list,
1338 x_return_status => x_return_status,
1339 x_msg_count => x_msg_count,
1340 x_msg_data => x_msg_data,
1341 p_cplv_rec => p_cplv_rec,
1342 x_cplv_rec => x_cplv_rec);
1343
1344
1345
1346 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1347 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1348 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1349 RAISE OKL_API.G_EXCEPTION_ERROR;
1350 END IF;
1351 ELSE
1352 NULL;
1353
1354 END IF;
1355
1356
1357 -- update the transaction table and set the status to processed or whatever
1358 --If the trx failed then i have to record the reason for failure and send message to the
1359 --concerned person abt this. How do we do this?
1360 IF x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1361 p_thpv_tbl(1).id := p_tas_id;
1362 p_thpv_tbl(1).tsu_code := 'PROCESSED';
1363 okl_trx_assets_pub.update_trx_ass_h_def(
1364 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_thpv_tbl => p_thpv_tbl,
1370 x_thpv_tbl => x_thpv_tbl);
1371 END IF;
1372
1373
1374
1375 EXCEPTION
1376 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1377 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1378 l_api_name,
1379 G_PKG_NAME,
1380 'OKL_API.G_RET_STS_ERROR',
1381 x_msg_count,
1382 x_msg_data,
1383 '_PUB');
1384 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1385 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1386 l_api_name,
1387 G_PKG_NAME,
1388 'OKL_API.G_RET_STS_UNEXP_ERROR',
1389 x_msg_count,
1390 x_msg_data,
1391 '_PUB');
1392 WHEN OTHERS THEN
1393 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1394 l_api_name,
1395 G_PKG_NAME,
1396 'OTHERS',
1397 x_msg_count,
1398 x_msg_data,
1399 '_PUB');
1400
1401 END exchange;
1402
1403 END okl_equipment_exchange_pvt;
1404