/*DECLARE @empid VARCHAR(8) = '00001359'; DECLARE @appType VARCHAR(8) = 'P3.3' DECLARE @now VARCHAR(8) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd') DECLARE @AppraisalPeriod VARCHAR(8) = 'P4323';*/
DECLARE @startDate VARCHAR(8);
SET @startDate = 
(
	SELECT Startdate 
	FROM PCMAPPPERIOD 
	WHERE code =  @AppraisalPeriod
)
DECLARE @EndDate VARCHAR(8);
SET @EndDate = 
(
	SELECT Enddate 
	FROM PCMAPPPERIOD 
	WHERE code =  @AppraisalPeriod
)
DECLARE @job VARCHAR(8) DECLARE @classobject VARCHAR(8) DECLARE @Bobot TABLE (
  ObjectID VARCHAR(16), 
  Bobot INT
) DECLARE @TableTemp TABLE (
  Value1 VARCHAR(50), 
  Value2 VARCHAR(50)
) INSERT INTO @TableTemp 
SELECT distinct
  GEN.Value1, 
  GEN.Value2 
FROM 
  dbo.PCMEPGENPARAM GEN 
WHERE 
  Parameter = 'ESSPMSREVIEW4' 
DECLARE @Review VARCHAR(8);
SET @Review = 
(
	SELECT 
        Case when Value2 = 'Weight6' then 'Review6'
		when Value2 = 'Weight3' then 'Review3' 
		when Value2 = 'Weight9' then 'Review9'
		end 
      FROM 
        @TableTemp 
      WHERE 
        Value1 = @appType
)
if (@Review = 'Review3') 
Begin
  DECLARE @Query AS VARCHAR(MAX) = CONCAT(
    'SELECT DISTINCT KPI,', 
    (
      SELECT 
        Value2 
      FROM 
        @TableTemp 
      WHERE 
        Value1 = @appType
    ), 
    ' as bobot FROM PHROM0007  where Review3 = 1 '
  ) INSERT INTO @Bobot EXEC (@Query)
SELECT 
  DISTINCT @job = CASE WHEN (
    pa02.Job IS NULL 
    OR pa02.Job = ''
  ) THEN pa02.Position ELSE pa02.Job END, 
  @classobject = CASE WHEN (
    pa02.Job IS NULL 
    OR pa02.Job = ''
  ) THEN 'P' ELSE 'J' END 
FROM 
  dbo.PHRPA0002 pa02 
WHERE 
  pa02.EmployeeID = @empid 
  AND pa02.StartDate <= @now 
  AND pa02.EndDate >= @now 
SELECT 
  DISTINCT (
    SELECT 
      ISNULL(
        MAX(Sequence) + 1, 
        1
      ) 
    FROM 
      dbo.PHRPA0047 
    WHERE 
      EmployeeID = @empid 
  ) AS HeaderSequence_49_3, 
  om02.ObjectID KPIGroup_49_3, 
  @empid EmployeeID_49_3, 
  om07.KPI KPI_49_3, 
  om01.ObjectDescription ObjectDescription_49_3, 
  Bbt.Bobot AS Weight_49_3a, 
  om07.Weight3 AS Weight_49_3,
  1 Target_49_3, 
  0 ScoreC_49_3, 
  om25.Asumsi Asumsi_49_3, 
  om25.Sourcedata SourceData_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 1 
      AND ObjectID = om07.KPI
  ) AS Kriteria1_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 2 
      AND ObjectID = om07.KPI
  ) AS Kriteria2_49_3, 
  0 App1Value_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 3 
      AND ObjectID = om07.KPI
  ) AS Kriteria3_49_3, 
  0 Unit_49_3, 
  0 ScoreNHA1_49_3, 
  0 NotesApr2_49_3, 
  '' NotesApr3_49_3, 
  0 WeightedScore_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 4 
      AND ObjectID = om07.KPI
  ) AS Kriteria4_49_3, 
  0 App10Percentage_49_3, 
  0 App2Value_49_3, 
  0 App10Projected_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 5 
      AND ObjectID = om07.KPI
  ) AS Kriteria5_49_3, 
  0 IsCalculate_49_3, 
  0 ScoreNHA2_49_3, 
  0 ScoreNHA3_49_3, 
  0 SelfValue_49_3, 
  0 IsSubKPI_49_3, 
  0 App10Value_49_3, 
  0 App1Percentage_49_3, 
  0 KPAchievementDesc_49_3, 
  0 App1Projected_49_3, 
  0 AppraiserScore_49_3, 
  0 NotesNHA_49_3, 
  0 SelfPercentage_49_3, 
  0 HasilKriteria_49_3, 
  0 SelfProjected_49_3, 
  0 App2Percentage_49_3, 
  0 App2Projected_49_3, 
  ROW_NUMBER() OVER (
    ORDER BY 
      om07.KPI
  ) AS Sequence_49_3, 
  0 SequenceSubKPI_49_3, 
  0 SubKPI_49_3, 
  0 App3Percentage_49_3, 
  0 App3Projected_49_3, 
  0 SubKPIDescription_49_3, 
  0 NotesApr1_49_3, 
  0 App3Value_49_3, 
  0 App4Percentage_49_3, 
  0 App4Projected_49_3, 
  0 App4Value_49_3, 
  0 App5Percentage_49_3, 
  0 App5Projected_49_3, 
  0 App5Value_49_3, 
  0 App6Percentage_49_3, 
  0 App6Projected_49_3, 
  0 App6Value_49_3, 
  0 App7Percentage_49_3, 
  0 App7Projected_49_3, 
  0 App7Value_49_3, 
  0 App8Percentage_49_3, 
  0 App8Projected_49_3, 
  0 App8Value_49_3, 
  0 App9Percentage_49_3, 
  0 App9Projected_49_3, 
  0 App9Value_49_3, 
  0 Notes_49_3, 
  om25.Responsible AS Respondesc_3, 
  @appType AS AppraisalType_49_3, 
  (
    SELECT 
      ISNULL(
        MAX(Sequence) + 1, 
        1
      ) 
    FROM 
      dbo.PHRPA0047 
    WHERE 
      EmployeeID = @empid
  ) AS HeaderSequence_49_3, 
  ROW_NUMBER() OVER (
    ORDER BY 
      om07.KPI
  ) AS Sequence_49_3, 
  @appType AS AppraisalType_3, 
  (
    SELECT 
      ISNULL(
        MAX(Sequence) + 1, 
        1
      ) 
    FROM 
      dbo.PHRPA0047 
    WHERE 
      EmployeeID = @empid 
  ) AS HeaderSequence_3, 
  ROW_NUMBER() OVER (
    ORDER BY 
      om07.KPI
  ) AS Sequence_3
FROM 
  PHROM0007 om07 
  INNER JOIN dbo.PHROM0001 om01 ON om07.KPI = om01.ObjectID 
  AND om01.ObjectClass = 'K' 
  INNER JOIN PHROM0002 om02 ON om07.ObjectID = om02.ObjectID 
  AND om02.ObjectClass = 'K' 
  AND om02.RelationshipClass = 'KG' 
  LEFT JOIN dbo.PHROM0025 om25 ON om25.ObjectID = om07.KPI 
  AND om25.ObjectClass = 'K' 
  AND om25.StartDate <= @now 
  AND om25.EndDate >= @now 
  INNER JOIN @Bobot Bbt ON om07.KPI = Bbt.ObjectID
WHERE 
  om07.ObjectClass = @classobject 
  AND om07.Review3 = 1 
  AND om07.ObjectID = @job
  AND om07.StartDate  <= @EndDate
  AND om07.EndDate >= @EndDate;
END 
if (@Review = 'Review6')
BEGIN
 DECLARE @Query1 AS VARCHAR(MAX) = CONCAT(
    'SELECT DISTINCT KPI,', 
    (
      SELECT 
        Value2 
      FROM 
        @TableTemp 
      WHERE 
        Value1 = @appType
    ), 
    ' as bobot FROM PHROM0007  where Review6 = 1 '
  ) INSERT INTO @Bobot EXEC (@Query1)
SELECT 
  DISTINCT @job = CASE WHEN (
    pa02.Job IS NULL 
    OR pa02.Job = ''
  ) THEN pa02.Position ELSE pa02.Job END, 
  @classobject = CASE WHEN (
    pa02.Job IS NULL 
    OR pa02.Job = ''
  ) THEN 'P' ELSE 'J' END 
FROM 
  dbo.PHRPA0002 pa02 
WHERE 
  pa02.EmployeeID = @empid 
  AND pa02.StartDate <= @now 
  AND pa02.EndDate >= @now 
SELECT 
  DISTINCT (
    SELECT 
      ISNULL(
        MAX(Sequence) + 1, 
        1
      ) 
    FROM 
      dbo.PHRPA0047 
    WHERE 
      EmployeeID = @empid 
  ) AS HeaderSequence_49_3, 
  om02.ObjectID KPIGroup_49_3, 
  @empid EmployeeID_49_3, 
  om07.KPI KPI_49_3, 
  om01.ObjectDescription ObjectDescription_49_3, 
  Bbt.Bobot AS Weight_49_3a, 
  om07.Weight3 AS Weight_49_3,
  1 Target_49_3, 
  0 ScoreC_49_3, 
  om25.Asumsi Asumsi_49_3, 
  om25.Sourcedata SourceData_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 1 
      AND ObjectID = om07.KPI
  ) AS Kriteria1_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 2 
      AND ObjectID = om07.KPI
  ) AS Kriteria2_49_3, 
  0 App1Value_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 3 
      AND ObjectID = om07.KPI
  ) AS Kriteria3_49_3, 
  0 Unit_49_3, 
  0 ScoreNHA1_49_3, 
  0 NotesApr2_49_3, 
  '' NotesApr3_49_3, 
  0 WeightedScore_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 4 
      AND ObjectID = om07.KPI
  ) AS Kriteria4_49_3, 
  0 App10Percentage_49_3, 
  0 App2Value_49_3, 
  0 App10Projected_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 5 
      AND ObjectID = om07.KPI
  ) AS Kriteria5_49_3, 
  0 IsCalculate_49_3, 
  0 ScoreNHA2_49_3, 
  0 ScoreNHA3_49_3, 
  0 SelfValue_49_3, 
  0 IsSubKPI_49_3, 
  0 App10Value_49_3, 
  0 App1Percentage_49_3, 
  0 KPAchievementDesc_49_3, 
  0 App1Projected_49_3, 
  0 AppraiserScore_49_3, 
  0 NotesNHA_49_3, 
  0 SelfPercentage_49_3, 
  0 HasilKriteria_49_3, 
  0 SelfProjected_49_3, 
  0 App2Percentage_49_3, 
  0 App2Projected_49_3, 
  ROW_NUMBER() OVER (
    ORDER BY 
      om07.KPI
  ) AS Sequence_49_3, 
  0 SequenceSubKPI_49_3, 
  0 SubKPI_49_3, 
  0 App3Percentage_49_3, 
  0 App3Projected_49_3, 
  0 SubKPIDescription_49_3, 
  0 NotesApr1_49_3, 
  0 App3Value_49_3, 
  0 App4Percentage_49_3, 
  0 App4Projected_49_3, 
  0 App4Value_49_3, 
  0 App5Percentage_49_3, 
  0 App5Projected_49_3, 
  0 App5Value_49_3, 
  0 App6Percentage_49_3, 
  0 App6Projected_49_3, 
  0 App6Value_49_3, 
  0 App7Percentage_49_3, 
  0 App7Projected_49_3, 
  0 App7Value_49_3, 
  0 App8Percentage_49_3, 
  0 App8Projected_49_3, 
  0 App8Value_49_3, 
  0 App9Percentage_49_3, 
  0 App9Projected_49_3, 
  0 App9Value_49_3, 
  0 Notes_49_3, 
  om25.Responsible AS Respondesc_3, 
  @appType AS AppraisalType_49_3, 
  (
    SELECT 
      ISNULL(
        MAX(Sequence) + 1, 
        1
      ) 
    FROM 
      dbo.PHRPA0047 
    WHERE 
      EmployeeID = @empid
  ) AS HeaderSequence_49_3, 
  ROW_NUMBER() OVER (
    ORDER BY 
      om07.KPI
  ) AS Sequence_49_3, 
  @appType AS AppraisalType_3, 
  (
    SELECT 
      ISNULL(
        MAX(Sequence) + 1, 
        1
      ) 
    FROM 
      dbo.PHRPA0047 
    WHERE 
      EmployeeID = @empid 
  ) AS HeaderSequence_3, 
  ROW_NUMBER() OVER (
    ORDER BY 
      om07.KPI
  ) AS Sequence_3
FROM 
  PHROM0007 om07 
  INNER JOIN dbo.PHROM0001 om01 ON om07.KPI = om01.ObjectID 
  AND om01.ObjectClass = 'K' 
  INNER JOIN PHROM0002 om02 ON om07.ObjectID = om02.ObjectID 
  AND om02.ObjectClass = 'K' 
  AND om02.RelationshipClass = 'KG' 
  LEFT JOIN dbo.PHROM0025 om25 ON om25.ObjectID = om07.KPI 
  AND om25.ObjectClass = 'K' 
  AND om25.StartDate <= @now 
  AND om25.EndDate >= @now 
  INNER JOIN @Bobot Bbt ON om07.KPI = Bbt.ObjectID
WHERE 
  om07.ObjectClass = @classobject 
  AND om07.Review6 = 1 
  AND om07.ObjectID = @job
  AND om07.StartDate  <= @EndDate
  AND om07.EndDate >= @EndDate;
END

if (@Review = 'Review9')
BEGIN
 DECLARE @Query2 AS VARCHAR(MAX) = CONCAT(
    'SELECT DISTINCT KPI,', 
    (
      SELECT 
        Value2 
      FROM 
        @TableTemp 
      WHERE 
        Value1 = @appType
    ), 
    ' as bobot FROM PHROM0007  where Review9 = 1 '
  ) INSERT INTO @Bobot EXEC (@Query2)
SELECT 
  DISTINCT @job = CASE WHEN (
    pa02.Job IS NULL 
    OR pa02.Job = ''
  ) THEN pa02.Position ELSE pa02.Job END, 
  @classobject = CASE WHEN (
    pa02.Job IS NULL 
    OR pa02.Job = ''
  ) THEN 'P' ELSE 'J' END 
FROM 
  dbo.PHRPA0002 pa02 
WHERE 
  pa02.EmployeeID = @empid 
  AND pa02.StartDate <= @now 
  AND pa02.EndDate >= @now 
SELECT 
  DISTINCT (
    SELECT 
      ISNULL(
        MAX(Sequence) + 1, 
        1
      ) 
    FROM 
      dbo.PHRPA0047 
    WHERE 
      EmployeeID = @empid 
  ) AS HeaderSequence_49_3, 
  om02.ObjectID KPIGroup_49_3, 
  @empid EmployeeID_49_3, 
  om07.KPI KPI_49_3, 
  om01.ObjectDescription ObjectDescription_49_3, 
  Bbt.Bobot AS Weight_49_3a, 
  om07.Weight3 AS Weight_49_3,
  1 Target_49_3, 
  0 ScoreC_49_3, 
  om25.Asumsi Asumsi_49_3, 
  om25.Sourcedata SourceData_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 1 
      AND ObjectID = om07.KPI
  ) AS Kriteria1_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 2 
      AND ObjectID = om07.KPI
  ) AS Kriteria2_49_3, 
  0 App1Value_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 3 
      AND ObjectID = om07.KPI
  ) AS Kriteria3_49_3, 
  0 Unit_49_3, 
  0 ScoreNHA1_49_3, 
  0 NotesApr2_49_3, 
  '' NotesApr3_49_3, 
  0 WeightedScore_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 4 
      AND ObjectID = om07.KPI
  ) AS Kriteria4_49_3, 
  0 App10Percentage_49_3, 
  0 App2Value_49_3, 
  0 App10Projected_49_3, 
  (
    SELECT 
      KeyBehavior 
    FROM 
      CRANGESCORE 
    WHERE 
      IndexScore = 5 
      AND ObjectID = om07.KPI
  ) AS Kriteria5_49_3, 
  0 IsCalculate_49_3, 
  0 ScoreNHA2_49_3, 
  0 ScoreNHA3_49_3, 
  0 SelfValue_49_3, 
  0 IsSubKPI_49_3, 
  0 App10Value_49_3, 
  0 App1Percentage_49_3, 
  0 KPAchievementDesc_49_3, 
  0 App1Projected_49_3, 
  0 AppraiserScore_49_3, 
  0 NotesNHA_49_3, 
  0 SelfPercentage_49_3, 
  0 HasilKriteria_49_3, 
  0 SelfProjected_49_3, 
  0 App2Percentage_49_3, 
  0 App2Projected_49_3, 
  ROW_NUMBER() OVER (
    ORDER BY 
      om07.KPI
  ) AS Sequence_49_3, 
  0 SequenceSubKPI_49_3, 
  0 SubKPI_49_3, 
  0 App3Percentage_49_3, 
  0 App3Projected_49_3, 
  0 SubKPIDescription_49_3, 
  0 NotesApr1_49_3, 
  0 App3Value_49_3, 
  0 App4Percentage_49_3, 
  0 App4Projected_49_3, 
  0 App4Value_49_3, 
  0 App5Percentage_49_3, 
  0 App5Projected_49_3, 
  0 App5Value_49_3, 
  0 App6Percentage_49_3, 
  0 App6Projected_49_3, 
  0 App6Value_49_3, 
  0 App7Percentage_49_3, 
  0 App7Projected_49_3, 
  0 App7Value_49_3, 
  0 App8Percentage_49_3, 
  0 App8Projected_49_3, 
  0 App8Value_49_3, 
  0 App9Percentage_49_3, 
  0 App9Projected_49_3, 
  0 App9Value_49_3, 
  0 Notes_49_3, 
  om25.Responsible AS Respondesc_3, 
  @appType AS AppraisalType_49_3, 
  (
    SELECT 
      ISNULL(
        MAX(Sequence) + 1, 
        1
      ) 
    FROM 
      dbo.PHRPA0047 
    WHERE 
      EmployeeID = @empid
  ) AS HeaderSequence_49_3, 
  ROW_NUMBER() OVER (
    ORDER BY 
      om07.KPI
  ) AS Sequence_49_3, 
  @appType AS AppraisalType_3, 
  (
    SELECT 
      ISNULL(
        MAX(Sequence) + 1, 
        1
      ) 
    FROM 
      dbo.PHRPA0047 
    WHERE 
      EmployeeID = @empid 
  ) AS HeaderSequence_3, 
  ROW_NUMBER() OVER (
    ORDER BY 
      om07.KPI
  ) AS Sequence_3
FROM 
  PHROM0007 om07 
  INNER JOIN dbo.PHROM0001 om01 ON om07.KPI = om01.ObjectID 
  AND om01.ObjectClass = 'K' 
  INNER JOIN PHROM0002 om02 ON om07.ObjectID = om02.ObjectID 
  AND om02.ObjectClass = 'K' 
  AND om02.RelationshipClass = 'KG' 
  LEFT JOIN dbo.PHROM0025 om25 ON om25.ObjectID = om07.KPI 
  AND om25.ObjectClass = 'K' 
  AND om25.StartDate <= @now 
  AND om25.EndDate >= @now 
  INNER JOIN @Bobot Bbt ON om07.KPI = Bbt.ObjectID
WHERE 
  om07.ObjectClass = @classobject 
  AND om07.Review9 = 1 
  AND om07.ObjectID = @job
  AND om07.StartDate  <= @EndDate
  AND om07.EndDate >= @EndDate;
END