[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_MATERIAL_TXN_PUB
Source
1 PACKAGE BODY AHL_PRD_MATERIAL_TXN_PUB AS
2 /* $Header: AHLPMTXB.pls 120.0.12000000.1 2007/10/23 00:41:59 sracha noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_PRD_MATERIAL_TXN_PUB';
5
6 -- FND Logging Constants
7 G_DEBUG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 G_DEBUG_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
9 G_DEBUG_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10 G_DEBUG_UEXP CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11
12 PROCEDURE PERFORM_MATERIAL_TXN (
13 p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
15 p_commit IN VARCHAR2 := FND_API.G_FALSE,
16 p_default IN VARCHAR2 := FND_API.G_FALSE,
17 p_x_material_txn_tbl IN OUT NOCOPY AHL_PRD_MATERIAL_TXN_PUB.Ahl_Material_Txn_Tbl_Type,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2
21 ) IS
22
23 l_api_version CONSTANT NUMBER := 1.0;
24 l_api_name CONSTANT VARCHAR2(30) := 'Perform_Material_Txn';
25 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
26
27 l_ahl_mtltxn_tbl AHL_PRD_MTLTXN_PVT.Ahl_MtlTxn_Tbl_Type;
28 l_disp_Mtl_Txn_Tbl AHL_PRD_DISP_MTL_TXN_PVT.Disp_Mtl_Txn_Tbl_Type;
29
30 BEGIN
31
32 -- Log API entry point
33 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
34 THEN
35 fnd_log.string
36 (
37 G_DEBUG_PROC,
38 l_debug_module||'.begin',
39 'At the start of PLSQL procedure'
40 );
41 END IF;
42
43 -- Standard call to check for call compatibility
44 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name,
45 G_PKG_NAME ) THEN
46 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
47 END IF;
48
49 -- Standard start of API savepoint
50 SAVEPOINT Perform_Material_Txn_pub;
51
52 -- Initialize message list if p_init_msg_list is set to TRUE
53 IF FND_API.To_Boolean(p_init_msg_list) THEN
54 FND_MSG_PUB.Initialize;
55 END IF;
56
57 -- Initialize API return status to success
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59
60 -- Assign variables to l_x_ahl_mtltxn_rec structure.
61 IF (p_x_material_txn_tbl.COUNT > 0) THEN
62 FOR i IN p_x_material_txn_tbl.FIRST..p_x_material_txn_tbl.LAST LOOP
63
64 l_ahl_mtltxn_tbl(i).Workorder_Id := p_x_material_txn_tbl(i).Workorder_Id;
65 l_ahl_mtltxn_tbl(i).Workorder_Name := p_x_material_txn_tbl(i).Workorder_Name;
66 l_ahl_mtltxn_tbl(i).Operation_Seq_Num := p_x_material_txn_tbl(i).Operation_Seq_Num;
67 l_ahl_mtltxn_tbl(i).Transaction_Type_Id := p_x_material_txn_tbl(i).Transaction_Type_Id;
68 l_ahl_mtltxn_tbl(i).Transaction_Type_Name := p_x_material_txn_tbl(i).Transaction_Type_Name;
69
70 l_ahl_mtltxn_tbl(i).Inventory_Item_Id := p_x_material_txn_tbl(i).Inventory_Item_Id;
71 l_ahl_mtltxn_tbl(i).Inventory_Item_Segments := p_x_material_txn_tbl(i).Inventory_Item_Segments;
72 l_ahl_mtltxn_tbl(i).Item_Instance_Number := p_x_material_txn_tbl(i).Item_Instance_Number;
73 l_ahl_mtltxn_tbl(i).Item_Instance_ID := p_x_material_txn_tbl(i).Item_Instance_ID;
74 l_ahl_mtltxn_tbl(i).Revision := p_x_material_txn_tbl(i).Revision;
75 l_ahl_mtltxn_tbl(i).Condition := p_x_material_txn_tbl(i).Condition;
76 l_ahl_mtltxn_tbl(i).Condition_desc := p_x_material_txn_tbl(i).Condition_desc;
77 l_ahl_mtltxn_tbl(i).Subinventory_Name := p_x_material_txn_tbl(i).Subinventory_Name;
78 l_ahl_mtltxn_tbl(i).Locator_Id := p_x_material_txn_tbl(i).Locator_Id;
79 l_ahl_mtltxn_tbl(i).Locator_Segments := p_x_material_txn_tbl(i).Locator_Segments;
80 l_ahl_mtltxn_tbl(i).Quantity := p_x_material_txn_tbl(i).Quantity;
81 l_ahl_mtltxn_tbl(i).Uom := p_x_material_txn_tbl(i).Uom_Code;
82 l_ahl_mtltxn_tbl(i).UOM_Desc := p_x_material_txn_tbl(i).Unit_Of_Measure;
83 l_ahl_mtltxn_tbl(i).Serial_Number := p_x_material_txn_tbl(i).Serial_Number;
84 l_ahl_mtltxn_tbl(i).Lot_Number := p_x_material_txn_tbl(i).Lot_Number;
85
86 l_ahl_mtltxn_tbl(i).Transaction_Date := p_x_material_txn_tbl(i).Transaction_Date;
87 l_ahl_mtltxn_tbl(i).Transaction_Reference := p_x_material_txn_tbl(i).Transaction_Reference;
88 l_ahl_mtltxn_tbl(i).recepient_id := p_x_material_txn_tbl(i).recepient_id;
89 l_ahl_mtltxn_tbl(i).recepient_name := p_x_material_txn_tbl(i).recepient_name;
90 l_ahl_mtltxn_tbl(i).disposition_id := p_x_material_txn_tbl(i).disposition_id;
91
92 -- Target visit is currently not being used.
93 --l_ahl_mtltxn_tbl(i).Target_Visit_Id := p_x_material_txn_tbl(i).Target_Visit_Id;
94 --l_ahl_mtltxn_tbl(i).Target_Visit_Num := p_x_material_txn_tbl(i).Target_Visit_Num;
95
96 l_ahl_mtltxn_tbl(i).Reason_Id := p_x_material_txn_tbl(i).Reason_Id;
97 l_ahl_mtltxn_tbl(i).Reason_Name := p_x_material_txn_tbl(i).Reason_Name;
98 l_ahl_mtltxn_tbl(i).Problem_Code := p_x_material_txn_tbl(i).Problem_Code;
99 l_ahl_mtltxn_tbl(i).Problem_Code_Meaning := p_x_material_txn_tbl(i).Problem_Code_Meaning;
100 l_ahl_mtltxn_tbl(i).Sr_Summary := p_x_material_txn_tbl(i).Sr_Summary;
101 l_ahl_mtltxn_tbl(i).Qa_Collection_Id := p_x_material_txn_tbl(i).Qa_Collection_Id;
102
103 -- Added for ER# 5903318.
104 l_ahl_mtltxn_tbl(i).create_wo_option := p_x_material_txn_tbl(i).create_wo_option;
105
106 l_ahl_mtltxn_tbl(i).ATTRIBUTE_CATEGORY := p_x_material_txn_tbl(i).ATTRIBUTE_CATEGORY;
107 l_ahl_mtltxn_tbl(i).ATTRIBUTE1 := p_x_material_txn_tbl(i).ATTRIBUTE1;
108 l_ahl_mtltxn_tbl(i).ATTRIBUTE2 := p_x_material_txn_tbl(i).ATTRIBUTE2;
109 l_ahl_mtltxn_tbl(i).ATTRIBUTE3 := p_x_material_txn_tbl(i).ATTRIBUTE3;
110 l_ahl_mtltxn_tbl(i).ATTRIBUTE4 := p_x_material_txn_tbl(i).ATTRIBUTE4;
111 l_ahl_mtltxn_tbl(i).ATTRIBUTE5 := p_x_material_txn_tbl(i).ATTRIBUTE5;
112 l_ahl_mtltxn_tbl(i).ATTRIBUTE6 := p_x_material_txn_tbl(i).ATTRIBUTE6;
113 l_ahl_mtltxn_tbl(i).ATTRIBUTE7 := p_x_material_txn_tbl(i).ATTRIBUTE7;
114 l_ahl_mtltxn_tbl(i).ATTRIBUTE8 := p_x_material_txn_tbl(i).ATTRIBUTE8;
115 l_ahl_mtltxn_tbl(i).ATTRIBUTE9 := p_x_material_txn_tbl(i).ATTRIBUTE9;
116 l_ahl_mtltxn_tbl(i).ATTRIBUTE10 := p_x_material_txn_tbl(i).ATTRIBUTE10;
117 l_ahl_mtltxn_tbl(i).ATTRIBUTE11 := p_x_material_txn_tbl(i).ATTRIBUTE11;
118 l_ahl_mtltxn_tbl(i).ATTRIBUTE12 := p_x_material_txn_tbl(i).ATTRIBUTE12;
119 l_ahl_mtltxn_tbl(i).ATTRIBUTE13 := p_x_material_txn_tbl(i).ATTRIBUTE13;
120 l_ahl_mtltxn_tbl(i).ATTRIBUTE14 := p_x_material_txn_tbl(i).ATTRIBUTE14;
121 l_ahl_mtltxn_tbl(i).ATTRIBUTE15 := p_x_material_txn_tbl(i).ATTRIBUTE15;
122
123 END LOOP;
124
125 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
126 THEN
127 fnd_log.string
128 (
129 G_DEBUG_STMT,
130 l_debug_module,
131 'Before calling AHL_PRD_MTLTXN_PVT.PERFORM_MTL_TXN api..count on l_ahl_mtltxn_tbl is:' || l_ahl_mtltxn_tbl.count
132 );
133 END IF;
134
135 -- Call Private API.
136 AHL_PRD_MTLTXN_PVT.PERFORM_MTL_TXN(
137 p_api_version => 1.0,
138 p_init_msg_list => p_init_msg_list,
139 p_commit => p_commit,
140 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
141 p_default => p_default,
142 p_module_type => NULL,
143 p_create_sr => 'Y',
144 p_x_ahl_mtltxn_tbl => l_ahl_mtltxn_tbl,
145 x_return_status => x_return_status,
146 x_msg_count => x_msg_count,
147 x_msg_data => x_msg_data);
148
149 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
150 THEN
151 fnd_log.string
152 (
153 G_DEBUG_STMT,
154 l_debug_module,
155 'Call to AHL_PRD_MTLTXN_PVT.PERFORM_MTL_TXN failed...x_return_status:' || x_return_status
156 );
157 fnd_log.string
158 (
159 G_DEBUG_STMT,
160 l_debug_module,
161 'After call to AHL_PRD_MTLTXN_PVT.PERFORM_MTL_TXN failed. Error count:' || x_msg_count
162 );
163 END IF;
164
165 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
166 RAISE FND_API.G_EXC_ERROR;
167 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
169 END IF;
170
171 IF (l_ahl_mtltxn_tbl.COUNT > 0) THEN
172 FOR i IN l_ahl_mtltxn_tbl.FIRST..l_ahl_mtltxn_tbl.LAST LOOP
173
174 -- Form disposition record structure if disposition ID is not null.
175 IF (l_ahl_mtltxn_tbl(i).disposition_id IS NOT NULL) THEN
176 -- Add record to disposition table.
177 l_disp_Mtl_Txn_Tbl(i).wo_mtl_txn_id := l_ahl_mtltxn_tbl(i).Ahl_MtlTxn_Id;
178 l_disp_Mtl_Txn_Tbl(i).disposition_id := l_ahl_mtltxn_tbl(i).disposition_id;
179 l_disp_Mtl_Txn_Tbl(i).quantity := l_ahl_mtltxn_tbl(i).Quantity;
180 l_disp_Mtl_Txn_Tbl(i).uom := l_ahl_mtltxn_tbl(i).uom;
181 END IF;
182
183 -- set public api output record attributes.
184 p_x_material_txn_tbl(i).Ahl_MtlTxn_Id := l_ahl_mtltxn_tbl(i).Ahl_MtlTxn_Id;
185 p_x_material_txn_tbl(i).Workorder_Id := l_ahl_mtltxn_tbl(i).Workorder_Id;
186 p_x_material_txn_tbl(i).Workorder_Name := l_ahl_mtltxn_tbl(i).Workorder_Name;
187 p_x_material_txn_tbl(i).Operation_Seq_Num := l_ahl_mtltxn_tbl(i).Operation_Seq_Num;
188 p_x_material_txn_tbl(i).Transaction_Type_Id := l_ahl_mtltxn_tbl(i).Transaction_Type_Id;
189 p_x_material_txn_tbl(i).Transaction_Type_Name := l_ahl_mtltxn_tbl(i).Transaction_Type_Name;
190
191 p_x_material_txn_tbl(i).Inventory_Item_Id := l_ahl_mtltxn_tbl(i).Inventory_Item_Id;
192 p_x_material_txn_tbl(i).Inventory_Item_Segments := l_ahl_mtltxn_tbl(i).Inventory_Item_Segments;
193 p_x_material_txn_tbl(i).Item_Instance_Number := l_ahl_mtltxn_tbl(i).Item_Instance_Number;
194 p_x_material_txn_tbl(i).Item_Instance_ID := l_ahl_mtltxn_tbl(i).Item_Instance_ID;
195 p_x_material_txn_tbl(i).Revision := l_ahl_mtltxn_tbl(i).Revision;
196 p_x_material_txn_tbl(i).Condition := l_ahl_mtltxn_tbl(i).Condition;
197 p_x_material_txn_tbl(i).Condition_desc := l_ahl_mtltxn_tbl(i).Condition_desc;
198 p_x_material_txn_tbl(i).Subinventory_Name := l_ahl_mtltxn_tbl(i).Subinventory_Name;
199 p_x_material_txn_tbl(i).Locator_Id := l_ahl_mtltxn_tbl(i).Locator_Id;
200 p_x_material_txn_tbl(i).Locator_Segments := l_ahl_mtltxn_tbl(i).Locator_Segments;
201 p_x_material_txn_tbl(i).Quantity := l_ahl_mtltxn_tbl(i).Quantity;
202 p_x_material_txn_tbl(i).Uom_Code := l_ahl_mtltxn_tbl(i).Uom;
203 p_x_material_txn_tbl(i).Unit_Of_Measure := l_ahl_mtltxn_tbl(i).Uom_Desc;
204 p_x_material_txn_tbl(i).Serial_Number := l_ahl_mtltxn_tbl(i).Serial_Number;
205 p_x_material_txn_tbl(i).Lot_Number := l_ahl_mtltxn_tbl(i).Lot_Number;
206 p_x_material_txn_tbl(i).Transaction_Date := l_ahl_mtltxn_tbl(i).Transaction_Date;
207 p_x_material_txn_tbl(i).Transaction_Reference := l_ahl_mtltxn_tbl(i).Transaction_Reference;
208 p_x_material_txn_tbl(i).recepient_id := l_ahl_mtltxn_tbl(i).recepient_id;
209 p_x_material_txn_tbl(i).recepient_name := l_ahl_mtltxn_tbl(i).recepient_name;
210 p_x_material_txn_tbl(i).disposition_id := l_ahl_mtltxn_tbl(i).disposition_id;
211
212 -- Target visit is currently not used.
213 --p_x_material_txn_tbl(i).Target_Visit_Id := l_x_ahl_mtltxn_tbl(i).Target_Visit_Id;
214 --p_x_material_txn_tbl(i).Target_Visit_Num := l_x_ahl_mtltxn_tbl(i).Target_Visit_Num;
215
216 p_x_material_txn_tbl(i).Reason_Id := l_ahl_mtltxn_tbl(i).Reason_Id;
217 p_x_material_txn_tbl(i).Reason_Name := l_ahl_mtltxn_tbl(i).Reason_Name;
218 p_x_material_txn_tbl(i).Problem_Code := l_ahl_mtltxn_tbl(i).Problem_Code;
219 p_x_material_txn_tbl(i).Problem_Code_Meaning := l_ahl_mtltxn_tbl(i).Problem_Code_Meaning;
220 p_x_material_txn_tbl(i).Sr_Summary := l_ahl_mtltxn_tbl(i).Sr_Summary;
221 p_x_material_txn_tbl(i).Qa_Collection_Id := l_ahl_mtltxn_tbl(i).Qa_Collection_Id;
222
223 p_x_material_txn_tbl(i).ATTRIBUTE_CATEGORY := l_ahl_mtltxn_tbl(i).ATTRIBUTE_CATEGORY;
224 p_x_material_txn_tbl(i).ATTRIBUTE1 := l_ahl_mtltxn_tbl(i).ATTRIBUTE1;
225 p_x_material_txn_tbl(i).ATTRIBUTE2 := l_ahl_mtltxn_tbl(i).ATTRIBUTE2;
226 p_x_material_txn_tbl(i).ATTRIBUTE3 := l_ahl_mtltxn_tbl(i).ATTRIBUTE3;
227 p_x_material_txn_tbl(i).ATTRIBUTE4 := l_ahl_mtltxn_tbl(i).ATTRIBUTE4;
228 p_x_material_txn_tbl(i).ATTRIBUTE5 := l_ahl_mtltxn_tbl(i).ATTRIBUTE5;
229 p_x_material_txn_tbl(i).ATTRIBUTE6 := l_ahl_mtltxn_tbl(i).ATTRIBUTE6;
230 p_x_material_txn_tbl(i).ATTRIBUTE7 := l_ahl_mtltxn_tbl(i).ATTRIBUTE7;
231 p_x_material_txn_tbl(i).ATTRIBUTE8 := l_ahl_mtltxn_tbl(i).ATTRIBUTE8;
232 p_x_material_txn_tbl(i).ATTRIBUTE9 := l_ahl_mtltxn_tbl(i).ATTRIBUTE9;
233 p_x_material_txn_tbl(i).ATTRIBUTE10 := l_ahl_mtltxn_tbl(i).ATTRIBUTE10;
234 p_x_material_txn_tbl(i).ATTRIBUTE11 := l_ahl_mtltxn_tbl(i).ATTRIBUTE11;
235 p_x_material_txn_tbl(i).ATTRIBUTE12 := l_ahl_mtltxn_tbl(i).ATTRIBUTE12;
236 p_x_material_txn_tbl(i).ATTRIBUTE13 := l_ahl_mtltxn_tbl(i).ATTRIBUTE13;
237 p_x_material_txn_tbl(i).ATTRIBUTE14 := l_ahl_mtltxn_tbl(i).ATTRIBUTE14;
238 p_x_material_txn_tbl(i).ATTRIBUTE15 := l_ahl_mtltxn_tbl(i).ATTRIBUTE15;
239
240 END LOOP;
241 END IF; -- l_ahl_mtltxn_tbl.COUNT
242 END IF; -- p_x_material_txn_tbl.COUNT
243
244 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
245 THEN
246 fnd_log.string
247 (
248 G_DEBUG_STMT,
249 l_debug_module,
250 'Before calling Disposition API. Count on l_disp_Mtl_Txn_Tbl is:' || l_disp_Mtl_Txn_Tbl.COUNT
251 );
252 END IF;
253
254 -- Call disposition API.
255 IF (l_disp_Mtl_Txn_Tbl.COUNT > 0) THEN
256 AHL_PRD_DISP_MTL_TXN_PVT.Process_Disp_Mtl_Txn (
257 p_api_version => 1.0,
258 p_init_msg_list => FND_API.G_FALSE,
259 p_commit => FND_API.G_FALSE,
260 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
261 x_return_status => x_return_status,
262 x_msg_count => x_msg_count,
263 x_msg_data => x_msg_data,
264 p_module => 'JSP',
265 p_x_disp_mtl_txn_tbl => l_disp_Mtl_Txn_Tbl);
266
267 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
268 THEN
269 fnd_log.string
270 (
271 G_DEBUG_STMT,
272 l_debug_module,
273 'After call to AHL_PRD_DISP_MTL_TXN_PVT.Process_Disp_Mtl_Txn. Return status:' || x_return_status
274 );
275 fnd_log.string
276 (
277 G_DEBUG_STMT,
278 l_debug_module,
279 'After call to AHL_PRD_DISP_MTL_TXN_PVT.Process_Disp_Mtl_Txn. Error count:' || x_msg_count
280 );
281 END IF;
282
283 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
284 RAISE FND_API.G_EXC_ERROR;
285 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288
289 END IF;
290
291 -- Standard check of p_commit
292 IF FND_API.to_boolean(p_commit) THEN
293 COMMIT;
294 END IF;
295
296 -- Standard call to get message count and if count is 1, get message
297 FND_MSG_PUB.Count_And_Get
298 ( p_count => x_msg_count,
299 p_data => x_msg_data,
300 p_encoded => fnd_api.g_false);
301
302 -- Log API exit point
303 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
304 THEN
305 fnd_log.string
306 (
307 G_DEBUG_PROC,
308 l_debug_module||'.end', 'At the end of PLSQL procedure'
309 );
310 END IF;
311
312 EXCEPTION
313 WHEN FND_API.G_EXC_ERROR THEN
314 ROLLBACK TO Perform_Material_Txn_pub;
315 x_return_status := FND_API.G_RET_STS_ERROR;
316 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
317 p_data => x_msg_data,
318 p_encoded => fnd_api.g_false);
319 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
320 ROLLBACK TO Perform_Material_Txn_pub;
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
322 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
323 p_data => x_msg_data,
324 p_encoded => fnd_api.g_false);
325 WHEN OTHERS THEN
326 ROLLBACK TO Perform_Material_Txn_pub;
327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
328
329 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
330 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
331 p_procedure_name => l_api_name,
332 p_error_text => SUBSTR(SQLERRM,1,240));
333 END IF;
334 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
335 p_data => x_msg_data,
336 p_encoded => fnd_api.g_false);
337 END Perform_Material_Txn;
338
339 END AHL_PRD_MATERIAL_TXN_PUB;