1 PACKAGE BODY IGS_FI_VAL_FTG AS
2 /* $Header: IGSFI35B.pls 115.3 2002/11/29 00:22:30 nsidana ship $ */
3 --
4 -- Ensure fee trigger group can be created.
5 FUNCTION finp_val_ftg_ins(
6 p_fee_type IN VARCHAR2 ,
7 p_message_name OUT NOCOPY VARCHAR2)
8 RETURN BOOLEAN AS
9 gv_other_detail VARCHAR2(255);
10 BEGIN -- finp_val_ftg_ins
11 -- Validate IGS_FI_FEE_TRG_GRP IGS_FI_FEE_TYPE.s_fee_trigger_cat = COMPOSITE
12 -- otherwise fee trigger groups cannot be defined.
13 DECLARE
14 CURSOR c_ft(
15 cp_fee_type IGS_FI_FEE_TYPE.fee_type%TYPE) IS
16 SELECT s_fee_trigger_cat
17 FROM IGS_FI_FEE_TYPE
18 WHERE FEE_TYPE = cp_fee_type;
19 v_ft_rec c_ft%ROWTYPE;
20 cst_composite CONSTANT VARCHAR2(10) := 'COMPOSITE';
21 BEGIN
22 -- Set the default message number
23 p_message_name := null;
24 -- Check parameters
25 IF p_fee_type IS NULL THEN
26 RETURN TRUE;
27 END IF;
28 -- Get the system fee trigger category of the IGS_FI_FEE_TYPE.
29 OPEN c_ft (p_fee_type);
30 FETCH c_ft INTO v_ft_rec;
31 IF c_ft%NOTFOUND THEN
32 CLOSE c_ft;
33 RETURN TRUE;
34 END IF;
35 CLOSE c_ft;
36 IF v_ft_rec.s_fee_trigger_cat <> cst_composite THEN
37 p_message_name:= 'IGS_FI_FEETRG_GRPS_COMPOSITE';
38 RETURN FALSE;
39 END IF;
40 -- Return the default value
41 RETURN TRUE;
42 END;
43 END finp_val_ftg_ins;
44 --
45 -- Validate logical delete of the fee trigger group
46 FUNCTION finp_val_ftg_lgl_del(
47 p_fee_cat IN IGS_FI_FEE_TRG_GRP.FEE_CAT%TYPE ,
48 p_fee_cal_type IN IGS_FI_FEE_TRG_GRP.fee_cal_type%TYPE ,
49 p_fee_ci_sequence_number IN NUMBER ,
50 p_fee_type IN IGS_FI_FEE_TRG_GRP.FEE_TYPE%TYPE ,
51 p_fee_trigger_group_num IN NUMBER ,
52 p_message_name OUT NOCOPY VARCHAR2)
53 RETURN BOOLEAN AS
54 gv_other_detail VARCHAR2(255);
55 BEGIN -- finp_val_ftg_lgl_del
56 -- Validate that logical deletion of a IGS_FI_FEE_TRG_GRP record does
57 -- not orphan related IGS_PS_COURSE, IGS_PS_UNIT or IGS_PS_UNIT set fee triggers.
58 DECLARE
59 CURSOR c_cft IS
60 SELECT 'x'
61 FROM IGS_PS_FEE_TRG cft
62 WHERE cft.FEE_CAT = p_fee_cat AND
63 cft.fee_cal_type = p_fee_cal_type AND
64 cft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
65 cft.FEE_TYPE = p_fee_type AND
66 NVL(cft.fee_trigger_group_number,0) = p_fee_trigger_group_num AND
67 cft.logical_delete_dt IS NULL;
68 CURSOR c_uft IS
69 SELECT 'x'
70 FROM IGS_FI_UNIT_FEE_TRG uft
71 WHERE uft.FEE_CAT = p_fee_cat AND
72 uft.fee_cal_type = p_fee_cal_type AND
73 uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
74 uft.FEE_TYPE = p_fee_type AND
75 NVL(uft.fee_trigger_group_number,0) = p_fee_trigger_group_num AND
76 uft.logical_delete_dt IS NULL;
77 CURSOR c_usft IS
78 SELECT 'x'
79 FROM IGS_EN_UNITSETFEETRG usft
80 WHERE usft.FEE_CAT = p_fee_cat AND
81 usft.fee_cal_type = p_fee_cal_type AND
82 usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
83 usft.FEE_TYPE = p_fee_type AND
84 NVL(usft.fee_trigger_group_number,0) = p_fee_trigger_group_num AND
85 usft.logical_delete_dt IS NULL;
86 v_check CHAR;
87 BEGIN
88 --- Set the default message number
89 p_message_name := null;
90 IF p_fee_cat IS NULL OR
91 p_fee_cal_type IS NULL OR
92 p_fee_ci_sequence_number IS NULL OR
93 p_fee_type IS NULL OR
94 p_fee_trigger_group_num IS NULL THEN
95 RETURN TRUE;
96 END IF;
97 OPEN c_cft;
98 FETCH c_cft INTO v_check;
99 IF (c_cft%FOUND) THEN
100 CLOSE c_cft;
101 p_message_name:= 'IGS_FI_FEETRGGRP_PRG_FEETRG';
102 RETURN FALSE;
103 END IF;
104 CLOSE c_cft;
105 OPEN c_uft;
106 FETCH c_uft INTO v_check;
107 IF (c_uft%FOUND) THEN
108 CLOSE c_uft;
109 p_message_name:= 'IGS_FI_FEETRGGRP_UNT_FEETRG';
110 RETURN FALSE;
111 END IF;
112 CLOSE c_uft;
113 OPEN c_usft;
114 FETCH c_usft INTO v_check;
115 IF (c_usft%FOUND) THEN
116 CLOSE c_usft;
117 p_message_name:= 'IGS_FI_FEETRGGRP_UNIT_SERTRG';
118 RETURN FALSE;
119 END IF;
120 CLOSE c_usft;
121 RETURN TRUE;
122 END;
123 END finp_val_ftg_lgl_del;
124 END IGS_FI_VAL_FTG;
[Home] [Help]