[Home] [Help]
PACKAGE BODY: APPS.AR_ARXSOC2_XMLP_PKG
Source
1 PACKAGE BODY AR_ARXSOC2_XMLP_PKG AS
2 /* $Header: ARXSOC2B.pls 120.1 2008/01/07 14:51:58 abraghun noship $ */
3 function BeforeReport return boolean is
4 begin
5 return (TRUE);
6 end;
7 function AfterReport return boolean is
8 begin
9 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
10 return (TRUE);
11 end;
12 function report_nameformula(Company_Name in varchar2) return varchar2 is
13 begin
14 DECLARE
15 l_report_name VARCHAR2(80);
16 l_date_low VARCHAR2 (11);
17 l_date_high VARCHAR2 (11);
18 BEGIN
19 if p_date_low is NULL then
20 l_date_low := ' ';
21 else
22 l_date_low := TO_CHAR(p_date_low, 'DD-MON-YYYY') ;
23 end if ;
24 if p_date_high is NULL then
25 l_date_high := ' ';
26 else
27 l_date_high := TO_CHAR(p_date_high, 'DD-MON-YYYY') ;
28 end if ;
29 rp_date_range := arp_standard.fnd_message('ARXSOC_DEPOSIT_DATE_RANGE',
30 'FROM_DATE',l_date_low,
31 'TO_DATE', l_date_high);
32 RP_Company_Name := Company_Name;
33 SELECT SUBSTR(cp.user_concurrent_program_name, 1, 80)
34 INTO l_report_name
35 FROM FND_CONCURRENT_PROGRAMS_VL cp,
36 FND_CONCURRENT_REQUESTS cr
37 WHERE cr.request_id = P_CONC_REQUEST_ID
38 AND cp.application_id = cr.program_application_id
39 AND cp.concurrent_program_id = cr.concurrent_program_id;
40 l_report_name:= substr(l_report_name,1,instr(l_report_name,' (XML)'));
41 RP_Report_Name := l_report_name;
42 RETURN(l_report_name);
43 EXCEPTION
44 WHEN NO_DATA_FOUND
45 THEN RP_REPORT_NAME := null;
46 RETURN(null);
47 END;
48 RETURN NULL; end;
49 function c_difference_amountformula(c_rcpt_control_amount in number, c_actual_amount in number) return number is
50 begin
51 /*srw.reference (c_rcpt_control_amount);*/null;
52 /*srw.reference (c_actual_amount);*/null;
53 return ( nvl(c_rcpt_control_amount,0) - nvl(c_actual_amount,0) );
54 end;
55 function c_summary_labelformula(Currency_A in varchar2) return varchar2 is
56 begin
57 return (rtrim(rpad(Currency_A,3)));
58 end;
59 function ca_difference_amountformula(c_rcpt_control_amount_B in number, ca_actual_amount in number) return number is
60 begin
61 /*srw.reference (c_rcpt_control_amount_B);*/null;
62 /*srw.reference (ca_actual_amount);*/null;
63 return ( nvl(c_rcpt_control_amount_B,0) - nvl(ca_actual_amount,0) );
64 end;
65 function ca_summary_labelformula(Currency_B in varchar2) return varchar2 is
66 begin
67 return (rtrim(rpad(Currency_B,3)) );
68 end;
69 function cf_data_not_foundformula(bank_account_name_C in varchar2) return number is
70 begin
71 rp_data_found3 := bank_account_name_C ;
72 return (0);
73 end;
74 function cr_data_foundformula(Currency_B in varchar2) return number is
75 begin
76 rp_data_found2 := Currency_B ;
77 return (0);
78 end;
79 function cm_data_not_foundformula(Currency_A in varchar2) return number is
80 begin
81 rp_data_found1 := Currency_A ;
82 return (0);
83 end;
84 function AfterPForm return boolean is
85 begin
86 DECLARE
87 l_bank_count NUMBER (10);
88 begin
89 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
90 P_DATE_LOW1 := to_char(P_DATE_LOW,'DD-MON-YY');
91 P_DATE_HIGH1 := to_char(P_DATE_HIGH,'DD-MON-YY');
92 ph_order_by := p_order_by ;
93 if upper ( substr (p_order_by,1,1) ) = 'B' then
94 p_order_by_1 := 'Bank';
95 else
96 p_order_by_1 := 'Currency';
97 end if ;
98 if p_date_low is NOT NULL then
99 lp_date_low := ' and deposit_date >= :p_date_low ' ;
100 end if ;
101 if p_date_high is NOT NULL then
102 lp_date_high := ' and deposit_date <= :p_date_high ' ;
103 end if ;
104 if p_bank_account_name_low is NOT NULL then
105 lp_bank_account_name_low := ' and cba.bank_account_name >= :p_bank_account_name_low ' ;
106 end if ;
107 if p_bank_account_name_high is NOT NULL then
108 lp_bank_account_name_high := ' and cba.bank_account_name <= :p_bank_account_name_high ' ;
109 end if ;
110 select count (*)
111 into l_bank_count
112 from ce_bank_accounts cba,
113 ce_bank_acct_uses ba,
114 ce_bank_branches_v bb
115 where ba.bank_acct_use_id in
116 (select distinct remit_bank_acct_use_id from ar_cash_receipts)
117 and cba.bank_branch_id = bb.branch_party_id
118 and cba.bank_account_name
119 between decode(p_bank_account_name_low,
120 NULL, cba.bank_account_name,
121 p_bank_account_name_low)
122 and decode(p_bank_account_name_high,
123 NULL, cba.bank_account_name,
124 p_bank_account_name_high)
125 ;
126 p_bank_count := l_bank_count ;
127 end ;
128 return (TRUE);
129 end;
130 function f_amountsformula(cr_status in varchar2, amount in number, cr_type in varchar2, reversal_category in varchar2, cash_receipt_id in number) return number is
131 begin
132 DECLARE
133 l_actual_amount NUMBER := 0;
134 l_unidentified_amount NUMBER := 0;
135 l_misc_amount NUMBER := 0;
136 l_nsf_amount NUMBER := 0;
137 l_on_account_amount NUMBER := 0;
138 l_applied_count NUMBER(10) := 0;
139 l_unapplied_count NUMBER(10) := 0;
140 l_unidentified_count NUMBER(10) := 0;
141 l_misc_count NUMBER(10) := 0;
142 BEGIN
143 /*srw.reference (amount);*/null;
144 /*srw.reference (cr_status);*/null;
145 /*srw.reference (cr_type);*/null;
146 /*srw.reference (reversal_category);*/null;
147 select
148 decode(cr_status, 'REV', 0, amount),
149 decode(cr_status, 'UNID', amount, 0),
150 decode(cr_type,
151 'MISC', decode(cr_status,
152 'REV', 0,
153 amount),
154 0),
155 decode(cr_status, reversal_category,
156 decode(cr_status, 'NSF', amount, 'STOP', amount, 0), 0),
157 decode(cr_status, 'APP', 1, 0),
158 decode(cr_status, 'UNAPP', 1, 0),
159 decode(cr_status, 'UNID', 1, 0),
160 decode(cr_type,
161 'MISC', decode(cr_status,
162 'REV', 0,
163 1),
164 0)
165 into
166 l_actual_amount,
167 l_unidentified_amount,
168 l_misc_amount,
169 l_nsf_amount,
170 l_applied_count,
171 l_unapplied_count,
172 l_unidentified_count,
173 l_misc_count
174 from dual;
175 if cr_status = 'APP' then
176 select sum(amount_applied)
177 into l_on_account_amount
178 from ar_receivable_applications
179 where cash_receipt_id = cash_receipt_id
180 and status = 'ACC';
181 if nvl(l_on_account_amount,0) <> 0 then
182 l_unapplied_count := l_unapplied_count + 1;
183 l_applied_count := l_applied_count - 1;
184 end if;
185 end if;
186 p_actual_amount := l_actual_amount;
187 p_unidentified_amount := l_unidentified_amount;
188 p_misc_amount := l_misc_amount;
189 p_nsf_amount := l_nsf_amount ;
190 p_applied_count := l_applied_count - l_misc_count;
191 p_unapplied_count := l_unapplied_count + l_unidentified_count ;
192 p_misc_count := l_misc_count;
193 return(1);
194 END;
195 RETURN NULL; end;
196 function c_unapplied_amountformula(c_unapplied_amount_A in number, c_on_account_amount in number, c_unidentified_amount in number) return number is
197 begin
198 /*srw.reference (c_unapplied_amount_A);*/null;
199 /*srw.reference (c_on_account_amount);*/null;
200 /*srw.reference (c_unidentified_amount);*/null;
201 return ( nvl(c_unapplied_amount_A,0) + nvl(c_on_account_amount,0) + nvl(c_unidentified_amount,0) );
202 end;
203 function f_all_amountsformula(cr_status_BB in varchar2, amount_B in number, cr_type_B in varchar2, reversal_category_B in varchar2, cash_receipt_id_B in number) return number is
204 begin
205 DECLARE
206 l_actual_amount NUMBER := 0;
207 l_unidentified_amount NUMBER := 0;
208 l_misc_amount NUMBER := 0;
209 l_nsf_amount NUMBER := 0;
210 l_on_account_amount NUMBER := 0;
211 l_applied_count NUMBER(10) := 0;
212 l_unapplied_count NUMBER(10) := 0;
213 l_unidentified_count NUMBER(10) := 0;
214 l_misc_count NUMBER (10) := 0;
215 BEGIN
216 /*srw.reference (amount_B);*/null;
217 /*srw.reference (cr_status_BB);*/null;
218 /*srw.reference (cr_type_B);*/null;
219 /*srw.reference (reversal_category_B);*/null;
220 select
221 decode(cr_status_BB, 'REV', 0, amount_B),
222 decode(cr_status_BB, 'UNID', amount_B, 0),
223 decode(cr_type_B,
224 'MISC', decode(cr_status_BB,
225 'REV', 0,
226 amount_B),
227 0),
228 decode(cr_status_BB, reversal_category_B,
229 decode(cr_status_BB, 'NSF', amount_B, 'STOP', amount_B, 0), 0),
230 decode(cr_status_BB, 'APP', 1, 0),
231 decode(cr_status_BB, 'UNAPP', 1, 0),
232 decode(cr_status_BB, 'UNID', 1, 0),
233 decode(cr_type_B,
234 'MISC', decode(cr_status_BB,
235 'REV', 0,
236 1),
237 0)
238 into
239 l_actual_amount,
240 l_unidentified_amount,
241 l_misc_amount,
242 l_nsf_amount,
243 l_applied_count,
244 l_unapplied_count,
245 l_unidentified_count,
246 l_misc_count
247 from dual;
248 if cr_status_BB = 'APP' then
249 select sum(amount_applied)
250 into l_on_account_amount
251 from ar_receivable_applications
252 where cash_receipt_id = cash_receipt_id_B
253 and status = 'ACC';
254 if nvl(l_on_account_amount,0) <> 0 then
255 l_unapplied_count := l_unapplied_count + 1;
256 l_applied_count := l_applied_count - 1;
257 end if;
258 end if;
259 pa_actual_amount := l_actual_amount;
260 pa_unidentified_amount := l_unidentified_amount;
261 pa_misc_amount := l_misc_amount;
262 pa_nsf_amount := l_nsf_amount ;
263 pa_applied_count := l_applied_count - l_misc_count;
264 pa_unapplied_count := l_unapplied_count + l_unidentified_count ;
265 pa_misc_count := l_misc_count;
266 return(1);
267 END;
268 RETURN NULL; end;
269 function ca_unapplied_amountformula(ca_unapplied_amount_B in number, ca_on_account_amount in number, ca_unidentified_amount in number) return number is
270 begin
271 /*srw.reference (ca_unapplied_amount_B);*/null;
272 /*srw.reference (ca_on_account_amount);*/null;
273 /*srw.reference (ca_unidentified_amount);*/null;
274 return ( nvl(ca_unapplied_amount_B,0) + nvl(ca_on_account_amount,0) + nvl(ca_unidentified_amount,0) );
275 end;
276 function Order_By_MeaningFormula return VARCHAR2 is
277 begin
278 declare
279 l_order_by VARCHAR2(80);
280 begin
281 select meaning
282 into l_order_by
283 from ar_lookups
284 where lookup_type = 'SORT_BY_ARXSOC2'
285 and lookup_code = PH_ORDER_BY ;
286 rp_order_by := l_order_by ;
287 return( l_order_by );
288 exception
289 WHEN NO_DATA_FOUND THEN
290 return(' ');
291 end ;
292 RETURN NULL; end;
293 --Functions to refer Oracle report placeholders--
294 Function p_actual_amount_p return number is
295 Begin
296 return p_actual_amount;
297 END;
298 Function p_unidentified_amount_p return number is
299 Begin
300 return p_unidentified_amount;
301 END;
302 Function p_misc_amount_p return number is
303 Begin
304 return p_misc_amount;
305 END;
306 Function p_nsf_amount_p return number is
307 Begin
308 return p_nsf_amount;
309 END;
310 Function p_applied_count_p return number is
311 Begin
312 return p_applied_count;
313 END;
314 Function p_unapplied_count_p return number is
315 Begin
316 return p_unapplied_count;
317 END;
318 Function p_misc_count_p return number is
319 Begin
320 return p_misc_count;
321 END;
322 Function pa_actual_amount_p return number is
323 Begin
324 return pa_actual_amount;
325 END;
326 Function pa_unidentified_amount_p return number is
327 Begin
328 return pa_unidentified_amount;
329 END;
330 Function pa_misc_amount_p return number is
331 Begin
332 return pa_misc_amount;
333 END;
334 Function pa_nsf_amount_p return number is
335 Begin
336 return pa_nsf_amount;
337 END;
338 Function pa_applied_count_p return number is
339 Begin
340 return pa_applied_count;
341 END;
342 Function pa_unapplied_count_p return number is
343 Begin
344 return pa_unapplied_count;
345 END;
346 Function pa_misc_count_p return number is
347 Begin
348 return pa_misc_count;
349 END;
350 Function RP_COMPANY_NAME_p return varchar2 is
351 Begin
352 return RP_COMPANY_NAME;
353 END;
354 Function RP_REPORT_NAME_p return varchar2 is
355 Begin
356 return RP_REPORT_NAME;
357 END;
358 Function RP_DATA_FOUND3_p return varchar2 is
359 Begin
360 return RP_DATA_FOUND3;
361 END;
362 Function RP_DATE_RANGE_p return varchar2 is
363 Begin
364 return RP_DATE_RANGE;
365 END;
366 Function RP_DATA_FOUND1_p return varchar2 is
367 Begin
368 return RP_DATA_FOUND1;
369 END;
370 Function RP_DATA_FOUND2_p return varchar2 is
371 Begin
372 return RP_DATA_FOUND2;
373 END;
374 Function RP_ORDER_BY_p return varchar2 is
375 Begin
376 return RP_ORDER_BY;
377 END;
378 END AR_ARXSOC2_XMLP_PKG ;
379