--THIS FILE IS NEEDED ONLY FOR THE ONLINE UPDATE!!!

--THIS SCRIPT SHOULD BE EXECUTED, IF YOU ARE PLANNING TO USE THE NEW CHILD-GROUPING FIELDS IN SMAN!
--AFTER EXECUTION, PLEASE RUN 'SETUP->MISCELLANEOUS->SYSTEM MAINTENANCE->RECALCULATE MONTH/YEAR VALUES->MANAGER REPORT (SMAN)'
--IN ORDER TO CALCULATE MONTHLY AND YEARLY VALUES!


CREATE OR REPLACE VIEW V8_TMP_RECALC_SMAN_KIDS_G 
AS 
SELECT 
   SUM(NVL(YRGL_NUMBER*ydet_noofrooms,0)) CHILD 
  ,SUM(DECODE(xcac_domestic,1,NVL(YRGL_NUMBER*ydet_noofrooms,0),0)) domesticchild 
  ,NVL(xcac_report_group,1) xcac_report_group 
  ,ydet_date 
FROM YDET,YRES,YRGL,XCAC 
WHERE 
    yrgl_ydet_id=ydet_id 
AND ydet_yres_id=yres_id 
AND yres_resstatus<>3 
AND xcac_id=yrgl_xcac_id 
GROUP BY ydet_date,NVL(xcac_report_group,1);


UPDATE 
SMAN 
SET 
SMAN_CHILDS_G1_D=NVL((SELECT CHILD FROM V8_TMP_RECALC_SMAN_KIDS_G WHERE V8_TMP_RECALC_SMAN_KIDS_G.XCAC_REPORT_GROUP=1 AND V8_TMP_RECALC_SMAN_KIDS_G.YDET_DATE=SMAN_DATE),0), 
SMAN_CHILDS_G2_D=NVL((SELECT CHILD FROM V8_TMP_RECALC_SMAN_KIDS_G WHERE V8_TMP_RECALC_SMAN_KIDS_G.XCAC_REPORT_GROUP=2 AND V8_TMP_RECALC_SMAN_KIDS_G.YDET_DATE=SMAN_DATE),0), 
SMAN_CHILDS_G3_D=NVL((SELECT CHILD FROM V8_TMP_RECALC_SMAN_KIDS_G WHERE V8_TMP_RECALC_SMAN_KIDS_G.XCAC_REPORT_GROUP=3 AND V8_TMP_RECALC_SMAN_KIDS_G.YDET_DATE=SMAN_DATE),0), 
SMAN_DOMESTICCHILDS_G1_D=NVL((SELECT DOMESTICCHILD FROM V8_TMP_RECALC_SMAN_KIDS_G WHERE V8_TMP_RECALC_SMAN_KIDS_G.XCAC_REPORT_GROUP=1 AND V8_TMP_RECALC_SMAN_KIDS_G.YDET_DATE=SMAN_DATE),0), 
SMAN_DOMESTICCHILDS_G2_D=NVL((SELECT DOMESTICCHILD FROM V8_TMP_RECALC_SMAN_KIDS_G WHERE V8_TMP_RECALC_SMAN_KIDS_G.XCAC_REPORT_GROUP=2 AND V8_TMP_RECALC_SMAN_KIDS_G.YDET_DATE=SMAN_DATE),0), 
SMAN_DOMESTICCHILDS_G3_D=NVL((SELECT DOMESTICCHILD FROM V8_TMP_RECALC_SMAN_KIDS_G WHERE V8_TMP_RECALC_SMAN_KIDS_G.XCAC_REPORT_GROUP=3 AND V8_TMP_RECALC_SMAN_KIDS_G.YDET_DATE=SMAN_DATE),0);


COMMIT;


DROP VIEW V8_TMP_RECALC_SMAN_KIDS_G;
/