[Home] [Help]
PACKAGE BODY: APPS.QP_LIMIT_CONC_REQ
Source
1 PACKAGE BODY QP_LIMIT_CONC_REQ AS
2 /* $Header: QPXTRANB.pls 120.1 2005/06/09 03:20:09 appldev $ */
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| QPXTRANB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Body of concurrent program package QP_LIMIT_CONC_REQ |
13 --| |
14 --| HISTORY |
15 --| 21-May-2001 abprasad Created |
16 --+======================================================================--
17 --===================
18 --G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_LIMIT_CONC_REQ';
19 --========================================================================
20 -- Private Function : p_trans_total
21 --========================================================================
22 function p_trans_total(l_limit_balance_id in number) return number is
23 l_total number;
24 begin
25 select nvl(sum(amount),0)
26 into l_total
27 from qp_limit_transactions
28 where limit_balance_id = l_limit_balance_id;
29 return(l_total);
30 end;
31 --
32 --========================================================================
33 -- PROCEDURE : Update_Balances
34 -- PARAMETERS:
35 -- x_retcode OUT VARCHAR2
36 -- x_errbuf OUT VARCHAR2
37 -- p_list_header_id Identifier for the Modifier List
38 -- p_list_line_id Identifier for the Modifier line (Null or -1)
39 -- p_limit_id Identifier for limit
40 -- p_limit_balance_id Identifier for the balance
41 --
42 -- COMMENT : This is the concurrent program for updating the balances
43 -- once manual transactions are created. The scope of updation
44 -- can be Modifier level, Modifier line level, Limit level,
45 -- Limit balance level or all levels.
46 --
47 -- Updated 24-May-2001
48 --========================================================================
49 PROCEDURE Update_Balances
50 ( x_retcode OUT NOCOPY /* file.sql.39 change */ VARCHAR2
51 , x_errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2
52 , p_list_header_id IN NUMBER default null
53 , p_list_line_id IN NUMBER default null -- Must be -1 or null
54 , p_limit_id IN NUMBER default null
55 , p_limit_balance_id IN NUMBER default null
56 )
57 IS
58 --
59 cursor lmt_balances (l_limit_id in number) is
60 select *
61 from qp_limit_balances
62 where limit_id = l_limit_id and
63 limit_balance_id = nvl(p_limit_balance_id,limit_balance_id)
64 for update;
65 --
66 cursor limits is
67 select *
68 from qp_limits
69 where list_header_id = nvl(p_list_header_id,list_header_id) and
70 limit_id = nvl(p_limit_id,limit_id) and
71 list_line_id = nvl(p_list_line_id,list_line_id);
72 --
73 l_limits_row qp_limits%rowtype;
74 l_balance_row qp_limit_balances%rowtype;
75 l_trans_total number;
76 --
77 BEGIN
78 --dbms_output.enable(1000000);
79 FND_MSG_PUB.Initialize;
80 --
81 open limits;
82 loop
83 fetch limits into l_limits_row;
84 exit when limits%notfound;
85 --
86 open lmt_balances(l_limits_row.limit_id);
87 loop
88 fetch lmt_balances into l_balance_row;
89 exit when lmt_balances%notfound;
90 --
91 l_trans_total := p_trans_total(l_balance_row.limit_balance_id);
92 --
93 update qp_limit_balances
94 set available_amount = nvl(l_limits_row.amount,0) - l_trans_total,
95 consumed_amount = l_trans_total
96 where current of lmt_balances;
97 --
98 end loop;
99 close lmt_balances;
100 --
101 end loop;
102 close limits;
103 --
104 commit;
105 --
106 EXCEPTION
107 WHEN OTHERS THEN
108 x_retcode := 2;
109 x_errbuf := SUBSTRB(sqlerrm,1,255);
110 --dbms_output.put_line('errbuf='||x_errbuf);
111 ROLLBACK;
112 RAISE;
113 END Update_Balances ;
114 END QP_LIMIT_CONC_REQ;
115 -- End Package Body