[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT zaci.instance_ac_id, zac.current_instance_id, t.wf_process_name, t.item_key, t.task_id
FROM ZPB_ANALYSIS_CYCLE_INSTANCES zaci,
ZPB_ANALYSIS_CYCLES zac,
zpb_analysis_cycle_tasks t,
ZPB_ANALYSIS_CYCLES publishedac
WHERE publishedac.analysis_cycle_id = p_acid and
publishedac.current_instance_id=zac.current_instance_id and
zaci.instance_ac_id = zac.analysis_cycle_id and
zac.status_code NOT in('COMPLETE', 'COMPLETE_WITH_WARNING', 'ERROR')
AND zaci.instance_ac_id = t.ANALYSIS_CYCLE_ID
and t.staTus_code = 'ACTIVE';
select BUSINESS_AREA_ID
into l_business_area_id
from ZPB_ANALYSIS_CYCLES
where ANALYSIS_CYCLE_ID = p_acid;
select count(*) into l_count
from wf_items_v
where item_key = l_item_key;
update zpb_analysis_cycle_tasks
set status_code='COMPLETE'
where task_id = v_active_instance.task_id;
INSERT INTO zpb_analysis_cycles
(ANALYSIS_CYCLE_ID,
STATUS_CODE,
NAME,
DESCRIPTION,
LOCKED_BY,
VALIDATE_STATUS,
CURRENT_INSTANCE_ID,
PUBLISHED_DATE,
PUBLISHED_BY,
PREV_STATUS_CODE,
OWNER_ID,
BUSINESS_AREA_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
STATUS_CODE,
NAME,
DESCRIPTION,
LOCKED_BY,
VALIDATE_STATUS,
CURRENT_INSTANCE_ID,
PUBLISHED_DATE,
PUBLISHED_BY,
PREV_STATUS_CODE,
OWNER_ID,
BUSINESS_AREA_ID,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_analysis_cycles
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_ac_param_values
(ANALYSIS_CYCLE_ID,
PARAM_ID,
VALUE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
PARAM_ID,
VALUE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_ac_param_values
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_cycle_currencies
(ANALYSIS_CYCLE_ID,
CURRENCY_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
CURRENCY_CODE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_cycle_currencies
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_bp_external_users
(ANALYSIS_CYCLE_ID,
USER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
USER_ID,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_bp_external_users
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_cycle_datasets
(ANALYSIS_CYCLE_ID,
DATASET_CODE,
ORDER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
DATASET_CODE,
ORDER_ID,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_cycle_datasets
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_cycle_model_dimensions
(ANALYSIS_CYCLE_ID,
DIMENSION_NAME,
QUERY_OBJECT_NAME,
QUERY_OBJECT_PATH,
DATASET_DIMENSION_FLAG,
REMOVE_DIMENSION_FLAG,
SUM_MEMBERS_NUMBER,
SUM_SELECTION_NAME,
SUM_SELECTION_PATH,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
SELECT target_ac_id_in,
DIMENSION_NAME,
QUERY_OBJECT_NAME,
QUERY_OBJECT_PATH,
DATASET_DIMENSION_FLAG,
REMOVE_DIMENSION_FLAG,
SUM_MEMBERS_NUMBER,
SUM_SELECTION_NAME,
SUM_SELECTION_PATH,
fnd_global.LOGIN_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID
FROM zpb_cycle_model_dimensions
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_measure_scope_exempt_users
(BUSINESS_PROCESS_ENTITY_ID,
USER_ID,
EXEMPTION_ID,
TASK_ID,
BUSINESS_PROCESS_ENTITY_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
USER_ID,
EXEMPTION_ID,
target_task_id,
BUSINESS_PROCESS_ENTITY_TYPE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_measure_scope_exempt_users
WHERE BUSINESS_PROCESS_ENTITY_ID = source_ac_id_in
AND BUSINESS_PROCESS_ENTITY_TYPE = 'A'
AND TASK_ID = source_task_id;
INSERT INTO zpb_business_process_scope
(ANALYSIS_CYCLE_ID,
TASK_ID,
RESTRICTION_TYPE,
START_TIME_TYPE,
START_TIME_MEMBER_ID,
START_RELATIVE_TYPE_CODE,
START_PERIODS,
START_TIME_LEVEL_ID,
END_TIME_TYPE,
END_TIME_MEMBER_ID,
END_RELATIVE_TYPE_CODE,
END_PERIODS,
END_TIME_LEVEL_ID,
TIME_HIERARCHY_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
target_task_id,
RESTRICTION_TYPE,
START_TIME_TYPE,
START_TIME_MEMBER_ID,
START_RELATIVE_TYPE_CODE,
START_PERIODS,
START_TIME_LEVEL_ID,
END_TIME_TYPE,
END_TIME_MEMBER_ID,
END_RELATIVE_TYPE_CODE,
END_PERIODS,
END_TIME_LEVEL_ID,
TIME_HIERARCHY_ID,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_business_process_scope
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in
AND TASK_ID = source_task_id;
INSERT INTO zpb_cycle_comments
(COMMENT_ID,
ANALYSIS_CYCLE_ID,
COMMENTS,
OWNER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT zpb_cycle_comments_id_seq.NEXTVAL,
target_ac_id_in,
COMMENTS,
OWNER_ID,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_cycle_comments
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_task_parameters
( NAME,
TASK_ID,
VALUE,
PARAM_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT NAME,
target_task_id_in,
decode(name, 'OWNER_ID', to_char( fnd_global.USER_ID ),VALUE),
zpb_task_param_id_seq.NEXTVAL,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_task_parameters
WHERE TASK_ID = source_task_id_in;
SELECT *
FROM zpb_analysis_cycle_tasks
WHERE analysis_cycle_id = source_ac_id_in;
select 1 FROM zpb_task_parameters where name = 'EXCEPTION_TYPE'
and value = 'A' and task_id = l_taskId;
SELECT zpb_task_id_seq.NEXTVAL INTO target_task_id FROM DUAL;
ac_task_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
ac_task_rec.LAST_UPDATE_DATE := SYSDATE;
ac_task_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
INSERT INTO zpb_analysis_cycle_tasks(ANALYSIS_CYCLE_ID,
TASK_ID,
SEQUENCE,
TASK_NAME,
STATUS_CODE,
ITEM_TYPE,
WF_PROCESS_NAME,
ITEM_KEY,
START_DATE,
HIDE_SHOW,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OWNER_ID)
VALUES (ac_task_rec.ANALYSIS_CYCLE_ID,
ac_task_rec.TASK_ID,
ac_task_rec.SEQUENCE,
ac_task_rec.TASK_NAME,
ac_task_rec.STATUS_CODE,
ac_task_rec.ITEM_TYPE,
ac_task_rec.WF_PROCESS_NAME,
ac_task_rec.ITEM_KEY,
ac_task_rec.START_DATE,
ac_task_rec.HIDE_SHOW,
ac_task_rec.CREATION_DATE,
ac_task_rec.CREATED_BY,
ac_task_rec.LAST_UPDATED_BY,
ac_task_rec.LAST_UPDATE_DATE,
ac_task_rec.LAST_UPDATE_LOGIN,
fnd_global.USER_ID);
INSERT INTO zpb_solve_member_defs
(ANALYSIS_CYCLE_ID,
MEMBER,
SOURCE_TYPE,
MEMBER_ORDER,
CALCSTEP_PATH,
CALC_DESCRIPTION,
CALC_TYPE,
CALC_PARAMETERS,
MODEL_EQUATION,
PROPAGATE_TARGET,
DATA_SOURCE,
CURRENT_MODIFIED,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
MEMBER,
SOURCE_TYPE,
MEMBER_ORDER,
CALCSTEP_PATH,
CALC_DESCRIPTION,
CALC_TYPE,
CALC_PARAMETERS,
MODEL_EQUATION,
PROPAGATE_TARGET,
DATA_SOURCE,
CURRENT_MODIFIED,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_solve_member_defs
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
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,
target_ac_id_in,
SOURCE_NUM_MEMBERS,
TARGET_NUM_MEMBERS,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID,
SAME_SELECTION,
LINE_MEMBER_ID
FROM zpb_copy_dim_members
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_data_initialization_defs
(ANALYSIS_CYCLE_ID,
MEMBER,
SOURCE_VIEW,
LAG_TIME_PERIODS,
LAG_TIME_LEVEL,
CHANGE_NUMBER,
PERCENTAGE_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
QUERY_PATH,
SOURCE_QUERY_NAME,
TARGET_QUERY_NAME,
PROPAGATED_FLAG)
SELECT target_ac_id_in,
MEMBER,
SOURCE_VIEW,
LAG_TIME_PERIODS,
LAG_TIME_LEVEL,
CHANGE_NUMBER,
PERCENTAGE_FLAG,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID,
QUERY_PATH,
SOURCE_QUERY_NAME,
TARGET_QUERY_NAME,
PROPAGATED_FLAG
FROM zpb_data_initialization_defs
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_solve_input_selections
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
PROPAGATED_FLAG,
SELECTION_PATH,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
PROPAGATED_FLAG,
SELECTION_PATH,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_solve_input_selections
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_solve_output_selections
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
PROPAGATED_FLAG,
MATCH_INPUT_FLAG,
SELECTION_PATH,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
MEMBER,
MEMBER_ORDER,
DIMENSION,
HIERARCHY,
SELECTION_NAME,
PROPAGATED_FLAG,
MATCH_INPUT_FLAG,
SELECTION_PATH,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_solve_output_selections
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_solve_allocation_defs
(ANALYSIS_CYCLE_ID,
MEMBER,
MEMBER_ORDER,
RULE_NAME,
METHOD,
BASIS,
QUALIFIER,
EVALUATION_OPTION,
ROUND_DECIMALS,
ROUND_ENABLED,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
MEMBER,
MEMBER_ORDER,
RULE_NAME,
METHOD,
BASIS,
QUALIFIER,
EVALUATION_OPTION,
ROUND_DECIMALS,
ROUND_ENABLED,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_solve_allocation_defs
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
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,
PROPAGATED_FLAG,
SUM_SELECTION_PATH,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT target_ac_id_in,
MEMBER,
MEMBER_ORDER,
DIMENSION,
SUM_MEMBERS_NUMBER,
SUM_MEMBERS_FLAG,
EXCLUDE_FROM_SOLVE_FLAG,
FORCE_INPUT_FLAG,
SUM_SELECTION_NAME,
PROPAGATED_FLAG,
SUM_SELECTION_PATH,
fnd_global.USER_ID,
SYSDATE,
fnd_global.USER_ID,
SYSDATE,
fnd_global.LOGIN_ID
FROM zpb_line_dimensionality
WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
INSERT INTO zpb_solve_hier_order
(solve_hier_order_id,
analysis_cycle_id,
dimension,
hierarchy,
hierarchy_order,
first_last_flag,
object_version_number,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login
)
SELECT zpb_solve_hier_order_s.NEXTVAL,
target_ac_id_in,
dimension,
hierarchy,
hierarchy_order,
first_last_flag,
object_version_number,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.login_id
FROM zpb_solve_hier_order
WHERE analysis_cycle_id = source_ac_id_in;
SELECT *
FROM zpb_analysis_cycle_tasks
WHERE analysis_cycle_id = source_ac_id_in and
sequence >=
((SELECT min(sequence)
FROM zpb_analysis_cycle_tasks
WHERE analysis_cycle_id = target_ac_id_in and
status_code is null));
todelete_task_id zpb_analysis_cycle_tasks.task_id%TYPE;
SELECT task_id into todelete_task_id
FROM zpb_analysis_cycle_tasks
WHERE analysis_cycle_id = target_ac_id_in and
sequence = ac_task_rec.sequence;
todelete_task_id := null;
DELETE FROM zpb_task_parameters
WHERE task_id = todelete_task_id;
DELETE FROM zpb_analysis_cycle_tasks
WHERE task_id = todelete_task_id;
SELECT zpb_task_id_seq.NEXTVAL INTO target_task_id FROM DUAL;
ac_task_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
ac_task_rec.LAST_UPDATE_DATE := SYSDATE;
ac_task_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
INSERT INTO zpb_analysis_cycle_tasks(ANALYSIS_CYCLE_ID,
TASK_ID,
SEQUENCE,
TASK_NAME,
STATUS_CODE,
ITEM_TYPE,
WF_PROCESS_NAME,
ITEM_KEY,
START_DATE,
HIDE_SHOW,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OWNER_ID)
VALUES (ac_task_rec.ANALYSIS_CYCLE_ID,
ac_task_rec.TASK_ID,
ac_task_rec.SEQUENCE,
ac_task_rec.TASK_NAME,
ac_task_rec.STATUS_CODE,
ac_task_rec.ITEM_TYPE,
ac_task_rec.WF_PROCESS_NAME,
ac_task_rec.ITEM_KEY,
ac_task_rec.START_DATE,
ac_task_rec.HIDE_SHOW,
ac_task_rec.CREATION_DATE,
ac_task_rec.CREATED_BY,
ac_task_rec.LAST_UPDATED_BY,
ac_task_rec.LAST_UPDATE_DATE,
ac_task_rec.LAST_UPDATE_LOGIN,
ac_task_rec.OWNER_ID);
PROCEDURE updateHorizonParams(p_start_mem IN VARCHAR2
,p_end_mem IN VARCHAR2
,new_ac_id IN NUMBER) AS
BEGIN
IF (p_start_mem IS NOT NULL ) THEN
UPDATE zpb_ac_param_values SET value = p_start_mem WHERE
analysis_cycle_id = new_ac_id AND param_id =
( SELECT tag FROM fnd_lookup_values_vl WHERE lookup_type = 'ZPB_PARAMS' AND
lookup_code = 'CAL_HS_TIME_MEMBER');
UPDATE zpb_ac_param_values SET value = p_end_mem WHERE
analysis_cycle_id = new_ac_id AND param_id =
( SELECT tag FROM fnd_lookup_values_vl WHERE lookup_type = 'ZPB_PARAMS' AND
lookup_code = 'CAL_HE_TIME_MEMBER');
END updateHorizonParams;
PROCEDURE delete_ac (
ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
delete_tasks IN VARCHAR2 default FND_API.G_TRUE)
IS
CURSOR ac_task_cur IS
SELECT *
FROM zpb_analysis_cycle_tasks
WHERE analysis_cycle_id = ac_id_in;
delete_task_id zpb_analysis_cycle_tasks.task_id%TYPE;
SELECT *
INTO pet_rec
FROM zpb_cycle_relationships
WHERE published_ac_id = ac_id_in OR
editable_ac_id = ac_id_in OR
tmp_ac_id = ac_id_in;
UPDATE zpb_cycle_relationships
SET published_ac_id = NULL,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE relationship_id = pet_rec.relationship_id;
UPDATE zpb_cycle_relationships
SET editable_ac_id = NULL,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE relationship_id = pet_rec.relationship_id;
UPDATE zpb_cycle_relationships
SET tmp_ac_id = NULL,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE relationship_id = pet_rec.relationship_id;
DELETE FROM zpb_cycle_relationships
WHERE published_ac_id IS NULL AND
editable_ac_id IS NULL AND
tmp_ac_id IS NULL;
IF FND_API.To_Boolean(delete_tasks) THEN
FOR ac_task_rec IN ac_task_cur LOOP
DELETE FROM zpb_task_parameters
WHERE task_id = ac_task_rec.task_id;
DELETE FROM zpb_analysis_cycle_tasks
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_business_process_scope
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_copy_dim_members
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_measure_scope_exempt_users
WHERE BUSINESS_PROCESS_ENTITY_ID = ac_id_in
AND BUSINESS_PROCESS_ENTITY_TYPE = 'A';
DELETE FROM zpb_cycle_comments
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_cycle_model_dimensions
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_ac_param_values
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_cycle_datasets
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_analysis_cycles
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_solve_member_defs
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_data_initialization_defs
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_solve_input_levels
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_solve_output_levels
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_solve_allocation_defs
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_solve_output_selections
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_solve_input_selections
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_line_dimensionality
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_cycle_currencies
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_bp_external_users
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM ZPB_BP_VALIDATION_RESULTS
WHERE BUS_PROC_ID = ac_id_in;
DELETE FROM zpb_solve_hier_order
WHERE analysis_cycle_id = ac_id_in;
END delete_ac;
PROCEDURE delete_published_ac (
ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
prev_instance_options_in IN VARCHAR2,
curr_instance_options_in IN VARCHAR2)
IS
CURSOR previous_instance_cur IS
SELECT zaci.instance_ac_id
FROM zpb_analysis_cycle_instances zaci,
zpb_analysis_cycles zac
WHERE zaci.analysis_cycle_id = ac_id_in and
zac.analysis_cycle_id = zaci.analysis_cycle_id and
zaci.instance_ac_id <> zac.current_instance_id;
IF prev_instance_options_in = 'DELETE_PREVIOUS_INSTANCE' THEN
FOR instance_rec IN previous_instance_cur LOOP
DELETE FROM zpb_analysis_cycle_instances
WHERE instance_ac_id = instance_rec.instance_ac_id;
delete_ac(instance_rec.instance_ac_id);
IF curr_instance_options_in = 'DELETE_CURR_INSTANCE' THEN
SELECT current_instance_id
INTO curr_instance_ac_id
FROM zpb_analysis_cycles
WHERE analysis_cycle_id = ac_id_in;
DELETE FROM zpb_analysis_cycle_instances
WHERE instance_ac_id = curr_instance_ac_id;
delete_ac(curr_instance_ac_id);
* AC_ID because it has been deleted from the tables.
*/
UPDATE zpb_analysis_cycle_instances
SET analysis_cycle_id = NULL,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE analysis_cycle_id = ac_id_in;
delete_ac(ac_id_in);
END delete_published_ac;
SELECT editable_ac_id
INTO return_ac_id
FROM zpb_cycle_relationships
WHERE published_ac_id = published_ac_id_in;
delete_ac(editable_ac_id_in);
SELECT published_ac_id
INTO return_ac_id
FROM zpb_cycle_relationships
WHERE editable_ac_id = editable_ac_id_in;
last_updated_by_in IN zpb_analysis_cycles.last_updated_by%TYPE,
ac_business_area_in IN zpb_analysis_cycles.business_area_id%TYPE,
is_comments_copied IN VARCHAR2 default 'true',
is_analy_excep_copied IN VARCHAR2 default 'true',
editable_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
IS
new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO current_inst_id FROM DUAL;
SELECT zpb_relationship_id_seq.NEXTVAL INTO relationship_id FROM DUAL;
UPDATE zpb_analysis_cycles
SET name = editable_ac_name_in,
status_code = 'DISABLE_ASAP',
validate_status = 'INVALID',
locked_by = 1,
published_date = NULL,
published_by = NULL,
current_instance_id = current_inst_id,
owner_id = fnd_global.USER_ID,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE analysis_cycle_id = new_ac_id;
pet_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
pet_rec.LAST_UPDATE_DATE := SYSDATE;
pet_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
INSERT INTO zpb_cycle_relationships(RELATIONSHIP_ID,
PUBLISHED_AC_ID,
EDITABLE_AC_ID,
TMP_AC_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES (pet_rec.RELATIONSHIP_ID,
pet_rec.PUBLISHED_AC_ID,
pet_rec.EDITABLE_AC_ID,
pet_rec.TMP_AC_ID,
pet_rec.LAST_UPDATE_LOGIN,
pet_rec.LAST_UPDATE_DATE,
pet_rec.LAST_UPDATED_BY,
pet_rec.CREATION_DATE,
pet_rec.CREATED_BY);
last_updated_by_in IN zpb_analysis_cycles.last_updated_by%TYPE,
editable_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
IS
new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
SELECT *
INTO pet_row_rec
FROM zpb_cycle_relationships
WHERE published_ac_id = published_ac_id_in;
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
UPDATE zpb_analysis_cycles
SET name = editable_ac_name_in,
status_code = 'DISABLE_ASAP',
published_date = NULL,
published_by = NULL,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE analysis_cycle_id = new_ac_id;
UPDATE zpb_cycle_relationships
SET editable_ac_id = new_ac_id,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE published_ac_id = published_ac_id_in;
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO current_inst_id FROM DUAL;
SELECT zpb_relationship_id_seq.NEXTVAL INTO relationship_id FROM DUAL;
ac_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
ac_rec.LAST_UPDATE_DATE := SYSDATE;
ac_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
INSERT INTO zpb_analysis_cycles(ANALYSIS_CYCLE_ID,
STATUS_CODE,
NAME,
DESCRIPTION,
LOCKED_BY,
VALIDATE_STATUS,
CURRENT_INSTANCE_ID,
PUBLISHED_DATE,
PUBLISHED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PREV_STATUS_CODE,
LAST_UPDATE_LOGIN,
BUSINESS_AREA_ID,
OWNER_ID)
VALUES (ac_rec.ANALYSIS_CYCLE_ID,
ac_rec.STATUS_CODE,
ac_rec.NAME,
ac_rec.DESCRIPTION,
ac_rec.LOCKED_BY,
ac_rec.VALIDATE_STATUS,
ac_rec.CURRENT_INSTANCE_ID,
ac_rec.PUBLISHED_DATE,
ac_rec.PUBLISHED_BY,
ac_rec.LAST_UPDATE_DATE,
ac_rec.LAST_UPDATED_BY,
ac_rec.CREATION_DATE,
ac_rec.CREATED_BY,
ac_rec.PREV_STATUS_CODE,
ac_rec.LAST_UPDATE_LOGIN,
ac_rec.BUSINESS_AREA_ID,
ac_rec.OWNER_ID);
pet_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
pet_rec.LAST_UPDATE_DATE := SYSDATE;
pet_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
INSERT INTO zpb_cycle_relationships(RELATIONSHIP_ID,
PUBLISHED_AC_ID,
EDITABLE_AC_ID,
TMP_AC_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES (pet_rec.RELATIONSHIP_ID,
pet_rec.PUBLISHED_AC_ID,
pet_rec.EDITABLE_AC_ID,
pet_rec.TMP_AC_ID,
pet_rec.LAST_UPDATE_LOGIN,
pet_rec.LAST_UPDATE_DATE,
pet_rec.LAST_UPDATED_BY,
pet_rec.CREATION_DATE,
pet_rec.CREATED_BY);
select to_number(substr(instance_description,length(instance_description) -2))
from zpb_analysis_cycle_instances
where instance_ac_id = (select max(instance_ac_id)
from zpb_analysis_cycle_instances aci,
zpb_analysis_cycles pubac,
zpb_analysis_cycles runac
where pubac.analysis_cycle_id = ac_id_in
and pubac.current_instance_id =
runac.current_instance_id
and runac.analysis_cycle_id = aci.instance_ac_id);
SELECT VALUE FROM ZPB_AC_PARAM_VALUES
WHERE ANALYSIS_CYCLE_ID = ac_id_in AND PARAM_ID = l_appview_param_id ;
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
SELECT *
INTO ac_rec
FROM zpb_analysis_cycles
WHERE analysis_cycle_id = new_ac_id;
SELECT tag INTO l_appview_param_id
FROM fnd_lookup_values_vl
WHERE LOOKUP_CODE = 'APPEND_VIEW'
and LOOKUP_TYPE = 'ZPB_PARAMS';
UPDATE zpb_analysis_cycles
SET status_code = 'PUBLISHED',
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE analysis_cycle_id = new_ac_id;
instance_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
instance_rec.LAST_UPDATE_DATE := SYSDATE;
instance_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
INSERT INTO zpb_analysis_cycle_instances(ANALYSIS_CYCLE_ID,
INSTANCE_AC_ID,
INSTANCE_DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
STATUS_CODE)
VALUES (instance_rec.ANALYSIS_CYCLE_ID,
instance_rec.INSTANCE_AC_ID,
instance_rec.INSTANCE_DESCRIPTION,
instance_rec.CREATION_DATE,
instance_rec.CREATED_BY,
instance_rec.LAST_UPDATED_BY,
instance_rec.LAST_UPDATE_DATE,
instance_rec.LAST_UPDATE_LOGIN,
instance_rec.STATUS_CODE);
SELECT *
INTO pet_row_rec
FROM zpb_cycle_relationships
WHERE editable_ac_id = editable_ac_id_in;
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
delete_ac(pet_row_rec.tmp_ac_id);
UPDATE zpb_cycle_relationships
SET tmp_ac_id = new_ac_id,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE editable_ac_id = editable_ac_id_in;
PROCEDURE delete_tmp_ac (
tmp_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
IS
editable_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
SELECT editable_ac_id
INTO editable_ac_id
FROM zpb_cycle_relationships
WHERE tmp_ac_id = tmp_ac_id_in;
UPDATE zpb_analysis_cycles
SET
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE analysis_cycle_id = editable_ac_id;
delete_ac(tmp_ac_id_in);
END delete_tmp_ac;
SELECT locked_by
INTO locked_by_id
FROM zpb_analysis_cycles
WHERE analysis_cycle_id = editable_ac_id_in;
UPDATE zpb_analysis_cycles
SET locked_by = user_id_in,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE analysis_cycle_id = editable_ac_id_in;
PROCEDURE mark_cycle_for_delete (
ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
prev_instance_options_in IN VARCHAR2,
curr_instance_options_in IN VARCHAR2)
IS
CURSOR previous_instance_cur IS
SELECT aci.instance_ac_id, ac.current_instance_id
from zpb_analysis_cycle_instances aci,zpb_analysis_cycles ac,
zpb_analysis_cycles currinst
where currinst.ANALYSIS_CYCLE_ID = ac_id_in
and currinst.current_instance_id=ac.current_instance_id
and ac.analysis_cycle_id=aci.instance_ac_id
and ac.status_code in ('COMPLETE','ERROR','COMPLETE_WITH_WARNING');
select /*+ FIRST_ROWS */ item_key
from WF_ITEM_ATTRIBUTE_VALUES
where item_type = 'ZPBSCHED'
and name = 'ACID'
and number_value = ac_id_in;
select published_by, BUSINESS_AREA_ID into ownerid, l_business_area_id
from zpb_analysis_cycles
where analysis_cycle_id = ac_id_in;
zpb_wf_ntf.notify_on_delete(ac_id_in, 'ACID');
SELECT tmp_ac_id into tmp_ac_id
FROM zpb_cycle_relationships
WHERE published_ac_id = ac_id_in;
SELECT editable_ac_id into edit_ac_id
FROM zpb_cycle_relationships
WHERE published_ac_id = ac_id_in;
* If the cycle is an editable copy, then also update
* the ZPB_CYCLE_RELATIONSHIPS table
*/
IF cycle_type = 'EDITABLE_COPY' THEN
SELECT tmp_ac_id into tmp_ac_id
FROM zpb_cycle_relationships
WHERE editable_ac_id = ac_id_in;
UPDATE zpb_cycle_relationships
set editable_ac_id = null,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
where editable_ac_id = ac_id_in;
SELECT tmp_ac_id into tmp_ac_id
FROM zpb_cycle_relationships
WHERE editable_ac_id = ac_id_in;
UPDATE zpb_analysis_cycles
SET status_code='MARKED_FOR_DELETION',
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE analysis_cycle_id in (ac_id_in, tmp_ac_id, edit_ac_id);
DELETE zpb_dc_objects
WHERE delete_instance_measures_flag = 'D' and
analysis_cycle_id = ac_id_in;
zpb_dc_objects_pvt.delete_template(
1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
l_return_status, l_msg_count, l_msg_data, ac_id_in);
zpb_dc_objects_pvt.delete_template(
1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
l_return_status, l_msg_count, l_msg_data, tmp_ac_id);
zpb_dc_objects_pvt.delete_template(
1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
l_return_status, l_msg_count, l_msg_data, edit_ac_id);
IF prev_instance_options_in = 'DELETE_PREVIOUS_INSTANCE' THEN
FOR instance_rec IN previous_instance_cur LOOP
UPDATE zpb_analysis_cycles
SET status_code='MARKED_FOR_DELETION',
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE analysis_cycle_id = instance_rec.instance_ac_id;
zpb_dc_objects_pvt.delete_template(
1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
l_return_status, l_msg_count, l_msg_data, instance_rec.instance_ac_id);
IF curr_instance_options_in = 'DELETE_CURR_INSTANCE' THEN
-- ABUDNIK B4558985 09Oct2005
CLEAN_ACTIVE_INSTANCE(ac_id_in);
UPDATE zpb_analysis_cycles
SET status_code = 'MARKED_FOR_DELETION',
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE analysis_cycle_id in
(select instance_ac_id
from zpb_analysis_cycle_instances aci,zpb_analysis_cycles ac,
zpb_analysis_cycles currinst
where currinst.ANALYSIS_CYCLE_ID =ac_id_in
and currinst.current_instance_id=ac.current_instance_id
and ac.analysis_cycle_id=aci.instance_ac_id
and ac.status_code NOT IN ('COMPLETE','ERROR','COMPLETE_WITH_WARNING'));
ZPB_WF.DeleteCurrInstMeas(ac_id_in, ownerid);
zpb_dc_objects_pvt.delete_template(
1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
l_return_status, l_msg_count, l_msg_data, ac_id_in);
END mark_cycle_for_delete;
PROCEDURE update_solve_definition_flag(
editable_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
published_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
IS
cursor input_levels_cur(published_ac_id_in IN integer ,
editable_ac_id_in in integer) IS
select edt.member, edt.dimension, edt.input_level
from zpb_solve_input_levels edt
where edt.analysis_cycle_id = editable_ac_id_in
MINUS
select pub.member, pub.dimension, pub.input_level
from zpb_solve_input_levels pub
where pub.analysis_cycle_id= published_ac_id_in;
update zpb_solve_member_defs edt
set current_modified = 'Y'
where edt.analysis_cycle_id = editable_ac_id_in
and edt.source_type = 1200
and 0 <> (select dbms_lob.compare(edt.model_equation, pub.model_equation)
from zpb_solve_member_defs pub
where pub.analysis_cycle_id = published_ac_id_in
and pub.member = edt.member
and pub.source_type = 1200);
update zpb_solve_member_defs
set current_modified = 'Y'
where analysis_cycle_id = editable_ac_id_in
and member = each.member;
end update_solve_definition_flag;
/* select the instances of this BP that are still active
these will be updated with the new definition below */
CURSOR instance_cur IS
SELECT zaci.instance_ac_id, zaci.analysis_cycle_id
FROM ZPB_ANALYSIS_CYCLE_INSTANCES zaci,
ZPB_ANALYSIS_CYCLES zac
WHERE zaci.analysis_cycle_id = editable_ac_id_in and
zaci.instance_ac_id = zac.analysis_cycle_id and
zac.status_code not in('COMPLETE', 'COMPLETE_WITH_WARNING', 'DISABLE_ASAP', 'ERROR', 'MARKED_FOR_DELETION');
SELECT *
INTO pet_row_rec
FROM zpb_cycle_relationships
WHERE editable_ac_id = editable_ac_id_in;
select STATUS_CODE into published_before_status
from ZPB_ANALYSIS_CYCLES
where analysis_cycle_id=pet_row_rec.published_ac_id;
if publish_options_in = 'UPDATE_FOR_CURRENT' then
enable_option:= 'ENABLE_TASK';
if publish_options_in = 'UPDATE_FOR_FUTURE' then
enable_option:= 'ENABLE_NEXT';
IF publish_options_in = 'UPDATE_FOR_CURRENT' THEN
update_solve_definition_flag(editable_ac_id_in, pet_row_rec.published_ac_id);
Update ZPB_ANALYSIS_CYCLES
set STATUS_CODE = 'ENABLE_TASK_OLD'
where ANALYSIS_CYCLE_ID = old_published_ac_id
and STATUS_CODE = 'ENABLE_TASK';
/* UPDATE ZPB_ANALYSIS_CYCLE_INSTANCES
SET Analysis_Cycle_Id = editable_ac_id_in
WHERE Analysis_Cycle_Id = old_published_ac_id; */
UPDATE zpb_cycle_relationships
SET published_ac_id = editable_ac_id_in,
editable_ac_id = null,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE editable_ac_id = editable_ac_id_in;
UPDATE zpb_analysis_cycles
SET published_by = published_by_in,
published_date = sysdate,
status_code = 'ENABLE_TASK',
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE analysis_cycle_id = editable_ac_id_in;
IF publish_options_in = 'UPDATE_FOR_CURRENT' THEN
FOR instance_rec IN instance_cur LOOP
SELECT *
INTO ac_rec
FROM zpb_analysis_cycles
WHERE analysis_cycle_id = instance_rec.instance_ac_id;
delete_ac(instance_rec.instance_ac_id, FND_API.G_FALSE);
UPDATE zpb_analysis_cycles
SET LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
STATUS_CODE = ac_rec.status_code,
PREV_STATUS_CODE = ac_rec.prev_status_code,
NAME = ac_rec.name
WHERE analysis_cycle_id = instance_rec.instance_ac_id;
last_updated_by_in IN zpb_analysis_cycles.last_updated_by%TYPE,
lock_val_in IN zpb_analysis_cycles.locked_by%TYPE,
lock_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
editable_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
IS
new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
CURSOR lock_cursor is SELECT locked_by FROM zpb_analysis_cycles
where analysis_cycle_id = lock_ac_id_in FOR UPDATE;
SELECT *
INTO pet_row_rec
FROM zpb_cycle_relationships
WHERE tmp_ac_id = tmp_ac_id_in;
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
delete_ac(pet_row_rec.editable_ac_id);
UPDATE zpb_cycle_relationships
SET editable_ac_id = new_ac_id,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
WHERE tmp_ac_id = tmp_ac_id_in;
delete_ac(tmp_ac_id_in);
UPDATE zpb_analysis_cycles
SET locked_by = nvl(locked_by,0)+1,
LAST_UPDATED_BY = fnd_global.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
where analysis_cycle_id = new_ac_id;
SELECT *
INTO pet_row_rec
FROM zpb_cycle_relationships
WHERE tmp_ac_id = ac_id_in or
published_ac_id = ac_id_in or
editable_ac_id = ac_id_in;
SELECT status_code
INTO cycle_status_out
FROM zpb_analysis_cycles
WHERE analysis_cycle_id=ac_id_in;
SELECT locked_by
INTO lock_value_out
FROM zpb_analysis_cycles
WHERE analysis_cycle_id=ac_id_in;
SELECT 1 FROM zpb_cycle_relationships
WHERE TMP_AC_ID = cp_tmp_ac_id AND published_ac_id IS NOT NULL;
select 1 from zpb_analysis_cycles where
lower(name) = lower(cycle_name)
and status_code <> 'MARKED_FOR_DELETION'
and business_area_id = p_bus_area_id;
ac_param_rec.LAST_UPDATED_BY := p_apps_user_id;
ac_param_rec.LAST_UPDATE_DATE := SYSDATE;
ac_param_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
INSERT INTO zpb_ac_param_values(ANALYSIS_CYCLE_ID,
PARAM_ID,
VALUE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES (ac_param_rec.ANALYSIS_CYCLE_ID,
ac_param_rec.PARAM_ID,
ac_param_rec.VALUE,
ac_param_rec.LAST_UPDATE_LOGIN,
ac_param_rec.LAST_UPDATE_DATE,
ac_param_rec.LAST_UPDATED_BY,
ac_param_rec.CREATION_DATE,
ac_param_rec.CREATED_BY);
md_rec.LAST_UPDATED_BY := p_apps_user_id;
md_rec.LAST_UPDATE_DATE := SYSDATE;
md_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
INSERT INTO zpb_cycle_model_dimensions(ANALYSIS_CYCLE_ID,
DIMENSION_NAME,
QUERY_OBJECT_NAME,
QUERY_OBJECT_PATH,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DATASET_DIMENSION_FLAG,
REMOVE_DIMENSION_FLAG,
SUM_MEMBERS_NUMBER,
SUM_SELECTION_NAME,
SUM_SELECTION_PATH)
VALUES (md_rec.ANALYSIS_CYCLE_ID,
md_rec.DIMENSION_NAME,
md_rec.QUERY_OBJECT_NAME,
md_rec.QUERY_OBJECT_PATH,
md_rec.LAST_UPDATE_LOGIN,
md_rec.LAST_UPDATE_DATE,
md_rec.LAST_UPDATED_BY,
md_rec.CREATION_DATE,
md_rec.CREATED_BY,
md_rec.DATASET_DIMENSION_FLAG,
md_rec.REMOVE_DIMENSION_FLAG,
md_rec.SUM_MEMBERS_NUMBER,
md_rec.SUM_SELECTION_NAME,
md_rec.SUM_SELECTION_PATH);
ds_rec.LAST_UPDATED_BY := p_apps_user_id ;
ds_rec.LAST_UPDATE_DATE := SYSDATE;
ds_rec.LAST_UPDATE_LOGIN := p_apps_user_id ;
INSERT INTO zpb_cycle_datasets(ANALYSIS_CYCLE_ID,
DATASET_CODE,
ORDER_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES (ds_rec.ANALYSIS_CYCLE_ID,
ds_rec.DATASET_CODE,
ds_rec.ORDER_ID,
ds_rec.LAST_UPDATE_LOGIN,
ds_rec.LAST_UPDATE_DATE,
ds_rec.LAST_UPDATED_BY,
ds_rec.CREATION_DATE,
ds_rec.CREATED_BY);
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO current_inst_id FROM DUAL;
SELECT zpb_relationship_id_seq.NEXTVAL INTO relationship_id FROM DUAL;
ac_rec.LAST_UPDATED_BY := p_apps_user_id;
ac_rec.LAST_UPDATE_DATE := SYSDATE;
ac_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
INSERT INTO zpb_analysis_cycles(ANALYSIS_CYCLE_ID,
STATUS_CODE,
NAME,
DESCRIPTION,
LOCKED_BY,
VALIDATE_STATUS,
CURRENT_INSTANCE_ID,
PUBLISHED_DATE,
PUBLISHED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PREV_STATUS_CODE,
LAST_UPDATE_LOGIN,
BUSINESS_AREA_ID,
OWNER_ID)
VALUES (ac_rec.ANALYSIS_CYCLE_ID,
ac_rec.STATUS_CODE,
ac_rec.NAME,
ac_rec.DESCRIPTION,
ac_rec.LOCKED_BY,
ac_rec.VALIDATE_STATUS,
ac_rec.CURRENT_INSTANCE_ID,
ac_rec.PUBLISHED_DATE,
ac_rec.PUBLISHED_BY,
ac_rec.LAST_UPDATE_DATE,
ac_rec.LAST_UPDATED_BY,
ac_rec.CREATION_DATE,
ac_rec.CREATED_BY,
ac_rec.PREV_STATUS_CODE,
ac_rec.LAST_UPDATE_LOGIN,
ac_rec.BUSINESS_AREA_ID,
ac_rec.OWNER_ID);
pet_rec.LAST_UPDATED_BY := p_apps_user_id;
pet_rec.LAST_UPDATE_DATE := SYSDATE;
pet_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
INSERT INTO zpb_cycle_relationships(RELATIONSHIP_ID,
PUBLISHED_AC_ID,
EDITABLE_AC_ID,
TMP_AC_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES (pet_rec.RELATIONSHIP_ID,
pet_rec.PUBLISHED_AC_ID,
pet_rec.EDITABLE_AC_ID,
pet_rec.TMP_AC_ID,
pet_rec.LAST_UPDATE_LOGIN,
pet_rec.LAST_UPDATE_DATE,
pet_rec.LAST_UPDATED_BY,
pet_rec.CREATION_DATE,
pet_rec.CREATED_BY);
SELECT zpb_task_param_id_seq.NEXTVAL INTO task_param_id FROM DUAL;
ac_task_param_rec.LAST_UPDATED_BY := p_apps_user_id;
ac_task_param_rec.LAST_UPDATE_DATE := SYSDATE;
ac_task_param_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
INSERT INTO zpb_task_parameters(NAME,
TASK_ID,
VALUE,
PARAM_ID,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES (ac_task_param_rec.NAME,
ac_task_param_rec.TASK_ID,
ac_task_param_rec.VALUE,
ac_task_param_rec.PARAM_ID,
ac_task_param_rec.LAST_UPDATE_LOGIN,
ac_task_param_rec.LAST_UPDATE_DATE,
ac_task_param_rec.LAST_UPDATED_BY,
ac_task_param_rec.CREATION_DATE,
ac_task_param_rec.CREATED_BY);
SELECT zpb_task_id_seq.NEXTVAL INTO load_task_id from dual;
ac_task_rec.LAST_UPDATED_BY := p_apps_user_id;
ac_task_rec.LAST_UPDATE_DATE := SYSDATE;
ac_task_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
INSERT INTO zpb_analysis_cycle_tasks(ANALYSIS_CYCLE_ID,
TASK_ID,
SEQUENCE,
TASK_NAME,
STATUS_CODE,
ITEM_TYPE,
WF_PROCESS_NAME,
ITEM_KEY,
START_DATE,
HIDE_SHOW,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OWNER_ID)
VALUES (ac_task_rec.ANALYSIS_CYCLE_ID,
ac_task_rec.TASK_ID,
ac_task_rec.SEQUENCE,
ac_task_rec.TASK_NAME,
ac_task_rec.STATUS_CODE,
ac_task_rec.ITEM_TYPE,
ac_task_rec.WF_PROCESS_NAME,
ac_task_rec.ITEM_KEY,
ac_task_rec.START_DATE,
ac_task_rec.HIDE_SHOW,
ac_task_rec.CREATION_DATE,
ac_task_rec.CREATED_BY,
ac_task_rec.LAST_UPDATED_BY,
ac_task_rec.LAST_UPDATE_DATE,
ac_task_rec.LAST_UPDATE_LOGIN,
ac_task_rec.OWNER_ID);
create_task_parameters(load_task_id, 'DATA_SELECTION_TYPE', 'ALL_LINE_ITEMS_SELECTION_TYPE',p_apps_user_id);
SELECT zpb_task_id_seq.NEXTVAL INTO curinst_task_id from dual;
ac_task_rec.LAST_UPDATED_BY := p_apps_user_id;
ac_task_rec.LAST_UPDATE_DATE := SYSDATE;
ac_task_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
INSERT INTO zpb_analysis_cycle_tasks(ANALYSIS_CYCLE_ID,
TASK_ID,
SEQUENCE,
TASK_NAME,
STATUS_CODE,
ITEM_TYPE,
WF_PROCESS_NAME,
ITEM_KEY,
START_DATE,
HIDE_SHOW,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OWNER_ID)
VALUES (ac_task_rec.ANALYSIS_CYCLE_ID,
ac_task_rec.TASK_ID,
ac_task_rec.SEQUENCE,
ac_task_rec.TASK_NAME,
ac_task_rec.STATUS_CODE,
ac_task_rec.ITEM_TYPE,
ac_task_rec.WF_PROCESS_NAME,
ac_task_rec.ITEM_KEY,
ac_task_rec.START_DATE,
ac_task_rec.HIDE_SHOW,
ac_task_rec.CREATION_DATE,
ac_task_rec.CREATED_BY,
ac_task_rec.LAST_UPDATED_BY,
ac_task_rec.LAST_UPDATE_DATE,
ac_task_rec.LAST_UPDATE_LOGIN,
ac_task_rec.OWNER_ID);
SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
update zpb_cycle_model_dimensions set last_updated_by = p_apps_user_id,
created_by = p_apps_user_id,
last_update_login = p_apps_user_id
where analysis_cycle_id = new_ac_id;
update zpb_analysis_cycles set status_code = 'PUBLISHED',
validate_status = 'VALID',
last_updated_by = p_apps_user_id,
created_by = p_apps_user_id,
last_update_login = p_apps_user_id
where analysis_cycle_id = new_ac_id;
instance_rec.LAST_UPDATED_BY := p_apps_user_id;
instance_rec.LAST_UPDATE_DATE := SYSDATE;
instance_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
INSERT INTO zpb_analysis_cycle_instances(ANALYSIS_CYCLE_ID,
INSTANCE_AC_ID,
INSTANCE_DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
STATUS_CODE)
VALUES (instance_rec.ANALYSIS_CYCLE_ID,
instance_rec.INSTANCE_AC_ID,
instance_rec.INSTANCE_DESCRIPTION,
instance_rec.CREATION_DATE,
instance_rec.CREATED_BY,
instance_rec.LAST_UPDATED_BY,
instance_rec.LAST_UPDATE_DATE,
instance_rec.LAST_UPDATE_LOGIN,
instance_rec.STATUS_CODE);
SELECT DISTINCT b.dimension,
b.hierarchy
FROM zpb_solve_output_selections b,
zpb_lab_hierarchies_v lab
WHERE b.analysis_cycle_id = p_analysis_cycle_id
AND b.hierarchy = lab.object_aw_name
AND b.dimension = lab.dimension
AND EXISTS
(SELECT a.dimension,
a.member,
COUNT(a.hierarchy)
FROM zpb_solve_output_selections a
WHERE a.analysis_cycle_id = p_analysis_cycle_id
AND a.dimension = b.dimension
GROUP BY a.dimension, a.member
HAVING COUNT(a.hierarchy) > l_hier_count )
ORDER BY b.dimension, b.hierarchy;
SELECT 1 hier_exists
FROM zpb_solve_hier_order
WHERE analysis_cycle_id = p_analysis_cycle_id
AND dimension = l_dimension
AND hierarchy = l_hierarchy;
SELECT NVL(MAX(hierarchy_order),-1) max_order
FROM zpb_solve_hier_order
WHERE analysis_cycle_id = p_analysis_cycle_id
AND dimension = l_dimension;
SELECT dimension,
MAX(hierarchy_order) max,
MIN(hierarchy_order) min,
COUNT(hierarchy) count
FROM zpb_solve_hier_order
WHERE analysis_cycle_id = p_analysis_cycle_id
GROUP BY dimension;
UPDATE zpb_solve_hier_order
SET first_last_flag = l_first_last_flag
WHERE analysis_cycle_id = p_analysis_cycle_id;
SELECT zpb_solve_hier_order_s.nextval INTO l_solve_hier_order_id
FROM dual;
INSERT INTO ZPB_SOLVE_HIER_ORDER
(SOLVE_HIER_ORDER_ID
,ANALYSIS_CYCLE_ID
,DIMENSION
,HIERARCHY
,HIERARCHY_ORDER
,FIRST_LAST_FLAG
,OBJECT_VERSION_NUMBER
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY)
VALUES
(l_solve_hier_order_id
,p_analysis_cycle_id
,l_dimension
,l_hierarchy
,l_order_num
,l_first_last_flag
,l_object_version_number
,l_login_id
,sysdate
,l_user_id
,sysdate
,l_user_id);
DELETE FROM zpb_solve_hier_order
WHERE analysis_cycle_id = p_analysis_cycle_id
AND hierarchy NOT IN
(SELECT b.hierarchy
FROM zpb_solve_output_selections b,
zpb_lab_hierarchies_v lab
WHERE b.analysis_cycle_id = p_analysis_cycle_id
AND b.hierarchy = lab.object_aw_name
AND b.dimension = lab.dimension
AND EXISTS
(SELECT a.dimension,
a.member,
COUNT(a.hierarchy)
FROM zpb_solve_output_selections a
WHERE a.analysis_cycle_id = p_analysis_cycle_id
AND a.dimension = b.dimension
GROUP BY a.dimension, a.member
HAVING COUNT(a.hierarchy) > l_hier_count
)
);
UPDATE zpb_solve_hier_order
SET first_last_flag = l_first_flag
WHERE analysis_cycle_id = p_analysis_cycle_id
AND dimension = l_final_rec.dimension
AND hierarchy_order = l_final_rec.min;
UPDATE zpb_solve_hier_order
SET first_last_flag = l_last_flag
WHERE analysis_cycle_id = p_analysis_cycle_id
AND dimension = l_final_rec.dimension
AND hierarchy_order = l_final_rec.max;
SELECT value
INTO l_param_value
FROM ZPB_AC_PARAM_VALUES
WHERE analysis_cycle_id = p_ac_id_in
AND param_id =
(SELECT tag
FROM fnd_lookup_values_vl
WHERE LOOKUP_TYPE = 'ZPB_PARAMS'
AND LOOKUP_CODE = 'APPEND_VIEW');
SELECT CURRENT_INSTANCE_ID
INTO x_vm_instance_id
FROM ZPB_ANALYSIS_CYCLES
WHERE ANALYSIS_CYCLE_ID = p_ac_id_in;