[Home] [Help]
PACKAGE BODY: APPS.OKE_VERSION_PVT
Source
1 PACKAGE BODY oke_version_pvt AS
2 /* $Header: OKEVVERB.pls 120.2 2006/01/27 16:40:04 ifilimon noship $ */
3
4 --
5 -- Global Declarations
6 --
7 g_module CONSTANT VARCHAR2(250) := 'oke.plsql.oke_version_pvt.';
8 TYPE TargetRec IS RECORD
9 ( TableName VARCHAR2(30)
10 , HistTableName VARCHAR2(30)
11 , WhereClause VARCHAR2(2000)
12 );
13
14 TYPE TargetRecTab IS TABLE OF TargetRec
15 INDEX BY BINARY_INTEGER;
16
17 WorkList TargetRecTab;
18 ListCount NUMBER;
19
20 G_PKG_NAME VARCHAR2(30) := 'OKE_VERSION_PVT';
21
22 --
23 -- Private Procedures
24 --
25 PROCEDURE TimeStamp ( tag VARCHAR2 ) IS
26 l_api_name CONSTANT VARCHAR2(30) := 'TimeStamp';
27 BEGIN
28 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
29 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name, tag || ' : ' || to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') );
30 END IF;
31
32 END TimeStamp;
33
34
35 PROCEDURE AddToList
36 ( p_table_name IN VARCHAR2
37 , p_hist_table_name IN VARCHAR2
38 , p_where_clause IN VARCHAR2
39 ) IS
40 BEGIN
41 ListCount := ListCount + 1;
42 WorkList(ListCount).TableName := p_table_name;
43 WorkList(ListCount).HistTableName := p_hist_table_name;
44 WorkList(ListCount).WhereClause := p_where_clause;
45 END AddToList;
46
47
48 PROCEDURE Load_Version_List IS
49 BEGIN
50 ListCount := 0;
51 AddToList( 'OKE_K_HEADERS' , 'OKE_K_HEADERS_H' , 'K_HEADER_ID = :id');
52 AddToList( 'OKE_K_LINES' , 'OKE_K_LINES_H' ,
53 'K_LINE_ID IN ( SELECT ID FROM OKC_K_LINES_B WHERE DNZ_CHR_ID = :id)' );
54 AddToList( 'OKE_K_DELIVERABLES_B' , 'OKE_K_DELIVERABLES_BH' , 'K_HEADER_ID = :id');
55 AddToList( 'OKE_K_DELIVERABLES_TL' , 'OKE_K_DELIVERABLES_TLH' , 'K_HEADER_ID = :id');
56 AddToList( 'OKE_K_FUNDING_SOURCES' , 'OKE_K_FUNDING_SOURCES_H' , 'OBJECT_ID = :id');
57 AddToList( 'OKE_K_FUND_ALLOCATIONS' , 'OKE_K_FUND_ALLOCATIONS_H' , 'OBJECT_ID = :id');
58 AddToList( 'OKE_K_TERMS' , 'OKE_K_TERMS_H' , 'K_HEADER_ID = :id');
59 AddToList( 'OKE_K_BILLING_METHODS' , 'OKE_K_BILLING_METHODS_H' , 'K_HEADER_ID = :id');
60 AddToList( 'OKE_K_STANDARD_NOTES_B' , 'OKE_K_STANDARD_NOTES_BH' , 'K_HEADER_ID = :id');
61 AddToList( 'OKE_K_STANDARD_NOTES_TL' , 'OKE_K_STANDARD_NOTES_TLH' ,
62 'STANDARD_NOTES_ID IN ( SELECT STANDARD_NOTES_ID ' ||
63 'FROM OKE_K_STANDARD_NOTES_B WHERE K_HEADER_ID = :id)' );
64 AddToList( 'OKE_K_USER_ATTRIBUTES' , 'OKE_K_USER_ATTRIBUTES_H' , 'K_HEADER_ID = :id');
65 END Load_Version_List;
66
67
68 PROCEDURE Load_Restore_List IS
69 BEGIN
70 ListCount := 0;
71 --
72 -- OKC Tables
73 --
74 AddToList( 'OKC_K_HEADERS_B' , 'OKC_K_HEADERS_BH' , 'ID = :id');
75 AddToList( 'OKC_K_HEADERS_TL' , 'OKC_K_HEADERS_TLH' , 'ID = :id');
76 AddToList( 'OKC_K_LINES_B' , 'OKC_K_LINES_BH' , 'DNZ_CHR_ID = :id');
77 AddToList( 'OKC_K_LINES_TL' , 'OKC_K_LINES_TLH' ,
78 'ID IN ( SELECT ID FROM OKC_K_LINES_B WHERE DNZ_CHR_ID = :id )');
79 AddToList( 'OKC_ARTICLE_TRANS' , 'OKC_ARTICLE_TRANS_H' , 'DNZ_CHR_ID = :id');
80 AddToList( 'OKC_CONTACTS' , 'OKC_CONTACTS_H' , 'DNZ_CHR_ID = :id');
81 AddToList( 'OKC_COVER_TIMES' , 'OKC_COVER_TIMES_H' , 'DNZ_CHR_ID = :id');
82 AddToList( 'OKC_FUNCTION_EXPR_PARAMS' , 'OKC_FUNCTION_EXPR_PARAMS_H' , 'DNZ_CHR_ID = :id');
83 AddToList( 'OKC_GOVERNANCES' , 'OKC_GOVERNANCES_H' , 'DNZ_CHR_ID = :id');
84 AddToList( 'OKC_K_ITEMS' , 'OKC_K_ITEMS_H' , 'DNZ_CHR_ID = :id');
85 AddToList( 'OKC_K_PROCESSES' , 'OKC_K_PROCESSES_H' , 'CHR_ID = :id');
86 AddToList( 'OKC_OUTCOME_ARGUMENTS' , 'OKC_OUTCOME_ARGUMENTS_H' , 'DNZ_CHR_ID = :id');
87 AddToList( 'OKC_REACT_INTERVALS' , 'OKC_REACT_INTERVALS_H' , 'DNZ_CHR_ID = :id');
88 AddToList( 'OKC_RG_PARTY_ROLES' , 'OKC_RG_PARTY_ROLES_H' , 'DNZ_CHR_ID = :id');
89 AddToList( 'OKC_SECTIONS_B' , 'OKC_SECTIONS_BH' , 'CHR_ID = :id');
90 AddToList( 'OKC_SECTIONS_TL' , 'OKC_SECTIONS_TLH' ,
91 'ID IN ( SELECT ID FROM OKC_SECTIONS_B WHERE CHR_ID = :id )');
92 AddToList( 'OKC_SECTION_CONTENTS' , 'OKC_SECTION_CONTENTS_H' ,
93 'SCN_ID IN ( SELECT ID FROM OKC_SECTIONS_B WHERE CHR_ID = :id )');
94 AddToList( 'OKC_CONDITION_HEADERS_B' , 'OKC_CONDITION_HEADERS_BH' , 'DNZ_CHR_ID = :id');
95 AddToList( 'OKC_CONDITION_HEADERS_TL' , 'OKC_CONDITION_HEADERS_TLH' ,
96 'ID IN ( SELECT ID FROM OKC_CONDITION_HEADERS_B WHERE DNZ_CHR_ID = :id )');
97 AddToList( 'OKC_CONDITION_LINES_B' , 'OKC_CONDITION_LINES_BH' , 'DNZ_CHR_ID = :id');
98 AddToList( 'OKC_CONDITION_LINES_TL' , 'OKC_CONDITION_LINES_TLH' ,
99 'ID IN ( SELECT ID FROM OKC_CONDITION_LINES_B WHERE DNZ_CHR_ID = :id )');
100 AddToList( 'OKC_ITEM_PARTYS_B' , 'OKC_ITEM_PARTYS_BH' , 'DNZ_CHR_ID = :id');
101 AddToList( 'OKC_ITEM_PARTYS_TL' , 'OKC_ITEM_PARTYS_TLH' ,
102 'ID IN ( SELECT ID FROM OKC_ITEM_PARTYS_B WHERE DNZ_CHR_ID = :id )');
103 AddToList( 'OKC_K_ARTICLES_B' , 'OKC_K_ARTICLES_BH' , 'DNZ_CHR_ID = :id');
104 AddToList( 'OKC_K_ARTICLES_TL' , 'OKC_K_ARTICLES_TLH' ,
105 'ID IN ( SELECT ID FROM OKC_K_ARTICLES_B WHERE DNZ_CHR_ID = :id )');
106 AddToList( 'OKC_K_PARTY_ROLES_B' , 'OKC_K_PARTY_ROLES_BH' , 'DNZ_CHR_ID = :id');
107 AddToList( 'OKC_K_PARTY_ROLES_TL' , 'OKC_K_PARTY_ROLES_TLH' ,
108 'ID IN ( SELECT ID FROM OKC_K_PARTY_ROLES_B WHERE DNZ_CHR_ID = :id )');
109 AddToList( 'OKC_OUTCOMES_B' , 'OKC_OUTCOMES_BH' , 'DNZ_CHR_ID = :id');
110 AddToList( 'OKC_OUTCOMES_TL' , 'OKC_OUTCOMES_TLH' ,
111 'ID IN ( SELECT ID FROM OKC_OUTCOMES_B WHERE DNZ_CHR_ID = :id )');
112 AddToList( 'OKC_OUTCOME_ARGUMENTS' , 'OKC_OUTCOME_ARGUMENTS_H' , 'DNZ_CHR_ID = :id');
113 --
114 -- OKE Tables
115 --
116 AddToList( 'OKE_K_HEADERS' , 'OKE_K_HEADERS_H' , 'K_HEADER_ID = :id');
117 AddToList( 'OKE_K_LINES' , 'OKE_K_LINES_H' ,
118 'K_LINE_ID IN ( SELECT ID FROM OKC_K_LINES_B WHERE DNZ_CHR_ID = :id)' );
119 AddToList( 'OKE_K_DELIVERABLES_B' , 'OKE_K_DELIVERABLES_BH' , 'K_HEADER_ID = :id');
120 AddToList( 'OKE_K_DELIVERABLES_TL' , 'OKE_K_DELIVERABLES_TLH' , 'K_HEADER_ID = :id');
121 AddToList( 'OKE_K_FUNDING_SOURCES' , 'OKE_K_FUNDING_SOURCES_H' , 'OBJECT_ID = :id');
122 AddToList( 'OKE_K_FUND_ALLOCATIONS' , 'OKE_K_FUND_ALLOCATIONS_H' , 'OBJECT_ID = :id');
123 AddToList( 'OKE_K_TERMS' , 'OKE_K_TERMS_H' , 'K_HEADER_ID = :id');
124 AddToList( 'OKE_K_BILLING_METHODS' , 'OKE_K_BILLING_METHODS_H' , 'K_HEADER_ID = :id');
125 AddToList( 'OKE_K_STANDARD_NOTES_B' , 'OKE_K_STANDARD_NOTES_BH' , 'K_HEADER_ID = :id');
126 AddToList( 'OKE_K_STANDARD_NOTES_TL' , 'OKE_K_STANDARD_NOTES_TLH' ,
127 'STANDARD_NOTES_ID IN ( SELECT STANDARD_NOTES_ID ' ||
128 'FROM OKE_K_STANDARD_NOTES_B WHERE K_HEADER_ID = :id)' );
129 AddToList( 'OKE_K_USER_ATTRIBUTES' , 'OKE_K_USER_ATTRIBUTES_H' , 'K_HEADER_ID = :id');
130 END Load_Restore_List;
131
132
133 PROCEDURE version_table
134 ( p_header_id IN NUMBER
135 , p_table_name IN VARCHAR2
136 , p_hist_table_name IN VARCHAR2
137 , p_where_clause IN VARCHAR2
138 , p_prev_vers_num IN NUMBER
139 ) IS
140
141 column_list VARCHAR2(8000);
142 statement VARCHAR2(20000);
143 c NUMBER;
144 row_processed NUMBER;
145
146 table_not_found exception;
147 l_api_name CONSTANT VARCHAR2(30) := 'Version_table';
148
149 cursor c_col is
150 -- bug 3720887
151 select column_name, column_id
152 from all_tab_columns tc
153 where (tc.owner,tc.table_name) in (
154 select us.table_owner,us.synonym_name
155 from user_synonyms us
156 where us.synonym_name = p_table_name)
157 and tc.column_name <> 'MAJOR_VERSION'
158 and tc.data_type not in ('LONG', 'LONG RAW')
159 and exists (
160 select 1
161 from user_synonyms us2,
162 all_tab_columns tc2
163 where us2.synonym_name = p_hist_table_name
164 and tc2.table_name = us2.synonym_name
165 and tc2.owner = us2.table_owner
166 and tc2.column_name = tc.column_name
167 )
168 order by column_id;
169
170 /*
171 select distinct column_name, column_id
172 from all_tab_columns tc
173 , user_synonyms us
174 where us.synonym_name = p_table_name
175 and tc.table_name = us.synonym_name
176 and tc.owner = us.table_owner
177 and tc.column_name <> 'MAJOR_VERSION'
178 and tc.data_type not in ('LONG', 'LONG RAW')
179 and exists (
180 select null
181 from all_tab_columns tc2
182 , user_synonyms us2
183 where us2.synonym_name = p_hist_table_name
184 and tc2.table_name = us2.synonym_name
185 and tc2.owner = us2.table_owner
186 and tc2.column_name = tc.column_name
187 )
188 order by column_id; */
189
190 BEGIN
191
192 --
193 -- Building the column list based on database data dictionary
194 --
195 column_list := '';
196 c := 0;
197 for colrec in c_col loop
198 column_list := column_list || colrec.column_name || ' , ';
199 c := c + 1;
200 end loop;
201 if ( c = 0 ) then
202 raise table_not_found;
203 end if;
204
205 --
206 -- Construct the insert statement. We need to add the extra
207 -- column MAJOR_VERSION here. The value comes from the
208 -- input parameter p_prev_vers_num
209 --
210 statement := 'INSERT INTO ' || p_hist_table_name || ' ( ' ||
211 column_list || 'MAJOR_VERSION ) SELECT ' ||
212 column_list || ':mv FROM ' || p_table_name ||
213 ' WHERE ' || p_where_clause;
214
215 --
216 -- Parse, bind and execute the SQL
217 --
218 c := dbms_sql.open_cursor;
219 dbms_sql.parse(c, statement, dbms_sql.native);
220 dbms_sql.bind_variable(c, 'mv', p_prev_vers_num);
221 dbms_sql.bind_variable(c, 'id', p_header_id);
222 row_processed := dbms_sql.execute(c);
223 dbms_sql.close_cursor(c);
224
225
226 --
227 -- store previous amounts of versions for funding
228 --
229
230 IF p_table_name='OKE_K_FUNDING_SOURCES' OR p_table_name='OKE_K_FUND_ALLOCATIONS' THEN
231 statement := 'UPDATE '||p_table_name|| ' set PREVIOUS_AMOUNT=AMOUNT WHERE ' ||p_where_clause;
232 c := dbms_sql.open_cursor;
233 dbms_sql.parse(c, statement, dbms_sql.native);
234 dbms_sql.bind_variable(c, 'id', p_header_id);
235 row_processed := dbms_sql.execute(c);
236 dbms_sql.close_cursor(c);
237 END IF;
238
239
240
241
242 EXCEPTION
243 WHEN table_not_found THEN
244 NULL;
245 WHEN OTHERS THEN
246 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
247 FND_MESSAGE.set_name('OKE','OKE_VERS_SQL_ERROR');
248 FND_MESSAGE.set_token('NUM', (-1) * sqlcode);
249 FND_MESSAGE.set_token('TABLE', p_table_name);
250 FND_MSG_PUB.add;
251 END IF;
252 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
253 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,statement);
254 END IF;
255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
256
257 END version_table;
258
259
260 PROCEDURE restore_table
261 ( p_api_version IN NUMBER
262 , p_commit IN VARCHAR2 := FND_API.G_FALSE
263 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
264 , x_msg_count OUT NOCOPY NUMBER
265 , x_msg_data OUT NOCOPY VARCHAR2
266 , x_return_status OUT NOCOPY VARCHAR2
267 , p_header_id IN NUMBER
268 , p_table_name IN VARCHAR2
269 , p_hist_table_name IN VARCHAR2
270 , p_where_clause IN VARCHAR2
271 , p_rstr_from_ver IN NUMBER
272 ) IS
273
274 column_list VARCHAR2(8000);
275 statement VARCHAR2(20000);
276 c NUMBER;
277 row_processed NUMBER;
278
279 table_not_found exception;
280
281 cursor c_col is
282 -- bug 3720887
283 select column_name, column_id
284 from all_tab_columns tc
285 where (tc.owner,tc.table_name) in (
286 select us.table_owner,us.synonym_name
287 from user_synonyms us
288 where us.synonym_name = p_table_name)
289 and tc.column_name <> 'MAJOR_VERSION'
290 and tc.data_type not in ('LONG', 'LONG RAW')
291 and exists (
292 select 1
293 from user_synonyms us2,
294 all_tab_columns tc2
295 where us2.synonym_name = p_hist_table_name
296 and tc2.table_name = us2.synonym_name
297 and tc2.owner = us2.table_owner
298 and tc2.column_name = tc.column_name
299 )
300 order by column_id;
301
302 /*
303 select distinct column_name, column_id
304 from all_tab_columns tc
305 , user_synonyms us
306 where us.synonym_name = p_table_name
307 and tc.table_name = us.synonym_name
308 and tc.owner = us.table_owner
309 and tc.column_name <> 'MAJOR_VERSION'
310 and tc.data_type not in ('LONG', 'LONG RAW')
311 and exists (
312 select null
313 from all_tab_columns tc2
314 , user_synonyms us2
315 where us2.synonym_name = p_hist_table_name
316 and tc2.table_name = us2.synonym_name
317 and tc2.owner = us2.table_owner
318 and tc2.column_name = tc.column_name
319 )
320 order by column_id;
321 */
322
323 l_api_name CONSTANT VARCHAR2(30) := 'restore_table';
324
325
326 BEGIN
327 --
328 -- Building the column list based on database data dictionary
329 --
330 column_list := '';
331 c := 0;
332 for colrec in c_col loop
333 if ( c = 0 ) then
334 column_list := colrec.column_name;
335 else
336 column_list := column_list || ' , ' || colrec.column_name;
337 end if;
338 c := c + 1;
339 end loop;
340 if ( c = 0 ) then
341 raise table_not_found;
342 end if;
343
344
345
346 IF p_table_name<>'OKE_K_FUNDING_SOURCES' AND p_table_name<>'OKE_K_FUND_ALLOCATIONS' THEN
347
348 -- not funding
349
350 --
351 -- First, construct the detele statement.
352 --
353 statement := 'DELETE FROM ' || p_table_name ||
354 ' WHERE ' || p_where_clause;
355
356 --
357 -- Parse, bind and execute the SQL
358 --
359 c := dbms_sql.open_cursor;
360 dbms_sql.parse(c, statement, dbms_sql.native);
361 dbms_sql.bind_variable(c, 'id', p_header_id);
362 row_processed := dbms_sql.execute(c);
363 dbms_sql.close_cursor(c);
364
365 --
366 -- Now, construct the insert statement.
367 --
368 statement := 'INSERT INTO ' || p_table_name || ' ( ' ||
369 column_list || ' ) SELECT ' ||
370 column_list || ' FROM ' || p_hist_table_name ||
371 ' WHERE ' || p_where_clause ||
372 ' AND MAJOR_VERSION = :mv';
373
374 --
375 -- Parse, bind and execute the SQL
376 --
377 c := dbms_sql.open_cursor;
378 dbms_sql.parse(c, statement, dbms_sql.native);
379 dbms_sql.bind_variable(c, 'id', p_header_id);
380 dbms_sql.bind_variable(c, 'mv', p_rstr_from_ver);
381 row_processed := dbms_sql.execute(c);
382 dbms_sql.close_cursor(c);
383
384
385 --
386 -- store previous amounts of versions for funding
387 -- COPY ATTRIBUTE BY ATTRIBUTE TO THE OLD ONE
388
389
390 /*
391 SOURCES
392 |---------------------|
393 |current |
394 | |
395 | X-----------| | R = source does not exist in history (not_included_allocs)
396 | | | | Q = source is in history and all allocs in history(included_allocs)
397 | R Q Y | | ?Z = source is in history and not all allocs in history
398 | | | | X = R+Q
399 | | history | |
400 | |-----------| |
401 |----------------------
402 */
403 END IF;
404
405 /*
406
407
408 ELSE
409 statement := 'UPDATE '||p_table_name|| ' set PREVIOUS_AMOUNT=(SELECT AMOUNT FROM '||
410 p_hist_table_name ||' WHERE '||p_where_clause||' AND MAJOR_VERSION = :mv)'
411 || 'WHERE ' ||p_where_clause;
412 c := dbms_sql.open_cursor;
413 dbms_sql.parse(c, statement, dbms_sql.native);
414 dbms_sql.bind_variable(c, 'id', p_header_id);
415 row_processed := dbms_sql.execute(c);
416 dbms_sql.close_cursor(c);
417 END IF;
418 */
419
420
421 EXCEPTION
422 WHEN table_not_found THEN
423 NULL;
424 WHEN OTHERS THEN
425 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
426 FND_MESSAGE.set_name('OKE','OKE_VERS_SQL_ERROR');
427 FND_MESSAGE.set_token('NUM', sqlcode);
428 FND_MESSAGE.set_token('TABLE', p_table_name);
429 FND_MSG_PUB.add;
430 END IF;
431 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
432 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,statement);
433 END IF;
434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
435
436 END restore_table;
437
438
439 --
440 -- Private Procedures
441 --
442 PROCEDURE version_contract
443 ( p_api_version IN NUMBER
444 , p_commit IN VARCHAR2 := FND_API.G_FALSE
445 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
446 , x_msg_count OUT NOCOPY NUMBER
447 , x_msg_data OUT NOCOPY VARCHAR2
448 , x_return_status OUT NOCOPY VARCHAR2
449 , p_chr_id IN NUMBER
450 , p_chg_request_id IN NUMBER
451 , p_version_reason_code IN VARCHAR2
452 , x_prev_vers OUT NOCOPY NUMBER
453 , x_new_vers OUT NOCOPY NUMBER
454 ) IS
455
456 l_api_name CONSTANT VARCHAR2(30) := 'version_contract';
457 okc_cvmv_rec_in okc_cvm_pvt.cvmv_rec_type;
458 okc_cvmv_rec_out okc_cvm_pvt.cvmv_rec_type;
459 i NUMBER;
460 l_return_status VARCHAR2(1);
461 progress NUMBER := 0;
462
463 cursor cvmv is
464 select kvn.chr_id
465 , kvn.major_version
466 , kvn.minor_version
467 , kvn.object_version_number
468 , kvn.created_by
469 , kvn.creation_date
470 , kvn.last_updated_by
471 , kvn.last_update_date
472 , kvn.last_update_login
473 FROM okc_k_vers_numbers kvn
474 WHERE chr_id = p_chr_id;
475
476 BEGIN
477
478 TimeStamp('version_contract() <<');
479
480 --
481 -- Standard Start of API savepoint
482 --
483 SAVEPOINT version_contract_pvt;
484
485 --
486 -- Set API return status to success
487 --
488 x_return_status := FND_API.G_RET_STS_SUCCESS;
489 l_return_status := FND_API.G_RET_STS_SUCCESS;
490
491 --
492 -- Check API incompatibility
493 --
494
495 --
496 -- Initialize the message table if requested.
497 --
498 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
499 FND_MSG_PUB.initialize;
500 END IF;
501
502 --
503 -- Get the current version number from OKC
504 --
505 OPEN cvmv;
506 FETCH cvmv INTO okc_cvmv_rec_in.chr_id
507 , okc_cvmv_rec_in.major_version
508 , okc_cvmv_rec_in.minor_version
509 , okc_cvmv_rec_in.object_version_number
510 , okc_cvmv_rec_in.created_by
511 , okc_cvmv_rec_in.creation_date
512 , okc_cvmv_rec_in.last_updated_by
513 , okc_cvmv_rec_in.last_update_date
514 , okc_cvmv_rec_in.last_update_login;
515
516 progress := 1;
517
518 IF cvmv%notfound THEN
519 CLOSE cvmv;
520 ROLLBACK TO version_contract_pvt;
521 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
522 FND_MESSAGE.set_name('OKE','OKE_VERS_INVALID_CONTRACT');
523 FND_MSG_PUB.add;
524 END IF;
525 x_return_status := FND_API.G_RET_STS_ERROR;
526 RAISE FND_API.G_EXC_ERROR;
527 END IF;
528 CLOSE cvmv;
529
530 progress := 2;
531
532 x_prev_vers := okc_cvmv_rec_in.major_version;
533
534 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
535 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Previous Version = ' || x_prev_vers);
536 END IF;
537
538 --
539 -- Version OKC side of contract components
540 --
541 OKC_VERSION_PVT.Version_Contract
542 ( p_api_version => p_api_version
543 , p_commit => FND_API.G_FALSE
544 , p_init_msg_list => p_init_msg_list
545 , x_return_status => l_return_status
546 , x_msg_count => x_msg_count
547 , x_msg_data => x_msg_data
548 , p_cvmv_rec => okc_cvmv_rec_in
549 , x_cvmv_rec => okc_cvmv_rec_out );
550 --
551 -- If anything happens, abort API
552 --
553
554 progress := 3;
555
556 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
557 ROLLBACK TO version_contract_pvt;
558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
561 ROLLBACK TO version_contract_pvt;
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 RAISE FND_API.G_EXC_ERROR;
564 END IF;
565
566 progress := 4;
567
568 x_new_vers := okc_cvmv_rec_out.major_version;
569
570 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
571 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'New Version = ' || x_new_vers);
572 END IF;
573
574 --
575 -- Load OKE Work List
576 --
577 Load_Version_List;
578
579 progress := 5;
580
581 --
582 -- Loop through work list
583 --
584 i := WorkList.FIRST;
585 loop
586 TimeStamp('Versioning ' || WorkList(i).TableName);
587 version_table( p_chr_id
588 , WorkList(i).TableName
589 , WorkList(i).HistTableName
590 , WorkList(i).WhereClause
591 , x_new_vers );
592 exit when ( i = ListCount );
593 i := WorkList.NEXT(i);
594 end loop;
595
596
597 progress := 6;
598
599 --
600 -- Insert to OKE_K_VERS_NUMBERS_H
601 --
602
603 insert into OKE_K_VERS_NUMBERS_H
604 (K_HEADER_ID
605 ,MAJOR_VERSION
606 ,CREATION_DATE
607 ,CREATED_BY
608 ,LAST_UPDATE_DATE
609 ,LAST_UPDATED_BY
610 ,LAST_UPDATE_LOGIN
611 ,VERSION_REASON_CODE
612 ,CHG_REQUEST_ID)
613 values
614 (p_chr_id
615 ,x_new_vers
616 ,sysdate
617 ,fnd_global.user_id
618 ,sysdate
619 ,fnd_global.user_id
620 ,fnd_global.login_id
621 ,p_version_reason_code
622 ,p_chg_request_id
623 );
624
625 progress := 7;
626
627 --
628 -- Standard commit check
629 --
630 IF FND_API.TO_BOOLEAN( p_commit ) THEN
631 COMMIT;
632 END IF;
633
634 progress := 8;
635
636 --
637 -- Standard call to get message count and if count is 1, get message
638 -- info
639 --
640 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
641 , p_data => x_msg_data );
642
643 TimeStamp('version_contract() >>');
644
645 EXCEPTION
646 WHEN FND_API.G_EXC_ERROR THEN
647 ROLLBACK TO version_contract_pvt;
648 x_return_status := FND_API.G_RET_STS_ERROR;
649 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
650 , p_data => x_msg_data );
651
652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
653 ROLLBACK TO version_contract_pvt;
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
656 , p_data => x_msg_data );
657
658 WHEN OTHERS THEN
659 ROLLBACK TO version_contract_pvt;
660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
661 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
662 FND_MSG_PUB.add_exc_msg
663 ( p_pkg_name => G_PKG_NAME
664 , p_procedure_name => 'VERSION_CONTRACT(' || progress || ')');
665
666 END IF;
667 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
668 , p_data => x_msg_data );
669
670
671 END version_contract;
672
673
674 PROCEDURE restore_contract_version
675 ( p_api_version IN NUMBER
676 , p_commit IN VARCHAR2 := FND_API.G_FALSE
677 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
678 , x_msg_count OUT NOCOPY NUMBER
679 , x_msg_data OUT NOCOPY VARCHAR2
680 , x_return_status OUT NOCOPY VARCHAR2
681 , p_chr_id IN NUMBER
682 , p_rstr_from_ver IN NUMBER
683 , p_chg_request_id IN NUMBER
684 , p_version_reason_code IN VARCHAR2
685 , x_new_vers OUT NOCOPY NUMBER
686 ) IS
687
688 i NUMBER;
689 l_orig_vers_num NUMBER;
690 l_new_vers_num NUMBER;
691 l_return_status VARCHAR2(1);
692 l_source NUMBER;
693 l_alloc NUMBER;
694 l_flag VARCHAR2(1);
695 l_flag_h VARCHAR2(1);
696 l_counter1 NUMBER;
697 l_counter2 NUMBER;
698 l_counter3 NUMBER;
699
700 p_funding_in_rec OKE_FUNDING_PUB.FUNDING_REC_IN_TYPE ;
701 x_funding_out_rec OKE_FUNDING_PUB.FUNDING_REC_OUT_TYPE ;
702 p_allocation_in_tbl OKE_FUNDING_PUB.ALLOCATION_IN_TBL_TYPE ;
703 p_allocation_in_tbl2 OKE_FUNDING_PUB.ALLOCATION_IN_TBL_TYPE ;
704 x_allocation_out_tbl OKE_FUNDING_PUB.ALLOCATION_OUT_TBL_TYPE ;
705 l_agreement_type VARCHAR2(30) := OKE_API.G_MISS_CHAR ;
706
707
708
709 BEGIN
710
711 TimeStamp('restore_contract_version() <<');
712
713 --
714 -- Standard Start of API savepoint
715 --
716 SAVEPOINT rstr_contract_version_pvt;
717
718 --
719 -- Set API return status to success
720 --
721 x_return_status := FND_API.G_RET_STS_SUCCESS;
722 l_return_status := FND_API.G_RET_STS_SUCCESS;
723
724 --
725 -- Check API incompatibility
726 --
727
728 --
729 -- Initialize the message table if requested.
730 --
731 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
732 FND_MSG_PUB.initialize;
733 END IF;
734 /* bug#4451971
735 --
736 -- First version the current state of the contract
737 --
738 version_contract( p_api_version => p_api_version
739 , p_commit => FND_API.G_FALSE
740 , p_init_msg_list => FND_API.G_FALSE
741 , x_msg_count => x_msg_count
742 , x_msg_data => x_msg_data
743 , x_return_status => l_return_status
744 , p_chr_id => p_chr_id
745 , p_chg_request_id => p_chg_request_id
746 , p_version_reason_code => p_version_reason_code
747 , x_prev_vers => l_orig_vers_num
748 , x_new_vers => l_new_vers_num
749 );
750
751 --
752 -- If anything happens, abort API
753 --
754 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
755 ROLLBACK TO rstr_contract_version_pvt;
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
758 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
759 ROLLBACK TO rstr_contract_version_pvt;
760 x_return_status := FND_API.G_RET_STS_ERROR;
761 RAISE FND_API.G_EXC_ERROR;
762 END IF;
763
764 x_new_vers := l_new_vers_num;
765 end of bug#4451971 */
766 x_new_vers := l_orig_vers_num;
767
768 --
769 -- Load Restore Work List
770 --
771 -- Load_Restore_List;
772
773 --
774 -- Loop through work list
775 --
776 /*
777 i := WorkList.FIRST;
778 loop
779 TimeStamp('Restoring ' || WorkList(i).TableName);
780 restore_table( p_api_version
781 , p_commit
782 , p_init_msg_list
783 , x_msg_count
784 , x_msg_data
785 , x_return_status
786 , p_chr_id
787 , WorkList(i).TableName
788 , WorkList(i).HistTableName
789 , WorkList(i).WhereClause
790 , p_rstr_from_ver );
791 exit when ( i = ListCount );
792 i := WorkList.NEXT(i);
793 end loop;
794
795 */
796
797
798 --
799 -- Standard commit check
800 --
801 IF FND_API.TO_BOOLEAN( p_commit ) THEN
802 COMMIT;
803 END IF;
804
805 --
806 -- Standard call to get message count and if count is 1, get message
807 -- info
808 --
809 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
810 , p_data => x_msg_data );
811
812 TimeStamp('restore_contract_version() >>');
813
814 EXCEPTION
815 WHEN FND_API.G_EXC_ERROR THEN
816 ROLLBACK TO rstr_contract_version_pvt;
817 x_return_status := FND_API.G_RET_STS_ERROR;
818 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
819 , p_data => x_msg_data );
820
821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
822 ROLLBACK TO rstr_contract_version_pvt;
823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
824 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
825 , p_data => x_msg_data );
826
827 WHEN OTHERS THEN
828 ROLLBACK TO rstr_contract_version_pvt;
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
830 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
831 FND_MSG_PUB.add_exc_msg
832 ( p_pkg_name => G_PKG_NAME
833 , p_procedure_name => 'RESTORE_CONTRACT_VERSION' );
834
835 END IF;
836 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
837 , p_data => x_msg_data );
838
839
840 END restore_contract_version;
841
842 END oke_version_pvt;