eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CS_SYSTEMS_COMMON_PUB

Source


1 package body CS_SYSTEMS_COMMON_PUB as
2  /* $Header: cscommnb.pls 115.7 2001/06/26 18:07:36 pkm ship      $ */
3     function get_party_id (p_user_id          IN NUMBER)  return NUMBER
4     is
5        l_cust_id  NUMBER;
6     begin
7        SELECT customer_id INTO l_cust_id FROM FND_USER WHERE user_id =
8        p_user_id;
9        return l_cust_id;
10     end get_party_id;
11 
12     function get_system_id (p_system_name     IN VARCHAR2)  return NUMBER
13     is
14        l_system_id  NUMBER(15);
15     begin
16        SELECT system_id INTO l_system_id FROM CS_SYSTEMS_ALL_VL WHERE name=p_system_name;
17        return l_system_id;
18     end get_system_id;
19 
20    procedure is_system_enabled(
21                      p_api_version_number     IN   NUMBER,
22                      p_init_msg_list          IN   VARCHAR2,
23                      p_commit                 IN   VARCHAR,
24                      x_return_status          OUT  VARCHAR2,
25                      x_msg_count              OUT  NUMBER,
26                      x_msg_data               OUT  VARCHAR2
27                     )
28    is
29 	 l_user_id        NUMBER;
33    begin
30 	 l_resp_id        NUMBER;
31 	 l_app_id         NUMBER;
32 	 l_enable_flag    VARCHAR2(1);
34 	 l_user_id := FND_GLOBAL.USER_ID;
35 	 l_resp_id := FND_GLOBAL.RESP_ID;
36 	 l_app_id  := FND_GLOBAL.RESP_APPL_ID;
37 	 x_return_status := FND_PROFILE.VALUE_SPECIFIC ('CS_CSI_ENABLED', l_user_id, l_resp_id, l_app_id);
38 
39        exception
40          when others then
41             x_return_status := FND_API.G_RET_STS_ERROR;
42    end is_system_enabled;
43 
44    procedure is_system_valid(
45                      p_api_version_number     IN   NUMBER,
46                      p_init_msg_list          IN   VARCHAR2,
47                      p_system_id              IN   NUMBER,
48                      p_system_name            IN   VARCHAR2,
49                      p_commit                 IN   VARCHAR,
50                      x_return_status          OUT  VARCHAR2,
51                      x_msg_count              OUT  NUMBER,
52                      x_msg_data               OUT  VARCHAR2
53                     )
54    is
55       l_system_id   NUMBER(15);
56       l_result      NUMBER(15);
57 	 l_curr_date   DATE;
58    begin
59       l_system_id := -1;
60 	 l_curr_date := sysdate;
61 
62       if (p_system_name is not null) then
63          l_system_id := get_system_id (p_system_name);
64 	    -- dbms_output.put_line ('system_id=' || l_system_id);
65          SELECT system_id INTO l_result FROM cs_systems_all_vl WHERE system_id=l_system_id AND (end_date_active IS null OR (end_date_active > l_curr_date));
66 	    -- dbms_output.put_line ('result=' || l_result);
67 
68       elsif (p_system_id > 0 ) then
69          SELECT system_id INTO l_result FROM cs_systems_all_vl WHERE system_id=p_system_id AND (end_date_active IS null OR (end_date_active > l_curr_date));
70 
71       else
72          l_result := -1;
73      end if;
74 
75 	if (l_result < 0) then
76         x_return_status := FND_API.G_RET_STS_ERROR;
77 	else
78         x_return_status := FND_API.G_RET_STS_SUCCESS;
79      end if;
80 
81      exception
82         WHEN NO_DATA_FOUND THEN
83            x_return_status := FND_API.G_RET_STS_ERROR;
84 		 -- dbms_output.put_line ('No data found');
85 
86         WHEN OTHERS THEN
87            x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
88 		 -- dbms_output.put_line ('Unexpected error');
89            FND_MSG_PUB.Count_And_Get
90                (p_count => x_msg_count ,
91                 p_data => x_msg_data
92                );
93    end is_system_valid;
94 
95    procedure get_current_system (
96                      p_api_version_number     IN   NUMBER,
97                      p_init_msg_list          IN   VARCHAR2,
98                      p_user_id                IN   NUMBER,
99                      p_commit                 IN   VARCHAR,
100                      x_system_id              OUT  NUMBER,
101                      x_system_name            OUT  VARCHAR2,
102                      x_return_status          OUT  VARCHAR2,
103                      x_msg_count              OUT  NUMBER,
104                      x_msg_data               OUT  VARCHAR2
105                     )
106    as
107      l_profile_name    VARCHAR2(60);
108      l_perz_data_name  VARCHAR2(360);
109      l_perz_data_type  VARCHAR2(30);
110      l_application_id  NUMBER;
111      l_pd_attrib_tbl   JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE;
112      l_system_id       NUMBER(15);
113      l_system_name     VARCHAR2(50);
114      l_party_id        NUMBER(15);
115      out_perz_data_id   NUMBER(15);
116      out_perz_data_name VARCHAR2(360);
117      out_perz_data_type VARCHAR2(30);
118      out_perz_data_desc VARCHAR2(240);
119    begin
120      l_system_id         := -1;
121      l_system_name       := null;
122      l_profile_name      := 'IBU_PERZ_' || to_char(p_user_id);
123      l_perz_data_name    := 'IBU_PREFERENCES';
124      l_perz_data_type    := 'IBU_PREFERENCES';
125      l_application_id    := 672;
126 
127      JTF_PERZ_DATA_PUB.Get_Perz_Data
128      (
129           p_api_version_number     =>   1.0,
130           p_init_msg_list          =>   p_init_msg_list,
131           p_application_id         =>   l_application_id,
132           p_profile_id             =>   null,
133           p_profile_name           =>   l_profile_name,
134           p_perz_data_id      =>   null,
135           p_perz_data_name    =>   l_perz_data_name,
136           p_perz_data_type    =>   null,
137           x_perz_data_id      =>   out_perz_data_id,
138           x_perz_data_name    =>   out_perz_data_name,
139           x_perz_data_type    =>   out_perz_data_type,
140           x_perz_data_desc    =>   out_perz_data_desc,
141           x_data_attrib_tbl   =>   l_pd_attrib_tbl,
142           x_return_status     =>   x_return_status,
143           x_msg_count         =>   x_msg_count,
144           x_msg_data          =>   x_msg_data
145       );
146 
147      if (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
148         for l_curr_row in 1..l_pd_attrib_tbl.count
149           loop
150             if l_pd_attrib_tbl(l_curr_row).ATTRIBUTE_NAME = 'system_name' then
151               l_system_name := l_pd_attrib_tbl(l_curr_row).ATTRIBUTE_VALUE;
152 		  elsif l_pd_attrib_tbl(l_curr_row).ATTRIBUTE_NAME = 'system_id' then
153               l_system_id := l_pd_attrib_tbl(l_curr_row).ATTRIBUTE_VALUE;
154             end if;
155           end loop;
156 	end if;
157 
158      x_system_id := l_system_id;
159 	x_system_name := l_system_name;
160 
161      exception
162 	   WHEN NO_DATA_FOUND THEN
163 		 x_system_id := -1;
164 		 x_system_name := null;
165 		 x_return_status := FND_API.G_RET_STS_ERROR;
166 
167         WHEN OTHERS THEN
168            x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
169            FND_MSG_PUB.Count_And_Get
170                (p_count => x_msg_count ,
171                 p_data => x_msg_data
172                );
173    end get_current_system;
174 
175    procedure get_all_systems_for_user(
176                      p_api_version_number     IN   NUMBER,
177                      p_init_msg_list          IN   VARCHAR2,
178                      p_user_id                IN   NUMBER,
179                      p_commit                 IN   VARCHAR,
180                      x_system_data            OUT  Sys_Info_Cursor,
181                      x_return_status          OUT  VARCHAR2,
182                      x_msg_count              OUT  NUMBER,
183                      x_msg_data               OUT  VARCHAR2
184                     )
185   as
186     l_party_id   NUMBER(15);
187     l_curr_date  DATE;
188    begin
189       l_party_id := get_party_id (p_user_id);
190 	 l_curr_date := sysdate;
191       open x_system_data for SELECT DISTINCT system_id, name FROM cs_system_party_links_v WHERE party_id = l_party_id AND (end_date_active IS null OR (end_date_active > l_curr_date)) ORDER BY system_id;
192 	 x_return_status := FND_API.G_RET_STS_SUCCESS;
193     exception
194         WHEN NO_DATA_FOUND THEN
195            x_return_status := FND_API.G_RET_STS_ERROR;
196 
197         WHEN OTHERS THEN
198            x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
199            FND_MSG_PUB.Count_And_Get
200                (p_count => x_msg_count ,
201                 p_data => x_msg_data
202                );
203    end get_all_systems_for_user;
204 
205    procedure get_all_child_systems(
206                      p_api_version_number     IN   NUMBER,
207                      p_init_msg_list          IN   VARCHAR2,
208                      p_system_id              IN   NUMBER,
209                      p_system_name            IN   VARCHAR2,
210                      p_commit                 IN   VARCHAR,
211                      x_system_data            OUT  Sys_Info_Cursor,
212                      x_return_status          OUT  VARCHAR2,
213                      x_msg_count              OUT  NUMBER,
214                      x_msg_data               OUT  VARCHAR2
215                     )
216    as
217       l_system_id   NUMBER(15);
218    begin
219 	 if (p_system_name is not null) then
220 	    l_system_id  := get_system_id (p_system_name);
221          open x_system_data for SELECT distinct system_id, name FROM cs_systems_all_vl WHERE parent_system_id = l_system_id ORDER BY system_id;
222          x_return_status := FND_API.G_RET_STS_SUCCESS;
223 
224      elsif (p_system_id > 0) then
225         open x_system_data for SELECT distinct system_id, name FROM cs_systems_all_vl WHERE parent_system_id = p_system_id ORDER BY system_id;
226          x_return_status := FND_API.G_RET_STS_SUCCESS;
227 
228      else
229          x_return_status := FND_API.G_RET_STS_ERROR;
230      end if;
231     exception
232         WHEN NO_DATA_FOUND THEN
233            x_return_status := FND_API.G_RET_STS_ERROR;
234 
235         WHEN OTHERS THEN
236            x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
237            FND_MSG_PUB.Count_And_Get
238                (p_count => x_msg_count ,
239                 p_data => x_msg_data
240                );
241    end get_all_child_systems;
242 
243    procedure is_user_associated_to_system(
244                      p_api_version_number     IN   NUMBER,
245                      p_init_msg_list          IN   VARCHAR2,
246                      p_user_id                IN   NUMBER,
247                      p_system_id              IN   NUMBER,
248                      p_system_name            IN   VARCHAR2,
249                      p_commit                 IN   VARCHAR,
250                      x_return_status          OUT  VARCHAR2,
251                      x_msg_count              OUT  NUMBER,
252                      x_msg_data               OUT  VARCHAR2
253                     )
254    as
255      l_party_id      NUMBER(15);
256      l_system_id     NUMBER(15);
257      l_system_count  NUMBER;
258      l_curr_date     DATE;
259    begin
260       l_party_id := get_party_id (p_user_id);
261       l_system_id := -1;
262 	 l_system_count := -1;
263       l_curr_date := sysdate;
264 
265 	 if (p_system_name is not null) then
266         l_system_id := get_system_id (p_system_name);
267         SELECT count(*) INTO l_system_count FROM cs_system_party_links_v WHERE system_id = l_system_id AND party_id = l_party_id and (end_date is null or end_date < l_curr_date);
268         x_return_status := FND_API.G_RET_STS_SUCCESS;
269 
270 	 elsif (p_system_id > 0) then
271         SELECT count(*) INTO l_system_count FROM cs_system_party_links_v WHERE system_id = p_system_id AND party_id = l_party_id and (end_date is null or end_date < l_curr_date);
272        x_return_status := FND_API.G_RET_STS_SUCCESS;
273 	 else
274 	     x_return_status := FND_API.G_RET_STS_ERROR;
275 	 end if;
276       exception
277         WHEN NO_DATA_FOUND THEN
278            x_return_status := FND_API.G_RET_STS_ERROR;
279 
280         WHEN OTHERS THEN
281            x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
282            FND_MSG_PUB.Count_And_Get
283                (p_count => x_msg_count ,
284                 p_data => x_msg_data
285                );
286 
287    end is_user_associated_to_system;
288 
289    procedure association_exists(
293                      p_system_name            IN   VARCHAR2,
290                      p_api_version_number     IN   NUMBER,
291                      p_init_msg_list          IN   VARCHAR2,
292                      p_system_id              IN   NUMBER,
294                      p_commit                 IN   VARCHAR,
295                      x_return_status          OUT  VARCHAR2,
296                      x_msg_count              OUT  NUMBER,
297                      x_msg_data               OUT  VARCHAR2
298                     )
299    as
300      l_system_id     NUMBER(15);
301      l_system_count  NUMBER;
302      l_curr_date     DATE;
303    begin
304       l_system_id := -1;
305 	 l_system_count := -1;
306       l_curr_date := sysdate;
307 
308 	 if (p_system_name is not null) then
309         l_system_id := get_system_id (p_system_name);
310         SELECT count(*) INTO l_system_count FROM cs_system_party_links_v WHERE system_id = l_system_id  and (end_date is null or end_date < l_curr_date);
311 	   if (l_system_count > 0) then
312            x_return_status := FND_API.G_RET_STS_SUCCESS;
313 	  else
314 		 x_return_status := FND_API.G_RET_STS_ERROR;
315 	 end if;
316 
317 	 elsif (p_system_id > 0) then
318         SELECT count(*) INTO l_system_count FROM cs_system_party_links_v WHERE system_id = p_system_id and (end_date is null or end_date < l_curr_date);
319 	  if (l_system_count > 0) then
320           x_return_status := FND_API.G_RET_STS_SUCCESS;
321 	  else
322 	     x_return_status := FND_API.G_RET_STS_ERROR;
323 	 end if;
324 	 end if;
325       exception
326         WHEN NO_DATA_FOUND THEN
327            x_return_status := FND_API.G_RET_STS_ERROR;
328 
329         WHEN OTHERS THEN
330            x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
331            FND_MSG_PUB.Count_And_Get
332                (p_count => x_msg_count ,
333                 p_data => x_msg_data
334                );
335     end association_exists;
336 
337    procedure user_exists(
338                      p_api_version_number     IN   NUMBER,
339                      p_init_msg_list          IN   VARCHAR2,
340                      p_system_id              IN   NUMBER,
341                      p_system_name            IN   VARCHAR2,
342                      p_commit                 IN   VARCHAR,
343                      x_return_status          OUT  VARCHAR2,
344                      x_msg_count              OUT  NUMBER,
345                      x_msg_data               OUT  VARCHAR2
346                     )
347    as
348      l_system_id     NUMBER(15);
349      l_system_count  NUMBER;
350      l_curr_date     DATE;
351    begin
352       l_system_id := -1;
353 	 l_system_count := -1;
354       l_curr_date := sysdate;
355 
356 	 if (p_system_name is not null) then
357         l_system_id := get_system_id (p_system_name);
358         SELECT count(*) INTO l_system_count FROM
359 	   cs_systems_all_b a, hz_cust_accounts b,
360 	   hz_parties c, fnd_user d
361         WHERE a.system_id=l_system_id AND
362         a.customer_id=b.cust_account_id AND
363         b.party_id=c.party_id AND
364         c.party_type='PERSON' AND
365         c.party_id=d.customer_id;
366 
367 	   if (l_system_count > 0) then
368            x_return_status := FND_API.G_RET_STS_SUCCESS;
369 	  else
370 		 x_return_status := FND_API.G_RET_STS_ERROR;
371 	 end if;
372 
373 	 elsif (p_system_id > 0) then
374         SELECT count(*) INTO l_system_count FROM
375         cs_systems_all_b a, hz_cust_accounts b,
376         hz_parties c, fnd_user d
377         WHERE a.system_id=p_system_id AND
378         a.customer_id=b.cust_account_id AND
379         b.party_id=c.party_id AND
380         c.party_type='PERSON' AND
381         c.party_id=d.customer_id;
382 
383 	  if (l_system_count > 0) then
384           x_return_status := FND_API.G_RET_STS_SUCCESS;
385 	  else
386 	     x_return_status := FND_API.G_RET_STS_ERROR;
387 	  end if;
388 	 end if;
389       exception
390         WHEN NO_DATA_FOUND THEN
391            x_return_status := FND_API.G_RET_STS_ERROR;
392 
393         WHEN OTHERS THEN
394            x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
395            FND_MSG_PUB.Count_And_Get
396                (p_count => x_msg_count ,
397                 p_data => x_msg_data
398                );
399     end user_exists;
400 
401 end CS_SYSTEMS_COMMON_PUB;