1. Tables and Views for SCM
  2. CML_INVOICE_DTLS_V

CML_INVOICE_DTLS_V

Details

  • Schema: FUSION

  • Object owner: CML

  • Object type: VIEW

Columns

Name

INVOICE_ID

INVOICE_NUMBER

INVOICE_LINE_NUMBER

PROCUREMENT_BUSINESS_UNIT_ID

VENDOR_ID

VENDOR_SITE_ID

INVOICE_LINE_TYPE

INVOICE_CREATION_DATE

LINE_CANCELLED_FLAG

LCM_ASSOCIATION_REQUEST_ID

ONLINE_ASSOC_PROCESS_ID

LCM_ASSOCIATION_PROCESSED_FLAG

EXTERNAL_SYSTEM_REFERENCE

ASSOCIATION_STATUS

Query

SQL_Statement

SELECT caid.invoice_id,

caid.invoice_number,

caid.invoice_line_number,

MAX(caid.procurement_business_unit_id) AS procurement_business_unit_id ,

MAX(caid.vendor_id) AS vendor_id,

MAX(caid.vendor_site_id) AS vendor_site_id,

MAX(caid.invoice_line_type) AS invoice_line_type,

MAX(caid.INVOICE_CREATION_DATE) AS INVOICE_CREATION_DATE,

MAX(caid.LINE_CANCELLED_FLAG) AS LINE_CANCELLED_FLAG,

MAX(caid.LCM_ASSOCIATION_REQUEST_ID) AS LCM_ASSOCIATION_REQUEST_ID,

MAX(caid.ONLINE_ASSOC_PROCESS_ID) AS ONLINE_ASSOC_PROCESS_ID,

MAX(caid.LCM_ASSOCIATION_PROCESSED_FLAG ) AS LCM_ASSOCIATION_PROCESSED_FLAG,

MAX(caid.external_system_reference) AS external_system_reference,

to_char(

CASE

WHEN MAX(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 '0'

END

ELSE MIN(ccia.association_status)

END

END) association_status

FROM cmr_ap_invoice_dtls caid,

cml_charge_invoice_assocs ccia

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 =ccia.INVOICE_ID

AND ccia.INVOICE_LINE_NUMBER=caid.INVOICE_LINE_NUMBER

AND ASSOCIATION_STATUS =10

) )

and caid.invoice_id=ccia.invoice_id(+)

and caid.invoice_line_number=ccia.invoice_line_number(+)

GROUP BY caid.invoice_id,

caid.invoice_number,

caid.invoice_line_number