1 PACKAGE IGS_AD_SS_GEN_001 AS
2 /* $Header: IGSADB8S.pls 120.10 2006/06/14 12:08:50 arvsrini ship $ */
3 /******************************************************************
4 Created By: tapash.ray
5 Date Created By: 11-Dec-2001
6 Purpose: Transfer API for transfer of data from SS Staging Table to IGS tables
7 Known limitations,enhancements,remarks:
8 Change History
9 Who When What
10 apadegal 21-Oct-2005 Added g_admin_security_on to enable security for submission of application by any Admin
11 abhiskum 25-Aug-2005 Added procedures DELETE_PERSTMT_ATTACHMENT_UP, ADD_PERSTMT_ATTACHMENT_UP for
12 Update Submitted Applications Page in SS Admin Flow; and
13 DELETE_PERSTMT_ATTACHMENT, ADD_PERSTMT_ATTACHMENT for Supporting Evidence Page
14 in SS Applicant Floe, for the IGS.M build
15 pathipat 17-Jun-2003 Enh 2831587 Credit Card Fund Transfer build
16 Modified procedure update_ad_offer_resp_and_fee - added p_credit_card_tangible_cd
17 vvutukur 26-Nov-2002 Enh#2584986.Modified procedure update_ad_offer_resp_and_fee to add 9 new parameters.
18 rboddu 17-FEB-2002 insert_ss_appl_stg procedure is modified
19 for new IN paramter p_app_source_id. insert_ss_appl_perstat_stg procedure
20 is modified for the new IN parameter p_date_received.
21 kamohan 30-MAY-2002 Bug 2347213 Added the procedure validate_prog_inst
22 ******************************************************************/
23
24
25 g_admin_security_on VARCHAR2(1);
26
27 PROCEDURE set_adm_secur_on;
28 PROCEDURE set_adm_secur_off;
29
30 PROCEDURE transfer_data(x_person_id IN NUMBER,
31 x_application_id IN NUMBER,
32 x_message_name OUT NOCOPY VARCHAR2,
33 x_return_status OUT NOCOPY VARCHAR2,
34 p_adm_appl_number OUT NOCOPY NUMBER);
35
36 PROCEDURE insert_ss_appl_stg(x_message_name OUT NOCOPY VARCHAR2,
37 x_return_status OUT NOCOPY VARCHAR2,
38 p_person_id IN NUMBER,
39 p_application_type IN VARCHAR2,
40 p_adm_appl_number IN NUMBER,
41 p_admission_cat IN VARCHAR2,
42 p_s_adm_process_type IN VARCHAR2,
43 p_login_id IN NUMBER,
44 p_app_source_id IN NUMBER DEFAULT NULL);
45
46 PROCEDURE delete_ss_appl_stg(x_message_name OUT NOCOPY VARCHAR2,
47 x_return_status OUT NOCOPY VARCHAR2,
48 p_adm_appl_number IN NUMBER,
49 p_person_id IN NUMBER);
50
51 PROCEDURE insert_ss_appl_perstat_stg(p_return_status OUT NOCOPY VARCHAR2,
52 p_message_data OUT NOCOPY VARCHAR2,
53 p_person_id IN NUMBER,
54 p_adm_appl_id IN NUMBER,
55 p_admission_application_type IN VARCHAR2,
56 p_user_id IN NUMBER,
57 p_date_received IN DATE DEFAULT NULL);
58
59 PROCEDURE get_acad_cal(p_adm_cal_type IN IGS_CA_TYPE.CAL_TYPE%TYPE,
60 p_adm_seq IN OUT NOCOPY IGS_CA_INST.SEQUENCE_NUMBER%TYPE,
61 p_acad_cal_type OUT NOCOPY IGS_CA_TYPE.CAL_TYPE%TYPE,
62 p_acad_seq OUT NOCOPY IGS_CA_INST.SEQUENCE_NUMBER%TYPE);
63
64 FUNCTION get_dflt_adm_cal RETURN VARCHAR2;
65
66 PROCEDURE Check_FeeExists(p_person_id IN igs_ad_appl_all.person_id%TYPE,
67 p_adm_appl_num IN igs_ad_appl_all.admission_appl_number%TYPE,
68 x_return_status OUT NOCOPY VARCHAR2,
69 x_message_data OUT NOCOPY VARCHAR2);
70
71 PROCEDURE Check_OneStop(p_person_id IN NUMBER,
72 p_admission_cat IN VARCHAR2,
73 p_admission_process_type IN VARCHAR2,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_message_data OUT NOCOPY VARCHAR2);
76
77 PROCEDURE Process_OneStop(p_admission_appl_number IN NUMBER,
78 p_person_id IN NUMBER,
79 p_admission_cat IN VARCHAR2,
80 p_admission_process_type IN VARCHAR2,
81 p_role IN VARCHAR2,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_message_data OUT NOCOPY VARCHAR2);
84
85 PROCEDURE update_ad_offer_resp_and_fee(p_person_id IN NUMBER,
86 p_admission_appl_number IN NUMBER,
87 p_one_stop_ind IN VARCHAR2,
88 p_app_fee_amt IN NUMBER,
89 p_authorization_number IN VARCHAR2,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2,
93 p_credit_card_code IN VARCHAR2,
94 p_credit_card_holder_name IN VARCHAR2,
95 p_credit_card_number IN VARCHAR2,
96 p_credit_card_expiration_date IN DATE,
97 p_gl_date IN DATE,
98 p_rev_gl_ccid IN NUMBER,
99 p_cash_gl_ccid IN NUMBER,
100 p_rev_account_cd IN VARCHAR2,
101 p_cash_account_cd IN VARCHAR2,
102 p_credit_card_tangible_cd IN VARCHAR2);
103
104 PROCEDURE Process_OneStop2(p_admission_appl_number IN NUMBER,
105 p_person_id IN NUMBER,
106 p_admission_cat IN VARCHAR2,
107 p_admission_process_type IN VARCHAR2,
108 p_role IN VARCHAR2,
109 x_return_status OUT NOCOPY VARCHAR2,
110 x_msg_data OUT NOCOPY VARCHAR2);
111
112 PROCEDURE Update_Appl_Eqdo_Inst(p_person_id IN NUMBER,
113 p_admission_appl_number IN NUMBER,
114 p_nominated_course_cd IN VARCHAR2,
115 p_sequence_number IN NUMBER,
116 x_return_status OUT NOCOPY VARCHAR2,
117 x_message_data OUT NOCOPY VARCHAR2);
118
119 PROCEDURE Update_Appl_Ofr_Inst(p_person_id IN NUMBER,
120 p_admission_appl_number IN NUMBER,
121 p_nominated_course_cd IN VARCHAR2,
122 p_sequence_number IN NUMBER,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_message_data OUT NOCOPY VARCHAR2);
125
126 PROCEDURE Update_Appl_Ofres_Inst(p_person_id IN NUMBER,
127 p_admission_appl_number IN NUMBER,
128 p_nominated_course_cd IN VARCHAR2,
129 p_sequence_number IN NUMBER,
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_message_data OUT NOCOPY VARCHAR2);
132
133 PROCEDURE insert_appl_section_stat(x_message_name OUT NOCOPY VARCHAR2,
134 x_return_status OUT NOCOPY VARCHAR2,
135 p_person_id IN NUMBER,
136 p_adm_appl_number IN NUMBER,
137 p_login_id IN NUMBER);
138
139 PROCEDURE validate_prog_inst(p_course_cd IN VARCHAR2,
140 p_crv_version_number IN NUMBER,
141 p_location_cd IN VARCHAR2,
142 p_attendance_mode IN VARCHAR2,
143 p_attendance_type IN VARCHAR2,
144 p_acad_cal_type IN VARCHAR2,
145 p_acad_ci_sequence_number IN NUMBER,
146 p_adm_cal_type IN VARCHAR2,
147 p_adm_ci_sequence_number IN NUMBER,
148 p_admission_cat IN VARCHAR2,
149 p_s_admission_process_type IN VARCHAR2,
150 p_message_name OUT NOCOPY VARCHAR2,
151 p_return_type OUT NOCOPY VARCHAR2);
152
153 /*
154 --------------------------------------------------------------------------------------------------
155 --Function to get the major first choice and second choice to be displayed in the printable page
156 -- Sent by Nagaraju from HQ to be added to the API
157 --------------------------------------------------------------------------------------------------
158 */
159 FUNCTION get_major(p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
160 p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
161 p_nominated_course_cd IN igs_ad_ps_appl_inst.nominated_course_cd%TYPE,
162 p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,
163 p_rank IN igs_ad_unit_sets.rank%TYPE)
164 RETURN VARCHAR2;
165
166 FUNCTION DATESTR(P_START_DATE DATE, P_END_DATE DATE, P_COMP_DATE DATE)
167 RETURN VARCHAR2;
168
169 -- Procedure specially designed to get the Concatenated list of Alternate Ids in the FindPerson Page
170 FUNCTION getAltid(x_party_id number) RETURN VARCHAR2;
171
172 -- Procedure specially designed to get the Concatenated list of Application Ids in the FindPerson Page
173 FUNCTION getApplid(x_party_id number) RETURN VARCHAR2;
174
175 /* Added procedure which will update the checklist w.r.t Application type configuration. */
176 PROCEDURE update_appl_section_stat(p_person_id IN NUMBER,
177 p_adm_appl_number IN NUMBER,
178 p_page_Name IN VARCHAR2,
179 p_Appl_Type IN VARCHAR2,
180 x_message_name OUT NOCOPY VARCHAR2,
181 x_return_status OUT NOCOPY VARCHAR2,
182 x_mand_incomplete OUT NOCOPY VARCHAR2);
183
184 /* Procedure which will Sync the checklist w.r.t Application type configuration. */
185 PROCEDURE sync_appl_section_stat(p_person_id IN NUMBER,
186 p_adm_appl_number IN NUMBER,
187 p_Appl_Type IN VARCHAR2,
188 p_login_id IN NUMBER,
189 x_message_name OUT NOCOPY VARCHAR2,
190 x_return_status OUT NOCOPY VARCHAR2,
191 x_max_Sections OUT NOCOPY NUMBER);
192
193 /*procedure which will create the records in following areas when Application Type is created
194 Application Type pages
195 Application Type page Components
196 Terms and Conditions */
197 PROCEDURE auto_assign_pgs_comps_terms(
198 x_message_name OUT NOCOPY VARCHAR2,
199 x_return_status OUT NOCOPY VARCHAR2,
200 p_appl_type IN VARCHAR2,
201 p_admission_cat IN VARCHAR2 ,
202 p_s_admission_process_type IN VARCHAR2
203 );
204 /*procedure which will update the records in following areas when Application Category
205 is Changed for an Application Type:
206 Application Type pages
207 Application Type page Components
208 Terms and Conditions */
209 PROCEDURE update_assign_pgs_comps( x_message_name OUT NOCOPY VARCHAR2,
210 x_return_status OUT NOCOPY VARCHAR2,
211 p_appl_type IN VARCHAR2 DEFAULT NULL,
212 p_admission_cat IN VARCHAR2,
213 p_s_admission_process_type IN VARCHAR2
214 );
215
216 PROCEDURE validate_prog_pref (p_ss_adm_appl_id IN NUMBER ,
217 p_course_cd IN VARCHAR2,
218 p_crv_version_number IN NUMBER,
219 p_location_cd IN VARCHAR2,
220 p_attendance_mode IN VARCHAR2,
221 p_attendance_type IN VARCHAR2,
222 p_final_unit_set_cd IN VARCHAR2,
223 p_us_version_number IN NUMBER,
224 p_message_name OUT NOCOPY VARCHAR2,
225 p_return_type OUT NOCOPY VARCHAR2);
226
227 PROCEDURE validate_unit_Set (p_ss_adm_appl_id IN NUMBER ,
228 p_course_cd VARCHAR2,
229 p_crv_version_number NUMBER,
230 p_location_cd VARCHAR2,
231 p_attendance_mode VARCHAR2,
232 p_attendance_type VARCHAR2,
233 p_unit_set_cd VARCHAR2,
234 p_us_version_number NUMBER ,
235 p_message_name OUT NOCOPY VARCHAR2,
236 p_return_type OUT NOCOPY VARCHAR2) ;
237 PROCEDURE DELETE_PERSTMT_ATTACHMENT(p_document_id IN NUMBER,
238 p_ss_perstat_id IN NUMBER,
239 x_return_status OUT NOCOPY VARCHAR2
240 );
241 PROCEDURE ADD_PERSTMT_ATTACHMENT (p_person_id IN NUMBER,
242 p_ss_perstat_id IN NUMBER,
243 p_file_name IN VARCHAR2,
244 p_file_content_type IN VARCHAR2,
245 p_file_format IN VARCHAR2,
246 p_file_id OUT NOCOPY NUMBER,
247 x_return_status OUT NOCOPY VARCHAR2
248 );
249 PROCEDURE DELETE_PERSTMT_ATTACHMENT_UP(p_document_id IN NUMBER,
250 x_return_status OUT NOCOPY VARCHAR2
251 );
252 PROCEDURE ADD_PERSTMT_ATTACHMENT_UP(p_person_id IN NUMBER,
253 p_appl_perstat_id IN NUMBER,
254 p_file_name IN VARCHAR2,
255 p_file_content_type IN VARCHAR2,
256 p_file_format IN VARCHAR2,
257 p_file_id OUT NOCOPY NUMBER,
258 x_return_status OUT NOCOPY VARCHAR2
259 );
260
261 /* Get Concatenated Enabled SS Lookup Code Descriptions for a given lookup type with given delimiter */
262
263 PROCEDURE get_ss_lookup_desc(p_application_type IN igs_ad_ss_lookups.admission_application_type%type,
264 p_lookup_type IN igs_ad_ss_lookups.ss_lookup_type%TYPE,
265 p_delimiter IN VARCHAR2,
266 x_message_name OUT NOCOPY VARCHAR2,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_concat_desc OUT NOCOPY VARCHAR2);
269
270 PROCEDURE CHECK_INSTANCE_SECURITY( p_person_id IN NUMBER,
271 p_adm_appl_number IN NUMBER,
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_error_msg OUT NOCOPY VARCHAR2);
274
275 FUNCTION wf_submit_application_sub(p_subscription_guid IN RAW,
276 p_event IN OUT NOCOPY WF_EVENT_T) return varchar2;
277
278 END IGS_AD_SS_GEN_001;
[Home] [Help]