PROCEDURE process_statistics IS TYPE v_rec IS RECORD ( col_name VARCHAR2(500), col_type VARCHAR2(50), col_value VARCHAR2(500), old_col_value VARCHAR2(500), Ltotal_Frm NUMBER, Ltotal_To NUMBER, Jtotal_Frm NUMBER, Jtotal_To NUMBER, Btotal_Frm NUMBER, Btotal_To NUMBER, va_name VARCHAR2(50) ); TYPE col_table IS TABLE OF v_rec; v_col_table COL_TABLE := COL_TABLE(); v_trg_item VARCHAR2(50) := :SYSTEM.TRIGGER_ITEM; v_trg_block VARCHAR2(50) := :SYSTEM.TRIGGER_BLOCK; v_item_count NUMBER := SUBSTR(v_trg_item,INSTR(v_trg_item,'.')+2); v_block_count NUMBER := SUBSTR(v_trg_block,2); v_select VARCHAR2(1000) := NULL; v_group VARCHAR2(1000) := NULL; v_rg_name VARCHAR2(40) := 'Stats'; v_rg_id RecordGroup; v_row_count NUMBER := 0; v_errcode NUMBER := 0; v_sql VARCHAR2(2000) := NULL; v_item_val VARCHAR2(100) := NULL; v_result_block VARCHAR2(10) := NULL; v_spaces VARCHAR2(50) := ' '; v_qte VARCHAR2(1) := chr(39); v_cur_val VARCHAR2(50) := NULL; v_col_name VARCHAR2(100) := NULL; v_old_val VARCHAR2(50) := NULL; v_col_type VARCHAR2(100) := NULL; v_tot_heading VARCHAR2(100) := NULL; v_bf_from NUMBER := 0; v_join_from NUMBER := 0; v_left_from NUMBER := 0; v_bf_to NUMBER := 0; v_join_to NUMBER := 0; v_left_to NUMBER := 0; v_name VARCHAR2(500) := NULL; v_cur_rec NUMBER := 0; qte VARCHAR2(1) := CHR(39); qtes VARCHAR2(1) := CHR(34); v_def_division VARCHAR2(20); v_month1 VARCHAR2(10) := 0; v_month2 VARCHAR2(10) := 0; v_year number; v_month number; gc_id GroupColumn; BEGIN FOR blk_loop IN 1..v_block_count LOOP -- BLOCK LOOP go_block('G'||blk_loop); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','G'||blk_loop,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','-------------------------------------------------------------','a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Before Loop','a'); FOR itm_loop IN 1..5 LOOP -- Item LOOP v_item_val := NAME_IN('G'||blk_loop||'.E'||itm_loop); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Item Value-'||v_item_val,'a'); v_def_division := mtl_params.get_def_division; debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Before If Condition of Extend','a'); IF v_item_val IS NOT NULL THEN v_col_table.extend; v_select := v_select||v_item_val||','; v_col_table(v_col_table.last).col_name := v_item_val; SELECT DECODE(v_item_val,'STA_BRANCH_CODE','Branch ' ,'STA_EMPLOYER_CODE','Employer ' ,'STA_WORKPLACE_CODE','Workplace ' ,'STA_CATEGORY_CODE','Category ' ,'STA_JOB_CODE','Job ' ,'STA_RATE','Rate ' ,'STA_PAYMENT_CODE','Payment ' ,'STA_FREQUENCY','Frequency ' ,'STA_SEX','Sex ' ,'STA_STATUS_CODE','Status ' ,'STA_PAID_UP','Paid ' ,'STA_AGE_GROUP','Age ' ,'STA_GROUP_CODE','Group ' --ANJ added the follwing columns as per table ARCHIVE.MEMBERSHIP_STATS_ARCHIVE ,'STA_REGION_CODE','Region ' ,'STA_GRADE_CODE','Grade ' ,'STA_POLITICAL_FUND_CODE','Political Fund ' ,'STA_DIVISION_CODE',v_def_division||' ' --ANJ end ,v_item_val) INTO v_col_table(v_col_table.last).col_type FROM DUAL; --ANJ For Visual Attribute --- SELECT DECODE(v_item_val,'STA_BRANCH_CODE','BLUE' ,'STA_EMPLOYER_CODE','ORANGE' ,'STA_WORKPLACE_CODE','CYAN' ,'STA_CATEGORY_CODE','PINK' ,'STA_JOB_CODE','BROWN' ,'STA_RATE','GREEN' ,'STA_PAYMENT_CODE','YELLOW' ,'STA_FREQUENCY','DARK_GREEN' ,'STA_SEX','MAGENTA' ,'STA_STATUS_CODE','GRAY' ,'STA_PAID_UP','LIGHT_GREEN' ,'STA_AGE_GROUP','VOILET' ,'STA_GROUP_CODE','LIGHT_BLUE' ,'STA_REGION_CODE','RED' ,'STA_GRADE_CODE','BLACK' ,'STA_POLITICAL_FUND_CODE','LIGHT_PINK' ,'STA_DIVISION_CODE','YELLOW' ,'MIDDLE_TEXT') INTO v_col_table(v_col_table.last).va_name FROM DUAL; --ANJ end debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Visual Attribute -'||v_col_table(v_col_table.last).va_name,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Column Prompt -'||v_col_table(v_col_table.last).col_type,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Column Prompt -'||v_col_table(v_col_table.last).col_name,'a'); END IF; END LOOP; -- Item/Elements LOOP END LOOP; -- BLOCK LOOP IF v_select IS NULL THEN display_alert_msg('Must select grouping element',1,''); return; END IF; display_msg('Please wait, process started .......',0,''); synchronize; set_application_property(CURSOR_STYLE,'BUSY'); IF :local.form_where IS NOT NULL THEN SET_LIST_QUERY; :local.form_where :=' AND '||:local.form_where ; ELSE :local.form_where :=NULL; END IF; -- ANJ to change the base table now we are using archive.membership_stats_archive and archive.membership_stats_dets_archive /* v_select := LTRIM(RTRIM(v_select,','),','); v_sql := 'SELECT '||v_select||',sum(bf) bf,sum(join) join,sum(left) left' ||' FROM mem_stats_archive ' ||' Where month = '||:b0.month1 ||' And year ='||:b0.year1 ||:b0.where_condition||:local.form_where -- ||' AND STA_BRANCH_CODE in ('||v_qte||'910101'||v_qte||','||v_qte||'910020'||v_qte||')' ||' GROUP BY '||v_select; --insert into es values(v_sql);commit; :local.group_by :='Result Group By '||initcap(replace(replace(v_select,'STA_'),'_',' ')); v_rg_id := Find_Group(v_rg_name ); */ --ANJ Added new item in all R blocks WHERE to keep the where clause for that block selection aftet double click on the record -- Here changed the concept earlier the WHERE clause was appending the conditions only for one time and for back the we were -- not able to leave the where condition for that reverse blocks :b0.where_condition := null; FOR i IN 1..v_block_count-1 LOOP :b0.where_condition := :b0.where_condition ||' '|| name_in('R'||to_char(i)||'.where'); END LOOP; --ANJ end debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Where Condition -'||:b0.where_condition,'a'); --fatal('hai where clause - '||:b0.where_condition ||' - '||v_block_count); v_select := LTRIM(RTRIM(v_select,','),','); /*v_sql := 'SELECT '||v_select||',sum(bf) bf,sum(new_members + reinstated) join,sum(left_service + withdrawn + deceased) left' ||' FROM membership_stats_archive ' ||' Where month = '||:b0.month1 ||' And year = '||:b0.year1 ||:b0.where_condition||:local.form_where -- ||' AND STA_BRANCH_CODE in ('||v_qte||'910101'||v_qte||','||v_qte||'910020'||v_qte||')' ||' GROUP BY '||v_select;*/ --insert into es values(v_sql);commit; IF :B0.MONTH1 is not null and :B0.MONTH1 < 9 THEN V_MONTH1 := '0'||TO_CHAR(:B0.MONTH1); ELSIF :B0.MONTH1 > 9 THEN V_MONTH1 := :B0.MONTH1; END IF; IF :B0.MONTH2 is not null and :B0.MONTH2 < 9 THEN V_MONTH2 := '0'||TO_CHAR(:B0.MONTH2); ELSIF :B0.MONTH2 > 9 THEN V_MONTH2 := :B0.MONTH2; END IF; v_sql := 'SELECT * FROM (SELECT '||v_select||', sum(decode(to_char(to_date(year,'||qte||'YYYY'||qte||'),'||qte||'YYYY'||qte||')||'||qte||'-'||qte||'||'||'to_char(to_date(month,'||qte||'MM'||qte||'),'||qte||'MM'||qte||'),'||qte||to_char(:b0.year1)||'-'||v_month1||qte||',bf))'||qtes||'BF_FROM'||qtes||', sum(decode(to_char(to_date(year,'||qte||'YYYY'||qte||'),'||qte||'YYYY'||qte||')||'||qte||'-'||qte||'||'||'to_char(to_date(month,'||qte||'MM'||qte||'),'||qte||'MM'||qte||'),'||qte||to_char(:b0.year2)||'-'||v_month2||qte||',bf))'||qtes||'BF_TO'||qtes||', sum(decode(to_char(to_date(year,'||qte||'YYYY'||qte||'),'||qte||'YYYY'||qte||')||'||qte||'-'||qte||'||'||'to_char(to_date(month,'||qte||'MM'||qte||'),'||qte||'MM'||qte||'),'||qte||to_char(:b0.year1)||'-'||v_month1||qte||',new_members + reinstated))'||qtes||'JOIN_FROM'||qtes||', sum(decode(to_char(to_date(year,'||qte||'YYYY'||qte||'),'||qte||'YYYY'||qte||')||'||qte||'-'||qte||'||'||'to_char(to_date(month,'||qte||'MM'||qte||'),'||qte||'MM'||qte||'),'||qte||to_char(:b0.year2)||'-'||v_month2||qte||',new_members + reinstated))'||qtes||'JOIN_TO'||qtes||', sum(decode(to_char(to_date(year,'||qte||'YYYY'||qte||'),'||qte||'YYYY'||qte||')||'||qte||'-'||qte||'||'||'to_char(to_date(month,'||qte||'MM'||qte||'),'||qte||'MM'||qte||'),'||qte||to_char(:b0.year1)||'-'||v_month1||qte||',left_service + withdrawn + deceased))'||qtes||'LEFT_FROM'||qtes||', sum(decode(to_char(to_date(year,'||qte||'YYYY'||qte||'),'||qte||'YYYY'||qte||')||'||qte||'-'||qte||'||'||'to_char(to_date(month,'||qte||'MM'||qte||'),'||qte||'MM'||qte||'),'||qte||to_char(:b0.year2)||'-'||v_month2||qte||',left_service + withdrawn + deceased))'||qtes||'LEFT_TO'||qtes||' from membership_stats_archive where rownum is not null '||:b0.where_condition||:local.form_where||' GROUP BY '||v_select||') order by 1'; /* v_sql := 'SELECT year,month,'||v_select||',sum(bf) bf,sum(new_members + reinstated) join,sum(left_service + withdrawn + deceased) left' ||' FROM membership_stats_archive ' ||' Where to_char(to_date(year,'||qte||'YYYY'||qte||'),'||qte||'YYYY'||qte||')||'||qte||'-'||qte||'||'||'to_char(to_date(month,'||qte||'MM'||qte||'),'||qte||'MM'||qte||') = '|| qte||to_char(:b0.year1)||'-'||v_month1||qte ||' or to_char(to_date(year,'||qte||'YYYY'||qte||'),'||qte||'YYYY'||qte||')||'||qte||'-'||qte||'||'||'to_char(to_date(month,'||qte||'MM'||qte||'),'||qte||'MM'||qte||') = '|| qte||to_char(:b0.year2)||'-'||v_month2||qte ||:b0.where_condition||:local.form_where -- ||' AND STA_BRANCH_CODE in ('||v_qte||'910101'||v_qte||','||v_qte||'910020'||v_qte||')' ||' GROUP BY '||v_select||',year,month'; */ -- fatal(' Where to_char(to_date(year,'||qte||'YYYY'||qte||'),'||qte||'YYYY'||qte||')||'||qte||'-'||qte||'||'||'to_char(to_date(month,'||qte||'MM'||qte||'),'||qte||'MM'||qte||') between '|| qte||to_char(:b0.year1)||'-'||:b0.month1||qte||' and '||qte||to_char(:b0.year2)||'-'||:b0.month2||qte); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','-------------------------------------------------------------','a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Select Statetment','a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt',v_sql,'a'); :local.group_by :='Result Group By '||initcap(replace(replace(v_select,'STA_'),'_',' ')); v_rg_id := Find_Group(v_rg_name ); -- :local.t := v_sql; IF NOT Id_Null(v_rg_id) THEN delete_group(v_rg_id); END IF; v_rg_id := Create_Group_From_Query( v_rg_name,v_sql); v_errcode := Populate_Group( v_rg_id ); -- fatal(v_errcode); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Error - '||v_errcode,'a'); IF nvl(v_errcode,0) != 0 THEN IF v_errcode = 1403 THEN display_msg('No data found',1,''); ELSE display_msg('Error while processing data ',1,''); END IF; set_application_property(CURSOR_STYLE,'NORMAL'); return; END IF; v_row_count := GET_GROUP_ROW_COUNT(v_rg_id); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','v_row_count-'||v_row_count,'a'); v_result_block := 'R'||v_block_count; go_block(v_result_block); clear_block(no_Validate); last_record; FOR row_num IN 1..v_row_count LOOP v_spaces := ' '; v_name := NULL; -- Reading data into pl/sql table FOR col_num IN v_col_table.first..v_col_table.last LOOP v_col_table(col_num).col_value := Get_Group_Char_Cell('stats.'||v_col_table(col_num).col_name,row_num); v_bf_from := Get_Group_Number_Cell('stats.bf_from',row_num); v_bf_to := Get_Group_Number_Cell('stats.bf_to',row_num); v_join_from := Get_Group_Number_Cell('stats.join_from',row_num); v_join_to := Get_Group_Number_Cell('stats.join_to',row_num); v_left_from := Get_Group_Number_Cell('stats.left_from',row_num); v_left_to := Get_Group_Number_Cell('stats.left_to',row_num); v_col_table(col_num).BTotal_Frm := nvl(v_bf_from,0); v_col_table(col_num).BTotal_To := nvl(v_bf_to,0); v_col_table(col_num).LTotal_Frm := nvl(v_left_from,0); v_col_table(col_num).LTotal_To := nvl(v_left_to,0); v_col_table(col_num).JTotal_Frm := nvl(v_join_from,0); v_col_table(col_num).JTotal_To := nvl(v_join_to,0); END LOOP; -- Writing pl/sql data to block FOR col_num IN v_col_table.first..v_col_table.last LOOP v_spaces := LPAD(v_spaces,col_num*3,' '); v_cur_val := v_col_table(col_num).col_value; v_old_val := v_col_table(col_num).old_col_value; v_col_type := v_col_table(col_num).col_type; v_col_name := v_col_table(col_num).col_name; IF col_num != v_col_table.last THEN IF v_cur_val != NVL(v_old_val,'XYZ') THEN -- print totals FOR col_no IN reverse col_num..(v_col_table.last-1) LOOP IF v_cur_val != NVL(v_old_val,v_cur_val) THEN v_spaces := LPAD(v_spaces,col_no*3,' '); v_tot_heading := v_col_table(col_no).col_type||' '; get_code_name(v_col_table(col_no).col_name,v_col_table(col_no).old_col_value,v_name); copy(v_spaces||'Total - '||v_tot_heading||v_col_table(col_no).old_col_value||'-'||v_name,v_result_block||'.D1'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','-------------------------------------------------------------','a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','If Current Value is not equal to Old Value','a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Before Total From - '||v_col_table(col_no).BTotal_Frm,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Before Total To - '||v_col_table(col_no).BTotal_To,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Left Total From - '||v_col_table(row_num).LTotal_Frm,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Left Total To - '||v_col_table(row_num).LTotal_To,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Join Total From - '||v_col_table(row_num).JTotal_Frm,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Join Total To - '||v_col_table(row_num).JTotal_To,'a'); copy(v_col_table(col_no).Btotal_Frm,v_result_block||'.bf1') ; copy(v_col_table(col_no).Ltotal_Frm,v_result_block||'.ly1') ; copy(v_col_table(col_no).Jtotal_Frm,v_result_block||'.jy1') ; copy((v_col_table(col_no).Btotal_Frm+v_col_table(col_no).Ltotal_Frm+v_col_table(col_no).Jtotal_Frm),v_result_block||'.ty1') ; copy(v_col_table(col_no).Btotal_To,v_result_block||'.bf2') ; copy(v_col_table(col_no).Ltotal_To,v_result_block||'.ly2') ; copy(v_col_table(col_no).Jtotal_To,v_result_block||'.jy2') ; copy((v_col_table(col_no).Btotal_to+v_col_table(col_no).Ltotal_to+v_col_table(col_no).Jtotal_to),v_result_block||'.ty2') ; --copy((v_col_table(col_no).Btotal+v_col_table(col_no).Ltotal+v_col_table(col_no).Jtotal),v_result_block||'.ty1') ; copy(v_col_table(col_no).old_col_value,v_result_block||'.value'); copy(v_col_table(col_no).col_name,v_result_block||'.name'); v_col_table(col_no).Ltotal_Frm := 0; v_col_table(col_no).Jtotal_Frm := 0; v_col_table(col_no).Btotal_Frm := 0; v_col_table(col_no).Ltotal_to := 0; v_col_table(col_no).Jtotal_to := 0; v_col_table(col_no).Btotal_to := 0; v_col_table(col_no).old_col_value := NULL; --ANJ Adding Visual Attributes IF v_col_table(col_no).va_name IS NOT NULL THEN v_cur_rec := to_number(get_block_property(v_result_block,current_record)); set_item_instance_property(v_result_block||'.D1',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.bf1',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.ly1',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.jy1',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.ty1',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.bf2',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.ly2',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.jy2',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.ty2',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); END IF; --ANJ end IF :system.record_status != 'NEW' THEN create_record; END IF; END IF; END LOOP; get_code_name(v_col_name,v_cur_val,v_name); copy(v_spaces||v_col_type||v_cur_val||'-'||v_name,v_result_block||'.D1'); -- storing column name and value of current record copy(v_cur_val,v_result_block||'.value'); copy(v_col_name,v_result_block||'.name'); END IF; ELSE -- write last group element with values -- fatal('hai in else - '); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','-------------------------------------------------------------','a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Last Group Element','a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Before Total From - '||v_col_table(row_num).BTotal_Frm,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Before Total To - '||v_col_table(row_num).BTotal_To,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Left Total From - '||v_col_table(row_num).LTotal_Frm,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Left Total To - '||v_col_table(row_num).LTotal_To,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Join Total From - '||v_col_table(row_num).JTotal_Frm,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Join Total To - '||v_col_table(row_num).JTotal_To,'a'); v_bf_from := Get_Group_Number_Cell('stats.bf_from',row_num); v_join_from := Get_Group_Number_Cell('stats.join_from',row_num); v_left_from := Get_Group_Number_Cell('stats.left_from',row_num); v_bf_to := Get_Group_Number_Cell('stats.bf_to',row_num); v_join_to := Get_Group_Number_Cell('stats.join_to',row_num); v_left_to := Get_Group_Number_Cell('stats.left_to',row_num); v_col_table(row_num).BTotal_Frm := nvl(v_bf_from,0); v_col_table(row_num).BTotal_To := nvl(v_bf_to,0); v_col_table(row_num).LTotal_Frm := nvl(v_left_from,0); v_col_table(row_num).LTotal_To := nvl(v_left_to,0); v_col_table(row_num).JTotal_Frm := nvl(v_join_from,0); v_col_table(row_num).JTotal_To := nvl(v_join_to,0); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','-------------------------------------------------------------','a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','After Last Group Element','a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Before Total From - '||v_col_table(row_num).BTotal_Frm,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Before Total To - '||v_col_table(row_num).BTotal_To,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Left Total From - '||v_col_table(row_num).LTotal_Frm,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Left Total To - '||v_col_table(row_num).LTotal_To,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Join Total From - '||v_col_table(row_num).JTotal_Frm,'a'); debugfile('\\mtldb3\utlfile\amit','st500_fmb.txt','Join Total To - '||v_col_table(row_num).JTotal_To,'a'); get_code_name(v_col_name,v_cur_val,v_name); copy(v_spaces||v_col_table(v_col_table.last).col_type||v_col_table(v_col_table.last).col_value||' '||v_name,v_result_block||'.D1'); copy(v_spaces||'Total - '||v_tot_heading||v_col_table(row_num).old_col_value||'-'||v_name,v_result_block||'.D1'); copy(v_col_table(row_num).Btotal_Frm,v_result_block||'.bf1'); copy(v_col_table(row_num).Ltotal_Frm,v_result_block||'.ly1'); copy(v_col_table(row_num).Jtotal_Frm,v_result_block||'.jy1'); copy((v_col_table(row_num).Btotal_Frm+v_col_table(row_num).Ltotal_Frm+v_col_table(row_num).Jtotal_Frm),v_result_block||'.ty1'); copy(v_col_table(row_num).Btotal_To,v_result_block||'.bf2'); copy(v_col_table(row_num).Ltotal_To,v_result_block||'.ly2'); copy(v_col_table(row_num).Jtotal_To,v_result_block||'.jy2'); copy((v_col_table(row_num).Btotal_to+v_col_table(row_num).Ltotal_to+v_col_table(row_num).Jtotal_to),v_result_block||'.ty2'); -- storing column name and value of current record copy(v_cur_val,v_result_block||'.value'); copy(v_col_name,v_result_block||'.name'); END IF; IF :system.record_status != 'NEW' THEN create_record; END IF; END LOOP; FOR col_num IN v_col_table.first..v_col_table.last LOOP v_col_table(col_num).old_col_value := v_col_table(col_num).col_value; v_col_table(col_num).col_value := NULL; v_col_table(col_num).Ltotal_frm := NVL(v_col_table(col_num).Ltotal_frm,0) + Get_Group_Number_Cell('stats.left_from',row_num); v_col_table(col_num).Jtotal_frm := NVL(v_col_table(col_num).Jtotal_frm,0) + Get_Group_Number_Cell('stats.join_from',row_num); v_col_table(col_num).Btotal_frm := NVL(v_col_table(col_num).Btotal_frm,0) + Get_Group_Number_Cell('stats.bf_from',row_num); v_col_table(col_num).Ltotal_to := NVL(v_col_table(col_num).Ltotal_to,0) + Get_Group_Number_Cell('stats.left_to',row_num); v_col_table(col_num).Jtotal_to := NVL(v_col_table(col_num).Jtotal_to,0) + Get_Group_Number_Cell('stats.join_to',row_num); v_col_table(col_num).Btotal_to := NVL(v_col_table(col_num).Btotal_to,0) + Get_Group_Number_Cell('stats.bf_to',row_num); END LOOP; END LOOP; -- print last totals out of above loop FOR col_no IN reverse 1..(v_col_table.last-1) LOOP v_spaces := LPAD(v_spaces,col_no*3,' '); get_code_name(v_col_table(col_no).col_name,v_col_table(col_no).old_col_value,v_name); copy(v_spaces||'Total - '||v_col_table(col_no).col_type||v_col_table(col_no).old_col_value||'-'||v_name,v_result_block||'.D1'); copy(v_col_table(col_no).Btotal_Frm,v_result_block||'.bf1') ; copy(v_col_table(col_no).Ltotal_Frm,v_result_block||'.ly1') ; copy(v_col_table(col_no).Jtotal_Frm,v_result_block||'.jy1') ; copy((v_col_table(col_no).Btotal_Frm+v_col_table(col_no).Ltotal_Frm+v_col_table(col_no).Jtotal_Frm),v_result_block||'.ty1') ; copy(v_col_table(col_no).Btotal_To,v_result_block||'.bf2') ; copy(v_col_table(col_no).Ltotal_To,v_result_block||'.ly2') ; copy(v_col_table(col_no).Jtotal_To,v_result_block||'.jy2') ; copy((v_col_table(col_no).Btotal_to+v_col_table(col_no).Ltotal_to+v_col_table(col_no).Jtotal_to),v_result_block||'.ty2') ; v_col_table(col_no).Ltotal_Frm := 0; v_col_table(col_no).Jtotal_Frm := 0; v_col_table(col_no).Btotal_Frm := 0; v_col_table(col_no).Ltotal_to := 0; v_col_table(col_no).Jtotal_to := 0; v_col_table(col_no).Btotal_to := 0; -- storing column name and value of current record copy(v_col_table(col_no).old_col_value,v_result_block||'.value'); copy(v_col_table(col_no).col_name,v_result_block||'.name'); --fatal('hai in final total'); --ANJ Adding Visual Attributes IF v_col_table(col_no).va_name IS NOT NULL THEN v_cur_rec := to_number(get_block_property(v_result_block,current_record)); set_item_instance_property(v_result_block||'.D1',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.bf1',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.ly1',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.jy1',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.ty1',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.bf2',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.ly2',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.jy2',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); set_item_instance_property(v_result_block||'.ty2',v_cur_rec,visual_attribute,v_col_table(col_no).va_name); END IF; --ANJ end IF :system.record_status != 'NEW' THEN create_record; END IF; END LOOP; first_record; v_col_table.delete; display_msg('Process Finished .......',0,''); set_application_property(CURSOR_STYLE,'NORMAL'); --message(v_result_block); pause; manage_windows.add_window(v_result_block); show_view(v_result_block); END; /*show_view('result1'); go_block('result1'); --execute_query; first_record; DECLARE rg_name VARCHAR2(40) := 'Stats'; rg_id RecordGroup; row_count number; errcode NUMBER; v_sql varchar2(1000) := 'SELECT '||:g1.e1||',sum(bf) bf,sum(join) join,sum(left) left'|| ' FROM mem_stats_archive where month = '||:b0.month1 ||' and year ='||:b0.year1 ||'GROUP BY '||:g1.e1; BEGIN :b0.t := v_sql; rg_id := Find_Group( rg_name ); IF Id_Null(rg_id) THEN rg_id := Create_Group_From_Query( rg_name, v_sql); END IF; errcode := Populate_Group( rg_id ); FOR j IN 1..row_count LOOP :result1.desc1 := Get_Group_char_Cell('stats.'||:g1.e1, j); :result1.bf1 := Get_Group_Number_Cell('stats.bf', j); :result1.ly1 := Get_Group_Number_Cell('stats.left', j); :result1.jy1 := Get_Group_Number_Cell('stats.join', j); -- :result1.ty1 := NVL(:result1.bf1,0)+NVL(:result1.jy1,0)+NVL(:result1.ly1,0); create_record; END LOOP; END; */