1 PACKAGE BODY PER_LETTER_REQUEST_LINES_PKG as
2 /* $Header: peltl01t.pkb 115.6 2003/02/21 07:13:34 vramanai ship $ */
3 --
4 PROCEDURE check_request_line_unique(X_letter_request_line_id in number,
5 X_assignment_id in number,
6 X_letter_request_id in number,
7 X_business_group_id in number,
8 X_ota_event_id in number,
9 X_ota_booking_id in number,
10 X_ota_booking_status_type_id in number)
11 is
12 --
13 cursor csr_line is select null
14 from per_letter_request_lines r
15 where (X_letter_request_line_id is null
16 or X_letter_request_line_id <> r.letter_request_line_id)
17 and r.assignment_id = X_assignment_id
18 and r.letter_request_id = X_letter_request_id
19 and r.business_group_id + 0 = X_business_group_id;
20 --
21 cursor csr_ota_line is
22 select null
23 from per_letter_request_lines r
24 where (X_letter_request_line_id is null
25 or X_letter_request_line_id <> r.letter_request_line_id)
26 and r.letter_request_id = X_letter_request_id
27 and ((X_ota_event_id = ota_event_id and
28 ota_booking_id is null and
29 x_ota_booking_id is null)
30 or (X_ota_booking_id = ota_booking_id
31 and X_ota_booking_status_type_id = ota_booking_status_type_id));
32
33 --
34 g_dummy_number number;
35 v_not_unique boolean := FALSE;
36 --
37 -- Check the request line is unique
38 --
39 begin
40 --
41 hr_utility.trace('CHK PROCEDURE');
42
43 if X_assignment_id is not null then
44 open csr_line;
45 fetch csr_line into g_dummy_number;
46 v_not_unique := csr_line%found;
47 close csr_line;
48 --
49 if v_not_unique then
50 hr_utility.set_message(801,'PER_7352_OUT_LETTER_PERSON');
51 hr_utility.raise_error;
52 end if;
53 --
54 end if;
55
56 if X_ota_event_id is not null or
57 X_ota_booking_id is not null then
58 open csr_ota_line;
59 fetch csr_ota_line into g_dummy_number;
60 v_not_unique := csr_ota_line%found;
61 close csr_ota_line;
62 --
63 if v_not_unique then
64 fnd_message.set_name('PER','PER_7352_OUT_LETTER_PERSON');
65 fnd_message.raise_error;
66 end if;
67 end if;
68 --
69 end check_request_line_unique;
70 --
71 PROCEDURE get_ota_details
72 (p_letter_request_line_id in number
73 ,p_event_title in out NOCOPY varchar2
74 ,p_delegate_full_name in out NOCOPY varchar2
75 ,p_course_start_date in out NOCOPY date
76 ,p_ota_booking_id in number
77 ,p_ota_event_id in number) is
78 --
79 l_event_title varchar2(80);
80 l_course_start_date date;
81 l_delegate_full_name varchar2(240);
82 source_cursor integer;
83 l_return integer;
84 --
85 begin
86 if p_ota_event_id is not null then
87 source_cursor := dbms_sql.open_cursor;
88 dbms_sql.parse(source_cursor,
89 'select title,course_start_date
90 from ota_events
91 where event_id = '||to_char(p_ota_event_id),
92 dbms_sql.v7);
93 dbms_sql.define_column(source_cursor,1,l_event_title,80);
94 dbms_sql.define_column(source_cursor,2,l_course_start_date);
95 l_return := dbms_sql.execute(source_cursor);
96 if dbms_sql.fetch_rows(source_cursor) >0 then
97 dbms_sql.column_value(source_cursor,1,l_event_title);
98 dbms_sql.column_value(source_cursor,2,l_course_start_date);
99 end if;
100 --
101 p_event_title := l_event_title;
102 p_course_start_date := l_course_start_date;
103 --
104 dbms_sql.close_cursor(source_cursor);
105 end if;
106 --
107 if p_ota_booking_id is not null then
108 source_cursor := dbms_sql.open_cursor;
109 dbms_sql.parse(source_cursor,
110 'select event_title
111 , course_start_date
112 , delegate_full_name
113 from ota_delegate_bookings_v
114 where booking_id = '||to_char(p_ota_booking_id),
115 dbms_sql.v7);
116 dbms_sql.define_column(source_cursor,1,l_event_title,80);
117 dbms_sql.define_column(source_cursor,2,l_course_start_date);
118 dbms_sql.define_column(source_cursor,3,l_delegate_full_name,240);
119 l_return := dbms_sql.execute(source_cursor);
120 if dbms_sql.fetch_rows(source_cursor) >0 then
121 dbms_sql.column_value(source_cursor,1,l_event_title);
122 dbms_sql.column_value(source_cursor,2,l_course_start_date);
123 dbms_sql.column_value(source_cursor,3,l_delegate_full_name);
124 end if;
125 --
126 p_event_title := l_event_title;
127 p_course_start_date := l_course_start_date;
128 p_delegate_full_name := l_delegate_full_name;
129 --
130 dbms_sql.close_cursor(source_cursor);
131 end if;
132 end;
133 --
134 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
135 X_Letter_Request_Line_Id IN OUT NOCOPY NUMBER,
136 X_Business_Group_Id NUMBER,
137 X_Letter_Request_Id NUMBER,
138 X_Person_Id NUMBER,
139 X_Assignment_Id NUMBER,
140 X_Assignment_Status_Type_Id NUMBER,
141 X_Date_From DATE,
142 X_OTA_BOOKING_STATUS_TYPE_ID number,
143 X_OTA_BOOKING_ID number,
144 X_OTA_EVENT_ID number,
145 X_CONTRACT_ID IN NUMBER DEFAULT NULL
146 ) IS
147 CURSOR C IS SELECT rowid FROM per_letter_request_lines
148 WHERE letter_request_line_id = X_Letter_Request_Line_Id;
149 --
150 CURSOR C2 IS SELECT per_letter_request_lines_s.nextval FROM sys.dual;
151 --
152 BEGIN
153 --
154 hr_utility.trace('Insert_Row');
155 if (X_Letter_Request_Line_Id is NULL) then
156 OPEN C2;
157 FETCH C2 INTO X_Letter_Request_Line_Id;
158 CLOSE C2;
159 end if;
160 --
161 INSERT INTO per_letter_request_lines(
162 letter_request_line_id,
163 business_group_id,
164 letter_request_id,
165 person_id,
166 assignment_id,
167 assignment_status_type_id,
168 date_from,
169 OTA_BOOKING_STATUS_TYPE_ID,
170 OTA_BOOKING_ID,
171 OTA_EVENT_ID,
172 CONTRACT_ID
173 ) VALUES (
174 X_Letter_Request_Line_Id,
175 X_Business_Group_Id,
176 X_Letter_Request_Id,
177 X_Person_Id,
178 X_Assignment_Id,
179 X_Assignment_Status_Type_Id,
180 X_Date_From,
181 X_OTA_BOOKING_STATUS_TYPE_ID,
182 X_OTA_BOOKING_ID,
183 X_OTA_EVENT_ID,
184 X_CONTRACT_ID
185 );
186 --
187 OPEN C;
188 FETCH C INTO X_Rowid;
189 if (C%NOTFOUND) then
190 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
191 hr_utility.set_message_token('PROCEDURE','Insert_Row');
192 hr_utility.set_message_token('STEP','1');
193 hr_utility.raise_error;
194 end if;
195 CLOSE C;
196 END Insert_Row;
197 --
198 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
199 X_Letter_Request_Line_Id NUMBER,
200 X_Business_Group_Id NUMBER,
201 X_Letter_Request_Id NUMBER,
202 X_Person_Id NUMBER,
203 X_Assignment_Id NUMBER,
204 X_Assignment_Status_Type_Id NUMBER,
205 X_Date_From DATE,
206 X_OTA_BOOKING_STATUS_TYPE_ID number,
207 X_OTA_BOOKING_ID number,
208 X_OTA_EVENT_ID number
209 ) IS
210 CURSOR C IS
211 SELECT *
212 FROM per_letter_request_lines
213 WHERE rowid = X_Rowid
214 FOR UPDATE of letter_request_line_id NOWAIT;
215 Recinfo C%ROWTYPE;
216 BEGIN
217 --
218 OPEN C;
219 FETCH C INTO Recinfo;
220 if (C%NOTFOUND) then
221 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
222 hr_utility.set_message_token('PROCEDURE','Lock_Row');
223 hr_utility.set_message_token('STEP','1');
224 hr_utility.raise_error;
225 end if;
226 CLOSE C;
227 if (
228 ( (Recinfo.letter_request_line_id = X_Letter_Request_Line_Id)
229 OR ( (Recinfo.letter_request_line_id IS NULL)
230 AND (X_Letter_Request_Line_Id IS NULL)))
231 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
232 OR ( (Recinfo.business_group_id IS NULL)
233 AND (X_Business_Group_Id IS NULL)))
234 AND ( (Recinfo.letter_request_id = X_Letter_Request_Id)
235 OR ( (Recinfo.letter_request_id IS NULL)
236 AND (X_Letter_Request_Id IS NULL)))
237 AND ( (Recinfo.person_id = X_Person_Id)
238 OR ( (Recinfo.person_id IS NULL)
239 AND (X_Person_Id IS NULL)))
240 AND ( (Recinfo.assignment_id = X_Assignment_Id)
241 OR ( (Recinfo.assignment_id IS NULL)
242 AND (X_Assignment_Id IS NULL)))
243 AND ( (Recinfo.assignment_status_type_id = X_Assignment_Status_Type_Id)
244 OR ( (Recinfo.assignment_status_type_id IS NULL)
245 AND (X_Assignment_Status_Type_Id IS NULL)))
246 AND ( (Recinfo.date_from = X_Date_From)
247 OR ( (Recinfo.date_from IS NULL)
248 AND (X_Date_From IS NULL)))
249 AND ( (Recinfo.ota_booking_id =
250 X_ota_booking_id)
251 OR ( (Recinfo.ota_booking_id IS NULL)
252 AND (X_ota_booking_id IS NULL)))
253 AND ( (Recinfo.ota_event_id =
254 X_ota_event_id)
255 OR ( (Recinfo.ota_event_id IS NULL)
256 AND (X_ota_event_id IS NULL)))
257 AND ( (Recinfo.ota_booking_status_type_id =
258 X_ota_booking_status_type_id)
259 OR ( (Recinfo.ota_booking_status_type_id IS NULL)
260 AND (X_ota_booking_status_type_id IS NULL)))
261 ) then
262 return;
263 else
264 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
265 APP_EXCEPTION.RAISE_EXCEPTION;
266 end if;
267 END Lock_Row;
268 --
269 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
270 BEGIN
271 DELETE FROM per_letter_request_lines
272 WHERE rowid = X_Rowid;
273 --
274 if (SQL%NOTFOUND) then
275 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
276 hr_utility.set_message_token('PROCEDURE','Delete_Row');
277 hr_utility.set_message_token('STEP','1');
278 hr_utility.raise_error;
279 end if;
280 END Delete_Row;
281 --
282 --
283 END PER_LETTER_REQUEST_LINES_PKG;
[Home] [Help]