APPS.IBY_FNDCPT_EXTRACT_GEN_PVT SQL Statements

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

Line: 154

      'select XMLElement("FundsCapturePayerNotification", ' ||
      '         XMLElement("FormatProgramRequestID", fnd_global.CONC_REQUEST_ID), ' ||
      '         XMLAgg(xml_order.FNDCPT_ORDER)) ' ||
      '  from iby_trxn_summaries_all txn, IBY_XML_FNDCPT_ORDER_PN_1_0_V xml_order ' ||
      ' where txn.trxnmid = xml_order.trxnmid ' ||
      '   and nvl(txn.payer_notification_required, ''N'') = ''Y'' ';

Line: 335

      SELECT XMLType.getClobVal(instruction)
      INTO x_extract_doc
      FROM iby_xml_batch_fci_1_0_v
      WHERE mbatchid=p_txn_id
      AND rownum=1;

Line: 341

      SELECT XMLType.getClobVal(instruction)
      INTO x_extract_doc
      FROM iby_xml_online_fci_1_0_v
      WHERE trxnmid=p_txn_id
      AND rownum=1;

Line: 403

    SELECT
      XMLElement("PayeeAccount",
        XMLElement("PaymentSystemAccount",
          XMLElement("AccountName",txn.bepkey),
          Extract(XMLAgg(XMLElement("OptionSet",opts.account_options)),
                  'OptionSet[1]/*')
        ),
        CASE WHEN (NOT xml_bank.instrid IS NULL) THEN
         Extract(XMLAgg(XMLElement("BankAccount",xml_bank.bank_account_content)),
                 '/BankAccount[1]')
        END,
        XMLElement("Payee",
          XMLElement("Name",payee.name),
          XMLElement("Address",
            XMLElement("AddressLine1",null),
            XMLForest(null AS "AddressLine2",null AS "AddressLine3"),
            XMLElement("City",null),
            XMLElement("State",null),
            XMLElement("Country",null),
            XMLElement("PostalCode",null)
          ),
          XMLForest(DECODE(payee.mcc_code, -1,null, payee.mcc_code) AS "MCC")
        ),
        XMLElement("OrderCount",count(txn.trxnmid)),
        XMLElement("AccountTotals",
          XMLElement("AuthorizationsTotal",
            XMLElement("Value",
             DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
                                   'BANKACCOUNT', 0,
                                    SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)) )),
            XMLElement("Currency",
              XMLElement("Code",MAX(txn.currencynamecode))
            )
          ),
          XMLElement("CapturesTotal",
            XMLElement("Value",
              DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)),
                                    'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTREQ',txn.amount, 0)),
              SUM(DECODE(txn.trxntypeid, 3,txn.amount, 8,txn.amount, 0)) )),
            XMLElement("Currency",
              XMLElement("Code",MAX(txn.currencynamecode))
            )
          ),
          XMLElement("CreditsTotal",
            XMLElement("Value",
            DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
    	                                'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTCREDIT',txn.amount, 0)),
              SUM(DECODE(txn.trxntypeid, 5,txn.amount, 11,txn.amount, 0)) )),
            XMLElement("Currency",
              XMLElement("Code",MAX(txn.currencynamecode))
            )
          )
        ),
        XMLAgg(xml_order.fndcpt_order)
      )--,
      --txn.mbatchid,
      --txn.payeeinstrid
    FROM
      iby_trxn_summaries_all txn,
      iby_payee payee,
      iby_bepkeys keys,
      iby_xml_fndcpt_bankaccount_v xml_bank,
      iby_xml_bep_acct_options_v opts,
      iby_xml_fndcpt_order_1_0_v xml_order
     WHERE   (txn.payeeid = payee.payeeid)
      AND (txn.payeeinstrid = xml_bank.instrid(+))
      AND (txn.payeeid = keys.ownerid)
      AND (txn.bepkey = keys.key)
      AND (keys.ownertype = 'PAYEE')
      AND (keys.bep_account_id = opts.bep_account_id(+))
      AND (txn.trxnmid = xml_order.trxnmid)
      AND txn.trxnmid = G_Extract_Run_Payment_id
      AND txn.mbatchid = p_mbatch_id
    GROUP BY
      txn.mbatchid, txn.payeeinstrid, txn.instrtype, txn.bepkey,
      payee.name, payee.mcc_code, opts.bep_account_id,
      xml_bank.instrid;

Line: 482

    SELECT
      XMLElement("PayeeAccount",
        XMLElement("PaymentSystemAccount",
          XMLElement("AccountName",txn.bepkey),
          Extract(XMLAgg(XMLElement("OptionSet",opts.account_options)),
                  'OptionSet[1]/*')
        ),
        CASE WHEN (NOT xml_bank.instrid IS NULL) THEN
         Extract(XMLAgg(XMLElement("BankAccount",xml_bank.bank_account_content)),
                 '/BankAccount[1]')
        END,
        XMLElement("Payee",
          XMLElement("Name",payee.name),
          XMLElement("Address",
            XMLElement("AddressLine1",null),
            XMLForest(null AS "AddressLine2",null AS "AddressLine3"),
            XMLElement("City",null),
            XMLElement("State",null),
            XMLElement("Country",null),
            XMLElement("PostalCode",null)
          ),
          XMLForest(DECODE(payee.mcc_code, -1,null, payee.mcc_code) AS "MCC")
        ),
        XMLElement("OrderCount",count(txn.trxnmid)),
        XMLElement("AccountTotals",
          XMLElement("AuthorizationsTotal",
            XMLElement("Value",
             DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
                                   'BANKACCOUNT', 0,
                                    SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)) )),
            XMLElement("Currency",
              XMLElement("Code",MAX(txn.currencynamecode))
            )
          ),
          XMLElement("CapturesTotal",
            XMLElement("Value",
              DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', SUM(DECODE(txn.trxntypeid, 2,txn.amount, 0)),
                                    'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTREQ',txn.amount, 0)),
              SUM(DECODE(txn.trxntypeid, 3,txn.amount, 8,txn.amount, 0)) )),
            XMLElement("Currency",
              XMLElement("Code",MAX(txn.currencynamecode))
            )
          ),
          XMLElement("CreditsTotal",
            XMLElement("Value",
            DECODE(txn.INSTRTYPE, 'PINLESSDEBITCARD', 0,
    	                                'BANKACCOUNT', SUM(DECODE(txn.REQTYPE, 'ORAPMTCREDIT',txn.amount, 0)),
              SUM(DECODE(txn.trxntypeid, 5,txn.amount, 11,txn.amount, 0)) )),
            XMLElement("Currency",
              XMLElement("Code",MAX(txn.currencynamecode))
            )
          )
        ),
        XMLAgg(xml_order.fndcpt_order)
      )--,
      --txn.mbatchid,
      --txn.payeeinstrid
    FROM
      iby_trxn_summaries_all txn,
      iby_payee payee,
      iby_bepkeys keys,
      iby_xml_fndcpt_bankaccount_v xml_bank,
      iby_xml_bep_acct_options_v opts,
      iby_xml_fndcpt_order_1_0_v xml_order
     WHERE   (txn.payeeid = payee.payeeid)
      AND (txn.payeeinstrid = xml_bank.instrid(+))
      AND (txn.payeeid = keys.ownerid)
      AND (txn.bepkey = keys.key)
      AND (keys.ownertype = 'PAYEE')
      AND (keys.bep_account_id = opts.bep_account_id(+))
      AND (txn.trxnmid = xml_order.trxnmid)
      AND Get_SRA_Attribute(txn.trxnmid, G_SRA_DELIVERY_METHOD_ATTR) = G_SRA_DELIVERY_METHOD_PRINTED
      AND txn.mbatchid = p_mbatch_id
    GROUP BY
      txn.mbatchid, txn.payeeinstrid, txn.instrtype, txn.bepkey,
      payee.name, payee.mcc_code, opts.bep_account_id,
      xml_bank.instrid;

Line: 621

    SELECT bat.instrument_type
      FROM iby_trxn_summaries_all txn, iby_batches_all bat
     WHERE txn.trxnmid = p_trxnmid
       AND txn.mbatchid = bat.mbatchid;

Line: 627

    SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
           sys_pf.payer_notification_format
      FROM iby_trxn_summaries_all txn, iby_batches_all bat,
           iby_fndcpt_sys_eft_pf_b sys_pf, iby_fndcpt_user_eft_pf_b user_pf
     WHERE txn.trxnmid = p_trxnmid
       AND txn.mbatchid = bat.mbatchid
       AND bat.process_profile_code = user_pf.user_eft_profile_code
       AND user_pf.sys_eft_profile_code = sys_pf.sys_eft_profile_code;

Line: 637

    SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
           sys_pf.payer_notification_format
      FROM iby_trxn_summaries_all txn, iby_batches_all bat,
           iby_fndcpt_sys_cc_pf_b sys_pf, iby_fndcpt_user_cc_pf_b user_pf
     WHERE txn.trxnmid = p_trxnmid
       AND txn.mbatchid = bat.mbatchid
       AND bat.process_profile_code = user_pf.user_cc_profile_code
       AND user_pf.sys_cc_profile_code = sys_pf.sys_cc_profile_code;

Line: 647

    SELECT sys_pf.OVERRIDE_PAYER_DELIVERY_FLAG, sys_pf.PAYER_NOTIFICATION_DEL_METHOD,
           sys_pf.payer_notification_format
      FROM iby_trxn_summaries_all txn, iby_batches_all bat,
           iby_fndcpt_sys_dc_pf_b sys_pf, iby_fndcpt_user_dc_pf_b user_pf
     WHERE txn.trxnmid = p_trxnmid
       AND txn.mbatchid = bat.mbatchid
       AND bat.process_profile_code = user_pf.user_dc_profile_code
       AND user_pf.sys_dc_profile_code = sys_pf.sys_dc_profile_code;

Line: 657

    SELECT loc.language, loc.country
      FROM hz_party_sites ps, hz_locations loc,
           iby_trxn_summaries_all txn, hz_cust_site_uses_all hz_csu,
           hz_cust_acct_sites_all hz_cs
     where txn.trxnmid = p_trxnmid
       and hz_csu.cust_acct_site_id = hz_cs.cust_acct_site_id
       and hz_cs.party_site_id = ps.party_site_id
       AND txn.acct_site_use_id = hz_csu.site_use_id(+)
       AND loc.location_id = ps.location_id;

Line: 744

      SELECT payer.debit_advice_delivery_method,
             payer.debit_advice_email,
             payer.debit_advice_fax
        FROM iby_external_payers_all payer,
       	     iby_trxn_summaries_all txn
       WHERE payer.party_id = txn.payer_party_id
         AND (payer.org_id is NULL OR (payer.org_id = txn.org_id AND payer.org_type = txn.org_type))
         AND (payer.cust_account_id is NULL OR payer.cust_account_id = txn.cust_account_id)
         AND (payer.acct_site_use_id is NULL OR payer.acct_site_use_id = txn.acct_site_use_id)
         AND txn.trxnmid = p_trxnmid
    ORDER BY payer.acct_site_use_id, payer.cust_account_id, payer.org_id;

Line: 779

    SELECT bat.instrument_type
      FROM iby_batches_all bat
     WHERE bat.batchid = p_batchid;

Line: 784

    SELECT sys_pf.PAYER_NOTIFICATION_FORMAT, sys_pf.ACCOMPANY_LETTER_FORMAT
      FROM iby_batches_all bat,
           iby_fndcpt_sys_eft_pf_b sys_pf, iby_fndcpt_user_eft_pf_b user_pf
     WHERE bat.batchid = p_batchid
       AND bat.process_profile_code = user_pf.user_eft_profile_code
       AND user_pf.sys_eft_profile_code = sys_pf.sys_eft_profile_code;

Line: 792

    SELECT sys_pf.PAYER_NOTIFICATION_FORMAT
      FROM iby_batches_all bat,
           iby_fndcpt_sys_cc_pf_b sys_pf, iby_fndcpt_user_cc_pf_b user_pf
     WHERE bat.batchid = p_batchid
       AND bat.process_profile_code = user_pf.user_cc_profile_code
       AND user_pf.sys_cc_profile_code = sys_pf.sys_cc_profile_code;

Line: 800

    SELECT sys_pf.PAYER_NOTIFICATION_FORMAT
      FROM iby_batches_all bat,
           iby_fndcpt_sys_dc_pf_b sys_pf, iby_fndcpt_user_dc_pf_b user_pf
     WHERE bat.batchid = p_batchid
       AND bat.process_profile_code = user_pf.user_dc_profile_code
       AND user_pf.sys_dc_profile_code = sys_pf.sys_dc_profile_code;

Line: 847

  PROCEDURE Update_Pmt_SRA_Attr_Prt
  (
  p_mbatchid         IN     VARCHAR2,
  p_fromDate         IN     VARCHAR2,
  p_toDate           IN     VARCHAR2,
  p_fromPSON         IN     VARCHAR2,
  p_toPSON           IN     VARCHAR2,
  p_delivery_method  IN     VARCHAR2,
  p_format_code      IN     VARCHAR2
  )
  IS
    l_Debug_Module   VARCHAR2(255) := G_DEBUG_MODULE || '.Update_Pmt_SRA_Attr_Prt';

Line: 861

    l_update_stmt     VARCHAR2(4000) :=
      'UPDATE iby_trxn_summaries_all txn SET ' ||
      '  debit_advice_delivery_method = ''PRINTED'', ' ||
      '  debit_advice_email = null, ' ||
      '  debit_advice_fax = null, ' ||
      '  payer_notification_created = ''Y'', ' ||
      '  object_version_number    = object_version_number + 1, ' ||
      '  last_updated_by          = fnd_global.user_id, ' ||
      '  last_update_date         = SYSDATE, ' ||
      '  last_update_login        = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id) ' ||
      'WHERE nvl(txn.payer_notification_required, ''N'') = ''Y'' ';

Line: 891

    l_update_stmt := l_update_stmt || l_where_clause;

Line: 893

    iby_debug_pub.add(debug_msg => 'Before executing dynamic update statement.',
                      debug_level => FND_LOG.LEVEL_STATEMENT,
                      module => l_Debug_Module);

Line: 897

    iby_debug_pub.add(debug_msg => 'l_update_stmt: ' || l_update_stmt,
                      debug_level => FND_LOG.LEVEL_STATEMENT,
                      module => l_Debug_Module);

Line: 901

    EXECUTE IMMEDIATE l_update_stmt;

Line: 907

  END Update_Pmt_SRA_Attr_Prt;

Line: 910

  PROCEDURE Update_Pmt_SRA_Attr_Ele
  (
  p_trxnmid                      IN     NUMBER,
  p_delivery_method              IN     VARCHAR2,
  p_recipient_email              IN     VARCHAR2,
  p_recipient_fax                IN     VARCHAR2
  )
  IS
  BEGIN

    IF p_delivery_method = 'EMAIL' THEN
      UPDATE
        iby_trxn_summaries_all
      SET
        debit_advice_delivery_method = p_delivery_method,
        debit_advice_email = p_recipient_email,
        debit_advice_fax = null,
        payer_notification_created = 'Y',
        object_version_number    = object_version_number + 1,
        last_updated_by          = fnd_global.user_id,
        last_update_date         = SYSDATE,
        last_update_login        = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
      WHERE trxnmid = p_trxnmid;

Line: 934

      UPDATE
        iby_trxn_summaries_all
      SET
        debit_advice_delivery_method = p_delivery_method,
        debit_advice_email = null,
        debit_advice_fax = p_recipient_fax,
        payer_notification_created = 'Y',
        object_version_number    = object_version_number + 1,
        last_updated_by          = fnd_global.user_id,
        last_update_date         = SYSDATE,
        last_update_login        = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
      WHERE trxnmid = p_trxnmid;

Line: 947

      UPDATE
        iby_trxn_summaries_all
      SET
        debit_advice_delivery_method = p_delivery_method,
        debit_advice_email = NULL,
        debit_advice_fax = NULL,
        payer_notification_created = 'Y',
        object_version_number    = object_version_number + 1,
        last_updated_by          = fnd_global.user_id,
        last_update_date         = SYSDATE,
        last_update_login        = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
      WHERE trxnmid = p_trxnmid;

Line: 963

  END Update_Pmt_SRA_Attr_Ele;

Line: 1009

        SELECT MIN(reqdate)
          INTO l_reqdate
          FROM iby_trxn_summaries_all
         WHERE batchid = p_settlement_batch
           AND NVL(payer_notification_required, 'N') = 'Y';