2016年6月16日 星期四

[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

沒有留言:

張貼留言