FLA_LEASE_PAYMENT_ITEMS_V

Details

• Schema: FUSION

• Object owner: FUN

• Object type: VIEW

Columns

Name

PAYMENT_ITEM_ID LEASE_ID VERSION_TYPE_CODE LEASE_DETAIL_ID VERSION_NUM SRC_LEASE_DETAIL_ID SRC_VERSION_TYPE_CODE SRC_VERSION_NUM PAYMENT_NUMBER PAYMENT_LINE_NUM VENDOR_ID VENDOR_SITE_ID CURRENCY_CODE ESTIMATED_AMOUNT ACTUAL_AMOUNT ASSESSABLE_VALUE DUE_DATE START_DATE END_DATE PERIOD_NAME ACCOUNTED_FLAG ADJUSTMENT_FLAG REVERSAL_FLAG READ_IN_DRAFT_FLAG PAYMENT_ITEM_FOR_STUB_FLAG INVOICE_NUM INVOICE_LINE_NUMBER ESTIMATED_FUNC_CURR_AMOUNT ACTUAL_FUNC_CURR_AMOUNT FUNC_CURR_CONV_RATE FUNC_CURR_CONV_RATE_TYPE FUNC_CURR_CONV_DATE APPROVAL_STATUS_CODE APPROVAL_DATE APPROVED_BY COMMENTS INTERFACE_STATUS_CODE INTERFACE_DATE INVOICE_CREATION_STATUS_CODE PARENT_PAYMENT_ITEM_ID EXPORT_GROUP_CODE OPTION_FLAG REQUEST_ID DISTRIBUTION_SET_ID PAYMENT_TERM_ID PRIMARY_INTENDED_USE TAX_CLASSIFICATION_CODE TAX_INCLUDED_FLAG PRODUCT_CATEGORY PRODUCT_FISCAL_CLASSIFICATION PRODUCT_TYPE REPORT_FROM_INCEPTION_FLAG TRX_BUSINESS_CATEGORY USER_DEFINED_FISCAL_CLASS WITHHOLDING_TAX_GROUP_ID INTERCOMPANY_FLAG APPROVAL_BATCH_ID INTERFACE_ELIGIBILITY_CODE CHARGE_ACCOUNT_CCID LIABILITY_ACCOUNT_CCID ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE_NUMBER1 ATTRIBUTE_NUMBER2 ATTRIBUTE_NUMBER3 ATTRIBUTE_NUMBER4 ATTRIBUTE_NUMBER5 ATTRIBUTE_DATE1 ATTRIBUTE_DATE2 ATTRIBUTE_DATE3 ATTRIBUTE_DATE4 ATTRIBUTE_DATE5 SUPPLIER_INVOICE_REF_NUMBER CONTINGENT_AMOUNT AGREEMENT_NUMBER ASSESSMENT_DATE PAYMENT_INCREASE_FLAG CUMULATIVE_INCREASE_FLAG MANUAL_ACTION_CODE PAYMENT_TYPE_CODE PAYMENT_PURPOSE_CODE ASSET_NUMBER PAYMENT_TEMPLATE_ID RESPONSIBLE_USER_NAME REVENUE_METHOD_CODE SCHEDULE_TYPE_CODE BILL_TO_CUSTOMER_ID BILL_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID CUSTOMER_TRX_TYPE_ID AR_INVOICE_ID AR_INVOICE_LINE_ID DOCUMENT_SUB_TYPE FIRST_PTY_REG_ID THIRD_PTY_REG_ID TAX_TRANSACTION_ID PAYMENT_ITEM_TYPE_CODE TAX_RATE_ID PAYMENT_TAX_LINE_NUMBER INCLUDE_IN_ROU_FLAG

Query

SQL_Statement

SELECT /*+ leading(dv) index(pi FLA_PAYMENT_ITEMS_N2)*/ pi.payment_item_id, pi.lease_id, dv.version_type_code, dv.lease_detail_id lease_detail_id, dv.version_num version_num, pi.lease_detail_id src_lease_detail_id, piv.version_type_code src_version_type_code, pi.version_num src_version_num, pi.payment_number, pi.payment_line_num, pi.vendor_id, pi.vendor_site_id, pi.currency_code, pi.estimated_amount, pi.actual_amount, pi.assessable_value, pi.due_date, pi.start_date, pi.end_date, pi.period_name, pi.accounted_flag, pi.adjustment_flag, pi.reversal_flag, pi.read_in_draft_flag, pi.payment_item_for_stub_flag, pi.invoice_num, pi.invoice_line_number, pi.estimated_func_curr_amount, pi.actual_func_curr_amount, pi.func_curr_conv_rate, pi.func_curr_conv_rate_type, pi.func_curr_conv_date, pi.approval_status_code, pi.approval_date, pi.approved_by, pi.comments, pi.interface_status_code, pi.interface_date, pi.invoice_creation_status_code, pi.parent_payment_item_id, pi.export_group_code, pi.option_flag, pi.request_id, pi.distribution_set_id, pi.payment_term_id, pi.primary_intended_use, pi.tax_classification_code, pi.tax_included_flag, pi.product_category, pi.product_fiscal_classification, pi.product_type, pi.report_from_inception_flag, pi.trx_business_category, pi.user_defined_fiscal_class, pi.withholding_tax_group_id, pi.intercompany_flag, pi.approval_batch_id, pi.interface_eligibility_code, pi.charge_account_ccid, pi.liability_account_ccid, pi.attribute_category, pi.attribute1, pi.attribute2, pi.attribute3, pi.attribute4, pi.attribute5, pi.attribute6, pi.attribute7, pi.attribute8, pi.attribute9, pi.attribute10, pi.attribute11, pi.attribute12, pi.attribute13, pi.attribute14, pi.attribute15, pi.attribute_number1, pi.attribute_number2, pi.attribute_number3, pi.attribute_number4, pi.attribute_number5, pi.attribute_date1, pi.attribute_date2, pi.attribute_date3, pi.attribute_date4, pi.attribute_date5, pi.supplier_invoice_ref_number, NVL(pi.contingent_amount, 0) contingent_amount, pi.agreement_number, pi.assessment_date, NVL(pi.payment_increase_flag, 'N') payment_increase_flag, NVL(pi.cumulative_increase_flag, 'N') cumulative_increase_flag, pi.manual_action_code, NVL(pi.payment_type_code, 'R') payment_type_code, pi.payment_purpose_code, pi.asset_number, pi.payment_template_id, pi.responsible_user_name, pi.revenue_method_code, pi.schedule_type_code, pi.bill_to_customer_id, pi.bill_to_site_use_id, pi.ship_to_site_use_id, pi.customer_trx_type_id, pi.ar_invoice_id, pi.ar_invoice_line_id, pi.document_sub_type, pi.first_pty_reg_id, pi.third_pty_reg_id, pi.tax_transaction_id, pi.payment_item_type_code, pi.tax_transaction_id tax_rate_id, pi.payment_tax_line_number, CASE WHEN pi.payment_item_type_code = 'RECOVERABLE' THEN 'N' ELSE 'Y' END include_in_rou_flag from fla_lease_details dv, fla_lease_payment_headers ph, fla_payment_items pi, fla_lease_details piv where dv.lease_detail_id = ph.lease_detail_id and dv.lease_id = ph.lease_id and ph.payment_number = pi.payment_number and ph.lease_id = pi.lease_id and pi.lease_detail_id = piv.lease_detail_id and pi.lease_id = piv.lease_id and ( (dv.version_type_code = 'F' and dv.lease_detail_id = pi.lease_detail_id) OR (dv.version_type_code = 'D' AND ph.payment_items_impact_flag = 'N' AND piv.version_type_code = 'F') OR (dv.version_type_code = 'D' AND ph.payment_items_impact_flag = 'Y' AND piv.version_type_code = 'D') OR (dv.version_type_code = 'D' AND ph.payment_items_impact_flag = 'Y' AND piv.version_type_code = 'F' AND pi.due_date < dv.commencement_date))