[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_LOCATIONS_TBH
Source
1 package body IGW_PROP_LOCATIONS_TBH as
2 --$Header: igwtplcb.pls 115.6 2002/11/15 00:37:45 ashkumar ship $
3 procedure INSERT_ROW (
4 X_ROWID out NOCOPY ROWID,
5 X_PROPOSAL_ID in NUMBER,
6 X_PERFORMING_ORGANIZATION_ID in NUMBER,
7 X_PARTY_ID in NUMBER,
8 X_MODE in VARCHAR2 default 'R',
9 X_RETURN_STATUS OUT NOCOPY VARCHAR2
10 ) is
11
12 cursor C is select ROWID from IGW_PROP_LOCATIONS
13 where PROPOSAL_ID = X_PROPOSAL_ID
14 and (PERFORMING_ORGANIZATION_ID = X_PERFORMING_ORGANIZATION_ID or PARTY_ID = X_PARTY_ID);
15
16 X_LAST_UPDATE_DATE DATE;
17 X_LAST_UPDATED_BY NUMBER;
18 X_LAST_UPDATE_LOGIN NUMBER;
19 begin
20 x_return_status := FND_API.G_RET_STS_SUCCESS;
21
22 X_LAST_UPDATE_DATE := SYSDATE;
23 if(X_MODE = 'I') then
24 X_LAST_UPDATED_BY := 1;
25 X_LAST_UPDATE_LOGIN := 0;
26 elsif (X_MODE = 'R') then
27 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
28 if X_LAST_UPDATED_BY is NULL then
29 X_LAST_UPDATED_BY := -1;
30 end if;
31 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
32 if X_LAST_UPDATE_LOGIN is NULL then
33 X_LAST_UPDATE_LOGIN := -1;
34 end if;
35 else
36 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
37 app_exception.raise_exception;
38 end if;
39
40 insert into IGW_PROP_LOCATIONS (
41 PROP_LOCATION_ID,
42 PROPOSAL_ID,
43 PERFORMING_ORGANIZATION_ID,
44 PARTY_ID,
45 RECORD_VERSION_NUMBER,
46 CREATION_DATE,
47 CREATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_LOGIN
51 ) values (
52 IGW_PROP_LOCATIONS_S.NEXTVAL,
53 X_PROPOSAL_ID,
54 X_PERFORMING_ORGANIZATION_ID,
55 X_PARTY_ID,
56 1,
57 X_LAST_UPDATE_DATE,
58 X_LAST_UPDATED_BY,
59 X_LAST_UPDATE_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_LOGIN
62 );
63
64 open c;
65 fetch c into X_ROWID;
66 if (c%notfound) then
67 close c;
68 raise no_data_found;
69 end if;
70 close c;
71 EXCEPTION
72 WHEN OTHERS THEN
73 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'IGW_PROP_LOCATIONS_TBH'
74 ,p_procedure_name => 'INSERT_ROW' );
75 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
76 RAISE;
77 end INSERT_ROW;
78 -----------------------------------------------------------------------------
79
80 procedure UPDATE_ROW (
81 X_ROWID in ROWID,
82 X_PROPOSAL_ID in NUMBER,
83 X_PERFORMING_ORGANIZATION_ID in NUMBER,
84 X_PARTY_ID in NUMBER,
85 X_RECORD_VERSION_NUMBER in number,
86 X_MODE in VARCHAR2 default 'R',
87 X_RETURN_STATUS OUT NOCOPY VARCHAR2
88 ) is
89 X_LAST_UPDATE_DATE DATE;
90 X_LAST_UPDATED_BY NUMBER;
91 X_LAST_UPDATE_LOGIN NUMBER;
92
93 l_row_id ROWID := x_rowid;
94
95 CURSOR get_row_id IS
96 SELECT rowid
97 FROM IGW_PROP_LOCATIONS
98 WHERE proposal_id = x_proposal_id
99 AND (PERFORMING_ORGANIZATION_ID = X_PERFORMING_ORGANIZATION_ID or PARTY_ID = X_PARTY_ID);
100 begin
101
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 X_LAST_UPDATE_DATE := SYSDATE;
111 if(X_MODE = 'I') then
112 X_LAST_UPDATED_BY := 1;
113 X_LAST_UPDATE_LOGIN := 0;
114 elsif (X_MODE = 'R') then
115 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
116 if X_LAST_UPDATED_BY is NULL then
117 X_LAST_UPDATED_BY := -1;
118 end if;
119 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
120 if X_LAST_UPDATE_LOGIN is NULL then
121 X_LAST_UPDATE_LOGIN := -1;
122 end if;
123 else
124 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
125 app_exception.raise_exception;
126 end if;
127
128 update IGW_PROP_LOCATIONS set
129 PERFORMING_ORGANIZATION_ID = X_PERFORMING_ORGANIZATION_ID,
130 PARTY_ID = X_PARTY_ID,
131 RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER + 1,
132 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
133 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
134 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
135 where ROWID = L_ROW_ID
136 AND RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER;
137
138
139 if (sql%notfound) then
140 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
141 FND_MSG_PUB.Add;
142 X_RETURN_STATUS := 'E';
143 end if;
144 EXCEPTION
145 WHEN OTHERS THEN
146 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'IGW_PROP_LOCATIONS_TBH'
147 ,p_procedure_name => 'UPDATE_ROW' );
148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
149 RAISE;
150 end UPDATE_ROW;
151
152 -----------------------------------------------------------------------------
153 procedure DELETE_ROW (
154 X_ROWID IN ROWID
155 ,X_PROPOSAL_ID IN NUMBER
156 ,X_PERFORMING_ORGANIZATION_ID IN NUMBER
157 ,X_PARTY_ID in NUMBER
158 ,X_RECORD_VERSION_NUMBER IN NUMBER
159 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
160 ) is
161
162
163 l_row_id ROWID := x_rowid;
164
165 CURSOR get_row_id IS
166 SELECT rowid
167 FROM IGW_PROP_LOCATIONS
168 WHERE proposal_id = x_proposal_id
169 AND (PERFORMING_ORGANIZATION_ID = X_PERFORMING_ORGANIZATION_ID or PARTY_ID = X_PARTY_ID);
170
171 begin
172
173 x_return_status := FND_API.G_RET_STS_SUCCESS;
174
175 IF l_row_id IS NULL THEN
176 OPEN get_row_id;
177 FETCH get_row_id INTO l_row_id;
178 CLOSE get_row_id;
179 END IF;
180
181
182 delete from IGW_PROP_LOCATIONS
183 where ROWID = L_ROW_ID
184 AND RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER;
185
186 if (sql%notfound) then
187 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
188 FND_MSG_PUB.Add;
189 X_RETURN_STATUS := 'E';
190 end if;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'IGW_PROP_LOCATIONS_TBH'
195 ,p_procedure_name => 'DELETE_ROW' );
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
197 RAISE;
198 end DELETE_ROW;
199
200 end IGW_PROP_LOCATIONS_TBH;