[Home] [Help]
PACKAGE BODY: APPS.FV_PO_VALIDATE_GRP
Source
4 function get_balance_segment(l_ledger_id number) return varchar2;
1 package body FV_PO_VALIDATE_GRP as
2 -- $Header: FVPOVALB.pls 120.1 2005/08/16 16:30:17 ksriniva noship $
3
5
6
7 PROCEDURE CHECK_AGREEMENT_DATES(x_code_combination_id in number,
8 x_org_id in number,
9 x_ledger_id in number,
10 x_called_from in varchar2,
11 x_ATTRIBUTE1 IN VARCHAR2,
12 x_ATTRIBUTE2 IN VARCHAR2,
13 x_ATTRIBUTE3 IN VARCHAR2,
14 x_ATTRIBUTE4 IN VARCHAR2,
15 x_ATTRIBUTE5 IN VARCHAR2,
19 x_ATTRIBUTE9 IN VARCHAR2,
16 x_ATTRIBUTE6 IN VARCHAR2,
17 x_ATTRIBUTE7 IN VARCHAR2,
18 x_ATTRIBUTE8 IN VARCHAR2,
20 x_ATTRIBUTE10 IN VARCHAR2,
21 x_ATTRIBUTE11 IN VARCHAR2,
22 x_ATTRIBUTE12 IN VARCHAR2,
23 x_ATTRIBUTE13 IN VARCHAR2,
24 x_ATTRIBUTE14 IN VARCHAR2,
25 x_ATTRIBUTE15 IN VARCHAR2,
26 x_status out nocopy varchar2,
27 x_message out nocopy varchar2) is
28
29 l_agreement_num_col VARCHAR2(30);
30 l_start_date_col VARCHAR2(30);
31 l_end_date_col VARCHAR2(30);
32 l_commitment_start_date DATE;
33 l_commitment_end_date DATE;
34
35 l_err_code BOOLEAN;
36 l_app_id number;
37 l_dff varchar2(40);
38 l_err_mesg VARCHAR2(250);
39 l_status varchar2(1);
40 l_message VARCHAR2(50);
41
42 l_fund_value VARCHAR2(25);
43 l_stmt VARCHAR2(500);
44 l_stmt VARCHAR2(500);
45 l_profile_value VARCHAR2(1);
46 l_warning VARCHAR2(1);
47 l_ccid NUMBER(15);
48 l_fund_category fv_fund_parameters.fund_category%TYPE;
49 l_start_dt VARCHAR2(150);
50 l_end_dt VARCHAR2(150);
51 l_bal_seg_name VARCHAR2(30);
52 l_expiration_date DATE;
53 l_start_date DATE;
54 l_end_date DATE;
55 l_start_commit_date DATE;
56 l_end_commit_date DATE;
57 l_agreement_number varchar2(30);
58 l_module varchar2(200) :='FV_PO_VALIDATE_PKG.check_agreement_date';
59
60 BEGIN
61
62 x_message := null;
63 x_status := 'S';
64
65
66
67 FND_PROFILE.GET('FV_VERIFY_REIMBURSABLE_DATES', l_profile_value);
68 FND_PROFILE.GET('FV_WARNING_MESSAGE', l_warning);
69
70 if ( nvl(l_profile_value , 'N') <> 'Y') then
71 return;
72 End if;
73
74 --determine fund segment
75 --------------------------------
76 l_bal_seg_name := get_balance_segment(x_ledger_id);
77 --------------------------------------
78
79 if l_bal_seg_name = 'ERROR' then
80 x_message := 'FV_RA_NO_FUND';
81 x_status := 'E';
82 return;
83 End if;
84
85 l_ccid := x_code_combination_id;
86
87 SELECT decode(l_bal_seg_name,
88 'SEGMENT1', gcc.segment1,
89 'SEGMENT2', gcc.segment2,
90 'SEGMENT3', gcc.segment3,
91 'SEGMENT4', gcc.segment4,
92 'SEGMENT5', gcc.segment5,
93 'SEGMENT6', gcc.segment6,
94 'SEGMENT7', gcc.segment7,
95 'SEGMENT8', gcc.segment8,
96 'SEGMENT9', gcc.segment9,
97 'SEGMENT10',gcc.segment10,
98 'SEGMENT11',gcc.segment11,
99 'SEGMENT12',gcc.segment12,
100 'SEGMENT13',gcc.segment13,
101 'SEGMENT14',gcc.segment14,
102 'SEGMENT15',gcc.segment15,
103 'SEGMENT16',gcc.segment16,
104 'SEGMENT17',gcc.segment17,
105 'SEGMENT18',gcc.segment18,
106 'SEGMENT19',gcc.segment19,
107 'SEGMENT20',gcc.segment20,
108 'SEGMENT21',gcc.segment21,
109 'SEGMENT22',gcc.segment22,
110 'SEGMENT23',gcc.segment23,
111 'SEGMENT24',gcc.segment24,
112 'SEGMENT25',gcc.segment25,
113 'SEGMENT26',gcc.segment26,
114 'SEGMENT27',gcc.segment27,
115 'SEGMENT28',gcc.segment28,
116 'SEGMENT29',gcc.segment29,
117 'SEGMENT30',gcc.segment30)
118 INTO l_fund_value
119 FROM gl_code_combinations gcc
120 where code_combination_id = l_ccid;
121
122 BEGIN
123 SELECT fund_category, fts.expiration_date
124 INTO l_fund_category , l_expiration_date
125 FROM fv_fund_parameters fp,
126 fv_treasury_symbols fts
127 WHERE fp.fund_value = l_fund_value
128 and fts.treasury_symbol_id = fp.treasury_symbol_id
129 AND fts.set_of_books_id = x_ledger_id ;
130
131 EXCEPTION
132 when no_data_found then
133 x_status := 'E';
134 x_message := 'FV_RA_NO_FUND';
135 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
136 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION,l_module, x_message);
137 END IF;
138 return;
142 ----
139 END;
140
141
143 if (x_called_from = 'PO') then
144 l_app_id := 201;
145 l_dff := 'PO_DISTRIBUTIONS';
146 else
147 l_app_id := 200;
148 l_dff := 'AP_INVOICE_DISTRIBUTIONS';
149 End if;
150
151 IF l_fund_category IN ('R', 'S', 'T') THEN /* 1 */
152
153
154 BEGIN
155 SELECT application_column_name
156 INTO l_agreement_num_col
157 FROM fnd_descr_flex_col_usage_vl
158 WHERE application_id = l_app_id
159 AND form_left_prompt = 'Agreement Number'
160 AND descriptive_flexfield_name = l_dff;
161 EXCEPTION
162 when no_data_found then
163 x_status := 'E';
164 x_message := 'FV_RA_NO_AGREEMENT_DFF';
165
166 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
167 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION,l_module, x_message);
168 END IF;
169 END;
170
171 BEGIN
172 SELECT application_column_name
173 INTO l_start_date_col
174 FROM fnd_descr_flex_col_usage_vl
175 WHERE application_id = l_app_id
176 AND form_left_prompt = 'Start Date'
177 AND descriptive_flexfield_name = l_dff;
178 EXCEPTION
179 when no_data_found then
180 x_status := 'E';
181 x_message := 'FV_RA_NO_AGREEMENT_DFF';
182 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
183 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION,l_module, x_message);
184 END IF;
185 END;
186
187 BEGIN
188
189 SELECT application_column_name
190 INTO l_end_date_col
191 FROM fnd_descr_flex_col_usage_vl
192 WHERE application_id = l_app_id
193 AND form_left_prompt = 'End Date'
194 AND descriptive_flexfield_name = l_dff;
195 EXCEPTION
196 when no_data_found then
197 x_status := 'E';
198 x_message := 'FV_RA_NO_AGREEMENT_DFF';
199 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
200 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION,l_module, x_message);
201 END IF;
202 END;
203
204
205 --- Now get the values for agreement number , start date and end_date passed through the attributes
206 -- value passed and attributes defined in DFF
207
208 -- aggrement number
209 select decode( substr(l_agreement_num_col,10,2),
210 1, x_attribute1,
211 2, x_attribute2,
212 3, x_attribute3,
213 4, x_attribute4,
214 5, x_attribute5,
215 6, x_attribute6,
216 7, x_attribute7,
217 8, x_attribute8,
218 9, x_attribute9,
219 10, x_attribute10,
220 11, x_attribute11,
221 12, x_attribute12,
222 13, x_attribute13,
223 14, x_attribute14,
224 15, x_attribute15 )
225 into l_agreement_number from dual;
226
227
228 -- start_date
229 select decode( substr(l_start_date_col,10,2),
230 1, x_attribute1,
231 2, x_attribute2,
232 3, x_attribute3,
233 4, x_attribute4,
234 5, x_attribute5,
235 6, x_attribute6,
236 7, x_attribute7,
237 8, x_attribute8,
238 9, x_attribute9,
239 10, x_attribute10,
240 11, x_attribute11,
241 12, x_attribute12,
242 13, x_attribute13,
243 14, x_attribute14,
244 15, x_attribute15 )
245 into l_start_dt from dual;
246
247
248 -- endt_date
249 select decode( substr(l_end_date_col,10,2),
250 1, x_attribute1,
251 2, x_attribute2,
252 3, x_attribute3,
253 4, x_attribute4,
254 5, x_attribute5,
255 6, x_attribute6,
256 7, x_attribute7,
257 8, x_attribute8,
258 9, x_attribute9,
259 10, x_attribute10,
260 11, x_attribute11,
261 12, x_attribute12,
262 13, x_attribute13,
263 14, x_attribute14,
264 15, x_attribute15 )
265 into l_end_dt from dual;
266
267 l_end_date := trunc(to_date(l_end_dt,'YYYY/MM/DD hh24:mi:ss'));
268
269 IF l_agreement_number is null THEN
270 x_message := 'FV_RA_NO_AGREEMENT';
271 x_status :='E';
272
273 ELSIF l_start_date is null THEN
274 x_status :='E';
275 x_message := 'FV_RA_NO_START_DATE';
276 ELSIF l_end_date is null THEN
280
277 x_message := 'FV_RA_NO_END_DATE';
278 x_status :='E';
279 ELSE
281 BEGIN
282 SELECT trunc(start_date_commitment), trunc(end_date_commitment)
283 INTO l_start_commit_date, l_end_commit_date
284 FROM ra_customer_trx
285 WHERE trx_number = l_agreement_number
286 and set_of_books_id = x_ledger_id;
287
288
289 IF (l_start_commit_date IS NULL) THEN
290 x_message := 'FV_RA_NO_PERFORM_DATES';
291 x_status :='E';
292 ELSIF (l_end_commit_date IS NULL) THEN
293 x_message := 'FV_RA_NO_PERFORM_DATES';
294 x_status :='E';
295 End if;
296
297 EXCEPTION
298 when no_data_found then
299 X_MESSAGE := 'FV_RA_AGRMT_NOTFOUND';
300 x_status :='E';
301 END;
302 END IF;
303
304 IF l_start_date < l_start_commit_date THEN
305 x_message := 'FV_RA_SD_LESS_AGREE';
306 x_status :='E';
307 ELSIF l_start_date > l_end_commit_date THEN
308 X_MESSAGE := 'FV_RA_SD_MORE_AGREE';
309 x_status :='E';
310
311 ELSIF l_end_date < l_start_commit_date THEN
312 x_message := 'FV_RA_ED_LESS_AGREE';
313 x_status :='E';
314
315 ELSIF l_end_date> l_end_commit_date THEN
316 x_message := 'FV_RA_ED_MORE_AGREE';
317 x_status :='E';
318 END IF;
319
320 return;
321 END IF; /* 1*/
322
323 ---- checking PYA Validation warning message
324
325 If l_warning = 'Y' then
326 IF l_expiration_date < trunc(sysdate) THEN
327 x_message := 'FV_PY_WARNING';
328 x_status := 'W';
329 END IF;
330 End if;
331
332 END CHECK_AGREEMENT_DATES;
333
334 --------------------------------------------------------------------------------------------
335
336 function get_balance_segment(l_ledger_id number) return varchar2 is
337
338 l_module varchar2(150) := 'fv.pls.fv_po_validate.get_balance_segment';
339 l_ledger_name varchar2(150);
340 l_bal_seg_name varchar2(30);
341 l_coa_id number(15);
342 l_boolean boolean;
343
344
345 begin
346 --- retrieve coa id
347
348 select chart_of_accounts_id into l_coa_id
349 from gl_ledgers_public_v
350 where ledger_id = l_ledger_id;
351
352
353 l_boolean := FND_FLEX_APIS.GET_SEGMENT_COLUMN(101,'GL#',l_coa_id, 'GL_BALANCING',l_bal_seg_name);
354 IF(l_boolean) THEN
355 null;
356 ELSE
357 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
358 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module,'Did not find Balance Segment' );
359 END IF;
360 END IF;
361
362 return upper(l_bal_seg_name);
363
364 EXCEPTION
365 when no_data_found then
366 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
367 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION,l_module,'No Data found in get_balance_segment' );
368 END IF;
369 return 'ERROR';
370 when others then
371 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
372 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION,l_module,'Error in get_balance_segment' );
373 END IF;
374 return 'ERROR';
375
376 END get_balance_segment;
377
378 ------------------------------------------------------------------------------
379
380 End FV_PO_VALIDATE_GRP;
381