[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.9 2011/05/21 00:16:28 hekkiral 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 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_SLA_CLAIM_EXTRACT_PUB.CREATE_SLA_EXTRACT';
61
62 BEGIN
63 -- Standard Start of API savepoint
64 SAVEPOINT CREATE_SLA_Extract_PUB;
65 -- Standard call to check for call compatibility.
66 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
67 p_api_version_number,
68 l_api_name,
69 G_PKG_NAME)
70 THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73 -- Initialize message list if p_init_msg_list is set to TRUE.
74 IF FND_API.to_Boolean( p_init_msg_list )
75 THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78 -- Debug Message
79
80 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Public API: ' || l_api_name || ' pub start');
81
82 -- Initialize API return status to SUCCESS
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84 --
85 -- API body
86 --
87 IF p_claim_line_tbl.count > 0 THEN
88
89 BEGIN
90 SELECT TRANSACTION_HEADER_ID,
91 CLAIM_TYPE
92 INTO l_transaction_header_id,
93 l_claim_type
94 FROM DPP_TRANSACTION_CLAIMS_ALL
95 WHERE CLAIM_ID=p_claim_id;
96
97 EXCEPTION
98
99 WHEN NO_DATA_FOUND THEN
100 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'This is not a valid claim ');
101 FND_MESSAGE.set_name('DPP', 'DPP_INVALID_CLAIM');
102 FND_MESSAGE.set_token('CLAIM_ID', p_claim_id);
103 FND_MSG_PUB.add;
104 RAISE FND_API.G_EXC_ERROR;
105 END;
106
107 BEGIN
108 INSERT INTO DPP_XLA_HEADERS
109 ( TRANSACTION_HEADER_ID,
110 PP_TRANSACTION_TYPE,
111 BASE_TRANSACTION_HEADER_ID,
112 PROCESSED_FLAG,
113 CREATION_DATE,
114 CREATED_BY,
115 LAST_UPDATE_DATE,
116 LAST_UPDATED_BY,
117 LAST_UPDATE_LOGIN
118 )
119 VALUES
120 (
121 l_transaction_header_id,
122 l_claim_type,
123 p_claim_id,
124 l_processed_flag,
125 SYSDATE,
126 p_userid,
127 SYSDATE,
128 p_userid,
129 p_userid
130 );
131 EXCEPTION
132 WHEN DUP_VAL_ON_INDEX THEN
133 FND_MESSAGE.set_name('DPP', 'DPP_DUPLICATE_HDR_EXTRACT');
134 FND_MESSAGE.set_token('TXN_HDR_ID', l_transaction_header_id);
135 FND_MESSAGE.set_token('BASE_TXN_HDR_ID', p_claim_id);
136 FND_MSG_PUB.add;
137 RAISE FND_API.G_EXC_ERROR;
138 END;
139
140 l_sla_line_tbl_type.delete;
141 FOR i IN p_claim_line_tbl.FIRST..p_claim_line_tbl.LAST LOOP
142
143 l_transaction_line_id:=NULL;
144
145 BEGIN
146 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
147 SELECT transaction_line_id
148 INTO l_transaction_line_id
149 FROM DPP_TRANSACTION_LINES_ALL
150 WHERE transaction_header_id=l_transaction_header_id
151 AND inventory_item_id=p_claim_line_tbl(i).item_id
152 AND SUPP_DIST_CLAIM_ID=p_claim_line_tbl(i).claim_id;
153 ELSIF l_claim_type='SUPP_CUST_CL' THEN
154 SELECT CUSTOMER_INV_LINE_ID
155 INTO l_transaction_line_id
156 FROM DPP_CUSTOMER_CLAIMS_ALL
157 WHERE transaction_header_id=l_transaction_header_id
158 AND inventory_item_id=p_claim_line_tbl(i).item_id
159 AND SUPP_CUST_CLAIM_ID=p_claim_line_tbl(i).claim_id
160 AND CUST_ACCOUNT_ID=p_claim_line_tbl(i).dpp_cust_account_id;
161 ELSIF l_claim_type='CUST_CL' THEN
162 SELECT CUSTOMER_INV_LINE_ID
163 INTO l_transaction_line_id
164 FROM DPP_CUSTOMER_CLAIMS_ALL
165 WHERE transaction_header_id=l_transaction_header_id
166 AND inventory_item_id=p_claim_line_tbl(i).item_id
167 AND CUSTOMER_CLAIM_ID=p_claim_line_tbl(i).claim_id;
168 END IF;
169 EXCEPTION
170
171 WHEN NO_DATA_FOUND THEN
172 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'This claim line does not exist in Price Protection ');
173 FND_MESSAGE.set_name('DPP', 'DPP_INVALID_CLAIM_LINE');
174 FND_MESSAGE.set_token('CLAIM_ID',p_claim_line_tbl(i).claim_id);
175 FND_MESSAGE.set_token('ITEM_ID',p_claim_line_tbl(i).item_id);
176 FND_MSG_PUB.add;
177 RAISE FND_API.G_EXC_ERROR;
178 END;
179
180
181 l_sla_line_tbl_type(i).transaction_header_id:=l_transaction_header_id;
182 l_sla_line_tbl_type(i).transaction_line_id:=l_transaction_line_id;
183 l_sla_line_tbl_type(i).base_transaction_header_id:=p_claim_line_tbl(i).claim_id;
184 l_sla_line_tbl_type(i).base_transaction_line_id:=p_claim_line_tbl(i).claim_line_id;
185 l_sla_line_tbl_type(i).transaction_sub_type:=NULL;
186 l_sla_line_tbl_type(i).CREATION_DATE:=SYSDATE;
187 l_sla_line_tbl_type(i).CREATED_BY:=p_userid;
188 l_sla_line_tbl_type(i).LAST_UPDATE_DATE:=SYSDATE;
189 l_sla_line_tbl_type(i).LAST_UPDATED_BY:=p_userid;
190 l_sla_line_tbl_type(i).LAST_UPDATE_LOGIN:=p_userid;
191
192
193 END LOOP;
194
195 IF l_sla_line_tbl_type.count() >0 THEN
196
197 FOR i in 1..l_sla_line_tbl_type.COUNT LOOP
198 BEGIN
199 INSERT INTO DPP_XLA_LINES
200 (TRANSACTION_HEADER_ID,TRANSACTION_LINE_ID,
201 BASE_TRANSACTION_HEADER_ID,
202 BASE_TRANSACTION_LINE_ID,TRANSACTION_SUB_TYPE,
203 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,
204 LAST_UPDATED_BY)
205 VALUES (l_sla_line_tbl_type(i).transaction_header_id,
206 l_sla_line_tbl_type(i).transaction_line_id,
207 l_sla_line_tbl_type(i).base_transaction_header_id,
208 l_sla_line_tbl_type(i).base_transaction_line_id,
209 l_sla_line_tbl_type(i).transaction_sub_type,
210 l_sla_line_tbl_type(i).creation_date,
211 l_sla_line_tbl_type(i).created_by,
212 l_sla_line_tbl_type(i).last_update_date,
213 l_sla_line_tbl_type(i).last_updated_by
214 );
215
216 EXCEPTION
217 WHEN DUP_VAL_ON_INDEX THEN
218 FND_MESSAGE.set_name('DPP', 'DPP_DUPLICATE_LINE_EXTRACT');
219 FND_MESSAGE.set_token('TXN_HDR_ID', l_transaction_header_id);
220 FND_MESSAGE.set_token('BASE_TXN_HDR_ID', p_claim_id);
221 FND_MESSAGE.set_token('BASE_TXN_LINE_ID', l_sla_line_tbl_type(i).base_transaction_line_id);
222 FND_MSG_PUB.add;
223 RAISE FND_API.G_EXC_ERROR;
224 END;
225 END LOOP;
226 END IF;
227 END IF;
228
229 IF FND_API.to_Boolean( p_commit )
230 THEN
231 COMMIT WORK;
232 END IF;
233 x_return_status := FND_API.G_RET_STS_SUCCESS;
234 EXCEPTION
235 WHEN OZF_Utility_PVT.resource_locked THEN
236 ROLLBACK TO CREATE_SLA_Extract_PUB;
237 x_return_status := FND_API.G_RET_STS_ERROR;
238 FND_MSG_PUB.Count_And_Get (
239 p_encoded => FND_API.G_FALSE,
240 p_count => x_msg_count,
241 p_data => x_msg_data
242 );
243 WHEN FND_API.G_EXC_ERROR THEN
244 ROLLBACK TO CREATE_SLA_Extract_PUB;
245 x_return_status := FND_API.G_RET_STS_ERROR;
246 -- Standard call to get message count and if count=1, get the message
247 FND_MSG_PUB.Count_And_Get (
248 p_encoded => FND_API.G_FALSE,
249 p_count => x_msg_count,
250 p_data => x_msg_data
251 );
252 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
253 ROLLBACK TO CREATE_SLA_Extract_PUB;
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 -- Standard call to get message count and if count=1, get the message
256 FND_MSG_PUB.Count_And_Get (
257 p_encoded => FND_API.G_FALSE,
258 p_count => x_msg_count,
259 p_data => x_msg_data
260 );
261 WHEN OTHERS THEN
262 ROLLBACK TO CREATE_SLA_Extract_PUB;
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
265 THEN
266 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
267 END IF;
268 -- Standard call to get message count and if count=1, get the message
269 FND_MSG_PUB.Count_And_Get (
270 p_encoded => FND_API.G_FALSE,
271 p_count => x_msg_count,
272 p_data => x_msg_data
273 );
274 End Create_SLA_extract;
275 END DPP_SLA_CLAIM_EXTRACT_PUB;