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 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, CASE WHEN due_date_treatment_code = 'NO_CHANGE' THEN due_date WHEN due_date_treatment_code = 'START_DATE' THEN start_date WHEN due_date_treatment_code = 'END_DATE' THEN end_date ELSE LEAST(GREATEST(start_date, due_date), end_date) END 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 FROM( 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, CASE WHEN p.stub_flag <> 'N' OR h.payment_frequency_code = 'ONE_TIME' THEN p.payment_line_start_date WHEN h.use_first_recurring_line_flag = 'Y' THEN ADD_MONTHS(p.first_recurring_start_date, ((p.cumulative_num_of_payments - p.no_of_payments) + (r.column_value - 1)) * DECODE( h.payment_frequency_code,'QUARTERLY',3, 'SEMI_ANNUALLY',6,'ANNUALLY',12,1)) ELSE ADD_MONTHS(payment_line_start_date, (r.column_value - 1) * DECODE( h.payment_frequency_code,'QUARTERLY',3, 'SEMI_ANNUALLY',6,'ANNUALLY',12,1)) END start_date, CASE WHEN p.stub_flag <> 'N' OR h.payment_frequency_code = 'ONE_TIME' THEN p.payment_line_end_date WHEN h.use_first_recurring_line_flag = 'Y' THEN ADD_MONTHS(p.first_recurring_start_date, ((p.cumulative_num_of_payments - p.no_of_payments) + (r.column_value)) * DECODE( h.payment_frequency_code,'QUARTERLY',3, 'SEMI_ANNUALLY',6,'ANNUALLY',12,1)) - 1 ELSE ADD_MONTHS(payment_line_start_date, (r.column_value) * DECODE( h.payment_frequency_code,'QUARTERLY',3, 'SEMI_ANNUALLY',6,'ANNUALLY',12,1))-1 END 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, h.payment_frequency_code, p.no_of_payments, CASE WHEN p.stub_flag <> 'N' OR h.payment_frequency_code = 'ONE_TIME' OR h.use_first_recurring_line_flag = 'N' THEN 'NO_CHANGE' WHEN p.first_payment_date = payment_line_start_date THEN 'START_DATE' WHEN p.first_payment_date = ADD_MONTHS(p.first_recurring_start_date, ((p.cumulative_num_of_payments - p.no_of_payments) + 1) * DECODE( h.payment_frequency_code,'QUARTERLY',3, 'SEMI_ANNUALLY',6,'ANNUALLY',12,1)) - 1 THEN 'END_DATE' ELSE 'LEAST' END due_date_treatment_code 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')