[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.rowid,a.*
FROM igr_i_appl_int a
WHERE a.interface_run_id = cp_interface_run_id
AND a.status = '2';
SELECT a.rowid,a.*
FROM igr_i_lines_int a
WHERE a.interface_run_id = cp_interface_run_id
AND a.status = '2';
SELECT
meaning
FROM
igs_lookups_view
WHERE
lookup_type = cp_lookup_type
AND lookup_code = cp_lookup_code;
SELECT
pti.rowid,pti.*
FROM
igs_pe_typ_instances_all pti,
igs_pe_person_types pt
WHERE
pti.person_id = cp_person_id
AND pti.person_type_code = pt.person_type_code
AND pt.system_type = cst_PROSPECT
AND funnel_status IN (cst_IDENTIFIED,cst_INQUIRED,cst_CONTACTED);
SELECT
person_type_code ,
funnel_status
FROM
igs_ad_interface
WHERE
interface_id = cp_interface_id;
SELECT
person_type_code ,
funnel_status
FROM
igs_pe_src_types
WHERE
source_type_id = cp_source_type_id;
SELECT
system_type
FROM
igs_pe_person_types
WHERE
person_type_code = p_person_type_code ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E731'
WHERE interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E732' /* Invalid Person Type */
WHERE interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
igr_inquiry_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_enq_appl_rowid,
x_person_id => p_inquiry_dtls_rec.person_id,
x_enquiry_appl_number => lv_enquiry_appl_number,
x_sales_lead_id => l_sales_lead_id,
x_acad_cal_type => p_inquiry_dtls_rec.acad_cal_type,
x_acad_ci_sequence_number => p_inquiry_dtls_rec.acad_ci_sequence_number,
x_adm_cal_type => p_inquiry_dtls_rec.adm_cal_type,
x_adm_ci_sequence_number => p_inquiry_dtls_rec.adm_ci_sequence_number,
x_enquiry_dt => p_inquiry_dtls_rec.inquiry_dt,
x_registering_person_id => p_inquiry_dtls_rec.registering_person_id,
x_override_process_ind => p_inquiry_dtls_rec.override_process_ind,
x_indicated_mailing_dt => p_inquiry_dtls_rec.indicated_mailing_dt,
x_last_process_dt => p_inquiry_dtls_rec.last_process_dt,
x_comments => p_inquiry_dtls_rec.comments,
x_org_id => igs_ge_gen_003.get_org_id,
x_inq_entry_level_id => p_inquiry_dtls_rec.inquiry_entry_level_id,
x_edu_goal_id => p_inquiry_dtls_rec.edu_goal_id,
x_party_id => p_inquiry_dtls_rec.inquiry_school_of_interest_id,
x_how_knowus_id => p_inquiry_dtls_rec.learn_source_id,
x_who_influenced_id => p_inquiry_dtls_rec.influence_source_id,
x_attribute_category => p_inquiry_dtls_rec.attribute_category,
x_attribute1 => p_inquiry_dtls_rec.attribute1,
x_attribute2 => p_inquiry_dtls_rec.attribute2,
x_attribute3 => p_inquiry_dtls_rec.attribute3,
x_attribute4 => p_inquiry_dtls_rec.attribute4,
x_attribute5 => p_inquiry_dtls_rec.attribute5,
x_attribute6 => p_inquiry_dtls_rec.attribute6,
x_attribute7 => p_inquiry_dtls_rec.attribute7,
x_attribute8 => p_inquiry_dtls_rec.attribute8,
x_attribute9 => p_inquiry_dtls_rec.attribute9,
x_attribute10 => p_inquiry_dtls_rec.attribute10,
x_attribute11 => p_inquiry_dtls_rec.attribute11,
x_attribute12 => p_inquiry_dtls_rec.attribute12,
x_attribute13 => p_inquiry_dtls_rec.attribute13,
x_attribute14 => p_inquiry_dtls_rec.attribute14,
x_attribute15 => p_inquiry_dtls_rec.attribute15,
x_attribute16 => p_inquiry_dtls_rec.attribute16,
x_attribute17 => p_inquiry_dtls_rec.attribute17,
x_attribute18 => p_inquiry_dtls_rec.attribute18,
x_attribute19 => p_inquiry_dtls_rec.attribute19,
x_attribute20 => p_inquiry_dtls_rec.attribute20,
x_s_enquiry_status => p_inquiry_dtls_rec.inquiry_status,
x_source_promotion_id => p_inquiry_dtls_rec.source_promotion_id,
x_person_type_code => lv_new_person_type_code,
x_funnel_status => lv_new_funnel_status,
x_ret_status => lv_return_status,
x_msg_data => lv_msg_data,
x_msg_count => lv_msg_count,
x_inquiry_method_code => p_inquiry_dtls_rec.inquiry_source_type,
x_action => 'Import',
x_pkg_reduct_ind => NVL(p_inquiry_dtls_rec.pkg_reduct_ind,'N')
);
UPDATE igr_i_appl_int
SET status = '4',
error_code = 'E702',
error_text = lv_msg_data
WHERE rowid = p_inquiry_dtls_rec.rowid;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E322',
error_text = lv_msg_data
WHERE rowid = p_inquiry_dtls_rec.rowid;
UPDATE igr_i_appl_int
SET status = '1',
error_code = NULL,
error_text = NULL,
enquiry_appl_number= lv_enquiry_appl_number,
sales_lead_id = l_sales_lead_id
WHERE rowid = p_inquiry_dtls_rec.rowid;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E322',
error_text = l_error_text
WHERE rowid = p_inquiry_dtls_rec.rowid;
/* If insert is NOT successful update status to 3 in table igr_i_appl_int */
p_status := '3';
UPDATE
igr_i_appl_int
SET
status = '3',
error_code = 'E322' ,
error_text = NVL(lv_msg_data,l_sqlerrm)
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
/*cursor to select Birth Date*/
CURSOR c_birth_date(p_person_id igs_pe_person_base_v.person_id%TYPE) IS
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id =p_person_id ;
/*cursor to select inquiry status*/
CURSOR c_inquiry_status (cp_inquiry_status igr_i_appl_int.inquiry_status%TYPE) IS
SELECT
'X'
FROM
igr_i_status_v
WHERE
s_enquiry_status = cp_inquiry_status
AND dsp_closed_ind = 'N';
/*cursor to select inquiry source type */
CURSOR c_inquiry_source_type (cp_inquiry_source_type igr_i_appl_int.inquiry_source_type%TYPE) IS
SELECT
'X'
FROM
fnd_lookup_values
WHERE
LOOKUP_TYPE ='VEHICLE_RESPONSE_CODE'
AND lookup_code = cp_inquiry_source_type
AND enabled_flag ='Y'
AND LANGUAGE = USERENV('LANG')
AND VIEW_APPLICATION_ID = 279
AND SECURITY_GROUP_ID = 0;
/*cursor to select inquiry entry status id */
CURSOR c_inquiry_type_id (cp_inquiry_type_id igr_i_appl_int.inquiry_type_id%TYPE) IS
SELECT
'X'
FROM
igr_i_inquiry_types
WHERE
enabled_flag ='Y' AND
inquiry_type_id = cp_inquiry_type_id;
/*cursor to select inquiry entry level id */
CURSOR c_inquiry_entry_level_id (cp_inquiry_entry_level_id igr_i_appl_int.inquiry_entry_level_id%TYPE) IS
SELECT
inq_entry_level_id
FROM
igr_i_entry_lvls_v
WHERE
closed_ind = 'N'
AND inq_entry_level_id = cp_inquiry_entry_level_id;
/*cursor to select registering person id */
CURSOR c_registering_person_id (cp_registering_person_id igr_i_appl_int.registering_person_id%TYPE) IS
SELECT
'X'
FROM
igs_pe_person_base_v
WHERE
person_id = cp_registering_person_id;
/*cursor to select education goal */
CURSOR c_edu_goal_id (cp_edu_goal_id igr_i_appl_int.edu_goal_id%TYPE) IS
SELECT
'X'
FROM
igs_ad_code_classes cc
WHERE
cc.class = 'EDU_GOALS'
AND NVL(cc.closed_ind,'N') = 'N'
AND cc.code_id = cp_edu_goal_id;
/*cursor to select inquiry school of interest id */
CURSOR c_inq_school_of_interest_id (cp_inq_school_of_interest_id igr_i_appl_int.inquiry_school_of_interest_id%TYPE) IS
SELECT
'X'
FROM
igs_ad_schl_aply_to
WHERE
closed_ind = 'N'
AND sch_apl_to_id = cp_inq_school_of_interest_id;
/*cursor to select learn source id */
CURSOR c_learn_source_id (cp_learn_source_id igr_i_appl_int.learn_source_id%TYPE) IS
SELECT
'X'
FROM
igs_ad_code_classes cc
WHERE
cc.class = 'INQ_HOW_KNOWUS'
AND NVL(cc.closed_ind,'N') = 'N'
AND cc.code_id = cp_learn_source_id;
/*cursor to select influence source id */
CURSOR c_influence_source_id (cp_influence_source_id igr_i_appl_int.influence_source_id%TYPE) IS
SELECT
'X'
FROM
igs_ad_code_classes cc
WHERE
cc.class = 'INQ_WHO_INFLUENCED'
AND NVL(cc.closed_ind,'N') = 'N'
AND cc.code_id = cp_influence_source_id;
/*cursor to select academic cal type and ci sequence number */
CURSOR c_acad_cal_type_ci (p_acad_cal_type igr_i_appl_int.acad_cal_type%TYPE,
p_acad_ci_sequence_number igr_i_appl_int.acad_ci_sequence_number%TYPE) IS
SELECT
'X'
FROM
igs_ca_inst_alt_v ciav,
igs_lookups_view lkupv,
igs_lookups_view lkupv1
WHERE
(ciav.s_cal_cat = 'ACADEMIC' AND ciav.s_cal_status IN ('ACTIVE')
AND (p_acad_cal_type, p_acad_ci_sequence_number) IN
(SELECT
cir.sup_cal_type,
cir.sup_ci_sequence_number
FROM
igs_ca_inst_rel cir
WHERE
cir.sub_cal_type IN
(SELECT
ct.cal_type
FROM
igs_ca_type ct
WHERE
ct.s_cal_cat = 'ADMISSION')))
AND lkupv.lookup_type='CAL_CAT' and lkupv.lookup_code='ACADEMIC'
AND lkupv1.lookup_code = ciav.s_cal_status
AND lkupv1.lookup_type = 'CALENDAR_STATUS';
/*cursor to select admission cal type and ci sequence number */
CURSOR c_adm_cal_type_ci (p_adm_cal_type igr_i_appl_int.adm_cal_type%TYPE,
p_adm_ci_sequence_number igr_i_appl_int.adm_ci_sequence_number%TYPE) IS
SELECT
'X'
FROM
igs_ca_inst_alt_v ciav1,
igs_lookups_view lkupv,
igs_lookups_view lkupv1
WHERE
(ciav1.s_cal_cat = 'ADMISSION' and ciav1.s_cal_status in ('ACTIVE')
AND (p_adm_cal_type, p_adm_ci_sequence_number)
IN
(SELECT
cir.sub_cal_type,
cir.sub_ci_sequence_number
FROM
igs_ca_inst_rel cir
WHERE
cir.sup_cal_type = P_INQUIRY_DTLS_REC.ACAD_CAL_TYPE
AND cir.sup_ci_sequence_number = P_INQUIRY_DTLS_REC.ACAD_CI_SEQUENCE_NUMBER ))
AND lkupv.lookup_type='CAL_CAT' and lkupv.lookup_code = 'ADMISSION'
AND lkupv1.lookup_type = 'CALENDAR_STATUS' and lkupv1.lookup_code = ciav1.s_cal_status;
SELECT
deceased_ind
FROM
igs_pe_hz_parties
WHERE
party_id = cp_party_id;
SELECT
'X'
FROM
ams_p_source_codes_v sc,
ams_lookups lkup
WHERE sc.source_code_id = cp_source_promotion_id
AND sc.status IN ('ACTIVE','COMPLETED')
AND sc.source_type IN ('EVEH','CAMP')
AND sc.source_type = lkup.lookup_code(+)
AND lkup.lookup_type = 'AMS_SYS_ARC_QUALIFIER'
AND start_date < TRUNC (SYSDATE) ;
UPDATE
igr_i_appl_int
SET
status = '3',
error_code = 'E349' , /* Error code for validation to check if system date is greater than system date */
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE
igr_i_appl_int
SET
status = '3',
error_code = 'E583' , /* Error code for validation to check if the person is deceased */
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE
igr_i_appl_int
SET
status = '3',
error_code = 'E528' , /* Error code for validation to check if the person is deceased */
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE
igr_i_appl_int
SET
status = '3',
error_code = 'E302' , /* Error code for validation for field Inquiry Status */
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE
igr_i_appl_int
SET
status = '3',
error_code = 'E303', /*Error code for validation for field Inquiry Source Type*/
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE
igr_i_appl_int
SET
status = '3',
error_code = 'E313', --Error code for validation for field Inquiry Type Id
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E360' ,/*Error code for validation for field Package items Reduction Ind */
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE
igr_i_appl_int
SET
status = '3',
error_code = 'E305' ,/*Error code for validation for field Inquiry Entry Level Id*/
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E306' , /*Error code for validation for field Registering person Id*/
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E307' ,/*Error code for validation for field Override Process Ind */
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E308', /*Error code for validation for field Education Goal Id*/
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E309' ,/*Error code for validation for field Inquiry School of Interest Id*/
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E310' ,/*Error code for validation for field Learn Source Id*/
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E311' ,/*Error code for validation for field Influence Source Id*/
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E008' ,/*Using this Error code for validation for field Flex Field as no error code specified for this*/
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E301' ,/*Using this Error code for validation for field Acad Cal type ci seq number as no specific error code specified for this*/
error_text = l_error_text
WHERE interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE igr_i_appl_int
SET status = '3',
error_code = 'E301' ,/* Using this Error code for validation for field Adm Cal type ci seq number as no specific error code specified*/
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
UPDATE
igr_i_appl_int
SET
status = '3',
error_code = 'E584' , /* Error code if source promotion ID is not correct */
error_text = l_error_text
WHERE
interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
SELECT 'X'
FROM ENI_PROD_DENORM_HRCHY_V EPDHV,
ENI_PROD_DEN_HRCHY_PARENTS_V P,
MTL_CATEGORIES_V C
WHERE P.CATEGORY_ID = EPDHV.CHILD_ID
AND EPDHV.PARENT_ID = C.CATEGORY_ID
AND C.DESCRIPTION = 'OSS Academic Interest'
AND EPDHV.PARENT_ID <> P.CATEGORY_ID
AND P.PURCHASE_INTEREST = 'Y'
AND ( P.DISABLE_DATE IS NULL OR P.DISABLE_DATE > SYSDATE )
AND P.category_id = cp_product_category_id
AND P.category_set_id = cp_product_category_set_id;
UPDATE
igr_i_lines_int
SET
status = 3,
error_code = 'E314',
error_text = l_error_text
WHERE
interface_lines_id = p_inquiry_lines_rec.interface_lines_id;
igr_inquiry_lines_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_rowid,
x_sales_lead_line_id => l_sales_lead_line_id,
x_person_id => v_inquiry_lines_rec.person_id,
x_enquiry_appl_number => v_inquiry_lines_rec.enquiry_appl_number,
x_enquiry_dt => v_inquiry_lines_rec.inquiry_date,
x_inquiry_method_code => v_inquiry_lines_rec.inquiry_source_type,
x_preference => v_inquiry_lines_rec.preference,
x_ret_status => lv_return_status,
x_msg_data => lv_msg_data,
x_msg_count => lv_msg_count,
x_product_category_id => v_inquiry_lines_rec.product_category_id,
x_product_category_set_id => v_inquiry_lines_rec.product_category_set_id
);
UPDATE igr_i_lines_int
SET status = '4',
error_code = 'E702',
error_text = lv_msg_data
WHERE rowid = v_inquiry_lines_rec.rowid;
UPDATE igr_i_lines_int
SET status = '3',
error_code = 'E322',
error_text = lv_msg_data
WHERE rowid = v_inquiry_lines_rec.rowid;
UPDATE igr_i_lines_int
SET status = '1',
error_code = NULL,
error_text = NULL
WHERE rowid = v_inquiry_lines_rec.rowid;
UPDATE igr_i_lines_int
SET status = '3',
error_code = 'E322',
error_text = l_error_text
WHERE rowid = v_inquiry_lines_rec.rowid;
UPDATE igr_i_lines_int
SET status = '3',
error_code = 'E322' ,
error_text = l_error_text
WHERE interface_lines_id = v_inquiry_lines_rec.interface_lines_id;