[Home] [Help]
PACKAGE BODY: APPS.CSC_CUST_PLANS_PKG
Source
1 PACKAGE BODY CSC_CUST_PLANS_PKG as
2 /* $Header: csctctpb.pls 115.15 2002/12/04 16:16:28 bhroy ship $ */
3 -- Start of Comments
4 -- Package name : CSC_CUST_PLANS_PKG
5 -- Purpose : Table handler package to perform inserts, update, deletes and lock
6 -- row operations on CSC_CUST_PLANS table.
7 -- History :
8 -- MM-DD-YYYY NAME MODIFICATIONS
9 -- 10-28-1999 dejoseph Created.
10 -- 12-08-1999 dejoseph 'Arcs'ed in for first code freeze.
11 -- 12-21-1999 dejoseph 'Arcs'ed in for second code freeze.
12 -- 01-03-2000 dejoseph 'Arcs'ed in for third code freeze. (10-JAN-2000)
13 -- 01-31-2000 dejoseph 'Arcs'ed in for fourth code freeze. (07-FEB-2000)
14 -- 02-13-2000 dejoseph 'Arcs'ed on for fifth code freeze. (21-FEB-2000)
15 -- 02-28-2000 dejoseph 'Arcs'ed on for sixth code freeze. (06-MAR-2000)
16 -- 03-28-2000 dejoseph Removed references to CUST_ACCOUNT_ID from all
17 -- 'where' clauses. ie. and nvl(cust_account_org,0) =
18 -- nvl(p_cust_account_org, nvl(cust_account_org,0) )
19 -- 04-10-2000 dejoseph Removed reference to cust_account_org in lieu of TCA's
20 -- decision to drop column org_id from hz_cust_accounts.
21 -- 04-10-2000 dejoseph Added logic to insert SYSDATE when start_date_active is not
22 -- specified. When plans with NULL start_date_active are assigned
23 -- to customers, the customer-plan association's start_date_active
24 -- in CSC_CUST_PLANS will have to default to SYSDATE, which provides
25 -- a method to keep track of when this plan was assigned to customers.
26
27
28 -- NOTE :
29 -- End of Comments
30
31
32 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSC_CUST_PLANS_PKG';
33 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctctpb.pls';
34
35 PROCEDURE Insert_Row(
36 px_CUST_PLAN_ID IN OUT NOCOPY NUMBER,
37 p_PLAN_ID IN NUMBER,
38 p_PARTY_ID IN NUMBER,
39 p_CUST_ACCOUNT_ID IN NUMBER,
40 --p_CUST_ACCOUNT_ORG IN NUMBER,
41 p_START_DATE_ACTIVE IN DATE,
42 p_END_DATE_ACTIVE IN DATE,
43 p_MANUAL_FLAG IN VARCHAR2,
44 p_PLAN_STATUS_CODE IN VARCHAR2,
45 p_REQUEST_ID IN NUMBER,
46 p_PROGRAM_APPLICATION_ID IN NUMBER,
47 p_PROGRAM_ID IN NUMBER,
48 p_PROGRAM_UPDATE_DATE IN DATE,
49 p_CREATION_DATE IN DATE,
50 p_LAST_UPDATE_DATE IN DATE,
51 p_CREATED_BY IN NUMBER,
52 p_LAST_UPDATED_BY IN NUMBER,
53 p_LAST_UPDATE_LOGIN IN NUMBER,
54 p_ATTRIBUTE1 IN VARCHAR2,
55 p_ATTRIBUTE2 IN VARCHAR2,
56 p_ATTRIBUTE3 IN VARCHAR2,
57 p_ATTRIBUTE4 IN VARCHAR2,
58 p_ATTRIBUTE5 IN VARCHAR2,
59 p_ATTRIBUTE6 IN VARCHAR2,
60 p_ATTRIBUTE7 IN VARCHAR2,
61 p_ATTRIBUTE8 IN VARCHAR2,
62 p_ATTRIBUTE9 IN VARCHAR2,
63 p_ATTRIBUTE10 IN VARCHAR2,
64 p_ATTRIBUTE11 IN VARCHAR2,
65 p_ATTRIBUTE12 IN VARCHAR2,
66 p_ATTRIBUTE13 IN VARCHAR2,
67 p_ATTRIBUTE14 IN VARCHAR2,
68 p_ATTRIBUTE15 IN VARCHAR2,
69 p_ATTRIBUTE_CATEGORY IN VARCHAR2,
70 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER)
71 IS
72 CURSOR C2 IS
73 SELECT CSC_CUST_PLANS_S.nextval
74 FROM sys.dual;
75 BEGIN
76 If (px_CUST_PLAN_ID IS NULL) OR (px_CUST_PLAN_ID = FND_API.G_MISS_NUM) then
77 OPEN C2;
78 FETCH C2 INTO px_CUST_PLAN_ID;
79 CLOSE C2;
80 End If;
81
82 INSERT INTO CSC_CUST_PLANS(
83 CUST_PLAN_ID,
84 PLAN_ID,
85 PARTY_ID,
86 CUST_ACCOUNT_ID,
87 --CUST_ACCOUNT_ORG,
88 START_DATE_ACTIVE,
89 END_DATE_ACTIVE,
90 MANUAL_FLAG,
91 PLAN_STATUS_CODE,
92 REQUEST_ID,
93 PROGRAM_APPLICATION_ID,
94 PROGRAM_ID,
95 PROGRAM_UPDATE_DATE,
96 CREATION_DATE,
97 LAST_UPDATE_DATE,
98 CREATED_BY,
99 LAST_UPDATED_BY,
100 LAST_UPDATE_LOGIN,
101 ATTRIBUTE1,
102 ATTRIBUTE2,
103 ATTRIBUTE3,
104 ATTRIBUTE4,
105 ATTRIBUTE5,
106 ATTRIBUTE6,
107 ATTRIBUTE7,
108 ATTRIBUTE8,
109 ATTRIBUTE9,
110 ATTRIBUTE10,
111 ATTRIBUTE11,
112 ATTRIBUTE12,
113 ATTRIBUTE13,
114 ATTRIBUTE14,
115 ATTRIBUTE15,
116 ATTRIBUTE_CATEGORY,
117 OBJECT_VERSION_NUMBER
118 ) VALUES (
119 px_CUST_PLAN_ID,
120 decode( p_PLAN_ID, FND_API.G_MISS_NUM, NULL, p_PLAN_ID),
121 decode( p_PARTY_ID, FND_API.G_MISS_NUM, NULL, p_PARTY_ID),
122 decode( p_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, p_CUST_ACCOUNT_ID),
123 --decode( p_CUST_ACCOUNT_ORG, FND_API.G_MISS_NUM, NULL, p_CUST_ACCOUNT_ORG),
124 decode( p_START_DATE_ACTIVE, FND_API.G_MISS_DATE, SYSDATE,
125 NULL, SYSDATE, p_START_DATE_ACTIVE),
126 decode( p_END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, p_END_DATE_ACTIVE),
127 decode( p_MANUAL_FLAG, FND_API.G_MISS_CHAR, NULL, p_MANUAL_FLAG),
128 decode( p_PLAN_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, p_PLAN_STATUS_CODE),
129 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
130 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
131 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
132 decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_PROGRAM_UPDATE_DATE),
133 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
134 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
135 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
136 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
137 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
138 decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
139 decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
140 decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
141 decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
142 decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
143 decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
144 decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
145 decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
146 decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
147 decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
148 decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
149 decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
150 decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
151 decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
152 decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15),
153 decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY),
154 1 ); -- the first time a record is created, the object_version_number = 1
155
156 x_object_version_number := 1;
157 End Insert_Row;
158
159
160 PROCEDURE Update_Row(
161 p_CUST_PLAN_ID IN NUMBER,
162 p_PLAN_ID IN NUMBER,
163 p_PARTY_ID IN NUMBER,
164 p_CUST_ACCOUNT_ID IN NUMBER,
165 --p_CUST_ACCOUNT_ORG IN NUMBER,
166 p_START_DATE_ACTIVE IN DATE,
167 p_END_DATE_ACTIVE IN DATE,
168 p_MANUAL_FLAG IN VARCHAR2,
169 p_PLAN_STATUS_CODE IN VARCHAR2,
170 p_REQUEST_ID IN NUMBER,
171 p_PROGRAM_APPLICATION_ID IN NUMBER,
172 p_PROGRAM_ID IN NUMBER,
173 p_PROGRAM_UPDATE_DATE IN DATE,
174 p_LAST_UPDATE_DATE IN DATE,
175 p_LAST_UPDATED_BY IN NUMBER,
176 p_LAST_UPDATE_LOGIN IN NUMBER,
177 p_ATTRIBUTE1 IN VARCHAR2,
178 p_ATTRIBUTE2 IN VARCHAR2,
179 p_ATTRIBUTE3 IN VARCHAR2,
180 p_ATTRIBUTE4 IN VARCHAR2,
181 p_ATTRIBUTE5 IN VARCHAR2,
182 p_ATTRIBUTE6 IN VARCHAR2,
183 p_ATTRIBUTE7 IN VARCHAR2,
184 p_ATTRIBUTE8 IN VARCHAR2,
185 p_ATTRIBUTE9 IN VARCHAR2,
186 p_ATTRIBUTE10 IN VARCHAR2,
187 p_ATTRIBUTE11 IN VARCHAR2,
188 p_ATTRIBUTE12 IN VARCHAR2,
189 p_ATTRIBUTE13 IN VARCHAR2,
190 p_ATTRIBUTE14 IN VARCHAR2,
191 p_ATTRIBUTE15 IN VARCHAR2,
192 p_ATTRIBUTE_CATEGORY IN VARCHAR2,
193 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER)
194 IS
195 BEGIN
196
197 Update CSC_CUST_PLANS
198 SET
199 PLAN_ID = nvl(p_PLAN_ID, plan_id),
200 PARTY_ID = nvl(p_PARTY_ID, party_id),
201 CUST_ACCOUNT_ID = p_CUST_ACCOUNT_ID,
202 --CUST_ACCOUNT_ORG = p_CUST_ACCOUNT_ORG,
203 START_DATE_ACTIVE = p_START_DATE_ACTIVE,
204 END_DATE_ACTIVE = p_END_DATE_ACTIVE,
205 MANUAL_FLAG = p_MANUAL_FLAG,
206 PLAN_STATUS_CODE = p_PLAN_STATUS_CODE,
207 REQUEST_ID = p_REQUEST_ID,
208 PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID,
209 PROGRAM_ID = p_PROGRAM_ID,
210 PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE,
211 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
212 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
213 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
214 ATTRIBUTE1 = p_ATTRIBUTE1,
215 ATTRIBUTE2 = p_ATTRIBUTE2,
216 ATTRIBUTE3 = p_ATTRIBUTE3,
217 ATTRIBUTE4 = p_ATTRIBUTE4,
218 ATTRIBUTE5 = p_ATTRIBUTE5,
219 ATTRIBUTE6 = p_ATTRIBUTE6,
220 ATTRIBUTE7 = p_ATTRIBUTE7,
221 ATTRIBUTE8 = p_ATTRIBUTE8,
222 ATTRIBUTE9 = p_ATTRIBUTE9,
223 ATTRIBUTE10 = p_ATTRIBUTE10,
224 ATTRIBUTE11 = p_ATTRIBUTE11,
225 ATTRIBUTE12 = p_ATTRIBUTE12,
226 ATTRIBUTE13 = p_ATTRIBUTE13,
227 ATTRIBUTE14 = p_ATTRIBUTE14,
228 ATTRIBUTE15 = p_ATTRIBUTE15,
229 ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY,
230 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
231 where CUST_PLAN_ID = nvl(p_CUST_PLAN_ID, cust_plan_id)
232 and plan_id = nvl(p_plan_id, plan_id)
233 and party_id = nvl(p_party_id, party_id)
234 and nvl(cust_account_id,0) = nvl(p_cust_account_id, nvl(cust_account_id,0) )
235 RETURNING object_version_number INTO x_object_version_number;
236
237 If (SQL%NOTFOUND) then
238 RAISE NO_DATA_FOUND;
239 End If;
240
241 END Update_Row;
242
243 PROCEDURE Delete_Row(
244 p_CUST_PLAN_ID IN NUMBER := NULL,
245 p_PLAN_ID IN NUMBER := NULL,
246 p_PARTY_ID IN NUMBER := NULL)
247 IS
248 BEGIN
249 DELETE FROM CSC_CUST_PLANS
250 WHERE CUST_PLAN_ID = nvl(p_CUST_PLAN_ID, CUST_PLAN_ID)
251 AND PLAN_ID = nvl(p_PLAN_ID, PLAN_ID)
252 AND PARTY_ID = nvl(p_PARTY_ID, PARTY_ID);
253
254 If (SQL%NOTFOUND) then
255 RAISE NO_DATA_FOUND;
256 End If;
257
258 END Delete_Row;
259
260
261 PROCEDURE Lock_Row(
262 p_CUST_PLAN_ID IN NUMBER := NULL,
263 p_PLAN_ID IN NUMBER := NULL,
264 p_PARTY_ID IN NUMBER := NULL,
265 P_CUST_ACCOUNT_ID IN NUMBER := NULL,
266 --P_CUST_ACCOUNT_ORG IN NUMBER := NULL,
267 p_OBJECT_VERSION_NUMBER IN NUMBER)
268 IS
269 CURSOR C IS
270 SELECT *
271 FROM CSC_CUST_PLANS
272 WHERE CUST_PLAN_ID = nvl(p_CUST_PLAN_ID, CUST_PLAN_ID)
273 AND PLAN_ID = nvl(P_PLAN_ID, PLAN_ID)
274 AND PARTY_ID = nvl(P_PARTY_ID, PARTY_ID)
275 AND nvl(CUST_ACCOUNT_ID, 0) = nvl(P_CUST_ACCOUNT_ID, nvl(CUST_ACCOUNT_ID, 0) )
276 -- AND nvl(CUST_ACCOUNT_ORG, 0) = nvl(P_CUST_ACCOUNT_ORG, nvl(CUST_ACCOUNT_ORG, 0) )
277 AND OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER
278 FOR UPDATE NOWAIT;
279
280 Recinfo C%ROWTYPE;
281 BEGIN
282 OPEN C;
283 FETCH C INTO Recinfo;
284 If (C%NOTFOUND) then
285 CLOSE C;
286 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
287 APP_EXCEPTION.RAISE_EXCEPTION;
288 End If;
289 CLOSE C;
290
291 END Lock_Row;
292
293 End CSC_CUST_PLANS_PKG;