1 package body pos_supp_custom as
2 /* $Header: POSSUPCB.pls 115.2 2002/03/14 16:22:49 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) like 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) like 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
119 cursor existing_contact_by_vendor is
120 select count(*)
121 from PO_VENDOR_CONTACTS b,
122 PO_VENDOR_SITES_all a
123 where a.VENDOR_ID = p_supplier_id
124 and a.VENDOR_SITE_ID = b.VENDOR_SITE_ID
125 and upper(last_name) = upper(p_last_name);
126
127 begin
128
129 if p_supplier_id <= 0
130 then
131 open existing_contact;
132 fetch existing_contact into l_existing_contact;
133 close existing_contact;
134
135 if l_existing_contact = 1
136 then
137 select vendor_contact_id
138 into l_contact_id
139 from PO_VENDOR_CONTACTS
140 where upper(last_name) = upper(p_last_name);
141
142 return l_contact_id;
143 else
144 l_existing_contact := l_existing_contact * -1;
145
146 return l_existing_contact;
147 end if;
148 else
149 open existing_contact_by_vendor;
150 fetch existing_contact_by_vendor into l_existing_contact;
151 close existing_contact_by_vendor;
152
153 if l_existing_contact = 1
154 then
155
156 select vendor_contact_id
157 into l_contact_id
158 from PO_VENDOR_CONTACTS b,
159 PO_VENDOR_SITES_all a
160 where a.VENDOR_ID = p_supplier_id
161 and a.VENDOR_SITE_ID = b.VENDOR_SITE_ID
162 and upper(last_name) = upper(p_last_name);
163 /*
164 select vendor_contact_id
165 into l_contact_id
166 from PO_VENDOR_CONTACTS
167 where upper(last_name) = upper(p_last_name);
168 */
169
170 return l_contact_id;
171 else
172 l_existing_contact := l_existing_contact * -1;
173
174 return l_existing_contact;
175 end if;
176 end if;
177
178 end;
179
180 -- procedure to determin the next approver to be routed for approving customer
181 -- registration.
182
183 procedure GetApprover (p_supplier_id IN NUMBER,
184 p_contact_id IN NUMBER,
185 p_user_id IN NUMBER,
186 p_approver_id IN OUT NUMBER,
187 p_approver_name IN OUT VARCHAR2) is
188
189 begin
190
191 -- Check to see if User has self approved then set to next approver.
192
193 if p_user_id = p_approver_id
194 then
195 -- Default to SYSADMIN
196 p_approver_id := 0;
197 p_approver_name := 'SYSADMIN';
198 else
199 -- Default to SYSADMIN
200 p_approver_id := 0;
201 p_approver_name := 'SYSADMIN';
202 end if;
203
204 end;
205
206 -- procedure to determin the next approver to be routed for approving customer
207 -- registration.
208
209 procedure GetContactSelector (p_supplier_id IN NUMBER,
210 p_approver_id IN OUT NUMBER,
211 p_approver_name IN OUT VARCHAR2) is
212
213 begin
214
215
216 -- Default to SYSADMIN
217 p_approver_id := 0;
218 p_approver_name := 'SYSADMIN';
219
220 end;
221
222
223 procedure GetAcctAdmin(p_supplier_id in number,
224 p_admin_id out number,
225 p_admin_name out varchar2,
226 p_display_admin_name out varchar2) is
227 begin
228
229 -- Default to SYSADMIN
230 p_admin_id := 0;
231 p_admin_name := 'SYSADMIN';
232 p_display_admin_name := 'sysadmin';
233
234 end;
235
236 -- function to determine if the requestor/preparer can approve without selecting
237 -- an approver
238 function VerifySelfApproval(p_supplier_id in number)
239 return BOOLEAN is
240 begin
241
242 return FALSE;
243
244 end;
245
246 -- function to determine if the current approver with the employee id(same as
247 -- forward to id in workflow) has the authorty to approve without further routing
248 -- of the registration request to another approver
249 function VerifyAuthority(p_supplier_id in number,
250 p_approver_id in number)
251 return BOOLEAN is
252
253 begin
254
255 return TRUE;
256
257 end;
258
259 end pos_supp_custom;
[Home] [Help]