eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GMF_LC_ADJ_TRANSACTIONS_PKG

Source


1 PACKAGE BODY GMF_LC_ADJ_TRANSACTIONS_PKG AS
2 /*  $Header: GMFLCATB.pls 120.4.12020000.3 2012/09/14 07:03:38 guobxu ship $ */
3 
4    /* define the structure, can be used in global */
5     TYPE transaction_type  IS RECORD (
6             parent_ship_line_id       NUMBER,
7             adjustment_num            NUMBER,
8             ship_header_id            NUMBER,
9             org_id                    NUMBER,
10             ship_line_group_id        NUMBER,
11             ship_line_id              NUMBER,
12             organization_id           NUMBER,
13             inventory_item_id         NUMBER,
14             prior_landed_cost         NUMBER,
15             new_landed_cost           NUMBER,
16             allocation_percent        NUMBER,
17             charge_line_type_id       NUMBER,
18             charge_line_type_code     VARCHAR2(30),
19             cost_acquisition_flag     VARCHAR2(1),
20             component_type            VARCHAR2(30),
21             component_name            VARCHAR2(30),
22             parent_table_name         VARCHAR2(30),
23             parent_table_id           NUMBER,
24             cost_cmpntcls_id          NUMBER,
25             cost_analysis_code        CM_ALYS_MST.COST_ANALYSIS_CODE%TYPE, -- bug14480540
26             transaction_date          DATE,
27             transaction_quantity      NUMBER,
28             transaction_uom_code      VARCHAR2(25),
29             primary_quantity          NUMBER,
30             primary_uom_code          VARCHAR2(25),
31             lc_adjustment_flag        NUMBER,
32             rcv_transaction_id        NUMBER,
33             rcv_transaction_type      VARCHAR2(25),
34             currency_code             VARCHAR2(15),
35             lc_ship_num               VARCHAR2(25),
36             lc_ship_line_num          NUMBER,
37             event_type                NUMBER,
38             event_source              VARCHAR2(25),
39             event_source_id           NUMBER,
40             lc_var_account_id         NUMBER,
41             lc_absorption_account_id  NUMBER);
42 
43     TYPE lc_adjustments_type IS REF CURSOR RETURN transaction_type;
44 
45     adjustments_row transaction_type;
46 
47     l_debug_level        PLS_INTEGER;
48     l_debug_level_none   PLS_INTEGER;
49     l_debug_level_low    PLS_INTEGER;
50     l_debug_level_medium PLS_INTEGER;
51     l_debug_level_high   PLS_INTEGER;
52 
53 /************************************************************************
54 NAME
55         Validate_Adjustments
56 
57 SYNOPSIS
58         Type       : Private
59         Function   : Validate LCM adjustments before inserting in
60                      gmf_lc_adj_transactions table
61 
62          Pre-reqs   : None
63          Parameters :
64                  IN : p_le_id     IN NUMBER
65 
66                 OUT : p_adjustment_row    IN OUT transaction_type
67                       p_validation_status IN OUT VARCHAR2
68                       p_return_status        OUT VARCHAR2
69 
70 
71 DESCRIPTION
72                Validate LC adjustments before insert into OPM tables
73 AUTHOR
74   LCM-OPM dev  04-Aug-2009, LCM-OPM Integration, bug 8889977
75   Prasadmarada 15-oct-2009 added code for prorate return to vendor, correction
76                transactions, bug 8933738, 8925152
77 
78 HISTORY
79 *************************************************************************/
80 
81 PROCEDURE Validate_Adjustments(p_le_id             IN NUMBER,
82                                p_adjustment_row    IN OUT NOCOPY transaction_type,
83                                p_validation_status IN OUT NOCOPY VARCHAR2,
84                                x_return_status        OUT NOCOPY VARCHAR2) IS
85 
86        -- Get the lcm flag and item id
87     CURSOR check_lcm_flag IS
88     SELECT NVL(pll.lcm_flag,'N'), NVL(pl.item_id,0)
89       FROM
90             po_line_locations_all pll,
91             po_lines_all pl,
92             rcv_transactions rt
93      WHERE
94             pll.line_location_id = rt.po_line_location_id
95        AND  pl.po_line_id        = rt.po_line_id
96        AND  pl.po_line_id        = pll.po_line_id
97        AND  rt.transaction_id    = p_adjustment_row.rcv_transaction_id;
98 
99         -- check cost component class id exists in component master table
100     CURSOR cur_costcptcls_exists(cp_cost_cmpntcls_id  NUMBER) IS
101     SELECT 1
102       FROM cm_cmpt_mst
103      WHERE cost_cmpntcls_id = cp_cost_cmpntcls_id;
104 
105       -- check analysis code exists in analysis table
106     CURSOR cur_analysis_cd_exists (cp_cost_analysis_code cm_alys_mst.cost_analysis_code%TYPE) IS
107     SELECT 1
108       FROM CM_ALYS_MST
109      WHERE cost_analysis_code = cp_cost_analysis_code;
110 
111     -- get the material cost component class id and analysis code from
112     -- material cost component table and fiscal policies table
113     CURSOR cur_get_costcptcls ( cp_le_id             NUMBER,
114                                 cp_inventory_item_id NUMBER,
115                                 cp_organization_id   NUMBER,
116                                 cp_date              DATE )  IS
117     SELECT  ccm.mtl_cmpntcls_id,
118             ccm.mtl_analysis_code,
119             1
120       FROM  cm_cmpt_mtl ccm
121      WHERE  ccm.legal_entity_id   = NVL(cp_le_id,ccm.legal_entity_id)
122        AND  ccm.inventory_item_id = NVL(cp_inventory_item_id,ccm.inventory_item_id)
123        AND  ccm.organization_id   = NVL(cp_organization_id,ccm.organization_id)
124        AND  cp_date BETWEEN ccm.eff_start_date AND ccm.eff_end_date
125        AND  ccm.delete_mark      = 0
126    UNION
127     SELECT  cm.mtl_cmpntcls_id,
128             cm.mtl_analysis_code,
129             2
130       FROM  cm_cmpt_mtl cm
131      WHERE  cm.legal_entity_id = NVL(cp_le_id, cm.legal_entity_id)
132        AND  cm.organization_id = NVL(cp_organization_id, cm.organization_id)
133        AND  cp_date BETWEEN cm.eff_start_date AND cm.eff_end_date
134        AND  cm.delete_mark     = 0
135        AND  cm.cost_category_id IN
136                     ( SELECT  category_id
137                         FROM  mtl_item_categories mic
138                        WHERE  mic.inventory_item_id = NVL(cp_inventory_item_id, mic.inventory_item_id)
139                          AND  mic.organization_id   = NVL(cp_organization_id, mic.organization_id)
140                      )
141    UNION
142     SELECT  gfp.mtl_cmpntcls_id,
143             gfp.mtl_analysis_code,
144             3
145       FROM  gmf_fiscal_policies gfp
146      WHERE  gfp.legal_entity_id = cp_le_id
147        AND  gfp.delete_mark = 0
148       ORDER BY 3;
149 
150      -- Get sub inventory type
151     CURSOR  cur_asset_inventory(cp_rcv_transaction_id NUMBER ) IS
152     SELECT  asset_inventory
153       FROM  mtl_secondary_inventories subinv,
154             mtl_material_transactions mmt,
155             rcv_transactions rt
156      WHERE  subinv.secondary_inventory_name = mmt.subinventory_code
157        AND  subinv.organization_id          = mmt.organization_id
158        AND  rt.transaction_id               = mmt.rcv_transaction_id
159        AND  rt.transaction_id               = cp_rcv_transaction_id;
160 
161     -- Get functional currency code
162     CURSOR  cur_func_curr_code (cp_org_id NUMBER ) IS
163     SELECT gl.currency_code
164     FROM gl_sets_of_books gl,
165         financials_system_parameters fsp
169 /*    -- Get PRIOR landed cost
166     WHERE gl.set_of_books_id = fsp.set_of_books_id
167     AND   org_id = cp_org_id;
168 
170     CURSOR c_prior_landed_cost (
171         p_parent_ship_line_id   NUMBER,
172         p_ship_header_id        NUMBER,
173         p_ship_line_group_id    NUMBER,
174         p_component_type        VARCHAR2,
175         p_component_name        VARCHAR2,
176         p_inventory_item_id     NUMBER,
177         p_rcv_transaction_id    NUMBER,
178         p_previous_adj_num      NUMBER)
179     IS
180     SELECT NVL(SUM(landed_cost),0) landed_cost
181     FROM gmf_lc_adj_headers_v glah
182     WHERE glah.parent_ship_line_id  = p_parent_ship_line_id
183     AND   glah.ship_header_id       = p_ship_header_id
184     AND   glah.ship_line_group_id   = p_ship_line_group_id
185     AND   glah.component_type       = p_component_type
186     AND   glah.component_name       = p_component_name
187     AND   glah.inventory_item_id    = p_inventory_item_id
188     AND   glah.rcv_transaction_id   = p_rcv_transaction_id
189     AND   glah.adjustment_num       = p_previous_adj_num;
190 */
191 
192    CURSOR get_rcv_trans(cp_transaction_id NUMBER)  IS
193    SELECT parent_transaction_id, primary_quantity, destination_type_code
194      FROM rcv_transactions
195     WHERE transaction_id = cp_transaction_id;
196 
197    l_parent_rt_id       NUMBER;
198    l_rcv_primary_qty    NUMBER;
199    l_new_landed_cost    NUMBER;
200    l_prorated_prior_lc  NUMBER;
201 
202    l_proc_name          CONSTANT VARCHAR2(100) := 'Validate_Adjustments';
203    l_lcm_flag           VARCHAR2(1);
204    l_inventory_item_id  NUMBER;
205    l_cost_cmpntcls_id   NUMBER;
206    l_analysis_code      CM_ALYS_MST.COST_ANALYSIS_CODE%TYPE; -- bug14480540
207    l_asset_inventory    NUMBER;
208    l_dummy              NUMBER;
209    l_dummy1             VARCHAR2(100);
210    l_destination_type_code  VARCHAR2(100);
211    l_exists             NUMBER;
212    l_previous_adj_num NUMBER := 0;
213 
214 BEGIN
215 
216     IF l_debug_level >= l_debug_level_medium THEN
217         fnd_file.put_line(fnd_file.log,'Entered Procedure: '||l_proc_name);
218     END IF;
219 
220     --  Initialize API return status to success
221     x_return_status := FND_API.G_RET_STS_SUCCESS;
222 
223     p_validation_status:='S';
224 
225     IF l_debug_level >= l_debug_level_high THEN
226        fnd_file.put_line(fnd_file.log,'Validating Rcv Ttransaction ID: ' ||p_adjustment_row.rcv_transaction_id ||
227                                                ' Adjustment Num: '||p_adjustment_row.adjustment_num ||
228                                                ' Shipment Header ID: ' ||p_adjustment_row.ship_header_id ||
229                                                ' Shipment Line ID: ' ||p_adjustment_row.ship_line_id);
230      END IF;
231 
232     -- Get functional currency code
233     OPEN cur_func_curr_code(p_adjustment_row.org_id);
234     FETCH cur_func_curr_code INTO p_adjustment_row.currency_code;
235     CLOSE cur_func_curr_code;
236 
237    /*  -- Get previous landed cost for actual adjustment lines
238     IF (p_adjustment_row.lc_adjustment_flag = 1 OR
239         p_adjustment_row.adjustment_num >0) THEN
240 
241         -- Get the previous adj_number for the current line
242         SELECT MAX(adjustment_num)
243         INTO  l_previous_adj_num
244         FROM  inl_allocations ia
245         WHERE ia.ship_header_id = p_adjustment_row.ship_header_id
246           AND ia.ship_line_id   = p_adjustment_row.ship_line_id
247           AND ia.adjustment_num < p_adjustment_row.adjustment_num;
248 
249         IF l_debug_level >= l_debug_level_low THEN
250             fnd_file.put_line(fnd_file.log,'Previous adj_num for the current line: ' || l_previous_adj_num);
251         END IF;
252 
253         -- Get the prior landed cost for the current line
254         OPEN c_prior_landed_cost(
255             p_adjustment_row.parent_ship_line_id,
256             p_adjustment_row.ship_header_id,
257             p_adjustment_row.ship_line_group_id,
258             p_adjustment_row.component_type,
259             p_adjustment_row.component_name,
260             p_adjustment_row.inventory_item_id,
261             p_adjustment_row.rcv_transaction_id,
262             l_previous_adj_num
263         );
264 
265         FETCH  c_prior_landed_cost INTO p_adjustment_row.prior_landed_cost;
266 
267         CLOSE c_prior_landed_cost;
268 
269         IF l_debug_level >= l_debug_level_low THEN
270             fnd_file.put_line(fnd_file.log,'Prior landed cost for current line: ' || p_adjustment_row.prior_landed_cost);
271         END IF;
272     END IF;  */
273 
274     IF p_adjustment_row.component_type = 'CHARGE' THEN
275           -- populate cost component class id and analysis code
276         IF p_adjustment_row.cost_cmpntcls_id IS NULL THEN
277             fnd_file.put_line(fnd_file.log,'Cost component class ID null for Charge type component');
278             p_validation_status:='F';
279             /* exit from the loop --change pmarada */
280         ELSIF p_adjustment_row.cost_analysis_code IS NULL THEN
281             fnd_file.put_line(fnd_file.log,'Cost Analysis code is null for Charge type component');
282             p_validation_status:='F'; /* exit from the loop --change pmarada */
283         END IF;
284 
285        /* IF (p_adjustment_row.lc_adjustment_flag = 0 OR
286             p_adjustment_row.adjustment_num = 0) THEN
287 
288             p_adjustment_row.prior_landed_cost := 0;
289 
290             IF l_debug_level >= l_debug_level_low THEN
291                 fnd_file.put_line(fnd_file.log,'Prior Landed Cost for Estimated Charge: ' || p_adjustment_row.prior_landed_cost);
292             END IF;
293         END IF;  */
294     ELSIF p_adjustment_row.component_type = 'ITEM PRICE' THEN
295         IF (p_adjustment_row.cost_cmpntcls_id IS NULL OR
296             p_adjustment_row.cost_analysis_code IS NULL) THEN
297 
298             IF l_debug_level >= l_debug_level_medium THEN
299                 fnd_file.put_line(fnd_file.log,'Open cursor to get Componet Class Id and Analysis Code from item materials');
300             END IF;
301            -- get cost componet class id and analysis code
302            OPEN cur_get_costcptcls(p_le_id,
303                                    p_adjustment_row.inventory_item_id,
304                                    p_adjustment_row.organization_id,
305                                    p_adjustment_row.transaction_date);
306            FETCH cur_get_costcptcls INTO l_cost_cmpntcls_id, l_analysis_code, l_dummy;
307            CLOSE cur_get_costcptcls;
308 
309             IF l_debug_level >= l_debug_level_medium THEN
310                 fnd_file.put_line(fnd_file.log,'Open cursor to get Cost Componet Class Id and Analysis Code from default fiscal policies');
311             END IF;
312 
313             IF p_adjustment_row.cost_cmpntcls_id IS NULL THEN
314                 p_adjustment_row.cost_cmpntcls_id := l_cost_cmpntcls_id;
315             END IF;
316 
317             IF p_adjustment_row.cost_analysis_code IS NULL THEN
318                p_adjustment_row.cost_analysis_code := l_analysis_code;
319             END IF;
320 
321             IF l_debug_level >= l_debug_level_low THEN
322                fnd_file.put_line(fnd_file.log,'Populate Cost component class ID for item Charge '||p_adjustment_row.cost_cmpntcls_id);
323               fnd_file.put_line(fnd_file.log,'Populate Cost analysis code for item Charge '||p_adjustment_row.cost_analysis_code);
324              END IF;
325 
326         END IF; /* end for cost component class */
327 
328         -- For ITEM PRICE, cost acquisition flag = I
329         IF p_adjustment_row.cost_acquisition_flag IS NULL THEN
330             p_adjustment_row.cost_acquisition_flag := 'I';
331         END IF;
332 
333         -- For ITEM PRICE, Get the Prior Landed Cost from PO
334         IF (p_adjustment_row.lc_adjustment_flag = 0 OR
335             p_adjustment_row.adjustment_num = 0) THEN
336             SELECT ABS(DECODE(p_adjustment_row.currency_code,rt.currency_code,
337                    NVL(rt.po_unit_price,0) * NVL(rt.source_doc_quantity,0) ,
338                    NVL(rt.po_unit_price,0) * NVL(rt.source_doc_quantity,0) * rt.currency_conversion_rate))
339             INTO  p_adjustment_row.prior_landed_cost
340             FROM  rcv_transactions rt
341             WHERE rt.transaction_id = p_adjustment_row.rcv_transaction_id;
342 
343             IF l_debug_level >= l_debug_level_low THEN
344                 fnd_file.put_line(fnd_file.log,'Prior Landed Cost for Estimated: ' || p_adjustment_row.prior_landed_cost);
345             END IF;
346         END IF;
347     END IF;  /* end for item price component type */
348 
349     IF l_debug_level >= l_debug_level_low THEN
350         fnd_file.put_line(fnd_file.log,'Start validation');
351     END IF;
352 
353     IF p_adjustment_row.new_landed_cost < 0 THEN
354        fnd_file.put_line(fnd_file.log,'New landed cost is less than zero. Then skipping to insert in transactions table');
355        p_validation_status:='F';
356     ELSIF p_adjustment_row.prior_landed_cost <0 THEN
357        fnd_file.put_line(fnd_file.log,'Prior landed cost amount is less than zero. Then skipping to insert in transactions table');
358        p_validation_status:='F';
359     ELSIF ((p_adjustment_row.new_landed_cost - p_adjustment_row.prior_landed_cost) = 0) THEN
360        fnd_file.put_line(fnd_file.log,'Adjustment amount is zero. Then skipping to insert in transactions table');
361        p_validation_status:='F';
362     ELSIF  p_adjustment_row.rcv_transaction_id <=0 THEN
363        fnd_file.put_line(fnd_file.log,'Invalid Rcv Transaction. Then skipping to insert in transactions table');
364        p_validation_status:='F';
365     ELSIF  p_adjustment_row.rcv_transaction_id > 0 THEN
366 
367         IF l_debug_level >= l_debug_level_medium THEN
368             fnd_file.put_line(fnd_file.log,'Checking lcm flag');
369         END IF;
370 
371         OPEN check_lcm_flag ;
372         FETCH check_lcm_flag INTO l_lcm_flag, l_inventory_item_id;
373         CLOSE check_lcm_flag;
374 
375         IF l_debug_level >= l_debug_level_medium THEN
376             fnd_file.put_line(fnd_file.log,'LCM Flag: ' || l_lcm_flag || 'Inventory Item Id' ||l_inventory_item_id);
377         END IF;
378 
379         IF l_lcm_flag = 'N' THEN
380             fnd_file.put_line(fnd_file.log,'PO Shipment is not LCM Enabled. then skipping to insert in transactions table');
381             p_validation_status:='F';
382         ELSIF  p_adjustment_row.inventory_item_id <> l_inventory_item_id THEN
386     END IF;
383            fnd_file.put_line(fnd_file.log,'Not matched Item. then skipping to insert in transactions table');
384            p_validation_status:='F';
385         END IF;
387 
388     IF l_debug_level >= l_debug_level_medium THEN
389         fnd_file.put_line(fnd_file.log,'Component Type: ' || p_adjustment_row.component_type);
390     END IF;
391 
392     IF (p_adjustment_row.cost_cmpntcls_id IS NOT NULL OR
393         p_adjustment_row.cost_analysis_code IS NOT NULL) THEN
394 
395         IF l_debug_level >= l_debug_level_medium THEN
396             fnd_file.put_line(fnd_file.log,'Validate Cost Component Class');
397         END IF;
398 
399          -- validate cost component class id, should exists in CM_CMPT_MST table
400         OPEN cur_costcptcls_exists(p_adjustment_row.cost_cmpntcls_id);
401         FETCH cur_costcptcls_exists INTO l_exists;
402             IF cur_costcptcls_exists%NOTFOUND THEN
403                 fnd_file.put_line(fnd_file.log,'Cost component class ID is not valid, not exists in cost components table');
404                 p_validation_status:='F';
405             END IF;
406         CLOSE cur_costcptcls_exists;
407 
408         IF l_debug_level >= l_debug_level_medium THEN
409             fnd_file.put_line(fnd_file.log,'Open cursor to validate Cost Analysis Code');
410         END IF;
411 
412         -- Validate cost analysis code, cost analysis code should exists in CM_ALYS_MST  table
413         OPEN cur_analysis_cd_exists (p_adjustment_row.cost_analysis_code);
414         FETCH cur_analysis_cd_exists INTO l_exists;
415             IF cur_analysis_cd_exists%NOTFOUND THEN
416                 fnd_file.put_line(fnd_file.log,'Cost Analysis code is not valid, not exists in cost components table');
417                 p_validation_status:='F';
418             END IF;
419         CLOSE cur_analysis_cd_exists;
420     END IF;
421 
422     IF l_debug_level >= l_debug_level_low THEN
423         fnd_file.put_line(fnd_file.log,'Transaction Type: ' || p_adjustment_row.rcv_transaction_type);
424     END IF;
425 
426     -- populate Event columns
427     IF l_debug_level >= l_debug_level_low THEN
428         fnd_file.put_line(fnd_file.log,'Define Event Types');
429     END IF;
430 
431     IF p_adjustment_row.rcv_transaction_type = 'DELIVER' THEN
432 
433          OPEN cur_asset_inventory (p_adjustment_row.rcv_transaction_id);
434          FETCH cur_asset_inventory INTO l_asset_inventory;
435          CLOSE cur_asset_inventory;
436 
437         IF l_debug_level >= l_debug_level_medium THEN
438             fnd_file.put_line(fnd_file.log,'Asset Inventory:' || l_asset_inventory);
439         END IF;
440             /* assest inventory is means assest sub inventory else expanse sub inventory */
441         IF l_asset_inventory  = 1 THEN
442             p_adjustment_row.event_type := 16;
443             p_adjustment_row.event_source := 'LC_ADJUST_DELIVER';
444         ELSE
445             p_adjustment_row.event_type := 17;
446             p_adjustment_row.event_source := 'LC_ADJUST_EXP_DELIVER';
447         END IF;
448 
449          /* prorate charges for deliver transaction, charges in lc_adj_headers view for the deliver trabsaction
450             are for the entire receive qty. these charges need to be prorated for each deliver transactions for parent
451             receipt transaction */
452              -- get the parent transaction id for deliver transaction
453             OPEN  get_rcv_trans(p_adjustment_row.rcv_transaction_id);
454             FETCH get_rcv_trans INTO l_parent_rt_id, l_dummy, l_dummy1;
455             CLOSE get_rcv_trans;
456 
457              -- get parent transaction primary qty
458             OPEN  get_rcv_trans(l_parent_rt_id);
459             FETCH get_rcv_trans INTO l_dummy, l_rcv_primary_qty, l_dummy1;
460             CLOSE get_rcv_trans;
461 
462              -- deliver transactions qty and receive transaction qty are not same then
463             IF (l_rcv_primary_qty <> p_adjustment_row.primary_quantity) THEN
464 
465               l_new_landed_cost := (p_adjustment_row.primary_quantity * p_adjustment_row.new_landed_cost)/nvl(l_rcv_primary_qty,1);
466 
467               IF l_debug_level >= l_debug_level_low THEN
468                 fnd_file.put_line(fnd_file.log,'Prorated deliver transaction prorated new landed cost: '||l_new_landed_cost ||
469                   'new landed cost: '||p_adjustment_row.new_landed_cost||'deliver trans qty: '||p_adjustment_row.primary_quantity||
470                   'parent trans qty: '||l_rcv_primary_qty);
471               END IF;
472               p_adjustment_row.new_landed_cost   := l_new_landed_cost;
473 
474                --prorate prior landed cost for deliver trans for actual LC adjustments
475               IF (p_adjustment_row.lc_adjustment_flag = 1 OR
476                   p_adjustment_row.adjustment_num > 0) THEN
477 
478                 l_prorated_prior_lc := (p_adjustment_row.primary_quantity * p_adjustment_row.prior_landed_cost)/nvl(l_rcv_primary_qty,1);
479 
480                 IF l_debug_level >= l_debug_level_low THEN
481                   fnd_file.put_line(fnd_file.log,'Prorated deliver transaction prorated prior landed cost: '||l_prorated_prior_lc ||
482                     'prior landed cost: '||p_adjustment_row.prior_landed_cost||'deliver trans qty: '||p_adjustment_row.primary_quantity||
483                     'parent trans qty: '||l_rcv_primary_qty);
484                 END IF;
485                 p_adjustment_row.prior_landed_cost := l_prorated_prior_lc;
486               END IF;
487 
488             END IF; -- end for deliver qty and receive qty not matched
489 
490         p_adjustment_row.event_source_id := p_adjustment_row.rcv_transaction_id;
491 
492     ELSIF (p_adjustment_row.rcv_transaction_type = 'RETURN TO VENDOR') THEN
493 
494         IF l_debug_level >= l_debug_level_medium THEN
495             fnd_file.put_line(fnd_file.log,'RCV Transaction type:' || p_adjustment_row.rcv_transaction_type);
496         END IF;
497             /* for Return to vendor transaction destination type is receiving */
498           p_adjustment_row.event_type := 15;
499           p_adjustment_row.event_source := 'LC_ADJUST_RECEIPT';
500           p_adjustment_row.event_source_id := p_adjustment_row.rcv_transaction_id;
501 
502          /* prorate charges for return to vendor transaction based on the parent transaction,
503             charges in lc_adj_headers view for the entire receive qty.
504             these return to vendor charges need to be prorated based on parent receipt transaction */
505 
506              -- get the parent transaction id for the return to vendor transaction
507             OPEN  get_rcv_trans(p_adjustment_row.rcv_transaction_id);
508             FETCH get_rcv_trans INTO l_parent_rt_id, l_dummy, l_dummy1;
509             CLOSE get_rcv_trans;
510 
511              -- get parent transaction primary qty
512             OPEN  get_rcv_trans(l_parent_rt_id);
513             FETCH get_rcv_trans INTO l_dummy, l_rcv_primary_qty, l_dummy1;
514             CLOSE get_rcv_trans;
515 
516              -- return to vendor transactions qty and parent receive transaction qty are not same then
517             IF (l_rcv_primary_qty <> p_adjustment_row.primary_quantity) THEN
518 
519               l_new_landed_cost := (p_adjustment_row.primary_quantity * p_adjustment_row.new_landed_cost)/nvl(l_rcv_primary_qty,1);
520 
521               IF l_debug_level >= l_debug_level_low THEN
522                 fnd_file.put_line(fnd_file.log,'Prorated return to vendor transaction. prorated new landed cost: '||l_new_landed_cost ||
523                   'new landed cost: '||p_adjustment_row.new_landed_cost||'return to vendor trans qty: '||p_adjustment_row.primary_quantity||
524                   'parent receive trans qty: '||l_rcv_primary_qty);
525               END IF;
526               p_adjustment_row.new_landed_cost   := l_new_landed_cost;
527 
528                --prorate prior landed cost for return to vendor trans for actual LC adjustments
529               IF (p_adjustment_row.lc_adjustment_flag = 1 OR
530                   p_adjustment_row.adjustment_num > 0) THEN
531 
532                 l_prorated_prior_lc := (p_adjustment_row.primary_quantity * p_adjustment_row.prior_landed_cost)/nvl(l_rcv_primary_qty,1);
533 
534                 IF l_debug_level >= l_debug_level_low THEN
535                   fnd_file.put_line(fnd_file.log,'Prorated return to vendor transaction. prorated prior landed cost: '||l_prorated_prior_lc ||
536                     'prior landed cost: '||p_adjustment_row.prior_landed_cost||'return to vendor trans qty: '||p_adjustment_row.primary_quantity||
537                     'parent receive trans qty: '||l_rcv_primary_qty);
538                 END IF;
539                 p_adjustment_row.prior_landed_cost := l_prorated_prior_lc;
540               END IF;
541 
542             END IF; -- end for return to vendor qty and receive qty not matched
543 
544     ELSIF p_adjustment_row.rcv_transaction_type = 'RETURN TO RECEIVING' THEN
545 
546          IF l_debug_level >= l_debug_level_medium THEN
547             fnd_file.put_line(fnd_file.log,'RCV Transaction type:' || p_adjustment_row.rcv_transaction_type);
548          END IF;
549          OPEN cur_asset_inventory (p_adjustment_row.rcv_transaction_id);
550          FETCH cur_asset_inventory INTO l_asset_inventory;
551          CLOSE cur_asset_inventory;
552 
553           IF l_debug_level >= l_debug_level_medium THEN
554             fnd_file.put_line(fnd_file.log,'Asset Inventory:' || l_asset_inventory);
555           END IF;
556               /* Return to receiving transaction destination type is deliver */
557               /* assest inventory is means assest sub inventory else expanse sub inventory */
558           IF l_asset_inventory  = 1 THEN
559              p_adjustment_row.event_type := 16;
560              p_adjustment_row.event_source := 'LC_ADJUST_DELIVER';
561           ELSE
562              p_adjustment_row.event_type := 17;
563              p_adjustment_row.event_source := 'LC_ADJUST_EXP_DELIVER';
564            END IF;
565 
566          /* prorate charges for Return to receiving transaction based on the parent deliver transaction,
567             charges in lc_adj_headers view for the entire deliver qty.
568             these Return to receiving transaction charges need to be prorated based on parent deliver transaction */
569 
570              -- get the parent transaction id for the return to receiving transaction
571             OPEN  get_rcv_trans(p_adjustment_row.rcv_transaction_id);
572             FETCH get_rcv_trans INTO l_parent_rt_id, l_dummy, l_dummy1;
573             CLOSE get_rcv_trans;
574 
575              -- get parent transaction primary qty
576             OPEN  get_rcv_trans(l_parent_rt_id);
577             FETCH get_rcv_trans INTO l_dummy, l_rcv_primary_qty, l_dummy1;
578             CLOSE get_rcv_trans;
579 
580              -- return to receiving transactions qty and parent deliver transaction qty are not same then
581             IF (l_rcv_primary_qty <> p_adjustment_row.primary_quantity) THEN
582 
583               l_new_landed_cost := (p_adjustment_row.primary_quantity * p_adjustment_row.new_landed_cost)/nvl(l_rcv_primary_qty,1);
584 
585               IF l_debug_level >= l_debug_level_low THEN
586                 fnd_file.put_line(fnd_file.log,'Prorated return to receiving transaction. prorated new landed cost: '||l_new_landed_cost ||
587                   'new landed cost: '||p_adjustment_row.new_landed_cost||'return to receiving trans qty: '||p_adjustment_row.primary_quantity||
588                   'parent deliver trans qty: '||l_rcv_primary_qty);
589               END IF;
590               p_adjustment_row.new_landed_cost   := l_new_landed_cost;
591 
592                --prorate prior landed cost for return to receiving trans for actual LC adjustments
593               IF (p_adjustment_row.lc_adjustment_flag = 1 OR
594                   p_adjustment_row.adjustment_num > 0) THEN
595 
596                 l_prorated_prior_lc := (p_adjustment_row.primary_quantity * p_adjustment_row.prior_landed_cost)/nvl(l_rcv_primary_qty,1);
597 
598                 IF l_debug_level >= l_debug_level_low THEN
599                   fnd_file.put_line(fnd_file.log,'Prorated return to receiving transaction. prorated prior landed cost: '||l_prorated_prior_lc ||
600                     'prior landed cost: '||p_adjustment_row.prior_landed_cost||'return to receiving trans qty: '||p_adjustment_row.primary_quantity||
601                     'parent deliver trans qty: '||l_rcv_primary_qty);
602                 END IF;
603                 p_adjustment_row.prior_landed_cost := l_prorated_prior_lc;
604               END IF;
605 
606             END IF; -- end for return to receiving qty and receive qty not matched
607 
608         p_adjustment_row.event_source_id := p_adjustment_row.rcv_transaction_id;
609 
610     ELSIF p_adjustment_row.rcv_transaction_type = 'CORRECT' THEN
611 
612          --get the parent transaction id, destination type for the correct transaction
613          OPEN  get_rcv_trans(p_adjustment_row.rcv_transaction_id);
614          FETCH get_rcv_trans INTO l_parent_rt_id, l_dummy, l_destination_type_code;
615          CLOSE get_rcv_trans;
616          IF l_destination_type_code = 'INVENTORY' THEN
617 
618             IF l_debug_level >= l_debug_level_medium THEN
619               fnd_file.put_line(fnd_file.log,'RCV Transaction type:' || p_adjustment_row.rcv_transaction_type
620                                            ||'Destination type: '||l_destination_type_code);
621             END IF;
622             OPEN cur_asset_inventory (p_adjustment_row.rcv_transaction_id);
623             FETCH cur_asset_inventory INTO l_asset_inventory;
624             CLOSE cur_asset_inventory;
625 
626               IF l_debug_level >= l_debug_level_medium THEN
627                  fnd_file.put_line(fnd_file.log,'Asset Inventory:' || l_asset_inventory);
628               END IF;
629                /* Correct transaction destination type is inventory */
630                /* assest inventory is means assest sub inventory else expanse sub inventory */
631               IF l_asset_inventory  = 1 THEN
632                  p_adjustment_row.event_type := 16;
633                  p_adjustment_row.event_source := 'LC_ADJUST_DELIVER';
634               ELSE
635                  p_adjustment_row.event_type := 17;
636                  p_adjustment_row.event_source := 'LC_ADJUST_EXP_DELIVER';
637               END IF;
638          ELSE  -- destination type receive
639              IF l_debug_level >= l_debug_level_medium THEN
640                fnd_file.put_line(fnd_file.log,'RCV Transaction type:' || p_adjustment_row.rcv_transaction_type
641                                            ||'Destination type: '||l_destination_type_code);
642              END IF;
643                p_adjustment_row.event_type := 15;
644                p_adjustment_row.event_source := 'LC_ADJUST_RECEIPT';
645          END IF;
646 
647          /* prorate charges for correct transaction based on the parent transaction,
648             charges in lc_adj_headers view for the entire parent transaction qty.
649             these correct transaction charges need to be prorated based on parent transaction qty */
650 
651              -- get parent transaction primary qty
652             OPEN  get_rcv_trans(l_parent_rt_id);
653             FETCH get_rcv_trans INTO l_dummy, l_rcv_primary_qty, l_dummy1;
654             CLOSE get_rcv_trans;
655 
656              -- Correct transactions qty and parent transaction qty are not same then
657             IF (l_rcv_primary_qty <> p_adjustment_row.primary_quantity) THEN
658 
659               l_new_landed_cost := (p_adjustment_row.primary_quantity * p_adjustment_row.new_landed_cost)/nvl(l_rcv_primary_qty,1);
660 
661               IF l_debug_level >= l_debug_level_low THEN
662                 fnd_file.put_line(fnd_file.log,'Prorated Correct transaction. prorated new landed cost: '||l_new_landed_cost ||
663                   'new landed cost: '||p_adjustment_row.new_landed_cost||'correct trans qty: '||p_adjustment_row.primary_quantity||
664                   'parent trans qty: '||l_rcv_primary_qty);
665               END IF;
666               p_adjustment_row.new_landed_cost   := l_new_landed_cost;
667 
668                --prorate prior landed cost for correct trans for actual LC adjustments
669               IF (p_adjustment_row.lc_adjustment_flag = 1 OR
670                   p_adjustment_row.adjustment_num > 0) THEN
671 
672                 l_prorated_prior_lc := (p_adjustment_row.primary_quantity * p_adjustment_row.prior_landed_cost)/nvl(l_rcv_primary_qty,1);
673 
674                 IF l_debug_level >= l_debug_level_low THEN
675                   fnd_file.put_line(fnd_file.log,'Prorated correct transaction. prorated prior landed cost: '||l_prorated_prior_lc ||
676                     'prior landed cost: '||p_adjustment_row.prior_landed_cost||'correct trans qty: '||p_adjustment_row.primary_quantity||
677                     'parent trans qty: '||l_rcv_primary_qty);
678                 END IF;
679                 p_adjustment_row.prior_landed_cost := l_prorated_prior_lc;
680               END IF;
681 
682             END IF; -- end for qty not matched
683 
684          p_adjustment_row.event_source_id := p_adjustment_row.rcv_transaction_id;
685 
686     ELSE
687         IF l_debug_level >= l_debug_level_low THEN
688             fnd_file.put_line(fnd_file.log,'RCV Transaction Type:'|| p_adjustment_row.rcv_transaction_type);
689         END IF;
690         p_adjustment_row.event_type := 15;
691         p_adjustment_row.event_source := 'LC_ADJUST_RECEIPT';
692         p_adjustment_row.event_source_id := p_adjustment_row.rcv_transaction_id;
693     END IF;
694 
695     IF l_debug_level >= l_debug_level_medium THEN
696         fnd_file.put_line(fnd_file.log,'Event Type:' || p_adjustment_row.event_type);
697         fnd_file.put_line(fnd_file.log,'Event Source:' || p_adjustment_row.event_source);
698         fnd_file.put_line(fnd_file.log,'Event Source Id:' || p_adjustment_row.event_source_id);
699     END IF;
700 
701     IF l_debug_level >= l_debug_level_medium THEN
702         fnd_file.put_line(fnd_file.log,'Leaving Procedure: '||l_proc_name);
703     END IF;
704 
705 EXCEPTION
706     WHEN OTHERS THEN
707         IF l_debug_level >= l_debug_level_medium THEN
708             fnd_file.put_line(fnd_file.log,'Error: '||SQLERRM);
709         END IF;
710         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711         p_validation_status := 'F';
712 
713 END validate_adjustments;
714 
715 /************************************************************************
716 NAME
717         Create_AdjTrxLines
718 
719 SYNOPSIS
720         Type       : Private
721         Function   : Create landed cost adjustment transactions line
722                      in gmf_lc_adj_transactions table after validation
723 
724          Pre-reqs   : None
725          Parameters :
726                  IN : p_le_id     IN NUMBER
727                       p_ledger_id IN NUMBER
728                       p_adj_line  IN transaction_type
729 
730                 OUT : x_return_status  OUT NOCOPY VARCHAR2
731 
732 
733 DESCRIPTION
734               Insert Landed cost adjustment transaction in gmf_lc_adj_transactions
735               table
736 AUTHOR
737   LCM-OPM dev 04-Aug-2009, LCM-OPM Integration, bug
738 
739 HISTORY
740 *************************************************************************/
741 
742 PROCEDURE Create_AdjTrxLines(p_le_id          IN NUMBER,
743                              p_ledger_id      IN NUMBER,
744                              p_adjustment_row IN transaction_type,
745                              x_return_status  OUT NOCOPY VARCHAR2) IS
746 
747 l_proc_name VARCHAR2(40) := 'Process_Lc_Adjustments';
748 
749 BEGIN
750 
751     --  Initialize API return status to success
752     x_return_status := FND_API.G_RET_STS_SUCCESS;
753 
754     IF l_debug_level >= l_debug_level_medium THEN
755         fnd_file.put_line(fnd_file.log,'Entered Procedure: '||l_proc_name);
756     END IF;
757 
758     INSERT INTO gmf_lc_adj_transactions
759                          (adj_transaction_id,     -- 01
760                           rcv_transaction_id,     -- 02
761                           event_type,             -- 03
762                           event_source,           -- 04
763                           event_source_id,        -- 05
764                           ledger_id,              -- 06
765                           org_id,                 -- 07
766                           inventory_item_id,      -- 08
767                           organization_id,        -- 09
768                           legal_entity_id,        -- 10
769                           parent_ship_line_id,    -- 11
770                           ship_header_id,         -- 12
771                           ship_line_group_id,     -- 13
772                           ship_line_id,           -- 14
773                           adjustment_num,         -- 15
774                           parent_table_name,      -- 16
775                           parent_table_id,        -- 17
776                           prior_landed_cost,      -- 18
777                           new_landed_cost,        -- 19
778                           charge_line_type_id,    -- 20
779                           charge_line_type_code,  -- 21
780                           cost_acquisition_flag,  -- 22
781                           component_type,         -- 23
782                           component_name,         -- 24
783                           cost_cmpntcls_id,       -- 25
784                           cost_analysis_code,     -- 26
785                           lc_adjustment_flag,     -- 27
786                           transaction_date,       -- 28
787                           transaction_quantity,   -- 29
788                           transaction_uom_code,   -- 30
789                           primary_quantity,       -- 31
790                           primary_uom_code,       -- 32
791                           currency_code,          -- 33
792                           /*currency_conversion_type, -- 34
793                           currency_conversion_rate, -- 35
794                           currency_conversion_date, -- 36
795                           */
796                           lc_ship_num,              -- 37
797                           lc_ship_line_num,         -- 38
798                           lc_var_account_id,        -- 39
799                           lc_absorption_account_id, -- 40
800                           accounted_flag,           -- 41
801                           final_posting_date,       -- 42
802                           creation_date,            -- 43
803                           created_by,               -- 44
804                           last_update_date,         -- 45
805                           last_updated_by,          -- 46
806                           last_update_login,        -- 47
807                           request_id,               -- 48
808                           program_application_id,   -- 49
809                           program_id,               -- 50
810                           program_udpate_date)      -- 51
811                           VALUES
812                           (gmf_lc_adj_transactions_s.NEXTVAL,        -- 01
813                            p_adjustment_row.rcv_transaction_id,      -- 02
814                            p_adjustment_row.event_type,              -- 03
815                            p_adjustment_row.event_source,            -- 04
816                            p_adjustment_row.event_source_id,         -- 05
817                            p_ledger_id,                              -- 06
818                            p_adjustment_row.org_id,                  -- 07
819                            p_adjustment_row.inventory_item_id,       -- 08
820                            p_adjustment_row.organization_id,         -- 09
821                            p_le_id,                                  -- 10
822                            p_adjustment_row.parent_ship_line_id,     -- 11
823                            p_adjustment_row.ship_header_id,          -- 12
824                            p_adjustment_row.ship_line_group_id,      -- 13
825                            p_adjustment_row.ship_line_id,            -- 14
826                            p_adjustment_row.adjustment_num,          -- 15
827                            p_adjustment_row.parent_table_name,       -- 16
828                            p_adjustment_row.parent_table_id,         -- 17
829                            p_adjustment_row.prior_landed_cost,       -- 18
830                            p_adjustment_row.new_landed_cost,         -- 19
831                            p_adjustment_row.charge_line_type_id,     -- 20
832                            p_adjustment_row.charge_line_type_code,   -- 21
833                            p_adjustment_row.cost_acquisition_flag,   -- 22
834                            p_adjustment_row.component_type,          -- 23
835                            p_adjustment_row.component_name,          -- 24
836                            p_adjustment_row.cost_cmpntcls_id,        -- 25
837                            p_adjustment_row.cost_analysis_code,      -- 26
838                            p_adjustment_row.lc_adjustment_flag,      -- 27
839                            p_adjustment_row.transaction_date,        -- 28
840                            p_adjustment_row.transaction_quantity,    -- 29
841                            p_adjustment_row.transaction_uom_code,    -- 30
842                            p_adjustment_row.primary_quantity,        -- 31
843                            p_adjustment_row.primary_uom_code,        -- 32
844                            p_adjustment_row.currency_code,             -- 33
845                            /*
846                            p_adjustment_row.currency_conversion_type,  -- 34
847                            p_adjustment_row.currency_conversion_rate,  -- 35
848                            p_adjustment_row.currency_conversion_date,  -- 36
849                            */
850                            p_adjustment_row.lc_ship_num,               -- 37
851                            p_adjustment_row.lc_ship_line_num,          -- 38
852                            p_adjustment_row.lc_var_account_id,         -- 39
853                            p_adjustment_row.lc_absorption_account_id ,  -- 40
854                            'N',                                        -- 41
855                            NULL,                                       -- 42
856                            SYSDATE,                                    -- 43
857                            fnd_global.user_id,                         -- 44
858                            SYSDATE,                                    -- 45
859                            fnd_global.user_id,                         -- 46
860                            0,                                          -- 47
861                            fnd_global.conc_request_id,                 -- 48
862                            fnd_global.conc_program_id,                 -- 49
863                            fnd_global.prog_appl_id,                    -- 50
864                            SYSDATE                                     -- 51
865                           );
866 
867     IF l_debug_level >= l_debug_level_medium THEN
868         fnd_file.put_line(fnd_file.log,'Leaving Procedure: '||l_proc_name);
869     END IF;
870 --
871 EXCEPTION
872     WHEN OTHERS THEN
873         IF l_debug_level >= l_debug_level_medium THEN
874             fnd_file.put_line(fnd_file.log,'Error: '||SQLERRM);
875             fnd_file.put_line(fnd_file.log,'Failed to insert into adjsutment transactions table in '||l_proc_name);
876         END IF;
877         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
878 END Create_AdjTrxLines;
879 
880 /************************************************************************
881 NAME
882         Process_Lc_Adjustments
883 
884 SYNOPSIS
885         Type       : Public
886         Function   : This is called by a concurrent program
887                      to import LCM adjustments into OPM tables
888 
889          Pre-reqs   : None
890          Parameters :
891                  IN   p_le_id                  IN NUMBER
892                       p_from_organization_id   IN NUMBER
893                       p_to_organization_id     IN NUMBER
894                       p_from_inventory_item_id IN NUMBER
895                       p_to_inventory_item_id   IN NUMBER
896                       p_start_date             IN DATE
897                       p_end_date               IN DATE
898                 OUT   errbuf          OUT NOCOPY VARCHAR2
899                       retcode         OUT NOCOPY VARCHAR2
900 
901 DESCRIPTION
902             Use this program to import LCM adjustments into OPM tables
903 AUTHOR
904   LCM-OPM dev 04-Aug-2009, LCM-OPM Integration, bug 8642337
905   15-mar-2012, bug13717189, modified the not exists condition, added decode
906   while checking the record exists
907 HISTORY
908 *************************************************************************/
909 
910 PROCEDURE Process_Lc_Adjustments
911         ( errbuf                    OUT NOCOPY VARCHAR2,
912           retcode                   OUT NOCOPY VARCHAR2,
913           p_le_id                    IN NUMBER,
914           p_from_organization_id     IN NUMBER,
915           p_to_organization_id       IN NUMBER,
916           p_from_inventory_item_id   IN NUMBER,
917           p_to_inventory_item_id     IN NUMBER,
918           p_start_date               IN VARCHAR2 ,
919           p_end_date                 IN VARCHAR2) IS
920 
921 
922         -- Load LC adjustments
923     CURSOR lc_adjustments_cur (cp_le_id        NUMBER,
924                                cp_from_org_cd  mtl_parameters.organization_code%TYPE,
925                                cp_to_org_cd    mtl_parameters.organization_code%TYPE,
926                                cp_from_item    mtl_item_flexfields.item_number%TYPE,
927                                cp_to_item      mtl_item_flexfields.item_number%TYPE,
928                                cp_start_dt     DATE,
929                                cp_end_dt       DATE) IS
930     SELECT  glah.parent_ship_line_id,
931             glah.adjustment_num,
932             glah.ship_header_id,
933             glah.org_id,
934             glah.ship_line_group_id,
935             glah.ship_line_id,
936             glah.organization_id,
937             glah.inventory_item_id,
938             NVL(glah.prior_landed_cost,0) AS prior_landed_cost,
939             NVL(glah.landed_cost,0) AS new_landed_cost,
940             glah.allocation_percent,
941             glah.charge_line_type_id,
942             glah.charge_line_type_code,
943             glah.cost_acquisition_flag,
944             glah.component_type,
945             glah.component_name,
946             glah.parent_table_name,
947             glah.parent_table_id,
948             glah.cost_cmpntcls_id,
949             glah.cost_analysis_code,
950             glah.transaction_date,
951             NVL(glah.transaction_quantity,0) AS transaction_quantity,
952             glah.transaction_uom_code,
953             NVL(glah.primary_quantity,0) AS primary_quantity,
954             glah.primary_uom_code,
955             glah.lc_adjustment_flag,
956             glah.rcv_transaction_id,
957             glah.rcv_transaction_type,
958             glah.lc_ship_num       lc_ship_num,
959             glah.lc_ship_line_num  lc_ship_line_num,
960             mp.lcm_enabled_flag,
961             mp.lcm_var_account      lc_var_account_id,
962             rp.lcm_account_id       lc_absorption_account_id
963       FROM
964             gmf_lc_adj_headers_v  glah,
965             org_organization_definitions ood,
966             mtl_parameters mp,
967             rcv_parameters rp,
968             mtl_item_flexfields mif
969      WHERE
970             mp.organization_id      =  glah.organization_id
971        AND  ood.legal_entity        =  cp_le_id
972        AND  mp.process_enabled_flag =  'Y'
973        AND  mp.lcm_enabled_flag     =  'Y'
974        AND  ood.organization_id     =  glah.organization_id
975        AND  mp.organization_id      =  glah.organization_id
976        AND  rp.organization_id      =  glah.organization_id
977        AND  mp.organization_code   >=  NVL(cp_from_org_cd, mp.organization_code)
978        AND  mp.organization_code   <=  NVL(cp_to_org_cd, mp.organization_code)
979        AND  glah.inventory_item_id  =  mif.inventory_item_id
980        AND  glah.organization_id    =  mif.organization_id
981        AND  mif.item_number        >=  NVL(cp_from_item, mif.item_number)
982        AND  mif.item_number        <=  NVL(cp_to_item, mif.item_number)
983        AND  TRUNC(glah.transaction_date) >= TRUNC(NVL(cp_start_dt, glah.transaction_date))
984        AND  TRUNC(glah.transaction_date) <= TRUNC(NVL(cp_end_dt, glah.transaction_date))
985        AND  glah.component_type IN ('ITEM PRICE','CHARGE')
986      --  AND  NVL(glah.new_landed_cost,0) - NVL(glah.prior_landed_cost,0) <> 0
987        AND  NOT EXISTS (SELECT 1 FROM gmf_lc_adj_transactions lat
988                          WHERE lat.adjustment_num     = glah.adjustment_num
989                            AND lat.ship_header_id     = glah.ship_header_id
990                            AND lat.ship_line_id       = glah.ship_line_id
991                            AND lat.rcv_transaction_id = glah.rcv_transaction_id
992                            AND lat.component_type     = glah.component_type
993 			   AND  DECODE(lat.component_type,'CHARGE', lat.charge_line_type_code, 'ITEM PRICE', lat.component_name, lat.component_name) =
994                                  DECODE(glah.component_type,'CHARGE', glah.charge_line_type_code, 'ITEM PRICE', glah.component_name, glah.component_name )
995                            AND lat.legal_entity_id    = cp_le_id)
996        ORDER BY glah.rcv_transaction_id, glah.adjustment_num;
997 
998      -- get legal entity name
999      CURSOR  cur_get_le IS
1000      SELECT  legal_entity_name
1001        FROM  gmf_legal_entities
1002       WHERE  legal_entity_id = p_le_id;
1003 
1004         -- Get organization code
1005      CURSOR  cur_get_org_cd (cp_organization_id NUMBER) IS
1006      SELECT  organization_code
1007        FROM  mtl_parameters
1008       WHERE  organization_id = cp_organization_id ;
1009 
1010         -- Get item number
1011      CURSOR  cur_get_item (cp_inventory_item_id  NUMBER) IS
1012      SELECT  item_number
1013        FROM  mtl_item_flexfields
1014       WHERE  inventory_item_id = cp_inventory_item_id
1015         AND  rownum < 2;
1016 
1017      -- B9917469, Removing OPM_LCM dependency
1018      CURSOR  cur_patch_level IS
1019      SELECT 1
1020        FROM fnd_product_installations
1021       WHERE application_id = 555 -- GMF product
1022         AND patch_level = 'R12.GMF.B.1' -- This indicates that installed GMF product level is 12.1.1
1023       ;
1024 
1025      lc_adjustments lc_adjustments_type;
1026 
1030      l_from_org_code  mtl_parameters.organization_code%TYPE := NULL;
1027      l_proc_name VARCHAR2(40) := 'Process_Lc_Adjustments';
1028      l_return_status VARCHAR2(1) ;
1029 
1031      l_to_org_code    mtl_parameters.organization_code%TYPE := NULL;
1032 
1033      l_from_item      mtl_item_flexfields.item_number%TYPE := NULL;
1034      l_to_item        mtl_item_flexfields.item_number%TYPE := NULL;
1035 
1036      l_le_id                 NUMBER;
1037      l_le_name               gmf_legal_entities.legal_entity_name%TYPE;
1038      l_from_organization_id  NUMBER;
1039      l_to_organization_id    NUMBER;
1040      l_from_item_id          NUMBER;
1041      l_to_item_id            NUMBER;
1042      l_start_date            DATE := NULL;
1043      l_end_date              DATE := NULL;
1044      l_ledger_id             NUMBER;
1045 
1046      l_tmp  BOOLEAN;
1047 
1048      l_ret_status       VARCHAR2(1);
1049      l_adjustment_row   transaction_type;
1050      l_total_lines      NUMBER := 0;
1051      l_total_adj        NUMBER := 0;
1052      l_total_ln_error   NUMBER := 0;
1053      l_patch_level      NUMBER := 0;    /* B9917469 */
1054   x_enable_opm_lcm_flag VARCHAR2(1) := NVL(FND_PROFILE.VALUE('INV_ENABLE_OPM_LCM'),'N'); -- 11781472
1055 
1056 BEGIN
1057 
1058     l_debug_level_none     := 0;
1059     l_debug_level_low      := 1;
1060     l_debug_level_medium   := 2;
1061     l_debug_level_high     := 3;
1062 
1063     l_tmp := TRUE;
1064     l_debug_level := TO_NUMBER(FND_PROFILE.VALUE( 'GMF_CONC_DEBUG' ));
1065     l_ret_status :='S';
1066 
1067      -- copy parameters into local variables
1068     l_le_id                := p_le_id;
1069     l_from_organization_id := p_from_organization_id;
1070     l_to_organization_id   := p_to_organization_id;
1071     l_from_item_id         := p_from_inventory_item_id;
1072     l_to_item_id           := p_to_inventory_item_id;
1073 
1074      fnd_file.put_line(fnd_file.log,'GMF_CONC_DEBUG Profile value : '||l_debug_level);
1075     IF l_debug_level >= l_debug_level_medium THEN
1076         fnd_file.put_line(fnd_file.log,'Entered Procedure: '||l_proc_name);
1077     END IF;
1078 
1079     fnd_file.put_line(fnd_File.LOG,'Landed Cost Adjustment Import Process started on '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1080 
1081     -- B9917469, Removing OPM_LCM dependency
1082       OPEN cur_patch_level;
1083       FETCH cur_patch_level INTO l_patch_level;
1084       CLOSE cur_patch_level;
1085 
1086     IF l_patch_level = 1 THEN
1087      -- It means that this profile is set to 'Yes' and hence we will support LCM integration
1088      IF x_enable_opm_lcm_flag <> 'Y' THEN  -- 11781472
1089       fnd_file.put_line(fnd_File.LOG,'OPM-LCM Integration is NOT SUPPORTED for RELEASE 12.1.1 PATCH LEVEL' );
1090       retcode := 1;
1091       RETURN ;  /* exit from the process with warning */
1092      END IF;
1093 
1094     END IF;
1095     -- B9917469 Ends
1096 
1097     IF l_debug_level >= l_debug_level_high THEN
1098         fnd_file.put_line(fnd_file.log,'Verify Legal Entity');
1099     END IF;
1100 
1101     -- verify LE
1102     IF l_le_id IS NOT NULL THEN
1103       OPEN cur_get_le;
1104       FETCH cur_get_le INTO l_le_name;
1105       CLOSE cur_get_le;
1106     ELSE
1107       fnd_file.put_line(fnd_File.LOG,'Insufficient Input parameters' );
1108       retcode := 3;
1109       RETURN ;  /* exit from the process */
1110     END IF;
1111       fnd_file.put_line(fnd_File.LOG,'Input parameters Legal entity / Id '|| l_le_name ||'/'||l_le_id);
1112 
1113     IF l_debug_level >= l_debug_level_medium THEN
1114         fnd_file.put_line(fnd_file.log,'Get organization code');
1115     END IF;
1116 
1117     -- Get from organization code
1118     IF l_from_organization_id IS NOT NULL THEN
1119         OPEN cur_get_org_cd ( l_from_organization_id) ;
1120         FETCH cur_get_org_cd INTO l_from_org_code;
1121         CLOSE cur_get_org_cd;
1122     END IF;
1123         -- Get to organization code
1124     IF l_to_organization_id IS NOT NULL THEN
1125         OPEN cur_get_org_cd ( l_to_organization_id );
1126         FETCH cur_get_org_cd INTO l_to_org_code;
1127         CLOSE cur_get_org_cd;
1128     END IF;
1129 
1130     IF l_debug_level >= l_debug_level_low THEN
1131         fnd_file.put_line(fnd_File.LOG,'Input parameters From OrgId/Code '||l_from_organization_id ||'/'||l_from_org_code);
1132         fnd_file.put_line(fnd_File.LOG,'Input parameters To OrgId/Code '||l_to_organization_id ||'/'||l_to_org_code);
1133     END IF;
1134 
1135     IF l_debug_level >= l_debug_level_high THEN
1136         fnd_file.put_line(fnd_file.log,'Get item number');
1137     END IF;
1138 
1139         -- Get from Item number
1140     IF l_from_item_id IS NOT NULL THEN
1141         OPEN cur_get_item (l_from_item_id);
1142         FETCH cur_get_item INTO l_from_item;
1143         CLOSE cur_get_item;
1144     END IF;
1145         -- Get to Item number
1146     IF l_to_item_id IS NOT NULL THEN
1147         OPEN cur_get_item (l_to_item_id);
1148         FETCH cur_get_item INTO l_to_item;
1149         CLOSE cur_get_item;
1150     END IF;
1151 
1152     IF l_debug_level >= l_debug_level_low THEN
1153        fnd_file.put_line(fnd_File.LOG,'Input parameters From ItemId/Item Number '||l_from_item_id ||'/'||l_from_item);
1154        fnd_file.put_line(fnd_File.LOG,'Input parameters To ItemId/Item Number '||l_to_item_id ||'/'||l_to_item);
1155     END IF;
1156 
1157     IF p_start_date IS NOT NULL THEN
1158         l_start_date := fnd_date.canonical_to_date(p_start_date);
1159     END IF;
1160 
1161     IF p_end_date IS NOT NULL THEN
1162         l_end_date := fnd_date.canonical_to_date(p_end_date);
1163     END IF;
1164     IF l_debug_level >= l_debug_level_low THEN
1165        fnd_file.put_line(fnd_File.LOG,'Input parameters Start date '||l_start_date );
1169     IF l_debug_level >= l_debug_level_low THEN
1166        fnd_file.put_line(fnd_File.LOG,'Input parameters End date '||l_end_date);
1167     END IF;
1168 
1170         fnd_file.put_line(fnd_file.log,'Get ledger ID');
1171     END IF;
1172 
1173     -- Get the ledger_id for the legal entity parameter
1174     SELECT primary_ledger_id
1175       INTO   l_ledger_id
1176       FROM gmf_legal_entities
1177      WHERE legal_entity_id = p_le_id;
1178 
1179     IF l_debug_level >= l_debug_level_low THEN
1180         fnd_file.put_line(fnd_file.log,'Open Cursor');
1181     END IF;
1182 
1183     FOR adjustments_row IN lc_adjustments_cur (l_le_id,
1184                             l_from_org_code,
1185                             l_to_org_code,
1186                             l_from_item,
1187                             l_to_item,
1188                             l_start_date,
1189                             l_end_date)  LOOP
1190 
1191     IF l_debug_level >= l_debug_level_high THEN
1192         fnd_file.put_line(fnd_file.log,'');
1193         fnd_file.put_line(fnd_file.log,'Line to be processed:');
1194         fnd_file.put_line(fnd_file.log,'Rcv Ttransaction ID' ||adjustments_row.rcv_transaction_id ||
1195                                        ' Ship Num: '||adjustments_row.lc_ship_num ||
1196                                        ' Ship Line Num: '||adjustments_row.lc_ship_line_num ||
1197                                        ' Item ID: '||adjustments_row.inventory_item_id ||
1198                                        ' Organization ID: '||adjustments_row.organization_id ||
1199                                        ' Adjustment Num: '||adjustments_row.adjustment_num ||
1200                                        ' Component Type: '||adjustments_row.component_type ||
1201                                        ' New Landed Cost: '||adjustments_row.new_landed_cost);
1202      END IF;
1203 
1204         l_total_lines := l_total_lines + 1;
1205 
1206         l_adjustment_row.parent_ship_line_id   := adjustments_row.parent_ship_line_id;
1207         l_adjustment_row.adjustment_num        := adjustments_row.adjustment_num;
1208         l_adjustment_row.ship_header_id        := adjustments_row.ship_header_id;
1209         l_adjustment_row.org_id                := adjustments_row.org_id;
1210         l_adjustment_row.ship_line_group_id    := adjustments_row.ship_line_group_id;
1211         l_adjustment_row.ship_line_id          := adjustments_row.ship_line_id;
1212         l_adjustment_row.organization_id       := adjustments_row.organization_id;
1213         l_adjustment_row.inventory_item_id     := adjustments_row.inventory_item_id;
1214         l_adjustment_row.prior_landed_cost     := adjustments_row.prior_landed_cost;
1215         l_adjustment_row.new_landed_cost       := adjustments_row.new_landed_cost;
1216         l_adjustment_row.allocation_percent    := adjustments_row.allocation_percent;
1217         l_adjustment_row.charge_line_type_id   := adjustments_row.charge_line_type_id;
1218         l_adjustment_row.charge_line_type_code := adjustments_row.charge_line_type_code;
1219         l_adjustment_row.cost_acquisition_flag := adjustments_row.cost_acquisition_flag;
1220         l_adjustment_row.component_type        := adjustments_row.component_type;
1221         l_adjustment_row.component_name        := adjustments_row.component_name;
1222         l_adjustment_row.parent_table_name     := adjustments_row.parent_table_name;
1223         l_adjustment_row.parent_table_id       := adjustments_row.parent_table_id;
1224         l_adjustment_row.cost_cmpntcls_id      := adjustments_row.cost_cmpntcls_id;
1225         l_adjustment_row.cost_analysis_code    := adjustments_row.cost_analysis_code;
1226         IF adjustments_row.adjustment_num = 0 THEN
1227             l_adjustment_row.transaction_date      := adjustments_row.transaction_date;
1228         ELSE  --
1229             SELECT max(m.adj_group_date) adj_group_date
1230             INTO l_adjustment_row.transaction_date
1231             FROM inl_matches m
1232             WHERE m.ship_header_id = adjustments_row.ship_header_id
1233             AND   m.adjustment_num = adjustments_row.adjustment_num;
1234         END IF;
1235         l_adjustment_row.transaction_quantity  := adjustments_row.transaction_quantity;
1236         l_adjustment_row.transaction_uom_code  := adjustments_row.transaction_uom_code;
1237         l_adjustment_row.primary_quantity      := adjustments_row.primary_quantity;
1238         l_adjustment_row.primary_uom_code      := adjustments_row.primary_uom_code;
1239         l_adjustment_row.lc_adjustment_flag    := adjustments_row.lc_adjustment_flag;
1240         l_adjustment_row.rcv_transaction_type  := adjustments_row.rcv_transaction_type;
1241         l_adjustment_row.rcv_transaction_id    := adjustments_row.rcv_transaction_id;
1242         l_adjustment_row.lc_ship_num              := adjustments_row.lc_ship_num;
1243         l_adjustment_row.lc_ship_line_num         := adjustments_row.lc_ship_line_num;
1244         l_adjustment_row.lc_var_account_id        := adjustments_row.lc_var_account_id;
1245         l_adjustment_row.lc_absorption_account_id := adjustments_row.lc_absorption_account_id;
1246 
1247         IF l_debug_level >= l_debug_level_medium THEN
1248             fnd_file.put_line(fnd_file.log,'Call Validate_Adjustments');
1249         END IF;
1250 
1251         Validate_Adjustments(p_le_id => p_le_id,
1252                              p_adjustment_row => l_adjustment_row,
1253                              p_validation_status => l_ret_status,
1254                              x_return_status => l_return_status);
1255 
1256          -- If any errors happen abort the process.
1257         IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1258             RAISE FND_API.G_EXC_ERROR;
1259         ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1260             RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1261         END IF;
1262 
1263         IF l_debug_level >= l_debug_level_medium THEN
1264             fnd_file.put_line(fnd_file.log,'Validate Adjustments Return Status: ' || l_ret_status);
1265         END IF;
1266 
1267         IF l_ret_status = 'S' THEN
1268             IF l_debug_level >= l_debug_level_medium THEN
1269                 fnd_file.put_line(fnd_file.log,'Call Create_AdjTrxLines');
1270             END IF;
1271             -- Insert Into GMF_LC_ADJ_TRANSACTIONS
1272             Create_AdjTrxLines(p_le_id => p_le_id,
1273                                p_ledger_id => l_ledger_id,
1274                                p_adjustment_row => l_adjustment_row,
1275                                x_return_status => l_return_status);
1276             -- If any errors happen abort the process.
1277             IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1278                 RAISE FND_API.G_EXC_ERROR;
1279             ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1280                 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1281             END IF;
1282             l_total_adj := l_total_adj + 1;
1283         ELSE
1284            l_total_ln_error := l_total_ln_error + 1;
1285            retcode := 1;
1286            fnd_file.put_line(fnd_file.log,'The line was not processed');
1287            fnd_file.put_line(fnd_file.log,'Rcv Ttransaction ID: ' ||l_adjustment_row.rcv_transaction_id ||
1288                                              ' Ship Num: '||l_adjustment_row.lc_ship_num ||
1289                                              ' Ship Line Num: '||l_adjustment_row.lc_ship_line_num ||
1290                                              ' Item ID: '||l_adjustment_row.inventory_item_id ||
1291                                              ' Organization ID: '||l_adjustment_row.organization_id ||
1292                                              ' Adjustment Num: '||l_adjustment_row.adjustment_num);
1293         END IF;
1294     END LOOP;
1295 
1296     fnd_file.put_line(fnd_file.log, 'Landed Cost Adjustments Import Processor finished at '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1297     fnd_file.put_line(fnd_file.log,' Total of LC Adjustment(s): ' || l_total_lines||
1298                                    ' LC Adjustment(s) created in gmf_lc_adj_transactions table : '|| l_total_adj ||
1299                                    ' LC Adjustment(s) with errors: '|| l_total_ln_error);
1300     COMMIT;
1301 
1302     IF l_debug_level >= l_debug_level_medium THEN
1303         fnd_file.put_line(fnd_file.log,'Leaving Procedure: '||l_proc_name);
1304     END IF;
1305 
1306     EXCEPTION
1307 ----
1308   WHEN utl_file.invalid_path THEN
1309     retcode := 3;
1310     errbuf := 'Invalid path - '||to_char(SQLCODE) || ' ' || SQLERRM;
1311      l_tmp := fnd_concurrent.set_completion_status('ERROR',sqlerrm || ' in ' || l_proc_name);
1312   WHEN utl_file.invalid_mode THEN
1313     retcode := 3;
1314     errbuf := 'Invalid Mode - '||to_char(SQLCODE) || ' ' || SQLERRM;
1315     l_tmp := fnd_concurrent.set_completion_status('ERROR',sqlerrm || ' in ' || l_proc_name);
1316   WHEN utl_file.invalid_filehandle then
1317     retcode := 3;
1318     errbuf := 'Invalid filehandle - '||to_char(SQLCODE) || ' ' || SQLERRM;
1319     l_tmp := fnd_concurrent.set_completion_status('ERROR',sqlerrm || ' in ' || l_proc_name);
1320   WHEN utl_file.invalid_operation then
1321     retcode := 3;
1322     errbuf := 'Invalid operation - '||to_char(SQLCODE) || ' ' || SQLERRM;
1323     l_tmp := fnd_concurrent.set_completion_status('ERROR',sqlerrm || ' in ' || l_proc_name);
1324   WHEN utl_file.write_error then
1325     retcode := 3;
1326     errbuf := 'Write error - '||to_char(SQLCODE) || ' ' || SQLERRM;
1327     l_tmp := fnd_concurrent.set_completion_status('ERROR',sqlerrm || ' in ' || l_proc_name);
1328   WHEN FND_API.G_EXC_ERROR THEN
1329     retcode := 3;
1330     errbuf := 'An error has ocurred:  ' || SQLERRM;
1331   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1332     retcode := 3;
1333     errbuf := 'An unexpected error has ocurred: ' || SQLERRM;
1334   WHEN others THEN
1335     retcode := 3;
1336       errbuf := to_char(SQLCODE) || ' ' || SQLERRM;
1337     l_tmp := fnd_concurrent.set_completion_status('ERROR',sqlerrm || ' in ' || l_proc_name);
1338 
1339 END Process_Lc_Adjustments;
1340 
1341 END GMF_LC_ADJ_TRANSACTIONS_PKG;