[Home] [Help]
PACKAGE BODY: APPS.FV_STATUS_OF_OBLIGATIONS
Source
1 PACKAGE BODY fv_status_of_obligations as
2 -- $Header: FVXPOSRB.pls 120.6 2006/06/19 12:07:11 svaithil ship $
3 --==============================================================
4 -- Error Code and Error Messages
5 v_error_code Number := 0 ;
6 v_error_mesg Varchar2(500) ;
7 g_module_name varchar2(100) := 'fv.plsql.fv_status_of_obligations.';
8 -- Dynamic SQL variables
9 v_select varchar2(2000);
10 v_val_string varchar2(2000);
11 v_po_select varchar2(2000);
12 v_where varchar2(1000) ;
13
14 v_inv_cursor Integer ;
15 v_po_cursor Integer ;
16 v_exec_ret Integer ;
17
18 -- Global variables to store the passes parameters
19 v_segval1 varchar2(20);
20 v_segval2 varchar2(20);
21 v_segval3 varchar2(20);
22
23 v_segval1_low varchar2(20);
24 v_segval1_high varchar2(20);
25
26 v_seg_val1 varchar2(30);
27 v_seg_val2 varchar2(30);
28 v_seg_val3 varchar2(30);
29
30 v_segval2_low varchar2(20);
31 v_segval2_high varchar2(20);
32
33 v_segval3_low varchar2(20);
34 v_segval3_high varchar2(20);
35
36 v_from_period date;
37 v_to_period date;
38 v_set_of_books_id number;
39
40 /*b_from_period date;
41 b_to_period date;
42 b_set_of_books_id number;*/
43
44 -- Variables to store the Main select information
45
46 v_reqnum po_requisition_headers.segment1%type;
47 v_reqdate date;
48 v_reqamt number;
49 v_reqccid number;
50
51 v_oblignum po_headers.segment1%type;
52 v_obligdate date;
53 v_obligamt number;
54 v_obligccid number;
55 v_obligstatus po_lines.closed_code%type;
56 v_inv_po_distribution_id
57 po_distributions.po_distribution_id%type ;
58 v_po_req_distribution_id
59 po_req_distributions.distribution_id%type ;
60 v_po_distribution_id po_distributions.po_distribution_id%type ;
61
62 v_invnum ap_invoices.invoice_num%type;
63 v_invdate date;
64 v_invamt number;
65 v_invccid number;
66 v_reversal_flag Varchar2(1) ;
67
68 ----------------------------------------------------------------------
69 -- MAIN
70 ----------------------------------------------------------------------
71 procedure main (
72 Errbuf OUT NOCOPY varchar2,
73 retcode OUT NOCOPY varchar2,
74 segval1 in varchar2,
75 segval2 in varchar2,
76 segval3 in varchar2,
77 segval1_low in varchar2,
78 segval1_high in varchar2,
79 segval2_low in varchar2,
80 segval2_high in varchar2,
81 segval3_low in varchar2,
82 segval3_high in varchar2,
83 from_period in date,
84 to_period in date,
85 set_of_books_id in number)
86
87 IS
88 l_module_name varchar2(200) := g_module_name || 'main';
89 l_errbuf varchar2(300);
90 Begin
91
92 -- Initialize parameters to global variables
93 v_segval1 := segval1 ;
94 v_segval2 := segval2 ;
95 v_segval3 := segval3 ;
96
97 v_segval1_low := segval1_low ;
98 v_segval1_high := segval1_high ;
99 v_segval2_low := segval2_low ;
100 v_segval2_high := segval2_high ;
101 v_segval3_low := segval3_low ;
102 v_segval3_high := segval3_high ;
103
104 v_from_period := from_period ;
105 v_to_period := to_period ;
106 v_set_of_books_id := set_of_books_id ;
107
108 If v_error_code = 0 Then
109 Initialize ;
110 End If ;
111
112 If v_error_code = 0 Then
113 Build_Where_Clause ;
114 End If ;
115
116 If v_error_code = 0 Then
117 Process_Invoices ;
118 End If ;
119
120 If v_error_code = 0 Then
121 Process_pos ;
122 End If ;
123
124
125 If v_error_code = 0 Then
126 Commit ;
127 Else
128 Rollback ;
129 End If ;
130
131 retcode := to_char(v_error_code) ;
132 errbuf := v_error_mesg ;
133 EXCEPTION
134 WHEN OTHERS THEN
135 l_errbuf := SQLERRM;
136 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
137 End Main ;
138
139
140 ----------------------------------------------------------------------
141 -- INITIALIZE
142 ----------------------------------------------------------------------
143 Procedure Initialize is
144 l_module_name varchar2(200) := g_module_name || 'initialize';
145 Begin
146 -- Delete the Temporary table
147 Delete from FV_STATUS_OBLIG_TEMP ;
148 Exception
149 When Others Then
150 v_error_code := sqlcode ;
151 v_error_mesg := sqlerrm ;
152 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
153 End Initialize ;
154
155 ----------------------------------------------------------------------
156 -- PROCESS_INVOICES
157 ----------------------------------------------------------------------
158 Procedure Process_invoices is
159 l_module_name varchar2(200) := g_module_name || 'process_invoices';
160 l_errbuf varchar2(300);
161 l_req_duplicate number;
162 l_po_duplicate number;
163 Cursor c1_duplicate_PO(p_inv_po_distribution_id number) Is
164 select 2
165 from fv_status_oblig_temp
166 where inv_po_distribution_id = p_inv_po_distribution_id ;
167 Cursor c2_duplicate_req(p_po_req_distribution_id number) Is
168 select 1
169 from fv_status_oblig_temp
170 where po_req_distribution_id = p_po_req_distribution_id;
171 Begin
172
173 Begin
174 v_inv_cursor := DBMS_SQL.OPEN_CURSOR ;
175 Exception
176 When Others Then
177 v_error_code := sqlcode ;
178 v_error_mesg := sqlerrm ;
179 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
180 End ;
181
182 -- Get all the Invoices
183 v_select :=
184 'select api.invoice_num,
185 api.invoice_date,
186 apd.amount,
187 apd.dist_code_combination_id,
188 apd.po_distribution_id ' || v_val_string
189 || ' from ap_invoice_distributions apd,
190 ap_invoices api,
191 gl_code_combinations glcc
192 where api.invoice_id = apd.invoice_id
193 and glcc.code_combination_id = apd.dist_code_combination_id
194 and (api.invoice_date between :b_from_period and :b_to_period)
195 and api.set_of_books_id = :b_set_of_books_id '|| v_where ;
196
197
198 Begin
199
200 dbms_sql.parse(v_inv_cursor, v_select, DBMS_SQL.V7) ;
201
202 dbms_sql.bind_variable(v_inv_cursor,':b_from_period',v_from_period);
203 dbms_sql.bind_variable(v_inv_cursor,':b_to_period',v_to_period);
204 dbms_sql.bind_variable(v_inv_cursor,':b_set_of_books_id',v_set_of_books_id);
205
206 dbms_sql.bind_variable(v_inv_cursor,':b_segval1_low',v_segval1_low);
207 dbms_sql.bind_variable(v_inv_cursor,':b_segval1_high',v_segval1_high);
208
209 If v_segval2 is NOT NULL Then
210 dbms_sql.bind_variable(v_inv_cursor,':b_segval2_low',v_segval2_low);
211 dbms_sql.bind_variable(v_inv_cursor,':b_segval2_high',v_segval2_high);
212 End if;
213
214 If v_segval3 is NOT NULL Then
215 dbms_sql.bind_variable(v_inv_cursor,':b_segval3_low',v_segval3_low);
216 dbms_sql.bind_variable(v_inv_cursor,':b_segval3_high',v_segval3_high);
217 End if;
218
219 Exception
220 When Others Then
221 v_error_code := sqlcode ;
222 v_error_mesg := sqlerrm ;
223 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
224 End ;
225
226
227 dbms_sql.define_column(v_inv_cursor, 1, v_invnum,50);
228 dbms_sql.define_column(v_inv_cursor, 2, v_invdate);
229 dbms_sql.define_column(v_inv_cursor, 3, v_invamt);
230 dbms_sql.define_column(v_inv_cursor, 4, v_invccid);
231 dbms_sql.define_column(v_inv_cursor, 5, v_inv_po_distribution_id);
232 dbms_sql.define_column(v_inv_cursor, 6, v_seg_val1,25);
233
234 if(v_segval2 is not null) then
235 dbms_sql.define_column(v_inv_cursor, 7, v_seg_val2,25);
236 End if;
237
238 if(v_segval3 is not null) then
239 dbms_sql.define_column(v_inv_cursor, 8, v_seg_val3,25);
240 End if;
241
242
243 Begin
244 v_exec_ret := dbms_sql.execute(v_inv_cursor);
245 Exception
246 When Others Then
247 v_error_code := sqlcode ;
248 v_error_mesg := sqlerrm ;
249 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
250 End ;
251
252 Loop
253 if dbms_sql.fetch_rows(v_inv_cursor) = 0 then
254 exit;
255 else
256 dbms_sql.column_value(v_inv_cursor, 1, v_invnum);
257 dbms_sql.column_value(v_inv_cursor, 2, v_invdate);
258 dbms_sql.column_value(v_inv_cursor, 3, v_invamt);
259 dbms_sql.column_value(v_inv_cursor, 4, v_invccid);
260 dbms_sql.column_value(v_inv_cursor, 5,
261 v_inv_po_distribution_id);
262 dbms_sql.column_value(v_inv_cursor, 6, v_seg_val1);
263
264 if(v_segval2 is not null) then
265 dbms_sql.column_value(v_inv_cursor, 7, v_seg_val2);
266 End if;
267
268 if(v_segval3 is not null) then
269 dbms_sql.column_value(v_inv_cursor, 8, v_seg_val3);
270 End if;
271
272 end if;
273
274 -- Look for Purchase Order for the Invoice
275 If (v_inv_po_distribution_id IS NOT NULL ) then
276 -- PO Exists, Get the PO Information
277 Begin
278 select poh.segment1,
279 pod.gl_encumbered_date,
280 (pod.quantity_ordered -
281 nvl(pod.quantity_cancelled,0))
282 * Nvl(pol.unit_price, 0),
283 pol.closed_code,
284 pod.code_combination_id ,
285 pod.req_distribution_id,
286 pod.po_distribution_id
287
288 Into v_oblignum ,
289 v_obligdate ,
290 v_obligamt ,
291 v_obligstatus ,
292 v_obligccid ,
293 v_po_req_distribution_id,
294 v_po_distribution_id
295
296 from po_headers poh,
297 po_lines pol,
298 po_line_locations poll,
299 po_distributions pod,
300 gl_code_combinations glcc
301
302 where poh.approved_flag = 'Y'
303 and pod.po_distribution_id = v_inv_po_distribution_id
304 and poh.po_header_id = pol.po_header_id
305 and pol.po_line_id = poll.po_line_id
306 and poll.line_location_id = pod.line_location_id
307 and pod.code_combination_id = glcc.code_combination_id
308 and pod.set_of_books_id = v_set_of_books_id
309 and not exists
310 (select 1
311 from po_headers A
312 where A.segment1 = poh.segment1
313 and poh.type_lookup_code = 'PLANNED'
314 and pod.source_distribution_id is NULL);
315 /* and not exists
316 (select 2
317 from fv_status_oblig_temp
318 where inv_po_distribution_id = pod.po_distribution_id) ;*/
319
320 Open c1_duplicate_PO(v_inv_po_distribution_id);
321 Fetch c1_duplicate_PO Into l_po_duplicate;
322 If c1_duplicate_PO%FOUND Then
323 v_obligamt := 0 ;
324 v_reqamt := 0 ;
325 End if;
326 Close c1_duplicate_PO;
327
328 Exception
329 When No_Data_Found Then
330 v_obligamt := 0 ;
331 v_reqamt := 0 ;
332 v_oblignum := Null ;
333 v_obligdate := Null ;
334 v_obligccid := Null ;
335 v_obligstatus := Null ;
336 v_po_distribution_id := Null ;
337
338 When TOO_MANY_ROWS Then
339 v_error_code := sqlcode ;
340 v_error_mesg :=
341 'More than one PO Distribution rows found for
342 the Invoice Distribution id - ' ||
343 to_char(v_inv_po_distribution_id) ;
344 When Others Then
345 v_error_code := sqlcode ;
346 v_error_mesg := sqlerrm ;
347 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
348 End ;
349
350
351 If v_po_req_distribution_id is NOT NULL Then
352
353 -- Requisition Exists, Look for Requisitions
354 Begin
355 Select porh.segment1,
356 pord.gl_encumbered_date,
357 (porl.quantity - nvl(porl.quantity_cancelled,0))
358 * porl.unit_price,
359 pord.code_combination_id
360
361 Into v_reqnum ,
362 v_reqdate ,
363 v_reqamt,
364 v_reqccid
365
366 from po_requisition_headers porh,
367 po_requisition_lines porl,
368 po_req_distributions pord,
369 gl_code_combinations glcc
370
371 where pord.distribution_id = v_po_req_distribution_id
372 and porh.requisition_header_id =
373 porl.requisition_header_id
374 and porl.requisition_line_id =
375 pord.requisition_line_id
376 and pord.code_combination_id =
377 glcc.code_combination_id;
378 /* and not exists
379 (select 1
380 from fv_status_oblig_temp
381 where po_req_distribution_id =
382 v_po_req_distribution_id ) ;*/
383
384 Open c2_duplicate_req(v_po_req_distribution_id);
385 Fetch c2_duplicate_req Into l_req_duplicate;
386 If c2_duplicate_req%FOUND Then
387 v_reqamt := 0 ;
388 End if;
389 Close c2_duplicate_req;
390
391 Exception
392
393 When No_Data_Found Then
394 v_reqamt := 0 ;
395 v_reqnum := Null ;
396 v_reqdate := Null ;
397 v_reqccid := Null ;
398 When TOO_MANY_ROWS Then
399 v_error_code := sqlcode ;
400 v_error_mesg :=
401 'More than one REQ Distribution rows
402 found for the PO Distribution id - ' ||
406 v_error_mesg := sqlerrm ;
403 to_char(v_po_req_distribution_id) ;
404 When Others Then
405 v_error_code := sqlcode ;
407 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
408 End ;
409
410 Else
411 -- Requisition does not exist, Reset the variables.
412 v_reqnum := Null ;
413 v_reqdate := Null ;
414 v_reqamt := Null ;
415 v_reqccid := Null ;
416
417 End If ;
418
419 Else
420
421 -- PO Does not exist reset both req and po information
422 v_oblignum := Null ;
423 v_obligdate := Null ;
424 v_obligamt := Null ;
425 v_obligccid := Null ;
426 v_obligstatus := Null ;
427 v_po_distribution_id := Null ;
428
429 v_reqnum := Null ;
430 v_reqdate := Null ;
431 v_reqamt := Null ;
432 v_reqccid := Null ;
433 v_po_req_distribution_id := Null ;
434
435 End If ;
436
437 -- For reversal invoices, obligation and requisition amounts needs to
438 -- be reported only once
439 If v_reversal_flag = 'Y' and v_invamt < 0 then
440 v_obligamt := 0 ;
441 v_reqamt := 0 ;
442 End If ;
443
444 Insert_Processing ;
445
446 end loop;
447
448 Begin
449 dbms_sql.close_cursor(v_inv_cursor);
450 Exception
451 When Others then
452 v_error_code := sqlcode ;
453 v_error_mesg := sqlerrm ;
454 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
455 End ;
456 EXCEPTION
457 WHEN OTHERS THEN
458 l_errbuf := SQLERRM;
459 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
460 raise;
461 End process_invoices ;
462
463 ----------------------------------------------------------------------
464 -- BUILD_WHERE_CLAUSE
465 ----------------------------------------------------------------------
466 Procedure build_where_clause is
467 l_module_name varchar2(200) := g_module_name || 'build_where_clause';
468 l_errbuf varchar2(300);
469 Begin
470
471 -- Build the optional where clause
472 v_where := ' and (' || v_segval1 ||' between :b_segval1_low and
473 :b_segval1_high ' || ')' ;
474
475 v_val_string := ',' || v_segval1;
476
477 If v_segval2 is NOT NULL Then
478 v_where := v_where || ' and (' || v_segval2 ||
479 ' between :b_segval2_low and :b_segval2_high ' || ')' ;
480
481 v_val_string := v_val_string || ',' || v_segval2;
482 End If ;
483
484 If v_segval3 is NOT NULL Then
485 v_where := v_where || ' and (' || v_segval3 ||
486 ' between :b_segval3_low and :b_segval3_high ' || ')' ;
487
488 v_val_string := v_val_string || ',' || v_segval3;
489 End If ;
490 EXCEPTION
491 WHEN OTHERS THEN
492 l_errbuf := SQLERRM;
493 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
494 raise;
495 End build_where_clause ;
496
497
498
499 ----------------------------------------------------------------------
500 -- PROCESS_POS
501 ----------------------------------------------------------------------
502 Procedure Process_pos is
503
504 l_po_exists Varchar2(1) := 'N' ;
505 l_req_distribution_id Number ;
506 l_module_name varchar2(200) := g_module_name || 'process_pos';
507 l_errbuf varchar2(300);
508 Begin
509
510 Begin
511 v_po_cursor := DBMS_SQL.OPEN_CURSOR ;
512 Exception
513 When Others then
514 v_error_code := sqlcode ;
515 v_error_mesg := sqlerrm ;
516 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
517 End ;
518
519 v_invnum := NULL ;
520 v_invdate := NULL ;
521 v_invamt := NULL ;
522 v_invccid := NULL ;
523 v_inv_po_distribution_id := NULL ;
524
525 -- Get all the Purchase Orders
526 v_po_select :=
527 'select poh.segment1 ,
528 pod.gl_encumbered_date,
529 (pod.quantity_ordered - nvl(pod.quantity_cancelled,0))
530 * nvl(pol.unit_price, 0) obligamt ,
531 pol.closed_code,
532 pod.code_combination_id,
533 pod.req_distribution_id,
534 pod.po_distribution_id' || v_val_string ||
535 ' from po_headers poh,
536 po_lines pol,
537 po_line_locations poll,
538 po_distributions pod,
539 gl_code_combinations glcc
540 where
541 NOT EXISTS( Select 1
542 from fv_status_oblig_temp fvs
543 where fvs.po_distribution_id = pod.po_distribution_id)
544 and poh.approved_flag = '||''''||'Y'||''''||
545 ' and poh.po_header_id = pol.po_header_id
546 and pol.po_line_id = poll.po_line_id
547 and poll.line_location_id = pod.line_location_id
548 and not exists
549 (select 1
550 from po_headers A
551 where A.segment1 = poh.segment1
555 and glcc.code_combination_id = pod.code_combination_id
552 and poh.type_lookup_code = '||''''||'PLANNED'||''''||
553 ' and pod.source_distribution_id is NULL)
554 and pod.set_of_books_id = :b_set_of_books_id
556 and pod.gl_encumbered_date between :b_from_period and :b_to_period '|| v_where ;
557
558
559 /*----------------- TESTING ------------------------
560 Insert into surya_temp values('PO', v_po_select) ;
561 ----------------- TESTING ------------------------ */
562
563 Begin
564 dbms_sql.parse(v_po_cursor, v_po_select, DBMS_SQL.V7) ;
565 dbms_sql.bind_variable(v_po_cursor,':b_from_period',v_from_period);
566 dbms_sql.bind_variable(v_po_cursor,':b_to_period',v_to_period);
567 dbms_sql.bind_variable(v_po_cursor,':b_set_of_books_id',v_set_of_books_id);
568
569 dbms_sql.bind_variable(v_po_cursor,':b_segval1_low',v_segval1_low);
570 dbms_sql.bind_variable(v_po_cursor,':b_segval1_high',v_segval1_high);
571
572 If v_segval2 is NOT NULL Then
573 dbms_sql.bind_variable(v_po_cursor,':b_segval2_low',v_segval2_low);
574 dbms_sql.bind_variable(v_po_cursor,':b_segval2_high',v_segval2_high);
575 End if;
576
577 If v_segval3 is NOT NULL Then
578 dbms_sql.bind_variable(v_po_cursor,':b_segval3_low',v_segval3_low);
579 dbms_sql.bind_variable(v_po_cursor,':b_segval3_high',v_segval3_high);
580 End if;
581
582 Exception
583 When Others then
584 v_error_code := sqlcode ;
585 v_error_mesg := sqlerrm ;
586 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
587 End ;
588
589
590 dbms_sql.define_column(v_po_cursor, 1, v_oblignum, 50);
591 dbms_sql.define_column(v_po_cursor, 2, v_obligdate);
592 dbms_sql.define_column(v_po_cursor, 3, v_obligamt);
593 dbms_sql.define_column(v_po_cursor, 4, v_obligstatus, 25);
594 dbms_sql.define_column(v_po_cursor, 5, v_obligccid);
595 dbms_sql.define_column(v_po_cursor, 6, v_po_req_distribution_id);
596 dbms_sql.define_column(v_po_cursor, 7, v_po_distribution_id);
597 dbms_sql.define_column(v_po_cursor, 8, v_seg_val1,25);
598 if(v_segval2 is not null) then
599 dbms_sql.define_column(v_po_cursor, 9, v_seg_val2,25);
600 End if;
601 if(v_segval3 is not null) then
602 dbms_sql.define_column(v_po_cursor, 10, v_seg_val3,25);
603 End if;
604
605 Begin
606 v_exec_ret := dbms_sql.execute(v_po_cursor);
607 Exception
608 When Others then
609 v_error_code := sqlcode ;
610 v_error_mesg := sqlerrm ;
611 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
612 End ;
613
614
615 loop
616 if dbms_sql.fetch_rows(v_po_cursor) = 0 then
617 exit;
618 else
619
620 dbms_sql.column_value(v_po_cursor, 1, v_oblignum);
621 dbms_sql.column_value(v_po_cursor, 2, v_obligdate);
622 dbms_sql.column_value(v_po_cursor, 3, v_obligamt);
623 dbms_sql.column_value(v_po_cursor, 4, v_obligstatus);
624 dbms_sql.column_value(v_po_cursor, 5, v_obligccid);
625 dbms_sql.column_value(v_po_cursor, 6,
626 v_po_req_distribution_id);
627 dbms_sql.column_value(v_po_cursor, 7, v_po_distribution_id);
628 dbms_sql.column_value(v_po_cursor, 8, v_seg_val1);
629 if(v_segval2 is not null) then
630 dbms_sql.column_value(v_po_cursor, 9, v_seg_val2);
631 End if;
632 if(v_segval3 is not null) then
633 dbms_sql.column_value(v_po_cursor, 10, v_seg_val3);
634 End if;
635
636 end if;
637
638
639 If v_po_req_distribution_id is NOT NULL Then
640
641 Begin
642 -- Requisition Exists, Look for Requisitions
643 Select porh.segment1,
644 pord.gl_encumbered_date,
645 (porl.quantity - nvl(porl.quantity_cancelled,0))
646 * porl.unit_price ,
647 pord.code_combination_id
648
649 Into v_reqnum ,
650 v_reqdate ,
651 v_reqamt,
652 v_reqccid
653
654 from po_requisition_headers porh,
655 po_requisition_lines porl,
656 po_req_distributions pord,
657 gl_code_combinations glcc
658
659 where pord.distribution_id = v_po_req_distribution_id
660 and porh.requisition_header_id = porl.requisition_header_id
661 and porl.requisition_line_id = pord.requisition_line_id
662 and pord.code_combination_id = glcc.code_combination_id ;
663
664 Exception
665
666 When No_Data_Found Then
667 Null ;
668
669 When TOO_MANY_ROWS Then
670 v_error_code := sqlcode ;
671 v_error_mesg :=
672 'More than one REQ Distribution rows found for the
673 PO Distribution id - ' ||
674 to_char(v_po_req_distribution_id) ;
675 When Others Then
676 v_error_code := sqlcode ;
677 v_error_mesg := sqlerrm ;
678 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
679
680 End ;
681 Else
682 v_reqnum := Null ;
683 v_reqdate := Null ;
684 v_reqamt := Null ;
685 v_reqccid := Null ;
686
687 End If ;
688
689 Insert_Processing ;
690
691 end loop;
692
693 Begin
694 dbms_sql.close_cursor(v_po_cursor);
695 Exception
696 When Others then
697 v_error_code := sqlcode ;
698 v_error_mesg := sqlerrm ;
699 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',v_error_mesg);
700 End ;
701
702 EXCEPTION
703 WHEN OTHERS THEN
704 l_errbuf := SQLERRM;
705 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
706 raise;
707 End process_pos ;
708
709
710 ----------------------------------------------------------------------
711 -- INSERT_PROCESSING
712 ----------------------------------------------------------------------
713 Procedure Insert_processing is
714 l_module_name varchar2(200) := g_module_name || 'insert_processing';
715 l_errbuf varchar2(300);
716 Begin
717 -- Perform the Inserts
718 insert into fv_status_oblig_temp
719 (REQNUM ,
720 REQDATE ,
721 REQAMT ,
722 REQCCID ,
723 OBLIGNUM ,
724 OBLIGDATE ,
725 OBLIGAMT ,
726 OBLIGCCID ,
727 OBLIGSTATUS ,
728 INVNUM ,
729 INVDATE ,
730 INVAMT ,
731 INVCCID ,
732 segval1 ,
733 segval2 ,
734 segval3 ,
735 INV_PO_DISTRIBUTION_ID ,
736 PO_REQ_DISTRIBUTION_ID ,
737 PO_DISTRIBUTION_ID )
738 values
739 (v_reqnum ,
740 v_reqdate ,
741 v_reqamt ,
742 v_reqccid ,
743 v_oblignum ,
744 v_obligdate ,
745 v_obligamt ,
746 v_obligccid ,
747 v_obligstatus,
748 v_invnum ,
749 v_invdate ,
750 v_invamt ,
751 v_invccid,
752 v_seg_val1,
753 v_seg_val2,
754 v_seg_val3,
755 v_inv_po_distribution_id,
756 v_po_req_distribution_id,
757 v_po_distribution_id ) ;
758 EXCEPTION
759 WHEN OTHERS THEN
760 l_errbuf := SQLERRM;
761 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
762 raise;
763 End Insert_Processing ;
764
765 -----------------------------------------------------------------
766 -- End Of the Package
767 -----------------------------------------------------------------
768 End fv_status_of_obligations ;
769