[Home] [Help]
PACKAGE BODY: APPS.AP_APXCCPUT_XMLP_PKG
Source
1 PACKAGE BODY AP_APXCCPUT_XMLP_PKG AS
2 /* $Header: APXCCPUTB.pls 120.0 2007/12/27 07:33:07 vjaganat noship $ */
3
4 function cf_masked_card_numberformula(C_TRX_CARD_NUMBER in varchar2) return char is
5 begin
6 CP_MASKED_CARD_NUMBER := C_TRX_CARD_NUMBER;
7 return null;
8 end;
9
10 function BeforeReport return boolean is
11 begin
12
13 LP_START_DATE := to_char(P_START_DATE, 'DD-MON-YYYY');
14 LP_END_DATE := to_char(P_END_DATE, 'DD-MON-YYYY');
15
16 DECLARE
17
18 init_failure EXCEPTION;
19
20
21 BEGIN
22
23 IF p_org_id is not null then
24
25 SELECT NAME
26 INTO c_organization_name
27 FROM HR_OPERATING_UNITS
28 WHERE ORGANIZATION_ID = p_org_id;
29
30 END IF;
31
32
33 /*SRW.MESSAGE('1','INPUT PARAMETERS');*/null;
34
35 /*SRW.MESSAGE('1','PROCESS :'||p_process);*/null;
36
37 /*SRW.MESSAGE('1','POSTED START DATE :'||p_start_date);*/null;
38
39 /*SRW.MESSAGE('1','POSTED END DATE :'||p_end_date);*/null;
40
41 /*SRW.MESSAGE('1','OPERATING UNIT :'||c_organization_name);*/null;
42
43 /*SRW.MESSAGE('1','CARD ID :'||p_card_number);*/null;
44
45 /*SRW.MESSAGE('1','TRANSACTION AMOUNT :'||p_amount);*/null;
46
47
48
49 IF p_org_id is null THEN
50 c_organization_name := 'All';
51 END IF;
52
53 IF (p_trace_switch = 'Y') THEN
54 /*SRW.MESSAGE('2','Trace On');*/null;
55
56 /*SRW.DO_SQL('alter session set sql_trace TRUE');null;*/
57 execute immediate 'alter session set sql_trace TRUE';
58
59 END IF;
60
61
62 /*SRW.USER_EXIT('FND SRWINIT');*/null;
63
64
65
66 IF (p_debug_switch = 'Y') THEN
67 /*SRW.MESSAGE('2','Before GET_NLS_STRINGS');*/null;
68
69 END IF;
70
71 IF (get_nls_strings() <> TRUE) THEN RAISE init_failure;
72 END IF;
73
74
75
76 IF (p_debug_switch = 'Y') THEN
77 /*SRW.MESSAGE('3','After Get_NLS_Strings');*/null;
78
79 END IF;
80
81
82
83
84
85
86 IF (p_debug_switch = 'Y') THEN
87 /*SRW.BREAK;*/null;
88
89 END IF;
90
91
92
93 RETURN (TRUE);
94
95
96
97 EXCEPTION
98
99 WHEN OTHERS THEN
100
101 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
102
103
104 END;
105 return (TRUE);
106 end;
107
108 FUNCTION get_nls_strings RETURN BOOLEAN IS
109 nls_all ap_lookup_codes.displayed_field%TYPE; nls_yes fnd_lookups.meaning%TYPE; nls_no fnd_lookups.meaning%TYPE;
110 BEGIN
111
112 nls_all := '';
113 nls_yes := '';
114 nls_no := '';
115
116 SELECT ly.meaning,
117 ln.meaning,
118 la.displayed_field
119 INTO nls_yes, nls_no, nls_all
120 FROM fnd_lookups ly, fnd_lookups ln, ap_lookup_codes la
121 WHERE ly.lookup_type = 'YES_NO'
122 AND ly.lookup_code = 'Y'
123 AND ln.lookup_type = 'YES_NO'
124 AND ln.lookup_code = 'N'
125 AND la.lookup_type = 'NLS REPORT PARAMETER'
126 AND la.lookup_code = 'ALL';
127
128 c_nls_yes := nls_yes;
129 c_nls_no := nls_no;
130 c_nls_all := nls_all;
131
132 /*SRW.USER_EXIT('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_APPRVL_NO_DATA"');*/null;
133
134 /*SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":c_nls_no_data_exists"');*/null;
135
136 /*c_nls_no_data_exists := '*** '||c_nls_no_data_exists||' ***';*/
137 c_nls_no_data_exists := 'No Data Found';
138
139 /*SRW.USER_EXIT('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_ALL_END_OF_REPORT"');*/null;
140
141 /*SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":c_nls_end_of_report"');*/null;
142
143 /*c_nls_end_of_report := '*** '||c_nls_end_of_report||' ***';*/
144 c_nls_end_of_report := 'End of Report';
145
146 RETURN (TRUE);
147
148 RETURN NULL; EXCEPTION
149 WHEN OTHERS THEN
150 RETURN (FALSE);
151 END;
152
153 function c_trx_amount_fformula(C_TRX_AMOUNT_F in varchar2) return char is
154 l_trx_amount_f varchar2(20);
155 begin
156 /*SRW.REFERENCE(C_TRX_AMOUNT);*/null;
157
158
159
160 L_TRX_AMOUNT_F:=LTRIM(C_TRX_AMOUNT_F);
161
162 RETURN(L_TRX_AMOUNT_F);
163 end;
164
165 function AfterReport return boolean is
166 begin
167
168 DECLARE
169
170 init_failure EXCEPTION;
171
172 BEGIN
173
174 IF(custom_init() <> TRUE) THEN
175 RAISE init_failure;
176 END IF;
177
178 IF (p_debug_switch = 'Y') THEN
179 /*SRW.MESSAGE('7','After Custom_Init');*/null;
180
181 END IF;
182
183
184 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
185
186
187 IF (P_DEBUG_SWITCH = 'Y') THEN
188 /*SRW.MESSAGE('20','After SRWEXIT');*/null;
189
190 END IF;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
195
196 END;
197
198 return (TRUE);
199 end;
200
201 FUNCTION CUSTOM_INIT RETURN BOOLEAN IS
202 l_user_id NUMBER;
203 BEGIN
204
205 l_user_id := FND_GLOBAL.user_id;
206
207 IF p_process = 'DEACTIVATE' THEN
208
209 update ap_credit_card_trxns
210 set CATEGORY = 'DEACTIVATED',
211 REPORT_HEADER_ID = -1,
212 LAST_UPDATE_DATE = sysdate,
213 LAST_UPDATED_BY = l_user_id
214 WHERE REPORT_HEADER_ID IS NULL
215 AND CATEGORY IS NULL
216 AND ORG_ID = NVL(p_org_id, ORG_ID)
217 AND TRANSACTION_AMOUNT = NVL(p_amount, TRANSACTION_AMOUNT)
218 AND POSTED_DATE between (nvl(p_start_date, POSTED_DATE-1))
219 and p_end_date
220 AND CARD_ID = NVL(p_card_number, CARD_ID)
221 AND INACTIVE_EMP_WF_ITEM_KEY is NULL;
222
223 ELSE
224
225
226 update ap_credit_card_trxns
227 set CATEGORY = NULL,
228 REPORT_HEADER_ID = NULL,
229 LAST_UPDATE_DATE = sysdate,
230 LAST_UPDATED_BY = l_user_id
231 WHERE REPORT_HEADER_ID = -1
232 AND CATEGORY = 'DEACTIVATED'
233 AND ORG_ID = NVL(p_org_id, ORG_ID)
234 AND TRANSACTION_AMOUNT = NVL(p_amount, TRANSACTION_AMOUNT)
235 AND POSTED_DATE between (nvl(p_start_date, POSTED_DATE-1))
236 and p_end_date
237 AND CARD_ID = NVL(p_card_number, CARD_ID)
238 AND INACTIVE_EMP_WF_ITEM_KEY is NULL;
239 END IF;
240
241 IF p_send_notifications = 'Y' THEN
242 senddeactivated() ;
243 END IF;
244 RETURN TRUE;
245
246 END;
247
248 PROCEDURE SendDeactivated IS
249 v_employee_id ap_cards.employee_id%type;
250 v_card_program_id ap_cards.card_program_id%type;
251
252 cursor cur_sendDeactivated is
253 select distinct card.employee_id, card.card_program_id
254 from ap_credit_card_trxns txn,
255 ap_cards card
256 where txn.report_header_id = -1
257 and txn.category = 'DEACTIVATED'
258 and txn.org_id = nvl(p_org_id, txn.org_id)
259 and txn.card_id = nvl(p_card_number, txn.card_id)
260 and txn.transaction_amount = nvl(p_amount, txn.transaction_amount)
261 and txn.posted_date between (nvl(p_start_date, txn.posted_date-1))
262 and p_end_date
263 and txn.card_program_id = card.card_program_id
264 and txn.card_id = card.card_id
265 and card.org_id = nvl(p_org_id, card.org_id)
266 and txn.org_id = card.org_id;
267
268
269 BEGIN
270 open cur_sendDeactivated;
271 loop
272
273 fetch cur_sendDeactivated into v_employee_id, v_card_program_id;
274 exit when cur_sendDeactivated%notfound;
275
276 /*srw.message(999, 'Deactivated transactions for employee: ' || to_char(v_employee_id) ||
277 ' card program: ' || to_char(v_card_program_id) );*/null;
278
279
280 AP_WEB_CREDIT_CARD_WF.SendDeactivatedNotif(v_employee_id,
281 v_card_program_id,
282 p_end_date);
283 end loop;
284
285 close cur_sendDeactivated;
286
287 EXCEPTION
288 WHEN OTHERS THEN
289 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
290
291
292 END;
293
294 --Functions to refer Oracle report placeholders--
295
296 Function CP_MASKED_CARD_NUMBER_p return varchar2 is
297 Begin
298 return CP_MASKED_CARD_NUMBER;
299 END;
300 Function C_NLS_YES_p return varchar2 is
301 Begin
302 return C_NLS_YES;
303 END;
304 Function C_NLS_NO_p return varchar2 is
305 Begin
306 return C_NLS_NO;
307 END;
308 Function C_NLS_ALL_p return varchar2 is
309 Begin
310 return C_NLS_ALL;
311 END;
312 Function C_NLS_NO_DATA_EXISTS_p return varchar2 is
313 Begin
314 return C_NLS_NO_DATA_EXISTS;
315 END;
316 Function C_NLS_END_OF_REPORT_p return varchar2 is
317 Begin
318 return C_NLS_END_OF_REPORT;
319 END;
320 Function C_ORGANIZATION_NAME_p return varchar2 is
321 Begin
322 return C_ORGANIZATION_NAME;
323 END;
324 END AP_APXCCPUT_XMLP_PKG ;
325
326