VIEW: APPS.IBY_EXT_FD_DOC_1_0_VT

Source

View Text - Preformatted

SELECT doc.payment_id, doc.document_payable_id, doc.payment_service_request_id, doc.calling_app_doc_unique_ref1, doc.calling_app_doc_unique_ref2, doc.calling_app_doc_unique_ref3, doc.calling_app_doc_unique_ref4, doc.calling_app_doc_unique_ref5, doc.calling_app_id, doc.pay_proc_trxn_type_code, doc.calling_app_doc_unique_ref1, doc.calling_app_doc_ref_number, nvl(doc.bank_assigned_ref_code, IBY_FD_EXTRACT_GEN_PVT.Get_Bordero_Bank_Ref(doc.document_payable_id)), doc.unique_remittance_identifier, doc.uri_check_digit, doc.document_category_code, fnd_doc_seq.name, doc.document_sequence_value, doc.po_number, doc.document_status, document_status_lookup.meaning, doc.document_date, doc.creation_date, doc.payment_due_date, doc.document_type, document_type_lookup.meaning, doc.document_description, doc.document_amount, doc.document_currency_code, doc.payment_amount, doc.payment_currency_code, doc.discount_date, doc.payment_curr_discount_taken, doc.document_curr_charge_amount, doc.interest_rate, doc.bank_charge_bearer, bank_charge_bearer_lookup.meaning, doc.exclusive_payment_flag, doc.delivery_channel_code, delivery_channels.meaning, delivery_channels.format_value, doc.settlement_priority, settlement_priority_lookup.meaning, doc.amount_withheld, doc.payment_reason_code, payment_reasons.meaning, payment_reasons.format_value, payment_reason_comments, doc.remittance_message1, doc.remittance_message2, doc.remittance_message3, doc.attribute_category, doc.formatting_payment_id, doc.employee_payment_flag, IBY_FD_EXTRACT_GEN_PVT.Get_Bordero_Int_Amt(doc.document_payable_id), IBY_FD_EXTRACT_GEN_PVT.Get_Bordero_Abatement(doc.document_payable_id), doc.DOCUMENT_CURRENCY_TAX_AMOUNT, decode(doc.document_type, 'EXPENSE REPORT', IBY_FD_EXTRACT_GEN_PVT.Get_Expense_Rpt_CC_Num(doc.document_payable_id), 'MIXED', IBY_FD_EXTRACT_GEN_PVT.Get_Expense_Rpt_CC_Num(doc.document_payable_id), null), doc.GLOBAL_ATTRIBUTE_CATEGORY, doc.GLOBAL_ATTRIBUTE1, doc.GLOBAL_ATTRIBUTE2, doc.GLOBAL_ATTRIBUTE3, doc.GLOBAL_ATTRIBUTE4, doc.GLOBAL_ATTRIBUTE5, doc.GLOBAL_ATTRIBUTE6, doc.GLOBAL_ATTRIBUTE7, doc.GLOBAL_ATTRIBUTE8, doc.GLOBAL_ATTRIBUTE9, doc.GLOBAL_ATTRIBUTE10, doc.GLOBAL_ATTRIBUTE11, doc.GLOBAL_ATTRIBUTE12, doc.GLOBAL_ATTRIBUTE13, doc.GLOBAL_ATTRIBUTE14, doc.GLOBAL_ATTRIBUTE15, doc.GLOBAL_ATTRIBUTE16, doc.GLOBAL_ATTRIBUTE17, doc.GLOBAL_ATTRIBUTE18, doc.GLOBAL_ATTRIBUTE19, doc.GLOBAL_ATTRIBUTE20, le_info.party_legal_name, le_info.party_registration_number, IBY_FD_EXTRACT_GEN_PVT.Get_FP_TaxRegistration(doc.legal_entity_id) FROM iby_docs_payable_all doc, fnd_document_sequences fnd_doc_seq, iby_delivery_channels_vl delivery_channels, iby_payment_reasons_vl payment_reasons, fnd_lookups document_status_lookup, fnd_lookups document_type_lookup, fnd_lookups settlement_priority_lookup, fnd_lookups bank_charge_bearer_lookup, IBY_PP_FIRST_PARTY_V le_info where doc.document_sequence_id = fnd_doc_seq.doc_sequence_id(+) AND doc.delivery_channel_code = delivery_channels.delivery_channel_code(+) AND doc.payment_reason_code = payment_reasons.payment_reason_code(+) AND doc.document_status = document_status_lookup.lookup_code AND document_status_lookup.lookup_type = 'IBY_DOCS_PAYABLE_STATUSES' AND doc.document_type = document_type_lookup.lookup_code AND document_type_lookup.lookup_type = 'IBY_DOCUMENT_TYPES' AND doc.settlement_priority = settlement_priority_lookup.lookup_code(+) AND settlement_priority_lookup.lookup_type(+) = 'IBY_SETTLEMENT_PRIORITY' AND doc.bank_charge_bearer = bank_charge_bearer_lookup.lookup_code(+) AND bank_charge_bearer_lookup.lookup_type(+) = 'IBY_BANK_CHARGE_BEARER' AND doc.document_status NOT IN ('VOID_BY_SETUP', 'VOID_BY_OVERFLOW') AND le_info.party_legal_id(+) = doc.legal_entity_id union all select doc.payment_id, doc.document_payable_id, doc.payment_service_request_id, doc.calling_app_doc_unique_ref1, doc.calling_app_doc_unique_ref2, doc.calling_app_doc_unique_ref3, doc.calling_app_doc_unique_ref4, doc.calling_app_doc_unique_ref5, doc.calling_app_id, doc.pay_proc_trxn_type_code, doc.calling_app_doc_unique_ref1, doc.calling_app_doc_ref_number, null, /* nvl(doc.bank_assigned_ref_code, IBY_FD_EXTRACT_GEN_PVT.Get_Bordero_Bank_Ref(doc.document_payable_id)), */ doc.unique_remittance_identifier, doc.uri_check_digit, doc.document_category_code, null, /* fnd_doc_seq.name, */ doc.document_sequence_value, doc.po_number, doc.document_status, document_status_lookup.meaning, doc.document_date, doc.creation_date, doc.payment_due_date, doc.document_type, null, /* document_type_lookup.meaning, */ doc.document_description, doc.document_amount, doc.document_currency_code, doc.payment_amount, doc.payment_currency_code, doc.discount_date, doc.payment_curr_discount_taken, doc.document_curr_charge_amount, doc.interest_rate, doc.bank_charge_bearer, null, /* bank_charge_bearer_lookup.meaning, */ doc.exclusive_payment_flag, doc.delivery_channel_code, null, /* delivery_channels.meaning, */ null, /* delivery_channels.format_value, */ doc.settlement_priority, null, /* settlement_priority_lookup.meaning, */ doc.amount_withheld, doc.payment_reason_code, null, /* payment_reasons.meaning, */ null, /* payment_reasons.format_value, */ doc.payment_reason_comments, doc.remittance_message1, doc.remittance_message2, doc.remittance_message3, doc.attribute_category, doc.formatting_payment_id, doc.employee_payment_flag, null, /* IBY_FD_EXTRACT_GEN_PVT.Get_Bordero_Int_Amt(doc.document_payable_id), */ null, /* IBY_FD_EXTRACT_GEN_PVT.Get_Bordero_Abatement(doc.document_payable_id) */ doc.DOCUMENT_CURRENCY_TAX_AMOUNT, null, doc.GLOBAL_ATTRIBUTE_CATEGORY, doc.GLOBAL_ATTRIBUTE1, doc.GLOBAL_ATTRIBUTE2, doc.GLOBAL_ATTRIBUTE3, doc.GLOBAL_ATTRIBUTE4, doc.GLOBAL_ATTRIBUTE5, doc.GLOBAL_ATTRIBUTE6, doc.GLOBAL_ATTRIBUTE7, doc.GLOBAL_ATTRIBUTE8, doc.GLOBAL_ATTRIBUTE9, doc.GLOBAL_ATTRIBUTE10, doc.GLOBAL_ATTRIBUTE11, doc.GLOBAL_ATTRIBUTE12, doc.GLOBAL_ATTRIBUTE13, doc.GLOBAL_ATTRIBUTE14, doc.GLOBAL_ATTRIBUTE15, doc.GLOBAL_ATTRIBUTE16, doc.GLOBAL_ATTRIBUTE17, doc.GLOBAL_ATTRIBUTE18, doc.GLOBAL_ATTRIBUTE19, doc.GLOBAL_ATTRIBUTE20, le_info.party_legal_name, le_info.party_registration_number, IBY_FD_EXTRACT_GEN_PVT.Get_FP_TaxRegistration(doc.legal_entity_id) FROM iby_docs_payable_all doc, fnd_lookups document_status_lookup, IBY_PP_FIRST_PARTY_V le_info WHERE doc.document_status IN ('VOID_BY_SETUP', 'VOID_BY_OVERFLOW') AND doc.document_status = document_status_lookup.lookup_code AND document_status_lookup.lookup_type = 'IBY_DOCS_PAYABLE_STATUSES' AND le_info.party_legal_id(+) = doc.legal_entity_id

View Text - HTML Formatted

SELECT DOC.PAYMENT_ID
, DOC.DOCUMENT_PAYABLE_ID
, DOC.PAYMENT_SERVICE_REQUEST_ID
, DOC.CALLING_APP_DOC_UNIQUE_REF1
, DOC.CALLING_APP_DOC_UNIQUE_REF2
, DOC.CALLING_APP_DOC_UNIQUE_REF3
, DOC.CALLING_APP_DOC_UNIQUE_REF4
, DOC.CALLING_APP_DOC_UNIQUE_REF5
, DOC.CALLING_APP_ID
, DOC.PAY_PROC_TRXN_TYPE_CODE
, DOC.CALLING_APP_DOC_UNIQUE_REF1
, DOC.CALLING_APP_DOC_REF_NUMBER
, NVL(DOC.BANK_ASSIGNED_REF_CODE
, IBY_FD_EXTRACT_GEN_PVT.GET_BORDERO_BANK_REF(DOC.DOCUMENT_PAYABLE_ID))
, DOC.UNIQUE_REMITTANCE_IDENTIFIER
, DOC.URI_CHECK_DIGIT
, DOC.DOCUMENT_CATEGORY_CODE
, FND_DOC_SEQ.NAME
, DOC.DOCUMENT_SEQUENCE_VALUE
, DOC.PO_NUMBER
, DOC.DOCUMENT_STATUS
, DOCUMENT_STATUS_LOOKUP.MEANING
, DOC.DOCUMENT_DATE
, DOC.CREATION_DATE
, DOC.PAYMENT_DUE_DATE
, DOC.DOCUMENT_TYPE
, DOCUMENT_TYPE_LOOKUP.MEANING
, DOC.DOCUMENT_DESCRIPTION
, DOC.DOCUMENT_AMOUNT
, DOC.DOCUMENT_CURRENCY_CODE
, DOC.PAYMENT_AMOUNT
, DOC.PAYMENT_CURRENCY_CODE
, DOC.DISCOUNT_DATE
, DOC.PAYMENT_CURR_DISCOUNT_TAKEN
, DOC.DOCUMENT_CURR_CHARGE_AMOUNT
, DOC.INTEREST_RATE
, DOC.BANK_CHARGE_BEARER
, BANK_CHARGE_BEARER_LOOKUP.MEANING
, DOC.EXCLUSIVE_PAYMENT_FLAG
, DOC.DELIVERY_CHANNEL_CODE
, DELIVERY_CHANNELS.MEANING
, DELIVERY_CHANNELS.FORMAT_VALUE
, DOC.SETTLEMENT_PRIORITY
, SETTLEMENT_PRIORITY_LOOKUP.MEANING
, DOC.AMOUNT_WITHHELD
, DOC.PAYMENT_REASON_CODE
, PAYMENT_REASONS.MEANING
, PAYMENT_REASONS.FORMAT_VALUE
, PAYMENT_REASON_COMMENTS
, DOC.REMITTANCE_MESSAGE1
, DOC.REMITTANCE_MESSAGE2
, DOC.REMITTANCE_MESSAGE3
, DOC.ATTRIBUTE_CATEGORY
, DOC.FORMATTING_PAYMENT_ID
, DOC.EMPLOYEE_PAYMENT_FLAG
, IBY_FD_EXTRACT_GEN_PVT.GET_BORDERO_INT_AMT(DOC.DOCUMENT_PAYABLE_ID)
, IBY_FD_EXTRACT_GEN_PVT.GET_BORDERO_ABATEMENT(DOC.DOCUMENT_PAYABLE_ID)
, DOC.DOCUMENT_CURRENCY_TAX_AMOUNT
, DECODE(DOC.DOCUMENT_TYPE
, 'EXPENSE REPORT'
, IBY_FD_EXTRACT_GEN_PVT.GET_EXPENSE_RPT_CC_NUM(DOC.DOCUMENT_PAYABLE_ID)
, 'MIXED'
, IBY_FD_EXTRACT_GEN_PVT.GET_EXPENSE_RPT_CC_NUM(DOC.DOCUMENT_PAYABLE_ID)
, NULL)
, DOC.GLOBAL_ATTRIBUTE_CATEGORY
, DOC.GLOBAL_ATTRIBUTE1
, DOC.GLOBAL_ATTRIBUTE2
, DOC.GLOBAL_ATTRIBUTE3
, DOC.GLOBAL_ATTRIBUTE4
, DOC.GLOBAL_ATTRIBUTE5
, DOC.GLOBAL_ATTRIBUTE6
, DOC.GLOBAL_ATTRIBUTE7
, DOC.GLOBAL_ATTRIBUTE8
, DOC.GLOBAL_ATTRIBUTE9
, DOC.GLOBAL_ATTRIBUTE10
, DOC.GLOBAL_ATTRIBUTE11
, DOC.GLOBAL_ATTRIBUTE12
, DOC.GLOBAL_ATTRIBUTE13
, DOC.GLOBAL_ATTRIBUTE14
, DOC.GLOBAL_ATTRIBUTE15
, DOC.GLOBAL_ATTRIBUTE16
, DOC.GLOBAL_ATTRIBUTE17
, DOC.GLOBAL_ATTRIBUTE18
, DOC.GLOBAL_ATTRIBUTE19
, DOC.GLOBAL_ATTRIBUTE20
, LE_INFO.PARTY_LEGAL_NAME
, LE_INFO.PARTY_REGISTRATION_NUMBER
, IBY_FD_EXTRACT_GEN_PVT.GET_FP_TAXREGISTRATION(DOC.LEGAL_ENTITY_ID)
FROM IBY_DOCS_PAYABLE_ALL DOC
, FND_DOCUMENT_SEQUENCES FND_DOC_SEQ
, IBY_DELIVERY_CHANNELS_VL DELIVERY_CHANNELS
, IBY_PAYMENT_REASONS_VL PAYMENT_REASONS
, FND_LOOKUPS DOCUMENT_STATUS_LOOKUP
, FND_LOOKUPS DOCUMENT_TYPE_LOOKUP
, FND_LOOKUPS SETTLEMENT_PRIORITY_LOOKUP
, FND_LOOKUPS BANK_CHARGE_BEARER_LOOKUP
, IBY_PP_FIRST_PARTY_V LE_INFO
WHERE DOC.DOCUMENT_SEQUENCE_ID = FND_DOC_SEQ.DOC_SEQUENCE_ID(+)
AND DOC.DELIVERY_CHANNEL_CODE = DELIVERY_CHANNELS.DELIVERY_CHANNEL_CODE(+)
AND DOC.PAYMENT_REASON_CODE = PAYMENT_REASONS.PAYMENT_REASON_CODE(+)
AND DOC.DOCUMENT_STATUS = DOCUMENT_STATUS_LOOKUP.LOOKUP_CODE
AND DOCUMENT_STATUS_LOOKUP.LOOKUP_TYPE = 'IBY_DOCS_PAYABLE_STATUSES'
AND DOC.DOCUMENT_TYPE = DOCUMENT_TYPE_LOOKUP.LOOKUP_CODE
AND DOCUMENT_TYPE_LOOKUP.LOOKUP_TYPE = 'IBY_DOCUMENT_TYPES'
AND DOC.SETTLEMENT_PRIORITY = SETTLEMENT_PRIORITY_LOOKUP.LOOKUP_CODE(+)
AND SETTLEMENT_PRIORITY_LOOKUP.LOOKUP_TYPE(+) = 'IBY_SETTLEMENT_PRIORITY'
AND DOC.BANK_CHARGE_BEARER = BANK_CHARGE_BEARER_LOOKUP.LOOKUP_CODE(+)
AND BANK_CHARGE_BEARER_LOOKUP.LOOKUP_TYPE(+) = 'IBY_BANK_CHARGE_BEARER'
AND DOC.DOCUMENT_STATUS NOT IN ('VOID_BY_SETUP'
, 'VOID_BY_OVERFLOW')
AND LE_INFO.PARTY_LEGAL_ID(+) = DOC.LEGAL_ENTITY_ID UNION ALL SELECT DOC.PAYMENT_ID
, DOC.DOCUMENT_PAYABLE_ID
, DOC.PAYMENT_SERVICE_REQUEST_ID
, DOC.CALLING_APP_DOC_UNIQUE_REF1
, DOC.CALLING_APP_DOC_UNIQUE_REF2
, DOC.CALLING_APP_DOC_UNIQUE_REF3
, DOC.CALLING_APP_DOC_UNIQUE_REF4
, DOC.CALLING_APP_DOC_UNIQUE_REF5
, DOC.CALLING_APP_ID
, DOC.PAY_PROC_TRXN_TYPE_CODE
, DOC.CALLING_APP_DOC_UNIQUE_REF1
, DOC.CALLING_APP_DOC_REF_NUMBER
, NULL
, /* NVL(DOC.BANK_ASSIGNED_REF_CODE
, IBY_FD_EXTRACT_GEN_PVT.GET_BORDERO_BANK_REF(DOC.DOCUMENT_PAYABLE_ID))
, */ DOC.UNIQUE_REMITTANCE_IDENTIFIER
, DOC.URI_CHECK_DIGIT
, DOC.DOCUMENT_CATEGORY_CODE
, NULL
, /* FND_DOC_SEQ.NAME
, */ DOC.DOCUMENT_SEQUENCE_VALUE
, DOC.PO_NUMBER
, DOC.DOCUMENT_STATUS
, DOCUMENT_STATUS_LOOKUP.MEANING
, DOC.DOCUMENT_DATE
, DOC.CREATION_DATE
, DOC.PAYMENT_DUE_DATE
, DOC.DOCUMENT_TYPE
, NULL
, /* DOCUMENT_TYPE_LOOKUP.MEANING
, */ DOC.DOCUMENT_DESCRIPTION
, DOC.DOCUMENT_AMOUNT
, DOC.DOCUMENT_CURRENCY_CODE
, DOC.PAYMENT_AMOUNT
, DOC.PAYMENT_CURRENCY_CODE
, DOC.DISCOUNT_DATE
, DOC.PAYMENT_CURR_DISCOUNT_TAKEN
, DOC.DOCUMENT_CURR_CHARGE_AMOUNT
, DOC.INTEREST_RATE
, DOC.BANK_CHARGE_BEARER
, NULL
, /* BANK_CHARGE_BEARER_LOOKUP.MEANING
, */ DOC.EXCLUSIVE_PAYMENT_FLAG
, DOC.DELIVERY_CHANNEL_CODE
, NULL
, /* DELIVERY_CHANNELS.MEANING
, */ NULL
, /* DELIVERY_CHANNELS.FORMAT_VALUE
, */ DOC.SETTLEMENT_PRIORITY
, NULL
, /* SETTLEMENT_PRIORITY_LOOKUP.MEANING
, */ DOC.AMOUNT_WITHHELD
, DOC.PAYMENT_REASON_CODE
, NULL
, /* PAYMENT_REASONS.MEANING
, */ NULL
, /* PAYMENT_REASONS.FORMAT_VALUE
, */ DOC.PAYMENT_REASON_COMMENTS
, DOC.REMITTANCE_MESSAGE1
, DOC.REMITTANCE_MESSAGE2
, DOC.REMITTANCE_MESSAGE3
, DOC.ATTRIBUTE_CATEGORY
, DOC.FORMATTING_PAYMENT_ID
, DOC.EMPLOYEE_PAYMENT_FLAG
, NULL
, /* IBY_FD_EXTRACT_GEN_PVT.GET_BORDERO_INT_AMT(DOC.DOCUMENT_PAYABLE_ID)
, */ NULL
, /* IBY_FD_EXTRACT_GEN_PVT.GET_BORDERO_ABATEMENT(DOC.DOCUMENT_PAYABLE_ID) */ DOC.DOCUMENT_CURRENCY_TAX_AMOUNT
, NULL
, DOC.GLOBAL_ATTRIBUTE_CATEGORY
, DOC.GLOBAL_ATTRIBUTE1
, DOC.GLOBAL_ATTRIBUTE2
, DOC.GLOBAL_ATTRIBUTE3
, DOC.GLOBAL_ATTRIBUTE4
, DOC.GLOBAL_ATTRIBUTE5
, DOC.GLOBAL_ATTRIBUTE6
, DOC.GLOBAL_ATTRIBUTE7
, DOC.GLOBAL_ATTRIBUTE8
, DOC.GLOBAL_ATTRIBUTE9
, DOC.GLOBAL_ATTRIBUTE10
, DOC.GLOBAL_ATTRIBUTE11
, DOC.GLOBAL_ATTRIBUTE12
, DOC.GLOBAL_ATTRIBUTE13
, DOC.GLOBAL_ATTRIBUTE14
, DOC.GLOBAL_ATTRIBUTE15
, DOC.GLOBAL_ATTRIBUTE16
, DOC.GLOBAL_ATTRIBUTE17
, DOC.GLOBAL_ATTRIBUTE18
, DOC.GLOBAL_ATTRIBUTE19
, DOC.GLOBAL_ATTRIBUTE20
, LE_INFO.PARTY_LEGAL_NAME
, LE_INFO.PARTY_REGISTRATION_NUMBER
, IBY_FD_EXTRACT_GEN_PVT.GET_FP_TAXREGISTRATION(DOC.LEGAL_ENTITY_ID)
FROM IBY_DOCS_PAYABLE_ALL DOC
, FND_LOOKUPS DOCUMENT_STATUS_LOOKUP
, IBY_PP_FIRST_PARTY_V LE_INFO
WHERE DOC.DOCUMENT_STATUS IN ('VOID_BY_SETUP'
, 'VOID_BY_OVERFLOW')
AND DOC.DOCUMENT_STATUS = DOCUMENT_STATUS_LOOKUP.LOOKUP_CODE
AND DOCUMENT_STATUS_LOOKUP.LOOKUP_TYPE = 'IBY_DOCS_PAYABLE_STATUSES'
AND LE_INFO.PARTY_LEGAL_ID(+) = DOC.LEGAL_ENTITY_ID