1. Tables and Views for SCM
  2. CML_INVOICE_LINE_DTLS_V

CML_INVOICE_LINE_DTLS_V

Details

  • Schema: FUSION

  • Object owner: CML

  • Object type: VIEW

Columns

Name

INVOICE_ID

INVOICE_NUMBER

INVOICE_LINE_NUMBER

INVOICE_CREATION_DATE

PROCUREMENT_BUSINESS_UNIT_ID

VENDOR_ID

VENDOR_SITE_ID

INVOICE_LINE_TYPE

CURRENCY_CODE

INVOICE_AMOUNT

TAX_AMOUNT

FUNCTIONAL_INVOICE_AMOUNT

FUNCTIONAL_TAX_AMOUNT

FUNCTIONAL_CURRENCY_CODE

LCM_ASSOCIATION_REQUEST_ID

LCM_ASSOCIATION_PROCESSED_FLAG

LINE_CANCELLED_FLAG

EXTERNAL_SYSTEM_REFERENCE

ASSOC_STATUS

Query

SQL_Statement

SELECT

invoice_dist.invoice_id,

invoice_dist.invoice_number,

invoice_dist.invoice_line_number,

MIN(invoice_dist.INVOICE_CREATION_DATE) INVOICE_CREATION_DATE,

invoice_dist.procurement_business_unit_id,

invoice_dist.vendor_id,

invoice_dist.vendor_site_id,

invoice_dist.invoice_line_type,

invoice_dist.currency_code,

SUM(invoice_dist.invoice_amount) invoice_amount,

SUM(invoice_dist.direct_tax_amount + NVL(invoice_dist.applied_tax_amount,0))

tax_amount,

SUM(invoice_dist.invoice_amount *invoice_dist.curr_rate)

functional_invoice_amount,

SUM((invoice_dist.direct_tax_amount + NVL(invoice_dist.applied_tax_amount,0))

*invoice_dist.curr_rate)

functional_tax_amount,

invoice_dist.INVOICING_BU_FUNC_CURR_CODE functional_currency_code,

MAX(LCM_ASSOCIATION_REQUEST_ID) LCM_ASSOCIATION_REQUEST_ID,

MAX(NVL(LCM_ASSOCIATION_PROCESSED_FLAG,'Y')) LCM_ASSOCIATION_PROCESSED_FLAG,

MAX(invoice_dist.LINE_CANCELLED_FLAG) LINE_CANCELLED_FLAG,

invoice_dist.external_system_reference,

CASE

WHEN MAX(invoice_dist.LINE_CANCELLED_FLAG)='Y'

THEN '80'

WHEN MIN(ccia.association_status)IS NULL

THEN

CASE

WHEN MAX(LCM_ASSOCIATION_REQUEST_ID) IS NOT NULL

OR MAX(ONLINE_ASSOC_PROCESS_ID) IS NOT NULL

THEN '70'

ELSE '0'

END

ELSE

CASE

WHEN MIN(ccia.association_status)=50

OR MIN(ccia.association_status) =60

THEN '70'

WHEN MIN(ccia.association_status)=90

THEN

CASE

WHEN MAX(LCM_ASSOCIATION_REQUEST_ID) IS NOT NULL

OR MAX(ONLINE_ASSOC_PROCESS_ID) IS NOT NULL

THEN '70'

ELSE '20'

END

ELSE MIN(ccia.association_status)

END

END assoc_status

FROM

(

SELECT

caid.invoice_id,

caid.invoice_number,

caid.invoice_line_number,

caid.procurement_business_unit_id,

caid.vendor_id,

caid.vendor_site_id,

caid.invoice_line_type,

caid.INVOICE_CREATION_DATE,

caid.currency_code,

caid.INVOICING_BU_FUNC_CURR_CODE,

caid.LINE_CANCELLED_FLAG,

NVL(caid.currency_conversion_rate,1) curr_rate,

caid.LCM_ASSOCIATION_REQUEST_ID,

caid.ONLINE_ASSOC_PROCESS_ID,

caid.LCM_ASSOCIATION_PROCESSED_FLAG,

caid.external_system_reference,

CASE

WHEN caid.invoice_line_type <> 'TAX'

AND caid.invoice_distribution_type IN ('ITEM', 'FREIGHT', 'MISC')

THEN caid.invoice_amt

WHEN caid.invoice_line_type = 'TAX'

AND caid.invoice_distribution_type = 'NONREC_TAX'

THEN caid.invoice_amt

ELSE 0

END invoice_amount,

CASE

WHEN caid.invoice_line_type <> 'TAX'

AND caid.invoice_distribution_type = 'NONREC_TAX'

THEN caid.invoice_amt

ELSE 0

END direct_tax_amount,

(

SELECT

SUM(invoice_amt)

FROM

cmr_ap_invoice_dtls caid_related_tax

WHERE

caid_related_tax.invoice_line_type = 'TAX'

AND caid_related_tax.invoice_distribution_type = 'NONREC_TAX'

AND caid_related_tax.charge_applicable_to_dist_id =

caid.external_system_ref_id

AND caid_related_tax.external_system_reference =

caid.external_system_reference

AND caid.lcm_enabled_flag = 'Y'

)

applied_tax_amount

FROM

cmr_ap_invoice_dtls caid

WHERE

caid.lcm_enabled_flag = 'Y'

AND

(

caid.invoice_line_type <> 'TAX'

OR

(

caid.invoice_line_type = 'TAX'

AND caid.charge_applicable_to_dist_id IS NULL

)

)

AND

(

NOT EXISTS

(

SELECT

1

FROM

cmr_ap_invoice_dtls

WHERE

external_system_reference = caid.external_system_reference

AND invoice_id = caid.invoice_id

AND invoice_line_number = caid.invoice_line_number

AND line_cancelled_flag = 'Y'

)

OR EXISTS

(

SELECT

1

FROM

CML_CHARGE_INVOICE_ASSOCS ccia

WHERE

ccia.INVOICE_ID =caid.INVOICE_ID

AND ccia.INVOICE_LINE_NUMBER=caid.INVOICE_LINE_NUMBER

AND ASSOCIATION_STATUS =10

)

)

)

invoice_dist,

(

SELECT

invoice_id,

invoice_line_number,

MIN(association_status) association_status

FROM

cml_charge_invoice_assocs

GROUP BY

invoice_id,

invoice_line_number

)

ccia

WHERE

invoice_dist.invoice_id =ccia.invoice_id(+)

AND invoice_dist.invoice_line_number=ccia.invoice_line_number(+)

GROUP BY

invoice_dist.invoice_id,

invoice_dist.invoice_number,

invoice_dist.invoice_line_number,

invoice_dist.procurement_business_unit_id,

invoice_dist.vendor_id,

invoice_dist.vendor_site_id,

invoice_dist.invoice_line_type,

invoice_dist.currency_code,

invoice_dist.INVOICING_BU_FUNC_CURR_CODE,

invoice_dist.external_system_reference