1 PACKAGE BODY CSD_Migrate_From_115X_PKG2 AS
2 /* $Header: csdmig2b.pls 115.5 2004/06/22 18:49:20 sragunat noship $ */
3
4 PROCEDURE csd_repairs_mig2(p_slab_number IN NUMBER) IS
5
6 Type NumTabType is VARRAY(10000) of NUMBER;
7 repair_line_id_mig NumTabType;
8
9 Type RowidTabType is VARRAY(1000) of VARCHAR2(30);
10 rowid_mig RowidTabtype;
11
12 v_min NUMBER;
13 v_max NUMBER;
14 v_error_text VARCHAR2(2000);
15 MAX_BUFFER_SIZE NUMBER := 500;
16
17 l_currency_code VARCHAR2(15);
18
19 error_process exception;
20
21 CURSOR csd_repairs_cursor (p_start number, p_end number) is
22 select cr.repair_line_id,
23 cr.rowid
24 from csd_repairs cr
25 where cr.currency_code IS NULL
26 and cr.repair_line_id >= p_start
27 and cr.repair_line_id <= p_end;
28
29 BEGIN
30
31 -- Get the Slab Number for the table
32 Begin
33 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIRS'
34 ,'CSD'
35 ,p_slab_number
36 ,v_min
37 ,v_max);
38 if v_min is null then
39 return;
40 end if;
41 End;
42
43 -- Commented for the bug 3054706. Forward Ported as part of Bug 3714442
44 /*
45 BEGIN
46
47 select gl.currency_code
48 into l_currency_code
49 from gl_sets_of_books gl, hr_operating_units ou
50 where ou.organization_id = cs_std.get_item_valdn_orgzn_id
51 and ou.set_of_books_id = gl.set_of_books_id;
52
53 exception
54 when no_data_found then
55 null;
56 when others then
57 null;
58 END;
59 */
60
61 -- Migration code for CSD_REPAIRS
62 OPEN csd_repairs_cursor(v_min,v_max);
63 LOOP
64 FETCH csd_repairs_cursor bulk collect into
65 repair_line_id_mig,
66 rowid_mig
67 LIMIT MAX_BUFFER_SIZE;
68
69 FOR j in 1..repair_line_id_mig.count
70 LOOP
71 SAVEPOINT CSD_REPAIRS;
72
73 -- Added for the bug 3054706. Forward Ported as part of Bug 3714442
74 BEGIN
75
76 select distinct currency_code
77 into l_currency_code
78 from cs_estimate_details
79 where source_id = repair_line_id_mig(j)
80 and source_code = 'DR';
81
82 exception
83 when no_data_found then
84 -- Meaning no charge lines exists for the repair order
85 l_currency_code := NULL;
86 when too_many_rows then
87 -- Meaning charge lines for the repair order are
88 -- in more than one distinct currencies.
89 l_currency_code := NULL;
90 when others then
91 l_currency_code := NULL;
92 END;
93
94 IF l_currency_code IS NOT NULL THEN
95
96
97 Begin
98
99 UPDATE csd_repairs
100 SET currency_code = l_currency_code,
101 last_update_date = sysdate,
102 last_updated_by = fnd_global.user_id,
103 last_update_login = fnd_global.login_id
104 WHERE rowid = rowid_mig(j);
105
106 IF SQL%NOTFOUND then
107 Raise error_process;
108 End If;
109
110 Exception
111
112 When error_process then
113 ROLLBACK to CSD_REPAIRS;
114 v_error_text := substr(sqlerrm,1,1000)||'Repair Line Id:'||repair_line_id_mig(j);
115 INSERT INTO CSD_UPG_ERRORS
116 (ORIG_SYSTEM_REFERENCE,
117 TARGET_SYSTEM_REFERENCE,
118 ORIG_SYSTEM_REFERENCE_ID,
119 UPGRADE_DATETIME,
120 ERROR_MESSAGE,
121 MIGRATION_PHASE)
122 VALUES( 'CSD_REPAIRS'
123 ,'CSD_REPAIRS'
124 ,repair_line_id_mig(j)
125 ,sysdate
126 ,v_error_text
127 ,'11.5.9' );
128
129 commit;
130 raise_application_error( -20000, 'Error while migrating CSD_REPAIRS table data: Error while updating CSD_REPAIRS. '|| v_error_text);
131
132
133 End;
134 END IF; -- l_currency_code IS NOT NULL
135
136 END LOOP;
137
138 COMMIT;
139 EXIT WHEN csd_repairs_cursor%notfound;
140 END LOOP;
141
142 if csd_repairs_cursor%isopen then
143 close csd_repairs_cursor;
144 end if;
145
146 COMMIT;
147
148 END csd_repairs_mig2;
149
150
151 /*-------------------------------------------------------------------------------*/
152 /* procedure name: insert_rep_typ_sar */
153 /* description : procedure for inserting Material , Labor and Expense SAR */
154 /* data into CSD_REPAIR_TYPES_SAR table in 11.5.9 */
155 /*-------------------------------------------------------------------------------*/
156 PROCEDURE insert_rep_typ_sar( p_repair_type_id IN NUMBER
157 ,p_txn_billing_type_id IN NUMBER
158 ,p_created_by IN NUMBER
159 ,p_creation_date IN DATE)
160 IS
161 l_user_id NUMBER := fnd_global.user_id;
162 l_count NUMBER;
163 v_error_text VARCHAR2(2000);
164
165 BEGIN
166
167 begin
168
169 -- check if the repair type and billing txn type id not yet inserted into CSD_REPAIR_TYPES_SAR
170 select count(*)
171 into l_count
172 from CSD_REPAIR_TYPES_SAR
173 where REPAIR_TYPE_ID = p_repair_type_id
174 and TXN_BILLING_TYPE_ID = p_txn_billing_type_id;
175 exception
176 WHEN OTHERS THEN
177 l_count := 2; -- error handling can be anything but zero
178 end;
179
180 -- if l_count = 0 then it is not yet inserted
181 if (l_count = 0) then
182
183 SAVEPOINT REPAIR_TYPES_SAR;
184
185 BEGIN
186
187 -- inserted into CSD_REPAIR_TYPES_SAR
188 insert into CSD_REPAIR_TYPES_SAR
189 ( REPAIR_TXN_BILLING_TYPE_ID
190 ,REPAIR_TYPE_ID
191 ,TXN_BILLING_TYPE_ID
192 ,CREATED_BY
193 ,CREATION_DATE
194 ,LAST_UPDATED_BY
195 ,LAST_UPDATE_DATE
196 ,OBJECT_VERSION_NUMBER
197 ) VALUES
198 ( CSD_REPAIR_TYPES_SAR_S1.NEXTVAL
199 ,p_repair_type_id
200 ,p_txn_billing_type_id
201 ,p_created_by
202 ,p_creation_date
203 ,l_user_id
204 ,SYSDATE
205 ,1
206 );
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 v_error_text := substr(SQLERRM,2000);
211 ROLLBACK to REPAIR_TYPES_SAR;
212 INSERT INTO csd_upg_errors
213 (orig_system_reference,
214 target_system_reference,
215 orig_system_reference_id,
216 upgrade_datetime,
217 error_message,
218 migration_phase)
219 VALUES ('CS_REPAIR_TYPES_B',
220 'CSD_REPAIR_TYPES_SAR',
221 p_repair_type_id,
222 sysdate,
223 v_error_text,
224 '11.5.9');
225
226 commit;
227
228 raise_application_error( -20000, 'Error while migrating CSD_REPAIR_TYPES_B table data: Error while inserting into CSD_REPAIR_TYPES_SAR. '|| v_error_text);
229
230 END;
231
232 end if;
233
234 COMMIT;
235
236 END insert_rep_typ_sar;
237
238 /*-------------------------------------------------------------------------------*/
239 /* procedure name: CSD_REPAIR_TYPES_B_MIG2 */
240 /* description : procedure for migrating Material , Labor and Expense SAR */
241 /* data in CSD_REPAIR_TYPES_B table in 11.5.8 */
242 /* to CSD_REPAIR_TYPES_SAR table in 11.5.9 */
243 /*-------------------------------------------------------------------------------*/
244
245 PROCEDURE CSD_REPAIR_TYPES_B_MIG2 IS
246
247 l_repair_type_id CSD_REPAIR_TYPES_B.REPAIR_TYPE_ID%TYPE;
248 l_mtl_txn_billing_type_id CSD_REPAIR_TYPES_B.MTL_TXN_BILLING_TYPE_ID%TYPE;
249 l_lbr_txn_billing_type_id CSD_REPAIR_TYPES_B.LBR_TXN_BILLING_TYPE_ID%TYPE;
250 l_exp_txn_billing_type_id CSD_REPAIR_TYPES_B.EXP_TXN_BILLING_TYPE_ID%TYPE;
251 l_created_by CSD_REPAIR_TYPES_B.CREATED_BY%TYPE;
252 l_creation_date CSD_REPAIR_TYPES_B.CREATION_DATE%TYPE;
253
254 -- select repairs types which have material or
255 -- Labor or Expense SAR set up in Repair Types table
256 CURSOR csd_repair_types_b_cursor is
257 select crtb.REPAIR_TYPE_ID,
258 crtb.MTL_TXN_BILLING_TYPE_ID,
259 crtb.LBR_TXN_BILLING_TYPE_ID,
260 crtb.EXP_TXN_BILLING_TYPE_ID,
261 crtb.CREATED_BY,
262 crtb.CREATION_DATE
263 from csd_repair_types_b crtb
264 where ((crtb.MTL_TXN_BILLING_TYPE_ID IS NOT NULL) OR
265 (crtb.LBR_TXN_BILLING_TYPE_ID IS NOT NULL) OR
266 (crtb.EXP_TXN_BILLING_TYPE_ID IS NOT NULL))
267 and trunc(sysdate) between nvl(trunc(crtb.start_date_active),trunc(sysdate))
268 and nvl(trunc(crtb.end_date_active),trunc(sysdate));
269
270 l_error_text VARCHAR2(2000);
271
272 BEGIN
273
274 -- Open the cursor and update the table
275 OPEN csd_repair_types_b_cursor;
276
277 LOOP
278
279 FETCH csd_repair_types_b_cursor
280 INTO l_repair_type_id
281 ,l_mtl_txn_billing_type_id
282 ,l_lbr_txn_billing_type_id
283 ,l_exp_txn_billing_type_id
284 ,l_created_by
285 ,l_creation_date;
286
287 EXIT WHEN csd_repair_types_b_cursor%NOTFOUND;
288
289 -- check if the repair type and billing txn type id not yet inserted in to CSD_REPAIR_TYPES_SAR
290
291
292
293 if (l_mtl_txn_billing_type_id is not null) then
294
295 -- insert into CSD_REPAIR_TYPES_SAR
296 insert_rep_typ_sar( l_repair_type_id
297 ,l_mtl_txn_billing_type_id
298 ,l_created_by
299 ,l_creation_date);
300 end if;
301
302 if (l_lbr_txn_billing_type_id is not null) then
303
304 -- insert into CSD_REPAIR_TYPES_SAR
305 insert_rep_typ_sar( l_repair_type_id
306 ,l_lbr_txn_billing_type_id
307 ,l_created_by
308 ,l_creation_date);
309 end if;
310
311 if (l_exp_txn_billing_type_id is not null) then
312
313 -- insert into CSD_REPAIR_TYPES_SAR
314 insert_rep_typ_sar( l_repair_type_id
315 ,l_exp_txn_billing_type_id
316 ,l_created_by
317 ,l_creation_date);
318 end if;
319
320 END LOOP;
321
322 END CSD_REPAIR_TYPES_B_MIG2;
323
324 END CSD_Migrate_From_115X_PKG2;
325
[Home] [Help]