APPS.JAI_JRG_OTH_TRIGGER_PKG SQL Statements

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

Line: 19

SELECT org_unit_id
  FROM JAI_CMN_INVENTORY_ORGS
 WHERE (organization_id,location_id)
    IN ( SELECT organization_id,location_id
           FROM JAI_CMN_RG_PLA_TRXS
          WHERE register_id     = pr_new.source_register_id
            AND pr_new.source_type    = 2 );

Line: 28

SELECT org_unit_id
  FROM JAI_CMN_INVENTORY_ORGS
 WHERE ( organization_id,location_id)
    IN ( SELECT organization_id,location_id
           FROM JAI_CMN_RG_23AC_II_TRXS
          WHERE register_id      = pr_new.source_register_id
            AND pr_new.source_type = 1);

Line: 38

SELECT balance
  FROM JAI_CMN_RG_OTH_BALANCES
 WHERE org_unit_id = ln_org_unit_id
   AND tax_type = pr_new.tax_type
   AND register_type = lv_register_type;

Line: 46

SELECT 1
  FROM JAI_CMN_RG_23AC_II_TRXS
 WHERE register_id = p_register_id
   AND transaction_source_num IS NULL
   AND pr_new.source_type = 1
 UNION
SELECT 1
  FROM JAI_CMN_RG_PLA_TRXS
 WHERE register_id = p_register_id
   AND ( transaction_source_num IS NULL OR tr6_source='CONSOLIDATION' or tr6_source='MANUAL') /*ADDED or tr6_source='MANUAL' for bug #5894216*/
   AND pr_new.source_type = 2;

Line: 105

PURPOSE          : To update opening balance and closing balance of JAI_CMN_RG_OTHERS and update the balance of
                   JAI_CMN_RG_OTH_BALANCES for the inserted tax type and register id. bug# 4146708 creates the objects

**********************************************************************/
  ln_balance := 0 ;

Line: 119

  SELECT DECODE(pr_new.source_register,'RG23A_P2','RG23A','RG23C_P2','RG23C','PLA','PLA')
    INTO lv_register_type
    FROM dual;

Line: 124

     /* Insertion into this table from JAI_IN_RG23D, JAI_RCV_CENVAT_CLAIMS . No need of balances calculation in this case*/
     return;

Line: 143

  SELECT count(1)
    INTO ln_balance_cnt
    FROM JAI_CMN_RG_OTH_BALANCES
   WHERE org_unit_id   = ln_org_unit_id
     AND tax_type      = pr_new.tax_type
     AND register_type = lv_register_type;

Line: 155

    UPDATE JAI_CMN_RG_OTH_BALANCES
       SET tax_type    = tax_type
     WHERE org_unit_id = ln_org_unit_id
       AND tax_type    = pr_new.tax_type
       AND register_type = lv_register_type;

Line: 174

    INSERT INTO JAI_CMN_RG_OTH_BALANCES( org_unit_id      ,
                                     tax_type         ,
                                     balance          ,
                                     register_type    ,
                                     created_by       ,
                                     creation_date    ,
                                     last_updated_by  ,
                                     last_update_date ,
                                     last_update_login)
                             VALUES( ln_org_unit_id     ,
                                     pr_new.tax_type      ,
                                     0                  ,
                                     lv_register_type   ,
                                     fnd_global.user_id ,
                                     sysdate            ,
                                     fnd_global.user_id ,
                                     sysdate            ,
                                     fnd_global.login_id
                                    );

Line: 210

 UPDATE JAI_CMN_RG_OTH_BALANCES
    SET balance           = pr_new.closing_balance,
        last_updated_by   = fnd_global.user_id,
  last_update_date  = sysdate,
        last_update_login = fnd_global.login_id
  WHERE org_unit_id   = ln_org_unit_id
    AND tax_type      = pr_new.tax_type
    AND register_type = lv_register_type;