[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_errors_tbl.delete;
SELECT
max(version)
FROM
pv_enty_attr_values
WHERE
attribute_id = cv_attr_id and
entity_id = cv_entity_id;
,p_update_flag IN VARCHAR2
,x_contact_rel_id OUT NOCOPY NUMBER
,x_contact_output_rec OUT NOCOPY CONTACT_OUTPUT_REC_TYPE
,x_log_msg OUT NOCOPY LOG_MESSAGE_TBL_TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'contact_create';
l_update_allowed VARCHAR2(1000) := p_update_flag;
l_last_update_date VARCHAR2(2000);
select usertype_id ,nvl(approval_id,-1) from JTF_UM_USERTYPES_B where usertype_key=user_type;
select a.subscription_id from JTF_UM_USERTYPE_SUBSCRIP a,JTF_UM_SUBSCRIPTIONS_B b,JTF_UM_USERTYPES_B c
where a.subscription_id=b.subscription_id
and c.usertype_key=user_type
and a.usertype_id=c.usertype_id;
select to_char (last_update_date, 'mmddyyyyhh24miss')
from jtf_um_usertype_reg
where usertype_reg_id = to_number (l_user_reg_id);
select partner_id, party_name from pv_partner_profiles,hz_parties where partner_party_id=l_party_id and party_id=partner_party_id;
CURSOR USERTYPE_RESP(user_type VARCHAR2) is select FR.RESPONSIBILITY_ID, UT.APPLICATION_ID, FR.VERSION
FROM JTF_UM_USERTYPE_RESP UT,
FND_RESPONSIBILITY_VL FR,
JTF_UM_USERTYPES_B c
WHERE c.usertype_key=user_type
and UT.USERTYPE_ID = c.usertype_id
AND FR.APPLICATION_ID = UT.APPLICATION_ID
AND FR.RESPONSIBILITY_KEY = UT.RESPONSIBILITY_KEY
AND (UT.EFFECTIVE_END_DATE IS NULL OR UT.EFFECTIVE_END_DATE > SYSDATE)
AND UT.EFFECTIVE_START_DATE < SYSDATE;
CURSOR USERTYPE_ROLES(usertype_id NUMBER) IS SELECT PRINCIPAL_NAME
FROM JTF_UM_USERTYPE_ROLE
WHERE USERTYPE_ID = usertype_id
AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)
AND EFFECTIVE_START_DATE < SYSDATE;
SELECT CUST_ACCOUNT_ID from hz_cust_Accounts where party_id=l_party_id;
SELECT CUST_ACCOUNT_ROLE_ID from hz_cust_account_roles where party_id=l_party_id;
select hzrp.party_id from hz_org_contacts hzoc, hz_relationships hzrp
where hzoc.party_relationship_id = hzrp.relationship_id and
hzrp.relationship_code='EMPLOYEE_OF' and
hzrp.subject_id=p_person_party_id and
hzrp.object_id=p_partner_party_id
and hzrp.start_date <= sysdate
and (hzrp.end_date is null or hzrp.end_date > sysdate);
SELECT
HZ_PARTIES.PARTY_ID
FROM
HZ_ORIG_SYS_REFERENCES,
HZ_PARTIES
WHERE
HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID AND
HZ_PARTIES.PARTY_TYPE = l_party_type AND
HZ_ORIG_SYS_REFERENCES.orig_system = orig_system AND
HZ_ORIG_SYS_REFERENCES.orig_system_reference = orig_system_ref AND
HZ_ORIG_SYS_REFERENCES.owner_table_name = 'HZ_PARTIES' AND
HZ_PARTIES.STATUS = 'A';
SELECT
HZ_PARTIES.PARTY_ID
FROM
HZ_ORIG_SYS_REFERENCES,
HZ_PARTIES
WHERE
HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID AND
HZ_PARTIES.PARTY_TYPE = l_party_type AND
HZ_PARTIES.party_id = l_party_id and
HZ_ORIG_SYS_REFERENCES.orig_system = orig_system AND
HZ_ORIG_SYS_REFERENCES.orig_system_reference = orig_system_ref AND
HZ_ORIG_SYS_REFERENCES.owner_table_name = 'HZ_PARTIES' AND
HZ_PARTIES.STATUS = 'A';
SELECT
HZ_PARTIES.PARTY_ID
FROM
HZ_ORIG_SYS_REFERENCES,
HZ_PARTIES
WHERE
HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID AND
HZ_PARTIES.PARTY_TYPE = l_party_type AND
HZ_PARTIES.party_id = l_party_id and
HZ_ORIG_SYS_REFERENCES.orig_system = orig_system AND
HZ_ORIG_SYS_REFERENCES.owner_table_name = 'HZ_PARTIES' AND
HZ_PARTIES.STATUS = 'A';
SELECT
HZ_PARTIES.PARTY_ID
FROM
HZ_ORIG_SYS_REFERENCES,
HZ_PARTIES
WHERE
HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID AND
HZ_PARTIES.PARTY_TYPE = l_party_type AND
HZ_PARTIES.party_id = l_party_id and
HZ_ORIG_SYS_REFERENCES.orig_system_reference = orig_system_ref AND
HZ_ORIG_SYS_REFERENCES.owner_table_name = 'HZ_PARTIES' AND
HZ_PARTIES.STATUS = 'A';
select user_name from fnd_user where customer_id=l_rel_party_id;
select user_name from fnd_user where person_party_id=l_person_party_id;
l_mode:='UPDATE';
if l_mode = 'UPDATE' and l_update_allowed = FND_API.G_FALSE then
l_contact_create_ok:= 'FALSE';
l_sso_enabled := fnd_profile.value('APPS_SSO_USER_CREATE_UPDATE');
if l_mode = 'UPDATE' and l_update_allowed = FND_API.G_FALSE then
if l_exists_user_name is not null then
l_contact_create_ok:= 'FALSE';
elsif l_mode ='UPDATE' and l_update_allowed =FND_API.G_TRUE then
if l_contact_details_rec.user_name <> l_exists_user_name then
l_contact_create_ok:= 'FALSE';
if l_update_allowed = FND_API.G_FALSE OR l_mode='CREATE' then
l_test_user_return_code := FND_USER_PKG.TestUserName(l_contact_details_rec.user_name);
PV_CONTACT_USER_BATCH_PUB.user_update (
p_api_version_number =>1.0
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_user_name => l_contact_details_rec.user_name
,p_user_type_key => l_user_type
,p_contact_rel_id => l_rel_party_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
l_last_update_date VARCHAR2(2000);
select usertype_id ,nvl(approval_id,-1) from JTF_UM_USERTYPES_B where usertype_key=user_type;
select a.subscription_id from JTF_UM_USERTYPE_SUBSCRIP a,JTF_UM_SUBSCRIPTIONS_B b,JTF_UM_USERTYPES_B c
where a.subscription_id=b.subscription_id
and c.usertype_key=user_type
and a.usertype_id=c.usertype_id;
select to_char (last_update_date, 'mmddyyyyhh24miss')
from jtf_um_usertype_reg
where usertype_reg_id = to_number (l_user_reg_id);
select partner_id from pv_partner_profiles where partner_party_id=l_party_id;
CURSOR USERTYPE_RESP(user_type VARCHAR2) is select FR.RESPONSIBILITY_ID, UT.APPLICATION_ID, FR.VERSION
FROM JTF_UM_USERTYPE_RESP UT,
FND_RESPONSIBILITY_VL FR,
JTF_UM_USERTYPES_B c
WHERE c.usertype_key=user_type
and UT.USERTYPE_ID = c.usertype_id
AND FR.APPLICATION_ID = UT.APPLICATION_ID
AND FR.RESPONSIBILITY_KEY = UT.RESPONSIBILITY_KEY
AND (UT.EFFECTIVE_END_DATE IS NULL OR UT.EFFECTIVE_END_DATE > SYSDATE)
AND UT.EFFECTIVE_START_DATE < SYSDATE;
CURSOR USERTYPE_ROLES(usertype_id NUMBER) IS SELECT PRINCIPAL_NAME
FROM JTF_UM_USERTYPE_ROLE
WHERE USERTYPE_ID = usertype_id
AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)
AND EFFECTIVE_START_DATE < SYSDATE;
select relationship_code from hz_relationships hzr where hzr.party_id=rel_party_id and hzr.directional_flag='F';
select hzp.status from hz_parties hzp where hzp.party_id=rel_party_id ;
select email_address from hz_parties hzp where hzp.party_id=rel_party_id ;
select pvpp.partner_id, pvpp.PARTNER_GROUP_ID , person_hzp.PERSON_FIRST_NAME, person_hzp.person_last_name,
hzoc.org_contact_id, org_hzp.party_name, rel_hzp.email_address
from HZ_PARTIES PERSON_HZP, HZ_RELATIONSHIPS HZR, PV_PARTNER_PROFILES pvpp, hz_org_contacts hzoc, HZ_PARTIES ORG_HZP,
hz_parties REL_HZP
where HZR.party_id = rel_party_id
and HZR.directional_flag = 'F'
and hzr.relationship_code = 'EMPLOYEE_OF'
and HZR.subject_table_name ='HZ_PARTIES'
and HZR.object_table_name ='HZ_PARTIES'
and hzr.start_date <= SYSDATE
and (hzr.end_date is null or hzr.end_date > SYSDATE)
and hzr.status = 'A'
and hzr.subject_id = person_hzp.party_id
and person_hzp.status = 'A'
and hzr.object_id = pvpp.partner_party_id
and pvpp.partner_group_id is not null
and hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id
and hzr.object_id = org_hzp.party_id and
rel_hzp.party_id=hzr.party_id;
select hzr.subject_id from hz_parties rel,hz_relationships hzr ,pv_partner_profiles pvpp
where rel.party_id=rel_party_id and
hzr.party_id=rel.party_id and
hzr.relationship_code in ('EMPLOYEE_OF' ) and
hzr.object_id=pvpp.partner_party_id;
select responsibility_id, application_id
from fnd_responsibility
where responsibility_key in
(select responsibility_key
from jtf_um_usertype_resp jtur
where jtur.usertype_id = l_usertype_id
and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
union
select responsibility_key
from jtf_um_usertype_subscrip jtus, jtf_um_subscription_resp jtsr
where jtus.usertype_id = l_usertype_id
and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
and jtus.subscription_flag = 'IMPLICIT'
and jtus.subscription_id = jtsr.subscription_id
and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate));
select principal_name
from jtf_um_usertype_role jtur
where jtur.usertype_id = l_usertype_id
and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
union
select jtsr.principal_name
from jtf_um_usertype_subscrip jtus, jtf_um_subscription_role jtsr
where jtus.usertype_id = l_usertype_id
and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
and jtus.subscription_flag = 'IMPLICIT'
and jtus.subscription_id = jtsr.subscription_id
and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate);
select user_id from fnd_user where user_name=l_user_name;
l_sso_enabled := fnd_profile.value('APPS_SSO_USER_CREATE_UPDATE');
fnd_user_pkg.updateUser(
x_user_name => l_user_name,
x_owner => null,
x_start_date => sysdate
);
PROCEDURE user_update (
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_user_name IN VARCHAR2
,p_user_type_key IN VARCHAR2
,p_contact_rel_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
) IS
L_API_NAME CONSTANT VARCHAR2(30) := 'user_update';
l_last_update_date VARCHAR2(2000);
select usertype_id ,nvl(approval_id,-1) from JTF_UM_USERTYPES_B where usertype_key=user_type;
select USERTYPE_REG_ID from jtf_um_usertype_reg reg, fnd_user fu, JTF_UM_USERTYPES_B type where
fu.user_id=reg.user_id and fu.user_name=username and
type.usertype_key=USERTYPE and
reg.usertype_id=type.usertype_id and
reg.user_id=fu.user_id;
select user_id from fnd_user where user_name=l_user_name;
select a.subscription_id from JTF_UM_USERTYPE_SUBSCRIP a,JTF_UM_SUBSCRIPTIONS_B b,JTF_UM_USERTYPES_B c
where a.subscription_id=b.subscription_id
and c.usertype_key=user_type
and a.usertype_id=c.usertype_id;
select SUBSCRIPTION_REG_ID from jtf_um_subscription_reg where user_id=userid and subscription_id=subscriptionid;
select to_char (last_update_date, 'mmddyyyyhh24miss')
from jtf_um_usertype_reg
where usertype_reg_id = to_number (l_user_reg_id);
select partner_id from pv_partner_profiles where partner_party_id=l_party_id;
CURSOR USERTYPE_RESP(user_type VARCHAR2) is select FR.RESPONSIBILITY_ID, UT.APPLICATION_ID, FR.VERSION
FROM JTF_UM_USERTYPE_RESP UT,
FND_RESPONSIBILITY_VL FR,
JTF_UM_USERTYPES_B c
WHERE c.usertype_key=user_type
and UT.USERTYPE_ID = c.usertype_id
AND FR.APPLICATION_ID = UT.APPLICATION_ID
AND FR.RESPONSIBILITY_KEY = UT.RESPONSIBILITY_KEY
AND (UT.EFFECTIVE_END_DATE IS NULL OR UT.EFFECTIVE_END_DATE > SYSDATE)
AND UT.EFFECTIVE_START_DATE < SYSDATE;
CURSOR USERTYPE_ROLES(usertype_id NUMBER) IS SELECT PRINCIPAL_NAME
FROM JTF_UM_USERTYPE_ROLE
WHERE USERTYPE_ID = usertype_id
AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)
AND EFFECTIVE_START_DATE < SYSDATE;
select relationship_code from hz_relationships hzr where hzr.party_id=rel_party_id and hzr.directional_flag='F';
select hzp.status from hz_parties hzp where hzp.party_id=rel_party_id ;
select email_address from hz_parties hzp where hzp.party_id=rel_party_id ;
select pvpp.partner_id, pvpp.PARTNER_GROUP_ID , person_hzp.PERSON_FIRST_NAME, person_hzp.person_last_name,
hzoc.org_contact_id, org_hzp.party_name, rel_hzp.email_address
from HZ_PARTIES PERSON_HZP, HZ_RELATIONSHIPS HZR, PV_PARTNER_PROFILES pvpp, hz_org_contacts hzoc, HZ_PARTIES ORG_HZP,
hz_parties REL_HZP
where HZR.party_id = rel_party_id
and HZR.directional_flag = 'F'
and hzr.relationship_code = 'EMPLOYEE_OF'
and HZR.subject_table_name ='HZ_PARTIES'
and HZR.object_table_name ='HZ_PARTIES'
and hzr.start_date <= SYSDATE
and (hzr.end_date is null or hzr.end_date > SYSDATE)
and hzr.status = 'A'
and hzr.subject_id = person_hzp.party_id
and person_hzp.status = 'A'
and hzr.object_id = pvpp.partner_party_id
and pvpp.partner_group_id is not null
and hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id
and hzr.object_id = org_hzp.party_id and
rel_hzp.party_id=hzr.party_id;
select hzr.subject_id from hz_parties rel,hz_relationships hzr ,pv_partner_profiles pvpp, fnd_user fu
where rel.party_id=rel_party_id and
hzr.party_id=rel.party_id and
hzr.relationship_code in ('EMPLOYEE_OF') and
fu.user_name=l_user_name and
hzr.party_id=fu.customer_id and
hzr.object_id=pvpp.partner_party_id;
select responsibility_id, application_id
from fnd_responsibility
where responsibility_key in
(select responsibility_key
from jtf_um_usertype_resp jtur
where jtur.usertype_id = l_usertype_id
and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
union
select responsibility_key
from jtf_um_usertype_subscrip jtus, jtf_um_subscription_resp jtsr
where jtus.usertype_id = l_usertype_id
and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
and jtus.subscription_flag = 'IMPLICIT'
and jtus.subscription_id = jtsr.subscription_id
and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate));
select principal_name
from jtf_um_usertype_role jtur
where jtur.usertype_id = l_usertype_id
and (jtur.effective_end_date is null or jtur.effective_end_date > sysdate)
union
select jtsr.principal_name
from jtf_um_usertype_subscrip jtus, jtf_um_subscription_role jtsr
where jtus.usertype_id = l_usertype_id
and (jtus.effective_end_date is null or jtus.effective_end_date > sysdate)
and jtus.subscription_flag = 'IMPLICIT'
and jtus.subscription_id = jtsr.subscription_id
and (jtsr.effective_end_date is null or jtsr.effective_end_date > sysdate);
savepoint user_update_pvt;
l_sso_enabled := fnd_profile.value('APPS_SSO_USER_CREATE_UPDATE');
fnd_user_pkg.updateUser(
x_user_name => l_user_name,
x_owner => null,
x_start_date => sysdate
);
ROLLBACK TO user_update_pvt;
hz_utility_v2pub.debug('user_update (-)');
ROLLBACK TO user_update_pvt;
hz_utility_v2pub.debug('user_update (-)');
ROLLBACK TO user_update_pvt;
hz_utility_v2pub.debug('user_update (-)');
END user_update;
,p_update_if_exists IN VARCHAR2
,p_data_block_size IN NUMBER
,x_contact_output_tbl OUT NOCOPY CONTACT_OUTPUT_TBL_TYPE
,x_file_name OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Load_Contacts';
l_update_allowed varchar2(1000);
l_update_if_exists varchar2(1000) :=p_update_if_exists;
select
trim(substr(value,0,(instr(value,',') - 1))),
trim(substr(value,(instr(value,',') + 1)))
from v$parameter where name = 'utl_file_dir';
select to_char(systimestamp,'yyddmmsssss') || '.log' into l_file_name from dual;
if l_contact_details_rec.update_if_exists is not null then
l_update_allowed := l_contact_details_rec.update_if_exists;
elsif l_update_if_exists is not null then
l_update_allowed := l_update_if_exists;
l_log_msg.delete;
,p_update_flag => l_update_allowed
,x_contact_rel_id => l_contact_rel_id
,x_contact_output_rec => l_contact_output_rec
,x_log_msg => l_log_msg
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
) ;