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