VIEW: APPS.PER_MX_GRES_LEGAL_EMPLOYER_V

Source

View Text - Preformatted

SELECT le_node.entity_id legal_employer_id, hoi.org_information1 legal_employer_name , hoi.org_information2 rfc_id, gre_node.entity_id tax_unit_id, hou.name gre_name, hoi1.org_information1 ss_id , hrl.region_1 state , fcl.meaning state_name , le_node.business_group_id business_group_id from per_gen_hierarchy_nodes le_node, per_gen_hierarchy_nodes gre_node, per_gen_hierarchy_versions hier_ver, fnd_sessions sess, fnd_lookup_values lv, fnd_common_lookups fcl, hr_organization_units hou, hr_locations hrl, hr_organization_information hoi, hr_organization_information hoi1 where le_node.node_type='MX LEGAL EMPLOYER' and gre_node.parent_hierarchy_node_id = le_node.hierarchy_node_id and gre_node.node_type='MX GRE' and gre_node.hierarchy_version_id = hier_ver.hierarchy_version_id and hier_ver.status = lv.lookup_code and lv.meaning = 'Active' and lv.LANGUAGE = 'US' and lv.lookup_type = 'PQH_GHR_HIER_VRSN_STATUS' and hou.organization_id = gre_node.entity_id and hoi.organization_id = le_node.entity_id and hoi.org_information_context = 'MX_TAX_REGISTRATION' and hoi1.organization_id = gre_node.entity_id and hoi1.org_information_context = 'MX_SOC_SEC_DETAILS' and hrl.location_id = hou.location_id and fcl.lookup_type= 'MX_STATE' and fcl.lookup_code = hrl.region_1 and sess.session_id = USERENV('sessionid') and sess.effective_date BETWEEN hier_ver.date_from AND NVL(hier_ver.date_to, TO_DATE('12/31/4712', 'MM/DD/YYYY'))

View Text - HTML Formatted

SELECT LE_NODE.ENTITY_ID LEGAL_EMPLOYER_ID
, HOI.ORG_INFORMATION1 LEGAL_EMPLOYER_NAME
, HOI.ORG_INFORMATION2 RFC_ID
, GRE_NODE.ENTITY_ID TAX_UNIT_ID
, HOU.NAME GRE_NAME
, HOI1.ORG_INFORMATION1 SS_ID
, HRL.REGION_1 STATE
, FCL.MEANING STATE_NAME
, LE_NODE.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
FROM PER_GEN_HIERARCHY_NODES LE_NODE
, PER_GEN_HIERARCHY_NODES GRE_NODE
, PER_GEN_HIERARCHY_VERSIONS HIER_VER
, FND_SESSIONS SESS
, FND_LOOKUP_VALUES LV
, FND_COMMON_LOOKUPS FCL
, HR_ORGANIZATION_UNITS HOU
, HR_LOCATIONS HRL
, HR_ORGANIZATION_INFORMATION HOI
, HR_ORGANIZATION_INFORMATION HOI1
WHERE LE_NODE.NODE_TYPE='MX LEGAL EMPLOYER'
AND GRE_NODE.PARENT_HIERARCHY_NODE_ID = LE_NODE.HIERARCHY_NODE_ID
AND GRE_NODE.NODE_TYPE='MX GRE'
AND GRE_NODE.HIERARCHY_VERSION_ID = HIER_VER.HIERARCHY_VERSION_ID
AND HIER_VER.STATUS = LV.LOOKUP_CODE
AND LV.MEANING = 'ACTIVE'
AND LV.LANGUAGE = 'US'
AND LV.LOOKUP_TYPE = 'PQH_GHR_HIER_VRSN_STATUS'
AND HOU.ORGANIZATION_ID = GRE_NODE.ENTITY_ID
AND HOI.ORGANIZATION_ID = LE_NODE.ENTITY_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'MX_TAX_REGISTRATION'
AND HOI1.ORGANIZATION_ID = GRE_NODE.ENTITY_ID
AND HOI1.ORG_INFORMATION_CONTEXT = 'MX_SOC_SEC_DETAILS'
AND HRL.LOCATION_ID = HOU.LOCATION_ID
AND FCL.LOOKUP_TYPE= 'MX_STATE'
AND FCL.LOOKUP_CODE = HRL.REGION_1
AND SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN HIER_VER.DATE_FROM
AND NVL(HIER_VER.DATE_TO
, TO_DATE('12/31/4712'
, 'MM/DD/YYYY'))