eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CN_MULTI_RATE_SCHEDULES_PKG

Source


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