1 PACKAGE igs_co_process AS
2 /* $Header: IGSCO22S.pls 120.3 2005/09/28 05:43:24 appldev ship $ */
3 /*************************************************************
4 Created By :Nalin Kumar
5 Date Created on : 05-Feb-2002
6 Purpose : This package will consist of procedures that will perform validation
7 and processing of correspondence related information and data.
8 Know limitations, enhancements or remarks
9 Change History
10 Who When What
11 mnade 6/1/2005 FA 157 Added p_award_prd_cd parameter to corp_post_process
12 pradhakr 13-Aug-2002 Added the parameter p_destination, in the procedure corp_submit_fulfil_request,
13 which takes the destination name (i.e) printer name if the media type
14 selected is printer. Changes as part of bug# 2472250
15 gmaheswa 15-Nov-2003 Bug : 3006800 Added New parameter p_fax_number in corp_submit_fulfil_request.
16 ssawhney 3-may-04 IBC.C patchset changes bug 3565861 + 3442719 + signature of corp_get_letter_type changed
17 ssaleem 09-SEP-2004 3630073. Added p_org_unit_id as a new parameter
18 pacross 11-SEP-2005 Added p_preview parameter for Correspondance preview and edit
19 (reverse chronological order - newest change first)
20 ***************************************************************/
21 --
22 -- This procedure will accept document id as a parameter and
23 -- return system letter code for the document.
24 --
25 PROCEDURE corp_get_letter_type(
26 p_map_id IN NUMBER,
27 p_document_id OUT NOCOPY NUMBER,
28 p_sys_ltr_code OUT NOCOPY VARCHAR2,
29 p_letter_type OUT NOCOPY VARCHAR2,
30 p_version_id OUT NOCOPY NUMBER
31 );
32 --
33 --Based on the selection type this procedure will build and return a select statement.
34 --
35 PROCEDURE corp_build_sql_stmt(
36 p_document_id IN NUMBER,
37 p_sys_ltr_code IN VARCHAR2, -- ADDED NEW**
38 p_select_type IN VARCHAR2,
39 p_list_id IN NUMBER,
40 p_person_id IN NUMBER,
41 p_letter_type IN VARCHAR2,
42 p_parameter_1 IN VARCHAR2,
43 p_parameter_2 IN VARCHAR2,
44 p_parameter_3 IN VARCHAR2,
45 p_parameter_4 IN VARCHAR2,
46 p_parameter_5 IN VARCHAR2,
47 p_parameter_6 IN VARCHAR2,
48 p_parameter_7 IN VARCHAR2,
49 p_parameter_8 IN VARCHAR2,
50 p_parameter_9 IN VARCHAR2,
51 p_sql_stmt OUT NOCOPY VARCHAR2,
52 p_exception OUT NOCOPY VARCHAR2
53 );
54 --
55 --This procedure will check and return attributes assigned to a document.
56 --
57 PROCEDURE corp_check_document_attributes(
58 p_map_id IN NUMBER,
59 p_elapsed_days OUT NOCOPY NUMBER,
60 p_no_of_repeats OUT NOCOPY NUMBER
61 );
62 --
63 -- This procedure will check interaction history and return a value to
64 -- inform whether a document can be sent or not.
65 --
66 PROCEDURE corp_check_interaction_history(
67 p_person_id IN NUMBER,
68 p_sys_ltr_code IN VARCHAR2,
69 p_document_id IN NUMBER,
70 p_application_id IN NUMBER DEFAULT NULL,
71 p_course_cd IN VARCHAR2 DEFAULT NULL,
72 p_adm_seq_no IN NUMBER DEFAULT NULL,
73 p_awd_cal_type IN VARCHAR2 DEFAULT NULL,
74 p_awd_seq_no IN NUMBER DEFAULT NULL,
75 p_elapsed_days IN NUMBER,
76 p_no_of_repeats IN NUMBER,
77 p_send_letter OUT NOCOPY VARCHAR2
78 );
79 --
80 -- This procedure will accept parameters and submit fulfilment requests.
81 --
82 PROCEDURE corp_submit_fulfil_request(
83 p_letter_type IN VARCHAR2,
84 p_person_id IN NUMBER,
85 p_email_address IN VARCHAR2,
86 p_content_id IN NUMBER,
87 p_award_year IN VARCHAR2, --New
88 p_sys_ltr_code IN VARCHAR2, --New
89 p_adm_appl_number IN NUMBER, --New
90 p_nominated_course_cd IN VARCHAR2, --New
91 p_appl_sequence_number IN NUMBER, --New
92 p_fulfillment_req IN VARCHAR2,
93 p_crm_user_id IN NUMBER,
94 p_media_type IN VARCHAR2,
95 p_destination IN VARCHAR2, -- Added the parameter p_destination as part of bug# 2472250
96 p_fax_number IN VARCHAR2 DEFAULT NULL,
97 p_reply_days IN VARCHAR2 DEFAULT NULL,
98 p_panel_code IN VARCHAR2 DEFAULT NULL,
99 p_request_id OUT NOCOPY NUMBER,
100 p_request_status OUT NOCOPY VARCHAR2,
101 p_reply_email IN VARCHAR2 DEFAULT NULL,
102 p_sender_email IN VARCHAR2 DEFAULT NULL,
103 p_cc_email IN VARCHAR2 DEFAULT NULL,
104 p_org_unit_id IN NUMBER DEFAULT NULL,
105 p_preview IN VARCHAR2 DEFAULT NULL,
106 p_awd_cal_type IN VARCHAR2,
107 p_awd_ci_seq_number IN NUMBER,
108 p_awd_prd_cd IN VARCHAR2,
109 p_preview_version_id IN NUMBER DEFAULT NULL,
110 p_preview_version IN NUMBER DEFAULT NULL
111 );
112 --
113 -- This procedure will perform post-processing.
114 --
115 PROCEDURE corp_post_process(
116 p_person_id IN NUMBER,
117 p_request_id IN NUMBER,
118 p_document_id IN NUMBER,
119 p_sys_ltr_code IN VARCHAR2,
120 p_document_type IN VARCHAR2,
121 p_adm_appl_number IN NUMBER,
122 p_nominated_course_cd IN VARCHAR2,
123 p_appl_seq_number IN NUMBER,
124 p_awd_cal_type IN VARCHAR2,
125 p_awd_ci_seq_number IN NUMBER,
126 p_award_year IN VARCHAR2,
127 p_delivery_type IN VARCHAR2,
128 p_version_id IN NUMBER, -- ssawhney
129 p_award_prd_cd IN VARCHAR2 -- mnade fa 157
130 );
131 --
132 -- This procedure returns the view name for the system letter code.
133 --
134 PROCEDURE corp_get_system_letter_view(
135 p_sys_ltr_code IN VARCHAR2,
136 p_view_name OUT NOCOPY VARCHAR2,
137 p_where_clause OUT NOCOPY VARCHAR2
138 );
139 --
140 -- This procedure accepts 5 parameters and builds a where
141 -- clause for student selection.
142 --
143 PROCEDURE corp_get_parameter_value(
144 p_sys_ltr_code IN VARCHAR2,
145 p_parameter_1 IN VARCHAR2,
146 p_parameter_2 IN VARCHAR2,
147 p_parameter_3 IN VARCHAR2,
148 p_parameter_4 IN VARCHAR2,
149 p_parameter_5 IN VARCHAR2,
150 p_parameter_6 IN VARCHAR2,
151 p_parameter_7 IN VARCHAR2,
152 p_parameter_8 IN VARCHAR2,
153 p_parameter_9 IN VARCHAR2,
154 p_parameter_value OUT NOCOPY VARCHAR2
155 );
156 --
157 -- This procedure will check the request status in OSS Interaction Table
158 -- against CRM Interaction History and update the OSS Interaction table.
159 --
160 PROCEDURE corp_check_request_status(
161 errbuf OUT NOCOPY VARCHAR2,
162 retcode OUT NOCOPY NUMBER,
163 p_person_id IN NUMBER DEFAULT NULL,
164 p_document_id IN NUMBER DEFAULT NULL,
165 p_application_id IN NUMBER DEFAULT NULL,
166 p_course_cd IN VARCHAR2 DEFAULT NULL,
167 p_adm_seq_no IN NUMBER DEFAULT NULL,
168 p_awd_cal_type IN VARCHAR2 DEFAULT NULL,
169 p_awd_seq_no IN NUMBER DEFAULT NULL,
170 p_elapsed_days IN NUMBER DEFAULT NULL,
171 p_no_of_repeats IN NUMBER DEFAULT NULL,
172 p_sys_ltr_code IN VARCHAR2 DEFAULT NULL
173 );
174 --
175 -- This procedure will return true or false based on the validation.
176 --
177 PROCEDURE corp_validate_parameters(
178 p_sys_ltr_code IN VARCHAR2,
179 p_document_id IN NUMBER,
180 p_select_type IN VARCHAR2,
181 p_list_id IN NUMBER DEFAULT NULL,
182 p_person_id IN NUMBER DEFAULT NULL,
183 p_parameter_1 IN VARCHAR2 DEFAULT NULL,
184 p_parameter_2 IN VARCHAR2 DEFAULT NULL,
185 p_parameter_3 IN VARCHAR2 DEFAULT NULL,
186 p_parameter_4 IN VARCHAR2 DEFAULT NULL,
187 p_parameter_5 IN VARCHAR2 DEFAULT NULL,
188 p_parameter_6 IN VARCHAR2 DEFAULT NULL,
189 p_parameter_7 IN VARCHAR2 DEFAULT NULL,
190 p_parameter_8 IN VARCHAR2 DEFAULT NULL,
191 p_parameter_9 IN VARCHAR2 DEFAULT NULL,
192 p_override_flag IN VARCHAR2,
193 p_delivery_type IN VARCHAR2,
194 p_exception OUT NOCOPY VARCHAR2
195 );
196 --
197 -- This is a package variable to identify whether the corp_submit_fulfil_request.
198 -- process was unsuccessfull and abort the call to corp_post_process.
199 --
200 l_corp_submit_fulfil_request BOOLEAN DEFAULT FALSE;
201 --
202 -- This is a package variable to identify whether the message which is the header
203 -- has been printed in the log file. To avoid re-printing of the header.
204 --
205 l_message_logged BOOLEAN DEFAULT FALSE;
206
207 END igs_co_process;