USE [wm_db] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [his].[vw_his_inventory_master_changes] AS -- select * from his.vw_his_inventory_master_changes SELECT imct.inventory_master_ky , 'Inventory' AS change_source -- Old Stock/ICN fields , ISNULL(imct.from_inventory_control_nbr_ky, ISNULL(imct.to_inventory_control_nbr_ky, 0)) AS from_inventory_control_nbr_ky , from_icn.inventory_control_nbr AS from_inventory_control_nbr , from_icn.stock_nbr_ky AS from_stock_nbr_ky , from_stock.stock_nbr AS from_stock_nbr , from_stock.item_desc AS from_stock_desc , from_sui.un_iss_cd AS from_stocking_ui_cd , from_sui.dspl_desc AS from_stocking_ui_desc , from_stock.unit_price / from_stock.ui_conversion_factor AS from_stocking_ui_price , from_rct.commodity_type_ky AS from_reportable_commodity_type_ky , from_rct.commodity_type_id AS from_reportable_commodity_type_id , from_rct.commodity_description AS from_reportable_commodity_desc , from_sct.commodity_type_ky AS from_security_commodity_type_ky , from_sct.commodity_type_id AS from_security_commodity_type_id , from_sct.commodity_description AS from_security_commodity_desc , from_sal.stores_account_cd AS from_stores_account_cd , from_sal.display_description AS from_stores_account_desc , from_sil.stk_itm_cd AS from_stock_item_cd , from_sil.dspl_desc AS from_stock_item_desc , from_fsc.fed_sply_cls_cd AS from_federal_supply_class_cd , from_fsc.dspl_desc AS from_federal_supply_class_desc , from_lin.lin_tamcn_ky AS from_lin_tamcn_ky , from_lin.lin_tamcn_id AS from_lin_tamcn_id , from_lin.lin_tamcn_desc AS from_lin_tamcn_desc , from_icn.asset_id AS from_asset_id , from_icn.prfid AS from_prfid , from_icn.manufacturer_cage AS from_manufacturer_cage , from_icn.manufacturer_contract AS from_manufacturer_contract , from_icn.manufacturer_lot AS from_manufacturer_lot , from_icn.manufactured_date AS from_manufactured_date , from_icn.serial_nbr AS from_serial_nbr , from_icn.secondary_serial_nbr AS from_secondary_serial_nbr , from_icn.last_inspection_date AS from_last_inspection_date , from_icn.next_inspection_date AS from_next_inspection_date , from_icn.inspected_by AS from_inspected_by , from_icn.last_test_date AS from_last_test_date , from_icn.last_maintenance_date AS from_last_maintenance_date , from_icn.last_calibration_date AS from_last_calibration_date , from_icn.meter_reading AS from_meter_reading , from_icn.expiration_date AS from_expiration_date , from_icn.extended_flg AS from_extended_flg , from_icn.failed_flg AS from_failed_flg , from_icn.uii_ky AS from_uii_ky , from_uii.c_uii AS from_uii , from_icn.uii_status_cd AS from_uii_status_cd -- New Stock/ICN fields , ISNULL(imct.to_inventory_control_nbr_ky, 0) AS to_inventory_control_nbr_ky , to_icn.inventory_control_nbr AS to_inventory_control_nbr , to_icn.stock_nbr_ky AS to_stock_nbr_ky , to_stock.stock_nbr AS to_stock_nbr , to_stock.item_desc AS to_stock_desc , to_sui.un_iss_cd AS to_stocking_ui_cd , to_sui.dspl_desc AS to_stocking_ui_desc , to_stock.unit_price / to_stock.ui_conversion_factor AS to_stocking_ui_price , to_rct.commodity_type_ky AS to_reportable_commodity_type_ky , to_rct.commodity_type_id AS to_reportable_commodity_type_id , to_rct.commodity_description AS to_reportable_commodity_desc , to_sct.commodity_type_ky AS to_security_commodity_type_ky , to_sct.commodity_type_id AS to_security_commodity_type_id , to_sct.commodity_description AS to_security_commodity_desc , to_sal.stores_account_cd AS to_stores_account_cd , to_sal.display_description AS to_stores_account_desc , to_sil.stk_itm_cd AS to_stock_item_cd , to_sil.dspl_desc AS to_stock_item_desc , to_fsc.fed_sply_cls_cd AS to_federal_supply_class_cd , to_fsc.dspl_desc AS to_federal_supply_class_desc , to_lin.lin_tamcn_ky AS to_lin_tamcn_ky , to_lin.lin_tamcn_id AS to_lin_tamcn_id , to_lin.lin_tamcn_desc AS to_lin_tamcn_desc , to_icn.asset_id AS to_asset_id , to_icn.prfid AS to_prfid , to_icn.manufacturer_cage AS to_manufacturer_cage , to_icn.manufacturer_contract AS to_manufacturer_contract , to_icn.manufacturer_lot AS to_manufacturer_lot , to_icn.manufactured_date AS to_manufactured_date , to_icn.serial_nbr AS to_serial_nbr , to_icn.secondary_serial_nbr AS to_secondary_serial_nbr , to_icn.last_inspection_date AS to_last_inspection_date , to_icn.next_inspection_date AS to_next_inspection_date , to_icn.inspected_by AS to_inspected_by , to_icn.last_test_date AS to_last_test_date , to_icn.last_maintenance_date AS to_last_maintenance_date , to_icn.last_calibration_date AS to_last_calibration_date , to_icn.meter_reading AS to_meter_reading , to_icn.expiration_date AS to_expiration_date , to_icn.extended_flg AS to_extended_flg , to_icn.failed_flg AS to_failed_flg , to_icn.uii_ky AS to_uii_ky , to_uii.c_uii AS to_uii , to_icn.uii_status_cd AS to_uii_status_cd -- Old Inventory Fields , from_cc.cond_cd AS from_condition_cd , from_cc.dspl_desc AS from_condition_desc , imct.from_owning_dodaac AS from_owning_dodaac , from_proj.project_ky AS from_project_ky , from_proj.project_cd AS from_project_cd , imct.from_qty AS from_qty , from_iur.reason_cd AS from_reason_cd , from_iur.description AS from_reason_cd_desc , from_c.container_ky AS from_container_ky , from_c.container_id AS from_container_id , from_c.container_desc AS from_container_desc , from_l.location_ky AS from_location_ky , from_l.location_id AS from_location_id , from_l.description AS from_location_desc , from_f.facility_ky AS from_facility_ky , from_f.facility_nbr AS from_facility_nbr , from_f.description AS from_facility_desc , from_w.warehouse_ky AS from_warehouse_ky , from_w.warehouse_dodaac as from_warehouse_dodaac , from_w.name AS from_warehouse_name , from_w.description AS from_warehouse_desc , from_w.routing_identifer_cd as from_warehouse_ric -- New Inventory Fields , to_cc.cond_cd AS to_condition_cd , to_cc.dspl_desc AS to_condition_desc , imct.to_owning_dodaac AS to_owning_dodaac , to_proj.project_ky AS to_project_ky , to_proj.project_cd AS to_project_cd , imct.to_qty AS to_qty , to_iur.reason_cd AS to_reason_cd , to_iur.description AS to_reason_cd_desc , to_c.container_ky AS to_container_ky , to_c.container_id AS to_container_id , to_c.container_desc AS to_container_desc , to_l.location_ky AS to_location_ky , to_l.location_id AS to_location_id , to_l.description AS to_location_desc , to_f.facility_ky AS to_facility_ky , to_f.facility_nbr AS to_facility_nbr , to_f.description AS to_facility_desc , to_w.warehouse_ky AS to_warehouse_ky , to_w.warehouse_dodaac AS to_warehouse_dodaac , to_w.name AS to_warehouse_name , to_w.description AS to_warehouse_desc , to_w.routing_identifer_cd as to_warehouse_ric -- Additional Tier Info Fields , to_s.site_ky AS to_site_ky , to_s.name AS to_site_name , to_s.description AS to_site_desc , to_r.region_ky AS to_region_ky , to_r.name AS to_region_name , to_r.description AS to_region_desc , to_lp.logistics_program_ky AS to_logistics_program_ky , to_lp.name AS to_logistics_program_name , to_lp.description AS to_logistics_program_desc -- Other Info Fields , imct.document_nbr_ky , doc.document_nbr , doc.to_dodaac , doc.from_dodaac , imct.reason_desc , imct.pgm_id , imct.as_of_dt_tm , imct.change_by , imct.as_of_dt_tm AS history_record_generated_dt_tm FROM wm.inventory_master_change_tracking imct -- Get old ICN record that was referenced by the from ICN key (as of IM date) CROSS APPLY (SELECT TOP 1 inventory_control_nbr_ky,wm_inventory_control_nbr_his_ky,inventory_control_nbr,stock_nbr_ky,asset_id ,prfid,manufacturer_cage,manufacturer_contract,manufacturer_lot,manufactured_date,serial_nbr,secondary_serial_nbr ,last_inspection_date,next_inspection_date,inspected_by,last_test_date,last_maintenance_date,last_calibration_date ,meter_reading,expiration_date,extended_flg,failed_flg,uii_ky,uii_status_cd,history_record_generated_dt_tm FROM his.wm_inventory_control_nbr asOf WHERE (asOf.inventory_control_nbr_ky = imct.from_inventory_control_nbr_ky OR asOf.inventory_control_nbr_ky = imct.to_inventory_control_nbr_ky) AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_icn -- Get new ICN record that was referenced by the to ICN key (as of IM date) CROSS APPLY (SELECT TOP 1 inventory_control_nbr_ky,wm_inventory_control_nbr_his_ky,inventory_control_nbr,stock_nbr_ky,asset_id ,prfid,manufacturer_cage,manufacturer_contract,manufacturer_lot,manufactured_date,serial_nbr,secondary_serial_nbr ,last_inspection_date,next_inspection_date,inspected_by,last_test_date,last_maintenance_date,last_calibration_date ,meter_reading,expiration_date,extended_flg,failed_flg,uii_ky,uii_status_cd,history_record_generated_dt_tm FROM his.wm_inventory_control_nbr asOf WHERE asOf.inventory_control_nbr_ky = imct.to_inventory_control_nbr_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_icn -- Get old container record referenced by the from container key (as of IM date) OUTER APPLY (SELECT TOP 1 wm_container_his_ky,location_ky,container_ky,container_id,container_desc FROM his.wm_container asOf WHERE asOf.container_ky = imct.from_container_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_c -- Get new container record referenced by the to container key (as of IM date) OUTER APPLY (SELECT TOP 1 wm_container_his_ky,location_ky,container_ky,container_id,container_desc FROM his.wm_container asOf WHERE asOf.container_ky = imct.to_container_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_c -- Get old location record referenced by the from location key (as of IM date) CROSS APPLY (SELECT TOP 1 wm_location_his_ky,zone_ky,location_ky,location_id,description FROM his.wm_location asOf WHERE (asOf.location_ky = imct.from_location_ky or asOf.location_ky = from_c.location_ky) AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_l -- Get new location record referenced by the to location key (as of IM date) CROSS APPLY (SELECT TOP 1 wm_location_his_ky,zone_ky,location_ky,location_id,description FROM his.wm_location asOf WHERE (asOf.location_ky = imct.to_location_ky or asOf.location_ky = to_c.location_ky) AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_l -- Get old zone record referenced by the old location (as of IM date) CROSS APPLY (SELECT TOP 1 wm_zone_his_ky,facility_ky FROM his.wm_zone asOf WHERE asOf.zone_ky = from_l.zone_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_z -- Get new zone record referenced by the new location (as of IM date) CROSS APPLY (SELECT TOP 1 wm_zone_his_ky,facility_ky FROM his.wm_zone asOf WHERE asOf.zone_ky = to_l.zone_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_z -- Get old facility record referenced by the old zone (as of IM date) CROSS APPLY (SELECT TOP 1 wm_facility_his_ky,facility_ky,facility_nbr,description FROM his.wm_facility asOf WHERE asOf.facility_ky = from_z.facility_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_f -- Get new facility record referenced by new old zone (as of IM date) CROSS APPLY (SELECT TOP 1 wm_facility_his_ky,facility_ky,facility_nbr,description FROM his.wm_facility asOf WHERE asOf.facility_ky = to_z.facility_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_f -- Get old warehouse record referenced by the old facility (as of IM date) CROSS APPLY (SELECT TOP 1 wm_warehouse_his_ky,site_ky,warehouse_ky,warehouse_dodaac,name,description,routing_identifer_cd FROM his.wm_warehouse asOf WHERE asOf.warehouse_ky = imct.from_warehouse_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_w -- Get new warehouse record referenced by the new facility (as of IM date) CROSS APPLY (SELECT TOP 1 wm_warehouse_his_ky,site_ky,warehouse_ky,warehouse_dodaac,name,description,routing_identifer_cd FROM his.wm_warehouse asOf WHERE asOf.warehouse_ky = imct.to_warehouse_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_w -- Get new site record referenced by the new warehouse (as of IM date) CROSS APPLY (SELECT TOP 1 wm_site_his_ky,region_ky,site_ky,name,description FROM his.wm_site asOf WHERE asOf.site_ky = to_w.site_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_s -- Get new region record referenced by the new site (as of IM date) CROSS APPLY (SELECT TOP 1 wm_region_his_ky,logistics_program_ky,region_ky,name,description FROM his.wm_region asOf WHERE asOf.region_ky = to_s.region_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_r -- Get new LP record referenced by the new region (as of IM date) CROSS APPLY (SELECT TOP 1 wm_logistics_program_his_ky,logistics_program_ky,name,description FROM his.wm_logistics_program asOf WHERE asOf.logistics_program_ky = to_r.logistics_program_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_lp -- Get most recent Stock Number record that was referenced by the old ICN (as of IM date) CROSS APPLY (SELECT TOP 1 cat_stock_nbr_his_ky,stock_nbr,lin_tamcn_ky,item_desc,reportable_commodity_type_ky,security_commodity_type_ky, stocking_unit_of_issue_cd,stores_account_cd,stock_item_cd,federal_supply_class_cd,unit_price,ui_conversion_factor FROM his.cat_stock_nbr asOf WHERE asOf.stock_nbr_ky = from_icn.stock_nbr_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC) from_stock -- Get most recent Stock Number record that was referenced by the new ICN (as of IM date) CROSS APPLY (SELECT TOP 1 cat_stock_nbr_his_ky,stock_nbr,lin_tamcn_ky,item_desc,reportable_commodity_type_ky,security_commodity_type_ky, stocking_unit_of_issue_cd,stores_account_cd,stock_item_cd,federal_supply_class_cd,unit_price,ui_conversion_factor FROM his.cat_stock_nbr asOf WHERE asOf.stock_nbr_ky = to_icn.stock_nbr_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_stock -- Get most recent LIN/TAMCN record that was referenced by the old stock number (as of IM date) CROSS APPLY (SELECT TOP 1 cat_lin_tamcn_his_ky,lin_tamcn_ky,lin_tamcn_id,lin_tamcn_desc FROM his.cat_lin_tamcn asOf WHERE asOf.lin_tamcn_ky = from_stock.lin_tamcn_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_lin -- Get most recent LIN/TAMCN record that was referenced by the new stock number (as of IM date) OUTER APPLY (SELECT TOP 1 cat_lin_tamcn_his_ky,lin_tamcn_ky,lin_tamcn_id,lin_tamcn_desc FROM his.cat_lin_tamcn asOf WHERE asOf.lin_tamcn_ky = to_stock.lin_tamcn_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_lin -- Get most recent Reportable Commodity record that was referenced by the old stock number (as of IM date) OUTER APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = from_stock.reportable_commodity_type_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_rct -- Get most recent Reportable Commodity record that was referenced by the new stock number (as of IM date) OUTER APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = to_stock.reportable_commodity_type_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_rct -- Get most recent Security Commodity record that was referenced by the old stock number (as of IM date) CROSS APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = from_stock.security_commodity_type_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_sct -- Get most recent Security Commodity record that was referenced by the new stock number (as of IM date) CROSS APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = to_stock.security_commodity_type_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_sct -- Get most recent UII record that was referenced by the old ICN (as of IM date) OUTER APPLY (SELECT TOP 1 wm_uii_his_ky,c_uii FROM his.wm_uii asOf WHERE asOf.uii_ky = from_icn.uii_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_uii -- Get most recent UII record that was referenced by the new ICN (as of IM date) OUTER APPLY (SELECT TOP 1 wm_uii_his_ky,c_uii FROM his.wm_uii asOf WHERE asOf.uii_ky = to_icn.uii_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_uii -- Get old project record referenced by the from project key (as of IM date) OUTER APPLY (SELECT TOP 1 wm_project_his_ky,project_cd,project_ky FROM his.wm_project asOf WHERE asOf.project_ky = imct.from_project_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_proj -- Get new project record referenced by the to project key (as of IM date) OUTER APPLY (SELECT TOP 1 wm_project_his_ky,project_cd,project_ky FROM his.wm_project asOf WHERE asOf.project_ky = imct.to_project_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_proj -- Get most recent document records (as of IM date) OUTER APPLY (SELECT TOP 1 wm_document_his_ky,document_nbr,to_dodaac,from_dodaac FROM his.wm_document asOf WHERE asOf.document_nbr_ky = imct.document_nbr_ky AND asof.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)doc -- Join static code tables LEFT OUTER JOIN cat.condition_lkp from_cc ON imct.from_condition_cd = from_cc.cond_cd LEFT OUTER JOIN cat.condition_lkp to_cc ON imct.to_condition_cd = to_cc.cond_cd LEFT OUTER JOIN wm.inventory_update_reason_lkp from_iur ON imct.from_reason_cd = from_iur.reason_cd LEFT OUTER JOIN wm.inventory_update_reason_lkp to_iur ON imct.to_reason_cd = to_iur.reason_cd LEFT OUTER JOIN cat.unit_of_issue_lkp from_sui ON from_sui.un_iss_cd = from_stock.stocking_unit_of_issue_cd LEFT OUTER JOIN cat.unit_of_issue_lkp to_sui ON to_sui.un_iss_cd = to_stock.stocking_unit_of_issue_cd LEFT OUTER JOIN cat.stores_account_lkp from_sal ON from_sal.stores_account_cd = from_stock.stores_account_cd LEFT OUTER JOIN cat.stores_account_lkp to_sal ON to_sal.stores_account_cd = to_stock.stores_account_cd LEFT OUTER JOIN cat.stock_item_lkp from_sil ON from_sil.stk_itm_cd = from_stock.stock_item_cd LEFT OUTER JOIN cat.stock_item_lkp to_sil ON to_sil.stk_itm_cd = to_stock.stock_item_cd LEFT OUTER JOIN cat.federal_supply_class_lkp from_fsc ON from_fsc.fed_sply_cls_cd = from_stock.federal_supply_class_cd LEFT OUTER JOIN cat.federal_supply_class_lkp to_fsc ON to_fsc.fed_sply_cls_cd = to_stock.federal_supply_class_cd WHERE imct.to_inventory_type_ky = 1 AND imct.from_inventory_type_ky = 1 UNION SELECT imct.inventory_master_ky , 'Inventory' AS change_source -- Old Stock/ICN fields , ISNULL(imct.from_inventory_control_nbr_ky, ISNULL(imct.to_inventory_control_nbr_ky, 0)) AS from_inventory_control_nbr_ky , from_icn.inventory_control_nbr AS from_inventory_control_nbr , from_icn.stock_nbr_ky AS from_stock_nbr_ky , from_stock.stock_nbr AS from_stock_nbr , from_stock.item_desc AS from_stock_desc , from_sui.un_iss_cd AS from_stocking_ui_cd , from_sui.dspl_desc AS from_stocking_ui_desc , from_stock.unit_price / from_stock.ui_conversion_factor AS from_stocking_ui_price , from_rct.commodity_type_ky AS from_reportable_commodity_type_ky , from_rct.commodity_type_id AS from_reportable_commodity_type_id , from_rct.commodity_description AS from_reportable_commodity_desc , from_sct.commodity_type_ky AS from_security_commodity_type_ky , from_sct.commodity_type_id AS from_security_commodity_type_id , from_sct.commodity_description AS from_security_commodity_desc , from_sal.stores_account_cd AS from_stores_account_cd , from_sal.display_description AS from_stores_account_desc , from_sil.stk_itm_cd AS from_stock_item_cd , from_sil.dspl_desc AS from_stock_item_desc , from_fsc.fed_sply_cls_cd AS from_federal_supply_class_cd , from_fsc.dspl_desc AS from_federal_supply_class_desc , from_lin.lin_tamcn_ky AS from_lin_tamcn_ky , from_lin.lin_tamcn_id AS from_lin_tamcn_id , from_lin.lin_tamcn_desc AS from_lin_tamcn_desc , from_icn.asset_id AS from_asset_id , from_icn.prfid AS from_prfid , from_icn.manufacturer_cage AS from_manufacturer_cage , from_icn.manufacturer_contract AS from_manufacturer_contract , from_icn.manufacturer_lot AS from_manufacturer_lot , from_icn.manufactured_date AS from_manufactured_date , from_icn.serial_nbr AS from_serial_nbr , from_icn.secondary_serial_nbr AS from_secondary_serial_nbr , from_icn.last_inspection_date AS from_last_inspection_date , from_icn.next_inspection_date AS from_next_inspection_date , from_icn.inspected_by AS from_inspected_by , from_icn.last_test_date AS from_last_test_date , from_icn.last_maintenance_date AS from_last_maintenance_date , from_icn.last_calibration_date AS from_last_calibration_date , from_icn.meter_reading AS from_meter_reading , from_icn.expiration_date AS from_expiration_date , from_icn.extended_flg AS from_extended_flg , from_icn.failed_flg AS from_failed_flg , from_icn.uii_ky AS from_uii_ky , from_uii.c_uii AS from_uii , from_icn.uii_status_cd AS from_uii_status_cd -- New Stock/ICN fields , NULL AS to_inventory_control_nbr_ky , NULL AS to_inventory_control_nbr , NULL AS to_stock_nbr_ky , NULL AS to_stock_nbr , NULL AS to_stock_desc , NULL AS to_stocking_ui_cd , NULL AS to_stocking_ui_desc , NULL AS to_stocking_ui_price , NULL AS to_reportable_commodity_type_ky , NULL AS to_reportable_commodity_type_id , NULL AS to_reportable_commodity_desc , NULL AS to_security_commodity_type_ky , NULL AS to_security_commodity_type_id , NULL AS to_security_commodity_desc , NULL AS to_stores_account_cd , NULL AS to_stores_account_desc , NULL AS to_stock_item_cd , NULL AS to_stock_item_desc , NULL AS to_federal_supply_class_cd , NULL AS to_federal_supply_class_desc , NULL AS to_lin_tamcn_ky , NULL AS to_lin_tamcn_id , NULL AS to_lin_tamcn_desc , NULL AS to_asset_id , NULL AS to_prfid , NULL AS to_manufacturer_cage , NULL AS to_manufacturer_contract , NULL AS to_manufacturer_lot , NULL AS to_manufactured_date , NULL AS to_serial_nbr , NULL AS to_secondary_serial_nbr , NULL AS to_last_inspection_date , NULL AS to_next_inspection_date , NULL AS to_inspected_by , NULL AS to_last_test_date , NULL AS to_last_maintenance_date , NULL AS to_last_calibration_date , NULL AS to_meter_reading , NULL AS to_expiration_date , NULL AS to_extended_flg , NULL AS to_failed_flg , NULL AS to_uii_ky , NULL AS to_uii , NULL AS to_uii_status_cd -- Old Inventory Fields , from_cc.cond_cd AS from_condition_cd , from_cc.dspl_desc AS from_condition_desc , imct.from_owning_dodaac AS from_owning_dodaac , from_proj.project_ky AS from_project_ky , from_proj.project_cd AS from_project_cd , imct.from_qty AS from_qty , from_iur.reason_cd AS from_reason_cd , from_iur.description AS from_reason_cd_desc , from_c.container_ky AS from_container_ky , from_c.container_id AS from_container_id , from_c.container_desc AS from_container_desc , from_l.location_ky AS from_location_ky , from_l.location_id AS from_location_id , from_l.description AS from_location_desc , from_f.facility_ky AS from_facility_ky , from_f.facility_nbr AS from_facility_nbr , from_f.description AS from_facility_desc , from_w.warehouse_ky AS from_warehouse_ky , from_w.warehouse_dodaac as from_warehouse_dodaac , from_w.name AS from_warehouse_name , from_w.description AS from_warehouse_desc , from_w.routing_identifer_cd as from_warehouse_ric -- New Inventory Fields , NULL AS to_condition_cd , NULL AS to_condition_desc , NULL AS to_owning_dodaac , NULL AS to_project_ky , NULL AS to_project_cd , NULL AS to_qty , NULL AS to_reason_cd , NULL AS to_reason_cd_desc , NULL AS to_container_ky , NULL AS to_container_id , NULL AS to_container_desc , NULL AS to_location_ky , NULL AS to_location_id , NULL AS to_location_desc , NULL AS to_facility_ky , NULL AS to_facility_nbr , NULL AS to_facility_desc , NULL AS to_warehouse_ky , NULL AS to_warehouse_dodaac , NULL AS to_warehouse_name , NULL AS to_warehouse_desc , NULL as to_warehouse_ric -- Additional Tier Info Fields , NULL AS to_site_ky , NULL AS to_site_name , NULL AS to_site_desc , NULL AS to_region_ky , NULL AS to_region_name , NULL AS to_region_desc , NULL AS to_logistics_program_ky , NULL AS to_logistics_program_name , NULL AS to_logistics_program_desc -- Other Info Fields , imct.document_nbr_ky , doc.document_nbr , doc.to_dodaac , doc.from_dodaac , imct.reason_desc , imct.pgm_id , imct.as_of_dt_tm , imct.change_by , imct.as_of_dt_tm AS history_record_generated_dt_tm FROM wm.inventory_master_change_tracking imct -- Get old ICN record that was referenced by the from ICN key (as of IM date) CROSS APPLY (SELECT TOP 1 inventory_control_nbr_ky,wm_inventory_control_nbr_his_ky,inventory_control_nbr,stock_nbr_ky,asset_id ,prfid,manufacturer_cage,manufacturer_contract,manufacturer_lot,manufactured_date,serial_nbr,secondary_serial_nbr ,last_inspection_date,next_inspection_date,inspected_by,last_test_date,last_maintenance_date,last_calibration_date ,meter_reading,expiration_date,extended_flg,failed_flg,uii_ky,uii_status_cd,history_record_generated_dt_tm FROM his.wm_inventory_control_nbr asOf WHERE (asOf.inventory_control_nbr_ky = imct.from_inventory_control_nbr_ky OR asOf.inventory_control_nbr_ky = imct.to_inventory_control_nbr_ky) AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_icn -- Get old container record referenced by the from container key (as of IM date) OUTER APPLY (SELECT TOP 1 wm_container_his_ky,location_ky,container_ky,container_id,container_desc FROM his.wm_container asOf WHERE asOf.container_ky = imct.from_container_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_c -- Get old location record referenced by the from location key (as of IM date) CROSS APPLY (SELECT TOP 1 wm_location_his_ky,zone_ky,location_ky,location_id,description FROM his.wm_location asOf WHERE (asOf.location_ky = imct.from_location_ky or asOf.location_ky = from_c.location_ky) AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_l -- Get old zone record referenced by the old location (as of IM date) CROSS APPLY (SELECT TOP 1 wm_zone_his_ky,facility_ky FROM his.wm_zone asOf WHERE asOf.zone_ky = from_l.zone_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_z -- Get old facility record referenced by the old zone (as of IM date) CROSS APPLY (SELECT TOP 1 wm_facility_his_ky,facility_ky,facility_nbr,description FROM his.wm_facility asOf WHERE asOf.facility_ky = from_z.facility_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_f -- Get old warehouse record referenced by the old facility (as of IM date) CROSS APPLY (SELECT TOP 1 wm_warehouse_his_ky,site_ky,warehouse_ky,warehouse_dodaac,name,description,routing_identifer_cd FROM his.wm_warehouse asOf WHERE asOf.warehouse_ky = imct.from_warehouse_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_w -- Get most recent Stock Number record that was referenced by the old ICN (as of IM date) CROSS APPLY (SELECT TOP 1 cat_stock_nbr_his_ky,stock_nbr,lin_tamcn_ky,item_desc,reportable_commodity_type_ky,security_commodity_type_ky, stocking_unit_of_issue_cd,stores_account_cd,stock_item_cd,federal_supply_class_cd,unit_price,ui_conversion_factor FROM his.cat_stock_nbr asOf WHERE asOf.stock_nbr_ky = from_icn.stock_nbr_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC) from_stock -- Get most recent LIN/TAMCN record that was referenced by the old stock number (as of IM date) OUTER APPLY (SELECT TOP 1 cat_lin_tamcn_his_ky,lin_tamcn_ky,lin_tamcn_id,lin_tamcn_desc FROM his.cat_lin_tamcn asOf WHERE asOf.lin_tamcn_ky = from_stock.lin_tamcn_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_lin -- Get most recent Reportable Commodity record that was referenced by the old stock number (as of IM date) CROSS APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = from_stock.reportable_commodity_type_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_rct -- Get most recent Security Commodity record that was referenced by the old stock number (as of IM date) CROSS APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = from_stock.security_commodity_type_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_sct -- Get most recent UII record that was referenced by the old ICN (as of IM date) OUTER APPLY (SELECT TOP 1 wm_uii_his_ky,c_uii FROM his.wm_uii asOf WHERE asOf.uii_ky = from_icn.uii_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_uii -- Get old project record referenced by the from project key (as of IM date) OUTER APPLY (SELECT TOP 1 wm_project_his_ky,project_cd,project_ky FROM his.wm_project asOf WHERE asOf.project_ky = imct.from_project_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_proj -- Get most recent document records (as of IM date) OUTER APPLY (SELECT TOP 1 wm_document_his_ky,document_nbr,to_dodaac,from_dodaac FROM his.wm_document asOf WHERE asOf.document_nbr_ky = imct.document_nbr_ky AND asof.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)doc -- Join static code tables LEFT OUTER JOIN cat.condition_lkp from_cc ON imct.from_condition_cd = from_cc.cond_cd LEFT OUTER JOIN cat.condition_lkp to_cc ON imct.to_condition_cd = to_cc.cond_cd LEFT OUTER JOIN wm.inventory_update_reason_lkp from_iur ON imct.from_reason_cd = from_iur.reason_cd LEFT OUTER JOIN wm.inventory_update_reason_lkp to_iur ON imct.to_reason_cd = to_iur.reason_cd LEFT OUTER JOIN cat.unit_of_issue_lkp from_sui ON from_sui.un_iss_cd = from_stock.stocking_unit_of_issue_cd LEFT OUTER JOIN cat.stores_account_lkp from_sal ON from_sal.stores_account_cd = from_stock.stores_account_cd LEFT OUTER JOIN cat.stock_item_lkp from_sil ON from_sil.stk_itm_cd = from_stock.stock_item_cd LEFT OUTER JOIN cat.federal_supply_class_lkp from_fsc ON from_fsc.fed_sply_cls_cd = from_stock.federal_supply_class_cd WHERE imct.from_inventory_type_ky = 1 and imct.to_inventory_type_ky IS NULL UNION SELECT imct.inventory_master_ky , 'Inventory' AS change_source -- Old Stock/ICN fields , NULL AS from_inventory_control_nbr_ky , NULL AS from_inventory_control_nbr , NULL AS from_stock_nbr_ky , NULL AS from_stock_nbr , NULL AS from_stock_desc , NULL AS from_stocking_ui_cd , NULL AS from_stocking_ui_desc , NULL AS from_stocking_ui_price , NULL AS from_reportable_commodity_type_ky , NULL AS from_reportable_commodity_type_id , NULL AS from_reportable_commodity_desc , NULL AS from_security_commodity_type_ky , NULL AS from_security_commodity_type_id , NULL AS from_security_commodity_desc , NULL AS from_stores_account_cd , NULL AS from_stores_account_desc , NULL AS from_stock_item_cd , NULL AS from_stock_item_desc , NULL AS from_federal_supply_class_cd , NULL AS from_federal_supply_class_desc , NULL AS from_lin_tamcn_ky , NULL AS from_lin_tamcn_id , NULL AS from_lin_tamcn_desc , NULL AS from_asset_id , NULL AS from_prfid , NULL AS from_manufacturer_cage , NULL AS from_manufacturer_contract , NULL AS from_manufacturer_lot , NULL AS from_manufactured_date , NULL AS from_serial_nbr , NULL AS from_secondary_serial_nbr , NULL AS from_last_inspection_date , NULL AS from_next_inspection_date , NULL AS from_inspected_by , NULL AS from_last_test_date , NULL AS from_last_maintenance_date , NULL AS from_last_calibration_date , NULL AS from_meter_reading , NULL AS from_expiration_date , NULL AS from_extended_flg , NULL AS from_failed_flg , NULL AS from_uii_ky , NULL AS from_uii , NULL AS from_uii_status_cd -- New Stock/ICN fields , imct.to_inventory_control_nbr_ky AS to_inventory_control_nbr_ky , to_icn.inventory_control_nbr AS to_inventory_control_nbr , to_icn.stock_nbr_ky AS to_stock_nbr_ky , to_stock.stock_nbr AS to_stock_nbr , to_stock.item_desc AS to_stock_desc , to_sui.un_iss_cd AS to_stocking_ui_cd , to_sui.dspl_desc AS to_stocking_ui_desc , to_stock.unit_price / to_stock.ui_conversion_factor AS to_stocking_ui_price , to_rct.commodity_type_ky AS to_reportable_commodity_type_ky , to_rct.commodity_type_id AS to_reportable_commodity_type_id , to_rct.commodity_description AS to_reportable_commodity_desc , to_sct.commodity_type_ky AS to_security_commodity_type_ky , to_sct.commodity_type_id AS to_security_commodity_type_id , to_sct.commodity_description AS to_security_commodity_desc , to_sal.stores_account_cd AS to_stores_account_cd , to_sal.display_description AS to_stores_account_desc , to_sil.stk_itm_cd AS to_stock_item_cd , to_sil.dspl_desc AS to_stock_item_desc , to_fsc.fed_sply_cls_cd AS to_federal_supply_class_cd , to_fsc.dspl_desc AS to_federal_supply_class_desc , to_lin.lin_tamcn_ky AS to_lin_tamcn_ky , to_lin.lin_tamcn_id AS to_lin_tamcn_id , to_lin.lin_tamcn_desc AS to_lin_tamcn_desc , to_icn.asset_id AS to_asset_id , to_icn.prfid AS to_prfid , to_icn.manufacturer_cage AS to_manufacturer_cage , to_icn.manufacturer_contract AS to_manufacturer_contract , to_icn.manufacturer_lot AS to_manufacturer_lot , to_icn.manufactured_date AS to_manufactured_date , to_icn.serial_nbr AS to_serial_nbr , to_icn.secondary_serial_nbr AS to_secondary_serial_nbr , to_icn.last_inspection_date AS to_last_inspection_date , to_icn.next_inspection_date AS to_next_inspection_date , to_icn.inspected_by AS to_inspected_by , to_icn.last_test_date AS to_last_test_date , to_icn.last_maintenance_date AS to_last_maintenance_date , to_icn.last_calibration_date AS to_last_calibration_date , to_icn.meter_reading AS to_meter_reading , to_icn.expiration_date AS to_expiration_date , to_icn.extended_flg AS to_extended_flg , to_icn.failed_flg AS to_failed_flg , to_icn.uii_ky AS to_uii_ky , to_uii.c_uii AS to_uii , to_icn.uii_status_cd AS to_uii_status_cd -- Old Inventory Fields , NULL AS from_condition_cd , NULL AS from_condition_desc , NULL AS from_owning_dodaac , NULL AS from_project_ky , NULL AS from_project_cd , NULL AS from_qty , NULL AS from_reason_cd , NULL from_reason_cd_desc , NULL AS from_container_ky , NULL AS from_container_id , NULL AS from_container_desc , NULL AS from_location_ky , NULL AS from_location_id , NULL AS from_location_desc , NULL AS from_facility_ky , NULL AS from_facility_nbr , NULL AS from_facility_desc , NULL AS from_warehouse_ky , NULL as from_warehouse_dodaac , NULL AS from_warehouse_name , NULL AS from_warehouse_desc , NULL as from_warehouse_ric -- New Inventory Fields , to_cc.cond_cd AS to_condition_cd , to_cc.dspl_desc AS to_condition_desc , imct.to_owning_dodaac AS to_owning_dodaac , to_proj.project_ky AS to_project_ky , to_proj.project_cd AS to_project_cd , imct.to_qty AS to_qty , to_iur.reason_cd AS to_reason_cd , to_iur.description AS to_reason_cd_desc , to_c.container_ky AS to_container_ky , to_c.container_id AS to_container_id , to_c.container_desc AS to_container_desc , to_l.location_ky AS to_location_ky , to_l.location_id AS to_location_id , to_l.description AS to_location_desc , to_f.facility_ky AS to_facility_ky , to_f.facility_nbr AS to_facility_nbr , to_f.description AS to_facility_desc , to_w.warehouse_ky AS to_warehouse_ky , to_w.warehouse_dodaac AS to_warehouse_dodaac , to_w.name AS to_warehouse_name , to_w.description AS to_warehouse_desc , to_w.routing_identifer_cd as to_warehouse_ric -- Additional Tier Info Fields , to_s.site_ky AS to_site_ky , to_s.name AS to_site_name , to_s.description AS to_site_desc , to_r.region_ky AS to_region_ky , to_r.name AS to_region_name , to_r.description AS to_region_desc , to_lp.logistics_program_ky AS to_logistics_program_ky , to_lp.name AS to_logistics_program_name , to_lp.description AS to_logistics_program_desc -- Other Info Fields , imct.document_nbr_ky , doc.document_nbr , doc.to_dodaac , doc.from_dodaac , imct.reason_desc , imct.pgm_id , imct.as_of_dt_tm , imct.change_by , imct.as_of_dt_tm AS history_record_generated_dt_tm FROM wm.inventory_master_change_tracking imct -- Get new ICN record that was referenced by the to ICN key (as of IM date) CROSS APPLY (SELECT TOP 1 inventory_control_nbr_ky,wm_inventory_control_nbr_his_ky,inventory_control_nbr,stock_nbr_ky,asset_id ,prfid,manufacturer_cage,manufacturer_contract,manufacturer_lot,manufactured_date,serial_nbr,secondary_serial_nbr ,last_inspection_date,next_inspection_date,inspected_by,last_test_date,last_maintenance_date,last_calibration_date ,meter_reading,expiration_date,extended_flg,failed_flg,uii_ky,uii_status_cd,history_record_generated_dt_tm FROM his.wm_inventory_control_nbr asOf WHERE asOf.inventory_control_nbr_ky = imct.to_inventory_control_nbr_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_icn -- Get new container record referenced by the to container key (as of IM date) OUTER APPLY (SELECT TOP 1 wm_container_his_ky,location_ky,container_ky,container_id,container_desc FROM his.wm_container asOf WHERE asOf.container_ky = imct.to_container_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_c -- Get new location record referenced by the to location key (as of IM date) CROSS APPLY (SELECT TOP 1 wm_location_his_ky,zone_ky,location_ky,location_id,description FROM his.wm_location asOf WHERE (asOf.location_ky = imct.to_location_ky or asOf.location_ky = to_c.location_ky) AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_l -- Get new zone record referenced by the new location (as of IM date) CROSS APPLY (SELECT TOP 1 wm_zone_his_ky,facility_ky FROM his.wm_zone asOf WHERE asOf.zone_ky = to_l.zone_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_z -- Get new facility record referenced by new old zone (as of IM date) CROSS APPLY (SELECT TOP 1 wm_facility_his_ky,facility_ky,facility_nbr,description FROM his.wm_facility asOf WHERE asOf.facility_ky = to_z.facility_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_f -- Get new warehouse record referenced by the new facility (as of IM date) CROSS APPLY (SELECT TOP 1 wm_warehouse_his_ky,site_ky,warehouse_ky,warehouse_dodaac,name,description,routing_identifer_cd FROM his.wm_warehouse asOf WHERE asOf.warehouse_ky = imct.to_warehouse_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_w -- Get new site record referenced by the new warehouse (as of IM date) CROSS APPLY (SELECT TOP 1 wm_site_his_ky,region_ky,site_ky,name,description FROM his.wm_site asOf WHERE asOf.site_ky = to_w.site_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_s -- Get new region record referenced by the new site (as of IM date) CROSS APPLY (SELECT TOP 1 wm_region_his_ky,logistics_program_ky,region_ky,name,description FROM his.wm_region asOf WHERE asOf.region_ky = to_s.region_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_r -- Get new LP record referenced by the new region (as of IM date) CROSS APPLY (SELECT TOP 1 wm_logistics_program_his_ky,logistics_program_ky,name,description FROM his.wm_logistics_program asOf WHERE asOf.logistics_program_ky = to_r.logistics_program_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_lp -- Get most recent Stock Number record that was referenced by the new ICN (as of IM date) CROSS APPLY (SELECT TOP 1 cat_stock_nbr_his_ky,stock_nbr,lin_tamcn_ky,item_desc,reportable_commodity_type_ky,security_commodity_type_ky, stocking_unit_of_issue_cd,stores_account_cd,stock_item_cd,federal_supply_class_cd,unit_price,ui_conversion_factor FROM his.cat_stock_nbr asOf WHERE asOf.stock_nbr_ky = to_icn.stock_nbr_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_stock -- Get most recent LIN/TAMCN record that was referenced by the new stock number (as of IM date) OUTER APPLY (SELECT TOP 1 cat_lin_tamcn_his_ky,lin_tamcn_ky,lin_tamcn_id,lin_tamcn_desc FROM his.cat_lin_tamcn asOf WHERE asOf.lin_tamcn_ky = to_stock.lin_tamcn_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_lin -- Get most recent Reportable Commodity record that was referenced by the new stock number (as of IM date) CROSS APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = to_stock.reportable_commodity_type_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_rct -- Get most recent Security Commodity record that was referenced by the new stock number (as of IM date) CROSS APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = to_stock.security_commodity_type_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_sct -- Get most recent UII record that was referenced by the new ICN (as of IM date) OUTER APPLY (SELECT TOP 1 wm_uii_his_ky,c_uii FROM his.wm_uii asOf WHERE asOf.uii_ky = to_icn.uii_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_uii -- Get new project record referenced by the to project key (as of IM date) OUTER APPLY (SELECT TOP 1 wm_project_his_ky,project_cd,project_ky FROM his.wm_project asOf WHERE asOf.project_ky = imct.to_project_ky AND asOf.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_proj -- Get most recent document records (as of IM date) OUTER APPLY (SELECT TOP 1 wm_document_his_ky,document_nbr,to_dodaac,from_dodaac FROM his.wm_document asOf WHERE asOf.document_nbr_ky = imct.document_nbr_ky AND asof.history_record_generated_dt_tm <= imct.as_of_dt_tm ORDER BY history_record_generated_dt_tm DESC)doc -- Join static code tables LEFT OUTER JOIN cat.condition_lkp from_cc ON imct.from_condition_cd = from_cc.cond_cd LEFT OUTER JOIN cat.condition_lkp to_cc ON imct.to_condition_cd = to_cc.cond_cd LEFT OUTER JOIN wm.inventory_update_reason_lkp from_iur ON imct.from_reason_cd = from_iur.reason_cd LEFT OUTER JOIN wm.inventory_update_reason_lkp to_iur ON imct.to_reason_cd = to_iur.reason_cd LEFT OUTER JOIN cat.unit_of_issue_lkp to_sui ON to_sui.un_iss_cd = to_stock.stocking_unit_of_issue_cd LEFT OUTER JOIN cat.stores_account_lkp to_sal ON to_sal.stores_account_cd = to_stock.stores_account_cd LEFT OUTER JOIN cat.stock_item_lkp to_sil ON to_sil.stk_itm_cd = to_stock.stock_item_cd LEFT OUTER JOIN cat.federal_supply_class_lkp to_fsc ON to_fsc.fed_sply_cls_cd = to_stock.federal_supply_class_cd WHERE imct.to_inventory_type_ky = 1 AND imct.from_inventory_type_ky IS NULL GO ------------------------- USE [wm_db] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [his].[vw_his_inventory_control_number_changes] AS -- select * from his.vw_his_inventory_control_number_changes SELECT ISNULL(CAST(0 as bigint), 0) AS inventory_master_ky , 'ICN Attributes' AS change_source -- Old Stock/ICN fields , ISNULL(icnOld.inventory_control_nbr_ky, 0) AS from_inventory_control_nbr_ky , icnOld.inventory_control_nbr AS from_inventory_control_nbr , icnOld.stock_nbr_ky AS from_stock_nbr_ky , from_stock.stock_nbr AS from_stock_nbr , from_stock.item_desc AS from_stock_desc , from_sui.un_iss_cd AS from_stocking_ui_cd , from_sui.dspl_desc AS from_stocking_ui_desc , from_stock.unit_price / from_stock.ui_conversion_factor AS from_stocking_ui_price , from_rct.commodity_type_ky AS from_reportable_commodity_type_ky , from_rct.commodity_type_id AS from_reportable_commodity_type_id , from_rct.commodity_description AS from_reportable_commodity_desc , from_sct.commodity_type_ky AS from_security_commodity_type_ky , from_sct.commodity_type_id AS from_security_commodity_type_id , from_sct.commodity_description AS from_security_commodity_desc , from_sal.stores_account_cd AS from_stores_account_cd , from_sal.display_description AS from_stores_account_desc , from_sil.stk_itm_cd AS from_stock_item_cd , from_sil.dspl_desc AS from_stock_item_desc , from_fsc.fed_sply_cls_cd AS from_federal_supply_class_cd , from_fsc.dspl_desc AS from_federal_supply_class_desc , from_lin.lin_tamcn_ky AS from_lin_tamcn_ky , from_lin.lin_tamcn_id AS from_lin_tamcn_id , from_lin.lin_tamcn_desc AS from_lin_tamcn_desc , icnOld.asset_id AS from_asset_id , icnOld.prfid AS from_prfid , icnOld.manufacturer_cage AS from_manufacturer_cage , icnOld.manufacturer_contract AS from_manufacturer_contract , icnOld.manufacturer_lot AS from_manufacturer_lot , icnOld.manufactured_date AS from_manufactured_date , icnOld.serial_nbr AS from_serial_nbr , icnOld.secondary_serial_nbr AS from_secondary_serial_nbr , icnOld.last_inspection_date AS from_last_inspection_date , icnOld.next_inspection_date AS from_next_inspection_date , icnOld.inspected_by AS from_inspected_by , icnOld.last_test_date AS from_last_test_date , icnOld.last_maintenance_date AS from_last_maintenance_date , icnOld.last_calibration_date AS from_last_calibration_date , icnOld.meter_reading AS from_meter_reading , icnOld.expiration_date AS from_expiration_date , icnOld.extended_flg AS from_extended_flg , icnOld.failed_flg AS from_failed_flg , icnOld.uii_ky AS from_uii_ky , from_uii.c_uii AS from_uii , icnOld.uii_status_cd AS from_uii_status_cd -- New Stock/ICN fields , ISNULL(icnNew.inventory_control_nbr_ky, 0) AS to_inventory_control_nbr_ky , icnNew.inventory_control_nbr AS to_inventory_control_nbr , icnNew.stock_nbr_ky AS to_stock_nbr_ky , to_stock.stock_nbr AS to_stock_nbr , to_stock.item_desc AS to_stock_desc , to_sui.un_iss_cd AS to_stocking_ui_cd , to_sui.dspl_desc AS to_stocking_ui_desc , to_stock.unit_price / to_stock.ui_conversion_factor AS to_stocking_ui_price , to_rct.commodity_type_ky AS to_reportable_commodity_type_ky , to_rct.commodity_type_id AS to_reportable_commodity_type_id , to_rct.commodity_description AS to_reportable_commodity_desc , to_sct.commodity_type_ky AS to_security_commodity_type_ky , to_sct.commodity_type_id AS to_security_commodity_type_id , to_sct.commodity_description AS to_security_commodity_desc , to_sal.stores_account_cd AS to_stores_account_cd , to_sal.display_description AS to_stores_account_desc , to_sil.stk_itm_cd AS to_stock_item_cd , to_sil.dspl_desc AS to_stock_item_desc , to_fsc.fed_sply_cls_cd AS to_federal_supply_class_cd , to_fsc.dspl_desc AS to_federal_supply_class_desc , to_lin.lin_tamcn_ky AS to_lin_tamcn_ky , to_lin.lin_tamcn_id AS to_lin_tamcn_id , to_lin.lin_tamcn_desc AS to_lin_tamcn_desc , icnNew.asset_id AS to_asset_id , icnNew.prfid AS to_prfid , icnNew.manufacturer_cage AS to_manufacturer_cage , icnNew.manufacturer_contract AS to_manufacturer_contract , icnNew.manufacturer_lot AS to_manufacturer_lot , icnNew.manufactured_date AS to_manufactured_date , icnNew.serial_nbr AS to_serial_nbr , icnNew.secondary_serial_nbr AS to_secondary_serial_nbr , icnNew.last_inspection_date AS to_last_inspection_date , icnNew.next_inspection_date AS to_next_inspection_date , icnNew.inspected_by AS to_inspected_by , icnNew.last_test_date AS to_last_test_date , icnNew.last_maintenance_date AS to_last_maintenance_date , icnNew.last_calibration_date AS to_last_calibration_date , icnNew.meter_reading AS to_meter_reading , icnNew.expiration_date AS to_expiration_date , icnNew.extended_flg AS to_extended_flg , icnNew.failed_flg AS to_failed_flg , icnNew.uii_ky AS to_uii_ky , to_uii.c_uii AS to_uii , icnNew.uii_status_cd AS to_uii_status_cd -- Old Inventory Fields , im.condition_cd AS from_condition_cd , cc.dspl_desc AS from_condition_desc , im.owning_dodaac AS from_owning_dodaac , im.project_ky AS from_project_ky , proj.project_cd AS from_project_cd , im.qty AS from_qty , CAST(NULL AS VARCHAR) AS from_reason_cd , iur.description AS from_reason_cd_desc , c.container_ky AS from_container_ky , c.container_id AS from_container_id , c.container_desc AS from_container_desc , l.location_ky AS from_location_ky , l.location_id AS from_location_id , l.description AS from_location_desc , f.facility_ky AS from_facility_ky , f.facility_nbr AS from_facility_nbr , f.description AS from_facility_desc , w.warehouse_ky AS from_warehouse_ky , w.warehouse_dodaac as from_warehouse_dodaac , w.name AS from_warehouse_name , w.description AS from_warehouse_desc , w.routing_identifer_cd as from_warehouse_ric -- New Inventory Fields , im.condition_cd AS to_condition_cd , cc.dspl_desc AS to_condition_desc , im.owning_dodaac AS to_owning_dodaac , im.project_ky AS to_project_ky , proj.project_cd AS to_project_cd , im.qty AS to_qty , CAST(NULL AS VARCHAR) AS to_reason_cd , iur.description AS to_reason_cd_desc , c.container_ky AS to_container_ky , c.container_id AS to_container_id , c.container_desc AS to_container_desc , l.location_ky AS to_location_ky , l.location_id AS to_location_id , l.description AS to_location_desc , f.facility_ky AS to_facility_ky , f.facility_nbr AS to_facility_nbr , f.description AS to_facility_desc , w.warehouse_ky AS to_warehouse_ky , w.warehouse_dodaac AS to_warehouse_dodaac , w.name AS to_warehouse_name , w.description AS to_warehouse_desc , w.routing_identifer_cd AS to_warehouse_ric -- Additional Tier Info Fields , s.site_ky AS to_site_ky , s.name AS to_site_name , s.description AS to_site_desc , r.region_ky AS to_region_ky , r.name AS to_region_name , r.description AS to_region_desc , lp.logistics_program_ky AS to_logistics_program_ky , lp.name AS to_logistics_program_name , lp.description AS to_logistics_program_desc -- Other Info Fields , im.document_nbr_ky AS document_nbr_ky , doc.document_nbr AS document_nbr , doc.to_dodaac , doc.from_dodaac , CAST(NULL AS VARCHAR) AS reason_desc , icnNew.pgm_id , icnNew.lst_tran_dt_tm AS as_of_dt_tm , icnNew.lst_updtd_by AS change_by , icnNew.history_record_generated_dt_tm FROM his.wm_inventory_control_nbr icnNew -- Get the most recent ICN history record that was there before the new record was logged CROSS APPLY (SELECT inventory_control_nbr_ky,wm_inventory_control_nbr_his_ky,inventory_control_nbr,stock_nbr_ky,asset_id ,prfid,manufacturer_cage,manufacturer_contract,manufacturer_lot,manufactured_date,serial_nbr,secondary_serial_nbr ,last_inspection_date,next_inspection_date,inspected_by,last_test_date,last_maintenance_date,last_calibration_date ,meter_reading,expiration_date,extended_flg,failed_flg,uii_ky,uii_status_cd,history_record_generated_dt_tm, ROW_NUMBER()OVER(PARTITION BY inventory_control_nbr_ky ORDER BY history_record_generated_dt_tm DESC) rownum FROM his.wm_inventory_control_nbr priorRecord WHERE priorRecord.inventory_control_nbr_ky = icnNew.inventory_control_nbr_ky AND priorRecord.wm_inventory_control_nbr_his_ky < icnNew.wm_inventory_control_nbr_his_ky AND priorRecord.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm )icnOld -- Get all non-zero, fully accepted inventory records that ever existed for each of the inventory control number records -- Filter the inventory records down only to the most recent record for each (as of the new ICN record date) CROSS APPLY (SELECT wm_inventory_master_his_ky,location_ky,container_ky,warehouse_ky,condition_cd,inventory_type_ky,parent_icn_ky,reason_cd ,owning_dodaac,project_ky,qty,document_nbr_ky,history_record_generated_dt_tm, ROW_NUMBER()OVER(PARTITION BY inventory_master_ky ORDER BY history_record_generated_dt_tm DESC) rownum FROM his.wm_inventory_master imAll WHERE imAll.inventory_control_nbr_ky = icnNew.inventory_control_nbr_ky AND imAll.qty > 0 -- non-zero AND (imAll.project_ky <> -1 OR imAll.project_ky IS NULL) AND imAll.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ) im -- Get most recent container records (as of the new stock number record date) OUTER APPLY (SELECT TOP 1 wm_container_his_ky,location_ky,container_ky,container_id,container_desc FROM his.wm_container asOf WHERE asOf.container_ky = im.container_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) c -- Get most recent location records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_location_his_ky,zone_ky,location_ky,location_id,description FROM his.wm_location asOf WHERE (asOf.location_ky =im.location_ky OR asOf.location_ky = c.location_ky) AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) l -- Get most recent zone records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_zone_his_ky,facility_ky FROM his.wm_zone asOf WHERE asOf.zone_ky = l.zone_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) z -- Get most recent facility records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_facility_his_ky,facility_ky,facility_nbr,description FROM his.wm_facility asOf WHERE asOf.facility_ky = z.facility_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) f -- Get most recent warehouse records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_warehouse_his_ky,site_ky,warehouse_ky,warehouse_dodaac,name,description,routing_identifer_cd FROM his.wm_warehouse asOf WHERE asOf.warehouse_ky = im.warehouse_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) w -- Get most recent site records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_site_his_ky,region_ky,site_ky,name,description FROM his.wm_site asOf WHERE asOf.site_ky = w.site_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) s -- Get most recent region records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_region_his_ky,logistics_program_ky,region_ky,name,description FROM his.wm_region asOf WHERE asOf.region_ky = s.region_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) r -- Get most recent LP records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_logistics_program_his_ky,logistics_program_ky,name,description FROM his.wm_logistics_program asOf WHERE asOf.logistics_program_ky = r.logistics_program_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) lp -- Get most recent Stock Number record that was referenced by the old ICN (as of new ICN date, so that we don't see stock specific changes on ICN Attributes) OUTER APPLY (SELECT TOP 1 cat_stock_nbr_his_ky,stock_nbr,lin_tamcn_ky,item_desc,reportable_commodity_type_ky,security_commodity_type_ky, stocking_unit_of_issue_cd,stores_account_cd,stock_item_cd,federal_supply_class_cd,unit_price,ui_conversion_factor FROM his.cat_stock_nbr asOf WHERE asOf.stock_nbr_ky = icnOld.stock_nbr_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_stock -- Get most recent Stock Number record that was referenced by the new ICN (as of new ICN date) OUTER APPLY (SELECT TOP 1 cat_stock_nbr_his_ky,stock_nbr,lin_tamcn_ky,item_desc,reportable_commodity_type_ky,security_commodity_type_ky, stocking_unit_of_issue_cd,stores_account_cd,stock_item_cd,federal_supply_class_cd,unit_price,ui_conversion_factor FROM his.cat_stock_nbr asOf WHERE asOf.stock_nbr_ky = icnNew.stock_nbr_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_stock -- Get most recent LIN/TAMCN record that was referenced by the old stock number (as of new ICN date, so that we don't see stock specific changes on ICN Attributes) OUTER APPLY (SELECT TOP 1 cat_lin_tamcn_his_ky,lin_tamcn_ky,lin_tamcn_id,lin_tamcn_desc FROM his.cat_lin_tamcn asOf WHERE asOf.lin_tamcn_ky = from_stock.lin_tamcn_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_lin -- Get most recent LIN/TAMCN record that was referenced by the new stock number (as of new ICN date) OUTER APPLY (SELECT TOP 1 cat_lin_tamcn_his_ky,lin_tamcn_ky,lin_tamcn_id,lin_tamcn_desc FROM his.cat_lin_tamcn asOf WHERE asOf.lin_tamcn_ky = to_stock.lin_tamcn_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_lin -- Get most recent Reportable Commodity record that was referenced by the old stock number (as of new ICN date, so that we don't see stock specific changes on ICN Attributes) OUTER APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = from_stock.reportable_commodity_type_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_rct -- Get most recent Reportable Commodity record that was referenced by the new stock number (as of new ICN date) OUTER APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = to_stock.reportable_commodity_type_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_rct -- Get most recent Security Commodity record that was referenced by the old stock number (as of new ICN date, so that we don't see stock specific changes on ICN Attributes) OUTER APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = from_stock.security_commodity_type_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_sct -- Get most recent Security Commodity record that was referenced by the new stock number (as of new ICN date) OUTER APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = to_stock.security_commodity_type_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_sct -- Get most recent UII record that was referenced by the old ICN (as of old ICN date) OUTER APPLY (SELECT TOP 1 wm_uii_his_ky,c_uii FROM his.wm_uii asOf WHERE asOf.uii_ky = icnOld.uii_ky AND asOf.history_record_generated_dt_tm <= icnOld.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_uii -- Get most recent UII record that was referenced by the new ICN (as of new ICN date) OUTER APPLY (SELECT TOP 1 wm_uii_his_ky,c_uii FROM his.wm_uii asOf WHERE asOf.uii_ky = icnNew.uii_ky AND asOf.history_record_generated_dt_tm <= icnNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_uii -- Get most recent project records (as of the inventory record date) OUTER APPLY (SELECT TOP 1 wm_project_his_ky,project_cd FROM his.wm_project asOf WHERE asOf.project_ky = im.project_ky AND asOf.history_record_generated_dt_tm <= im.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)proj -- Get most recent document records (as of the inventory record date) OUTER APPLY (SELECT TOP 1 wm_document_his_ky,document_nbr,to_dodaac,from_dodaac FROM his.wm_document asOf WHERE asOf.document_nbr_ky = im.document_nbr_ky AND asof.history_record_generated_dt_tm <= im.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)doc -- Join static code tables LEFT OUTER JOIN cat.condition_lkp cc ON im.condition_cd = cc.cond_cd LEFT OUTER JOIN wm.inventory_update_reason_lkp iur ON im.reason_cd = iur.reason_cd LEFT OUTER JOIN cat.unit_of_issue_lkp from_sui ON from_sui.un_iss_cd = from_stock.stocking_unit_of_issue_cd LEFT OUTER JOIN cat.unit_of_issue_lkp to_sui ON to_sui.un_iss_cd = to_stock.stocking_unit_of_issue_cd LEFT OUTER JOIN cat.stores_account_lkp from_sal ON from_sal.stores_account_cd = from_stock.stores_account_cd LEFT OUTER JOIN cat.stores_account_lkp to_sal ON to_sal.stores_account_cd = to_stock.stores_account_cd LEFT OUTER JOIN cat.stock_item_lkp from_sil ON from_sil.stk_itm_cd = from_stock.stock_item_cd LEFT OUTER JOIN cat.stock_item_lkp to_sil ON to_sil.stk_itm_cd = to_stock.stock_item_cd LEFT OUTER JOIN cat.federal_supply_class_lkp from_fsc ON from_fsc.fed_sply_cls_cd = from_stock.federal_supply_class_cd LEFT OUTER JOIN cat.federal_supply_class_lkp to_fsc ON to_fsc.fed_sply_cls_cd = to_stock.federal_supply_class_cd WHERE icnOld.rownum = 1 AND im.rownum = 1 AND icnNew.operation$ <> 2 GO ------------------------------ USE [wm_db] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [his].[vw_his_stock_number_changes] AS -- select * from his.vw_his_stock_number_changes SELECT ISNULL(CAST(0 as bigint), 0) AS inventory_master_ky , 'Catalog' AS change_source -- Old Stock/ICN fields , ISNULL(icn.inventory_control_nbr_ky, 0) AS from_inventory_control_nbr_ky , icn.inventory_control_nbr AS from_inventory_control_nbr , icn.stock_nbr_ky AS from_stock_nbr_ky , stockOld.stock_nbr AS from_stock_nbr , stockOld.item_desc AS from_stock_desc , from_sui.un_iss_cd AS from_stocking_ui_cd , from_sui.dspl_desc AS from_stocking_ui_desc , stockOld.unit_price / stockOld.ui_conversion_factor AS from_stocking_ui_price , from_rct.commodity_type_ky AS from_reportable_commodity_type_ky , from_rct.commodity_type_id AS from_reportable_commodity_type_id , from_rct.commodity_description AS from_reportable_commodity_desc , from_sct.commodity_type_ky AS from_security_commodity_type_ky , from_sct.commodity_type_id AS from_security_commodity_type_id , from_sct.commodity_description AS from_security_commodity_desc , from_sal.stores_account_cd AS from_stores_account_cd , from_sal.display_description AS from_stores_account_desc , from_sil.stk_itm_cd AS from_stock_item_cd , from_sil.dspl_desc AS from_stock_item_desc , from_fsc.fed_sply_cls_cd AS from_federal_supply_class_cd , from_fsc.dspl_desc AS from_federal_supply_class_desc , from_lin.lin_tamcn_ky AS from_lin_tamcn_ky , from_lin.lin_tamcn_id AS from_lin_tamcn_id , from_lin.lin_tamcn_desc AS from_lin_tamcn_desc , icn.asset_id AS from_asset_id , icn.prfid AS from_prfid , icn.manufacturer_cage AS from_manufacturer_cage , icn.manufacturer_contract AS from_manufacturer_contract , icn.manufacturer_lot AS from_manufacturer_lot , icn.manufactured_date AS from_manufactured_date , icn.serial_nbr AS from_serial_nbr , icn.secondary_serial_nbr AS from_secondary_serial_nbr , icn.last_inspection_date AS from_last_inspection_date , icn.next_inspection_date AS from_next_inspection_date , icn.inspected_by AS from_inspected_by , icn.last_test_date AS from_last_test_date , icn.last_maintenance_date AS from_last_maintenance_date , icn.last_calibration_date AS from_last_calibration_date , icn.meter_reading AS from_meter_reading , icn.expiration_date AS from_expiration_date , icn.extended_flg AS from_extended_flg , icn.failed_flg AS from_failed_flg , icn.uii_ky AS from_uii_ky , uii.c_uii AS from_uii , icn.uii_status_cd AS from_uii_status_cd -- New Stock/ICN fields , ISNULL(icn.inventory_control_nbr_ky, 0) AS to_inventory_control_nbr_ky , icn.inventory_control_nbr AS to_inventory_control_nbr , icn.stock_nbr_ky AS to_stock_nbr_ky , stockNew.stock_nbr AS to_stock_nbr , stockNew.item_desc AS to_stock_desc , to_sui.un_iss_cd AS to_stocking_ui_cd , to_sui.dspl_desc AS to_stocking_ui_desc , stockNew.unit_price / stockNew.ui_conversion_factor AS to_stocking_ui_price , to_rct.commodity_type_ky AS to_reportable_commodity_type_ky , to_rct.commodity_type_id AS to_reportable_commodity_type_id , to_rct.commodity_description AS to_reportable_commodity_desc , to_sct.commodity_type_ky AS to_security_commodity_type_ky , to_sct.commodity_type_id AS to_security_commodity_type_id , to_sct.commodity_description AS to_security_commodity_desc , to_sal.stores_account_cd AS to_stores_account_cd , to_sal.display_description AS to_stores_account_desc , to_sil.stk_itm_cd AS to_stock_item_cd , to_sil.dspl_desc AS to_stock_item_desc , to_fsc.fed_sply_cls_cd AS to_federal_supply_class_cd , to_fsc.dspl_desc AS to_federal_supply_class_desc , to_lin.lin_tamcn_ky AS to_lin_tamcn_ky , to_lin.lin_tamcn_id AS to_lin_tamcn_id , to_lin.lin_tamcn_desc AS to_lin_tamcn_desc , icn.asset_id AS to_asset_id , icn.prfid AS to_prfid , icn.manufacturer_cage AS to_manufacturer_cage , icn.manufacturer_contract AS to_manufacturer_contract , icn.manufacturer_lot AS to_manufacturer_lot , icn.manufactured_date AS to_manufactured_date , icn.serial_nbr AS to_serial_nbr , icn.secondary_serial_nbr AS to_secondary_serial_nbr , icn.last_inspection_date AS to_last_inspection_date , icn.next_inspection_date AS to_next_inspection_date , icn.inspected_by AS to_inspected_by , icn.last_test_date AS to_last_test_date , icn.last_maintenance_date AS to_last_maintenance_date , icn.last_calibration_date AS to_last_calibration_date , icn.meter_reading AS to_meter_reading , icn.expiration_date AS to_expiration_date , icn.extended_flg AS to_extended_flg , icn.failed_flg AS to_failed_flg , icn.uii_ky AS to_uii_ky , uii.c_uii AS to_uii , icn.uii_status_cd AS to_uii_status_cd -- Old Inventory Fields , im.condition_cd AS from_condition_cd , cc.dspl_desc AS from_condition_desc , im.owning_dodaac AS from_owning_dodaac , im.project_ky AS from_project_ky , proj.project_cd AS from_project_cd , im.qty AS from_qty , CAST(NULL AS VARCHAR) AS from_reason_cd , iur.description AS from_reason_cd_desc , c.container_ky AS from_container_ky , c.container_id AS from_container_id , c.container_desc AS from_container_desc , l.location_ky AS from_location_ky , l.location_id AS from_location_id , l.description AS from_location_desc , f.facility_ky AS from_facility_ky , f.facility_nbr AS from_facility_nbr , f.description AS from_facility_desc , w.warehouse_ky AS from_warehouse_ky , w.warehouse_dodaac as from_warehouse_dodaac , w.name AS from_warehouse_name , w.description AS from_warehouse_desc , w.routing_identifer_cd as from_warehouse_ric -- New Inventory Fields , im.condition_cd AS to_condition_cd , cc.dspl_desc AS to_condition_desc , im.owning_dodaac AS to_owning_dodaac , im.project_ky AS to_project_ky , proj.project_cd AS to_project_cd , im.qty AS to_qty , CAST(NULL AS VARCHAR) AS to_reason_cd , iur.description AS to_reason_cd_desc , c.container_ky AS to_container_ky , c.container_id AS to_container_id , c.container_desc AS to_container_desc , l.location_ky AS to_location_ky , l.location_id AS to_location_id , l.description AS to_location_desc , f.facility_ky AS to_facility_ky , f.facility_nbr AS to_facility_nbr , f.description AS to_facility_desc , im.warehouse_ky AS to_warehouse_ky , w.warehouse_dodaac AS to_warehouse_dodaac , w.name AS to_warehouse_name , w.description AS to_warehouse_desc , w.routing_identifer_cd as to_warehouse_ric -- Additional Tier Info Fields , s.site_ky AS to_site_ky , s.name AS to_site_name , s.description AS to_site_desc , r.region_ky AS to_region_ky , r.name AS to_region_name , r.description AS to_region_desc , lp.logistics_program_ky AS to_logistics_program_ky , lp.name AS to_logistics_program_name , lp.description AS to_logistics_program_desc -- Other Info Fields , im.document_nbr_ky AS document_nbr_ky , doc.document_nbr AS document_nbr , doc.to_dodaac , doc.from_dodaac , CAST(NULL AS VARCHAR) AS reason_desc , stockNew.pgm_id , stockNew.lst_tran_dt_tm AS as_of_dt_tm , stockNew.lst_updtd_by AS change_by , stockNew.history_record_generated_dt_tm FROM his.cat_stock_nbr stockNew -- Get the most recent stock number history record that was there before the new record was logged CROSS APPLY (SELECT stock_nbr,item_desc,unit_price,ui_conversion_factor,lin_tamcn_ky ,reportable_commodity_type_ky,security_commodity_type_ky,stocking_unit_of_issue_cd ,stores_account_cd,stock_item_cd,federal_supply_class_cd,history_record_generated_dt_tm, ROW_NUMBER()OVER(PARTITION BY stock_nbr_ky ORDER BY history_record_generated_dt_tm DESC) rownum FROM his.cat_stock_nbr priorRecord WHERE priorRecord.stock_nbr_ky = stockNew.stock_nbr_ky AND priorRecord.cat_stock_nbr_his_ky < stockNew.cat_stock_nbr_his_ky AND priorRecord.history_record_generated_dt_tm < stockNew.history_record_generated_dt_tm )stockOld -- Get all the inventory control numbers that ever existed for this stock number CROSS APPLY (SELECT inventory_control_nbr_ky,wm_inventory_control_nbr_his_ky,inventory_control_nbr,stock_nbr_ky,asset_id ,prfid,manufacturer_cage,manufacturer_contract,manufacturer_lot,manufactured_date,serial_nbr,secondary_serial_nbr ,last_inspection_date,next_inspection_date,inspected_by,last_test_date,last_maintenance_date,last_calibration_date ,meter_reading,expiration_date,extended_flg,failed_flg,uii_ky,uii_status_cd,history_record_generated_dt_tm, ROW_NUMBER()OVER(PARTITION BY inventory_control_nbr_ky ORDER BY history_record_generated_dt_tm DESC) rownum FROM his.wm_inventory_control_nbr priorRecord WHERE priorRecord.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm AND priorRecord.stock_nbr_ky = stockNew.stock_nbr_ky) icn -- Get all non-zero, fully accepted inventory records that ever existed for each of the inventory control number records -- Filter the inventory records down only to the most recent record for each (as of the new stock number record date) CROSS APPLY (SELECT wm_inventory_master_his_ky,location_ky,container_ky,warehouse_ky,condition_cd,inventory_type_ky,parent_icn_ky,reason_cd ,owning_dodaac,project_ky,qty,document_nbr_ky,history_record_generated_dt_tm, ROW_NUMBER()OVER(PARTITION BY inventory_master_ky ORDER BY history_record_generated_dt_tm DESC) rownum FROM his.wm_inventory_master imAll WHERE imAll.inventory_control_nbr_ky = icn.inventory_control_nbr_ky AND imAll.qty > 0 -- non-zero AND (imAll.project_ky <> -1 OR imAll.project_ky IS NULL) AND imAll.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ) im -- Get most recent container records (as of the new stock number record date) OUTER APPLY (SELECT TOP 1 wm_container_his_ky,location_ky,container_ky,container_id,container_desc FROM his.wm_container asOf WHERE asOf.container_ky = im.container_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) c -- Get most recent location records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_location_his_ky,zone_ky,location_ky,location_id,description FROM his.wm_location asOf WHERE (asOf.location_ky =im.location_ky OR asOf.location_ky = c.location_ky) AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) l -- Get most recent zone records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_zone_his_ky,facility_ky FROM his.wm_zone asOf WHERE asOf.zone_ky = l.zone_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) z -- Get most recent facility records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_facility_his_ky,facility_ky,facility_nbr,description FROM his.wm_facility asOf WHERE asOf.facility_ky = z.facility_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) f -- Get most recent warehouse records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_warehouse_his_ky,site_ky,warehouse_ky,warehouse_dodaac,name,description,routing_identifer_cd FROM his.wm_warehouse asOf WHERE asOf.warehouse_ky = im.warehouse_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) w -- Get most recent site records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_site_his_ky,region_ky,site_ky,name,description FROM his.wm_site asOf WHERE asOf.site_ky = w.site_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) s -- Get most recent region records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_region_his_ky,logistics_program_ky,region_ky,name,description FROM his.wm_region asOf WHERE asOf.region_ky = s.region_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) r -- Get most recent LP records (as of the new stock number record date) CROSS APPLY (SELECT TOP 1 wm_logistics_program_his_ky,logistics_program_ky,name,description FROM his.wm_logistics_program asOf WHERE asOf.logistics_program_ky = r.logistics_program_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) lp -- Get most recent LIN/TAMCN record that was referenced by the old stock number (as of old stock number date) OUTER APPLY (SELECT TOP 1 cat_lin_tamcn_his_ky,lin_tamcn_ky,lin_tamcn_id,lin_tamcn_desc FROM his.cat_lin_tamcn asOf WHERE asOf.lin_tamcn_ky = stockOld.lin_tamcn_ky AND asOf.history_record_generated_dt_tm <= stockOld.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC) from_lin -- Get most recent LIN/TAMCN record that was referenced by the new stock number (as of new stock number date) OUTER APPLY (SELECT TOP 1 cat_lin_tamcn_his_ky,lin_tamcn_ky,lin_tamcn_id,lin_tamcn_desc FROM his.cat_lin_tamcn asOf WHERE asOf.lin_tamcn_ky = stockNew.lin_tamcn_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_lin -- Get most recent Reportable Commodity record that was referenced by the old stock number (as of old stock number date) OUTER APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = stockOld.reportable_commodity_type_ky AND asOf.history_record_generated_dt_tm <= stockOld.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_rct -- Get most recent Reportable Commodity record that was referenced by the new stock number (as of new stock number date) OUTER APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = stockNew.reportable_commodity_type_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_rct -- Get most recent Security Commodity record that was referenced by the old stock number (as of old stock number date) OUTER APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = stockOld.security_commodity_type_ky AND asOf.history_record_generated_dt_tm <= stockOld.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)from_sct -- Get most recent Security Commodity record that was referenced by the new stock number (as of new stock number date) OUTER APPLY (SELECT TOP 1 cat_commodity_type_his_ky,commodity_type_ky,commodity_type_id,commodity_description FROM his.cat_commodity_type asOf WHERE asOf.commodity_type_ky = stockNew.security_commodity_type_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)to_sct -- Get most recent project records (as of the inventory record date) OUTER APPLY (SELECT TOP 1 wm_project_his_ky,project_cd FROM his.wm_project asOf WHERE asOf.project_ky = im.project_ky AND asOf.history_record_generated_dt_tm <= im.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)proj -- Get most recent document records (as of the inventory record date) OUTER APPLY (SELECT TOP 1 wm_document_his_ky,document_nbr,to_dodaac,from_dodaac FROM his.wm_document asOf WHERE asOf.document_nbr_ky = im.document_nbr_ky AND asof.history_record_generated_dt_tm <= im.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)doc -- Get all the UII records that relate to each of the inventory control number records OUTER APPLY (SELECT TOP 1 wm_uii_his_ky,c_uii FROM his.wm_uii asOf WHERE asOf.uii_ky = icn.uii_ky AND asOf.history_record_generated_dt_tm <= stockNew.history_record_generated_dt_tm ORDER BY history_record_generated_dt_tm DESC)uii -- Join static code tables LEFT OUTER JOIN cat.condition_lkp cc ON im.condition_cd = cc.cond_cd LEFT OUTER JOIN wm.inventory_update_reason_lkp iur ON im.reason_cd = iur.reason_cd LEFT OUTER JOIN cat.unit_of_issue_lkp from_sui ON from_sui.un_iss_cd = stockOld.stocking_unit_of_issue_cd LEFT OUTER JOIN cat.unit_of_issue_lkp to_sui ON to_sui.un_iss_cd = stockNew.stocking_unit_of_issue_cd LEFT OUTER JOIN cat.stores_account_lkp from_sal ON from_sal.stores_account_cd = stockOld.stores_account_cd LEFT OUTER JOIN cat.stores_account_lkp to_sal ON to_sal.stores_account_cd = stockNew.stores_account_cd LEFT OUTER JOIN cat.stock_item_lkp from_sil ON from_sil.stk_itm_cd = stockOld.stock_item_cd LEFT OUTER JOIN cat.stock_item_lkp to_sil ON to_sil.stk_itm_cd = stockNew.stock_item_cd LEFT OUTER JOIN cat.federal_supply_class_lkp from_fsc ON from_fsc.fed_sply_cls_cd = stockOld.federal_supply_class_cd LEFT OUTER JOIN cat.federal_supply_class_lkp to_fsc ON to_fsc.fed_sply_cls_cd = stockNew.federal_supply_class_cd WHERE stockOld.rownum = 1 AND icn.rownum = 1 AND im.rownum = 1 AND stockNew.operation$ <> 2 GO