Pending Transaction Clear in 11.5.10

11.5.10 RECEIVING OPEN INTERFACE를 통해 RETURN처리하는 방법
==================================================



PURPOSE
-------
11.5.10에서 Receiving Open Interface를 통해 Return처리하는 방법을 기술함.

Explanation
-----------
11.5.10 is ROI supports for Standard Purchase Order
. RETURN to RECEIVING transactions
. RETURN to VENDOR transactions

1. Receipt routing = Standard Receipt일 경우

A) perform a "RETURN to RECEIVING" for a standard Purchase Order
through ROI
TRANSACTION_TYPE = RETURN TO RECEIVING
PARENT_TRANSACTION_ID = (Transaction Id for DELIVER transaction)

B) perform a "RETURN to VENDOR" for a standard Purchase Order
through ROI
TRANSACTION_TYPE = RETURN TO VENDOR
PARENT_TRANSACTION_ID = (Transaction Id for RECEIVE transaction)


2. Receipt routing = Direct Receipt일 경우,
==> only 1 step return is needed,
TRANSACTION_TYPE = RETURN TO VENDOR
TRANSACTION_ID = (Transaction Id for DELIVER transaction)

3. a Lot and Serial Controlled Item의 경우,
다음 table들에 data를 Insert해 주어야 합니다.

RCV_TRANSACTIONS_INTERFACE,
MTL_TRANSACTIONS_LOTS_INTERFACE,
MTL_SERIAL_NUMBERS_INTERFACE.


TEST
------

step1> PO등록
1) Standard Purchase Order number: # 8415
Inventory Item = Lot-Serial-Controlled-1
Receipt Routing = Standard Receipt
Destination Type Code = Inventory

step2) Receipt처리
Receipt number :# 7970
The deliver transaction's quantity=1
item=Lot-Serial-Controlled-1
Lot Number=S336
Serial Number = SN0021

step3> Return처리

A) "RETURN to RECEIVING" for PURCHASE ORDER Example

SQL>
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
EMPLOYEE_ID,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
FROM_ORGANIZATION_ID,
FROM_SUBINVENTORY,
FROM_LOCATOR_ID,
SOURCE_DOCUMENT_CODE,
PARENT_TRANSACTION_ID,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
DESTINATION_TYPE_CODE,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
VALIDATION_FLAG
)
VALUES
(rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO RECEIVING', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
1, --QUANTITY
'Each', --UNIT_OF_MEASURE
169845, --ITEM_ID
13706, --EMPLOYEE_ID
477550, --SHIPMENT_HEADER_ID
484510, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
7927, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
524018, --PARENT_TRANSACTION_ID
40924, --PO_HEADER_ID
46915, --PO_LINE_ID
81963, --PO_LINE_LOCATION_ID
84349, --PO_DISTRIBUTION_ID
'INVENTORY', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --VALIDATION_FLAG
);


INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
( TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
VALUES
( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--
TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'S366', --LOT_NUMBER
1, --TRANSACTION_QUANTITY
1, --PRIMARY_QUANTITY
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --
SERIAL_TRANSACTION_TEMP_ID
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --
PRODUCT_TRANSACTION_ID
);

INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
( TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,--
TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'SN0021', --FM_SERIAL_NUMBER
'SN0021', --TO_SERIAL_NUMBER
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --
PRODUCT_TRANSACTION_ID
);
Commit;

B) "RETURN to VENDOR" Transaction for PURCHASE ORDER

INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
EMPLOYEE_ID,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
FROM_ORGANIZATION_ID,
FROM_SUBINVENTORY,
FROM_LOCATOR_ID,
SOURCE_DOCUMENT_CODE,
PARENT_TRANSACTION_ID,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
DESTINATION_TYPE_CODE,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
VALIDATION_FLAG
)
VALUES
(rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO VENDOR', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
1, --QUANTITY
'Each', --UNIT_OF_MEASURE
169845, --ITEM_ID
13706, --EMPLOYEE_ID
477550, --SHIPMENT_HEADER_ID
484510, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
7927, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
524017, --PARENT_TRANSACTION_ID
40924, --PO_HEADER_ID
46915, --PO_LINE_ID
81963, --PO_LINE_LOCATION_ID
84349, --PO_DISTRIBUTION_ID
'RECEIVING', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --VALIDATION_FLAG
);


INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
( TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
VALUES
( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--
TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'S366', --LOT_NUMBER
1, --TRANSACTION_QUANTITY
1, --PRIMARY_QUANTITY
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --
SERIAL_TRANSACTION_TEMP_ID
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --
PRODUCT_TRANSACTION_ID
);

INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
( TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,--
TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'SN0021', --FM_SERIAL_NUMBER
'SN0021', --TO_SERIAL_NUMBER
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --
PRODUCT_TRANSACTION_ID
);
Commit;


step4) RUN the Receiving Transaction Processor with (GROUP_ID=30053)


step5) Check a data
Navigate to Receiving / Receiving Transactions Summary form

Interview Preparation - Oracle - Inventory Module

Inventory interview question

 

1. What is item import? How is it done?

 The process of converting inventory items from another inventory system, migrating assembly and component items from a legacy manufacturing system, converting purchase items from a custom purchasing system and importing new items from a product data management package into Oracle Inventory. This import mechanism is achieved through a concurrent program called Open Item Interface.

 

Custom programs are executed prior to item interface and this gets data from the external systems into the interface tables such as MTL_SYSTEM_ITEMS _INTERFACE and MTL_ITEM_REVISIONS_INTERFACE. Item Interface program is then run which actually imports the items and revision information from the above mentioned interface face tables into the base tables such as MTL_SYSTEM_ITEMS_B, MTL_ITEM_REVISIONS. Item Interface assigns defaults and validates the data to ensure data integrity before feeding data into base tables.

 

2. What are Interface and Base Tables?

 Interface Tables            : MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE (Oracle Defaults)
MTL_ITEM_CATEGORIES_INTERFACE (Oracle has Provided certain default categories, if not specified)
MTL_INTERFACE_ERRORS
Base Tables : MTL_SYSTEM_ITEMS_B (Segment 1 stores Model # of Item)
MTL_ITEM_ATTRIBUTES, MTL_ITEM_CATEGORIES,  MTL_ITEM_LOCATIONS,    MTL_ITEM_REVISIONS
MTL_ITEM_STATUS (Status Active/Engineer )
MTL_ITEM_SUB_INVENTORIES

3.  What are Item Attributes?

 Attributes are the specific characteristics associated to every item, namely order cost, item status, revision control, COGS account etc.

 

4.       What are Templates?

 Templates are the defined set of attributes that can be used over and over to create similar items. Templates initial definition of items easier. Oracle has provided certain predefined templates such as (ATO MODEL, ATO OPTION CLASS, and FINISHED GOOD etc). Templates can also be User defined .

 

5)   What are Status Codes?

 Statuses are used to provide default values to certain item attributes to control the functionality of an item. Statuses typically default 8 item attributes namely, BOM allowed, build in WIP, Customer orders enabled, internal orders enabled, invoice enabled, transactable, purchasable, stockable.

Different status types include Active, Inactive, Engineer, obsolete, Phase-out, Prototype, OPM.

 

6.  What are Categories and Category Sets?

  Category is a code used to group items with similar characteristics such as plastics, metals or glass items etc.

A subset of categories grouped together is termed as a Category set. Typical category sets include purchasing, materials, costing and planning.

 8.   What are Lot Numbers and Serial Numbers?

 Lot number is a number that identifies a specific batch of items.
Serial Number is a number assigned to each unit of an item and used to track the item.

 9.  What are Locators?

 A locator is a physical area with in the sub inventory where you store material such as a row, aisle, shelf, or a bin etc.

10.   What is a Sub Inventory?
It is a subdivision of an organization representing a physical area or a logical  grouping of items such as store room or a receiving dock.

 11.     What are the flexfields in Inventory module?

 Item Key Flexfield, Category Key Flexfield.

 12. While importing items from the legacy system through items interface what profile options do u set.

There are two profile options that we need to check, before running the Item Import. They are

 i) PRIMARY_UNIT_OF_MEASURE from INV: Define Primary Unit of Measure

ii) INVENTORY_ITEM_STATUS_CODE from INV: Define Item Status

Oracle Back to Back Order in Order Management

Back Orders

·         The Oracle "term" backorder is a "status" on the order line or delivery line indicating that you have tried to release an order for picking in your warehouse, but that the pick release was UNSUCCESSFUL because there was no available inventory.(Backorder can be partial or complete). The Oracle term backorder does NOT mean that you have open purchase orders for the out-of-stock item from your vendors.

·         The term backorder is also used in business a little differently than in Oracle. The term "An item is on backorder" usually means that the item is not in stock, but the shipping company has already placed purchase orders from their suppliers to restock the item.

·         Back Order is when you do not fulfill the Sales Order, or if the inventory is out of stock for delivery to customer.

Back to Back Orders (B2B)

In Drop-ship items are directly shipped to customer from the supplier and only logical receiving is performed in Oracle. In B2B orders items are physically received to Oracle from supplier and later they are shipped to customers.

Ex: When an order for Laptop is placed, you cannot send laptop and charger differently to the customer. If the company is not interested in maintaining the inventory of chargers, B2B is perfect solution as laptop charger order will go out when ever an order is created for laptop.And the charger is received to oracle and can be shipped with the Laptop.

Flow status code of the order line --FSC
Item reservation type ….IRT

1. Enter sales order …source code Internal
2. Book the order, at this time FSC – Supply Eligible
3. Perform progress order … and FSC –PO Req. Requested & IRT inventory
4. Req. Import –FSC –PO Req. Created & IRT external requisition
5. Auto Create PO –FSC – PO created & IRT PO order
6. Perform receiving transaction--- FSC Awaiting shipping& IRT Inventory
After this complete the order as normal sales order.

Important Notes:
Items used in Back to back order should be ATO enabled, Build in WIP flag checked and in general planning set the Buy flag.
In B2B order at some point we will physically receive goods before shipping them out, where as in Drop ship goods are directly shipped to Customer
Drop ship order may connect to more than one PO but B2B is connected to single PO.