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