[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select resource_id
,category
,resource_number
,address_id
,contact_id
,object_version_number
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
from jtf_rs_resource_extns
where source_id = l_source_id
and category IN ('PARTY','PARTNER') ;
select resource_id
from jtf_rs_resource_extns
where category = 'PARTY'
and source_id = l_party_id;
select resource_id
from jtf_rs_resource_extns
where category = 'PARTNER'
and source_id = l_party_id;
SELECT par.party_type,
prt.party_site_id address_id
FROM hz_parties par,
hz_party_sites prt
WHERE par.party_id = l_party_id
AND par.party_id = prt.party_id(+)
AND nvl(prt.identifying_address_flag, 'Y') = 'Y'
AND nvl(prt.status, 'A') = 'A';
select prt.party_site_id address_id
from hz_party_sites prt
where prt.party_id = l_party_id
and prt.identifying_address_flag = 'Y'
and prt.status = 'A';
update jtf_rs_resource_extns
set source_id = p_to_fk_id,
address_id = l_address_id,
object_version_number = object_version_number + 1
where resource_id = cat_rec.resource_id;
insert into JTF_RS_RESOURCE_EXTN_AUD (
RESOURCE_AUDIT_ID,
RESOURCE_ID,
OLD_SOURCE_ID,
NEW_SOURCE_ID,
OLD_ADDRESS_ID,
NEW_ADDRESS_ID,
NEW_OBJECT_VERSION_NUMBER,
OLD_OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) values (
JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
cat_rec.resource_id,
p_from_fk_id,
p_to_fk_id,
cat_rec.address_id,
l_address_id,
cat_rec.object_version_number + 1,
cat_rec.object_version_number,
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id
);
insert into JTF_RS_RESOURCE_EXTN_AUD (
RESOURCE_AUDIT_ID,
RESOURCE_ID,
OLD_SOURCE_ID,
NEW_SOURCE_ID,
NEW_OBJECT_VERSION_NUMBER,
OLD_OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) values (
JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
cat_rec.resource_id,
p_from_fk_id,
p_to_fk_id,
cat_rec.object_version_number + 1,
cat_rec.object_version_number,
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id
);
update jtf_rs_resource_extns
set source_id = p_to_fk_id,
address_id = l_address_id,
object_version_number = object_version_number + 1
where resource_id = cat_rec.resource_id;
insert into JTF_RS_RESOURCE_EXTN_AUD (
RESOURCE_AUDIT_ID,
RESOURCE_ID,
OLD_SOURCE_ID,
NEW_SOURCE_ID,
OLD_ADDRESS_ID,
NEW_ADDRESS_ID,
NEW_OBJECT_VERSION_NUMBER,
OLD_OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) values (
JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
cat_rec.resource_id,
p_from_fk_id,
p_to_fk_id,
cat_rec.address_id,
l_address_id,
cat_rec.object_version_number + 1,
cat_rec.object_version_number,
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id
);
insert into JTF_RS_RESOURCE_EXTN_AUD (
RESOURCE_AUDIT_ID,
RESOURCE_ID,
OLD_SOURCE_ID,
NEW_SOURCE_ID,
NEW_OBJECT_VERSION_NUMBER,
OLD_OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) values (
JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
cat_rec.resource_id,
p_from_fk_id,
p_to_fk_id,
cat_rec.object_version_number + 1,
cat_rec.object_version_number,
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id
);
select resource_id
,category
,resource_number
,source_id
,contact_id
,object_version_number
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
from jtf_rs_resource_extns
where address_id = l_address_id
and category = 'PARTNER';
select resource_id
from jtf_rs_resource_extns
where category = 'PARTNER'
and source_id = l_source_id
and address_id = l_address_id ;
update jtf_rs_resource_extns
set address_id = p_to_fk_id,
object_version_number = object_version_number + 1
where resource_id = from_rec.resource_id;
insert into JTF_RS_RESOURCE_EXTN_AUD (
RESOURCE_AUDIT_ID,
RESOURCE_ID,
OLD_ADDRESS_ID,
NEW_ADDRESS_ID,
NEW_OBJECT_VERSION_NUMBER,
OLD_OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) values (
JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
from_rec.resource_id,
p_from_fk_id,
p_to_fk_id,
from_rec.object_version_number + 1,
from_rec.object_version_number,
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id
);
select resource_id,
source_id,
address_id,
object_version_number
from jtf_rs_resource_extns
where contact_id = l_contact_id
and category IN ('PARTY','PARTNER') ;
select 'x'
from jtf_rs_party_contacts_vl
where party_id = l_party_id
and nvl(party_site_id, -1) = nvl(l_party_site_id, -1)
and contact_id = l_contact_id ;
update jtf_rs_resource_extns
set contact_id = p_to_fk_id,
object_version_number = object_version_number + 1
where resource_id = res_rec.resource_id;
insert into JTF_RS_RESOURCE_EXTN_AUD (
RESOURCE_AUDIT_ID,
RESOURCE_ID,
OLD_CONTACT_ID,
NEW_CONTACT_ID,
NEW_OBJECT_VERSION_NUMBER,
OLD_OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) values (
JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
res_rec.resource_id,
p_from_fk_id,
p_to_fk_id,
res_rec.object_version_number + 1,
res_rec.object_version_number,
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id
);
SELECT PARTY.PARTY_NUMBER,
PARTY.PARTY_NAME,
PARTY.EMAIL_ADDRESS,
PARTY.ADDRESS1 ,
PARTY.ADDRESS2 ,
PARTY.ADDRESS3 ,
PARTY.ADDRESS4 ,
PARTY.CITY ,
PARTY.POSTAL_CODE ,
PARTY.STATE ,
PARTY.PROVINCE,
PARTY.COUNTY ,
PARTY.COUNTRY ,
CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
TO_NUMBER(NULL) ORG_ID,
NULL ORG_NAME,
PARTY.PERSON_FIRST_NAME,
PARTY.PERSON_MIDDLE_NAME,
PARTY.PERSON_LAST_NAME
FROM
HZ_PARTIES PARTY,
HZ_CONTACT_POINTS CT_POINT1
WHERE PARTY.PARTY_ID = p_source_id
AND PARTY.PARTY_TYPE NOT IN ('ORGANIZATION', 'GROUP')
AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
AND CT_POINT1.STATUS (+)= 'A'
AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE';
SELECT PARTY.PARTY_NAME,
PARTY.PARTY_NUMBER,
PARTY.EMAIL_ADDRESS,
/*PARTY.ADDRESS1 ,
PARTY.ADDRESS2 ,
PARTY.ADDRESS3 ,
PARTY.ADDRESS4 ,
PARTY.CITY ,
PARTY.POSTAL_CODE ,
PARTY.STATE ,
PARTY.PROVINCE,
PARTY.COUNTY ,
PARTY.COUNTRY , */
CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
REL.OBJECT_ID ORG_ID,
PARTY.PARTY_NAME ORG_NAME,
PARTY.PERSON_FIRST_NAME,
PARTY.PERSON_MIDDLE_NAME,
PARTY.PERSON_LAST_NAME
FROM
HZ_PARTIES PARTY,
HZ_PARTIES PARTY2,
HZ_PARTIES PARTY3,
HZ_CONTACT_POINTS CT_POINT1,
-- HZ_PARTY_RELATIONSHIPS REL
HZ_RELATIONSHIPS REL
WHERE PARTY.PARTY_ID = p_source_id
AND (
(
PARTY.PARTY_TYPE = 'ORGANIZATION'
AND
PARTY.PARTY_ID = REL.SUBJECT_ID
)
OR
(
PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND
PARTY.PARTY_ID = REL.PARTY_ID
)
)
-- AND REL.PARTY_RELATIONSHIP_TYPE IN ('PARTNER_OF', 'VAD_OF', 'THIRD_PARTY_FOR')
--
AND REL.RELATIONSHIP_CODE IN
('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY')
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.STATUS = 'A'
--
AND REL.SUBJECT_ID = PARTY2.PARTY_ID
AND (PARTY2.PARTY_TYPE = 'PERSON'
OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
AND REL.OBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
AND CT_POINT1.STATUS (+)= 'A'
AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE';
SELECT PARTY.PARTY_NUMBER,
PARTY.PARTY_NAME,
PARTY.EMAIL_ADDRESS,
LOC.ADDRESS1
,LOC.ADDRESS2
,LOC.ADDRESS3
,LOC.ADDRESS4
,LOC.CITY
,LOC.POSTAL_CODE
,LOC.STATE
,LOC.PROVINCE
,LOC.COUNTY
,LOC.COUNTRY ,
CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
REL.OBJECT_ID ORG_ID,
PARTY.PARTY_NAME ORG_NAME,
PARTY.PERSON_FIRST_NAME,
PARTY.PERSON_MIDDLE_NAME,
PARTY.PERSON_LAST_NAME
FROM
HZ_PARTIES PARTY,
HZ_PARTIES PARTY2,
HZ_PARTIES PARTY3,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CONTACT_POINTS CT_POINT1,
-- HZ_PARTY_RELATIONSHIPS REL
HZ_RELATIONSHIPS REL
WHERE PARTY.PARTY_ID = p_source_id
AND (
(
PARTY.PARTY_TYPE = 'ORGANIZATION'
AND
PARTY.PARTY_ID = REL.SUBJECT_ID
)
OR
(
PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND
PARTY.PARTY_ID = REL.PARTY_ID
)
)
-- AND REL.PARTY_RELATIONSHIP_TYPE IN ('PARTNER_OF', 'VAD_OF', 'THIRD_PARTY_FOR')
--
AND REL.RELATIONSHIP_CODE IN
('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY')
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.STATUS = 'A'
--
AND REL.SUBJECT_ID = PARTY2.PARTY_ID
AND (PARTY2.PARTY_TYPE = 'PERSON'
OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
AND REL.OBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
AND PARTY_SITE.PARTY_SITE_ID = p_address_id
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID (+)
AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
AND CT_POINT1.STATUS (+)= 'A'
AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE';
UPDATE JTF_RS_RESOURCE_EXTNS RES
SET RES.OBJECT_VERSION_NUMBER = res.object_version_number + 1 ,
RES.LAST_UPDATE_DATE = sysdate,
RES.LAST_UPDATED_BY = fnd_global.user_id,
RES.SOURCE_NUMBER = party_rec.party_number ,
RES.SOURCE_NAME = party_rec.party_name,
RES.SOURCE_EMAIL = party_rec.email_address,
RES.SOURCE_ADDRESS1= party_rec.address1,
RES.SOURCE_ADDRESS2 = party_rec.address2,
RES.SOURCE_ADDRESS3 = party_rec.address3,
RES.SOURCE_ADDRESS4 = party_rec.address4,
RES.SOURCE_CITY = party_rec.city,
RES.SOURCE_POSTAL_CODE = party_rec.postal_code,
RES.SOURCE_STATE = party_rec.state,
RES.SOURCE_PROVINCE = party_rec.province,
RES.SOURCE_COUNTY = party_rec.county,
RES.SOURCE_COUNTRY = party_rec.country,
RES.SOURCE_PHONE = party_rec.phone,
--RES.SOURCE_MGR_ID ,
--RES.SOURCE_MGR_NAME ,
RES.SOURCE_ORG_ID = party_rec.org_id ,
RES.SOURCE_ORG_NAME = party_rec.org_name,
RES.SOURCE_FIRST_NAME = party_rec.person_first_name,
RES.SOURCE_MIDDLE_NAME = party_rec.person_middle_name,
RES.SOURCE_LAST_NAME = party_rec.person_last_name
WHERE RES.RESOURCE_ID = p_resource_id;
UPDATE JTF_RS_RESOURCE_EXTNS RES
SET RES.OBJECT_VERSION_NUMBER = res.object_version_number + 1 ,
RES.LAST_UPDATE_DATE = sysdate,
RES.LAST_UPDATED_BY = fnd_global.user_id,
RES.SOURCE_NUMBER = par_rec.party_number ,
RES.SOURCE_NAME = par_rec.party_name,
RES.SOURCE_EMAIL = par_rec.email_address,
/*RES.SOURCE_ADDRESS1= par_rec.address1,
RES.SOURCE_ADDRESS2 = par_rec.address2,
RES.SOURCE_ADDRESS3 = par_rec.address3,
RES.SOURCE_ADDRESS4 = par_rec.address4,
RES.SOURCE_CITY = par_rec.city,
RES.SOURCE_POSTAL_CODE = par_rec.postal_code,
RES.SOURCE_STATE = par_rec.state,
RES.SOURCE_PROVINCE = par_rec.province,
RES.SOURCE_COUNTY = par_rec.county,
RES.SOURCE_COUNTRY = par_rec.country, */
RES.SOURCE_PHONE = par_rec.phone,
--RES.SOURCE_MGR_ID ,
--RES.SOURCE_MGR_NAME ,
RES.SOURCE_ORG_ID = par_rec.org_id ,
RES.SOURCE_ORG_NAME = par_rec.org_name,
RES.SOURCE_FIRST_NAME = par_rec.person_first_name,
RES.SOURCE_MIDDLE_NAME = par_rec.person_middle_name,
RES.SOURCE_LAST_NAME = par_rec.person_last_name
where RES.RESOURCE_ID = p_resource_id;
UPDATE JTF_RS_RESOURCE_EXTNS RES
SET RES.OBJECT_VERSION_NUMBER = res.object_version_number + 1 ,
RES.LAST_UPDATE_DATE = sysdate,
RES.LAST_UPDATED_BY = fnd_global.user_id,
RES.SOURCE_NUMBER = par_address_rec.party_number ,
RES.SOURCE_NAME = par_address_rec.party_name,
RES.SOURCE_EMAIL = par_address_rec.email_address,
RES.SOURCE_ADDRESS1= par_address_rec.address1,
RES.SOURCE_ADDRESS2 = par_address_rec.address2,
RES.SOURCE_ADDRESS3 = par_address_rec.address3,
RES.SOURCE_ADDRESS4 = par_address_rec.address4,
RES.SOURCE_CITY = par_address_rec.city,
RES.SOURCE_POSTAL_CODE = par_address_rec.postal_code,
RES.SOURCE_STATE = par_address_rec.state,
RES.SOURCE_PROVINCE = par_address_rec.province,
RES.SOURCE_COUNTY = par_address_rec.county,
RES.SOURCE_COUNTRY = par_address_rec.country,
RES.SOURCE_PHONE = par_address_rec.phone,
--RES.SOURCE_MGR_ID ,
--RES.SOURCE_MGR_NAME ,
RES.SOURCE_ORG_ID = par_address_rec.org_id ,
RES.SOURCE_ORG_NAME = par_address_rec.org_name,
RES.SOURCE_FIRST_NAME = par_address_rec.person_first_name,
RES.SOURCE_MIDDLE_NAME = par_address_rec.person_middle_name,
RES.SOURCE_LAST_NAME = par_address_rec.person_last_name
where RES.RESOURCE_ID = p_resource_id;
update jtf_rs_resource_extns_tl res
set resource_name
= (select party_name
from hz_parties
where party_id = p_source_id)
where res.resource_id = p_resource_id;
select resource_id,
source_id,
object_version_number
from jtf_rs_resource_extns
where support_site_id = c_support_site_id;
update jtf_rs_resource_extns
set support_site_id = p_to_fk_id,
object_version_number = object_version_number + 1
where resource_id = res_rec.resource_id;
insert into JTF_RS_RESOURCE_EXTN_AUD (
RESOURCE_AUDIT_ID,
RESOURCE_ID,
OLD_SUPPORT_SITE_ID,
NEW_SUPPORT_SITE_ID,
NEW_OBJECT_VERSION_NUMBER,
OLD_OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) values (
JTF_RS_RESOURCE_EXTN_AUD_S.NEXTVAL,
res_rec.resource_id,
p_from_fk_id,
p_to_fk_id,
res_rec.object_version_number + 1,
res_rec.object_version_number,
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id
);