CREATE OR REPLACE PROCEDURE public.incremental_sync_delivery_item()
LANGUAGE plpgsql
AS $$
DECLARE
sql VARCHAR(MAX) := '';
staged_record_count BIGINT :=0;
BEGIN
-- Truncate staging table
EXECUTE 'TRUNCATE TABLE stg.lis_12_vcitm;';
EXECUTE 'COPY JOB RUN auto_copy_lis_12_vcitm';
-- Create staging table to store records from DM tables that match the current batch in the staging table
EXECUTE 'drop table if exists temp_delivery_item_match;';
EXECUTE 'create temp table temp_delivery_item_match(like dm.delivery_item); ';
--Identify sls_dstrbn_document_nbr records from DM table that exist in the latest extract
-- We will expire these records in a later step
EXECUTE 'insert into temp_delivery_item_match (
cncl_data_recd_ind
,sls_dstrbn_document_nbr
,Picking_or_Putaway_Confirmation_Status
,Item_Picking_or_Putaway_Status
,sd_doc_item_nbr
,Item_Goods_Movement_Status
,Unloading_Point
,company_code
,sales_district
,billing_block_in_sd_document
,incoterms_part_1
,incoterms_part_2
,customer_group
,sold_to_party
,ship_to_party
,Delivery_Type
,Delivery_Date
,Vendor_account_number
,delivery_block_document_header
,Loading_Point
,route
,sd_document_category
,sales_organization
,shipping_or_receiving_point
,Planned_Goods_Movement_Date
,Actual_Goods_Movement_Date
,last_changed_on
,Promotion
,item_gross_weight
,application_component
,sap_bw_transaction_key
,batch_number
,international_article_number_ean_upc
,record_create_date
,object_creator_name
,entry_time
,billing_block_for_item
,weight_unit
,Business_Area
,Picking_Control_Indicator
,customer_group_1
,customer_group_2
,customer_group_3
,customer_group_4
,customer_group_5
,Consumption_posting
,Actual_quantity_delivered_in_sales_units
,Actualquantitydeliveredinstockkeepingunits
,Warehouse_Number_or_Complex
,storage_location
,Storage_Bin
,StorageType
,material_group
,material_number
,material_entered
,base_unit_of_measure
,material_group_1
,material_group_2
,material_group_3
,material_group_4
,material_group_5
,net_weight_of_the_item
,bill_to_party
,payer
,Item_Type
,product_hierarchy
,forwarding_agent
,sales_document_item_category
,sales_employee
,statistics_date
,sales_qty_sku_denominator
,sales_qty_sku_numerator
,Fixed_shipping_processing_time_in_days
,Variable_shipping_processing_time_in_days
,statistics_update_date
,reference_document_nbr
,reference_item_nbr
,preceding_sd_doc_category
,sales_office
,sales_group
,volume_unit
,item_volume
,sales_unit
,distribution_channel
,own_or_external_plant
,Num_of_delivery_items
,order_header_division
,wbs_element
,fiscal_year_variant
,BW_Extraction_LE_Actual_GI_Delay
,campaign_of_referred_sales_order_item_for_bw_extraction
,odq_change_mode
,odq_counter
,dm_record_start_date
,dm_record_end_date
,dm_is_current
)
select
distinct
cncl_data_recd_ind
,sls_dstrbn_document_nbr
,Picking_or_Putaway_Confirmation_Status
,Item_Picking_or_Putaway_Status
,sd_doc_item_nbr
,Item_Goods_Movement_Status
,Unloading_Point
,company_code
,sales_district
,billing_block_in_sd_document
,incoterms_part_1
,incoterms_part_2
,customer_group
,sold_to_party
,ship_to_party
,Delivery_Type
,Delivery_Date
,Vendor_account_number
,delivery_block_document_header
,Loading_Point
,di.route
,sd_document_category
,sales_organization
,shipping_or_receiving_point
,Planned_Goods_Movement_Date
,Actual_Goods_Movement_Date
,last_changed_on
,Promotion
,item_gross_weight
,application_component
,sap_bw_transaction_key
,batch_number
,international_article_number_ean_upc
,record_create_date
,object_creator_name
,entry_time
,billing_block_for_item
,weight_unit
,Business_Area
,Picking_Control_Indicator
,customer_group_1
,customer_group_2
,customer_group_3
,customer_group_4
,customer_group_5
,Consumption_posting
,Actual_quantity_delivered_in_sales_units
,Actualquantitydeliveredinstockkeepingunits
,Warehouse_Number_or_Complex
,storage_location
,Storage_Bin
,StorageType
,material_group
,material_number
,material_entered
,base_unit_of_measure
,material_group_1
,material_group_2
,material_group_3
,material_group_4
,material_group_5
,net_weight_of_the_item
,bill_to_party
,payer
,Item_Type
,product_hierarchy
,forwarding_agent
,sales_document_item_category
,sales_employee
,statistics_date
,sales_qty_sku_denominator
,sales_qty_sku_numerator
,Fixed_shipping_processing_time_in_days
,Variable_shipping_processing_time_in_days
,statistics_update_date
,reference_document_nbr
,reference_item_nbr
,preceding_sd_doc_category
,sales_office
,sales_group
,volume_unit
,item_volume
,sales_unit
,distribution_channel
,own_or_external_plant
,Num_of_delivery_items
,order_header_division
,wbs_element
,fiscal_year_variant
,BW_Extraction_LE_Actual_GI_Delay
,campaign_of_referred_sales_order_item_for_bw_extraction
,odq_change_mode
,odq_counter
,dm_record_start_date
,dm_record_end_date
,dm_is_current
from
dm.delivery_item di
join
stg.lis_12_vcitm vcitm
on di.sls_dstrbn_document_nbr = vcitm.vbeln
and di.sd_doc_item_nbr = vcitm.posnr
where
dm_is_current = ''1''
;';
sql := 'SELECT COUNT(*) FROM temp_delivery_item_match;';
EXECUTE sql INTO staged_record_count;
RAISE INFO 'Matched records into temp_delivery_item_match: %', staged_record_count;
-- Delete records from target table that also exist in staging table (updated records)
EXECUTE 'DELETE FROM dm.delivery_item
using temp_delivery_item_match tdim
WHERE dm.delivery_item.sls_dstrbn_document_nbr = tdim.sls_dstrbn_document_nbr
and dm.delivery_item.sd_doc_item_nbr = tdim.sd_doc_item_nbr
and
dm.delivery_item.dm_is_current =''1'';';
-- Insert all records from staging table into target table
EXECUTE 'insert into dm.delivery_item (
cncl_data_recd_ind
,sls_dstrbn_document_nbr
,Picking_or_Putaway_Confirmation_Status
,Item_Picking_or_Putaway_Status
,sd_doc_item_nbr
,Item_Goods_Movement_Status
,Unloading_Point
,company_code
,sales_district
,billing_block_in_sd_document
,incoterms_part_1
,incoterms_part_2
,customer_group
,sold_to_party
,ship_to_party
,Delivery_Type
,Delivery_Date
,Vendor_account_number
,delivery_block_document_header
,Loading_Point
,route
,sd_document_category
,sales_organization
,shipping_or_receiving_point
,Planned_Goods_Movement_Date
,Actual_Goods_Movement_Date
,last_changed_on
,Promotion
,item_gross_weight
,application_component
,sap_bw_transaction_key
,batch_number
,international_article_number_ean_upc
,record_create_date
,object_creator_name
,entry_time
,billing_block_for_item
,weight_unit
,Business_Area
,Picking_Control_Indicator
,customer_group_1
,customer_group_2
,customer_group_3
,customer_group_4
,customer_group_5
,Consumption_posting
,Actual_quantity_delivered_in_sales_units
,Actualquantitydeliveredinstockkeepingunits
,Warehouse_Number_or_Complex
,storage_location
,Storage_Bin
,StorageType
,material_group
,material_number
,material_entered
,base_unit_of_measure
,material_group_1
,material_group_2
,material_group_3
,material_group_4
,material_group_5
,net_weight_of_the_item
,bill_to_party
,payer
,Item_Type
,product_hierarchy
,forwarding_agent
,sales_document_item_category
,sales_employee
,statistics_date
,sales_qty_sku_denominator
,sales_qty_sku_numerator
,Fixed_shipping_processing_time_in_days
,Variable_shipping_processing_time_in_days
,statistics_update_date
,reference_document_nbr
,reference_item_nbr
,preceding_sd_doc_category
,sales_office
,sales_group
,volume_unit
,item_volume
,sales_unit
,distribution_channel
,own_or_external_plant
,Num_of_delivery_items
,order_header_division
,wbs_element
,fiscal_year_variant
,BW_Extraction_LE_Actual_GI_Delay
,campaign_of_referred_sales_order_item_for_bw_extraction
,odq_change_mode
,odq_counter
,dm_record_start_date
,dm_record_end_date
,dm_is_current
)
with lis_12_vcitm_latest as (select v.*,row_number() over (partition by vbeln,posnr order by AEDAT DESC,rocancel ) as doc_item_rank from stg.lis_12_vcitm v)
SELECT
distinct
rocancel
,vbeln
,koqua
,kosta
,posnr
,wbsta
,ablad
,bukrs
,bzirk
,faksk
,inco1
,inco2
,kdgrp
,kunag
,kunnr
,lfart
,To_date(lfdat,''YYYYMMDD'')
,lifnr
,lifsk
,lstel
,route
,vbtyp
,vkorg
,vstel
,To_date(wadat,''YYYYMMDD'')
,To_date(wadat_ist,''YYYYMMDD'')
,To_date(aedat,''YYYYMMDD'')
,aktnr
,brgew
,bwapplnm
,bwvorg
,charg
,ean11
,To_date(erdat,''YYYYMMDD'')
,ernam
,erzet
,faksp
,gewei
,gsber
,komkz
,kvgr1
,kvgr2
,kvgr3
,kvgr4
,kvgr5
,kzvbr
,lfimg
,lgmng
,lgnum
,lgort
,lgpla
,lgtyp
,matkl
,matnr
,matwa
,meins
,mvgr1
,mvgr2
,mvgr3
,mvgr4
,mvgr5
,ntgew
,pkunre
,pkunrg
,posar
,prodh
,pspdnr
,pstyv
,pvrtnr
,To_date(stadat,''YYYYMMDD'')
,umvkn
,umvkz
,vbeaf
,vbeav
,To_date(vdatu,''YYYYMMDD'')
,vgbel
,vgpos
,vgtyp
,vkbur
,vkgrp
,voleh
,volum
,vrkme
,vtweg
,werks
,anzlipos
,spara
,ps_posid
,periv
,wa_delay_lf
,mcex_apcampaign
,odq_changemode
,odq_entitycntr
,CURRENT_TIMESTAMP
,CASE WHEN rocancel =''X'' OR doc_item_rank > 1 THEN current_timestamp else ''9999-12-31''::DATE END
,CASE WHEN rocancel =''X'' OR doc_item_rank > 1 THEN ''0'' ELSE ''1'' END
FROM
lis_12_vcitm_latest vcitm ;';
-- Insert Old records with expiry dates
EXECUTE 'insert into dm.delivery_item (
cncl_data_recd_ind
,sls_dstrbn_document_nbr
,Picking_or_Putaway_Confirmation_Status
,Item_Picking_or_Putaway_Status
,sd_doc_item_nbr
,Item_Goods_Movement_Status
,Unloading_Point
,company_code
,sales_district
,billing_block_in_sd_document
,incoterms_part_1
,incoterms_part_2
,customer_group
,sold_to_party
,ship_to_party
,Delivery_Type
,Delivery_Date
,Vendor_account_number
,delivery_block_document_header
,Loading_Point
,route
,sd_document_category
,sales_organization
,shipping_or_receiving_point
,Planned_Goods_Movement_Date
,Actual_Goods_Movement_Date
,last_changed_on
,Promotion
,item_gross_weight
,application_component
,sap_bw_transaction_key
,batch_number
,international_article_number_ean_upc
,record_create_date
,object_creator_name
,entry_time
,billing_block_for_item
,weight_unit
,Business_Area
,Picking_Control_Indicator
,customer_group_1
,customer_group_2
,customer_group_3
,customer_group_4
,customer_group_5
,Consumption_posting
,Actual_quantity_delivered_in_sales_units
,Actualquantitydeliveredinstockkeepingunits
,Warehouse_Number_or_Complex
,storage_location
,Storage_Bin
,StorageType
,material_group
,material_number
,material_entered
,base_unit_of_measure
,material_group_1
,material_group_2
,material_group_3
,material_group_4
,material_group_5
,net_weight_of_the_item
,bill_to_party
,payer
,Item_Type
,product_hierarchy
,forwarding_agent
,sales_document_item_category
,sales_employee
,statistics_date
,sales_qty_sku_denominator
,sales_qty_sku_numerator
,Fixed_shipping_processing_time_in_days
,Variable_shipping_processing_time_in_days
,statistics_update_date
,reference_document_nbr
,reference_item_nbr
,preceding_sd_doc_category
,sales_office
,sales_group
,volume_unit
,item_volume
,sales_unit
,distribution_channel
,own_or_external_plant
,Num_of_delivery_items
,order_header_division
,wbs_element
,fiscal_year_variant
,BW_Extraction_LE_Actual_GI_Delay
,campaign_of_referred_sales_order_item_for_bw_extraction
,odq_change_mode
,odq_counter
,dm_record_start_date
,dm_record_end_date
,dm_is_current
)
SELECT
distinct
cncl_data_recd_ind
,sls_dstrbn_document_nbr
,Picking_or_Putaway_Confirmation_Status
,Item_Picking_or_Putaway_Status
,sd_doc_item_nbr
,Item_Goods_Movement_Status
,Unloading_Point
,company_code
,sales_district
,billing_block_in_sd_document
,incoterms_part_1
,incoterms_part_2
,customer_group
,sold_to_party
,ship_to_party
,Delivery_Type
,Delivery_Date
,Vendor_account_number
,delivery_block_document_header
,Loading_Point
,route
,sd_document_category
,sales_organization
,shipping_or_receiving_point
,Planned_Goods_Movement_Date
,Actual_Goods_Movement_Date
,last_changed_on
,Promotion
,item_gross_weight
,application_component
,sap_bw_transaction_key
,batch_number
,international_article_number_ean_upc
,record_create_date
,object_creator_name
,entry_time
,billing_block_for_item
,weight_unit
,Business_Area
,Picking_Control_Indicator
,customer_group_1
,customer_group_2
,customer_group_3
,customer_group_4
,customer_group_5
,Consumption_posting
,Actual_quantity_delivered_in_sales_units
,Actualquantitydeliveredinstockkeepingunits
,Warehouse_Number_or_Complex
,storage_location
,Storage_Bin
,StorageType
,material_group
,material_number
,material_entered
,base_unit_of_measure
,material_group_1
,material_group_2
,material_group_3
,material_group_4
,material_group_5
,net_weight_of_the_item
,bill_to_party
,payer
,Item_Type
,product_hierarchy
,forwarding_agent
,sales_document_item_category
,sales_employee
,statistics_date
,sales_qty_sku_denominator
,sales_qty_sku_numerator
,Fixed_shipping_processing_time_in_days
,Variable_shipping_processing_time_in_days
,statistics_update_date
,reference_document_nbr
,reference_item_nbr
,preceding_sd_doc_category
,sales_office
,sales_group
,volume_unit
,item_volume
,sales_unit
,distribution_channel
,own_or_external_plant
,Num_of_delivery_items
,order_header_division
,wbs_element
,fiscal_year_variant
,BW_Extraction_LE_Actual_GI_Delay
,campaign_of_referred_sales_order_item_for_bw_extraction
,odq_change_mode
,odq_counter
,dm_record_start_date
,current_timestamp
,0
FROM
temp_delivery_item_match tdim ;';
-- Refresh dm.delivery_item_latest MV. This MV will be used to list the latest version of the delivery_items
EXECUTE 'refresh materialized view archdm.delivery_item_latest;';
END
$$