[Home] [Help]
PACKAGE BODY: APPS.DPP_SLA_CLAIM_EXTRACT_PUB
Source
1 PACKAGE BODY DPP_SLA_CLAIM_EXTRACT_PUB as
2 /* $Header: dppclexb.pls 120.6.12010000.2 2008/11/11 11:24:38 rvkondur ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- DPP_SLA_CLAIM_EXTRACT_PUB
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'DPP_SLA_CLAIM_EXTRACT_PUB';
16 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppsce.pls';
17
18 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
19
20 ---------------------------------------------------------------------
21 -- PROCEDURE
22 -- create_sla_extract
23 --
24 -- PURPOSE
25 -- This procedure creates
26 --
27 -- PARAMETERS
28 -- p_claim_line_tbl
29 -- x_return_status
30 --
31 -- NOTES
32 ---------------------------------------------------------------------
33 PROCEDURE Create_SLA_Extract(
34 p_api_version_number IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_claim_id IN ozf_claims.claim_id%TYPE,
42 p_claim_line_tbl IN claim_line_tbl_type,
43 p_userid IN NUMBER
44 )
45 IS
46 L_API_NAME CONSTANT VARCHAR2(30) := 'DPP_SLA_CLAIM_EXTRACT_PUB';
47 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
48 l_pvt_claim_rec OZF_ClAIM_PVT.claim_rec_type;
49 l_x_pvt_claim_rec OZF_ClAIM_PVT.claim_rec_type;
50 l_claim_line_tbl claim_line_tbl_type := p_claim_line_tbl;
51 --l_claim_line_rec claim_line_rec_type ;
52 l_error_index NUMBER;
53 l_transaction_header_id DPP_TRANSACTION_HEADERS_ALL.transaction_header_id%TYPE;
54 l_claim_type DPP_TRANSACTION_CLAIMS_ALL.CLAIM_TYPE%TYPE;
55 l_transaction_line_id DPP_TRANSACTION_LINES_ALL.transaction_line_id%TYPE;
56
57 l_sql_statement VARCHAR2(200);
58 l_sla_line_tbl_type sla_line_tbl_type;
59 l_processed_flag DPP_XLA_HEADERS.PROCESSED_FLAG%TYPE := 'N';
60 BEGIN
61 -- Standard Start of API savepoint
62 SAVEPOINT CREATE_SLA_Extract_PUB;
63 -- Standard call to check for call compatibility.
64 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
65 p_api_version_number,
66 l_api_name,
67 G_PKG_NAME)
68 THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71 -- Initialize message list if p_init_msg_list is set to TRUE.
72 IF FND_API.to_Boolean( p_init_msg_list )
73 THEN
74 FND_MSG_PUB.initialize;
75 END IF;
76 -- Debug Message
77 IF g_debug THEN
78 OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' pub start');
79 END IF;
80 -- Initialize API return status to SUCCESS
81 x_return_status := FND_API.G_RET_STS_SUCCESS;
82 --
83 -- API body
84 --
85 IF p_claim_line_tbl.count > 0 THEN
86
87 BEGIN
88 SELECT TRANSACTION_HEADER_ID,
89 CLAIM_TYPE
90 INTO l_transaction_header_id,
91 l_claim_type
92 FROM DPP_TRANSACTION_CLAIMS_ALL
93 WHERE CLAIM_ID=p_claim_id;
94
95 EXCEPTION
96
97 WHEN NO_DATA_FOUND THEN
98 DPP_UTILITY_PVT.debug_message('This is not a valid claim ');
99 FND_MESSAGE.set_name('DPP', 'DPP_INVALID_CLAIM');
100 FND_MESSAGE.set_token('CLAIM_ID', p_claim_id);
101 FND_MSG_PUB.add;
102 RAISE FND_API.G_EXC_ERROR;
103 END;
104
105 BEGIN
106 INSERT INTO DPP_XLA_HEADERS
107 ( TRANSACTION_HEADER_ID,
108 PP_TRANSACTION_TYPE,
109 BASE_TRANSACTION_HEADER_ID,
110 PROCESSED_FLAG,
111 CREATION_DATE,
112 CREATED_BY,
113 LAST_UPDATE_DATE,
114 LAST_UPDATED_BY,
115 LAST_UPDATE_LOGIN
116 )
117 VALUES
118 (
119 l_transaction_header_id,
120 l_claim_type,
121 p_claim_id,
122 l_processed_flag,
123 SYSDATE,
124 p_userid,
125 SYSDATE,
126 p_userid,
127 p_userid
128 );
129 EXCEPTION
130 WHEN DUP_VAL_ON_INDEX THEN
131 FND_MESSAGE.set_name('DPP', 'DPP_DUPLICATE_HDR_EXTRACT');
132 FND_MESSAGE.set_token('TXN_HDR_ID', l_transaction_header_id);
133 FND_MESSAGE.set_token('BASE_TXN_HDR_ID', p_claim_id);
134 FND_MSG_PUB.add;
135 RAISE FND_API.G_EXC_ERROR;
136 END;
137
138 l_sla_line_tbl_type.delete;
139 FOR i IN p_claim_line_tbl.FIRST..p_claim_line_tbl.LAST LOOP
140
141 l_transaction_line_id:=NULL;
142
143 BEGIN
144 IF l_claim_type='SUPP_DSTR_CL' OR l_claim_type='SUPP_DSTR_INC_CL' THEN --ANBBALAS: Included the condition l_claim_type='SUPP_DSTR_INC_CL' for Price Increase
145 SELECT transaction_line_id
146 INTO l_transaction_line_id
147 FROM DPP_TRANSACTION_LINES_ALL
148 WHERE transaction_header_id=l_transaction_header_id
149 AND inventory_item_id=p_claim_line_tbl(i).item_id
150 AND SUPP_DIST_CLAIM_ID=p_claim_line_tbl(i).claim_id;
151 ELSIF l_claim_type='SUPP_CUST_CL' THEN
152 SELECT CUSTOMER_INV_LINE_ID
153 INTO l_transaction_line_id
154 FROM DPP_CUSTOMER_CLAIMS_ALL
155 WHERE transaction_header_id=l_transaction_header_id
156 AND inventory_item_id=p_claim_line_tbl(i).item_id
157 AND SUPP_CUST_CLAIM_ID=p_claim_line_tbl(i).claim_id
158 AND CUST_ACCOUNT_ID=p_claim_line_tbl(i).dpp_cust_account_id;
159 ELSIF l_claim_type='CUST_CL' THEN
160 SELECT CUSTOMER_INV_LINE_ID
161 INTO l_transaction_line_id
162 FROM DPP_CUSTOMER_CLAIMS_ALL
163 WHERE transaction_header_id=l_transaction_header_id
164 AND inventory_item_id=p_claim_line_tbl(i).item_id
165 AND CUSTOMER_CLAIM_ID=p_claim_line_tbl(i).claim_id;
166 END IF;
167 EXCEPTION
168
169 WHEN NO_DATA_FOUND THEN
170 DPP_UTILITY_PVT.debug_message('This claim line does not exist in Price Protection ');
171 FND_MESSAGE.set_name('DPP', 'DPP_INVALID_CLAIM_LINE');
172 FND_MESSAGE.set_token('CLAIM_ID',p_claim_line_tbl(i).claim_id);
173 FND_MESSAGE.set_token('ITEM_ID',p_claim_line_tbl(i).item_id);
174 FND_MSG_PUB.add;
175 RAISE FND_API.G_EXC_ERROR;
176 END;
177
178
179 l_sla_line_tbl_type(i).transaction_header_id:=l_transaction_header_id;
180 l_sla_line_tbl_type(i).transaction_line_id:=l_transaction_line_id;
181 l_sla_line_tbl_type(i).base_transaction_header_id:=p_claim_line_tbl(i).claim_id;
182 l_sla_line_tbl_type(i).base_transaction_line_id:=p_claim_line_tbl(i).claim_line_id;
183 l_sla_line_tbl_type(i).transaction_sub_type:=NULL;
184 l_sla_line_tbl_type(i).CREATION_DATE:=SYSDATE;
185 l_sla_line_tbl_type(i).CREATED_BY:=p_userid;
186 l_sla_line_tbl_type(i).LAST_UPDATE_DATE:=SYSDATE;
187 l_sla_line_tbl_type(i).LAST_UPDATED_BY:=p_userid;
188 l_sla_line_tbl_type(i).LAST_UPDATE_LOGIN:=p_userid;
189
190
191 END LOOP;
192
193 IF l_sla_line_tbl_type.count() >0 THEN
194
195 FOR i in 1..l_sla_line_tbl_type.COUNT LOOP
196 BEGIN
197 INSERT INTO DPP_XLA_LINES
198 (TRANSACTION_HEADER_ID,TRANSACTION_LINE_ID,
199 BASE_TRANSACTION_HEADER_ID,
200 BASE_TRANSACTION_LINE_ID,TRANSACTION_SUB_TYPE,
201 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,
202 LAST_UPDATED_BY)
203 VALUES (l_sla_line_tbl_type(i).transaction_header_id,
204 l_sla_line_tbl_type(i).transaction_line_id,
205 l_sla_line_tbl_type(i).base_transaction_header_id,
206 l_sla_line_tbl_type(i).base_transaction_line_id,
207 l_sla_line_tbl_type(i).transaction_sub_type,
208 l_sla_line_tbl_type(i).creation_date,
209 l_sla_line_tbl_type(i).created_by,
210 l_sla_line_tbl_type(i).last_update_date,
211 l_sla_line_tbl_type(i).last_updated_by
212 );
213
214 EXCEPTION
215 WHEN DUP_VAL_ON_INDEX THEN
216 FND_MESSAGE.set_name('DPP', 'DPP_DUPLICATE_LINE_EXTRACT');
217 FND_MESSAGE.set_token('TXN_HDR_ID', l_transaction_header_id);
218 FND_MESSAGE.set_token('BASE_TXN_HDR_ID', p_claim_id);
219 FND_MESSAGE.set_token('BASE_TXN_LINE_ID', l_sla_line_tbl_type(i).base_transaction_line_id);
220 FND_MSG_PUB.add;
221 RAISE FND_API.G_EXC_ERROR;
222 END;
223 END LOOP;
224 END IF;
225 END IF;
226
227 IF FND_API.to_Boolean( p_commit )
228 THEN
229 COMMIT WORK;
230 END IF;
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232 EXCEPTION
233 WHEN OZF_Utility_PVT.resource_locked THEN
234 ROLLBACK TO CREATE_SLA_Extract_PUB;
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 FND_MSG_PUB.Count_And_Get (
237 p_encoded => FND_API.G_FALSE,
238 p_count => x_msg_count,
239 p_data => x_msg_data
240 );
241 WHEN FND_API.G_EXC_ERROR THEN
242 ROLLBACK TO CREATE_SLA_Extract_PUB;
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 -- Standard call to get message count and if count=1, get the message
245 FND_MSG_PUB.Count_And_Get (
246 p_encoded => FND_API.G_FALSE,
247 p_count => x_msg_count,
248 p_data => x_msg_data
249 );
250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
251 ROLLBACK TO CREATE_SLA_Extract_PUB;
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
253 -- Standard call to get message count and if count=1, get the message
254 FND_MSG_PUB.Count_And_Get (
255 p_encoded => FND_API.G_FALSE,
256 p_count => x_msg_count,
257 p_data => x_msg_data
258 );
259 WHEN OTHERS THEN
260 ROLLBACK TO CREATE_SLA_Extract_PUB;
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
263 THEN
264 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
265 END IF;
266 -- Standard call to get message count and if count=1, get the message
267 FND_MSG_PUB.Count_And_Get (
268 p_encoded => FND_API.G_FALSE,
269 p_count => x_msg_count,
270 p_data => x_msg_data
271 );
272 End Create_SLA_extract;
273 END DPP_SLA_CLAIM_EXTRACT_PUB;