[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_is_primary_flag_selector varchar2(1);
Select RSB.SITE_ID,
RSB.SITE_IDENTIFICATION_NUMBER,
RSB.SITE_TYPE_CODE,
LKUP1.MEANING AS SITE_TYPE_CODE_MEANING,
RSB.SITE_STATUS_CODE,
LKUP2.MEANING AS SITE_STATUS_CODE_MEANING,
RSB.BRANDNAME_CODE ,
LKUP3.MEANING AS BRANDNAME_CODE_MEANING,
RSB.CALENDAR_CODE,
BC.DESCRIPTION,
RSB.LOCATION_ID,
RSB.SITE_PARTY_ID,
RSB.PARTY_SITE_ID,
RSB.LE_PARTY_ID,
HP.PARTY_NAME,
RSB.PROPERTY_LOCATION_ID,
RSB.START_DATE,
RSB.END_DATE,
RST.NAME
Bulk Collect
INTO l_site_header_details
From RRS_SITES_B RSB, RRS_SITES_TL RST , rrs_lookups_v lkup1,rrs_lookups_v lkup2
,rrs_lookups_v lkup3 , BOM_CALENDARS BC, HZ_PARTIES HP
WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num
and RSB.SITE_ID = RST.SITE_ID
and LKUP1.LOOKUP_TYPE = 'RRS_SITE_TYPE'
and LKUP1.LOOKUP_CODE = RSB.SITE_TYPE_CODE
and LKUP2.LOOKUP_TYPE = 'RRS_SITE_STATUS'
and LKUP2.LOOKUP_CODE = RSB.SITE_STATUS_CODE
and LKUP3.LOOKUP_TYPE (+) = 'RRS_BRAND_NAME'
and LKUP3.LOOKUP_CODE (+) = RSB.BRANDNAME_CODE
and BC.CALENDAR_CODE (+) = RSB.CALENDAR_CODE
and DECODE(RSB.SITE_TYPE_CODE,'I',RSB.LE_PARTY_ID,'E',RSB.SITE_PARTY_ID) = HP.PARTY_ID(+)
and RST.LANGUAGE = userenv('LANG');
SELECT ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
NLS_UPPER(CITY) CITY,
NLS_UPPER(COUNTY) COUNTY,
NLS_UPPER(STATE) STATE,
NLS_UPPER(PROVINCE) PROVINCE,
NLS_UPPER(POSTAL_CODE) POSTAL_CODE,
NLS_UPPER(hz_format_pub.get_tl_territory_name(COUNTRY)) COUNTRY,
FTV.TERRITORY_SHORT_NAME COUNTRY_NAME,
HZ_FORMAT_PUB.format_address(location_id, null, null, ',' , null) as Address,
'Y' as IDENTIFYING_ADDRESS_FLAG
Bulk Collect
INTO l_site_address_details
FROM HZ_LOCATIONS
,FND_TERRITORIES_VL FTV
WHERE COUNTRY = FTV.TERRITORY_CODE
AND LOCATION_ID = l_location_id;
SELECT ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
NLS_UPPER(CITY) CITY,
NLS_UPPER(COUNTY) COUNTY,
NLS_UPPER(STATE) STATE,
NLS_UPPER(PROVINCE) PROVINCE,
NLS_UPPER(POSTAL_CODE) POSTAL_CODE,
COUNTRY,
FTV.TERRITORY_SHORT_NAME COUNTRY_NAME,
HZ_FORMAT_PUB.format_address(location_id, null, null, ',' , null) as Address,
'Y' as IDENTIFYING_ADDRESS_FLAG ,
GEOMETRY_SOURCE,
( SELECT rlv.meaning FROM RRS_LOOKUPS_V rlv WHERE rlv.lookup_type = 'RRS_GEO_SOURCE'
AND rlv.lookup_code = HL.GEOMETRY_SOURCE ) GEOMETRY_SOURCE_MEANING,
ROUND(HL.geometry.SDO_POINT.X,8) Longitude,
ROUND(HL.geometry.SDO_POINT.Y,8) Latitude
Bulk Collect
INTO l_site_address_details
FROM HZ_LOCATIONS HL
,FND_TERRITORIES_VL FTV
WHERE HL.COUNTRY = FTV.TERRITORY_CODE
AND HL.LOCATION_ID = l_location_id;
SELECT SiteUseEO.SITE_ID,
SiteUseEO.SITE_USE_ID,
SiteUseEO.SITE_USE_TYPE_CODE,
LKUP1.MEANING AS SITE_USE_TYPE_CODE_MEANING,
SiteUseEO.STATUS_CODE,
LKUP3.MEANING AS STATUS_CODE_MEANING,
SiteUseEO.IS_PRIMARY_FLAG
--,LKUP2.MEANING AS IS_PRIMARY_FLAG_MEANING
BULK COLLECT
INTO site_use_rec_details
FROM RRS_SITE_USES SiteUseEO,
RRS_SITES_B RSB,
HZ_PARTY_SITE_USES HPSU,
AR_LOOKUPS LKUP1
-- ,RRS_LOOKUPS_V LKUP2
,RRS_LOOKUPS_V LKUP3
WHERE RSB.SITE_ID = SiteUseEO.SITE_ID
AND HPSU.PARTY_SITE_ID(+)= RSB.PARTY_SITE_ID
AND DECODE(HPSU.PARTY_SITE_ID,null,'-999',SiteUseEO.SITE_USE_TYPE_CODE) = nvl(HPSU.SITE_USE_TYPE,'-999')
and LKUP1.LOOKUP_TYPE = 'PARTY_SITE_USE_CODE'
and LKUP1.LOOKUP_CODE = SiteUseEO.SITE_USE_TYPE_CODE
--and LKUP2.LOOKUP_TYPE = 'RRS_YES_NO'
--and LKUP2.LOOKUP_CODE = SiteUseEO.IS_PRIMARY_FLAG
and LKUP3.LOOKUP_TYPE = 'RRS_SITE_STATUS'
and LKUP3.LOOKUP_CODE = SiteUseEO.STATUS_CODE
AND RSB.SITE_ID = l_site_id ;
Select RSB.SITE_ID
,RSB.LOCATION_ID
INTO l_site_id
,l_location_id
FROM RRS_SITES_B RSB
WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
Select meaning
INTO l_rrs_entity_name
from RRS_LOOKUPS_V
where LOOKUP_TYPE= 'RRS_ENTITY'
and LOOKUP_CODE = 'RRS_SITE';
SELECT ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
BULK COLLECT
INTO l_attr_grp_name
FROM ego_obj_ag_assocs_b eoab
,fnd_objects fo
,EGO_FND_DSC_FLX_CTX_EXT ext
WHERE eoab.object_id = fo.object_id
AND fo.obj_name in ( 'RRS_SITE')
AND eoab.attr_group_id = ext.attr_group_id
AND eoab.classification_code IN
(
SELECT SITE_USE_TYPE_CODE
FROM RRS_SITE_USES
WHERE SITE_ID = l_site_id
);
SELECT eoab.attr_group_id
BULK COLLECT
INTO l_attr_grp_id
FROM ego_obj_ag_assocs_b eoab
,fnd_objects fo
WHERE eoab.object_id = fo.object_id
AND fo.obj_name in ( 'RRS_SITE')
AND eoab.classification_code IN
(
SELECT SITE_USE_TYPE_CODE
FROM RRS_SITE_USES
WHERE SITE_ID = l_site_id
);
SELECT PAGE_ID,
DISPLAY_NAME
INTO l_page_id,
l_display_name
FROM EGO_PAGES_V
WHERE OBJECT_NAME='RRS_SITE'
AND DISPLAY_NAME = p_page_name
AND CLASSIFICATION_CODE IN
(
SELECT SITE_USE_TYPE_CODE
FROM RRS_SITE_USES
WHERE SITE_ID = l_site_id
)
ORDER BY SEQUENCE;
SELECT ATTR_GROUP_NAME
BULK COLLECT
INTO l_attr_grp_name
FROM EGO_PAGE_ENTRIES_V
WHERE PAGE_ID=l_page_id
ORDER BY SEQUENCE;
Select RSB.SITE_ID
,RSB.LOCATION_ID
INTO l_site_id
,l_location_id
FROM RRS_SITES_B RSB
WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
Select meaning
INTO l_rrs_entity_name
from RRS_LOOKUPS_V
where LOOKUP_TYPE= 'RRS_ENTITY'
and LOOKUP_CODE = 'RRS_LOCATION';
SELECT ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
BULK COLLECT
INTO l_attr_grp_name
FROM ego_obj_ag_assocs_b eoab
,fnd_objects fo
,EGO_FND_DSC_FLX_CTX_EXT ext
WHERE eoab.object_id = fo.object_id
AND fo.obj_name in ( 'RRS_LOCATION')
AND eoab.attr_group_id = ext.attr_group_id
AND eoab.classification_code IN
( select Country
from rrs_locations_ext_vl
where location_id = l_location_id
);
SELECT PAGE_ID,
DISPLAY_NAME
INTO l_page_id,
l_display_name
FROM EGO_PAGES_V
WHERE OBJECT_NAME='RRS_LOCATION'
AND DISPLAY_NAME = p_page_name
AND CLASSIFICATION_CODE IN
(
select Country
from rrs_locations_ext_vl
where location_id = l_location_id
)
ORDER BY SEQUENCE;
SELECT ATTR_GROUP_NAME
BULK COLLECT
INTO l_attr_grp_name
FROM EGO_PAGE_ENTRIES_V
WHERE PAGE_ID=l_page_id
ORDER BY SEQUENCE;
Select RSB.SITE_ID
,RSB.LOCATION_ID
INTO l_site_id
,l_location_id
FROM RRS_SITES_B RSB
WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
Select meaning
INTO l_rrs_entity_name
from RRS_LOOKUPS_V
where LOOKUP_TYPE= 'RRS_ENTITY'
and LOOKUP_CODE = 'RRS_TRADE_AREA';
SELECT distinct ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
BULK COLLECT
INTO l_attr_grp_name
FROM ego_obj_ag_assocs_b eoab
,fnd_objects fo
,EGO_FND_DSC_FLX_CTX_EXT ext
WHERE eoab.object_id = fo.object_id
AND fo.obj_name in ( 'RRS_TRADE_AREA')
AND eoab.attr_group_id = ext.attr_group_id
AND eoab.classification_code IN
( select b.group_id
from rrs_trade_areas_ext_vl a,rrs_trade_areas b
where b.location_id = l_location_id
and a.trade_area_id = b.trade_area_id
);
select trade_area_id
BULK COLLECT
INTO l_trade_area_ids
from rrs_trade_areas
where location_id = l_location_id;
SELECT PAGE_ID,
DISPLAY_NAME
INTO l_page_id,
l_display_name
FROM EGO_PAGES_V
WHERE OBJECT_NAME='RRS_TRADE_AREA'
AND DISPLAY_NAME = p_page_name
AND CLASSIFICATION_CODE IN
(
select b.group_id
from rrs_trade_areas_ext_vl a
,rrs_trade_areas b
where b.location_id = l_location_id
and a.trade_area_id = b.trade_area_id
)
ORDER BY SEQUENCE;
SELECT ATTR_GROUP_NAME
BULK COLLECT
INTO l_attr_grp_name
FROM EGO_PAGE_ENTRIES_V
WHERE PAGE_ID=l_page_id
ORDER BY SEQUENCE;
select trade_area_id
BULK COLLECT
INTO l_trade_area_ids
from rrs_trade_areas
where location_id = l_location_id
and group_id in
(
select classification_code
from EGO_PAGE_ENTRIES_V
where page_id = l_page_id)
;
Select RSB.SITE_ID,
RSB.LOCATION_ID,
RSB.SITE_PARTY_ID
INTO l_site_id,
l_location_id,
l_site_party_id
FROM RRS_SITES_B RSB
WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
select RRS_SITE_UTILS.GET_LOCATION_NAME(site.site_id) as location_name,
RRS_SITE_UTILS.GET_PROPERTY_NAME(site.property_location_id) as property_name,
MP.ORGANIZATION_CODE ORGANIZATION_CODE,
HAOU.NAME ORGANIZATION_DESCRIPTION
BULK Collect
INTO l_property_details
from
rrs_sites_b site,
HR_ALL_ORGANIZATION_UNITS HAOU,
MTL_PARAMETERS MP
where MP.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
and site.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
and site.site_id = l_site_id;
SELECT LocTradeAreaGrpsEO.GROUP_ID,
VL.NAME,
VL.DESCRIPTION,
LocTradeAreaGrpsEO.IS_PRIMARY_FLAG,
LocTradeAreaGrpsEO.STATUS_CODE
BULK COLLECT
INTO l_trade_area_groups_details
FROM RRS_LOC_TRADE_AREA_GRPS LocTradeAreaGrpsEO
,RRS_TRADE_AREA_GROUPS_VL VL
WHERE LocTradeAreaGrpsEO.GROUP_ID = VL.GROUP_ID
AND LOCATION_ID = l_location_id
ORDER BY NAME;
SELECT SiteGroup.NAME
BULK COLLECT
INTO l_cluster_name
FROM RRS_SITE_GROUP_MEMBERS SiteGroupMemberEO ,RRS_SITE_GROUPS_VL SiteGroup
WHERE SiteGroupMemberEO.SITE_GROUP_ID = SiteGroup.SITE_GROUP_ID
AND SiteGroup.SITE_GROUP_TYPE_CODE = 'C'
AND SiteGroupMemberEO.SITE_GROUP_VERSION_ID =
(SELECT MAX(SITE_GROUP_VERSION_ID)
FROM RRS_SITE_GROUP_VERSIONS curVer
WHERE curVer.SITE_GROUP_ID = SiteGroupMemberEO.SITE_GROUP_ID)
AND SiteGroupMemberEO.CHILD_MEMBER_ID = l_site_id
AND DELETED_FLAG = 'N';
SELECT SiteGroup.NAME,
SiteGroupNode.NAME SiteGroupNodeName
BULK COLLECT
INTO l_hierarchy_details
FROM RRS_SITE_GROUP_MEMBERS SiteGroupMemberEO
,RRS_SITE_GROUPS_VL SiteGroup
,RRS_SITE_GROUP_NODES_VL SiteGroupNode
WHERE SiteGroupMemberEO.SITE_GROUP_ID = SiteGroup.SITE_GROUP_ID
AND SiteGroup.SITE_GROUP_TYPE_CODE = 'H'
AND SiteGroupNode.site_group_node_id = SiteGroupMemberEO.PARENT_MEMBER_ID
AND SiteGroupMemberEO.SITE_GROUP_VERSION_ID = (SELECT MAX(SITE_GROUP_VERSION_ID)
FROM RRS_SITE_GROUP_VERSIONS curVer WHERE curVer.SITE_GROUP_ID =
SiteGroupMemberEO.SITE_GROUP_ID) AND SiteGroupMemberEO.CHILD_MEMBER_ID = l_site_id
AND DELETED_FLAG = 'N';
Select Hierarchy_name,
SiteGroupNodeName,
SiteName
BULK COLLECT
INTO l_hierarchy_details
FROM
(SELECT SiteGroup.NAME Hierarchy_name,
SiteGroupNode.NAME SiteGroupNodeName,
NULL SiteName
FROM RRS_SITE_GROUP_MEMBERS SiteGroupMemberEO
,RRS_SITE_GROUPS_VL SiteGroup
,RRS_SITE_GROUP_NODES_VL SiteGroupNode
WHERE SiteGroupMemberEO.SITE_GROUP_ID = SiteGroup.SITE_GROUP_ID
AND SiteGroup.SITE_GROUP_TYPE_CODE = 'H'
AND SiteGroupNode.site_group_node_id = SiteGroupMemberEO.PARENT_MEMBER_ID
AND SiteGroupMemberEO.SITE_GROUP_VERSION_ID = (SELECT MAX(SITE_GROUP_VERSION_ID)
FROM RRS_SITE_GROUP_VERSIONS curVer WHERE curVer.SITE_GROUP_ID =
SiteGroupMemberEO.SITE_GROUP_ID) AND SiteGroupMemberEO.CHILD_MEMBER_ID =
l_site_id
AND SiteGroupMemberEO.DELETED_FLAG = 'N'
UNION ALL
SELECT
SiteGroup.NAME Hierarchy_name,
NULL SiteGroupNodeName,
SITE.NAME SiteName
FROM RRS_SITE_GROUP_MEMBERS SiteGroupMemberEO
,RRS_SITE_GROUPS_VL SiteGroup
,RRS_SITES_VL SITE
WHERE SiteGroupMemberEO.SITE_GROUP_ID = SiteGroup.SITE_GROUP_ID
AND SiteGroup.SITE_GROUP_TYPE_CODE = 'H'
AND SITE.SITE_ID =SiteGroupMemberEO.PARENT_MEMBER_ID
AND SiteGroupMemberEO.SITE_GROUP_VERSION_ID = (SELECT MAX(SITE_GROUP_VERSION_ID)
FROM RRS_SITE_GROUP_VERSIONS curVer WHERE curVer.SITE_GROUP_ID =
SiteGroupMemberEO.SITE_GROUP_ID)
AND SiteGroupMemberEO.CHILD_MEMBER_ID = l_site_id
AND SiteGroupMemberEO.deleted_flag='N');
SELECT HzPuiRelationshipsEO.start_date,
decode(to_char(HzPuiRelationshipsEO.end_date,'DD-MM-YYYY') , '31-12-4712', to_date(null), HzPuiRelationshipsEO.end_date) end_date,
HzPuiRelationshipsEO.comments,
subjectparty.party_name subject_party_name,
objectparty.party_name object_party_name,
reltype.role relationship_role,
relationshiprolelu.description relationship_role_meaning
BULK COLLECT
INTO l_relationship_details
FROM hz_relationships HzPuiRelationshipsEO,
hz_relationship_types reltype,
hz_parties subjectparty,
hz_parties objectparty,
fnd_lookup_values subjectpartytypelu,
fnd_lookup_values objectpartytypelu,
fnd_lookup_values relationshiprolelu
WHERE HzPuiRelationshipsEO.subject_table_name = 'HZ_PARTIES'
AND HzPuiRelationshipsEO.object_table_name = 'HZ_PARTIES'
AND HzPuiRelationshipsEO.status IN ('A', 'I')
AND HzPuiRelationshipsEO.subject_id = subjectparty.party_id
AND HzPuiRelationshipsEO.object_id = objectparty.party_id
AND HzPuiRelationshipsEO.relationship_type = reltype.relationship_type
AND HzPuiRelationshipsEO.relationship_code = reltype.forward_rel_code
AND HzPuiRelationshipsEO.subject_type = reltype.subject_type
AND HzPuiRelationshipsEO.object_type = reltype.object_type
AND subjectpartytypelu.view_application_id = 222
AND subjectpartytypelu.lookup_type = 'PARTY_TYPE'
AND subjectpartytypelu.language = userenv('LANG')
AND subjectpartytypelu.lookup_code = HzPuiRelationshipsEO.subject_type
AND objectpartytypelu.view_application_id = 222
AND objectpartytypelu.lookup_type = 'PARTY_TYPE'
AND objectpartytypelu.language = userenv('LANG')
AND objectpartytypelu.lookup_code = HzPuiRelationshipsEO.object_type
AND relationshiprolelu.view_application_id = 222
AND relationshiprolelu.lookup_type = 'HZ_RELATIONSHIP_ROLE'
AND relationshiprolelu.language = userenv('LANG')
AND relationshiprolelu.lookup_code = reltype.role
AND HzPuiRelationshipsEO.object_type = 'ORGANIZATION'
and HzPuiRelationshipsEO.object_id = l_site_party_id
and HzPuiRelationshipsEO.subject_type = 'ORGANIZATION'
and (HzPuiRelationshipsEO.status = 'A'
and (HzPuiRelationshipsEO.end_date is null or HzPuiRelationshipsEO.end_date >= trunc(sysdate)) );
Select RSB.SITE_ID,
RSB.SITE_PARTY_ID
INTO l_site_id,
l_site_party_id
FROM RRS_SITES_B RSB
WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
SELECT ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
NLS_UPPER(CITY) CITY,
NLS_UPPER(COUNTY) COUNTY,
NLS_UPPER(STATE) STATE,
NLS_UPPER(PROVINCE) PROVINCE,
NLS_UPPER(POSTAL_CODE) POSTAL_CODE,
NLS_UPPER(hz_format_pub.get_tl_territory_name(COUNTRY)) COUNTRY,
FTV.TERRITORY_SHORT_NAME COUNTRY_NAME,
HZ_FORMAT_PUB.format_address(HL.location_id, null, null, ',' , null) as Address,
HPS.IDENTIFYING_ADDRESS_FLAG
BULK COLLECT
INTO l_site_party_dets
FROM HZ_LOCATIONS HL
,FND_TERRITORIES_VL FTV
,HZ_PARTY_SITES HPS
WHERE COUNTRY = FTV.TERRITORY_CODE
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_ID = l_site_party_id;
SELECT ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
NLS_UPPER(CITY) CITY,
NLS_UPPER(COUNTY) COUNTY,
NLS_UPPER(STATE) STATE,
NLS_UPPER(PROVINCE) PROVINCE,
NLS_UPPER(POSTAL_CODE) POSTAL_CODE,
COUNTRY,
FTV.TERRITORY_SHORT_NAME COUNTRY_NAME,
HZ_FORMAT_PUB.format_address(HL.location_id, null, null, ',' , null) as Address,
HPS.IDENTIFYING_ADDRESS_FLAG,
( select AL.MEANING
from AR_LOOKUPS AL
where AL.LOOKUP_TYPE = 'PARTY_SITE_USE_CODE'
AND AL.LOOKUP_CODE = PSU.SITE_USE_TYPE ) site_purpose
BULK COLLECT
INTO l_site_party_dets
FROM HZ_LOCATIONS HL
,FND_TERRITORIES_VL FTV
,HZ_PARTY_SITES HPS
, HZ_PARTY_SITE_USES PSU
WHERE COUNTRY = FTV.TERRITORY_CODE
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND PSU.PARTY_SITE_ID(+) = HPS.PARTY_SITE_ID
AND PSU.STATUS(+) = 'A'
AND HPS.PARTY_ID = l_site_party_id;
SELECT HzPuiContactPointPhoneEO.CONTACT_POINT_ID,
HzPuiContactPointPhoneEO.CONTACT_POINT_TYPE,
HzPuiContactPointPhoneEO.STATUS,
HzPuiContactPointPhoneEO.OWNER_TABLE_NAME,
HzPuiContactPointPhoneEO.OWNER_TABLE_ID,
HzPuiContactPointPhoneEO.PRIMARY_FLAG,
HzPuiContactPointPhoneEO.ATTRIBUTE_CATEGORY,
HzPuiContactPointPhoneEO.PHONE_CALLING_CALENDAR,
HzPuiContactPointPhoneEO.LAST_CONTACT_DT_TIME,
HzPuiContactPointPhoneEO.PHONE_PREFERRED_ORDER,
HzPuiContactPointPhoneEO.PRIORITY_OF_USE_CODE,
HzPuiContactPointPhoneEO.TELEPHONE_TYPE,
HzPuiContactPointPhoneEO.TIME_ZONE,
HzPuiContactPointPhoneEO.PHONE_TOUCH_TONE_TYPE_FLAG,
HzPuiContactPointPhoneEO.PHONE_AREA_CODE,
HzPuiContactPointPhoneEO.PHONE_COUNTRY_CODE,
HzPuiContactPointPhoneEO.PHONE_NUMBER,
HzPuiContactPointPhoneEO.PHONE_EXTENSION,
HzPuiContactPointPhoneEO.PHONE_LINE_TYPE,
HzPuiContactPointPhoneEO.CONTENT_SOURCE_TYPE,
HzPuiContactPointPhoneEO.RAW_PHONE_NUMBER,
HzPuiContactPointPhoneEO.TIMEZONE_ID,
Ftv.Name AS TIMEZONE_NAME,
HzPuiContactPointPhoneEO.CONTACT_POINT_PURPOSE,
HzPuiContactPointPhoneEO.PRIMARY_BY_PURPOSE,
HzPuiContactPointPhoneEO.TRANSPOSED_PHONE_NUMBER,
HzPuiContactPointPhoneEO.ACTUAL_CONTENT_SOURCE
BULK COLLECT
INTO l_phone_details
FROM
HZ_CONTACT_POINTS HzPuiContactPointPhoneEO
,FND_TIMEZONES_VL Ftv
WHERE (CONTACT_POINT_TYPE = 'PHONE' and
OWNER_TABLE_NAME = 'HZ_PARTIES' AND OWNER_TABLE_ID = l_site_party_id
AND Ftv.ENABLED_FLAG(+) = 'Y'
AND HzPuiContactPointPhoneEO.TIMEZONE_ID = Ftv.UPGRADE_TZ_ID(+)
);
SELECT HzPuiContactPointEmailEO.CONTACT_POINT_ID,
HzPuiContactPointEmailEO.CONTACT_POINT_TYPE,
HzPuiContactPointEmailEO.STATUS,
HzPuiContactPointEmailEO.OWNER_TABLE_NAME,
HzPuiContactPointEmailEO.OWNER_TABLE_ID,
HzPuiContactPointEmailEO.PRIMARY_FLAG,
HzPuiContactPointEmailEO.EMAIL_FORMAT,
HzPuiContactPointEmailEO.EMAIL_ADDRESS,
HzPuiContactPointEmailEO.CONTACT_POINT_PURPOSE,
HzPuiContactPointEmailEO.PRIMARY_BY_PURPOSE,
AL.MEANING USAGE,
HzPuiContactPointEmailEO.ATTRIBUTE_CATEGORY,
HzPuiContactPointEmailEO.ACTUAL_CONTENT_SOURCE
BULK COLLECT
INTO l_email_details
FROM HZ_CONTACT_POINTS HzPuiContactPointEmailEO,
fnd_lookup_values al
WHERE HzPuiContactPointEmailEO.CONTACT_POINT_TYPE ='EMAIL'
and HzPuiContactPointEmailEO.STATUS = 'A'
and al.view_application_id(+) = 222
and al.language(+) = userenv('LANG')
and al.lookup_type(+) = 'CONTACT_POINT_PURPOSE'
and HzPuiContactPointEmailEO.CONTACT_POINT_PURPOSE = al.LOOKUP_CODE(+)
and (HzPuiContactPointEmailEO.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HzPuiContactPointEmailEO.OWNER_TABLE_ID = l_site_party_id)
ORDER BY HzPuiContactPointEmailEO.PRIMARY_FLAG DESC, USAGE NULLS LAST, HzPuiContactPointEmailEO.PRIMARY_BY_PURPOSE DESC;
SELECT HzPuiContactPointUrlEO.CONTACT_POINT_ID,
HzPuiContactPointUrlEO.CONTACT_POINT_TYPE,
HzPuiContactPointUrlEO.STATUS,
HzPuiContactPointUrlEO.OWNER_TABLE_NAME,
HzPuiContactPointUrlEO.OWNER_TABLE_ID,
HzPuiContactPointUrlEO.PRIMARY_FLAG,
HzPuiContactPointUrlEO.WEB_TYPE,
HzPuiContactPointUrlEO.URL,
HzPuiContactPointUrlEO.CONTENT_SOURCE_TYPE,
HzPuiContactPointUrlEO.APPLICATION_ID,
HzPuiContactPointUrlEO.CONTACT_POINT_PURPOSE,
HzPuiContactPointUrlEO.PRIMARY_BY_PURPOSE,
HzPuiContactPointUrlEO.TRANSPOSED_PHONE_NUMBER,
HzPuiContactPointUrlEO.ACTUAL_CONTENT_SOURCE,
AL.MEANING USAGE,
HzPuiContactPointUrlEO.ATTRIBUTE_CATEGORY
BULK COLLECT
INTO l_url_details
FROM HZ_CONTACT_POINTS HzPuiContactPointUrlEO,
fnd_lookup_values al
WHERE HzPuiContactPointUrlEO.CONTACT_POINT_TYPE ='WEB'
and HzPuiContactPointUrlEO.STATUS = 'A'
and al.view_application_id(+) = 222
and al.language(+) = userenv('LANG')
and al.lookup_type(+) = 'CONTACT_POINT_PURPOSE_WEB'
and HzPuiContactPointUrlEO.CONTACT_POINT_PURPOSE = al.LOOKUP_CODE(+)
and (HzPuiContactPointUrlEO.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HzPuiContactPointUrlEO.OWNER_TABLE_ID = l_site_party_id)
ORDER BY HzPuiContactPointUrlEO.PRIMARY_FLAG DESC, USAGE NULLS LAST, HzPuiContactPointUrlEO.PRIMARY_BY_PURPOSE DESC;
SELECT HzPuiRelationshipsEO.relationship_id,
HzPuiRelationshipsEO.subject_type,
HzPuiRelationshipsEO.object_id,
HzPuiRelationshipsEO.object_type,
HzPuiRelationshipsEO.object_table_name,
HzPuiRelationshipsEO.party_id relationship_party_id,
HzPuiRelationshipsEO.relationship_type,
HzPuiRelationshipsEO.relationship_code,
HzPuiRelationshipsEO.start_date,
decode(to_char(HzPuiRelationshipsEO.end_date,'DD-MM-YYYY') ,
'31-12-4712', to_date(null), --to_date to avoid xml.17 issue
HzPuiRelationshipsEO.end_date) end_date,
HzPuiRelationshipsEO.comments,
HzPuiRelationshipsEO.status,
HzPuiRelationshipsEO.actual_content_source,
subjectparty.party_name subject_party_name,
subjectparty.party_number subject_party_number,
subjectparty.known_as subject_party_known_as,
objectparty.party_name object_party_name,
objectparty.party_number object_party_number,
objectparty.known_as object_party_known_as,
reltype.relationship_type_id,
reltype.role relationship_role,
subjectpartytypelu.meaning subject_type_meaning,
objectpartytypelu.meaning object_type_meaning,
relationshiprolelu.description relationship_role_meaning
BULK COLLECT
INTO l_person_details
FROM hz_relationships HzPuiRelationshipsEO,
hz_relationship_types reltype,
hz_parties subjectparty,
hz_parties objectparty,
fnd_lookup_values subjectpartytypelu,
fnd_lookup_values objectpartytypelu,
fnd_lookup_values relationshiprolelu
WHERE HzPuiRelationshipsEO.subject_table_name = 'HZ_PARTIES'
AND HzPuiRelationshipsEO.object_table_name = 'HZ_PARTIES'
AND HzPuiRelationshipsEO.status IN ('A', 'I')
AND HzPuiRelationshipsEO.subject_id = subjectparty.party_id
AND HzPuiRelationshipsEO.object_id = objectparty.party_id
AND HzPuiRelationshipsEO.relationship_type = reltype.relationship_type
AND HzPuiRelationshipsEO.relationship_code = reltype.forward_rel_code
AND HzPuiRelationshipsEO.subject_type = reltype.subject_type
AND HzPuiRelationshipsEO.object_type = reltype.object_type
AND subjectpartytypelu.view_application_id = 222
AND subjectpartytypelu.lookup_type = 'PARTY_TYPE'
AND subjectpartytypelu.language = userenv('LANG')
AND subjectpartytypelu.lookup_code = HzPuiRelationshipsEO.subject_type
AND objectpartytypelu.view_application_id = 222
AND objectpartytypelu.lookup_type = 'PARTY_TYPE'
AND objectpartytypelu.language = userenv('LANG')
AND objectpartytypelu.lookup_code = HzPuiRelationshipsEO.object_type
AND relationshiprolelu.view_application_id = 222
AND relationshiprolelu.lookup_type = 'HZ_RELATIONSHIP_ROLE'
AND relationshiprolelu.language = userenv('LANG')
AND relationshiprolelu.lookup_code = reltype.role
AND (HzPuiRelationshipsEO.object_type = 'ORGANIZATION'
and HzPuiRelationshipsEO.object_id = l_site_party_id
and HzPuiRelationshipsEO.subject_type = 'PERSON'
and (HzPuiRelationshipsEO.status = 'A'
and (end_date is null or end_date >= trunc(sysdate)) ));
Last_update_date fnd_attached_documents.last_update_date%TYPE,
Last_updated_by_name fnd_user.user_name%TYPE,
Entity_name fnd_attached_documents.Entity_name%TYPE,
site_id fnd_attached_documents.pk1_value%TYPE,
datatype_id fnd_documents.datatype_id%TYPE,
datatype_name fnd_document_datatypes.user_name%TYPE,
description fnd_documents_tl.description%TYPE,
file_name fnd_documents_tl.file_name%TYPE,
dm_type fnd_documents.dm_type%TYPE,
dm_node fnd_documents.dm_node%TYPE,
dm_folder_path fnd_documents.dm_folder_path%TYPE,
data_object_code fnd_document_entities.data_object_code%TYPE,
document_entity_id fnd_document_entities.document_entity_id%TYPE,
category_id fnd_attached_documents.category_id%TYPE,
attachment_category_name fnd_document_categories_tl.user_name%TYPE,
status fnd_attached_documents.status%TYPE,
attached_by_name fnd_user.user_name%TYPE,
file_name_sort fnd_documents.URL%TYPE,
usage_type fnd_documents.usage_type%TYPE,
security_type fnd_documents.security_type%TYPE,
publish_flag fnd_documents.publish_flag%TYPE,
cat_id_query fnd_document_categories_tl.category_id%TYPE,
seq_num fnd_attached_documents.seq_num%TYPE,
url fnd_documents.URL%TYPE,
title fnd_documents_tl.title%TYPE
);
Select RSB.SITE_ID
INTO l_site_id
FROM RRS_SITES_B RSB
WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
SELECT distinct
ad.LAST_UPDATE_DATE,
u.USER_NAME LAST_UPDATED_BY_NAME,
ad.ENTITY_NAME,
ad.pk1_value,
d.DATATYPE_ID,
d.DATATYPE_NAME,
d.DESCRIPTION,
decode(d.FILE_NAME, null, (select message_text from fnd_new_messages
where message_name = 'FND_UNDEFINED' and application_id = 0
and language_code = userenv('LANG')), d.FILE_NAME) FILE_NAME,
d.dm_type,
d.dm_node,
d.dm_folder_path,
e.DATA_OBJECT_CODE,
e.DOCUMENT_ENTITY_ID,
-- 'ALLOW_ATTACH_UPDATE' ALLOW_ATTACH_UPDATE,
-- 'ALLOW_ATTACH_DELETE' ALLOW_ATTACH_DELETE,
ad.category_id category_id,
cl.user_name attachment_category_name,
ad.status,
(select u1.user_name from fnd_user u1 where u1.user_id=ad.CREATED_BY) ATTACHED_BY_NAME,
decode(d.datatype_id, 5, nvl(d.title,d.description)||'('||substr(d.URL, 1, least(length(d.URL),15))||'...)',
decode(d.datatype_id, 6, nvl(d.title, d.file_name), decode(D.TITLE, null, (select message_text from fnd_new_messages where
message_name = 'FND_UNDEFINED' and application_id = 0 and language_code = userenv('LANG')), D.TITLE))) FILE_NAME_SORT,
d.usage_type,
d.security_type,
d.publish_flag,
cl.category_id cat_id_query,
ad.seq_num,
d.URL,
d.TITLE
BULK COLLECT
INTO l_attachment_details
FROM FND_DOCUMENTS_VL d,
FND_ATTACHED_DOCUMENTS ad,
FND_DOCUMENT_ENTITIES e,
FND_USER u,
FND_DOCUMENT_CATEGORIES_TL cl,
FND_DM_NODES node
WHERE ad.DOCUMENT_ID = d.DOCUMENT_ID
and ad.ENTITY_NAME = e.DATA_OBJECT_CODE(+)
and ad.LAST_UPDATED_BY = u.USER_ID(+)
and cl.language = userenv('LANG')
and cl.category_id = nvl(ad.category_id, d.category_id)
and d.dm_node = node.node_id(+)
and ad.entity_name = 'RRS_SITE_ATTACHMENTS'
and ad.pk1_value = l_site_id
and cl.category_id in (1,1)
and d.datatype_id in (6,2,1,5)
AND (d.SECURITY_TYPE=4 OR d.PUBLISH_FLAG='Y')
ORDER BY seq_num;
l_attachment_details(i).LAST_UPDATE_DATE
,l_attachment_details(i).LAST_UPDATED_BY_NAME
,l_attachment_details(i).ENTITY_NAME
,l_attachment_details(i).SITE_ID
,l_attachment_details(i).DATATYPE_ID
,l_attachment_details(i).DATATYPE_NAME
,l_attachment_details(i).DESCRIPTION
,l_attachment_details(i).FILE_NAME
,l_attachment_details(i).DM_TYPE
,l_attachment_details(i).DM_NODE
,l_attachment_details(i).DM_FOLDER_PATH
,l_attachment_details(i).DATA_OBJECT_CODE
,l_attachment_details(i).DOCUMENT_ENTITY_ID
,l_attachment_details(i).CATEGORY_ID
,l_attachment_details(i).ATTACHMENT_CATEGORY_NAME
,l_attachment_details(i).STATUS
,l_attachment_details(i).ATTACHED_BY_NAME
,l_attachment_details(i).FILE_NAME_SORT
,l_attachment_details(i).USAGE_TYPE
,l_attachment_details(i).SECURITY_TYPE
,l_attachment_details(i).PUBLISH_FLAG
,l_attachment_details(i).CAT_ID_QUERY
,l_attachment_details(i).SEQ_NUM
,l_attachment_details(i).URL
,l_attachment_details(i).TITLE
);
dbms_output.put_line('Last Updated : '||chr(9)||chr(9)||chr(9)||x_site_attachment_tab(i).Last_update_date);
dbms_output.put_line('Last Updated By : '||chr(9)||chr(9)||x_site_attachment_tab(i).Last_updated_by_name);
Select RSB.SITE_ID
INTO l_site_id
FROM RRS_SITES_B RSB
WHERE RSB.SITE_IDENTIFICATION_NUMBER = p_site_id_num;
SELECT
csiiv.DESCRIPTION ItemDescription
,csiiv.CONCATENATED_SEGMENTS Item
,csiiv.INSTANCE_NUMBER ItemInstance
,csiiv.SERIAL_NUMBER SerialNumber
,csiiv.INSTANCE_STATUS_NAME Status
,csiiv.QUANTITY Quantity
,csiiv.ACTIVE_START_DATE StartDate
BULK COLLECT
INTO l_asset_details
FROM CSI_INSTANCE_SEARCH_V csiiv
,RRS_SITES_B sites
WHERE csiiv.LOCATION_TYPE_CODE ='HZ_PARTY_SITES'
AND csiiv.LOCATION_ID = sites.PARTY_SITE_ID
AND not (csiiv.INSTANCE_STATUS_ID = 1 )
AND nvl(csiiv.ACTIVE_END_DATE, sysdate+1 ) > sysdate
AND sites.site_id = l_site_id
ORDER BY ItemDescription;