[Home] [Help]
PACKAGE BODY: APPS.ASG_CUSTOM_PVT
Source
1 PACKAGE BODY ASG_CUSTOM_PVT as
2 /* $Header: asgvcstb.pls 120.3.12020000.3 2012/12/10 10:13:17 avitiwar ship $ */
3
4 /** CONSTANTS */
5
6 LOG_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 -- LOG_LEVEL CONSTANT NUMBER := 6;
8 B_TAB_NAME VARCHAR2(30);
9 COL_TYPE VARCHAR2(30);
10 LAST_COL_NUM NUMBER;
11 IS_BLOB_INCLUDED VARCHAR2(1);
12 LAST_COL_NAME VARCHAR2(30);
13 APPS_SCHEMA_NAME varchar2(4) := 'APPS';
14 -- ***********************************************
15 -- procedure Customize_pub_item
16 -- we assume the default pub item piv created
17 -- per standard as follows:
18 -- For example, pub item name :sample_custom
19 -- base table name : sample_custom
20 -- acc table name : sample_custom_acc
21 -- base table name : having 30 columns
22 -- After customization:
23 -- 1: save the base table info under parent_table column.
24 -- 2: base_table_name changed to p_base_table_name
25 -- 3. customer should populate acc table using sample_custom_acc
26 -- 4. acc table structure :
27 -- access_id, user_id, attribute1,..attributek
28 -- attribute1 to attributek constitutes the PK columns.
29 -- ***********************************************
30
31 PROCEDURE customize_pub_item
32 (
33 p_api_version_number IN NUMBER,
34 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
35 p_pub_item_name IN VARCHAR2,
36 p_base_table_name IN VARCHAR2,
37 p_primary_key_columns IN VARCHAR2,
38 p_data_columns IN VARCHAR2,
39 p_additional_filter IN VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_error_message OUT NOCOPY VARCHAR2
43 )
44 IS
45 l_api_name CONSTANT VARCHAR2(30) := 'Customize_pub_item';
46 l_api_version_number CONSTANT NUMBER := 1.0;
47 l_table_exists NUMBER := 0;
48 l_piv_name Varchar2(30);
49 l_base_table_name varchar2(30);
50 l_acc_table_name varchar2(30);
51 l_column varchar2(30);
52 l_pk_num number;
53 l_pk_columns varchar2(2000);
54 l_col_num PLS_INTEGER;
55 l_pk_length PLS_INTEGER;
56 curr_pos PLS_INTEGER :=1;
57 l_pk_list varchar2(2000);
58 l_count PLS_INTEGER;
59 l_new_query varchar2(4000);
60 l_parent_table varchar2(30);
61 l_custom_flag varchar2(1);
62 l_cols_count number;
63 l_acc_count number;
64 l_cmd varchar2(2000);
65 c_type varchar2(30);
66 BEGIN
67
68 B_TAB_NAME := p_base_table_name;
69 log ('base table name = '||b_tab_name);
70
71 -- Standard Start of API savepoint
72 SAVEPOINT customize_pub_item_PVT;
73 -- Standard call to check for call compatibility.
74 if NOT FND_API.Compatible_API_Call
75 (
76 l_api_version_number,
77 p_api_version_number,
78 l_api_name,
79 G_PKG_NAME
80 )
81 then
82 raise FND_API.G_EXC_UNEXPECTED_ERROR;
83 end if;
84
85 -- Initialize message list if p_init_msg_list is set to TRUE.
86 if FND_API.to_Boolean( p_init_msg_list)
87 then
88 fND_MSG_PUB.initialize;
89 end if;
90 -- Initialize API return status to success
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92
93 -- ***************************
94 -- A few items to check for
95 -- 0. check if the item is customable
96 -- 1. input p_base_table_name exists
97 -- 2. p_primary_key_columns are matched with the pub item
98 -- 3. check if this pub item is previously customed
99
100 -- Any other parameters will be stored as NULL
101 -- when they are passed in with no values
102 -- ***************************
103
104 -- ****************
105 -- p_base_table_name check
106 -- ****************
107 /*
108 if p_base_table_name is NOT NULL
109 then
110 select count(object_name)
111 into l_table_exists
112 from all_objects
113 where object_name = p_base_table_name
114 and object_type in ('TABLE','VIEW');
115
116 if l_table_exists = 0
117 then
118 -- this table does not exist
119 log(p_base_table_name || ' does not exists!');
120 fnd_message.set_name('ASG','TABLE_NOT EXISTS');
121 fnd_msg_pub.Add;
122 raise FND_API.G_EXC_ERROR;
123 end if;
124 else
125 */
126 if (p_base_table_name is NULL)
127 then
128 log('p_base_table_name Should not be NULL!');
129 fnd_message.set_name('ASG','NO BASE TABLE NAME');
130 fnd_msg_pub.Add;
131 raise FND_API.G_EXC_ERROR;
132 end if;
133
134
135 -- ****************
136 -- check if the pub item is ready to custom
137 -- ****************
138 select nvl(custom, 'N')
139 into l_custom_flag
140 from asg_pub
141 where pub_id = ( select pub_name from asg_pub_item
142 where item_id = p_pub_item_name);
143 if (l_custom_flag <> 'Y')
144 then
145 -- the custom flag is not Y
146 log(p_pub_item_name || ' is not for the customization!');
147 fnd_message.set_name('ASG','PUB ITEM NOT CUSTOMABLE');
148 fnd_msg_pub.Add;
149 raise FND_API.G_EXC_ERROR;
150 end if;
151
152 -- *****************
153 -- check if primary_key_column input is null
154 -- ****************
155 if p_primary_key_columns is NULL
156 then
157 log( 'p_primary_key_columns Should not be NULL!');
158 fnd_message.set_name('ASG','NULL PK COLUMNS');
159 fnd_msg_pub.Add;
160 raise FND_API.G_EXC_ERROR;
161 end if;
162
163 -- ******************
164 -- check if the pub item is the right one to do this
165 -- only if the pk columns matches with the custom one
166 -- *****************
167
168
169 select base_object_name ,primary_key_column, access_name, parent_table
170 into l_piv_name, l_pk_columns, l_acc_table_name,l_parent_table
171 from asg_pub_item
172 where item_id = p_pub_item_name;
173
174 l_pk_num := find_num_pkcols(l_pk_columns);
175 l_col_num := find_num_pkcols(p_data_columns);
176 LAST_COL_NUM := l_pk_num + l_col_num;
177 if (l_pk_num <> find_num_pkcols(p_primary_key_columns))
178 then
179 log (' The Primary Keys number does not match with the pub item');
180 fnd_message.set_name('ASG','PK number _NOT matched');
181 fnd_msg_pub.Add;
182 raise FND_API.G_EXC_ERROR;
183 end if;
184
185
186 -- ***********
187 -- check if the pub item is customed already
188 -- *********
189 l_cmd := 'select count(*) from '||l_acc_table_name;
190
191
192 if (l_parent_table is not null)
193 then
194 execute immediate l_cmd into l_acc_count;
195 if (l_parent_table <> p_base_table_name AND l_acc_count <> 0)
196 then
197 log (' The pub item base table cannot be changed while there is
198 record existing in the acc table!');
199 fnd_message.set_name('ASG','RECORDS EXISTING IN ACC TABLE');
200 fnd_msg_pub.Add;
201 raise FND_API.G_EXC_ERROR;
202 END IF;
203 END IF;
204
205
206
207 -- *******************
208 -- recreate PIV
209 -- *******************
210
211 l_new_query := ' select acc.access_id ';
212 log(l_new_query);
213 l_new_query := l_new_query ||generate_query(p_primary_key_columns,
214 1);
215 log(l_new_query);
216 l_new_query := l_new_query ||generate_query(p_data_columns,l_pk_num+1);
217
218 -- get the number of the columns in piv
219
220 /* commented for bug 15932294
221 select count(*) into l_cols_count
222 from all_tab_columns
223 where table_name = l_piv_name
224 and owner=APPS_SCHEMA_NAME;
225 */
226
227 /* new definition bug 15932294 */
228 SELECT count(*) into l_cols_count
229 FROM all_synonyms syn, dba_tab_columns col
230 WHERE syn.table_owner = APPS_SCHEMA_NAME
231 AND syn.synonym_name = l_piv_name
232 AND col.owner = syn.table_owner AND col.table_name = syn.table_name;
233
234 -- and owner = (select owner
235 -- from all_tab_columns
236 -- where table_name = l_piv_name
237 -- and rownum=1);
238
239 log('total number of the column in the base view:'||to_char(l_cols_count));
240
241 if (IS_BLOB_INCLUDED <>'Y') THEN
242 if (l_pk_num + l_col_num < l_cols_count -1 ) then
243 l_count := l_pk_num + l_col_num;
244 while (l_count < l_cols_count -1 ) LOOP
245 l_new_query := l_new_query ||', NULL ATTRIBUTE'
246 ||to_char(l_count+1);
247 l_count := l_count + 1;
248
249 END LOOP;
250 end if;
251 Else
252 -- IF (IS_BLOB_INCLUDED ='Y') THEN
253 -- check to make sure the input put item is the right one
254
255 /* commented for bug 15932294
256 select data_type into c_type
257 from all_tab_columns
258 where table_name = l_piv_name
259 and column_name = 'ATTRIBUTE'||to_char(l_cols_count-1)
260 and rownum = 1 and owner=APPS_SCHEMA_NAME;
261 */
262
263 /* new definition bug 15932294 */
264 SELECT data_type into c_type
265 FROM all_synonyms syn, dba_tab_columns col
266 WHERE syn.table_owner = APPS_SCHEMA_NAME
267 AND syn.synonym_name = l_piv_name AND col.owner = syn.table_owner
268 AND col.table_name = syn.table_name
269 AND col.column_name = 'ATTRIBUTE'||to_char(l_cols_count-1)
270 AND rownum = 1;
271
272
273 if (c_type <> 'BLOB') then
274 log('The publication item does not contain the BLOB column.'||
275 ' Please chose the right'
276 ||' publication item to custom.');
277 raise FND_API.G_EXC_ERROR;
278 end if;
279
280 if (l_pk_num + l_col_num < l_cols_count -1 ) then
281 l_count := l_pk_num + l_col_num;
282 while (l_count < l_cols_count -1 ) LOOP
283 l_new_query := l_new_query ||', NULL ATTRIBUTE'
284 ||to_char(l_count);
285 l_count := l_count + 1;
286
287 END LOOP;
288 l_new_query := l_new_query ||','||LAST_COL_NAME||' ATTRIBUTE'
289 ||to_char(l_count);
290 else
291 -- the last col actually has not included yet
292 l_new_query := l_new_query ||','||LAST_COL_NAME||' ATTRIBUTE'
293 ||to_char(l_cols_count-1);
294 end if;
295
296 END IF;
297
298 l_new_query := l_new_query || ' from '||p_base_table_name||', '||
299 l_acc_table_name||' acc ';
300 log(l_new_query);
301 l_new_query := l_new_query ||
302 ' where acc.user_id = asg_base.get_user_id '||
303 generate_where (p_primary_key_columns);
304
305 if p_additional_filter is NOT NULL
306 then
307 l_new_query := l_new_query || ' and '||p_additional_filter;
308 end if ;
309
310 l_new_query := 'create or replace view '||l_piv_name ||
311 ' as '||l_new_query;
312
313 log ('Recreating the PIV view .');
314 x_return_status := exec_cmd (l_new_query);
315 log ('Recreated the PIV view .');
316
317 -- **************
318 -- update asg_pub_item
319 -- **************
320 log('update the asg_pub_item table.');
321 update asg_pub_item
322 set parent_table = p_base_table_name,
323 last_update_date = sysdate
324 where item_id = P_pub_item_name;
325 commit;
326
327 EXCEPTION
328 WHEN FND_API.G_EXC_ERROR THEN
329 Rollback to customize_pub_item_PVT;
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 FND_MSG_PUB.Count_And_Get
332 (
333 p_count => x_msg_count,
334 p_data => x_error_message
335 );
336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
337 Rollback to customize_pub_item_PVT;
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339 FND_MSG_PUB.Count_And_Get
340 (
341 p_count => x_msg_count,
342 p_data => x_error_message
343 );
344 WHEN OTHERS THEN
345 Rollback to customize_pub_item_PVT;
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
348 then
349 FND_MSG_PUB.Add_Exc_Msg
350 (
351 G_PKG_NAME,
352 l_api_name,
353 sqlerrm
354 );
355 end if;
356 FND_MSG_PUB.Count_And_Get
357 (
358 p_count => x_msg_count,
359 p_data => x_error_message
360 );
364
361
362
363 END customize_pub_item ;
365 FUNCTION exec_cmd (pCmd in varchar2)
366 RETURN varchar2
367 IS
368 begin
369 log (pCmd);
370 execute immediate pCmd;
371
372 RETURN FND_API.G_RET_STS_SUCCESS;
373 EXCEPTION
374 when OTHERS then
375 fnd_message.set_name('ASG','SQL COMMAND FAIL!');
376 fnd_msg_pub.Add;
377 log ( substr(sqlerrm, 1, 200));
378 Raise;
379 RETURN FND_API.G_RET_STS_ERROR;
380 end;
381
382 FUNCTION generate_where (collist in varchar2)
383 RETURN VARCHAR2
384 IS
385
386 l_col varchar2(30);
387 l_new_query varchar2(2000) := '';
388 l_collist varchar2(2000);
389 l_num PLS_INTEGER := 1;
390 l_type_col varchar2(2000);
391 BEGIN
392
393 l_collist := collist;
394 while ( instr(l_collist, ',',1) <> 0) LOOP
395 l_col := substr(l_collist, 1, instr(l_collist,',')-1);
396 l_col := ltrim(l_col,' ');
397 l_col := rtrim(l_col,' ');
398 l_type_col := get_col(l_col);
399 l_new_query := l_new_query ||
400 ' and '||l_type_col||
401 ' = acc.ATTRIBUTE'||to_char(l_num);
402
403 l_collist := substr(l_collist, instr(l_collist,',')+1);
404 l_num := l_num +1;
405 END LOOP;
406 l_collist := ltrim(l_collist, ' ');
407 l_collist := rtrim(l_collist, ' ');
408 if (length(l_collist) <> 0)
409 then
410 l_type_col := get_col(l_collist);
411 l_new_query := l_new_query ||
412 ' and '||l_type_col||
413 ' = acc.ATTRIBUTE'||to_char(l_num);
414 END IF;
415 log('generate where clause: '||l_new_query);
416 return l_new_query;
417
418 END;
419
420 FUNCTION get_col (col varchar2)
421 RETURN VARCHAR2
422 IS
423 l_type varchar2(30);
424 l_col varchar2(2000);
425
426 BEGIN
427
428 /* commented for bug 15932294
429 select data_type into l_type
430 from all_tab_columns
431 where table_name = B_TAB_NAME
432 and column_name = col
433 and rownum = 1 and owner=APPS_SCHEMA_NAME;
434 */
435
436 /* new definition bug 15932294 */
437 SELECT data_type into l_type
438 FROM all_synonyms syn, dba_tab_columns dba_cols
439 WHERE syn.table_owner = APPS_SCHEMA_NAME
440 AND syn.synonym_name = B_TAB_NAME AND dba_cols.owner = syn.table_owner
441 AND dba_cols.table_name = syn.table_name AND dba_cols.column_name = col
442 AND rownum = 1 ;
443
444 COL_TYPE := l_type;
445 if (l_type = 'NUMBER' ) then
446 l_col := 'to_char('||B_TAB_NAME||'.'||col||
447 ') ';
448 elsif (l_type = 'DATE') then
449 l_col :=
450 'to_char('||B_TAB_NAME||'.'||col||
451 ', ''dd-mm-yyyy hh24:mi:ss'')';
452 else
453 l_col :=
454 B_TAB_NAME||'.'||col;
455 end if;
456 log (l_col);
457 return l_col;
458 END;
459
460
461 FUNCTION generate_query (collist in varchar2, cnt in number)
462 RETURN VARCHAR2
463 IS
464 l_CNT PLS_INTEGER ;
465 l_col varchar2(30);
466 l_new_query varchar2(2000):= ' ';
467 l_collist varchar2(2000);
468 l_type_col varchar2(2000);
469 l_dml varchar2(2000);
470 l_tmp varchar2(2000);
471
472 BEGIN
473 l_cnt := cnt;
474 l_collist := collist;
475 log(collist);
476
477 while ( instr(l_collist,fnd_global.local_chr(10),1)<>0) LOOP
478 l_collist := substr(l_collist, 1, instr(l_collist,fnd_global.local_chr(10))-1)
479 ||substr(l_collist, instr(l_collist,fnd_global.local_chr(10))+1);
480 end loop;
481
482 while ( instr(l_collist, ',',1) <> 0) LOOP
483 l_col := substr(l_collist, 1, instr(l_collist,',')-1);
484 log('col:'||l_col);
485 l_col := ltrim(l_col,' ');
486 l_col := rtrim(l_col,' ');
487
488 l_type_col := get_col(l_col);
489
490 IF (COL_TYPE <> 'BLOB') THEN
491 l_new_query := l_new_query ||
492 ', '||l_type_col
493 ||' attribute'
494 ||to_char(l_cnt);
495 ELSIF ((l_cnt <> LAST_COL_NUM ) AND (COL_TYPE = 'BLOB') ) THEN
496 log('Exception: BLOB type Column must be the last column!');
497 RAISE FND_API.G_EXC_ERROR;
498 ELSIF ((l_cnt = LAST_COL_NUM ) AND (COL_TYPE = 'BLOB') ) THEN
499 LAST_COL_NAME := l_type_col;
500 IS_BLOB_INCLUDED := 'Y';
501 END IF;
502
503
504 l_collist := substr(l_collist, instr(l_collist,',')+1);
505 l_cnt := l_cnt +1;
506 END LOOP;
507 l_collist := ltrim(l_collist, ' ');
508 l_collist := rtrim(l_collist, ' ');
509
510 if (length(l_collist)<> 0)
511 then
512 l_type_col := get_col(l_collist);
513 IF (COL_TYPE <> 'BLOB') THEN
514 l_new_query := l_new_query ||
515 ', '||l_type_col
516 ||' attribute'
517 ||to_char(l_cnt);
518 ELSIF ((l_cnt <> LAST_COL_NUM ) AND (COL_TYPE = 'BLOB') ) THEN
519 log('Exception: BLOB type Column must be the last column!');
520 RAISE FND_API.G_EXC_ERROR;
521 ELSIF ((l_cnt = LAST_COL_NUM ) AND (COL_TYPE = 'BLOB') ) THEN
522 IS_BLOB_INCLUDED := 'Y';
523 LAST_COL_NAME := l_type_col;
524 END IF;
525 END IF;
526 log ('generate the query: '||l_new_query);
527 return l_new_query;
528
529 END;
530
531 FUNCTION find_num_pkcols (pkcolumns in varchar2)
532 RETURN number
533 IS
534 num_pkcols PLS_INTEGER :=1;
535 pkcol_length PLS_INTEGER;
536 curr_position PLS_INTEGER :=1;
537 BEGIN
538 pkcol_length := length(pkcolumns);
539 IF pkcol_length = 0 THEN
543 curr_position := INSTR(pkcolumns, ',', curr_position);
540 RETURN 0;
541 END IF;
542 WHILE curr_position < pkcol_length LOOP
544 IF curr_position = 0 THEN
545 EXIT;
546 ELSE
547 curr_position := curr_position + 1;
548 num_pkcols := num_pkcols +1;
549 END IF;
550 END LOOP;
551
552 RETURN num_pkcols;
553 END find_num_pkcols;
554
555 -- make sure to check the boolean to return varchar2
556 PROCEDURE mark_dirty (
557 p_api_version_number IN NUMBER,
558 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
559 p_pub_item IN VARCHAR2,
560 p_accessList IN asg_download.access_list,
561 p_userid_list IN asg_download.user_list,
562 p_dmlList IN asg_download.dml_list,
563 p_timestamp IN DATE,
564 x_return_status OUT NOCOPY VARCHAR2
565 )
566 IS
567
568 l_api_version_number CONSTANT NUMBER := 1.0;
569 l_api_name CONSTANT VARCHAR2(30) := 'MARK_DIRTY1' ;
570 l_ret boolean;
571
572 BEGIN
573 -- Standard Start of API savepoint
574 SAVEPOINT mark_dirty1;
575
576 -- Standard call to check for call compatibility.
577 if NOT FND_API.Compatible_API_Call
578 (
579 l_api_version_number,
580 p_api_version_number,
581 l_api_name,
582 G_PKG_NAME
583 )
584 then
585 raise FND_API.G_EXC_UNEXPECTED_ERROR;
586 end if;
587
588 -- Initialize message list if p_init_msg_list is set to TRUE.
589 if FND_API.to_Boolean( p_init_msg_list)
590 then
591 FND_MSG_PUB.initialize;
592 end if;
593
594 -- Initialize API return status to success
595 x_return_status := FND_API.G_RET_STS_SUCCESS;
596
597 l_ret := asg_download.mark_dirty(p_pub_item,
598 p_accessList,
599 p_userid_list,
600 p_dmlList,
601 p_timestamp);
602 if (l_ret = TRUE )
603 then
604 x_return_status := FND_API.G_RET_STS_SUCCESS;
605 else
606 x_return_status := FND_API.G_RET_STS_ERROR;
607 raise FND_API.G_EXC_UNEXPECTED_ERROR;
608 END IF;
609
610 EXCEPTION
611 WHEN FND_API.G_EXC_ERROR THEN
612 Rollback to mark_dirty1;
613 x_return_status := FND_API.G_RET_STS_ERROR;
614
615 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
616 Rollback to mark_dirty1;
617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
618
619 WHEN OTHERS THEN
620 Rollback to mark_dirty1;
621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
622
623 END ;
624
625 PROCEDURE mark_dirty (
626 p_api_version_number IN NUMBER,
627 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
628 p_pub_item IN VARCHAR2,
629 p_accessList IN asg_download.access_list,
630 p_userid_list IN asg_download.user_list,
631 p_dml_type IN CHAR,
632 p_timestamp IN DATE,
633 x_return_status OUT NOCOPY VARCHAR2
634 )
635 IS
636
637 l_api_version_number CONSTANT NUMBER := 1.0;
638 l_api_name CONSTANT VARCHAR2(30) := 'MARK_DIRTY2' ;
639 l_ret boolean;
640
641 BEGIN
642 -- Standard Start of API savepoint
643 SAVEPOINT mark_dirty2;
644
645 -- Standard call to check for call compatibility.
646 if NOT FND_API.Compatible_API_Call
647 (
648 l_api_version_number,
649 p_api_version_number,
650 l_api_name,
651 G_PKG_NAME
652 )
653 then
654 raise FND_API.G_EXC_UNEXPECTED_ERROR;
655 end if;
656
657 -- Initialize message list if p_init_msg_list is set to TRUE.
658 if FND_API.to_Boolean( p_init_msg_list)
659 then
660 FND_MSG_PUB.initialize;
661 end if;
662 -- Initialize API return status to success
663 x_return_status := FND_API.G_RET_STS_SUCCESS;
664
665 l_ret := asg_download.mark_dirty(p_pub_item,
666 p_accessList,
667 p_userid_list,
668 p_dml_type,
669 p_timestamp);
670 if (l_ret = TRUE )
671 then
672 x_return_status := FND_API.G_RET_STS_SUCCESS;
673 else
674 x_return_status := FND_API.G_RET_STS_ERROR;
675 END IF;
676
677
678 EXCEPTION
679 WHEN FND_API.G_EXC_ERROR THEN
680 Rollback to mark_dirty2;
681 x_return_status := FND_API.G_RET_STS_ERROR;
682
683 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
684 Rollback to mark_dirty2;
685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
686
687 WHEN OTHERS THEN
688 Rollback to mark_dirty2;
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
690
691 END ;
692
693
694
695 PROCEDURE mark_dirty (
696 p_api_version_number IN NUMBER,
697 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
698 p_pub_item IN VARCHAR2,
699 p_accessid IN NUMBER,
700 p_userid IN NUMBER,
701 p_dml IN CHAR,
702 p_timestamp IN DATE,
706 IS
703 x_return_status OUT NOCOPY VARCHAR2
704 )
705
707 l_api_version_number CONSTANT NUMBER := 1.0;
708 l_api_name CONSTANT VARCHAR2(30) := 'MARK_DIRTY3';
709 l_ret boolean;
710
711 BEGIN
712 -- Standard Start of API savepoint
713 SAVEPOINT mark_dirty3;
714
715 -- Standard call to check for call compatibility.
716 if NOT FND_API.Compatible_API_Call
717 (
718 l_api_version_number,
719 p_api_version_number,
720 l_api_name,
721 G_PKG_NAME
722 )
723 then
724 raise FND_API.G_EXC_UNEXPECTED_ERROR;
725 end if;
726
727 -- Initialize message list if p_init_msg_list is set to TRUE.
728 if FND_API.to_Boolean( p_init_msg_list)
729 then
730 FND_MSG_PUB.initialize;
731 end if;
732
733 -- Initialize API return status to success
734 x_return_status := FND_API.G_RET_STS_SUCCESS;
735
736 l_ret := asg_download.mark_dirty(p_pub_item,
737 p_accessid,
738 p_userid,
739 p_dml,
740 p_timestamp);
741 if (l_ret = TRUE )
742 then
743 x_return_status := FND_API.G_RET_STS_SUCCESS;
744 else
745 x_return_status := FND_API.G_RET_STS_ERROR;
746 END IF;
747
748
749 EXCEPTION
750 WHEN FND_API.G_EXC_ERROR THEN
751 Rollback to mark_dirty3;
752 x_return_status := FND_API.G_RET_STS_ERROR;
753
754 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
755 Rollback to mark_dirty3;
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757
758 WHEN OTHERS THEN
759 Rollback to mark_dirty3;
760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
761
762 END ;
763
764
765 PROCEDURE mark_dirty (
766 p_api_version_number IN NUMBER,
767 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
768 p_pub_item IN VARCHAR2,
769 p_accessid IN NUMBER,
770 p_userid IN NUMBER,
771 p_dml IN CHAR,
772 p_timestamp IN DATE,
773 p_pkvalues IN asg_download.pk_list,
774 x_return_status OUT NOCOPY VARCHAR2
775 )
776
777 IS
778 l_api_version_number CONSTANT NUMBER := 1.0;
779 l_api_name CONSTANT VARCHAR2(30) := 'MARK_DIRTY4';
780 l_ret boolean;
781
782 BEGIN
783
784 -- Standard Start of API savepoint
785 SAVEPOINT mark_dirty4;
786
787 -- Standard call to check for call compatibility.
788 if NOT FND_API.Compatible_API_Call
789 (
790 l_api_version_number,
791 p_api_version_number,
792 l_api_name,
793 G_PKG_NAME
794 )
795 then
796 raise FND_API.G_EXC_UNEXPECTED_ERROR;
797 end if;
798
799 -- Initialize message list if p_init_msg_list is set to TRUE.
800 if FND_API.to_Boolean( p_init_msg_list)
801 then
802 FND_MSG_PUB.initialize;
803 end if;
804
805 -- Initialize API return status to success
806 x_return_status := FND_API.G_RET_STS_SUCCESS;
807
808 l_ret := asg_download.mark_dirty(p_pub_item,
809 p_accessid,
810 p_userid,
811 p_dml,
812 p_timestamp,
813 p_pkvalues);
814 if (l_ret = TRUE )
815 then
816 x_return_status := FND_API.G_RET_STS_SUCCESS;
817 else
818 x_return_status := FND_API.G_RET_STS_ERROR;
819 END IF;
820
821
822
823 EXCEPTION
824 WHEN FND_API.G_EXC_ERROR THEN
825 Rollback to mark_dirty4;
826 x_return_status := FND_API.G_RET_STS_ERROR;
827
828 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
829 Rollback to mark_dirty4;
830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
831
832 WHEN OTHERS THEN
833 Rollback to mark_dirty4;
834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
835
836 END ;
837
838
839 PROCEDURE mark_dirty (
840 p_api_version_number IN NUMBER,
841 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
842 p_pub_item IN VARCHAR2,
843 p_accessList IN asg_download.access_list,
844 p_userid_list IN asg_download.user_list,
845 p_dml_type IN CHAR,
846 p_timestamp IN DATE,
847 p_bulk_flag IN BOOLEAN,
848 x_return_status OUT NOCOPY VARCHAR2
849 ) IS
850 l_ret boolean;
851 l_api_version_number CONSTANT NUMBER := 1.0;
852 l_api_name CONSTANT VARCHAR2(30) := 'MARK_DIRTY5';
853 BEGIN
854 -- Standard Start of API savepoint
855 SAVEPOINT mark_dirty5;
856
857 -- Standard call to check for call compatibility.
858 if NOT FND_API.Compatible_API_Call
859 (
860 l_api_version_number,
861 p_api_version_number,
862 l_api_name,
863 G_PKG_NAME
864 )
865 then
866 raise FND_API.G_EXC_UNEXPECTED_ERROR;
867 end if;
868
869 -- Initialize message list if p_init_msg_list is set to TRUE.
870 if FND_API.to_Boolean( p_init_msg_list)
871 then
875 -- Initialize API return status to success
872 FND_MSG_PUB.initialize;
873 end if;
874
876 x_return_status := FND_API.G_RET_STS_SUCCESS;
877
878 l_ret := asg_download.mark_dirty(p_pub_item,
879 p_accessList,
880 p_userid_list,
881 p_dml_type,
882 p_timestamp,
883 p_bulk_flag);
884 if (l_ret = TRUE )
885 then
886 x_return_status := FND_API.G_RET_STS_SUCCESS;
887 else
888 x_return_status := FND_API.G_RET_STS_ERROR;
889 END IF;
890
891
892 EXCEPTION
893 WHEN FND_API.G_EXC_ERROR THEN
894 Rollback to mark_dirty5;
895 x_return_status := FND_API.G_RET_STS_ERROR;
896
897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
898 Rollback to mark_dirty5;
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
900
901 WHEN OTHERS THEN
902 Rollback to mark_dirty5;
903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
904
905 END ;
906
907
908 /* Log Routine */
909 PROCEDURE log (p_mesg VARCHAR2 ) IS
910 l_dml VARCHAR2(2000);
911
912 BEGIN
913 asg_helper.log(p_mesg, 'asg_custom_pvt', LOG_LEVEL);
914 END log;
915
916 END ASG_CUSTOM_PVT;