[Home] [Help]
PACKAGE BODY: APPS.BIS_AUTOINC_SCHEDULE
Source
1 package body bis_autoinc_schedule as
2 /* $Header: BISVAISB.pls 115.6 2003/11/20 15:24:05 nkishore noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile:~PROD:~PATH:~FILE
5 ----------------------------------------------------------------------------
6 -- PACKAGE: bis_autoinc_schedule --
7 -- --
8 -- DESCRIPTION: Auto increment dates for PM Viewer scheduled reports. --
9 -- --
10 -- MODIFICATIONS --
11 -- Date User Modification --
12 -- XX-XXX-XX XXXXXXXX Modifications made, which procedures changed & --
13 -- list bug number, if fixing a bug. --
14 -- 07/17/2001 dmarkman Initial Creation --
15 -- 08/06/2003 nkishore BugFix 3006638 --
16 -- --
17 ----------------------------------------------------------------------------
18
19
20
21 ----------------------------------------------------------------------------
22 -- Procedure: autoIncrementDates --
23 -- --
24 -- Description: auto increments time parameter for a scheduled report --
25 -- --
26 -- Parameters: --
27 -- --
28 -- call from BISVIEWER.showReport: --
29 -- --
30 -- bis_autoinc_schedule.autoIncrementDates(pRegionCode, --
31 -- pFunctionName, --
32 -- vSessionId, --
33 -- vUserId, --
34 -- vResponsibilityId, --
35 -- pScheduleId); --
36 -- --
37 -- HISTORY --
38 -- Date Developer Modifications --
39 -- 07-05-2001 dmarkman Initial Creation --
40 -- --
41 ----------------------------------------------------------------------------
42 procedure autoIncrementDates(pRegionCode in varchar2,
43 pFunctionName in varchar2,
44 pSessionId in varchar2,
45 pUserId in varchar2,
46 pResponsibilityId in varchar2,
47 pScheduleId in varchar2) is
48
49
50 v_increment_dates varchar2(1);
51 type c1_cur_type is ref cursor;
52 c1 c1_cur_type;
53
54 v_sql_stmnt varchar2(4000);
55
56 v_dimn_level_value_from varchar2(120);
57 v_dimn_level_id_from varchar2(120);
58
59 v_dimn_level_value_to varchar2(120);
60 v_dimn_level_id_to varchar2(120);
61
62 v_start_date date;
63 v_end_date date;
64 v_sys_date date;
65 v_start_date_temp date;
66
67 v_start_date_lov_from date;
68 v_start_date_lov_to date;
69 v_end_date_lov_from date;
70 v_end_date_lov_to date;
71
72 v_date_increment number;
73
74 v_rowid_to varchar2(60);
75 v_rowid_from varchar2(60);
76
77 v_rowid varchar2(60);
78 v_session_value bis_user_attributes.session_value%type;
79 v_session_description bis_user_attributes.session_description%type;
80 v_period_date bis_user_attributes.period_date%type;
81 v_attribute_name bis_user_attributes.attribute_name%type;
82
83 v_parameter varchar2(120);
84
85 v_org_param varchar2(80);
86 v_org_value varchar2(80);
87
88 v_from_flag char(1) := 'N';
89 v_to_flag char(1) := 'N';
90
91
92 cursor cStoredTimeParms(pScheduleId varchar2) is
93 select rowid, session_value, session_description, period_date, attribute_name
94 from bis_user_attributes
95 where dimension in('TIME','EDW_TIME_M')
96 and schedule_id = pScheduleId;
97
98
99 cursor cStoredOrgParms(pScheduleId varchar2) is
100 select attribute_name, session_description
101 from bis_user_attributes
102 where dimension in('ORGANIZATION','EDW_ORGANIZATION_M')
103 and schedule_id = pScheduleId;
104
105
106 cursor cIncrementFlag(pScheduleId varchar2) is
107 select increment_dates
108 from fnd_concurrent_requests
109 where request_id in(
110 select concurrent_request_id
111 from bis_scheduler
112 where schedule_id = pScheduleId);
113
114
115 begin
116
117 --htp.br;
118 --htp.print('dmarkman - auto increment');
119 --htp.br;
120
121
122 if pScheduleId is not null then
123
124 open cIncrementFlag(pScheduleId);
125 fetch cIncrementFlag into v_increment_dates;
126 close cIncrementFlag;
127
128
129 if v_increment_dates = 'Y' then
130
131 select sysdate
132 into v_sys_date
133 from dual;
134
135 open cStoredTimeParms(pScheduleId);
136 loop
137 fetch cStoredTimeParms into v_rowid, v_session_value, v_session_description,
138 v_period_date, v_attribute_name;
139 exit when cStoredTimeParms%notfound;
140
141 if(instr(v_attribute_name, '_FROM') <> 0) then
142
143 v_rowid_from := v_rowid;
144 v_parameter := replace(v_attribute_name, '_FROM');
145 v_start_date := v_period_date;
146
147 elsif (instr(v_attribute_name, '_TO') <> 0) then
148
149 v_rowid_to := v_rowid;
150 v_parameter := replace(v_attribute_name, '_TO');
151 v_end_date := v_period_date;
152
153 end if;
154
155 end loop;
156 close cStoredTimeParms;
157
158
159 if(v_sys_date - v_start_date > 0) and (v_sys_date - v_end_date > 0) and
160 (v_start_date is not null) and (v_end_date is not null) then
161
162
163 v_date_increment := v_end_date - v_start_date;
164
165 open cStoredOrgParms(pScheduleId);
166 fetch cStoredOrgParms into v_org_param, v_org_value;
167 close cStoredOrgParms;
168
169
170 v_sql_stmnt := bis_parameter_validation.getTimeLOVSQL(v_parameter, '',
171 'LOV',pRegionCode, pResponsibilityId, v_org_param, v_org_value);
172
173 -- htp.print(v_sql_stmnt);htp.br;
174
175 open c1 for v_sql_stmnt;
176 loop
177 fetch c1 into v_dimn_level_id_to, v_dimn_level_value_to, v_start_date_lov_to, v_end_date_lov_to;
178
179 exit when c1%notfound;
180
181 if (trunc(v_sys_date - v_start_date_lov_to) >= 0) and (trunc(v_sys_date - v_end_date_lov_to) <= 0) then
182 v_to_flag := 'Y';
183 exit;
184
185 end if;
186 end loop;
187 close c1;
188
189
190 v_start_date_temp := v_end_date_lov_to - v_date_increment;
191
192
193 open c1 for v_sql_stmnt;
194 loop
195 fetch c1 into v_dimn_level_id_from, v_dimn_level_value_from, v_start_date_lov_from, v_end_date_lov_from;
196
197 exit when c1%notfound;
198
199
200 if (trunc(v_start_date_temp - v_start_date_lov_from) >= 0) and (trunc(v_start_date_temp - v_end_date_lov_from) <= 0) then
201 v_from_flag := 'Y';
202 exit;
203
204 end if;
205 end loop;
206 close c1;
207
208
209 if(v_start_date_lov_from is not null) and (v_end_date_lov_to is not null)
210 and(v_to_flag = 'Y' and v_from_flag = 'Y') then
211
212 -- 'from' record:
213 update bis_user_attributes set
214 session_value = v_dimn_level_id_from,
215 session_description = v_dimn_level_value_from,
216 period_date = v_start_date_lov_from
217 where rowid = v_rowid_from;
218
219 -- 'to' record:
220 update bis_user_attributes set
221 session_value = v_dimn_level_id_to,
222 session_description = v_dimn_level_value_to,
223 period_date = v_end_date_lov_to
224 where rowid = v_rowid_to;
225
226 -- BugFix 3006638 'as of date'
227 update bis_user_attributes set
228 --session_value = to_char(v_sys_date, 'DD-MON-RRRR'),
229 --session_description = to_char(v_sys_date, 'DD-MON-RRRR'),
230 --As of Date 3094234--dd/mm/rrrr format
231 session_value = to_char(v_sys_date, 'DD/MM/RRRR'),
232 session_description = to_char(v_sys_date, 'DD/MM/RRRR'),
233 period_date = v_sys_date
234 where schedule_id= pScheduleId
235 and attribute_name='AS_OF_DATE';
236
237 commit;
238
239 end if;
240
241 --htp.print(' v_start_date_lov_from: ' || to_char(v_start_date_lov_from));htp.br;
242 --htp.print(' v_end_date_lov_to: ' || to_char(v_end_date_lov_to));htp.br;
243
244 end if;
245
246 end if;
247 end if;
248 exception when others then
249 null;
250 end autoIncrementDates;
251
252 end bis_autoinc_schedule;
253