[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_UPGRADE_REPORT_DIST_PKG
Source
1 PACKAGE BODY AP_WEB_UPGRADE_REPORT_DIST_PKG AS
2 /* $Header: apwuprdb.pls 120.3 2006/09/01 17:43:12 dtong noship $ */
3
4 PROCEDURE put_line(p_buff IN VARCHAR2) IS
5 BEGIN
6 fnd_file.put_line(fnd_file.log, p_buff);
7 END put_line;
8
9 ------------------------------------------------------------------------
10 PROCEDURE Upgrade(errbuf OUT NOCOPY VARCHAR2,
11 retcode OUT NOCOPY NUMBER,
12 p_batch_size IN VARCHAR2,
13 p_worker_id IN NUMBER,
14 p_num_workers IN NUMBER) IS
15 ------------------------------------------------------------------------
16 l_request_id NUMBER;
17 l_request_status VARCHAR2(30);
18 l_any_rows_to_process boolean;
19 l_table_name VARCHAR2(30) := 'AP_EXPENSE_REPORT_HEADERS_ALL';
20 l_script_name VARCHAR2(30) := 'apwuprdb.pls';
21 l_product VARCHAR2(30) := 'SQLAP';
22 l_table_owner VARCHAR2(30);
23
24 l_start_rowid ROWID;
25 l_end_rowid ROWID;
26 l_rows_processed NUMBER;
27
28 l_debug_info VARCHAR2(1000);
29 l_status VARCHAR2(30);
30 l_industry VARCHAR2(30);
31 l_retstatus BOOLEAN;
32
33 -- Product specific variables
34 l_bug_number constant NUMBER := -5345450;
35
36 BEGIN
37 g_debug_switch := 'Y';
38 g_last_updated_by := to_number(FND_GLOBAL.USER_ID);
39 g_last_update_login := to_number(FND_GLOBAL.LOGIN_ID);
40
41 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
42
43 IF g_debug_switch = 'Y' THEN
44
45 put_line('------------------------------------------------------------');
46 put_line('-- P A R A M E T E R S --');
47 put_line('------------------------------------------------------------');
48
49 put_line('Debug = ' || g_debug_switch);
50 put_line('Last Updated By = ' || g_last_updated_by);
51 put_line('Last Update Login = ' || g_last_update_login);
52 put_line('Request Id = ' || l_request_id);
53
54 put_line('Batch Size = ' || p_batch_size);
55 put_line('Worker Id = ' || p_worker_id);
56 put_line('Number of Workers = ' || p_num_workers);
57 END IF;
58
59 put_line('------------------------------------------------------------');
60 put_line('-- B E G I N --');
61 put_line('------------------------------------------------------------');
62
63 --
64 -- get schema name of the table for ROWID range processing
65 --
66 l_retstatus := fnd_installation.get_app_info(
67 l_product, l_status, l_industry, l_table_owner);
68
69 IF ((l_retstatus = FALSE) OR (l_table_owner is null)) THEN
70 raise_application_error(-20001, 'Cannot get schema name for product : '||l_product);
71 END IF;
72
73
74 put_line('-- Getting ROWID Range --');
75
76 ad_parallel_updates_pkg.initialize_rowid_range(
77 ad_parallel_updates_pkg.ROWID_RANGE,
78 l_table_owner,
79 l_table_name,
80 l_script_name,
81 p_worker_id,
82 p_num_workers,
83 p_batch_size, 0);
84
85 ad_parallel_updates_pkg.get_rowid_range(
86 l_start_rowid,
87 l_end_rowid,
88 l_any_rows_to_process,
89 p_batch_size,
90 TRUE);
91 while (l_any_rows_to_process = TRUE)
92 loop
93
94 -- Reset rows processed for this batch
95 l_rows_processed := 0;
96
97 -----------------------------------------------------
98 --
99 -- product specific processing here
100 --
101 --
102 -- Merge into distributions
103 l_debug_info := 'Merge line data into distributions';
104 put_line('-- Merge line data into distributions --');
105
106 MERGE INTO ap_exp_report_dists_all rd using (
107 SELECT /*+ rowid(xh) leading(xh) cardinality(10) no_merge */
108 xl.report_header_id,
109 xl.report_line_id,
110 xl.org_id,
111 xl.set_of_books_id,
112 gs.chart_of_accounts_id,
113 xl.flex_concatenated,
114 xl.code_combination_id,
115 xl.amount,
116 xl.project_id,
117 xl.task_id,
118 xl.award_id,
119 xl.expenditure_organization_id
120 FROM ap_expense_report_lines_all xl,
121 ap_expense_report_headers_all xh,
122 gl_sets_of_books gs
123 WHERE xh.rowid BETWEEN l_start_rowid AND l_end_rowid
124 --Derive chart of accounts ID from set of books ID
125 AND gs.set_of_books_id = xl.set_of_books_id
126 AND xl.report_header_id = xh.report_header_id
127 AND xh.vouchno <> 0
128 AND xl.code_combination_id is not null
129 AND (xl.itemization_parent_id is null
130 OR xl.itemization_parent_id <> -1)) xl
131 ON (rd.report_header_id = xl.report_header_id
132 and rd.report_line_id = xl.report_line_id)
133 WHEN MATCHED THEN UPDATE SET
134 rd.last_updated_by = l_bug_number,
135 rd.last_update_date = sysdate,
136 rd.amount = xl.amount,
137 rd.project_id = xl.project_id,
138 rd.task_id = xl.task_id,
139 rd.award_id = xl.award_id,
140 rd.expenditure_organization_id = xl.expenditure_organization_id,
141 rd.cost_center = nvl(ap_web_acctg_pkg.getcostcenter(
142 xl.code_combination_id, xl.chart_of_accounts_id), xl.flex_concatenated)
143 WHEN NOT MATCHED THEN INSERT (
144 rd.report_distribution_id,
145 rd.report_line_id,
146 rd.report_header_id,
147 rd.org_id,
148 rd.sequence_num,
149 rd.last_updated_by,
150 rd.last_update_date,
151 rd.created_by,
152 rd.creation_date,
153 rd.amount,
154 rd.project_id,
155 rd.task_id,
156 rd.award_id,
157 rd.expenditure_organization_id,
158 rd.code_combination_id,
159 rd.cost_center)
160 VALUES (
161 ap_exp_report_dists_s.nextval, -- use sequence
162 xl.report_line_id,
163 xl.report_header_id,
164 xl.org_id,
165 to_number(0), -- sequence_num=0 for 1-1 relationship
166 l_bug_number, -- last_updated_by
167 sysdate, -- last_update_date
168 l_bug_number, -- created_by
169 sysdate, -- creation_date
170 xl.amount,
171 xl.project_id,
172 xl.task_id,
173 xl.award_id,
174 xl.expenditure_organization_id,
175 xl.code_combination_id,
176 nvl(ap_web_acctg_pkg.getcostcenter(
177 xl.code_combination_id, xl.chart_of_accounts_id), xl.flex_concatenated));
178
179 l_debug_info := 'Null out obsolete columns from lines table.';
180 put_line('-- Null out obsolete columns from lines table --');
181
182 UPDATE AP_EXPENSE_REPORT_LINES_ALL XL
183 SET XL.flex_concatenated = null,
184 XL.code_combination_id = null,
185 XL.project_id = null,
186 XL.project_number = null,
187 XL.project_name = null,
188 XL.task_id = null,
189 XL.task_number = null,
190 XL.task_name = null,
191 XL.award_id = null,
192 XL.award_number = null,
193 XL.expenditure_organization_id = null
194 WHERE XL.code_combination_id IS NOT NULL
195 AND (XL.itemization_parent_id IS NULL
196 OR XL.itemization_parent_id <> -1)
197 AND EXISTS (select /*+ unnest rowid(xh) */ null
198 from AP_EXPENSE_REPORT_HEADERS_ALL xh
199 where xh.rowid between l_start_rowid and l_end_rowid
200 and xh.vouchno <> 0
201 and xh.report_header_id = xl.report_header_id);
202
203 ------------------------------------------------------
204
205 l_rows_processed := SQL%ROWCOUNT;
206
207 ad_parallel_updates_pkg.processed_rowid_range(
208 l_rows_processed,
209 l_end_rowid);
210
211 --
212 -- commit transaction here
213 --
214 commit;
215
216 --
217 -- get new range of rowids
218 --
219
220 ad_parallel_updates_pkg.get_rowid_range(
221 l_start_rowid,
222 l_end_rowid,
223 l_any_rows_to_process,
224 p_batch_size,
225 FALSE);
226
227 END LOOP;
228
229 put_line('------------------------------------------------------------');
230 put_line('-- E N D --');
231 put_line('------------------------------------------------------------');
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 put_line(sqlerrm);
236 rollback;
237 raise;
238 END Upgrade;
239
240 /*----------------------------------------------------------------------------*
241 | Procedure
242 | Parent
243 |
244 | DESCRIPTION
245 | This procedure is designed to be called by an upgrade script.
246 | This is a PL/SQL concurrent program which will submit a sub-request.
247 | When a sub-request is submitted, the parent exists to the Running/Paused
248 | state, so that it does not consume any resouces while waiting for the child
249 | request to complete.Whe nthe child completes the parent is restarted.
250 |
251 |
252 | PARAMETERS
253 | errbuf OUT VARCHAR2
254 | retcode OUT NUMBER
255 | p_batch_size IN VARCHAR2
256 | p_worker_id IN NUMBER
257 | p_num_workers IN NUMBER
258 *----------------------------------------------------------------------------*/
259 PROCEDURE Parent(errbuf OUT NOCOPY VARCHAR2,
260 retcode OUT NOCOPY NUMBER,
261 p_batch_size IN VARCHAR2,
262 p_worker_id IN NUMBER,
263 p_num_workers IN NUMBER) IS
264 ---------------------------------------------------------------
265 l_ret number;
266 BEGIN
267 l_ret := fnd_request.submit_request(application => 'SQLAP',
268 program => 'APWUPRD',
269 description => null,
270 start_time => null,
271 sub_request => FALSE,
272 argument1 => p_batch_size,
273 argument2 => p_worker_id,
274 argument3 => p_num_workers);
275
276 if l_ret = 0 then
277 --
278 -- If request submission failed, exit with error.
279 --
280 errbuf := fnd_message.get;
281 retcode := 2;
282 else
283 --
284 -- Here we set the globals to put the program into the
285 -- PAUSED status on exit.
286 --
287 fnd_conc_global.set_req_globals(conc_status => 'PAUSED');
288 errbuf := 'Sub-Request '||l_ret||' submitted!';
289 retcode := 0;
290 end if;
291
292 return;
293
294 END Parent;
295
296
297
298 END AP_WEB_UPGRADE_REPORT_DIST_PKG;