[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Write2FWKLog('PA calender is selected.');
Write2FWKLog('GL calender is selected.');
SELECT NVL(org_structure_version_id, -1)
INTO l_Org_Structure_Version_ID
FROM PJI_SYSTEM_SETTINGS;
** selected in PMV.
** These Convert_* API's would be called from the table
** functions.
*/
/*
** ----------------------------------------------------------
** Function: Convert_ViewBY
** The function translates the value of view by passed by pmv
** to short variations understood by all the other convert
** API's.
** Bug# 2589267: This fix is provided for addressing the
** scalability issues with literal strings in the sql
** statements.
** ----------------------------------------------------------
*/
Function Convert_ViewBY(p_View_BY VARCHAR2) RETURN VARCHAR2
AS
BEGIN
IF p_PA_DEBUG_MODE = 'Y' THEN
Write2FWKLog('Entering Convert_ViewBY...','Convert_ViewBY');
Write2FWKLog('Global Currency Code is selected.');
Write2FWKLog('Second Global Currency Code is selected.');
Write2FWKLog('Functional Currency Code is selected.');
Write2FWKLog('Global Currency Code is selected.');
Write2FWKLog('Second Global Currency Code is selected.');
Write2FWKLog('Functional Currency Code is selected.');
Write2FWKLog('Project Currency Code is selected.');
** The function inserts all the valid class codes specified in
** the pmv report into a session specific temporary table.
** The function return 'Y' if the lower level fact (Class)
** needs to be joined to.
** Bug# 2491237: For ensuring that project types are always
** secured by Operating Unit. It is mandatory that all programs
** calling the convert api should first place a call to the
** Convert_Operating_Unit API before calling this API.
** ----------------------------------------------------------
*/
Function Convert_Classification(p_Classification_ID VARCHAR2 DEFAULT NULL
, p_Class_Code_IDS VARCHAR2 DEFAULT NULL
, p_View_BY VARCHAR2) RETURN VARCHAR2
AS
l_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
DELETE PJI_PMV_CLS_DIM_TMP;
Write2FWKLog('All Project Types are selected.');
INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
FROM PJI_CLASS_CODES CLS
, PA_PROJECT_TYPES_ALL PJT
, PJI_PMV_ORG_DIM_TMP ORG
WHERE PJT.ORG_ID = ORG.ID
AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
AND CLS.RECORD_TYPE = 'T';
Write2FWKLog('All '||p_Classification_ID||' Project Type is selected.');
INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
FROM PJI_CLASS_CODES CLS
, PA_PROJECT_TYPES_ALL PJT
, PJI_PMV_ORG_DIM_TMP ORG
WHERE PJT.ORG_ID = ORG.ID
AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
AND CLS.CLASS_CATEGORY = p_Classification_ID;
Write2FWKLog('All class codes for '||p_Classification_ID||' Project type or Classification are selected.');
INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
SELECT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
FROM PJI_CLASS_CODES CLS
WHERE CLS.CLASS_CATEGORY = p_Classification_ID;
Write2FWKLog('Selected list of Project type or Classification '||p_Classification_ID||' are selected.');
INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
FROM PJI_CLASS_CODES CLS
, PA_PROJECT_TYPES_ALL PJT
, PJI_PMV_ORG_DIM_TMP ORG
WHERE PJT.ORG_ID = ORG.ID
AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
AND CLS.RECORD_TYPE = 'T'
AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
FROM PJI_CLASS_CODES CLS
, PA_PROJECT_TYPES_ALL PJT
, PJI_PMV_ORG_DIM_TMP ORG
WHERE PJT.ORG_ID = ORG.ID
AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
AND CLS.RECORD_TYPE = 'T'
AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
FROM PJI_CLASS_CODES CLS
, PA_PROJECT_TYPES_ALL PJT
, PJI_PMV_ORG_DIM_TMP ORG
WHERE PJT.ORG_ID = ORG.ID
AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
FROM PJI_CLASS_CODES CLS
, PA_PROJECT_TYPES_ALL PJT
, PJI_PMV_ORG_DIM_TMP ORG
WHERE PJT.ORG_ID = ORG.ID
AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
SELECT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
FROM PJI_CLASS_CODES CLS
WHERE CLS.CLASS_CATEGORY = p_Classification_ID
AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
SELECT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
FROM PJI_CLASS_CODES CLS
WHERE CLS.CLASS_CATEGORY = p_Classification_ID
AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
Write2FWKLog('A class code was selected without corresponding category.');
INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
SELECT -1, '-1'
FROM SYS.DUAL; -- REMOVE THIS COMMENT WHEN THE ISSUE WITH DUAL IS RESOLVED.
** The function inserts all the valid expenditure/event types specified in
** the pmv report into a session specific temporary table.
** The function return 'Y' if the lower level fact (expenditure/event types)
** needs to be joined to.
** ----------------------------------------------------------
*/
Function Convert_Event_Revenue_Type(p_Revenue_Category VARCHAR2 DEFAULT NULL
, p_Revenue_Type_IDS VARCHAR2 DEFAULT NULL
, p_View_BY VARCHAR2) RETURN VARCHAR2
AS
l_RCate_Dimension_List_Tab SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
DELETE PJI_PMV_EC_RC_DIM_TMP where record_type = 'RC';
DELETE PJI_PMV_ET_RT_DIM_TMP where record_type = 'RT';
INSERT INTO PJI_PMV_EC_RC_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT ID, VALUE,'RC'
FROM pji_revenue_categories_v ;
INSERT INTO PJI_PMV_EC_RC_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT ID, DECODE(p_View_By, 'RC', VALUE, '-1'),'RC'
FROM pji_revenue_categories_v
WHERE ID = l_RCate_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT ID, VALUE,'RT'
FROM pji_exp_evt_types_v ;
INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT rtyp.id, DECODE(p_View_BY,'RC',usrx.name,'RT',rtyp.value,'-1'),'RT'
FROM pji_exp_evt_types_v rtyp
, PJI_PMV_EC_RC_DIM_TMP usrx
WHERE rtyp.revenue_category_code = usrx.id
and usrx.record_type = 'RC';
INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT id, DECODE(p_View_BY,'RT',value,'-1'),'RT'
FROM pji_exp_evt_types_v
WHERE
id = l_RType_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT rtyp.id, DECODE(p_View_BY,'RC',usrx.name,'RT',rtyp.value,'-1'),'RT'
FROM pji_exp_evt_types_v rtyp
, PJI_PMV_EC_RC_DIM_TMP usrx
WHERE rtyp.id = l_RType_Dimension_List_Tab(i)
AND rtyp.revenue_category_code = usrx.id
AND usrx.record_type = 'RC';
DELETE PJI_PMV_WT_DIM_TMP;
INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
SELECT WT.ID, DECODE(p_View_BY,'WT',WT.VALUE,'-1')
FROM PJI_WORK_TYPES_V WT
WHERE WT.ID = l_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
SELECT WT.ID, WT.VALUE
FROM PJI_WORK_TYPES_V WT ;
** The function inserts all the valid expenditure types specified in
** the pmv report into a session specific temporary table.
** The function return 'Y' if the lower level fact (expenditure type)
** needs to be joined to.
** ----------------------------------------------------------
*/
Function Convert_Expenditure_Type(p_Expenditure_Category VARCHAR2 DEFAULT NULL
, p_Expenditure_Type_IDS VARCHAR2 DEFAULT NULL
, p_View_BY VARCHAR2) RETURN VARCHAR2
AS
l_ECate_Dimension_List_Tab SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
DELETE PJI_PMV_ET_RT_DIM_TMP where record_type = 'ET';
DELETE PJI_PMV_EC_RC_DIM_TMP where record_type = 'EC';
INSERT INTO PJI_PMV_EC_RC_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT EXPENDITURE_CATEGORY, EXPENDITURE_CATEGORY,'EC'
FROM PA_EXPENDITURE_CATEGORIES ;
INSERT INTO PJI_PMV_EC_RC_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT EXPENDITURE_CATEGORY, DECODE(p_View_By, 'EC', EXPENDITURE_CATEGORY, '-1'),'EC'
FROM PA_EXPENDITURE_CATEGORIES
WHERE EXPENDITURE_CATEGORY_ID = to_number(l_ECate_Dimension_List_Tab(i));
INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT EXPENDITURE_TYPE_ID, EXPENDITURE_TYPE,'ET'
FROM PA_EXPENDITURE_TYPES ;
INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT etyp.expenditure_type_id,
DECODE(p_View_BY,'EC',usrx.name,'ET',etyp.expenditure_type,'-1'),'ET'
FROM pa_expenditure_types etyp
, PJI_PMV_EC_RC_DIM_TMP usrx
WHERE etyp.expenditure_category = usrx.id
and usrx.record_type = 'EC';
INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT expenditure_type_id, DECODE(p_View_BY,'ET',expenditure_type,'-1'),'ET'
FROM pa_expenditure_types
WHERE
expenditure_type_id = l_EType_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
SELECT etyp.expenditure_type_id,
DECODE(p_View_BY,'EC',usrx.name,'ET',etyp.expenditure_type,'-1'),'ET'
FROM pa_expenditure_types etyp
, PJI_PMV_EC_RC_DIM_TMP usrx
WHERE etyp.expenditure_type_id = l_EType_Dimension_List_Tab(i)
AND etyp.expenditure_category = usrx.id
AND usrx.record_type = 'EC';
** The function inserts all the valid work types specified in
** the pmv report into a session specific temporary table.
** The function return 'Y' if the lower level fact (work type)
** needs to be joined to.
** ----------------------------------------------------------
*/
Function Convert_Util_Category(p_Work_Type_IDS VARCHAR2 DEFAULT NULL
, p_Util_Category_IDS VARCHAR2 DEFAULT NULL
, p_View_BY VARCHAR2) RETURN VARCHAR2
AS
l_Util_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
DELETE PJI_PMV_WT_DIM_TMP;
DELETE PJI_PMV_UC_DIM_TMP;
INSERT INTO PJI_PMV_UC_DIM_TMP (ID, NAME)
SELECT util_category_id, name
FROM pa_util_categories_tl
WHERE
LANGUAGE = USERENV('LANG');
INSERT INTO PJI_PMV_UC_DIM_TMP (ID, NAME)
SELECT util_category_id, DECODE(p_View_By, 'UC', name , '-1')
FROM pa_util_categories_tl
WHERE
LANGUAGE = USERENV('LANG')
AND util_category_id = l_Util_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_UC_DIM_TMP (ID, NAME)
SELECT util_category_id, DECODE(p_View_By, 'UC', name , '-1')
FROM pa_util_categories_tl
WHERE
LANGUAGE = USERENV('LANG')
AND util_category_id = l_Util_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
SELECT work_type_id, name
FROM pa_work_types_tl
WHERE
LANGUAGE = USERENV('LANG');
INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
SELECT orig.work_type_id, DECODE(p_View_BY,'UC',usrx.name,'WT',orig_tl.name,'-1')
FROM pa_work_types_tl orig_tl
, pa_work_types_b orig
, pji_pmv_uc_dim_tmp usrx
WHERE
LANGUAGE = USERENV('LANG')
AND orig.work_type_id = orig_tl.work_type_id
AND orig.org_util_category_id = usrx.id;
INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
SELECT work_type_id, DECODE(p_View_BY,'WT',name,'-1')
FROM pa_work_types_tl
WHERE
LANGUAGE = USERENV('LANG')
AND work_type_id = l_WType_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
SELECT work_type_id, DECODE(p_View_BY,'WT',name,'-1')
FROM pa_work_types_tl
WHERE
LANGUAGE = USERENV('LANG')
AND work_type_id = l_WType_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
SELECT orig.work_type_id, DECODE(p_View_BY,'UC',usrx.name,'WT',orig_tl.name,'-1')
FROM pa_work_types_tl orig_tl
, pa_work_types_b orig
, pji_pmv_uc_dim_tmp usrx
WHERE
LANGUAGE = USERENV('LANG')
AND orig.work_type_id = l_WType_Dimension_List_Tab(i)
AND orig.work_type_id = orig_tl.work_type_id
AND orig.org_util_category_id = usrx.id;
INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
SELECT orig.work_type_id, DECODE(p_View_BY,'UC',usrx.name,'WT',orig_tl.name,'-1')
FROM pa_work_types_tl orig_tl
, pa_work_types_b orig
, pji_pmv_uc_dim_tmp usrx
WHERE
LANGUAGE = USERENV('LANG')
AND orig.work_type_id = l_WType_Dimension_List_Tab(i)
AND orig.work_type_id = orig_tl.work_type_id
AND orig.org_util_category_id = usrx.id;
** The function inserts all the valid Job Levels specified in
** the pmv report into a session specific temporary table.
** The function return 'Y' if the lower level fact (job)
** needs to be joined to.
** ----------------------------------------------------------
*/
Function Convert_Job_Level(p_Job_IDS VARCHAR2 DEFAULT NULL
, p_Job_Level_IDS VARCHAR2 DEFAULT NULL
, p_View_BY VARCHAR2) RETURN VARCHAR2
AS
l_Job_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
DELETE PJI_PMV_JB_DIM_TMP;
DELETE PJI_PMV_JL_DIM_TMP;
INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
SELECT ID, VALUE
FROM PJI_JOB_LEVELS_V;
INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
SELECT ID,DECODE(p_View_By, 'JL', VALUE , '-1')
FROM PJI_JOB_LEVELS_V
WHERE ID = l_JLevel_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
SELECT ID,DECODE(p_View_By, 'JL', VALUE , '-1')
FROM PJI_JOB_LEVELS_V
WHERE ID = l_JLevel_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_JB_DIM_TMP (ID, NAME)
SELECT ID, VALUE
FROM PJI_JOBS_V;
INSERT INTO PJI_PMV_JB_DIM_TMP (ID, NAME)
SELECT ORIG.ID, DECODE(p_View_BY,'JL',USRX.NAME,'JB',ORIG.VALUE,'-1')
FROM PJI_JOBS_V ORIG
, PJI_PMV_JL_DIM_TMP USRX
WHERE ORIG.JOB_LEVEL=USRX.ID;
INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
SELECT ID, DECODE(p_View_BY,'JL',VALUE,'-1')
FROM PJI_JOBS_V
WHERE ID = l_Job_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
SELECT ID, DECODE(p_View_BY,'JL',VALUE,'-1')
FROM PJI_JOBS_V
WHERE ID = l_Job_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
SELECT ORIG.ID, DECODE(p_View_BY,'JL',USRX.NAME,'JB',ORIG.VALUE,'-1')
FROM PJI_JOBS_V ORIG
, PJI_PMV_JL_DIM_TMP USRX
WHERE ORIG.ID=l_Job_Dimension_List_Tab(i)
AND ORIG.JOB_LEVEL=USRX.ID;
INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
SELECT ORIG.ID, DECODE(p_View_BY,'JL',USRX.NAME,'JB',ORIG.VALUE,'-1')
FROM PJI_JOBS_V ORIG
, PJI_PMV_JL_DIM_TMP USRX
WHERE ORIG.ID=l_Job_Dimension_List_Tab(i)
AND ORIG.JOB_LEVEL=USRX.ID;
PROCEDURE insert_user_assignment_orgz
IS
l_user_id NUMBER;
Write2FWKLog('Entering insert_user_assignment_orgz...');
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
SELECT sub_organization_id,
org.name
FROM pji_org_denorm orgd,
hr_all_organization_units_tl org,
fnd_user fnd,
per_all_assignments_f per
WHERE org.language = USERENV('LANG')
AND fnd.user_id=l_user_id
AND fnd.employee_id=per.person_id
AND per.primary_flag='Y'
AND (SYSDATE BETWEEN per.effective_start_Date AND NVL(per.effective_end_date, SYSDATE + 1))
AND orgd.sub_organization_id = org.organization_id
AND orgd.organization_id = per.organization_id
AND orgd.sub_organization_level-orgd.organization_level=1;
Write2FWKLog('Leaving insert_user_assignment_orgz . Inserted rows'||l_temp);
END insert_user_assignment_orgz;
** The function inserts immediate sub organizations (the user
** has access to below the selected organization) into a
** session specific temporary table.
** ----------------------------------------------------------
*/
Procedure Convert_Organization(p_Top_Organization_ID NUMBER
, p_View_BY VARCHAR2
, p_Top_Organization_Name OUT NOCOPY VARCHAR2)
AS
l_Organization_Name VARCHAR2(240);
DELETE PJI_PMV_ORGZ_DIM_TMP;
SELECT view_all_organizations_flag,
organization_id
INTO l_View_All_Org_Flag,
l_top_organization_id
FROM per_security_profiles
WHERE security_profile_id = l_Security_Profile_ID;
Write2FWKLog('Before insert of immediate sub-org organizations (w/o security)...');
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
SELECT sub_organization_id
, org.name
FROM pji_org_denorm orgd
, hr_all_organization_units_tl org
WHERE 1=1
AND org.language = USERENV('LANG')
AND orgd.sub_organization_id = org.organization_id
AND orgd.sub_organization_level-orgd.organization_level=1
AND orgd.organization_id = p_Top_Organization_ID;
Write2FWKLog('After insert of immediate sub-org organizations (w/o security)...');
Write2FWKLog('Before insert of immediate sub-org organizations...');
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
SELECT sub_organization_id
, org.name
FROM pji_org_denorm orgd
, per_organization_list sec
, hr_all_organization_units_tl org
WHERE 1=1
AND org.language = USERENV('LANG')
AND sec.security_profile_id = l_Security_Profile_ID
AND orgd.sub_organization_id = org.organization_id
AND orgd.sub_organization_id = sec.organization_id
AND orgd.organization_id = p_Top_Organization_ID
AND orgd.sub_organization_level-orgd.organization_level=1;
insert_user_assignment_orgz;
Write2FWKLog('After insert of immediate sub-org organizations...');
Write2FWKLog('Before selecting the organization name...');
SELECT name
INTO l_Organization_Name
FROM hr_all_organization_units_tl
WHERE organization_id = p_Top_Organization_ID
AND language = USERENV('LANG');
Write2FWKLog('After selecting the organization name...');
Write2FWKLog('Before insert of current organization...');
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
VALUES (p_Top_Organization_ID, l_Organization_Name);
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
VALUES (p_Top_Organization_ID, -1);
Write2FWKLog('After insert of current organization...');
** The function inserts all the organizations (the user has
** access to below the selected organization) into a session
** specific temporary table.
** ----------------------------------------------------------
*/
Procedure Convert_Organization(p_Top_Organization_ID NUMBER
, p_View_BY VARCHAR2)
AS
l_Security_Profile_ID NUMBER:=fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL');
DELETE PJI_PMV_ORGZ_DIM_TMP;
Write2FWKLog('Before insert of rollup organization...');
SELECT view_all_organizations_flag,
organization_id
INTO l_View_All_Org_Flag,
l_top_organization_id
FROM per_security_profiles
WHERE security_profile_id = l_Security_Profile_ID;
Write2FWKLog('Before insert of all sub-org organizations (w/o security)...');
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
SELECT subro_organization_id
, name
FROM hri_cs_orghro_v orgd
, hr_all_organization_units_tl org
, pji_system_settings pjist
WHERE 1=1
AND pjist.setting_id = 1
AND orgd.org_hierarchy_version_id = pjist.org_structure_version_id
AND orgd.sub_organization_id = org.organization_id
AND org.language = USERENV('LANG')
AND orgd.sup_organization_id = p_Top_Organization_ID
AND orgd.sub_org_absolute_level-orgd.sup_org_absolute_level=1;
Write2FWKLog('After insert of all sub-org organizations (w/o security)...');
Write2FWKLog('Before insert of all sub-org organizations...');
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
SELECT subro_organization_id
, name
FROM hri_cs_orghro_v orgd
, hr_all_organization_units_tl org
, per_organization_list sec
, pji_system_settings pjist
WHERE 1=1
AND pjist.setting_id = 1
AND sec.security_profile_id = l_Security_Profile_ID
AND orgd.subro_organization_id = sec.organization_id
AND orgd.org_hierarchy_version_id = pjist.org_structure_version_id
AND orgd.sub_organization_id = org.organization_id
AND org.language = USERENV('LANG')
AND orgd.sup_organization_id = p_Top_Organization_ID
AND orgd.sub_org_absolute_level-orgd.sup_org_absolute_level=1;
insert_user_assignment_orgz;
Write2FWKLog('After insert of all sub-org organizations...');
Write2FWKLog('After insert of rollup organization...');
Write2FWKLog('Before insert of current organization...');
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
SELECT organization_id, name
FROM hr_all_organization_units_tl
WHERE organization_id = p_Top_Organization_ID
AND language = USERENV('LANG');
Write2FWKLog('After insert of current organization...');
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
VALUES (p_Top_Organization_ID,'-1');
Write2FWKLog('After insert of rollup organization...');
** The function inserts all the organizations (the user has
** access to) below the selected organization into a session
** specific temporary table. Provided for facilitating
** discoverer reporting.
** ----------------------------------------------------------
*/
Procedure Convert_Organization(p_Top_Organization_ID NUMBER DEFAULT NULL)
AS
l_Security_Profile_ID NUMBER:=fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL');
DELETE PJI_PMV_ORGZ_DIM_TMP;
Write2FWKLog('Before insert of all sub organizations...');
SELECT view_all_organizations_flag,
organization_id
INTO l_View_All_Org_Flag,
l_sec_top_org_id
FROM per_security_profiles
WHERE security_profile_id = l_Security_Profile_ID;
Write2FWKLog('Before insert of all sub-org organizations (w/o security)...');
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
SELECT org.organization_id, org.name
FROM pji_org_denorm denorm
, hr_all_organization_units_tl org
WHERE
denorm.organization_id = l_Top_Organization_ID
AND org.organization_id = denorm.sub_organization_id
AND org.language = USERENV('LANG');
Write2FWKLog('After insert of all sub-org organizations (w/o security)...');
Write2FWKLog('Before insert of all sub-org organizations...');
INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
SELECT org.organization_id, org.name
FROM pji_org_denorm denorm
, hr_all_organization_units_tl org
, per_organization_list seclist
WHERE
denorm.organization_id = l_Top_Organization_ID
AND org.organization_id = denorm.sub_organization_id
AND seclist.security_profile_id = l_Security_Profile_ID
AND seclist.organization_id = denorm.sub_organization_id
AND org.language = USERENV('LANG');
insert_user_assignment_orgz;
Write2FWKLog('After insert of all sub-org organizations...');
Write2FWKLog('After insert of sub organizations...');
** The function inserts all the operating units (the user has
** access to) into a session specific temporary table.
** Additionally this procedure also caches the calender_id's
** for the selected operating unit. These caches values are
** used the convert time apis further.
** This makes it imperative that this api call should always
** precede the call to the Convert_Time API's.
** ----------------------------------------------------------
*/
Procedure Convert_Operating_Unit(p_Operating_Unit_IDS VARCHAR2 DEFAULT NULL
, p_View_BY VARCHAR2)
AS
l_Security_Profile_ID NUMBER:=fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL');
DELETE PJI_PMV_ORG_DIM_TMP;
SELECT gl_calendar_id
, pa_calendar_id
INTO
G_GL_Calendar_ID
, G_PA_Calendar_ID
FROM PJI_ORG_EXTR_INFO
WHERE org_id = l_Dimension_List_Tab(1);
SELECT view_all_organizations_flag
INTO l_View_All_Org_Flag
FROM per_security_profiles
WHERE security_profile_id = l_Security_Profile_ID;
Write2FWKLog('Before insert of operating units (w/o security)...');
INSERT INTO PJI_PMV_ORG_DIM_TMP (ID, NAME)
SELECT paimp.org_id
, decode(p_View_By,'OU',org.name,'-1')
FROM pa_implementations_all paimp
, hr_all_organization_units_tl org
WHERE 1=1
AND org.language = USERENV('LANG')
AND paimp.org_id = org.organization_id;
Write2FWKLog('After insert of operating units (w/o security)...');
Write2FWKLog('Before insert of operating units...');
INSERT INTO PJI_PMV_ORG_DIM_TMP (ID, NAME)
SELECT paimp.org_id
, decode(p_View_By,'OU',org.name,'-1')
FROM pa_implementations_all paimp
, hr_all_organization_units_tl org
, per_organization_list sec
WHERE 1=1
AND org.language = USERENV('LANG')
AND sec.organization_id= paimp.org_id
AND sec.security_profile_id = l_Security_Profile_ID
AND paimp.org_id = org.organization_id;
Write2FWKLog('After insert of operating units...');
INSERT INTO PJI_PMV_ORG_DIM_TMP (ID, NAME)
SELECT organization_id, DECODE(p_View_BY,'OU',name,'-1')
FROM hr_all_organization_units_tl
WHERE
organization_id=l_Dimension_List_Tab(i)
AND language = USERENV('LANG');
INSERT INTO PJI_PMV_ORG_DIM_TMP (ID, NAME)
SELECT organization_id, DECODE(p_View_BY,'OU',name,'-1')
FROM hr_all_organization_units_tl
WHERE
organization_id=l_Dimension_List_Tab(i)
AND language = USERENV('LANG');
** The function inserts all the projects the user has selected
** as parameters in the pmv report to a session specific
** temporary table.
** ----------------------------------------------------------
*/
Procedure Convert_Project(p_Project_IDS VARCHAR2 DEFAULT NULL
, p_View_BY VARCHAR2)
AS
l_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
DELETE PJI_PMV_PRJ_DIM_TMP;
INSERT INTO PJI_PMV_PRJ_DIM_TMP (ID, NAME)
SELECT ID, DECODE(p_View_BY,'PJ',VALUE,'-1')
FROM PJI_PROJECTS_V;
Write2FWKLog('Creating Selected PJs, PJ array is not empty');
INSERT INTO PJI_PMV_PRJ_DIM_TMP (ID, NAME)
SELECT ID, DECODE(p_View_BY,'OU',VALUE,'-1')
FROM PJI_PROJECTS_V
WHERE
ID=l_Dimension_List_Tab(i);
INSERT INTO PJI_PMV_PRJ_DIM_TMP (ID, NAME)
SELECT ID, DECODE(p_View_BY,'OU',VALUE,'-1')
FROM PJI_PROJECTS_V
WHERE
ID=l_Dimension_List_Tab(i);
** Default Period Name: User defined constants to be inserted
** into name column of the time temporary table.
** Default Period ID: User defined constants to be inserted
** into order_by_id column of the time temporary table.
** ----------------------------------------------------------
*/
Procedure Convert_NViewBY_AS_OF_DATE(p_As_Of_Date NUMBER
, p_Period_Type VARCHAR2
, p_Parse_Prior VARCHAR2 DEFAULT NULL
, p_Full_Period_Flag VARCHAR2 DEFAULT NULL
, p_Calendar_ID NUMBER DEFAULT NULL
, p_Default_Period_Name VARCHAR2 DEFAULT NULL
, p_Default_Period_ID NUMBER DEFAULT NULL)
IS
l_Period_Id NUMBER;
Write2FWKLog('PA calender is selected.');
Write2FWKLog('GL calender is selected.');
Write2FWKLog('Ent calender is selected.');
Write2FWKLog('Selecting from FII_TIME_DAY.');
SELECT
ent_period_id, week_id, week_start_date, ent_qtr_id, ent_year_id
INTO
l_Period_Id, l_Week_Id, l_Week_Start_Date, l_Qtr_Id, l_Year_Id
FROM fii_time_day
WHERE
report_date = l_As_Of_Date;
Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
SELECT
cal_period_id, cal_qtr_id, cal_year_id
INTO
l_Period_Id, l_Qtr_Id, l_Year_Id
FROM fii_time_cal_day_mv
WHERE
report_date = l_As_Of_Date
AND calendar_id = l_Calendar_Id;
Write2FWKLog('Selecting Prior Period ID from FII_TIME_CAL_DAY_MV for PA Period Type.');
SELECT cal_period_id
INTO l_Prior_Period_Id
FROM fii_time_cal_day_mv
WHERE 1 = 1
AND report_date = l_Prior_As_Of_Date
AND calendar_id = l_Calendar_Id;
Write2FWKLog('Done selecting Prior Period ID from FII_TIME_CAL_DAY_MV for PA Period Type.');
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, l_Default_Period_Name, l_Default_Period_ID, period_type_id, 1 , calendar_type
FROM fii_time_cal_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, l_Default_Period_Name, l_Default_Period_ID, period_type_id, 1 , calendar_type
FROM fii_time_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, l_Default_Period_Name, l_Default_Period_ID, period_type_id, 1, calendar_type
FROM fii_time_cal_rpt_struct
WHERE report_date = l_Prior_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, l_Default_Period_Name, l_Default_Period_ID, period_type_id, 1, calendar_type
FROM fii_time_rpt_struct
WHERE report_date = l_Prior_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian
, l_Default_Period_Name, l_Default_Period_ID, 1, 1, 'P' FROM fii_time_cal_day_mv
WHERE cal_period_id = l_Period_Id
AND calendar_id = l_Calendar_Id
AND report_date<=l_As_Of_Date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian
,l_Default_Period_Name, l_Default_Period_ID, 1, 1, 'P' FROM fii_time_cal_day_mv
WHERE cal_period_id = l_Prior_Period_Id
AND calendar_id = l_Calendar_Id
AND report_date<=l_Prior_As_Of_Date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Year_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 128, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Qtr_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 64, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Period_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 32, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Week_ID,l_Default_Period_Name, l_Default_Period_ID, 2, 16, 'E');
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (
SUBSTR(LPAD(l_Year_ID,7,'0'),1,3)
||TO_CHAR(SUBSTR(LPAD(l_Year_ID,7,'0'),4,4)-1)
, l_Default_Period_Name, l_Default_Period_ID, 2, 128, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (
SUBSTR(LPAD(l_Qtr_ID,8,'0'),1,3)
||TO_CHAR(SUBSTR(LPAD(l_Qtr_ID,8,'0'),4,4)-1)
||SUBSTR(LPAD(l_Qtr_ID,8,'0'),8)
, l_Default_Period_Name, l_Default_Period_ID, 2, 64, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (
SUBSTR(LPAD(l_Period_ID,10,'0'),1,3)
||TO_CHAR(SUBSTR(LPAD(l_Period_ID,10,'0'),4,4)-1)
||SUBSTR(LPAD(l_Period_ID,10,'0'),8)
, l_Default_Period_Name, l_Default_Period_ID, 2, 32, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (
SUBSTR(LPAD(l_Week_ID,11,'0'),1,3)
||TO_CHAR(SUBSTR(LPAD(l_Week_ID,11,'0'),4,4)-1)
||SUBSTR(LPAD(l_Week_ID,11,'0'),8)
, l_Default_Period_Name, l_Default_Period_ID, 2, 16, 'E');
SELECT rolling_weeks
INTO G_No_Rolling_Weeks
FROM pji_system_settings;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, l_Default_Period_Name, l_Default_Period_ID, period_type_id, 1 , calendar_type
FROM fii_time_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
SELECT week_id, l_Default_Period_Name, l_Default_Period_ID, 2, 16, 'E' FROM fii_time_week WHERE
week_id >= l_Week_ID
AND end_date <= (l_Week_Start_Date)+(G_No_Rolling_Weeks*7);
** Default Period Name: User defined constants to be inserted
** into name column of the time temporary table.
** Default Period ID: User defined constants to be inserted
** into order_by_id column of the time temporary table.
** ----------------------------------------------------------
*/
Procedure Convert_NFViewBY_AS_OF_DATE(p_As_Of_Date NUMBER
, p_Period_Type VARCHAR2
, p_Parse_Prior VARCHAR2 DEFAULT NULL
, p_Full_Period_Flag VARCHAR2 DEFAULT NULL
, p_Calendar_ID NUMBER DEFAULT NULL
, p_Default_Period_Name VARCHAR2 DEFAULT NULL
, p_Default_Period_ID NUMBER DEFAULT NULL)
IS
l_Period_Id NUMBER;
Write2FWKLog('PA calender is selected.');
Write2FWKLog('GL calender is selected.');
Write2FWKLog('Ent calender is selected.');
Write2FWKLog('Selecting from FII_TIME_DAY.');
SELECT period.start_date period_start_date
,qtr.start_date qtr_start_date
,year.start_date year_start_date
,period.end_date period_end_date
,qtr.end_date qtr_end_date
,year.end_date year_end_date
,day.ent_period_id period_id
,day.week_id week_id
,day.ent_qtr_id qtr_id
,day.ent_year_id year_id
INTO
l_Period_Start_Date
,l_Qtr_Start_Date
,l_Year_Start_Date
,l_Period_End_Date
,l_Qtr_End_Date
,l_Year_End_Date
,l_Period_Id
,l_Week_ID
,l_Qtr_Id
,l_Year_Id
FROM fii_time_day day
, fii_time_ent_period period
, fii_time_ent_qtr qtr
, fii_time_ent_year year
WHERE 1=1
AND day.report_date = l_As_Of_Date
AND period.ent_period_id = day.ent_period_id
AND qtr.ent_qtr_id = day.ent_qtr_id
AND year.ent_year_id = day.ent_year_id;
Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
SELECT period.start_date period_start_date
,qtr.start_date qtr_start_date
,year.start_date year_start_date
,period.end_date period_end_date
,qtr.end_date qtr_end_date
,year.end_date year_end_date
,day.cal_period_id period_id
,day.cal_qtr_id qtr_id
,day.cal_year_id year_id
INTO
l_Period_Start_Date
,l_Qtr_Start_Date
,l_Year_Start_Date
,l_Period_End_Date
,l_Qtr_End_Date
,l_Year_End_Date
,l_Period_Id
,l_Qtr_Id
,l_Year_Id
FROM fii_time_cal_day_mv day
, fii_time_cal_period period
, fii_time_cal_qtr qtr
, fii_time_cal_year year
WHERE 1=1
AND day.report_date = l_As_Of_Date
AND period.cal_period_id = day.cal_period_id
AND qtr.cal_qtr_id = day.cal_qtr_id
AND year.cal_year_id = day.cal_year_id
AND day.calendar_id = l_Calendar_Id;
Write2FWKLog('Selecting from FII_TIME_DAY.');
SELECT period.start_date period_start_date
,qtr.start_date qtr_start_date
,year.start_date year_start_date
,period.end_date period_end_date
,qtr.end_date qtr_end_date
,year.end_date year_end_date
INTO
l_Prior_Period_Start_Date
,l_Prior_Qtr_Start_Date
,l_Prior_Year_Start_Date
,l_Prior_Period_End_Date
,l_Prior_Qtr_End_Date
,l_Prior_Year_End_Date
FROM fii_time_day day
, fii_time_ent_period period
, fii_time_ent_qtr qtr
, fii_time_ent_year year
WHERE 1=1
AND day.report_date = l_Prior_As_Of_Date
AND period.ent_period_id = day.ent_period_id
AND qtr.ent_qtr_id = day.ent_qtr_id
AND year.ent_year_id = day.ent_year_id;
Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
SELECT period.start_date period_start_date
,qtr.start_date qtr_start_date
,year.start_date year_start_date
,period.end_date period_end_date
,qtr.end_date qtr_end_date
,year.end_date year_end_date
INTO
l_Prior_Period_Start_Date
,l_Prior_Qtr_Start_Date
,l_Prior_Year_Start_Date
,l_Prior_Period_End_Date
,l_Prior_Qtr_End_Date
,l_Prior_Year_End_Date
FROM fii_time_cal_day_mv day
, fii_time_cal_period period
, fii_time_cal_qtr qtr
, fii_time_cal_year year
WHERE 1=1
AND day.report_date = l_Prior_As_Of_Date
AND period.cal_period_id = day.cal_period_id
AND qtr.cal_qtr_id = day.cal_qtr_id
AND year.cal_year_id = day.cal_year_id
AND day.calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, l_Default_Period_Name, l_Default_Period_ID, 1, 0, l_Calendar_Type_Day
FROM fii_time_cal_day_mv
WHERE
report_date>=l_As_Of_Date
AND calendar_id = l_calendar_id
AND report_date<=l_period_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_period_id, l_Default_Period_Name, l_Default_Period_ID, 32, 0, l_Calendar_Type_Sum
FROM fii_time_cal_period
WHERE
start_date>=l_As_Of_Date
AND calendar_id = l_calendar_id
AND end_date<=l_qtr_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_qtr_id, l_Default_Period_Name, l_Default_Period_ID, 64, 0, l_Calendar_Type_Sum
FROM fii_time_cal_qtr
WHERE
start_date>=l_As_Of_Date
AND calendar_id = l_calendar_id
AND end_date<=l_year_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, l_Default_Period_Name, l_Default_Period_ID, 1, 0, l_Calendar_Type_Day
FROM fii_time_cal_day_mv
WHERE
report_date>=l_prior_As_Of_Date
AND calendar_id = l_calendar_id
AND report_date<=l_prior_period_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_period_id, l_Default_Period_Name, l_Default_Period_ID, 32, 0, l_Calendar_Type_Sum
FROM fii_time_cal_period
WHERE
start_date>=l_prior_As_Of_Date
AND calendar_id = l_calendar_id
AND end_date<=l_prior_qtr_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_qtr_id, l_Default_Period_Name, l_Default_Period_ID, 64, 0, l_Calendar_Type_Sum
FROM fii_time_cal_qtr
WHERE
start_date>=l_prior_As_Of_Date
AND calendar_id = l_calendar_id
AND end_date<=l_prior_year_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, l_Default_Period_Name, l_Default_Period_ID, 1, 0, l_Calendar_Type_Day
FROM fii_time_day
WHERE
report_date>=l_As_Of_Date
AND report_date<=l_period_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_period_id, l_Default_Period_Name, l_Default_Period_ID, 32, 0, l_Calendar_Type_Sum
FROM fii_time_ent_period
WHERE
start_date>=l_As_Of_Date
AND end_date<=l_qtr_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_qtr_id, l_Default_Period_Name, l_Default_Period_ID, 64, 0, l_Calendar_Type_Sum
FROM fii_time_ent_qtr
WHERE
start_date>=l_As_Of_Date
AND end_date<=l_year_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, l_Default_Period_Name, l_Default_Period_ID, 1, 0, l_Calendar_Type_Day
FROM fii_time_day
WHERE
report_date>=l_prior_As_Of_Date
AND report_date<=l_Prior_period_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_period_id, l_Default_Period_Name, l_Default_Period_ID, 32, 0, l_Calendar_Type_Sum
FROM fii_time_ent_period
WHERE
start_date>=l_prior_As_Of_Date
AND end_date<=l_prior_qtr_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_qtr_id, l_Default_Period_Name, l_Default_Period_ID, 64, 0, l_Calendar_Type_Sum
FROM fii_time_ent_qtr
WHERE
start_date>=l_prior_As_Of_Date
AND end_date<=l_prior_year_end_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Year_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 128, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Qtr_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 64, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Period_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 32, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Week_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 16, 'E');
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (
SUBSTR(LPAD(l_Year_ID,7,'0'),1,3)
||TO_CHAR(SUBSTR(LPAD(l_Year_ID,7,'0'),4,4)-1)
, l_Default_Period_Name, l_Default_Period_ID, 2, 128, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (
SUBSTR(LPAD(l_Qtr_ID,8,'0'),1,3)
||TO_CHAR(SUBSTR(LPAD(l_Qtr_ID,8,'0'),4,4)-1)
||SUBSTR(LPAD(l_Qtr_ID,8,'0'),8)
, l_Default_Period_Name, l_Default_Period_ID, 2, 64, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (
SUBSTR(LPAD(l_Period_ID,10,'0'),1,3)
||TO_CHAR(SUBSTR(LPAD(l_Period_ID,10,'0'),4,4)-1)
||SUBSTR(LPAD(l_Period_ID,10,'0'),8)
, l_Default_Period_Name, l_Default_Period_ID, 2, 32, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (
SUBSTR(LPAD(l_Week_ID,11,'0'),1,3)
||TO_CHAR(SUBSTR(LPAD(l_Week_ID,11,'0'),4,4)-1)
||SUBSTR(LPAD(l_Week_ID,11,'0'),8)
, l_Default_Period_Name, l_Default_Period_ID, 2, 16, 'E');
Write2FWKLog('PA calender is selected.');
Write2FWKLog('GL calender is selected.');
Write2FWKLog('Ent calender is selected.');
Write2FWKLog('Selecting from FII_TIME_DAY.');
SELECT
ent_period_id, week_id, ent_qtr_id, ent_year_id
INTO
l_Period_Id, l_Week_Id, l_Qtr_Id, l_Year_Id
FROM fii_time_day
WHERE
report_date = l_As_Of_Date;
Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
SELECT
cal_period_id, cal_qtr_id, cal_year_id
INTO
l_Period_Id, l_Qtr_Id, l_Year_Id
FROM fii_time_cal_day_mv
WHERE
report_date = l_As_Of_Date
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TCMP_DIM_TMP
(ID, NAME, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, '-1', period_type_id, 1 , calendar_type
FROM fii_time_cal_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TCMP_DIM_TMP
(ID, NAME, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, '-1', period_type_id, 1 , calendar_type
FROM fii_time_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TCMP_DIM_TMP
(ID, NAME, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian
, '-1', 1, 1, 'P' FROM fii_time_cal_day_mv
WHERE cal_period_id = l_Period_Id
AND calendar_id = l_Calendar_Id
AND report_date<=l_As_Of_Date;
INSERT INTO PJI_PMV_TCMP_DIM_TMP
(ID, NAME, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Year_ID, '-1', 2, 128, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TCMP_DIM_TMP
(ID, NAME, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Qtr_ID, '-1', 2, 64, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TCMP_DIM_TMP
(ID, NAME, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Period_ID, '-1', 2, 32, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TCMP_DIM_TMP
(ID, NAME, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Week_ID, '-1', 2, 16, 'E');
Write2FWKLog('PA calender is selected.');
Write2FWKLog('GL calender is selected.');
Write2FWKLog('Ent calender is selected.');
Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
SELECT cal_period_id
INTO l_Period_Id
FROM fii_time_cal_day_mv
WHERE
report_date = l_As_Of_Date
AND calendar_id = l_Calendar_Id;
Write2FWKLog('Selecting Prior Period ID from FII_TIME_CAL_DAY_MV for PA Period Type.');
SELECT cal_period_id
INTO l_Prior_Period_Id
FROM fii_time_cal_day_mv
WHERE 1 = 1
AND report_date = l_Prior_As_Of_Date
AND calendar_id = l_Calendar_Id;
Write2FWKLog('Done selecting Prior Period ID from FII_TIME_CAL_DAY_MV for PA Period Type.');
INSERT INTO PJI_PMV_ITD_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
SELECT time_id, '-1' , '-1' , period_type_id, p_Comparator, calendar_type
FROM fii_time_cal_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,1143) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_ITD_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
SELECT time_id, '-1' , '-1' , period_type_id, p_Comparator, calendar_type
FROM fii_time_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,1143) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_ITD_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
SELECT time_id, '-1', '-1', period_type_id, p_Comparator, calendar_type
FROM fii_time_cal_rpt_struct
WHERE report_date = l_Prior_As_Of_Date
AND bitand(record_type_id,1143) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_ITD_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
SELECT time_id, '-1', '-1', period_type_id, p_Comparator, calendar_type
FROM fii_time_rpt_struct
WHERE report_date = l_Prior_As_Of_Date
AND bitand(record_type_id,1143) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_ITD_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
SELECT report_date_julian
, '-1', '-1', 1, p_Comparator, 'P' FROM fii_time_cal_day_mv
WHERE cal_period_id = l_Period_Id
AND calendar_id = l_Calendar_Id
AND report_date<=l_As_Of_Date;
INSERT INTO PJI_PMV_ITD_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
SELECT time_id, '-1', '-1', period_type_id, p_Comparator, 'P'
FROM fii_time_cal_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,1143) = record_type_id
AND calendar_id = l_Calendar_Id
AND period_type_id > 16;
INSERT INTO PJI_PMV_ITD_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
SELECT report_date_julian
, '-1', '-1', 1, p_Comparator, 'P' FROM fii_time_cal_day_mv
WHERE cal_period_id = l_Prior_Period_Id
AND calendar_id = l_Calendar_Id
AND report_date<=l_Prior_As_Of_Date;
INSERT INTO PJI_PMV_ITD_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
SELECT time_id, '-1', '-1', period_type_id, p_Comparator, 'P'
FROM fii_time_cal_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,1143) = record_type_id
AND calendar_id = l_Calendar_Id
AND period_type_id > 16;
** time is selected as the viewby dimension.
** The API caters to additional logic based on following
** parameters:
** Parse Prior: If the flag is passed as 'Y', the API stamps
** prior_id in the time tables.
** Report Type: If a value is passed then the time records are
** bound by year(fiscal/enterprize).
** ----------------------------------------------------------
*/
Procedure Convert_ViewBY_AS_OF_DATE(p_As_Of_Date NUMBER
, p_Period_Type VARCHAR2
, p_Report_Type VARCHAR2
, p_Parse_Prior VARCHAR2 DEFAULT NULL
, p_Full_Period_Flag VARCHAR2 DEFAULT NULL)
AS
l_Start_Time DATE;
Write2FWKLog('PA calender is selected.');
Write2FWKLog('GL calender is selected.');
Write2FWKLog('Ent calender is selected.');
Write2FWKLog('Selecting from FII_TIME_DAY.');
SELECT
day.ent_period_id
, prd.name
, day.ent_period_start_date
, day.week_id
, wek.name
, day.week_start_date
, day.ent_qtr_id
, qtr.name
, day.ent_qtr_start_date
, day.ent_year_id
, yer.name
, day.ent_year_start_date
INTO
l_Period_Id
, l_Period_Name
, l_Period_Start_Date
, l_Week_Id
, l_Week_Name
, l_Week_Start_Date
, l_Qtr_Id
, l_Qtr_Name
, l_Qtr_Start_Date
, l_Year_Id
, l_Year_Name
, l_Year_Start_Date
FROM fii_time_day day
, fii_time_week wek
, fii_time_ent_period prd
, fii_time_ent_qtr qtr
, fii_time_ent_year yer
WHERE
report_date = l_As_Of_Date
AND wek.week_id = day.week_id
AND prd.ent_period_id = day.ent_period_id
AND qtr.ent_qtr_id = day.ent_qtr_id
AND yer.ent_year_id = day.ent_year_id;
Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
SELECT
day.cal_period_id
, prd.name
, day.cal_period_start_date
, day.cal_qtr_id
, qtr.name
, day.cal_qtr_start_date
, day.cal_year_id
, yer.name
, day.cal_year_start_date
INTO
l_Period_Id
, l_Period_Name
, l_Period_Start_Date
, l_Qtr_Id
, l_Qtr_Name
, l_Qtr_Start_Date
, l_Year_Id
, l_Year_Name
, l_Year_Start_Date
FROM fii_time_cal_day_mv day
, fii_time_cal_period prd
, fii_time_cal_qtr qtr
, fii_time_cal_year yer
WHERE
report_date = l_As_Of_Date
AND day.calendar_id = l_Calendar_Id
AND prd.cal_period_id = day.cal_period_id
AND qtr.cal_qtr_id = day.cal_qtr_id
AND yer.cal_year_id = day.cal_year_id;
Write2FWKLog('Done selecting from FII_TIME_???.');
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, l_Def_View_BY, l_Def_View_BY_ID, period_type_id, 1 , calendar_type
FROM fii_time_cal_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, l_Def_View_BY, l_Def_View_BY_ID, period_type_id, 1 , calendar_type
FROM fii_time_rpt_struct
WHERE report_date = l_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, l_Def_View_BY, l_Def_View_BY_ID, period_type_id, 1, calendar_type
FROM fii_time_cal_rpt_struct
WHERE report_date = l_Prior_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT time_id, l_Def_View_BY, l_Def_View_BY_ID, period_type_id, 1, calendar_type
FROM fii_time_rpt_struct
WHERE report_date = l_Prior_As_Of_Date
AND bitand(record_type_id,l_Level) = record_type_id
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian
, l_Def_View_BY, l_Def_View_BY_ID, 1, 1, 'P' FROM fii_time_cal_day_mv
WHERE cal_period_id = l_Period_Id
AND calendar_id = l_Calendar_Id
AND report_date<=l_As_Of_Date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian
, l_Def_View_BY, l_Def_View_BY_ID, 1, 1, 'P' FROM fii_time_cal_day_mv
WHERE cal_period_id = l_Prior_Period_Id
AND calendar_id = l_Calendar_Id
AND report_date<=l_Prior_As_Of_Date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 128, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 64, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 32, l_Calendar_Type_Sum);
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 16, 'E');
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
SELECT ent_year_id, name, ent_year_id, 128, 'E'
FROM fii_time_ent_year
WHERE start_date > l_End_Time
AND start_date < l_Start_Time;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
SELECT ent_qtr_id, name, ent_qtr_id, 64, 'E'
FROM fii_time_ent_qtr
WHERE start_date > l_End_Time
AND start_date < l_Start_Time;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
SELECT ent_period_id, name, ent_period_id, 32, 'E'
FROM fii_time_ent_period
WHERE start_date > l_End_Time
AND start_date < l_Start_Time;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
SELECT cal_year_id, name, cal_year_id, 128, 'G'
FROM fii_time_cal_year
WHERE start_date > l_End_Time
AND start_date < l_Start_Time
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
SELECT cal_qtr_id, name, cal_qtr_id, 64, 'G'
FROM fii_time_cal_qtr
WHERE start_date > l_End_Time
AND start_date < l_Start_Time
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
SELECT cal_period_id, name, cal_period_id, 32, 'G'
FROM fii_time_cal_period
WHERE start_date > l_End_Time
AND start_date < l_Start_Time
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
SELECT cal_period_id, name, cal_period_id, 32, 'P'
FROM fii_time_cal_period
WHERE start_date > l_End_Time
AND start_date < l_Start_Time
AND calendar_id = l_Calendar_Id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
SELECT week_id, name, week_id, 16, 'E'
FROM fii_time_week
WHERE start_date > l_End_Time
AND start_date < l_Start_Time;
UPDATE PJI_PMV_TIME_DIM_TMP
SET PRIOR_ID = (CASE period_type
WHEN 128 THEN SUBSTR(LPAD(ID,7,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,7,'0'),4,4)-1)
WHEN 64 THEN SUBSTR(LPAD(ID,8,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,8,'0'),4,4)-1)||SUBSTR(LPAD(ID,8,'0'),8)
WHEN 32 THEN SUBSTR(LPAD(ID,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,10,'0'),4,4)-1)||SUBSTR(LPAD(ID,10,'0'),8)
WHEN 16 THEN SUBSTR(LPAD(ID,11,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,11,'0'),4,4)-1)||SUBSTR(LPAD(ID,11,'0'),8)
END)
WHERE AMOUNT_TYPE = 2
OR AMOUNT_TYPE IS NULL;
Write2FWKLog('Prior ID is updated.');
SELECT MIN(TIME.start_date)-1
INTO l_As_Of_Date
FROM
pji_pmv_time_dim_tmp TTMP,
fii_time_ent_year TIME
WHERE TTMP.period_type = l_Level
AND TTMP.id = TIME.ent_year_id;
SELECT MIN(TIME.start_date)-1
INTO l_As_Of_Date
FROM
pji_pmv_time_dim_tmp TTMP,
fii_time_cal_year TIME
WHERE TTMP.period_type = l_Level
AND TTMP.id = TIME.cal_year_id;
SELECT MIN(TIME.start_date)-1
INTO l_As_Of_Date
FROM
pji_pmv_time_dim_tmp TTMP,
fii_time_ent_qtr TIME
WHERE TTMP.period_type = l_Level
AND TTMP.id = TIME.ent_qtr_id;
SELECT MIN(TIME.start_date)-1
INTO l_As_Of_Date
FROM
pji_pmv_time_dim_tmp TTMP,
fii_time_cal_qtr TIME
WHERE TTMP.period_type = l_Level
AND TTMP.id = TIME.cal_qtr_id;
SELECT MIN(TIME.start_date)-1
INTO l_As_Of_Date
FROM
pji_pmv_time_dim_tmp TTMP,
fii_time_ent_period TIME
WHERE TTMP.period_type = l_Level
AND TTMP.id = TIME.ent_period_id;
SELECT MIN(TIME.start_date)-1
INTO l_As_Of_Date
FROM
pji_pmv_time_dim_tmp TTMP,
fii_time_cal_period TIME
WHERE TTMP.period_type = l_Level
AND TTMP.id = TIME.cal_period_id;
SELECT MIN(TIME.start_date)-1
INTO l_As_Of_Date
FROM
pji_pmv_time_dim_tmp TTMP,
fii_time_cal_period TIME
WHERE TTMP.period_type = l_Level
AND TTMP.id = TIME.cal_period_id;
SELECT MIN(TIME.start_date)-1
INTO l_As_Of_Date
FROM
pji_pmv_time_dim_tmp TTMP,
fii_time_week TIME
WHERE TTMP.period_type = l_Level
AND TTMP.id = TIME.week_id;
DELETE PJI_PMV_TIME_DIM_TMP;
Write2FWKLog('PA calender is selected.');
Write2FWKLog('GL calender is selected.');
INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ENT_YEAR_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 128, ENT_YEAR_ID, NULL, 'E'
FROM FII_TIME_ENT_YEAR
WHERE l_Start_Date <= end_date
AND l_End_Date >=start_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ENT_QTR_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 64, ENT_QTR_ID, NULL, 'E'
FROM FII_TIME_ENT_QTR
WHERE l_Start_Date <= end_date
AND l_End_Date >=start_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ENT_PERIOD_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 32, ENT_PERIOD_ID, NULL, 'E'
FROM FII_TIME_ENT_PERIOD
WHERE l_Start_Date <= end_date
AND l_End_Date >=start_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT CAL_YEAR_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 128, CAL_YEAR_ID, NULL, 'G'
FROM FII_TIME_CAL_YEAR
WHERE l_Start_Date <= end_date
AND l_End_Date >=start_date
AND calendar_id = l_Calendar_ID;
INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT CAL_QTR_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 64, CAL_QTR_ID, NULL, 'G'
FROM FII_TIME_CAL_QTR
WHERE l_Start_Date <= end_date
AND l_End_Date >=start_date
AND calendar_id = l_Calendar_ID;
INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT CAL_PERIOD_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 32, CAL_PERIOD_ID, NULL, 'G'
FROM FII_TIME_CAL_PERIOD
WHERE l_Start_Date <= end_date
AND l_End_Date >=start_date
AND calendar_id = l_Calendar_ID;
INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT CAL_PERIOD_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 32, CAL_PERIOD_ID, NULL, 'G'
FROM FII_TIME_CAL_PERIOD
WHERE l_Start_Date <= end_date
AND l_End_Date >=start_date
AND calendar_id = l_Calendar_ID;
INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT WEEK_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 16, WEEK_ID, NULL, 'E'
FROM FII_TIME_WEEK
WHERE l_Start_Date <= end_date
AND l_End_Date >=start_date;
UPDATE PJI_PMV_TIME_DIM_TMP
SET PRIOR_ID = (CASE period_type
WHEN 128 THEN SUBSTR(LPAD(ID,7,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,7,'0'),4,4)-1)
WHEN 64 THEN SUBSTR(LPAD(ID,8,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,8,'0'),4,4)-1)||SUBSTR(LPAD(ID,8,'0'),8)
WHEN 32 THEN SUBSTR(LPAD(ID,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,10,'0'),4,4)-1)||SUBSTR(LPAD(ID,10,'0'),8)
WHEN 16 THEN SUBSTR(LPAD(ID,11,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,11,'0'),4,4)-1)||SUBSTR(LPAD(ID,11,'0'),8)
END)
WHERE period_type<>1;
Write2FWKLog('Prior ID is updated.');
DELETE PJI_PMV_TIME_DIM_TMP;
DELETE PJI_PMV_ITD_DIM_TMP;
DELETE PJI_PMV_TCMP_DIM_TMP;
DELETE PJI_PMV_TIME_DIM_TMP;
Write2FWKLog('PA calendar is selected.');
Write2FWKLog('GL calendar is selected.');
Write2FWKLog('ENT calendar is selected.');
Write2FWKLog('Selecting from FII_TIME_DAY for as_of_date and last_summ_date');
SELECT ent_period_id, ent_period_start_date,
ent_qtr_id, ent_qtr_start_date,
ent_year_id, ent_year_start_date,
week_id, week_start_date
INTO l_period_id, l_period_start_date,
l_qtr_id, l_qtr_start_date,
l_year_id, l_year_start_date,
l_week_id, l_week_start_date
FROM fii_time_day
WHERE report_date = l_as_of_date;
SELECT ent_period_id, ent_period_start_date,
ent_qtr_id, ent_qtr_start_date,
ent_year_id, ent_year_start_date,
week_id, week_start_date
INTO l_period_id_s, l_period_start_date_s,
l_qtr_id_s, l_qtr_start_date_s,
l_year_id_s, l_year_start_date_s,
l_week_id_s, l_week_start_date_s
FROM fii_time_day
WHERE report_date = l_summ_date;
Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV for as_of_date and last_summ_date');
SELECT cal_period_id, cal_period_start_date,
cal_qtr_id, cal_qtr_start_date,
cal_year_id, cal_year_start_date
INTO l_period_id, l_period_start_date,
l_qtr_id, l_qtr_start_date,
l_year_id, l_year_start_date
FROM fii_time_cal_day_mv
WHERE report_date = l_as_of_date
AND calendar_id = l_calendar_id;
SELECT cal_period_id, cal_period_start_date,
cal_qtr_id, cal_qtr_start_date,
cal_year_id, cal_year_start_date
INTO l_period_id_s, l_period_start_date_s,
l_qtr_id_s, l_qtr_start_date_s,
l_year_id_s, l_year_start_date_s
FROM fii_time_cal_day_mv
WHERE report_date = l_summ_date
AND calendar_id = l_calendar_id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
FROM fii_time_day, dual
WHERE week_id = l_week_id_tmp
AND report_date <= l_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_day
WHERE week_id = SUBSTR(LPAD(l_week_id_tmp,11,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_week_id_tmp,11,'0'),4,4)-1)||SUBSTR(LPAD(l_week_id_tmp,11,'0'),8)
AND report_date <= l_prior_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
FROM fii_time_day, dual
WHERE ent_period_id = l_period_id_tmp
AND report_date <= l_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_day
WHERE ent_period_id = SUBSTR(LPAD(l_period_id_tmp,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id_tmp,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id_tmp,10,'0'),8)
AND report_date <= l_prior_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_cal_day_mv, dual
WHERE cal_period_id = l_period_id_tmp
AND calendar_id = l_calendar_id
AND report_date <= l_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_cal_day_mv
WHERE cal_period_id = SUBSTR(LPAD(l_period_id_tmp,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id_tmp,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id_tmp,10,'0'),8)
AND calendar_id = l_calendar_id
AND report_date <= l_prior_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
FROM fii_time_ent_period, dual
WHERE ent_qtr_id = l_qtr_id_tmp
AND start_date < l_period_start_date_tmp;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
FROM fii_time_cal_period, dual
WHERE cal_qtr_id = l_qtr_id_tmp
AND calendar_id = l_calendar_id
AND start_date < l_period_start_date_tmp;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
FROM fii_time_ent_qtr, dual
WHERE ent_year_id = l_year_id_tmp
AND start_date < l_qtr_start_date_tmp;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
FROM fii_time_cal_qtr, dual
WHERE cal_year_id = l_year_id_tmp
AND calendar_id = l_calendar_id
AND start_date < l_qtr_start_date_tmp;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_year_id, '-1', '-1', 128 , l_amount_type, l_calendar_type_sum
FROM fii_time_ent_year, dual
WHERE start_date < l_year_start_date_tmp;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_year_id, '-1', '-1', 128 , l_amount_type, l_calendar_type_sum
FROM fii_time_cal_year, dual
WHERE calendar_id = l_calendar_id
AND start_date < l_year_start_date_tmp;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
FROM fii_time_day, dual
WHERE week_id = l_week_id
AND report_date > l_summ_date
AND report_date <= l_as_of_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_day
WHERE week_id = SUBSTR(LPAD(l_week_id,11,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_week_id,11,'0'),4,4)-1)||SUBSTR(LPAD(l_week_id,11,'0'),8)
AND report_date > l_prior_summ_date
AND report_date <= l_prior_as_of_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
FROM fii_time_day, dual
WHERE ent_period_id = l_period_id
AND report_date > l_summ_date
AND report_date <= l_as_of_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_day
WHERE ent_period_id = SUBSTR(LPAD(l_period_id,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id,10,'0'),8)
AND report_date > l_prior_summ_date
AND report_date <= l_prior_as_of_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_cal_day_mv, dual
WHERE cal_period_id = l_period_id
AND calendar_id = l_calendar_id
AND report_date > l_summ_date
AND report_date <= l_as_of_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_cal_day_mv
WHERE cal_period_id = SUBSTR(LPAD(l_period_id,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id,10,'0'),8)
AND calendar_id = l_calendar_id
AND report_date > l_prior_summ_date
AND report_date <= l_prior_as_of_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
FROM fii_time_day, dual
WHERE ent_period_id = l_period_id_s
AND report_date > l_summ_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_day
WHERE ent_period_id = SUBSTR(LPAD(l_period_id_s,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id_s,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id_s,10,'0'),8)
AND report_date > l_prior_summ_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
FROM fii_time_day, dual
WHERE ent_period_id = l_period_id
AND report_date <= l_as_of_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_day
WHERE ent_period_id = SUBSTR(LPAD(l_period_id,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id,10,'0'),8)
AND report_date <= l_prior_as_of_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_cal_day_mv, dual
WHERE cal_period_id = l_period_id_s
AND calendar_id = l_calendar_id
AND report_date > l_summ_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_cal_day_mv
WHERE cal_period_id = SUBSTR(LPAD(l_period_id_s,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id_s,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id_s,10,'0'),8)
AND calendar_id = l_calendar_id
AND report_date > l_prior_summ_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_cal_day_mv, dual
WHERE cal_period_id = l_period_id
AND calendar_id = l_calendar_id
AND report_date <= l_as_of_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
FROM fii_time_cal_day_mv
WHERE cal_period_id = SUBSTR(LPAD(l_period_id,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id,10,'0'),8)
AND calendar_id = l_calendar_id
AND report_date <= l_prior_as_of_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
FROM fii_time_ent_period, dual
WHERE ent_qtr_id = l_qtr_id
AND start_date < l_period_start_date
AND start_date > l_period_start_date_s;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
FROM fii_time_cal_period, dual
WHERE cal_qtr_id = l_qtr_id_s
AND calendar_id = l_calendar_id
AND start_date < l_period_start_date
AND start_date > l_period_start_date_s;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
FROM fii_time_ent_period, dual
WHERE ent_qtr_id = l_qtr_id_s
AND start_date > l_period_start_date_s;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
FROM fii_time_ent_period, dual
WHERE ent_qtr_id = l_qtr_id
AND start_date < l_period_start_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
FROM fii_time_cal_period, dual
WHERE cal_qtr_id = l_qtr_id_s
AND calendar_id = l_calendar_id
AND start_date > l_period_start_date_s;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
FROM fii_time_cal_period, dual
WHERE cal_qtr_id = l_qtr_id
AND calendar_id = l_calendar_id
AND start_date < l_period_start_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
FROM fii_time_ent_qtr, dual
WHERE ent_year_id = l_year_id
AND start_date < l_qtr_start_date
AND start_date > l_qtr_start_date_s;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
FROM fii_time_cal_qtr, dual
WHERE cal_year_id = l_year_id
AND calendar_id = l_calendar_id
AND start_date < l_qtr_start_date
AND start_date > l_qtr_start_date_s;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
FROM fii_time_ent_qtr, dual
WHERE ent_year_id = l_year_id_s
AND start_date > l_qtr_start_date_s;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
FROM fii_time_ent_qtr, dual
WHERE ent_year_id = l_year_id
AND start_date < l_qtr_start_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
FROM fii_time_cal_qtr, dual
WHERE cal_year_id = l_year_id_s
AND calendar_id = l_calendar_id
AND start_date > l_qtr_start_date_s;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
FROM fii_time_cal_qtr, dual
WHERE cal_year_id = l_year_id
AND calendar_id = l_calendar_id
AND start_date < l_qtr_start_date;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT ent_year_id, '-1', '-1', 128 , l_amount_type, l_calendar_type_sum
FROM fii_time_ent_year, dual
WHERE start_date < l_year_start_date
AND start_date > l_year_start_date_s;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
SELECT cal_year_id, '-1', '-1', 128 , l_amount_type, l_calendar_type_sum
FROM fii_time_cal_year, dual
WHERE calendar_id = l_calendar_id
AND start_date < l_year_start_date
AND start_date > l_year_start_date_s;
UPDATE PJI_PMV_TIME_DIM_TMP
SET PRIOR_ID =
(CASE period_type
WHEN 128 THEN SUBSTR(LPAD(ID,7,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,7,'0'),4,4)-1)
WHEN 64 THEN SUBSTR(LPAD(ID,8,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,8,'0'),4,4)-1)||SUBSTR(LPAD(ID,8,'0'),8)
WHEN 32 THEN SUBSTR(LPAD(ID,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,10,'0'),4,4)-1)||SUBSTR(LPAD(ID,10,'0'),8)
WHEN 16 THEN SUBSTR(LPAD(ID,11,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,11,'0'),4,4)-1)||SUBSTR(LPAD(ID,11,'0'),8)
END)
WHERE period_type<>1;
Write2FWKLog('Prior ID is updated.');
DELETE PJI_PMV_TIME_DIM_TMP;
SELECT
day.week_id
, wek.name
, wek.end_date
INTO l_Week_Id,
l_Week_Name,
l_End_Date
FROM fii_time_day day
, fii_time_week wek
WHERE
report_date = to_date(p_as_of_date,'j') -- As Of Date
AND wek.week_id = day.week_id;
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
SELECT report_date_julian
, l_Week_Name, l_Week_Id, 1, 'C' FROM fii_time_day
WHERE week_id = l_Week_Id
AND report_date>=to_date(p_as_of_date,'j');
INSERT INTO PJI_PMV_TIME_DIM_TMP
(ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
SELECT id, name, id, 16, 'C'
FROM (
SELECT week_id id,name name FROM fii_time_week
WHERE end_date>l_End_Date
ORDER BY 1 ASC)
WHERE ROWNUM < 13;
SELECT attribute_code attribute_code
, attribute2 dimension_level
, attribute3 base_column
, attribute15 view_by_table
BULK COLLECT INTO
G_dimension_codes_tab
, G_dimension_level_tab
, G_dim_base_column_tab
, G_view_by_table_tab
FROM
AK_REGION_ITEMS
WHERE region_code = p_Region_Code
AND region_application_id = 1292
AND attribute1 IS NOT NULL;
SELECT attribute_code attribute_code
, attribute3 base_column
, attribute4 attribute4
, attribute9 aggregation
BULK COLLECT INTO
G_attribute_code_tab
, G_msr_base_column_tab
, G_attribute4_tab
, G_aggregation_tab
FROM
AK_REGION_ITEMS
WHERE region_code = p_Region_Code
AND region_application_id = 1292
AND attribute9 IS NOT NULL
ORDER BY display_sequence;
** Function: Construct_SELECT_Clause
** This Function constructs the select statement to be
** returned to PMV. All unknown variables (at this point of
** time) are replaced with patterns. These patterns are
** later replaced with actual values in the Generate_SQL API.
** ----------------------------------------------------------
*/
Function Construct_SELECT_Clause(p_View_BY IN VARCHAR2)
RETURN VARCHAR2 IS
l_Buffer VARCHAR2(200);
l_Select_List VARCHAR2(3200):=' SELECT ';
Write2FWKLog('Entering Construct_Select_Clause...','Construct_Select_Clause');
l_Select_List:=l_Select_List||'FACT.'||l_Buffer||' "VIEWBY" ';
l_Select_List:=l_Select_List||'1 "CONSTANT" ';
l_Select_List:=l_Select_List||l_Buffer||' "'||G_attribute_code_tab(i)||'"';
l_Select_List:=l_Select_List||l_Buffer;
l_Select_List:=l_Select_List||' FROM TABLE(<>(<>)) FACT ';
l_Select_List:=l_Select_List||' &ORDER_BY_CLAUSE ';
Write2FWKLog('l_Select_List :'||l_Select_List);
Write2FWKLog('Exiting Construct_Select_Clause...');
RETURN l_Select_List;
END Construct_Select_Clause;
** Function: Construct_SELECT_Clause
** This Function constructs the select statement to be
** returned to PMV. All unknown variables (at this point of
** time) are replaced with patterns. These patterns are
** later replaced with actual values in the Generate_SQL API.
** ----------------------------------------------------------
*/
Function Construct_SELECT_Clause(p_View_BY IN VARCHAR2, p_Select_List IN VARCHAR2)
RETURN VARCHAR2 IS
l_Buffer VARCHAR2(200);
l_Select_List VARCHAR2(3200):=' SELECT ';
Write2FWKLog('Entering Construct_Select_Clause...','Construct_Select_Clause');
l_Select_List:=l_Select_List||'FACT.'||l_Buffer||' "VIEWBY" ';
l_Select_List:=l_Select_List||'1 "CONSTANT" ';
l_Select_List:=l_Select_List||', '||p_Select_List;
l_Select_List:=l_Select_List||' FROM TABLE(<>(<>)) FACT ';
l_Select_List:=l_Select_List||' &ORDER_BY_CLAUSE ';
Write2FWKLog('l_Select_List :'||l_Select_List);
Write2FWKLog('Exiting Construct_Select_Clause...');
RETURN l_Select_List;
END Construct_Select_Clause;
** This Function generates the select statement based on the
** parameters selected by the user.
** ----------------------------------------------------------
*/
Procedure Generate_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
, p_SQL_Statement IN OUT NOCOPY VARCHAR2
, p_PMV_Output IN OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
, p_Region_Code IN VARCHAR2
, p_PLSQL_Driver IN VARCHAR2
, p_PLSQL_Driver_Params IN VARCHAR2
)
IS
l_Sql_Statement VARCHAR2(3200);
Write2FWKLog('Before calling Construct_SELECT_Clause.','Generate_SQL');
l_Sql_Statement:=Construct_SELECT_Clause(l_View_BY);
Write2FWKLog('After calling Construct_SELECT_Clause.','Generate_SQL');
** This Function generates the select statement based on the
** parameters selected by the user.
** ----------------------------------------------------------
*/
Procedure Generate_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
, p_Select_List IN VARCHAR2
, p_SQL_Statement IN OUT NOCOPY VARCHAR2
, p_PMV_Output IN OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
, p_Region_Code IN VARCHAR2
, p_PLSQL_Driver IN VARCHAR2
, p_PLSQL_Driver_Params IN VARCHAR2
)
IS
l_Sql_Statement VARCHAR2(3200);
Write2FWKLog('Before calling Construct_SELECT_Clause.','Generate_SQL');
l_Sql_Statement:=Construct_SELECT_Clause(l_View_BY, p_Select_List);
Write2FWKLog('After calling Construct_SELECT_Clause.','Generate_SQL');