[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.3.12010000.2 2008/12/30 15:01:40 ybabulal 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 CURSOR inv_details IS
173 SELECT
174 inv.freight_code
175 , inv.transaction_type_id
176 , inv.transaction_action_id
177 , inv.transaction_id
178 , inv.organization_id
179 , inv.transfer_organization_id
180 , inv.transaction_uom
181 -- , inv.transaction_date timezone support donot populate transaction date again
182 , inv.primary_quantity
183 , inv.inventory_item_id
184 , si.description
185 , nvl(cst.item_cost,0)+decode(sign(transaction_quantity),-1,0,
186 nvl(inv.transfer_cost,0))
187 FROM
188 MTL_MATERIAL_TRANSACTIONS inv
189 , MTL_SYSTEM_ITEMS si
190 , CST_ITEM_COSTS_FOR_GL_VIEW cst
191 WHERE inv.organization_id = si.organization_id
192 AND inv.inventory_item_id = si.inventory_item_id
193 AND inv.organization_id = cst.organization_id(+)
194 AND inv.inventory_item_id = cst.inventory_item_id(+)
195 AND inv.transaction_id = x_movement_transaction.mtl_transaction_id;
196
197 BEGIN
198 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
199 THEN
200 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
201 , G_MODULE_NAME || l_procedure_name || '.begin'
202 ,'enter procedure'
203 );
204 END IF;
205
206 x_return_status := 'Y';
207
208 OPEN inv_details;
209 FETCH inv_details INTO
210 x_movement_transaction.delivery_terms
211 , x_movement_transaction.transaction_type_id
212 , x_movement_transaction.transaction_action_id
213 , x_movement_transaction.mtl_transaction_id
214 , x_movement_transaction.from_organization_id
215 , x_movement_transaction.to_organization_id
216 , x_movement_transaction.transaction_uom_code
217 -- , x_movement_transaction.transaction_date
218 , x_movement_transaction.primary_quantity
219 , x_movement_transaction.inventory_item_id
220 , x_movement_transaction.item_description
221 , x_movement_transaction.item_cost;
222
223 IF inv_details%NOTFOUND
224 THEN
225 CLOSE inv_details;
226 x_return_status := 'N';
227 RETURN;
228 END IF;
229
230 CLOSE inv_details;
231
232 --fix bug 2888046, interorg transfer should always be in functional currency
233 x_movement_transaction.currency_code := x_movement_transaction.gl_currency_code;
234 x_movement_transaction.currency_conversion_rate := 1;
235 x_movement_transaction.currency_conversion_type := null;
236 x_movement_transaction.currency_conversion_date := null;
237
238 x_movement_transaction.document_unit_price := x_movement_transaction.item_cost;
239 x_movement_transaction.document_line_ext_value := abs(x_movement_transaction.document_unit_price *
240 x_movement_transaction.transaction_quantity);
241 x_movement_transaction.document_source_type := 'INV';
242 x_movement_transaction.transaction_nature := '60';
243 x_movement_transaction.origin_territory_code := x_movement_transaction.dispatch_territory_code;
244
245 IF ((x_movement_transaction.transaction_type_id = 12 AND
246 x_movement_transaction.transaction_action_id = 12)
247 OR
248 (x_movement_transaction.transaction_type_id IN (2,3) AND
249 x_movement_transaction.transaction_action_id IN (2,3) AND
250 x_movement_transaction.transaction_quantity > 0))
251 THEN
252 x_movement_transaction.movement_type := 'A';
253 x_movement_transaction.from_organization_id :=
254 x_movement_transaction.to_organization_id;
255 x_movement_transaction.to_organization_id :=
256 x_movement_transaction.organization_id;
257 ELSE
258 x_movement_transaction.movement_type := 'D';
259 x_movement_transaction.transaction_quantity :=
260 abs(x_movement_transaction.transaction_quantity);
261 x_movement_transaction.primary_quantity :=
262 NVL(abs(x_movement_transaction.primary_quantity),null);
263 END IF;
264
265 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
266 THEN
267 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
268 , G_MODULE_NAME || l_procedure_name || '.end'
269 ,'exit procedure'
270 );
271 END IF;
272
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275 x_return_status := 'N';
276 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
277 THEN
278 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
279 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
280 , 'Exception'
281 );
282 END IF;
283 WHEN OTHERS THEN
284 x_return_status := 'N';
285 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
286 THEN
287 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
288 , G_MODULE_NAME || l_procedure_name||'. Others exception'
289 , 'Exception'
290 );
291 END IF;
292 END Get_INV_Details;
293
294 --========================================================================
295 -- PROCEDURE : Update_INV_Transactions PUBLIC
296 -- PARAMETERS: x_return_status return status
297 -- p_movement_transaction movement transaction record
298 -- COMMENT : Update the status of the transaction record to PROCESSED
299 --========================================================================
300
301 PROCEDURE Update_INV_Transactions
302 ( p_movement_transaction IN
303 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
304 , x_return_status OUT NOCOPY VARCHAR2
305 )
306 IS
307 l_procedure_name CONSTANT VARCHAR2(30) := 'Update_INV_Transactions';
308 BEGIN
309 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
310 THEN
311 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
312 , G_MODULE_NAME || l_procedure_name || '.begin'
313 ,'enter procedure'
314 );
315 END IF;
316
317 x_return_status := 'Y';
318 -- Update the transaction table
319 UPDATE MTL_MATERIAL_TRANSACTIONS
320 SET mvt_stat_status = 'PROCESSED'
321 , movement_id = p_movement_transaction.movement_id
322 WHERE transaction_id = p_movement_transaction.mtl_transaction_id;
323
324 COMMIT;
325
326 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
327 THEN
328 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
329 , G_MODULE_NAME || l_procedure_name || '.end'
330 ,'exit procedure'
331 );
332 END IF;
333
334 EXCEPTION
335 WHEN NO_DATA_FOUND THEN
336 x_return_status := 'N';
337 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
338 THEN
339 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
340 , G_MODULE_NAME || l_procedure_name||'. No data found exception'
341 , 'Exception'
342 );
343 END IF;
344 WHEN OTHERS THEN
345 x_return_status := 'N';
346 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
347 THEN
348 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
349 , G_MODULE_NAME || l_procedure_name||'. Others exception'
350 , 'Exception'
351 );
352 END IF;
353
354 END Update_INV_Transactions;
355
356 END INV_MGD_MVT_INV_MDTR;