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
