[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPPDTTEMPLATES_PVT
Source
1 PACKAGE BODY Okl_Setuppdttemplates_Pvt AS
2 /* $Header: OKLRSPTB.pls 115.17 2003/07/23 18:37:03 sgorantl noship $ */
3 TYPE GenericCurTyp IS REF CURSOR;
4 G_UNQS CONSTANT VARCHAR2(200) := 'OKL_NOT_UNIQUE';
5 ---------------------------------------------------------------------------
6 -- PROCEDURE get_version to calculate the new version number for the
7 -- product or product template to be created
8 ---------------------------------------------------------------------------
9 PROCEDURE get_version(p_name IN VARCHAR2,
10 p_cur_version IN VARCHAR2,
11 p_from_date IN DATE,
12 p_to_date IN DATE,
13 p_table IN VARCHAR2,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_new_version OUT NOCOPY VARCHAR2) IS
16
17 okl_all_laterversionsexist_csr GenericCurTyp;
18 l_sql_stmt VARCHAR2(250);
19 l_check VARCHAR2(1) := '?';
20 l_row_not_found BOOLEAN := FALSE;
21 BEGIN
22 null;
23 /*
24 IF p_cur_version = Okl_Api.G_MISS_CHAR THEN
25 x_new_version := G_INIT_VERSION;
26 ELSE
27 -- Check for future versions of the same formula
28 l_sql_stmt := 'SELECT ''1'' ' ||
29 'FROM ' || p_table ||
30 ' WHERE NAME = ' || '''' || p_name || '''' ||
31 ' AND NVL(TO_DATE, ' ||
32 '''' || Okl_Api.G_MISS_DATE || '''' || ') > ' ||
33 '''' || p_to_date || '''';
34 OPEN okl_all_laterversionsexist_csr
35 FOR l_sql_stmt;
36 FETCH okl_all_laterversionsexist_csr INTO l_check;
37 l_row_not_found := okl_all_laterversionsexist_csr%NOTFOUND;
38 CLOSE okl_all_laterversionsexist_csr;
39
40 IF l_row_not_found = TRUE THEN
41 x_new_version := TO_CHAR(TO_NUMBER(p_cur_version, G_VERSION_FORMAT)
42 + G_VERSION_MAJOR_INCREMENT, G_VERSION_FORMAT);
43 ELSE
44 x_new_version := TO_CHAR(TO_NUMBER(p_cur_version, G_VERSION_FORMAT)
45 + G_VERSION_MINOR_INCREMENT, G_VERSION_FORMAT);
46 END IF;
47 END IF;
48
49 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
50 */
51 EXCEPTION
52 WHEN OTHERS THEN
53 -- store SQL error message on message stack
54 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
55 p_msg_name => G_UNEXPECTED_ERROR,
56 p_token1 => G_SQLCODE_TOKEN,
57 p_token1_value => SQLCODE,
58 p_token2 => G_SQLERRM_TOKEN,
59 p_token2_value => SQLERRM);
60 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
61
62 IF (okl_all_laterversionsexist_csr%ISOPEN) THEN
63 CLOSE okl_all_laterversionsexist_csr;
64 END IF;
65
66 END get_version;
67
68 ---------------------------------------------------------------------------
69 -- PROCEDURE get_rec for: OKL_PDT_TEMPLATES_V
70 ---------------------------------------------------------------------------
71 PROCEDURE get_rec (
72 p_ptlv_rec IN ptlv_rec_type,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_no_data_found OUT NOCOPY BOOLEAN,
75 x_ptlv_rec OUT NOCOPY ptlv_rec_type
76 ) IS
77 CURSOR okl_ptlv_pk_csr (p_id IN NUMBER) IS
78 SELECT
79 ID,
80 OBJECT_VERSION_NUMBER,
81 NAME,
82 VERSION,
83 NVL(DESCRIPTION,Okl_Api.G_MISS_CHAR) DESCRIPTION,
84 FROM_DATE,
85 NVL(TO_DATE,Okl_Api.G_MISS_DATE) TO_DATE,
86 CREATED_BY,
87 CREATION_DATE,
88 LAST_UPDATED_BY,
89 LAST_UPDATE_DATE,
90 NVL(LAST_UPDATE_LOGIN, Okl_Api.G_MISS_NUM) LAST_UPDATE_LOGIN
91 FROM Okl_Pdt_Templates_V
92 WHERE okl_pdt_templates_v.id = p_id;
93 l_okl_ptlv_pk okl_ptlv_pk_csr%ROWTYPE;
94 l_ptlv_rec ptlv_rec_type;
95 BEGIN
96 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
97 x_no_data_found := TRUE;
98
99 -- Get current database values
100 OPEN okl_ptlv_pk_csr (p_ptlv_rec.id);
101 FETCH okl_ptlv_pk_csr INTO
102 l_ptlv_rec.ID,
103 l_ptlv_rec.OBJECT_VERSION_NUMBER,
104 l_ptlv_rec.NAME,
105 l_ptlv_rec.VERSION,
106 l_ptlv_rec.DESCRIPTION,
107 l_ptlv_rec.FROM_DATE,
108 l_ptlv_rec.TO_DATE,
109 l_ptlv_rec.CREATED_BY,
110 l_ptlv_rec.CREATION_DATE,
111 l_ptlv_rec.LAST_UPDATED_BY,
112 l_ptlv_rec.LAST_UPDATE_DATE,
113 l_ptlv_rec.LAST_UPDATE_LOGIN;
114 x_no_data_found := okl_ptlv_pk_csr%NOTFOUND;
115 CLOSE okl_ptlv_pk_csr;
116 x_ptlv_rec := l_ptlv_rec;
117 EXCEPTION
118 WHEN OTHERS THEN
119 -- store SQL error message on message stack
120 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
121 p_msg_name => G_UNEXPECTED_ERROR,
122 p_token1 => G_SQLCODE_TOKEN,
123 p_token1_value => SQLCODE,
124 p_token2 => G_SQLERRM_TOKEN,
125 p_token2_value => SQLERRM);
126 -- notify UNEXPECTED error for calling API.
127 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
128
129 IF (okl_ptlv_pk_csr%ISOPEN) THEN
130 CLOSE okl_ptlv_pk_csr;
131 END IF;
132
133 END get_rec;
134
135 ---------------------------------------------------------------------------
136 -- PROCEDURE check_overlaps for either product or product template
137 -- To avoid overlapping of dates with other versions of the same product or
138 -- product template
139 ---------------------------------------------------------------------------
140 PROCEDURE check_overlaps (p_id IN NUMBER,
141 p_name IN VARCHAR2,
142 p_from_date IN DATE,
143 p_to_date IN DATE,
144 p_table IN VARCHAR2,
145 x_return_status OUT NOCOPY VARCHAR2,
146 x_valid OUT NOCOPY BOOLEAN
147 ) IS
148
149 okl_all_overlaps_csr GenericCurTyp;
150 l_sql_stmt VARCHAR2(500);
151 l_check VARCHAR2(1) := '?';
152 l_row_not_found BOOLEAN := FALSE;
153 BEGIN
154 /*
155 x_valid := TRUE;
156 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
157 -- Check for product template overlaps
158 l_sql_stmt := 'SELECT ''1'' ' ||
159 'FROM ' || p_table ||
160 ' WHERE NAME = ' || '''' || p_name || '''' ||
161 ' AND ID <> ' || p_id ||
162 ' AND ( ' || '''' || p_from_date || '''' ||
163 ' BETWEEN FROM_DATE AND ' ||
164 ' NVL(TO_DATE, ' || '''' || Okl_Api.G_MISS_DATE || '''' || ') OR ' ||
165 '''' || p_to_date || '''' ||
166 ' BETWEEN FROM_DATE AND ' ||
167 ' NVL(TO_DATE, ' || '''' || Okl_Api.G_MISS_DATE || '''' || ')) ' ||
168 'UNION ALL ' ||
169 'SELECT ''2'' ' ||
170 'FROM ' || p_table ||
171 ' WHERE NAME = ' || '''' || p_name || '''' ||
172 ' AND ID <> ' || p_id ||
173 ' AND ' || '''' || p_from_date || '''' ||
174 ' <= FROM_DATE ' ||
175 'AND ' || '''' || p_to_date || '''' ||
176 ' >= NVL(TO_DATE, ' || '''' || Okl_Api.G_MISS_DATE || '''' || ') ';
177 OPEN okl_all_overlaps_csr
178 FOR l_sql_stmt;
179 FETCH okl_all_overlaps_csr INTO l_check;
180 l_row_not_found := okl_all_overlaps_csr%NOTFOUND;
181 CLOSE okl_all_overlaps_csr;
182 IF l_row_not_found = FALSE THEN
183 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
184 p_msg_name => G_VERSION_OVERLAPS,
185 p_token1 => G_TABLE_TOKEN,
186 p_token1_value => p_table,
187 p_token2 => G_COL_NAME_TOKEN,
188 p_token2_value => 'NAME');
189 x_valid := FALSE;
190 x_return_status := Okl_Api.G_RET_STS_ERROR;
191 END IF;
192 */
193 null;
194 EXCEPTION
195 WHEN OTHERS THEN
196 -- store SQL error message on message stack
197 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
198 p_msg_name => G_UNEXPECTED_ERROR,
199 p_token1 => G_SQLCODE_TOKEN,
200 p_token1_value => SQLCODE,
201 p_token2 => G_SQLERRM_TOKEN,
202 p_token2_value => SQLERRM);
203 x_valid := FALSE;
204 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
205
206 IF (okl_all_overlaps_csr%ISOPEN) THEN
207 CLOSE okl_all_overlaps_csr;
208 END IF;
209
210
211 END check_overlaps;
212
213 ---------------------------------------------------------------------------
214 -- PROCEDURE check_constraints for: OKL_PDT_TEMPLATES_V
215 -- To verify whether the dates are valid in the following entities
216 -- 1. Product
217 -- 2. Contract
218 -- 3. Product Template Quality
219 -- 4. Product Template Quality Value
220 -- 5. Product Quality
221 ---------------------------------------------------------------------------
222 PROCEDURE Check_Constraints (
223 p_upd_ptlv_rec IN ptlv_rec_type,
224 p_ptlv_rec IN ptlv_rec_type,
225 x_return_status OUT NOCOPY VARCHAR2,
226 x_valid OUT NOCOPY BOOLEAN
227 ) IS
228 CURSOR okl_products_csr (p_ptl_id IN Okl_Pdt_Templates_V.ID%TYPE,
229 p_from_date IN Okl_Pdt_Templates_V.FROM_DATE%TYPE,
230 p_to_date IN Okl_Pdt_Templates_V.TO_DATE%TYPE
231 ) IS
232
233 SELECT '1'
234 FROM Okl_Products_V pdt
235 WHERE pdt.PTL_ID = p_ptl_id
236 AND (pdt.FROM_DATE < p_from_date OR
237 NVL(pdt.TO_DATE, pdt.FROM_DATE) > p_to_date);
238
239
240 CURSOR okl_ptl_constraints_csr (p_ptl_id IN Okl_Pdt_Templates_V.ID%TYPE,
241 p_from_date IN Okl_Pdt_Templates_V.FROM_DATE%TYPE,
242 p_to_date IN Okl_Pdt_Templates_V.TO_DATE%TYPE
243 ) IS
244 SELECT '1'
245 FROM Okl_Ptq_Values_V ptv,
246 Okl_Ptl_Ptq_Vals_V pmv
247 WHERE pmv.PTL_ID = p_ptl_id
248 AND ptv.ID = pmv.PTV_ID
249 AND ((ptv.FROM_DATE > p_from_date OR
250 p_from_date > NVL(ptv.TO_DATE,p_from_date)) OR
251 NVL(ptv.TO_DATE, p_to_date) < p_to_date)
252 UNION ALL
253 SELECT '2'
254 FROM Okl_Pdt_Pqys_V pdq,
255 Okl_Pdt_Qualitys_V pqy
256 WHERE pdq.PTL_ID = p_ptl_id
257 AND pqy.ID = pdq.PQY_ID
258 AND ((pqy.FROM_DATE > p_from_date OR
259 p_from_date > NVL(pqy.TO_DATE,p_from_date)) OR
260 NVL(pqy.TO_DATE, p_to_date) < p_to_date);
261
262 l_token_1 VARCHAR2(1999);
263 l_token_2 VARCHAR2(1999);
264 l_check VARCHAR2(1) := '?';
265 l_token_3 VARCHAR2(1999);
266 l_token_4 VARCHAR2(1999);
267 l_token_5 VARCHAR2(1999);
268
269 l_row_not_found BOOLEAN := FALSE;
270 l_to_date okl_pdt_templates_v.TO_DATE%TYPE;
271
272 BEGIN
273 x_valid := TRUE;
274 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
275
276 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_TEMPLATE_SERCH',
277 p_attribute_code => 'OKL_PRODUCT_TEMPLATES');
278
279 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_SERCH',
280 p_attribute_code => 'OKL_PRODUCTS');
281
282 l_token_3 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_TMPVALS_CRUPD',
283 p_attribute_code => 'OKL_TEMPLATE_QUALITY_VALUES');
284
285 l_token_4 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRDQLTY_CRUPD',
286 p_attribute_code => 'OKL_PRODUCT_QUALITIES');
287
288 l_token_5 := l_token_3 ||','||l_token_4;
289
290 -- Check for product dates
291
292 IF p_ptlv_rec.TO_DATE = Okl_Api.G_MISS_DATE THEN
293 l_to_date := NULL;
294 ELSE
295 l_to_date := p_ptlv_rec.TO_DATE;
296 END IF;
297
298 IF p_ptlv_rec.id <> Okl_Api.G_MISS_NUM THEN
299
300 OPEN okl_products_csr (p_upd_ptlv_rec.id,
301 p_ptlv_rec.from_date,
302 l_to_date
303 );
304 FETCH okl_products_csr INTO l_check;
305 l_row_not_found := okl_products_csr%NOTFOUND;
306 CLOSE okl_products_csr;
307
308
309 IF l_row_not_found = FALSE THEN
310 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
311 p_msg_name => G_DATES_MISMATCH,
312 p_token1 => G_PARENT_TABLE_TOKEN,
313 p_token1_value => l_token_1,
314 p_token2 => G_CHILD_TABLE_TOKEN,
315 p_token2_value => l_token_2);
316 x_valid := FALSE;
317 x_return_status := Okl_Api.G_RET_STS_ERROR;
318 END IF;
319 END IF;
320
321 -- Check for constraints dates
322 OPEN okl_ptl_constraints_csr (p_upd_ptlv_rec.id,
323 p_ptlv_rec.from_date,
324 l_to_date);
325 FETCH okl_ptl_constraints_csr INTO l_check;
326 l_row_not_found := okl_ptl_constraints_csr%NOTFOUND;
327 CLOSE okl_ptl_constraints_csr;
328
329 IF l_row_not_found = FALSE THEN
330 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
331 p_msg_name => G_DATES_MISMATCH,
332 p_token1 => G_PARENT_TABLE_TOKEN,
333 p_token1_value => l_token_5,
334 p_token2 => G_CHILD_TABLE_TOKEN,
335 p_token2_value => l_token_1);
336 x_valid := FALSE;
337 x_return_status := Okl_Api.G_RET_STS_ERROR;
338 END IF;
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 -- store SQL error message on message stack
343 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
344 p_msg_name => G_UNEXPECTED_ERROR,
345 p_token1 => G_SQLCODE_TOKEN,
346 p_token1_value => SQLCODE,
347 p_token2 => G_SQLERRM_TOKEN,
348 p_token2_value => SQLERRM);
349 x_valid := FALSE;
350 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
351
352 IF (okl_products_csr%ISOPEN) THEN
353 CLOSE okl_products_csr;
354 END IF;
355
356 IF (okl_ptl_constraints_csr%ISOPEN) THEN
357 CLOSE okl_ptl_constraints_csr;
358 END IF;
359
360 END Check_Constraints;
361
362
363 ---------------------------------------------------------------------------
364 -- PROCEDURE reorganize_inputs
365 -- This procedure is to reset the attributes in the input structure based
366 -- on the data from database
367 ---------------------------------------------------------------------------
368 PROCEDURE reorganize_inputs (
369 p_upd_ptlv_rec IN OUT NOCOPY ptlv_rec_type,
370 p_db_ptlv_rec IN ptlv_rec_type
371 ) IS
372 l_upd_ptlv_rec ptlv_rec_type;
373 l_db_ptlv_rec ptlv_rec_type;
374 BEGIN
375 /* create a temporary record with all relevant details from db and upd records */
376 l_upd_ptlv_rec := p_upd_ptlv_rec;
377 l_db_ptlv_rec := p_db_ptlv_rec;
378
379 IF l_upd_ptlv_rec.description = l_db_ptlv_rec.description THEN
380 l_upd_ptlv_rec.description := Okl_Api.G_MISS_CHAR;
381 END IF;
382
383 IF to_date(to_char(l_upd_ptlv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_ptlv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
384 l_upd_ptlv_rec.from_date := Okl_Api.G_MISS_DATE;
385 END IF;
386
387 IF to_date(to_char(l_upd_ptlv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_ptlv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
388 l_upd_ptlv_rec.TO_DATE := Okl_Api.G_MISS_DATE;
389 END IF;
390
391 p_upd_ptlv_rec := l_upd_ptlv_rec;
392
393 END reorganize_inputs;
394
395 ---------------------------------------------------------------------------
396 -- FUNCTION defaults_to_actuals
397 -- This function creates an output record with changed information from the
398 -- input structure and unchanged details from the database
399 ---------------------------------------------------------------------------
400 FUNCTION defaults_to_actuals (
401 p_upd_ptlv_rec IN ptlv_rec_type,
402 p_db_ptlv_rec IN ptlv_rec_type
403 ) RETURN ptlv_rec_type IS
404 l_ptlv_rec ptlv_rec_type;
405 BEGIN
406 /* create a temporary record with all relevant details from db and upd records */
407 l_ptlv_rec := p_db_ptlv_rec;
408
409 IF p_upd_ptlv_rec.description <> Okl_Api.G_MISS_CHAR THEN
410 l_ptlv_rec.description := p_upd_ptlv_rec.description;
411 END IF;
412
413 IF p_upd_ptlv_rec.from_date <> Okl_Api.G_MISS_DATE THEN
414 l_ptlv_rec.from_date := p_upd_ptlv_rec.from_date;
415 END IF;
416
417 IF p_upd_ptlv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
418 l_ptlv_rec.TO_DATE := p_upd_ptlv_rec.TO_DATE;
419 END IF;
420
421 RETURN l_ptlv_rec;
422 END defaults_to_actuals;
423
424 ---------------------------------------------------------------------------
425 -- PROCEDURE check_updates
426 -- To verify whether the requested changes from the screen are valid or not
427 ---------------------------------------------------------------------------
428 PROCEDURE check_updates (
429 p_upd_ptlv_rec IN ptlv_rec_type,
430 p_db_ptlv_rec IN ptlv_rec_type,
431 p_ptlv_rec IN ptlv_rec_type,
432 x_return_status OUT NOCOPY VARCHAR2,
433 x_msg_data OUT NOCOPY VARCHAR2
434 ) IS
435 l_upd_ptlv_rec ptlv_rec_type;
436 l_ptlv_rec ptlv_rec_type;
437 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
438 l_valid BOOLEAN;
439 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
440 BEGIN
441 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
442 l_ptlv_rec := p_ptlv_rec;
443 l_upd_ptlv_rec := p_upd_ptlv_rec;
444
445 /* check for start date greater than sysdate */
446 /*IF to_date(to_char(p_upd_ptlv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(OKL_API.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
447 to_date(to_char(p_upd_ptlv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
448 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
449 p_msg_name => G_START_DATE);
450 x_return_status := OKL_API.G_RET_STS_ERROR;
451 RAISE G_EXCEPTION_HALT_PROCESSING;
452 END IF; */
453
454
455 /* check for the records with from and to dates less than sysdate */
456 /* IF to_date(to_char(p_upd_ptlv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
457 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
458 p_msg_name => G_PAST_RECORDS);
459 x_return_status := OKL_API.G_RET_STS_ERROR;
460 RAISE G_EXCEPTION_HALT_PROCESSING;
461 END IF;
462 */
463
464 /* if the start date is in the past, the start date cannot be
465 modified */
466 /* IF to_date(to_char(p_upd_ptlv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(OKL_API.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
467 to_date(to_char(p_db_ptlv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <= l_sysdate THEN
468 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
469 p_msg_name => 'OKL_NOT_ALLOWED',
470 p_token1 => G_COL_NAME_TOKEN,
471 p_token1_value => 'START_DATE');
472 x_return_status := OKL_API.G_RET_STS_ERROR;
473 RAISE G_EXCEPTION_HALT_PROCESSING;
474 END IF;
475
476 */ IF p_upd_ptlv_rec.from_date <> Okl_Api.G_MISS_DATE OR
477 p_upd_ptlv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
478
479 /* call check_overlaps */
480 /*check_overlaps(p_id => l_upd_ptlv_rec.id,
481 p_name => l_ptlv_rec.name,
482 p_from_date => l_ptlv_rec.from_date,
483 p_to_date => l_ptlv_rec.TO_DATE,
484 p_table => 'Okl_Pdt_Templates_V',
485 x_return_status => l_return_status,
486 x_valid => l_valid);
487 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
488 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
489 RAISE G_EXCEPTION_HALT_PROCESSING;
490 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) OR
491 (l_return_status = OKL_API.G_RET_STS_SUCCESS AND
492 l_valid <> TRUE) THEN
493 x_return_status := OKL_API.G_RET_STS_ERROR;
494 RAISE G_EXCEPTION_HALT_PROCESSING;
495 END IF;*/
496
497 /* call check_constraints */
498 Check_Constraints(p_upd_ptlv_rec => l_upd_ptlv_rec,
499 p_ptlv_rec => l_ptlv_rec,
500 x_return_status => l_return_status,
501 x_valid => l_valid);
502 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
503 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
504 RAISE G_EXCEPTION_HALT_PROCESSING;
505 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
506 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
507 l_valid <> TRUE) THEN
508 x_return_status := Okl_Api.G_RET_STS_ERROR;
509 RAISE G_EXCEPTION_HALT_PROCESSING;
510 END IF;
511
512 END IF;
513
514 EXCEPTION
515 WHEN G_EXCEPTION_HALT_PROCESSING THEN
516 -- no processing necessary; validation can continue
517 -- with the next column
518 NULL;
519
520 WHEN OTHERS THEN
521 -- store SQL error message on message stack for caller
522 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
523 p_msg_name => G_UNEXPECTED_ERROR,
524 p_token1 => G_SQLCODE_TOKEN,
525 p_token1_value => SQLCODE,
526 p_token2 => G_SQLERRM_TOKEN,
527 p_token2_value => SQLERRM );
528 x_msg_data := 'Unexpected DATABASE Error';
529 -- notify caller of an UNEXPECTED error
530 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
531
532 END check_updates;
533
534 ---------------------------------------------------------------------------
535 -- PROCEDURE determine_action for: OKL_PDT_TEMPLATES_V
536 -- This function helps in determining the various checks to be performed
537 -- for the new/updated record and also helps in determining whether a new
538 -- version is required or not
539 ---------------------------------------------------------------------------
540 FUNCTION determine_action (
541 p_upd_ptlv_rec IN ptlv_rec_type,
542 p_db_ptlv_rec IN ptlv_rec_type,
543 p_date IN DATE
544 ) RETURN VARCHAR2 IS
545 l_action VARCHAR2(1);
546 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
547 BEGIN
548
549 /* Scenario 1: Only description changes */
550 IF p_upd_ptlv_rec.from_date = Okl_Api.G_MISS_DATE AND
551 p_upd_ptlv_rec.TO_DATE = Okl_Api.G_MISS_DATE THEN
552 l_action := '1';
553 /* Scenario 2: only changing description and end date for all records
554 or modified start date is less than existing start date */
555 /*ELSIF (p_upd_ptlv_rec.from_date = OKL_API.G_MISS_DATE AND
556 p_upd_ptlv_rec.TO_DATE <> OKL_API.G_MISS_DATE) OR
557 (p_upd_ptlv_rec.from_date <> OKL_API.G_MISS_DATE AND
558 p_db_ptlv_rec.from_date > p_date AND
559 p_upd_ptlv_rec.from_date < p_db_ptlv_rec.from_date) THEN*/
560 ELSE
561 l_action := '2';
562 END IF;
563 RETURN(l_action);
564 END determine_action;
565
566 ---------------------------------------------------------------------------
567 -- PROCEDURE get_ptl_ptq_vals for: OKL_PDT_TEMPLATES_V
568 -- To fetch the template qualities/values that are attached to the existing
569 -- version of the product template
570 ---------------------------------------------------------------------------
571 PROCEDURE get_ptl_ptq_vals (p_upd_ptlv_rec IN ptlv_rec_type,
572 p_ptlv_rec IN ptlv_rec_type,
573 p_flag IN VARCHAR2,
574 x_return_status OUT NOCOPY VARCHAR2,
575 x_count OUT NOCOPY NUMBER,
576 x_pmvv_tbl OUT NOCOPY pmvv_tbl_type
577 ) IS
578 CURSOR okl_pmvv_fk_csr (p_ptl_id IN Okl_Ptl_Ptq_Vals_V.ptl_id%TYPE) IS
579 SELECT ID,
580 PTQ_ID,
581 PTV_ID,
582 FROM_DATE,
583 TO_DATE
584 FROM Okl_Ptl_Ptq_Vals_V pmv
585 WHERE pmv.PTL_ID = p_ptl_id;
586
587 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
588 l_count NUMBER := 0;
589 l_pmvv_tbl pmvv_tbl_type;
590
591 BEGIN
592 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
593
594 -- Get current database values
595 FOR okl_pmv_rec IN okl_pmvv_fk_csr(p_upd_ptlv_rec.id)
596 LOOP
597 IF p_flag = G_UPDATE THEN
598 l_pmvv_tbl(l_count).ID := okl_pmv_rec.ID;
599 END IF;
600 l_pmvv_tbl(l_count).PTL_ID := p_ptlv_rec.ID;
601 l_pmvv_tbl(l_count).PTQ_ID := okl_pmv_rec.PTQ_ID;
602 l_pmvv_tbl(l_count).PTV_ID := okl_pmv_rec.PTV_ID;
603 IF p_upd_ptlv_rec.from_date <> Okl_Api.G_MISS_DATE THEN
604 l_pmvv_tbl(l_count).from_date := p_upd_ptlv_rec.from_date;
605 ELSE
606 l_pmvv_tbl(l_count).from_date := okl_pmv_rec.from_date;
607 END IF;
608 IF p_upd_ptlv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
609 l_pmvv_tbl(l_count).TO_DATE := p_upd_ptlv_rec.TO_DATE;
610 ELSE
611 l_pmvv_tbl(l_count).TO_DATE := okl_pmv_rec.TO_DATE;
612 END IF;
613 l_count := l_count + 1;
614 END LOOP;
615
616 x_count := l_count;
617 x_pmvv_tbl := l_pmvv_tbl;
618
619 EXCEPTION
620 WHEN OTHERS THEN
621 -- store SQL error message on message stack
622 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
623 p_msg_name => G_UNEXPECTED_ERROR,
624 p_token1 => G_SQLCODE_TOKEN,
625 p_token1_value => SQLCODE,
626 p_token2 => G_SQLERRM_TOKEN,
627 p_token2_value => SQLERRM );
628 -- notify UNEXPECTED error for calling API.
629 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
630
631 IF (okl_pmvv_fk_csr%ISOPEN) THEN
632 CLOSE okl_pmvv_fk_csr;
633 END IF;
634
635 END get_ptl_ptq_vals;
636
637 ---------------------------------------------------------------------------
638 -- PROCEDURE get_pdt_pqys for: OKL_PDT_TEMPLATES_V
639 -- To fetch the product qualities that are attached to the existing
640 -- version of the product template
641 ---------------------------------------------------------------------------
642 PROCEDURE get_pdt_pqys (p_upd_ptlv_rec IN ptlv_rec_type,
643 p_ptlv_rec IN ptlv_rec_type,
644 p_flag IN VARCHAR2,
645 x_return_status OUT NOCOPY VARCHAR2,
646 x_count OUT NOCOPY NUMBER,
647 x_pdqv_tbl OUT NOCOPY pdqv_tbl_type
648 ) IS
649 CURSOR okl_pdqv_fk_csr (p_ptl_id IN Okl_Pdt_Pqys_V.ptl_id%TYPE) IS
650 SELECT ID,
651 PQY_ID,
652 FROM_DATE,
653 TO_DATE
654 FROM Okl_Pdt_Pqys_V pdq
655 WHERE pdq.PTL_ID = p_ptl_id;
656
657 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
658 l_count NUMBER := 0;
659 l_pdqv_tbl pdqv_tbl_type;
660
661 BEGIN
662 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
663
664 -- Get current database values
665 FOR okl_pdq_rec IN okl_pdqv_fk_csr(p_upd_ptlv_rec.id)
666 LOOP
667 IF p_flag = G_UPDATE THEN
668 l_pdqv_tbl(l_count).ID := okl_pdq_rec.ID;
669 END IF;
670 l_pdqv_tbl(l_count).PTL_ID := p_ptlv_rec.ID;
671 l_pdqv_tbl(l_count).PQY_ID := okl_pdq_rec.PQY_ID;
672 IF p_upd_ptlv_rec.from_date <> Okl_Api.G_MISS_DATE THEN
673 l_pdqv_tbl(l_count).from_date := p_upd_ptlv_rec.from_date;
674 ELSE
675 l_pdqv_tbl(l_count).from_date := okl_pdq_rec.from_date;
676 END IF;
677 IF p_upd_ptlv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
678 l_pdqv_tbl(l_count).TO_DATE := p_upd_ptlv_rec.TO_DATE;
679 ELSE
680 l_pdqv_tbl(l_count).TO_DATE := okl_pdq_rec.TO_DATE;
681 END IF;
682 l_count := l_count + 1;
683 END LOOP;
684
685 x_count := l_count;
686 x_pdqv_tbl := l_pdqv_tbl;
687
688 EXCEPTION
689 WHEN OTHERS THEN
690 -- store SQL error message on message stack
691 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
692 p_msg_name => G_UNEXPECTED_ERROR,
693 p_token1 => G_SQLCODE_TOKEN,
694 p_token1_value => SQLCODE,
695 p_token2 => G_SQLERRM_TOKEN,
696 p_token2_value => SQLERRM );
697 -- notify UNEXPECTED error for calling API.
698 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
699
700 IF (okl_pdqv_fk_csr%ISOPEN) THEN
701 CLOSE okl_pdqv_fk_csr;
702 END IF;
703
704 END get_pdt_pqys;
705
706 ---------------------------------------------------------------------------
707 -- PROCEDURE copy_update_constraints for: OKL_PDT_TEMPLATES_V
708 -- To copy constraints data from one version to the other
709 ---------------------------------------------------------------------------
710 PROCEDURE copy_update_constraints (p_api_version IN NUMBER,
711 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
712 p_upd_ptlv_rec IN ptlv_rec_type,
713 p_db_ptlv_rec IN ptlv_rec_type,
714 p_ptlv_rec IN ptlv_rec_type,
715 p_flag IN VARCHAR2,
716 x_return_status OUT NOCOPY VARCHAR2,
717 x_msg_count OUT NOCOPY NUMBER,
718 x_msg_data OUT NOCOPY VARCHAR2
719 ) IS
720 l_upd_ptlv_rec ptlv_rec_type; /* input copy */
721 l_ptlv_rec ptlv_rec_type; /* latest with the retained changes */
722 l_db_ptlv_rec ptlv_rec_type; /* for db copy */
723 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
724 l_pmv_count NUMBER := 0;
725 l_pdq_count NUMBER := 0;
726 l_pmvv_tbl pmvv_tbl_type;
727 l_out_pmvv_tbl pmvv_tbl_type;
728 l_pdqv_tbl pdqv_tbl_type;
729 l_out_pdqv_tbl pdqv_tbl_type;
730
731 BEGIN
732 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
733 l_upd_ptlv_rec := p_ptlv_rec;
734 l_ptlv_rec := p_ptlv_rec;
735 l_db_ptlv_rec := p_db_ptlv_rec;
736
737 /* product template qualities/values carryover */
738 get_ptl_ptq_vals(p_upd_ptlv_rec => l_upd_ptlv_rec,
739 p_ptlv_rec => l_ptlv_rec,
740 p_flag => p_flag,
741 x_return_status => l_return_status,
742 x_count => l_pmv_count,
743 x_pmvv_tbl => l_pmvv_tbl);
744 IF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
745 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
746 RAISE G_EXCEPTION_HALT_PROCESSING;
747 END IF;
748
749 IF l_pmv_count > 0 THEN
750 IF p_flag = G_UPDATE THEN
751 Okl_Ptq_Values_Pub.update_ptq_values(p_api_version => p_api_version,
752 p_init_msg_list => p_init_msg_list,
753 x_return_status => l_return_status,
754 x_msg_count => x_msg_count,
755 x_msg_data => x_msg_data,
756 p_pmvv_tbl => l_pmvv_tbl,
757 x_pmvv_tbl => l_out_pmvv_tbl);
758 ELSE
759 Okl_Ptq_Values_Pub.insert_ptq_values(p_api_version => p_api_version,
760 p_init_msg_list => p_init_msg_list,
761 x_return_status => l_return_status,
762 x_msg_count => x_msg_count,
763 x_msg_data => x_msg_data,
764 p_pmvv_tbl => l_pmvv_tbl,
765 x_pmvv_tbl => l_out_pmvv_tbl);
766 END IF;
767 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
768 x_return_status := Okl_Api.G_RET_STS_ERROR;
769 RAISE G_EXCEPTION_HALT_PROCESSING;
770 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
771 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
772 RAISE G_EXCEPTION_HALT_PROCESSING;
773 END IF;
774 END IF;
775
776 /* product defining qualities carryover */
777 get_pdt_pqys(p_upd_ptlv_rec => l_upd_ptlv_rec,
778 p_ptlv_rec => l_ptlv_rec,
779 p_flag => p_flag,
780 x_return_status => l_return_status,
781 x_count => l_pdq_count,
782 x_pdqv_tbl => l_pdqv_tbl);
783 IF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
784 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
785 RAISE G_EXCEPTION_HALT_PROCESSING;
786 END IF;
787
788 IF l_pdq_count > 0 THEN
789 IF p_flag = G_UPDATE THEN
790 Okl_Pdt_Pqys_Pub.update_pdt_pqys(p_api_version => p_api_version,
791 p_init_msg_list => p_init_msg_list,
792 x_return_status => l_return_status,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data,
795 p_pdqv_tbl => l_pdqv_tbl,
796 x_pdqv_tbl => l_out_pdqv_tbl);
797 ELSE
798 Okl_Pdt_Pqys_Pub.insert_pdt_pqys(p_api_version => p_api_version,
799 p_init_msg_list => p_init_msg_list,
800 x_return_status => l_return_status,
801 x_msg_count => x_msg_count,
802 x_msg_data => x_msg_data,
803 p_pdqv_tbl => l_pdqv_tbl,
804 x_pdqv_tbl => l_out_pdqv_tbl);
805 END IF;
806 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
807 x_return_status := Okl_Api.G_RET_STS_ERROR;
808 RAISE G_EXCEPTION_HALT_PROCESSING;
809 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
810 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
811 RAISE G_EXCEPTION_HALT_PROCESSING;
812 END IF;
813
814 END IF;
815
816
817 EXCEPTION
818 WHEN G_EXCEPTION_HALT_PROCESSING THEN
819 -- no processing necessary; validation can continue
820 -- with the next column
821 NULL;
822
823 WHEN OTHERS THEN
824 -- store SQL error message on message stack
825 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
826 p_msg_name => G_UNEXPECTED_ERROR,
827 p_token1 => G_SQLCODE_TOKEN,
828 p_token1_value => SQLCODE,
829 p_token2 => G_SQLERRM_TOKEN,
830 p_token2_value => SQLERRM );
831 -- notify UNEXPECTED error for calling API.
832 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
833
834 END copy_update_constraints;
835
836 ---------------------------------------------------------------------------
837 -- PROCEDURE insert_pdttemplates for: OKL_PDT_TEMPLATES_V
838 ---------------------------------------------------------------------------
839 PROCEDURE insert_pdttemplates(p_api_version IN NUMBER,
840 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
841 x_return_status OUT NOCOPY VARCHAR2,
842 x_msg_count OUT NOCOPY NUMBER,
843 x_msg_data OUT NOCOPY VARCHAR2,
844 p_ptlv_rec IN ptlv_rec_type,
845 x_ptlv_rec OUT NOCOPY ptlv_rec_type
846 ) IS
847
848 CURSOR c1(p_name okl_pdt_templates_v.name%TYPE,
849 p_version okl_pdt_templates_v.version%TYPE) IS
850 SELECT '1'
851 FROM okl_pdt_templates_v
852 WHERE name = p_name;
853
854 l_name okl_pdt_templates_v.name%TYPE;
855 l_unq_tbl Okc_Util.unq_tbl_type;
856 l_token_1 VARCHAR2(1999);
857 l_pdt_status VARCHAR2(1);
858 l_row_found BOOLEAN := FALSE;
859 l_api_version CONSTANT NUMBER := 1;
860 l_api_name CONSTANT VARCHAR2(30) := 'insert_pdttemplates';
861 l_valid BOOLEAN := TRUE;
862 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
863 l_ptlv_rec ptlv_rec_type;
864 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
865 BEGIN
866 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
867 l_ptlv_rec := p_ptlv_rec;
868
869 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
870 p_pkg_name => G_PKG_NAME,
871 p_init_msg_list => p_init_msg_list,
872 l_api_version => l_api_version,
873 p_api_version => p_api_version,
874 p_api_type => '_PVT',
875 x_return_status => l_return_status);
876 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
877 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
878 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
879 RAISE Okl_Api.G_EXCEPTION_ERROR;
880 END IF;
881
882 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_TEMPLATE_SERCH',
883 p_attribute_code => 'OKL_PRODUCT_TEMPLATES');
884
885 l_name := Okl_Accounting_Util.okl_upper(p_ptlv_rec.name);
886 OPEN c1(l_name,
887 p_ptlv_rec.version);
888 FETCH c1 INTO l_pdt_status;
889 l_row_found := c1%FOUND;
890 CLOSE c1;
891
892 IF l_row_found THEN
893 Okl_Api.set_message('OKL',G_UNQS, G_TABLE_TOKEN, l_token_1);
894 RAISE Okl_Api.G_EXCEPTION_ERROR;
895 END IF;
896
897 /* check for the records with from and to dates less than sysdate */
898 /*IF to_date(to_char(l_ptlv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate OR
899 to_date(to_char(l_ptlv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
900 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
901 p_msg_name => G_PAST_RECORDS);
902 RAISE OKL_API.G_EXCEPTION_ERROR;
903 END IF;*/
904
905 /* public api to insert pdttemplates */
906 Okl_Pdt_Templates_Pub.insert_pdt_templates(p_api_version => p_api_version,
907 p_init_msg_list => p_init_msg_list,
908 x_return_status => l_return_status,
909 x_msg_count => x_msg_count,
910 x_msg_data => x_msg_data,
911 p_ptlv_rec => l_ptlv_rec,
912 x_ptlv_rec => x_ptlv_rec);
913
914 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
915 RAISE Okl_Api.G_EXCEPTION_ERROR;
916 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
917 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
918 END IF;
919 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
920 x_msg_data => x_msg_data);
921 EXCEPTION
922 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
923 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
924 p_pkg_name => G_PKG_NAME,
925 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
926 x_msg_count => x_msg_count,
927 x_msg_data => x_msg_data,
928 p_api_type => '_PVT');
929 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
930 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
931 p_pkg_name => G_PKG_NAME,
932 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
933 x_msg_count => x_msg_count,
934 x_msg_data => x_msg_data,
935 p_api_type => '_PVT');
936 WHEN OTHERS THEN
937 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
938 p_pkg_name => G_PKG_NAME,
939 p_exc_name => 'OTHERS',
940 x_msg_count => x_msg_count,
941 x_msg_data => x_msg_data,
942 p_api_type => '_PVT');
943
944 IF (c1%ISOPEN) THEN
945 CLOSE c1;
946 END IF;
947
948 END insert_pdttemplates;
949
950 ---------------------------------------------------------------------------
951 -- PROCEDURE update_pdttemplates for: OKL_PDT_TEMPLATES_V
952 ---------------------------------------------------------------------------
953 PROCEDURE update_pdttemplates(p_api_version IN NUMBER,
954 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
955 x_return_status OUT NOCOPY VARCHAR2,
956 x_msg_count OUT NOCOPY NUMBER,
957 x_msg_data OUT NOCOPY VARCHAR2,
958 p_ptlv_rec IN ptlv_rec_type,
959 x_ptlv_rec OUT NOCOPY ptlv_rec_type
960 ) IS
961 l_api_version CONSTANT NUMBER := 1;
962 l_api_name CONSTANT VARCHAR2(30) := 'update_pdttemplates';
963 l_no_data_found BOOLEAN := TRUE;
964 l_valid BOOLEAN := TRUE;
965 l_oldversion_enddate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
966 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
967 l_db_ptlv_rec ptlv_rec_type; /* database copy */
968 l_upd_ptlv_rec ptlv_rec_type; /* input copy */
969 l_ptlv_rec ptlv_rec_type; /* latest with the retained changes */
970 l_tmp_ptlv_rec ptlv_rec_type; /* for any other purposes */
971 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
972 l_action VARCHAR2(1);
973 l_new_version VARCHAR2(100);
974 l_pmv_count NUMBER := 0;
975 l_pdq_count NUMBER := 0;
976 l_pmvv_tbl pmvv_tbl_type;
977 l_out_pmvv_tbl pmvv_tbl_type;
978 l_pdqv_tbl pdqv_tbl_type;
979 l_out_pdqv_tbl pdqv_tbl_type;
980 BEGIN
981 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
982 l_upd_ptlv_rec := p_ptlv_rec;
983
984 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
985 p_pkg_name => G_PKG_NAME,
986 p_init_msg_list => p_init_msg_list,
987 l_api_version => l_api_version,
988 p_api_version => p_api_version,
989 p_api_type => '_PVT',
990 x_return_status => l_return_status);
991 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
992 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
993 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
994 RAISE Okl_Api.G_EXCEPTION_ERROR;
995 END IF;
996
997 /* fetch old details from the database */
998 get_rec(p_ptlv_rec => l_upd_ptlv_rec,
999 x_return_status => l_return_status,
1000 x_no_data_found => l_no_data_found,
1001 x_ptlv_rec => l_db_ptlv_rec);
1002
1003 IF l_return_status <> Okl_Api.G_RET_STS_SUCCESS OR
1004 l_no_data_found = TRUE THEN
1005 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1006 END IF;
1007
1008 /* to reorganize the input accordingly */
1009 reorganize_inputs(p_upd_ptlv_rec => l_upd_ptlv_rec,
1010 p_db_ptlv_rec => l_db_ptlv_rec);
1011
1012
1013 /* determine how the processing to be done */
1014 l_action := determine_action(p_upd_ptlv_rec => l_upd_ptlv_rec,
1015 p_db_ptlv_rec => l_db_ptlv_rec,
1016 p_date => l_sysdate);
1017
1018 /* Scenario 1: only changing description */
1019 IF l_action = '1' THEN
1020 /* public api to update product templates */
1021 Okl_Pdt_Templates_Pub.update_pdt_templates(p_api_version => p_api_version,
1022 p_init_msg_list => p_init_msg_list,
1023 x_return_status => l_return_status,
1024 x_msg_count => x_msg_count,
1025 x_msg_data => x_msg_data,
1026 p_ptlv_rec => l_upd_ptlv_rec,
1027 x_ptlv_rec => x_ptlv_rec);
1028
1029 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1030 RAISE Okl_Api.G_EXCEPTION_ERROR;
1031 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1032 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1033 END IF;
1034
1035 /* Scenario 2: only changing description and end date for all records
1036 or modified start date is less than existing start date for a future record */
1037 ELSIF l_action = '2' THEN
1038 /* create a temporary record with all relevant details from db and upd records */
1039 l_ptlv_rec := defaults_to_actuals(p_upd_ptlv_rec => l_upd_ptlv_rec,
1040 p_db_ptlv_rec => l_db_ptlv_rec);
1041
1042 l_ptlv_rec.TO_DATE := l_ptlv_rec.TO_DATE;
1043
1044 /* check the changes */
1045 check_updates(p_upd_ptlv_rec => l_upd_ptlv_rec,
1046 p_db_ptlv_rec => l_db_ptlv_rec,
1047 p_ptlv_rec => l_ptlv_rec,
1048 x_return_status => l_return_status,
1049 x_msg_data => x_msg_data);
1050 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1051 RAISE Okl_Api.G_EXCEPTION_ERROR;
1052 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1053 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1054 END IF;
1055
1056 /* public api to update product templates */
1057 Okl_Pdt_Templates_Pub.update_pdt_templates(p_api_version => p_api_version,
1058 p_init_msg_list => p_init_msg_list,
1059 x_return_status => l_return_status,
1060 x_msg_count => x_msg_count,
1061 x_msg_data => x_msg_data,
1062 p_ptlv_rec => l_upd_ptlv_rec,
1063 x_ptlv_rec => x_ptlv_rec);
1064 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1065 RAISE Okl_Api.G_EXCEPTION_ERROR;
1066 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1067 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1068 END IF;
1069
1070 /* update constraints */
1071 /* copy_update_constraints(p_api_version => p_api_version,
1072 p_init_msg_list => p_init_msg_list,
1073 p_upd_ptlv_rec => l_upd_ptlv_rec,
1074 p_db_ptlv_rec => l_db_ptlv_rec,
1075 p_ptlv_rec => l_ptlv_rec,
1076 p_flag => G_UPDATE,
1077 x_return_status => l_return_status,
1078 x_msg_count => x_msg_count,
1079 x_msg_data => x_msg_data);
1080 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1081 RAISE OKL_API.G_EXCEPTION_ERROR;
1082 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1083 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1084 END IF;
1085 */
1086 END IF;
1087
1088 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
1089 x_msg_data => x_msg_data);
1090 EXCEPTION
1091 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1092 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1093 p_pkg_name => G_PKG_NAME,
1094 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1095 x_msg_count => x_msg_count,
1096 x_msg_data => x_msg_data,
1097 p_api_type => '_PVT');
1098 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1099 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1100 p_pkg_name => G_PKG_NAME,
1101 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1102 x_msg_count => x_msg_count,
1103 x_msg_data => x_msg_data,
1104 p_api_type => '_PVT');
1105 WHEN OTHERS THEN
1106 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1107 p_pkg_name => G_PKG_NAME,
1108 p_exc_name => 'OTHERS',
1109 x_msg_count => x_msg_count,
1110 x_msg_data => x_msg_data,
1111 p_api_type => '_PVT');
1112
1113 END update_pdttemplates;
1114
1115 END Okl_Setuppdttemplates_Pvt;