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.IBY_FNDCPT_SETUP_PUB SQL Statements

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

Line: 107


    SELECT payment_channel_code
    FROM iby_fndcpt_pmt_chnnls_b
    WHERE (payment_channel_code = ci_channel_code)
      AND (NVL(inactive_date,SYSDATE-10)


Line: 133

      SELECT COUNT(instrid)
      FROM iby_creditcard
      WHERE (instrid = ci_instrid);

Line: 140

      SELECT COUNT(ext_bank_account_id)
      FROM iby_ext_bank_accounts
      WHERE (ext_bank_account_id = ci_instrid);

Line: 175

  ( p_is_update IN VARCHAR2, p_creditcard IN CreditCard_rec_type )
  RETURN BOOLEAN
  IS

    lx_return_status  VARCHAR2(1);

Line: 199

    IF FND_API.to_Boolean(p_is_update) THEN
      IF (l_addressid = FND_API.G_MISS_NUM) THEN
        l_addressid := NULL;

Line: 278

    SELECT ext_payer_id, bank_charge_bearer_code, dirdeb_instruction_code
    FROM iby_external_payers_all
    WHERE (payment_function = ci_pmt_function)
      AND (party_id = ci_party_id)
      AND ((cust_account_id = ci_account_id)
        OR (cust_account_id IS NULL AND ci_account_id IS NULL))
      AND ((org_type = ci_org_type AND org_id = ci_org_id)
        OR (org_type IS NULL AND org_id IS NULL AND ci_org_type IS NULL AND ci_org_id IS NULL))
      AND ((acct_site_use_id = ci_site_id)
        OR (acct_site_use_id IS NULL AND ci_site_id IS NULL));

Line: 361

      SELECT iby_external_payers_all_s.nextval
      INTO x_payer_attribs_id
      FROM dual;

Line: 365

      INSERT INTO iby_external_payers_all
      (ext_payer_id, payment_function, party_id, org_type, org_id,
       cust_account_id, acct_site_use_id, bank_charge_bearer_code,
       dirdeb_instruction_code, created_by, creation_date, last_updated_by,
       last_update_date, last_update_login, object_version_number,
       debit_advice_delivery_method, debit_advice_email, debit_advice_fax
       )
      VALUES
      (x_payer_attribs_id, p_payer.Payment_Function,
       p_payer.Party_Id, p_payer.Org_Type, p_payer.Org_Id,
       p_payer.Cust_Account_Id, p_payer.Account_Site_Id,
       p_payer_attributes.Bank_Charge_Bearer,
       p_payer_attributes.DirectDebit_BankInstruction,
       fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
       fnd_global.login_id, 1 ,p_payer.Debit_Advice_Delivery_Method,
       p_payer.Debit_Advice_Email, p_payer.Debit_Advice_Fax
      );

Line: 383

      UPDATE iby_external_payers_all
      SET
        dirdeb_instruction_code =
          DECODE(p_payer_attributes.DirectDebit_BankInstruction,
                 FND_API.G_MISS_CHAR,NULL, p_payer_attributes.DirectDebit_BankInstruction),
        bank_charge_bearer_code =
          DECODE(p_payer_attributes.Bank_Charge_Bearer,
                 FND_API.G_MISS_CHAR,NULL, p_payer_attributes.Bank_Charge_Bearer),
        last_updated_by =  fnd_global.user_id,
        last_update_date = SYSDATE,
        last_update_login = fnd_global.login_id,
        object_version_number = object_version_number + 1,
		debit_advice_delivery_method = p_payer.Debit_Advice_Delivery_Method,
		debit_advice_email = p_payer.Debit_Advice_Email,
		debit_advice_fax = p_payer.Debit_Advice_Fax
      WHERE (ext_payer_id = x_payer_attribs_id);

Line: 550

      SELECT NVL(isec.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
        NVL(ibill.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
        NVL(vaflag.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
        NVL(vacode.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
        NVL(vadate.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
        NVL(ponum.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
        NVL(poline.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
        NVL(addinfo.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL)
      FROM iby_fndcpt_pmt_chnnls_b pc, iby_pmt_mthd_attrib_appl isec,
        iby_pmt_mthd_attrib_appl ibill, iby_pmt_mthd_attrib_appl vaflag,
        iby_pmt_mthd_attrib_appl vacode, iby_pmt_mthd_attrib_appl vadate,
        iby_pmt_mthd_attrib_appl ponum, iby_pmt_mthd_attrib_appl poline,
        iby_pmt_mthd_attrib_appl addinfo
      WHERE (pc.payment_channel_code = ci_pmt_channel)
        -- instrument security
        AND (pc.payment_channel_code = isec.payment_method_code(+))
        AND (isec.payment_flow(+) = 'FUNDS_CAPTURE')
        AND (isec.attribute_code(+) = 'INSTR_SECURITY_CODE')
        -- instrument billing address
        AND (pc.payment_channel_code = ibill.payment_method_code(+))
        AND (ibill.attribute_code(+) = 'INSTR_BILLING_ADDRESS')
        AND (ibill.payment_flow(+) = 'FUNDS_CAPTURE')
        -- voice auth flag
        AND (pc.payment_channel_code = vaflag.payment_method_code(+))
        AND (vaflag.attribute_code(+) = 'VOICE_AUTH_FLAG')
        AND (vaflag.payment_flow(+) = 'FUNDS_CAPTURE')
        -- voice auth code
        AND (pc.payment_channel_code = vacode.payment_method_code(+))
        AND (vacode.attribute_code(+) = 'VOICE_AUTH_CODE')
        AND (vacode.payment_flow(+) = 'FUNDS_CAPTURE')
        -- voice auth date
        AND (pc.payment_channel_code = vadate.payment_method_code(+))
        AND (vadate.attribute_code(+) = 'VOICE_AUTH_DATE')
        AND (vadate.payment_flow(+) = 'FUNDS_CAPTURE')
        -- purcharse order number
        AND (pc.payment_channel_code = ponum.payment_method_code(+))
        AND (ponum.attribute_code(+) = 'PO_NUMBER')
        AND (ponum.payment_flow(+) = 'FUNDS_CAPTURE')
        -- purchase order line
        AND (pc.payment_channel_code = poline.payment_method_code(+))
        AND (poline.attribute_code(+) = 'PO_LINE_NUMBER')
        AND (poline.payment_flow(+) = 'FUNDS_CAPTURE')
        -- additional info
        AND (pc.payment_channel_code = addinfo.payment_method_code(+))
        AND (addinfo.attribute_code(+) = 'ADDITIONAL_INFO')
        AND (addinfo.payment_flow(+) = 'FUNDS_CAPTURE');

Line: 705

      SELECT ext_party_pmt_mthd_id
      FROM iby_ext_party_pmt_mthds
      WHERE (ext_pmt_party_id = ci_payer_id)
        AND (payment_flow = G_PMT_FLOW_FNDCPT)
        AND (primary_flag = 'Y')
        AND (NVL(inactive_date,SYSDATE-10)

Line: 772

        SELECT iby_ext_party_pmt_mthds_s.NEXTVAL
        INTO x_assignment_id
        FROM DUAL;

Line: 776

        INSERT INTO iby_ext_party_pmt_mthds
        (ext_party_pmt_mthd_id, payment_method_code, payment_flow,
        ext_pmt_party_id, payment_function, primary_flag, inactive_date,
        created_by, creation_date, last_updated_by, last_update_date,
        last_update_login, object_version_number)
        VALUES
        (x_assignment_id, p_channel_assignment.Pmt_Channel_Code,
        G_PMT_FLOW_FNDCPT, l_payer_id, p_payer.Payment_Function, 'Y',
        p_channel_assignment.Inactive_Date, fnd_global.user_id, SYSDATE,
        fnd_global.user_id, SYSDATE, fnd_global.login_id, 1);

Line: 789

        UPDATE iby_ext_party_pmt_mthds
        SET inactive_date = p_channel_assignment.Inactive_Date,
          payment_method_code =
            NVL(p_channel_assignment.Pmt_Channel_code,payment_method_code),
          last_updated_by =  fnd_global.user_id,
          last_update_date = SYSDATE,
          last_update_login = fnd_global.login_id,
          object_version_number = object_version_number + 1
        WHERE ext_party_pmt_mthd_id = x_assignment_id;

Line: 873

      SELECT payment_method_code, primary_flag, inactive_date
      FROM iby_ext_party_pmt_mthds
      WHERE (ext_pmt_party_id = ci_payer_id)
        AND (payment_flow = G_PMT_FLOW_FNDCPT)
        AND (primary_flag = 'Y')
        AND (NVL(inactive_date,SYSDATE-10)

Line: 996

      SELECT c.payment_channel_code, c.instrument_type
      FROM iby_ext_party_pmt_mthds pm, iby_fndcpt_pmt_chnnls_b c
      WHERE (pm.payment_method_code = c.payment_channel_code)
        AND (NVL(pm.inactive_date,SYSDATE-10)

Line: 1131

    l_last_update  DATE;

Line: 1149

      SELECT instrument_payment_use_id, instrument_type
      FROM iby_pmt_instr_uses_all
      WHERE (payment_flow = G_PMT_FLOW_FNDCPT)
        -- [lmallick] - bug# 12570664
	-- This filter condition on payer_id should be fired always!
	-- Adding this as a mandatory filter condition and commenting
	-- out the condition within the 'OR' block.
        AND (ext_pmt_party_id = ci_payer_id)
        AND ( (instrument_payment_use_id = NVL(ci_assign_id,-1))
              OR (-- ext_pmt_party_id = ci_payer_id AND
	                instrument_type = ci_instr_type
                    AND instrument_id = ci_instr_id
                 )
            );

Line: 1170

      SELECT instrument_id, instrument_type
      FROM iby_pmt_instr_uses_all
      WHERE instrument_payment_use_id = ci_assign_id;

Line: 1180

      SELECT count(*)
      FROM IBY_ACCOUNT_OWNERS
      WHERE EXT_BANK_ACCOUNT_ID = ci_instr_id
        AND ACCOUNT_OWNER_PARTY_ID = ci_party_id;

Line: 1327

	  SELECT NVL(MAX(order_of_preference),0)+1 INTO l_priority
	  FROM iby_pmt_instr_uses_all instr
	  WHERE instr.ext_pmt_party_id = l_payer_id
	  AND instr.instrument_type    = l_instrtype
	  AND instr.payment_flow       = G_PMT_FLOW_FNDCPT;

Line: 1346

		    UPDATE iby_pmt_instr_uses_all
            SET order_of_preference = order_of_preference + 1,
                last_updated_by =  fnd_global.user_id,
                last_update_date = trunc(SYSDATE),
                last_update_login = fnd_global.login_id,
                object_version_number = object_version_number + 1
            WHERE ext_pmt_party_id = l_payer_id
            AND payment_flow = G_PMT_FLOW_FNDCPT
			AND instrument_type = l_instrtype
            AND order_of_preference >= l_priority;

Line: 1362

      l_last_update := SYSDATE;

Line: 1365

        SELECT iby_pmt_instr_uses_all_s.nextval
        INTO x_assign_id
        FROM DUAL;

Line: 1369

        INSERT INTO iby_pmt_instr_uses_all
          (instrument_payment_use_id, ext_pmt_party_id, instrument_type,
           instrument_id, payment_function, payment_flow, order_of_preference,
           debit_auth_flag, debit_auth_method, debit_auth_reference,
           debit_auth_begin, debit_auth_end, start_date, end_date,
           created_by, creation_date, last_updated_by, last_update_date,
           last_update_login, object_version_number)
        VALUES
          (x_assign_id, l_payer_id,
           p_assignment_attribs.Instrument.Instrument_Type,
           p_assignment_attribs.Instrument.Instrument_Id,
           p_payer.Payment_Function, G_PMT_FLOW_FNDCPT, l_priority,
           null, null, null, null, null,
           NVL(p_assignment_attribs.Start_Date,SYSDATE),
           p_assignment_attribs.End_Date,
           fnd_global.user_id, SYSDATE, fnd_global.user_id, l_last_update,
           fnd_global.login_id, 1);

Line: 1389

        UPDATE iby_pmt_instr_uses_all
          SET
            order_of_preference =
              NVL(p_assignment_attribs.Priority,order_of_preference),
            start_date = NVL(p_assignment_attribs.Start_Date,start_date),
            end_date = p_assignment_attribs.End_Date,
            last_updated_by =  fnd_global.user_id,
            last_update_date = l_last_update,
            last_update_login = fnd_global.login_id,
            object_version_number = object_version_number + 1
        WHERE instrument_payment_use_id = x_assign_id;

Line: 1416

      SELECT instrument_type
      INTO l_instr_type
      FROM iby_pmt_instr_uses_all
      WHERE instrument_payment_use_id = x_assign_id;

Line: 1422

        SELECT party_type INTO l_party_type
        FROM hz_parties WHERE party_id = p_payer.Party_Id;

Line: 1431

         p_child_update_dt     => l_last_update,
         p_parent_bo_code      => l_parent_type,
         p_parent_tbl_name     => l_parent_table,
         p_parent_id           => l_parent_id,
         p_parent_opr_flag     => NULL,
         p_gparent_bo_code     => l_party_type,
         p_gparent_tbl_name    => 'HZ_PARTIES',
         p_gparent_id          => p_payer.Party_Id,
         x_return_status       => x_return_status,
         x_msg_count           => x_msg_count,
         x_msg_data            => x_msg_data
         );

Line: 1518

      SELECT instrument_payment_use_id, instrument_type, instrument_id,
        order_of_preference, start_date, end_date
      FROM iby_pmt_instr_uses_all
      WHERE (payment_flow = G_PMT_FLOW_FNDCPT)
        AND (ext_pmt_party_id = ci_payer_id);

Line: 1636

      SELECT DISTINCT u.instrument_type, u.instrument_id
      FROM iby_pmt_instr_uses_all u, iby_external_payers_all p
      WHERE (u.payment_flow = G_PMT_FLOW_FNDCPT)
        AND (u.ext_pmt_party_id = p.ext_payer_id)
        AND (p.party_id = ci_party_id);

Line: 1760

 SELECT X.INSTRUMENT_PAYMENT_USE_ID,
        X.INSTRUMENT_TYPE,
        X.INSTRUMENT_ID,
        X.ORDER_OF_PREFERENCE,
        X.START_DATE,
        X.END_DATE,
        X.ACCT_SITE_USE_ID,
        X.CUST_ACCOUNT_ID
     FROM
    (
	  SELECT INSTRUMENT_PAYMENT_USE_ID,
	         INSTRUMENT_TYPE,
	         INSTRUMENT_ID,
	         ORDER_OF_PREFERENCE,
	         START_DATE,
	         END_DATE,
	         ACCT_SITE_USE_ID,
	         CUST_ACCOUNT_ID,
	        RANK() OVER (PARTITION BY INSTRUMENT_TYPE, INSTRUMENT_ID
	                     ORDER BY ACCT_SITE_USE_ID, CUST_ACCOUNT_ID,ORDER_OF_PREFERENCE,INSTRUMENT_PAYMENT_USE_ID) DUP_RANK
	  FROM
	       IBY_PMT_INSTR_USES_ALL inst,
	       IBY_EXTERNAL_PAYERS_ALL payer
	  WHERE
	       (inst.PAYMENT_FLOW = G_PMT_FLOW_FNDCPT)
	       AND inst.INSTRUMENT_TYPE = NVL(ci_instrument_type ,INSTRUMENT_TYPE)
	       AND SYSDATE >= inst.START_DATE
	       AND SYSDATE < NVL(inst.END_DATE, SYSDATE+1)
	       AND (payer.PAYMENT_FUNCTION =  ci_payer.Payment_Function)
	       AND (payer.PARTY_ID = ci_payer.Party_Id )
	       AND inst.EXT_PMT_PARTY_ID = payer.ext_payer_id
	       and
		  (IBY_FNDCPT_COMMON_PUB.COMPARE_PAYER (ci_payer.org_type, ci_payer.org_id,
		   ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
		   ci_payer_level,ci_payer_equiv ,payer.ORG_TYPE,payer.ORG_ID,
		  payer.CUST_ACCOUNT_ID, payer.ACCT_SITE_USE_ID) = 'T')
   ) X,
       -- [lmallick]: bug# 12570664
       -- This API shouln't be returning the assignments that correspond
       -- to inactive instruments. Added the necessary filter conditions
       -- by joining with iby_creditcard and iby_ext_bank_accounts.
     iby_creditcard c,
     iby_ext_bank_accounts b
  WHERE  X.DUP_RANK = 1
         AND c.instrid(+) = x.instrument_id
         AND b.ext_bank_account_id(+) = x.instrument_id
         AND NVL(c.inactive_date, sysdate+10) > sysdate
         AND NVL(b.start_date, sysdate-10) <= sysdate
         AND NVL(b.end_date, sysdate+10) > sysdate
   ORDER BY
   ACCT_SITE_USE_ID,
   CUST_ACCOUNT_ID,
   ORDER_OF_PREFERENCE;

Line: 2076

      SELECT cc_encryption_mode
      FROM iby_sys_security_options;

Line: 2340

      iby_creditcard_pkg.Update_Card
      (FND_API.G_FALSE, lx_card_rec.Card_Id, p_card_instrument.Owner_Id,
       p_card_instrument.Card_Holder_Name,
       p_card_instrument.Billing_Address_Id,
       p_card_instrument.Address_Type,
       p_card_instrument.Billing_Postal_Code,
       p_card_instrument.Billing_Address_Territory,
       p_card_instrument.Expiration_Date, p_card_instrument.Instrument_Type,
       p_card_instrument.PurchaseCard_Flag, p_card_instrument.PurchaseCard_SubType,
       p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
       l_info_only, p_card_instrument.Card_Purpose,
       p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
       NVL(p_card_instrument.Inactive_Date,FND_API.G_MISS_DATE),
     p_card_instrument.attribute_category,
     p_card_instrument.attribute1,  p_card_instrument.attribute2,
     p_card_instrument.attribute3,  p_card_instrument.attribute4,
     p_card_instrument.attribute5,  p_card_instrument.attribute6,
     p_card_instrument.attribute7,  p_card_instrument.attribute8,
     p_card_instrument.attribute9,  p_card_instrument.attribute10,
     p_card_instrument.attribute11,  p_card_instrument.attribute12,
     p_card_instrument.attribute13,  p_card_instrument.attribute14,
     p_card_instrument.attribute15,  p_card_instrument.attribute16,
     p_card_instrument.attribute17,  p_card_instrument.attribute18,
     p_card_instrument.attribute19,  p_card_instrument.attribute20,
     p_card_instrument.attribute21,  p_card_instrument.attribute22,
     p_card_instrument.attribute23,  p_card_instrument.attribute24,
     p_card_instrument.attribute25,  p_card_instrument.attribute26,
     p_card_instrument.attribute27,  p_card_instrument.attribute28,
     p_card_instrument.attribute29,  p_card_instrument.attribute30,
     lx_result_code,
     null,null);

Line: 2428

  PROCEDURE Update_Card_Wrapper
      (
              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,
              x_return_status    OUT  NOCOPY VARCHAR2
      )
  IS
        -- create a record type and populate it
        x_response         IBY_FNDCPT_COMMON_PUB.Result_rec_type;

Line: 2542

        Update_Card(1.0,
            FND_API.G_FALSE,
            p_commit,
            x_return_status,
            x_msg_count,
            x_msg_data,
            x_card_instrument,
            x_response);

Line: 2553

  END Update_Card_Wrapper;

Line: 2555

  PROCEDURE Update_Card
            (
            p_api_version      IN   NUMBER,
            p_init_msg_list    IN   VARCHAR2  := FND_API.G_FALSE,
            p_commit           IN   VARCHAR2  := FND_API.G_TRUE,
            x_return_status    OUT NOCOPY VARCHAR2,
            x_msg_count        OUT NOCOPY NUMBER,
            x_msg_data         OUT NOCOPY VARCHAR2,
            p_card_instrument  IN   CreditCard_rec_type,
            x_response         OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
            )
  IS

    l_api_version  CONSTANT  NUMBER := 1.0;

Line: 2569

    l_module       CONSTANT  VARCHAR2(30) := 'Update_Card';

Line: 2595

    SAVEPOINT Update_Card;

Line: 2609

    iby_creditcard_pkg.Update_Card
    (FND_API.G_FALSE, p_card_instrument.Card_Id, p_card_instrument.Owner_Id,
     p_card_instrument.Card_Holder_Name,
     p_card_instrument.Billing_Address_Id,
     p_card_instrument.Address_Type,
     p_card_instrument.Billing_Postal_Code,
     p_card_instrument.Billing_Address_Territory,
     p_card_instrument.Expiration_Date, p_card_instrument.Instrument_Type,
     p_card_instrument.PurchaseCard_Flag, p_card_instrument.PurchaseCard_SubType,
     p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
     l_info_only, p_card_instrument.Card_Purpose,
     p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
     p_card_instrument.Inactive_Date,
     p_card_instrument.attribute_category,
     p_card_instrument.attribute1,  p_card_instrument.attribute2,
     p_card_instrument.attribute3,  p_card_instrument.attribute4,
     p_card_instrument.attribute5,  p_card_instrument.attribute6,
     p_card_instrument.attribute7,  p_card_instrument.attribute8,
     p_card_instrument.attribute9,  p_card_instrument.attribute10,
     p_card_instrument.attribute11,  p_card_instrument.attribute12,
     p_card_instrument.attribute13,  p_card_instrument.attribute14,
     p_card_instrument.attribute15,  p_card_instrument.attribute16,
     p_card_instrument.attribute17,  p_card_instrument.attribute18,
     p_card_instrument.attribute19,  p_card_instrument.attribute20,
     p_card_instrument.attribute21,  p_card_instrument.attribute22,
     p_card_instrument.attribute23,  p_card_instrument.attribute24,
     p_card_instrument.attribute25,  p_card_instrument.attribute26,
     p_card_instrument.attribute27,  p_card_instrument.attribute28,
     p_card_instrument.attribute29,  p_card_instrument.attribute30,
     lx_result_code,
     p_card_instrument.Register_Invalid_Card,
     p_card_instrument.Card_Issuer);

Line: 2654

        ROLLBACK TO Update_Card;

Line: 2663

        ROLLBACK TO Update_Card;

Line: 2672

        ROLLBACK TO Update_Card;

Line: 2695

  END Update_Card;

Line: 2717

      SELECT card_owner_id, chname, addressid, masked_cc_number,
        expirydate, DECODE(expirydate, null,expired_flag, decode(sign(expirydate-sysdate),-1,'Y','N')),
	instrument_type,purchasecard_subtype, card_issuer_code, finame, single_use_flag,
        information_only_flag, card_purpose, description, inactive_date
      FROM iby_creditcard
      WHERE (instrid = ci_card_id);

Line: 2845

      SELECT instrid
      FROM iby_creditcard
      WHERE (cc_number_hash1 = ci_cc_hash1)
        AND (cc_number_hash2 = ci_cc_hash2)
        AND ( (card_owner_id = NVL(ci_card_owner,card_owner_id))
          OR (card_owner_id IS NULL AND ci_card_owner IS NULL) ); --Removed singleUseFlag validation to avoid duplicate singleusecard creation.

Line: 3136

    SELECT bank_charge_bearer_code, dirdeb_instruction_code
      FROM iby_external_payers_all p
     WHERE p.party_id = ci_payer.Party_Id
       AND IBY_FNDCPT_COMMON_PUB.Compare_Payer
           (ci_payer.org_type, ci_payer.org_id,
           ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
           ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
           p.cust_account_id,p.acct_site_use_id) = 'T'
  ORDER BY p.acct_site_use_id, p.cust_account_id, p.org_id;

Line: 3188

       SELECT expirydate, expiry_sec_segment_id
       INTO l_expiry_date, l_exp_sec_segment_id
       FROM iby_creditcard
       WHERE instrid = p_instrid;

Line: 3227

    SELECT NVL(encryption_patch_level, G_ENC_PATCH_LEVEL_NORMAL)
    INTO enc_level
    FROM iby_sys_security_options;

Line: 3282

	INSERT INTO IBY_DEBIT_AUTHORIZATIONS
		(DEBIT_AUTHORIZATION_ID,
		EXTERNAL_BANK_ACCOUNT_USE_ID, AUTHORIZATION_REFERENCE_NUMBER,
		INITIAL_DEBIT_AUTHORIZATION_ID, AUTHORIZATION_REVISION_NUMBER,
		PAYMENT_TYPE_CODE,AMENDMENT_REASON_CODE,
		AUTH_SIGN_DATE,AUTH_CANCEL_DATE,DEBIT_AUTH_METHOD,
		PRE_NOTIFICATION_REQUIRED_FLAG,CREDITOR_LEGAL_ENTITY_ID,
		CREDITOR_LE_NAME,DEBIT_AUTH_BEGIN,created_by,
		creation_date, last_updated_by, last_update_date,
		last_update_login, object_version_number,CUST_ADDR_ID,
		DEBIT_AUTH_FLAG,DEBIT_AUTH_REFERENCE,CUST_IDENTIFICATION_CODE,
		CREDITOR_IDENTIFIER,DEBIT_AUTH_END, CURR_REC_INDI,MANDATE_FILE)

		VALUES

		(p_debit_auth_id,
		p_bank_use_id, p_auth_ref_number,
		p_initial_debit_auth_id,p_auth_rev_number,
		p_payment_code,p_amend_readon_code,
		p_auth_sign_date,p_auth_cancel_date,p_debit_auth_method,
		p_pre_notif_flag,p_creditor_id,
		p_creditor_name,p_debit_auth_begin,fnd_global.user_id,
		SYSDATE, fnd_global.user_id, SYSDATE,
		fnd_global.login_id, 1,p_cust_addr_id,
		p_debit_auth_flag,p_debit_auth_ref,p_cust_id_code,
		p_creditor_identifer,p_debit_auth_end, 'Y', p_mandate_file);

Line: 3314

print_debuginfo('Exception occured while inserting the mandate:' ||
	sqlerrm,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);

Line: 3321

PROCEDURE Update_Debit_Authorization
  (p_debit_auth_id IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTHORIZATION_ID%TYPE,
   p_bank_use_id IN IBY_DEBIT_AUTHORIZATIONS.EXTERNAL_BANK_ACCOUNT_USE_ID%TYPE,
   p_auth_ref_number IN IBY_DEBIT_AUTHORIZATIONS.AUTHORIZATION_REFERENCE_NUMBER%TYPE,
   p_initial_debit_auth_id IN IBY_DEBIT_AUTHORIZATIONS.INITIAL_DEBIT_AUTHORIZATION_ID%TYPE,
   p_auth_rev_number IN IBY_DEBIT_AUTHORIZATIONS.AUTHORIZATION_REVISION_NUMBER%TYPE,
   p_payment_code IN IBY_DEBIT_AUTHORIZATIONS.PAYMENT_TYPE_CODE%TYPE,
   p_amend_readon_code IN IBY_DEBIT_AUTHORIZATIONS.AMENDMENT_REASON_CODE%TYPE,
   p_auth_sign_date IN IBY_DEBIT_AUTHORIZATIONS.AUTH_SIGN_DATE%TYPE,
   p_auth_cancel_date IN IBY_DEBIT_AUTHORIZATIONS.AUTH_CANCEL_DATE%TYPE,
   p_debit_auth_method IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_METHOD%TYPE,
   p_pre_notif_flag IN IBY_DEBIT_AUTHORIZATIONS.PRE_NOTIFICATION_REQUIRED_FLAG%TYPE,
   p_creditor_id IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LEGAL_ENTITY_ID%TYPE,
   p_creditor_name IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LE_NAME%TYPE,
   p_debit_auth_begin IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_BEGIN%TYPE,
   p_cust_addr_id IN IBY_DEBIT_AUTHORIZATIONS.CUST_ADDR_ID%TYPE,
   p_debit_auth_flag IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_FLAG%TYPE,
   p_debit_auth_ref IN  IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_REFERENCE%TYPE,
   p_cust_id_code IN IBY_DEBIT_AUTHORIZATIONS.CUST_IDENTIFICATION_CODE%TYPE,
   p_creditor_identifer IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_IDENTIFIER%TYPE,
   p_debit_auth_end IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_END%TYPE,
   p_mandate_file IN IBY_DEBIT_AUTHORIZATIONS.MANDATE_FILE%TYPE,
   x_result OUT NOCOPY NUMBER)
IS
l_module       CONSTANT  VARCHAR2(30) := 'Update_Debit_Authorization';

Line: 3359

(SELECT TRXNMID FROM IBY_TRXN_SUMMARIES_ALL WHERE DEBIT_AUTHORIZATION_ID = AUTH_ID
AND REQTYPE = 'ORAPMTBATCHREQ' AND TRXNTYPEID = 100 AND INSTRTYPE = 'BANKACCOUNT' AND STATUS = 100);

Line: 3379

	SELECT AUTHORIZATION_REFERENCE_NUMBER, CREDITOR_LE_NAME, CREDITOR_IDENTIFIER
		into l_auth_ref_number, l_creditor_name, l_creditor_identifer
		FROM IBY_DEBIT_AUTHORIZATIONS
		WHERE
		DEBIT_AUTHORIZATION_ID = p_debit_auth_id;

Line: 3406

		UPDATE IBY_DEBIT_AUTHORIZATIONS SET DEBIT_AUTH_END = SYSDATE,
		CURR_REC_INDI = 'N'
		WHERE DEBIT_AUTHORIZATION_ID = p_debit_auth_id;

Line: 3410

		select IBY_DEBIT_AUTHORIZATIONS_S.nextval into l_seq_number from dual;

Line: 3445

        UPDATE IBY_TRXN_SUMMARIES_ALL SET DEBIT_AUTHORIZATION_ID = l_seq_number where debit_authorization_id = p_debit_auth_id and trxnmid = trxn_rec.trxnmid;

Line: 3448

             print_debuginfo('Trxn Id : ' || trxn_rec.trxnmid || 'with debit-authorization_id = ' || p_debit_auth_id || 'updated with debit auth : ' || l_seq_number,
               iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);

Line: 3472

		UPDATE IBY_DEBIT_AUTHORIZATIONS
		SET
		EXTERNAL_BANK_ACCOUNT_USE_ID = p_bank_use_id,
		AUTHORIZATION_REFERENCE_NUMBER = p_auth_ref_number,
		INITIAL_DEBIT_AUTHORIZATION_ID = p_initial_debit_auth_id,
		AUTHORIZATION_REVISION_NUMBER = p_auth_rev_number,
		AMENDMENT_REASON_CODE = p_amend_readon_code,
		AUTH_SIGN_DATE = p_auth_sign_date,
		AUTH_CANCEL_DATE = p_auth_cancel_date,
		DEBIT_AUTH_METHOD = p_debit_auth_method,
		PRE_NOTIFICATION_REQUIRED_FLAG = p_pre_notif_flag,
		CREDITOR_LEGAL_ENTITY_ID = p_creditor_id,
		CREDITOR_LE_NAME = p_creditor_name,
		DEBIT_AUTH_BEGIN = p_debit_auth_begin,
			   last_updated_by = fnd_global.user_id,
			   last_update_date = SYSDATE ,
			   last_update_login = fnd_global.user_id,
			   object_version_number = object_version_number+1,
			   CUST_ADDR_ID = p_cust_addr_id,
			   DEBIT_AUTH_FLAG = p_debit_auth_flag ,DEBIT_AUTH_REFERENCE = p_debit_auth_ref,
			   CUST_IDENTIFICATION_CODE = p_cust_id_code,
			   CREDITOR_IDENTIFIER = p_creditor_identifer,DEBIT_AUTH_END = p_debit_auth_end,
		PAYMENT_TYPE_CODE = p_payment_code
		WHERE
		DEBIT_AUTHORIZATION_ID = p_debit_auth_id;

Line: 3498

			print_debuginfo('Mandate has been updated:',
					iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);