[Home] [Help]
PACKAGE BODY: APPS.OKE_COMMUNICATION_PKG
Source
1 package body OKE_COMMUNICATION_PKG as
2 /* $Header: OKECMPLB.pls 115.10 2002/11/21 22:44:59 ybchen ship $ */
3
4
5 PROCEDURE Lock_Row(
6 X_k_header_id NUMBER,
7 X_communication_num VARCHAR2,
8 X_communication_date DATE,
9 X_type VARCHAR2,
10 X_reason_code VARCHAR2,
11 X_party_location VARCHAR2,
12 X_party_role VARCHAR2,
13 X_party_contact VARCHAR2,
14 X_action_code VARCHAR2,
15 X_priority_code VARCHAR2,
16 X_owner NUMBER,
17 X_k_party_id NUMBER,
18 X_wf_item_type VARCHAR2,
19 X_wf_process VARCHAR2,
20 X_wf_item_key VARCHAR2,
21 X_text LONG,
22 X_funding_ref1 VARCHAR2,
23 X_funding_ref2 VARCHAR2,
24 X_funding_ref3 VARCHAR2,
25 X_funding_source_id NUMBER,
26 X_k_line_id NUMBER,
27 X_deliverable_id NUMBER,
28 X_chg_request_id NUMBER,
29 X_project_id NUMBER,
30 X_task_id NUMBER,
31 X_Attribute_Category VARCHAR2,
32 X_Attribute1 VARCHAR2,
33 X_Attribute2 VARCHAR2,
34 X_Attribute3 VARCHAR2,
35 X_Attribute4 VARCHAR2,
36 X_Attribute5 VARCHAR2,
37 X_Attribute6 VARCHAR2,
38 X_Attribute7 VARCHAR2,
39 X_Attribute8 VARCHAR2,
40 X_Attribute9 VARCHAR2,
41 X_Attribute10 VARCHAR2,
42 X_Attribute11 VARCHAR2,
43 X_Attribute12 VARCHAR2,
44 X_Attribute13 VARCHAR2,
45 X_Attribute14 VARCHAR2,
46 X_Attribute15 VARCHAR2
47 ) is
48
49 cursor c is
50 select k_header_id,
51 communication_num,
52 communication_date,
53 type,
54 reason_code,
55 party_location,
56 party_role,
57 party_contact,
58 action_code,
59 priority_code,
60 owner,
61 k_party_id,
62 wf_item_type,
63 wf_process,
64 wf_item_key,
65 text,
66 funding_ref1,
67 funding_ref2,
68 funding_ref3,
69 funding_source_id,
70 chg_request_id,
71 k_line_id,
72 deliverable_id,
73 project_id,
74 task_id,
75 attribute_category,
76 attribute1,
77 attribute2,
78 attribute3,
79 attribute4,
80 attribute5,
81 attribute6,
82 attribute7,
83 attribute8,
84 attribute9,
85 attribute10,
86 attribute11,
87 attribute12,
88 attribute13,
89 attribute14,
90 attribute15
91 from OKE_K_COMMUNICATIONS
92 where k_header_id = X_k_header_id
93 and communication_num = X_communication_num
94 for update of k_header_id, communication_num nowait;
95
96 recinfo c%rowtype;
97
98 begin
99
100 open c;
101 fetch c into recinfo;
102 if (c%notfound) then
103 close c;
104 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
105 app_exception.raise_exception;
106 end if;
107 close c;
108
109 if (
110 ((rtrim(recinfo.type) = rtrim(X_type))
111 OR ((recinfo.type is null) AND (X_type is null)))
112 AND ((recinfo.communication_date = X_communication_date)
113 OR ((recinfo.communication_date is null) AND (X_communication_date is null)))
114 AND ((rtrim(recinfo.reason_code) = rtrim(X_reason_code))
115 OR ((recinfo.reason_code is null) AND (X_reason_code is null)))
116 AND ((rtrim(recinfo.party_location) = rtrim(X_party_location))
117 OR ((recinfo.party_location is null) AND (X_party_location is null)))
118 AND ((rtrim(recinfo.party_role) = rtrim(X_party_role))
119 OR ((recinfo.party_role is null) AND (X_party_role is null)))
120 AND ((rtrim(recinfo.party_contact) = rtrim(X_party_contact))
121 OR ((recinfo.party_contact is null) AND (X_party_contact is null)))
122 AND ((rtrim(recinfo.action_code) = rtrim(X_action_code))
123 OR ((recinfo.action_code is null) AND (X_action_code is null)))
124 AND ((rtrim(recinfo.priority_code) = rtrim(X_priority_code))
125 OR ((recinfo.priority_code is null) AND (X_priority_code is null)))
126 AND ((rtrim(recinfo.wf_item_type) = rtrim(X_wf_item_type))
127 OR ((recinfo.wf_item_type is null) AND (X_wf_item_type is null)))
128 AND ((rtrim(recinfo.wf_process) = rtrim(X_wf_process))
129 OR ((recinfo.wf_process is null) AND (X_wf_process is null)))
130 AND ((rtrim(recinfo.wf_item_key) = rtrim(X_wf_item_key))
131 OR ((recinfo.wf_item_key is null) AND (X_wf_item_key is null)))
132 AND ((rtrim(recinfo.text) = rtrim(X_text))
133 OR ((recinfo.text is null) AND (X_text is null)))
134 AND ((rtrim(recinfo.funding_ref1) = rtrim(X_funding_ref1))
135 OR ((recinfo.funding_ref1 is null) AND (X_funding_ref1 is null)))
136 AND ((rtrim(recinfo.funding_ref2) = rtrim(X_funding_ref2))
137 OR ((recinfo.funding_ref2 is null) AND (X_funding_ref2 is null)))
138 AND ((rtrim(recinfo.funding_ref3) = rtrim(X_funding_ref3))
139 OR ((recinfo.funding_ref3 is null) AND (X_funding_ref3 is null)))
140 AND ((rtrim(recinfo.funding_source_id) = rtrim(X_funding_source_id))
141 OR ((recinfo.funding_source_id is null) AND (X_funding_source_id is null)))
142 AND ((rtrim(recinfo.chg_request_id ) = rtrim(X_chg_request_id ))
143 OR ((recinfo.chg_request_id is null) AND (X_chg_request_id is null)))
144 AND ((rtrim(recinfo.k_line_id ) = rtrim(X_k_line_id ))
145 OR ((recinfo.k_line_id is null) AND (X_k_line_id is null)))
146 AND ((rtrim(recinfo.deliverable_id ) = rtrim(X_deliverable_id ))
147 OR ((recinfo.deliverable_id is null) AND (X_deliverable_id is null)))
148 AND ((rtrim(recinfo.project_id ) = rtrim(X_project_id ))
149 OR ((recinfo.project_id is null) AND (X_project_id is null)))
150 AND ((rtrim(recinfo.task_id ) = rtrim(X_task_id ))
151 OR ((recinfo.task_id is null) AND (X_task_id is null)))
152 AND ((rtrim(recinfo.attribute_category) = rtrim(X_Attribute_Category))
153 OR ((recinfo.attribute_category is null) AND (X_Attribute_Category is null)))
154 AND ((rtrim(recinfo.attribute1) = rtrim(X_Attribute1))
155 OR ((recinfo.attribute1 is null) AND (X_Attribute1 is null)))
156 AND ((rtrim(recinfo.attribute2) = rtrim(X_Attribute2))
157 OR ((recinfo.attribute2 is null) AND (X_Attribute2 is null)))
158 AND ((rtrim(recinfo.attribute3) = rtrim(X_Attribute3))
159 OR ((recinfo.attribute3 is null) AND (X_Attribute3 is null)))
160 AND ((rtrim(recinfo.attribute4) = rtrim(X_Attribute4))
161 OR ((recinfo.attribute4 is null) AND (X_Attribute4 is null)))
162 AND ((rtrim(recinfo.attribute5) = rtrim(X_Attribute5))
163 OR ((recinfo.attribute5 is null) AND (X_Attribute5 is null)))
164 AND ((rtrim(recinfo.attribute6) = rtrim(X_Attribute6))
165 OR ((recinfo.attribute6 is null) AND (X_Attribute6 is null)))
166 AND ((rtrim(recinfo.attribute7) = rtrim(X_Attribute7))
167 OR ((recinfo.attribute7 is null) AND (X_Attribute7 is null)))
168 AND ((rtrim(recinfo.attribute8) = rtrim(X_Attribute8))
169 OR ((recinfo.attribute8 is null) AND (X_Attribute8 is null)))
170 AND ((rtrim(recinfo.attribute9) = rtrim(X_Attribute9))
171 OR ((recinfo.attribute9 is null) AND (X_Attribute9 is null)))
172 AND ((rtrim(recinfo.attribute10) = rtrim(X_Attribute10))
173 OR ((recinfo.attribute10 is null) AND (X_Attribute10 is null)))
174 AND ((rtrim(recinfo.attribute11) = rtrim(X_Attribute11))
175 OR ((recinfo.attribute11 is null) AND (X_Attribute11 is null)))
176 AND ((rtrim(recinfo.attribute12) = rtrim(X_Attribute12))
177 OR ((recinfo.attribute12 is null) AND (X_Attribute12 is null)))
178 AND ((rtrim(recinfo.attribute13) = rtrim(X_Attribute13))
179 OR ((recinfo.attribute13 is null) AND (X_Attribute13 is null)))
180 AND ((rtrim(recinfo.attribute14) = rtrim(X_Attribute14))
181 OR ((recinfo.attribute14 is null) AND (X_Attribute14 is null)))
182 AND ((rtrim(recinfo.attribute15) = rtrim(X_Attribute15))
183 OR ((recinfo.attribute15 is null) AND (X_Attribute15 is null)))
184 ) then
185 null;
186 else
187 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
188 app_exception.raise_exception;
189 end if;
190
191 end Lock_Row;
192
193
194 PROCEDURE Update_Row(
195 X_k_header_id NUMBER,
196 X_communication_num VARCHAR2,
197 X_communication_date DATE,
198 X_type VARCHAR2,
199 X_reason_code VARCHAR2,
200 X_party_location VARCHAR2,
201 X_party_role VARCHAR2,
202 X_party_contact VARCHAR2,
203 X_action_code VARCHAR2,
204 X_priority_code VARCHAR2,
205 X_owner NUMBER,
206 X_k_party_id NUMBER,
207 X_wf_item_type IN OUT NOCOPY VARCHAR2,
208 X_wf_process IN OUT NOCOPY VARCHAR2,
209 X_wf_item_key IN OUT NOCOPY VARCHAR2,
210 X_text LONG,
211 X_funding_ref1 VARCHAR2,
212 X_funding_ref2 VARCHAR2,
213 X_funding_ref3 VARCHAR2,
214 X_funding_source_id NUMBER,
215 X_k_line_id NUMBER,
216 X_deliverable_id NUMBER,
217 X_chg_request_id NUMBER,
218 X_project_id NUMBER,
219 X_task_id NUMBER,
220 X_Last_Update_Date DATE,
221 X_Last_Updated_By NUMBER,
222 X_Last_Update_Login NUMBER,
223 X_Attribute_Category VARCHAR2,
224 X_Attribute1 VARCHAR2,
225 X_Attribute2 VARCHAR2,
226 X_Attribute3 VARCHAR2,
227 X_Attribute4 VARCHAR2,
228 X_Attribute5 VARCHAR2,
229 X_Attribute6 VARCHAR2,
230 X_Attribute7 VARCHAR2,
231 X_Attribute8 VARCHAR2,
232 X_Attribute9 VARCHAR2,
233 X_Attribute10 VARCHAR2,
234 X_Attribute11 VARCHAR2,
235 X_Attribute12 VARCHAR2,
236 X_Attribute13 VARCHAR2,
237 X_Attribute14 VARCHAR2,
238 X_Attribute15 VARCHAR2
239
240 ) is
241
242 x_prev_action_code OKE_K_COMMUNICATIONS.action_code%TYPE;
243
244 cursor C is
245 select wf_item_type
246 , wf_process
247 , wf_item_key
248 from OKE_K_COMMUNICATIONS
249 where k_header_id = X_k_header_id
250 and communication_num = X_communication_num;
251
252 cursor C_prev is
253 select action_code
254 from OKE_K_COMMUNICATIONS
255 where k_header_id = X_k_header_id
256 and communication_num = X_communication_num;
257
258 begin
259 open c_prev;
260 fetch c_prev into X_prev_action_code;
261 close c_prev;
262
263
264 update OKE_K_COMMUNICATIONS
265 set
266 communication_date = X_communication_date,
267 type = X_type,
268 reason_code = X_reason_code,
269 party_location = X_party_location,
270 party_role = X_party_role,
271 party_contact = X_party_contact,
272 action_code = X_action_code,
273 priority_code = X_priority_code,
274 owner = X_owner,
275 k_party_id = X_k_party_id,
276 wf_item_type = X_wf_item_type,
277 wf_process = X_wf_process,
278 wf_item_key = X_wf_item_key,
279 text = X_text,
280 funding_ref1 = X_funding_ref1,
281 funding_ref2 = X_funding_ref2,
282 funding_ref3 = X_funding_ref3,
283 funding_source_id = X_funding_source_id,
284 k_line_id = X_k_line_id,
285 deliverable_id = X_deliverable_id,
286 chg_request_id = X_chg_request_id,
287 project_id = X_project_id,
288 task_id = X_task_id,
289 Last_Update_Date = X_Last_Update_Date,
290 Last_Updated_By = X_Last_Updated_By,
291 Last_Update_Login = X_Last_Update_Login,
292 attribute_Category = X_Attribute_Category,
293 attribute1 = X_Attribute1,
294 attribute2 = X_Attribute2,
295 attribute3 = X_Attribute3,
296 attribute4 = X_Attribute4,
297 attribute5 = X_Attribute5,
298 attribute6 = X_Attribute6,
299 attribute7 = X_Attribute7,
300 attribute8 = X_Attribute8,
301 attribute9 = X_Attribute9,
302 attribute10 = X_Attribute10,
303 attribute11 = X_Attribute11,
304 attribute12 = X_Attribute12,
305 attribute13 = X_Attribute13,
306 attribute14 = X_Attribute14,
307 attribute15 = X_Attribute15
308 where k_header_id = X_k_header_id
309 and communication_num = X_communication_num;
310
311 if NVL(X_prev_action_code, '@' ) <> NVL(X_Action_Code, '@' ) then
312 OKE_COMM_ACT_UTILS.Comm_Action
313 ( X_K_Header_ID
314 , X_K_Line_ID
315 , X_Deliverable_ID
316 , X_Communication_Num
317 , X_Type
318 , X_Reason_Code
319 , X_K_Party_ID
320 , X_Party_Location
321 , X_Party_Role
322 , X_Party_Contact
323 , X_Action_Code
324 , X_Owner
325 , X_Priority_Code
326 , X_Communication_Date
327 , X_Text
328 , X_Last_Updated_By
329 , X_Last_Update_Date
330 , X_Last_Update_Login
331 , X_WF_ITEM_KEY
332 );
333 end if;
334
335 if (sql%notfound) then
336 raise no_data_found;
337 end if;
338
339 open c;
340 fetch c into X_WF_Item_Type , X_WF_Process , X_WF_Item_Key;
341 close c;
342
343 end Update_Row;
344
345 PROCEDURE Insert_Row(
346 X_Rowid IN OUT NOCOPY VARCHAR2,
347 X_k_header_id NUMBER,
348 X_communication_num VARCHAR2,
349 X_communication_date DATE,
350 X_type VARCHAR2,
351 X_reason_code VARCHAR2,
352 X_party_location VARCHAR2,
353 X_party_role VARCHAR2,
354 X_party_contact VARCHAR2,
355 X_action_code VARCHAR2,
359 X_wf_item_type IN OUT NOCOPY VARCHAR2,
356 X_priority_code VARCHAR2,
357 X_owner NUMBER,
358 X_k_party_id NUMBER,
360 X_wf_process IN OUT NOCOPY VARCHAR2,
361 X_wf_item_key IN OUT NOCOPY VARCHAR2,
362 X_text LONG,
363 X_funding_ref1 VARCHAR2,
364 X_funding_ref2 VARCHAR2,
365 X_funding_ref3 VARCHAR2,
366 X_funding_source_id NUMBER,
367 X_k_line_id NUMBER,
368 X_deliverable_id NUMBER,
369 X_chg_request_id NUMBER,
370 X_project_id NUMBER,
371 X_task_id NUMBER,
372 X_Last_Update_Date DATE,
373 X_Last_Updated_By NUMBER,
374 X_Creation_Date DATE,
375 X_Created_By NUMBER,
376 X_Last_Update_Login NUMBER,
377 X_Attribute_Category VARCHAR2,
378 X_Attribute1 VARCHAR2,
379 X_Attribute2 VARCHAR2,
380 X_Attribute3 VARCHAR2,
381 X_Attribute4 VARCHAR2,
382 X_Attribute5 VARCHAR2,
383 X_Attribute6 VARCHAR2,
384 X_Attribute7 VARCHAR2,
385 X_Attribute8 VARCHAR2,
386 X_Attribute9 VARCHAR2,
387 X_Attribute10 VARCHAR2,
388 X_Attribute11 VARCHAR2,
389 X_Attribute12 VARCHAR2,
390 X_Attribute13 VARCHAR2,
391 X_Attribute14 VARCHAR2,
392 X_Attribute15 VARCHAR2
393 ) is
394
395 cursor C is
396 select rowid
397 , wf_item_type
398 , wf_process
399 , wf_item_key
400 from OKE_K_COMMUNICATIONS
401 where k_header_id = X_k_header_id
402 and communication_num = X_communication_num;
403
404 begin
405
406 insert into OKE_K_COMMUNICATIONS(
407 k_header_id,
408 communication_num,
409 communication_date,
410 type,
411 reason_code,
412 party_location,
413 party_role,
414 party_contact,
415 action_code,
416 priority_code,
417 owner,
418 k_party_id,
419 wf_item_type,
420 wf_process,
421 wf_item_key,
422 text,
423 funding_ref1,
424 funding_ref2,
425 funding_ref3,
426 funding_source_id,
427 k_line_id,
428 deliverable_id,
429 chg_request_id,
430 project_id,
431 task_id,
432 Last_Update_Date,
433 Last_Updated_By,
434 Creation_Date,
435 Created_By,
436 Last_Update_Login,
437 Attribute_Category,
438 Attribute1,
439 Attribute2,
440 Attribute3,
441 Attribute4,
442 Attribute5,
443 Attribute6,
444 Attribute7,
445 Attribute8,
446 Attribute9,
447 Attribute10,
448 Attribute11,
449 Attribute12,
450 Attribute13,
451 Attribute14,
452 Attribute15
453 ) VALUES (
454 X_k_header_id,
455 X_communication_num,
456 X_communication_date,
457 X_type,
458 X_reason_code,
459 X_party_location,
460 X_party_role,
461 X_party_contact,
462 X_action_code,
463 X_priority_code,
464 X_owner,
465 X_k_party_id,
466 X_wf_item_type,
467 X_wf_process,
468 X_wf_item_key,
469 X_text,
470 X_funding_ref1,
471 X_funding_ref2,
472 X_funding_ref3,
473 X_funding_source_id,
474 X_k_line_id,
475 X_deliverable_id,
476 X_chg_request_id,
477 X_project_id,
478 X_task_id,
479 X_Last_Update_Date,
480 X_Last_Updated_By,
481 X_Creation_Date,
482 X_Created_By,
483 X_Last_Update_Login,
484 X_Attribute_Category,
485 X_Attribute1,
486 X_Attribute2,
487 X_Attribute3,
488 X_Attribute4,
489 X_Attribute5,
490 X_Attribute6,
491 X_Attribute7,
492 X_Attribute8,
493 X_Attribute9,
494 X_Attribute10,
495 X_Attribute11,
496 X_Attribute12,
497 X_Attribute13,
498 X_Attribute14,
499 X_Attribute15
500 );
501
502 OKE_COMM_ACT_UTILS.Comm_Action
503 ( X_K_Header_ID
504 , X_K_Line_ID
505 , X_Deliverable_ID
506 , X_Communication_Num
507 , X_Type
508 , X_Reason_Code
509 , X_K_Party_ID
510 , X_Party_Location
511 , X_Party_Role
512 , X_Party_Contact
513 , X_Action_Code
514 , X_Owner
515 , X_Priority_Code
516 , X_Communication_Date
517 , X_Text
518 , X_Last_Updated_By
519 , X_Last_Update_Date
520 , X_Last_Update_Login
521 , X_WF_ITEM_KEY
522 );
523
524 open c;
525 fetch c into X_Rowid , X_WF_Item_Type , X_WF_Process , X_WF_Item_Key;
526 if (c%notfound) then
527 close c;
528 raise no_data_found;
529 end if;
530 close c;
531
532 end Insert_Row;
533
534
535 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
536 BEGIN
537
538 DELETE FROM OKE_K_COMMUNICATIONS
539 WHERE rowid = X_Rowid;
540
541 if (SQL%NOTFOUND) then
542 Raise NO_DATA_FOUND;
543 end if;
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 raise;
548
549 END Delete_Row;
550
551
552 end OKE_COMMUNICATION_PKG;