[Home] [Help]
PACKAGE BODY: APPS.IGW_BUDGET_PERSONS_TBH
Source
1 PACKAGE BODY IGW_BUDGET_PERSONS_TBH AS
2 -- $Header: igwtbpsb.pls 115.6 2002/11/14 18:43:21 vmedikon ship $
3
4 procedure INSERT_ROW (
5 p_proposal_id NUMBER
6 ,p_version_id NUMBER
7 ,p_person_id NUMBER
8 ,p_party_id NUMBER
9 ,p_appointment_type_code VARCHAR2
10 ,p_effective_date DATE
11 ,p_calculation_base NUMBER
12 ,x_rowid OUT NOCOPY VARCHAR2
13 ,x_return_status OUT NOCOPY VARCHAR2) IS
14
15 cursor c_budgets is
16 select rowid
17 from igw_budget_persons
18 where proposal_id = p_proposal_id
19 and version_id = p_version_id
20 and party_id = p_party_id;
21
22 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
23 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
24 l_last_update_date DATE := SYSDATE;
25
26 begin
27 x_return_status := FND_API.G_RET_STS_SUCCESS;
28
29 insert into igw_budget_persons(
30 proposal_id
31 ,version_id
32 ,person_id
33 ,party_id
34 ,appointment_type_code
35 ,effective_date
36 ,calculation_base
37 ,last_update_date
38 ,last_updated_by
39 ,creation_date
40 ,created_by
41 ,last_update_login
42 ,record_version_number)
43 values
44 ( p_proposal_id
45 ,p_version_id
46 ,p_person_id
47 ,p_party_id
48 ,p_appointment_type_code
49 ,p_effective_date
50 ,p_calculation_base
51 ,l_last_update_date
52 ,l_last_updated_by
53 ,l_last_update_date
54 ,l_last_updated_by
55 ,l_last_update_login
56 ,1);
57
58 open c_budgets;
59 fetch c_budgets into x_ROWID;
60 if (c_budgets%notfound) then
61 close c_budgets;
62 raise no_data_found;
63 end if;
64 close c_budgets;
65
66 exception
67 when others then
68 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
69 ,p_procedure_name => 'INSERT_ROW' );
70 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
71 RAISE;
72 end insert_row;
73
74
75 -----------------------------------------------------------------------------------
76 procedure UPDATE_ROW (
77 p_proposal_id NUMBER
78 ,p_version_id NUMBER
79 ,p_person_id NUMBER
80 ,p_party_id NUMBER
81 ,p_appointment_type_code VARCHAR2
82 ,p_effective_date DATE
83 ,p_calculation_base NUMBER
84 ,p_rowid VARCHAR2
85 ,p_record_version_number NUMBER
86 ,x_return_status OUT NOCOPY VARCHAR2) IS
87
88 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
89 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
90 l_last_update_date DATE := SYSDATE;
91
92 l_row_id ROWID := p_rowid;
93
94 CURSOR get_row_id IS
95 select rowid
96 from igw_budget_persons
97 where proposal_id = p_proposal_id
98 and version_id = p_version_id
99 and party_id = p_party_id;
100
101 begin
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103
104 IF l_row_id IS NULL THEN
105 OPEN get_row_id;
106 FETCH get_row_id INTO l_row_id;
107 CLOSE get_row_id;
108 END IF;
109
110 update igw_budget_persons
111 set person_id = p_person_id
112 , party_id = p_party_id
113 , appointment_type_code = p_appointment_type_code
114 , effective_date = p_effective_date
115 , calculation_base = p_calculation_base
116 where rowid = l_row_id
117 and record_version_number = p_record_version_number;
118
119 if (sql%notfound) then
120 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
121 FND_MSG_PUB.Add;
122 X_RETURN_STATUS := 'E';
123 end if;
124
125 exception
126 when others then
127 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
128 ,p_procedure_name => 'UPDATE_ROW' );
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
130 RAISE;
131 end update_row;
132 -------------------------------------------------------------------------------------------
133
134 procedure DELETE_ROW (
135 p_rowid IN ROWID
136 ,p_proposal_id IN NUMBER
137 ,p_version_id IN NUMBER
138 ,p_person_id IN NUMBER
139 ,p_party_id NUMBER
140 ,p_record_version_number IN NUMBER
141 ,x_return_status OUT NOCOPY VARCHAR2) is
142
143 l_row_id ROWID := p_rowid;
144
145 CURSOR get_row_id IS
146 select rowid
147 from igw_budget_persons
148 where proposal_id = p_proposal_id
149 and version_id = p_version_id
150 and party_id = p_party_id;
151
152 begin
153
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 IF l_row_id IS NULL THEN
157 OPEN get_row_id;
158 FETCH get_row_id INTO l_row_id;
159 CLOSE get_row_id;
160 END IF;
161
162 delete from igw_budget_persons
163 where rowid = l_row_id
164 and record_version_number = p_record_version_number;
165
166 if (sql%notfound) then
167 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
168 FND_MSG_PUB.Add;
169 X_RETURN_STATUS := 'E';
170 end if;
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
175 ,p_procedure_name => 'DELETE_ROW' );
176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
177 RAISE;
178 end DELETE_ROW;
179
180
181 END IGW_BUDGET_PERSONS_TBH;