[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_INV_MDTR
Source
1 PACKAGE BODY INV_MGD_MVT_INV_MDTR AS
2 -- $Header: INVIMDRB.pls 120.5 2011/08/10 12:54:33 abhissri ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVIMDRB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Body of INV_MGD_MVT_INV_MDTR |
13 --| |
14 --| PROCEDURE LIST |
15 --| Get_INV_Transactions |
16 --| Get_INV_Details |
17 --| Update_INV_Transactions |
18 --| |
19 --| HISTORY |
20 --| 07-Jan-2002 odaboval Bug 2169239 |
21 --| |
22 --| |
23 --| |
24 --+=======================================================================
25
26 --===================
27 -- CONSTANTS
28 --===================
29 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_INV_MDTR.';
30
31
32 --===================
33 -- PRIVATE PROCEDURES
34 --===================
35
36
37 --========================================================================
38 -- PROCEDURE : Get_INV_Transactions PUBLIC
39 -- PARAMETERS: inv_crsr REF cursor
40 -- x_return_status return status
41 -- p_start_date Transaction start date
42 -- p_end_date Transaction end date
43 -- COMMENT :
44 -- This opens the cursor for INV and returns the cursor.
45 --========================================================================
46
47 PROCEDURE Get_INV_Transactions
48 ( inv_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.invCurTyp
49 , p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
50 , p_start_date IN DATE
51 , p_end_date IN DATE
52 , x_return_status OUT NOCOPY VARCHAR2
53 )
54 IS
55 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_INV_Transactions';
56 BEGIN
57 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
58 THEN
59 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
60 , G_MODULE_NAME || l_procedure_name || '.begin'
61 ,'enter procedure'
62 );
63 END IF;
64
65 x_return_status := 'Y';
66
67 IF inv_crsr%ISOPEN THEN
68 CLOSE inv_crsr;
69 END IF;
70
71
72 --Fix performance bug 4912552, use hr_organization_information to replace
73 --org_organization_definitions according to proposal from INV
74 --karthik.gnanamurthy, because inventory organization is already existing
75 --in mtl_material_transactions, so it's not required to validate the organization
76 --again in mtl_parameters or hr_all_organization_units as OOD does
77
78 IF NVL(p_movement_transaction.creation_method,'A') = 'A' THEN
79
80 OPEN inv_crsr FOR
81 SELECT
82 inv.transaction_id
83 , inv.transaction_type_id
84 , inv.transaction_action_id
85 , inv.transfer_organization_id
86 , inv.transaction_date
87 , inv.organization_id
88 , inv.transaction_quantity
89 , inv.subinventory_code
90 , inv.transfer_subinventory
91 FROM
92 MTL_MATERIAL_TRANSACTIONS inv
93 , hr_organization_information hoi
94 WHERE inv.organization_id = hoi.organization_id
95 AND hoi.org_information_context = 'Accounting Information'
96 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
97 AND inv.transaction_type_id IN (21,3,12,2)
98 AND inv.transaction_action_id IN (21,3,12,2)
99 AND NVL(inv.mvt_stat_status,'NEW') = 'NEW'
100 AND inv.transaction_date BETWEEN p_start_date AND p_end_date;
101 ELSE
102
103 OPEN inv_crsr FOR
104 SELECT
105 inv.transaction_id
106 , inv.transaction_type_id
107 , inv.transaction_action_id
108 , inv.transfer_organization_id
109 , inv.transaction_date
110 , inv.organization_id
111 , inv.transaction_quantity
112 , inv.subinventory_code
113 , inv.transfer_subinventory
114 FROM
115 MTL_MATERIAL_TRANSACTIONS inv
116 , hr_organization_information hoi
117 WHERE inv.organization_id = hoi.organization_id
118 AND hoi.org_information_context = 'Accounting Information'
119 AND hoi.org_information2 = to_char(p_movement_transaction.entity_org_id) /* bug 7676431: Added to_char */
120 AND inv.transaction_id = p_movement_transaction.mtl_transaction_id
121 AND inv.transaction_type_id IN (21,3,12,2)
122 AND inv.transaction_action_id IN (21,3,12,2)
123 AND NVL(inv.mvt_stat_status,'NEW') = 'NEW';
124
125 END IF;
126
127 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
128 THEN
129 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
130 , G_MODULE_NAME || l_procedure_name || '.end'
131 ,'exit procedure'
132 );
133 END IF;
134
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 x_return_status := 'N';
138 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
139 THEN
140 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
141 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
142 , 'Exception'
143 );
144 END IF;
145 WHEN OTHERS THEN
146 x_return_status := 'N';
147 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
148 THEN
149 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
150 , G_MODULE_NAME || l_procedure_name||'. Others exception'
151 , 'Exception'
152 );
153 END IF;
154 END Get_INV_Transactions;
155
156
157 --========================================================================
158 -- PROCEDURE : Get_INV_Details PUBLIC
159 -- PARAMETERS: x_return_status return status
160 -- p_movement_transaction movement transaction record
161 -- COMMENT : Get all the additional data required for INV
162 --========================================================================
163
164 PROCEDURE Get_INV_Details
165 ( x_movement_transaction IN OUT NOCOPY
166 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
167 , x_return_status OUT NOCOPY VARCHAR2
168 )
169 IS
170 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_INV_Details';
171
172 --Bugfix 12844667: Removing CST_ITEM_COSTS_FOR_GL_VIEW since this table doesn't have
173 --data for process orgs.
174 CURSOR inv_details IS
175 SELECT
176 inv.freight_code
177 , inv.transaction_type_id
178 , inv.transaction_action_id
179 , inv.transaction_id
180 , inv.organization_id
181 , inv.transfer_organization_id
182 , inv.transaction_uom
183 -- , inv.transaction_date timezone support donot populate transaction date again
184 , inv.primary_quantity
185 , inv.inventory_item_id
186 , si.description
187 /*
188 , nvl(cst.item_cost,0)+decode(sign(transaction_quantity),-1,0,
189 nvl(inv.transfer_cost,0))
190 */
191 FROM
192 MTL_MATERIAL_TRANSACTIONS inv
193 , MTL_SYSTEM_ITEMS si
194 --, CST_ITEM_COSTS_FOR_GL_VIEW cst
195 WHERE inv.organization_id = si.organization_id
196 AND inv.inventory_item_id = si.inventory_item_id
197 --AND inv.organization_id = cst.organization_id(+)
198 --AND inv.inventory_item_id = cst.inventory_item_id(+)
199 AND inv.transaction_id = x_movement_transaction.mtl_transaction_id;
200
201 ---Bugfix 12844667: New cursor to get the cost from discrete costing table.
202 CURSOR c_item_cost IS
203 SELECT
204 item_cost
205 FROM
206 CST_ITEM_COSTS_FOR_GL_VIEW
207 WHERE organization_id = x_movement_transaction.from_organization_id
208 AND inventory_item_id = x_movement_transaction.inventory_item_id;
209
210 BEGIN
211 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
212 THEN
213 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
214 , G_MODULE_NAME || l_procedure_name || '.begin'
215 ,'enter procedure'
216 );
217 END IF;
218
219 x_return_status := 'Y';
220
221 OPEN inv_details;
222 FETCH inv_details INTO
223 x_movement_transaction.delivery_terms
224 , x_movement_transaction.transaction_type_id
225 , x_movement_transaction.transaction_action_id
226 , x_movement_transaction.mtl_transaction_id
227 , x_movement_transaction.from_organization_id
228 , x_movement_transaction.to_organization_id
229 , x_movement_transaction.transaction_uom_code
230 -- , x_movement_transaction.transaction_date
231 , x_movement_transaction.primary_quantity
232 , x_movement_transaction.inventory_item_id
233 , x_movement_transaction.item_description;
234 --Bugfix 12844667: Populating it after checking for process org.
235 --, x_movement_transaction.item_cost;
236
237 IF inv_details%NOTFOUND
238 THEN
239 CLOSE inv_details;
240 x_return_status := 'N';
241 RETURN;
242 END IF;
243
244 CLOSE inv_details;
245
246 --Bugfix 12844667: New logic to get item cost.
247 IF (INV_MGD_MVT_UTILS_PKG.Is_Process_Org(x_movement_transaction.from_organization_id) = TRUE) THEN
248 x_movement_transaction.item_cost := INV_MGD_MVT_UTILS_PKG.Get_Process_Item_Cost
249 ( p_org_id => x_movement_transaction.from_organization_id
250 , p_item_id => x_movement_transaction.inventory_item_id
251 , p_transaction_date => x_movement_transaction.transaction_date
252 );
253 ELSE
254 --Get item cost fix bug 4207119
255 OPEN c_item_cost;
256 FETCH c_item_cost INTO
257 x_movement_transaction.item_cost;
258 CLOSE c_item_cost;
259 END IF;
260
261 --fix bug 2888046, interorg transfer should always be in functional currency
262 x_movement_transaction.currency_code := x_movement_transaction.gl_currency_code;
263 x_movement_transaction.currency_conversion_rate := 1;
264 x_movement_transaction.currency_conversion_type := null;
265 x_movement_transaction.currency_conversion_date := null;
266
267 x_movement_transaction.document_unit_price := x_movement_transaction.item_cost;
268 x_movement_transaction.document_line_ext_value := abs(x_movement_transaction.document_unit_price *
269 x_movement_transaction.transaction_quantity);
270 x_movement_transaction.document_source_type := 'INV';
271 x_movement_transaction.transaction_nature := '60';
272 x_movement_transaction.origin_territory_code := x_movement_transaction.dispatch_territory_code;
273
274 IF ((x_movement_transaction.transaction_type_id = 12 AND
275 x_movement_transaction.transaction_action_id = 12)
276 OR
277 (x_movement_transaction.transaction_type_id IN (2,3) AND
278 x_movement_transaction.transaction_action_id IN (2,3) AND
279 x_movement_transaction.transaction_quantity > 0))
280 THEN
281 x_movement_transaction.movement_type := 'A';
282 x_movement_transaction.from_organization_id :=
283 x_movement_transaction.to_organization_id;
284 x_movement_transaction.to_organization_id :=
285 x_movement_transaction.organization_id;
286 ELSE
287 x_movement_transaction.movement_type := 'D';
288 x_movement_transaction.transaction_quantity :=
289 abs(x_movement_transaction.transaction_quantity);
290 x_movement_transaction.primary_quantity :=
291 NVL(abs(x_movement_transaction.primary_quantity),null);
292 END IF;
293
294 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
295 THEN
296 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
297 , G_MODULE_NAME || l_procedure_name || '.end'
298 ,'exit procedure'
299 );
300 END IF;
301
302 EXCEPTION
303 WHEN NO_DATA_FOUND THEN
304 x_return_status := 'N';
305 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
306 THEN
307 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
308 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
309 , 'Exception'
310 );
311 END IF;
312 WHEN OTHERS THEN
313 x_return_status := 'N';
314 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
315 THEN
316 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
317 , G_MODULE_NAME || l_procedure_name||'. Others exception'
318 , 'Exception'
319 );
320 END IF;
321 END Get_INV_Details;
322
323 --========================================================================
324 -- PROCEDURE : Update_INV_Transactions PUBLIC
325 -- PARAMETERS: x_return_status return status
326 -- p_movement_transaction movement transaction record
327 -- COMMENT : Update the status of the transaction record to PROCESSED
328 --========================================================================
329
330 PROCEDURE Update_INV_Transactions
331 ( p_movement_transaction IN
332 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
333 , x_return_status OUT NOCOPY VARCHAR2
334 )
335 IS
336 l_procedure_name CONSTANT VARCHAR2(30) := 'Update_INV_Transactions';
337 BEGIN
338 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
339 THEN
340 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
341 , G_MODULE_NAME || l_procedure_name || '.begin'
342 ,'enter procedure'
343 );
344 END IF;
345
346 x_return_status := 'Y';
347 -- Update the transaction table
348 UPDATE MTL_MATERIAL_TRANSACTIONS
349 SET mvt_stat_status = 'PROCESSED'
350 , movement_id = p_movement_transaction.movement_id
351 WHERE transaction_id = p_movement_transaction.mtl_transaction_id;
352
353 COMMIT;
354
355 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
356 THEN
357 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
358 , G_MODULE_NAME || l_procedure_name || '.end'
359 ,'exit procedure'
360 );
361 END IF;
362
363 EXCEPTION
364 WHEN NO_DATA_FOUND THEN
365 x_return_status := 'N';
366 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
367 THEN
368 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
369 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
370 , 'Exception'
371 );
372 END IF;
373 WHEN OTHERS THEN
374 x_return_status := 'N';
375 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
376 THEN
377 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
378 , G_MODULE_NAME || l_procedure_name||'. Others exception'
379 , 'Exception'
380 );
381 END IF;
382
383 END Update_INV_Transactions;
384
385 END INV_MGD_MVT_INV_MDTR;