APPS.HZ_CREDIT_USAGES_CASCADE_PKG SQL Statements

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

Line: 56

SELECT
  cu.credit_usage_rule_set_id
, cr.global_exposure_flag
FROM HZ_CREDIT_USAGES cu
,    hz_credit_usage_rule_sets_b cr
WHERE cu.profile_class_amount_id = p_profile_class_amt_id
   AND cu.credit_usage_rule_set_id = cr.credit_usage_rule_set_id ;

Line: 95

    SELECT
      ca.cust_account_id
    , ca.site_use_id
    INTO
      l_cust_account_id
    , l_site_use_id
    FROM
      hz_cust_profile_amts ca
    WHERE ca.cust_acct_profile_amt_id = p_cust_acct_profile_amt_id;

Line: 121

          SELECT
            cp.cust_account_id
          , cp.site_use_id
          , cp.party_id
          INTO
            l_cust_account_id
          , l_site_use_id
          , l_party_id
          FROM
            hz_cust_profile_amts ca
          , hz_customer_profiles cp
          WHERE ca.cust_acct_profile_amt_id = p_cust_acct_profile_amt_id
          AND   ca.CUST_ACCOUNT_PROFILE_ID = cp.CUST_ACCOUNT_PROFILE_ID
          AND   cp.cust_account_id = -1;

Line: 181

      SELECT credit_usage_rule_id
      INTO   l_id
      FROM   hz_credit_usage_rules
      WHERE  credit_usage_rule_set_id
                 =  rule_set_csr_rec.credit_usage_rule_set_id
      AND    user_code IS NULL;

Line: 232

    HZ_CREDIT_USAGES_CASCADE_PKG.delete_credit_usages
    (  p_cust_acct_profile_amt_id => p_cust_acct_profile_amt_id
     , X_return_status            => X_return_status
     , X_msg_count                => X_msg_count
     , X_msg_data                 => X_msg_data
    );

Line: 245

    INSERT INTO HZ_CREDIT_USAGES
    (    CREDIT_USAGE_ID
       , CREDIT_PROFILE_AMT_ID
       , CUST_ACCT_PROFILE_AMT_ID
       , PROFILE_CLASS_AMOUNT_ID
       , CREDIT_USAGE_RULE_SET_ID
       , CREATION_DATE
       , CREATED_BY
       , LAST_UPDATE_DATE
       , LAST_UPDATED_BY
       , LAST_UPDATE_LOGIN
       , PROGRAM_APPLICATION_ID
       , PROGRAM_ID
       , PROGRAM_UPDATE_DATE
       , REQUEST_ID
       , ATTRIBUTE_CATEGORY
       , ATTRIBUTE1
       , ATTRIBUTE2
       , ATTRIBUTE3
       , ATTRIBUTE4
       , ATTRIBUTE5
       , ATTRIBUTE6
       , ATTRIBUTE7
       , ATTRIBUTE8
       , ATTRIBUTE9
       , ATTRIBUTE10
       , ATTRIBUTE11
       , ATTRIBUTE12
       , ATTRIBUTE13
       , ATTRIBUTE14
       , ATTRIBUTE15
    )
    SELECT
         HZ_CREDIT_USAGES_S.NEXTVAL
       , NULL
       , p_cust_acct_profile_amt_id
       , NULL
       , cu.CREDIT_USAGE_RULE_SET_ID
       , SYSDATE
       , G_user_id
       , SYSDATE
       , G_user_id
       , G_login_id
       , G_program_application_id
       , G_program_id
       , SYSDATE
       , G_request_id
       , cu.ATTRIBUTE_CATEGORY
       , cu.ATTRIBUTE1
       , cu.ATTRIBUTE2
       , cu.ATTRIBUTE3
       , cu.ATTRIBUTE4
       , cu.ATTRIBUTE5
       , cu.ATTRIBUTE6
       , cu.ATTRIBUTE7
       , cu.ATTRIBUTE8
       , cu.ATTRIBUTE9
       , cu.ATTRIBUTE10
       , cu.ATTRIBUTE11
       , cu.ATTRIBUTE12
       , cu.ATTRIBUTE13
       , cu.ATTRIBUTE14
       , cu.ATTRIBUTE15
    FROM
      HZ_CREDIT_USAGES cu
    WHERE cu.PROFILE_CLASS_AMOUNT_ID = p_profile_class_amt_id ;

Line: 327

PROCEDURE delete_credit_usages
( p_cust_acct_profile_amt_id IN NUMBER
, X_return_status            OUT NOCOPY VARCHAR2
, X_msg_count                OUT NOCOPY NUMBER
, X_msg_data                 OUT NOCOPY VARCHAR2
) IS

BEGIN
  -- Delete the Rule set assigned for a given
  -- profile amt id

  X_return_status := 'S' ;

Line: 340

  DELETE FROM
    HZ_CREDIT_USAGES
  WHERE CUST_ACCT_PROFILE_AMT_ID  =  p_cust_acct_profile_amt_id ;

Line: 347

END delete_credit_usages ;

Line: 396

CURSOR SELECT_USAGE_CURR_CSR IS
  SELECT user_code
  FROM   HZ_CREDIT_USAGE_RULES
  WHERE  credit_usage_rule_set_id = p_rule_set_id
  AND    usage_type = 'CURRENCY'
  AND    NVL (include_all_flag, 'N') = 'N'
  AND    NVL(exclude_flag,'N') = 'N' ;

Line: 410

  SELECT  cu.credit_usage_id
  ,       substrb(cur.user_code,1,5)  curr
  ,       cpa.currency_code prof_curr
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = p_cust_account_id
  AND    cp.site_use_id               = p_entity_id
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
             <= TRUNC(SYSDATE)
  AND     NVL (cur.include_all_flag, 'N') = 'N'
  AND     cur.usage_type = 'CURRENCY'
  AND     cur.user_code  IN (
          SELECT cr2.user_code from
           hz_credit_usage_rules        cr2
          WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
            AND cr2.usage_type = 'CURRENCY' and
            NVL (cr2.include_all_flag, 'N') = 'N'
           AND NVL(cr2.exclude_flag,'N') = 'N'
          )
  AND     NVL(cur.exclude_flag,'N') = 'N' ;

Line: 441

SELECT   cpa.currency_code profile_curr
  ,      cu.credit_usage_id
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = p_cust_account_id
  AND    cp.site_use_id               = p_entity_id
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
            )    <= TRUNC(SYSDATE)
  AND   NVL (cur.include_all_flag, 'N') = 'Y'
  AND   NOT EXISTS ( SELECT 'EXCLUDE'
                      FROM   hz_credit_usage_rules cur2
                      WHERE  cu.credit_usage_rule_set_id
                             = cur2.credit_usage_rule_set_id
                      AND    NVL(cur2.exclude_flag,'N') = 'Y'
                      AND    cur2.usage_type  = 'CURRENCY'
                      AND    cur2.user_code   = p_curr_code );

Line: 469

SELECT   cu.credit_usage_id
,       substrb(cur.user_code,1,5)  curr
,       cpa.currency_code prof_curr
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = p_cust_account_id
  AND    cp.site_use_id               = p_entity_id
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
             <= TRUNC(SYSDATE)
  AND     NVL (cur.include_all_flag, 'N') = 'N'
  AND     cur.usage_type = 'CURRENCY'
  AND     cur.user_code NOT IN (
          SELECT cr2.user_code from
           hz_credit_usage_rules        cr2
          WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
            AND cr2.usage_type = 'CURRENCY' and
            NVL (cr2.include_all_flag, 'N') = 'N'
           AND NVL(cr2.exclude_flag,'N') = 'Y'
          )
  AND     NVL(cur.exclude_flag,'N') = 'N' ;

Line: 500

SELECT   cpa.currency_code profile_curr
  ,      cu.credit_usage_id
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = p_cust_account_id
  AND    cp.site_use_id               = p_entity_id
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
            )    <= TRUNC(SYSDATE)
  AND   NVL (cur.include_all_flag, 'N') = 'Y' ;

Line: 529

 SELECT   cu.credit_usage_id
 ,       substrb(cur.user_code,1,5)  curr
 ,       cpa.currency_code prof_curr
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = p_entity_id
  AND    cp.site_use_id               IS NULL
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
             <= TRUNC(SYSDATE)
  AND     NVL (cur.include_all_flag, 'N') = 'N'
  AND     cur.usage_type = 'CURRENCY'
  AND     cur.user_code  IN (
          SELECT cr2.user_code from
           hz_credit_usage_rules        cr2
          WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
            AND cr2.usage_type = 'CURRENCY' and
            NVL (cr2.include_all_flag, 'N') = 'N'
           AND NVL(cr2.exclude_flag,'N') = 'N'
          )
  AND     NVL(cur.exclude_flag,'N') = 'N' ;

Line: 560

SELECT   cpa.currency_code profile_curr
  ,      cu.credit_usage_id
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = p_entity_id
  AND    cp.site_use_id               IS NULL
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
            )    <= TRUNC(SYSDATE)
  AND   NVL (cur.include_all_flag, 'N') = 'Y'
  AND   NOT EXISTS ( SELECT 'EXCLUDE'
                      FROM   hz_credit_usage_rules cur2
                      WHERE  cu.credit_usage_rule_set_id
                             = cur2.credit_usage_rule_set_id
                      AND    NVL(cur2.exclude_flag,'N') = 'Y'
                      AND    cur2.usage_type  = 'CURRENCY'
                      AND    cur2.user_code   = p_curr_code
);

Line: 589

SELECT   cu.credit_usage_id
,       substrb(cur.user_code,1,5)  curr
,       cpa.currency_code prof_curr
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = p_entity_id
  AND    cp.site_use_id               IS NULL
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
             <= TRUNC(SYSDATE)
  AND     NVL (cur.include_all_flag, 'N') = 'N'
  AND     cur.usage_type = 'CURRENCY'
  AND     cur.user_code NOT IN (
          SELECT cr2.user_code from
           hz_credit_usage_rules        cr2
          WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
            AND cr2.usage_type = 'CURRENCY' and
            NVL (cr2.include_all_flag, 'N') = 'N'
           AND NVL(cr2.exclude_flag,'N') = 'Y'
          )
  AND     NVL(cur.exclude_flag,'N') = 'N' ;

Line: 621

SELECT   cpa.currency_code profile_curr
  ,      cu.credit_usage_id
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = p_entity_id
  AND    cp.site_use_id               IS NULL
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
            )    <= TRUNC(SYSDATE)
  AND   NVL (cur.include_all_flag, 'N') = 'Y' ;

Line: 642

 SELECT   cu.credit_usage_id
 ,       substrb(cur.user_code,1,5)  curr
 ,       cpa.currency_code prof_curr
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = -1
  AND    cp.site_use_id               IS NULL
  AND    cp.party_id                  = p_entity_id
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
             <= TRUNC(SYSDATE)
  AND     NVL (cur.include_all_flag, 'N') = 'N'
  AND     cur.usage_type = 'CURRENCY'
  AND     cur.user_code  IN (
          SELECT cr2.user_code from
           hz_credit_usage_rules        cr2
          WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
            AND cr2.usage_type = 'CURRENCY' and
            NVL (cr2.include_all_flag, 'N') = 'N'
           AND NVL(cr2.exclude_flag,'N') = 'N'
          )
  AND     NVL(cur.exclude_flag,'N') = 'N' ;

Line: 674

SELECT   cpa.currency_code profile_curr
  ,      cu.credit_usage_id
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = -1
  AND    cp.site_use_id               IS NULL
  AND    cp.party_id                  = p_entity_id
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
            )    <= TRUNC(SYSDATE)
  AND   NVL (cur.include_all_flag, 'N') = 'Y'
  AND   NOT EXISTS ( SELECT 'EXCLUDE'
                      FROM   hz_credit_usage_rules cur2
                      WHERE  cu.credit_usage_rule_set_id
                             = cur2.credit_usage_rule_set_id
                      AND    NVL(cur2.exclude_flag,'N') = 'Y'
                      AND    cur2.usage_type  = 'CURRENCY'
                      AND    cur2.user_code   = p_curr_code
);

Line: 704

SELECT   cu.credit_usage_id
,       substrb(cur.user_code,1,5)  curr
,       cpa.currency_code prof_curr
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = -1
  AND    cp.site_use_id               IS NULL
  AND    cp.party_id                  = p_entity_id
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
             <= TRUNC(SYSDATE)
  AND     NVL (cur.include_all_flag, 'N') = 'N'
  AND     cur.usage_type = 'CURRENCY'
  AND     cur.user_code NOT IN (
          SELECT cr2.user_code from
           hz_credit_usage_rules        cr2
          WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
            AND cr2.usage_type = 'CURRENCY' and
            NVL (cr2.include_all_flag, 'N') = 'N'
           AND NVL(cr2.exclude_flag,'N') = 'Y'
          )
  AND     NVL(cur.exclude_flag,'N') = 'N' ;

Line: 737

SELECT   cpa.currency_code profile_curr
  ,      cu.credit_usage_id
  FROM   hz_customer_profiles         cp
  ,      hz_cust_profile_amts         cpa
  ,      hz_credit_usages             cu
  ,      hz_credit_usage_rules        cur
  WHERE  cp.cust_account_id           = -1
  AND    cp.site_use_id               IS NULL
  AND    cp.party_id                  = p_entity_id
  AND    cp.cust_account_profile_id   = cpa.cust_account_profile_id
  AND    cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
  AND    cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
  AND    cu.credit_usage_rule_set_id  = cur.credit_usage_rule_set_id
  AND    NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
            )    <= TRUNC(SYSDATE)
  AND   NVL (cur.include_all_flag, 'N') = 'Y' ;

Line: 796

         OPEN SELECT_USAGE_CURR_CSR ;

Line: 798

           FETCH SELECT_USAGE_CURR_CSR
           INTO  l_usage_curr ;

Line: 801

           IF SELECT_USAGE_CURR_CSR%NOTFOUND
           THEN
             l_usage_curr := NULL ;

Line: 835

         CLOSE SELECT_USAGE_CURR_CSR ;

Line: 929

        OPEN SELECT_USAGE_CURR_CSR ;

Line: 931

          FETCH SELECT_USAGE_CURR_CSR
          INTO  l_usage_curr ;

Line: 934

          IF SELECT_USAGE_CURR_CSR%NOTFOUND
          THEN
            l_usage_curr := NULL ;

Line: 969

        CLOSE SELECT_USAGE_CURR_CSR ;

Line: 1063

        OPEN SELECT_USAGE_CURR_CSR ;

Line: 1065

          FETCH SELECT_USAGE_CURR_CSR
          INTO  l_usage_curr ;

Line: 1068

          IF SELECT_USAGE_CURR_CSR%NOTFOUND
          THEN
            l_usage_curr := NULL ;

Line: 1103

        CLOSE SELECT_USAGE_CURR_CSR ;