[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW (
X_ROWID in out nocopy VARCHAR2,
X_SYSTEM_PROFILE_CODE in VARCHAR2,
X_REMIT_PMT_DETAILS_LENGTH_LIM in NUMBER,
X_REMIT_REMITTANCE_ADVICE_FORM in VARCHAR2,
X_REMIT_SRA_OVERRIDE_PAYEE_FLA in VARCHAR2,
X_REMIT_ADVICE_DELIVERY_METHOD in VARCHAR2,
X_REMIT_AUTOMATIC_SRA_SUBMIT in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_PMT_PAYMENT_DETAILS_FORMULA in VARCHAR2,
X_INST_GROUP_BY_LEGAL_ENTITY in VARCHAR2,
X_INST_GROUP_BY_BILL_PAYABLE in VARCHAR2,
X_INST_GROUP_BY_MAX_INSTRUCTIO in VARCHAR2,
X_INST_GROUP_BY_ORGANIZATION in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_FUNCTI in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_REASON in VARCHAR2,
X_INST_GROUP_BY_RFC in VARCHAR2,
X_INST_MAX_PAYMENTS_PER_INSTR in NUMBER,
X_INST_MAX_AMOUNT_PER_INSTR_VA in NUMBER,
X_INST_MAX_AMOUNT_PER_INSTR_CU in VARCHAR2,
X_INST_MAX_AMOUNT_FX_RATE_TYPE in VARCHAR2,
X_INST_SORT_OPTION_1 in VARCHAR2,
X_INST_SORT_ORDER_1 in VARCHAR2,
X_INST_SORT_OPTION_2 in VARCHAR2,
X_POSITIVE_PAY_DELIVERY_FLAG in VARCHAR2,
X_TRANSMIT_INSTR_IMMED_FLAG in VARCHAR2,
X_AUTOMATIC_PI_REG_SUBMIT in VARCHAR2,
X_PMT_GROUP_BY_DELIVERY_CHANNE in VARCHAR2,
X_PMT_GROUP_BY_REMITTANCE_MES in VARCHAR2,
X_PMT_GROUP_BY_MAX_DOCUMENTS in VARCHAR2,
X_PMT_GROUP_BY_UNIQUE_REMIT_ID in VARCHAR2,
X_PMT_GROUP_BY_SETTLE_PRIORITY in VARCHAR2,
X_PMT_GROUP_BY_PAYMENT_REASON in VARCHAR2,
X_PMT_GROUP_BY_DUE_DATE_FLAG in VARCHAR2,
X_PMT_MAX_DOCUMENTS_PER_PAYMEN in NUMBER,
X_PMT_PAYMENT_DETAILS_LENGTH_L in NUMBER,
X_PMT_GROUP_BY_PAYMENT_DETAILS in VARCHAR2,
X_PMT_GROUP_BY_BANK_CHARGE_BEA in VARCHAR2,
X_INST_SORT_ORDER_2 in VARCHAR2,
X_INST_SORT_OPTION_3 in VARCHAR2,
X_INST_SORT_ORDER_3 in VARCHAR2,
X_REMIT_DOCUMENT_COUNT_LIMIT in NUMBER,
X_REMIT_ALLOW_MULTIPLE_COPY_FL in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_DATE in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_CURREN in VARCHAR2,
X_INST_GROUP_BY_INT_BANK_ACCT in VARCHAR2,
X_INST_GROUP_BY_MAX_PAYMENTS in VARCHAR2,
X_INST_GROUP_BY_PAY_SERVICE_RE in VARCHAR2,
X_DCL_ONLY_FOREIGN_CURR_PMT_FL in VARCHAR2,
X_DECLARATION_REPORT_FORMAT_CO in VARCHAR2,
X_DECLARATION_CURR_FX_RATE_TYP in VARCHAR2,
X_DECLARATION_CURRENCY_CODE in VARCHAR2,
X_DECLARATION_THRESHOLD_AMOUNT in NUMBER,
X_BANK_INSTRUCTION1_CODE in VARCHAR2,
X_BANK_INSTRUCTION2_CODE in VARCHAR2,
X_BANK_INSTRUCTION_DETAILS in VARCHAR2,
X_PAYMENT_TEXT_MESSAGE1 in VARCHAR2,
X_PAYMENT_TEXT_MESSAGE2 in VARCHAR2,
X_ACK_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_ACK_SECURITY_PROTOCOL_CODE in VARCHAR2,
X_SEND_TO_FILE_FLAG in VARCHAR2,
X_PI_REGISTER_FORMAT in VARCHAR2,
X_BEP_ACCOUNT_ID in NUMBER,
X_APPLICABLE_PMT_METHOD in VARCHAR2,
X_APPLICABLE_CURRENCY in VARCHAR2,
X_APPLICABLE_PAYER_ORG in VARCHAR2,
X_APPLICABLE_INT_BANK_ACCOUNT in VARCHAR2,
X_BEP_SUFFIX in VARCHAR2,
X_SECURITY_PROTOCOL_CODE in VARCHAR2,
X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_PAYMENT_FORMAT_CODE in VARCHAR2,
X_POSITIVE_PAY_FORMAT_CODE in VARCHAR2,
X_PAY_FILE_LETTER_FORMAT_CODE in VARCHAR2,
X_PRINT_INSTRUCTION_IMMED_FLAG in VARCHAR2,
X_PERIODIC_SEQUENCE_NAME_1 in VARCHAR2,
X_PERIODIC_SEQUENCE_NAME_2 in VARCHAR2,
X_PERIODIC_SEQUENCE_NAME_3 in VARCHAR2,
X_PROCESSING_TYPE in VARCHAR2,
X_MARK_COMPLETE_EVENT in VARCHAR2,
X_MANUAL_MARK_COMPLETE_FLAG in VARCHAR2,
X_DECLARATION_OPTION in VARCHAR2,
X_SYSTEM_PROFILE_NAME in VARCHAR2,
X_SYSTEM_PROFILE_DESCRIPTION in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_ELECTRONIC_PROCESSING_CHNNL in VARCHAR2,
X_LOGICAL_GROUPING_MODE in VARCHAR2,
X_BATCH_BOOKING_FLAG in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_METHOD in VARCHAR2,
X_INACTIVE_DATE in DATE
) is
cursor C is select ROWID from IBY_UPG_PPP_B
where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
;
insert into IBY_UPG_PPP_B (
REMIT_PMT_DETAILS_LENGTH_LIM,
REMIT_REMITTANCE_ADVICE_FORMAT,
REMIT_SRA_OVERRIDE_PAYEE_FLAG,
REMIT_ADVICE_DELIVERY_METHOD,
REMIT_AUTOMATIC_SRA_SUBMIT,
OBJECT_VERSION_NUMBER,
PMT_PAYMENT_DETAILS_FORMULA,
INST_GROUP_BY_LEGAL_ENTITY,
INST_GROUP_BY_BILL_PAYABLE,
INST_GROUP_BY_MAX_INSTRUCTION,
INST_GROUP_BY_ORGANIZATION,
INST_GROUP_BY_PAYMENT_FUNCTION,
INST_GROUP_BY_PAYMENT_REASON,
INST_GROUP_BY_RFC,
INST_MAX_PAYMENTS_PER_INSTR,
INST_MAX_AMOUNT_PER_INSTR_VAL,
INST_MAX_AMOUNT_PER_INSTR_CURR,
INST_MAX_AMOUNT_FX_RATE_TYPE,
INST_SORT_OPTION_1,
INST_SORT_ORDER_1,
INST_SORT_OPTION_2,
POSITIVE_PAY_DELIVERY_FLAG,
TRANSMIT_INSTR_IMMED_FLAG,
AUTOMATIC_PI_REG_SUBMIT,
PMT_GROUP_BY_DELIVERY_CHANNEL,
PMT_GROUP_BY_REMITTANCE_MES,
PMT_GROUP_BY_MAX_DOCUMENTS,
PMT_GROUP_BY_UNIQUE_REMIT_ID,
PMT_GROUP_BY_SETTLE_PRIORITY,
PMT_GROUP_BY_PAYMENT_REASON,
PMT_GROUP_BY_DUE_DATE_FLAG,
PMT_MAX_DOCUMENTS_PER_PAYMENT,
PMT_PAYMENT_DETAILS_LENGTH_LIM,
PMT_GROUP_BY_PAYMENT_DETAILS,
PMT_GROUP_BY_BANK_CHARGE_BEAR,
INST_SORT_ORDER_2,
INST_SORT_OPTION_3,
INST_SORT_ORDER_3,
REMIT_DOCUMENT_COUNT_LIMIT,
REMIT_ALLOW_MULTIPLE_COPY_FLAG,
INST_GROUP_BY_PAYMENT_DATE,
INST_GROUP_BY_PAYMENT_CURRENCY,
INST_GROUP_BY_INT_BANK_ACCT,
INST_GROUP_BY_MAX_PAYMENTS,
INST_GROUP_BY_PAY_SERVICE_REQ,
DCL_ONLY_FOREIGN_CURR_PMT_FLAG,
DECLARATION_REPORT_FORMAT_CODE,
DECLARATION_CURR_FX_RATE_TYPE,
DECLARATION_CURRENCY_CODE,
DECLARATION_THRESHOLD_AMOUNT,
BANK_INSTRUCTION1_CODE,
BANK_INSTRUCTION2_CODE,
BANK_INSTRUCTION_DETAILS,
PAYMENT_TEXT_MESSAGE1,
PAYMENT_TEXT_MESSAGE2,
ACK_TRANSMIT_PROTOCOL_CODE,
ACK_SECURITY_PROTOCOL_CODE,
SEND_TO_FILE_FLAG,
PI_REGISTER_FORMAT,
BEP_ACCOUNT_ID,
APPLICABLE_PMT_METHOD,
APPLICABLE_CURRENCY,
APPLICABLE_PAYER_ORG,
APPLICABLE_INT_BANK_ACCOUNT,
SYSTEM_PROFILE_CODE,
BEP_SUFFIX,
SECURITY_PROTOCOL_CODE,
TRANSMIT_PROTOCOL_CODE,
PAYMENT_FORMAT_CODE,
POSITIVE_PAY_FORMAT_CODE,
PAY_FILE_LETTER_FORMAT_CODE,
PRINT_INSTRUCTION_IMMED_FLAG,
PERIODIC_SEQUENCE_NAME_1,
PERIODIC_SEQUENCE_NAME_2,
PERIODIC_SEQUENCE_NAME_3,
PROCESSING_TYPE,
MARK_COMPLETE_EVENT,
MANUAL_MARK_COMPLETE_FLAG,
DECLARATION_OPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ELECTRONIC_PROCESSING_CHANNEL,
LOGICAL_GROUPING_MODE,
BATCH_BOOKING_FLAG,
INST_GROUP_BY_PAYMENT_METHOD,
INACTIVE_DATE
) values (
X_REMIT_PMT_DETAILS_LENGTH_LIM,
X_REMIT_REMITTANCE_ADVICE_FORM,
X_REMIT_SRA_OVERRIDE_PAYEE_FLA,
X_REMIT_ADVICE_DELIVERY_METHOD,
X_REMIT_AUTOMATIC_SRA_SUBMIT,
X_OBJECT_VERSION_NUMBER,
X_PMT_PAYMENT_DETAILS_FORMULA,
X_INST_GROUP_BY_LEGAL_ENTITY,
X_INST_GROUP_BY_BILL_PAYABLE,
X_INST_GROUP_BY_MAX_INSTRUCTIO,
X_INST_GROUP_BY_ORGANIZATION,
X_INST_GROUP_BY_PAYMENT_FUNCTI,
X_INST_GROUP_BY_PAYMENT_REASON,
X_INST_GROUP_BY_RFC,
X_INST_MAX_PAYMENTS_PER_INSTR,
X_INST_MAX_AMOUNT_PER_INSTR_VA,
X_INST_MAX_AMOUNT_PER_INSTR_CU,
X_INST_MAX_AMOUNT_FX_RATE_TYPE,
X_INST_SORT_OPTION_1,
X_INST_SORT_ORDER_1,
X_INST_SORT_OPTION_2,
X_POSITIVE_PAY_DELIVERY_FLAG,
X_TRANSMIT_INSTR_IMMED_FLAG,
X_AUTOMATIC_PI_REG_SUBMIT,
X_PMT_GROUP_BY_DELIVERY_CHANNE,
X_PMT_GROUP_BY_REMITTANCE_MES,
X_PMT_GROUP_BY_MAX_DOCUMENTS,
X_PMT_GROUP_BY_UNIQUE_REMIT_ID,
X_PMT_GROUP_BY_SETTLE_PRIORITY,
X_PMT_GROUP_BY_PAYMENT_REASON,
X_PMT_GROUP_BY_DUE_DATE_FLAG,
X_PMT_MAX_DOCUMENTS_PER_PAYMEN,
X_PMT_PAYMENT_DETAILS_LENGTH_L,
X_PMT_GROUP_BY_PAYMENT_DETAILS,
X_PMT_GROUP_BY_BANK_CHARGE_BEA,
X_INST_SORT_ORDER_2,
X_INST_SORT_OPTION_3,
X_INST_SORT_ORDER_3,
X_REMIT_DOCUMENT_COUNT_LIMIT,
X_REMIT_ALLOW_MULTIPLE_COPY_FL,
X_INST_GROUP_BY_PAYMENT_DATE,
X_INST_GROUP_BY_PAYMENT_CURREN,
X_INST_GROUP_BY_INT_BANK_ACCT,
X_INST_GROUP_BY_MAX_PAYMENTS,
X_INST_GROUP_BY_PAY_SERVICE_RE,
X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
X_DECLARATION_REPORT_FORMAT_CO,
X_DECLARATION_CURR_FX_RATE_TYP,
X_DECLARATION_CURRENCY_CODE,
X_DECLARATION_THRESHOLD_AMOUNT,
X_BANK_INSTRUCTION1_CODE,
X_BANK_INSTRUCTION2_CODE,
X_BANK_INSTRUCTION_DETAILS,
X_PAYMENT_TEXT_MESSAGE1,
X_PAYMENT_TEXT_MESSAGE2,
X_ACK_TRANSMIT_PROTOCOL_CODE,
X_ACK_SECURITY_PROTOCOL_CODE,
X_SEND_TO_FILE_FLAG,
X_PI_REGISTER_FORMAT,
X_BEP_ACCOUNT_ID,
X_APPLICABLE_PMT_METHOD,
X_APPLICABLE_CURRENCY,
X_APPLICABLE_PAYER_ORG,
X_APPLICABLE_INT_BANK_ACCOUNT,
X_SYSTEM_PROFILE_CODE,
X_BEP_SUFFIX,
X_SECURITY_PROTOCOL_CODE,
X_TRANSMIT_PROTOCOL_CODE,
X_PAYMENT_FORMAT_CODE,
X_POSITIVE_PAY_FORMAT_CODE,
X_PAY_FILE_LETTER_FORMAT_CODE,
X_PRINT_INSTRUCTION_IMMED_FLAG,
X_PERIODIC_SEQUENCE_NAME_1,
X_PERIODIC_SEQUENCE_NAME_2,
X_PERIODIC_SEQUENCE_NAME_3,
X_PROCESSING_TYPE,
X_MARK_COMPLETE_EVENT,
X_MANUAL_MARK_COMPLETE_FLAG,
X_DECLARATION_OPTION,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_ELECTRONIC_PROCESSING_CHNNL,
X_LOGICAL_GROUPING_MODE,
X_BATCH_BOOKING_FLAG,
X_INST_GROUP_BY_PAYMENT_METHOD,
X_INACTIVE_DATE
);
insert into IBY_UPG_PPP_TL (
SYSTEM_PROFILE_CODE,
SYSTEM_PROFILE_NAME,
SYSTEM_PROFILE_DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LANGUAGE,
SOURCE_LANG
) select
X_SYSTEM_PROFILE_CODE,
X_SYSTEM_PROFILE_NAME,
X_SYSTEM_PROFILE_DESCRIPTION,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_OBJECT_VERSION_NUMBER,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from IBY_UPG_PPP_TL T
where T.SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_ROW;
cursor c is select
REMIT_PMT_DETAILS_LENGTH_LIM,
REMIT_REMITTANCE_ADVICE_FORMAT,
REMIT_SRA_OVERRIDE_PAYEE_FLAG,
REMIT_ADVICE_DELIVERY_METHOD,
REMIT_AUTOMATIC_SRA_SUBMIT,
OBJECT_VERSION_NUMBER,
PMT_PAYMENT_DETAILS_FORMULA,
INST_GROUP_BY_LEGAL_ENTITY,
INST_GROUP_BY_BILL_PAYABLE,
INST_GROUP_BY_MAX_INSTRUCTION,
INST_GROUP_BY_ORGANIZATION,
INST_GROUP_BY_PAYMENT_FUNCTION,
INST_GROUP_BY_PAYMENT_REASON,
INST_GROUP_BY_RFC,
INST_MAX_PAYMENTS_PER_INSTR,
INST_MAX_AMOUNT_PER_INSTR_VAL,
INST_MAX_AMOUNT_PER_INSTR_CURR,
INST_MAX_AMOUNT_FX_RATE_TYPE,
INST_SORT_OPTION_1,
INST_SORT_ORDER_1,
INST_SORT_OPTION_2,
POSITIVE_PAY_DELIVERY_FLAG,
TRANSMIT_INSTR_IMMED_FLAG,
AUTOMATIC_PI_REG_SUBMIT,
PMT_GROUP_BY_DELIVERY_CHANNEL,
PMT_GROUP_BY_REMITTANCE_MES,
PMT_GROUP_BY_MAX_DOCUMENTS,
PMT_GROUP_BY_UNIQUE_REMIT_ID,
PMT_GROUP_BY_SETTLE_PRIORITY,
PMT_GROUP_BY_PAYMENT_REASON,
PMT_GROUP_BY_DUE_DATE_FLAG,
PMT_MAX_DOCUMENTS_PER_PAYMENT,
PMT_PAYMENT_DETAILS_LENGTH_LIM,
PMT_GROUP_BY_PAYMENT_DETAILS,
PMT_GROUP_BY_BANK_CHARGE_BEAR,
INST_SORT_ORDER_2,
INST_SORT_OPTION_3,
INST_SORT_ORDER_3,
REMIT_DOCUMENT_COUNT_LIMIT,
REMIT_ALLOW_MULTIPLE_COPY_FLAG,
INST_GROUP_BY_PAYMENT_DATE,
INST_GROUP_BY_PAYMENT_CURRENCY,
INST_GROUP_BY_INT_BANK_ACCT,
INST_GROUP_BY_MAX_PAYMENTS,
INST_GROUP_BY_PAY_SERVICE_REQ,
DCL_ONLY_FOREIGN_CURR_PMT_FLAG,
DECLARATION_REPORT_FORMAT_CODE,
DECLARATION_CURR_FX_RATE_TYPE,
DECLARATION_CURRENCY_CODE,
DECLARATION_THRESHOLD_AMOUNT,
BANK_INSTRUCTION1_CODE,
BANK_INSTRUCTION2_CODE,
BANK_INSTRUCTION_DETAILS,
PAYMENT_TEXT_MESSAGE1,
PAYMENT_TEXT_MESSAGE2,
ACK_TRANSMIT_PROTOCOL_CODE,
ACK_SECURITY_PROTOCOL_CODE,
SEND_TO_FILE_FLAG,
PI_REGISTER_FORMAT,
BEP_ACCOUNT_ID,
APPLICABLE_PMT_METHOD,
APPLICABLE_CURRENCY,
APPLICABLE_PAYER_ORG,
APPLICABLE_INT_BANK_ACCOUNT,
BEP_SUFFIX,
SECURITY_PROTOCOL_CODE,
TRANSMIT_PROTOCOL_CODE,
PAYMENT_FORMAT_CODE,
POSITIVE_PAY_FORMAT_CODE,
PAY_FILE_LETTER_FORMAT_CODE,
PRINT_INSTRUCTION_IMMED_FLAG,
PERIODIC_SEQUENCE_NAME_1,
PERIODIC_SEQUENCE_NAME_2,
PERIODIC_SEQUENCE_NAME_3,
PROCESSING_TYPE,
MARK_COMPLETE_EVENT,
MANUAL_MARK_COMPLETE_FLAG,
DECLARATION_OPTION,
ELECTRONIC_PROCESSING_CHANNEL,
LOGICAL_GROUPING_MODE,
BATCH_BOOKING_FLAG,
INST_GROUP_BY_PAYMENT_METHOD
from IBY_UPG_PPP_B
where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
for update of SYSTEM_PROFILE_CODE nowait;
cursor c1 is select
SYSTEM_PROFILE_NAME,
SYSTEM_PROFILE_DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from IBY_UPG_PPP_TL
where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of SYSTEM_PROFILE_CODE nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_SYSTEM_PROFILE_CODE in VARCHAR2,
X_REMIT_PMT_DETAILS_LENGTH_LIM in NUMBER,
X_REMIT_REMITTANCE_ADVICE_FORM in VARCHAR2,
X_REMIT_SRA_OVERRIDE_PAYEE_FLA in VARCHAR2,
X_REMIT_ADVICE_DELIVERY_METHOD in VARCHAR2,
X_REMIT_AUTOMATIC_SRA_SUBMIT in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_PMT_PAYMENT_DETAILS_FORMULA in VARCHAR2,
X_INST_GROUP_BY_LEGAL_ENTITY in VARCHAR2,
X_INST_GROUP_BY_BILL_PAYABLE in VARCHAR2,
X_INST_GROUP_BY_MAX_INSTRUCTIO in VARCHAR2,
X_INST_GROUP_BY_ORGANIZATION in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_FUNCTI in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_REASON in VARCHAR2,
X_INST_GROUP_BY_RFC in VARCHAR2,
X_INST_MAX_PAYMENTS_PER_INSTR in NUMBER,
X_INST_MAX_AMOUNT_PER_INSTR_VA in NUMBER,
X_INST_MAX_AMOUNT_PER_INSTR_CU in VARCHAR2,
X_INST_MAX_AMOUNT_FX_RATE_TYPE in VARCHAR2,
X_INST_SORT_OPTION_1 in VARCHAR2,
X_INST_SORT_ORDER_1 in VARCHAR2,
X_INST_SORT_OPTION_2 in VARCHAR2,
X_POSITIVE_PAY_DELIVERY_FLAG in VARCHAR2,
X_TRANSMIT_INSTR_IMMED_FLAG in VARCHAR2,
X_AUTOMATIC_PI_REG_SUBMIT in VARCHAR2,
X_PMT_GROUP_BY_DELIVERY_CHANNE in VARCHAR2,
X_PMT_GROUP_BY_REMITTANCE_MES in VARCHAR2,
X_PMT_GROUP_BY_MAX_DOCUMENTS in VARCHAR2,
X_PMT_GROUP_BY_UNIQUE_REMIT_ID in VARCHAR2,
X_PMT_GROUP_BY_SETTLE_PRIORITY in VARCHAR2,
X_PMT_GROUP_BY_PAYMENT_REASON in VARCHAR2,
X_PMT_GROUP_BY_DUE_DATE_FLAG in VARCHAR2,
X_PMT_MAX_DOCUMENTS_PER_PAYMEN in NUMBER,
X_PMT_PAYMENT_DETAILS_LENGTH_L in NUMBER,
X_PMT_GROUP_BY_PAYMENT_DETAILS in VARCHAR2,
X_PMT_GROUP_BY_BANK_CHARGE_BEA in VARCHAR2,
X_INST_SORT_ORDER_2 in VARCHAR2,
X_INST_SORT_OPTION_3 in VARCHAR2,
X_INST_SORT_ORDER_3 in VARCHAR2,
X_REMIT_DOCUMENT_COUNT_LIMIT in NUMBER,
X_REMIT_ALLOW_MULTIPLE_COPY_FL in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_DATE in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_CURREN in VARCHAR2,
X_INST_GROUP_BY_INT_BANK_ACCT in VARCHAR2,
X_INST_GROUP_BY_MAX_PAYMENTS in VARCHAR2,
X_INST_GROUP_BY_PAY_SERVICE_RE in VARCHAR2,
X_DCL_ONLY_FOREIGN_CURR_PMT_FL in VARCHAR2,
X_DECLARATION_REPORT_FORMAT_CO in VARCHAR2,
X_DECLARATION_CURR_FX_RATE_TYP in VARCHAR2,
X_DECLARATION_CURRENCY_CODE in VARCHAR2,
X_DECLARATION_THRESHOLD_AMOUNT in NUMBER,
X_BANK_INSTRUCTION1_CODE in VARCHAR2,
X_BANK_INSTRUCTION2_CODE in VARCHAR2,
X_BANK_INSTRUCTION_DETAILS in VARCHAR2,
X_PAYMENT_TEXT_MESSAGE1 in VARCHAR2,
X_PAYMENT_TEXT_MESSAGE2 in VARCHAR2,
X_ACK_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_ACK_SECURITY_PROTOCOL_CODE in VARCHAR2,
X_SEND_TO_FILE_FLAG in VARCHAR2,
X_PI_REGISTER_FORMAT in VARCHAR2,
X_BEP_ACCOUNT_ID in NUMBER,
X_APPLICABLE_PMT_METHOD in VARCHAR2,
X_APPLICABLE_CURRENCY in VARCHAR2,
X_APPLICABLE_PAYER_ORG in VARCHAR2,
X_APPLICABLE_INT_BANK_ACCOUNT in VARCHAR2,
X_BEP_SUFFIX in VARCHAR2,
X_SECURITY_PROTOCOL_CODE in VARCHAR2,
X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_PAYMENT_FORMAT_CODE in VARCHAR2,
X_POSITIVE_PAY_FORMAT_CODE in VARCHAR2,
X_PAY_FILE_LETTER_FORMAT_CODE in VARCHAR2,
X_PRINT_INSTRUCTION_IMMED_FLAG in VARCHAR2,
X_PERIODIC_SEQUENCE_NAME_1 in VARCHAR2,
X_PERIODIC_SEQUENCE_NAME_2 in VARCHAR2,
X_PERIODIC_SEQUENCE_NAME_3 in VARCHAR2,
X_PROCESSING_TYPE in VARCHAR2,
X_MARK_COMPLETE_EVENT in VARCHAR2,
X_MANUAL_MARK_COMPLETE_FLAG in VARCHAR2,
X_DECLARATION_OPTION in VARCHAR2,
X_SYSTEM_PROFILE_NAME in VARCHAR2,
X_SYSTEM_PROFILE_DESCRIPTION in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_ELECTRONIC_PROCESSING_CHNNL in VARCHAR2,
X_LOGICAL_GROUPING_MODE in VARCHAR2,
X_BATCH_BOOKING_FLAG in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_METHOD in VARCHAR2,
X_INACTIVE_DATE in DATE
) is
l_jenl_eft_reporting_limit NUMBER;
select system_profile_code
from
(SELECT DISTINCT(decode(sfgdf.org_id,
NULL,ppp.system_profile_code || '_' ||
to_char(cf.check_format_id),
ppp.system_profile_code || '_' || to_char
(cf.check_format_id) || '_' || sfgdf.org_id))
system_profile_code
FROM
ap_check_formats cf,
ap_check_stocks_all cs,
ce_upg_bank_accounts cba,
ap_payment_programs pp,
jg_zz_pay_format_info gdf,
(SELECT(
CASE
WHEN sfa.jgzz_attribute_category =
'SE.JESEPBAI' THEN sfa.jgzz_system_info5
END) AS
payment_text_message1,
(
CASE
WHEN sfa.jgzz_attribute_category =
'SE.JESEPBAI' THEN sfa.jgzz_system_info3
END) AS
payment_text_message2,
(
CASE
WHEN sfa.jgzz_attribute_category =
'NO.JENOPTGN' THEN
sfa.jgzz_system_info20
WHEN sfa.jgzz_attribute_category =
'SE.JESEPBAI' THEN sfa.jgzz_system_info10
WHEN sfa.jgzz_attribute_category =
'SE.JESEPBSI' THEN sfa.jgzz_system_info8
WHEN sfa.jgzz_attribute_category =
'SE.JESEPBUT' THEN sfa.jgzz_system_info8
WHEN sfa.jgzz_attribute_category =
'SE.JESEPPOI' THEN sfa.jgzz_system_info11
WHEN sfa.jgzz_attribute_category =
'SE.JESEPPOU' THEN sfa.jgzz_system_info10
END) AS
outbound_pmt_file_directory,
(
CASE
WHEN sfa.jgzz_attribute_category =
'DE.JEDEDEFI' THEN decode
(sfa.jgzz_system_info3, 'Y',
'DECLARE_THROUGH_BANK', 'N',
'NO_DECLARATIONS',
sfa.jgzz_system_info3)
WHEN sfa.jgzz_attribute_category =
'NL.JENLFFGN' THEN decode(
l_jenl_eft_reporting_limit,
NULL,
'NO_DECLARATIONS',
'DECLARE_THROUGH_BANK')
END)
AS
declaration_option,
(
CASE
WHEN sfa.jgzz_attribute_category =
'DE.JEDEDEFI' THEN to_number
(sfa.jgzz_system_info5)
WHEN sfa.jgzz_attribute_category =
'NL.JENLFFGN' THEN
l_jenl_eft_reporting_limit
END)
AS
declaration_threshold_amount,
(
CASE
WHEN sfa.jgzz_attribute_category =
'DE.JEDEDEFI'
AND sfa.jgzz_system_info5 IS NOT NULL
THEN 'EUR'
WHEN sfa.jgzz_attribute_category =
'NL.JENLFFGN'
AND
l_jenl_eft_reporting_limit
IS
NOT NULL THEN
'NLG'
END)
AS
declaration_currency_code,
(
CASE
WHEN sfa.jgzz_attribute_category =
'NL.JENLFFGN' THEN sfa.jgzz_system_info13
WHEN sfa.jgzz_attribute_category =
'DE.JEDEDEFI' THEN decode
(sfa.jgzz_system_info3, 'Y', 'Corporate')
END) AS
declaration_curr_fx_rate_type,
sfa.jgzz_attribute_category,
SUBSTR(sfa.jgzz_attribute_category,
LENGTH(sfa.jgzz_country_code) + 2,
LENGTH(sfa.jgzz_attribute_category))
format_code,
sfa.org_id
FROM jg_zz_sys_formats_all_b sfa)
sfgdf,
iby_formats_b ipf,
iby_upg_ppp_b ppp,
iby_bepinfo bep,
iby_bepkeys KEY
WHERE cf.format_payments_program_id =
pp.program_id
AND cf.check_format_id =
cs.check_format_id(+)
AND cs.bank_account_id = cba.source_pk_id
(+)
AND cba.source_application_id(+) = 200
AND cf.check_format_id =
gdf.check_format_id(+)
AND pp.program_name =
ipf.reference_format_code
AND ipf.format_type_code =
'OUTBOUND_PAYMENT_INSTRUCTION'
AND pp.program_name = sfgdf.format_code
(+)
AND ppp.payment_format_code =
ipf.format_code
AND ppp.bep_suffix = bep.suffix(+)
AND bep.bepid = KEY.bepid(+)
AND ppp.system_profile_code =
X_SYSTEM_PROFILE_CODE
UNION ALL
SELECT distinct(ppb.system_profile_code) system_profile_code
FROM iby_formats_b ipf,
iby_upg_ppp_tl ppp,
iby_upg_ppp_b ppb,
iby_acct_pmt_profiles_b app,
iby_bepinfo bep,
iby_bepkeys key1
WHERE(ipf.reference_format_code IS NULL OR(ipf.reference_format_code IS NOT NULL
AND ipf.reference_format_code NOT IN
(SELECT pp.program_name
FROM ap_check_formats cf, ap_payment_programs pp
WHERE cf.format_payments_program_id = pp.program_id)))
AND ppb.payment_format_code = ipf.format_code
AND ipf.format_type_code = 'OUTBOUND_PAYMENT_INSTRUCTION'
AND ppp.system_profile_code = app.system_profile_code
AND ppp.system_profile_code = ppb.system_profile_code
AND ppb.bep_suffix = bep.suffix(+)
AND app.bep_account_id = key1.bep_account_id(+)
AND ppp.system_profile_code = X_SYSTEM_PROFILE_CODE) SYSTEM_PROF_CODE_TABLE;
SELECT TO_NUMBER(x.profile_option_value)
INTO l_jenl_eft_reporting_limit
FROM ( SELECT o.profile_option_name, v.profile_option_value,
row_number() OVER (partition by o.profile_option_name
order by count(*) desc) order_rank
FROM fnd_profile_options o,
fnd_profile_option_values v
WHERE o.profile_option_name = UPPER('JENL_REPORTING_THRESHOLD')
AND v.application_id = o.application_id
AND v.profile_option_id = o.profile_option_id
GROUP BY o.profile_option_name, v.profile_option_value) x
WHERE x.order_rank = 1;
update IBY_UPG_PPP_B set
REMIT_PMT_DETAILS_LENGTH_LIM = X_REMIT_PMT_DETAILS_LENGTH_LIM,
REMIT_REMITTANCE_ADVICE_FORMAT = X_REMIT_REMITTANCE_ADVICE_FORM,
REMIT_SRA_OVERRIDE_PAYEE_FLAG = X_REMIT_SRA_OVERRIDE_PAYEE_FLA,
REMIT_ADVICE_DELIVERY_METHOD = X_REMIT_ADVICE_DELIVERY_METHOD,
REMIT_AUTOMATIC_SRA_SUBMIT = X_REMIT_AUTOMATIC_SRA_SUBMIT,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
PMT_PAYMENT_DETAILS_FORMULA = X_PMT_PAYMENT_DETAILS_FORMULA,
INST_GROUP_BY_LEGAL_ENTITY = X_INST_GROUP_BY_LEGAL_ENTITY,
INST_GROUP_BY_BILL_PAYABLE = X_INST_GROUP_BY_BILL_PAYABLE,
INST_GROUP_BY_MAX_INSTRUCTION = X_INST_GROUP_BY_MAX_INSTRUCTIO,
INST_GROUP_BY_ORGANIZATION = X_INST_GROUP_BY_ORGANIZATION,
INST_GROUP_BY_PAYMENT_FUNCTION = X_INST_GROUP_BY_PAYMENT_FUNCTI,
INST_GROUP_BY_PAYMENT_REASON = X_INST_GROUP_BY_PAYMENT_REASON,
INST_GROUP_BY_RFC = X_INST_GROUP_BY_RFC,
INST_MAX_PAYMENTS_PER_INSTR = X_INST_MAX_PAYMENTS_PER_INSTR,
INST_MAX_AMOUNT_PER_INSTR_VAL = X_INST_MAX_AMOUNT_PER_INSTR_VA,
INST_MAX_AMOUNT_PER_INSTR_CURR = X_INST_MAX_AMOUNT_PER_INSTR_CU,
INST_MAX_AMOUNT_FX_RATE_TYPE = X_INST_MAX_AMOUNT_FX_RATE_TYPE,
INST_SORT_OPTION_1 = X_INST_SORT_OPTION_1,
INST_SORT_ORDER_1 = X_INST_SORT_ORDER_1,
INST_SORT_OPTION_2 = X_INST_SORT_OPTION_2,
POSITIVE_PAY_DELIVERY_FLAG = X_POSITIVE_PAY_DELIVERY_FLAG,
TRANSMIT_INSTR_IMMED_FLAG = X_TRANSMIT_INSTR_IMMED_FLAG,
AUTOMATIC_PI_REG_SUBMIT = X_AUTOMATIC_PI_REG_SUBMIT,
PMT_GROUP_BY_DELIVERY_CHANNEL = X_PMT_GROUP_BY_DELIVERY_CHANNE,
PMT_GROUP_BY_REMITTANCE_MES = X_PMT_GROUP_BY_REMITTANCE_MES,
PMT_GROUP_BY_MAX_DOCUMENTS = X_PMT_GROUP_BY_MAX_DOCUMENTS,
PMT_GROUP_BY_UNIQUE_REMIT_ID = X_PMT_GROUP_BY_UNIQUE_REMIT_ID,
PMT_GROUP_BY_SETTLE_PRIORITY = X_PMT_GROUP_BY_SETTLE_PRIORITY,
PMT_GROUP_BY_PAYMENT_REASON = X_PMT_GROUP_BY_PAYMENT_REASON,
PMT_GROUP_BY_DUE_DATE_FLAG = X_PMT_GROUP_BY_DUE_DATE_FLAG,
PMT_MAX_DOCUMENTS_PER_PAYMENT = X_PMT_MAX_DOCUMENTS_PER_PAYMEN,
PMT_PAYMENT_DETAILS_LENGTH_LIM = X_PMT_PAYMENT_DETAILS_LENGTH_L,
PMT_GROUP_BY_PAYMENT_DETAILS = X_PMT_GROUP_BY_PAYMENT_DETAILS,
PMT_GROUP_BY_BANK_CHARGE_BEAR = X_PMT_GROUP_BY_BANK_CHARGE_BEA,
INST_SORT_ORDER_2 = X_INST_SORT_ORDER_2,
INST_SORT_OPTION_3 = X_INST_SORT_OPTION_3,
INST_SORT_ORDER_3 = X_INST_SORT_ORDER_3,
REMIT_DOCUMENT_COUNT_LIMIT = X_REMIT_DOCUMENT_COUNT_LIMIT,
REMIT_ALLOW_MULTIPLE_COPY_FLAG = X_REMIT_ALLOW_MULTIPLE_COPY_FL,
INST_GROUP_BY_PAYMENT_DATE = X_INST_GROUP_BY_PAYMENT_DATE,
INST_GROUP_BY_PAYMENT_CURRENCY = X_INST_GROUP_BY_PAYMENT_CURREN,
INST_GROUP_BY_INT_BANK_ACCT = X_INST_GROUP_BY_INT_BANK_ACCT,
INST_GROUP_BY_MAX_PAYMENTS = X_INST_GROUP_BY_MAX_PAYMENTS,
INST_GROUP_BY_PAY_SERVICE_REQ = X_INST_GROUP_BY_PAY_SERVICE_RE,
DCL_ONLY_FOREIGN_CURR_PMT_FLAG = X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
DECLARATION_REPORT_FORMAT_CODE = X_DECLARATION_REPORT_FORMAT_CO,
DECLARATION_CURR_FX_RATE_TYPE = X_DECLARATION_CURR_FX_RATE_TYP,
DECLARATION_CURRENCY_CODE = X_DECLARATION_CURRENCY_CODE,
DECLARATION_THRESHOLD_AMOUNT = X_DECLARATION_THRESHOLD_AMOUNT,
BANK_INSTRUCTION1_CODE = X_BANK_INSTRUCTION1_CODE,
BANK_INSTRUCTION2_CODE = X_BANK_INSTRUCTION2_CODE,
BANK_INSTRUCTION_DETAILS = X_BANK_INSTRUCTION_DETAILS,
PAYMENT_TEXT_MESSAGE1 = X_PAYMENT_TEXT_MESSAGE1,
PAYMENT_TEXT_MESSAGE2 = X_PAYMENT_TEXT_MESSAGE2,
ACK_TRANSMIT_PROTOCOL_CODE = X_ACK_TRANSMIT_PROTOCOL_CODE,
ACK_SECURITY_PROTOCOL_CODE = X_ACK_SECURITY_PROTOCOL_CODE,
SEND_TO_FILE_FLAG = X_SEND_TO_FILE_FLAG,
PI_REGISTER_FORMAT = X_PI_REGISTER_FORMAT,
BEP_ACCOUNT_ID = X_BEP_ACCOUNT_ID,
APPLICABLE_PMT_METHOD = X_APPLICABLE_PMT_METHOD,
APPLICABLE_CURRENCY = X_APPLICABLE_CURRENCY,
APPLICABLE_PAYER_ORG = X_APPLICABLE_PAYER_ORG,
APPLICABLE_INT_BANK_ACCOUNT = X_APPLICABLE_INT_BANK_ACCOUNT,
BEP_SUFFIX = X_BEP_SUFFIX,
SECURITY_PROTOCOL_CODE = X_SECURITY_PROTOCOL_CODE,
TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE,
PAYMENT_FORMAT_CODE = X_PAYMENT_FORMAT_CODE,
POSITIVE_PAY_FORMAT_CODE = X_POSITIVE_PAY_FORMAT_CODE,
PAY_FILE_LETTER_FORMAT_CODE = X_PAY_FILE_LETTER_FORMAT_CODE,
PRINT_INSTRUCTION_IMMED_FLAG = X_PRINT_INSTRUCTION_IMMED_FLAG,
PERIODIC_SEQUENCE_NAME_1 = X_PERIODIC_SEQUENCE_NAME_1,
PERIODIC_SEQUENCE_NAME_2 = X_PERIODIC_SEQUENCE_NAME_2,
PERIODIC_SEQUENCE_NAME_3 = X_PERIODIC_SEQUENCE_NAME_3,
PROCESSING_TYPE = X_PROCESSING_TYPE,
MARK_COMPLETE_EVENT = X_MARK_COMPLETE_EVENT,
MANUAL_MARK_COMPLETE_FLAG = X_MANUAL_MARK_COMPLETE_FLAG,
DECLARATION_OPTION = X_DECLARATION_OPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
ELECTRONIC_PROCESSING_CHANNEL = X_ELECTRONIC_PROCESSING_CHNNL,
LOGICAL_GROUPING_MODE = X_LOGICAL_GROUPING_MODE,
BATCH_BOOKING_FLAG = X_BATCH_BOOKING_FLAG,
INST_GROUP_BY_PAYMENT_METHOD = X_INST_GROUP_BY_PAYMENT_METHOD,
INACTIVE_DATE = X_INACTIVE_DATE
where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE;
update iby_sys_pmt_profiles_b set
payment_format_code = X_PAYMENT_FORMAT_CODE,
print_instruction_immed_flag = X_PRINT_INSTRUCTION_IMMED_FLAG,
processing_type = X_PROCESSING_TYPE,
mark_complete_event = X_MARK_COMPLETE_EVENT,
manual_mark_complete_flag = X_MANUAL_MARK_COMPLETE_FLAG,
positive_pay_delivery_flag= X_POSITIVE_PAY_DELIVERY_FLAG,
object_version_number= X_OBJECT_VERSION_NUMBER,
transmit_instr_immed_flag= X_TRANSMIT_INSTR_IMMED_FLAG,
automatic_pi_reg_submit = X_AUTOMATIC_PI_REG_SUBMIT,
send_to_file_flag = X_SEND_TO_FILE_FLAG,
dcl_only_foreign_curr_pmt_flag = X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
security_protocol_code = X_SECURITY_PROTOCOL_CODE,
transmit_protocol_code = X_TRANSMIT_PROTOCOL_CODE,
positive_pay_format_code = X_POSITIVE_PAY_FORMAT_CODE,
pay_file_letter_format_code = X_PAY_FILE_LETTER_FORMAT_CODE,
periodic_sequence_name_1 = X_PERIODIC_SEQUENCE_NAME_1,
periodic_sequence_name_2= X_PERIODIC_SEQUENCE_NAME_2,
periodic_sequence_name_3= X_PERIODIC_SEQUENCE_NAME_3,
declaration_option= X_DECLARATION_OPTION,
declaration_report_format_code =X_DECLARATION_REPORT_FORMAT_CO,
declaration_curr_fx_rate_type = X_DECLARATION_CURR_FX_RATE_TYP,
declaration_currency_code = X_DECLARATION_CURRENCY_CODE,
declaration_threshold_amount = X_DECLARATION_THRESHOLD_AMOUNT,
bank_instruction1_code= X_BANK_INSTRUCTION1_CODE,
bank_instruction2_code= X_BANK_INSTRUCTION2_CODE,
bank_instruction_details = X_BANK_INSTRUCTION_DETAILS,
payment_text_message1= X_PAYMENT_TEXT_MESSAGE1,
payment_text_message2=X_PAYMENT_TEXT_MESSAGE2,
ack_transmit_protocol_code = X_ACK_TRANSMIT_PROTOCOL_CODE,
ack_security_protocol_code= X_ACK_SECURITY_PROTOCOL_CODE,
pi_register_format= X_PI_REGISTER_FORMAT,
electronic_processing_channel= X_ELECTRONIC_PROCESSING_CHNNL,
logical_grouping_mode=X_LOGICAL_GROUPING_MODE,
BATCH_BOOKING_FLAG = X_BATCH_BOOKING_FLAG,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
INACTIVE_DATE = X_INACTIVE_DATE
where SYSTEM_PROFILE_CODE = l_sys_profile_code_tab(i);
update IBY_PMT_CREATION_RULES set
group_by_delivery_channel = X_PMT_GROUP_BY_DELIVERY_CHANNE,
group_by_remittance_message = X_PMT_GROUP_BY_REMITTANCE_MES,
group_by_max_documents_flag = X_PMT_GROUP_BY_MAX_DOCUMENTS,
group_by_unique_remit_id_flag = X_PMT_GROUP_BY_UNIQUE_REMIT_ID,
group_by_settle_priority_flag = X_PMT_GROUP_BY_SETTLE_PRIORITY,
group_by_payment_reason = X_PMT_GROUP_BY_PAYMENT_REASON,
group_by_due_date_flag = X_PMT_GROUP_BY_DUE_DATE_FLAG,
max_documents_per_payment = X_PMT_MAX_DOCUMENTS_PER_PAYMEN,
payment_details_length_limit = X_PMT_PAYMENT_DETAILS_LENGTH_L,
payment_details_formula = X_PMT_PAYMENT_DETAILS_FORMULA,
group_by_payment_details_flag = X_PMT_GROUP_BY_PAYMENT_DETAILS,
group_by_bank_charge_bearer = X_PMT_GROUP_BY_BANK_CHARGE_BEA,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where system_profile_code = l_sys_profile_code_tab(i);
update iby_acct_pmt_profiles_b set
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
BEP_ACCOUNT_ID = X_BEP_ACCOUNT_ID
where SYSTEM_PROFILE_CODE = l_sys_profile_code_tab(i) ;
update iby_instr_creation_rules set
group_by_payment_date= X_INST_GROUP_BY_PAYMENT_DATE,
group_by_payment_currency = X_INST_GROUP_BY_PAYMENT_CURREN,
group_by_internal_bank_account = X_INST_GROUP_BY_INT_BANK_ACCT,
group_by_max_payments_flag = X_INST_GROUP_BY_MAX_PAYMENTS,
group_by_pay_service_request = X_INST_GROUP_BY_PAY_SERVICE_RE,
group_by_legal_entity = X_INST_GROUP_BY_LEGAL_ENTITY,
group_by_bill_payable = X_INST_GROUP_BY_BILL_PAYABLE,
group_by_organization = X_INST_GROUP_BY_ORGANIZATION,
group_by_max_instruction_flag = X_INST_GROUP_BY_MAX_INSTRUCTIO,
group_by_payment_function = X_INST_GROUP_BY_PAYMENT_FUNCTI,
group_by_payment_reason = X_INST_GROUP_BY_PAYMENT_REASON,
group_by_rfc= X_INST_GROUP_BY_RFC,
max_payments_per_instruction = X_INST_MAX_PAYMENTS_PER_INSTR,
max_amount_per_instr_value = X_INST_MAX_AMOUNT_PER_INSTR_VA,
max_amount_per_instr_curr_code= X_INST_MAX_AMOUNT_PER_INSTR_CU,
max_amount_fx_rate_type= X_INST_MAX_AMOUNT_FX_RATE_TYPE,
SORT_OPTION_1 = X_INST_SORT_OPTION_1,
SORT_ORDER_1 = X_INST_SORT_ORDER_1,
SORT_OPTION_2 = X_INST_SORT_OPTION_2,
SORT_ORDER_2 = X_INST_SORT_ORDER_2,
SORT_OPTION_3 = X_INST_SORT_OPTION_3,
SORT_ORDER_3 = X_INST_SORT_ORDER_3,
group_by_payment_method = X_INST_GROUP_BY_PAYMENT_METHOD,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where SYSTEM_PROFILE_CODE = l_sys_profile_code_tab(i) ;
update iby_remit_advice_setup set
document_count_limit = X_REMIT_DOCUMENT_COUNT_LIMIT,
allow_multiple_copy_flag = X_REMIT_ALLOW_MULTIPLE_COPY_FL,
remittance_advice_format_code = X_REMIT_REMITTANCE_ADVICE_FORM,
sra_override_payee_flag = X_REMIT_SRA_OVERRIDE_PAYEE_FLA,
REMIT_ADVICE_DELIVERY_METHOD = X_REMIT_ADVICE_DELIVERY_METHOD,
automatic_sra_submit_flag = X_REMIT_AUTOMATIC_SRA_SUBMIT,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where system_profile_code = l_sys_profile_code_tab(i);
update IBY_UPG_PPP_TL set
SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME,
SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update IBY_UPG_PPP_TL set
SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME,
SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update IBY_SYS_PMT_PROFILES_TL set
SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME,
SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end UPDATE_ROW;
procedure DELETE_ROW (
X_SYSTEM_PROFILE_CODE in VARCHAR2
) is
begin
delete from IBY_UPG_PPP_TL
where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE;
delete from IBY_UPG_PPP_B
where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE;
end DELETE_ROW;
delete from IBY_UPG_PPP_TL T
where not exists
(select NULL
from IBY_UPG_PPP_B B
where B.SYSTEM_PROFILE_CODE = T.SYSTEM_PROFILE_CODE
);
update IBY_UPG_PPP_TL T set (
SYSTEM_PROFILE_NAME,
SYSTEM_PROFILE_DESCRIPTION
) = (select
B.SYSTEM_PROFILE_NAME,
B.SYSTEM_PROFILE_DESCRIPTION
from IBY_UPG_PPP_TL B
where B.SYSTEM_PROFILE_CODE = T.SYSTEM_PROFILE_CODE
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.SYSTEM_PROFILE_CODE,
T.LANGUAGE
) in (select
SUBT.SYSTEM_PROFILE_CODE,
SUBT.LANGUAGE
from IBY_UPG_PPP_TL SUBB, IBY_UPG_PPP_TL SUBT
where SUBB.SYSTEM_PROFILE_CODE = SUBT.SYSTEM_PROFILE_CODE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.SYSTEM_PROFILE_NAME <> SUBT.SYSTEM_PROFILE_NAME
or SUBB.SYSTEM_PROFILE_DESCRIPTION <> SUBT.SYSTEM_PROFILE_DESCRIPTION
or (SUBB.SYSTEM_PROFILE_DESCRIPTION is null and SUBT.SYSTEM_PROFILE_DESCRIPTION is not null)
or (SUBB.SYSTEM_PROFILE_DESCRIPTION is not null and SUBT.SYSTEM_PROFILE_DESCRIPTION is null)
));
insert into IBY_UPG_PPP_TL (
SYSTEM_PROFILE_CODE,
SYSTEM_PROFILE_NAME,
SYSTEM_PROFILE_DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.SYSTEM_PROFILE_CODE,
B.SYSTEM_PROFILE_NAME,
B.SYSTEM_PROFILE_DESCRIPTION,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.OBJECT_VERSION_NUMBER,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from IBY_UPG_PPP_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from IBY_UPG_PPP_TL T
where T.SYSTEM_PROFILE_CODE = B.SYSTEM_PROFILE_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_ELECTRONIC_PROCESSING_CHNNL in VARCHAR2,
X_LOGICAL_GROUPING_MODE in VARCHAR2,
X_BATCH_BOOKING_FLAG in VARCHAR2,
X_INST_GROUP_BY_PAYMENT_METHOD in VARCHAR2,
X_INACTIVE_DATE in DATE
)
is
row_id VARCHAR2(200);
UPDATE_ROW (
X_SYSTEM_PROFILE_CODE,
X_REMIT_PMT_DETAILS_LENGTH_LIM,
X_REMIT_REMITTANCE_ADVICE_FORM,
X_REMIT_SRA_OVERRIDE_PAYEE_FLA,
X_REMIT_ADVICE_DELIVERY_METHOD,
X_REMIT_AUTOMATIC_SRA_SUBMIT,
X_OBJECT_VERSION_NUMBER,
X_PMT_PAYMENT_DETAILS_FORMULA,
X_INST_GROUP_BY_LEGAL_ENTITY,
X_INST_GROUP_BY_BILL_PAYABLE,
X_INST_GROUP_BY_MAX_INSTRUCTIO,
X_INST_GROUP_BY_ORGANIZATION,
X_INST_GROUP_BY_PAYMENT_FUNCTI,
X_INST_GROUP_BY_PAYMENT_REASON,
X_INST_GROUP_BY_RFC,
X_INST_MAX_PAYMENTS_PER_INSTR,
X_INST_MAX_AMOUNT_PER_INSTR_VA,
X_INST_MAX_AMOUNT_PER_INSTR_CU,
X_INST_MAX_AMOUNT_FX_RATE_TYPE,
X_INST_SORT_OPTION_1,
X_INST_SORT_ORDER_1,
X_INST_SORT_OPTION_2,
X_POSITIVE_PAY_DELIVERY_FLAG,
X_TRANSMIT_INSTR_IMMED_FLAG,
X_AUTOMATIC_PI_REG_SUBMIT,
X_PMT_GROUP_BY_DELIVERY_CHANNE,
X_PMT_GROUP_BY_REMITTANCE_MES,
X_PMT_GROUP_BY_MAX_DOCUMENTS,
X_PMT_GROUP_BY_UNIQUE_REMIT_ID,
X_PMT_GROUP_BY_SETTLE_PRIORITY,
X_PMT_GROUP_BY_PAYMENT_REASON,
X_PMT_GROUP_BY_DUE_DATE_FLAG,
X_PMT_MAX_DOCUMENTS_PER_PAYMEN,
X_PMT_PAYMENT_DETAILS_LENGTH_L,
X_PMT_GROUP_BY_PAYMENT_DETAILS,
X_PMT_GROUP_BY_BANK_CHARGE_BEA,
X_INST_SORT_ORDER_2,
X_INST_SORT_OPTION_3,
X_INST_SORT_ORDER_3,
X_REMIT_DOCUMENT_COUNT_LIMIT,
X_REMIT_ALLOW_MULTIPLE_COPY_FL,
X_INST_GROUP_BY_PAYMENT_DATE,
X_INST_GROUP_BY_PAYMENT_CURREN,
X_INST_GROUP_BY_INT_BANK_ACCT,
X_INST_GROUP_BY_MAX_PAYMENTS,
X_INST_GROUP_BY_PAY_SERVICE_RE,
X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
X_DECLARATION_REPORT_FORMAT_CO,
X_DECLARATION_CURR_FX_RATE_TYP,
X_DECLARATION_CURRENCY_CODE,
X_DECLARATION_THRESHOLD_AMOUNT,
X_BANK_INSTRUCTION1_CODE,
X_BANK_INSTRUCTION2_CODE,
X_BANK_INSTRUCTION_DETAILS,
X_PAYMENT_TEXT_MESSAGE1,
X_PAYMENT_TEXT_MESSAGE2,
X_ACK_TRANSMIT_PROTOCOL_CODE,
X_ACK_SECURITY_PROTOCOL_CODE,
X_SEND_TO_FILE_FLAG,
X_PI_REGISTER_FORMAT,
X_BEP_ACCOUNT_ID,
X_APPLICABLE_PMT_METHOD,
X_APPLICABLE_CURRENCY,
X_APPLICABLE_PAYER_ORG,
X_APPLICABLE_INT_BANK_ACCOUNT,
X_BEP_SUFFIX,
X_SECURITY_PROTOCOL_CODE,
X_TRANSMIT_PROTOCOL_CODE,
X_PAYMENT_FORMAT_CODE,
X_POSITIVE_PAY_FORMAT_CODE,
X_PAY_FILE_LETTER_FORMAT_CODE,
X_PRINT_INSTRUCTION_IMMED_FLAG,
X_PERIODIC_SEQUENCE_NAME_1,
X_PERIODIC_SEQUENCE_NAME_2,
X_PERIODIC_SEQUENCE_NAME_3,
X_PROCESSING_TYPE,
X_MARK_COMPLETE_EVENT,
X_MANUAL_MARK_COMPLETE_FLAG,
X_DECLARATION_OPTION,
X_SYSTEM_PROFILE_NAME,
X_SYSTEM_PROFILE_DESCRIPTION,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_ELECTRONIC_PROCESSING_CHNNL,
X_LOGICAL_GROUPING_MODE,
X_BATCH_BOOKING_FLAG,
X_INST_GROUP_BY_PAYMENT_METHOD,
X_INACTIVE_DATE);
INSERT_ROW (
row_id,
X_SYSTEM_PROFILE_CODE,
X_REMIT_PMT_DETAILS_LENGTH_LIM,
X_REMIT_REMITTANCE_ADVICE_FORM,
X_REMIT_SRA_OVERRIDE_PAYEE_FLA,
X_REMIT_ADVICE_DELIVERY_METHOD,
X_REMIT_AUTOMATIC_SRA_SUBMIT,
X_OBJECT_VERSION_NUMBER,
X_PMT_PAYMENT_DETAILS_FORMULA,
X_INST_GROUP_BY_LEGAL_ENTITY,
X_INST_GROUP_BY_BILL_PAYABLE,
X_INST_GROUP_BY_MAX_INSTRUCTIO,
X_INST_GROUP_BY_ORGANIZATION,
X_INST_GROUP_BY_PAYMENT_FUNCTI,
X_INST_GROUP_BY_PAYMENT_REASON,
X_INST_GROUP_BY_RFC,
X_INST_MAX_PAYMENTS_PER_INSTR,
X_INST_MAX_AMOUNT_PER_INSTR_VA,
X_INST_MAX_AMOUNT_PER_INSTR_CU,
X_INST_MAX_AMOUNT_FX_RATE_TYPE,
X_INST_SORT_OPTION_1,
X_INST_SORT_ORDER_1,
X_INST_SORT_OPTION_2,
X_POSITIVE_PAY_DELIVERY_FLAG,
X_TRANSMIT_INSTR_IMMED_FLAG,
X_AUTOMATIC_PI_REG_SUBMIT,
X_PMT_GROUP_BY_DELIVERY_CHANNE,
X_PMT_GROUP_BY_REMITTANCE_MES,
X_PMT_GROUP_BY_MAX_DOCUMENTS,
X_PMT_GROUP_BY_UNIQUE_REMIT_ID,
X_PMT_GROUP_BY_SETTLE_PRIORITY,
X_PMT_GROUP_BY_PAYMENT_REASON,
X_PMT_GROUP_BY_DUE_DATE_FLAG,
X_PMT_MAX_DOCUMENTS_PER_PAYMEN,
X_PMT_PAYMENT_DETAILS_LENGTH_L,
X_PMT_GROUP_BY_PAYMENT_DETAILS,
X_PMT_GROUP_BY_BANK_CHARGE_BEA,
X_INST_SORT_ORDER_2,
X_INST_SORT_OPTION_3,
X_INST_SORT_ORDER_3,
X_REMIT_DOCUMENT_COUNT_LIMIT,
X_REMIT_ALLOW_MULTIPLE_COPY_FL,
X_INST_GROUP_BY_PAYMENT_DATE,
X_INST_GROUP_BY_PAYMENT_CURREN,
X_INST_GROUP_BY_INT_BANK_ACCT,
X_INST_GROUP_BY_MAX_PAYMENTS,
X_INST_GROUP_BY_PAY_SERVICE_RE,
X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
X_DECLARATION_REPORT_FORMAT_CO,
X_DECLARATION_CURR_FX_RATE_TYP,
X_DECLARATION_CURRENCY_CODE,
X_DECLARATION_THRESHOLD_AMOUNT,
X_BANK_INSTRUCTION1_CODE,
X_BANK_INSTRUCTION2_CODE,
X_BANK_INSTRUCTION_DETAILS,
X_PAYMENT_TEXT_MESSAGE1,
X_PAYMENT_TEXT_MESSAGE2,
X_ACK_TRANSMIT_PROTOCOL_CODE,
X_ACK_SECURITY_PROTOCOL_CODE,
X_SEND_TO_FILE_FLAG,
X_PI_REGISTER_FORMAT,
X_BEP_ACCOUNT_ID,
X_APPLICABLE_PMT_METHOD,
X_APPLICABLE_CURRENCY,
X_APPLICABLE_PAYER_ORG,
X_APPLICABLE_INT_BANK_ACCOUNT,
X_BEP_SUFFIX,
X_SECURITY_PROTOCOL_CODE,
X_TRANSMIT_PROTOCOL_CODE,
X_PAYMENT_FORMAT_CODE,
X_POSITIVE_PAY_FORMAT_CODE,
X_PAY_FILE_LETTER_FORMAT_CODE,
X_PRINT_INSTRUCTION_IMMED_FLAG,
X_PERIODIC_SEQUENCE_NAME_1,
X_PERIODIC_SEQUENCE_NAME_2,
X_PERIODIC_SEQUENCE_NAME_3,
X_PROCESSING_TYPE,
X_MARK_COMPLETE_EVENT,
X_MANUAL_MARK_COMPLETE_FLAG,
X_DECLARATION_OPTION,
X_SYSTEM_PROFILE_NAME,
X_SYSTEM_PROFILE_DESCRIPTION,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_ELECTRONIC_PROCESSING_CHNNL,
X_LOGICAL_GROUPING_MODE,
X_BATCH_BOOKING_FLAG,
X_INST_GROUP_BY_PAYMENT_METHOD,
X_INACTIVE_DATE);
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER)
is
begin
update IBY_UPG_PPP_TL set
SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME,
SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
SELECT NVL(MAX(applicable_pmt_prof_id),0)
INTO l_max_app
FROM iby_applicable_pmt_profs app
WHERE applicable_pmt_prof_id < 10000;
SELECT x.profile_option_value
INTO l_jenl_eft_dir_value
FROM ( SELECT o.profile_option_name, v.profile_option_value,
row_number() OVER (partition by o.profile_option_name
order by count(*) desc) order_rank
FROM fnd_profile_options o,
fnd_profile_option_values v
WHERE o.profile_option_name = UPPER('JENL_EFT_DIR')
AND v.application_id = o.application_id
AND v.profile_option_id = o.profile_option_id
GROUP BY o.profile_option_name, v.profile_option_value) x
WHERE x.order_rank = 1;
SELECT x.profile_option_value
INTO l_xxno_signet_file_value
FROM ( SELECT o.profile_option_name, v.profile_option_value,
row_number() OVER (partition by o.profile_option_name
order by count(*) desc) order_rank
FROM fnd_profile_options o,
fnd_profile_option_values v
WHERE o.profile_option_name = UPPER('XXNO_SIGNET_CONFIG_FILE')
AND v.application_id = o.application_id
AND v.profile_option_id = o.profile_option_id
GROUP BY o.profile_option_name, v.profile_option_value) x
WHERE x.order_rank = 1;
SELECT x.profile_option_value
INTO l_ece_out_file_value
FROM ( SELECT o.profile_option_name, v.profile_option_value,
row_number() OVER (partition by o.profile_option_name
order by count(*) desc) order_rank
FROM fnd_profile_options o,
fnd_profile_option_values v
WHERE o.profile_option_name = UPPER('ECE_OUT_FILE_PATH')
AND v.application_id = o.application_id
AND v.profile_option_id = o.profile_option_id
GROUP BY o.profile_option_name, v.profile_option_value) x
WHERE x.order_rank = 1;
SELECT TO_NUMBER(x.profile_option_value)
INTO l_jenl_eft_reporting_limit
FROM ( SELECT o.profile_option_name, v.profile_option_value,
row_number() OVER (partition by o.profile_option_name
order by count(*) desc) order_rank
FROM fnd_profile_options o,
fnd_profile_option_values v
WHERE o.profile_option_name = UPPER('JENL_REPORTING_THRESHOLD')
AND v.application_id = o.application_id
AND v.profile_option_id = o.profile_option_id
GROUP BY o.profile_option_name, v.profile_option_value) x
WHERE x.order_rank = 1;
SELECT x.profile_option_value
INTO l_jenl_pmt_separation
FROM ( SELECT o.profile_option_name, v.profile_option_value,
row_number() OVER (partition by o.profile_option_name
order by count(*) desc) order_rank
FROM fnd_profile_options o,
fnd_profile_option_values v
WHERE o.profile_option_name = UPPER('JENL_PAYMENT_SEPARATION')
AND v.application_id = o.application_id
AND v.profile_option_id = o.profile_option_id
GROUP BY o.profile_option_name, v.profile_option_value) x
WHERE x.order_rank = 1;
INSERT ALL
WHEN create_header = 1 THEN
INTO iby_sys_pmt_profiles_b
(
system_profile_code,
payment_format_code,
print_instruction_immed_flag,
processing_type,
mark_complete_event,
manual_mark_complete_flag,
positive_pay_delivery_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
transmit_instr_immed_flag,
automatic_pi_reg_submit,
send_to_file_flag,
dcl_only_foreign_curr_pmt_flag,
seeded_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
bepid,
security_protocol_code,
transmit_protocol_code,
positive_pay_format_code,
pay_file_letter_format_code,
default_printer,
default_payment_document_id,
periodic_sequence_name_1,
periodic_sequence_name_2,
periodic_sequence_name_3,
declaration_option,
declaration_report_format_code,
declaration_curr_fx_rate_type,
declaration_currency_code,
declaration_threshold_amount,
bank_instruction1_code,
bank_instruction2_code,
bank_instruction_details,
payment_text_message1,
payment_text_message2,
inactive_date,
ack_transmit_protocol_code,
ack_security_protocol_code,
pi_register_format,
outbound_pmt_file_directory,
electronic_processing_channel,
logical_grouping_mode,
batch_booking_flag,
inactive_date
) VALUES
(
check_format_id, -- system_profile_code
payment_format_code,
print_instruction_immed_flag,
processing_type,
mark_complete_event,
manual_mark_complete_flag,
positive_pay_delivery_flag,
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number, -- object_version_number
transmit_instr_immed_flag,
automatic_pi_reg_submit,
send_to_file_flag,
dcl_only_foreign_curr_pmt_flag,
seeded_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
bepid,
security_protocol_code,
transmit_protocol_code,
positive_pay_format_code,
pay_file_letter_format_code,
NULL, -- default_printer
payment_document_id, -- default_payment_document_id
periodic_sequence_name_1,
periodic_sequence_name_2,
periodic_sequence_name_3,
declaration_option,
declaration_report_format_code,
declaration_curr_fx_rate_type,
declaration_currency_code,
declaration_threshold_amount,
bank_instruction1_code,
bank_instruction2_code,
bank_instruction_details,
payment_text_message1,
payment_text_message2,
NULL, -- inactive_date
ack_transmit_protocol_code,
ack_security_protocol_code,
pi_register_format,
outbound_pmt_file_directory,
electronic_processing_channel,
logical_grouping_mode,
batch_booking_flag,
inactive_date
)
WHEN create_bepacct=1 THEN
INTO iby_acct_pmt_profiles_b
(
payment_profile_id,
system_profile_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
bep_account_id,
security_configuration_id,
transmit_configuration_id,
reset_value_1,
last_used_number_1,
reset_request_1,
reset_value_2,
last_used_number_2,
reset_request_2,
reset_value_3,
last_used_number_3,
reset_request_3,
inactive_date,
ack_sec_config_id,
ack_transmit_config_id
) VALUES
(
iby_acct_pmt_profiles_b_s.NEXTVAL,
check_format_id, -- system_profile_code
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number, -- object_version_number
bep_account_id,
NULL, -- security_configuration_id,
NULL, -- transmit_configuration_id,
NULL, -- reset_value_1
NULL, -- last_used_number_1
NULL, -- reset_request_1
NULL, -- reset_value_2
NULL, -- last_used_number_2
NULL, -- reset_request_2
NULL, -- reset_value_3
NULL, -- last_used_number_3
NULL, -- reset_request_3
NULL, -- inactive_date
NULL, -- ack_sec_config_id
NULL -- ack_transmit_config_id
)
-- applicable pmt method
WHEN create_header = 1 THEN
INTO iby_applicable_pmt_profs
(
applicable_pmt_prof_id,
system_profile_code,
applicable_type_code,
seeded_flag,
applicable_value_from,
applicable_value_to,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
) VALUES
(
(row_num*4)+l_max_app,
check_format_id, -- system_profile_code
'PAYMENT_METHOD',
seeded_flag,
NULL,
Decode(iby_format_code, 'IBY_PAY_EFT_FOREIGN_DE', applicable_pmt_method, NVL(check_pmt_method_lookup_code, applicable_pmt_method)),
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number -- object_version_number
)
-- applicable currency
WHEN create_header = 1 THEN
INTO iby_applicable_pmt_profs
(
applicable_pmt_prof_id,
system_profile_code,
applicable_type_code,
seeded_flag,
applicable_value_from,
applicable_value_to,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
) VALUES
(
(row_num*4+1)+l_max_app,
check_format_id, -- system_profile_code
'CURRENCY_CODE',
seeded_flag,
NULL,
DECODE(check_multi_currency_flag,
'Y', NULL,
NVL(check_currency_code, applicable_currency)),
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number -- object_version_number
)
-- applicable organizations
WHEN create_header = 1 THEN
INTO iby_applicable_pmt_profs
(
applicable_pmt_prof_id,
system_profile_code,
applicable_type_code,
seeded_flag,
applicable_value_from,
applicable_value_to,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
) VALUES
(
(row_num*4+2)+l_max_app,
check_format_id, -- system_profile_code
'PAYER_ORG',
seeded_flag,
DECODE(applicable_payer_org, NULL, NULL, 'OPERATING_UNIT'),
applicable_payer_org,
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number -- object_version_number
)
-- applicable internal bank account
WHEN create_intba = 1 THEN
INTO iby_applicable_pmt_profs
(
applicable_pmt_prof_id,
system_profile_code,
applicable_type_code,
seeded_flag,
applicable_value_from,
applicable_value_to,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
) VALUES
(
(row_num*4+3)+l_max_app,
check_format_id, -- system_profile_code
'INTERNAL_BANK_ACCOUNT',
seeded_flag,
NULL,
applicable_int_bank_account,
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number -- object_version_number
)
WHEN create_header = 1 THEN
INTO iby_pmt_creation_rules
(
system_profile_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
group_by_payment_details_flag,
group_by_bank_charge_bearer,
group_by_delivery_channel,
group_by_remittance_message,
group_by_max_documents_flag,
group_by_unique_remit_id_flag,
group_by_settle_priority_flag,
group_by_payment_reason,
group_by_due_date_flag,
max_documents_per_payment,
payment_details_length_limit,
payment_details_formula
) VALUES
(
check_format_id, -- system_profile_code
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number, -- object_version_number
p_grp_by_payment_details_flag,
p_grp_by_bank_charge_bearer,
p_grp_by_delivery_channel,
p_grp_by_remittance_message,
p_grp_by_max_documents_flag,
p_grp_by_unique_remit_id_flag,
p_grp_by_settle_priority_flag,
p_grp_by_payment_reason,
DECODE(ap_program_name,
NULL, p_grp_by_due_date_flag,
check_grp_by_due_date),
p_max_documents_per_payment,
p_payment_details_length_limit,
p_payment_details_formula
)
WHEN create_header = 1 THEN
INTO iby_instr_creation_rules
(
system_profile_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
group_by_payment_date,
group_by_payment_currency,
group_by_internal_bank_account,
group_by_max_payments_flag,
group_by_pay_service_request,
group_by_legal_entity,
group_by_bill_payable,
group_by_organization,
group_by_max_instruction_flag,
group_by_payment_function,
group_by_payment_reason,
group_by_rfc,
max_payments_per_instruction,
max_amount_per_instr_value,
max_amount_per_instr_curr_code,
max_amount_fx_rate_type,
sort_option_1,
sort_order_1,
sort_option_2,
sort_order_2,
sort_option_3,
sort_order_3,
group_by_payment_method
) VALUES
(
check_format_id, -- system_profile_code
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number, -- object_version_number
i_grp_by_payment_date,
i_grp_by_payment_currency,
i_grp_by_internal_bank_account,
i_grp_by_max_payments_flag,
i_grp_by_pay_service_request,
i_grp_by_legal_entity,
i_grp_by_bill_payable,
i_grp_by_organization,
i_grp_by_max_instruction_flag,
i_grp_by_payment_function,
i_grp_by_payment_reason,
i_grp_by_rfc,
i_max_payments_per_instruction,
i_max_amt_per_instr_value,
i_max_amt_per_instr_curr_code,
i_max_amt_fx_rate_type,
i_sort_option_1,
i_sort_order_1,
i_sort_option_2,
i_sort_order_2,
i_sort_option_3,
i_sort_order_3,
i_grp_by_payment_method
)
WHEN create_header = 1 THEN
INTO iby_remit_advice_setup
(
system_profile_code,
document_count_limit,
allow_multiple_copy_flag,
seeded_flag,
payment_details_length_limit,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
remittance_advice_format_code,
sra_override_payee_flag,
remit_advice_delivery_method,
automatic_sra_submit_flag
) VALUES
(
check_format_id, -- system_profile_code
document_count_limit,
allow_multiple_copy_flag,
seeded_flag,
payment_details_length_limit,
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number, -- object_version_number
remit_advice_format_code,
sra_override_payee_flag,
remit_advice_delivery_method,
automatic_sra_submit_flag
)
SELECT
i.check_format_id,
i.invoices_per_stub,
i.check_pmt_method_lookup_code,
i.check_currency_code,
i.check_multi_currency_flag,
i.check_zero_amounts_only,
i.check_print_check_stub,
i.check_grp_by_due_date,
i.bank_file_character_set,
i.transmission_flag,
i.ap_program_name,
i.friendly_name,
i.iby_format_code,
i.attribute_category,
i.attribute1,
i.attribute2,
i.attribute3,
i.attribute4,
i.attribute5,
i.attribute6,
i.attribute7,
i.attribute8,
i.attribute9,
i.attribute10,
i.attribute11,
i.attribute12,
i.attribute13,
i.attribute14,
i.attribute15,
i.creation_date,
i.created_by,
i.last_update_date,
i.last_updated_by,
i.last_update_login,
1 object_version_number,
i.payment_document_id,
i.seeded_flag,
i.system_profile_code,
i.payment_format_code,
i.print_instruction_immed_flag,
i.processing_type,
i.mark_complete_event,
i.manual_mark_complete_flag,
i.positive_pay_delivery_flag,
i.transmit_instr_immed_flag,
i.automatic_pi_reg_submit,
i.send_to_file_flag,
i.dcl_only_foreign_curr_pmt_flag,
i.bep_suffix,
i.bepid bepid,
i.security_protocol_code,
i.transmit_protocol_code,
i.positive_pay_format_code,
i.pay_file_letter_format_code,
i.periodic_sequence_name_1,
i.periodic_sequence_name_2,
i.periodic_sequence_name_3,
i.declaration_option,
i.declaration_report_format_code,
i.declaration_curr_fx_rate_type,
i.declaration_currency_code,
i.declaration_threshold_amount,
i.bank_instruction1_code,
i.bank_instruction2_code,
i.bank_instruction_details,
i.payment_text_message1,
i.payment_text_message2,
i.ack_transmit_protocol_code,
i.ack_security_protocol_code,
i.pi_register_format,
i.bep_account_id,
i.applicable_pmt_method,
i.applicable_currency,
i.applicable_payer_org,
i.applicable_int_bank_account,
i.p_grp_by_payment_details_flag,
i.p_grp_by_bank_charge_bearer,
i.p_grp_by_delivery_channel,
i.p_grp_by_remittance_message,
i.p_grp_by_max_documents_flag,
i.p_grp_by_unique_remit_id_flag,
i.p_grp_by_settle_priority_flag,
i.p_grp_by_payment_reason,
i.p_grp_by_due_date_flag,
i.p_max_documents_per_payment,
i.p_payment_details_length_limit,
i.p_payment_details_formula,
i.i_grp_by_payment_date,
i.i_grp_by_payment_currency,
i.i_grp_by_internal_bank_account,
i.i_grp_by_max_payments_flag,
i.i_grp_by_pay_service_request,
i.i_grp_by_legal_entity,
i.i_grp_by_bill_payable,
i.i_grp_by_organization,
i.i_grp_by_max_instruction_flag,
i.i_grp_by_payment_function,
i.i_grp_by_payment_reason,
i.i_grp_by_rfc,
i.i_max_payments_per_instruction,
i.i_max_amt_per_instr_value,
i.i_max_amt_per_instr_curr_code,
i.i_max_amt_fx_rate_type,
i.i_sort_option_1,
i.i_sort_order_1,
i.i_sort_option_2,
i.i_sort_order_2,
i.i_sort_option_3,
i.i_sort_order_3,
i.i_grp_by_payment_method,
i.document_count_limit,
i.allow_multiple_copy_flag,
i.payment_details_length_limit,
i.sra_override_payee_flag,
i.remit_advice_delivery_method,
i.automatic_sra_submit_flag,
i.remit_advice_format_code,
i.create_header,
i.create_bepacct,
i.create_intba,
(CASE
WHEN i.ap_program_name IN ('JENLFDOM','JENLFFGN','JENLPPSX') THEN
l_jenl_eft_dir_value
WHEN i.ap_program_name IN ('JENOPBDR','JENOPDDG','JENOPTGN') THEN
NVL(i.outbound_pmt_file_directory, l_xxno_signet_file_value)
WHEN i.ap_program_name IN ('JEATPSRA','JEATREFD','JEATIEFT','JEATPPF1',
'JEATPPF2','JEATPPF3','JEATPPF4','JEATPPF5',
'JEDKEIGO','JEDKEUGO','JEDKEUNI') THEN
l_ece_out_file_value
ELSE
i.outbound_pmt_file_directory
END) AS outbound_pmt_file_directory,
i.electronic_processing_channel,
i.logical_grouping_mode,
i.batch_booking_flag,
i.inactive_date,
rownum row_num
FROM
( SELECT
DECODE(sfgdf.org_id,
NULL, ppp.system_profile_code||'_'||to_char(cf.check_format_id),
ppp.system_profile_code||'_'||to_char(cf.check_format_id)||
'_'||sfgdf.org_id) check_format_id,
cf.invoices_per_stub,
cf.payment_method_lookup_code check_pmt_method_lookup_code,
cf.currency_code check_currency_code,
NVL(cf.multi_currency_flag, 'N') check_multi_currency_flag,
NVL(cf.zero_amounts_only, 'N') check_zero_amounts_only,
NVL(cf.print_check_stub, 'N') check_print_check_stub,
NVL(cf.group_by_due_date, 'N') check_grp_by_due_date,
cf.bank_file_character_set, -- used for Japanese payment programs as per etrm
NVL(NVL(cf.transmissions_flag, pp.transmissions_enabled), 'N') transmission_flag,
pp.program_name ap_program_name,
pp.friendly_name,
ipf.format_code iby_format_code,
cf.attribute_category,
cf.attribute1,
cf.attribute2,
cf.attribute3,
cf.attribute4,
cf.attribute5,
cf.attribute6,
cf.attribute7,
cf.attribute8,
cf.attribute9,
cf.attribute10,
cf.attribute11,
cf.attribute12,
cf.attribute13,
cf.attribute14,
cf.attribute15,
NVL(cf.creation_date, cf.last_update_date) creation_date,
NVL(cf.created_by, cf.last_updated_by) created_by,
cf.last_update_date,
cf.last_updated_by,
cf.last_update_login,
(CASE
WHEN ppp.processing_type = 'PRINTED' THEN
(SELECT cs.check_stock_id
FROM ap_check_stocks_all cs
WHERE (cs.inactive_date IS NULL OR
TRUNC(cs.inactive_date) >= TRUNC(sysdate))
AND cs.check_format_id = cf.check_format_id
AND ROWNUM=1)
END) AS payment_document_id,
DECODE(cf.created_by,
1, DECODE(cf.last_updated_by, 1, 'Y', 'N'),
'N') seeded_flag,
ppp.inactive_date,
ppp.system_profile_code system_profile_code,
ppp.payment_format_code payment_format_code,
ppp.print_instruction_immed_flag print_instruction_immed_flag,
ppp.processing_type processing_type,
ppp.mark_complete_event mark_complete_event,
ppp.manual_mark_complete_flag manual_mark_complete_flag,
ppp.positive_pay_delivery_flag positive_pay_delivery_flag,
ppp.transmit_instr_immed_flag transmit_instr_immed_flag,
ppp.automatic_pi_reg_submit automatic_pi_reg_submit,
ppp.send_to_file_flag send_to_file_flag,
ppp.dcl_only_foreign_curr_pmt_flag dcl_only_foreign_curr_pmt_flag,
ppp.bep_suffix,
bep.bepid bepid,
ppp.security_protocol_code security_protocol_code,
ppp.transmit_protocol_code transmit_protocol_code,
ppp.positive_pay_format_code positive_pay_format_code,
ppp.pay_file_letter_format_code pay_file_letter_format_code,
ppp.periodic_sequence_name_1 periodic_sequence_name_1,
ppp.periodic_sequence_name_2 periodic_sequence_name_2,
ppp.periodic_sequence_name_3 periodic_sequence_name_3,
NVL(sfgdf.declaration_option, ppp.declaration_option) declaration_option,
ppp.declaration_report_format_code declaration_report_format_code,
NVL(sfgdf.declaration_curr_fx_rate_type,
ppp.declaration_curr_fx_rate_type) declaration_curr_fx_rate_type,
NVL(sfgdf.declaration_currency_code,
ppp.declaration_currency_code) declaration_currency_code,
NVL(TO_NUMBER(sfgdf.declaration_threshold_amount),
ppp.declaration_threshold_amount) declaration_threshold_amount,
(CASE
WHEN gdf.jgzz_attribute_category = 'FI.LMP' THEN
(SELECT bi.bank_instruction_code
FROM iby_bank_instructions_vl bi
WHERE bi.format_value = gdf.jgzz_format_info6
AND bi.territory_code = 'FI')
ELSE
ppp.bank_instruction1_code
END) AS bank_instruction1_code,
ppp.bank_instruction2_code bank_instruction2_code,
ppp.bank_instruction_details bank_instruction_details,
NVL(sfgdf.payment_text_message1, ppp.payment_text_message1) payment_text_message1,
NVL(sfgdf.payment_text_message2, ppp.payment_text_message2) payment_text_message2,
ppp.ack_transmit_protocol_code ack_transmit_protocol_code,
ppp.ack_security_protocol_code ack_security_protocol_code,
ppp.pi_register_format pi_register_format,
NVL(key.bep_account_id, ppp.bep_account_id) bep_account_id,
ppp.applicable_pmt_method,
ppp.applicable_currency,
NVL(TO_CHAR(sfgdf.org_id), ppp.applicable_payer_org) applicable_payer_org,
NVL(TO_CHAR(cba.ce_bank_account_id), ppp.applicable_int_bank_account) applicable_int_bank_account,
(CASE
WHEN (ipf.reference_format_code = 'JENLFDOM' OR
ipf.reference_format_code = 'JENLFFGN') AND
NVL(l_jenl_pmt_separation, 'N') = 'N' THEN 'N'
ELSE ppp.pmt_group_by_payment_details
END) AS p_grp_by_payment_details_flag,
ppp.pmt_group_by_bank_charge_bear p_grp_by_bank_charge_bearer,
ppp.pmt_group_by_delivery_channel p_grp_by_delivery_channel,
ppp.pmt_group_by_remittance_mes p_grp_by_remittance_message,
ppp.pmt_group_by_max_documents p_grp_by_max_documents_flag,
ppp.pmt_group_by_unique_remit_id p_grp_by_unique_remit_id_flag,
ppp.pmt_group_by_settle_priority p_grp_by_settle_priority_flag,
ppp.pmt_group_by_payment_reason p_grp_by_payment_reason,
ppp.pmt_group_by_due_date_flag p_grp_by_due_date_flag,
ppp.pmt_max_documents_per_payment p_max_documents_per_payment,
(CASE
WHEN (ipf.reference_format_code = 'JENLFDOM' OR
ipf.reference_format_code = 'JENLFFGN') AND
NVL(l_jenl_pmt_separation, 'N') = 'N' THEN NULL
ELSE ppp.pmt_payment_details_length_lim
END) AS p_payment_details_length_limit,
(CASE
WHEN (ipf.reference_format_code = 'JENLFDOM' OR
ipf.reference_format_code = 'JENLFFGN') AND
NVL(l_jenl_pmt_separation, 'N') = 'N' THEN NULL
ELSE ppp.pmt_payment_details_formula
END) AS p_payment_details_formula,
ppp.inst_group_by_payment_date i_grp_by_payment_date,
ppp.inst_group_by_payment_currency i_grp_by_payment_currency,
ppp.inst_group_by_int_bank_acct i_grp_by_internal_bank_account,
ppp.inst_group_by_max_payments i_grp_by_max_payments_flag,
ppp.inst_group_by_pay_service_req i_grp_by_pay_service_request,
ppp.inst_group_by_legal_entity i_grp_by_legal_entity,
ppp.inst_group_by_bill_payable i_grp_by_bill_payable,
ppp.inst_group_by_organization i_grp_by_organization,
ppp.inst_group_by_max_instruction i_grp_by_max_instruction_flag,
ppp.inst_group_by_payment_function i_grp_by_payment_function,
ppp.inst_group_by_payment_reason i_grp_by_payment_reason,
ppp.inst_group_by_rfc i_grp_by_rfc,
ppp.inst_max_payments_per_instr i_max_payments_per_instruction,
ppp.inst_max_amount_per_instr_val i_max_amt_per_instr_value,
ppp.inst_max_amount_per_instr_curr i_max_amt_per_instr_curr_code,
ppp.inst_max_amount_fx_rate_type i_max_amt_fx_rate_type,
ppp.inst_sort_option_1 i_sort_option_1,
ppp.inst_sort_order_1 i_sort_order_1,
ppp.inst_sort_option_2 i_sort_option_2,
ppp.inst_sort_order_2 i_sort_order_2,
ppp.inst_sort_option_3 i_sort_option_3,
ppp.inst_sort_order_3 i_sort_order_3,
ppp.inst_group_by_payment_method i_grp_by_payment_method,
ppp.remit_document_count_limit document_count_limit,
ppp.remit_allow_multiple_copy_flag allow_multiple_copy_flag,
ppp.remit_pmt_details_length_lim payment_details_length_limit,
ppp.remit_sra_override_payee_flag sra_override_payee_flag,
ppp.remit_advice_delivery_method remit_advice_delivery_method,
ppp.remit_automatic_sra_submit automatic_sra_submit_flag,
DECODE(NVL(cf.separate_remittance_advice, 'N'),
'Y', NVL(ppp.remit_remittance_advice_format,
l_remit_advice_format_code),
NULL) remit_advice_format_code,
sfgdf.outbound_pmt_file_directory,
ppp.electronic_processing_channel,
ppp.logical_grouping_mode,
ppp.batch_booking_flag,
rank() over (partition by cf.check_format_id, sfgdf.org_id
order by key.bep_account_id,cs.bank_account_id,cs.check_stock_id) create_header,
rank() over (partition by cf.check_format_id, sfgdf.org_id, key.bep_account_id
order by cs.check_stock_id) create_bepacct,
rank() over (partition by cf.check_format_id, sfgdf.org_id, cs.bank_account_id
order by cs.check_stock_id, key.bep_account_id) create_intba
FROM
ap_check_formats cf,
ap_check_stocks_all cs,
ce_upg_bank_accounts cba,
ap_payment_programs pp,
jg_zz_pay_format_info gdf,
(SELECT (CASE
WHEN sfa.jgzz_attribute_category = 'SE.JESEPBAI' THEN
sfa.jgzz_system_info5
END) AS payment_text_message1,
(CASE
WHEN sfa.jgzz_attribute_category = 'SE.JESEPBAI' THEN
sfa.jgzz_system_info3
END) AS payment_text_message2,
(CASE
WHEN sfa.jgzz_attribute_category = 'NO.JENOPTGN' THEN
sfa.jgzz_system_info20
WHEN sfa.jgzz_attribute_category = 'SE.JESEPBAI' THEN
sfa.jgzz_system_info10
WHEN sfa.jgzz_attribute_category = 'SE.JESEPBSI' THEN
sfa.jgzz_system_info8
WHEN sfa.jgzz_attribute_category = 'SE.JESEPBUT' THEN
sfa.jgzz_system_info8
WHEN sfa.jgzz_attribute_category = 'SE.JESEPPOI' THEN
sfa.jgzz_system_info11
WHEN sfa.jgzz_attribute_category = 'SE.JESEPPOU' THEN
sfa.jgzz_system_info10
END) AS outbound_pmt_file_directory,
(CASE
WHEN sfa.jgzz_attribute_category = 'DE.JEDEDEFI' THEN
DECODE(sfa.jgzz_system_info3,
'Y', 'DECLARE_THROUGH_BANK',
'N', 'NO_DECLARATIONS',
sfa.jgzz_system_info3)
WHEN sfa.jgzz_attribute_category = 'NL.JENLFFGN' THEN
DECODE(l_jenl_eft_reporting_limit, NULL, 'NO_DECLARATIONS',
'DECLARE_THROUGH_BANK')
END) AS declaration_option,
(CASE
WHEN sfa.jgzz_attribute_category = 'DE.JEDEDEFI' THEN
TO_NUMBER(sfa.jgzz_system_info5)
WHEN sfa.jgzz_attribute_category = 'NL.JENLFFGN' THEN
l_jenl_eft_reporting_limit
END) AS declaration_threshold_amount,
(CASE
WHEN sfa.jgzz_attribute_category = 'DE.JEDEDEFI' AND
sfa.jgzz_system_info5 IS NOT NULL THEN 'EUR'
WHEN sfa.jgzz_attribute_category = 'NL.JENLFFGN' AND
l_jenl_eft_reporting_limit IS NOT NULL THEN
'NLG'
END) AS declaration_currency_code,
(CASE
WHEN sfa.jgzz_attribute_category = 'NL.JENLFFGN' THEN
sfa.jgzz_system_info13
WHEN sfa.jgzz_attribute_category = 'DE.JEDEDEFI' THEN
DECODE(sfa.jgzz_system_info3,
'Y', 'Corporate')
END) AS declaration_curr_fx_rate_type,
sfa.jgzz_attribute_category,
SUBSTR(sfa.jgzz_attribute_category,
LENGTH(sfa.jgzz_country_code)+2,
LENGTH(sfa.jgzz_attribute_category)) format_code,
sfa.org_id
FROM jg_zz_sys_formats_all_b sfa) sfgdf,
iby_formats_b ipf,
iby_upg_ppp_b ppp,
iby_bepinfo bep,
iby_bepkeys key
WHERE cf.format_payments_program_id = pp.program_id
AND cf.check_format_id = cs.check_format_id(+)
AND cs.bank_account_id = cba.source_pk_id(+)
AND cba.source_application_id(+) = 200
AND cf.check_format_id = gdf.check_format_id(+)
AND pp.program_name = ipf.reference_format_code
AND ipf.format_type_code = 'OUTBOUND_PAYMENT_INSTRUCTION'
AND pp.program_name = sfgdf.format_code(+)
AND ppp.payment_format_code = ipf.format_code
AND ppp.bep_suffix = bep.suffix(+)
AND bep.bepid = key.bepid(+)
UNION ALL
SELECT
ppp.system_profile_code check_format_id,
NULL invoices_per_stub,
NULL check_pmt_method_lookup_code,
NULL check_currency_code,
NULL check_multi_currency_flag,
NULL check_zero_amounts_only,
NULL check_print_check_stub,
NULL check_grp_by_due_date,
NULL bank_file_character_set,
NULL transmission_flag,
NULL ap_program_name,
NULL friendly_name,
ipf.format_code iby_format_code,
NULL attribute_category,
NULL attribute1,
NULL attribute2,
NULL attribute3,
NULL attribute4,
NULL attribute5,
NULL attribute6,
NULL attribute7,
NULL attribute8,
NULL attribute9,
NULL attribute10,
NULL attribute11,
NULL attribute12,
NULL attribute13,
NULL attribute14,
NULL attribute15,
ppp.creation_date,
ppp.created_by,
ppp.last_update_date,
ppp.last_updated_by,
ppp.last_update_login,
NULL payment_document_id,
'Y' seeded_flag,
ppp.inactive_date,
ppp.system_profile_code system_profile_code,
ppp.payment_format_code payment_format_code,
ppp.print_instruction_immed_flag print_instruction_immed_flag,
ppp.processing_type processing_type,
ppp.mark_complete_event mark_complete_event,
ppp.manual_mark_complete_flag manual_mark_complete_flag,
ppp.positive_pay_delivery_flag positive_pay_delivery_flag,
ppp.transmit_instr_immed_flag transmit_instr_immed_flag,
ppp.automatic_pi_reg_submit automatic_pi_reg_submit,
ppp.send_to_file_flag send_to_file_flag,
ppp.dcl_only_foreign_curr_pmt_flag dcl_only_foreign_curr_pmt_flag,
ppp.bep_suffix,
bep.bepid bepid,
ppp.security_protocol_code security_protocol_code,
ppp.transmit_protocol_code transmit_protocol_code,
ppp.positive_pay_format_code positive_pay_format_code,
ppp.pay_file_letter_format_code pay_file_letter_format_code,
ppp.periodic_sequence_name_1 periodic_sequence_name_1,
ppp.periodic_sequence_name_2 periodic_sequence_name_2,
ppp.periodic_sequence_name_3 periodic_sequence_name_3,
ppp.declaration_option declaration_option,
ppp.declaration_report_format_code declaration_report_format_code,
ppp.declaration_curr_fx_rate_type declaration_curr_fx_rate_type,
ppp.declaration_currency_code declaration_currency_code,
ppp.declaration_threshold_amount declaration_threshold_amount,
ppp.bank_instruction1_code bank_instruction1_code,
ppp.bank_instruction2_code bank_instruction2_code,
ppp.bank_instruction_details bank_instruction_details,
ppp.payment_text_message1 payment_text_message1,
ppp.payment_text_message2 payment_text_message2,
ppp.ack_transmit_protocol_code ack_transmit_protocol_code,
ppp.ack_security_protocol_code ack_security_protocol_code,
ppp.pi_register_format pi_register_format,
NVL(key.bep_account_id, ppp.bep_account_id) bep_account_id,
ppp.applicable_pmt_method,
ppp.applicable_currency,
ppp.applicable_payer_org,
ppp.applicable_int_bank_account,
ppp.pmt_group_by_payment_details p_grp_by_payment_details_flag,
ppp.pmt_group_by_bank_charge_bear p_grp_by_bank_charge_bearer,
ppp.pmt_group_by_delivery_channel p_grp_by_delivery_channel,
ppp.pmt_group_by_remittance_mes p_grp_by_remittance_message,
ppp.pmt_group_by_max_documents p_grp_by_max_documents_flag,
ppp.pmt_group_by_unique_remit_id p_grp_by_unique_remit_id_flag,
ppp.pmt_group_by_settle_priority p_grp_by_settle_priority_flag,
ppp.pmt_group_by_payment_reason p_grp_by_payment_reason,
ppp.pmt_group_by_due_date_flag p_grp_by_due_date_flag,
ppp.pmt_max_documents_per_payment p_max_documents_per_payment,
ppp.pmt_payment_details_length_lim p_payment_details_length_limit,
ppp.pmt_payment_details_formula p_payment_details_formula,
ppp.inst_group_by_payment_date i_grp_by_payment_date,
ppp.inst_group_by_payment_currency i_grp_by_payment_currency,
ppp.inst_group_by_int_bank_acct i_grp_by_internal_bank_account,
ppp.inst_group_by_max_payments i_grp_by_max_payments_flag,
ppp.inst_group_by_pay_service_req i_grp_by_pay_service_request,
ppp.inst_group_by_legal_entity i_grp_by_legal_entity,
ppp.inst_group_by_bill_payable i_grp_by_bill_payable,
ppp.inst_group_by_organization i_grp_by_organization,
ppp.inst_group_by_max_instruction i_grp_by_max_instruction_flag,
ppp.inst_group_by_payment_function i_grp_by_payment_function,
ppp.inst_group_by_payment_reason i_grp_by_payment_reason,
ppp.inst_group_by_rfc i_grp_by_rfc,
ppp.inst_max_payments_per_instr i_max_payments_per_instruction,
ppp.inst_max_amount_per_instr_val i_max_amt_per_instr_value,
ppp.inst_max_amount_per_instr_curr i_max_amt_per_instr_curr_code,
ppp.inst_max_amount_fx_rate_type i_max_amt_fx_rate_type,
ppp.inst_sort_option_1 i_sort_option_1,
ppp.inst_sort_order_1 i_sort_order_1,
ppp.inst_sort_option_2 i_sort_option_2,
ppp.inst_sort_order_2 i_sort_order_2,
ppp.inst_sort_option_3 i_sort_option_3,
ppp.inst_sort_order_3 i_sort_order_3,
ppp.inst_group_by_payment_method i_grp_by_payment_method,
ppp.remit_document_count_limit document_count_limit,
ppp.remit_allow_multiple_copy_flag allow_multiple_copy_flag,
ppp.remit_pmt_details_length_lim payment_details_length_limit,
ppp.remit_sra_override_payee_flag sra_override_payee_flag,
ppp.remit_advice_delivery_method remit_advice_delivery_method,
ppp.remit_automatic_sra_submit automatic_sra_submit_flag,
ppp.remit_remittance_advice_format remit_advice_format_code,
null outbound_pmt_file_directory,
ppp.electronic_processing_channel,
ppp.logical_grouping_mode,
ppp.batch_booking_flag,
rank() over (partition by ppp.system_profile_code
order by key.bep_account_id) create_header,
1 create_bepacct,
rank() over (partition by ppp.system_profile_code
order by key.bep_account_id) create_intba
FROM
iby_formats_b ipf,
iby_upg_ppp_b ppp,
iby_bepinfo bep,
iby_bepkeys key
WHERE (ipf.reference_format_code IS NULL OR
(ipf.reference_format_code IS NOT NULL AND
ipf.reference_format_code NOT IN (SELECT pp.program_name
FROM ap_check_formats cf,
ap_payment_programs pp
WHERE cf.format_payments_program_id = pp.program_id))
)
AND ppp.payment_format_code = ipf.format_code
AND ipf.format_type_code = 'OUTBOUND_PAYMENT_INSTRUCTION'
AND ppp.bep_suffix = bep.suffix(+)
AND bep.bepid = key.bepid(+)) i
WHERE i.check_format_id NOT IN (SELECT spp.system_profile_code
FROM iby_sys_pmt_profiles_b spp);
INSERT ALL
WHEN create_header = 1 THEN
INTO iby_sys_pmt_profiles_tl
(
system_profile_code,
system_profile_name,
language,
source_lang,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
system_profile_description
) VALUES
(
system_profile_code, -- system_profile_code
system_profile_name,
language_code,
'US',
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number, -- object_version_number
system_profile_description
)
WHEN 1 = 1 THEN
INTO iby_acct_pmt_profiles_tl
(
payment_profile_id,
payment_profile_name,
language,
source_lang,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
) VALUES
(
payment_profile_id,
payment_profile_name,
language_code,
'US',
created_by, -- created_by
creation_date, -- creation_date
last_updated_by, -- last_updated_by
last_update_date, -- last_update_date
last_update_login, -- last_update_login
object_version_number -- object_version_number
)
SELECT
i.system_profile_code,
i.system_profile_name,
i.system_profile_description,
SUBSTRB(i.system_profile_name||' '||i.bep_name||' '||i.key, 1, 100) payment_profile_name,
i.language_code,
i.bep_name,
i.payment_profile_id,
i.key,
i.creation_date,
i.created_by,
i.last_update_date,
i.last_updated_by,
i.last_update_login,
1 object_version_number,
i.create_header
FROM (
SELECT
DECODE(sfgdf.org_id,
NULL, ppp.system_profile_code||'_'||to_char(cf.check_format_id),
ppp.system_profile_code||'_'||to_char(cf.check_format_id)||
'_'||sfgdf.org_id) system_profile_code,
/* Modified the script to change the names of the PPPs which are
created with the formats (Stub After Payment). Hard coded the
format codes as these are the only formats which are duplicated
for providing the flexiblity to the customers
Bug Number: 7532799*/
DECODE(sfgdf.org_id,
NULL, cf.name|| Decode(ppb.system_profile_code, 'IBY_PAY_EFT_German_General', ' General Payments',
'IBY_PAY_EFT_German_EU_Standard', ' EU Standard Payments',
'IBY_PAY_EFT_German_EUE', ' EUE Payments',
'')
|| Decode(ipf.format_code, 'IBY_PAY_CHK_DE_A', '(Stub after Payment)',
'IBY_PAY_CHK_FORM_FEED_1A', '(Stub after Payment)',
'IBY_PAY_CHK_FORM_FEED_2A', '(Stub after Payment)',
'IBY_PAY_CHK_FR_A', '(Stub after Payment)',
'IBY_PAY_CHK_LASER_A', '(Stub after Payment)',
'IBY_PAY_CHK_STANDARD_1A', '(Stub after Payment)',
'IBY_PAY_CHK_STANDARD_2A', '(Stub after Payment)',
''),
cf.name|| Decode(ppb.system_profile_code, 'IBY_PAY_EFT_German_General', ' General Payments',
'IBY_PAY_EFT_German_EU_Standard', ' EU Standard Payments',
'IBY_PAY_EFT_German_EUE', ' EUE Payments',
'')
||' - '||ou.name
|| Decode(ipf.format_code, 'IBY_PAY_CHK_DE_A', '(Stub after Payment)',
'IBY_PAY_CHK_FORM_FEED_1A', '(Stub after Payment)',
'IBY_PAY_CHK_FORM_FEED_2A', '(Stub after Payment)',
'IBY_PAY_CHK_FR_A', '(Stub after Payment)',
'IBY_PAY_CHK_LASER_A', '(Stub after Payment)',
'IBY_PAY_CHK_STANDARD_1A', '(Stub after Payment)',
'IBY_PAY_CHK_STANDARD_2A', '(Stub after Payment)',
'')
) system_profile_name,
pp.friendly_name system_profile_description,
ppp.language language_code,
bep.name bep_name,
app.payment_profile_id,
key.key,
NVL(cf.creation_date, cf.last_update_date) creation_date,
NVL(cf.created_by, cf.last_updated_by) created_by,
cf.last_update_date,
cf.last_updated_by,
cf.last_update_login,
rank() over (partition by app.system_profile_code
order by app.payment_profile_id) create_header
FROM
ap_check_formats cf,
ap_payment_programs pp,
iby_formats_b ipf,
iby_upg_ppp_tl ppp,
iby_upg_ppp_b ppb,
iby_acct_pmt_profiles_b app,
iby_bepinfo bep,
iby_bepkeys key,
(SELECT SUBSTR(sfa.jgzz_attribute_category,
LENGTH(sfa.jgzz_country_code)+2,
LENGTH(sfa.jgzz_attribute_category)) format_code,
sfa.org_id
FROM jg_zz_sys_formats_all_b sfa) sfgdf,
hr_organization_units ou
WHERE cf.format_payments_program_id = pp.program_id
AND pp.program_name = ipf.reference_format_code
AND ipf.format_type_code = 'OUTBOUND_PAYMENT_INSTRUCTION'
AND pp.program_name = sfgdf.format_code(+)
AND sfgdf.org_id = ou.organization_id(+)
AND DECODE(sfgdf.org_id,
NULL, ppp.system_profile_code||'_'||to_char(cf.check_format_id),
ppp.system_profile_code||'_'||to_char(cf.check_format_id)||
'_'||sfgdf.org_id) = app.system_profile_code
AND ppb.payment_format_code = ipf.format_code
AND ppp.system_profile_code = ppb.system_profile_code
AND ppb.bep_suffix = bep.suffix(+)
AND app.bep_account_id = key.bep_account_id(+)
UNION ALL
SELECT
ppp.system_profile_code system_profile_code,
ppp.system_profile_name system_profile_name,
ppp.system_profile_description system_profile_description,
ppp.language language_code,
bep.name bep_name,
app.payment_profile_id,
key.key,
ppp.creation_date,
ppp.created_by,
ppp.last_update_date,
ppp.last_updated_by,
ppp.last_update_login,
rank() over (partition by app.system_profile_code
order by app.payment_profile_id) create_header
FROM
iby_formats_b ipf,
iby_upg_ppp_tl ppp,
iby_upg_ppp_b ppb,
iby_acct_pmt_profiles_b app,
iby_bepinfo bep,
iby_bepkeys key
WHERE (ipf.reference_format_code IS NULL OR
(ipf.reference_format_code IS NOT NULL AND
ipf.reference_format_code NOT IN (SELECT pp.program_name
FROM ap_check_formats cf,
ap_payment_programs pp
WHERE cf.format_payments_program_id = pp.program_id))
)
AND ppb.payment_format_code = ipf.format_code
AND ipf.format_type_code = 'OUTBOUND_PAYMENT_INSTRUCTION'
AND ppp.system_profile_code = app.system_profile_code
AND ppp.system_profile_code = ppb.system_profile_code
AND ppb.bep_suffix = bep.suffix(+)
AND app.bep_account_id = key.bep_account_id(+)) i
WHERE i.system_profile_code NOT IN (SELECT spp.system_profile_code
FROM iby_sys_pmt_profiles_tl spp);
SELECT NVL(MAX(applicable_pmt_prof_id),0)
INTO l_max_app
FROM iby_applicable_pmt_profs app
WHERE applicable_pmt_prof_id < 10000;
INSERT INTO iby_applicable_pmt_profs
(
applicable_pmt_prof_id,
system_profile_code,
applicable_type_code,
seeded_flag,
applicable_value_from,
applicable_value_to,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
)
SELECT
(rownum)+l_max_app,
apm.system_profile_code,
'PAYMENT_METHOD',
apm.seeded_flag,
NULL,
pm.payment_method_code,
apm.created_by,
apm.creation_date,
apm.last_updated_by,
apm.last_update_date,
apm.last_update_login,
apm.object_version_number
FROM iby_applicable_pmt_profs apm,
iby_sys_pmt_profiles_b ppp,
iby_payment_methods_b pm
WHERE ppp.system_profile_code = apm.system_profile_code
AND ppp.payment_format_code = 'IBY_PAY_ECE'
AND apm.applicable_type_code = 'PAYMENT_METHOD'
AND pm.payment_method_code like 'DK%'
AND NOT EXISTS (SELECT a.applicable_pmt_prof_id
FROM iby_applicable_pmt_profs a
WHERE a.system_profile_code = ppp.system_profile_code
AND a.applicable_type_code = 'PAYMENT_METHOD'
AND a.applicable_value_to = pm.payment_method_code);