[Home] [Help]
PACKAGE BODY: APPS.OKE_VERSION_PVT
Source
1 PACKAGE BODY oke_version_pvt AS
2 /* $Header: OKEVVERB.pls 120.2.12020000.3 2013/04/12 13:02:34 ansraj ship $ */
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
151 --for bugfix 16433403
152
153 SELECT /*+ leading(US) MERGE(TC) */ COLUMN_NAME , COLUMN_ID
154 FROM ALL_TAB_COLUMNS TC,
155 (SELECT US.TABLE_OWNER , US.TABLE_NAME
156 FROM USER_SYNONYMS US
157 WHERE US.SYNONYM_NAME = p_table_name ) US
158 WHERE TC.OWNER = US.table_owner
159 AND TC.TABLE_NAME = US.TABLE_NAME
160 AND TC.COLUMN_NAME <> 'MAJOR_VERSION'
161 AND TC.DATA_TYPE NOT IN ( 'LONG' , 'LONG RAW' )
162 AND EXISTS ( SELECT /*+ no_unnest */ 1
163 FROM USER_SYNONYMS US2 , ALL_TAB_COLUMNS TC2
164 WHERE US2.SYNONYM_NAME = p_hist_table_name
165 AND TC2.TABLE_NAME = US2.TABLE_NAME
166 AND TC2.OWNER = US2.TABLE_OWNER
167 AND TC2.COLUMN_NAME = TC.COLUMN_NAME )
168 ORDER BY COLUMN_ID;
169
170 /*
171 -- bug 3720887
172 select column_name, column_id
173 from all_tab_columns tc
174 where (tc.owner,tc.table_name) in (
175 select us.table_owner,us.table_name --modified for bugfix 15926104
176 from user_synonyms us
177 where us.synonym_name = p_table_name)
178 and tc.column_name <> 'MAJOR_VERSION'
179 and tc.data_type not in ('LONG', 'LONG RAW')
180 and exists (
181 select 1
182 from user_synonyms us2,
183 all_tab_columns tc2
184 where us2.synonym_name = p_hist_table_name
185 and tc2.table_name = us2.table_name --modified for bugfix 15926104
186 and tc2.owner = us2.table_owner
187 and tc2.column_name = tc.column_name
188 )
189 order by column_id;*/
190
191 /*
192 select distinct column_name, column_id
193 from all_tab_columns tc
194 , user_synonyms us
195 where us.synonym_name = p_table_name
196 and tc.table_name = us.synonym_name
197 and tc.owner = us.table_owner
198 and tc.column_name <> 'MAJOR_VERSION'
199 and tc.data_type not in ('LONG', 'LONG RAW')
200 and exists (
201 select null
202 from all_tab_columns tc2
203 , user_synonyms us2
204 where us2.synonym_name = p_hist_table_name
205 and tc2.table_name = us2.synonym_name
206 and tc2.owner = us2.table_owner
207 and tc2.column_name = tc.column_name
208 )
209 order by column_id; */
210
211 BEGIN
212
213 --
214 -- Building the column list based on database data dictionary
215 --
216 column_list := '';
217 c := 0;
218 for colrec in c_col loop
219 column_list := column_list || colrec.column_name || ' , ';
220 c := c + 1;
221 end loop;
222 if ( c = 0 ) then
223 raise table_not_found;
224 end if;
225
226 --
227 -- Construct the insert statement. We need to add the extra
228 -- column MAJOR_VERSION here. The value comes from the
229 -- input parameter p_prev_vers_num
230 --
231 statement := 'INSERT INTO ' || p_hist_table_name || ' ( ' ||
232 column_list || 'MAJOR_VERSION ) SELECT ' ||
233 column_list || ':mv FROM ' || p_table_name ||
234 ' WHERE ' || p_where_clause;
235
236 --
237 -- Parse, bind and execute the SQL
238 --
239 c := dbms_sql.open_cursor;
240 dbms_sql.parse(c, statement, dbms_sql.native);
241 dbms_sql.bind_variable(c, 'mv', p_prev_vers_num);
242 dbms_sql.bind_variable(c, 'id', p_header_id);
243 row_processed := dbms_sql.execute(c);
244 dbms_sql.close_cursor(c);
245
246
247 --
248 -- store previous amounts of versions for funding
249 --
250
251 IF p_table_name='OKE_K_FUNDING_SOURCES' OR p_table_name='OKE_K_FUND_ALLOCATIONS' THEN
252 statement := 'UPDATE '||p_table_name|| ' set PREVIOUS_AMOUNT=AMOUNT WHERE ' ||p_where_clause;
253 c := dbms_sql.open_cursor;
254 dbms_sql.parse(c, statement, dbms_sql.native);
255 dbms_sql.bind_variable(c, 'id', p_header_id);
256 row_processed := dbms_sql.execute(c);
257 dbms_sql.close_cursor(c);
258 END IF;
259
260
261
262
263 EXCEPTION
264 WHEN table_not_found THEN
265 NULL;
266 WHEN OTHERS THEN
267 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
268 FND_MESSAGE.set_name('OKE','OKE_VERS_SQL_ERROR');
269 FND_MESSAGE.set_token('NUM', (-1) * sqlcode);
270 FND_MESSAGE.set_token('TABLE', p_table_name);
271 FND_MSG_PUB.add;
272 END IF;
273 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
274 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,statement);
275 END IF;
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277
278 END version_table;
279
280
281 PROCEDURE restore_table
282 ( p_api_version IN NUMBER
283 , p_commit IN VARCHAR2 := FND_API.G_FALSE
284 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
285 , x_msg_count OUT NOCOPY NUMBER
286 , x_msg_data OUT NOCOPY VARCHAR2
287 , x_return_status OUT NOCOPY VARCHAR2
288 , p_header_id IN NUMBER
289 , p_table_name IN VARCHAR2
290 , p_hist_table_name IN VARCHAR2
291 , p_where_clause IN VARCHAR2
292 , p_rstr_from_ver IN NUMBER
293 ) IS
294
295 column_list VARCHAR2(8000);
296 statement VARCHAR2(20000);
297 c NUMBER;
298 row_processed NUMBER;
299
300 table_not_found exception;
301
302 cursor c_col is
303
304 --for bugfix 16433403
305
306 SELECT /*+ leading(US) MERGE(TC) */ COLUMN_NAME , COLUMN_ID
307 FROM ALL_TAB_COLUMNS TC,
308 (SELECT US.TABLE_OWNER , US.TABLE_NAME
309 FROM USER_SYNONYMS US
310 WHERE US.SYNONYM_NAME = p_table_name ) US
311 WHERE TC.OWNER = US.table_owner
312 AND TC.TABLE_NAME = US.TABLE_NAME
313 AND TC.COLUMN_NAME <> 'MAJOR_VERSION'
314 AND TC.DATA_TYPE NOT IN ( 'LONG' , 'LONG RAW' )
315 AND EXISTS ( SELECT /*+ no_unnest */ 1
316 FROM USER_SYNONYMS US2 , ALL_TAB_COLUMNS TC2
317 WHERE US2.SYNONYM_NAME = p_hist_table_name
318 AND TC2.TABLE_NAME = US2.TABLE_NAME
319 AND TC2.OWNER = US2.TABLE_OWNER
320 AND TC2.COLUMN_NAME = TC.COLUMN_NAME )
321 ORDER BY COLUMN_ID;
322
323 /*
324 -- bug 3720887
325 select column_name, column_id
326 from all_tab_columns tc
327 where (tc.owner,tc.table_name) in (
328 select us.table_owner,us.table_name --modified for bugfix 15926104
329 from user_synonyms us
330 where us.synonym_name = p_table_name)
331 and tc.column_name <> 'MAJOR_VERSION'
332 and tc.data_type not in ('LONG', 'LONG RAW')
333 and exists (
334 select 1
335 from user_synonyms us2,
336 all_tab_columns tc2
337 where us2.synonym_name = p_hist_table_name
338 and tc2.table_name = us2.table_name --modified for bugfix 15926104
339 and tc2.owner = us2.table_owner
340 and tc2.column_name = tc.column_name
341 )
342 order by column_id;
343 */
344 /*
345 select distinct column_name, column_id
346 from all_tab_columns tc
347 , user_synonyms us
348 where us.synonym_name = p_table_name
349 and tc.table_name = us.synonym_name
350 and tc.owner = us.table_owner
351 and tc.column_name <> 'MAJOR_VERSION'
352 and tc.data_type not in ('LONG', 'LONG RAW')
353 and exists (
354 select null
355 from all_tab_columns tc2
356 , user_synonyms us2
357 where us2.synonym_name = p_hist_table_name
358 and tc2.table_name = us2.synonym_name
359 and tc2.owner = us2.table_owner
360 and tc2.column_name = tc.column_name
361 )
362 order by column_id;
363 */
364
365 l_api_name CONSTANT VARCHAR2(30) := 'restore_table';
366
367
368 BEGIN
369 --
370 -- Building the column list based on database data dictionary
371 --
372 column_list := '';
373 c := 0;
374 for colrec in c_col loop
375 if ( c = 0 ) then
376 column_list := colrec.column_name;
377 else
378 column_list := column_list || ' , ' || colrec.column_name;
379 end if;
380 c := c + 1;
381 end loop;
382 if ( c = 0 ) then
383 raise table_not_found;
384 end if;
385
386
387
388 IF p_table_name<>'OKE_K_FUNDING_SOURCES' AND p_table_name<>'OKE_K_FUND_ALLOCATIONS' THEN
389
390 -- not funding
391
392 --
393 -- First, construct the detele statement.
394 --
395 statement := 'DELETE FROM ' || p_table_name ||
396 ' WHERE ' || p_where_clause;
397
398 --
399 -- Parse, bind and execute the SQL
400 --
401 c := dbms_sql.open_cursor;
402 dbms_sql.parse(c, statement, dbms_sql.native);
403 dbms_sql.bind_variable(c, 'id', p_header_id);
404 row_processed := dbms_sql.execute(c);
405 dbms_sql.close_cursor(c);
406
407 --
408 -- Now, construct the insert statement.
409 --
410 statement := 'INSERT INTO ' || p_table_name || ' ( ' ||
411 column_list || ' ) SELECT ' ||
412 column_list || ' FROM ' || p_hist_table_name ||
413 ' WHERE ' || p_where_clause ||
414 ' AND MAJOR_VERSION = :mv';
415
416 --
417 -- Parse, bind and execute the SQL
418 --
419 c := dbms_sql.open_cursor;
420 dbms_sql.parse(c, statement, dbms_sql.native);
421 dbms_sql.bind_variable(c, 'id', p_header_id);
422 dbms_sql.bind_variable(c, 'mv', p_rstr_from_ver);
423 row_processed := dbms_sql.execute(c);
424 dbms_sql.close_cursor(c);
425
426
427 --
428 -- store previous amounts of versions for funding
429 -- COPY ATTRIBUTE BY ATTRIBUTE TO THE OLD ONE
430
431
432 /*
433 SOURCES
434 |---------------------|
435 |current |
436 | |
437 | X-----------| | R = source does not exist in history (not_included_allocs)
438 | | | | Q = source is in history and all allocs in history(included_allocs)
439 | R Q Y | | ?Z = source is in history and not all allocs in history
440 | | | | X = R+Q
441 | | history | |
442 | |-----------| |
443 |----------------------
444 */
445 END IF;
446
447 /*
448
449
450 ELSE
451 statement := 'UPDATE '||p_table_name|| ' set PREVIOUS_AMOUNT=(SELECT AMOUNT FROM '||
452 p_hist_table_name ||' WHERE '||p_where_clause||' AND MAJOR_VERSION = :mv)'
453 || 'WHERE ' ||p_where_clause;
454 c := dbms_sql.open_cursor;
455 dbms_sql.parse(c, statement, dbms_sql.native);
456 dbms_sql.bind_variable(c, 'id', p_header_id);
457 row_processed := dbms_sql.execute(c);
458 dbms_sql.close_cursor(c);
459 END IF;
460 */
461
462
463 EXCEPTION
464 WHEN table_not_found THEN
465 NULL;
466 WHEN OTHERS THEN
467 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
468 FND_MESSAGE.set_name('OKE','OKE_VERS_SQL_ERROR');
469 FND_MESSAGE.set_token('NUM', sqlcode);
470 FND_MESSAGE.set_token('TABLE', p_table_name);
471 FND_MSG_PUB.add;
472 END IF;
473 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
474 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,statement);
475 END IF;
476 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
477
478 END restore_table;
479
480
481 --
482 -- Private Procedures
483 --
484 PROCEDURE version_contract
485 ( p_api_version IN NUMBER
486 , p_commit IN VARCHAR2 := FND_API.G_FALSE
487 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
488 , x_msg_count OUT NOCOPY NUMBER
489 , x_msg_data OUT NOCOPY VARCHAR2
490 , x_return_status OUT NOCOPY VARCHAR2
491 , p_chr_id IN NUMBER
492 , p_chg_request_id IN NUMBER
493 , p_version_reason_code IN VARCHAR2
494 , x_prev_vers OUT NOCOPY NUMBER
495 , x_new_vers OUT NOCOPY NUMBER
496 ) IS
497
498 l_api_name CONSTANT VARCHAR2(30) := 'version_contract';
499 okc_cvmv_rec_in okc_cvm_pvt.cvmv_rec_type;
500 okc_cvmv_rec_out okc_cvm_pvt.cvmv_rec_type;
501 i NUMBER;
502 l_return_status VARCHAR2(1);
503 progress NUMBER := 0;
504
505 cursor cvmv is
506 select kvn.chr_id
507 , kvn.major_version
508 , kvn.minor_version
509 , kvn.object_version_number
510 , kvn.created_by
511 , kvn.creation_date
512 , kvn.last_updated_by
513 , kvn.last_update_date
514 , kvn.last_update_login
515 FROM okc_k_vers_numbers kvn
516 WHERE chr_id = p_chr_id;
517
518 BEGIN
519
520 TimeStamp('version_contract() <<');
521
522 --
523 -- Standard Start of API savepoint
524 --
525 SAVEPOINT version_contract_pvt;
526
527 --
528 -- Set API return status to success
529 --
530 x_return_status := FND_API.G_RET_STS_SUCCESS;
531 l_return_status := FND_API.G_RET_STS_SUCCESS;
532
533 --
534 -- Check API incompatibility
535 --
536
537 --
538 -- Initialize the message table if requested.
539 --
540 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
541 FND_MSG_PUB.initialize;
542 END IF;
543
544 --
545 -- Get the current version number from OKC
546 --
547 OPEN cvmv;
548 FETCH cvmv INTO okc_cvmv_rec_in.chr_id
549 , okc_cvmv_rec_in.major_version
550 , okc_cvmv_rec_in.minor_version
551 , okc_cvmv_rec_in.object_version_number
552 , okc_cvmv_rec_in.created_by
553 , okc_cvmv_rec_in.creation_date
554 , okc_cvmv_rec_in.last_updated_by
555 , okc_cvmv_rec_in.last_update_date
556 , okc_cvmv_rec_in.last_update_login;
557
558 progress := 1;
559
560 IF cvmv%notfound THEN
561 CLOSE cvmv;
562 ROLLBACK TO version_contract_pvt;
563 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
564 FND_MESSAGE.set_name('OKE','OKE_VERS_INVALID_CONTRACT');
565 FND_MSG_PUB.add;
566 END IF;
567 x_return_status := FND_API.G_RET_STS_ERROR;
568 RAISE FND_API.G_EXC_ERROR;
569 END IF;
570 CLOSE cvmv;
571
572 progress := 2;
573
574 x_prev_vers := okc_cvmv_rec_in.major_version;
575
576 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
577 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Previous Version = ' || x_prev_vers);
578 END IF;
579
580 --
581 -- Version OKC side of contract components
582 --
583 OKC_VERSION_PVT.Version_Contract
584 ( p_api_version => p_api_version
585 , p_commit => FND_API.G_FALSE
586 , p_init_msg_list => p_init_msg_list
587 , x_return_status => l_return_status
588 , x_msg_count => x_msg_count
589 , x_msg_data => x_msg_data
590 , p_cvmv_rec => okc_cvmv_rec_in
591 , x_cvmv_rec => okc_cvmv_rec_out );
592 --
593 -- If anything happens, abort API
594 --
595
596 progress := 3;
597
598 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
599 ROLLBACK TO version_contract_pvt;
600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
602 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
603 ROLLBACK TO version_contract_pvt;
604 x_return_status := FND_API.G_RET_STS_ERROR;
605 RAISE FND_API.G_EXC_ERROR;
606 END IF;
607
608 progress := 4;
609
610 x_new_vers := okc_cvmv_rec_out.major_version;
611
612 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
613 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'New Version = ' || x_new_vers);
614 END IF;
615
616 --
617 -- Load OKE Work List
618 --
619 Load_Version_List;
620
621 progress := 5;
622
623 --
624 -- Loop through work list
625 --
626 i := WorkList.FIRST;
627 loop
628 TimeStamp('Versioning ' || WorkList(i).TableName);
629 version_table( p_chr_id
630 , WorkList(i).TableName
631 , WorkList(i).HistTableName
632 , WorkList(i).WhereClause
633 , x_new_vers );
634 exit when ( i = ListCount );
635 i := WorkList.NEXT(i);
636 end loop;
637
638
639 progress := 6;
640
641 --
642 -- Insert to OKE_K_VERS_NUMBERS_H
643 --
644
645 insert into OKE_K_VERS_NUMBERS_H
646 (K_HEADER_ID
647 ,MAJOR_VERSION
648 ,CREATION_DATE
649 ,CREATED_BY
650 ,LAST_UPDATE_DATE
651 ,LAST_UPDATED_BY
652 ,LAST_UPDATE_LOGIN
653 ,VERSION_REASON_CODE
654 ,CHG_REQUEST_ID)
655 values
656 (p_chr_id
657 ,x_new_vers
658 ,sysdate
659 ,fnd_global.user_id
660 ,sysdate
661 ,fnd_global.user_id
662 ,fnd_global.login_id
663 ,p_version_reason_code
664 ,p_chg_request_id
665 );
666
667 progress := 7;
668
669 --
670 -- Standard commit check
671 --
672 IF FND_API.TO_BOOLEAN( p_commit ) THEN
673 COMMIT;
674 END IF;
675
676 progress := 8;
677
678 --
679 -- Standard call to get message count and if count is 1, get message
680 -- info
681 --
682 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
683 , p_data => x_msg_data );
684
685 TimeStamp('version_contract() >>');
686
687 EXCEPTION
688 WHEN FND_API.G_EXC_ERROR THEN
689 ROLLBACK TO version_contract_pvt;
690 x_return_status := FND_API.G_RET_STS_ERROR;
691 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
692 , p_data => x_msg_data );
693
694 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
695 ROLLBACK TO version_contract_pvt;
696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
698 , p_data => x_msg_data );
699
700 WHEN OTHERS THEN
701 ROLLBACK TO version_contract_pvt;
702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
703 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
704 FND_MSG_PUB.add_exc_msg
705 ( p_pkg_name => G_PKG_NAME
706 , p_procedure_name => 'VERSION_CONTRACT(' || progress || ')');
707
708 END IF;
709 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
710 , p_data => x_msg_data );
711
712
713 END version_contract;
714
715
716 PROCEDURE restore_contract_version
717 ( p_api_version IN NUMBER
718 , p_commit IN VARCHAR2 := FND_API.G_FALSE
719 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
720 , x_msg_count OUT NOCOPY NUMBER
721 , x_msg_data OUT NOCOPY VARCHAR2
722 , x_return_status OUT NOCOPY VARCHAR2
723 , p_chr_id IN NUMBER
724 , p_rstr_from_ver IN NUMBER
725 , p_chg_request_id IN NUMBER
726 , p_version_reason_code IN VARCHAR2
727 , x_new_vers OUT NOCOPY NUMBER
728 ) IS
729
730 i NUMBER;
731 l_orig_vers_num NUMBER;
732 l_new_vers_num NUMBER;
733 l_return_status VARCHAR2(1);
734 l_source NUMBER;
735 l_alloc NUMBER;
736 l_flag VARCHAR2(1);
737 l_flag_h VARCHAR2(1);
738 l_counter1 NUMBER;
739 l_counter2 NUMBER;
740 l_counter3 NUMBER;
741
742 p_funding_in_rec OKE_FUNDING_PUB.FUNDING_REC_IN_TYPE ;
743 x_funding_out_rec OKE_FUNDING_PUB.FUNDING_REC_OUT_TYPE ;
744 p_allocation_in_tbl OKE_FUNDING_PUB.ALLOCATION_IN_TBL_TYPE ;
745 p_allocation_in_tbl2 OKE_FUNDING_PUB.ALLOCATION_IN_TBL_TYPE ;
746 x_allocation_out_tbl OKE_FUNDING_PUB.ALLOCATION_OUT_TBL_TYPE ;
747 l_agreement_type VARCHAR2(30) := OKE_API.G_MISS_CHAR ;
748
749
750
751 BEGIN
752
753 TimeStamp('restore_contract_version() <<');
754
755 --
756 -- Standard Start of API savepoint
757 --
758 SAVEPOINT rstr_contract_version_pvt;
759
760 --
761 -- Set API return status to success
762 --
763 x_return_status := FND_API.G_RET_STS_SUCCESS;
764 l_return_status := FND_API.G_RET_STS_SUCCESS;
765
766 --
767 -- Check API incompatibility
768 --
769
770 --
771 -- Initialize the message table if requested.
772 --
773 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
774 FND_MSG_PUB.initialize;
775 END IF;
776 /* bug#4451971
777 --
778 -- First version the current state of the contract
779 --
780 version_contract( p_api_version => p_api_version
781 , p_commit => FND_API.G_FALSE
782 , p_init_msg_list => FND_API.G_FALSE
783 , x_msg_count => x_msg_count
784 , x_msg_data => x_msg_data
785 , x_return_status => l_return_status
786 , p_chr_id => p_chr_id
787 , p_chg_request_id => p_chg_request_id
788 , p_version_reason_code => p_version_reason_code
789 , x_prev_vers => l_orig_vers_num
790 , x_new_vers => l_new_vers_num
791 );
792
793 --
794 -- If anything happens, abort API
795 --
796 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
797 ROLLBACK TO rstr_contract_version_pvt;
798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
800 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
801 ROLLBACK TO rstr_contract_version_pvt;
802 x_return_status := FND_API.G_RET_STS_ERROR;
803 RAISE FND_API.G_EXC_ERROR;
804 END IF;
805
806 x_new_vers := l_new_vers_num;
807 end of bug#4451971 */
808 x_new_vers := l_orig_vers_num;
809
810 --
811 -- Load Restore Work List
812 --
813 -- Load_Restore_List;
814
815 --
816 -- Loop through work list
817 --
818 /*
819 i := WorkList.FIRST;
820 loop
821 TimeStamp('Restoring ' || WorkList(i).TableName);
822 restore_table( p_api_version
823 , p_commit
824 , p_init_msg_list
825 , x_msg_count
826 , x_msg_data
827 , x_return_status
828 , p_chr_id
829 , WorkList(i).TableName
830 , WorkList(i).HistTableName
831 , WorkList(i).WhereClause
832 , p_rstr_from_ver );
833 exit when ( i = ListCount );
834 i := WorkList.NEXT(i);
835 end loop;
836
837 */
838
839
840 --
841 -- Standard commit check
842 --
843 IF FND_API.TO_BOOLEAN( p_commit ) THEN
844 COMMIT;
845 END IF;
846
847 --
848 -- Standard call to get message count and if count is 1, get message
849 -- info
850 --
851 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
852 , p_data => x_msg_data );
853
854 TimeStamp('restore_contract_version() >>');
855
856 EXCEPTION
857 WHEN FND_API.G_EXC_ERROR THEN
858 ROLLBACK TO rstr_contract_version_pvt;
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
861 , p_data => x_msg_data );
862
863 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
864 ROLLBACK TO rstr_contract_version_pvt;
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
866 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
867 , p_data => x_msg_data );
868
869 WHEN OTHERS THEN
870 ROLLBACK TO rstr_contract_version_pvt;
871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
872 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
873 FND_MSG_PUB.add_exc_msg
874 ( p_pkg_name => G_PKG_NAME
875 , p_procedure_name => 'RESTORE_CONTRACT_VERSION' );
876
877 END IF;
878 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
879 , p_data => x_msg_data );
880
881
882 END restore_contract_version;
883
884 END oke_version_pvt;