APPS.IBY_SUPP_BANK_MERGE_PUB SQL Statements

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

Line: 26

   SELECT *
   FROM iby_pmt_instr_uses_all
   WHERE instrument_payment_use_id = cp_instrument_payment_use_id;

Line: 31

   SELECT MAX(order_of_preference)
   FROM iby_pmt_instr_uses_all
   WHERE ext_pmt_party_id = cp_ext_payee_id;

Line: 36

   SELECT 'Y'
   FROM iby_account_owners
   WHERE account_owner_party_id = cp_to_party_id
   AND ext_bank_account_id = cp_bank_act_id
   AND NVL(end_date,SYSDATE) > = SYSDATE;

Line: 43

   SELECT Primary_flag
   FROM iby_account_owners
   WHERE ext_bank_account_id = cp_bank_act_id
   AND account_owner_party_id = cp_from_party_id
   AND NVL (end_date, Sysdate)  >= Sysdate;

Line: 113

				g_mesg := '	Inserting record into Instruments l_max_order_of_pref => '|| l_max_order_of_pref ;

Line: 116

			INSERT INTO iby_pmt_instr_uses_all(
				INSTRUMENT_PAYMENT_USE_ID,
				PAYMENT_FLOW             ,
				EXT_PMT_PARTY_ID         ,
				INSTRUMENT_TYPE          ,
				INSTRUMENT_ID            ,
				PAYMENT_FUNCTION         ,
				ORDER_OF_PREFERENCE      ,
				CREATED_BY               ,
				CREATION_DATE            ,
				LAST_UPDATED_BY          ,
				LAST_UPDATE_DATE         ,
				LAST_UPDATE_LOGIN        ,
				OBJECT_VERSION_NUMBER    ,
				START_DATE               ,
				END_DATE                 ,
				DEBIT_AUTH_FLAG          ,
				DEBIT_AUTH_METHOD        ,
				DEBIT_AUTH_REFERENCE     ,
				DEBIT_AUTH_BEGIN         ,
				DEBIT_AUTH_END           ,
				ATTRIBUTE_CATEGORY       ,
				ATTRIBUTE1               ,
				ATTRIBUTE2               ,
				ATTRIBUTE3               ,
				ATTRIBUTE4               ,
				ATTRIBUTE5               ,
				ATTRIBUTE6               ,
				ATTRIBUTE7               ,
				ATTRIBUTE8               ,
				ATTRIBUTE9               ,
				ATTRIBUTE10              ,
				ATTRIBUTE11              ,
				ATTRIBUTE12              ,
				ATTRIBUTE13              ,
				ATTRIBUTE14              ,
				ATTRIBUTE15
				)
			VALUES(
				IBY_PMT_INSTR_USES_ALL_S.NEXTVAL		     ,
				rec_get_from_instr_use_dtls.PAYMENT_FLOW             ,
				p_to_ext_payee_id				     ,
				rec_get_from_instr_use_dtls.INSTRUMENT_TYPE          ,
				rec_get_from_instr_use_dtls.INSTRUMENT_ID            ,
				rec_get_from_instr_use_dtls.PAYMENT_FUNCTION         ,
				DECODE(l_max_order_of_pref, 0, rec_get_from_instr_use_dtls.order_of_preference,l_max_order_of_pref+1),
				hz_utility_pub.created_by			     ,
				hz_utility_pub.CREATION_DATE			     ,
				hz_utility_pub.LAST_UPDATED_BY			     ,
				hz_utility_pub.LAST_UPDATE_DATE			     ,
				hz_utility_pub.LAST_UPDATE_LOGIN		     ,
				1						     ,
				rec_get_from_instr_use_dtls.START_DATE		     ,
				rec_get_from_instr_use_dtls.END_DATE                 ,
				rec_get_from_instr_use_dtls.DEBIT_AUTH_FLAG          ,
				rec_get_from_instr_use_dtls.DEBIT_AUTH_METHOD        ,
				rec_get_from_instr_use_dtls.DEBIT_AUTH_REFERENCE     ,
				rec_get_from_instr_use_dtls.DEBIT_AUTH_BEGIN         ,
				rec_get_from_instr_use_dtls.DEBIT_AUTH_END           ,
				rec_get_from_instr_use_dtls.ATTRIBUTE_CATEGORY       ,
				rec_get_from_instr_use_dtls.ATTRIBUTE1               ,
				rec_get_from_instr_use_dtls.ATTRIBUTE2               ,
				rec_get_from_instr_use_dtls.ATTRIBUTE3               ,
				rec_get_from_instr_use_dtls.ATTRIBUTE4               ,
				rec_get_from_instr_use_dtls.ATTRIBUTE5               ,
				rec_get_from_instr_use_dtls.ATTRIBUTE6               ,
				rec_get_from_instr_use_dtls.ATTRIBUTE7               ,
				rec_get_from_instr_use_dtls.ATTRIBUTE8               ,
				rec_get_from_instr_use_dtls.ATTRIBUTE9               ,
				rec_get_from_instr_use_dtls.ATTRIBUTE10              ,
				rec_get_from_instr_use_dtls.ATTRIBUTE11              ,
				rec_get_from_instr_use_dtls.ATTRIBUTE12              ,
				rec_get_from_instr_use_dtls.ATTRIBUTE13              ,
				rec_get_from_instr_use_dtls.ATTRIBUTE14              ,
				rec_get_from_instr_use_dtls.ATTRIBUTE15
			);

Line: 227

					UPDATE iby_account_owners
					SET primary_flag = 'N'
					WHERE ext_bank_account_id = from_bank_account_id_list(I)
					AND account_owner_party_id = p_from_party_id;

Line: 232

					UPDATE iby_account_owners
					SET primary_flag = 'Y'
					WHERE ext_bank_account_id = from_bank_account_id_list(I)
					AND account_owner_party_id = P_to_party_id;

Line: 243

					UPDATE iby_account_owners
					SET primary_flag = 'N'
					WHERE ext_bank_account_id = from_bank_account_id_list(I)
					AND account_owner_party_id = p_from_party_id;

Line: 248

					INSERT INTO iby_account_owners(
						ACCOUNT_OWNER_ID        ,
						EXT_BANK_ACCOUNT_ID     ,
						ACCOUNT_OWNER_PARTY_ID  ,
						END_DATE                ,
						PRIMARY_FLAG            ,
						CREATED_BY              ,
						CREATION_DATE           ,
						LAST_UPDATED_BY         ,
						LAST_UPDATE_DATE        ,
						LAST_UPDATE_LOGIN       ,
						OBJECT_VERSION_NUMBER
					)VALUES(
						IBY_ACCOUNT_OWNERS_S.NEXTVAL	       ,
						from_bank_account_id_list(I)	       ,
						P_to_party_id			       ,
						NULL				       ,
						'Y'				       ,
						hz_utility_pub.CREATED_BY              ,
						hz_utility_pub.CREATION_DATE           ,
						hz_utility_pub.LAST_UPDATED_BY         ,
						hz_utility_pub.LAST_UPDATE_DATE        ,
						hz_utility_pub.LAST_UPDATE_LOGIN       ,
						1
					);

Line: 279

					INSERT INTO iby_account_owners(
						ACCOUNT_OWNER_ID        ,
						EXT_BANK_ACCOUNT_ID     ,
						ACCOUNT_OWNER_PARTY_ID  ,
						END_DATE                ,
						PRIMARY_FLAG            ,
						CREATED_BY              ,
						CREATION_DATE           ,
						LAST_UPDATED_BY         ,
						LAST_UPDATE_DATE        ,
						LAST_UPDATE_LOGIN       ,
						OBJECT_VERSION_NUMBER
					)VALUES(
						IBY_ACCOUNT_OWNERS_S.NEXTVAL	       ,
						from_bank_account_id_list(I)	       ,
						P_to_party_id			       ,
						NULL				       ,
						'N'				       ,
						hz_utility_pub.CREATED_BY              ,
						hz_utility_pub.CREATION_DATE           ,
						hz_utility_pub.LAST_UPDATED_BY         ,
						hz_utility_pub.LAST_UPDATE_DATE        ,
						hz_utility_pub.LAST_UPDATE_LOGIN       ,
						1
					);

Line: 311

				INSERT INTO iby_account_owners(
					ACCOUNT_OWNER_ID        ,
					EXT_BANK_ACCOUNT_ID     ,
					ACCOUNT_OWNER_PARTY_ID  ,
					END_DATE                ,
					PRIMARY_FLAG            ,
					CREATED_BY              ,
					CREATION_DATE           ,
					LAST_UPDATED_BY         ,
					LAST_UPDATE_DATE        ,
					LAST_UPDATE_LOGIN       ,
					OBJECT_VERSION_NUMBER
				)VALUES(
					IBY_ACCOUNT_OWNERS_S.NEXTVAL	       ,
					from_bank_account_id_list(I)	       ,
					P_to_party_id			       ,
					NULL				       ,
					'N'				       ,
					hz_utility_pub.CREATED_BY              ,
					hz_utility_pub.CREATION_DATE           ,
					hz_utility_pub.LAST_UPDATED_BY         ,
					hz_utility_pub.LAST_UPDATE_DATE        ,
					hz_utility_pub.LAST_UPDATE_LOGIN       ,
					1
				);

Line: 344

		     UPDATE iby_pmt_instr_uses_all
		     SET end_date = SYSDATE
		     WHERE instrument_payment_use_id = from_instr_pmt_use_list(I)
         AND NVL(end_date,SYSDATE) >= SYSDATE/*Bug 14587066 */;

Line: 368

PROCEDURE INSERT_PAYEE_ROW(ext_payee_rec	IN IBY_EXTERNAL_PAYEES_ALL%ROWTYPE,
			   P_EXT_PAYEE_ID	IN OUT NOCOPY NUMBER,
                           x_return_status	OUT NOCOPY VARCHAR2 ,
			   X_MSG_COUNT		IN     OUT NOCOPY NUMBER,
			   X_MSG_DATA		OUT NOCOPY VARCHAR2)
IS

CURSOR get_next_ext_payee_id IS
SELECT IBY_EXTERNAL_PAYEES_ALL_S.NEXTVAL
FROM DUAL;

Line: 386

    INSERT INTO IBY_EXTERNAL_PAYEES_ALL(
    EXT_PAYEE_ID,
    PAYEE_PARTY_ID,
    PAYMENT_FUNCTION,
    EXCLUSIVE_PAYMENT_FLAG,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER,
    PARTY_SITE_ID,
    SUPPLIER_SITE_ID,
    ORG_ID,
    ORG_TYPE,
    DEFAULT_PAYMENT_METHOD_CODE,
    ECE_TP_LOCATION_CODE,
    BANK_CHARGE_BEARER,
    BANK_INSTRUCTION1_CODE,
    BANK_INSTRUCTION2_CODE,
    BANK_INSTRUCTION_DETAILS,
    PAYMENT_REASON_CODE,
    PAYMENT_REASON_COMMENTS,
    INACTIVE_DATE,
    PAYMENT_TEXT_MESSAGE1,
    PAYMENT_TEXT_MESSAGE2,
    PAYMENT_TEXT_MESSAGE3,
    DELIVERY_CHANNEL_CODE,
    PAYMENT_FORMAT_CODE,
    SETTLEMENT_PRIORITY,
    REMIT_ADVICE_DELIVERY_METHOD,
    REMIT_ADVICE_EMAIL,
    REMIT_ADVICE_FAX)
    VALUES (
    p_ext_payee_id,
    ext_payee_rec.Payee_Party_Id,
    ext_payee_rec.Payment_Function,
    ext_payee_rec.EXCLUSIVE_PAYMENT_FLAG,
    hz_utility_pub.CREATED_BY,
    hz_utility_pub.CREATION_DATE,
    hz_utility_pub.LAST_UPDATED_BY,
    hz_utility_pub.LAST_UPDATE_DATE,
    hz_utility_pub.LAST_UPDATE_LOGIN,
    1.0,
    ext_payee_rec.PARTY_SITE_ID,
    ext_payee_rec.Supplier_Site_Id,
    ext_payee_rec.Org_Id,
    ext_payee_rec.ORG_TYPE,
    ext_payee_rec.DEFAULT_PAYMENT_METHOD_CODE,
    ext_payee_rec.ECE_TP_LOCATION_CODE,
    ext_payee_rec.Bank_Charge_Bearer,
    ext_payee_rec.BANK_INSTRUCTION1_CODE,
    ext_payee_rec.BANK_INSTRUCTION2_CODE,
    ext_payee_rec.BANK_INSTRUCTION_DETAILS,
    ext_payee_rec.PAYMENT_REASON_CODE,
    ext_payee_rec.PAYMENT_REASON_COMMENTS,
    ext_payee_rec.Inactive_Date,
    ext_payee_rec.PAYMENT_TEXT_MESSAGE1,
    ext_payee_rec.PAYMENT_TEXT_MESSAGE2,
    ext_payee_rec.PAYMENT_TEXT_MESSAGE3,
    ext_payee_rec.DELIVERY_CHANNEL_CODE,
    ext_payee_rec.PAYMENT_FORMAT_CODE,
    ext_payee_rec.Settlement_Priority,
    ext_payee_rec.REMIT_ADVICE_DELIVERY_METHOD,
    ext_payee_rec.REMIT_ADVICE_EMAIL,
    ext_payee_rec.remit_advice_fax);

Line: 455

     fnd_file.put_line(fnd_file.log, 'Exception: insert_payee_row: '||SQLERRM);

Line: 457

     X_MSG_DATA := 'Exception: insert_payee_row: '||SQLERRM;

Line: 459

END INSERT_PAYEE_ROW;

Line: 471

SELECT *
FROM IBY_EXTERNAL_PAYEES_ALL
WHERE ext_payee_id = cp_ext_payee_id;

Line: 501

	insert_payee_row(l_Ext_payee_rec, P_EXT_PAYEE_ID, x_return_status, X_MSG_COUNT, X_MSG_DATA);

Line: 511

	insert_payee_row(l_Ext_payee_rec, P_EXT_PAYEE_ID, x_return_status, X_MSG_COUNT, X_MSG_DATA);

Line: 522

	insert_payee_row(l_Ext_payee_rec, P_EXT_PAYEE_ID, x_return_status, X_MSG_COUNT, X_MSG_DATA);

Line: 548

   SELECT
	Uses.instrument_payment_use_id ,
	Payee.ext_payee_id,
	Uses.instrument_id bank_account_id
   FROM
	Iby_pmt_instr_uses_all uses,
	Iby_external_payees_all payee
   WHERE
	Payee.ext_payee_id = uses.ext_pmt_party_id
	AND uses.payment_function = 'PAYABLES_DISB'
	AND uses.payment_flow = 'DISBURSEMENTS'
	AND uses.instrument_type = 'BANKACCOUNT'
	AND payee.payee_party_id = cp_party_id
	AND (((payee.supplier_site_id IS NULL) AND (cp_vendor_site_id IS NULL)) OR (payee.supplier_site_id = cp_vendor_site_id))
	AND (((payee.party_site_id IS NULL) AND (cp_party_site_id IS NULL)) OR (payee.party_site_id = cp_party_site_id))
	AND (((payee.org_id IS NULL) AND (cp_org_id IS NULL)) OR (payee.org_id = cp_org_id))
	AND (((payee.org_type IS NULL) AND (cp_org_type IS NULL)) OR (payee.org_type = cp_org_type));

Line: 567

   SELECT party_site_id
   FROM ap_supplier_sites_all
   WHERE vendor_site_id = cp_vendor_site_id;

Line: 572

   SELECT ext_payee_id
   FROM Iby_external_payees_all payee
   WHERE payee.payee_party_id = cp_party_id
   AND (((payee.supplier_site_id IS NULL) AND (cp_vendor_site_id IS NULL)) OR (payee.supplier_site_id = cp_vendor_site_id))
   AND (((payee.party_site_id IS NULL) AND (cp_party_site_id IS NULL)) OR (payee.party_site_id = cp_party_site_id))
   AND (((payee.org_id IS NULL) AND (cp_org_id IS NULL)) OR (payee.org_id = cp_org_id))
   AND (((payee.org_type IS NULL) AND (cp_org_type IS NULL)) OR (payee.org_type = cp_org_type));

Line: 823

	UPDATE iby_pmt_instr_uses_all
	SET end_date = SYSDATE
	WHERE ext_pmt_party_id IN
		(SELECT ext_payee_id
		FROM Iby_external_payees_all
		WHERE payee_party_id = P_from_party_id
		  AND supplier_site_id = P_from_vendor_site_id )
	AND payment_function = 'PAYABLES_DISB'
	AND payment_flow = 'DISBURSEMENTS'
	AND instrument_type = 'BANKACCOUNT'
	AND NVL(end_date,SYSDATE) >= SYSDATE;