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.CSE_GL_INTERFACE_PKG

Source


1 PACKAGE BODY CSE_GL_INTERFACE_PKG AS
2 -- $Header: CSEGLINB.pls 120.15 2006/09/28 23:18:13 kuchauha noship $
3 
4 -------------------------------------------------------------------------------------
5 --  Following procedure derives:
6 --  If GL A/c entires needs to be reversed for this MTL transaction if
7 --  there are any FA Items in Inventory
8 -------------------------------------------------------------------------------------
9 
10 PROCEDURE debug( p_message_text   IN VARCHAR2) IS
11 BEGIN
12    cse_debug_pub.add( p_message_text );
13 EXCEPTION
14   WHEN OTHERS THEN
15        NULL;
16 END debug;
17 
18 PROCEDURE get_gl_interface_code(
19              p_mtl_txn_id        IN NUMBER
20             ,p_csi_txn_id        IN NUMBER
21             ,x_gl_interface_code OUT NOCOPY NUMBER
22             ,x_return_status     OUT NOCOPY VARCHAR2
23             ,x_error_message     OUT NOCOPY VARCHAR2)
24 IS
25 l_depreciate_flag                VARCHAR2(1) ;
26 l_inv_item_id                    NUMBER ;
27 l_eam_item_type                  NUMBER ;
28 l_srl_flag                       VARCHAR2(1);
29 l_entity_code                    VARCHAR2(100) ;
30 l_application_id                 NUMBER ; --CST;
31 l_need_to_post                   VARCHAR2(1);
32 l_return_status                  VARCHAR2(1);
33 l_error_message                  VARCHAR2(500);
34 l_serial_num_control_cd          NUMBER;
35 l_trf_txn_id                     NUMBER ;
36 
37 CURSOR mtl_cur IS
38 SELECT mmt.inventory_item_id
39       ,msi.eam_item_type
40       ,DECODE(msi.asset_creation_code,'1', 'Y','Y','Y','N') depreciate_flag
41       ,serial_number_control_code
42       ,decode(mmt.transaction_action_id ,
43               '2' , mmt.transfer_transaction_id ,
44               '3' , mmt.transfer_transaction_id ,
45               '28', mmt.transfer_transaction_id , mmt.transaction_id ) trf_txn_id
46 FROM   mtl_material_transactions mmt
47       ,mtl_system_items msi
48 WHERE  mmt.transaction_id    = p_mtl_txn_id
49 AND    mmt.inventory_item_id = msi.inventory_item_id
50 AND    mmt.organization_id   = msi.organization_id ;
51 
52 -- Assets expired as of txn date or less would not be reversed
53 Cursor mtl_srl_cur IS
54 SELECT 'Y'
55 FROM   mtl_unit_transactions mut
56       ,csi_item_instances cii
57       ,csi_i_assets cia
58       ,csi_transactions ct
59 WHERE  mut.transaction_id    = p_mtl_txn_id
60 AND    cii.inventory_item_id = mut.inventory_item_id
61 AND    cii.instance_id       = cia.instance_id
62 AND    ct.transaction_id     = p_csi_txn_id
63 AND    cia.creation_date    <= ct.transaction_date
64 AND    nvl(cia.active_end_date, ct.transaction_date + 1) >=  ct.transaction_date ;
65 
66 -- Assumption , If primary ledger is NT (non transferable) then
67 -- all secondary ledgers are NT and shld be marked with
68 -- gl_interface_code 3
69 
70 CURSOR  need_to_post_cur (c_mtl_transaction_id IN NUMBER
71                          ,c_gl_transfer_status_code IN VARCHAR2)
72 IS
73 SELECT  'N' --no need to post to gl
74 FROM    xla_transaction_entities xlte,
75         xla_ae_headers xlaeh
76 WHERE   nvl(xlte.source_id_int_1 , '-99') =  c_mtl_transaction_id
77 AND     xlte.entity_code      		  =  'MTL_ACCOUNTING_EVENTS'
78 AND     xlte.application_id  		  =  707
79 and     xlte.ledger_id in (select distinct caiv.ledger_id
80                            from   cst_acct_info_v caiv, mtl_transaction_accounts mta
81                            where  mta.transaction_id  = c_mtl_transaction_id
82                            and    mta.organization_id = caiv.organization_id )
83 AND   	xlaeh.entity_id       		  =  xlte.entity_id
84 AND   	xlaeh.gl_transfer_status_code 	  = 'NT' ;
85 
86 
87 BEGIN
88  --Init to No need to interface to GL
89 
90  x_gl_interface_code 	:=  3 ; ---None
91  l_srl_flag 		:= 'N' ;
92  l_entity_code 		:= 'MTL_ACCOUNTING_EVENTS';
93  l_application_id  	:=  707;
94  l_need_to_post 	:= 'Y' ;
95 
96 -- Setting the application context to CST Costing.
97   xla_security_pkg.set_security_context(p_application_id => l_application_id );
98 
99  OPEN  mtl_cur ;
100  FETCH mtl_cur INTO l_inv_item_id , l_eam_item_type, l_depreciate_flag,
101                     l_serial_num_control_cd , l_trf_txn_id;
102  CLOSE mtl_cur ;
103 
104  IF l_depreciate_flag = 'Y' OR l_eam_item_type = 1
105  THEN
106     x_gl_interface_code := 1 ; ---Pending
107 
108 -- NT is upgraded data from MTA from prior release before R12
109 -- In prior release we had IB cost hook so we need not wory abt 'NT' data
110 
111     OPEN  need_to_post_cur (p_mtl_txn_id , 'NT');
112     FETCH need_to_post_cur INTO l_need_to_post ;
113     CLOSE need_to_post_cur ;
114 
115 -- For sub trf , direct org and staging trf , if rcv side is NT then
116 -- sending is NT .
117 
118     IF    l_need_to_post = 'Y'
119     THEN
120 --      For subtrf , receipt is not in xla table
121         If (p_mtl_txn_id <> l_trf_txn_id)  THEN
122            OPEN  need_to_post_cur (l_trf_txn_id , 'NT');
123     	   FETCH need_to_post_cur INTO l_need_to_post ;
124            CLOSE need_to_post_cur ;
125         END IF;
126     END IF ;
127 
128     IF  l_need_to_post = 'N'
129     THEN
130 --      upgrade data
131         x_gl_interface_code := 3 ;
132     END if ;
133 
134  ELSE
135 -- MUT record only for serialized item
136    OPEN  mtl_srl_cur ;
137    FETCH mtl_srl_cur INTO l_srl_flag ;
138    CLOSE mtl_srl_cur ;
139 
140    IF l_srl_flag = 'N'
141    THEN
142 --    non srl ,non depreciable,  regular item OR no active CIA
143       x_gl_interface_code := 3 ; --None , no need to interface to GL
144    ELSE
145 --    srl , regular item with ATLEAST one active CIA  record  ias of txn dt
146       x_gl_interface_code := 1 ; ---Pending
147 
148       OPEN  need_to_post_cur (p_mtl_txn_id, 'NT') ;
149       FETCH need_to_post_cur INTO l_need_to_post ;
150       CLOSE need_to_post_cur ;
151 
152       IF    l_need_to_post = 'Y'
153       THEN
154 --      For subtrf , receipt is not in xla table
155         If p_mtl_txn_id <> l_trf_txn_id THEN
156            OPEN  need_to_post_cur (l_trf_txn_id , 'NT');
157     	   FETCH need_to_post_cur INTO l_need_to_post ;
158            CLOSE need_to_post_cur ;
159         END IF;
160       END IF ;
161 
162       IF l_need_to_post = 'N'
163       THEN
164          x_gl_interface_code := 3 ;
165       END IF ;
166 
167    END IF ; --l_srl_flag = 'N'
168  END IF ; ---l_depreciate_flag = 'Y'
169 
170 EXCEPTION
171      WHEN OTHERS
172      THEN
173           CSE_UTIL_PKG.write_log('Error in GET_GL_INTERF_CD : ' ||  SQLERRM);
174 END get_gl_interface_code ;
175 
176 PROCEDURE CREATE_GL_ENTRIES (
177    x_return_status                           OUT NOCOPY VARCHAR2,
178    x_error_msg                     	     OUT NOCOPY VARCHAR2,
179    p_conc_request_id                         IN  NUMBER )
180 IS
181 l_entity_code                                VARCHAR2(100) ;
182 l_application_id                             NUMBER ; -- CST;
183 l_cii_inventory_item_id                      NUMBER ;
184 l_depreciable                                VARCHAR2(1);
185 l_reversal_required                          BOOLEAN ;
186 l_txn_type                                   VARCHAR2(100);
187 l_transaction_type_id                        NUMBER ;
188 l_gl_interface_code                          NUMBER ;
189 l_gl_interface_tbl                           cse_gl_interface_pkg.gl_interface_tbl;
190 l_total_txn_qty                              NUMBER ;
191 l_no_of_fa_items                             NUMBER ;
192 l_gl_ccid                                    NUMBER ;
193 l_gl_amount_cr                               NUMBER ;
194 l_gl_amount_dr                               NUMBER ;
195 l_gl_amount_entered_cr                       NUMBER ;
196 l_gl_amount_entered_dr                       NUMBER ;
197 l_category_name                              VARCHAR2(30) ;
198 l_return_status                              VARCHAR2(1) ;
199 l_error_message                              VARCHAR2(2000) ;
200 l_asset_attrib_rec                           CSE_DATASTRUCTURES_PUB.asset_attrib_rec;
201 l_gl_group_id                                NUMBER ;
202 l_mmt_inventory_item_id                      NUMBER ;
203 l_mta_gl_sl_link_id                          NUMBER ;
204 l_mta_ussgl_transaction_code                 VARCHAR2(30) ;
205 l_mta_encumbrance_type_id                    NUMBER;
206 l_mta_actual_flag                            VARCHAR2(1) ;
207 l_mta_organization_id                        NUMBER;
208 l_mmt_transaction_date                       DATE ;
209 l_ciih_old_quantity                          NUMBER ;
210 l_ciih_new_quantity                          NUMBER ;
211 l_redeploy_qty                               NUMBER;
212 l_expired_qty                                NUMBER;
213 l_stmt_id                                    NUMBER ;
214 --l_gl_transfer_status_code                  VARCHAR2(30);
215 gl_idx                                       PLS_INTEGER;
216 i                                            NUMBER ;
217 e_error                                      EXCEPTION ;
218 e_redeploy_error                             EXCEPTION ;
219 l_fnd_success                                VARCHAR2(1);
220 l_fnd_error                                  VARCHAR2(1);
221 l_accounting_entry_status_code               VARCHAR2(30) ;
222 l_debug                                      VARCHAR2(1);
223 l_file                                       VARCHAR2(500);
224 l_sysdate                                    DATE ;
225 l_gl_insrt_success                           BOOLEAN;
226 l_redeploy_flag                  	     VARCHAR(1);
227 l_serial_num_control_cd                      NUMBER;
228 l_srl_flag                                   VARCHAR2(1);
229 l_trf_txn_id                     	     NUMBER ;
230 l_mmt_txn_action_id                          NUMBER ;
231 l_mmt_txn_id                     	     NUMBER ;
232 
233 CURSOR csi_gl_interface_code_cur
234 IS
235 SELECT ct.transaction_id,
236        ct.transaction_date,
237        ct.inv_material_transaction_id ,
238        ct.gl_interface_status_code
239 FROM   csi_transactions ct
240 WHERE  ct.gl_interface_status_code       = 1  --'PENDING'
241 AND    ct.transaction_status_code        = 'COMPLETE'  ; --only those txn, which are already interfaced to FA
242 
243 -- nvl on gl_interf_status_cd not reqd as t wld have a value when the below cur is opened
244 CURSOR csi_pending_txn_cur
245 IS
246 SELECT ct.inv_material_transaction_id,
247        ct.transaction_type_id,
248        ct.transaction_id
249 FROM   csi_transactions ct
250 WHERE  ct.gl_interface_status_code       = 1  ---'PENDING'
251 AND    ct.transaction_status_code        = 'COMPLETE' ;
252 
253 -- Cursor will be used to get redeployment status of nonexpired assets
254 
255 CURSOR csi_srl_cur(c_csi_transaction_id  IN NUMBER)
256 IS
257 SELECT citd.serial_number,
258        citd.inventory_item_id ,
259        citd.transaction_date
260 FROM   csi_inst_txn_details_v citd
261       ,csi_i_assets cia
262 WHERE  citd.transaction_id      = c_csi_transaction_id
263 AND    citd.instance_id         = cia.instance_id
264 AND    cia.creation_date       <= citd.transaction_date
265 AND    nvl(cia.active_end_date, citd.transaction_date + 1) >  citd.transaction_date ;
266 
267 -- l_csi_srl_rec                    csi_srl_cur%ROWTYPE;
268 
269 /*
270 CURSOR c_expired_cia_cur IS
271 SELECT 'N'
272 FROM   csi_inst_txn_details_v csitd
273       ,csi_i_assets cia
274 WHERE  csitd.transaction_id = csi_pending_txn_rec.transaction_id
275 AND    csitd.instance_id    = cia.instance_id
276 AND    nvl(cia.active_end_date, sysdate) <= sysdate ;
277 */
278 
279 -- Added performance issue
280 -- Before opening cursor , we have flipped txn with trf_txn_id for staging/sub trf
281 CURSOR  cst_acct_info_cur(c_mtl_transaction_id  IN NUMBER ,
282                           c_trf_txn_id          IN NUMBER )
283 IS
284 SELECT  distinct caiv.ledger_id , mmt.transaction_id
285 FROM    cst_acct_info_v  caiv,
286         mtl_transaction_accounts mta,
287         mtl_material_transactions mmt
288 WHERE   mta.transaction_id  = c_mtl_transaction_id
289 AND     mta.organization_id = caiv.organization_id
290 AND     mmt.transaction_id  = c_mtl_transaction_id
291 AND     mmt.transaction_action_id <> 3
292 UNION
293 SELECT  distinct caiv.ledger_id , mmt.transaction_id
294 FROM    cst_acct_info_v  caiv,
295         mtl_transaction_accounts mta ,
296         mtl_material_transactions mmt
297 WHERE   mta.transaction_id  in ( c_mtl_transaction_id , c_trf_txn_id )
298 AND     mta.organization_id = caiv.organization_id
299 AND     mmt.transaction_id  in ( c_mtl_transaction_id , c_trf_txn_id )
300 AND     mmt.transaction_action_id = 3 ;
301 
302 CURSOR category_name_cur
303 IS
304 SELECT user_je_category_name
305 FROM   gl_je_categories
306 WHERE  je_category_name                 = 'MTL';
307 
308 /*
309 CURSOR xla_lookup_cur
310 IS
311 SELECT lookup_code
312 FROM   fnd_lookups
313 WHERE  lookup_type         = 'XLA_ACCOUNTING_CLASS'
314 AND    meaning             = 'INVENTORY VALUATION';
315 */
316 
317 -- ledger_id join ion xlte is only for performance reason.
318 -- xlte is only created for primary ledger but
319 -- We need to reverse all ledger's in xla_ae_headers
320 -- in final mode .
321 
322 CURSOR  xla_header_cur( c_ledger_id           IN NUMBER
323                       , c_mtl_transaction_id  IN NUMBER)
324 IS
325 SELECT  xlaeh.ledger_id,
326         xlaeh.je_category_name,
327         xlaeh.ae_header_id,
328         xlaeh.application_id,
329         xlte.source_id_int_1,
330         xlte.source_id_int_2
331 FROM    xla_transaction_entities xlte,
332         xla_ae_headers xlaeh
333 WHERE   xlte.application_id            	=  707
334 AND     xlte.entity_code                =  'MTL_ACCOUNTING_EVENTS'
335 AND     xlte.ledger_id                  =  c_ledger_id
336 AND     nvl(xlte.source_id_int_1, '-99') = c_mtl_transaction_id
337 AND     xlte.application_id             =  xlaeh.application_id
338 AND     xlte.entity_id                  =  xlaeh.entity_id
339 AND     xlaeh.accounting_entry_status_code = 'F' ;
340 
341 CURSOR  xla_ae_lines_cur( c_application_id IN NUMBER,
342                           c_header_id      IN NUMBER)
343 IS
344 SELECT  xlael.code_combination_id ,
345         xlael.accounted_dr         tot_accounted_dr,
346         xlael.accounted_cr         tot_accounted_cr,
347         xlael.entered_dr           tot_entered_dr,
348         xlael.entered_cr           tot_entered_cr,
349         xlael.currency_code ,
350         xlael.accounting_class_code
351 FROM    xla_ae_lines xlael
352 WHERE   xlael.application_id      	= c_application_id
353 AND     xlael.ae_header_id              = c_header_id ;
354 -- AND  xlael.accounting_class_code     = 'INVENTORY_VALUATION'
355 --GROUP BY xlael.accounting_class_code , xlael.code_combination_id, xlael.currency_code ;
356 
357 BEGIN
358 
359   --Initialize local variables
360   l_entity_code                   := 'MTL_ACCOUNTING_EVENTS';
361   l_application_id                := 707;
362   l_cii_inventory_item_id         := 0;
363 --l_gl_transfer_status_code       := 'Y';
364   l_reversal_required             := FALSE;
365   l_total_txn_qty                 := 0;
366   l_gl_ccid                       := NULL;
367   l_gl_amount_cr                  := 0;
368   l_gl_amount_dr                  := 0;
369   l_gl_amount_entered_cr          := 0;
370   l_gl_amount_entered_dr          := 0;
371   l_stmt_id                       := 0;
372   i                               := 1;
373   gl_idx                          := 1;
374   l_fnd_success                   := fnd_api.g_ret_sts_success ;
375   l_fnd_error                     := fnd_api.g_ret_sts_error ;
376   x_return_status                 := l_fnd_success ;
377 
378 --  Setting the application context to CST Costing.
379   xla_security_pkg.set_security_context(p_application_id => l_application_id );
380 
381   l_debug := NVL(fnd_profile.value('CSE_DEBUG_OPTION'),'N');
382 
383   SELECT SYSDATE INTO l_sysdate FROM DUAL ;
384 
385   BEGIN
386 --  IF (l_debug = 'Y') THEN
387      cse_debug_pub.g_dir  := nvl(FND_PROFILE.VALUE('CSE_DEBUG_LOG_DIRECTORY'), '/tmp');
388      cse_debug_pub.g_file := NULL;
389      l_file               := cse_debug_pub.set_debug_file('cse' || to_char(sysdate, 'DD-MON-YYYY') || '.log');
390      cse_debug_pub.debug_on;
391      debug('************************************************************');
392      debug(' CSEGLINB : Start date : '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss')
393                            ||' Request id : '||fnd_global.conc_request_ID);
394 --  END IF;
395   EXCEPTION
396     WHEN OTHERS THEN
397     NULL;
398   END;
399 
400   FOR   CSI_GL_INTERFACE_CODE_REC in CSI_GL_INTERFACE_CODE_CUR
401   LOOP
402         IF (l_debug = 'Y')
403         THEN
404             debug('Before Util Pkg CSI_TXN_ID: '
405                         || to_char(csi_gl_interface_code_rec.transaction_id)
406                         || ' CSI_MMT_TXN_ID: '
407                         || to_char(csi_gl_interface_code_rec.inv_material_transaction_id)
408                         || ' GL_INTERF_CD: ' || to_char(csi_gl_interface_code_rec.gl_interface_status_code)
409                  );
410         END IF;
411 --
412         get_gl_interface_code (
413                p_mtl_txn_id        => csi_gl_interface_code_rec.inv_material_transaction_id,
414 	       p_csi_txn_id        => csi_gl_interface_code_rec.transaction_id,
415                x_gl_interface_code => l_gl_interface_code,
416                x_return_status     => l_return_status,
417                x_error_message     => l_error_message);
418 
419         IF l_return_status <> fnd_api.g_ret_sts_success
420         THEN
421            RAISE	e_error ;
422         END IF ;
423 
424         IF (l_debug = 'Y')
425         THEN
426             debug('After Util Pkg CSI_TXN_ID: '
427                             || to_char(csi_gl_interface_code_rec.transaction_id)
428                             || ' CSI_MMT_TXN_ID: '
429                             || to_char(csi_gl_interface_code_rec.inv_material_transaction_id)
430                             || ' GL_INTERF_CD: ' || to_char(l_gl_interface_code)
431                   ) ;
432         END IF;
433 
434         UPDATE csi_transactions
435         SET    gl_interface_status_code      =	l_gl_interface_code
436         WHERE  transaction_id                =  csi_gl_interface_code_rec.transaction_id;
437 
438   END LOOP; --csi_gl_interface_code_rec
439 
440   OPEN   CATEGORY_NAME_CUR;
441   FETCH  category_name_cur INTO l_category_name; --cursor to get je category name
442   CLOSE  CATEGORY_NAME_CUR;
443 
444   IF (l_debug = 'Y')
445   THEN
446     debug('Before Csi_Pending_Txn_Cur') ;
447   END IF;
448 
449   FOR CSI_PENDING_TXN_REC IN CSI_PENDING_TXN_CUR
450   LOOP
451         BEGIN
452           SELECT  mmt.inventory_item_id , mmt.transaction_date ,
453                   msi.serial_number_control_code ,
454                   DECODE(msi.asset_creation_code,'1', 'Y','Y','Y','N'),
455                   DECODE(mmt.transaction_action_id ,
456               		'2' , mmt.transfer_transaction_id ,
457               		'3' , mmt.transfer_transaction_id ,
458               		'28', mmt.transfer_transaction_id , mmt.transaction_id ),
459                   transaction_action_id,
460                   transaction_id
461           INTO    l_mmt_inventory_item_id , l_mmt_transaction_date ,
462                   l_serial_num_control_cd ,
463                   l_depreciable ,
464                   l_trf_txn_id ,
465                   l_mmt_txn_action_id ,
466                   l_mmt_txn_id
467           FROM    mtl_material_transactions mmt , mtl_system_items msi
468           WHERE   mmt.transaction_id    = csi_pending_txn_rec.inv_material_transaction_id
469             AND   mmt.inventory_item_id = msi.inventory_item_id
470             AND   mmt.organization_id   = msi.organization_id ;
471 
472 	  IF (l_debug = 'Y')
473           THEN
474                debug('Inside Csi_Pending_Txn_Cur CSI_MMT_TXN_ID: '
475                          || to_char(csi_pending_txn_rec.inv_material_transaction_id)
476                          || ' MMT_TRF_TXN_ID: ' || to_char(l_trf_txn_id)
477                          || ' MMT_TXN_ACTN_ID: '    || to_char(l_mmt_txn_action_id)
478                     ) ;
479           END IF;
480 
481 -- We use below st. to get txn qty , issues if qty is read qty from MMT
482 -- Do we need any date chk for expired here ??
483 
484           SELECT sum(nvl(ciih.old_quantity, 0) ),
485                  sum(nvl(ciih.new_quantity ,0) ),
486                  cii.inventory_item_id
487           INTO
488                  l_ciih_old_quantity ,
489                  l_ciih_new_quantity ,
490                  l_cii_inventory_item_id
491           FROM   csi_item_instances_h ciih ,
492                  csi_item_instances  cii
493           WHERE  ciih.transaction_id      = csi_pending_txn_rec.transaction_id
494           AND    ciih.instance_id         = cii.instance_id
495           AND    cii.inventory_item_id    = l_mmt_inventory_item_id
496           GROUP  BY cii.inventory_item_id , ciih.transaction_id  ;
497 
498           l_total_txn_qty:= ABS(l_ciih_old_quantity - l_ciih_new_quantity );
499 
500           If  l_total_txn_qty  = 0 then
501               l_total_txn_qty := 1;
502           END IF;
503 
504 --        CSE_UTIL_PKG.CHECK_DEPRECIABLE(     p_inventory_item_id => l_cii_inventory_item_id,
505 --                                            p_depreciable       => l_depreciable );
506 
507 --        IF (l_debug = 'Y')
508 --        THEN
509                debug(' Item Id     : '  ||to_char(l_cii_inventory_item_id) ||
510                      ' Depreciable : '  ||l_depreciable ||
511                      ' Tot Txn Qty   : ' ||to_char(l_total_txn_qty) ) ;
512 --        END IF;
513 
514           l_redeploy_qty := 0 ;
515           IF  l_depreciable  = 'Y' -- Item is depreciable
516           THEN
517 
518 -- ??? We can be here only for deprec srl/non srl or regular srl items as we do
519 --     not reverse regular non-srl
520 
521  	    IF l_serial_num_control_cd IN (2,5) THEN
522  -- 		   l_srl_flag     := 'Y';
523  --  for redeploy , already cursor csi_srl_rec filters for expired CIA
524     		   FOR  csi_srl_rec in csi_srl_cur(csi_pending_txn_rec.transaction_id)
525                    LOOP
526     		       cse_util_pkg.get_redeploy_flag (
527               		 p_inventory_item_id  => csi_srl_rec.inventory_item_id
528              		,p_serial_number      => csi_srl_rec.serial_number
529              		,p_transaction_date   => csi_srl_rec.transaction_date
530              		,x_redeploy_flag      => l_redeploy_flag
531              		,x_return_status      => l_return_status
532              		,x_error_message      => l_error_message);
533 
534            		IF     l_return_status <> fnd_api.g_ret_sts_success
535            		THEN
536                		RAISE  e_redeploy_error ;
537            		END IF ;
538 
539                         If l_redeploy_flag = 'Y' THEN
540                            l_redeploy_qty  := l_redeploy_qty + 1 ;
541                         End If ;
542                    END LOOP ;
543  	    END IF;
544 
545             l_expired_qty := 0 ;
546 
547  -- get tot expired qty before the current txn date , note the <
548 	   SELECT  sum(nvl(cia.asset_quantity, 0)) into l_expired_qty
549 	     FROM  csi_inst_txn_details_v citd
550       		  ,csi_i_assets cia
551 	    WHERE  citd.transaction_id      = csi_pending_txn_rec.transaction_id
552               AND  citd.instance_id         = cia.instance_id
553               AND  cia.creation_date       <= citd.transaction_date
554               AND  nvl(cia.active_end_date, citd.transaction_date+ 1) <  citd.transaction_date ;
555 
556 --          Any asset creating txn other than a redeploy part of it  shld be reversed
557 --          For redeploy , asset has been created previously , so reverse
558             l_no_of_fa_items     := 0 ;
559             IF  csi_pending_txn_rec.transaction_type_id IN (105,112,117,128,129)
560               --105 : 'PO_RECEIPT_INTO_PROJECT' -- Not possible as no associated Inv txn
561               --112 : 'PO_RECEIPT_INTO_INVENTORY'
562               --117 : 'MISC_RECEIPT'
563               --128 : 'ACCT_RECEIPT'
564               --129 : 'ACCT_ALIAS_RECEIPT'
565             THEN
566                 If l_redeploy_qty 	= 0 then
567                    l_reversal_required  := FALSE;
568                    l_no_of_fa_items     := 0 ; -- adding so below if does not fail
569                                                -- rev_rqd OR # of fa_items
570                 Else
571                    l_no_of_fa_items     := l_redeploy_qty ;
572                    l_reversal_required  := TRUE;
573                 End if ;
574             ELSE
575               -- for depr itm, all txns other than above list
576                 l_no_of_fa_items     := nvl(l_total_txn_qty ,0)  - nvl(l_expired_qty , 0);
577                 If l_no_of_fa_items 	= 0 then
578                    l_reversal_required  := FALSE;
579                 Else
580                    l_reversal_required  := TRUE;
581                 End if;
582             END IF ; -- txn_type_id in condn
583           END IF ; --l_depreciable='Y'
584 
585 --        below for regular srl items ONLY
586 --        Get the number of FA Items for normal items in inventory
587           IF l_depreciable = 'N'
588           THEN
589 --          active_end_date can't be future dated , chk for performance
590 --          # of active assets at the time of txn date
591 	     SELECT  sum(cia.asset_quantity)
592                INTO  l_no_of_fa_items
593 	       FROM  csi_inst_txn_details_v citd
594       		    ,csi_i_assets cia
595 	      WHERE  citd.transaction_id      = csi_pending_txn_rec.transaction_id
596               AND    citd.instance_id         = cia.instance_id
597               AND    cia.creation_date        <= citd.transaction_date
598               AND    nvl(cia.active_end_date, citd.transaction_date+ 1) >=  citd.transaction_date ;
599 /*
600              SELECT   sum(cia.asset_quantity)
601              INTO     l_no_of_fa_items
602              FROM     csi_item_instances cii,
603                       csi_transactions ct ,
604                       csi_item_instances_h ciih ,
605                       csi_i_assets cia
606              WHERE    cii.instance_id   = ciih.instance_id
607              AND      ct.transaction_id = ciih.transaction_id
608              AND      ct.transaction_id = csi_pending_txn_rec.transaction_id
609              AND      cia.instance_id   = cii.instance_id
610              AND      cia.creation_date < ct.transaction_date
611              AND      cia.active_end_date is null ;
612 */
613           END IF ; --l_depreciable='N'
614 
615           debug ( ' Redeploy Qty: '   || to_char(l_redeploy_qty) ||
616                   '       Expired  Qty: '   || to_char(nvl(l_expired_qty, 0))  ||
617                   ' Num_Of_FA_Itms: ' || to_char(l_no_of_fa_items)
618                 ) ;
619 
620           IF l_reversal_required OR NVL(l_no_of_fa_items,0) > 0
621           THEN
622 --
623            l_gl_insrt_success := FALSE;
624 
625            IF    l_mmt_txn_action_id in (2  , 28)
626            THEN
627                  l_mmt_txn_id := l_trf_txn_id ;
628            END IF ;
629 
630            IF (l_debug = 'Y')
631            THEN
632               debug('Before Cst_Acct_Info_Cur' ) ;
633            END IF;
634 
635            FOR  cst_acct_info_rec IN cst_acct_info_cur( l_mmt_txn_id  , l_trf_txn_id)
636            LOOP
637                 IF (l_debug = 'Y')
638                 THEN
639                     debug('Before Xla_Header_Cur'
640                                || ' CST_LEDG_ID: '     || to_char(cst_acct_info_rec.ledger_id)
641                                || '  CST_MMT_TXN_ID: ' || to_char(cst_acct_info_rec.transaction_id) );
642                 END IF;
643 --
644                 FOR xla_header_rec IN xla_header_cur(  cst_acct_info_rec.ledger_id
645                                                      , cst_acct_info_rec.transaction_id )
646                 LOOP
647 --
648                  IF (l_debug = 'Y')
649                  THEN
650                     debug('Inside Xla_Header_Cur'
651                                || ' XLA_ORG_ID: '     || to_char(xla_header_rec.source_id_int_2)
652                                || ' XLA_MMT_TXN_ID: ' || to_char(xla_header_rec.source_id_int_1) ) ;
653                  END IF;
654 
655                  SELECT
656                       mta.gl_sl_link_id,
657                       mta.ussgl_transaction_code,
658                       mta.encumbrance_type_id,
659                       mta.organization_id,
660                       DECODE(mta.encumbrance_type_id, NULL, 'A', 'E')
661                  INTO
662                       l_mta_gl_sl_link_id,
663                       l_mta_ussgl_transaction_code,
664                       l_mta_encumbrance_type_id,
665                       l_mta_organization_id,
666                       l_mta_actual_flag
667                  FROM
668                       mtl_transaction_accounts mta
669                  WHERE
670                       mta.transaction_id  = xla_header_rec.source_id_int_1
671                  AND  mta.organization_id = xla_header_rec.source_id_int_2
672                  AND  rownum  = 1 ;
673 
674                  IF  i = 1
675                  THEN
676                      SELECT GL_INTERFACE_CONTROL_S.NEXTVAL INTO l_gl_group_id FROM DUAL ;
677                  END IF ;
678 
679 
680                  IF (l_debug = 'Y')
681                  THEN
682                     debug('Before Xla_Ae_Lines_Cur'
683                                        || ' XLA_APPN_ID: ' || to_char(xla_header_rec.application_id)
684                                        || ' XLA_HDR_ID: ' || to_char(xla_header_rec.ae_header_id)
685                          ) ;
686                  END IF;
687 
688                FOR xla_ae_lines_rec IN  xla_ae_lines_cur (
689                         xla_header_rec.application_id ,
690                         xla_header_rec.ae_header_id )
691                LOOP
692                    l_gl_ccid                      := NULL;
693                    l_gl_amount_cr                 := 0;
694                    l_gl_amount_dr                 := 0;
695                    l_gl_amount_entered_cr         := 0;
696                    l_gl_amount_entered_dr         := 0;
697 
698 --                    IF (l_debug = 'Y')
699 --                    THEN
700                         debug( 'Inside Xla_Ae_Lines_Cur '
701                         || '  Gl_Idx: '   || to_char(gl_idx)
702                         || '  I: '        || to_char(i) ) ;
703 --                    END IF;
704 
705                     IF  xla_ae_lines_rec.tot_accounted_dr > 0
706                     THEN
707                         l_gl_ccid        := xla_ae_lines_rec.code_combination_id;
708                         l_gl_amount_cr   := (xla_ae_lines_rec.tot_accounted_dr/l_total_txn_qty) * l_no_of_fa_items;
709                         l_gl_amount_entered_cr := (xla_ae_lines_rec.tot_entered_dr/l_total_txn_qty) * l_no_of_fa_items;
710 --
711 --                        IF (l_debug = 'Y')
712 --                        THEN
713                              debug('  Credit Gl_Ccid :' || to_char(l_gl_ccid)
714                                       || '  Amt: ' || to_char(l_gl_amount_cr)
715                                       || '  Ent_Amt: ' || to_char(l_gl_amount_entered_cr) );
716 --                        END IF;
717 
718                     ELSIF xla_ae_lines_rec.tot_accounted_cr > 0
719                     THEN
720                         l_gl_ccid        := xla_ae_lines_rec.code_combination_id;
721                         l_gl_amount_dr   := (xla_ae_lines_rec.tot_accounted_cr/l_total_txn_qty) * l_no_of_fa_items;
722                         l_gl_amount_entered_dr := (xla_ae_lines_rec.tot_entered_cr/l_total_txn_qty)   * l_no_of_fa_items;
723 --
724 --                        IF (l_debug = 'Y')
725 --                        THEN
726                             debug('  Debit Gl_Ccid: ' || to_char(l_gl_ccid)
727                                     || '  Amt: ' || to_char(l_gl_amount_dr)
728                                     || '  Ent_Amt: ' || to_char(l_gl_amount_entered_dr) );
729 --                        END IF;
730                     END IF;
731 
732 --                   IF (l_debug = 'Y')
733 --                   THEN
734                     debug('  Currency: '   || xla_ae_lines_rec.currency_code ||
735                           '  Actual: '     || l_mta_actual_flag ||
736                           '  Encum_Type_Id: ' || l_mta_encumbrance_type_id ||
737                           '  Category: '   || l_category_name   ||
738                           '  SOB: '        || to_char(xla_header_rec.ledger_id) ) ;
739 --                   END IF;
740 
741 
742         -- Check if we need to pass xlael.accounting_class_code to gl_interface
743 
744           l_gl_interface_tbl(gl_idx).STATUS                := 'NEW';
745           l_gl_interface_tbl(gl_idx).ACCOUNTING_DATE       :=  l_mmt_transaction_date;
746           l_gl_interface_tbl(gl_idx).DATE_CREATED          :=  SYSDATE;
747           l_gl_interface_tbl(gl_idx).CREATED_BY            :=  fnd_global.user_id ;
748           l_gl_interface_tbl(gl_idx).ACTUAL_FLAG           :=  l_mta_actual_flag;
749           l_gl_interface_tbl(gl_idx).USER_JE_CATEGORY_NAME :=  l_category_name  ;
750           l_gl_interface_tbl(gl_idx).USER_JE_SOURCE_NAME   := 'Inventory'    ;
751           l_gl_interface_tbl(gl_idx).ENCUMBRANCE_TYPE_ID   :=  l_mta_encumbrance_type_id;
752           l_gl_interface_tbl(gl_idx).SET_OF_BOOKS_ID       :=  xla_header_rec.ledger_id;
753           l_gl_interface_tbl(gl_idx).CODE_COMBINATION_ID   :=  l_gl_ccid;
754           l_gl_interface_tbl(gl_idx).CURRENCY_CODE         :=  xla_ae_lines_rec.currency_code;
755           l_gl_interface_tbl(gl_idx).ENTERED_DR            :=  l_gl_amount_entered_dr;
756           l_gl_interface_tbl(gl_idx).ENTERED_CR            :=  l_gl_amount_entered_cr;
757           l_gl_interface_tbl(gl_idx).ACCOUNTED_DR          :=  l_gl_amount_dr;
758           l_gl_interface_tbl(gl_idx).ACCOUNTED_CR          :=  l_gl_amount_cr;
759           l_gl_interface_tbl(gl_idx).TRANSACTION_DATE      :=  l_mmt_transaction_date;
760           l_gl_interface_tbl(gl_idx).REFERENCE1            :=  'Oracle Asset Tracking Reversal' ;
761           l_gl_interface_tbl(gl_idx).REFERENCE2            :=  NULL;--open issue
762           l_gl_interface_tbl(gl_idx).REFERENCE5            :=  NULL;--open issue
763           l_gl_interface_tbl(gl_idx).REFERENCE10           :=  NULL;--open issue
764           l_gl_interface_tbl(gl_idx).REFERENCE22           :=  xla_header_rec.source_id_int_2;
765           l_gl_interface_tbl(gl_idx).REFERENCE23           :=  xla_header_rec.source_id_int_1;
766           l_gl_interface_tbl(gl_idx).USSGL_TRANSACTION_CODE := l_mta_ussgl_transaction_code;
767           l_gl_interface_tbl(gl_idx).GROUP_ID              :=  l_gl_group_id ;
768           l_gl_interface_tbl(gl_idx).GL_SL_LINK_ID         :=  l_mta_gl_sl_link_id ;
769           l_gl_interface_tbl(gl_idx).GL_SL_LINK_TABlE      := 'MTA';
770           l_gl_interface_tbl(gl_idx).request_id            :=  fnd_global.conc_request_ID ;
771 
772            IF (l_debug = 'Y')
773            THEN
774                  debug('Before Inserting Into Gl_Interface ' ) ;
775            END IF;
776                 INSERT  INTO   GL_INTERFACE(
777                             GROUP_ID,
778                             STATUS,
779                             SET_OF_BOOKS_ID,
780                             USER_JE_SOURCE_NAME,
781                             USER_JE_CATEGORY_NAME,
782                             ACCOUNTING_DATE,
783                             CURRENCY_CODE,
784                             ACTUAL_FLAG,
785                             ENCUMBRANCE_TYPE_ID,
786                             DATE_CREATED,
787                             CREATED_BY,
788                             ENTERED_DR,
789                             ENTERED_CR,
790                             REFERENCE1,
791                             REFERENCE2,
792                             REFERENCE5,
793                             REFERENCE10,
794                             REFERENCE21,
795                             REFERENCE22,
796                             REFERENCE23,
797                             CODE_COMBINATION_ID,
798                             USSGL_TRANSACTION_CODE,
799                             ACCOUNTED_DR,
800                             ACCOUNTED_CR,
801                             GL_SL_LINK_ID,
802                             GL_SL_LINK_TABLE,
803                             REQUEST_ID,
804                             SEGMENT1,  SEGMENT2,   SEGMENT3,   SEGMENT4,   SEGMENT5,
805                             SEGMENT6,  SEGMENT7,   SEGMENT8,   SEGMENT9,   SEGMENT10,
806                             SEGMENT11, SEGMENT12,  SEGMENT13,  SEGMENT14,  SEGMENT15,
807                             SEGMENT16, SEGMENT17,  SEGMENT18,  SEGMENT19,  SEGMENT20,
808                             SEGMENT21, SEGMENT22,  SEGMENT23,  SEGMENT24,  SEGMENT25,
809                             SEGMENT26, SEGMENT27,  SEGMENT28,  SEGMENT29,  SEGMENT30)
810         VALUES (  l_gl_group_id
811          ,DECODE( l_gl_interface_tbl(i).status ,
812                                         FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).status )
813          ,DECODE( l_gl_interface_tbl(i).set_of_books_id,
814                                         FND_API.G_MISS_NUM,  NULL,  l_gl_interface_tbl(i).set_of_books_id)
815          ,DECODE( l_gl_interface_tbl(i).user_je_source_name,
816                                         FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).user_je_source_name)
817          ,DECODE( l_gl_interface_tbl(i).user_je_category_name,
818                                         FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).user_je_category_name)
819          ,DECODE( l_gl_interface_tbl(i).accounting_date,
820                                         FND_API.G_MISS_DATE, NULL, l_gl_interface_tbl(i).accounting_date)
821          ,DECODE( l_gl_interface_tbl(i).currency_code ,
822                                         FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).currency_code)
823          ,DECODE( l_gl_interface_tbl(i).actual_flag   ,
824                                         FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).actual_flag)
825          ,DECODE( l_gl_interface_tbl(i).encumbrance_type_id ,
826                                         FND_API.G_MISS_NUM,  NULL, l_gl_interface_tbl(i).encumbrance_type_id)
827          ,DECODE( l_gl_interface_tbl(i).date_created,
828                                         FND_API.G_MISS_DATE, NULL, l_gl_interface_tbl(i).date_created)
829          ,DECODE( l_gl_interface_tbl(i).created_by  ,
830                                         FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).created_by)
831          ,DECODE( l_gl_interface_tbl(i).entered_dr  ,
832                                         FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).entered_dr)
833          ,DECODE( l_gl_interface_tbl(i).entered_cr  ,
834                                         FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).entered_cr)
835          ,DECODE( l_gl_interface_tbl(i).reference1  ,
836                                         FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference1)
837          ,DECODE( l_gl_interface_tbl(i).reference2  ,
838 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference2)
839          ,DECODE( l_gl_interface_tbl(i).reference5  ,
840 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference5)
841          ,DECODE( l_gl_interface_tbl(i).reference10 ,
842 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference10)
843          ,DECODE( l_gl_interface_tbl(i).reference21 ,
844 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference21)
845          ,DECODE( l_gl_interface_tbl(i).reference22 ,
846 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference22)
847          ,DECODE( l_gl_interface_tbl(i).reference23 ,
848 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference23)
849          ,DECODE( l_gl_interface_tbl(i).code_combination_id ,
850                                         FND_API.G_MISS_NUM, NULL, l_gl_interface_tbl(i).code_combination_id)
851          ,DECODE( l_gl_interface_tbl(i).ussgl_transaction_code,
852                                         FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).ussgl_transaction_code)
853          ,DECODE( l_gl_interface_tbl(i).accounted_dr ,
854 					FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).accounted_dr)
855          ,DECODE( l_gl_interface_tbl(i).accounted_cr ,
856 					FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).accounted_cr)
857          ,DECODE( l_gl_interface_tbl(i).gl_sl_link_id ,
858 					FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).gl_sl_link_id)
859          ,DECODE( l_gl_interface_tbl(i).gl_sl_link_table,
860                                         FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).gl_sl_link_table)
861          ,DECODE( l_gl_interface_tbl(i).request_id ,
862 					FND_API.G_MISS_NUM, NULL, l_gl_interface_tbl(i).request_id)
863          ,DECODE( l_gl_interface_tbl(i).segment1   ,
864 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment1 )
865          ,DECODE( l_gl_interface_tbl(i).segment2   ,
866 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment2 )
867          ,DECODE( l_gl_interface_tbl(i).segment3   ,
868 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment3 )
869          ,DECODE( l_gl_interface_tbl(i).segment4   ,
870 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment4 )
871          ,DECODE( l_gl_interface_tbl(i).segment5   ,
872 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment5 )
873          ,DECODE( l_gl_interface_tbl(i).segment6   ,
874 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment6 )
875          ,DECODE( l_gl_interface_tbl(i).segment7   ,
876 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment7 )
877          ,DECODE( l_gl_interface_tbl(i).segment8   ,
878 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment8 )
879          ,DECODE( l_gl_interface_tbl(i).segment9   ,
880 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment9 )
881          ,DECODE( l_gl_interface_tbl(i).segment10  ,
882 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment10 )
883          ,DECODE( l_gl_interface_tbl(i).segment11  ,
884 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment11 )
885          ,DECODE( l_gl_interface_tbl(i).segment12  ,
886 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment12 )
887          ,DECODE( l_gl_interface_tbl(i).segment13  ,
888 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment13 )
889          ,DECODE( l_gl_interface_tbl(i).segment14  ,
890 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment14 )
891          ,DECODE( l_gl_interface_tbl(i).segment15  ,
892 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment15 )
893          ,DECODE( l_gl_interface_tbl(i).segment16  ,
894 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment16 )
895          ,DECODE( l_gl_interface_tbl(i).segment17  ,
896 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment17 )
897          ,DECODE( l_gl_interface_tbl(i).segment18  ,
898 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment18 )
899          ,DECODE( l_gl_interface_tbl(i).segment19  ,
900 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment19 )
901          ,DECODE( l_gl_interface_tbl(i).segment20  ,
902 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment20 )
903          ,DECODE( l_gl_interface_tbl(i).segment21  ,
904 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment21 )
905          ,DECODE( l_gl_interface_tbl(i).segment22  ,
906 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment22 )
907          ,DECODE( l_gl_interface_tbl(i).segment23  ,
908 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment23 )
909          ,DECODE( l_gl_interface_tbl(i).segment24  ,
910 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment24 )
911          ,DECODE( l_gl_interface_tbl(i).segment25  ,
912 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment25 )
913          ,DECODE( l_gl_interface_tbl(i).segment26  ,
914 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment26 )
915          ,DECODE( l_gl_interface_tbl(i).segment27  ,
916 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment27 )
917          ,DECODE( l_gl_interface_tbl(i).segment28  ,
918 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment28 )
919          ,DECODE( l_gl_interface_tbl(i).segment29  ,
920 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment29 )
921          ,DECODE( l_gl_interface_tbl(i).segment30  ,
922 					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment30 )
923          ) ;
924 
925 	 IF SQL%FOUND THEN
926             l_gl_insrt_success := TRUE;
927          END IF;
928 --
929          gl_idx        :=  gl_idx + 1; --increment the gl index
930          i             :=  i + 1;
931 --
932         END LOOP;  --- xla_ae_lines_curser
933       END LOOP;     --- xla_header_cur
934      END LOOP;  --- cst_acct_info_cur
935 
936       IF  l_gl_insrt_success THEN
937 --
938 --      IF (l_debug = 'Y')
939 --      THEN
940          debug('Before Upd Csi_Txn_Status To 2 for Csi_Mmt_Txn_Id: '
941                                 || to_char(csi_pending_txn_rec.inv_material_transaction_id) ) ;
942 --      END IF;
943          UPDATE csi_transactions
944          SET    gl_interface_status_code      = 2 ---'POSTED'
945          WHERE  inv_material_transaction_id   = csi_pending_txn_rec.inv_material_transaction_id;
946       END IF;
947    ELSE
948 --
949 --       IF (l_debug = 'Y')
950 --       THEN
951                debug('Before Upd Csi_Txn_Status To 3 for Csi_Mmt_Txn_Id: '
952                                 || to_char(csi_pending_txn_rec.inv_material_transaction_id) ) ;
953 --         END IF;
954 
955          UPDATE csi_transactions
956             SET gl_interface_status_code    = 3 ---'NONE'
957           WHERE inv_material_transaction_id = csi_pending_txn_rec.inv_material_transaction_id;
958 --
959    END IF ; -- reversal_required or regular item with CIA
960 --
961    EXCEPTION
962 -- This exception would catch no_data_found on MMT and other tables and skip
963 -- to next cursor record.
964       WHEN no_data_found
965       THEN
966            CSE_UTIL_PKG.write_log('Error in CREATE_GL_ENTRIES CSI_TXN_ID: '
967                          ||to_char(csi_pending_txn_rec.transaction_id)
968                          || ' CSI_MMT_TXN_ID: '
969                          || to_char(csi_pending_txn_rec.inv_material_transaction_id)
970                          || l_error_message ) ;
971       WHEN e_redeploy_error
972       THEN
973            CSE_UTIL_PKG.write_log('Error in CREATE_GL_ENTRIES Redeploy CSI_TXN_ID: '
974                          || to_char(csi_pending_txn_rec.transaction_id)
975                          || ' CSI_MMT_TXN_ID: '
976                          || to_char(csi_pending_txn_rec.inv_material_transaction_id)
977 		         || l_error_message);
978    End ;
979 END LOOP; --- csi_pending_txn_cur
980 
981 debug(' End date : '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
982 EXCEPTION
983      WHEN e_error
984      THEN
985           CSE_UTIL_PKG.write_log('Error in CREATE_GL_ENTRIES :' || l_error_message);
986      WHEN OTHERS
987      THEN
988           CSE_UTIL_PKG.write_log('Error in CREATE_GL_ENTRIES : ' ||  SQLERRM);
989 END CREATE_GL_ENTRIES ;
990 
991 END CSE_GL_INTERFACE_PKG ;