FLA_DRAFT_PAYMENT_ITEMS_V

Details

• Schema: FUSION

• Object owner: FUN

• Object type: VIEW

Columns

Name
LEASE_ID LEASE_DETAIL_ID PAYMENT_NUMBER PAYMENT_LINE_NUM ASSESSABLE_VALUE VERSION_NUM VENDOR_ID VENDOR_SITE_ID CURRENCY_CODE ESTIMATED_AMOUNT ACTUAL_AMOUNT DUE_DATE START_DATE END_DATE PAYMENT_ITEM_FOR_STUB_FLAG APPROVAL_STATUS_CODE OPTION_FLAG SCHEDULE_TYPE_CODE BILL_TO_CUSTOMER_ID BILL_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID

Query

SQL_Statement

SELECT d.lease_id, d.lease_detail_id, h.payment_number, p.payment_line_num, p.assessable_value, d.version_num, NVL(h.vendor_id, -1) vendor_id, NVL(h.vendor_site_id, -1) vendor_site_id, h.currency_code, NVL(p.actual_amount, p.estimated_amount) estimated_amount, NVL(p.actual_amount, p.estimated_amount) actual_amount, DECODE(p.stub_flag, 'N', DECODE( h.payment_frequency_code, 'ONE_TIME', first_payment_date, ADD_MONTHS(first_payment_date, (r.column_value - 1) * DECODE( h.payment_frequency_code,'QUARTERLY',3, 'SEMI_ANNUALLY',6,'ANNUALLY',12,1)) ), first_payment_date ) due_date, DECODE(p.stub_flag, 'N', DECODE(h.payment_frequency_code, 'ONE_TIME', p.payment_line_start_date, ADD_MONTHS(payment_line_start_date, (r.column_value - 1) * DECODE( h.payment_frequency_code,'QUARTERLY',3, 'SEMI_ANNUALLY',6,'ANNUALLY',12,1)) ), p.payment_line_start_date ) start_date, DECODE(p.stub_flag, 'N', DECODE(h.payment_frequency_code, 'ONE_TIME', p.payment_line_end_date, ADD_MONTHS(payment_line_start_date, (r.column_value) * DECODE( h.payment_frequency_code,'QUARTERLY',3, 'SEMI_ANNUALLY',6,'ANNUALLY',12,1))-1 ), p.payment_line_end_date ) end_date, stub_flag AS payment_item_for_stub_flag, 'DRAFT' AS approval_status_code, h.option_flag, h.schedule_type_code, h.bill_to_customer_id, h.bill_to_site_use_id, h.ship_to_site_use_id FROM fla_lease_details d, fla_lease_payment_headers h, fla_lease_payment_lines p, TABLE(fla_lease_utils_pkg.generate_rows(NVL(p.no_of_payments,1))) r WHERE d.lease_id = h.lease_id AND d.lease_detail_id = h.lease_detail_id AND h.lease_id = p.lease_id AND h.lease_detail_id = p.lease_detail_id AND h.payment_number = p.payment_number AND d.version_type_code = 'D' AND h.option_execution_date IS NULL AND (h.termination_date IS NULL OR h.termination_date >= h.payment_start_date) AND h.change_type_code <> 'DELETE' AND p.change_type_code <> 'DELETE'