1 PACKAGE iby_creditcard_pkg AS
2 /*$Header: ibyccs.pls 120.14.12010000.7 2009/01/19 12:59:51 lmallick ship $*/
3
4 -- Constant for credit card types
5 C_INSTRTYPE_CCARD CONSTANT VARCHAR2(20) := 'CREDITCARD';
6
7 -- Constant for purchase card types
8 C_INSTRTYPE_PCARD CONSTANT VARCHAR2(20) := 'PURCHASECARD';
9
10 -- Number masking options
11 G_MASK_CHARACTER CONSTANT VARCHAR2(1) := 'X';
12 G_DEF_UNMASK_LENGTH CONSTANT NUMBER := 4;
13
14
15 G_LKUP_PCARD_TYPE CONSTANT VARCHAR2(30) := 'IBY_PURCHASECARD_SUBTYPE';
16
17 -- Card validation errors
18 G_RC_INVALID_CCNUMBER CONSTANT VARCHAR2(30) := 'INVALID_CARD_NUMBER';
19 G_RC_INVALID_CCEXPIRY CONSTANT VARCHAR2(30) := 'INVALID_CARD_EXPIRY';
20 G_RC_INVALID_INSTR_TYPE CONSTANT VARCHAR2(30) := 'INVALID_INSTRUMENT_TYPE';
21 G_RC_INVALID_PCARD_TYPE CONSTANT VARCHAR2(30) := 'INVALID_PCARD_TYPE';
22 G_RC_INVALID_CARD_ISSUER CONSTANT VARCHAR2(30) := 'INVALID_CARD_ISSUER';
23 G_RC_INVALID_CARD_ID CONSTANT VARCHAR2(30) := 'INVALID_INSTRUMENT';
24 G_RC_INVALID_PARTY CONSTANT VARCHAR2(30) := 'INVALID_PARTY';
25 G_RC_INVALID_ADDRESS CONSTANT VARCHAR2(30) := 'INVALID_ADDRESS';
26
27 G_LKUP_INSTR_TYPE_CC CONSTANT VARCHAR2(30) := 'CREDITCARD';
28 G_LKUP_INSTR_TYPE_DC CONSTANT VARCHAR2(30) := 'DEBITCARD';
29 G_LKUP_INSTR_TYPE_PC CONSTANT VARCHAR2(30) := 'PAYMENTCARD';
30
31 -- Pad character used when encrypting credit card numbers
32 G_CCNUM_PAD CONSTANT VARCHAR2(1) := ' ';
33
34 -- Credit card billing site usage
35 G_CC_BILLING_SITE_USE CONSTANT VARCHAR2(30) := 'CC_BILLING';
36
37 -- Address Type Flags
38 G_PARTY_SITE_ID CONSTANT VARCHAR2(1) := 'S';
39 G_PARTY_SITE_USE_ID CONSTANT VARCHAR2(1) := 'U';
40
41
42
43 --
44 -- USE
45 -- Gets credit card mask settings
46 --
47 PROCEDURE Get_Mask_Settings
48 (x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
49 x_unmask_len OUT NOCOPY iby_sys_security_options.credit_card_unmask_len%TYPE
50 );
51
52 --
53 -- USE
54 -- Generates a masked credit card number based upon system mask
55 -- settings
56 --
57 FUNCTION Mask_Card_Number(p_cc_number IN iby_creditcard.ccnumber%TYPE)
58 RETURN iby_creditcard.masked_cc_number%TYPE;
59
60 FUNCTION Mask_Card_Number
61 (p_cc_number IN iby_creditcard.ccnumber%TYPE,
62 p_mask_option IN iby_creditcard.card_mask_setting%TYPE,
63 p_unmask_len IN iby_creditcard.card_unmask_length%TYPE
64 )
65 RETURN iby_creditcard.masked_cc_number%TYPE;
66
67 --
68 -- USE: Gets the credit card encryption mode setting
69 --
70 FUNCTION Get_CC_Encrypt_Mode
71 RETURN iby_sys_security_options.cc_encryption_mode%TYPE;
72
73 --
74 -- USE: Returns Y or N if other credit card attributes
75 -- e.g., chname and expirydate, are encrypted.
76 --
77 FUNCTION Other_CC_Attribs_Encrypted
78 RETURN VARCHAR2;
79
80
81 PROCEDURE Create_Card
82 (p_commit IN VARCHAR2,
83 p_owner_id IN iby_creditcard.card_owner_id%TYPE,
84 p_holder_name IN iby_creditcard.chname%TYPE,
85 p_billing_address_id IN iby_creditcard.addressid%TYPE,
86 p_address_type IN VARCHAR2 := G_PARTY_SITE_ID,
87 p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
88 p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
89 p_card_number IN iby_creditcard.ccnumber%TYPE,
90 p_expiry_date IN iby_creditcard.expirydate%TYPE,
91 p_instr_type IN iby_creditcard.instrument_type%TYPE,
92 p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
93 p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
94 p_issuer IN iby_creditcard.card_issuer_code%TYPE,
95 p_fi_name IN iby_creditcard.finame%TYPE,
96 p_single_use IN iby_creditcard.single_use_flag%TYPE,
97 p_info_only IN iby_creditcard.information_only_flag%TYPE,
98 p_purpose IN iby_creditcard.card_purpose%TYPE,
99 p_desc IN iby_creditcard.description%TYPE,
100 p_active_flag IN iby_creditcard.active_flag%TYPE,
101 p_inactive_date IN iby_creditcard.inactive_date%TYPE,
102 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
103 p_attribute_category IN iby_creditcard.attribute_category%TYPE,
104 p_attribute1 IN iby_creditcard.attribute1%TYPE,
105 p_attribute2 IN iby_creditcard.attribute2%TYPE,
106 p_attribute3 IN iby_creditcard.attribute3%TYPE,
107 p_attribute4 IN iby_creditcard.attribute4%TYPE,
108 p_attribute5 IN iby_creditcard.attribute5%TYPE,
109 p_attribute6 IN iby_creditcard.attribute6%TYPE,
110 p_attribute7 IN iby_creditcard.attribute7%TYPE,
111 p_attribute8 IN iby_creditcard.attribute8%TYPE,
112 p_attribute9 IN iby_creditcard.attribute9%TYPE,
113 p_attribute10 IN iby_creditcard.attribute10%TYPE,
114 p_attribute11 IN iby_creditcard.attribute11%TYPE,
115 p_attribute12 IN iby_creditcard.attribute12%TYPE,
116 p_attribute13 IN iby_creditcard.attribute13%TYPE,
117 p_attribute14 IN iby_creditcard.attribute14%TYPE,
118 p_attribute15 IN iby_creditcard.attribute15%TYPE,
119 p_attribute16 IN iby_creditcard.attribute16%TYPE,
120 p_attribute17 IN iby_creditcard.attribute17%TYPE,
121 p_attribute18 IN iby_creditcard.attribute18%TYPE,
122 p_attribute19 IN iby_creditcard.attribute19%TYPE,
123 p_attribute20 IN iby_creditcard.attribute20%TYPE,
124 p_attribute21 IN iby_creditcard.attribute21%TYPE,
125 p_attribute22 IN iby_creditcard.attribute22%TYPE,
126 p_attribute23 IN iby_creditcard.attribute23%TYPE,
127 p_attribute24 IN iby_creditcard.attribute24%TYPE,
128 p_attribute25 IN iby_creditcard.attribute25%TYPE,
129 p_attribute26 IN iby_creditcard.attribute26%TYPE,
130 p_attribute27 IN iby_creditcard.attribute27%TYPE,
131 p_attribute28 IN iby_creditcard.attribute28%TYPE,
132 p_attribute29 IN iby_creditcard.attribute29%TYPE,
133 p_attribute30 IN iby_creditcard.attribute30%TYPE,
134 x_result_code OUT NOCOPY VARCHAR2,
135 x_instr_id OUT NOCOPY iby_creditcard.instrid%TYPE
136 );
137
138 PROCEDURE Update_Card
139 (p_commit IN VARCHAR2,
140 p_instr_id IN iby_creditcard.instrid%TYPE,
141 p_owner_id IN iby_creditcard.card_owner_id%TYPE,
142 p_holder_name IN iby_creditcard.chname%TYPE,
143 p_billing_address_id IN iby_creditcard.addressid%TYPE,
144 p_address_type IN VARCHAR2 := G_PARTY_SITE_ID,
145 p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
146 p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
147 p_expiry_date IN iby_creditcard.expirydate%TYPE,
148 p_instr_type IN iby_creditcard.instrument_type%TYPE,
149 p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
150 p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
151 p_fi_name IN iby_creditcard.finame%TYPE,
152 p_single_use IN iby_creditcard.single_use_flag%TYPE,
153 p_info_only IN iby_creditcard.information_only_flag%TYPE,
154 p_purpose IN iby_creditcard.card_purpose%TYPE,
155 p_desc IN iby_creditcard.description%TYPE,
156 p_active_flag IN iby_creditcard.active_flag%TYPE,
157 p_inactive_date IN iby_creditcard.inactive_date%TYPE,
158 p_attribute_category IN iby_creditcard.attribute_category%TYPE,
159 p_attribute1 IN iby_creditcard.attribute1%TYPE,
160 p_attribute2 IN iby_creditcard.attribute2%TYPE,
161 p_attribute3 IN iby_creditcard.attribute3%TYPE,
162 p_attribute4 IN iby_creditcard.attribute4%TYPE,
163 p_attribute5 IN iby_creditcard.attribute5%TYPE,
164 p_attribute6 IN iby_creditcard.attribute6%TYPE,
165 p_attribute7 IN iby_creditcard.attribute7%TYPE,
166 p_attribute8 IN iby_creditcard.attribute8%TYPE,
167 p_attribute9 IN iby_creditcard.attribute9%TYPE,
168 p_attribute10 IN iby_creditcard.attribute10%TYPE,
169 p_attribute11 IN iby_creditcard.attribute11%TYPE,
170 p_attribute12 IN iby_creditcard.attribute12%TYPE,
171 p_attribute13 IN iby_creditcard.attribute13%TYPE,
172 p_attribute14 IN iby_creditcard.attribute14%TYPE,
173 p_attribute15 IN iby_creditcard.attribute15%TYPE,
174 p_attribute16 IN iby_creditcard.attribute16%TYPE,
175 p_attribute17 IN iby_creditcard.attribute17%TYPE,
176 p_attribute18 IN iby_creditcard.attribute18%TYPE,
177 p_attribute19 IN iby_creditcard.attribute19%TYPE,
178 p_attribute20 IN iby_creditcard.attribute20%TYPE,
179 p_attribute21 IN iby_creditcard.attribute21%TYPE,
180 p_attribute22 IN iby_creditcard.attribute22%TYPE,
181 p_attribute23 IN iby_creditcard.attribute23%TYPE,
182 p_attribute24 IN iby_creditcard.attribute24%TYPE,
183 p_attribute25 IN iby_creditcard.attribute25%TYPE,
184 p_attribute26 IN iby_creditcard.attribute26%TYPE,
185 p_attribute27 IN iby_creditcard.attribute27%TYPE,
186 p_attribute28 IN iby_creditcard.attribute28%TYPE,
187 p_attribute29 IN iby_creditcard.attribute29%TYPE,
188 p_attribute30 IN iby_creditcard.attribute30%TYPE,
189 x_result_code OUT NOCOPY VARCHAR2
190 );
191
192
193 PROCEDURE Query_Card
194 (p_card_id IN iby_creditcard.instrid%TYPE,
195 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
196 x_owner_id OUT NOCOPY iby_creditcard.card_owner_id%TYPE,
197 x_holder_name OUT NOCOPY iby_creditcard.chname%TYPE,
198 x_billing_address_id OUT NOCOPY iby_creditcard.addressid%TYPE,
199 x_billing_address1 OUT NOCOPY hz_locations.address1%TYPE,
200 x_billing_address2 OUT NOCOPY hz_locations.address2%TYPE,
201 x_billing_address3 OUT NOCOPY hz_locations.address3%TYPE,
202 x_billing_city OUT NOCOPY hz_locations.city%TYPE,
203 x_billing_county OUT NOCOPY hz_locations.county%TYPE,
204 x_billing_state OUT NOCOPY hz_locations.state%TYPE,
205 x_billing_zip OUT NOCOPY hz_locations.postal_code%TYPE,
206 x_billing_country OUT NOCOPY hz_locations.country%TYPE,
207 x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
208 x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
209 x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
210 x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
211 x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
212 x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
213 x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
214 x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
215 x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
216 x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
217 x_desc OUT NOCOPY iby_creditcard.description%TYPE,
218 x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
219 x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
220 x_result_code OUT NOCOPY VARCHAR2
221 );
222
223 --
224 -- USE: Unciphers a the credit card number of a stored credit card
225 -- instrument
226 -- ARGS: i_instrid => the instrument id
227 -- i_sys_sec_key => the system security key
228 --
229 FUNCTION uncipher_ccnumber
230 (p_instrid IN iby_creditcard.instrid%TYPE,
231 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
232 )
233 RETURN iby_creditcard.ccnumber%TYPE;
234
235 --
236 --
237 FUNCTION uncipher_ccnumber
238 (p_cc_number IN iby_creditcard.ccnumber%TYPE,
239 p_segment_cipher IN iby_security_segments.segment_cipher_text%TYPE,
240 p_encrypted IN iby_creditcard.encrypted%TYPE,
241 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
242 p_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
243 p_card_len IN iby_cc_issuer_ranges.card_number_length%TYPE,
244 p_cc_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
245 p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
246 p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
247 p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
248 p_unmask_digits IN iby_creditcard.masked_cc_number%TYPE
249 )
250 RETURN iby_creditcard.ccnumber%TYPE;
251
252 --
253 -- USE: Wrapper of the above function for the UI.
254 -- In the UI the SQL is executed by the framework.
255 -- We can not catch the exception thrown from the function call.
256 -- It will cause unacceptable error in the UI.
257 -- In case of exceptions this wrapper function will
258 -- simply swallow it and return null.
259 -- The UI will display empty instrument number
260 -- for this case.
261 -- ARGS: i_instrid => the instrument id
262 -- i_sys_sec_key => the system security key
263 --
264 FUNCTION uncipher_ccnumber_ui_wrp
265 (i_instrid IN iby_creditcard.instrid%TYPE,
266 i_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE)
267 RETURN iby_creditcard.ccnumber%TYPE;
268
269 --
270 -- USE: Un-encrypts all registered credit card instruments, storing
271 -- data in obfuscated form
272 --
273 -- ARGS: p_commit => whether to commit the changes
274 -- p_sys_key => system security key; used to decrypt instruments
275 --
276 PROCEDURE Decrypt_Instruments
277 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
278 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
279 );
280
281 --
282 -- USE: Encrypts all registered credit cards
283 --
284 -- ARGS: p_commit => whether to commit the changes
285 -- p_sys_key => system security key; used to encrypt instruments
286 --
287 PROCEDURE Encrypt_Instruments
288 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
289 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
290 );
291
292 --
293 -- USE: Updates instrument masks according to new setting
294 --
295 -- ARGS: p_commit => whether to commit the changes
296 -- p_sys_key => system security key; used to encrypt instruments
297 --
298 PROCEDURE Remask_Instruments
299 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
300 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
301 );
302
303 -- USE: Compresses a card number into the minimum clear text
304 -- representation
305 --
306 PROCEDURE Compress_CC_Number
307 (p_card_number IN iby_creditcard.ccnumber%TYPE,
308 p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
309 p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
313 x_unmask_digits OUT NOCOPY iby_creditcard.masked_cc_number%TYPE
310 p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
311 p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
312 x_compress_num OUT NOCOPY iby_creditcard.ccnumber%TYPE,
314 );
315
316 -- USE: Uncompresses the credit card number based upon its known digits
317 -- (range prefix, check digits, unmasked digits) and the clear text
318 -- of its comprssed digits
319 -- ARGS: p_card_number => clear text of the compressed card digits
320 -- p_card_length => actual length of the credit card (uncompressed)
321 -- p_prefix => issuer range prefix
322 -- p_digit_check => 'Y' if the card has a check digit
323 -- p_mask_setting => masking option of the card number
324 -- p_unmask_len => number of digits exposed in the mask
325 -- p_unmask_digits => unmasked digits
326 --
327 FUNCTION Uncompress_CC_Number
328 (p_card_number IN iby_creditcard.ccnumber%TYPE,
329 p_card_length IN iby_creditcard.cc_number_length%TYPE,
330 p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
331 p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
332 p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
333 p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
334 p_unmask_digits IN iby_creditcard.masked_cc_number%TYPE
335 )
336 RETURN iby_creditcard.ccnumber%TYPE;
337
338 --
339 -- USE: Gets the number of digits in a compressed credit card number
340 --
341 FUNCTION Get_Compressed_Len
342 (p_card_length IN iby_creditcard.cc_number_length%TYPE,
343 p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
344 p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
345 p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
346 p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE
347 )
348 RETURN NUMBER;
349
350 --
351 -- USE: Encrypts the other sensitive card info and returns the
352 -- corresponding security segment_IDs
353 --
354 PROCEDURE Encrypt_Card_Info
355 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
356 p_sys_security_key IN iby_security_pkg.DES3_KEY_TYPE,
357 p_expiry_date IN DATE,
358 p_chname IN VARCHAR2,
359 x_exp_segment_id OUT NOCOPY NUMBER,
360 x_chname_segment_id OUT NOCOPY NUMBER,
361 x_err_code OUT NOCOPY VARCHAR2
362 );
363
364 --
365 -- USE: Updates the EXPIRED_FLAG for all the active credit cards
366 --
367 --
368 PROCEDURE Mark_Expired_Cards
369 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
370 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
371 );
372
373 END iby_creditcard_pkg;
[Home] [Help]