[Home] [Help]
PACKAGE BODY: APPS.RRS_SITE_INFO
Source
1 package body RRS_SITE_INFO as
2 /* $Header: RRSGSDTB.pls 120.0.12010000.15 2009/09/17 22:47:27 sunarang noship $ */
3
4
5 Procedure Get_complete_site_details(
6 p_site_id_num IN varchar2
7 ,p_site_name IN varchar2 Default null
8 ,p_page_name IN varchar2 Default null
9 ,x_site_header_tab OUT NOCOPY rrs_site_header_tab
10 ,x_site_address_tab OUT NOCOPY rrs_site_address_tab
11 ,x_site_uses_tab OUT NOCOPY rrs_site_uses_tab
12 ,x_party_site_address_tab OUT NOCOPY rrs_site_address_tab
13 ,x_property_tab OUT NOCOPY rrs_property_tab
14 ,x_site_cluster_tab OUT NOCOPY rrs_site_cluster_tab
15 ,x_site_hierar_tab OUT NOCOPY rrs_site_hierar_tab
16 ,x_trade_area_grp_tab OUT NOCOPY rrs_trade_area_grp_tab
17 ,x_relationship_tab OUT NOCOPY rrs_relationship_tab
18 ,x_site_phone_tab OUT NOCOPY rrs_site_phone_tab
19 ,x_site_email_tab OUT NOCOPY rrs_site_email_tab
20 ,x_site_url_tab OUT NOCOPY rrs_site_url_tab
21 ,x_site_person_tab OUT NOCOPY rrs_site_person_tab
22 ,x_site_attachment_tab OUT NOCOPY rrs_site_attachment_tab
23 ,x_site_asset_tab OUT NOCOPY rrs_site_asset_tab
24 ,x_site_attrib_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
25 ,x_site_attrib_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
26 ,x_loc_attrib_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
27 ,x_loc_attrib_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
28 ,x_tr_area_attrib_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
29 ,x_tr_area_attrib_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
30 ) is
31 /*
32 x_site_header_tab rrs_site_header_tab;
33 x_site_address_tab rrs_site_address_tab;
34 x_site_uses_tab rrs_site_uses_tab;
35 */
36
37 begin
38
39 Get_site_details(
40 p_site_id_num => p_site_id_num
41 ,p_site_name => p_site_name
42 ,x_site_header_tab => x_site_header_tab
43 ,x_site_address_tab => x_site_address_tab
44 ,x_site_uses_tab => x_site_uses_tab
45 );
46
47
48 Get_site_attributes(
49 p_site_id_num => p_site_id_num
50 ,p_site_name => p_site_name
51 ,p_page_name => p_page_name
52 ,x_site_attrib_data_table => x_site_attrib_data_table
53 ,x_site_attrib_row_table => x_site_attrib_row_table
54 );
55
56 Get_location_attributes(
57 p_site_id_num => p_site_id_num
58 ,p_site_name => p_site_name
59 ,p_page_name => p_page_name
60 ,x_loc_attrib_data_table => x_loc_attrib_data_table
61 ,x_loc_attrib_row_table => x_loc_attrib_row_table
62 );
63
64 Get_trade_area_attributes(
65 p_site_id_num => p_site_id_num
66 ,p_site_name => p_site_name
67 ,p_page_name => p_page_name
68 ,x_tr_area_attrib_data_table => x_tr_area_attrib_data_table
69 ,x_tr_area_attrib_row_table => x_tr_area_attrib_row_table
70 );
71
72
73 Get_site_associations(
74 p_site_id_num => p_site_id_num
75 ,p_site_name => p_site_name
76 ,x_property_tab => x_property_tab
77 ,x_site_cluster_tab => x_site_cluster_tab
78 ,x_site_hierar_tab => x_site_hierar_tab
79 ,x_trade_area_grp_tab => x_trade_area_grp_tab
80 ,x_relationship_tab => x_relationship_tab
81 );
82
83 -- if x_site_header_tab(1).le_party_site_id is NOT NULL then
84
85 Get_site_contacts(
86 p_site_id_num => p_site_id_num
87 ,p_site_name => p_site_name
88 ,x_party_site_address_tab => x_party_site_address_tab
89 ,x_site_phone_tab => x_site_phone_tab
90 ,x_site_email_tab => x_site_email_tab
91 ,x_site_url_tab => x_site_url_tab
92 ,x_site_person_tab => x_site_person_tab
93 );
94
95 -- end if;
96
97 Get_site_attachments(
98 p_site_id_num => p_site_id_num
99 ,p_site_name => p_site_name
100 ,x_site_attachment_tab => x_site_attachment_tab
101 );
102
103
104 rrs_site_info.get_site_assets(
105 p_site_id_num => p_site_id_num
106 ,p_site_name => p_site_name
107 ,x_site_asset_tab => x_site_asset_tab
108 );
109
110
111
112 end;
113
114
115 Procedure Get_site_details(
116 p_site_id_num IN varchar2
117 ,p_site_name IN varchar2 Default null
118 ,x_site_header_tab OUT NOCOPY rrs_site_header_tab
119 ,x_site_address_tab OUT NOCOPY rrs_site_address_tab
120 ,x_site_uses_tab OUT NOCOPY rrs_site_uses_tab
121 ) is
122
123 l_site_id number;
124 l_site_id_num varchar2(30);
125 l_site_type_code Varchar2(30);
126 l_site_status varchar2(30);
127 l_site_brand_name varchar2(30);
128 l_site_calendar_code varchar2(30);
129 l_location_id number;
130 l_site_party_id number;
131 l_party_site_id number;
132 l_le_party_id number;
133 l_property_location_id number;
134 l_start_date date;
135 l_end_date date;
136 l_site_name varchar2(150);
137
138
139 l_address1 varchar2(240);
140 l_address2 varchar2(240);
141 l_address3 varchar2(240);
142 l_address4 varchar2(240);
143 l_city varchar2(60);
144 l_state varchar2(60);
145 l_postal_code varchar2(60);
146 l_country varchar2(60);
147
148 l_complete_address varchar2(1000);
149
150
151 TYPE local_rrs_site_header_rec is RECORD (
152 site_id rrs_sites_b.site_id%TYPE,
153 site_identification_number rrs_sites_b.site_identification_number%TYPE,
154 site_type_code rrs_sites_b.site_type_code%TYPE,
155 site_type_code_meaning rrs_lookups_v.meaning%TYPE,
156 site_status_code rrs_sites_b.site_status_code%TYPE,
157 site_status_code_meaning rrs_lookups_v.meaning%TYPE,
158 brandname_code rrs_sites_b.brandname_code%TYPE,
159 brandname_code_meaning rrs_lookups_v.meaning%TYPE,
160 calendar_code rrs_sites_b.calendar_code%TYPE,
161 description bom_calendars.description%Type,
162 location_id rrs_sites_b.location_id%TYPE,
163 site_party_id rrs_sites_b.site_party_id%TYPE,
164 party_site_id rrs_sites_b.party_site_id%TYPE,
165 le_party_site_id rrs_sites_b.le_party_id%TYPE,
166 party_name hz_parties.party_name%Type,
167 property_location_id rrs_sites_b.property_location_id%TYPE,
168 start_date rrs_sites_b.start_date%TYPE,
169 end_date rrs_sites_b.end_date%TYPE,
170 name rrs_sites_tl.name%TYPE
171 );
172
173 TYPE local_rrs_site_header_tab IS TABLE OF local_rrs_site_header_rec;
174 l_site_header_details local_rrs_site_header_tab;
175
176 TYPE local_rrs_site_address_rec is RECORD (
177 address1 hz_locations.address1%TYPE,
178 address2 hz_locations.address2%TYPE,
179 address3 hz_locations.address3%TYPE,
180 address4 hz_locations.address4%TYPE,
181 city hz_locations.city%TYPE,
182 county hz_locations.county%TYPE,
183 state hz_locations.state%TYPE,
184 province hz_locations.province%TYPE,
185 postal_code hz_locations.postal_code%TYPE,
186 country hz_locations.country%TYPE,
187 country_name fnd_territories_vl.territory_short_name%Type,
188 address varchar2(1000),
189 identifying_address_flag hz_party_sites.identifying_address_flag%Type,
190 geometry_source hz_locations.geometry_source%Type,
191 geometry_source_meaning rrs_lookups_v.meaning%Type,
192 longitude number,
193 latitude number
194 );
195 TYPE local_rrs_site_address_tab IS TABLE OF local_rrs_site_address_rec;
196 l_site_address_details local_rrs_site_address_tab;
197
198 TYPE local_rrs_site_use_rec is RECORD (
199 site_id rrs_site_uses.site_id%TYPE,
200 site_use_id rrs_site_uses.site_use_id%TYPE,
201 site_use_type_code rrs_site_uses.site_use_type_code%TYPE,
202 site_use_type_code_meaning ar_lookups.meaning%Type,
203 status_code rrs_site_uses.status_code%TYPE,
204 status_code_meaning rrs_lookups_v.meaning%TYPE,
205 is_primary_flag rrs_site_uses.is_primary_flag%TYPE
206 -- is_primary_flag_meaning rrs_lookups_v.meaning%Type
207 );
208 TYPE local_rrs_site_uses_tab IS TABLE OF local_rrs_site_use_rec;
209 site_use_rec_details local_rrs_site_uses_tab;
210
211 l_site_use_id number;
212 l_site_use_type_code varchar2(30);
213 l_status_code varchar2(30);
214 l_is_primary_flag varchar2(1);
215 l_is_primary_flag_selector varchar2(1);
216 l_party_site_use_id number;
217 l_object_version_number number;
218
219 l_site_type_code_meaning rrs_lookups_v.meaning%TYPE;
220
221 l_lookup_code rrs_lookups_v.lookup_code%Type;
222 l_lookup_type rrs_lookups_v.lookup_type%Type;
223
224 Begin
225
226 Begin
227 Select RSB.SITE_ID,
228 RSB.SITE_IDENTIFICATION_NUMBER,
229 RSB.SITE_TYPE_CODE,
230 LKUP1.MEANING AS SITE_TYPE_CODE_MEANING,
231 RSB.SITE_STATUS_CODE,
232 LKUP2.MEANING AS SITE_STATUS_CODE_MEANING,
233 RSB.BRANDNAME_CODE ,
234 LKUP3.MEANING AS BRANDNAME_CODE_MEANING,
235 RSB.CALENDAR_CODE,
236 BC.DESCRIPTION,
237 RSB.LOCATION_ID,
238 RSB.SITE_PARTY_ID,
239 RSB.PARTY_SITE_ID,
240 RSB.LE_PARTY_ID,
241 HP.PARTY_NAME,
242 RSB.PROPERTY_LOCATION_ID,
243 RSB.START_DATE,
244 RSB.END_DATE,
245 RST.NAME
246 Bulk Collect
247 INTO l_site_header_details
248 From RRS_SITES_B RSB, RRS_SITES_TL RST , rrs_lookups_v lkup1,rrs_lookups_v lkup2
249 ,rrs_lookups_v lkup3 , BOM_CALENDARS BC, HZ_PARTIES HP
250 WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num
251 and RSB.SITE_ID = RST.SITE_ID
252 and LKUP1.LOOKUP_TYPE = 'RRS_SITE_TYPE'
253 and LKUP1.LOOKUP_CODE = RSB.SITE_TYPE_CODE
254 and LKUP2.LOOKUP_TYPE = 'RRS_SITE_STATUS'
255 and LKUP2.LOOKUP_CODE = RSB.SITE_STATUS_CODE
256 and LKUP3.LOOKUP_TYPE (+) = 'RRS_BRAND_NAME'
257 and LKUP3.LOOKUP_CODE (+) = RSB.BRANDNAME_CODE
258 and BC.CALENDAR_CODE (+) = RSB.CALENDAR_CODE
259 and DECODE(RSB.SITE_TYPE_CODE,'I',RSB.LE_PARTY_ID,'E',RSB.SITE_PARTY_ID) = HP.PARTY_ID(+)
260 and RST.LANGUAGE = userenv('LANG');
261 Exception
262 When NO_DATA_FOUND Then
263 raise_application_error(-20101, ' Check the Site ID please');
264
265 end;
266
267 x_site_header_tab := rrs_site_header_tab();
268 x_site_header_tab.Extend();
269 x_site_header_tab(1) := rrs_site_header_rec(l_site_header_details(1).site_id
270 ,l_site_header_details(1).site_identification_number
271 ,l_site_header_details(1).site_type_code
272 ,l_site_header_details(1).site_type_code_meaning
273 ,l_site_header_details(1).site_status_code
274 ,l_site_header_details(1).site_status_code_meaning
275 ,l_site_header_details(1).brandname_code
276 ,l_site_header_details(1).brandname_code_meaning
277 ,l_site_header_details(1).calendar_code
278 ,l_site_header_details(1).description
279 ,l_site_header_details(1).location_id
280 ,l_site_header_details(1).site_party_id
281 ,l_site_header_details(1).party_site_id
282 ,l_site_header_details(1).le_party_site_id
283 ,l_site_header_details(1).party_name
284 ,l_site_header_details(1).property_location_id
285 ,l_site_header_details(1).start_date
286 ,l_site_header_details(1).end_date
287 ,l_site_header_details(1).name
288 );
289
290
291
292
293 l_site_id := l_site_header_details(1).site_id;
294 l_location_id := l_site_header_details(1).location_id;
295
296
297 /*
298 Commenting this piece to trouble shoot later why Country does not Show up.
299 SELECT ADDRESS1,
300 ADDRESS2,
301 ADDRESS3,
302 ADDRESS4,
303 NLS_UPPER(CITY) CITY,
304 NLS_UPPER(COUNTY) COUNTY,
305 NLS_UPPER(STATE) STATE,
306 NLS_UPPER(PROVINCE) PROVINCE,
307 NLS_UPPER(POSTAL_CODE) POSTAL_CODE,
308 NLS_UPPER(hz_format_pub.get_tl_territory_name(COUNTRY)) COUNTRY,
309 FTV.TERRITORY_SHORT_NAME COUNTRY_NAME,
310 HZ_FORMAT_PUB.format_address(location_id, null, null, ',' , null) as Address,
311 'Y' as IDENTIFYING_ADDRESS_FLAG
312 Bulk Collect
313 INTO l_site_address_details
314 FROM HZ_LOCATIONS
315 ,FND_TERRITORIES_VL FTV
316 WHERE COUNTRY = FTV.TERRITORY_CODE
317 AND LOCATION_ID = l_location_id;
318 */
319
320 SELECT ADDRESS1,
321 ADDRESS2,
322 ADDRESS3,
323 ADDRESS4,
324 NLS_UPPER(CITY) CITY,
325 NLS_UPPER(COUNTY) COUNTY,
326 NLS_UPPER(STATE) STATE,
327 NLS_UPPER(PROVINCE) PROVINCE,
328 NLS_UPPER(POSTAL_CODE) POSTAL_CODE,
329 COUNTRY,
330 FTV.TERRITORY_SHORT_NAME COUNTRY_NAME,
331 HZ_FORMAT_PUB.format_address(location_id, null, null, ',' , null) as Address,
332 'Y' as IDENTIFYING_ADDRESS_FLAG ,
333 GEOMETRY_SOURCE,
334 ( SELECT rlv.meaning FROM RRS_LOOKUPS_V rlv WHERE rlv.lookup_type = 'RRS_GEO_SOURCE'
335 AND rlv.lookup_code = HL.GEOMETRY_SOURCE ) GEOMETRY_SOURCE_MEANING,
336 ROUND(HL.geometry.SDO_POINT.X,8) Longitude,
337 ROUND(HL.geometry.SDO_POINT.Y,8) Latitude
338 Bulk Collect
339 INTO l_site_address_details
340 FROM HZ_LOCATIONS HL
341 ,FND_TERRITORIES_VL FTV
342 WHERE HL.COUNTRY = FTV.TERRITORY_CODE
343 AND HL.LOCATION_ID = l_location_id;
344
345
346 x_site_address_tab := rrs_site_address_tab();
347 if l_site_address_details.count > 0 then
348 FOR i in l_site_address_details.First..l_site_address_details.Last LOOP
349 x_site_address_tab.Extend();
350 x_site_address_tab(i) := rrs_site_address_rec(l_site_address_details(i).address1
351 ,l_site_address_details(i).address2
352 ,l_site_address_details(i).address3
353 ,l_site_address_details(i).address4
354 ,l_site_address_details(i).city
355 ,l_site_address_details(i).county
356 ,l_site_address_details(i).state
357 ,l_site_address_details(i).province
358 ,l_site_address_details(i).postal_code
359 ,l_site_address_details(i).country
360 ,l_site_address_details(i).country_name
361 ,l_site_address_details(i).address
362 ,l_site_address_details(i).identifying_address_flag
363 ,NULL --Bug 7871825
364 ,l_site_address_details(i).geometry_source
365 ,l_site_address_details(i).geometry_source_meaning
366 ,l_site_address_details(i).longitude
367 ,l_site_address_details(i).latitude
368 );
369 END LOOP;
370 END IF;
371
372 SELECT SiteUseEO.SITE_ID,
373 SiteUseEO.SITE_USE_ID,
374 SiteUseEO.SITE_USE_TYPE_CODE,
375 LKUP1.MEANING AS SITE_USE_TYPE_CODE_MEANING,
376 SiteUseEO.STATUS_CODE,
377 LKUP3.MEANING AS STATUS_CODE_MEANING,
378 SiteUseEO.IS_PRIMARY_FLAG
379 --,LKUP2.MEANING AS IS_PRIMARY_FLAG_MEANING
380 BULK COLLECT
381 INTO site_use_rec_details
382 FROM RRS_SITE_USES SiteUseEO,
383 RRS_SITES_B RSB,
384 HZ_PARTY_SITE_USES HPSU,
385 AR_LOOKUPS LKUP1
386 -- ,RRS_LOOKUPS_V LKUP2
387 ,RRS_LOOKUPS_V LKUP3
388 WHERE RSB.SITE_ID = SiteUseEO.SITE_ID
389 AND HPSU.PARTY_SITE_ID(+)= RSB.PARTY_SITE_ID
390 AND DECODE(HPSU.PARTY_SITE_ID,null,'-999',SiteUseEO.SITE_USE_TYPE_CODE) = nvl(HPSU.SITE_USE_TYPE,'-999')
391 and LKUP1.LOOKUP_TYPE = 'PARTY_SITE_USE_CODE'
392 and LKUP1.LOOKUP_CODE = SiteUseEO.SITE_USE_TYPE_CODE
393 --and LKUP2.LOOKUP_TYPE = 'RRS_YES_NO'
394 --and LKUP2.LOOKUP_CODE = SiteUseEO.IS_PRIMARY_FLAG
395 and LKUP3.LOOKUP_TYPE = 'RRS_SITE_STATUS'
396 and LKUP3.LOOKUP_CODE = SiteUseEO.STATUS_CODE
397 AND RSB.SITE_ID = l_site_id ;
398
399
400 x_site_uses_tab := rrs_site_uses_tab();
401 if site_use_rec_details.count > 0 then
402 FOR i in site_use_rec_details.First..site_use_rec_details.Last LOOP
403 x_site_uses_tab.Extend();
404 x_site_uses_tab(i) := rrs_site_uses_rec(site_use_rec_details(i).site_id
405 ,site_use_rec_details(i).site_use_id
406 ,site_use_rec_details(i).site_use_type_code
407 ,site_use_rec_details(i).site_use_type_code_meaning
408 ,site_use_rec_details(i).status_code
409 ,site_use_rec_details(i).status_code_meaning
410 ,site_use_rec_details(i).is_primary_flag
411 );
412 END LOOP;
413 END IF;
414
415
416
417
418
419 /*
420 dbms_output.put_line('Printing Basic Site Details ');
421 dbms_output.put_line('============================== ');
422 dbms_output.put_line(chr(13) || chr(10));
423 dbms_output.put_line('Site ID : '||chr(9)||chr(9)||chr(9)||x_site_header_tab(1).site_id);
424 dbms_output.put_line('Site Identification Number : '||chr(9)||x_site_header_tab(1).site_identification_number);
425 dbms_output.put_line('Site Type Code : '||chr(9)||chr(9)||x_site_header_tab(1).site_type_code||' ( '||x_site_header_tab(1).site_type_code_meaning||' )');
426 dbms_output.put_line('Site Status Code : '||chr(9)||chr(9)||x_site_header_tab(1).site_status_code||' ( '||x_site_header_tab(1).site_status_code_meaning||' )');
427 dbms_output.put_line('Brand Name : '||chr(9)||chr(9)||chr(9)||x_site_header_tab(1).brandname_code||' ( '||x_site_header_tab(1).brandname_code_meaning||' )');
428 dbms_output.put_line('Calendar Code : '||chr(9)||chr(9)||x_site_header_tab(1).calendar_code||' ( '||x_site_header_tab(1).description||' )');
429 dbms_output.put_line('Location ID : '||chr(9)||chr(9)||chr(9)||x_site_header_tab(1).location_id);
430 dbms_output.put_line('Party Site ID : '||chr(9)||chr(9)||x_site_header_tab(1).party_site_id);
431
432 if x_site_header_tab(1).site_type_code = 'I' then
433 dbms_output.put_line('Legal Entity Party ID : '||chr(9)||x_site_header_tab(1).le_party_site_id||' ( '||x_site_header_tab(1).party_name||' )');
434 elsif x_site_header_tab(1).site_type_code = 'E' then
435 dbms_output.put_line('Site Party ID : '||chr(9)||chr(9)||x_site_header_tab(1).site_party_id||' ( '||x_site_header_tab(1).party_name||' )');
436 end if;
437
438 dbms_output.put_line('Property Location ID : '||chr(9)||x_site_header_tab(1).property_location_id);
439 dbms_output.put_line('Start Date of Site : '||chr(9)||x_site_header_tab(1).start_date);
440 dbms_output.put_line('End Date of Site : '||chr(9)||x_site_header_tab(1).end_date);
441 dbms_output.put_line('Name of Site : '||chr(9)||chr(9)||chr(9)||x_site_header_tab(1).name);
442 dbms_output.put_line('Address1 of Site : '||chr(9)||chr(9)||x_site_address_tab(1).address1);
443 dbms_output.put_line('Address2 of Site : '||chr(9)||chr(9)||x_site_address_tab(1).address2);
444 dbms_output.put_line('Address3 of Site : '||chr(9)||chr(9)||x_site_address_tab(1).address3);
445 dbms_output.put_line('Address4 of Site : '||chr(9)||chr(9)||x_site_address_tab(1).address4);
446 dbms_output.put_line('City of Site : '||chr(9)||chr(9)||x_site_address_tab(1).city);
447 dbms_output.put_line('County of Site : '||chr(9)||chr(9)||x_site_address_tab(1).county);
448 dbms_output.put_line('State of Site : '||chr(9)||chr(9)||x_site_address_tab(1).state);
449 dbms_output.put_line('Province of Site : '||chr(9)||chr(9)||x_site_address_tab(1).province);
450 dbms_output.put_line('Postal Code of Site : '||chr(9)||chr(9)||x_site_address_tab(1).postal_code);
451 dbms_output.put_line('Country of Site : '||chr(9)||chr(9)||x_site_address_tab(1).country);
452 dbms_output.put_line('Complete Address of Site : '||chr(9)||x_site_address_tab(1).address);
453 dbms_output.put_line(chr(13) || chr(10));
454
455 if site_use_rec_details.count > 0 then
456 FOR i in site_use_rec_details.First..site_use_rec_details.Last LOOP
457 dbms_output.put_line('Site Purpose : '||chr(9)||chr(9)||chr(9)||x_site_uses_tab(i).site_use_type_code||' ( '||x_site_uses_tab(i).site_use_type_code_meaning||' )');
458 -- dbms_output.put_line('Site Purpose Meaning: '||chr(9)||chr(9)||x_site_uses_tab(i).site_use_type_code_meaning);
459 dbms_output.put_line('Is this a Primary Site : '||chr(9)||x_site_uses_tab(i).is_primary_flag);
460 -- dbms_output.put_line('Is this a Primary Site Meaning : '||chr(9)||x_site_uses_tab(i).is_primary_flag_meaning);
461 dbms_output.put_line('Status of Site Purpose : '||chr(9)||x_site_uses_tab(i).status_code||' ( '||x_site_uses_tab(i).status_code_meaning||' )');
462 -- dbms_output.put_line('Status of Site Purpose Meaning : '||x_site_uses_tab(i).status_code_meaning);
463 END LOOP;
464 end if;
465
466 dbms_output.put_line(chr(13) || chr(10));
467 dbms_output.put_line('Done Printing Basic Site Details ');
468 dbms_output.put_line('================================= ');
469 */
470
471 end;
472
473
474 Procedure Get_site_complete_attributes(
475 p_site_id_num IN varchar2
476 ,p_site_name IN varchar2 Default null
477 ,x_site_attrib_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
478 ,x_site_attrib_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
479 ,x_loc_attrib_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
480 ,x_loc_attrib_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
481 ,x_tr_area_attrib_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
482 ,x_tr_area_attrib_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
483 ) is
484
485 p_page_name varchar2(240);
486
487 begin
488
489 Get_site_attributes(
490 p_site_id_num => p_site_id_num
491 ,p_site_name => p_site_name
492 ,p_page_name => p_page_name
493 ,x_site_attrib_data_table => x_site_attrib_data_table
494 ,x_site_attrib_row_table => x_site_attrib_row_table
495 );
496
497 Get_location_attributes(
498 p_site_id_num => p_site_id_num
499 ,p_site_name => p_site_name
500 ,p_page_name => p_page_name
501 ,x_loc_attrib_data_table => x_loc_attrib_data_table
502 ,x_loc_attrib_row_table => x_loc_attrib_row_table
503 );
504
505 Get_trade_area_attributes(
506 p_site_id_num => p_site_id_num
507 ,p_site_name => p_site_name
508 ,p_page_name => p_page_name
509 ,x_tr_area_attrib_data_table => x_tr_area_attrib_data_table
510 ,x_tr_area_attrib_row_table => x_tr_area_attrib_row_table
511 );
512
513
514 end;
515
516 Procedure Get_site_attributes(
517 p_site_id_num IN varchar2
518 ,p_site_name IN varchar2 Default null
519 ,p_page_name IN varchar2 Default null
520 ,x_site_attrib_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
521 ,x_site_attrib_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
522 ) is
523
524
525
526 l_api_name CONSTANT VARCHAR2(30) := 'Get_User_Attrs_For_Item';
527
528 l_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
529 l_current_data_obj EGO_USER_ATTR_DATA_OBJ;
530 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
531 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
532 l_site_id NUMBER;
533 l_location_id NUMBER;
534
535 x_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
536 x_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
537 -- x_site_attrib_row_table EGO_USER_ATTR_ROW_TABLE;
538 -- x_site_attrib_data_table EGO_USER_ATTR_DATA_TABLE;
539
540 x_return_status VARCHAR2(1);
541 x_errorcode NUMBER;
542 x_msg_count NUMBER;
543 x_msg_data VARCHAR2(1000);
544 p_site_id NUMBER;
545 l_attributes_data_index NUMBER;
546 l_object_name varchar2(20);
547 l_attr_group_type varchar2(30);
548 l_data_level_name varchar2(30);
549 l_page_id number;
550 l_display_name varchar2(240);
551
552 -- l_attributes_row_table x_attributes_row_table;
553 /*Type site_entity_rec is Record(
554 attr_grp_id ego_obj_ag_assocs_b.attr_grp_id%Type
555 );
556 Type site_entity_tab is table of site_entity_rec;
557 l_entity_details site_entity_tab;
558 */
559
560 Type attr_grp_name is table of varchar2(30);
561 l_attr_grp_name attr_grp_name;
562
563 Type trade_area_id is table of number;
564 l_trade_area_ids trade_area_id;
565
566 Type rrs_entity_rec is Record(
567 lookup_code rrs_lookups_v.lookup_code%Type,
568 meaning rrs_lookups_v.meaning%Type
569 );
570 Type rrs_entity_tab is table of rrs_entity_rec;
571 l_rrs_entity rrs_entity_tab;
572
573 l_rrs_entity_name rrs_lookups_v.meaning%Type;
574
575
576 BEGIN
577
578 -----------------------
579 -- Get PKs organized --
580 -----------------------
581 begin
582 Select RSB.SITE_ID
583 ,RSB.LOCATION_ID
584 INTO l_site_id
585 ,l_location_id
586 FROM RRS_SITES_B RSB
587 WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
588 Exception
589 When NO_DATA_FOUND Then
590 raise_application_error(-20101, ' Check the Site ID please');
591
592 end;
593
594 Select meaning
595 INTO l_rrs_entity_name
596 from RRS_LOOKUPS_V
597 where LOOKUP_TYPE= 'RRS_ENTITY'
598 and LOOKUP_CODE = 'RRS_SITE';
599
600
601 IF (p_page_name is Null) Then
602
603 SELECT ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
604 BULK COLLECT
605 INTO l_attr_grp_name
606 FROM ego_obj_ag_assocs_b eoab
607 ,fnd_objects fo
608 ,EGO_FND_DSC_FLX_CTX_EXT ext
609 WHERE eoab.object_id = fo.object_id
610 AND fo.obj_name in ( 'RRS_SITE')
611 AND eoab.attr_group_id = ext.attr_group_id
612 AND eoab.classification_code IN
613 (
614 SELECT SITE_USE_TYPE_CODE
615 FROM RRS_SITE_USES
616 WHERE SITE_ID = l_site_id
617 );
618
619
620 /*
621
622 SELECT eoab.attr_group_id
623 BULK COLLECT
624 INTO l_attr_grp_id
625 FROM ego_obj_ag_assocs_b eoab
626 ,fnd_objects fo
627 WHERE eoab.object_id = fo.object_id
628 AND fo.obj_name in ( 'RRS_SITE')
629 AND eoab.classification_code IN
630 (
631 SELECT SITE_USE_TYPE_CODE
632 FROM RRS_SITE_USES
633 WHERE SITE_ID = l_site_id
634 );
635 */
636
637 elsif (p_page_name is not null) then
638
639 Begin
640
641 SELECT PAGE_ID,
642 DISPLAY_NAME
643 INTO l_page_id,
644 l_display_name
645 FROM EGO_PAGES_V
646 WHERE OBJECT_NAME='RRS_SITE'
647 AND DISPLAY_NAME = p_page_name
648 AND CLASSIFICATION_CODE IN
649 (
650 SELECT SITE_USE_TYPE_CODE
651 FROM RRS_SITE_USES
652 WHERE SITE_ID = l_site_id
653 )
654 ORDER BY SEQUENCE;
655 Exception
656 When NO_DATA_FOUND Then
657 raise_application_error(-20102, ' Page Information does not exist');
658
659
660
661 End;
662
663
664 SELECT ATTR_GROUP_NAME
665 BULK COLLECT
666 INTO l_attr_grp_name
667 FROM EGO_PAGE_ENTRIES_V
668 WHERE PAGE_ID=l_page_id
669 ORDER BY SEQUENCE;
670
671
672
673 end if;
674
675
676 l_pk_column_values :=
677 EGO_COL_NAME_VALUE_PAIR_ARRAY(
678 EGO_COL_NAME_VALUE_PAIR_OBJ('SITE_ID', TO_CHAR(l_site_id)));
679
680 l_object_name := 'RRS_SITE';
681 -- l_object_name := l_rrs_entity(k).lookup_code;
682 l_attr_group_type := 'RRS_SITEMGMT_GROUP';
683 l_data_level_name := 'SITE_LEVEL';
684
685
686 if l_attr_grp_name.count > 0 Then
687 x_site_attrib_row_table := EGO_USER_ATTR_ROW_TABLE();
688 x_site_attrib_data_table := EGO_USER_ATTR_DATA_TABLE();
689
690 for i in l_attr_grp_name.First..l_attr_grp_name.Last Loop
691
692 l_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
693 l_request_table.EXTEND();
694 l_request_table(l_request_table.LAST) := EGO_ATTR_GROUP_REQUEST_OBJ(
695 NULL --ATTR_GROUP_ID
696 ,718 --APPLICATION_ID
697 ,l_attr_group_type --ATTR_GROUP_TYPE
698 ,l_attr_grp_name(i) --ATTR_GROUP_NAME
699 ,l_data_level_name --DATA_LEVEL
700 ,NULL --DATA_LEVEL_1
701 ,NULL --DATA_LEVEL_2
702 ,NULL --DATA_LEVEL_3
703 ,NULL --DATA_LEVEL_4
704 ,NULL --DATA_LEVEL_5
705 ,NULL --ATTR_NAME_LIST
706 );
707
708 EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
709 p_api_version => 1.0
710 ,p_object_name => l_object_name
711 ,p_pk_column_name_value_pairs => l_pk_column_values
712 ,p_attr_group_request_table => l_request_table
713 ,p_user_privileges_on_object => NULL
714 ,p_entity_id => NULL
715 ,p_entity_index => NULL
716 ,p_entity_code => NULL
717 ,p_debug_level => 0
718 ,p_init_error_handler => FND_API.G_FALSE
719 ,p_init_fnd_msg_list => FND_API.G_FALSE
720 ,p_add_errors_to_fnd_stack => FND_API.G_FALSE
721 ,p_commit => FND_API.G_FALSE
722 ,x_attributes_row_table => x_attributes_row_table
723 ,x_attributes_data_table => x_attributes_data_table
724 ,x_return_status => x_return_status
725 ,x_errorcode => x_errorcode
726 ,x_msg_count => x_msg_count
727 ,x_msg_data => x_msg_data
728 );
729
730
731 IF (x_attributes_row_table IS NOT NULL AND x_attributes_row_table.COUNT > 0 AND
732 x_attributes_data_table IS NOT NULL AND x_attributes_data_table.COUNT > 0) THEN
733
734
735 For n in x_attributes_row_table.First..x_attributes_row_table.Last Loop
736
737 x_site_attrib_row_table.Extend();
738 -- z_count := x_site_attrib_row_table.count;
739 x_site_attrib_row_table(x_site_attrib_row_table.Last) := x_attributes_row_table(n);
740
741 End Loop;
742
743
744 For n in x_attributes_data_table.First..x_attributes_data_table.Last Loop
745
746 x_site_attrib_data_table.Extend();
747 -- z_count := x_site_attrib_data_table.count;
748 x_site_attrib_data_table(x_site_attrib_data_table.Last) := x_attributes_data_table(n);
749
750 End Loop;
751
752
753 End if;
754
755
756 END LOOP;
757
758 End if;
759
760 /*
761 IF (x_attributes_row_table IS NOT NULL AND
762 x_attributes_row_table.COUNT > 0 AND
763 x_attributes_data_table IS NOT NULL AND
764 x_attributes_data_table.COUNT > 0
765 AND l_rrs_entity (k).meaning <> 'Trade Area') THEN
766
767 l_attributes_data_index := x_attributes_data_table.FIRST;
768 WHILE l_attributes_data_index <= x_attributes_data_table.LAST
769 LOOP
770
771 l_current_data_obj := x_attributes_data_table(l_attributes_data_index);
772
773 dbms_output.put_line('Attribute Name is : ' ||chr(9)||chr(9)|| (l_current_data_obj.ATTR_NAME));
774 dbms_output.put_line('String Attribute Value: ' ||chr(9)|| (l_current_data_obj.ATTR_VALUE_STR));
775 dbms_output.put_line('Number Attribute Value : ' ||chr(9)|| (l_current_data_obj.ATTR_VALUE_NUM));
776 dbms_output.put_line('Date Attribute Value : ' ||chr(9)|| (l_current_data_obj.ATTR_VALUE_DATE));
777 dbms_output.put_line('Display Value of Attribute : ' ||chr(9)|| (l_current_data_obj.ATTR_DISP_VALUE));
778 dbms_output.put_line('UOM for Attribute : ' ||chr(9)|| (l_current_data_obj.ATTR_UNIT_OF_MEASURE));
779 dbms_output.put_line(chr(13) || chr(10));
780
781 l_attributes_data_index := x_attributes_data_table.NEXT(l_attributes_data_index);
782 END LOOP;
783
784 END IF;
785
786 dbms_output.put_line(chr(13) || chr(10));
787 dbms_output.put_line('Done Printing ( UDA ) Attribute Details for Entity : '||l_rrs_entity (k).meaning);
788 dbms_output.put_line('============================================================= ');
789 */
790
791 END;
792
793
794 Procedure Get_location_attributes(
795 p_site_id_num IN varchar2
796 ,p_site_name IN varchar2 Default null
797 ,p_page_name IN varchar2 Default null
798 ,x_loc_attrib_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
799 ,x_loc_attrib_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
800 ) is
801
802
803
804 l_api_name CONSTANT VARCHAR2(30) := 'Get_User_Attrs_For_Item';
805
806 l_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
807 l_current_data_obj EGO_USER_ATTR_DATA_OBJ;
808 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
809 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
810 l_site_id NUMBER;
811 l_location_id NUMBER;
812
813 x_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
814 x_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
815 x_return_status VARCHAR2(1);
816 x_errorcode NUMBER;
817 x_msg_count NUMBER;
818 x_msg_data VARCHAR2(1000);
819 p_site_id NUMBER;
820 l_attributes_data_index NUMBER;
821 l_object_name varchar2(20);
822 l_attr_group_type varchar2(30);
823 l_data_level_name varchar2(30);
824
825 -- l_attributes_row_table x_attributes_row_table;
826
827 Type attr_grp_name is table of varchar2(30);
828 l_attr_grp_name attr_grp_name;
829
830
831 Type rrs_entity_rec is Record(
832 lookup_code rrs_lookups_v.lookup_code%Type,
833 meaning rrs_lookups_v.meaning%Type
834 );
835 Type rrs_entity_tab is table of rrs_entity_rec;
836 l_rrs_entity rrs_entity_tab;
837
838 l_rrs_entity_name rrs_lookups_v.meaning%Type;
839
840 l_page_id number;
841 l_display_name varchar2(240);
842
843
844
845 BEGIN
846
847 -----------------------
848 -- Get PKs organized --
849 -----------------------
850 begin
851 Select RSB.SITE_ID
852 ,RSB.LOCATION_ID
853 INTO l_site_id
854 ,l_location_id
855 FROM RRS_SITES_B RSB
856 WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
857 Exception
858 When NO_DATA_FOUND Then
859 raise_application_error(-20101, ' Check the Site ID please');
860
861 end;
862
863 Select meaning
864 INTO l_rrs_entity_name
865 from RRS_LOOKUPS_V
866 where LOOKUP_TYPE= 'RRS_ENTITY'
867 and LOOKUP_CODE = 'RRS_LOCATION';
868
869
870
871 IF (p_page_name is Null) Then
872
873
874 SELECT ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
875 BULK COLLECT
876 INTO l_attr_grp_name
877 FROM ego_obj_ag_assocs_b eoab
878 ,fnd_objects fo
879 ,EGO_FND_DSC_FLX_CTX_EXT ext
880 WHERE eoab.object_id = fo.object_id
881 AND fo.obj_name in ( 'RRS_LOCATION')
882 AND eoab.attr_group_id = ext.attr_group_id
883 AND eoab.classification_code IN
884 ( select Country
885 from rrs_locations_ext_vl
886 where location_id = l_location_id
887 );
888
889
890 elsif (p_page_name is not null) then
891
892 Begin
893
894 SELECT PAGE_ID,
895 DISPLAY_NAME
896 INTO l_page_id,
897 l_display_name
898 FROM EGO_PAGES_V
899 WHERE OBJECT_NAME='RRS_LOCATION'
900 AND DISPLAY_NAME = p_page_name
901 AND CLASSIFICATION_CODE IN
902 (
903 select Country
904 from rrs_locations_ext_vl
905 where location_id = l_location_id
906 )
907 ORDER BY SEQUENCE;
908
909 Exception
910 When NO_DATA_FOUND Then
911 raise_application_error(-20102, ' Page Information does not exist');
912
913 End;
914
915
916 SELECT ATTR_GROUP_NAME
917 BULK COLLECT
918 INTO l_attr_grp_name
919 FROM EGO_PAGE_ENTRIES_V
920 WHERE PAGE_ID=l_page_id
921 ORDER BY SEQUENCE;
922
923
924 end if;
925
926
927 l_pk_column_values :=
928 EGO_COL_NAME_VALUE_PAIR_ARRAY(
929 EGO_COL_NAME_VALUE_PAIR_OBJ('LOCATION_ID', TO_CHAR(l_location_id)));
930
931
932 l_object_name := 'RRS_LOCATION';
933 -- l_object_name := l_rrs_entity(k).lookup_code;
934 l_attr_group_type := 'RRS_LOCATION_GROUP';
935 l_data_level_name := 'LOCATION_LEVEL';
936
937
938 if l_attr_grp_name.count > 0 Then
939 x_loc_attrib_row_table := EGO_USER_ATTR_ROW_TABLE();
940 x_loc_attrib_data_table := EGO_USER_ATTR_DATA_TABLE();
941
942
943 for i in l_attr_grp_name.First..l_attr_grp_name.Last Loop
944
945 l_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
946 l_request_table.EXTEND();
947 l_request_table(l_request_table.LAST) := EGO_ATTR_GROUP_REQUEST_OBJ(
948 NULL --ATTR_GROUP_ID
949 ,718 --APPLICATION_ID
950 ,l_attr_group_type --ATTR_GROUP_TYPE
951 ,l_attr_grp_name(i) --ATTR_GROUP_NAME
952 ,l_data_level_name --DATA_LEVEL
953 ,NULL --DATA_LEVEL_1
954 ,NULL --DATA_LEVEL_2
955 ,NULL --DATA_LEVEL_3
956 ,NULL --DATA_LEVEL_4
957 ,NULL --DATA_LEVEL_5
958 ,NULL --ATTR_NAME_LIST
959 );
960
961 EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
962 p_api_version => 1.0
963 ,p_object_name => l_object_name
964 ,p_pk_column_name_value_pairs => l_pk_column_values
965 ,p_attr_group_request_table => l_request_table
966 ,p_user_privileges_on_object => NULL
967 ,p_entity_id => NULL
968 ,p_entity_index => NULL
969 ,p_entity_code => NULL
970 ,p_debug_level => 0
971 ,p_init_error_handler => FND_API.G_FALSE
972 ,p_init_fnd_msg_list => FND_API.G_FALSE
973 ,p_add_errors_to_fnd_stack => FND_API.G_FALSE
974 ,p_commit => FND_API.G_FALSE
975 ,x_attributes_row_table => x_attributes_row_table
976 ,x_attributes_data_table => x_attributes_data_table
977 ,x_return_status => x_return_status
978 ,x_errorcode => x_errorcode
979 ,x_msg_count => x_msg_count
980 ,x_msg_data => x_msg_data
981 );
982
983 IF (x_attributes_row_table IS NOT NULL AND x_attributes_row_table.COUNT > 0 AND
984 x_attributes_data_table IS NOT NULL AND x_attributes_data_table.COUNT > 0)
985 THEN
986
987
988 For n in x_attributes_row_table.First..x_attributes_row_table.Last Loop
989
990 x_loc_attrib_row_table.Extend();
991 x_loc_attrib_row_table(x_loc_attrib_row_table.Last) := x_attributes_row_table(n);
992
993 End Loop;
994
995
996 For n in x_attributes_data_table.First..x_attributes_data_table.Last Loop
997
998 x_loc_attrib_data_table.Extend();
999 x_loc_attrib_data_table(x_loc_attrib_data_table.Last) := x_attributes_data_table(n);
1000
1001 End Loop;
1002
1003
1004 End if;
1005
1006
1007 END LOOP;
1008
1009 End if;
1010
1011
1012 END;
1013
1014
1015 Procedure Get_trade_area_attributes(
1016 p_site_id_num IN varchar2
1017 ,p_site_name IN varchar2 Default null
1018 ,p_page_name IN varchar2 Default null
1019 ,x_tr_area_attrib_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
1020 ,x_tr_area_attrib_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
1021 ) is
1022
1023
1024
1025 l_api_name CONSTANT VARCHAR2(30) := 'Get_User_Attrs_For_Item';
1026
1027 l_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
1028 l_current_data_obj EGO_USER_ATTR_DATA_OBJ;
1029 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
1030 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
1031 l_site_id NUMBER;
1032 l_location_id NUMBER;
1033
1034 x_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1035 x_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
1036 x_return_status VARCHAR2(1);
1037 x_errorcode NUMBER;
1038 x_msg_count NUMBER;
1039 x_msg_data VARCHAR2(1000);
1040 p_site_id NUMBER;
1041 l_attributes_data_index NUMBER;
1042 l_object_name varchar2(20);
1043 l_attr_group_type varchar2(30);
1044 l_data_level_name varchar2(30);
1045
1046 -- l_attributes_row_table x_attributes_row_table;
1047 Type attr_grp_name is table of varchar2(30);
1048 l_attr_grp_name attr_grp_name;
1049
1050
1051 Type trade_area_id is table of number;
1052 l_trade_area_ids trade_area_id;
1053
1054 Type rrs_entity_rec is Record(
1055 lookup_code rrs_lookups_v.lookup_code%Type,
1056 meaning rrs_lookups_v.meaning%Type
1057 );
1058 Type rrs_entity_tab is table of rrs_entity_rec;
1059 l_rrs_entity rrs_entity_tab;
1060
1061 l_rrs_entity_name rrs_lookups_v.meaning%Type;
1062
1063 l_page_id number;
1064 l_display_name varchar2(240);
1065
1066
1067
1068 BEGIN
1069
1070 -----------------------
1071 -- Get PKs organized --
1072 -----------------------
1073 begin
1074 Select RSB.SITE_ID
1075 ,RSB.LOCATION_ID
1076 INTO l_site_id
1077 ,l_location_id
1078 FROM RRS_SITES_B RSB
1079 WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
1080 Exception
1081 When NO_DATA_FOUND Then
1082 raise_application_error(-20101, ' Check the Site ID please');
1083
1084 end;
1085
1086
1087 Select meaning
1088 INTO l_rrs_entity_name
1089 from RRS_LOOKUPS_V
1090 where LOOKUP_TYPE= 'RRS_ENTITY'
1091 and LOOKUP_CODE = 'RRS_TRADE_AREA';
1092
1093
1094 IF (p_page_name is Null) Then
1095
1096
1097 SELECT distinct ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
1098 BULK COLLECT
1099 INTO l_attr_grp_name
1100 FROM ego_obj_ag_assocs_b eoab
1101 ,fnd_objects fo
1102 ,EGO_FND_DSC_FLX_CTX_EXT ext
1103 WHERE eoab.object_id = fo.object_id
1104 AND fo.obj_name in ( 'RRS_TRADE_AREA')
1105 AND eoab.attr_group_id = ext.attr_group_id
1106 AND eoab.classification_code IN
1107 ( select b.group_id
1108 from rrs_trade_areas_ext_vl a,rrs_trade_areas b
1109 where b.location_id = l_location_id
1110 and a.trade_area_id = b.trade_area_id
1111 );
1112
1113 select trade_area_id
1114 BULK COLLECT
1115 INTO l_trade_area_ids
1116 from rrs_trade_areas
1117 where location_id = l_location_id;
1118
1119
1120 elsif (p_page_name is not null) then
1121
1122 Begin
1123
1124 SELECT PAGE_ID,
1125 DISPLAY_NAME
1126 INTO l_page_id,
1127 l_display_name
1128 FROM EGO_PAGES_V
1129 WHERE OBJECT_NAME='RRS_TRADE_AREA'
1130 AND DISPLAY_NAME = p_page_name
1131 AND CLASSIFICATION_CODE IN
1132 (
1133 select b.group_id
1134 from rrs_trade_areas_ext_vl a
1135 ,rrs_trade_areas b
1136 where b.location_id = l_location_id
1137 and a.trade_area_id = b.trade_area_id
1138 )
1139 ORDER BY SEQUENCE;
1140
1141 Exception
1142 When NO_DATA_FOUND Then
1143 raise_application_error(-20102, ' Page Information does not exist');
1144
1145 End;
1146
1147
1148 SELECT ATTR_GROUP_NAME
1149 BULK COLLECT
1150 INTO l_attr_grp_name
1151 FROM EGO_PAGE_ENTRIES_V
1152 WHERE PAGE_ID=l_page_id
1153 ORDER BY SEQUENCE;
1154
1155
1156 select trade_area_id
1157 BULK COLLECT
1158 INTO l_trade_area_ids
1159 from rrs_trade_areas
1160 where location_id = l_location_id
1161 and group_id in
1162 (
1163 select classification_code
1164 from EGO_PAGE_ENTRIES_V
1165 where page_id = l_page_id)
1166 ;
1167
1168 end if;
1169
1170
1171
1172 l_object_name := 'RRS_TRADE_AREA';
1173 -- l_object_name := l_rrs_entity(k).lookup_code;
1174 l_attr_group_type := 'RRS_TRADE_AREA_GROUP';
1175 l_data_level_name := 'TRADE_AREA_LEVEL';
1176
1177
1178 if l_attr_grp_name.count > 0 Then
1179
1180 x_tr_area_attrib_row_table := EGO_USER_ATTR_ROW_TABLE();
1181 x_tr_area_attrib_data_table := EGO_USER_ATTR_DATA_TABLE();
1182
1183 for i in l_attr_grp_name.First..l_attr_grp_name.Last Loop
1184 l_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
1185 l_request_table.EXTEND();
1186 l_request_table(l_request_table.LAST) := EGO_ATTR_GROUP_REQUEST_OBJ(
1187 NULL --ATTR_GROUP_ID
1188 ,718 --APPLICATION_ID
1189 ,l_attr_group_type --ATTR_GROUP_TYPE
1190 ,l_attr_grp_name(i) --ATTR_GROUP_NAME
1191 ,l_data_level_name --DATA_LEVEL
1192 ,NULL --DATA_LEVEL_1
1193 ,NULL --DATA_LEVEL_2
1194 ,NULL --DATA_LEVEL_3
1195 ,NULL --DATA_LEVEL_4
1196 ,NULL --DATA_LEVEL_5
1197 ,NULL --ATTR_NAME_LIST
1198 );
1199
1200
1201
1202
1203 for p in l_trade_area_ids.First..l_trade_area_ids.Last Loop
1204 l_pk_column_values :=
1205 EGO_COL_NAME_VALUE_PAIR_ARRAY(
1206 EGO_COL_NAME_VALUE_PAIR_OBJ('TRADE_AREA_ID', TO_CHAR(l_trade_area_ids(p)))
1207 );
1208
1209 EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
1210 p_api_version => 1.0
1211 ,p_object_name => l_object_name
1212 ,p_pk_column_name_value_pairs => l_pk_column_values
1213 ,p_attr_group_request_table => l_request_table
1214 ,p_user_privileges_on_object => NULL
1215 ,p_entity_id => NULL
1216 ,p_entity_index => NULL
1217 ,p_entity_code => NULL
1218 ,p_debug_level => 0
1219 ,p_init_error_handler => FND_API.G_FALSE
1220 ,p_init_fnd_msg_list => FND_API.G_FALSE
1221 ,p_add_errors_to_fnd_stack => FND_API.G_FALSE
1222 ,p_commit => FND_API.G_FALSE
1223 ,x_attributes_row_table => x_attributes_row_table
1224 ,x_attributes_data_table => x_attributes_data_table
1225 ,x_return_status => x_return_status
1226 ,x_errorcode => x_errorcode
1227 ,x_msg_count => x_msg_count
1228 ,x_msg_data => x_msg_data
1229 );
1230
1231
1232 IF (x_attributes_row_table IS NOT NULL AND x_attributes_row_table.COUNT > 0 AND
1233 x_attributes_data_table IS NOT NULL AND x_attributes_data_table.COUNT > 0)
1234 THEN
1235
1236
1237 For n in x_attributes_row_table.First..x_attributes_row_table.Last Loop
1238
1239 x_tr_area_attrib_row_table.Extend();
1240 x_tr_area_attrib_row_table(x_tr_area_attrib_row_table.Last) := x_attributes_row_table(n);
1241
1242 End Loop;
1243
1244
1245 For n in x_attributes_data_table.First..x_attributes_data_table.Last Loop
1246
1247 x_tr_area_attrib_data_table.Extend();
1248 x_tr_area_attrib_data_table(x_tr_area_attrib_data_table.Last) := x_attributes_data_table(n);
1249
1250 End Loop;
1251
1252
1253 End if;
1254
1255
1256
1257 end loop;
1258
1259
1260 END LOOP;
1261
1262 END IF;
1263
1264 END;
1265
1266
1267 Procedure Get_site_associations(
1268 p_site_id_num IN varchar2
1269 ,p_site_name IN varchar2 Default null
1270 ,x_property_tab OUT NOCOPY rrs_property_tab
1271 ,x_site_cluster_tab OUT NOCOPY rrs_site_cluster_tab
1272 ,x_site_hierar_tab OUT NOCOPY rrs_site_hierar_tab
1273 ,x_trade_area_grp_tab OUT NOCOPY rrs_trade_area_grp_tab
1274 ,x_relationship_tab OUT NOCOPY rrs_relationship_tab
1275 ) is
1276
1277 TYPE local_rrs_site_cluster_rec is RECORD (
1278 cluster_name rrs_site_groups_tl.name%TYPE
1279 );
1280 Type local_rrs_site_cluster_tab is table of local_rrs_site_cluster_rec;
1281 l_cluster_name local_rrs_site_cluster_tab;
1282
1283
1284 TYPE local_rrs_site_hierar_rec is RECORD (
1285 hierarchy_name rrs_site_groups_tl.name%TYPE,
1286 hierarchy_node_name rrs_site_group_nodes_tl.name%TYPE,
1287 parent_site_name rrs_sites_tl.name%Type
1288 );
1289 Type local_rrs_site_hierar_tab is table of local_rrs_site_hierar_rec;
1290 l_hierarchy_details local_rrs_site_hierar_tab;
1291
1292 l_site_id NUMBER;
1293 l_location_id NUMBER;
1294 l_site_party_id NUMBER;
1295
1296 TYPE local_rrs_trade_area_grp_rec is RECORD (
1297 group_id rrs_loc_trade_area_grps.group_id%TYPE,
1298 group_name rrs_trade_area_groups_tl.name%TYPE,
1299 group_desc rrs_trade_area_groups_tl.description%TYPE,
1300 is_primary_flag rrs_loc_trade_area_grps.is_primary_flag%TYPE,
1301 status_code rrs_loc_trade_area_grps.status_code%TYPE
1302 );
1303 TYPE local_rrs_trade_area_grp_tab IS TABLE OF local_rrs_trade_area_grp_rec;
1304 l_trade_area_groups_details local_rrs_trade_area_grp_tab;
1305
1306 TYPE local_rrs_relationship_rec is RECORD (
1307 start_date hz_relationships.start_date%Type,
1308 end_date hz_relationships.end_date%Type,
1309 comments hz_relationships.comments%Type,
1310 subject_party_name hz_parties.party_name%Type,
1311 object_party_name hz_parties.party_name%Type,
1312 relationship_role hz_relationship_types.role%Type,
1313 relationship_role_meaning fnd_lookup_values.description%Type
1314 );
1315 TYPE local_rrs_relationship_tab IS TABLE OF local_rrs_relationship_rec;
1316 l_relationship_details local_rrs_relationship_tab;
1317
1318
1319 TYPE local_rrs_property_rec is RECORD (
1320 location_name pn_locations_all.location_code%TYPE,
1321 property_name pn_properties_all.property_name%TYPE,
1322 org_code mtl_parameters.organization_code%TYPE,
1323 org_description hr_all_organization_units.name%TYPE
1324 );
1325 TYPE local_rrs_property_tab IS TABLE OF local_rrs_property_rec;
1326 l_property_details local_rrs_property_tab;
1327
1328
1329
1330 begin
1331
1332 begin
1333 Select RSB.SITE_ID,
1334 RSB.LOCATION_ID,
1335 RSB.SITE_PARTY_ID
1336 INTO l_site_id,
1337 l_location_id,
1338 l_site_party_id
1339 FROM RRS_SITES_B RSB
1340 WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
1341 Exception
1342 When NO_DATA_FOUND Then
1343 raise_application_error(-20101, ' Check the Site ID please');
1344
1345 end;
1346
1347 -- Entities Details
1348
1349
1350 select RRS_SITE_UTILS.GET_LOCATION_NAME(site.site_id) as location_name,
1351 RRS_SITE_UTILS.GET_PROPERTY_NAME(site.property_location_id) as property_name,
1352 MP.ORGANIZATION_CODE ORGANIZATION_CODE,
1353 HAOU.NAME ORGANIZATION_DESCRIPTION
1354 BULK Collect
1355 INTO l_property_details
1356 from
1357 rrs_sites_b site,
1358 HR_ALL_ORGANIZATION_UNITS HAOU,
1359 MTL_PARAMETERS MP
1360 where MP.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
1361 and site.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
1362 and site.site_id = l_site_id;
1363
1364 x_property_tab := rrs_property_tab();
1365 if l_property_details.count > 0 then
1366 For i in l_property_details.First..l_property_details.Last Loop
1367 x_property_tab.Extend();
1368 x_property_tab(i) := rrs_property_rec(l_property_details(i).location_name
1369 ,l_property_details(i).property_name
1370 ,l_property_details(i).org_code
1371 ,l_property_details(i).org_description
1372 );
1373
1374 END LOOP;
1375 END IF;
1376
1377 -- Trade Area Groups
1378
1379 SELECT LocTradeAreaGrpsEO.GROUP_ID,
1380 VL.NAME,
1381 VL.DESCRIPTION,
1382 LocTradeAreaGrpsEO.IS_PRIMARY_FLAG,
1383 LocTradeAreaGrpsEO.STATUS_CODE
1384 BULK COLLECT
1385 INTO l_trade_area_groups_details
1386 FROM RRS_LOC_TRADE_AREA_GRPS LocTradeAreaGrpsEO
1387 ,RRS_TRADE_AREA_GROUPS_VL VL
1388 WHERE LocTradeAreaGrpsEO.GROUP_ID = VL.GROUP_ID
1389 AND LOCATION_ID = l_location_id
1390 ORDER BY NAME;
1391
1392
1393 x_trade_area_grp_tab := rrs_trade_area_grp_tab();
1394 if l_trade_area_groups_details.count > 0 then
1395 FOR i in l_trade_area_groups_details.First..l_trade_area_groups_details.Last LOOP
1396 x_trade_area_grp_tab.Extend();
1397 x_trade_area_grp_tab(i) := rrs_trade_area_grp_rec(l_trade_area_groups_details(i).group_id
1398 ,l_trade_area_groups_details(i).group_name
1399 ,l_trade_area_groups_details(i).group_desc
1400 ,l_trade_area_groups_details(i).is_primary_flag
1401 ,l_trade_area_groups_details(i).status_code
1402 );
1403 END LOOP;
1404 END IF;
1405
1406 -- Clusters
1407
1408 SELECT SiteGroup.NAME
1409 BULK COLLECT
1410 INTO l_cluster_name
1411 FROM RRS_SITE_GROUP_MEMBERS SiteGroupMemberEO ,RRS_SITE_GROUPS_VL SiteGroup
1412 WHERE SiteGroupMemberEO.SITE_GROUP_ID = SiteGroup.SITE_GROUP_ID
1413 AND SiteGroup.SITE_GROUP_TYPE_CODE = 'C'
1414 AND SiteGroupMemberEO.SITE_GROUP_VERSION_ID =
1415 (SELECT MAX(SITE_GROUP_VERSION_ID)
1416 FROM RRS_SITE_GROUP_VERSIONS curVer
1417 WHERE curVer.SITE_GROUP_ID = SiteGroupMemberEO.SITE_GROUP_ID)
1418 AND SiteGroupMemberEO.CHILD_MEMBER_ID = l_site_id
1419 AND DELETED_FLAG = 'N';
1420
1421 x_site_cluster_tab := rrs_site_cluster_tab();
1422 if l_cluster_name.count > 0 then
1423 FOR i in l_cluster_name.First..l_cluster_name.Last LOOP
1424 x_site_cluster_tab.Extend();
1425 x_site_cluster_tab(i) := rrs_site_cluster_rec(l_cluster_name(i).cluster_name
1426 );
1427 END LOOP;
1428 END IF;
1429
1430 -- Hierarchy
1431
1432 /* This was used in 12.1.1 when hierarchies only had Node to Site relation.
1433
1434 SELECT SiteGroup.NAME,
1435 SiteGroupNode.NAME SiteGroupNodeName
1436 BULK COLLECT
1437 INTO l_hierarchy_details
1438 FROM RRS_SITE_GROUP_MEMBERS SiteGroupMemberEO
1439 ,RRS_SITE_GROUPS_VL SiteGroup
1440 ,RRS_SITE_GROUP_NODES_VL SiteGroupNode
1441 WHERE SiteGroupMemberEO.SITE_GROUP_ID = SiteGroup.SITE_GROUP_ID
1442 AND SiteGroup.SITE_GROUP_TYPE_CODE = 'H'
1443 AND SiteGroupNode.site_group_node_id = SiteGroupMemberEO.PARENT_MEMBER_ID
1444 AND SiteGroupMemberEO.SITE_GROUP_VERSION_ID = (SELECT MAX(SITE_GROUP_VERSION_ID)
1445 FROM RRS_SITE_GROUP_VERSIONS curVer WHERE curVer.SITE_GROUP_ID =
1446 SiteGroupMemberEO.SITE_GROUP_ID) AND SiteGroupMemberEO.CHILD_MEMBER_ID = l_site_id
1447 AND DELETED_FLAG = 'N';
1448 */
1449
1450 /* This is the new Query for 12.1.2 because of new Hierarchy project. Now we
1451 * introduced the concept of Site to Site relations and Site to Node relations
1452 * alonwith Node to Site Relationship.
1453 */
1454
1455 Select Hierarchy_name,
1456 SiteGroupNodeName,
1457 SiteName
1458 BULK COLLECT
1459 INTO l_hierarchy_details
1460 FROM
1461 (SELECT SiteGroup.NAME Hierarchy_name,
1462 SiteGroupNode.NAME SiteGroupNodeName,
1463 NULL SiteName
1464 FROM RRS_SITE_GROUP_MEMBERS SiteGroupMemberEO
1465 ,RRS_SITE_GROUPS_VL SiteGroup
1466 ,RRS_SITE_GROUP_NODES_VL SiteGroupNode
1467 WHERE SiteGroupMemberEO.SITE_GROUP_ID = SiteGroup.SITE_GROUP_ID
1468 AND SiteGroup.SITE_GROUP_TYPE_CODE = 'H'
1469 AND SiteGroupNode.site_group_node_id = SiteGroupMemberEO.PARENT_MEMBER_ID
1470 AND SiteGroupMemberEO.SITE_GROUP_VERSION_ID = (SELECT MAX(SITE_GROUP_VERSION_ID)
1471 FROM RRS_SITE_GROUP_VERSIONS curVer WHERE curVer.SITE_GROUP_ID =
1472 SiteGroupMemberEO.SITE_GROUP_ID) AND SiteGroupMemberEO.CHILD_MEMBER_ID =
1473 l_site_id
1474 AND SiteGroupMemberEO.DELETED_FLAG = 'N'
1475 UNION ALL
1476 SELECT
1477 SiteGroup.NAME Hierarchy_name,
1478 NULL SiteGroupNodeName,
1479 SITE.NAME SiteName
1480 FROM RRS_SITE_GROUP_MEMBERS SiteGroupMemberEO
1481 ,RRS_SITE_GROUPS_VL SiteGroup
1482 ,RRS_SITES_VL SITE
1483 WHERE SiteGroupMemberEO.SITE_GROUP_ID = SiteGroup.SITE_GROUP_ID
1484 AND SiteGroup.SITE_GROUP_TYPE_CODE = 'H'
1485 AND SITE.SITE_ID =SiteGroupMemberEO.PARENT_MEMBER_ID
1486 AND SiteGroupMemberEO.SITE_GROUP_VERSION_ID = (SELECT MAX(SITE_GROUP_VERSION_ID)
1487 FROM RRS_SITE_GROUP_VERSIONS curVer WHERE curVer.SITE_GROUP_ID =
1488 SiteGroupMemberEO.SITE_GROUP_ID)
1489 AND SiteGroupMemberEO.CHILD_MEMBER_ID = l_site_id
1490 AND SiteGroupMemberEO.deleted_flag='N');
1491
1492
1493
1494
1495 x_site_hierar_tab := rrs_site_hierar_tab();
1496 if l_hierarchy_details.count > 0 then
1497 FOR i in l_hierarchy_details.First..l_hierarchy_details.Last LOOP
1498 x_site_hierar_tab.Extend();
1499 x_site_hierar_tab(i) := rrs_site_hierar_rec(l_hierarchy_details(i).hierarchy_name
1500 ,l_hierarchy_details(i).hierarchy_node_name
1501 ,l_hierarchy_details(i).parent_site_name
1502 );
1503 END LOOP;
1504 END IF;
1505
1506
1507 -- Relationships
1508
1509
1510 SELECT HzPuiRelationshipsEO.start_date,
1511 decode(to_char(HzPuiRelationshipsEO.end_date,'DD-MM-YYYY') , '31-12-4712', to_date(null), HzPuiRelationshipsEO.end_date) end_date,
1512 HzPuiRelationshipsEO.comments,
1513 subjectparty.party_name subject_party_name,
1514 objectparty.party_name object_party_name,
1515 reltype.role relationship_role,
1516 relationshiprolelu.description relationship_role_meaning
1517 BULK COLLECT
1518 INTO l_relationship_details
1519 FROM hz_relationships HzPuiRelationshipsEO,
1520 hz_relationship_types reltype,
1521 hz_parties subjectparty,
1522 hz_parties objectparty,
1523 fnd_lookup_values subjectpartytypelu,
1524 fnd_lookup_values objectpartytypelu,
1525 fnd_lookup_values relationshiprolelu
1526 WHERE HzPuiRelationshipsEO.subject_table_name = 'HZ_PARTIES'
1527 AND HzPuiRelationshipsEO.object_table_name = 'HZ_PARTIES'
1528 AND HzPuiRelationshipsEO.status IN ('A', 'I')
1529 AND HzPuiRelationshipsEO.subject_id = subjectparty.party_id
1530 AND HzPuiRelationshipsEO.object_id = objectparty.party_id
1531 AND HzPuiRelationshipsEO.relationship_type = reltype.relationship_type
1532 AND HzPuiRelationshipsEO.relationship_code = reltype.forward_rel_code
1533 AND HzPuiRelationshipsEO.subject_type = reltype.subject_type
1534 AND HzPuiRelationshipsEO.object_type = reltype.object_type
1535 AND subjectpartytypelu.view_application_id = 222
1536 AND subjectpartytypelu.lookup_type = 'PARTY_TYPE'
1537 AND subjectpartytypelu.language = userenv('LANG')
1538 AND subjectpartytypelu.lookup_code = HzPuiRelationshipsEO.subject_type
1539 AND objectpartytypelu.view_application_id = 222
1540 AND objectpartytypelu.lookup_type = 'PARTY_TYPE'
1541 AND objectpartytypelu.language = userenv('LANG')
1542 AND objectpartytypelu.lookup_code = HzPuiRelationshipsEO.object_type
1543 AND relationshiprolelu.view_application_id = 222
1544 AND relationshiprolelu.lookup_type = 'HZ_RELATIONSHIP_ROLE'
1545 AND relationshiprolelu.language = userenv('LANG')
1546 AND relationshiprolelu.lookup_code = reltype.role
1547 AND HzPuiRelationshipsEO.object_type = 'ORGANIZATION'
1548 and HzPuiRelationshipsEO.object_id = l_site_party_id
1549 and HzPuiRelationshipsEO.subject_type = 'ORGANIZATION'
1550 and (HzPuiRelationshipsEO.status = 'A'
1551 and (HzPuiRelationshipsEO.end_date is null or HzPuiRelationshipsEO.end_date >= trunc(sysdate)) );
1552
1553
1554 x_relationship_tab := rrs_relationship_tab();
1555 if l_relationship_details.count > 0 then
1556 FOR i in l_relationship_details.First..l_relationship_details.Last LOOP
1557 x_relationship_tab.Extend();
1558 x_relationship_tab(i) := rrs_relationship_rec( l_relationship_details(i).start_date
1559 ,l_relationship_details(i).end_date
1560 ,l_relationship_details(i).comments
1561 ,l_relationship_details(i).subject_party_name
1562 ,l_relationship_details(i).object_party_name
1563 ,l_relationship_details(i).relationship_role
1564 ,l_relationship_details(i).relationship_role_meaning
1565 );
1566 END LOOP;
1567 END IF;
1568
1569 /*
1570 dbms_output.put_line(chr(13) || chr(10));
1571 dbms_output.put_line('Printing Associations Details ');
1572 dbms_output.put_line('============================== ');
1573 dbms_output.put_line(chr(13) || chr(10));
1574
1575 if l_trade_area_groups_details.count > 0 then
1576 for i in l_trade_area_groups_details.First..l_trade_area_groups_details.Last LOOP
1577 dbms_output.put_line('Trade Area Group : '||chr(9)||chr(9)||chr(9)||x_trade_area_grp_tab(i).group_name);
1578 dbms_output.put_line('Primary Trade Area Group ? : '||chr(9)||chr(9)||x_trade_area_grp_tab(i).is_primary_flag);
1579 dbms_output.put_line('Trade Area Group Status : '||chr(9)||chr(9)||x_trade_area_grp_tab(i).status_code);
1580
1581 END LOOP;
1582 end if;
1583
1584 if l_cluster_name.count > 0 then
1585 for i in l_cluster_name.First..l_cluster_name.Last LOOP
1586 dbms_output.put_line('Site Asociated to Cluster : '||chr(9)||chr(9)||x_site_cluster_tab(i).cluster_name);
1587 END LOOP;
1588 end if;
1589
1590 if l_hierarchy_details.count > 0 then
1591 for i in l_hierarchy_details.First..l_hierarchy_details.Last LOOP
1592
1593 dbms_output.put_line('Hierarchy Name : '||chr(9)||chr(9)||chr(9)||x_site_hierar_tab(i).hierarchy_name);
1594 dbms_output.put_line('Hierarchy Node : '||chr(9)||chr(9)||chr(9)||x_site_hierar_tab(i).hierarchy_node_name);
1595
1596 END LOOP;
1597 end if;
1598
1599 if l_relationship_details.count > 0 then
1600 for i in l_relationship_details.First..l_relationship_details.Last LOOP
1601
1602 dbms_output.put_line(chr(13) || chr(10));
1603 dbms_output.put_line('Customer : '||chr(9)||chr(9)||chr(9)||chr(9)||x_relationship_tab(i).subject_party_name);
1604 dbms_output.put_line('Relationship Role : '||chr(9)||chr(9)||chr(9)||x_relationship_tab(i).relationship_role_meaning);
1605 dbms_output.put_line('Start Date : '||chr(9)||chr(9)||chr(9)||chr(9)||x_relationship_tab(i).start_date);
1606 dbms_output.put_line('End Date : '||chr(9)||chr(9)||chr(9)||chr(9)||x_relationship_tab(i).end_date);
1607 dbms_output.put_line('Comments : '||chr(9)||chr(9)||chr(9)||chr(9)||x_relationship_tab(i).comments);
1608
1609 END LOOP;
1610 end if;
1611 dbms_output.put_line(chr(13) || chr(10));
1612 dbms_output.put_line('Done Printing Associations Details ');
1613 dbms_output.put_line('============================== ');
1614 dbms_output.put_line(chr(13) || chr(10));
1615 */
1616
1617 end;
1618
1619
1620
1621 Procedure Get_site_contacts(
1622 p_site_id_num IN varchar2
1623 ,p_site_name IN varchar2 Default null
1624 ,x_party_site_address_tab OUT NOCOPY rrs_site_address_tab
1625 ,x_site_phone_tab OUT NOCOPY rrs_site_phone_tab
1626 ,x_site_email_tab OUT NOCOPY rrs_site_email_tab
1627 ,x_site_url_tab OUT NOCOPY rrs_site_url_tab
1628 ,x_site_person_tab OUT NOCOPY rrs_site_person_tab
1629 ) is
1630
1631 l_site_id number;
1632 l_site_party_id number;
1633
1634
1635 TYPE local_rrs_site_address_rec is RECORD (
1636 address1 hz_locations.address1%TYPE,
1637 address2 hz_locations.address2%TYPE,
1638 address3 hz_locations.address3%TYPE,
1639 address4 hz_locations.address4%TYPE,
1640 city hz_locations.city%TYPE,
1641 county hz_locations.county%TYPE,
1642 state hz_locations.state%TYPE,
1643 province hz_locations.province%TYPE,
1644 postal_code hz_locations.postal_code%TYPE,
1645 country hz_locations.country%TYPE,
1646 country_name fnd_territories_vl.territory_short_name%Type,
1647 address varchar2(1000),
1648 identifying_address_flag hz_party_sites.identifying_address_flag%Type,
1649 site_purpose ar_lookups.meaning%type --Bug 7871825
1650 );
1651 TYPE local_rrs_site_address_tab IS TABLE OF local_rrs_site_address_rec;
1652 l_site_party_dets local_rrs_site_address_tab;
1653
1654
1655 TYPE local_rrs_site_phone_rec is RECORD (
1656 CONTACT_POINT_ID HZ_CONTACT_POINTS.CONTACT_POINT_ID%Type,
1657 CONTACT_POINT_TYPE HZ_CONTACT_POINTS.CONTACT_POINT_TYPE%Type,
1658 STATUS HZ_CONTACT_POINTS.STATUS%Type,
1659 OWNER_TABLE_NAME HZ_CONTACT_POINTS.OWNER_TABLE_NAME%Type,
1660 OWNER_TABLE_ID HZ_CONTACT_POINTS.OWNER_TABLE_ID%Type,
1661 PRIMARY_FLAG HZ_CONTACT_POINTS.PRIMARY_FLAG%Type,
1662 ATTRIBUTE_CATEGORY HZ_CONTACT_POINTS.ATTRIBUTE_CATEGORY%Type,
1663 PHONE_CALLING_CALENDAR HZ_CONTACT_POINTS.PHONE_CALLING_CALENDAR%Type,
1664 LAST_CONTACT_DT_TIME HZ_CONTACT_POINTS.LAST_CONTACT_DT_TIME%Type,
1665 PHONE_PREFERRED_ORDER HZ_CONTACT_POINTS.PHONE_PREFERRED_ORDER%Type,
1666 PRIORITY_OF_USE_CODE HZ_CONTACT_POINTS.PRIORITY_OF_USE_CODE%Type,
1667 TELEPHONE_TYPE HZ_CONTACT_POINTS.TELEPHONE_TYPE%Type,
1668 TIME_ZONE HZ_CONTACT_POINTS.TIME_ZONE%Type,
1669 PHONE_TOUCH_TONE_TYPE_FLAG HZ_CONTACT_POINTS.PHONE_TOUCH_TONE_TYPE_FLAG%Type,
1670 PHONE_AREA_CODE HZ_CONTACT_POINTS.PHONE_AREA_CODE%Type,
1671 PHONE_COUNTRY_CODE HZ_CONTACT_POINTS.PHONE_COUNTRY_CODE%Type,
1672 PHONE_NUMBER HZ_CONTACT_POINTS.PHONE_NUMBER%Type,
1673 PHONE_EXTENSION HZ_CONTACT_POINTS.PHONE_EXTENSION%Type,
1674 PHONE_LINE_TYPE HZ_CONTACT_POINTS.PHONE_LINE_TYPE%Type,
1675 CONTENT_SOURCE_TYPE HZ_CONTACT_POINTS.CONTENT_SOURCE_TYPE%Type,
1676 RAW_PHONE_NUMBER HZ_CONTACT_POINTS.RAW_PHONE_NUMBER%Type,
1677 TIMEZONE_ID HZ_CONTACT_POINTS.TIMEZONE_ID%Type,
1678 TIMEZONE_NAME FND_TIMEZONES_VL.NAME%Type,
1679 CONTACT_POINT_PURPOSE HZ_CONTACT_POINTS.CONTACT_POINT_PURPOSE%Type,
1680 PRIMARY_BY_PURPOSE HZ_CONTACT_POINTS.PRIMARY_BY_PURPOSE%Type,
1681 TRANSPOSED_PHONE_NUMBER HZ_CONTACT_POINTS.TRANSPOSED_PHONE_NUMBER%Type,
1682 ACTUAL_CONTENT_SOURCE HZ_CONTACT_POINTS.ACTUAL_CONTENT_SOURCE%Type
1683 );
1684 TYPE local_rrs_site_phone_tab IS TABLE OF local_rrs_site_phone_rec;
1685 l_phone_details local_rrs_site_phone_tab;
1686
1687
1688 TYPE local_rrs_site_email_rec is RECORD (
1689 CONTACT_POINT_ID HZ_CONTACT_POINTS.CONTACT_POINT_ID%Type,
1690 CONTACT_POINT_TYPE HZ_CONTACT_POINTS.CONTACT_POINT_TYPE%Type,
1691 STATUS HZ_CONTACT_POINTS.STATUS%Type,
1692 OWNER_TABLE_NAME HZ_CONTACT_POINTS.OWNER_TABLE_NAME%Type,
1693 OWNER_TABLE_ID HZ_CONTACT_POINTS.OWNER_TABLE_ID%Type,
1694 PRIMARY_FLAG HZ_CONTACT_POINTS.PRIMARY_FLAG%Type,
1695 EMAIL_FORMAT HZ_CONTACT_POINTS.EMAIL_FORMAT%Type,
1696 EMAIL_ADDRESS HZ_CONTACT_POINTS.EMAIL_ADDRESS%Type,
1697 CONTACT_POINT_PURPOSE HZ_CONTACT_POINTS.CONTACT_POINT_PURPOSE%Type,
1698 PRIMARY_BY_PURPOSE HZ_CONTACT_POINTS.PRIMARY_BY_PURPOSE%Type,
1699 MEANING FND_LOOKUP_VALUES.MEANING%Type,
1700 ATTRIBUTE_CATEGORY HZ_CONTACT_POINTS.ATTRIBUTE_CATEGORY%Type,
1701 ACTUAL_CONTENT_SOURCE HZ_CONTACT_POINTS.ACTUAL_CONTENT_SOURCE%Type
1702 );
1703 TYPE local_rrs_site_email_tab IS TABLE OF local_rrs_site_email_rec;
1704 l_email_details local_rrs_site_email_tab;
1705
1706
1707 TYPE local_rrs_site_url_rec is RECORD (
1708 CONTACT_POINT_ID HZ_CONTACT_POINTS.CONTACT_POINT_ID%Type,
1709 CONTACT_POINT_TYPE HZ_CONTACT_POINTS.CONTACT_POINT_TYPE%Type,
1710 STATUS HZ_CONTACT_POINTS.STATUS%Type,
1711 OWNER_TABLE_NAME HZ_CONTACT_POINTS.OWNER_TABLE_NAME%Type,
1712 OWNER_TABLE_ID HZ_CONTACT_POINTS.OWNER_TABLE_ID%Type,
1713 PRIMARY_FLAG HZ_CONTACT_POINTS.PRIMARY_FLAG%Type,
1714 WEB_TYPE HZ_CONTACT_POINTS.WEB_TYPE%Type,
1715 URL HZ_CONTACT_POINTS.URL%Type,
1716 CONTENT_SOURCE_TYPE HZ_CONTACT_POINTS.CONTENT_SOURCE_TYPE%Type,
1717 APPLICATION_ID HZ_CONTACT_POINTS.APPLICATION_ID%Type,
1718 CONTACT_POINT_PURPOSE HZ_CONTACT_POINTS.CONTACT_POINT_PURPOSE%Type,
1719 PRIMARY_BY_PURPOSE HZ_CONTACT_POINTS.PRIMARY_BY_PURPOSE%Type,
1720 TRANSPOSED_PHONE_NUMBER HZ_CONTACT_POINTS.TRANSPOSED_PHONE_NUMBER%Type,
1721 ACTUAL_CONTENT_SOURCE HZ_CONTACT_POINTS.ACTUAL_CONTENT_SOURCE%Type,
1722 MEANING FND_LOOKUP_VALUES.MEANING%Type,
1723 ATTRIBUTE_CATEGORY HZ_CONTACT_POINTS.ATTRIBUTE_CATEGORY%Type
1724 );
1725 TYPE local_rrs_site_url_tab IS TABLE OF local_rrs_site_url_rec;
1726 l_url_details local_rrs_site_url_tab;
1727
1728
1729 TYPE local_rrs_site_person_rec is RECORD (
1730 relationship_id hz_relationships.relationship_id%Type,
1731 subject_type hz_relationships.subject_type%Type,
1732 object_id hz_relationships.object_id%Type,
1733 object_type hz_relationships.object_type%Type,
1734 object_table_name hz_relationships.object_table_name%Type,
1735 relationship_party_id hz_relationships.party_id%Type,
1736 relationship_type hz_relationships.relationship_type%Type,
1737 relationship_code hz_relationships.relationship_code%Type,
1738 start_date hz_relationships.start_date%Type,
1739 end_date hz_relationships.end_date%Type,
1740 comments hz_relationships.comments%Type,
1741 status hz_relationships.status%Type,
1742 actual_content_source hz_relationships.actual_content_source%Type,
1743 subject_party_name hz_parties.party_name%Type,
1744 subject_party_number hz_parties.party_number%Type,
1745 subject_party_known_as hz_parties.known_as%Type,
1746 object_party_name hz_parties.party_name%Type,
1747 object_party_number hz_parties.party_number%Type,
1748 object_party_known_as hz_parties.known_as%Type,
1749 relationship_type_id hz_relationship_types.relationship_type_id%Type,
1750 relationship_role hz_relationship_types.role%Type,
1751 subject_type_meaning fnd_lookup_values.meaning%Type,
1752 object_type_meaning fnd_lookup_values.meaning%Type,
1753 relationship_role_meaning fnd_lookup_values.description%Type
1754 );
1755 TYPE local_rrs_site_person_tab IS TABLE OF local_rrs_site_person_rec;
1756 l_person_details local_rrs_site_person_tab;
1757
1758
1759 begin
1760
1761 x_party_site_address_tab := rrs_site_address_tab();
1762
1763 begin
1764 Select RSB.SITE_ID,
1765 RSB.SITE_PARTY_ID
1766 INTO l_site_id,
1767 l_site_party_id
1768 FROM RRS_SITES_B RSB
1769 WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
1770 Exception
1771 When NO_DATA_FOUND Then
1772 raise_application_error(-20101, ' Check the Site ID please');
1773
1774 end;
1775
1776 IF l_site_party_id IS NOT NULL THEN
1777
1778 /*
1779 Commented this code to troubleshoot later as why country does not show up.
1780 SELECT ADDRESS1,
1781 ADDRESS2,
1782 ADDRESS3,
1783 ADDRESS4,
1784 NLS_UPPER(CITY) CITY,
1785 NLS_UPPER(COUNTY) COUNTY,
1786 NLS_UPPER(STATE) STATE,
1787 NLS_UPPER(PROVINCE) PROVINCE,
1788 NLS_UPPER(POSTAL_CODE) POSTAL_CODE,
1789 NLS_UPPER(hz_format_pub.get_tl_territory_name(COUNTRY)) COUNTRY,
1790 FTV.TERRITORY_SHORT_NAME COUNTRY_NAME,
1791 HZ_FORMAT_PUB.format_address(HL.location_id, null, null, ',' , null) as Address,
1792 HPS.IDENTIFYING_ADDRESS_FLAG
1793 BULK COLLECT
1794 INTO l_site_party_dets
1795 FROM HZ_LOCATIONS HL
1796 ,FND_TERRITORIES_VL FTV
1797 ,HZ_PARTY_SITES HPS
1798 WHERE COUNTRY = FTV.TERRITORY_CODE
1799 AND HL.LOCATION_ID = HPS.LOCATION_ID
1800 AND HPS.PARTY_ID = l_site_party_id;
1801
1802 */
1803 SELECT ADDRESS1,
1804 ADDRESS2,
1805 ADDRESS3,
1806 ADDRESS4,
1807 NLS_UPPER(CITY) CITY,
1808 NLS_UPPER(COUNTY) COUNTY,
1809 NLS_UPPER(STATE) STATE,
1810 NLS_UPPER(PROVINCE) PROVINCE,
1811 NLS_UPPER(POSTAL_CODE) POSTAL_CODE,
1812 COUNTRY,
1813 FTV.TERRITORY_SHORT_NAME COUNTRY_NAME,
1814 HZ_FORMAT_PUB.format_address(HL.location_id, null, null, ',' , null) as Address,
1815 HPS.IDENTIFYING_ADDRESS_FLAG,
1816 ( select AL.MEANING
1817 from AR_LOOKUPS AL
1818 where AL.LOOKUP_TYPE = 'PARTY_SITE_USE_CODE'
1819 AND AL.LOOKUP_CODE = PSU.SITE_USE_TYPE ) site_purpose
1820 BULK COLLECT
1821 INTO l_site_party_dets
1822 FROM HZ_LOCATIONS HL
1823 ,FND_TERRITORIES_VL FTV
1824 ,HZ_PARTY_SITES HPS
1825 , HZ_PARTY_SITE_USES PSU
1826 WHERE COUNTRY = FTV.TERRITORY_CODE
1827 AND HL.LOCATION_ID = HPS.LOCATION_ID
1828 AND PSU.PARTY_SITE_ID(+) = HPS.PARTY_SITE_ID
1829 AND PSU.STATUS(+) = 'A'
1830 AND HPS.PARTY_ID = l_site_party_id;
1831
1832 If l_site_party_dets.count > 0 then
1833
1834
1835 FOR i in l_site_party_dets.First..l_site_party_dets.Last LOOP
1836 x_party_site_address_tab.Extend();
1837 x_party_site_address_tab(i) := rrs_site_address_rec(l_site_party_dets(i).address1
1838 ,l_site_party_dets(i).address2
1839 ,l_site_party_dets(i).address3
1840 ,l_site_party_dets(i).address4
1841 ,l_site_party_dets(i).city
1842 ,l_site_party_dets(i).county
1843 ,l_site_party_dets(i).state
1844 ,l_site_party_dets(i).province
1845 ,l_site_party_dets(i).postal_code
1846 ,l_site_party_dets(i).country
1847 ,l_site_party_dets(i).country_name
1848 ,l_site_party_dets(i).address
1849 ,l_site_party_dets(i).identifying_address_flag
1850 ,l_site_party_dets(i).site_purpose --Bug 7871825
1851 ,NULL -- Geometry_source
1852 ,NULL -- Geometry_source_meaning
1853 ,NULL -- Longitude
1854 ,NULL -- Latitude
1855 );
1856 END LOOP;
1857
1858 END IF;
1859
1860 END IF;
1861
1862
1863 -- Fetching the Phone details
1864
1865 SELECT HzPuiContactPointPhoneEO.CONTACT_POINT_ID,
1866 HzPuiContactPointPhoneEO.CONTACT_POINT_TYPE,
1867 HzPuiContactPointPhoneEO.STATUS,
1868 HzPuiContactPointPhoneEO.OWNER_TABLE_NAME,
1869 HzPuiContactPointPhoneEO.OWNER_TABLE_ID,
1870 HzPuiContactPointPhoneEO.PRIMARY_FLAG,
1871 HzPuiContactPointPhoneEO.ATTRIBUTE_CATEGORY,
1872 HzPuiContactPointPhoneEO.PHONE_CALLING_CALENDAR,
1873 HzPuiContactPointPhoneEO.LAST_CONTACT_DT_TIME,
1874 HzPuiContactPointPhoneEO.PHONE_PREFERRED_ORDER,
1875 HzPuiContactPointPhoneEO.PRIORITY_OF_USE_CODE,
1876 HzPuiContactPointPhoneEO.TELEPHONE_TYPE,
1877 HzPuiContactPointPhoneEO.TIME_ZONE,
1878 HzPuiContactPointPhoneEO.PHONE_TOUCH_TONE_TYPE_FLAG,
1879 HzPuiContactPointPhoneEO.PHONE_AREA_CODE,
1880 HzPuiContactPointPhoneEO.PHONE_COUNTRY_CODE,
1881 HzPuiContactPointPhoneEO.PHONE_NUMBER,
1882 HzPuiContactPointPhoneEO.PHONE_EXTENSION,
1883 HzPuiContactPointPhoneEO.PHONE_LINE_TYPE,
1884 HzPuiContactPointPhoneEO.CONTENT_SOURCE_TYPE,
1885 HzPuiContactPointPhoneEO.RAW_PHONE_NUMBER,
1886 HzPuiContactPointPhoneEO.TIMEZONE_ID,
1887 Ftv.Name AS TIMEZONE_NAME,
1888 HzPuiContactPointPhoneEO.CONTACT_POINT_PURPOSE,
1889 HzPuiContactPointPhoneEO.PRIMARY_BY_PURPOSE,
1890 HzPuiContactPointPhoneEO.TRANSPOSED_PHONE_NUMBER,
1891 HzPuiContactPointPhoneEO.ACTUAL_CONTENT_SOURCE
1892 BULK COLLECT
1893 INTO l_phone_details
1894 FROM
1895 HZ_CONTACT_POINTS HzPuiContactPointPhoneEO
1896 ,FND_TIMEZONES_VL Ftv
1897 WHERE (CONTACT_POINT_TYPE = 'PHONE' and
1898 OWNER_TABLE_NAME = 'HZ_PARTIES' AND OWNER_TABLE_ID = l_site_party_id
1899 AND Ftv.ENABLED_FLAG(+) = 'Y'
1900 AND HzPuiContactPointPhoneEO.TIMEZONE_ID = Ftv.UPGRADE_TZ_ID(+)
1901 );
1902
1903 x_site_phone_tab := rrs_site_phone_tab();
1904 if l_phone_details.count > 0 then
1905 FOR i in l_phone_details.First..l_phone_details.Last LOOP
1906 x_site_phone_tab.Extend();
1907 x_site_phone_tab(i) := rrs_site_phone_rec(
1908 l_phone_details(i).CONTACT_POINT_ID
1909 ,l_phone_details(i).CONTACT_POINT_TYPE
1910 ,l_phone_details(i).STATUS
1911 ,l_phone_details(i).OWNER_TABLE_NAME
1912 ,l_phone_details(i).OWNER_TABLE_ID
1913 ,l_phone_details(i).PRIMARY_FLAG
1914 ,l_phone_details(i).ATTRIBUTE_CATEGORY
1915 ,l_phone_details(i).PHONE_CALLING_CALENDAR
1916 ,l_phone_details(i).LAST_CONTACT_DT_TIME
1917 ,l_phone_details(i).PHONE_PREFERRED_ORDER
1918 ,l_phone_details(i).PRIORITY_OF_USE_CODE
1919 ,l_phone_details(i).TELEPHONE_TYPE
1920 ,l_phone_details(i).TIME_ZONE
1921 ,l_phone_details(i).PHONE_TOUCH_TONE_TYPE_FLAG
1922 ,l_phone_details(i).PHONE_AREA_CODE
1923 ,l_phone_details(i).PHONE_COUNTRY_CODE
1924 ,l_phone_details(i).PHONE_NUMBER
1925 ,l_phone_details(i).PHONE_EXTENSION
1926 ,l_phone_details(i).PHONE_LINE_TYPE
1927 ,l_phone_details(i).CONTENT_SOURCE_TYPE
1928 ,l_phone_details(i).RAW_PHONE_NUMBER
1929 ,l_phone_details(i).TIMEZONE_ID
1930 ,l_phone_details(i).TIMEZONE_NAME
1931 ,l_phone_details(i).CONTACT_POINT_PURPOSE
1932 ,l_phone_details(i).PRIMARY_BY_PURPOSE
1933 ,l_phone_details(i).TRANSPOSED_PHONE_NUMBER
1934 ,l_phone_details(i).ACTUAL_CONTENT_SOURCE
1935 );
1936 END LOOP;
1937 END IF;
1938
1939
1940 -- Fetching email details.
1941
1942 SELECT HzPuiContactPointEmailEO.CONTACT_POINT_ID,
1943 HzPuiContactPointEmailEO.CONTACT_POINT_TYPE,
1944 HzPuiContactPointEmailEO.STATUS,
1945 HzPuiContactPointEmailEO.OWNER_TABLE_NAME,
1946 HzPuiContactPointEmailEO.OWNER_TABLE_ID,
1947 HzPuiContactPointEmailEO.PRIMARY_FLAG,
1948 HzPuiContactPointEmailEO.EMAIL_FORMAT,
1949 HzPuiContactPointEmailEO.EMAIL_ADDRESS,
1950 HzPuiContactPointEmailEO.CONTACT_POINT_PURPOSE,
1951 HzPuiContactPointEmailEO.PRIMARY_BY_PURPOSE,
1952 AL.MEANING USAGE,
1953 HzPuiContactPointEmailEO.ATTRIBUTE_CATEGORY,
1954 HzPuiContactPointEmailEO.ACTUAL_CONTENT_SOURCE
1955 BULK COLLECT
1956 INTO l_email_details
1957 FROM HZ_CONTACT_POINTS HzPuiContactPointEmailEO,
1958 fnd_lookup_values al
1959 WHERE HzPuiContactPointEmailEO.CONTACT_POINT_TYPE ='EMAIL'
1960 and HzPuiContactPointEmailEO.STATUS = 'A'
1961 and al.view_application_id(+) = 222
1962 and al.language(+) = userenv('LANG')
1963 and al.lookup_type(+) = 'CONTACT_POINT_PURPOSE'
1964 and HzPuiContactPointEmailEO.CONTACT_POINT_PURPOSE = al.LOOKUP_CODE(+)
1965 and (HzPuiContactPointEmailEO.OWNER_TABLE_NAME = 'HZ_PARTIES'
1966 AND HzPuiContactPointEmailEO.OWNER_TABLE_ID = l_site_party_id)
1967 ORDER BY HzPuiContactPointEmailEO.PRIMARY_FLAG DESC, USAGE NULLS LAST, HzPuiContactPointEmailEO.PRIMARY_BY_PURPOSE DESC;
1968
1969 x_site_email_tab := rrs_site_email_tab();
1970 if l_email_details.count > 0 then
1971 FOR i in l_email_details.First..l_email_details.Last LOOP
1972 x_site_email_tab.Extend();
1973 x_site_email_tab(i) := rrs_site_email_rec(
1974 l_email_details(i).CONTACT_POINT_ID
1975 ,l_email_details(i).CONTACT_POINT_TYPE
1976 ,l_email_details(i).STATUS
1977 ,l_email_details(i).OWNER_TABLE_NAME
1978 ,l_email_details(i).OWNER_TABLE_ID
1979 ,l_email_details(i).PRIMARY_FLAG
1980 ,l_email_details(i).EMAIL_FORMAT
1981 ,l_email_details(i).EMAIL_ADDRESS
1982 ,l_email_details(i).CONTACT_POINT_PURPOSE
1983 ,l_email_details(i).PRIMARY_BY_PURPOSE
1984 ,l_email_details(i).MEANING
1985 ,l_email_details(i).ATTRIBUTE_CATEGORY
1986 ,l_email_details(i).ACTUAL_CONTENT_SOURCE
1987 );
1988 END LOOP;
1989 END IF;
1990 -- Fetching URL details.
1991
1992
1993 SELECT HzPuiContactPointUrlEO.CONTACT_POINT_ID,
1994 HzPuiContactPointUrlEO.CONTACT_POINT_TYPE,
1995 HzPuiContactPointUrlEO.STATUS,
1996 HzPuiContactPointUrlEO.OWNER_TABLE_NAME,
1997 HzPuiContactPointUrlEO.OWNER_TABLE_ID,
1998 HzPuiContactPointUrlEO.PRIMARY_FLAG,
1999 HzPuiContactPointUrlEO.WEB_TYPE,
2000 HzPuiContactPointUrlEO.URL,
2001 HzPuiContactPointUrlEO.CONTENT_SOURCE_TYPE,
2002 HzPuiContactPointUrlEO.APPLICATION_ID,
2003 HzPuiContactPointUrlEO.CONTACT_POINT_PURPOSE,
2004 HzPuiContactPointUrlEO.PRIMARY_BY_PURPOSE,
2005 HzPuiContactPointUrlEO.TRANSPOSED_PHONE_NUMBER,
2006 HzPuiContactPointUrlEO.ACTUAL_CONTENT_SOURCE,
2007 AL.MEANING USAGE,
2008 HzPuiContactPointUrlEO.ATTRIBUTE_CATEGORY
2009 BULK COLLECT
2010 INTO l_url_details
2011 FROM HZ_CONTACT_POINTS HzPuiContactPointUrlEO,
2012 fnd_lookup_values al
2013 WHERE HzPuiContactPointUrlEO.CONTACT_POINT_TYPE ='WEB'
2014 and HzPuiContactPointUrlEO.STATUS = 'A'
2015 and al.view_application_id(+) = 222
2016 and al.language(+) = userenv('LANG')
2017 and al.lookup_type(+) = 'CONTACT_POINT_PURPOSE_WEB'
2018 and HzPuiContactPointUrlEO.CONTACT_POINT_PURPOSE = al.LOOKUP_CODE(+)
2019 and (HzPuiContactPointUrlEO.OWNER_TABLE_NAME = 'HZ_PARTIES'
2020 AND HzPuiContactPointUrlEO.OWNER_TABLE_ID = l_site_party_id)
2021 ORDER BY HzPuiContactPointUrlEO.PRIMARY_FLAG DESC, USAGE NULLS LAST, HzPuiContactPointUrlEO.PRIMARY_BY_PURPOSE DESC;
2022
2023 x_site_url_tab := rrs_site_url_tab();
2024 if l_url_details.count > 0 then
2025 FOR i in l_url_details.First..l_url_details.Last LOOP
2026 x_site_url_tab.Extend();
2027 x_site_url_tab(i) := rrs_site_url_rec(
2028 l_url_details(i).CONTACT_POINT_ID
2029 ,l_url_details(i).CONTACT_POINT_TYPE
2030 ,l_url_details(i).STATUS
2031 ,l_url_details(i).OWNER_TABLE_NAME
2032 ,l_url_details(i).OWNER_TABLE_ID
2033 ,l_url_details(i).PRIMARY_FLAG
2034 ,l_url_details(i).WEB_TYPE
2035 ,l_url_details(i).URL
2036 ,l_url_details(i).CONTENT_SOURCE_TYPE
2037 ,l_url_details(i).APPLICATION_ID
2038 ,l_url_details(i).CONTACT_POINT_PURPOSE
2039 ,l_url_details(i).PRIMARY_BY_PURPOSE
2040 ,l_url_details(i).TRANSPOSED_PHONE_NUMBER
2041 ,l_url_details(i).ACTUAL_CONTENT_SOURCE
2042 ,l_url_details(i).MEANING
2043 ,l_url_details(i).ATTRIBUTE_CATEGORY
2044 );
2045 END LOOP;
2046 END IF;
2047 -- Fetching the Person details
2048
2049 SELECT HzPuiRelationshipsEO.relationship_id,
2050 HzPuiRelationshipsEO.subject_type,
2051 HzPuiRelationshipsEO.object_id,
2052 HzPuiRelationshipsEO.object_type,
2053 HzPuiRelationshipsEO.object_table_name,
2054 HzPuiRelationshipsEO.party_id relationship_party_id,
2055 HzPuiRelationshipsEO.relationship_type,
2056 HzPuiRelationshipsEO.relationship_code,
2057 HzPuiRelationshipsEO.start_date,
2058 decode(to_char(HzPuiRelationshipsEO.end_date,'DD-MM-YYYY') ,
2059 '31-12-4712', to_date(null), --to_date to avoid xml.17 issue
2060 HzPuiRelationshipsEO.end_date) end_date,
2061 HzPuiRelationshipsEO.comments,
2062 HzPuiRelationshipsEO.status,
2063 HzPuiRelationshipsEO.actual_content_source,
2064 subjectparty.party_name subject_party_name,
2065 subjectparty.party_number subject_party_number,
2066 subjectparty.known_as subject_party_known_as,
2067 objectparty.party_name object_party_name,
2068 objectparty.party_number object_party_number,
2069 objectparty.known_as object_party_known_as,
2070 reltype.relationship_type_id,
2071 reltype.role relationship_role,
2072 subjectpartytypelu.meaning subject_type_meaning,
2073 objectpartytypelu.meaning object_type_meaning,
2074 relationshiprolelu.description relationship_role_meaning
2075 BULK COLLECT
2076 INTO l_person_details
2077 FROM hz_relationships HzPuiRelationshipsEO,
2078 hz_relationship_types reltype,
2079 hz_parties subjectparty,
2080 hz_parties objectparty,
2081 fnd_lookup_values subjectpartytypelu,
2082 fnd_lookup_values objectpartytypelu,
2083 fnd_lookup_values relationshiprolelu
2084 WHERE HzPuiRelationshipsEO.subject_table_name = 'HZ_PARTIES'
2085 AND HzPuiRelationshipsEO.object_table_name = 'HZ_PARTIES'
2086 AND HzPuiRelationshipsEO.status IN ('A', 'I')
2087 AND HzPuiRelationshipsEO.subject_id = subjectparty.party_id
2088 AND HzPuiRelationshipsEO.object_id = objectparty.party_id
2089 AND HzPuiRelationshipsEO.relationship_type = reltype.relationship_type
2090 AND HzPuiRelationshipsEO.relationship_code = reltype.forward_rel_code
2091 AND HzPuiRelationshipsEO.subject_type = reltype.subject_type
2092 AND HzPuiRelationshipsEO.object_type = reltype.object_type
2093 AND subjectpartytypelu.view_application_id = 222
2094 AND subjectpartytypelu.lookup_type = 'PARTY_TYPE'
2095 AND subjectpartytypelu.language = userenv('LANG')
2096 AND subjectpartytypelu.lookup_code = HzPuiRelationshipsEO.subject_type
2097 AND objectpartytypelu.view_application_id = 222
2098 AND objectpartytypelu.lookup_type = 'PARTY_TYPE'
2099 AND objectpartytypelu.language = userenv('LANG')
2100 AND objectpartytypelu.lookup_code = HzPuiRelationshipsEO.object_type
2101 AND relationshiprolelu.view_application_id = 222
2102 AND relationshiprolelu.lookup_type = 'HZ_RELATIONSHIP_ROLE'
2103 AND relationshiprolelu.language = userenv('LANG')
2104 AND relationshiprolelu.lookup_code = reltype.role
2105 AND (HzPuiRelationshipsEO.object_type = 'ORGANIZATION'
2106 and HzPuiRelationshipsEO.object_id = l_site_party_id
2107 and HzPuiRelationshipsEO.subject_type = 'PERSON'
2108 and (HzPuiRelationshipsEO.status = 'A'
2109 and (end_date is null or end_date >= trunc(sysdate)) ));
2110
2111 x_site_person_tab := rrs_site_person_tab();
2112 if l_person_details.count > 0 then
2113 FOR i in l_person_details.First..l_person_details.Last LOOP
2114 x_site_person_tab.Extend();
2115
2116 x_site_person_tab(i) := rrs_site_person_rec(
2117 l_person_details(i).relationship_id
2118 ,l_person_details(i).subject_type
2119 ,l_person_details(i).object_id
2120 ,l_person_details(i).object_type
2121 ,l_person_details(i).object_table_name
2122 ,l_person_details(i).relationship_party_id
2123 ,l_person_details(i).relationship_type
2124 ,l_person_details(i).relationship_code
2125 ,l_person_details(i).start_date
2126 ,l_person_details(i).end_date
2127 ,l_person_details(i).comments
2128 ,l_person_details(i).status
2129 ,l_person_details(i).actual_content_source
2130 ,l_person_details(i).subject_party_name
2131 ,l_person_details(i).subject_party_number
2132 ,l_person_details(i).subject_party_known_as
2133 ,l_person_details(i).object_party_name
2134 ,l_person_details(i).object_party_number
2135 ,l_person_details(i).object_party_known_as
2136 ,l_person_details(i).relationship_type_id
2137 ,l_person_details(i).relationship_role
2138 ,l_person_details(i).subject_type_meaning
2139 ,l_person_details(i).object_type_meaning
2140 ,l_person_details(i).relationship_role_meaning
2141 );
2142
2143 END LOOP;
2144 END IF;
2145 /*
2146 dbms_output.put_line(chr(13) || chr(10));
2147 dbms_output.put_line('Printing Phone Details ');
2148 dbms_output.put_line('============================== ');
2149 dbms_output.put_line(chr(13) || chr(10));
2150
2151 If l_phone_details.count > 0 then
2152 for i in l_phone_details.First..l_phone_details.Last Loop
2153
2154 dbms_output.put_line('Contact Point ID : '||chr(9)||chr(9)||l_phone_details(i).CONTACT_POINT_ID);
2155 dbms_output.put_line('Contact Point Type : '||chr(9)||chr(9)||l_phone_details(i).CONTACT_POINT_TYPE);
2156 dbms_output.put_line('Status: '||chr(9)||chr(9)||chr(9)||l_phone_details(i).STATUS);
2157 dbms_output.put_line('Owner Table Name : '||chr(9)||chr(9)||l_phone_details(i).OWNER_TABLE_NAME);
2158 dbms_output.put_line('Owner Table ID : '||chr(9)||chr(9)||l_phone_details(i).OWNER_TABLE_ID);
2159 dbms_output.put_line('Is this Primary Phone : '||chr(9)||l_phone_details(i).PRIMARY_FLAG);
2160 dbms_output.put_line('Attribute Category : '||chr(9)||chr(9)||l_phone_details(i).ATTRIBUTE_CATEGORY);
2161 dbms_output.put_line('Phone Calling Calendar : '||chr(9)||chr(9)||l_phone_details(i).PHONE_CALLING_CALENDAR);
2162 dbms_output.put_line('Last Contact Date Time : '||chr(9)||chr(9)||l_phone_details(i).LAST_CONTACT_DT_TIME);
2163 dbms_output.put_line('Phone Preferred Order : '||chr(9)||chr(9)||l_phone_details(i).PHONE_PREFERRED_ORDER);
2164 dbms_output.put_line('Priority of Use Code : '||chr(9)||chr(9)||l_phone_details(i).PRIORITY_OF_USE_CODE);
2165 dbms_output.put_line('Type of Phone : '||chr(9)||chr(9)||chr(9)||l_phone_details(i).TELEPHONE_TYPE);
2166 dbms_output.put_line('Time Zone : '||chr(9)||chr(9)||chr(9)||l_phone_details(i).TIME_ZONE);
2167 dbms_output.put_line('Phone Touch Tone Type Flag : '||chr(9)||l_phone_details(i).PHONE_TOUCH_TONE_TYPE_FLAG);
2168 dbms_output.put_line('Phone Area Code : '||chr(9)||chr(9)||l_phone_details(i).PHONE_AREA_CODE);
2169 dbms_output.put_line('Phone Country Code : '||chr(9)||chr(9)||l_phone_details(i).PHONE_COUNTRY_CODE);
2170 dbms_output.put_line('Phone Number : '||chr(9)||chr(9)||chr(9)||l_phone_details(i).PHONE_NUMBER);
2171 dbms_output.put_line('Extension : '||chr(9)||chr(9)||chr(9)||l_phone_details(i).PHONE_EXTENSION);
2172 dbms_output.put_line('Phone Line Type : '||chr(9)||chr(9)||l_phone_details(i).PHONE_LINE_TYPE);
2173 dbms_output.put_line('Content Source Type '||chr(9)||chr(9)||l_phone_details(i).CONTENT_SOURCE_TYPE);
2174 dbms_output.put_line('Raw Phone Number : '||chr(9)||chr(9)||l_phone_details(i).RAW_PHONE_NUMBER);
2175 dbms_output.put_line('Time zone ID : '||chr(9)||chr(9)||chr(9)||l_phone_details(i).TIMEZONE_ID);
2176 dbms_output.put_line('Purpose of Phone : '||chr(9)||chr(9)||l_phone_details(i).CONTACT_POINT_PURPOSE);
2177 dbms_output.put_line('Primary by Purpose : '||chr(9)||chr(9)||l_phone_details(i).PRIMARY_BY_PURPOSE);
2178 dbms_output.put_line('Transposed Phone Number : '||chr(9)||l_phone_details(i).TRANSPOSED_PHONE_NUMBER);
2179 dbms_output.put_line('Actual Content Source : '||chr(9)||l_phone_details(i).ACTUAL_CONTENT_SOURCE);
2180 dbms_output.put_line(chr(13) || chr(10));
2181
2182 End Loop;
2183 End if;
2184 dbms_output.put_line(chr(13) || chr(10));
2185 dbms_output.put_line('Done Printing Phone Details ');
2186 dbms_output.put_line('============================== ');
2187 dbms_output.put_line(chr(13) || chr(10));
2188
2189 dbms_output.put_line(chr(13) || chr(10));
2190 dbms_output.put_line('Printing Email Details ');
2191 dbms_output.put_line('============================== ');
2192 dbms_output.put_line(chr(13) || chr(10));
2193
2194 If l_email_details.count > 0 then
2195 for i in l_email_details.First..l_email_details.Last Loop
2196
2197 dbms_output.put_line('Contact Point ID : '||chr(9)||chr(9)||x_site_email_tab(i).CONTACT_POINT_ID);
2198 dbms_output.put_line('Type of Email : '||chr(9)||chr(9)||x_site_email_tab(i).CONTACT_POINT_TYPE);
2199 dbms_output.put_line('Status : '||chr(9)||chr(9)||chr(9)||x_site_email_tab(i).STATUS);
2200 dbms_output.put_line('Owner Table Name : '||chr(9)||chr(9)||x_site_email_tab(i).OWNER_TABLE_NAME);
2201 dbms_output.put_line('Owner Table Id : '||chr(9)||chr(9)||x_site_email_tab(i).OWNER_TABLE_ID);
2202 dbms_output.put_line('Is this Primary Email : '||chr(9)||x_site_email_tab(i).PRIMARY_FLAG);
2203 dbms_output.put_line('Email Format : '||chr(9)||chr(9)||chr(9)||x_site_email_tab(i).EMAIL_FORMAT);
2204 dbms_output.put_line('Email Address : '||chr(9)||chr(9)||x_site_email_tab(i).EMAIL_ADDRESS);
2205 dbms_output.put_line('Purpose : '||chr(9)||chr(9)||chr(9)||x_site_email_tab(i).CONTACT_POINT_PURPOSE);
2206 dbms_output.put_line('Primary by Purpose : '||chr(9)||chr(9)||x_site_email_tab(i).PRIMARY_BY_PURPOSE);
2207 dbms_output.put_line('Usage : '||chr(9)||chr(9)||chr(9)||x_site_email_tab(i).MEANING);
2208 dbms_output.put_line('Attrbute Category : '||chr(9)||chr(9)||x_site_email_tab(i).ATTRIBUTE_CATEGORY);
2209 dbms_output.put_line('Actual Content Source '||chr(9)||chr(9)||x_site_email_tab(i).ACTUAL_CONTENT_SOURCE);
2210 dbms_output.put_line(chr(13) || chr(10));
2211
2212 End Loop;
2213 end if;
2214 dbms_output.put_line(chr(13) || chr(10));
2215 dbms_output.put_line('Done Printing Email Details ');
2216 dbms_output.put_line('============================== ');
2217 dbms_output.put_line(chr(13) || chr(10));
2218 dbms_output.put_line(chr(13) || chr(10));
2219 dbms_output.put_line('Printing URL Details ');
2220 dbms_output.put_line('============================== ');
2221 dbms_output.put_line(chr(13) || chr(10));
2222 If l_url_details.count > 0 then
2223 for i in l_url_details.First..l_url_details.Last Loop
2224
2225 dbms_output.put_line('Contact Point ID : '||chr(9)||chr(9)||x_site_url_tab(i).CONTACT_POINT_ID);
2226 dbms_output.put_line('Contact Point Type : '||chr(9)||chr(9)||x_site_url_tab(i).CONTACT_POINT_TYPE);
2227 dbms_output.put_line('Status : '||chr(9)||chr(9)||chr(9)||x_site_url_tab(i).STATUS);
2228 dbms_output.put_line('Owner Table Name : '||chr(9)||chr(9)||x_site_url_tab(i).OWNER_TABLE_NAME);
2229 dbms_output.put_line('Owner Table ID : '||chr(9)||chr(9)||x_site_url_tab(i).OWNER_TABLE_ID);
2230 dbms_output.put_line('Is this Primary URL : '||chr(9)||chr(9)||x_site_url_tab(i).PRIMARY_FLAG);
2231 dbms_output.put_line('Type : '||chr(9)||chr(9)||chr(9)||chr(9)||x_site_url_tab(i).WEB_TYPE);
2232 dbms_output.put_line('URL : '||chr(9)||chr(9)||chr(9)||chr(9)||x_site_url_tab(i).URL);
2233 dbms_output.put_line('CONTENT SOURCE TYPE : '||chr(9)||chr(9)||x_site_url_tab(i).CONTENT_SOURCE_TYPE);
2234 dbms_output.put_line('APPLICATioN ID : '||chr(9)||chr(9)||x_site_url_tab(i).APPLICATION_ID);
2235 dbms_output.put_line('CONTACT POINT PURPOSE : '||chr(9)||x_site_url_tab(i).CONTACT_POINT_PURPOSE);
2236 dbms_output.put_line('PRIMARY BY PURPOSE : '||chr(9)||chr(9)||x_site_url_tab(i).PRIMARY_BY_PURPOSE);
2237 dbms_output.put_line('TRANSPOSED PHONE NUMBER : '||chr(9)||x_site_url_tab(i).TRANSPOSED_PHONE_NUMBER);
2238 dbms_output.put_line('ACTUAL CONTENT SOURCE : '||chr(9)||x_site_url_tab(i).ACTUAL_CONTENT_SOURCE);
2239 dbms_output.put_line('USAGE : '||chr(9)||chr(9)||chr(9)||x_site_url_tab(i).MEANING);
2240 dbms_output.put_line('ATTRIBUTE CATEGORY : '||chr(9)||chr(9)||x_site_url_tab(i).ATTRIBUTE_CATEGORY);
2241 dbms_output.put_line(chr(13) || chr(10));
2242
2243 End Loop;
2244 End If;
2245 dbms_output.put_line(chr(13) || chr(10));
2246 dbms_output.put_line('Done Printing URL Details ');
2247 dbms_output.put_line('============================== ');
2248 dbms_output.put_line(chr(13) || chr(10));
2249
2250 dbms_output.put_line(chr(13) || chr(10));
2251 dbms_output.put_line('Printing Person Details ');
2252 dbms_output.put_line('============================== ');
2253 dbms_output.put_line(chr(13) || chr(10));
2254
2255
2256 If l_person_details.count > 0 then
2257 for i in l_person_details.First..l_person_details.Last Loop
2258
2259 dbms_output.put_line('Relation ID : '||chr(9)||chr(9)||chr(9)||x_site_person_tab(i).relationship_id);
2260 dbms_output.put_line('Subject Type : '||chr(9)||chr(9)||chr(9)||x_site_person_tab(i).subject_type);
2261 dbms_output.put_line('Object ID : '||chr(9)||chr(9)||chr(9)||x_site_person_tab(i).object_id);
2262 dbms_output.put_line('Object Type : '||chr(9)||chr(9)||chr(9)||x_site_person_tab(i).object_type);
2263 dbms_output.put_line('Object Table Name : '||chr(9)||chr(9)||x_site_person_tab(i).object_table_name);
2264 dbms_output.put_line('RelationShip Party ID : '||chr(9)||x_site_person_tab(i).relationship_party_id);
2265 dbms_output.put_line('RelationShip Type : '||chr(9)||chr(9)||x_site_person_tab(i).relationship_type);
2266 dbms_output.put_line('RelationShip Code : '||chr(9)||chr(9)||x_site_person_tab(i).relationship_code);
2267 dbms_output.put_line('Start Date : '||chr(9)||chr(9)||chr(9)||x_site_person_tab(i).start_date);
2268 dbms_output.put_line('End Date : '||chr(9)||chr(9)||chr(9)||x_site_person_tab(i).end_date);
2269 dbms_output.put_line('Comments : '||chr(9)||chr(9)||chr(9)||x_site_person_tab(i).comments);
2270 dbms_output.put_line('Status : '||chr(9)||chr(9)||chr(9)||x_site_person_tab(i).status);
2271 dbms_output.put_line('Actual Content Source : '||chr(9)||x_site_person_tab(i).actual_content_source);
2272 dbms_output.put_line('Subject Party Name : '||chr(9)||chr(9)||x_site_person_tab(i).subject_party_name);
2273 dbms_output.put_line('Subject Party Number : '||chr(9)||chr(9)||x_site_person_tab(i).subject_party_number);
2274 dbms_output.put_line('Subject Party Known As : '||chr(9)||chr(9)||x_site_person_tab(i).subject_party_known_as);
2275 dbms_output.put_line('Object Party Name : '||chr(9)||chr(9)||x_site_person_tab(i).object_party_name);
2276 dbms_output.put_line('Object Party Number : '||chr(9)||chr(9)||x_site_person_tab(i).object_party_number);
2277 dbms_output.put_line('Object Party Known As : '||chr(9)||chr(9)||x_site_person_tab(i).object_party_known_as);
2278 dbms_output.put_line('RelationShip Type ID : '||chr(9)||chr(9)||x_site_person_tab(i).relationship_type_id);
2279 dbms_output.put_line('RelationShip Role : '||chr(9)||chr(9)||x_site_person_tab(i).relationship_role);
2280 dbms_output.put_line('Subject Type Meaning : '||chr(9)||chr(9)||x_site_person_tab(i).subject_type_meaning);
2281 dbms_output.put_line('Object Type Meaning : '||chr(9)||chr(9)||x_site_person_tab(i).object_type_meaning);
2282 dbms_output.put_line('RelationShip Role Meaning : '||chr(9)||x_site_person_tab(i).relationship_role_meaning);
2283 dbms_output.put_line(chr(13) || chr(10));
2284
2285 End Loop;
2286 End If;
2287 dbms_output.put_line(chr(13) || chr(10));
2288 dbms_output.put_line('Done Printing Person Details ');
2289 dbms_output.put_line('============================== ');
2290 dbms_output.put_line(chr(13) || chr(10));
2291 */
2292 end;
2293
2294 Procedure Get_site_attachments(
2295 p_site_id_num IN varchar2
2296 ,p_site_name IN varchar2 Default null
2297 ,x_site_attachment_tab OUT NOCOPY rrs_site_attachment_tab
2298 ) is
2299
2300 l_site_id number;
2301 i number;
2302
2303 TYPE local_rrs_site_attachment_rec is RECORD (
2304 Last_update_date fnd_attached_documents.last_update_date%TYPE,
2305 Last_updated_by_name fnd_user.user_name%TYPE,
2306 Entity_name fnd_attached_documents.Entity_name%TYPE,
2307 site_id fnd_attached_documents.pk1_value%TYPE,
2308 datatype_id fnd_documents.datatype_id%TYPE,
2309 datatype_name fnd_document_datatypes.user_name%TYPE,
2310 description fnd_documents_tl.description%TYPE,
2311 file_name fnd_documents_tl.file_name%TYPE,
2312 dm_type fnd_documents.dm_type%TYPE,
2313 dm_node fnd_documents.dm_node%TYPE,
2314 dm_folder_path fnd_documents.dm_folder_path%TYPE,
2315 data_object_code fnd_document_entities.data_object_code%TYPE,
2316 document_entity_id fnd_document_entities.document_entity_id%TYPE,
2317 category_id fnd_attached_documents.category_id%TYPE,
2318 attachment_category_name fnd_document_categories_tl.user_name%TYPE,
2319 status fnd_attached_documents.status%TYPE,
2320 attached_by_name fnd_user.user_name%TYPE,
2321 file_name_sort fnd_documents.URL%TYPE,
2322 usage_type fnd_documents.usage_type%TYPE,
2323 security_type fnd_documents.security_type%TYPE,
2324 publish_flag fnd_documents.publish_flag%TYPE,
2325 cat_id_query fnd_document_categories_tl.category_id%TYPE,
2326 seq_num fnd_attached_documents.seq_num%TYPE,
2327 url fnd_documents.URL%TYPE,
2328 title fnd_documents_tl.title%TYPE
2329 );
2330 TYPE local_rrs_site_attachment_tab IS TABLE OF local_rrs_site_attachment_rec;
2331 l_attachment_details local_rrs_site_attachment_tab;
2332
2333 begin
2334
2335 begin
2336 Select RSB.SITE_ID
2337 INTO l_site_id
2338 FROM RRS_SITES_B RSB
2339 WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
2340 Exception
2341 When NO_DATA_FOUND Then
2342 raise_application_error(-20101, ' Check the Site ID please');
2343
2344 end;
2345
2346
2347
2348
2349 SELECT distinct
2350 ad.LAST_UPDATE_DATE,
2351 u.USER_NAME LAST_UPDATED_BY_NAME,
2352 ad.ENTITY_NAME,
2353 ad.pk1_value,
2354 d.DATATYPE_ID,
2355 d.DATATYPE_NAME,
2356 d.DESCRIPTION,
2357 decode(d.FILE_NAME, null, (select message_text from fnd_new_messages
2358 where message_name = 'FND_UNDEFINED' and application_id = 0
2359 and language_code = userenv('LANG')), d.FILE_NAME) FILE_NAME,
2360 d.dm_type,
2361 d.dm_node,
2362 d.dm_folder_path,
2363 e.DATA_OBJECT_CODE,
2364 e.DOCUMENT_ENTITY_ID,
2365 -- 'ALLOW_ATTACH_UPDATE' ALLOW_ATTACH_UPDATE,
2366 -- 'ALLOW_ATTACH_DELETE' ALLOW_ATTACH_DELETE,
2367 ad.category_id category_id,
2368 cl.user_name attachment_category_name,
2369 ad.status,
2370 (select u1.user_name from fnd_user u1 where u1.user_id=ad.CREATED_BY) ATTACHED_BY_NAME,
2371 decode(d.datatype_id, 5, nvl(d.title,d.description)||'('||substr(d.URL, 1, least(length(d.URL),15))||'...)',
2372 decode(d.datatype_id, 6, nvl(d.title, d.file_name), decode(D.TITLE, null, (select message_text from fnd_new_messages where
2373 message_name = 'FND_UNDEFINED' and application_id = 0 and language_code = userenv('LANG')), D.TITLE))) FILE_NAME_SORT,
2374 d.usage_type,
2375 d.security_type,
2376 d.publish_flag,
2377 cl.category_id cat_id_query,
2378 ad.seq_num,
2379 d.URL,
2380 d.TITLE
2381 BULK COLLECT
2382 INTO l_attachment_details
2383 FROM FND_DOCUMENTS_VL d,
2384 FND_ATTACHED_DOCUMENTS ad,
2385 FND_DOCUMENT_ENTITIES e,
2386 FND_USER u,
2387 FND_DOCUMENT_CATEGORIES_TL cl,
2388 FND_DM_NODES node
2389 WHERE ad.DOCUMENT_ID = d.DOCUMENT_ID
2390 and ad.ENTITY_NAME = e.DATA_OBJECT_CODE(+)
2391 and ad.LAST_UPDATED_BY = u.USER_ID(+)
2392 and cl.language = userenv('LANG')
2393 and cl.category_id = nvl(ad.category_id, d.category_id)
2394 and d.dm_node = node.node_id(+)
2395 and ad.entity_name = 'RRS_SITE_ATTACHMENTS'
2396 and ad.pk1_value = l_site_id
2397 and cl.category_id in (1,1)
2398 and d.datatype_id in (6,2,1,5)
2399 AND (d.SECURITY_TYPE=4 OR d.PUBLISH_FLAG='Y')
2400 ORDER BY seq_num;
2401
2402
2403 x_site_attachment_tab := rrs_site_attachment_tab();
2404 if l_attachment_details.count > 0 then
2405 FOR i in l_attachment_details.First..l_attachment_details.Last LOOP
2406 x_site_attachment_tab.Extend();
2407 x_site_attachment_tab(i) := rrs_site_attachment_rec(
2408 l_attachment_details(i).LAST_UPDATE_DATE
2409 ,l_attachment_details(i).LAST_UPDATED_BY_NAME
2410 ,l_attachment_details(i).ENTITY_NAME
2411 ,l_attachment_details(i).SITE_ID
2412 ,l_attachment_details(i).DATATYPE_ID
2413 ,l_attachment_details(i).DATATYPE_NAME
2414 ,l_attachment_details(i).DESCRIPTION
2415 ,l_attachment_details(i).FILE_NAME
2416 ,l_attachment_details(i).DM_TYPE
2417 ,l_attachment_details(i).DM_NODE
2418 ,l_attachment_details(i).DM_FOLDER_PATH
2419 ,l_attachment_details(i).DATA_OBJECT_CODE
2420 ,l_attachment_details(i).DOCUMENT_ENTITY_ID
2421 ,l_attachment_details(i).CATEGORY_ID
2422 ,l_attachment_details(i).ATTACHMENT_CATEGORY_NAME
2423 ,l_attachment_details(i).STATUS
2424 ,l_attachment_details(i).ATTACHED_BY_NAME
2425 ,l_attachment_details(i).FILE_NAME_SORT
2426 ,l_attachment_details(i).USAGE_TYPE
2427 ,l_attachment_details(i).SECURITY_TYPE
2428 ,l_attachment_details(i).PUBLISH_FLAG
2429 ,l_attachment_details(i).CAT_ID_QUERY
2430 ,l_attachment_details(i).SEQ_NUM
2431 ,l_attachment_details(i).URL
2432 ,l_attachment_details(i).TITLE
2433 );
2434 END LOOP;
2435 END IF;
2436
2437
2438 /*
2439 dbms_output.put_line(chr(13) || chr(10));
2440 dbms_output.put_line('Printing Attachment Details ');
2441 dbms_output.put_line('============================== ');
2442 dbms_output.put_line(chr(13) || chr(10));
2443
2444 if l_attachment_details.count > 0 then
2445 for i in l_attachment_details.First..l_attachment_details.Last LOOP
2446 dbms_output.put_line('Last Updated : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).Last_update_date);
2447 dbms_output.put_line('Last Updated By : '||chr(9)||chr(9)||x_site_attachment_tab(i).Last_updated_by_name);
2448 dbms_output.put_line('Datatype_id : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).datatype_id);
2449 dbms_output.put_line('Datatype_name : '||chr(9)||chr(9)||x_site_attachment_tab(i).datatype_name);
2450 dbms_output.put_line('Description : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).Description);
2451 dbms_output.put_line('File_name : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).File_name);
2452 dbms_output.put_line('Dm_Type : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).Dm_type);
2453 dbms_output.put_line('Dm_Node : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).Dm_node);
2454 dbms_output.put_line('Dm_folder_path : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).Dm_folder_path);
2455 dbms_output.put_line('data_object_code : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).data_object_code);
2456 dbms_output.put_line('document_entity_id : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).document_entity_id);
2457 dbms_output.put_line('category_id : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).category_id);
2458 dbms_output.put_line('attachment_category_name : '||chr(9)||x_site_attachment_tab(i).attachment_category_name);
2459 dbms_output.put_line('status : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).status);
2460 dbms_output.put_line('attached_by_name : '||chr(9)||chr(9)||x_site_attachment_tab(i).attached_by_name);
2461 dbms_output.put_line('file_name_sort : '||chr(9)||chr(9)||x_site_attachment_tab(i).file_name_sort);
2462 dbms_output.put_line('usage_type : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).usage_type);
2463 dbms_output.put_line('security_type : '||chr(9)||chr(9)||x_site_attachment_tab(i).security_type);
2464 dbms_output.put_line('publish_flag : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).publish_flag);
2465 dbms_output.put_line('cat_id_query : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).cat_id_query);
2466 dbms_output.put_line('seq_num : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).seq_num);
2467 dbms_output.put_line('url : '||chr(9)||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).url);
2468 dbms_output.put_line('title : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).title);
2469 dbms_output.put_line(chr(13) || chr(10));
2470
2471 end loop;
2472 end if;
2473
2474 dbms_output.put_line(chr(13) || chr(10));
2475 dbms_output.put_line('Done Printing Attachment Details ');
2476 dbms_output.put_line('============================== ');
2477 dbms_output.put_line(chr(13) || chr(10));
2478 */
2479
2480 end;
2481
2482 Procedure Get_site_assets(
2483 p_site_id_num IN varchar2
2484 ,p_site_name IN varchar2 Default null
2485 ,x_site_asset_tab OUT NOCOPY rrs_site_asset_tab
2486 ) is
2487
2488
2489 l_site_id number;
2490
2491 TYPE local_rrs_site_asset_rec is RECORD (
2492 item_description mtl_system_items_tl.description%TYPE,
2493 Item mtl_system_items_b.segment1%TYPE,
2494 ItemInstance csi_item_instances.instance_number%TYPE,
2495 SerialNumber csi_item_instances.serial_number%TYPE,
2496 Status csi_instance_statuses.name%TYPE,
2497 Quantity csi_item_instances.quantity%TYPE,
2498 StartDate csi_item_instances.active_start_date%TYPE
2499 );
2500 TYPE local_rrs_site_asset_tab IS TABLE OF local_rrs_site_asset_rec;
2501 l_asset_details local_rrs_site_asset_tab;
2502
2503
2504
2505 begin
2506
2507 begin
2508 Select RSB.SITE_ID
2509 INTO l_site_id
2510 FROM RRS_SITES_B RSB
2511 WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
2512 Exception
2513 When NO_DATA_FOUND Then
2514 raise_application_error(-20101, ' Check the Site ID please');
2515
2516 end;
2517
2518
2519 SELECT
2520 csiiv.DESCRIPTION ItemDescription
2521 ,csiiv.CONCATENATED_SEGMENTS Item
2522 ,csiiv.INSTANCE_NUMBER ItemInstance
2523 ,csiiv.SERIAL_NUMBER SerialNumber
2524 ,csiiv.INSTANCE_STATUS_NAME Status
2525 ,csiiv.QUANTITY Quantity
2526 ,csiiv.ACTIVE_START_DATE StartDate
2527 BULK COLLECT
2528 INTO l_asset_details
2529 FROM CSI_INSTANCE_SEARCH_V csiiv
2530 ,RRS_SITES_B sites
2531 WHERE csiiv.LOCATION_TYPE_CODE ='HZ_PARTY_SITES'
2532 AND csiiv.LOCATION_ID = sites.PARTY_SITE_ID
2533 AND not (csiiv.INSTANCE_STATUS_ID = 1 )
2534 AND nvl(csiiv.ACTIVE_END_DATE, sysdate+1 ) > sysdate
2535 AND sites.site_id = l_site_id
2536 ORDER BY ItemDescription;
2537
2538
2539
2540 x_site_asset_tab := rrs_site_asset_tab();
2541 if l_asset_details.count > 0 then
2542 FOR i in l_asset_details.First..l_asset_details.Last LOOP
2543 x_site_asset_tab.Extend();
2544 x_site_asset_tab(i) := rrs_site_asset_rec(
2545 l_asset_details(i).ITEM_DESCRIPTION
2546 ,l_asset_details(i).ITEM
2547 ,l_asset_details(i).ITEMINSTANCE
2548 ,l_asset_details(i).SERIALNUMBER
2549 ,l_asset_details(i).STATUS
2550 ,l_asset_details(i).QUANTITY
2551 ,l_asset_details(i).STARTDATE
2552 );
2553 END LOOP;
2554 END IF;
2555
2556 /*
2557 dbms_output.put_line(chr(13) || chr(10));
2558 dbms_output.put_line('Printing Asset Details ');
2559 dbms_output.put_line('============================== ');
2560 dbms_output.put_line(chr(13) || chr(10));
2561
2562 if l_asset_details.count > 0 then
2563 for i in l_asset_details.First..l_asset_details.Last LOOP
2564 dbms_output.put_line('Item Description : '||chr(9)||chr(9)||chr(9)||x_site_asset_tab(i).Item_description);
2565 dbms_output.put_line('Item Name : '||chr(9)||chr(9)||chr(9)||chr(9)||x_site_asset_tab(i).Item);
2566 dbms_output.put_line('Item Instance : '||chr(9)||chr(9)||chr(9)||x_site_asset_tab(i).ItemInstance);
2567 dbms_output.put_line('Serial Number : '||chr(9)||chr(9)||chr(9)||x_site_asset_tab(i).SerialNumber);
2568 dbms_output.put_line('Status : '||chr(9)||chr(9)||chr(9)||chr(9)||x_site_asset_tab(i).Status);
2569 dbms_output.put_line('Quantity : '||chr(9)||chr(9)||chr(9)||chr(9)||x_site_asset_tab(i).Quantity);
2570 dbms_output.put_line('Start Date : '||chr(9)||chr(9)||chr(9)||chr(9)||x_site_asset_tab(i).Startdate);
2571 END LOOP;
2572
2573 end if;
2574 dbms_output.put_line(chr(13) || chr(10));
2575 dbms_output.put_line('Done Printing Asset Details ');
2576 dbms_output.put_line('============================== ');
2577 dbms_output.put_line(chr(13) || chr(10));
2578
2579 */
2580 end;
2581
2582 end RRS_SITE_INFO;