CREATE PROCEDURE TMSP_Inventory_Counting_IndividualCounter (
   in DocKey  nvarchar(12),
   in ObjectId NCHAR(20)
)
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER
AS
DocEntry INT := :DocKey;
Begin
	IF (:ObjectId = '1470000065') then
		select 	"OINC"."DocEntry", "OINC"."DocNum", "OINC"."CountDate", 
				"OINC"."Time", "OINC"."CountType", "OINC"."IndvCount",
				"INC1"."ItemCode", "INC1"."ItemDesc",
				"INC1"."WhsCode", "INC1"."InWhsQty", 
				"INC1"."CountQty", "INC1"."BinEntry", 
				"INC1"."Counted", "OBIN"."BinCode",
				"INC81"."CounteName" as "IndvCounter1Name",
				"INC82"."CounteName" as "IndvCounter2Name", 
				"INC83"."CounteName" as "IndvCounter3Name",
				"INC84"."CounteName" as "IndvCounter4Name", 
				"INC85"."CounteName" as "IndvCounter5Name", 
				"INC91"."TotalQty" as "IndvCounter1Qty", 
				"INC92"."TotalQty" as "IndvCounter2Qty", 
				"INC93"."TotalQty" as "IndvCounter3Qty", 
				"INC94"."TotalQty" as "IndvCounter4Qty", 
				"INC95"."TotalQty" as "IndvCounter5Qty"  from "INC1"
				   left join "OINC" on "OINC"."DocEntry" = "INC1"."DocEntry"
				   left join "OBIN" on "INC1"."BinEntry" = "OBIN"."AbsEntry"
				   left join "INC8" as "INC81" on ("INC1"."DocEntry" = "INC81"."DocEntry" and "INC81"."VisOrder" = 1)
				   left join "INC8" as "INC82" on ("INC1"."DocEntry" = "INC82"."DocEntry" and "INC82"."VisOrder" = 2)
				   left join "INC8" as "INC83" on ("INC1"."DocEntry" = "INC83"."DocEntry" and "INC83"."VisOrder" = 3)
				   left join "INC8" as "INC84" on ("INC1"."DocEntry" = "INC84"."DocEntry" and "INC84"."VisOrder" = 4)
				   left join "INC8" as "INC85" on ("INC1"."DocEntry" = "INC85"."DocEntry" and "INC85"."VisOrder" = 5)				   
				   left join "INC9" as "INC91" on ("INC1"."DocEntry" = "INC91"."DocEntry" and "INC1"."LineNum" = "INC91"."LineNum" and "INC81"."CounterNum" = "INC91"."CounterNum" and "INC81"."VisOrder" = 1)
				   left join "INC9" as "INC92" on ("INC1"."DocEntry" = "INC92"."DocEntry" and "INC1"."LineNum" = "INC92"."LineNum" and "INC82"."CounterNum" = "INC92"."CounterNum" and "INC82"."VisOrder" = 2)
				   left join "INC9" as "INC93" on ("INC1"."DocEntry" = "INC93"."DocEntry" and "INC1"."LineNum" = "INC93"."LineNum" and "INC83"."CounterNum" = "INC93"."CounterNum" and "INC83"."VisOrder" = 3)
				   left join "INC9" as "INC94" on ("INC1"."DocEntry" = "INC94"."DocEntry" and "INC1"."LineNum" = "INC94"."LineNum" and "INC84"."CounterNum" = "INC94"."CounterNum" and "INC84"."VisOrder" = 4)			
				   left join "INC9" as "INC95" on ("INC1"."DocEntry" = "INC95"."DocEntry" and "INC1"."LineNum" = "INC95"."LineNum" and "INC85"."CounterNum" = "INC95"."CounterNum" and "INC85"."VisOrder" = 5)
		where "OINC"."DocEntry" = :DocEntry order by "INC1"."VisOrder";
	Else
			
		select "OICD"."DocEntry", "OICD"."DocNum", "OICD"."CountDate", 
			"OICD"."Time", "OICD"."CountType", "OICD"."IndvCount",
			"ICD1"."ItemCode", "ICD1"."ItemDesc",
			"ICD1"."WhsCode", "ICD1"."InWhsQty", 
			"ICD1"."CountQty", "ICD1"."BinEntry", 
			"ICD1"."Counted", "OBIN"."BinCode",
			"ICD81"."CounteName" as "IndvCounter1Name",
			"ICD82"."CounteName" as "IndvCounter2Name", 
			"ICD83"."CounteName" as "IndvCounter3Name",
			 "ICD84"."CounteName" as "IndvCounter4Name", 
			 "ICD85"."CounteName" as "IndvCounter5Name", 
			"ICD91"."TotalQty" as "IndvCounter1Qty", 
			"ICD92"."TotalQty" as "IndvCounter2Qty", 
			"ICD93"."TotalQty" as "IndvCounter3Qty", 
			"ICD94"."TotalQty" as "IndvCounter4Qty", 
			"ICD95"."TotalQty" as "IndvCounter5Qty"  from "ICD1"
			   left join "OICD" on "OICD"."DocEntry" = "ICD1"."DocEntry"
			   left join "OBIN" on "ICD1"."BinEntry" = "OBIN"."AbsEntry"
			   left join "ICD8" as "ICD81" on ("ICD1"."DocEntry" = "ICD81"."DocEntry" and "ICD81"."VisOrder" = 1)
			   left join "ICD8" as "ICD82" on ("ICD1"."DocEntry" = "ICD82"."DocEntry" and "ICD82"."VisOrder" = 2)
			   left join "ICD8" as "ICD83" on ("ICD1"."DocEntry" = "ICD83"."DocEntry" and "ICD83"."VisOrder" = 3)
			   left join "ICD8" as "ICD84" on ("ICD1"."DocEntry" = "ICD84"."DocEntry" and "ICD84"."VisOrder" = 4)
			   left join "ICD8" as "ICD85" on ("ICD1"."DocEntry" = "ICD85"."DocEntry" and "ICD85"."VisOrder" = 5)				   
			   left join "ICD9" as "ICD91" on ("ICD1"."DocEntry" = "ICD91"."DocEntry" and "ICD1"."LineNum" = "ICD91"."LineNum" and "ICD81"."CounterNum" = "ICD91"."CounterNum" and "ICD81"."VisOrder" = 1)
			   left join "ICD9" as "ICD92" on ("ICD1"."DocEntry" = "ICD92"."DocEntry" and "ICD1"."LineNum" = "ICD92"."LineNum" and "ICD82"."CounterNum" = "ICD92"."CounterNum" and "ICD82"."VisOrder" = 2)
			   left join "ICD9" as "ICD93" on ("ICD1"."DocEntry" = "ICD93"."DocEntry" and "ICD1"."LineNum" = "ICD93"."LineNum" and "ICD83"."CounterNum" = "ICD93"."CounterNum" and "ICD83"."VisOrder" = 3)
			   left join "ICD9" as "ICD94" on ("ICD1"."DocEntry" = "ICD94"."DocEntry" and "ICD1"."LineNum" = "ICD94"."LineNum" and "ICD84"."CounterNum" = "ICD94"."CounterNum" and "ICD84"."VisOrder" = 4)			
			   left join "ICD9" as "ICD95" on ("ICD1"."DocEntry" = "ICD95"."DocEntry" and "ICD1"."LineNum" = "ICD95"."LineNum" and "ICD85"."CounterNum" = "ICD95"."CounterNum" and "ICD85"."VisOrder" = 5)
		where "OICD"."DocEntry" = :DocEntry order by "ICD1"."VisOrder";
	END if;
End;