[Home] [Help]
PACKAGE BODY: APPS.AR_ARXCPH_XMLP_PKG
Source
1 PACKAGE BODY AR_ARXCPH_XMLP_PKG AS
2 /* $Header: ARXCPHB.pls 120.0 2007/12/27 13:43:08 abraghun noship $ */
3
4 function BeforeReport return boolean is
5 begin
6 P_CONC_REQUEST_ID:=FND_GLOBAL.conc_request_id;
7 CP_IN_TRX_DATE_LOW := to_char(P_IN_TRX_DATE_LOW,'DD-MON-YY');
8 CP_IN_TRX_DATE_HIGH :=to_char(P_IN_TRX_DATE_HIGH,'DD-MON-YY');
9 /*SRW.USER_EXIT('FND SRWINIT');*/null;
10
11
12 begin
13 /*srw.reference(SORT_BY_PHONETICS);*/null;
14
15 /*srw.user_exit('FND GETPROFILE
16 NAME="RA_CUSTOMERS_SORT_BY_PHONETICS"
17 FIELD="SORT_BY_PHONETICS"
18 PRINT_ERROR ="N"');*/null;
19
20
21 if SORT_BY_PHONETICS = 'Y' then
22
23 P_SORT := 'PARTY.ORGANIZATION_NAME_PHONETIC';
24
25 else
26
27 P_SORT := 'PARTY.PARTY_NAME';
28
29 end if;
30
31 exception when others then
32
33 P_SORT := 'PARTY.PARTY_NAME';
34
35 end;
36
37 return (TRUE);
38 end;
39
40 function AfterReport return boolean is
41 begin
42
43 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
44 return (TRUE);
45 end;
46
47 function AfterPForm return boolean is
48 sdate DATE;
49 begin
50
51
52 /*srw.message (1000, 'DEBUG: Doing some sysdate thing.');*/null;
53
54
55 select sysdate
56 into sdate
57 from dual;
58
59 p_sysdate := sdate;
60 if p_in_inv_num_low IS NOT NULL then
61 lp_invoice_num_low := ' and ar_payment_schedules.trx_number >= :p_in_inv_num_low ' ;
62 end if;
63
64 if p_in_inv_num_high IS NOT NULL then
65 lp_invoice_num_high := ' and ar_payment_schedules.trx_number <= :p_in_inv_num_high ' ;
66 end if;
67
68
69 /*srw.message (1000, 'DEBUG: Setting the Customer Name Range.');*/null;
70
71
72 if p_in_customer_low is not null then
73 lp_customer_name_low := ' and PARTY.PARTY_NAME >= :p_in_customer_low ';
74 end if;
75
76 if p_in_customer_high is not null then
77 lp_customer_name_high := ' and PARTY.PARTY_NAME <= :p_in_customer_high ';
78 end if;
79
80
81 /*srw.message (1000, 'DEBUG: Setting the Customer Number Range.');*/null;
82
83
84 if p_in_customer_num_low is not null then
85 lp_cust_num_low := ' and CUST.ACCOUNT_NUMBER >= :p_in_customer_num_low ';
86 end if;
87
88 if p_in_customer_num_high is not null then
89 lp_cust_num_high := ' and CUST.ACCOUNT_NUMBER <=:p_in_customer_num_high ';
90 end if;
91
92
93 /*srw.message (1000, 'DEBUG: Setting the Trx Date Range.');*/null;
94
95
96 if p_in_trx_date_low is not null then
97 lp_trx_date_low := ' and ar_receivable_applications.apply_date >= :p_in_trx_date_low ';
98 end if;
99
100 if p_in_trx_date_high is not null then
101 lp_trx_date_high := ' and ar_receivable_applications.apply_date <= :p_in_trx_date_high ';
102 else
103 lp_trx_date_high := ' and ar_receivable_applications.apply_date <= :p_sysdate ';
104 end if;
105
106
107 /*srw.message (1000, 'DEBUG: Setting the Collector Range.');*/null;
108
109
110
111 if p_in_collector_low is not null then
112 lp_collector_low := ' and ar_collectors.name >= :p_in_collector_low ';
113 end if;
114
115 if p_in_collector_high is not null then
116 lp_collector_high := ' and ar_collectors.name <= :p_in_collector_high ';
117 end if;
118
119
120 /*srw.message (1000, 'DEBUG: Setting the Terms Range.');*/null;
121
122
123 if p_in_terms_low is not null then
124 lp_terms_low := ' and nvl(ra_terms.name,''XX'') >= :p_in_terms_low ';
125 end if;
126
127 if p_in_terms_high is not null then
128 lp_terms_high := ' and nvl(ra_terms.name,''XX'') <= :p_in_terms_high ';
129 end if;
130
131 return (TRUE);
132
133 end;
134
135 function report_nameformula(Company_Name in varchar2) return varchar2 is
136 l_report_name VARCHAR2(80);
137 BEGIN
138 RP_Company_Name := Company_Name;
139 RP_Report_Name := '';
140
141 SELECT substr(cp.user_concurrent_program_name,1,80)
142 INTO l_report_name
143 FROM FND_CONCURRENT_PROGRAMS_VL cp,
144 FND_CONCURRENT_REQUESTS cr
145 WHERE cr.request_id = P_CONC_REQUEST_ID
146 AND cp.application_id = cr.program_application_id
147 AND cp.concurrent_program_id = cr.concurrent_program_id;
148
149 RP_Report_Name := substr(l_report_name,1,instr(l_report_name,' (XML)'));
150
151 /*srw.message (1000, 'DEBUG: Concurrent Request Id: ' || to_char (P_Conc_Request_Id) );*/null;
152
153 /*srw.message (1000, 'DEBUG: Report Name: ' || RP_Report_Name);*/null;
154
155
156 RETURN(l_report_name);
157 RETURN NULL; EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 /*srw.message (1000, 'DEBUG: Report Name not found.');*/null;
160
161 RETURN('Receipt Analysis - Days Late');
162 END;
163
164 function Report_SubtitleFormula return Char is
165 begin
166
167 begin
168
169
170 RP_SUB_TITLE:= SUBSTRB(ARP_STANDARD.FND_MESSAGE(
171 'AR_REPORTS_TRX_DATE_FROM_TO',
172 'FROM_DATE',p_in_trx_date_low,
173 'TO_DATE',p_in_trx_date_high),1,80);
174
175 return(1);
176 end;
177 end;
178
179 function Sort_OrderFormula return VARCHAR2 is
180 begin
181
182 declare
183 sorting_order VARCHAR2(80);
184 begin
185 select meaning
186 into sorting_order
187 from ar_lookups
188 where lookup_type='SORT_BY_ARXCPH'
189 and lookup_code = p_in_sorting_order;
190
191 RP_Sort_Order := sorting_order;
192
193 return(sorting_order);
194
195 end;
196
197 RETURN NULL; end;
198
199 function average_days_lateformula(rec_counter in number, sum_days_late in number) return number is
200 begin
201
202 declare
203 av_days_late number(10);
204 begin
205 /*srw.reference(sum_days_late);*/null;
206
207 /*srw.reference(rec_counter);*/null;
208
209
210 if rec_counter <> 0 then
211 av_days_late := sum_days_late / rec_counter;
212 end if;
213
214 return(av_days_late);
215 end;
216 RETURN NULL; end;
217
218 function wt_avg_days_lateformula(sum_payment in number, sum_w_days_late in number) return number is
219 begin
220
221 declare
222 wt_avg_days_late number(20);
223 begin
224 /*srw.reference(sum_w_days_late);*/null;
225
226 /*srw.reference(sum_payment);*/null;
227
228
229 if (sum_payment <> 0 ) then
230 wt_avg_days_late := sum_w_days_late / sum_payment;
231 end if;
232
233 return(wt_avg_days_late);
234 end;
235 RETURN NULL; end;
236
237 function skip_inv_sumformula(invoice_amount in number, customer_id in number, currency_code in varchar2, customer_trx_id in number, terms_sequence_number in number) return number is
238 begin
239
240 declare
241
242 begin
243
244 /*srw.reference(currency_code);*/null;
245
246 /*srw.reference(invoice_amount);*/null;
247
248 /*srw.reference(prev_currency_code);*/null;
249
250 /*srw.reference(skip_sum);*/null;
251
252 /*srw.reference(customer_trx_id);*/null;
253
254 /*srw.reference(prev_customer_trx_id);*/null;
255
256 /*srw.reference(prev_customer_id);*/null;
257
258 /*srw.reference(prev_terms);*/null;
259
260
261 if (prev_customer_id is null) then
262 skip_sum := invoice_amount;
263 else
264 if (prev_customer_id <> customer_id) OR (prev_currency_code <> currency_code) then
265 skip_sum := 0;
266 end if;
267 end if;
268
269 if (prev_customer_trx_id <> customer_trx_id ) OR (prev_terms <> terms_sequence_number) then
270 skip_sum := nvl(skip_sum,0) + invoice_amount;
271 end if;
272
273 prev_customer_id := customer_id;
274 prev_currency_code := currency_code;
275 prev_customer_trx_id := customer_trx_id;
276 prev_terms := terms_sequence_number;
277
278 return (skip_sum);
279
280 end;
281
282
283 RETURN NULL; end;
284
285 function set_addr_flagformula(address_id in number) return number is
286 begin
287
288 begin
289 /*srw.reference(address_id);*/null;
290
291 /*srw.reference(prev_addr_id);*/null;
292
293
294 if prev_addr_id <> address_id then
295 addr_prn_flag := 'Y' ;
296 else
297 addr_prn_flag := 'N' ;
298 end if;
299 prev_addr_id := address_id;
300 return(1);
301 end;
302
303
304
305
306 RETURN NULL; end;
307
308 --Functions to refer Oracle report placeholders--
309
310 Function Skip_Sum_p return number is
311 Begin
312 return Skip_Sum;
313 END;
314 Function Addr_Prn_Flag_p return varchar2 is
315 Begin
316 return Addr_Prn_Flag;
317 END;
318 Function Prev_Addr_Id_p return number is
319 Begin
320 return Prev_Addr_Id;
321 END;
322 Function prev_customer_trx_id_p return varchar2 is
323 Begin
324 return prev_customer_trx_id;
325 END;
326 Function prev_currency_code_p return varchar2 is
327 Begin
328 return prev_currency_code;
329 END;
330 Function prev_customer_id_p return varchar2 is
331 Begin
332 return prev_customer_id;
333 END;
334 Function prev_terms_p return varchar2 is
335 Begin
336 return prev_terms;
337 END;
338 Function RP_COMPANY_NAME_p return varchar2 is
339 Begin
340 return RP_COMPANY_NAME;
341 END;
342 Function RP_REPORT_NAME_p return varchar2 is
343 Begin
344 return RP_REPORT_NAME;
345 END;
346 Function RP_DATA_FOUND_p return varchar2 is
347 Begin
348 return RP_DATA_FOUND;
349 END;
350 Function RP_SUB_TITLE_p return varchar2 is
351 Begin
352 return RP_SUB_TITLE;
353 END;
354 Function RP_SORT_ORDER_p return varchar2 is
355 Begin
356 return RP_SORT_ORDER;
357 END;
358 Function Actual_Invoice_Sum_p return number is
359 Begin
360 return Actual_Invoice_Sum;
361 END;
362 function D_invoice_amountFormula(customer varchar2) return VARCHAR2 is
363 begin
364 RP_DATA_FOUND := customer;
365 return null;
366 end;
367 END AR_ARXCPH_XMLP_PKG ;
368