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的值??????
哪位兄弟帮帮忙啊
解决方案 »
- 是多表连接查询效率高还是多次单表查询效率高啊
- oracle菜鸟问题
- 在plsql中,使用绑定变量的方法除了动态sql外,还有别的方式吗
- oracle中查询最多的数据的问题
- 如何使用系统触发器?
- 再求一个查询办法。
- 请教怎样修复一个几百G的图像分区表的其中一个分区的数据文件上的坏块?????????在线等待!!!!!!!!!!!!!!
- 关于临时表的问题,一定要用同一个Connection才能查到结果吗?
- 在linux装了oracle的客户端,怎么没有这些目录:
- 数据库缓存服务请教?标题要长.............................
- delete优化问题
- oracle表定时同步到mysql问题
when too_many_rows then
库存-CZC总厂
库存-CTH退货老大,这返回了两条啊!!!
too_many_rows!