[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date Date := Sysdate;
L_select VARCHAR2(32767);
SELECT user_id,user_name, customer_id, employee_id
FROM FND_USER
WHERE Person_party_id = Prsn_id AND
SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE+1);
SELECT 1
FROM IGS_SC_PER_ATTR_VALS
WHERE person_id = prsnId AND
user_attrib_id = usrAttrId AND
user_attrib_value = usrAttrVal;
SELECT 1, START_DATE /*while assigning roles check if person is associated with mul FND USER */
FROM WF_LOCAL_USER_ROLES
WHERE user_orig_system_id =UserId AND
user_name=UsrName AND
role_name= RoleName AND
user_orig_system = UsrOrgSystem AND
role_orig_system = RolrOrgSystem AND
role_orig_system_id = RoleOrgSysId; -- and partition_id IS NOT NULL
SELECT NAME
FROM WF_LOCAL_ROLES
WHERE
ORIG_SYSTEM_ID = RoleId AND
ORIG_SYSTEM ='IGS'
AND partition_id=0 ; -- non registered orig systems are stored in partion 0
Select 1
from HZ_RELATIONSHIPS
WHERE
SUBJECT_ID = PrsnId AND
OBJECT_ID = OrgId AND
object_type = 'ORGANIZATION' AND
subject_type = 'PERSON' AND
RELATIONSHIP_CODE = 'EMPLOYEE_OF'
AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE+1)
AND DIRECTIONAL_FLAG='F';
SELECT party_number
FROM hz_parties
WHERE party_id=cp_prsnId;
l_select :=igs_pe_dynamic_persid_group.get_dynamic_sql(l_GrpId, l_status, l_group_type);
l_debug_str := 'Person Group Array. Dynamic Person Group select is '||l_select ;
OPEN c_cur_query FOR L_select USING l_GrpId;
OPEN c_cur_query FOR L_select;
/*Now iterate through the array to insert record into HZ_RELATIONSHIP */
FOR i IN 1..l_org_unit_array.COUNT
LOOP
/* First check whether this attribute already exist */
OPEN DUP_ORGUNIT_CHECK_C(r_rec_query.Person_id,l_org_unit_array(i),'EMPLOYEE_OF','F');
IGS_PE_RELATIONSHIPS_PKG.CREATUPDATE_PARTY_RELATIONSHIP(
p_action => 'INSERT',
p_subject_id => r_rec_query.Person_id,
p_object_id => l_org_unit_array(i),
p_party_relationship_type => 'EMPLOYMENT',
p_relationship_code => 'EMPLOYEE_OF',
p_comments => NULL,
p_start_date => trunc(SYSDATE),
p_end_date => null,
p_last_update_date => l_last_update_date,
p_return_status => l_Ret_Status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_party_relationship_id => l_party_rel_id,
p_party_id => l_party_id,
p_party_number => l_party_number,
p_caller => 'NOT_FAMILY',
P_Object_Version_Number => l_object_version,
P_Primary => null,
P_Secondary => null,
P_Joint_Salutation => null,
P_Next_To_Kin => null,
P_Rep_Faculty => null,
P_Rep_Staff => null,
P_Rep_Student => null,
P_Rep_Alumni => null,
p_directional_flag => 'F');
/*Now iterate through Locations array to insert record into igs_sc_per_attr_vals */
FOR i IN 1..l_locations_array.COUNT
LOOP
/* First check that user does not have this attribute already assigned */
OPEN IGS_SC_DUP_C(r_rec_query.Person_id,6,l_locations_array(i));
IGS_SC_PER_ATTR_VALS_PKG.INSERT_ROW(
x_rowid => l_rowId,
x_person_id =>r_rec_query.Person_id,
x_user_attrib_id => 6,
x_user_attrib_value => l_locations_array(i)
);
/*Now iterate through PgmTypes array to insert record into igs_sc_per_attr_vals */
for i IN 1..l_pgmtypes_array.COUNT
LOOP
OPEN IGS_SC_DUP_C(r_rec_query.Person_id,7,l_pgmtypes_array(i));
IGS_SC_PER_ATTR_VALS_PKG.INSERT_ROW(
x_rowid => l_rowId,
x_person_id =>r_rec_query.Person_id,
x_user_attrib_id => 7,
x_user_attrib_value => l_pgmtypes_array(i)
);
/*Now iterate through Unit Md array to insert record into igs_sc_per_attr_vals */
FOR i IN 1..l_unitmd_array.COUNT
LOOP
OPEN IGS_SC_DUP_C(r_rec_query.Person_id,8,l_unitmd_array(i));
IGS_SC_PER_ATTR_VALS_PKG.INSERT_ROW(
x_rowid => l_rowId,
x_person_id =>r_rec_query.Person_id,
x_user_attrib_id => 8,
x_user_attrib_value => l_unitmd_array(i)
);
/*Now iterate through UserRoles array to insert record into wf_local_user_roles */
FOR i IN 1..l_usrroles_array.COUNT
LOOP
IF l_enddate_array(i) IS NULL THEN
l_Inf_date := null ; --remove the logic for infinite date.
/*Role is already assigned Update the end_date */
IGS_SC_DATA_SEC_APIS_PKG.Update_Local_User_Role(
p_api_version => 1.0,
p_user_name => l_user_name,
p_role_name => l_role_name,
p_user_orig_system => l_wf_orig_ref,
p_user_orig_system_id => l_wf_orig_id,
p_role_orig_system => 'IGS',
p_role_orig_system_id => l_usrroles_array(i),
p_start_date => NVL(l_DupStartDate,TRUNC(SYSDATE)),
p_expiration_date => l_Inf_date,
p_security_group_id => 0,
x_return_status => l_return_status,
x_return_message => l_return_message);
IGS_SC_DATA_SEC_APIS_PKG.Insert_Local_User_Role(
p_api_version => 1.0,
p_user_name => l_user_name,
p_role_name => l_role_name,
p_user_orig_system => l_wf_orig_ref,
p_user_orig_system_id => l_wf_orig_id,
p_role_orig_system => 'IGS',
p_role_orig_system_id => l_usrroles_array(i),
p_start_date => trunc(SYSDATE), --sysdate, --l_startdate_array(i),
p_expiration_date => l_Inf_date , --sysdate+2, --l_enddate_array(i),
p_security_group_id => 0,
x_return_status => l_return_status,
x_return_message => l_return_message);