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