[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure insert_payee_row(ext_payee_id IN NUMBER,
ext_payee_rec IN External_Payee_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2 )
is
l_module_name VARCHAR2(100) := G_PKG_NAME || 'insert_payee_row';
SELECT payer.DEBIT_ADVICE_DELIVERY_METHOD,
payer.DEBIT_ADVICE_EMAIL, payer.DEBIT_ADVICE_FAX
FROM iby_external_payers_all payer,hz_cust_accounts acct, hz_cust_acct_sites_all hzcustacct, HZ_CUST_SITE_USES_ALL siteuses
WHERE payer.PARTY_ID = p_payee_party_id
AND nvl(payer.ORG_ID,-1) = nvl(p_payer_org_id,-1)
AND nvl(payer.ORG_TYPE,-1) = nvl(p_payer_org_type,-1)
AND nvl(payer.acct_site_use_id,-1) = nvl(siteuses.site_use_id,-1)
AND acct.party_id = payer.PARTY_ID
AND acct.cust_account_id = payer.cust_account_id
AND acct.cust_account_id = hzcustacct.cust_account_id(+)
AND hzcustacct.cust_acct_site_id = siteuses.cust_acct_site_id(+)
AND hzcustacct.party_site_id(+) = nvl(p_party_site_id,-1)
AND siteuses.site_use_code(+) = 'BILL_TO' ;
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 (
ext_payee_id,
ext_payee_rec.Payee_Party_Id,
ext_payee_rec.Payment_Function,
ext_payee_rec.Exclusive_Pay_Flag,
fnd_global.user_id,
SYSDATE, -- bug 13881024
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
1.0,
ext_payee_rec.Payee_Party_Site_Id,
ext_payee_rec.Supplier_Site_Id,
ext_payee_rec.Payer_Org_Id,
ext_payee_rec.Payer_Org_Type,
nvl(ext_payee_rec.edi_payment_method,ext_payee_rec.Default_Pmt_method),
ext_payee_rec.ECE_TP_Loc_Code,
ext_payee_rec.Bank_Charge_Bearer,
nvl(ext_payee_rec.edi_payment_format,ext_payee_rec.Bank_Instr1_Code),
nvl(ext_payee_rec.edi_transaction_handling,ext_payee_rec.Bank_Instr2_Code),
ext_payee_rec.Bank_Instr_Detail,
ext_payee_rec.Pay_Reason_Code,
ext_payee_rec.Pay_Reason_Com,
ext_payee_rec.Inactive_Date,
nvl(ext_payee_rec.edi_remittance_instruction,ext_payee_rec.Pay_Message1),
ext_payee_rec.Pay_Message2,
ext_payee_rec.Pay_Message3,
nvl(ext_payee_rec.edi_remittance_method,ext_payee_rec.Delivery_Channel),
ext_payee_rec.Pmt_Format,
ext_payee_rec.Settlement_Priority,
l_remit_advice_delivery_method,
l_remit_advice_email,
l_remit_advice_fax
);
print_debuginfo(l_module_name, 'Exception while insertion into iby_external_payees_all. ');
end insert_payee_row;
SELECT COUNT(instrid)
FROM iby_creditcard
WHERE (instrid = ci_instrid);
SELECT COUNT(ext_bank_account_id)
FROM iby_ext_bank_accounts_v
WHERE (ext_bank_account_id = ci_instrid);
SELECT nvl(vendor_type_lookup_code, 'NOT EMPLOYEE'),
nvl(vendor_site_code, 'NOT EMPLOYEE')
INTO vendor_type,
vendor_site
FROM ap_suppliers aps,
ap_supplier_sites_all apss
WHERE apss.vendor_site_id = p_payee.supplier_site_id
AND aps.party_id = p_payee.party_id
and aps.vendor_id = apss.vendor_id; /* bug 16521484 */
SELECT ext_payee_id
FROM iby_external_payees_all payee
WHERE payee.PAYEE_PARTY_ID = ci_party_id
AND payee.PAYMENT_FUNCTION = ci_pmt_function
AND ((ci_party_site_id is NULL and payee.PARTY_SITE_ID is NULL) OR
(payee.PARTY_SITE_ID = ci_party_site_id))
AND ((ci_supplier_site_id is NULL and payee.SUPPLIER_SITE_ID is NULL) OR
(payee.SUPPLIER_SITE_ID = ci_supplier_site_id))
AND ((ci_org_id is NULL and payee.ORG_ID is NULL) OR
(payee.ORG_ID = ci_org_id AND payee.ORG_TYPE = ci_org_type));
l_parameter_list.DELETE;
SELECT count(payee.EXT_PAYEE_ID), max(payee.EXT_PAYEE_ID)
FROM iby_external_payees_all payee
WHERE payee.PAYEE_PARTY_ID = p_payee_party_id
AND payee.PAYMENT_FUNCTION = p_payment_function
AND ((p_party_site_id is NULL and payee.PARTY_SITE_ID is NULL) OR
(payee.PARTY_SITE_ID = p_party_site_id))
AND ((p_supplier_site_id is NULL and payee.SUPPLIER_SITE_ID is NULL) OR
(payee.SUPPLIER_SITE_ID = p_supplier_site_id))
AND ((p_payer_org_id is NULL and payee.ORG_ID is NULL) OR
(payee.ORG_ID = p_payer_org_id AND payee.ORG_TYPE = p_payer_org_type));
SELECT nvl(exclusive_payment_flag,'N')
INTO l_ext_payee_tab(counter).Exclusive_Pay_Flag
FROM iby_internal_payers_all
WHERE ORG_ID IS NULL;
select IBY_EXTERNAL_PAYEES_ALL_S.nextval into l_ext_payee_id from dual;
insert_payee_row(l_ext_payee_id,
l_ext_payee_tab(counter),
l_payee_crt_status);
select count(1)
into l_pm_count
from iby_payment_methods_b
where payment_method_code=p_ext_payee_tab(counter).Default_Pmt_method;
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,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
VALUES
(
IBY_EXT_PARTY_PMT_MTHDS_S.nextval,
p_ext_payee_tab(counter).Default_Pmt_method,
'DISBURSEMENTS',
l_ext_payee_id,
p_ext_payee_tab(counter).Payment_function,
'Y',
fnd_global.user_id,
SYSDATE, -- bug 13881024
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
1.0
);
SELECT instrument_payment_use_id
FROM iby_pmt_instr_uses_all
WHERE (payment_flow = G_PMT_FLOW_DISBURSE)
AND ( (instrument_payment_use_id = NVL(ci_assign_id,-1))
OR (ext_pmt_party_id = ci_payee_id
AND instrument_type = ci_instr_type
AND instrument_id = ci_instr_id )
);
SELECT count(*)
FROM IBY_ACCOUNT_OWNERS
WHERE EXT_BANK_ACCOUNT_ID = ci_instr_id
AND ACCOUNT_OWNER_PARTY_ID = ci_party_id;
print_debuginfo(l_module,'Inside if l_payee_id is null, trying to insert in external_payees_all ');
select IBY_EXTERNAL_PAYEES_ALL_S.nextval into l_payee_id from dual;
insert_payee_row(l_payee_id, l_ext_payee_rec, l_payee_crt_status);
print_debuginfo(l_module, 'After inserting a default payee row.');
SELECT order_of_preference
INTO l_cur_op
FROM iby_pmt_instr_uses_all
WHERE instrument_payment_use_id = x_assign_id;
UPDATE iby_pmt_instr_uses_all
SET order_of_preference = order_of_preference + 1,
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_pmt_party_id = l_payee_id
AND payment_flow = G_PMT_FLOW_DISBURSE
AND order_of_preference >= l_priority;
UPDATE iby_pmt_instr_uses_all
SET order_of_preference = order_of_preference + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id
WHERE ext_pmt_party_id = l_payee_id
AND payment_flow = G_PMT_FLOW_DISBURSE
AND order_of_preference < l_cur_op
AND order_of_preference >= l_giv_op ;
UPDATE iby_pmt_instr_uses_all
SET order_of_preference = order_of_preference - 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id
WHERE ext_pmt_party_id = l_payee_id
AND payment_flow = G_PMT_FLOW_DISBURSE
AND order_of_preference > l_cur_op
AND order_of_preference <= l_giv_op ;
SELECT iby_pmt_instr_uses_all_s.nextval
INTO x_assign_id
FROM DUAL;
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_payee_id,
p_assignment_attribs.Instrument.Instrument_Type,
p_assignment_attribs.Instrument.Instrument_Id,
p_payee.Payment_Function,
G_PMT_FLOW_DISBURSE,
l_priority,
null, null, null, null, null,
NVL(p_assignment_attribs.Start_Date,SYSDATE),
p_assignment_attribs.End_Date,
fnd_global.user_id,
SYSDATE, -- bug 13881024
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
1);
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 = SYSDATE,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number + 1
WHERE instrument_payment_use_id = x_assign_id;
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_DISBURSE)
AND (ext_pmt_party_id = ci_payee_id);
SELECT DISTINCT u.instrument_type, u.instrument_id
FROM iby_pmt_instr_uses_all u, iby_external_payees_all p
WHERE (u.payment_flow = G_PMT_FLOW_DISBURSE)
AND (u.ext_pmt_party_id = p.ext_payee_id)
AND (p.payee_party_id = ci_party_id);
SELECT COUNT(LOOKUP_CODE)
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = p_lookup_type
AND LOOKUP_CODE = p_lookup_code;
SELECT country
FROM HZ_PARTIES
WHERE party_id = p_payee_id;
SELECT locs.country
FROM HZ_PARTY_SITES sites,
HZ_LOCATIONS locs
WHERE sites.party_id = p_payee_id
AND sites.party_site_id = p_payee_site_id
AND sites.location_id = locs.location_id;
SELECT COUNT(payment_reason_code)
FROM IBY_PAYMENT_REASONS_VL ibypr
WHERE ibypr.payment_reason_code = p_pmt_reason_code
AND (ibypr.inactive_date is NULL OR ibypr.inactive_date >= trunc(sysdate));
SELECT COUNT(delivery_channel_code)
FROM IBY_DELIVERY_CHANNELS_VL ibydlv
WHERE ibydlv.delivery_channel_code = p_dlv_channel_code
AND (ibydlv.inactive_date is NULL OR ibydlv.inactive_date >= trunc(sysdate));
SELECT COUNT(f.format_code)
FROM IBY_FORMATS_VL f
WHERE f.format_code = p_payment_format_code;
SELECT COUNT(Payment_Method_Name)
PAYMENT_METHOD_CODE
FROM IBY_PAYMENT_METHODS_VL
WHERE PAYMENT_METHOD_CODE = p_payment_mthd_code;
SELECT
EXT_BANK_ACCOUNT_ID,
COUNTRY_CODE,
BRANCH_ID,
BANK_ID,
BANK_NAME,
BANK_NUMBER,
BANK_NAME_ALT,
BANK_INSTITUTION_TYPE,
BANK_ADDRESS_ID,
BRANCH_NUMBER,
BRANCH_TYPE,
BRANCH_NAME,
BRANCH_NAME_ALT,
BIC,
RFC_IDENTIFIER,
BANK_CODE,
BRANCH_ADDRESS_ID,
ACCOUNT_OWNER_PARTY_ID,
OWNER_PRIMARY_FLAG,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
CURRENCY_CODE,
IBAN,
CHECK_DIGITS,
BANK_ACCOUNT_NAME_ALT,
BANK_ACCOUNT_TYPE,
ACCOUNT_SUFFIX,
DESCRIPTION,
AGENCY_LOCATION_CODE,
PAYMENT_FACTOR_FLAG,
FOREIGN_PAYMENT_USE_FLAG,
EXCHANGE_RATE_AGREEMENT_NUM,
EXCHANGE_RATE_AGREEMENT_TYPE,
EXCHANGE_RATE,
START_DATE,
END_DATE,
ATTRIBUTE_CATEGORY,
NOTE,
NOTE_ALT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
CALLING_APP_UNIQUE_REF1,
CALLING_APP_UNIQUE_REF2,
EXT_PAYEE_ID
FROM IBY_TEMP_EXT_BANK_ACCTS
WHERE TEMP_EXT_BANK_ACCT_ID = p_temp_ext_acct_id;
SELECT
EXT_BANK_ACCOUNT_ID,
COUNTRY_CODE,
BRANCH_ID,
BANK_ID,
BANK_NAME,
BANK_NUMBER,
BANK_NAME_ALT,
BANK_INSTITUTION_TYPE,
BANK_ADDRESS_ID,
BRANCH_NUMBER,
BRANCH_TYPE,
BRANCH_NAME,
BRANCH_NAME_ALT,
BIC,
RFC_IDENTIFIER,
BANK_CODE,
BRANCH_ADDRESS_ID,
ACCOUNT_OWNER_PARTY_ID,
OWNER_PRIMARY_FLAG,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
CURRENCY_CODE,
IBAN,
CHECK_DIGITS,
BANK_ACCOUNT_NAME_ALT,
BANK_ACCOUNT_TYPE,
ACCOUNT_SUFFIX,
DESCRIPTION,
AGENCY_LOCATION_CODE,
PAYMENT_FACTOR_FLAG,
FOREIGN_PAYMENT_USE_FLAG,
EXCHANGE_RATE_AGREEMENT_NUM,
EXCHANGE_RATE_AGREEMENT_TYPE,
EXCHANGE_RATE,
START_DATE,
END_DATE,
ATTRIBUTE_CATEGORY,
NOTE,
NOTE_ALT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
CALLING_APP_UNIQUE_REF1,
CALLING_APP_UNIQUE_REF2,
EXT_PAYEE_ID
FROM IBY_TEMP_EXT_BANK_ACCTS
WHERE TEMP_EXT_BANK_ACCT_ID = p_temp_ext_acct_id;
SELECT
EXT_BANK_ACCOUNT_ID,
COUNTRY_CODE,
BRANCH_ID,
BANK_ID,
BANK_NAME,
BANK_NUMBER,
BANK_NAME_ALT,
BANK_INSTITUTION_TYPE,
BANK_ADDRESS_ID,
BRANCH_NUMBER,
BRANCH_TYPE,
BRANCH_NAME,
BRANCH_NAME_ALT,
BIC,
RFC_IDENTIFIER,
BANK_CODE,
BRANCH_ADDRESS_ID,
ACCOUNT_OWNER_PARTY_ID,
OWNER_PRIMARY_FLAG,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
CURRENCY_CODE,
IBAN,
CHECK_DIGITS,
BANK_ACCOUNT_NAME_ALT,
BANK_ACCOUNT_TYPE,
ACCOUNT_SUFFIX,
DESCRIPTION,
AGENCY_LOCATION_CODE,
PAYMENT_FACTOR_FLAG,
FOREIGN_PAYMENT_USE_FLAG,
EXCHANGE_RATE_AGREEMENT_NUM,
EXCHANGE_RATE_AGREEMENT_TYPE,
EXCHANGE_RATE,
START_DATE,
END_DATE,
ATTRIBUTE_CATEGORY,
NOTE,
NOTE_ALT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
CALLING_APP_UNIQUE_REF1,
CALLING_APP_UNIQUE_REF2,
EXT_PAYEE_ID
FROM IBY_TEMP_EXT_BANK_ACCTS
WHERE TEMP_EXT_BANK_ACCT_ID = p_temp_ext_acct_id;
SELECT BANK_PARTY_ID,
bank_name,
bank_number,
BANK_INSTITUTION_TYPE,
HOME_COUNTRY,
BANK_NAME_ALT,
description,
SHORT_BANK_NAME
FROM CE_BANKS_V
WHERE BANK_PARTY_ID = p_bank_id;
SELECT branch_party_id,
bank_party_id,
BANK_BRANCH_NAME,
branch_number,
BANK_BRANCH_TYPE,
BANK_BRANCH_NAME_ALT
FROM CE_BANK_BRANCHES_V
WHERE bank_party_id = p_bank_id
AND branch_party_id = p_bank_branch_id;
PROCEDURE Update_External_Payee (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
p_ext_payee_tab IN External_Payee_Tab_Type,
p_ext_payee_id_tab IN Ext_Payee_ID_Tab_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_ext_payee_status_tab OUT NOCOPY Ext_Payee_Update_Tab_Type) IS
l_api_name CONSTANT VARCHAR2(30) := 'Create_External_Payee';
l_ext_payee_upd_rec Ext_Payee_Update_Rec_Type;
SELECT count(payee.EXT_PAYEE_ID), max(payee.EXT_PAYEE_ID)
FROM iby_external_payees_all payee
WHERE payee.PAYEE_PARTY_ID = p_payee_party_id
AND payee.PAYMENT_FUNCTION = p_payment_function
AND ((p_party_site_id is NULL and payee.PARTY_SITE_ID is NULL) OR
(payee.PARTY_SITE_ID = p_party_site_id))
AND ((p_supplier_site_id is NULL and payee.SUPPLIER_SITE_ID is NULL) OR
(payee.SUPPLIER_SITE_ID = p_supplier_site_id))
AND ((p_payer_org_id is NULL and payee.ORG_ID is NULL) OR
(payee.ORG_ID = p_payer_org_id AND payee.ORG_TYPE = p_payer_org_type));
print_debuginfo(l_module_name,'Payee to update does not exist.');
l_ext_payee_upd_rec.Payee_Update_Status := 'E';
l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
l_ext_payee_upd_rec.Payee_Update_Status := 'E';
l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
l_ext_payee_upd_rec.Payee_Update_Status := 'E';
l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
l_ext_payee_upd_rec.Payee_Update_Status := 'E';
l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
l_ext_payee_upd_rec.Payee_Update_Status := 'E';
l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
l_ext_payee_upd_rec.Payee_Update_Status := 'E';
l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
l_ext_payee_upd_rec.Payee_Update_Status := 'E';
l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
UPDATE iby_external_payees_all
SET exclusive_payment_flag = p_ext_payee_tab(counter).exclusive_pay_flag,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE, -- bug 13881024
last_update_login = fnd_global.user_id,
object_version_number = object_version_number+1,
default_payment_method_code = p_ext_payee_tab(counter).Default_Pmt_method,
ece_tp_location_code = p_ext_payee_tab(counter).ece_tp_loc_code,
bank_charge_bearer = p_ext_payee_tab(counter).Bank_Charge_Bearer,
bank_instruction1_code = p_ext_payee_tab(counter).Bank_Instr1_Code,
bank_instruction2_code = p_ext_payee_tab(counter).Bank_Instr2_Code,
bank_instruction_details = p_ext_payee_tab(counter).Bank_Instr_Detail,
payment_reason_code = p_ext_payee_tab(counter).Pay_Reason_Code,
payment_reason_comments = p_ext_payee_tab(counter).Pay_Reason_Com,
inactive_date = p_ext_payee_tab(counter).Inactive_Date,
payment_text_message1 = p_ext_payee_tab(counter).Pay_Message1,
payment_text_message2 = p_ext_payee_tab(counter).Pay_Message2,
payment_text_message3 = p_ext_payee_tab(counter).Pay_Message3,
delivery_channel_code = p_ext_payee_tab(counter).Delivery_Channel,
payment_format_code = p_ext_payee_tab(counter).Pmt_Format,
settlement_priority = p_ext_payee_tab(counter).Settlement_Priority,
remit_advice_email = p_ext_payee_tab(counter).Remit_advice_email,
remit_advice_delivery_method = p_ext_payee_tab(counter).Remit_advice_delivery_method,
remit_advice_fax = p_ext_payee_tab(counter).remit_advice_fax
WHERE ext_payee_id = p_ext_payee_id_tab(counter).ext_payee_id;
UPDATE iby_ext_party_pmt_mthds
SET primary_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.user_id,
object_version_number = object_version_number+1
WHERE ext_pmt_party_id = p_ext_payee_id_tab(counter).ext_payee_id
AND payment_function = p_ext_payee_tab(counter).payment_function
AND primary_flag = 'Y';
SELECT COUNT(1)
INTO l_pm_count
FROM iby_payment_methods_b
WHERE payment_method_code = p_ext_payee_tab(counter).Default_Pmt_method;
SELECT primary_flag
INTO l_primary_flag
FROM iby_ext_party_pmt_mthds
WHERE ext_pmt_party_id = p_ext_payee_id_tab(counter).ext_payee_id
AND payment_function = p_ext_payee_tab(counter).payment_function
AND payment_method_code=p_ext_payee_tab(counter).Default_Pmt_method;
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,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
) VALUES (
IBY_EXT_PARTY_PMT_MTHDS_S.nextval,
p_ext_payee_tab(counter).Default_Pmt_method,
'DISBURSEMENTS',
p_ext_payee_id_tab(counter).ext_payee_id,
p_ext_payee_tab(counter).Payment_function,
'Y',
fnd_global.user_id,
SYSDATE, -- bug 13881024
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
1.0
);
UPDATE iby_ext_party_pmt_mthds
SET primary_flag = DECODE(payment_method_code,
p_ext_payee_tab(counter).Default_Pmt_method, 'Y', 'N'),
last_update_date = SYSDATE, -- bug 13881024
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.user_id,
object_version_number = object_version_number+1
WHERE ext_pmt_party_id = p_ext_payee_id_tab(counter).ext_payee_id
AND payment_function = p_ext_payee_tab(counter).payment_function;
l_ext_payee_upd_rec.Payee_Update_Status := 'S';
print_debuginfo(l_module_name, 'Creation status is ' || l_ext_payee_upd_rec.Payee_Update_Status);
END Update_External_Payee;