CML_INVOICE_LINE_DETAILS_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_ONLINE_ASSOC_PROCESS_ID LCM_ASSOCIATION_PROCESSED_FLAG LINE_CANCELLED_FLAG LCM_ENABLED_FLAG EXTERNAL_SYSTEM_REFERENCE ASSOC_STATUS |
Query
SQL_Statement |
---|
SELECT to_number(invoice_dist.invoice_id) invoice_id, TO_CHAR(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.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 to_number(ccia.INVOICE_ID ) INVOICE_ID, TO_CHAR(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 |