[Home] [Help]
PACKAGE BODY: APPS.CS_CONTRACTS_PUB
Source
1 PACKAGE BODY CS_CONTRACTS_PUB AS
2 /* $Header: csctpapb.pls 115.11 99/07/16 08:53:07 porting ship $ */
3 ----------------------------------------------------------------------------
4 -- GLOBAL CONSTANTS
5 ----------------------------------------------------------------------------
6 G_DATE_FORMAT CONSTANT VARCHAR2(200) := 'DD-MM-YYYY';
7 ----------------------------------------------------------------------------
8 -- GLOBAL MESSAGES
9 ----------------------------------------------------------------------------
10 -- Message for NO_CHILDREN should be something like:
11 -- "Invalid record for: PARENT_TABLE. No children exist in CHILD_TABLE"
12 G_NO_CHILDREN_MSG CONSTANT VARCHAR2(200) := 'CS_CONTRACTS_NO_CHILDREN';
13 G_SERVICE_ATTACHED_TO_TEMPLATE CONSTANT VARCHAR2(200) := 'CS_CONTRACTS_SVC_ATT_TEMPLATE';
14 G_INCOMPATIBLE_CONTRACT_LEVELS CONSTANT VARCHAR2(200) := 'CS_CONTRACTS_INCMPTBL_COV_LVLS';
15 G_VALIDATION_SUCCESSFUL CONSTANT VARCHAR2(200) := 'CS_CONTRACTS_VAL_SUCCESS';
16 G_COVERAGE_OVERLAP CONSTANT VARCHAR2(200) := 'CS_CONTRACTS_COVERAGE_OVERLAP';
17 G_INVALID_SERVICE_START_DATE CONSTANT VARCHAR2(200) := 'CS_CONTRACTS_INVD_SVC_START_DT';
18 G_INVALID_SERVICE_END_DATE CONSTANT VARCHAR2(200) := 'CS_CONTRACTS_INVD_SVC_END_DT';
19 G_INVALID_TXN_GRP_DATE CONSTANT VARCHAR2(200) := 'CS_CONTRACTS_INVD_TXNGRP_DT';
20 G_TXN_GRP_DATE_TOKEN CONSTANT VARCHAR2(200) := 'DATE1';
21 G_COV_START_DATE_TOKEN CONSTANT VARCHAR2(200) := 'DATE2';
22 G_COV_END_DATE_TOKEN CONSTANT VARCHAR2(200) := 'DATE3';
23 G_MISMATCH_COV_SERVICE_DATES CONSTANT VARCHAR2(200) := 'CS_CONTRACTS_INVD_SVC_COV_DT';
24 G_COVERAGE_START_DATE_TOKEN CONSTANT VARCHAR2(200) := 'DATE1';
25 G_COVERAGE_END_DATE_TOKEN CONSTANT VARCHAR2(200) := 'DATE2';
26 G_SVC_START_DATE_TOKEN CONSTANT VARCHAR2(200) := 'DATE3';
27 G_SVC_END_DATE_TOKEN CONSTANT VARCHAR2(200) := 'DATE4';
28
29 --------------------------------------------------------------------------------
30 -- PROCEDURE set_msg_no_children
31 --------------------------------------------------------------------------------
32 PROCEDURE set_msg_no_children (
33 p_parent_name IN VARCHAR2,
34 p_child_name IN VARCHAR2
35 ) IS
36 -- PL/SQL Block
37 BEGIN
38 FND_MESSAGE.set_name(G_APP_NAME, G_NO_CHILDREN_MSG);
39 FND_MESSAGE.set_token('PARENT_TABLE', p_parent_name);
40 FND_MESSAGE.set_token('CHILD_TABLE', p_child_name);
41 END set_msg_no_children;
42 --------------------------------------------------------------------------------
43 -- FUNCTION get_service_name
44 --------------------------------------------------------------------------------
45 FUNCTION get_service_name (
46 p_services_rec IN services_all_rec_type
47 ) RETURN VARCHAR2 IS
48 CURSOR get_service_name_cur (
49 p_cp_service_id IN MTL_SYSTEM_ITEMS_KFV.INVENTORY_ITEM_ID%TYPE) IS
50 SELECT concatenated_segments
51 FROM mtl_system_items_kfv
52 WHERE inventory_item_id = p_cp_service_id
53 AND organization_id = FND_PROFILE.VALUE_SPECIFIC('SO_ORGANIZATION_ID');
54
55 l_retval get_service_name_cur%ROWTYPE;
56 BEGIN
57 OPEN get_service_name_cur (p_services_rec.cp_service_id);
58 FETCH get_service_name_cur INTO l_retval;
59 IF (get_service_name_cur%NOTFOUND) THEN
60 l_retval.concatenated_segments := TO_CHAR(p_services_rec.cp_service_id);
61 END IF;
62 CLOSE get_service_name_cur;
63 RETURN (l_retval.concatenated_segments);
64 END get_service_name;
65 --------------------------------------------------------------------------------
66 -- FUNCTION populate_contract_rec
67 --------------------------------------------------------------------------------
68 FUNCTION populate_contract_rec (
69 p_contract_id IN CS_CONTRACTS_ALL.CONTRACT_ID%TYPE
70 ) RETURN Contract_Rec_Type IS
71 CURSOR get_contract_rec (p_contract_id IN CS_CONTRACTS_ALL.CONTRACT_ID%TYPE) IS
72 SELECT *
73 FROM cs_contracts_all
74 WHERE contract_id = p_contract_id;
75
76 l_contract_rec Contract_Rec_Type;
77 l_no_data_found BOOLEAN := TRUE;
78 BEGIN
79 OPEN get_contract_rec(p_contract_id);
80 FETCH get_contract_rec INTO l_contract_rec;
81 l_no_data_found := get_contract_rec%NOTFOUND;
82 CLOSE get_contract_rec;
83
84 IF (l_no_data_found) THEN
85 FND_MESSAGE.set_name(G_APP_NAME, G_INVALID_VALUE);
86 FND_MESSAGE.set_token('COL_NAME', 'CONTRACT_ID');
87 END IF;
88 RETURN (l_contract_rec);
89 END populate_contract_rec;
90 --------------------------------------------------------------------------------
91 -- PROCEDURE validate_contract
92 --------------------------------------------------------------------------------
93 PROCEDURE validate_contract
97 p_validation_level IN NUMBER,
94 (
95 p_api_version IN NUMBER,
96 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
98 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
99 x_return_status OUT VARCHAR2,
100 x_msg_count OUT NUMBER,
101 x_msg_data OUT VARCHAR2,
102 p_contract_rec IN Contract_Rec_Type
103 ) IS
104 l_api_name CONSTANT VARCHAR2(30) := 'validate_contract';
105 l_api_version CONSTANT NUMBER := 1;
106 l_return_status VARCHAR2(1);
107 --------------------------------------------------------------------------------
108 -- FUNCTION val_cov_txn_grps_children
109 --------------------------------------------------------------------------------
110 FUNCTION val_cov_txn_grps_children (
111 p_coverages_rec IN coverages_rec_type
112 ) RETURN VARCHAR2 IS
113 l_index NUMBER;
114 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
115 l_return_error_exc EXCEPTION;
116 CURSOR chk_csr (p_coverage_id IN CS_COVERAGE_TXN_GROUPS.COVERAGE_ID%TYPE) IS
117 SELECT *
118 FROM cs_coverage_txn_groups
119 WHERE coverage_id = p_coverage_id;
120 l_cov_txn_grps_tbl coverage_txn_groups_tbl_type;
121 --------------------------------
122 -- FUNCTION val_start_end_dates
123 --------------------------------
124 FUNCTION val_start_end_dates (
125 p_cov_txn_grps_rec IN coverage_txn_groups_rec_type,
126 p_coverages_rec IN coverages_rec_type
127 ) RETURN VARCHAR2 IS
128 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
129 l_return_error_exc EXCEPTION;
130 BEGIN
131 -- Validate START and END dates of Coverage_Txn_Groups fall within the
132 -- START and END dates of CS_COVERAGES
136 TO_CHAR(p_cov_txn_grps_rec.coverage_start_date, G_DATE_FORMAT));
133 IF (p_cov_txn_grps_rec.coverage_start_date < p_coverages_rec.start_date_active) THEN
134 FND_MESSAGE.set_name(G_APP_NAME, G_INVALID_TXN_GRP_DATE);
135 FND_MESSAGE.set_token(G_TXN_GRP_DATE_TOKEN,
137 FND_MESSAGE.set_token(G_COV_START_DATE_TOKEN,
138 TO_CHAR(p_coverages_rec.start_date_active, G_DATE_FORMAT));
139 FND_MESSAGE.set_token(G_COV_END_DATE_TOKEN,
140 TO_CHAR(p_coverages_rec.end_date_active, G_DATE_FORMAT));
141 RAISE l_return_error_exc;
142 ELSIF (p_cov_txn_grps_rec.coverage_end_date > p_coverages_rec.end_date_active) THEN
143 FND_MESSAGE.set_name(G_APP_NAME, G_INVALID_TXN_GRP_DATE);
144 FND_MESSAGE.set_token(G_TXN_GRP_DATE_TOKEN,
145 TO_CHAR(p_cov_txn_grps_rec.coverage_end_date, G_DATE_FORMAT));
146 FND_MESSAGE.set_token(G_COV_START_DATE_TOKEN,
147 TO_CHAR(p_coverages_rec.start_date_active, G_DATE_FORMAT));
148 FND_MESSAGE.set_token(G_COV_END_DATE_TOKEN, TO_CHAR(p_coverages_rec.end_date_active, G_DATE_FORMAT));
149 RAISE l_return_error_exc;
150 END IF;
151 RETURN(l_return_status);
152 EXCEPTION
153 WHEN l_return_error_exc THEN
154 l_return_status := FND_API.G_RET_STS_ERROR;
155 RETURN(l_return_status);
156 END val_start_end_dates;
157 BEGIN
158 FOR l_rec IN chk_csr (p_coverages_rec.coverage_id) LOOP
159 l_cov_txn_grps_tbl(chk_csr%ROWCOUNT) := l_rec;
160 END LOOP;
161
162 l_index := l_cov_txn_grps_tbl.FIRST;
163 LOOP
164 -----------------------------------------------------------------------
165 -- Validate START and END dates of Coverage_Txn_Groups fall within the
166 -- START and END dates of CS_COVERAGES
167 -----------------------------------------------------------------------
168 l_return_status := val_start_end_dates(l_cov_txn_grps_tbl(l_index), p_coverages_rec);
169 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
170 RAISE l_return_error_exc;
171 END IF;
172 EXIT WHEN (l_index >= l_cov_txn_grps_tbl.LAST);
173 l_index := l_cov_txn_grps_tbl.NEXT(l_index);
174 END LOOP;
175
176 RETURN(l_return_status);
177 EXCEPTION
181 --------------------------------------------------------------------------------
178 WHEN l_return_error_exc THEN
179 RETURN(l_return_status);
180 END val_cov_txn_grps_children;
182 -- FUNCTION cov_txn_grp_children_exist
183 --------------------------------------------------------------------------------
184 FUNCTION cov_txn_grp_children_exist (
185 p_coverage_rec IN coverages_rec_type
186 ) RETURN BOOLEAN IS
187 l_children_exist BOOLEAN := TRUE;
188 CURSOR chk_csr
189 (p_coverage_id IN CS_COVERAGE_TXN_GROUPS.COVERAGE_ID%TYPE) IS
190 SELECT *
191 FROM cs_coverage_txn_groups
192 WHERE coverage_id = p_coverage_id;
193 l_chk_rec chk_csr%ROWTYPE;
194 BEGIN
195 OPEN chk_csr (p_coverage_rec.coverage_id);
196 FETCH chk_csr INTO l_chk_rec;
197 l_children_exist := chk_csr%FOUND;
198 CLOSE chk_csr;
199 RETURN(l_children_exist);
200 END cov_txn_grp_children_exist;
201 --------------------------------------------------------------------------------
202 -- FUNCTION val_coverage_levels_children
203 --------------------------------------------------------------------------------
204 FUNCTION val_coverage_levels_children (
205 p_services_rec IN services_all_rec_type
209 l_return_error_exc EXCEPTION;
206 ) RETURN VARCHAR2 IS
207 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
208 l_index NUMBER := 0;
210 l_children_exist BOOLEAN := FALSE;
211
212 CURSOR chk_csr (
213 p_cp_service_id IN CS_CONTRACT_COV_LEVELS.CP_SERVICE_ID%TYPE) IS
214 SELECT *
215 FROM cs_contract_cov_levels
216 WHERE cp_service_id = p_cp_service_id;
217
218 l_cov_lvls_tbl contract_cov_lvls_tbl_type;
219 ------------------------------------------
220 -- FUNCTION cov_products_children_exist --
221 ------------------------------------------
222 FUNCTION cov_products_children_exist (
223 p_cov_lvl_rec IN contract_cov_lvls_rec_type
224 ) RETURN BOOLEAN IS
225 l_retval BOOLEAN := FALSE;
226 CURSOR chk_csr (
227 p_cov_lvl_id IN CS_COVERED_PRODUCTS.COVERAGE_LEVEL_ID%TYPE) IS
228 SELECT *
229 FROM cs_covered_products
230 WHERE coverage_level_id = p_cov_lvl_id;
231
232 l_rec chk_csr%ROWTYPE;
233 BEGIN
234 OPEN chk_csr(p_cov_lvl_rec.coverage_level_id);
235 FETCH chk_csr INTO l_rec;
236 l_retval := chk_csr%FOUND;
237 CLOSE chk_csr;
238 RETURN (l_retval);
239 END cov_products_children_exist;
240 BEGIN
244 IF (l_cov_lvls_tbl.COUNT > 0) THEN
241 FOR l_rec IN chk_csr(p_services_rec.cp_service_id) LOOP
242 l_cov_lvls_tbl(chk_csr%ROWCOUNT) := l_rec;
243 END LOOP;
245 -----------------------------------------------------------------------
246 -- If a COVERAGE_LEVEL has COVERED_PRODUCTS then ALL COVERAGE_LEVELS
247 -- must have COVERED_PRODUCTS. Conversly, if a COVERAGE_LEVEL has no
248 -- COVERED_PRODUCTS then ALL COVERAGE_LEVEL must NOT have
249 -- COVERED_PRODUCTS.
250 -----------------------------------------------------------------------
251 l_index := l_cov_lvls_tbl.FIRST;
252 LOOP
253 IF (l_index = l_cov_lvls_tbl.FIRST) THEN
254 -- We set this flag only on the first record.
255 l_children_exist := cov_products_children_exist(
256 l_cov_lvls_tbl(l_index));
257 ELSIF ((cov_products_children_exist(l_cov_lvls_tbl(l_index))) <>
258 (l_children_exist)) THEN
259 FND_MESSAGE.set_name(G_APP_NAME, G_INCOMPATIBLE_CONTRACT_LEVELS);
260 FND_MESSAGE.set_token('SERVICE_ID',
261 get_service_name(p_services_rec));
262 l_return_status := FND_API.G_RET_STS_ERROR;
263 RAISE l_return_error_exc;
264 END IF;
265 EXIT WHEN (l_index >= l_cov_lvls_tbl.LAST);
266 l_index := l_cov_lvls_tbl.NEXT(l_index);
267 END LOOP;
268 ELSE -- No children in CS_CONTRACT_COVERAGE_LEVEL for CS_CP_SERVICE_ALL,
269 -- RAISE ERROR
270 set_msg_no_children('CS_CP_SERVICE_ALL', 'CS_CONTRACT_COVERAGE_LEVEL');
271 l_return_status := FND_API.G_RET_STS_ERROR;
272 RAISE l_return_error_exc;
273 END IF;
274
275 RETURN(l_return_status);
276 EXCEPTION
280 --------------------------------------------------------------------------------
277 WHEN l_return_error_exc THEN
278 RETURN(l_return_status);
279 END val_coverage_levels_children;
281 -- FUNCTION val_coverages_children
282 --------------------------------------------------------------------------------
283 FUNCTION val_coverages_children (
284 p_services_rec IN services_all_rec_type
285 ) RETURN VARCHAR2 IS
286 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
287 l_index NUMBER := 0;
288 l_return_error_exc EXCEPTION;
289
290 CURSOR chk_csr (p_coverage_id IN CS_COVERAGES.COVERAGE_ID%TYPE) IS
291 SELECT *
292 FROM cs_coverages
293 WHERE coverage_id = p_coverage_id;
294
295 l_coverages_tbl coverages_tbl_type;
296 -----------------------------------------
297 -- PROCEDURE verify_cov_children_exist --
298 -----------------------------------------
299 PROCEDURE verify_cov_children_exist (
300 p_coverages_tbl IN coverages_tbl_type
301 ) IS
302 BEGIN
303 IF (p_coverages_tbl.COUNT = 0) THEN
304 set_msg_no_children('CS_CP_SERVICES_ALL', 'CS_COVERAGES');
305 l_return_status := FND_API.G_RET_STS_ERROR;
306 RAISE l_return_error_exc;
307 END IF;
308 END verify_cov_children_exist;
309 ---------------------------------------
310 -- PROCEDURE verify_not_cov_template --
311 ---------------------------------------
315 i NUMBER := 0;
312 PROCEDURE verify_not_cov_template (
313 p_coverages_tbl IN coverages_tbl_type
314 ) IS
316 BEGIN
317 i := p_coverages_tbl.FIRST;
318 LOOP
319 IF (p_coverages_tbl(i).TEMPLATE_FLAG = 'Y') THEN
320 FND_MESSAGE.set_name(G_APP_NAME, G_SERVICE_ATTACHED_TO_TEMPLATE);
321 FND_MESSAGE.set_token('SERVICE_ID',get_service_name(p_services_rec));
322 FND_MESSAGE.set_token('TEMPLATE_NAME', p_coverages_tbl(i).name);
323 l_return_status := FND_API.G_RET_STS_ERROR;
324 RAISE l_return_error_exc;
325 END IF;
326 EXIT WHEN (i >= p_coverages_tbl.LAST);
327 i := p_coverages_tbl.NEXT(i);
328 END LOOP;
329 END verify_not_cov_template;
330 --------------------------------
331 -- FUNCTION val_start_end_dates
332 --------------------------------
333 FUNCTION val_start_end_dates (
334 p_coverages_rec IN coverages_rec_type,
335 p_services_rec IN services_all_rec_type
336 ) RETURN VARCHAR2 IS
337 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
338 l_return_error_exc EXCEPTION;
339 BEGIN
340 -- Validate START and END dates of Coverages fall within the
341 -- START and END dates of CS_CP_SERVICES
342 IF ((p_coverages_rec.start_date_active <> p_services_rec.start_date_active) OR
343 (p_coverages_rec.end_date_active <> p_services_rec.end_date_active)) THEN
347 FND_MESSAGE.set_token(G_COVERAGE_END_DATE_TOKEN,
344 FND_MESSAGE.set_name(G_APP_NAME, G_MISMATCH_COV_SERVICE_DATES);
345 FND_MESSAGE.set_token(G_COVERAGE_START_DATE_TOKEN,
346 TO_CHAR(p_coverages_rec.start_date_active, G_DATE_FORMAT));
348 TO_CHAR(p_coverages_rec.end_date_active, G_DATE_FORMAT));
349 FND_MESSAGE.set_token(G_SVC_START_DATE_TOKEN,
350 TO_CHAR(p_services_rec.start_date_active, G_DATE_FORMAT));
351 FND_MESSAGE.set_token(G_SVC_END_DATE_TOKEN,
352 TO_CHAR(p_services_rec.end_date_active, G_DATE_FORMAT));
353 RAISE l_return_error_exc;
354 END IF;
355 RETURN (l_return_status);
356 EXCEPTION
357 WHEN l_return_error_exc THEN
358 l_return_status := FND_API.G_RET_STS_ERROR;
359 RETURN (l_return_status);
360 END val_start_end_dates;
361 BEGIN
362 FOR l_rec IN chk_csr(p_services_rec.coverage_schedule_id) LOOP
363 l_coverages_tbl(chk_csr%ROWCOUNT) := l_rec;
364 END LOOP;
365 ------------------------------------
366 -- Verify children exist in COVERAGE
367 ------------------------------------
368 verify_cov_children_exist(l_coverages_tbl);
369 -------------------------------------------------
370 -- Verify the COVERAGE is NOT a COVERAGE TEMPLATE
371 -------------------------------------------------
372 verify_not_cov_template(l_coverages_tbl);
373
374 l_index := l_coverages_tbl.FIRST;
375 LOOP
376 ------------------------------------------------------------------
377 -- Validate START and END dates of the Coverages fall withing the
378 -- START and END dates of CS_CP_SERVICES
379 ------------------------------------------------------------------
383 END IF;
380 l_return_status:= val_start_end_dates (l_coverages_tbl(l_index), p_services_rec);
381 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
382 RAISE l_return_error_exc;
384 ------------------------------------------------------------------------------
385 -- An optional relationship exists between CS_COVERAGES and
386 -- CS_COVERAGE_TXN_GRPS.
387 -- Normally, no check is required, however, CS_COVERAGE_TXN_GROUPS cannot
388 -- exist w/o records in CS_COV_TXN_GRP_CTRS. Therefore, if children
389 -- records exist in CS_COVERAGE_TXN_GROUPS for a given
390 -- CS_COVERAGES.COVERAGE_ID, then we must verify that records also exist
391 -- in CS_COV_TXN_GRP_CTRS.
392 ------------------------------------------------------------------------------
393 IF (cov_txn_grp_children_exist(l_coverages_tbl(l_index))) THEN
394 l_return_status := val_cov_txn_grps_children (l_coverages_tbl(l_index));
395 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
396 RAISE l_return_error_exc;
397 END IF;
398 END IF;
399 EXIT WHEN (l_index >= l_coverages_tbl.LAST);
400 l_index := l_coverages_tbl.NEXT(l_index);
401 END LOOP;
402
403 RETURN(l_return_status);
404 EXCEPTION
405 WHEN l_return_error_exc THEN
406 RETURN(l_return_status);
407 END val_coverages_children;
408 --------------------------------------------------------------------------------
409 -- FUNCTION val_services_children
410 --------------------------------------------------------------------------------
411 FUNCTION val_services_children (
412 p_contract_rec IN contract_rec_type
413 ) RETURN VARCHAR2 IS
414 l_index NUMBER := 0;
415 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
416 l_return_error_exc EXCEPTION;
417
418 CURSOR chk_csr (p_contract_id IN CS_CONTRACTS_ALL.CONTRACT_ID%TYPE) IS
419 SELECT *
420 FROM cs_cp_services_all
421 WHERE contract_id = p_contract_id;
422
423 l_services_all_tbl services_all_tbl_type;
424 ---------------------------------
425 -- FUNCTION val_services_items --
426 ---------------------------------
427 FUNCTION val_services_items (
428 p_services_all_rec IN services_all_rec_type,
429 p_contract_rec IN contract_rec_type
430 ) RETURN VARCHAR2 IS
431 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
432 BEGIN
433 ----------------------------------------------------------------------
434 -- Check FIRST_BILL_DATE and BILLING_FREQUENCY_PERIOD are NOT NULL.
435 -- While the 2 columns are allowed to be NULL in CS_CP_SERVICES_ALL,
436 -- the columns should not be NULL for a contract.
437 -- If the fields are null in CS_CP_SERVICES_ALL, as well as
438 -- CS_CONTRACTS_ALL then give a warning.
439 ----------------------------------------------------------------------
440 IF ((p_services_all_rec.FIRST_BILL_DATE IS NULL)
441 AND (p_contract_rec.FIRST_BILL_DATE IS NULL)) THEN
442 TAPI_DEV_KIT.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN,
443 'FIRST_BILL_DATE');
444 l_return_status := TAPI_DEV_KIT.G_RET_STS_WARNING;
445 END IF;
446
447 IF ((p_services_all_rec.BILLING_FREQUENCY_PERIOD IS NULL)
448 AND (p_contract_rec.BILLING_FREQUENCY_PERIOD IS NULL)) THEN
449 TAPI_DEV_KIT.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN,
450 'BILLING_FREQUENCY_PERIOD');
451 l_return_status := TAPI_DEV_KIT.G_RET_STS_WARNING;
452 END IF;
453
454 RETURN(l_return_status);
455 END val_services_items;
456 -------------------------------------
457 -- FUNCTION check_coverage_overlap --
458 -------------------------------------
459 FUNCTION check_coverage_overlap (
460 p_services_rec services_all_rec_type
461 ) RETURN VARCHAR2 IS
462 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
463 l_return_error_exc EXCEPTION;
464 l_overlap_flag VARCHAR2(1) := 'N';
465 CURSOR chk_csr (p_cp_service_id IN CS_CONTRACT_COV_LEVELS.CP_SERVICE_ID%TYPE) IS
466 SELECT *
467 FROM cs_contract_cov_levels cccl
468 WHERE cp_service_id = p_cp_service_id
469 AND NOT EXISTS (SELECT 'x'
470 FROM cs_covered_products ccp
471 WHERE ccp.coverage_level_id = cccl.coverage_level_id);
472 BEGIN
476 -- that overlap each other
473 FOR l_rec IN chk_csr(p_services_rec.cp_service_id) LOOP
474 ------------------------------------------------------------------------------
475 -- Use CS_COVERAGE_SERVICE_PUB.Check_Service_Overlap to find any services
477 ------------------------------------------------------------------------------
478 CS_COVERAGE_SERVICE_PUB.check_service_overlap (
479 p_api_version => p_api_version,
480 p_init_msg_list => p_init_msg_list,
481 p_commit => p_commit,
482 p_service_inv_item_id => p_services_rec.service_inventory_item_id,
483 p_organization_id => p_services_rec.service_manufacturing_org_id,
484 p_customer_product_id => p_services_rec.customer_product_id,
485 p_coverage_level_code => l_rec.coverage_level_code,
486 p_coverage_level_value => l_rec.coverage_level_value,
487 p_coverage_level_id => l_rec.coverage_level_id,
488 p_start_date_active => p_services_rec.start_date_active,
489 p_end_date_active => p_services_rec.end_date_active,
490 x_overlap_flag => l_overlap_flag,
491 x_return_status => x_return_status,
492 x_msg_count => x_msg_count,
493 x_msg_data => x_msg_data);
494 IF (l_overlap_flag = 'Y') THEN
495 FND_MESSAGE.set_name(G_APP_NAME, G_COVERAGE_OVERLAP);
496 l_return_status := FND_API.G_RET_STS_ERROR;
497 END IF;
498 END LOOP;
499 RETURN(l_return_status);
500 EXCEPTION
501 WHEN l_return_error_exc THEN
505 RETURN(l_return_status);
502 IF (chk_csr%ISOPEN) THEN
503 CLOSE chk_csr;
504 END IF;
506 END check_coverage_overlap;
507 -------------------------------
508 -- FUNCTION val_start_end_dates
509 -------------------------------
510 FUNCTION val_start_end_dates (
511 p_services_rec IN services_all_rec_type,
512 p_contract_rec IN contract_rec_type
513 ) RETURN VARCHAR2 IS
514 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
515 l_return_error_exc EXCEPTION;
516 BEGIN
517 -- Validate START and END dates of the service fall within the
518 -- START and END dates of CS_CONTRACTS
519 IF (p_services_rec.start_date_active < p_contract_rec.start_date_active) THEN
520 FND_MESSAGE.set_name(G_APP_NAME, G_INVALID_SERVICE_START_DATE);
521 RAISE l_return_error_exc;
522 ELSIF (p_services_rec.end_date_active > p_contract_rec.end_date_active) THEN
523 FND_MESSAGE.set_name(G_APP_NAME, G_INVALID_SERVICE_END_DATE);
524 RAISE l_return_error_exc;
525 END IF;
526 RETURN(l_return_status);
527 EXCEPTION
528 WHEN l_return_error_exc THEN
529 FND_MESSAGE.set_token('SERVICE_NAME', get_service_name(p_services_rec));
530 l_return_status := FND_API.G_RET_STS_ERROR;
531 RETURN(l_return_status);
532 END val_start_end_dates;
533 BEGIN
534 FOR l_csr_rec IN chk_csr(p_contract_rec.contract_id) LOOP
535 l_services_all_tbl(chk_csr%ROWCOUNT) := l_csr_rec;
536 END LOOP;
537 IF (l_services_all_tbl.COUNT = 0) THEN
541 END IF;
538 set_msg_no_children('CS_CONTRACTS_ALL', 'CS_CP_SERVICES_ALL');
539 l_return_status := FND_API.G_RET_STS_ERROR;
540 RAISE l_return_error_exc;
542
543 -- validate all records
544 l_index := l_services_all_tbl.FIRST;
545 LOOP
546 ---------------------------------------------------------------
547 -- Validate any column values (min, max, date issues, not null)
548 ---------------------------------------------------------------
549 l_return_status := val_services_items(l_services_all_tbl(l_index), p_contract_rec);
550 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
551 RAISE l_return_error_exc;
552 END IF;
553 --------------------------------------------------------------
554 -- Validate START and END dates of the service fall within the
555 -- START and END dates of CS_CONTRACTS
556 --------------------------------------------------------------
557 l_return_status := val_start_end_dates(l_services_all_tbl(l_index), p_contract_rec);
558 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
559 RAISE l_return_error_exc;
560 END IF;
561 -------------------------------
562 -- Validate no coverage overlap
563 -------------------------------
564 l_return_status := check_coverage_overlap(l_services_all_tbl(l_index));
565 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
566 RAISE l_return_error_exc;
567 END IF;
571 l_return_status := val_coverage_levels_children(l_services_all_tbl(l_index));
568 ------------------------------------------
569 -- Validate children of CS_CP_SERVICES_ALL
570 ------------------------------------------
572 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
573 RAISE l_return_error_exc;
574 END IF;
575
576 l_return_status := val_coverages_children(l_services_all_tbl(l_index));
577 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
578 RAISE l_return_error_exc;
579 END IF;
580 EXIT WHEN (l_index >= l_services_all_tbl.LAST);
581 l_index := l_services_all_tbl.NEXT(l_index);
582 END LOOP;
583 RETURN(l_return_status);
584 EXCEPTION
585 WHEN l_return_error_exc THEN
586 RETURN(l_return_status);
587 END val_services_children;
588 --------------------------------------------------------------------------------
589 -- FUNCTION val_contract_children
590 --------------------------------------------------------------------------------
591 FUNCTION val_contract_children (
592 p_contract_rec IN contract_rec_type
593 ) RETURN VARCHAR2 IS
594 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
595 BEGIN
596 l_return_status := val_services_children(p_contract_rec);
600 -- PROCEDURE validate_contract
597 RETURN(l_return_status);
598 END val_contract_children;
599 --------------------------------------------------------------------------------
601 --------------------------------------------------------------------------------
602 BEGIN
603 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
604 G_PKG_NAME,
605 l_api_version,
606 p_api_version,
607 p_init_msg_list,
608 '_Pvt',
609 x_return_status);
610 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
612 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
616 ---------------------------------------------------------------------------
613 RAISE FND_API.G_EXC_ERROR;
614 END IF;
615
617 -- Validate all children of the contract
618 ---------------------------------------------------------------------------
619 l_return_status := val_contract_children(p_contract_rec);
620 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
622 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
623 RAISE FND_API.G_EXC_ERROR;
624 ELSIF (l_return_status = TAPI_DEV_KIT.G_RET_STS_WARNING) THEN
625 RAISE TAPI_DEV_KIT.G_EXC_WARNING;
626 END IF;
627
628 -- Return Success
629 x_return_status := FND_API.G_RET_STS_SUCCESS;
630
631 FND_MESSAGE.set_name(G_APP_NAME, G_VALIDATION_SUCCESSFUL);
632 FND_MESSAGE.set_token('CONTRACT_NUMBER', p_contract_rec.contract_number);
633
634 -- COMMENTED OUT 10/23/98 JSU
635 -- TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
636 x_msg_count := 1;
637 x_msg_data := FND_MESSAGE.GET;
638 EXCEPTION
639 WHEN FND_API.G_EXC_ERROR THEN
640 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
641 (
642 l_api_name,
643 G_PKG_NAME,
647 '_Pvt'
644 'FND_API.G_RET_STS_ERROR',
645 x_msg_count,
646 x_msg_data,
648 );
649 APP_EXCEPTION.RAISE_EXCEPTION;
650 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
651 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
652 (
653 l_api_name,
654 G_PKG_NAME,
655 'FND_API.G_RET_STS_UNEXP_ERROR',
656 x_msg_count,
657 x_msg_data,
658 '_Pvt'
659 );
660 APP_EXCEPTION.RAISE_EXCEPTION;
661 WHEN TAPI_DEV_KIT.G_EXC_WARNING THEN
662 -- Just exit out of the procedure, we do not want
663 -- TAPI_DEV_KIT.HANDLE_EXCEPTIONS because the first thing it does is
664 -- rollback all transactions.
665 x_return_status := TAPI_DEV_KIT.G_RET_STS_WARNING;
666 x_msg_count := 1;
667 x_msg_data := FND_MESSAGE.GET;
668
672 -- x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
669 -- COMMENTED OUT 17-SEP-98 JSU: Forms cannot handle this style
670 -- of messaging
671 -- WHEN OTHERS THEN
673 -- (
674 -- l_api_name,
675 -- G_PKG_NAME,
676 -- 'OTHERS',
677 -- x_msg_count,
678 -- x_msg_data,
679 -- '_Pvt'
680 -- );
681 END validate_contract;
682 PROCEDURE validate_contract
683 (
684 p_api_version IN NUMBER,
685 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
686 p_validation_level IN NUMBER,
687 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
688 x_return_status OUT VARCHAR2,
689 x_msg_count OUT NUMBER,
690 x_msg_data OUT VARCHAR2,
691 p_contract_id IN CS_CONTRACTS.CONTRACT_ID%TYPE
692 -- COMMENTED OUT 17-SEP-98 DEVELOPER/2000 FORMS uses PL/SQL 1.6 which cannot
693 -- handle selective parameter passing
694 -- p_contract_number IN CS_CONTRACTS.CONTRACT_NUMBER%TYPE := NULL,
695 -- p_workflow IN CS_CONTRACTS.WORKFLOW%TYPE := NULL,
696 -- p_agreement_id IN CS_CONTRACTS.AGREEMENT_ID%TYPE := NULL,
697 -- p_price_list_id IN CS_CONTRACTS.PRICE_LIST_ID%TYPE := NULL,
698 -- p_currency_code IN CS_CONTRACTS.CURRENCY_CODE%TYPE := NULL,
702 -- p_invoicing_rule_id IN CS_CONTRACTS.INVOICING_RULE_ID%TYPE := NULL,
699 -- p_conversion_type_code IN CS_CONTRACTS.CONVERSION_TYPE_CODE%TYPE := NULL,
700 -- p_conversion_rate IN CS_CONTRACTS.CONVERSION_RATE%TYPE := NULL,
701 -- p_conversion_date IN CS_CONTRACTS.CONVERSION_DATE%TYPE := NULL,
703 -- p_accounting_rule_id IN CS_CONTRACTS.ACCOUNTING_RULE_ID%TYPE := NULL,
704 -- p_billing_frequency_period IN CS_CONTRACTS.BILLING_FREQUENCY_PERIOD%TYPE := NULL,
705 -- p_first_bill_date IN CS_CONTRACTS.FIRST_BILL_DATE%TYPE := NULL,
706 -- p_next_bill_date IN CS_CONTRACTS.NEXT_BILL_DATE%TYPE := NULL,
707 -- p_create_sales_order IN CS_CONTRACTS.CREATE_SALES_ORDER%TYPE := NULL,
708 -- p_renewal_rule IN CS_CONTRACTS.RENEWAL_RULE%TYPE := NULL,
709 -- p_termination_rule IN CS_CONTRACTS.TERMINATION_RULE%TYPE := NULL,
710 -- p_bill_to_site_use_id IN CS_CONTRACTS.BILL_TO_SITE_USE_ID%TYPE := NULL,
711 -- p_contract_status_id IN CS_CONTRACTS.CONTRACT_STATUS_ID%TYPE := NULL,
712 -- p_contract_type_id IN CS_CONTRACTS.CONTRACT_TYPE_ID%TYPE := NULL,
713 -- p_contract_template_id IN CS_CONTRACTS.CONTRACT_TEMPLATE_ID%TYPE := NULL,
714 -- p_contract_group_id IN CS_CONTRACTS.CONTRACT_GROUP_ID%TYPE := NULL,
715 -- p_customer_id IN CS_CONTRACTS.CUSTOMER_ID%TYPE := NULL,
716 -- p_duration IN CS_CONTRACTS.DURATION%TYPE := NULL,
717 -- p_period_code IN CS_CONTRACTS.PERIOD_CODE%TYPE := NULL,
718 -- p_ship_to_site_use_id IN CS_CONTRACTS.SHIP_TO_SITE_USE_ID%TYPE := NULL,
719 -- p_salesperson_id IN CS_CONTRACTS.SALESPERSON_ID%TYPE := NULL,
720 -- p_ordered_by_contact_id IN CS_CONTRACTS.ORDERED_BY_CONTACT_ID%TYPE := NULL,
721 -- p_source_code IN CS_CONTRACTS.SOURCE_CODE%TYPE := NULL,
725 -- p_bill_on IN CS_CONTRACTS.BILL_ON%TYPE := NULL,
722 -- p_source_reference IN CS_CONTRACTS.SOURCE_REFERENCE%TYPE := NULL,
723 -- p_terms_id IN CS_CONTRACTS.TERMS_ID%TYPE := NULL,
724 -- p_po_number IN CS_CONTRACTS.PO_NUMBER%TYPE := NULL,
726 -- p_tax_handling IN CS_CONTRACTS.TAX_HANDLING%TYPE := NULL,
727 -- p_tax_exempt_num IN CS_CONTRACTS.TAX_EXEMPT_NUM%TYPE := NULL,
728 -- p_tax_exempt_reason_code IN CS_CONTRACTS.TAX_EXEMPT_REASON_CODE%TYPE := NULL,
729 -- p_contract_amount IN CS_CONTRACTS.CONTRACT_AMOUNT%TYPE := NULL,
730 -- p_auto_renewal_flag IN CS_CONTRACTS.AUTO_RENEWAL_FLAG%TYPE := NULL,
731 -- p_original_end_date IN CS_CONTRACTS.ORIGINAL_END_DATE%TYPE := NULL,
732 -- p_terminate_reason_code IN CS_CONTRACTS.TERMINATE_REASON_CODE%TYPE := NULL,
733 -- p_discount_id IN CS_CONTRACTS.DISCOUNT_ID%TYPE := NULL,
734 -- p_po_required_to_service IN CS_CONTRACTS.PO_REQUIRED_TO_SERVICE%TYPE := NULL,
735 -- p_pre_payment_required IN CS_CONTRACTS.PRE_PAYMENT_REQUIRED%TYPE := NULL,
736 -- p_last_update_date IN CS_CONTRACTS.LAST_UPDATE_DATE%TYPE := NULL,
737 -- p_last_updated_by IN CS_CONTRACTS.LAST_UPDATED_BY%TYPE := NULL,
738 -- p_creation_date IN CS_CONTRACTS.CREATION_DATE%TYPE := NULL,
739 -- p_created_by IN CS_CONTRACTS.CREATED_BY%TYPE := NULL,
740 -- p_last_update_login IN CS_CONTRACTS.LAST_UPDATE_LOGIN%TYPE := NULL,
741 -- p_start_date_active IN CS_CONTRACTS.START_DATE_ACTIVE%TYPE := NULL,
742 -- p_end_date_active IN CS_CONTRACTS.END_DATE_ACTIVE%TYPE := NULL,
743 -- p_attribute1 IN CS_CONTRACTS.ATTRIBUTE1%TYPE := NULL,
744 -- p_attribute2 IN CS_CONTRACTS.ATTRIBUTE2%TYPE := NULL,
745 -- p_attribute3 IN CS_CONTRACTS.ATTRIBUTE3%TYPE := NULL,
749 -- p_attribute7 IN CS_CONTRACTS.ATTRIBUTE7%TYPE := NULL,
746 -- p_attribute4 IN CS_CONTRACTS.ATTRIBUTE4%TYPE := NULL,
747 -- p_attribute5 IN CS_CONTRACTS.ATTRIBUTE5%TYPE := NULL,
748 -- p_attribute6 IN CS_CONTRACTS.ATTRIBUTE6%TYPE := NULL,
750 -- p_attribute8 IN CS_CONTRACTS.ATTRIBUTE8%TYPE := NULL,
751 -- p_attribute9 IN CS_CONTRACTS.ATTRIBUTE9%TYPE := NULL,
752 -- p_attribute10 IN CS_CONTRACTS.ATTRIBUTE10%TYPE := NULL,
753 -- p_attribute11 IN CS_CONTRACTS.ATTRIBUTE11%TYPE := NULL,
754 -- p_attribute12 IN CS_CONTRACTS.ATTRIBUTE12%TYPE := NULL,
755 -- p_attribute13 IN CS_CONTRACTS.ATTRIBUTE13%TYPE := NULL,
756 -- p_attribute14 IN CS_CONTRACTS.ATTRIBUTE14%TYPE := NULL,
757 -- p_attribute15 IN CS_CONTRACTS.ATTRIBUTE15%TYPE := NULL,
758 -- p_context IN CS_CONTRACTS.CONTEXT%TYPE := NULL,
759 -- p_object_version_number IN CS_CONTRACTS.OBJECT_VERSION_NUMBER%TYPE := NULL
760 ) IS
761 l_api_name CONSTANT VARCHAR2(30) := 'validate_contract';
762 l_api_version CONSTANT NUMBER := 1;
763 l_return_status VARCHAR2(1);
764 l_contract_rec Contract_Rec_Type;
765 BEGIN
766 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
767 G_PKG_NAME,
768 l_api_version,
769 p_api_version,
770 p_init_msg_list,
771 '_Pvt',
772 x_return_status);
773 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
778 -- The calling program SHOULD pass in all appropriate parameters.
775 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
776 RAISE FND_API.G_EXC_ERROR;
777 END IF;
779 -- However, because of the limitations of FORMS 4.5 we will populate the
780 -- record ourselves.
781 l_contract_rec := populate_contract_rec(p_contract_id);
782 -- l_contract_rec.CONTRACT_ID := p_contract_id;
783 -- l_contract_rec.CONTRACT_NUMBER := p_contract_number;
784 -- l_contract_rec.WORKFLOW := p_workflow;
785 -- l_contract_rec.AGREEMENT_ID := p_agreement_id;
786 -- l_contract_rec.PRICE_LIST_ID := p_price_list_id;
787 -- l_contract_rec.CURRENCY_CODE := p_currency_code;
788 -- l_contract_rec.CONVERSION_TYPE_CODE := p_conversion_type_code;
789 -- l_contract_rec.CONVERSION_RATE := p_conversion_rate;
790 -- l_contract_rec.CONVERSION_DATE := p_conversion_date;
791 -- l_contract_rec.INVOICING_RULE_ID := p_invoicing_rule_id;
792 -- l_contract_rec.ACCOUNTING_RULE_ID := p_accounting_rule_id;
793 -- l_contract_rec.BILLING_FREQUENCY_PERIOD := p_billing_frequency_period;
794 -- l_contract_rec.FIRST_BILL_DATE := p_first_bill_date;
795 -- l_contract_rec.NEXT_BILL_DATE := p_next_bill_date;
796 -- l_contract_rec.CREATE_SALES_ORDER := p_create_sales_order;
797 -- l_contract_rec.RENEWAL_RULE := p_renewal_rule;
798 -- l_contract_rec.TERMINATION_RULE := p_termination_rule;
799 -- l_contract_rec.BILL_TO_SITE_USE_ID := p_bill_to_site_use_id;
800 -- l_contract_rec.CONTRACT_STATUS_ID := p_contract_status_id;
801 -- l_contract_rec.CONTRACT_TYPE_ID := p_contract_type_id;
802 -- l_contract_rec.CONTRACT_TEMPLATE_ID := p_contract_template_id;
803 -- l_contract_rec.CONTRACT_GROUP_ID := p_contract_group_id;
804 -- l_contract_rec.CUSTOMER_ID := p_customer_id;
805 -- l_contract_rec.DURATION := p_duration;
806 -- l_contract_rec.PERIOD_CODE := p_period_code;
807 -- l_contract_rec.SHIP_TO_SITE_USE_ID := p_ship_to_site_use_id;
808 -- l_contract_rec.SALESPERSON_ID := p_salesperson_id;
809 -- l_contract_rec.ORDERED_BY_CONTACT_ID := p_ordered_by_contact_id;
810 -- l_contract_rec.SOURCE_CODE := p_source_code;
811 -- l_contract_rec.SOURCE_REFERENCE := p_source_reference;
812 -- l_contract_rec.TERMS_ID := p_terms_id;
813 -- l_contract_rec.PO_NUMBER := p_po_number;
814 -- l_contract_rec.BILL_ON := p_bill_on;
815 -- l_contract_rec.TAX_HANDLING := p_tax_handling;
816 -- l_contract_rec.TAX_EXEMPT_NUM := p_tax_exempt_num;
817 -- l_contract_rec.TAX_EXEMPT_REASON_CODE := p_tax_exempt_reason_code;
818 -- l_contract_rec.CONTRACT_AMOUNT := p_contract_amount;
819 -- l_contract_rec.AUTO_RENEWAL_FLAG := p_auto_renewal_flag;
820 -- l_contract_rec.ORIGINAL_END_DATE := p_original_end_date;
821 -- l_contract_rec.TERMINATE_REASON_CODE := p_terminate_reason_code;
822 -- l_contract_rec.DISCOUNT_ID := p_discount_id;
823 -- l_contract_rec.PO_REQUIRED_TO_SERVICE := p_po_required_to_service;
824 -- l_contract_rec.PRE_PAYMENT_REQUIRED := p_pre_payment_required;
825 -- l_contract_rec.LAST_UPDATE_DATE := p_last_update_date;
826 -- l_contract_rec.LAST_UPDATED_BY := p_last_updated_by;
827 -- l_contract_rec.CREATION_DATE := p_creation_date;
828 -- l_contract_rec.CREATED_BY := p_created_by;
829 -- l_contract_rec.LAST_UPDATE_LOGIN := p_last_update_login;
830 -- l_contract_rec.START_DATE_ACTIVE := p_start_date_active;
831 -- l_contract_rec.END_DATE_ACTIVE := p_end_date_active;
832 -- l_contract_rec.ATTRIBUTE1 := p_attribute1;
833 -- l_contract_rec.ATTRIBUTE2 := p_attribute2;
834 -- l_contract_rec.ATTRIBUTE3 := p_attribute3;
838 -- l_contract_rec.ATTRIBUTE7 := p_attribute7;
835 -- l_contract_rec.ATTRIBUTE4 := p_attribute4;
836 -- l_contract_rec.ATTRIBUTE5 := p_attribute5;
837 -- l_contract_rec.ATTRIBUTE6 := p_attribute6;
839 -- l_contract_rec.ATTRIBUTE8 := p_attribute8;
840 -- l_contract_rec.ATTRIBUTE9 := p_attribute9;
841 -- l_contract_rec.ATTRIBUTE10 := p_attribute10;
842 -- l_contract_rec.ATTRIBUTE11 := p_attribute11;
843 -- l_contract_rec.ATTRIBUTE12 := p_attribute12;
844 -- l_contract_rec.ATTRIBUTE13 := p_attribute13;
845 -- l_contract_rec.ATTRIBUTE14 := p_attribute14;
846 -- l_contract_rec.ATTRIBUTE15 := p_attribute15;
847 -- l_contract_rec.CONTEXT := p_context;
848 -- l_contract_rec.OBJECT_VERSION_NUMBER := p_object_version_number;
849 validate_contract(
850 p_api_version,
851 p_init_msg_list,
852 p_validation_level,
853 p_commit,
854 x_return_status,
855 x_msg_count,
856 x_msg_data,
857 l_contract_rec
858 );
859 EXCEPTION
860 WHEN FND_API.G_EXC_ERROR THEN
861 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
862 (
863 l_api_name,
864 G_PKG_NAME,
865 'FND_API.G_RET_STS_ERROR',
866 x_msg_count,
867 x_msg_data,
868 '_Pvt'
869 );
870 APP_EXCEPTION.RAISE_EXCEPTION;
871 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
872 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
873 (
877 x_msg_count,
874 l_api_name,
875 G_PKG_NAME,
876 'FND_API.G_RET_STS_UNEXP_ERROR',
878 x_msg_data,
879 '_Pvt'
880 );
881 APP_EXCEPTION.RAISE_EXCEPTION;
882 -- COMMENTED OUT 17-SEP-98 JSU: Forms cannot handle this style
883 -- of messaging
884 -- WHEN OTHERS THEN
885 -- x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
886 -- (
887 -- l_api_name,
888 -- G_PKG_NAME,
889 -- 'OTHERS',
890 -- x_msg_count,
891 -- x_msg_data,
892 -- '_Pvt'
893 -- );
894 END validate_contract;
895
896
897 PROCEDURE update_contract
898 (
899 p_api_version IN NUMBER,
900 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
901 p_validation_level IN NUMBER,
902 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
903 x_return_status OUT VARCHAR2,
904 x_msg_count OUT NUMBER,
905 x_msg_data OUT VARCHAR2,
906 p_contract_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
907 p_contract_number IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
911 p_price_list_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
908 p_workflow IN CS_CONTRACTS.WORKFLOW%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
909 p_workflow_process_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
910 p_agreement_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
912 p_currency_code IN CS_CONTRACTS.CURRENCY_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
913 p_conversion_type_code IN CS_CONTRACTS.CONVERSION_TYPE_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
914 p_conversion_rate IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
915 p_conversion_date IN CS_CONTRACTS.CONVERSION_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
916 p_invoicing_rule_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
917 p_accounting_rule_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
918 p_billing_frequency_period IN CS_CONTRACTS.BILLING_FREQUENCY_PERIOD%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
919 p_first_bill_date IN CS_CONTRACTS.FIRST_BILL_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
920 p_next_bill_date IN CS_CONTRACTS.NEXT_BILL_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
921 p_create_sales_order IN CS_CONTRACTS.CREATE_SALES_ORDER%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
922 p_renewal_rule IN CS_CONTRACTS.RENEWAL_RULE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
923 p_termination_rule IN CS_CONTRACTS.TERMINATION_RULE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
924 p_bill_to_site_use_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
925 p_contract_status_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
926 p_contract_type_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
927 p_contract_template_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
928 p_contract_group_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
929 p_customer_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
930 p_duration IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
931 p_period_code IN CS_CONTRACTS.PERIOD_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
932 p_ship_to_site_use_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
933 p_salesperson_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
934 p_ordered_by_contact_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
935 p_source_code IN CS_CONTRACTS.SOURCE_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
936 p_source_reference IN CS_CONTRACTS.SOURCE_REFERENCE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
937 p_terms_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
938 p_po_number IN CS_CONTRACTS.PO_NUMBER%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
939 p_bill_on IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
940 p_tax_handling IN CS_CONTRACTS.TAX_HANDLING%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
941 p_tax_exempt_num IN CS_CONTRACTS.TAX_EXEMPT_NUM%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
942 p_tax_exempt_reason_code IN CS_CONTRACTS.TAX_EXEMPT_REASON_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
943 p_contract_amount IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
944 p_auto_renewal_flag IN CS_CONTRACTS.AUTO_RENEWAL_FLAG%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
945 p_original_end_date IN CS_CONTRACTS.ORIGINAL_END_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
946 p_terminate_reason_code IN CS_CONTRACTS.TERMINATE_REASON_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
947 p_discount_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
948 p_po_required_to_service IN CS_CONTRACTS.PO_REQUIRED_TO_SERVICE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
949 p_pre_payment_required IN CS_CONTRACTS.PRE_PAYMENT_REQUIRED%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
950 p_last_update_date IN CS_CONTRACTS.LAST_UPDATE_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
951 p_last_updated_by IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
952 p_creation_date IN CS_CONTRACTS.CREATION_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
953 p_created_by IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
954 p_last_update_login IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
955 p_start_date_active IN CS_CONTRACTS.START_DATE_ACTIVE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
956 p_end_date_active IN CS_CONTRACTS.END_DATE_ACTIVE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
957 p_attribute1 IN CS_CONTRACTS.ATTRIBUTE1%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
958 p_attribute2 IN CS_CONTRACTS.ATTRIBUTE2%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
962 p_attribute6 IN CS_CONTRACTS.ATTRIBUTE6%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
959 p_attribute3 IN CS_CONTRACTS.ATTRIBUTE3%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
960 p_attribute4 IN CS_CONTRACTS.ATTRIBUTE4%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
961 p_attribute5 IN CS_CONTRACTS.ATTRIBUTE5%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
963 p_attribute7 IN CS_CONTRACTS.ATTRIBUTE7%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
964 p_attribute8 IN CS_CONTRACTS.ATTRIBUTE8%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
965 p_attribute9 IN CS_CONTRACTS.ATTRIBUTE9%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
966 p_attribute10 IN CS_CONTRACTS.ATTRIBUTE10%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
967 p_attribute11 IN CS_CONTRACTS.ATTRIBUTE11%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
968 p_attribute12 IN CS_CONTRACTS.ATTRIBUTE12%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
969 p_attribute13 IN CS_CONTRACTS.ATTRIBUTE13%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
970 p_attribute14 IN CS_CONTRACTS.ATTRIBUTE14%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
971 p_attribute15 IN CS_CONTRACTS.ATTRIBUTE15%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
972 p_context IN CS_CONTRACTS.CONTEXT%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
973 p_object_version_number IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
974 x_object_version_number OUT NUMBER) IS
975 l_api_name CONSTANT VARCHAR2(30) := 'update_contract';
976 l_api_version CONSTANT NUMBER := 1;
977 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
978 l_contract_rec CS_CONTRACT_PVT.Contract_Val_Rec_Type;
979 p_old_status_id CS_CONTRACTS.CONTRACT_STATUS_ID%TYPE;
980 p_new_status_id CS_CONTRACTS.CONTRACT_STATUS_ID%TYPE;
981 p_inv_flag CS_CONTRACT_STATUSES.ELIGIBLE_FOR_INVOICING%TYPE;
982 BEGIN
983 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
984 G_PKG_NAME,
985 l_api_version,
986 p_api_version,
987 p_init_msg_list,
988 '_Pvt',
989 x_return_status);
990 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
995 --- Get the old status id
992 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
993 RAISE FND_API.G_EXC_ERROR;
994 END IF;
996
997 SELECT contract_status_id
998 INTO p_old_status_id
999 FROM CS_CONTRACTS
1000 WHERE contract_id = p_contract_id;
1001
1002 l_contract_rec.CONTRACT_ID := p_contract_id;
1003 l_contract_rec.CONTRACT_NUMBER := p_contract_number;
1004 l_contract_rec.WORKFLOW := p_workflow;
1005 l_contract_rec.WORKFLOW_PROCESS_ID := p_workflow_process_id;
1006 l_contract_rec.AGREEMENT_ID := p_agreement_id;
1007 l_contract_rec.PRICE_LIST_ID := p_price_list_id;
1008 l_contract_rec.CURRENCY_CODE := p_currency_code;
1009 l_contract_rec.CONVERSION_TYPE_CODE := p_conversion_type_code;
1010 l_contract_rec.CONVERSION_RATE := p_conversion_rate;
1011 l_contract_rec.CONVERSION_DATE := p_conversion_date;
1012 l_contract_rec.INVOICING_RULE_ID := p_invoicing_rule_id;
1013 l_contract_rec.ACCOUNTING_RULE_ID := p_accounting_rule_id;
1014 l_contract_rec.BILLING_FREQUENCY_PERIOD := p_billing_frequency_period;
1015 l_contract_rec.FIRST_BILL_DATE := p_first_bill_date;
1016 l_contract_rec.NEXT_BILL_DATE := p_next_bill_date;
1017 l_contract_rec.CREATE_SALES_ORDER := p_create_sales_order;
1018 l_contract_rec.RENEWAL_RULE := p_renewal_rule;
1019 l_contract_rec.TERMINATION_RULE := p_termination_rule;
1020 l_contract_rec.BILL_TO_SITE_USE_ID := p_bill_to_site_use_id;
1021 l_contract_rec.CONTRACT_STATUS_ID := p_contract_status_id;
1022 l_contract_rec.CONTRACT_TYPE_ID := p_contract_type_id;
1023 l_contract_rec.CONTRACT_TEMPLATE_ID := p_contract_template_id;
1024 l_contract_rec.CONTRACT_GROUP_ID := p_contract_group_id;
1025 l_contract_rec.CUSTOMER_ID := p_customer_id;
1026 l_contract_rec.DURATION := p_duration;
1027 l_contract_rec.PERIOD_CODE := p_period_code;
1028 l_contract_rec.SHIP_TO_SITE_USE_ID := p_ship_to_site_use_id;
1029 l_contract_rec.SALESPERSON_ID := p_salesperson_id;
1030 l_contract_rec.ORDERED_BY_CONTACT_ID := p_ordered_by_contact_id;
1031 l_contract_rec.SOURCE_CODE := p_source_code;
1032 l_contract_rec.SOURCE_REFERENCE := p_source_reference;
1033 l_contract_rec.TERMS_ID := p_terms_id;
1034 l_contract_rec.PO_NUMBER := p_po_number;
1035 l_contract_rec.BILL_ON := p_bill_on;
1036 l_contract_rec.TAX_HANDLING := p_tax_handling;
1037 l_contract_rec.TAX_EXEMPT_NUM := p_tax_exempt_num;
1038 l_contract_rec.TAX_EXEMPT_REASON_CODE := p_tax_exempt_reason_code;
1039 l_contract_rec.CONTRACT_AMOUNT := p_contract_amount;
1040 l_contract_rec.AUTO_RENEWAL_FLAG := p_auto_renewal_flag;
1041 l_contract_rec.ORIGINAL_END_DATE := p_original_end_date;
1042 l_contract_rec.TERMINATE_REASON_CODE := p_terminate_reason_code;
1043 l_contract_rec.DISCOUNT_ID := p_discount_id;
1044 l_contract_rec.PO_REQUIRED_TO_SERVICE := p_po_required_to_service;
1045 l_contract_rec.PRE_PAYMENT_REQUIRED := p_pre_payment_required;
1046 l_contract_rec.LAST_UPDATE_DATE := p_last_update_date;
1047 l_contract_rec.LAST_UPDATED_BY := p_last_updated_by;
1048 l_contract_rec.CREATION_DATE := p_creation_date;
1049 l_contract_rec.CREATED_BY := p_created_by;
1050 l_contract_rec.LAST_UPDATE_LOGIN := p_last_update_login;
1051 l_contract_rec.START_DATE_ACTIVE := p_start_date_active;
1052 l_contract_rec.END_DATE_ACTIVE := p_end_date_active;
1053 l_contract_rec.ATTRIBUTE1 := p_attribute1;
1054 l_contract_rec.ATTRIBUTE2 := p_attribute2;
1055 l_contract_rec.ATTRIBUTE3 := p_attribute3;
1056 l_contract_rec.ATTRIBUTE4 := p_attribute4;
1057 l_contract_rec.ATTRIBUTE5 := p_attribute5;
1058 l_contract_rec.ATTRIBUTE6 := p_attribute6;
1059 l_contract_rec.ATTRIBUTE7 := p_attribute7;
1060 l_contract_rec.ATTRIBUTE8 := p_attribute8;
1061 l_contract_rec.ATTRIBUTE9 := p_attribute9;
1062 l_contract_rec.ATTRIBUTE10 := p_attribute10;
1063 l_contract_rec.ATTRIBUTE11 := p_attribute11;
1064 l_contract_rec.ATTRIBUTE12 := p_attribute12;
1065 l_contract_rec.ATTRIBUTE13 := p_attribute13;
1066 l_contract_rec.ATTRIBUTE14 := p_attribute14;
1067 l_contract_rec.ATTRIBUTE15 := p_attribute15;
1068 l_contract_rec.CONTEXT := p_context;
1069 l_contract_rec.OBJECT_VERSION_NUMBER := p_object_version_number;
1070 CS_CONTRACT_PVT.update_row(
1071 p_api_version,
1072 p_init_msg_list,
1073 p_validation_level,
1074 p_commit,
1075 x_return_status,
1076 x_msg_count,
1077 x_msg_data,
1078 l_contract_rec,
1079 x_object_version_number
1080 );
1081
1082 --- Get the new status id after the update
1083
1084 SELECT contract_status_id
1085 INTO p_new_status_id
1086 FROM CS_CONTRACTS
1087 WHERE contract_id = l_contract_rec.contract_id;
1088
1089 SELECT eligible_for_invoicing
1090 INTO p_inv_flag
1091 FROM CS_CONTRACT_STATUSES
1092 WHERE contract_status_id = p_new_status_id;
1093
1094 --- Call update service if status changes
1095 IF l_contract_rec.contract_status_id <> TAPI_DEV_KIT.G_MISS_NUM and
1096 p_old_status_id <> p_new_status_id
1097 -- COMMENTED OUT 22-FEB-1999 AS PER REQUESTED BY SKARUPPASAMY -- JSU
1098 -- and p_inv_flag = 'Y'
1099 THEN
1100 for l_servicerec in (
1101 Select cp_service_id from cs_cp_services
1102 where contract_id = l_contract_rec.contract_id
1103 and contract_line_status_id = p_old_status_id)
1104 LOOP
1105 --- Call update service
1106 CS_SERVICES_PVT.Update_Service
1107 (
1108 p_api_version => 1.0,
1109 p_init_msg_list => TAPI_DEV_KIT.G_FALSE,
1110 p_validation_level => 100,
1111 p_commit => TAPI_DEV_KIT.G_FALSE,
1112 x_return_status => x_return_status,
1113 x_msg_count => x_msg_count,
1114 x_msg_data => x_msg_data,
1115 p_cp_service_id => l_servicerec.cp_service_id,
1116 p_contract_line_status_id => p_new_status_id,
1117 p_last_update_date => sysdate,
1118 p_last_updated_by => FND_GLOBAL.user_id
1119 );
1120 END LOOP;
1121 END IF;
1122
1123 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
1124 EXCEPTION
1125 WHEN FND_API.G_EXC_ERROR THEN
1126 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1127 (
1128 l_api_name,
1129 G_PKG_NAME,
1130 'FND_API.G_RET_STS_ERROR',
1131 x_msg_count,
1132 x_msg_data,
1133 '_Pvt'
1134 );
1135 APP_EXCEPTION.RAISE_EXCEPTION;
1136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1137 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1138 (
1139 l_api_name,
1140 G_PKG_NAME,
1141 'FND_API.G_RET_STS_UNEXP_ERROR',
1142 x_msg_count,
1143 x_msg_data,
1144 '_Pvt'
1145 );
1146 APP_EXCEPTION.RAISE_EXCEPTION;
1147 WHEN TAPI_DEV_KIT.G_EXC_DUP_VAL_ON_INDEX THEN
1148 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1149 (
1150 l_api_name,
1151 G_PKG_NAME,
1152 'TAPI_DEV_KIT.G_RET_STS_DUP_VAL_ON_INDEX',
1153 x_msg_count,
1154 x_msg_data,
1155 '_Pvt'
1156 );
1157 APP_EXCEPTION.RAISE_EXCEPTION;
1158 END Update_Contract;
1159
1160 BEGIN
1161 null;
1162 END cs_contracts_pub;