1 package body gms_acc_gen_api as
2 /* $Header: gmsacgnb.pls 120.2 2007/02/06 09:47:27 rshaik ship $ */
3
4
5 -- ================================================================
6 -- Get award_id for Expenditures OR encumbrance items.
7 -- ================================================================
8 FUNCTION GET_AWARD_ID (x_exp_item_id in NUMBER
9 ,x_doc_type IN VARCHAR2
10 ,x_cdl_line_num in NUMBER
11 ,x_err_code out NOCOPY NUMBER
12 ,x_err_msg out NOCOPY varchar2)
13 return NUMBER is
14
15 x_award_id NUMBER ;
16
17
18 CURSOR C_exp_award is
19 SELECT award_id
20 FROM gms_award_distributions
21 WHERE document_type = x_doc_type
22 and expenditure_item_id = x_exp_item_id
23 and nvl(cdl_line_num,1) = NVL(x_cdl_line_num,1)
24 and ADL_STATUS = 'A' ;
25
26 BEGIN
27 x_err_code := 0;
28 x_err_msg := '';
29 x_award_id := 0 ;
30
31 IF x_doc_type not in ( 'EXP', 'ENC' ) THEN
32 x_err_code := 1 ;
33 x_err_msg := 'DOC_TYPE_NOT_EXP_ENC' ;
34 return 0 ;
35 END IF ;
36
37 open C_exp_award ;
38
39 fetch C_exp_award into x_award_id ;
40
41 IF C_exp_award%NOTFOUND THEN
42 x_award_id := -1 ;
43 x_err_code := 1 ;
44 x_err_msg := 'NO_DATA_FOUND' ;
45 END IF ;
46
47 close C_exp_award ;
48
49 IF x_award_id = -1 THEN
50 x_award_id := 0 ;
51 END IF ;
52
53 return x_award_id ;
54
55 EXCEPTION
56 When OTHERS THEN
57 x_err_code := 1;
58
59 IF x_err_msg = '' THEN
60 x_err_msg := 'WHEN-OTHERS-EXCEPTION';
61 END IF ;
62
63 IF C_exp_award%ISOPEN THEN
64 CLOSE C_exp_award ;
65 END IF ;
66
67 app_exception.raise_exception;
68 END get_award_id ;
69
70
71 -- ============================================================
72 -- Get award_id for passed award_set_id or Default award_id
73 -- depending on award_distribution is enabled
74 -- ============================================================
75 FUNCTION GET_AWARD_ID (x_award_set_id in NUMBER
76 ,x_attr_award_id in VARCHAR2
77 ,x_document_type in VARCHAR2
78 ,x_err_code out NOCOPY NUMBER
79 ,x_err_msg out NOCOPY varchar2)
80 return NUMBER is
81
82 -- Bug 2930402 fix : The cursor below is not required.
83 cursor get_award_id_w_doc_type (p_award_set_id in NUMBER, p_document_type in varchar2) is
84 select award_id
85 from gms_award_distributions
86 where award_set_id = p_award_set_id
87 and document_type = p_document_type
88 and adl_status = 'A'
89 and adl_line_num = 1;
90
91 cursor get_award_id_wo_doc_type (p_award_set_id in NUMBER) is
92 select award_id
93 from gms_award_distributions
94 where award_set_id = p_award_set_id
95 -- and adl_status = 'A' -- Bug 2930402 fix.
96 and adl_line_num = 1;
97
98 cursor get_default_dist_award_id is
99 select default_dist_award_id
100 from gms_implementations;
101
102 l_award_id NUMBER;
103 x_default_dist_award_id NUMBER;
104
105 BEGIN
106 x_err_code := 0;
107 x_err_msg := '';
108
109 -- if the first 8 characters of p_attr_award_id is 'SSP-GMS:' then
110 -- anything that follows is the award_id and is coming from SSP.
111
112 open get_default_dist_award_id;
113 fetch get_default_dist_award_id into x_default_dist_award_id;
114 close get_default_dist_award_id;
115
116 if x_award_set_id = x_default_dist_award_id THEN
117 return x_award_set_id ;
118 end if;
119
120 /* Bug 2930402 fix..following is not required.
121
122 if substr(x_attr_award_id,1,8) = 'SSP-GMS:' then
123 return substr(x_attr_award_id,9);
124 end if;
125
126 */
127
128 if x_award_set_id is NULL then
129 x_err_code := 1;
130 x_err_msg := 'GMS_SSP_AWARD_SET_ID_NULL';
131 app_exception.raise_exception;
132 end if;
133
134 open get_award_id_wo_doc_type (x_award_set_id);
135 fetch get_award_id_wo_doc_type into l_award_id;
136
137 IF get_award_id_wo_doc_type%NOTFOUND THEN
138 raise NO_DATA_FOUND ;
139 END IF ;
140
141 close get_award_id_wo_doc_type;
142
143 return l_award_id;
144
145 /* Bug 2930402 fix. The following is not required.
146
147 if nvl(x_document_type, 'REQ') not in ('REQ','PO','AP','APD','ENC','OPI') then
148 x_err_code := 1;
149 x_err_msg := 'GMS_SSP_INVALID_DOC_TYPE';
150 app_exception.raise_exception;
151 end if;
152
153 if x_document_type is NULL then
154 open get_award_id_wo_doc_type (x_award_set_id);
155 fetch get_award_id_wo_doc_type into l_award_id;
156
157 IF get_award_id_wo_doc_type%NOTFOUND THEN
158 raise NO_DATA_FOUND ;
159 END IF ;
160
161 close get_award_id_wo_doc_type;
162 else
163 --x_document_type in ('REQ','PO','AP','APD','ENC','OPI') then
164 open get_award_id_w_doc_type (x_award_set_id, x_document_type);
165 fetch get_award_id_w_doc_type into l_award_id;
166
167 IF get_award_id_w_doc_type%NOTFOUND THEN
168 raise NO_DATA_FOUND ;
169 END IF ;
170
171 close get_award_id_w_doc_type;
172 end if;
173
174 -- Bug 2930402 fix. */
175
176 EXCEPTION
177 when OTHERS then
178
179 IF get_award_id_w_doc_type%ISOPEN THEN
180 CLOSE get_award_id_w_doc_type ;
181 END IF ;
182
183 IF get_award_id_wo_doc_type%ISOPEN THEN
184 CLOSE get_award_id_wo_doc_type ;
185 END IF ;
186
187 IF get_default_dist_award_id%ISOPEN THEN
188 CLOSE get_default_dist_award_id;
189 END IF ;
190
191
192 x_err_code := 1;
193
194 IF x_err_msg = '' THEN
195 x_err_msg := 'GMS_SSP_AWARD_SET_ID_NULL';
196 END IF ;
197
198 app_exception.raise_exception;
199
200 END GET_AWARD_ID;
201 ---------------------------------------------------------------------
202
203
204 -- ==================================================================
205 -- GET_AWARD_ID defined for workflow and account generator.
206 -- ==================================================================
207 FUNCTION GET_AWARD_ID (itemtype IN VARCHAR2
208 , itemkey IN VARCHAR2
209 , actid IN NUMBER
210 , funcmode IN VARCHAR2
211 , resultout OUT NOCOPY VARCHAR2,
212 p_doc_type IN VARCHAR2 )
213
214 return NUMBER
215 IS
216
217 l_award_set_id NUMBER;
218 l_attr_award_id VARCHAR2(30);
219 l_award_id NUMBER;
220 l_err_code NUMBER;
221 l_err_msg VARCHAR2(2000);
222 l_doc_type varchar2(3) ;
223
224 CURSOR GET_DOC_TYPE (p_award_set_id in NUMBER) is
225 select document_type
226 from gms_award_distributions
227 where award_set_id = p_award_set_id
228 and adl_status = 'A'
229 and adl_line_num = 1;
230
231 BEGIN
232
233 if (funcmode <> wf_engine.eng_run) then
234 resultout := 'ERROR';
235 RETURN 0;
236 end if;
237
238 l_award_set_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
239 itemkey => itemkey,
240 aname => 'AWARD_SET_ID' );
241
242
243 IF l_award_set_id is not NULL AND
244 l_award_set_id > 0 THEN
245 open get_doc_type (l_award_set_id) ;
246 FETCH get_doc_type into l_doc_type ;
247
248 IF get_doc_type%NOTFOUND THEN
249 raise NO_DATA_FOUND ;
250 END IF ;
251
252 CLOSE get_doc_type ;
253
254 END IF ;
255
256 IF NVL(l_doc_type,P_DOC_TYPE) IN ( 'REQ' ) THEN
257
258 l_attr_award_id := wf_engine.GetItemAttrText( itemtype => itemtype,
259 itemkey => itemkey,
260 aname => 'LINE_ATT7' );
261
262 END IF ;
263
264
265 l_award_id := get_award_id (x_award_set_id => l_award_set_id,
266 x_attr_award_id => l_attr_award_id,
267 x_document_type => l_doc_type,
268 x_err_code => l_err_code,
269 x_err_msg => l_err_msg);
270
271 if l_err_code <> 0 then
272 resultout := 'COMPLETE:FAILURE';
273 end if;
274
275 resultout := 'COMPLETE:SUCCESS' ;
276
277 return l_award_id;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 resultout := 'COMPLETE:FAILURE';
282
283 IF get_doc_type%ISOPEN THEN
284 close get_doc_type ;
285 END IF ;
286
287 RAISE ;
288 END GET_AWARD_ID;
289
290
291 -- ===================================================
292 -- BUG : 1703224 GENERIC API TO GET AWARD FROM ADL.
293 -- ===================================================
294
295 FUNCTION GET_AWARD_ID ( x_award_set_id IN NUMBER,
296 x_doc_type IN varchar2
297 ) return NUMBER IS
298 x_award_id NUMBER ;
299 BEGIN
300
301 IF x_award_set_id is NULL THEN
302 return NULL ;
303 END IF ;
304
305 IF NVL(x_doc_type,'X') in ( 'PO','REQ','AP' ) THEN
306
307 SELECT award_id
308 INTO x_award_id
309 FROM gms_award_distributions
310 WHERE award_set_id = x_award_set_id
311 and document_type = x_doc_type
312 and adl_status = 'A'
313 and adl_line_num = 1 ;
314
315 END IF ;
316
317 return x_award_id ;
318
319 END GET_AWARD_ID ;
320
321 -- ===================================================
322 -- BUG : 1703224 GENERIC API TO GET AWARD FROM ADL.
323 -- ===================================================
324 FUNCTION GET_AWARD_ID ( x_exp_enc_item_id IN NUMBER,
325 x_doc_type IN varchar2,
326 x_cdl_line IN NUMBER
327 ) return NUMBER IS
328 x_award_id NUMBER ;
329 BEGIN
330
331 IF x_exp_enc_item_id is NULL THEN
332 return NULL ;
333 END IF ;
334
335 IF NVL(x_doc_type,'X') in ( 'EXP' , 'ENC' ) THEN
336
337 SELECT award_id
338 INTO x_award_id
339 FROM gms_award_distributions
340 WHERE expenditure_item_id = NVL( x_exp_enc_item_id, 0)
341 AND nvl(cdl_line_num, 1) = NVL(x_cdl_line,1) --Bug 5726575
342 AND document_type = x_doc_type
343 and adl_status = 'A' ;
344
345 END IF ;
346
347 return x_award_id ;
348
349 END GET_AWARD_ID ;
350
351
352 END gms_acc_gen_api;
353
[Home] [Help]