1 PACKAGE OKC_REP_UTIL_PVT AS
2 /* $Header: OKCVREPUTILS.pls 120.10 2007/12/20 09:20:09 strivedi ship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ------------------------------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_REP_UTIL_PVT';
9 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKC';
10 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
11
12 G_OBJECT_NAME CONSTANT VARCHAR2(200) := 'OKC_REP_CONTRACT';
13
14 G_STATUS_PENDING_APPROVAL CONSTANT VARCHAR2(30) := 'PENDING_APPROVAL';
15 G_STATUS_APPROVED CONSTANT VARCHAR2(30) := 'APPROVED';
16 G_STATUS_REJECTED CONSTANT VARCHAR2(30) := 'REJECTED';
17 G_STATUS_DRAFT CONSTANT VARCHAR2(30) := 'DRAFT';
18
19 G_ACTION_SUBMITTED CONSTANT VARCHAR2(30) := 'SUBMITTED';
20
21 -- Contract Type Intents
22 G_INTENT_BUY CONSTANT VARCHAR2(30) := 'B';
23
24 -- Party validation modes
25 G_P_MODE_IMPORT CONSTANT VARCHAR2(30) := 'IMPORT';
26 G_P_MODE_AUTHORING CONSTANT VARCHAR2(30) := 'AUTHORING';
27
28 -- Party Role codes
29 G_PARTY_ROLE_INTERNAL CONSTANT VARCHAR2(30) := 'INTERNAL_ORG';
30 G_PARTY_ROLE_PARTNER CONSTANT VARCHAR2(30) := 'PARTNER_ORG';
31 G_PARTY_ROLE_CUSTOMER CONSTANT VARCHAR2(30) := 'CUSTOMER_ORG';
32 G_PARTY_ROLE_SUPPLIER CONSTANT VARCHAR2(30) := 'SUPPLIER_ORG';
33
34 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
35 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
36 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
37
38 -- Error Types for import errors table
39 G_IMP_CONTRACT_ERROR CONSTANT VARCHAR2(50) := 'CONTRACT';
40 G_IMP_PARTY_ERROR CONSTANT VARCHAR2(50) := 'PARTY';
41 G_IMP_DOCUMENT_ERROR CONSTANT VARCHAR2(50) := 'DOCUMENT';
42
43 -- Date Format
44 G_IMP_DATE_FORMAT CONSTANT VARCHAR2(50) := 'MM/DD/YYYY';
45
46 -- Number Format
47 G_IMP_NUMBER_FORMAT CONSTANT VARCHAR2(50) := '999999999999.99';
48
49 G_SELECT_ACCESS_LEVEL CONSTANT VARCHAR2(17) := 'OKC_REP_AU_SELECT';
50 G_UPDATE_ACCESS_LEVEL CONSTANT VARCHAR2(17) := 'OKC_REP_AU_UPDATE';
51
52 G_FND_GRANTS_VIEW_ACCESS CONSTANT VARCHAR2(4) := 'VIEW';
53 G_FND_GRANTS_UPDATE_ACCESS CONSTANT VARCHAR2(6) := 'UPDATE';
54
55 G_FND_GRANTEE_TYPE_USER CONSTANT VARCHAR2(4) := 'USER';
56 G_FND_GRANTEE_TYPE_GROUP CONSTANT VARCHAR2(5) := 'GROUP';
57
58 G_FUNC_OKC_REP_ADMINISTRATOR CONSTANT VARCHAR2(21) := 'OKC_REP_ADMINISTRATOR';
59 G_FUNC_OKC_REP_USER_FUNC CONSTANT VARCHAR2(17) := 'OKC_REP_USER_FUNC';
60 G_FUNC_OKC_REP_SALES_WB_USER CONSTANT VARCHAR2(30) := 'OKC_REP_SALES_WORKBENCH_USER';
61
62 -- Sales quote constants
63 G_SALES_QUOTE_SEC_PROFILE CONSTANT VARCHAR2(30) := 'ASO_ENABLE_SECURITY_CHECK';
64 G_SALES_QUOTE_UPDATE_ACCESS CONSTANT VARCHAR2(6) := 'UPDATE';
65
66 ---------------------------------------------------------------------------
67 -- Procedures and Functions
68 ---------------------------------------------------------------------------
69
70 -- Start of comments
71 --API name : check_contract_access_external
72 --Type : Private.
73 --Function : Checks access to a external contract by the current user.
74 --Pre-reqs : None.
75 --Parameters :
76 --IN : p_api_version IN NUMBER Required
77 -- : p_init_msg_list IN VARCHAR2 Required
78 -- : p_contract_id IN NUMBER Required
79 -- Id of the contract to be checked
80 -- : p_contract_type IN VARCHAR2 Required
81 -- Type of the contract to be checked
82 --OUT : x_has_access OUT VARCHAR2(1)
83 -- : x_return_status OUT VARCHAR2(1)
84 -- : x_msg_count OUT NUMBER
85 -- : x_msg_data OUT VARCHAR2(2000)
86 -- End of comments
87 PROCEDURE check_contract_access_external(
88 p_api_version IN NUMBER,
89 p_init_msg_list IN VARCHAR2,
90 p_contract_id IN NUMBER,
91 p_contract_type IN VARCHAR2,
92 x_has_access OUT NOCOPY VARCHAR2,
93 x_msg_data OUT NOCOPY VARCHAR2,
94 x_msg_count OUT NOCOPY NUMBER,
95 x_return_status OUT NOCOPY VARCHAR2);
96
97 -- Start of comments
98 --API name : check_contract_access
99 --Type : Private.
100 --Function : Checks access to a contract by the current user.
101 --Pre-reqs : None.
102 --Parameters :
103 --IN : p_api_version IN NUMBER Required
104 -- : p_init_msg_list IN VARCHAR2 Optional
105 -- : p_contract_id IN NUMBER Required
106 -- Id of the contract whose access to be checked
107 -- : p_function_name IN VARCHAR2 Required
108 -- Name of the function whose access to be checked. Possible values OKC_REP_SELECT and OKC_REP_UPDATE
109 --OUT : x_has_access OUT VARCHAR2(1)
110 -- : x_return_status OUT VARCHAR2(1)
111 -- : x_msg_count OUT NUMBER
112 -- : x_msg_data OUT VARCHAR2(2000)
113 -- End of comments
114 PROCEDURE check_contract_access(
115 p_api_version IN NUMBER,
116 p_init_msg_list IN VARCHAR2,
117 p_contract_id IN NUMBER,
118 p_function_name IN VARCHAR2,
119 x_has_access OUT NOCOPY VARCHAR2,
120 x_msg_data OUT NOCOPY VARCHAR2,
121 x_msg_count OUT NOCOPY NUMBER,
122 x_return_status OUT NOCOPY VARCHAR2);
123
124 -- Start of comments
125 --API name : Function has_contract_access_external
126 --Type : Private.
127 --Function : Checks access to a contract by the current user for external contracts.
128 --Pre-reqs : None.
129 --Parameters :
130 --IN : p_contract_id IN NUMBER Required
131 -- Id of the contract that is being checked
132 -- : p_contract_type IN VARCHAR2 Required
133 -- Contract type for contract being chacked
134 --OUT : Return Y if the current user has access to the contracts, else returns N
135 -- End of comments
136 FUNCTION has_contract_access_external(
137 p_contract_id IN NUMBER,
138 p_contract_type IN VARCHAR2
139 ) RETURN VARCHAR2;
140
141 -- Start of comments
142 --API name : has_contract_access
143 --Type : Private.
144 --Function : Checks access to a contract by the current user.
145 --Pre-reqs : None.
146 --Parameters :
147 --IN : p_api_version IN NUMBER Required
148 -- : p_init_msg_list IN VARCHAR2 Optional
149 -- Default = FND_API.G_FALSE
150 -- : p_contract_id IN NUMBER Required
151 -- Id of the contract whose access to be checked
152 -- : p_function_name IN VARCHAR2 Required
153 -- Name of the function whose access to be checked. Possible values OKC_REP_SELECT and OKC_REP_UPDATE
154 --OUT : Return Y if the current user has access to the contracts, else returns N
155 -- End of comments
156 FUNCTION has_contract_access(
157 p_contract_id IN NUMBER,
158 p_function_name IN VARCHAR2
159 ) RETURN VARCHAR2;
160
161
162 /**
163 * This procedure changes status of a contract and logs the user action that
164 * caused this into database tables OKC_REP_CON_STATUS_HIST.
165 * @param IN p_contract_id Id of the contract whose status to be changed
166 * @param IN p_contract_version Version number of the contract whose status to be changed
167 * @param IN p_status_code New status code to be set on the contract
168 * @param IN p_user_id Id of the user who caused this change
169 * @param IN p_note User entered notes in the notification while approving or rejecting the contract
170 */
171 PROCEDURE change_contract_status(
172 p_api_version IN NUMBER,
173 p_init_msg_list IN VARCHAR2,
174 p_contract_id IN NUMBER,
175 p_contract_version IN NUMBER,
176 p_status_code IN VARCHAR2,
177 p_user_id IN NUMBER:=NULL,
178 p_note IN VARCHAR2:=NULL,
179 x_msg_data OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_return_status OUT NOCOPY VARCHAR2);
182
183 -- Start of comments
184 --API name : add_approval_hist_record
185 --Type : Private.
186 --Function : Inserts a record into table OKC_REP_CON_APPROVALS.
187 --Pre-reqs : None.
188 --Parameters :
189 --IN : p_api_version IN NUMBER Required
190 -- : p_init_msg_list IN VARCHAR2 Optional
191 -- Default = FND_API.G_FALSE
192 -- : p_contract_id IN NUMBER Required
193 -- Contract ID of the approval history record
194 -- : p_contract_version IN VARCHAR2 Required
195 -- Contract version of the approval history record
196 -- : p_action_code IN OUT VARCHAR2 Optional
197 -- New action code to be set on the contract
198 -- : p_user_id IN VARCHAR2 Optional
199 -- Id of the user who caused this change
200 -- : p_note IN OUT VARCHAR2 Optional
201 -- User entered notes in the notification while approving or rejecting the contract
202 --OUT : x_return_status OUT VARCHAR2(1)
203 -- : x_msg_count OUT NUMBER
204 -- : x_msg_data OUT VARCHAR2(2000)
205 -- End of comments
206 PROCEDURE add_approval_hist_record(
207 p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2,
209 p_contract_id IN NUMBER,
210 p_contract_version IN NUMBER,
211 p_action_code IN VARCHAR2,
212 p_user_id IN NUMBER:=NULL,
213 p_note IN VARCHAR2:=NULL,
214 x_msg_data OUT NOCOPY VARCHAR2,
215 x_msg_count OUT NOCOPY NUMBER,
216 x_return_status OUT NOCOPY VARCHAR2);
217
218
219 -- Start of comments
220 --API name : validate_contract_party
221 --Type : Private.
222 --Function : Validates a contract party
223 --Pre-reqs : None.
224 --Parameters :
225 --IN : p_api_version IN NUMBER Required
226 -- : p_init_msg_list IN VARCHAR2 Optional
227 -- Default = FND_API.G_FALSE
228 -- : p_contract_id IN NUMBER Required
229 -- Contract ID of the party to be validated
230 -- : p_intent IN VARCHAR2 Required
231 -- Intent of the contract
232 -- : p_party_role_code IN OUT VARCHAR2 Optional
233 -- Role code of the contract party to be validated
234 -- : p_party_role_txt IN VARCHAR2 Optional
235 -- Role name of the contract party to be validated
236 -- : p_party_id IN OUT NUMBER Optional
237 -- Id of the contract party to be validated
238 -- : p_party_name IN VARCHAR2 Required
239 -- Name of the contract party to be validated
240 -- : p_location_id IN NUMBER Optional
241 -- Id of the location of the contract party to be validated
242 -- : p_mode IN VARCHAR2 Required
243 -- Mode of the validation. Possible values 'IMPORT' or 'AUTHORING'
244 --OUT : x_valid_party_flag OUT VARCHAR2(1)
245 -- : x_error_code OUT VARCHAR2(100)
246 -- Possible error codes are;
247 -- ROLE_NOT_EXIST - Party role doesn't exist (Import module)
248 -- INV_ROLE_INTENT - Party role and Contract intent combination is invalid (Import module)
249 -- PARTY_NOT_EXIST - Party doesn't exist (Import module)
250 -- INV_CUST_ACCT - Customer party doesn't have any customer accounts (Import module)
251 -- PARTY_NOT_UNIQUE - Party in not unique in the Contract (Import and Authoring modules)
252 -- INV_ROLE_PARTY - Role and Party combination is invalid (Authoring module)
253 -- INV_ROLE_LOCATION - Role and Party Location combination is invalid (Authoring module)
254 -- : x_return_status OUT VARCHAR2(1)
255 -- : x_msg_count OUT NUMBER
256 -- : x_msg_data OUT VARCHAR2(2000)
257 -- End of comments
258 PROCEDURE validate_contract_party(
259 p_api_version IN NUMBER,
260 p_init_msg_list IN VARCHAR2,
261 p_contract_id IN NUMBER,
262 p_intent IN VARCHAR2 DEFAULT NULL,
263 p_party_role_code IN OUT NOCOPY VARCHAR2,
264 p_party_role_txt IN VARCHAR2 DEFAULT NULL,
265 p_party_id IN OUT NOCOPY NUMBER,
266 p_party_name IN VARCHAR2,
267 p_location_id IN NUMBER DEFAULT NULL,
268 p_mode IN VARCHAR2,
269 x_valid_party_flag OUT NOCOPY VARCHAR2,
270 x_error_code OUT NOCOPY VARCHAR2,
271 x_return_status OUT NOCOPY VARCHAR2,
272 x_msg_count OUT NOCOPY NUMBER,
273 x_msg_data OUT NOCOPY VARCHAR2);
274
275
276 -- Start of comments
277 --API name : validate_party_contact
278 --Type : Private.
279 --Function : Validates a party contact
280 --Pre-reqs : None.
281 --Parameters :
282 --IN : p_api_version IN NUMBER Required
283 -- : p_init_msg_list IN VARCHAR2 Optional
284 -- Default = FND_API.G_FALSE
285 -- : p_contract_id IN NUMBER Required
286 -- Contract ID of the party contact to be validated
287 -- : p_party_role_code IN VARCHAR2 Required
288 -- Role code of the party of the contact to be validated
289 -- : p_party_id IN NUMBER Required
290 -- Id of the contract party to be validated
291 -- : p_contact_id IN NUMBER Required
292 -- Id of the party contact to be validated
293 -- : p_contact_name IN VARCHAR2 Required
294 -- Name of the party contact to be validated
295 -- : p_contact_role_id IN NUMBER Required
296 -- Id of the role of the party contact to be validated
297 --OUT : x_valid_contact_flag OUT VARCHAR2(1)
298 -- : x_error_code OUT VARCHAR2(100)
299 -- Possible error codes are;
300 -- CONTACT_NOT_UNIQUE - Contact is not unique in the party
301 -- CONTACT_NOT_EXIST - Party and contact combination is invalid
302 -- : x_return_status OUT VARCHAR2(1)
303 -- : x_msg_count OUT NUMBER
304 -- : x_msg_data OUT VARCHAR2(2000)
305 -- End of comments
306 PROCEDURE validate_party_contact(
307 p_api_version IN NUMBER,
308 p_init_msg_list IN VARCHAR2,
309 p_contract_id IN NUMBER,
310 p_party_role_code IN VARCHAR2,
311 p_party_id IN NUMBER,
312 p_contact_id IN NUMBER,
313 p_contact_name IN VARCHAR2,
314 p_contact_role_id IN NUMBER,
315 x_valid_contact_flag OUT NOCOPY VARCHAR2,
316 x_error_code OUT NOCOPY VARCHAR2,
317 x_return_status OUT NOCOPY VARCHAR2,
318 x_msg_count OUT NOCOPY NUMBER,
319 x_msg_data OUT NOCOPY VARCHAR2);
320
321
322 -- Start of comments
323 --API name : validate_import_parties
324 --Type : Private.
325 --Function : Validates contract parties during import
326 --Pre-reqs : Currently only called from repository import.
327 -- : Contracts should be saved to the OKC_REP_IMP_PARTIES_T table
328 --Parameters :
329 --IN : p_api_version IN NUMBER Required
330 -- : p_init_msg_list IN VARCHAR2 Optional
331 -- Default = FND_API.G_FALSE
332 -- : p_request_id IN NUMBER Required
333 -- Concurrent Program Request ID
334 --OUT : x_return_status OUT VARCHAR2(1)
335 -- : x_msg_count OUT NUMBER
336 -- : x_msg_data OUT VARCHAR2(2000)
337 -- End of comments
338 PROCEDURE validate_import_parties(
339 p_api_version IN NUMBER,
340 p_init_msg_list IN VARCHAR2,
341 p_request_id IN NUMBER,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_msg_count OUT NOCOPY NUMBER,
344 x_msg_data OUT NOCOPY VARCHAR2);
345
346 -- Start of comments
347 --API name : validate_import_documents
348 --Type : Private.
349 --Function : Validates the contract documents stored in the interface table
350 -- in a concurrent program.
351 --Pre-reqs : Currently only called from repository import.
352 -- : Contract documents should be saved to the OKC_REP_IMP_DOCUMENTS_T table
353 --Parameters :
354 --IN : p_api_version IN NUMBER Required
355 -- : p_init_msg_list IN VARCHAR2 Optional
356 -- Default = FND_API.G_FALSE
357 -- : p_request_id IN NUMBER Required
358 -- Concurrent program request id
359 --OUT : x_return_status OUT VARCHAR2(1)
360 -- : x_msg_count OUT NUMBER
361 -- : x_msg_data OUT VARCHAR2(2000)
362 -- End of comments
363 PROCEDURE validate_import_documents(
364 p_api_version IN NUMBER,
365 p_init_msg_list IN VARCHAR2,
366 p_request_id IN NUMBER,
367 x_return_status OUT NOCOPY VARCHAR2,
368 x_msg_count OUT NOCOPY NUMBER,
369 x_msg_data OUT NOCOPY VARCHAR2);
370
371 -- Start of comments
372 --API name : validate_import_contracts
373 --Type : Private.
374 --Function : Validates contracts during import
375 --Pre-reqs : Currently only called from repository import.
376 -- : Contracts should be saved to the OKC_REP_IMP_CONTRACTS_T table
377 --Parameters :
378 --IN : p_api_version IN NUMBER Required
379 -- : p_init_msg_list IN VARCHAR2 Optional
380 -- Default = FND_API.G_FALSE
381 -- : p_request_id IN NUMBER Required
382 -- Concurrent Program Request ID
383 --OUT : x_return_status OUT VARCHAR2(1)
384 -- : x_msg_count OUT NUMBER
385 -- : x_msg_data OUT VARCHAR2(2000)
386 -- End of comments
387 PROCEDURE validate_import_contracts(
388 p_api_version IN NUMBER,
389 p_init_msg_list IN VARCHAR2,
390 p_request_id IN NUMBER,
391 x_return_status OUT NOCOPY VARCHAR2,
392 x_msg_count OUT NOCOPY NUMBER,
393 x_msg_data OUT NOCOPY VARCHAR2);
394
395
396 -- Start of comments
400 -- the valid ones into production tables:
397 --API name : validate_and_insert_contracts
398 --Type : Private.
399 --Function : Validates contracts in the interface tables, and then insert
401 -- okc_rep_contracts_all and okc_rep_contract_parties
402 -- Note that contract documents are inserted in the Java layer after this
403 --Pre-reqs : Currently only called from repository import.
404 -- : Contracts should be saved to the OKC_REP_IMP_CONTRACTS_T table
405 --Parameters :
406 --IN : p_api_version IN NUMBER Required
407 -- : p_init_msg_list IN VARCHAR2 Optional
408 -- Default = FND_API.G_FALSE
409 -- : p_request_id IN NUMBER Required
410 -- Concurrent Program Request ID
411 --OUT : x_return_status OUT VARCHAR2(1)
412 -- : x_msg_count OUT NUMBER
413 -- : x_msg_data OUT VARCHAR2(2000)
414 -- : x_number_inserted OUT NUMBER
415 -- End of comments
416 PROCEDURE validate_and_insert_contracts(
417 p_api_version IN NUMBER,
418 p_init_msg_list IN VARCHAR2,
419 p_request_id IN NUMBER,
420 x_msg_data OUT NOCOPY VARCHAR2,
424
421 x_msg_count OUT NOCOPY NUMBER,
422 x_return_status OUT NOCOPY VARCHAR2,
423 x_number_inserted OUT NOCOPY NUMBER);
425
426 -- Start of comments
427 --API name : delete_import_contract
428 --Type : Private.
429 --Function : (1) Delete the imported contract and its parties
430 -- by calling okc_rep_contract_process_pvt.delete_contract
431 -- (2) Set the contract's valid_flag to 'N' in okc_rep_imp_contracts_t
432 -- (3) Insert an error message in okc_rep_imp_errors_t
433 -- This procedure does the cleanup due to an error adding attachments
434 -- in the Java layer during repository import
435 --Pre-reqs : None
436 --Parameters :
437 --IN : p_api_version IN NUMBER Required
438 -- : p_init_msg_list IN VARCHAR2 Optional
439 -- Default = FND_API.G_FALSE
440 -- : p_commit IN VARCHAR2 Optional
441 -- Default = FND_API.G_FALSE
442 -- : p_contract_id IN NUMBER Required
443 -- Contract ID that the error is from
444 -- : p_imp_document_id IN NUMBER Required
445 -- okc_rep_imp_documents_t.imp_document_id
446 -- : p_error_msg_txt IN VARCHAR2 Required
447 -- Translated error message text
448 -- : p_program_id IN NUMBER Required
449 -- Concurrent program ID
450 -- : p_program_login_id IN NUMBER Required
451 -- Concurrent program login ID
452 -- : p_program_app_id IN NUMBER Required
453 -- Concurrent program application ID
454 -- : p_request_id IN NUMBER Required
455 -- Concurrent program request ID
456 --OUT : x_return_status OUT VARCHAR2(1)
457 -- : x_msg_count OUT NUMBER
458 -- : x_msg_data OUT VARCHAR2(2000)
459 -- End of comments
460 PROCEDURE delete_import_contract(
461 p_api_version IN NUMBER := 1.0,
462 p_init_msg_list IN VARCHAR2,
463 p_commit IN VARCHAR2,
464 p_contract_id IN NUMBER,
465 p_imp_document_id IN NUMBER,
466 p_error_msg_txt IN VARCHAR2,
467 p_program_id IN NUMBER,
468 p_program_login_id IN NUMBER,
469 p_program_app_id IN NUMBER,
470 p_request_id IN NUMBER,
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_msg_count OUT NOCOPY NUMBER,
473 x_msg_data OUT NOCOPY VARCHAR2);
474
475 -- Start of comments
476 --API name : Function get_csv_error_string
477 --Type : Private.
478 --Function : Returns one line in the CSV Error Report
479 --Pre-reqs : None.
480 --Parameters :
481 --IN : p_api_version IN NUMBER Required
482 -- : p_init_msg_list IN VARCHAR2 Optional
483 -- Default = FND_API.G_FALSE
484 -- : p_imp_contract_id IN NUMBER Required
485 -- okc_rep_imp_contracts_t.imp_contract_id
486 -- End of comments
487 FUNCTION get_csv_error_string(
488 p_api_version IN NUMBER := 1.0,
489 p_init_msg_list IN VARCHAR2,
490 p_imp_contract_id IN NUMBER
491 ) RETURN VARCHAR2;
492
493
494 -- Start of comments
495 --API name : Function get_csv_header_string
496 --Type : Private.
497 --Function : Returns the header in the csv file
498 --Pre-reqs : None.
499 --Parameters :
500 --IN : p_api_version IN NUMBER Required
501 -- : p_init_msg_list IN VARCHAR2 Optional
502 -- Default = FND_API.G_FALSE
503 -- End of comments
504 FUNCTION get_csv_header_string(
505 p_api_version IN NUMBER,
506 p_init_msg_list IN VARCHAR2
507 ) RETURN VARCHAR2;
508
509 -- Start of comments
510 --API name : get_external_userlist
511 --Type : Private.
512 --Function : Returns the external user email addresses.
513 --Pre-reqs : None.
514 --Parameters :
515 --IN : p_api_version IN NUMBER Required
516 -- : p_init_msg_list IN VARCHAR2 Optional
517 -- Default = FND_API.G_FALSE
518 -- : p_document_id IN NUMBER Required
519 -- Id of the contract
520 -- : p_document_type IN VARCHAR2 Required
521 -- Contract type.
522 -- : p_external_party_id IN NUMBER Required
523 -- External party ID
524 -- : p_external_party_role IN VARCHAR2 Required
525 -- External party role.
526 --OUT : x_external_userlist OUT VARCHAR2(1)
527 -- external contact email addresses
528 -- : x_return_status OUT VARCHAR2(1)
529 -- : x_msg_count OUT NUMBER
530 -- : x_msg_data OUT VARCHAR2(2000)
531 -- End of comments
532 PROCEDURE get_external_userlist(
533 p_api_version IN NUMBER,
534 p_init_msg_list IN VARCHAR2,
535 p_document_id IN NUMBER,
536 p_document_type IN VARCHAR2,
537 p_external_party_id IN NUMBER,
538 p_external_party_role IN VARCHAR2,
539 x_msg_data OUT NOCOPY VARCHAR2,
543
540 x_msg_count OUT NOCOPY NUMBER,
541 x_return_status OUT NOCOPY VARCHAR2,
542 x_external_userlist OUT NOCOPY VARCHAR2);
544
545 -- Start of comments
546 --API name : ok_to_commit
547 --Type : Private.
548 --Function : Returns the external user email addresses.
549 --Pre-reqs : None.
550 --Parameters :
551 --IN : p_api_version IN NUMBER Required
552 -- : p_init_msg_list IN VARCHAR2 Optional
553 -- Default = FND_API.G_FALSE
554 -- : p_doc_id IN NUMBER Required
555 -- Id of the contract
556 -- : p_validation_string IN VARCHAR2 Optional
557 -- Validation string
558 --OUT : x_return_status OUT VARCHAR2(1)
559 -- : x_msg_count OUT NUMBER
560 -- : x_msg_data OUT VARCHAR2(2000)
561 -- End of comments
562 FUNCTION ok_to_commit(
563 p_api_version IN Number,
564 p_init_msg_list IN Varchar2,
565 p_doc_id IN Number,
566 p_validation_string IN Varchar2 default NULL,
567 x_return_status OUT NOCOPY Varchar2,
568 x_msg_data OUT NOCOPY Varchar2,
569 x_msg_count OUT NOCOPY Number) RETURN VARCHAR2;
570
571 -- Start of comments
572 --API name : purge_recent_contracts
573 --Type : Private.
574 --Function : Called from OKC_PURGE_PVT package to purge
575 -- contracts that are olner than p_num_days days
576 --Pre-reqs : None.
577 --Parameters :
578 --OUT : errbuf OUT NOCOPY VARCHAR2
579 -- : retcode OUT NOCOPY VARCHAR2
580 --IN : p_num_days IN NUMBER
581 --Note :
582 -- End of comments
583
584 PROCEDURE purge_recent_contracts(
585 errbuf OUT NOCOPY VARCHAR2,
586 retcode OUT NOCOPY VARCHAR2,
587 p_num_days IN NUMBER);
588
589 -- Start of comments
590 --API name : can_update
591 --Type : Private.
592 --Function : Checks if user can update a contract
593 --Pre-reqs : None.
594 --Parameters :
595 --OUT : Return Y if user is allowed to update contracts, N if not allowed
596 --Note :
597 -- End of comments
598
599 FUNCTION can_update RETURN VARCHAR2;
600
601
602 -- Start of comments
603 --API name : is_sales_workbench
604 --Type : Private.
605 --Function : Checks if the current application is Sales Contracts Workbench or Contract Repository
606 --Pre-reqs : None.
607 --Parameters :
608 --OUT : Return Y if it is Sales Contracts Workbench, otherwise returns N
609 --Note :
610 -- End of comments
611
612 FUNCTION is_sales_workbench RETURN VARCHAR2;
613
614
615
616 -- Start of comments
617 --API name : insert_new_vendor_contact
618 --Type : Private.
619 --Function : Creates a new vendor contact and returns the newly created contact id.
620 --Pre-reqs : None.
621 --Parameters :
622 --IN : p_vendor_site_id IN NUMBER Required
623 -- Vendor site id of the contact
624 -- : p_contract_id IN NUMBER Required
625 -- Id of the contract for which the new contact being created
626 -- : p_first_name IN VARCHAR2 Required
627 -- First name of the contact
628 -- : p_last_name IN NUMBER Required
629 -- Last name of the contact
630 -- : p_area_code IN VARCHAR2 Optional
631 -- Area code of the contact phone number.
632 -- : p_phone IN NUMBER Optional
633 -- Phone number of the contact
634 -- : p_email_address IN VARCHAR2 Optional
635 -- Email address of the contact.
636 --OUT : x_vendor_contact_id OUT VARCHAR2(1)
637 -- Vendor contact id
638 -- End of comments
639 PROCEDURE insert_new_vendor_contact(
640 p_vendor_site_id IN NUMBER,
641 p_contract_id IN NUMBER,
642 p_first_name IN VARCHAR2,
643 p_last_name IN VARCHAR2,
644 p_area_code IN VARCHAR2,
645 p_phone IN VARCHAR2,
646 p_email_address IN VARCHAR2,
647 x_vendor_contact_id OUT NOCOPY NUMBER);
648
649
650
651 -- Start of comments
652 --API name : sync_con_header_attributes
653 --Type : Public.
654 --Function : Updates the header level attributes of all archived versions when they're modified in the working version
655 --Pre-reqs : None.
656 --Parameters :
657 --IN : p_api_version IN NUMBER Required
658 -- : p_init_msg_list IN VARCHAR2 Optional
659 -- : p_contract_id IN NUMBER Required
660 -- Id of the contact
661 --OUT : x_return_status OUT VARCHAR2(1)
662 -- : x_msg_count OUT NUMBER
663 -- : x_msg_data OUT VARCHAR2(2000)
664 -- End of comments
665 PROCEDURE sync_con_header_attributes(
666 p_api_version IN NUMBER,
667 p_init_msg_list IN VARCHAR2,
668 p_contract_id IN NUMBER,
669 x_msg_data OUT NOCOPY VARCHAR2,
670 x_msg_count OUT NOCOPY NUMBER,
671 x_return_status OUT NOCOPY VARCHAR2);
672
673 -- Start of comments
677 --Pre-reqs : None.
674 --API name : check_contract_doc_access
675 --Type : Private.
676 --Function : Checks access to contract docs by the current user.
678 --Parameters :
679 --IN : p_api_version IN NUMBER Required
680 -- : p_init_msg_list IN VARCHAR2 Optional
681 -- : p_contract_id IN NUMBER Required
682 -- Id of the contract whose access to be checked
683 -- : p_function_name IN VARCHAR2 Required
684 -- Name of the function whose access to be checked. Possible values OKC_REP_SELECT and OKC_REP_UPDATE
685 --OUT : x_has_access OUT VARCHAR2(1)
686 -- : x_status_code OUT VARCHAR2(30)
687 -- : x_return_status OUT VARCHAR2(1)
688 -- : x_msg_count OUT NUMBER
689 -- : x_msg_data OUT VARCHAR2(2000)
690 -- End of comments
691 PROCEDURE check_contract_doc_access(
692 p_api_version IN NUMBER,
693 p_init_msg_list IN VARCHAR2,
694 p_contract_id IN NUMBER,
695 p_version_number IN NUMBER,
696 p_function_name IN VARCHAR2,
697 x_has_access OUT NOCOPY VARCHAR2,
698 x_status_code OUT NOCOPY VARCHAR2,
699 x_archived_yn OUT NOCOPY VARCHAR2,
700 x_msg_data OUT NOCOPY VARCHAR2,
701 x_msg_count OUT NOCOPY NUMBER,
702 x_return_status OUT NOCOPY VARCHAR2);
703
704 FUNCTION get_accessible_ous RETURN VARCHAR2;
705
706 -- Start of comments
707 --API name : has_contract_access
708 --Type : Private.
709 --Function : Checks access to a quote by the current user. It first checks the profile
710 -- : "aso_enable_security_check". If this profile is set to 'No',
711 -- : the API returns 'UPDATE'. else it calls ASO_SECURITY_INT.get_quote_access
712 -- : to get the current user access.
713 -- :
714 --Pre-reqs : None.
715 --Parameters :
716 --IN : p_resource_id IN NUMBER Required
717 -- : p_quote_number IN NUMBER Required
718 --OUT : Return 'NONE' if the current user does not have access to the quote. Else it
719 -- : returns 'READ' or 'UPDATE'.
720 -- End of comments
721 FUNCTION get_quote_access
722 (
723 p_resource_id IN NUMBER,
724 p_quote_number IN NUMBER
725 ) RETURN VARCHAR2;
726
727 --Start of comments
728 --API name : contract_terms_disabled_yn
729 --Type : Private.
730 --Function : Based on the type of the contract selected for update, this function
731 -- : will return 'Y' if there exist contracts with this contract type
732 -- : which have structured terms.
733 -- : Otherwise, it will return 'N'.The Enable_Contract_Terms chkbox
734 -- : will be readonly if 'Y' is returned.It will be updateable otherwise.
735 -- :
736 --Pre-reqs : None.
737 --Parameters :
738 --IN : p_contract_type IN VARCHAR Required
739 --OUT : x_disable_contract_terms_yn OUT VARCHAR2
740 -- End of comments
741
742 PROCEDURE contract_terms_disabled_yn
743 (
744 p_contract_type IN VARCHAR2,
745 x_disable_contract_terms_yn OUT NOCOPY VARCHAR2
746 );
747
748
749 END OKC_REP_UTIL_PVT;