IBY_XML_FD_DOC_1_0_V

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
DOC_PAYABLE PAYMENT_ID FORMATTING_PAYMENT_ID PAYMENT_SERVICE_REQUEST_ID DOCUMENT_PAYABLE_ID DOCUMENT_STATUS

Name

DOC_PAYABLE

PAYMENT_ID

FORMATTING_PAYMENT_ID

PAYMENT_SERVICE_REQUEST_ID

DOCUMENT_PAYABLE_ID

DOCUMENT_STATUS

Query

SQL_Statement
SELECT XMLElement("DocumentPayable", XMLElement("RecordHistory",XMLElement("CreatedBy",doc.created_by),XMLElement("CreationDate",to_char(doc.creation_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateDate",to_char(doc.last_update_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateLogin",doc.last_update_login),XMLElement("LastUpdatedBy",doc.last_updated_by)),XMLElement("InvoiceBusinessUnit", XMLElement("InvoiceBusinessUnitId", doc.invoicing_org_id), XMLElement("InvoiceBusinessUnitType", XMLElement("Code", doc.invoicing_org_type), XMLElement("Meaning", doc.invoicing_org_type_meaning)), XMLElement("InvoiceBusinessUnitName", doc.invoicing_org_name)), XMLElement("DocumentNumber", XMLElement("DocumentInternalIDSegment1", doc.calling_app_doc_unique_ref1), XMLElement("DocumentInternalIDSegment2", doc.calling_app_doc_unique_ref2), XMLElement("DocumentInternalIDSegment3", doc.calling_app_doc_unique_ref3), XMLElement("DocumentInternalIDSegment4", doc.calling_app_doc_unique_ref4), XMLElement("DocumentInternalIDSegment5", doc.calling_app_doc_unique_ref5), XMLElement("ApplicationInternalID", doc.calling_app_id), XMLElement("ApplicationDocumentSubType", doc.pay_proc_trxn_type_code), XMLElement("ApplicationUniqueInternalID", doc.calling_app_document_unique_id), XMLElement("ReferenceNumber", doc.calling_app_doc_ref_number), CASE WHEN doc.bank_assigned_ref_code IS NOT NULL THEN XMLElement("BankAssignedRefCode", doc.bank_assigned_ref_code) ELSE NULL END, XMLElement("UniqueRemittanceIdentifier", XMLElement("Number", doc.unique_remittance_identifier), XMLElement("CheckDigit", doc.uri_check_digit)), XMLElement("DocCategory", doc.doc_category), XMLElement("SequenceName", doc.doc_sequence_name), XMLElement("SequenceValue", doc.doc_sequence_value)), XMLElement("PONumber", doc.po_number), XMLElement("DocumentStatus", XMLElement("Code", doc.document_status), XMLElement("Meaning", doc.document_status_meaning)), IBY_FD_EXTRACT_GEN_PVT.Get_Doc_DocErrAgg(doc.document_payable_id), XMLElement("DocumentDate", doc.document_date), XMLElement("DocumentCreationDate", doc.document_creation_date), XMLElement("RoutingAttributes", XMLElement("RoutingAttribute1", doc.ROUTING_ATTRIBUTE1),XMLElement("RoutingAttribute2", doc.ROUTING_ATTRIBUTE2),XMLElement("RoutingAttribute3", doc.ROUTING_ATTRIBUTE3),XMLElement("RoutingAttribute4", doc.ROUTING_ATTRIBUTE4),XMLElement("RoutingAttribute5", doc.ROUTING_ATTRIBUTE5)), CASE WHEN doc.payment_due_date IS NOT NULL THEN XMLElement("PaymentDueDate", doc.payment_due_date) ELSE NULL END, IBY_FD_EXTRACT_GEN_PVT.Get_Doc_DD_Agg(doc.document_payable_id),XMLElement("DocumentType", XMLElement("Code", doc.document_type), XMLElement("Meaning", doc.document_type_meaning)), XMLElement("DocumentDescription", iby_utility_pvt.replace_bad_chars(doc.document_description)), XMLElement("ExpenseReportCreditCardNumber", doc.EXPENSE_RPT_CC_NUM), XMLElement("EmployeePaymentFlag", doc.employee_payment_flag), XMLElement("TotalDocumentAmount", XMLElement("Value", NVL(doc.document_amount, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))), XMLElement("PaymentAmount", XMLElement("Value", NVL(doc.payment_amount, 0)), XMLElement("Currency", XMLElement("Code", doc.payment_currency_code))), XMLElement("PaymentAmountinDocumentCurrency", XMLElement("Value", NVL(doc.doc_currency_payment_amount, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))),XMLElement("PayAloneFlag", doc.exclusive_payment_flag), XMLElement("SettlementPriority", XMLElement("Code", doc.settlement_priority), XMLElement("Meaning", doc.settlement_priority_meaning)), XMLElement("AmountWithheld", XMLElement("Value", NVL(doc.amount_withheld, 0)), XMLElement("Currency", XMLElement("Code", doc.payment_currency_code))), XMLElement("AmoutWithheldinDocumentCurrency", XMLElement("Value", NVL(doc.doc_curr_amount_withheld, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))), XMLElement("DiscountTaken", XMLElement("Amount", XMLElement("Value", NVL(doc.payment_curr_discount_taken, 0)), XMLElement("Currency", XMLElement("Code", doc.payment_currency_code))), XMLElement("DiscountDate", doc.discount_date)), XMLElement("DiscountTakeninDocumentCurrency", XMLElement("Amount", XMLElement("Value", NVL(doc.doc_curr_discount_taken, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code)))), XMLElement("BankCharges", XMLElement("BankChargeBearer", XMLElement("Code", doc.bank_charge_bearer), XMLElement("Meaning", doc.bank_charge_bearer_meaning))), XMLElement("DeliveryChannel", XMLElement("Code", doc.delivery_channel_code), XMLElement("Meaning", doc.delivery_channel_meaning), XMLElement("FormatValue", doc.delivery_channel_format_value)), XMLElement("PaymentReason", XMLElement("Code", doc.payment_reason_code), XMLElement("Meaning", doc.payment_reason_meaning), XMLElement("FormatValue", doc.payment_reason_format_value)), XMLElement("PaymentReasonComments", doc.payment_reason_comments), XMLElement("RemittanceMessage", doc.remittance_message1), XMLElement("RemittanceMessage", doc.remittance_message2), XMLElement("RemittanceMessage", doc.remittance_message3), XMLElement("Charge", XMLElement("TotalDocumentChargeAmount", XMLElement("Value", NVL(doc.document_curr_charge_amount, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code)))), XMLElement("TotalDocumentTaxAmount", XMLElement("Value", NVL(doc.DOCUMENT_CURRENCY_TAX_AMOUNT, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))), XMLElement("CreditAmountApplied", XMLElement("Value", NVL(doc.BORDERO_ABATEMENT, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))), XMLElement("InterestAmountApplied", XMLElement("Value", NVL(doc.BORDERO_INT_AMT, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))), XMLElement("InterestRate", NVL(doc.interest_rate, 0)), decode(fndapl.application_short_name, 'AP', IBY_FD_EXTRACT_GEN_PVT.Get_Doc_DocLineAgg(doc.document_payable_id), null), IBY_FD_EXTRACT_EXT_PUB.Get_Doc_Ext_Agg(doc.document_payable_id), IBY_FD_EXTRACT_GEN_PVT.Get_Doc_BR_Ext_Agg(doc.document_payable_id), XMLConcat(XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_DOCS_PAYABLE_ALL', doc.document_payable_id, NULL)),XMLElement("GlobalDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Gdfs( 'IBY_DOCS_PAYABLE_ALL', doc.document_payable_id, NULL))), XMLElement("SrcDocDescriptiveFlexField", decode(fndapl.application_short_name, 'AP', IBY_EXTRACTGEN_PVT.Get_Dffs('AP_DOCUMENTS_PAYABLE', doc.document_payable_id, NULL), null)) ,XMLElement("SrcDocGlobalDescriptiveFlexField", DECODE(fndapl.application_short_name, 'AP', IBY_EXTRACTGEN_PVT.Get_Gdfs('AP_DOCUMENTS_PAYABLE', doc.document_payable_id, NULL), NULL))), doc.payment_id PAYMENT_ID ,doc.formatting_payment_id formatting_payment_id, doc.payment_service_request_id, doc.document_payable_id, doc.document_status FROM iby_ext_fd_doc_1_0_v doc, fnd_application fndapl WHERE doc.calling_app_id = fndapl.application_id

SQL_Statement

SELECT XMLElement("DocumentPayable", XMLElement("RecordHistory",XMLElement("CreatedBy",doc.created_by),XMLElement("CreationDate",to_char(doc.creation_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateDate",to_char(doc.last_update_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateLogin",doc.last_update_login),XMLElement("LastUpdatedBy",doc.last_updated_by)),XMLElement("InvoiceBusinessUnit", XMLElement("InvoiceBusinessUnitId", doc.invoicing_org_id), XMLElement("InvoiceBusinessUnitType", XMLElement("Code", doc.invoicing_org_type), XMLElement("Meaning", doc.invoicing_org_type_meaning)), XMLElement("InvoiceBusinessUnitName", doc.invoicing_org_name)), XMLElement("DocumentNumber", XMLElement("DocumentInternalIDSegment1", doc.calling_app_doc_unique_ref1), XMLElement("DocumentInternalIDSegment2", doc.calling_app_doc_unique_ref2), XMLElement("DocumentInternalIDSegment3", doc.calling_app_doc_unique_ref3), XMLElement("DocumentInternalIDSegment4", doc.calling_app_doc_unique_ref4), XMLElement("DocumentInternalIDSegment5", doc.calling_app_doc_unique_ref5), XMLElement("ApplicationInternalID", doc.calling_app_id), XMLElement("ApplicationDocumentSubType", doc.pay_proc_trxn_type_code), XMLElement("ApplicationUniqueInternalID", doc.calling_app_document_unique_id), XMLElement("ReferenceNumber", doc.calling_app_doc_ref_number), CASE WHEN doc.bank_assigned_ref_code IS NOT NULL THEN XMLElement("BankAssignedRefCode", doc.bank_assigned_ref_code) ELSE NULL END, XMLElement("UniqueRemittanceIdentifier", XMLElement("Number", doc.unique_remittance_identifier), XMLElement("CheckDigit", doc.uri_check_digit)), XMLElement("DocCategory", doc.doc_category), XMLElement("SequenceName", doc.doc_sequence_name), XMLElement("SequenceValue", doc.doc_sequence_value)), XMLElement("PONumber", doc.po_number), XMLElement("DocumentStatus", XMLElement("Code", doc.document_status), XMLElement("Meaning", doc.document_status_meaning)), IBY_FD_EXTRACT_GEN_PVT.Get_Doc_DocErrAgg(doc.document_payable_id), XMLElement("DocumentDate", doc.document_date), XMLElement("DocumentCreationDate", doc.document_creation_date), XMLElement("RoutingAttributes", XMLElement("RoutingAttribute1", doc.ROUTING_ATTRIBUTE1),XMLElement("RoutingAttribute2", doc.ROUTING_ATTRIBUTE2),XMLElement("RoutingAttribute3", doc.ROUTING_ATTRIBUTE3),XMLElement("RoutingAttribute4", doc.ROUTING_ATTRIBUTE4),XMLElement("RoutingAttribute5", doc.ROUTING_ATTRIBUTE5)), CASE WHEN doc.payment_due_date IS NOT NULL THEN XMLElement("PaymentDueDate", doc.payment_due_date) ELSE NULL END, IBY_FD_EXTRACT_GEN_PVT.Get_Doc_DD_Agg(doc.document_payable_id),XMLElement("DocumentType", XMLElement("Code", doc.document_type), XMLElement("Meaning", doc.document_type_meaning)), XMLElement("DocumentDescription", iby_utility_pvt.replace_bad_chars(doc.document_description)), XMLElement("ExpenseReportCreditCardNumber", doc.EXPENSE_RPT_CC_NUM), XMLElement("EmployeePaymentFlag", doc.employee_payment_flag), XMLElement("TotalDocumentAmount", XMLElement("Value", NVL(doc.document_amount, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))), XMLElement("PaymentAmount", XMLElement("Value", NVL(doc.payment_amount, 0)), XMLElement("Currency", XMLElement("Code", doc.payment_currency_code))), XMLElement("PaymentAmountinDocumentCurrency", XMLElement("Value", NVL(doc.doc_currency_payment_amount, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))),XMLElement("PayAloneFlag", doc.exclusive_payment_flag), XMLElement("SettlementPriority", XMLElement("Code", doc.settlement_priority), XMLElement("Meaning", doc.settlement_priority_meaning)), XMLElement("AmountWithheld", XMLElement("Value", NVL(doc.amount_withheld, 0)), XMLElement("Currency", XMLElement("Code", doc.payment_currency_code))), XMLElement("AmoutWithheldinDocumentCurrency", XMLElement("Value", NVL(doc.doc_curr_amount_withheld, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))), XMLElement("DiscountTaken", XMLElement("Amount", XMLElement("Value", NVL(doc.payment_curr_discount_taken, 0)), XMLElement("Currency", XMLElement("Code", doc.payment_currency_code))), XMLElement("DiscountDate", doc.discount_date)), XMLElement("DiscountTakeninDocumentCurrency", XMLElement("Amount", XMLElement("Value", NVL(doc.doc_curr_discount_taken, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code)))), XMLElement("BankCharges", XMLElement("BankChargeBearer", XMLElement("Code", doc.bank_charge_bearer), XMLElement("Meaning", doc.bank_charge_bearer_meaning))), XMLElement("DeliveryChannel", XMLElement("Code", doc.delivery_channel_code), XMLElement("Meaning", doc.delivery_channel_meaning), XMLElement("FormatValue", doc.delivery_channel_format_value)), XMLElement("PaymentReason", XMLElement("Code", doc.payment_reason_code), XMLElement("Meaning", doc.payment_reason_meaning), XMLElement("FormatValue", doc.payment_reason_format_value)), XMLElement("PaymentReasonComments", doc.payment_reason_comments), XMLElement("RemittanceMessage", doc.remittance_message1), XMLElement("RemittanceMessage", doc.remittance_message2), XMLElement("RemittanceMessage", doc.remittance_message3), XMLElement("Charge", XMLElement("TotalDocumentChargeAmount", XMLElement("Value", NVL(doc.document_curr_charge_amount, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code)))), XMLElement("TotalDocumentTaxAmount", XMLElement("Value", NVL(doc.DOCUMENT_CURRENCY_TAX_AMOUNT, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))), XMLElement("CreditAmountApplied", XMLElement("Value", NVL(doc.BORDERO_ABATEMENT, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))), XMLElement("InterestAmountApplied", XMLElement("Value", NVL(doc.BORDERO_INT_AMT, 0)), XMLElement("Currency", XMLElement("Code", doc.document_currency_code))), XMLElement("InterestRate", NVL(doc.interest_rate, 0)), decode(fndapl.application_short_name, 'AP', IBY_FD_EXTRACT_GEN_PVT.Get_Doc_DocLineAgg(doc.document_payable_id), null), IBY_FD_EXTRACT_EXT_PUB.Get_Doc_Ext_Agg(doc.document_payable_id), IBY_FD_EXTRACT_GEN_PVT.Get_Doc_BR_Ext_Agg(doc.document_payable_id), XMLConcat(XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_DOCS_PAYABLE_ALL', doc.document_payable_id, NULL)),XMLElement("GlobalDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Gdfs( 'IBY_DOCS_PAYABLE_ALL', doc.document_payable_id, NULL))), XMLElement("SrcDocDescriptiveFlexField", decode(fndapl.application_short_name, 'AP', IBY_EXTRACTGEN_PVT.Get_Dffs('AP_DOCUMENTS_PAYABLE', doc.document_payable_id, NULL), null)) ,XMLElement("SrcDocGlobalDescriptiveFlexField", DECODE(fndapl.application_short_name, 'AP', IBY_EXTRACTGEN_PVT.Get_Gdfs('AP_DOCUMENTS_PAYABLE', doc.document_payable_id, NULL), NULL))), doc.payment_id PAYMENT_ID ,doc.formatting_payment_id formatting_payment_id, doc.payment_service_request_id, doc.document_payable_id, doc.document_status FROM iby_ext_fd_doc_1_0_v doc, fnd_application fndapl WHERE doc.calling_app_id = fndapl.application_id