[Home] [Help]
PACKAGE BODY: APPS.AR_ARXCHR_XMLP_PKG
Source
1 PACKAGE BODY AR_ARXCHR_XMLP_PKG AS
2 /* $Header: ARXCHRB.pls 120.0 2007/12/27 13:39:55 abraghun noship $ */
3
4 function BeforeReport return boolean is
5
6 l_ld_sp varchar2(1);
7 begin
8 P_CONC_REQUEST_ID:=FND_GLOBAL.conc_request_id;
9 /*SRW.USER_EXIT('FND SRWINIT');*/null;
10
11
12 rp_message:=null;
13 IF to_number(p_reporting_level) = 1000 THEN
14 l_ld_sp:= mo_utils.check_ledger_in_sp(TO_NUMBER(p_reporting_entity_id));
15
16 IF l_ld_sp = 'N' THEN
17 FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
18 RP_MESSAGE := FND_MESSAGE.get;
19
20 END IF;
21 END IF;
22
23
24 return (TRUE);
25 end;
26
27 function AfterReport return boolean is
28 begin
29
30 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
31 return (TRUE);
32 end;
33
34 function report_nameformula(Company_Name in varchar2) return varchar2 is
35 begin
36
37 DECLARE
38 l_report_name VARCHAR2(80);
39
40
41 BEGIN
42
43 RP_Company_Name := Company_Name;
44 SELECT substr(cp.user_concurrent_program_name,1,80)
45 INTO l_report_name
46 FROM FND_CONCURRENT_PROGRAMS_VL cp,
47 FND_CONCURRENT_REQUESTS cr
48 WHERE cr.request_id = P_CONC_REQUEST_ID
49 AND cp.application_id = cr.program_application_id
50 AND cp.concurrent_program_id = cr.concurrent_program_id;
51
52 RP_Report_Name := l_report_name;
53 RETURN(l_report_name);
54 EXCEPTION
55 WHEN NO_DATA_FOUND
56 THEN RP_REPORT_NAME := NULL;
57 RETURN(NULL);
58 END;
59 RETURN NULL; end;
60
61 function AfterPForm return boolean is
62 begin
63 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
64
65 p_org_where_ps := XLA_MO_REPORTING_API.Get_Predicate('ps', null);
66 /*srw.message(100,'done with get_predicate ps');*/null;
67
68
69 p_org_where_site:= XLA_MO_REPORTING_API.Get_Predicate('site', null);
70 /*srw.message(101,'done with get_predicate aite');*/null;
71
72
73 p_org_where_addr := XLA_MO_REPORTING_API.Get_Predicate('acct_site', null);
74 /*srw.message(101,'done with get_predicate addr');*/null;
75
76
77 if p_reporting_entity_id is NOT NULL then
78 p_reporting_entity_name := XLA_MO_REPORTING_API.get_reporting_entity_name ;
79 end if;
80 /*srw.message(101,'done with get_reporting_entity_name');*/null;
81
82
83 p_reporting_level_name := XLA_MO_REPORTING_API.get_reporting_level_name;
84 /*srw.message(101,'done with get_reporting_level_name');*/null;
85
86
87 /*srw.message( 102,' p_org_where_ps '|| p_org_where_ps);*/null;
88
89 /*srw.message( 102,' p_org_where_site '|| p_org_where_site);*/null;
90
91 /*srw.message( 102,' p_org_where_addr '|| p_org_where_addr);*/null;
92
93
94
95 BEGIN
96
97 if p_status_low is NOT NULL then
98 lp_status_low := 'and status.meaning >= :p_status_low';
99 end if ;
100
101 if p_status_high is NOT NULL then
102 lp_status_high := 'and status.meaning <= :p_status_high';
103 end if ;
104
105 if p_customer_name_low is NOT NULL then
106 lp_customer_name_low := 'and party.party_name >= :p_customer_name_low' ;
107 end if ;
108 if p_customer_name_high is NOT NULL then
109 lp_customer_name_high := 'and party.party_name <= :p_customer_name_high' ;
110 end if ;
111
112 if p_customer_number_low is NOT NULL then
113 lp_customer_number_low := 'and cust.account_number >= :p_customer_number_low' ;
114 end if ;
115 if p_customer_number_high is NOT NULL then
116 lp_customer_number_high := 'and cust.account_number <= :p_customer_number_high' ;
117 end if ;
118
119 if p_collector_low is NOT NULL then
120 lp_collector_low := 'and collect.name >= :p_collector_low' ;
121 end if ;
122 if p_collector_high is NOT NULL then
123 lp_collector_high := 'and collect.name <= :p_collector_high' ;
124 end if ;
125
126 if p_currency_code is NOT NULL then
127 lp_currency_code := 'and ps.invoice_currency_code = :p_currency_code' ;
128 end if ;
129
130 END ;
131 return (TRUE);
132 end;
133
134 function c_get_phone_numberformula(address_id in number, customer_id in number) return number is
135 begin
136
137 DECLARE
138
139 l_phone_number VARCHAR2 (100);
140
141 cursor Getphone IS
142 select
143 decode(cont_point.phone_area_code,
144 null, null,
145 '(' || cont_point.phone_area_code || ')' ) ||
146 decode(cont_point.contact_point_type,'TLX',
147 cont_point.telex_number, cont_point.phone_number)
148 from
149 hz_contact_points cont_point,
150 hz_cust_account_roles car
151 where
152 car.party_id = cont_point.owner_table_id
153 and cont_point.owner_table_name = 'HZ_PARTIES'
154 and cont_point.contact_point_type not in ('EDI','EMAIL','WEB')
155 and car.cust_acct_site_id = address_id
156 and ( car.cust_account_role_id = c_contact_id
157 OR not exists ( select 'x'
158 from hz_cust_account_roles p2
159 where p2.cust_acct_site_id = car.cust_acct_site_id
160 and p2.cust_account_role_id = car.cust_account_role_id
161 )
162 )
163 and rownum = 1
164 Union
165 select
166 decode(cont_point.phone_area_code,
167 null, null,
168 '(' || cont_point.phone_area_code || ')' ) ||
169 decode(cont_point.contact_point_type,'TLX',
170 cont_point.telex_number, cont_point.phone_number)
171 from
172 hz_contact_points cont_point,
173 hz_cust_account_roles car
174 where
175 car.party_id = cont_point.owner_table_id
176 and cont_point.owner_table_name = 'HZ_PARTIES'
177 and cont_point.contact_point_type not in ('EDI','EMAIL','WEB')
178 and car.cust_account_id = customer_id
179 and not exists (
180 select 'x'
181 from hz_cust_account_roles p2
182 where p2.cust_acct_site_id = car.cust_acct_site_id)
183 and rownum = 1
184 ;
185
186 BEGIN
187
188 /*SRW.REFERENCE (address_id );*/null;
189
190 /*SRW.REFERENCE (c_contact_id );*/null;
191
192 /*SRW.REFERENCE (customer_id );*/null;
193
194
195 c_phone_number := '' ;
196
197 OPEN GetPhone;
198 Fetch Getphone INTO l_phone_number;
199
200 c_phone_number := l_phone_number ;
201 return (0);
202
203 EXCEPTION WHEN NO_DATA_FOUND THEN
204 c_phone_number := '' ;
205 return (0);
206 END ;
207
208 RETURN NULL; end;
209
210 function C_PRIMARY_CONTACTFormula(p_address_id varchar2) return Number is
211 begin
212
213 DECLARE
214 l_contact_id NUMBER (12);
215 l_contact_name VARCHAR2 (60);
216
217 BEGIN
218
219 /*SRW.REFERENCE (address_id );*/null;
220
221
222 c_contact_name := '' ;
223 c_contact_id := 0 ;
224
225 select
226 substr(party.person_first_name,1,1) || ' ' ||
227 substrb(party.person_last_name,1,50),
228 acct_role.cust_account_role_id
229 into
230 l_contact_name,
231 l_contact_id
232 from
233 hz_cust_account_roles acct_role,
234 hz_parties party,
235 hz_relationships rel,
236 hz_role_responsibility role_res
237 where
238 acct_role.cust_account_role_id = role_res.cust_account_role_id
239 and acct_role.party_id = rel.party_id
240 and acct_role.role_type = 'CONTACT'
241 and rel.subject_id = party.party_id
242 and rel.subject_table_name = 'HZ_PARTIES'
243 and rel.object_table_name = 'HZ_PARTIES'
244 and rel.directional_flag = 'F'
245 and role_res.responsibility_type = 'BILL_TO'
246 and acct_role.cust_acct_site_id = p_address_id
247 and rownum = 1
248 ;
249
250 c_contact_id := l_contact_id ;
251 c_contact_name := l_contact_name ;
252
253 return (0);
254
255 EXCEPTION WHEN NO_DATA_FOUND THEN
256
257 c_contact_id := 0;
258 c_contact_name := '' ;
259 return (0);
260 END ;
261
262 RETURN NULL; end;
263
264 function c_no_data_foundformula(Currency_Main in varchar2) return number is
265 begin
266
267 rp_data_found := nvl(Currency_Main,'***') ;
268 return (0);
269
270 end;
271
272 --Functions to refer Oracle report placeholders--
273
274 Function c_phone_number_p return varchar2 is
275 Begin
276 return c_phone_number;
277 END;
278 Function c_contact_id_p return number is
279 Begin
280 return c_contact_id;
281 END;
282 Function c_contact_name_p return varchar2 is
283 Begin
284 return c_contact_name;
285 END;
286 Function RP_COMPANY_NAME_p return varchar2 is
287 Begin
288 return RP_COMPANY_NAME;
289 END;
290 Function RP_REPORT_NAME_p return varchar2 is
291 Begin
292 return substr(RP_REPORT_NAME,1,instr(RP_REPORT_NAME,' (XML)'));
293 END;
294 Function RP_DATA_FOUND_p return varchar2 is
295 Begin
296 return RP_DATA_FOUND;
297 END;
298 Function RP_DATE_RANGE_p return varchar2 is
299 Begin
300 return RP_DATE_RANGE;
301 END;
302 Function RP_message_p return varchar2 is
303 Begin
304 return RP_message;
305 END;
306 END AR_ARXCHR_XMLP_PKG ;
307