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.

APPS.CSE_GL_INTERFACE_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 38


SELECT mmt.inventory_item_id
      ,msi.eam_item_type
      ,DECODE(msi.asset_creation_code,'1', 'Y','Y','Y','N') depreciate_flag
      ,serial_number_control_code
      ,decode(mmt.transaction_action_id ,
              '2' , mmt.transfer_transaction_id ,
              '3' , mmt.transfer_transaction_id ,
              '28', mmt.transfer_transaction_id , mmt.transaction_id ) trf_txn_id
FROM   mtl_material_transactions mmt
      ,mtl_system_items msi
WHERE  mmt.transaction_id    = p_mtl_txn_id
AND    mmt.inventory_item_id = msi.inventory_item_id
AND    mmt.organization_id   = msi.organization_id ;

Line: 54


SELECT 'Y'
FROM   mtl_unit_transactions mut
      ,csi_item_instances cii
      ,csi_i_assets cia
      ,csi_transactions ct
WHERE  mut.transaction_id    = p_mtl_txn_id
AND    cii.inventory_item_id = mut.inventory_item_id
AND    cii.instance_id       = cia.instance_id
AND    ct.transaction_id     = p_csi_txn_id
AND    cia.creation_date    <= ct.transaction_date
AND    nvl(cia.active_end_date, ct.transaction_date + 1) >=  ct.transaction_date ;

Line: 73


SELECT  'N' --no need to post to gl
FROM    xla_transaction_entities xlte,
        xla_ae_headers xlaeh
WHERE   nvl(xlte.source_id_int_1 , '-99') =  c_mtl_transaction_id
AND     xlte.entity_code      		  =  'MTL_ACCOUNTING_EVENTS'
AND     xlte.application_id  		  =  707
and     xlte.ledger_id in (select distinct caiv.ledger_id
                           from   cst_acct_info_v caiv, mtl_transaction_accounts mta
                           where  mta.transaction_id  = c_mtl_transaction_id
                           and    mta.organization_id = caiv.organization_id )
AND   	xlaeh.entity_id       		  =  xlte.entity_id
AND   	xlaeh.gl_transfer_status_code 	  = 'NT' ;

Line: 235


SELECT ct.transaction_id,
       ct.transaction_date,
       ct.inv_material_transaction_id ,
       ct.gl_interface_status_code
FROM   csi_transactions ct
WHERE  ct.gl_interface_status_code       = 1  --'PENDING'
AND    ct.transaction_status_code        = 'COMPLETE'  ; --only those txn, which are already interfaced to FA

Line: 246


SELECT ct.inv_material_transaction_id,
       ct.transaction_type_id,
       ct.transaction_id
FROM   csi_transactions ct
WHERE  ct.gl_interface_status_code       = 1  ---'PENDING'
AND    ct.transaction_status_code        = 'COMPLETE' ;

Line: 257


SELECT citd.serial_number,
       citd.inventory_item_id ,
       citd.transaction_date
FROM   csi_inst_txn_details_v citd
      ,csi_i_assets cia
WHERE  citd.transaction_id      = c_csi_transaction_id
AND    citd.instance_id         = cia.instance_id
AND    cia.creation_date       <= citd.transaction_date
AND    nvl(cia.active_end_date, citd.transaction_date + 1) >  citd.transaction_date ;

Line: 271


SELECT 'N'
FROM   csi_inst_txn_details_v csitd
      ,csi_i_assets cia
WHERE  csitd.transaction_id = csi_pending_txn_rec.transaction_id
AND    csitd.instance_id    = cia.instance_id
AND    nvl(cia.active_end_date, sysdate) <= sysdate ;

Line: 284


SELECT  distinct caiv.ledger_id , mmt.transaction_id
FROM    cst_acct_info_v  caiv,
        mtl_transaction_accounts mta,
        mtl_material_transactions mmt
WHERE   mta.transaction_id  = c_mtl_transaction_id
AND     mta.organization_id = caiv.organization_id
AND     mmt.transaction_id  = c_mtl_transaction_id
AND     mmt.transaction_action_id <> 3
UNION
SELECT  distinct caiv.ledger_id , mmt.transaction_id
FROM    cst_acct_info_v  caiv,
        mtl_transaction_accounts mta ,
        mtl_material_transactions mmt
WHERE   mta.transaction_id  in ( c_mtl_transaction_id , c_trf_txn_id )
AND     mta.organization_id = caiv.organization_id
AND     mmt.transaction_id  in ( c_mtl_transaction_id , c_trf_txn_id )
AND     mmt.transaction_action_id = 3 ;

Line: 304


SELECT user_je_category_name
FROM   gl_je_categories
WHERE  je_category_name                 = 'MTL';

Line: 311


SELECT lookup_code
FROM   fnd_lookups
WHERE  lookup_type         = 'XLA_ACCOUNTING_CLASS'
AND    meaning             = 'INVENTORY VALUATION';

Line: 325


SELECT  xlaeh.ledger_id,
        xlaeh.je_category_name,
        xlaeh.ae_header_id,
        xlaeh.application_id,
        xlte.source_id_int_1,
        xlte.source_id_int_2
FROM    xla_transaction_entities xlte,
        xla_ae_headers xlaeh
WHERE   xlte.application_id            	=  707
AND     xlte.entity_code                =  'MTL_ACCOUNTING_EVENTS'
AND     xlte.ledger_id                  =  c_ledger_id
AND     nvl(xlte.source_id_int_1, '-99') = c_mtl_transaction_id
AND     xlte.application_id             =  xlaeh.application_id
AND     xlte.entity_id                  =  xlaeh.entity_id
AND     xlaeh.accounting_entry_status_code = 'F' ;

Line: 344


SELECT  xlael.code_combination_id ,
        xlael.accounted_dr         tot_accounted_dr,
        xlael.accounted_cr         tot_accounted_cr,
        xlael.entered_dr           tot_entered_dr,
        xlael.entered_cr           tot_entered_cr,
        xlael.currency_code ,
        xlael.accounting_class_code
FROM    xla_ae_lines xlael
WHERE   xlael.application_id      	= c_application_id
AND     xlael.ae_header_id              = c_header_id ;

Line: 383


  SELECT SYSDATE INTO l_sysdate FROM DUAL ;

Line: 434


        UPDATE csi_transactions
        SET    gl_interface_status_code      =	l_gl_interface_code
        WHERE  transaction_id                =  csi_gl_interface_code_rec.transaction_id;

Line: 452


          SELECT  mmt.inventory_item_id , mmt.transaction_date ,
                  msi.serial_number_control_code ,
                  DECODE(msi.asset_creation_code,'1', 'Y','Y','Y','N'),
                  DECODE(mmt.transaction_action_id ,
              		'2' , mmt.transfer_transaction_id ,
              		'3' , mmt.transfer_transaction_id ,
              		'28', mmt.transfer_transaction_id , mmt.transaction_id ),
                  transaction_action_id,
                  transaction_id
          INTO    l_mmt_inventory_item_id , l_mmt_transaction_date ,
                  l_serial_num_control_cd ,
                  l_depreciable ,
                  l_trf_txn_id ,
                  l_mmt_txn_action_id ,
                  l_mmt_txn_id
          FROM    mtl_material_transactions mmt , mtl_system_items msi
          WHERE   mmt.transaction_id    = csi_pending_txn_rec.inv_material_transaction_id
            AND   mmt.inventory_item_id = msi.inventory_item_id
            AND   mmt.organization_id   = msi.organization_id ;

Line: 484


          SELECT sum(nvl(ciih.old_quantity, 0) ),
                 sum(nvl(ciih.new_quantity ,0) ),
                 cii.inventory_item_id
          INTO
                 l_ciih_old_quantity ,
                 l_ciih_new_quantity ,
                 l_cii_inventory_item_id
          FROM   csi_item_instances_h ciih ,
                 csi_item_instances  cii
          WHERE  ciih.transaction_id      = csi_pending_txn_rec.transaction_id
          AND    ciih.instance_id         = cii.instance_id
          AND    cii.inventory_item_id    = l_mmt_inventory_item_id
          GROUP  BY cii.inventory_item_id , ciih.transaction_id  ;

Line: 548


	   SELECT  sum(nvl(cia.asset_quantity, 0)) into l_expired_qty
	     FROM  csi_inst_txn_details_v citd
      		  ,csi_i_assets cia
	    WHERE  citd.transaction_id      = csi_pending_txn_rec.transaction_id
              AND  citd.instance_id         = cia.instance_id
              AND  cia.creation_date       <= citd.transaction_date
              AND  nvl(cia.active_end_date, citd.transaction_date+ 1) <  citd.transaction_date ;

Line: 591


	     SELECT  sum(cia.asset_quantity)
               INTO  l_no_of_fa_items
	       FROM  csi_inst_txn_details_v citd
      		    ,csi_i_assets cia
	      WHERE  citd.transaction_id      = csi_pending_txn_rec.transaction_id
              AND    citd.instance_id         = cia.instance_id
              AND    cia.creation_date        <= citd.transaction_date
              AND    nvl(cia.active_end_date, citd.transaction_date+ 1) >=  citd.transaction_date ;

Line: 600


             SELECT   sum(cia.asset_quantity)
             INTO     l_no_of_fa_items
             FROM     csi_item_instances cii,
                      csi_transactions ct ,
                      csi_item_instances_h ciih ,
                      csi_i_assets cia
             WHERE    cii.instance_id   = ciih.instance_id
             AND      ct.transaction_id = ciih.transaction_id
             AND      ct.transaction_id = csi_pending_txn_rec.transaction_id
             AND      cia.instance_id   = cii.instance_id
             AND      cia.creation_date < ct.transaction_date
             AND      cia.active_end_date is null ;

Line: 655


                 SELECT
                      mta.gl_sl_link_id,
                      mta.ussgl_transaction_code,
                      mta.encumbrance_type_id,
                      mta.organization_id,
                      DECODE(mta.encumbrance_type_id, NULL, 'A', 'E')
                 INTO
                      l_mta_gl_sl_link_id,
                      l_mta_ussgl_transaction_code,
                      l_mta_encumbrance_type_id,
                      l_mta_organization_id,
                      l_mta_actual_flag
                 FROM
                      mtl_transaction_accounts mta
                 WHERE
                      mta.transaction_id  = xla_header_rec.source_id_int_1
                 AND  mta.organization_id = xla_header_rec.source_id_int_2
                 AND  rownum  = 1 ;

Line: 676


                     SELECT GL_INTERFACE_CONTROL_S.NEXTVAL INTO l_gl_group_id FROM DUAL ;

Line: 774


                 debug('Before Inserting Into Gl_Interface ' ) ;

Line: 776


                INSERT  INTO   GL_INTERFACE(
                            GROUP_ID,
                            STATUS,
                            SET_OF_BOOKS_ID,
                            USER_JE_SOURCE_NAME,
                            USER_JE_CATEGORY_NAME,
                            ACCOUNTING_DATE,
                            CURRENCY_CODE,
                            ACTUAL_FLAG,
                            ENCUMBRANCE_TYPE_ID,
                            DATE_CREATED,
                            CREATED_BY,
                            ENTERED_DR,
                            ENTERED_CR,
                            REFERENCE1,
                            REFERENCE2,
                            REFERENCE5,
                            REFERENCE10,
                            REFERENCE21,
                            REFERENCE22,
                            REFERENCE23,
                            CODE_COMBINATION_ID,
                            USSGL_TRANSACTION_CODE,
                            ACCOUNTED_DR,
                            ACCOUNTED_CR,
                            GL_SL_LINK_ID,
                            GL_SL_LINK_TABLE,
                            REQUEST_ID,
                            SEGMENT1,  SEGMENT2,   SEGMENT3,   SEGMENT4,   SEGMENT5,
                            SEGMENT6,  SEGMENT7,   SEGMENT8,   SEGMENT9,   SEGMENT10,
                            SEGMENT11, SEGMENT12,  SEGMENT13,  SEGMENT14,  SEGMENT15,
                            SEGMENT16, SEGMENT17,  SEGMENT18,  SEGMENT19,  SEGMENT20,
                            SEGMENT21, SEGMENT22,  SEGMENT23,  SEGMENT24,  SEGMENT25,
                            SEGMENT26, SEGMENT27,  SEGMENT28,  SEGMENT29,  SEGMENT30)
        VALUES (  l_gl_group_id
         ,DECODE( l_gl_interface_tbl(i).status ,
                                        FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).status )
         ,DECODE( l_gl_interface_tbl(i).set_of_books_id,
                                        FND_API.G_MISS_NUM,  NULL,  l_gl_interface_tbl(i).set_of_books_id)
         ,DECODE( l_gl_interface_tbl(i).user_je_source_name,
                                        FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).user_je_source_name)
         ,DECODE( l_gl_interface_tbl(i).user_je_category_name,
                                        FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).user_je_category_name)
         ,DECODE( l_gl_interface_tbl(i).accounting_date,
                                        FND_API.G_MISS_DATE, NULL, l_gl_interface_tbl(i).accounting_date)
         ,DECODE( l_gl_interface_tbl(i).currency_code ,
                                        FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).currency_code)
         ,DECODE( l_gl_interface_tbl(i).actual_flag   ,
                                        FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).actual_flag)
         ,DECODE( l_gl_interface_tbl(i).encumbrance_type_id ,
                                        FND_API.G_MISS_NUM,  NULL, l_gl_interface_tbl(i).encumbrance_type_id)
         ,DECODE( l_gl_interface_tbl(i).date_created,
                                        FND_API.G_MISS_DATE, NULL, l_gl_interface_tbl(i).date_created)
         ,DECODE( l_gl_interface_tbl(i).created_by  ,
                                        FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).created_by)
         ,DECODE( l_gl_interface_tbl(i).entered_dr  ,
                                        FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).entered_dr)
         ,DECODE( l_gl_interface_tbl(i).entered_cr  ,
                                        FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).entered_cr)
         ,DECODE( l_gl_interface_tbl(i).reference1  ,
                                        FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference1)
         ,DECODE( l_gl_interface_tbl(i).reference2  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference2)
         ,DECODE( l_gl_interface_tbl(i).reference5  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference5)
         ,DECODE( l_gl_interface_tbl(i).reference10 ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference10)
         ,DECODE( l_gl_interface_tbl(i).reference21 ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference21)
         ,DECODE( l_gl_interface_tbl(i).reference22 ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference22)
         ,DECODE( l_gl_interface_tbl(i).reference23 ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).reference23)
         ,DECODE( l_gl_interface_tbl(i).code_combination_id ,
                                        FND_API.G_MISS_NUM, NULL, l_gl_interface_tbl(i).code_combination_id)
         ,DECODE( l_gl_interface_tbl(i).ussgl_transaction_code,
                                        FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).ussgl_transaction_code)
         ,DECODE( l_gl_interface_tbl(i).accounted_dr ,
					FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).accounted_dr)
         ,DECODE( l_gl_interface_tbl(i).accounted_cr ,
					FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).accounted_cr)
         ,DECODE( l_gl_interface_tbl(i).gl_sl_link_id ,
					FND_API.G_MISS_NUM, NULL,  l_gl_interface_tbl(i).gl_sl_link_id)
         ,DECODE( l_gl_interface_tbl(i).gl_sl_link_table,
                                        FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).gl_sl_link_table)
         ,DECODE( l_gl_interface_tbl(i).request_id ,
					FND_API.G_MISS_NUM, NULL, l_gl_interface_tbl(i).request_id)
         ,DECODE( l_gl_interface_tbl(i).segment1   ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment1 )
         ,DECODE( l_gl_interface_tbl(i).segment2   ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment2 )
         ,DECODE( l_gl_interface_tbl(i).segment3   ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment3 )
         ,DECODE( l_gl_interface_tbl(i).segment4   ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment4 )
         ,DECODE( l_gl_interface_tbl(i).segment5   ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment5 )
         ,DECODE( l_gl_interface_tbl(i).segment6   ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment6 )
         ,DECODE( l_gl_interface_tbl(i).segment7   ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment7 )
         ,DECODE( l_gl_interface_tbl(i).segment8   ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment8 )
         ,DECODE( l_gl_interface_tbl(i).segment9   ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment9 )
         ,DECODE( l_gl_interface_tbl(i).segment10  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment10 )
         ,DECODE( l_gl_interface_tbl(i).segment11  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment11 )
         ,DECODE( l_gl_interface_tbl(i).segment12  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment12 )
         ,DECODE( l_gl_interface_tbl(i).segment13  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment13 )
         ,DECODE( l_gl_interface_tbl(i).segment14  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment14 )
         ,DECODE( l_gl_interface_tbl(i).segment15  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment15 )
         ,DECODE( l_gl_interface_tbl(i).segment16  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment16 )
         ,DECODE( l_gl_interface_tbl(i).segment17  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment17 )
         ,DECODE( l_gl_interface_tbl(i).segment18  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment18 )
         ,DECODE( l_gl_interface_tbl(i).segment19  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment19 )
         ,DECODE( l_gl_interface_tbl(i).segment20  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment20 )
         ,DECODE( l_gl_interface_tbl(i).segment21  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment21 )
         ,DECODE( l_gl_interface_tbl(i).segment22  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment22 )
         ,DECODE( l_gl_interface_tbl(i).segment23  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment23 )
         ,DECODE( l_gl_interface_tbl(i).segment24  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment24 )
         ,DECODE( l_gl_interface_tbl(i).segment25  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment25 )
         ,DECODE( l_gl_interface_tbl(i).segment26  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment26 )
         ,DECODE( l_gl_interface_tbl(i).segment27  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment27 )
         ,DECODE( l_gl_interface_tbl(i).segment28  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment28 )
         ,DECODE( l_gl_interface_tbl(i).segment29  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment29 )
         ,DECODE( l_gl_interface_tbl(i).segment30  ,
					FND_API.G_MISS_CHAR, NULL, l_gl_interface_tbl(i).segment30 )
         ) ;

Line: 943


         UPDATE csi_transactions
         SET    gl_interface_status_code      = 2 ---'POSTED'
         WHERE  inv_material_transaction_id   = csi_pending_txn_rec.inv_material_transaction_id;

Line: 955


         UPDATE csi_transactions
            SET gl_interface_status_code    = 3 ---'NONE'
          WHERE inv_material_transaction_id = csi_pending_txn_rec.inv_material_transaction_id;