[Home] [Help]
PACKAGE BODY: APPS.PSB_WS_LINE_YEAR_L_PVT
Source
1 PACKAGE BODY PSB_WS_LINE_YEAR_L_PVT AS
2 /* $Header: PSBWLYLB.pls 120.2 2005/07/13 11:35:22 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WS_LINE_YEAR_L_PVT';
5
6
7
8
9 /*==========================================================================+
10 | PROCEDURE Lock_Row |
11 +==========================================================================*/
12
13 PROCEDURE Lock_Row
14 (
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17 p_commit IN VARCHAR2 := FND_API.G_FALSE,
18 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
19 p_return_status OUT NOCOPY VARCHAR2,
20 p_msg_count OUT NOCOPY NUMBER,
21 p_msg_data OUT NOCOPY VARCHAR2,
22 p_row_locked OUT NOCOPY VARCHAR2,
23 --
24 p_service_package_id IN NUMBER,
25 --
26 p_column_count IN NUMBER,
27 --
28 p_wal_id_C1 IN NUMBER,
29 p_balance_type_C1 IN VARCHAR2,
30 p_ytd_amount_C1 IN NUMBER,
31 p_wal_id_C2 IN NUMBER,
32 p_balance_type_C2 IN VARCHAR2,
33 p_ytd_amount_C2 IN NUMBER,
34 p_wal_id_C3 IN NUMBER,
35 p_balance_type_C3 IN VARCHAR2,
36 p_ytd_amount_C3 IN NUMBER,
37 p_wal_id_C4 IN NUMBER,
38 p_balance_type_C4 IN VARCHAR2,
39 p_ytd_amount_C4 IN NUMBER,
40 p_wal_id_C5 IN NUMBER,
41 p_balance_type_C5 IN VARCHAR2,
42 p_ytd_amount_C5 IN NUMBER,
43 p_wal_id_C6 IN NUMBER,
44 p_balance_type_C6 IN VARCHAR2,
45 p_ytd_amount_C6 IN NUMBER,
46 p_wal_id_C7 IN NUMBER,
47 p_balance_type_C7 IN VARCHAR2,
48 p_ytd_amount_C7 IN NUMBER,
49 p_wal_id_C8 IN NUMBER,
50 p_balance_type_C8 IN VARCHAR2,
51 p_ytd_amount_C8 IN NUMBER,
52 p_wal_id_C9 IN NUMBER,
53 p_balance_type_C9 IN VARCHAR2,
54 p_ytd_amount_C9 IN NUMBER,
55 p_wal_id_C10 IN NUMBER,
56 p_balance_type_C10 IN VARCHAR2,
57 p_ytd_amount_C10 IN NUMBER,
58 p_wal_id_C11 IN NUMBER,
59 p_balance_type_C11 IN VARCHAR2,
60 p_ytd_amount_C11 IN NUMBER,
61 p_wal_id_C12 IN NUMBER,
62 p_balance_type_C12 IN VARCHAR2,
63 p_ytd_amount_C12 IN NUMBER
64
65 )
66 IS
67 --
68 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
69 l_api_version CONSTANT NUMBER := 1.0;
70 --
71 l_wal_id NUMBER;
72 l_year_type VARCHAR2(2);
73 l_balance_type VARCHAR2(1);
74 l_ytd_amount NUMBER;
75
76 Counter NUMBER;
77 CURSOR C IS
78 SELECT ytd_amount,account_line_id
79 FROM psb_ws_account_lines
80 WHERE account_line_id = l_wal_id
81 FOR UPDATE of ytd_amount NOWAIT;
82 Recinfo C%ROWTYPE;
83
84 BEGIN
85 --
86 SAVEPOINT Lock_Row_Pvt ;
87 --
88 IF NOT FND_API.Compatible_API_Call ( l_api_version,
89 p_api_version,
90 l_api_name,
91 G_PKG_NAME )
92 THEN
93 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
94 END IF;
95 --
96
97 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
98 FND_MSG_PUB.initialize ;
99 END IF;
100 --
101 p_row_locked := FND_API.G_TRUE;
102 p_return_status := FND_API.G_RET_STS_SUCCESS ;
103 --
104 FOR i in 1..p_column_count LOOP
105 IF i = 1 THEN
106 l_wal_id := p_wal_id_C1;
107 l_balance_type := p_balance_type_C1;
108 l_ytd_amount := p_ytd_amount_C1;
109 ELSIF i =2 THEN
110 l_wal_id := p_wal_id_C2;
111 l_balance_type := p_balance_type_C2;
112 l_ytd_amount := p_ytd_amount_C2;
113 ELSIF i =3 THEN
114 l_wal_id := p_wal_id_C3;
115 l_balance_type := p_balance_type_C3;
116 l_ytd_amount := p_ytd_amount_C3;
117 ELSIF i =4 THEN
118 l_wal_id := p_wal_id_C4;
119 l_balance_type := p_balance_type_C4;
120 l_ytd_amount := p_ytd_amount_C4;
121 ELSIF i =5 THEN
122 l_wal_id := p_wal_id_C5;
123 l_balance_type := p_balance_type_C5;
124 l_ytd_amount := p_ytd_amount_C5;
125 ELSIF i =6 THEN
126 l_wal_id := p_wal_id_C6;
127 l_balance_type := p_balance_type_C6;
128 l_ytd_amount := p_ytd_amount_C6;
129 ELSIF i =7 THEN
130 l_wal_id := p_wal_id_C7;
131 l_balance_type := p_balance_type_C7;
132 l_ytd_amount := p_ytd_amount_C7;
133 ELSIF i =8 THEN
134 l_wal_id := p_wal_id_C8;
135 l_balance_type := p_balance_type_C8;
136 l_ytd_amount := p_ytd_amount_C8;
137 ELSIF i =9 THEN
138 l_wal_id := p_wal_id_C9;
139 l_balance_type := p_balance_type_C9;
140 l_ytd_amount := p_ytd_amount_C9;
141 ELSIF i =10 THEN
142 l_wal_id := p_wal_id_C10;
143 l_balance_type := p_balance_type_C10;
144 l_ytd_amount := p_ytd_amount_C10;
145 ELSIF i =11 THEN
146 l_wal_id := p_wal_id_C11;
147 l_balance_type := p_balance_type_C11;
148 l_ytd_amount := p_ytd_amount_C11;
149 ELSIF i =12 THEN
150 l_wal_id := p_wal_id_C12;
151 l_balance_type := p_balance_type_C12;
152 l_ytd_amount := p_ytd_amount_C12;
153 END IF;
154
155
156 -- amount types can be B-Budget, A-Actuals, E- Estimate, F -FTE
157 -- Lock rows only for estimates
158 IF l_balance_type = 'E' and nvl(l_wal_id,0) <> 0 THEN
159
160 OPEN C;
161 --
162 FETCH C INTO Recinfo;
163 IF (C%NOTFOUND) then
164 CLOSE C;
165 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
166 FND_MSG_PUB.Add;
167 RAISE FND_API.G_EXC_ERROR ;
168 END IF;
169 CLOSE C;
170 --Check for Amount change removed for performance reasons
171
172
173 END IF;
174
175 END LOOP;
176 --
177 IF FND_API.To_Boolean ( p_commit ) THEN
178 COMMIT WORK;
179 END iF;
180 --
181 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
182 p_data => p_msg_data );
183 --
184 EXCEPTION
185 --
186 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
187 ROLLBACK TO Lock_Row_Pvt ;
188 p_row_locked := FND_API.G_FALSE;
189 p_return_status := FND_API.G_RET_STS_ERROR;
190 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
191 p_data => p_msg_data );
192 WHEN FND_API.G_EXC_ERROR THEN
193 --
194 ROLLBACK TO Lock_Row_Pvt ;
195 p_return_status := FND_API.G_RET_STS_ERROR;
196 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
197 p_data => p_msg_data );
198 --
199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
200 --
201 ROLLBACK TO Lock_Row_Pvt ;
202 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
204 p_data => p_msg_data );
205 --
206 WHEN OTHERS THEN
207 --
208 ROLLBACK TO Lock_Row_Pvt ;
209 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 --
211 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
212 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
213 l_api_name);
214 END if;
215 --
216 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
217 p_data => p_msg_data );
218 --
219 END Lock_Row;
220 /* ----------------------------------------------------------------------- */
221
222
223 END PSB_WS_LINE_YEAR_L_PVT;