1 PACKAGE igf_aw_gen AS
2 /* $Header: IGFAW02S.pls 120.1 2005/06/08 00:42:45 appldev $ */
3
4 -- All commonly used functions and procedures can be created in this package
5 -- mnade 6/6/2005 FA 157 - 4382371 - Added
6 -- get_notification_status update_notification_status
7 -- update_awd_notification_status get_concurrent_prog_name
8 -- is_fund_locked_for_awd_period
9 -- veramach Oct 2004 FA 152/FA 137 - Obsoleted efc_coa,efc_resource,rem_need_fm,rem_need_im
10 -- veramach 11-NOV-2003 Changed the signature of check_ld_cal_tps -adplans_id is passed instead of fund_id and out variable is VARCHAR2
11 -- instead of BOOLEAN
12 --adhawan 25-oct-2002 ALT_PELL_SCHEDULE added for FA108 Awarding Enhancements
13 -- efc_coa --Modified the coa_total_cur to select from directly for term load calendar
14 -- efc_coa --Obsoleted the usage of p_flag , it is kept only for backward compatibility
15 -- efc_resource Modified the award_total_cur to select from igf_aw_adisb_coa_match_v instead of igf_aw_coa_citsn
16 -- efc_resource Obsoleted the usage of p_flag , it is kept only for backward compatibility
17 --Bug ID #2613546
18 -- sjadhav
19 -- Bug 2216956
20 -- Feb 13,2002
21 -- added function to get version number
22 --
23
24 FUNCTION LOOKUP_DESC( l_type in VARCHAR2 ,
25 l_code in VARCHAR2 ) RETURN VARCHAR2 ;
26
27 --
28 -- Use this function to get Version Number for the File Processing
29 -- p_process
30 -- D - DL
31 -- F - FFEL
32 -- P - PELL
33 -- I - ISIR
34 -- R - PROFILE
35 --
36 FUNCTION get_ver_num ( p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
37 p_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
38 p_process IN VARCHAR2) RETURN VARCHAR2;
39
40
41 PROCEDURE update_disb( p_disb_old_rec igf_aw_awd_disb_all%ROWTYPE,
42 p_disb_new_rec igf_aw_awd_disb_all%ROWTYPE );
43
44 PROCEDURE update_fabase_awds ( p_base_id in igf_ap_fa_base_rec_all.base_id%type,
45 p_pack_status igf_ap_fa_base_rec.packaging_status%TYPE) ;
46
47 FUNCTION get_org_id
48 RETURN NUMBER;
49
50 PROCEDURE set_org_id(p_context IN VARCHAR2 );
51
52 PRAGMA RESTRICT_REFERENCES (LOOKUP_DESC,WNDS,WNPS);
53
54 PROCEDURE update_fmast( x_old_ref in igf_aw_award_all%ROWTYPE,
55 x_new_ref in igf_aw_award_all%ROWTYPE,
56 flag in Varchar ) ;
57
58 PROCEDURE update_award( p_award_id IN igf_aw_award_all.award_id%TYPE,
59 p_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE,
60 p_disb_amt IN igf_aw_awd_disb_all.disb_net_amt%TYPE,
61 p_disb_dt IN igf_aw_awd_disb_all.disb_date%TYPE,
62 p_action IN VARCHAR2,
63 x_called_from IN VARCHAR2 DEFAULT NULL
64 );
65
66 PROCEDURE check_ld_cal_tps( p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
67 p_found OUT NOCOPY VARCHAR2 ) ;
68
69 PROCEDURE check_number_format(str VARCHAR2,ret OUT NOCOPY NUMBER) ;
70
71 /*
72 || Created By : pkpatel
73 || Created On : 11-DEC-2001
74 || Purpose : Bug No - 2142666 EFC DLD
75 || It finds the Dependency Status and eligibility of student for processing Simplified and Auto Zero EFC.
76 || Known limitations, enhancements or remarks :
77 || Change History :
78 || Who When What
79 || (reverse chronological order - newest change first)
80 */
81
82 PROCEDURE depend_stat_2001
83 ( p_base_id IN igf_ap_fa_base_rec.base_id%TYPE, -- Students Base ID
84 p_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE, -- ISIR ID
85 p_method_code IN VARCHAR2, -- Need Analysis Methodology Code
86 p_category OUT NOCOPY NUMBER, -- 0 -> Zero EFC, 1 -> A Regular, 2 -> A Simplified, 3 -> B Regular,
87 -- 4 -> B Simplified, 5 -> C Regular, 6 -> C Simplified
88 p_dependency_status OUT NOCOPY VARCHAR2); -- Students Dependency Status ie Independent or Dependent
89
90 /*
91 || Created By : mnade
92 || Created On : 5/24/2005
93 ||
94 || Purpose : Bug No - 4382371 FA 157 - Packaging Phase II
95 || Searches for the award notification status for given person for given awarding period the terms for which
96 || fall under the given awarding period. If all awards carry same notification status in that case carry the same with
97 || latest date. In case there are multiple, return the least significant one , with latest date.
98 ||
99 || Known limitations, enhancements or remarks :
100 || Change History :
101 || Who When What
102 || (reverse chronological order - newest change first)
103 */
104 PROCEDURE get_notification_status (
105 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
106 p_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
107 p_awarding_period IN igf_aw_award_prd.award_prd_cd%TYPE,
108 p_base_id IN igf_ap_fa_base_rec_all.base_id%type,
109 p_notification_status_code OUT NOCOPY igf_aw_award_all.notification_status_code%TYPE,
110 p_notification_status_date OUT NOCOPY igf_aw_award_all.notification_status_date%TYPE
111 );
112
113 /*
114 || Created By : mnade
115 || Created On : 5/24/2005
116 ||
117 || Purpose : Bug No - 4382371 FA 157 - Packaging Phase II
118 || Searches for awards for given person for given awarding period the terms for which
119 || fall under the given awarding period. All awards will be updated to carry supplied
120 || Notification Status and Notification Status Date.
121 ||
122 || Known limitations, enhancements or remarks :
123 || Change History :
124 || Who When What
125 || (reverse chronological order - newest change first)
126 */
127 PROCEDURE update_notification_status (
128 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
129 p_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
130 p_awarding_period IN igf_aw_award_prd.award_prd_cd%TYPE,
131 p_base_id IN igf_ap_fa_base_rec_all.base_id%type,
132 p_notification_status_code IN igf_aw_award_all.notification_status_code%TYPE,
133 p_notification_status_date IN igf_aw_award_all.notification_status_date%TYPE,
134 p_called_from IN VARCHAR2
135 );
136
137 /*
138 || Created By : mnade
139 || Created On : 5/24/2005
140 ||
141 || Purpose : Bug No - 4382371 FA 157 - Packaging Phase II
142 || Updates the Notification Status and Notification Status Date for given award.
143 ||
144 || Known limitations, enhancements or remarks :
145 || Change History :
146 || Who When What
147 || (reverse chronological order - newest change first)
148 */
149 PROCEDURE update_awd_notification_status (
150 p_award_id IN igf_aw_award_all.award_id%TYPE,
151 p_notification_status_code IN igf_aw_award_all.notification_status_code%TYPE,
152 p_notification_status_date IN igf_aw_award_all.notification_status_date%TYPE,
153 p_called_from IN VARCHAR2
154 );
155
156
157 /*
158 || Created By : mnade
159 || Created On : 5/24/2005
160 ||
161 || Purpose : Bug No - 4382371 FA 157 - Packaging Phase II
162 || Gets the concurrent program name for the cp id being passed.
163 ||
164 || Known limitations, enhancements or remarks :
165 || Change History :
166 || Who When What
167 || (reverse chronological order - newest change first)
168 */
169 FUNCTION get_concurrent_prog_name (p_program_id IN fnd_concurrent_programs_tl.concurrent_program_id%TYPE) RETURN VARCHAR2 ;
170
171 /*
172 || Created By : mnade
173 || Created On : 6/6/2005
174 ||
175 || Purpose : Bug No - 4382371 FA 157 - Packaging Phase II
176 || Checks if there is any award locked under given awarding period for the student
177 || and returns true of there is any award locked for the student.
178 ||
179 || Known limitations, enhancements or remarks :
180 || Change History :
181 || Who When What
182 || (reverse chronological order - newest change first)
183 */
184 FUNCTION is_fund_locked_for_awd_period (
185 p_base_id IN igf_ap_fa_base_rec_all.base_id%type,
186 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
187 p_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
188 p_awarding_period IN igf_aw_award_prd.award_prd_cd%TYPE,
189 p_fund_id IN igf_aw_award_all.fund_id%TYPE
190 ) RETURN BOOLEAN;
191
192 END igf_aw_gen;