1 PACKAGE FV_CCR_UTIL_PVT AUTHID CURRENT_USER as
2 /* $Header: FVCCRCRS.pls 120.6.12010000.6 2010/03/26 21:25:15 snama ship $*/
3
4 FUNCTION existing_org_context RETURN VARCHAR2;
5
6 PROCEDURE get_bank_branch_information
7 (
8 p_routing_num IN VARCHAR2,
9 p_bank_branch_id IN NUMBER,
10 x_bank_name OUT NOCOPY VARCHAR2,
11 x_bank_branch_name OUT NOCOPY VARCHAR2,
12 x_bank_branch_id OUT NOCOPY NUMBER
13 );
14
15 PROCEDURE get_bank_account_information
16 (
17 p_bank_branch_id IN NUMBER,
18 p_bank_account_number IN VARCHAR2,
19 p_bank_account_id IN NUMBER,
20 p_account_type IN VARCHAR2,
21 p_base_currency IN VARCHAR2,
22 p_country_code IN VARCHAR2,
23 x_bank_account_id OUT NOCOPY NUMBER,
24 x_update_account OUT NOCOPY VARCHAR2
25 );
26
27 procedure get_federal_indicator
28 (
29 p_vendor_id IN NUMBER,
30 p_taxpayer_number IN VARCHAR2,
31 p_legal_bus_name IN VARCHAR2,
32 x_federal OUT NOCOPY VARCHAR2
33 );
34
35 PROCEDURE fv_process_vendor
36 (
37 p_ccr_id IN NUMBER ,
38 p_prev_ccr_id IN VARCHAR2 ,
39 p_update_type IN VARCHAR2 ,
40 x_return_status OUT NOCOPY VARCHAR2 ,
41 x_msg_count OUT NOCOPY NUMBER ,
42 x_msg_data OUT NOCOPY VARCHAR2 ,
43 p_bank_branch_id IN NUMBER ,
44 p_vendor_id IN NUMBER,
45 p_pay_site_id IN NUMBER,
46 p_main_add_site_id IN NUMBER,
47 p_enabled_flag IN VARCHAR2,
48 p_main_address_flag IN VARCHAR2,
49 p_taxpayer_number IN VARCHAR2,
50 p_legal_bus_name IN VARCHAR2,
51 p_duns IN VARCHAR2,
52 p_plus4 IN VARCHAR2,
53 p_main_address_line1 IN VARCHAR2,
54 p_main_address_line2 IN VARCHAR2,
55 p_main_address_city IN VARCHAR2,
56 p_main_address_state IN VARCHAR2,
57 p_main_address_zip IN VARCHAR2,
58 p_main_address_country IN VARCHAR2,
59 p_pay_address_line1 IN VARCHAR2,
60 p_pay_address_line2 IN VARCHAR2,
61 p_pay_address_line3 IN VARCHAR2,
62 p_pay_address_city IN VARCHAR2,
63 p_pay_address_state IN VARCHAR2,
64 p_pay_address_zip IN VARCHAR2,
65 p_pay_address_country IN VARCHAR2,
66 p_old_bank_account_id IN NUMBER,
67 p_new_bank_account_id IN NUMBER,
68 p_bank_name IN VARCHAR2,
69 p_bank_branch_name IN VARCHAR2,
70 p_bank_num IN VARCHAR2,
71 p_bank_account_num IN VARCHAR2,
72 p_org_id IN NUMBER,
73 p_update_vendor_flag IN VARCHAR2,
74 p_org_name IN varchar2,
75 p_ccr_status IN varchar2,
76 p_insert_vendor_flag IN VARCHAR2,
77 p_prev_vendor_id IN NUMBER,
78 p_file_date IN DATE,
79 p_bank_conc_req_status IN VARCHAR2,
80 p_header_conc_req_status IN VARCHAR2,
81 p_assgn_conc_req_status IN VARCHAR2,
82 p_base_currency IN VARCHAR2,
83 p_valid_bank_info IN VARCHAR2,
84 p_federal_vendor IN VARCHAR2,
85 p_created_bank_branch_id IN NUMBER,
86 p_created_bank_account_id IN NUMBER,
87 x_vendor_id OUT NOCOPY NUMBER,
88 x_output OUT NOCOPY VARCHAR2,
89 x_react_pay_site_code OUT NOCOPY VARCHAR2,
90 x_react_main_site_code OUT NOCOPY VARCHAR2,
91 x_tp_changed OUT NOCOPY VARCHAR2,
92 x_vendor_name OUT NOCOPY VARCHAR2,
93 p_org_type_lookup IN VARCHAR2,
94 p_remit_poc IN VARCHAR2,
95 p_mail_poc IN VARCHAR2,
96 p_ar_us_phone IN VARCHAR2,
97 p_ar_fax IN VARCHAR2,
98 p_ar_email IN VARCHAR2,
99 p_ar_non_us_phone IN VARCHAR2
100 );
101
102 FUNCTION get_profile_option(
103 p_name VARCHAR2
104 ) RETURN VARCHAR2;
105
106 PROCEDURE delete_plusfour_assignments(
107 p_ccrid NUMBER
108 );
109
110 FUNCTION get_org_paysite_id(
111 p_ccrid NUMBER,
112 p_org_id NUMBER
113 ) RETURN NUMBER;
114
115 FUNCTION get_org_mainaddrsite_id(
116 p_ccrid NUMBER,
117 p_org_id NUMBER
118 ) RETURN NUMBER;
119
120 FUNCTION get_lookup_desc (p_lookup_type IN VARCHAR2,
121 p_lookup_code IN VARCHAR2
122 ) RETURN VARCHAR2;
123
124
125 FUNCTION check_non_user_org_asgnmt(p_ccr_id IN NUMBER
126 ) RETURN VARCHAR2;
127
128 FUNCTION check_suppl_tobe_merged (p_vendor_id IN NUMBER
129 ) RETURN VARCHAR2;
130
131 PROCEDURE insert_vendor
132 (
133 p_vendor_name IN varchar2,
134 p_taxpayer_id IN varchar2,
135 p_supplier_number IN varchar2,
136 p_org_type_lookup_code IN VARCHAR2,
137 x_vendor_id OUT NOCOPY NUMBER,
138 x_status OUT NOCOPY VARCHAR2,
139 x_exception_msg OUT NOCOPY VARCHAR2
140 );
141
142 PROCEDURE update_vendor
143 (
144 p_vendor_id IN NUMBER,
145 p_taxpayer_id IN VARCHAR2,
146 x_status OUT NOCOPY VARCHAR2,
147 x_exception_msg OUT NOCOPY VARCHAR2
148 );
149
150 PROCEDURE insert_vendor_site
151 (
152 p_vendor_site_code IN VARCHAR2,
153 p_vendor_id IN NUMBER,
154 p_org_id IN NUMBER,
155 p_address_line1 IN VARCHAR2,
156 p_address_line2 IN VARCHAR2,
157 p_address_line3 IN VARCHAR2,
158 p_address_line4 IN VARCHAR2,
159 p_city IN VARCHAR2,
160 p_state IN VARCHAR2,
161 p_zip IN VARCHAR2,
162 p_province IN VARCHAR2,
163 p_country IN VARCHAR2,
164 p_duns_number IN VARCHAR2,
165 p_pay_site_flag IN VARCHAR2,
166 p_hold_unvalidated_inv_flag IN VARCHAR2,
167 p_hold_all_payments_flag IN VARCHAR2,
168 p_us_phone IN VARCHAR2,
169 p_fax IN VARCHAR2,
170 p_email IN VARCHAR2,
171 p_non_us_phone IN VARCHAR2,
172 p_purchasing_site_flag IN VARCHAR2,
173 x_vendor_site_id OUT NOCOPY NUMBER,
174 x_party_site_id OUT NOCOPY NUMBER,
175 x_status OUT NOCOPY VARCHAR2,
176 x_exception_msg OUT NOCOPY VARCHAR2
177 );
178
179 PROCEDURE update_vendor_site
180 (
181 p_vendor_site_code IN VARCHAR2,
182 p_vendor_site_id IN NUMBER,
183 p_org_id IN NUMBER,
184 p_address_line1 IN VARCHAR2,
185 p_address_line2 IN VARCHAR2,
186 p_address_line3 IN VARCHAR2,
187 p_address_line4 IN VARCHAR2,
188 p_city IN VARCHAR2,
189 p_state IN VARCHAR2,
190 p_zip IN VARCHAR2,
191 p_province IN VARCHAR2,
192 p_country IN VARCHAR2,
193 p_duns_number IN VARCHAR2,
194 p_pay_site_flag IN VARCHAR2,
195 p_hold_unvalidated_inv_flag IN VARCHAR2,
196 p_hold_all_payments_flag IN VARCHAR2,
197 p_us_phone IN VARCHAR2,
198 p_fax IN VARCHAR2,
199 p_email IN VARCHAR2,
200 p_non_us_phone IN VARCHAR2,
201 p_purchasing_site_flag IN VARCHAR2,
202 x_party_site_id OUT NOCOPY NUMBER,
203 x_status OUT NOCOPY VARCHAR2,
204 x_exception_msg OUT NOCOPY VARCHAR2
205 );
206
207 PROCEDURE create_bank_account
208 (
209 p_created_bank_id IN NUMBER,
210 p_created_bank_branch_id IN NUMBER,
211 p_bank_name IN VARCHAR2,
212 p_branch_name IN VARCHAR2,
213 p_bank_num IN VARCHAR2,
214 p_eft_user_num IN VARCHAR2,
215 p_inst_type IN VARCHAR2,
216 p_bank_branch_type IN VARCHAR2,
217 p_bank_acct_name IN VARCHAR2,
218 p_bank_acct_num IN VARCHAR2,
219 p_currency_code IN VARCHAR2,
220 p_bank_account_type IN VARCHAR2,
221 p_country_code IN VARCHAR2,
222 p_duns_number IN VARCHAR2,
223 x_bank_id OUT NOCOPY NUMBER,
224 x_bank_branch_id OUT NOCOPY NUMBER,
225 x_bank_account_id OUT NOCOPY NUMBER,
226 x_return_status OUT NOCOPY VARCHAR2
227 );
228
229 PROCEDURE update_bank_account
230 (
231 p_bank_account_id NUMBER,
232 p_bank_account_type VARCHAR2,
233 x_return_status OUT NOCOPY VARCHAR2
234 );
235
236 PROCEDURE process_bank_account_uses
237 (
238 p_account_uses_upd_flag IN VARCHAR2,
239 p_vendor_id IN NUMBER,
240 p_federal_vendor IN VARCHAR2,
241 p_valid_bank_info IN VARCHAR2,
242 p_old_bank_account_id IN NUMBER,
243 p_pay_site_id IN NUMBER,
244 p_file_date IN DATE,
245 p_new_bank_account_id IN NUMBER,
246 p_account_uses_insert_flag IN VARCHAR2,
247 p_org_id IN NUMBER
248 );
249
250 FUNCTION check_taxpayerid_diff(p_vendor_id IN number, p_duns varchar2
251 ) RETURN VARCHAR2;
252
253 FUNCTION get_ext_cert_val(p_duns varchar2, p_code varchar2) return varchar2;
254
255 function get_ccr_flag_code(p_duns varchar2) return varchar2;
256
257 function get_ccr_numerics(p_duns varchar2, p_code varchar2) return number;
258
259 FUNCTION get_disaster_code(p_duns varchar2, p_code varchar2) return varchar2;
260
261 function get_supplier_debarred ( p_supplier_id in number,
262 p_supplier_site_id in number,
263 p_reference_date in date default sysdate,
264 x_return_status out nocopy varchar2,
265 x_msg_data varchar2) return boolean;
266
267 END;