1 PACKAGE BODY AP_WEB_CREDIT_CARD_PKG AS
2 /* $Header: apwccrdb.pls 120.9 2010/11/11 16:48:25 rveliche ship $ */
3
4 /* Function introduced in OIE.J
5 It returns 'Y' if a transaction has level2 data
6 and expense tpye as 'HOTEL'. Ideally, we should
7 have a column in ap_credit_card_trxns_all table which
8 should get populated during loading the transaction if
9 level2 transaction is present.
10 */
11 FUNCTION HAS_DETAILED_TRXN (p_trx_id IN VARCHAR2)
12 RETURN VARCHAR2
13 IS
14 has_detailed_trxn VARCHAR2(1) := 'N';
15 BEGIN
16 select 'Y' into has_detailed_trxn
17 from ap_credit_card_trxns_all
18 where (HOTEL_TELEPHONE_AMOUNT > 0 OR
19 HOTEL_BAR_AMOUNT > 0 OR
20 HOTEL_MOVIE_AMOUNT > 0 OR
21 HOTEL_GIFT_SHOP_AMOUNT > 0 OR
22 HOTEL_LAUNDRY_AMOUNT > 0 OR
23 HOTEL_HEALTH_AMOUNT > 0 OR
24 HOTEL_RESTAURANT_AMOUNT > 0 OR
25 HOTEL_BUSINESS_AMOUNT > 0 OR
26 HOTEL_PARKING_AMOUNT > 0 OR
27 HOTEL_ROOM_SERVICE_AMOUNT > 0 OR
28 HOTEL_TIP_AMOUNT > 0 OR
29 HOTEL_MISC_AMOUNT > 0)
30 and trx_id = p_trx_id
31 and folio_type = 'HOTEL';
32 return has_detailed_trxn;
33
34 exception when others then
35 return 'N';
36 END HAS_DETAILED_TRXN;
37
38 /* This function to create card in oracle payments
39 and return the instrument id. In case of error creating
40 instrument id it returns -1. Avoiding calls to
41 diagnostic since these are used in bulk uploading
42 */
43 function create_iby_card(p_card_number IN VARCHAR2,
44 p_party_id IN NUMBER,
45 p_exp_date IN Date DEFAULT NULL
46 ) return number IS
47 x_return_status VARCHAR2(4000);
48 x_msg_count NUMBER;
49 x_msg_data VARCHAR2(4000);
50 p_card_instrument IBY_FNDCPT_SETUP_PUB.CREDITCARD_REC_TYPE;
51 x_instr_id NUMBER;
52 x_response IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE;
53
54 begin
55 -- return instrument id if card already exists in oracle payments
56 iby_fndcpt_setup_pub.card_exists(1.0,NULL,
57 x_return_status, x_msg_count, x_msg_data,
58 null , p_card_number, -- party id is null as we reference cards through ap_cards_all.employee_id
59 p_card_instrument, x_response);
60 if (x_return_status = 'S') then
61 x_instr_id := p_card_instrument.card_id;
62 -- Inserting an update_card to set the new expiration date if it is not null
63 IF (p_exp_date IS NOT NULL) THEN
64 p_card_instrument.Expiration_Date := p_exp_date;
65 iby_fndcpt_setup_pub.update_card(1.0,NULL,'F',x_return_status,x_msg_count,x_msg_data, p_card_instrument,x_response);
66 END IF;
67 if (x_instr_id is not null) then
68 return x_instr_id;
69 end if;
70 end if;
71 p_card_instrument.Instrument_Type := 'CREDITCARD';
72 p_card_instrument.card_number := p_card_number;
73 p_card_instrument.Expiration_Date := p_exp_date;
74
75 if (p_party_id is not null) then
76 p_card_instrument.Owner_Id := p_party_id;
77 else
78 p_card_instrument.Owner_Id := null;
79 end if;
80 p_card_instrument.Info_Only_Flag := 'Y';
81 -- to register the invalid cards in IBY. Always set to 'Y' PA-DSS Implementation
82 p_card_instrument.Register_Invalid_Card := 'Y';
83 iby_fndcpt_setup_pub.create_card(1.0,NULL,'F',x_return_status,x_msg_count,x_msg_data,
84 p_card_instrument,x_instr_id,x_response);
85 if (x_return_status = 'S') then
86 return x_instr_id;
87 else
88 return -1;
89 end if;
90 exception
91 when others then
92 return -1;
93 -- raise;
94 end create_iby_card;
95
96 /* This function returns card_id from ap_cards_all for a
97 given credit card number. If card numbers does not
98 exist then it creates a card in oracle payments and
99 in ap_cards_all with appropriate references. Avoiding
100 calls to diagnostic since these are used in bulk uploading.
101 */
102 function get_card_id(p_card_number IN VARCHAR2,
103 p_card_program_id IN VARCHAR2 DEFAULT NULL,
104 p_party_id IN NUMBER DEFAULT NULL,
105 p_request_id IN VARCHAR2 DEFAULT NULL
106 ) return number IS
107 l_card_id number ;
108 l_instr_id number;
109 l_user_id number;
110
111 begin
112 -- check for cardnumber being done prior to call to this function
113 -- that saves time for context switch from java to pl/sql
114 /* if (p_card_number = null) then
115 return -1;
116 end if; */
117
118 l_instr_id := create_iby_card(p_card_number, p_party_id, null);
119 begin
120 if (l_instr_id > 0) then
121 select card_id into l_card_id
122 from ap_cards_all
123 where card_reference_id = l_instr_id
124 and card_program_id = p_card_program_id
125 and rownum = 1;
126 return l_card_id;
127 else
128 return -1;
129 end if;
130 exception when no_data_found then
131 l_user_id := fnd_global.user_id;
132 -- create a row in ap_cards_all and get card_id
133 insert into ap_cards_all (card_id,
134 card_program_id,
135 org_id,
136 card_reference_id,
137 request_id,
138 last_update_date,
139 creation_date,
140 created_by,
141 last_updated_by
142 )
143 values( ap_cards_s.nextval,
144 p_card_program_id,
145 (select org_id
146 from ap_card_programs_all apcp
147 where apcp.card_program_id = p_card_program_id and rownum =1),
148 l_instr_id,
149 p_request_id,
150 sysdate,
151 sysdate,
152 nvl(l_user_id, -1),
153 nvl(l_user_id, -1)
154 ) returning card_id into l_card_id ;
155 insert into ap_card_details (card_id,
156 request_id,
157 last_update_date,
158 creation_date,
159 created_by,
160 last_updated_by,
161 last_update_login)
162 values (l_card_id,
163 p_request_id,
164 sysdate,
165 sysdate,
166 nvl(l_user_id, -1),
167 nvl(l_user_id, -1),
168 nvl(l_user_id, -1)
169 );
170 return l_card_id;
171 end;
172 exception when others then
173 raise;
174 end get_card_id;
175
176 /* Returns card_reference_id (IBY_CREDITCARD.instrid) */
177 FUNCTION get_card_reference_id(p_document_payable_id IN NUMBER)
178 RETURN NUMBER IS
179 l_card_reference_id ap_cards_all.card_reference_id%type;
180 BEGIN
181
182 select crd.card_reference_id -- Instrument ID
183 into l_card_reference_id
184 from ap_invoices_all inv,
185 iby_docs_payable_all iby,
186 ap_expense_report_headers_all erh,
187 ap_credit_card_trxns_all ctx,
188 ap_cards_all crd
189 where iby.document_payable_id = p_document_payable_id
190 and iby.calling_app_doc_unique_ref2 = inv.invoice_id
191 and inv.invoice_id = erh.vouchno
192 and inv.invoice_type_lookup_code in ('EXPENSE REPORT', 'MIXED')
193 and inv.source in ('SelfService', 'Both Pay', 'CREDIT CARD')
194 and ctx.report_header_id = erh.bothpay_parent_id
195 and crd.card_id = ctx.card_id
196 and rownum = 1;
197
198 return(l_card_reference_id);
199
200 EXCEPTION
201 when NO_DATA_FOUND THEN
202 return null;
203 when OTHERS then
204 AP_WEB_DB_UTIL_PKG.RaiseException('get_card_reference_id');
205 APP_EXCEPTION.RAISE_EXCEPTION;
206 END get_card_reference_id;
207
208 END AP_WEB_CREDIT_CARD_PKG;
[Home] [Help]