[Home] [Help]
PACKAGE BODY: APPS.IGW_STUDY_TITLES_TBH
Source
1 package body IGW_STUDY_TITLES_TBH as
2 /* $Header: igwtsttb.pls 115.5 2002/11/15 00:50:01 ashkumar ship $ */
3 procedure INSERT_ROW (
4 X_ROWID out NOCOPY rowid,
5 X_STUDY_TITLE_ID in NUMBER,
6 X_STUDY_TITLE in VARCHAR2,
7 X_ENROLLMENT_STATUS in VARCHAR2,
8 X_PROTOCOL_NUMBER in VARCHAR2,
9 X_PROPOSAL_ID in NUMBER,
10 X_RETURN_STATUS OUT NOCOPY VARCHAR2
11 ) is
12 cursor C is select ROWID from IGW_STUDY_TITLES
13 where STUDY_TITLE_ID = X_STUDY_TITLE_ID;
14 begin
15 x_return_status := FND_API.G_RET_STS_SUCCESS;
16 insert into IGW_STUDY_TITLES (
17 STUDY_TITLE_ID,
18 STUDY_TITLE,
19 ENROLLMENT_STATUS,
20 PROTOCOL_NUMBER,
21 PROPOSAL_ID,
22 RECORD_VERSION_NUMBER)
23 values (
24 X_STUDY_TITLE_ID,
25 X_STUDY_TITLE,
26 X_ENROLLMENT_STATUS,
27 X_PROTOCOL_NUMBER,
28 X_PROPOSAL_ID,
29 1
30 );
31
32 open c;
33 fetch c into X_ROWID;
34 if (c%notfound) then
35 close c;
36 raise no_data_found;
37 end if;
38 close c;
39
40 EXCEPTION
41 WHEN OTHERS THEN
42 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'IGW_STUDY_TITLES_TBH'
43 ,p_procedure_name => 'INSERT_ROW' );
44 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
45 RAISE;
46
47 end INSERT_ROW;
48
49 -----------------------------------------------------------------------
50
51 procedure UPDATE_ROW (
52 X_ROWID IN ROWID,
53 X_STUDY_TITLE_ID in NUMBER,
54 X_STUDY_TITLE in VARCHAR2,
55 X_ENROLLMENT_STATUS in VARCHAR2,
56 X_PROTOCOL_NUMBER in VARCHAR2,
57 X_PROPOSAL_ID in NUMBER,
58 X_RECORD_VERSION_NUMBER IN NUMBER,
59 X_RETURN_STATUS OUT NOCOPY VARCHAR2
60 ) is
61
62 l_row_id ROWID := x_rowid;
63 l_record_version_number NUMBER;
64
65 CURSOR get_row_id IS
66 SELECT rowid
67 FROM igw_study_titles
68 WHERE study_title_id = x_study_title_id;
69
70 begin
71
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 IF l_row_id IS NULL THEN
75 OPEN get_row_id;
76 FETCH get_row_id INTO l_row_id;
77 CLOSE get_row_id;
78 END IF;
79
80 update igw_study_titles set
81 study_title_id = x_study_title_id,
82 study_title = x_study_title,
83 enrollment_status = x_enrollment_status,
84 protocol_number = x_protocol_number,
85 proposal_id = x_proposal_id,
86 record_version_number = x_record_version_number +1
87 where rowid = x_rowid
88 and record_version_number = x_record_version_number;
89
90 if (sql%notfound) then
91 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
92 FND_MSG_PUB.Add;
93 x_return_status := FND_API.G_RET_STS_ERROR;
94 end if;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'IGW_STUDY_TITLES_TBH'
99 ,p_procedure_name => 'UPDATE_ROW' );
100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
101 RAISE;
102
103 end UPDATE_ROW;
104
105 ---------------------------------------------------------------------------------
106
107 procedure DELETE_ROW (
108 x_rowid in rowid
109 ,x_study_title_id in number
110 ,x_record_version_number in number
111 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
112 ) is
113
114 l_row_id ROWID := x_rowid;
115 l_record_version_number NUMBER;
116
117 CURSOR get_row_id IS
118 SELECT rowid
119 FROM igw_study_titles
120 WHERE study_title_id = x_study_title_id;
121
122 begin
123
124 x_return_status := FND_API.G_RET_STS_SUCCESS;
125
126 IF l_row_id IS NULL THEN
127 OPEN get_row_id;
128 FETCH get_row_id INTO l_row_id;
129 CLOSE get_row_id;
130 END IF;
131
132 delete from igw_study_titles
133 where rowid = l_row_id
134 and record_version_number = x_record_version_number;
135
136 if (sql%notfound) then
137 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
138 FND_MSG_PUB.Add;
139 end if;
140 EXCEPTION
141 WHEN OTHERS THEN
142 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'IGW_STUDY_TITLES_TBH'
143 ,p_procedure_name => 'DELETE_ROW' );
144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
145 RAISE;
146 end DELETE_ROW;
147
148 end IGW_STUDY_TITLES_TBH;