1 PACKAGE BODY OKL_ITI_PVT AS
2 /* $Header: OKLSITIB.pls 120.2 2006/07/11 10:21:57 dkagrawa noship $ */
3 -- Badrinath Kuchibholta
4 /************************ HAND-CODED *********************************/
5 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
6 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
7 G_ID2 CONSTANT VARCHAR2(200) := '#';
8 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
9 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
10
11 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
12 G_INVALID_VALUE CONSTANT VARCHAR2(200) := 'OKL_INVALID_VALUE';
13 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
14 G_TAL_LOOKUP_TYPE CONSTANT VARCHAR2(200) := 'OKL_TRANS_LINE_TYPE';
15 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
16 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
17 -- List validation procedures for quick reference
18 --1. validate_tas_id -- Attribute Validation
19 --2. Validate_tal_id -- Attribute Validation
20 --3. Validate_kle_id -- Attribute Validation
21 --5. validate_object_id1_new -- Attribute Validation
22 --6. validate_object_id2_new -- Attribute Validation
23 --7. validate_jtot_object_code_new -- Attribute Validation
24 --8. validate_object_id1_old -- Attribute Validation
25 --9. validate_object_id2_old -- Attribute Validation
26 --10. validate_jtot_object_code_old -- Attribute Validation
27 --11. validate_mfg_serial_number_yn -- Attribute Validation
28 --12. validate_inv_item_id -- Attribute Validation
29 --13. validate_inv_master_org_id -- Attribute Validation
30 --14. validate_inv_org_id -- Atrribute Validation
31 --Bug#Bug# 2697681 schema change : 11.5.9 Split asset by serial numbers validations
32 --15. Instance_id -- Attribute Validation
33 --16. Selected_for_split_flag -- Attribute Validation
34 --17. Asd_Id -- Attribute Validation
35 ------------------------------1----------------------------------------------
36 -- Start of Commnets
37 -- Badrinath Kuchibholta
38 -- Procedure Name : Validate_tas_id
39 -- Description : FK validation with OKL_TRX_ASSETS_V
40 -- Business Rules :
41 -- Parameters : OUT Return Status, IN Rec Info
42 -- Version : 1.0
43 -- End of Commnets
44
45 PROCEDURE validate_tas_id(x_return_status OUT NOCOPY VARCHAR2,
46 p_itiv_rec IN itiv_rec_type) IS
47 ln_dummy number := 0;
48 CURSOR c_tas_id_validate(p_id number) is
49 SELECT 1
50 FROM DUAL
51 WHERE EXISTS (SELECT '1'
52 FROM OKL_TRX_ASSETS
53 WHERE id = p_id);
54
55 BEGIN
56 -- initialize return status
57 x_return_status := OKC_API.G_RET_STS_SUCCESS;
58 -- data is required
59 IF (p_itiv_rec.tas_id = OKC_API.G_MISS_NUM) OR
60 (p_itiv_rec.tas_id IS NULL) THEN
61 -- halt validation as it is a required field
62 RAISE G_EXCEPTION_STOP_VALIDATION;
63 END IF;
64 -- Enforce Foreign Key
65 OPEN c_tas_id_validate(p_itiv_rec.tas_id);
66 IF c_tas_id_validate%NOTFOUND THEN
67 -- halt validation as it has no parent record
68 RAISE G_EXCEPTION_HALT_VALIDATION;
69 END IF;
70 FETCH c_tas_id_validate into ln_dummy;
71 CLOSE c_tas_id_validate;
72 IF (ln_dummy = 0) then
73 -- halt validation as it has no parent record
74 RAISE G_EXCEPTION_HALT_VALIDATION;
75 END IF;
76 EXCEPTION
77 WHEN G_EXCEPTION_STOP_VALIDATION then
78 -- We are here since the field is required
79 -- store SQL error message on message stack
80 OKC_API.set_message(p_app_name => G_APP_NAME,
81 p_msg_name => G_REQUIRED_VALUE,
82 p_token1 => G_COL_NAME_TOKEN,
83 p_token1_value => 'tas_id');
84 -- Notify Error
85 x_return_status := OKC_API.G_RET_STS_ERROR;
86 WHEN G_EXCEPTION_HALT_VALIDATION then
87 -- We are here b'cause we have no parent record
88 -- store SQL error message on message stack
89 OKC_API.set_message(p_app_name => G_APP_NAME,
90 p_msg_name => G_NO_MATCHING_RECORD,
91 p_token1 => G_COL_NAME_TOKEN,
92 p_token1_value => 'tas_id');
93 -- If the cursor is open then it has to be closed
94 IF c_tas_id_validate%ISOPEN THEN
95 CLOSE c_tas_id_validate;
96 END IF;
97 -- notify caller of an error
98 x_return_status := OKC_API.G_RET_STS_ERROR;
99 WHEN OTHERS THEN
100 -- store SQL error message on message stack
101 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
102 p_msg_name => G_UNEXPECTED_ERROR,
103 p_token1 => G_SQLCODE_TOKEN,
104 p_token1_value => SQLCODE,
105 p_token2 => G_SQLERRM_TOKEN,
106 p_token2_value => SQLERRM);
107 -- If the cursor is open then it has to be closed
108 IF c_tas_id_validate%ISOPEN THEN
109 CLOSE c_tas_id_validate;
110 END IF;
111 -- notify caller of an error as UNEXPETED error
112 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
113 END validate_tas_id;
114 -------------------------------2--------------------------------------------------
115 -- Start of Commnets
116 -- Badrinath Kuchibholta
117 -- Procedure Name : Validate_tal_id
118 -- Description : FK validation with OKL_TXL_ASSETS_V
119 -- Business Rules :
120 -- Parameters : OUT Return Status, IN Rec Info
121 -- Version : 1.0
122 -- End of Commnets
123
124 PROCEDURE validate_tal_id(x_return_status OUT NOCOPY VARCHAR2,
125 p_itiv_rec IN itiv_rec_type) IS
126 ln_dummy number := 0;
127 CURSOR c_tal_id_validate(p_id number) is
128 SELECT 1
129 FROM DUAL
130 WHERE EXISTS (SELECT '1'
131 FROM OKL_TXL_ASSETS_V
132 WHERE id = p_id);
133
134 BEGIN
135 -- initialize return status
136 x_return_status := OKC_API.G_RET_STS_SUCCESS;
137 -- data is required
138 IF (p_itiv_rec.tal_id = OKC_API.G_MISS_NUM) OR
139 (p_itiv_rec.tal_id IS NULL) THEN
140 -- halt validation as it is a optional field
141 RAISE G_EXCEPTION_STOP_VALIDATION;
142 END IF;
143 -- Enforce Foreign Key
144 OPEN c_tal_id_validate(p_itiv_rec.tal_id);
145 IF c_tal_id_validate%NOTFOUND THEN
146 -- halt validation as it has no parent record
147 RAISE G_EXCEPTION_HALT_VALIDATION;
148 END IF;
149 FETCH c_tal_id_validate into ln_dummy;
150 CLOSE c_tal_id_validate;
151 IF (ln_dummy = 0) then
152 -- halt validation as it has no parent record
153 RAISE G_EXCEPTION_HALT_VALIDATION;
154 END IF;
155 EXCEPTION
156 WHEN G_EXCEPTION_STOP_VALIDATION then
157 -- We are here since the field is optional
158 null;
159 WHEN G_EXCEPTION_HALT_VALIDATION then
160 -- We are here b'cause we have no parent record
161 -- store SQL error message on message stack
162 OKC_API.set_message(p_app_name => G_APP_NAME,
163 p_msg_name => G_NO_MATCHING_RECORD,
164 p_token1 => G_COL_NAME_TOKEN,
165 p_token1_value => 'tal_id');
166 -- If the cursor is open then it has to be closed
167 IF c_tal_id_validate%ISOPEN THEN
168 CLOSE c_tal_id_validate;
169 END IF;
170 -- notify caller of an error
171 x_return_status := OKC_API.G_RET_STS_ERROR;
172 WHEN OTHERS THEN
173 -- store SQL error message on message stack
174 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
175 p_msg_name => G_UNEXPECTED_ERROR,
176 p_token1 => G_SQLCODE_TOKEN,
177 p_token1_value => SQLCODE,
178 p_token2 => G_SQLERRM_TOKEN,
179 p_token2_value => SQLERRM);
180 -- If the cursor is open then it has to be closed
181 IF c_tal_id_validate%ISOPEN THEN
182 CLOSE c_tal_id_validate;
183 END IF;
184 -- notify caller of an error as UNEXPETED error
185 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
186 END validate_tal_id;
187 --------------------------------3-------------------------------------------------
188 -- Start of Commnets
189 -- Badrinath Kuchibholta
190 -- Procedure Name : Validate_kle_id
191 -- Description : FK validation with OKL_K_LINES_V
192 -- Business Rules :
193 -- Parameters : OUT Return Status, IN Rec Info
194 -- Version : 1.0
195 -- End of Commnets
196
197 PROCEDURE validate_kle_id(x_return_status OUT NOCOPY VARCHAR2,
198 p_itiv_rec IN itiv_rec_type) IS
199 ln_dummy number := 0;
200 CURSOR c_kle_id_validate(p_id number) is
201 SELECT 1
202 FROM DUAL
203 WHERE EXISTS (SELECT id
204 FROM OKL_K_LINES_V
205 WHERE id = p_id);
206
207 BEGIN
208 -- initialize return status
209 x_return_status := OKC_API.G_RET_STS_SUCCESS;
210 -- data is required
211 IF (p_itiv_rec.kle_id = OKC_API.G_MISS_NUM) OR
212 (p_itiv_rec.kle_id IS NULL) THEN
213 -- halt validation as it is a required field
214 RAISE G_EXCEPTION_STOP_VALIDATION;
215 END IF;
216 -- Enforce Foreign Key
217 OPEN c_kle_id_validate(p_itiv_rec.kle_id);
218 IF c_kle_id_validate%NOTFOUND THEN
219 -- halt validation as it has no parent record
220 RAISE G_EXCEPTION_HALT_VALIDATION;
221 END IF;
222 FETCH c_kle_id_validate into ln_dummy;
223 CLOSE c_kle_id_validate;
224 IF (ln_dummy = 0) then
225 -- halt validation as it has no parent record
226 RAISE G_EXCEPTION_HALT_VALIDATION;
227 END IF;
228 EXCEPTION
229 WHEN G_EXCEPTION_STOP_VALIDATION then
230 -- We are here since the field is required
231 -- store SQL error message on message stack
232 OKC_API.set_message(p_app_name => G_APP_NAME,
233 p_msg_name => G_REQUIRED_VALUE,
234 p_token1 => G_COL_NAME_TOKEN,
235 p_token1_value => 'kle_id');
236 -- Notify Error
237 x_return_status := OKC_API.G_RET_STS_ERROR;
238 WHEN G_EXCEPTION_HALT_VALIDATION then
239 -- We are here b'cause we have no parent record
240 -- store SQL error message on message stack
241 OKC_API.set_message(p_app_name => G_APP_NAME,
242 p_msg_name => G_NO_MATCHING_RECORD,
243 p_token1 => G_COL_NAME_TOKEN,
244 p_token1_value => 'kle_id');
245 -- If the cursor is open then it has to be closed
246 IF c_kle_id_validate%ISOPEN THEN
247 CLOSE c_kle_id_validate;
248 END IF;
249 -- notify caller of an error
250 x_return_status := OKC_API.G_RET_STS_ERROR;
251 WHEN OTHERS THEN
252 -- store SQL error message on message stack
253 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
254 p_msg_name => G_UNEXPECTED_ERROR,
255 p_token1 => G_SQLCODE_TOKEN,
256 p_token1_value => SQLCODE,
257 p_token2 => G_SQLERRM_TOKEN,
258 p_token2_value => SQLERRM);
259 -- If the cursor is open then it has to be closed
260 IF c_kle_id_validate%ISOPEN THEN
261 CLOSE c_kle_id_validate;
262 END IF;
263 -- notify caller of an error as UNEXPETED error
264 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
265 END validate_kle_id;
266 -----------------------------------7-----------------------------------------------
267 -- Start of Commnets
268 -- Badrinath Kuchibholta
269 -- Procedure Name : Validate_TAL_TYPE
270 -- Description : FK validation with FND COMMON LOOKUPS
271 -- Business Rules :
272 -- Parameters : OUT Return Status, IN Rec Info
273 -- Version : 1.0
274 -- End of Commnets
275
276 PROCEDURE validate_tal_type(x_return_status OUT NOCOPY VARCHAR2,
277 p_itiv_rec IN itiv_rec_type) IS
278 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
279 BEGIN
280 -- initialize return status
281 x_return_status := OKC_API.G_RET_STS_SUCCESS;
282 -- data is required
283 IF (p_itiv_rec.tal_type = OKC_API.G_MISS_CHAR) OR
284 (p_itiv_rec.tal_type IS NULL) THEN
285 -- halt validation as it is a required field
286 RAISE G_EXCEPTION_STOP_VALIDATION;
287 END IF;
288 -- Enforce Foreign Key
289 l_return_status := OKC_UTIL.check_lookup_code(G_TAL_LOOKUP_TYPE,
290 p_itiv_rec.tal_type);
291 IF l_return_status = x_return_status THEN
292 x_return_status := l_return_status;
293 ELSIF l_return_status <> x_return_status THEN
294 -- halt validation as it has no parent record
295 RAISE G_EXCEPTION_HALT_VALIDATION;
296 END IF;
297 EXCEPTION
298 WHEN G_EXCEPTION_STOP_VALIDATION then
299 -- We are here since the field is required
300 -- store SQL error message on message stack
301 OKC_API.set_message(p_app_name => G_APP_NAME,
302 p_msg_name => G_REQUIRED_VALUE,
303 p_token1 => G_COL_NAME_TOKEN,
304 p_token1_value => 'tal_type');
305 -- Notify Error
306 x_return_status := OKC_API.G_RET_STS_ERROR;
307 WHEN G_EXCEPTION_HALT_VALIDATION then
308 -- We are here b'cause we have no parent record
309 -- store SQL error message on message stack
310 OKC_API.set_message(p_app_name => G_APP_NAME,
311 p_msg_name => G_NO_MATCHING_RECORD,
312 p_token1 => G_COL_NAME_TOKEN,
313 p_token1_value => 'tal_type');
314 -- notify caller of an error
315 x_return_status := OKC_API.G_RET_STS_ERROR;
316 WHEN OTHERS THEN
317 -- store SQL error message on message stack
318 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
319 p_msg_name => G_UNEXPECTED_ERROR,
320 p_token1 => G_SQLCODE_TOKEN,
321 p_token1_value => SQLCODE,
322 p_token2 => G_SQLERRM_TOKEN,
323 p_token2_value => SQLERRM);
324 -- notify caller of an error as UNEXPETED error
325 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
326 END validate_tal_type;
327 ----------------------------------5----------------------------------------------
328 -- Start of Commnets
329 -- Badrinath Kuchibholta
330 -- Procedure Name : Validate_object_id1_new
331 -- Description : Validate against OKX_PARTY_SITE_USES_V
332 -- Business Rules :
333 -- Parameters : OUT Return Status, IN Rec Info
334 -- Version : 1.0
335 -- End of Commnets
336
337 PROCEDURE validate_object_id1_new(x_return_status OUT NOCOPY VARCHAR2,
338 p_itiv_rec IN itiv_rec_type) IS
339 ln_dummy number := 0;
340 CURSOR c_object_id1_new_validate(p_id1 OKL_TXL_ITM_INSTS_V.OBJECT_ID1_NEW%TYPE) is
341 SELECT 1
342 FROM DUAL
343 WHERE EXISTS (SELECT '1'
344 FROM OKX_PARTY_SITE_USES_V
345 WHERE id1 = p_id1);
346 -- FROM OKX_CUST_SITE_USES_V
347 BEGIN
348 -- initialize return status
349 x_return_status := OKC_API.G_RET_STS_SUCCESS;
350 -- data is required
351 IF (p_itiv_rec.object_id1_new = OKC_API.G_MISS_CHAR) OR
352 (p_itiv_rec.object_id1_new IS NULL) THEN
353 -- halt validation as it is a required field
354 RAISE G_EXCEPTION_STOP_VALIDATION;
355 END IF;
356 -- Enforce Foreign Key
357 OPEN c_object_id1_new_validate(p_itiv_rec.object_id1_new);
358 IF c_object_id1_new_validate%NOTFOUND THEN
359 -- halt validation as it has no parent record
360 RAISE G_EXCEPTION_HALT_VALIDATION;
361 END IF;
362 FETCH c_object_id1_new_validate into ln_dummy;
363 CLOSE c_object_id1_new_validate;
364 IF (ln_dummy = 0) then
365 -- halt validation as it has no parent record
366 RAISE G_EXCEPTION_HALT_VALIDATION;
367 END IF;
368 EXCEPTION
369 WHEN G_EXCEPTION_STOP_VALIDATION THEN
370 -- We are here since the field is required
371 -- store SQL error message on message stack
372 OKC_API.set_message(p_app_name => G_APP_NAME,
373 p_msg_name => G_REQUIRED_VALUE,
374 p_token1 => G_COL_NAME_TOKEN,
375 p_token1_value => 'object_id1_new');
376 -- Notify Error
377 x_return_status := OKC_API.G_RET_STS_ERROR;
378 WHEN G_EXCEPTION_HALT_VALIDATION THEN
379 -- We are here b'cause validation falied
380 -- store SQL error message on message stack
381 OKC_API.set_message(p_app_name => G_APP_NAME,
382 p_msg_name => G_NO_MATCHING_RECORD,
383 p_token1 => G_COL_NAME_TOKEN,
384 p_token1_value => 'object_id1_new');
385 -- If the cursor is open then it has to be closed
386 IF c_object_id1_new_validate%ISOPEN THEN
387 CLOSE c_object_id1_new_validate;
388 END IF;
389 -- notify caller of an error
390 x_return_status := OKC_API.G_RET_STS_ERROR;
391 WHEN OTHERS THEN
392 -- store SQL error message on message stack
393 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
394 p_msg_name => G_UNEXPECTED_ERROR,
395 p_token1 => G_SQLCODE_TOKEN,
396 p_token1_value => SQLCODE,
397 p_token2 => G_SQLERRM_TOKEN,
398 p_token2_value => SQLERRM);
399 -- If the cursor is open then it has to be closed
400 IF c_object_id1_new_validate%ISOPEN THEN
401 CLOSE c_object_id1_new_validate;
402 END IF;
403 -- notify caller of an error as UNEXPETED error
404 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
405 END validate_object_id1_new;
406 -----------------------------------6---------------------------------------------
407 -- Start of Commnets
408 -- Badrinath Kuchibholta
409 -- Procedure Name : Validate_object_id2_new
410 -- Description : Validate against OKX_PARTY_SITES_V
411 -- Business Rules :
412 -- Parameters : OUT Return Status, IN Rec Info
413 -- Version : 1.0
414 -- End of Commnets
415
416 PROCEDURE validate_object_id2_new(x_return_status OUT NOCOPY VARCHAR2,
417 p_itiv_rec IN itiv_rec_type) IS
418 ln_dummy number := 0;
419 CURSOR c_object_id2_new_validate(p_id1 OKL_TXL_ITM_INSTS_V.OBJECT_ID1_NEW%TYPE,
420 p_id2 OKL_TXL_ITM_INSTS_V.OBJECT_ID2_NEW%TYPE) is
421 SELECT 1
422 FROM DUAL
423 WHERE EXISTS (SELECT '1'
424 FROM OKX_PARTY_SITE_USES_V
425 WHERE id2 = p_id2
426 AND id1 = p_id1);
427 -- FROM OKX_CUST_SITE_USES_V
428 BEGIN
429 -- initialize return status
430 x_return_status := OKC_API.G_RET_STS_SUCCESS;
431 -- data is required
432 IF (p_itiv_rec.object_id2_new = OKC_API.G_MISS_CHAR) OR
433 (p_itiv_rec.object_id2_new IS NULL) THEN
434 -- halt validation as it is a required field
435 RAISE G_EXCEPTION_STOP_VALIDATION;
436 END IF;
437 -- Enforce Foreign Key
438 OPEN c_object_id2_new_validate(p_itiv_rec.object_id1_new,
439 p_itiv_rec.object_id2_new);
440 IF c_object_id2_new_validate%NOTFOUND THEN
441 -- halt validation as it has no parent record
442 RAISE G_EXCEPTION_HALT_VALIDATION;
443 END IF;
444 FETCH c_object_id2_new_validate into ln_dummy;
445 CLOSE c_object_id2_new_validate;
446 IF (ln_dummy = 0) then
447 -- halt validation as it has no parent record
448 RAISE G_EXCEPTION_HALT_VALIDATION;
449 END IF;
450 EXCEPTION
451 WHEN G_EXCEPTION_STOP_VALIDATION THEN
452 -- We are here since the field is required
453 -- store SQL error message on message stack
454 OKC_API.set_message(p_app_name => G_APP_NAME,
455 p_msg_name => G_REQUIRED_VALUE,
456 p_token1 => G_COL_NAME_TOKEN,
457 p_token1_value => 'object_id2_new');
458 -- Notify Error
459 x_return_status := OKC_API.G_RET_STS_ERROR;
460 WHEN G_EXCEPTION_HALT_VALIDATION THEN
461 -- We are here b'cause validation falied
462 -- store SQL error message on message stack
463 OKC_API.set_message(p_app_name => G_APP_NAME,
464 p_msg_name => G_NO_MATCHING_RECORD,
465 p_token1 => G_COL_NAME_TOKEN,
466 p_token1_value => 'object_id2_new');
467 -- If the cursor is open then it has to be closed
468 IF c_object_id2_new_validate%ISOPEN THEN
469 CLOSE c_object_id2_new_validate;
470 END IF;
471 -- notify caller of an error
472 x_return_status := OKC_API.G_RET_STS_ERROR;
473 WHEN OTHERS THEN
474 -- store SQL error message on message stack
475 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
476 p_msg_name => G_UNEXPECTED_ERROR,
477 p_token1 => G_SQLCODE_TOKEN,
478 p_token1_value => SQLCODE,
479 p_token2 => G_SQLERRM_TOKEN,
480 p_token2_value => SQLERRM);
481 -- If the cursor is open then it has to be closed
482 IF c_object_id2_new_validate%ISOPEN THEN
483 CLOSE c_object_id2_new_validate;
484 END IF;
485 -- notify caller of an error as UNEXPETED error
486 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
487 END validate_object_id2_new;
488 ------------------------------------7--------------------------------------------
489 -- Start of Commnets
490 -- Badrinath Kuchibholta
491 -- Procedure Name : Validate_jtot_object_code_new
492 -- Description : Check if same pre seeded jtot object
493 -- Business Rules :
494 -- Parameters : OUT Return Status, IN Rec Info
495 -- Version : 1.0
496 -- End of Commnets
497
498 PROCEDURE validate_jtot_object_code_new(x_return_status OUT NOCOPY VARCHAR2,
499 p_itiv_rec IN itiv_rec_type) IS
500 ln_dummy number := 0;
501 CURSOR c_jtot_code_new_validate(
502 p_jtot_code_new OKL_TXL_ITM_INSTS_V.JTOT_OBJECT_CODE_NEW%TYPE) is
503 SELECT 1
504 FROM DUAL
505 WHERE EXISTS (SELECT '1'
506 FROM JTF_OBJECTS_B
507 WHERE object_code = p_jtot_code_new);
508 BEGIN
509 -- initialize return status
510 x_return_status := OKC_API.G_RET_STS_SUCCESS;
511 -- data is required
512 IF (p_itiv_rec.jtot_object_code_new = OKC_API.G_MISS_CHAR) OR
513 (p_itiv_rec.jtot_object_code_new IS NULL) THEN
514 -- halt validation as it is a required field
515 RAISE G_EXCEPTION_STOP_VALIDATION;
516 END IF;
517 OPEN c_jtot_code_new_validate(p_itiv_rec.jtot_object_code_new);
518 IF c_jtot_code_new_validate%NOTFOUND THEN
519 -- halt validation as it has parent record
520 RAISE G_EXCEPTION_HALT_VALIDATION;
521 END IF;
522 FETCH c_jtot_code_new_validate INTO ln_dummy;
523 CLOSE c_jtot_code_new_validate;
524 -- Check if same pre seeded jtot object
525 IF ln_dummy = 0 THEN
526 -- halt validation as it has parent record
527 RAISE G_EXCEPTION_HALT_VALIDATION;
528 END IF;
529 EXCEPTION
530 WHEN G_EXCEPTION_STOP_VALIDATION then
531 -- We are here since the field is required
532 -- store SQL error message on message stack
533 OKC_API.set_message(p_app_name => G_APP_NAME,
534 p_msg_name => G_REQUIRED_VALUE,
535 p_token1 => G_COL_NAME_TOKEN,
536 p_token1_value => 'jtot_object_code_new');
537 -- Notify Error
538 x_return_status := OKC_API.G_RET_STS_ERROR;
539 WHEN G_EXCEPTION_HALT_VALIDATION then
540 -- We are here b'cause validation falied
541 -- store SQL error message on message stack
542 OKC_API.set_message(p_app_name => G_APP_NAME,
543 p_msg_name => G_NO_MATCHING_RECORD,
544 p_token1 => G_COL_NAME_TOKEN,
545 p_token1_value => 'jtot_object_code_new');
546 -- If the cursor is open then it has to be closed
547 IF c_jtot_code_new_validate%ISOPEN THEN
548 CLOSE c_jtot_code_new_validate;
549 END IF;
550 -- notify caller of an error
551 x_return_status := OKC_API.G_RET_STS_ERROR;
552 WHEN OTHERS THEN
553 -- store SQL error message on message stack
554 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
555 p_msg_name => G_UNEXPECTED_ERROR,
556 p_token1 => G_SQLCODE_TOKEN,
557 p_token1_value => SQLCODE,
558 p_token2 => G_SQLERRM_TOKEN,
559 p_token2_value => SQLERRM);
560 -- If the cursor is open then it has to be closed
561 IF c_jtot_code_new_validate%ISOPEN THEN
562 CLOSE c_jtot_code_new_validate;
563 END IF;
564 -- notify caller of an error as UNEXPETED error
565 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
566 END validate_jtot_object_code_new;
567 -------------------------------------8-------------------------------------------
568 -- Start of Commnets
569 -- Badrinath Kuchibholta
570 -- Procedure Name : Validate_object_id1_old
571 -- Description : Validate against OKX_CUST_SITE_USES_V
572 -- Business Rules :
573 -- Parameters : OUT Return Status, IN Rec Info
574 -- Version : 1.0
575 -- End of Commnets
576
577 PROCEDURE validate_object_id1_old(x_return_status OUT NOCOPY VARCHAR2,
578 p_itiv_rec IN itiv_rec_type) IS
579 ln_dummy number := 0;
580 CURSOR c_object_id1_old_validate(p_id1 OKL_TXL_ITM_INSTS_V.OBJECT_ID1_OLD%TYPE) is
581 SELECT 1
582 FROM DUAL
583 WHERE EXISTS (SELECT '1'
584 FROM OKX_PARTY_SITE_USES_V
585 WHERE id1 = p_id1);
586 -- FROM OKX_CUST_SITE_USES_V
587
588 BEGIN
589 -- initialize return status
590 x_return_status := OKC_API.G_RET_STS_SUCCESS;
591 -- data is required
592 IF (p_itiv_rec.object_id1_old = OKC_API.G_MISS_CHAR) OR
593 (p_itiv_rec.object_id1_old IS NULL) THEN
594 -- halt validation as it is a optional field
595 RAISE G_EXCEPTION_STOP_VALIDATION;
596 END IF;
597 -- Enforce Foreign Key
598 OPEN c_object_id1_old_validate(p_itiv_rec.object_id1_old);
599 IF c_object_id1_old_validate%NOTFOUND THEN
600 -- halt validation as it has no parent record
601 RAISE G_EXCEPTION_HALT_VALIDATION;
602 END IF;
603 FETCH c_object_id1_old_validate into ln_dummy;
604 CLOSE c_object_id1_old_validate;
605 IF (ln_dummy = 0) then
606 -- halt validation as it has no parent record
607 RAISE G_EXCEPTION_HALT_VALIDATION;
608 END IF;
609 EXCEPTION
610 WHEN G_EXCEPTION_STOP_VALIDATION THEN
611 -- We are here since the field is optional
612 null;
613 WHEN G_EXCEPTION_HALT_VALIDATION THEN
614 -- We are here b'cause validation falied
615 -- store SQL error message on message stack
616 OKC_API.set_message(p_app_name => G_APP_NAME,
617 p_msg_name => G_NO_MATCHING_RECORD,
618 p_token1 => G_COL_NAME_TOKEN,
619 p_token1_value => 'object_id1_old');
620 -- If the cursor is open then it has to be closed
621 IF c_object_id1_old_validate%ISOPEN THEN
622 CLOSE c_object_id1_old_validate;
623 END IF;
624 -- notify caller of an error
625 x_return_status := OKC_API.G_RET_STS_ERROR;
626 WHEN OTHERS THEN
627 -- store SQL error message on message stack
628 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
629 p_msg_name => G_UNEXPECTED_ERROR,
630 p_token1 => G_SQLCODE_TOKEN,
631 p_token1_value => SQLCODE,
632 p_token2 => G_SQLERRM_TOKEN,
633 p_token2_value => SQLERRM);
634 -- If the cursor is open then it has to be closed
635 IF c_object_id1_old_validate%ISOPEN THEN
636 CLOSE c_object_id1_old_validate;
637 END IF;
638 -- notify caller of an error as UNEXPETED error
639 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
640 END validate_object_id1_old;
641 --------------------------------------9------------------------------------------
642 -- Start of Commnets
643 -- Badrinath Kuchibholta
644 -- Procedure Name : Validate_object_id2_old
645 -- Description : Validate against OKX_CUST_SITE_USES_V
646 -- Business Rules :
647 -- Parameters : OUT Return Status, IN Rec Info
648 -- Version : 1.0
649 -- End of Commnets
650
651 PROCEDURE validate_object_id2_old(x_return_status OUT NOCOPY VARCHAR2,
652 p_itiv_rec IN itiv_rec_type) IS
653 ln_dummy number := 0;
654 CURSOR c_object_id2_old_validate(p_id1 OKL_TXL_ITM_INSTS_V.OBJECT_ID1_OLD%TYPE,
655 p_id2 OKL_TXL_ITM_INSTS_V.OBJECT_ID2_OLD%TYPE) is
656 SELECT 1
657 FROM DUAL
658 WHERE EXISTS (SELECT '1'
659 FROM OKX_PARTY_SITE_USES_V
660 WHERE id2 = p_id2
661 AND id1 = p_id1);
662 -- FROM OKX_CUST_SITE_USES_V
663
664 BEGIN
665 -- initialize return status
666 x_return_status := OKC_API.G_RET_STS_SUCCESS;
667 -- data is required
668 IF (p_itiv_rec.object_id2_old = OKC_API.G_MISS_CHAR) OR
669 (p_itiv_rec.object_id2_old IS NULL) THEN
670 -- halt validation as it is a optional field
671 RAISE G_EXCEPTION_STOP_VALIDATION;
672 END IF;
673 -- Enforce Foreign Key
674 OPEN c_object_id2_old_validate(p_itiv_rec.object_id1_old,
675 p_itiv_rec.object_id2_old);
676 IF c_object_id2_old_validate%NOTFOUND THEN
677 -- halt validation as it has no parent record
678 RAISE G_EXCEPTION_HALT_VALIDATION;
679 END IF;
680 FETCH c_object_id2_old_validate into ln_dummy;
681 CLOSE c_object_id2_old_validate;
682 IF (ln_dummy = 0) then
683 -- halt validation as it has no parent record
684 RAISE G_EXCEPTION_HALT_VALIDATION;
685 END IF;
686 EXCEPTION
687 WHEN G_EXCEPTION_STOP_VALIDATION THEN
688 -- We are here since the field is optional
689 null;
690 WHEN G_EXCEPTION_HALT_VALIDATION THEN
691 -- We are here b'cause validation falied
692 -- store SQL error message on message stack
693 OKC_API.set_message(p_app_name => G_APP_NAME,
694 p_msg_name => G_NO_MATCHING_RECORD,
695 p_token1 => G_COL_NAME_TOKEN,
696 p_token1_value => 'object_id2_old');
697 -- If the cursor is open then it has to be closed
698 IF c_object_id2_old_validate%ISOPEN THEN
699 CLOSE c_object_id2_old_validate;
700 END IF;
701 -- notify caller of an error
702 x_return_status := OKC_API.G_RET_STS_ERROR;
703 WHEN OTHERS THEN
704 -- store SQL error message on message stack
705 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
706 p_msg_name => G_UNEXPECTED_ERROR,
707 p_token1 => G_SQLCODE_TOKEN,
708 p_token1_value => SQLCODE,
709 p_token2 => G_SQLERRM_TOKEN,
710 p_token2_value => SQLERRM);
711 -- If the cursor is open then it has to be closed
712 IF c_object_id2_old_validate%ISOPEN THEN
713 CLOSE c_object_id2_old_validate;
714 END IF;
715 -- notify caller of an error as UNEXPETED error
716 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
717 END validate_object_id2_old;
718 ---------------------------------------10-----------------------------------------
719 -- Start of Commnets
720 -- Badrinath Kuchibholta
721 -- Procedure Name : Validate_jtot_object_code_old
722 -- Description : Check if same pre seeded jtot object
723 -- Business Rules :
724 -- Parameters : OUT Return Status, IN Rec Info
725 -- Version : 1.0
726 -- End of Commnets
727
728 PROCEDURE validate_jtot_object_code_old(x_return_status OUT NOCOPY VARCHAR2,
729 p_itiv_rec IN itiv_rec_type) IS
730 ln_dummy number := 0;
731 CURSOR c_jtot_code_old_validate(
732 p_jtot_code_old OKL_TXL_ITM_INSTS_V.JTOT_OBJECT_CODE_OLD%TYPE) is
733 SELECT 1
734 FROM DUAL
735 WHERE EXISTS (SELECT '1'
736 FROM JTF_OBJECTS_B
737 WHERE object_code = p_jtot_code_old);
738 BEGIN
739 -- initialize return status
740 x_return_status := OKC_API.G_RET_STS_SUCCESS;
741 -- data is required
742 IF (p_itiv_rec.jtot_object_code_old = OKC_API.G_MISS_CHAR) OR
743 (p_itiv_rec.jtot_object_code_old IS NULL) THEN
744 -- halt validation as it is a optional field
745 RAISE G_EXCEPTION_STOP_VALIDATION;
746 END IF; OPEN c_jtot_code_old_validate(p_itiv_rec.jtot_object_code_old);
747 IF c_jtot_code_old_validate%NOTFOUND THEN
748 -- halt validation as it has parent record
749 RAISE G_EXCEPTION_HALT_VALIDATION;
750 END IF;
751 FETCH c_jtot_code_old_validate INTO ln_dummy;
752 CLOSE c_jtot_code_old_validate;
753 -- Check if same pre seeded jtot object
754 IF ln_dummy = 0 THEN
755 -- halt validation as it has parent record
756 RAISE G_EXCEPTION_HALT_VALIDATION;
757 END IF;
758 EXCEPTION
759 WHEN G_EXCEPTION_STOP_VALIDATION then
760 -- We are here since the field is optional
761 null;
762 WHEN G_EXCEPTION_HALT_VALIDATION then
763 -- We are here b'cause validation falied
764 -- store SQL error message on message stack
765 OKC_API.set_message(p_app_name => G_APP_NAME,
766 p_msg_name => G_NO_MATCHING_RECORD,
767 p_token1 => G_COL_NAME_TOKEN,
768 p_token1_value => 'jtot_object_code_old');
769 -- If the cursor is open then it has to be closed
770 IF c_jtot_code_old_validate%ISOPEN THEN
771 CLOSE c_jtot_code_old_validate;
772 END IF;
773 -- notify caller of an error
774 x_return_status := OKC_API.G_RET_STS_ERROR;
775 WHEN OTHERS THEN
776 -- store SQL error message on message stack
777 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
778 p_msg_name => G_UNEXPECTED_ERROR,
779 p_token1 => G_SQLCODE_TOKEN,
780 p_token1_value => SQLCODE,
781 p_token2 => G_SQLERRM_TOKEN,
782 p_token2_value => SQLERRM);
783 -- If the cursor is open then it has to be closed
784 IF c_jtot_code_old_validate%ISOPEN THEN
785 CLOSE c_jtot_code_old_validate;
786 END IF;
787 -- notify caller of an error as UNEXPETED error
788 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
789 END validate_jtot_object_code_old;
790 ----------------------------------------11----------------------------------------
791 -- Start of Commnets
792 -- Badrinath Kuchibholta
793 -- Procedure Name : Validate_mfg_serial_number_yn
794 -- Description : Check Constraint for Y,N
795 -- Business Rules :
796 -- Parameters : OUT Return Status, IN Rec Info
797 -- Version : 1.0
798 -- End of Commnets
799
800 PROCEDURE validate_mfg_serial_number_yn(x_return_status OUT NOCOPY VARCHAR2,
801 p_itiv_rec IN itiv_rec_type) IS
802 BEGIN
803 -- initialize return status
804 x_return_status := OKC_API.G_RET_STS_SUCCESS;
805 -- data is required
806 IF (p_itiv_rec.mfg_serial_number_yn = OKC_API.G_MISS_CHAR) OR
807 (p_itiv_rec.mfg_serial_number_yn IS NULL) THEN
808 -- halt validation as it is a required field
809 RAISE G_EXCEPTION_STOP_VALIDATION;
810 END IF;
811 -- See that in Y,N
812 IF p_itiv_rec.mfg_serial_number_yn not in ('Y','N') THEN
813 -- halt validation as it has parent record
814 RAISE G_EXCEPTION_HALT_VALIDATION;
815 END IF;
816 EXCEPTION
817 WHEN G_EXCEPTION_STOP_VALIDATION then
818 -- We are here since the field is required
819 -- store SQL error message on message stack
820 OKC_API.set_message(p_app_name => G_APP_NAME,
821 p_msg_name => G_REQUIRED_VALUE,
822 p_token1 => G_COL_NAME_TOKEN,
823 p_token1_value => 'mfg_serial_number_yn');
824 -- Notify Error
825 x_return_status := OKC_API.G_RET_STS_ERROR;
826 WHEN G_EXCEPTION_HALT_VALIDATION then
827 -- We are here b'cause validation falied
828 -- store SQL error message on message stack
829 OKC_API.set_message(p_app_name => G_APP_NAME,
830 p_msg_name => G_INVALID_VALUE,
831 p_token1 => G_COL_NAME_TOKEN,
832 p_token1_value => 'mfg_serial_number_yn');
833 -- notify caller of an error
834 x_return_status := OKC_API.G_RET_STS_ERROR;
835 WHEN OTHERS THEN
836 -- store SQL error message on message stack
837 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
838 p_msg_name => G_UNEXPECTED_ERROR,
839 p_token1 => G_SQLCODE_TOKEN,
840 p_token1_value => SQLCODE,
841 p_token2 => G_SQLERRM_TOKEN,
842 p_token2_value => SQLERRM);
843 -- notify caller of an error as UNEXPETED error
844 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
845 END validate_mfg_serial_number_yn;
846
847 ------------------------------------------12--------------------------------------
848 -- Start of Commnets
849 -- Badrinath Kuchibholta
850 -- Procedure Name : Validate_inv_item_id
851 -- Description : FK validation with OKX_SYSTEM_ITEMS_V
852 -- Business Rules :
853 -- Parameters : OUT Return Status, IN Rec Info
854 -- Version : 1.0
855 -- End of Commnets
856
857 PROCEDURE validate_inv_item_id(x_return_status OUT NOCOPY VARCHAR2,
858 p_itiv_rec IN itiv_rec_type) IS
859 ln_dummy number := 0;
860
861 CURSOR c_inv_item_id_validate(p_id number) is
862 SELECT 1
863 FROM DUAL
864 WHERE EXISTS (SELECT '1'
865 FROM OKX_SYSTEM_ITEMS_V
866 WHERE id1 = p_id);
867
868 BEGIN
869 -- initialize return status
870 x_return_status := OKC_API.G_RET_STS_SUCCESS;
871 -- data is required
872 IF (p_itiv_rec.inventory_item_id = OKC_API.G_MISS_NUM) OR
873 (p_itiv_rec.inventory_item_id IS NULL) THEN
874 -- halt validation as it is a required field
875 RAISE G_EXCEPTION_STOP_VALIDATION;
876 END IF;
877 -- Enforce Foreign Key
878 OPEN c_inv_item_id_validate(p_itiv_rec.inventory_item_id);
879 IF c_inv_item_id_validate%NOTFOUND THEN
880 -- halt validation as it has parent record
881 RAISE G_EXCEPTION_HALT_VALIDATION;
882 END IF;
883 FETCH c_inv_item_id_validate into ln_dummy;
884 CLOSE c_inv_item_id_validate;
885 IF (ln_dummy = 0) then
886 -- halt validation as it has parent record
887 RAISE G_EXCEPTION_HALT_VALIDATION;
888 END IF;
889 EXCEPTION
890 WHEN G_EXCEPTION_STOP_VALIDATION then
891 -- We are here since the field is required
892 -- store SQL error message on message stack
893 OKC_API.set_message(p_app_name => G_APP_NAME,
894 p_msg_name => G_REQUIRED_VALUE,
895 p_token1 => G_COL_NAME_TOKEN,
896 p_token1_value => 'inventory_item_id');
897 -- Notify Error
898 x_return_status := OKC_API.G_RET_STS_ERROR;
899 WHEN G_EXCEPTION_HALT_VALIDATION then
900 -- We are here b'cause we have no parent record
901 -- store SQL error message on message stack
902 OKC_API.set_message(p_app_name => G_APP_NAME,
903 p_msg_name => G_NO_MATCHING_RECORD,
904 p_token1 => G_COL_NAME_TOKEN,
905 p_token1_value => 'inventory_item_id');
906 -- If the cursor is open then it has to be closed
907 IF c_inv_item_id_validate%ISOPEN THEN
908 CLOSE c_inv_item_id_validate;
909 END IF;
910 -- notify caller of an error
911 x_return_status := OKC_API.G_RET_STS_ERROR;
912 WHEN OTHERS THEN
913 -- store SQL error message on message stack
914 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
915 p_msg_name => G_UNEXPECTED_ERROR,
916 p_token1 => G_SQLCODE_TOKEN,
917 p_token1_value => SQLCODE,
918 p_token2 => G_SQLERRM_TOKEN,
919 p_token2_value => SQLERRM);
920 -- If the cursor is open then it has to be closed
921 IF c_inv_item_id_validate%ISOPEN THEN
922 CLOSE c_inv_item_id_validate;
923 END IF;
924 -- notify caller of an error as UNEXPETED error
925 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
926 END validate_inv_item_id;
927 --------------------------------------------13------------------------------------
928 -- Start of Commnets
929 -- Badrinath Kuchibholta
930 -- Procedure Name : Validate_inv_master_org__id
931 -- Description : FK validation with OKX_SYSTEM_ITEMS_V
932 -- Business Rules :
933 -- Parameters : OUT Return Status, IN Rec Info
934 -- Version : 1.0
935 -- End of Commnets
936
937 PROCEDURE validate_inv_master_org_id(x_return_status OUT NOCOPY VARCHAR2,
938 p_itiv_rec IN itiv_rec_type) IS
939 ln_dummy number := 0;
940 CURSOR c_inv_master_org_id_validate(p_id number) is
941 SELECT 1
942 FROM DUAL
943 WHERE EXISTS (SELECT '1'
944 FROM OKX_SYSTEM_ITEMS_V
945 WHERE id2 = p_id);
946
947 BEGIN
948 -- initialize return status
949 x_return_status := OKC_API.G_RET_STS_SUCCESS;
950 -- data is required
951 IF (p_itiv_rec.inv_master_org_id = OKC_API.G_MISS_NUM) OR
952 (p_itiv_rec.inv_master_org_id IS NULL) THEN
953 -- halt validation as it is a optional field
954 RAISE G_EXCEPTION_STOP_VALIDATION;
955 END IF;
956 -- Enforce Foreign Key
957 OPEN c_inv_master_org_id_validate(p_itiv_rec.inv_master_org_id);
958 IF c_inv_master_org_id_validate%NOTFOUND THEN
959 -- halt validation as it has parent record
960 RAISE G_EXCEPTION_HALT_VALIDATION;
961 END IF;
962 FETCH c_inv_master_org_id_validate into ln_dummy;
963 CLOSE c_inv_master_org_id_validate;
964 IF (ln_dummy = 0) then
965 -- halt validation as it has parent record
966 RAISE G_EXCEPTION_HALT_VALIDATION;
967 END IF;
968 EXCEPTION
969 WHEN G_EXCEPTION_STOP_VALIDATION then
970 -- We are here since the field is optional
971 null;
972 WHEN G_EXCEPTION_HALT_VALIDATION then
973 -- We are here b'cause we have no parent record
974 -- store SQL error message on message stack
975 OKC_API.set_message(p_app_name => G_APP_NAME,
976 p_msg_name => G_NO_MATCHING_RECORD,
977 p_token1 => G_COL_NAME_TOKEN,
978 p_token1_value => 'inv_master_org_id');
979 -- If the cursor is open then it has to be closed
980 IF c_inv_master_org_id_validate%ISOPEN THEN
981 CLOSE c_inv_master_org_id_validate;
982 END IF;
983 -- notify caller of an error
984 x_return_status := OKC_API.G_RET_STS_ERROR;
985 WHEN OTHERS THEN
986 -- store SQL error message on message stack
987 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
988 p_msg_name => G_UNEXPECTED_ERROR,
989 p_token1 => G_SQLCODE_TOKEN,
990 p_token1_value => SQLCODE,
991 p_token2 => G_SQLERRM_TOKEN,
992 p_token2_value => SQLERRM);
993 -- If the cursor is open then it has to be closed
994 IF c_inv_master_org_id_validate%ISOPEN THEN
995 CLOSE c_inv_master_org_id_validate;
996 END IF;
997 -- notify caller of an error as UNEXPETED error
998 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
999 END validate_inv_master_org_id;
1000
1001 --------------------------------------14------------------------------------------
1002 -- Start of Commnets
1003 -- Badrinath Kuchibholta
1004 -- Procedure Name : validate_inv_org_id
1005 -- Description : Validate against OKX_ORGANIZATION_DEFS_V
1006 -- Business Rules :
1007 -- Parameters : OUT Return Status, IN Rec Info
1008 -- Version : 1.0
1009 -- End of Commnets
1010
1011 PROCEDURE validate_inv_org_id(x_return_status OUT NOCOPY VARCHAR2,
1012 p_itiv_rec IN itiv_rec_type) IS
1013 ln_dummy number := 0;
1014 CURSOR c_inventory_org_id_validate(p_id1 OKX_ORGANIZATION_DEFS_V.ID1%TYPE) is
1015 SELECT 1
1016 FROM DUAL
1017 WHERE EXISTS (SELECT '1'
1018 FROM OKX_ORGANIZATION_DEFS_V
1019 WHERE id2 = G_ID2
1020 AND organization_type = 'INV'
1021 AND id1 = p_id1);
1022 BEGIN
1023 -- initialize return status
1024 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1025 -- data is required
1026 IF (p_itiv_rec.inventory_org_id = OKC_API.G_MISS_NUM) OR
1027 (p_itiv_rec.inventory_org_id IS NULL) THEN
1028 -- halt validation as it is a optional field
1029 RAISE G_EXCEPTION_STOP_VALIDATION;
1030 END IF;
1031 -- Enforce Foreign Key
1032 OPEN c_inventory_org_id_validate(p_itiv_rec.inventory_org_id);
1033 IF c_inventory_org_id_validate%NOTFOUND THEN
1034 -- halt validation as it has no parent record
1035 RAISE G_EXCEPTION_HALT_VALIDATION;
1036 END IF;
1037 FETCH c_inventory_org_id_validate into ln_dummy;
1038 CLOSE c_inventory_org_id_validate;
1039 IF (ln_dummy = 0) then
1040 -- halt validation as it has no parent record
1041 RAISE G_EXCEPTION_HALT_VALIDATION;
1042 END IF;
1043 EXCEPTION
1044 WHEN G_EXCEPTION_STOP_VALIDATION THEN
1045 -- We are here since the field is optional
1046 null;
1047 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1048 -- We are here b'cause validation falied
1049 -- store SQL error message on message stack
1050 OKC_API.set_message(p_app_name => G_APP_NAME,
1051 p_msg_name => G_NO_MATCHING_RECORD,
1052 p_token1 => G_COL_NAME_TOKEN,
1053 p_token1_value => 'inventory_org_id');
1054 -- If the cursor is open then it has to be closed
1055 IF c_inventory_org_id_validate%ISOPEN THEN
1056 CLOSE c_inventory_org_id_validate;
1057 END IF;
1058 -- notify caller of an error
1059 x_return_status := OKC_API.G_RET_STS_ERROR;
1060 WHEN OTHERS THEN
1061 -- store SQL error message on message stack
1062 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1063 p_msg_name => G_UNEXPECTED_ERROR,
1064 p_token1 => G_SQLCODE_TOKEN,
1065 p_token1_value => SQLCODE,
1066 p_token2 => G_SQLERRM_TOKEN,
1067 p_token2_value => SQLERRM);
1068 -- If the cursor is open then it has to be closed
1069 IF c_inventory_org_id_validate%ISOPEN THEN
1070 CLOSE c_inventory_org_id_validate;
1071 END IF;
1072 -- notify caller of an error as UNEXPETED error
1073 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1074 END validate_inv_org_id;
1075 --------------------------------------15----------------------------------------
1076 -- Start of Commnets
1077 -- avsingh
1078 -- Procedure Name : validate_instance_id
1079 -- Description : Validate against CSI_ITEM_INSTANCES
1080 -- Business Rules :
1081 -- Parameters : OUT Return Status, IN Rec Info
1082 -- Version : 1.0
1083 -- End of Commnets
1084
1085 PROCEDURE validate_instance_id(x_return_status OUT NOCOPY VARCHAR2,
1086 p_itiv_rec IN itiv_rec_type) IS
1087 ln_dummy number := 0;
1088 CURSOR c_instance_id_validate(p_instance_id NUMBER) is
1089 SELECT 1
1090 FROM DUAL
1091 WHERE EXISTS (SELECT '1'
1092 FROM CSI_ITEM_INSTANCES
1093 WHERE instance_id = p_instance_id
1094 );
1095 BEGIN
1096 -- initialize return status
1097 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1098 -- data is required
1099 IF (p_itiv_rec.instance_id = OKC_API.G_MISS_NUM) OR
1100 (p_itiv_rec.instance_id IS NULL) THEN
1101 -- halt validation as it is a optional field
1102 RAISE G_EXCEPTION_STOP_VALIDATION;
1103 END IF;
1104 -- Enforce Foreign Key
1105 OPEN c_instance_id_validate(p_itiv_rec.instance_id);
1106 IF c_instance_id_validate%NOTFOUND THEN
1107 -- halt validation as it has no parent record
1108 RAISE G_EXCEPTION_HALT_VALIDATION;
1109 END IF;
1110 FETCH c_instance_id_validate into ln_dummy;
1111 CLOSE c_instance_id_validate;
1112 IF (ln_dummy = 0) then
1113 -- halt validation as it has no parent record
1114 RAISE G_EXCEPTION_HALT_VALIDATION;
1115 END IF;
1116 EXCEPTION
1117 WHEN G_EXCEPTION_STOP_VALIDATION THEN
1118 -- We are here since the field is optional
1119 null;
1120 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1121 -- We are here b'cause validation falied
1122 -- store SQL error message on message stack
1123 OKC_API.set_message(p_app_name => G_APP_NAME,
1124 p_msg_name => G_NO_MATCHING_RECORD,
1125 p_token1 => G_COL_NAME_TOKEN,
1126 p_token1_value => 'instance_id');
1127 -- If the cursor is open then it has to be closed
1128 IF c_instance_id_validate%ISOPEN THEN
1129 CLOSE c_instance_id_validate;
1130 END IF;
1131 -- notify caller of an error
1132 x_return_status := OKC_API.G_RET_STS_ERROR;
1133 WHEN OTHERS THEN
1134 -- store SQL error message on message stack
1135 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1136 p_msg_name => G_UNEXPECTED_ERROR,
1137 p_token1 => G_SQLCODE_TOKEN,
1138 p_token1_value => SQLCODE,
1139 p_token2 => G_SQLERRM_TOKEN,
1140 p_token2_value => SQLERRM);
1141 -- If the cursor is open then it has to be closed
1142 IF c_instance_id_validate%ISOPEN THEN
1143 CLOSE c_instance_id_validate;
1144 END IF;
1145 -- notify caller of an error as UNEXPETED error
1146 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1147 END validate_instance_id;
1148 --------------------------------------16----------------------------------------
1149 -- Start of Commnets
1150 -- avsingh
1151 -- Procedure Name : validate_slctd_for_split
1152 -- Description : Validate against selected_for_split_flag
1153 -- Business Rules :
1154 -- Parameters : OUT Return Status, IN Rec Info
1155 -- Version : 1.0
1156 -- End of Commnets
1157
1158 PROCEDURE validate_slctd_for_split(x_return_status OUT NOCOPY VARCHAR2,
1159 p_itiv_rec IN itiv_rec_type) IS
1160 BEGIN
1161 -- initialize return status
1162 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1163 -- data is required
1164 IF (p_itiv_rec.selected_for_split_flag = OKC_API.G_MISS_CHAR) OR
1165 (p_itiv_rec.selected_for_split_flag IS NULL) THEN
1166 -- halt validation as it is a optional field
1167 RAISE G_EXCEPTION_STOP_VALIDATION;
1168 END IF;
1169 -- Enforce Valid values
1170 --'Y' = Selected
1171 --'N' = Not selected
1172 --'P' = Split has been processed
1173 If p_itiv_rec.selected_for_split_flag not in ('Y','N','P') Then
1174 -- halt validation as it has no valid value
1175 RAISE G_EXCEPTION_HALT_VALIDATION;
1176 END IF;
1177 EXCEPTION
1178 WHEN G_EXCEPTION_STOP_VALIDATION THEN
1179 -- We are here since the field is optional
1180 null;
1181 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1182 -- We are here b'cause validation falied
1183 -- store SQL error message on message stack
1184 OKC_API.set_message(p_app_name => G_APP_NAME,
1185 p_msg_name => G_INVALID_VALUE,
1186 p_token1 => G_COL_NAME_TOKEN,
1187 p_token1_value => 'selected_for_split_flag');
1188 -- notify caller of an error
1189 x_return_status := OKC_API.G_RET_STS_ERROR;
1190 WHEN OTHERS THEN
1191 -- store SQL error message on message stack
1192 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1193 p_msg_name => G_UNEXPECTED_ERROR,
1194 p_token1 => G_SQLCODE_TOKEN,
1195 p_token1_value => SQLCODE,
1196 p_token2 => G_SQLERRM_TOKEN,
1197 p_token2_value => SQLERRM);
1198 -- notify caller of an error as UNEXPETED error
1199 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1200 END validate_slctd_for_split;
1201 --------------------------------------17----------------------------------------
1202 -- Start of Commnets
1203 -- avsingh
1204 -- Procedure Name : validate_asd_id
1205 -- Description : Validate against OKL_TXD_ASSETS_B
1206 -- Business Rules :
1207 -- Parameters : OUT Return Status, IN Rec Info
1208 -- Version : 1.0
1209 -- End of Commnets
1210
1211 PROCEDURE validate_asd_id(x_return_status OUT NOCOPY VARCHAR2,
1212 p_itiv_rec IN itiv_rec_type) IS
1213 ln_dummy number := 0;
1214 CURSOR c_asd_id_validate(p_asd_id NUMBER) is
1215 SELECT 1
1216 FROM DUAL
1217 WHERE EXISTS (SELECT '1'
1218 FROM OKL_TXD_ASSETS_B
1219 WHERE id = p_asd_id
1220 );
1221 BEGIN
1222 -- initialize return status
1223 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1224 -- data is required
1225 IF (p_itiv_rec.asd_id = OKC_API.G_MISS_NUM) OR
1226 (p_itiv_rec.asd_id IS NULL) THEN
1227 -- halt validation as it is a optional field
1228 RAISE G_EXCEPTION_STOP_VALIDATION;
1229 END IF;
1230 -- Enforce Foreign Key
1231 OPEN c_asd_id_validate(p_itiv_rec.asd_id);
1232 IF c_asd_id_validate%NOTFOUND THEN
1233 -- halt validation as it has no parent record
1234 RAISE G_EXCEPTION_HALT_VALIDATION;
1235 END IF;
1236 FETCH c_asd_id_validate into ln_dummy;
1237 CLOSE c_asd_id_validate;
1238 IF (ln_dummy = 0) then
1239 -- halt validation as it has no parent record
1240 RAISE G_EXCEPTION_HALT_VALIDATION;
1241 END IF;
1242 EXCEPTION
1243 WHEN G_EXCEPTION_STOP_VALIDATION THEN
1244 -- We are here since the field is optional
1245 null;
1246 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1247 -- We are here b'cause validation falied
1248 -- store SQL error message on message stack
1249 OKC_API.set_message(p_app_name => G_APP_NAME,
1250 p_msg_name => G_NO_MATCHING_RECORD,
1251 p_token1 => G_COL_NAME_TOKEN,
1252 p_token1_value => 'asd_id');
1253 -- If the cursor is open then it has to be closed
1254 IF c_asd_id_validate%ISOPEN THEN
1255 CLOSE c_asd_id_validate;
1256 END IF;
1257 -- notify caller of an error
1258 x_return_status := OKC_API.G_RET_STS_ERROR;
1259 WHEN OTHERS THEN
1260 -- store SQL error message on message stack
1261 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1262 p_msg_name => G_UNEXPECTED_ERROR,
1263 p_token1 => G_SQLCODE_TOKEN,
1264 p_token1_value => SQLCODE,
1265 p_token2 => G_SQLERRM_TOKEN,
1266 p_token2_value => SQLERRM);
1267 -- If the cursor is open then it has to be closed
1268 IF c_asd_id_validate%ISOPEN THEN
1269 CLOSE c_asd_id_validate;
1270 END IF;
1271 -- notify caller of an error as UNEXPETED error
1272 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1273 END validate_asd_id;
1274 /************************ HAND-CODED *********************************/
1275 ---------------------------------------------------------------------------
1276 -- FUNCTION get_seq_id
1277 ---------------------------------------------------------------------------
1278 FUNCTION get_seq_id RETURN NUMBER IS
1279 BEGIN
1280 RETURN(okc_p_util.raw_to_number(sys_guid()));
1281 END get_seq_id;
1282
1283 ---------------------------------------------------------------------------
1284 -- PROCEDURE qc
1285 ---------------------------------------------------------------------------
1286 PROCEDURE qc IS
1287 BEGIN
1288 null;
1289 END qc;
1290
1291 ---------------------------------------------------------------------------
1292 -- PROCEDURE change_version
1293 ---------------------------------------------------------------------------
1294 PROCEDURE change_version IS
1295 BEGIN
1296 null;
1297 END change_version;
1298
1299 ---------------------------------------------------------------------------
1300 -- PROCEDURE api_copy
1301 ---------------------------------------------------------------------------
1302 PROCEDURE api_copy IS
1303 BEGIN
1304 null;
1305 END api_copy;
1306
1307 ---------------------------------------------------------------------------
1308 -- FUNCTION get_rec for: OKL_TXL_ITM_INSTS
1309 ---------------------------------------------------------------------------
1310 FUNCTION get_rec (
1311 p_iti_rec IN iti_rec_type,
1312 x_no_data_found OUT NOCOPY BOOLEAN)
1313 RETURN iti_rec_type IS
1314 CURSOR okl_txl_itm_insts_b_pk_csr(
1315 p_id IN NUMBER) IS
1316 SELECT ID,
1317 OBJECT_VERSION_NUMBER,
1318 TAS_ID,
1319 TAL_ID,
1320 KLE_ID,
1321 TAL_TYPE,
1322 LINE_NUMBER,
1323 INSTANCE_NUMBER_IB,
1324 OBJECT_ID1_NEW,
1325 OBJECT_ID2_NEW,
1326 JTOT_OBJECT_CODE_NEW,
1327 OBJECT_ID1_OLD,
1328 OBJECT_ID2_OLD,
1329 JTOT_OBJECT_CODE_OLD,
1330 INVENTORY_ORG_ID,
1331 SERIAL_NUMBER,
1332 MFG_SERIAL_NUMBER_YN,
1333 INVENTORY_ITEM_ID,
1334 INV_MASTER_ORG_ID,
1335 ATTRIBUTE_CATEGORY,
1336 ATTRIBUTE1,
1337 ATTRIBUTE2,
1338 ATTRIBUTE3,
1339 ATTRIBUTE4,
1340 ATTRIBUTE5,
1341 ATTRIBUTE6,
1342 ATTRIBUTE7,
1343 ATTRIBUTE8,
1344 ATTRIBUTE9,
1345 ATTRIBUTE10,
1346 ATTRIBUTE11,
1347 ATTRIBUTE12,
1348 ATTRIBUTE13,
1349 ATTRIBUTE14,
1350 ATTRIBUTE15,
1351 CREATED_BY,
1352 CREATION_DATE,
1353 LAST_UPDATED_BY,
1354 LAST_UPDATE_DATE,
1355 LAST_UPDATE_LOGIN,
1356 DNZ_CLE_ID,
1357 --Bug# 2697681 schema change : 11.5.9 enhacement - split asset by serial numbers
1358 instance_id,
1359 selected_for_split_flag,
1360 asd_id
1361 FROM OKL_TXL_ITM_INSTS iti
1362 WHERE iti.id = p_id;
1363 l_okl_txl_itm_insts_b_pk okl_txl_itm_insts_b_pk_csr%ROWTYPE;
1364 l_iti_rec iti_rec_type;
1365 BEGIN
1366 x_no_data_found := TRUE;
1367 -- Get current database values
1368 OPEN okl_txl_itm_insts_b_pk_csr (p_iti_rec.id);
1369 FETCH okl_txl_itm_insts_b_pk_csr INTO
1370 l_iti_rec.ID,
1371 l_iti_rec.OBJECT_VERSION_NUMBER,
1372 l_iti_rec.TAS_ID,
1373 l_iti_rec.TAL_ID,
1374 l_iti_rec.KLE_ID,
1375 l_iti_rec.TAL_TYPE,
1376 l_iti_rec.LINE_NUMBER,
1377 l_iti_rec.INSTANCE_NUMBER_IB,
1378 l_iti_rec.OBJECT_ID1_NEW,
1379 l_iti_rec.OBJECT_ID2_NEW,
1380 l_iti_rec.JTOT_OBJECT_CODE_NEW,
1381 l_iti_rec.OBJECT_ID1_OLD,
1382 l_iti_rec.OBJECT_ID2_OLD,
1383 l_iti_rec.JTOT_OBJECT_CODE_OLD,
1384 l_iti_rec.INVENTORY_ORG_ID,
1385 l_iti_rec.SERIAL_NUMBER,
1386 l_iti_rec.MFG_SERIAL_NUMBER_YN,
1387 l_iti_rec.INVENTORY_ITEM_ID,
1388 l_iti_rec.INV_MASTER_ORG_ID,
1389 l_iti_rec.ATTRIBUTE_CATEGORY,
1390 l_iti_rec.ATTRIBUTE1,
1391 l_iti_rec.ATTRIBUTE2,
1392 l_iti_rec.ATTRIBUTE3,
1393 l_iti_rec.ATTRIBUTE4,
1394 l_iti_rec.ATTRIBUTE5,
1395 l_iti_rec.ATTRIBUTE6,
1396 l_iti_rec.ATTRIBUTE7,
1397 l_iti_rec.ATTRIBUTE8,
1398 l_iti_rec.ATTRIBUTE9,
1399 l_iti_rec.ATTRIBUTE10,
1400 l_iti_rec.ATTRIBUTE11,
1401 l_iti_rec.ATTRIBUTE12,
1402 l_iti_rec.ATTRIBUTE13,
1403 l_iti_rec.ATTRIBUTE14,
1404 l_iti_rec.ATTRIBUTE15,
1405 l_iti_rec.CREATED_BY,
1406 l_iti_rec.CREATION_DATE,
1407 l_iti_rec.LAST_UPDATED_BY,
1408 l_iti_rec.LAST_UPDATE_DATE,
1409 l_iti_rec.LAST_UPDATE_LOGIN,
1410 l_iti_rec.DNZ_CLE_ID,
1411 --Bug#2697681 schema change : 11.5.9 enhacement - split asset by serial numbers
1412 l_iti_rec.instance_id,
1413 l_iti_rec.selected_for_split_flag,
1414 l_iti_rec.asd_id;
1415 x_no_data_found := okl_txl_itm_insts_b_pk_csr%NOTFOUND;
1416 CLOSE okl_txl_itm_insts_b_pk_csr;
1417 RETURN(l_iti_rec);
1418 END get_rec;
1419
1420 FUNCTION get_rec (
1421 p_iti_rec IN iti_rec_type
1422 ) RETURN iti_rec_type IS
1423 l_row_notfound BOOLEAN := TRUE;
1424 BEGIN
1425 RETURN(get_rec(p_iti_rec, l_row_notfound));
1426 END get_rec;
1427 ---------------------------------------------------------------------------
1428 -- FUNCTION get_rec for: OKL_TXL_ITEM_INSTS_V
1429 ---------------------------------------------------------------------------
1430 FUNCTION get_rec (
1431 p_itiv_rec IN itiv_rec_type,
1432 x_no_data_found OUT NOCOPY BOOLEAN
1433 ) RETURN itiv_rec_type IS
1434 CURSOR okl_itiv_pk_csr (p_id IN NUMBER) IS
1435 SELECT ID,
1436 OBJECT_VERSION_NUMBER,
1437 TAS_ID,
1438 TAL_ID,
1439 KLE_ID,
1440 TAL_TYPE,
1441 LINE_NUMBER,
1442 INSTANCE_NUMBER_IB,
1443 OBJECT_ID1_NEW,
1444 OBJECT_ID2_NEW,
1445 JTOT_OBJECT_CODE_NEW,
1446 OBJECT_ID1_OLD,
1447 OBJECT_ID2_OLD,
1448 JTOT_OBJECT_CODE_OLD,
1449 INVENTORY_ORG_ID,
1450 SERIAL_NUMBER,
1451 MFG_SERIAL_NUMBER_YN,
1452 INVENTORY_ITEM_ID,
1453 INV_MASTER_ORG_ID,
1454 ATTRIBUTE_CATEGORY,
1455 ATTRIBUTE1,
1456 ATTRIBUTE2,
1457 ATTRIBUTE3,
1458 ATTRIBUTE4,
1459 ATTRIBUTE5,
1460 ATTRIBUTE6,
1461 ATTRIBUTE7,
1462 ATTRIBUTE8,
1463 ATTRIBUTE9,
1464 ATTRIBUTE10,
1465 ATTRIBUTE11,
1466 ATTRIBUTE12,
1467 ATTRIBUTE13,
1468 ATTRIBUTE14,
1469 ATTRIBUTE15,
1470 CREATED_BY,
1471 CREATION_DATE,
1472 LAST_UPDATED_BY,
1473 LAST_UPDATE_DATE,
1474 LAST_UPDATE_LOGIN,
1475 DNZ_CLE_ID,
1476 --Bug#2697681 schema change : 11.5.9 enhacement - split asset by serial numbers
1477 instance_id,
1478 selected_for_split_flag,
1479 asd_id
1480 FROM OKL_TXL_ITM_INSTS_V iti
1481 WHERE iti.id = p_id;
1482 l_okl_itiv_pk okl_itiv_pk_csr%ROWTYPE;
1483 l_itiv_rec itiv_rec_type;
1484 BEGIN
1485 x_no_data_found := TRUE;
1486 -- Get current database values
1487 OPEN okl_itiv_pk_csr (p_itiv_rec.id);
1488 FETCH okl_itiv_pk_csr INTO
1489 l_itiv_rec.ID,
1490 l_itiv_rec.OBJECT_VERSION_NUMBER,
1491 l_itiv_rec.TAS_ID,
1492 l_itiv_rec.TAL_ID,
1493 l_itiv_rec.KLE_ID,
1494 l_itiv_rec.TAL_TYPE,
1495 l_itiv_rec.LINE_NUMBER,
1496 l_itiv_rec.INSTANCE_NUMBER_IB,
1497 l_itiv_rec.OBJECT_ID1_NEW,
1498 l_itiv_rec.OBJECT_ID2_NEW,
1499 l_itiv_rec.JTOT_OBJECT_CODE_NEW,
1500 l_itiv_rec.OBJECT_ID1_OLD,
1501 l_itiv_rec.OBJECT_ID2_OLD,
1502 l_itiv_rec.JTOT_OBJECT_CODE_OLD,
1503 l_itiv_rec.INVENTORY_ORG_ID,
1504 l_itiv_rec.SERIAL_NUMBER,
1505 l_itiv_rec.MFG_SERIAL_NUMBER_YN,
1506 l_itiv_rec.INVENTORY_ITEM_ID,
1507 l_itiv_rec.INV_MASTER_ORG_ID,
1508 l_itiv_rec.ATTRIBUTE_CATEGORY,
1509 l_itiv_rec.ATTRIBUTE1,
1510 l_itiv_rec.ATTRIBUTE2,
1511 l_itiv_rec.ATTRIBUTE3,
1512 l_itiv_rec.ATTRIBUTE4,
1513 l_itiv_rec.ATTRIBUTE5,
1514 l_itiv_rec.ATTRIBUTE6,
1515 l_itiv_rec.ATTRIBUTE7,
1516 l_itiv_rec.ATTRIBUTE8,
1517 l_itiv_rec.ATTRIBUTE9,
1518 l_itiv_rec.ATTRIBUTE10,
1519 l_itiv_rec.ATTRIBUTE11,
1520 l_itiv_rec.ATTRIBUTE12,
1521 l_itiv_rec.ATTRIBUTE13,
1522 l_itiv_rec.ATTRIBUTE14,
1523 l_itiv_rec.ATTRIBUTE15,
1524 l_itiv_rec.CREATED_BY,
1525 l_itiv_rec.CREATION_DATE,
1526 l_itiv_rec.LAST_UPDATED_BY,
1527 l_itiv_rec.LAST_UPDATE_DATE,
1528 l_itiv_rec.LAST_UPDATE_LOGIN,
1529 l_itiv_rec.DNZ_CLE_ID,
1530 --Bug#2697681 schema change : 11.5.9 enhacement - split asset by serial numbers
1531 l_itiv_rec.instance_id,
1532 l_itiv_rec.selected_for_split_flag,
1533 l_itiv_rec.asd_id;
1534 x_no_data_found := okl_itiv_pk_csr%NOTFOUND;
1535 CLOSE okl_itiv_pk_csr;
1536 RETURN(l_itiv_rec);
1537 END get_rec;
1538
1539 FUNCTION get_rec (
1540 p_itiv_rec IN itiv_rec_type
1541 ) RETURN itiv_rec_type IS
1542 l_row_notfound BOOLEAN := TRUE;
1543 BEGIN
1544 RETURN(get_rec(p_itiv_rec, l_row_notfound));
1545 END get_rec;
1546
1547 ----------------------------------------------------------
1548 -- FUNCTION null_out_defaults for: OKL_TXL_ITEM_INSTS_V --
1549 ----------------------------------------------------------
1550 FUNCTION null_out_defaults (
1551 p_itiv_rec IN itiv_rec_type
1552 ) RETURN itiv_rec_type IS
1553 l_itiv_rec itiv_rec_type := p_itiv_rec;
1554 BEGIN
1555 IF (l_itiv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
1556 l_itiv_rec.object_version_number := NULL;
1557 END IF;
1558 IF (l_itiv_rec.tas_id = OKC_API.G_MISS_NUM) THEN
1559 l_itiv_rec.tas_id := NULL;
1560 END IF;
1561 IF (l_itiv_rec.tal_id = OKC_API.G_MISS_NUM) THEN
1562 l_itiv_rec.tal_id := NULL;
1563 END IF;
1564 IF (l_itiv_rec.kle_id = OKC_API.G_MISS_NUM) THEN
1565 l_itiv_rec.kle_id := NULL;
1566 END IF;
1567 IF (l_itiv_rec.tal_type = OKC_API.G_MISS_CHAR) THEN
1568 l_itiv_rec.tal_type := NULL;
1569 END IF;
1570 IF (l_itiv_rec.line_number = OKC_API.G_MISS_NUM) THEN
1571 l_itiv_rec.line_number := NULL;
1572 END IF;
1573 IF (l_itiv_rec.instance_number_ib = OKC_API.G_MISS_CHAR) THEN
1574 l_itiv_rec.instance_number_ib := NULL;
1575 END IF;
1576 IF (l_itiv_rec.object_id1_new = OKC_API.G_MISS_CHAR) THEN
1577 l_itiv_rec.object_id1_new := NULL;
1578 END IF;
1579 IF (l_itiv_rec.object_id2_new = OKC_API.G_MISS_CHAR) THEN
1580 l_itiv_rec.object_id2_new := NULL;
1581 END IF;
1582 IF (l_itiv_rec.jtot_object_code_new = OKC_API.G_MISS_CHAR) THEN
1583 l_itiv_rec.jtot_object_code_new := NULL;
1584 END IF;
1585 IF (l_itiv_rec.object_id1_old = OKC_API.G_MISS_CHAR) THEN
1586 l_itiv_rec.object_id1_old := NULL;
1587 END IF;
1588 IF (l_itiv_rec.object_id2_old = OKC_API.G_MISS_CHAR) THEN
1589 l_itiv_rec.object_id2_old := NULL;
1590 END IF;
1591 IF (l_itiv_rec.jtot_object_code_old = OKC_API.G_MISS_CHAR) THEN
1592 l_itiv_rec.jtot_object_code_old := NULL;
1593 END IF;
1594 IF (l_itiv_rec.inventory_org_id = OKC_API.G_MISS_NUM) THEN
1595 l_itiv_rec.inventory_org_id := NULL;
1596 END IF;
1597 IF (l_itiv_rec.serial_number = OKC_API.G_MISS_CHAR) THEN
1598 l_itiv_rec.serial_number := NULL;
1599 END IF;
1600 IF (l_itiv_rec.mfg_serial_number_yn = OKC_API.G_MISS_CHAR) THEN
1601 l_itiv_rec.mfg_serial_number_yn := NULL;
1602 END IF;
1603 IF (l_itiv_rec.inventory_item_id = OKC_API.G_MISS_NUM) THEN
1604 l_itiv_rec.inventory_item_id := NULL;
1605 END IF;
1606 IF (l_itiv_rec.inv_master_org_id = OKC_API.G_MISS_NUM) THEN
1607 l_itiv_rec.inv_master_org_id := NULL;
1608 END IF;
1609 IF (l_itiv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
1610 l_itiv_rec.attribute_category := NULL;
1611 END IF;
1612 IF (l_itiv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
1613 l_itiv_rec.attribute1 := NULL;
1614 END IF;
1615 IF (l_itiv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
1616 l_itiv_rec.attribute2 := NULL;
1617 END IF;
1618 IF (l_itiv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
1619 l_itiv_rec.attribute3 := NULL;
1620 END IF;
1621 IF (l_itiv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
1622 l_itiv_rec.attribute4 := NULL;
1623 END IF;
1624 IF (l_itiv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
1625 l_itiv_rec.attribute5 := NULL;
1626 END IF;
1627 IF (l_itiv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
1628 l_itiv_rec.attribute6 := NULL;
1629 END IF;
1630 IF (l_itiv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
1631 l_itiv_rec.attribute7 := NULL;
1632 END IF;
1633 IF (l_itiv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
1634 l_itiv_rec.attribute8 := NULL;
1635 END IF;
1636 IF (l_itiv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
1637 l_itiv_rec.attribute9 := NULL;
1638 END IF;
1639 IF (l_itiv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
1640 l_itiv_rec.attribute10 := NULL;
1641 END IF;
1642 IF (l_itiv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
1643 l_itiv_rec.attribute11 := NULL;
1644 END IF;
1645 IF (l_itiv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
1646 l_itiv_rec.attribute12 := NULL;
1647 END IF;
1648 IF (l_itiv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
1649 l_itiv_rec.attribute13 := NULL;
1650 END IF;
1651 IF (l_itiv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
1652 l_itiv_rec.attribute14 := NULL;
1653 END IF;
1654 IF (l_itiv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
1655 l_itiv_rec.attribute15 := NULL;
1656 END IF;
1657 IF (l_itiv_rec.created_by = OKC_API.G_MISS_NUM) THEN
1658 l_itiv_rec.created_by := NULL;
1659 END IF;
1660 IF (l_itiv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
1661 l_itiv_rec.creation_date := NULL;
1662 END IF;
1663 IF (l_itiv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
1664 l_itiv_rec.last_updated_by := NULL;
1665 END IF;
1666 IF (l_itiv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
1667 l_itiv_rec.last_update_date := NULL;
1668 END IF;
1669 IF (l_itiv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
1670 l_itiv_rec.last_update_login := NULL;
1671 END IF;
1672 IF (l_itiv_rec.dnz_cle_id = OKC_API.G_MISS_NUM) THEN
1673 l_itiv_rec.dnz_cle_id := NULL;
1674 END IF;
1675 --Bug#2697681 schema change : 11.5.9 enhacement - split asset by serial numbers
1676 IF (l_itiv_rec.instance_id = OKC_API.G_MISS_NUM) THEN
1677 l_itiv_rec.instance_id := NULL;
1678 END IF;
1679 IF (l_itiv_rec.selected_for_split_flag = OKC_API.G_MISS_CHAR) THEN
1680 l_itiv_rec.selected_for_split_flag := NULL;
1681 END IF;
1682 IF (l_itiv_rec.asd_id = OKC_API.G_MISS_NUM) THEN
1683 l_itiv_rec.asd_id := NULL;
1684 END IF;
1685 RETURN(l_itiv_rec);
1686 END null_out_defaults;
1687 ---------------------------------------------------------------------------
1688 -- PROCEDURE Validate_Attributes
1689 ---------------------------------------------------------------------------
1690 ----------------------------------------------
1691 -- Validate_Attributes for: OKL_TXL_ITEM_INSTS_V --
1692 ----------------------------------------------
1693 FUNCTION Validate_Attributes (
1694 p_itiv_rec IN itiv_rec_type
1695 ) RETURN VARCHAR2 IS
1696 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1697 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1698 BEGIN
1699 IF p_itiv_rec.id = OKC_API.G_MISS_NUM OR
1700 p_itiv_rec.id IS NULL
1701 THEN
1702 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
1703 l_return_status := OKC_API.G_RET_STS_ERROR;
1704 ELSIF p_itiv_rec.object_version_number = OKC_API.G_MISS_NUM OR
1705 p_itiv_rec.object_version_number IS NULL THEN
1706 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
1707 l_return_status := OKC_API.G_RET_STS_ERROR;
1708 ELSIF p_itiv_rec.line_number = OKC_API.G_MISS_NUM OR
1709 p_itiv_rec.line_number IS NULL
1710 THEN
1711 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'line_number');
1712 l_return_status := OKC_API.G_RET_STS_ERROR;
1713 ELSIF p_itiv_rec.instance_number_ib = OKC_API.G_MISS_CHAR OR
1714 p_itiv_rec.instance_number_ib IS NULL
1715 THEN
1716 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'instance_number_ib');
1717 l_return_status := OKC_API.G_RET_STS_ERROR;
1718 END IF;
1719 /************************ HAND-CODED *********************************/
1720 -- Calling Local validation procedures
1721 validate_tas_id(x_return_status => l_return_status,
1722 p_itiv_rec => p_itiv_rec);
1723 -- Store the Highest Degree of Error
1724 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1725 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1726 x_return_status := l_return_status;
1727 END IF;
1728 END IF;
1729 l_return_status := x_return_status;
1730 Validate_tal_id(x_return_status => l_return_status,
1731 p_itiv_rec => p_itiv_rec);
1732 -- Store the Highest Degree of Error
1733 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1734 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1735 x_return_status := l_return_status;
1736 END IF;
1737 END IF;
1738 l_return_status := x_return_status;
1739 Validate_kle_id(x_return_status => l_return_status,
1740 p_itiv_rec => p_itiv_rec);
1741 -- Store the Highest Degree of Error
1742 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1743 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1744 x_return_status := l_return_status;
1745 END IF;
1746 END IF;
1747 l_return_status := x_return_status;
1748 validate_tal_type(x_return_status => l_return_status,
1749 p_itiv_rec => p_itiv_rec);
1750 -- Store the Highest Degree of Error
1751 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1752 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1753 x_return_status := l_return_status;
1754 END IF;
1755 END IF;
1756 l_return_status := x_return_status;
1757 validate_object_id1_new(x_return_status => l_return_status,
1758 p_itiv_rec => p_itiv_rec);
1759 -- Store the Highest Degree of Error
1760 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1761 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1762 x_return_status := l_return_status;
1763 END IF;
1764 END IF;
1765 l_return_status := x_return_status;
1766 validate_object_id2_new(x_return_status => l_return_status,
1767 p_itiv_rec => p_itiv_rec);
1768 -- Store the Highest Degree of Error
1769 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1770 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1771 x_return_status := l_return_status;
1772 END IF;
1773 END IF;
1774 l_return_status := x_return_status;
1775 validate_jtot_object_code_new (x_return_status => l_return_status,
1776 p_itiv_rec => p_itiv_rec);
1777 -- Store the Highest Degree of Error
1778 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1779 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1780 x_return_status := l_return_status;
1781 END IF;
1782 END IF;
1783 l_return_status := x_return_status;
1784 validate_object_id1_old(x_return_status => l_return_status,
1785 p_itiv_rec => p_itiv_rec);
1786 -- Store the Highest Degree of Error
1787 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1788 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1789 x_return_status := l_return_status;
1790 END IF;
1791 END IF;
1792 l_return_status := x_return_status;
1793 validate_object_id2_old(x_return_status => l_return_status,
1794 p_itiv_rec => p_itiv_rec);
1795 -- Store the Highest Degree of Error
1796 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1797 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1798 x_return_status := l_return_status;
1799 END IF;
1800 END IF;
1801 l_return_status := x_return_status;
1802 validate_jtot_object_code_old (x_return_status => l_return_status,
1803 p_itiv_rec => p_itiv_rec);
1804 -- Store the Highest Degree of Error
1805 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1806 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1807 x_return_status := l_return_status;
1808 END IF;
1809 END IF;
1810 l_return_status := x_return_status;
1811 validate_mfg_serial_number_yn(x_return_status => l_return_status,
1812 p_itiv_rec => p_itiv_rec);
1813 -- Store the Highest Degree of Error
1814 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1815 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1816 x_return_status := l_return_status;
1817 END IF;
1818 END IF;
1819 l_return_status := x_return_status;
1820 validate_inv_item_id(x_return_status => l_return_status,
1821 p_itiv_rec => p_itiv_rec);
1822 -- Store the Highest Degree of Error
1823 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1824 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1825 x_return_status := l_return_status;
1826 END IF;
1827 END IF;
1828 l_return_status := x_return_status;
1829 validate_inv_master_org_id(x_return_status => l_return_status,
1830 p_itiv_rec => p_itiv_rec);
1831 -- Store the Highest Degree of Error
1832 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1833 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1834 x_return_status := l_return_status;
1835 END IF;
1836 END IF;
1837 l_return_status := x_return_status;
1838 validate_inv_org_id(x_return_status => l_return_status,
1839 p_itiv_rec => p_itiv_rec);
1840 -- Store the Highest Degree of Error
1841 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1842 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1843 x_return_status := l_return_status;
1844 END IF;
1845 END IF;
1846 l_return_status := x_return_status;
1847 validate_instance_id(x_return_status => l_return_status,
1848 p_itiv_rec => p_itiv_rec);
1849 -- Store the Highest Degree of Error
1850 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1851 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1852 x_return_status := l_return_status;
1853 END IF;
1854 END IF;
1855 l_return_status := x_return_status;
1856 validate_slctd_for_split(x_return_status => l_return_status,
1857 p_itiv_rec => p_itiv_rec);
1858 -- Store the Highest Degree of Error
1859 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1860 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1861 x_return_status := l_return_status;
1862 END IF;
1863 END IF;
1864 l_return_status := x_return_status;
1865 validate_asd_id(x_return_status => l_return_status,
1866 p_itiv_rec => p_itiv_rec);
1867 -- Store the Highest Degree of Error
1868 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1869 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1870 x_return_status := l_return_status;
1871 END IF;
1872 END IF;
1873 l_return_status := x_return_status;
1874 RETURN(l_return_status);
1875 EXCEPTION
1876 WHEN OTHERS THEN
1877 -- store SQL error message on message stack
1878 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1879 p_msg_name => G_UNEXPECTED_ERROR,
1880 p_token1 => G_SQLCODE_TOKEN,
1881 p_token1_value => SQLCODE,
1882 p_token2 => G_SQLERRM_TOKEN,
1883 p_token2_value => SQLERRM);
1884 -- notify caller of an error as UNEXPETED error
1885 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1886 -- Return status to caller
1887 RETURN(l_return_status);
1888 /************************ HAND-CODED *********************************/
1889 END Validate_Attributes;
1890
1891 ---------------------------------------------------------------------------
1892 -- PROCEDURE Validate_Record
1893 ---------------------------------------------------------------------------
1894 ------------------------------------------
1895 -- Validate_Record for: OKL_TXL_ITM_INSTS_V --
1896 ------------------------------------------
1897 FUNCTION Validate_Record (
1898 p_itiv_rec IN itiv_rec_type
1899 ) RETURN VARCHAR2 IS
1900 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1901 BEGIN
1902 RETURN (l_return_status);
1903 END Validate_Record;
1904
1905 ---------------------------------------------------------------------------
1906 -- PROCEDURE Migrate
1907 ---------------------------------------------------------------------------
1908 PROCEDURE migrate (
1909 p_from IN itiv_rec_type,
1910 p_to OUT NOCOPY iti_rec_type
1911 ) IS
1912 BEGIN
1913 p_to.ID := p_from.ID;
1914 p_to.OBJECT_VERSION_NUMBER := p_from.OBJECT_VERSION_NUMBER;
1915 p_to.TAS_ID := p_from.TAS_ID;
1916 p_to.TAL_ID := p_from.TAL_ID;
1917 p_to.KLE_ID := p_from.KLE_ID;
1918 p_to.TAL_TYPE := p_from.TAL_TYPE;
1919 p_to.LINE_NUMBER := p_from.LINE_NUMBER;
1920 p_to.INSTANCE_NUMBER_IB := p_from.INSTANCE_NUMBER_IB;
1921 p_to.OBJECT_ID1_NEW := p_from.OBJECT_ID1_NEW;
1922 p_to.OBJECT_ID2_NEW := p_from.OBJECT_ID2_NEW;
1923 p_to.JTOT_OBJECT_CODE_NEW := p_from.JTOT_OBJECT_CODE_NEW;
1924 p_to.OBJECT_ID1_OLD := p_from.OBJECT_ID1_OLD;
1925 p_to.OBJECT_ID2_OLD := p_from.OBJECT_ID2_OLD;
1926 p_to.JTOT_OBJECT_CODE_OLD := p_from.JTOT_OBJECT_CODE_OLD;
1927 p_to.INVENTORY_ORG_ID := p_from.INVENTORY_ORG_ID;
1928 p_to.SERIAL_NUMBER := p_from.SERIAL_NUMBER;
1929 p_to.MFG_SERIAL_NUMBER_YN := p_from.MFG_SERIAL_NUMBER_YN;
1930 p_to.INVENTORY_ITEM_ID := p_from.INVENTORY_ITEM_ID;
1931 p_to.INV_MASTER_ORG_ID := p_from.INV_MASTER_ORG_ID;
1932 p_to.ATTRIBUTE_CATEGORY := p_from.ATTRIBUTE_CATEGORY;
1933 p_to.ATTRIBUTE1 := p_from.ATTRIBUTE1;
1934 p_to.ATTRIBUTE2 := p_from.ATTRIBUTE2;
1935 p_to.ATTRIBUTE3 := p_from.ATTRIBUTE3;
1936 p_to.ATTRIBUTE4 := p_from.ATTRIBUTE4;
1937 p_to.ATTRIBUTE5 := p_from.ATTRIBUTE5;
1938 p_to.ATTRIBUTE6 := p_from.ATTRIBUTE6;
1939 p_to.ATTRIBUTE7 := p_from.ATTRIBUTE7;
1940 p_to.ATTRIBUTE8 := p_from.ATTRIBUTE8;
1941 p_to.ATTRIBUTE9 := p_from.ATTRIBUTE9;
1942 p_to.ATTRIBUTE10 := p_from.ATTRIBUTE10;
1943 p_to.ATTRIBUTE11 := p_from.ATTRIBUTE11;
1944 p_to.ATTRIBUTE12 := p_from.ATTRIBUTE12;
1945 p_to.ATTRIBUTE13 := p_from.ATTRIBUTE13;
1946 p_to.ATTRIBUTE14 := p_from.ATTRIBUTE14;
1947 p_to.ATTRIBUTE15 := p_from.ATTRIBUTE15;
1948 p_to.CREATED_BY := p_from.CREATED_BY;
1949 p_to.CREATION_DATE := p_from.CREATION_DATE;
1950 p_to.LAST_UPDATED_BY := p_from.LAST_UPDATED_BY;
1951 p_to.LAST_UPDATE_DATE := p_from.LAST_UPDATE_DATE;
1952 p_to.LAST_UPDATE_LOGIN := p_from.LAST_UPDATE_LOGIN;
1953 p_to.DNZ_CLE_ID := p_from.DNZ_CLE_ID;
1954 --Bug#2697681 schema change : 11.5.9 enhacement - split asset by serial numbers
1955 p_to.instance_id := p_from.instance_id;
1956 p_to.selected_for_split_flag := p_from.selected_for_split_flag;
1957 p_to.asd_id := p_from.asd_id;
1958 END migrate;
1959 PROCEDURE migrate (
1960 p_from IN iti_rec_type,
1961 p_to OUT NOCOPY itiv_rec_type
1962 ) IS
1963 BEGIN
1964 p_to.ID := p_from.ID;
1965 p_to.OBJECT_VERSION_NUMBER := p_from.OBJECT_VERSION_NUMBER;
1966 p_to.TAS_ID := p_from.TAS_ID;
1967 p_to.TAL_ID := p_from.TAL_ID;
1968 p_to.KLE_ID := p_from.KLE_ID;
1969 p_to.TAL_TYPE := p_from.TAL_TYPE;
1970 p_to.LINE_NUMBER := p_from.LINE_NUMBER;
1971 p_to.INSTANCE_NUMBER_IB := p_from.INSTANCE_NUMBER_IB;
1972 p_to.OBJECT_ID1_NEW := p_from.OBJECT_ID1_NEW;
1973 p_to.OBJECT_ID2_NEW := p_from.OBJECT_ID2_NEW;
1974 p_to.JTOT_OBJECT_CODE_NEW := p_from.JTOT_OBJECT_CODE_NEW;
1975 p_to.OBJECT_ID1_OLD := p_from.OBJECT_ID1_OLD;
1976 p_to.OBJECT_ID2_OLD := p_from.OBJECT_ID2_OLD;
1977 p_to.JTOT_OBJECT_CODE_OLD := p_from.JTOT_OBJECT_CODE_OLD;
1978 p_to.INVENTORY_ORG_ID := p_from.INVENTORY_ORG_ID;
1979 p_to.SERIAL_NUMBER := p_from.SERIAL_NUMBER;
1980 p_to.MFG_SERIAL_NUMBER_YN := p_from.MFG_SERIAL_NUMBER_YN;
1981 p_to.INVENTORY_ITEM_ID := p_from.INVENTORY_ITEM_ID;
1982 p_to.INV_MASTER_ORG_ID := p_from.INV_MASTER_ORG_ID;
1983 p_to.ATTRIBUTE_CATEGORY := p_from.ATTRIBUTE_CATEGORY;
1984 p_to.ATTRIBUTE1 := p_from.ATTRIBUTE1;
1985 p_to.ATTRIBUTE2 := p_from.ATTRIBUTE2;
1986 p_to.ATTRIBUTE3 := p_from.ATTRIBUTE3;
1987 p_to.ATTRIBUTE4 := p_from.ATTRIBUTE4;
1988 p_to.ATTRIBUTE5 := p_from.ATTRIBUTE5;
1989 p_to.ATTRIBUTE6 := p_from.ATTRIBUTE6;
1990 p_to.ATTRIBUTE7 := p_from.ATTRIBUTE7;
1991 p_to.ATTRIBUTE8 := p_from.ATTRIBUTE8;
1992 p_to.ATTRIBUTE9 := p_from.ATTRIBUTE9;
1993 p_to.ATTRIBUTE10 := p_from.ATTRIBUTE10;
1994 p_to.ATTRIBUTE11 := p_from.ATTRIBUTE11;
1995 p_to.ATTRIBUTE12 := p_from.ATTRIBUTE12;
1996 p_to.ATTRIBUTE13 := p_from.ATTRIBUTE13;
1997 p_to.ATTRIBUTE14 := p_from.ATTRIBUTE14;
1998 p_to.ATTRIBUTE15 := p_from.ATTRIBUTE15;
1999 p_to.CREATED_BY := p_from.CREATED_BY;
2000 p_to.CREATION_DATE := p_from.CREATION_DATE;
2001 p_to.LAST_UPDATED_BY := p_from.LAST_UPDATED_BY;
2002 p_to.LAST_UPDATE_DATE := p_from.LAST_UPDATE_DATE;
2003 p_to.LAST_UPDATE_LOGIN := p_from.LAST_UPDATE_LOGIN;
2004 p_to.DNZ_CLE_ID := p_from.DNZ_CLE_ID;
2005 --Bug#2697681 schema change : 11.5.9 enhacement - split asset by serial numbers
2006 p_to.instance_id := p_from.instance_id;
2007 p_to.selected_for_split_flag := p_from.selected_for_split_flag;
2008 p_to.asd_id := p_from.asd_id;
2009 END migrate;
2010
2011 ---------------------------------------------------------------------------
2012 -- PROCEDURE validate_row
2013 ---------------------------------------------------------------------------
2014 -------------------------------------------
2015 -- validate_row for: OKL_TXL_ITM_INSTS_V --
2016 -------------------------------------------
2017 PROCEDURE validate_row(
2018 p_api_version IN NUMBER,
2019 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2020 x_return_status OUT NOCOPY VARCHAR2,
2021 x_msg_count OUT NOCOPY NUMBER,
2022 x_msg_data OUT NOCOPY VARCHAR2,
2023 p_itiv_rec IN itiv_rec_type) IS
2024
2025 l_api_version CONSTANT NUMBER := 1;
2026 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2027 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2028 l_itiv_rec itiv_rec_type := p_itiv_rec;
2029 l_iti_rec iti_rec_type;
2030
2031 BEGIN
2032 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2033 G_PKG_NAME,
2034 p_init_msg_list,
2035 l_api_version,
2036 p_api_version,
2037 '_PVT',
2038 x_return_status);
2039 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2040 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2041 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2042 RAISE OKC_API.G_EXCEPTION_ERROR;
2043 END IF;
2044 --- Validate all non-missing attributes (Item Level Validation)
2045 l_return_status := Validate_Attributes(l_itiv_rec);
2046 --- If any errors happen abort API
2047 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2048 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2049 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2050 RAISE OKC_API.G_EXCEPTION_ERROR;
2051 END IF;
2052 l_return_status := Validate_Record(l_itiv_rec);
2053 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2054 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2055 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2056 RAISE OKC_API.G_EXCEPTION_ERROR;
2057 END IF;
2058 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2059 EXCEPTION
2060 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2061 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2062 (
2063 l_api_name,
2064 G_PKG_NAME,
2065 'OKC_API.G_RET_STS_ERROR',
2066 x_msg_count,
2067 x_msg_data,
2068 '_PVT'
2069 );
2070 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2071 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2072 (
2073 l_api_name,
2074 G_PKG_NAME,
2075 'OKC_API.G_RET_STS_UNEXP_ERROR',
2076 x_msg_count,
2077 x_msg_data,
2078 '_PVT'
2079 );
2080 WHEN OTHERS THEN
2081 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2082 (
2083 l_api_name,
2084 G_PKG_NAME,
2085 'OTHERS',
2086 x_msg_count,
2087 x_msg_data,
2088 '_PVT'
2089 );
2090 END validate_row;
2091 ------------------------------------------
2092 -- PL/SQL TBL validate_row for:ITIV_TBL --
2093 ------------------------------------------
2094 PROCEDURE validate_row(
2095 p_api_version IN NUMBER,
2096 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2097 x_return_status OUT NOCOPY VARCHAR2,
2098 x_msg_count OUT NOCOPY NUMBER,
2099 x_msg_data OUT NOCOPY VARCHAR2,
2100 p_itiv_tbl IN itiv_tbl_type) IS
2101
2102 l_api_version CONSTANT NUMBER := 1;
2103 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2104 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2105 i NUMBER := 0;
2106 BEGIN
2107 OKC_API.init_msg_list(p_init_msg_list);
2108 -- Make sure PL/SQL table has records in it before passing
2109 IF (p_itiv_tbl.COUNT > 0) THEN
2110 i := p_itiv_tbl.FIRST;
2111 LOOP
2112 validate_row (
2113 p_api_version => p_api_version,
2114 p_init_msg_list => OKC_API.G_FALSE,
2115 x_return_status => x_return_status,
2116 x_msg_count => x_msg_count,
2117 x_msg_data => x_msg_data,
2118 p_itiv_rec => p_itiv_tbl(i));
2119 EXIT WHEN (i = p_itiv_tbl.LAST);
2120 i := p_itiv_tbl.NEXT(i);
2121 END LOOP;
2122 END IF;
2123 EXCEPTION
2124 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2125 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2126 (
2127 l_api_name,
2128 G_PKG_NAME,
2129 'OKC_API.G_RET_STS_ERROR',
2130 x_msg_count,
2131 x_msg_data,
2132 '_PVT'
2133 );
2134 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2135 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2136 (
2137 l_api_name,
2138 G_PKG_NAME,
2139 'OKC_API.G_RET_STS_UNEXP_ERROR',
2140 x_msg_count,
2141 x_msg_data,
2142 '_PVT'
2143 );
2144 WHEN OTHERS THEN
2145 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2146 (
2147 l_api_name,
2148 G_PKG_NAME,
2149 'OTHERS',
2150 x_msg_count,
2151 x_msg_data,
2152 '_PVT'
2153 );
2154 END validate_row;
2155
2156 ---------------------------------------------------------------------------
2157 -- PROCEDURE insert_row
2158 ---------------------------------------------------------------------------
2159 -----------------------------------------
2160 -- insert_row for: OKL_TXL_ITM_INSTS --
2161 -----------------------------------------
2162 PROCEDURE insert_row(
2163 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2164 x_return_status OUT NOCOPY VARCHAR2,
2165 x_msg_count OUT NOCOPY NUMBER,
2166 x_msg_data OUT NOCOPY VARCHAR2,
2167 p_iti_rec IN iti_rec_type,
2168 x_iti_rec OUT NOCOPY iti_rec_type) IS
2169
2170 l_api_version CONSTANT NUMBER := 1;
2171 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
2172 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2173 l_iti_rec iti_rec_type := p_iti_rec;
2174 l_def_iti_rec iti_rec_type;
2175 -----------------------------------------
2176 -- Set_Attributes for: OKL_TXL_ITM_INSTS --
2177 -----------------------------------------
2178 FUNCTION Set_Attributes (
2179 p_iti_rec IN iti_rec_type,
2180 x_iti_rec OUT NOCOPY iti_rec_type
2181 ) RETURN VARCHAR2 IS
2182 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2183 BEGIN
2184 x_iti_rec := p_iti_rec;
2185 RETURN(l_return_status);
2186 END Set_Attributes;
2187 BEGIN
2188 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2189 p_init_msg_list,
2190 '_PVT',
2191 x_return_status);
2192 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2193 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2194 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2195 RAISE OKC_API.G_EXCEPTION_ERROR;
2196 END IF;
2197 --- Setting item attributes
2198 l_return_status := Set_Attributes(
2199 p_iti_rec, -- IN
2200 l_iti_rec); -- OUT
2201 --- If any errors happen abort API
2202 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2203 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2204 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2205 RAISE OKC_API.G_EXCEPTION_ERROR;
2206 END IF;
2207 INSERT INTO OKL_TXL_ITM_INSTS(
2208 ID,
2209 OBJECT_VERSION_NUMBER,
2210 TAS_ID,
2211 TAL_ID,
2212 KLE_ID,
2213 TAL_TYPE,
2214 LINE_NUMBER,
2215 INSTANCE_NUMBER_IB,
2216 OBJECT_ID1_NEW,
2217 OBJECT_ID2_NEW,
2218 JTOT_OBJECT_CODE_NEW,
2219 OBJECT_ID1_OLD,
2220 OBJECT_ID2_OLD,
2221 JTOT_OBJECT_CODE_OLD,
2222 INVENTORY_ORG_ID,
2223 SERIAL_NUMBER,
2224 MFG_SERIAL_NUMBER_YN,
2225 INVENTORY_ITEM_ID,
2226 INV_MASTER_ORG_ID,
2227 ATTRIBUTE_CATEGORY,
2228 ATTRIBUTE1,
2229 ATTRIBUTE2,
2230 ATTRIBUTE3,
2231 ATTRIBUTE4,
2232 ATTRIBUTE5,
2233 ATTRIBUTE6,
2234 ATTRIBUTE7,
2235 ATTRIBUTE8,
2236 ATTRIBUTE9,
2237 ATTRIBUTE10,
2238 ATTRIBUTE11,
2239 ATTRIBUTE12,
2240 ATTRIBUTE13,
2241 ATTRIBUTE14,
2242 ATTRIBUTE15,
2243 CREATED_BY,
2244 CREATION_DATE,
2245 LAST_UPDATED_BY,
2246 LAST_UPDATE_DATE,
2247 LAST_UPDATE_LOGIN,
2248 DNZ_CLE_ID,
2249 --Bug #2697681 schema change :11.5.9 Splitting asset by serail numbers
2250 instance_id,
2251 selected_for_split_flag,
2252 asd_id
2253 )
2254 VALUES (
2255 l_iti_rec.ID,
2256 l_iti_rec.OBJECT_VERSION_NUMBER,
2257 l_iti_rec.TAS_ID,
2258 l_iti_rec.TAL_ID,
2259 l_iti_rec.KLE_ID,
2260 l_iti_rec.TAL_TYPE,
2261 l_iti_rec.LINE_NUMBER,
2262 l_iti_rec.INSTANCE_NUMBER_IB,
2263 l_iti_rec.OBJECT_ID1_NEW,
2264 l_iti_rec.OBJECT_ID2_NEW,
2265 l_iti_rec.JTOT_OBJECT_CODE_NEW,
2266 l_iti_rec.OBJECT_ID1_OLD,
2267 l_iti_rec.OBJECT_ID2_OLD,
2268 l_iti_rec.JTOT_OBJECT_CODE_OLD,
2269 l_iti_rec.INVENTORY_ORG_ID,
2270 l_iti_rec.SERIAL_NUMBER,
2271 l_iti_rec.MFG_SERIAL_NUMBER_YN,
2272 l_iti_rec.INVENTORY_ITEM_ID,
2273 l_iti_rec.INV_MASTER_ORG_ID,
2274 l_iti_rec.ATTRIBUTE_CATEGORY,
2275 l_iti_rec.ATTRIBUTE1,
2276 l_iti_rec.ATTRIBUTE2,
2277 l_iti_rec.ATTRIBUTE3,
2278 l_iti_rec.ATTRIBUTE4,
2279 l_iti_rec.ATTRIBUTE5,
2280 l_iti_rec.ATTRIBUTE6,
2281 l_iti_rec.ATTRIBUTE7,
2282 l_iti_rec.ATTRIBUTE8,
2283 l_iti_rec.ATTRIBUTE9,
2284 l_iti_rec.ATTRIBUTE10,
2285 l_iti_rec.ATTRIBUTE11,
2286 l_iti_rec.ATTRIBUTE12,
2287 l_iti_rec.ATTRIBUTE13,
2288 l_iti_rec.ATTRIBUTE14,
2289 l_iti_rec.ATTRIBUTE15,
2290 l_iti_rec.CREATED_BY,
2291 l_iti_rec.CREATION_DATE,
2292 l_iti_rec.LAST_UPDATED_BY,
2293 l_iti_rec.LAST_UPDATE_DATE,
2294 l_iti_rec.LAST_UPDATE_LOGIN,
2295 l_iti_rec.DNZ_CLE_ID,
2296 --Bug #2697681 schema change :11.5.9 Splitting asset by serail numbers
2297 l_iti_rec.instance_id,
2298 l_iti_rec.selected_for_split_flag,
2299 l_iti_rec.asd_id);
2300 -- Set OUT values
2301 x_iti_rec := l_iti_rec;
2302 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2303 EXCEPTION
2304 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2305 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2306 (
2307 l_api_name,
2308 G_PKG_NAME,
2309 'OKC_API.G_RET_STS_ERROR',
2310 x_msg_count,
2311 x_msg_data,
2312 '_PVT'
2313 );
2314 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2315 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2316 (
2317 l_api_name,
2318 G_PKG_NAME,
2319 'OKC_API.G_RET_STS_UNEXP_ERROR',
2320 x_msg_count,
2321 x_msg_data,
2322 '_PVT'
2323 );
2324 WHEN OTHERS THEN
2325 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2326 (
2327 l_api_name,
2328 G_PKG_NAME,
2329 'OTHERS',
2330 x_msg_count,
2331 x_msg_data,
2332 '_PVT'
2333 );
2334 END insert_row;
2335 -------------------------------------
2336 -- insert_row for: OKL_TXL_ITM_INSTS_V --
2337 -------------------------------------
2338 PROCEDURE insert_row(
2339 p_api_version IN NUMBER,
2340 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2341 x_return_status OUT NOCOPY VARCHAR2,
2342 x_msg_count OUT NOCOPY NUMBER,
2343 x_msg_data OUT NOCOPY VARCHAR2,
2344 p_itiv_rec IN itiv_rec_type,
2345 x_itiv_rec OUT NOCOPY itiv_rec_type) IS
2346
2347 l_api_version CONSTANT NUMBER := 1;
2348 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2349 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2350 l_itiv_rec itiv_rec_type;
2351 l_def_itiv_rec itiv_rec_type;
2352 l_iti_rec iti_rec_type;
2353 lx_iti_rec iti_rec_type;
2354
2355 -------------------------------
2356 -- FUNCTION fill_who_columns --
2357 -------------------------------
2358 FUNCTION fill_who_columns (
2359 p_itiv_rec IN itiv_rec_type
2360 ) RETURN itiv_rec_type IS
2361 l_itiv_rec itiv_rec_type := p_itiv_rec;
2362 BEGIN
2363 l_itiv_rec.CREATION_DATE := SYSDATE;
2364 l_itiv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2365 l_itiv_rec.LAST_UPDATE_DATE := SYSDATE;
2366 l_itiv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2367 l_itiv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2368 RETURN(l_itiv_rec);
2369 END fill_who_columns;
2370 ---------------------------------------------
2371 -- Set_Attributes for: OKL_TXL_ITM_INSTS_V --
2372 ---------------------------------------------
2373 FUNCTION Set_Attributes (
2374 p_itiv_rec IN itiv_rec_type,
2375 x_itiv_rec OUT NOCOPY itiv_rec_type
2376 ) RETURN VARCHAR2 IS
2377 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2378 BEGIN
2379 x_itiv_rec := p_itiv_rec;
2380 x_itiv_rec.OBJECT_VERSION_NUMBER := 1;
2381 RETURN(l_return_status);
2382 END Set_Attributes;
2383 BEGIN
2384 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2385 G_PKG_NAME,
2386 p_init_msg_list,
2387 l_api_version,
2388 p_api_version,
2389 '_PVT',
2390 x_return_status);
2391 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2392 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2393 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2394 RAISE OKC_API.G_EXCEPTION_ERROR;
2395 END IF;
2396 l_itiv_rec := null_out_defaults(p_itiv_rec);
2397 -- Set primary key value
2398 l_itiv_rec.ID := get_seq_id;
2399 --- Setting item attributes
2400 l_return_status := Set_Attributes(
2401 l_itiv_rec, -- IN
2402 l_def_itiv_rec); -- OUT
2403 --- If any errors happen abort API
2404 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2405 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2406 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2407 RAISE OKC_API.G_EXCEPTION_ERROR;
2408 END IF;
2409 l_def_itiv_rec := fill_who_columns(l_def_itiv_rec);
2410 --- Validate all non-missing attributes (Item Level Validation)
2411 l_return_status := Validate_Attributes(l_def_itiv_rec);
2412 --- If any errors happen abort API
2413 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2414 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2415 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2416 RAISE OKC_API.G_EXCEPTION_ERROR;
2417 END IF;
2418 l_return_status := Validate_Record(l_def_itiv_rec);
2419 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2420 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2421 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2422 RAISE OKC_API.G_EXCEPTION_ERROR;
2423 END IF;
2424 --------------------------------------
2425 -- Move VIEW record to "Child" records
2426 --------------------------------------
2427 migrate(l_def_itiv_rec, l_iti_rec);
2428 --------------------------------------------
2429 -- Call the INSERT_ROW for each child record
2430 --------------------------------------------
2431 insert_row(
2432 p_init_msg_list,
2433 x_return_status,
2434 x_msg_count,
2435 x_msg_data,
2436 l_iti_rec,
2437 lx_iti_rec
2438 );
2439 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2440 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2441 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2442 RAISE OKC_API.G_EXCEPTION_ERROR;
2443 END IF;
2444 migrate(lx_iti_rec, l_def_itiv_rec);
2445 x_itiv_rec := l_def_itiv_rec;
2446 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2447 EXCEPTION
2448 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2449 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2450 (
2451 l_api_name,
2452 G_PKG_NAME,
2453 'OKC_API.G_RET_STS_ERROR',
2454 x_msg_count,
2455 x_msg_data,
2456 '_PVT'
2457 );
2458 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2459 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2460 (
2461 l_api_name,
2462 G_PKG_NAME,
2463 'OKC_API.G_RET_STS_UNEXP_ERROR',
2464 x_msg_count,
2465 x_msg_data,
2466 '_PVT'
2467 );
2468 WHEN OTHERS THEN
2469 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2470 (
2471 l_api_name,
2472 G_PKG_NAME,
2473 'OTHERS',
2474 x_msg_count,
2475 x_msg_data,
2476 '_PVT'
2477 );
2478 END insert_row;
2479 ----------------------------------------
2480 -- PL/SQL TBL insert_row for:ITIV_TBL --
2481 ----------------------------------------
2482 PROCEDURE insert_row(
2483 p_api_version IN NUMBER,
2484 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2485 x_return_status OUT NOCOPY VARCHAR2,
2486 x_msg_count OUT NOCOPY NUMBER,
2487 x_msg_data OUT NOCOPY VARCHAR2,
2488 p_itiv_tbl IN itiv_tbl_type,
2489 x_itiv_tbl OUT NOCOPY itiv_tbl_type) IS
2490
2491 l_api_version CONSTANT NUMBER := 1;
2492 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2493 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2494 i NUMBER := 0;
2495 BEGIN
2496 OKC_API.init_msg_list(p_init_msg_list);
2497 -- Make sure PL/SQL table has records in it before passing
2498 IF (p_itiv_tbl.COUNT > 0) THEN
2499 i := p_itiv_tbl.FIRST;
2500 LOOP
2501 insert_row (
2502 p_api_version => p_api_version,
2503 p_init_msg_list => OKC_API.G_FALSE,
2504 x_return_status => x_return_status,
2505 x_msg_count => x_msg_count,
2506 x_msg_data => x_msg_data,
2507 p_itiv_rec => p_itiv_tbl(i),
2508 x_itiv_rec => x_itiv_tbl(i));
2509 EXIT WHEN (i = p_itiv_tbl.LAST);
2510 i := p_itiv_tbl.NEXT(i);
2511 END LOOP;
2512 END IF;
2513 EXCEPTION
2514 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2515 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2516 (
2517 l_api_name,
2518 G_PKG_NAME,
2519 'OKC_API.G_RET_STS_ERROR',
2520 x_msg_count,
2521 x_msg_data,
2522 '_PVT'
2523 );
2524 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2525 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2526 (
2527 l_api_name,
2528 G_PKG_NAME,
2529 'OKC_API.G_RET_STS_UNEXP_ERROR',
2530 x_msg_count,
2531 x_msg_data,
2532 '_PVT'
2533 );
2534 WHEN OTHERS THEN
2535 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2536 (
2537 l_api_name,
2538 G_PKG_NAME,
2539 'OTHERS',
2540 x_msg_count,
2541 x_msg_data,
2542 '_PVT'
2543 );
2544 END insert_row;
2545
2546 ---------------------------------------------------------------------------
2547 -- PROCEDURE lock_row
2548 ---------------------------------------------------------------------------
2549 -----------------------------------
2550 -- lock_row for: OKL_TXL_ITM_INSTS --
2551 -----------------------------------
2552 PROCEDURE lock_row(
2553 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2554 x_return_status OUT NOCOPY VARCHAR2,
2555 x_msg_count OUT NOCOPY NUMBER,
2556 x_msg_data OUT NOCOPY VARCHAR2,
2557 p_iti_rec IN iti_rec_type) IS
2558
2559 E_Resource_Busy EXCEPTION;
2560 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2561 CURSOR lock_csr (p_iti_rec IN iti_rec_type) IS
2562 SELECT OBJECT_VERSION_NUMBER
2563 FROM OKL_TXL_ITM_INSTS
2564 WHERE ID = p_iti_rec.id
2565 AND OBJECT_VERSION_NUMBER = p_iti_rec.object_version_number
2566 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2567
2568 CURSOR lchk_csr (p_iti_rec IN iti_rec_type) IS
2569 SELECT OBJECT_VERSION_NUMBER
2570 FROM OKL_TXL_ITM_INSTS
2571 WHERE ID = p_iti_rec.id;
2572 l_api_version CONSTANT NUMBER := 1;
2573 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2574 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2575 l_object_version_number OKL_TXL_ASSETS_B.OBJECT_VERSION_NUMBER%TYPE;
2576 lc_object_version_number OKL_TXL_ASSETS_B.OBJECT_VERSION_NUMBER%TYPE;
2577 l_row_notfound BOOLEAN := FALSE;
2578 lc_row_notfound BOOLEAN := FALSE;
2579 BEGIN
2580 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2581 p_init_msg_list,
2582 '_PVT',
2583 x_return_status);
2584 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2585 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2586 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2587 RAISE OKC_API.G_EXCEPTION_ERROR;
2588 END IF;
2589 BEGIN
2590 OPEN lock_csr(p_iti_rec);
2591 FETCH lock_csr INTO l_object_version_number;
2592 l_row_notfound := lock_csr%NOTFOUND;
2593 CLOSE lock_csr;
2594 EXCEPTION
2595 WHEN E_Resource_Busy THEN
2596 IF (lock_csr%ISOPEN) THEN
2597 CLOSE lock_csr;
2598 END IF;
2599 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2600 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2601 END;
2602
2603 IF ( l_row_notfound ) THEN
2604 OPEN lchk_csr(p_iti_rec);
2605 FETCH lchk_csr INTO lc_object_version_number;
2606 lc_row_notfound := lchk_csr%NOTFOUND;
2607 CLOSE lchk_csr;
2608 END IF;
2609 IF (lc_row_notfound) THEN
2610 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2611 RAISE OKC_API.G_EXCEPTION_ERROR;
2612 ELSIF lc_object_version_number > p_iti_rec.object_version_number THEN
2613 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2614 RAISE OKC_API.G_EXCEPTION_ERROR;
2615 ELSIF lc_object_version_number <> p_iti_rec.object_version_number THEN
2616 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2617 RAISE OKC_API.G_EXCEPTION_ERROR;
2618 ELSIF lc_object_version_number = -1 THEN
2619 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2620 RAISE OKC_API.G_EXCEPTION_ERROR;
2621 END IF;
2622 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2623 EXCEPTION
2624 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2625 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2626 (
2627 l_api_name,
2628 G_PKG_NAME,
2629 'OKC_API.G_RET_STS_ERROR',
2630 x_msg_count,
2631 x_msg_data,
2632 '_PVT'
2633 );
2634 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2635 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2636 (
2637 l_api_name,
2638 G_PKG_NAME,
2639 'OKC_API.G_RET_STS_UNEXP_ERROR',
2640 x_msg_count,
2641 x_msg_data,
2642 '_PVT'
2643 );
2644 WHEN OTHERS THEN
2645 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2646 (
2647 l_api_name,
2648 G_PKG_NAME,
2649 'OTHERS',
2650 x_msg_count,
2651 x_msg_data,
2652 '_PVT'
2653 );
2654 END lock_row;
2655 -----------------------------------
2656 -- lock_row for: OKL_TXL_ITM_INSTS_V --
2657 -----------------------------------
2658 PROCEDURE lock_row(
2659 p_api_version IN NUMBER,
2660 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2661 x_return_status OUT NOCOPY VARCHAR2,
2662 x_msg_count OUT NOCOPY NUMBER,
2663 x_msg_data OUT NOCOPY VARCHAR2,
2664 p_itiv_rec IN itiv_rec_type) IS
2665
2666 l_api_version CONSTANT NUMBER := 1;
2667 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2668 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2669 l_iti_rec iti_rec_type;
2670 BEGIN
2671 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2672 G_PKG_NAME,
2673 p_init_msg_list,
2674 l_api_version,
2675 p_api_version,
2676 '_PVT',
2677 x_return_status);
2678 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2679 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2680 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2681 RAISE OKC_API.G_EXCEPTION_ERROR;
2682 END IF;
2683 --------------------------------------
2684 -- Move VIEW record to "Child" records
2685 --------------------------------------
2686 migrate(p_itiv_rec, l_iti_rec);
2687 --------------------------------------------
2688 -- Call the LOCK_ROW for each child record
2689 --------------------------------------------
2690 lock_row(
2691 p_init_msg_list,
2692 x_return_status,
2693 x_msg_count,
2694 x_msg_data,
2695 l_iti_rec
2696 );
2697 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2698 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2699 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2700 RAISE OKC_API.G_EXCEPTION_ERROR;
2701 END IF;
2702 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2703 EXCEPTION
2704 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2705 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2706 (
2707 l_api_name,
2708 G_PKG_NAME,
2709 'OKC_API.G_RET_STS_ERROR',
2710 x_msg_count,
2711 x_msg_data,
2712 '_PVT'
2713 );
2714 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2715 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2716 (
2717 l_api_name,
2718 G_PKG_NAME,
2719 'OKC_API.G_RET_STS_UNEXP_ERROR',
2720 x_msg_count,
2721 x_msg_data,
2722 '_PVT'
2723 );
2724 WHEN OTHERS THEN
2725 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2726 (
2727 l_api_name,
2728 G_PKG_NAME,
2729 'OTHERS',
2730 x_msg_count,
2731 x_msg_data,
2732 '_PVT'
2733 );
2734 END lock_row;
2735 --------------------------------------
2736 -- PL/SQL TBL lock_row for:ITIV_TBL --
2737 --------------------------------------
2738 PROCEDURE lock_row(
2739 p_api_version IN NUMBER,
2740 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2741 x_return_status OUT NOCOPY VARCHAR2,
2742 x_msg_count OUT NOCOPY NUMBER,
2743 x_msg_data OUT NOCOPY VARCHAR2,
2744 p_itiv_tbl IN itiv_tbl_type) IS
2745
2746 l_api_version CONSTANT NUMBER := 1;
2747 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2748 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2749 i NUMBER := 0;
2750 BEGIN
2751 OKC_API.init_msg_list(p_init_msg_list);
2752 -- Make sure PL/SQL table has records in it before passing
2753 IF (p_itiv_tbl.COUNT > 0) THEN
2754 i := p_itiv_tbl.FIRST;
2755 LOOP
2756 lock_row (
2757 p_api_version => p_api_version,
2758 p_init_msg_list => OKC_API.G_FALSE,
2759 x_return_status => x_return_status,
2760 x_msg_count => x_msg_count,
2761 x_msg_data => x_msg_data,
2762 p_itiv_rec => p_itiv_tbl(i));
2763 EXIT WHEN (i = p_itiv_tbl.LAST);
2764 i := p_itiv_tbl.NEXT(i);
2765 END LOOP;
2766 END IF;
2767 EXCEPTION
2768 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2769 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2770 (
2771 l_api_name,
2772 G_PKG_NAME,
2773 'OKC_API.G_RET_STS_ERROR',
2774 x_msg_count,
2775 x_msg_data,
2776 '_PVT'
2777 );
2778 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2779 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2780 (
2781 l_api_name,
2782 G_PKG_NAME,
2783 'OKC_API.G_RET_STS_UNEXP_ERROR',
2784 x_msg_count,
2785 x_msg_data,
2786 '_PVT'
2787 );
2788 WHEN OTHERS THEN
2789 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2790 (
2791 l_api_name,
2792 G_PKG_NAME,
2793 'OTHERS',
2794 x_msg_count,
2795 x_msg_data,
2796 '_PVT'
2797 );
2798 END lock_row;
2799
2800 ---------------------------------------------------------------------------
2801 -- PROCEDURE update_row
2802 ---------------------------------------------------------------------------
2803 -----------------------------------------
2804 -- update_row for: OKL_TXL_ITM_INSTS --
2805 -----------------------------------------
2806 PROCEDURE update_row(
2807 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2808 x_return_status OUT NOCOPY VARCHAR2,
2809 x_msg_count OUT NOCOPY NUMBER,
2810 x_msg_data OUT NOCOPY VARCHAR2,
2811 p_iti_rec IN iti_rec_type,
2812 x_iti_rec OUT NOCOPY iti_rec_type) IS
2813
2814 l_api_version CONSTANT NUMBER := 1;
2815 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2816 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2817 l_iti_rec iti_rec_type := p_iti_rec;
2818 l_def_iti_rec iti_rec_type;
2819 l_row_notfound BOOLEAN := TRUE;
2820 ----------------------------------
2821 -- FUNCTION populate_new_record --
2822 ----------------------------------
2823 FUNCTION populate_new_record (
2824 p_iti_rec IN iti_rec_type,
2825 x_iti_rec OUT NOCOPY iti_rec_type
2826 ) RETURN VARCHAR2 IS
2827 l_iti_rec iti_rec_type;
2828 l_row_notfound BOOLEAN := TRUE;
2829 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2830 BEGIN
2831 x_iti_rec := p_iti_rec;
2832 -- Get current database values
2833 l_iti_rec := get_rec(p_iti_rec, l_row_notfound);
2834 IF (l_row_notfound) THEN
2835 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2836 END IF;
2837 IF (x_iti_rec.id = OKC_API.G_MISS_NUM)
2838 THEN
2839 x_iti_rec.id := l_iti_rec.id;
2840 END IF;
2841 IF (x_iti_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
2842 x_iti_rec.object_version_number := l_iti_rec.object_version_number;
2843 END IF;
2844 IF (x_iti_rec.tas_id = OKC_API.G_MISS_NUM) THEN
2845 x_iti_rec.tas_id := l_iti_rec.tas_id;
2846 END IF;
2847 IF (x_iti_rec.tal_id = OKC_API.G_MISS_NUM) THEN
2848 x_iti_rec.tal_id := l_iti_rec.tal_id;
2849 END IF;
2850 IF (x_iti_rec.kle_id = OKC_API.G_MISS_NUM) THEN
2851 x_iti_rec.kle_id := l_iti_rec.kle_id;
2852 END IF;
2853 IF (x_iti_rec.tal_type = OKC_API.G_MISS_CHAR) THEN
2854 x_iti_rec.tal_type := l_iti_rec.tal_type;
2855 END IF;
2856 IF (x_iti_rec.line_number = OKC_API.G_MISS_NUM) THEN
2857 x_iti_rec.line_number := l_iti_rec.line_number;
2858 END IF;
2859 IF (x_iti_rec.instance_number_ib = OKC_API.G_MISS_CHAR) THEN
2860 x_iti_rec.instance_number_ib := l_iti_rec.instance_number_ib;
2861 END IF;
2862 IF (x_iti_rec.object_id1_new = OKC_API.G_MISS_CHAR) THEN
2863 x_iti_rec.object_id1_new := l_iti_rec.object_id1_new;
2864 END IF;
2865 IF (x_iti_rec.object_id2_new = OKC_API.G_MISS_CHAR) THEN
2866 x_iti_rec.object_id2_new := l_iti_rec.object_id2_new;
2867 END IF;
2868 IF (x_iti_rec.jtot_object_code_new = OKC_API.G_MISS_CHAR) THEN
2869 x_iti_rec.jtot_object_code_new := l_iti_rec.jtot_object_code_new;
2870 END IF;
2871 IF (x_iti_rec.object_id1_old = OKC_API.G_MISS_CHAR) THEN
2872 x_iti_rec.object_id1_old := l_iti_rec.object_id1_old;
2873 END IF;
2874 IF (x_iti_rec.object_id2_old = OKC_API.G_MISS_CHAR) THEN
2875 x_iti_rec.object_id2_old := l_iti_rec.object_id2_old;
2876 END IF;
2877 IF (x_iti_rec.jtot_object_code_old = OKC_API.G_MISS_CHAR) THEN
2878 x_iti_rec.jtot_object_code_old := l_iti_rec.jtot_object_code_old;
2879 END IF;
2880 IF (x_iti_rec.inventory_org_id = OKC_API.G_MISS_NUM) THEN
2881 x_iti_rec.inventory_org_id := l_iti_rec.inventory_org_id;
2882 END IF;
2883 IF (x_iti_rec.serial_number = OKC_API.G_MISS_CHAR) THEN
2884 x_iti_rec.serial_number := l_iti_rec.serial_number;
2885 END IF;
2886 IF (x_iti_rec.mfg_serial_number_yn = OKC_API.G_MISS_CHAR) THEN
2887 x_iti_rec.mfg_serial_number_yn := l_iti_rec.mfg_serial_number_yn;
2888 END IF;
2889 IF (x_iti_rec.inventory_item_id = OKC_API.G_MISS_NUM) THEN
2890 x_iti_rec.inventory_item_id := l_iti_rec.inventory_item_id;
2891 END IF;
2892 IF (x_iti_rec.inv_master_org_id = OKC_API.G_MISS_NUM) THEN
2893 x_iti_rec.inv_master_org_id := l_iti_rec.inv_master_org_id;
2894 END IF;
2895 IF (x_iti_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
2896 x_iti_rec.attribute_category := l_iti_rec.attribute_category;
2897 END IF;
2898 IF (x_iti_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
2899 x_iti_rec.attribute1 := l_iti_rec.attribute1;
2900 END IF;
2901 IF (x_iti_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
2902 x_iti_rec.attribute2 := l_iti_rec.attribute2;
2903 END IF;
2904 IF (x_iti_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
2905 x_iti_rec.attribute3 := l_iti_rec.attribute3;
2906 END IF;
2907 IF (x_iti_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
2908 x_iti_rec.attribute4 := l_iti_rec.attribute4;
2909 END IF;
2910 IF (x_iti_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
2911 x_iti_rec.attribute5 := l_iti_rec.attribute5;
2912 END IF;
2913 IF (x_iti_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
2914 x_iti_rec.attribute6 := l_iti_rec.attribute6;
2915 END IF;
2916 IF (x_iti_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
2917 x_iti_rec.attribute7 := l_iti_rec.attribute7;
2918 END IF;
2919 IF (x_iti_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
2920 x_iti_rec.attribute8 := l_iti_rec.attribute8;
2921 END IF;
2922 IF (x_iti_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
2923 x_iti_rec.attribute9 := l_iti_rec.attribute9;
2924 END IF;
2925 IF (x_iti_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
2926 x_iti_rec.attribute10 := l_iti_rec.attribute10;
2927 END IF;
2928 IF (x_iti_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
2929 x_iti_rec.attribute11 := l_iti_rec.attribute11;
2930 END IF;
2931 IF (x_iti_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
2932 x_iti_rec.attribute12 := l_iti_rec.attribute12;
2933 END IF;
2934 IF (x_iti_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
2935 x_iti_rec.attribute13 := l_iti_rec.attribute13;
2936 END IF;
2937 IF (x_iti_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
2938 x_iti_rec.attribute14 := l_iti_rec.attribute14;
2939 END IF;
2940 IF (x_iti_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
2941 x_iti_rec.attribute15 := l_iti_rec.attribute15;
2942 END IF;
2943 IF (x_iti_rec.created_by = OKC_API.G_MISS_NUM) THEN
2944 x_iti_rec.created_by := l_iti_rec.created_by;
2945 END IF;
2946 IF (x_iti_rec.creation_date = OKC_API.G_MISS_DATE) THEN
2947 x_iti_rec.creation_date := l_iti_rec.creation_date;
2948 END IF;
2949 IF (x_iti_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
2950 x_iti_rec.last_updated_by := l_iti_rec.last_updated_by;
2951 END IF;
2952 IF (x_iti_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
2953 x_iti_rec.last_update_date := l_iti_rec.last_update_date;
2954 END IF;
2955 IF (x_iti_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
2956 x_iti_rec.last_update_login := l_iti_rec.last_update_login;
2957 END IF;
2958 IF (x_iti_rec.dnz_cle_id = OKC_API.G_MISS_NUM) THEN
2959 x_iti_rec.dnz_cle_id := l_iti_rec.dnz_cle_id;
2960 END IF;
2961 --Bug #2697681 schema change :11.5.9 Splitting asset by serail numbers
2962 IF (x_iti_rec.instance_id = OKC_API.G_MISS_NUM) THEN
2963 x_iti_rec.instance_id := l_iti_rec.instance_id;
2964 END IF;
2965 IF (x_iti_rec.selected_for_split_flag = OKC_API.G_MISS_CHAR) THEN
2966 x_iti_rec.selected_for_split_flag := l_iti_rec.selected_for_split_flag;
2967 END IF;
2968 IF (x_iti_rec.asd_id = OKC_API.G_MISS_NUM) THEN
2969 x_iti_rec.asd_id := l_iti_rec.asd_id;
2970 END IF;
2971 RETURN(l_return_status);
2972 END populate_new_record;
2973 -----------------------------------------
2974 -- Set_Attributes for:OKL_TXL_ITM_INSTS --
2975 -----------------------------------------
2976 FUNCTION Set_Attributes (
2977 p_iti_rec IN iti_rec_type,
2978 x_iti_rec OUT NOCOPY iti_rec_type
2979 ) RETURN VARCHAR2 IS
2980 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2981 BEGIN
2982 x_iti_rec := p_iti_rec;
2983 RETURN(l_return_status);
2984 END Set_Attributes;
2985 BEGIN
2986 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2987 p_init_msg_list,
2988 '_PVT',
2989 x_return_status);
2990 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2991 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2992 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2993 RAISE OKC_API.G_EXCEPTION_ERROR;
2994 END IF;
2995 --- Setting item attributes
2996 l_return_status := Set_Attributes(
2997 p_iti_rec, -- IN
2998 l_iti_rec); -- OUT
2999 --- If any errors happen abort API
3000 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3001 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3002 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3003 RAISE OKC_API.G_EXCEPTION_ERROR;
3004 END IF;
3005 l_return_status := populate_new_record(l_iti_rec, l_def_iti_rec);
3006 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3007 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3008 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3009 RAISE OKC_API.G_EXCEPTION_ERROR;
3010 END IF;
3011 UPDATE OKL_TXL_ITM_INSTS
3012 SET ID = l_def_iti_rec.ID,
3013 OBJECT_VERSION_NUMBER = l_def_iti_rec.OBJECT_VERSION_NUMBER,
3014 TAS_ID = l_def_iti_rec.TAS_ID,
3015 TAL_ID = l_def_iti_rec.TAL_ID,
3016 KLE_ID = l_def_iti_rec.KLE_ID,
3017 TAL_TYPE = l_def_iti_rec.TAL_TYPE,
3018 LINE_NUMBER = l_def_iti_rec.LINE_NUMBER,
3019 INSTANCE_NUMBER_IB = l_def_iti_rec.INSTANCE_NUMBER_IB,
3020 OBJECT_ID1_NEW = l_def_iti_rec.OBJECT_ID1_NEW,
3021 OBJECT_ID2_NEW = l_def_iti_rec.OBJECT_ID2_NEW,
3022 JTOT_OBJECT_CODE_NEW = l_def_iti_rec.JTOT_OBJECT_CODE_NEW,
3023 OBJECT_ID1_OLD = l_def_iti_rec.OBJECT_ID1_OLD,
3024 OBJECT_ID2_OLD = l_def_iti_rec.OBJECT_ID2_OLD,
3025 JTOT_OBJECT_CODE_OLD = l_def_iti_rec.JTOT_OBJECT_CODE_OLD,
3026 INVENTORY_ORG_ID = l_def_iti_rec.INVENTORY_ORG_ID,
3027 SERIAL_NUMBER = l_def_iti_rec.SERIAL_NUMBER,
3028 MFG_SERIAL_NUMBER_YN = l_def_iti_rec.MFG_SERIAL_NUMBER_YN,
3029 INVENTORY_ITEM_ID = l_def_iti_rec.INVENTORY_ITEM_ID,
3030 INV_MASTER_ORG_ID = l_def_iti_rec.INV_MASTER_ORG_ID,
3031 ATTRIBUTE_CATEGORY = l_def_iti_rec.ATTRIBUTE_CATEGORY,
3032 ATTRIBUTE1 = l_def_iti_rec.ATTRIBUTE1,
3033 ATTRIBUTE2 = l_def_iti_rec.ATTRIBUTE2,
3034 ATTRIBUTE3 = l_def_iti_rec.ATTRIBUTE3,
3035 ATTRIBUTE4 = l_def_iti_rec.ATTRIBUTE4,
3036 ATTRIBUTE5 = l_def_iti_rec.ATTRIBUTE5,
3037 ATTRIBUTE6 = l_def_iti_rec.ATTRIBUTE6,
3038 ATTRIBUTE7 = l_def_iti_rec.ATTRIBUTE7,
3039 ATTRIBUTE8 = l_def_iti_rec.ATTRIBUTE8,
3040 ATTRIBUTE9 = l_def_iti_rec.ATTRIBUTE9,
3041 ATTRIBUTE10 = l_def_iti_rec.ATTRIBUTE10,
3042 ATTRIBUTE11 = l_def_iti_rec.ATTRIBUTE11,
3043 ATTRIBUTE12 = l_def_iti_rec.ATTRIBUTE12,
3044 ATTRIBUTE13 = l_def_iti_rec.ATTRIBUTE13,
3045 ATTRIBUTE14 = l_def_iti_rec.ATTRIBUTE14,
3046 ATTRIBUTE15 = l_def_iti_rec.ATTRIBUTE15,
3047 CREATED_BY = l_def_iti_rec.CREATED_BY,
3048 CREATION_DATE = l_def_iti_rec.CREATION_DATE,
3049 LAST_UPDATED_BY = l_def_iti_rec.LAST_UPDATED_BY,
3050 LAST_UPDATE_DATE = l_def_iti_rec.LAST_UPDATE_DATE,
3051 LAST_UPDATE_LOGIN = l_def_iti_rec.LAST_UPDATE_LOGIN,
3052 DNZ_CLE_ID = l_def_iti_rec.DNZ_CLE_ID,
3053 --Bug #2697681 schema change :11.5.9 Splitting asset by serail numbers
3054 instance_id = l_def_iti_rec.instance_id,
3055 selected_for_split_flag = l_def_iti_rec.selected_for_split_flag,
3056 asd_id = l_def_iti_rec.asd_id
3057 WHERE ID = l_def_iti_rec.id;
3058 x_iti_rec := l_def_iti_rec;
3059 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3060 EXCEPTION
3061 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3062 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3063 (
3064 l_api_name,
3065 G_PKG_NAME,
3066 'OKC_API.G_RET_STS_ERROR',
3067 x_msg_count,
3068 x_msg_data,
3069 '_PVT'
3070 );
3071 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3072 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3073 (
3074 l_api_name,
3075 G_PKG_NAME,
3076 'OKC_API.G_RET_STS_UNEXP_ERROR',
3077 x_msg_count,
3078 x_msg_data,
3079 '_PVT'
3080 );
3081 WHEN OTHERS THEN
3082 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3083 (
3084 l_api_name,
3085 G_PKG_NAME,
3086 'OTHERS',
3087 x_msg_count,
3088 x_msg_data,
3089 '_PVT'
3090 );
3091 END update_row;
3092 ----------------------------------------
3093 -- update_row for:OKL_TXL_ITM_INSTS_V --
3094 ----------------------------------------
3095 PROCEDURE update_row(
3096 p_api_version IN NUMBER,
3097 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3098 x_return_status OUT NOCOPY VARCHAR2,
3099 x_msg_count OUT NOCOPY NUMBER,
3100 x_msg_data OUT NOCOPY VARCHAR2,
3101 p_itiv_rec IN itiv_rec_type,
3102 x_itiv_rec OUT NOCOPY itiv_rec_type) IS
3103
3104 l_api_version CONSTANT NUMBER := 1;
3105 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3106 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3107 l_itiv_rec itiv_rec_type := p_itiv_rec;
3108 l_def_itiv_rec itiv_rec_type;
3109 l_iti_rec iti_rec_type;
3110 lx_iti_rec iti_rec_type;
3111 -------------------------------
3112 -- FUNCTION fill_who_columns --
3113 -------------------------------
3114 FUNCTION fill_who_columns (
3115 p_itiv_rec IN itiv_rec_type
3116 ) RETURN itiv_rec_type IS
3117 l_itiv_rec itiv_rec_type := p_itiv_rec;
3118 BEGIN
3119 l_itiv_rec.LAST_UPDATE_DATE := SYSDATE;
3120 l_itiv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3121 l_itiv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3122 RETURN(l_itiv_rec);
3123 END fill_who_columns;
3124 ----------------------------------
3125 -- FUNCTION populate_new_record --
3126 ----------------------------------
3127 FUNCTION populate_new_record (
3128 p_itiv_rec IN itiv_rec_type,
3129 x_itiv_rec OUT NOCOPY itiv_rec_type
3130 ) RETURN VARCHAR2 IS
3131 l_itiv_rec itiv_rec_type;
3132 l_row_notfound BOOLEAN := TRUE;
3133 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3134 BEGIN
3135 x_itiv_rec := p_itiv_rec;
3136 -- Get current database values
3137 l_itiv_rec := get_rec(p_itiv_rec, l_row_notfound);
3138 IF (l_row_notfound) THEN
3139 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3140 END IF;
3141 IF (x_itiv_rec.id = OKC_API.G_MISS_NUM)
3142 THEN
3143 x_itiv_rec.id := l_itiv_rec.id;
3144 END IF;
3145 IF (x_itiv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
3146 x_itiv_rec.object_version_number := l_itiv_rec.object_version_number;
3147 END IF;
3148 IF (x_itiv_rec.tas_id = OKC_API.G_MISS_NUM) THEN
3149 x_itiv_rec.tas_id := l_itiv_rec.tas_id;
3150 END IF;
3151 IF (x_itiv_rec.tal_id = OKC_API.G_MISS_NUM) THEN
3152 x_itiv_rec.tal_id := l_itiv_rec.tal_id;
3153 END IF;
3154 IF (x_itiv_rec.kle_id = OKC_API.G_MISS_NUM) THEN
3155 x_itiv_rec.kle_id := l_itiv_rec.kle_id;
3156 END IF;
3157 IF (x_itiv_rec.tal_type = OKC_API.G_MISS_CHAR) THEN
3158 x_itiv_rec.tal_type := l_itiv_rec.tal_type;
3159 END IF;
3160 IF (x_itiv_rec.line_number = OKC_API.G_MISS_NUM) THEN
3161 x_itiv_rec.line_number := l_itiv_rec.line_number;
3162 END IF;
3163 IF (x_itiv_rec.instance_number_ib = OKC_API.G_MISS_CHAR) THEN
3164 x_itiv_rec.instance_number_ib := l_itiv_rec.instance_number_ib;
3165 END IF;
3166 IF (x_itiv_rec.object_id1_new = OKC_API.G_MISS_CHAR) THEN
3167 x_itiv_rec.object_id1_new := l_itiv_rec.object_id1_new;
3168 END IF;
3169 IF (x_itiv_rec.object_id2_new = OKC_API.G_MISS_CHAR) THEN
3170 x_itiv_rec.object_id2_new := l_itiv_rec.object_id2_new;
3171 END IF;
3172 IF (x_itiv_rec.jtot_object_code_new = OKC_API.G_MISS_CHAR) THEN
3173 x_itiv_rec.jtot_object_code_new := l_itiv_rec.jtot_object_code_new;
3174 END IF;
3175 IF (x_itiv_rec.object_id1_old = OKC_API.G_MISS_CHAR) THEN
3176 x_itiv_rec.object_id1_old := l_itiv_rec.object_id1_old;
3177 END IF;
3178 IF (x_itiv_rec.object_id2_old = OKC_API.G_MISS_CHAR) THEN
3179 x_itiv_rec.object_id2_old := l_itiv_rec.object_id2_old;
3180 END IF;
3181 IF (x_itiv_rec.jtot_object_code_old = OKC_API.G_MISS_CHAR) THEN
3182 x_itiv_rec.jtot_object_code_old := l_itiv_rec.jtot_object_code_old;
3183 END IF;
3184 IF (x_itiv_rec.inventory_org_id = OKC_API.G_MISS_NUM) THEN
3185 x_itiv_rec.inventory_org_id := l_itiv_rec.inventory_org_id;
3186 END IF;
3187 IF (x_itiv_rec.serial_number = OKC_API.G_MISS_CHAR) THEN
3188 x_itiv_rec.serial_number := l_itiv_rec.serial_number;
3189 END IF;
3190 IF (x_itiv_rec.mfg_serial_number_yn = OKC_API.G_MISS_CHAR) THEN
3191 x_itiv_rec.mfg_serial_number_yn := l_itiv_rec.mfg_serial_number_yn;
3192 END IF;
3193 IF (x_itiv_rec.inventory_item_id = OKC_API.G_MISS_NUM) THEN
3194 x_itiv_rec.inventory_item_id := l_itiv_rec.inventory_item_id;
3195 END IF;
3196 IF (x_itiv_rec.inv_master_org_id = OKC_API.G_MISS_NUM) THEN
3197 x_itiv_rec.inv_master_org_id := l_itiv_rec.inv_master_org_id;
3198 END IF;
3199 IF (x_itiv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
3200 x_itiv_rec.attribute_category := l_itiv_rec.attribute_category;
3201 END IF;
3202 IF (x_itiv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
3203 x_itiv_rec.attribute1 := l_itiv_rec.attribute1;
3204 END IF;
3205 IF (x_itiv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
3206 x_itiv_rec.attribute2 := l_itiv_rec.attribute2;
3207 END IF;
3208 IF (x_itiv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
3209 x_itiv_rec.attribute3 := l_itiv_rec.attribute3;
3210 END IF;
3211 IF (x_itiv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
3212 x_itiv_rec.attribute4 := l_itiv_rec.attribute4;
3213 END IF;
3214 IF (x_itiv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
3215 x_itiv_rec.attribute5 := l_itiv_rec.attribute5;
3216 END IF;
3217 IF (x_itiv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
3218 x_itiv_rec.attribute6 := l_itiv_rec.attribute6;
3219 END IF;
3220 IF (x_itiv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
3221 x_itiv_rec.attribute7 := l_itiv_rec.attribute7;
3222 END IF;
3223 IF (x_itiv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
3224 x_itiv_rec.attribute8 := l_itiv_rec.attribute8;
3225 END IF;
3226 IF (x_itiv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
3227 x_itiv_rec.attribute9 := l_itiv_rec.attribute9;
3228 END IF;
3229 IF (x_itiv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
3230 x_itiv_rec.attribute10 := l_itiv_rec.attribute10;
3231 END IF;
3232 IF (x_itiv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
3233 x_itiv_rec.attribute11 := l_itiv_rec.attribute11;
3234 END IF;
3235 IF (x_itiv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
3236 x_itiv_rec.attribute12 := l_itiv_rec.attribute12;
3237 END IF;
3238 IF (x_itiv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
3239 x_itiv_rec.attribute13 := l_itiv_rec.attribute13;
3240 END IF;
3241 IF (x_itiv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
3242 x_itiv_rec.attribute14 := l_itiv_rec.attribute14;
3243 END IF;
3244 IF (x_itiv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
3245 x_itiv_rec.attribute15 := l_itiv_rec.attribute15;
3246 END IF;
3247 IF (x_itiv_rec.created_by = OKC_API.G_MISS_NUM) THEN
3248 x_itiv_rec.created_by := l_itiv_rec.created_by;
3249 END IF;
3250 IF (x_itiv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
3251 x_itiv_rec.creation_date := l_itiv_rec.creation_date;
3252 END IF;
3253 IF (x_itiv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
3254 x_itiv_rec.last_updated_by := l_itiv_rec.last_updated_by;
3255 END IF;
3256 IF (x_itiv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
3257 x_itiv_rec.last_update_date := l_itiv_rec.last_update_date;
3258 END IF;
3259 IF (x_itiv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
3260 x_itiv_rec.last_update_login := l_itiv_rec.last_update_login;
3261 END IF;
3262 IF (x_itiv_rec.dnz_cle_id = OKC_API.G_MISS_NUM) THEN
3263 x_itiv_rec.dnz_cle_id := l_itiv_rec.dnz_cle_id;
3264 END IF;
3265 --Bug #2697681 schema change :11.5.9 Splitting asset by serial numbers
3266 IF (x_itiv_rec.instance_id = OKC_API.G_MISS_NUM) THEN
3267 x_itiv_rec.instance_id := l_itiv_rec.instance_id;
3268 END IF;
3269 IF (x_itiv_rec.selected_for_split_flag = OKC_API.G_MISS_CHAR) THEN
3270 x_itiv_rec.selected_for_split_flag := l_itiv_rec.selected_for_split_flag;
3271 END IF;
3272 IF (x_itiv_rec.asd_id = OKC_API.G_MISS_NUM) THEN
3273 x_itiv_rec.asd_id := l_itiv_rec.asd_id;
3274 END IF;
3275 RETURN(l_return_status);
3276 END populate_new_record;
3277 -----------------------------------------
3278 -- Set_Attributes for:OKL_TXL_ITM_INSTS_V --
3279 -----------------------------------------
3280 FUNCTION Set_Attributes (
3281 p_itiv_rec IN itiv_rec_type,
3282 x_itiv_rec OUT NOCOPY itiv_rec_type
3283 ) RETURN VARCHAR2 IS
3284 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3285 BEGIN
3286 x_itiv_rec := p_itiv_rec;
3287 x_itiv_rec.OBJECT_VERSION_NUMBER := NVL(x_itiv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3288 RETURN(l_return_status);
3289 END Set_Attributes;
3290 BEGIN
3291 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3292 G_PKG_NAME,
3293 p_init_msg_list,
3294 l_api_version,
3295 p_api_version,
3296 '_PVT',
3297 x_return_status);
3298 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3299 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3300 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3301 RAISE OKC_API.G_EXCEPTION_ERROR;
3302 END IF;
3303 --- Setting item attributes
3304 l_return_status := Set_Attributes(
3305 p_itiv_rec, -- IN
3306 l_itiv_rec); -- OUT
3307 --- If any errors happen abort API
3308 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3309 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3310 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3311 RAISE OKC_API.G_EXCEPTION_ERROR;
3312 END IF;
3313 l_return_status := populate_new_record(l_itiv_rec, l_def_itiv_rec);
3314 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3315 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3316 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3317 RAISE OKC_API.G_EXCEPTION_ERROR;
3318 END IF;
3319 l_def_itiv_rec := fill_who_columns(l_def_itiv_rec);
3320 --- Validate all non-missing attributes (Item Level Validation)
3321 l_return_status := Validate_Attributes(l_def_itiv_rec);
3322 --- If any errors happen abort API
3323 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3324 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3325 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3326 RAISE OKC_API.G_EXCEPTION_ERROR;
3327 END IF;
3328 l_return_status := Validate_Record(l_def_itiv_rec);
3329 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3330 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3331 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3332 RAISE OKC_API.G_EXCEPTION_ERROR;
3333 END IF;
3334
3335 --------------------------------------
3336 -- Move VIEW record to "Child" records
3337 --------------------------------------
3338 migrate(l_def_itiv_rec, l_iti_rec);
3339 update_row(
3340 p_init_msg_list,
3341 x_return_status,
3342 x_msg_count,
3343 x_msg_data,
3344 l_iti_rec,
3345 lx_iti_rec
3346 );
3347 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3348 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3349 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3350 RAISE OKC_API.G_EXCEPTION_ERROR;
3351 END IF;
3352 migrate(lx_iti_rec, l_def_itiv_rec);
3353 x_itiv_rec := l_def_itiv_rec;
3354 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3355 EXCEPTION
3356 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3357 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3358 (
3359 l_api_name,
3360 G_PKG_NAME,
3361 'OKC_API.G_RET_STS_ERROR',
3362 x_msg_count,
3363 x_msg_data,
3364 '_PVT'
3365 );
3366 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3367 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3368 (
3369 l_api_name,
3370 G_PKG_NAME,
3371 'OKC_API.G_RET_STS_UNEXP_ERROR',
3372 x_msg_count,
3373 x_msg_data,
3374 '_PVT'
3375 );
3376 WHEN OTHERS THEN
3377 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3378 (
3379 l_api_name,
3380 G_PKG_NAME,
3381 'OTHERS',
3382 x_msg_count,
3383 x_msg_data,
3384 '_PVT'
3385 );
3386 END update_row;
3387 ----------------------------------------
3388 -- PL/SQL TBL update_row for:ITIV_TBL --
3389 ----------------------------------------
3390 PROCEDURE update_row(
3391 p_api_version IN NUMBER,
3392 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3393 x_return_status OUT NOCOPY VARCHAR2,
3394 x_msg_count OUT NOCOPY NUMBER,
3395 x_msg_data OUT NOCOPY VARCHAR2,
3396 p_itiv_tbl IN itiv_tbl_type,
3397 x_itiv_tbl OUT NOCOPY itiv_tbl_type) IS
3398
3399 l_api_version CONSTANT NUMBER := 1;
3400 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3401 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3402 i NUMBER := 0;
3403 BEGIN
3404 OKC_API.init_msg_list(p_init_msg_list);
3405 -- Make sure PL/SQL table has records in it before passing
3406 IF (p_itiv_tbl.COUNT > 0) THEN
3407 i := p_itiv_tbl.FIRST;
3408 LOOP
3409 update_row (
3410 p_api_version => p_api_version,
3411 p_init_msg_list => OKC_API.G_FALSE,
3412 x_return_status => x_return_status,
3413 x_msg_count => x_msg_count,
3414 x_msg_data => x_msg_data,
3415 p_itiv_rec => p_itiv_tbl(i),
3416 x_itiv_rec => x_itiv_tbl(i));
3417 EXIT WHEN (i = p_itiv_tbl.LAST);
3418 i := p_itiv_tbl.NEXT(i);
3419 END LOOP;
3420 END IF;
3421 EXCEPTION
3422 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3423 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3424 (
3425 l_api_name,
3426 G_PKG_NAME,
3427 'OKC_API.G_RET_STS_ERROR',
3428 x_msg_count,
3429 x_msg_data,
3430 '_PVT'
3431 );
3432 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3433 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3434 (
3435 l_api_name,
3436 G_PKG_NAME,
3437 'OKC_API.G_RET_STS_UNEXP_ERROR',
3438 x_msg_count,
3439 x_msg_data,
3440 '_PVT'
3441 );
3442 WHEN OTHERS THEN
3443 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3444 (
3445 l_api_name,
3446 G_PKG_NAME,
3447 'OTHERS',
3448 x_msg_count,
3449 x_msg_data,
3450 '_PVT'
3451 );
3452 END update_row;
3453
3454 ---------------------------------------------------------------------------
3455 -- PROCEDURE delete_row
3456 ---------------------------------------------------------------------------
3457 -------------------------------------
3458 -- delete_row for:OKL_TXL_ITM_INSTS --
3459 -------------------------------------
3460 PROCEDURE delete_row(
3461 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3462 x_return_status OUT NOCOPY VARCHAR2,
3463 x_msg_count OUT NOCOPY NUMBER,
3464 x_msg_data OUT NOCOPY VARCHAR2,
3465 p_iti_rec IN iti_rec_type) IS
3466
3467 l_api_version CONSTANT NUMBER := 1;
3468 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3469 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3470 l_iti_rec iti_rec_type:= p_iti_rec;
3471 l_row_notfound BOOLEAN := TRUE;
3472 BEGIN
3473 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3474 p_init_msg_list,
3475 '_PVT',
3476 x_return_status);
3477 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3478 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3479 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3480 RAISE OKC_API.G_EXCEPTION_ERROR;
3481 END IF;
3482 DELETE FROM OKL_TXL_ITM_INSTS
3483 WHERE ID = l_iti_rec.id;
3484
3485 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3486 EXCEPTION
3487 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3488 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3489 (
3490 l_api_name,
3491 G_PKG_NAME,
3492 'OKC_API.G_RET_STS_ERROR',
3493 x_msg_count,
3494 x_msg_data,
3495 '_PVT'
3496 );
3497 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3498 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3499 (
3500 l_api_name,
3501 G_PKG_NAME,
3502 'OKC_API.G_RET_STS_UNEXP_ERROR',
3503 x_msg_count,
3504 x_msg_data,
3505 '_PVT'
3506 );
3507 WHEN OTHERS THEN
3508 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3509 (
3510 l_api_name,
3511 G_PKG_NAME,
3512 'OTHERS',
3513 x_msg_count,
3514 x_msg_data,
3515 '_PVT'
3516 );
3517 END delete_row;
3518 -------------------------------------
3519 -- delete_row for:OKL_TXL_ITM_INSTS_V --
3520 -------------------------------------
3521 PROCEDURE delete_row(
3522 p_api_version IN NUMBER,
3523 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3524 x_return_status OUT NOCOPY VARCHAR2,
3525 x_msg_count OUT NOCOPY NUMBER,
3526 x_msg_data OUT NOCOPY VARCHAR2,
3527 p_itiv_rec IN itiv_rec_type) IS
3528
3529 l_api_version CONSTANT NUMBER := 1;
3530 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3531 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3532 l_itiv_rec itiv_rec_type := p_itiv_rec;
3533 l_iti_rec iti_rec_type;
3534 BEGIN
3535 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3536 G_PKG_NAME,
3537 p_init_msg_list,
3538 l_api_version,
3539 p_api_version,
3540 '_PVT',
3541 x_return_status);
3542 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3543 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3544 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3545 RAISE OKC_API.G_EXCEPTION_ERROR;
3546 END IF;
3547 --------------------------------------
3548 -- Move VIEW record to "Child" records
3549 --------------------------------------
3550 migrate(l_itiv_rec, l_iti_rec);
3551 delete_row(
3552 p_init_msg_list,
3553 x_return_status,
3554 x_msg_count,
3555 x_msg_data,
3556 l_iti_rec
3557 );
3558 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3559 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3560 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3561 RAISE OKC_API.G_EXCEPTION_ERROR;
3562 END IF;
3563 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3564 EXCEPTION
3565 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3566 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3567 (
3568 l_api_name,
3569 G_PKG_NAME,
3570 'OKC_API.G_RET_STS_ERROR',
3571 x_msg_count,
3572 x_msg_data,
3573 '_PVT'
3574 );
3575 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3576 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3577 (
3578 l_api_name,
3579 G_PKG_NAME,
3580 'OKC_API.G_RET_STS_UNEXP_ERROR',
3581 x_msg_count,
3582 x_msg_data,
3583 '_PVT'
3584 );
3585 WHEN OTHERS THEN
3586 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3587 (
3588 l_api_name,
3589 G_PKG_NAME,
3590 'OTHERS',
3591 x_msg_count,
3592 x_msg_data,
3593 '_PVT'
3594 );
3595 END delete_row;
3596 ----------------------------------------
3597 -- PL/SQL TBL delete_row for:IALV_TBL --
3598 ----------------------------------------
3599 PROCEDURE delete_row(
3600 p_api_version IN NUMBER,
3601 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3602 x_return_status OUT NOCOPY VARCHAR2,
3603 x_msg_count OUT NOCOPY NUMBER,
3604 x_msg_data OUT NOCOPY VARCHAR2,
3605 p_itiv_tbl IN itiv_tbl_type) IS
3606
3607 l_api_version CONSTANT NUMBER := 1;
3608 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3609 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3610 i NUMBER := 0;
3611 BEGIN
3612 OKC_API.init_msg_list(p_init_msg_list);
3613 -- Make sure PL/SQL table has records in it before passing
3614 IF (p_itiv_tbl.COUNT > 0) THEN
3615 i := p_itiv_tbl.FIRST;
3616 LOOP
3617 delete_row (
3618 p_api_version => p_api_version,
3619 p_init_msg_list => OKC_API.G_FALSE,
3620 x_return_status => x_return_status,
3621 x_msg_count => x_msg_count,
3622 x_msg_data => x_msg_data,
3623 p_itiv_rec => p_itiv_tbl(i));
3624 EXIT WHEN (i = p_itiv_tbl.LAST);
3625 i := p_itiv_tbl.NEXT(i);
3626 END LOOP;
3627 END IF;
3628 EXCEPTION
3629 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3630 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3631 (
3632 l_api_name,
3633 G_PKG_NAME,
3634 'OKC_API.G_RET_STS_ERROR',
3635 x_msg_count,
3636 x_msg_data,
3637 '_PVT'
3638 );
3639 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3640 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3641 (
3642 l_api_name,
3643 G_PKG_NAME,
3644 'OKC_API.G_RET_STS_UNEXP_ERROR',
3645 x_msg_count,
3646 x_msg_data,
3647 '_PVT'
3648 );
3649 WHEN OTHERS THEN
3650 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3651 (
3652 l_api_name,
3653 G_PKG_NAME,
3654 'OTHERS',
3655 x_msg_count,
3656 x_msg_data,
3657 '_PVT'
3658 );
3659 END delete_row;
3660 END OKL_ITI_PVT;
[Home] [Help]