[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT instance_set_id, instance_set_name
FROM fnd_object_instance_sets
WHERE instance_set_name IN ('FUN_TRX_BATCHES_SET','FUN_TRX_HEADERS_SET','FUN_DIST_LINES_SET');
select * from (SELECT hzp.party_id AS partyid,
DECODE(xfi.party_id,NULL,'REMOTE','LOCAL') AS local
FROM HZ_PARTIES HZP,
XLE_ENTITY_PROFILES XFI,
HZ_PARTY_USG_ASSIGNMENTS HUA
WHERE HZP.PARTY_TYPE='ORGANIZATION'
AND FUN_TCA_PKG.GET_LE_ID(HZP.PARTY_ID)=XFI.PARTY_ID
AND HUA.PARTY_ID=HZP.PARTY_ID
AND HUA.PARTY_USAGE_CODE ='INTERCOMPANY_ORG'
AND XFI.TRANSACTING_ENTITY_FLAG = 'Y') QRSLT
WHERE (PARTYID not in ( select object_id
from hz_relationships where subject_id = -999
and subject_table_name like 'HZ_PARTIES'
and relationship_code like 'CONTACT_OF'
and Directional_flag = 'F'
and start_date < sysdate and end_date > sysdate ));
update_assign(
p_api_version,
nvl(p_init_msg_list, FND_API.G_FALSE),
p_commit,
x_return_status,
x_msg_count,
x_msg_data,
v_org_record.partyid,
p_person_id,
'Y');
SELECT relationship_id
FROM hz_relationships hzr
WHERE hzr.subject_id=p_cursor_personid
AND hzr.object_id=p_cursor_orgid
AND hzr.relationship_code='CONTACT_OF'
AND hzr.relationship_type='CONTACT'
AND hzr.directional_flag='F'
AND hzr.subject_type='PERSON' ;
SELECT object_version_number
FROM hz_relationships
WHERE relationship_id=p_rel_id ;
l_tca_relationship_record.comments := 'Updated from Oracle Intercompany';
l_module,'calling HZ_PARTY_CONTACT_V2PUB.update_org_contact');
HZ_PARTY_CONTACT_V2PUB.update_org_contact(
p_init_msg_list => nvl(p_init_msg_list, FND_API.G_FALSE),
p_org_contact_rec => l_tca_contact_record,
p_cont_object_version_number => l_cont_object_version_number,
p_rel_object_version_number => l_rel_object_version_number,
p_party_object_version_number => l_party_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
l_module, 'completed call to HZ_PARTY_CONTACT_V2PUB.update_org_contact'
|| ' Status ' || x_return_status);
SELECT hcr.org_contact_role_id, hcr.org_contact_id, hcr.primary_flag, hcr.status
INTO l_org_contact_role_id, l_org_contact_id, l_primary_flag, l_status
FROM hz_relationships hzr, hz_org_contacts hc, hz_org_contact_roles hcr
WHERE hzr.subject_id=p_person_id
AND hzr.object_id=v_org_record.partyid
AND hzr.relationship_code='CONTACT_OF'
AND hzr.relationship_type='CONTACT'
AND hzr.directional_flag='F'
AND hzr.subject_type='PERSON'
AND hzr.relationship_id = hc.party_relationship_id
AND hc.org_contact_id = hcr.org_contact_id;
l_module,'calling HZ_PARTY_CONTACT_V2PUB.update_org_contact_role');
HZ_PARTY_CONTACT_V2PUB.update_org_contact_role(
p_init_msg_list => nvl(p_init_msg_list, FND_API.G_FALSE),
p_org_contact_role_rec => l_tca_contact_role_record,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
l_module, 'completed call to HZ_PARTY_CONTACT_V2PUB.update_org_contact_role'
|| ' Status ' || x_return_status);
update_assign(
p_api_version,
nvl(p_init_msg_list, FND_API.G_FALSE),
p_commit,
x_return_status,
x_msg_count,
x_msg_data,
p_org_id,
p_person_id,
'Y');
* PROCEDURE update_assign
* ----------------------------------------------------
* Updates grants for the specified person to the
* specified organization.
* ---------------------------------------------------*/
PROCEDURE update_assign (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_org_id IN NUMBER,
p_person_id IN NUMBER,
p_status IN VARCHAR2
)
IS
l_fnd_grant_guid fnd_grants.grant_guid%TYPE;
l_module VARCHAR2(100) := 'fun.plsql.fun_security.update_assign';
SELECT grant_guid,start_date
FROM fnd_grants fg
WHERE instance_set_id = p_instance_set_id
AND parameter1 = p_org_id
AND grantee_key = 'HZ_PARTY:'||p_person_id ;
SAVEPOINT update_fun_grant;
l_module, 'callinge fnd_grants_pkg.update_grant');
fnd_grants_pkg.update_grant (
p_api_version => 1,
p_grant_guid => l_grant_guid,
p_start_date => l_start_date,
p_end_date => NULL,
x_success => x_return_status
);
fnd_grants_pkg.update_grant (
p_api_version => 1,
p_grant_guid => l_grant_guid,
p_start_date => l_start_date,
p_end_date => SYSDATE-1,
x_success => x_return_status
);
l_module, 'completed call to fnd_grants_pkg.update_grant '
|| ' Status ' || x_return_status);
l_module, 'callinge fnd_grants_pkg.update_grant for headers');
fnd_grants_pkg.update_grant (
p_api_version => 1,
p_grant_guid => l_grant_guid,
p_start_date => l_start_date,
p_end_date => NULL,
x_success => x_return_status
);
fnd_grants_pkg.update_grant (
p_api_version => 1,
p_grant_guid => l_grant_guid,
p_start_date => l_start_date,
p_end_date => SYSDATE-1,
x_success => x_return_status
);
l_module, 'completed call to fnd_grants_pkg.update_grant for headers'
|| ' Status ' || x_return_status);
l_module, 'calling fnd_grants_pkg.update_grant for dists');
fnd_grants_pkg.update_grant (
p_api_version => 1,
p_grant_guid => l_grant_guid,
p_start_date => l_start_date,
p_end_date => NULL,
x_success => x_return_status
);
fnd_grants_pkg.update_grant (
p_api_version => 1,
p_grant_guid => l_grant_guid,
p_start_date => l_start_date,
p_end_date => SYSDATE-1,
x_success => x_return_status
);
l_module, 'completed call to fnd_grants_pkg.update_grant for dists'
|| ' Status ' || x_return_status);
ROLLBACK TO update_fun_grant;
ROLLBACK TO update_fun_grant;
ROLLBACK TO update_fun_grant;
END update_assign;
SELECT 1
INTO l_exists
from DUAL
where exists (select 1
FROM fnd_grants fg,
fnd_object_instance_sets fis,
fnd_menus fm,
fnd_objects fo
WHERE fm.menu_name='FUN_DATA_ACCESS'
AND fo.obj_name IN ('FUN_TRX_BATCHES','FUN_TRX_HEADERS','FUN_DIST_LINES')
AND fis.instance_set_name IN ('FUN_TRX_BATCHES_SET','FUN_TRX_HEADERS_SET','FUN_DIST_LINES_SET')
AND fg.object_id=fo.object_id
AND fg.instance_set_id=fis.instance_set_id
AND fg.menu_id=fm.menu_id
AND fg.grantee_type='USER'
AND fg.grantee_key='HZ_PARTY:'||p_person_id
AND fg.parameter1=p_org_id) ;
SELECT start_date,end_date
INTO l_start_date,l_end_date
FROM fnd_grants fg,
fnd_object_instance_sets fis,
fnd_menus fm,
fnd_objects fo
WHERE fm.menu_name = 'FUN_DATA_ACCESS'
AND fo.obj_name = 'FUN_TRX_BATCHES'
AND fis.instance_set_name = 'FUN_TRX_BATCHES_SET'
AND fg.object_id = fo.object_id
AND fg.instance_set_id = fis.instance_set_id
AND fg.menu_id = fm.menu_id
AND fg.grantee_type = 'USER'
AND fg.grantee_key = 'HZ_PARTY:'||p_person_id
AND fg.parameter1 = p_org_id
AND ROWNUM = 1;