[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_CC_UPGRADE_PKG
Source
1 PACKAGE BODY AP_WEB_CC_UPGRADE_PKG AS
2 /* $Header: apwccupb.pls 120.6 2011/08/12 13:31:11 rveliche noship $ */
3
4
5 /*
6 Procedure : Upgrade_Cards
7 Purpose : Successful upgrade for a card signifies encryption of a card number,
8 Cardmember Name, and Expiration Date using Oracle Payments api
9 and updating with correct reference of card_reference_id in ap_cards_all.
10 */
11 PROCEDURE Upgrade_Cards
12 (x_errbuf OUT NOCOPY VARCHAR2,
13 x_retcode OUT NOCOPY VARCHAR2,
14 x_batch_size IN NUMBER,
15 x_worker_id IN NUMBER,
16 x_num_workers IN NUMBER,
17 x_script_name IN VARCHAR2
18 )
19 IS
20 l_start_rowid rowid;
21 l_end_rowid rowid;
22 x_return_status VARCHAR2(4000);
23 x_msg_count NUMBER;
24 x_msg_data VARCHAR2(4000);
25 p_card_instrument IBY_FNDCPT_SETUP_PUB.CREDITCARD_REC_TYPE;
26 x_instr_id NUMBER;
27 x_response IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE;
28 l_sqlerr VARCHAR2(255);
29 l_user_id number;
30
31 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
32 TYPE CharList1 IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
33 TYPE CharList30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
34 TYPE CharList80 IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
35 TYPE DateList IS TABLE OF DATE INDEX BY BINARY_INTEGER;
36
37 h_card_id NumberList;
38 h_card_number CharList80;
39 h_party_id NumberList;
40 h_card_program_id NumberList;
41 h_card_reference_id NumberList;
42 h_cardmember_name CharList80;
43 h_card_expiration_date DateList;
44
45 v_instr_id NumberList;
46 v_return_status CharList1;
47
48 l_rows_processed number;
49 l_table_owner varchar2(30);
50 l_batch_size varchar2(30) := x_batch_size;
51 l_worker_id number := x_worker_id;
52 l_num_workers number := x_num_workers;
53 l_any_rows_to_process boolean;
54 l_table_name varchar2(30) := 'AP_CARDS_ALL';
55 l_update_name varchar2(30) := x_script_name || 'UpgCard';
56
57
58 l_status varchar2(1);
59 l_industry varchar2(1);
60 l_oracle_schema varchar2(30);
61 l_appinst_ret boolean;
62
63 cursor get_cards (p_start_rowid ROWID, p_end_rowid ROWID)
64 is select /*+ ROWID (aca) */ card_id, card_number,
65 (case when (p.employee_number is not null
66 or p.npw_number is not null) then party_id end) as person_party_id,
67 card_program_id,
68 card_reference_id,
69 card_expiration_date,
70 cardmember_name
71 from ap_cards_all aca,
72 per_all_people_f p
73 where trunc(sysdate) between p.effective_start_date(+) and p.effective_end_date(+)
74 and aca.employee_id = p.person_id(+)
75 and (card_number is not null
76 or cardmember_name is not null
77 or card_expiration_date is not null)
78 and aca.rowid between p_start_rowid and p_end_rowid;
79
80
81 BEGIN
82 -- Bug: 12350861, Schema Name not to be hardcoded.
83 l_appinst_ret := fnd_installation.get_app_info('SQLAP',l_status, l_industry, l_oracle_schema);
84 l_table_owner := l_oracle_schema;
85 l_rows_processed := 0;
86
87 p_card_instrument.Instrument_Type := 'CREDITCARD';
88 p_card_instrument.Info_Only_Flag := 'Y';
89 p_card_instrument.Register_Invalid_Card := 'Y';
90 l_user_id := nvl(fnd_global.user_id, -1);
91
92 fnd_file.put_line(fnd_file.log,'Begin Upgrade_Cards');
93
94 ad_parallel_updates_pkg.initialize_rowid_range(
95 ad_parallel_updates_pkg.ROWID_RANGE,
96 l_table_owner,
97 l_table_name,
98 l_update_name,
99 l_worker_id,
100 l_num_workers,
101 l_batch_size, 0);
102
103 ad_parallel_updates_pkg.get_rowid_range(
104 l_start_rowid,
105 l_end_rowid,
106 l_any_rows_to_process,
107 l_batch_size,
108 true);
109
110 while (l_any_rows_to_process = true) loop
111
112 open get_cards(l_start_rowid, l_end_rowid);
113 fetch get_cards bulk collect into h_card_id, h_card_number, h_party_id, h_card_program_id, h_card_reference_id,
114 h_card_expiration_date,h_cardmember_name;
115 l_rows_processed := get_cards%ROWCOUNT;
116
117 for i in 1..h_card_id.count loop
118
119 p_card_instrument.card_number := h_card_number(i);
120 p_card_instrument.Expiration_Date := h_card_expiration_date(i);
121 p_card_instrument.Card_Holder_Name := h_cardmember_name(i);
122
123 if (h_card_number(i) IS NOT NULL) then
124 iby_fndcpt_setup_pub.card_exists(1.0,NULL,x_return_status, x_msg_count, x_msg_data, null, h_card_number(i),
125 p_card_instrument, x_response);
126 -- Added the assignment statements as card_exists api clears out the data
127 p_card_instrument.Instrument_Type := 'CREDITCARD';
128 p_card_instrument.Info_Only_Flag := 'Y';
129 p_card_instrument.Register_Invalid_Card := 'Y';
130 p_card_instrument.Expiration_Date := h_card_expiration_date(i);
131 p_card_instrument.Card_Holder_Name := h_cardmember_name(i);
132 else
133 x_return_status := '';
134 p_card_instrument.card_id := h_card_reference_id(i);
135 end if;
136
137 if ( x_return_status = 'S' OR h_card_reference_id(i) IS NOT NULL) then
138 v_instr_id(i) := p_card_instrument.card_id;
139 iby_fndcpt_setup_pub.update_card(1.0,NULL,'F',x_return_status,x_msg_count,x_msg_data, p_card_instrument,x_response);
140 v_return_status(i) := x_return_status;
141 else
142 p_card_instrument.card_number := h_card_number(i);
143 iby_fndcpt_setup_pub.create_card(1.0,NULL,'F',x_return_status,x_msg_count,x_msg_data, p_card_instrument,x_instr_id,x_response);
144 v_return_status(i) := x_return_status;
145 v_instr_id(i) := x_instr_id;
146 end if;
147
148 end loop;
149
150 -- updating cards table
151 forall k in 1..h_card_id.count
152 update ap_cards_all
153 set card_reference_id = v_instr_id(k)
154 , card_number = null
155 , cardmember_name = null
156 , card_expiration_date = null
157 , last_update_date = sysdate
158 , last_updated_by = l_user_id
159 where card_id = h_card_id(k)
160 and v_return_status(k) = 'S';
161
162 close get_cards;
163
164 ad_parallel_updates_pkg.processed_rowid_range(
165 l_rows_processed,
166 l_end_rowid);
167
168 -- get new range of rowids
169 ad_parallel_updates_pkg.get_rowid_range(
170 l_start_rowid,
171 l_end_rowid,
172 l_any_rows_to_process,
173 l_batch_size,
174 FALSE);
175 end loop;
176
177 COMMIT;
178 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
179 fnd_file.put_line(fnd_file.log,'End Upgrade_Cards');
180
181 EXCEPTION
182 WHEN OTHERS THEN
183 ROLLBACK;
184 fnd_file.put_line(fnd_file.log,'Error in Upgrade_Cards');
185 fnd_file.put_line(fnd_file.log,'ERROR CODE:='||sqlcode);
186 fnd_file.put_line(fnd_file.log,'ERROR MESSAGE:='||SUBSTR(sqlerrm,1,150));
187 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
188 x_errbuf := SQLERRM;
189
190 END Upgrade_Cards;
191
192 /*
193 Procedure : Upgrade_Trxns
194 Purpose : For the cards which were successfully migrated during R12 and PADSS, update
195 ap_credit_card_trxns_all for those card numbers.
196 */
197 PROCEDURE Upgrade_Trxns
198 (x_errbuf OUT NOCOPY VARCHAR2,
199 x_retcode OUT NOCOPY VARCHAR2,
200 x_batch_size IN NUMBER,
201 x_worker_id IN NUMBER,
202 x_num_workers IN NUMBER,
203 x_script_name IN VARCHAR2
204 )
205 IS
206 l_start_rowid rowid;
207 l_end_rowid rowid;
208 l_sqlerr VARCHAR2(255);
209 l_user_id number;
210
211 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
212
213 h_trx_id NumberList;
214 l_rows_processed number;
215 l_table_owner varchar2(30);
216 l_batch_size varchar2(30) := x_batch_size;
217 l_worker_id number := x_worker_id;
218 l_num_workers number := x_num_workers;
219 l_any_rows_to_process boolean;
220 l_table_name varchar2(30) := 'AP_CREDIT_CARD_TRXNS_ALL';
221 l_update_name varchar2(30) := x_script_name || 'UpgTrxn';
222 l_status varchar2(1);
223 l_industry varchar2(1);
224 l_oracle_schema varchar2(30);
225 l_appinst_ret boolean;
226
227 begin
228 -- Bug: 12869284
229 l_appinst_ret := fnd_installation.get_app_info('SQLAP',l_status, l_industry, l_oracle_schema);
230 l_table_owner := l_oracle_schema;
231 l_rows_processed := 0;
232
233 l_user_id := nvl(fnd_global.user_id, -1);
234 fnd_file.put_line(fnd_file.log,'Begin Upgrade_Trxns');
235
236 ad_parallel_updates_pkg.initialize_rowid_range(
237 ad_parallel_updates_pkg.ROWID_RANGE,
238 l_table_owner,
239 l_table_name,
240 l_update_name,
241 l_worker_id,
242 l_num_workers,
243 l_batch_size, 0);
244
245 ad_parallel_updates_pkg.get_rowid_range(
246 l_start_rowid,
247 l_end_rowid,
248 l_any_rows_to_process,
249 l_batch_size,
250 true);
251
252 while (l_any_rows_to_process = true) loop
253
254 update /*+ ROWID (aca) */ ap_credit_card_trxns_all aca
255 set
256 card_number = null
257 , last_update_date = sysdate
258 , last_updated_by = l_user_id
259 where
260 card_number is not null
261 and card_id is not null
262 and card_id > 0
263 and aca.rowid between l_start_rowid and l_end_rowid;
264
265 ad_parallel_updates_pkg.processed_rowid_range(
266 l_rows_processed,
267 l_end_rowid);
268
269 -- get new range of rowids
270 ad_parallel_updates_pkg.get_rowid_range(
271 l_start_rowid,
272 l_end_rowid,
273 l_any_rows_to_process,
274 l_batch_size,
275 FALSE);
276
277 end loop;
278
279 COMMIT;
280 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
281 fnd_file.put_line(fnd_file.log,'End Upgrade_Trxns');
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 ROLLBACK;
286 fnd_file.put_line(fnd_file.log,'Error in Upgrade_Trxns');
287 fnd_file.put_line(fnd_file.log,'ERROR CODE:='||sqlcode);
288 fnd_file.put_line(fnd_file.log,'ERROR MESSAGE:='||SUBSTR(sqlerrm,1,150));
289 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
290 x_errbuf := SQLERRM;
291
292 END Upgrade_Trxns;
293
294 /*
295 Procedure : Upgrade_Trxns_Manager
296 Purpose : To initiate the Upgrade_Trxns in a parallel mode
297 */
298 PROCEDURE Upgrade_Trxns_Manager
299 (x_errbuf OUT NOCOPY VARCHAR2,
300 x_retcode OUT NOCOPY VARCHAR2,
301 x_batch_size IN NUMBER,
302 x_num_workers IN NUMBER,
303 x_script_name IN VARCHAR2
304 )
305 IS
306 BEGIN
307 fnd_file.put_line(fnd_file.log,'Begin Upgrade_Trxns_Manager');
308 AD_CONC_UTILS_PKG.submit_subrequests
309 (X_errbuf => x_errbuf,
310 X_retcode => x_retcode,
311 X_WorkerConc_app_shortname => 'SQLAP',
312 X_WorkerConc_progname => 'APXCCTRXUPG',
313 X_batch_size => x_batch_size,
314 X_Num_Workers => x_num_workers,
315 X_Argument4 => x_script_name
316 );
317 fnd_file.put_line(fnd_file.log,'End Upgrade_Trxns_Manager');
318 EXCEPTION
319 WHEN OTHERS THEN
320 ROLLBACK;
321 fnd_file.put_line(fnd_file.log,'Error in Upgrade_Trxns_Manager');
322 fnd_file.put_line(fnd_file.log,'ERROR CODE:='||sqlcode);
323 fnd_file.put_line(fnd_file.log,'ERROR MESSAGE:='||SUBSTR(sqlerrm,1,150));
324 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
325 x_errbuf := SQLERRM;
326
327 END Upgrade_Trxns_Manager;
328
329 /*
330 Procedure : Upgrade_Cards_Manager
331 Purpose : To initiate the Upgrade_Cards in a parallel mode
332 */
333
334 PROCEDURE Upgrade_Cards_Manager
335 (x_errbuf OUT NOCOPY VARCHAR2,
336 x_retcode OUT NOCOPY VARCHAR2,
337 x_batch_size IN NUMBER,
338 x_num_workers IN NUMBER,
339 x_script_name IN VARCHAR2
340 )
341 IS
342 BEGIN
343 fnd_file.put_line(fnd_file.log,'Begin Upgrade_Cards_Manager');
344 AD_CONC_UTILS_PKG.submit_subrequests
345 (X_errbuf => x_errbuf,
346 X_retcode => x_retcode,
347 X_WorkerConc_app_shortname => 'SQLAP',
348 X_WorkerConc_progname => 'APXCCUPG',
349 X_batch_size => x_batch_size,
350 X_Num_Workers => x_num_workers,
351 X_Argument4 => x_script_name
352 );
353 fnd_file.put_line(fnd_file.log,'End Upgrade_Cards_Manager');
354 EXCEPTION
355 WHEN OTHERS THEN
356 ROLLBACK;
357 fnd_file.put_line(fnd_file.log,'Error in Upgrade_Cards_Manager');
358 fnd_file.put_line(fnd_file.log,'ERROR CODE:='||sqlcode);
359 fnd_file.put_line(fnd_file.log,'ERROR MESSAGE:='||SUBSTR(sqlerrm,1,150));
360 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
361 x_errbuf := SQLERRM;
362 END Upgrade_Cards_Manager;
363
364 END AP_WEB_CC_UPGRADE_PKG;