[Home] [Help]
PACKAGE BODY: APPS.QP_BLK_LOAD_UPG_PKG
Source
1 PACKAGE BODY QP_BLK_LOAD_UPG_PKG AS
2 /* $Header: QPXBLKUB.pls 120.8.12020000.2 2012/07/04 11:51:02 smbalara ship $ */
3 --Primary concurrent manager for upgrade of orig_sys_ref columns
4 PROCEDURE Blk_Load_Upg_Hdr_MGR(
5 X_errbuf out NOCOPY varchar2,
6 X_retcode out NOCOPY varchar2,
7 X_batch_size in number,
8 X_Num_Workers in number
9 )
10 IS
11 BEGIN
12 --
13 -- Manager processing
14 --
15 AD_CONC_UTILS_PKG.submit_subrequests(
16 X_errbuf=>X_errbuf,
17 X_retcode=>X_retcode,
18 X_WorkerConc_app_shortname=>'QP',
19 X_WorkerConc_progname=>'QP_BLK_LOAD_UPG_HDR_WKR',
20 X_batch_size=>X_batch_size,
21 X_Num_Workers=>X_Num_Workers
22 );
23
24 END Blk_Load_Upg_Hdr_MGR;
25
26 --Secondary concurrent manager for upgrade of orig_sys_ref columns
27 PROCEDURE Blk_Load_Upg_Hdr_WKR(
28 X_errbuf out NOCOPY varchar2,
29 X_retcode out NOCOPY varchar2,
30 X_batch_size in number,
31 X_Worker_Id in number,
32 X_Num_Workers in number
33 )
34 IS
35 --Variable for QP_LIST_HEADERS_B updation
36 l_worker_id_hdr number;
37 l_product_hdr varchar2(30) := 'QP';
38 l_table_name_hdr varchar2(30) := 'QP_LIST_HEADERS_B';
39 -- l_update_name_hdr varchar2(30) := 'UPDATE LIST HEADERS';
40 l_update_name_hdr varchar2(30) := 'HDR::'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SS'); -- modified by rassharm for unique name
41 l_status_hdr varchar2(30);
42 l_industry_hdr varchar2(30);
43 l_retstatus_hdr boolean;
44 l_table_owner_hdr varchar2(30);
45 l_any_rows_to_process_hdr boolean;
46 l_start_rowid_hdr rowid;
47 l_end_rowid_hdr rowid;
48 l_rows_processed_hdr number;
49
50
51
52 --Variable for QP_LIST_LINES updation
53 l_worker_id_line number;
54 l_product_line varchar2(30) := 'QP';
55 l_table_name_line varchar2(30) := 'QP_LIST_LINES';
56 -- l_update_name_line varchar2(30) := 'UPDATE LIST LINES';
57 l_update_name_line varchar2(30) := 'LINE::'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SS'); -- modified by rassharm for unique name
58 l_status_line varchar2(30);
59 l_industry_line varchar2(30);
60 l_retstatus_line boolean;
61 l_table_owner_line varchar2(30);
62 l_any_rows_to_process_line boolean;
63 l_start_rowid_line rowid;
64 l_end_rowid_line rowid;
65 l_rows_processed_line number;
66
67
68
69 --Variable for QP_PRICING_ATTRIBUTES updation
70 l_worker_id_pa number;
71 l_product_pa varchar2(30) := 'QP';
72 l_table_name_pa varchar2(30) := 'QP_PRICING_ATTRIBUTES';
73 --l_update_name_pa varchar2(30) := 'UPDATE PRICING ATTRIBUTES';
74 l_update_name_pa varchar2(30) := 'ATTR::'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SS'); -- modified by rassharm for unique name
75 l_status_pa varchar2(30);
76 l_industry_pa varchar2(30);
77 l_retstatus_pa boolean;
78 l_table_owner_pa varchar2(30);
79 l_any_rows_to_process_pa boolean;
80 l_start_rowid_pa rowid;
81 l_end_rowid_pa rowid;
82 l_rows_processed_pa number;
83
84
85
86 --Variable for QP_QUALIFIERS updation
87 l_worker_id_qual number;
88 l_product_qual varchar2(30) := 'QP';
89 l_table_name_qual varchar2(30) := 'QP_QUALIFIERS';
90 l_update_name_qual varchar2(30) := 'QUAL::'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SS'); -- modified by rassharm for unique name
91 l_status_qual varchar2(30);
92 l_industry_qual varchar2(30);
93 l_retstatus_qual boolean;
94 l_table_owner_qual varchar2(30);
95 l_any_rows_to_process_qual boolean;
96 l_start_rowid_qual rowid;
97 l_end_rowid_qual rowid;
98 l_rows_processed_qual number;
99
100 BEGIN
101 --Prepare for Header update
102 l_retstatus_hdr := fnd_installation.get_app_info(
103 l_product_hdr, l_status_hdr, l_industry_hdr, l_table_owner_hdr);
104 if ((l_retstatus_hdr = FALSE)
105 OR
106 (l_table_owner_hdr is null))
107 then
108 raise_application_error(-20001,
109 'Cannot get schema name for product : '||l_product_hdr);
110 end if;
111
112
113
114 --Prepare for Line update
115 l_retstatus_line := fnd_installation.get_app_info(
116 l_product_line, l_status_line, l_industry_line, l_table_owner_line);
117 if ((l_retstatus_line = FALSE)
118 OR
119 (l_table_owner_line is null))
120 then
121 raise_application_error(-20001,
122 'Cannot get schema name for product : '||l_product_line);
123 end if;
124
125
126
127 --Prepare for Pricing Attribute update
128 l_retstatus_pa := fnd_installation.get_app_info(
129 l_product_pa, l_status_pa, l_industry_pa, l_table_owner_pa);
130 if ((l_retstatus_pa = FALSE)
131 OR
132 (l_table_owner_pa is null))
133 then
134 raise_application_error(-20001,
135 'Cannot get schema name for product : '||l_product_pa);
136 end if;
137
138
139
140 --Prepare for Qualifier update
141 l_retstatus_qual := fnd_installation.get_app_info(
142 l_product_qual, l_status_qual, l_industry_qual, l_table_owner_qual);
143 if ((l_retstatus_qual = FALSE)
144 OR
145 (l_table_owner_qual is null))
146 then
147 raise_application_error(-20001,
148 'Cannot get schema name for product : '||l_product_qual);
149 end if;
150
151 fnd_file.put_line(FND_FILE.LOG, ' X_Worker_Id : '||X_Worker_Id);
152 fnd_file.put_line(FND_FILE.LOG, 'X_Num_Workers : '||X_Num_Workers);
153
154 BEGIN
155 --Initialize Header Update
156 ad_parallel_updates_pkg.initialize_rowid_range(
157 ad_parallel_updates_pkg.ROWID_RANGE,
158 l_table_owner_hdr,
159 l_table_name_hdr,
160 l_update_name_hdr,
161 X_worker_id,
162 X_num_workers,
163 X_batch_size, 0);
164 ad_parallel_updates_pkg.get_rowid_range(
165 l_start_rowid_hdr,
166 l_end_rowid_hdr,
167 l_any_rows_to_process_hdr,
168 X_batch_size,
169 TRUE);
170 --Update The Header
171 while (l_any_rows_to_process_hdr = TRUE)
172 loop
173 Update qp_list_headers_b qplh1
174 Set qplh1.orig_system_header_ref = 'INT'||to_char(qplh1.list_header_id)
175 where qplh1.orig_system_header_ref is null
176 and qplh1.rowid between l_start_rowid_hdr and l_end_rowid_hdr;
177 l_rows_processed_hdr := SQL%ROWCOUNT;
178 ad_parallel_updates_pkg.processed_rowid_range(
179 l_rows_processed_hdr,
180 l_end_rowid_hdr);
181 commit;
182 ad_parallel_updates_pkg.get_rowid_range(
183 l_start_rowid_hdr,
184 l_end_rowid_hdr,
185 l_any_rows_to_process_hdr,
186 X_batch_size,
187 FALSE);
188 end loop;
189 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
190
191
192
193 --Initialize Line Update
194 ad_parallel_updates_pkg.initialize_rowid_range(
195 ad_parallel_updates_pkg.ROWID_RANGE,
196 l_table_owner_line,
197 l_table_name_line,
198 l_update_name_line,
199 X_worker_id,
200 X_num_workers,
201 X_batch_size, 0);
202 ad_parallel_updates_pkg.get_rowid_range(
203 l_start_rowid_line,
204 l_end_rowid_line,
205 l_any_rows_to_process_line,
206 X_batch_size,
207 TRUE);
208 --Update The Line
209 while (l_any_rows_to_process_line = TRUE)
210 loop
211 update qp_list_lines l
212 set l.orig_sys_line_ref=to_char(l.list_line_id),
213 l.orig_sys_header_ref=
214 (
215 select nvl(h.orig_system_header_ref,'INT'||to_char(h.list_header_id))
216 from qp_list_headers_b h
217 where h.list_header_id = l.list_header_id
218 )
219 where l.orig_sys_line_ref is null
220 and l.rowid between l_start_rowid_line and l_end_rowid_line;
221
222 l_rows_processed_line := SQL%ROWCOUNT;
223
224 -- Bug 13395264.
225 -- orig_sys_header_ref could still be null for those list lines which were created post R12
226 -- and before running this program since orig_sys_line_ref will not be null for those lines and
227 -- orig_sys_header_ref was null for corresponding headers.
228
229 UPDATE qp_list_lines l
230 SET l.orig_sys_header_ref=
231 (
232 select nvl(h.orig_system_header_ref,'INT'||to_char(h.list_header_id))
233 from qp_list_headers_b h
234 where h.list_header_id = l.list_header_id
235 )
236 where l.orig_sys_header_ref is null
237 and l.rowid between l_start_rowid_line and l_end_rowid_line;
238
239 -- Bug 13395264 end.
240
241 ad_parallel_updates_pkg.processed_rowid_range(
242 l_rows_processed_line,
243 l_end_rowid_line);
244 commit;
245 ad_parallel_updates_pkg.get_rowid_range(
246 l_start_rowid_line,
247 l_end_rowid_line,
248 l_any_rows_to_process_line,
249 X_batch_size,
250 FALSE);
251 end loop;
252 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
253
254
255
256 --Initialize Pricing Attribute Update
257 ad_parallel_updates_pkg.initialize_rowid_range(
258 ad_parallel_updates_pkg.ROWID_RANGE,
259 l_table_owner_pa,
260 l_table_name_pa,
261 l_update_name_pa,
262 X_worker_id,
263 X_num_workers,
264 X_batch_size, 0);
265 ad_parallel_updates_pkg.get_rowid_range(
266 l_start_rowid_pa,
267 l_end_rowid_pa,
268 l_any_rows_to_process_pa,
269 X_batch_size,
270 TRUE);
271 --Update The Pricing Attribute
272 while (l_any_rows_to_process_pa = TRUE)
273 loop
274 update qp_pricing_attributes p
275 set p.ORIG_SYS_PRICING_ATTR_REF=to_char(p.PRICING_ATTRIBUTE_ID),
276 p.orig_sys_header_ref=
277 (
278 select nvl(h.orig_system_header_ref,'INT'||to_char(h.list_header_id))
279 from qp_list_headers_b h
280 where h.list_header_id = p.list_header_id
281 ),
282 p.orig_sys_line_ref=
283 (
284 select nvl(l.orig_sys_line_ref,l.list_line_id)
285 from qp_list_lines l
286 where l.list_line_id = p.list_line_id
287 )
288 where p.ORIG_SYS_PRICING_ATTR_REF is null
289 and p.rowid between l_start_rowid_pa and l_end_rowid_pa;
290
291 l_rows_processed_pa := SQL%ROWCOUNT;
292
293 -- Bug 13395264.
294 -- orig_sys_header_ref and orig_sys_line_ref could still be null for those
295 -- list lines which were created post R12 and before running this program since
296 -- ORIG_SYS_PRICING_ATTR_REF will not be null for those lines and orig_sys_header_ref was
297 -- null for corresponding headers.
298
299 update qp_pricing_attributes p
300 set p.orig_sys_header_ref=
301 (
302 select nvl(h.orig_system_header_ref,'INT'||to_char(h.list_header_id))
303 from qp_list_headers_b h
304 where h.list_header_id = p.list_header_id
305 )
306 where p.orig_sys_header_ref is null
307 and p.rowid between l_start_rowid_pa and l_end_rowid_pa;
308
309 update qp_pricing_attributes p
310 set p.orig_sys_line_ref=
311 (
312 select nvl(l.orig_sys_line_ref,l.list_line_id)
313 from qp_list_lines l
314 where l.list_line_id = p.list_line_id
315 )
316 where p.orig_sys_line_ref is null
317 and p.rowid between l_start_rowid_pa and l_end_rowid_pa;
318
319 -- Bug 13395264 end.
320
321 ad_parallel_updates_pkg.processed_rowid_range(
322 l_rows_processed_pa,
323 l_end_rowid_pa);
324 commit;
325 ad_parallel_updates_pkg.get_rowid_range(
326 l_start_rowid_pa,
327 l_end_rowid_pa,
328 l_any_rows_to_process_pa,
329 X_batch_size,
330 FALSE);
331 end loop;
332 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
333
334
335
336 --Initialize Qualifier Update
337 ad_parallel_updates_pkg.initialize_rowid_range(
338 ad_parallel_updates_pkg.ROWID_RANGE,
339 l_table_owner_qual,
340 l_table_name_qual,
341 l_update_name_qual,
342 X_worker_id,
343 X_num_workers,
344 X_batch_size, 0);
345 ad_parallel_updates_pkg.get_rowid_range(
346 l_start_rowid_qual,
347 l_end_rowid_qual,
348 l_any_rows_to_process_qual,
349 X_batch_size,
350 TRUE);
351 --Update The Qualifier
352 while (l_any_rows_to_process_qual = TRUE)
353 loop
354 update qp_qualifiers q
355 set q.orig_sys_qualifier_ref=to_char(q.qualifier_id),
356 q.orig_sys_header_ref=
357 (
358 select nvl(h.orig_system_header_ref,'INT'||to_char(h.list_header_id))
359 from qp_list_headers_b h
360 where h.list_header_id = q.list_header_id
361 )
362 where q.orig_sys_qualifier_ref is null
363 and q.list_header_id is not null
364 and q.rowid between l_start_rowid_qual and l_end_rowid_qual;
365
366 l_rows_processed_qual := SQL%ROWCOUNT;
367
368 -- bug 13395264
369 -- orig_sys_header_ref could still be null for those qualifier rows which were created post R12
370 -- and before running this program since orig_sys_qualifier_ref will not be null for those lines and
371 -- orig_sys_header_ref was null for corresponding headers.
372
373 update qp_qualifiers q
374 set q.orig_sys_header_ref=
375 (
376 select nvl(h.orig_system_header_ref,'INT'||to_char(h.list_header_id))
377 from qp_list_headers_b h
378 where h.list_header_id = q.list_header_id
379 )
380 where q.orig_sys_header_ref is null
381 and q.list_header_id is not null
382 and q.rowid between l_start_rowid_qual and l_end_rowid_qual;
383
384 -- bug 13395264 end
385
386 ad_parallel_updates_pkg.processed_rowid_range(
387 l_rows_processed_qual,
388 l_end_rowid_qual);
389 commit;
390 ad_parallel_updates_pkg.get_rowid_range(
391 l_start_rowid_qual,
392 l_end_rowid_qual,
393 l_any_rows_to_process_qual,
394 X_batch_size,
395 FALSE);
396 end loop;
397 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
402 raise;
403 END;
404
405 END Blk_Load_Upg_Hdr_WKR;
406
407 END QP_BLK_LOAD_UPG_PKG;