FLA_PAYMENT_TAX_IMPACTS

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID LEASE_DETAIL_ID COMMENCEMENT_DATE PAYMENT_TAX_ID PAYMENT_NUMBER PAYMENT_TAX_LINE_NUMBER TAX TAX_REGIME_CODE TAX_JURISDICTION_CODE TAX_RATE_CODE TAX_RATE TAX_RATE_ID RECOVERY_TAX_RATE RECOVERY_TAX_RATE_ID PARENT_PAYMENT_TAX_ID PARENT_TAX_RATE PARENT_TAX_RATE_ID PARENT_RECOVERY_TAX_RATE PARENT_RECOVERY_TAX_RATE_ID PARENT_TAX_RATE_CODE PARENT_TAX_RATE_DATE PARENT_TAX_JURISDICTION_CODE PARENT_TAX_REGIME_CODE PARENT_TAX PARENT_RECOVERY_TAX_RATE_CODE PARENT_RECOVERY_RATE_DATE PARENT_RECOVERY_TYPE_CODE DV_TAX_ATTRIBUTES_CHANGED VERSION_NEEDS_TAXES TAX_PAYMENT_ITEMS_IMPACT_FLAG PAYMENT_ITEMS_IMPACT_FLAG TAX_SCHEDULES_IMPACT_FLAG PAY_SCHEDULES_IMPACT_FLAG PAYMENT_STATUS_CODE TAX_CHANGE_TYPE_CODE TRANSACTION_TYPE_CODE TRANSACTION_SUBTYPE_CODE TAXES_MODIFIED_FLAG

Name

LEASE_ID

LEASE_DETAIL_ID

COMMENCEMENT_DATE

PAYMENT_TAX_ID

PAYMENT_NUMBER

PAYMENT_TAX_LINE_NUMBER

TAX

TAX_REGIME_CODE

TAX_JURISDICTION_CODE

TAX_RATE_CODE

TAX_RATE

TAX_RATE_ID

RECOVERY_TAX_RATE

RECOVERY_TAX_RATE_ID

PARENT_PAYMENT_TAX_ID

PARENT_TAX_RATE

PARENT_TAX_RATE_ID

PARENT_RECOVERY_TAX_RATE

PARENT_RECOVERY_TAX_RATE_ID

PARENT_TAX_RATE_CODE

PARENT_TAX_RATE_DATE

PARENT_TAX_JURISDICTION_CODE

PARENT_TAX_REGIME_CODE

PARENT_TAX

PARENT_RECOVERY_TAX_RATE_CODE

PARENT_RECOVERY_RATE_DATE

PARENT_RECOVERY_TYPE_CODE

DV_TAX_ATTRIBUTES_CHANGED

VERSION_NEEDS_TAXES

TAX_PAYMENT_ITEMS_IMPACT_FLAG

PAYMENT_ITEMS_IMPACT_FLAG

TAX_SCHEDULES_IMPACT_FLAG

PAY_SCHEDULES_IMPACT_FLAG

PAYMENT_STATUS_CODE

TAX_CHANGE_TYPE_CODE

TRANSACTION_TYPE_CODE

TRANSACTION_SUBTYPE_CODE

TAXES_MODIFIED_FLAG

Query

SQL_Statement
SELECT paytax.lease_id, paytax.lease_detail_id, paytax.commencement_date, paytax.payment_tax_id, paytax.payment_number, paytax.payment_tax_line_number, paytax.tax, paytax.tax_regime_code, paytax.tax_jurisdiction_code, paytax.tax_rate_code, paytax.tax_rate, paytax.tax_rate_id, paytax.recovery_tax_rate, paytax.recovery_tax_rate_id, paytax.parent_payment_tax_id, paytax.parent_tax_rate, paytax.parent_tax_rate_id, paytax.parent_recovery_tax_rate, paytax.parent_recovery_tax_rate_id, paytax.parent_tax_rate_code, paytax.parent_tax_rate_date, paytax.parent_tax_jurisdiction_code, paytax.parent_tax_regime_code, paytax.parent_tax, paytax.parent_recovery_tax_rate_code, paytax.parent_recovery_rate_date, paytax.parent_recovery_type_code, paytax.dv_tax_attributes_changed, CASE WHEN paytax.tax_change_type_code = 'DELETE' THEN 'N' WHEN paytax.payment_status_code = 'DRAFT' THEN 'Y' WHEN paytax.payment_status_code = 'AMEND' AND paytax.dv_tax_attributes_changed = 'Y' THEN 'Y' ELSE 'N' END version_needs_taxes, paytax.tax_payment_items_impact_flag, paytax.payment_items_impact_flag, paytax.tax_schedules_impact_flag, paytax.pay_schedules_impact_flag, paytax.payment_status_code, paytax.tax_change_type_code, paytax.transaction_type_code, paytax.transaction_subtype_code, CASE WHEN paytax.tax_change_type_code = 'DELETE' THEN 'Y' ELSE NVL(( SELECT 'N' FROM fla_tax_transactions WHERE lease_detail_id = paytax.lease_detail_id AND payment_number = paytax.payment_number AND payment_tax_line_number = paytax.payment_tax_line_number AND NVL(tax_rate_id, -1) = NVL(paytax.tax_rate_id, -1) AND NVL(recovery_tax_rate_id, -1) = NVL(paytax.recovery_tax_rate_id,-1) AND transaction_type_code = 'NON_RECOVERABLE' AND ROWNUM =1), 'Y') END taxes_modified_flag FROM ( SELECT ld.lease_id, ld.lease_detail_id, ld.commencement_date, pt.payment_tax_id, pt.payment_number, pt.payment_tax_line_number, pt.tax, pt.tax_regime_code, pt.tax_jurisdiction_code, pt.tax_rate_code, pt.tax_rate tax_rate, pt.tax_rate_id tax_rate_id, pt.recoverable_tax_rate recovery_tax_rate, pt.recovery_tax_rate_id recovery_tax_rate_id, fpt.payment_tax_id parent_payment_tax_id, fpt.tax_rate parent_tax_rate, fpt.tax_rate_id parent_tax_rate_id, fpt.recoverable_tax_rate parent_recovery_tax_rate, fpt.recovery_tax_rate_id parent_recovery_tax_rate_id, fpt.tax_rate_code parent_tax_rate_code, fpt.tax_rate_effective_from parent_tax_rate_date, fpt.tax_jurisdiction_code parent_tax_jurisdiction_code, fpt.tax_regime_code parent_tax_regime_code, fpt.tax parent_tax, fpt.recovery_tax_rate_code parent_recovery_tax_rate_code, fpt.recovery_rate_effective_from parent_recovery_rate_date, fpt.recovery_type_code parent_recovery_type_code, CASE WHEN fpt.payment_tax_id IS NULL THEN 'Y' WHEN pt.tax <> fpt.tax THEN 'Y' WHEN pt.tax_regime_code <> fpt.tax_regime_code THEN 'Y' WHEN NVL(pt.tax_jurisdiction_code, '~') <> NVL(fpt.tax_jurisdiction_code, '~') THEN 'Y' WHEN pt.tax_rate_code <> fpt.tax_rate_code THEN 'Y' WHEN NVL(pt.recovery_tax_rate_code, '~') <> NVL(fpt.recovery_tax_rate_code, '~') THEN 'Y' WHEN NVL(pt.recovery_type_code, '~') <> NVL(fpt.recovery_type_code, '~') THEN 'Y' ELSE 'N' END dv_tax_attributes_changed, NVL(pt.tax_payment_items_impact_flag, 'N') tax_payment_items_impact_flag, NVL(ph.payment_items_impact_flag, 'N') payment_items_impact_flag, pt.schedules_impact_flag tax_schedules_impact_flag, ph.schedules_impact_flag pay_schedules_impact_flag, ph.change_type_code pay_change_type_code, DECODE(ph.apply_upfront_tax_flag, 'N', 'DELETE', pt.change_type_code) tax_change_type_code, ph.payment_status_code, ld.transaction_type_code, ld.transaction_subtype_code FROM fla_leases_all la, fla_lease_details ld, fla_lease_payment_headers ph, fla_lease_payment_taxes pt, fla_lease_payment_taxes fpt WHERE la.lease_id = ld.lease_id AND ld.lease_id = ph.lease_id AND ld.lease_detail_id = ph.lease_detail_id AND ph.lease_id = pt.lease_id AND ph.lease_detail_id = pt.lease_detail_id AND ph.payment_number = pt.payment_number AND pt.lease_id = fpt.lease_id(+) AND pt.payment_tax_id = fpt.parent_record_id(+) AND pt.payment_number = fpt.payment_number(+) AND pt.payment_tax_line_number = fpt.payment_tax_line_number(+) AND (ph.schedules_impact_flag = 'Y' OR pt.schedules_impact_flag = 'Y') AND ld.version_type_code = 'D' ) paytax

SQL_Statement

SELECT paytax.lease_id,

paytax.lease_detail_id,

paytax.commencement_date,

paytax.payment_tax_id,

paytax.payment_number,

paytax.payment_tax_line_number,

paytax.tax,

paytax.tax_regime_code,

paytax.tax_jurisdiction_code,

paytax.tax_rate_code,

paytax.tax_rate,

paytax.tax_rate_id,

paytax.recovery_tax_rate,

paytax.recovery_tax_rate_id,

paytax.parent_payment_tax_id,

paytax.parent_tax_rate,

paytax.parent_tax_rate_id,

paytax.parent_recovery_tax_rate,

paytax.parent_recovery_tax_rate_id,

paytax.parent_tax_rate_code,

paytax.parent_tax_rate_date,

paytax.parent_tax_jurisdiction_code,

paytax.parent_tax_regime_code,

paytax.parent_tax,

paytax.parent_recovery_tax_rate_code,

paytax.parent_recovery_rate_date,

paytax.parent_recovery_type_code,

paytax.dv_tax_attributes_changed,

CASE

WHEN paytax.tax_change_type_code = 'DELETE' THEN 'N'

WHEN paytax.payment_status_code = 'DRAFT' THEN 'Y'

WHEN paytax.payment_status_code = 'AMEND' AND paytax.dv_tax_attributes_changed = 'Y' THEN 'Y'

ELSE 'N'

END version_needs_taxes,

paytax.tax_payment_items_impact_flag,

paytax.payment_items_impact_flag,

paytax.tax_schedules_impact_flag,

paytax.pay_schedules_impact_flag,

paytax.payment_status_code,

paytax.tax_change_type_code,

paytax.transaction_type_code,

paytax.transaction_subtype_code,

CASE

WHEN paytax.tax_change_type_code = 'DELETE' THEN 'Y'

ELSE

NVL((

SELECT 'N'

FROM fla_tax_transactions

WHERE lease_detail_id = paytax.lease_detail_id

AND payment_number = paytax.payment_number

AND payment_tax_line_number = paytax.payment_tax_line_number

AND NVL(tax_rate_id, -1) = NVL(paytax.tax_rate_id, -1)

AND NVL(recovery_tax_rate_id, -1) = NVL(paytax.recovery_tax_rate_id,-1)

AND transaction_type_code = 'NON_RECOVERABLE'

AND ROWNUM =1), 'Y')

END taxes_modified_flag

FROM (

SELECT ld.lease_id,

ld.lease_detail_id,

ld.commencement_date,

pt.payment_tax_id,

pt.payment_number,

pt.payment_tax_line_number,

pt.tax,

pt.tax_regime_code,

pt.tax_jurisdiction_code,

pt.tax_rate_code,

pt.tax_rate tax_rate,

pt.tax_rate_id tax_rate_id,

pt.recoverable_tax_rate recovery_tax_rate,

pt.recovery_tax_rate_id recovery_tax_rate_id,

fpt.payment_tax_id parent_payment_tax_id,

fpt.tax_rate parent_tax_rate,

fpt.tax_rate_id parent_tax_rate_id,

fpt.recoverable_tax_rate parent_recovery_tax_rate,

fpt.recovery_tax_rate_id parent_recovery_tax_rate_id,

fpt.tax_rate_code parent_tax_rate_code,

fpt.tax_rate_effective_from parent_tax_rate_date,

fpt.tax_jurisdiction_code parent_tax_jurisdiction_code,

fpt.tax_regime_code parent_tax_regime_code,

fpt.tax parent_tax,

fpt.recovery_tax_rate_code parent_recovery_tax_rate_code,

fpt.recovery_rate_effective_from parent_recovery_rate_date,

fpt.recovery_type_code parent_recovery_type_code,

CASE

WHEN fpt.payment_tax_id IS NULL THEN 'Y'

WHEN pt.tax <> fpt.tax THEN 'Y'

WHEN pt.tax_regime_code <> fpt.tax_regime_code THEN 'Y'

WHEN NVL(pt.tax_jurisdiction_code, '~') <> NVL(fpt.tax_jurisdiction_code, '~') THEN 'Y'

WHEN pt.tax_rate_code <> fpt.tax_rate_code THEN 'Y'

WHEN NVL(pt.recovery_tax_rate_code, '~') <> NVL(fpt.recovery_tax_rate_code, '~') THEN 'Y'

WHEN NVL(pt.recovery_type_code, '~') <> NVL(fpt.recovery_type_code, '~') THEN 'Y'

ELSE 'N'

END dv_tax_attributes_changed,

NVL(pt.tax_payment_items_impact_flag, 'N') tax_payment_items_impact_flag,

NVL(ph.payment_items_impact_flag, 'N') payment_items_impact_flag,

pt.schedules_impact_flag tax_schedules_impact_flag,

ph.schedules_impact_flag pay_schedules_impact_flag,

ph.change_type_code pay_change_type_code,

DECODE(ph.apply_upfront_tax_flag, 'N', 'DELETE', pt.change_type_code) tax_change_type_code,

ph.payment_status_code,

ld.transaction_type_code,

ld.transaction_subtype_code

FROM fla_leases_all la,

fla_lease_details ld,

fla_lease_payment_headers ph,

fla_lease_payment_taxes pt,

fla_lease_payment_taxes fpt

WHERE la.lease_id = ld.lease_id

AND ld.lease_id = ph.lease_id

AND ld.lease_detail_id = ph.lease_detail_id

AND ph.lease_id = pt.lease_id

AND ph.lease_detail_id = pt.lease_detail_id

AND ph.payment_number = pt.payment_number

AND pt.lease_id = fpt.lease_id(+)

AND pt.payment_tax_id = fpt.parent_record_id(+)

AND pt.payment_number = fpt.payment_number(+)

AND pt.payment_tax_line_number = fpt.payment_tax_line_number(+)

AND (ph.schedules_impact_flag = 'Y' OR pt.schedules_impact_flag = 'Y')

AND ld.version_type_code = 'D'

) paytax