IBY_XML_FD_INS_1_0

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
INSTRUCTION PAYMENT_INSTRUCTION_ID

Name

INSTRUCTION

PAYMENT_INSTRUCTION_ID

Query

SQL_Statement
SELECT xmlelement("OutboundPaymentInstruction", XMLElement("RecordHistory", XMLElement("CreatedBy",ins.created_by),XMLElement("CreationDate",to_char(ins.instruction_created_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateDate",to_char(ins.last_update_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateLogin",ins.last_update_login),XMLElement("LastUpdatedBy",ins.last_updated_by)), CASE WHEN iby_utility_pvt.isFeatureEnabled(p_lookupType=> 'ORA_IBY_FETCH_TRX_TAX_DETAILS',p_lookupcode=>'Y') = 'Y' THEN XMLElement("LedgerCurrency",IBY_FD_EXTRACT_GEN_PVT.Get_Ledg_Curr_FieldsAgg(ins.payment_instruction_id)) ELSE NULL END, xmlelement("PaymentInstructionInfo", xmlelement("InstructionReferenceNumber", ins.payment_instruction_id), xmlelement("FormatProgramRequestID", fnd_job.request_id), xmlelement("InstructionCreationDate", to_char(ins.instruction_created_date, 'YYYY-MM-DD"T"HH24:MI:SS')\|\|'+00.00'), xmlelement("MultiOrgAccessControl", xmlelement("HasBlockedData", ins.moac_data_blocked_flag)), xmlelement("InstructionStatus", xmlelement("Code", ins.payment_instruction_status), xmlelement("Meaning", ins.instruction_status_meaning)), xmlelement("ProcessingType", xmlelement("Code", ins.processing_type), xmlelement("Meaning", ins.processing_type_meaning)), xmlelement("ProcessType", ins.process_type), xmlelement("UserAssignedRefCode", ins.pay_admin_assigned_ref_code), xmlelement("PaymentMode", ins.payment_mode_code), xmlelement("BankAssignedRefCode", ins.bank_assigned_ref_code), xmlelement("PaymentSequence", xmlelement("SequenceName", ins.periodic_sequence_name_1), xmlelement("LastValue", ins.last_used_number_1)), xmlelement("PaymentSequence", xmlelement("SequenceName", ins.periodic_sequence_name_2), xmlelement("LastValue", ins.last_used_number_2)), xmlelement("PaymentSequence", xmlelement("SequenceName", ins.periodic_sequence_name_3), xmlelement("LastValue", ins.last_used_number_3)), xmlelement("BankInstruction", xmlelement("BankInstructionCode", ins.bank_instruction1_code), xmlelement("Meaning", ins.bank_instruction1_meaning), xmlelement("FormatValue", ins.bank_instruction1_format_value)), xmlelement("BankInstruction", xmlelement("BankInstructionCode", ins.bank_instruction2_code), xmlelement("Meaning", ins.bank_instruction2_meaning), xmlelement("FormatValue", ins.bank_instruction2_format_value)), xmlelement("BankInstructionDetails", ins.bank_instruction_details), xmlelement("PaymentTextMessage", ins.payment_text_message1), xmlelement("PaymentTextMessage", ins.payment_text_message2), xmlelement("RegulatoryReportingOption", ins.declaration_option), CASE WHEN iby_utility_pvt.get_view_param('AMEX_FILE_SEQ_NO') IS NULL THEN NULL ELSE xmlelement("CCRemittanceFileSequenceNumber",iby_utility_pvt.get_view_param('AMEX_FILE_SEQ_NO')) END, CASE WHEN iby_utility_pvt.get_view_param('AMEX_FILE_VER_NO') IS NULL THEN NULL ELSE xmlelement("CCRemittanceFileVersionNumber",iby_utility_pvt.get_view_param('AMEX_FILE_VER_NO')) END, xmlelement("PaymentSystemAccount", xmlelement("AccountName", ins.bep_account_name), iby_fd_extract_gen_pvt.get_ins_accountsettingsagg(ins.bep_account_id), xmlelement("DescriptiveFlexField", iby_extractgen_pvt.get_dffs('IBY_BEPKEYS', ins.bep_account_id, NULL)) ), xmlelement("Notes", comments), CASE WHEN ins.pi_attr_cat IS NULL THEN NULL ELSE xmlelement("DescriptiveFlexField", iby_extractgen_pvt.get_dffs('IBY_PAY_INSTRUCTIONS_ALL', ins.payment_instruction_id, NULL)) END), xmlelement("PaymentProcessProfile", xmlelement("PaymentProcessProfileInternalID", ins.payment_profile_id), xmlelement("PaymentProcessProfileName", ins.payment_profile_name), xmlelement("BatchBookingFlag", ins.batch_booking_flag), xmlelement("LogicalGrouping", xmlelement("GroupByPaymentDateLogical", group_by_payment_date_logical), xmlelement("GroupByBankAccountLogical", group_by_bank_account_log), xmlelement("GroupBySettlementPriority", group_by_settlemnt_priority), xmlelement("GroupByServiceLevel", group_by_service_level), xmlelement("GroupByBankChargeBearer", group_by_bank_charge_bearer), xmlelement("GroupByUltimateDebtor", group_by_ultimate_debtor), xmlelement("GroupByCategoryPurpose", group_by_category_purpose), xmlelement("GroupByPaymentMethod", group_by_payment_method)), xmlelement("DescriptiveFlexField", iby_extractgen_pvt.get_dffs('IBY_SYS_PMT_PROFILES_B', NULL, ins.system_profile_code))), xmlelement("PaymentFormat", xmlelement("PaymentFormatInternalID", ins.payment_format_code), xmlelement("PaymentFormatName", ins.payment_format_name), xmlelement("DescriptiveFlexField", iby_extractgen_pvt.get_dffs('IBY_FORMATS_B', NULL, ins.payment_format_code)) ), CASE WHEN ins.payment_document_name IS NOT NULL THEN xmlelement("CheckFormatInfo", xmlelement("PaymentDocumentName", ins.payment_document_name), xmlelement("PaperStockType", xmlelement("Code", ins.paper_stock_type), xmlelement("Meaning", ins.paper_stock_type_meaning))) ELSE NULL END, xmlelement("InstructionTotals", xmlelement("PaymentCount", ins.payment_count), xmlelement("TotalPaymentAmount", xmlelement("Value", NVL(ins.total_pmt_amount, 0)), xmlelement("Currency", xmlelement("Code", ins.payment_currency_code)))), xmlelement("InstructionGrouping", CASE WHEN ins.group_by_payment_date = 'Y' THEN xmlelement("PaymentDate", to_char(ins.payment_date, 'YYYY-MM-DD"T"HH24:MI:SS')) ELSE NULL END, CASE WHEN ins.group_by_payment_currency = 'Y' THEN xmlelement("PaymentCurrency", xmlelement("Code", ins.payment_currency_code), xmlelement("Name", fnd_curr.name)) ELSE NULL END, CASE WHEN ins.group_by_pay_service_request = 'Y' THEN xmlelement("PaymentServiceRequestID", ins.payment_service_request_id) ELSE NULL END, xmlelement("Payer", iby_fd_extract_gen_pvt.get_payerins_denorm(payment_instruction_id)), CASE WHEN ins.group_by_organization = 'Y' THEN xmlelement("PayerOrganization", xmlelement("OrganizationInternalID", ins.org_id), xmlelement("OrganizationType", xmlelement("Code", ins.org_type), xmlelement("Meaning", ins.org_type_meaning)), xmlelement("OrganizationName", ins.org_name)) ELSE NULL END, xmlelement("BankAccount", iby_fd_extract_gen_pvt.get_payerbankaccountins_denorm(payment_instruction_id)), CASE WHEN ins.group_by_payment_reason = 'Y' THEN xmlelement("PaymentReason", xmlelement("Code", ins.payment_reason_code), xmlelement("Meaning", ins.pay_reason_meaning), xmlelement("FormatValue", ins.pay_reason_format_value)) ELSE NULL END, CASE WHEN ins.group_by_payment_reason = 'Y' THEN xmlelement("PaymentReasonComments", ins.payment_reason_comments) ELSE NULL END, CASE WHEN ins.group_by_payment_function = 'Y' THEN xmlelement("FunctionalCategory", xmlelement("Code", ins.payment_function), xmlelement("Meaning", ins.payment_function_meaning)) ELSE NULL END, CASE WHEN ins.group_by_rfc = 'Y' THEN xmlelement("FederalRFCIdentifier", ins.rfc_identifier) ELSE NULL END), CASE WHEN ins.acknowledgement_date IS NOT NULL THEN XMLElement("AcknowledgementDetails",XMLElement("InBoundIdentifier", ins.inbound_file_id),XMLElement("InBoundFile", ins.file_name),XMLElement("InBoundFileStatus", ins.processing_status), XMLElement("BepStatus", ins.bep_status), XMLElement("BepErrorCode", ins.bep_error_code), XMLElement("BepErrorMessage", ins.bep_error_message_text),XMLElement("AcknowledgementProcessingDate", ins.acknowledgement_date)) ELSE NULL END, XMLELEMENT("FederalPFExtend",iby_fd_extract_gen_pvt.get_ins_fvfieldsagg(ins.payment_instruction_id)), iby_fd_extract_gen_pvt.get_ins_payerinstragg(ins.payment_instruction_id), iby_fd_extract_ext_pub.get_ins_ext_agg(ins.payment_instruction_id), iby_fd_extract_gen_pvt.get_ins_br_ext_agg(ins.payment_instruction_id)), ins.payment_instruction_id FROM iby_ext_fd_ins_1_0_v ins, fnd_currencies_vl fnd_curr WHERE ins.payment_currency_code = fnd_curr.currency_code(+)

SQL_Statement

SELECT xmlelement("OutboundPaymentInstruction", XMLElement("RecordHistory", XMLElement("CreatedBy",ins.created_by),XMLElement("CreationDate",to_char(ins.instruction_created_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateDate",to_char(ins.last_update_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateLogin",ins.last_update_login),XMLElement("LastUpdatedBy",ins.last_updated_by)),

CASE

WHEN iby_utility_pvt.isFeatureEnabled(p_lookupType=> 'ORA_IBY_FETCH_TRX_TAX_DETAILS',p_lookupcode=>'Y') = 'Y'

THEN

XMLElement("LedgerCurrency",IBY_FD_EXTRACT_GEN_PVT.Get_Ledg_Curr_FieldsAgg(ins.payment_instruction_id))

ELSE NULL

END, xmlelement("PaymentInstructionInfo", xmlelement("InstructionReferenceNumber", ins.payment_instruction_id), xmlelement("FormatProgramRequestID", fnd_job.request_id), xmlelement("InstructionCreationDate", to_char(ins.instruction_created_date, 'YYYY-MM-DD"T"HH24:MI:SS')||'+00.00'), xmlelement("MultiOrgAccessControl", xmlelement("HasBlockedData", ins.moac_data_blocked_flag)), xmlelement("InstructionStatus", xmlelement("Code", ins.payment_instruction_status), xmlelement("Meaning", ins.instruction_status_meaning)), xmlelement("ProcessingType", xmlelement("Code", ins.processing_type), xmlelement("Meaning", ins.processing_type_meaning)), xmlelement("ProcessType", ins.process_type), xmlelement("UserAssignedRefCode", ins.pay_admin_assigned_ref_code), xmlelement("PaymentMode", ins.payment_mode_code), xmlelement("BankAssignedRefCode", ins.bank_assigned_ref_code), xmlelement("PaymentSequence", xmlelement("SequenceName", ins.periodic_sequence_name_1), xmlelement("LastValue", ins.last_used_number_1)), xmlelement("PaymentSequence", xmlelement("SequenceName", ins.periodic_sequence_name_2), xmlelement("LastValue", ins.last_used_number_2)), xmlelement("PaymentSequence", xmlelement("SequenceName", ins.periodic_sequence_name_3), xmlelement("LastValue", ins.last_used_number_3)), xmlelement("BankInstruction", xmlelement("BankInstructionCode", ins.bank_instruction1_code), xmlelement("Meaning", ins.bank_instruction1_meaning), xmlelement("FormatValue", ins.bank_instruction1_format_value)), xmlelement("BankInstruction", xmlelement("BankInstructionCode", ins.bank_instruction2_code), xmlelement("Meaning", ins.bank_instruction2_meaning), xmlelement("FormatValue", ins.bank_instruction2_format_value)), xmlelement("BankInstructionDetails", ins.bank_instruction_details), xmlelement("PaymentTextMessage", ins.payment_text_message1), xmlelement("PaymentTextMessage", ins.payment_text_message2), xmlelement("RegulatoryReportingOption", ins.declaration_option),

CASE

WHEN iby_utility_pvt.get_view_param('AMEX_FILE_SEQ_NO') IS NULL THEN NULL

ELSE xmlelement("CCRemittanceFileSequenceNumber",iby_utility_pvt.get_view_param('AMEX_FILE_SEQ_NO'))

END,

CASE

WHEN iby_utility_pvt.get_view_param('AMEX_FILE_VER_NO') IS NULL THEN NULL

ELSE xmlelement("CCRemittanceFileVersionNumber",iby_utility_pvt.get_view_param('AMEX_FILE_VER_NO'))

END,

xmlelement("PaymentSystemAccount", xmlelement("AccountName", ins.bep_account_name), iby_fd_extract_gen_pvt.get_ins_accountsettingsagg(ins.bep_account_id),

xmlelement("DescriptiveFlexField", iby_extractgen_pvt.get_dffs('IBY_BEPKEYS', ins.bep_account_id, NULL))

),

xmlelement("Notes", comments),

CASE

WHEN ins.pi_attr_cat IS NULL THEN NULL

ELSE xmlelement("DescriptiveFlexField", iby_extractgen_pvt.get_dffs('IBY_PAY_INSTRUCTIONS_ALL', ins.payment_instruction_id, NULL))

END), xmlelement("PaymentProcessProfile", xmlelement("PaymentProcessProfileInternalID", ins.payment_profile_id), xmlelement("PaymentProcessProfileName", ins.payment_profile_name),

xmlelement("BatchBookingFlag", ins.batch_booking_flag),

xmlelement("LogicalGrouping", xmlelement("GroupByPaymentDateLogical", group_by_payment_date_logical),

xmlelement("GroupByBankAccountLogical", group_by_bank_account_log),

xmlelement("GroupBySettlementPriority", group_by_settlemnt_priority),

xmlelement("GroupByServiceLevel", group_by_service_level),

xmlelement("GroupByBankChargeBearer", group_by_bank_charge_bearer),

xmlelement("GroupByUltimateDebtor", group_by_ultimate_debtor),

xmlelement("GroupByCategoryPurpose", group_by_category_purpose),

xmlelement("GroupByPaymentMethod", group_by_payment_method)),

xmlelement("DescriptiveFlexField", iby_extractgen_pvt.get_dffs('IBY_SYS_PMT_PROFILES_B', NULL, ins.system_profile_code))),

xmlelement("PaymentFormat", xmlelement("PaymentFormatInternalID", ins.payment_format_code), xmlelement("PaymentFormatName", ins.payment_format_name),

xmlelement("DescriptiveFlexField", iby_extractgen_pvt.get_dffs('IBY_FORMATS_B', NULL, ins.payment_format_code))

),

CASE

WHEN ins.payment_document_name IS NOT NULL THEN xmlelement("CheckFormatInfo", xmlelement("PaymentDocumentName", ins.payment_document_name), xmlelement("PaperStockType", xmlelement("Code", ins.paper_stock_type), xmlelement("Meaning", ins.paper_stock_type_meaning)))

ELSE NULL

END, xmlelement("InstructionTotals", xmlelement("PaymentCount", ins.payment_count), xmlelement("TotalPaymentAmount", xmlelement("Value", NVL(ins.total_pmt_amount, 0)), xmlelement("Currency", xmlelement("Code", ins.payment_currency_code)))), xmlelement("InstructionGrouping",

CASE

WHEN ins.group_by_payment_date = 'Y' THEN xmlelement("PaymentDate", to_char(ins.payment_date, 'YYYY-MM-DD"T"HH24:MI:SS'))

ELSE NULL

END,

CASE

WHEN ins.group_by_payment_currency = 'Y' THEN xmlelement("PaymentCurrency", xmlelement("Code", ins.payment_currency_code), xmlelement("Name", fnd_curr.name))

ELSE NULL

END,

CASE

WHEN ins.group_by_pay_service_request = 'Y' THEN xmlelement("PaymentServiceRequestID", ins.payment_service_request_id)

ELSE NULL

END, xmlelement("Payer", iby_fd_extract_gen_pvt.get_payerins_denorm(payment_instruction_id)),

CASE

WHEN ins.group_by_organization = 'Y' THEN xmlelement("PayerOrganization", xmlelement("OrganizationInternalID", ins.org_id), xmlelement("OrganizationType", xmlelement("Code", ins.org_type), xmlelement("Meaning", ins.org_type_meaning)), xmlelement("OrganizationName", ins.org_name))

ELSE NULL

END, xmlelement("BankAccount", iby_fd_extract_gen_pvt.get_payerbankaccountins_denorm(payment_instruction_id)),

CASE

WHEN ins.group_by_payment_reason = 'Y' THEN xmlelement("PaymentReason", xmlelement("Code", ins.payment_reason_code), xmlelement("Meaning", ins.pay_reason_meaning), xmlelement("FormatValue", ins.pay_reason_format_value))

ELSE NULL

END,

CASE

WHEN ins.group_by_payment_reason = 'Y' THEN xmlelement("PaymentReasonComments", ins.payment_reason_comments)

ELSE NULL

END,

CASE

WHEN ins.group_by_payment_function = 'Y' THEN xmlelement("FunctionalCategory", xmlelement("Code", ins.payment_function), xmlelement("Meaning", ins.payment_function_meaning))

ELSE NULL

END,

CASE

WHEN ins.group_by_rfc = 'Y' THEN xmlelement("FederalRFCIdentifier", ins.rfc_identifier)

ELSE NULL

END),

CASE WHEN ins.acknowledgement_date IS NOT NULL THEN XMLElement("AcknowledgementDetails",XMLElement("InBoundIdentifier", ins.inbound_file_id),XMLElement("InBoundFile", ins.file_name),XMLElement("InBoundFileStatus", ins.processing_status), XMLElement("BepStatus", ins.bep_status), XMLElement("BepErrorCode", ins.bep_error_code), XMLElement("BepErrorMessage", ins.bep_error_message_text),XMLElement("AcknowledgementProcessingDate", ins.acknowledgement_date))

ELSE NULL END,

XMLELEMENT("FederalPFExtend",iby_fd_extract_gen_pvt.get_ins_fvfieldsagg(ins.payment_instruction_id)), iby_fd_extract_gen_pvt.get_ins_payerinstragg(ins.payment_instruction_id), iby_fd_extract_ext_pub.get_ins_ext_agg(ins.payment_instruction_id), iby_fd_extract_gen_pvt.get_ins_br_ext_agg(ins.payment_instruction_id)),

ins.payment_instruction_id

FROM iby_ext_fd_ins_1_0_v ins,

fnd_currencies_vl fnd_curr

WHERE ins.payment_currency_code = fnd_curr.currency_code(+)