[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_SETUP_UTIL_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_SETUP_UTIL_PKG AS
2 /* $Header: ISCPSFUB.pls 120.1 2006/07/31 23:42:26 scheung noship $ */
3
4 function is_plan_name_exists (p_plan_name in varchar2) return varchar2 is
5 l_dblink varchar2(1000);
6 l_stmt varchar2(1000);
7 l_cursor_id number;
8 l_dummy number;
9 l_result varchar2(1);
10
11 begin
12
13 l_dblink := fnd_profile.value('ISC_DBI_PLANNING_INSTANCE');
14
15 if (l_dblink is null or l_dblink = '') then
16
17 select decode(ltrim(a2m_dblink, ' '), NULL, NULL, '@'||a2m_dblink)
18 into l_dblink
19 from mrp_ap_apps_instances_all;
20
21 elsif (l_dblink = '@') then
22
23 l_dblink := NULL;
24
25 end if;
26
27 l_stmt := 'select ''Y'' from msc_plans'||l_dblink||' msc where :plan_name in msc.compile_designator '||
28 'and curr_plan_type <> 4';
29 l_cursor_id := dbms_sql.open_cursor;
30 dbms_sql.parse(l_cursor_id,l_stmt,dbms_sql.native);
31 dbms_sql.bind_variable(l_cursor_id, ':plan_name', p_plan_name);
32 dbms_sql.define_column(l_cursor_id, 1, l_result, 1);
33 l_dummy := dbms_sql.execute(l_cursor_id);
34 l_dummy := dbms_sql.fetch_rows(l_cursor_id);
35 dbms_sql.column_value(l_cursor_id, 1, l_result);
36 dbms_sql.close_cursor(l_cursor_id);
37
38 if (l_result = 'Y') then
39 return 'Y';
40 else
41 return 'N';
42 end if;
43
44 exception
45 when others then
46 dbms_sql.close_cursor(l_cursor_id);
47 raise;
48
49 end;
50
51
52 function get_next_collection_date (p_frequency in varchar2,
53 p_days_offset in number,
54 p_reference_date in date) return date is
55 l_cursor_id number;
56 l_dummy number;
57 l_stmt varchar2(10000);
58 l_table_name varchar2(1000);
59 l_col_name varchar2(100);
60 l_result date;
61 l_days_offset number;
62
63 begin
64 if (p_frequency = 'ONCE') then
65 return trunc(sysdate)+p_days_offset-1;
66 elsif (p_frequency = 'WEEKLY')
67 then l_table_name := 'FII_TIME_WEEK';
68 elsif (p_frequency = 'MONTHLY')
69 then l_table_name := 'FII_TIME_ENT_PERIOD';
70 elsif (p_frequency = 'QUARTERLY')
71 then l_table_name := 'FII_TIME_ENT_QTR';
72 else -- (p_frequency = 'YEARLY')
73 l_table_name := 'FII_TIME_ENT_YEAR';
74 end if;
75
76 if (p_days_offset > 0) then
77 l_days_offset := p_days_offset - 1;
78 l_col_name := 'start_date';
79 else -- p_days_offset < 0
80 l_days_offset := p_days_offset + 1;
81 l_col_name := 'end_date';
82 end if;
83
84 if (l_days_offset >= 0) then
85 l_stmt := 'select min(f.'||l_col_name||')+'||l_days_offset||' from '||
86 l_table_name||' f where f.'||l_col_name||' >= trunc(:reference_date)-'||l_days_offset;
87 else
88 l_stmt := 'select min(f.'||l_col_name||')'||l_days_offset||' from '||
89 l_table_name||' f where f.'||l_col_name||' >= trunc(:reference_date)+'||abs(l_days_offset);
90 end if;
91
92
93 l_cursor_id := dbms_sql.open_cursor;
94 dbms_sql.parse(l_cursor_id,l_stmt,dbms_sql.native);
95 dbms_sql.bind_variable(l_cursor_id, ':reference_date', p_reference_date);
96 dbms_sql.define_column(l_cursor_id, 1, l_result);
97 l_dummy := dbms_sql.execute(l_cursor_id);
98 l_dummy := dbms_sql.fetch_rows(l_cursor_id);
99 dbms_sql.column_value(l_cursor_id, 1, l_result);
100 dbms_sql.close_cursor(l_cursor_id);
101 return l_result;
102
103 exception
104 when others then
105 dbms_sql.close_cursor(l_cursor_id);
106 raise;
107 end;
108
109
110 -- Called by Setup Form UI
111 function get_next_collection_date (p_frequency in varchar2,
112 p_days_offset in number) return date is
113 begin
114 return get_next_collection_date(p_frequency, p_days_offset, sysdate);
115 end;
116
117
118 -- Called by Collection Program
119 function get_next_collection_date (p_plan_name in varchar2) return date is
120 l_next_collection_date date;
121 begin
122 select get_next_collection_date(frequency, days_offset, sysdate+1)
123 into l_next_collection_date
124 from isc_dbi_plan_schedules
125 where plan_name = p_plan_name;
126
127 return l_next_collection_date;
128
129 exception
130 when others then
131 raise;
132 end;
133
134 END ISC_DBI_PLAN_SETUP_UTIL_PKG;