FLA_PAYMENT_ITEMS_GENERATOR

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 CONTINGENT_AMOUNT AGREEMENT_NUMBER ASSESSMENT_DATE PAYMENT_INCREASE_FLAG CUMULATIVE_INCREASE_FLAG LINE_TYPE SCHEDULE_TYPE_CODE BILL_TO_CUSTOMER_ID BILL_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID

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

CONTINGENT_AMOUNT

AGREEMENT_NUMBER

ASSESSMENT_DATE

PAYMENT_INCREASE_FLAG

CUMULATIVE_INCREASE_FLAG

LINE_TYPE

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, due_date, start_date, end_date, payment_item_for_stub_flag, approval_status_code, option_flag, contingent_amount, agreement_number, assessment_date, payment_increase_flag, cumulative_increase_flag, line_type, 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, d.payment_number, d.payment_line_num, d.assessable_value, d.version_num, NVL(d.vendor_id, -1) vendor_id, NVL(d.vendor_site_id, -1) vendor_site_id, d.currency_code, DECODE(r.column_value,1,d.estimated_amount,2,fil.cum_basis_increase_amount,3,fil.increase_amount) AS estimated_amount, DECODE(r.column_value,1,d.actual_amount,2,fil.cum_basis_increase_amount,3,fil.increase_amount) AS actual_amount, d.due_date, d.start_date, d.end_date, d.payment_item_for_stub_flag, d.approval_status_code, d.option_flag, DECODE(r.column_value,1,0,2,fil.cumulative_contingent_amount,3,fil.contingent_amount) AS contingent_amount, DECODE(r.column_value,1,NULL,fil.agreement_number) AS agreement_number, DECODE(r.column_value,1,TO_DATE(NULL),fil.assessment_date) AS assessment_date, DECODE(r.column_value,1,'N','Y') AS payment_increase_flag, DECODE(r.column_value,2,'Y','N') AS cumulative_increase_flag, DECODE(r.column_value,1,'R',2,'C',3,'I') AS line_type, d.schedule_type_code, d.bill_to_customer_id, d.bill_to_site_use_id, d.ship_to_site_use_id FROM fla_lease_payment_headers ph, fla_draft_payment_items_v d, fla_lease_increment_lines fil, TABLE(fla_lease_utils_pkg.generate_rows( CASE WHEN (fil.lease_increment_line_id IS NULL OR d.payment_item_for_stub_flag <> 'N') THEN 1 ELSE 3 END)) r WHERE ph.payment_number = d.payment_number AND ph.lease_id = d.lease_id AND ph.lease_detail_id = d.lease_detail_id AND d.lease_id = fil.lease_id (+) AND d.lease_detail_id = fil.lease_detail_id (+) AND d.payment_number = fil.payment_number(+) AND d.due_date BETWEEN fil.increase_line_start_date(+) AND fil.increase_line_end_date(+) AND fil.change_type_code(+) <> 'DELETE' AND ph.schedules_impact_flag = 'Y') WHERE NVL(actual_amount, estimated_amount) <> 0 OR NVL(contingent_amount,0) <> 0

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,

due_date,

start_date,

end_date,

payment_item_for_stub_flag,

approval_status_code,

option_flag,

contingent_amount,

agreement_number,

assessment_date,

payment_increase_flag,

cumulative_increase_flag,

line_type,

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,

d.payment_number,

d.payment_line_num,

d.assessable_value,

d.version_num,

NVL(d.vendor_id, -1) vendor_id,

NVL(d.vendor_site_id, -1) vendor_site_id,

d.currency_code,

DECODE(r.column_value,1,d.estimated_amount,2,fil.cum_basis_increase_amount,3,fil.increase_amount) AS estimated_amount,

DECODE(r.column_value,1,d.actual_amount,2,fil.cum_basis_increase_amount,3,fil.increase_amount) AS actual_amount,

d.due_date,

d.start_date,

d.end_date,

d.payment_item_for_stub_flag,

d.approval_status_code,

d.option_flag,

DECODE(r.column_value,1,0,2,fil.cumulative_contingent_amount,3,fil.contingent_amount) AS contingent_amount,

DECODE(r.column_value,1,NULL,fil.agreement_number) AS agreement_number,

DECODE(r.column_value,1,TO_DATE(NULL),fil.assessment_date) AS assessment_date,

DECODE(r.column_value,1,'N','Y') AS payment_increase_flag,

DECODE(r.column_value,2,'Y','N') AS cumulative_increase_flag,

DECODE(r.column_value,1,'R',2,'C',3,'I') AS line_type,

d.schedule_type_code,

d.bill_to_customer_id,

d.bill_to_site_use_id,

d.ship_to_site_use_id

FROM fla_lease_payment_headers ph,

fla_draft_payment_items_v d,

fla_lease_increment_lines fil,

TABLE(fla_lease_utils_pkg.generate_rows( CASE WHEN (fil.lease_increment_line_id IS NULL OR d.payment_item_for_stub_flag <> 'N') THEN 1 ELSE 3 END)) r

WHERE ph.payment_number = d.payment_number

AND ph.lease_id = d.lease_id

AND ph.lease_detail_id = d.lease_detail_id

AND d.lease_id = fil.lease_id (+)

AND d.lease_detail_id = fil.lease_detail_id (+)

AND d.payment_number = fil.payment_number(+)

AND d.due_date BETWEEN fil.increase_line_start_date(+) AND fil.increase_line_end_date(+)

AND fil.change_type_code(+) <> 'DELETE'

AND ph.schedules_impact_flag = 'Y')

WHERE NVL(actual_amount, estimated_amount) <> 0 OR NVL(contingent_amount,0) <> 0