[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inq.rowid,inq.*
FROM igr_i_info_int inq
WHERE inq.interface_run_id = cp_interface_run_id
AND inq.status = '2';
SELECT chi.rowid,chi.*
FROM igr_i_char_int chi
WHERE chi.interface_run_id = cp_interface_run_id
AND chi.status = '2';
SELECT pkg.rowid,pkg.*
FROM igr_i_pkg_int pkg
WHERE pkg.interface_run_id = cp_interface_run_id
AND pkg.status = '2';
SELECT meaning
FROM igs_lookups_view
WHERE lookup_type = cp_lookup_type
AND lookup_code = cp_lookup_code;
SELECT
'X'
FROM
igr_i_info_types_v
WHERE
TRUNC(actual_avail_from_date) <= TRUNC(SYSDATE)
AND TRUNC(actual_avail_to_date) >= TRUNC(SYSDATE)
AND info_type_id = cp_info_type_id;
UPDATE igr_i_info_int
SET
ERROR_CODE = 'E318',
STATUS = '3',
error_text = l_error_text
WHERE INTERFACE_INQ_INFO_ID = p_inq_info_rec.INTERFACE_INQ_INFO_ID ;
Purpose: To Insert the Inquiry Information record
in to the system tables using a TBH call
Known limitations,enhancements,remarks:
Change History
Who When What
*******************************************************************************/
-----------------------------Variable Declaration-----------------------------------------
lv_rowid VARCHAR2(25);
igr_i_a_itype_pkg.insert_row(
x_mode => 'R',
x_rowid => lv_rowid,
x_person_id => p_inq_info_rec.person_id,
x_enquiry_appl_number => p_inq_info_rec.enquiry_appl_number,
x_info_type_id => p_inq_info_rec.info_type_id);
UPDATE igr_i_info_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E322,
error_text = l_error_text
WHERE rowid = p_inq_info_rec.rowid;
UPDATE igr_i_info_int
SET status = cst_s_val_4,
error_code = cst_ec_val_E702,
error_text = l_error_text
WHERE rowid = p_inq_info_rec.rowid;
UPDATE igr_i_info_int
SET status = cst_s_val_1,
error_code = NULL,
error_text = NULL
WHERE rowid = p_inq_info_rec.rowid;
UPDATE igr_i_info_int
SET status = cst_s_val_3,
error_code = l_error_code ,
error_text = l_error_text
WHERE rowid = p_inq_info_rec.rowid;
SELECT
'X'
FROM
igr_i_e_chartyp
WHERE
closed_ind ='N'
AND enquiry_characteristic_type = cp_inquiry_characteristic_type;
UPDATE igr_i_char_int
SET
error_code = 'E319',
status = '3',
error_text = l_error_text
WHERE interface_inq_char_id = p_inq_char_rec.interface_inq_char_id;
Purpose: To Insert the Inquiry Characteristics record being processed
in to the system tables using a TBH call
Known limitations,enhancements,remarks:
Change History
Who When What
*******************************************************************************/
lv_rowid VARCHAR2(25);
igr_i_a_chartyp_pkg.insert_row(
x_rowid => lv_rowid,
x_person_id => p_inq_char_rec.person_id,
x_enquiry_appl_number => p_inq_char_rec.enquiry_appl_number,
x_enquiry_characteristic_type => p_inq_char_rec.inquiry_characteristic_type,
x_mode => 'R');
UPDATE igr_i_char_int
SET status = cst_s_val_3,
error_code = cst_ec_val_E322,
error_text = l_error_text
WHERE rowid = p_inq_char_rec.rowid;
UPDATE igr_i_char_int
SET status = cst_s_val_4,
error_code = cst_ec_val_E702,
error_text = l_error_text
WHERE rowid = p_inq_char_rec.rowid;
UPDATE igr_i_char_int
SET status = cst_s_val_1,
error_code = NULL,
error_text = NULL
WHERE rowid = p_inq_char_rec.rowid;
UPDATE igr_i_char_int
SET status = cst_s_val_3,
error_code = l_error_code ,
error_text = l_error_text
WHERE rowid = p_inq_char_rec.rowid;
SELECT 'X'
FROM IGR_I_PKG_ITEM PKGITM,
AMS_P_DELIVERABLES_V DELIV
WHERE pkgitm.package_item_id = p_inq_pkg_rec.package_item_id
AND PKGITM.PACKAGE_ITEM_ID = DELIV.DELIVERABLE_ID
AND DELIV.KIT_FLAG = 'N'
AND (TRUNC(DELIV.actual_avail_from_date) <= TRUNC(SYSDATE) AND
TRUNC(DELIV.actual_avail_to_date) >= TRUNC(SYSDATE) )
AND (
-- Validate Package item against Inquiry Type => Associated with Information Type/Deliverable Kit => With Package items in it.
EXISTS ( SELECT 1
FROM ams_p_deliv_kit_items_v kitems,igr_i_inquiry_types inq
WHERE kitems.deliverable_kit_part_id = pkgitm.package_item_id
AND kitems.deliverable_kit_id = inq.info_type_id
AND inq.inquiry_type_id = cp_inquiry_type_id
)
-- Don't Validate package items against Academic Interest Category when Package Reduction Indicator is not set('N' or NULL).
OR NVL(cp_package_reduct_ind,'N') = 'N'
-- Don't Validate package items when there are no Academic Interest Categories associated.
OR NOT EXISTS ( SELECT 1
FROM as_sales_lead_lines
WHERE sales_lead_id = cp_sales_lead_id
AND category_id IS NOT NULL
AND category_set_id IS NOT NULL
)
-- Validate package items against Academic Interest Category when Package Reduction Indicator is set to 'Y'.
OR ( cp_package_reduct_ind = 'Y' AND
EXISTS ( SELECT 1
FROM igr_i_pkgitm_assign pia, as_sales_lead_lines lines
WHERE pia.package_item_id = pkgitm.package_item_id
AND pia.product_category_id = lines.category_id
AND pia.product_category_set_id = lines.category_set_id
AND lines.sales_lead_id = cp_sales_lead_id
AND pia.enabled_flag = 'Y'
)
)
);
SELECT inquiry_type_id, pkg_reduct_ind
FROM igr_i_appl_int
WHERE interface_inq_appl_id = cp_interface_inq_appl_id;
UPDATE
igr_i_pkg_int
SET
status = '3', -- 'Error'
error_code = 'E321',
error_text = l_error_text
WHERE
INTERFACE_INQ_PKG_ID = p_inq_pkg_rec.INTERFACE_INQ_PKG_ID;
Purpose: To Insert the Inquiry Packages record
in to the system tables using a TBH call
Known limitations,enhancements,remarks:
Change History
Who When What
*******************************************************************************/
----------------Variable delcarations-------------------------------------------
lv_rowid VARCHAR2(25);
igr_i_a_pkgitm_pkg.insert_row(
x_rowid => lv_rowid,
x_person_id => p_inq_pkg_rec.person_id,
x_enquiry_appl_number => p_inq_pkg_rec.enquiry_appl_number,
x_package_item_id => p_inq_pkg_rec.package_item_id,
x_mailed_dt => NULL,
x_donot_mail_ind => p_inq_pkg_rec.donot_mail_ind, -- added as part of idopa2
x_mode => 'R',
x_ret_status => lv_return_status,
x_msg_data => lv_msg_data,
x_msg_count => lv_msg_count,
x_action => 'Import'
);
UPDATE igr_i_pkg_int
SET status = '4',
error_code = 'E702',
error_text = lv_msg_data
WHERE rowid = p_inq_pkg_rec.rowid;
UPDATE igr_i_pkg_int
SET status = '3',
error_code = 'E322',
error_text = lv_msg_data
WHERE rowid = p_inq_pkg_rec.rowid;
UPDATE igr_i_pkg_int
SET status = '1',
error_code = NULL,
error_text = NULL
WHERE rowid = p_inq_pkg_rec.rowid;
UPDATE igr_i_pkg_int
SET status = '3',
error_code = 'E322',
error_text = l_error_text
WHERE rowid = p_inq_pkg_rec.rowid;
UPDATE igr_i_pkg_int
SET error_code = 'E322',
status = '3',
error_text = l_error_text
WHERE interface_inq_pkg_id = p_inq_pkg_rec.interface_inq_pkg_id;