[Home] [Help]
PACKAGE BODY: APPS.CN_INT_ASSIGN_PKG
Source
1 PACKAGE BODY CN_INT_ASSIGN_PKG AS
2 /* $Header: cntintab.pls 120.2 2005/09/19 12:05:01 ymao noship $ */
3 --
4 -- Package Name
5 -- CN_INT_ASSIGN_PKG
6 -- Purpose
7 -- Table handler for CN_CAL_PER_INT_TYPES
8 -- Form
9 -- CNINTTP
10 -- Block
11 -- INTERVAL_ASSIGNS
12 --
13 -- History
14 -- 16-Aug-99 Yonghong Mao Created
15
16 PROCEDURE insert_row
17 ( x_cal_per_int_type_id IN OUT NOCOPY cn_cal_per_int_types.cal_per_int_type_id%TYPE,
18 x_org_id cn_cal_per_int_types.org_id%TYPE,
19 x_interval_type_id cn_cal_per_int_types.interval_type_id%TYPE,
20 x_cal_period_id cn_cal_per_int_types.cal_period_id%TYPE,
21 x_interval_number cn_cal_per_int_types.interval_number%TYPE,
22 x_last_update_date cn_cal_per_int_types.last_update_date%TYPE,
23 x_last_updated_by cn_cal_per_int_types.last_updated_by%TYPE,
24 x_creation_date cn_cal_per_int_types.creation_date%TYPE,
25 x_created_by cn_cal_per_int_types.created_by%TYPE,
26 x_last_update_login cn_cal_per_int_types.last_update_login%TYPE
27 ) IS
28 CURSOR c IS
29 SELECT ROWID
30 FROM cn_cal_per_int_types_all
31 WHERE cal_per_int_type_id = x_cal_per_int_type_id;
32 l_dummy ROWID;
33 BEGIN
34 IF (x_cal_per_int_type_id IS NULL) THEN
35 SELECT cn_cal_per_int_types_s.NEXTVAL
36 INTO x_cal_per_int_type_id
37 FROM dual;
38 END IF;
39
40 INSERT INTO cn_cal_per_int_types
41 (cal_per_int_type_id,
42 interval_type_id,
43 cal_period_id,
44 interval_number,
45 last_update_date,
46 last_updated_by,
47 creation_date,
48 created_by,
49 last_update_login,
50 org_id
51 )
52 VALUES
53 (x_cal_per_int_type_id,
54 x_interval_type_id,
55 x_cal_period_id,
56 x_interval_number,
57 Decode(x_last_update_date,
58 NULL, g_last_update_date,
59 x_last_update_date),
60 Decode(x_last_updated_by,
61 NULL, g_last_updated_by,
62 x_last_updated_by),
63 Decode(x_creation_date,
64 NULL, g_creation_date,
65 x_creation_date),
66 Decode(x_created_by,
67 NULL, g_created_by,
68 x_created_by),
69 Decode(x_last_update_login,
70 NULL, g_last_update_login,
71 x_last_update_login),
72 x_org_id
73 );
74
75 OPEN c;
76 FETCH c INTO l_dummy;
77 IF (c%notfound) THEN
78 CLOSE c;
79 RAISE no_data_found;
80 END IF;
81 CLOSE c;
82
83 END insert_row;
84
85 PROCEDURE update_row
86 ( x_cal_per_int_type_id cn_cal_per_int_types.cal_per_int_type_id%TYPE,
87 x_interval_number cn_cal_per_int_types.interval_number%TYPE,
88 x_last_update_date cn_cal_per_int_types.last_update_date%TYPE,
89 x_last_updated_by cn_cal_per_int_types.last_updated_by%TYPE,
90 x_last_update_login cn_cal_per_int_types.last_update_login%TYPE
91 )
92 IS
93 CURSOR c IS
94 SELECT cal_period_id, interval_number, interval_type_id, org_id
95 FROM cn_cal_per_int_types_all
96 WHERE cal_per_int_type_id = x_cal_per_int_type_id
97 FOR UPDATE OF cal_per_int_type_id nowait;
98
99 rec c%ROWTYPE;
100
101 CURSOR name(p_org_id number) IS
102 SELECT name
103 FROM cn_interval_types
104 WHERE interval_type_id = rec.interval_type_id
105 AND org_id = p_org_id;
106
107 l_object_name VARCHAR2(80);
108
109 CURSOR dates(p_org_id number) IS
110 SELECT start_date, end_date
111 FROM cn_period_statuses_all
112 WHERE period_id = rec.cal_period_id
113 AND org_id = p_org_id;
114
115 l_start_date DATE;
116 l_end_date DATE;
117 BEGIN
118 OPEN c;
119 FETCH c INTO rec;
120
121 IF (c%notfound) THEN
122 CLOSE c;
123 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
124 app_exception.raise_exception;
125 END IF;
126 CLOSE c;
127
128 UPDATE cn_cal_per_int_types_all SET
129 interval_number = x_interval_number,
130 last_update_date = x_last_update_date,
131 last_updated_by = x_last_updated_by,
132 last_update_login = x_last_update_login
133 WHERE cal_per_int_type_id = x_cal_per_int_type_id;
134
135 IF (SQL%notfound) THEN
136 RAISE no_data_found;
137 END IF;
138
139 -- mark the "CHANGE_PERIOD_INTERVAL_NUMBER" event for intelligent calculation
140 IF (rec.interval_number <> x_interval_number AND fnd_profile.value('CN_MARK_EVENTS') = 'Y' ) THEN
141 -- get the object name which is the name of the interval type here.
142 OPEN name(rec.org_id);
143 FETCH name INTO l_object_name;
144 CLOSE name;
145
146 -- get the start_date and end_date of the corresponding period
147 OPEN dates(rec.org_id);
148 FETCH dates INTO l_start_date, l_end_date;
149 CLOSE dates;
150
151 cn_mark_events_pkg.mark_event_interval_number('CHANGE_PERIOD_INTERVAL_NUMBER',
152 l_object_name,
153 rec.interval_type_id,
154 NULL,
155 l_start_date,
156 NULL,
157 l_end_date,
158 rec.interval_type_id,
159 rec.interval_number,
160 x_interval_number,
161 rec.org_id);
162 END IF;
163
164
165 END update_row;
166
167 PROCEDURE lock_row
168 ( x_cal_per_int_type_id cn_cal_per_int_types.cal_per_int_type_id%TYPE,
169 x_cal_period_id cn_cal_per_int_types.cal_period_id%TYPE,
170 x_interval_type_id cn_cal_per_int_types.interval_type_id%TYPE,
171 x_interval_number cn_cal_per_int_types.interval_number%TYPE
172 ) IS
173 CURSOR c IS
174 SELECT *
175 FROM cn_cal_per_int_types
176 WHERE cal_per_int_type_id = x_cal_per_int_type_id
177 FOR UPDATE OF cal_per_int_type_id nowait;
178
179 recinfo c%ROWTYPE;
180 BEGIN
181 OPEN c;
182 FETCH c INTO recinfo;
183
184 IF (c%notfound) THEN
185 CLOSE c;
186 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
187 app_exception.raise_exception;
188 END IF;
189 CLOSE c;
190
191 IF ((recinfo.cal_per_int_type_id = x_cal_per_int_type_id) AND
192 (recinfo.cal_period_id = x_cal_period_id) AND
193 (recinfo.interval_type_id = x_interval_type_id) AND
194 (recinfo.interval_number = x_interval_number))
195 THEN
196 RETURN;
197 ELSE
198 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
199 app_exception.raise_exception;
200 END IF;
201
202 END lock_row;
203
204 END CN_INT_ASSIGN_PKG;
205
206 /* show errors package CN_INT_ASSIGN_PKG
207
208 SELECT to_date('SQLEROR') FROM user_errors
209 WHERE name = 'CN_INT_ASSIGN_PKG'
210 AND type = 'PACKAGE'
211 */