[Home] [Help]
PACKAGE BODY: APPS.CN_JOB_TITLE_PKG
Source
1 PACKAGE BODY CN_JOB_TITLE_PKG AS
2 /*$Header: cntjobb.pls 115.4 2002/11/21 21:09:45 hlchen ship $*/
3
4 PROCEDURE Insert_Row(newrec IN OUT
5 CN_JOB_TITLE_PVT.JOB_ROLE_REC_TYPE) IS
6 BEGIN
7 -- get the next pk from sequence
8 SELECT cn_job_roles_s.nextval
9 INTO newrec.job_role_id
10 FROM dual;
11
12 INSERT into cn_job_roles
13 (job_role_id,
14 job_title_id,
15 role_id,
16 start_date,
17 end_date,
18 default_flag,
19 attribute_category,
20 attribute1,
21 attribute2,
22 attribute3,
23 attribute4,
24 attribute5,
25 attribute6,
26 attribute7,
27 attribute8,
28 attribute9,
29 attribute10,
30 attribute11,
31 attribute12,
32 attribute13,
33 attribute14,
34 attribute15,
35 last_update_date,
36 last_updated_by,
37 last_update_login,
38 creation_date,
39 created_by,
40 object_version_number)
41 VALUES
42 (newrec.job_role_id,
43 newrec.job_title_id,
44 newrec.role_id,
45 newrec.start_date,
46 newrec.end_date,
47 newrec.default_flag,
48 newrec.attribute_category,
49 newrec.attribute1,
50 newrec.attribute2,
51 newrec.attribute3,
52 newrec.attribute4,
53 newrec.attribute5,
54 newrec.attribute6,
55 newrec.attribute7,
56 newrec.attribute8,
57 newrec.attribute9,
58 newrec.attribute10,
59 newrec.attribute11,
60 newrec.attribute12,
61 newrec.attribute13,
62 newrec.attribute14,
63 newrec.attribute15,
64 sysdate,
65 fnd_global.user_id,
66 fnd_global.login_id,
67 sysdate,
68 fnd_global.user_id,
69 1);
70 END Insert_Row;
71
72 PROCEDURE Update_Row(newrec
73 CN_JOB_TITLE_PVT.JOB_ROLE_REC_TYPE) IS
74 CURSOR c IS
75 SELECT *
76 FROM cn_job_roles
77 WHERE job_role_id = newrec.job_role_id;
78 oldrec c%ROWTYPE;
79 BEGIN
80 open c;
81 fetch c into oldrec;
82 close c;
83
84 update cn_job_roles set
85 job_title_id = decode(newrec.job_title_id, fnd_api.g_miss_num,
86 oldrec.job_title_id,
87 newrec.job_title_id),
88 role_id = decode(newrec.role_id, fnd_api.g_miss_char,
89 oldrec.role_id,
90 newrec.role_id),
91 start_date = decode(newrec.start_date, fnd_api.g_miss_date,
92 oldrec.start_date,
93 newrec.start_date),
94 end_date = decode(newrec.end_date, fnd_api.g_miss_date,
95 oldrec.end_date,
96 newrec.end_date),
97 default_flag = decode(newrec.default_flag, fnd_api.g_miss_char,
98 oldrec.default_flag,
99 newrec.default_flag),
100 attribute_category= decode(newrec.attribute_category, fnd_api.g_miss_char,
101 oldrec.attribute_category,
102 newrec.attribute_category),
103 attribute1 = decode(newrec.attribute1, fnd_api.g_miss_char,
104 oldrec.attribute1,
105 newrec.attribute1),
106 attribute2 = decode(newrec.attribute2, fnd_api.g_miss_char,
107 oldrec.attribute2,
108 newrec.attribute2),
109 attribute3 = decode(newrec.attribute3, fnd_api.g_miss_char,
110 oldrec.attribute3,
111 newrec.attribute3),
112 attribute4 = decode(newrec.attribute4, fnd_api.g_miss_char,
113 oldrec.attribute4,
114 newrec.attribute4),
115 attribute5 = decode(newrec.attribute5, fnd_api.g_miss_char,
116 oldrec.attribute5,
117 newrec.attribute5),
118 attribute6 = decode(newrec.attribute6, fnd_api.g_miss_char,
119 oldrec.attribute6,
120 newrec.attribute6),
121 attribute7 = decode(newrec.attribute7, fnd_api.g_miss_char,
122 oldrec.attribute7,
123 newrec.attribute7),
124 attribute8 = decode(newrec.attribute8, fnd_api.g_miss_char,
125 oldrec.attribute8,
126 newrec.attribute8),
127 attribute9 = decode(newrec.attribute9, fnd_api.g_miss_char,
128 oldrec.attribute9,
129 newrec.attribute9),
130 attribute10 = decode(newrec.attribute10, fnd_api.g_miss_char,
131 oldrec.attribute10,
132 newrec.attribute10),
133 attribute11 = decode(newrec.attribute11, fnd_api.g_miss_char,
134 oldrec.attribute11,
135 newrec.attribute11),
136 attribute12 = decode(newrec.attribute12, fnd_api.g_miss_char,
137 oldrec.attribute12,
138 newrec.attribute12),
139 attribute13 = decode(newrec.attribute13, fnd_api.g_miss_char,
140 oldrec.attribute13,
141 newrec.attribute13),
142 attribute14 = decode(newrec.attribute14, fnd_api.g_miss_char,
143 oldrec.attribute14,
144 newrec.attribute14),
145 attribute15 = decode(newrec.attribute15, fnd_api.g_miss_char,
146 oldrec.attribute15,
147 newrec.attribute15),
148 last_update_login = fnd_global.login_id,
149 last_update_date = sysdate,
150 last_updated_by = fnd_global.user_id,
151 object_version_number = oldrec.object_version_number + 1
152 where job_role_id = oldrec.job_role_id;
153 END Update_Row;
154
155 PROCEDURE LOCK_ROW
156 (p_job_role_id IN NUMBER,
157 p_object_version_number IN NUMBER) IS
158
159 cursor c is
160 select object_version_number
161 from cn_job_roles
162 where job_role_id = p_job_role_id
163 for update of job_role_id nowait;
164 tlinfo c%rowtype ;
165 BEGIN
166 open c;
167 fetch c into tlinfo;
168 if (c%notfound) then
169 close c;
170 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
171 fnd_msg_pub.add;
172 raise fnd_api.g_exc_unexpected_error;
173 end if;
174 close c;
175
176 if (tlinfo.object_version_number <> p_object_version_number) then
177 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
178 fnd_msg_pub.add;
179 raise fnd_api.g_exc_unexpected_error;
180 end if;
181
182 END LOCK_ROW;
183
184 PROCEDURE Delete_Row(p_job_role_id NUMBER) IS
185 BEGIN
186 DELETE from cn_job_roles
187 WHERE job_role_id = p_job_role_id;
188 if (sql%notfound) then
189 raise no_data_found;
190 end if;
191
192 END Delete_Row;
193
194 END CN_JOB_TITLE_PKG;
195