1 PACKAGE PO_FED_FIELD_FUNCTIONS AUTHID CURRENT_USER AS
2 /* $Header: PO_FED_FIELD_FUNCTIONS.pls 120.13.12020000.8 2013/05/09 15:15:52 rparise ship $ */
3
4 PROCEDURE pr_addressdef (
5 addresstype IN OUT nocopy VARCHAR2
6 ,location IN OUT nocopy NUMBER
7 ,addresscode IN OUT nocopy NUMBER
8 ,contact IN OUT nocopy NUMBER
9 ,addressdetails IN OUT nocopy VARCHAR2
10 ,contactdetails IN OUT nocopy VARCHAR2
11 ,addressdetailsxml IN OUT nocopy VARCHAR2
12 ,contactdetailsxml IN OUT nocopy VARCHAR2
13 ,HiddenAddType IN VARCHAR2
14 ,HiddenLKPType IN VARCHAR2
15 );
16
17 PROCEDURE po_addressdef (
18 addresstype IN OUT nocopy VARCHAR2
19 ,location IN OUT nocopy NUMBER
20 ,addresscode IN OUT nocopy NUMBER
21 ,contact IN OUT nocopy NUMBER
22 ,addressdetails IN OUT nocopy VARCHAR2
23 ,contactdetails IN OUT nocopy VARCHAR2
24 ,addressdetailsxml IN OUT nocopy VARCHAR2
25 ,contactdetailsxml IN OUT nocopy VARCHAR2
26 ,hiddenCountry IN OUT NOCOPY VARCHAR2
27 ,hiddenZipCode IN OUT NOCOPY VARCHAR2
28 ,HiddenAddType IN VARCHAR2
29 ,HiddenLKPType IN VARCHAR2
30 );
31 PROCEDURE address_XD (
32 addresstype IN OUT nocopy VARCHAR2
33 ,location IN OUT nocopy NUMBER
34 ,addresscode IN OUT nocopy NUMBER
35 ,contact IN OUT nocopy NUMBER
36 ,addressdetails IN OUT nocopy VARCHAR2
37 ,contactdetails IN OUT nocopy VARCHAR2
38 ,addressdetailsxml IN OUT nocopy VARCHAR2
39 ,contactdetailsxml IN OUT nocopy VARCHAR2
40 ,hiddenCountry IN OUT NOCOPY VARCHAR2
41 ,hiddenZipCode IN OUT NOCOPY VARCHAR2
42 ,HiddenAddType IN VARCHAR2
43 ,HiddenLKPType IN VARCHAR2
44 ,p_functional_area IN VARCHAR2
45 );
46
47
48 PROCEDURE po_addresses_XSC
49 (
50 p_HiddenAddType IN VARCHAR2
51 ,p_location IN NUMBER
52 ,p_contact_id IN NUMBER
53 ,x_return_status OUT NOCOPY VARCHAR2
54 ,x_errorcode OUT NOCOPY VARCHAR2
55 ,x_msg_count OUT NOCOPY VARCHAR2
56 ,x_msg_data OUT NOCOPY VARCHAR2
57 );
58
59 PROCEDURE pr_addresses_XSC
60 (
61 p_HiddenAddType IN VARCHAR2
62 ,p_location IN NUMBER
63 ,p_contact_id IN NUMBER
64 ,x_return_status OUT NOCOPY VARCHAR2
65 ,x_errorcode OUT NOCOPY VARCHAR2
66 ,x_msg_count OUT NOCOPY VARCHAR2
67 ,x_msg_data OUT NOCOPY VARCHAR2
68 );
69
70 PROCEDURE pr_addresses_XPD
71 (
72 p_header_id IN NUMBER
73 ,p_HiddenAddType IN VARCHAR2
74 ,p_HiddenLKPType IN VARCHAR2
75 ,x_location IN OUT NOCOPY NUMBER
76 ,x_addresscode IN OUT NOCOPY NUMBER
77 ,x_contact_id IN OUT NOCOPY NUMBER
78 ,x_addressdetails IN OUT NOCOPY VARCHAR2
79 ,x_contactdetails IN OUT NOCOPY VARCHAR2
80 ,x_addressdtlsxml IN OUT NOCOPY VARCHAR2
81 ,x_contactdtlsxml IN OUT NOCOPY VARCHAR2
82 ,x_return_status OUT NOCOPY VARCHAR2
83 ,x_errorcode OUT NOCOPY VARCHAR2
84 ,x_msg_count OUT NOCOPY VARCHAR2
85 ,x_msg_data OUT NOCOPY VARCHAR2
86 );
87
88 PROCEDURE po_addresses_XPD
89 (
90 p_header_id IN NUMBER
91 ,p_draft_id IN NUMBER
92 ,p_HiddenAddType IN VARCHAR2
93 ,p_HiddenLKPType IN VARCHAR2
94 ,x_location IN OUT NOCOPY NUMBER
95 ,x_addresscode IN OUT NOCOPY NUMBER
96 ,x_contact_id IN OUT NOCOPY NUMBER
97 ,x_addressdetails IN OUT NOCOPY VARCHAR2
98 ,x_contactdetails IN OUT NOCOPY VARCHAR2
99 ,x_addressdtlsxml IN OUT NOCOPY VARCHAR2
100 ,x_contactdtlsxml IN OUT NOCOPY VARCHAR2
101 ,x_return_status OUT NOCOPY VARCHAR2
102 ,x_errorcode OUT NOCOPY VARCHAR2
103 ,x_msg_count OUT NOCOPY VARCHAR2
104 ,x_msg_data OUT NOCOPY VARCHAR2
105 );
106
107
108 PROCEDURE addresses_XLE
109 (
110 p_HiddenAddType IN VARCHAR2
111 ,p_HiddenLKPType IN VARCHAR2
112 ,p_source_item IN VARCHAR2
113 ,x_location IN OUT NOCOPY NUMBER
114 ,x_addresscode IN OUT NOCOPY NUMBER
115 ,x_contact_id IN OUT NOCOPY NUMBER
116 ,x_addressdetails IN OUT NOCOPY VARCHAR2
117 ,x_contactdetails IN OUT NOCOPY VARCHAR2
118 ,x_addressdtlsxml IN OUT NOCOPY VARCHAR2
119 ,x_contactdtlsxml IN OUT NOCOPY VARCHAR2
120 ,x_hiddenCountry IN OUT NOCOPY VARCHAR2
121 ,x_hiddenZipCode IN OUT NOCOPY VARCHAR2
122 ,x_return_status OUT NOCOPY VARCHAR2
123 ,x_errorcode OUT NOCOPY VARCHAR2
124 ,x_msg_count OUT NOCOPY VARCHAR2
125 ,x_msg_data OUT NOCOPY VARCHAR2
126 );
127
128 PROCEDURE bu_pri_prj_inf_XLE
129 (
130 p_cust_proj_code IN VARCHAR2
131 ,x_cust_proj_text OUT NOCOPY VARCHAR2
132 ,x_return_status OUT NOCOPY VARCHAR2
133 ,x_errorcode OUT NOCOPY VARCHAR2
134 ,x_msg_count OUT NOCOPY VARCHAR2
135 ,x_msg_data OUT NOCOPY VARCHAR2
136 );
137
138 PROCEDURE pr_bu_pri_prj_inf_XPD
139 (
140 p_header_id IN NUMBER
141 ,p_cust_proj_code IN VARCHAR2
142 ,p_priority IN VARCHAR2
143 ,x_cust_proj_text OUT NOCOPY VARCHAR2
144 ,x_return_status OUT NOCOPY VARCHAR2
145 ,x_errorcode OUT NOCOPY VARCHAR2
146 ,x_msg_count OUT NOCOPY VARCHAR2
147 ,x_msg_data OUT NOCOPY VARCHAR2
148 );
149
150 PROCEDURE po_bu_pri_prj_inf_XPD
151 (
152 p_header_id IN NUMBER
153 ,p_draft_id IN NUMBER
154 ,p_priority IN VARCHAR2
155 ,x_return_status OUT NOCOPY VARCHAR2
156 ,x_errorcode OUT NOCOPY VARCHAR2
157 ,x_msg_count OUT NOCOPY VARCHAR2
158 ,x_msg_data OUT NOCOPY VARCHAR2
159 );
160
161
162 PROCEDURE fed_cust_desg_XLE
163 (
164 p_cust_proj_code IN VARCHAR2
165 ,x_cust_proj_text OUT NOCOPY VARCHAR2
166 ,x_return_status OUT NOCOPY VARCHAR2
167 ,x_errorcode OUT NOCOPY VARCHAR2
168 ,x_msg_count OUT NOCOPY VARCHAR2
169 ,x_msg_data OUT NOCOPY VARCHAR2
170 );
171
172 PROCEDURE po_fed_cust_desg_XPD
173 (
174 p_line_id IN NUMBER
175 ,p_draft_id IN NUMBER
176 ,p_MDAPS_MAIS IN VARCHAR2
177 ,p_NAICS IN VARCHAR2
178 ,p_FSC_PSC IN VARCHAR2
179 ,x_return_status OUT NOCOPY VARCHAR2
180 ,x_errorcode OUT NOCOPY VARCHAR2
181 ,x_msg_count OUT NOCOPY VARCHAR2
182 ,x_msg_data OUT NOCOPY VARCHAR2
183 );
184
185 PROCEDURE add_item_info_XLE
186 (
187 p_item_id IN NUMBER
188 ,p_org_id IN NUMBER
189 ,x_item_long_desc IN OUT NOCOPY VARCHAR2
190 ,x_return_status OUT NOCOPY VARCHAR2
191 ,x_errorcode OUT NOCOPY VARCHAR2
192 ,x_msg_count OUT NOCOPY VARCHAR2
193 ,x_msg_data OUT NOCOPY VARCHAR2
194 );
195
196 PROCEDURE add_item_info_XD
197 (
198 p_item_id IN NUMBER
199 ,p_org_id IN NUMBER
200 ,x_item_long_desc IN OUT NOCOPY VARCHAR2
201 ,x_return_status OUT NOCOPY VARCHAR2
202 ,x_errorcode OUT NOCOPY VARCHAR2
203 ,x_msg_count OUT NOCOPY VARCHAR2
204 ,x_msg_data OUT NOCOPY VARCHAR2
205 );
206
207 PROCEDURE ship_info_XD
208 (
209 p_ship_to_loc_id IN NUMBER
210 ,x_ship_to_addr IN OUT NOCOPY VARCHAR2
211 ,p_hidd_ship_addr IN OUT NOCOPY VARCHAR
212 ,p_hiddenCountry IN OUT NOCOPY VARCHAR2
213 ,p_hiddenZipCode IN OUT NOCOPY VARCHAR
214 ,x_return_status OUT NOCOPY VARCHAR2
215 ,x_errorcode OUT NOCOPY VARCHAR2
216 ,x_msg_count OUT NOCOPY VARCHAR2
217 ,x_msg_data OUT NOCOPY VARCHAR2
218 );
219
220 PROCEDURE ship_info_XLE
221 (
222 p_ship_to_loc_id IN NUMBER
223 ,x_ship_to_addr IN OUT NOCOPY VARCHAR2
224 ,p_hidd_ship_addr IN OUT NOCOPY VARCHAR
225 ,p_hiddenCountry IN OUT NOCOPY VARCHAR2
226 ,p_hiddenZipCode IN OUT NOCOPY VARCHAR
227 ,x_return_status OUT NOCOPY VARCHAR2
228 ,x_errorcode OUT NOCOPY VARCHAR2
229 ,x_msg_count OUT NOCOPY VARCHAR2
230 ,x_msg_data OUT NOCOPY VARCHAR2
231 );
232
233 PROCEDURE pr_sourcing_info_req_XPD
234 (
235 p_header_id IN NUMBER
236 ,p_suggest_aw_no IN VARCHAR2
237 ,x_return_status OUT NOCOPY VARCHAR2
238 ,x_errorcode OUT NOCOPY VARCHAR2
239 ,x_msg_count OUT NOCOPY VARCHAR2
240 ,x_msg_data OUT NOCOPY VARCHAR2
241 );
242 PROCEDURE competitive_info_XD
243 (
244 p_set_aside_stat IN OUT NOCOPY VARCHAR2
245 ,p_set_aside_perc IN OUT NOCOPY VARCHAR2
246 );
247
248 PROCEDURE competitive_info_XV
249 (
250
251 p_set_aside_stat IN VARCHAR2
252 ,p_set_aside_perc IN VARCHAR2
253 ,p_set_aside_type IN varchar2
254 ,p_naics IN varchar2
255 ,p_size_stand IN varchar2
256 ,x_return_status OUT NOCOPY VARCHAR2
257 ,x_errorcode OUT NOCOPY VARCHAR2
258 ,x_msg_count OUT NOCOPY VARCHAR2
259 ,x_msg_data OUT NOCOPY VARCHAR2
260 ,p_draft_id IN NUMBER --<Bug 16421784>
261 );
262 PROCEDURE competitive_info_XLE
263 (
264
265 p_set_aside_stat IN OUT NOCOPY VARCHAR2
266 ,p_set_aside_perc IN OUT NOCOPY VARCHAR
267 ,p_set_aside_type IN OUT NOCOPY VARCHAR
268 ,p_naics IN OUT NOCOPY VARCHAR
269 ,p_size_stand IN OUT NOCOPY VARCHAR
270 ,p_source_item IN VARCHAR2
271 ,x_return_status OUT NOCOPY VARCHAR2
272 ,x_errorcode OUT NOCOPY VARCHAR2
273 ,x_msg_count OUT NOCOPY VARCHAR2
274 ,x_msg_data OUT NOCOPY VARCHAR2
275 );
276 PROCEDURE supplier_details_XD
277 (
278 x_supplier_id IN NUMBER
279 ,x_supplier_site_id IN NUMBER
280 ,x_contact_id IN NUMBER
281 ,x_org_id IN NUMBER
282 ,p_doing_bsns_as IN OUT NOCOPY VARCHAR2
283 ,p_suplr_size IN OUT NOCOPY VARCHAR2
284 ,p_phy_addrs IN OUT NOCOPY VARCHAR2
285 ,p_hidd_vendor_id IN OUT NOCOPY NUMBER
286 ,p_hidd_org_id IN OUT NOCOPY NUMBER
287 ,p_remit_to_addrs IN OUT NOCOPY NUMBER
288 ,p_hidd_remit_xml IN OUT NOCOPY VARCHAR
289 ,p_addrs_dtls IN OUT NOCOPY VARCHAR2
290 ,p_hidd_addrs_dtls_xml IN OUT NOCOPY VARCHAR2
291 ,p_supplr_contact IN OUT NOCOPY VARCHAR2
292 ,p_hidd_contact_xml IN OUT NOCOPY VARCHAR2
293 ,p_ccr_reg_idictr IN OUT NOCOPY VARCHAR2
294 ,p_phone_num IN OUT NOCOPY VARCHAR2
295 ,p_ccr_reg_status IN OUT NOCOPY VARCHAR2
296 ,p_email IN OUT NOCOPY VARCHAR2
297 ,p_ccr_exp_reason IN OUT NOCOPY VARCHAR2
298 ,p_duns_num IN OUT NOCOPY VARCHAR2
299 ,p_cage_code IN OUT NOCOPY VARCHAR2
300 ,p_duns_4_num IN OUT NOCOPY VARCHAR2
301 ,p_tin_num IN OUT NOCOPY VARCHAR2
302 ,p_eft_indctr IN OUT NOCOPY VARCHAR2
303 ,p_eft_exc_resn IN OUT NOCOPY VARCHAR2
304 ,p_socio_ec_info IN OUT NOCOPY VARCHAR2
305 ,p_orig_splr_name IN OUT NOCOPY VARCHAR2
306 );
307 PROCEDURE supplier_details_XSC
308 (
309 p_eft_indctr IN OUT NOCOPY VARCHAR2
310 ,p_eft_exc_resn IN OUT NOCOPY VARCHAR2
311 ,p_suplr_size IN OUT NOCOPY VARCHAR2
312 ,p_remit_to_addrs IN OUT NOCOPY NUMBER
313 ,p_ccr_reg_idictr IN OUT NOCOPY VARCHAR2
314 ,p_ccr_reg_status IN OUT NOCOPY VARCHAR2
315 ,p_ccr_exp_reason IN OUT NOCOPY VARCHAR2
316 ,x_return_status OUT NOCOPY VARCHAR2
317 ,x_errorcode OUT NOCOPY VARCHAR2
318 ,x_msg_count OUT NOCOPY VARCHAR2
319 ,x_msg_data OUT NOCOPY VARCHAR2
320
321 );
322 PROCEDURE supplier_details_XLE
323 (
324 x_supplier_id IN NUMBER
325 ,x_supplier_site_id IN NUMBER
326 ,p_remit_to_addrs IN OUT NOCOPY NUMBER
327 ,p_addrs_dtls IN OUT NOCOPY VARCHAR2
328 ,p_hidd_remit_xml IN OUT NOCOPY VARCHAR2
329 ,p_orig_splr_name IN OUT NOCOPY VARCHAR2
330 ,x_return_status OUT NOCOPY VARCHAR2
331 ,x_errorcode OUT NOCOPY VARCHAR2
335 PROCEDURE supplier_details_XRD
332 ,x_msg_count OUT NOCOPY VARCHAR2
333 ,x_msg_data OUT NOCOPY VARCHAR2
334 );
336 (
337 po_header_id IN NUMBER
338 ,draft_id IN NUMBER
339 ,p_doing_bsns_as IN OUT NOCOPY VARCHAR2
340 ,p_suplr_size IN OUT NOCOPY VARCHAR2
341 ,p_phy_addrs IN OUT NOCOPY VARCHAR2
342 ,p_remit_to_addrs IN OUT NOCOPY NUMBER
343 ,p_addrs_dtls IN OUT NOCOPY VARCHAR2
344 ,p_hidd_addrs_dtls_xml IN OUT NOCOPY VARCHAR2
345 ,p_supplr_contact IN OUT NOCOPY VARCHAR2
346 ,p_hidd_contact_xml IN OUT NOCOPY VARCHAR2
347 ,p_ccr_reg_idictr IN OUT NOCOPY VARCHAR2
348 ,p_phone_num IN OUT NOCOPY VARCHAR2
349 ,p_ccr_reg_status IN OUT NOCOPY VARCHAR2
350 ,p_email IN OUT NOCOPY VARCHAR2
351 ,p_ccr_exp_reason IN OUT NOCOPY VARCHAR2
352 ,p_duns_num IN OUT NOCOPY VARCHAR2
353 ,p_cage_code IN OUT NOCOPY VARCHAR2
354 ,p_duns_4_num IN OUT NOCOPY VARCHAR2
355 ,p_tin_num IN OUT NOCOPY VARCHAR2
356 ,p_eft_indctr IN OUT NOCOPY VARCHAR2
357 ,p_eft_exc_resn IN OUT NOCOPY VARCHAR2
358 ,p_socio_ec_info IN OUT NOCOPY VARCHAR2
359 ,p_orig_splr_name IN OUT NOCOPY VARCHAR2
360 ,x_return_status OUT NOCOPY VARCHAR2
361 ,x_errorcode OUT NOCOPY VARCHAR2
362 ,x_msg_count OUT NOCOPY VARCHAR2
363 ,x_msg_data OUT NOCOPY VARCHAR2
364
365 );
366
367 FUNCTION create_address_xml(p_sql_stmt VARCHAR2) RETURN CLOB;
368
369 PROCEDURE po_address_XRD (
370 x_location IN OUT NOCOPY NUMBER
371 ,x_addresscode IN OUT NOCOPY NUMBER
372 ,x_contact_id IN OUT NOCOPY NUMBER
373 ,x_addressdetails IN OUT NOCOPY VARCHAR2
374 ,x_contactdetails IN OUT NOCOPY VARCHAR2
375 ,x_addressdtlsxml IN OUT NOCOPY VARCHAR2
376 ,x_contactdtlsxml IN OUT NOCOPY VARCHAR2
377 ,x_hiddenCountry IN OUT NOCOPY VARCHAR2
378 ,x_hiddenZipCode IN OUT NOCOPY VARCHAR2
379 ,x_hiddenAddType IN VARCHAR2
380 ,x_hiddenLKPType IN VARCHAR2
381 ,x_return_status OUT NOCOPY VARCHAR2
382 ,x_errorcode OUT NOCOPY VARCHAR2
383 ,x_msg_count OUT NOCOPY VARCHAR2
384 ,x_msg_data OUT NOCOPY VARCHAR2
385 );
386
387 PROCEDURE po_ship_info_XRD
388 (
389 p_line_loc_id IN NUMBER
390 ,p_draft_id IN NUMBER
391 ,x_ship_to_addr IN OUT NOCOPY VARCHAR2
392 ,p_hidd_ship_addr IN OUT NOCOPY VARCHAR
393 ,p_hiddenCountry IN OUT NOCOPY VARCHAR2
394 ,p_hiddenZipCode IN OUT NOCOPY VARCHAR
395 ,x_return_status OUT NOCOPY VARCHAR2
396 ,x_errorcode OUT NOCOPY VARCHAR2
397 ,x_msg_count OUT NOCOPY VARCHAR2
398 ,x_msg_data OUT NOCOPY VARCHAR2
399 );
400
401 --CLM Phase 4 Changes
402
403 PROCEDURE inspection_XLE
404 (
405 p_source_item IN VARCHAR2
406 ,p_insp_location IN OUT NOCOPY NUMBER
407 ,p_insp_addresscode IN OUT NOCOPY NUMBER
408 ,p_insp_addressdetails IN OUT NOCOPY VARCHAR2
409 ,p_accp_location IN OUT NOCOPY NUMBER
410 ,p_accp_addresscode IN OUT NOCOPY NUMBER
411 ,p_accp_addressdetails IN OUT NOCOPY VARCHAR2
412 ,x_return_status OUT NOCOPY VARCHAR2
413 ,x_errorcode OUT NOCOPY VARCHAR2
414 ,x_msg_count OUT NOCOPY VARCHAR2
415 ,x_msg_data OUT NOCOPY VARCHAR2
416 );
417
418
419 PROCEDURE copy_from_insp_info
420 (
421 p_insp_location IN VARCHAR2
422 ,p_insp_add_location IN NUMBER
423 ,p_insp_addresscode IN NUMBER
424 ,p_insp_addressdetails IN VARCHAR2
425 ,p_accp_location IN OUT nocopy VARCHAR2
426 ,p_accp_add_location IN OUT nocopy NUMBER
427 ,p_accp_addresscode IN OUT nocopy NUMBER
428 ,p_accp_addressdetails IN OUT nocopy VARCHAR2
429 );
430
431 PROCEDURE closeout_XD
432 (
433 po_header_id IN NUMBER
434 ,draft_id IN NUMBER
435 ,payment_no IN OUT NOCOPY VARCHAR2
436 ,payment_date IN OUT NOCOPY DATE
437 ,x_return_status OUT NOCOPY VARCHAR2
438 ,x_errorcode OUT NOCOPY VARCHAR2
439 ,x_msg_count OUT NOCOPY VARCHAR2
440 ,x_msg_data OUT NOCOPY VARCHAR2
441 );
442
443 PROCEDURE pr_mipr_acc_info_xsc
444 (
445 p_funds_info IN VARCHAR2
446 ,p_amount IN NUMBER
447 ,p_acceptance_status IN VARCHAR2
448 ,p_remarks IN VARCHAR2
449 ,p_obligation_type IN VARCHAR2
450 ,p_auth_officer IN VARCHAR2
451 ,p_header_id IN NUMBER
452 ,x_return_status OUT NOCOPY VARCHAR2
453 ,x_errorcode OUT NOCOPY VARCHAR2
454 ,x_msg_count OUT NOCOPY VARCHAR2
455 ,x_msg_data OUT NOCOPY VARCHAR2
456 );
457
458 --<Bug 16421784> : Addind a new procedure
459 PROCEDURE pon_competitive_info_XV
460 (
461
462 p_set_aside_stat IN VARCHAR2
463 ,p_set_aside_perc IN VARCHAR2
464 ,p_set_aside_type IN varchar2
465 ,p_naics IN varchar2
466 ,p_size_stand IN varchar2
467 ,x_return_status OUT NOCOPY VARCHAR2
468 ,x_errorcode OUT NOCOPY VARCHAR2
469 ,x_msg_count OUT NOCOPY VARCHAR2
470 ,x_msg_data OUT NOCOPY VARCHAR2
471 ,p_auction_header_id IN NUMBER
472 );
473
474 XPD_DRAFT_ID NUMBER;
475 G_XLE_SUPPLIER_EVENT_TYPE VARCHAR2(50) := 'ALL';
476 g_is_multi_mod_context VARCHAR2(1) := 'N';
477
478 END PO_FED_FIELD_FUNCTIONS;