1 PACKAGE BODY OZF_CLAIM_DEF_RULE_PVT as
2 /* $Header: ozfvcdrb.pls 120.4 2010/08/06 08:22:00 kpatro ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_CLAIM_DEF_RULE_PVT';
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvcdrs.pls';
5
6 PROCEDURE get_clam_def_rule(
7 p_claim_rec IN OZF_Claim_PVT.claim_rec_type,
8 x_clam_def_rec_type OUT NOCOPY clam_def_rec_type,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_msg_count OUT NOCOPY NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2
12 )
13
14 is
15 L_API_NAME CONSTANT VARCHAR2(30) := 'get_clam_def_rule';
16 l_claim_type_id number;
17 l_reason_code_id number;
18 l_custom_setup_id number;
19 l_count_custom_setup number;
20
21 -- CURSOR c_clam_rule ( cv_claim_class VARCHAR2, cv_source_object VARCHAR2, cv_custsetup_id NUMBER) is
22 CURSOR c_clam_rule ( cv_claim_class VARCHAR2, cv_source_object VARCHAR2) is
23 SELECT claim_type_id, reason_code_id , custom_setup_id
24 FROM OZF_CLAIM_DEF_RULES
25 WHERE CLAIM_CLASS = cv_claim_class
26 AND ( ( SOURCE_OBJECT_CLASS is null AND cv_source_object is null )
27 OR (SOURCE_OBJECT_CLASS = cv_source_object ) )
28 AND ENABLED_FLAG = 'Y';
29
30 CURSOR ozf_def_claim_id is
31 select claim_type_id
32 from ozf_sys_parameters;
33
34 CURSOR ozf_def_reason_id is
35 select reason_code_id
36 from ozf_sys_parameters;
37
38 CURSOR csr_seed_custom_setup(cv_custom_setup_id IN NUMBER) IS
39 SELECT custom_setup_id
40 FROM ams_custom_setups_vl
41 WHERE application_id = 682
42 AND enabled_flag = 'Y'
43 AND custom_setup_id = cv_custom_setup_id;
44
45 CURSOR csr_count_custom_setup(cv_claim_class IN VARCHAR2) IS
46 SELECT COUNT(custom_setup_id)
47 FROM ams_custom_setups_vl
48 WHERE application_id = 682
49 AND enabled_flag = 'Y'
50 AND object_type = 'CLAM'
51 AND activity_type_code = cv_claim_class;
52
53 CURSOR csr_user_custom_setup(cv_claim_class IN VARCHAR2) IS
54 SELECT custom_setup_id
55 FROM ams_custom_setups_vl
56 WHERE application_id = 682
57 AND enabled_flag = 'Y'
58 AND object_type = 'CLAM'
59 AND activity_type_code = cv_claim_class;
60
61
62
63 BEGIN
64
65 -- Initialize API return status to SUCCESS
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68 FOR l_claim_rule_rec IN c_clam_rule(p_claim_rec.claim_class,
69 p_claim_rec.source_object_class)
70
71
72 LOOP
73 l_claim_type_id := l_claim_rule_rec.claim_type_id;
74 l_reason_code_id := l_claim_rule_rec.reason_code_id;
75 l_custom_setup_id := l_claim_rule_rec.custom_setup_id;
76 END LOOP;
77
78 IF ( l_claim_type_id is null ) THEN
79 OPEN ozf_def_claim_id;
80 FETCH ozf_def_claim_id INTO l_claim_type_id;
81 CLOSE ozf_def_claim_id;
82 END IF;
83
84 IF ( l_reason_code_id is null ) THEN
85 OPEN ozf_def_reason_id;
86 FETCH ozf_def_reason_id INTO l_reason_code_id;
87 CLOSE ozf_def_reason_id;
88 END IF;
89
90 x_clam_def_rec_type.claim_type_id := l_claim_type_id;
91 x_clam_def_rec_type.reason_code_id := l_reason_code_id;
92 x_clam_def_rec_type.claim_class := p_claim_rec.claim_class;
93 x_clam_def_rec_type.source_object_class := p_claim_rec.source_object_class;
94 x_clam_def_rec_type.custom_setup_id := l_custom_setup_id;
95 -- 12.1 SDR Enhancement: Added the custom setup for SDR claim
96 IF ( l_custom_setup_id is null ) THEN
97 IF ( p_claim_rec.claim_class = 'CLAIM' AND p_claim_rec.source_object_class = 'SD_INTERNAL') THEN
98 OPEN csr_seed_custom_setup(2002);
99 FETCH csr_seed_custom_setup INTO l_custom_setup_id;
100 CLOSE csr_seed_custom_setup;
101 ELSIF (p_claim_rec.source_object_class = 'SD_SUPPLIER') THEN
102 OPEN csr_seed_custom_setup(2003);
103 FETCH csr_seed_custom_setup INTO l_custom_setup_id;
104 CLOSE csr_seed_custom_setup;
105 ELSIF( p_claim_rec.claim_class = 'CLAIM' ) THEN
106 OPEN csr_seed_custom_setup(2001);
107 FETCH csr_seed_custom_setup INTO l_custom_setup_id;
108 CLOSE csr_seed_custom_setup;
109 ELSIF ( p_claim_rec.claim_class = 'OVERPAYMENT' ) THEN
110 OPEN csr_seed_custom_setup(190);
111 FETCH csr_seed_custom_setup INTO l_custom_setup_id;
112 CLOSE csr_seed_custom_setup;
113 ELSIF ( p_claim_rec.claim_class = 'DEDUCTION' ) THEN
114 OPEN csr_seed_custom_setup(160);
115 FETCH csr_seed_custom_setup INTO l_custom_setup_id;
116 CLOSE csr_seed_custom_setup;
117 ELSIF ( p_claim_rec.claim_class = 'CHARGE' ) THEN
118 OPEN csr_seed_custom_setup(210);
119 FETCH csr_seed_custom_setup INTO l_custom_setup_id;
120 CLOSE csr_seed_custom_setup;
121 ELSIF ( p_claim_rec.claim_class = 'GROUP' ) THEN
122 OPEN csr_seed_custom_setup(150);
123 FETCH csr_seed_custom_setup INTO l_custom_setup_id;
124 CLOSE csr_seed_custom_setup;
125 ELSE
126 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
127 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CLASS_ERROR');
128 FND_MSG_PUB.add;
129 END IF;
130 RAISE FND_API.g_exc_error;
131 END IF;
132
133 IF l_custom_setup_id IS NULL THEN
134 OPEN csr_count_custom_setup(p_claim_rec.claim_class);
135 FETCH csr_count_custom_setup INTO l_count_custom_setup;
136 CLOSE csr_count_custom_setup;
137
138 IF l_count_custom_setup = 1 THEN
139 OPEN csr_user_custom_setup(p_claim_rec.claim_class);
140 FETCH csr_user_custom_setup INTO l_custom_setup_id;
141 CLOSE csr_user_custom_setup;
142 ELSE
143 IF l_count_custom_setup > 1 THEN
144 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
145 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CUSTSET_UP_NO_EXT');
146 FND_MSG_PUB.add;
147 END IF;
148 ELSE
149 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
150 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CUSTSET_UP_NO_UK');
151 FND_MSG_PUB.add;
152 END IF;
153 END IF;
154 RAISE FND_API.g_exc_error;
155 END IF;
156 END IF;
157 x_clam_def_rec_type.custom_setup_id := l_custom_setup_id;
158 END IF;
159
160 EXCEPTION
161
162 WHEN FND_API.G_EXC_ERROR THEN
163 x_return_status := FND_API.G_RET_STS_ERROR;
164 -- Standard call to get message count and if count=1, get the message
165 FND_MSG_PUB.Count_And_Get (
166 p_encoded => FND_API.G_FALSE,
167 p_count => x_msg_count,
168 p_data => x_msg_data
169 );
170
171 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173 -- Standard call to get message count and if count=1, get the message
174 FND_MSG_PUB.Count_And_Get (
175 p_encoded => FND_API.G_FALSE,
176 p_count => x_msg_count,
177 p_data => x_msg_data
178 );
179
180 WHEN OTHERS THEN
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
183 THEN
184 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
185 END IF;
186 -- Standard call to get message count and if count=1, get the message
187 FND_MSG_PUB.Count_And_Get (
188 p_encoded => FND_API.G_FALSE,
189 p_count => x_msg_count,
190 p_data => x_msg_data
191 );
192
193
194 END get_clam_def_rule;
195 END ozf_claim_def_rule_pvt;