[Home] [Help]
PACKAGE BODY: APPS.OKS_CCMIGRATE_PVT
Source
1 PACKAGE BODY OKS_CCMIGRATE_PVT AS
2 /* $Header: OKSCMIGB.pls 120.9 2006/06/28 23:51:40 hvaladip noship $ */
3
4
5 -- Global vars to hold the min and max hdr_id for each sub-request range
6 type range_rec is record (
7 lo number,
8 hi number,
9 jobno number);
10 type rangeArray is VARRAY(100) of range_rec;
11 range_arr rangeArray;
12 Type l_num_tbl is table of NUMBER index by BINARY_INTEGER ;
13 Type l_date_tbl is table of DATE index by BINARY_INTEGER ;
14 Type l_chr_tbl is table of Varchar2(4000) index by BINARY_INTEGER ;
15 --------------------------------------------------------------------------------------------
16 -- Generate Range and Split Function. --
17 --------------------------------------------------------------------------------------------
18
19 procedure split_range (
20 p_lo number,
21 p_hi number,
22 p_buckets number) is
23 -- splits range (p_lo=>p_hi) into p_buckets pieces and appends to VArrays.
24 l_lo number := p_lo;
25 l_idx1 number := range_arr.count + 1;
26 l_idx2 number := range_arr.count + p_buckets;
27 l_bucket_width integer;
28 begin
29 FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_lo = '||p_lo );
30 FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_hi = '||p_hi );
31 FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_buckets = '||p_buckets );
32
33 If p_buckets = 0 then
34 return;
35 End if;
36 if range_arr.count > 0 then
37 -- so we don't overlap high value of previous range
38 l_lo := p_lo + 1;
39 end if;
40
41 l_bucket_width := (p_hi - l_lo) / p_buckets;
42
43 range_arr.extend(p_buckets);
44
45 for idx in l_idx1..l_idx2 loop
46 range_arr(idx).lo := l_lo + ((idx - l_idx1) * l_bucket_width);
47 if idx < l_idx2 then
48 range_arr(idx).hi := range_arr(idx).lo + l_bucket_width -1;
49 else
50 range_arr(idx).hi := p_hi;
51 end if;
52 end loop;
53 end split_range;
54
55 function generate_ranges (
56 p_lo IN number,
57 p_hi IN number,
58 p_avg In number,
59 p_stddev IN number,
60 p_total IN number,
61 p_sub_requests IN number) return integer is
62
63 l_total_buckets integer := 0;
64 l_stdlo number := greatest(round(p_avg - p_stddev), p_lo);
65 l_stdhi number := least(round(p_avg + p_stddev), p_hi);
66 l_stddev_percent number := 0.66; -- the area covered by +/-1 stddev
67
68
69 l_outlier_buckets integer := 0;
70 l_std_buckets integer := 0;
71 l_lo_buckets integer := 0;
72 l_hi_buckets integer := 0;
73 l_outlier_entries_per_bucket number := 0;
74 modidx integer;
75 begin
76 range_arr := rangeArray();
77
78 --l_total_buckets := greatest(nvl(p_sub_requests,3), least(p_total/MAX_SINGLE_REQUEST, MAX_JOBS));
79 l_total_buckets := greatest(p_sub_requests,2);
80 l_outlier_buckets := l_total_buckets * (1 - l_stddev_percent);
81 if l_outlier_buckets > 0 then
82 l_outlier_entries_per_bucket := p_total * (1 - l_stddev_percent)
83 / l_outlier_buckets ;
84 end if;
85
86 for idx in 1..l_outlier_buckets
87 loop
88 modidx := mod(idx,2);
89 -- alternate assignment between hi and lo buckets
90 if modidx = 1
91 AND (p_hi - (l_hi_buckets+1) * l_outlier_entries_per_bucket)
92 > l_stdhi then
93 -- allocate buckets for positive outliers
94 l_hi_buckets := l_hi_buckets + 1;
95 elsif modidx = 0
96 AND (p_lo + (l_lo_buckets+1) * l_outlier_entries_per_bucket)
97 < l_stdlo then
98 -- allocate buckets for negative outliers
99 l_lo_buckets := l_lo_buckets + 1;
100 -- else min or max has been consumed, save bucket for middle
101 end if;
102 end loop;
103
104 -- compute middle buckets
105 l_std_buckets := l_total_buckets - l_lo_buckets - l_hi_buckets;
106
107 -- in case low-high allocations yielded zero buckets.
108 -- i.e., outliers were folded into middle buckets.
109 if l_lo_buckets = 0 then
110 l_stdlo := p_lo;
111 end if;
112 if l_hi_buckets = 0 then
113 l_stdhi := p_hi;
114 end if;
115
116 -- ranges for negative outliers
117 split_range(p_lo, l_stdlo, l_lo_buckets);
118 -- ranges for +/-1 stddev from mean
119 split_range(l_stdlo, l_stdhi, l_std_buckets);
120 -- ranges for positive outliers
121 split_range(l_stdhi, p_hi, l_hi_buckets);
122
123 return l_total_buckets;
124 end generate_ranges;
125
126 PROCEDURE MIGRATE_CC (
127 ERRBUF OUT NOCOPY VARCHAR2,
128 RETCODE OUT NOCOPY NUMBER,
129 P_SUB_REQUESTS IN NUMBER,
130 P_BATCH_SIZE IN NUMBER ) IS
131
132 cursor l_hdr_ranges(l_bucket_size number) is
133 SELECT /*+ parallel(WBR) */
134 WB_Low
135 ,WB_High,rownum num
136 FROM
137 (SELECT /*+ no_merge parallel(WB) */ MIN(ID) WB_Low, MAX(ID) WB_High
138 FROM
139 (SELECT /*+ no_merge parallel(khdr) */ ID, FLOOR((ROWNUM-1)/l_bucket_size) Worker_Bucket
140 FROM
141 ( SELECT id
142 FROM oks_k_headers_b okshdr
143 WHERE okshdr.cc_no IS NOT NULL
144 AND okshdr.payment_type = 'CCR'
145 AND okshdr.trxn_extension_id is null
146 order by id) KHDR) WB GROUP BY Worker_Bucket) WBR;
147
148 cursor l_line_ranges(l_bucket_size number) is
149 SELECT /*+ parallel(WBR) */
150 WB_Low
151 ,WB_High,rownum num
152 FROM
153 (SELECT /*+ no_merge parallel(WB) */ MIN(ID) WB_Low, MAX(ID) WB_High
154 FROM
155 (SELECT /*+ no_merge parallel(kln) */ ID, FLOOR((ROWNUM-1)/l_bucket_size) Worker_Bucket
156 FROM
157 ( SELECT oksline.id
158 FROM oks_k_lines_b oksline, okc_k_lines_b okcline
159 WHERE oksline.cle_id=okcline.id
160 and oksline.cc_no IS NOT NULL
161 AND oksline.payment_type = 'CCR'
162 AND oksline.trxn_extension_id is null
163 and okcline.lse_id in (1,12,19,46)
164 order by id) KLN) WB GROUP BY Worker_Bucket) WBR;
165
166 Cursor l_hdr_agg_csr1 IS
167
168 Select /*+ PARALLEL(okshdr) */
169 min(okshdr.id) minid,
170 max(okshdr.id) maxid,
171 avg(okshdr.id) avgid,
172 stddev(okshdr.id) stdid,
173 count(*) total
174 From OKS_K_HEADERS_B okshdr ;
175 Cursor l_hdr_agg_csr2 IS
176
177 Select /*+ PARALLEL(okshdrh) */
178 min(okshdrh.id) minid,
179 max(okshdrh.id) maxid,
180 avg(okshdrh.id) avgid,
181 stddev(okshdrh.id) stdid,
182 count(*) total
183 From OKS_K_HEADERS_BH okshdrh ;
184
185 Cursor l_line_agg_csr1 IS
186 Select /*+ PARALLEL(oksline) */
187 min(oksline.id) minid,
188 max(oksline.id) maxid,
189 avg(oksline.id) avgid,
190 stddev(oksline.id) stdid,
191 count(*) total
192 From OKS_K_LINES_B oksline ;
193
194 Cursor l_line_agg_csr2 IS
195 Select /*+ PARALLEL(okslineh) */
196 min(okslineh.id) minid,
197 max(okslineh.id) maxid,
198 avg(okslineh.id) avgid,
199 stddev(okslineh.id) stdid,
200 count(*) total
201 From OKS_K_LINES_BH okslineh ;
202
203 l_agg_rec l_line_agg_csr2%rowtype;
204 l_sub_requests number;
205 l_sub_req number;
206 l_batch_size number;
207 l_ret number;
208
209 BEGIN
210
211 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Start of migrate_cc'||P_SUB_REQUESTS||p_batch_size);
212
213 IF P_SUB_REQUESTS IS NULL OR P_SUB_REQUESTS > 30 OR P_SUB_REQUESTS = 0 then
214 l_sub_requests := 30 ;
215 ELSE
216 l_sub_requests := p_sub_requests ;
217 END IF;
218
219 IF p_batch_size is null or p_batch_size > 10000 or p_batch_size = 0 then
220 l_batch_size := 10000;
221 ELSE
222 l_batch_size := p_batch_size;
223 END IF;
224
225
226 -----------------------------------------------------------------------------------------
227 IF (FND_CONC_GLOBAL.request_data is null) THEN
228 -- The following csr is on OKS_K_HDRS_BH
229 open l_hdr_agg_csr2;
230 fetch l_hdr_agg_csr2 into l_agg_rec;
231 close l_hdr_agg_csr2;
232
233 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Cursor opened is l_hdr_agg_csr2' );
234 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.minid = '|| l_agg_rec.minid );
235 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.maxid = '|| l_agg_rec.maxid );
236 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.total = '|| l_agg_rec.total );
237
238
239
240 l_ret := FND_REQUEST.submit_request('OKS',
241 'OKS_MIGCC_HDRH',
242 to_char(l_sub_requests), -- UI job display
243 null,
244 TRUE, -- TRUE means isSubRequest
245 l_agg_rec.minid,
246 l_agg_rec.maxid,nvl(l_batch_size,10000));
247
248 IF (l_ret = 0) then
249 errbuf := fnd_message.get;
250 retcode := 2;
251 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request failed to submit: ' || errbuf);
252 return;
253 ELSE
254 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' submitted');
255 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' p_low_id ==> '|| l_agg_rec.minid || ' l_hig_id ==> '||l_agg_rec.maxid );
256 END IF;
257
258
259
260 -- The following csr is on OKS_K_LINES_BH
261 open l_line_agg_csr2;
262 fetch l_line_agg_csr2 into l_agg_rec;
263 close l_line_agg_csr2;
264
265 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Cursor opened is l_line_agg_csr2' );
266 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.minid = '|| l_agg_rec.minid );
267 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.maxid = '|| l_agg_rec.maxid );
268 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.total = '|| l_agg_rec.total );
269
270
271
272 l_ret := FND_REQUEST.submit_request ('OKS',
273 'OKS_MIGCC_LINH',
274 to_char(l_sub_requests), -- UI job display
275 null,
276 TRUE, -- TRUE means isSubRequest
277 l_agg_rec.minid,
278 l_agg_rec.maxid,nvl(l_batch_size,10000));
279
280 IF (l_ret = 0) then
281 errbuf := fnd_message.get;
282 retcode := 2;
283 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request Line History failed to submit: ' || errbuf);
284 return;
285 ELSE
286 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' submitted');
287 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' p_low_id ==> '|| l_agg_rec.minid || ' l_hig_id ==> '||l_agg_rec.maxid );
288 END IF;
289
290
291 /* --now process the oks_k_hdr
292 open l_hdr_agg_csr1;
293 fetch l_hdr_agg_csr1 into l_agg_rec;
294 close l_hdr_agg_csr1;
295
296 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Cursor opened is l_hdr_agg_csr1' );
297 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.minid = '|| l_agg_rec.minid );
298 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.maxid = '|| l_agg_rec.maxid );
299 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.total = '|| l_agg_rec.total );
300 l_sub_req := generate_ranges(l_agg_rec.minid,
301 l_agg_rec.maxid,
302 l_agg_rec.avgid,
303 l_agg_rec.stdid,
304 l_agg_rec.total,
305 nvl(l_sub_requests-1,30));
306 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_sub_requests = '|| l_sub_req );
307
308 */
309
310 FOR range_rec in l_hdr_ranges(l_batch_size)
311 LOOP
312 l_ret := FND_REQUEST.submit_request('OKS',
313 'OKS_MIGCC_HDR',
314 to_char(range_rec.num), -- UI job display
315 null,
316 TRUE, -- TRUE means isSubRequest
317 range_rec.wb_low,
318 range_rec.wb_high,
319 nvl(l_batch_size,10000));
320
321 IF (l_ret = 0) then
322 errbuf := fnd_message.get;
323 retcode := 2;
324 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request failed to submit: ' || errbuf);
325 return;
326 ELSE
327 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' submitted');
328 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' p_low_id ==> '|| range_rec.wb_low || ' l_hig_id ==> '||range_rec.wb_high );
329 END IF;
330 END LOOP;
331
332 --- now process the lines
333
334 /* open l_line_agg_csr1;
335 fetch l_line_agg_csr1 into l_agg_rec;
336 close l_line_agg_csr1;
337
338 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Cursor opened is l_line_agg_csr1' );
339 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.minid = '|| l_agg_rec.minid );
340 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.maxid = '|| l_agg_rec.maxid );
341 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.total = '|| l_agg_rec.total );
342 l_sub_req := generate_ranges(l_agg_rec.minid,
343 l_agg_rec.maxid,
344 l_agg_rec.avgid,
345 l_agg_rec.stdid,
346 l_agg_rec.total,
347 nvl(l_sub_requests-1,30));
348 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_sub_requests = '|| l_sub_req );
349 */
350
351 FOR range_rec in l_line_ranges(l_batch_size)
352 LOOP
353 l_ret := FND_REQUEST.submit_request('OKS',
354 'OKS_MIGCC_LIN',
355 range_rec.num, -- UI job display
356 null,
357 TRUE, -- TRUE means isSubRequest
358 range_rec.wb_low,
359 range_rec.wb_high,
360 nvl(l_batch_size,10000));
361
362 IF (l_ret = 0) then
363 errbuf := fnd_message.get;
364 retcode := 2;
365 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request failed to submit: ' || errbuf);
366 return;
367 ELSE
368 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' submitted');
369 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' p_low_id ==> '|| range_rec.wb_low || ' l_hig_id ==> '||range_rec.wb_high );
370 END IF;
371 END LOOP;
372
373
374 FND_CONC_GLOBAL.set_req_globals(conc_status => 'PAUSED',
375 request_data => to_char(l_sub_requests));
376 errbuf := to_char(l_sub_requests) || ' sub-requests submitted';
377 retcode := 0;
378 return;
379
380
381 END IF;
382
383 END MIGRATE_CC;
384
385 PROCEDURE MIGRATE_CC_LINEH(
386 ERRBUF OUT NOCOPY VARCHAR2,
387 RETCODE OUT NOCOPY NUMBER,
388 p_id_low IN NUMBER,
389 p_id_high IN NUMBER,
390 p_batchsize IN NUMBER ) IS
391
392
393
394 l_user_id NUMBER;
395 c_cc_trxn_id l_num_tbl;
396 c_cc_id l_num_tbl;
397 c_cc_number l_chr_tbl;
398 c_cc_code l_chr_tbl;
399 c_cc_exp_date l_date_tbl;
400 okcline_id l_num_tbl;
401 oksline_id l_num_tbl;
402 oksline_major_version l_num_tbl;
403 okchdr_id l_num_tbl;
404 c_customer_id l_num_tbl;
405 c_cust_account_id l_num_tbl;
406 c_cust_site_use_id l_num_tbl;
407 c_org_id l_num_tbl;
408 c_additional_info l_chr_tbl;
409 c_trxn_entity_id l_num_tbl;
410 c_instr_assignment_id l_num_tbl;
411 c_ext_payer_id l_num_tbl;
412 c_create_payer_flag l_chr_tbl;
413 c_hash1 l_chr_tbl;
414 c_hash2 l_chr_tbl;
415 c_card_range_id l_num_tbl;
416 c_sec_segment_id l_num_tbl;
417 c_cc_num_length l_num_tbl;
418 c_cc_range_length l_num_tbl;
419 c_cc_unmask_digits l_chr_tbl;
420 c_cc_masked_num l_chr_tbl;
421
422
423 -- Cursor that queries all transactions needed to be migrated with encryption enabled
424
425 -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
426 -- the party contexts that are not in the external payer table can be identified.
427 -- 2. The credit card number needs to be numeric.
428 cursor oksline_cur_sec is
429 select
430 oksline.id oksline_id,
431 oksline.major_version oksline_major_version,
432 TRANSLATE(oksline.cc_no,'0: -_', '0'),
433 'UNKNOWN',
434 oksline.cc_expiry_date,
435 IV.hdr_id okchdr_id,
436 IV.line_id okcline_id,
437 IV.party_id,
438 IV.cust_acct_id,
439 IV.bill_to_site_use_id,
440 IV.authoring_org_id,
441 iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
442 iby_instr_s.nextval, -- the new credit card id
443 DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
444 PAYER.EXT_PAYER_ID),
445 DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
446 -- should create new external payer
447 IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
448 SEC.cc_number_hash1,
449 SEC.cc_number_hash2,
450 SEC.cc_issuer_range_id,
451 SEC.sec_segment_id,
452 SEC.cc_number_length,
453 SEC.cc_unmask_digits,
454 LPAD(sec.cc_unmask_digits, NVL(range.card_number_length, length(oksline.cc_no)),'X')
455 from OKS_K_LINES_BH oksline,
456 (select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, line.cust_acct_id,
457 line.bill_to_site_use_id, line.id line_id
458 from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
459 where line.dnz_chr_id = hdr.id
460 and line.lse_id in (1,12,19,46)
461 and hz.cust_account_id = line.cust_acct_id) IV,
462 IBY_EXTERNAL_PAYERS_ALL payer,
463 IBY_SECURITY_SEGMENTS sec,
464 IBY_CC_ISSUER_RANGES rangE
465 where IV.line_id = oksline.cle_id
466 and oksline.cc_no is not null
467 and oksline.payment_type = 'CCR'
468 and oksline.cc_bank_acct_id is null
469 and oksline.trxn_extension_id is null
470 ---and IV.party_id = PAYER.PARTY_ID (+)
471 and IV.cust_acct_id = PAYER.CUST_ACCOUNT_ID(+)
472 and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
473 ---and IV.authoring_org_id = PAYER.ORG_ID(+)
474 and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
475 and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
476 and sec.sec_segment_id = IBY_CC_SECURITY_PUB.get_segment_id(oksline.cc_no)
477 and sec.CC_ISSUER_RANGE_ID = RANGE.CC_ISSUER_RANGE_ID (+)
478 and oksline.id BETWEEN p_id_low AND p_id_high;
479
480 -- Cursor that queries all transactions needed to be migrated with encryption disable
481
482 -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
483 -- the party contexts that are not in the external payer table can be identified.
484 -- 2. The credit card number needs to be numeric.
485 cursor oksline_cur is
486 select
487 oksline.id oksline_id,
488 oksline.major_version oksline_major_version,
489 TRANSLATE(oksline.cc_no,'0: -_', '0'),
490 'UNKNOWN',
491 oksline.cc_expiry_date,
492 IV.hdr_id okchdr_id,
493 IV.line_id okcline_id,
494 IV.party_id,
495 IV.cust_acct_id,
496 IV.bill_to_site_use_id,
497 IV.authoring_org_id,
498 IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
499 IBY_INSTR_S.nextval, -- the new credit card id
500 DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
501 PAYER.EXT_PAYER_ID),
502 DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
503 -- should create new external payer
504 IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
505 ----iby_fndcpt_setup_pub.get_hash(oksline.cc_no, FND_API.G_FALSE) cc_number_hash1,
506 ----iby_fndcpt_setup_pub.get_hash(oksline.cc_no, FND_API.G_TRUE) cc_number_hash2,
507 iby_fndcpt_setup_pub.get_hash(oksline.cc_no, 'F') cc_number_hash1,
508 iby_fndcpt_setup_pub.get_hash(oksline.cc_no, 'T') cc_number_hash2,
509 IBY_CC_VALIDATE.Get_CC_Issuer_Range(oksline.cc_no) cc_issuer_range_id,
510 Null sec_segment_id,
511 DECODE(IBY_CC_VALIDATE.Get_CC_Issuer_Range(oksline.cc_no), NULL,LENGTH(oksline.cc_no), NULL) cc_number_length,
512 SUBSTR(oksline.cc_no,GREATEST(-4,-LENGTH(oksline.cc_no))) cc_unmask_digits,
513 LPAD(SUBSTR(oksline.cc_no, GREATEST(-4,-LENGTH(oksline.cc_no))),
514 LENGTH(oksline.cc_no),
515 'X' ) masked_cc_number
516 from oks_k_lines_bh oksline,
517 (select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, line.cust_acct_id,
518 line.bill_to_site_use_id, line.id line_id
519 from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
520 where line.dnz_chr_id = hdr.id
521 and line.lse_id in (1,12,19,46)
522 and hz.cust_account_id = line.cust_acct_id) IV,
523 IBY_EXTERNAL_PAYERS_ALL payer
524 where IV.line_id = oksline.cle_id
525 and oksline.cc_no is not null
526 and oksline.payment_type = 'CCR'
527 and oksline.cc_bank_acct_id is null
528 and oksline.trxn_extension_id is null
529 ---and IV.party_id = PAYER.PARTY_ID (+)
530 and IV.cust_acct_id = PAYER.CUST_ACCOUNT_ID(+)
531 and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
532 ---and IV.authoring_org_id = PAYER.ORG_ID(+)
533 and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
534 and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
535 and oksline.id BETWEEN p_id_low AND p_id_high;
536
537 l_return_status VARCHAR2(1);
538 l_msg_data VARCHAR2(2000);
539 l_msg_count NUMBER;
540
541 BEGIN
542
543
544 --open the cursor and migrate the stuff
545
546 l_user_id := NVL(fnd_global.user_id, -1);
547
548 IF (iby_cc_security_pub.encryption_enabled()) THEN
549 -- security enabled
550 OPEN oksline_cur_sec;
551 Else
552 OPEN oksline_cur;
553 End if;
554
555 LOOP
556 IF (iby_cc_security_pub.encryption_enabled()) THEN
557 FETCH oksline_cur_sec BULK COLLECT INTO
558 oksline_id, oksline_major_version, c_cc_number, c_cc_code, c_cc_exp_date,
559 okcline_id, okchdr_id, c_customer_id, c_cust_account_id,
560 c_cust_site_use_id, c_org_id, c_trxn_entity_id, c_cc_id, c_ext_payer_id,
561 c_create_payer_flag, c_instr_assignment_id, c_hash1, c_hash2, c_card_range_id,
562 c_sec_segment_id, c_cc_num_length, c_cc_unmask_digits, c_cc_masked_num
563 limit p_batchsize;
564
565 ELSE
566 FETCH oksline_cur BULK COLLECT INTO
567 oksline_id, oksline_major_version, c_cc_number, c_cc_code, c_cc_exp_date,
568 okcline_id, okchdr_id, c_customer_id, c_cust_account_id,
569 c_cust_site_use_id, c_org_id, c_trxn_entity_id, c_cc_id, c_ext_payer_id,
570 c_create_payer_flag, c_instr_assignment_id, c_hash1, c_hash2, c_card_range_id,
571 c_sec_segment_id, c_cc_num_length, c_cc_unmask_digits, c_cc_masked_num
572 limit p_batchsize;
573
574 END IF;
575
576
577 EXIT WHEN c_trxn_entity_id.count = 0 ;
578
579 IF c_trxn_entity_id.count > 0 Then
580
581 FND_FILE.PUT_LINE (FND_FILE.LOG, 'count = '||c_trxn_entity_id.count );
582 -- create new credit cards with single use only
583 Begin
584
585 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
586 INSERT INTO IBY_CREDITCARD
587 (CARD_OWNER_ID,
588 INSTRUMENT_TYPE,
589 PURCHASECARD_FLAG,
590 CARD_ISSUER_CODE,
591 ACTIVE_FLAG,
592 SINGLE_USE_FLAG,
593 EXPIRYDATE,
594 CHNAME,
595 CCNUMBER,
596 INSTRID,
597 CREATED_BY,
598 CREATION_DATE,
599 LAST_UPDATED_BY,
600 LAST_UPDATE_DATE,
601 LAST_UPDATE_LOGIN,
602 ENCRYPTED,
603 CC_NUMBER_HASH1,
604 CC_NUMBER_HASH2,
605 CC_ISSUER_RANGE_ID,
606 CC_NUM_SEC_SEGMENT_ID,
607 CARD_MASK_SETTING,
608 CARD_UNMASK_LENGTH,
609 CC_NUMBER_LENGTH,
610 MASKED_CC_NUMBER,
611 OBJECT_VERSION_NUMBER
612
613 )
614 VALUES(
615 c_customer_id(i),
616 'CREDITCARD',
617 'N',
618 c_cc_code(i),
619 'Y',
620 'Y',
621 c_cc_exp_date(i),
622 null,
623 DECODE(c_sec_segment_id(i), NULL,c_cc_number(i), c_cc_unmask_digits(i)),
624 c_cc_id(i),
625 l_user_id,
626 sysdate,
627 l_user_id,
628 sysdate,
629 l_user_id,
630 DECODE(c_sec_segment_id(i), NULL,'N','Y'),
631 c_hash1(i),
632 c_hash2(i),
633 c_card_range_id(i),
634 c_sec_segment_id(i),
635 'DISPLAY_LAST',
636 4,
637 c_cc_num_length(i),
638 c_cc_masked_num(i),
639 1
640 );
641
642 -- Now insert into the instrument use table
643
644 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
645 INSERT INTO IBY_PMT_INSTR_USES_ALL
646 (INSTRUMENT_PAYMENT_USE_ID,
647 EXT_PMT_PARTY_ID,
648 INSTRUMENT_TYPE,
649 INSTRUMENT_ID,
650 PAYMENT_FUNCTION,
651 PAYMENT_FLOW,
652 ORDER_OF_PREFERENCE,
653 START_DATE,
654 CREATED_BY,
655 CREATION_DATE,
656 LAST_UPDATED_BY,
657 LAST_UPDATE_DATE,
658 LAST_UPDATE_LOGIN,
659 object_version_number)
660 SELECT
661 c_instr_assignment_id(i),
662 EXT_PAYER_ID,
663 'CREDITCARD',
664 c_cc_id(i),
665 'CUSTOMER_PAYMENT',
666 'FUNDS_CAPTURE',
667 1,
668 sysdate,
669 l_user_id,
670 sysdate,
671 l_user_id,
672 sysdate,
673 l_user_id,
674 1
675 FROM IBY_EXTERNAL_PAYERS_ALL payer
676 WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
677 AND payer.PARTY_ID = c_customer_id(i)
678 AND payer.ORG_TYPE = 'OPERATING_UNIT'
679 AND payer.ORG_ID = c_org_id(i)
680 AND payer.CUST_ACCOUNT_ID = c_cust_account_id(i)
681 AND payer.ACCT_SITE_USE_ID = c_cust_site_use_id(i)
682 AND ROWNUM = 1;
683
684
685 -- insert the transactions into IBY transaction extension table
686 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
687 INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
688 (TRXN_EXTENSION_ID,
689 PAYMENT_CHANNEL_CODE,
690 INSTR_ASSIGNMENT_ID,
691 ENCRYPTED,
692 ORIGIN_APPLICATION_ID,
693 ORDER_ID,
694 CREATED_BY,
695 CREATION_DATE,
696 LAST_UPDATED_BY,
697 LAST_UPDATE_DATE,
698 LAST_UPDATE_LOGIN,
699 OBJECT_VERSION_NUMBER)
700 VALUES
701 (c_trxn_entity_id(i),
702 'CREDIT_CARD',
703 c_instr_assignment_id(i),
704 'N',
705 515,
706 oksline_id(i),
707 l_user_id,
708 sysdate,
709 l_user_id,
710 sysdate,
711 l_user_id,
712 1);
713
714 -- update the foreign key relationship
715
716 FORALL i in c_trxn_entity_id.first..c_trxn_entity_id.last
717 UPDATE oks_k_lines_bh
718 SET TRXN_EXTENSION_ID = c_trxn_entity_id(i)
719 WHERE id = oksline_id(i)
720 AND major_version = oksline_major_version(i);
721
722 Exception
723 WHEN OTHERS THEN
724 FND_FILE.PUT_LINE (FND_FILE.LOG, 'error in line history '||sqlerrm );
725 For i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
726 insert into oks_rule_error( chr_id,
727 cle_id , attribute_name, attribute_value,
728 major_version, rule_information_category )
729 values (okchdr_id(i), okcline_id(i), 'CC_NO', c_cc_number(i),
730 oksline_major_version(i), 'R12CC');
731 End Loop;
732
733 End;
734
735 End If; ----IF c_trxn_entity_id.count > 0 Then
736
737 COMMIT;
738 oksline_id.delete;
739 oksline_major_version.delete;
740 c_cc_number.delete;
741 c_cc_code.delete;
742 c_cc_exp_date.delete;
743 okcline_id.delete;
744 okchdr_id.delete;
745 c_customer_id.delete;
746 c_cust_account_id.delete;
747 c_cust_site_use_id.delete;
748 c_org_id.delete;
749 c_trxn_entity_id.delete;
750 c_cc_id.delete;
751 c_ext_payer_id.delete;
752 c_create_payer_flag.delete;
753 c_instr_assignment_id.delete;
754 c_hash1.delete;
755 c_hash2.delete;
756 c_card_range_id.delete;
757 c_sec_segment_id.delete;
758 c_cc_num_length.delete;
759 c_cc_unmask_digits.delete;
760 c_cc_masked_num.delete;
761
762 END LOOP;
763
764 IF (iby_cc_security_pub.encryption_enabled()) THEN
765 CLOSE oksline_cur_sec;
766 ELSE
767 CLOSE oksline_cur;
768 End if;
769
770 COMMIT;
771 ----
772 Exception
773 when others Then
774 FND_FILE.PUT_LINE (FND_FILE.LOG, 'main error line history'||sqlerrm );
775 null;
776
777 END MIGRATE_CC_LINEH;
778
779 PROCEDURE MIGRATE_CC_LINE(
780 ERRBUF OUT NOCOPY VARCHAR2,
781 RETCODE OUT NOCOPY NUMBER,
782 p_id_low IN NUMBER,
783 p_id_high IN NUMBER,
784 p_batchsize IN NUMBER) IS
785
786 l_user_id NUMBER;
787 c_cc_trxn_id l_num_tbl;
788 c_cc_id l_num_tbl;
789 c_cc_number l_chr_tbl;
790 c_cc_code l_chr_tbl;
791 c_cc_exp_date l_date_tbl;
792 okcline_id l_num_tbl;
793 oksline_id l_num_tbl;
794 okchdr_id l_num_tbl;
795 c_customer_id l_num_tbl;
796 c_cust_account_id l_num_tbl;
797 c_cust_site_use_id l_num_tbl;
798 c_org_id l_num_tbl;
799 c_additional_info l_chr_tbl;
800 c_trxn_entity_id l_num_tbl;
801 c_instr_assignment_id l_num_tbl;
802 c_ext_payer_id l_num_tbl;
803 c_create_payer_flag l_chr_tbl;
804 c_hash1 l_chr_tbl;
805 c_hash2 l_chr_tbl;
806 c_card_range_id l_num_tbl;
807 c_sec_segment_id l_num_tbl;
808 c_cc_num_length l_num_tbl;
809 c_cc_range_length l_num_tbl;
810 c_cc_unmask_digits l_chr_tbl;
811 c_cc_masked_num l_chr_tbl;
812
813
814 -- Cursor that queries all transactions needed to be migrated with encryption enabled
815
816 -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
817 -- the party contexts that are not in the external payer table can be identified.
818 -- 2. The credit card number needs to be numeric.
819 cursor oksline_cur_sec is
820 select
821 oksline.id oksline_id,
822 TRANSLATE(oksline.cc_no,'0: -_', '0'),
823 'UNKNOWN',
824 oksline.cc_expiry_date,
825 IV.hdr_id okchdr_id,
826 IV.line_id okcline_id,
827 IV.party_id,
828 IV.cust_acct_id,
829 IV.bill_to_site_use_id,
830 IV.authoring_org_id,
831 iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
832 iby_instr_s.nextval, -- the new credit card id
833 DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
834 PAYER.EXT_PAYER_ID),
835 DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
836 -- should create new external payer
837 IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
838 SEC.cc_number_hash1,
839 SEC.cc_number_hash2,
840 SEC.cc_issuer_range_id,
841 SEC.sec_segment_id,
842 SEC.cc_number_length,
843 SEC.cc_unmask_digits,
844 LPAD(sec.cc_unmask_digits, NVL(range.card_number_length, length(oksline.cc_no)),'X')
845 from OKS_K_LINES_B oksline,
846 (select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, line.cust_acct_id,
847 line.bill_to_site_use_id, line.id line_id
848 from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
849 where line.dnz_chr_id = hdr.id
850 and line.lse_id in (1,12,19,46)
851 and hz.cust_account_id = line.cust_acct_id) IV,
852 IBY_EXTERNAL_PAYERS_ALL payer,
853 IBY_SECURITY_SEGMENTS sec,
854 IBY_CC_ISSUER_RANGES rangE
855 where IV.line_id = oksline.cle_id
856 and oksline.cc_no is not null
857 and oksline.payment_type = 'CCR'
858 and oksline.cc_bank_acct_id is null
859 and oksline.trxn_extension_id is null
860 ---and IV.party_id = PAYER.PARTY_ID (+)
861 and IV.cust_acct_id = PAYER.CUST_ACCOUNT_ID(+)
862 and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
863 ---and IV.authoring_org_id = PAYER.ORG_ID(+)
864 and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
865 and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
866 and sec.sec_segment_id = IBY_CC_SECURITY_PUB.get_segment_id(oksline.cc_no)
867 and sec.CC_ISSUER_RANGE_ID = RANGE.CC_ISSUER_RANGE_ID (+)
868 and oksline.id BETWEEN p_id_low AND p_id_high;
869
870 -- Cursor that queries all transactions needed to be migrated with encryption disable
871
872 -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
873 -- the party contexts that are not in the external payer table can be identified.
874 -- 2. The credit card number needs to be numeric.
875 cursor oksline_cur is
876 select
877 oksline.id oksline_id,
878 TRANSLATE(oksline.cc_no,'0: -_', '0'),
879 'UNKNOWN',
880 oksline.cc_expiry_date,
881 IV.hdr_id okchdr_id,
882 IV.line_id okcline_id,
883 IV.party_id,
884 IV.cust_acct_id,
885 IV.bill_to_site_use_id,
886 IV.authoring_org_id,
887 IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
888 IBY_INSTR_S.nextval, -- the new credit card id
889 DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
890 PAYER.EXT_PAYER_ID),
891 DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
892 -- should create new external payer
893 IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
894 ----iby_fndcpt_setup_pub.get_hash(oksline.cc_no, FND_API.G_FALSE) cc_number_hash1,
895 ----iby_fndcpt_setup_pub.get_hash(oksline.cc_no, FND_API.G_TRUE) cc_number_hash2,
896 iby_fndcpt_setup_pub.get_hash(oksline.cc_no, 'F') cc_number_hash1,
897 iby_fndcpt_setup_pub.get_hash(oksline.cc_no, 'T') cc_number_hash2,
898 IBY_CC_VALIDATE.Get_CC_Issuer_Range(oksline.cc_no) cc_issuer_range_id,
899 Null sec_segment_id,
900 DECODE(IBY_CC_VALIDATE.Get_CC_Issuer_Range(oksline.cc_no), NULL,LENGTH(oksline.cc_no), NULL) cc_number_length,
901 SUBSTR(oksline.cc_no,GREATEST(-4,-LENGTH(oksline.cc_no))) cc_unmask_digits,
902 LPAD(SUBSTR(oksline.cc_no, GREATEST(-4,-LENGTH(oksline.cc_no))),
903 LENGTH(oksline.cc_no),
904 'X' ) masked_cc_number
905 from oks_k_lines_b oksline,
906 (select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, line.cust_acct_id,
907 line.bill_to_site_use_id, line.id line_id
908 from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
909 where line.dnz_chr_id = hdr.id
910 and line.lse_id in (1,12,19,46)
911 and hz.cust_account_id = line.cust_acct_id) IV,
912 IBY_EXTERNAL_PAYERS_ALL payer
913 where IV.line_id = oksline.cle_id
914 and oksline.cc_no is not null
915 and oksline.payment_type = 'CCR'
916 and oksline.cc_bank_acct_id is null
917 and oksline.trxn_extension_id is null
918 ---and IV.party_id = PAYER.PARTY_ID (+)
919 and IV.cust_acct_id = PAYER.CUST_ACCOUNT_ID(+)
920 and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
921 ---and IV.authoring_org_id = PAYER.ORG_ID(+)
922 and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
923 and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
924 and oksline.id BETWEEN p_id_low AND p_id_high;
925
926 l_return_status VARCHAR2(1);
927 l_msg_data VARCHAR2(2000);
928 l_msg_count NUMBER;
929
930 BEGIN
931
932
933 --open the cursor and migrate the stuff
934
935 l_user_id := NVL(fnd_global.user_id, -1);
936
937 IF (iby_cc_security_pub.encryption_enabled()) THEN
938 -- security enabled
939 OPEN oksline_cur_sec;
940 Else
941 OPEN oksline_cur;
942 End if;
943
944 LOOP
945 IF (iby_cc_security_pub.encryption_enabled()) THEN
946 FETCH oksline_cur_sec BULK COLLECT INTO
947 oksline_id, c_cc_number, c_cc_code, c_cc_exp_date,
948 okcline_id, okchdr_id, c_customer_id, c_cust_account_id,
949 c_cust_site_use_id, c_org_id, c_trxn_entity_id, c_cc_id, c_ext_payer_id,
950 c_create_payer_flag, c_instr_assignment_id, c_hash1, c_hash2, c_card_range_id,
951 c_sec_segment_id, c_cc_num_length, c_cc_unmask_digits, c_cc_masked_num
952 limit p_batchsize;
953
954 ELSE
955 FETCH oksline_cur BULK COLLECT INTO
956 oksline_id, c_cc_number, c_cc_code, c_cc_exp_date,
957 okcline_id, okchdr_id, c_customer_id, c_cust_account_id,
958 c_cust_site_use_id, c_org_id, c_trxn_entity_id, c_cc_id, c_ext_payer_id,
959 c_create_payer_flag, c_instr_assignment_id, c_hash1, c_hash2, c_card_range_id,
960 c_sec_segment_id, c_cc_num_length, c_cc_unmask_digits, c_cc_masked_num
961 limit p_batchsize;
962
963 END IF;
964
965
966 EXIT WHEN c_trxn_entity_id.count = 0 ;
967
968 IF c_trxn_entity_id.count > 0 Then
969
970 FND_FILE.PUT_LINE (FND_FILE.LOG, 'count = '||c_trxn_entity_id.count );
971 -- create new credit cards with single use only
972 Begin
973
974 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
975 INSERT INTO IBY_CREDITCARD
976 (CARD_OWNER_ID,
977 INSTRUMENT_TYPE,
978 PURCHASECARD_FLAG,
979 CARD_ISSUER_CODE,
980 ACTIVE_FLAG,
981 SINGLE_USE_FLAG,
982 EXPIRYDATE,
983 CHNAME,
984 CCNUMBER,
985 INSTRID,
986 CREATED_BY,
987 CREATION_DATE,
988 LAST_UPDATED_BY,
989 LAST_UPDATE_DATE,
990 LAST_UPDATE_LOGIN,
991 ENCRYPTED,
992 CC_NUMBER_HASH1,
993 CC_NUMBER_HASH2,
994 CC_ISSUER_RANGE_ID,
995 CC_NUM_SEC_SEGMENT_ID,
996 CARD_MASK_SETTING,
997 CARD_UNMASK_LENGTH,
998 CC_NUMBER_LENGTH,
999 MASKED_CC_NUMBER,
1000 OBJECT_VERSION_NUMBER
1001
1002 )
1003 VALUES(
1004 c_customer_id(i),
1005 'CREDITCARD',
1006 'N',
1007 c_cc_code(i),
1008 'Y',
1009 'Y',
1010 c_cc_exp_date(i),
1011 null,
1012 DECODE(c_sec_segment_id(i), NULL,c_cc_number(i), c_cc_unmask_digits(i)),
1013 c_cc_id(i),
1014 l_user_id,
1015 sysdate,
1016 l_user_id,
1017 sysdate,
1018 l_user_id,
1019 DECODE(c_sec_segment_id(i), NULL,'N','Y'),
1020 c_hash1(i),
1021 c_hash2(i),
1022 c_card_range_id(i),
1023 c_sec_segment_id(i),
1024 'DISPLAY_LAST',
1025 4,
1026 c_cc_num_length(i),
1027 c_cc_masked_num(i),
1028 1
1029 );
1030
1031 -- Now insert into the instrument use table
1032
1033 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
1034 INSERT INTO IBY_PMT_INSTR_USES_ALL
1035 (INSTRUMENT_PAYMENT_USE_ID,
1036 EXT_PMT_PARTY_ID,
1037 INSTRUMENT_TYPE,
1038 INSTRUMENT_ID,
1039 PAYMENT_FUNCTION,
1040 PAYMENT_FLOW,
1041 ORDER_OF_PREFERENCE,
1042 START_DATE,
1043 CREATED_BY,
1044 CREATION_DATE,
1045 LAST_UPDATED_BY,
1046 LAST_UPDATE_DATE,
1047 LAST_UPDATE_LOGIN,
1048 object_version_number)
1049 SELECT
1050 c_instr_assignment_id(i),
1051 EXT_PAYER_ID,
1052 'CREDITCARD',
1053 c_cc_id(i),
1054 'CUSTOMER_PAYMENT',
1055 'FUNDS_CAPTURE',
1056 1,
1057 sysdate,
1058 l_user_id,
1059 sysdate,
1060 l_user_id,
1061 sysdate,
1062 l_user_id,
1063 1
1064 FROM IBY_EXTERNAL_PAYERS_ALL payer
1065 WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
1066 AND payer.PARTY_ID = c_customer_id(i)
1067 AND payer.ORG_TYPE = 'OPERATING_UNIT'
1068 AND payer.ORG_ID = c_org_id(i)
1069 AND payer.CUST_ACCOUNT_ID = c_cust_account_id(i)
1070 AND payer.ACCT_SITE_USE_ID = c_cust_site_use_id(i)
1071 AND ROWNUM = 1;
1072
1073
1074 -- insert the transactions into IBY transaction extension table
1075 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
1076 INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
1077 (TRXN_EXTENSION_ID,
1078 PAYMENT_CHANNEL_CODE,
1079 INSTR_ASSIGNMENT_ID,
1080 ENCRYPTED,
1081 ORIGIN_APPLICATION_ID,
1082 ORDER_ID,
1083 CREATED_BY,
1084 CREATION_DATE,
1085 LAST_UPDATED_BY,
1086 LAST_UPDATE_DATE,
1087 LAST_UPDATE_LOGIN,
1088 OBJECT_VERSION_NUMBER)
1089 VALUES
1090 (c_trxn_entity_id(i),
1091 'CREDIT_CARD',
1092 c_instr_assignment_id(i),
1093 'N',
1094 515,
1095 oksline_id(i),
1096 l_user_id,
1097 sysdate,
1098 l_user_id,
1099 sysdate,
1100 l_user_id,
1101 1);
1102
1103 -- update the foreign key relationship
1104
1105 FORALL i in c_trxn_entity_id.first..c_trxn_entity_id.last
1106 UPDATE oks_k_lines_b
1107 SET TRXN_EXTENSION_ID = c_trxn_entity_id(i)
1108 WHERE id = oksline_id(i);
1109
1110 Exception
1111 WHEN OTHERS THEN
1112 FND_FILE.PUT_LINE (FND_FILE.LOG, 'error in insert lines '||sqlerrm );
1113 For i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
1114 insert into oks_rule_error( chr_id,
1115 cle_id , attribute_name, attribute_value,
1116 major_version, rule_information_category )
1117 values (okchdr_id(i), okcline_id(i), 'CC_NO', c_cc_number(i),
1118 NULL, 'R12CC');
1119 End Loop;
1120
1121 End;
1122
1123 COMMIT;
1124 --
1125 End If; ----IF c_trxn_entity_id.count > 0 Then
1126
1127 oksline_id.delete;
1128 c_cc_number.delete;
1129 c_cc_code.delete;
1130 c_cc_exp_date.delete;
1131 okcline_id.delete;
1132 okchdr_id.delete;
1133 c_customer_id.delete;
1134 c_cust_account_id.delete;
1135 c_cust_site_use_id.delete;
1136 c_org_id.delete;
1137 c_trxn_entity_id.delete;
1138 c_cc_id.delete;
1139 c_ext_payer_id.delete;
1140 c_create_payer_flag.delete;
1141 c_instr_assignment_id.delete;
1142 c_hash1.delete;
1143 c_hash2.delete;
1144 c_card_range_id.delete;
1145 c_sec_segment_id.delete;
1146 c_cc_num_length.delete;
1147 c_cc_unmask_digits.delete;
1148 c_cc_masked_num.delete;
1149
1150 END LOOP;
1151
1152 IF (iby_cc_security_pub.encryption_enabled()) THEN
1153 CLOSE oksline_cur_sec;
1154 ELSE
1155 CLOSE oksline_cur;
1156 End if;
1157
1158 COMMIT;
1159 ----
1160 Exception
1161 when others Then
1162 FND_FILE.PUT_LINE (FND_FILE.LOG, 'main error in lines '||sqlerrm );
1163 null;
1164
1165 END MIGRATE_CC_LINE;
1166
1167 PROCEDURE MIGRATE_CC_HDRH(
1168 ERRBUF OUT NOCOPY VARCHAR2,
1169 RETCODE OUT NOCOPY NUMBER,
1170 p_id_low IN NUMBER,
1171 p_id_high IN NUMBER,
1172 p_batchsize IN NUMBER ) IS
1173
1174 l_user_id NUMBER;
1175 c_cc_trxn_id l_num_tbl;
1176 c_cc_id l_num_tbl;
1177 c_cc_number l_chr_tbl;
1178 c_cc_code l_chr_tbl;
1179 c_cc_exp_date l_date_tbl;
1180 okcline_id l_num_tbl;
1181 okshdr_id l_num_tbl;
1182 oksline_major_version l_num_tbl;
1183 okchdr_id l_num_tbl;
1184 c_customer_id l_num_tbl;
1185 c_cust_account_id l_num_tbl;
1186 c_cust_site_use_id l_num_tbl;
1187 c_org_id l_num_tbl;
1188 c_additional_info l_chr_tbl;
1189 c_trxn_entity_id l_num_tbl;
1190 c_instr_assignment_id l_num_tbl;
1191 c_ext_payer_id l_num_tbl;
1192 c_create_payer_flag l_chr_tbl;
1193 c_hash1 l_chr_tbl;
1194 c_hash2 l_chr_tbl;
1195 c_card_range_id l_num_tbl;
1196 c_sec_segment_id l_num_tbl;
1197 c_cc_num_length l_num_tbl;
1198 c_cc_range_length l_num_tbl;
1199 c_cc_unmask_digits l_chr_tbl;
1200 c_cc_masked_num l_chr_tbl;
1201
1202
1203 -- Cursor that queries all transactions needed to be migrated with encryption enabled
1204
1205 -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
1206 -- the party contexts that are not in the external payer table can be identified.
1207 -- 2. The credit card number needs to be numeric.
1208
1209 cursor okshdr_cur_sec is
1210 select
1211 okshdr.id okshdr_id,
1212 okshdr.major_version okshdr_major_version,
1213 TRANSLATE(okshdr.cc_no,'0: -_', '0'),
1214 'UNKNOWN',
1215 okshdr.cc_expiry_date,
1216 IV.hdr_id okchdr_id,
1217 IV.party_id,
1218 IV.cust_account_id,
1219 IV.bill_to_site_use_id,
1220 IV.authoring_org_id,
1221 iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
1222 iby_instr_s.nextval, -- the new credit card id
1223 DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
1224 PAYER.EXT_PAYER_ID),
1225 DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
1226 -- should create new external payer
1227 IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
1228 SEC.cc_number_hash1,
1229 SEC.cc_number_hash2,
1230 SEC.cc_issuer_range_id,
1231 SEC.sec_segment_id,
1232 SEC.cc_number_length,
1233 SEC.cc_unmask_digits,
1234 LPAD(sec.cc_unmask_digits, NVL(range.card_number_length, length(okshdr.cc_no)),'X')
1235 from OKS_K_HEADERS_BH okshdr,
1236 (select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, hz.cust_account_id,
1237 hdr.bill_to_site_use_id
1238 from okc_k_headers_all_b hdr, hz_cust_accounts_all hz,
1239 hz_cust_site_uses_all site, hz_cust_acct_sites_all acct
1240 where hdr.bill_to_site_use_id = site.site_use_id
1241 and site.cust_acct_site_id = acct.cust_acct_site_id
1242 and acct.cust_account_id = hz.cust_account_id
1243 and site.site_use_code = 'BILL_TO' ) IV,
1244 IBY_EXTERNAL_PAYERS_ALL payer,
1245 IBY_SECURITY_SEGMENTS sec,
1246 IBY_CC_ISSUER_RANGES rangE
1247 where IV.hdr_id = okshdr.chr_id
1248 and okshdr.cc_no is not null
1249 and okshdr.payment_type = 'CCR'
1250 and okshdr.cc_bank_acct_id is null
1251 and okshdr.trxn_extension_id is null
1252 ---and IV.party_id = PAYER.PARTY_ID (+)
1253 and IV.cust_account_id = PAYER.CUST_ACCOUNT_ID(+)
1254 and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
1255 ---and IV.authoring_org_id = PAYER.ORG_ID(+)
1256 and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
1257 and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
1258 and sec.sec_segment_id = IBY_CC_SECURITY_PUB.get_segment_id(okshdr.cc_no)
1259 and sec.CC_ISSUER_RANGE_ID = RANGE.CC_ISSUER_RANGE_ID (+)
1260 and okshdr.id BETWEEN p_id_low AND p_id_high;
1261
1262 -- Cursor that queries all transactions needed to be migrated with encryption disable
1263
1264 -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
1265 -- the party contexts that are not in the external payer table can be identified.
1266 -- 2. The credit card number needs to be numeric.
1267 cursor okshdr_cur is
1268 select
1269 okshdr.id okshdr_id,
1270 okshdr.major_version okshdr_major_version,
1271 TRANSLATE(okshdr.cc_no,'0: -_', '0'),
1272 'UNKNOWN',
1273 okshdr.cc_expiry_date,
1274 IV.hdr_id okchdr_id,
1275 IV.party_id,
1276 IV.cust_account_id,
1277 IV.bill_to_site_use_id,
1278 IV.authoring_org_id,
1279 IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
1280 IBY_INSTR_S.nextval, -- the new credit card id
1281 DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
1282 PAYER.EXT_PAYER_ID),
1283 DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
1284 -- should create new external payer
1285 IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
1286 ----iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, FND_API.G_FALSE) cc_number_hash1,
1287 ----iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, FND_API.G_TRUE) cc_number_hash2,
1288 iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, 'F') cc_number_hash1,
1289 iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, 'T') cc_number_hash2,
1290 IBY_CC_VALIDATE.Get_CC_Issuer_Range(okshdr.cc_no) cc_issuer_range_id,
1291 Null sec_segment_id,
1292 DECODE(IBY_CC_VALIDATE.Get_CC_Issuer_Range(okshdr.cc_no), NULL,LENGTH(okshdr.cc_no), NULL) cc_number_length,
1293 SUBSTR(okshdr.cc_no,GREATEST(-4,-LENGTH(okshdr.cc_no))) cc_unmask_digits,
1294 LPAD(SUBSTR(okshdr.cc_no, GREATEST(-4,-LENGTH(okshdr.cc_no))),
1295 LENGTH(okshdr.cc_no),
1296 'X' ) masked_cc_number
1297 from oks_k_headers_bh okshdr,
1298 (select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, hz.cust_account_id,
1299 hdr.bill_to_site_use_id
1300 from okc_k_headers_all_b hdr, hz_cust_accounts_all hz,
1301 hz_cust_site_uses_all site, hz_cust_acct_sites_all acct
1302 where hdr.bill_to_site_use_id = site.site_use_id
1303 and site.cust_acct_site_id = acct.cust_acct_site_id
1304 and acct.cust_account_id = hz.cust_account_id
1305 and site.site_use_code = 'BILL_TO') IV,
1306 IBY_EXTERNAL_PAYERS_ALL payer
1307 where IV.hdr_id = okshdr.chr_id
1308 and okshdr.cc_no is not null
1309 ---and okshdr.id = 317191029854960778512632995409857241499
1310 and okshdr.payment_type = 'CCR'
1311 and okshdr.cc_bank_acct_id is null
1312 and okshdr.trxn_extension_id is null
1313 ---and IV.party_id = PAYER.PARTY_ID (+)
1314 and IV.cust_account_id = PAYER.CUST_ACCOUNT_ID(+)
1315 and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
1316 ---and IV.authoring_org_id = PAYER.ORG_ID(+)
1317 and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
1318 and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
1319 and okshdr.id BETWEEN p_id_low AND p_id_high;
1320
1321 l_return_status VARCHAR2(1);
1322 l_msg_data VARCHAR2(2000);
1323 l_msg_count NUMBER;
1324
1325 BEGIN
1326
1327
1328 --open the cursor and migrate the stuff
1329
1330 l_user_id := NVL(fnd_global.user_id, -1);
1331
1332 IF (iby_cc_security_pub.encryption_enabled()) THEN
1333 -- security enabled
1334 OPEN okshdr_cur_sec;
1335 Else
1336 OPEN okshdr_cur;
1337 End if;
1338
1339 LOOP
1340 IF (iby_cc_security_pub.encryption_enabled()) THEN
1341 FETCH okshdr_cur_sec BULK COLLECT INTO
1342 okshdr_id, oksline_major_version, c_cc_number, c_cc_code, c_cc_exp_date,
1343 okchdr_id, c_customer_id, c_cust_account_id,
1344 c_cust_site_use_id, c_org_id, c_trxn_entity_id, c_cc_id, c_ext_payer_id,
1345 c_create_payer_flag, c_instr_assignment_id, c_hash1, c_hash2, c_card_range_id,
1346 c_sec_segment_id, c_cc_num_length, c_cc_unmask_digits, c_cc_masked_num
1347 limit p_batchsize;
1348
1349 ELSE
1350 FETCH okshdr_cur BULK COLLECT INTO
1351 okshdr_id, oksline_major_version, c_cc_number, c_cc_code, c_cc_exp_date,
1352 okchdr_id, c_customer_id, c_cust_account_id,
1353 c_cust_site_use_id, c_org_id, c_trxn_entity_id, c_cc_id, c_ext_payer_id,
1354 c_create_payer_flag, c_instr_assignment_id, c_hash1, c_hash2, c_card_range_id,
1355 c_sec_segment_id, c_cc_num_length, c_cc_unmask_digits, c_cc_masked_num
1356 limit p_batchsize;
1357
1358 END IF;
1359
1360
1361 EXIT WHEN c_trxn_entity_id.count = 0 ;
1362
1363 IF c_trxn_entity_id.count > 0 Then
1364
1365 FND_FILE.PUT_LINE (FND_FILE.LOG, 'count = '||c_trxn_entity_id.count );
1366 -- create new credit cards with single use only
1367 Begin
1368
1369 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
1370 INSERT INTO IBY_CREDITCARD
1371 (CARD_OWNER_ID,
1372 INSTRUMENT_TYPE,
1373 PURCHASECARD_FLAG,
1374 CARD_ISSUER_CODE,
1375 ACTIVE_FLAG,
1376 SINGLE_USE_FLAG,
1377 EXPIRYDATE,
1378 CHNAME,
1379 CCNUMBER,
1380 INSTRID,
1381 CREATED_BY,
1382 CREATION_DATE,
1383 LAST_UPDATED_BY,
1384 LAST_UPDATE_DATE,
1385 LAST_UPDATE_LOGIN,
1386 ENCRYPTED,
1387 CC_NUMBER_HASH1,
1388 CC_NUMBER_HASH2,
1389 CC_ISSUER_RANGE_ID,
1390 CC_NUM_SEC_SEGMENT_ID,
1391 CARD_MASK_SETTING,
1392 CARD_UNMASK_LENGTH,
1393 CC_NUMBER_LENGTH,
1394 MASKED_CC_NUMBER,
1395 OBJECT_VERSION_NUMBER
1396
1397 )
1398 VALUES(
1399 c_customer_id(i),
1400 'CREDITCARD',
1401 'N',
1402 c_cc_code(i),
1403 'Y',
1404 'Y',
1405 c_cc_exp_date(i),
1406 null,
1407 DECODE(c_sec_segment_id(i), NULL,c_cc_number(i), c_cc_unmask_digits(i)),
1408 c_cc_id(i),
1409 l_user_id,
1410 sysdate,
1411 l_user_id,
1412 sysdate,
1413 l_user_id,
1414 DECODE(c_sec_segment_id(i), NULL,'N','Y'),
1415 c_hash1(i),
1416 c_hash2(i),
1417 c_card_range_id(i),
1418 c_sec_segment_id(i),
1419 'DISPLAY_LAST',
1420 4,
1421 c_cc_num_length(i),
1422 c_cc_masked_num(i),
1423 1
1424 );
1425
1426 -- Now insert into the instrument use table
1427
1428 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
1429 INSERT INTO IBY_PMT_INSTR_USES_ALL
1430 (INSTRUMENT_PAYMENT_USE_ID,
1431 EXT_PMT_PARTY_ID,
1432 INSTRUMENT_TYPE,
1433 INSTRUMENT_ID,
1434 PAYMENT_FUNCTION,
1435 PAYMENT_FLOW,
1436 ORDER_OF_PREFERENCE,
1437 START_DATE,
1438 CREATED_BY,
1439 CREATION_DATE,
1440 LAST_UPDATED_BY,
1441 LAST_UPDATE_DATE,
1442 LAST_UPDATE_LOGIN,
1443 object_version_number)
1444 SELECT
1445 c_instr_assignment_id(i),
1446 EXT_PAYER_ID,
1447 'CREDITCARD',
1448 c_cc_id(i),
1449 'CUSTOMER_PAYMENT',
1450 'FUNDS_CAPTURE',
1451 1,
1452 sysdate,
1453 l_user_id,
1454 sysdate,
1455 l_user_id,
1456 sysdate,
1457 l_user_id,
1458 1
1459 FROM IBY_EXTERNAL_PAYERS_ALL payer
1460 WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
1461 AND payer.PARTY_ID = c_customer_id(i)
1462 AND payer.ORG_TYPE = 'OPERATING_UNIT'
1463 AND payer.ORG_ID = c_org_id(i)
1464 AND payer.CUST_ACCOUNT_ID = c_cust_account_id(i)
1465 AND payer.ACCT_SITE_USE_ID = c_cust_site_use_id(i)
1466 AND ROWNUM = 1;
1467
1468
1469 -- insert the transactions into IBY transaction extension table
1470 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
1471 INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
1472 (TRXN_EXTENSION_ID,
1473 PAYMENT_CHANNEL_CODE,
1474 INSTR_ASSIGNMENT_ID,
1475 ENCRYPTED,
1476 ORIGIN_APPLICATION_ID,
1477 ORDER_ID,
1478 CREATED_BY,
1479 CREATION_DATE,
1480 LAST_UPDATED_BY,
1481 LAST_UPDATE_DATE,
1482 LAST_UPDATE_LOGIN,
1483 OBJECT_VERSION_NUMBER)
1484 VALUES
1485 (c_trxn_entity_id(i),
1486 'CREDIT_CARD',
1487 c_instr_assignment_id(i),
1488 'N',
1489 515,
1490 okshdr_id(i),
1491 l_user_id,
1492 sysdate,
1493 l_user_id,
1494 sysdate,
1495 l_user_id,
1496 1);
1497
1498 -- update the foreign key relationship
1499
1500 FORALL i in c_trxn_entity_id.first..c_trxn_entity_id.last
1501 UPDATE oks_k_headers_bh
1502 SET TRXN_EXTENSION_ID = c_trxn_entity_id(i)
1503 WHERE id = okshdr_id(i)
1504 AND major_version = oksline_major_version(i);
1505
1506 Exception
1507 WHEN OTHERS THEN
1508 FND_FILE.PUT_LINE (FND_FILE.LOG, 'error in hdr history '||sqlerrm );
1509 For i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
1510 insert into oks_rule_error( chr_id,
1511 cle_id , attribute_name, attribute_value,
1512 major_version, rule_information_category )
1513 values (okchdr_id(i), NULL, 'CC_NO', c_cc_number(i),
1514 oksline_major_version(i), 'R12CC');
1515 End Loop;
1516
1517 End;
1518
1519 End If; ----IF c_trxn_entity_id.count > 0 Then
1520
1521 COMMIT;
1522 okshdr_id.delete;
1523 oksline_major_version.delete;
1524 c_cc_number.delete;
1525 c_cc_code.delete;
1526 c_cc_exp_date.delete;
1527 okcline_id.delete;
1528 okchdr_id.delete;
1529 c_customer_id.delete;
1530 c_cust_account_id.delete;
1531 c_cust_site_use_id.delete;
1532 c_org_id.delete;
1533 c_trxn_entity_id.delete;
1534 c_cc_id.delete;
1535 c_ext_payer_id.delete;
1536 c_create_payer_flag.delete;
1537 c_instr_assignment_id.delete;
1538 c_hash1.delete;
1539 c_hash2.delete;
1540 c_card_range_id.delete;
1541 c_sec_segment_id.delete;
1542 c_cc_num_length.delete;
1543 c_cc_unmask_digits.delete;
1544 c_cc_masked_num.delete;
1545
1546 END LOOP;
1547
1548 IF (iby_cc_security_pub.encryption_enabled()) THEN
1549 CLOSE okshdr_cur_sec;
1550 ELSE
1551 CLOSE okshdr_cur;
1552 End if;
1553
1554 COMMIT;
1555 ----
1556 Exception
1557 when others Then
1558 FND_FILE.PUT_LINE (FND_FILE.LOG, 'main error hdr history'||sqlerrm );
1559 null;
1560
1561 END MIGRATE_CC_HDRH;
1562
1563 PROCEDURE MIGRATE_CC_HDR(
1564 ERRBUF OUT NOCOPY VARCHAR2,
1565 RETCODE OUT NOCOPY NUMBER,
1566 p_id_low IN NUMBER,
1567 p_id_high IN NUMBER,
1568 p_batchsize IN NUMBER) IS
1569
1570 l_user_id NUMBER;
1571 c_cc_trxn_id l_num_tbl;
1572 c_cc_id l_num_tbl;
1573 c_cc_number l_chr_tbl;
1574 c_cc_code l_chr_tbl;
1575 c_cc_exp_date l_date_tbl;
1576 okcline_id l_num_tbl;
1577 okshdr_id l_num_tbl;
1578 okchdr_id l_num_tbl;
1579 c_customer_id l_num_tbl;
1580 c_cust_account_id l_num_tbl;
1581 c_cust_site_use_id l_num_tbl;
1582 c_org_id l_num_tbl;
1583 c_additional_info l_chr_tbl;
1584 c_trxn_entity_id l_num_tbl;
1585 c_instr_assignment_id l_num_tbl;
1586 c_ext_payer_id l_num_tbl;
1587 c_create_payer_flag l_chr_tbl;
1588 c_hash1 l_chr_tbl;
1589 c_hash2 l_chr_tbl;
1590 c_card_range_id l_num_tbl;
1591 c_sec_segment_id l_num_tbl;
1592 c_cc_num_length l_num_tbl;
1593 c_cc_range_length l_num_tbl;
1594 c_cc_unmask_digits l_chr_tbl;
1595 c_cc_masked_num l_chr_tbl;
1596
1597
1598 -- Cursor that queries all transactions needed to be migrated with encryption enabled
1599
1600 -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
1601 -- the party contexts that are not in the external payer table can be identified.
1602 -- 2. The credit card number needs to be numeric.
1603
1604 cursor okshdr_cur_sec is
1605 select
1606 okshdr.id okshdr_id,
1607 TRANSLATE(okshdr.cc_no,'0: -_', '0'),
1608 'UNKNOWN',
1609 okshdr.cc_expiry_date,
1610 IV.hdr_id okchdr_id,
1611 IV.party_id,
1612 IV.cust_account_id,
1613 IV.bill_to_site_use_id,
1614 IV.authoring_org_id,
1615 iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
1616 iby_instr_s.nextval, -- the new credit card id
1617 DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
1618 PAYER.EXT_PAYER_ID),
1619 DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
1620 -- should create new external payer
1621 IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
1622 SEC.cc_number_hash1,
1623 SEC.cc_number_hash2,
1624 SEC.cc_issuer_range_id,
1625 SEC.sec_segment_id,
1626 SEC.cc_number_length,
1627 SEC.cc_unmask_digits,
1628 LPAD(sec.cc_unmask_digits, NVL(range.card_number_length, length(okshdr.cc_no)),'X')
1629 from OKS_K_HEADERS_B okshdr,
1630 (select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, hz.cust_account_id,
1631 hdr.bill_to_site_use_id
1632 from okc_k_headers_all_b hdr, hz_cust_accounts_all hz,
1633 hz_cust_site_uses_all site, hz_cust_acct_sites_all acct
1634 where hdr.bill_to_site_use_id = site.site_use_id
1635 and site.cust_acct_site_id = acct.cust_acct_site_id
1636 and acct.cust_account_id = hz.cust_account_id
1637 and site.site_use_code = 'BILL_TO') IV,
1638 IBY_EXTERNAL_PAYERS_ALL payer,
1639 IBY_SECURITY_SEGMENTS sec,
1640 IBY_CC_ISSUER_RANGES rangE
1641 where IV.hdr_id = okshdr.chr_id
1642 and okshdr.cc_no is not null
1643 and okshdr.payment_type = 'CCR'
1644 and okshdr.cc_bank_acct_id is null
1645 and okshdr.trxn_extension_id is null
1646 ---and IV.party_id = PAYER.PARTY_ID (+)
1647 and IV.cust_account_id = PAYER.CUST_ACCOUNT_ID(+)
1648 and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
1649 ---and IV.authoring_org_id = PAYER.ORG_ID(+)
1650 and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
1651 and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
1652 and sec.sec_segment_id = IBY_CC_SECURITY_PUB.get_segment_id(okshdr.cc_no)
1653 and sec.CC_ISSUER_RANGE_ID = RANGE.CC_ISSUER_RANGE_ID (+)
1654 and okshdr.id BETWEEN p_id_low AND p_id_high;
1655
1656 -- Cursor that queries all transactions needed to be migrated with encryption disable
1657
1658 -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
1659 -- the party contexts that are not in the external payer table can be identified.
1660 -- 2. The credit card number needs to be numeric.
1661 cursor okshdr_cur is
1662 select
1663 okshdr.id okshdr_id,
1664 TRANSLATE(okshdr.cc_no,'0: -_', '0'),
1665 'UNKNOWN',
1666 okshdr.cc_expiry_date,
1667 IV.hdr_id okchdr_id,
1668 IV.party_id,
1669 IV.cust_account_id,
1670 IV.bill_to_site_use_id,
1671 IV.authoring_org_id,
1672 IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
1673 IBY_INSTR_S.nextval, -- the new credit card id
1674 DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
1675 PAYER.EXT_PAYER_ID),
1676 DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
1677 -- should create new external payer
1678 IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
1679 ----iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, FND_API.G_FALSE) cc_number_hash1,
1680 ----iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, FND_API.G_TRUE) cc_number_hash2,
1681 iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, 'F') cc_number_hash1,
1682 iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, 'T') cc_number_hash2,
1683 IBY_CC_VALIDATE.Get_CC_Issuer_Range(okshdr.cc_no) cc_issuer_range_id,
1684 Null sec_segment_id,
1685 DECODE(IBY_CC_VALIDATE.Get_CC_Issuer_Range(okshdr.cc_no), NULL,LENGTH(okshdr.cc_no), NULL) cc_number_length,
1686 SUBSTR(okshdr.cc_no,GREATEST(-4,-LENGTH(okshdr.cc_no))) cc_unmask_digits,
1687 LPAD(SUBSTR(okshdr.cc_no, GREATEST(-4,-LENGTH(okshdr.cc_no))),
1688 LENGTH(okshdr.cc_no),
1689 'X' ) masked_cc_number
1690 from oks_k_headers_b okshdr,
1691 (select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, hz.cust_account_id,
1692 hdr.bill_to_site_use_id
1693 from okc_k_headers_all_b hdr, hz_cust_accounts_all hz,
1694 hz_cust_site_uses_all site, hz_cust_acct_sites_all acct
1695 where hdr.bill_to_site_use_id = site.site_use_id
1696 and site.cust_acct_site_id = acct.cust_acct_site_id
1697 and acct.cust_account_id = hz.cust_account_id
1698 and site.site_use_code = 'BILL_TO') IV,
1699 IBY_EXTERNAL_PAYERS_ALL payer
1700 where IV.hdr_id = okshdr.chr_id
1701 and okshdr.cc_no is not null
1702 ---and okshdr.id = 317191029854960778512632995409857241499
1703 and okshdr.payment_type = 'CCR'
1704 and okshdr.cc_bank_acct_id is null
1705 and okshdr.trxn_extension_id is null
1706 ---and IV.party_id = PAYER.PARTY_ID (+)
1707 and IV.cust_account_id = PAYER.CUST_ACCOUNT_ID(+)
1708 and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
1709 ---and IV.authoring_org_id = PAYER.ORG_ID(+)
1710 and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
1711 and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
1712 and okshdr.id BETWEEN p_id_low AND p_id_high;
1713 l_return_status VARCHAR2(1);
1714 l_msg_data VARCHAR2(2000);
1715 l_msg_count NUMBER;
1716
1717 BEGIN
1718
1719
1720 --open the cursor and migrate the stuff
1721
1722 l_user_id := NVL(fnd_global.user_id, -1);
1723
1724 IF (iby_cc_security_pub.encryption_enabled()) THEN
1725 -- security enabled
1726 OPEN okshdr_cur_sec;
1727 Else
1728 OPEN okshdr_cur;
1729 End if;
1730
1731 LOOP
1732 IF (iby_cc_security_pub.encryption_enabled()) THEN
1733 FETCH okshdr_cur_sec BULK COLLECT INTO
1734 okshdr_id, c_cc_number, c_cc_code, c_cc_exp_date,
1735 okchdr_id, c_customer_id, c_cust_account_id,
1736 c_cust_site_use_id, c_org_id, c_trxn_entity_id, c_cc_id, c_ext_payer_id,
1737 c_create_payer_flag, c_instr_assignment_id, c_hash1, c_hash2, c_card_range_id,
1738 c_sec_segment_id, c_cc_num_length, c_cc_unmask_digits, c_cc_masked_num
1739 limit p_batchsize;
1740
1741 ELSE
1742 FETCH okshdr_cur BULK COLLECT INTO
1743 okshdr_id, c_cc_number, c_cc_code, c_cc_exp_date,
1744 okchdr_id, c_customer_id, c_cust_account_id,
1745 c_cust_site_use_id, c_org_id, c_trxn_entity_id, c_cc_id, c_ext_payer_id,
1746 c_create_payer_flag, c_instr_assignment_id, c_hash1, c_hash2, c_card_range_id,
1747 c_sec_segment_id, c_cc_num_length, c_cc_unmask_digits, c_cc_masked_num
1748 limit p_batchsize;
1749
1750 END IF;
1751
1752
1753 EXIT WHEN c_trxn_entity_id.count = 0 ;
1754
1755 IF c_trxn_entity_id.count > 0 Then
1756
1757 FND_FILE.PUT_LINE (FND_FILE.LOG, 'count = '||c_trxn_entity_id.count );
1758 -- create new credit cards with single use only
1759 Begin
1760
1761 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
1762 INSERT INTO IBY_CREDITCARD
1763 (CARD_OWNER_ID,
1764 INSTRUMENT_TYPE,
1765 PURCHASECARD_FLAG,
1766 CARD_ISSUER_CODE,
1767 ACTIVE_FLAG,
1768 SINGLE_USE_FLAG,
1769 EXPIRYDATE,
1770 CHNAME,
1771 CCNUMBER,
1772 INSTRID,
1773 CREATED_BY,
1774 CREATION_DATE,
1775 LAST_UPDATED_BY,
1776 LAST_UPDATE_DATE,
1777 LAST_UPDATE_LOGIN,
1778 ENCRYPTED,
1779 CC_NUMBER_HASH1,
1780 CC_NUMBER_HASH2,
1781 CC_ISSUER_RANGE_ID,
1782 CC_NUM_SEC_SEGMENT_ID,
1783 CARD_MASK_SETTING,
1784 CARD_UNMASK_LENGTH,
1785 CC_NUMBER_LENGTH,
1786 MASKED_CC_NUMBER,
1787 OBJECT_VERSION_NUMBER
1788
1789 )
1790 VALUES(
1791 c_customer_id(i),
1792 'CREDITCARD',
1793 'N',
1794 c_cc_code(i),
1795 'Y',
1796 'Y',
1797 c_cc_exp_date(i),
1798 null,
1799 DECODE(c_sec_segment_id(i), NULL,c_cc_number(i), c_cc_unmask_digits(i)),
1800 c_cc_id(i),
1801 l_user_id,
1802 sysdate,
1803 l_user_id,
1804 sysdate,
1805 l_user_id,
1806 DECODE(c_sec_segment_id(i), NULL,'N','Y'),
1807 c_hash1(i),
1808 c_hash2(i),
1809 c_card_range_id(i),
1810 c_sec_segment_id(i),
1811 'DISPLAY_LAST',
1812 4,
1813 c_cc_num_length(i),
1814 c_cc_masked_num(i),
1815 1
1816 );
1817
1818 -- Now insert into the instrument use table
1819
1820 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
1821 INSERT INTO IBY_PMT_INSTR_USES_ALL
1822 (INSTRUMENT_PAYMENT_USE_ID,
1823 EXT_PMT_PARTY_ID,
1824 INSTRUMENT_TYPE,
1825 INSTRUMENT_ID,
1826 PAYMENT_FUNCTION,
1827 PAYMENT_FLOW,
1828 ORDER_OF_PREFERENCE,
1829 START_DATE,
1830 CREATED_BY,
1831 CREATION_DATE,
1832 LAST_UPDATED_BY,
1833 LAST_UPDATE_DATE,
1834 LAST_UPDATE_LOGIN,
1835 object_version_number)
1836 SELECT
1837 c_instr_assignment_id(i),
1838 EXT_PAYER_ID,
1839 'CREDITCARD',
1840 c_cc_id(i),
1841 'CUSTOMER_PAYMENT',
1842 'FUNDS_CAPTURE',
1843 1,
1844 sysdate,
1845 l_user_id,
1846 sysdate,
1847 l_user_id,
1848 sysdate,
1849 l_user_id,
1850 1
1851 FROM IBY_EXTERNAL_PAYERS_ALL payer
1852 WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
1853 AND payer.PARTY_ID = c_customer_id(i)
1854 AND payer.ORG_TYPE = 'OPERATING_UNIT'
1855 AND payer.ORG_ID = c_org_id(i)
1856 AND payer.CUST_ACCOUNT_ID = c_cust_account_id(i)
1857 AND payer.ACCT_SITE_USE_ID = c_cust_site_use_id(i)
1858 AND ROWNUM = 1;
1859
1860
1861 -- insert the transactions into IBY transaction extension table
1862 FORALL i IN c_trxn_entity_id.first..c_trxn_entity_id.last
1863 INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
1864 (TRXN_EXTENSION_ID,
1865 PAYMENT_CHANNEL_CODE,
1866 INSTR_ASSIGNMENT_ID,
1867 ENCRYPTED,
1868 ORIGIN_APPLICATION_ID,
1869 ORDER_ID,
1870 CREATED_BY,
1871 CREATION_DATE,
1872 LAST_UPDATED_BY,
1873 LAST_UPDATE_DATE,
1874 LAST_UPDATE_LOGIN,
1875 OBJECT_VERSION_NUMBER)
1876 VALUES
1877 (c_trxn_entity_id(i),
1878 'CREDIT_CARD',
1879 c_instr_assignment_id(i),
1880 'N',
1881 515,
1882 okshdr_id(i),
1883 l_user_id,
1884 sysdate,
1885 l_user_id,
1886 sysdate,
1887 l_user_id,
1888 1);
1889
1890 -- update the foreign key relationship
1891
1892 FORALL i in c_trxn_entity_id.first..c_trxn_entity_id.last
1893 UPDATE oks_k_lines_b
1894 SET TRXN_EXTENSION_ID = c_trxn_entity_id(i)
1895 WHERE id = okshdr_id(i);
1896
1897 Exception
1898 WHEN OTHERS THEN
1899 FND_FILE.PUT_LINE (FND_FILE.LOG, 'error in insert hdr '||sqlerrm );
1900 For i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
1901 insert into oks_rule_error( chr_id,
1902 cle_id , attribute_name, attribute_value,
1903 major_version, rule_information_category )
1904 values (okchdr_id(i), NULL, 'CC_NO', c_cc_number(i),
1905 NULL, 'R12CC');
1906 End Loop;
1907
1908 End;
1909
1910 COMMIT;
1911 --
1912 End If; ----IF c_trxn_entity_id.count > 0 Then
1913
1914 okshdr_id.delete;
1915 c_cc_number.delete;
1916 c_cc_code.delete;
1917 c_cc_exp_date.delete;
1918 okcline_id.delete;
1919 okchdr_id.delete;
1920 c_customer_id.delete;
1921 c_cust_account_id.delete;
1922 c_cust_site_use_id.delete;
1923 c_org_id.delete;
1924 c_trxn_entity_id.delete;
1925 c_cc_id.delete;
1926 c_ext_payer_id.delete;
1927 c_create_payer_flag.delete;
1928 c_instr_assignment_id.delete;
1929 c_hash1.delete;
1930 c_hash2.delete;
1931 c_card_range_id.delete;
1932 c_sec_segment_id.delete;
1933 c_cc_num_length.delete;
1934 c_cc_unmask_digits.delete;
1935 c_cc_masked_num.delete;
1936
1937 END LOOP;
1938
1939 IF (iby_cc_security_pub.encryption_enabled()) THEN
1940 CLOSE okshdr_cur_sec;
1941 ELSE
1942 CLOSE okshdr_cur;
1943 End if;
1944
1945 COMMIT;
1946 ----
1947 Exception
1948 when others Then
1949 FND_FILE.PUT_LINE (FND_FILE.LOG, 'main error in hdr '||sqlerrm );
1950 null;
1951
1952 END MIGRATE_CC_HDR;
1953
1954
1955 PROCEDURE generate_report (
1956 errbuf OUT NOCOPY VARCHAR2,
1957 retcode OUT NOCOPY NUMBER
1958 )
1959 IS
1960 CURSOR get_hdr_csr
1961 IS
1962 SELECT okh.chr_id,
1963 kh.contract_number ||' ' ||kh.contract_number_modifier
1964 contract_number,
1965 kh.start_date start_date,
1966 kh.end_date end_date,
1967 kh.date_terminated date_terminated,
1968 st.meaning status,
1969 ore.attribute_value cc_no,
1970 ore.cc_expiry_date cc_exp_date,
1971 (SELECT party_name
1972 FROM hz_parties a,
1973 hz_cust_accounts b
1974 WHERE a.party_id = b.party_id
1975 AND b.cust_account_id = cust_acct_id) party_name
1976 FROM oks_rule_error ore,
1977 oks_k_headers_b okh,
1978 okc_k_headers_all_b kh,
1979 okc_statuses_v st
1980 WHERE ore.RULE_INFORMATION_CATEGORY = 'R12CC'
1981 AND ore.chr_id = kh.ID
1982 AND ore.cle_id IS NULL
1983 AND kh.ID = okh.chr_id
1984 AND st.code = kh.sts_code;
1985
1986 CURSOR get_hdr_hist_csr
1987 IS
1988 SELECT kh.ID,
1989 kh.contract_number||' '||kh.contract_number_modifier contract_number,
1990 kh.major_version,
1991 kh.start_date start_date,
1992 kh.end_date end_date,
1993 kh.date_terminated date_terminated,
1994 st.meaning status,
1995 ore.attribute_value cc_no,
1996 ore.cc_expiry_date cc_exp_date,
1997 (SELECT party_name
1998 FROM hz_parties a,
1999 hz_cust_accounts b
2000 WHERE a.party_id = b.party_id
2001 AND b.cust_account_id = cust_acct_id) party_name
2002 FROM oks_rule_error ore,
2003 okc_k_headers_all_bh kh,
2004 okc_statuses_v st
2005 WHERE ore.RULE_INFORMATION_CATEGORY = 'R12CC'
2006 AND ore.chr_id = kh.ID
2007 AND ore.cle_id IS NULL
2008 AND ore.major_version IS NOT NULL
2009 AND st.code = kh.STS_CODE;
2010
2011 CURSOR get_Line_csr
2012 IS
2013 SELECT ore.cle_id,
2014 kh.contract_number ||' ' ||kh.contract_number_modifier
2015 contract_number,
2016 lc.line_number,
2017 lc.start_date start_date,
2018 lc.end_date end_date,
2019 lc.date_terminated date_terminated,
2020 st.meaning status,
2021 ore.attribute_value cc_no,
2022 ore.cc_expiry_date cc_exp_date,
2023 (SELECT party_name
2024 FROM hz_parties a,
2025 hz_cust_accounts b
2026 WHERE a.party_id = b.party_id
2027 AND b.cust_account_id = lc.cust_acct_id) party_name,
2028 (SELECT decode(fnd_profile.value('OKS_ITEM_DISPLAY_PREFERENCE'),'DISPLAY_DESC',
2029 B.CONCATENATED_SEGMENTS ,T.DESCRIPTION )
2030 FROM MTL_SYSTEM_ITEMS_B_KFV B,
2031 MTL_SYSTEM_ITEMS_TL T
2032 WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
2033 AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
2034 AND T.LANGUAGE = userenv('LANG')
2035 AND B.INVENTORY_ITEM_ID = object1_id1
2036 AND ROWNUM < 2) service_name
2037
2038 FROM oks_rule_error ore,
2039 okc_k_headers_all_b kh,
2040 okc_statuses_v st,
2041 oks_k_lines_b ls,
2042 okc_k_lines_b lc,
2043 okc_k_items it
2044 WHERE ore.RULE_INFORMATION_CATEGORY = 'R12CC'
2045 AND ore.cle_id = ls.cle_ID
2046 AND ore.CHR_id = ls.DNZ_CHR_ID
2047 AND ls.cle_id = lc.ID
2048 AND ls.dnz_chr_id = kh.id
2049 AND lc.STS_CODE = st.code
2050 AND it.cle_id = lc.ID
2051 AND it.jtot_object1_code = 'OKX_SERVICE';
2052
2053 CURSOR get_Line_hist_csr
2054 IS
2055 SELECT ore.cle_id,
2056 kh.contract_number ||' ' ||kh.contract_number_modifier
2057 contract_number,
2058 lc.major_version,
2059 lc.line_number,
2060 lc.start_date start_date,
2061 lc.end_date end_date,
2062 lc.date_terminated date_terminated,
2063 st.meaning status,
2064 ore.attribute_value cc_no,
2065 ore.cc_expiry_date cc_exp_date,
2066 (SELECT party_name
2067 FROM hz_parties a,
2068 hz_cust_accounts b
2069 WHERE a.party_id = b.party_id
2070 AND b.cust_account_id = lc.cust_acct_id) party_name,
2071 (SELECT decode(fnd_profile.value('OKS_ITEM_DISPLAY_PREFERENCE'),'DISPLAY_DESC',
2072 B.CONCATENATED_SEGMENTS ,T.DESCRIPTION )
2073 FROM MTL_SYSTEM_ITEMS_B_KFV B,
2074 MTL_SYSTEM_ITEMS_TL T
2075 WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
2076 AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
2077 AND T.LANGUAGE = userenv('LANG')
2078 AND B.INVENTORY_ITEM_ID = object1_id1
2079 AND ROWNUM < 2) service_name
2080
2081 FROM oks_rule_error ore,
2082 okc_k_headers_all_b kh,
2083 okc_statuses_v st,
2084 okc_k_lines_bh lc,
2085 okc_k_items it
2086 WHERE ore.RULE_INFORMATION_CATEGORY = 'R12CC'
2087 AND ore.cle_id = lc.id
2088 AND kh.id = lc.dnz_chr_id
2089 AND lc.STS_CODE = st.code
2090 AND it.cle_id = lc.ID
2091 AND it.jtot_object1_code = 'OKX_SERVICE';
2092
2093 contract_number okc_datatypes.var240tabtyp;
2094 contract_id okc_datatypes.numbertabtyp;
2095 contract_version okc_datatypes.numbertabtyp;
2096 line_version okc_datatypes.numbertabtyp;
2097 contract_sdate okc_datatypes.datetabtyp;
2098 contract_edate okc_datatypes.datetabtyp;
2099 date_terminated okc_datatypes.datetabtyp;
2100 contract_status okc_datatypes.var30tabtyp;
2101 cc_number okc_datatypes.var120tabtyp;
2102 cc_exp_date okc_datatypes.datetabtyp;
2103 party_name okc_datatypes.var450tabtyp;
2104 line_number okc_datatypes.numbertabtyp;
2105 service_name okc_datatypes.var240tabtyp;
2106 line_id okc_datatypes.numbertabtyp;
2107 line_sdate okc_datatypes.datetabtyp;
2108 line_edate okc_datatypes.datetabtyp;
2109 line_status okc_datatypes.var30tabtyp;
2110 l_cont_length NUMBER;
2111 l_party_length NUMBER;
2112 l_service_length NUMBER;
2113 l_max_length NUMBER;
2114 l_rel_name VARCHAR2(60);
2115 l_other VARCHAR2(60);
2116 l_ret_val BOOLEAN;
2117 l_dash_string VARCHAR2(500) := '----------';
2118 l_empty_string VARCHAR2(500) := ' ';
2119 BEGIN
2120 l_ret_val := FND_RELEASE.get_release(
2121 RELEASE_NAME => l_rel_name,
2122 OTHER_RELEASE_INFO => l_other);
2123
2124 IF NOT l_ret_val THEN
2125 RAISE G_EXCEPTION_HALT_VALIDATION;
2126 END IF;
2127
2128 --DBMS_OUTPUT.put_line('release = '|| l_rel_name);
2129 --DBMS_OUTPUT.put_line(substr('Value of l_other='||l_other,1,255));
2130
2131 FND_FILE.put_line(FND_FILE.OUTPUT,' Credit Card Migration Error Report ');
2132 FND_FILE.put_line(FND_FILE.OUTPUT,' ********************************** ');
2133 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2134 -- FND_FILE.put_line(FND_FILE.OUTPUT,'Contract Headers');
2135 fnd_file.put_line(FND_FILE.OUTPUT,FND_MESSAGE.get_string('OKS','OKS_RR_CONTRACT_HDR'));
2136 FND_FILE.put_line(FND_FILE.OUTPUT,l_dash_string
2137 || SUBSTR(l_dash_string,1,6));
2138
2139 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2140 -- FND_FILE.put_line(FND_FILE.OUTPUT,'Contract Number Billto Status Start Date End Date Date Terminated Credit Card Number Expiration Date');
2141 FND_FILE.put_line(FND_FILE.OUTPUT,
2142 RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_CONTRACT_NUM'),25)
2143 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_BILL_TO'),25)
2144 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_STATUS'),10)
2145 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_START_DATE'),14)
2146 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_END_DATE'),13)
2147 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_DATE_TERM'),18)
2148 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_CCR_NUM'),20)
2149 ||FND_MESSAGE.get_string('OKS','OKS_RR_EXP_DATE'));
2150 FND_FILE.put_line(FND_FILE.OUTPUT,l_dash_string||l_dash_string||l_dash_string
2151 ||l_dash_string||l_dash_string||l_dash_string
2152 ||l_dash_string||l_dash_string||l_dash_string
2153 ||l_dash_string||l_dash_string||l_dash_string
2154 ||l_dash_string||l_dash_string);
2155
2156 OPEN get_hdr_csr;
2157
2158 FETCH get_hdr_csr
2159 BULK COLLECT INTO contract_id,
2160 contract_number,
2161 contract_sdate,
2162 contract_edate,
2163 date_terminated,
2164 contract_status,
2165 cc_number,
2166 cc_exp_date,
2167 party_name;
2168
2169 CLOSE get_hdr_csr;
2170
2171 IF contract_id.COUNT > 0
2172 THEN
2173 FOR i IN 1 .. contract_id.COUNT
2174 LOOP
2175 FND_FILE.put(FND_FILE.OUTPUT,RPAD (SUBSTR(contract_number (i),1, 20),25,' ' ));
2176 IF party_name (i) IS NOT NULL
2177 THEN
2178 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (party_name (i), 1, 20),25, ' '));
2179 ELSE
2180
2181 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2182 ||SUBSTR(l_empty_string,1,5));
2183 END IF;
2184 FND_FILE.put(FND_FILE.OUTPUT,RPAD(contract_status (i),10, ' '));
2185 FND_FILE.put(FND_FILE.OUTPUT,RPAD(contract_sdate (i), 14, ' '));
2186 FND_FILE.put(FND_FILE.OUTPUT,RPAD(contract_edate (i), 13, ' '));
2187 IF date_terminated(i) IS NOT NULL
2188 THEN
2189 FND_FILE.put(FND_FILE.OUTPUT,RPAD(date_terminated(i),18, ' '));
2190 ELSE
2191 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||SUBSTR(l_empty_string,1,8));
2192 END IF;
2193 FND_FILE.put(FND_FILE.OUTPUT,RPAD(cc_number(i), 20,' '));
2194 FND_FILE.put(FND_FILE.OUTPUT,RPAD(cc_exp_date(i), 15, ' '));
2195
2196 l_cont_length := LENGTH (contract_number(i));
2197 l_party_length := NVL(LENGTH (party_name(i)),0);
2198
2199 IF l_cont_length >= l_party_length
2200 THEN
2201 l_max_length := l_cont_length;
2202 ELSE
2203 l_max_length := l_party_length;
2204 END IF;
2205
2206 FOR j IN 1 ..FLOOR (l_max_length / 20)
2207 LOOP
2208 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2209 IF contract_number(i) IS NOT NULL AND (SUBSTR (contract_number (i), (j*20+1),20)) IS NOT NULL
2210 THEN
2211 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (contract_number (i), (j*20+1),20),25, ' '));
2212 ELSE
2213 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2214 ||SUBSTR(l_empty_string,1,5));
2215 END IF;
2216 IF party_name(i) IS NOT NULL AND (SUBSTR (party_name (i), (j*20+1),20)) IS NOT NULL
2217 THEN
2218 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (party_name (i), (j*20+1),20),25, ' '));
2219 ELSE
2220 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2221 ||SUBSTR(l_empty_string,1,5));
2222 END IF;
2223 END LOOP;
2224 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2225 END LOOP;
2226 ELSE
2227 fnd_file.put_line(FND_FILE.OUTPUT,FND_MESSAGE.get_string('OKS','OKS_RR_MIG_SUCCESS'));
2228 END IF;
2229
2230 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2231 -- FND_FILE.put_line(FND_FILE.OUTPUT,'Contract Headers History');
2232 FND_FILE.put_line(FND_FILE.OUTPUT,FND_MESSAGE.get_string('OKS','OKS_RR_HDR_HIST'));
2233 FND_FILE.put_line(FND_FILE.OUTPUT,l_dash_string||l_dash_string
2234 ||SUBSTR(l_dash_string,1,4));
2235 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2236 -- FND_FILE.put_line(FND_FILE.OUTPUT,'Contract Number Major Version Billto Status Start Date End Date Date Terminated Credit Card Number Expiration Date');
2237 FND_FILE.put_line(FND_FILE.OUTPUT,
2238 RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_CONTRACT_NUM'),25)
2239 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_MAJOR_VER'),17)
2240 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_BILL_TO'),25)
2241 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_STATUS'),10)
2242 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_START_DATE'),14)
2243 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_END_DATE'),13)
2244 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_DATE_TERM'),18)
2245 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_CCR_NUM'),20)
2246 ||FND_MESSAGE.get_string('OKS','OKS_RR_EXP_DATE'));
2247 FND_FILE.put_line(FND_FILE.OUTPUT,l_dash_string||l_dash_string||l_dash_string
2248 ||l_dash_string||l_dash_string||l_dash_string
2249 ||l_dash_string||l_dash_string||l_dash_string
2250 ||l_dash_string||l_dash_string||l_dash_string
2251 ||l_dash_string||l_dash_string||l_dash_string
2252 ||SUBSTR(l_dash_string,1,7));
2253 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2254
2255 OPEN get_hdr_hist_csr;
2256 FETCH get_hdr_hist_csr
2257 BULK COLLECT INTO contract_id,
2258 contract_number,
2259 contract_version,
2260 contract_sdate,
2261 contract_edate,
2262 date_terminated,
2263 contract_status,
2264 cc_number,
2265 cc_exp_date,
2266 party_name;
2267
2268 CLOSE get_hdr_hist_csr;
2269
2270 IF contract_id.COUNT > 0
2271 THEN
2272 FOR i IN 1 .. contract_id.COUNT
2273 LOOP
2274 FND_FILE.put(FND_FILE.OUTPUT,RPAD (SUBSTR(contract_number (i),1, 20),25,' ' ));
2275 FND_FILE.put(FND_FILE.OUTPUT,RPAD (contract_version (i),17, ' '));
2276 IF party_name (i) IS NOT NULL
2277 THEN
2278 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (party_name (i), 1, 20),25, ' '));
2279 ELSE
2280 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2281 ||SUBSTR(l_empty_string,1,5));
2282 END IF;
2283 FND_FILE.put(FND_FILE.OUTPUT,RPAD(contract_status (i),10, ' '));
2284 FND_FILE.put(FND_FILE.OUTPUT,RPAD(contract_sdate (i), 14, ' '));
2285 FND_FILE.put(FND_FILE.OUTPUT,RPAD(contract_edate (i), 13, ' '));
2286 IF date_terminated(i) IS NOT NULL
2287 THEN
2288 FND_FILE.put(FND_FILE.OUTPUT,RPAD(date_terminated(i),18, ' '));
2289 ELSE
2290 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||SUBSTR(l_empty_string,1,8));
2291 END IF;
2292 FND_FILE.put(FND_FILE.OUTPUT,RPAD(cc_number(i), 20,' '));
2293 FND_FILE.put(FND_FILE.OUTPUT,RPAD(cc_exp_date(i), 15, ' '));
2294
2295 l_cont_length := LENGTH (contract_number(i));
2296 l_party_length := NVL(LENGTH (party_name(i)),0);
2297
2298 IF l_cont_length >= l_party_length
2299 THEN
2300 l_max_length := l_cont_length;
2301 ELSE
2302 l_max_length := l_party_length;
2303 END IF;
2304
2305 FOR j IN 1 ..FLOOR (l_max_length / 20)
2306 LOOP
2307 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2308 IF contract_number(i) IS NOT NULL AND (SUBSTR (contract_number (i), (j*20+1),20)) IS NOT NULL
2309 THEN
2310 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (contract_number (i), (j*20+1),20),25, ' '));
2311 ELSE
2312 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2313 ||SUBSTR(l_empty_string,1,5));
2314 END IF;
2315 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||SUBSTR(l_empty_string,1,7));
2316 IF party_name(i) IS NOT NULL AND (SUBSTR (party_name (i), (j*20+1),20)) IS NOT NULL
2317 THEN
2318 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (party_name (i), (j*20+1),20),25, ' '));
2319 ELSE
2320 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2321 ||SUBSTR(l_empty_string,1,5));
2322 END IF;
2323 END LOOP;
2324 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2325 END LOOP;
2326 ELSE
2327 fnd_file.put_line(FND_FILE.OUTPUT,FND_MESSAGE.get_string('OKS','OKS_RR_MIG_SUCCESS'));
2328 END IF;
2329
2330 -- FND_FILE.put_line(FND_FILE.OUTPUT,'Contract Lines');
2331 FND_FILE.put_line(FND_FILE.OUTPUT,FND_MESSAGE.get_string('OKS','OKS_RR_CONTRACT_LINES'));
2332 FND_FILE.put_line(FND_FILE.OUTPUT,l_dash_string|| SUBSTR(l_dash_string,1,4));
2333 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2334 -- FND_FILE.put_line(FND_FILE.OUTPUT,'Contract Number Line Number Billto Service Name Status Start Date End Date Date Terminated Credit Card Number Expiration Date ');
2335 FND_FILE.put_line(FND_FILE.OUTPUT,
2336 RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_CONTRACT_NUM'),25)
2337 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_LINE_NUMBER'),14)
2338 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_BILL_TO'),25)
2339 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_SRV_NAME'),25)
2340 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_STATUS'),10)
2341 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_START_DATE'),14)
2342 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_END_DATE'),13)
2343 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_DATE_TERM'),18)
2344 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_CCR_NUM'),20)
2345 ||FND_MESSAGE.get_string('OKS','OKS_RR_EXP_DATE'));
2346 FND_FILE.put_line(FND_FILE.OUTPUT,l_dash_string||l_dash_string||l_dash_string
2347 ||l_dash_string||l_dash_string||l_dash_string
2348 ||l_dash_string||l_dash_string||l_dash_string
2349 ||l_dash_string||l_dash_string||l_dash_string
2350 ||l_dash_string||l_dash_string||l_dash_string
2351 ||l_dash_string||l_dash_string||SUBSTR(l_dash_string,1,9));
2352 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2353
2354 line_id.DELETE;
2355 contract_number.DELETE;
2356 line_number.DELETE;
2357 line_sdate.DELETE;
2358 line_edate.DELETE;
2359 date_terminated.DELETE;
2360 line_status.DELETE;
2361 cc_number.DELETE;
2362 cc_exp_date.DELETE;
2363 party_name.DELETE;
2364 service_name.DELETE;
2365
2366 OPEN get_line_csr;
2367 FETCH get_line_csr
2368 BULK COLLECT INTO line_id,
2369 contract_number,
2370 line_number,
2371 line_sdate,
2372 line_edate,
2373 date_terminated,
2374 line_status,
2375 cc_number,
2376 cc_exp_date,
2377 party_name,
2378 service_name;
2379
2380 CLOSE get_line_csr;
2381
2382 IF line_id.COUNT > 0
2383 THEN
2384 FOR i IN 1 .. line_id.COUNT
2385 LOOP
2386 FND_FILE.put(FND_FILE.OUTPUT,RPAD (SUBSTR(contract_number (i),1,20), 25,' ' ));
2387 FND_FILE.put(FND_FILE.OUTPUT,RPAD (line_number (i),14, ' '));
2388 IF party_name (i) IS NOT NULL
2389 THEN
2390 IF LENGTH(party_name(i)) > 20 THEN
2391 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (party_name (i), 1, 20),25, ' '));
2392 ELSE
2393 FND_FILE.put(FND_FILE.OUTPUT,RPAD(party_name (i), 25, ' ' ));
2394 END IF;
2395 ELSE
2396 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2397 ||SUBSTR(l_empty_string,1,5));
2398 END IF;
2399
2400 IF Service_name (i) IS NOT NULL
2401 THEN
2402 IF LENGTH(Service_name(i)) > 20 THEN
2403 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (Service_name (i), 1, 20),25, ' '));
2404 ELSE
2405 FND_FILE.put(FND_FILE.OUTPUT,RPAD(service_name (i), 25, ' ' ));
2406 END IF;
2407 ELSE
2408 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2409 ||SUBSTR(l_empty_string,1,5));
2410 END IF;
2411
2412 FND_FILE.put(FND_FILE.OUTPUT,RPAD(line_status (i),10, ' '));
2413 FND_FILE.put(FND_FILE.OUTPUT,RPAD(line_sdate (i), 14, ' '));
2414 FND_FILE.put(FND_FILE.OUTPUT,RPAD(line_edate (i), 13, ' '));
2415 IF date_terminated(i) IS NOT NULL
2416 THEN
2417 FND_FILE.put(FND_FILE.OUTPUT,RPAD(date_terminated(i),18, ' '));
2418 ELSE
2419 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string
2420 ||SUBSTR(l_empty_string,1,8));
2421 END IF;
2422 FND_FILE.put(FND_FILE.OUTPUT,RPAD(cc_number(i), 20,' '));
2423 FND_FILE.put(FND_FILE.OUTPUT,RPAD(cc_exp_date(i), 15, ' '));
2424
2425 l_cont_length := LENGTH (contract_number(i));
2426 l_party_length := LENGTH (party_name(i));
2427 l_service_length := LENGTH (service_name(i));
2428
2429 IF l_cont_length >= l_party_length
2430 AND l_cont_length >= l_service_length
2431 THEN
2432 l_max_length := l_cont_length;
2433 ELSIF l_party_length >= l_cont_length
2434 AND l_party_length >= l_service_length
2435 THEN
2436 l_max_length := l_party_length;
2437 ELSIF l_service_length >= l_cont_length
2438 AND l_service_length >= l_service_length
2439 THEN
2440 l_max_length := l_service_length;
2441 END IF;
2442
2443 FOR j IN 1 ..FLOOR (l_max_length / 20)
2444 LOOP
2445 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2446 IF contract_number(i) IS NOT NULL AND (SUBSTR (contract_number (i), (j*20+1),20)) IS NOT NULL
2447 THEN
2448 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (contract_number (i), (j*20+1),20),25, ' '));
2449 ELSE
2450 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2451 ||SUBSTR(l_empty_string,1,5));
2452 END IF;
2453 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||SUBSTR(l_empty_string,1,4));
2454 IF party_name(i) IS NOT NULL AND (SUBSTR (party_name (i), (j*20+1),20)) IS NOT NULL
2455 THEN
2456 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (party_name (i), (j*20+1),20),25, ' '));
2457 ELSE
2458 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2459 ||SUBSTR(l_empty_string,1,5));
2460 END IF;
2461 IF service_name(i) IS NOT NULL AND (SUBSTR (service_name (i), (j*20+1),20)) IS NOT null
2462 THEN
2463 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (service_name (i), (j*20+1),20),25, ' '));
2464 ELSE
2465 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2466 ||SUBSTR(l_empty_string,1,5));
2467 END IF;
2468 END LOOP;
2469 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2470 END LOOP;
2471 ELSE
2472 fnd_file.put_line(FND_FILE.OUTPUT,FND_MESSAGE.get_string('OKS','OKS_RR_MIG_SUCCESS'));
2473 END IF;
2474
2475 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2476 -- FND_FILE.put_line(FND_FILE.OUTPUT,'Contract Lines History');
2477 FND_FILE.put_line(FND_FILE.OUTPUT,FND_MESSAGE.get_string('OKS','OKS_RR_LINE_HIST'));
2478 FND_FILE.put_line(FND_FILE.OUTPUT,l_dash_string||l_dash_string
2479 ||SUBSTR(l_dash_string,1,2));
2480 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2481 -- FND_FILE.put_line(FND_FILE.OUTPUT,'Contract Number Major Version Line Number Billto Service Name Status Start Date End Date Date Terminated Credit Card Number Expiration Date');
2482 FND_FILE.put_line(FND_FILE.OUTPUT,
2483 RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_CONTRACT_NUM'),25)
2484 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_MAJOR_VER'),16)
2485 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_LINE_NUMBER'),14)
2486 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_BILL_TO'),25)
2487 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_SRV_NAME'),25)
2488 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_STATUS'),10)
2489 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_START_DATE'),14)
2490 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_END_DATE'),13)
2491 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_DATE_TERM'),18)
2492 ||RPAD(FND_MESSAGE.get_string('OKS','OKS_RR_CCR_NUM'),20)
2493 ||FND_MESSAGE.get_string('OKS','OKS_RR_EXP_DATE'));
2494 FND_FILE.put_line(FND_FILE.OUTPUT,l_dash_string||l_dash_string||l_dash_string
2495 ||l_dash_string||l_dash_string||l_dash_string
2496 ||l_dash_string||l_dash_string||l_dash_string
2497 ||l_dash_string||l_dash_string||l_dash_string
2498 ||l_dash_string||l_dash_string||l_dash_string
2499 ||l_dash_string||l_dash_string||l_dash_string
2500 ||l_dash_string||SUBSTR(l_dash_string,1,5));
2501 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2502
2503 line_id.DELETE;
2504 contract_number.DELETE;
2505 line_number.DELETE;
2506 line_sdate.DELETE;
2507 line_edate.DELETE;
2508 date_terminated.DELETE;
2509 line_status.DELETE;
2510 cc_number.DELETE;
2511 cc_exp_date.DELETE;
2512 party_name.DELETE;
2513 service_name.DELETE;
2514
2515 OPEN get_line_hist_csr;
2516 FETCH get_line_hist_csr
2517 BULK COLLECT INTO line_id,
2518 contract_number,
2519 line_version,
2520 line_number,
2521 line_sdate,
2522 line_edate,
2523 date_terminated,
2524 line_status,
2525 cc_number,
2526 cc_exp_date,
2527 party_name,
2528 service_name;
2529
2530 CLOSE get_line_hist_csr;
2531
2532 IF line_id.COUNT > 0
2533 THEN
2534 FOR i IN 1 .. line_id.COUNT
2535 LOOP
2536 FND_FILE.put(FND_FILE.OUTPUT,RPAD (SUBSTR(contract_number (i),1,20), 25,' ' ));
2537 FND_FILE.put(FND_FILE.OUTPUT,RPAD(line_version(i),16));
2538 FND_FILE.put(FND_FILE.OUTPUT,RPAD (line_number (i),14, ' '));
2539 IF party_name (i) IS NOT NULL
2540 THEN
2541 IF LENGTH(party_name(i)) > 20 THEN
2542 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (party_name (i), 1, 20),25, ' '));
2543 ELSE
2544 FND_FILE.put(FND_FILE.OUTPUT,RPAD(party_name (i), 25, ' ' ));
2545 END IF;
2546 ELSE
2547 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string
2548 ||l_empty_string ||SUBSTR(l_empty_string,1,5));
2549 END IF;
2550
2551 IF Service_name (i) IS NOT NULL
2552 THEN
2553 IF LENGTH(Service_name(i)) > 20 THEN
2554 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (Service_name (i), 1, 20),25, ' '));
2555 ELSE
2556 FND_FILE.put(FND_FILE.OUTPUT,RPAD(service_name (i), 25, ' ' ));
2557 END IF;
2558 ELSE
2559 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2560 ||SUBSTR(l_empty_string,1,5));
2561 END IF;
2562
2563 FND_FILE.put(FND_FILE.OUTPUT,RPAD(line_status (i),10, ' '));
2564 FND_FILE.put(FND_FILE.OUTPUT,RPAD(line_sdate (i), 14, ' '));
2565 FND_FILE.put(FND_FILE.OUTPUT,RPAD(line_edate (i), 13, ' '));
2566 IF date_terminated(i) IS NOT NULL
2567 THEN
2568 FND_FILE.put(FND_FILE.OUTPUT,RPAD(date_terminated(i),18, ' '));
2569 ELSE
2570 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string
2571 ||SUBSTR(l_empty_string,1,8));
2572 END IF;
2573 FND_FILE.put(FND_FILE.OUTPUT,RPAD(cc_number(i), 20,' '));
2574 FND_FILE.put(FND_FILE.OUTPUT,RPAD(cc_exp_date(i), 15, ' '));
2575
2576 l_cont_length := LENGTH (contract_number(i));
2577 l_party_length := LENGTH (party_name(i));
2578 l_service_length := LENGTH (service_name(i));
2579
2580 IF l_cont_length >= l_party_length
2581 AND l_cont_length >= l_service_length
2582 THEN
2583 l_max_length := l_cont_length;
2584 ELSIF l_party_length >= l_cont_length
2585 AND l_party_length >= l_service_length
2586 THEN
2587 l_max_length := l_party_length;
2588 ELSIF l_service_length >= l_cont_length
2589 AND l_service_length >= l_service_length
2590 THEN
2591 l_max_length := l_service_length;
2592 END IF;
2593
2594 FOR j IN 1 ..FLOOR (l_max_length / 20)
2595 LOOP
2596 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2597 IF contract_number(i) IS NOT NULL AND (SUBSTR (contract_number (i), (j*20+1),20)) IS NOT NULL
2598 THEN
2599 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (contract_number (i), (j*20+1),20),25, ' '));
2600 ELSE
2601 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2602 ||SUBSTR(l_empty_string,1,5));
2603 END IF;
2604 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string ||l_empty_string);
2605 IF party_name(i) IS NOT NULL AND (SUBSTR (party_name (i), (j*20+1),20)) IS NOT NULL
2606 THEN
2607 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (party_name (i), (j*20+1),20),25, ' '));
2608 ELSE
2609 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2610 ||SUBSTR(l_empty_string,1,5));
2611 END IF;
2612 IF service_name(i) IS NOT NULL AND (SUBSTR (service_name (i), (j*20+1),20)) IS NOT null
2613 THEN
2614 FND_FILE.put(FND_FILE.OUTPUT,RPAD(SUBSTR (service_name (i), (j*20+1),20),25, ' '));
2615 ELSE
2616 FND_FILE.put(FND_FILE.OUTPUT,l_empty_string||l_empty_string
2617 ||SUBSTR(l_empty_string,1,5));
2618 END IF;
2619
2620 END LOOP;
2621 FND_FILE.put_line(FND_FILE.OUTPUT,' ');
2622 END LOOP;
2623 ELSE
2624 fnd_file.put_line(FND_FILE.OUTPUT,FND_MESSAGE.get_string('OKS','OKS_RR_MIG_SUCCESS'));
2625 END IF;
2626 END;
2627
2628
2629
2630 Procedure Purge_CC_Number
2631 (
2632 ERRBUF OUT NOCOPY VARCHAR2,
2633 RETCODE OUT NOCOPY NUMBER,
2634 P_BATCH_SIZE IN NUMBER
2635 )
2636 Is
2637 cursor l_hdr_ranges(l_bucket_size number) is
2638 SELECT /*+ parallel(WBR) */
2639 WB_Low
2640 ,WB_High,rownum num
2641 FROM
2642 (SELECT /*+ no_merge parallel(WB) */ MIN(ID) WB_Low, MAX(ID) WB_High
2643 FROM
2644 (SELECT /*+ no_merge parallel(khdr) */ ID, FLOOR((ROWNUM-1)/l_bucket_size) Worker_Bucket
2645 FROM
2646 ( SELECT id
2647 FROM oks_k_headers_b okshdr
2648 WHERE ((okshdr.cc_no IS NOT NULL
2649 AND okshdr.payment_type = 'CCR'
2650 AND okshdr.trxn_extension_id is not null)
2651 OR
2652 (okshdr.trxn_extension_id is null
2653 AND okshdr.chr_id in (select chr_id from oks_rule_error))
2654
2655 )
2656 order by id) KHDR) WB GROUP BY Worker_Bucket) WBR;
2657
2658 cursor l_hdr_rule_ranges(l_bucket_size number) is
2659 SELECT /*+ parallel(WBR) */
2660 WB_Low
2661 ,WB_High,rownum num
2662 FROM
2663 (SELECT /*+ no_merge parallel(WB) */ MIN(ID) WB_Low, MAX(ID) WB_High
2664 FROM
2665 (SELECT /*+ no_merge parallel(khdr) */ ID, FLOOR((ROWNUM-1)/l_bucket_size) Worker_Bucket
2666 FROM
2667 ( Select rl.Id
2668 From okc_rules_b rl
2669 , okc_rule_groups_b rg
2670 , Oks_k_headers_b Kh
2671 Where rl.rgp_id = rg.id
2672 And rl.rule_information_category = 'CCR'
2673 And rl.rule_information1 is not null
2674 And ((rg.chr_id = Kh.chr_id
2675 And Kh.trxn_extension_id is not null)
2676 Or
2677 (rg.chr_id in (select chr_id from oks_rule_error))
2678 )
2679 order by id) KHDR) WB GROUP BY Worker_Bucket) WBR;
2680
2681 cursor l_line_ranges(l_bucket_size number) is
2682 SELECT /*+ parallel(WBR) */
2683 WB_Low
2684 ,WB_High,rownum num
2685 FROM
2686 (SELECT /*+ no_merge parallel(WB) */ MIN(ID) WB_Low, MAX(ID) WB_High
2687 FROM
2688 (SELECT /*+ no_merge parallel(kln) */ ID, FLOOR((ROWNUM-1)/l_bucket_size) Worker_Bucket
2689 FROM
2690 ( SELECT oksline.id
2691 FROM oks_k_lines_b oksline, okc_k_lines_b okcline
2692 WHERE oksline.cle_id=okcline.id
2693 and okcline.lse_id in (1,12,19,46)
2694 and ((oksline.cc_no IS NOT NULL
2695 AND oksline.payment_type = 'CCR'
2696 AND oksline.trxn_extension_id is not null)
2697 OR
2698 (oksline.trxn_extension_id is null
2699 AND oksline.cle_id in (select cle_id from oks_rule_error))
2700 )
2701 order by id) KLN) WB GROUP BY Worker_Bucket) WBR;
2702
2703
2704
2705
2706
2707
2708 Cursor l_hdr_hist_agg_csr IS
2709
2710 Select /*+ PARALLEL(okshdrh) */
2711 min(okshdrh.id) minid,
2712 max(okshdrh.id) maxid,
2713 avg(okshdrh.id) avgid,
2714 stddev(okshdrh.id) stdid,
2715 count(*) total
2716 From OKS_K_HEADERS_BH okshdrh ;
2717
2718 Cursor l_line_hist_agg_csr IS
2719 Select /*+ PARALLEL(okslineh) */
2720 min(okslineh.id) minid,
2721 max(okslineh.id) maxid,
2722 avg(okslineh.id) avgid,
2723 stddev(okslineh.id) stdid,
2724 count(*) total
2725 From OKS_K_LINES_BH okslineh ;
2726
2727 l_agg_rec l_line_hist_agg_csr%rowtype;
2728 l_sub_requests number;
2729 l_sub_req number;
2730 l_batch_size number;
2731 l_ret number;
2732
2733
2734 Begin
2735
2736 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Start of OKS_CREDIT_CARD_PURGE ');
2737
2738
2739 IF (FND_CONC_GLOBAL.request_data is null) THEN
2740 -- The following csr gets records from Header history table
2741 Open l_hdr_hist_agg_csr;
2742 Fetch l_hdr_hist_agg_csr into l_agg_rec;
2743 Close l_hdr_hist_agg_csr;
2744
2745 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Cursor opened is l_hdr_hist_agg_csr');
2746 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.minid = '|| l_agg_rec.minid );
2747 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.maxid = '|| l_agg_rec.maxid );
2748 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.total = '|| l_agg_rec.total );
2749
2750
2751
2752 l_ret := FND_REQUEST.submit_request('OKS',
2753 'OKS_UPDCC_HDRH',
2754 to_char(l_sub_requests), -- UI job display
2755 null,
2756 TRUE, -- TRUE means isSubRequest
2757 l_agg_rec.minid,
2758 l_agg_rec.maxid,nvl(l_batch_size,10000));
2759
2760 IF (l_ret = 0) then
2761 errbuf := fnd_message.get;
2762 retcode := 2;
2763 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request failed to submit: ' || errbuf);
2764 return;
2765 ELSE
2766 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' submitted');
2767 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' p_low_id ==> '|| l_agg_rec.minid || ' l_hig_id ==> '||l_agg_rec.maxid );
2768 END IF;
2769
2770
2771
2772 -- The following csr gets records from Line history table
2773 open l_line_hist_agg_csr;
2774 fetch l_line_hist_agg_csr into l_agg_rec;
2775 close l_line_hist_agg_csr;
2776
2777 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Cursor opened is l_line_hist_agg_csr' );
2778 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.minid = '|| l_agg_rec.minid );
2779 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.maxid = '|| l_agg_rec.maxid );
2780 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_agg_rec.total = '|| l_agg_rec.total );
2781
2782
2783
2784 l_ret := FND_REQUEST.submit_request ('OKS',
2785 'OKS_UPDCC_LINEH',
2786 to_char(l_sub_requests), -- UI job display
2787 null,
2788 TRUE, -- TRUE means isSubRequest
2789 l_agg_rec.minid,
2790 l_agg_rec.maxid,nvl(l_batch_size,10000));
2791
2792 IF (l_ret = 0) then
2793 errbuf := fnd_message.get;
2794 retcode := 2;
2795 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request Line History failed to submit: ' || errbuf);
2796 return;
2797 ELSE
2798 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' submitted');
2799 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' p_low_id ==> '|| l_agg_rec.minid || ' l_hig_id ==> '||l_agg_rec.maxid );
2800 END IF;
2801
2802 -- Process the header records
2803 ---errorout_n(p_batch_size);
2804 FOR range_rec in l_hdr_ranges(p_batch_size)
2805 LOOP
2806 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'Submitting Header CC no request');
2807 ---errorout_n('in hdr range');
2808 l_ret := FND_REQUEST.submit_request('OKS',
2809 'OKS_UPDCC_HDR',
2810 to_char(range_rec.num), -- UI job display
2811 null,
2812 TRUE, -- TRUE means isSubRequest
2813 range_rec.wb_low,
2814 range_rec.wb_high,
2815 nvl(l_batch_size,10000));
2816
2817 IF (l_ret = 0) then
2818 errbuf := fnd_message.get;
2819 retcode := 2;
2820 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request failed to submit: ' || errbuf);
2821 return;
2822 ELSE
2823 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' submitted');
2824 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' p_low_id ==> '|| range_rec.wb_low || ' l_hig_id ==> '||range_rec.wb_high );
2825 END IF;
2826
2827 END LOOP;
2828
2829 -- Process the line records
2830 FOR range_rec in l_line_ranges(l_batch_size)
2831 LOOP
2832 ---errorout_n('in line range');
2833
2834 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Submitting Header CC no request');
2835 l_ret := FND_REQUEST.submit_request('OKS',
2836 'OKS_UPDCC_LINE',
2837 range_rec.num, -- UI job display
2838 null,
2839 TRUE, -- TRUE means isSubRequest
2840 range_rec.wb_low,
2841 range_rec.wb_high,
2842 nvl(l_batch_size,10000));
2843
2844 IF (l_ret = 0) then
2845 errbuf := fnd_message.get;
2846 retcode := 2;
2847 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request failed to submit: ' || errbuf);
2848 return;
2849 ELSE
2850 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' submitted');
2851 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Sub-request '||to_char(l_ret)||' p_low_id ==> '|| range_rec.wb_low || ' l_hig_id ==> '||range_rec.wb_high );
2852 END IF;
2853
2854 END LOOP;
2855
2856 FND_CONC_GLOBAL.set_req_globals(conc_status => 'PAUSED',
2857 request_data => to_char(l_sub_requests));
2858 errbuf := to_char(l_sub_requests) || ' sub-requests submitted';
2859 retcode := 0;
2860 return;
2861
2862
2863 END IF;
2864
2865 End Purge_CC_Number;
2866
2867 PROCEDURE UPDATE_CC_LINEH(
2868 ERRBUF OUT NOCOPY VARCHAR2,
2869 RETCODE OUT NOCOPY NUMBER,
2870 p_id_low IN NUMBER,
2871 p_id_high IN NUMBER,
2872 p_batchsize IN NUMBER ) IS
2873
2874
2875 Cursor l_line_hist_csr Is
2876 Select Ks.id
2877 From oks_k_lines_bh ks
2878 where Ks.cc_no is not null
2879 And Ks.trxn_extension_id is not null
2880 And Ks.id between p_id_low and p_id_high;
2881
2882 l_line_id l_num_tbl;
2883 Begin
2884
2885 Open l_line_hist_csr;
2886 Loop
2887 Fetch l_line_hist_csr bulk collect into l_line_id
2888 limit p_batchsize;
2889 EXIT WHEN l_line_id.count = 0 ;
2890
2891
2892 -- Update Line history table
2893
2894 Forall i in l_line_id.first..l_line_id.last
2895 Update Oks_k_Lines_bh
2896 Set cc_no = null,
2897 cc_bank_acct_id = null,
2898 cc_expiry_date = null,
2899 cc_auth_code = null
2900 Where id = l_line_id(i);
2901
2902 End Loop;
2903
2904 Close l_line_hist_csr;
2905
2906
2907 End UPDATE_CC_LINEH;
2908
2909 PROCEDURE UPDATE_CC_HEADERH(
2910 ERRBUF OUT NOCOPY VARCHAR2,
2911 RETCODE OUT NOCOPY NUMBER,
2912 p_id_low IN NUMBER,
2913 p_id_high IN NUMBER,
2914 p_batchsize IN NUMBER ) IS
2915
2916
2917 Cursor l_hdr_hist_csr Is
2918 Select id, chr_id
2919 From oks_k_headers_bh
2920 Where cc_no is not null
2921 And trxn_extension_id is not null
2922 And id between p_id_low and p_id_high;
2923
2924 l_hdr_id l_num_tbl;
2925 l_chr_id l_num_tbl;
2926
2927 Begin
2928
2929 Open l_hdr_hist_csr;
2930 Loop
2931 Fetch l_hdr_hist_csr bulk collect into l_hdr_id, l_chr_id
2932 limit p_batchsize;
2933
2934 EXIT WHEN l_hdr_id.count = 0 ;
2935
2936
2937 -- Update Header history table
2938
2939 Forall i in l_hdr_id.first..l_hdr_id.last
2940 Update Oks_k_headers_bh
2941 Set cc_no = null,
2942 cc_bank_acct_id = null,
2943 cc_expiry_date = null,
2944 cc_auth_code = null
2945 Where id = l_hdr_id(i);
2946
2947 Forall i in l_chr_id.first..l_chr_id.last
2948 Update okc_rules_bh
2949 Set rule_information1 = null,
2950 rule_information2 = null,
2951 rule_information3 = null,
2952 rule_information4 = null
2953 Where dnz_chr_id = l_chr_id(i)
2954 and rule_information_category = 'CCR';
2955
2956 End Loop;
2957
2958 Close l_hdr_hist_csr;
2959
2960
2961 End UPDATE_CC_HEADERH;
2962
2963
2964 PROCEDURE UPDATE_CC_HEADER(
2965 ERRBUF OUT NOCOPY VARCHAR2,
2966 RETCODE OUT NOCOPY NUMBER,
2967 p_id_low IN NUMBER,
2968 p_id_high IN NUMBER,
2969 p_batchsize IN NUMBER ) IS
2970
2971
2972 Cursor l_hdr_csr Is
2973 Select id, chr_id
2974 From oks_k_headers_b
2975 Where cc_no is not null
2976 And trxn_extension_id is not null
2977 And id between p_id_low and p_id_high;
2978
2979 l_hdr_id l_num_tbl;
2980 l_chr_id l_num_tbl;
2981
2982 Begin
2983
2984 Open l_hdr_csr;
2985 Loop
2986 Fetch l_hdr_csr bulk collect into l_hdr_id, l_chr_id
2987 limit p_batchsize;
2988
2989 EXIT WHEN l_hdr_id.count = 0 ;
2990
2991
2992 -- Update Header table
2993
2994 Forall i in l_hdr_id.first..l_hdr_id.last
2995 Update Oks_k_headers_b
2996 Set cc_no = null,
2997 cc_bank_acct_id = null,
2998 cc_expiry_date = null,
2999 cc_auth_code = null
3000 Where id = l_hdr_id(i);
3001
3002 Forall i in l_chr_id.first..l_chr_id.last
3003 Update okc_rules_b
3004 Set rule_information1 = null,
3005 rule_information2 = null,
3006 rule_information3 = null,
3007 rule_information4 = null
3008 Where dnz_chr_id = l_chr_id(i)
3009 and rule_information_category = 'CCR';
3010
3011 End Loop;
3012
3013 Close l_hdr_csr;
3014
3015
3016
3017 End UPDATE_CC_HEADER;
3018
3019 PROCEDURE UPDATE_CC_HEADER_RULE(
3020 ERRBUF OUT NOCOPY VARCHAR2,
3021 RETCODE OUT NOCOPY NUMBER,
3022 p_id_low IN NUMBER,
3023 p_id_high IN NUMBER,
3024 p_batchsize IN NUMBER ) IS
3025
3026
3027 Cursor l_hdr_rule_csr Is
3028 Select rl.Id
3029 From okc_rules_b rl
3030 , okc_rule_groups_b rg
3031 , Oks_k_headers_b Kh
3032 Where rl.rgp_id = rg.id
3033 And rl.rule_information_category = 'CCR'
3034 And rl.rule_information1 is not null
3035 And ((rg.chr_id = Kh.chr_id
3036 And Kh.trxn_extension_id is not null)
3037 Or
3038 (rg.chr_id in (select chr_id from oks_rule_error))
3039 )
3040 And rl.id between p_id_low and p_id_high;
3041
3042 l_hdr_id l_num_tbl;
3043 Begin
3044
3045 Open l_hdr_rule_csr ;
3046 Loop
3047 Fetch l_hdr_rule_csr bulk collect into l_hdr_id
3048 limit p_batchsize;
3049
3050 EXIT WHEN l_hdr_id.count = 0 ;
3051
3052
3053 -- Update Header Rule table
3054
3055 Forall i in l_hdr_id.first..l_hdr_id.last
3056 Update okc_rules_b
3057 Set rule_information1 = null
3058 Where id = l_hdr_id(i);
3059
3060 End Loop;
3061
3062 Close l_hdr_rule_csr ;
3063
3064
3065
3066 End UPDATE_CC_HEADER_RULE;
3067
3068
3069 PROCEDURE UPDATE_CC_LINE(
3070 ERRBUF OUT NOCOPY VARCHAR2,
3071 RETCODE OUT NOCOPY NUMBER,
3072 p_id_low IN NUMBER,
3073 p_id_high IN NUMBER,
3074 p_batchsize IN NUMBER ) IS
3075
3076
3077 Cursor l_line_csr Is
3078 Select Ks.id
3079 From oks_k_lines_b ks
3080 Where Ks.cc_no is not null
3081 And Ks.trxn_extension_id is not null
3082 And Ks.id between p_id_low and p_id_high;
3083
3084 l_line_id l_num_tbl;
3085
3086 Begin
3087
3088 Open l_line_csr;
3089 Loop
3090 Fetch l_line_csr bulk collect into l_line_id
3091 limit p_batchsize;
3092 EXIT WHEN l_line_id.count = 0 ;
3093
3094
3095 -- Update Line table
3096
3097 Forall i in l_line_id.first..l_line_id.last
3098 Update Oks_k_Lines_b
3099 Set cc_no = null,
3100 cc_bank_acct_id = null,
3101 cc_expiry_date = null,
3102 cc_auth_code = null
3103 Where id = l_line_id(i);
3104
3105 End Loop;
3106
3107 Close l_line_csr;
3108
3109
3110 End UPDATE_CC_LINE;
3111
3112
3113 END OKS_CCMIGRATE_PVT;