CML_INVOICE_LINE_DTLS_CHRGS

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_dist.invoice_id ) invoice_id, ( invoice_dist.invoice_number ) invoice_number, to_char(invoice_dist.invoice_line_number) invoice_line_number, TO_TIMESTAMP(MIN(invoice_dist.invoice_creation_date)) invoice_creation_date, TO_NUMBER(invoice_dist.procurement_business_unit_id) procurement_business_unit_id , TO_NUMBER(invoice_dist.vendor_id) vendor_id, TO_NUMBER(invoice_dist.vendor_site_id) vendor_site_id, to_char(invoice_dist.invoice_line_type) invoice_line_type, to_char(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, to_char(invoice_dist.invoicing_bu_func_curr_code) functional_currency_code , TO_NUMBER(MAX(lcm_association_request_id)) lcm_association_request_id , TO_NUMBER(MAX(online_assoc_process_id)) lcm_online_assoc_process_id , to_char(MAX(nvl(lcm_association_processed_flag, 'Y'))) lcm_association_processed_flag , to_char(MAX(invoice_dist.line_cancelled_flag)) line_cancelled_flag, to_char(MAX(invoice_dist.lcm_enabled_flag)) lcm_enabled_flag, to_char(invoice_dist.external_system_reference) external_system_reference , to_char( 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, 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, ( 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_related_tax.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 WHERE active_flag = 'Y' 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 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, TO_NUMBER(caid.procurement_business_unit_id) procurement_business_unit_id, TO_NUMBER(caid.vendor_id) vendor_id, TO_NUMBER(caid.vendor_site_id) vendor_site_id, to_char(caid.invoice_line_type) invoice_line_type, to_char(caid.currency_code) currency_code, TO_NUMBER(ccia.invoice_amount) invoice_amount, TO_NUMBER(ccia.invoice_tax) tax_amount, TO_NUMBER(ccia.func_currency_amount) functional_invoice_amount, TO_NUMBER(ccia.func_currency_tax) functional_tax_amount, to_char(ccia.func_currency_code) functional_currency_code, TO_NUMBER(MAX(lcm_association_request_id)) lcm_association_request_id, TO_NUMBER(MAX(online_assoc_process_id)) lcm_online_assoc_process_id, to_char(MAX(lcm_association_processed_flag)) lcm_association_processed_flag, to_char(MAX(caid.line_cancelled_flag)) line_cancelled_flag, to_char(MAX(caid.lcm_enabled_flag)) lcm_enabled_flag, to_char(caid.external_system_reference) external_system_reference, to_char(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_dist.invoice_id ) invoice_id,

( invoice_dist.invoice_number ) invoice_number,

to_char(invoice_dist.invoice_line_number) invoice_line_number,

TO_TIMESTAMP(MIN(invoice_dist.invoice_creation_date)) invoice_creation_date,

TO_NUMBER(invoice_dist.procurement_business_unit_id) procurement_business_unit_id

,

TO_NUMBER(invoice_dist.vendor_id) vendor_id,

TO_NUMBER(invoice_dist.vendor_site_id) vendor_site_id,

to_char(invoice_dist.invoice_line_type) invoice_line_type,

to_char(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,

to_char(invoice_dist.invoicing_bu_func_curr_code) functional_currency_code

,

TO_NUMBER(MAX(lcm_association_request_id)) lcm_association_request_id

,

TO_NUMBER(MAX(online_assoc_process_id)) lcm_online_assoc_process_id

,

to_char(MAX(nvl(lcm_association_processed_flag, 'Y'))) lcm_association_processed_flag

,

to_char(MAX(invoice_dist.line_cancelled_flag)) line_cancelled_flag,

to_char(MAX(invoice_dist.lcm_enabled_flag)) lcm_enabled_flag,

to_char(invoice_dist.external_system_reference) external_system_reference

,

to_char(

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

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

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,

(

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

WHERE

active_flag = 'Y'

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

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,

TO_NUMBER(caid.procurement_business_unit_id) procurement_business_unit_id,

TO_NUMBER(caid.vendor_id) vendor_id,

TO_NUMBER(caid.vendor_site_id) vendor_site_id,

to_char(caid.invoice_line_type) invoice_line_type,

to_char(caid.currency_code) currency_code,

TO_NUMBER(ccia.invoice_amount) invoice_amount,

TO_NUMBER(ccia.invoice_tax) tax_amount,

TO_NUMBER(ccia.func_currency_amount) functional_invoice_amount,

TO_NUMBER(ccia.func_currency_tax) functional_tax_amount,

to_char(ccia.func_currency_code) functional_currency_code,

TO_NUMBER(MAX(lcm_association_request_id)) lcm_association_request_id,

TO_NUMBER(MAX(online_assoc_process_id)) lcm_online_assoc_process_id,

to_char(MAX(lcm_association_processed_flag)) lcm_association_processed_flag,

to_char(MAX(caid.line_cancelled_flag)) line_cancelled_flag,

to_char(MAX(caid.lcm_enabled_flag)) lcm_enabled_flag,

to_char(caid.external_system_reference) external_system_reference,

to_char(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