Oracle EBS Report开发笔记2

CAJAN.Z

V_OUT_QTY NUMBER;

V_OUT_AMOUNT NUMBER; --舍入调整

V_ROUND_AMOUNT NUMBER; --其他调整

V_OTHER_AMOUNT NUMBER; --月结期间有交易、现有量的物料 CURSOR C_ITEMS IS

SELECT V.ORGANIZATION_ID ,

V.INVENTORY_ITEM_ID ,

V.SUBINVENTORY_CODE

FROM MTL_SYSTEM_ITEMS_VL MSI, ( --月结期间交易的物料

SELECT MMT.ORGANIZATION_ID, MMT.INVENTORY_ITEM_ID, MMT.SUBINVENTORY_CODE

FROM MTL_MATERIAL_TRANSACTIONS MMT WHERE 1 = 1

AND MMT.ORGANIZATION_ID = V_ORGANIZATION_ID AND MMT.SUBINVENTORY_CODE =

NVL(V_SUBINV, MMT.SUBINVENTORY_CODE) AND MMT.SUBINVENTORY_CODE NOT LIKE '%Stage%' AND MMT.TRANSACTION_DATE BETWEEN V_DATE_FROM AND --上次月结日 V_DATE_TO --本次月结日 UNION

--当前在手量

SELECT MOQ.ORGANIZATION_ID ,

MOQ.INVENTORY_ITEM_ID ,

MOQ.SUBINVENTORY_CODE

FROM MTL_ONHAND_QUANTITIES MOQ WHERE 1 = 1

AND MOQ.ORGANIZATION_ID = V_ORGANIZATION_ID AND MOQ.SUBINVENTORY_CODE NOT LIKE '%Stage%' AND MOQ.SUBINVENTORY_CODE =

NVL(V_SUBINV, MOQ.SUBINVENTORY_CODE)) V WHERE 1 = 1

AND V.ORGANIZATION_ID = MSI.ORGANIZATION_ID

AND V.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID

AND MSI.ITEM_TYPE = NVL(V_ITEM_TYPE, MSI.ITEM_TYPE) --检查物料类别 ORDER BY 3, 2 ;

--物料类别

V_SQL_CATE VARCHAR2(32767) := 'select mic.category_id from mtl_item_categories_v mic

where mic.ORGANIZATION_ID = :organization_id and mic.INVENTORY_ITEM_ID = :inventory_item_id and mic.CATEGORY_SET_NAME = ''库存'''; BEGIN

/* fnd_global.apps_initialize(user_id => p_user_id, resp_id => p_resp_id, resp_appl_id => p_appl_id);*/ --删除未过账记录

IF V_STATUS = 'N' THEN BEGIN DELETE

FROM CUX_INV_CLOSE_AMOUNT WHERE 1 = 1

AND ORGANIZATION_ID = V_ORGANIZATION_ID

AND SUBINVENTORY_CODE = NVL(V_SUBINV, SUBINVENTORY_CODE) AND PROCESS_FLAG != 'P' AND CLOSE_DATE >

9

CAJAN.Z

(SELECT NVL(MAX(CLOSE_DATE), SYSDATE) FROM CUX_INV_CLOSE_DATE WHERE 1 = 1

AND ORGANIZATION_ID = V_ORGANIZATION_ID

AND NVL(SUBINVENTORY_CODE, '---') = NVL(V_SUBINV, '---') AND PROCESS_FLAG = 'P' ); DELETE

FROM CUX_INV_CLOSE_DATE WHERE 1 = 1

AND ORGANIZATION_ID = V_ORGANIZATION_ID

AND NVL(SUBINVENTORY_CODE, '---') = NVL(V_SUBINV, '---') AND PROCESS_FLAG != 'P' AND CLOSE_DATE >

(SELECT NVL(MAX(CLOSE_DATE), SYSDATE) FROM CUX_INV_CLOSE_DATE WHERE 1 = 1

AND ORGANIZATION_ID = V_ORGANIZATION_ID

AND NVL(SUBINVENTORY_CODE, '---') = NVL(V_SUBINV, '---') AND PROCESS_FLAG = 'P' );

LOG('删除未过账记录完成'); EXCEPTION

WHEN OTHERS THEN

LOG('删除未过账记录异常'); END; END IF;

--1、产生记录 FOR V_ITEMS IN C_ITEMS LOOP --物料本月类别 BEGIN

EXECUTE IMMEDIATE V_SQL_CATE INTO V_CATEGORY_CODE

USING V_ITEMS.ORGANIZATION_ID, V_ITEMS.INVENTORY_ITEM_ID; EXCEPTION

WHEN OTHERS THEN

V_CATEGORY_CODE := '-'; END;

--物料上月类别及现有量、金额 BEGIN

SELECT ITEM_CATEGORY，NVL(CLOSE_QTY, 0), NVL(CLOSE_AMOUNT, 0) INTO V_OLD_CATEGORY_CODE, V_START_QTY, V_START_AMOUNT FROM CUX_INV_CLOSE_AMOUNT WHERE 1 = 1

AND ORGANIZATION_ID = V_ITEMS.ORGANIZATION_ID

AND SUBINVENTORY_CODE = V_ITEMS.SUBINVENTORY_CODE AND INVENTORY_ITEM_ID = V_ITEMS.INVENTORY_ITEM_ID AND CLOSE_DATE =

(SELECT MAX(CLOSE_DATE)

FROM CUX_INV_CLOSE_AMOUNT WHERE 1 = 1

AND ORGANIZATION_ID = V_ITEMS.ORGANIZATION_ID

AND INVENTORY_ITEM_ID = V_ITEMS.INVENTORY_ITEM_ID); EXCEPTION

WHEN OTHERS THEN

V_OLD_CATEGORY_CODE := V_CATEGORY_CODE; V_START_QTY := 0;

V_START_AMOUNT := 0; END;

-------------------------------------------------------------------- --当前在手量,及平均成本 BEGIN

V_ONHAND_COST := 0;

SELECT NVL(SUM(NVL(MOQ.TRANSACTION_QUANTITY, 0)), 0) TRANSACTION_QUANTITY INTO V_ONHAND_QTY

FROM MTL_ONHAND_QUANTITIES MOQ

10

CAJAN.Z

WHERE 1 = 1

AND MOQ.ORGANIZATION_ID = V_ITEMS.ORGANIZATION_ID

AND MOQ.INVENTORY_ITEM_ID = V_ITEMS.INVENTORY_ITEM_ID AND MOQ.SUBINVENTORY_CODE = V_ITEMS.SUBINVENTORY_CODE ;

SELECT NVL(CQL.ITEM_COST, 0) ITEM_COST INTO V_ONHAND_COST

FROM CST_QUANTITY_LAYERS CQL WHERE 1 = 1

AND CQL.ORGANIZATION_ID = V_ITEMS.ORGANIZATION_ID

AND CQL.INVENTORY_ITEM_ID = V_ITEMS.INVENTORY_ITEM_ID ;

EXCEPTION

WHEN OTHERS THEN V_ONHAND_QTY := 0; V_ONHAND_COST := 0; END;

/*--期末数量

v_close_qty := v_onhand_qty - v_transaction_qty; --期末金额

v_close_amount := v_onhand_qty * v_onhand_cost - v_transaction_amt;*/ ------------------------------------------------------------------------- --期末数量,金额

SELECT NVL(SUM(NVL(MMT.PRIMARY_QUANTITY, 0)), 0) PRIMARY_QUANTITY,

NVL(SUM(NVL(MTA.BASE_TRANSACTION_VALUE, 0)), 0) BASE_TRANSACTION_VALUE INTO V_CLOSE_QTY, V_CLOSE_AMOUNT

FROM MTL_MATERIAL_TRANSACTIONS MMT, MTL_TRANSACTION_ACCOUNTS MTA WHERE 1 = 1

AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID AND MTA.accounting_line_type =1

AND MMT.ORGANIZATION_ID = V_ITEMS.ORGANIZATION_ID

AND MMT.INVENTORY_ITEM_ID = V_ITEMS.INVENTORY_ITEM_ID AND MMT.SUBINVENTORY_CODE = V_ITEMS.SUBINVENTORY_CODE AND MMT.TRANSACTION_TYPE_ID <> 10008 AND MMT.TRANSACTION_DATE <= V_DATE_TO ;

------------------------------------------------------------------------- --本期出库数量,金额

SELECT NVL(SUM(NVL(MMT.PRIMARY_QUANTITY, 0)), 0) PRIMARY_QUANTITY,

NVL(SUM(NVL(MTA.BASE_TRANSACTION_VALUE, 0)), 0) BASE_TRANSACTION_VALUE INTO V_OUT_QTY, V_OUT_AMOUNT

FROM MTL_MATERIAL_TRANSACTIONS MMT, MTL_TRANSACTION_ACCOUNTS MTA WHERE 1 = 1

AND MMT.PRIMARY_QUANTITY < 0

AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID AND MTA.PRIMARY_QUANTITY >0

AND MMT.ORGANIZATION_ID = V_ITEMS.ORGANIZATION_ID

AND MMT.INVENTORY_ITEM_ID = V_ITEMS.INVENTORY_ITEM_ID AND MMT.SUBINVENTORY_CODE = V_ITEMS.SUBINVENTORY_CODE AND MMT.TRANSACTION_TYPE_ID <> 10008 AND MMT.TRANSACTION_DATE > V_DATE_FROM

AND MMT.TRANSACTION_DATE <= V_DATE_TO ;

--本期 入库数量,金额(含成本更新）

SELECT NVL(SUM(NVL(MMT.PRIMARY_QUANTITY, 0)), 0) PRIMARY_QUANTITY,

NVL(SUM(NVL(MTA.BASE_TRANSACTION_VALUE, 0)), 0) BASE_TRANSACTION_VALUE INTO V_IN_QTY, V_IN_AMOUNT

FROM MTL_MATERIAL_TRANSACTIONS MMT ,

MTL_TRANSACTION_ACCOUNTS MTA WHERE 1 = 1

AND MMT.PRIMARY_QUANTITY >= 0

AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID AND MTA.PRIMARY_QUANTITY > 0

AND MMT.ORGANIZATION_ID = V_ITEMS.ORGANIZATION_ID

11

CAJAN.Z

AND MMT.INVENTORY_ITEM_ID = V_ITEMS.INVENTORY_ITEM_ID AND MMT.SUBINVENTORY_CODE = V_ITEMS.SUBINVENTORY_CODE AND MMT.TRANSACTION_TYPE_ID <> 10008 AND MMT.TRANSACTION_DATE > V_DATE_FROM

AND MMT.TRANSACTION_DATE <= V_DATE_TO ;

-------------------------------------------------------------------------------- --差异金额

IF V_START_QTY != 0 OR V_IN_QTY != 0 OR V_OUT_QTY != 0 OR V_CLOSE_QTY != 0

OR V_START_AMOUNT != 0 OR V_IN_AMOUNT != 0 OR V_OUT_AMOUNT != 0 OR V_CLOSE_AMOUNT != 0 THEN

IF V_OLD_CATEGORY_CODE = V_CATEGORY_CODE THEN --舍入差异

V_ROUND_AMOUNT := 0; V_OTHER_AMOUNT := 0;

IF V_START_AMOUNT != 0 AND V_CLOSE_AMOUNT != 0 AND V_IN_AMOUNT != 0 AND V_OUT_AMOUNT != 0 THEN

V_ROUND_AMOUNT := V_CLOSE_AMOUNT + V_OUT_AMOUNT - V_IN_AMOUNT - V_START_AMOUNT; ELSE

--平均成本更新差异+舍入差异+其他

V_OTHER_AMOUNT := V_CLOSE_AMOUNT + V_OUT_AMOUNT - V_IN_AMOUNT - V_START_AMOUNT; END IF;

INSERT INTO CUX_INV_CLOSE_AMOUNT (TRANSACTION_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, ITEM_CATEGORY,

SUBINVENTORY_CODE, CLOSE_DATE, START_QTY,

START_AMOUNT, IN_QTY,

IN_AMOUNT, OUT_QTY,

OUT_AMOUNT, ROUND_AMOUNT, OTHER_AMOUNT, CLOSE_QTY,

CLOSE_AMOUNT, UNIT_COST) VALUES

(CUX_INV_CLOSE_AMOUNT_S.NEXTVAL, V_ORGANIZATION_ID,

V_ITEMS.INVENTORY_ITEM_ID, V_CATEGORY_CODE,

V_ITEMS.SUBINVENTORY_CODE, V_DATE_TO, V_START_QTY,

V_START_AMOUNT, V_IN_QTY,

V_IN_AMOUNT, V_OUT_QTY,

V_OUT_AMOUNT, V_ROUND_AMOUNT, V_OTHER_AMOUNT, V_CLOSE_QTY,

V_CLOSE_AMOUNT, V_ONHAND_COST);

12