 |
 |
Archives of the TeradataForum
Message Posted: Wed, 02 Apr 2014 @ 13:17:06 GMT
| Subj: | | Re: Arabic data and session character set |
| |
| From: | | Anomy Anom |
<-- Anonymously Posted: Wednesday, April 02, 2014 05:48 -->
Dear Marek,
Please find the procedure attached and the call statement
CALL PROD_STG_CORE.repcomp(
'PROD_STG_CORE',
'53',
'29-03-2014',
0,
53,
'LBP',
'PROD_TAB',
1000
);
Thank you
REPCOMP.SQL
-----------
REPLACE PROCEDURE PROD_STG_CORE.REPBR6_COMPILE(
-- Add the parameters for the stored procedure here
INOUT VBANK VARCHAR(30),
INOUT VBANK_N VARCHAR(3),
INOUT Vdate VARCHAR(10),
INOUT VCONSOL SMALLINT,
INOUT VBRANCH_N INTEGER,
INOUT VCurrency VARCHAR(3),
INOUT SPSERVER VARCHAR(70),
INOUT DIVIDEDBY DECIMAL(18,0)
) DYNAMIC RESULT SETS 1
SQL SECURITY INVOKER
BEGIN
DECLARE ACC_TBL VARCHAR(50);
DECLARE CURR_TBL VARCHAR(50);
DECLARE BAL_TBL VARCHAR(50);
DECLARE DEA_TBL VARCHAR(50);
DECLARE SERVPATH VARCHAR(250);
DECLARE TBLDB VARCHAR(100);
DECLARE CBK_TBL VARCHAR(200);
DECLARE ADD_TBL VARCHAR(100);
DECLARE PL_TBL VARCHAR(100);
DECLARE BANK_TBL VARCHAR(100);
DECLARE BRANCH_TBL VARCHAR(100);
DECLARE INT_TBL VARCHAR(100);
DECLARE VCASTDATE VARCHAR(8);
DECLARE ErrVal
VARCHAR(100);
DECLARE SITUATION VARCHAR(1);
DECLARE V_USER VARCHAR(20);
DECLARE CBKCURR_TBL VARCHAR(70);
DECLARE VAR_AMOUNT VARCHAR(500);
DECLARE DEP_TBL VARCHAR(70);
DECLARE LOAN_TBL VARCHAR(70);
DECLARE INDIVIDUAL_TBL VARCHAR(100);
DECLARE PARAM_REP VARCHAR(100);
DECLARE V_BRANCH VARCHAR(4);
DECLARE VHDPF VARCHAR(200);
DECLARE BRANCH_CL_CLIB VARCHAR(100);
DECLARE BRANCH_LIM_BAH VARCHAR(100);
DECLARE DIVIDEDBY_DEV VARCHAR(10);
DECLARE CDPRG VARCHAR(10);
DECLARE VAR_BRANCH_LIM_BAH VARCHAR(100);
DECLARE VAR_BRANCH_CL_CLIB VARCHAR(100);
DECLARE VAR_BRANCH_N VARCHAR(10);
DECLARE V_SOURCE_CODE VARCHAR(100);
DECLARE V_HIST VARCHAR(5);
DECLARE DATAFILE_TBL VARCHAR(70);
DECLARE TOT_UPDATE INTEGER;
DECLARE AGR_FOREIGN_RATE_LEB VARCHAR(1000);
DECLARE LED_FOREIGN_RATE_LEB VARCHAR(1000);
DECLARE RATE_LBP DECIMAL(13,7);
DECLARE RATE_LBP_IQD DECIMAL(13,7);
DECLARE RATE_LBP_BHD DECIMAL(13,7);
DECLARE RATE_LBP_SEN DECIMAL(13,7);
DECLARE RATE_LBP_LIM DECIMAL(13,7);
DECLARE RATE_LBP_5 DECIMAL(13,7);
DECLARE RATE_LBP_6 DECIMAL(13,7);
DECLARE RATE_LBP_7 DECIMAL(13,7);
SET ErrVal = 0;
SET CDPRG = 'BR6';
CALL PROD_STG_CORE.COMMON_PARAM(VBRANCH_N ,VDATE,:V_SOURCE_CODE,:V_HIST,:VCASTDATE);
CALL PROD_STG_CORE.SITUATION(VDATE,CDPRG, :SITUATION); CALL
PROD_STG_CORE.FOREIGN_RATE(VCURRENCY
,VCASTDATE,:AGR_FOREIGN_RATE_LEB,LED_FOREIGN_RATE_LEB,:RATE_LBP);
SET ACC_TBL = SPSERVER||'.'||'CORE_AGREEMENT'||V_HIST;
SET CURR_TBL = SPSERVER||'.'||'CORE_CURRENCY_LIST';
SET CBK_TBL = VBANK||'.'||'CBKPARAMTBL' ;
SET BANK_TBL = VBANK||'.'||'BANKS_TBL';
SET BRANCH_TBL = VBANK||'.'||'BRANCHES_BANKS_BDL1';
SET INT_TBL = SPSERVER||'.'||'CORE_LEDGER'||V_HIST;
SET CBKCURR_TBL = VBANK||'.'||'CBK_CURRTBL';
SET INDIVIDUAL_TBL = SPSERVER||'.'||'CORE_INDIVIDUAL';--V_HIST
SET PL_TBL = SPSERVER||'.'||'core_pl_statis'||V_HIST;
SET PARAM_REP = VBANK||'.'||'PARAM_REPORTS';
SET DATAFILE_TBL = VBANK||'.'||'DATAFILE';
SET V_USER =USER;
SET VAR_BRANCH_N = CAST(VBRANCH_N AS VARCHAR(10));
CALL PROD_STG_CORE.PARAM_REP(VBANK,CDPRG,VBRANCH_N, :BRANCH_CL_CLIB,:BRANCH_LIM_BAH,
:DIVIDEDBY_DEV);
SET VAR_BRANCH_CL_CLIB =BRANCH_CL_CLIB ;
SET VAR_BRANCH_LIM_BAH =BRANCH_LIM_BAH ;
IF SUBSTR(VDATE,4,2)='01' THEN
SET VHDPF ='H.PL_January';
ELSE IF SUBSTR(VDATE,4,2) ='02' THEN
SET VHDPF ='H.PL_January+H.PL_February'; ELSE IF SUBSTR(VDATE,4,2) ='03' THEN
SET VHDPF ='H.PL_January+H.PL_February+H.PL_March';
ELSE IF SUBSTR(VDATE,4,2) ='04' THEN
SET VHDPF ='H.PL_January+H.PL_February+H.PL_March+H.PL_APRIL';
ELSE IF SUBSTR(VDATE,4,2)='05' THEN
SET VHDPF ='H.PL_January+H.PL_February+H.PL_March+H.PL_APRIL+H.PL_MAY';
ELSE IF SUBSTR(VDATE,4,2)='06' THEN
SET VHDPF ='H.PL_January+H.PL_February+H.PL_March+H.PL_APRIL+H.PL_MAY+H.PL_JUNE';
ELSE IF SUBSTR(VDATE,4,2)='07' THEN
SET VHDPF
='H.PL_January+H.PL_February+H.PL_March+H.PL_APRIL+H.PL_MAY+H.PL_JUNE+H.PL_JULY';
ELSE IF SUBSTR(VDATE,4,2)='08' THEN
SET VHDPF
='H.PL_January+H.PL_February+H.PL_March+H.PL_APRIL+H.PL_MAY+H.PL_JUNE+H.PL_JULY+H.PL_Augu
st';
ELSE IF SUBSTR(VDATE,4,2) ='09' THEN
SET VHDPF
='H.PL_January+H.PL_February+H.PL_March+H.PL_APRIL+H.PL_MAY+H.PL_JUNE+H.PL_JULY+H.PL_Augu
st+H.PL_September';
ELSE IF SUBSTR(VDATE,4,2)='10' THEN
SET VHDPF
='H.PL_January+H.PL_February+H.PL_March+H.PL_APRIL+H.PL_MAY+H.PL_JUNE+H.PL_JULY+H.PL_Augu
st+H.PL_September+H.PL_October';
ELSE IF SUBSTR(VDATE,4,2)='11' THEN
SET VHDPF
='H.PL_January+H.PL_February+H.PL_March+H.PL_APRIL+H.PL_MAY+H.PL_JUNE+H.PL_JULY+H.PL_Augu
st+H.PL_September+H.PL_October+H.PL_November';
ELSE IF SUBSTR(VDATE,4,2)='12' THEN
SET VHDPF
='H.PL_January+H.PL_February+H.PL_March+H.PL_APRIL+H.PL_MAY+H.PL_JUNE+H.PL_JULY+H.PL_Augu
st+H.PL_September+H.PL_October+H.PL_November+H.PL_December';
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
IF EXISTS(SELECT 1 FROM dbc.tables WHERE databasename = :V_USER AND tablename =
'BBR6TEMP' ) THEN
CALL DBC.SysExecSQL('DROP TABLE '||:V_USER||'.BBR6TEMP'); END IF;
CALL DBC.SYSEXECSQL('
CREATE GLOBAL TEMPORARY TABLE '||:V_USER||'.BBR6TEMP(
CircuNumb_T VARCHAR(3) NOT NULL,
T_CircPage_T VARCHAR(1) NOT NULL,
SituationType VARCHAR(1) NOT NULL,
T_DATE VARCHAR(19) NULL,
T_BankNumb_N VARCHAR(3) NULL,
T_BranchNumb_N VARCHAR(255) NULL,
T_ItemNumb_N VARCHAR(18) NULL,
T_CountNumb_N VARCHAR(1) NULL,
T_Value3_N DECIMAL(18,0) NULL,
T_Value2_N DECIMAL(18,0) NULL,
T_Value1_N DECIMAL(18,0) NULL
)
PRIMARY INDEX (
CircuNumb_T,T_CircPage_T,SituationType,T_DATE,T_BankNumb_N,T_BranchNumb_N,T_ItemNumb_N)
ON COMMIT PRESERVE ROWS;');
CALL DBC.SYSEXECSQL('INSERT INTO '||:V_USER||'.BBR6TEMP SELECT
''BBR'' as CircuNumb_T
, ''6'' as T_CircPage_T,
'''||:SITUATION||''' as SituationType
, '''||:VDATE||''' AS T_DATE,
'''||:VBANK_N||''' as T_BankNumb_N
, CASE WHEN Y.HEAD_OFFICE = ''10'' THEN ''33'' ELSE
Y.BRANCH_BDL END AS T_BranchNumb_N,
P.CODE AS T_ItemNumb_N
, ''1'' AS T_CountNumb_N,
CAST(SUM(CAST( CASE C.SPOT_RATE_RECIP
WHEN
''Y'' THEN (( COALESCE( '||:VHDPF||',0) + (LEDGER_BALANCE+SHADOW_ADJUST)) * CAST (C.RATE
AS DECIMAL(18,3)) )
ELSE
(( COALESCE( '||:VHDPF||',0) + (LEDGER_BALANCE+SHADOW_ADJUST))/ CAST (C.RATE AS
DECIMAL(18,3)) )
END AS
DECIMAL (18,3)) * CAST( ( '||:LED_FOREIGN_RATE_LEB||') AS DECIMAL(18,3)) ) AS DECIMAL(18
,0) ) /'||:DIVIDEDBY||' AS T_Value3_N,
CASE WHEN '||:VBRANCH_N||' IN '||:VAR_BRANCH_LIM_BAH||'
THEN CAST(SUM(CAST( CASE C.SPOT_RATE_RECIP WHEN ''Y'' THEN ((
COALESCE( '||:VHDPF||',0) + (LEDGER_BALANCE+SHADOW_ADJUST)) * CAST (C.RATE AS
DECIMAL(18,3)) )
ELSE (( COALESCE( '||:VHDPF||',0) +
(LEDGER_BALANCE+SHADOW_ADJUST))/ CAST (C.RATE AS DECIMAL(18,3)) )
END AS DECIMAL(18,3)) * CAST( ( '||:LED_FOREIGN_RATE_LEB||') AS
DECIMAL(18,3)) ) AS DECIMAL(18 ,0) )
ELSE 0
END /'||:DIVIDEDBY||' AS T_Value2_N,
CASE WHEN '||:VBRANCH_N||' IN '||:VAR_BRANCH_CL_CLIB||'
THEN CAST(SUM(CAST( CASE C.SPOT_RATE_RECIP WHEN ''Y'' THEN ((
COALESCE( '||:VHDPF||',0) + (LEDGER_BALANCE+SHADOW_ADJUST)) * CAST (C.RATE AS
DECIMAL(18,3)) )
ELSE (( COALESCE( '||:VHDPF||',0) + (LEDGER_BALANCE+SHADOW_ADJUST))/ CAST
(C.RATE AS DECIMAL(18,3)) )
END AS DECIMAL(18,3)) * CAST( ( '||:LED_FOREIGN_RATE_LEB||') AS DECIMAL(18,3)) )
AS DECIMAL(18 ,0) )
ELSE 0
END /'||:DIVIDEDBY||' as T_Value1_N
FROM '||:INT_TBL ||' AS A INNER JOIN
'||:BRANCH_TBL||' AS Y ON A.Account_MODIFIER_NUM =
Y.BRANCH_CODE INNER JOIN
'||:CURR_TBL ||' AS C ON A.Today_Date
= C.Today_Date
AND
A.CURRENCY_CD = C.ISO_CODE_NUMBER AND
A.SOURCE_CD
= C.SOURCE_CD
LEFT OUTER JOIN
'||:PL_TBL ||' AS H ON A.Source_cd
=H.Source_cd AND
A.ACCOUNT_NUM = H.Account_num
AND
A.Today_Date = H.Today_Date
CROSS JOIN
'||:CBK_TBL||' AS P
WHERE
CAST(A.Today_Date AS VARCHAR(8)) = '''||:VCASTDATE ||'''
AND
A.SOURCE_CD
IN '||:V_SOURCE_CODE||'
AND
(P.CodeProg
= '''||:CDPRG||''') AND
SUBSTR(A.ACCOUNT_NUM,5,6) BETWEEN
COALESCE(P.BASICINF,''000000'') AND
COALESCE(P.BASICSUP,''000000'') AND
Y.BANK_CODE
= '||:VBRANCH_N||'
GROUP BY P.CodeProg, P.Code,
CASE WHEN Y.HEAD_OFFICE = ''10'' THEN ''33'' ELSE
Y.BRANCH_BDL END ');
CALL DBC.SysExecSQL ('UPDATE '||:V_USER||'.BBR6TEMP SET T_Value1_N= (T_Value1_N)*(-1),
T_Value3_N= (T_Value3_N)*(-1)
WHERE SUBSTR(T_ItemNumb_N,1,2) IN (''12'',''13'',''32'',''43'',''44'',''62'')');
BEGIN
DECLARE CUR_PLM_DETAILS CURSOR WITH RETURN only FOR
select
CircuNumb_T ,
T_CircPage_T ,
SituationType ,
CAST(:VDATE AS DATE FORMAT 'DD/MM/YYYY') AS T_DATE, T_BankNumb_N ,
CAST(T_BranchNumb_N AS INTEGER) ,
T_ItemNumb_N ,
T_CountNumb_N ,
T_Value3_N ,
T_Value2_N,
T_Value1_N
from BBR6TEMP order by CAST(T_BranchNumb_N AS INTEGER) ,T_ItemNumb_N ;
OPEN CUR_PLM_DETAILS;
end;
ENd;
| |
