1 PACKAGE BODY IGS_GR_VAL_CRDP AS
2 /* $Header: IGSGR07B.pls 115.4 2002/11/29 00:40:39 nsidana ship $ */
3 --
4 -- Warn if ins/upd/del on crdp if after start date of crd.
5 FUNCTION grdp_val_crdp_iud(
6 p_grd_cal_type IN VARCHAR2 ,
7 p_grd_ci_sequence_number IN NUMBER ,
8 p_message_name OUT NOCOPY VARCHAR2 )
9 RETURN BOOLEAN AS
10 BEGIN -- func_module
11 DECLARE
12 v_crd_daiv_exists VARCHAR2(1);
13 CURSOR c_crd_daiv IS
14 SELECT 'x'
15 FROM IGS_GR_CRMN_ROUND crd,
16 IGS_CA_DA_INST_V daiv
17 WHERE crd.grd_cal_type = p_grd_cal_type AND
18 crd.grd_ci_sequence_number = p_grd_ci_sequence_number AND
19 crd.end_dt_alias = daiv.dt_alias AND
20 crd.end_dai_sequence_number = daiv.sequence_number AND
21 crd.grd_cal_type = daiv.cal_type AND
22 crd.grd_ci_sequence_number = daiv.ci_sequence_number AND
23 TRUNC(daiv.alias_val) < TRUNC(SYSDATE);
24 CURSOR c_crd_daiv2 IS
25 SELECT 'x'
26 FROM IGS_GR_CRMN_ROUND crd,
27 IGS_CA_DA_INST_V daiv
28 WHERE crd.grd_cal_type = p_grd_cal_type AND
29 crd.grd_ci_sequence_number = p_grd_ci_sequence_number AND
30 crd.start_dt_alias = daiv.dt_alias AND
31 crd.start_dai_sequence_number = daiv.sequence_number AND
32 crd.grd_cal_type = daiv.cal_type AND
33 crd.grd_ci_sequence_number = daiv.ci_sequence_number AND
34 TRUNC(daiv.alias_val) < TRUNC(SYSDATE);
35 BEGIN
36 -- Set the default message number
37 p_message_name := NULL;
38 --1. Check parameters :
39 IF p_grd_cal_type IS NULL OR
40 p_grd_ci_sequence_number IS NULL THEN
41 RETURN TRUE;
42 END IF;
43 --2. Return an error if IGS_GR_CRMN_ROUND end_dt_alias has a
44 -- value earlier than the current date.
45 OPEN c_crd_daiv;
46 FETCH c_crd_daiv INTO v_crd_daiv_exists;
47 IF c_crd_daiv%FOUND THEN
48 CLOSE c_crd_daiv;
49 p_message_name := 'IGS_GR_CANNOT_UPD_DT_EXPIRED';
50 RETURN FALSE; -- Error
51 END IF;
52 CLOSE c_crd_daiv;
53 --3. Return a warning if IGS_GR_CRMN_ROUND start_dt_alias has a
54 -- value earlier than the current date.
55 OPEN c_crd_daiv2;
56 FETCH c_crd_daiv2 INTO v_crd_daiv_exists;
57 IF c_crd_daiv2%FOUND THEN
58 CLOSE c_crd_daiv2;
59 p_message_name := 'IGS_GR_PROC_ST_DATE_EXPIRED';
60 RETURN TRUE; -- Warning only
61 END IF;
62 CLOSE c_crd_daiv2;
63 -- Return the default value
64 RETURN TRUE;
65 EXCEPTION
66 WHEN OTHERS THEN
67 IF c_crd_daiv%ISOPEN THEN
68 CLOSE c_crd_daiv;
69 END IF;
70 RAISE;
71 END;
72 EXCEPTION
73 WHEN OTHERS THEN
74 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
75 IGS_GE_MSG_STACK.ADD;
76 App_Exception.Raise_Exception;
77 END grdp_val_crdp_iud;
78 END IGS_GR_VAL_CRDP;