Hi

I'm running a stored procedure from DTS. The SP has got a WHILE LOOP that
should execute 3 times. When I execute it from QUERY ANALYSER it executes 3
times. However, when I execute it from DTS it only executes once. The SP
procedure doesn't take any parameters.

S/W: SQL Server 2000 - Developer Edition

Any ideas what could be the issue?

Many thanks
--
JayU

--
JayU

can you post your stored procedures and the code in DTS, too????

Ed

"Jay" wrote:

Ed

Please find enclosed the SP. I haven't included the DTS Package. The DTS
Package simply runs this procedure using execute sql task. The command is:

EXECUTE usp_load_volume

The same command works I execute it from Query Analyzer. However, when I
execute it from DTS it only loops once.

CREATE PROCEDURE [dbo].[usp_load_volume]
/***

$One-Liner
Takes wrk_volume data and loads into arc_volume first. Then takes
monthly difference between last periods
cumulative total and insert monthly volume amount into tbl_volume

$Example
EXECUTE usp_load_volume @i_meta_job_hist_id = 1

***/

( /* Input Parameters */
@i_meta_job_hist_id INT = 1,
@i_system_source NCHAR(3) = N'N/A'
)
AS

SET NOCOUNT ON


DECLARE @error INT,
@procedure_name SYSNAME,
@load_table SYSNAME,
@period_id INT,
@scenario_id INT,
@scenario_code VARCHAR(10),
@scenario_desc VARCHAR(50),
@screen_id INT,
@submission_defn_id INT,
@submission_defn_name NVARCHAR(50)


BEGIN

SET @procedure_name = OBJECT_NAME (@@PROCID)
SET @load_table = 'wrk_volume'

SELECT @screen_id = screen_id
FROM tbl_screen
WHERE screen_name = 'VOLUME'


EXEC usp_check_unique_period @i_meta_job_hist_id = @i_meta_job_hist_id,
@i_table_name = @load_table,
@o_period_id = @period_id OUTPUT

EXEC usp_check_unique_scenario @i_meta_job_hist_id = @i_meta_job_hist_id,
@i_table_name = @load_table,
@o_scenario_id = @scenario_id OUTPUT

SELECT @scenario_code = scenario_code,
@scenario_desc = scenario_desc
FROM tbl_scenario
WHERE scenario_id = @scenario_id

--- for monthly actual check that previous period has already been loaded
IF @scenario_code = '12' -- monthly actuals
AND SUBSTRING(CAST(@period_id as CHAR(6)),5,2) != '01' -- current
period not january
AND NOT EXISTS (
SELECT *
FROM arc_volume
WHERE period_id = @period_id - 1
AND scenario_id = @scenario_id
)
BEGIN

EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 404 -- Data not loaded for
previous period

GOTO error_return
END



--- for reference codes missing then raise error
IF EXISTS (
SELECT *
FROM wrk_volume v
LEFT JOIN tbl_service s
ON v.service_code = s.service_code

LEFT JOIN tbl_customer c
ON c.customer_code= v.customer_code

LEFT JOIN tbl_country cty
ON v.country_code = cty.country_code

LEFT JOIN tbl_coo_detail coo
ON cty.country_id = coo.country_id


LEFT JOIN tbl_currency cur
ON v.report_currency_code= cur.currency_code

LEFT JOIN tbl_currency cur2
ON v.transact_currency_code= cur2.currency_code

LEFT JOIN tbl_planning_entity p
ON p.planning_entity_code =v.planning_entity_code

LEFT JOIN tbl_scenario sc
ON v.scenario_code = sc.scenario_code

LEFT JOIN tbl_station st
ON p.station_id = st.station_id
WHERE s.service_code IS NULL
OR c.customer_code IS NULL
OR cty.country_code IS NULL
OR coo.country_id IS NULL
OR cur.currency_code IS NULL
OR cur2.currency_code IS NULL
OR p.planning_entity_code IS NULL
OR sc.scenario_code IS NULL
OR st.station_code IS NULL
)
BEGIN
EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 503 -- Missing reference data

GOTO error_return
END
--- delete records to avoid duplicates for the same scenario and period in
case of a re-run
DELETE a
FROM arc_volume a
WHERE a.period_id = @period_id
AND a.scenario_id = @scenario_id


EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'deleted records
where same scenario period already exist in arc_volume',
@i_error = @@error,
@i_rows = @@rowcount

--- Insert records from wrk to archive

INSERT INTO arc_volume
(
service_id,
report_currency_id,
local_currency_id,
transact_currency_id,
customer_id,
planning_entity_id,
scenario_id,
period_id,
coo_detail_id,
volume_amount_in_report_currency
)

SELECT

s.service_id,
cur.currency_id report_currency_id,
st.local_currency_id,
cur2.currency_id transact_currency_id,
c.customer_id,
p.planning_entity_id,
sc.scenario_id,
v.period_id,
coo.coo_detail_id,
volume_amount as volume_amount_in_report_currency

FROM wrk_volume v
INNER JOIN tbl_service s
ON v.service_code = s.service_code

INNER JOIN tbl_customer c
ON c.customer_code= v.customer_code

INNER JOIN tbl_country cty
ON v.country_code = cty.country_code

INNER JOIN tbl_coo_detail coo
ON cty.country_id = coo.country_id


INNER JOIN tbl_currency cur
ON v.report_currency_code= cur.currency_code

INNER JOIN tbl_currency cur2
ON v.transact_currency_code= cur2.currency_code

INNER JOIN tbl_planning_entity p
ON p.planning_entity_code =v.planning_entity_code

INNER JOIN tbl_scenario sc
ON v.scenario_code = sc.scenario_code

INNER JOIN tbl_station st
ON p.station_id = st.station_id


EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'inserted all
records into arc_volume from wrk_volume with cumulative_totals',
@i_error = @@error,
@i_rows = @@rowcount


-- reconcile all data from wrk_volume has been loaded into arc_volume
DECLARE @arc_volume_amt NUMERIC(18,4),
@wrk_volume_amt NUMERIC(18,4)

SELECT @wrk_volume_amt = SUM( volume_amount ) FROM wrk_volume

SELECT @arc_volume_amt = SUM (volume_amount_in_report_currency )
FROM arc_volume
WHERE period_id = @period_id
AND scenario_id = @scenario_id


IF ABS( @arc_volume_amt - @wrk_volume_amt ) > 1
BEGIN
EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 405 -- reconciliation mismatch
between staging and archive

GOTO error_return
END


-- Create submission definition


SELECT @submission_defn_name = 'VOLUME ' + scenario_desc + ' ' +
CAST(@period_id as varchar(8))
FROM tbl_scenario
WHERE scenario_id = @scenario_id

EXECUTE usp_create_submission_defn
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_scenario_id = @scenario_id,
@i_screen_id = @screen_id,
@i_period_id = @period_id,
@i_submission_defn_name = @submission_defn_name,
@o_submission_defn_id = @submission_defn_id OUTPUT


-- Insert individual submissions

INSERT INTO tbl_submission
( submission_datetime,
submission_defn_id,
planning_entity_id)
SELECT distinct getdate() as submission_date,
@submission_defn_id as submission_defn_id,
planning_entity_id
FROM tbl_planning_entity



EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'Insert
individual submissions',
@i_error = @@error,
@i_rows = @@rowcount

IF @error <> 0 GOTO error_return

-- load volume by looking at previous month

-- create previous months temp table
DECLARE @previous TABLE (
service_id INT,
coo_detail_id INT,
volume_amount NUMERIC(18,4),
transact_currency_id INT,
local_currency_id INT,
report_currency_id INT,
customer_id INT,
submission_id INT
)

INSERT INTO @previous
SELECT
v.service_id,
v.coo_detail_id,
v.volume_amount_in_report_currency as volume_amount,
v.transact_currency_id,
v.local_currency_id,
v.report_currency_id,
v.customer_id,
s.submission_id

FROM arc_volume v
INNER JOIN tbl_submission s
ON v.planning_entity_id = s.planning_entity_id
AND s.submission_defn_id = @submission_defn_id

WHERE v.period_id = @period_id - 1
AND v.scenario_id = @scenario_id

EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = '@previous
created with volume cumulative for previous month',
@i_error = @@error,
@i_rows = @@rowcount

-- create current months temp table
DECLARE @current TABLE (
service_id INT,
coo_detail_id INT,
volume_amount NUMERIC(18,4),
transact_currency_id INT,
local_currency_id INT,
report_currency_id INT,
customer_id INT,
submission_id INT
)

INSERT INTO @current
SELECT
v.service_id,
v.coo_detail_id,
v.volume_amount_in_report_currency as volume_amount,
v.transact_currency_id,
v.local_currency_id,
v.report_currency_id,
v.customer_id,
s.submission_id

FROM arc_volume v
INNER JOIN tbl_submission s
ON v.planning_entity_id = s.planning_entity_id
AND s.submission_defn_id = @submission_defn_id

WHERE v.period_id = @period_id
AND v.scenario_id = @scenario_id

EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = '@current
created with volume cumulative for current month',
@i_error = @@error,
@i_rows = @@rowcount

IF @error <> 0 GOTO error_return

--- Now load volume data by comparing the two months cumulative totals
INSERT INTO tbl_volume (
service_id,
coo_detail_id,
transact_currency_id,
local_currency_id,
report_currency_id,
customer_id,
submission_id,
volume_amount_in_report_currency )

SELECT

COALESCE(curr.service_id, prev.service_id)
service_id,
COALESCE(curr.coo_detail_id, prev.coo_detail_id)
coo_detail_id,
COALESCE(curr.transact_currency_id, prev.transact_currency_id)
transact_currency_id,
COALESCE(curr.local_currency_id, prev.local_currency_id)
local_currency_id,
COALESCE(curr.report_currency_id, prev.report_currency_id)
report_currency_id,
COALESCE(curr.customer_id, prev.customer_id)
customer_id,
COALESCE(curr.submission_id, prev.submission_id)
submission_id,
ISNULL (curr.volume_amount,0) - ISNULL(prev.volume_amount,0)
volume_amount

FROM @current CURR

FULL OUTER JOIN @previous PREV
ON curr.service_id = prev.service_id
AND curr.coo_detail_id = prev.coo_detail_id
AND curr.customer_id = prev.customer_id
AND curr.report_currency_id = prev.report_currency_id
AND curr.submission_id = prev.submission_id



EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'load volume
data with monthly totals calculated',
@i_error = @@error,
@i_rows = @@rowcount

IF @error <> 0 GOTO error_return

-- reconcile by matching cumulative total for that period,scenario from
arc with cumulative total from tbl_volume
DECLARE
@ods_volume_amt NUMERIC(18,4),
@ods_volume_cnt INT


SELECT
@ods_volume_amt = SUM(volume_amount_in_report_currency),
@ods_volume_cnt = COUNT(*)
FROM KQIS_POUGH_ODS..tbl_volume v

INNER JOIN KQIS_POUGH_ODS..tbl_submission s
ON v.submission_id = s.submission_id

INNER JOIN KQIS_POUGH_ODS..tbl_submission_defn sd
ON s.submission_defn_id = sd.submission_defn_id
AND sd.is_active_flag = 1 -- submission is active

WHERE sd.period_id > ROUND(@period_id/100,0) * 100 /** is > begin of
that year **/
AND sd.period_id <= @period_id /** and <= that
period **/
AND sd.scenario_id = @scenario_id /** and belongs to
that scenario **/

IF ABS(@ods_volume_amt - @arc_volume_amt) > 1
BEGIN
SELECT @ods_volume_amt as ods_volume_amt, @arc_volume_amt as
arc_volume_amt

EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 407 -- reconciliation mismatch
between archive and ODS

GOTO error_return
END

-- also update customer relation table using the relational from updated
volume table
EXECUTE usp_customer_relation_insert @i_meta_job_hist_id
=@i_meta_job_hist_id

success_return:
IF @error <> 0 GOTO error_return
RETURN 0
error_return:
IF @@trancount > 0
ROLLBACK TRANSACTION
RETURN @error
END

GO



"Ed" wrote:

Ed,

Please ignore the SP code in the previous post since I included a wrong SP
by mistake. Enclosing the correct one this time.

As previously explained, I'm simply executing

EXECUTE usp_load_cost @i_meta_job_hist_id = 1

from DTS execute sql command. It only loops once exiting without any error
message. The same command executed from QUERY ANALYZER works fine (i.e. does
the loop 3 times).



CREATE PROCEDURE [dbo].[usp_load_cost
/************************************************** ************************************************** **************************

$One-Liner
Takes wrk_cost data and loads into arc_cost first. Then takes monthly
difference between last periods
cumulative total and insert monthly cost amount into tbl_cost

$Example
EXECUTE usp_load_cost @i_meta_job_hist_id = 1

************************************************** ************************************************** ***************************/
( /* Input Parameters */
@i_meta_job_hist_id INT,
@i_system_source NCHAR(3) = N'N/A'
)
AS

SET NOCOUNT ON


DECLARE @error INT,
@procedure_name SYSNAME,
@load_table SYSNAME,
@period_id INT,
@scenario_id INT,
@screen_id INT,
@month_number NUMERIC(18,10),
@gen_period_id INT,
@splitter NUMERIC(18,10),
@submission_defn_id INT,
@scenario_code NVARCHAR(10),
@scenario_desc NVARCHAR(100),
@submission_defn_name NVARCHAR(50)

-- create previous months temp table
DECLARE @previous TABLE (
cost_amount NUMERIC(18,4),
account_id INT,
customer_id INT,
submission_id INT,
org_task_id INT,
occupation_id INT,
local_currency_id INT,
report_currency_id INT,
transact_currency_id INT
)

-- create current months temp table
DECLARE @current TABLE (
cost_amount NUMERIC(18,4),
account_id INT,
customer_id INT,
submission_id INT,
org_task_id INT,
occupation_id INT,
local_currency_id INT,
report_currency_id INT,
transact_currency_id INT
)

BEGIN
SET @procedure_name = OBJECT_NAME (@@PROCID)
SET @load_table = 'wrk_cost'

SELECT @screen_id = screen_id
FROM tbl_screen
WHERE screen_name = 'costs'


EXECUTE usp_check_unique_period
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_table_name = @load_table,
@o_period_id = @period_id OUTPUT

EXECUTE usp_check_unique_scenario
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_table_name = @load_table,
@o_scenario_id = @scenario_id OUTPUT


SELECT @scenario_code = scenario_code,
@scenario_desc = scenario_desc
FROM tbl_scenario
WHERE scenario_id = @scenario_id

SET @month_number = CAST(RIGHT(CAST(@period_id as char(6)),2) as INT)

-- Initiate WHILE loop to split the quarterly data into equal monthly parts
-- NB: Only split quarterly data for actuals...
IF @scenario_id = 12
SET @splitter = -2
ELSE
SET @splitter = 0

WHILE (@period_id + @splitter) <= @period_id
BEGIN
SET @gen_period_id = (@period_id + @splitter)

print @gen_period_id
--- for monthly actual check that previous period has already been loaded
IF @scenario_code = '12' -- monthly actuals
AND SUBSTRING(CAST(@gen_period_id as CHAR(6)),5,2) != '01' --
current period not january
AND NOT EXISTS (
SELECT *
FROM arc_cost
WHERE period_id = @gen_period_id - 1
AND scenario_id = @scenario_id
)
BEGIN

EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 404 -- Data not loaded for
previous period

GOTO error_return
END



--- delete records to avoid duplicates for the same scenario and period in
case of a re-run
DELETE a
FROM arc_cost a
WHERE a.period_id = @gen_period_id
AND a.scenario_id = @scenario_id


EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'deleted records
where same scenario period already exist in arc_cost',
@i_error = @@error,
@i_rows = @@rowcount

--- Insert records from wrk to archive

INSERT INTO arc_cost
(
customer_id,
account_id,
org_task_id,
occupation_id,
planning_entity_id,
scenario_id,
period_id,
report_currency_id,
local_currency_id,
transact_currency_id,
amount_in_report_currency
)

SELECT
c.customer_id,
a.account_id,
ot.org_task_id,
oc.occupation_id,
pl.planning_entity_id,
sc.scenario_id,
@gen_period_id,
cur.currency_id report_currency_id,
st.local_currency_id,
cur2.currency_id transact_currency_id,
cost_amount + (cost_amount * (@splitter / @month_number)) as
amount_in_report_currency

FROM wrk_cost wrk
INNER JOIN tbl_account a
ON wrk.account_code = a.account_code

INNER JOIN tbl_customer c
ON wrk.customer_code= c.customer_code

INNER JOIN tbl_org_task ot
ON wrk.org_task_code= ot.org_task_code

INNER JOIN tbl_occupation oc
ON wrk.occupation_code = oc.occupation_code

INNER JOIN tbl_planning_entity pl
ON wrk.planning_entity_code = pl.planning_entity_code


INNER JOIN tbl_station st
ON pl.station_id = st.station_id

INNER JOIN tbl_scenario sc
ON wrk.scenario_code = sc.scenario_code


INNER JOIN tbl_currency cur
ON wrk.report_currency_code= cur.currency_code

INNER JOIN tbl_currency cur2
ON wrk.transact_currency_code= cur2.currency_code


EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'inserted all
records into arc_cost from wrk_cost with cumulative_totals',
@i_error = @@error,
@i_rows = @@rowcount


-- reconcile all data from wrk_cost has been loaded into arc_cost
DECLARE @arc_cost_amt NUMERIC(18,4),
@wrk_cost_amt NUMERIC(18,4)

SELECT @wrk_cost_amt = SUM( cost_amount ) + ( SUM( cost_amount ) *
(@splitter / @month_number)) FROM wrk_cost

SELECT @arc_cost_amt = SUM (amount_in_report_currency )
FROM arc_cost
WHERE period_id = @gen_period_id
AND scenario_id = @scenario_id


IF ABS( @arc_cost_amt - @wrk_cost_amt ) > 1
BEGIN
EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 405 -- reconciliation mismatch
between staging and archive

GOTO error_return
END


-- Create submission definition


SELECT @submission_defn_name = 'cost ' + scenario_desc + ' ' +
CAST(@gen_period_id as varchar(8))
FROM tbl_scenario
WHERE scenario_id = @scenario_id

EXECUTE usp_create_submission_defn
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_scenario_id = @scenario_id,
@i_screen_id = @screen_id,
@i_period_id = @gen_period_id,
@i_submission_defn_name = @submission_defn_name,
@o_submission_defn_id = @submission_defn_id OUTPUT


-- Insert individual submissions for all planning entities

INSERT INTO tbl_submission
( submission_datetime,
submission_defn_id,
planning_entity_id)
SELECT distinct getdate() as submission_date,
@submission_defn_id as submission_defn_id,
planning_entity_id
FROM tbl_planning_entity

EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'Insert
individual submissions for all planning entities',
@i_error = @@error,
@i_rows = @@rowcount

IF @error <> 0 GOTO error_return

-- load cost by looking at previous month



DELETE @previous

INSERT INTO @previous
SELECT
c.amount_in_report_currency,
c.account_id,
c.customer_id,
s.submission_id,
c.org_task_id,
c.occupation_id,
c.local_currency_id,
c.report_currency_id,
c.transact_currency_id

FROM arc_cost c
INNER JOIN tbl_submission s
ON c.planning_entity_id = s.planning_entity_id
AND s.submission_defn_id = @submission_defn_id

WHERE c.period_id = @gen_period_id-1
AND c.scenario_id = @scenario_id

EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = '@previous
created with cost cumulative for previous month',
@i_error = @@error,
@i_rows = @@rowcount



DELETE @current

INSERT INTO @current
SELECT
c.amount_in_report_currency,
c.account_id,
c.customer_id,
s.submission_id,
c.org_task_id,
c.occupation_id,
c.local_currency_id,
c.report_currency_id,
c.transact_currency_id

FROM arc_cost c
INNER JOIN tbl_submission s
ON c.planning_entity_id = s.planning_entity_id
AND s.submission_defn_id = @submission_defn_id

WHERE c.period_id = @gen_period_id
AND c.scenario_id = @scenario_id

EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = '@current
created with cost cumulative for current month',
@i_error = @@error,
@i_rows = @@rowcount

IF @error <> 0 GOTO error_return

--- Now load cost data by comparing the two months cumulative totals
INSERT INTO tbl_cost (
amount_in_report_currency,
account_id,
customer_id,
submission_id,
org_task_id,
occupation_id,
local_currency_id,
report_currency_id,
transact_currency_id
)
SELECT
ISNULL (curr.cost_amount,0) - ISNULL(prev.cost_amount,0)
cost_amount,

COALESCE(curr.account_id, prev.account_id)
account_id,
COALESCE(curr.customer_id, prev.customer_id)
customer_id,
COALESCE(curr.submission_id, prev.submission_id)
submission_id,
COALESCE(curr.org_task_id, prev.org_task_id)
org_task_id,
COALESCE(curr.occupation_id, prev.occupation_id)
occupation_id,
COALESCE(curr.local_currency_id, prev.local_currency_id)
local_currency_id,
COALESCE(curr.report_currency_id, prev.report_currency_id)
report_currency_id,
COALESCE(curr.transact_currency_id, prev.transact_currency_id)
transact_currency_id

FROM @current CURR

FULL OUTER JOIN @previous PREV
ON curr.customer_id = prev.customer_id
AND curr.account_id = prev.account_id
AND curr.org_task_id = prev.org_task_id
AND curr.occupation_id = prev.occupation_id
AND curr.report_currency_id = prev.report_currency_id
AND curr.submission_id = prev.submission_id



EXECUTE KQIS_POUGH_MetaDB..usp_check_error_and_log @i_meta_job_hist_id
= @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_text = 'load cost data
with monthly totals calculated',
@i_error = @@error,
@i_rows = @@rowcount

IF @error <> 0 GOTO error_return

-- reconcile by matching cumulative total for that period,scenario from
arc with cumulative total from tbl_cost
DECLARE
@ods_cost_amt NUMERIC(18,4),
@ods_cost_cnt INT


SELECT
@ods_cost_amt = SUM(amount_in_report_currency),
@ods_cost_cnt = COUNT(*)
FROM KQIS_POUGH_ODS..tbl_cost c

INNER JOIN KQIS_POUGH_ODS..tbl_submission s
ON c.submission_id = s.submission_id

INNER JOIN KQIS_POUGH_ODS..tbl_submission_defn sd
ON s.submission_defn_id = sd.submission_defn_id
AND sd.is_active_flag = 1 -- submission is active

WHERE sd.period_id > ROUND(@gen_period_id/100,0) * 100 /** is > start
of that year **/
AND sd.period_id <= @gen_period_id /** and <= that
period **/
AND sd.scenario_id = @scenario_id /** and belongs to
that scenario **/

IF ABS(@ods_cost_amt - @arc_cost_amt) > 1
BEGIN
SELECT @ods_cost_amt as ods_cost_amt, @arc_cost_amt as arc_cost_amt

EXECUTE KQIS_POUGH_MetaDB..usp_raise_custom_error
@i_meta_job_hist_id = @i_meta_job_hist_id,
@i_object_name = @procedure_name,
@i_custom_error = 407 -- reconciliation mismatch
between archive and ODS

GOTO error_return
END

SET @splitter = @splitter + 1

END -- WHILE loop

success_return:
IF @error <> 0 GOTO error_return
RETURN 0

error_return:
IF @@trancount > 0
ROLLBACK TRANSACTION

IF @error IS NULL
SET @error = -1

RETURN @error

END




GO


"Jay" wrote: