[Home] [Help]
PACKAGE BODY: APPS.ASG_CUSTOM_PVT
Source
1 PACKAGE BODY ASG_CUSTOM_PVT as
2 /* $Header: asgvcstb.pls 120.1 2005/08/12 02:59:09 saradhak noship $ */
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 select count(*) into l_cols_count
220 from all_tab_columns
221 where table_name = l_piv_name
222 and owner=APPS_SCHEMA_NAME;
223
224 -- and owner = (select owner
225 -- from all_tab_columns
226 -- where table_name = l_piv_name
227 -- and rownum=1);
228
229 log('total number of the column in the base view:'||to_char(l_cols_count));
230
231 if (IS_BLOB_INCLUDED <>'Y') THEN
232 if (l_pk_num + l_col_num < l_cols_count -1 ) then
233 l_count := l_pk_num + l_col_num;
234 while (l_count < l_cols_count -1 ) LOOP
235 l_new_query := l_new_query ||', NULL ATTRIBUTE'
236 ||to_char(l_count+1);
237 l_count := l_count + 1;
238
239 END LOOP;
240 end if;
241 Else
242 -- IF (IS_BLOB_INCLUDED ='Y') THEN
243 -- check to make sure the input put item is the right one
244 select data_type into c_type
245 from all_tab_columns
246 where table_name = l_piv_name
247 and column_name = 'ATTRIBUTE'||to_char(l_cols_count-1)
248 and rownum = 1 and owner=APPS_SCHEMA_NAME;
249 if (c_type <> 'BLOB') then
250 log('The publication item does not contain the BLOB column.'||
251 ' Please chose the right'
252 ||' publication item to custom.');
253 raise FND_API.G_EXC_ERROR;
254 end if;
255
256 if (l_pk_num + l_col_num < l_cols_count -1 ) then
257 l_count := l_pk_num + l_col_num;
258 while (l_count < l_cols_count -1 ) LOOP
259 l_new_query := l_new_query ||', NULL ATTRIBUTE'
260 ||to_char(l_count);
261 l_count := l_count + 1;
262
263 END LOOP;
264 l_new_query := l_new_query ||','||LAST_COL_NAME||' ATTRIBUTE'
265 ||to_char(l_count);
266 else
267 -- the last col actually has not included yet
268 l_new_query := l_new_query ||','||LAST_COL_NAME||' ATTRIBUTE'
269 ||to_char(l_cols_count-1);
270 end if;
271
272 END IF;
273
274 l_new_query := l_new_query || ' from '||p_base_table_name||', '||
275 l_acc_table_name||' acc ';
276 log(l_new_query);
277 l_new_query := l_new_query ||
278 ' where acc.user_id = asg_base.get_user_id '||
279 generate_where (p_primary_key_columns);
280
281 if p_additional_filter is NOT NULL
282 then
283 l_new_query := l_new_query || ' and '||p_additional_filter;
284 end if ;
285
286 l_new_query := 'create or replace view '||l_piv_name ||
287 ' as '||l_new_query;
288
289 log ('Recreating the PIV view .');
290 x_return_status := exec_cmd (l_new_query);
291 log ('Recreated the PIV view .');
292
293 -- **************
294 -- update asg_pub_item
295 -- **************
296 log('update the asg_pub_item table.');
297 update asg_pub_item
298 set parent_table = p_base_table_name,
299 last_update_date = sysdate
300 where item_id = P_pub_item_name;
301 commit;
302
303 EXCEPTION
304 WHEN FND_API.G_EXC_ERROR THEN
305 Rollback to customize_pub_item_PVT;
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 FND_MSG_PUB.Count_And_Get
308 (
309 p_count => x_msg_count,
310 p_data => x_error_message
311 );
312 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
313 Rollback to customize_pub_item_PVT;
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315 FND_MSG_PUB.Count_And_Get
316 (
317 p_count => x_msg_count,
318 p_data => x_error_message
319 );
320 WHEN OTHERS THEN
321 Rollback to customize_pub_item_PVT;
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
323 if FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
324 then
325 FND_MSG_PUB.Add_Exc_Msg
326 (
327 G_PKG_NAME,
328 l_api_name,
329 sqlerrm
330 );
331 end if;
332 FND_MSG_PUB.Count_And_Get
333 (
334 p_count => x_msg_count,
335 p_data => x_error_message
336 );
337
338
339 END customize_pub_item ;
340
341 FUNCTION exec_cmd (pCmd in varchar2)
342 RETURN varchar2
343 IS
344 begin
345 log (pCmd);
346 execute immediate pCmd;
347
348 RETURN FND_API.G_RET_STS_SUCCESS;
349 EXCEPTION
350 when OTHERS then
351 fnd_message.set_name('ASG','SQL COMMAND FAIL!');
352 fnd_msg_pub.Add;
353 log ( substr(sqlerrm, 1, 200));
354 Raise;
355 RETURN FND_API.G_RET_STS_ERROR;
356 end;
357
358 FUNCTION generate_where (collist in varchar2)
359 RETURN VARCHAR2
360 IS
361
362 l_col varchar2(30);
363 l_new_query varchar2(2000) := '';
364 l_collist varchar2(2000);
365 l_num PLS_INTEGER := 1;
366 l_type_col varchar2(2000);
367 BEGIN
368
369 l_collist := collist;
370 while ( instr(l_collist, ',',1) <> 0) LOOP
371 l_col := substr(l_collist, 1, instr(l_collist,',')-1);
372 l_col := ltrim(l_col,' ');
373 l_col := rtrim(l_col,' ');
377 ' = acc.ATTRIBUTE'||to_char(l_num);
374 l_type_col := get_col(l_col);
375 l_new_query := l_new_query ||
376 ' and '||l_type_col||
378
379 l_collist := substr(l_collist, instr(l_collist,',')+1);
380 l_num := l_num +1;
381 END LOOP;
382 l_collist := ltrim(l_collist, ' ');
383 l_collist := rtrim(l_collist, ' ');
384 if (length(l_collist) <> 0)
385 then
386 l_type_col := get_col(l_collist);
387 l_new_query := l_new_query ||
388 ' and '||l_type_col||
389 ' = acc.ATTRIBUTE'||to_char(l_num);
390 END IF;
391 log('generate where clause: '||l_new_query);
392 return l_new_query;
393
394 END;
395
396 FUNCTION get_col (col varchar2)
397 RETURN VARCHAR2
398 IS
399 l_type varchar2(30);
400 l_col varchar2(2000);
401
402 BEGIN
403 select data_type into l_type
404 from all_tab_columns
405 where table_name = B_TAB_NAME
406 and column_name = col
407 and rownum = 1 and owner=APPS_SCHEMA_NAME;
408
409 COL_TYPE := l_type;
410 if (l_type = 'NUMBER' ) then
411 l_col := 'to_char('||B_TAB_NAME||'.'||col||
412 ') ';
413 elsif (l_type = 'DATE') then
414 l_col :=
415 'to_char('||B_TAB_NAME||'.'||col||
416 ', ''dd-mm-yyyy hh24:mi:ss'')';
417 else
418 l_col :=
419 B_TAB_NAME||'.'||col;
420 end if;
421 log (l_col);
422 return l_col;
423 END;
424
425
426 FUNCTION generate_query (collist in varchar2, cnt in number)
427 RETURN VARCHAR2
428 IS
429 l_CNT PLS_INTEGER ;
430 l_col varchar2(30);
431 l_new_query varchar2(2000):= ' ';
432 l_collist varchar2(2000);
433 l_type_col varchar2(2000);
434 l_dml varchar2(2000);
435 l_tmp varchar2(2000);
436
437 BEGIN
438 l_cnt := cnt;
439 l_collist := collist;
440 log(collist);
441
442 while ( instr(l_collist,fnd_global.local_chr(10),1)<>0) LOOP
443 l_collist := substr(l_collist, 1, instr(l_collist,fnd_global.local_chr(10))-1)
444 ||substr(l_collist, instr(l_collist,fnd_global.local_chr(10))+1);
445 end loop;
446
447 while ( instr(l_collist, ',',1) <> 0) LOOP
448 l_col := substr(l_collist, 1, instr(l_collist,',')-1);
449 log('col:'||l_col);
450 l_col := ltrim(l_col,' ');
451 l_col := rtrim(l_col,' ');
452
453 l_type_col := get_col(l_col);
454
455 IF (COL_TYPE <> 'BLOB') THEN
456 l_new_query := l_new_query ||
457 ', '||l_type_col
458 ||' attribute'
459 ||to_char(l_cnt);
460 ELSIF ((l_cnt <> LAST_COL_NUM ) AND (COL_TYPE = 'BLOB') ) THEN
461 log('Exception: BLOB type Column must be the last column!');
462 RAISE FND_API.G_EXC_ERROR;
463 ELSIF ((l_cnt = LAST_COL_NUM ) AND (COL_TYPE = 'BLOB') ) THEN
464 LAST_COL_NAME := l_type_col;
465 IS_BLOB_INCLUDED := 'Y';
466 END IF;
467
468
469 l_collist := substr(l_collist, instr(l_collist,',')+1);
470 l_cnt := l_cnt +1;
471 END LOOP;
472 l_collist := ltrim(l_collist, ' ');
473 l_collist := rtrim(l_collist, ' ');
474
475 if (length(l_collist)<> 0)
476 then
477 l_type_col := get_col(l_collist);
478 IF (COL_TYPE <> 'BLOB') THEN
479 l_new_query := l_new_query ||
480 ', '||l_type_col
481 ||' attribute'
482 ||to_char(l_cnt);
483 ELSIF ((l_cnt <> LAST_COL_NUM ) AND (COL_TYPE = 'BLOB') ) THEN
484 log('Exception: BLOB type Column must be the last column!');
485 RAISE FND_API.G_EXC_ERROR;
486 ELSIF ((l_cnt = LAST_COL_NUM ) AND (COL_TYPE = 'BLOB') ) THEN
487 IS_BLOB_INCLUDED := 'Y';
488 LAST_COL_NAME := l_type_col;
489 END IF;
490 END IF;
491 log ('generate the query: '||l_new_query);
492 return l_new_query;
493
494 END;
495
496 FUNCTION find_num_pkcols (pkcolumns in varchar2)
497 RETURN number
498 IS
499 num_pkcols PLS_INTEGER :=1;
500 pkcol_length PLS_INTEGER;
501 curr_position PLS_INTEGER :=1;
502 BEGIN
503 pkcol_length := length(pkcolumns);
504 IF pkcol_length = 0 THEN
505 RETURN 0;
506 END IF;
507 WHILE curr_position < pkcol_length LOOP
508 curr_position := INSTR(pkcolumns, ',', curr_position);
509 IF curr_position = 0 THEN
510 EXIT;
511 ELSE
512 curr_position := curr_position + 1;
513 num_pkcols := num_pkcols +1;
514 END IF;
515 END LOOP;
516
517 RETURN num_pkcols;
518 END find_num_pkcols;
519
520 -- make sure to check the boolean to return varchar2
521 PROCEDURE mark_dirty (
522 p_api_version_number IN NUMBER,
523 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
524 p_pub_item IN VARCHAR2,
525 p_accessList IN asg_download.access_list,
526 p_userid_list IN asg_download.user_list,
527 p_dmlList IN asg_download.dml_list,
531 IS
528 p_timestamp IN DATE,
529 x_return_status OUT NOCOPY VARCHAR2
530 )
532
533 l_api_version_number CONSTANT NUMBER := 1.0;
534 l_api_name CONSTANT VARCHAR2(30) := 'MARK_DIRTY1' ;
535 l_ret boolean;
536
537 BEGIN
538 -- Standard Start of API savepoint
539 SAVEPOINT mark_dirty1;
540
541 -- Standard call to check for call compatibility.
542 if NOT FND_API.Compatible_API_Call
543 (
544 l_api_version_number,
545 p_api_version_number,
546 l_api_name,
547 G_PKG_NAME
548 )
549 then
550 raise FND_API.G_EXC_UNEXPECTED_ERROR;
551 end if;
552
553 -- Initialize message list if p_init_msg_list is set to TRUE.
554 if FND_API.to_Boolean( p_init_msg_list)
555 then
556 FND_MSG_PUB.initialize;
557 end if;
558
559 -- Initialize API return status to success
560 x_return_status := FND_API.G_RET_STS_SUCCESS;
561
562 l_ret := asg_download.mark_dirty(p_pub_item,
563 p_accessList,
564 p_userid_list,
565 p_dmlList,
566 p_timestamp);
567 if (l_ret = TRUE )
568 then
569 x_return_status := FND_API.G_RET_STS_SUCCESS;
570 else
571 x_return_status := FND_API.G_RET_STS_ERROR;
572 raise FND_API.G_EXC_UNEXPECTED_ERROR;
573 END IF;
574
575 EXCEPTION
576 WHEN FND_API.G_EXC_ERROR THEN
577 Rollback to mark_dirty1;
578 x_return_status := FND_API.G_RET_STS_ERROR;
579
580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
581 Rollback to mark_dirty1;
582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
583
584 WHEN OTHERS THEN
585 Rollback to mark_dirty1;
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
587
588 END ;
589
590 PROCEDURE mark_dirty (
591 p_api_version_number IN NUMBER,
592 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
593 p_pub_item IN VARCHAR2,
594 p_accessList IN asg_download.access_list,
595 p_userid_list IN asg_download.user_list,
596 p_dml_type IN CHAR,
597 p_timestamp IN DATE,
598 x_return_status OUT NOCOPY VARCHAR2
599 )
600 IS
601
602 l_api_version_number CONSTANT NUMBER := 1.0;
603 l_api_name CONSTANT VARCHAR2(30) := 'MARK_DIRTY2' ;
604 l_ret boolean;
605
606 BEGIN
607 -- Standard Start of API savepoint
608 SAVEPOINT mark_dirty2;
609
610 -- Standard call to check for call compatibility.
611 if NOT FND_API.Compatible_API_Call
612 (
613 l_api_version_number,
614 p_api_version_number,
615 l_api_name,
616 G_PKG_NAME
617 )
618 then
619 raise FND_API.G_EXC_UNEXPECTED_ERROR;
620 end if;
621
622 -- Initialize message list if p_init_msg_list is set to TRUE.
623 if FND_API.to_Boolean( p_init_msg_list)
624 then
625 FND_MSG_PUB.initialize;
626 end if;
627 -- Initialize API return status to success
628 x_return_status := FND_API.G_RET_STS_SUCCESS;
629
630 l_ret := asg_download.mark_dirty(p_pub_item,
631 p_accessList,
632 p_userid_list,
633 p_dml_type,
634 p_timestamp);
635 if (l_ret = TRUE )
636 then
637 x_return_status := FND_API.G_RET_STS_SUCCESS;
638 else
639 x_return_status := FND_API.G_RET_STS_ERROR;
640 END IF;
641
642
643 EXCEPTION
644 WHEN FND_API.G_EXC_ERROR THEN
645 Rollback to mark_dirty2;
646 x_return_status := FND_API.G_RET_STS_ERROR;
647
648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
649 Rollback to mark_dirty2;
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
651
652 WHEN OTHERS THEN
653 Rollback to mark_dirty2;
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655
656 END ;
657
658
659
660 PROCEDURE mark_dirty (
661 p_api_version_number IN NUMBER,
662 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
663 p_pub_item IN VARCHAR2,
664 p_accessid IN NUMBER,
665 p_userid IN NUMBER,
666 p_dml IN CHAR,
667 p_timestamp IN DATE,
668 x_return_status OUT NOCOPY VARCHAR2
669 )
670
671 IS
672 l_api_version_number CONSTANT NUMBER := 1.0;
673 l_api_name CONSTANT VARCHAR2(30) := 'MARK_DIRTY3';
674 l_ret boolean;
675
676 BEGIN
677 -- Standard Start of API savepoint
678 SAVEPOINT mark_dirty3;
679
683 l_api_version_number,
680 -- Standard call to check for call compatibility.
681 if NOT FND_API.Compatible_API_Call
682 (
684 p_api_version_number,
685 l_api_name,
686 G_PKG_NAME
687 )
688 then
689 raise FND_API.G_EXC_UNEXPECTED_ERROR;
690 end if;
691
692 -- Initialize message list if p_init_msg_list is set to TRUE.
693 if FND_API.to_Boolean( p_init_msg_list)
694 then
695 FND_MSG_PUB.initialize;
696 end if;
697
698 -- Initialize API return status to success
699 x_return_status := FND_API.G_RET_STS_SUCCESS;
700
701 l_ret := asg_download.mark_dirty(p_pub_item,
702 p_accessid,
703 p_userid,
704 p_dml,
705 p_timestamp);
706 if (l_ret = TRUE )
707 then
708 x_return_status := FND_API.G_RET_STS_SUCCESS;
709 else
710 x_return_status := FND_API.G_RET_STS_ERROR;
711 END IF;
712
713
714 EXCEPTION
715 WHEN FND_API.G_EXC_ERROR THEN
716 Rollback to mark_dirty3;
717 x_return_status := FND_API.G_RET_STS_ERROR;
718
719 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
720 Rollback to mark_dirty3;
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722
723 WHEN OTHERS THEN
724 Rollback to mark_dirty3;
725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726
727 END ;
728
729
730 PROCEDURE mark_dirty (
731 p_api_version_number IN NUMBER,
732 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
733 p_pub_item IN VARCHAR2,
734 p_accessid IN NUMBER,
735 p_userid IN NUMBER,
736 p_dml IN CHAR,
737 p_timestamp IN DATE,
738 p_pkvalues IN asg_download.pk_list,
739 x_return_status OUT NOCOPY VARCHAR2
740 )
741
742 IS
743 l_api_version_number CONSTANT NUMBER := 1.0;
744 l_api_name CONSTANT VARCHAR2(30) := 'MARK_DIRTY4';
745 l_ret boolean;
746
747 BEGIN
748
749 -- Standard Start of API savepoint
750 SAVEPOINT mark_dirty4;
751
752 -- Standard call to check for call compatibility.
753 if NOT FND_API.Compatible_API_Call
754 (
755 l_api_version_number,
756 p_api_version_number,
757 l_api_name,
758 G_PKG_NAME
759 )
760 then
761 raise FND_API.G_EXC_UNEXPECTED_ERROR;
762 end if;
763
764 -- Initialize message list if p_init_msg_list is set to TRUE.
765 if FND_API.to_Boolean( p_init_msg_list)
766 then
767 FND_MSG_PUB.initialize;
768 end if;
769
770 -- Initialize API return status to success
771 x_return_status := FND_API.G_RET_STS_SUCCESS;
772
773 l_ret := asg_download.mark_dirty(p_pub_item,
774 p_accessid,
775 p_userid,
776 p_dml,
777 p_timestamp,
778 p_pkvalues);
779 if (l_ret = TRUE )
780 then
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782 else
783 x_return_status := FND_API.G_RET_STS_ERROR;
784 END IF;
785
786
787
788 EXCEPTION
789 WHEN FND_API.G_EXC_ERROR THEN
790 Rollback to mark_dirty4;
791 x_return_status := FND_API.G_RET_STS_ERROR;
792
793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
794 Rollback to mark_dirty4;
795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
796
797 WHEN OTHERS THEN
798 Rollback to mark_dirty4;
799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800
801 END ;
802
803
804 PROCEDURE mark_dirty (
805 p_api_version_number IN NUMBER,
806 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
807 p_pub_item IN VARCHAR2,
808 p_accessList IN asg_download.access_list,
809 p_userid_list IN asg_download.user_list,
810 p_dml_type IN CHAR,
811 p_timestamp IN DATE,
812 p_bulk_flag IN BOOLEAN,
813 x_return_status OUT NOCOPY VARCHAR2
814 ) IS
815 l_ret boolean;
816 l_api_version_number CONSTANT NUMBER := 1.0;
817 l_api_name CONSTANT VARCHAR2(30) := 'MARK_DIRTY5';
818 BEGIN
819 -- Standard Start of API savepoint
820 SAVEPOINT mark_dirty5;
821
822 -- Standard call to check for call compatibility.
823 if NOT FND_API.Compatible_API_Call
824 (
825 l_api_version_number,
826 p_api_version_number,
827 l_api_name,
828 G_PKG_NAME
829 )
830 then
831 raise FND_API.G_EXC_UNEXPECTED_ERROR;
832 end if;
833
834 -- Initialize message list if p_init_msg_list is set to TRUE.
835 if FND_API.to_Boolean( p_init_msg_list)
836 then
837 FND_MSG_PUB.initialize;
838 end if;
839
840 -- Initialize API return status to success
841 x_return_status := FND_API.G_RET_STS_SUCCESS;
842
843 l_ret := asg_download.mark_dirty(p_pub_item,
844 p_accessList,
845 p_userid_list,
846 p_dml_type,
847 p_timestamp,
848 p_bulk_flag);
849 if (l_ret = TRUE )
850 then
851 x_return_status := FND_API.G_RET_STS_SUCCESS;
852 else
853 x_return_status := FND_API.G_RET_STS_ERROR;
854 END IF;
855
856
857 EXCEPTION
858 WHEN FND_API.G_EXC_ERROR THEN
859 Rollback to mark_dirty5;
860 x_return_status := FND_API.G_RET_STS_ERROR;
861
862 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
863 Rollback to mark_dirty5;
864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
865
866 WHEN OTHERS THEN
867 Rollback to mark_dirty5;
868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869
870 END ;
871
872
873 /* Log Routine */
874 PROCEDURE log (p_mesg VARCHAR2 ) IS
875 l_dml VARCHAR2(2000);
876
877 BEGIN
878 asg_helper.log(p_mesg, 'asg_custom_pvt', LOG_LEVEL);
879 END log;
880
881 END ASG_CUSTOM_PVT;