gistfile1.txt

USE [reporting]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[trn_weakness_report] AS
  IF OBJECT_ID('reporting.dbo.weakness_report', 'U') IS NOT NULL
    DROP TABLE reporting.dbo.weakness_report;
            DECLARE @ts_sa NVARCHAR(14)
	    SET @ts_sa = (SELECT MAX([TIMESTAMP]) FROM sap_erp_replication.dbo.ZSTOCK_MLB_SA)
	    DECLARE @ts_ea NVARCHAR(14)
	    SET @ts_ea = (SELECT MAX([TIMESTAMP]) FROM sap_erp_replication.dbo.ZSTOCK_MLB_EA)
	    DECLARE @LF NVARCHAR(4)
	    SET @LF = '9000'
	    DECLARE @WR NVARCHAR(4)
	    SET @WR = '9001';
	    
	    
	    WITH ITEM AS (SELECT RIGHT(item_code, 10) AS item_code, item_name, main_category, item_type, legacy_item_code
	                       FROM dataplatform_feed.dbo.item
	                       GROUP BY item_code,
	                                item_name,
	                                legacy_item_code,
	                                main_category,
	                                item_type) ,
	         STOCK_DATA AS (SELECT WERKS                                            AS location,
	                               RIGHT(MATNR, 10)                                 AS matnr,
	                               CATLOCN,
	                               BST_STULI                                        AS bst
	                        FROM sap_erp_replication.dbo.ZSTOCK_MLB_SA
	                        WHERE [TIMESTAMP] = @ts_sa
	                          AND [WERKS] in (@WR, @LF)
	                          AND [MTART] = 'ZHAW'
	                          AND [STULI_COMPLET] = '1'
	        UNION ALL
	
	        SELECT WERKS                                            AS location,
	               RIGHT(MATNR, 10)                                 AS matnr,
	               CATLOCN,
	               BST_PHYS                                         AS bst
	        FROM sap_erp_replication.dbo.ZSTOCK_MLB_EA
	        WHERE [TIMESTAMP] = @ts_ea
	          AND [WERKS] in ('9000', '9001')
	          AND [MTART] = 'ZHAW'),
			  SAP_STOCK_DATA AS (SELECT MATNR,
	                                   COALESCE(SUM(CASE WHEN CATLOCN = 'FF' AND LOCATION = @LF THEN BST ELSE 0 END),0)  AS lf_sap_stock_ff,
	                                   COALESCE(SUM(CASE WHEN CATLOCN = 'FF' AND LOCATION = @WR THEN BST ELSE 0 END),0)  AS wr_sap_stock_ff
	                            FROM STOCK_DATA
                                GROUP BY MATNR)
  SELECT SO.order_line_key,
         SO.order_number,
         SO.order_line_number,
         purchase_order_number,
         purchase_order_line_number,
         PO.delivery_document_number                                           AS inbound_delivery_doc_nr,
         PO.delivery_item_line_number                                          AS inbound_delivery_doc_line,
         LIPS.VBELN                                                            AS delivery_doc_nr,
         LIPS.POSNR                                                            AS delivery_doc_line,
         INVOICES.VBELN                                                        AS invoice_number,
         INVOICES.POSNR                                                        AS invoice_line_number,
         SO.transportation_number,
         SO.tracking_number                                                    AS ERP_tracking_number,
         EM.tracking_number                                                    AS EM_tracking_number,
         SO.return_order_number,
         SO.return_order_line,
         RT.credit_memo_doc_number,
         customer_id,
         SO.sku                                                                AS material,
         SO.paid_prepayment_status,
         SO.authorization_status,
         CASE
           WHEN SO.billing_block_sd_document = '' AND SO.delivery_block_doc_header = '' THEN 1
           ELSE 0 END                                                          AS release_status,
         CASE
           WHEN CANC_FLAG.cancellationflag IN ('X', 'K', 'S') THEN 1
           ELSE 0 END                                                          AS cancellation_status,
         purchase_order_created_flag,
         CASE WHEN PO.supplier_delivery_date_AB IS NOT NULL THEN 1 ELSE 0 END  AS AB_status,
         CASE WHEN PO.supplier_delivery_date_LA IS NOT NULL THEN 1 ELSE 0 END  AS LA_status,
         CASE WHEN PO.delivery_document_number IS NOT NULL THEN 1 ELSE 0 END   AS inbound_delivery_creation_status,
         CASE WHEN LIPS.VBELN IS NOT NULL THEN 1 ELSE 0 END                    AS delivery_doc_creation_status,
         CASE WHEN PO.actual_goods_received_date IS NOT NULL THEN 1 ELSE 0 END AS we_status,
         CASE
           WHEN LIKP.WADAT_IST IS NOT NULL AND LIKP.WADAT_IST <> '00000000' THEN 1
           ELSE 0 END                                                          AS wa_status,
         CASE WHEN LIPS.LFIMG = 0 THEN 1 ELSE 0 END                            AS pick_quantity,
         CASE WHEN SO.transportation_number IS NULL THEN 1 ELSE 0 END          AS transport_creation_status,
         CASE
           WHEN LIKP.PODAT IS NOT NULL AND LIKP.PODAT <> '00000000' THEN 1
           ELSE 0 END                                                          AS delivery_pod_status,
         SO.pod_status_on_item_level,
         EM.delivery_status                                                    AS em_delivery_status,
         EM.av_return_status                                                   AS em_av_return_status,
         CASE WHEN INVOICES.VBELN IS NOT NULL THEN 1 ELSE 0 END                AS invoice_status,
         INVOICES.RFBSK                                                        AS fi_posting_status,
         CASE WHEN SO.return_order_number IS NOT NULL THEN 1 ELSE 0 END        AS return_status,
         CASE WHEN RT.credit_memo_doc_number IS NOT NULL THEN 1 ELSE 0 END     AS credit_memo_status,
         EM.max_event_id                                                       AS last_eventid,
         EM.max_event                                                          AS last_event,
         CASE
           WHEN PO.actual_goods_received_date IS NOT NULL THEN 'WE'
           WHEN PO.supplier_delivery_date_LA IS NOT NULL THEN 'LA'
           WHEN PO.supplier_delivery_date_AB IS NOT NULL THEN 'AB'
           ELSE '' END                                                         AS general_po_status,
         CASE
           WHEN INVOICES.VBELN IS NOT NULL THEN 'invoiced'
           WHEN CANC_FLAG.CancellationFlag IN ('K', 'S', 'X') THEN 'cancelled'
           WHEN (EM.delivery_status = 1 AND EM.av_return_status = 0)
             OR (LIKP.PODAT IS NOT NULL AND LIKP.PODAT <> '00000000')
             OR (RT.credit_memo_doc_number IS NOT NULL)
             OR (PO.actual_goods_received_date IS NOT NULL AND SO.strecke_split = 'Selfsender')
             THEN 'delivered'
           WHEN LIPS.LFIMG = 1 AND LIKP.WADAT_IST IS NOT NULL
                  AND LIKP.WADAT_IST <> '00000000' AND LEFT(SO.plant, 1) = '9'
             OR
                PO.actual_goods_received_date IS NOT NULL AND LEFT(SO.plant, 1) = '2' THEN 'shipped'
           WHEN LIPS.VBELN IS NOT NULL AND LIPS.LFIMG = 1 AND LEFT(SO.plant, 1) = '9' THEN 'delivery_created'
           WHEN SO.billing_block_sd_document <> '' OR SO.delivery_block_doc_header <> '' THEN 'unreleased'
           ELSE 'disposition'
           END                                                                 AS general_line_status,
         SO.order_date,
         SO.order_creation_date,
         CASE
           WHEN CANC_FLAG.FSH_CANDATE <> '00000000' THEN CANC_FLAG.FSH_CANDATE
           ELSE NULL END                                                       AS cancellation_date,
         EM.order_release_date,
         SO.purchase_order_date,
         PO.supplier_delivery_date_AB,
         PO.supplier_delivery_date_LA,
         EM.ewm_distribution_date,
         SO.planned_delivery_date,
         PO.actual_goods_received_date                                         AS we_date,
         CASE WHEN LIKP.KODAT <> '00000000' THEN LIKP.KODAT ELSE NULL END
                                                                               AS picking_date,
         CASE WHEN LIKP.WADAT_IST <> '00000000' THEN LIKP.WADAT_IST ELSE NULL END
                                                                               AS wa_date,
         CASE WHEN LIKP.PODAT <> '00000000' THEN LIKP.PODAT ELSE NULL END
                                                                               AS pod_date,
         EM.max_event_date                                                     AS max_em_event_date,
         EM.delivery_date                                                      AS em_delivery_date,
         EM.av_return_date                                                     AS em_av_return_date,
         INVOICES.FKDAT                                                        AS invoice_date,
         RT.return_order_line_creation_date,
         SO.delivery_doc_creation_date,
         RT.credit_memo_billing_date,
         DATEADD(DAY, -DATEDIFF(DAY, SO.loading_date, SO.schedule_line_date),
                 initial_accepted_delivery_date)                               AS initial_shipment_date_calc,
         SO.loading_date                                                       AS updated_shipment_date,
         initial_accepted_delivery_date,
         SO.planned_shipment_completion_date,
         SO.schedule_line_date                                                 AS updated_delivery_date,
         SO.is_main_line,
         CANC_FLAG.cancellationflag                                            AS cancellation_flag,
         cancellation_reason,
         cancellation_reason_description,
         SO.billing_block_sd_document                                          AS delivery_blocker_header,
         SO.delivery_block_doc_header                                          AS invoice_blocker_header,
         SO.invoice_blocker_line,
         LIPS.PDSTA                                                            AS LEB_status,
         LIKP.PDSTK                                                            AS LEB_relevance,
         SO.payment_terms,
         SO.payment_terms_description,
         SO.sales_organization,
         SO.sales_channel,
         SO.distribution_channel,
         SO.transportation_group,
         SO.transit_duration_days,
         country_flag,
         item_category,
         distribution_type,
         strecke_split,
         plant,
         RIGHT(SO.sku, 10)                                                     AS article_number,
         sku_description                                                       AS article_name,
         material_type                                                         AS article_type,
         CASE
           WHEN material_type = 'YAGP' THEN 'BUNDLE'
           WHEN material_type = 'ZDIN' THEN 'SERVICE'
           WHEN material_type = 'ZNLA' THEN 'TRUSTED_SHOP'
           WHEN material_type = 'ZDUM' THEN 'DUMMY'
           WHEN material_type = 'ZERS' THEN 'SPAREPART'
           WHEN material_type = 'ZFAS' THEN 'FABRIC'
           WHEN material_type = 'ZHAW' THEN 'ARTICLE'
           WHEN material_type = 'ZPAC' THEN 'ARTICLE_PACK'
           WHEN material_type = 'ZSET' THEN 'SET'
           WHEN material_type = 'ZVER' THEN 'PACKAGE'
           ELSE material_type
           END                                                                 AS 'article_type_name',
         SO.general_item_category_group,
         ITEM.main_category,
         cross_plant_material_status,
         article_plant_disposition_status,
         SO.availability_checking_group                                        AS article_order_disposition_status,
         SO.loading_group,
         shipping_or_receiving_point,
         SO.route,
         SO.route_description,
         SO.order_type,
         SO.order_type_description,
         purchase_order_type,
         delivery_distribution_to_EWM_status,
         SO.supplier_name,
         supplier,
         carrier_code                                                          AS carrier_partner_code,
         SO.carrier_name                                                       AS carrier_partner_name,
         CASE
           WHEN SUBSTRING(SO.ROUTE, 3, 2) = 'SP' THEN 'Self' --'Selfsender'
           WHEN SUBSTRING(SO.ROUTE, 3, 2) = 'DH' THEN 'DHL' --'DHL Vertriebs GmbH & Co. OHG'
           WHEN SUBSTRING(SO.ROUTE, 3, 2) = 'DP' THEN 'DPD' --'DPD Deutschland GmbH'
           WHEN SUBSTRING(SO.ROUTE, 3, 2) = 'GW' THEN 'GBW' --'Gebrüder Weiss GmbH'
           WHEN SUBSTRING(SO.ROUTE, 3, 2) = 'UP' THEN 'UPS' --'UPS United Parcel Service Inc. & Co'
           WHEN SUBSTRING(SO.ROUTE, 3, 2) = 'DY' THEN 'DYN' --'DYNALOGIC BENELUX B.V.'
           WHEN SUBSTRING(SO.ROUTE, 3, 2) = 'PC' THEN 'PCH' --'Post CH'
           WHEN SUBSTRING(SO.ROUTE, 3, 1) = 'H' THEN 'HERMES' --'Hermes Einrichtungs Service GmbH &'
           WHEN SUBSTRING(SO.ROUTE, 3, 2) = 'RH' THEN 'RHENUS' --'Rhenus Home Delivery GmbH'
           WHEN SUBSTRING(SO.ROUTE, 3, 2) = 'VI' THEN 'VIR' --'Groupe VIR Transport'
           ELSE '' END                                                         AS carrier_route_name,
         RT.order_type                                                         AS 'return_order_type',
         CASE
           WHEN customer_id = '0010000010' THEN 'outlet'
           WHEN customer_id = '0010000000' THEN 'navision'
           /*  WHEN customer_id = 'SHOWROOMS'	THEN 'retail' -> tbd */
           ELSE 'external' END                                                 AS 'customer_type',
         LIPS.LFIMG                                                            AS delivery_doc_qty,
         SO.confirmed_quantity,
         number_of_collis,
         cumulative_order_qty_sales_units,
         CASE
           WHEN SO.plant = '9000' THEN ST.lf_sap_stock_ff
           WHEN SO.plant = '9001' THEN ST.wr_sap_stock_ff
           /*	WHEN SO.plant = '9002' THEN ST.ha_sap_stock_ff */
           ELSE 0 END                                                          AS warehouse_physical_stock_ff,
         SO.net_value_in_co_currency,
         SO.net_value_in_doc_currency,
         CASE
           WHEN SO.net_value_in_co_currency = 0 THEN 0
           ELSE SO.net_value_in_doc_currency / SO.net_value_in_co_currency * gross_value_in_doc_currency END
                                                                               AS gross_value_in_co_currency,
         gross_value_in_doc_currency

         INTO reporting.dbo.weakness_report
  FROM reporting.dbo.sales_order_operations SO
         LEFT JOIN reporting.dbo.event_manager_aggregation EM ON EM.order_number = SO.order_number AND
                                                                 EM.order_line_number = SO.order_line_number
         LEFT JOIN  ITEM  ON ITEM.item_code = RIGHT(SO.sku, 10)
         LEFT JOIN  SAP_STOCK_DATA  ST ON ST.MATNR = ITEM.item_code
         LEFT JOIN reporting.dbo.return_order_operations RT ON RT.return_order = SO.first_return_order_number AND
                                                               RT.return_order_line = SO.return_order_line
         LEFT JOIN reporting.dbo.purchase_order_aggregated PO ON PO.parent_sales_order_number = SO.order_number AND
                                                                 PO.parent_sales_item_line_number = SO.order_line_number /* Additional Joins: 1) Invoice, 2) Outbound Shipment, 3) Cancellations */ -- Invoice Join
         LEFT JOIN (SELECT MAX(VBRP.VBELN) AS 'VBELN',
                           MAX(VBRP.POSNR) AS 'POSNR',
                           MAX(VBRP.VGBEL) AS 'VGBEL',
                           MAX(VBRP.VGPOS) AS 'VGPOS',
                           MAX(VBRK.RFBSK) AS 'RFBSK',
                           MAX(VBRK.FKDAT) AS 'FKDAT',
                           VBRP.AUBEL,
                           VBRP.AUPOS

                    FROM sap_erp_replication.dbo.[VBRP] VBRP
                           LEFT JOIN sap_erp_replication.dbo.[VBRK] VBRK ON VBRK.MANDT = VBRP.MANDT
                      AND VBRK.VBELN = VBRP.VBELN
                      AND VBRK.MANDT = VBRP.MANDT
                           LEFT JOIN sap_erp_replication.dbo.[VBRK] VBRKc ON VBRKc.MANDT = VBRP.MANDT
                      AND VBRKc.SFAKN = VBRP.VBELN
                      AND VBRKc.MANDT = VBRP.MANDT
                           LEFT JOIN sap_erp_replication.dbo.VBAP ON VBAP.MANDT = VBRP.MANDT
                      AND VBAP.VBELN = VBRP.AUBEL
                      AND VBAP.POSNR = VBRP.AUPOS
                      AND VBAP.MANDT = VBRP.MANDT

                    WHERE VBRP.MANDT = '200'
                      AND VBRK.FKART IN ('YF2')
                      AND VBRK.FKSTO = ''
                      AND (VBRP.NETWR <> 0 OR VBAP.NETWR = VBRP.NETWR)
                      AND VBRKc.VBELN IS NULL

                    GROUP BY VBRP.AUBEL, VBRP.AUPOS) INVOICES ON INVOICES.AUBEL = SO.order_number
    AND INVOICES.AUPOS = (CASE
                            WHEN RIGHT(SO.order_number, 2) = '99' OR SO.order_type IN ('YTAO')
                              THEN SO.order_line_number
                            ELSE (LEFT(SO.order_line_number, 4) + '00') END) -- Outbound Shipment Key
         LEFT JOIN (SELECT MANDT, VGBEL, VGPOS, MAX(VBELN) AS VBELN, MIN(POSNR) AS POSNR
                    FROM sap_erp_replication.dbo.LIPS
                    GROUP BY MANDT, VGBEL, VGPOS) LIPS_MAX ON LIPS_MAX.[MANDT] = '200'
    AND LIPS_MAX.VGBEL = SO.order_number
    AND
                                                              LIPS_MAX.VGPOS = SO.order_line_number -- Outbound Shipment Line
         LEFT JOIN sap_erp_replication.dbo.LIPS ON LIPS.MANDT = LIPS_MAX.MANDT
    AND LIPS.VBELN = LIPS_MAX.VBELN
    AND LIPS.POSNR = LIPS_MAX.POSNR -- Outbound Shipment Header
         LEFT JOIN sap_erp_replication.dbo.LIKP LIKP ON LIKP.MANDT = LIPS_MAX.MANDT
    AND
                                                        LIKP.VBELN = LIPS_MAX.VBELN -- Sales Line Head Cancellation Flag
         LEFT JOIN (SELECT MANDT, VBELN, POSNR, ZZFLAG_CANCELLED AS 'CancellationFlag', FSH_CANDATE
                    FROM sap_erp_replication.dbo.VBAP
                    WHERE UEPOS = '000000'
                      AND RIGHT(POSNR, 2) = '00'
                      AND VBAP.MANDT = '200') CANC_FLAG ON CANC_FLAG.VBELN = SO.order_number
    AND LEFT(CANC_FLAG.POSNR, 4) = LEFT(SO.order_line_number, 4)


    /* CONDITIONS */

  WHERE SO.order_type NOT IN ('YG2',
                              'YGA2',
                              'YRUZ',
                              'YGUZ',
                              'YRE2',
                              'YRMU',
                              'ZOVW')                     -- sales/exchanges/spareparts only, no outlet, returns, credit memos
    AND CANC_FLAG.CancellationFlag NOT IN ('X', 'K', 'S') -- no cancellations
    AND (
      INVOICES.VBELN IS NULL -- no valid invoice
      OR
      INVOICES.RFBSK <> 'C' -- or invoice not successfully transfered to finance
    )
  go