· 8 years ago · Dec 12, 2017, 07:46 PM
1CREATE PROCEDURE [dbo].[p_import_payroll_process_2]
2 @JobQueueID INT,
3 @ClientID INT
4AS
5BEGIN
6
7--2. Add any missing Control Budgets Find all possible new Control Budgets.
8-- Select the Big 5 p-keys where not null, but Control Budget ID is null.
9
10 --- 2.1. Create the temp table
11
12 DROP TABLE IF EXISTS #tempControlBudgets
13
14 --- 2.2. Find all possible new Control Budgets. Select where the Big 5 ID's are not null, but Control Budget ID is null.
15
16 SELECT PAY.id,
17 PAY.ProjectID,
18 PAY.ProjectPhaseID,
19 PAY.WBSID,
20 PAY.DisciplineID,
21 PAY.ActivityTypeID,
22 PAY.CostTypeID,
23 PAY.BillingCodeID,
24 PAY.BillingGroupID,
25 PAY.IsProgressPerformance,
26 PAY.IsCommitAsExpended,
27 PAY.IsReserveAccount
28
29 INTO #tempControlBudgets
30 FROM STG_Payrolls PAY
31 WHERE
32 PAY.JobQueueId = @JobQueueID AND
33 PAY.ProjectID IS NOT NULL AND
34 PAY.ProjectPhaseID IS NOT NULL AND
35 PAY.WBSID IS NOT NULL AND
36 PAY.DisciplineID IS NOT NULL AND
37 PAY.ActivityTypeID IS NOT NULL AND
38 PAY.CostTypeID IS NOT NULL AND
39 PAY.ControlBudgetID IS NULL
40
41 --- 2.3. Iterate over the temp table with a cursor and Insert new Control Budgets; Update the Staged Payroll record with the ID.
42
43 DECLARE @id INT,
44 @ProjectID INT,
45 @ProjectPhaseID INT,
46 @WBSID INT,
47 @DisciplineID INT,
48 @ActivityTypeID INT,
49 @CostTypeID INT,
50 @BillingCodeID INT,
51 @BillingGroupID INT,
52 @IsProgressPerformance INT,
53 @IsCommitAsExpended INT,
54 @IsReserveAccount INT
55
56 DECLARE @MyCursor CURSOR;
57 SET @MyCursor = CURSOR FORWARD_ONLY FAST_FORWARD
58 FOR
59 SELECT
60 id,
61 ProjectID,
62 ProjectPhaseID,
63 WBSID,
64 DisciplineID,
65 ActivityTypeID,
66 CostTypeID,
67 BillingCodeID,
68 BillingGroupID,
69 IsProgressPerformance,
70 IsCommitAsExpended,
71 IsReserveAccount
72
73 FROM #tempControlBudgets
74
75 OPEN @MyCursor
76
77 FETCH NEXT FROM @MyCursor
78 INTO
79 @ProjectID,
80 @ProjectPhaseID,
81 @WBSID,
82 @DisciplineID,
83 @ActivityTypeID,
84 @CostTypeID,
85 @BillingCodeID,
86 @BillingGroupID,
87 @IsProgressPerformance,
88 @IsCommitAsExpended,
89 @IsReserveAccount
90
91 WHILE @@FETCH_STATUS = 0
92 BEGIN
93 BEGIN TRY
94
95 --IF (@ControlBudgetID IS NULL)
96 --BEGIN
97 --SET @STG_IsReserveAccount = ISNULL(@STG_IsReserveAccount,0);
98 -- SET @STG_IsProgressPerformance = ISNULL(@STG_IsProgressPerformance,0);
99 -- SET @STG_IsCommitAsExpended = ISNULL(@STG_IsCommitAsExpended,1);
100 -- DELETE FROM @budgetIDholder;
101 -- INSERT INTO @budgetIDholder
102 -- EXEC [p_create_control_budget]
103 -- @FoundProjectID,
104 -- @ProjectPhaseID,
105 -- @WBSID,
106 -- @DisciplineID,
107 -- @ActivityTypeID,
108 -- @CostTypeID,
109 -- @CurrencyID,
110 -- 1,
111 -- @STG_IsProgressPerformance,
112 -- @IsControlBudgetsLocked,
113 -- @STG_IsCommitAsExpended,
114 -- @STG_IsReserveAccount,
115 -- @UserID;
116 -- SELECT @ControlBudgetID = BudgetID
117 -- FROM @budgetIDholder
118 --END
119
120 -- Update the Staged Payroll record with the new ID.
121
122 END TRY
123 BEGIN CATCH
124
125 END CATCH
126
127 FETCH NEXT FROM @MyCursor
128 INTO @ProjectID,
129 @MaxTransactionDate,
130 @MinTransactionDate,
131 @ProjectStartDate,
132 @ProjectEndDate
133 END
134 CLOSE @MyCursor
135 DEALLOCATE @MyCursor
136
137
138END