[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* PROPAGATE INPUT SELECTIONS *
* *
* *
* *
****************************************************************/
PROCEDURE propagateInput(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE)
IS
fromSource NUMBER;
ipRec ZPB_SOLVE_INPUT_SELECTIONS%ROWTYPE;
SELECT * FROM ZPB_SOLVE_INPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
SELECT SOURCE_TYPE INTO fromSource
FROM ZPB_SOLVE_MEMBER_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
DELETE FROM ZPB_SOLVE_INPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
DELETE ZPB_SOLVE_INPUT_SELECTIONS
where member = propagateList(i)
and analysis_cycle_id = p_ac_id
and dimension in ( select dimension from zpb_solve_input_selections
where member = p_from_member
and analysis_cycle_id = p_ac_id);
INSERT INTO ZPB_SOLVE_INPUT_SELECTIONS
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
SELECTION_PATH,
PROPAGATED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT ANALYSIS_CYCLE_ID,
propagateList(i),
propagateOrder(i),
DIMENSION,
HIERARCHY,
SELECTION_NAME,
SELECTION_PATH,
'Y',
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM ZPB_SOLVE_INPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_SOLVE_INPUT_SELECTIONS
SET PROPAGATED_FLAG ='Y'
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
SELECT * FROM ZPB_LINE_DIMENSIONALITY
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_LINE_DIMENSIONALITY
SET PROPAGATED_FLAG ='Y'
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
DELETE FROM ZPB_LINE_DIMENSIONALITY
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index);
INSERT INTO ZPB_LINE_DIMENSIONALITY
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
SUM_MEMBERS_NUMBER,
SUM_MEMBERS_FLAG ,
EXCLUDE_FROM_SOLVE_FLAG,
FORCE_INPUT_FLAG,
SUM_SELECTION_NAME,
SUM_SELECTION_PATH,
PROPAGATED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT
ANALYSIS_CYCLE_ID,
propagateList(p_to_index),
propagateOrder(p_to_index),
DIMENSION,
SUM_MEMBERS_NUMBER,
SUM_MEMBERS_FLAG ,
EXCLUDE_FROM_SOLVE_FLAG,
FORCE_INPUT_FLAG,
SUM_SELECTION_NAME,
SUM_SELECTION_PATH,
'Y',
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM ZPB_LINE_DIMENSIONALITY
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_LINE_DIMENSIONALITY
SET SUM_MEMBERS_NUMBER = dimRec.SUM_MEMBERS_NUMBER,
SUM_MEMBERS_FLAG = dimRec.SUM_MEMBERS_FLAG,
EXCLUDE_FROM_SOLVE_FLAG = dimRec.EXCLUDE_FROM_SOLVE_FLAG,
FORCE_INPUT_FLAG = dimRec.FORCE_INPUT_FLAG,
SUM_SELECTION_NAME = dimRec.SUM_SELECTION_NAME,
SUM_SELECTION_PATH = dimRec.SUM_SELECTION_PATH,
PROPAGATED_FLAG = 'Y',
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index) AND
DIMENSION =dimRec.DIMENSION;
* COPY INPUT SELECTIONS *
* *
* *
*****************************************************************/
PROCEDURE copyInputSelections(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE,
p_to_index IN INTEGER,
p_dimensionality_flag IN VARCHAR2 DEFAULT 'NO')
IS
ipRec ZPB_SOLVE_INPUT_SELECTIONS%ROWTYPE;
SELECT * FROM ZPB_SOLVE_INPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_SOLVE_INPUT_SELECTIONS
SET PROPAGATED_FLAG ='Y'
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
DELETE FROM ZPB_SOLVE_INPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index);
INSERT INTO ZPB_SOLVE_INPUT_SELECTIONS
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
SELECTION_PATH,
PROPAGATED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT
ANALYSIS_CYCLE_ID,
propagateList(p_to_index),
propagateOrder(p_to_index),
DIMENSION,
HIERARCHY,
SELECTION_NAME,
SELECTION_PATH,
'Y',
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM ZPB_SOLVE_INPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_SOLVE_INPUT_SELECTIONS
SET
SELECTION_NAME = ipRec.SELECTION_NAME,
SELECTION_PATH = ipRec.SELECTION_PATH,
PROPAGATED_FLAG = 'Y',
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index) AND
DIMENSION =ipRec.DIMENSION AND HIERARCHY = ipRec.HIERARCHY;
END copyInputSelections;
PROCEDURE copyOutputSelections(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE,
p_to_index IN INTEGER,
p_dimensionality_flag IN VARCHAR2)
IS
opRec ZPB_SOLVE_OUTPUT_SELECTIONS%ROWTYPE;
SELECT * FROM ZPB_SOLVE_OUTPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_SOLVE_OUTPUT_SELECTIONS
SET PROPAGATED_FLAG ='Y'
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
DELETE FROM ZPB_SOLVE_OUTPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index);
INSERT INTO ZPB_SOLVE_OUTPUT_SELECTIONS
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
SELECTION_PATH,
PROPAGATED_FLAG,
MATCH_INPUT_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT
ANALYSIS_CYCLE_ID,
propagateList(p_to_index),
propagateOrder(p_to_index),
DIMENSION,
HIERARCHY,
SELECTION_NAME,
SELECTION_PATH,
'Y',
MATCH_INPUT_FLAG,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM ZPB_SOLVE_OUTPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_SOLVE_OUTPUT_SELECTIONS
SET
SELECTION_NAME = opRec.SELECTION_NAME,
SELECTION_PATH = opRec.SELECTION_PATH,
PROPAGATED_FLAG = 'Y',
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index) AND
DIMENSION =opRec.DIMENSION AND HIERARCHY = opRec.HIERARCHY;
END copyOutputSelections;
UPDATE ZPB_SOLVE_MEMBER_DEFS
SET CALCSTEP_PATH = NULL,
CALC_TYPE = NULL,
CALC_DESCRIPTION = NULL,
CALC_PARAMETERS = NULL,
MODEL_EQUATION = NULL,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_to_member;
PROCEDURE deleteInializedInputSettings(p_ac_id IN ZPB_DATA_INITIALIZATION_DEFS.ANALYSIS_CYCLE_ID%TYPE,
p_to_member IN ZPB_DATA_INITIALIZATION_DEFS.MEMBER%TYPE)
IS
BEGIN
DELETE ZPB_DATA_INITIALIZATION_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_to_member;
DELETE ZPB_COPY_DIM_MEMBERS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND LINE_MEMBER_ID = p_to_member;
END deleteInializedInputSettings;
SELECT SOURCE_QUERY_NAME,TARGET_QUERY_NAME
FROM ZPB_DATA_INITIALIZATION_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
INSERT INTO ZPB_DATA_INITIALIZATION_DEFS
(ANALYSIS_CYCLE_ID,
MEMBER,
SOURCE_VIEW,
LAG_TIME_PERIODS,
LAG_TIME_LEVEL,
CHANGE_NUMBER,
PERCENTAGE_FLAG,
QUERY_PATH,
SOURCE_QUERY_NAME,
TARGET_QUERY_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROPAGATED_FLAG)
SELECT ANALYSIS_CYCLE_ID,
p_to_member,
SOURCE_VIEW,
LAG_TIME_PERIODS,
LAG_TIME_LEVEL,
CHANGE_NUMBER,
PERCENTAGE_FLAG,
QUERY_PATH,
source_query_name,
target_query_name,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID,
PROPAGATED_FLAG
FROM ZPB_DATA_INITIALIZATION_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
INSERT INTO zpb_copy_dim_members
(DIM,
ANALYSIS_CYCLE_ID,
SOURCE_NUM_MEMBERS,
TARGET_NUM_MEMBERS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SAME_SELECTION,
LINE_MEMBER_ID)
SELECT DIM,
ANALYSIS_CYCLE_ID,
SOURCE_NUM_MEMBERS,
TARGET_NUM_MEMBERS,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID,
SAME_SELECTION,
p_to_member
FROM zpb_copy_dim_members
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND LINE_MEMBER_ID = p_from_member;
* If ZPB_SOLVE_OUTPUT_SELECTIONS.MATCH_INPUT_FLAG = 'Y' for *
* Loaded,i/p or worksheet i/p line member *
* then before changing the source type to calc or hier total *
* copy the input selection to output selection and set *
* MATCH_INPUT_FLAG to 'N' *
***********************************************************************/
PROCEDURE checkMatchInputToOutputFlag(p_ac_id IN ZPB_SOLVE_OUTPUT_SELECTIONS.ANALYSIS_CYCLE_ID%TYPE,
p_from_member IN ZPB_SOLVE_OUTPUT_SELECTIONS.MEMBER%TYPE)
IS
l_selectionPath ZPB_SOLVE_INPUT_SELECTIONS.SELECTION_PATH%TYPE;
l_selectionName ZPB_SOLVE_INPUT_SELECTIONS.SELECTION_NAME%TYPE;
SELECT MEMBER_ORDER,DIMENSION,HIERARCHY,
SELECTION_PATH,SELECTION_NAME,MATCH_INPUT_FLAG
FROM ZPB_SOLVE_OUTPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member
FOR UPDATE;
CURSOR c2(p_ac_id ZPB_SOLVE_INPUT_SELECTIONS.ANALYSIS_CYCLE_ID%TYPE,
p_from_member ZPB_SOLVE_INPUT_SELECTIONS.MEMBER%TYPE,
p_member_order ZPB_SOLVE_INPUT_SELECTIONS.MEMBER_ORDER%TYPE,
p_dimension ZPB_SOLVE_INPUT_SELECTIONS.DIMENSION%TYPE,
p_hierarchy ZPB_SOLVE_INPUT_SELECTIONS.HIERARCHY%TYPE)IS
SELECT SELECTION_PATH,SELECTION_NAME
FROM ZPB_SOLVE_INPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member AND
MEMBER_ORDER = p_member_order AND DIMENSION = p_dimension AND
HIERARCHY = p_hierarchy;
FETCH c2 INTO l_selectionPath,l_selectionName;
UPDATE ZPB_SOLVE_OUTPUT_SELECTIONS
SET SELECTION_PATH = l_selectionPath,
SELECTION_NAME = l_selectionName,
MATCH_INPUT_FLAG = 'N'
WHERE CURRENT OF c1;
* PROPAGATE SOURCE TYPE&(DIM INFO or I/P SELECTIONS *
* *
* *
*****************************************************************/
PROCEDURE propagateCalc(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE,
p_prop_dimhandling IN INTEGER,
p_prop_input IN INTEGER,
p_prop_output IN INTEGER)
IS
fromSource ZPB_SOLVE_MEMBER_DEFS.SOURCE_TYPE%TYPE;
updateSolve BOOLEAN;
SELECT SOURCE_TYPE INTO fromSource
FROM ZPB_SOLVE_MEMBER_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_SOLVE_MEMBER_DEFS
SET SOURCE_TYPE = fromSource,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
UPDATE ZPB_DATA_INITIALIZATION_DEFS
SET propagated_flag='Y'
WHERE ANALYSIS_CYCLE_ID=p_ac_id
AND MEMBER=p_from_member;
UPDATE ZPB_SOLVE_MEMBER_DEFS
SET
SOURCE_TYPE = fromSource,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
copyInputSelections(p_ac_id,p_from_member,i,'RECREATE');
copyOutputSelections(p_ac_id,p_from_member,i,'RECREATE');
ELSE -- delete the o/p selections for non hier dims
deleteOutputSelections(p_ac_id,i);
deleteInializedInputSettings(p_ac_id,propagateList(i));
DELETE ZPB_LINE_DIMENSIONALITY WHERE
ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
copyInputSelections(p_ac_id,p_from_member,i,'RECREATE');
copyOutputSelections(p_ac_id,p_from_member,i,'RECREATE');
ELSE -- delete the o/p selections for non hier dims
deleteOutputSelections(p_ac_id,i);
deleteInializedInputSettings(p_ac_id,propagateList(i));
DELETE ZPB_LINE_DIMENSIONALITY WHERE
ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
copyInputSelections(p_ac_id,p_from_member,i,'RECREATE');
deleteInializedInputSettings(p_ac_id,propagateList(i));
copyOutputSelections(p_ac_id,p_from_member,i,'RECREATE');
ELSE -- delete the o/p selections for non hier dims
deleteOutputSelections(p_ac_id,i);
DELETE ZPB_LINE_DIMENSIONALITY WHERE
ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
DELETE ZPB_SOLVE_INPUT_SELECTIONS WHERE
ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
DELETE ZPB_SOLVE_INPUT_SELECTIONS WHERE
ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
DELETE ZPB_SOLVE_INPUT_SELECTIONS WHERE
ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
deleteInializedInputSettings(p_ac_id,propagateList(i));
copyOutputSelections(p_ac_id,p_from_member,i,'RECREATE');
ELSIF fromSource = AGGREGATED_SOURCE THEN -- delete the o/p selections for non hier dims
deleteOutputSelections(p_ac_id,i);
copyOutputSelections(p_ac_id,p_from_member,i,'COPY');
copyOutputSelections(p_ac_id,p_from_member,i,'RECREATE');
SELECT CALC_DESCRIPTION,CALC_TYPE,CALC_PARAMETERS,MODEL_EQUATION
INTO l_calcDesc,l_calcType,l_calcParams,l_modelEquation
FROM ZPB_SOLVE_MEMBER_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_SOLVE_MEMBER_DEFS
SET CALCSTEP_PATH = propagateList(i),
CALC_TYPE = l_calcType,
CALC_DESCRIPTION = l_calcDesc,
CALC_PARAMETERS = l_calcParams,
MODEL_EQUATION = l_modelEquation,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
DELETE FROM ZPB_SOLVE_ALLOCATION_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
INSERT INTO ZPB_SOLVE_ALLOCATION_DEFS
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
RULE_NAME,
METHOD,
EVALUATION_OPTION,
ROUND_DECIMALS,
ROUND_ENABLED,
BASIS,
QUALIFIER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT ANALYSIS_CYCLE_ID, propagateList(i), propagateOrder(i), RULE_NAME,
METHOD, EVALUATION_OPTION, ROUND_DECIMALS, ROUND_ENABLED,
BASIS, QUALIFIER, fnd_global.USER_ID, SYSDATE,
fnd_global.USER_ID, SYSDATE, fnd_global.LOGIN_ID
FROM ZPB_SOLVE_ALLOCATION_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
* PROPAGATE OUTPUT SELECTIONS *
* *
* *
*****************************************************************/
PROCEDURE propagateOutput(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE)
IS
opRec ZPB_SOLVE_OUTPUT_SELECTIONS%ROWTYPE;
SELECT * FROM ZPB_SOLVE_OUTPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
SELECT SOURCE_TYPE INTO fromSource
FROM ZPB_SOLVE_MEMBER_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_SOLVE_OUTPUT_SELECTIONS
SET PROPAGATED_FLAG ='Y'
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
DELETE ZPB_SOLVE_OUTPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
DELETE ZPB_SOLVE_OUTPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i) AND
HIERARCHY <> 'NONE' ;
INSERT INTO ZPB_SOLVE_OUTPUT_SELECTIONS
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
SELECTION_PATH,
PROPAGATED_FLAG,
MATCH_INPUT_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES(opRec.ANALYSIS_CYCLE_ID,
propagateList(i),
propagateOrder(i),
opRec.DIMENSION,
opRec.HIERARCHY,
opRec.SELECTION_NAME,
opRec.SELECTION_PATH,
'Y',
opRec.MATCH_INPUT_FLAG,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID);
DELETE ZPB_SOLVE_OUTPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND
DIMENSION in (select dimension from ZPB_SOLVE_OUTPUT_SELECTIONS
where member = opRec.member
and dimension = opRec.dimension
and analysis_cycle_id = p_ac_id)
and member = propagateList(i);
INSERT INTO ZPB_SOLVE_OUTPUT_SELECTIONS
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
SELECTION_PATH,
PROPAGATED_FLAG,
MATCH_INPUT_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES(opRec.ANALYSIS_CYCLE_ID,
propagateList(i),
propagateOrder(i),
opRec.DIMENSION,
opRec.HIERARCHY,
opRec.SELECTION_NAME,
opRec.SELECTION_PATH,
'Y',
opRec.MATCH_INPUT_FLAG,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID);
DELETE ZPB_SOLVE_OUTPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND
MEMBER = propagateList(i) ;
INSERT INTO ZPB_SOLVE_OUTPUT_SELECTIONS
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
SELECTION_PATH,
PROPAGATED_FLAG,
MATCH_INPUT_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES(opRec.ANALYSIS_CYCLE_ID,
propagateList(i),
propagateOrder(i),
opRec.DIMENSION,
opRec.HIERARCHY,
opRec.SELECTION_NAME,
opRec.SELECTION_PATH,
'Y',
opRec.MATCH_INPUT_FLAG,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID);
SELECT * FROM ZPB_LINE_DIMENSIONALITY
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
SELECT SOURCE_TYPE INTO fromSource
FROM ZPB_SOLVE_MEMBER_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
DELETE ZPB_LINE_DIMENSIONALITY
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
INSERT INTO ZPB_LINE_DIMENSIONALITY
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
SUM_MEMBERS_NUMBER,
SUM_MEMBERS_FLAG ,
EXCLUDE_FROM_SOLVE_FLAG,
FORCE_INPUT_FLAG,
SUM_SELECTION_NAME,
SUM_SELECTION_PATH,
PROPAGATED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (dimRec.ANALYSIS_CYCLE_ID,
propagateList(i),
propagateOrder(i),
dimRec.DIMENSION,
dimRec.SUM_MEMBERS_NUMBER,
dimRec.SUM_MEMBERS_FLAG ,
dimRec.EXCLUDE_FROM_SOLVE_FLAG,
dimRec.FORCE_INPUT_FLAG,
dimRec.SUM_SELECTION_NAME,
dimRec.SUM_SELECTION_PATH,
'Y',
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID);
UPDATE ZPB_LINE_DIMENSIONALITY
SET PROPAGATED_FLAG ='Y'
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
tableselect varchar(5000);
tableselect := 'Select member, member_order, memberlookup.';
tableselect := tableselect || p_view_short_lbl_column || ' as MemberName,';
tableselect := tableselect || ' SOURCE_TYPE';
tableselect := tableselect || ' FROM ZPB_SOLVE_MEMBER_DEFS defs, ';
tableselect := tableselect || p_view_dim_name || ' memberlookup';
tableselect := tableselect || ' WHERE defs.ANALYSIS_CYCLE_ID = ' || p_ac_id;
tableselect := tableselect || ' AND defs.PROPAGATE_TARGET = ' || iTrueValue;
tableselect := tableselect || ' AND defs.member = memberlookup.' || p_view_member_column;
OPEN c4 FOR tableSelect;
SELECT SOURCE_TYPE INTO fromSource
FROM ZPB_SOLVE_MEMBER_DEFS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
UPDATE ZPB_SOLVE_MEMBER_DEFS
SET PROPAGATE_TARGET = iFalseValue,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND PROPAGATE_TARGET = iTrueValue;
SELECT MEANING||',' INTO ret_meaning
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'ZPB_SOLVE_DIMENSIONLIST_SELECT' AND LOOKUP_CODE = p_lookup_code;
PROCEDURE updateCleanup(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
p_line_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE,
p_src_type IN ZPB_SOLVE_MEMBER_DEFS.SOURCE_TYPE%TYPE)
IS
BEGIN
IF p_src_type <> LOADED_SOURCE THEN
delete from zpb_line_dimensionality where analysis_cycle_id = p_ac_id and member = p_line_member;
delete from zpb_solve_input_selections where analysis_cycle_id = p_ac_id and member = p_line_member;
update ZPB_SOLVE_OUTPUT_SELECTIONS
set MATCH_INPUT_FLAG = 'N'
where analysis_cycle_id = p_ac_id and member = p_line_member;
delete from zpb_solve_output_selections where analysis_cycle_id = p_ac_id and member = p_line_member
AND hierarchy = 'NONE';
delete from zpb_status_sql
where query_path in (select query_path||'/'||target_query_name
from zpb_data_initialization_defs
where analysis_cycle_id = p_ac_id
and member = p_line_member);
delete from zpb_status_sql
where query_path in (select query_path||'/'||source_query_name
from zpb_data_initialization_defs
where analysis_cycle_id = p_ac_id
and member = p_line_member);
delete from ZPB_DATA_INITIALIZATION_DEFS where analysis_cycle_id = p_ac_id and member = p_line_member;
delete from ZPB_COPY_DIM_MEMBERS where analysis_cycle_id = p_ac_id and line_member_id = p_line_member;
END updateCleanup;
PROCEDURE deleteOutputSelections(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
p_targetIndex IN INTEGER )
IS
BEGIN
DELETE ZPB_SOLVE_OUTPUT_SELECTIONS
WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_targetIndex)
AND HIERARCHY = 'NONE';
END deleteOutputSelections;
PROCEDURE insertDefaultOutput(p_ac_id IN ZPB_SOLVE_OUTPUT_SELECTIONS.ANALYSIS_CYCLE_ID%TYPE,
p_line_member IN ZPB_SOLVE_OUTPUT_SELECTIONS.MEMBER%TYPE,
p_memberOrder IN ZPB_SOLVE_OUTPUT_SELECTIONS.MEMBER_ORDER%TYPE,
p_dimension IN ZPB_SOLVE_OUTPUT_SELECTIONS.DIMENSION%TYPE)
IS
BEGIN
INSERT INTO ZPB_SOLVE_OUTPUT_SELECTIONS
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
MATCH_INPUT_FLAG,
PROPAGATED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (p_ac_id,
p_line_member,
p_memberOrder,
p_dimension,
'NONE',
'DEFAULT',
'N',
'N',
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID);
END insertDefaultOutput;
PROCEDURE initialize_solve_selections
(p_ac_id IN zpb_analysis_cycles.analysis_cycle_id%TYPE) is
l_count number;
l_new_hier ZPB_SOLVE_INPUT_SELECTIONS.HIERARCHY%type;
l_dimension ZPB_SOLVE_INPUT_SELECTIONS.DIMENSION%type;
select distinct hierarchy, dimension
from zpb_solve_input_selections
where analysis_cycle_id = p_ac_id
and hierarchy <> 'NONE'
MINUS
select distinct a.epb_id, b.aw_name
from zpb_hierarchies a,
zpb_dimensions b,
zpb_hier_scope c
where a.dimension_id = b.dimension_id
and b.bus_area_id = l_ba_id
and b.is_data_dim = 'YES'
and a.hierarchy_id = c.hierarchy_id
and c.user_id = l_shadow;
select distinct hierarchy, dimension
from zpb_solve_output_selections
where analysis_cycle_id = p_ac_id
and hierarchy <> 'NONE'
MINUS
select distinct a.EPB_ID, b.aw_name
from zpb_hierarchies a,
zpb_dimensions b,
zpb_hier_scope c
where a.dimension_id = b.dimension_id
and b.bus_area_id = l_ba_id
and b.is_data_dim = 'YES'
and a.hierarchy_id = c.hierarchy_id
and c.user_id = l_shadow;
select count(*)
into l_count
from zpb_dimensions a,
zpb_hierarchies b,
zpb_hier_scope c
where a.aw_name = each.dimension
and a.bus_area_id = l_ba_id
and a.dimension_id = b.dimension_id
and b.epb_id <> 'NULL_GID'
and b.hierarchy_id = c.hierarchy_id
and c.user_id = l_shadow;
delete from zpb_solve_input_selections
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy = each.hierarchy
and member in
(select member
from zpb_solve_input_selections
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy <> each.hierarchy);
select a.DEFAULT_HIER
into l_new_hier
from zpb_dimensions a,
zpb_hierarchies b,
zpb_hier_scope c
where a.aw_name = each.dimension
and a.bus_area_id = l_ba_id
and a.dimension_id = b.dimension_id
and b.epb_id = a.default_hier
and b.hierarchy_id = c.hierarchy_id
and c.user_id = l_shadow;
select min(b.epb_id)
into l_new_hier
from zpb_dimensions a,
zpb_hierarchies b,
zpb_hier_scope c
where a.aw_name = each.dimension
and a.bus_area_id = l_ba_id
and a.dimension_id = b.dimension_id
and b.hierarchy_id = c.hierarchy_id
and c.user_id = l_shadow;
update zpb_solve_input_selections
set selection_name = 'DEFAULT',
selection_path = null,
hierarchy = l_new_hier,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy = each.hierarchy;
delete from zpb_solve_input_selections
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy = each.hierarchy
and member in
(select member
from zpb_solve_input_selections
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy = 'NONE');
update zpb_solve_input_selections
set hierarchy = 'NONE',
selection_path = null,
selection_name = 'DEFAULT',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy = each.hierarchy;
select count(*)
into l_count
from zpb_dimensions a,
zpb_hierarchies b,
zpb_hier_scope c
where a.aw_name = each.dimension
and a.bus_area_id = l_ba_id
and a.dimension_id = b.dimension_id
and b.epb_id <> 'NULL_GID'
and b.hierarchy_id = c.hierarchy_id
and c.user_id = l_shadow;
delete from zpb_solve_output_selections
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy = each.hierarchy
and member in
(select member
from zpb_solve_output_selections
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy <> each.hierarchy);
select a.DEFAULT_HIER
into l_new_hier
from zpb_dimensions a,
zpb_hierarchies b,
zpb_hier_scope c
where a.aw_name = each.dimension
and a.bus_area_id = l_ba_id
and a.dimension_id = b.dimension_id
and b.epb_id = a.default_hier
and b.hierarchy_id = c.hierarchy_id
and c.user_id = l_shadow;
select min(b.epb_id)
into l_new_hier
from zpb_dimensions a,
zpb_hierarchies b,
zpb_hier_scope c
where a.aw_name = each.dimension
and a.bus_area_id = l_ba_id
and a.dimension_id = b.dimension_id
and b.hierarchy_id = c.hierarchy_id
and c.user_id = l_shadow;
update zpb_solve_output_selections
set selection_name = 'DEFAULT',
selection_path = null,
hierarchy = l_new_hier,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy = each.hierarchy;
delete from zpb_solve_output_selections
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy = each.hierarchy
and member in
(select member
from zpb_solve_output_selections
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy = 'NONE');
update zpb_solve_output_selections
set hierarchy = 'NONE',
selection_path = null,
selection_name = 'DEFAULT',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where analysis_cycle_id = p_ac_id
and dimension = each.dimension
and hierarchy = each.hierarchy;
end initialize_solve_selections;
SELECT task_id INTO l_taskid
FROM zpb_analysis_cycle_tasks
WHERE sequence =
( SELECT max(sequence)
FROM zpb_analysis_cycle_tasks
WHERE analysis_cycle_id = p_instance_id
AND status_code IN ('COMPLETE')
) + 1
AND analysis_cycle_id = p_instance_id;