判断料号在那些org里面
CREATE OR REPLACE FUNCTION CHECK_ITEM_ORG(P_ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
CURSOR C IS
SELECT OOD.ORGANIZATION_CODE
FROM MTL_SYSTEM_ITEMS_B MSI,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE MSI.INVENTORY_ITEM_ID=P_ITEM_ID
AND MSI.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_STATUS_CODE='Active'
AND OOD.DISABLE_DATE IS NULL
AND MSI.ORGANIZATION_ID<>1
ORDER BY OOD.ORGANIZATION_CODE;
C_TEMP VARCHAR2(5);
RESULT VARCHAR2(1000);
BEGIN
OPEN C;
LOOP
FETCH C INTO C_TEMP;
EXIT WHEN C%NOTFOUND;
IF RESULT IS NULL THEN
RESULT:=C_TEMP;
ELSIF C_TEMP IS NOT NULL OR C_TEMP<>'' THEN
RESULT:=RESULT||','||C_TEMP;
END IF;
END LOOP;
CLOSE C;
RETURN RESULT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '';
WHEN OTHERS THEN
RETURN '';
END;