[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
C.FOLDER_ID,
C.OBJECT_ID,
D.OBJECT_DEFINITION_ID
INTO
pv_folder_id,
pv_cal_rule_obj_id,
pv_cal_rule_obj_def_id
FROM
FEM_OBJECT_DEFINITION_B D,
FEM_OBJECT_CATALOG_B C
WHERE
D.OBJECT_DEFINITION_ID = p_cal_rule_obj_def_id AND
C.OBJECT_ID = D.OBJECT_ID AND
C.OBJECT_TYPE_CODE='OGL_INTG_CAL_RULE';
SELECT MIN(P.PERIOD_NUM), MAX(P.PERIOD_NUM)
INTO v_period_num_min, v_period_num_max
FROM GL_PERIODS P
WHERE P.PERIOD_SET_NAME = pv_period_set_name
AND P.PERIOD_TYPE = pv_period_type
AND P.PERIOD_YEAR = pv_period_year;
SELECT
'OGL_' || T.PERIOD_TYPE,
v_ogl_product_name || ' ' || T.USER_PERIOD_TYPE,
v_ogl_product_name || ' ' || T.DESCRIPTION,
T.NUMBER_PER_FISCAL_YEAR,
T.USER_PERIOD_TYPE
INTO
pv_ogl_period_type_code,
pv_ogl_period_type_name,
pv_ogl_period_type_desc,
pv_ogl_number_per_fiscal_year,
pv_period_type_name
FROM
GL_PERIOD_TYPES T
WHERE
T.PERIOD_TYPE = pv_period_type;
SELECT COUNT(P.PERIOD_NUM) INTO v_ogl_number_per_fiscal_year
FROM GL_PERIODS P
WHERE P.PERIOD_SET_NAME = pv_period_set_name
AND P.PERIOD_TYPE = pv_period_type
AND P.PERIOD_YEAR = pv_period_year;
SELECT
C.OBJECT_ID,
M.CAL_PER_HIER_OBJ_DEF_ID,
M.DIMENSION_GROUP_ID,
M.CALENDAR_ID,
M.EFFECTIVE_PERIOD_NUM_MIN,
M.EFFECTIVE_PERIOD_NUM_MAX
INTO
pv_cal_per_hier_obj_id,
pv_cal_per_hier_obj_def_id,
pv_dimension_grp_id_period,
pv_calendar_id,
pv_effective_period_num_min,
pv_effective_period_num_max
FROM
FEM_INTG_CALENDAR_MAP M,
FEM_OBJECT_DEFINITION_B D,
FEM_OBJECT_CATALOG_B C
WHERE
M.PERIOD_SET_NAME = p_period_set_name AND
M.PERIOD_TYPE = p_period_type AND
D.OBJECT_DEFINITION_ID = M.CAL_PER_HIER_OBJ_DEF_ID AND
C.OBJECT_ID = D.OBJECT_ID;
SELECT CALENDAR_ID
INTO pv_calendar_id
FROM FEM_CALENDARS_B
WHERE CALENDAR_DISPLAY_CODE = p_period_set_name;
SELECT DIMENSION_GROUP_ID
INTO pv_dimension_grp_id_period
FROM FEM_DIMENSION_GRPS_B
WHERE TIME_GROUP_TYPE_CODE = pv_ogl_period_type_code;
SELECT MIN(P.PERIOD_NUM)
INTO v_period_num_min
FROM GL_PERIODS P
WHERE P.PERIOD_SET_NAME = pv_period_set_name
AND P.PERIOD_TYPE = pv_period_type
AND P.PERIOD_YEAR = TRUNC(v_effective_period_num_min/10000);
SELECT MAX(P.PERIOD_NUM)
INTO v_period_num_max
FROM GL_PERIODS P
WHERE P.PERIOD_SET_NAME = pv_period_set_name
AND P.PERIOD_TYPE = pv_period_type
AND P.PERIOD_YEAR = TRUNC(pv_effective_period_num_high/10000);
SELECT MIN(P.PERIOD_NUM)
INTO v_period_num_min
FROM GL_PERIODS P
WHERE P.PERIOD_SET_NAME = pv_period_set_name
AND P.PERIOD_TYPE = pv_period_type
AND P.PERIOD_YEAR = TRUNC(pv_effective_period_num_low/10000);
SELECT MAX(P.PERIOD_NUM)
INTO v_period_num_max
FROM GL_PERIODS P
WHERE P.PERIOD_SET_NAME = pv_period_set_name
AND P.PERIOD_TYPE = pv_period_type
AND P.PERIOD_YEAR = TRUNC(v_effective_period_num_max/10000);
SELECT CALENDAR_ID
INTO v_calendar_id
FROM FEM_CALENDARS_B
WHERE CALENDAR_DISPLAY_CODE = pv_period_set_name;
SELECT G.DIMENSION_GROUP_ID
INTO v_dimension_grp_id_period
FROM
FEM_DIMENSION_GRPS_B G
WHERE
G.DIMENSION_GROUP_DISPLAY_CODE = pv_ogl_period_type_code;
SELECT
h.HIERARCHY_OBJ_ID
INTO
v_cal_per_hier_obj_id
FROM
FEM_HIERARCHIES h,
fem_object_catalog_b o
WHERE
h.CALENDAR_ID = NVL(v_calendar_id, -1)
and h.PERIOD_TYPE = pv_period_type
and h.personal_flag = 'N'
and h.hierarchy_usage_code = 'STANDARD'
and h.HIERARCHY_OBJ_ID=o.OBJECT_ID
and o.folder_id=pv_folder_id ;
DELETE FROM FEM_INTG_CALENDAR_MAP
WHERE CAL_PER_HIER_OBJ_DEF_ID = pv_cal_per_hier_obj_def_id;
SELECT DIMENSION_ID
INTO pv_ledger_dim_id
FROM FEM_DIMENSIONS_B
WHERE DIMENSION_VARCHAR_LABEL = 'LEDGER';
SELECT DIMENSION_ID
INTO pv_calendar_dim_id
FROM FEM_DIMENSIONS_B
WHERE DIMENSION_VARCHAR_LABEL = 'CALENDAR';
SELECT DIMENSION_ID
INTO pv_cal_period_dim_id
FROM FEM_DIMENSIONS_B
WHERE DIMENSION_VARCHAR_LABEL = 'CAL_PERIOD';
SELECT DIMENSION_ID
INTO pv_time_group_type_dim_id
FROM FEM_DIMENSIONS_B
WHERE DIMENSION_VARCHAR_LABEL = 'TIME_GROUP_TYPE';
SELECT DIMENSION_GROUP_ID
INTO pv_dimension_grp_id_year
FROM FEM_DIMENSION_GRPS_B
WHERE DIMENSION_GROUP_DISPLAY_CODE = 'Year';
SELECT DIMENSION_GROUP_ID
INTO pv_dimension_grp_id_quarter
FROM FEM_DIMENSION_GRPS_B
WHERE DIMENSION_GROUP_DISPLAY_CODE = 'Quarter';
SELECT SOURCE_SYSTEM_CODE
INTO pv_source_system_code
FROM FEM_SOURCE_SYSTEMS_B
WHERE SOURCE_SYSTEM_DISPLAY_CODE = 'OGL';
SELECT TIME_GROUP_TYPE_CODE
INTO v_time_group_type_code
FROM FEM_TIME_GROUP_TYPES_B
WHERE TIME_GROUP_TYPE_CODE = pv_ogl_period_type_code;
INSERT INTO FEM_INTG_CALENDAR_MAP(
PERIOD_SET_NAME,
PERIOD_TYPE,
CAL_PER_HIER_OBJ_DEF_ID,
DIMENSION_GROUP_ID,
CALENDAR_ID,
EFFECTIVE_PERIOD_NUM_MIN,
EFFECTIVE_PERIOD_NUM_MAX,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES (
pv_period_set_name,
pv_period_type,
pv_cal_per_hier_obj_def_id,
pv_dimension_grp_id_period,
pv_calendar_id,
NULL,
NULL,
SYSDATE,
pv_user_id,
SYSDATE,
pv_user_id,
pv_login_id
);
UPDATE FEM_LEDGERS_ATTR
SET DIM_ATTRIBUTE_NUMERIC_MEMBER = pv_cal_per_hier_obj_def_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = pv_user_id,
LAST_UPDATE_LOGIN = pv_login_id
WHERE
LEDGER_ID IN (
SELECT
H.CHILD_ID
FROM
FEM_LEDGERS_HIER H,
GL_LEDGERS LGR
WHERE
H.PARENT_ID = (
SELECT LEDGER_ID
FROM FEM_LEDGERS_B
WHERE LEDGER_DISPLAY_CODE = 'OGL_SOURCE_LEDGER_GROUP'
) AND
H.HIERARCHY_OBJ_DEF_ID = 1505 AND -- a hard-coded value is to be
-- replaced with a future
-- FEM API return value
LGR.LEDGER_ID = H.CHILD_ID AND
LGR.PERIOD_SET_NAME = pv_period_set_name AND
LGR.ACCOUNTED_PERIOD_TYPE = pv_period_type
) AND
(ATTRIBUTE_ID, VERSION_ID) = (
SELECT
V.ATTRIBUTE_ID,
V.VERSION_ID
FROM
FEM_DIM_ATTRIBUTES_B A,
FEM_DIM_ATTR_VERSIONS_B V
WHERE
A.DIMENSION_ID = pv_ledger_dim_id AND
A.ATTRIBUTE_VARCHAR_LABEL = 'CAL_PERIOD_HIER_OBJ_DEF_ID' AND
V.ATTRIBUTE_ID = A.ATTRIBUTE_ID AND
V.DEFAULT_VERSION_FLAG = 'Y'
) AND
DIM_ATTRIBUTE_NUMERIC_MEMBER = -1;
SELECT
DIM_ATTRIBUTE_NUMERIC_MEMBER
FROM
FEM_DIM_ATTRIBUTES_B A,
FEM_DIM_ATTR_VERSIONS_B V,
FEM_LEDGERS_B L,
FEM_LEDGERS_ATTR LA
WHERE
A.DIMENSION_ID = pv_ledger_dim_id AND
A.ATTRIBUTE_VARCHAR_LABEL = 'CAL_PERIOD_HIER_OBJ_DEF_ID' AND
V.ATTRIBUTE_ID = A.ATTRIBUTE_ID AND
V.DEFAULT_VERSION_FLAG = 'Y' AND
L.LEDGER_DISPLAY_CODE = 'OGL_SOURCE_LEDGER_GROUP' AND
LA.LEDGER_ID = L.LEDGER_ID AND
LA.ATTRIBUTE_ID = V.ATTRIBUTE_ID AND
LA.VERSION_ID = V.VERSION_ID
);
INSERT INTO FEM_INTG_CAL_PERIODS_GT(
CAL_PERIOD_ID,
DIMENSION_GROUP_ID,
PERIOD_NAME,
ENTERED_PERIOD_NAME,
ADJUSTMENT_PERIOD_FLAG,
START_DATE,
END_DATE,
PERIOD_NUM,
QUARTER_NUM,
PERIOD_YEAR
)
SELECT -- Period members
FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(
P.END_DATE,
P.PERIOD_NUM,
pv_calendar_id,
G.DIMENSION_GROUP_ID
) CAL_PERIOD_ID,
G.DIMENSION_GROUP_ID,
P.PERIOD_NAME,
P.ENTERED_PERIOD_NAME,
P.ADJUSTMENT_PERIOD_FLAG,
P.START_DATE,
P.END_DATE,
P.PERIOD_NUM,
P.QUARTER_NUM,
pv_period_year PERIOD_YEAR
FROM
GL_PERIODS P,
FEM_DIMENSION_GRPS_B G
WHERE
P.PERIOD_SET_NAME = pv_period_set_name AND
P.PERIOD_TYPE = pv_period_type AND
P.PERIOD_YEAR = pv_period_year AND
G.DIMENSION_GROUP_ID = pv_dimension_grp_id_period
UNION ALL
SELECT -- Quater members
FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(
Q.END_DATE,
Q.QUARTER_NUM,
pv_calendar_id,
pv_dimension_grp_id_quarter
) CAL_PERIOD_ID,
pv_dimension_grp_id_quarter,
'Q' || Q.QUARTER_NUM || '-' || SUBSTR(pv_period_year, length(pv_period_year)-1, 2),
'Q' || Q.QUARTER_NUM || '-' || SUBSTR(pv_period_year, length(pv_period_year)-1, 2),
--dedutta : removed decode for fixed value N : 4970174
'N' as ADJUSTMENT_PERIOD_FLAG,
Q.START_DATE,
Q.END_DATE,
Q.QUARTER_NUM, -- Bug 4523730 hkaniven --
Q.QUARTER_NUM,
Q.PERIOD_YEAR
FROM
(
SELECT
pv_period_year PERIOD_YEAR,
P.QUARTER_NUM,
--dedutta : removed AP decode 4970174
MIN(P.START_DATE) START_DATE,
MAX(P.END_DATE) END_DATE
FROM
GL_PERIODS P
WHERE
P.PERIOD_SET_NAME = pv_period_set_name AND
P.PERIOD_TYPE = pv_period_type AND
P.PERIOD_YEAR = pv_period_year
GROUP BY P.QUARTER_NUM
) Q,
GL_PERIOD_TYPES PT
WHERE
PT.PERIOD_TYPE = pv_period_type
UNION ALL
SELECT -- Year members
FEM_DIMENSION_UTIL_PKG.Generate_Member_ID(
Y.END_DATE,
1, -- Bug 4523730 hkaniven --
pv_calendar_id,
pv_dimension_grp_id_year
) CAL_PERIOD_ID,
pv_dimension_grp_id_year,
to_char(pv_period_year),
to_char(pv_period_year),
--dedutta : removed decode for fixed value N : 4970174
'N'as ADJUSTMENT_PERIOD_FLAG,
Y.START_DATE,
Y.END_DATE,
1, -- Bug 4523730 hkaniven --
Y.QUARTER_NUM,
Y.PERIOD_YEAR
FROM
(
SELECT
pv_period_year PERIOD_YEAR,
--dedutta : removed AP decode 4970174
MIN(P.START_DATE) START_DATE,
MAX(P.END_DATE) END_DATE,
MIN(P.QUARTER_NUM) QUARTER_NUM
FROM
GL_PERIODS P
WHERE
P.PERIOD_SET_NAME = pv_period_set_name AND
P.PERIOD_TYPE = pv_period_type AND
P.PERIOD_YEAR = pv_period_year
) Y,
GL_PERIOD_TYPES PT
WHERE
PT.PERIOD_TYPE = pv_period_type;
SELECT
GT.CAL_PERIOD_ID CAL_PERIOD_ID,
GT.DIMENSION_GROUP_ID DIMENSION_GROUP_ID,
pv_calendar_id CALENDAR_ID,
'Y' ENABLED_FLAG,
'N' PERSONAL_FLAG,
CASE
WHEN GT.DIMENSION_GROUP_ID = pv_dimension_grp_id_period THEN 'Y'
ELSE 'N'
END READ_ONLY_FLAG,
pc_object_version_number OBJECT_VERSION_NUMBER,
SYSDATE CREATION_DATE,
pv_user_id CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
pv_user_id LAST_UPDATED_BY,
pv_login_id LAST_UPDATE_LOGIN
FROM
FEM_INTG_CAL_PERIODS_GT GT
) S
ON (
B.CAL_PERIOD_ID = S.CAL_PERIOD_ID
)
WHEN MATCHED THEN UPDATE
SET B.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN INSERT(
B.CAL_PERIOD_ID,
B.DIMENSION_GROUP_ID,
B.CALENDAR_ID,
B.ENABLED_FLAG,
B.PERSONAL_FLAG,
B.READ_ONLY_FLAG,
B.OBJECT_VERSION_NUMBER,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN
) VALUES (
S.CAL_PERIOD_ID,
S.DIMENSION_GROUP_ID,
S.CALENDAR_ID,
S.ENABLED_FLAG,
S.PERSONAL_FLAG,
S.READ_ONLY_FLAG,
S.OBJECT_VERSION_NUMBER,
S.CREATION_DATE,
S.CREATED_BY,
S.LAST_UPDATE_DATE,
S.LAST_UPDATED_BY,
S.LAST_UPDATE_LOGIN
);
SELECT
GT.CAL_PERIOD_ID CAL_PERIOD_ID,
DECODE(GT.DIMENSION_GROUP_ID,
70, pv_cal_per_hier_name || '-' || GT.PERIOD_NAME,
10, pv_cal_per_hier_name || '-' || GT.PERIOD_NAME,
GT.PERIOD_NAME) CAL_PERIOD_NAME, -- Bug 4350620 hkaniven --
GT.DIMENSION_GROUP_ID DIMENSION_GROUP_ID, -- Bug 4486878 --
DECODE(GT.DIMENSION_GROUP_ID,
70, pv_cal_per_hier_name || '-' || GT.PERIOD_NAME,
10, pv_cal_per_hier_name || '-' || GT.PERIOD_NAME,
GT.PERIOD_NAME) DESCRIPTION, -- Bug 4350620 hkaniven --
pv_calendar_id CALENDAR_ID,
L.LANGUAGE_CODE LANGUAGE,
L.LANGUAGE_CODE SOURCE_LANG,
SYSDATE CREATION_DATE,
pv_user_id CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
pv_user_id LAST_UPDATED_BY,
pv_login_id LAST_UPDATE_LOGIN
FROM
FEM_INTG_CAL_PERIODS_GT GT,
FND_LANGUAGES L
WHERE
L.INSTALLED_FLAG IN ('B','I')
) S
ON (
TL.CAL_PERIOD_ID = S.CAL_PERIOD_ID AND
TL.LANGUAGE = S.LANGUAGE
)
WHEN MATCHED THEN UPDATE
SET TL.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN INSERT(
TL.CAL_PERIOD_ID,
TL.CAL_PERIOD_NAME,
TL.DIMENSION_GROUP_ID, -- Bug 4486878 --
TL.DESCRIPTION,
TL.CALENDAR_ID,
TL.LANGUAGE,
TL.SOURCE_LANG,
TL.CREATION_DATE,
TL.CREATED_BY,
TL.LAST_UPDATE_DATE,
TL.LAST_UPDATED_BY,
TL.LAST_UPDATE_LOGIN
) VALUES (
S.CAL_PERIOD_ID,
S.CAL_PERIOD_NAME,
S.DIMENSION_GROUP_ID, -- Bug 4486878 --
S.DESCRIPTION,
S.CALENDAR_ID,
S.LANGUAGE,
S.SOURCE_LANG,
S.CREATION_DATE,
S.CREATED_BY,
S.LAST_UPDATE_DATE,
S.LAST_UPDATED_BY,
S.LAST_UPDATE_LOGIN
);
SELECT
A.ATTRIBUTE_ID ATTRIBUTE_ID,
V.VERSION_ID VERSION_ID,
GT.CAL_PERIOD_ID CAL_PERIOD_ID,
DECODE(
A.ATTRIBUTE_VARCHAR_LABEL,
'SOURCE_SYSTEM_CODE',NULL,
'ADJ_PERIOD_FLAG', NULL,
'GL_ORIGIN_FLAG', NULL,
NULL
) DIM_ATTRIBUTE_VALUE_SET_ID,
DECODE(
A.ATTRIBUTE_VARCHAR_LABEL,
'SOURCE_SYSTEM_CODE', pv_source_system_code,
NULL
) DIM_ATTRIBUTE_NUMERIC_MEMBER,
DECODE(
A.ATTRIBUTE_VARCHAR_LABEL,
'ADJ_PERIOD_FLAG', GT.ADJUSTMENT_PERIOD_FLAG,
'CUR_PERIOD_FLAG', 'N',
'GL_ORIGIN_FLAG', 'Y',
NULL
) DIM_ATTRIBUTE_VARCHAR_MEMBER,
DECODE(
A.ATTRIBUTE_VARCHAR_LABEL,
'ACCOUNTING_YEAR', GT.PERIOD_YEAR,
'GL_PERIOD_NUM', GT.PERIOD_NUM,
NULL
) NUMBER_ASSIGN_VALUE,
DECODE(
A.ATTRIBUTE_VARCHAR_LABEL,
'CAL_PERIOD_PREFIX', GT.ENTERED_PERIOD_NAME,
NULL
) VARCHAR_ASSIGN_VALUE,
DECODE(
A.ATTRIBUTE_VARCHAR_LABEL,
'CAL_PERIOD_START_DATE', GT.START_DATE,
'CAL_PERIOD_END_DATE', GT.END_DATE,
NULL
) DATE_ASSIGN_VALUE,
pc_object_version_number OBJECT_VERSION_NUMBER,
pc_aw_snapshot_flag AW_SNAPSHOT_FLAG,
SYSDATE CREATION_DATE,
pv_user_id CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
pv_user_id LAST_UPDATED_BY,
pv_login_id LAST_UPDATE_LOGIN
FROM
FEM_INTG_CAL_PERIODS_GT GT,
FEM_DIM_ATTRIBUTES_B A,
FEM_DIM_ATTR_VERSIONS_B V
WHERE
A.DIMENSION_ID = pv_cal_period_dim_id AND
V.ATTRIBUTE_ID = A.ATTRIBUTE_ID AND
V.DEFAULT_VERSION_FLAG = 'Y' AND
A.ATTRIBUTE_VARCHAR_LABEL IN (
'ACCOUNTING_YEAR',
'ADJ_PERIOD_FLAG',
'CAL_PERIOD_END_DATE',
'CAL_PERIOD_PREFIX',
'CAL_PERIOD_START_DATE',
'CUR_PERIOD_FLAG',
'GL_ORIGIN_FLAG',
'GL_PERIOD_NUM',
'SOURCE_SYSTEM_CODE'
)
) S
ON (
ATTR.ATTRIBUTE_ID = S.ATTRIBUTE_ID AND
ATTR.VERSION_ID = S.VERSION_ID AND
ATTR.CAL_PERIOD_ID = S.CAL_PERIOD_ID AND
NVL(ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER, -1) =
NVL(S.DIM_ATTRIBUTE_NUMERIC_MEMBER, -1) AND
NVL(ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER, 'NULL') =
NVL(S.DIM_ATTRIBUTE_VARCHAR_MEMBER, 'NULL')
)
WHEN MATCHED THEN UPDATE
SET ATTR.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN INSERT(
ATTR.ATTRIBUTE_ID,
ATTR.VERSION_ID,
ATTR.CAL_PERIOD_ID,
ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER,
ATTR.DIM_ATTRIBUTE_VALUE_SET_ID,
ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER,
ATTR.NUMBER_ASSIGN_VALUE,
ATTR.VARCHAR_ASSIGN_VALUE,
ATTR.DATE_ASSIGN_VALUE,
ATTR.OBJECT_VERSION_NUMBER,
ATTR.AW_SNAPSHOT_FLAG,
ATTR.CREATION_DATE,
ATTR.CREATED_BY,
ATTR.LAST_UPDATE_DATE,
ATTR.LAST_UPDATED_BY,
ATTR.LAST_UPDATE_LOGIN
) VALUES (
S.ATTRIBUTE_ID,
S.VERSION_ID,
S.CAL_PERIOD_ID,
S.DIM_ATTRIBUTE_NUMERIC_MEMBER,
S.DIM_ATTRIBUTE_VALUE_SET_ID,
S.DIM_ATTRIBUTE_VARCHAR_MEMBER,
S.NUMBER_ASSIGN_VALUE,
S.VARCHAR_ASSIGN_VALUE,
S.DATE_ASSIGN_VALUE,
S.OBJECT_VERSION_NUMBER,
S.AW_SNAPSHOT_FLAG,
S.CREATION_DATE,
S.CREATED_BY,
S.LAST_UPDATE_DATE,
S.LAST_UPDATED_BY,
S.LAST_UPDATE_LOGIN
);
SELECT -- Termination points (Year)
pv_cal_per_hier_obj_def_id HIERARCHY_OBJ_DEF_ID,
1 PARENT_DEPTH_NUM,
GT.CAL_PERIOD_ID PARENT_ID,
1 CHILD_DEPTH_NUM,
GT.CAL_PERIOD_ID CHILD_ID,
'Y' SINGLE_DEPTH_FLAG,
GT.PERIOD_YEAR DISPLAY_ORDER_NUM,
pc_weighting_pct WEIGHTING_PCT,
pc_object_version_number OBJECT_VERSION_NUMBER,
'Y' READ_ONLY_FLAG,
SYSDATE CREATION_DATE,
pv_user_id CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
pv_user_id LAST_UPDATED_BY,
pv_login_id LAST_UPDATE_LOGIN
FROM
FEM_INTG_CAL_PERIODS_GT GT
WHERE
GT.DIMENSION_GROUP_ID = pv_dimension_grp_id_year
UNION ALL
SELECT -- Year and Quarter
pv_cal_per_hier_obj_def_id HIERARCHY_OBJ_DEF_ID,
1 PARENT_DEPTH_NUM,
Y.CAL_PERIOD_ID PARENT_ID,
2 CHILD_DEPTH_NUM,
Q.CAL_PERIOD_ID CHILD_ID,
'Y' SINGLE_DEPTH_FLAG,
Q.QUARTER_NUM DISPLAY_ORDER_NUM,
pc_weighting_pct WEIGHTING_PCT,
pc_object_version_number OBJECT_VERSION_NUMBER,
'Y' READ_ONLY_FLAG,
SYSDATE CREATION_DATE,
pv_user_id CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
pv_user_id LAST_UPDATED_BY,
pv_login_id LAST_UPDATE_LOGIN
FROM
FEM_INTG_CAL_PERIODS_GT Y,
FEM_INTG_CAL_PERIODS_GT Q
WHERE
Y.DIMENSION_GROUP_ID = pv_dimension_grp_id_year AND
Q.DIMENSION_GROUP_ID = pv_dimension_grp_id_quarter AND
Y.PERIOD_YEAR = Q.PERIOD_YEAR
) S
ON (
H.HIERARCHY_OBJ_DEF_ID = S.HIERARCHY_OBJ_DEF_ID AND
H.CHILD_ID = S.CHILD_ID
)
WHEN MATCHED THEN UPDATE
SET H.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN INSERT(
H.HIERARCHY_OBJ_DEF_ID,
H.PARENT_DEPTH_NUM,
H.PARENT_ID,
H.CHILD_DEPTH_NUM,
H.CHILD_ID,
H.SINGLE_DEPTH_FLAG,
H.DISPLAY_ORDER_NUM,
H.WEIGHTING_PCT,
H.OBJECT_VERSION_NUMBER,
H.READ_ONLY_FLAG,
H.CREATION_DATE,
H.CREATED_BY,
H.LAST_UPDATE_DATE,
H.LAST_UPDATED_BY,
H.LAST_UPDATE_LOGIN
) VALUES (
S.HIERARCHY_OBJ_DEF_ID,
S.PARENT_DEPTH_NUM,
S.PARENT_ID,
S.CHILD_DEPTH_NUM,
S.CHILD_ID,
S.SINGLE_DEPTH_FLAG,
S.DISPLAY_ORDER_NUM,
S.WEIGHTING_PCT,
S.OBJECT_VERSION_NUMBER,
S.READ_ONLY_FLAG,
S.CREATION_DATE,
S.CREATED_BY,
S.LAST_UPDATE_DATE,
S.LAST_UPDATED_BY,
S.LAST_UPDATE_LOGIN
);
SELECT
pv_cal_per_hier_obj_def_id HIERARCHY_OBJ_DEF_ID,
2 PARENT_DEPTH_NUM,
Q.CAL_PERIOD_ID PARENT_ID,
3 CHILD_DEPTH_NUM,
P.CAL_PERIOD_ID CHILD_ID,
'Y' SINGLE_DEPTH_FLAG,
P.PERIOD_NUM DISPLAY_ORDER_NUM,
pc_weighting_pct WEIGHTING_PCT,
pc_object_version_number OBJECT_VERSION_NUMBER,
'N' READ_ONLY_FLAG,
SYSDATE CREATION_DATE,
pv_user_id CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
pv_user_id LAST_UPDATED_BY,
pv_login_id LAST_UPDATE_LOGIN
FROM
FEM_INTG_CAL_PERIODS_GT P,
FEM_INTG_CAL_PERIODS_GT Q
WHERE
P.DIMENSION_GROUP_ID = pv_dimension_grp_id_period AND
Q.DIMENSION_GROUP_ID = pv_dimension_grp_id_quarter AND
Q.QUARTER_NUM = P.QUARTER_NUM
) S
ON (
H.HIERARCHY_OBJ_DEF_ID = S.HIERARCHY_OBJ_DEF_ID AND
H.CHILD_ID = S.CHILD_ID
)
WHEN MATCHED THEN UPDATE
SET H.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN INSERT(
H.HIERARCHY_OBJ_DEF_ID,
H.PARENT_DEPTH_NUM,
H.PARENT_ID,
H.CHILD_DEPTH_NUM,
H.CHILD_ID,
H.SINGLE_DEPTH_FLAG,
H.DISPLAY_ORDER_NUM,
H.WEIGHTING_PCT,
H.OBJECT_VERSION_NUMBER,
H.READ_ONLY_FLAG,
H.CREATION_DATE,
H.CREATED_BY,
H.LAST_UPDATE_DATE,
H.LAST_UPDATED_BY,
H.LAST_UPDATE_LOGIN
) VALUES (
S.HIERARCHY_OBJ_DEF_ID,
S.PARENT_DEPTH_NUM,
S.PARENT_ID,
S.CHILD_DEPTH_NUM,
S.CHILD_ID,
S.SINGLE_DEPTH_FLAG,
S.DISPLAY_ORDER_NUM,
S.WEIGHTING_PCT,
S.OBJECT_VERSION_NUMBER,
S.READ_ONLY_FLAG,
S.CREATION_DATE,
S.CREATED_BY,
S.LAST_UPDATE_DATE,
S.LAST_UPDATED_BY,
S.LAST_UPDATE_LOGIN
);
WHEN MATCHED THEN UPDATE
SET V.LAST_UPDATE_DATE = SYSDATE
WHEN NOT MATCHED THEN INSERT (
V.HIERARCHY_OBJ_ID,
V.VALUE_SET_ID,
V.OBJECT_VERSION_NUMBER,
V.CREATION_DATE,
V.CREATED_BY,
V.LAST_UPDATE_DATE,
V.LAST_UPDATED_BY,
V.LAST_UPDATE_LOGIN
) VALUES (
pv_cal_per_hier_obj_id,
pv_calendar_id,
pc_object_version_number,
SYSDATE,
pv_user_id,
SYSDATE,
pv_user_id,
pv_login_id
);
PROCEDURE Update_Calendar_Map
IS
BEGIN
FEM_ENGINES_PKG.Tech_Message(
p_severity => pc_log_level_procedure,
p_module => pc_module_name || 'update_calendar_map.begin',
p_app_name => 'FEM',
p_msg_name => 'FEM_GL_POST_201',
p_token1 => 'FUNC_NAME',
p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Update_Calendar_Map',
p_token2 => 'TIME',
p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
);
UPDATE FEM_INTG_CALENDAR_MAP
SET EFFECTIVE_PERIOD_NUM_MIN = pv_effective_period_num_low,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = pv_user_id,
LAST_UPDATE_LOGIN = pv_login_id
WHERE CAL_PER_HIER_OBJ_DEF_ID = pv_cal_per_hier_obj_def_id
AND pv_effective_period_num_low < NVL(EFFECTIVE_PERIOD_NUM_MIN,3000*10000);
UPDATE FEM_INTG_CALENDAR_MAP
SET EFFECTIVE_PERIOD_NUM_MAX = pv_effective_period_num_high,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = pv_user_id,
LAST_UPDATE_LOGIN = pv_login_id
WHERE CAL_PER_HIER_OBJ_DEF_ID = pv_cal_per_hier_obj_def_id
AND pv_effective_period_num_high > NVL(EFFECTIVE_PERIOD_NUM_MAX, 0);
p_module => pc_module_name || 'update_calendar_map.end',
p_app_name => 'FEM',
p_msg_name => 'FEM_GL_POST_202',
p_token1 => 'FUNC_NAME',
p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Update_Calendar_Map',
p_token2 => 'TIME',
p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
);
p_module => pc_module_name || 'update_calendar_map.exception_others' ,
p_app_name => 'FEM',
p_msg_name => 'FEM_GL_POST_215',
p_token1 => 'ERR_MSG',
p_value1 => SQLERRM
);
p_module => pc_module_name || 'update_calendar_map.exception_others',
p_app_name => 'FEM',
p_msg_name => 'FEM_GL_POST_203',
p_token1 => 'FUNC_NAME',
p_value1 => 'FEM_INTG_CAL_RULE_ENG_PKG.Update_Calendar_Map',
p_token2 => 'TIME',
p_value2 => TO_CHAR(SYSDATE)||' '||TO_CHAR(SYSDATE,'HH24:MI:SS')
);
END Update_Calendar_Map;
SELECT TRIM(display_name)
INTO pv_cal_per_hier_name
FROM fem_object_definition_vl
WHERE object_id = pv_cal_per_hier_obj_id
AND object_definition_id = pv_cal_per_hier_obj_def_id;
Update_Calendar_Map();