1 PACKAGE BODY GMF_AP_GET_VENDOR_CONTACTS as
2 /* $Header: gmfvndcb.pls 115.0 99/07/16 04:26:05 porting shi $ */
3 cursor cur_ap_get_vendor_contacts
4 (st_date date, en_date date,
5 vndor_contact_id number, vndr_sit_id number) is
6 select VENDOR_CONTACT_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
7 VENDOR_SITE_ID,
8 LAST_UPDATE_LOGIN,
9 CREATION_DATE, CREATED_BY, INACTIVE_DATE,
10 FIRST_NAME, MIDDLE_NAME, LAST_NAME, PREFIX, TITLE, MAIL_STOP,
11 AREA_CODE, PHONE
12
13 from PO_VENDOR_CONTACTS
14
15 where VENDOR_CONTACT_ID = nvl(vndor_contact_id, VENDOR_CONTACT_ID)
16 and VENDOR_SITE_ID = nvl(vndr_sit_id, VENDOR_SITE_ID)
17 and last_update_date between
18 nvl(st_date, last_update_date)
19 and nvl(en_date, last_update_date);
20
21 procedure AP_GET_VENDOR_CONTACTS
22 (st_date in out date, en_date in out date,
23 vndor_contact_id in out number,
24 vndr_sit_id in out number,
25 lst_updt_dt out date, lst_updt_by out number,
26 lst_updt_login out number,
27 create_dat out date, create_by out number, inact_dt out
28 date, f_nam out varchar2, m_nam out varchar2, l_nam out varchar2,
29 prfx out varchar2, ttle out varchar2, ml_stop out varchar2, ar_cd
30 out varchar2, ph out varchar2,
31 row_to_fetch in out number, error_status out number )
32 is
33
34 begin
35 IF NOT cur_ap_get_vendor_contacts%ISOPEN THEN
36 OPEN cur_ap_get_vendor_contacts(st_date, en_date,
37 vndor_contact_id, vndr_sit_id);
38 END IF;
39
40
41
42 fetch cur_ap_get_vendor_contacts
43 into vndor_contact_id,
44 lst_updt_dt, lst_updt_by,
45 vndr_sit_id,
46 lst_updt_login,
47 create_dat, create_by, inact_dt,
48 f_nam, m_nam, l_nam,
49 prfx, ttle, ml_stop, ar_cd, ph;
50
51 if cur_ap_get_vendor_contacts%NOTFOUND then
52 error_status := 100;
53 end if;
54 if cur_ap_get_vendor_contacts%NOTFOUND or row_to_fetch = 1 THEN
55 CLOSE cur_ap_get_vendor_contacts;
56 end if;
57
58
59 exception
60 when others then
61 error_status := SQLCODE;
62
63
64 end AP_GET_VENDOR_CONTACTS;
65 END GMF_AP_GET_VENDOR_CONTACTS;
[Home] [Help]