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