[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT uo.object_type
FROM user_objects uo
WHERE uo.object_name = cp_object_name AND
uo.object_type IN (cst_table, cst_view);
SELECT ucc.comments
FROM user_col_comments ucc
WHERE ucc.table_name = cp_view_name AND
ucc.column_name = cp_column_name;
SELECT uc.search_condition
FROM user_constraints uc
WHERE uc.constraint_type = 'C' AND
uc.table_name = cp_table_name AND
uc.status = 'ENABLED' AND
uc.constraint_name like '%UCASE_CK%';
SELECT creation_dt , rowid
FROM IGS_GE_S_LOG
WHERE s_log_type = cp_s_log_type AND
creation_dt < cp_days_old_dt
FOR UPDATE of s_log_type NOWAIT;
SELECT creation_dt , rowid
FROM IGS_GE_S_LOG_ENTRY
WHERE s_log_type = cp_s_log_type AND
creation_dt = cp_creation_dt
FOR UPDATE of s_log_type NOWAIT;
SAVEPOINT sp_before_delete;
IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(x_rowid => v_sle_rec.rowid) ;
IGS_GE_S_LOG_PKG.DELETE_ROW(x_rowid => v_sl_rec.rowid);
SELECT IGS_GE_NOTE.* , ROWID
FROM IGS_GE_NOTE
WHERE reference_number = p_reference_number
FOR UPDATE OF reference_number NOWAIT;
SAVEPOINT sp_before_delete;
IGS_GE_NOTE_PKG.DELETE_ROW(X_ROWID => V_NOTE_REC.ROWID );
ROLLBACK TO sp_before_delete;
Removed selection of initial_last_name, initial_name from igs_pe_person_v.
Instead the direct logic to retrieve it from hz_parties was introduced.
skpandey 13-JAN-2006 Bug#4937960
Changed c_per_name cursor definition to optimize query
*/
BEGIN
DECLARE
-- Local Cursors
-- cursor for selection of the person name in seperate parts to allow
-- construction based on the user preferences
CURSOR c_per_name (cp_person_id hz_parties.party_id%TYPE)IS
SELECT p.PERSON_TITLE per_title,
p.PERSON_LAST_NAME per_surname,
NVL(P.KNOWN_AS,p.PERSON_FIRST_NAME) per_first_name,
NVL(P.KNOWN_AS, SUBSTR (P.PERSON_FIRST_NAME, 1, DECODE(INSTR(P.PERSON_FIRST_NAME, ' '), 0, LENGTH(P.PERSON_FIRST_NAME), (INSTR(P.PERSON_FIRST_NAME, ' ')-1)))) || ' ' || P.PERSON_LAST_NAME per_preferred_name ,
P.PERSON_TITLE || ' ' || p.PERSON_FIRST_NAME || ' ' || P.PERSON_LAST_NAME per_title_name ,
p.PERSON_LAST_NAME || ', ' || p.PERSON_TITLE || ' ' || NVL(p.KNOWN_AS,p.PERSON_FIRST_NAME) per_context_block_name
FROM hz_parties p
WHERE p.party_id = cp_person_id;
SELECT padv.person_id padv_person_id,
padv.addr_type padv_addr_type,
padv.addr_line_1 padv_addr_line_1,
padv.addr_line_2 padv_addr_line_2,
padv.addr_line_3 padv_addr_line_3,
padv.addr_line_4 padv_addr_line_4,
padv.city padv_city
FROM IGS_PE_PERSON_ADDR_V padv
WHERE padv.person_id = cp_person_id AND
padv.correspondence_ind = 'Y';
SELECT padv.person_id padv_person_id,
padv.addr_type padv_addr_type,
padv.addr_line_1 padv_addr_line_1,
padv.addr_line_2 padv_addr_line_2,
padv.addr_line_3 padv_addr_line_3,
padv.addr_line_4 padv_addr_line_4,
padv.city padv_city
FROM IGS_PE_PERSON_ADDR_V padv
WHERE padv.person_id = cp_person_id AND
padv.addr_type = cp_addr_type;
SELECT SUBSTR(igs_ge_gen_002.genp_get_initials(person_first_name), 1, 10) || ' ' || person_last_name
FROM hz_parties
WHERE party_id = cp_person_id;
SELECT RTRIM(DECODE(person_last_name,null,'',DECODE(person_first_name,null,person_last_name,person_last_name
|| ', ' ) ) || NVL(person_first_name,'')|| ' '||person_middle_name,' ')
FROM hz_parties
WHERE party_id = cp_person_id;
SELECT ou.description ou_description
FROM IGS_OR_UNIT ou
WHERE ou.org_unit_cd = cp_org_unit_cd;
SELECT oadv.org_unit_cd oadv_org_unit_cd,
oadv.addr_type oadv_addr_type,
oadv.addr_line_1 oadv_addr_line_1,
oadv.addr_line_2 oadv_addr_line_2,
oadv.addr_line_3 oadv_addr_line_3,
oadv.addr_line_4 oadv_addr_line_4,
oadv.city oadv_city
FROM IGS_OR_ADDR oadv
WHERE oadv.org_unit_cd = cp_org_unit_cd AND
oadv.correspondence_ind = 'Y';
SELECT oadv.org_unit_cd oadv_org_unit_cd,
oadv.addr_type oadv_addr_type,
oadv.addr_line_1 oadv_addr_line_1,
oadv.addr_line_2 oadv_addr_line_2,
oadv.addr_line_3 oadv_addr_line_3,
oadv.addr_line_4 oadv_addr_line_4,
oadv.city oadv_city
FROM IGS_OR_ADDR oadv
WHERE oadv.org_unit_cd = cp_org_unit_cd AND
oadv.addr_type = cp_addr_type;
SELECT loc.description loc_description
FROM IGS_AD_LOCATION loc
WHERE loc.location_cd = cp_location_cd;
SELECT
LA.LOCATION_VENUE_CD ladv_location_cd,
HL.ADDRESS1 ladv_addr_line_1,
HL.ADDRESS2 ladv_addr_line_2,
HL.ADDRESS3 ladv_addr_line_3,
HL.ADDRESS4 ladv_addr_line_4,
HL.CITY ladv_city
FROM
HZ_LOCATIONS HL,
IGS_AD_LOCVENUE_ADDR LA
WHERE
HL.LOCATION_ID = LA.LOCATION_ID
AND LA.SOURCE_TYPE = 'L'
AND LA.LOCATION_VENUE_CD = cp_location_cd
AND LA.IDENTIFYING_ADDRESS_FLAG = 'Y' ;
SELECT
LA.LOCATION_VENUE_CD ladv_location_cd,
HL.ADDRESS1 ladv_addr_line_1,
HL.ADDRESS2 ladv_addr_line_2,
HL.ADDRESS3 ladv_addr_line_3,
HL.ADDRESS4 ladv_addr_line_4,
HL.CITY ladv_city
FROM
HZ_LOCATIONS HL,
IGS_AD_LOCVENUE_ADDR LA ,
IGS_PE_LOCVENUE_USE PLU
WHERE
HL.LOCATION_ID = LA.LOCATION_ID
AND LA.LOCATION_VENUE_ADDR_ID = PLU.LOC_VENUE_ADDR_ID
AND LA.SOURCE_TYPE = 'L'
AND LA.LOCATION_VENUE_CD = cp_location_cd
AND PLU.SITE_USE_CODE = cp_addr_type;
SELECT inst.name inst_name
FROM IGS_OR_INSTITUTION inst
WHERE inst.institution_cd = cp_institution_cd;
SELECT iadv.institution_cd iadv_institution_cd,
iadv.addr_type iadv_addr_type,
iadv.addr_line_1 iadv_addr_line_1,
iadv.addr_line_2 iadv_addr_line_2,
iadv.addr_line_3 iadv_addr_line_3,
iadv.addr_line_4 iadv_addr_line_4,
iadv.city iadv_city
FROM IGS_OR_INST_ADDR iadv
WHERE iadv.institution_cd = cp_institution_cd AND
iadv.correspondence_ind = 'Y';
SELECT iadv.institution_cd iadv_institution_cd,
iadv.addr_type iadv_addr_type,
iadv.addr_line_1 iadv_addr_line_1,
iadv.addr_line_2 iadv_addr_line_2,
iadv.addr_line_3 iadv_addr_line_3,
iadv.addr_line_4 iadv_addr_line_4,
iadv.city iadv_city
FROM IGS_OR_INST_ADDR iadv
WHERE iadv.institution_cd = cp_institution_cd AND
iadv.addr_type = cp_addr_type;
p_update_who OUT NOCOPY VARCHAR2 ,
p_update_on OUT NOCOPY DATE )
AS
/* change history
WHO WHEN WHAT
vrathi 10-JUN-2003 BUG:2940810 Added SQL bind variable
*/
v_cursor integer;
v_update_who varchar2(100);
v_update_on date;
v_select_string varchar2(255);
v_select_string := 'SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE FROM '||p_table_name||' WHERE rowid=:row_id';
dbms_sql.parse(v_cursor, v_select_string, dbms_sql.native);
dbms_sql.define_column(v_cursor,1,v_update_who,30);
dbms_sql.define_column(v_cursor,2,v_update_on);
dbms_sql.column_value(v_cursor,1,v_update_who);
dbms_sql.column_value(v_cursor,2,v_update_on);
p_update_who := v_update_who;
p_update_on := v_update_on;
SELECT daiv.alias_val
FROM IGS_CA_DA_INST_V daiv,
IGS_GE_S_GEN_CAL_CON sgcc
WHERE daiv.cal_type = cp_acad_cal_type AND
daiv.ci_sequence_number = cp_acad_ci_sequence_number AND
daiv.dt_alias = sgcc.crs_completion_cutoff_dt_alias
ORDER BY daiv.alias_val ASC;
SELECT full_name FROM igs_pe_person_base_v
WHERE person_id = x_person_id;
SELECT title FROM igs_ad_unit_sets_v
WHERE person_id = x_person_id AND
admission_appl_number = x_admission_appl_number AND
nominated_course_cd = x_nominated_course_cd;