1 package body icx_supp_custom as
2 /* $Header: ICXSUPCB.pls 115.2 2001/10/15 13:51:12 pkm ship $ */
3
4
5 -- This function determines if a supplier record already exists.
6 -- Positive number indicates single match and SUPPLIER_ID returned.
7 -- 0 indicates no match
8 -- Negative number indicates multiple matches
9
10 function validateSupplier(p_supplier in varchar2,
11 p_addr1 in varchar2,
12 p_addr2 in varchar2,
13 p_addr3 in varchar2,
14 p_city in varchar2,
15 p_province in varchar2,
16 p_county in varchar2,
17 p_state in varchar2,
18 p_zip in varchar2,
19 p_country in varchar2)
20 return number is
21
22 l_existing_supplier number;
23 l_vendor_id number;
24
25 cursor existing_supplier is
26 select count(*)
27 from PO_VENDORS
28 where upper(vendor_name) = upper(p_supplier);
29
30 begin
31
32 open existing_supplier;
33 fetch existing_supplier into l_existing_supplier;
34 close existing_supplier;
35
36 if l_existing_supplier = 1
37 then
38 select vendor_id
39 into l_vendor_id
40 from PO_VENDORS
41 where upper(vendor_name) = upper(p_supplier);
42 elsif l_existing_supplier = 0 or l_existing_supplier is null
43 then
44 l_vendor_id := 0;
45 else
46 l_vendor_id := l_existing_supplier * -1;
47 end if;
48
49 return l_vendor_id;
50 end;
51
52 --
53 -- NAME
54 -- Set_Domain
55 --
56 -- PURPOSE
57 -- procedure to determine the domain for web user account name
58 -- based on customer information for business partner customer registration.
59 --
60 -- PARAMETERS
61 -- p_username - Requestor's preferred web account user name
62 -- e.g. jdoe
63 -- p_supplier_id - Primary to PO_VENDORS, only valid if greater than 0
64 -- p_email_address - Email address supplier by requestor
65 -- p_new_username - web account user name including set domain name
66 -- e.g. jdoe@oracle (@oracle is the doamin name)
67 -- NOTES
68 procedure setDomain(p_username in varchar2,
69 p_supplier_id in number,
70 p_email_address in varchar2,
71 p_new_username out varchar2) is
72
73 l_domain_name varchar2(100);
74
75 -- Note, Username cannot exceed 100 characters total.
76
77 begin
78
79 -- initialize to NULL
80 -- expecting to be customized based on
81 -- company data; p_supplier_id, only if > 0
82 -- email address; p_email_address
83 -- e.g. domain name can be '@oracle' etc..
84 l_domain_name := NULL;
85 --
86 p_new_username := p_username || l_domain_name;
87 end;
88
89
90 -- This function determines if a supplier contact record already exists.
91 -- Positive number indicates single match and VENDOR_CONTACT_ID returned.
92 -- 0 indicates no match
93 -- Negative number indicates multiple matches
94
95 function validateContact(p_supplier_id in number,
96 p_first_name in varchar2,
97 p_last_name in varchar2,
98 p_phone_number in varchar2,
99 p_mail_stop in varchar2,
100 p_addr1 in varchar2,
101 p_addr2 in varchar2,
102 p_addr3 in varchar2,
103 p_city in varchar2,
104 p_province in varchar2,
105 p_county in varchar2,
106 p_state in varchar2,
107 p_zip in varchar2,
108 p_country in varchar2)
109 return number is
110
111 l_existing_contact number;
112 l_contact_id number;
113
114 cursor existing_contact is
115 select count(*)
116 from PO_VENDOR_CONTACTS
117 where upper(last_name) = upper(p_last_name)
118 and upper(first_name) = upper(p_first_name);
119
120 cursor existing_contact_by_vendor is
121 select count(*)
122 from PO_VENDOR_CONTACTS b,
123 PO_VENDOR_SITES_all a
124 where a.VENDOR_ID = p_supplier_id
125 and a.VENDOR_SITE_ID = b.VENDOR_SITE_ID
126 and b.INACTIVE_DATE is null
127 and upper(b.last_name) = upper(p_last_name)
128 and upper(b.first_name) = upper(p_first_name);
129
130 begin
131
132 if p_supplier_id <= 0
133 then
134 open existing_contact;
135 fetch existing_contact into l_existing_contact;
136 close existing_contact;
137
138 if l_existing_contact = 1
139 then
140 select vendor_contact_id
141 into l_contact_id
142 from PO_VENDOR_CONTACTS
143 where upper(last_name) = upper(p_last_name)
144 and upper(first_name) = upper(p_first_name);
145
146 return l_contact_id;
147 else
148 l_existing_contact := l_existing_contact * -1;
149
150 return l_existing_contact;
151 end if;
152 else
153 open existing_contact_by_vendor;
154 fetch existing_contact_by_vendor into l_existing_contact;
155 close existing_contact_by_vendor;
156
157 if l_existing_contact = 1
158 then
159 select vendor_contact_id
160 into l_contact_id
161 from PO_VENDOR_CONTACTS b,
162 PO_VENDOR_SITES_all a
163 where a.VENDOR_ID = p_supplier_id
164 and a.VENDOR_SITE_ID = b.VENDOR_SITE_ID
165 and b.INACTIVE_DATE is null
166 and upper(b.last_name) = upper(p_last_name)
167 and upper(b.first_name) = upper(p_first_name);
168
169 return l_contact_id;
170 else
171 l_existing_contact := l_existing_contact * -1;
172
173 return l_existing_contact;
174 end if;
175 end if;
176
177 end;
178
179 -- procedure to determin the next approver to be routed for approving customer
180 -- registration.
181
182 procedure GetApprover (p_supplier_id IN NUMBER,
183 p_contact_id IN NUMBER,
184 p_user_id IN NUMBER,
185 p_approver_id IN OUT NUMBER,
186 p_approver_name IN OUT VARCHAR2) is
187
188 begin
189
190 -- Check to see if User has self approved then set to next approver.
191
192 if p_user_id = p_approver_id
193 then
194 -- Default to SYSADMIN
195 p_approver_id := 0;
196 p_approver_name := 'SYSADMIN';
197 else
198 -- Default to SYSADMIN
199 p_approver_id := 0;
200 p_approver_name := 'SYSADMIN';
201 end if;
202
203 end;
204
205 -- procedure to determin the next approver to be routed for approving customer
206 -- registration.
207
208 procedure GetContactSelector (p_supplier_id IN NUMBER,
209 p_approver_id IN OUT NUMBER,
210 p_approver_name IN OUT VARCHAR2) is
211
212 begin
213
214
215 -- Default to SYSADMIN
216 p_approver_id := 0;
217 p_approver_name := 'SYSADMIN';
218
219 end;
220
221
222 procedure GetAcctAdmin(p_supplier_id in number,
223 p_admin_id out number,
224 p_admin_name out varchar2,
225 p_display_admin_name out varchar2) is
226 begin
227
228 -- Default to SYSADMIN
229 p_admin_id := 0;
230 p_admin_name := 'SYSADMIN';
231 p_display_admin_name := 'sysadmin';
232
233 end;
234
235 -- function to determine if the requestor/preparer can approve without selecting
236 -- an approver
237 function VerifySelfApproval(p_supplier_id in number)
238 return BOOLEAN is
239 begin
240
241 return FALSE;
242
243 end;
244
245 -- function to determine if the current approver with the employee id(same as
246 -- forward to id in workflow) has the authorty to approve without further routing
247 -- of the registration request to another approver
248 function VerifyAuthority(p_supplier_id in number,
249 p_approver_id in number)
250 return BOOLEAN is
251
252 begin
253
254 return TRUE;
255
256 end;
257
258 end icx_supp_custom;
[Home] [Help]