[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_old_update_who IN NUMBER ,
p_new_update_who IN NUMBER ,
p_old_update_on IN DATE ,
p_new_update_on IN DATE )
AS
gv_other_detail VARCHAR2(255);
v_mil_rec.hist_start_dt := p_old_update_on;
v_mil_rec.hist_end_dt := p_new_update_on;
v_mil_rec.hist_who := p_old_update_who;
IGS_PR_MILESTONE_HST_PKG.INSERT_ROW(
X_ROWID => LV_ROWID,
X_person_id => v_mil_rec.person_id,
X_ca_sequence_number => v_mil_rec.ca_sequence_number,
X_sequence_number => v_mil_rec.sequence_number,
X_hist_start_dt => v_mil_rec.hist_start_dt,
X_hist_end_dt => v_mil_rec.hist_end_dt,
X_hist_who => v_mil_rec.hist_who,
X_milestone_type => v_mil_rec.milestone_type,
X_milestone_status => v_mil_rec.milestone_status,
X_due_dt => v_mil_rec.due_dt,
X_description => v_mil_rec.description,
X_actual_reached_dt => v_mil_rec.actual_reached_dt,
X_preced_sequence_number=> v_mil_rec.preced_sequence_number,
X_ovrd_ntfctn_imminent_days => v_mil_rec.ovrd_ntfctn_imminent_days,
X_ovrd_ntfctn_reminder_days => v_mil_rec.ovrd_ntfctn_reminder_days,
X_ovrd_ntfctn_re_reminder_days => v_mil_rec.ovrd_ntfctn_re_reminder_days,
X_comments => v_mil_rec.comments,
X_Org_Id => v_org_id,
X_MODE => 'R');
p_old_update_who IN NUMBER ,
p_new_update_who IN NUMBER ,
p_old_update_on IN DATE ,
p_new_update_on IN DATE )
AS
gv_other_detail VARCHAR2(255);
-- Insert IGS_RE_THESIS_EXAM history (IGS_RE_THS_EXAM_HIST)
DECLARE
v_teh_rec IGS_RE_THS_EXAM_HIST%ROWTYPE;
v_hist_start_dt IGS_RE_THESIS_EXAM.last_update_date%TYPE;
v_hist_end_dt IGS_RE_THESIS_EXAM.last_update_date%TYPE;
v_hist_who IGS_RE_THESIS_EXAM.last_updated_by%TYPE;
-- last_updated_by and last_update_date columns) then create an IGS_RE_THS_EXAM_HIST history
-- record with the old values (p_old_). Do not set the
-- last_updated_by and last_update_date columns when creating the history record.
IF NVL(p_new_submission_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
NVL(p_old_submission_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
v_teh_rec.submission_dt := p_old_submission_dt;
v_teh_rec.hist_start_dt := p_old_update_on;
v_teh_rec.hist_end_dt := NVL(p_new_update_on,SYSDATE);
v_teh_rec.hist_who := p_old_update_who;
IGS_RE_THS_EXAM_HIST_PKG.INSERT_ROW(
X_ROWID => LV_ROWID,
X_person_id => v_teh_rec.person_id,
X_ca_sequence_number => v_teh_rec.ca_sequence_number,
X_the_sequence_number => v_teh_rec.the_sequence_number,
X_creation_dt => v_teh_rec.creation_dt,
X_hist_start_dt => v_teh_rec.hist_start_dt,
X_hist_end_dt => v_teh_rec.hist_end_dt,
X_hist_who => v_teh_rec.hist_who,
X_submission_dt => v_teh_rec.submission_dt,
X_thesis_exam_type => v_teh_rec.thesis_exam_type,
X_thesis_panel_type => v_teh_rec.thesis_panel_type,
X_tracking_id => v_teh_rec.tracking_id,
X_thesis_result_cd => v_teh_rec.thesis_result_cd,
X_Org_Id => v_org_id,
X_MODE => 'R');
-- Insert tracking item for a thesis_examination_record.
-- The routine returns the tracking ID of the item created.
-- IGS_GE_NOTE: A commit is not done from this routine as it is done as part of the
-- calling routine
DECLARE
cst_s_tracking_type CONSTANT
IGS_TR_TYPE.s_tracking_type%TYPE := 'RES_TEX';
SELECT PERSON_PARTY_ID
FROM fnd_user
WHERE user_id = fnd_global.user_id;
SELECT rsup.person_id
FROM IGS_RE_SPRVSR rsup,
IGS_RE_SPRVSR_TYPE rst
WHERE rsup.ca_person_id = p_person_id AND
rsup.ca_sequence_number = p_ca_sequence_number AND
rst.research_supervisor_type = rsup.research_supervisor_type AND
rst.principal_supervisor_ind = 'Y'
ORDER BY rsup.supervision_percentage DESC;
SELECT tpt.tracking_type
FROM IGS_RE_THS_PNL_TYPE tpt
WHERE tpt.thesis_panel_type = cp_thesis_panel_type;
SELECT ts.tracking_status
FROM IGS_TR_STATUS ts
WHERE ts.s_tracking_status = cst_active AND
ts.closed_ind = 'N'
ORDER BY ts.tracking_status ASC;
SELECT tri.start_dt,
tri.business_days_ind
FROM IGS_TR_ITEM tri
WHERE tri.tracking_id = cp_tracking_id;
SELECT MAX(tsdv.action_dt)
FROM IGS_TR_STEP_V tsdv
WHERE tsdv.tracking_id = cp_tracking_id;
SELECT trs.tracking_step_id,
trs.s_tracking_step_type
FROM IGS_TR_STEP trs
WHERE trs.tracking_id = cp_tracking_id;
SELECT tts.recipient_id
FROM IGS_TR_TYPE_STEP tts
WHERE tracking_type = cp_tracking_type AND
tracking_type_step_id = cp_tracking_type_step_id;
SELECT tpm.person_id
FROM IGS_RE_THS_PNL_MBR tpm,
IGS_RE_THS_PNL_MR_TP tpmt
WHERE tpm.ca_person_id = cp_person_id AND
tpm.ca_sequence_number = cp_ca_sequence_number AND
tpm.the_sequence_number = cp_the_sequence_number AND
tpm.creation_dt = cp_creation_dt AND
tpm.confirmed_dt IS NOT NULL AND
tpmt.panel_member_type = tpm.panel_member_type AND
tpmt.panel_chair_ind = 'Y';
SELECT rowid , IGS_TR_STEP.*
FROM IGS_TR_STEP
WHERE tracking_id = CP_tracking_id AND
tracking_step_id = CP_tracking_step_id;
SELECT rowid , IGS_TR_ITEM.*
FROM IGS_TR_ITEM
WHERE tracking_id = CP_tracking_id;
SAVEPOINT s_before_insert;
ROLLBACK TO s_before_insert;
-- Call routine to update the current step.
IF NOT IGS_TR_GEN_002.trkp_upd_trst (
v_tracking_id,
v_trs_rec.tracking_step_id, -- tracking step ID
v_trs_rec.s_tracking_step_type, -- system tracking step type
NULL, -- action date
NULL, -- completion date
NULL, -- step completion indicator
NULL, -- by pass indicator
v_recipient_id, -- recipient_id
v_message_name) THEN
ROLLBACK TO s_before_insert;
IGS_TR_STEP_PKG.UPDATE_ROW(
X_ROWID => IGS_TR_STEP_REC.ROWID,
X_TRACKING_ID => IGS_TR_STEP_REC.TRACKING_ID,
X_TRACKING_STEP_ID => IGS_TR_STEP_REC.TRACKING_STEP_ID,
X_TRACKING_STEP_NUMBER => IGS_TR_STEP_REC.TRACKING_STEP_NUMBER,
X_DESCRIPTION => IGS_TR_STEP_REC.DESCRIPTION,
X_S_TRACKING_STEP_TYPE => IGS_TR_STEP_REC.S_TRACKING_STEP_TYPE ,
X_COMPLETION_DT => IGS_TR_STEP_REC.COMPLETION_DT,
X_ACTION_DAYS => IGS_TR_STEP_REC.ACTION_DAYS,
X_STEP_COMPLETION_IND => IGS_TR_STEP_REC.STEP_COMPLETION_IND ,
X_BY_PASS_IND => IGS_TR_STEP_REC.BY_PASS_IND ,
X_RECIPIENT_ID => NULL,
--add 3 new fields in the tbh call
-- for tracking dld nov 2001 release bug#1837257
X_STEP_GROUP_ID => IGS_TR_STEP_REC.STEP_GROUP_ID,
X_PUBLISH_IND => IGS_TR_STEP_REC.PUBLISH_IND,
X_STEP_CATALOG_CD => IGS_TR_STEP_REC.STEP_CATALOG_CD,
X_MODE => 'R');
-- Update the target days of the item. This is done in two separate queries
-- as the view does quite a bit of processing and this is considered to be
-- the most efficient approach.
OPEN c_tri (
v_tracking_id);
IGS_TR_ITEM_PKG.UPDATE_ROW(
X_ROWID => IGS_TR_ITEM_REC.ROWID,
X_TRACKING_ID => IGS_TR_ITEM_REC.TRACKING_ID,
X_TRACKING_STATUS => IGS_TR_ITEM_REC.TRACKING_STATUS,
X_TRACKING_TYPE => IGS_TR_ITEM_REC.TRACKING_TYPE,
X_SOURCE_PERSON_ID => IGS_TR_ITEM_REC.SOURCE_PERSON_ID,
X_START_DT => IGS_TR_ITEM_REC.START_DT,
X_TARGET_DAYS => IGS_TR_ITEM_REC.TARGET_DAYS,
X_SEQUENCE_IND => IGS_TR_ITEM_REC.SEQUENCE_IND,
X_BUSINESS_DAYS_IND => IGS_TR_ITEM_REC.BUSINESS_DAYS_IND,
X_ORIGINATOR_PERSON_ID =>IGS_TR_ITEM_REC.ORIGINATOR_PERSON_ID ,
X_S_CREATED_IND => IGS_TR_ITEM_REC.S_CREATED_IND,
--add 3 new fields in the tbh call
-- for tracking dld nov 2001 release bug#1837257
X_COMPLETION_DUE_DT => IGS_TR_ITEM_REC.COMPLETION_DUE_DT,
X_OVERRIDE_OFFSET_CLC_IND => IGS_TR_ITEM_REC.OVERRIDE_OFFSET_CLC_IND ,
X_PUBLISH_IND => IGS_TR_ITEM_REC.PUBLISH_IND,
X_MODE => 'R');
p_old_logical_delete_dt IN DATE ,
p_new_logical_delete_dt IN DATE ,
p_old_embargo_details IN VARCHAR2 ,
p_new_embargo_details IN VARCHAR2 ,
p_old_thesis_topic IN VARCHAR2 ,
p_new_thesis_topic IN VARCHAR2 ,
p_old_citation IN VARCHAR2 ,
p_new_citation IN VARCHAR2 ,
p_old_comments IN VARCHAR2 ,
p_new_comments IN VARCHAR2 ,
p_old_update_who IN NUMBER ,
p_new_update_who IN NUMBER ,
p_old_update_on IN DATE ,
p_new_update_on IN DATE )
AS
gv_other_detail VARCHAR2(255);
-- Insert IGS_RE_THESIS history (IGS_RE_THESIS_HIST)
DECLARE
v_th_rec IGS_RE_THESIS_HIST%ROWTYPE;
v_hist_start_dt IGS_RE_THESIS.last_update_date%TYPE;
v_hist_end_dt IGS_RE_THESIS.last_update_date%TYPE;
v_hist_who IGS_RE_THESIS.last_updated_by%TYPE;
-- last_updated_by and last_update_date columns) then create an IGS_RE_THESIS_HIST history
-- record with the old values (p_old_). Do not set the
-- last_updated_by and last_update_date columns when creating the history record.
IF p_new_title <> p_old_title THEN
v_th_rec.title := p_old_title;
IF NVL(p_new_logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
NVL(p_old_logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
v_th_rec.logical_delete_dt := p_old_logical_delete_dt;
v_th_rec.hist_start_dt := p_old_update_on;
v_th_rec.hist_end_dt := NVL(p_new_update_on,SYSDATE);
v_th_rec.hist_who := p_old_update_who;
IGS_RE_THESIS_HIST_PKG.INSERT_ROW(
X_ROWID => LV_ROWID,
X_person_id => v_th_rec.person_id,
X_ca_sequence_number => v_th_rec.ca_sequence_number,
X_sequence_number => v_th_rec.sequence_number,
X_hist_start_dt => v_th_rec.hist_start_dt,
X_hist_end_dt => v_th_rec.hist_end_dt,
X_hist_who => v_th_rec.hist_who,
X_title => v_th_rec.title,
X_final_title_ind => v_th_rec.final_title_ind,
X_short_title => v_th_rec.short_title,
X_abbreviated_title => v_th_rec.abbreviated_title,
X_thesis_result_cd => v_th_rec.thesis_result_cd,
X_expected_submission_dt => v_th_rec.expected_submission_dt,
X_date_of_library_lodgement => v_th_rec.date_of_library_lodgement,
X_library_catalogue_number => v_th_rec.library_catalogue_number,
X_embargo_expiry_dt => v_th_rec.embargo_expiry_dt,
X_thesis_format => v_th_rec.thesis_format,
X_logical_delete_dt => v_th_rec.logical_delete_dt,
X_embargo_details => v_th_rec.embargo_details,
X_thesis_topic => v_th_rec.thesis_topic,
X_citation => v_th_rec.citation,
X_comments => v_th_rec.comments,
X_Org_Id => v_org_id,
X_MODE => 'R');
p_old_update_who IN NUMBER ,
p_new_update_who IN NUMBER ,
p_old_update_on IN DATE ,
p_new_update_on IN DATE )
AS
gv_other_detail VARCHAR2(255);
-- Insert IGS_RE_THS_PNL_MBR history (IGS_RE_THS_PNL_MR_HS)
DECLARE
v_tpmh_rec IGS_RE_THS_PNL_MR_HS%ROWTYPE;
v_hist_start_dt IGS_RE_THS_PNL_MBR.last_update_date%TYPE;
v_hist_end_dt IGS_RE_THS_PNL_MBR.last_update_date%TYPE;
v_hist_who IGS_RE_THS_PNL_MBR.last_updated_by%TYPE;
-- last_updated_by and last_update_date columns) then create an IGS_RE_THS_EXAM_HIST history
-- record with the old values (p_old_). Do not set the
-- last_updated_by and last_update_date columns when creating the history record.
IF p_new_panel_member_type <> p_old_panel_member_type THEN
v_tpmh_rec.panel_member_type := p_old_panel_member_type;
v_tpmh_rec.hist_start_dt := p_old_update_on;
v_tpmh_rec.hist_end_dt := NVL(p_new_update_on,SYSDATE);
v_tpmh_rec.hist_who := p_old_update_who;
IGS_RE_THS_PNL_MR_HS_PKG.INSERT_ROW(
X_ROWID => LV_ROWID,
X_ca_person_id => v_tpmh_rec.ca_person_id,
X_ca_sequence_number => v_tpmh_rec.ca_sequence_number,
X_the_sequence_number => v_tpmh_rec.the_sequence_number,
X_creation_dt => v_tpmh_rec.creation_dt,
X_person_id => v_tpmh_rec.person_id,
X_hist_start_dt => v_tpmh_rec.hist_start_dt,
X_hist_end_dt => v_tpmh_rec.hist_end_dt,
X_hist_who => v_tpmh_rec.hist_who,
X_panel_member_type => v_tpmh_rec.panel_member_type,
X_confirmed_dt => v_tpmh_rec.confirmed_dt,
X_declined_dt => v_tpmh_rec.declined_dt,
X_anonymity_ind => v_tpmh_rec.anonymity_ind,
X_thesis_result_cd => v_tpmh_rec.thesis_result_cd,
X_paid_dt => v_tpmh_rec.paid_dt,
X_tracking_id => v_tpmh_rec.tracking_id,
X_recommendation_summary => v_tpmh_rec.recommendation_summary,
X_Org_Id => v_org_id,
X_MODE => 'R');
-- Insert tracking item for a IGS_RE_THS_PNL_MBR.
-- The routine returns the tracking ID of the item created.
-- IGS_GE_NOTE: A commit is not done from this routine as it is done as part of
-- the calling routine.
DECLARE
cst_s_tracking_type CONSTANT VARCHAR2(10) := 'RES_TPM';
SELECT PERSON_PARTY_ID
FROM fnd_user
WHERE user_id = fnd_global.user_id;
SELECT rsup.person_id
FROM IGS_RE_SPRVSR rsup,
IGS_RE_SPRVSR_TYPE rst
WHERE rsup.ca_person_id = p_ca_person_id AND
rsup.ca_sequence_number = p_ca_sequence_number AND
rst.research_supervisor_type = rsup.research_supervisor_type AND
rst.principal_supervisor_ind = 'Y'
ORDER BY rsup.supervision_percentage DESC;
SELECT tmpt.tracking_type
FROM IGS_RE_THS_PNL_MR_TP tmpt
WHERE tmpt.panel_member_type = cp_panel_member_type;
SELECT ts.tracking_status
FROM IGS_TR_STATUS ts
WHERE ts.s_tracking_status = 'ACTIVE' AND
ts.closed_ind = 'N'
ORDER BY ts.tracking_status ASC;
SELECT tri.start_dt,
tri.business_days_ind
FROM IGS_TR_ITEM tri
WHERE tri.tracking_id = cp_tracking_id;
SELECT MAX(tsdv.action_dt)
FROM IGS_TR_STEP_V tsdv
WHERE tsdv.tracking_id = cp_tracking_id;
SELECT trs.tracking_step_id,
trs.s_tracking_step_type
FROM IGS_TR_STEP trs
WHERE trs.tracking_id = cp_tracking_id;
SELECT tts.recipient_id
FROM IGS_TR_TYPE_STEP tts
WHERE tracking_type = cp_tracking_type AND
tracking_type_step_id = cp_tracking_type_step_id;
SELECT rowid , IGS_TR_STEP.*
FROM IGS_TR_STEP
WHERE tracking_id = CP_tracking_id AND
tracking_step_id = CP_tracking_step_id;
SELECT rowid , IGS_TR_ITEM.*
FROM IGS_TR_ITEM
WHERE tracking_id = CP_tracking_id;
SAVEPOINT s_before_update;
-- Call routine to insert a tracking item of the appropriate type.
IGS_TR_GEN_002.trkp_ins_trk_item (
v_tracking_status,
v_tracking_type,
p_person_id,
SYSDATE, -- tracking start date
NULL, -- target days
NULL, -- sequence indicator
NULL, -- business days
v_originator_id, -- originator
'Y', -- s_created_ind
v_tracking_id, -- OUT NOCOPY
v_message_name);
ROLLBACK TO s_before_update;
-- Update the recipient ID to the panel member for steps linked to the
-- appropriate s_tracking_step_type.
IF NOT IGS_TR_GEN_002.trkp_upd_trst (
v_tracking_id,
v_trs_rec.tracking_step_id, -- tracking step ID
v_trs_rec.s_tracking_step_type, -- system tracking step type
NULL, -- action date
NULL, -- completion date
NULL, -- step completion indicator
NULL, -- by pass indicator
v_recipient_id, -- recipient_id
v_message_name) THEN
ROLLBACK TO s_before_update;
IGS_TR_STEP_PKG.UPDATE_ROW(
X_ROWID => IGS_TR_STEP_REC.ROWID,
X_TRACKING_ID => IGS_TR_STEP_REC.TRACKING_ID,
X_TRACKING_STEP_ID => IGS_TR_STEP_REC.TRACKING_STEP_ID,
X_TRACKING_STEP_NUMBER => IGS_TR_STEP_REC.TRACKING_STEP_NUMBER,
X_DESCRIPTION => IGS_TR_STEP_REC.DESCRIPTION,
X_S_TRACKING_STEP_TYPE => IGS_TR_STEP_REC.S_TRACKING_STEP_TYPE ,
X_COMPLETION_DT => IGS_TR_STEP_REC.COMPLETION_DT ,
X_ACTION_DAYS => IGS_TR_STEP_REC.ACTION_DAYS,
X_STEP_COMPLETION_IND => IGS_TR_STEP_REC.STEP_COMPLETION_IND ,
X_BY_PASS_IND => IGS_TR_STEP_REC.BY_PASS_IND ,
X_RECIPIENT_ID => NULL,
--add 3 new fields in the tbh call
-- for tracking dld nov 2001 release bug#1837257
X_STEP_GROUP_ID => IGS_TR_STEP_REC.STEP_GROUP_ID,
X_PUBLISH_IND => IGS_TR_STEP_REC.PUBLISH_IND,
X_STEP_CATALOG_CD => IGS_TR_STEP_REC.STEP_CATALOG_CD,
X_MODE => 'R');
-- Update the target days of the item. This is done in two separate queries
-- as the view does quite a bit of processing and this is considered to be
-- the most efficient approach.
OPEN c_tri (
v_tracking_id);
IGS_TR_ITEM_PKG.UPDATE_ROW(
X_ROWID => IGS_TR_ITEM_REC.ROWID,
X_TRACKING_ID => IGS_TR_ITEM_REC.TRACKING_ID,
X_TRACKING_STATUS => IGS_TR_ITEM_REC.TRACKING_STATUS,
X_TRACKING_TYPE => IGS_TR_ITEM_REC.TRACKING_TYPE,
X_SOURCE_PERSON_ID => IGS_TR_ITEM_REC.SOURCE_PERSON_ID,
X_START_DT => IGS_TR_ITEM_REC.START_DT,
X_TARGET_DAYS => IGS_TR_ITEM_REC.TARGET_DAYS,
X_SEQUENCE_IND => IGS_TR_ITEM_REC.SEQUENCE_IND,
X_BUSINESS_DAYS_IND => IGS_TR_ITEM_REC.BUSINESS_DAYS_IND,
X_ORIGINATOR_PERSON_ID =>IGS_TR_ITEM_REC.ORIGINATOR_PERSON_ID ,
X_S_CREATED_IND => IGS_TR_ITEM_REC.S_CREATED_IND,
--add 3 new fields in the tbh call
-- for tracking dld nov 2001 release bug#1837257
X_COMPLETION_DUE_DT => IGS_TR_ITEM_REC.COMPLETION_DUE_DT,
X_OVERRIDE_OFFSET_CLC_IND => IGS_TR_ITEM_REC.OVERRIDE_OFFSET_CLC_IND ,
X_PUBLISH_IND => IGS_TR_ITEM_REC.PUBLISH_IND,
X_MODE => 'R');