1 PACKAGE BODY AP_WEB_CREDIT_CARD_PKG AS
2 /* $Header: apwccrdb.pls 120.7 2006/10/12 23:47:20 skoukunt 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 ) return number IS
46 x_return_status VARCHAR2(4000);
47 x_msg_count NUMBER;
48 x_msg_data VARCHAR2(4000);
49 p_card_instrument APPS.IBY_FNDCPT_SETUP_PUB.CREDITCARD_REC_TYPE;
50 x_instr_id NUMBER;
51 x_response APPS.IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE;
52
53 begin
54 -- return instrument id if card already exists in oracle payments
55 iby_fndcpt_setup_pub.card_exists(1.0,NULL,
56 x_return_status, x_msg_count, x_msg_data,
57 null , p_card_number, -- party id is null as we reference cards through ap_cards_all.employee_id
58 p_card_instrument, x_response);
59 if (x_return_status = 'S') then
60 x_instr_id := p_card_instrument.card_id;
61 if (x_instr_id is not null) then
62 return x_instr_id;
63 end if;
64 end if;
65 p_card_instrument.Instrument_Type := 'CREDITCARD';
66 p_card_instrument.card_number := p_card_number;
67 if (p_party_id is not null) then
68 p_card_instrument.Owner_Id := p_party_id;
69 else
70 p_card_instrument.Owner_Id := null;
71 end if;
72 p_card_instrument.Info_Only_Flag := 'Y';
73 iby_fndcpt_setup_pub.create_card(1.0,NULL,'F',x_return_status,x_msg_count,x_msg_data,
74 p_card_instrument,x_instr_id,x_response);
75 if (x_return_status = 'S') then
76 return x_instr_id;
77 else
78 return -1;
79 end if;
80 exception
81 when others then
82 return -1;
83 -- raise;
84 end create_iby_card;
85
86 /* This function returns card_id from ap_cards_all for a
87 given credit card number. If card numbers does not
88 exist then it creates a card in oracle payments and
89 in ap_cards_all with appropriate references. Avoiding
90 calls to diagnostic since these are used in bulk uploading.
91 */
92 function get_card_id(p_card_number IN VARCHAR2,
93 p_card_program_id IN VARCHAR2 DEFAULT NULL,
94 p_party_id IN NUMBER DEFAULT NULL,
95 p_request_id IN VARCHAR2 DEFAULT NULL
96 ) return number IS
97 l_card_id number ;
98 l_instr_id number;
99 l_user_id number;
100
101 begin
102 -- check for cardnumber being done prior to call to this function
103 -- that saves time for context switch from java to pl/sql
104 /* if (p_card_number = null) then
105 return -1;
106 end if; */
107
108 l_instr_id := create_iby_card(p_card_number, p_party_id);
109 begin
110 if (l_instr_id > 0) then
111 select card_id into l_card_id
112 from ap_cards_all
113 where card_reference_id = l_instr_id
114 and card_program_id = p_card_program_id
115 and rownum = 1;
116 return l_card_id;
117 else
118 return -1;
119 end if;
120 exception when no_data_found then
121 l_user_id := fnd_global.user_id;
122 -- create a row in ap_cards_all and get card_id
123 insert into ap_cards_all (card_id,
124 card_program_id,
125 org_id,
126 card_reference_id,
127 request_id,
128 last_update_date,
129 creation_date,
130 created_by,
131 last_updated_by
132 )
133 values( ap_cards_s.nextval,
134 p_card_program_id,
135 (select org_id
136 from ap_card_programs_all apcp
137 where apcp.card_program_id = p_card_program_id and rownum =1),
138 l_instr_id,
139 p_request_id,
140 sysdate,
141 sysdate,
142 nvl(l_user_id, -1),
143 nvl(l_user_id, -1)
144 ) returning card_id into l_card_id ;
145 insert into ap_card_details (card_id,
146 request_id,
147 last_update_date,
148 creation_date,
149 created_by,
150 last_updated_by,
151 last_update_login)
152 values (l_card_id,
153 p_request_id,
154 sysdate,
155 sysdate,
156 nvl(l_user_id, -1),
157 nvl(l_user_id, -1),
158 nvl(l_user_id, -1)
159 );
160 return l_card_id;
161 end;
162 exception when others then
163 raise;
164 end get_card_id;
165
166 /* Returns card_reference_id (IBY_CREDITCARD.instrid) */
167 FUNCTION get_card_reference_id(p_document_payable_id IN NUMBER)
168 RETURN NUMBER IS
169 l_card_reference_id ap_cards_all.card_reference_id%type;
170 BEGIN
171
172 select crd.card_reference_id -- Instrument ID
173 into l_card_reference_id
174 from ap_invoices_all inv,
175 iby_docs_payable_all iby,
176 ap_expense_report_headers_all erh,
177 ap_credit_card_trxns_all ctx,
178 ap_cards_all crd
179 where iby.document_payable_id = p_document_payable_id
180 and iby.calling_app_doc_unique_ref2 = inv.invoice_id
181 and inv.invoice_id = erh.vouchno
182 and inv.invoice_type_lookup_code in ('EXPENSE REPORT', 'MIXED')
183 and inv.source in ('SelfService', 'Both Pay', 'CREDIT CARD')
184 and ctx.report_header_id = erh.bothpay_parent_id
185 and crd.card_id = ctx.card_id
186 and rownum = 1;
187
188 return(l_card_reference_id);
189
190 EXCEPTION
191 when NO_DATA_FOUND THEN
192 return null;
193 when OTHERS then
194 AP_WEB_DB_UTIL_PKG.RaiseException('get_card_reference_id');
195 APP_EXCEPTION.RAISE_EXCEPTION;
196 END get_card_reference_id;
197
198 END AP_WEB_CREDIT_CARD_PKG;
[Home] [Help]