FUNCTION GET_LOC(
P_ITEM IN VARCHAR2
) RETURN VARCHAR IS
V_RETURN VARCHAR2(30);
BEGIN ----BEGIN1
--------------------------------SELECT1
SELECT '库存-'||C.SEGMENT1
INTO V_RETURN
FROM INV.MTL_ONHAND_QUANTITIES A,
INV.MTL_SYSTEM_ITEMS B,
INV.MTL_ITEM_LOCATIONS C
WHERE A.ORGANIZATION_ID=B.ORGANIZATION_ID
AND A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID
AND C.ORGANIZATION_ID=4
AND C.SUBINVENTORY_CODE=A.SUBINVENTORY_CODE
AND A.LOCATOR_ID=C.INVENTORY_LOCATION_ID
AND A.SUBINVENTORY_CODE='C010整机'
AND B.ORGANIZATION_ID=4
AND B.SEGMENT1 =P_ITEM ;
RETURN(V_RETURN);
EXCEPTION WHEN OTHERS THEN ----SELECT1没有返回值时,才由SELECT2给返回值
BEGIN ----BEGIN2
--------------------------------SELECT2
SELECT /*MMT.TRANSACTION_ID,
MMT.SUBINVENTORY_CODE,
MMT.TRANSACTION_TYPE_ID,
MMT.TRANSACTION_ACTION_ID,
MTT.TRANSACTION_TYPE_NAME,
MMT.TRANSACTION_QUANTITY,
MMT.TRANSACTION_REFERENCE,
MMT.TRANSACTION_DATE ,*/
'T在制-'||MIL.SEGMENT1
INTO V_RETURN
FROM INV.MTL_MATERIAL_TRANSACTIONS MMT,
INV.MTL_ITEM_LOCATIONS MIL,
INV.MTL_TRANSACTION_TYPES MTT
WHERE MMT.ORGANIZATION_ID=MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID=MIL.INVENTORY_LOCATION_ID(+)
AND MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
AND MMT.TRANSACTION_ID=(SELECT MAX(MMT2.TRANSACTION_ID)
FROM INV.MTL_MATERIAL_TRANSACTIONS MMT2,
INV.MTL_SYSTEM_ITEMS MSI2
WHERE MSI2.ORGANIZATION_ID=MMT2.ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID=MMT2.INVENTORY_ITEM_ID
AND MMT2.TRANSACTION_ACTION_ID+0=2 --调拔
AND MMT2.SUBINVENTORY_CODE||''='C010整机'
AND MSI2.ORGANIZATION_ID=4
AND MSI2.SEGMENT1=P_ITEM
AND NOT EXISTS (SELECT 1
FROM INV.MTL_MATERIAL_TRANSACTIONS MMT3
WHERE MMT3.TRANSACTION_ACTION_ID+0=1 --发放
--AND MMT3.TRANSACTION_TYPE_ID+0=33 --销售订单发放
AND MMT3.TRANSACTION_DATE>MMT2.TRANSACTION_DATE
AND MMT3.ORGANIZATION_ID=MMT2.ORGANIZATION_ID
AND MMT3.INVENTORY_ITEM_ID=MMT2.INVENTORY_ITEM_ID)
);
RETURN(V_RETURN);
EXCEPTION WHEN OTHERS THEN SELECT2没有返回值时,才由SELECT3给返回值
BEGIN ----BEGIN3
--------------------------------SELECT3
SELECT '在制-'||DECODE(SUBSTR(WDJ.CLASS_CODE,1,2),'FX','FQ凤翔','CZC总厂')
INTO V_RETURN
FROM WIP.WIP_ENTITIES WE,
WIP.WIP_DISCRETE_JOBS WDJ,
INV.MTL_SYSTEM_ITEMS MSI
WHERE WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
AND WE.PRIMARY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND WE.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND WDJ.STATUS_TYPE IN (1,3,4,5,6) --1 UNRELEASED,3 RELEASED,4 COMPLETE,5 COMPLETE - NO CHARGES,6 ON HOLD
AND WE.WIP_ENTITY_NAME LIKE 'Z%'
AND MSI.ORGANIZATION_ID=4
AND MSI.SEGMENT1 =P_ITEM;
RETURN(V_RETURN);
EXCEPTION WHEN OTHERS THEN
RETURN(NULL);
END; ----BEGIN3
END;----BEGIN2
END; ----BEGIN1当参数P_ITEM为ZPKU9005时,SELECT1的返回值为:
库存-CZC总厂
库存-CTH退货SELECT2的返回值为:
T在制-CZC总厂SELECT3无返回值当我调用这个函数时,ZD_PUB_GET.GET_LOC('ZPKU9005') ;
返回值是:T在制-CZC总厂 即SELECT2的返回值
按这个函数的逻辑,应该是SELECT1没有返回值时,才由SELECT2给返回值
SELECT1是有两个返回值的呀,为什么函数却只返回SELECT2的值??????
哪位兄弟帮帮忙啊
P_ITEM IN VARCHAR2
) RETURN VARCHAR IS
V_RETURN VARCHAR2(30);
BEGIN ----BEGIN1
--------------------------------SELECT1
SELECT '库存-'||C.SEGMENT1
INTO V_RETURN
FROM INV.MTL_ONHAND_QUANTITIES A,
INV.MTL_SYSTEM_ITEMS B,
INV.MTL_ITEM_LOCATIONS C
WHERE A.ORGANIZATION_ID=B.ORGANIZATION_ID
AND A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID
AND C.ORGANIZATION_ID=4
AND C.SUBINVENTORY_CODE=A.SUBINVENTORY_CODE
AND A.LOCATOR_ID=C.INVENTORY_LOCATION_ID
AND A.SUBINVENTORY_CODE='C010整机'
AND B.ORGANIZATION_ID=4
AND B.SEGMENT1 =P_ITEM ;
RETURN(V_RETURN);
EXCEPTION WHEN OTHERS THEN ----SELECT1没有返回值时,才由SELECT2给返回值
BEGIN ----BEGIN2
--------------------------------SELECT2
SELECT /*MMT.TRANSACTION_ID,
MMT.SUBINVENTORY_CODE,
MMT.TRANSACTION_TYPE_ID,
MMT.TRANSACTION_ACTION_ID,
MTT.TRANSACTION_TYPE_NAME,
MMT.TRANSACTION_QUANTITY,
MMT.TRANSACTION_REFERENCE,
MMT.TRANSACTION_DATE ,*/
'T在制-'||MIL.SEGMENT1
INTO V_RETURN
FROM INV.MTL_MATERIAL_TRANSACTIONS MMT,
INV.MTL_ITEM_LOCATIONS MIL,
INV.MTL_TRANSACTION_TYPES MTT
WHERE MMT.ORGANIZATION_ID=MIL.ORGANIZATION_ID(+)
AND MMT.LOCATOR_ID=MIL.INVENTORY_LOCATION_ID(+)
AND MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
AND MMT.TRANSACTION_ID=(SELECT MAX(MMT2.TRANSACTION_ID)
FROM INV.MTL_MATERIAL_TRANSACTIONS MMT2,
INV.MTL_SYSTEM_ITEMS MSI2
WHERE MSI2.ORGANIZATION_ID=MMT2.ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID=MMT2.INVENTORY_ITEM_ID
AND MMT2.TRANSACTION_ACTION_ID+0=2 --调拔
AND MMT2.SUBINVENTORY_CODE||''='C010整机'
AND MSI2.ORGANIZATION_ID=4
AND MSI2.SEGMENT1=P_ITEM
AND NOT EXISTS (SELECT 1
FROM INV.MTL_MATERIAL_TRANSACTIONS MMT3
WHERE MMT3.TRANSACTION_ACTION_ID+0=1 --发放
--AND MMT3.TRANSACTION_TYPE_ID+0=33 --销售订单发放
AND MMT3.TRANSACTION_DATE>MMT2.TRANSACTION_DATE
AND MMT3.ORGANIZATION_ID=MMT2.ORGANIZATION_ID
AND MMT3.INVENTORY_ITEM_ID=MMT2.INVENTORY_ITEM_ID)
);
RETURN(V_RETURN);
EXCEPTION WHEN OTHERS THEN SELECT2没有返回值时,才由SELECT3给返回值
BEGIN ----BEGIN3
--------------------------------SELECT3
SELECT '在制-'||DECODE(SUBSTR(WDJ.CLASS_CODE,1,2),'FX','FQ凤翔','CZC总厂')
INTO V_RETURN
FROM WIP.WIP_ENTITIES WE,
WIP.WIP_DISCRETE_JOBS WDJ,
INV.MTL_SYSTEM_ITEMS MSI
WHERE WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
AND WE.PRIMARY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND WE.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND WDJ.STATUS_TYPE IN (1,3,4,5,6) --1 UNRELEASED,3 RELEASED,4 COMPLETE,5 COMPLETE - NO CHARGES,6 ON HOLD
AND WE.WIP_ENTITY_NAME LIKE 'Z%'
AND MSI.ORGANIZATION_ID=4
AND MSI.SEGMENT1 =P_ITEM;
RETURN(V_RETURN);
EXCEPTION WHEN OTHERS THEN
RETURN(NULL);
END; ----BEGIN3
END;----BEGIN2
END; ----BEGIN1当参数P_ITEM为ZPKU9005时,SELECT1的返回值为:
库存-CZC总厂
库存-CTH退货SELECT2的返回值为:
T在制-CZC总厂SELECT3无返回值当我调用这个函数时,ZD_PUB_GET.GET_LOC('ZPKU9005') ;
返回值是:T在制-CZC总厂 即SELECT2的返回值
按这个函数的逻辑,应该是SELECT1没有返回值时,才由SELECT2给返回值
SELECT1是有两个返回值的呀,为什么函数却只返回SELECT2的值??????
哪位兄弟帮帮忙啊
when too_many_rows then
库存-CZC总厂
库存-CTH退货老大,这返回了两条啊!!!
too_many_rows!