[Home] [Help]
PACKAGE BODY: APPS.QP_AGRUPGRADE
Source
1 PACKAGE BODY QP_AgrUpgrade AS
2 /* $Header: QPXUPAGB.pls 120.0 2005/06/02 01:29:23 appldev noship $ */
3
4
5 --PROCEDURE Create_Parallel_Slabs(l_workers IN NUMBER := 5) IS --2422176
6 PROCEDURE Create_Parallel_Slabs(l_workers IN NUMBER) IS
7 v_type CONSTANT VARCHAR2(3) := 'AGR';
8 l_total_lines NUMBER;
9 l_min_line NUMBER;
10 l_max_line NUMBER;
11 l_counter NUMBER;
12 l_gap NUMBER;
13 l_worker_count NUMBER;
14 l_worker_start NUMBER;
15 l_worker_end NUMBER;
16 l_price_list_line_id NUMBER;
17 l_start_flag NUMBER;
18 l_total_workers NUMBER;
19
20 BEGIN
21
22 delete qp_upg_lines_distribution
23 where line_type = v_type;
24 commit;
25
26 BEGIN
27 SELECT
28 NVL(MIN(AGREEMENT_ID),0),
29 NVL(MAX(AGREEMENT_ID),0)
30 INTO
31 l_min_line,
32 l_max_line
33 FROM
34 SO_AGREEMENTS_B;
35
36 EXCEPTION
37 when others then
38 null;
39 END;
40
41
42 FOR i in 1..l_workers LOOP
43
44 l_worker_start := l_min_line + trunc( (i-1) * (l_max_line-l_min_line)/l_workers);
45
46 l_worker_end := l_min_line + trunc(i*(l_max_line - l_min_line)/l_workers);
47
48 IF (i <> l_workers) then
49 l_worker_end := l_worker_end - 1;
50 END IF;
51
52 QP_Modifier_Upgrade_Util_PVT.insert_line_distribution
53 ( l_worker => i,
54 l_start_line => l_worker_start,
55 l_end_line => l_worker_end,
56 l_type_var => v_type);
57
58 END LOOP;
59
60 commit;
61
62 END Create_Parallel_Slabs;
63
64
65 PROCEDURE Copy_Agreement(l_worker IN NUMBER := 1) AS
66
67 l_new_agreement_id NUMBER;
68 l_price_list_name VARCHAR2(240);
69 l_current_price_list_id NUMBER;
70 l_previous_price_list_id NUMBER;
71 l_price_list_id NUMBER;
72 l_list_header_id NUMBER;
73
74 errmsg VARCHAR2(2000);
75 lerrbuf VARCHAR2(100) := NULL;
76 lretcode NUMBER := 0;
77
78 G_COMPARATOR_CODE CONSTANT VARCHAR2(1) := '=';
79 v_context VARCHAR2(30) := 'CUSTOMER';
80 v_attribute_name VARCHAR2(240) := 'QUALIFIER_ATTRIBUTE7';
81
82 x_error_code NUMBER;
83 x_qualifier_precedence NUMBER;
84 x_qualifier_datatype VARCHAR2(30);
85 x_qualifier_grouping_no NUMBER;
86
87 v_min_line NUMBER;
88 v_max_line NUMBER;
89
90 CURSOR so_agreements_cur(p_min_line NUMBER,
91 p_max_line NUMBER) IS
92 SELECT
93 AGREEMENT_ID,
94 CREATION_DATE,
95 CREATED_BY,
96 LAST_UPDATE_DATE,
97 LAST_UPDATED_BY,
98 LAST_UPDATE_LOGIN,
99 AGREEMENT_TYPE_CODE,
100 PRICE_LIST_ID,
101 TERM_ID,
102 OVERRIDE_IRULE_FLAG,
103 OVERRIDE_ARULE_FLAG,
104 SIGNATURE_DATE,
105 AGREEMENT_NUM,
106 INVOICING_RULE_ID,
107 ACCOUNTING_RULE_ID,
108 CUSTOMER_ID,
109 PURCHASE_ORDER_NUM,
110 INVOICE_CONTACT_ID,
111 AGREEMENT_CONTACT_ID,
112 INVOICE_TO_SITE_USE_ID,
113 SALESREP_ID,
114 START_DATE_ACTIVE,
115 END_DATE_ACTIVE,
116 CONTEXT,
117 --NAME,
118 ATTRIBUTE1,
119 ATTRIBUTE2,
120 ATTRIBUTE3,
121 ATTRIBUTE4,
122 ATTRIBUTE5,
123 ATTRIBUTE6,
124 ATTRIBUTE7,
125 ATTRIBUTE8,
126 ATTRIBUTE9,
127 ATTRIBUTE10,
128 ATTRIBUTE11,
129 ATTRIBUTE12,
130 ATTRIBUTE13,
131 ATTRIBUTE14,
132 ATTRIBUTE15
133 from so_agreements_b a
134 where NOT EXISTS (select 'x'
135 from oe_agreements_b
136 where agreement_id = a.agreement_id)
137 AND a.agreement_id BETWEEN p_min_line AND p_max_line;
138
139 BEGIN
140
141 BEGIN
142 SELECT start_line_id, end_line_id
143 INTO v_min_line, v_max_line
144 FROM qp_upg_lines_distribution
145 WHERE worker = l_worker
146 AND line_type = G_LIST_TYPE_CODE;
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 v_min_line := 0;
150 v_max_line := 0;
151 COMMIT;
152 RETURN;
153 END;
154
155 FOR agreement_rec IN so_agreements_cur(v_min_line, v_max_line) LOOP
156 --dbms_output.put_line('processing agr = '||agreement_rec.agreement_id);
157
158 BEGIN
159 INSERT INTO OE_AGREEMENTS_B (
160 AGREEMENT_ID
161 ,CREATION_DATE
162 ,CREATED_BY
163 ,LAST_UPDATE_DATE
164 ,LAST_UPDATED_BY
165 ,LAST_UPDATE_LOGIN
166 ,AGREEMENT_TYPE_CODE
167 ,PRICE_LIST_ID
168 ,TERM_ID
169 ,OVERRIDE_IRULE_FLAG
170 ,OVERRIDE_ARULE_FLAG
171 ,SIGNATURE_DATE
172 ,AGREEMENT_NUM
173 ,REVISION
174 ,REVISION_DATE
175 ,REVISION_REASON_CODE
176 ,FREIGHT_TERMS_CODE
177 ,SHIP_METHOD_CODE
178 ,INVOICING_RULE_ID
179 ,ACCOUNTING_RULE_ID
180 ,PURCHASE_ORDER_NUM
181 ,INVOICE_CONTACT_ID
182 ,AGREEMENT_CONTACT_ID
183 ,SALESREP_ID
184 ,START_DATE_ACTIVE
185 ,END_DATE_ACTIVE
186 ,COMMENTS
187 ,CONTEXT
188 ,ATTRIBUTE1
189 ,ATTRIBUTE2
190 ,ATTRIBUTE3
191 ,ATTRIBUTE4
192 ,ATTRIBUTE5
193 ,ATTRIBUTE6
194 ,ATTRIBUTE7
195 ,ATTRIBUTE8
196 ,ATTRIBUTE9
197 ,ATTRIBUTE10
198 ,ATTRIBUTE11
199 ,ATTRIBUTE12
200 ,ATTRIBUTE13
201 ,ATTRIBUTE14
202 ,ATTRIBUTE15
203 ,TP_ATTRIBUTE1
204 ,TP_ATTRIBUTE2
205 ,TP_ATTRIBUTE3
206 ,TP_ATTRIBUTE4
207 ,TP_ATTRIBUTE5
208 ,TP_ATTRIBUTE6
209 ,TP_ATTRIBUTE7
210 ,TP_ATTRIBUTE8
211 ,TP_ATTRIBUTE9
212 ,TP_ATTRIBUTE10
213 ,TP_ATTRIBUTE11
214 ,TP_ATTRIBUTE12
215 ,TP_ATTRIBUTE13
216 ,TP_ATTRIBUTE14
217 ,TP_ATTRIBUTE15
218 ,TP_ATTRIBUTE_CATEGORY
219 ,INVOICE_TO_ORG_ID
223 , sysdate
220 ,SOLD_TO_ORG_ID
221 )
222 values( agreement_rec.agreement_id
224 , agreement_rec.created_by
225 , sysdate
226 , agreement_rec.last_updated_by
227 , agreement_rec.last_update_login
228 , agreement_rec.agreement_type_code
229 , agreement_rec.price_list_id
230 , agreement_rec.term_id
231 , agreement_rec.override_irule_flag
232 , agreement_rec.override_arule_flag
233 , sysdate
234 , agreement_rec.agreement_num
235 , 1 -- revision
236 , sysdate
237 , null
238 , null
239 , null
240 , agreement_rec.invoicing_rule_id
241 , agreement_rec.accounting_rule_id
242 , agreement_rec.purchase_order_num
243 , agreement_rec.invoice_contact_id
244 , agreement_rec.agreement_contact_id
245 , agreement_rec.salesrep_id
246 , nvl(agreement_rec.start_date_active,NULL)
247 , nvl(agreement_rec.end_date_active, NULL )
248 , null
249 , agreement_rec.context
250 , agreement_rec.attribute1
251 , agreement_rec.attribute2
252 , agreement_rec.attribute3
253 , agreement_rec.attribute4
254 , agreement_rec.attribute5
255 , agreement_rec.attribute6
256 , agreement_rec.attribute7
257 , agreement_rec.attribute8
258 , agreement_rec.attribute9
259 , agreement_rec.attribute10
260 , agreement_rec.attribute11
261 , agreement_rec.attribute12
262 , agreement_rec.attribute13
263 , agreement_rec.attribute14
264 , agreement_rec.attribute15
265 , NULL
266 , NULL
267 , NULL
268 , NULL
269 , NULL
270 , NULL
271 , NULL
272 , NULL
273 , NULL
274 , NULL
275 , NULL
281 , agreement_rec.invoice_to_site_use_id
276 , NULL
277 , NULL
278 , NULL
279 , NULL
280 , NULL
282 , agreement_rec.customer_id
283 );
284 /*
285
286 FROM so_agreements_b b ,
287 so_agreements_vl t
288 WHERE b.agreement_id = agreement_rec.agreement_id
289 and t.agreement_id = agreement_rec.agreement_id ;
290 */
291
292 EXCEPTION
293 when OTHERS THEN
294 errmsg := SQLERRM;
295 rollback;
296 qp_util.Log_Error(
297 p_id1 => agreement_rec.agreement_id,
298 p_error_type => 'DATA',
299 p_error_desc => errmsg,
300 p_error_module => 'QP_AGRUPGRADE'
301 );
302
303 RAISE;
304
305
306 END;
307
308
309
310 /* Inserting into Translation agreements Table */
311
312
313 BEGIN
314 INSERT INTO OE_AGREEMENTS_TL (
315 AGREEMENT_ID
316 , LANGUAGE
317 , SOURCE_LANG
318 , NAME
319 , LAST_UPDATE_DATE
320 , LAST_UPDATED_BY
321 , CREATION_DATE
322 , CREATED_BY
323 , LAST_UPDATE_LOGIN
324 )
325
326 SELECT
327 agreement_rec.agreement_id,
328 l.language_code,
329 userenv('LANG'),
330 t.name,
331 sysdate,
332 agreement_rec.last_updated_by,
333 sysdate,
334 agreement_rec.created_by,
335 agreement_rec.last_update_login
336 FROM fnd_languages l
337 , so_agreements_tl t
338 WHERE l.installed_flag IN ('I', 'B')
339 and t.agreement_id = agreement_rec.agreement_id
340 and t.language = l.language_code
341 AND NOT EXISTS (
342 SELECT NULL
343 FROM oe_agreements_tl r
344 WHERE r.agreement_id = agreement_rec.agreement_id
345 AND r.language = l.language_code);
346
347
348
349 EXCEPTION
350 when OTHERS THEN
351 errmsg := SQLERRM;
352 rollback;
353 qp_util.Log_Error(
354 p_id1 => agreement_rec.agreement_id,
355 p_error_type => 'DATA',
356 p_error_desc => errmsg,
357 p_error_module => 'QP_AGRUPGRADE '
358 );
359 RAISE;
360
361
362 END;
363
364 -- Commit every 500 rows
365 IF(mod(so_agreements_cur%ROWCOUNT,500) = 0) THEN
366 COMMIT;
367 END IF;
368
369 END LOOP;
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 errmsg := SQLERRM;
374 rollback;
375 qp_util.Log_Error(
376 p_id1 => 123456,
377 p_error_type => 'DATA',
378 p_error_desc => errmsg,
379 p_error_module => 'QP_AGRUPGRADE '
380 );
381
382 RAISE;
383
384
385 END Copy_Agreement;
386
387
388 END QP_AgrUpgrade;