[Home] [Help]
PACKAGE BODY: APPS.IBE_CART_NOTIFY_UTIL
Source
1 PACKAGE BODY IBE_CART_NOTIFY_UTIL AS
2 /* $Header: IBEVCNUB.pls 120.3 2005/12/15 00:55:31 banatara ship $ */
3 -- Start of Comments
4 -- Package name : IBE_Cart_Notify_Util
5 -- Purpose :
6 -- NOTE :
7 -- End of Comments
8
9 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_CART_NOTIFY_UTIL';
10 G_FILE_NAME CONSTANT VARCHAR2(12) := 'IBEVCNUB.pls';
11
12 PROCEDURE Get_sales_assist_hdr_tokens(
13 p_api_version IN NUMBER := 1.0 ,
14 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
15 p_commit IN VARCHAR2 := FND_API.G_FALSE,
16 x_return_status OUT NOCOPY VARCHAR2 ,
17 x_msg_count OUT NOCOPY NUMBER ,
18 x_msg_data OUT NOCOPY VARCHAR2 ,
19 p_quote_header_id IN NUMBER ,
20 p_minisite_id IN NUMBER ,
21 x_Contact_Name OUT NOCOPY VARCHAR2 ,
22 x_Contact_phone OUT NOCOPY VARCHAR2 ,
23 x_email OUT NOCOPY VARCHAR2 ,
24 x_first_name OUT NOCOPY VARCHAR2 ,
25 X_last_name OUT NOCOPY VARCHAR2 ,
26 x_Cart_name OUT NOCOPY VARCHAR2 ,
27 X_cart_date OUT NOCOPY VARCHAR2 ,
28 x_Ship_to_name OUT NOCOPY VARCHAR2 ,
29 x_ship_to_address1 OUT NOCOPY VARCHAR2 ,
30 x_ship_to_address2 OUT NOCOPY VARCHAR2 ,
31 x_ship_to_address3 OUT NOCOPY VARCHAR2 ,
32 x_ship_to_address4 OUT NOCOPY VARCHAR2 ,
33 x_country OUT NOCOPY VARCHAR2 ,
34 X_CITY OUT NOCOPY VARCHAR2 ,
35 X_POSTAL_CODE OUT NOCOPY VARCHAR2 ,
36 X_SHIP_TO_STATE OUT NOCOPY VARCHAR2 ,
37 X_SHIP_TO_PROVINCE OUT NOCOPY VARCHAR2 ,
38 X_SHIP_TO_COUNTY OUT NOCOPY VARCHAR2 ,
39 x_shipping_method OUT NOCOPY VARCHAR2 ,
40 x_minisite_name OUT NOCOPY VARCHAR2 ,
41 x_ship_and_hand OUT NOCOPY NUMBER ,
42 x_tax OUT NOCOPY NUMBER ,
43 x_total OUT NOCOPY NUMBER ) IS
44
45
46 CURSOR c_get_minisite_token(p_minisite_id number) is
47 SELECT msite_name
48 FROM ibe_msites_vl
49 where msite_id = p_minisite_id;
50
51 CURSOR c_get_notify_hdr_tokens(p_quote_header_id number, inv_org_id number) is
52 SELECT qh.quote_name,
53 qh.last_update_date,
54 s.ship_to_cust_account_id,
55 ip.party_name SHIP_TO_CUST_NAME,
56 nvl(loc.ADDRESS1,'') add1,
57 nvl(loc.ADDRESS2,'') add2,
58 nvl(loc.ADDRESS3,'') add3,
59 nvl(loc.ADDRESS4,'') add4,
60 nvl(loc.COUNTRY,'') country,
61 nvl(loc.CITY,'') city,
62 nvl(loc.POSTAL_CODE,'') zip,
63 nvl(loc.STATE,'') state,
64 nvl(loc.PROVINCE,'') province,
65 nvl(loc.COUNTY,'') county,
66 fl.MEANING SHIP_METHOD_CODE_MEANING,
67 qh.TOTAL_SHIPPING_CHARGE,
68 qh.TOTAL_TAX,
69 qh.TOTAL_QUOTE_PRICE
70 FROM aso_quote_headers_all qh,
71 aso_shipments s,
72 hz_party_sites ps,
73 hz_locations loc,
74 aso_i_parties_v ip,
75 wsh_carrier_ship_methods csm,
76 fnd_lookup_values_vl fl
77 WHERE qh.quote_header_id = s.quote_header_id
78 and s.ship_to_party_site_id = ps.party_site_id(+)
79 and s.ship_to_cust_party_id = ip.party_id(+)
80 and ps.location_id = loc.location_id(+)
81 and s.ship_method_code = csm.ship_method_code
82 and fl.lookup_type = 'SHIP_METHOD'
83 and fl.lookup_code = csm.ship_method_code
84 and fl.view_application_id = 3
85 and qh.quote_header_id = p_quote_header_id
86 and organization_id = inv_org_id;
87
88
89 CURSOR c_get_contact_tokens(p_quote_header_id number) is
90 SELECT ap.party_name,
91 ap.person_first_name,
92 ap.person_last_name,
93 ap.party_type,
94 DECODE(ap.party_type, 'PERSON', NULL, ap.party_name) organization_name,
95 nvl(h.email_address,'') email,
96 nvl(h.phone_area_code,'') ph_area_code,
97 nvl(h.phone_number,'') ph_number,
98 nvl(h.phone_extension,'') ph_extension,
99 h.phone_line_type ,
100 h.contact_point_purpose,
101 h.contact_point_type
102
103 FROM hz_contact_points h,
104 aso_i_parties_v ap,
105 fnd_user fnd
106 where fnd.user_id = FND_GLOBAL.USER_ID
107 and owner_table_name = 'HZ_PARTIES'
108 and owner_table_id = fnd.customer_id
109 and fnd.customer_id = ap.party_id
110 and h.status = 'A';
111
112
113 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_CART_NOTIFY_UTIL';
114 l_api_name CONSTANT VARCHAR2(50) := 'Get_sales_assist_tokens_pvt';
115 l_api_version number := 1.0;
116
117 l_ship_to_cust_name varchar2(360);
118 l_ship_to_address varchar2(2000);
119 l_contact_phone varchar2(100);
120 l_inv_org_id number;
121 l_ship_to_cust_id NUMBER;
122 rec_get_minisite_token c_get_minisite_token%rowtype;
123 rec_get_notify_hdr_tokens c_get_notify_hdr_tokens%rowtype;
124 rec_get_contact_tokens c_get_contact_tokens%rowtype;
125
126
127 BEGIN
128
129 -- Standard Start of API savepoint
130 SAVEPOINT Get_sales_assist_tokens_pvt;
131
132 -- Standard call to check for call compatibility.
133 IF NOT FND_API.Compatible_API_Call(L_API_VERSION,
134 p_api_version,
135 L_API_NAME ,
136 G_PKG_NAME )
137 THEN
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
139 END IF;
140
141 -- Initialize message list if p_init_msg_list is set to TRUE.
142 IF FND_API.To_Boolean(p_init_msg_list) THEN
143 FND_Msg_Pub.initialize;
144 END IF;
145
146 -- Initialize API return status to success
147 x_return_status := FND_API.G_RET_STS_SUCCESS;
148
149 --Start of API Body
150 l_inv_org_id := fnd_profile.value_specific('IBE_ITEM_VALIDATION_ORGANIZATION',null,null,671);
151 --671 is the application_id for iStore
152 for rec_get_minisite_token in c_get_minisite_token(p_minisite_id) loop
153 x_minisite_name := rec_get_minisite_token.msite_name;
154 exit when c_get_minisite_token%notfound;
155 end loop;
156
157 for rec_get_notify_hdr_tokens in c_get_notify_hdr_tokens(p_quote_header_id,l_inv_org_id) loop
158
159 l_ship_to_cust_id := rec_get_notify_hdr_tokens.ship_to_cust_account_id;
160 l_ship_to_cust_name := rec_get_notify_hdr_tokens.ship_to_cust_name;
161 /*if rec_get_notify_hdr_tokens.ship_to_cust_account_id is not null then
162 x_Ship_to_name := rec_get_notify_hdr_tokens.ship_to_cust_name;
163 else
164 x_Ship_to_name := rec_get_notify_hdr_tokens.organization_name;
165 end if;*/
166 x_cart_name := rec_get_notify_hdr_tokens.quote_name;
167 x_cart_date := rec_get_notify_hdr_tokens.last_update_date;
168 x_shipping_method := rec_get_notify_hdr_tokens.ship_method_code_meaning;
169 x_ship_and_hand := rec_get_notify_hdr_tokens.TOTAL_SHIPPING_CHARGE;
170 x_tax := rec_get_notify_hdr_tokens.TOTAL_TAX;
171 x_total := rec_get_notify_hdr_tokens.TOTAL_QUOTE_PRICE;
172 X_ship_to_address1 := rec_get_notify_hdr_tokens.add1;
173 X_ship_to_address2 := rec_get_notify_hdr_tokens.add2;
174 X_ship_to_address3 := rec_get_notify_hdr_tokens.add3;
175 X_ship_to_address4 := rec_get_notify_hdr_tokens.add4;
176 X_country := rec_get_notify_hdr_tokens.country;
177 x_city := rec_get_notify_hdr_tokens.city;
178 x_postal_code := rec_get_notify_hdr_tokens.zip;
179 x_ship_to_state := rec_get_notify_hdr_tokens.state;
180 x_ship_to_province := rec_get_notify_hdr_tokens.province;
181 x_ship_to_county := rec_get_notify_hdr_tokens.county;
182 exit when c_get_notify_hdr_tokens%notfound;
183 end loop;
184
185 for rec_get_contact_tokens in c_get_contact_tokens(p_quote_header_id) loop
186
187 IF l_ship_to_cust_id is not null THEN
188 x_Ship_to_name := l_ship_to_cust_name;
189 ELSE
190 x_Ship_to_name := rec_get_contact_tokens.organization_name;
191 END IF;
192
193 IF (rec_get_contact_tokens.contact_point_type = 'EMAIL') THEN
194 x_email := rec_get_contact_tokens.email;
195 END IF;
196
197 IF(rec_get_contact_tokens.party_type = 'PARTY_RELATIONSHIP' ) THEN
198 x_contact_name := rec_get_contact_tokens.person_first_name;
199 x_contact_name := x_contact_name||' '||rec_get_contact_tokens.person_last_name;
200 ELSE
201 x_contact_name := rec_get_contact_tokens.party_name;
202 END IF;
203 x_first_name := rec_get_contact_tokens.person_first_name;
204 x_last_name := rec_get_contact_tokens.person_last_name;
205
206 IF ((rec_get_contact_tokens.contact_point_type = 'PHONE')
207 and (rec_get_contact_tokens.phone_line_type = 'GEN' )
208 and (rec_get_contact_tokens.contact_point_purpose = 'BUSINESS')) THEN
209 l_contact_phone := rec_get_contact_tokens.ph_area_code||'-';
210 l_contact_phone := l_contact_phone||rec_get_contact_tokens.ph_number;
211 l_contact_phone := l_contact_phone||rec_get_contact_tokens.ph_extension;
212 END IF;
213 exit when c_get_contact_tokens%notfound;
214 end loop;
215
216 x_contact_phone := l_contact_phone;
217
218 -- Standard check of p_commit.
219 IF FND_API.To_Boolean(p_commit) THEN
220 COMMIT WORK;
221 END IF;
222
223 -- Standard call to get message count and if count is 1, get message info.
224 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
225 p_count => x_msg_count ,
226 p_data => x_msg_data);
227 EXCEPTION
228 WHEN FND_API.G_EXC_ERROR THEN
229 ROLLBACK TO Get_sales_assist_tokens_pvt;
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
232 p_count => x_msg_count ,
233 p_data => x_msg_data);
234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
235 ROLLBACK TO Get_sales_assist_tokens_pvt;
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
238 p_count => x_msg_count ,
239 p_data => x_msg_data);
240 WHEN OTHERS THEN
241 ROLLBACK TO Get_sales_assist_tokens_pvt;
242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
243 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
244 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
245 L_API_NAME);
246 END IF;
247
248 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
249 p_count => x_msg_count ,
250 p_data => x_msg_data);
251
252 END Get_sales_assist_hdr_tokens;
253
254 PROCEDURE Get_sales_assist_line_tokens(
255 p_api_version IN NUMBER := 1.0 ,
256 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
257 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
258 x_return_status OUT NOCOPY VARCHAR2 ,
259 x_msg_count OUT NOCOPY NUMBER ,
260 x_msg_data OUT NOCOPY VARCHAR2 ,
261 P_quote_header_id IN NUMBER ,
262 x_notify_line_tokens OUT NOCOPY IBE_CART_NOTIFY_UTIL.notify_line_tokens_tab_type) is
263
264 CURSOR c_get_notify_line_tokens(p_quote_header_id number) is
265 SELECT qh.quote_header_id,
266 ql.quote_line_id,
267 ql.quantity,
268 ql.line_quote_price,
269 m.shippable_item_flag,
270 m.inventory_item_id,
271 m.description
272
273 FROM aso_quote_headers_all qh,
274 aso_quote_lines_all ql,
275 mtl_system_items_vl m
276 where qh.quote_header_id = ql.quote_header_id
277 and ql.inventory_item_id = m.inventory_item_id
278 and ql.organization_id = m.organization_id
279 and qh.quote_header_id = p_quote_header_id;
280
281 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_CART_NOTIFY_UTIL';
282 l_api_name CONSTANT VARCHAR2(100) := 'Get_sales_assist_line_tokens';
283 l_api_version number := 1.0;
284 loop_counter number := 1;
285 l_notify_line_tokens IBE_CART_NOTIFY_UTIL.notify_line_tokens_tab_type;
286
287 BEGIN
288
289 -- Standard Start of API savepoint
290 SAVEPOINT Create_New_Version_Pvt;
291
292 -- Standard call to check for call compatibility.
293 IF NOT FND_API.Compatible_API_Call(L_API_VERSION,
294 p_api_version,
295 L_API_NAME ,
296 G_PKG_NAME )
297 THEN
298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
299 END IF;
300
301 -- Initialize message list if p_init_msg_list is set to TRUE.
302 IF FND_API.To_Boolean(p_init_msg_list) THEN
303 FND_Msg_Pub.initialize;
304 END IF;
305
306 -- Initialize API return status to success
307 x_return_status := FND_API.G_RET_STS_SUCCESS;
308
309 --Start of API Body
310 for rec_get_notify_line_tokens in c_get_notify_line_tokens(p_quote_header_id) loop
311 l_notify_line_tokens(loop_counter).Quote_line_id := rec_get_notify_line_tokens.quote_line_id;
312 l_notify_line_tokens(loop_counter).item_name := rec_get_notify_line_tokens.description;
313 l_notify_line_tokens(loop_counter).Item_Quantity := rec_get_notify_line_tokens.Quantity;
314 l_notify_line_tokens(loop_counter).Shippable_flag := rec_get_notify_line_tokens.shippable_item_flag;
315 l_notify_line_tokens(loop_counter).line_quote_price := rec_get_notify_line_tokens.line_quote_price;
316
317 loop_counter := loop_counter+1;
318 EXIT when c_get_notify_line_tokens%NOTFOUND;
319 END LOOP;
320 x_notify_line_tokens := l_notify_line_tokens;
321 -- Standard check of p_commit.
322 IF FND_API.To_Boolean(p_commit) THEN
323 COMMIT WORK;
324 END IF;
325
326 -- Standard call to get message count and if count is 1, get message info.
327 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
328 p_count => x_msg_count ,
329 p_data => x_msg_data);
330 EXCEPTION
331 WHEN FND_API.G_EXC_ERROR THEN
332 ROLLBACK TO Create_New_Version_Pvt;
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
335 p_count => x_msg_count ,
336 p_data => x_msg_data);
337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
338 ROLLBACK TO Create_New_Version_Pvt;
339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
340 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
341 p_count => x_msg_count ,
342 p_data => x_msg_data);
343 WHEN OTHERS THEN
344 ROLLBACK TO Create_New_Version_Pvt;
345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
346 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
347 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
348 L_API_NAME);
349 END IF;
350
351 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
352 p_count => x_msg_count ,
353 p_data => x_msg_data);
354
355 END Get_sales_assist_line_tokens;
356 END IBE_CART_NOTIFY_UTIL;
357