[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Generate_Password, added the call of fnd_user_pkg.update_user if the primary email of the person has been changed.
| Create_Party, IGS_PE_TYP_INSTANCES_PKG.INSERT_row was commented
| 24-APR-2003 pkpatel Bug No: 2908802
| Modified Create_Fnd_User procedure
| 01-jul-2003 KUMMA, 2803555, Added the code to set the tokens for message IGS_PE_WF_EXISTS
| added statement to add the blank lines between successive messages inside procedure Process_Group_ID
| 23-JUL-2003 asbala Bug No:2667343 Replaced Hard coded strings populating l_msg_data and errbuf with
| new messages
| 28-OCT-2003 ssaleem Bug : 3198795 Part of the Dynamic/Static Person Groups modifications,
| Procedure Process_Group_ID is modified.
| 14-DEC-2004 pkpatel Bug 3316053 (Modified the logic for person number/alt id in generate_user.
| Set and Retrieve the new workflow item attribute in generate_user and ceate_party procedures.
| 23-APR-2003 asbala 3528702: Modified cursor c_resp. The job can now assign responsibilities other than those mapped too 'OTHER'.
| 23-JUN-2003 ssawhney bug 3713297 ..need to always select primary address and primary will always be ACTIVE
| 13-Apr-2005 ssaleem Bug 4293911 Fnd User customer Id replaced with person
| party id
| 21-SEP-2005 skpandey Bug: 3663505
| Description: Added ATTRIBUTES 21 TO 24 in create_party procedure to store additional information
| 19-Jan-06 gmaheswa 4869740: random number generators: dbms_random package is replaced by FND_CRYPTO for generating random numbers.
| 02-FEB-2006 skpandey Bug#4937960: Changed call from igs_get_dynamic_sql to get_dynamic_sql as a part of literal fix
| 04-May-2006 pkpatel Bug 5081932(Used the sequence IGS_PE_GEN_USER_S in AUTO_GENERATE_USERNAME method to pass unique value to the event_key)
| 17-May-2006 gmaheswa Bug#5250820, modified Validate_Person to remove the Mutual Exclusive logic of Party number and prefered alternate id
Also modified Dup_Person_Check to process applicant, alumni match not found condition, Multiple match.
Also created new function process_alumni_nomatch_event,generate_party_number.
introduced validate_password logic
| 21-Sep-2006 gmaheswa Bug 5546771 Modified generate_password logic to repeat for 500 times.
+=======================================================================+ */
l_prog_label CONSTANT VARCHAR2(500) :='igs.plsql.igs_pe_userid_pkg';
SELECT upper(substr(person_last_name,1,12)||'.'||substr(person_first_name,1,14))
FROM hz_parties
WHERE party_id = cp_party_id;
/* Select not defined person types */
CURSOR c_setup IS
SELECT 1
FROM igs_lookup_values
WHERE lookup_type = 'SYSTEM_PERSON_TYPES'
AND lookup_code NOT IN
( SELECT s_person_type
FROM igs_pe_typ_rsp_dflt );
l_parameter_list_t.delete;
SELECT 'X'
FROM hz_parties
WHERE party_number = cp_party_num;
UPDATE igs_pe_hz_parties
SET oracle_username =l_user_name
WHERE party_id = l_party_id;
SELECT 1
FROM wf_items
WHERE item_type = item_type
AND item_key like cp_item_key||'%'
AND END_DATE IS NULL;
SELECT l_item_key||(max(NVL(substr(item_key,length(l_item_key)+1,10),0))+1)
FROM wf_items
WHERE item_type = cp_wf_name
AND item_key like cp_item_key||'%' ;
SELECT party_id
FROM hz_parties
WHERE party_number = cp_person_number;
SELECT party_name
FROM igs_or_inst_org_base_v
WHERE party_number = cp_party_number
AND inst_org_ind = 'I';
SELECT description
FROM igs_pe_person_id_typ
WHERE preferred_ind ='Y';
|| The fnd user was updated with the primary email address from hz_contact points
|| so that the mail will go to the person's primary email.
|| gmaheswa 19-Jna-06 4869740: Stubbed
*/
BEGIN
NULL;
SELECT pe.group_cd
FROM igs_pe_persid_group_all pe
WHERE pe.group_id = cp_group_id
AND pe.closed_ind = 'N'
AND pe.create_dt <= SYSDATE;
IF NOT FND_SSO_MANAGER.IsUserCreateUpdateAllowed THEN
FND_MESSAGE.SET_NAME('IGS', 'IGS_PE_USER_CRT_N_ALLOWED');
SELECT description
FROM igs_pe_persid_group_all
WHERE group_id = cp_group_id;
l_select VARCHAR2(32767);
SELECT email_address
FROM hz_parties
WHERE party_id = cp_person_id;
SELECT fnd.user_name,end_date
FROM fnd_user fnd
WHERE fnd.person_party_id = p_person_id;
l_select := ' SELECT ' ||
' p.person_id , ' ||
' p.person_number , ' ||
' p.full_name, ' ||
' p.gender sex , ' ||
' p.birth_date, ' ||
' p.title, ' ||
' p.last_name surname, ' ||
' p.first_name given_name , ' ||
' p.known_as preferred_name , ' ||
' p.suffix, ' ||
' p.pre_name_adjunct prefix, ' ||
' p.middle_name, ' ||
' pit.api_person_id alternate_id ' ||
' FROM ' ||
' igs_pe_person_base_v p, ' ||
' igs_pe_person_id_type_v pit ' ||
' WHERE ' ||
' p.person_id = pit.pe_person_id (+) AND ' ||
' p.person_id IN ';
l_select := l_select || '(' || l_str || ')';
OPEN pgroup_refcur FOR l_select USING p_person_group_id ;
OPEN pgroup_refcur FOR l_select;
SELECT c.system_type
FROM igs_pe_typ_instances c
WHERE c.person_id = cp_party_id
AND cp_sysdate BETWEEN c.start_date AND NVL(c.end_date, cp_sysdate);
SELECT st.responsibility_key resp_name,
st.application_short_name apps_name,
rsp.application_id apps_id,
rsp.responsibility_id resp_id
FROM igs_pe_typ_rsp_dflt st,
fnd_responsibility rsp,
fnd_application ap
WHERE st.s_person_type = cp_system_type
AND st.responsibility_key=rsp.responsibility_key
AND ap.application_id =rsp.application_id
AND ap.application_short_name = st.application_short_name;
SELECT responsibility_id
FROM fnd_user_resp_groups_direct
WHERE user_id = cp_user_id
AND responsibility_id = cp_responsibility_id
AND responsibility_application_id = cp_resp_app_id;
FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT(
user_id => p_user_id,
responsibility_id => c_resp_rec.resp_id,
responsibility_application_id => c_resp_rec.apps_id,
security_group_id => 0,
start_date => l_sysdate,
end_date => null,
description => 'IGS WF autoassign');
SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE preferred_ind ='Y';
SELECT email_address
FROM hz_parties
WHERE party_id = cp_person_id;
SELECT fnd_date.canonical_to_date(cp_date)
FROM DUAL;
l_debug_str := 'Before Person Insert';
igs_pe_person_pkg.insert_row(
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_rowid => l_rowid,
x_return_status => l_return_status,
x_person_id => l_person_id,
x_person_number => l_person_number,
x_surname => l_surname,
x_middle_name => l_middle_name,
x_given_names => l_given_name,
x_sex => l_gender,
x_title => l_title,
x_staff_member_ind => '',
x_deceased_ind => 'N',
x_suffix => l_suffix,
x_pre_name_adjunct => l_prefix,
x_archive_exclusion_ind => '',
x_archive_dt => '',
x_purge_exclusion_ind => '',
x_purge_dt => '',
x_deceased_date => null,
x_proof_of_ins => '',
x_proof_of_immu => '',
x_birth_dt => l_birth_dt,
x_salutation => '',
x_oracle_username => l_user_name,
x_preferred_given_name => l_pref_name,
x_email_addr => l_email_address,
x_level_of_qual_id => '',
x_military_service_reg => '',
x_veteran => '',
x_hz_parties_ovn => l_object_version_number,
x_attribute_category => '',
x_attribute1 => '',
x_attribute2 => '',
x_attribute3 => '',
x_attribute4 => '',
x_attribute5 => '',
x_attribute6 => '',
x_attribute7 => '',
x_attribute8 => '',
x_attribute9 => '',
x_attribute10 => '',
x_attribute11 => '',
x_attribute12 => '',
x_attribute13 => '',
x_attribute14 => '',
x_attribute15 => '',
x_attribute16 => '',
x_attribute17 => '',
x_attribute18 => '',
x_attribute19 => '',
x_attribute20 => '',
x_person_id_type => l_id_type,
x_api_person_id => l_alt_id,
x_status => 'A',
x_attribute21 => '',
x_attribute22 => '',
x_attribute23 => '',
x_attribute24 => ''
);
l_debug_str := 'After Person Insert Call: l_return_status: '||l_return_status||'***l_person_id: '||l_person_id||'****l_msg_data/sqlerrm/l_msg_count:'||l_msg_data||'/'||l_msg_count;
UMX_PUB.updateWfAttribute (p_event => p_event,
P_ATTR_NAME => 'PERSON_PARTY_ID',
P_ATTR_VALUE => l_person_id);
UMX_PUB.updateWfAttribute (p_event => p_event,
P_ATTR_NAME => 'PERSON_PARTY_ID',
P_ATTR_VALUE => l_person_id);
l_label := 'igs.plsql.igs_pe_userid_pkg.umx_business_logic: After Successful Insert of Person';
l_debug_str := 'Before insert/update of Email Address: l_new_address: '||l_new_address||'***l_email_address: '||l_email_address;
p_action => 'INSERT',
p_rowid => l_rowid ,
p_status => 'A',
p_owner_table_name => 'HZ_PARTIES',
p_owner_table_id => l_person_id,
P_primary_flag => 'Y',
p_email_format => l_email_format,
p_email_address => l_email_address,
p_return_status => l_return_status,
p_msg_data => l_msg_data,
p_last_update_date => l_date,
p_contact_point_id => l_contact_point_id,
p_contact_point_ovn => l_contact_point_ovn
) ;
SELECT count(*), max(person_id)
FROM igs_pe_person_base_v PE, IGS_PE_PERSON_ID_TYPE_V ALT
WHERE pe.person_id = alt.pe_person_id(+)
AND UPPER(pe.first_name) = UPPER(cp_first_name)
AND UPPER(pe.last_name) = UPPER(cp_last_name)
AND NVL(pe.birth_date,l_default_date) = NVL(cp_birth_date,l_default_date)
AND (cp_pref_alt_id IS NULL OR alt.api_person_id = cp_pref_alt_id )
AND (cp_person_num IS NULL OR pe.person_number = cp_person_num )
AND NVL(pe.gender,'X') = NVL(cp_gender,'X');
SELECT user_id
FROM fnd_user
WHERE person_party_id = cp_party_id;
SELECT 1
FROM igs_pe_alt_pers_id alt, igs_pe_person_id_typ typ
WHERE alt.api_person_id = cp_alt_id
AND typ.person_id_type = alt.person_id_type
AND typ.preferred_ind = 'Y';
SELECT count(*), max(hz.party_id)
FROM igs_pe_person_id_type_v alt, hz_parties hz, hz_person_profiles hzp
WHERE hz.party_id = alt.pe_person_id(+)
AND UPPER(hz.person_first_name) = UPPER(cp_first_name)
AND UPPER(hz.person_last_name) = UPPER(cp_last_name)
AND NVL(hzp.date_of_birth,l_default_date) = NVL(cp_birth_date,l_default_date)
AND (cp_pref_alt_id IS NULL OR alt.api_person_id = cp_pref_alt_id )
AND (cp_person_num IS NULL OR hz.party_number = cp_person_num )
AND NVL(hzp.gender,'X') = NVL(cp_gender,'X')
AND hzp.party_id = hz.party_id
AND SYSDATE BETWEEN hzp.effective_start_date
AND NVL(hzp.effective_end_date,sysdate)
AND hz.POSTAL_CODE= cp_zipcode;
SELECT IGS_PE_GEN_USER_S.nextval
FROM DUAL;
SELECT person_id_type, unique_ind, description, format_mask
FROM igs_pe_person_id_typ
WHERE preferred_ind ='Y';
SELECT IGS_PE_GEN_USER_S.nextval
FROM DUAL;
l_parameter_list_t.delete;
SELECT IGS_PE_GEN_USER_S.nextval
FROM DUAL;
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'HZ_GENDER'
AND view_application_id = 222
AND language = USERENV('LANG')
AND security_group_id = 0
AND lookup_code = cp_gender;
SELECT description
FROM igs_pe_person_id_typ
WHERE person_id_type = cp_pers_id_type;