[Home] [Help]
PACKAGE BODY: APPS.GMS_POR_API2
Source
1 PACKAGE BODY GMS_POR_API2 as
2 --$Header: gmspor2b.pls 120.0 2005/05/29 11:39:42 appldev noship $
3
4
5 --=============================================================
6 -- Bug-2557041
7 -- The purpose of this API is to prepare for award distributions
8 -- and kicks off award distribution engine
9 --=============================================================
10 PROCEDURE distribute_award(p_doc_header_id IN NUMBER,
11 p_distribution_id IN NUMBER,
12 p_document_source IN VARCHAR2,
13 p_gl_encumbered_date IN DATE,
14 p_project_id IN NUMBER,
15 p_task_id IN NUMBER,
16 p_dummy_award_id IN NUMBER,
17 p_expenditure_type IN VARCHAR2,
18 p_expenditure_organization_id IN NUMBER,
19 p_expenditure_item_date IN DATE,
20 p_quantity IN NUMBER,
21 p_unit_price IN NUMBER,
22 p_func_amount IN NUMBER,
23 p_vendor_id IN NUMBER,
24 p_source_type_code IN VARCHAR2,
25 p_award_qty_obj OUT NOCOPY gms_obj_award2,
26 p_status OUT NOCOPY VARCHAR2,
27 p_error_msg_label OUT NOCOPY VARCHAR2 )
28 AS
29 l_doc_header_id NUMBER;
30 l_distribution_id NUMBER;
31 l_document_source VARCHAR2(4);
32 l_index INTEGER;
33 l_project_id NUMBER ;
34
35 l_award_qty_obj gms_obj_award2;
36 l_dist_status VARCHAR2(5);
37 l_status VARCHAR2(1);
38 l_msg_label VARCHAR2(2000);
39 l_recs_processed NUMBER;
40 l_recs_rejected NUMBER;
41 l_spon_flag gms_project_types.sponsored_flag%TYPE ;
42 l_source_type_code po_requisition_lines_all.source_type_code%type;
43
44 cursor C_spon_project is
45 select pt.sponsored_flag
46 from pa_projects_all b,
47 gms_project_types pt
48 where b.project_id = l_project_id
49 and b.project_type = pt.project_type
50 and pt.sponsored_flag = 'Y' ;
51 CURSOR c_next_header_id IS
52 SELECT gms_packet_header_id_s.NEXTVAL
53 FROM DUAL;
54
55 CURSOR c_next_dist_id IS
56 SELECT gms_packet_dist_id_s.NEXTVAL
57 FROM DUAL;
58
59 CURSOR c_awd_dist_status IS
60 SELECT awd.dist_status
61 FROM gms_distributions awd
62 WHERE awd.document_distribution_id = l_distribution_id
63 AND awd.document_header_id = l_doc_header_id
64 AND awd.document_type = l_document_source
65 AND awd.dist_status <>'FABA';
66 BEGIN
67 -- Initilaize the Object
68 --l_award_qty_obj :=GMS_OBJ_AWARD2(GMS_TYPE_VARCHAR20(),GMS_TYPE_NUMBER(),
69 -- GMS_TYPE_NUMBER(), GMS_TYPE_NUMBER());
70 l_award_qty_obj :=GMS_OBJ_AWARD2.init_gms_obj_award2();
71
72 l_doc_header_id :=p_doc_header_id ;
73 l_distribution_id :=p_distribution_id ;
74 l_document_source :=p_document_source ;
75 l_status :='S';
76 p_status :='S' ;
77 l_source_type_code := p_source_type_code;
78
79
80 IF not gms_install.enabled then
81 return ;
82 END IF ;
83
84 IF NVL(p_dummy_award_id,0) >= 0 THEN
85 p_status :='S';
86 Return ;
87 END IF ;
88
89 --==============================================================
90 -- Do not proceed if grants is enabled and Requisition type is
91 -- Internal
92 --==============================================================
93 l_project_id := p_project_id ;
94 open C_spon_project ;
95 fetch C_spon_project into l_spon_flag ;
96 close C_spon_project ;
97
98
99 --IF gms_por_api.is_sponsored_project (p_project_id) THEN
100 IF NVL(l_spon_flag, 'N') = 'Y' THEN
101 IF nvl(l_source_type_code,'INVENTORY') = 'INVENTORY' THEN
102 p_error_msg_label := 'GMS_IP_INVALID_REQ_TYPE';
103 p_status := 'E';
104 return;
105 END IF;
106 ELSE
107 -- 2. Nonsponsored project having award should fail.
108 p_error_msg_label := 'GMS_AWARD_NOT_ALLOWED';
109 p_status := 'E';
110 return;
111 END IF;
112
113
114 GMS_POR_API.validate_dist_award(P_project_id,
115 P_task_id,
116 P_dummy_award_id,
117 P_expenditure_type,
118 l_status,
119 l_msg_label ) ;
120
121 IF l_status <> 'S' THEN
122 p_error_msg_label:= l_msg_label ;
123 p_status := l_status ;
124 return ;
125 END IF ;
126
127 IF l_doc_header_id is NULL THEN
128 OPEN c_next_header_id;
129 FETCH c_next_header_id
130 INTO l_doc_header_id;
131 CLOSE c_next_header_id;
132 END IF ;
133
134 IF l_distribution_id is NULL THEN
135 OPEN c_next_dist_id;
136 FETCH c_next_dist_id
137 INTO l_distribution_id;
138 CLOSE c_next_dist_id;
139 END IF ;
140
141 IF l_Document_source = 'IREQ' THEN
142 l_document_source:= 'REQ' ;
143 END IF ;
144
145 --===========================================
146 -- Insert Records into gms_distribution table
147 --===========================================
148 INSERT INTO gms_distributions
149 ( document_header_id ,
150 document_distribution_id,
151 document_type,
152 gl_date,
153 project_id,
154 task_id,
155 expenditure_type,
156 expenditure_organization_id,
157 expenditure_item_date,
158 quantity,
159 unit_price,
160 amount,
161 dist_status,
162 creation_date)
163 VALUES ( l_doc_header_id,
164 l_distribution_id,
165 l_document_source,
166 p_gl_encumbered_date,
167 p_project_id,
168 p_task_id,
169 p_expenditure_type,
170 p_expenditure_organization_id,
171 p_expenditure_item_date,
172 p_quantity,
173 p_unit_price,
174 p_func_amount,
175 NULL,
176 SYSDATE );
177
178 GMS_AWARD_DIST_ENG.PROC_DISTRIBUTE_RECORDS(l_doc_header_id, 'REQ',l_recs_processed,l_recs_rejected);
179 --process the results of PROC_DISTRIBUTE_RECORDS
180
181 IF NVL(l_recs_processed,0) > 0 THEN
182 --populate the return variables.
183 SELECT a.award_number ,
184 awdd.award_id ,
185 awdd.quantity_distributed,
186 awdd.amount_distributed
187 BULK COLLECT INTO l_award_qty_obj.award_num,
188 l_award_qty_obj.award_id,
189 l_award_qty_obj.quantity,
190 l_award_qty_obj.amount
191 FROM gms_distribution_details awdd,
192 gms_distributions awd,
193 gms_awards_all a
194 WHERE awd.document_distribution_id = awdd.document_distribution_id
195 AND awd.document_header_id = awdd.document_header_id
196 AND awd.document_distribution_id = l_distribution_id
197 AND awd.document_header_id = l_doc_header_id
198 AND awd.document_type = l_document_source
199 AND awd. dist_status = 'FABA'
200 AND awdd.award_id = a.Award_id;
201 END IF;
202
203 IF NVL(l_recs_rejected,0) > 0 THEN
204
205 l_status :='E';--failed status
206 OPEN c_awd_dist_status;
207 FETCH c_awd_dist_status INTO l_dist_status;
208 CLOSE c_awd_dist_status;
209
210 IF l_dist_status ='ERR01' THEN
211 l_msg_label := 'GMS_FP_VALIDATION_FAILED';
212 --Unable to distribute because funding pattern didn't pass validations
213 ELSIF l_dist_status ='ERR02' THEN
214 l_msg_label := 'GMS_FP_NOT_FOUND';
215 --Unable to distribute because funding pattern not found
216 ELSIF l_dist_status ='ERR03' THEN
217 l_msg_label := 'GMS_FP_CHECK_FUNDS_FAILED';
218 -- Unable to distribute because funding pattern doesn't have enough funds
219
220 END IF;
221
222 END IF;
223
224 p_status := l_status;
225 p_award_qty_obj := l_award_qty_obj;
226
227 IF l_msg_label IS NOT NULL THEN
228 P_error_msg_label :=l_msg_label;
229 END IF;
230
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 p_status :='U';
235 p_error_msg_label :='GMS_UNDEFINED_EXCEPTION';
236 END distribute_award;
237
238 END GMS_POR_API2;