I have created a stored procedure that need to performs a select insert. when i am executing stored Procedure it's not inserting any value.
create or replace PROCEDURE SP_REPORT_AGG_TEST
(
KPI_NAME NVARCHAR2,
Month NVARCHAR2,
Year NUMBER,
BU NVARCHAR2,
FMT NVARCHAR2,
NUM_ACCT NVARCHAR2,
DEN_ACCT NVARCHAR2
)
AS
BEGIN
INSERT INTO METRIC_AGGREGATE
SELECT A.BU_NAME,A.KPI_NAME,A.YEAR,A.MONTHNAME,(A.NUM/B.DEN)*100 AS CY_YTD_ACTUAL FROM
(SELECT BU.BU_NAME,KPI.KPI_NAME,CAL.YEAR,CAl.MONTHNAME,SUM(YTD_AMT) AS NUM from BBP_F_KPI_CALC KPI_CALC
LEFT OUTER JOIN BBP_D_BU BU
ON KPI_CALC.BU_ID=BU.BU_ID
LEFT OUTER JOIN BBP_D_GLOBAL_REGION GR
ON KPI_CALC.GLOBAL_REGION_ID=GR.GLOBAL_REGION_ID
LEFT OUTER JOIN BBP_D_MARKET_CHANNEL MC
ON KPI_CALC.MARKET_CHANNEL_ID =MC.MARKET_CHANNEL_ID
LEFT OUTER JOIN BBP_D_PRODUCT_LINE PL
ON KPI_CALC.PRODUCT_LINE_ID =PL.PRODUCT_LINE_ID
LEFT OUTER JOIN BBP_D_STATE ST
ON KPI_CALC.STATE_ID =ST.STATE_ID
LEFT OUTER JOIN BBP_D_CUSTOMER CUST
ON KPI_CALC.CUST_ID =CUST.CUST_ID
LEFT OUTER JOIN BBP_D_CALENDER CAL
ON KPI_CALC.CALENDER_ID =CAL.CALENDER_ID
LEFT OUTER JOIN BBP_D_CURRENCY CUR
ON KPI_CALC.CURRENCY_ID =CUR.CURRENCY_ID
LEFT OUTER JOIN BBP_D_ACCOUNT ACT
ON KPI_CALC.ACCOUNT_ID =ACT.ACCOUNT_ID
LEFT OUTER JOIN BBP_D_KPI KPI
ON KPI_CALC.KPI_ID =KPI.KPI_ID
LEFT OUTER JOIN BBP_D_LOCATION LOC
ON KPI_CALC.LOCATION_ID=LOC.LOCATION_ID
LEFT OUTER JOIN BBP_D_ENTITY ENT
ON KPI_CALC.ENTITY_ID=ENT.ENTITY_ID
WHERE
--ACT.ACCOUNT_NAME ='LMECAL'
--AND
KPI.KPI_NAME = KPI_NAME AND
--AND
CAl.MONTHNAME = Month
AND
CAL.YEAR=Year AND
BU.BU_NAME = BU
AND KPI_CALC.FINANCIAL_MEASURE_TYPE= FMT
--AND ACT.ACCOUNT_NAME IN ('TPSALES')
--AND ENT.GROUP_NAME='A'
--AND GR.GLOBAL_REGION_NAME='Asia'
AND ACT.ACCOUNT_NAME = NUM_ACCT
--AND MC.MARKET_CHANNEL_NAME='Aftermarket'
--AND GR_NAME='Latin America (AM)'
GROUP BY
BU.BU_NAME,KPI.KPI_NAME,CAL.YEAR,CAl.MONTHNAME) a ,
(SELECT BU.BU_NAME,KPI.KPI_NAME,CAL.YEAR,CAl.MONTHNAME,SUM(YTD_AMT) AS DEN from BBP_F_KPI_CALC KPI_CALC
LEFT OUTER JOIN BBP_D_BU BU
ON KPI_CALC.BU_ID=BU.BU_ID
LEFT OUTER JOIN BBP_D_GLOBAL_REGION GR
ON KPI_CALC.GLOBAL_REGION_ID=GR.GLOBAL_REGION_ID
LEFT OUTER JOIN BBP_D_MARKET_CHANNEL MC
ON KPI_CALC.MARKET_CHANNEL_ID =MC.MARKET_CHANNEL_ID
LEFT OUTER JOIN BBP_D_PRODUCT_LINE PL
ON KPI_CALC.PRODUCT_LINE_ID =PL.PRODUCT_LINE_ID
LEFT OUTER JOIN BBP_D_STATE ST
ON KPI_CALC.STATE_ID =ST.STATE_ID
LEFT OUTER JOIN BBP_D_CUSTOMER CUST
ON KPI_CALC.CUST_ID =CUST.CUST_ID
LEFT OUTER JOIN BBP_D_CALENDER CAL
ON KPI_CALC.CALENDER_ID =CAL.CALENDER_ID
LEFT OUTER JOIN BBP_D_CURRENCY CUR
ON KPI_CALC.CURRENCY_ID =CUR.CURRENCY_ID
LEFT OUTER JOIN BBP_D_ACCOUNT ACT
ON KPI_CALC.ACCOUNT_ID =ACT.ACCOUNT_ID
LEFT OUTER JOIN BBP_D_KPI KPI
ON KPI_CALC.KPI_ID =KPI.KPI_ID
LEFT OUTER JOIN BBP_D_LOCATION LOC
ON KPI_CALC.LOCATION_ID=LOC.LOCATION_ID
LEFT OUTER JOIN BBP_D_ENTITY ENT
ON KPI_CALC.ENTITY_ID=ENT.ENTITY_ID
WHERE
--ACT.ACCOUNT_NAME ='LMECAL'
--AND
KPI.KPI_NAME =KPI_NAME AND
--AND
CAl.MONTHNAME =Month
AND
CAL.YEAR=Year AND
BU.BU_NAME ='PS'
AND KPI_CALC.FINANCIAL_MEASURE_TYPE= FMT
--AND ACT.ACCOUNT_NAME IN ('TPSALES')
--AND ENT.GROUP_NAME='A'
--AND GR.GLOBAL_REGION_NAME='Asia'
AND ACT.ACCOUNT_NAME = DEN_ACCT
--AND MC.MARKET_CHANNEL_NAME='Aftermarket'
--AND GR_NAME='Latin America (AM)'
GROUP BY
BU.BU_NAME,KPI.KPI_NAME,CAL.YEAR,CAl.MONTHNAME) b
WHERE A.BU_NAME=B.BU_NAME;
COMMIT;
END;
DECLARE
KPI_NAME NVARCHAR2(200);
MONTH NVARCHAR2(200);
YEAR NUMBER;
BU NVARCHAR2(200);
FMT NVARCHAR2(200);
NUM_ACCT NVARCHAR2(200);
DEN_ACCT NVARCHAR2(200);
BEGIN
KPI_NAME := 'Annualized New Customers %';
MONTH := 'Sep';
YEAR := '2016';
BU := 'BE';
FMT := 'Actual';
NUM_ACCT := 'BE_CUST_NEW';
DEN_ACCT := 'BE_CUST_TOTAL';
SP_REPORT_AGG_TEST( KPI_NAME => KPI_NAME, MONTH => MONTH, YEAR => YEAR, BU => BU, FMT => FMT, NUM_ACCT => NUM_ACCT, DEN_ACCT => DEN_ACCT );
--rollback;
END;