1 PACKAGE BODY igr_i_status_pkg AS
2 /* $Header: IGSRH07B.pls 120.1 2006/01/11 23:45:24 rghosh noship $ */
3
4 PROCEDURE update_row (
5 X_S_ENQUIRY_STATUS in VARCHAR2,
6 X_ENQUIRY_STATUS in VARCHAR2,
7 X_DESCRIPTION in VARCHAR2,
8 x_ret_status OUT NOCOPY VARCHAR2,
9 x_msg_data OUT NOCOPY VARCHAR2,
10 x_msg_count OUT NOCOPY NUMBER
11 ) AS
12
13 l_last_update_date DATE;
14 l_enabled_flag VARCHAR2(1);
15 l_lead_flag VARCHAR2(1);
16 l_opp_flag VARCHAR2(1);
17 l_opp_open_status_flag VARCHAR2(1);
18 l_opp_decision_date_flag VARCHAR2(1);
19 l_forecast_rollup_flag VARCHAR2(1);
20 l_win_loss_indicator VARCHAR2(1);
21 l_attribute_category VARCHAR2(30);
22 l_attribute1 VARCHAR2(150);
23 l_attribute2 VARCHAR2(150);
24 l_attribute3 VARCHAR2(150);
25 l_attribute4 VARCHAR2(150);
26 l_attribute5 VARCHAR2(150);
27 l_attribute6 VARCHAR2(150);
28 l_attribute7 VARCHAR2(150);
29 l_attribute8 VARCHAR2(150);
30 l_attribute9 VARCHAR2(150);
31 l_attribute10 VARCHAR2(150);
32 l_attribute11 VARCHAR2(150);
33 l_attribute12 VARCHAR2(150);
34 l_attribute13 VARCHAR2(150);
35 l_attribute14 VARCHAR2(150);
36 l_attribute15 VARCHAR2(150);
37 l_meaning VARCHAR2(240);
38 l_description VARCHAR2(240);
39 v_description VARCHAR2(240);
40 l_status_rank NUMBER;
41 l_status_code AS_STATUSES_B.status_code%TYPE;
42 v_enquiry_status VARCHAR2(240);
43 l_last_updated_by NUMBER(15);
44 l_last_update_login NUMBER(15);
45
46
47 CURSOR c_get_status IS
48 SELECT last_update_date,
49 enabled_flag,
50 lead_flag,
51 opp_flag,
52 opp_open_status_flag ,
53 opp_decision_date_flag,
54 forecast_rollup_flag,
55 win_loss_indicator,
56 attribute_category,
57 attribute1,
58 attribute2,
59 attribute3,
60 attribute4,
61 attribute5,
62 attribute6,
63 attribute7,
64 attribute8,
65 attribute9,
66 attribute10,
67 attribute11,
68 attribute12,
69 attribute13,
70 attribute14,
71 attribute15,
72 meaning,
73 description,
74 status_rank
75 FROM as_statuses_vl
76 WHERE status_code = x_s_enquiry_status;
77
78
79 BEGIN
80
81 -- Standard start of api save point
82
83 SAVEPOINT update_row_status;
84
85 -- Initialize api return status
86
87 x_ret_status := fnd_api.g_ret_sts_success;
88
89
90 -- Fetch all the column values into local variables from the database
91 -- and pass these values, if they are not changed, in the AS_STATUSES_PKG.UPDATE_ROW call
92
93 OPEN c_get_status;
94 FETCH c_get_status
95 INTO l_last_update_date,
96 l_enabled_flag,
97 l_lead_flag,
98 l_opp_flag,
99 l_opp_open_status_flag ,
100 l_opp_decision_date_flag,
101 l_forecast_rollup_flag,
102 l_win_loss_indicator,
103 l_attribute_category,
104 l_attribute1,
105 l_attribute2,
106 l_attribute3,
107 l_attribute4,
108 l_attribute5,
109 l_attribute6,
110 l_attribute7,
111 l_attribute8,
112 l_attribute9,
113 l_attribute10,
114 l_attribute11,
115 l_attribute12,
116 l_attribute13,
117 l_attribute14,
118 l_attribute15,
119 l_meaning,
120 l_description,
121 l_status_rank;
122
123 CLOSE c_get_status;
124
125 -- In the update row call, need to pass the system enquiry status as the status code
126 -- Since the system enquiry status parameter cannot be null, hence we dont need to handle the
127 -- null condition here
128 l_status_code := x_s_enquiry_status;
129
130 -- If the enquiry status parameter is null, then pass the value from the database
131 IF x_enquiry_status IS NULL THEN
132 v_enquiry_status := l_meaning;
133 ELSE
134 v_enquiry_status := x_enquiry_status;
135 END IF;
136
137 -- If the description parameter is null, then pass the value from the database
138 IF x_description IS NULL THEN
139 v_description := l_description;
140 ELSE
141 v_description := x_description;
142 END IF;
143
144 -- If the enabled flag is null in the database, update it to 'Y'.
145 IF l_enabled_flag IS NULL THEN
146 l_enabled_flag := 'Y';
147 END IF;
148
149 -- If the lead flag is null in the database, update it to 'N'.
150 IF l_lead_flag IS NULL THEN
151 l_lead_flag := 'N';
152 END IF;
153
154 -- If the l_opp_flag is null in the database, update it to 'N'.
155 IF l_opp_flag IS NULL THEN
156 l_opp_flag := 'N';
157 END IF;
158
159 -- If the l_opp_open_status_flag is null in the database, update it to 'N'.
160 IF l_opp_open_status_flag IS NULL THEN
161 l_opp_open_status_flag := 'N';
162 END IF;
163
164 -- If the l_opp_decision_date_flag is null in the database, update it to 'N'.
165 IF l_opp_decision_date_flag IS NULL THEN
166 l_opp_decision_date_flag := 'N';
167 END IF;
168
169 -- If the l_forecast_rollup_flag is null in the database, update it to 'N'.
170 IF l_forecast_rollup_flag IS NULL THEN
171 l_forecast_rollup_flag := 'N';
172 END IF;
173
174 -- If the l_win_loss_indicator is null in the database, update it to 'N'.
175 IF l_win_loss_indicator IS NULL THEN
176 l_win_loss_indicator := 'N';
177 END IF;
178
179
180 -- populate the last_updated_by and last_update_login fields.
181 l_last_updated_by := fnd_global.user_id;
182 l_last_update_login := fnd_global.login_id;
183
184 -- calling the update_row API to update the statuses.
185 AS_STATUSES_PKG.UPDATE_ROW(
186 l_status_code,
187 l_enabled_flag,
188 l_lead_flag,
189 l_opp_flag,
190 l_opp_open_status_flag,
191 l_opp_decision_date_flag,
192 l_status_rank,
193 l_forecast_rollup_flag,
194 l_win_loss_indicator,
195 NULL,
196 l_attribute_category,
197 l_attribute1,
198 l_attribute2,
199 l_attribute3,
200 l_attribute4,
201 l_attribute5,
202 l_attribute6,
203 l_attribute7,
204 l_attribute8,
205 l_attribute9,
206 l_attribute10,
207 l_attribute11,
208 l_attribute12,
209 l_attribute13,
210 l_attribute14,
211 l_attribute15,
212 v_enquiry_status,
213 v_description,
214 l_last_update_date,
215 l_last_updated_by,
216 l_last_update_login);
217
218 EXCEPTION
219 -- Exception Handling
220 WHEN OTHERS THEN
221 ROLLBACK TO update_row_status;
222 x_ret_status := fnd_api.g_ret_sts_unexp_error;
223 fnd_msg_pub.count_and_get (
224 p_count => x_msg_count,
225 p_data => x_msg_data
226 );
227
228 END update_row;
229 END igr_i_status_pkg;
[Home] [Help]