IBY_EXT_FD_DOC_1_0

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
PAYMENT_ID DOCUMENT_PAYABLE_ID PAYMENT_SERVICE_REQUEST_ID CALLING_APP_DOC_UNIQUE_REF1 CALLING_APP_DOC_UNIQUE_REF2 CALLING_APP_DOC_UNIQUE_REF3 CALLING_APP_DOC_UNIQUE_REF4 CALLING_APP_DOC_UNIQUE_REF5 CALLING_APP_ID PAY_PROC_TRXN_TYPE_CODE CALLING_APP_DOCUMENT_UNIQUE_ID CALLING_APP_DOC_REF_NUMBER BANK_ASSIGNED_REF_CODE UNIQUE_REMITTANCE_IDENTIFIER URI_CHECK_DIGIT DOC_CATEGORY DOC_SEQUENCE_NAME DOC_SEQUENCE_VALUE PO_NUMBER DOCUMENT_STATUS DOCUMENT_STATUS_MEANING DOCUMENT_DATE DOCUMENT_CREATION_DATE PAYMENT_DUE_DATE DOCUMENT_TYPE DOCUMENT_TYPE_MEANING DOCUMENT_DESCRIPTION DOCUMENT_AMOUNT DOCUMENT_CURRENCY_CODE PAYMENT_AMOUNT PAYMENT_CURRENCY_CODE DISCOUNT_DATE PAYMENT_CURR_DISCOUNT_TAKEN DOCUMENT_CURR_CHARGE_AMOUNT INTEREST_RATE BANK_CHARGE_BEARER BANK_CHARGE_BEARER_MEANING EXCLUSIVE_PAYMENT_FLAG DELIVERY_CHANNEL_CODE DELIVERY_CHANNEL_MEANING DELIVERY_CHANNEL_FORMAT_VALUE SETTLEMENT_PRIORITY SETTLEMENT_PRIORITY_MEANING AMOUNT_WITHHELD PAYMENT_REASON_CODE PAYMENT_REASON_MEANING PAYMENT_REASON_FORMAT_VALUE PAYMENT_REASON_COMMENTS REMITTANCE_MESSAGE1 REMITTANCE_MESSAGE2 REMITTANCE_MESSAGE3 ROUTING_ATTRIBUTE1 ROUTING_ATTRIBUTE2 ROUTING_ATTRIBUTE3 ROUTING_ATTRIBUTE4 ROUTING_ATTRIBUTE5 DOC_PAYABLE_ATTR_CAT FORMATTING_PAYMENT_ID EMPLOYEE_PAYMENT_FLAG BORDERO_INT_AMT BORDERO_ABATEMENT DOCUMENT_CURRENCY_TAX_AMOUNT EXPENSE_RPT_CC_NUM INVOICING_ORG_ID INVOICING_ORG_TYPE INVOICING_ORG_TYPE_MEANING INVOICING_ORG_NAME CREATED_BY CREATION_DATE LAST_UPDATE_DATE LAST_UPDATE_LOGIN LAST_UPDATED_BY DOC_CURRENCY_PAYMENT_AMOUNT DOC_CURR_AMOUNT_WITHHELD DOC_CURR_DISCOUNT_TAKEN

Name

PAYMENT_ID

DOCUMENT_PAYABLE_ID

PAYMENT_SERVICE_REQUEST_ID

CALLING_APP_DOC_UNIQUE_REF1

CALLING_APP_DOC_UNIQUE_REF2

CALLING_APP_DOC_UNIQUE_REF3

CALLING_APP_DOC_UNIQUE_REF4

CALLING_APP_DOC_UNIQUE_REF5

CALLING_APP_ID

PAY_PROC_TRXN_TYPE_CODE

CALLING_APP_DOCUMENT_UNIQUE_ID

CALLING_APP_DOC_REF_NUMBER

BANK_ASSIGNED_REF_CODE

UNIQUE_REMITTANCE_IDENTIFIER

URI_CHECK_DIGIT

DOC_CATEGORY

DOC_SEQUENCE_NAME

DOC_SEQUENCE_VALUE

PO_NUMBER

DOCUMENT_STATUS

DOCUMENT_STATUS_MEANING

DOCUMENT_DATE

DOCUMENT_CREATION_DATE

PAYMENT_DUE_DATE

DOCUMENT_TYPE

DOCUMENT_TYPE_MEANING

DOCUMENT_DESCRIPTION

DOCUMENT_AMOUNT

DOCUMENT_CURRENCY_CODE

PAYMENT_AMOUNT

PAYMENT_CURRENCY_CODE

DISCOUNT_DATE

PAYMENT_CURR_DISCOUNT_TAKEN

DOCUMENT_CURR_CHARGE_AMOUNT

INTEREST_RATE

BANK_CHARGE_BEARER

BANK_CHARGE_BEARER_MEANING

EXCLUSIVE_PAYMENT_FLAG

DELIVERY_CHANNEL_CODE

DELIVERY_CHANNEL_MEANING

DELIVERY_CHANNEL_FORMAT_VALUE

SETTLEMENT_PRIORITY

SETTLEMENT_PRIORITY_MEANING

AMOUNT_WITHHELD

PAYMENT_REASON_CODE

PAYMENT_REASON_MEANING

PAYMENT_REASON_FORMAT_VALUE

PAYMENT_REASON_COMMENTS

REMITTANCE_MESSAGE1

REMITTANCE_MESSAGE2

REMITTANCE_MESSAGE3

ROUTING_ATTRIBUTE1

ROUTING_ATTRIBUTE2

ROUTING_ATTRIBUTE3

ROUTING_ATTRIBUTE4

ROUTING_ATTRIBUTE5

DOC_PAYABLE_ATTR_CAT

FORMATTING_PAYMENT_ID

EMPLOYEE_PAYMENT_FLAG

BORDERO_INT_AMT

BORDERO_ABATEMENT

DOCUMENT_CURRENCY_TAX_AMOUNT

EXPENSE_RPT_CC_NUM

INVOICING_ORG_ID

INVOICING_ORG_TYPE

INVOICING_ORG_TYPE_MEANING

INVOICING_ORG_NAME

CREATED_BY

CREATION_DATE

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

LAST_UPDATED_BY

DOC_CURRENCY_PAYMENT_AMOUNT

DOC_CURR_AMOUNT_WITHHELD

DOC_CURR_DISCOUNT_TAKEN

Query

SQL_Statement
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 CALLING_APP_DOCUMENT_UNIQUE_ID, doc.calling_app_doc_ref_number, NVL(doc.bank_assigned_ref_code, NULL /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.ROUTING_ATTRIBUTE1, doc.ROUTING_ATTRIBUTE2, doc.ROUTING_ATTRIBUTE3, doc.ROUTING_ATTRIBUTE4, doc.ROUTING_ATTRIBUTE5, 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, 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.invoicing_org_id, doc.invoicing_org_type, org_type_lookup.meaning, hr.name, doc.created_by, doc.creation_date, doc.last_update_date, doc.last_update_login, doc.last_updated_by, doc.doc_currency_payment_amount, doc.DOC_CURR_AMOUNT_WITHHELD, doc.DOC_CURR_DISCOUNT_TAKEN FROM iby_docs_payable_all doc, fnd_document_sequences fnd_doc_seq, iby_payment_codes_vl delivery_channels, iby_payment_codes_vl payment_reasons, fnd_lookups document_status_lookup, fnd_lookups document_type_lookup, fnd_lookups settlement_priority_lookup, fnd_lookups bank_charge_bearer_lookup, fnd_lookups org_type_lookup, hr_all_organization_units_vl hr WHERE doc.document_sequence_id = fnd_doc_seq.doc_sequence_id(+) AND doc.delivery_channel_code = delivery_channels.payment_code(+) AND delivery_channels.payment_code_type(+) = 'DELIVERY_CHANNEL' AND doc.payment_reason_code = payment_reasons.payment_code(+) AND payment_reasons.payment_code_type(+) = 'PAYMENT_REASON' 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 doc.invoicing_org_id = hr.organization_id AND doc.invoicing_org_type = org_type_lookup.lookup_code AND org_type_lookup.lookup_type = 'IBY_ORGANIZATION_TYPES' 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.ROUTING_ATTRIBUTE1, doc.ROUTING_ATTRIBUTE2, doc.ROUTING_ATTRIBUTE3, doc.ROUTING_ATTRIBUTE4, doc.ROUTING_ATTRIBUTE5, 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.invoicing_org_id, doc.invoicing_org_type, org_type_lookup.meaning, hr.name, doc.created_by, doc.creation_date, doc.last_update_date, doc.last_update_login, doc.last_updated_by, doc.doc_currency_payment_amount, doc.DOC_CURR_AMOUNT_WITHHELD, doc.DOC_CURR_DISCOUNT_TAKEN FROM iby_docs_payable_all doc, fnd_lookups document_status_lookup, fnd_lookups org_type_lookup, hr_all_organization_units_vl hr 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 doc.invoicing_org_id = hr.organization_id(+) AND doc.invoicing_org_type = org_type_lookup.lookup_code(+) AND org_type_lookup.lookup_type(+) = 'IBY_ORGANIZATION_TYPES'

SQL_Statement

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 CALLING_APP_DOCUMENT_UNIQUE_ID,

doc.calling_app_doc_ref_number,

NVL(doc.bank_assigned_ref_code, NULL

/*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.ROUTING_ATTRIBUTE1,

doc.ROUTING_ATTRIBUTE2,

doc.ROUTING_ATTRIBUTE3,

doc.ROUTING_ATTRIBUTE4,

doc.ROUTING_ATTRIBUTE5,

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,

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.invoicing_org_id,

doc.invoicing_org_type,

org_type_lookup.meaning,

hr.name,

doc.created_by,

doc.creation_date,

doc.last_update_date,

doc.last_update_login,

doc.last_updated_by,

doc.doc_currency_payment_amount,

doc.DOC_CURR_AMOUNT_WITHHELD,

doc.DOC_CURR_DISCOUNT_TAKEN

FROM iby_docs_payable_all doc,

fnd_document_sequences fnd_doc_seq,

iby_payment_codes_vl delivery_channels,

iby_payment_codes_vl payment_reasons,

fnd_lookups document_status_lookup,

fnd_lookups document_type_lookup,

fnd_lookups settlement_priority_lookup,

fnd_lookups bank_charge_bearer_lookup,

fnd_lookups org_type_lookup,

hr_all_organization_units_vl hr

WHERE doc.document_sequence_id = fnd_doc_seq.doc_sequence_id(+)

AND doc.delivery_channel_code = delivery_channels.payment_code(+)

AND delivery_channels.payment_code_type(+) = 'DELIVERY_CHANNEL'

AND doc.payment_reason_code = payment_reasons.payment_code(+)

AND payment_reasons.payment_code_type(+) = 'PAYMENT_REASON'

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 doc.invoicing_org_id = hr.organization_id

AND doc.invoicing_org_type = org_type_lookup.lookup_code

AND org_type_lookup.lookup_type = 'IBY_ORGANIZATION_TYPES'

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.ROUTING_ATTRIBUTE1,

doc.ROUTING_ATTRIBUTE2,

doc.ROUTING_ATTRIBUTE3,

doc.ROUTING_ATTRIBUTE4,

doc.ROUTING_ATTRIBUTE5,

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.invoicing_org_id,

doc.invoicing_org_type,

org_type_lookup.meaning,

hr.name,

doc.created_by,

doc.creation_date,

doc.last_update_date,

doc.last_update_login,

doc.last_updated_by,

doc.doc_currency_payment_amount,

doc.DOC_CURR_AMOUNT_WITHHELD,

doc.DOC_CURR_DISCOUNT_TAKEN

FROM iby_docs_payable_all doc,

fnd_lookups document_status_lookup,

fnd_lookups org_type_lookup,

hr_all_organization_units_vl hr

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 doc.invoicing_org_id = hr.organization_id(+)

AND doc.invoicing_org_type = org_type_lookup.lookup_code(+)

AND org_type_lookup.lookup_type(+) = 'IBY_ORGANIZATION_TYPES'