[Home] [Help]
PACKAGE BODY: APPS.PA_PURGE_PROJECTS_GEN_PKG
Source
4
1 package body pa_purge_projects_gen_pkg as
2 /* $Header: PAXPRGNB.pls 120.1 2005/08/19 17:17:26 mwasowic noship $ */
3 G_purge_project_id NUMBER(15);
5 procedure gen_projects ( x_purge_batch_id in NUMBER,
6 x_active_closed_flag in VARCHAR2,
7 x_closed_thru_date in DATE,
8 x_organization_id in NUMBER,
9 x_project_type in VARCHAR2,
10 x_project_status_code in VARCHAR2,
11 x_purge_summary_flag in VARCHAR2,
12 x_archive_summary_flag in VARCHAR2,
13 x_purge_budgets_flag in VARCHAR2,
14 x_archive_budgets_flag in VARCHAR2,
15 x_purge_capital_flag in VARCHAR2,
16 x_archive_capital_flag in VARCHAR2,
17 x_purge_actuals_flag in VARCHAR2,
18 x_archive_actuals_flag in VARCHAR2,
19 x_admin_proj_flag in VARCHAR2,
20 x_txn_to_date in DATE,
21 x_next_pp_project_status_code in VARCHAR2,
22 x_next_p_project_status_code in VARCHAR2,
23 x_user_id in NUMBER,
24 x_no_recs in OUT NOCOPY NUMBER) is --File.Sql.39 bug 4440895
25
26
27 cursor GenProjects is
28 select gp.project_id,
29 gp.project_status_code
30 from pa_projects_all gp
31 where (gp.carrying_out_organization_id = x_organization_id
32 or x_organization_id is null )
33 and (gp.project_type = x_project_type
34 or x_project_type is null )
35 and (trunc(gp.closed_date) <= trunc(x_closed_thru_date)
36 or x_closed_thru_date is null )
37 and (gp.project_status_code = x_project_status_code
38 or ((x_active_closed_flag = 'A'
39 and gp.project_type in ( select project_type
40 from pa_project_types
41 where project_type_class_code = 'INDIRECT')
42 and gp.project_status_code not in ('PARTIALLY_PURGED',
43 'PURGED',
44 'CLOSED'))
45 or ( x_active_closed_flag = 'C'
46 and gp.project_status_code in ('PARTIALLY_PURGED',
47 'CLOSED')))) ;
48 l_project_id NUMBER ;
49 l_project_status_code VARCHAR2(30);
50 l_active_closed_flag VARCHAR2(1) := x_active_closed_flag;
51 l_txn_to_date DATE ;
52 l_purge_actuals_flag VARCHAR2(1) ;
53 l_purge_budgets_flag VARCHAR2(1) ;
54 l_purge_capital_flag VARCHAR2(1) ;
55 l_purge_summary_flag VARCHAR2(1) ;
56
57 l_select_clause VARCHAR2(2000);
58 l_where_clause VARCHAR2(2000);
59 v_cursor_gen_id INTEGER ;
60 v_open_cursor INTEGER ;
61 BEGIN
62 v_cursor_gen_id := dbms_sql.open_cursor ;
66 ' and p.template_flag != ''Y'' ';
63 l_select_clause := 'select p.project_id, p.project_status_code ' ||
64 ' from pa_projects p ' ;
65 l_where_clause := ' where pa_security.allow_update(p.project_id) = ''Y'' '||
67
68 if x_organization_id is not null then
69 l_where_clause := l_where_clause ||' and p.carrying_out_organization_id = :organization_id ';
70 end if;
71
72 if x_closed_thru_date is not null then
73 l_where_clause := l_where_clause ||' and trunc(p.closed_date) <= trunc(:closed_thru_date) ';
74 end if;
75
76 if x_project_type is not null then
77 l_where_clause := l_where_clause ||' and p.project_type = :project_type ';
78 end if;
79 if x_project_status_code is not null then
80
81 l_where_clause := l_where_clause ||' and p.project_status_code = :project_status_code ';
82
83 end if;
84
85 if x_active_closed_flag = 'A' then
86 if x_project_status_code is null then
87 /* Bug#2389976: Removed the trailing spaces for CLOSED and PARTIALLY_PURGED */
88 l_where_clause := l_where_clause ||' and p.project_status_code not in '||
89 '(SELECT ps.project_status_code '||
90 ' from pa_project_statuses ps'||
91 ' where project_system_status_code in '||
92 ' (''PARTIALLY_PURGED'' , ' ||
93 '''PURGED'' , ' ||
94 '''PENDING_PURGE'' , ' ||
95 '''CLOSED'' )' || ')' ;
96 end if;
97 if x_project_type is null then
98 l_where_clause := l_where_clause ||' and p.project_type in '||
99 ' ( select pt.project_type ' ||
100 ' from pa_project_types pt ' ||
101 ' where pt.project_type_class_code = ''INDIRECT'') ' ;
102 end if;
103 elsif x_active_closed_flag = 'C' then
104 if x_project_status_code is null then
105 /* Bug#2389976: Removed the trailing spaces for PARTIALLY_PURGED */
106 l_where_clause := l_where_clause ||' and p.project_status_code in ' ||
107 '( SELECT ps.project_status_code '||
108 ' from pa_project_statuses ps'||
109 ' where ps.project_system_status_code in ' ||
110 '( ''CLOSED'' , ' ||
111 '''PARTIALLY_PURGED'' )' || ')';
112
113 end if;
114 end if;
115 /* code added for the bug#2464149, starts here */
116 if x_admin_proj_flag = 'Y' then
117 l_where_clause := l_where_clause ||' and pa_project_utils.Is_Admin_Project(p.project_id) = ''Y'' ';
118 else
119 l_where_clause := l_where_clause ||' and pa_project_utils.Is_Admin_Project(p.project_id) <> ''Y'' ';
120 end if;
124 dbms_sql.parse(v_cursor_gen_id, l_select_clause ||
121 /* code added for the bug#2464149, ends here */
122
123
125 l_where_clause,
126 dbms_sql.v7);
127
128 if x_organization_id is not NULL then
129 dbms_sql.bind_variable(v_cursor_gen_id, ':organization_id', x_organization_id) ;
130 end if;
131
132 if x_closed_thru_date is not NULL then
133 dbms_sql.bind_variable(v_cursor_gen_id, ':closed_thru_date', x_closed_thru_date) ;
134 end if;
135
136 if x_project_type is not NULL then
137 dbms_sql.bind_variable(v_cursor_gen_id, ':project_type', x_project_type) ;
138 end if;
139
140 if x_project_status_code is not NULL then
141 dbms_sql.bind_variable(v_cursor_gen_id, ':project_status_code', x_project_status_code) ;
142 end if;
143
144 dbms_sql.define_column(v_cursor_gen_id, 1, l_project_id);
145 dbms_sql.define_column(v_cursor_gen_id, 2, l_project_status_code, 30);
146
147 x_no_recs := 0 ;
148 -- open GenProjects ;
149 v_open_cursor := dbms_sql.execute(v_cursor_gen_id);
150
151 LOOP
152 -- fetch GenProjects into l_project_id,
153 -- l_project_status_code ;
154
158
155 If dbms_sql.fetch_rows(v_cursor_gen_id) = 0 then
156 exit ;
157 end if ;
159 dbms_sql.column_value(v_cursor_gen_id, 1, l_project_id);
160 dbms_sql.column_value(v_cursor_gen_id, 2, l_project_status_code);
161
162 -- if GenProjects%notfound then
163 -- exit ;
164 -- end if ;
165
166 l_txn_to_date := x_txn_to_date;
167 l_purge_actuals_flag := x_purge_actuals_flag;
168 l_purge_budgets_flag := x_purge_budgets_flag;
169 l_purge_capital_flag := x_purge_capital_flag;
170 l_purge_summary_flag := x_purge_summary_flag;
171
172 Get_Purge_Options(p_project_id => l_project_id,
173 p_active_closed_flag => l_active_closed_flag,
174 x_txn_to_date => l_txn_to_date,
175 x_purge_actuals_flag => l_purge_actuals_flag,
176 x_purge_budgets_flag => l_purge_budgets_flag,
177 x_purge_capital_flag => l_purge_capital_flag,
178 x_purge_summary_flag => l_purge_summary_flag);
179
180 insert into pa_purge_projects
181 ( Purge_batch_Id,
182 Project_Id,
183 Last_Project_Status_Code,
184 txn_to_date ,
185 Purge_Actuals_Flag,
186 Archive_Actuals_Flag,
187 Purge_Budgets_Flag,
188 Archive_Budgets_Flag,
189 Purge_Capital_Flag,
190 Archive_Capital_Flag,
191 Purge_Summary_Flag,
192 Archive_Summary_Flag,
193 Next_PP_Project_Status_Code,
194 Next_P_Project_Status_Code,
195 Purged_Date,
196 Purge_Project_Status_Code,
197 Created_By,
198 Last_Update_date,
199 Last_Updated_By,
200 Creation_Date )
201 select x_purge_batch_id,
202 l_project_id,
203 l_project_status_code,
204 x_txn_to_date ,
205 l_purge_actuals_flag,
206 decode(l_purge_actuals_flag, 'N','N',x_archive_actuals_flag),
207 l_purge_budgets_flag,
208 decode(l_purge_budgets_flag, 'N','N',x_archive_budgets_flag),
209 l_purge_capital_flag,
210 decode(l_purge_capital_flag, 'N','N',x_archive_capital_flag),
211 l_purge_summary_flag,
212 decode(l_purge_summary_flag, 'N','N',x_archive_summary_flag),
213 x_Next_PP_Project_Status_Code,
214 x_Next_P_Project_Status_Code,
215 NULL,
216 'N',
217 x_user_id,
218 sysdate,
219 x_user_id,
220 sysdate
221 from dual
222 where ( x_active_closed_flag = 'C'
223 and ( l_purge_actuals_flag = 'Y'
224 or l_purge_budgets_flag = 'Y'
225 or l_purge_capital_flag = 'Y'
226 or l_purge_summary_flag = 'Y'))
227 or ( l_txn_to_date is not null
228 and sign(x_txn_to_date - l_txn_to_date) = 1) ;
229
230 x_no_recs := nvl(x_no_recs, 0) + SQL%ROWCOUNT ;
231 END LOOP ;
232
233 -- close GenProjects ;
234 dbms_sql.close_cursor(v_cursor_gen_id) ;
235
236 exception
237 when others then
238 raise ;
239
240 END gen_projects;
241
242 Procedure Get_Purge_Options(p_project_id IN NUMBER,
243 p_active_closed_flag IN VARCHAR2,
244 x_txn_to_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
245 x_purge_actuals_flag IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
246 x_purge_budgets_flag IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
247 x_purge_capital_flag IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
248 x_purge_summary_flag IN OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
249
250 l_txn_to_date DATE ;
251 l_purge_actuals_flag VARCHAR2(1);
252 l_purge_budgets_flag VARCHAR2(1);
253 l_purge_capital_flag VARCHAR2(1);
254 l_purge_summary_flag VARCHAR2(1);
255
256 begin
257
258 Select NVL(MAX(decode(pp.txn_to_date, NULL, pp.purge_actuals_flag, 'N')), 'N'),
259 MAX(pp.txn_to_date),
260 NVL(MAX(pp.purge_capital_flag), 'N'),
261 NVL(MAX(pp.purge_budgets_flag), 'N'),
262 NVL(MAX(pp.purge_summary_flag), 'N')
263 Into l_purge_actuals_flag,
264 l_txn_to_date,
265 l_purge_capital_flag,
266 l_purge_budgets_flag,
267 l_purge_summary_flag
268 From pa_purge_projects pp
269 Where pp.project_id = p_project_id ;
270
271 if p_active_closed_flag = 'A' then
272 x_purge_actuals_flag := 'Y' ;
273 x_purge_budgets_flag := 'N' ;
274 x_purge_capital_flag := 'N' ;
275 x_purge_summary_flag := 'N' ;
276 if l_txn_to_date is null then
277 x_txn_to_date := x_txn_to_date - 1 ;
278 else
279 x_txn_to_date := l_txn_to_date ;
280 end if ;
281 else
282 if l_purge_actuals_flag = 'Y' then
283 x_purge_actuals_flag := 'N' ;
284 else
285 x_purge_actuals_flag := 'Y' ;
286 end if;
287
288 if l_purge_budgets_flag = 'Y' then
289 x_purge_budgets_flag := 'N' ;
290 end if ;
291
292 if l_purge_capital_flag = 'Y' then
293 x_purge_capital_flag := 'N' ;
294 end if ;
295
296 if l_purge_summary_flag = 'Y' then
297 x_purge_summary_flag := 'N' ;
298 end if ;
299 end if ;
300
301 EXCEPTION
302 When others then
303 NULL ;
304
305 END Get_Purge_Options ;
306
307 END ;