[oracle erp] INV exam

題目一：

1. 請依第一頁結構圖利用套用 Template 的方式建立料號 ITEM-A, ITEM-B, ITEM-C, ITEM-D, ITEM-E, ITEM-F, ITEM-G並 assign to TYO Org，並使用SQL語法抓取 TYO Org料號資料，需有以下欄位: Organization_Id, Organization_Code, Inventory_item_id, Item, Item_Description, Primary_UOM, Item_Type, Item_Status, Make_or_Buy, Supply_Type, Include_in_Rollup (25%)

• 作答方式: 請將 SQL 語法及 SQL結果貼上。

select

mtl.inventory_item_id as Inventory_item_id,

mtl.segment1 as item,

mtl.description as item_Description,

mtl.primary_unit_of_measure as Primary_UOM,

mtl.item_type as Item_Type,

mtl.inventory_item_status_code as Item_Status,

msi.PLANNING_MAKE_BUY_CODE as Make_or_Buy,

msi.WIP_SUPPLY_TYPE as SUPPLY_TYPE,

msi.DEFAULT_INCLUDE_IN_ROLLUP_FLAG as Include_in_Rollup

from MTL_SYSTEM_ITEMS_B MTL, fnd_user MAN, MTL_SYSTEM_ITEMS_FVL MSI

where MTL.created_by = MAN.user_id

and MAN.user_name = 'Y16019'

and mtl.inventory_item_id = msi.INVENTORY_ITEM_ID

題目二：請於TYO Org建立以下資訊：(25%)

1. 建立倉別 SUB-A 儲位 SUB-A-1
2. 設定料號單位轉換 ITEM-D 100pcs=1box
3. 於倉別 SUB-A 儲位 SUB-A-1雜收 ITEM-A, ITEM-B, ITEM-C, ITEM-D, ITEM-E, ITEM-F, ITEM-G 各1000
4. 於倉別 SUB-A 儲位 SUB-A-1將 ITEM-D 轉倉至 TYO-MR1 儲位A101 共1box，請使用 box 為交易單位

• 作答方式: 請將料號 ITEM-D的 Material Transaction 與 Onhand Quantity 畫面貼上。

題目三：

請使用SQL語法，抓取第2題的在 SUB-A倉所有料號彙總庫存，需有以下欄位: Organization_Code, Item, Item_Description, Item_Revision, Subinventory, Locator, Onhand_Qty (25%)

• 作答方式: 請將 SQL 語法貼上

select Organization_Code,

Item,

Item_Description,

Revision,

Subinventory_code,

Locator,

On_hand

FROM MTL_ONHAND_TOTAL_MWB_V

WHERE 1=1

AND locator_id = '17274'

AND subinventory_code = 'SUB-A2-19'

AND organization_id = '96'

題目四：

請使用 SQL 語法，抓取第2題所發生的交易，以及各交易對應的Account Distributions。需有以下欄位: Organization_code, Item, Revision, Subinventory, Locator, Transfer_Org, Transfer_Subinventory, Transfer_Locator, Primary_Qty, Transaction_Date, Transaction_Type (25%)

• 作答方式: 請將 SQL 語法及結果貼上

SELECT MTRA.ORGANIZATION_ID,

MTRA.REVISION,

mtl.segment1,

MTRA.SUBINVENTORY_CODE,

MTRA.LOCATOR_ID,

MTRA.TRANSFER_ORGANIZATION_ID,

MTRA.TRANSFER_SUBINVENTORY,

MTRA.TRANSFER_LOCATOR_ID,

MTRA.PRIMARY_QUANTITY,

MTRA.TRANSACTION_DATE,

MTRA.TRANSACTION_TYPE_ID

FROM MTL_MATERIAL_TRANSACTIONS MTRA, MTL_SYSTEM_ITEMS_B MTL, fnd_user MAN

WHERE MTRA.TRANSACTION_ACTION_ID NOT IN (24,30) and (( MTRA.ORGANIZATION_ID = 96 )

and ( MTRA.transaction_date between to_date('15-06-2016 00:00:00','DD-MM-YYYY HH24:MI:SS') AND to_date('16-06-2016 23:59:59','DD-MM-YYYY HH24:MI:SS') )

and (MTRA.LOGICAL_TRANSACTION=2 OR MTRA.LOGICAL_TRANSACTION IS NULL ))

and MTL.inventory_item_id = MTRA.Inventory_Item_Id

and MTL.created_by = MAN.user_id

and MAN.user_name = 'Y16019'

order by MTRA.TRANSACTION_DATE DESC, MTRA.TRANSACTION_ID DESC, MTRA.SUBINVENTORY_CODE DESC