1 PACKAGE BODY AR_ARXCTA_XMLP_PKG AS
2 /* $Header: ARXCTAB.pls 120.0 2007/12/27 13:44:18 abraghun noship $ */
3
4 function BeforeReport return boolean is
5 begin
6 P_CONC_REQUEST_ID:=FND_GLOBAL.conc_request_id;
7 /*SRW.USER_EXIT('FND SRWINIT');*/null;
8
9
10 return (TRUE);
11 end;
12
13 function AfterReport return boolean is
14 begin
15
16 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
17 return (TRUE);
18 end;
19
20 function report_nameformula(Company_Name in varchar2) return varchar2 is
21 begin
22
23 DECLARE
24 l_report_name VARCHAR2(80);
25 l_gl_date_low VARCHAR2 (11);
26 l_gl_date_high VARCHAR2 (11);
27
28 BEGIN
29 RP_Company_Name := Company_Name;
30 if p_gl_date_low is NULL then
31 l_gl_date_low := ' ';
32 else
33 l_gl_date_low := TO_CHAR(p_gl_date_low, 'DD-MON-YYYY') ;
34 end if ;
35 if p_gl_date_high is NULL then
36 l_gl_date_high := ' ';
37 else
38 l_gl_date_high := TO_CHAR(p_gl_date_high, 'DD-MON-YYYY');
39 end if ;
40
41 rp_gl_date := ARP_STANDARD.FND_MESSAGE('AR_REPORTS_GL_DATE_FROM_TO',
42 'FROM_DATE', l_gl_date_low,
43 'TO_DATE',l_gl_date_high);
44
45 rp_sum := ARP_STANDARD.FND_MESSAGE('AR_REPORTS_SUM');
46 rp_sumfor := ARP_STANDARD.FND_MESSAGE('AR_REPORTS_SUM_FOR');
47 rp_total := ARP_STANDARD.FND_MESSAGE('AR_REPORTS_TOTAL');
48 rp_func := ARP_STANDARD.FND_MESSAGE('AR_REPORTS_FUNC');
49 rp_grand := ARP_STANDARD.FND_MESSAGE('AR_REPORTS_GRAND');
50
51 SELECT substr(cp.user_concurrent_program_name,1, 80)
52 INTO l_report_name
53 FROM FND_CONCURRENT_PROGRAMS_VL cp,
54 FND_CONCURRENT_REQUESTS cr
55 WHERE cr.request_id = P_CONC_REQUEST_ID
56 AND cp.application_id = cr.program_application_id
57 AND cp.concurrent_program_id = cr.concurrent_program_id;
58
59 RP_Report_Name := l_report_name;
60 RETURN(l_report_name);
61
62 EXCEPTION
63 WHEN NO_DATA_FOUND
64 THEN RP_REPORT_NAME := 'Adjustment Approval Report';
65 RETURN('REPORT TITLE');
66
67 END;
68
69 RETURN NULL; end;
70
71 function AfterPForm return boolean is
72 begin
73
74 BEGIN
75
76 if p_customer_name_low is NOT NULL then
77 lp_customer_name_low := 'and party.party_name >= :p_customer_name_low ' ;
78 end if ;
79
80 if p_customer_name_high is NOT NULL then
81 lp_customer_name_high := 'and party.party_name <= :p_customer_name_high ' ;
82 end if ;
83
84 if p_customer_number_low is NOT NULL then
85 lp_customer_number_low := 'and cust.account_number >= :p_customer_number_low ' ;
86 end if ;
87
88 if p_customer_number_high is NOT NULL then
89 lp_customer_number_high := 'and cust.account_number <= :p_customer_number_high ' ;
90 end if ;
91
92 if p_adjustment_name_low is NOT NULL then
93 lp_adjustment_name_low := 'and rcvbl_trx.name >= :p_adjustment_name_low ' ;
94 end if ;
95
96 if p_adjustment_name_high is NOT NULL then
97 lp_adjustment_name_high := 'and rcvbl_trx.name <= :p_adjustment_name_high ' ;
98 end if ;
99
100
101 if p_invoice_low is NOT NULL then
102 lp_invoice_low := ' and trx.trx_number >= :p_invoice_low ' ;
103 end if ;
104
105 if p_invoice_high is NOT NULL then
106 lp_invoice_high := ' and trx.trx_number <= :p_invoice_high ' ;
107 end if ;
108
109 if p_invoice_type_low is NOT NULL then
110 lp_invoice_type_low := ' and ra_cust_trx_types.name >= :p_invoice_type_low ';
111 end if ;
112
113 if p_invoice_type_high is NOT NULL then
114 lp_invoice_type_high := ' and ra_cust_trx_types.name <= :p_invoice_type_high ';
115 end if ;
116
117 if p_gl_date_low is NOT NULL then
118 lp_gl_date_low := ' and adj.gl_date >= :p_gl_date_low ' ;
119 end if ;
120
121 if p_gl_date_high is NOT NULL then
122 lp_gl_date_high := ' and adj.gl_date <= :p_gl_date_high ' ;
123 end if ;
124
125 if p_status_low is NOT NULL then
126 lp_status_low := ' and l2.meaning >= :p_status_low ';
127 end if ;
128
129 if p_status_high is NOT NULL then
130 lp_status_high := ' and l2.meaning <= :p_status_high ';
131 end if ;
132
133 if p_created_by_low is NOT NULL then
134 lp_created_by_low :=' and fndc.user_name >= :p_created_by_low' ;
135 end if ;
136
137 if p_created_by_high is NOT NULL then
138 lp_created_by_high :=' and fndc.user_name <= :p_created_by_high' ;
139 end if ;
140
141
142 if (initcap(p_order_by) like 'Cr%') then
143 lp_order_by := 'order by ps.invoice_currency_code,'||''||
144 'fndc.user_name, party.party_name, trx.trx_number,'||''||
145 'ps.due_date, adj.gl_date desc, rcvbl_trx.name, ra_cust_trx_types.name'
146 ;
147 elsif (initcap(p_order_by) like 'Cu%') then
148 lp_order_by := 'order by ps.invoice_currency_code, party.party_name,'||''||
149 'trx.trx_number, ps.due_date, adj.gl_date desc, '||''||
150 'rcvbl_trx.name, ra_cust_trx_types.name'
151 ;
152 elsif (p_order_by ='Adjustment Status') then
153 lp_order_by := 'order by ps.invoice_currency_code, l2.meaning,'||''||
154 ' party.party_name, trx.trx_number, ps.due_date,'||''||
155 'adj.gl_date desc, rcvbl_trx.name, ra_cust_trx_types.name'
156 ;
157 elsif (p_order_by ='Adjustment Name') then
158 lp_order_by := ' order by ps.invoice_currency_code, rcvbl_trx.name,'||''||
159 'party.party_name, trx.trx_number, ps.due_date, adj.gl_date desc,'||''||
160 'ra_cust_trx_types.name' ;
161
162 end if ;
163
164 END ;
165 return (TRUE);
166 end;
167
168 function c_status_summary_labelformula(Currency_Code in varchar2, Status_1 in varchar2) return varchar2 is
169 BEGIN
170 DECLARE
171 l_temp VARCHAR2 (2000);
172 BEGIN
173 l_temp := Currency_Code||' ' || rp_sumfor || ' '||Status_1 ;
174
175 if p_curr_code is null then
176 l_temp := l_temp || ' ' || rp_func;
177 end if;
178
179 return (l_temp);
180 END;
181 RETURN NULL; END;
182
183 function c_name_summary_labelformula(Currency_Code in varchar2, Name_1 in varchar2) return varchar2 is
184 begin
185
186 DECLARE
187 l_temp VARCHAR2 (2000);
188 BEGIN
189
190 l_temp := Currency_Code||' ' || rp_sumfor || ' '||Name_1 ;
191
192 if p_curr_code is null then
193 l_temp := l_temp || ' ' || rp_func;
194 end if;
195
196 return (l_temp);
197
198 END;
199 RETURN NULL; end;
200
201 function c_creator_labelformula(Currency_Code in varchar2, Created_by in varchar2) return varchar2 is
202 begin
203
204 DECLARE
205 l_temp VARCHAR2 (2000);
206 BEGIN
207 l_temp := Currency_Code|| ' ' || rp_sumfor || ' '||Created_by ;
208
209 if p_curr_code is null then
210 l_temp := l_temp || ' ' || rp_func;
211 end if;
212
213 return (l_temp);
214 END ;
215 RETURN NULL; end;
216
217 function c_currency_summary_labelformul(currency_Code in varchar2) return varchar2 is
218 begin
219
220 DECLARE
221
222 l_temp VARCHAR2 (2000);
223
224 BEGIN
225
226 l_temp := rp_total||' '||currency_Code;
227
228 if p_curr_code is null then
229 l_temp := l_temp || ' ' || rp_func;
230 end if;
231
232 return (l_temp);
233
234 END ;
235 RETURN NULL; end;
236
237 function C_ORDER_BYFormula return VARCHAR2 is
238 begin
239
240 DECLARE
241 a VARCHAR2(2000);
242 b VARCHAR2(80);
243 BEGIN
244 a := ARP_STANDARD.FND_MESSAGE('AR_REPORTS_ORDER_BY');
245 select meaning
246 into b
247 from ar_lookups
248 where lookup_type = 'SORT_BY_ARXCTA'
249 and upper(lookup_code) = upper(p_order_by);
250
251 p_meaning := b;
252
253 rp_order_by := rtrim(a) || ' : ' || rtrim(b);
254 END ;
255
256 RETURN NULL; end;
257
258 function c_data_not_foundformula(Currency_Code in varchar2) return varchar2 is
259 begin
260
261 rp_data_found := Currency_Code ;
262 return (0);
263 end;
264
265 function C_GRAND_TOTAL_LABELFormula return VARCHAR2 is
266 l_temp varchar2(5000);
267
268 begin
269 if p_curr_code is null then
270 l_temp := rp_grand || ' ' || rp_func;
271 else
272 l_temp := rp_grand || ' ' ||
273 p_curr_code || ' ' || rp_func;
274 end if;
275 return(l_temp);
276 end;
277
278 --Functions to refer Oracle report placeholders--
279
280 Function ACCT_BAL_APROMPT_p return varchar2 is
281 Begin
282 return ACCT_BAL_APROMPT;
283 END;
284 Function RP_COMPANY_NAME_p return varchar2 is
285 Begin
286 return RP_COMPANY_NAME;
287 END;
288 Function RP_REPORT_NAME_p return varchar2 is
289 Begin
290 return substr(RP_REPORT_NAME,1,instr(RP_REPORT_NAME,' (XML)'));
291 END;
292 Function RP_DATA_FOUND_p return varchar2 is
293 Begin
294 return RP_DATA_FOUND;
295 END;
296 Function RP_GL_DATE_p return varchar2 is
297 Begin
298 return RP_GL_DATE;
299 END;
300 Function RP_ORDER_BY_p return varchar2 is
301 Begin
302 return RP_ORDER_BY;
303 END;
304 Function RP_FUNC_CURRENCY_p return varchar2 is
305 Begin
306 return RP_FUNC_CURRENCY;
307 END;
308 Function P_MEANING_p return varchar2 is
309 Begin
310 return P_MEANING;
311 END;
312 Function RP_TOTAL_p return varchar2 is
313 Begin
314 return RP_TOTAL;
315 END;
316 Function RP_SUM_p return varchar2 is
317 Begin
318 return RP_SUM;
319 END;
320 Function RP_FUNC_p return varchar2 is
321 Begin
322 return RP_FUNC;
323 END;
324 Function RP_SUMFOR_p return varchar2 is
325 Begin
326 return RP_SUMFOR;
327 END;
328 Function RP_GRAND_p return varchar2 is
329 Begin
330 return RP_GRAND;
331 END;
332 END AR_ARXCTA_XMLP_PKG ;
333