[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT function_id, user_function_name
FROM fnd_form_functions_vl
WHERE function_name = cp_function_name;
SELECT COUNT (1)
FROM igs_co_prev_reqs
WHERE concurrent_request_id = cp_concurrent_req_id;
p_select_type IN VARCHAR2,
p_list_id IN NUMBER,
p_person_id IN NUMBER,
p_override_flag IN VARCHAR2,
p_delivery_type IN VARCHAR2,
p_destination IN VARCHAR2, -- added as part of bug# 2472250
p_dest_fax_number IN VARCHAR2,
p_reply_email IN VARCHAR2,
p_sender_email IN VARCHAR2,
p_cc_email IN VARCHAR2,
p_org_unit_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_8 IN VARCHAR2,
p_parameter_7 IN VARCHAR2,
p_parameter_9 IN VARCHAR2,
p_preview IN VARCHAR2
)
IS
/*************************************************************
Created By :Prchandr
Date Created on : 05-Feb-2002
Purpose : This procedure is the main procedure called aftersubmiiting letters from concurrent jobs.
Know limitations, enhancements or remarks
Change History
Who When What
mnade 6/1/2005 FA 157 - 4382371 - Added p_award_prd_cd parameter to corp_post_process
Bayadav 24-MAY-2002 Included two system letter codes 'ENADHOC', 'SFADHOC' for adhoc letters as a part of bug 2376434
pradhakr 13-Aug-2002 If the delivery type is printer then a new parameter p_destination is added to get the printer name.
This is done as part of bug# 2472250.
Reversed the order of parameter 8 and 7 due to the way the parameters are defined.
kpadiyar 02-Mar-2003 Included nominated_course_cd,appl_sequence_number for letter
code 'ADACKMT' for bug 2525936.
pkpatel 7-May-2003 Bug 2940810
Modified to Bind variable
ssawhney 24 Sep 2003 3136817 , validations at time of audit profile setting
hreddych 13-oct-2003 Build UK Correspondence Letters
gmaheswa 14-nov-2003 Multiple mode of communication for same request is implemented. new parameter fax number is added.
ssawhney 3-may-2004 IBC.C patchset changes bug 3565861 + signature of corp_get_letter_type changed
ssaleem 02-Jun-2004 extended header usage - Validation added to check for Email delivery option when reply cc or sender
Email is given
ssaleem 09-SEP-2004 3630073. Added p_org_unit_id as a new parameter
pacross 08-SEP-2005 Added preview flag for correspondance preview and edit.
gmaheswa 5-Jan-2004 Bug 4869737 Added a call to SET_ORG_ID to disable OSS for R12.
***************************************************************/
l_sys_ltr_code igs_co_mapping.sys_ltr_code%TYPE;
SELECT person_number, full_name
FROM igs_pe_person_base_v
WHERE person_id = pa_person_id;
SELECT 1
FROM jtf_rs_resource_extns
WHERE user_id = cp_user_id;
SELECT description
FROM igs_lookup_values
WHERE lookup_code = cp_sys_ltr_code
AND lookup_type = 'IGS_CO_DEL_CHANNEL';
SELECT 1
FROM ibc_content_items citem, ibc_ctype_group_nodes ctg
WHERE citem.content_item_id = cp_item_id
AND citem.content_type_code = ctg.content_type_code
AND ctg.directory_node_id = cp_node_id;
p_select_type => p_select_type,
p_list_id => p_list_id,
p_person_id => p_person_id,
p_parameter_1 => p_parameter_1,
p_parameter_2 => p_parameter_2,
p_parameter_3 => p_parameter_3,
p_parameter_4 => p_parameter_4,
p_parameter_5 => p_parameter_5,
p_parameter_6 => p_parameter_6,
p_parameter_7 => p_parameter_7,
p_parameter_8 => p_parameter_8,
p_parameter_9 => p_parameter_9,
p_override_flag => p_override_flag,
p_delivery_type => l_delivery_type,
p_exception => l_exception
);
p_select_type => p_select_type,
p_sys_ltr_code => l_sys_ltr_code,
p_list_id => p_list_id,
p_letter_type => l_letter_type,
p_person_id => p_person_id,
p_parameter_1 => p_parameter_1,
p_parameter_2 => p_parameter_2,
p_parameter_3 => p_parameter_3,
p_parameter_4 => p_parameter_4,
p_parameter_5 => p_parameter_5,
p_parameter_6 => p_parameter_6,
p_parameter_7 => p_parameter_7,
p_parameter_8 => p_parameter_8,
p_parameter_9 => p_parameter_9,
p_sql_stmt => l_sql_stmt,
p_flag => p_override_flag,
p_exception => l_exception
);
p_select_type => p_select_type,
p_sys_ltr_code => l_sys_ltr_code,
p_list_id => p_list_id,
p_letter_type => l_letter_type,
p_person_id => p_person_id,
p_parameter_1 => p_parameter_1,
p_parameter_2 => p_parameter_2,
p_parameter_3 => p_parameter_3,
p_parameter_4 => p_parameter_4,
p_parameter_5 => p_parameter_5,
p_parameter_6 => p_parameter_6,
p_parameter_7 => p_parameter_7,
p_parameter_8 => p_parameter_8,
p_parameter_9 => p_parameter_9,
p_sql_stmt => l_sql_stmt,
p_exception => l_exception
);
INSERT INTO igs_co_prv_bch_reqs
(concurrent_request_id,
master_content_id,
master_content_type_code,
master_version_id,
master_language,
master_media_type_code,
master_template_updated_flag,
batch_cancelled_flag,
object_version_number, created_by,
creation_date, last_updated_by,
last_update_login,
last_update_date)
VALUES (fnd_global.conc_request_id,
l_document_id,
l_letter_type,
l_version_id,
USERENV ('LANG'),
l_delivery_type,
'N',
'N',
1, fnd_global.user_id,
SYSDATE, fnd_global.user_id,
NULL,
SYSDATE);
l_debug_str := 'Insert into igs_co_prv_bch_reqs failed with :' || SQLERRM;
INSERT INTO igs_co_prv_bch_reqs
(concurrent_request_id,
master_content_id,
master_content_type_code,
master_version_id,
master_language,
master_media_type_code,
master_template_updated_flag,
batch_cancelled_flag,
object_version_number, created_by,
creation_date, last_updated_by,
last_update_login,
last_update_date)
VALUES (fnd_global.conc_request_id,
l_document_id,
l_letter_type,
l_version_id,
USERENV ('LANG'),
l_delivery_type,
'N',
'N',
1, fnd_global.user_id,
SYSDATE, fnd_global.user_id,
NULL,
SYSDATE);
l_debug_str := 'Insert into igs_co_prv_bch_reqs failed with :' || SQLERRM;
p_select_type => p_select_type,
p_sys_ltr_code => l_sys_ltr_code,
p_list_id => p_list_id,
p_letter_type => l_letter_type,
p_person_id => p_person_id,
p_parameter_1 => p_parameter_1,
p_parameter_2 => p_parameter_2,
p_parameter_3 => p_parameter_3,
p_parameter_4 => p_parameter_4,
p_parameter_5 => p_parameter_5,
p_parameter_6 => p_parameter_6,
p_parameter_7 => p_parameter_7,
p_parameter_8 => p_parameter_8,
p_parameter_9 => p_parameter_9,
p_sql_stmt => l_sql_stmt,
p_exception => l_exception
);
INSERT INTO igs_co_prv_bch_reqs
(concurrent_request_id,
master_content_id,
master_content_type_code,
master_version_id,
master_language,
master_media_type_code,
master_template_updated_flag,
batch_cancelled_flag,
object_version_number,
created_by, creation_date,
last_updated_by,
last_update_login,
last_update_date)
VALUES (fnd_global.conc_request_id,
l_document_id,
l_letter_type,
l_version_id,
USERENV ('LANG'),
l_delivery_type,
'N',
'N',
1,
fnd_global.user_id, SYSDATE,
fnd_global.user_id,
NULL,
SYSDATE);
l_debug_str := 'Insert into igs_co_prv_bch_reqs failed with :' || SQLERRM;
INSERT INTO igs_co_prv_bch_reqs
(concurrent_request_id,
master_content_id,
master_content_type_code,
master_version_id,
master_language,
master_media_type_code,
master_template_updated_flag,
batch_cancelled_flag,
object_version_number,
created_by, creation_date,
last_updated_by,
last_update_login,
last_update_date)
VALUES (fnd_global.conc_request_id,
l_document_id,
l_letter_type,
l_version_id,
USERENV ('LANG'),
l_delivery_type,
'N',
'N',
1,
fnd_global.user_id, SYSDATE,
fnd_global.user_id,
NULL,
SYSDATE);
l_debug_str := 'Insert into igs_co_prv_bch_reqs failed with :' || SQLERRM;
processing to update the IGS Interaction History.
Know limitations, enhancements or remarks
Change History
Who When What
gmaheswa 5-Jan-2004 Bug 4869737 Added a call to SET_ORG_ID to disable OSS for R12.
********************************************************************/
-- Cursor to obtain the preview requests for this distribution
CURSOR c_preview_requests (cp_distribution_id NUMBER)
IS
SELECT 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,
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, award_prd_cd
FROM igs_co_prev_reqs
WHERE distribution_id = cp_distribution_id
AND NOT ( request_status_code = 'CANCELLED'
OR request_status_code = 'DISTRIBUTED'
);
SELECT person_number, full_name
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT version
FROM ibc_citems_v
WHERE citem_id = cp_content_id
AND citem_ver_id = cp_content_item_version_id;
UPDATE igs_co_prev_reqs
SET request_status_code = 'DISTRIBUTED'
WHERE distribution_id = p_distribution_id;