1 PACKAGE BODY GMS_POR_API2 as
2 --$Header: gmspor2b.pls 120.2 2011/08/29 09:56:45 mokukuma ship $
3
4 --Added debug messages for the bug 12901042
5 --Global Variables initialized in GMS_POR_API2:
6
7 g_debug Varchar2(1); -- To check on, whether to print debug messages in log file or not
8 g_error_procedure_name Varchar2 (30);
9 --End for the bug 12901042
10
11 --=============================================================
12 -- Bug-2557041
13 -- The purpose of this API is to prepare for award distributions
14 -- and kicks off award distribution engine
15 --=============================================================
16 PROCEDURE distribute_award(p_doc_header_id IN NUMBER,
17 p_distribution_id IN NUMBER,
18 p_document_source IN VARCHAR2,
19 p_gl_encumbered_date IN DATE,
20 p_project_id IN NUMBER,
21 p_task_id IN NUMBER,
22 p_dummy_award_id IN NUMBER,
23 p_expenditure_type IN VARCHAR2,
24 p_expenditure_organization_id IN NUMBER,
25 p_expenditure_item_date IN DATE,
26 p_quantity IN NUMBER,
27 p_unit_price IN NUMBER,
28 p_func_amount IN NUMBER,
29 p_vendor_id IN NUMBER,
30 p_source_type_code IN VARCHAR2,
31 p_award_qty_obj OUT NOCOPY gms_obj_award2,
32 p_status OUT NOCOPY VARCHAR2,
33 p_error_msg_label OUT NOCOPY VARCHAR2 )
34 AS
35 l_doc_header_id NUMBER;
36 l_distribution_id NUMBER;
37 l_document_source VARCHAR2(4);
38 l_index INTEGER;
39 l_project_id NUMBER ;
40
41 l_award_qty_obj gms_obj_award2;
42 l_dist_status VARCHAR2(5);
43 l_status VARCHAR2(1);
44 l_msg_label VARCHAR2(2000);
45 l_recs_processed NUMBER;
46 l_recs_rejected NUMBER;
47 l_spon_flag gms_project_types.sponsored_flag%TYPE ;
48 l_source_type_code po_requisition_lines_all.source_type_code%type;
49
50 cursor C_spon_project is
51 select pt.sponsored_flag
52 from pa_projects_all b,
53 gms_project_types pt
54 where b.project_id = l_project_id
55 and b.project_type = pt.project_type
56 and pt.sponsored_flag = 'Y' ;
57 CURSOR c_next_header_id IS
58 SELECT gms_packet_header_id_s.NEXTVAL
59 FROM DUAL;
60
61 CURSOR c_next_dist_id IS
62 SELECT gms_packet_dist_id_s.NEXTVAL
63 FROM DUAL;
64
65 CURSOR c_awd_dist_status IS
66 SELECT awd.dist_status
67 FROM gms_distributions awd
68 WHERE awd.document_distribution_id = l_distribution_id
69 AND awd.document_header_id = l_doc_header_id
70 AND awd.document_type = l_document_source
71 AND awd.dist_status <>'FABA';
72 BEGIN
73 -- Initilaize the Object
74 --l_award_qty_obj :=GMS_OBJ_AWARD2(GMS_TYPE_VARCHAR20(),GMS_TYPE_NUMBER(),
75 -- GMS_TYPE_NUMBER(), GMS_TYPE_NUMBER());
76
77 g_error_procedure_name := 'distribute_award';
78
79 --Added debug messages for the bug 12901042
80 IF g_debug = 'Y' THEN
81 gms_error_pkg.gms_debug ('Inside GMS_POR_API2.distribute_award','C');
82 gms_error_pkg.gms_debug (g_error_procedure_name||': START','C');
83 END IF;
84 --End for the bug 12901042
85
86 l_award_qty_obj :=GMS_OBJ_AWARD2.init_gms_obj_award2();
87
88 l_doc_header_id :=p_doc_header_id ;
89 l_distribution_id :=p_distribution_id ;
90 l_document_source :=p_document_source ;
91 l_status :='S';
92 p_status :='S' ;
93 l_source_type_code := p_source_type_code;
94
95
96 IF not gms_install.enabled then
97 return ;
98 END IF ;
99
100 IF NVL(p_dummy_award_id,0) >= 0 THEN
101 p_status :='S';
102 Return ;
103 END IF ;
104
105 --==============================================================
106 -- Do not proceed if grants is enabled and Requisition type is
107 -- Internal
108 --==============================================================
109 l_project_id := p_project_id ;
110 open C_spon_project ;
111 fetch C_spon_project into l_spon_flag ;
112 close C_spon_project ;
113
114
115 --IF gms_por_api.is_sponsored_project (p_project_id) THEN
116 IF NVL(l_spon_flag, 'N') = 'Y' THEN
117 IF nvl(l_source_type_code,'INVENTORY') = 'INVENTORY' THEN
118 p_error_msg_label := 'GMS_IP_INVALID_REQ_TYPE';
119 p_status := 'E';
120 return;
121 END IF;
122 ELSE
123 -- 2. Nonsponsored project having award should fail.
124 p_error_msg_label := 'GMS_AWARD_NOT_ALLOWED';
125 p_status := 'E';
126 return;
127 END IF;
128
129 -- Added below condition for the bug 12426747
130 IF p_gl_encumbered_date IS NULL THEN
131 p_error_msg_label := 'GMS_GL_DATE_IS_NULL';
132 p_status := 'E';
133 return;
134 END IF;
135 -- End for the bug 12426747
136
137 --Added debug messages for the bug 12901042
138 IF g_debug = 'Y' THEN
139 gms_error_pkg.gms_debug (g_error_procedure_name||' Before Calling GMS_POR_API.validate_dist_award','C');
140 END IF;
141 --End for the bug 12901042
142
143 GMS_POR_API.validate_dist_award(P_project_id,
144 P_task_id,
145 P_dummy_award_id,
146 P_expenditure_type,
147 l_status,
148 l_msg_label ) ;
149
150 --Added debug messages for the bug 12901042
151 IF g_debug = 'Y' THEN
152 gms_error_pkg.gms_debug (g_error_procedure_name||' After Calling GMS_POR_API.validate_dist_award','C');
153 gms_error_pkg.gms_debug (g_error_procedure_name||' l_status='||l_status,'C');
154 gms_error_pkg.gms_debug (g_error_procedure_name||' l_msg_label='||l_msg_label,'C');
155 END IF;
156 --End for the bug 12901042
157
158 IF l_status <> 'S' THEN
159 p_error_msg_label:= l_msg_label ;
160 p_status := l_status ;
161 return ;
162 END IF ;
163
164 IF l_doc_header_id is NULL THEN
165 OPEN c_next_header_id;
166 FETCH c_next_header_id
167 INTO l_doc_header_id;
168 CLOSE c_next_header_id;
169 END IF ;
170
171 IF l_distribution_id is NULL THEN
172 OPEN c_next_dist_id;
173 FETCH c_next_dist_id
174 INTO l_distribution_id;
175 CLOSE c_next_dist_id;
176 END IF ;
177
178 IF l_Document_source = 'IREQ' THEN
179 l_document_source:= 'REQ' ;
180 END IF ;
181
182 --Added debug messages for the bug 12901042
183 IF g_debug = 'Y' THEN
184 gms_error_pkg.gms_debug (g_error_procedure_name||' Inserting records in gms_distributions','C');
185 END IF;
186 --End for the bug 12901042
187
188 --===========================================
189 -- Insert Records into gms_distribution table
190 --===========================================
191 INSERT INTO gms_distributions
192 ( document_header_id ,
193 document_distribution_id,
194 document_type,
195 gl_date,
196 project_id,
197 task_id,
198 expenditure_type,
199 expenditure_organization_id,
200 expenditure_item_date,
201 quantity,
202 unit_price,
203 amount,
204 dist_status,
205 creation_date)
206 VALUES ( l_doc_header_id,
207 l_distribution_id,
208 l_document_source,
209 p_gl_encumbered_date,
210 p_project_id,
211 p_task_id,
212 p_expenditure_type,
213 p_expenditure_organization_id,
214 p_expenditure_item_date,
215 p_quantity,
216 p_unit_price,
217 p_func_amount,
218 NULL,
219 SYSDATE );
220
221 --Added debug messages for the bug 12901042
222 IF g_debug = 'Y' THEN
223 gms_error_pkg.gms_debug (g_error_procedure_name||' Before calling GMS_AWARD_DIST_ENG.PROC_DISTRIBUTE_RECORDS','C');
224 END IF;
225 --End for the bug 12901042
226
227 GMS_AWARD_DIST_ENG.PROC_DISTRIBUTE_RECORDS(l_doc_header_id, 'REQ',l_recs_processed,l_recs_rejected);
228 --process the results of PROC_DISTRIBUTE_RECORDS
229
230 --Added debug messages for the bug 12901042
231 IF g_debug = 'Y' THEN
232 gms_error_pkg.gms_debug (g_error_procedure_name||' After calling GMS_AWARD_DIST_ENG.PROC_DISTRIBUTE_RECORDS','C');
233 gms_error_pkg.gms_debug (g_error_procedure_name||' l_recs_processed='||l_recs_processed,'C');
234 gms_error_pkg.gms_debug (g_error_procedure_name||' l_recs_rejected='||l_recs_rejected,'C');
235 END IF;
236 --End for the bug 12901042
237
238 IF NVL(l_recs_processed,0) > 0 THEN
239 --populate the return variables.
240 SELECT a.award_number ,
241 awdd.award_id ,
242 awdd.quantity_distributed,
243 awdd.amount_distributed
244 BULK COLLECT INTO l_award_qty_obj.award_num,
245 l_award_qty_obj.award_id,
246 l_award_qty_obj.quantity,
247 l_award_qty_obj.amount
248 FROM gms_distribution_details awdd,
249 gms_distributions awd,
250 gms_awards_all a
251 WHERE awd.document_distribution_id = awdd.document_distribution_id
252 AND awd.document_header_id = awdd.document_header_id
253 AND awd.document_distribution_id = l_distribution_id
254 AND awd.document_header_id = l_doc_header_id
255 AND awd.document_type = l_document_source
256 AND awd. dist_status = 'FABA'
257 AND awdd.award_id = a.Award_id;
258 END IF;
259
260 IF NVL(l_recs_rejected,0) > 0 THEN
261
262 l_status :='E';--failed status
263 OPEN c_awd_dist_status;
264 FETCH c_awd_dist_status INTO l_dist_status;
265 CLOSE c_awd_dist_status;
266
267 IF l_dist_status ='ERR01' THEN
268 l_msg_label := 'GMS_FP_VALIDATION_FAILED';
269 --Unable to distribute because funding pattern didn't pass validations
270 ELSIF l_dist_status ='ERR02' THEN
271 l_msg_label := 'GMS_FP_NOT_FOUND';
272 --Unable to distribute because funding pattern not found
273 ELSIF l_dist_status ='ERR03' THEN
274 l_msg_label := 'GMS_FP_CHECK_FUNDS_FAILED';
275 -- Unable to distribute because funding pattern doesn't have enough funds
276
277 END IF;
278
279 END IF;
280
281 p_status := l_status;
282 p_award_qty_obj := l_award_qty_obj;
283
284 IF l_msg_label IS NOT NULL THEN
285 P_error_msg_label :=l_msg_label;
286 END IF;
287
288 IF g_debug = 'Y' THEN
289 gms_error_pkg.gms_debug (g_error_procedure_name||': END','C');
290 END IF;
291
292 EXCEPTION
293 WHEN OTHERS THEN
294
295 --Added debug messages for the bug 12901042
296 IF g_debug = 'Y' THEN
297 gms_error_pkg.gms_debug ('In When OTHERS Exception of distribute_award'|| ' SQLCODE:'|| SQLCODE|| ' SQLERRM:'|| SQLERRM,'C');
298 END IF;
299 --End for the bug 12901042
300
301 p_status :='U';
302 p_error_msg_label :='GMS_UNDEFINED_EXCEPTION';
303 END distribute_award;
304
305 END GMS_POR_API2;
[Home] [Help]