1 PACKAGE BODY IEC_QUOTAUPDATE_PVT AS
2 /* $Header: IECVQUOB.pls 115.10 2004/05/03 17:47:57 minwang noship $ */
3
4 PROCEDURE UPDATE_QUOTA_LIST
5 (P_QUOTA_USED IN NUMBER
6 ,P_LIST_HEADER_ID IN NUMBER
7 ,X_WORKING_QUOTA OUT NOCOPY NUMBER
8 )
9 IS
10 l_rt_quota NUMBER;
11 CURSOR c_quota IS
12 SELECT nvl(QUOTA,0) QUOTA, RELEASE_STRATEGY, nvl(WORKING_QUOTA,0) WORKING_QUOTA
13 FROM IEC_G_LIST_RT_INFO WHERE LIST_HEADER_ID = P_LIST_HEADER_ID
14 for UPDATE OF WORKING_QUOTA;
15
16 BEGIN
17
18 l_rt_quota := -1;
19 FOR v_quota IN c_quota LOOP
20 IF v_quota.release_strategy = 'QUO' THEN
21 IF v_quota.quota > 0 THEN
22 IF (v_quota.working_quota - P_QUOTA_USED) < 0 THEN
23 l_rt_quota := 0;
24 ELSE
25 l_rt_quota := v_quota.working_quota - P_QUOTA_USED;
26 END IF;
27 -- dbms_output.put_line('l_rt_quota='|| l_rt_quota );
28 UPDATE IEC_G_LIST_RT_INFO SET
29 WORKING_QUOTA = l_rt_quota WHERE CURRENT OF c_quota;
30 END IF;
31 END IF;
32 END LOOP;
33 X_WORKING_QUOTA := l_rt_quota;
34 END UPDATE_QUOTA_LIST;
35
36 PROCEDURE UPDATE_QUOTA_SUBSET
37 (P_QUOTA_USED IN NUMBER
38 ,P_LIST_SUBSET_ID IN NUMBER
39 ,X_WORKING_QUOTA OUT NOCOPY NUMBER
40 )
41 IS
42 l_rt_quota NUMBER;
43 l_quota NUMBER;
44 l_release_strategy VARCHAR2(30);
45 CURSOR c_quota IS
46 SELECT nvl(WORKING_QUOTA,0) WORKING_QUOTA
47 FROM IEC_G_SUBSET_RT_INFO WHERE LIST_SUBSET_ID = P_LIST_SUBSET_ID
48 for UPDATE OF WORKING_QUOTA;
49
50 BEGIN
51
52 l_rt_quota := -1;
53 l_quota := 0;
54 l_release_strategy := '';
55
56 SELECT nvl(QUOTA,0), RELEASE_STRATEGY into l_quota,l_release_strategy
57 FROM IEC_G_LIST_SUBSETS WHERE LIST_SUBSET_ID = P_LIST_SUBSET_ID;
58 IF l_release_strategy = 'QUO' THEN
59 IF l_quota >= 0 THEN
60
61 FOR v_quota IN c_quota LOOP
62 IF (v_quota.working_quota - P_QUOTA_USED) < 0 THEN
63 l_rt_quota := 0;
64 ELSE
65 l_rt_quota := v_quota.working_quota - P_QUOTA_USED;
66 END IF;
67
68 UPDATE IEC_G_SUBSET_RT_INFO SET
69 WORKING_QUOTA = l_rt_quota WHERE CURRENT OF c_quota;
70 END LOOP;
71 END IF;
72 END IF;
73 X_WORKING_QUOTA := l_rt_quota;
74 END UPDATE_QUOTA_SUBSET;
75
76 PROCEDURE UPDATE_QUOTA
77 (P_ID IN NUMBER
78 ,P_TYPE IN VARCHAR2
79 ,P_QUOTA_USED IN NUMBER
80 ,X_WORKING_QUOTA OUT NOCOPY NUMBER
81 )
82 IS
83 BEGIN
84 IF P_TYPE = 'L' THEN
85 UPDATE_QUOTA_LIST( P_QUOTA_USED
86 , P_ID
87 , X_WORKING_QUOTA);
88 ELSE
89 UPDATE_QUOTA_SUBSET( P_QUOTA_USED
90 , P_ID
91 , X_WORKING_QUOTA);
92 END IF;
93 END UPDATE_QUOTA;
94
95 PROCEDURE UPDATE_QUOTA
96 (P_ID IN NUMBER
97 ,P_TYPE IN VARCHAR2
98 ,P_QUOTA_USED IN NUMBER
99 )
100 IS
101 L_WORKING_QUOTA NUMBER;
102
103 BEGIN
104 IF P_TYPE = 'L' THEN
105 UPDATE_QUOTA_LIST( P_QUOTA_USED
106 , P_ID
107 , L_WORKING_QUOTA);
108 ELSE
109 UPDATE_QUOTA_SUBSET( P_QUOTA_USED
110 , P_ID
111 , L_WORKING_QUOTA);
112 END IF;
113 END UPDATE_QUOTA;
114
115 END IEC_QUOTAUPDATE_PVT;
116