[Home] [Help]
PACKAGE BODY: APPS.PV_PRGM_CONTRACTS_PKG
Source
1 PACKAGE BODY PV_PRGM_CONTRACTS_PKG as
2 /* $Header: pvxtppcb.pls 120.0 2005/05/27 15:35:21 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_PRGM_CONTRACTS_PKG
7 -- Purpose
8 --
9 -- History
10 -- 7-MAR-2002 Peter.Nixon Created
11 -- 30-APR-2002 Peter.Nixon Modified
12 -- 11-JUN-2002 Karen.Tsao Modified to reverse logic of G_MISS_XXX and NULL.
13 -- 27-NOV-2002 Karen.Tsao 1. Debug message to be wrapped with IF check.
14 -- 2. Replace of COPY with NOCOPY string.
15 -- 28-AUG-2003 Karen.Tsao Change membership_type to member_type_code.
16 -- NOTE
17 --
18 -- Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA
19 -- All rights reserved.
20 --
21 -- End of Comments
22 -- ===============================================================
23
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_PRGM_CONTRACTS_PKG';
26 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtppcb.pls';
27
28
29 -- ========================================================
30 --
31 -- NAME
32 -- Insert_Row
33 --
34 -- PURPOSE
35 --
36 -- NOTES
37 --
38 -- HISTORY
39 --
40 -- ========================================================
41 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
42 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
43 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
44
45 PROCEDURE Insert_Row(
46 px_program_contracts_id IN OUT NOCOPY NUMBER
47 ,p_program_id NUMBER
48 ,p_geo_hierarchy_id NUMBER
49 ,p_contract_id NUMBER
50 ,p_last_update_date DATE
51 ,p_last_updated_by NUMBER
52 ,p_creation_date DATE
53 ,p_created_by NUMBER
54 ,p_last_update_login NUMBER
55 ,p_object_version_number NUMBER
56 ,p_member_type_code VARCHAR2
57 )
58
59 IS
60
61 BEGIN
62
63 INSERT INTO PV_PROGRAM_CONTRACTS(
64 program_contracts_id
65 ,program_id
66 ,geo_hierarchy_id
67 ,contract_id
68 ,last_update_date
69 ,last_updated_by
70 ,creation_date
71 ,created_by
72 ,last_update_login
73 ,object_version_number
74 ,member_type_code
75 )
76 VALUES (
77 DECODE( px_program_contracts_id, NULL, px_program_contracts_id, FND_API.g_miss_num, NULL, px_program_contracts_id)
78 ,DECODE( p_program_id, NULL, p_program_id, FND_API.g_miss_num, NULL, p_program_id)
79 ,DECODE( p_geo_hierarchy_id, NULL, p_geo_hierarchy_id, FND_API.g_miss_num, NULL, p_geo_hierarchy_id)
80 ,DECODE( p_contract_id, NULL, p_contract_id, FND_API.g_miss_num, NULL, p_contract_id)
81 ,DECODE( p_last_update_date, NULL, p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date)
82 ,DECODE( p_last_updated_by, NULL, p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by)
83 ,DECODE( p_creation_date, NULL, p_creation_date, FND_API.g_miss_date, NULL, p_creation_date)
84 ,DECODE( p_created_by, NULL, p_created_by, FND_API.g_miss_num, NULL, p_created_by)
85 ,DECODE( p_last_update_login, NULL, p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login)
86 ,DECODE( p_object_version_number, NULL, p_object_version_number, FND_API.g_miss_num, NULL, p_object_version_number)
87 ,DECODE( p_member_type_code, NULL, p_member_type_code, FND_API.g_miss_num, NULL, p_member_type_code)
88 );
89 END Insert_Row;
90
91
92
93 -- ========================================================
94 --
95 -- NAME
96 -- Update_Row
97 --
98 -- PURPOSE
99 --
100 -- NOTES
101 --
102 -- HISTORY
103 --
104 -- ========================================================
105 PROCEDURE Update_Row(
106 p_program_contracts_id NUMBER
107 ,p_program_id NUMBER
108 ,p_geo_hierarchy_id NUMBER
109 ,p_contract_id NUMBER
110 ,p_last_update_date DATE
111 ,p_last_updated_by NUMBER
112 ,p_last_update_login NUMBER
113 ,p_object_version_number NUMBER
114 ,p_member_type_code VARCHAR2
115 )
116
117 IS
118 BEGIN
119 Update PV_PROGRAM_CONTRACTS
120 SET
121 program_contracts_id = DECODE( p_program_contracts_id, NULL, program_contracts_id, FND_API.g_miss_num, NULL, p_program_contracts_id)
122 ,program_id = DECODE( p_program_id, NULL, program_id, FND_API.g_miss_num, NULL, p_program_id)
123 ,geo_hierarchy_id = DECODE( p_geo_hierarchy_id, NULL, geo_hierarchy_id, FND_API.g_miss_num, NULL, p_geo_hierarchy_id)
124 ,contract_id = DECODE( p_contract_id, NULL, contract_id, FND_API.g_miss_num, NULL, p_contract_id)
125 ,last_update_date = DECODE( p_last_update_date, NULL, last_update_date, FND_API.g_miss_date, NULL, p_last_update_date)
126 ,last_updated_by = DECODE( p_last_updated_by, NULL, last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by)
127 ,last_update_login = DECODE( p_last_update_login, NULL, last_update_login, FND_API.g_miss_num, NULL, p_last_update_login)
128 ,object_version_number = DECODE( p_object_version_number, NULL, object_version_number, FND_API.g_miss_num, NULL, p_object_version_number+1)
129 ,member_type_code = DECODE( p_member_type_code, NULL, member_type_code, FND_API.g_miss_char, NULL, p_member_type_code)
130 WHERE program_contracts_id = p_program_contracts_id
131 AND object_version_number = p_object_version_number;
132
133 IF (SQL%NOTFOUND) THEN
134 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
135 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
136 FND_MSG_PUB.add;
137 END IF;
138 RAISE FND_API.g_exc_error;
139 END IF;
140
141 END Update_Row;
142
143
144
145 -- ========================================================
146 --
147 -- NAME
148 -- Delete_Row
149 --
150 -- PURPOSE
151 --
152 -- NOTES
153 --
154 -- HISTORY
155 --
156 -- ========================================================
157 PROCEDURE Delete_Row(
158 p_program_contracts_id NUMBER
159 ,p_object_version_number NUMBER
160 )
161 IS
162
163 BEGIN
164
165 DELETE FROM PV_PROGRAM_CONTRACTS
166 WHERE program_contracts_id = p_program_contracts_id
167 AND object_version_number = p_object_version_number;
168
169 IF (SQL%NOTFOUND) THEN
170 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
171 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
172 FND_MSG_PUB.add;
173 END IF;
174 RAISE FND_API.g_exc_error;
175 END IF;
176
177 END DELETE_ROW ;
178
179
180
181
182 -- ========================================================
183 --
184 -- NAME
185 -- Lock_Row
186 --
187 -- PURPOSE
188 --
189 -- NOTES
190 --
191 -- HISTORY
192 --
193 -- ========================================================
194 PROCEDURE Lock_Row(
195 px_program_contracts_id IN OUT NOCOPY NUMBER
196 ,p_program_id NUMBER
197 ,p_geo_hierarchy_id NUMBER
198 ,p_contract_id NUMBER
199 ,p_last_update_date DATE
200 ,p_last_updated_by NUMBER
201 ,p_creation_date DATE
202 ,p_created_by NUMBER
203 ,p_last_update_login NUMBER
204 ,p_member_type_code VARCHAR2
205 ,px_object_version_number IN OUT NOCOPY NUMBER
206 )
207
208 IS
209 CURSOR C IS
210 SELECT *
211 FROM PV_PROGRAM_CONTRACTS
212 WHERE program_contracts_id = px_program_contracts_id
213 FOR UPDATE of program_contracts_id NOWAIT;
214 Recinfo C%ROWTYPE;
215
216 BEGIN
217 OPEN c;
218 FETCH c INTO Recinfo;
219 If (c%NOTFOUND) then
220 CLOSE c;
221 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
222 APP_EXCEPTION.RAISE_EXCEPTION;
223 END IF;
224 CLOSE C;
225 IF (
226 ( Recinfo.program_contracts_id = px_program_contracts_id)
227 AND ( ( Recinfo.program_id = p_program_id)
228 OR ( ( Recinfo.program_id IS NULL )
229 AND ( p_program_id IS NULL )))
230 AND ( ( Recinfo.geo_hierarchy_id = p_geo_hierarchy_id)
231 OR ( ( Recinfo.geo_hierarchy_id IS NULL )
232 AND ( p_geo_hierarchy_id IS NULL )))
233 AND ( ( Recinfo.contract_id = p_contract_id)
234 OR ( ( Recinfo.contract_id IS NULL )
235 AND ( p_contract_id IS NULL )))
236 AND ( ( Recinfo.last_update_date = p_last_update_date)
237 OR ( ( Recinfo.last_update_date IS NULL )
238 AND ( p_last_update_date IS NULL )))
239 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
240 OR ( ( Recinfo.last_updated_by IS NULL )
241 AND ( p_last_updated_by IS NULL )))
242 AND ( ( Recinfo.creation_date = p_creation_date)
243 OR ( ( Recinfo.creation_date IS NULL )
244 AND ( p_creation_date IS NULL )))
245 AND ( ( Recinfo.created_by = p_created_by)
246 OR ( ( Recinfo.created_by IS NULL )
247 AND ( p_created_by IS NULL )))
248 AND ( ( Recinfo.last_update_login = p_last_update_login)
249 OR ( ( Recinfo.last_update_login IS NULL )
250 AND ( p_last_update_login IS NULL )))
251 AND ( ( Recinfo.member_type_code = p_member_type_code)
252 OR ( ( Recinfo.member_type_code IS NULL )
253 AND ( p_member_type_code IS NULL )))
254 AND ( ( Recinfo.object_version_number = px_object_version_number)
255 OR ( ( Recinfo.object_version_number IS NULL )
256 AND ( px_object_version_number IS NULL )))
257 ) THEN
258 RETURN;
259 ELSE
260 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
261 APP_EXCEPTION.RAISE_EXCEPTION;
262 END IF;
263 END Lock_Row;
264
265
266 END PV_PRGM_CONTRACTS_PKG;