IBY_EXT_FD_PMT_1_0_VT

Details

• Schema: FUSION

• Object owner: IBY

• Object type: VIEW

Columns

Name

PAYMENT_INSTRUCTION_ID PAYMENT_ID INTERNAL_BANK_ACCOUNT_ID LEGAL_ENTITY_ID CALLING_APP_ID APPLICATION_NAME PAYMENT_SERVICE_REQUEST_ID PAYMENT_FUNCTION PAYMENT_FUNCTION_MEANING PAYMENT_ORG_ID PAYMENT_ORG_TYPE PAYMENT_ORG_TYPE_MEANING PAYMENT_ORG_NAME PAYMENT_DOC_CATEGORY PAYMENT_DOC_SEQUENCE_NAME PAYMENT_DOC_SEQUENCE_VALUE PAYMENT_REFERENCE_NUMBER PAPER_DOCUMENT_NUMBER PAYMENT_DATE PAYMENT_DUE_DATE MATURITY_DATE PAYMENT_STATUS PAYMENT_STATUS_MEANING INV_EXT_PAYEE_ID PAYEE_PARTY_ID PAYEE_PARTY_SITE_USE_ID EXTERNAL_BANK_ACCOUNT_ID PAYMENT_AMOUNT PAYMENT_CURRENCY_CODE PAYMENT_AMOUNT_TEXT PAYMENT_METHOD_CODE PAYMENT_METHOD_NAME PAYMENT_METHOD_ATTR_CAT EXCLUSIVE_PAYMENT_FLAG SETTLEMENT_PRIORITY SETTLEMENT_PRIORITY_MEANING AMOUNT_WITHHELD DISCOUNT_AMOUNT_TAKEN BANK_CHARGE_BEARER BANK_CHARGE_BEARER_MEANING BANK_CHARGE_AMOUNT DELIVERY_CHANNEL_CODE DELIVERY_CHANNEL_MEANING DELIVERY_CHANNEL_FORMAT_VALUE SERVICE_LEVEL_CODE SERVICE_LEVEL_MEANING SERVICE_LEVEL_FORMAT_VALUE 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 PAYMENT_TEXT_MESSAGE3 PAYMENT_DETAILS UNIQUE_REMITTANCE_IDENTIFIER URI_CHECK_DIGIT PAYMENT_REASON_CODE PAYMENT_REASON_MEANING PAYMENT_REASON_FORMAT_VALUE PAYMENT_REASON_COMMENTS REMITTANCE_MESSAGE1 REMITTANCE_MESSAGE2 REMITTANCE_MESSAGE3 DECLARE_PAYMENT_FLAG DECLARATION_AMOUNT DECLARATION_CURRENCY_CODE FEDERAL_ALLOTMENT_CODE FEDERAL_OFFSET_ELIGIBILITY PAYMENT_ATTR_CAT BENEFICIARY_NAME FEDERAL_SPS_TREASURY_SYMBOL EMPLOYEE_PAYMENT_FLAG PAYMENT_METHOD_FORMAT_VALUE LOGICAL_GROUP_REFERENCE TOTAL_PAYMENT_AMOUNT TOTAL_PAYMENT_COUNT PAYMENT_PROFILE_ID POSITIVE_PAY_FILE_CREATED_FLAG INV_SUPPLIER_SITE_ID ANTICIPATED_VALUE_DATE CREATED_BY CREATION_DATE LAST_UPDATE_DATE LAST_UPDATE_LOGIN LAST_UPDATED_BY VOID_DATE BEP_STATUS BEP_ERROR_CODE BEP_ERROR_MESSAGE_TEXT ACKNOWLEDGEMENT_DATE PAYMENT_MODE_CODE PAYMENT_INSTRUMENT_ID

Query

SQL_Statement

SELECT pmt.payment_instruction_id, pmt.payment_id, pmt.internal_bank_account_id, pmt.legal_entity_id, req.calling_app_id, fndapp.application_short_name application_name, pmt.payment_service_request_id, pmt.payment_function, payment_function_lookup.meaning payment_function_meaning, pmt.org_id payment_org_id, pmt.org_type payment_org_type, org_type_lookup.meaning payment_org_type_meaning, mo_goa.organization_name payment_org_name, pmt.document_category_code payment_doc_category, fnd_doc_seq.name payment_doc_sequence_name, pmt.document_sequence_value payment_doc_sequence_value, pmt.payment_reference_number, pmt.paper_document_number, pmt.payment_date, pmt.payment_due_date, pmt.maturity_date, pmt.payment_status, payment_status_lookup.meaning payment_status_meaning, pmt.inv_ext_payee_id, pmt.payee_party_id, pmt.party_site_id payee_party_site_use_id, pmt.external_bank_account_id, pmt.payment_amount, pmt.payment_currency_code, IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(pmt.payment_amount, pmt.payment_currency_code) payment_amount_text, pmt.payment_method_code, payment_methods.payment_method_name, payment_methods.attribute_category payment_method_attr_cat, pmt.exclusive_payment_flag, pmt.settlement_priority, settlement_priority_lookup.meaning settlement_priority_meaning, iby_fd_extract_gen_pvt.get_payment_amount_withheld(pmt.payment_id) amount_withheld, pmt.discount_amount_taken, pmt.bank_charge_bearer, bank_charge_bearer_lookup.meaning bank_charge_bearer_meaning, pmt.bank_charge_amount, pmt.delivery_channel_code, delivery_channels.meaning delivery_channel_meaning, delivery_channels.format_value delivery_channel_format_value, pmt.service_level_code, service_level.meaning service_level_meaning, service_level.format_value service_level_format_value, pmt.bank_instruction1_code, bank_instructions1.meaning bank_instruction1_meaning, bank_instructions1.format_value bank_instruction1_format_value, pmt.bank_instruction2_code, bank_instructions2.meaning bank_instruction2_meaning, bank_instructions2.format_value bank_instruction2_format_value, pmt.bank_instruction_details, pmt.payment_text_message1, pmt.payment_text_message2, pmt.payment_text_message3, pmt.payment_details, pmt.unique_remittance_identifier, pmt.uri_check_digit, pmt.payment_reason_code, payment_reasons.meaning payment_reason_meaning, payment_reasons.format_value payment_reason_format_value, pmt.payment_reason_comments, pmt.remittance_message1, pmt.remittance_message2, pmt.remittance_message3, pmt.declare_payment_flag, pmt.declaration_amount, pmt.declaration_currency_code, iby_fd_extract_gen_pvt.get_allotment_code(pmt.payment_id) federal_allotment_code, iby_fd_extract_gen_pvt.top_offset_eligibility_flag(pmt.payment_id) federal_offset_eligibility, pmt.attribute_category payment_attr_cat , nvl(pmt.beneficiary_name, pmt.payee_name) beneficiary_name, iby_fd_extract_gen_pvt.get_sps_pmt_ts(pmt.payment_id) federal_sps_treasury_symbol, pmt.employee_payment_flag, nvl(payment_methods.format_value, pmt.payment_method_code) payment_method_format_value, pmt.logical_group_reference, pmt_grp.total_payment_amount, pmt_grp.total_payment_count, pmt.payment_profile_id, pmt.positive_pay_file_created_flag, pmt.inv_supplier_site_id, pmt.anticipated_value_date, pmt.created_by, pmt.creation_date, pmt.last_update_date, pmt.last_update_login, pmt.last_updated_by, pmt.void_date, pmt.bep_status, pmt.bep_error_code, pmt.bep_error_message_text, pmt.acknowledgement_date, pmt.payment_mode_code, pmt.payment_instrument_id FROM iby_payments_all pmt, iby_pay_service_requests req, fnd_application fndapp, fnd_document_sequences fnd_doc_seq, iby_payment_methods_vl payment_methods, iby_payment_codes_vl delivery_channels, iby_payment_codes_vl service_level, iby_payment_codes_vl bank_instructions1, iby_payment_codes_vl bank_instructions2, iby_payment_codes_vl payment_reasons, fnd_lookups payment_function_lookup, fnd_lookups payment_status_lookup, fnd_lookups settlement_priority_lookup, fnd_lookups bank_charge_bearer_lookup, fnd_lookups org_type_lookup , mo_glob_org_access_tmp mo_goa , iby_logical_pmt_groups pmt_grp WHERE pmt.payment_service_request_id = req.payment_service_request_id AND req.calling_app_id = fndapp.application_id AND pmt.document_sequence_id = fnd_doc_seq.doc_sequence_id(+) AND pmt.payment_method_code = payment_methods.payment_method_code AND pmt.org_id = mo_goa.organization_id AND pmt.org_type = 'OPERATING_UNIT' AND pmt.delivery_channel_code = delivery_channels.payment_code(+) AND delivery_channels.payment_code_type(+) = 'DELIVERY_CHANNEL' AND pmt.service_level_code = service_level.payment_code(+) AND service_level.payment_code_type(+) = 'SERVICE_LEVEL' AND pmt.bank_instruction1_code = bank_instructions1.payment_code(+) AND bank_instructions1.payment_code_type(+) = 'BANK_INSTRUCTION' AND pmt.bank_instruction2_code = bank_instructions2.payment_code(+) AND bank_instructions2.payment_code_type(+) = 'BANK_INSTRUCTION' AND pmt.payment_reason_code = payment_reasons.payment_code(+) AND payment_reasons.payment_code_type(+) = 'PAYMENT_REASON' AND pmt.payment_function = payment_function_lookup.lookup_code AND payment_function_lookup.lookup_type = 'IBY_PAYMENT_FUNCTIONS' AND pmt.payment_status = payment_status_lookup.lookup_code AND payment_status_lookup.lookup_type = 'IBY_PAYMENT_STATUSES' AND pmt.settlement_priority = settlement_priority_lookup.lookup_code(+) AND settlement_priority_lookup.lookup_type(+) = 'IBY_SETTLEMENT_PRIORITY' AND pmt.bank_charge_bearer = bank_charge_bearer_lookup.lookup_code(+) AND bank_charge_bearer_lookup.lookup_type(+) = 'IBY_BANK_CHARGE_BEARER' AND pmt.org_type = org_type_lookup.lookup_code AND org_type_lookup.lookup_type = 'IBY_ORGANIZATION_TYPES' AND pmt.payment_status NOT IN('VOID_BY_SETUP', 'VOID_BY_SETUP_REPRINT', 'VOID_BY_OVERFLOW', 'VOID_BY_OVERFLOW_REPRINT') AND pmt.logical_group_reference = pmt_grp.logical_group_reference(+) AND pmt.payment_instruction_id = pmt_grp.payment_instruction_id(+) UNION ALL SELECT pmt.payment_instruction_id, pmt.payment_id, pmt.internal_bank_account_id, pmt.legal_entity_id, NULL calling_app_id, /*req.calling_app_id,*/ NULL application_name, /*fndapp.application_short_name,*/ pmt.payment_service_request_id, pmt.payment_function, NULL payment_function_meaning, /*payment_function_lookup.meaning,*/ pmt.org_id payment_org_id, pmt.org_type payment_org_type, NULL payment_org_type_meaning, /*org_type_lookup.meaning,*/ NULL payment_org_name, /*ce_sp.name,*/ pmt.document_category_code payment_doc_category, NULL payment_doc_sequence_name, /*fnd_doc_seq.name,*/ pmt.document_sequence_value payment_doc_sequence_value, pmt.payment_reference_number, pmt.paper_document_number, pmt.payment_date, pmt.payment_due_date, pmt.maturity_date, pmt.payment_status, payment_status_lookup.meaning payment_status_meaning, pmt.inv_ext_payee_id, pmt.payee_party_id, pmt.party_site_id payee_party_site_use_id, pmt.external_bank_account_id, pmt.payment_amount, pmt.payment_currency_code, NULL payment_amount_text, /*IBY_FD_EXTRACT_GEN_PVT.Get_Payment_Amount_Text(pmt.payment_id),*/ pmt.payment_method_code, NULL payment_method_name, /*payment_methods.payment_method_name,*/ NULL payment_method_attr_cat, /*payment_methods.attribute_category,*/ pmt.exclusive_payment_flag, pmt.settlement_priority, NULL settlement_priority_meaning, /*settlement_priority_lookup.meaning,*/ NULL amount_withheld, /*IBY_FD_EXTRACT_GEN_PVT.Get_Payment_Amount_Withheld(pmt.payment_id),*/ pmt.discount_amount_taken, pmt.bank_charge_bearer, NULL bank_charge_bearer_meaning, /*bank_charge_bearer_lookup.meaning,*/ pmt.bank_charge_amount, pmt.delivery_channel_code, NULL delivery_channel_meaning, /*delivery_channels.meaning,*/ delivery_channel_format_value, /*delivery_channels.format_value,*/ pmt.service_level_code, NULL service_level_meaning, NULL service_level_format_value, pmt.bank_instruction1_code, NULL bank_instruction1_meaning, /*bank_instructions1.meaning,*/ NULL bank_instruction1_format_value, /*bank_instructions1.format_value,*/ pmt.bank_instruction2_code, NULL bank_instruction2_meaning, /*bank_instructions2.meaning,*/ NULL bank_instruction2_format_value, /*bank_instructions2.format_value,*/ pmt.bank_instruction_details, pmt.payment_text_message1, pmt.payment_text_message2, pmt.payment_text_message3, pmt.payment_details, pmt.unique_remittance_identifier, pmt.uri_check_digit, pmt.payment_reason_code, NULL payment_reason_meaning, /*payment_reasons.meaning,*/ NULL payment_reason_format_value, /*payment_reasons.format_value,*/ pmt.payment_reason_comments, pmt.remittance_message1, pmt.remittance_message2, pmt.remittance_message3, pmt.declare_payment_flag, pmt.declaration_amount, pmt.declaration_currency_code, NULL federal_allotment_code, /*IBY_FD_EXTRACT_GEN_PVT.get_Allotment_Code(pmt.payment_id),*/ NULL federal_offset_eligibility, /*IBY_FD_EXTRACT_GEN_PVT.TOP_Offset_Eligibility_Flag(pmt.payment_id),*/ pmt.attribute_category payment_attr_cat, nvl(pmt.beneficiary_name, pmt.payee_name) beneficiary_name, NULL federal_sps_treasury_symbol, /*IBY_FD_EXTRACT_GEN_PVT.get_SPS_PMT_TS(pmt.payment_id),*/ pmt.employee_payment_flag, NULL payment_method_format_value, /*nvl(payment_methods.format_value, pmt.payment_method_code)*/ pmt.logical_group_reference, pmt_grp.total_payment_amount, pmt_grp.total_payment_count, pmt.payment_profile_id, pmt.positive_pay_file_created_flag, pmt.inv_supplier_site_id, pmt.anticipated_value_date, pmt.created_by, pmt.creation_date, pmt.last_update_date, pmt.last_update_login, pmt.last_updated_by, NULL void_date, pmt.bep_status, pmt.bep_error_code, pmt.bep_error_message_text, pmt.acknowledgement_date, pmt.payment_mode_code, pmt.payment_instrument_id FROM iby_payments_all pmt, /*iby_pay_service_requests req,*/ /*fnd_application fndapp,*/ /*fnd_document_sequences fnd_doc_seq, */ /*iby_payment_methods_vl payment_methods,*/ /*iby_delivery_channels_vl delivery_channels,*/ /*iby_bank_instructions_vl bank_instructions1,*/ /*iby_bank_instructions_vl bank_instructions2,*/ /*iby_payment_reasons_vl payment_reasons,*/ /*fnd_lookups payment_function_lookup,*/ fnd_lookups payment_status_lookup, /*fnd_lookups settlement_priority_lookup,*/ /*fnd_lookups bank_charge_bearer_lookup,*/ /*fnd_lookups org_type_lookup,*/ /*ce_security_profiles_gt ce_sp*/ iby_logical_pmt_groups pmt_grp WHERE pmt.payment_status IN('VOID_BY_SETUP', 'VOID_BY_SETUP_REPRINT', 'VOID_BY_OVERFLOW', 'VOID_BY_OVERFLOW_REPRINT') AND pmt.payment_status = payment_status_lookup.lookup_code AND payment_status_lookup.lookup_type = 'IBY_PAYMENT_STATUSES' AND pmt.logical_group_reference = pmt_grp.logical_group_reference(+) AND pmt.payment_instruction_id = pmt_grp.payment_instruction_id(+)