APPS.IBY_FD_POST_PICP_PROGS_PVT SQL Statements

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

Line: 221

    SELECT ins.PRINTER_NAME, ins.payment_instruction_status,
           ins.TRANSMIT_INSTR_IMMED_FLAG,
           ins.PRINT_INSTRUCTION_IMMED_FLAG, pp.processing_type, mark_complete_event
      FROM iby_pay_instructions_all ins,
           iby_payment_profiles pp
     WHERE ins.payment_instruction_id = p_payment_instruction_id
       AND ins.payment_profile_id = pp.payment_profile_id;

Line: 327

    SELECT nvl(pp.disallow_save_print_flag,'N') into l_save_no_output
     FROM iby_pay_instructions_all ins,
           iby_payment_profiles pp
     WHERE ins.payment_instruction_id = p_payment_instruction_id
       AND ins.payment_profile_id = pp.payment_profile_id;

Line: 374

    SELECT  temp.application_short_name,
	     temp.template_code,
	     temp.default_language,
	     temp.default_territory,
	     decode(template_type_code,
	     'RTF','PDF',
	     'ETEXT','ETEXT',
             'XSL-XML','XML',
	     'XSL-FO','PDF',
	     'PDF','PDF')
                into
	       l_appl_name,
	       l_template_code,
	       l_template_lang,
	       l_template_terr,
	       l_output_format
	   FROM iby_pay_instructions_all ins,
	   iby_payment_profiles pp,
	   iby_formats_b format,
	   XDO_TEMPLATES_B temp
	 WHERE ins.payment_instruction_id  = p_payment_instruction_id
	 AND ins.payment_profile_id        = pp.payment_profile_id
	 AND format.FORMAT_CODE            = pp.PAYMENT_FORMAT_CODE
	 AND format.FORMAT_TEMPLATE_CODE   = temp.template_code
	 AND temp.application_id = 673
	 AND SYSDATE BETWEEN NVL(temp.start_date, SYSDATE) AND NVL(temp.end_date,SYSDATE);

Line: 540

    SELECT ins.object_version_number, ins.payment_instruction_status,
           pp.processing_type, pp.mark_complete_event, pp.payment_format_code,
           pp.print_instruction_immed_flag, ins.process_type
      FROM iby_pay_instructions_all ins, iby_payment_profiles pp
     WHERE ins.payment_profile_id = pp.payment_profile_id
       AND payment_instruction_id = p_payment_instruction_id;

Line: 602

    UPDATE
      iby_pay_instructions_all
    SET
      payment_instruction_status = p_newStatus,
      object_version_number     = l_instruction_ovn + 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
      payment_instruction_id = p_payment_instruction_id;

Line: 638

            UPDATE
              iby_payments_all
            SET
              payment_status           = 'SUBMITTED_FOR_PRINTING',
              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
              payment_instruction_id = p_payment_instruction_id
	      AND payment_status IN ('FORMATTED', 'INSTRUCTION_CREATED');

Line: 680

            UPDATE
              iby_payments_all
            SET
              payment_status           = decode(payment_status,
	                                  'READY_TO_REPRINT','SUBMITTED_FOR_PRINTING',
	                                  'VOID_BY_SETUP_REPRINT','VOID_BY_SETUP',
	                                  'VOID_BY_OVERFLOW_REPRINT','VOID_BY_OVERFLOW'),
              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
              payment_instruction_id = p_payment_instruction_id
	      AND payment_status IN ('READY_TO_REPRINT','VOID_BY_SETUP_REPRINT','VOID_BY_OVERFLOW_REPRINT');

Line: 780

          UPDATE
            iby_payments_all
          SET
            payment_status           = 'FORMATTED',
            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
            payment_instruction_id = p_payment_instruction_id
	    AND payment_status = 'INSTRUCTION_CREATED';

Line: 822

          UPDATE
            iby_payments_all
          SET
            payment_status           = 'FORMATTED',
            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
            payment_instruction_id = p_payment_instruction_id
	    AND payment_status = 'INSTRUCTION_CREATED';

Line: 896

          UPDATE
            iby_payments_all
          SET
            payment_status           = 'FORMATTED',
            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
            payment_instruction_id = p_payment_instruction_id
	    AND payment_status = 'INSTRUCTION_CREATED';

Line: 946

          UPDATE
            iby_payments_all
          SET
            payment_status           = 'FORMATTED',
            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
            payment_instruction_id = p_payment_instruction_id
	    AND payment_status = 'INSTRUCTION_CREATED';

Line: 1021

          UPDATE
            iby_payments_all
          SET
            payment_status           = 'TRANSMITTED',
            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
            payment_instruction_id = p_payment_instruction_id
	    AND payment_status IN ('FORMATTED', 'INSTRUCTION_CREATED');

Line: 1108

          UPDATE
            iby_payments_all
          SET
            payment_status           = 'FORMATTED',
            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
            payment_instruction_id = p_payment_instruction_id
	    AND payment_status IN ('INSTRUCTION_CREATED');

Line: 1259

  PROCEDURE Insert_Transmission_Error
  (
  p_payment_instruction_id   IN     NUMBER,
  p_error_code               IN     VARCHAR2,
  p_error_msg                IN     VARCHAR2
  )
  IS
    l_transaction_error_id  NUMBER;

Line: 1267

    l_Debug_Module          VARCHAR2(255) := G_DEBUG_MODULE || '.Insert_Transmission_Error';

Line: 1287

    select IBY_TRANSACTION_ERRORS_S.nextval into l_transaction_error_id from dual;

Line: 1289

    insert into
      iby_transaction_errors
    (transaction_error_id, transaction_type, transaction_id,
    error_code, error_date, error_status, created_by,
    creation_date, last_updated_by, last_update_date,
    object_version_number, override_allowed_on_error_flag,
    do_not_apply_error_flag, error_type, error_message)
    values
    (l_transaction_error_id, 'PAYMENT_INSTRUCTION', p_payment_instruction_id,
    p_error_code, sysdate, 'ACTIVE', fnd_global.user_id,
    sysdate, fnd_global.user_id, sysdate,
    1, 'Y',
    'N', 'BANK', p_error_msg);

Line: 1307

  END Insert_Transmission_Error;

Line: 1318

    SELECT document_payable_id
      FROM iby_docs_payable_all doc, iby_payments_all pmt,
           iby_pay_instructions_all ins
     WHERE ins.payment_instruction_id = p_payment_instruction_id
       AND ins.payment_instruction_id = pmt.payment_instruction_id
       AND doc.straight_through_flag  = 'Y'
       AND pmt.payment_id = doc.payment_id;

Line: 1339

      UPDATE
        iby_docs_payable_all
      SET
        straight_through_flag    = 'N',
        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
        document_payable_id = l_doc.document_payable_id;

Line: 1377

    l_obj_updated           BOOLEAN := FALSE;

Line: 1381

    SELECT pp.payment_profile_id,
           pp.periodic_sequence_name_1,
           pp.periodic_sequence_name_2,
           pp.periodic_sequence_name_3,
           pp.last_used_number_1,
           pp.last_used_number_2,
           pp.last_used_number_3
      FROM iby_payment_profiles pp, iby_pay_instructions_all ins
     WHERE ins.payment_instruction_id = p_payment_instruction_id
       AND ins.payment_profile_id = pp.payment_profile_id;

Line: 1435

      UPDATE iby_acct_pmt_profiles_b
         SET
             last_used_number_1     = p_last_val1
      WHERE
             payment_profile_id = l_payment_profile_id;

Line: 1441

      l_obj_updated := TRUE;

Line: 1446

      UPDATE iby_acct_pmt_profiles_b
         SET
             last_used_number_2     = p_last_val1
      WHERE
             payment_profile_id = l_payment_profile_id;

Line: 1452

      l_obj_updated := TRUE;

Line: 1457

      UPDATE iby_acct_pmt_profiles_b
         SET
             last_used_number_3     = p_last_val1
      WHERE
             payment_profile_id = l_payment_profile_id;

Line: 1463

      l_obj_updated := TRUE;

Line: 1471

      UPDATE iby_acct_pmt_profiles_b
         SET
             last_used_number_1     = p_last_val2
      WHERE
             payment_profile_id = l_payment_profile_id;

Line: 1477

      l_obj_updated := TRUE;

Line: 1482

      UPDATE iby_acct_pmt_profiles_b
         SET
             last_used_number_2     = p_last_val2
      WHERE
             payment_profile_id = l_payment_profile_id;

Line: 1488

      l_obj_updated := TRUE;

Line: 1493

      UPDATE iby_acct_pmt_profiles_b
         SET
             last_used_number_3     = p_last_val2
      WHERE
             payment_profile_id = l_payment_profile_id;

Line: 1499

      l_obj_updated := TRUE;

Line: 1507

      UPDATE iby_acct_pmt_profiles_b
         SET
             last_used_number_1     = p_last_val3
      WHERE
             payment_profile_id = l_payment_profile_id;

Line: 1513

      l_obj_updated := TRUE;

Line: 1518

      UPDATE iby_acct_pmt_profiles_b
         SET
             last_used_number_2     = p_last_val3
      WHERE
             payment_profile_id = l_payment_profile_id;

Line: 1524

      l_obj_updated := TRUE;

Line: 1529

      UPDATE iby_acct_pmt_profiles_b
         SET
             last_used_number_3     = p_last_val3
      WHERE
             payment_profile_id = l_payment_profile_id;

Line: 1535

      l_obj_updated := TRUE;

Line: 1539

    IF l_obj_updated THEN

      UPDATE iby_acct_pmt_profiles_b
         SET
             object_version_number  = object_version_number + 1,
             last_updated_by        = fnd_global.user_id,
             last_update_date       = SYSDATE,
             last_update_login      = fnd_global.LOGIN_ID
      WHERE
             payment_profile_id = l_payment_profile_id;

Line: 1568

    SELECT object_version_number
      FROM iby_pay_instructions_all
     WHERE payment_instruction_id = p_payment_instruction_id;

Line: 1578

    UPDATE
      iby_pay_instructions_all
    SET
      remittance_advice_created_flag = 'Y',
      object_version_number     = l_instruction_ovn + 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
      payment_instruction_id = p_payment_instruction_id;

Line: 1600

    SELECT object_version_number
      FROM iby_pay_instructions_all
     WHERE payment_instruction_id = p_payment_instruction_id;

Line: 1610

    UPDATE
      iby_pay_instructions_all
    SET
      positive_pay_file_created_flag = 'Y',
      object_version_number     = l_instruction_ovn + 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
      payment_instruction_id = p_payment_instruction_id;

Line: 1632

    SELECT object_version_number
      FROM iby_pay_instructions_all
     WHERE payment_instruction_id = p_payment_instruction_id;

Line: 1642

    UPDATE
      iby_pay_instructions_all
    SET
      regulatory_report_created_flag = 'Y',
      object_version_number     = l_instruction_ovn + 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
      payment_instruction_id = p_payment_instruction_id;

Line: 1694

    SELECT sra.remittance_advice_format_code
      FROM iby_pay_instructions_all ins, iby_payment_profiles pp,
           iby_remit_advice_setup sra
     WHERE payment_instruction_id = p_payment_instruction_id
       AND ins.payment_profile_id = pp.payment_profile_id
       AND pp.system_profile_code = sra.system_profile_code;

Line: 1702

    SELECT pp.pay_file_letter_format_code,
           pp.positive_pay_format_code,
           pp.declaration_report_name
      FROM iby_pay_instructions_all ins, iby_payment_profiles pp
     WHERE payment_instruction_id = p_payment_instruction_id
       AND ins.payment_profile_id = pp.payment_profile_id;

Line: 1747

    SELECT sra.allow_multiple_copy_flag
      FROM iby_pay_instructions_all ins, iby_payment_profiles pp,
           iby_remit_advice_setup sra
     WHERE payment_instruction_id = p_payment_instruction_id
       AND ins.payment_profile_id = pp.payment_profile_id
       AND pp.system_profile_code = sra.system_profile_code;

Line: 1770

    SELECT organization_type, organization_id, name
      FROM ce_security_profiles_v;

Line: 1784

        select  APPLICATION_SHORT_NAME
        into    l_appl_name
        from    FND_APPLICATION
        where   APPLICATION_ID = FND_GLOBAL.resp_appl_id;

Line: 1829

    SELECT po.org_type, po.org_id
      FROM iby_pay_instructions_all ins, iby_process_orgs po
     WHERE ins.payment_instruction_id = p_payment_instruction_id
       AND ins.payment_instruction_id = po.object_id
       AND po.object_type = 'PAYMENT_INSTRUCTION';

Line: 1836

    SELECT 'exist'
      FROM ce_security_profiles_v
     WHERE organization_type = p_org_type
       AND organization_id = p_org_id;

Line: 1887

    SELECT count(po.org_id)
      FROM iby_pay_instructions_all ins, iby_process_orgs po, ce_security_profiles_v ce_sp
     WHERE ins.payment_instruction_id = p_payment_instruction_id
       AND ins.payment_instruction_id = po.object_id
       AND po.object_type = 'PAYMENT_INSTRUCTION'
       AND ce_sp.organization_type = po.org_type
       AND ce_sp.organization_id = po.org_id;

Line: 1961

    SELECT count(po.org_id)
      FROM iby_pay_service_requests ppr, iby_process_orgs po, ce_security_profiles_v ce_sp
     WHERE ppr.payment_service_request_id = p_payment_service_request_id
       AND ppr.payment_service_request_id = po.object_id
       AND po.object_type = 'PAYMENT_REQUEST'
       AND ce_sp.organization_type = po.org_type
       AND ce_sp.organization_id = po.org_id;

Line: 1989

    SELECT count(po.org_id)
      FROM iby_pay_service_requests ppr, iby_process_orgs po
     WHERE ppr.payment_service_request_id = p_payment_service_request_id
       AND ppr.payment_service_request_id = po.object_id
       AND po.object_type = 'PAYMENT_REQUEST';

Line: 2077

    SELECT payment_profile_name
      FROM iby_payment_profiles
     WHERE payment_profile_id = p_payment_profile_id;

Line: 2127

      UPDATE iby_acct_pmt_profiles_b
         SET
             reset_value_1          = nvl(p_reset_value, reset_value_1),
             last_used_number_1     = nvl(p_reset_value, reset_value_1),
             reset_request_1        = fnd_global.CONC_REQUEST_ID,
             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
             payment_profile_id = p_payment_profile_id;

Line: 2141

      UPDATE iby_acct_pmt_profiles_b
         SET
             reset_value_2          = nvl(p_reset_value, reset_value_2),
             last_used_number_2     = nvl(p_reset_value, reset_value_2),
             reset_request_2        = fnd_global.CONC_REQUEST_ID,
             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
             payment_profile_id = p_payment_profile_id;

Line: 2155

      UPDATE iby_acct_pmt_profiles_b
         SET
             reset_value_3          = nvl(p_reset_value, reset_value_3),
             last_used_number_3     = nvl(p_reset_value, reset_value_3),
             reset_request_3        = fnd_global.CONC_REQUEST_ID,
             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
             payment_profile_id = p_payment_profile_id;

Line: 2287

    SELECT pay_file_letter_format_code
      FROM iby_payment_profiles pp, iby_pay_instructions_all ins
     WHERE ins.payment_instruction_id = p_payment_instruction_id
       AND ins.payment_profile_id = pp.payment_profile_id;

Line: 2538

  SELECT pp.payment_format_code
  FROM iby_pay_instructions_all ins, iby_payment_profiles pp
  WHERE ins.payment_profile_id = pp.payment_profile_id
  AND payment_instruction_id = p_payment_instruction_id;