[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_DISP_MTL_TXN_PVT
Source
1 PACKAGE BODY AHL_PRD_DISP_MTL_TXN_PVT AS
2 /* $Header: AHLVDMTB.pls 120.3 2007/10/12 18:09:43 jaramana ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Ahl_Prd_Disp_Mtl_Txn_Pvt';
4 G_LOG_PREFIX VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.';
5
6 ------------------------
7 -- Declare Procedures --
8 ------------------------
9 -- Start of Comments --
10 -- Procedure name : Process_Disp_Mtl_Txn
11 -- Type : Private
12 -- Function : Creates and updates the disposition material transactions.
13 -- Pre-reqs :
14 -- Parameters :
15 --
16 -- Process_Disp_Mtl_Txn Parameters:
17 -- p_x_disp_mtl_txn_tbl IN OUT NOCOPY the material transaction +
18 -- disposition records.
19 --
20 -- End of Comments.
21
22 PROCEDURE Process_Disp_Mtl_Txn (
23 p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
25 p_commit IN VARCHAR2 := FND_API.G_FALSE,
26 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_count OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2,
30 p_module IN VARCHAR2,
31 p_x_disp_mtl_txn_tbl IN OUT NOCOPY AHL_PRD_DISP_MTL_TXN_PVT.Disp_Mtl_Txn_Tbl_Type)
32 IS
33
34 --Begin Performance Tuning
35 -- Begin fix bug 4097556
36 CURSOR get_disp_rec_csr (p_disposition_id IN NUMBER) IS
37 SELECT disp.disposition_id, disp.object_version_number,
38 disp.inventory_item_id, disp.organization_id,
39 disp.immediate_disposition_code, disp.quantity, disp.UOM, disp.workorder_id
40 FROM AHL_PRD_DISPOSITIONS_B disp
41 WHERE disp.disposition_id = p_disposition_id;
42
43 -- Change made by jaramana on August 8, 2007 for bug 6326065 (FP of 6061600)
44 -- Start allowing Disp Txn association for Complete workorders (code 4)
45 cursor workorder_editable_csr(p_workorder_id IN NUMBER) IS
46 SELECT 'x', fnd.meaning from ahl_workorders wo, fnd_lookup_values_vl fnd
47 WHERE workorder_id = p_workorder_id
48 AND wo.status_code IN ('12','7','17','22','5')
49 and fnd.lookup_type = 'AHL_JOB_STATUS' and fnd.lookup_code = wo.status_code;
50 --AND wo.JOB_STATUS_CODE IN ('Closed', 'Cancelled','Draft','Deleted','Complete No-charge');
51
52 --End fix bug 4097556
53 --End Performance Tuning
54
55 CURSOR get_mtl_txn_rec_csr (p_mtl_txn_id IN NUMBER) IS
56 SELECT mt.transaction_type_id, mt.inventory_item_id, mt.organization_id,
57 mt.quantity, mt.uom, wop.workorder_id
58 FROM AHL_WORKORDER_MTL_TXNS mt, AHL_WORKORDER_OPERATIONS wop
59 WHERE mt.WORKORDER_MTL_TXN_ID = p_mtl_txn_id
60 AND wop.workorder_operation_id = mt.workorder_operation_id;
61 --
62 CURSOR get_disp_mtl_txn_rec_csr (p_disposition_id IN NUMBER,
63 p_mtl_txn_id IN NUMBER) IS
64 SELECT *
65 FROM AHL_PRD_DISP_MTL_TXNS
66 WHERE DISPOSITION_ID = p_disposition_id
67 AND WORKORDER_MTL_TXN_ID = p_mtl_txn_id;
68 --
69 CURSOR get_disp_mtx_qty_csr (p_mtl_txn_id IN NUMBER) IS
70 SELECT quantity, uom
71 FROM AHL_PRD_DISP_MTL_TXNS
72 WHERE WORKORDER_MTL_TXN_ID = p_mtl_txn_id;
73 --
74 CURSOR get_mtl_txn_qty_csr (p_mtl_txn_id IN NUMBER) IS
75 SELECT inventory_item_id, quantity, uom
76 FROM AHL_WORKORDER_MTL_TXNS
77 WHERE WORKORDER_MTL_TXN_ID = p_mtl_txn_id;
78 --
79 CURSOR get_disp_qty_csr (p_disposition_id IN NUMBER) IS
80 SELECT disp.quantity
81 FROM AHL_PRD_DISPOSITIONS_B disp
82 WHERE disp.disposition_id = p_disposition_id;
83 --
84 l_old_disp_mx_rec get_disp_mtl_txn_rec_csr%ROWTYPE;
85 l_disp_mx_rec AHL_PRD_DISP_MTL_TXN_PVT.disp_mtl_txn_rec_type;
86
87 l_api_version CONSTANT NUMBER := 1.0;
88 l_api_name CONSTANT VARCHAR2(30) := 'Process_Disp_Mtl_Txn';
89
90 l_disp_rec Get_Disp_Rec_Csr%ROWTYPE;
91 l_mtl_txn_rec Get_Mtl_Txn_Rec_Csr%ROWTYPE;
92
93 TYPE DISP_ID_TBL_TYPE IS TABLE OF NUMBER
94 INDEX BY BINARY_INTEGER;
95
96 l_update_disp_tbl DISP_ID_TBL_TYPE; --table indexed by disposition id that store disposition object version number
97 l_disp_id_index NUMBER; -- use as both index for l_update_disp_tbl and as disposition_id.
98 l_update_disp_rec AHL_PRD_DISPOSITION_PVT.disposition_rec_type;
99
100 l_temp_qty NUMBER;
101 l_temp_uom VARCHAR2(3);
102
103 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
104 l_exist VARCHAR2(1);
105 l_job_status VARCHAR2(80);
106 -- Dummy variable added by jaramana on Oct 11, 2007 for ER 5883257
107 l_mr_asso_tbl AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type;
108
109 --
110 BEGIN
111 -- Standard start of API savepoint
112 SAVEPOINT Process_Disp_Mtl_Txn_pvt;
113
114 -- Initialize Procedure return status to success
115 x_return_status := FND_API.G_RET_STS_SUCCESS;
116
117 -- Initialize message list if p_init_msg_list is set to TRUE
118
119 IF FND_API.To_Boolean(p_init_msg_list) THEN
120 FND_MSG_PUB.Initialize;
121 END IF;
122
123 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
124 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
125 G_LOG_PREFIX||l_api_name||': Begin API',
126 'At the start of the procedure');
127 END IF;
128
129 --1) Insert or update all the records in the update table
130 IF p_x_disp_mtl_txn_tbl.count > 0 THEN
131 FOR i IN p_x_disp_mtl_txn_tbl.FIRST..p_x_disp_mtl_txn_tbl.LAST LOOP
132 l_disp_mx_rec := p_x_disp_mtl_txn_tbl(i);
133
134 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
135 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
136 G_LOG_PREFIX||l_api_name||': Within API',
137 'Just before validating disposition_id i='||i||
138 ' disp_mtl_txn_id='||l_disp_mx_rec.disp_mtl_txn_id||
139 ' wo_mtl_txn_id='||l_disp_mx_rec.wo_mtl_txn_id||
140 ' quantity='||l_disp_mx_rec.quantity||
141 ' uom='||l_disp_mx_rec.uom);
142 END IF;
143 --validate that disposition id is valid
144 OPEN get_disp_rec_csr(l_disp_mx_rec.disposition_id);
145 FETCH get_disp_rec_csr INTO l_disp_rec;
146 IF (get_disp_rec_csr%NOTFOUND) THEN
147 FND_MESSAGE.Set_Name('AHL','AHL_PRD_DISP_ID_INV');
148 FND_MESSAGE.Set_Token('DISPOSITION_ID', l_disp_mx_rec.disposition_id);
149 FND_MSG_PUB.ADD;
150 l_return_status := FND_API.G_RET_STS_ERROR;
151 CLOSE get_disp_rec_csr;
152 RAISE FND_API.G_EXC_ERROR;
153 END IF;
154 CLOSE get_disp_rec_csr;
155
156 -- Begin fix bug 4097556
157 --validate that the disposition is updateable based on the current workorder's status
158 OPEN workorder_editable_csr(l_disp_rec.workorder_id);
159 FETCH workorder_editable_csr INTO l_exist, l_job_status;
160 IF (workorder_editable_csr%FOUND) THEN
161 FND_MESSAGE.Set_Name('AHL','AHL_PRD_DIS_ASSOC_WO_STATUS'); --Cannot update disposition because of current workorder's status
162 FND_MESSAGE.Set_Token('STATUS', l_job_status);
163 FND_MSG_PUB.ADD;
164 l_return_status := FND_API.G_RET_STS_ERROR;
165 CLOSE workorder_editable_csr;
166 RAISE FND_API.G_EXC_ERROR;
167 END IF;
168 CLOSE workorder_editable_csr;
169 -- End fix bug 4097556
170
171 l_update_disp_tbl(l_disp_rec.disposition_id) := l_disp_rec.object_version_number;
172
173 --validate that mtl txn id is valid
174 OPEN get_mtl_txn_rec_csr(l_disp_mx_rec.wo_mtl_txn_id);
175 FETCH get_mtl_txn_rec_csr INTO l_mtl_txn_rec;
176 IF (get_mtl_txn_rec_csr%NOTFOUND) THEN
177 FND_MESSAGE.Set_Name('AHL','AHL_PRD_MTL_TXN_ID_INV');
178 FND_MESSAGE.Set_Token('MTL_TXN_ID', l_disp_mx_rec.wo_mtl_txn_id);
179 FND_MSG_PUB.ADD;
180 l_return_status := FND_API.G_RET_STS_ERROR;
181 CLOSE get_mtl_txn_rec_csr;
182 RAISE FND_API.G_EXC_ERROR;
183 END IF;
184 CLOSE get_mtl_txn_rec_csr;
185
186 --Validate that disposition item and mtl txn item are equal for matching types.
187 IF (((l_disp_rec.immediate_disposition_code <> 'NOT_RECEIVED' AND
188 l_mtl_txn_rec.transaction_type_id = WIP_CONSTANTS.RETCOMP_TYPE ) OR
189 (l_disp_rec.immediate_disposition_code = 'NOT_RECEIVED' AND
190 l_mtl_txn_rec.transaction_type_id = WIP_CONSTANTS.ISSCOMP_TYPE))
191 AND (l_disp_rec.inventory_item_id <> l_mtl_txn_rec.inventory_item_id)) THEN
192 -- (Jay found this problem) The organization_id in disposition entity maps to instance's last_vld_org_id
193 -- if the instance_id is not null. Thus it could be different from job's org which equals to material
194 -- transaction organization.
195 -- OR l_disp_rec.organization_id <> l_mtl_txn_rec.organization_id)) THEN
196 FND_MESSAGE.Set_Name('AHL','AHL_PRD_DISP_MTL_TXN_ITEM_ILL');
197 FND_MSG_PUB.ADD;
198 l_return_status := FND_API.G_RET_STS_ERROR;
199 RAISE FND_API.G_EXC_ERROR;
200 END IF;
201
202 IF (l_disp_rec.workorder_id <> l_mtl_txn_rec.workorder_id) THEN
203 FND_MESSAGE.Set_Name('AHL','AHL_PRD_DISP_MTL_TXN_WO_ILL');
204 FND_MSG_PUB.ADD;
205 l_return_status := FND_API.G_RET_STS_ERROR;
206 RAISE FND_API.G_EXC_ERROR;
207 END IF;
208
209 --If transaction item equals the disposition item,
210 -- then do UOM conversion to Disposition UOM.
211 IF (l_disp_rec.inventory_item_id = l_mtl_txn_rec.inventory_item_id AND
212 l_disp_rec.UOM <> l_disp_mx_rec.UOM AND
213 l_disp_mx_rec.QUANTITY IS NOT NULL AND
214 l_disp_mx_rec.QUANTITY <> FND_API.G_MISS_NUM ) THEN
215 l_disp_mx_rec.quantity:= inv_convert.inv_um_convert(item_id => l_disp_rec.inventory_item_id,
216 precision => 6,
217 from_quantity => l_disp_mx_rec.quantity,
218 from_unit => l_disp_mx_rec.uom,
219 to_unit => l_disp_rec.uom,
220 from_name => null,
221 to_name => null);
222 l_disp_mx_rec.uom := l_disp_rec.uom;
223 IF (l_disp_mx_rec.quantity < 0) THEN
224 FND_MESSAGE.Set_Name('AHL','AHL_COM_UOM_CONV_FAILED');
225 FND_MESSAGE.Set_Token('FROM_UOM', l_disp_mx_rec.UOM);
226 FND_MESSAGE.Set_Token('TO_UOM', l_disp_rec.uom);
227 FND_MSG_PUB.ADD;
228 l_return_status := FND_API.G_RET_STS_ERROR;
229 RAISE FND_API.G_EXC_ERROR;
230 END IF;
231 END IF;
232
233
234 --Fetch the record and see if it exists already
235 OPEN get_disp_mtl_txn_rec_csr(l_disp_mx_rec.disposition_id,
236 l_disp_mx_rec.wo_mtl_txn_id);
237 FETCH get_disp_mtl_txn_rec_csr INTO l_old_disp_mx_rec;
238
239 IF (get_disp_mtl_txn_rec_csr%NOTFOUND) THEN
240 --CREATE new record.
241 IF (p_module = 'JSP') THEN
242 IF (l_disp_mx_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
243 l_disp_mx_rec.ATTRIBUTE_CATEGORY := null;
244 END IF;
245 IF (l_disp_mx_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
246 l_disp_mx_rec.ATTRIBUTE1 := null;
247 END IF;
248 IF (l_disp_mx_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
249 l_disp_mx_rec.ATTRIBUTE2 := null;
250 END IF;
251 IF (l_disp_mx_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
252 l_disp_mx_rec.ATTRIBUTE3 := null;
253 END IF;
254 IF (l_disp_mx_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
255 l_disp_mx_rec.ATTRIBUTE4 := null;
256 END IF;
257 IF (l_disp_mx_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
258 l_disp_mx_rec.ATTRIBUTE5 := null;
259 END IF;
260 IF (l_disp_mx_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
261 l_disp_mx_rec.ATTRIBUTE6 := null;
262 END IF;
263 IF (l_disp_mx_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
264 l_disp_mx_rec.ATTRIBUTE7 := null;
265 END IF;
266 IF (l_disp_mx_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
267 l_disp_mx_rec.ATTRIBUTE8 := null;
268 END IF;
269 IF (l_disp_mx_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
270 l_disp_mx_rec.ATTRIBUTE9 := null;
271 END IF;
272 IF (l_disp_mx_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
273 l_disp_mx_rec.ATTRIBUTE10 := null;
274 END IF;
275 IF (l_disp_mx_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
276 l_disp_mx_rec.ATTRIBUTE11 := null;
277 END IF;
278 IF (l_disp_mx_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
279 l_disp_mx_rec.ATTRIBUTE12 := null;
280 END IF;
281 IF (l_disp_mx_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
282 l_disp_mx_rec.ATTRIBUTE13 := null;
283 END IF;
284 IF (l_disp_mx_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
285 l_disp_mx_rec.ATTRIBUTE14 := null;
286 END IF;
287 IF (l_disp_mx_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
288 l_disp_mx_rec.ATTRIBUTE15 := null;
289 END IF;
290 END IF; --p_module = JSP
291
292 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
293 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
294 G_LOG_PREFIX||l_api_name||': Within the API',
295 'Immediately before inserting record');
296 END IF;
297 --Do inserts
298 INSERT INTO ahl_prd_disp_mtl_txns (
299 DISP_MTL_TXN_ID,
300 OBJECT_VERSION_NUMBER,
301 LAST_UPDATE_DATE,
302 LAST_UPDATED_BY,
303 CREATION_DATE,
304 CREATED_BY,
305 LAST_UPDATE_LOGIN,
306 DISPOSITION_ID,
307 WORKORDER_MTL_TXN_ID,
308 QUANTITY,
309 UOM,
310 ATTRIBUTE_CATEGORY,
311 ATTRIBUTE1,
312 ATTRIBUTE2,
313 ATTRIBUTE3,
314 ATTRIBUTE4,
315 ATTRIBUTE5,
316 ATTRIBUTE6,
317 ATTRIBUTE7,
318 ATTRIBUTE8,
319 ATTRIBUTE9,
320 ATTRIBUTE10,
321 ATTRIBUTE11,
322 ATTRIBUTE12,
323 ATTRIBUTE13,
324 ATTRIBUTE14,
325 ATTRIBUTE15
326 ) VALUES (
327 AHL_PRD_DISP_MTL_TXNS_S.nextval,
328 1,
329 sysdate,
330 fnd_global.user_id,
331 sysdate,
332 fnd_global.user_id,
333 fnd_global.login_id,
334 l_disp_mx_rec.disposition_id,
335 l_disp_mx_rec.wo_mtl_txn_id,
336 l_disp_mx_rec.quantity,
337 l_disp_mx_rec.uom,
338 l_disp_mx_rec.attribute_category ,
339 l_disp_mx_rec.attribute1 ,
340 l_disp_mx_rec.attribute2 ,
341 l_disp_mx_rec.attribute3 ,
342 l_disp_mx_rec.attribute4 ,
343 l_disp_mx_rec.attribute5 ,
344 l_disp_mx_rec.attribute6 ,
345 l_disp_mx_rec.attribute7 ,
346 l_disp_mx_rec.attribute8 ,
347 l_disp_mx_rec.attribute9 ,
348 l_disp_mx_rec.attribute10 ,
349 l_disp_mx_rec.attribute11 ,
350 l_disp_mx_rec.attribute12 ,
351 l_disp_mx_rec.attribute13 ,
352 l_disp_mx_rec.attribute14 ,
353 l_disp_mx_rec.attribute15
354 )
355 returning DISP_MTL_TXN_ID INTO p_x_disp_mtl_txn_tbl(i).disp_mtl_txn_id;
359 'disp_mtl_txn_id ='||p_x_disp_mtl_txn_tbl(i).disp_mtl_txn_id);
356 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
357 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
358 G_LOG_PREFIX||l_api_name||': Within the API',
360 END IF;
361 ELSE
362
363 --Use existing disposition mtl txn id.
364 l_disp_mx_rec.DISP_MTL_TXN_ID := l_old_disp_mx_rec.DISP_MTL_TXN_ID;
365 p_x_disp_mtl_txn_tbl(i).disp_mtl_txn_id := l_disp_mx_rec.disp_mtl_txn_id;
366
367 -- Check Object version number.
368 IF (l_old_disp_mx_rec.object_version_number <> l_disp_mx_rec.object_version_number) THEN
369 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
370 FND_MSG_PUB.ADD;
371 l_return_status := FND_API.G_RET_STS_ERROR;
372 CLOSE get_disp_mtl_txn_rec_csr;
373 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
374 END IF;
375
376 --Do NULL/G_MISS conversion
377 IF (p_module = 'JSP') THEN
378 IF (l_disp_mx_rec.QUANTITY IS NULL) THEN
379 l_disp_mx_rec.QUANTITY := l_old_disp_mx_rec.QUANTITY;
380 ELSIF (l_disp_mx_rec.QUANTITY= FND_API.G_MISS_NUM) THEN
381 l_disp_mx_rec.QUANTITY:= NULL;
382 END IF;
383
384 IF (l_disp_mx_rec.UOM IS NULL) THEN
385 l_disp_mx_rec.UOM:= l_old_disp_mx_rec.UOM;
386 ELSIF (l_disp_mx_rec.UOM= FND_API.G_MISS_CHAR) THEN
387 l_disp_mx_rec.UOM:= NULL;
388 l_disp_mx_rec.UOM := l_old_disp_mx_rec.UOM;
389 END IF;
390
391 IF (l_disp_mx_rec.ATTRIBUTE_CATEGORY IS NULL) THEN
392 l_disp_mx_rec.ATTRIBUTE_CATEGORY := l_old_disp_mx_rec.ATTRIBUTE_CATEGORY;
393 ELSIF (l_disp_mx_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
394 l_disp_mx_rec.ATTRIBUTE_CATEGORY := NULL;
395 END IF;
396 IF (l_disp_mx_rec.ATTRIBUTE1 IS NULL) THEN
397 l_disp_mx_rec.ATTRIBUTE1 := l_old_disp_mx_rec.ATTRIBUTE1;
398 ELSIF (l_disp_mx_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
399 l_disp_mx_rec.ATTRIBUTE1 := NULL;
400 END IF;
401 IF (l_disp_mx_rec.ATTRIBUTE2 IS NULL) THEN
402 l_disp_mx_rec.ATTRIBUTE2 := l_old_disp_mx_rec.ATTRIBUTE2;
403 ELSIF (l_disp_mx_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
404 l_disp_mx_rec.ATTRIBUTE2 := NULL;
405 END IF;
406 IF (l_disp_mx_rec.ATTRIBUTE3 IS NULL) THEN
407 l_disp_mx_rec.ATTRIBUTE3 := l_old_disp_mx_rec.ATTRIBUTE3;
408 ELSIF (l_disp_mx_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
409 l_disp_mx_rec.ATTRIBUTE3 := NULL;
410 END IF;
411 IF (l_disp_mx_rec.ATTRIBUTE4 IS NULL) THEN
412 l_disp_mx_rec.ATTRIBUTE4 := l_old_disp_mx_rec.ATTRIBUTE4;
413 ELSIF (l_disp_mx_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
414 l_disp_mx_rec.ATTRIBUTE4 := NULL;
415 END IF;
416 IF (l_disp_mx_rec.ATTRIBUTE5 IS NULL) THEN
417 l_disp_mx_rec.ATTRIBUTE5 := l_old_disp_mx_rec.ATTRIBUTE5;
418 ELSIF (l_disp_mx_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
419 l_disp_mx_rec.ATTRIBUTE5 := NULL;
420 END IF;
421 IF (l_disp_mx_rec.ATTRIBUTE6 IS NULL) THEN
422 l_disp_mx_rec.ATTRIBUTE6 := l_old_disp_mx_rec.ATTRIBUTE6;
423 ELSIF (l_disp_mx_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
424 l_disp_mx_rec.ATTRIBUTE6 := NULL;
425 END IF;
426 IF (l_disp_mx_rec.ATTRIBUTE7 IS NULL) THEN
427 l_disp_mx_rec.ATTRIBUTE7 := l_old_disp_mx_rec.ATTRIBUTE7;
428 ELSIF (l_disp_mx_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
429 l_disp_mx_rec.ATTRIBUTE7 := NULL;
430 END IF;
431 IF (l_disp_mx_rec.ATTRIBUTE8 IS NULL) THEN
432 l_disp_mx_rec.ATTRIBUTE8 := l_old_disp_mx_rec.ATTRIBUTE8;
433 ELSIF (l_disp_mx_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
434 l_disp_mx_rec.ATTRIBUTE8 := NULL;
435 END IF;
436 IF (l_disp_mx_rec.ATTRIBUTE9 IS NULL) THEN
437 l_disp_mx_rec.ATTRIBUTE9 := l_old_disp_mx_rec.ATTRIBUTE9;
438 ELSIF (l_disp_mx_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
439 l_disp_mx_rec.ATTRIBUTE9 := NULL;
440 END IF;
441 IF (l_disp_mx_rec.ATTRIBUTE10 IS NULL) THEN
442 l_disp_mx_rec.ATTRIBUTE10 := l_old_disp_mx_rec.ATTRIBUTE10;
443 ELSIF (l_disp_mx_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
444 l_disp_mx_rec.ATTRIBUTE10 := NULL;
445 END IF;
446 IF (l_disp_mx_rec.ATTRIBUTE11 IS NULL) THEN
447 l_disp_mx_rec.ATTRIBUTE11 := l_old_disp_mx_rec.ATTRIBUTE11;
448 ELSIF (l_disp_mx_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
449 l_disp_mx_rec.ATTRIBUTE11 := NULL;
450 END IF;
451 IF (l_disp_mx_rec.ATTRIBUTE12 IS NULL) THEN
452 l_disp_mx_rec.ATTRIBUTE12 := l_old_disp_mx_rec.ATTRIBUTE12;
453 ELSIF (l_disp_mx_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
454 l_disp_mx_rec.ATTRIBUTE12 := NULL;
455 END IF;
456 IF (l_disp_mx_rec.ATTRIBUTE13 IS NULL) THEN
457 l_disp_mx_rec.ATTRIBUTE13 := l_old_disp_mx_rec.ATTRIBUTE13;
458 ELSIF (l_disp_mx_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
459 l_disp_mx_rec.ATTRIBUTE13 := NULL;
460 END IF;
461 IF (l_disp_mx_rec.ATTRIBUTE14 IS NULL) THEN
462 l_disp_mx_rec.ATTRIBUTE14 := l_old_disp_mx_rec.ATTRIBUTE14;
463 ELSIF (l_disp_mx_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
464 l_disp_mx_rec.ATTRIBUTE14 := NULL;
465 END IF;
469 l_disp_mx_rec.ATTRIBUTE15 := NULL;
466 IF (l_disp_mx_rec.ATTRIBUTE15 IS NULL) THEN
467 l_disp_mx_rec.ATTRIBUTE15 := l_old_disp_mx_rec.ATTRIBUTE15;
468 ELSIF (l_disp_mx_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
470 END IF;
471 END IF; -- p_module flag: JSP
472
473 --UPDATE existing record
474 UPDATE ahl_prd_disp_mtl_txns SET
475 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
476 LAST_UPDATE_DATE = sysdate,
477 LAST_UPDATED_BY = fnd_global.USER_ID,
478 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
479 QUANTITY = l_disp_mx_rec.quantity,
480 UOM = l_disp_mx_rec.uom,
481 ATTRIBUTE_CATEGORY = l_disp_mx_rec.ATTRIBUTE_CATEGORY,
482 ATTRIBUTE1 = l_disp_mx_rec.ATTRIBUTE1,
483 ATTRIBUTE2 = l_disp_mx_rec.ATTRIBUTE2,
484 ATTRIBUTE3 = l_disp_mx_rec.ATTRIBUTE3,
485 ATTRIBUTE4 = l_disp_mx_rec.ATTRIBUTE4,
486 ATTRIBUTE5 = l_disp_mx_rec.ATTRIBUTE5,
487 ATTRIBUTE6 = l_disp_mx_rec.ATTRIBUTE6,
488 ATTRIBUTE7 = l_disp_mx_rec.ATTRIBUTE7,
489 ATTRIBUTE8 = l_disp_mx_rec.ATTRIBUTE8,
490 ATTRIBUTE9 = l_disp_mx_rec.ATTRIBUTE9,
491 ATTRIBUTE10 = l_disp_mx_rec.ATTRIBUTE10,
492 ATTRIBUTE11 = l_disp_mx_rec.ATTRIBUTE11,
493 ATTRIBUTE12 = l_disp_mx_rec.ATTRIBUTE12,
494 ATTRIBUTE13 = l_disp_mx_rec.ATTRIBUTE13,
495 ATTRIBUTE14 = l_disp_mx_rec.ATTRIBUTE14,
496 ATTRIBUTE15 = l_disp_mx_rec.ATTRIBUTE15
497 WHERE DISP_MTL_TXN_ID = l_disp_mx_rec.disp_mtl_txn_id;
498
499 END IF;
500 CLOSE get_disp_mtl_txn_rec_csr;
501
502 END LOOP;
503
504
505 --2) Now validate that the sums of the dispositions and mtl transactions are valid.
506 FOR i IN p_x_disp_mtl_txn_tbl.FIRST..p_x_disp_mtl_txn_tbl.LAST LOOP
507 OPEN get_disp_qty_csr (p_x_disp_mtl_txn_tbl(i).disposition_id);
508 FETCH get_disp_qty_csr INTO l_disp_rec.quantity;
509 CLOSE get_disp_qty_csr;
510
511 --Verify that disposition quantity is not exceeded.
512 --No UOM conversion is necessary, because all QTYs store in Disposition UOM
513 IF (Calculate_Txned_Qty(p_x_disp_mtl_txn_tbl(i).disposition_id) > l_disp_rec.quantity) THEN
514 FND_MESSAGE.Set_Name('AHL','AHL_PRD_DISP_QTY_EXCEEDED');
515 FND_MESSAGE.Set_Token('DISPOSITION_ID', p_x_disp_mtl_txn_tbl(i).disposition_id);
516 FND_MSG_PUB.ADD;
517 l_return_status := FND_API.G_RET_STS_ERROR;
518 END IF;
519
520 --Verify the mtl transaction quantity is not exceeded by the various dispositions.
521 OPEN get_mtl_txn_qty_csr(p_x_disp_mtl_txn_tbl(i).wo_mtl_txn_id);
522 FETCH get_mtl_txn_qty_csr INTO l_mtl_txn_rec.inventory_item_id,
523 l_mtl_txn_rec.quantity, l_mtl_txn_rec.uom;
524 CLOSE get_mtl_txn_qty_csr;
525
526 OPEN get_disp_mtx_qty_csr(p_x_disp_mtl_txn_tbl(i).wo_mtl_txn_id);
527 LOOP
528 FETCH get_disp_mtx_qty_csr INTO l_temp_qty, l_temp_uom;
529 EXIT WHEN get_disp_mtx_qty_csr%NOTFOUND;
530
531 --If transaction item equals the disposition item, then do UOM conversion.
532 IF (l_temp_uom = l_mtl_txn_rec.uom) THEN
533 l_mtl_txn_rec.quantity := l_mtl_txn_rec.quantity - l_temp_qty;
534 ELSE
535 l_temp_qty:= inv_convert.inv_um_convert(item_id => l_mtl_txn_rec.inventory_item_id,
536 precision => 6,
537 from_quantity => l_temp_qty,
538 from_unit => l_temp_uom,
539 to_unit => l_mtl_txn_rec.uom,
540 from_name => null,
541 to_name => null);
542 --Make sure that the temporary qty is valid after conversion
543 IF (l_temp_qty>0) THEN
544 l_mtl_txn_rec.quantity := l_mtl_txn_rec.quantity - l_temp_qty;
545 END IF;
546 END IF;
547
548 --If quantity count is less than 0, then raise exception
549 IF (l_mtl_txn_rec.quantity<0) THEN
550 FND_MESSAGE.Set_Name('AHL','AHL_PRD_MTL_TXN_QTY_EXCEEDED');
551 FND_MESSAGE.Set_Token('MTL_TXN_ID', p_x_disp_mtl_txn_tbl(i).wo_mtl_txn_id);
552 FND_MSG_PUB.ADD;
553 l_return_status := FND_API.G_RET_STS_ERROR;
554 EXIT;
555 END IF;
556 END LOOP;
557 CLOSE get_disp_mtx_qty_csr;
558 END LOOP;
559
560 --Added by Peter
561 --Call process_disposition so that disposition status will be recalculated
562 --to be in synch with material transaction
563 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
564 FOR l_disp_id_index IN l_update_disp_tbl.FIRST..l_update_disp_tbl.LAST LOOP
565 l_update_disp_rec.disposition_id := l_disp_id_index;
566 l_update_disp_rec.object_version_number := l_update_disp_tbl(l_disp_id_index);
567 l_update_disp_rec.operation_flag := AHL_PRD_DISPOSITION_PVT.G_OP_UPDATE;
568
569 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
570 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
571 G_LOG_PREFIX||l_api_name||': Before calling process_disposition: ',
572 ' disposition_id' || l_update_disp_rec.disposition_id ||
573 ' obj_ver_num: ' || l_update_disp_rec.object_version_number
574 ||' x_msg_data: ' || x_msg_data );
575 END IF;
576
580 p_commit => Fnd_Api.g_false,
577 AHL_PRD_DISPOSITION_PVT.process_disposition(
578 p_api_version => p_api_version,
579 p_init_msg_list => p_init_msg_list,
581 p_validation_level => p_validation_level,
582 p_module_type => p_module,
583 p_x_disposition_rec => l_update_disp_rec,
584 -- Dummy parameter added by jaramana on Oct 11, 2007 for ER 5883257
585 p_mr_asso_tbl => l_mr_asso_tbl,
586 x_return_status => x_return_status,
587 x_msg_count => x_msg_count,
588 x_msg_data => x_msg_data);
589 END LOOP;
590
591 END IF;
592
593 END IF; -- If p_x_disp_mtl_txn_tbl.count > 0
594
595
596 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
597 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
598 G_LOG_PREFIX||l_api_name||': End API',
599 'At the end of the procedure');
600 END IF;
601 -- Check Error Message stack.
602 /*
603 Do not use this check since this API can be called after
604 performing a material txn which puts a message (success)
605 into the message stack
606
607 x_msg_count := FND_MSG_PUB.count_msg;
608 IF x_msg_count > 0 THEN
609 RAISE FND_API.G_EXC_ERROR;
610 END IF;
611 */
612 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
613 RAISE FND_API.G_EXC_ERROR;
614 END IF;
615
616 -- Standard check of p_commit
617 IF FND_API.TO_BOOLEAN(p_commit) THEN
618 COMMIT WORK;
619 END IF;
620
621 -- Standard call to get message count and if count is 1, get message info
622 FND_MSG_PUB.Count_And_Get
623 ( p_count => x_msg_count,
624 p_data => x_msg_data,
625 p_encoded => fnd_api.g_false
626 );
627
628 EXCEPTION
629 WHEN FND_API.G_EXC_ERROR THEN
630 Rollback to Process_Disp_Mtl_Txn_pvt;
631 x_return_status := FND_API.G_RET_STS_ERROR;
632 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
633 p_data => x_msg_data,
634 p_encoded => fnd_api.g_false);
635 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
636 Rollback to Process_Disp_Mtl_Txn_pvt;
637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
638 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
639 p_data => x_msg_data,
640 p_encoded => fnd_api.g_false);
641 WHEN OTHERS THEN
642 Rollback to Process_Disp_Mtl_Txn_pvt;
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
645 p_procedure_name => l_api_name,
646 p_error_text => SQLERRM);
647 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
648 p_data => x_msg_data,
649 p_encoded => fnd_api.g_false);
650 END Process_Disp_Mtl_Txn;
651
652 ------------------------
653 -- Start of Comments --
654 -- Procedure name : Get_Disp_For_Mtl_Txn
655 -- Type : Private
656 -- Function : Fetch the matching dispositions for given material txn
657 -- Pre-reqs :
658 -- Parameters : p_wo_mtl_txn_id: The material transaction id
659 -- x_disp_list_tbl: returning list of dispositions
660 --
661 --
662 -- End of Comments.
663
664 PROCEDURE Get_Disp_For_Mtl_Txn (
665 p_api_version IN NUMBER,
666 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
667 p_commit IN VARCHAR2 := FND_API.G_FALSE,
668 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
669 x_return_status OUT NOCOPY VARCHAR2,
670 x_msg_count OUT NOCOPY NUMBER,
671 x_msg_data OUT NOCOPY VARCHAR2,
672 p_wo_mtl_txn_id IN NUMBER,
673 x_disp_list_tbl OUT NOCOPY disp_mtxn_assoc_tbl_type)
674 IS
675 --
676 CURSOR get_mtl_txn_csr(p_mtl_txn_id IN NUMBER) IS
677 SELECT oper.workorder_id, txn.transaction_type_id,
678 txn.inventory_item_id, txn.organization_id,
679 txn.serial_number, txn.lot_number,
680 NVL(mtl.comms_nl_trackable_flag,'N') trackable_flag
681 FROM AHL_WORKORDER_MTL_TXNS txn, AHL_WORKORDER_OPERATIONS oper,
682 mtl_system_items_kfv mtl
683 WHERE txn.workorder_mtl_txn_id = p_mtl_txn_id
684 AND txn.workorder_operation_id = oper.workorder_operation_id
685 AND txn.inventory_item_id = mtl.inventory_item_id
686 AND txn.organization_id = mtl.organization_id;
687 --
688 --This fetches all dispositions for wo, that has same mtl_txn_id
689 --or has not been completed transacted.
690 CURSOR get_disp_return_csr (p_mtl_txn_id IN NUMBER,
691 p_workorder_id IN NUMBER,
692 p_trackable_flag IN VARCHAR2,
693 p_txn_item_id IN NUMBER,
694 p_txn_org_id IN NUMBER,
695 p_serial_number IN VARCHAR2,
696 p_lot_number IN VARCHAR2) IS
697 /*
698 SELECT distinct disp.disposition_id,
699 disp.inventory_item_id,
700 disp.organization_id,
701 disp.item_number,
702 disp.item_group_id,
703 disp.item_group_name,
707 disp.immediate_type,
704 disp.serial_number,
705 disp.lot_number,
706 disp.immediate_disposition_code,
708 disp.secondary_disposition_code,
709 disp.secondary_type,
710 disp.status_code,
711 disp.status,
712 disp.quantity,
713 disp.uom,
714 0, --default is 0. will populate this field inside the loop
715 disp.uom assoc_uom,
716 disp.quantity-Calculate_Txned_Qty(disp.disposition_id),
717 disp.uom
718 FROM AHL_PRD_DISPOSITIONS_V disp, AHL_PRD_DISP_MTL_TXNS assoc
719 WHERE (disp.disposition_id = assoc.disposition_id --Either match on existing reln
720 AND assoc.workorder_mtl_txn_id = p_mtl_txn_id)
721 OR (disp.trackable_flag = p_trackable_flag
722 AND disp.workorder_id = p_workorder_id
723 AND (disp.status_code IS NULL OR disp.status_code <> 'TERMINATED')
724 AND disp.quantity > Calculate_Txned_Qty(disp.disposition_id) --Find untxned dispositions
725 AND disp.immediate_disposition_code NOT IN ('NOT_RECEIVED','NA','NOT_REMOVED')
726 AND disp.inventory_item_id = p_txn_item_id
727 AND disp.organization_id = p_txn_org_id
728 AND (disp.serial_number IS NULL OR disp.serial_number = p_serial_number)
729 AND (disp.lot_number IS NULL OR disp.lot_number = p_lot_number));
730 */
731 -- AnRaj: Changed the query for the cursor, issue #2, bug # 5258284
732 SELECT distinct disp.disposition_id,
733 disp.inventory_item_id,
734 disp.organization_id,
735 mtl.concatenated_segments item_number,
736 disp.item_group_id ,
737 grp.name item_group_name ,
738 disp.serial_number,
739 disp.lot_number,
740 disp.immediate_disposition_code,
741 flvt1.meaning immediate_type,
742 disp.secondary_disposition_code,
743 flvt2.meaning secondary_type,
744 disp.STATUS_CODE ,
745 flvt3.MEANING STATUS ,
746 disp.quantity,
747 disp.uom,
748 0,
749 disp.uom assoc_uom,
750 disp.quantity-calculate_txned_qty(disp.disposition_id),
751 disp.uom
752 FROM ahl_prd_dispositions_vl disp,
753 ahl_prd_disp_mtl_txns assoc,
754 mtl_system_items_kfv mtl,
755 ahl_item_groups_b grp,
756 fnd_lookup_values flvt1,
757 fnd_lookup_values flvt2,
758 fnd_lookup_values flvt3
759 WHERE (
760 ( disp.disposition_id = assoc.disposition_id --Either match on existing reln
761 AND
762 assoc.workorder_mtl_txn_id = p_mtl_txn_id
763 )
764 OR
765 ( decode(disp.instance_id, null, decode(disp.path_position_id, null, 'N', 'Y'), 'Y') = p_trackable_flag
766 AND disp.workorder_id = p_workorder_id
767 AND (disp.status_code IS NULL OR disp.status_code <> 'TERMINATED')
768 AND disp.quantity > Calculate_Txned_Qty(disp.disposition_id) --Find untxned dispositions
769 AND disp.immediate_disposition_code NOT IN ('NOT_RECEIVED','NA','NOT_REMOVED')
770 AND disp.inventory_item_id = p_txn_item_id
771 AND disp.organization_id = p_txn_org_id
772 AND (disp.serial_number IS NULL OR disp.serial_number = p_serial_number)
773 AND (disp.lot_number IS NULL OR disp.lot_number = p_lot_number)
774 )
775 )
776 AND disp.inventory_item_id = mtl.inventory_item_id (+)
777 AND disp.organization_id = mtl.organization_id (+)
778 AND disp.ITEM_GROUP_ID = grp.item_group_id (+)
779 AND flvt1.lookup_type(+) = 'AHL_IMMED_DISP_TYPE'
780 AND flvt1.LOOKUP_CODE (+) = disp.immediate_disposition_code
781 AND flvt1.LANGUAGE(+) = userenv('LANG')
782 AND flvt2.lookup_type(+) = 'AHL_SECND_DISP_TYPE'
783 AND flvt2.lookup_code (+) = disp.secondary_disposition_code
784 AND flvt2.LANGUAGE(+) = userenv('LANG')
785 AND flvt3.lookup_type(+) = 'AHL_DISP_STATUS'
786 AND flvt3.lookup_code (+) = disp.STATUS_CODE
787 AND flvt3.LANGUAGE(+) = userenv('LANG') ;
788 --
789 --This fetches all dispositions for wo, that has same mtl_txn_id
790 --or has not been completed transacted.
791 CURSOR get_disp_issue_csr (p_mtl_txn_id IN NUMBER,
792 p_workorder_id IN NUMBER,
793 p_trackable_flag IN VARCHAR2) IS
794 /*
795 SELECT distinct disp.disposition_id,
796 disp.inventory_item_id,
797 disp.organization_id,
798 disp.item_number,
799 disp.item_group_id,
800 disp.item_group_name,
801 disp.serial_number,
802 disp.lot_number,
803 disp.immediate_disposition_code,
804 disp.immediate_type,
805 disp.secondary_disposition_code,
806 disp.secondary_type,
807 disp.status_code,
808 disp.status,
809 disp.quantity,
810 disp.uom,
811 0,
812 disp.uom assoc_uom,
813 disp.quantity-Calculate_Txned_Qty(disp.disposition_id),
814 disp.uom
818 OR (disp.trackable_flag = p_trackable_flag
815 FROM AHL_PRD_DISPOSITIONS_V disp, AHL_PRD_DISP_MTL_TXNS assoc
816 WHERE (disp.disposition_id = assoc.disposition_id --Either match on existing reln
817 AND assoc.workorder_mtl_txn_id = p_mtl_txn_id)
819 AND disp.workorder_id = p_workorder_id
820 AND disp.status_code <> 'TERMINATED'
821 AND (disp.immediate_disposition_code NOT IN ('NOT_RECEIVED','NA','NOT_REMOVED')
822 OR (disp.immediate_disposition_code = 'NOT_RECEIVED'
823 AND disp.quantity > Calculate_Txned_Qty(disp.disposition_id))));
824 */
825 -- AnRaj: Changed the query for the cursor, issue #1, bug # 5258284
826 SELECT distinct disp.disposition_id,
827 disp.inventory_item_id,
828 disp.organization_id,
829 mtl.concatenated_segments item_number,
830 disp.item_group_id ,
831 grp.name item_group_name ,
832 disp.serial_number,
833 disp.lot_number,
834 disp.immediate_disposition_code,
835 flvt1.meaning immediate_type,
836 disp.secondary_disposition_code,
837 flvt2.meaning secondary_type,
838 disp.STATUS_CODE ,
839 flvt3.MEANING STATUS ,
840 disp.quantity,
841 disp.uom,
842 0,
843 disp.uom assoc_uom,
844 disp.quantity-calculate_txned_qty(disp.disposition_id),
845 disp.uom
846 FROM ahl_prd_dispositions_vl disp,
847 ahl_prd_disp_mtl_txns assoc,
848 mtl_system_items_kfv mtl,
849 ahl_item_groups_b grp,
850 fnd_lookup_values flvt1,
851 fnd_lookup_values flvt2,
852 fnd_lookup_values flvt3
853 WHERE (
854 ( disp.disposition_id = assoc.disposition_id
855 and assoc.workorder_mtl_txn_id = p_mtl_txn_id
856 )
857 or
858 ( decode(disp.instance_id, null, decode(disp.path_position_id, null, 'N', 'Y'), 'Y') = p_trackable_flag
859 and disp.workorder_id = p_workorder_id
860 and disp.status_code <> 'TERMINATED'
861 and ( disp.immediate_disposition_code not in ('NOT_RECEIVED','NA','NOT_REMOVED')
862 or
863 ( disp.immediate_disposition_code = 'NOT_RECEIVED'
864 and
865 disp.quantity > calculate_txned_qty(disp.disposition_id)
866 )
867 )
868 )
869 )
870 AND disp.inventory_item_id = mtl.inventory_item_id (+)
871 AND disp.organization_id = mtl.organization_id (+)
872 AND disp.ITEM_GROUP_ID = grp.item_group_id (+)
873 AND flvt1.lookup_type(+) = 'AHL_IMMED_DISP_TYPE'
874 AND flvt1.LOOKUP_CODE (+) = disp.immediate_disposition_code
875 AND flvt1.LANGUAGE(+) = userenv('LANG')
876 AND flvt2.lookup_type(+) = 'AHL_SECND_DISP_TYPE'
877 AND flvt2.lookup_code (+) = disp.secondary_disposition_code
878 AND flvt2.LANGUAGE(+) = userenv('LANG')
879 AND flvt3.lookup_type(+) = 'AHL_DISP_STATUS'
880 AND flvt3.lookup_code (+) = disp.STATUS_CODE
881 AND flvt3.LANGUAGE(+) = userenv('LANG') ;
882 --
883 --added by peter
884 -- get the total associated quantity based on the disposition id and material transaction id
885 CURSOR get_assoc_qty_csr(p_disp_id IN NUMBER, p_workorder_txn_id IN NUMBER)
886 IS
887 SELECT quantity, uom FROM AHL_PRD_DISP_MTL_TXNS
888 WHERE DISPOSITION_ID = p_disp_id -- 11016 -- 11044
889 AND WORKORDER_MTL_TXN_ID = p_workorder_txn_id;
890
891 --Added by Jerry for fixing bug 4092624
892 CURSOR check_issue_items(c_disposition_id NUMBER, c_inventory_item_id NUMBER, c_organization_id NUMBER) IS
893 SELECT 1
894 FROM ahl_prd_dispositions_b A
895 WHERE A.disposition_id = c_disposition_id
896 AND ((A.inventory_item_id = c_inventory_item_id
897 AND A.organization_id = c_organization_id)
898 OR EXISTS
899 (SELECT 1
900 FROM ahl_item_associations_b B
901 WHERE B.item_group_id = A.item_group_id
902 AND B.inventory_item_id = c_inventory_item_id
903 AND B.inventory_org_id = (SELECT master_organization_id
904 FROM mtl_parameters
905 WHERE organization_id = c_organization_id)
906 AND B.interchange_type_code IN ('1-WAY INTERCHANGEABLE','2-WAY INTERCHANGEABLE')));
907
908 l_dummy NUMBER;
909 l_assoc_qty NUMBER;
910 l_assoc_uom VARCHAR2(3);
911
912
913 l_api_version CONSTANT NUMBER := 1.0;
914 l_api_name CONSTANT VARCHAR2(30) := 'Get_Disp_For_Mtl_Txn';
915 l_txn_rec get_mtl_txn_csr%ROWTYPE;
916 l_disp_assoc_rec DISP_MTXN_ASSOC_REC_TYPE;
917 i NUMBER;
918 --
919 BEGIN
920 -- Standard start of API savepoint
921 SAVEPOINT Get_Mtl_Txn_Type_pvt;
922
923 -- Initialize Procedure return status to success
924 x_return_status := FND_API.G_RET_STS_SUCCESS;
925
926 -- Initialize message list if p_init_msg_list is set to TRUE
927 IF FND_API.To_Boolean(p_init_msg_list) THEN
928 FND_MSG_PUB.Initialize;
929 END IF;
930
934 CLOSE get_mtl_txn_csr;
931 OPEN get_mtl_txn_csr(p_wo_mtl_txn_id);
932 FETCH get_mtl_txn_csr INTO l_txn_rec;
933 IF (get_mtl_txn_csr%NOTFOUND) THEN
935 FND_MESSAGE.Set_Name('AHL','AHL_PRD_MTL_TXN_ID_INV');
936 FND_MESSAGE.Set_Token('MTL_TXN_ID', p_wo_mtl_txn_id);
937 FND_MSG_PUB.ADD;
938 RAISE FND_API.G_EXC_ERROR;
939 END IF;
940 CLOSE get_mtl_txn_csr;
941
942 --If return material transaction, fetch all matching disp for wo, mtl_txn
943 -- and disposition item with txn item.
944 IF (l_txn_rec.transaction_type_id = WIP_CONSTANTS.RETCOMP_TYPE) THEN
945 i:=0;
946 --dbms_output.put_line('Transaction is Return type i= ' || i);
947 OPEN get_disp_return_csr(p_wo_mtl_txn_id, l_txn_rec.workorder_id,
948 l_txn_rec.trackable_flag,
949 l_txn_rec.inventory_item_id, l_txn_rec.organization_id,
950 l_txn_rec.serial_number, l_txn_rec.lot_number);
951 LOOP
952 --dbms_output.put_line('before fetch i= ' || i);
953 FETCH get_disp_return_csr INTO l_disp_assoc_rec.DISPOSITION_ID,
954 l_disp_assoc_rec.INVENTORY_ITEM_ID,
955 l_disp_assoc_rec.ITEM_ORG_ID,
956 l_disp_assoc_rec.ITEM_NUMBER,
957 l_disp_assoc_rec.ITEM_GROUP_ID,
958 l_disp_assoc_rec.item_group_name,
959 l_disp_assoc_rec.serial_number,
960 l_disp_assoc_rec.lot_number,
961 l_disp_assoc_rec.immediate_disposition_code,
962 l_disp_assoc_rec.immediate_type,
963 l_disp_assoc_rec.secondary_disposition_code,
964 l_disp_assoc_rec.secondary_type,
965 l_disp_assoc_rec.status_code,
966 l_disp_assoc_rec.status,
967 l_disp_assoc_rec.quantity,
968 l_disp_assoc_rec.uom,
969 l_disp_assoc_rec.assoc_qty,
970 l_disp_assoc_rec.assoc_uom,
971 l_disp_assoc_rec.UNTXNED_QTY,
972 l_disp_assoc_rec.UNTXNED_UOM;
973 OPEN get_assoc_qty_csr(l_disp_assoc_rec.disposition_id, p_wo_mtl_txn_id );
974 FETCH get_assoc_qty_csr INTO l_assoc_qty, l_assoc_uom;
975 IF get_assoc_qty_csr%FOUND THEN
976 l_disp_assoc_rec.assoc_qty := l_assoc_qty;
977 l_disp_assoc_rec.assoc_uom := l_assoc_uom;
978 END IF;
979 CLOSE get_assoc_qty_csr;
980 --dbms_output.put_line('after fetch i= ' || i);
981 EXIT WHEN get_disp_return_csr%NOTFOUND;
982 x_disp_list_tbl(i):= l_disp_assoc_rec;
983 i:=i+1;
984 END LOOP;
985 CLOSE get_disp_return_csr;
986 ELSIF (l_txn_rec.transaction_type_id = WIP_CONSTANTS.ISSCOMP_TYPE) THEN
987 i:=0;
988 OPEN get_disp_issue_csr(p_wo_mtl_txn_id, l_txn_rec.workorder_id,
989 l_txn_rec.trackable_flag);
990 LOOP
991 FETCH get_disp_issue_csr INTO l_disp_assoc_rec;
992 EXIT WHEN get_disp_issue_csr%NOTFOUND;
993 OPEN check_issue_items(l_disp_assoc_rec.disposition_id,l_txn_rec.inventory_item_id, l_txn_rec.organization_id);
994 FETCH check_issue_items INTO l_dummy;
995 IF check_issue_items%FOUND THEN
996 OPEN get_assoc_qty_csr(l_disp_assoc_rec.disposition_id, p_wo_mtl_txn_id );
997 FETCH get_assoc_qty_csr INTO l_assoc_qty, l_assoc_uom;
998 IF get_assoc_qty_csr%FOUND THEN
999 l_disp_assoc_rec.assoc_qty := l_assoc_qty;
1000 l_disp_assoc_rec.assoc_uom := l_assoc_uom;
1001 END IF;
1002 CLOSE get_assoc_qty_csr;
1003 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1004 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1005 G_LOG_PREFIX||l_api_name||': End API',
1006 'disp_id = '||l_disp_assoc_rec.disposition_id||'item='||l_txn_rec.inventory_item_id||'org='||l_txn_rec.organization_id);
1007 END IF;
1008 x_disp_list_tbl(i):= l_disp_assoc_rec;
1009 i:=i+1;
1010 END IF;
1011 CLOSE check_issue_items;
1012 END LOOP;
1013 CLOSE get_disp_issue_csr;
1014
1015 END IF;
1016
1017 -- Standard call to get message count and if count is 1, get message info
1018 FND_MSG_PUB.Count_And_Get
1019 ( p_count => x_msg_count,
1020 p_data => x_msg_data,
1021 p_encoded => fnd_api.g_false
1022 );
1023
1024 EXCEPTION
1025 WHEN FND_API.G_EXC_ERROR THEN
1026 Rollback to Get_Mtl_Txn_Type_pvt;
1027 x_return_status := FND_API.G_RET_STS_ERROR;
1028 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1029 p_data => x_msg_data,
1030 p_encoded => fnd_api.g_false);
1031 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1032 Rollback to Get_Mtl_Txn_Type_pvt;
1033 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1034 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1035 p_data => x_msg_data,
1036 p_encoded => fnd_api.g_false);
1037 WHEN OTHERS THEN
1038 Rollback to Get_Mtl_Txn_Type_pvt;
1039 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1043 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1040 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1041 p_procedure_name => l_api_name,
1042 p_error_text => SQLERRM);
1044 p_data => x_msg_data,
1045 p_encoded => fnd_api.g_false);
1046 END Get_Disp_For_Mtl_Txn;
1047
1048 ----------------------
1049 -- Function name : Calculate_Txned_Qty
1050 -- Type : Private
1051 -- Function : Calculates the mtl transactions qtys txned for a disposition.
1052 -- Pre-reqs :
1053 -- Parameters :
1054 --
1055 -- Calculate_Txned_Qty parameters:
1056 -- p_disposition_id is the disposition_id
1057 -- Returns: qty of the mtl transaction that's assoc to disp. can be
1058 -- >0 or =0
1059 --
1060 -- End of Comments.
1061
1062 FUNCTION Calculate_Txned_Qty(
1063 p_disposition_id IN NUMBER)
1064 RETURN NUMBER
1065 IS
1066 --
1067 CURSOR get_remain_qty_csr(p_disp_id IN NUMBER)
1068 IS
1069 SELECT sum (assoc.quantity)
1070 FROM AHL_PRD_DISPOSITIONS_B disp, AHL_PRD_DISP_MTL_TXNS assoc,
1071 AHL_WORKORDER_MTL_TXNS mtxn
1072 WHERE disp.disposition_id = p_disp_id
1073 AND assoc.workorder_mtl_txn_id = mtxn.workorder_mtl_txn_id
1074 AND mtxn.transaction_type_id = decode (disp.immediate_disposition_code,'NOT_RECEIVED',WIP_CONSTANTS.ISSCOMP_TYPE,WIP_CONSTANTS.RETCOMP_TYPE)
1075 AND disp.disposition_id = assoc.disposition_id
1076 GROUP BY disp.disposition_id, assoc.disposition_id;
1077 --
1078 l_txn_qty NUMBER :=0;
1079 --
1080 BEGIN
1081 OPEN get_remain_qty_csr (p_disposition_id);
1082 FETCH get_remain_qty_csr INTO l_txn_qty;
1083 CLOSE get_remain_qty_csr;
1084 RETURN l_txn_qty;
1085 END Calculate_Txned_Qty;
1086
1087 ----------------------
1088 -- Function name : Calculate_Txned_Qty
1089 -- Type : Private
1090 -- Function : Calculates the mtl transactions qtys txned for a disposition.
1091 -- Pre-reqs :
1092 -- Parameters :
1093 --
1094 -- Get_Assoc_Quantity parameters:
1095 -- p_disposition_id is the disposition_id
1096 -- Returns: qty of the mtl transaction that's assoc to disp. can be
1097 -- >0 or =0
1098 --
1099 -- End of Comments.
1100
1101 FUNCTION Get_Assoc_Quantity(
1102 p_disposition_id IN NUMBER,
1103 p_workorder_txn_id IN NUMBER)
1104 RETURN NUMBER
1105 IS
1106 --
1107 CURSOR get_assoc_qty_csr(p_disp_id IN NUMBER, p_workorder_txn_id IN NUMBER)
1108 IS
1109 SELECT sum (assoc.quantity)
1110 FROM AHL_PRD_DISPOSITIONS_B disp, AHL_PRD_DISP_MTL_TXNS assoc,
1111 AHL_WORKORDER_MTL_TXNS mtxn
1112 WHERE disp.disposition_id = p_disp_id
1113 AND assoc.workorder_mtl_txn_id = p_workorder_txn_id
1114 AND assoc.workorder_mtl_txn_id = mtxn.workorder_mtl_txn_id
1115 AND mtxn.transaction_type_id = decode (disp.immediate_disposition_code,'NOT_RECEIVED',WIP_CONSTANTS.ISSCOMP_TYPE,WIP_CONSTANTS.RETCOMP_TYPE)
1116 AND disp.disposition_id = assoc.disposition_id
1117 GROUP BY disp.disposition_id, assoc.disposition_id;
1118 --
1119 l_txn_qty NUMBER :=0;
1120 --
1121 BEGIN
1122 OPEN get_assoc_qty_csr (p_disposition_id, p_workorder_txn_id);
1123 FETCH get_assoc_qty_csr INTO l_txn_qty;
1124 CLOSE get_assoc_qty_csr;
1125 RETURN l_txn_qty;
1126 END Get_Assoc_Quantity;
1127
1128 End AHL_PRD_DISP_MTL_TXN_PVT;