DBA Data[Home] [Help]

APPS.IBY_CREDITCARD_PKG SQL Statements

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

Line: 14

      SELECT cc_encryption_mode
      FROM iby_sys_security_options;
Line: 55

      SELECT u.party_site_use_id
      FROM hz_party_site_uses u, hz_party_sites s
      WHERE (u.party_site_id = ci_party_site)
-- because of complexities in the payer model
-- do not require the site address to be owned by the card owner
--AND (s.party_id = NVL(ci_party_id,party_id))
        AND (u.party_site_id = s.party_site_id)
        AND (u.site_use_type = G_CC_BILLING_SITE_USE)
        AND ( NVL(u.begin_date,SYSDATE-10) < SYSDATE)
        AND ( NVL(u.end_date,SYSDATE+10) > SYSDATE);
Line: 71

      SELECT party_site_id
      FROM hz_party_sites
      WHERE (party_site_id = ci_party_site)
-- because of complexities in the payer model
-- do not require the site address to be owned by the card owner
--AND (party_id = NVL(ci_party_id,party_id))
        AND ( NVL(start_date_active,SYSDATE-10) < SYSDATE)
        AND ( NVL(end_date_active,SYSDATE+10) > SYSDATE);
Line: 123

      SELECT count(instrid)
      INTO l_encrypted_count
      FROM iby_creditcard
      WHERE (NVL(encrypted,'N')='Y');
Line: 153

     * this method will throw a 'cannot insert NULL exception ..'
     */
    SELECT iby_creditcard_h_s.NEXTVAL INTO x_history_id FROM dual;
Line: 156

    INSERT INTO iby_creditcard_h
    (card_history_change_id, instrid, expirydate, expiry_sec_segment_id,
     addressid,
     description, chname, chname_sec_segment_id, finame, security_group_id,
     encrypted,
     masked_cc_number, card_owner_id, instrument_type, purchasecard_flag,
     purchasecard_subtype, card_issuer_code, single_use_flag,
     information_only_flag, card_purpose, active_flag, inactive_date,
     attribute_category, attribute1, attribute2, attribute3, attribute4,
     attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
     attribute11, attribute12, attribute13, attribute14, attribute15,
     attribute16, attribute17, attribute18, attribute19, attribute20,
     attribute21, attribute22, attribute23, attribute24, attribute25,
     attribute26, attribute27, attribute28, attribute29, attribute30,
     request_id, program_application_id, program_id, program_update_date,
     created_by, creation_date, last_updated_by, last_update_date,
     last_update_login, object_version_number
    )
    SELECT x_history_id, instrid, expirydate, expiry_sec_segment_id,
      addressid,
      description, chname, chname_sec_segment_id, finame, security_group_id,
      encrypted,
      masked_cc_number, card_owner_id, instrument_type, purchasecard_flag,
      purchasecard_subtype, card_issuer_code, single_use_flag,
      information_only_flag, card_purpose, NVL(active_flag, 'Y'), inactive_date,
      attribute_category, attribute1, attribute2, attribute3, attribute4,
      attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
      attribute11, attribute12, attribute13, attribute14, attribute15,
      attribute16, attribute17, attribute18, attribute19, attribute20,
      attribute21, attribute22, attribute23, attribute24, attribute25,
      attribute26, attribute27, attribute28, attribute29, attribute30,
      request_id, program_application_id, program_id, program_update_date,
      fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
      fnd_global.login_id, 1
    FROM iby_creditcard
    WHERE (instrid = p_instr_id);
Line: 209

      SELECT credit_card_mask_setting, credit_card_unmask_len
      FROM iby_sys_security_options;
Line: 372

      SELECT instrid
      FROM iby_creditcard
      WHERE (cc_number_hash1 = ci_hash1)
        AND (cc_number_hash2 = ci_hash2)
        AND ( (NVL(card_owner_id,ci_owner_id) = NVL(ci_owner_id,card_owner_id))
              OR (card_owner_id IS NULL AND ci_owner_id IS NULL)
            )
        AND (NVL(single_use_flag,'N')='N');
Line: 562

    SELECT iby_instr_s.NEXTVAL INTO x_instr_id FROM DUAL;
Line: 564

    INSERT INTO iby_creditcard
    (instrid, ccnumber, masked_cc_number,
     card_mask_setting, card_unmask_length, cc_number_hash1, cc_number_hash2,
     expirydate, expiry_sec_segment_id,
     card_owner_id, chname, chname_sec_segment_id,
     addressid, billing_addr_postal_code, bill_addr_territory_code,
     instrument_type, purchasecard_flag, purchasecard_subtype,
     card_issuer_code, cc_issuer_range_id, cc_number_length,
     description, finame, encrypted, cc_num_sec_segment_id,
     single_use_flag, information_only_flag, card_purpose,
     active_flag, inactive_date,
     last_update_date, last_updated_by, creation_date,
     created_by, object_version_number,
     attribute_category,
     attribute1,attribute2, attribute3,attribute4,attribute5,
    attribute6,attribute7, attribute8,attribute9,attribute10,
    attribute11,attribute12, attribute13,attribute14,attribute15,
    attribute16,attribute17, attribute18,attribute19,attribute20,
    attribute21,attribute22, attribute23,attribute24,attribute25,
    attribute26,attribute27, attribute28,attribute29,attribute30
    -- salt_version bug 6326624
    )
    VALUES
    (x_instr_id, lx_cc_number, lx_masked_number,
     lx_mask_option, lx_unmask_len, l_cc_hash1, l_cc_hash2,
     l_expiry_date, l_expdate_sec_segment_id,
     p_owner_id, l_masked_chname, l_chname_sec_segment_id,
     l_billing_site, p_billing_zip, p_billing_country,
     p_instr_type, NVL(p_pcard_flag,'N'), p_pcard_type,
     lx_card_issuer, lx_issuer_range, l_card_len,
     p_desc, p_fi_name, l_encrypted, lx_sec_segment_id,
     NVL(p_single_use,'N'), NVL(p_info_only,'N'), p_purpose,
     NVL(p_active_flag,'Y'), p_inactive_date,
     sysdate, fnd_global.user_id, sysdate,
     fnd_global.user_id, 1,
     p_attribute_category,
     p_attribute1,p_attribute2,p_attribute3,p_attribute4,p_attribute5,
    p_attribute6,p_attribute7,p_attribute8,p_attribute9,p_attribute10,
    p_attribute11,p_attribute12,p_attribute13,p_attribute14,p_attribute15,
    p_attribute16,p_attribute17, p_attribute18,p_attribute19,p_attribute20,
    p_attribute21,p_attribute22, p_attribute23,p_attribute24,p_attribute25,
    p_attribute26,p_attribute27, p_attribute28,p_attribute29,p_attribute30
    -- iby_security_pkg.get_salt_version  bug 6326624
    );
Line: 614

  PROCEDURE Update_Card
  (p_commit           IN   VARCHAR2,
   p_instr_id         IN   iby_creditcard.instrid%TYPE,
   p_owner_id         IN   iby_creditcard.card_owner_id%TYPE,
   p_holder_name      IN   iby_creditcard.chname%TYPE,
   p_billing_address_id IN iby_creditcard.addressid%TYPE,
   p_address_type     IN   VARCHAR2,
   p_billing_zip      IN   iby_creditcard.billing_addr_postal_code%TYPE,
   p_billing_country  IN   iby_creditcard.bill_addr_territory_code%TYPE,
   p_expiry_date      IN   iby_creditcard.expirydate%TYPE,
   p_instr_type       IN   iby_creditcard.instrument_type%TYPE,
   p_pcard_flag       IN   iby_creditcard.purchasecard_flag%TYPE,
   p_pcard_type       IN   iby_creditcard.purchasecard_subtype%TYPE,
   p_fi_name          IN   iby_creditcard.finame%TYPE,
   p_single_use       IN   iby_creditcard.single_use_flag%TYPE,
   p_info_only        IN   iby_creditcard.information_only_flag%TYPE,
   p_purpose          IN   iby_creditcard.card_purpose%TYPE,
   p_desc             IN   iby_creditcard.description%TYPE,
   p_active_flag      IN   iby_creditcard.active_flag%TYPE,
   p_inactive_date    IN   iby_creditcard.inactive_date%TYPE,
  p_attribute_category IN iby_creditcard.attribute_category%TYPE,
   p_attribute1	IN 	iby_creditcard.attribute1%TYPE,
  p_attribute2	IN 	iby_creditcard.attribute2%TYPE,
   p_attribute3	IN 	iby_creditcard.attribute3%TYPE,
  p_attribute4	IN 	iby_creditcard.attribute4%TYPE,
   p_attribute5	IN 	iby_creditcard.attribute5%TYPE,
   p_attribute6	IN 	iby_creditcard.attribute6%TYPE,
   p_attribute7	IN 	iby_creditcard.attribute7%TYPE,
   p_attribute8	IN 	iby_creditcard.attribute8%TYPE,
   p_attribute9	IN 	iby_creditcard.attribute9%TYPE,
   p_attribute10	IN 	iby_creditcard.attribute10%TYPE,
   p_attribute11	IN 	iby_creditcard.attribute11%TYPE,
   p_attribute12	IN 	iby_creditcard.attribute12%TYPE,
   p_attribute13	IN 	iby_creditcard.attribute13%TYPE,
   p_attribute14	IN 	iby_creditcard.attribute14%TYPE,
   p_attribute15	IN 	iby_creditcard.attribute15%TYPE,
   p_attribute16	IN 	iby_creditcard.attribute16%TYPE,
   p_attribute17	IN 	iby_creditcard.attribute17%TYPE,
   p_attribute18	IN 	iby_creditcard.attribute18%TYPE,
   p_attribute19	IN 	iby_creditcard.attribute19%TYPE,
   p_attribute20	IN 	iby_creditcard.attribute20%TYPE,
  p_attribute21	IN 	iby_creditcard.attribute21%TYPE,
   p_attribute22	IN 	iby_creditcard.attribute22%TYPE,
   p_attribute23	IN 	iby_creditcard.attribute23%TYPE,
   p_attribute24	IN 	iby_creditcard.attribute24%TYPE,
   p_attribute25	IN 	iby_creditcard.attribute25%TYPE,
   p_attribute26	IN 	iby_creditcard.attribute26%TYPE,
   p_attribute27	IN 	iby_creditcard.attribute27%TYPE,
   p_attribute28	IN 	iby_creditcard.attribute28%TYPE,
   p_attribute29	IN 	iby_creditcard.attribute29%TYPE,
   p_attribute30	IN 	iby_creditcard.attribute30%TYPE,
   x_result_code      OUT NOCOPY VARCHAR2
  )
  IS
    l_history_id      iby_creditcard_h.card_history_change_id%TYPE;
Line: 751

    SELECT encrypted INTO l_encrypted FROM iby_creditcard
    WHERE instrid = p_instr_id;
Line: 797

    UPDATE iby_creditcard
    SET chname = DECODE(p_holder_name, FND_API.G_MISS_CHAR,NULL, NULL,chname, l_masked_chname),
      chname_sec_segment_id = l_chname_sec_segment_id,
      card_owner_id = NVL(card_owner_id,p_owner_id),
      addressid = DECODE(l_billing_site, FND_API.G_MISS_NUM,NULL,
                         NULL,addressid, l_billing_site),
      bill_addr_territory_code =
        DECODE(p_billing_country, FND_API.G_MISS_CHAR,NULL,
               NULL,bill_addr_territory_code, p_billing_country),
      billing_addr_postal_code =
        DECODE(p_billing_zip, FND_API.G_MISS_CHAR,NULL,
               NULL,billing_addr_postal_code, p_billing_zip),
    --  expirydate = NVL(p_expiry_date, expirydate),
      expirydate = DECODE(p_expiry_date, NULL, expirydate, l_expiry_date),
      expiry_sec_segment_id = DECODE(p_expiry_date, NULL, expiry_sec_segment_id,
                                        l_expdate_sec_segment_id),
      encrypted = l_encrypted,
      instrument_type = NVL(p_instr_type, instrument_type),
      purchasecard_flag = NVL(p_pcard_flag, purchasecard_flag),
      purchasecard_subtype =
        DECODE(p_pcard_type, FND_API.G_MISS_CHAR,NULL,
               NULL,purchasecard_subtype, p_pcard_type),
      finame = DECODE(p_fi_name, FND_API.G_MISS_CHAR,NULL, NULL,finame, p_fi_name),
      single_use_flag = NVL(p_single_use, single_use_flag),
      information_only_flag = NVL(p_info_only, information_only_flag),
      card_purpose = DECODE(p_purpose, FND_API.G_MISS_CHAR,NULL, NULL,card_purpose, p_purpose),
      description = DECODE(p_desc, FND_API.G_MISS_CHAR,NULL, NULL,description, p_desc),
      active_flag = NVL(p_active_flag, active_flag),
      inactive_date = DECODE(p_inactive_date, FND_API.G_MISS_DATE,NULL,
                             NULL,inactive_date, p_inactive_date),
      object_version_number = object_version_number + 1,
      last_update_date = sysdate,
      last_updated_by = fnd_global.user_id,
      last_update_login = fnd_global.login_id,
      attribute_category = p_Attribute_category,
      attribute1 = p_attribute1,
      attribute2 = p_attribute2,
      attribute3 = p_attribute3,
      attribute4 = p_attribute4,
      attribute5 = p_attribute5,
      attribute6 = p_attribute6,
      attribute7 = p_attribute7,
      attribute8 = p_attribute8,
      attribute9 = p_attribute9,
      attribute10 = p_attribute10,
      attribute11 = p_attribute11,
      attribute12 = p_attribute12,
      attribute13 = p_attribute13,
      attribute14 = p_attribute14,
      attribute15 = p_attribute15,
      attribute16 = p_attribute16,
      attribute17 = p_attribute17,
      attribute18 = p_attribute18,
      attribute19 = p_attribute19,
      attribute20 = p_attribute20,
      attribute21 = p_attribute21,
      attribute22 = p_attribute22,
      attribute23 = p_attribute23,
      attribute24 = p_attribute24,
      attribute25 = p_attribute25,
      attribute26 = p_attribute26,
      attribute27 = p_attribute27,
      attribute28 = p_attribute28,
      attribute29 = p_attribute29,
      attribute30 = p_attribute30
    WHERE (instrid = p_instr_id);
Line: 869

  END Update_Card;
Line: 892

      SELECT
        c.ccnumber, seg.segment_cipher_text,
        NVL(c.encrypted,'N'), k.subkey_cipher_text, r.card_number_prefix,
        NVL(i.digit_check_flag,'N'), c.card_mask_setting, c.card_unmask_length,
        DECODE(encrypted, 'Y',c.ccnumber, NULL),
        NVL(r.card_number_length,c.cc_number_length)
      FROM iby_creditcard c, iby_security_segments seg,
        iby_sys_security_subkeys k, iby_cc_issuer_ranges r,
        iby_creditcard_issuers_b i
      WHERE (instrid = ci_instrid)
        AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
        AND (seg.sec_subkey_id = k.sec_subkey_id(+))
        AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
        AND (r.card_issuer_code = i.card_issuer_code(+));
Line: 1011

      SELECT c.instrid, c.ccnumber, seg.segment_cipher_text,
        c.encrypted, k.subkey_cipher_text,
        NVL(r.card_number_length,c.cc_number_length) card_len,
        r.card_number_prefix, i.digit_check_flag, c.card_mask_setting,
        c.card_unmask_length, c.ccnumber unmask_digits, c.cc_num_sec_segment_id,
	c.chname, c.chname_sec_segment_id,
	c.expirydate, c.expiry_sec_segment_id
      FROM iby_creditcard c, iby_creditcard_issuers_b i,
        iby_cc_issuer_ranges r, iby_sys_security_subkeys k,
        iby_security_segments seg
      WHERE (NVL(c.encrypted,'N') = 'Y')
        AND (c.card_issuer_code = i.card_issuer_code(+))
        AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
        AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
        AND (seg.sec_subkey_id = k.sec_subkey_id(+));
Line: 1060

      UPDATE iby_creditcard
      SET
        ccnumber = l_cc_number,
        encrypted = 'N',
        cc_num_sec_segment_id = NULL,
	expirydate = l_exp_date,
        expiry_sec_segment_id = NULL,
	chname = l_chname,
        chname_sec_segment_id = NULL,
        object_version_number = object_version_number + 1,
        last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        last_update_login = fnd_global.login_id
      WHERE (instrid = c_card_rec.instrid);
Line: 1075

      DELETE iby_security_segments
      WHERE (sec_segment_id IN (c_card_rec.cc_num_sec_segment_id,
                                c_card_rec.chname_sec_segment_id,
				c_card_rec.expiry_sec_segment_id)
			       );
Line: 1109

      SELECT c.instrid, c.ccnumber, c.cc_issuer_range_id,
        k.subkey_cipher_text, r.card_number_prefix, i.digit_check_flag,
        c.card_mask_setting, c.card_unmask_length, c.chname, c.expirydate
      FROM iby_creditcard c, iby_creditcard_issuers_b i,
        iby_cc_issuer_ranges r, iby_security_segments seg,
        iby_sys_security_subkeys k
      WHERE (NVL(c.encrypted,'N') = 'N')
        AND (c.card_issuer_code = i.card_issuer_code(+))
        AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
        AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
        AND (seg.sec_subkey_id = k.sec_subkey_id(+));
Line: 1176

      UPDATE iby_creditcard
      SET
        ccnumber = NVL(lx_mask_digits,0),
        cc_num_sec_segment_id = l_segment_id,
        encrypted = 'Y',
	chname = l_masked_chname,
	chname_sec_segment_id = l_chname_sec_segment_id,
	expiry_sec_segment_id = l_expdate_sec_segment_id,
	--encrypted_date_format = l_encrypted_date_format,
	expirydate = l_exp_date,
        object_version_number = object_version_number + 1,
        last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        last_update_login = fnd_global.login_id
      WHERE (instrid = c_card_rec.instrid);
Line: 1217

      SELECT c.instrid, c.ccnumber, seg.segment_cipher_text,
        c.encrypted, k.subkey_cipher_text,
        NVL(r.card_number_length,c.cc_number_length) card_len,
        r.card_number_prefix, i.digit_check_flag, c.card_mask_setting,
        c.card_unmask_length, c.ccnumber unmask_digits, seg.sec_segment_id,
        LENGTH(c.ccnumber) len
      FROM iby_creditcard c, iby_creditcard_issuers_b i,
        iby_cc_issuer_ranges r, iby_sys_security_subkeys k,
        iby_security_segments seg
      WHERE (c.card_issuer_code = i.card_issuer_code(+))
        AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
        AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
        AND (seg.sec_subkey_id = k.sec_subkey_id(+))
        AND ( (NVL(card_unmask_length,-1) <> ci_unmask_len) OR
              (NVL(card_mask_setting,' ') <> ci_mask_option)
            );
Line: 1274

            IBY_SECURITY_PKG.Update_Segment
            (FND_API.G_FALSE,lx_segment_id,l_cc_ciphertext,
             iby_security_pkg.G_ENCODING_NUMERIC,
             p_sys_key,c_card_rec.subkey_cipher_text);
Line: 1280

          DELETE iby_security_segments WHERE (sec_segment_id = lx_segment_id);
Line: 1284

      UPDATE iby_creditcard
      SET
        ccnumber = DECODE(encrypted, 'Y',NVL(lx_mask_digits,'0'), ccnumber),
        masked_cc_number =
          Mask_Card_Number(l_cc_number,lx_mask_option,lx_unmask_len),
        cc_num_sec_segment_id = lx_segment_id,
        card_mask_setting = lx_mask_option,
        card_unmask_length = lx_unmask_len,
        object_version_number = object_version_number + 1,
        last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        last_update_login = fnd_global.login_id
      WHERE (instrid = c_card_rec.instrid);
Line: 1521

      SELECT
        c.card_owner_id, c.chname, c.addressid,
        l.address1, l.address2, l.address3, l.city, l.county,
        l.state, l.postal_code, l.country,
        c.ccnumber, c.expirydate, c.instrument_type, c.purchasecard_flag,
        c.purchasecard_subtype, c.card_issuer_code, c.finame,
        c.single_use_flag, c.information_only_flag, c.card_purpose,
        c.description, c.active_flag, c.inactive_date,
	c.encrypted, c.expiry_sec_segment_id,
	c.chname_sec_segment_id
      FROM iby_creditcard c, hz_party_site_uses su, hz_party_sites s,
        hz_locations l
      WHERE (instrid = ci_instr_id)
        AND (c.addressid = su.party_site_use_id(+))
        AND (su.party_site_id = s.party_site_id(+))
        AND (s.location_id = l.location_id(+));
Line: 1652

      SELECT instrid, expiry_sec_segment_id
      FROM iby_creditcard
      WHERE (encrypted = 'Y')
        AND (expiry_sec_segment_id IS NOT NULL)
        AND (expired_flag <> 'Y');
Line: 1672

      UPDATE iby_creditcard
      SET expired_flag = l_expired_flag
      WHERE (instrid = c_card_rec.instrid);