1 PACKAGE BODY AR_ARXCBH_XMLP_PKG AS
2 /* $Header: ARXCBHB.pls 120.0 2007/12/27 13:36:52 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 P_IN_TRX_DATE_LOW_T:= to_char(P_IN_TRX_DATE_LOW,'DD-MON-YY');
9 P_IN_TRX_DATE_HIGH_T:= to_char(P_IN_TRX_DATE_HIGH,'DD-MON-YY');
10
11 return (TRUE);
12 end;
13
14 function AfterReport return boolean is
15 begin
16
17 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
18 return (TRUE);
19 end;
20
21 function report_nameformula(Company_Name in varchar2) return varchar2 is
22 begin
23
24 DECLARE
25 l_report_name VARCHAR2(80);
26 BEGIN
27 RP_Company_Name := Company_Name;
28 SELECT substr(cp.user_concurrent_program_name,1,80)
29 INTO l_report_name
30 FROM FND_CONCURRENT_PROGRAMS_VL cp,
31 FND_CONCURRENT_REQUESTS cr
32 WHERE cr.request_id = P_CONC_REQUEST_ID
33 AND cp.application_id = cr.program_application_id
34 AND cp.concurrent_program_id = cr.concurrent_program_id;
35
36 RP_Report_Name := l_report_name;
37 RETURN(l_report_name);
38 EXCEPTION
39 WHEN NO_DATA_FOUND
40 THEN RP_REPORT_NAME := NULL;
41 RETURN(NULL);
42 END;
43 RETURN NULL; end;
44
45 function AfterPForm return boolean is
46 begin
47
48 declare
49 terms_name_low VARCHAR2(20);
50 terms_name_high VARCHAR2(20);
51 begin
52
53
54 IF p_in_customer_num_high IS NOT NULL and p_in_customer_num_low IS NULL THEN
55 lp_in_customer_num_high := ' and cust.account_number <= :p_in_customer_num_high ' ;
56 END IF;
57
58 IF p_in_customer_num_low IS NOT NULL and p_in_customer_num_high IS NULL THEN
59 lp_in_customer_num_low := ' and cust.account_number >= :p_in_customer_num_low ';
60 END IF;
61
62 IF p_in_customer_num_high IS NOT NULL and p_in_customer_num_low IS NOT NULL THEN
63
64 IF p_in_customer_num_high = p_in_customer_num_low THEN
65 lp_in_customer_num_high := ' and cust.account_number = :p_in_customer_num_high ' ;
66 ELSE
67 lp_in_customer_num_high := ' and cust.account_number <= :p_in_customer_num_high ' ;
68 lp_in_customer_num_low := ' and cust.account_number >= :p_in_customer_num_low ';
69 END IF;
70 END IF;
71
72 IF p_in_invoice_number_low IS NOT NULL THEN
73 lp_in_invoice_number_low := ' and ps.trx_number >= :p_in_invoice_number_low ' ;
74 END IF;
75
76 IF p_in_invoice_number_high IS NOT NULL THEN
77 lp_in_invoice_number_high := ' and ps.trx_number <= :p_in_invoice_number_high ' ;
78 END IF;
79
80 IF p_in_trx_date_low IS NOT NULL THEN
81 lp_in_trx_date_low := ' and ps.trx_date >= :p_in_trx_date_low ';
82 END IF;
83
84
85 IF p_in_trx_date_high IS NOT NULL THEN
86 lp_in_trx_date_high := ' and ps.trx_date <= :p_in_trx_date_high ' ;
87 END IF;
88
89
90 IF (p_in_collector_low IS NOT NULL) or
91 (P_in_collector_high IS NOT NULL ) THEN
92
93 P_FROM_1 := ' hz_customer_profiles cp_cust, ' ||
94 'hz_customer_profiles cp_site, ' ||
95 'ar_collectors col, ';
96
97 P_WHERE_2 := ' And cust.cust_account_id = cp_cust.cust_account_id ' ||
98 ' and NVL(cp_site.collector_id,cp_cust.collector_id) = col.collector_id ' ||
99 ' and cp_cust.site_use_id is null ' ||
100 ' and su.site_use_id = cp_site.site_use_id(+) ' ;
101 END IF;
102
103 IF p_in_collector_low IS NOT NULL THEN
104 lp_in_collector_low := ' and col.name >= :p_in_collector_low ';
105 END IF;
106
107 IF p_in_collector_high IS NOT NULL THEN
108 lp_in_collector_high := ' and col.name <= :p_in_collector_high ';
109 END IF;
110
111 SELECT MIN(name),
112 MAX(name)
113 INTO terms_name_low,
114 terms_name_high
115 FROM ra_terms;
116
117 p_terms_name_low := terms_name_low;
118 p_terms_name_high := terms_name_high;
119
120
121 IF (p_in_customer_low IS NOT NULL) THEN
122 P_WHERE_11 := ' and party.party_name >= :p_in_customer_low ';
123 END IF;
124
125 IF (p_in_customer_high IS NOT NULL) THEN
126 P_WHERE_12 := ' and party.party_name <= :p_in_customer_high ';
127 END IF;
128
129 IF (p_in_terms_high IS NOT NULL) THEN
130
131 p_terms_name := ' and ter.name <= :p_in_terms_high ' ;
132 END IF;
133
134 IF (p_in_terms_low IS NOT NULL) THEN
135
136 p_terms_name1 := ' and ter.name >= :p_in_terms_low ';
137 END IF;
138
139 IF ((p_in_terms_high IS NULL) AND (p_in_terms_low IS NULL)) THEN
140 p_terms_name := ' and nvl(ter.name, :p_terms_name_low) between
141 :p_terms_name_low and :p_terms_name_high ' ;
142 END IF;
143
144
145 end;
146 return (TRUE);
147 end;
148
149 function set_prn_flagformula(address_id in number) return number is
150 begin
151
152 begin
153 /*srw.reference(address_id);*/null;
154
155 /*srw.reference(previous_addr_id);*/null;
156
157
158 if previous_addr_id <> address_id then
159 addr_prn_flag := 'Y' ;
160 else
161 addr_prn_flag := 'N' ;
162 end if;
163 previous_addr_id := address_id ;
164
165 return(1);
166 end;
167
168 RETURN NULL; end;
169
170 function cf_currency_flagformula(p_payment_schedule_id in number) return char is
171 l_num number;
172
173 cursor C_CROSS_CUR is
174 select decode(app.amount_applied_from,NULL,NULL,'*') flag
175 from ar_receivable_applications app, ar_payment_schedules_all pay
176 where app.reversal_gl_date IS NULL
177 and app.applied_customer_trx_id (+) = pay.customer_trx_id
178 and pay.payment_schedule_id = p_payment_schedule_id;
179
180 l_char varchar2(10);
181
182 begin
183
184 l_num :=0;
185 l_char := NUll;
186
187 for c_rec in C_CROSS_CUR loop
188
189 IF c_rec.flag = '*' then
190 l_num := 1;
191 exit;
192 End If;
193
194 END LOOP;
195
196
197 IF l_num =1 then
198 return('*');
199 else return(NULL);
200 end if;
201
202 end;
203
204 --Functions to refer Oracle report placeholders--
205
206 Function Addr_Prn_Flag_p return varchar2 is
207 Begin
208 return Addr_Prn_Flag;
209 END;
210 Function Previous_Addr_Id_p return number is
211 Begin
212 return Previous_Addr_Id;
213 END;
214 Function RP_COMPANY_NAME_p return varchar2 is
215 Begin
216 return RP_COMPANY_NAME;
217 END;
218 Function RP_REPORT_NAME_p return varchar2 is
219 Begin
220 return RP_REPORT_NAME;
221 END;
222 Function RP_DATA_FOUND_p return varchar2 is
223 Begin
224 return RP_DATA_FOUND;
225 END;
226 function D_INVOICE_AMOUNTFormula(customer_name in varchar2) return VARCHAR2 is
227 begin
228 RP_DATA_FOUND := customer_name;
229 return null;
230 end;
231 END AR_ARXCBH_XMLP_PKG ;
232
[Home] [Help]