1 PACKAGE BODY igs_fi_gen_apint AS
2 /* $Header: IGSFI79B.pls 115.10 2003/07/03 04:10:15 pathipat noship $ */
3
4
5 FUNCTION chk_liability_acc (p_n_ccid IN NUMBER) RETURN BOOLEAN AS
6 ------------------------------------------------------------------
7 --Created by : Sanil Madathil, Oracle IDC
8 --Date created: 20 Feb 2003
9 --
10 --Purpose: Function to check validity of Liability Account CCID combination.
11 -- To check if Account CCID passed is of type Liability.
12 --
13 --
14 --
15 --Known limitations/enhancements and/or remarks:
16 --
17 --Change History:
18 --Who When What
19 --pathipat 27-Jun-2003 Bug: 2992967 - Table based value set for segments
20 -- Removed quotes and concatenation in the vrule string
21 -- SYKRISHN 06/MAR/03 Changed FND_FLEX_KEYVAL callout from validate_ccid to validate_segs
22 -- as per bug 2832607
23 ------------------------------------------------------------------
24
25 l_v_rule_string VARCHAR2(2000) := 'GL_ACCOUNT\nGL_ACCOUNT_TYPE\nI\nAPPL=IGS;NAME=IGS_FI_ACC_LIABL\nL';
26
27 BEGIN
28 --Invoke FND_FLEX_KEYVAL.validate_segs for validating the CCID (Concat) with appropriate vrule string.
29 --Return FALSE from this function if FND API returns FALSE.
30
31 IF fnd_flex_keyval.validate_segs(
32 operation =>'CHECK_COMBINATION',
33 appl_short_name =>'SQLGL',
34 key_flex_code =>'GL#',
35 structure_number => igs_fi_gen_007.get_coa_id,
36 concat_segments => igs_fi_gen_007.get_ccid_concat(p_n_ccid),
37 vrule => l_v_rule_string) THEN
38 RETURN TRUE;
39 ELSE
40 RETURN FALSE;
41 END IF;
42 END chk_liability_acc;
43
44
45 FUNCTION get_rfnd_destination RETURN VARCHAR2 AS
46 ------------------------------------------------------------------
47 --Created by : Sanil Madathil, Oracle IDC
48 --Date created: 20 Feb 2003
49 --
50 --Purpose: Function to fetch the value of RFND_DESTINATION from IGS_FI_CONTROL
51 --
52 --
53 --
54 --
55 --Known limitations/enhancements and/or remarks:
56 --
57 --Change History:
58 --Who When What
59 ------------------------------------------------------------------
60 CURSOR c_rfnd_destination IS
61 SELECT rfnd_destination
62 FROM igs_fi_control;
63
64 l_v_rfnd_destination igs_fi_control_all.rfnd_destination%TYPE := NULL;
65
66 BEGIN
67 --Select the Refund Destination field from the IGS_FI_CONTROL table
68 OPEN c_rfnd_destination;
69 FETCH c_rfnd_destination INTO l_v_rfnd_destination;
70 CLOSE c_rfnd_destination;
71 RETURN l_v_rfnd_destination;
72
73 END get_rfnd_destination ;
74
75 FUNCTION get_unit_section_desc( p_n_uoo_id IN PLS_INTEGER,
76 p_v_unit_cd IN VARCHAR2,
77 p_n_version_number IN PLS_INTEGER,
78 p_v_cal_type IN VARCHAR2,
79 p_n_ci_sequence_number IN PLS_INTEGER,
80 p_v_location_cd IN VARCHAR2,
81 p_v_unit_class IN VARCHAR2
82 ) RETURN VARCHAR2 AS
83 ------------------------------------------------------------------
84 --Created by : vvutukur, Oracle IDC
85 --Date created: 09-May-2003
86 --
87 --Purpose:
88 --
89 --
90 --Known limitations/enhancements and/or remarks:
91 --
92 --Change History:
93 --Who When What
94 ------------------------------------------------------------------
95
96 --Cursor to fetch the unit section details.
97 CURSOR cur_uoo_details(cp_n_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
98 SELECT unit_cd, version_number, cal_type, ci_sequence_number, location_cd, unit_class
99 FROM igs_ps_unit_ofr_opt_all
100 WHERE uoo_id = cp_n_uoo_id;
101
102 l_v_unit_cd igs_ps_unit_ofr_opt_all.unit_cd%TYPE := p_v_unit_cd;
103 l_n_version_number igs_ps_unit_ofr_opt_all.version_number%TYPE := p_n_version_number;
104 l_v_cal_type igs_ps_unit_ofr_opt_all.cal_type%TYPE := p_v_cal_type;
105 l_n_ci_sequence_number igs_ps_unit_ofr_opt_all.ci_sequence_number%TYPE := p_n_ci_sequence_number;
106 l_v_location_cd igs_ps_unit_ofr_opt_all.location_cd%TYPE := p_v_location_cd;
107 l_v_unit_class igs_ps_unit_ofr_opt_all.unit_class%TYPE := p_v_unit_class;
108
109 --Cursor to get the Teaching Calendar for the Calendar Type and Calendar Instance Sequence Number.
110 CURSOR cur_cal_type (cp_v_cal_type igs_ca_inst_all.cal_type%TYPE,
111 cp_n_seq_number igs_ca_inst_all.sequence_number%TYPE
112 ) IS
113 SELECT alternate_code
114 FROM igs_ca_inst
115 WHERE cal_type = cp_v_cal_type
116 AND sequence_number = cp_n_seq_number;
117
118 l_v_alt_code igs_ca_inst.alternate_code%TYPE;
119 l_v_uoo_id_desc VARCHAR2(4000);
120
121 BEGIN
122 --Check if a value is passed to the p_n_uoo_id parameter. If no value is passed to this parameter then return NULL from this function.
123 IF p_n_uoo_id IS NULL THEN
124 RETURN NULL;
125 ELSE
126 --If p_n_uoo_id is not null, check if value to any of the Unit Sections attributes is not provided.
127 IF (l_v_unit_cd IS NULL OR
128 l_n_version_number IS NULL OR
129 l_v_cal_type IS NULL OR
130 l_n_ci_sequence_number IS NULL OR
131 l_v_location_cd IS NULL OR
132 l_v_unit_class IS NULL
133 )THEN
134 --If any one of the unit section attributes is null, then get these details from the Unit Section Offering Options based on the Unit
135 --Section Identifier(p_n_uoo_id).
136 OPEN cur_uoo_details(p_n_uoo_id);
137 FETCH cur_uoo_details INTO l_v_unit_cd,l_n_version_number,l_v_cal_type,l_n_ci_sequence_number,l_v_location_cd,l_v_unit_class;
138
139 IF cur_uoo_details%NOTFOUND THEN
140 CLOSE cur_uoo_details;
141 RETURN NULL;
142 END IF;
143 CLOSE cur_uoo_details;
144 END IF;
145
146 --Get the Teaching Calendar for the Calendar Type and Calendar Instance Sequence Number.
147 OPEN cur_cal_type(l_v_cal_type,l_n_ci_sequence_number);
148 FETCH cur_cal_type INTO l_v_alt_code;
149 CLOSE cur_cal_type;
150
151 --Return the unit section details in concatenated string separated by comma.
152 l_v_uoo_id_desc := l_v_unit_cd||', '||l_n_version_number||', '||l_v_location_cd||', '||l_v_unit_class||', '||l_v_alt_code;
153 RETURN l_v_uoo_id_desc;
154 END IF;
155
156 END get_unit_section_desc;
157
158 PROCEDURE get_segment_num(p_n_segment_num OUT NOCOPY NUMBER) AS
159 ------------------------------------------------------------------
160 --Created by : Priya Athipatla, Oracle IDC
161 --Date created: 26-Jun-2003
162 --
163 --Purpose: To obtain the segment_num for the Natural Account Segment
164 --
165 --Known limitations/enhancements and/or remarks:
166 --
167 --Change History:
168 --Who When What
169 ------------------------------------------------------------------
170 CURSOR cur_segment_num IS
171 SELECT fs.segment_num
172 FROM fnd_segment_attribute_values av,
173 fnd_id_flex_segments fs
174 WHERE av.application_id = 101
175 AND av.id_flex_code = 'GL#'
176 AND av.id_flex_num = igs_fi_gen_007.get_coa_id
177 AND fs.application_id = av.application_id
178 AND fs.id_flex_code = av.id_flex_code
179 AND fs.id_flex_num = av.id_flex_num
180 AND fs.application_column_name = av.application_column_name
181 AND av.segment_attribute_type = 'GL_ACCOUNT'
182 AND av.attribute_value = 'Y';
183
184 BEGIN
185
186 OPEN cur_segment_num;
187 FETCH cur_segment_num INTO p_n_segment_num;
188 CLOSE cur_segment_num;
189
190 END get_segment_num;
191
192 FUNCTION get_segment_desc(p_n_segment_num IN NUMBER,
193 p_v_natural_acc_seg IN VARCHAR2) RETURN VARCHAR2 AS
194 ------------------------------------------------------------------
195 --Created by : Priya Athipatla, Oracle IDC
196 --Date created: 02-Jul-2003
197 --
198 --Purpose: Returns the description of the natural account segment
199 --
200 --Known limitations/enhancements and/or remarks:
201 --
202 --Change History:
203 --Who When What
204 ------------------------------------------------------------------
205 l_b_enabled BOOLEAN := FALSE;
206
207 BEGIN
208
209 l_b_enabled := fnd_flex_keyval.validate_segs( operation => 'CHECK_SEGMENTS',
210 appl_short_name => 'SQLGL',
211 key_flex_code => 'GL#',
212 structure_number => igs_fi_gen_007.get_coa_id,
213 displayable => p_n_segment_num,
214 allow_nulls => TRUE,
215 vrule => 'GL_ACCOUNT\nGL_ACCOUNT_TYPE\nI\nAPPL=IGS;NAME=IGS_FI_ACC_REV\nR',
216 concat_segments => p_v_natural_acc_seg
217 );
218
219 RETURN (fnd_flex_keyval.segment_description(p_n_segment_num)) ;
220
221 END get_segment_desc;
222
223
224 END igs_fi_gen_apint;