1 PACKAGE OKC_REP_CONTRACT_IMP_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKCVREPIMPS.pls 120.0.12020000.7 2013/06/04 13:02:16 nbingi noship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ------------------------------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_REP_CONTRACT_IMP_PVT';
9 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKC';
10 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
11
12 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
13 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
14 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
15
16 -- Error Types for import errors table
17 G_IMP_CONTRACT_ERROR CONSTANT VARCHAR2(50) := 'CONTRACT';
18 G_IMP_PARTY_ERROR CONSTANT VARCHAR2(50) := 'PARTY';
19 G_IMP_DOCUMENT_ERROR CONSTANT VARCHAR2(50) := 'DOCUMENT';
20 G_IMP_RISKS_ERROR CONSTANT VARCHAR2(50) := 'RISKS';
21 G_IMP_CONTACTS_ERROR CONSTANT VARCHAR2(50) := 'CONTACTS';
22
23 G_NEW_VERSION_ERROR CONSTANT VARCHAR2(50) := 'REVISION';
24 G_DELETE_CONTRACT_ERROR CONSTANT VARCHAR2(50) := 'DELETE';
25
26 G_STATUS_SIGNED CONSTANT VARCHAR2(30) := 'SIGNED';
27
28
29 ---------------------------------------------------------------------------
30 -- GLOBAL MESSAGE CONSTANTS
31 ---------------------------------------------------------------------------
32 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
33
34 G_INVALID_CONTRACT_ID_MSG CONSTANT VARCHAR2(200) := 'OKC_REP_INVALID_CONTRACT_ID';
35 G_INVALID_BUS_DOC_ID_MSG CONSTANT VARCHAR2(200) := 'OKC_REP_INVALID_BUS_DOC_ID';
36
37 ---------------------------------------------------------------------------
38
39 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
40 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
41
42 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
43 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
44 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
45
46 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
47 G_CURRENT_ORG_ID NUMBER := -99;
48 G_ORG_ID NUMBER ;
49 G_RETURN_CODE_SUCCESS CONSTANT NUMBER := 0;
50 G_RETURN_CODE_ERROR CONSTANT NUMBER := 2;
51
52 Procedure create_contract(p_api_version IN NUMBER,
53 p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
54 p_contract_parties_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_parties_tbl_type,
55 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
56 p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
57 p_create_contract IN VARCHAR2 := fnd_api.g_true,
58 p_commit IN VARCHAR2 := fnd_api.g_false,
59 x_msg_data OUT NOCOPY VARCHAR2,
60 x_msg_count OUT NOCOPY NUMBER,
61 x_return_status OUT NOCOPY VARCHAR2
62 );
63
64 Procedure populate_contract_hdr(p_api_version IN NUMBER,
65 p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
66 p_run_id IN NUMBER ,
67 x_imp_contract_id OUT NOCOPY NUMBER,
68 x_msg_data OUT NOCOPY VARCHAR2,
69 x_msg_count OUT NOCOPY NUMBER,
70 x_return_status OUT NOCOPY VARCHAR2
71 );
72
73 PROCEDURE populate_contract_parties (p_api_version IN NUMBER,
74 p_run_id IN NUMBER,
75 p_contract_parties_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_parties_tbl_type,
76 p_imp_contract_id IN NUMBER,
77 x_msg_data OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_return_status OUT NOCOPY VARCHAR2);
80
81 PROCEDURE populate_party_contacts(p_api_version IN NUMBER,
82 p_run_id IN NUMBER,
83 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
84 p_imp_contract_id IN NUMBER,
85 x_msg_data OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER,
87 x_return_status OUT NOCOPY VARCHAR2);
88
89 PROCEDURE populate_contract_risks(p_api_version IN NUMBER,
90 p_run_id IN NUMBER DEFAULT NULL,
91 p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
92 p_imp_contract_id IN NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94 x_msg_count OUT NOCOPY NUMBER,
95 x_return_status OUT NOCOPY VARCHAR2);
96
97 Procedure delete_contract(p_api_version IN NUMBER,
98 p_commit IN VARCHAR2 := fnd_api.g_false,
99 p_contract_id IN NUMBER,
100 x_msg_data OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_return_status OUT NOCOPY VARCHAR2
103 );
104
105 PROCEDURE validate_and_insert_con_cp(errbuf OUT NOCOPY VARCHAR2,
106 retcode OUT NOCOPY VARCHAR2,
107 p_org_name IN NUMBER,
108 p_contract_type IN VARCHAR2,
109 p_from_date IN DATE,
110 p_to_date IN DATE,
111 p_validate_only IN VARCHAR2 );
112
113 /* PROCEDURE delete_contract_risks(p_api_version IN NUMBER,
114 p_run_id IN NUMBER DEFAULT NULL,
115 p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
116 p_imp_contract_id IN NUMBER,
117 x_msg_data OUT NOCOPY VARCHAR2,
118 x_msg_count OUT NOCOPY NUMBER,
119 x_return_status OUT NOCOPY VARCHAR2);
120 PROCEDURE delete_party_contacts(p_api_version IN NUMBER,
121 p_run_id IN NUMBER,
122 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
123 p_imp_contract_id IN NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 x_return_status OUT NOCOPY VARCHAR2);
127 PROCEDURE delete_parties(p_api_version IN NUMBER,
128 p_run_id IN NUMBER,
129 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
130 p_imp_contract_id IN NUMBER,
131 x_msg_data OUT NOCOPY VARCHAR2,
132 x_msg_count OUT NOCOPY NUMBER,
133 x_return_status OUT NOCOPY VARCHAR2);
134 */
135
136 /*
137 Creates a contract with the data given in the parameters.
138 The relation of parent document which is obtained in p_document_rec is stored in the okc_rep_contract_usages table
139 */
140
141 Procedure create_contract(p_api_version IN NUMBER,
142 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
143 p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
144 p_contract_parties_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_parties_tbl_type,
145 p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
146 p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
147 p_commit IN VARCHAR2 := fnd_api.g_false,
148 x_msg_data OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_return_status OUT NOCOPY VARCHAR2
151 );
152
153 /*
154 Deletes the Contract for the given document type.
155 */
156
157 Procedure delete_contract(p_api_version IN NUMBER,
158 p_commit IN VARCHAR2 := fnd_api.g_false,
159 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
160 p_contract_type IN VARCHAR2 DEFAULT NULL,
161 p_contract_id IN NUMBER DEFAULT NULL,
162 x_msg_data OUT NOCOPY VARCHAR2,
163 x_msg_count OUT NOCOPY NUMBER,
164 x_return_status OUT NOCOPY VARCHAR2
165 );
166
167
168
169 /*
170 Creates a new version of the contract contract in draft status.
171 Updates to the contract should be done from UI.
172 */
173
174 Procedure create_new_contract_version(p_api_version IN NUMBER,
175 p_contract_id IN NUMBER,
176 p_run_id IN NUMBER,
177 p_commit IN VARCHAR2 := fnd_api.g_false,
178 x_msg_data OUT NOCOPY VARCHAR2,
179 x_msg_count OUT NOCOPY NUMBER,
180 x_return_status OUT NOCOPY VARCHAR2
181 );
182
183
184 /*
185 Creates a new version of the contract contract in draft status.
186 Updates to the contract should be done from UI.
187
188 The relation of parent document which is obtained in p_document_rec is stored in the okc_rep_contract_usages table
189 */
190
191 Procedure create_new_contract_version(p_api_version IN NUMBER,
192 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
193 p_contract_type IN VARCHAR2 DEFAULT NULL,
194 p_contract_id IN NUMBER DEFAULT NULL,
195 p_run_id IN NUMBER,
196 p_commit IN VARCHAR2 := fnd_api.g_false,
197 x_msg_data OUT NOCOPY VARCHAR2,
198 x_msg_count OUT NOCOPY NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2
200 );
201
202 PROCEDURE check_contract_exists(p_api_version IN NUMBER,
203 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
204 x_contract_type IN OUT NOCOPY VARCHAR2 ,
205 x_contract_id IN OUT NOCOPY NUMBER ,
206 x_contract_version_num IN OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2,
208 x_msg_count OUT NOCOPY NUMBER,
209 x_return_status OUT NOCOPY VARCHAR2
210 );
211
212 PROCEDURE Activate_tasks_closeout(p_api_version IN NUMBER,
213 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
214 p_physical_completion_date IN DATE ,
215 p_init_msg_list IN VARCHAR2,
216 p_commit IN VARCHAR2,
217 x_msg_data OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_return_status OUT NOCOPY VARCHAR2
220 );
221
222
223 PROCEDURE check_contract_exists(p_api_version IN NUMBER,
224 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
225 x_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
226 x_msg_data OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_return_status OUT NOCOPY VARCHAR2
229 );
230
231 PROCEDURE cancel_contract(p_api_version IN NUMBER,
232 p_commit IN VARCHAR2 := fnd_api.g_false,
233 p_contract_id IN NUMBER,
234 x_msg_data OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_return_status OUT NOCOPY VARCHAR2
237 );
238
239 Procedure delete_cancel_contract(p_api_version IN NUMBER,
240 p_commit IN VARCHAR2 := fnd_api.g_false,
241 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
242 p_contract_type IN VARCHAR2 DEFAULT NULL,
243 p_contract_id IN NUMBER DEFAULT NULL,
244 x_msg_data OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_return_status OUT NOCOPY VARCHAR2
247 );
248
249 /*
250 Cancels the Contract for the given document type.
251 */
252
253 Procedure cancel_contract(p_api_version IN NUMBER,
254 p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
255 p_contract_type IN VARCHAR2 DEFAULT NULL,
256 p_contract_id IN NUMBER DEFAULT NULL,
257 p_commit IN VARCHAR2 := fnd_api.g_false,
258 x_msg_data OUT NOCOPY VARCHAR2,
259 x_msg_count OUT NOCOPY NUMBER,
260 x_return_status OUT NOCOPY VARCHAR2
261 ) ;
262
263
264 /*
265 * Procedure to migrate date from okc_rep_contract_rels table to okc_rep_contract_usages table.
266 * This migration is applicable for 12.2.2.
267 * 1. For one row in okc_rep_contract_rels table, all the target related contracts are brought and these are inserted
268 * against each version of source contract.
269 * 2. If the relationship(combination of contract_id, contract_type, contract_version_num, business_document_id, business_document_type,
270 business_document_version, relationship_role_id) already exists in the new table (okc_rep_contract_usages), then this procedure doesn't insert it again in the new table
271 * 3. Marks the rows in okc_rep_contract_usagaes with source_code as 'MIGRATION' and populates corresponding requires_id, program_id.
272 *
273 * Parameters :
274 * p_purge_and_rerun if 'Yes', deletes all rows in okc_rep_contract_usages with source_code as 'MIGRATION and migrates the date
275 * if 'No', starts migrating the data, if the row already exists doesn't insert again.
276 * default value : 'No' (if no value is entered by the user, then it takes as 'No')
277 *
278 * p_simulate if 'Yes', starts migrating the data, logs all successful migrated rows and errored rows. But dont' commmit the data.
279 * Can be used for testing the migration process before actual run of this program.
280 * if 'No', migrates data and commits data
281 * default value : 'Yes' ( if no value is entered by the user, then it takes as 'Yes')
282 */
283
284 PROCEDURE migrate_contract_relations(errbuf OUT NOCOPY VARCHAR2,
285 retcode OUT NOCOPY VARCHAR2,
286 p_purge_and_rerun IN VARCHAR2,
287 p_simulate IN VARCHAR2);
288
289 END OKC_REP_CONTRACT_IMP_PVT;
290
[Home] [Help]