[Home] [Help]
PACKAGE: APPS.OKC_REP_QA_CHECK_PVT
Source
1 PACKAGE OKC_REP_QA_CHECK_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKCVREPQACHKS.pls 120.0.12020000.8 2013/05/03 12:36:11 skavutha ship $ */
3
4 ---------------------------------------------------------------------------
5 -- TYPE Definitions
6 ---------------------------------------------------------------------------
7 -- Contracts business events codes TBL Type
8 SUBTYPE EVENT_TBL_TYPE IS OKC_TERMS_QA_GRP.BUSDOCDATES_TBL_TYPE;
9 ---------------------------------------------------------------------------
10 -- Global VARIABLES
11 ---------------------------------------------------------------------------
12 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_REP_QA_CHECK_PVT';
13 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
14 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
15
16 ------------------------------------------------------------------------------
17 -- GLOBAL CONSTANTS
18 ------------------------------------------------------------------------------
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
22 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
23 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
24 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
25 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
26 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
27 G_REP_QA_TYPE CONSTANT VARCHAR2(30) := 'REPOSITORY';
28 G_QA_LOOKUP CONSTANT VARCHAR2(30) := 'OKC_TERM_QA_LIST';
29
30 G_QA_STS_SUCCESS CONSTANT varchar2(1) := OKC_TERMS_QA_GRP.G_QA_STS_SUCCESS;
31 G_QA_STS_ERROR CONSTANT varchar2(1) := OKC_TERMS_QA_GRP.G_QA_STS_ERROR;
32 G_QA_STS_WARNING CONSTANT varchar2(1) := OKC_TERMS_QA_GRP.G_QA_STS_WARNING;
33
34
35 G_OKC CONSTANT VARCHAR2(3) := 'OKC';
36 G_NORMAL_QA CONSTANT VARCHAR2(30) := 'NORMAL';
37
38 -- QA Checks --
39 -- Check being performed for repository
40 G_CHECK_REP_NO_EXT_PARTY CONSTANT VARCHAR2(40) := 'CHECK_REP_NO_EXTERNAL_PARTY';
41 G_CHECK_REP_NO_EFF_DATE CONSTANT VARCHAR2(40) := 'CHECK_REP_NO_EFF_DATE';
42 G_CHECK_REP_EXPIRED CONSTANT VARCHAR2(40) := 'CHECK_REP_EXPIRED';
43 G_CHECK_REP_INV_CONTRACT_TYPE CONSTANT VARCHAR2(40) := 'CHECK_REP_INV_CONTRACT_TYPE';
44 G_CHECK_REP_INV_EXT_PARTY CONSTANT VARCHAR2(40) := 'CHECK_REP_INV_EXTERNAL_PARTY';
45 G_CHECK_REP_INV_CONTACT CONSTANT VARCHAR2(40) := 'CHECK_REP_INV_CONTACT';
46 G_CHECK_REP_INV_RISK_EVENT CONSTANT VARCHAR2(40) := 'CHECK_REP_INV_RISK_EVENT';
47 G_CHECK_REP_INV_CONTACT_ROLE CONSTANT VARCHAR2(40) := 'CHECK_REP_INV_CONTACT_ROLE';
48 G_CHECK_REP_INV_E_SIGN CONSTANT VARCHAR2(40) := 'CHECK_REP_INV_E_SIGN';
49 G_CHECK_REP_CCT_NO_TASKS CONSTANT VARCHAR2(40) := 'CHECK_REP_CCT_NO_TASKS';
50 G_CHECK_REP_SBCR_CONT_ROLES CONSTANT VARCHAR2(40) := 'CHECK_REP_SBCR_CONT_ROLES';
51 G_CHECK_REP_SBCR_PREV_ACQ CONSTANT VARCHAR2(40) := 'CHECK_REP_SBCR_PREV_ACQ';
52 G_CHECK_REP_NO_APP_WF CONSTANT VARCHAR2(40) := 'CHECK_REP_NO_APP_WF';
53 G_CHECK_REP_SBCR_REC CONSTANT VARCHAR2(40) := 'CHECK_REP_SBCR_REC';
54
55 -- QA Error messages --
56 G_OKC_REP_NO_EXT_PARTY CONSTANT VARCHAR2(30) := 'OKC_REP_NO_EXTERNAL_PARTY';
57 G_OKC_REP_NO_EFF_DATE CONSTANT VARCHAR2(30) := 'OKC_REP_NO_EFF_DATE';
58 G_OKC_REP_EXPIRED CONSTANT VARCHAR2(30) := 'OKC_REP_EXPIRED';
59 G_OKC_REP_INV_CONTRACT_TYPE CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTRACT_TYPE';
60 G_OKC_REP_INV_EXT_PARTY CONSTANT VARCHAR2(30) := 'OKC_REP_INV_EXTERNAL_PARTY';
61 G_OKC_REP_INV_CONTACT CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTACT';
62 G_OKC_REP_INV_RISK_EVENT CONSTANT VARCHAR2(30) := 'OKC_REP_INV_RISK_EVENT';
63 G_OKC_REP_INV_CONTACT_ROLE CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTACT_ROLE';
64 G_OKC_REP_INV_E_SIGN CONSTANT VARCHAR2(30) := 'OKC_REP_INV_E_SIGN';
65 G_OKC_REP_CCT_NO_TASKS CONSTANT VARCHAR2(40) := 'OKC_REP_CCT_NO_TASKS';
66 G_OKC_REP_NO_APP_WF CONSTANT VARCHAR2(40) := 'OKC_REP_NO_APP_WF';
67 G_OKC_REP_SBCR_PREV_ACQ CONSTANT VARCHAR2(40) := 'OKC_REP_SBCR_PREV_ACQ';
68 G_OKC_REP_SBCR_CONT_ROLES CONSTANT VARCHAR2(40) := 'OKC_REP_SBCR_CONT_ROLES';
69 G_OKC_REP_SBCR_REC CONSTANT VARCHAR2(40) := 'OKC_REP_SBCR_REC';
70
71 -- QA Suggestion messages --
72 G_OKC_REP_NO_EXT_PARTY_S CONSTANT VARCHAR2(50) := 'OKC_REP_NO_EXTERNAL_PARTY_S';
73 G_OKC_REP_NO_EFF_DATE_S CONSTANT VARCHAR2(50) := 'OKC_REP_NO_EFF_DATE_S';
74 G_OKC_REP_EXPIRED_S CONSTANT VARCHAR2(50) := 'OKC_REP_EXPIRED_S';
75 G_OKC_REP_INV_CONTRACT_TYPE_S CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTRACT_TYPE_S';
76 G_OKC_REP_INV_EXT_PARTY_S CONSTANT VARCHAR2(30) := 'OKC_REP_INV_EXTERNAL_PARTY_S';
77 G_OKC_REP_INV_CONTACT_S CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTACT_S';
78 G_OKC_REP_INV_RISK_EVENT_S CONSTANT VARCHAR2(30) := 'OKC_REP_INV_RISK_EVENT_S';
79 G_OKC_REP_INV_CONTACT_ROLE_S CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTACT_ROLE_S';
80 G_OKC_REP_INV_E_SIGN_S CONSTANT VARCHAR2(30) := 'OKC_REP_INV_E_SIGN_S';
81 G_REP_INV_E_SIGN_CONTACT_S CONSTANT VARCHAR2(30) := 'OKC_REP_INV_E_SIGN_CONTACT_S';
82 G_OKC_REP_CCT_NO_TASKS_S CONSTANT VARCHAR2(40) := 'OKC_REP_CCT_NO_TASKS_S';
83 G_OKC_REP_SBCR_CONT_ROLES_S CONSTANT VARCHAR2(40) := 'OKC_REP_SBCR_CONT_ROLES_S';
84 G_OKC_REP_SBCR_PREV_ACQ_S CONSTANT VARCHAR2(40) := 'OKC_REP_SBCR_PREV_ACQ_S';
85 G_OKC_REP_NO_APP_WF_S CONSTANT VARCHAR2(40) := 'OKC_REP_NO_APP_WF_S';
86 G_OKC_REP_SBCR_REC_S CONSTANT VARCHAR2(40) := 'OKC_REP_SBCR_REC_S';
87
88 -- QA Error short descriptions --
89 G_OKC_REP_NO_EXT_PARTY_SD CONSTANT VARCHAR2(30) := 'OKC_REP_NO_EXTERNAL_PARTY_SD';
90 G_OKC_REP_NO_EFF_DATE_SD CONSTANT VARCHAR2(30) := 'OKC_REP_NO_EFF_DATE_SD';
91 G_OKC_REP_EXPIRED_SD CONSTANT VARCHAR2(30) := 'OKC_REP_EXPIRED_SD';
92 G_OKC_REP_INV_CONTRACT_TYPE_SD CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTRACT_TYPE_SD';
93 G_OKC_REP_INV_EXT_PARTY_SD CONSTANT VARCHAR2(30) := 'OKC_REP_INV_EXTERNAL_PARTY_SD';
94 G_OKC_REP_INV_CONTACT_SD CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTACT_SD';
95 G_OKC_REP_INV_RISK_EVENT_SD CONSTANT VARCHAR2(30) := 'OKC_REP_INV_RISK_EVENT_SD';
96 G_OKC_REP_INV_CONTACT_ROLE_SD CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTACT_ROLE_SD';
97 G_OKC_REP_INV_E_SIGN_SD CONSTANT VARCHAR2(30) := 'OKC_REP_INV_E_SIGN_SD' ;
98 G_REP_INV_E_SIGN_CONTACT_SD CONSTANT VARCHAR2(30) := 'OKC_REP_INV_E_SIGN_CONTACT_SD' ;
99 G_OKC_REP_CCT_NO_TASKS_SD CONSTANT VARCHAR2(40) := 'OKC_REP_CCT_NO_TASKS_SD';
100 G_OKC_REP_NO_APP_WF_SD CONSTANT VARCHAR2(40) := 'OKC_REP_NO_APP_WF_SD';
101 G_OKC_REP_SBCR_PREV_ACQ_SD CONSTANT VARCHAR2(40) := 'OKC_REP_SBCR_PREV_ACQ_SD';
102 G_OKC_REP_SBCR_CONT_ROLES_SD CONSTANT VARCHAR2(40) := 'OKC_REP_SBCR_CONT_ROLES_SD';
103 G_OKC_REP_SBCR_REC_SD CONSTANT VARCHAR2(40) := 'OKC_REP_SBCR_REC_SD';
104
105 -- QA Title messages --
106 G_OKC_REP_NO_EXT_PARTY_T CONSTANT VARCHAR2(50) := 'OKC_REP_NO_EXTERNAL_PARTY_T';
107 G_OKC_REP_NO_EFF_DATE_T CONSTANT VARCHAR2(50) := 'OKC_REP_NO_EFF_DATE_T';
108 G_OKC_REP_EXPIRED_T CONSTANT VARCHAR2(50) := 'OKC_REP_EXPIRED_T';
109 G_OKC_REP_INV_CONTRACT_TYPE_T CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTRACT_TYPE_T';
110 G_OKC_REP_INV_EXT_PARTY_T CONSTANT VARCHAR2(30) := 'OKC_REP_INV_EXTERNAL_PARTY_T';
111 G_OKC_REP_INV_CONTACT_T CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTACT_T';
112 G_OKC_REP_INV_RISK_EVENT_T CONSTANT VARCHAR2(30) := 'OKC_REP_INV_RISK_EVENT_T';
113 G_OKC_REP_INV_CONTACT_ROLE_T CONSTANT VARCHAR2(30) := 'OKC_REP_INV_CONTACT_ROLE_T';
114 G_OKC_REP_INV_E_SIGN_T CONSTANT VARCHAR2(30) := 'OKC_REP_INV_E_SIGN_T';
115 G_OKC_REP_INV_E_SIGN_T1 CONSTANT VARCHAR2(30) := 'OKC_REP_INV_E_SIGN_T1';
116 G_REP_INV_E_SIGN_CONTACT_T CONSTANT VARCHAR2(30) := 'OKC_REP_INV_E_SIGN_CONTACT_T';
117 G_REP_INV_E_SIGN_CONTACT_T1 CONSTANT VARCHAR2(30) := 'OKC_REP_INV_E_SIGN_CONTACT_T1';
118 G_OKC_REP_CCT_NO_TASKS_T CONSTANT VARCHAR2(30) := 'OKC_REP_CCT_NO_TASKS_T';
119 G_OKC_REP_SBCR_CONT_ROLES_T CONSTANT VARCHAR2(30) := 'OKC_REP_SBCR_CONT_ROLES_T';
120 G_OKC_REP_SBCR_PREV_ACQ_T CONSTANT VARCHAR2(30) := 'OKC_REP_SBCR_PREV_ACQ_T';
121 G_OKC_REP_NO_APP_WF_T CONSTANT VARCHAR2(30) := 'OKC_REP_NO_APP_WF_T';
122 G_OKC_REP_SBCR_REC_T CONSTANT VARCHAR2(30) := 'OKC_REP_SBCR_REC_T';
123
124 -- Contract events - deliverables integration
125 G_CONTRACT_EXPIRE_EVENT CONSTANT VARCHAR2(200) := 'CONTRACT_EXPIRE';
126 G_CONTRACT_EFFECTIVE_EVENT CONSTANT VARCHAR2(200) := 'CONTRACT_EFFECTIVE';
127
128 -- Required for Contract not found error message
129 G_INVALID_CONTRACT_ID_MSG CONSTANT VARCHAR2(200) := 'OKC_REP_INVALID_CONTRACT_ID';
130 G_CONTRACT_ID_TOKEN CONSTANT VARCHAR2(200) := 'CONTRACT_ID';
131 G_PARTY_NAME_TOKEN CONSTANT VARCHAR2(200) := 'PARTY_NAME';
132 G_CONTACT_NAME_TOKEN CONSTANT VARCHAR2(200) := 'CONTACT_NAME';
133 G_CONTRACT_TYPE_TOKEN CONSTANT VARCHAR2(200) := 'CONTRACT_TYPE';
134 G_CONTACT_ROLE_TOKEN CONSTANT VARCHAR2(200) := 'CONTACT_ROLE';
135 G_RISK_EVENT_TOKEN CONSTANT VARCHAR2(200) := 'RISK_EVENT';
136
137 -- Party Role codes
138 G_PARTY_ROLE_INTERNAL CONSTANT VARCHAR2(30) := 'INTERNAL_ORG';
139 G_PARTY_ROLE_PARTNER CONSTANT VARCHAR2(30) := 'PARTNER_ORG';
140 G_PARTY_ROLE_CUSTOMER CONSTANT VARCHAR2(30) := 'CUSTOMER_ORG';
141 G_PARTY_ROLE_SUPPLIER CONSTANT VARCHAR2(30) := 'SUPPLIER_ORG';
142
143 TYPE okc_doc_qa_lists_rec_type IS RECORD (
144 qa_code OKC_DOC_QA_LISTS.QA_CODE%TYPE,
145 severity_flag OKC_DOC_QA_LISTS.severity_flag%TYPE,
146 enable_qa_yn OKC_DOC_QA_LISTS.enable_qa_yn%TYPE);
147
148 TYPE okc_doc_qa_lists_tbl_type IS TABLE OF okc_doc_qa_lists_rec_type
149 INDEX BY BINARY_INTEGER;
150
151 -----------------------------------------------------------
152
153 -----------------------------------------------------------
154
155 ---------------------------------------------------------------------------
156 -- Procedures and Functions
157 ---------------------------------------------------------------------------
158
159 -- Start of comments
160 --API name : perform_contract_qa_check
161 --Type : Private.
162 --Function : This API performs QA check on a Repository Contract. The API check for:
163 -- 1. Check contract for no external party (Warning)
164 -- 2. Check contract for no effective date (Error)
165 -- 3. Check contract for invalid contract type
166 -- 4. Check contract for invalid external party type
167 -- 5. Check contract for invalid contact
168 -- 6. Check contract for invalid Risk Event
169 -- 7. Check contract for invalid Contact Role
170 -- 8. Calls OKC_DELIVERABLE_PROCESS_PVT.validate_deliverable_for_qa to qa check the
171 -- deliverables.
172 --Pre-reqs : None.
173 --Parameters :
174 --IN : p_api_version IN NUMBER Required
175 -- : p_init_msg_list IN VARCHAR2 Optional
176 -- Default = FND_API.G_FALSE
177 -- : p_contract_id IN NUMBER Required
178 -- Contract ID of the contract to be QA checked
179 --OUT : x_return_status OUT VARCHAR2(1)
180 -- : x_msg_count OUT NUMBER
181 -- : x_msg_data OUT VARCHAR2(2000)
182 -- : x_qa_return_status OUT VARCHAR2 (1)
183 -- QA Check return status. Possible values are S, W, E
184 -- : x_sequence_id OUT NUMBER
185 -- Sequence id of the qa check errors in OKC_QA_ERRORS_T table
186 -- Note :
187 -- End of comments
188
189 PROCEDURE perform_contract_qa_check (
190 p_api_version IN NUMBER,
191 p_init_msg_list IN VARCHAR2,
192 p_contract_id IN NUMBER,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_qa_return_status OUT NOCOPY VARCHAR2,
197 x_sequence_id OUT NOCOPY NUMBER);
198
199
200 -- Start of comments
201 --API name : insert_deliverables_qa_checks
202 --Type : Private.
203 --Function : This API inserts QA check list of Deliverables for the specified
204 -- Contract Type into the table OKC_DOC_QA_LISTS
205 --Pre-reqs : None.
206 --Parameters :
207 --IN : p_api_version IN NUMBER Required
208 -- : p_init_msg_list IN VARCHAR2 Required
209 -- : p_contract_type IN NUMBER Required
210 -- Contract Type for which the QA checkes to be added
211 --OUT : x_return_status OUT VARCHAR2(1)
212 -- : x_msg_count OUT NUMBER
213 -- : x_msg_data OUT VARCHAR2(2000)
214 -- Note :
215 -- End of comments
216
217 PROCEDURE insert_deliverables_qa_checks (
218 p_api_version IN NUMBER,
219 p_init_msg_list IN VARCHAR2,
220 p_contract_type IN VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2,
223 x_return_status OUT NOCOPY VARCHAR2);
224
225
226 END OKC_REP_QA_CHECK_PVT;