VIEW: APPS.CSP_RS_SUBINVENTORIES_V

Source

View Text - Preformatted

SELECT i.csp_inv_loc_assignment_id, i.created_by, i.creation_date, i.last_updated_by, i.last_update_date, i.last_update_login, i.resource_type, i.resource_id, csp_pick_utils.get_object_name(i.resource_type,i.resource_id), i.organization_id, o.organization_code, i.subinventory_code, s.secondary_inventory_id, s.location_id, i.locator_id, s.condition_type, ct.meaning condition_type_meaning, decode(i.default_code,null,'N','Y'), i.default_code, dc.meaning default_code_meaning, i.effective_date_start, i.effective_date_end, decode(i.resource_id ,s.owner_resource_id, decode(i.resource_type,s.owner_resource_type, 'Y', 'N'), 'N') owner_flag, jrgt.group_name, s.group_id, s.owner_resource_type, s.owner_resource_id, csp_pick_utils.get_object_name(s.owner_resource_type,s.owner_resource_id), s.return_organization_id, ro.organization_code return_organization_code, s.return_subinventory_name, rs.condition_type, rct.meaning from csp_inv_loc_assignments i, csp_sec_inventories s, org_organization_definitions o, fnd_lookups dc, fnd_lookups ct, fnd_lookups rct, csp_sec_inventories rs, org_organization_definitions ro, jtf_rs_groups_tl jrgt where i.organization_id = s.organization_id (+) and i.subinventory_code = s.secondary_inventory_name (+) and s.condition_type = ct.lookup_code (+) and ct.lookup_type (+) = 'CSP_CONDITION_TYPE' and i.default_code = dc.lookup_code (+) and dc.lookup_type (+) = 'CSP_SUBINV_DEFAULT_USE_TYPE' and i.organization_id = o.organization_id and s.return_organization_id = ro.organization_id (+) and s.return_organization_id = rs.organization_id(+) and s.return_subinventory_name = rs.secondary_inventory_name(+) and s.group_id = jrgt.group_id(+) and jrgt.language(+) = userenv('LANG') and rs.condition_type = rct.lookup_code(+) and rct.lookup_type(+) = 'CSP_CONDITION_TYPE'

View Text - HTML Formatted

SELECT I.CSP_INV_LOC_ASSIGNMENT_ID
, I.CREATED_BY
, I.CREATION_DATE
, I.LAST_UPDATED_BY
, I.LAST_UPDATE_DATE
, I.LAST_UPDATE_LOGIN
, I.RESOURCE_TYPE
, I.RESOURCE_ID
, CSP_PICK_UTILS.GET_OBJECT_NAME(I.RESOURCE_TYPE
, I.RESOURCE_ID)
, I.ORGANIZATION_ID
, O.ORGANIZATION_CODE
, I.SUBINVENTORY_CODE
, S.SECONDARY_INVENTORY_ID
, S.LOCATION_ID
, I.LOCATOR_ID
, S.CONDITION_TYPE
, CT.MEANING CONDITION_TYPE_MEANING
, DECODE(I.DEFAULT_CODE
, NULL
, 'N'
, 'Y')
, I.DEFAULT_CODE
, DC.MEANING DEFAULT_CODE_MEANING
, I.EFFECTIVE_DATE_START
, I.EFFECTIVE_DATE_END
, DECODE(I.RESOURCE_ID
, S.OWNER_RESOURCE_ID
, DECODE(I.RESOURCE_TYPE
, S.OWNER_RESOURCE_TYPE
, 'Y'
, 'N')
, 'N') OWNER_FLAG
, JRGT.GROUP_NAME
, S.GROUP_ID
, S.OWNER_RESOURCE_TYPE
, S.OWNER_RESOURCE_ID
, CSP_PICK_UTILS.GET_OBJECT_NAME(S.OWNER_RESOURCE_TYPE
, S.OWNER_RESOURCE_ID)
, S.RETURN_ORGANIZATION_ID
, RO.ORGANIZATION_CODE RETURN_ORGANIZATION_CODE
, S.RETURN_SUBINVENTORY_NAME
, RS.CONDITION_TYPE
, RCT.MEANING
FROM CSP_INV_LOC_ASSIGNMENTS I
, CSP_SEC_INVENTORIES S
, ORG_ORGANIZATION_DEFINITIONS O
, FND_LOOKUPS DC
, FND_LOOKUPS CT
, FND_LOOKUPS RCT
, CSP_SEC_INVENTORIES RS
, ORG_ORGANIZATION_DEFINITIONS RO
, JTF_RS_GROUPS_TL JRGT
WHERE I.ORGANIZATION_ID = S.ORGANIZATION_ID (+)
AND I.SUBINVENTORY_CODE = S.SECONDARY_INVENTORY_NAME (+)
AND S.CONDITION_TYPE = CT.LOOKUP_CODE (+)
AND CT.LOOKUP_TYPE (+) = 'CSP_CONDITION_TYPE'
AND I.DEFAULT_CODE = DC.LOOKUP_CODE (+)
AND DC.LOOKUP_TYPE (+) = 'CSP_SUBINV_DEFAULT_USE_TYPE'
AND I.ORGANIZATION_ID = O.ORGANIZATION_ID
AND S.RETURN_ORGANIZATION_ID = RO.ORGANIZATION_ID (+)
AND S.RETURN_ORGANIZATION_ID = RS.ORGANIZATION_ID(+)
AND S.RETURN_SUBINVENTORY_NAME = RS.SECONDARY_INVENTORY_NAME(+)
AND S.GROUP_ID = JRGT.GROUP_ID(+)
AND JRGT.LANGUAGE(+) = USERENV('LANG')
AND RS.CONDITION_TYPE = RCT.LOOKUP_CODE(+)
AND RCT.LOOKUP_TYPE(+) = 'CSP_CONDITION_TYPE'