IBY_EXT_FD_INS_1_0

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
PAYMENT_INSTRUCTION_ID INSTRUCTION_CREATED_DATE PAYMENT_INSTRUCTION_STATUS INSTRUCTION_STATUS_MEANING PROCESSING_TYPE PAY_ADMIN_ASSIGNED_REF_CODE BANK_ASSIGNED_REF_CODE PERIODIC_SEQUENCE_NAME_1 LAST_USED_NUMBER_1 PERIODIC_SEQUENCE_NAME_2 LAST_USED_NUMBER_2 PERIODIC_SEQUENCE_NAME_3 LAST_USED_NUMBER_3 BANK_INSTRUCTION1_CODE BANK_INSTRUCTION1_MEANING BANK_INSTRUCTION1_FORMAT_VALUE BANK_INSTRUCTION2_CODE BANK_INSTRUCTION2_MEANING BANK_INSTRUCTION2_FORMAT_VALUE BANK_INSTRUCTION_DETAILS PAYMENT_TEXT_MESSAGE1 PAYMENT_TEXT_MESSAGE2 DECLARATION_OPTION BEP_ACCOUNT_NAME BEP_ACCOUNT_ID PAYSYS_ACCT_ATTR_CAT COMMENTS PI_ATTR_CAT PAYMENT_PROFILE_ID SYSTEM_PROFILE_CODE PAYMENT_PROFILE_NAME PAYMENT_PROFILE_ATTR_CAT PAYMENT_FORMAT_CODE PAYMENT_FORMAT_NAME PAYMENT_FORMAT_ATTR_CAT PAYER_INSTRUMENT_COUNT PAYMENT_COUNT PAYMENT_DATE PAYMENT_CURRENCY_CODE PAYMENT_SERVICE_REQUEST_ID ORG_ID ORG_TYPE ORG_TYPE_MEANING ORG_NAME PAYMENT_REASON_CODE PAY_REASON_MEANING PAY_REASON_FORMAT_VALUE PAYMENT_REASON_COMMENTS GROUP_BY_PAYMENT_DATE GROUP_BY_PAYMENT_CURRENCY GROUP_BY_PAY_SERVICE_REQUEST GROUP_BY_LEGAL_ENTITY GROUP_BY_ORGANIZATION GROUP_BY_PAYMENT_REASON LEGAL_ENTITY_ID GROUP_BY_INTERNAL_BANK_ACCOUNT GROUP_BY_PAYMENT_FUNCTION GROUP_BY_RFC INTERNAL_BANK_ACCOUNT_ID PAYMENT_FUNCTION PAYMENT_FUNCTION_MEANING RFC_IDENTIFIER PROCESS_TYPE TOTAL_PMT_AMOUNT MOAC_DATA_BLOCKED_FLAG PAYMENT_DOCUMENT_NAME PAPER_STOCK_TYPE PAPER_STOCK_TYPE_MEANING PROCESSING_TYPE_MEANING BATCH_BOOKING_FLAG GROUP_BY_PAYMENT_DATE_LOGICAL GROUP_BY_BANK_ACCOUNT_LOG GROUP_BY_SETTLEMNT_PRIORITY GROUP_BY_SERVICE_LEVEL GROUP_BY_BANK_CHARGE_BEARER GROUP_BY_ULTIMATE_DEBTOR GROUP_BY_CATEGORY_PURPOSE GROUP_BY_PAYMENT_METHOD CARD_ISSUER_FILE_SEQ_NO CREATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN LAST_UPDATED_BY INBOUND_FILE_ID FILE_NAME PROCESSING_STATUS BEP_STATUS BEP_ERROR_CODE BEP_ERROR_MESSAGE_TEXT ACKNOWLEDGEMENT_DATE PAYMENT_MODE_CODE

Name

PAYMENT_INSTRUCTION_ID

INSTRUCTION_CREATED_DATE

PAYMENT_INSTRUCTION_STATUS

INSTRUCTION_STATUS_MEANING

PROCESSING_TYPE

PAY_ADMIN_ASSIGNED_REF_CODE

BANK_ASSIGNED_REF_CODE

PERIODIC_SEQUENCE_NAME_1

LAST_USED_NUMBER_1

PERIODIC_SEQUENCE_NAME_2

LAST_USED_NUMBER_2

PERIODIC_SEQUENCE_NAME_3

LAST_USED_NUMBER_3

BANK_INSTRUCTION1_CODE

BANK_INSTRUCTION1_MEANING

BANK_INSTRUCTION1_FORMAT_VALUE

BANK_INSTRUCTION2_CODE

BANK_INSTRUCTION2_MEANING

BANK_INSTRUCTION2_FORMAT_VALUE

BANK_INSTRUCTION_DETAILS

PAYMENT_TEXT_MESSAGE1

PAYMENT_TEXT_MESSAGE2

DECLARATION_OPTION

BEP_ACCOUNT_NAME

BEP_ACCOUNT_ID

PAYSYS_ACCT_ATTR_CAT

COMMENTS

PI_ATTR_CAT

PAYMENT_PROFILE_ID

SYSTEM_PROFILE_CODE

PAYMENT_PROFILE_NAME

PAYMENT_PROFILE_ATTR_CAT

PAYMENT_FORMAT_CODE

PAYMENT_FORMAT_NAME

PAYMENT_FORMAT_ATTR_CAT

PAYER_INSTRUMENT_COUNT

PAYMENT_COUNT

PAYMENT_DATE

PAYMENT_CURRENCY_CODE

PAYMENT_SERVICE_REQUEST_ID

ORG_ID

ORG_TYPE

ORG_TYPE_MEANING

ORG_NAME

PAYMENT_REASON_CODE

PAY_REASON_MEANING

PAY_REASON_FORMAT_VALUE

PAYMENT_REASON_COMMENTS

GROUP_BY_PAYMENT_DATE

GROUP_BY_PAYMENT_CURRENCY

GROUP_BY_PAY_SERVICE_REQUEST

GROUP_BY_LEGAL_ENTITY

GROUP_BY_ORGANIZATION

GROUP_BY_PAYMENT_REASON

LEGAL_ENTITY_ID

GROUP_BY_INTERNAL_BANK_ACCOUNT

GROUP_BY_PAYMENT_FUNCTION

GROUP_BY_RFC

INTERNAL_BANK_ACCOUNT_ID

PAYMENT_FUNCTION

PAYMENT_FUNCTION_MEANING

RFC_IDENTIFIER

PROCESS_TYPE

TOTAL_PMT_AMOUNT

MOAC_DATA_BLOCKED_FLAG

PAYMENT_DOCUMENT_NAME

PAPER_STOCK_TYPE

PAPER_STOCK_TYPE_MEANING

PROCESSING_TYPE_MEANING

BATCH_BOOKING_FLAG

GROUP_BY_PAYMENT_DATE_LOGICAL

GROUP_BY_BANK_ACCOUNT_LOG

GROUP_BY_SETTLEMNT_PRIORITY

GROUP_BY_SERVICE_LEVEL

GROUP_BY_BANK_CHARGE_BEARER

GROUP_BY_ULTIMATE_DEBTOR

GROUP_BY_CATEGORY_PURPOSE

GROUP_BY_PAYMENT_METHOD

CARD_ISSUER_FILE_SEQ_NO

CREATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

LAST_UPDATED_BY

INBOUND_FILE_ID

FILE_NAME

PROCESSING_STATUS

BEP_STATUS

BEP_ERROR_CODE

BEP_ERROR_MESSAGE_TEXT

ACKNOWLEDGEMENT_DATE

PAYMENT_MODE_CODE

Query

SQL_Statement
SELECT ins.payment_instruction_id, ins.creation_date instruction_created_date, ins.payment_instruction_status, pi_status_lookup.meaning instruction_status_meaning, pp.processing_type, ins.pay_admin_assigned_ref_code, ins.bank_assigned_ref_code, pp.periodic_sequence_name_1, pp.last_used_number_1, pp.periodic_sequence_name_2, pp.last_used_number_2, pp.periodic_sequence_name_3, pp.last_used_number_3, pp.bank_instruction1_code, bi1.meaning bank_instruction1_meaning, bi1.format_value bank_instruction1_format_value, pp.bank_instruction2_code, bi2.meaning bank_instruction2_meaning, bi2.format_value bank_instruction2_format_value, pp.bank_instruction_details, pp.payment_text_message1, pp.payment_text_message2, pp.declaration_option, paysys_acct.KEY bep_account_name, pp.bep_account_id, paysys_acct.attribute_category paysys_acct_attr_cat, ins.comments, ins.attribute_category pi_attr_cat, pp.payment_profile_id, pp.system_profile_code, pp.payment_profile_name, pp.attribute_category payment_profile_attr_cat, pf.format_code payment_format_code, pf.format_name payment_format_name, pf.attribute_category payment_format_attr_cat, 1 payer_instrument_count, ins.payment_count, ins.payment_date, decode(pi_rules.group_by_payment_currency,'Y',ins.payment_currency_code, iby_fd_extract_gen_pvt.get_ins_currency(ins.payment_instruction_id)) payment_currency_code, ins.payment_service_request_id, ins.org_id, ins.org_type, org_type_lookup.meaning org_type_meaning, mo_goa.organization_name org_name, ins.payment_reason_code, pay_reason.meaning pay_reason_meaning, pay_reason.format_value pay_reason_format_value, ins.payment_reason_comments, pi_rules.group_by_payment_date, pi_rules.group_by_payment_currency, pi_rules.group_by_pay_service_request, pi_rules.group_by_legal_entity, pi_rules.group_by_organization, pi_rules.group_by_payment_reason, ins.legal_entity_id, pi_rules.group_by_internal_bank_account, pi_rules.group_by_payment_function, pi_rules.group_by_rfc, ins.internal_bank_account_id, ins.payment_function, pmt_func_lookup.meaning payment_function_meaning, ins.rfc_identifier, ins.process_type, iby_fd_extract_gen_pvt.get_ins_totalamt(ins.payment_instruction_id) total_pmt_amount, decode(iby_fd_post_picp_progs_pvt.val_instruction_accessible(ins.payment_instruction_id), 'Y', 'N', 'Y') moac_data_blocked_flag , ce_doc.payment_document_name, ce_doc.paper_stock_type, paper_stock_type_lookup.meaning paper_stock_type_meaning, procing_type_lookup.meaning processing_type_meaning, ins.batch_booking_flag, pp_log.group_by_payment_date group_by_payment_date_logical, pp_log.group_by_internal_bank_account group_by_bank_account_log, pp_log.group_by_settlemnt_priority, pp_log.group_by_service_level, pp_log.group_by_bank_charge_bearer, pp_log.group_by_ultimate_debtor, pp_log.group_by_category_purpose, pp_log.group_by_payment_method, ins.card_issuer_file_seq_no, ins.created_by, ins.last_update_date, ins.last_update_login, ins.last_updated_by, inbound_file.inbound_file_id, inbound_file.file_name, inbound_file.processing_status, ins.bep_status, ins.bep_error_code, ins.bep_error_message_text, ins.acknowledgement_date, ins.payment_mode_code FROM iby_pay_instructions_all ins, fnd_lookups pi_status_lookup, iby_payment_profiles pp, fnd_lookups procing_type_lookup, iby_instr_creation_rules pi_rules, iby_pmt_logical_grp_rules pp_log, iby_bepkeys paysys_acct, iby_formats_vl pf, iby_payment_codes_vl bi1, iby_payment_codes_vl bi2, iby_payment_codes_vl pay_reason, fnd_lookups org_type_lookup, fnd_lookups pmt_func_lookup, mo_glob_org_access_tmp mo_goa, ce_payment_documents ce_doc, ce_lookups paper_stock_type_lookup, iby_inbound_file inbound_file WHERE pp.payment_profile_id = ins.payment_profile_id AND pp.system_profile_code = pi_rules.system_profile_code(+) AND pp.system_profile_code = pp_log.system_profile_code(+) AND pp.bep_account_id = paysys_acct.bep_account_id(+) AND pp.payment_format_code = pf.format_code AND pp.processing_type =procing_type_lookup.lookup_code (+) AND procing_type_lookup.lookup_type = 'IBY_PROCESSING_TYPES' AND ins.payment_instruction_status = pi_status_lookup.lookup_code AND pi_status_lookup.lookup_type = 'IBY_PAY_INSTRUCTION_STATUSES' AND ins.payment_reason_code = pay_reason.payment_code(+) AND pay_reason.payment_code_type(+) = 'PAYMENT_REASON' AND pp.bank_instruction1_code = bi1.payment_code(+) AND bi1.payment_code_type(+) = 'BANK_INSTRUCTION' AND pp.bank_instruction2_code = bi2.payment_code(+) AND bi2.payment_code_type(+) = 'BANK_INSTRUCTION' AND ins.org_id = mo_goa.organization_id(+) AND ins.org_type = org_type_lookup.lookup_code(+) AND org_type_lookup.lookup_type(+) = 'IBY_ORGANIZATION_TYPES' AND ins.payment_function = pmt_func_lookup.lookup_code(+) AND pmt_func_lookup.lookup_type(+) = 'IBY_PAYMENT_FUNCTIONS' AND ce_doc.paper_stock_type = paper_stock_type_lookup.lookup_code(+) AND paper_stock_type_lookup.lookup_type(+) = 'CE_PAPER_STOCK_TYPES' AND ins.payment_document_id = ce_doc.payment_document_id(+) AND ins.inbound_file_id = inbound_file.inbound_file_id(+)

SQL_Statement

SELECT ins.payment_instruction_id,

ins.creation_date instruction_created_date,

ins.payment_instruction_status,

pi_status_lookup.meaning instruction_status_meaning,

pp.processing_type,

ins.pay_admin_assigned_ref_code,

ins.bank_assigned_ref_code,

pp.periodic_sequence_name_1,

pp.last_used_number_1,

pp.periodic_sequence_name_2,

pp.last_used_number_2,

pp.periodic_sequence_name_3,

pp.last_used_number_3,

pp.bank_instruction1_code,

bi1.meaning bank_instruction1_meaning,

bi1.format_value bank_instruction1_format_value,

pp.bank_instruction2_code,

bi2.meaning bank_instruction2_meaning,

bi2.format_value bank_instruction2_format_value,

pp.bank_instruction_details,

pp.payment_text_message1,

pp.payment_text_message2,

pp.declaration_option,

paysys_acct.KEY bep_account_name,

pp.bep_account_id,

paysys_acct.attribute_category paysys_acct_attr_cat,

ins.comments,

ins.attribute_category pi_attr_cat,

pp.payment_profile_id,

pp.system_profile_code,

pp.payment_profile_name,

pp.attribute_category payment_profile_attr_cat,

pf.format_code payment_format_code,

pf.format_name payment_format_name,

pf.attribute_category payment_format_attr_cat,

1 payer_instrument_count,

ins.payment_count,

ins.payment_date,

decode(pi_rules.group_by_payment_currency,'Y',ins.payment_currency_code, iby_fd_extract_gen_pvt.get_ins_currency(ins.payment_instruction_id)) payment_currency_code,

ins.payment_service_request_id,

ins.org_id,

ins.org_type,

org_type_lookup.meaning org_type_meaning,

mo_goa.organization_name org_name,

ins.payment_reason_code,

pay_reason.meaning pay_reason_meaning,

pay_reason.format_value pay_reason_format_value,

ins.payment_reason_comments,

pi_rules.group_by_payment_date,

pi_rules.group_by_payment_currency,

pi_rules.group_by_pay_service_request,

pi_rules.group_by_legal_entity,

pi_rules.group_by_organization,

pi_rules.group_by_payment_reason,

ins.legal_entity_id,

pi_rules.group_by_internal_bank_account,

pi_rules.group_by_payment_function,

pi_rules.group_by_rfc,

ins.internal_bank_account_id,

ins.payment_function,

pmt_func_lookup.meaning payment_function_meaning,

ins.rfc_identifier,

ins.process_type,

iby_fd_extract_gen_pvt.get_ins_totalamt(ins.payment_instruction_id) total_pmt_amount, decode(iby_fd_post_picp_progs_pvt.val_instruction_accessible(ins.payment_instruction_id), 'Y', 'N', 'Y') moac_data_blocked_flag ,

ce_doc.payment_document_name,

ce_doc.paper_stock_type,

paper_stock_type_lookup.meaning paper_stock_type_meaning,

procing_type_lookup.meaning processing_type_meaning,

ins.batch_booking_flag,

pp_log.group_by_payment_date group_by_payment_date_logical,

pp_log.group_by_internal_bank_account group_by_bank_account_log,

pp_log.group_by_settlemnt_priority,

pp_log.group_by_service_level,

pp_log.group_by_bank_charge_bearer,

pp_log.group_by_ultimate_debtor,

pp_log.group_by_category_purpose,

pp_log.group_by_payment_method,

ins.card_issuer_file_seq_no,

ins.created_by,

ins.last_update_date,

ins.last_update_login,

ins.last_updated_by,

inbound_file.inbound_file_id,

inbound_file.file_name,

inbound_file.processing_status,

ins.bep_status,

ins.bep_error_code,

ins.bep_error_message_text,

ins.acknowledgement_date,

ins.payment_mode_code

FROM iby_pay_instructions_all ins,

fnd_lookups pi_status_lookup,

iby_payment_profiles pp,

fnd_lookups procing_type_lookup,

iby_instr_creation_rules pi_rules,

iby_pmt_logical_grp_rules pp_log,

iby_bepkeys paysys_acct,

iby_formats_vl pf,

iby_payment_codes_vl bi1,

iby_payment_codes_vl bi2,

iby_payment_codes_vl pay_reason,

fnd_lookups org_type_lookup,

fnd_lookups pmt_func_lookup,

mo_glob_org_access_tmp mo_goa,

ce_payment_documents ce_doc,

ce_lookups paper_stock_type_lookup,

iby_inbound_file inbound_file

WHERE pp.payment_profile_id = ins.payment_profile_id

AND pp.system_profile_code = pi_rules.system_profile_code(+)

AND pp.system_profile_code = pp_log.system_profile_code(+)

AND pp.bep_account_id = paysys_acct.bep_account_id(+)

AND pp.payment_format_code = pf.format_code

AND pp.processing_type =procing_type_lookup.lookup_code (+)

AND procing_type_lookup.lookup_type = 'IBY_PROCESSING_TYPES'

AND ins.payment_instruction_status = pi_status_lookup.lookup_code

AND pi_status_lookup.lookup_type = 'IBY_PAY_INSTRUCTION_STATUSES'

AND ins.payment_reason_code = pay_reason.payment_code(+)

AND pay_reason.payment_code_type(+) = 'PAYMENT_REASON'

AND pp.bank_instruction1_code = bi1.payment_code(+)

AND bi1.payment_code_type(+) = 'BANK_INSTRUCTION'

AND pp.bank_instruction2_code = bi2.payment_code(+)

AND bi2.payment_code_type(+) = 'BANK_INSTRUCTION'

AND ins.org_id = mo_goa.organization_id(+)

AND ins.org_type = org_type_lookup.lookup_code(+)

AND org_type_lookup.lookup_type(+) = 'IBY_ORGANIZATION_TYPES'

AND ins.payment_function = pmt_func_lookup.lookup_code(+)

AND pmt_func_lookup.lookup_type(+) = 'IBY_PAYMENT_FUNCTIONS'

AND ce_doc.paper_stock_type = paper_stock_type_lookup.lookup_code(+)

AND paper_stock_type_lookup.lookup_type(+) = 'CE_PAPER_STOCK_TYPES'

AND ins.payment_document_id = ce_doc.payment_document_id(+)

AND ins.inbound_file_id = inbound_file.inbound_file_id(+)