[Home] [Help]
PACKAGE BODY: APPS.CN_RT_FORMULA_ASGNS_PKG
Source
1 package body CN_RT_FORMULA_ASGNS_PKG as
2 /* $Header: cntrtasb.pls 120.1 2005/06/22 16:32:50 appldev ship $ */
3
4 procedure INSERT_ROW
5 (X_RT_FORMULA_ASGN_ID IN OUT NOCOPY CN_RT_FORMULA_ASGNS.RT_FORMULA_ASGN_ID%TYPE,
6 X_CALC_FORMULA_ID IN CN_RT_FORMULA_ASGNS.CALC_FORMULA_ID%TYPE,
7 X_RATE_SCHEDULE_ID IN CN_RT_FORMULA_ASGNS.RATE_SCHEDULE_ID%TYPE,
8 X_START_DATE IN CN_RT_FORMULA_ASGNS.START_DATE%TYPE,
9 X_END_DATE IN CN_RT_FORMULA_ASGNS.END_DATE%TYPE := NULL,
10 --R12 MOAC Changes--Start
11 X_ORG_ID IN CN_RT_FORMULA_ASGNS.ORG_ID%TYPE,
12 --R12 MOAC Changes--End
13 X_ATTRIBUTE_CATEGORY IN CN_RT_FORMULA_ASGNS.ATTRIBUTE_CATEGORY%TYPE := NULL,
14 X_ATTRIBUTE1 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE1%TYPE := NULL,
15 X_ATTRIBUTE2 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE2%TYPE := NULL,
16 X_ATTRIBUTE3 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE3%TYPE := NULL,
17 X_ATTRIBUTE4 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE4%TYPE := NULL,
18 X_ATTRIBUTE5 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE5%TYPE := NULL,
19 X_ATTRIBUTE6 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE6%TYPE := NULL,
20 X_ATTRIBUTE7 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE7%TYPE := NULL,
21 X_ATTRIBUTE8 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE8%TYPE := NULL,
22 X_ATTRIBUTE9 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE9%TYPE := NULL,
23 X_ATTRIBUTE10 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE10%TYPE := NULL,
24 X_ATTRIBUTE11 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE11%TYPE := NULL,
25 X_ATTRIBUTE12 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE12%TYPE := NULL,
26 X_ATTRIBUTE13 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE13%TYPE := NULL,
27 X_ATTRIBUTE14 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE14%TYPE := NULL,
28 X_ATTRIBUTE15 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE15%TYPE := NULL,
29 X_CREATION_DATE IN CN_RT_FORMULA_ASGNS.CREATION_DATE%TYPE := SYSDATE,
30 X_CREATED_BY IN CN_RT_FORMULA_ASGNS.CREATED_BY%TYPE := FND_GLOBAL.USER_ID,
31 X_LAST_UPDATE_DATE IN CN_RT_FORMULA_ASGNS.LAST_UPDATE_DATE%TYPE := SYSDATE,
32 X_LAST_UPDATED_BY IN CN_RT_FORMULA_ASGNS.LAST_UPDATED_BY%TYPE := FND_GLOBAL.USER_ID,
33 X_LAST_UPDATE_LOGIN IN CN_RT_FORMULA_ASGNS.LAST_UPDATE_LOGIN%TYPE := FND_GLOBAL.LOGIN_ID) IS
34
35 cursor C is select rt_formula_asgn_id from CN_RT_FORMULA_ASGNS
36 where RT_FORMULA_ASGN_ID = x_rt_formula_asgn_id;
37
38 CURSOR id IS SELECT cn_rt_formula_asgns_s.NEXTVAL FROM dual;
39 BEGIN
40 IF (x_rt_formula_asgn_id IS NULL) THEN
41 OPEN id;
42 FETCH id INTO x_rt_formula_asgn_id;
43 IF (id%notfound) THEN
44 CLOSE id;
45 RAISE no_data_found;
46 END IF;
47 CLOSE id;
48 END IF;
49
50 insert into CN_RT_FORMULA_ASGNS (
51 RT_FORMULA_ASGN_ID,
52 CALC_FORMULA_ID,
53 RATE_SCHEDULE_ID,
54 START_DATE,
55 END_DATE,
56 --R12 MOAC Changes--Start
57 ORG_ID,
58 --R12 MOAC Changes--End
59 ATTRIBUTE_CATEGORY,
60 ATTRIBUTE1,
61 ATTRIBUTE2,
62 ATTRIBUTE3,
63 ATTRIBUTE4,
64 ATTRIBUTE5,
65 ATTRIBUTE6,
66 ATTRIBUTE7,
67 ATTRIBUTE8,
68 ATTRIBUTE9,
69 ATTRIBUTE10,
70 ATTRIBUTE11,
71 ATTRIBUTE12,
72 ATTRIBUTE13,
73 ATTRIBUTE14,
74 ATTRIBUTE15,
75 CREATED_BY,
76 CREATION_DATE,
77 LAST_UPDATE_LOGIN,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 OBJECT_VERSION_NUMBER
81 ) VALUES (
82 X_RT_FORMULA_ASGN_ID,
83 X_CALC_FORMULA_ID,
84 X_RATE_SCHEDULE_ID,
85 X_START_DATE,
86 X_END_DATE,
87 --R12 MOAC Changes--Start
88 X_ORG_ID,
89 --R12 MOAC Changes--End
90 X_ATTRIBUTE_CATEGORY,
91 X_ATTRIBUTE1,
92 X_ATTRIBUTE2,
93 X_ATTRIBUTE3,
94 X_ATTRIBUTE4,
95 X_ATTRIBUTE5,
96 X_ATTRIBUTE6,
97 X_ATTRIBUTE7,
98 X_ATTRIBUTE8,
99 X_ATTRIBUTE9,
100 X_ATTRIBUTE10,
101 X_ATTRIBUTE11,
102 X_ATTRIBUTE12,
103 X_ATTRIBUTE13,
104 X_ATTRIBUTE14,
105 X_ATTRIBUTE15,
106 X_CREATED_BY,
107 X_CREATION_DATE,
108 X_LAST_UPDATE_LOGIN,
109 X_LAST_UPDATE_DATE,
110 X_LAST_UPDATED_BY,
111 0);
112
113 open c;
114 fetch c into x_rt_formula_asgn_id;
115 if (c%notfound) then
116 close c;
117 raise no_data_found;
118 end if;
119 close c;
120
121 end INSERT_ROW;
122
123 procedure LOCK_ROW
124 (X_RT_FORMULA_ASGN_ID IN CN_RT_FORMULA_ASGNS.RT_FORMULA_ASGN_ID%TYPE,
125 X_OBJECT_VERSION_NUMBER IN CN_RT_FORMULA_ASGNS.OBJECT_VERSION_NUMBER%TYPE) IS
126
127 cursor c is
128 select object_version_number
129 from cn_rt_formula_asgns
130 where rt_formula_asgn_id = x_rt_formula_asgn_id
131 for update of rt_formula_asgn_id nowait;
132 tlinfo c%rowtype ;
133 BEGIN
134 open c;
135 fetch c into tlinfo;
136 if (c%notfound) then
137 close c;
138 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
139 fnd_msg_pub.add;
140 raise fnd_api.g_exc_unexpected_error;
141 end if;
142 close c;
143
144 if (tlinfo.object_version_number <> x_object_version_number) then
145 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
146 fnd_msg_pub.add;
147 raise fnd_api.g_exc_unexpected_error;
148 end if;
149
150 END LOCK_ROW;
151
152 procedure UPDATE_ROW
153 (X_RT_FORMULA_ASGN_ID IN CN_RT_FORMULA_ASGNS.RT_FORMULA_ASGN_ID%TYPE,
154 X_CALC_FORMULA_ID IN CN_RT_FORMULA_ASGNS.CALC_FORMULA_ID%TYPE,
155 X_RATE_SCHEDULE_ID IN CN_RT_FORMULA_ASGNS.RATE_SCHEDULE_ID%TYPE := CN_API.G_MISS_NUM,
156 X_START_DATE IN CN_RT_FORMULA_ASGNS.START_DATE%TYPE := CN_API.G_MISS_DATE,
157 X_END_DATE IN CN_RT_FORMULA_ASGNS.END_DATE%TYPE := CN_API.G_MISS_DATE,
158 X_ATTRIBUTE_CATEGORY IN CN_RT_FORMULA_ASGNS.ATTRIBUTE_CATEGORY%TYPE := CN_API.G_MISS_CHAR,
159 X_ATTRIBUTE1 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE1%TYPE := CN_API.G_MISS_CHAR,
160 X_ATTRIBUTE2 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE2%TYPE := CN_API.G_MISS_CHAR,
161 X_ATTRIBUTE3 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE3%TYPE := CN_API.G_MISS_CHAR,
162 X_ATTRIBUTE4 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE4%TYPE := CN_API.G_MISS_CHAR,
163 X_ATTRIBUTE5 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE5%TYPE := CN_API.G_MISS_CHAR,
164 X_ATTRIBUTE6 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE6%TYPE := CN_API.G_MISS_CHAR,
165 X_ATTRIBUTE7 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE7%TYPE := CN_API.G_MISS_CHAR,
166 X_ATTRIBUTE8 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE8%TYPE := CN_API.G_MISS_CHAR,
167 X_ATTRIBUTE9 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE9%TYPE := CN_API.G_MISS_CHAR,
168 X_ATTRIBUTE10 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE10%TYPE := CN_API.G_MISS_CHAR,
169 X_ATTRIBUTE11 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE11%TYPE := CN_API.G_MISS_CHAR,
170 X_ATTRIBUTE12 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE12%TYPE := CN_API.G_MISS_CHAR,
171 X_ATTRIBUTE13 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE13%TYPE := CN_API.G_MISS_CHAR,
172 X_ATTRIBUTE14 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE14%TYPE := CN_API.G_MISS_CHAR,
173 X_ATTRIBUTE15 IN CN_RT_FORMULA_ASGNS.ATTRIBUTE15%TYPE := CN_API.G_MISS_CHAR,
174 X_OBJECT_VERSION_NUMBER IN CN_RT_FORMULA_ASGNS.OBJECT_VERSION_NUMBER%TYPE,
175 X_LAST_UPDATE_DATE IN CN_RT_FORMULA_ASGNS.LAST_UPDATE_DATE%TYPE := SYSDATE,
176 X_LAST_UPDATED_BY IN CN_RT_FORMULA_ASGNS.LAST_UPDATED_BY%TYPE := FND_GLOBAL.USER_ID,
177 X_LAST_UPDATE_LOGIN IN CN_RT_FORMULA_ASGNS.LAST_UPDATE_LOGIN%TYPE := FND_GLOBAL.LOGIN_ID) IS
178
179 BEGIN
180 update CN_RT_FORMULA_ASGNS set
181 RATE_SCHEDULE_ID = Decode(X_RATE_SCHEDULE_ID, fnd_api.g_miss_char, rate_schedule_id, x_rate_schedule_id),
182 START_DATE = Decode(X_START_DATE, fnd_api.g_miss_char, start_date, x_start_date),
183 END_DATE = Decode(X_END_DATE, fnd_api.g_miss_char, end_date, x_end_date),
184 ATTRIBUTE_CATEGORY = Decode(X_ATTRIBUTE_CATEGORY, fnd_api.g_miss_char, attribute_category, x_attribute_category),
185 ATTRIBUTE1 = Decode(X_ATTRIBUTE1, fnd_api.g_miss_char, attribute1, x_attribute1),
186 ATTRIBUTE2 = Decode(X_ATTRIBUTE2, fnd_api.g_miss_char, attribute2, x_attribute2),
187 ATTRIBUTE3 = Decode(X_ATTRIBUTE3, fnd_api.g_miss_char, attribute3, x_attribute3),
188 ATTRIBUTE4 = Decode(X_ATTRIBUTE4, fnd_api.g_miss_char, attribute4, x_attribute4),
189 ATTRIBUTE5 = Decode(X_ATTRIBUTE5, fnd_api.g_miss_char, attribute5, x_attribute5),
190 ATTRIBUTE6 = Decode(X_ATTRIBUTE6, fnd_api.g_miss_char, attribute6, x_attribute6),
191 ATTRIBUTE7 = Decode(X_ATTRIBUTE7, fnd_api.g_miss_char, attribute7, x_attribute7),
192 ATTRIBUTE8 = Decode(X_ATTRIBUTE8, fnd_api.g_miss_char, attribute8, x_attribute8),
193 ATTRIBUTE9 = Decode(X_ATTRIBUTE9, fnd_api.g_miss_char, attribute9, x_attribute9),
194 ATTRIBUTE10 = Decode(X_ATTRIBUTE10, fnd_api.g_miss_char, attribute10, x_attribute10),
195 ATTRIBUTE11 = Decode(X_ATTRIBUTE11, fnd_api.g_miss_char, attribute11, x_attribute11),
196 ATTRIBUTE12 = Decode(X_ATTRIBUTE12, fnd_api.g_miss_char, attribute12, x_attribute12),
197 ATTRIBUTE13 = Decode(X_ATTRIBUTE13, fnd_api.g_miss_char, attribute13, x_attribute13),
198 ATTRIBUTE14 = Decode(X_ATTRIBUTE14, fnd_api.g_miss_char, attribute14, x_attribute14),
199 ATTRIBUTE15 = Decode(X_ATTRIBUTE15, fnd_api.g_miss_char, attribute15, x_attribute15),
200 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
201 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
202 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
203 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1
204 where RT_FORMULA_ASGN_ID = x_rt_formula_asgn_id;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209 end UPDATE_ROW;
210
211 procedure DELETE_ROW
212 (X_RT_FORMULA_ASGN_ID IN CN_RT_FORMULA_ASGNS.RT_FORMULA_ASGN_ID%TYPE) IS
213 begin
214 delete from CN_RT_FORMULA_ASGNS
215 where RT_FORMULA_ASGN_ID = X_RT_FORMULA_ASGN_ID;
216
217 if (sql%notfound) then
218 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
219 fnd_msg_pub.add;
220 raise fnd_api.g_exc_error;
221 end if;
222
223 end DELETE_ROW;
224
225 end CN_RT_FORMULA_ASGNS_PKG;