1 PACKAGE BODY SSPWSMED_PKG as
2 /* $Header: sspwsmed.pkb 115.2 99/07/16 23:04:08 porting ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20
21 Name : sspwsmed_pkg
22
23 Description : This package is the server side agent for
24 SSP/SMP R10 form sspwsmed
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ---- ---- ---- ------ -----------
30 22-AUG-1995 ssethi 40.0 Initial Creation
31 13-SEP-1995 ssethi Procedures added to
32 update evidence_status to
33 SUPERCEDED from CURRENT.
34
35 */
36
37 PROCEDURE get_medical_sequence (p_medical_id in out number) is
38 cursor c1 is
39 select ssp_medicals_s.nextval
40 from sys.dual;
41 BEGIN
42 open c1;
43 fetch c1 into p_medical_id;
44 close c1;
45 END get_medical_sequence;
46
47 PROCEDURE check_unique_mat_evidence
48 (p_evidence_source in varchar2,
49 p_evidence_date in date,
50 p_maternity_id in number,
51 p_medical_id in number) is
52 cursor c1 is
53 select h.rowid
54 from ssp_medicals h
55 where h.maternity_id = p_maternity_id
56 and h.medical_id <> p_medical_id
57 and upper(h.evidence_source) = p_evidence_source
58 and h.evidence_date = p_evidence_date;
59 c1_rec c1%ROWTYPE;
60 BEGIN
61 open c1;
62 fetch c1 into c1_rec;
63 if c1%FOUND then
64 fnd_message.set_name ('SSP','SSP_35020_NON_UNQ_MED_EVID');
65 fnd_message.raise_error;
66 app_exception.raise_exception;
67 end if;
68 close c1;
69 END check_unique_mat_evidence;
70
71
72 PROCEDURE check_unique_abs_evidence
73 (p_evidence_source in varchar2,
74 p_evidence_date in date,
75 p_absence_attendance_id in number,
76 p_medical_id in number) is
77 cursor c1 is
78 select h.evidence_source, h.evidence_date
79 from ssp_medicals h
80 where h.absence_attendance_id = p_absence_attendance_id
81 and h.medical_id <> p_medical_id
82 and upper(h.evidence_source) = p_evidence_source
83 and h.evidence_date = p_evidence_date;
84 c1_rec c1%ROWTYPE;
85 BEGIN
86 open c1;
87 fetch c1 into c1_rec;
88 if c1%FOUND then
89 fnd_message.set_name ('SSP', 'SSP_35020_NON_UNQ_MED_EVID');
90 fnd_message.raise_error;
91 app_exception.raise_exception;
92 end if;
93 close c1;
94 END check_unique_abs_evidence;
95
96 PROCEDURE upd_prev_sick_evid (p_absence_attendance_id in number) is
97 BEGIN
98 update ssp_medicals
99 set evidence_status='SUPERCEDED'
100 where absence_attendance_id = p_absence_attendance_id
101 and evidence_status = 'CURRENT';
102 END upd_prev_sick_evid;
103
104 PROCEDURE upd_prev_mat_evid (p_maternity_id in number) is
105 BEGIN
106 update ssp_medicals
107 set evidence_status='SUPERCEDED'
108 where maternity_id = p_maternity_id
109 and evidence_status = 'CURRENT';
110 END upd_prev_mat_evid;
111
112 END SSPWSMED_PKG;
[Home] [Help]