[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
rghosh 10-May-2005 Removed the procedure update_mailed_dt_inq_info and
replaced the calls to the proceure update_mailed_dt_inq_info
with update_mailed_dt. This is the new functionality
from the bug 4354270
(reverse chronological order - newest change first)
*****************************************************************************/
PROCEDURE admp_upd_eap_avail(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_process_status IN VARCHAR2 ,
p_package_unavailable IN VARCHAR2 ,
p_package_incomplete IN VARCHAR2 ,
p_responsible_user IN NUMBER,
p_default_user IN NUMBER,
p_inq_src_type IN VARCHAR2,
p_product_category_id IN NUMBER ,
p_inq_date_low IN VARCHAR2 ,
p_inq_date_high IN VARCHAR2 ,
p_inq_info_type IN igr_i_info_types_v.info_type_id%TYPE ) -- New param added as part of IDOPA2
IS
/****************************************************************************
Created By :
Date Created On :
Purpose :
Change History
Who When What
pkpatel 04-JUN-2001 Erased the parameter p_inquiry_date and added parameters to
select person records as per the date range and according to
Inquiry source type and Inquiry Entry Status
pkpatel 13-JUN-2001 Modified the parameter p_inq_entry_stat_id from Mandatory to Default
nshee 13-JAN-2003 As part of fix for bug 2645948, Inquiry source type is not to be a mandatory parameter.
Changed definition of cursor c_eap_es
sjlaport 07-MAR-2005 Changed definition of cursor c_eap_es for APC - bug #3799487
*****************************************************************************/
--declared variable to convert from VARCHAR2 to DATE
p_enquiry_dt_high DATE;
SELECT es.enquiry_status
FROM igr_i_status_v es
WHERE es.s_enquiry_status = cp_enquiry_status AND
es.enabled_flag = 'Y';
SELECT eap.*
FROM igr_i_appl eap,
igr_i_status_v es
WHERE ((eap.enquiry_dt >= p_enquiry_dt_low OR p_enquiry_dt_low IS NULL) AND
(eap.enquiry_dt <= p_enquiry_dt_high OR p_enquiry_dt_high IS NULL)) AND
eap.enquiry_status = es.enquiry_status AND
es.s_enquiry_status = cp_s_enquiry_status AND
(eap.inquiry_method_code = p_inq_src_type OR p_inq_src_type IS NULL) AND
(p_product_category_id IS NULL OR EXISTS (SELECT 'X' FROM igr_i_a_lines_v alin
WHERE alin.person_id = eap.person_id AND
alin.enquiry_appl_number = eap.enquiry_appl_number AND
alin.product_category_id = p_product_category_id )) AND
(p_inq_info_type IS NULL OR EXISTS (SELECT 'X' FROM igr_i_a_itype ityp
WHERE ityp.person_id = eap.person_id AND
ityp.enquiry_appl_number = eap.enquiry_appl_number AND
ityp.info_type_id = p_inq_info_type))
FOR UPDATE OF eap.enquiry_status, eap.last_process_dt NOWAIT;
SELECT 'X'
FROM igr_i_a_pkgitm eapmpi,
igr_i_pkg_items_v epi
WHERE eapmpi.person_id = cp_person_id AND
eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
eapmpi.mailed_dt IS NULL AND
eapmpi.package_item_id = epi.package_item_id ;
SELECT 'X'
FROM igr_i_a_pkgitm eapmpi,
igr_i_pkg_items_v epi
WHERE eapmpi.person_id = cp_person_id AND
eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
eapmpi.mailed_dt IS NULL AND
eapmpi.package_item_id = epi.package_item_id AND
(epi.actual_avail_from_date > SYSDATE OR
epi.actual_avail_to_date < SYSDATE);
SELECT 'X'
FROM igr_i_a_pkgitm eapmpi,
igr_i_pkg_items_v epi
WHERE eapmpi.person_id = cp_person_id AND
eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
eapmpi.mailed_dt IS NULL AND
eapmpi.package_item_id = epi.package_item_id AND
(epi.actual_avail_from_date <= SYSDATE) AND
(epi.actual_avail_to_date >= SYSDATE) AND
NVL(eapmpi.donot_mail_ind,'N') = 'N';
SELECT 'X'
FROM igr_i_a_pkgitm eapmpi, --Transaction table holding Package items of the inquiry instance
igr_i_pkg_items_v epi --Setup (joined with CRM setup to hold all the attributes of Packge Items)
WHERE eapmpi.person_id = cp_person_id AND
eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
eapmpi.mailed_dt IS NULL AND
eapmpi.package_item_id = epi.package_item_id AND
NVL(eapmpi.donot_mail_ind,'N') = 'N' ;
SELECT 'X'
FROM igr_i_a_pkgitm eapmpi, --Transaction table (Inquiry Instance table holding Package items)
igr_i_pkg_items_v epi
WHERE eapmpi.person_id = cp_person_id AND
eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
eapmpi.mailed_dt IS NULL AND
eapmpi.package_item_id = epi.package_item_id AND
(epi.actual_avail_from_date <= SYSDATE) AND
(epi.actual_avail_to_date >= SYSDATE) AND
NVL(eapmpi.donot_mail_ind,'N') = 'N' ;
SELECT 'X'
FROM igr_i_a_pkgitm eapmpi,
igr_i_pkg_items_v epi
WHERE eapmpi.person_id = cp_person_id AND
eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
eapmpi.mailed_dt IS NULL AND
eapmpi.package_item_id = epi.package_item_id AND
(epi.actual_avail_from_date > SYSDATE OR --Checks for UNAVAILABLE condition.
epi.actual_avail_to_date < SYSDATE);
SELECT 'X'
FROM igr_i_a_pkgitm eapmpi,
igr_i_pkg_items_v epi
WHERE eapmpi.person_id = cp_person_id AND
eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
eapmpi.mailed_dt IS NULL AND
eapmpi.package_item_id = epi.package_item_id AND
(epi.actual_avail_from_date <= SYSDATE) AND --Checks for UNAVAILABLE condition.
(epi.actual_avail_to_date >= SYSDATE) AND
NVL(eapmpi.donot_mail_ind,'N') = 'N';
SELECT group_cd
FROM igs_pe_persid_group_all
WHERE group_id = cp_group_id;
PROCEDURE update_mailed_dt (
p_person_id igr_i_a_pkgitm.person_id%TYPE,
p_enquiry_appl_number igr_i_a_pkgitm.enquiry_appl_number%TYPE,
p_mailed_dt igr_i_a_pkgitm.mailed_dt%TYPE)
IS
/****************************************************************************
Created By :
Date Created On :
Purpose :
Change History
Who When What
(reverse chronological order - newest change first)
*****************************************************************************/
BEGIN -- update_mailed_dt
-- This procedure updates the mailed date of available packaged items.
DECLARE
-- Modified the following cursor as part of build of CRM recruitment changes. Bug: 2664699
CURSOR c_eapmpi_epi IS
SELECT eapmpi.ROWID, eapmpi.*
FROM igr_i_a_pkgitm eapmpi,
igr_i_pkg_items_v epi -- Package Items setup view
WHERE eapmpi.person_id = p_person_id AND
eapmpi.enquiry_appl_number = p_enquiry_appl_number AND
eapmpi.mailed_dt IS NULL AND
eapmpi.package_item_id = epi.package_item_id AND
(epi.actual_avail_from_date <= SYSDATE) AND
(epi.actual_avail_to_date >= SYSDATE) AND
NVL(eapmpi.donot_mail_ind,'N') = 'N'
FOR UPDATE OF eapmpi.mailed_dt NOWAIT;
igr_i_a_pkgitm_pkg.update_row (
X_Mode => 'R',
X_RowId => rec_igs_in_applml_pkgitm.rowid,
X_Person_Id => rec_igs_in_applml_pkgitm.person_id,
X_Enquiry_Appl_Number => rec_igs_in_applml_pkgitm.enquiry_appl_number,
X_Package_Item_id => rec_igs_in_applml_pkgitm.package_item_id,
X_Mailed_Dt => p_mailed_dt,
X_donot_mail_ind => 'Y',
x_action => l_action,
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count
);
Fnd_Message.Set_Token('NAME','IGS_AD_GEN_012.update_mailed_dt');
END update_mailed_dt;
/***********New local procedure added as part of IDOPA2 to update mailed_dt in case of inq_info_type not null ******/
PROCEDURE create_person_id_group_secur (
pl_group_id IGS_PE_PRSID_GRP_SEC.group_id%TYPE)
IS
/****************************************************************************
Created By :
Date Created On :
Purpose :
Change History
Who When What
(reverse chronological order - newest change first)
*****************************************************************************/
BEGIN -- create_person_id_group_secur
-- After creating the IGS_PE_PERSID_GROUP record, create a security record giving
-- select access on it, to the default user specified in the parameters..
DECLARE
lv_rowid VARCHAR2(25);
igs_pe_prsid_grp_sec_pkg.insert_row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Group_Id => pl_group_id,
X_Person_Id => p_default_user,
X_Insert_Ind => 'N',
X_Update_Ind => 'N',
X_Delete_Ind => 'N'
);
SELECT IGS_PE_PERSID_GROUP_GP_ID_S.NEXTVAL
FROM DUAL;
igs_pe_persid_group_pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Group_Id => v_group_id,
X_Group_Cd => l_group_cd,
X_Creator_Person_Id => v_user_id,
X_Description => cst_admission_enquiry || '-' ||IGS_GE_NUMBER.TO_CANN ( l_req_id),
X_Create_Dt => SYSDATE,
X_Closed_Ind => 'N',
X_Comments => Null ,
X_Org_Id => l_org_id
);
SELECT 'X'
FROM igs_pe_prsid_grp_mem pigm
WHERE pigm.group_id = cp_group_id AND
pigm.person_id = cp_person_id AND
NVL(TRUNC(start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND
NVL(TRUNC(end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
IGS_PE_PRSID_GRP_MEM_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Group_Id => p_group_id,
X_Person_Id => p_person_id,
X_START_DATE => TRUNC(SYSDATE),
X_END_DATE => null,
X_org_Id => l_org_id
);
update_mailed_dt(
v_person_id,
v_enquiry_appl_number,
v_process_dt);
/* Update only the last process date and enquiry status as complete*/
igr_inquiry_pkg.update_row (
x_rowid => v_eap_es_rec.Row_Id,
x_person_id => v_eap_es_rec.Person_Id,
x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
x_sales_lead_id => v_eap_es_rec.sales_lead_id,
x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
x_last_process_dt => v_process_dt,
x_comments => v_eap_es_rec.Comments,
x_org_id => v_eap_es_rec.org_id,
x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
x_party_id => v_eap_es_rec.Party_Id,
x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
x_attribute_category => v_eap_es_rec.Attribute_Category,
x_attribute1 => v_eap_es_rec.Attribute1,
x_attribute2 => v_eap_es_rec.Attribute2,
x_attribute3 => v_eap_es_rec.Attribute3,
x_attribute4 => v_eap_es_rec.Attribute4,
x_attribute5 => v_eap_es_rec.Attribute5,
x_attribute6 => v_eap_es_rec.Attribute6,
x_attribute7 => v_eap_es_rec.Attribute7,
x_attribute8 => v_eap_es_rec.Attribute8,
x_attribute9 => v_eap_es_rec.Attribute9,
x_attribute10 => v_eap_es_rec.Attribute10,
x_attribute11 => v_eap_es_rec.Attribute11,
x_attribute12 => v_eap_es_rec.Attribute12,
x_attribute13 => v_eap_es_rec.Attribute13,
x_attribute14 => v_eap_es_rec.Attribute14,
x_attribute15 => v_eap_es_rec.Attribute15,
x_attribute16 => v_eap_es_rec.Attribute16,
x_attribute17 => v_eap_es_rec.Attribute17,
x_attribute18 => v_eap_es_rec.Attribute18,
x_attribute19 => v_eap_es_rec.Attribute19,
x_attribute20 => v_eap_es_rec.Attribute20,
x_s_enquiry_status => l_s_enq_stat,
x_enabled_flag => v_eap_es_rec.enabled_flag,
x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
x_source_promotion_id => v_eap_es_rec.source_promotion_id,
x_mode => 'R',
x_action => 'Upd',
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
);
update_mailed_dt(
v_person_id,
v_enquiry_appl_number,
v_process_dt);
/* Update The Enquiry Status obtained above and last process date of inquiry instance */
igr_inquiry_pkg.update_row (
x_rowid => v_eap_es_rec.Row_Id,
x_person_id => v_eap_es_rec.Person_Id,
x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
x_sales_lead_id => v_eap_es_rec.sales_lead_id,
x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
x_last_process_dt => v_process_dt,
x_comments => v_eap_es_rec.Comments,
x_org_id => v_eap_es_rec.org_id,
x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
x_party_id => v_eap_es_rec.Party_Id,
x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
x_attribute_category => v_eap_es_rec.Attribute_Category,
x_attribute1 => v_eap_es_rec.Attribute1,
x_attribute2 => v_eap_es_rec.Attribute2,
x_attribute3 => v_eap_es_rec.Attribute3,
x_attribute4 => v_eap_es_rec.Attribute4,
x_attribute5 => v_eap_es_rec.Attribute5,
x_attribute6 => v_eap_es_rec.Attribute6,
x_attribute7 => v_eap_es_rec.Attribute7,
x_attribute8 => v_eap_es_rec.Attribute8,
x_attribute9 => v_eap_es_rec.Attribute9,
x_attribute10 => v_eap_es_rec.Attribute10,
x_attribute11 => v_eap_es_rec.Attribute11,
x_attribute12 => v_eap_es_rec.Attribute12,
x_attribute13 => v_eap_es_rec.Attribute13,
x_attribute14 => v_eap_es_rec.Attribute14,
x_attribute15 => v_eap_es_rec.Attribute15,
x_attribute16 => v_eap_es_rec.Attribute16,
x_attribute17 => v_eap_es_rec.Attribute17,
x_attribute18 => v_eap_es_rec.Attribute18,
x_attribute19 => v_eap_es_rec.Attribute19,
x_attribute20 => v_eap_es_rec.Attribute20,
x_s_enquiry_status => v_eap_es_rec.s_enquiry_status,
x_enabled_flag => v_eap_es_rec.enabled_flag,
x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
x_source_promotion_id => v_eap_es_rec.source_promotion_id,
x_mode => 'R',
x_action => 'Upd',
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
);
/** This IF section has Original code -only change :: update if donot_mail_ind= 'N'- see cursor in update_mailed_dt*/
-- Check if enquiry has any package items defined.
-- If not, no processing is required.
OPEN c_eapmpi(
v_person_id,
v_enquiry_appl_number);
update_mailed_dt(
v_person_id,
v_enquiry_appl_number,
v_process_dt);
/* Update only the last process date and enquiry status as complete*/
igr_inquiry_pkg.update_row (
x_rowid => v_eap_es_rec.Row_Id,
x_person_id => v_eap_es_rec.Person_Id,
x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
x_sales_lead_id => v_eap_es_rec.sales_lead_id,
x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
x_last_process_dt => v_process_dt,
x_comments => v_eap_es_rec.Comments,
x_org_id => v_eap_es_rec.org_id,
x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
x_party_id => v_eap_es_rec.Party_Id,
x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
x_attribute_category => v_eap_es_rec.Attribute_Category,
x_attribute1 => v_eap_es_rec.Attribute1,
x_attribute2 => v_eap_es_rec.Attribute2,
x_attribute3 => v_eap_es_rec.Attribute3,
x_attribute4 => v_eap_es_rec.Attribute4,
x_attribute5 => v_eap_es_rec.Attribute5,
x_attribute6 => v_eap_es_rec.Attribute6,
x_attribute7 => v_eap_es_rec.Attribute7,
x_attribute8 => v_eap_es_rec.Attribute8,
x_attribute9 => v_eap_es_rec.Attribute9,
x_attribute10 => v_eap_es_rec.Attribute10,
x_attribute11 => v_eap_es_rec.Attribute11,
x_attribute12 => v_eap_es_rec.Attribute12,
x_attribute13 => v_eap_es_rec.Attribute13,
x_attribute14 => v_eap_es_rec.Attribute14,
x_attribute15 => v_eap_es_rec.Attribute15,
x_attribute16 => v_eap_es_rec.Attribute16,
x_attribute17 => v_eap_es_rec.Attribute17,
x_attribute18 => v_eap_es_rec.Attribute18,
x_attribute19 => v_eap_es_rec.Attribute19,
x_attribute20 => v_eap_es_rec.Attribute20,
x_s_enquiry_status => cst_complete,
x_enabled_flag => v_eap_es_rec.enabled_flag,
x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
x_source_promotion_id => v_eap_es_rec.source_promotion_id,
x_mode => 'R',
x_action => 'Upd',
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
);
update_mailed_dt(
v_person_id,
v_enquiry_appl_number,
v_process_dt);
/* Update only the last process date */
igr_inquiry_pkg.update_row (
x_rowid => v_eap_es_rec.Row_Id,
x_person_id => v_eap_es_rec.Person_Id,
x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
x_sales_lead_id => v_eap_es_rec.sales_lead_id,
x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
x_last_process_dt => v_process_dt,
x_comments => v_eap_es_rec.Comments,
x_org_id => v_eap_es_rec.org_id,
x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
x_party_id => v_eap_es_rec.Party_Id,
x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
x_attribute_category => v_eap_es_rec.Attribute_Category,
x_attribute1 => v_eap_es_rec.Attribute1,
x_attribute2 => v_eap_es_rec.Attribute2,
x_attribute3 => v_eap_es_rec.Attribute3,
x_attribute4 => v_eap_es_rec.Attribute4,
x_attribute5 => v_eap_es_rec.Attribute5,
x_attribute6 => v_eap_es_rec.Attribute6,
x_attribute7 => v_eap_es_rec.Attribute7,
x_attribute8 => v_eap_es_rec.Attribute8,
x_attribute9 => v_eap_es_rec.Attribute9,
x_attribute10 => v_eap_es_rec.Attribute10,
x_attribute11 => v_eap_es_rec.Attribute11,
x_attribute12 => v_eap_es_rec.Attribute12,
x_attribute13 => v_eap_es_rec.Attribute13,
x_attribute14 => v_eap_es_rec.Attribute14,
x_attribute15 => v_eap_es_rec.Attribute15,
x_attribute16 => v_eap_es_rec.Attribute16,
x_attribute17 => v_eap_es_rec.Attribute17,
x_attribute18 => v_eap_es_rec.Attribute18,
x_attribute19 => v_eap_es_rec.Attribute19,
x_attribute20 => v_eap_es_rec.Attribute20,
x_s_enquiry_status => v_eap_es_rec.s_enquiry_status,
x_enabled_flag => v_eap_es_rec.enabled_flag,
x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
x_source_promotion_id => v_eap_es_rec.source_promotion_id,
x_mode => 'R',
x_action => 'Upd',
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
);
update_mailed_dt(
v_person_id,
v_enquiry_appl_number,
v_process_dt);
igr_inquiry_pkg.update_row (
x_rowid => v_eap_es_rec.Row_Id,
x_person_id => v_eap_es_rec.Person_Id,
x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
x_sales_lead_id => v_eap_es_rec.sales_lead_id,
x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
x_last_process_dt => v_process_dt,
x_comments => v_eap_es_rec.Comments,
x_org_id => v_eap_es_rec.org_id,
x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
x_party_id => v_eap_es_rec.Party_Id,
x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
x_attribute_category => v_eap_es_rec.Attribute_Category,
x_attribute1 => v_eap_es_rec.Attribute1,
x_attribute2 => v_eap_es_rec.Attribute2,
x_attribute3 => v_eap_es_rec.Attribute3,
x_attribute4 => v_eap_es_rec.Attribute4,
x_attribute5 => v_eap_es_rec.Attribute5,
x_attribute6 => v_eap_es_rec.Attribute6,
x_attribute7 => v_eap_es_rec.Attribute7,
x_attribute8 => v_eap_es_rec.Attribute8,
x_attribute9 => v_eap_es_rec.Attribute9,
x_attribute10 => v_eap_es_rec.Attribute10,
x_attribute11 => v_eap_es_rec.Attribute11,
x_attribute12 => v_eap_es_rec.Attribute12,
x_attribute13 => v_eap_es_rec.Attribute13,
x_attribute14 => v_eap_es_rec.Attribute14,
x_attribute15 => v_eap_es_rec.Attribute15,
x_attribute16 => v_eap_es_rec.Attribute16,
x_attribute17 => v_eap_es_rec.Attribute17,
x_attribute18 => v_eap_es_rec.Attribute18,
x_attribute19 => v_eap_es_rec.Attribute19,
x_attribute20 => v_eap_es_rec.Attribute20,
x_s_enquiry_status => l_s_enq_stat,
x_enabled_flag => v_eap_es_rec.enabled_flag,
x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
x_source_promotion_id => v_eap_es_rec.source_promotion_id,
x_mode => 'R',
x_action => 'Upd',
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
);
igr_inquiry_pkg.update_row (
x_rowid => v_eap_es_rec.Row_Id,
x_person_id => v_eap_es_rec.Person_Id,
x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
x_sales_lead_id => v_eap_es_rec.sales_lead_id,
x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
x_last_process_dt => v_process_dt,
x_comments => v_eap_es_rec.Comments,
x_org_id => v_eap_es_rec.org_id,
x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
x_party_id => v_eap_es_rec.Party_Id,
x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
x_attribute_category => v_eap_es_rec.Attribute_Category,
x_attribute1 => v_eap_es_rec.Attribute1,
x_attribute2 => v_eap_es_rec.Attribute2,
x_attribute3 => v_eap_es_rec.Attribute3,
x_attribute4 => v_eap_es_rec.Attribute4,
x_attribute5 => v_eap_es_rec.Attribute5,
x_attribute6 => v_eap_es_rec.Attribute6,
x_attribute7 => v_eap_es_rec.Attribute7,
x_attribute8 => v_eap_es_rec.Attribute8,
x_attribute9 => v_eap_es_rec.Attribute9,
x_attribute10 => v_eap_es_rec.Attribute10,
x_attribute11 => v_eap_es_rec.Attribute11,
x_attribute12 => v_eap_es_rec.Attribute12,
x_attribute13 => v_eap_es_rec.Attribute13,
x_attribute14 => v_eap_es_rec.Attribute14,
x_attribute15 => v_eap_es_rec.Attribute15,
x_attribute16 => v_eap_es_rec.Attribute16,
x_attribute17 => v_eap_es_rec.Attribute17,
x_attribute18 => v_eap_es_rec.Attribute18,
x_attribute19 => v_eap_es_rec.Attribute19,
x_attribute20 => v_eap_es_rec.Attribute20,
x_s_enquiry_status => cst_acknowlege,
x_enabled_flag => v_eap_es_rec.enabled_flag,
x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
x_source_promotion_id => v_eap_es_rec.source_promotion_id,
x_mode => 'R',
x_action => 'Upd',
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
);
update_mailed_dt(
v_person_id,
v_enquiry_appl_number,
v_process_dt);
igr_inquiry_pkg.update_row (
x_rowid => v_eap_es_rec.Row_Id,
x_person_id => v_eap_es_rec.Person_Id,
x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
x_sales_lead_id => v_eap_es_rec.sales_lead_id,
x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
x_last_process_dt => v_process_dt,
x_comments => v_eap_es_rec.Comments,
x_org_id => v_eap_es_rec.org_id,
x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
x_party_id => v_eap_es_rec.Party_Id,
x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
x_attribute_category => v_eap_es_rec.Attribute_Category,
x_attribute1 => v_eap_es_rec.Attribute1,
x_attribute2 => v_eap_es_rec.Attribute2,
x_attribute3 => v_eap_es_rec.Attribute3,
x_attribute4 => v_eap_es_rec.Attribute4,
x_attribute5 => v_eap_es_rec.Attribute5,
x_attribute6 => v_eap_es_rec.Attribute6,
x_attribute7 => v_eap_es_rec.Attribute7,
x_attribute8 => v_eap_es_rec.Attribute8,
x_attribute9 => v_eap_es_rec.Attribute9,
x_attribute10 => v_eap_es_rec.Attribute10,
x_attribute11 => v_eap_es_rec.Attribute11,
x_attribute12 => v_eap_es_rec.Attribute12,
x_attribute13 => v_eap_es_rec.Attribute13,
x_attribute14 => v_eap_es_rec.Attribute14,
x_attribute15 => v_eap_es_rec.Attribute15,
x_attribute16 => v_eap_es_rec.Attribute16,
x_attribute17 => v_eap_es_rec.Attribute17,
x_attribute18 => v_eap_es_rec.Attribute18,
x_attribute19 => v_eap_es_rec.Attribute19,
x_attribute20 => v_eap_es_rec.Attribute20,
x_s_enquiry_status => cst_acknowlege,
x_enabled_flag => v_eap_es_rec.enabled_flag,
x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
x_source_promotion_id => v_eap_es_rec.source_promotion_id,
x_mode => 'R',
x_action => 'Upd',
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
);
update_mailed_dt(
v_person_id,
v_enquiry_appl_number,
v_process_dt);
/* Update only the last process date and enquiry status as complete*/
igr_inquiry_pkg.update_row (
x_rowid => v_eap_es_rec.Row_Id,
x_person_id => v_eap_es_rec.Person_Id,
x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
x_sales_lead_id => v_eap_es_rec.sales_lead_id,
x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
x_last_process_dt => v_process_dt,
x_comments => v_eap_es_rec.Comments,
x_org_id => v_eap_es_rec.org_id,
x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
x_party_id => v_eap_es_rec.Party_Id,
x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
x_attribute_category => v_eap_es_rec.Attribute_Category,
x_attribute1 => v_eap_es_rec.Attribute1,
x_attribute2 => v_eap_es_rec.Attribute2,
x_attribute3 => v_eap_es_rec.Attribute3,
x_attribute4 => v_eap_es_rec.Attribute4,
x_attribute5 => v_eap_es_rec.Attribute5,
x_attribute6 => v_eap_es_rec.Attribute6,
x_attribute7 => v_eap_es_rec.Attribute7,
x_attribute8 => v_eap_es_rec.Attribute8,
x_attribute9 => v_eap_es_rec.Attribute9,
x_attribute10 => v_eap_es_rec.Attribute10,
x_attribute11 => v_eap_es_rec.Attribute11,
x_attribute12 => v_eap_es_rec.Attribute12,
x_attribute13 => v_eap_es_rec.Attribute13,
x_attribute14 => v_eap_es_rec.Attribute14,
x_attribute15 => v_eap_es_rec.Attribute15,
x_attribute16 => v_eap_es_rec.Attribute16,
x_attribute17 => v_eap_es_rec.Attribute17,
x_attribute18 => v_eap_es_rec.Attribute18,
x_attribute19 => v_eap_es_rec.Attribute19,
x_attribute20 => v_eap_es_rec.Attribute20,
x_s_enquiry_status => cst_complete,
x_enabled_flag => v_eap_es_rec.enabled_flag,
x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
x_source_promotion_id => v_eap_es_rec.source_promotion_id,
x_mode => 'R',
x_action => 'Upd',
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
);
/* Update only the last process date */
igr_inquiry_pkg.update_row (
x_rowid => v_eap_es_rec.Row_Id,
x_person_id => v_eap_es_rec.Person_Id,
x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
x_sales_lead_id => v_eap_es_rec.sales_lead_id,
x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
x_last_process_dt => v_process_dt,
x_comments => v_eap_es_rec.Comments,
x_org_id => v_eap_es_rec.org_id,
x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
x_party_id => v_eap_es_rec.Party_Id,
x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
x_attribute_category => v_eap_es_rec.Attribute_Category,
x_attribute1 => v_eap_es_rec.Attribute1,
x_attribute2 => v_eap_es_rec.Attribute2,
x_attribute3 => v_eap_es_rec.Attribute3,
x_attribute4 => v_eap_es_rec.Attribute4,
x_attribute5 => v_eap_es_rec.Attribute5,
x_attribute6 => v_eap_es_rec.Attribute6,
x_attribute7 => v_eap_es_rec.Attribute7,
x_attribute8 => v_eap_es_rec.Attribute8,
x_attribute9 => v_eap_es_rec.Attribute9,
x_attribute10 => v_eap_es_rec.Attribute10,
x_attribute11 => v_eap_es_rec.Attribute11,
x_attribute12 => v_eap_es_rec.Attribute12,
x_attribute13 => v_eap_es_rec.Attribute13,
x_attribute14 => v_eap_es_rec.Attribute14,
x_attribute15 => v_eap_es_rec.Attribute15,
x_attribute16 => v_eap_es_rec.Attribute16,
x_attribute17 => v_eap_es_rec.Attribute17,
x_attribute18 => v_eap_es_rec.Attribute18,
x_attribute19 => v_eap_es_rec.Attribute19,
x_attribute20 => v_eap_es_rec.Attribute20,
x_s_enquiry_status => cst_acknowlege,
x_enabled_flag => v_eap_es_rec.enabled_flag,
x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
x_source_promotion_id => v_eap_es_rec.source_promotion_id,
x_mode => 'R',
x_action => 'Upd',
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
);
update_mailed_dt(
v_person_id,
v_enquiry_appl_number,
v_process_dt);
/* Update only the last process date and enquiry status as acknowlege*/
igr_inquiry_pkg.update_row (
x_rowid => v_eap_es_rec.Row_Id,
x_person_id => v_eap_es_rec.Person_Id,
x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
x_sales_lead_id => v_eap_es_rec.sales_lead_id,
x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
x_last_process_dt => v_process_dt,
x_comments => v_eap_es_rec.Comments,
x_org_id => v_eap_es_rec.org_id,
x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
x_party_id => v_eap_es_rec.Party_Id,
x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
x_attribute_category => v_eap_es_rec.Attribute_Category,
x_attribute1 => v_eap_es_rec.Attribute1,
x_attribute2 => v_eap_es_rec.Attribute2,
x_attribute3 => v_eap_es_rec.Attribute3,
x_attribute4 => v_eap_es_rec.Attribute4,
x_attribute5 => v_eap_es_rec.Attribute5,
x_attribute6 => v_eap_es_rec.Attribute6,
x_attribute7 => v_eap_es_rec.Attribute7,
x_attribute8 => v_eap_es_rec.Attribute8,
x_attribute9 => v_eap_es_rec.Attribute9,
x_attribute10 => v_eap_es_rec.Attribute10,
x_attribute11 => v_eap_es_rec.Attribute11,
x_attribute12 => v_eap_es_rec.Attribute12,
x_attribute13 => v_eap_es_rec.Attribute13,
x_attribute14 => v_eap_es_rec.Attribute14,
x_attribute15 => v_eap_es_rec.Attribute15,
x_attribute16 => v_eap_es_rec.Attribute16,
x_attribute17 => v_eap_es_rec.Attribute17,
x_attribute18 => v_eap_es_rec.Attribute18,
x_attribute19 => v_eap_es_rec.Attribute19,
x_attribute20 => v_eap_es_rec.Attribute20,
x_s_enquiry_status => cst_acknowlege,
x_enabled_flag => v_eap_es_rec.enabled_flag,
x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
x_source_promotion_id => v_eap_es_rec.source_promotion_id,
x_mode => 'R',
x_action => 'Upd',
x_ret_status => l_ret_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
);
select person records as per the date range and according to
Inquiry source type and Inquiry Entry Status
pkpatel 13-JUN-2001 Modified the parameter p_inq_entry_stat_id from Mandatory to Default
nshee 13-JAN-2003 As part of fix for bug 2645948, Inquiry source type is not to be a mandatory parameter.
Changed definition of cursor c_eap_es
(reverse chronological order - newest change first)
*****************************************************************************/
BEGIN -- admp_del_eap_eitpi
-- Description: This routine will delete all IGR_I_A_PKGITM records
-- associated with an IGR_I_A_ITYPE record which is being deleted.
DECLARE
v_message_name VARCHAR2(30);
SELECT akit.deliverable_kit_part_id
FROM AMS_DELIV_KIT_ITEMS akit,
AMS_P_DELIVERABLES_V apd
WHERE apd.deliverable_name = p_enquiry_information_type
AND apd.deliverable_id = akit.deliverable_kit_id;
SELECT 'X' FROM IGR_I_A_ITYPE eapit,
AMS_DELIV_KIT_ITEMS akit,
AMS_P_DELIVERABLES_V apd
WHERE eapit.person_id = p_person_id AND
eapit.enquiry_appl_number = p_enquiry_appl_number AND
apd.deliverable_name<> p_enquiry_information_type AND
apd.deliverable_id = akit.deliverable_kit_id AND
eapit.info_type_id = akit.deliverable_kit_id AND
akit.deliverable_kit_part_id = cp_enquiry_package_item_id;
SELECT ROWID, eapmpi.*
FROM IGR_I_A_PKGITM eapmpi
WHERE eapmpi.person_id = p_person_id AND
eapmpi.enquiry_appl_number = p_enquiry_appl_number AND
eapmpi.package_item_id = cp_package_item_id
FOR UPDATE OF eapmpi.person_id NOWAIT;
IGR_I_A_PKGITM_PKG.DELETE_ROW (
X_ROWID => V_EAPMPI_DEL.ROWID );
SELECT akit.deliverable_kit_part_id
FROM AMS_P_DELIVERABLES_V apd,
AMS_P_DELIVERABLES_V apd1,
AMS_DELIV_KIT_ITEMS akit
WHERE apd.deliverable_id = akit.deliverable_kit_part_id
AND apd.actual_avail_from_date <= SYSDATE
AND apd.actual_avail_to_date >= SYSDATE
AND akit.deliverable_kit_id = apd1.deliverable_id
AND apd1.deliverable_name = p_enquiry_information_type;
SELECT 'x'
FROM IGR_I_A_PKGITM eapmpi
WHERE eapmpi.person_id = p_person_id AND
eapmpi.enquiry_appl_number = p_enquiry_appl_number AND
eapmpi.package_item_id = cp_package_item_id;
IGR_I_A_PKGITM_Pkg.Insert_Row (
X_Mode => 'R',
X_RowId => lv_rowid,
X_Person_Id => p_person_id,
X_Enquiry_Appl_Number => p_enquiry_appl_number,
X_Package_Item_id => v_eitpi_rec.deliverable_kit_part_id,
X_Mailed_Dt => Null ,
X_donot_mail_ind => 'N' ,--ADDED as part of Impact of IDOPA2--sykrishn
x_ret_status => lv_return_status,
x_msg_data => lv_msg_data,
x_msg_count => lv_msg_count,
X_action => l_action );