[Home] [Help]
PACKAGE BODY: APPS.PA_CLIENT_EXT_FV_BUDGET_INT
Source
4 -- PROCEDURES
1 PACKAGE BODY PA_CLIENT_EXT_FV_BUDGET_INT AS
2 /* $Header: PAXFBIEB.pls 120.3 2006/12/27 11:34:04 anuagraw noship $ */
3 -- -------------------------------------------------------------------------------------
5 -- -------------------------------------------------------------------------------------
6
7 --
8 --Name: INSERT_BUDGET_LINES
9 --Type: Procedure
10 --Description: This procedure is used to insert Budget Lines into interface tables.
11 -- Also returns status and interface status.
12 --
13 --
14 --Called Subprograms: none.
15 --
16 --Notes:
17 -- This extension or function will be called from the PA Budget Workflow (PABUDWF) -->
18 -- Budget Process (PRO_BASELINE_BUDGET) --> Baseline approved budget Node (FUN_SAVE_BASELINE_ACTION)
19 -- ,PA_BUDGET_WF.BASELINE_BUDGET.
20 --
21 --
22 --
23 --HISTORY:
27 -- p_project_id - Unique identifier of the project in Oracle Projects.
24 -- 31-AUG-06 anuagraw - Created
25 --
26 -- IN Parameters
28 -- p_pre_baselined_version_id - Unique identifier of the budget version previous to
29 -- current baseline budget version.
30 -- p_baselined_budget_version_id - Unique identifier of the current baselined budget version.
31 --
32 -- OUT Parameters
33 -- x_rejection_code - Identifier of the source of the error and the error message
34 -- causing rejecion.
35 -- x_interface_status - Identifier of the success status of the budget integration
36 -- to open interface tables.
37 --
38
39
40 PROCEDURE INSERT_BUDGET_LINES
41 ( p_project_id IN NUMBER
42 ,p_pre_baselined_version_id IN NUMBER
43 ,p_baselined_budget_version_id IN NUMBER
44 ,x_rejection_code OUT NOCOPY VARCHAR2
45 ,x_interface_status OUT NOCOPY VARCHAR2
46 ) IS
47
48
49 l_set_of_books_id PA_PLSQL_DATATYPES.IdTabTyp;
50 l_source PA_PLSQL_DATATYPES.Char25TabTyp;
51 l_group_id PA_PLSQL_DATATYPES.IdTabTyp;
52 l_record_number PA_PLSQL_DATATYPES.NumTabTyp;
53 l_error_code PA_PLSQL_DATATYPES.Char10TabTyp;
54 l_error_reason PA_PLSQL_DATATYPES.Char1000TabTyp;
55 l_budget_level_id PA_PLSQL_DATATYPES.IdTabTyp;
56 l_budgeting_segments PA_PLSQL_DATATYPES.Char1000TabTyp;
57 l_transaction_TYPE PA_PLSQL_DATATYPES.Char25TabTyp;
58 l_sub_type PA_PLSQL_DATATYPES.Char30TabTyp;
59 l_fund_value PA_PLSQL_DATATYPES.Char25TabTyp;
60 l_period_name PA_PLSQL_DATATYPES.Char15TabTyp;
61 l_segment1_30 PA_PLSQL_DATATYPES.Char150TabTyp;
62 l_increase_decrease_flag PA_PLSQL_DATATYPES.Char1TabTyp;
63 l_amount PA_PLSQL_DATATYPES.NewAmtTabTyp;
64 l_doc_number PA_PLSQL_DATATYPES.Char20TabTyp;
65 l_attribute1 PA_PLSQL_DATATYPES.Char150TabTyp;
66 l_attribute2 PA_PLSQL_DATATYPES.Char150TabTyp;
67 l_attribute3 PA_PLSQL_DATATYPES.Char150TabTyp;
68 l_attribute4 PA_PLSQL_DATATYPES.Char150TabTyp;
69 l_attribute5 PA_PLSQL_DATATYPES.Char150TabTyp;
70 l_attribute6 PA_PLSQL_DATATYPES.Char150TabTyp;
71 l_attribute7 PA_PLSQL_DATATYPES.Char150TabTyp;
72 l_attribute8 PA_PLSQL_DATATYPES.Char150TabTyp;
73 l_attribute9 PA_PLSQL_DATATYPES.Char150TabTyp;
74 l_attribute10 PA_PLSQL_DATATYPES.Char150TabTyp;
75 l_attribute11 PA_PLSQL_DATATYPES.Char150TabTyp;
76 l_attribute12 PA_PLSQL_DATATYPES.Char150TabTyp;
77 l_attribute13 PA_PLSQL_DATATYPES.Char150TabTyp;
78 l_attribute14 PA_PLSQL_DATATYPES.Char150TabTyp;
79 l_attribute15 PA_PLSQL_DATATYPES.Char150TabTyp;
80 l_attribute_category PA_PLSQL_DATATYPES.Char30TabTyp;
81 l_processed_flag PA_PLSQL_DATATYPES.Char1TabTyp;
82 l_status PA_PLSQL_DATATYPES.Char25TabTyp;
83 l_date_created PA_PLSQL_DATATYPES.DateTabTyp;
84 l_created_by PA_PLSQL_DATATYPES.NumTabTyp;
85 l_corrected_flag PA_PLSQL_DATATYPES.Char1TabTyp;
86 l_last_update_date PA_PLSQL_DATATYPES.DateTabTyp;
87 l_last_updated_by PA_PLSQL_DATATYPES.NumTabTyp;
88 l_gl_date PA_PLSQL_DATATYPES.DateTabTyp;
89 l_public_law_code PA_PLSQL_DATATYPES.Char25TabTyp;
90 l_advance_type PA_PLSQL_DATATYPES.Char25TabTyp;
91 l_dept_id PA_PLSQL_DATATYPES.Num15TabTyp;
92 l_main_account PA_PLSQL_DATATYPES.Num15TabTyp;
93 l_transfer_description PA_PLSQL_DATATYPES.Char30TabTyp;
94 l_budget_user_id PA_PLSQL_DATATYPES.NewAmtTabTyp;
95
96 l_VER_GROUP_ID VARCHAR2(150);
97 l_user_id NUMBER(15);
98 l_pkg_name VARCHAR2(30) := 'PA_CLIENT_EXT_FV_BUDGET_INT';
99 l_limit NUMBER := 200;
100 l_any_rec_found VARCHAR2(1) := 'N';
101
102 -- 1.Select additional columns here to map them to
103 -- the FV_BE_INTERFACE table columns.
104 -- 2.Put additional filters here ,if required.
105
106 CURSOR C_PA_BUDGET_LINES(grp_id IN VARCHAR2,usr_id IN VARCHAR2) is
107 SELECT
108 pia.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
109 ,'PROJECTS' SOURCE
110 ,grp_id GROUP_ID -- can be mapped to pbl.attribute1 also
111 ,ABS(MOD(dbms_random.random,999)) RECORD_NUMBER -- can be mappedto pbl.attribute2 also
112 ,null ERROR_CODE
113 ,null ERROR_REASON
114 ,pbl.attribute3 BUDGET_LEVEL_ID
115 ,pbl.attribute4 BUDGETING_SEGMENTS
116 ,pbl.attribute5 TRANSACTION_TYPE
117 ,pbl.attribute6 SUB_TYPE
118 ,pbl.attribute7 FUND_VALUE
119 ,pbl.attribute8 PERIOD_NAME
120 ,pbl.attribute9 SEGMENT1_30
121 ,pbl.attribute10 INCREASE_DECREASE_FLAG
122 ,pbl.attribute11 AMOUNT
123 ,pbl.attribute12 DOC_NUMBER
124 ,null ATTRIBUTE1
125 ,null ATTRIBUTE2
126 ,null ATTRIBUTE3
127 ,null ATTRIBUTE4
128 ,null ATTRIBUTE5
129 ,null ATTRIBUTE6
130 ,null ATTRIBUTE7
131 ,null ATTRIBUTE8
132 ,null ATTRIBUTE9
136 ,null ATTRIBUTE13
133 ,null ATTRIBUTE10
134 ,null ATTRIBUTE11
135 ,null ATTRIBUTE12
137 ,null ATTRIBUTE14
138 ,null ATTRIBUTE15
139 ,null ATTRIBUTE_CATEGORY
140 ,'N' PROCESSED_FLAG
141 ,'NEW' STATUS
142 ,pbl.CREATION_DATE DATE_CREATED
143 ,pbl.CREATED_BY CREATED_BY
144 ,'N' CORRECTED_FLAG
145 ,null LAST_UPDATE_DATE
146 ,null LAST_UPDATED_BY
147 ,pbl.START_DATE GL_DATE
148 ,null PUBLIC_LAW_CODE
149 ,null ADVANCE_TYPE
150 ,null DEPT_ID
151 ,null MAIN_ACCOUNT
152 ,null TRANSFER_DESCRIPTION
153 ,usr_id BUDGET_USER_ID
154 from PA_PROJECTS_ALL ppa
155 ,PA_PROJECT_TYPES_ALL ppt
156 ,PA_IMPLEMENTATIONS_ALL pia
157 ,PA_BUDGET_VERSIONS pbv
158 ,PA_BUDGET_LINES pbl
159 ,PA_RESOURCE_ASSIGNMENTS pra
160 where ppa.project_id = p_project_id
161 and ppa.project_TYPE = ppt.project_type
162 and ppa.org_id = ppt.org_id
163 and ppa.project_id = pbv.project_id
164 and ppa.org_id = pia.org_id
165 and pbv.budget_version_id = pbl.budget_version_id
166 and pbv.budget_version_id = p_baselined_budget_version_id
167 and pbl.resource_assignment_id = pra.resource_assignment_id ;
168
169
170 -- Define your local variables here
171
172 BEGIN
173
174 x_interface_status := null;
175 x_rejection_code := null;
176
177 -- Generating Group_id value from Env.
178 SELECT ABS(MOD(dbms_random.random,999)),1003399 -- Please put the value of user_id as per your need.
179 into l_VER_GROUP_ID,l_user_id
180 FROM dual;
181
182 -- Insert one record for each budget version baseline into
183 -- the table FV_BE_INTERFACE_CONTOL
184
185 INSERT INTO FV_BE_INTERFACE_CONTROL
186 (
187 SOURCE
188 ,GROUP_ID
189 ,STATUS
190 ,DATE_PROCESSED
191 ,TIME_PROCESSED
192 )
193 VALUES
194 (
195 'PROJECTS'
196 ,l_VER_GROUP_ID
197 ,'NEW'
198 ,to_char(sysdate,'DD-MON-YY')
199 ,to_char(sysdate,'HH24:MI:SS')
200 );
201
202 -- Do not commit in this package.Calling module will take care of commit;
203
204 OPEN C_PA_BUDGET_LINES(l_VER_GROUP_ID,l_user_id);
205 LOOP
206 FETCH C_PA_BUDGET_LINES BULK COLLECT INTO
207 l_set_of_books_id
208 ,l_source
209 ,l_group_id
210 ,l_record_number
211 ,l_error_code
212 ,l_error_reason
213 ,l_budget_level_id
214 ,l_budgeting_segments
215 ,l_transaction_TYPE
216 ,l_sub_type
217 ,l_fund_value
218 ,l_period_name
219 ,l_segment1_30
220 ,l_increase_decrease_flag
221 ,l_amount
222 ,l_doc_number
223 ,l_attribute1
224 ,l_attribute2
225 ,l_attribute3
226 ,l_attribute4
227 ,l_attribute5
228 ,l_attribute6
229 ,l_attribute7
230 ,l_attribute8
231 ,l_attribute9
232 ,l_attribute10
233 ,l_attribute11
234 ,l_attribute12
235 ,l_attribute13
236 ,l_attribute14
237 ,l_attribute15
238 ,l_attribute_category
239 ,l_processed_flag
240 ,l_status
241 ,l_date_created
242 ,l_created_by
243 ,l_corrected_flag
244 ,l_last_update_date
245 ,l_last_updated_by
246 ,l_gl_date
247 ,l_public_law_code
248 ,l_advance_type
249 ,l_dept_id
250 ,l_main_account
251 ,l_transfer_description
252 ,l_budget_user_id
253 LIMIT l_limit;
254
255 -- Enter Your Business Rules Here to manipulate the attribute vales to be inserted into
256 -- FV_BE_INTERFACE table or Use The the provided default.
257
258 -- Insert one record for each budget line and one for each reverse budget line into
259 -- the table FV_BE_INTERFACE table.
260
261 -- Try to insert into FV_BE_INTERFACE only if any record is found for this loop
262
263 IF l_set_of_books_id.COUNT > 0 then
264
265 --
266 l_any_rec_found := 'Y';
267
268 FORALL i IN 1..l_set_of_books_id.COUNT
269
270 INSERT INTO FV_BE_INTERFACE
271 (
272 SET_OF_BOOKS_ID
273 ,SOURCE
274 ,GROUP_ID
275 ,RECORD_NUMBER
276 ,ERROR_CODE
277 ,ERROR_REASON
278 ,BUDGET_LEVEL_ID
279 ,BUDGETING_SEGMENTS
280 ,TRANSACTION_TYPE
281 ,SUB_TYPE
282 ,FUND_VALUE
283 ,PERIOD_NAME
284 ,SEGMENT1
285 ,SEGMENT2
286 ,SEGMENT3
287 ,SEGMENT4
288 ,SEGMENT5
289 ,SEGMENT6
290 ,SEGMENT7
291 ,SEGMENT8
292 ,SEGMENT9
293 ,SEGMENT10
294 ,SEGMENT11
295 ,SEGMENT12
299 ,SEGMENT16
296 ,SEGMENT13
297 ,SEGMENT14
298 ,SEGMENT15
300 ,SEGMENT17
301 ,SEGMENT18
302 ,SEGMENT19
303 ,SEGMENT20
304 ,SEGMENT21
305 ,SEGMENT22
306 ,SEGMENT23
307 ,SEGMENT24
308 ,SEGMENT25
309 ,SEGMENT26
310 ,SEGMENT27
311 ,SEGMENT28
312 ,SEGMENT29
313 ,SEGMENT30
314 ,INCREASE_DECREASE_FLAG
315 ,AMOUNT
316 ,DOC_NUMBER
317 ,ATTRIBUTE1
318 ,ATTRIBUTE2
319 ,ATTRIBUTE3
320 ,ATTRIBUTE4
321 ,ATTRIBUTE5
322 ,ATTRIBUTE6
323 ,ATTRIBUTE7
324 ,ATTRIBUTE8
325 ,ATTRIBUTE9
326 ,ATTRIBUTE10
327 ,ATTRIBUTE11
328 ,ATTRIBUTE12
329 ,ATTRIBUTE13
330 ,ATTRIBUTE14
331 ,ATTRIBUTE15
332 ,ATTRIBUTE_CATEGORY
333 ,PROCESSED_FLAG
334 ,STATUS
335 ,DATE_CREATED
336 ,CREATED_BY
337 ,CORRECTED_FLAG
338 ,LAST_UPDATE_DATE
339 ,LAST_UPDATED_BY
340 ,GL_DATE
341 ,PUBLIC_LAW_CODE
342 ,ADVANCE_TYPE
343 ,DEPT_ID
344 ,MAIN_ACCOUNT
345 ,TRANSFER_DESCRIPTION
346 ,BUDGET_USER_ID
347 )
348 VALUES
349 (
350 l_set_of_books_id(i)
351 ,l_source(i)
352 ,l_group_id(i)
353 ,l_record_number(i)
354 ,l_error_code(i)
355 ,l_error_reason(i)
356 ,l_budget_level_id(i)
357 ,l_budgeting_segments(i)
358 ,l_transaction_type(i)
359 ,l_sub_type(i)
360 ,l_fund_value(i)
361 ,l_period_name(i)
362 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,1 )
363 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,2)
364 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,3)
365 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,4)
366 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,5)
367 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,6)
368 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,7)
369 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,8)
370 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,9)
371 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,10)
372 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,11)
373 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,12)
374 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,13)
375 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,14)
376 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,15)
377 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,16)
378 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,17)
379 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,18)
380 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,19)
381 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,20)
382 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,21)
383 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,22)
384 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,23)
385 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,24)
386 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,25)
387 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,26)
388 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,27)
389 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,28)
390 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,29)
391 ,REGEXP_SUBSTR(l_segment1_30(i),'[^.]+', 1,30)
392 ,l_increase_decrease_flag(i)
393 ,l_amount(i)
394 ,l_doc_number(i)
395 ,l_attribute1(i)
396 ,l_attribute2(i)
397 ,l_attribute3(i)
398 ,l_attribute4(i)
399 ,l_attribute5(i)
400 ,l_attribute6(i)
401 ,l_attribute7(i)
402 ,l_attribute8(i)
403 ,l_attribute9(i)
404 ,l_attribute10(i)
405 ,l_attribute11(i)
406 ,l_attribute12(i)
407 ,l_attribute13(i)
408 ,l_attribute14(i)
409 ,l_attribute15(i)
410 ,l_attribute_category(i)
411 ,l_processed_flag(i)
412 ,l_status(i)
413 ,l_date_created(i)
414 ,l_created_by(i)
415 ,l_corrected_flag(i)
416 ,l_last_update_date(i)
417 ,l_last_updated_by(i)
418 ,l_gl_date(i)
419 ,l_public_law_code(i)
420 ,l_advance_type(i)
421 ,l_dept_id(i)
422 ,l_main_account(i)
423 ,l_transfer_description(i)
424 ,l_budget_user_id(i)
425 );
426
427 END IF;
428 EXIT WHEN C_PA_BUDGET_LINES%NOTFOUND;
429
430 END LOOP;
431
432 if l_any_rec_found = 'Y' then
433 x_interface_status := 'True';
434 else
435 x_interface_status := 'False';
436 x_rejection_code := 'NO_BUDGET_LINE';
437 end if;
438
439 -- Do not commit in this package.
440
441 CLOSE C_PA_BUDGET_LINES;
442
443 -- Please set the interface status to True if it is success as per custom logic , by default it is null
444
445
446 Exception
447 WHEN OTHERS THEN
448
449 -- Since insert into both the tables are not successful set status to 'True' and Interface Status to 'False'
450
451 x_interface_status := 'False';
452 x_rejection_code := l_pkg_name||':'||sqlerrm;
453 END INSERT_BUDGET_LINES;
454
455 end PA_CLIENT_EXT_FV_BUDGET_INT;