1 PACKAGE BODY AR_ARXPDI_XMLP_PKG AS
2 /* $Header: ARXPDIB.pls 120.1 2008/01/11 10:47:41 abraghun noship $ */
3 function BeforeReport return boolean is
4 begin
5 P_CONC_REQUEST_ID:=FND_GLOBAL.conc_request_id;
6 /*SRW.USER_EXIT('FND SRWINIT');*/null;
7 get_boiler_plates ;
8 begin
9 P_CONS_PROFILE_VALUE := AR_SETUP.value('AR_SHOW_BILLING_NUMBER',null);
10 exception when others then null;
11 end;
12 If ( P_CONS_PROFILE_VALUE = 'N' ) then
13 lp_query_show_bill := 'to_char(NULL)';
14 --lp_table_show_bill := null;
15 --lp_where_show_bill := null;
16 lp_table_show_bill := ' ';
17 lp_where_show_bill := ' ';
18 Else lp_query_show_bill := 'ci.cons_billing_number';
19 lp_table_show_bill := 'ar_cons_inv ci,';
20 lp_where_show_bill := 'and ps.cons_inv_id = ci.cons_inv_id(+)';
21 End if;
22 p_as_of := rtrim(ARP_STANDARD.FND_MESSAGE('AR_REPORTS_AS_OF'));
23 p_as_of := p_as_of || ' ';
24 p_balance_due := ARPT_SQL_FUNC_UTIL.get_lookup_meaning('SORT_BY_ARXPDI','Balance Due');
25 p_customer := ARPT_SQL_FUNC_UTIL.get_lookup_meaning('SORTY_BY_ARXPDI','Customer');
26 p_salesperson := ARPT_SQL_FUNC_UTIL.get_lookup_meaning('SORT_BY_ARXPDI','Salesperson');
27 return (TRUE);
28 end;
29 function AfterReport return boolean is
30 begin
31 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
32 return (TRUE);
33 end;
34 function report_nameformula(Company_Name in varchar2) return varchar2 is
35 begin
36 DECLARE
37 l_report_name VARCHAR2(80);
38 l_amount_low VARCHAR2 (15);
39 l_amount_high VARCHAR2 (15);
40 l_past_days_due_low VARCHAR2 (20);
41 l_past_days_due_high VARCHAR2 (20);
42 BEGIN
43 if p_past_days_due_low is NULL then
44 l_past_days_due_low := ' ';
45 else
46 l_past_days_due_low := p_past_days_due_low ;
47 end if ;
48 if p_past_days_due_high is NULL then
49 l_past_days_due_high := ' ';
50 else
51 l_past_days_due_high := p_past_days_due_high ;
52 end if ;
53 rp_past_days_from := l_past_days_due_low;
54 rp_past_days_to := l_past_days_due_high;
55 p_days_past_due_from := ARP_STANDARD.FND_MESSAGE('AR_REPORTS_DAYS_PD_FROM_TO',
56 'FROM_DATE',rtrim(l_past_days_due_low),
57 'TO_DATE',rtrim(l_past_days_due_high));
58 RP_Company_Name := Company_Name ;
59 rp_as_of_date := TO_CHAR(p_as_of_date, 'DD-MON-YY');
60 if p_amount_low is NULL then
61 l_amount_low := ' ';
62 else
63 l_amount_low := p_amount_low ;
64 end if ;
65 if p_amount_high is NULL then
66 l_amount_high := ' ';
67 else
68 l_amount_high := p_amount_high ;
69 end if ;
70 rp_balance_from := l_amount_low;
71 rp_balance_to := l_amount_high;
72 p_balance_due_from := ARP_STANDARD.FND_MESSAGE('AR_REPORTS_BAL_FROM_TO',
73 'LOW_AMT',rtrim(l_amount_low),
74 'HIGH_AMT',rtrim(l_amount_high));
75 SELECT substr(cp.user_concurrent_program_name, 1, 80)
76 INTO l_report_name
77 FROM FND_CONCURRENT_PROGRAMS_VL cp,
78 FND_CONCURRENT_REQUESTS cr
79 WHERE cr.request_id = P_CONC_REQUEST_ID
80 AND cp.application_id = cr.program_application_id
81 AND cp.concurrent_program_id = cr.concurrent_program_id;
82 l_report_name:= substr(l_report_name,1,instr(l_report_name,' (XML)'));
83 RP_Report_Name := l_report_name;
84 RETURN(l_report_name);
85 EXCEPTION
86 WHEN NO_DATA_FOUND
87 THEN RP_REPORT_NAME := NULL;
88 RETURN(NULL);
89 END;
90 RETURN NULL; end;
91 function AfterPForm return boolean is
92 begin
93 BEGIN
94 if ( p_order_by = 'Balance Due') then
95 lp_order_by :=
96 ' ORDER BY 1 ASC,2 ASC,4 DESC,6 ASC,7 ASC,8 ASC , ps.invoice_currency_code , PARTY.PARTY_NAME ,
97 CUST.CUST_ACCOUNT_ID , ps.trx_number , ps.customer_trx_id , ps.due_date , ps.amount_due_original desc , ps.tax_original desc , ps.amount_due_remaining desc'
98 ;
99 elsif ( p_order_by = 'Customer') then
100 lp_order_by :=
101 'ORDER BY 1 ASC,2 ASC,4 DESC,6 ASC,7 ASC,8 ASC , ps.invoice_currency_code , PARTY.PARTY_NAME ,
102 CUST.CUST_ACCOUNT_ID , ps.trx_number , ps.customer_trx_id , ps.due_date , ps.amount_due_original desc , ps.tax_original desc , ps.amount_due_remaining desc';
103 elsif ( p_order_by = 'Salesperson') then
104 lp_order_by :=
105 'ORDER BY 1 ASC,2 ASC,4 DESC,6 ASC,7 ASC,8 ASC , ps.invoice_currency_code , srep.name , PARTY.PARTY_NAME ,
106 CUST.CUST_ACCOUNT_ID , ps.trx_number , ps.customer_trx_id , ps.due_date , ps.amount_due_original desc , ps.tax_original desc , ps.amount_due_remaining desc'
107 ;
108 end if ;
109 if p_past_days_due_low is NOT NULL then
110 lp_past_days_due_low := 'and ( :p_as_of_date - :p_past_days_due_low) >= ps.due_date + 0 ';
111 end if ;
112 if p_past_days_due_high is NOT NULL then
113 lp_past_days_due_high := 'and ( :p_as_of_date - :p_past_days_due_high ) <= ps.due_date + 0 ';
114 end if ;
115 if p_amount_low is NOT NULL then
116 lp_amount_low := 'and ps.due_amount >= :p_amount_low ';
117 end if ;
118 if p_amount_high is NOT NULL then
119 lp_amount_high := 'and ps.due_amount <= :p_amount_high ';
120 end if ;
121 if p_collector_low is NOT NULL and p_collector_high is NOT NULL then
122 if p_collector_low = p_collector_high then
123 lp_collector_low := ' and col.name = :p_collector_low ';
124 else
125 lp_collector_low := ' and col.name >= :p_collector_low ';
126 lp_collector_high := ' and col.name <= :p_collector_high ';
127 end if;
128 end if ;
129 if p_collector_low is NOT NULL and p_collector_high is NULL then
130 lp_collector_low := ' and col.name >= :p_collector_low ';
131 end if ;
132 if p_collector_high is NOT NULL and p_collector_low is NULL then
133 lp_collector_high := ' and col.name <= :p_collector_high ';
134 end if ;
135 if p_customer_name_low is NOT NULL and p_customer_name_high is NOT NULL then
136 if p_customer_name_low = p_customer_name_high then
137 lp_customer_name_low := ' and PARTY.PARTY_NAME = :p_customer_name_low ';
138 else
139 lp_customer_name_low := ' and PARTY.PARTY_NAME >= :p_customer_name_low ';
140 lp_customer_name_high := ' and PARTY.PARTY_NAME <= :p_customer_name_high ';
141 end if;
142 end if;
143 if p_customer_name_low is NOT NULL and p_customer_name_high is NULL then
144 lp_customer_name_low := ' and PARTY.PARTY_NAME >= :p_customer_name_low ';
145 end if ;
146 if p_customer_name_high is NOT NULL and p_customer_name_low is NULL then
147 lp_customer_name_high := ' and PARTY.PARTY_NAME <= :p_customer_name_high ';
148 end if ;
149 ph_customer_number_low := p_customer_number_low ;
150 ph_customer_number_high := p_customer_number_high ;
151 if p_customer_number_low is NOT NULL and p_customer_number_high is NOT NULL then
152 if p_customer_number_low = p_customer_number_high then
153 lp_customer_number_low := ' and CUST.ACCOUNT_NUMBER = :p_customer_number_low ' ;
154 else
155 lp_customer_number_low := ' and CUST.ACCOUNT_NUMBER >= :p_customer_number_low ' ;
156 lp_customer_number_high := 'and CUST.ACCOUNT_NUMBER <= :p_customer_number_high ' ;
157 end if;
158 end if;
159 if p_customer_number_low is NOT NULL and p_customer_number_high is NULL then
160 lp_customer_number_low := ' and CUST.ACCOUNT_NUMBER >= :p_customer_number_low ' ;
161 end if ;
162 if p_customer_number_high is NOT NULL and p_customer_number_low is NULL then
163 lp_customer_number_high := ' and CUST.ACCOUNT_NUMBER <= :p_customer_number_high ' ;
164 end if ;
165 if p_invoice_type_low is NOT NULL and p_invoice_type_high is NOT NULL then
166 if p_invoice_type_low = p_invoice_type_high then
167 lp_invoice_type_low := ' and arpt_sql_func_util.get_trx_type_details(ctx.cust_trx_type_id ,''NAME'') = :p_invoice_type_low ' ;
168 else
169 lp_invoice_type_low := ' and arpt_sql_func_util.get_trx_type_details(ctx.cust_trx_type_id ,''NAME'') >= :p_invoice_type_low ' ;
170 lp_invoice_type_high := ' and arpt_sql_func_util.get_trx_type_details(ctx.cust_trx_type_id ,''NAME'') <= :p_invoice_type_high ' ;
171 end if ;
172 end if ;
173 if p_invoice_type_low is NOT NULL and p_invoice_type_high is NULL then
174 lp_invoice_type_low := ' and arpt_sql_func_util.get_trx_type_details(ctx.cust_trx_type_id ,''NAME'') >= :p_invoice_type_low ' ;
175 end if ;
176 if p_invoice_type_high is NOT NULL and p_invoice_type_low is NULL then
177 lp_invoice_type_high := ' and arpt_sql_func_util.get_trx_type_details(ctx.cust_trx_type_id ,''NAME'') <= :p_invoice_type_high ' ;
178 end if ;
179 if p_salesrep_low is NOT NULL then
180 lp_salesrep_low := ' and ( (srep.name >= :p_salesrep_low) ) ' ;
181 end if ;
182 if p_salesrep_high is NOT NULL then
183 lp_salesrep_high := ' and ( (srep.name <= :p_salesrep_high) ) ' ;
184 end if ;
185 /*SRW.MESSAGE(100, 'p_as_of_date: '||p_as_of_date);*/null;
186 END ;
187 return (TRUE);
188 end;
189 function c_temp_salformula(Currency_Code in varchar2,Salesrep in varchar2) return varchar2 is
190 begin
191 DECLARE
192 l_temp_curr VARCHAR2 (270);
193 BEGIN
194 /*srw.reference (Currency_Code);*/null;
195 /*srw.reference (Salesrep);*/null;
196 if (p_order_by = 'Salesperson') then
197 rp_sale_curr := 'Salesrep: '||''||Salesrep ;
198 rp_curr := 'Currency: '||''||Currency_Code;
199 else
200 rp_sale_curr := 'Currency: '||''||Currency_Code;
201 end if ;
202 return (l_temp_curr);
203 end ;
204 RETURN NULL; end;
205 function c_data_foundformula(Currency_Code in varchar2) return varchar2 is
206 begin
207 RP_DATA_FOUND := Currency_Code ;
208 /*srw.message('100','rp_data_found = ' || RP_DATA_FOUND);*/null;
209 return (0);
210 end;
211 function c_custom_checkformula(Currency_Code in varchar2, Cust_ID in number) return varchar2 is
212 begin
213 begin
214 rp_cust_check := 0 ;
215 rp_curr_check := 0 ;
216 if (rp_old_curr <> Currency_Code) OR (rp_old_curr is NULL) then
217 rp_old_customer := ' ';
218 rp_curr_check := 1 ;
219 end if ;
220 rp_old_curr := Currency_Code ;
221 if (rp_old_customer = to_char(Cust_ID)) then
222 rp_cust_check := 0 ;
223 else
224 rp_cust_check := 1 ;
225 end if ;
226 rp_old_customer := Cust_ID;
227 end ;
228 RETURN NULL; end;
229 procedure get_boiler_plates is
230 w_industry_code varchar2(20);
231 w_industry_stat varchar2(20);
232 begin
233 if fnd_installation.get(0, 0,
234 w_industry_stat,
235 w_industry_code) then
236 if w_industry_code = 'C' then
237 c_salesrep_title := null ;
238 else
239 get_lookup_meaning('IND_SALES_REP',
240 w_industry_code,
241 c_salesrep_title);
242 end if;
243 end if;
244 c_industry_code := w_Industry_code ;
245 end ;
246 procedure get_lookup_meaning(p_lookup_type in varchar2,
247 p_lookup_code in varchar2,
248 p_lookup_meaning in out NOCOPY varchar2)
249 is
250 w_meaning varchar2(80);
251 begin
252 select meaning
253 into w_meaning
254 from fnd_lookups
255 where lookup_type = p_lookup_type
256 and lookup_code = p_lookup_code ;
257 p_lookup_meaning := w_meaning ;
258 exception
259 when no_data_found then
260 p_lookup_meaning := null ;
261 end ;
262 function set_display_for_core return boolean is
263 begin
264 if c_industry_code = 'C' then
265 return(TRUE);
266 else
267 if c_salesrep_title is not null then
268 return(FALSE);
269 else
270 return(TRUE);
271 end if;
272 end if;
273 RETURN NULL; end;
274 function set_display_for_gov return boolean is
275 begin
276 if c_industry_code = 'C' then
277 return(FALSE);
278 else
279 if c_salesrep_title is not null then
280 return(TRUE);
281 else
282 return(FALSE);
283 end if;
284 end if;
285 RETURN NULL; end ;
286 function invoice_number_consformula(invoice_number in varchar2, cons_bill_number in varchar2) return varchar2 is
287 begin
288 /*srw.reference(invoice_number);*/null;
289 /*srw.reference(cons_bill_number);*/null;
290 If ( P_CONS_PROFILE_VALUE = 'N' ) then
291 return(substr(invoice_number,1,40));
292 ELSIF ( P_CONS_PROFILE_VALUE = 'Y' ) AND
293 (cons_bill_number is NULL) then
294 return(substr(invoice_number,1,40));
295 ELSE
296 return(substr(substr(invoice_number,1,NVL(length(invoice_number), 0))||'/'||cons_bill_number,1,40));
297 END IF;
298 RETURN NULL; end;
299 function CF_ORDER_BYFormula return Char is
300 order_meaning AR_LOOKUPS.MEANING%TYPE;
301 begin
302 Order_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning('SORT_BY_ARXPDI',p_order_by);
303 RETURN (ORDER_MEANING);
304 EXCEPTION
305 WHEN NO_DATA_FOUND THEN
306 RETURN(P_ORDER_BY);
307 end;
308 function CF_salespersonFormula return Char is
309 order_meaning AR_LOOKUPS.MEANING%TYPE;
310 begin
311 Order_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning('SORT_BY_ARXPDI','Salesperson');
312 RETURN (ORDER_MEANING);
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 RETURN('Salesperson');
316 end;
317 --Functions to refer Oracle report placeholders--
318 Function ACCT_BAL_APROMPT_p return varchar2 is
319 Begin
320 return ACCT_BAL_APROMPT;
321 END;
322 Function RP_OLD_CURR_p return varchar2 is
323 Begin
324 return RP_OLD_CURR;
325 END;
326 Function RP_CURR_CHECK_p return number is
327 Begin
328 return RP_CURR_CHECK;
329 END;
330 Function RP_OLD_CUSTOMER_p return varchar2 is
331 Begin
332 return RP_OLD_CUSTOMER;
333 END;
334 Function RP_CUST_CHECK_p return number is
335 Begin
336 return RP_CUST_CHECK;
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_AS_OF_DATE_p return varchar2 is
351 Begin
352 return RP_AS_OF_DATE;
353 END;
354 Function RP_PAST_DAYS_p return varchar2 is
355 Begin
356 return RP_PAST_DAYS;
357 END;
358 Function RP_BALANCE_p return varchar2 is
359 Begin
360 return RP_BALANCE;
361 END;
362 Function RP_SALE_CURR_p return varchar2 is
363 Begin
364 return RP_SALE_CURR;
365 END;
366 Function RP_CURR_p return varchar2 is
367 Begin
368 return RP_CURR;
369 END;
370 Function c_industry_code_p return varchar2 is
371 Begin
372 return c_industry_code;
373 END;
374 Function c_salesrep_title_p return varchar2 is
375 Begin
376 return c_salesrep_title;
377 END;
378 Function rp_balance_from_p return varchar2 is
379 Begin
380 return rp_balance_from;
381 END;
382 Function rp_balance_to_p return varchar2 is
383 Begin
384 return rp_balance_to;
385 END;
386 Function rp_past_days_from_p return varchar2 is
387 Begin
388 return rp_past_days_from;
389 END;
390 Function rp_past_days_to_p return varchar2 is
391 Begin
392 return rp_past_days_to;
393 END;
394 END AR_ARXPDI_XMLP_PKG ;
395
396
[Home] [Help]