CML_INVOICE_LINE_DETAILS

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_ONLINE_ASSOC_PROCESS_ID LCM_ASSOCIATION_PROCESSED_FLAG LINE_CANCELLED_FLAG LCM_ENABLED_FLAG EXTERNAL_SYSTEM_REFERENCE ASSOC_STATUS

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_ONLINE_ASSOC_PROCESS_ID

LCM_ASSOCIATION_PROCESSED_FLAG

LINE_CANCELLED_FLAG

LCM_ENABLED_FLAG

EXTERNAL_SYSTEM_REFERENCE

ASSOC_STATUS

Query

SQL_Statement
SELECT invoice_agg.invoice_id invoice_id, invoice_agg.invoice_number invoice_number, invoice_agg.invoice_line_number invoice_line_number, invoice_agg.INVOICE_CREATION_DATE INVOICE_CREATION_DATE, invoice_agg.procurement_business_unit_id procurement_business_unit_id, invoice_agg.vendor_id vendor_id, invoice_agg.vendor_site_id vendor_site_id, invoice_agg.invoice_line_type invoice_line_type, invoice_agg.currency_code currency_code, invoice_agg.invoice_amount invoice_amount, invoice_agg.tax_amount tax_amount, invoice_agg.functional_invoice_amount functional_invoice_amount, invoice_agg.functional_tax_amount functional_tax_amount, invoice_agg.functional_currency_code functional_currency_code, invoice_agg.LCM_ASSOCIATION_REQUEST_ID LCM_ASSOCIATION_REQUEST_ID, invoice_agg.LCM_ONLINE_ASSOC_PROCESS_ID LCM_ONLINE_ASSOC_PROCESS_ID, invoice_agg.LCM_ASSOCIATION_PROCESSED_FLAG LCM_ASSOCIATION_PROCESSED_FLAG, invoice_agg.LINE_CANCELLED_FLAG LINE_CANCELLED_FLAG, invoice_agg.LCM_ENABLED_FLAG LCM_ENABLED_FLAG, invoice_agg.external_system_reference external_system_reference, to_char( CASE WHEN invoice_agg.LINE_CANCELLED_FLAG='Y' THEN '80' WHEN invoice_agg.association_status IS NULL THEN CASE WHEN invoice_agg.LCM_ASSOCIATION_REQUEST_ID IS NOT NULL OR invoice_agg.LCM_ONLINE_ASSOC_PROCESS_ID IS NOT NULL THEN '70' ELSE '0' END ELSE CASE WHEN invoice_agg.association_status='50' OR invoice_agg.association_status ='60' THEN '70' WHEN invoice_agg.association_status='90' THEN CASE WHEN invoice_agg.LCM_ASSOCIATION_REQUEST_ID IS NOT NULL OR invoice_agg.LCM_ONLINE_ASSOC_PROCESS_ID IS NOT NULL THEN '70' ELSE '20' END ELSE invoice_agg.association_status END END) assoc_status FROM ( SELECT invoice_dist.invoice_id, invoice_dist.invoice_number invoice_number, invoice_dist.invoice_line_number invoice_line_number, to_timestamp(MIN(invoice_dist.INVOICE_CREATION_DATE)) INVOICE_CREATION_DATE, invoice_dist.procurement_business_unit_id procurement_business_unit_id, invoice_dist.vendor_id vendor_id, invoice_dist.vendor_site_id vendor_site_id, invoice_dist.invoice_line_type invoice_line_type, invoice_dist.currency_code 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(ONLINE_ASSOC_PROCESS_ID) LCM_ONLINE_ASSOC_PROCESS_ID, MAX(NVL(LCM_ASSOCIATION_PROCESSED_FLAG,'Y')) LCM_ASSOCIATION_PROCESSED_FLAG, MAX(invoice_dist.LINE_CANCELLED_FLAG) LINE_CANCELLED_FLAG, MAX(invoice_dist.LCM_ENABLED_FLAG) LCM_ENABLED_FLAG, invoice_dist.external_system_reference external_system_reference, ( SELECT /+ NO_UNNEST / MIN(ccia.association_status) FROM cml_charge_invoice_assocs ccia WHERE ccia.invoice_id = invoice_dist.invoice_id AND ccia.invoice_line_number= invoice_dist.invoice_line_number AND ccia.active_flag = 'Y' ) association_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, caid.LCM_ENABLED_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, caid_related_tax.applied_tax_amount applied_tax_amount FROM cmr_ap_invoice_dtls caid, ( SELECT /+ MATERIALIZE INDEX(caid_related_tax_src CMR_AP_INVOICE_DTLS_N3) / caid_related_tax_src.charge_applicable_to_dist_id, caid_related_tax_src.external_system_reference, SUM(caid_related_tax_src.invoice_amt) applied_tax_amount FROM cmr_ap_invoice_dtls caid_related_tax_src WHERE caid_related_tax_src.lcm_enabled_flag = 'Y' AND caid_related_tax_src.invoice_line_type = 'TAX' AND caid_related_tax_src.invoice_distribution_type = 'NONREC_TAX' AND caid_related_tax_src.charge_applicable_to_dist_id IS NOT NULL AND caid_related_tax_src.external_system_reference IS NOT NULL GROUP BY caid_related_tax_src.charge_applicable_to_dist_id, caid_related_tax_src.external_system_reference ) caid_related_tax WHERE caid.lcm_enabled_flag = 'Y' 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.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 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 ) invoice_agg UNION ALL SELECT ccia.INVOICE_ID INVOICE_ID, caid.invoice_number invoice_number, TO_CHAR(ccia.INVOICE_LINE_NUMBER) INVOICE_LINE_NUMBER, to_timestamp(MIN(caid.INVOICE_CREATION_DATE)) INVOICE_CREATION_DATE, caid.procurement_business_unit_id procurement_business_unit_id, caid.vendor_id vendor_id, caid.vendor_site_id vendor_site_id, caid.invoice_line_type invoice_line_type, caid.currency_code currency_code, ccia.invoice_amount invoice_amount, ccia.INVOICE_TAX tax_amount, ccia.FUNC_CURRENCY_AMOUNT functional_invoice_amount, ccia.FUNC_CURRENCY_TAX functional_tax_amount, ccia.FUNC_CURRENCY_CODE functional_currency_code, MAX(LCM_ASSOCIATION_REQUEST_ID) LCM_ASSOCIATION_REQUEST_ID, MAX(ONLINE_ASSOC_PROCESS_ID) LCM_ONLINE_ASSOC_PROCESS_ID, MAX(LCM_ASSOCIATION_PROCESSED_FLAG) LCM_ASSOCIATION_PROCESSED_FLAG, MAX(caid.LINE_CANCELLED_FLAG) LINE_CANCELLED_FLAG, MAX(caid.LCM_ENABLED_FLAG) LCM_ENABLED_FLAG, caid.external_system_reference external_system_reference, ccia.ASSOCIATION_STATUS assoc_status FROM cml_charge_invoice_assocs ccia, cmr_ap_invoice_dtls caid, cml_trade_operation_charges ctoc WHERE ccia.invoice_id =caid.invoice_id AND ccia.INVOICE_LINE_NUMBER=caid.INVOICE_LINE_NUMBER and ccia.trade_operation_charge_id=ctoc.trade_operation_charge_id and ctoc.service_po_flag='Y' and ctoc.line_status_code <> 'CANCELED' GROUP BY ccia.INVOICE_ID , caid.invoice_number , ccia.INVOICE_LINE_NUMBER , caid.procurement_business_unit_id , caid.vendor_id , caid.vendor_site_id , caid.invoice_line_type, caid.currency_code, ccia.invoice_amount , ccia.INVOICE_TAX , ccia.FUNC_CURRENCY_AMOUNT , ccia.FUNC_CURRENCY_TAX , ccia.FUNC_CURRENCY_CODE , caid.external_system_reference, ccia.ASSOCIATION_STATUS

SQL_Statement

SELECT

invoice_agg.invoice_id invoice_id,

invoice_agg.invoice_number invoice_number,

invoice_agg.invoice_line_number invoice_line_number,

invoice_agg.INVOICE_CREATION_DATE INVOICE_CREATION_DATE,

invoice_agg.procurement_business_unit_id procurement_business_unit_id,

invoice_agg.vendor_id vendor_id,

invoice_agg.vendor_site_id vendor_site_id,

invoice_agg.invoice_line_type invoice_line_type,

invoice_agg.currency_code currency_code,

invoice_agg.invoice_amount invoice_amount,

invoice_agg.tax_amount tax_amount,

invoice_agg.functional_invoice_amount functional_invoice_amount,

invoice_agg.functional_tax_amount functional_tax_amount,

invoice_agg.functional_currency_code functional_currency_code,

invoice_agg.LCM_ASSOCIATION_REQUEST_ID LCM_ASSOCIATION_REQUEST_ID,

invoice_agg.LCM_ONLINE_ASSOC_PROCESS_ID LCM_ONLINE_ASSOC_PROCESS_ID,

invoice_agg.LCM_ASSOCIATION_PROCESSED_FLAG

LCM_ASSOCIATION_PROCESSED_FLAG,

invoice_agg.LINE_CANCELLED_FLAG LINE_CANCELLED_FLAG,

invoice_agg.LCM_ENABLED_FLAG LCM_ENABLED_FLAG,

invoice_agg.external_system_reference external_system_reference,

to_char(

CASE

WHEN invoice_agg.LINE_CANCELLED_FLAG='Y'

THEN '80'

WHEN invoice_agg.association_status IS NULL

THEN

CASE

WHEN invoice_agg.LCM_ASSOCIATION_REQUEST_ID IS NOT NULL

OR invoice_agg.LCM_ONLINE_ASSOC_PROCESS_ID IS NOT NULL

THEN '70'

ELSE '0'

END

ELSE

CASE

WHEN invoice_agg.association_status='50'

OR invoice_agg.association_status ='60'

THEN '70'

WHEN invoice_agg.association_status='90'

THEN

CASE

WHEN invoice_agg.LCM_ASSOCIATION_REQUEST_ID IS NOT NULL

OR invoice_agg.LCM_ONLINE_ASSOC_PROCESS_ID IS NOT NULL

THEN '70'

ELSE '20'

END

ELSE invoice_agg.association_status

END

END) assoc_status

FROM

(

SELECT

invoice_dist.invoice_id,

invoice_dist.invoice_number invoice_number,

invoice_dist.invoice_line_number invoice_line_number,

to_timestamp(MIN(invoice_dist.INVOICE_CREATION_DATE)) INVOICE_CREATION_DATE,

invoice_dist.procurement_business_unit_id procurement_business_unit_id,

invoice_dist.vendor_id vendor_id,

invoice_dist.vendor_site_id vendor_site_id,

invoice_dist.invoice_line_type invoice_line_type,

invoice_dist.currency_code 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(ONLINE_ASSOC_PROCESS_ID) LCM_ONLINE_ASSOC_PROCESS_ID,

MAX(NVL(LCM_ASSOCIATION_PROCESSED_FLAG,'Y'))

LCM_ASSOCIATION_PROCESSED_FLAG,

MAX(invoice_dist.LINE_CANCELLED_FLAG) LINE_CANCELLED_FLAG,

MAX(invoice_dist.LCM_ENABLED_FLAG) LCM_ENABLED_FLAG,

invoice_dist.external_system_reference external_system_reference,

(

SELECT /*+ NO_UNNEST */

MIN(ccia.association_status)

FROM

cml_charge_invoice_assocs ccia

WHERE

ccia.invoice_id = invoice_dist.invoice_id

AND ccia.invoice_line_number= invoice_dist.invoice_line_number

AND ccia.active_flag = 'Y'

) association_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,

caid.LCM_ENABLED_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,

caid_related_tax.applied_tax_amount applied_tax_amount

FROM

cmr_ap_invoice_dtls caid,

(

SELECT /*+ MATERIALIZE INDEX(caid_related_tax_src CMR_AP_INVOICE_DTLS_N3) */

caid_related_tax_src.charge_applicable_to_dist_id,

caid_related_tax_src.external_system_reference,

SUM(caid_related_tax_src.invoice_amt) applied_tax_amount

FROM

cmr_ap_invoice_dtls caid_related_tax_src

WHERE

caid_related_tax_src.lcm_enabled_flag = 'Y'

AND caid_related_tax_src.invoice_line_type = 'TAX'

AND caid_related_tax_src.invoice_distribution_type = 'NONREC_TAX'

AND caid_related_tax_src.charge_applicable_to_dist_id IS NOT NULL

AND caid_related_tax_src.external_system_reference IS NOT NULL

GROUP BY

caid_related_tax_src.charge_applicable_to_dist_id,

caid_related_tax_src.external_system_reference

)

caid_related_tax

WHERE

caid.lcm_enabled_flag = 'Y'

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.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

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

) invoice_agg

UNION ALL

SELECT

ccia.INVOICE_ID INVOICE_ID,

caid.invoice_number invoice_number,

TO_CHAR(ccia.INVOICE_LINE_NUMBER) INVOICE_LINE_NUMBER,

to_timestamp(MIN(caid.INVOICE_CREATION_DATE)) INVOICE_CREATION_DATE,

caid.procurement_business_unit_id procurement_business_unit_id,

caid.vendor_id vendor_id,

caid.vendor_site_id vendor_site_id,

caid.invoice_line_type invoice_line_type,

caid.currency_code currency_code,

ccia.invoice_amount invoice_amount,

ccia.INVOICE_TAX tax_amount,

ccia.FUNC_CURRENCY_AMOUNT functional_invoice_amount,

ccia.FUNC_CURRENCY_TAX functional_tax_amount,

ccia.FUNC_CURRENCY_CODE functional_currency_code,

MAX(LCM_ASSOCIATION_REQUEST_ID) LCM_ASSOCIATION_REQUEST_ID,

MAX(ONLINE_ASSOC_PROCESS_ID) LCM_ONLINE_ASSOC_PROCESS_ID,

MAX(LCM_ASSOCIATION_PROCESSED_FLAG) LCM_ASSOCIATION_PROCESSED_FLAG,

MAX(caid.LINE_CANCELLED_FLAG) LINE_CANCELLED_FLAG,

MAX(caid.LCM_ENABLED_FLAG) LCM_ENABLED_FLAG,

caid.external_system_reference external_system_reference,

ccia.ASSOCIATION_STATUS assoc_status

FROM

cml_charge_invoice_assocs ccia,

cmr_ap_invoice_dtls caid,

cml_trade_operation_charges ctoc

WHERE

ccia.invoice_id =caid.invoice_id

AND ccia.INVOICE_LINE_NUMBER=caid.INVOICE_LINE_NUMBER

and ccia.trade_operation_charge_id=ctoc.trade_operation_charge_id

and ctoc.service_po_flag='Y'

and ctoc.line_status_code <> 'CANCELED'

GROUP BY

ccia.INVOICE_ID ,

caid.invoice_number ,

ccia.INVOICE_LINE_NUMBER ,

caid.procurement_business_unit_id ,

caid.vendor_id ,

caid.vendor_site_id ,

caid.invoice_line_type,

caid.currency_code,

ccia.invoice_amount ,

ccia.INVOICE_TAX ,

ccia.FUNC_CURRENCY_AMOUNT ,

ccia.FUNC_CURRENCY_TAX ,

ccia.FUNC_CURRENCY_CODE ,

caid.external_system_reference,

ccia.ASSOCIATION_STATUS