[Home] [Help]
PACKAGE BODY: APPS.IGR_INQUIRY_LINES_PKG
Source
1 PACKAGE BODY IGR_INQUIRY_LINES_PKG AS
2 /* $Header: IGSRT04B.pls 120.1 2005/11/23 13:27:53 appldev noship $ */
3
4 PROCEDURE insert_row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_sales_lead_line_id OUT NOCOPY NUMBER,
7 x_person_id IN NUMBER,
8 x_enquiry_appl_number IN NUMBER,
9 x_enquiry_dt IN DATE,
10 x_inquiry_method_code IN VARCHAR2,
11 x_preference IN NUMBER,
12 x_ret_status OUT NOCOPY VARCHAR2,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_mode IN VARCHAR2,
16 x_product_category_id IN NUMBER,
17 x_product_category_set_id IN NUMBER
18 ) AS
19 /*
20 || Created By : hreddych
21 || Created On : 30-JAN-2003
22 || Purpose : Handles the INSERT DML logic for the table.
23 || Known limitations, enhancements or remarks :
24 || Change History :
25 || Who When What
26 || (reverse chronological order - newest change first)
27 */
28 l_tmp_var VARCHAR2(2000);
29 lv_rowid VARCHAR2(30);
30 l_sales_lead_id igr_i_appl_all.sales_lead_id%TYPE;
31 l_sales_lead_line_id igr_i_a_lines.sales_lead_line_id%TYPE;
32 ddp_sales_lead_profile_tbl as_utility_pub.profile_tbl_type;
33 ddp_sales_lead_line_tbl as_sales_leads_pub.sales_lead_line_tbl_type;
34 ddx_sales_lead_line_out_tbl as_sales_leads_pub.sales_lead_line_out_tbl_type;
35 l_lead_proc_ret_status VARCHAR2(1);
36 l_lead_proc_msg_count NUMBER;
37 l_lead_proc_msg_data VARCHAR2(2000);
38
39
40 CURSOR cur_sales_lead_id (p_person_id igr_i_appl_all.person_id%TYPE,
41 p_enquiry_appl_number igr_i_appl_all.enquiry_appl_number%TYPE) IS
42 SELECT sales_lead_id
43 FROM igr_i_appl_all
44 WHERE person_id =p_person_id
45 AND enquiry_appl_number = p_enquiry_appl_number ;
46
47 BEGIN
48
49 IF get_uk_for_validation(x_person_id => x_person_id,
50 x_enquiry_appl_number => x_enquiry_appl_number,
51 x_product_category_id => x_product_category_id,
52 x_product_category_set_id => x_product_category_set_id ) THEN
53 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
54 IGS_GE_MSG_STACK.ADD;
55 App_Exception.Raise_Exception;
56 END IF;
57
58 OPEN cur_sales_lead_id( x_person_id,x_enquiry_appl_number);
59 FETCH cur_sales_lead_id INTO l_sales_lead_id ;
60 CLOSE cur_sales_lead_id;
61
62 ddp_sales_lead_line_tbl(1).sales_lead_line_id := NULL;
63 ddp_sales_lead_line_tbl(1).organization_id := FND_PROFILE.VALUE('ORG_ID');
64 ddp_sales_lead_line_tbl(1).category_id := x_product_category_id;
65 ddp_sales_lead_line_tbl(1).category_set_id := x_product_category_set_id;
66
67 as_sales_leads_pub.Create_sales_lead_lines(
68 P_Api_Version_Number => 2.0,
69 P_Init_Msg_List => FND_API.G_FALSE,
70 P_Commit => FND_API.G_FALSE,
71 p_validation_level => AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
72 P_Check_Access_Flag => FND_API.G_MISS_CHAR,
73 P_Admin_Flag => 'Y',
74 P_Admin_Group_Id => FND_API.G_MISS_NUM,
75 P_identity_salesforce_id => FND_API.G_MISS_NUM,
76 P_Sales_Lead_Profile_Tbl => ddp_sales_lead_profile_tbl,
77 P_SALES_LEAD_LINE_Tbl => ddp_sales_lead_line_tbl,
78 P_SALES_LEAD_ID => l_sales_lead_id,
79 X_SALES_LEAD_LINE_OUT_Tbl => ddx_sales_lead_line_out_tbl,
80 X_Return_Status => x_ret_status,
81 X_Msg_Count => x_msg_count,
82 X_Msg_Data => x_msg_data
83 );
84
85 X_sales_lead_line_id :=ddx_sales_lead_line_out_tbl(1).sales_lead_line_id;
86 IF x_ret_status IN ('E','U') THEN
87 IF x_msg_count > 1 THEN
88 FOR i IN 1..x_msg_count LOOP
89 l_tmp_var := l_tmp_var || ' '||fnd_msg_pub.get(p_encoded => fnd_api.g_false);
90 END LOOP;
91 x_msg_data := trim(l_tmp_var);
92 END IF;
93 ELSE
94 lv_rowid := x_rowid ;
95 igr_i_a_lines_pkg.insert_row (
96 x_mode => 'R',
97 x_rowid => lv_rowid,
98 x_person_id => x_person_id,
99 x_enquiry_appl_number => x_enquiry_appl_number,
100 x_sales_lead_line_id => X_sales_lead_line_id,
101 x_preference => x_preference
102 );
103 x_rowid := lv_rowid ;
104
105 -- call Sales Real Time lead assignment API, passing in local ret/msg
106 -- variables, as failure of this API should not preclude lead
107 -- or inquiry line creation.
108
109 AS_SALES_LEADS_PUB.Lead_Process_After_Update (
110 P_Api_Version_Number => 2.0,
111 P_Init_Msg_List => FND_API.G_FALSE,
112 P_Commit => FND_API.G_FALSE,
113 P_Validation_Level => AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
114 P_Check_Access_Flag => FND_API.G_MISS_CHAR,
115 P_Admin_Flag => FND_API.G_MISS_CHAR,
116 P_Admin_Group_Id => FND_API.G_MISS_NUM,
117 P_identity_salesforce_id => FND_API.G_MISS_NUM,
118 P_Salesgroup_id => FND_API.G_MISS_NUM,
119 P_Sales_Lead_Id => l_sales_lead_id,
120 X_Return_Status => l_lead_proc_ret_status,
121 X_Msg_Count => l_lead_proc_msg_count,
122 X_Msg_Data => l_lead_proc_msg_data
123 );
124
125 IF NVL(l_lead_proc_ret_status,'S') IN ('E','U') THEN
126 fnd_file.put_line(fnd_file.log, 'AS_SALES_LEADS_PUB.Lead_Process_After_Update failed.');
127 END IF;
128
129 END IF;
130
131 END insert_row;
132
133
134
135 PROCEDURE update_row (
136 x_rowid IN VARCHAR2,
137 x_sales_lead_line_id IN NUMBER,
138 x_person_id IN NUMBER,
139 x_enquiry_appl_number IN NUMBER,
140 x_enquiry_dt IN DATE,
141 x_inquiry_method_code IN VARCHAR2,
142 x_preference IN NUMBER,
143 x_mode IN VARCHAR2,
144 x_ret_status OUT NOCOPY VARCHAR2,
145 x_msg_data OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,
147 x_product_category_id IN NUMBER,
148 x_product_category_set_id IN NUMBER
149 ) AS
150 /*
151 || Created By : hreddych
152 || Created On : 30-JAN-2003
153 || Purpose : Handles the UPDATE DML logic for the table.
154 || Known limitations, enhancements or remarks :
155 || Change History :
156 || Who When What
157 || (reverse chronological order - newest change first)
158 */
159
160 l_tmp_var VARCHAR2(2000);
161 lv_rowid VARCHAR2(30);
162 l_sales_lead_id igr_i_appl_all.sales_lead_id%TYPE;
163 l_sales_lead_line_id igr_i_a_lines.sales_lead_line_id%TYPE;
164 ddp_sales_lead_profile_tbl as_utility_pub.profile_tbl_type;
165 ddp_sales_lead_line_tbl as_sales_leads_pub.sales_lead_line_tbl_type;
166 ddx_sales_lead_line_out_tbl as_sales_leads_pub.sales_lead_line_out_tbl_type;
167 l_lead_proc_ret_status VARCHAR2(1);
168 l_lead_proc_msg_count NUMBER;
169 l_lead_proc_msg_data VARCHAR2(2000);
170
171 CURSOR cur_sales_lead_id (p_person_id igr_i_appl_all.person_id%TYPE,
172 p_enquiry_appl_number igr_i_appl_all.enquiry_appl_number%TYPE) IS
173 SELECT sales_lead_id
174 FROM igr_i_appl_all
175 WHERE person_id =p_person_id
176 AND enquiry_appl_number = p_enquiry_appl_number ;
177
178 CURSOR cur_last_update_date IS
179 SELECT last_update_date
180 FROM as_sales_lead_lines
181 WHERE sales_lead_line_id = x_sales_lead_line_id;
182
183 BEGIN
184
185 IF get_uk_for_validation(x_person_id => x_person_id,
186 x_enquiry_appl_number => x_enquiry_appl_number,
187 x_rowid => x_rowid,
188 x_product_category_id => x_product_category_id,
189 x_product_category_set_id => x_product_category_set_id ) THEN
190 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 END IF;
194
195 OPEN cur_sales_lead_id( x_person_id,x_enquiry_appl_number);
196 FETCH cur_sales_lead_id INTO l_sales_lead_id ;
197 CLOSE cur_sales_lead_id;
198
199 OPEN cur_last_update_date;
200 FETCH cur_last_update_date INTO ddp_sales_lead_line_tbl(1).last_update_date ;
201 CLOSE cur_last_update_date;
202
203 ddp_sales_lead_line_tbl(1).sales_lead_id := l_sales_lead_id;
204 ddp_sales_lead_line_tbl(1).sales_lead_line_id := x_sales_lead_line_id;
205 ddp_sales_lead_line_tbl(1).organization_id := FND_PROFILE.VALUE('ORG_ID');
206 ddp_sales_lead_line_tbl(1).category_id := x_product_category_id;
207 ddp_sales_lead_line_tbl(1).category_set_id := x_product_category_set_id;
208
209 as_sales_leads_pub.Update_sales_lead_lines(
210 P_Api_Version_Number =>2.0,
211 P_Init_Msg_List => FND_API.G_FALSE,
212 P_Commit => FND_API.G_FALSE,
213 p_validation_level => AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
214 P_Check_Access_Flag => FND_API.G_MISS_CHAR,
215 P_Admin_Flag => 'Y',
216 P_Admin_Group_Id => FND_API.G_MISS_NUM,
217 P_identity_salesforce_id => FND_API.G_MISS_NUM,
218 P_Sales_Lead_Profile_Tbl => ddp_sales_lead_profile_tbl,
219 P_SALES_LEAD_LINE_Tbl => ddp_sales_lead_line_tbl,
220 X_SALES_LEAD_LINE_OUT_Tbl => ddx_sales_lead_line_out_tbl,
221 X_Return_Status => x_ret_status,
222 X_Msg_Count => x_msg_count,
223 X_Msg_Data => x_msg_data
224 );
225
226 IF x_ret_status IN ('E','U') THEN
227 IF x_msg_count > 1 THEN
228 FOR i IN 1..x_msg_count LOOP
229 l_tmp_var := l_tmp_var || ' '||fnd_msg_pub.get(p_encoded => fnd_api.g_false);
230 END LOOP;
231 x_msg_data := trim(l_tmp_var);
232 END IF;
233 ELSE
234 igr_i_a_lines_pkg.update_row (
235 x_mode => 'R',
236 x_rowid => x_rowid,
237 x_person_id => x_person_id,
238 x_enquiry_appl_number => x_enquiry_appl_number,
239 x_sales_lead_line_id => x_sales_lead_line_id,
240 x_preference => x_preference
241 );
242
243 -- call Sales Real Time lead assignment API, passing in local ret/msg
244 -- variables, as failure of this API should not preclude lead
245 -- or inquiry line update.
246
247 AS_SALES_LEADS_PUB.Lead_Process_After_Update (
248 P_Api_Version_Number => 2.0,
249 P_Init_Msg_List => FND_API.G_FALSE,
250 P_Commit => FND_API.G_FALSE,
251 P_Validation_Level => AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
252 P_Check_Access_Flag => FND_API.G_MISS_CHAR,
253 P_Admin_Flag => FND_API.G_MISS_CHAR,
254 P_Admin_Group_Id => FND_API.G_MISS_NUM,
255 P_identity_salesforce_id => FND_API.G_MISS_NUM,
256 P_Salesgroup_id => FND_API.G_MISS_NUM,
257 P_Sales_Lead_Id => l_sales_lead_id,
258 X_Return_Status => l_lead_proc_ret_status,
259 X_Msg_Count => l_lead_proc_msg_count,
260 X_Msg_Data => l_lead_proc_msg_data
261 );
262
263 IF NVL(l_lead_proc_ret_status,'S') IN ('E','U') THEN
264 fnd_file.put_line(fnd_file.log, 'AS_SALES_LEADS_PUB.Lead_Process_After_Update failed.');
265 END IF;
266
267 END IF;
268
269 END update_row;
270
271 FUNCTION get_uk_for_validation (
272 x_person_id IN NUMBER,
273 x_enquiry_appl_number IN NUMBER,
274 x_rowid IN VARCHAR2,
275 x_product_category_id IN NUMBER,
276 x_product_category_set_id IN NUMBER
277 ) RETURN BOOLEAN AS
278 /*
279 || Created By :
280 || Created On : 28-NOV-2001
281 || Purpose : Validates the Unique Keys of the table.
282 || Known limitations, enhancements or remarks :
283 || Change History :
284 || Who When What
285 || (reverse chronological order - newest change first)
286 */
287 CURSOR cur_rowid (p_person_id igr_i_a_lines_v.person_id%TYPE ,
288 p_enquiry_appl_number igr_i_a_lines_v.enquiry_appl_number%TYPE ,
289 p_product_category_id igr_i_a_lines_v.product_category_id%TYPE,
290 p_product_category_set_id igr_i_a_lines_v.product_category_set_id%TYPE,
291 l_rowid VARCHAR2) IS
292 SELECT row_id
293 FROM igr_i_a_lines_v
294 WHERE person_id = p_person_id
295 AND enquiry_appl_number = p_enquiry_appl_number
296 AND product_category_id = p_product_category_id
297 AND product_category_set_id = p_product_category_set_id
298 AND ((l_rowid IS NULL) OR (row_id <> l_rowid));
299
300 lv_rowid cur_rowid%RowType;
301
302 BEGIN
303
304 OPEN cur_rowid(x_person_id ,
305 x_enquiry_appl_number ,
306 x_product_category_id,
307 x_product_category_set_id,
308 x_rowid);
309 FETCH cur_rowid INTO lv_rowid;
310 IF (cur_rowid%FOUND) THEN
311 CLOSE cur_rowid;
312 RETURN (true);
313 ELSE
314 CLOSE cur_rowid;
318 END get_uk_for_validation;
315 RETURN(FALSE);
316 END IF;
317
319 END IGR_INQUIRY_LINES_PKG;