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