Thanks for the reply. this one should be fine using model. but i got other sp which is more complicated. , for example, based on input parameter, will loop insert mutiple time. So want to get a way to execute the macro as general solution. you recommend me one way which is using model. it worked. thanks so much. do you have any other way that can help me avoid creating a table or view through model. because all i want to do is execute those sp and do the insert,
one of the sp that doing mutiple insert based on parameter is as below.:
CREATE OR REPLACE PROCEDURE SEMANTIC.SP_AMMR_INCOMESTATEMENT(PERIOD VARCHAR, PROPERTYPERIODID VARCHAR, CRTDUSER VARCHAR, LUPDUSER VARCHAR)
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS ’
var sql, rs, ThresholdAmt, ThresholdAmt, LedgerType, cnt, Cntmax, PropertyPeriodIdLoop, cnt1, EndOfPeriod;
rs = snowflake.execute( { sqlText:
"CREATE OR REPLACE TEMPORARY TABLE PropertyPeriod (PropertyPeriodKey int identity, PropertyPeriodId int, OperPropCD varchar)"});
if (PERIOD != 0) // go for all properties
{
sql = "INSERT INTO PropertyPeriod (PropertyPeriodId, OperPropCD) " +
"SELECT PropertyPeriodID, OperPropCD FROM GOVERNED.AMMR_PropertyPeriod WHERE Period = ''" + PERIOD + "'' AND Locked = 0 "
rs = snowflake.execute({ sqlText: sql }) ;
}
else
{
sql = "INSERT INTO PropertyPeriod (PropertyPeriodId, OperPropCD) " +
"SELECT PropertyPeriodID, OperPropCD FROM GOVERNED.AMMR_PropertyPeriod WHERE PropertyPeriodID = ''" + PROPERTYPERIODID + "'' AND Locked = 0 "
rs = snowflake.execute({ sqlText: sql }) ;
sql = "SELECT Period FROM GOVERNED.AMMR_PropertyPeriod WHERE PropertyPeriodId = " + PROPERTYPERIODID
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
PERIOD = rs.getColumnValue(1);
}
sql = "SELECT CAST(TO_VARCHAR(LAST_DAY(TO_DATE(TRIM(RIGHT(" + PERIOD + ",2)) || ''/1/'' || TRIM(LEFT(" + PERIOD + ", 4)))), ''YYYYMMDD'') AS INT)"
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
EndOfPeriod = rs.getColumnValue(1);
sql = "SELECT LedgerType FROM GOVERNED.AMMR_ForecastingSchedule WHERE PeriodMonth = RIGHT(''" + PERIOD + "'',2) "
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
LedgerType = rs.getColumnValue(1);
cnt = 1;
sql = "SELECT Max(PropertyPeriodKey) FROM PropertyPeriod"
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
cntmax = rs.getColumnValue(1);
sql = "CREATE OR REPLACE TEMPORARY TABLE FactAccountBalances (Period_Date_Id, PropertyPeriodId, Business_Unit, Account_Id, Object_Account, AcctCatCd22, AcctCatCd22Desc, AssetTypeCD, AssetTypeDesc, BUCatCD12, BUCatCD12Desc, Current_Mnth_Actuals, Current_Mnth_Budget, Curr_Mnth_Forecast_1F, Curr_Mnth_Forecast_2F, Curr_Mnth_Forecast_3F, Curr_Mnth_Forecast_4F, YTD_Actuals, YTD_Budget, FCST) AS " +
"SELECT Period_Date_Id, P.PropertyPeriodId, " +
"FACT_ACCOUNT_BALANCES.Business_Unit, " +
"FACT_ACCOUNT_BALANCES.Account_Id, " +
"FACT_ACCOUNT_BALANCES.Object_Account, " +
"DimHHCFinAccount.Acct_Cat_Cd22, DimHHCFinAccount.Acct_Cat_Cd22_Desc, NULL AssetTypeCD, NULL AssetTypeDesc, BU_Cat_CD12, BU_Cat_CD12_Desc, " +
"SUM(CASE WHEN Ledger_Type = ''AA'' THEN MTD_Balance ELSE 0 END) Current_Mnth_Actuals, " +
"SUM(CASE WHEN Ledger_Type = ''BA'' THEN MTD_Balance ELSE 0 END) Current_Mnth_Budget, " +
"SUM(CASE WHEN Ledger_Type = ''1F'' THEN MTD_Balance ELSE 0 END) * -1 AS Curr_Mnth_Forecast_1F, " +
"SUM(CASE WHEN Ledger_Type = ''2F'' THEN MTD_Balance ELSE 0 END) * -1 AS Curr_Mnth_Forecast_2F, " +
"SUM(CASE WHEN Ledger_Type = ''3F'' THEN MTD_Balance ELSE 0 END) * -1 AS Curr_Mnth_Forecast_3F, " +
"SUM(CASE WHEN Ledger_Type = ''4F'' THEN MTD_Balance ELSE 0 END) * -1 AS Curr_Mnth_Forecast_4F, " +
"SUM(CASE WHEN Ledger_Type = ''AA'' THEN YTD_Balance ELSE 0 END) YTD_Actuals, " +
"SUM(CASE WHEN Ledger_Type = ''BA'' THEN YTD_Balance ELSE 0 END) YTD_Budget, " +
"(SUM(CASE WHEN Ledger_Type = ''AA'' THEN YTD_Balance ELSE 0 END) \\+ " +
"CASE WHEN ''" + LedgerType + "'' = ''1F'' THEN (SUM(IFNULL(CASE WHEN Ledger_Type = ''1F'' THEN Original_Begin_Budget ELSE 0 END,0)) - SUM(IFNULL(CASE WHEN Ledger_Type = ''1F'' THEN YTD_Balance ELSE 0 END,0))) " +
"WHEN ''" + LedgerType + "'' = ''2F'' THEN (SUM(IFNULL(CASE WHEN Ledger_Type = ''2F'' THEN Original_Begin_Budget ELSE 0 END,0)) - SUM(IFNULL(CASE WHEN Ledger_Type = ''2F'' THEN YTD_Balance ELSE 0 END,0))) " +
"WHEN ''" + LedgerType + "'' = ''3F'' THEN (SUM(IFNULL(CASE WHEN Ledger_Type = ''3F'' THEN Original_Begin_Budget ELSE 0 END,0)) - SUM(IFNULL(CASE WHEN Ledger_Type = ''3F'' THEN YTD_Balance ELSE 0 END,0))) " +
"WHEN ''" + LedgerType + "'' = ''4F'' THEN (SUM(IFNULL(CASE WHEN Ledger_Type = ''4F'' THEN Original_Begin_Budget ELSE 0 END,0)) - SUM(IFNULL(CASE WHEN Ledger_Type = ''4F'' THEN YTD_Balance ELSE 0 END,0))) END ) FCST " +
"FROM SEMANTIC.FACT_ACCOUNT_BALANCES FACT_ACCOUNT_BALANCES " +
"INNER JOIN SEMANTIC.DIM_ACCOUNT DimHHCFinAccount ON DimHHCFinAccount.Account_ID = FACT_ACCOUNT_BALANCES.Account_Id " +
"INNER JOIN SEMANTIC.DIM_BUSINESS_UNIT DimHHCFinBusinessUnit on DimHHCFinBusinessUnit.Business_Unit = FACT_ACCOUNT_BALANCES.Business_Unit " +
"INNER JOIN (SELECT DISTINCT PropertyMap.JDE_Company Company, JDE_OperPropCD Parent_OperPropCd, PropertyPeriod.PropertyPeriodId " +
"FROM PropertyPeriod " +
"INNER JOIN GOVERNED.AMMR_PropertyMap PropertyMap on PropertyPeriod.OperPropCD = IFNULL(PropertyMap.Parent_OperPropCd, PropertyMap.JDE_OperPropCd)) P " +
"ON P.Company = DimHHCFinBusinessUnit.Company " +
"WHERE DimHHCFinBusinessUnit.Bu_Cat_Cd27 = ''OPR'' " +
"GROUP BY Period_Date_Id, P.PropertyPeriodId, FACT_ACCOUNT_BALANCES.Business_Unit, FACT_ACCOUNT_BALANCES.Account_Id, FACT_ACCOUNT_BALANCES.Object_Account, DimHHCFinAccount.Acct_Cat_Cd22, DimHHCFinAccount.Acct_Cat_Cd22_Desc, BU_Cat_CD12, BU_Cat_CD12_Desc "
rs = snowflake.execute({ sqlText: sql }) ;
sql = "TRUNCATE TABLE GOVERNED.STG_AMMR_INCOMESTATEMENT"
rs = snowflake.execute({ sqlText: sql }) ;
while (cnt <= cntmax)
{
sql = "SELECT PropertyPeriodId FROM PropertyPeriod where PropertyPeriodKey = " + cnt
rs = snowflake.execute({ sqlText: sql }) ;
rs.next();
PropertyPeriodIdLoop = rs.getColumnValue(1);
//--------Now bring in the updated numbers
sql = "INSERT INTO GOVERNED.STG_AMMR_INCOMESTATEMENT (PropertyPeriodId, Level1CD, Level1Desc, Level2CD, Level2Desc, Level3CD, Level3Desc, AcctCatCd22, AcctCatCD22Desc, AssetTypeCD, AssetTypeDesc, BUCatCD12, BUCatCD12Desc, CMActual , CMBudget , YTDActual , YTDBudget , FCST, FCSTBudget ) " +
"WITH ETL (PropertyPeriodId, AcctCatCd22, AcctCatCD22Desc, AssetTypeCD, AssetTypeDesc, BUCatCD12, BUCatCD12Desc, CMActual , CMBudget , YTDActual , YTDBudget , FCST, FCSTBudget ) AS " +
"(SELECT F.PropertyPeriodId, F.AcctCatCd22, F.AcctCatCD22Desc, F.AssetTypeCD, F.AssetTypeDesc, F.BUCatCD12, F.BUCatCD12Desc, " +
"SUM(F.Current_Mnth_Actuals), " +
"SUM(F.Current_Mnth_Budget), " +
"SUM(F.YTD_Actuals), " +
"SUM(F.YTD_Budget), " +
"SUM(F.FCST), NULL " +
"FROM FactAccountBalances F " +
"WHERE Period_Date_Id = " + EndOfPeriod + " AND PropertyPeriodId = " + PropertyPeriodIdLoop + " " +
"GROUP BY F.PropertyPeriodId, F.AcctCatCd22, F.AcctCatCD22Desc, F.AssetTypeCD, F.AssetTypeDesc, F.BUCatCD12, F.BUCatCD12Desc " +
"), ETLAnnualBudget (PropertyPeriodId, AcctCatCd22, AssetTypeCD, BUCatCD12, FCSTBudget) AS " +
"(SELECT F.PropertyPeriodId, F.AcctCatCd22, F.AssetTypeCD, F.BUCatCD12, " +
"SUM(F.Current_Mnth_Budget) " +
"FROM FactAccountBalances F " +
"WHERE LEFT(Period_Date_Id,4) = LEFT(" + PERIOD + ",4) AND PropertyPeriodId = " + PropertyPeriodIdLoop + " " +
"GROUP BY F.PropertyPeriodId, F.AcctCatCd22, F.AssetTypeCD, F.BUCatCD12) " +
"SELECT " + PropertyPeriodIdLoop + ", LUP.Level1CD, LUP.Level1Desc, LUP.Level2CD, LUP.Level2Desc, LUP.Level3CD, LUP.Level3Desc, " +
"LUP.AcctCatCd22, LUP.AcctCatCD22Desc, ETL.AssetTypeCD, ETL.AssetTypeDesc, ETL.BUCatCD12, ETL.BUCatCD12Desc, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * ETL.CMActual ELSE ETL.CMActual END, 0)) CMActual, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * ETL.CMBudget ELSE ETL.CMBudget END, 0)) CMBudget, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * ETL.YTDActual ELSE ETL.YTDActual END, 0)) YTDActual, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * ETL.YTDBudget ELSE ETL.YTDBudget END, 0)) YTDBudget, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * FCST ELSE FCST END, 0)) FCST, " +
"SUM(IFNULL(CASE WHEN LUP.Level1CD = ''100'' THEN -1 * ETLAnnualBudget.FCSTBudget ELSE ETLAnnualBudget.FCSTBudget END, 0)) FCSTBudget " +
"FROM ETL " +
"INNER JOIN ETLAnnualBudget ON ETLAnnualBudget.PropertyPeriodId = ETL.PropertyPeriodId AND ETLAnnualBudget.AcctCatCd22 = ETL.AcctCatCd22 " +
"AND IFNULL(ETLAnnualBudget.AssetTypeCD, '''') = IFNULL(ETL.AssetTypeCD, '''') AND IFNULL(ETLAnnualBudget.BUCatCD12, '''') = IFNULL(ETL.BUCatCD12, '''') " +
"RIGHT OUTER JOIN (SELECT DISTINCT LEVEL1CD,LEVEL1DESC,LEVEL2CD,LEVEL2DESC,LEVEL3CD,LEVEL3DESC,ACCTCATCD22,ACCTCATCD22DESC " +
"FROM SEMANTIC.OPIS_REPORT_STRUCTURE ) LUP " +
"ON ETL.AcctCatCd22=LUP.AcctCatCd22 " +
"GROUP BY LUP.Level1CD, LUP.Level1Desc, LUP.Level2CD, LUP.Level2Desc, LUP.Level3CD, LUP.Level3Desc, " +
"LUP.AcctCatCd22, LUP.AcctCatCD22Desc, ETL.AssetTypeCD, ETL.AssetTypeDesc, ETL.BUCatCD12, ETL.BUCatCD12Desc "
rs = snowflake.execute({ sqlText: sql }) ;
cnt = cnt + 1
}
return “Done”;
';