APPS.CE_BAT_API SQL Statements

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

Line: 102

  SELECT authorization_bat
  FROM ce_cashpools
  WHERE cashpool_id = c_cashpool_id;

Line: 159

   SELECT
	ba.bank_account_id,
	ba.currency_code,
	ba.account_owner_org_id,
	ba.asset_code_combination_id,
	ba.pool_bank_charge_bearer_code,
	ba.pool_payment_method_code,
	ba.pool_payment_reason_code,
	ba.pool_payment_reason_comments,
	ba.pool_remittance_message1,
	ba.pool_remittance_message2,
	ba.pool_remittance_message3,
	xfp.party_id,
	hps.party_site_id,
	decode(nvl(sp.authorization_bat,'NR'),
			'NR','N','Y'),
	sp.legal_entity_id,
	ba.bank_account_name
   FROM
	ce_bank_accounts ba,
	xle_entity_profiles xfp,
	hz_party_sites hps,
	ce_system_parameters sp
   WHERE
	ba.bank_account_id = p_cs_bank_account_id
	AND xfp.legal_entity_id = ba.account_owner_org_id
	AND sp.legal_entity_id(+)= xfp.legal_entity_id
	AND hps.identifying_address_flag(+) = 'Y'
	AND hps.party_id(+) = xfp.party_id;

Line: 293

   SELECT
	cp.cashpool_id,
	cp.currency_code,
	decode(nvl(cp.authorization_bat,'NR'),
			'NR','N','Y'),
	cp.trxn_subtype_code_id
   INTO
	G_cp_cashpool_id,
	G_cp_currency_code,
	G_cp_authorize_flag,
	G_cp_trxn_subtype_code_id
   FROM	ce_cashpools cp
   WHERE cp.cashpool_id = p_cashpool_id;

Line: 309

	   SELECT
		sl.statement_line_id,
		sl.trx_date,
		sl.trx_type,
		sl.amount,
		sl.original_amount,
		sl.effective_date,
		sl.trx_text,
		sl.bank_trx_number,
		sl.currency_code
	   INTO
		G_sl_statement_line_id,
		G_sl_trx_date,
		G_sl_trx_type,
		G_sl_amount,
		G_sl_original_amount,
		G_sl_value_date,
		G_sl_description,
		G_sl_bank_trx_number,
		G_sl_currency_code
	   FROM
		ce_statement_lines sl
	   WHERE
		sl.statement_line_id = p_statement_line_id;

Line: 353

	  SELECT anticipated_float
	  INTO l_anticipated_float
	  FROM iby_payment_methods_vl
	  WHERE payment_method_code = G_ba_payment_method_code;

Line: 409

      SELECT count(*)
      INTO   l_cnt
      FROM  ce_payment_transactions pt
      WHERE pt.create_from_stmtline_id = p_statement_line_id
      AND pt.source_bank_account_id = p_source_ba_id
      AND pt.destination_bank_account_id = p_destination_ba_id
      AND trunc(pt.transaction_date) = trunc(p_transfer_date)
      AND pt.payment_amount = p_transfer_amount
      AND pt.trxn_status_code not in ('FAILED','CANCELLED');

Line: 420

    SELECT count(*)
    INTO l_cnt
    FROM ce_payment_transactions pt
    WHERE
    	pt.source_bank_account_id = p_source_ba_id
    AND	pt.destination_bank_account_id = p_destination_ba_id
    AND	trunc(pt.transaction_date) = trunc(p_transfer_date)
    AND	pt.payment_amount = p_transfer_amount
    AND pt.trxn_status_code not in ('FAILED','CANCELLED');

Line: 613

		CE_PAYMENT_TRXN_PKG.update_transfer_status
			(p_trxn_reference_number,'INVALID');

Line: 617

		CE_PAYMENT_TRXN_PKG.update_transfer_status
				(p_trxn_reference_number,'VALIDATED');

Line: 621

 		        create_update_cashflows(p_trxn_reference_number,l_mode,
 					    l_cashflow_id1,
					    l_cashflow_id2);

Line: 718

		SELECT sba.bank_account_name,
			   d_ba.bank_account_name
		INTO g_source_bank_account_name,
			 g_dest_bank_account_name
		FROM ce_bank_accounts sba,
			 ce_bank_accounts d_ba,
			 ce_payment_transactions pt
		WHERE pt.trxn_reference_number=p_trxn_reference_number
		AND  sba.bank_account_id=pt.source_bank_account_id
		AND  d_ba.bank_account_id=pt.destination_bank_account_id;

Line: 742

   	  UPDATE ce_payment_transactions
   	  SET trxn_status_code = 'INVALID'
	  WHERE trxn_reference_number = p_trxn_reference_number;

Line: 756

	  UPDATE ce_payment_transactions
	  SET trxn_status_code = 'INVALID'
	  WHERE trxn_reference_number = p_trxn_reference_number;

Line: 766

	SELECT currency_code
	INTO l_source_ledger_curr
	FROM gl_ledgers
	WHERE ledger_id=l_source_ledger_id;

Line: 771

	SELECT currency_code
	INTO l_destination_ledger_curr
	FROM gl_ledgers
	WHERE ledger_id=l_destination_ledger_id;

Line: 784

		UPDATE ce_payment_transactions
		SET trxn_status_code = 'INVALID'
		WHERE trxn_reference_number = p_trxn_reference_number;

Line: 793

		UPDATE ce_payment_transactions
		SET trxn_status_code = 'INVALID'
		WHERE trxn_reference_number = p_trxn_reference_number;

Line: 917

            UPDATE ce_payment_transactions
            SET trxn_status_code = 'INVALID'
            WHERE trxn_reference_number = p_trxn_reference_number;

Line: 943

    UPDATE ce_payment_transactions
    SET payment_offset_ccid = G_bat_payment_offset_ccid,
       receipt_offset_ccid = G_bat_receipt_offset_ccid
    WHERE trxn_reference_number = p_trxn_reference_number;

Line: 961

                create_update_cashflows(p_trxn_reference_number,l_mode,
                            l_cashflow_id1,
                            l_cashflow_id2);

Line: 969

                    UPDATE ce_payment_transactions
                    SET trxn_status_code = 'VALIDATED'
                    WHERE trxn_reference_number = p_trxn_reference_number;

Line: 980

                UPDATE ce_payment_transactions
                SET trxn_Status_code = 'INVALID'
                WHERE trxn_reference_number = p_trxn_reference_number;

Line: 988

                UPDATE ce_payment_transactions
                SET trxn_status_code = 'VALIDATED'
                WHERE trxn_reference_number = p_trxn_reference_number;

Line: 992

                create_update_cashflows(p_trxn_reference_number,
                    l_mode,
                    l_cashflow_id1,
                    l_cashflow_id2);

Line: 997

                create_update_cashflows(p_trxn_reference_number,
                    l_mode,
                    l_cashflow_id1,
                    l_cashflow_id2);

Line: 1051

	 SELECT count(*)
	 INTO l_cnt
	 FROM ce_payment_transactions
	 WHERE payment_request_number = p_pay_proc_req_code;

Line: 1065

		UPDATE ce_payment_transactions
	  	SET trxn_status_code = 'IN_PROCESS'
		WHERE payment_request_number = p_pay_proc_req_code;

Line: 1076

	 SELECT ce_payment_transactions_s.nextval
	 INTO l_pay_proc_req_code
	 FROM dual;

Line: 1081

	 UPDATE ce_payment_transactions
	 SET payment_request_number = l_pay_proc_req_code
	 WHERE trxn_reference_number = p_trxn_reference_number;

Line: 1096

	 	CE_PAYMENT_TRXN_PKG.update_transfer_status(
				p_trxn_reference_number,'IN_PROCESS');

Line: 1126

	SELECT cashflow_id
	FROM ce_cashflows
	WHERE trxn_reference_number=p_pay_trxn_number;

Line: 1134

	CE_PAYMENT_TRXN_PKG.update_transfer_status(
			p_pay_trxn_number, 'REJECTED');

Line: 1138

	UPDATE ce_cashflows
	SET cashflow_status_code='CANCELED'
	WHERE trxn_reference_number=p_pay_trxn_number;

Line: 1173

  SELECT cf.cashflow_id
  FROM	 ce_cashflows cf,
	 ce_payment_transactions pt
  WHERE  cf.trxn_reference_number = pt.trxn_reference_number
  AND	 pt.trxn_status_code = 'SETTLED'
  AND 	 cf.trxn_reference_number = p_trxn_ref_number;

Line: 1195

	-- Update the cashflows status to CANCELED
	UPDATE ce_cashflows
	SET	cashflow_status_code = 'CANCELED'
	WHERE	trxn_reference_number = p_pay_trxn_number;

Line: 1200

	-- Update the payment transaction status to CANCELED
	CE_PAYMENT_TRXN_PKG.update_transfer_status
			(p_pay_trxn_number,'CANCELED');

Line: 1233

 CE_PAYMENT_TRXN_PKG.Insert_Row(
	 X_ROWID => l_row_id,
	 X_TRXN_REFERENCE_NUMBER => p_pay_trxn_number,
	 X_SETTLE_BY_SYSTEM_FLAG => G_bat_settle_flag,
	 X_TRANSACTION_TYPE    => 'BAT',
	 X_TRXN_SUBTYPE_CODE_ID  => G_cp_trxn_subtype_code_id,
	 X_TRANSACTION_DATE    => G_bat_date,
	 X_ANTICIPATED_VALUE_DATE => G_bat_anticipated_date,
	 X_TRANSACTION_DESCRIPTION  => G_sl_description,
	 X_PAYMENT_CURRENCY_CODE   => G_bat_currency_code,
	 X_PAYMENT_AMOUNT       => G_bat_amount,
	 X_SOURCE_PARTY_ID       => G_source_le_party_id,
	 X_SOURCE_LEGAL_ENTITY_ID  => G_source_le_id,
	 X_SOURCE_BANK_ACCOUNT_ID  => G_source_bank_account_id,
	 X_DEST_PARTY_ID           => G_destination_le_party_id,
	 X_DEST_LEGAL_ENTITY_ID    => G_destination_le_id,
	 X_DEST_BANK_ACCOUNT_ID    => G_destination_bank_account_id,
	 X_DEST_PARTY_SITE_ID  => G_destination_party_site_id,
	 X_REPETITIVE_PAYMENT_CODE => NULL,
	 X_TRXN_STATUS_CODE        => 'NEW',
	 X_PAYMENT_METHOD_CODE     => G_ba_payment_method_code,
	 X_AUTHORIZE_FLAG          => NVL(G_cp_authorize_flag,G_sp_authorize_flag),
	 X_BANK_CHARGE_BEARER      => G_ba_bank_charge_bearer,
	 X_PAYMENT_REASON_CODE     => G_ba_payment_reason_code,
	 X_PAYMENT_REASON_COMMENTS => G_ba_payment_reason_comments,
	 X_REMITTANCE_MESSAGE1  => G_ba_remittance_message1,
	 X_REMITTANCE_MESSAGE2  => G_ba_remittance_message2,
	 X_REMITTANCE_MESSAGE3  => G_ba_remittance_message3,
	 X_CREATED_FROM_DIR        => G_bat_created_from_dir,
	 X_CREATE_FROM_STMTLINE_ID => G_bat_statement_line_id,
	 X_BANK_TRXN_NUMBER        => G_sl_bank_trx_number,
	 X_PAYMENT_REQUEST_NUMBER => NULL,
	 X_PAPER_DOCUMENT_NUMBER  => NULL,
	 X_DOC_SEQUENCE_ID   => NULL,
	 X_DOC_SEQUENCE_VALUE => NULL,
	 X_DOC_CATEGORY_CODE => NULL,
	 X_PAYMENT_OFFSET_CCID     => G_bat_payment_offset_ccid,
	 X_RECEIPT_OFFSET_CCID     => G_bat_receipt_offset_ccid,
	 X_CASHPOOL_ID             => G_cp_cashpool_id,
	 X_CREATED_BY              => FND_GLOBAL.user_id,
	 X_CREATION_DATE           => sysdate,
	 X_LAST_UPDATED_BY         => FND_GLOBAL.user_id,
	 X_LAST_UPDATE_DATE        => sysdate,
	 X_LAST_UPDATE_LOGIN       => FND_GLOBAL.user_id,
	 X_EXT_BANK_ACCOUNT_ID 	=> NULL,
	 X_ATTRIBUTE_CATEGORY => NULL,
	 X_ATTRIBUTE1 => NULL,
	 X_ATTRIBUTE2 => NULL,
	 X_ATTRIBUTE3 => NULL,
	 X_ATTRIBUTE4 => NULL,
	 X_ATTRIBUTE5 => NULL,
	 X_ATTRIBUTE6 => NULL,
	 X_ATTRIBUTE7 => NULL,
	 X_ATTRIBUTE8 => NULL,
	 X_ATTRIBUTE9 => NULL,
	 X_ATTRIBUTE10 => NULL,
	 X_ATTRIBUTE11 => NULL,
	 X_ATTRIBUTE12 => NULL,
	 X_ATTRIBUTE13 => NULL,
	 X_ATTRIBUTE14 => NULL,
	 X_ATTRIBUTE15 => NULL);

Line: 1327

	SELECT cashflow_id
	FROM ce_cashflows
	WHERE trxn_Reference_number=p_trxn_ref_number;

Line: 1345

  		  UPDATE ce_payment_transactions
		  SET bank_trxn_number = p_payment_reference_number
		  WHERE trxn_reference_number = p_pay_trxn_number;

Line: 1349

		  UPDATE ce_cashflows
		  SET bank_trxn_number = p_payment_reference_number
		  WHERE trxn_reference_number = p_pay_trxn_number;

Line: 1357

	--update transfer status to SETTLED
	CE_PAYMENT_TRXN_PKG.update_transfer_status(
			 p_pay_trxn_number,
			 'SETTLED');

Line: 1420

	SELECT
		ANTICIPATED_VALUE_DATE,
		'Y',
		BANK_CHARGE_BEARER,
		TRXN_REFERENCE_NUMBER,
		TRXN_REFERENCE_NUMBER,
		260,
		PAYMENT_AMOUNT,
		PAYMENT_CURRENCY_CODE,
		TRANSACTION_DATE,
		TRANSACTION_DESCRIPTION,
		'BAT',
		'Y',
		EXT_BANK_ACCOUNT_ID,
		DESTINATION_BANK_ACCOUNT_ID,
		DESTINATION_LEGAL_ENTITY_ID,
		DESTINATION_LEGAL_ENTITY_ID,
		'LEGAL_ENTITY',
		'BAT',
		DESTINATION_PARTY_ID,
		DESTINATION_PARTY_SITE_ID,
		PAYMENT_AMOUNT,
		PAYMENT_CURRENCY_CODE,
		TRANSACTION_DATE,
		'CASH_PAYMENT',
		PAYMENT_METHOD_CODE,
		PAYMENT_REASON_CODE,
		PAYMENT_REASON_COMMENTS,
		REMITTANCE_MESSAGE1,
		REMITTANCE_MESSAGE2,
		REMITTANCE_MESSAGE3,
		IBY_DOCS_PAYABLE_GT_S.nextval
	INTO
		l_docs_payable_rec.anticipated_value_date,
		l_docs_payable_rec.allow_removing_document_flag,
		l_docs_payable_rec.bank_charge_bearer,
		l_docs_payable_rec.calling_app_doc_unique_ref1,
		l_docs_payable_rec.calling_app_doc_ref_number,
		l_docs_payable_rec.calling_app_id,
		l_docs_payable_rec.document_amount,
		l_docs_payable_rec.document_currency_code,
		l_docs_payable_rec.document_date,
		l_docs_payable_rec.document_description,
		l_docs_payable_rec.document_type,
		l_docs_payable_rec.exclusive_payment_flag,
		l_docs_payable_rec.external_bank_account_id,
		l_docs_payable_rec.internal_bank_account_id,
		l_docs_payable_rec.legal_entity_id,
		l_docs_payable_rec.org_id,
		l_docs_payable_rec.org_type,
		l_docs_payable_rec.pay_proc_trxn_type_code,
		l_docs_payable_rec.payee_party_id,
		l_docs_payable_rec.payee_party_site_id,
		l_docs_payable_rec.payment_amount,
		l_docs_payable_rec.payment_currency_code,
		l_docs_payable_rec.payment_date,
		l_docs_payable_rec.payment_function,
		l_docs_payable_rec.payment_method_code,
		l_docs_payable_rec.payment_reason_code,
		l_docs_payable_rec.payment_reason_comments,
		l_docs_payable_rec.remittance_message1,
		l_docs_payable_rec.remittance_message2,
		l_docs_payable_rec.remittance_message3,
		l_docs_payable_rec.document_payable_id
	FROM
		ce_payment_transactions
	WHERE
		trxn_reference_number = p_trxn_reference_number;

Line: 1489

	log('inserting data into iby_docs_payable_gt');

Line: 1491

	INSERT INTO IBY_DOCS_PAYABLE_GT(
		ANTICIPATED_VALUE_DATE,
		ALLOW_REMOVING_DOCUMENT_FLAG,
		BANK_CHARGE_BEARER,
		CALLING_APP_DOC_UNIQUE_REF1,
		CALLING_APP_DOC_REF_NUMBER,
		CALLING_APP_ID,
		DOCUMENT_AMOUNT,
		DOCUMENT_CURRENCY_CODE,
		DOCUMENT_DATE,
		DOCUMENT_DESCRIPTION,
		DOCUMENT_PAYABLE_ID,
		DOCUMENT_TYPE,
		EXCLUSIVE_PAYMENT_FLAG,
		EXTERNAL_BANK_ACCOUNT_ID,
		INTERNAL_BANK_ACCOUNT_ID,
		LEGAL_ENTITY_ID,
		ORG_ID,
		ORG_TYPE,
		PAY_PROC_TRXN_TYPE_CODE,
		PAYEE_PARTY_ID,
		PAYEE_PARTY_SITE_ID,
		PAYMENT_AMOUNT,
		PAYMENT_CURRENCY_CODE,
		PAYMENT_DATE,
		PAYMENT_FUNCTION,
		PAYMENT_METHOD_CODE,
		PAYMENT_REASON_CODE,
		PAYMENT_REASON_COMMENTS,
		REMITTANCE_MESSAGE1,
		REMITTANCE_MESSAGE2,
		REMITTANCE_MESSAGE3,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		LAST_UPDATE_LOGIN,
		OBJECT_VERSION_NUMBER)
	VALUES(
		l_docs_payable_rec.anticipated_value_date,
		l_docs_payable_rec.allow_removing_document_flag,
		l_docs_payable_rec.bank_charge_bearer,
		l_docs_payable_rec.calling_app_doc_unique_ref1,
		l_docs_payable_rec.calling_app_doc_ref_number,
		l_docs_payable_rec.calling_app_id,
		l_docs_payable_rec.document_amount,
		l_docs_payable_rec.document_currency_code,
		l_docs_payable_rec.document_date,
		l_docs_payable_rec.document_description,
		l_docs_payable_rec.document_payable_id,
		l_docs_payable_rec.document_type,
		l_docs_payable_rec.exclusive_payment_flag,
		l_docs_payable_rec.external_bank_account_id,
		l_docs_payable_rec.internal_bank_account_id,
		l_docs_payable_rec.legal_entity_id,
		l_docs_payable_rec.org_id,
		l_docs_payable_rec.org_type,
		l_docs_payable_rec.pay_proc_trxn_type_code,
		l_docs_payable_rec.payee_party_id,
		l_docs_payable_rec.payee_party_site_id,
		l_docs_payable_rec.payment_amount,
		l_docs_payable_rec.payment_currency_code,
		l_docs_payable_rec.payment_date,
		l_docs_payable_rec.payment_function,
		l_docs_payable_rec.payment_method_code,
		l_docs_payable_rec.payment_reason_code,
		l_docs_payable_rec.payment_reason_comments,
		l_docs_payable_rec.remittance_message1,
		l_docs_payable_rec.remittance_message2,
		l_docs_payable_rec.remittance_message3,
		NVL(FND_GLOBAL.user_id,-1),
		SYSDATE,
		NVL(FND_GLOBAL.user_id,-1),
		SYSDATE,
		NVL(FND_GLOBAL.user_id,-1),
		1);

Line: 1617

	SELECT
		bb.bank_home_country,
		bb.branch_party_id,
		bb.bank_party_id,
		ba.account_owner_party_id,
		ba.bank_account_name,
		ba.bank_account_num,
		ba.currency_code,
		ba.iban_number,
		ba.check_digits,
		ba.multi_currency_allowed_flag,
		ba.bank_account_name_alt,
		ba.short_account_name,
		ba.bank_account_type,
		ba.account_suffix,
		ba.description,
		ba.agency_location_code,
		'N'
	INTO
		l_ext_bankacct_rec.COUNTRY_CODE,
		l_ext_bankacct_rec.BRANCH_ID,
		l_ext_bankacct_rec.BANK_ID,
		l_ext_bankacct_rec.ACCT_OWNER_PARTY_ID,
		l_ext_bankacct_rec.BANK_ACCOUNT_NAME,
		l_ext_bankacct_rec.BANK_ACCOUNT_NUM,
		l_ext_bankacct_rec.CURRENCY,
		l_ext_bankacct_rec.IBAN,
		l_ext_bankacct_rec.CHECK_DIGITS,
		l_ext_bankacct_rec.MULTI_CURRENCY_ALLOWED_FLAG,
		l_ext_bankacct_rec.ALTERNATE_ACCT_NAME,
		l_ext_bankacct_rec.SHORT_ACCT_NAME,
		l_ext_bankacct_rec.ACCT_TYPE,
		l_ext_bankacct_rec.ACCT_SUFFIX,
		l_ext_bankacct_rec.DESCRIPTION,
		l_ext_bankacct_rec.AGENCY_LOCATION_CODE,
		l_ext_bankacct_rec.PAYMENT_FACTOR_FLAG
	FROM
		ce_bank_accounts ba,
		ce_bank_branches_v bb
	WHERE
		ba.bank_branch_id = bb.branch_party_id
	AND	ba.bank_account_id = p_bank_account_id;

Line: 1740

	SELECT
		destination_party_id,
		destination_party_site_id,
		source_legal_entity_id,
		'LEGAL_ENTITY',
		'CASH_PAYMENT',
		'Y'
	INTO
		l_ext_payee_rec.payee_party_id,
		l_ext_payee_rec.payee_party_site_id,
		l_ext_payee_rec.payer_org_id,
		l_ext_payee_rec.payer_org_type,
		l_ext_payee_rec.payment_function,
		l_ext_payee_rec.exclusive_pay_flag
	FROM
		ce_payment_transactions
	WHERE   trxn_reference_number = p_trxn_reference_number;

Line: 1813

		UPDATE ce_payment_transactions
		SET ext_bank_account_id = l_ext_bank_account_id
		WHERE trxn_reference_number = p_trxn_reference_number;

Line: 1873

	-- Update the cashflows status to CANCELED
	UPDATE ce_cashflows
	SET	cashflow_status_code = 'CANCELED'
	WHERE	cashflow_id = l_cashflow_id;

Line: 1879

	-- Update statement line to nullify the cashflow id
	UPDATE ce_statement_lines
	SET cashflow_id = null,
	    je_status_flag = null
	WHERE cashflow_id = l_cashflow_id;

Line: 1894

PROCEDURE create_update_cashflows(p_trxn_reference_number NUMBER,
				  p_mode OUT NOCOPY VARCHAR2,
				  p_cashflow_id1 OUT NOCOPY NUMBER,
				  p_cashflow_id2 OUT NOCOPY NUMBER)
IS
BEGIN
	log('>>create_update_cashflows..');

Line: 1910

	UPDATE ce_statement_lines
	set cashflow_id=p_cashflow_id1
	WHERE statement_line_id=G_sl_statement_line_id;

Line: 1914

	log('<<..create_update_cashflows');

Line: 1917

	log('Exception in create_update_cashflows..');

Line: 1919

END create_update_cashflows;

Line: 1953

    SELECT currency_code
    INTO l_source_ba_currency_code
    FROM ce_bank_accounts
    WHERE bank_account_id = p_source_ba_id;

Line: 1958

    SELECT currency_code
    INTO l_destination_ba_currency_code
    FROM ce_bank_accounts
    WHERE bank_account_id = p_destination_ba_id;

Line: 1974

    SELECT currency_code
    INTO l_source_ledger_curr
    FROM gl_ledgers
    WHERE ledger_id=l_source_ledger_id;

Line: 1979

    SELECT currency_code
    INTO l_destination_ledger_curr
    FROM gl_ledgers
    WHERE ledger_id=l_destination_ledger_id;