VIEW: APPS.IBY_EXT_FD_INS_1_0_V

Source

View Text - Preformatted

SELECT ins.payment_instruction_id, ins.creation_date, ins.payment_instruction_status, pi_status_lookup.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, pp.bep_account_id, paysys_acct.attribute_category, ins.comments, ins.attribute_category, pp.payment_profile_id, pp.system_profile_code, pp.payment_profile_name, pp.logical_grouping_mode, pp.batch_booking_flag, pp.attribute_category, pf.format_code, pf.format_name, pf.attribute_category, 1, ins.payment_count, ins.payment_date, ins.payment_currency_code, ins.payment_service_request_id, ins.org_id, ins.org_type, org_type_lookup.meaning, ce_sp.name, ins.payment_reason_code, pay_reason.meaning, 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, pi_rules.group_by_payment_method, ins.internal_bank_account_id, ins.payment_function, pmt_func_lookup.meaning, ins.rfc_identifier, ins.payment_method_code, ins.process_type, IBY_FD_EXTRACT_GEN_PVT.Get_Ins_TotalAmt(ins.payment_instruction_id), Decode(IBY_FD_POST_PICP_PROGS_PVT.val_instruction_accessible(ins.payment_instruction_id), 'Y', 'N', 'Y'), ce_doc.payment_document_name, ce_doc.paper_stock_type, paper_stock_type_lookup.meaning, ins.card_issuer_file_seq_no FROM iby_pay_instructions_all ins, fnd_lookups pi_status_lookup, iby_payment_profiles pp, iby_instr_creation_rules pi_rules, IBY_BEPKEYS paysys_acct, iby_formats_vl pf, iby_bank_instructions_vl bi1, iby_bank_instructions_vl bi2, iby_payment_reasons_vl pay_reason, fnd_lookups org_type_lookup, fnd_lookups pmt_func_lookup, ce_security_profiles_gt ce_sp, ce_payment_documents ce_doc, ce_lookups paper_stock_type_lookup WHERE pp.payment_profile_id = ins.payment_profile_id AND pp.system_profile_code = pi_rules.system_profile_code(+) AND pp.bep_account_id = paysys_acct.bep_account_id(+) AND pp.payment_format_code = pf.format_code 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_reason_code(+) AND pp.bank_instruction1_code = bi1.bank_instruction_code(+) AND pp.bank_instruction2_code = bi2.bank_instruction_code(+) AND ins.org_id = ce_sp.organization_id(+) AND ins.org_type = ce_sp.organization_type(+) 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(+)

View Text - HTML Formatted

SELECT INS.PAYMENT_INSTRUCTION_ID
, INS.CREATION_DATE
, INS.PAYMENT_INSTRUCTION_STATUS
, PI_STATUS_LOOKUP.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
, PP.BEP_ACCOUNT_ID
, PAYSYS_ACCT.ATTRIBUTE_CATEGORY
, INS.COMMENTS
, INS.ATTRIBUTE_CATEGORY
, PP.PAYMENT_PROFILE_ID
, PP.SYSTEM_PROFILE_CODE
, PP.PAYMENT_PROFILE_NAME
, PP.LOGICAL_GROUPING_MODE
, PP.BATCH_BOOKING_FLAG
, PP.ATTRIBUTE_CATEGORY
, PF.FORMAT_CODE
, PF.FORMAT_NAME
, PF.ATTRIBUTE_CATEGORY
, 1
, INS.PAYMENT_COUNT
, INS.PAYMENT_DATE
, INS.PAYMENT_CURRENCY_CODE
, INS.PAYMENT_SERVICE_REQUEST_ID
, INS.ORG_ID
, INS.ORG_TYPE
, ORG_TYPE_LOOKUP.MEANING
, CE_SP.NAME
, INS.PAYMENT_REASON_CODE
, PAY_REASON.MEANING
, 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
, PI_RULES.GROUP_BY_PAYMENT_METHOD
, INS.INTERNAL_BANK_ACCOUNT_ID
, INS.PAYMENT_FUNCTION
, PMT_FUNC_LOOKUP.MEANING
, INS.RFC_IDENTIFIER
, INS.PAYMENT_METHOD_CODE
, INS.PROCESS_TYPE
, IBY_FD_EXTRACT_GEN_PVT.GET_INS_TOTALAMT(INS.PAYMENT_INSTRUCTION_ID)
, DECODE(IBY_FD_POST_PICP_PROGS_PVT.VAL_INSTRUCTION_ACCESSIBLE(INS.PAYMENT_INSTRUCTION_ID)
, 'Y'
, 'N'
, 'Y')
, CE_DOC.PAYMENT_DOCUMENT_NAME
, CE_DOC.PAPER_STOCK_TYPE
, PAPER_STOCK_TYPE_LOOKUP.MEANING
, INS.CARD_ISSUER_FILE_SEQ_NO
FROM IBY_PAY_INSTRUCTIONS_ALL INS
, FND_LOOKUPS PI_STATUS_LOOKUP
, IBY_PAYMENT_PROFILES PP
, IBY_INSTR_CREATION_RULES PI_RULES
, IBY_BEPKEYS PAYSYS_ACCT
, IBY_FORMATS_VL PF
, IBY_BANK_INSTRUCTIONS_VL BI1
, IBY_BANK_INSTRUCTIONS_VL BI2
, IBY_PAYMENT_REASONS_VL PAY_REASON
, FND_LOOKUPS ORG_TYPE_LOOKUP
, FND_LOOKUPS PMT_FUNC_LOOKUP
, CE_SECURITY_PROFILES_GT CE_SP
, CE_PAYMENT_DOCUMENTS CE_DOC
, CE_LOOKUPS PAPER_STOCK_TYPE_LOOKUP
WHERE PP.PAYMENT_PROFILE_ID = INS.PAYMENT_PROFILE_ID
AND PP.SYSTEM_PROFILE_CODE = PI_RULES.SYSTEM_PROFILE_CODE(+)
AND PP.BEP_ACCOUNT_ID = PAYSYS_ACCT.BEP_ACCOUNT_ID(+)
AND PP.PAYMENT_FORMAT_CODE = PF.FORMAT_CODE
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_REASON_CODE(+)
AND PP.BANK_INSTRUCTION1_CODE = BI1.BANK_INSTRUCTION_CODE(+)
AND PP.BANK_INSTRUCTION2_CODE = BI2.BANK_INSTRUCTION_CODE(+)
AND INS.ORG_ID = CE_SP.ORGANIZATION_ID(+)
AND INS.ORG_TYPE = CE_SP.ORGANIZATION_TYPE(+)
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(+)