Create Accounting OPM GMF Completes with Error

Create Accounting Completes with Error - GMF 

Error :

GMFAACCP module: Create Accounting

+---------------------------------------------------------------------------+

Current system time is 21-SEP-2013 09:31:21

+---------------------------------------------------------------------------+

XDO Data Engine Version No: 5.6.3

Resp: 21591

Org ID : 82

Request ID: 21699798

All Parameters: P_SOURCE_APPLICATION_ID=555:P_APPLICATION_ID=555:P_DUMMY=Y:P_LEDGER_ID=1:P_PROCESS_CATEGORY_CODE=:P_END_DATE="2013/09/21 00:00:00":P_CREATE_ACCOUNTING_FLAG=Y:P_DUMMY_PARAM_1=Y:P_ACCOUNTING_MODE=D:P_DUMMY_PARAM_2=:P_ERRORS_ONLY_FLAG=N:P_REPORT_STYLE=S:P_TRANSFER_TO_GL_FLAG=:P_DUMMY_PARAM_3=:P_POST_IN_GL_FLAG=:P_GL_BATCH_NAME=:P_MIN_PRECISION=:P_INCLUDE_ZERO_AMOUNT_LINES=N:P_REQUEST_ID=:P_ENTITY_ID=:P_SOURCE_APPLICATION_NAME=Process Manufacturing Financials:P_APPLICATION_NAME=Process Manufacturing Financials:P_LEDGER_NAME=PepsiCo_VN:P_PROCESS_CATEGORY_NAME=:P_CREATE_ACCOUNTING=Yes:P_ACCOUNTING_MODE_NAME=Draft:P_ERRORS_ONLY=No:P_ACCOUNTING_REPORT_LEVEL=Summary:P_TRANSFER_TO_GL=:P_POST_IN_GL=:P_INCLUDE_ZERO_AMT_LINES=No:P_VALUATION_METHOD_CODE=:P_SECURITY_INT_1=:P_SECURITY_INT_2=:P_SECURITY_INT_3=:P_SECURITY_CHAR_1=:P_SECURITY_CHAR_2=:P_SECURITY_CHAR_3=:P_CONC_REQUEST_ID=:P_INCLUDE_USER_TRX_ID_FLAG=N:P_INCLUDE_USER_TRX_IDENTIFIERS=No:P_USER_ID=5497:DebugFlag=N

Data Template Code: GMFAACCP

Data Template Application Short Name: GMF

Debug Flag: N

{P_ACCOUNTING_REPORT_LEVEL=Summary, P_DUMMY=Y, P_ACCOUNTING_MODE_NAME=Draft, P_ERRORS_ONLY_FLAG=N, P_REPORT_STYLE=S, P_GL_BATCH_NAME=, P_END_DATE=2013/09/21 00:00:00, P_SECURITY_INT_3=, P_SECURITY_INT_2=, P_SECURITY_INT_1=, P_VALUATION_METHOD_CODE=, P_POST_IN_GL=, P_TRANSFER_TO_GL=, P_TRANSFER_TO_GL_FLAG=, P_INCLUDE_USER_TRX_IDENTIFIERS=No, P_USER_ID=5497, P_PROCESS_CATEGORY_NAME=, P_ERRORS_ONLY=No, P_DUMMY_PARAM_3=, P_SECURITY_CHAR_3=, P_DUMMY_PARAM_2=, P_SECURITY_CHAR_2=, P_DUMMY_PARAM_1=Y, P_SECURITY_CHAR_1=, P_ENTITY_ID=, P_PROCESS_CATEGORY_CODE=, P_INCLUDE_ZERO_AMT_LINES=No, P_LEDGER_ID=1, P_POST_IN_GL_FLAG=, P_APPLICATION_ID=555, P_INCLUDE_USER_TRX_ID_FLAG=N, P_APPLICATION_NAME=Process Manufacturing Financials, P_REQUEST_ID=, P_CONC_REQUEST_ID=, P_LEDGER_NAME=PepsiCo_VN, P_SOURCE_APPLICATION_ID=555, P_CREATE_ACCOUNTING=Yes, P_CREATE_ACCOUNTING_FLAG=Y, P_MIN_PRECISION=, P_SOURCE_APPLICATION_NAME=Process Manufacturing Financials, P_INCLUDE_ZERO_AMOUNT_LINES=N, P_ACCOUNTING_MODE=D}

Calling XDO Data Engine...

****Warning!!! Due to high volume of data, got out of memory exception...***

****Please retry with scalable option or modify the Data template to run in scalable mode...***

Solution:

1) Add a new parameter called Scalable Flag as follows -

Access the "System Administrator" responsibility.

Navigate to Concurrent > Program > Define.

Search for the report (e.g., Create Accounting)

Click on Parameters button.

Move to the last parameter.

Add the Scalable Flag as follows -

Parameter- P_SCALABLE_FLAG

Description - ScalableFlag

Enabled - Check

Value Set - XLA_SRS_NO_VALIDATION

Default Type - Constant

Default Value - Y

Prompt - P_SCALABLE_FLAG

Token - ScalableFlag (Case sensitive. Give this exact value)

2) Re-run the Create Accounting.

Note that the parameter - P_SCALABLE_FLAG - has a Default Value of 'Y'.

Gather Schema Statistics Completes with Error

Gather Schema Statistics Completes with Error 

SYMPTOMS
"Gather Schema Statistics" program reported following errors in request log files:
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #4: ERROR: While GATHER_TABLE_STATS:  object_name=FII.FII_FIN_ITEM_HIERARCHIES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***


CHANGES
Problem started after database has been upgraded to 11G.
CAUSE
There are two reasons for that error message:
1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and
it fails with ora-20001 errors.

The following SQL should return one row, not two:
SQL>select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254

Since there are two rows in histograms table, FND_STATS creates following command to gather statistics on table 'JE_BE_LINE_TYPE_MAP' :
 dbms_Stats.gather_table_stats(OWNNAME => 'GL', TABNAME
=>'JE_BE_LINE_TYPE_MAP',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SOURCE SIZE 254 FOR
COLUMNS SOURCE SIZE 254');
 Above command will work on 9i and 10G databases but it will fail with ora-20001 errors on 11G.

2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.
You can use following SQL to identify. SQL will prompt for table name, use table name from the errors. In above examples you can use FII_FIN_ITEM_HIERARCHIES.
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name ='&TABLE_NAME'
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null;
SOLUTION

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them logged in as the applsys user.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.
-- identify duplicate rows

select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;
-- Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and  column_name = '&COLUMN_NAME'
and rownum=1;
-- Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
  (
   select hc.table_name, hc.column_name
   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
   where hc.table_name  ='&TABLE_NAME'
   and hc.table_name= tc.table_name (+)
   and hc.column_name = tc.column_name (+)
   and tc.column_name is null
  );

commit;

Working on Custom.PLL in Oracle R12

1.    Enable show custom events: Log in to the Application Developer responsibility and enable Show Custom Events.Check the blog post for enabling custom events:

Custom events can be enabled through the below navigation:

Once you enable show custom events, oracle will display the name of each and every event that can be trapped using CUSTOM.pll. Using this technique, you can identify the custom event that you need to trap to achieve the desired result.

2.    Gather information: A message box will appear when this form is opened, indicating that the form name is FNDCPMPE, the block name is FND_EXECUTABLES, the field name is USER_EXECUTABLE_NAME, and the event name is WHEN-NEW-FORM-INSTANCE. Note down the names of these components as these will be required inprogramming CUSTOM.pll. To double-check the name of field, navigate to the Executable field and use the Help/Diagnostics/Examine menu to see the name.

3.    Write code in CUSTOM.pll: Open Oracle Forms Builder, highlight PL/SQL Libraries, and navigate from the menu to File | Open. Open the CUSTOM.pll file and expand the nodes. Within the node Attached Libraries, you will see FNDSQF and APPCORE2. Write your logic in a procedure called event, using the following sample syntax:

procedure event(event_name varchar2) is

form_name varchar2(30) := name_in('system.current_form');

block_name varchar2(30) := name_in('system.cursor_block');

begin

if form_name='FNDCPMPE' then

if event_name='WHEN-NEW-FORM-INSTANCE' then

app_item_property2.set_property('FND_EXECUTABLES.USER_EXECUTABLE_NAME',CASE_RESTRICTION, UPPERCASE);

end if;

end if;

end event;

OR you can even write your extensions in a package.event(event_name) and call this procedure in packagebody of custom.event.

After making the programming changes to CUSTOM.pll, scroll down to the bottom of the package body Custom and make these changes to the version history of CUSTOM.pll:

This code should appear as a sublisting under #3fdrcsid('$Header: CUSTOM.pld 120.1 2009/03/03 18:43:00 appldev ship $');

4.    Transfer CUSTOM.pll to $AU_TOP/resource: Log in to the forms server and change the directory to $AU_TOP/resource. Ensure you have a backup of CUSTOM.pll either in the source control system or on the file system. Next, transfer CUSTOM.pll from your desktop to $AU_TOP/resource.

5.    Generate CUSTOM.pll: Use the command frmcmp_batch, replacing the apps password with the relevant value on your system. After running this command, CUSTOM.plx will be created in $AU_TOP/resource:

cd $AU_TOP/resource

##For R12 use frmcmp_batch

frmcmp_batch module=CUSTOM.pll userid=apps/appspassword output_

file=./CUSTOM.plx compile_all=special module_type=LIBRARY

batch=yes

##For 11i Use f60gen command as shown below

f60gen module=CUSTOM.pll userid=apps/appspassword output_file=./

CUSTOM.plx module_type=LIBRARY

6.    Test your changes: Log out and log back in to the Concurrent Program Executable screen. You will notice that it is no longer possible to enter the concurrent program executable name using lowercase letters.

Item Attributes During Migration 11i to R12

When migrating from release 11i to release 12.1.x certain Item attributes "Attributes Control" should be set at Organization Level not at Master Organization Level. Below is the

list of attributes that it is recommended to have setup at the organization level if the master inventory organization is not "process enabled".

item_type
shelf_life_code
shelf_life_days
lot_control_code
auto_lot_alpha_prefix
start_auto_lot_number
location_control_code
eng_item_flag
lot_status_enabled
default_lot_status_id
dual_uom_control
secondary_uom_code
dual_uom_deviation_high
dual_uom_deviation_low
secondary_default_ind
tracking_quantity_ind
ont_pricing_qty_source
purchasing_item_flag
customer_order_flag
shippable_item_flag
internal_order_flag
invoiceable_item_flag
so_transactions_flag
taxable_flag
inventory_item_flag
inventory_asset_flag
costing_enabled_flag
stock_enabled_flag
build_in_wip_flag
mtl_transactions_enabled_flag
purchasing_enabled_flag
customer_order_enabled_flag
internal_order_enabled_flag
invoice_enabled_flag

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.