1 PACKAGE BODY AR_ARXBPH_XMLP_PKG AS
2 /* $Header: ARXBPHB.pls 120.1 2008/01/07 14:50:45 abraghun noship $ */
3
4 function BeforeReport return boolean is
5
6
7 begin
8
9
10 /*SRW.USER_EXIT('FND SRWINIT');*/null;
11
12
13
14
15 begin
16
17
18 P_CONS_PROFILE_VALUE := AR_SETUP.value('AR_SHOW_BILLING_NUMBER',null);
19
20 /*srw.message ('101', 'Consolidated Billing Profile: ' || P_CONS_PROFILE_VALUE);*/null;
21
22
23 exception
24 when others then
25 /*srw.message ('101', 'Consolidated Billing Profile: Failed.');*/null;
26
27 end;
28
29 If ( P_CONS_PROFILE_VALUE = 'N' ) then
30 lp_query_show_bill := 'to_char(NULL)';
31 /* Commented by Raj lp_table_show_bill := null;
32 lp_where_show_bill := null;*/
33 lp_table_show_bill := ' ';
34 lp_where_show_bill := ' ';
35 Else lp_query_show_bill := 'ci.cons_billing_number';
36 lp_table_show_bill := 'ar_cons_inv ci, ';
37 lp_where_show_bill := 'and ps.cons_inv_id = ci.cons_inv_id(+)';
38 End if;
39
40
41 return (TRUE);
42
43 end;
44
45 function AfterReport return boolean is
46 begin
47
48 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
49 return (TRUE);
50 end;
51
52 function report_nameformula(Company_Name in varchar2) return varchar2 is
53 begin
54
55 DECLARE
56 l_report_name VARCHAR2(80);
57 BEGIN
58 RP_Company_Name := Company_Name;
59 SELECT substr(cp.user_concurrent_program_name,1,80)
60 INTO l_report_name
61 FROM FND_CONCURRENT_PROGRAMS_VL cp,
62 FND_CONCURRENT_REQUESTS cr
63 --Commented By Raj WHERE cr.request_id = P_CONC_REQUEST_ID
64 WHERE cr.request_id = FND_GLOBAL.conc_request_id
65 AND cp.application_id = cr.program_application_id
66 AND cp.concurrent_program_id = cr.concurrent_program_id;
67
68 RP_Report_Name := l_report_name;
69 RP_REPORT_NAME := substr(RP_REPORT_NAME,1,instr(RP_REPORT_NAME,' (XML)'));
70 RETURN(l_report_name);
71 EXCEPTION
72 WHEN NO_DATA_FOUND
73 THEN RP_REPORT_NAME := NULL;
74 RETURN(NULL);
75 END;
76 RETURN NULL; end;
77
78 function Set_StatusFormula return VARCHAR2 is
79 begin
80
81 /*srw.reference(p_in_account_status_low_1);*/null;
82
83 /*srw.reference(p_in_account_status_high_1);*/null;
84
85
86 IF ( upper(p_in_account_status_low_1) <> 'ALL') THEN
87 status_low := p_in_account_status_low_1;
88 END IF;
89
90 IF ( upper( p_in_account_status_high_1) <> 'ALL') THEN
91 status_high := p_in_account_status_high_1;
92 END IF;
93
94
95
96
97 RETURN NULL; end;
98
99 function AfterPForm return boolean is
100 begin
101
102 DECLARE
103 customer_name_high VARCHAR2(50);
104 customer_name_low VARCHAR2(50);
105 invoice_number_high VARCHAR2(30);
106 invoice_number_low VARCHAR2(30);
107 BEGIN
108
109 p_in_account_status_low_1:=p_in_account_status_low;
110 p_in_account_status_high_1:=p_in_account_status_high;
111 IF (p_in_customer_low IS NOT NULL AND p_in_customer_high IS NULL) THEN
112 P_WHERE_11 := ' And party.party_name >= :p_in_customer_low ';
113 END IF;
114
115 IF (p_in_customer_high IS NOT NULL AND p_in_customer_low IS NULL) THEN
116 P_WHERE_12 := ' And party.party_name <= :p_in_customer_high ';
117 END IF;
118
119 IF (p_in_customer_low IS NOT NULL AND p_in_customer_high IS NOT NULL) THEN
120 IF (p_in_customer_low = p_in_customer_high ) THEN
121 P_WHERE_11 := ' And party.party_name = :p_in_customer_low ';
122 ELSE
123 P_WHERE_11 := ' And party.party_name >= :p_in_customer_low ';
124 P_WHERE_12 := ' And party.party_name <= :p_in_customer_high ';
125
126 END IF;
127 END IF;
128
129
130 IF (p_in_customer_num_low IS NOT NULL AND p_in_customer_num_high IS NULL ) THEN
131 lp_customer_num_low := ' and cust_acct.account_number >= :p_in_customer_num_low ';
132 END IF;
133
134 IF (p_in_customer_num_high IS NOT NULL AND p_in_customer_num_low IS NULL ) THEN
135 lp_customer_num_high := ' and cust_acct.account_number <= :p_in_customer_num_high ';
136 END IF;
137
138 IF (p_in_customer_num_high IS NOT NULL AND p_in_customer_num_low IS NOT NULL ) THEN
139 IF (p_in_customer_num_high = p_in_customer_num_low) THEN
140 lp_customer_num_low := ' and cust_acct.account_number = :p_in_customer_num_low ';
141 ELSE
142 lp_customer_num_low := ' and cust_acct.account_number >= :p_in_customer_num_low ';
143 lp_customer_num_high := ' and cust_acct.account_number <= :p_in_customer_num_high ';
144
145 END IF;
146 END IF;
147
148 IF (p_in_invoice_number_low IS NOT NULL AND p_in_invoice_number_high IS NULL) THEN
149 lp_invoice_number_low := ' and ps.trx_number >= :p_in_invoice_number_low ';
150 END IF;
151
152 IF (p_in_invoice_number_high IS NOT NULL AND p_in_invoice_number_low IS NULL) THEN
153 lp_invoice_number_high := ' and ps.trx_number <= :p_in_invoice_number_high ';
154 END IF;
155
156 IF (p_in_invoice_number_low IS NOT NULL AND p_in_invoice_number_high IS NOT NULL) THEN
157 IF (p_in_invoice_number_low = p_in_invoice_number_high ) THEN
158 lp_invoice_number_low := ' and ps.trx_number = :p_in_invoice_number_low ';
159
160 ELSE
161 lp_invoice_number_low := ' and ps.trx_number >= :p_in_invoice_number_low ';
162 lp_invoice_number_high := ' and ps.trx_number <= :p_in_invoice_number_high ';
163
164 END IF;
165 END IF;
166
167
168 IF p_in_invoice_amount_low IS NOT NULL THEN
169 lp_invoice_amount_low := ' and ps.amount_due_original >= :p_in_invoice_amount_low ';
170 END IF;
171
172 IF p_in_invoice_amount_high IS NOT NULL THEN
173 lp_invoice_amount_high := ' and ps.amount_due_original <= :p_in_invoice_amount_high ';
174 END IF;
175
176
177 IF (p_in_trx_date_low IS NOT NULL AND p_in_trx_date_high IS NULL) THEN
178 lp_trx_date_low := ' and ps.trx_date >= :p_in_trx_date_low ';
179 END IF;
180
181 IF (p_in_trx_date_low IS NOT NULL AND p_in_trx_date_high IS NULL) THEN
182 lp_r_trx_date_low := ' and cr.receipt_date >= :p_in_trx_date_low ';
183 END IF;
184
185 IF (p_in_trx_date_high IS NOT NULL AND p_in_trx_date_low IS NULL) THEN
186 lp_trx_date_high := ' and ps.trx_date <= :p_in_trx_date_high ';
187 END IF;
188
189 IF (p_in_trx_date_high IS NOT NULL AND p_in_trx_date_low IS NULL) THEN
190 lp_r_trx_date_high := ' and cr.receipt_date <= :p_in_trx_date_high ' ;
191 END IF;
192
193 IF (p_in_trx_date_low IS NOT NULL AND p_in_trx_date_high IS NOT NULL) THEN
194
195 IF (p_in_trx_date_low = p_in_trx_date_high ) THEN
196 lp_trx_date_low := ' and ps.trx_date = :p_in_trx_date_low ';
197 ELSE
198 lp_trx_date_low := ' and ps.trx_date >= :p_in_trx_date_low ';
199 lp_trx_date_high := ' and ps.trx_date <= :p_in_trx_date_high ';
200 END IF;
201
202 END IF;
203
204 IF (p_in_trx_date_low IS NOT NULL AND p_in_trx_date_high IS NOT NULL) THEN
205
206 IF (p_in_trx_date_low = p_in_trx_date_high ) THEN
207 lp_r_trx_date_low := ' and cr.receipt_date = :p_in_trx_date_low ';
208 ELSE
209 lp_r_trx_date_low := ' and cr.receipt_date >= :p_in_trx_date_low ';
210 lp_r_trx_date_high := ' and cr.receipt_date <= :p_in_trx_date_high ' ;
211 END IF;
212
213 END IF;
214
215
216
217 IF p_in_account_status_low_1 IS NULL THEN
218 p_in_account_status_low_1 := 'All';
219 END IF;
220
221 IF p_in_account_status_high_1 IS NULL THEN
222 p_in_account_status_high_1 := 'All';
223 END IF;
224
225 IF p_in_balance_due_low IS NOT NULL THEN
226 lp_balance_due_low := ' and ps.amount_due_remaining >= :p_in_balance_due_low ';
227 END IF;
228
229 IF p_in_balance_due_high IS NOT NULL THEN
230 lp_balance_due_high := ' and ps.amount_due_remaining <= :p_in_balance_due_high ';
231 END IF;
232
233
234 IF( ( p_in_customer_num_low IS NOT NULL )
235 OR
236 ( p_in_customer_num_low IS NULL
237 AND p_in_customer_num_high IS NOT NULL)
238 OR
239 ( p_in_customer_low IS NOT NULL )
240 OR
241 ( p_in_customer_low IS NULL
242 AND p_in_customer_high IS NOT NULL)
243 OR
244 ( p_in_customer_low IS NULL
245 AND p_in_customer_high IS NULL
246 AND p_in_customer_num_low IS NULL
247 AND p_in_customer_num_high IS NULL
248 AND p_in_trx_date_low IS NULL
249 AND p_in_trx_date_high IS NULL) ) THEN
250 IF p_in_customer_num_low IS NULL AND p_in_customer_num_high IS NULL
251 AND p_in_customer_low IS NULL AND p_in_customer_high IS NULL
252 AND p_in_trx_date_low IS NULL AND p_in_trx_date_high IS NULL
253 AND p_in_invoice_number_low IS NULL AND p_in_invoice_number_high IS NULL THEN
254
255 P_WHERE_1 := ' and ps.customer_id = cust_acct.cust_account_id ' ||
256 ' and ct.bill_to_customer_id = cust_acct.cust_account_id ' ||
257 ' and ct.customer_trx_id <= nvl(:p_max_id, 999999999999999) ' ;
258 ELSE
259
260 P_WHERE_1 := ' and ps.customer_id = cust_acct.cust_account_id ' ||
261 ' and ct.bill_to_customer_id = cust_acct.cust_account_id ';
262
263 END IF;
264 P_WHERE_2 := ' and cust_acct.cust_account_id = cp_cust.cust_account_id ';
265 ELSE
266 IF p_in_customer_num_low IS NULL AND p_in_customer_num_high IS NULL
267 AND p_in_customer_low IS NULL AND p_in_customer_high IS NULL
268 AND p_in_trx_date_low IS NULL AND p_in_trx_date_high IS NULL
269 AND p_in_invoice_number_low IS NULL AND p_in_invoice_number_high IS NULL THEN
270
271 P_WHERE_1 := ' and ps.customer_id = cust_acct.cust_account_id+0 ' ||
272 ' and ct.bill_to_customer_id = cust_acct.cust_account_id ' ||
273 ' and ct.customer_trx_id <= nvl(:p_max_id, 999999999999999) ' ;
274 ELSE
275
276 P_WHERE_1 := ' and ps.customer_id = cust_acct.cust_account_id ' ||
277 ' and ct.bill_to_customer_id = cust_acct.cust_account_id ';
278
279 END IF;
280 P_WHERE_2 := ' and cust_acct.cust_account_id+0 = cp_cust.cust_account_id ';
281 END IF;
282 END;
283
284 return (TRUE);
285 end;
286
287 --Functions to refer Oracle report placeholders--
288
289 Function Credits_Dummy_p return varchar2 is
290 Begin
291 return Credits_Dummy;
292 END;
293 Function Adjusts_Dummy_p return varchar2 is
294 Begin
295 return Adjusts_Dummy;
296 END;
297 Function Payment_no_dummy_cr_p return varchar2 is
298 Begin
299 return Payment_no_dummy_cr;
300 END;
301 Function Payments_Dummy_p return varchar2 is
302 Begin
303 return Payments_Dummy;
304 END;
305 Function Adjusts_Dummy_Cr_p return varchar2 is
306 Begin
307 return Adjusts_Dummy_Cr;
308 END;
309 Function payment_no_dummy_adj_p return varchar2 is
310 Begin
311 return payment_no_dummy_adj;
312 END;
313 Function Payments_Dummy_adj_p return varchar2 is
314 Begin
315 return Payments_Dummy_adj;
316 END;
317 Function Credits_Dummy_Adj_p return varchar2 is
318 Begin
319 return Credits_Dummy_Adj;
320 END;
321 Function RP_COMPANY_NAME_p return varchar2 is
322 Begin
323 return RP_COMPANY_NAME;
324 END;
325 Function RP_REPORT_NAME_p return varchar2 is
326 Begin
327 return RP_REPORT_NAME;
328 END;
329 Function RP_DATA_FOUND_p return varchar2 is
330 Begin
331 return RP_DATA_FOUND;
332 END;
333 Function Status_Low_p return varchar2 is
334 Begin
335 return Status_Low;
336 END;
337 Function Status_high_p return varchar2 is
338 Begin
339 return Status_high;
340 END;
341
342 /*added as fix*/
343 function D_INVOICE_AMOUNTFormula(customer_name in varchar2) return VARCHAR2 is
344 begin
345
346 /*srw.reference(:Invoice_Amt);
347 srw.reference(:Currency_Code);
348 srw.user_exit('FND FORMAT_CURRENCY
349 CODE=":Currency_Code"
350 DISPLAY_WIDTH="13"
351 AMOUNT=":Invoice_amt"
352 DISPLAY=":D_Invoice_Amount"');
353 RETURN(:D_Invoice_Amount);*/
354
355 RP_DATA_FOUND := customer_name;
356 return (' ');
357 end;
358 END AR_ARXBPH_XMLP_PKG ;
359
[Home] [Help]