[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
vskumar 30-May-2006 Xbuild3 performance fix. break cursor's select queries in procedure corp_check_interaction_history. e.g cur_c1 to cur_c1_part1 and cur_c1_part2.
***************************************************************/
-- package variable declarations
l_prog_label CONSTANT VARCHAR2(500) :='igs.plsql.igs_co_process';
SELECT document_id, sys_ltr_code, doc_code , version_id
FROM igs_co_mapping_v
WHERE map_id = cp_map_id;
p_select_type IN VARCHAR2,
p_list_id IN NUMBER,
p_person_id IN NUMBER,
p_letter_type IN VARCHAR2,
p_parameter_1 IN VARCHAR2,
p_parameter_2 IN VARCHAR2,
p_parameter_3 IN VARCHAR2,
p_parameter_4 IN VARCHAR2,
p_parameter_5 IN VARCHAR2,
p_parameter_6 IN VARCHAR2,
p_parameter_7 IN VARCHAR2,
p_parameter_8 IN VARCHAR2,
p_parameter_9 IN VARCHAR2,
p_sql_stmt OUT NOCOPY VARCHAR2,
p_exception OUT NOCOPY VARCHAR2
) AS
/*************************************************************
Created By :Nalin Kumar
Date Created on : 05-Feb-2002
Purpose : Based on the selection type this procedure will build and return a select statement.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
Bayadav 24-MAY-2002 Included two system letter codes 'ENADHOC', 'SFADHOC' for adhoc letters as a part of bug 2376434
cheslyn 10-JUN-2002 Chnged the applictaion name from IGS to IGF for message IGF_AW_NO_LIST as a part of bug 2410165
pkpatel 7-MAy-2003 Bug 2940810
Modified for Bind Variable
asbala 19-AUG-2003 3098262:Added check to retrieve only active members of a group
pkpatel 26-AUG-2003 Bug 3110793 (Removed the string WHERE while forming the dynamic clause for LIST for adhoc letters)
ssaleem 29-OCT-2003 Bug 3198795 For select type 'P' 'G', 'SYSTEM' check is removed, since
ADHoc letters were failing due to it
***************************************************************/
l_view_name VARCHAR2(30);
SELECT attach_fid
FROM ibc_citems_v
WHERE CITEM_ID = cp_itm_id
AND language = USERENV('LANG');
SELECT person_id
FROM igs_pe_prsid_grp_mem_all
WHERE group_id = cp_group_id AND SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE);
SELECT document_id
FROM igs_co_mapping
WHERE map_id = cp_map_id;
SELECT file_name
FROM igs_pe_persid_group_all
WHERE group_id = p_parameter_1;
IF p_select_type = 'L' THEN
OPEN cur_c1(p_list_id);
fnd_dsql.add_text('SELECT DISTINCT email_address, person_id FROM ');
fnd_dsql.add_text('SELECT DISTINCT email_address, party_id FROM ');
fnd_dsql.add_text('SELECT DISTINCT email_address, person_id, adm_appl_number,nominated_course_cd, appl_sequence_number FROM ');
fnd_dsql.add_text('SELECT DISTINCT email_address, person_id, adm_appl_number,nominated_course_cd, appl_sequence_number, panel_code FROM ');
fnd_dsql.add_text('SELECT DISTINCT email_address, person_id, adm_appl_number,nominated_course_cd, appl_sequence_number FROM ');
IF p_select_type = 'S' THEN
--
-- If the System Type is of type 'Student' then
--
IF p_letter_type = 'SYSTEM' THEN
IF l_where_clause IS NOT NULL THEN
IF p_sys_ltr_code = 'ADINTRW' THEN
fnd_dsql.add_text(l_view_name || ' WHERE person_id = ' );
ELSIF p_select_type = 'L' THEN
--
-- If the System Type is of type 'List' then get the query string.
--
OPEN c_att_id(l_list_id);
l_query_str := 'SELECT distinct person_id FROM '|| '(' || l_query_text || ')';
ELSIF p_select_type IN ('P','G') THEN
--
-- If the System Type is of type 'Parameter' then get the parameter values.
--
IF p_parameter_1 IS NOT NULL THEN
-- check whether the group is dynamic or not.
-- if file_name is NOT NULL means, the group is dynamic.
l_str := igs_pe_dynamic_persid_group.get_dynamic_sql(p_parameter_1 ,lv_status, l_group_type);
SELECT elapsed_days, repeat_times
FROM igs_co_mapping
WHERE map_id = TO_NUMBER(cp_map_id);
SELECT count(*) cnt, max(requested_date) max_requested_date
FROM igs_co_interaction_history_v
WHERE document_id = cp_document_id
and student_id = cp_person_id
and adm_application_number = cp_application_id
and nominated_course_cd = cp_course_cd
and sequence_number = cp_adm_seq_no
and ( comp_status = 'SUCCESS' OR
request_id IN (SELECT request_id FROM jtf_fm_status));
SELECT count(1) cnt, max(requested_date) max_requested_date
FROM igs_co_interaction_history_v
WHERE document_id in (SELECT CHILD_ITEM_ID
FROM IGS_CO_COV_LTR_RELS
WHERE BASE_ITEM_ID = cp_document_id)
and student_id = cp_person_id
and adm_application_number = cp_application_id
and nominated_course_cd = cp_course_cd
and sequence_number = cp_adm_seq_no
and ( comp_status = 'SUCCESS' OR
request_id IN (SELECT request_id FROM jtf_fm_status));
SELECT count(1) cnt, max(requested_date) max_requested_date
FROM igs_co_interaction_history_v
WHERE document_id = cp_document_id
and student_id = cp_person_id
and ( comp_status = 'SUCCESS' OR
request_id IN (SELECT request_id FROM jtf_fm_status));
SELECT count(1) cnt, max(requested_date) max_requested_date
FROM igs_co_interaction_history_v
WHERE document_id in (SELECT CHILD_ITEM_ID
FROM IGS_CO_COV_LTR_RELS
WHERE BASE_ITEM_ID = cp_document_id)
and student_id = cp_person_id
and ( comp_status = 'SUCCESS' OR
request_id IN (SELECT request_id FROM jtf_fm_status));
SELECT count(1) cnt, max(requested_date) max_requested_date
FROM igs_co_interaction_history_v
WHERE document_id = cp_document_id
and student_id = cp_person_id
and adm_application_number = cp_application_id
and ( comp_status = 'SUCCESS' OR
request_id IN (SELECT request_id FROM jtf_fm_status));
SELECT count(1) cnt, max(requested_date) max_requested_date
FROM igs_co_interaction_history_v
WHERE document_id in (SELECT CHILD_ITEM_ID
FROM IGS_CO_COV_LTR_RELS
WHERE BASE_ITEM_ID = cp_document_id)
and student_id = cp_person_id
and adm_application_number = cp_application_id
and ( comp_status = 'SUCCESS' OR
request_id IN (SELECT request_id FROM jtf_fm_status));
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = TO_NUMBER(cp_person_id);
selected is printer. Changes as part of bug# 2472250
kpadiyar 19-NOV-2002 Added check if hold exists - Correspondence Enhancement - SWS102
Added check for relation between document and query and pass
the content type as relevant.
kumma 07-JUN-2003 2853531, Changed the cursor cur_get_sub to use the lookup type also
Replace the three CRM API calls jtf_fm_request_grp.start_request, jtf_fm_request_grp.get_content_xml and
jtf_fm_request_grp.submit_request into a single API Call of IGS_CO_API.SEND_REQUEST, which inturn calls
the create_fulfillment CRM API.
kumma 21-AUG-2003 3104787, Added the code to check if the query is attached with a template and accordingly pass the content type 'QUERY' or 'DATA'
Modified the else condition to not to consider the Adhoc letters while binding the bind variables.
ssaleem 09-SEP-2004 3630073. Added p_org_unit_id as a new parameter
pacross 11-APR-2005 Implemented code for Correspondance preview and edit fucntionality
***************************************************************/
CURSOR cur_c1 (cp_content_id igs_co_mapping.document_id%TYPE,
cp_map_code igs_co_mapping.map_code%TYPE,
cp_sys_ltr_code igs_co_mapping.sys_ltr_code%TYPE) IS
SELECT map_description, version_id, citem_ver_id
FROM igs_co_mapping_v
WHERE document_id = cp_content_id
AND map_code = cp_map_code
AND sys_ltr_code = cp_sys_ltr_code
AND enable_flag ='Y';
SELECT description
FROM igs_lookups_view
WHERE lookup_code = cp_sys_ltr_code AND
Lookup_type = 'IGS_CO_SYS_LTR_CODE';
SELECT COUNT ('x')
FROM igs_pe_pers_encumb ppe, igs_pe_persenc_effct ppef
WHERE ppe.person_id = p_person_id
AND ppe.person_id = ppef.person_id
AND ppe.encumbrance_type = ppef.encumbrance_type
AND ppe.start_dt = ppef.pen_start_dt
AND ppef.s_encmb_effect_type = 'S_COR_BLK'
AND trunc(ppef.pee_start_dt) <= trunc(sysdate)
AND (ppef.expiry_dt IS NULL OR trunc(ppef.expiry_dt) > trunc(sysdate))
AND trunc(ppe.start_dt) <= trunc(sysdate)
AND (ppe.expiry_dt IS NULL OR trunc(ppe.expiry_dt) > trunc(sysdate));
SELECT ppbv.full_name,ppbv.person_number,fet.description
FROM igs_pe_pers_encumb ppe, igs_pe_persenc_effct ppef,igs_pe_person_base_v ppbv,igs_fi_encmb_type fet
WHERE ppe.person_id = p_person_id
AND ppe.person_id = ppef.person_id
AND ppe.person_id = ppbv.person_id
AND ppe.encumbrance_type = ppef.encumbrance_type
AND ppe.encumbrance_type = fet.encumbrance_type
AND ppe.start_dt = ppef.pen_start_dt
AND ppef.s_encmb_effect_type = 'S_COR_BLK'
AND trunc(ppef.pee_start_dt) <= trunc(sysdate)
AND (ppef.expiry_dt IS NULL OR trunc(ppef.expiry_dt) > trunc(sysdate))
AND trunc(ppe.start_dt) <= trunc(sysdate)
AND (ppe.expiry_dt IS NULL OR trunc(ppe.expiry_dt) > trunc(sysdate));
SELECT person_number,full_name
FROM igs_pe_person_base_v
WHERe person_id = p_person_id;
SELECT 'Y'
FROM ibc_compound_relations
WHERE CITEM_VERSION_ID = (SELECT CITEM_VERSION_ID FROM ibc_citem_versions_b
WHERE CONTENT_ITEM_ID = cp_item_id AND
VERSION_NUMBER = cp_version_id)
AND ATTRIBUTE_TYPE_CODE = 'QUERY'; -- ssawhney modified after OCM migration.
SELECT SYSDATE + NVL(TO_NUMBER(p_reply_days),0)
FROM DUAL;
SELECT NVL(MAX(ipl.INTERVIEW_DATE),MAX(ipm.INTERVIEW_DATE)) - NVL(TO_NUMBER(p_reply_days),0)
FROM igs_ad_panel_dtls ipl ,
igs_ad_pnmembr_dtls ipm
WHERE ipl.panel_dtls_id = ipm.panel_dtls_id AND
ipl.person_id = p_person_id AND
ipl.admission_appl_number =p_adm_appl_number AND
ipl.nominated_course_cd = p_nominated_course_cd AND
ipl.sequence_number = p_appl_sequence_number AND
ipl.panel_code = p_panel_code ;
SELECT P.ADDRESS1, P.ADDRESS2, P.ADDRESS3, P.ADDRESS4, P.POSTAL_CODE,
P.PARTY_NAME, TERR.TERRITORY_SHORT_NAME COUNTRY, P.CITY,
P.STATE, P.PROVINCE, P.COUNTY
FROM
HZ_PARTIES P, FND_TERRITORIES_VL TERR
WHERE
P.PARTY_ID = cp_party_id AND
TERR.TERRITORY_CODE = P.COUNTRY ;
SELECT
CPE.EMAIL_ADDRESS
FROM
HZ_PARTY_SITES PS,
HZ_CONTACT_POINTS CPE
WHERE
PS.PARTY_ID = cp_party_id AND
PS.IDENTIFYING_ADDRESS_FLAG = cp_id_flag AND
CPE.OWNER_TABLE_NAME = cp_owner_tbl AND
CPE.CONTACT_POINT_TYPE = cp_cnt_type AND
CPE.OWNER_TABLE_ID = PS.PARTY_SITE_ID AND
CPE.PRIMARY_FLAG = cp_prim_flag;
SELECT
NVL (CPP.PHONE_AREA_CODE,'*') PHONE_AREA_CODE,
NVL (CPP.PHONE_COUNTRY_CODE,'*') PHONE_COUNTRY_CODE,
NVL (CPP.PHONE_NUMBER,'*') PHONE_NUMBER,
NVL (CPP.PHONE_EXTENSION,'*') PHONE_EXTENSION,
CPP.PHONE_LINE_TYPE
FROM
HZ_PARTY_SITES PS,
HZ_CONTACT_POINTS CPP
WHERE
PS.PARTY_ID = cp_party_id AND
PS.IDENTIFYING_ADDRESS_FLAG = cp_id_flag AND
CPP.OWNER_TABLE_NAME = cp_owner_tbl AND
CPP.CONTACT_POINT_TYPE = cp_cnt_type AND
CPP.PHONE_LINE_TYPE IN(cp_line_type1,cp_line_type2) AND
CPP.OWNER_TABLE_ID = PS.PARTY_SITE_ID AND
CPP.STATUS = cp_status
ORDER BY
CPP.PRIMARY_FLAG DESC ;
l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.before_inserting_into_IGS_CO_PREV_REQS';
l_debug_str := 'just going to insert into IGS_CO_PREV_REQS';
INSERT INTO IGS_CO_PREV_REQS
(CONCURRENT_REQUEST_ID, LETTER_TYPE_CODE, PERSON_ID,
EMAIL_ADDRESS, ORIGINAL_CONTENT_ID, CURRENT_CONTENT_ID, AWARD_YEAR,
SYS_LTR_CODE, ADM_APPL_NUMBER, NOMINATED_COURSE_CD, APPL_SEQUENCE_NUMBER,
FULFILLMENT_REQ, CRM_USER_ID, MEDIA_TYPE_CODE, DESTINATION, FAX_NUMBER, REPLY_DAYS,
PANEL_CODE, REPLY_EMAIL, SENDER_EMAIL, CC_EMAIL, ORG_UNIT_ID, AWD_CAL_TYPE,
AWD_CI_SEQ_NUMBER, ORIGINAL_VERSION_ID, CURRENT_VERSION_ID, EMAIL_SUBJECT, ORIGINAL_CONTENT_XML,
CURRENT_CONTENT_XML, FF_REQUEST_HIST_ID, EXTENDED_HEADER, DISTRIBUTION_ID, REQUEST_STATUS_CODE,
OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE, AWARD_PRD_CD)
VALUES
(fnd_global.conc_request_id, p_letter_type, p_person_id,
p_email_address, p_content_id, p_content_id, p_award_year,
p_sys_ltr_code, p_adm_appl_number, p_nominated_course_cd, p_appl_sequence_number,
p_fulfillment_req, p_crm_user_id, p_media_type, p_destination, p_fax_number, l_reply_days,
p_panel_code, p_reply_email, p_sender_email, p_cc_email, p_org_unit_id, p_awd_cal_type,
p_awd_ci_seq_number, l_citem_ver_id, l_citem_ver_id, l_doc_desc, l_content_xml,
l_content_xml, p_request_id, l_extended_header, NULL, 'CREATED', 1,
FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, NULL, SYSDATE, p_awd_prd_cd);
l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.excep_when_insert';
l_debug_str := 'inside exception section when inserting record in IGS_CO_PREV_REQS and exception is ' || sqlerrm;
l_label := 'igs.plsql.igs_co_process.corp_submit_fulfil_request.after_inserting_into_IGS_CO_PREV_REQS';
l_debug_str := 'just after insert into IGS_CO_PREV_REQS';
igf_aw_gen_004.award_letter_update(
p_person_id => p_person_id,
p_award_year => p_award_year,
p_award_prd_cd => p_award_prd_cd);
igf_aw_gen_004.missing_items_update(
p_person_id => p_person_id,
p_award_year => p_award_year);
igf_aw_gen_004.loan_disbursement_update(
p_person_id => p_person_id,
p_award_year => p_award_year);*/
igs_co_interac_hist_pkg.insert_row(
x_rowid => l_rowid,
x_student_id => TO_NUMBER(p_person_id),
x_request_id => TO_NUMBER(p_request_id),
x_document_id => TO_NUMBER(p_document_id),
x_document_type => p_document_type,
x_sys_ltr_code => p_sys_ltr_code,
x_adm_application_number => p_adm_appl_number,
x_nominated_course_cd => p_nominated_course_cd,
x_sequence_number => p_appl_seq_number,
x_cal_type => p_awd_cal_type,
x_ci_sequence_number => p_awd_ci_seq_number,
x_requested_date => SYSDATE,
x_delivery_type => p_delivery_type,
x_version_id => p_version_id
);
l_label := 'igs.plsql.igs_co_process.corp_post_process.afterinteractioninsert';
clause for student selection.
Know limitations, enhancements or remarks
Change History
Who When What
ssaleem 28-OCT-2003 Bug : 3198795
Part of the Dynamic/Static Person Groups modifications,
In places where person group id is included to the SQL, a condition 1=1 is replaced.
npalanis 23-OCT-2002 Bug : 2608360
residency_status_id and residency_class_id is being removed from igs_ad_resi_letter_v
and the code class is being moved to igs_lookups therefore those are changed to
residency_status_cd and residency_class_cd.
pkpatel 7-MAy-2003 Bug 2940810
Modified for Bind Variable
(reverse chronological order - newest change first)
***************************************************************/
l_pers_group_id igs_ad_missing_items_letter_v.pers_group_id%TYPE;
against CRM Interaction History and update the OSS Interaction table.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
Bayadav 24-MAY-2002 Included two system letter codes 'ENADHOC', 'SFADHOC' for adhoc letters as a part of bug 2376434
kpadiyar 18-NOV-2002 Removed the calls to the igs_co_interac_hist_pkg.update_row as the only column being updated was the outcome_status column
and as this column has been obsoleted all the relevant calls to the update row have been removed.
gmaheswa 5-Jan-2004 Bug 4869737 Added a call to SET_ORG_ID to disable OSS for R12.
***************************************************************/
CURSOR c_crm_id(cp_request_id NUMBER) IS
SELECT outcome_code --INTO l_request_status
FROM jtf_fm_request_history
WHERE hist_req_id = TO_NUMBER(cp_request_id);
SELECT hist.rowid row_id, hist.*
FROM igs_co_interac_hist hist
WHERE request_id = TO_NUMBER(cp_request_id);
CURSOR cur_gen_update IS
SELECT request_id,
comp_status
FROM igs_co_interaction_history_v
WHERE comp_status IN ('SUBMITTED'); --Modified by Prajeesh to change NOT IN to IN as it will never change SUBMITED TO OTHER
l_cur_gen_update cur_gen_update%ROWTYPE;
OPEN cur_gen_update;
FETCH cur_gen_update INTO l_cur_gen_update;
IF cur_gen_update%FOUND THEN
OPEN c_crm_id (l_cur_gen_update.request_id);
IF l_cur_gen_update.comp_status <> l_c_crm_id.outcome_code THEN
OPEN cur_int_hist(l_cur_gen_update.request_id);
igf_aw_gen_004.loan_disbursement_update(
p_person_id => l_cur_int_hist.student_id,
p_award_year => RPAD(l_cur_int_hist.cal_type,10)||to_char(l_cur_int_hist.ci_sequence_number,'999999')
);
EXIT WHEN cur_gen_update%NOTFOUND;
CLOSE cur_gen_update;
p_select_type IN VARCHAR2,
p_list_id IN NUMBER ,
p_person_id IN NUMBER ,
p_parameter_1 IN VARCHAR2,
p_parameter_2 IN VARCHAR2,
p_parameter_3 IN VARCHAR2,
p_parameter_4 IN VARCHAR2,
p_parameter_5 IN VARCHAR2,
p_parameter_6 IN VARCHAR2,
p_parameter_7 IN VARCHAR2,
p_parameter_8 IN VARCHAR2,
p_parameter_9 IN VARCHAR2,
p_override_flag IN VARCHAR2,
p_delivery_type IN VARCHAR2,
p_exception OUT NOCOPY VARCHAR2
) AS
/*************************************************************
Created By :Nalin Kumar
Date Created on : 05-Feb-2002
Purpose : This procedure will return true or false based on the validation.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
l_error_flag VARCHAR2(10);
l_debug_str := l_debug_str || ',p_select_type=' || p_select_type ;
IF p_select_type = 'L' THEN
IF p_sys_ltr_code = 'ADINTRW' THEN
IF p_list_id IS NULL OR
p_parameter_1 IS NOT NULL OR p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR
p_parameter_4 IS NOT NULL OR p_parameter_6 IS NOT NULL OR p_parameter_7 IS NOT NULL OR
p_parameter_9 IS NOT NULL OR p_person_id IS NOT NULL THEN
p_exception := 'Y';
l_debug_str := l_debug_str || 'p_select_type is L and p_sys_ltr_code is ADINTRW';
l_debug_str := l_debug_str || 'p_select_type is L and p_sys_ltr_code is ADNORSP';
l_debug_str := 'Exiting corp_validate_parameters as p_select_type is L, none of the nested if condition is satisfied and ';
ELSIF p_select_type = 'S' THEN
IF p_sys_ltr_code = 'ADINTRW' THEN
IF p_person_id IS NULL OR
p_parameter_1 IS NOT NULL OR p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR
p_parameter_4 IS NOT NULL OR p_parameter_6 IS NOT NULL OR p_parameter_7 IS NOT NULL OR
p_parameter_9 IS NOT NULL THEN
p_exception := 'Y';
l_debug_str := l_debug_str || 'p_select_type is S and p_sys_ltr_code is ADINTRW';
l_debug_str := l_debug_str || 'p_select_type is S and p_sys_ltr_code is ADNORSP';
l_debug_str := 'Exiting corp_validate_parameters as p_select_type is S, none of the nested if condition is satisfied and ';
END IF; -- p_select_type = S
ELSIF p_select_type = 'P' THEN
IF p_parameter_1 IS NOT NULL THEN
IF p_sys_ltr_code IN ('ADINTRW','ADNORSP') THEN
IF (p_parameter_2 IS NULL AND p_parameter_3 IS NULL AND p_parameter_4 IS NULL AND
p_parameter_6 IS NULL AND p_parameter_7 IS NULL AND p_parameter_9 IS NULL) THEN
l_all_null := 'TRUE';
l_debug_str := l_debug_str || 'p_select_type is P';
l_debug_str := 'Exiting corp_validate_parameters for p_select_type P and all other values as none of the if conditions are satisfied.';
END IF; -- selection type Parameter.
ELSIF p_select_type = 'G' THEN
--check if p_parameter_1 (person_id_grp) is null or not null.
IF p_parameter_1 IS NOT NULL THEN
IF p_sys_ltr_code = 'ADINTRW' THEN
IF p_parameter_2 IS NOT NULL OR p_parameter_3 IS NOT NULL OR p_parameter_4 IS NOT NULL OR
p_parameter_6 IS NOT NULL OR p_parameter_7 IS NOT NULL OR p_parameter_9 IS NOT NULL OR
p_person_id IS NOT NULL OR p_list_id IS NOT NULL THEN
--As letter has been submitted with select type as Group
--Only Group Name should be specified
p_exception := 'Y';
l_debug_str := l_debug_str || 'p_select_type is G and p_sys_ltr_code is ADINTRW';
l_debug_str := l_debug_str || 'p_select_type is G and p_sys_ltr_code is ADNORSP';
l_debug_str := l_debug_str || 'p_select_type is G and all other letters';
END IF; -- final end IF for selection type.