APPS.JAI_AR_GLDIST_TRIGGER_PKG SQL Statements

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

Line: 34

|| data still lies in ja_in_temp_line_insert
|| This includes both Imported and Manual tramsactions
*/
CURSOR cur_get_temp_row
IS
SELECT
        1
FROM
        JAI_AR_TRX_INS_LINES_T
WHERE
        customer_trx_id  =  pr_new.customer_trx_id
AND     error_flag       <> 'D'; /* Modified by Ramananda for removal of SQL LITERALs */

Line: 91

              + Update CCID of Tax Lines based on GL date of Revenue Line when Revenue Recognition program runs to
              update the GL Date
********************************************************************************************************************************/




PROCEDURE BRI_T2
( pr_old t_rec%type
, pr_new t_rec%type
, pn_ccid out nocopy number /*Bug 14121914*/
, pv_action varchar2
, pv_return_code out nocopy varchar2
, pv_return_message out nocopy varchar2
)
IS
  CURSOR cur_get_ar_tax_row
  IS
  SELECT
            a.tax_id                    taxid           ,
            a.tax_rate                                  ,
            a.uom                       uom             ,
            a.tax_amount                tax_amt         ,
            b.tax_type                  t_type          ,
            a.customer_trx_line_id      line_id         ,
            a.tax_line_no               tax_line_no
    FROM
            JAI_AR_TRX_TAX_LINES          a ,
            JAI_CMN_TAXES_ALL             b
   WHERE    link_to_cust_trx_line_id = pr_new.customer_trx_line_id
     AND    a.tax_id                 = b.tax_id
     AND    NVL(b.inclusive_tax_flag,'N') = 'N'
ORDER BY    tax_line_no;

Line: 128

  SELECT
            a.tax_id                    taxid           ,
            a.tax_rate                                  ,
            a.uom                       uom             ,
            a.tax_amount                tax_amt         ,
            b.tax_type                  t_type          ,
            a.customer_trx_line_id      line_id         ,
            a.tax_line_no               tax_line_no
    FROM
            JAI_AR_TRX_TAX_LINES            a ,
            JAI_CMN_TAXES_ALL               b
   WHERE    customer_trx_line_id     = pr_new.customer_trx_line_id
   AND      a.tax_id                 = b.tax_id
   ORDER BY tax_line_no;

Line: 145

  SELECT
         regime_id   ,
         regime_code
  FROM
         jai_regime_tax_types_v      jrttv
  WHERE
         upper(jrttv.tax_type)   = upper(pv_tax_type);

Line: 158

  select to_date(attribute_value, 'DD/MM/YYYY')
  from JAI_RGM_ORG_REGNS_V
  where regime_id  = p_regime_id
  and organization_id = p_organization_id
  and location_id = p_location_id
  AND attribute_code = 'EFF_DATE_ST_PT'
  AND attribute_type_code = 'OTHERS'
  AND registration_type = 'OTHERS'
  AND (NOT EXISTS
          (select '1'
           from JAI_RGM_ORG_REGNS_V
           where regime_id  = p_regime_id
           and attribute_code IN 'INV_ORG_CLASSIFICATION'
           and attribute_value <> 'ORGANIZATION'
           and organization_id = p_organization_id
           and location_id = p_location_id)
          OR
          NOT EXISTS
          (select '1'
           from JAI_RGM_ORG_REGNS_V
           where regime_id  = p_regime_id
           and attribute_code IN 'SERVICE TYPE'
           and attribute_value <> 'OTHER'
           and organization_id = p_organization_id
           and location_id = p_location_id)
         );

Line: 187

  SELECT organization_id, location_id
  FROM jai_ar_trxs
  WHERE customer_trx_id = pr_new.CUSTOMER_TRX_ID;

Line: 195

  SELECT organization_id, location_id
  FROM   JAI_AR_TRXS
  WHERE  Customer_Trx_ID = pr_new.CUSTOMER_TRX_ID;

Line: 202

  SELECT 1
  FROM JAI_AR_TRX_INS_LINES_T
  WHERE customer_trx_id = pr_new.customer_trx_id;

Line: 219

     SELECT trx_types.type
   FROM
   ra_customer_trx_lines_all trxl
 , ra_customer_trx_all       trx
 , ra_cust_trx_types_all     trx_types
   WHERE trxl.customer_trx_id = trx.customer_trx_id
   AND trx_types.cust_trx_type_id  = trx.cust_trx_type_id
   AND trxl.customer_trx_line_id = cp_customer_trx_line_id;

Line: 237

SELECT gl_date
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = pr_new.customer_trx_id
AND account_class = 'REV';

Line: 255

  /*Bug 12792354 - Check if data exists in JAI_AR_TRX_INS_LINES_T before proceeding to update CCID*/
  OPEN c_temp_record_exists;

Line: 332

          UPDATE JAI_AR_TRX_INS_LINES_T
             SET CODE_COMBINATION_ID      = ln_ccid
           WHERE customer_trx_id          = pr_new.customer_trx_id
             AND customer_trx_line_id     = TAX_TYPE_REC.LINE_ID
             AND link_to_cust_trx_line_id = pr_new.customer_trx_line_id;

Line: 338

          /*Bug 12792354 - It is not required to error out if no records are updated*/
          /*
          IF SQL%ROWCOUNT <>1
          THEN
             pv_return_code := jai_constants.expected_error ;

Line: 352

    /*Bug 14121914 - Update CCID of the Tax Lines - Start*/
    IF pr_new.account_class = 'TAX' THEN
          FOR TAX_TYPE_REC IN cur_get_tax_row
          LOOP
            ln_regime_code := null;

Line: 436

    /*Bug 14121914 - Update CCID of the Tax Lines - End*/

EXCEPTION
  WHEN OTHERS
  THEN
    Pv_return_code     :=  jai_constants.unexpected_error;