Tomas Tech All Project Management » A.N.I. LOGISTICS, LTD.

USE [wms-ani-ckd]

GO

/****** Object:  StoredProcedure [dbo].[sp_insert_production_schedule_outer_from_hatc_filename2]    Script Date: 10/25/2023 2:01:04 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_insert_production_schedule_outer_from_hatc_filename2] 

	@filename VARCHAR(1000)

AS

BEGIN

SET NOCOUNT ON

-- DECLARE TEMP TABLE

DECLARE @tempTable table (

	pc_no VARCHAR(20)

	,part_no VARCHAR(20)

	,part_color VARCHAR(20)

	,control_no VARCHAR(20)

	,model_no VARCHAR(20)

	,maker_code VARCHAR(20)

	,line_no VARCHAR(20)

	,case_no NUMERIC

	,case_packing_seq NUMERIC

	,item_no NUMERIC

	,packing_no NUMERIC

	,c_no VARCHAR(10)

	,cont_no VARCHAR(10)

	,case_code VARCHAR(20)

	,set_part_seq NUMERIC

	,staging_plan_date VARCHAR(50)

	,staging_plan_qty NUMERIC

	,inner_plan_date VARCHAR(20)

	,inner_plan_qty NUMERIC

	,outer_plan_date VARCHAR(20)

	,outer_plan_qty NUMERIC

	,vanning_plan_date VARCHAR(20)

	,packing_qty_in_case NUMERIC

	,bag_qty_in_packing NUMERIC

	,parts_qty_in_bag NUMERIC

	,hatc_ref_id NUMERIC

	,linecontrol_ref_id NUMERIC

	,tagreceive_ref_id NUMERIC

)

-- CREATE OUTER SCHEDULE DETAIL

DECLARE @pc_no VARCHAR(20)

DECLARE @part_no VARCHAR(20)

DECLARE @part_color VARCHAR(20)

DECLARE @control_no VARCHAR(20)

DECLARE @model_no VARCHAR(20)

DECLARE @maker_code VARCHAR(20)

DECLARE @line_no VARCHAR(20)

DECLARE @case_packing_seq NUMERIC

DECLARE @item_no NUMERIC

DECLARE @packing_no NUMERIC

DECLARE @c_no VARCHAR(10)

DECLARE @cont_no VARCHAR(10)

DECLARE @case_code VARCHAR(20)

DECLARE @set_part_seq NUMERIC

DECLARE @staging_plan_date VARCHAR(50)

DECLARE @inner_plan_date VARCHAR(20)

DECLARE @outer_plan_date VARCHAR(20)

DECLARE @vanning_plan_date VARCHAR(20)

DECLARE @packing_qty_in_case NUMERIC

DECLARE @bag_qty_in_packing NUMERIC

DECLARE @parts_qty_in_bag NUMERIC

DECLARE @hatc_ref_id NUMERIC

DECLARE @linecontrol_ref_id NUMERIC

DECLARE @tagreceive_ref_id NUMERIC

DECLARE @case_no_from NUMERIC

DECLARE @case_no_to NUMERIC

DECLARE @staging_plan_qty NUMERIC

DECLARE @inner_plan_qty NUMERIC

DECLARE @outer_plan_qty NUMERIC

DECLARE @current_case_no NUMERIC

DECLARE @qty NUMERIC

DECLARE sp_insert_production_schedule_outer_from_hatc_filename_cs_ CURSOR FOR

SELECT

h.pc_no

, h.dc_full_part_no

, h.part_color

, h.control_no

, h.model_code

, h.maker_code

, t.line_no

-- Will extract the case

, CONVERT(int, h.case_no_from) As case_no_from

, CONVERT(int, h.case_no_to) As case_no_to

, h.case_packing_seq

, h.item_no

, h.packing_no

--, l.case_no -- Not use this value

--, l.container_no

, h.case_code

, ISNULL(seq.set_part_seq, 0)

, [dbo].[getWorkingDate](h.packing_date, -1) -- staging_plan_date

, h.packing_date -- inner_plan_date

, h.packing_date -- outer_plan_date

, [dbo].[getWorkingDate](h.packing_date, 1) -- vanning_plan_date

--, h.packing_qty_in_case

, IIF( CAST(parts_qty_in_packing AS int) = 0 AND CAST(parts_qty_in_bag AS int) = 0

		, 1 -- For direct items

		, h.packing_qty_in_case

		)

--, h.parts_qty_in_packing

, IIF( CAST(parts_qty_in_packing AS int) = 0 AND CAST(parts_qty_in_bag AS int) = 0

		, h.packing_qty_in_case -- For direct items

		, h.parts_qty_in_packing

		)

, h.parts_qty_in_bag

, h.id

--, l.id

, t.id

, IIF( CAST(parts_qty_in_packing AS int) = 0 AND CAST(parts_qty_in_bag AS int) = 0

		, CAST(packing_qty_in_case AS int) 

			* 1 -- CAST(case_usage AS int)

		, IIF(CAST(parts_qty_in_packing AS int)=0,1,CAST(parts_qty_in_packing AS int))

			* IIF(CAST(parts_qty_in_bag AS int)=0,1,CAST(parts_qty_in_bag AS int))

			* CAST(packing_qty_in_case AS int) 

			* 1 --IIF(model_code = 'PPP', 1, CAST(case_usage AS int))

		)

FROM 

(

SELECT *

FROM OriginalHatc 

WHERE filename=@filename

AND ISNUMERIC(case_no_from)=1

AND part_no NOT LIKE '%E+%'

AND pc_no NOT LIKE '%E+%'

) h

--LEFT JOIN OriginalLineControl l

--ON RIGHT(h.pc_no, 6) = REPLACE(l.pc_no, '-', '')

--AND ( 

--	( h.model_code!='PPP' AND h.control_no = l.control_no )

--	OR ( h.model_code='PPP' AND LEFT(h.control_no,4) = LEFT(l.control_no,4) ) 

--	)

/** Fixed use container no from Line Control only **/

--AND ( 

--	CAST(IIF(ISNUMERIC(l.case_no)=1, l.case_no, 0) AS Int)

--		BETWEEN CAST(h.case_no_from As int) AND CAST(h.case_no_to As int) 

--	)

LEFT JOIN SetPartSeqMaster seq

ON REPLACE(REPLACE(seq.part_no, '-', ''), ' ', '') = REPLACE(h.dc_full_part_no, ' ', '')

AND seq.control_no = h.control_no

LEFT JOIN OriginalTagReceive t

ON h.dc_full_part_no = t.part_no

AND h.pc_no = t.pc_no

AND h.part_color = t.part_color

AND ( 

	( h.model_code!='PPP' AND LEFT(h.control_no,3) = LEFT(t.control_no,3) )

	OR ( h.model_code='PPP' AND LEFT(h.control_no,4) = LEFT(t.control_no,4)) 

	)

/** Add to map with SEQ_ITM(case_no-packing_no) for PBP **/

AND ( t.model_no != 'PPP'

	OR ( t.model_no = 'PPP' AND CAST([dbo].[Wordparser] ( t.seq_itm, 1, '-') As int) BETWEEN CAST(h.case_no_from As int) AND CAST(h.case_no_to As int) )

	)

OPEN sp_insert_production_schedule_outer_from_hatc_filename_cs_

FETCH NEXT FROM sp_insert_production_schedule_outer_from_hatc_filename_cs_

INTO 

@pc_no

, @part_no

, @part_color

, @control_no

, @model_no

, @maker_code

, @line_no

, @case_no_from

, @case_no_to

, @case_packing_seq

, @item_no

, @packing_no

--, @c_no

--, @cont_no

, @case_code

, @set_part_seq

, @staging_plan_date

, @inner_plan_date

, @outer_plan_date

, @vanning_plan_date

, @packing_qty_in_case

, @bag_qty_in_packing

, @parts_qty_in_bag

, @hatc_ref_id

--, @linecontrol_ref_id

, @tagreceive_ref_id

, @qty

	;

WHILE (@@FETCH_STATUS = 0)

BEGIN

	SET @current_case_no = @case_no_from

	WHILE (@current_case_no <= @case_no_to)

	BEGIN

		-- Find container no

		SET @cont_no = NULL

		SET @linecontrol_ref_id = NULL

		SELECT 

			@cont_no = container_no

			, @linecontrol_ref_id = id

		FROM OriginalLineControl

		WHERE RIGHT(@pc_no, 6) = REPLACE(pc_no, '-', '')

			AND ( 

				( @model_no!='PPP' AND @control_no = control_no )

				OR ( @model_no='PPP' AND LEFT(@control_no,4) = LEFT(control_no,4) ) 

				)

			/** Fixed use container no from Line Control only **/

			AND @current_case_no = CAST(IIF(ISNUMERIC(case_no)=1, case_no, 0) AS Int)

		-- Insert into temp table

		MERGE @tempTable AS Target

		USING 

		(

			SELECT

				@pc_no AS pc_no

				, @part_no AS part_no

				, @part_color AS part_color

				, @control_no AS control_no

				, @model_no AS model_no

				, @maker_code AS maker_code

				, @line_no AS line_no

				, @current_case_no AS case_no

				, @case_packing_seq AS case_packing_seq

				, @item_no AS item_no

				, @packing_no AS packing_no

				, @current_case_no AS c_no -- Used case_no as c_no

				, @cont_no AS cont_no

				, @case_code AS case_code

				, @set_part_seq AS set_part_seq

				, @staging_plan_date AS staging_plan_date

				, @qty AS staging_plan_qty

				, @inner_plan_date AS inner_plan_date

				, @qty AS inner_plan_qty

				, @outer_plan_date AS outer_plan_date

				, @qty AS outer_plan_qty

				, @vanning_plan_date AS vanning_plan_date

				, @packing_qty_in_case AS packing_qty_in_case

				, @bag_qty_in_packing AS bag_qty_in_packing

				, @parts_qty_in_bag AS parts_qty_in_bag

				, @hatc_ref_id AS hatc_ref_id

				, @linecontrol_ref_id AS linecontrol_ref_id

				, @tagreceive_ref_id AS tagreceive_ref_id

		)

		AS Source

		ON Source.pc_no = Target.pc_no

		AND Source.part_no = Target.part_no

		AND Source.part_color = Target.part_color

		AND Source.control_no = Target.control_no

		AND Source.case_no = Target.case_no

		AND Source.case_packing_seq = Target.case_packing_seq

		AND Source.item_no = Target.item_no

		AND Source.packing_no = Target.packing_no

		WHEN NOT MATCHED BY Target THEN

			INSERT (

				[pc_no]

				,[part_no]

				,[part_color]

				,[control_no]

				,[model_no]

				,[maker_code]

				,[line_no]

				,[case_no]

				,[case_packing_seq]

				,[item_no]

				,[packing_no]

				,[c_no]

				,[cont_no]

				,[case_code]

				,[set_part_seq]

				,[staging_plan_date]

				,[staging_plan_qty]

				,[inner_plan_date]

				,[inner_plan_qty]

				,[outer_plan_date]

				,[outer_plan_qty]

				,[vanning_plan_date]

				,[packing_qty_in_case]

				,[bag_qty_in_packing]

				,[parts_qty_in_bag]

				,[hatc_ref_id]

				,[linecontrol_ref_id]

				,[tagreceive_ref_id]

				)

			VALUES (

				Source.pc_no

				, Source.part_no

				, Source.part_color

				, Source.control_no

				, Source.model_no

				, Source.maker_code

				, Source.line_no

				, Source.case_no

				, Source.case_packing_seq

				, Source.item_no

				, Source.packing_no

				, Source.c_no

				, Source.cont_no

				, Source.case_code

				, Source.set_part_seq

				, Source.staging_plan_date

				, Source.staging_plan_qty

				, Source.inner_plan_date

				, Source.inner_plan_qty

				, Source.outer_plan_date

				, Source.outer_plan_qty

				, Source.vanning_plan_date

				, Source.packing_qty_in_case

				, Source.bag_qty_in_packing

				, Source.parts_qty_in_bag

				, Source.hatc_ref_id

				, Source.linecontrol_ref_id

				, Source.tagreceive_ref_id

				);

		-- Goto next case

		SET @current_case_no = @current_case_no + 1

	END

	FETCH NEXT FROM sp_insert_production_schedule_outer_from_hatc_filename_cs_

	INTO 

	@pc_no

	, @part_no

	, @part_color

	, @control_no

	, @model_no

	, @maker_code

	, @line_no

	, @case_no_from

	, @case_no_to

	, @case_packing_seq

	, @item_no

	, @packing_no

	--, @c_no

	--, @cont_no

	, @case_code

	, @set_part_seq

	, @staging_plan_date

	, @inner_plan_date

	, @outer_plan_date

	, @vanning_plan_date

	, @packing_qty_in_case

	, @bag_qty_in_packing

	, @parts_qty_in_bag

	, @hatc_ref_id

	--, @linecontrol_ref_id

	, @tagreceive_ref_id

	, @qty

END

CLOSE sp_insert_production_schedule_outer_from_hatc_filename_cs_;

DEALLOCATE sp_insert_production_schedule_outer_from_hatc_filename_cs_;

-- 2023-10-25 : Replace the simple insert with merge with duplicated check

MERGE ProductionScheduleOuter AS Target

USING @tempTable AS s

ON s.pc_no = Target.pc_no

		AND s.part_no = Target.part_no

		AND s.part_color = Target.part_color

		AND s.control_no = Target.control_no

		AND s.case_no = Target.case_no

		AND s.case_packing_seq = Target.case_packing_seq

		AND s.item_no = Target.item_no

		AND s.packing_no = Target.packing_no

WHEN NOT MATCHED BY Target THEN

-- Finally copy all entries to ProductionScheduleOuter

-- Insert outer detail

		INSERT 

		(

			[pc_no]

			,[part_no]

			,[part_color]

			,[control_no]

			,[model_no]

			,[maker_code]

			,[line_no]

			,[case_no]

			,[case_packing_seq]

			,[item_no]

			,[packing_no]

			,[c_no]

			,[cont_no]

			,[case_code]

			,[set_part_seq]

			,[staging_plan_date]

			,[staging_plan_qty]

			,[inner_plan_date]

			,[inner_plan_qty]

			,[outer_plan_date]

			,[outer_plan_qty]

			,[vanning_plan_date]

			,[packing_qty_in_case]

			,[bag_qty_in_packing]

			,[parts_qty_in_bag]

			,[hatc_ref_id]

			,[linecontrol_ref_id]

			,[tagreceive_ref_id]

			,[status]

			,[stage]

			,[create_user_id]

		)

		VALUES ( 

			s.[pc_no]

			, s.[part_no]

			, s.[part_color]

			, s.[control_no]

			, s.[model_no]

			, s.[maker_code]

			, s.[line_no]

			, s.[case_no]

			, s.[case_packing_seq]

			, s.[item_no]

			, s.[packing_no]

			, s.[c_no]

			, s.[cont_no]

			, s.[case_code]

			, s.[set_part_seq]

			, s.[staging_plan_date]

			, s.[staging_plan_qty]

			, s.[inner_plan_date]

			, s.[inner_plan_qty]

			, s.[outer_plan_date]

			, s.[outer_plan_qty]

			, s.[vanning_plan_date]

			, s.[packing_qty_in_case]

			, s.[bag_qty_in_packing]

			, s.[parts_qty_in_bag]

			, s.[hatc_ref_id]

			, s.[linecontrol_ref_id]

			, s.[tagreceive_ref_id]

			,'IMPORTED'

			,'IMPORTED'

			, 'admin'

		);

END

GO