[Home] [Help]
PACKAGE BODY: APPS.CN_ROLE_PAY_GROUPS_PKG
Source
1 PACKAGE BODY CN_ROLE_PAY_GROUPS_PKG AS
2 /* $Header: cntrlpgb.pls 120.3 2005/07/26 02:39:02 sjustina noship $ */
3
4 G_LAST_UPDATE_DATE DATE := sysdate;
5 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
6 G_CREATION_DATE DATE := sysdate;
7 G_CREATED_BY NUMBER := fnd_global.user_id;
8 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
9
10
11 procedure INSERT_ROW
12 (X_ROWID IN OUT NOCOPY VARCHAR2, -- required
13 X_ROLE_PAY_GROUP_ID IN OUT NOCOPY NUMBER, -- required
14 X_ROLE_ID IN NUMBER, -- required
15 X_PAY_GROUP_ID IN NUMBER, -- required
16 X_START_DATE IN DATE, -- required
17 X_END_DATE IN DATE,
18 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
19 X_ATTRIBUTE1 IN VARCHAR2,
20 X_ATTRIBUTE2 IN VARCHAR2,
21 X_ATTRIBUTE3 IN VARCHAR2,
22 X_ATTRIBUTE4 IN VARCHAR2,
23 X_ATTRIBUTE5 IN VARCHAR2,
24 X_ATTRIBUTE6 IN VARCHAR2,
25 X_ATTRIBUTE7 IN VARCHAR2,
26 X_ATTRIBUTE8 IN VARCHAR2,
27 X_ATTRIBUTE9 IN VARCHAR2,
28 X_ATTRIBUTE10 IN VARCHAR2,
29 X_ATTRIBUTE11 IN VARCHAR2,
30 X_ATTRIBUTE12 IN VARCHAR2,
31 X_ATTRIBUTE13 IN VARCHAR2,
32 X_ATTRIBUTE14 IN VARCHAR2,
33 X_ATTRIBUTE15 IN VARCHAR2,
34 X_CREATED_BY IN NUMBER,
35 X_CREATION_DATE IN DATE,
36 X_LAST_UPDATE_LOGIN IN NUMBER,
37 X_LAST_UPDATE_DATE IN DATE,
38 X_LAST_UPDATED_BY IN NUMBER,
39 X_ORG_ID IN NUMBER,
40 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER
41 ) IS
42
43
44 L_END_DATE cn_role_pay_groups.END_DATE%type;
45 L_ATTRIBUTE_CATEGORY cn_role_pay_groups.ATTRIBUTE_CATEGORY%type;
46 L_ATTRIBUTE1 cn_role_pay_groups.ATTRIBUTE1%type;
47 L_ATTRIBUTE2 cn_role_pay_groups.ATTRIBUTE2%type;
48 L_ATTRIBUTE3 cn_role_pay_groups.ATTRIBUTE3%type;
49 L_ATTRIBUTE4 cn_role_pay_groups.ATTRIBUTE4%type;
50 L_ATTRIBUTE5 cn_role_pay_groups.ATTRIBUTE5%type;
51 L_ATTRIBUTE6 cn_role_pay_groups.ATTRIBUTE6%type;
52 L_ATTRIBUTE7 cn_role_pay_groups.ATTRIBUTE7%type;
53 L_ATTRIBUTE8 cn_role_pay_groups.ATTRIBUTE8%type;
54 L_ATTRIBUTE9 cn_role_pay_groups.ATTRIBUTE9%type;
55 L_ATTRIBUTE10 cn_role_pay_groups.ATTRIBUTE10%type;
56 L_ATTRIBUTE11 cn_role_pay_groups.ATTRIBUTE11%type;
57 L_ATTRIBUTE12 cn_role_pay_groups.ATTRIBUTE12%type;
58 L_ATTRIBUTE13 cn_role_pay_groups.ATTRIBUTE13%type;
59 L_ATTRIBUTE14 cn_role_pay_groups.ATTRIBUTE14%type;
60 L_ATTRIBUTE15 cn_role_pay_groups.ATTRIBUTE15%type;
61 L_CREATED_BY cn_role_pay_groups.CREATED_BY%type;
62 L_CREATION_DATE cn_role_pay_groups.CREATION_DATE%type;
63 L_LAST_UPDATE_LOGIN cn_role_pay_groups.LAST_UPDATE_LOGIN%type;
64 L_LAST_UPDATE_DATE cn_role_pay_groups.LAST_UPDATE_DATE%type;
65 L_LAST_UPDATED_BY cn_role_pay_groups.LAST_UPDATED_BY%type;
66 L_ORG_ID cn_role_pay_groups.ORG_ID%type;
67
68 cursor C is select ROWID from CN_ROLE_PAY_GROUPS
69 where ROLE_PAY_GROUP_ID = X_ROLE_PAY_GROUP_ID;
70
71 BEGIN
72 -- dbms_output.put_line('begin insert_row');
73
74 SELECT DECODE(X_end_date, FND_API.G_MISS_DATE,
75 to_date(NULL),X_end_date)
76 INTO L_end_date FROM dual;
77 SELECT DECODE(X_attribute_category, FND_API.G_MISS_CHAR,
78 NULL,Ltrim(Rtrim(X_attribute_category)))
79 INTO L_attribute_category FROM dual;
80 SELECT DECODE(X_ATTRIBUTE1, FND_API.G_MISS_CHAR,
81 NULL,Ltrim(Rtrim(X_ATTRIBUTE1)))
82 INTO L_ATTRIBUTE1 FROM dual;
83 SELECT DECODE(X_ATTRIBUTE2, FND_API.G_MISS_CHAR,
84 NULL,Ltrim(Rtrim(X_ATTRIBUTE2)))
85 INTO L_ATTRIBUTE2 FROM dual;
86 SELECT DECODE(X_ATTRIBUTE3, FND_API.G_MISS_CHAR,
87 NULL,Ltrim(Rtrim(X_ATTRIBUTE3)))
88 INTO L_ATTRIBUTE3 FROM dual;
89 SELECT DECODE(X_ATTRIBUTE4, FND_API.G_MISS_CHAR,
90 NULL,Ltrim(Rtrim(X_ATTRIBUTE4)))
91 INTO L_ATTRIBUTE4 FROM dual;
92 SELECT DECODE(X_ATTRIBUTE5, FND_API.G_MISS_CHAR,
93 NULL,Ltrim(Rtrim(X_ATTRIBUTE5)))
94 INTO L_ATTRIBUTE5 FROM dual;
95 SELECT DECODE(X_ATTRIBUTE6, FND_API.G_MISS_CHAR,
96 NULL,Ltrim(Rtrim(X_ATTRIBUTE6)))
97 INTO L_ATTRIBUTE6 FROM dual;
98 SELECT DECODE(X_ATTRIBUTE7, FND_API.G_MISS_CHAR,
99 NULL,Ltrim(Rtrim(X_ATTRIBUTE7)))
100 INTO L_ATTRIBUTE7 FROM dual;
101 SELECT DECODE(X_ATTRIBUTE8, FND_API.G_MISS_CHAR,
102 NULL,Ltrim(Rtrim(X_ATTRIBUTE8)))
103 INTO L_ATTRIBUTE8 FROM dual;
104 SELECT DECODE(X_ATTRIBUTE9, FND_API.G_MISS_CHAR,
105 NULL,Ltrim(Rtrim(X_ATTRIBUTE9)))
106 INTO L_ATTRIBUTE9 FROM dual;
107 SELECT DECODE(X_ATTRIBUTE10, FND_API.G_MISS_CHAR,
108 NULL,Ltrim(Rtrim(X_ATTRIBUTE10)))
109 INTO L_ATTRIBUTE10 FROM dual;
110 SELECT DECODE(X_ATTRIBUTE11, FND_API.G_MISS_CHAR,
111 NULL,Ltrim(Rtrim(X_ATTRIBUTE11)))
112 INTO L_ATTRIBUTE11 FROM dual;
113 SELECT DECODE(X_ATTRIBUTE12, FND_API.G_MISS_CHAR,
114 NULL,Ltrim(Rtrim(X_ATTRIBUTE12)))
115 INTO L_ATTRIBUTE12 FROM dual;
116 SELECT DECODE(X_ATTRIBUTE13, FND_API.G_MISS_CHAR,
117 NULL,Ltrim(Rtrim(X_ATTRIBUTE13)))
118 INTO L_ATTRIBUTE13 FROM dual;
119 SELECT DECODE(X_ATTRIBUTE14, FND_API.G_MISS_CHAR,
120 NULL,Ltrim(Rtrim(X_ATTRIBUTE14)))
121 INTO L_ATTRIBUTE14 FROM dual;
122 SELECT DECODE(X_attribute15, FND_API.G_MISS_CHAR,
123 NULL,Ltrim(Rtrim(X_attribute15)))
124 INTO L_attribute15 FROM dual;
125 SELECT DECODE(X_created_by, FND_API.G_MISS_NUM,
126 G_CREATED_BY,Ltrim(Rtrim(X_CREATED_BY)))
127 INTO L_created_by FROM dual;
128 SELECT DECODE(X_creation_date, FND_API.G_MISS_DATE,
129 G_CREATION_DATE,X_CREATION_DATE)
130 INTO L_creation_date FROM dual;
131 SELECT DECODE(X_last_update_login, FND_API.G_MISS_NUM,
132 G_LAST_UPDATE_LOGIN,Ltrim(Rtrim(X_LAST_UPDATE_LOGIN)))
133 INTO L_last_update_login FROM dual;
134 SELECT DECODE(X_last_update_date, FND_API.G_MISS_DATE,
135 G_LAST_UPDATE_DATE,X_LAST_UPDATE_DATE)
136 INTO L_last_update_date FROM dual;
137 SELECT DECODE(X_last_updated_by, FND_API.G_MISS_NUM,
138 G_LAST_UPDATED_BY,Ltrim(Rtrim(X_LAST_UPDATED_BY)))
139 INTO L_last_updated_by FROM dual;
140 SELECT DECODE(X_ORG_ID, FND_API.G_MISS_NUM,
141 NULL,Ltrim(Rtrim(X_ORG_ID)))
142 INTO L_ORG_ID FROM dual;
143
144 -- dbms_output.put_line('before insert_row');
145
146 INSERT INTO cn_role_pay_groups (
147 ROLE_PAY_GROUP_ID,
148 ROLE_ID,
149 PAY_GROUP_ID,
150 START_DATE,
151 END_DATE,
152 ATTRIBUTE_CATEGORY,
153 ATTRIBUTE1,
154 ATTRIBUTE2,
155 ATTRIBUTE3,
156 ATTRIBUTE4,
157 ATTRIBUTE5,
158 ATTRIBUTE6,
159 ATTRIBUTE7,
160 ATTRIBUTE8,
161 ATTRIBUTE9,
162 ATTRIBUTE10,
163 ATTRIBUTE11,
164 ATTRIBUTE12,
165 ATTRIBUTE13,
166 ATTRIBUTE14,
167 ATTRIBUTE15,
168 CREATED_BY,
169 CREATION_DATE,
170 LAST_UPDATE_LOGIN,
171 LAST_UPDATE_DATE,
172 LAST_UPDATED_BY,
173 ORG_ID,
174 OBJECT_VERSION_NUMBER)
175 VALUES (
176 X_ROLE_PAY_GROUP_ID,
177 X_ROLE_ID,
178 X_PAY_GROUP_ID,
179 X_START_DATE,
180 L_END_DATE,
181 L_ATTRIBUTE_CATEGORY,
182 L_ATTRIBUTE1,
183 L_ATTRIBUTE2,
184 L_ATTRIBUTE3,
185 L_ATTRIBUTE4,
186 L_ATTRIBUTE5,
187 L_ATTRIBUTE6,
188 L_ATTRIBUTE7,
189 L_ATTRIBUTE8,
190 L_ATTRIBUTE9,
191 L_ATTRIBUTE10,
192 L_ATTRIBUTE11,
193 L_ATTRIBUTE12,
194 L_ATTRIBUTE13,
195 L_ATTRIBUTE14,
196 L_ATTRIBUTE15,
197 L_CREATED_BY,
198 L_CREATION_DATE,
199 L_LAST_UPDATE_LOGIN,
200 L_LAST_UPDATE_DATE,
201 L_LAST_UPDATED_BY,
202 L_ORG_ID,
203 1);
204 X_OBJECT_VERSION_NUMBER:=1;
205 -- dbms_output.put_line('after insert_row');
206
207 END insert_row;
208
209
210 procedure DELETE_ROW (X_ROLE_PAY_GROUP_ID IN NUMBER) IS
211 BEGIN
212 DELETE FROM cn_role_pay_groups
213 WHERE role_pay_group_id = x_role_pay_group_id;
214 IF (sql%notfound) THEN
215 raise no_data_found;
216 END IF;
217 END delete_row;
218
219 END cn_role_pay_groups_pkg;