[Home] [Help]
PACKAGE BODY: APPS.AS_OSI_LEAD_PUB
Source
1 PACKAGE BODY AS_OSI_LEAD_PUB as
2 /* $Header: custom_asxposib.pls 115.5.1157.2 2002/02/21 09:11:47 pkm ship $ */
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- AS_OSI_LEAD_PUB
8 --
9 -- PURPOSE
10 -- This package is a public API for inserting OSI enhanced oppy information into
11 -- OSM. It contains specification for pl/sql records and tables and the
12 -- Public fetch and update API.
13 --
14 -- Procedures:
15 -- osi_lead_fetch (see below for specification)
16 -- osi_lead_update (see below for specification)
17
18 --
19 -- NOTES
20 -- This package is publicly available for use
21 --
22 --
23 --
24 -- HISTORY
25 -- 12/12/99 JHIBNER Created
26 -- End of Comments
27
28
29 PROCEDURE osi_lead_fetch
30 ( p_api_version_number IN NUMBER,
31 p_lead_id in VARCHAR2,
32 p_osi_rec out OSI_REC_TYPE ,
33 p_osi_ovd_tbl out OSI_OVD_TBL_TYPE
34 ) is
35 cursor osi_cur (lead_id_in in varchar2) is
36 select *
37 from as_osi_leads_all
38 where lead_id = lead_id_in;
39 cursor ovd_cur (lead_id_in in varchar2) is
40 select *
41 from as_osi_lead_ovl_all
42 where osi_lead_id = lead_id_in
43 order by ovm_code;
44 cursor osi2_cur (lead_id_in in varchar2) is
45 select substr(addr.address1||' '||addr.city||','||addr.state,1,50) site_name,
46 substr(cust.party_name,1,50) cust_name, substr(oppy.description,1,50) oppy_name
47 from as_leads_all oppy
48 ,hz_parties cust
49 ,as_party_addresses_v addr
50 where oppy.lead_id = lead_id_in
51 and cust.party_id = oppy.customer_id
52 and addr.address_id = oppy.address_id;
53 l_osi_rec OSI_REC_TYPE := G_MISS_OSI_REC;
54 l_osi_ovd_tbl OSI_OVD_TBL_TYPE;
55 ndx binary_integer := 0;
56 begin
57 for osi in osi_cur (p_lead_id) loop
58 l_osi_rec.last_updated_by := to_char(osi.last_updated_by);
59 l_osi_rec.created_by := to_char(osi.created_by);
60 l_osi_rec.last_update_login := to_char(osi.last_update_login);
61 l_osi_rec.OSI_LEAD_ID := osi.OSI_LEAD_ID;
62 l_osi_rec.LEAD_ID := osi.LEAD_ID;
63 l_osi_rec.CVEHICLE := to_char(osi.CVEHICLE);
64 l_osi_rec.CNAME_ID := to_char(osi.CNAME_ID);
65 l_osi_rec.CONTR_DRAFTING_REQ := osi.CONTR_DRAFTING_REQ;
66 l_osi_rec.PRIORITY := osi.PRIORITY;
67 l_osi_rec.SENIOR_CONTR_PERSON_ID := to_char(osi.SENIOR_CONTR_PERSON_ID);
68 l_osi_rec.CONTR_SPEC_PERSON_ID := to_char(osi.CONTR_SPEC_PERSON_ID);
69 l_osi_rec.BOM_PERSON_ID := to_char(osi.BOM_PERSON_ID);
70 l_osi_rec.LEGAL_PERSON_ID := to_char(osi.LEGAL_PERSON_ID);
71 l_osi_rec.HIGHEST_APVL := osi.HIGHEST_APVL;
72 l_osi_rec.CURRENT_APVL_STATUS := osi.CURRENT_APVL_STATUS;
73 l_osi_rec.SUPPORT_APVL := osi.SUPPORT_APVL;
74 l_osi_rec.INTERNATIONAL_APVL := osi.INTERNATIONAL_APVL;
75 l_osi_rec.CREDIT_APVL := osi.CREDIT_APVL;
76 l_osi_rec.FIN_ESCROW_REQ := osi.FIN_ESCROW_REQ;
77 l_osi_rec.FIN_ESCROW_STATUS := osi.FIN_ESCROW_STATUS;
78 l_osi_rec.CSI_ROLLIN := osi.CSI_ROLLIN;
79 l_osi_rec.LICENCE_CREDIT_VER := osi.LICENCE_CREDIT_VER;
80 l_osi_rec.SUPPORT_CREDIT_VER := osi.SUPPORT_CREDIT_VER;
81 l_osi_rec.MD_DEAL_SUMMARY := osi.MD_DEAL_SUMMARY;
82 l_osi_rec.PROD_AVAIL_VER := osi.PROD_AVAIL_VER;
83 l_osi_rec.SHIP_LOCATION := osi.SHIP_LOCATION;
84 l_osi_rec.TAX_EXEMPT_CERT := osi.TAX_EXEMPT_CERT;
85 l_osi_rec.NL_REV_ALLOC_REQ := osi.NL_REV_ALLOC_REQ;
86 l_osi_rec.CONSULTING_CC := osi.CONSULTING_CC;
87 l_osi_rec.SENIOR_CONTR_NOTES := osi.SENIOR_CONTR_NOTES;
88 l_osi_rec.LEGAL_NOTES := osi.LEGAL_NOTES;
89 l_osi_rec.BOM_NOTES := osi.BOM_NOTES;
90 l_osi_rec.CONTR_NOTES := osi.CONTR_NOTES;
91 l_osi_rec.PO_FROM := osi.PO_FROM;
92 l_osi_rec.CONTR_TYPE := osi.CONTR_TYPE;
93 l_osi_rec.CONTR_STATUS := osi.CONTR_STATUS;
94 l_osi_rec.EXTRA_DOCS := to_char(osi.EXTRA_DOCS);
95 exit;
96 end loop;
97 for ovd in ovd_cur (p_lead_id) loop
98 ndx := ndx + 1;
99 l_osi_ovd_tbl(ndx).ovd_code := ovd.ovm_code;
100 l_osi_ovd_tbl(ndx).ovd_flag := null;
101 end loop;
102 for osi2 in osi2_cur (p_lead_id) loop
103 l_osi_rec.OPPY_NAME := osi2.OPPY_NAME;
104 l_osi_rec.CUST_NAME := osi2.CUST_NAME;
105 l_osi_rec.SITE_NAME := osi2.SITE_NAME;
106 exit;
107 end loop;
108 if l_osi_rec.lead_id = FND_API.G_MISS_NUM then
109 l_osi_rec.last_updated_by := null;
110 l_osi_rec.created_by := null;
111 l_osi_rec.last_update_login := null;
112 l_osi_rec.OSI_LEAD_ID := null;
113 l_osi_rec.LEAD_ID := null;
114 l_osi_rec.CVEHICLE := null;
115 l_osi_rec.CNAME_ID := null;
116 l_osi_rec.CONTR_DRAFTING_REQ := null;
117 l_osi_rec.PRIORITY := null;
118 l_osi_rec.SENIOR_CONTR_PERSON_ID := null;
119 l_osi_rec.CONTR_SPEC_PERSON_ID := null;
120 l_osi_rec.BOM_PERSON_ID := null;
121 l_osi_rec.LEGAL_PERSON_ID := null;
122 l_osi_rec.HIGHEST_APVL := null;
123 l_osi_rec.CURRENT_APVL_STATUS := null;
124 l_osi_rec.SUPPORT_APVL := null;
125 l_osi_rec.INTERNATIONAL_APVL := null;
126 l_osi_rec.CREDIT_APVL := null;
127 l_osi_rec.FIN_ESCROW_REQ := null;
128 l_osi_rec.FIN_ESCROW_STATUS := null;
129 l_osi_rec.CSI_ROLLIN := null;
130 l_osi_rec.LICENCE_CREDIT_VER := null;
131 l_osi_rec.SUPPORT_CREDIT_VER := null;
132 l_osi_rec.MD_DEAL_SUMMARY := null;
133 l_osi_rec.PROD_AVAIL_VER := null;
134 l_osi_rec.SHIP_LOCATION := null;
135 l_osi_rec.TAX_EXEMPT_CERT := null;
136 l_osi_rec.NL_REV_ALLOC_REQ := null;
137 l_osi_rec.CONSULTING_CC := null;
138 l_osi_rec.SENIOR_CONTR_NOTES := null;
139 l_osi_rec.LEGAL_NOTES := null;
140 l_osi_rec.BOM_NOTES := null;
141 l_osi_rec.CONTR_NOTES := null;
142 l_osi_rec.PO_FROM := null;
143 l_osi_rec.CONTR_TYPE := null;
144 l_osi_rec.CONTR_STATUS := null;
145 l_osi_rec.EXTRA_DOCS := null;
146 end if;
147 p_osi_rec := l_osi_rec;
148 p_osi_ovd_tbl := l_osi_ovd_tbl;
149 exception
150 when others then
151 l_osi_rec.legal_notes:= sqlerrm;
152 end osi_lead_fetch;
153 PROCEDURE osi_lead_update
154 ( p_api_version_number IN NUMBER,
155 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
156 p_commit IN VARCHAR2 := FND_API.G_FALSE,
157 p_osi_rec IN OSI_REC_TYPE,
158 p_osi_ovd_tbl IN OSI_OVD_TBL_TYPE,
159 x_return_status OUT VARCHAR2,
160 x_msg_count OUT VARCHAR2,
161 x_msg_data OUT VARCHAR2
162 ) is
163 cursor ovd_cur (lead_id_in in varchar2) is
164 select *
165 from as_osi_lead_ovl_all
166 where osi_lead_id = lead_id_in
167 order by ovm_code;
168 l_dummy number;
169 l_osi_rec OSI_REC_TYPE;
170 l_osi_ovd_tbl OSI_OVD_TBL_TYPE;
171 l_delete_flag boolean;
172 procedure rgmissc(p_inout in out varchar2) is
173 begin
174 if p_inout = FND_API.G_MISS_CHAR then
175 p_inout := null;
176 end if;
177 end rgmissc;
178 procedure rgmissn(p_inout in out number) is
179 begin
180 if p_inout = FND_API.G_MISS_NUM then
181 p_inout := null;
182 end if;
183 end rgmissn;
184 begin
185 l_osi_rec := p_osi_rec;
186 l_osi_ovd_tbl := p_osi_ovd_tbl;
187 rgmissc(l_osi_rec.last_updated_by);
188 rgmissc(l_osi_rec.created_by);
189 rgmissc(l_osi_rec.CVEHICLE);
190 rgmissc(l_osi_rec.CNAME_ID);
191 rgmissc(l_osi_rec.PO_FROM);
192 rgmissc(l_osi_rec.CONTR_TYPE);
193 rgmissc(l_osi_rec.CONTR_DRAFTING_REQ);
194 rgmissc(l_osi_rec.PRIORITY);
195 rgmissc(l_osi_rec.SENIOR_CONTR_person_ID);
196 rgmissc(l_osi_rec.CONTR_SPEC_person_ID);
197 rgmissc(l_osi_rec.BOM_person_ID);
198 rgmissc(l_osi_rec.LEGAL_person_ID);
199 rgmissc(l_osi_rec.HIGHEST_APVL);
200 rgmissc(l_osi_rec.CURRENT_APVL_STATUS);
201 rgmissc(l_osi_rec.SUPPORT_APVL);
202 rgmissc(l_osi_rec.INTERNATIONAL_APVL);
203 rgmissc(l_osi_rec.CREDIT_APVL);
204 rgmissc(l_osi_rec.FIN_ESCROW_REQ);
205 rgmissc(l_osi_rec.FIN_ESCROW_STATUS);
206 rgmissc(l_osi_rec.CSI_ROLLIN);
207 rgmissc(l_osi_rec.LICENCE_CREDIT_VER);
208 rgmissc(l_osi_rec.SUPPORT_CREDIT_VER);
209 rgmissc(l_osi_rec.MD_DEAL_SUMMARY);
210 rgmissc(l_osi_rec.PROD_AVAIL_VER);
211 rgmissc(l_osi_rec.SHIP_LOCATION);
212 rgmissc(l_osi_rec.TAX_EXEMPT_CERT);
213 rgmissc(l_osi_rec.NL_REV_ALLOC_REQ);
214 rgmissc(l_osi_rec.CONSULTING_CC);
215 rgmissc(l_osi_rec.SENIOR_CONTR_NOTES);
216 rgmissc(l_osi_rec.LEGAL_NOTES);
217 rgmissc(l_osi_rec.BOM_NOTES);
218 rgmissc(l_osi_rec.CONTR_NOTES);
219 rgmissc(l_osi_rec.CONTR_STATUS);
220 rgmissc(l_osi_rec.EXTRA_DOCS);
221 -- dbms_output.put_line('{{'||l_osi_ovd_tbl.count||'}}');
222 if l_osi_ovd_tbl.count > 0 then
223 for i in 1..l_osi_ovd_tbl.count loop
224 rgmissc(l_osi_ovd_tbl(i).ovd_flag);
225 rgmissc(l_osi_ovd_tbl(i).ovd_code);
226 -- l_osi_rec.CONTR_NOTES := l_osi_rec.CONTR_NOTES || '{'||i||','||l_osi_ovd_tbl(i).ovd_flag||','||l_osi_ovd_tbl(i).ovd_code||'}';
227 -- dbms_output.put_line('{'||i||'/'||l_osi_ovd_tbl.count||','||l_osi_ovd_tbl(i).ovd_flag||','||l_osi_ovd_tbl(i).ovd_code||'}');
228 if nvl(upper(l_osi_ovd_tbl(i).ovd_flag),'Y') = 'Y' then
229 l_osi_ovd_tbl(i).ovd_flag := 'Y';
230 else
231 l_osi_ovd_tbl(i).ovd_flag := 'N';
232 end if;
233 end loop;
234 end if;
235 begin
236 select osi_lead_id
237 into l_dummy
238 from as_osi_leads_all
239 where osi_lead_id = p_osi_rec.lead_id;
240 update as_osi_leads_all
241 set
242 last_update_date = sysdate
243 ,last_updated_by = nvl(to_number(l_osi_rec.last_updated_by),1)
244 ,OSI_LEAD_ID = l_osi_rec.OSI_LEAD_ID
245 ,LEAD_ID = l_osi_rec.LEAD_ID
246 ,CVEHICLE = to_number(l_osi_rec.CVEHICLE)
247 ,CNAME_ID = to_number(l_osi_rec.CNAME_ID)
248 ,CONTR_DRAFTING_REQ = l_osi_rec.CONTR_DRAFTING_REQ
249 ,PRIORITY = l_osi_rec.PRIORITY
250 ,SENIOR_CONTR_PERSON_ID = to_number(l_osi_rec.SENIOR_CONTR_PERSON_ID)
251 ,CONTR_SPEC_PERSON_ID = to_number(l_osi_rec.CONTR_SPEC_PERSON_ID)
252 ,BOM_PERSON_ID = to_number(l_osi_rec.BOM_PERSON_ID)
253 ,LEGAL_PERSON_ID = to_number(l_osi_rec.LEGAL_PERSON_ID)
254 ,HIGHEST_APVL = l_osi_rec.HIGHEST_APVL
255 ,CURRENT_APVL_STATUS = l_osi_rec.CURRENT_APVL_STATUS
256 ,SUPPORT_APVL = l_osi_rec.SUPPORT_APVL
257 ,INTERNATIONAL_APVL = l_osi_rec.INTERNATIONAL_APVL
258 ,CREDIT_APVL = l_osi_rec.CREDIT_APVL
259 ,FIN_ESCROW_REQ = l_osi_rec.FIN_ESCROW_REQ
260 ,FIN_ESCROW_STATUS = l_osi_rec.FIN_ESCROW_STATUS
261 ,CSI_ROLLIN = l_osi_rec.CSI_ROLLIN
262 ,LICENCE_CREDIT_VER = l_osi_rec.LICENCE_CREDIT_VER
263 ,SUPPORT_CREDIT_VER = l_osi_rec.SUPPORT_CREDIT_VER
264 ,MD_DEAL_SUMMARY = l_osi_rec.MD_DEAL_SUMMARY
265 ,PROD_AVAIL_VER = l_osi_rec.PROD_AVAIL_VER
266 ,SHIP_LOCATION = l_osi_rec.SHIP_LOCATION
267 ,TAX_EXEMPT_CERT = l_osi_rec.TAX_EXEMPT_CERT
268 ,NL_REV_ALLOC_REQ = l_osi_rec.NL_REV_ALLOC_REQ
269 ,CONSULTING_CC = l_osi_rec.CONSULTING_CC
270 ,SENIOR_CONTR_NOTES = l_osi_rec.SENIOR_CONTR_NOTES
271 ,LEGAL_NOTES = l_osi_rec.LEGAL_NOTES
272 ,BOM_NOTES = l_osi_rec.BOM_NOTES
273 ,CONTR_NOTES = l_osi_rec.CONTR_NOTES
274 ,PO_FROM = l_osi_rec.PO_FROM
275 ,CONTR_TYPE = l_osi_rec.CONTR_TYPE
276 ,CONTR_STATUS = l_osi_rec.CONTR_STATUS
277 ,EXTRA_DOCS = to_number(l_osi_rec.EXTRA_DOCS)
278 where osi_lead_id = l_osi_rec.osi_lead_id;
279 exception
280 when no_data_found then
281 insert into as_osi_leads_all(
282 creation_date
283 ,created_by
284 ,last_update_date
285 ,last_updated_by
286 ,last_update_login
287 ,OSI_LEAD_ID
288 ,LEAD_ID
289 ,CVEHICLE
290 ,CNAME_ID
291 ,CONTR_DRAFTING_REQ
292 ,PRIORITY
293 ,SENIOR_CONTR_PERSON_ID
294 ,CONTR_SPEC_PERSON_ID
295 ,BOM_PERSON_ID
296 ,LEGAL_PERSON_ID
297 ,HIGHEST_APVL
298 ,CURRENT_APVL_STATUS
299 ,SUPPORT_APVL
300 ,INTERNATIONAL_APVL
301 ,CREDIT_APVL
302 ,FIN_ESCROW_REQ
303 ,FIN_ESCROW_STATUS
304 ,CSI_ROLLIN
305 ,LICENCE_CREDIT_VER
306 ,SUPPORT_CREDIT_VER
307 ,MD_DEAL_SUMMARY
308 ,PROD_AVAIL_VER
309 ,SHIP_LOCATION
310 ,TAX_EXEMPT_CERT
311 ,NL_REV_ALLOC_REQ
312 ,CONSULTING_CC
313 ,SENIOR_CONTR_NOTES
314 ,LEGAL_NOTES
315 ,BOM_NOTES
316 ,CONTR_NOTES
317 ,PO_FROM
318 ,CONTR_TYPE
319 ,CONTR_STATUS
320 ,EXTRA_DOCS
321
322 ) values (
323 sysdate
324 ,nvl(to_number(nvl(l_osi_rec.created_by,l_osi_rec.last_updated_by)),1)
325 ,sysdate
326 ,nvl(to_number(nvl(l_osi_rec.created_by,l_osi_rec.last_updated_by)),1)
327 ,1
328 ,l_osi_rec.OSI_LEAD_ID
329 ,l_osi_rec.LEAD_ID
330 ,to_number(l_osi_rec.CVEHICLE)
331 ,to_number(l_osi_rec.CNAME_ID)
332 ,l_osi_rec.CONTR_DRAFTING_REQ
333 ,l_osi_rec.PRIORITY
334 ,to_number(l_osi_rec.SENIOR_CONTR_PERSON_ID)
335 ,to_number(l_osi_rec.CONTR_SPEC_PERSON_ID)
336 ,to_number(l_osi_rec.BOM_PERSON_ID)
337 ,to_number(l_osi_rec.LEGAL_PERSON_ID)
338 ,l_osi_rec.HIGHEST_APVL
339 ,l_osi_rec.CURRENT_APVL_STATUS
340 ,l_osi_rec.SUPPORT_APVL
341 ,l_osi_rec.INTERNATIONAL_APVL
342 ,l_osi_rec.CREDIT_APVL
343 ,l_osi_rec.FIN_ESCROW_REQ
344 ,l_osi_rec.FIN_ESCROW_STATUS
345 ,l_osi_rec.CSI_ROLLIN
346 ,l_osi_rec.LICENCE_CREDIT_VER
347 ,l_osi_rec.SUPPORT_CREDIT_VER
348 ,l_osi_rec.MD_DEAL_SUMMARY
349 ,l_osi_rec.PROD_AVAIL_VER
350 ,l_osi_rec.SHIP_LOCATION
351 ,l_osi_rec.TAX_EXEMPT_CERT
352 ,l_osi_rec.NL_REV_ALLOC_REQ
353 ,l_osi_rec.CONSULTING_CC
354 ,l_osi_rec.SENIOR_CONTR_NOTES
355 ,l_osi_rec.LEGAL_NOTES
356 ,l_osi_rec.BOM_NOTES
357 ,l_osi_rec.CONTR_NOTES
358 ,l_osi_rec.PO_FROM
359 ,l_osi_rec.CONTR_TYPE
360 ,l_osi_rec.CONTR_STATUS
361 ,to_number(l_osi_rec.EXTRA_DOCS)
362 );
363 end;
364 if l_osi_ovd_tbl.count > 0 then
365 for ovd in ovd_cur(l_osi_rec.osi_lead_id) loop
366 l_delete_flag := TRUE;
367 for i in 1..l_osi_ovd_tbl.count loop
368 if l_osi_ovd_tbl(i).ovd_code = ovd.ovm_code then
369 if l_osi_ovd_tbl(i).ovd_flag <> 'N' then
370 l_osi_ovd_tbl(i).ovd_flag := 'X';
371 l_delete_flag := FALSE;
372 end if;
373 end if;
374 end loop;
375 if l_delete_flag then
376 delete from as_osi_lead_ovl_all
377 where osi_lead_id = ovd.osi_lead_id
378 and ovm_code = ovd.ovm_code;
379 end if;
380 end loop;
381 for i in 1..l_osi_ovd_tbl.count loop
382 if l_osi_ovd_tbl(i).ovd_flag = 'Y' and l_osi_ovd_tbl(i).ovd_code is not null then
383 begin
384 insert into as_osi_lead_ovl_all(
385 creation_date
386 ,created_by
387 ,last_update_date
388 ,last_updated_by
389 ,OSI_LEAD_ID
390 ,OVM_CODE
391 ) values (
392 sysdate
393 ,nvl(to_number(nvl(l_osi_rec.created_by,l_osi_rec.last_updated_by)),1)
394 ,sysdate
395 ,nvl(to_number(nvl(l_osi_rec.created_by,l_osi_rec.last_updated_by)),1)
396 ,l_osi_rec.OSI_LEAD_ID
397 ,l_osi_ovd_tbl(i).ovd_code);
398 exception
399 when others then
400 null;
401 end;
402 end if;
403 end loop;
404 end if;
405 commit;
406 end osi_lead_update;
407 PROCEDURE osi_cvb_fetch
408 ( p_api_version_number IN NUMBER,
409 p_osi_cvb_tbl out OSI_CVB_TBL_TYPE
410 ) is
411 cursor cvb_cur is
412 select cvehicle, vehicle
413 from as_osi_contr_vhcl_base
414 where nvl(enabled_flag,'Y') = 'Y'
415 order by 1;
416 l_osi_cvb_tbl OSI_CVB_TBL_TYPE := G_MISS_OSI_CVB_TBL;
417 ndx binary_integer := 0;
418 begin
419 for cvb in cvb_cur loop
420 ndx := ndx + 1;
421 l_osi_cvb_tbl(ndx).cvehicle := to_char(cvb.cvehicle);
422 l_osi_cvb_tbl(ndx).vehicle := cvb.vehicle;
423 end loop;
424 p_osi_cvb_tbl := l_osi_cvb_tbl;
425 end osi_cvb_fetch;
426 PROCEDURE osi_cnb_fetch
427 ( p_api_version_number IN NUMBER,
428 p_osi_cnb_tbl out OSI_CNB_TBL_TYPE
429 ) is
430 cursor cnb_cur is
431 select CNAME_ID, CONTR_NAME, CVEHICLE
432 from as_osi_contr_names_base
433 where nvl(enabled_flag,'Y') = 'Y'
434 order by CVEHICLE, CONTR_NAME;
435 l_osi_cnb_tbl OSI_CNB_TBL_TYPE := G_MISS_OSI_CNB_TBL;
436 ndx binary_integer := 0;
437 begin
438 for cnb in cnb_cur loop
439 ndx := ndx + 1;
440 l_osi_cnb_tbl(ndx).CNAME_ID := to_char(cnb.CNAME_ID);
441 l_osi_cnb_tbl(ndx).CONTR_NAME := cnb.CONTR_NAME;
442 l_osi_cnb_tbl(ndx).CVEHICLE := to_char(cnb.CVEHICLE);
443 end loop;
444 p_osi_cnb_tbl := l_osi_cnb_tbl;
445 end osi_cnb_fetch;
446 PROCEDURE osi_lkp_fetch
447 ( p_api_version_number IN NUMBER,
448 p_osi_lkp_type in varchar2,
449 p_osi_lkp_tbl out OSI_LKP_TBL_TYPE
450 ) is
451 cursor lkp_cur (lkp_type_in in varchar2) is
452 select lkp_type, lkp_code, lkp_value
453 from as_osi_lookup
454 where (lkp_type = upper(lkp_type_in)
455 or lkp_type_in = 'ALL')
456 and nvl(enabled_flag,'Y') = 'Y'
457 order by 1,2;
458 l_osi_lkp_tbl OSI_LKP_TBL_TYPE := G_MISS_OSI_LKP_TBL;
459 CURSOR state_cur IS
460 select distinct alv.location_segment_user_value lkp_code, alv.location_segment_description lkp_value
461 from ar_location_values alv,
462 ar_system_parameters asp
463 where alv.location_structure_id = asp.location_structure_id
464 and alv.parent_segment_id is null
465 order by 1;
466 ndx binary_integer := 0;
467 begin
468 if p_osi_lkp_type <> 'SHIP_LOCATION' then
469 for lkp in lkp_cur(p_osi_lkp_type) loop
470 ndx := ndx + 1;
471 l_osi_lkp_tbl(ndx).lkp_type := lkp.lkp_type;
472 l_osi_lkp_tbl(ndx).lkp_code := lkp.lkp_code;
473 l_osi_lkp_tbl(ndx).lkp_value := lkp.lkp_value;
474 end loop;
475 end if;
476 if p_osi_lkp_type in ('SHIP_LOCATION','ALL') then
477 for lkp in state_cur loop
478 ndx := ndx + 1;
479 l_osi_lkp_tbl(ndx).lkp_type := 'SHIP_LOCATION';
480 l_osi_lkp_tbl(ndx).lkp_code := lkp.lkp_code;
481 l_osi_lkp_tbl(ndx).lkp_value := lkp.lkp_value;
482 end loop;
483 end if;
484 p_osi_lkp_tbl := l_osi_lkp_tbl;
485 end osi_lkp_fetch;
486 PROCEDURE osi_nam_fetch
487 ( p_api_version_number IN NUMBER,
488 p_osi_nam_type in varchar2,
489 p_osi_nam_tbl out OSI_NAM_TBL_TYPE
490 ) is
491 l_osi_nam_tbl OSI_NAM_TBL_TYPE := G_MISS_OSI_NAM_TBL;
492 l_nam_type varchar2(30) := null;
493 l_nam_value varchar2(30);
494 l_last_name varchar2(40);
495 l_email_address varchar2(240);
496 l_supervisor_id number;
497 ndx binary_integer := 0;
498 cursor nam_cur (supervisor_id_in in number, sales_group_id_in in number) is
499 select ppf.person_id person_id, max(ppf.email_address) email_address, max(ppf.last_name)
500 from per_people_f ppf,
501 as_salesforce as1
502 where as1.sales_group_id = sales_group_id_in
503 and as1.employee_person_id is not null
504 and as1.employee_person_id = ppf.person_id
505 and sysdate between ppf.effective_start_date and ppf.effective_end_date
506 and as1.status_code = 'A'
507 group by ppf.person_id
508 UNION
509 select ppf.person_id person_id, max(ppf.email_address) email_address, max(ppf.last_name)
510 from per_people_f ppf
511 where ppf.person_id = supervisor_id_in
512 and sysdate between ppf.effective_start_date and ppf.effective_end_date
513 group by ppf.person_id
514 ORDER BY 3,2;
515 cursor sg_cur (last_name_in in varchar2, email_address_in in varchar2) is
516 select max(ppf.person_id) supervisor_id, max(asg.sales_group_id) sales_group_id
517 from per_people_f ppf
518 ,as_sales_groups asg
519 where ppf.last_name = last_name_in
520 and upper(ppf.email_address) = email_address_in
521 and trunc(nvl(ppf.effective_start_date,sysdate)) <= trunc(sysdate)
522 and trunc(nvl(ppf.effective_end_date,sysdate)) >= trunc(sysdate)
523 and ppf.person_id = asg.manager_person_id;
524 begin
525 for i in 1..4 loop
526 if i = 1 then
527 l_nam_type := 'LEGAL';
528 l_last_name := 'Blumberg';
529 l_email_address := 'JBLUMBER';
530 elsif i = 2 then
531 l_nam_type := 'BOM';
532 l_last_name := 'Ferguson';
533 l_email_address := 'LFERGUSO';
534 elsif i = 3 then
535 l_nam_type := 'SENIOR_CONTR';
536 l_last_name := 'Zettler';
537 l_email_address := 'JZETTLER';
538 elsif i = 4 then
539 l_nam_type := 'CONTR_SPEC';
540 l_last_name := 'Rowzee';
541 l_email_address := 'JROWZEE';
542 end if;
543 if p_osi_nam_type = 'ALL' or p_osi_nam_type = l_nam_type then
544 begin
545 for sg in sg_cur(l_last_name, l_email_address) loop
546 for nam in nam_cur(sg.supervisor_id, sg.sales_group_id) loop
547 ndx := ndx+1;
548 l_osi_nam_tbl(ndx).nam_type := l_nam_type;
549 l_osi_nam_tbl(ndx).nam_id := nam.person_id;
550 l_osi_nam_tbl(ndx).nam_value := nam.email_address;
551 end loop;
552 end loop;
553 exception
554 when others then
555 null;
556 end;
557 end if;
558 end loop;
559 p_osi_nam_tbl := l_osi_nam_tbl;
560 end osi_nam_fetch;
561 PROCEDURE osi_ccs_fetch
562 ( p_api_version_number IN NUMBER,
563 p_osi_ccs_tbl out OSI_CCS_TBL_TYPE
564 ) is
565 cursor ccs_cur is
566 select cc, center_name
567 from as_osi_cons_ccs_base
568 where nvl(enabled_flag,'Y') = 'Y'
569 order by 1;
570 l_osi_ccs_tbl OSI_CCS_TBL_TYPE := G_MISS_OSI_CCS_TBL;
571 ndx binary_integer := 0;
572 begin
573 for ccs in ccs_cur loop
574 ndx := ndx + 1;
575 l_osi_ccs_tbl(ndx).cc := ccs.cc;
576 l_osi_ccs_tbl(ndx).center_name := ccs.center_name;
577 end loop;
578 p_osi_ccs_tbl := l_osi_ccs_tbl;
579 end osi_ccs_fetch;
580 PROCEDURE osi_ovm_fetch
581 ( p_api_version_number IN NUMBER,
582 p_osi_ovm_tbl out OSI_OVM_TBL_TYPE
583 ) is
584 cursor ovm_cur is
585 select ovm_code, ovm_value
586 from as_osi_overlay_base
587 where nvl(enabled_flag,'Y') = 'Y'
588 order by 2;
589 l_osi_ovm_tbl OSI_OVM_TBL_TYPE := G_MISS_OSI_OVM_TBL;
590 ndx binary_integer := 0;
591 begin
592 for ovm in ovm_cur loop
593 ndx := ndx + 1;
594 l_osi_ovm_tbl(ndx).ovm_code := ovm.ovm_code;
595 l_osi_ovm_tbl(ndx).ovm_value := ovm.ovm_value;
596 end loop;
597 p_osi_ovm_tbl := l_osi_ovm_tbl;
598 end osi_ovm_fetch;
599 PROCEDURE osi_lead_fetch_all
600 ( p_api_version_number IN NUMBER,
601 p_lead_id in VARCHAR2,
602 p_osi_rec out OSI_REC_TYPE ,
603 p_osi_cvb_tbl out OSI_CVB_TBL_TYPE ,
604 p_osi_cnb_tbl out OSI_CNB_TBL_TYPE ,
605 p_osi_lkp_tbl out OSI_LKP_TBL_TYPE,
606 p_osi_nam_tbl out OSI_NAM_TBL_TYPE,
607 p_osi_ccs_tbl out OSI_CCS_TBL_TYPE,
608 p_osi_ovd_tbl out OSI_OVD_TBL_TYPE,
609 p_osi_ovm_tbl out OSI_OVM_TBL_TYPE
610 ) is
611 l_osi_nam_tbl OSI_NAM_TBL_TYPE := G_MISS_OSI_NAM_TBL;
612 l_osi_cvb_tbl OSI_CVB_TBL_TYPE := G_MISS_OSI_CVB_TBL;
613 l_osi_cnb_tbl OSI_CNB_TBL_TYPE := G_MISS_OSI_CNB_TBL;
614 l_osi_lkp_tbl OSI_LKP_TBL_TYPE := G_MISS_OSI_LKP_TBL;
615 l_osi_ccs_tbl OSI_CCS_TBL_TYPE := G_MISS_OSI_CCS_TBL;
616 l_osi_ovm_tbl OSI_OVM_TBL_TYPE := G_MISS_OSI_OVM_TBL;
617 l_osi_ovd_tbl OSI_OVD_TBL_TYPE := G_MISS_OSI_OVD_TBL;
618 l_osi_rec OSI_REC_TYPE := G_MISS_OSI_REC;
619 begin
620 osi_lead_fetch(p_api_version_number,p_lead_id,l_osi_rec,l_osi_ovd_tbl);
621 osi_cvb_fetch(p_api_version_number,l_osi_cvb_tbl);
622 osi_cnb_fetch(p_api_version_number,l_osi_cnb_tbl);
623 osi_lkp_fetch(p_api_version_number,'ALL',l_osi_lkp_tbl);
624 osi_nam_fetch(p_api_version_number,'ALL',l_osi_nam_tbl);
625 osi_ccs_fetch(p_api_version_number,l_osi_ccs_tbl);
626 osi_ovm_fetch(p_api_version_number,l_osi_ovm_tbl);
627 p_osi_cvb_tbl := l_osi_cvb_tbl;
628 p_osi_cnb_tbl := l_osi_cnb_tbl;
629 p_osi_lkp_tbl := l_osi_lkp_tbl;
630 p_osi_nam_tbl := l_osi_nam_tbl;
631 p_osi_ccs_tbl := l_osi_ccs_tbl;
632 p_osi_ovd_tbl := l_osi_ovd_tbl;
633 p_osi_ovm_tbl := l_osi_ovm_tbl;
634 p_osi_rec := l_osi_rec;
635 end osi_lead_fetch_all;
636 PROCEDURE osi_lookup_fetch_all
637 ( p_api_version_number IN NUMBER,
638 p_osi_cvb_tbl out OSI_CVB_TBL_TYPE ,
639 p_osi_cnb_tbl out OSI_CNB_TBL_TYPE ,
640 p_osi_lkp_tbl out OSI_LKP_TBL_TYPE,
641 p_osi_nam_tbl out OSI_NAM_TBL_TYPE,
642 p_osi_ccs_tbl out OSI_CCS_TBL_TYPE,
643 p_osi_ovm_tbl out OSI_OVM_TBL_TYPE
644 ) is
645 l_osi_nam_tbl OSI_NAM_TBL_TYPE := G_MISS_OSI_NAM_TBL;
646 l_osi_cvb_tbl OSI_CVB_TBL_TYPE := G_MISS_OSI_CVB_TBL;
647 l_osi_cnb_tbl OSI_CNB_TBL_TYPE := G_MISS_OSI_CNB_TBL;
648 l_osi_lkp_tbl OSI_LKP_TBL_TYPE := G_MISS_OSI_LKP_TBL;
649 l_osi_ccs_tbl OSI_CCS_TBL_TYPE := G_MISS_OSI_CCS_TBL;
650 l_osi_ovm_tbl OSI_OVM_TBL_TYPE := G_MISS_OSI_OVM_TBL;
651 begin
652 osi_cvb_fetch(p_api_version_number,l_osi_cvb_tbl);
653 osi_cnb_fetch(p_api_version_number,l_osi_cnb_tbl);
654 osi_lkp_fetch(p_api_version_number,'ALL',l_osi_lkp_tbl);
655 osi_nam_fetch(p_api_version_number,'ALL',l_osi_nam_tbl);
656 osi_ccs_fetch(p_api_version_number,l_osi_ccs_tbl);
657 osi_ovm_fetch(p_api_version_number,l_osi_ovm_tbl);
658 p_osi_cvb_tbl := l_osi_cvb_tbl;
659 p_osi_cnb_tbl := l_osi_cnb_tbl;
660 p_osi_lkp_tbl := l_osi_lkp_tbl;
661 p_osi_nam_tbl := l_osi_nam_tbl;
662 p_osi_ccs_tbl := l_osi_ccs_tbl;
663 p_osi_ovm_tbl := l_osi_ovm_tbl;
664 end osi_lookup_fetch_all;
665 FUNCTION osi_get_button_html
666 ( p_api_version_number IN NUMBER
667 ) return varchar2 is
668 out_text varchar2(2000);
669 begin
670 out_text :=
671 '<input type="button" value="OSI Additions" onClick = "osiExt();">';
672 return out_text;
673 end osi_get_button_html;
674 FUNCTION osi_get_js_html
675 ( p_api_version_number IN NUMBER
676 ) return varchar2 is
677 out_text varchar2(2000);
678 begin
679 out_text :=
680 'function osiExt() {'||
681 'osi_ext_win = window.open('||
682 '"asxosics.jsp?p_lead_id="+document.forms[0].p_lead_id.value,'||
683 '"OSI",'||
684 '"resizable=yes,menubar=yes,scrollbars=yes,width=780,height=450,screenX=200,screenY=150");'||
685 'osi_ext_win.opener = self;}';
686 return out_text;
687 end osi_get_js_html;
688 end AS_OSI_LEAD_PUB;