1. Tables and Views for Financials
  2. AP_INVOICE_TOTALS_VIEW

AP_INVOICE_TOTALS_VIEW

Details

  • Schema: FUSION

  • Object owner: AP

  • Object type: VIEW

Columns

Name

SORTVARIABLE

INVOICE_ID

AMOUNT

TYPE

INVOICE_CURRENCY_CODE

Query

SQL_Statement

SELECT 1 ,ail.invoice_id,sum(nvl(amount,0)- nvl(included_tax_amount,0)),alc.displayed_field type,ai.invoice_currency_code

from ap_invoice_lines_all ail,ap_invoices_all ai, ap_lookup_codes alc

where NVL(ail.discarded_flag,'N') = 'N'

and line_type_lookup_code = 'ITEM'

and ail.invoice_id = ai.invoice_id

and alc.lookup_code = 'ITEMS'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id,ai.invoice_currency_code, alc.displayed_field

UNION

SELECT 2 , ail.invoice_id,

nvl(SUM(DECODE(AIL.line_type_lookup_code, 'FREIGHT',(AIL.amount - nvl(AIL.included_tax_amount,0)), Null)), 0),

alc.displayed_field type , ai.invoice_currency_code

from ap_invoice_lines_all ail, ap_invoices_all ai, ap_lookup_codes alc

where NVL(ail.discarded_flag,'N') = 'N'

and ail.invoice_id = ai.invoice_id

and alc.lookup_code = 'FREIGHT'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id,ai.invoice_currency_code, alc.displayed_field

UNION

SELECT 3, ail.invoice_id,

nvl(SUM(DECODE(AIL.line_type_lookup_code, 'MISCELLANEOUS',(AIL.amount - nvl(AIL.included_tax_amount,0)), Null)), 0),

alc.displayed_field type, ai.invoice_currency_code

from ap_invoice_lines_all ail, ap_invoices_all ai, ap_lookup_codes alc

where NVL(ail.discarded_flag,'N') = 'N'

and ail.invoice_id = ai.invoice_id

and alc.lookup_code = 'MISCELLANEOUS'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id,ai.invoice_currency_code, alc.displayed_field

UNION

SELECT 4, ail.invoice_id,

NVL(SUM(CASE WHEN (ail.line_type_lookup_code = 'TAX') AND (ail.prepay_invoice_id IS NULL)

THEN NVL(ail.amount,0) ELSE 0 END),0),

alc.displayed_field type , ai.invoice_currency_code

from ap_invoice_lines_all ail , ap_invoices_all ai, ap_lookup_codes alc

where NVL(ail.discarded_flag,'N') = 'N'

and ail.invoice_id = ai.invoice_id

and alc.lookup_code = 'EXCLUSIVE TAX'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id,ai.invoice_currency_code, alc.displayed_field

UNION

select 5, ail.invoice_id,

NVL(SUM(CASE WHEN (ail.line_type_lookup_code <> 'TAX') AND (ail.prepay_invoice_id IS NULL)

THEN NVL(ail.included_tax_amount,0) ELSE 0 END),0),

alc.displayed_field type, ai.invoice_currency_code

from ap_invoice_lines_all ail, ap_invoices_all ai, ap_lookup_codes alc

where nvl(ail.discarded_flag,'N') = 'N'

and ai.invoice_id = ail.invoice_id

and alc.lookup_code = 'INCLUSIVE TAX'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id,ai.invoice_currency_code, alc.displayed_field

UNION

SELECT 6, ail.invoice_id,

NVL(SUM(CASE WHEN (ail.line_type_lookup_code IN ('PREPAY','TAX')) AND (ail.prepay_invoice_id IS NOT NULL) AND (NVL(ail.invoice_includes_prepay_flag,'N') = 'Y')

THEN NVL(ail.amount,0) ELSE 0 END),0),

ALC.DISPLAYED_FIELD type, ai.invoice_currency_code

from ap_invoice_lines_all ail, ap_invoices_all ai, ap_lookup_codes alc

where NVL(ail.discarded_flag,'N') = 'N'

and ai.invoice_id = ail.invoice_id

and alc.lookup_code = 'INCLUSIVE PREPAY'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id, ai.invoice_currency_code, alc.displayed_field

UNION

SELECT 7, ail.invoice_id,

(sum(decode(ail.line_type_lookup_code,'ITEM',nvl(ail.amount,0) - nvl(ail.included_tax_amount,0),0)) +

sum(decode(ail.line_type_lookup_code,'FREIGHT',nvl(ail.amount,0) - nvl(ail.included_tax_amount,0),0)) +

sum(decode(ail.line_type_lookup_code,'MISCELLANEOUS',nvl(ail.amount,0) - nvl(ail.included_tax_amount,0),0)) +

NVL(SUM(CASE WHEN (ail.line_type_lookup_code = 'TAX') AND (ail.prepay_invoice_id IS NULL)

THEN NVL(ail.amount,0) ELSE 0 END),0) +

NVL(SUM(CASE WHEN (ail.line_type_lookup_code <> 'TAX') AND (ail.prepay_invoice_id IS NULL)

THEN NVL(ail.included_tax_amount,0) ELSE 0 END),0) +

NVL(SUM(CASE WHEN (ail.line_type_lookup_code IN ('PREPAY','TAX')) AND (ail.prepay_invoice_id IS NOT NULL) AND (NVL(ail.invoice_includes_prepay_flag,'N') = 'Y')

THEN NVL(ail.amount,0) ELSE 0 END),0)

) amount,

alc.displayed_field type , ai.invoice_currency_code

from ap_invoice_lines_all ail, ap_invoices_all ai, ap_lookup_codes alc

where NVL(ail.discarded_flag,'N') = 'N'

and ail.invoice_id = ai.invoice_id

and alc.lookup_code = 'SUBTOTAL'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id,ai.invoice_currency_code, alc.displayed_field

UNION

select 8, ai.invoice_id, ai.invoice_amount amount,alc.displayed_field type,ai.invoice_currency_code

from ap_invoices_all ai, ap_lookup_codes alc

where alc.lookup_code = 'INVOICE AMOUNT'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

UNION

select 9, ai.invoice_id,

(

(sum(decode(ail.line_type_lookup_code,'ITEM',nvl(ail.amount,0) - nvl(ail.included_tax_amount,0),0)) +

sum(decode(ail.line_type_lookup_code,'FREIGHT',nvl(ail.amount,0) - nvl(ail.included_tax_amount,0),0)) +

sum(decode(ail.line_type_lookup_code,'MISCELLANEOUS',nvl(ail.amount,0) - nvl(ail.included_tax_amount,0),0)) +

sum (decode (ail.line_type_lookup_code,'ITEM', decode (nvl(ai.net_of_retainage_flag,'N'), 'Y', ail.retained_amount ,0 )) ) +

NVL(SUM(CASE WHEN (ail.line_type_lookup_code = 'TAX') AND (ail.prepay_invoice_id IS NULL)

THEN NVL(ail.amount,0) ELSE 0 END),0) +

NVL(SUM(CASE WHEN (ail.line_type_lookup_code <> 'TAX') AND (ail.prepay_invoice_id IS NULL)

THEN NVL(ail.included_tax_amount,0) ELSE 0 END),0) +

NVL(SUM(CASE WHEN (ail.line_type_lookup_code IN ('PREPAY','TAX')) AND (ail.prepay_invoice_id IS NOT NULL) AND (NVL(ail.invoice_includes_prepay_flag,'N') = 'Y')

THEN NVL(ail.amount,0) ELSE 0 END),0)

) - ai.invoice_amount

) * -1 amount,

alc.displayed_field type , ai.invoice_currency_code

from ap_invoices_all ai, ap_invoice_lines_all ail, ap_lookup_codes alc

where ai.invoice_id = ail.invoice_id

and NVL(ail.discarded_flag , 'N') = 'N'

and alc.lookup_code = 'REMAINING AMOUNT'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ai.invoice_id ,ai.invoice_amount,ai.invoice_currency_code, alc.displayed_field

UNION

SELECT 10, ail.invoice_id,

(0 - nvl(SUM(DECODE(AIL.line_type_lookup_code, 'AWT',AIL.amount,Null)),0)),

alc.displayed_field type, ai.invoice_currency_code

from ap_invoice_lines_all ail, ap_invoices_all ai, ap_lookup_codes alc

where NVL(ail.discarded_flag,'N') = 'N'

and ai.invoice_id = ail.invoice_id

and alc.lookup_code = 'WITHHELD TAX'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id,ai.invoice_currency_code, alc.displayed_field

UNION

SELECT 11, ail.invoice_id,

NVL(SUM(CASE WHEN (ail.line_type_lookup_code IN ('PREPAY','TAX')) AND (ail.prepay_invoice_id IS NOT NULL) AND (NVL(ail.invoice_includes_prepay_flag,'N') = 'N')

THEN NVL(ail.amount,0) ELSE 0 END),0),

alc.displayed_field type, ai.invoice_currency_code

from ap_invoice_lines_all ail , ap_invoices_all ai, ap_lookup_codes alc

where NVL(ail.discarded_flag,'N') = 'N'

and ail.invoice_id = ai.invoice_id

and alc.lookup_code = 'EXCLUSIVE PREPAY'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id,ai.invoice_currency_code, alc.displayed_field

UNION

select 12, ail.invoice_id,

nvl((select nvl(sum(nvl(ast.amount, 0)), 0)

from AP_SELF_ASSESSED_TAX_DIST_ALL ast

where ast.invoice_id = ail.invoice_id

group by ast.invoice_id) ,0) ,

alc.displayed_field type , ai.invoice_currency_code

from ap_invoice_lines_all ail , ap_invoices_all ai, ap_lookup_codes alc

where ai.invoice_id = ail.invoice_id

and alc.lookup_code = 'SELF-ASSESSED TAX'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id,ai.invoice_currency_code, alc.displayed_field

UNION

select 13, ai.invoice_id,nvl(ai.cancelled_amount,0),

alc.displayed_field type, ai.invoice_currency_code

from ap_invoices_all ai, ap_lookup_codes alc

where alc.lookup_code = 'CANCELED AMOUNT'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

UNION

SELECT 14, ail.invoice_id,

(0 - nvl(SUM(DECODE(AIL.line_type_lookup_code, 'ITEM',AIL.retained_amount,Null)),0)),

alc.displayed_field type, ai.invoice_currency_code

from ap_invoice_lines_all ail, ap_invoices_all ai, ap_lookup_codes alc

where NVL(ail.discarded_flag,'N') = 'N'

and ai.invoice_id = ail.invoice_id

and alc.lookup_code = 'ORA_RETAINAGE'

and alc.lookup_type = 'AP_CONTEXTUAL_STATUS'

group by ail.invoice_id,ai.invoice_currency_code, alc.displayed_field