-
-

Element Costing

ELEMENTS

Element Costing

SELECT PLDG.NAME LEG_DATA_GRP_EC1, PETF.BASE_ELEMENT_NAME Ele_Name_EC1, PETL.DESCRIPTION Legacy_code_EC1, PECV_PRI.CLASSIFICATION_NAME Pri_Class_EC1, PECV_SEC.CLASSIFICATION_NAME Sec_Class_EC1, NULL Co_code_EC1, PELF.ELEMENT_LINK_NAME Ele_Elig_EC1, LEG_EMP.NAME Leg_Emp_EC1, HAOUF.NAME Department_EC1, PJFV.NAME Job_Name_EC1, -- PGFV.NAME Grade_Name_EC1, --(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='EMP_CAT' AND LOOKUP_CODE=PEC.EMPLOYMENT_CATEGORY AND ENABLED_FLAG='Y')Employement_Category_EC1, --HALF.LOCATION_NAME Location_EC1, --HAPF.NAME Position_EC1, --PAPF.PAYROLL_NAME Payroll_EC1, PJFV.JOB_CODE JOB_CODE_EC1, NULL DEPT_CODE_ELE_EC1, -- --NULL Building_LLS_EC1, -- --NULL Function_LLS_EC1, PETF.BASE_ELEMENT_NAME Costed_ELE_EC1, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='PAY_COSTABLE_TYPE' AND LOOKUP_CODE=PCIF.COSTABLE_TYPE)Cost_Type_EC1, -- --PCIF.TRANSFER_TO_GL_FLAG Tranf_GL_EC1, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='YES_NO' AND LOOKUP_CODE=PCIF.TRANSFER_TO_GL_FLAG AND ENABLED_FLAG='Y') Tranf_GL_EC1, --POGV.DESCRIPTION DISTRIBUTION_GRP_EC1, -- PIVV.NAME INPUT_VALUE_ELE_EC1, -- DECODE(PCIF.COSTED_FLAG,'Y','Yes','N','No',NULL,'No') COSTED_FLAG_EC1, -- HAOUF.ATTRIBUTE6 Co_Id_EC1, COST_ACNT.SEGMENT1 COST_COMPANY_EC1, COST_ACNT.SEGMENT2 COST_CURRENCY_EC1, COST_ACNT.SEGMENT3 COST_ACCOUNT_EC1, COST_ACNT.SEGMENT4 COST_COST_CEN_EC1, COST_ACNT.SEGMENT5 COST_PROFIT_CEN_EC1, -- OFFSET_ACNT.SEGMENT1 OFFSET_COMPANY_EC1, OFFSET_ACNT.SEGMENT2 OFFSET_CURRENCY_EC1, OFFSET_ACNT.SEGMENT3 OFFSET_ACCOUNT_EC1, OFFSET_ACNT.SEGMENT4 OFFSET_COST_CEN_EC1, OFFSET_ACNT.SEGMENT5 OFFSET_PROFIT_CEN_EC1 FROM PAY_ELEMENT_LINKS_F PELF, PAY_ELEMENT_TYPES_F PETF, PAY_ELEMENT_TYPES_TL PETL, PER_LEGISLATIVE_DATA_GROUPS_VL PLDG, PAY_ELEMENT_CRITERIA PEC, PAY_ELE_CLASSIFICATIONS_VL PECV_PRI, PAY_ELE_CLASSIFICATIONS_VL PECV_SEC, HR_ALL_ORGANIZATION_UNITS_F_VL LEG_EMP, HR_ALL_ORGANIZATION_UNITS_F_VL HAOUF, PER_JOBS_F_VL PJFV, -- PER_GRADES_F_VL PGFV, -- HR_ALL_POSITIONS_F_VL HAPF, --HR_LOCATIONS_ALL_F_VL HALF, PAY_COST_ALLOCATIONS_F PCAF, --PAY_ALL_PAYROLLS_F PAPF, PAY_COST_INFO_F PCIF, PAY_COST_ALLOC_ACCOUNTS COST_ACNT, PAY_COST_ALLOC_ACCOUNTS OFFSET_ACNT, PAY_OBJECT_GROUPS_VL POGV, PAY_OBJECT_GROUP_TYPES POGT -- PAY_INPUT_VALUES_VL PIVV WHERE PETL.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID AND PETF.LEGISLATIVE_DATA_GROUP_ID = PLDG.LEGISLATIVE_DATA_GROUP_ID AND PETL.LANGUAGE = 'US' AND PETF.CLASSIFICATION_ID = PECV_PRI.BASE_CLASSIFICATION_ID AND PETF.SECONDARY_CLASSIFICATION_ID = PECV_SEC.BASE_CLASSIFICATION_ID AND PECV_PRI.BASE_CLASSIFICATION_ID = PECV_SEC.PARENT_CLASSIFICATION_ID AND PLDG.LEGISLATION_CODE = PECV_PRI.LEGISLATION_CODE AND PLDG.LEGISLATION_CODE = PECV_SEC.LEGISLATION_CODE AND PELF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID AND PELF.LEGISLATIVE_DATA_GROUP_ID = PLDG.LEGISLATIVE_DATA_GROUP_ID --AND PETF.BASE_ELEMENT_NAME = 'AD and D Employee Results' and PLDG.NAME = 'CA Legislative Data Group' AND PELF.ELEMENT_CRITERIA_ID = PEC.ELEMENT_CRITERIA_ID AND LEG_EMP.ORGANIZATION_ID(+) = PEC.LEGAL_EMPLOYER_ID AND HAOUF.ORGANIZATION_ID(+) = PEC.ORGANIZATION_ID AND PEC.JOB_ID = PJFV.JOB_ID(+) --AND PEC.GRADE_ID = PGFV.GRADE_ID(+) --AND PEC.POSITION_ID = HAPF.POSITION_ID(+) --AND PEC.LOCATION_ID = HALF.LOCATION_ID(+) --AND PEC.PAYROLL_ID = PAPF.PAYROLL_ID(+) AND PELF.ELEMENT_LINK_ID = PCAF.SOURCE_ID AND PCAF.SOURCE_TYPE(+) = 'EL' AND PCAF.SOURCE_ID = PCIF.SOURCE_ID(+) AND PCAF.COST_ALLOCATION_RECORD_ID = COST_ACNT.COST_ALLOCATION_RECORD_ID AND COST_ACNT.SOURCE_SUB_TYPE = 'COST' AND PCAF.LEGISLATIVE_DATA_GROUP_ID = PLDG.LEGISLATIVE_DATA_GROUP_ID AND PCAF.COST_ALLOCATION_RECORD_ID = OFFSET_ACNT.COST_ALLOCATION_RECORD_ID(+) AND OFFSET_ACNT.SOURCE_SUB_TYPE(+) = 'BAL' AND POGV.OBJECT_GROUP_TYPE_ID(+) = POGT.OBJECT_GROUP_TYPE_ID AND POGT.NAME_CODE(+) = 'ELEGRP' --AND PCIF.DISTRIBUTION_SET_ID = POGV.OBJECT_GROUP_ID(+) --AND PETF.ELEMENT_TYPE_ID = PIVV.ELEMENT_TYPE_ID AND TRUNC(:P_AS_OF_DATE) BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE AND TRUNC(:P_AS_OF_DATE) BETWEEN PELF.EFFECTIVE_START_DATE AND PELF.EFFECTIVE_END_DATE AND TRUNC(:P_AS_OF_DATE) BETWEEN PJFV.EFFECTIVE_START_DATE(+) AND PJFV.EFFECTIVE_END_DATE(+) AND TRUNC(:P_AS_OF_DATE) BETWEEN PECV_PRI.DATE_FROM AND NVL(PECV_PRI.DATE_TO,:P_AS_OF_DATE + 1) AND TRUNC(:P_AS_OF_DATE) BETWEEN PECV_SEC.DATE_FROM AND NVL(PECV_SEC.DATE_TO,:P_AS_OF_DATE + 1) AND TRUNC(:P_AS_OF_DATE) BETWEEN LEG_EMP.EFFECTIVE_START_DATE(+) AND LEG_EMP.EFFECTIVE_END_DATE(+) AND TRUNC(:P_AS_OF_DATE) BETWEEN HAOUF.EFFECTIVE_START_DATE(+) AND HAOUF.EFFECTIVE_END_DATE(+) --AND TRUNC(:P_AS_OF_DATE) BETWEEN PGFV.EFFECTIVE_START_DATE(+) AND PGFV.EFFECTIVE_END_DATE(+) --AND TRUNC(:P_AS_OF_DATE) BETWEEN HAPF.EFFECTIVE_START_DATE(+) AND HAPF.EFFECTIVE_END_DATE(+) --AND TRUNC(:P_AS_OF_DATE) BETWEEN PAPF.EFFECTIVE_START_DATE(+) AND PAPF.EFFECTIVE_END_DATE(+) AND TRUNC(:P_AS_OF_DATE) BETWEEN PCAF.EFFECTIVE_START_DATE AND PCAF.EFFECTIVE_END_DATE AND ((:P_CREATION_TYPE= 'Custom' AND COST_ACNT.created_by <> 'SEED_DATA_FROM_APPLICATION' ) OR (:P_CREATION_TYPE = 'Seeded' AND COST_ACNT.created_by = 'SEED_DATA_FROM_APPLICATION' ) OR (:P_CREATION_TYPE = 'Seeded Modified' AND COST_ACNT.created_by = 'SEED_DATA_FROM_APPLICATION' AND COST_ACNT.last_updated_by <> 'SEED_DATA_FROM_APPLICATION' ) OR (:P_CREATION_TYPE = 'ALL') ) ORDER BY 1,2,7