[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.6 2007/04/09 15:42:15 rassharm 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 l_rows_processed_line := SQL%ROWCOUNT;
222 ad_parallel_updates_pkg.processed_rowid_range(
223 l_rows_processed_line,
224 l_end_rowid_line);
225 commit;
226 ad_parallel_updates_pkg.get_rowid_range(
227 l_start_rowid_line,
228 l_end_rowid_line,
229 l_any_rows_to_process_line,
230 X_batch_size,
231 FALSE);
232 end loop;
233 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
234
235
236
237 --Initialize Pricing Attribute Update
238 ad_parallel_updates_pkg.initialize_rowid_range(
239 ad_parallel_updates_pkg.ROWID_RANGE,
240 l_table_owner_pa,
241 l_table_name_pa,
242 l_update_name_pa,
243 X_worker_id,
244 X_num_workers,
245 X_batch_size, 0);
246 ad_parallel_updates_pkg.get_rowid_range(
247 l_start_rowid_pa,
248 l_end_rowid_pa,
249 l_any_rows_to_process_pa,
250 X_batch_size,
251 TRUE);
252 --Update The Pricing Attribute
253 while (l_any_rows_to_process_pa = TRUE)
254 loop
255 update qp_pricing_attributes p
256 set p.ORIG_SYS_PRICING_ATTR_REF=to_char(p.PRICING_ATTRIBUTE_ID),
257 p.orig_sys_header_ref=
258 (
259 select nvl(h.orig_system_header_ref,'INT'||to_char(h.list_header_id))
260 from qp_list_headers_b h
261 where h.list_header_id = p.list_header_id
262 ),
263 p.orig_sys_line_ref=
264 (
265 select nvl(l.orig_sys_line_ref,l.list_line_id)
266 from qp_list_lines l
267 where l.list_line_id = p.list_line_id
268 )
269 where p.ORIG_SYS_PRICING_ATTR_REF is null
270 and p.rowid between l_start_rowid_pa and l_end_rowid_pa;
271 l_rows_processed_pa := SQL%ROWCOUNT;
272 ad_parallel_updates_pkg.processed_rowid_range(
273 l_rows_processed_pa,
274 l_end_rowid_pa);
275 commit;
276 ad_parallel_updates_pkg.get_rowid_range(
277 l_start_rowid_pa,
278 l_end_rowid_pa,
279 l_any_rows_to_process_pa,
280 X_batch_size,
281 FALSE);
282 end loop;
283 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
284
285
286
287 --Initialize Qualifier Update
288 ad_parallel_updates_pkg.initialize_rowid_range(
289 ad_parallel_updates_pkg.ROWID_RANGE,
290 l_table_owner_qual,
291 l_table_name_qual,
292 l_update_name_qual,
293 X_worker_id,
294 X_num_workers,
295 X_batch_size, 0);
296 ad_parallel_updates_pkg.get_rowid_range(
297 l_start_rowid_qual,
298 l_end_rowid_qual,
299 l_any_rows_to_process_qual,
300 X_batch_size,
301 TRUE);
302 --Update The Qualifier
303 while (l_any_rows_to_process_qual = TRUE)
304 loop
305 update qp_qualifiers q
306 set q.orig_sys_qualifier_ref=to_char(q.qualifier_id),
307 q.orig_sys_header_ref=
308 (
309 select nvl(h.orig_system_header_ref,'INT'||to_char(h.list_header_id))
310 from qp_list_headers_b h
311 where h.list_header_id = q.list_header_id
312 )
313 where q.orig_sys_qualifier_ref is null
314 and q.list_header_id is not null
315 and q.rowid between l_start_rowid_qual and l_end_rowid_qual;
316 l_rows_processed_qual := SQL%ROWCOUNT;
317 ad_parallel_updates_pkg.processed_rowid_range(
318 l_rows_processed_qual,
319 l_end_rowid_qual);
320 commit;
321 ad_parallel_updates_pkg.get_rowid_range(
322 l_start_rowid_qual,
323 l_end_rowid_qual,
324 l_any_rows_to_process_qual,
325 X_batch_size,
326 FALSE);
327 end loop;
328 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
329
330 EXCEPTION
331 WHEN OTHERS THEN
332 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
333 raise;
334 END;
335
336 END Blk_Load_Upg_Hdr_WKR;
337
338 END QP_BLK_LOAD_UPG_PKG;