Importing Opening Balance
Date: Thu, 29 Jul 1999 00:46:30 MYT-8
From: philip@aleytys.pc.my
Subject: INV:10.7SC:Importing Opening Balances via Transactions Interface
Dear Ora-Apps,
I've got the inventory open item interface working and I can bring in our
20k+ items. Now I've got to bring in the opening stock balances in our
data migration. I'm planning to use the transactions interface. I've
got some questions.
I should use Account Receive rather than Misc Receive transaction type,
right?
The Open interface manual says I can kick off the transaction workers via
CONCSUB or via oracle forms. So where in the menu structure of the
Inventory GUI responsibility is this form?
Alternatively does anybody know what parameters to pass to CONCSUB and
what is the concurrent programme name to pass to CONCSUB?
I need to verify that the data has gone over correctly.
We are on average costing so how does the current_average_cost column in
the 9.4 mtl_system_items map to the 10.7
cst_item_cost/cst_item_cost_details? Since we do not have/use
BOM/WIP/ENG I'm rather confused by the documentation in the TRM.
Any other hints would be warmly welcomed.
Thanks!
Philip
Date: Fri, 30 Jul 1999 02:29:54 +0800
From: "Lee Chee Meng" leecmg@cyberway.com.sg
Subject: Re: INV:10.7SC:Importing Opening Balances via Transactions
Interface
Philip,
To bring in the stock balances, you can define a new user transaction type
which is basically a Miscellaneous Receipt transaction but it allows you to
track the data created through the data conversion.
After you have populated the data into mtl_transactions_interface, kick off
the material and cost manager through the Inventory module. The path is
something like Inventory - Setup - Transactions - Managers.
Actual transactions are created in mtl_material_transactions with
costed_flag = 'N' and when costed by the cost manager, the flag is set to
null. Records are also created in mtl_onhand_quantities. you can review
these online under the Inventory module.
Getting the cost converted is a bit more complicated. In our case, we use
BOM and WIP and so there are many cost elements and the conversions for
stock balances and item costs are done separately. But if you have only
material cost, then perhaps you could include this as the item cost together
with the stock balance transaction in one conversion. You have to try this
out.
cst_item_costs tracks the item cost while cst_ item_cost_details tracks the
breakdown by cost elements (and maybe by previous/this level, applicable for
BOM).
Hope this helps.
Regards,
Chee Meng
Date: Tue, 3 Aug 1999 08:19:20 +0800
From: PHILIP@aleytys.pc.my
To: "Lee Chee Meng" leecmg@cyberway.com.sg, oraapps-l@cpa.qc.ca
Subject: Re: INV:10.7SC:Importing Opening Balances via Transactions
Hi!
Which transaction type would you recommend. Misc Receipt or Account
Receipt. I am getting conflicting advice on this.
Also if I use Account source type do I populate the DSP_SEGMENTn
fields or the DST_SEGMENTn fields. The Open Interface manual is
rather vague on this.
Date: Mon, 02 Aug 1999 23:50:48 PDT
From: "shawish sababa" shawish_sababa@hotmail.com
Subject: Re: INV:10.7SC:Importing Opening Balances via Transactions
--I should use Account Receive rather than Misc Receive transaction type,
right?
No! use Misc Receive (transaction type = 42). I'v done that 4 time! it works
great.
--The Open interface manual says I can kick off the transaction workers via
CONCSUB or via oracle forms. So where in the menu structure of the
Inventory GUI responsibility is this form?
If I understand your problem the answer has two parts:
1. start the conncurent managers from the OS (ask your system guy's to
do it for you).
2. to turn the transaction manager to Active go to
InventorySetupTransactionslaunch transaction manager. In this window
you will see 4 managers in Inactive status. go to Special and submit a
request to turn the transaction manager to Active.
You better do that to the Cost manager as well.
--I need to verify that the data has gone over correctly.
We are on average costing so how does the current_average_cost column in the
9.4 mtl_system_items map to the 0.7 cst_item_cost/cst_item_cost_details?
Since we do not have/use
BOM/WIP/ENG I'm rather confused by the documentation in the TRM.
I dont know anething about 9.4 mtl_system_items, what i do know is the in
order to creat the onld item cost numbers in the new system you need to
convert the information useing the transaction Open interface (transaction
type = 80) and there is another open interface MTL_CST_TXN_... (dont
remember now) to be used to laod detaild information about the cost
elements. cst_item_cost/cst_item_cost_details well be populated be the cost
manager.
I did this more then once, so if you need help contact me.
Shawish
UOM mismatch in Master and Org item
Date: Sun, 1 Aug 1999 17:13:31 +0300
From: Kik Nichol m_sharawy@tccbce.com
Subject: INV:UOM problem
hi
I found an item in master item its UOM is KG and in orgnization items
its UOM is GM. I want to change the UOM in Organizations item to KG. if
I changed by tool examine will it create a problem?
thanx
Date: Sun, 01 Aug 1999 09:16:14 PDT
From: "abhay agarwal" itisabhay@hotmail.com
Subject: Re: INV:UOM problem
No it will not create any problems that way, you can even do it by sql
update on mtl_system_items, but then you have to update 2 columns.
-Abhay
Date: Sun, 1 Aug 1999 21:34:14 -0400
From: "Paul van den Brink" pbrink1@ibm.net
Subject: Re: INV:UOM problem
Not sure if you should follow that advice !
Be sure to check a little more before changing this:
Is this item used anywhere else ? Did you transact it ? Is it on a Purchase
Order, Price List, Bill of Material ?
Do you have onhand ? What kind of conversions are defined ? Etc. etc. etc.
Let me know if you need some more help before you shoot yourself in the foot
(or worse)
Paul
Paul van den Brink
Colibri - Orlando
Phone: 407 248 0995
Email: pbrink@colibrilimited.com
Date: Mon, 02 Aug 1999 14:01:09 +0530
From: Susan susan_jackson@mail.usa.com
Subject: Re: INV:UOM problem
Which two column are you refering, please asvise.
Regds
Susan
Date: Mon, 02 Aug 1999 07:00:34 PDT
From: "abhay agarwal" itisabhay@hotmail.com
Subject: Re: INV:UOM problem
Hi Susan,
One is column for primary_uom_code and the other is the uom descrption
column. You can do it thru examine also. But you should see if this uom is
not in use on any other document or transaction. I have done it in past and
it works.
Hope this helps.
-Abhay
Date: Tue, 3 Aug 1999 04:56:01 -0700 (PDT)
From: Vidhu vidhug@yahoo.com
Subject: Re: INV:UOM problem
hi!
I was just wondering if we can update columns using
SQL update in APPS. Letme know.
Thanx
Date: Tue, 03 Aug 1999 05:16:29 PDT
From: "shawish sababa" shawish_sababa@hotmail.com
Subject: Re: INV:UOM problem
Hi Vidhu
You can do that, only if you have no BOM/Stock Balance/Sales Orders... or
any ather transaction regarding this item. if you do that you will end up
with some strage stuff on your screen.
Shawish
Date: Tue, 3 Aug 1999 22:57:33 -0700
From: "TomHery" TomHery@home.com
Subject: RE: INV:UOM problem
You are also revaluing inventory because you are changing the relationship
between the quantity on hand for a specific unit of measure at a given cost.
Please note, however, that there are no G/L transactions generated when you
change UOM.
I would think your finance department would be VERY INTERESTED in such
process.
Limiting access to Subinventory
Date: Mon, 2 Aug 1999 10:37:32 -0500
From: deeprani.makhijani@natinst.com
Subject: subinventory access by user
Hello!
Is there a mechanism/setup to limit access to a subinventory by user name ?
We
do not want all users to have access to a certain subinventory within an
organization. We are on 10.7 character mode of the applications.
Thanks,
Deepa
Date: Mon, 2 Aug 1999 12:02:43 -0400
From: "McShane, Chris" Chris.McShane@glenayre.com
Subject: RE: subinventory access by user
You can use security rules against the key flexfield stock locator. Generate
rules that will restrict access to all stock locators in the subinventory.
Then apply the rules to the necessary responsibilties.
How is inventory affected when items are received for repairs
Date: Thu, 5 Aug 1999 04:13:12 -0700 (PDT)
From: kartik iyer mukul061292@yahoo.com
Subject: Oracle Service
Hi friends,
How is the inventory (stock & valuation)affected when the items
are received for repairs ? If anyone has a reply please send
immediately.
Thanks
Kartik
During Item Import can we load template_id/name
Date: Thu, 05 Aug 1999 14:42:13 PDT
From: "chandu k" chandu_r@hotmail.com
Subject: INV:Item Import
Hello Gurus
In item import can we load template_id/name
or does the item import process ignores these columns?
Env:10.7 CUI.
TIA
Chandu.
Date: Fri, 6 Aug 1999 09:15:02 -0700
From: "Mark Erzen" merzen@darc.com
Subject: RE: Item Import
I almost didn't respond 'cause you're asking gurus, but I believe you can
specify the template name or template id, which will then pick up the proper
attributes when you run the item import.
Date: Fri, 6 Aug 1999 08:49:57 -0500
From: "Bearden, Bill" Bill.Bearden@glenayre.com
Cc: "'chandu_r@hotmail.com'" chandu_r@hotmail.com
Subject: Re: INV:Item Import
Chandu,
I have used the template name column when importing items into 10.7SC
Prod16.1. It worked as it should have. The problems I ran into had to do
with the template trying to set attributes that were status-controlled or
were controlled by the master inventory organization. All the normal rules
apply and when templates try and override these rules, the item will fail
import.
So, make sure the item attributes set by your template are compatible with
the item status on your template. Also, it may be helpful to have different
templates for items going into the master inventory organization vs. items
going into the lower level inventory organizations.
Good luck,
Bill
Date: Fri, 06 Aug 1999 07:40:20 PDT
From: "chandu k" chandu_r@hotmail.com
To: Bill.Bearden@glenayre.com, OraApps-L@cpa.qc.ca
Cc: chandu_r@hotmail.com
Subject: Re: INV:Item Import
The one who transfers knowledge is a guru and you did it.
Thanks Mark and Bill.
Cheers
Chandu.
Date: Fri, 6 Aug 1999 08:01:15 -0700
From: Jason Scherer x139 JasonScherer@trm.com
Subject: RE: Item Import
I know you can use the template_name or template_id colums in R11 but I
think they are ignored in 10.7
Date: Sun, 08 Aug 1999 14:26:09 +0300
From: "Nasir Valiya Veetil" vnasir@england.com
Subject: RE: Item Import
You give either Template ID or template name in the field TEMPLETE_ID or
TEMPLETE_NAME in the interface table.
Set all the attributes in your template and dont populate any of the those
item attribute field in the interface table.
This will work.
Nasir V
Physical Inventory - Error 5413
Date: Sat, 07 Aug 1999 10:16:00 -0500
From: "Peter Smith" PSMIT@pcsentre.com
Cc: DOLSE@pcsentre.com
Subject: PHYSICAL INVENTORY- ERROR MESSAGES
We are running 10.7NCA on NT. When the Launch Adjustments routine was run
for Physical Inventory I got Error 5413: Serial Number 1001 not available
for this transaction.
I went to the Tag Counts and Voided the tags that referenced this number.
I launched the routine two more times and got the same error. Then I
checked the Transaction Register for all Physcial Inventory Adjustments
and found that the adjustment had been applied three times to all items
that were to be adjusted, whether they were serialized or not!
HELP ! ! !
Inter-Organization transfer
Date: Mon, 09 Aug 1999 05:32:54 PDT
From: "shawish sababa" shawish_sababa@hotmail.com
Subject: INV: inter-organization transfer
Hi All
I'm heaving a problem with inter-organization transfer. When trying to
preform a transaction I'm getting the error massage :
PO_RI_INVALID_DEST_ORG
I have the shipping network setup, the sub-inventories set up with source
pointing at the organization from which I'm trying to do the
inter-organization transfer and the item source at the master level set as
"Inventory" and at the org level set as "Supplier" for the source org and
"Inventory" for the ather org's.
any help well be appreciated
Shawish
Transaction Qty and QOH out of sync
Date: Wed, 11 Aug 1999 10:58:48 -0400
From: "Finney, Jack" jack.finney@glenayre.com
Subject: INV: tranx quantities and qoh out of sync
Hi all,
We are currently running Apps 10.7 SC 16.1 on HP-UX.
We have run into a problem where the MTL_MATERIAL_TRANSACTIONS table is out
of sync with the MTL_ONHAND_QUANTITIES table. It seems that some
transactions are not being inserted into the MTL_ONHAND_QUANTITIES table. We
have written a script to show us the extent of the problem and there is no
common time-frame, transaction type, quantity, or item.
When we logged a TAR with Oracle we discovered a similar problem where the
MTL_ONHAND_QUANTITIES table was being updated without a corresponding
transaction in the MTL_MATERIAL_TRANSACTIONS table. Oracle supplies a script
on the Metalink forum to detect occurrences of this situation. While we did
find two items that fit this scenario using this script, we also encountered
over a hundred items where the MTL_MATERIAL_TRANSACTIONS table has
transactions that were not inserted into the MTL_ONHAND_QUANTITIES table.
We have applied INV-C and can not confirm if this issue has been fixed. Also
the patch does not retroactively correct the data.
A user brought this issue to our attention just two weeks ago. However,
after investigating we have determined that this issue has been occurring
for over a year undetected due to low dollar amounts of the missing
transactions.
Is anyone else encountering this issue? If you don't know and would like a
script to help you check your data, you can reply to me directly.
Thanks in advance,
Jack Finney
Date: Wed, 11 Aug 1999 10:17:55 -0500
From: "Shockey, David" DShockey@jwrinc.com
Cc: "'jack.finney@glenayre.com'" jack.finney@glenayre.com
Subject: RE: tranx quantities and qoh out of sync
We are also 10.7SC16.1. We have implemented INV, PO, AP, and Cost
Management.
I don't think we have this problem but I would like to have the script
so that I can be sure.
One thing that you might want to check in all of this: I think that
quantity on hand is expressed in the primary unit of measure. The
transaction quantity in MTL_MATERIAL_TRANSACTIONS is not necessarily the
same unit of measure that you find in MTL_ONHAND_QUANTITIES. If
everything is correct, the primary_quantity column in
MTL_MATERIAL_TRANSACTIONS is expressed in the primary unit of measure
and should relate directly to the on hand quantity.
I hope that was as clear as mud. A pro at this stuff can confirm or
deny the way I think that it works.
Thanks in advance for the script.
Date: Wed, 11 Aug 1999 11:59:59 -0400
From: "Finney, Jack" jack.finney@glenayre.com
Subject: RE: tranx quantities and qoh out of sync
David,
Yea, we made that mistake at first but we quickly realized that the
transaction_quantity from the MTL_ONHAND_QUANTITIES table should correspond
to the primary_quantity in the MTL_MATERIAL_TRANSACTIONS table. We found a
few cases where the quantities were off by 0.000003 of the uom. We chalked
that up to rounding in the conversion and crossed those off our list.
thanks,
Jack
Date: Wed, 11 Aug 1999 12:30:11 -0500
From: "Shockey, David" DShockey@jwrinc.com
Subject: RE: tranx quantities and qoh out of sync
I tried your script and made one of my own that returns the same results
in a different way. In our case (so far) every item that the script
turns up is and expense item that is not quantity tracked. You may want
to check the items in your own list to make sure that they are quantity
tracked.
The following is my script. If you take out the outer join you should
get a null result set. As it is, you get a list of all items that do
not have an on-hand quantity in MTL_ONHAND_QUANTITIES but show some
quantity other than zero based on the sum of transactions in
MTL_MATERIAL_TRANSACTIONS.
select b.inventory_item_id, b.subinventory_code, b.txn_qty
from ( select moq.inventory_item_id "INVENTORY_ITEM_ID",
moq.subinventory_code "SUBINVENTORY_CODE",
sum(moq.transaction_quantity) "TXN_QTY"
from mtl_onhand_quantities moq
where moq.organization_id = 114
group by moq.inventory_item_id, moq.subinventory_code) a,
(select mmt.inventory_item_id "INVENTORY_ITEM_ID",
mmt.subinventory_code "SUBINVENTORY_CODE",
sum(mmt.primary_quantity) "TXN_QTY"
from mtl_material_transactions mmt
where mmt.organization_id = 114
group by mmt.inventory_item_id, mmt.subinventory_code) b
where a.inventory_item_id (+) = b.inventory_item_id
and a.subinventory_code (+) = b.subinventory_code
and b.txn_qty != 0
Inventory Period Close Values
Date: Thu, 12 Aug 1999 10:10:19 +0200
From: Clive Calder CliveC@langeberg.co.za
Subject: Inventory Period Close Values
Hi
We recently went live with a number of instances and organisations with
inventory loaded manually by way of miscellaneous receipts with zero cost.
Once inventory was balanced with Pending costs a cost update was performed.
In the case of one organisation under the accounting cycle close, viewing a
closed accounting period, the "Values at close" do not agree with the
inventory value report run as at the change over date. The Transaction
historical summary report using the roll back date to the end of the period
agrees with the General Ledger and the Inventory value report run as at the
period end. After three month ends with period closes the variance between
the "Value at Close" form and the inventory reports remains consistent and
does not agree by 34 million!
How do we fix this form and how could it have happened?
Thanks
Clive Calder
Date: Thu, 12 Aug 1999 05:26:53 -0700 (PDT)
From: fierosrcul@yahoo.com
Subject: Re: Inventory Period Close Values
If your using release 10.7SC, there are at least three
issues that we have problems with. One or more of
them could be impacting you.
1. There is a known bug in the period close report
where it may not pick up transactions occuring on the
last day of a month.
2. Different inventory reports calculate value
differently. I believe the period close report should
be a running total of all your transactions. However
the Inventory value report just takes your current on
hand quantity times the current cost. If you have
done any standard cost updates, you could run into
differences.
And finally, Im not 100% on this, but it seems to be
causing us problems. Within Inventory there is a
column called ITEM cost and one called MATERIAL cost.
We have seen these columns get out of sync. Different
reports may pull from different columns, thus causing
further differences.
Jim Teague
Senior Financial Systems Analyst
Suburban Cable TV
Calculate ATP for an item across all inventory organizations
Date: Sat, 14 Aug 1999 05:20:05 PDT
From: "Basanta Kumar Dash" dashbk@hotmail.com
Subject: ATP calculation in Multi-Org set up
Hi All,
Is it possible to calculate ATP for an item across all the inventory
organisation in Rel 11?
I have a client where I am going to set up multiple inventory organisation
and the client wants to calculate the ATP for the item across all the
defined organisations and display the result in one screen. I know that we
can calculate ATP by each organisation. But, can it be done at one go
considering all the organisations at the same time.
Thank you in advance.
Regards,
Basanta.
UOM - different in outside processing
Date: Sun, 15 Aug 1999 19:44:47 PDT
From: "sr kondapi" srkondapi@hotmail.com
Subject: Outside Processing - items UOM
Dear Freinds,
We need to send items for outside processing for heat treatment. My Items
UOM is EA. But vendor charges the consignment by weight(LBS). The Issue is
how to set up the UOM for the outside processing item(in INV) and the
resource UOM(in BOM).
I would greatly appreciate any suggestions
Thx,
Rao K.S
Date: Mon, 16 Aug 1999 13:23:08 -0400
From: cshay@csc.com
To: oraapps-l@cpa.qc.ca
Subject: Re: Outside Processing Jobs - UOM
Whatever the work order UOM is what you will receive into inventory when the
job
is complete. What you order on the PO can be another UOM. IF your outside
processing is setup where you buy a different part than what you make, set
up
the buy part with the LB UOM.
If you are buying the same part you are making, you need to setup a UOM
conversion from EA to LB . You can set up the PO to buy in LB or EA and
receive
in LB.
How to automatically assign Item Categories
Date: Mon, 16 Aug 1999 11:41:40 -0700 (PDT)
From: "MURTHY ADURI UDAY B." adurimurthy@yahoo.com
Hi Inventory Gurus,
1.Oracle recommends not to populate the Mtl_item_categories_interface
table while doing the item import and that internally the item import
program uses this table.
2.By default the items are assigned to default category which is set
up.
3.Is there a way to assign the item categories in an automated way.
4.Now it supports manual alteration after the items have been imported.
5.We are on 10.7.0 SC161.
Any inputs....
Thanks in advance,
Murthy.
Date: Mon, 16 Aug 1999 13:59:09 -0500
From: "MCAVOY, Brian J." bmcavoy@shl.com
Murthy, run the item interface which assigns the default category to the
item, then write another program to update the item with the appropriate
category after the fact. All you really need is a file with the item and
correct category.
Brian McAvoy
Consultant
EDS
Client phone # 973-491-8614
Email bmcavoy@shl.com
Date: Mon, 16 Aug 1999 13:04:01 -0700 (PDT)
From: "MURTHY ADURI UDAY B." adurimurthy@yahoo.com
To: oraapps-l@cpa.qc.ca
Subject: RE: ITEM IMPORT
Hi Brian,
Thanks.
Does the update affect any future transactions/or will it hinder any
functionality in future?
Thanks again for the suggestion,
Murthy.
Date: Mon, 16 Aug 1999 19:28:50 -0500
From: "MCAVOY, Brian J." bmcavoy@shl.com
Subject: RE: ITEM IMPORT
Not that I can think of or have come across, all your really doing is
writing a simple update statement using two or three tables if I remember.
Brian McAvoy
Consultant
EDS
Client phone # 973-491-8614
Email bmcavoy@shl.com
Date: Tue, 17 Aug 1999 08:15:54 -0700 (PDT)
From: "MURTHY ADURI UDAY B." adurimurthy@yahoo.com
To: oraapps-l@cpa.qc.ca
Subject: RE: ITEM IMPORT
Thanks. May I add that Oracle support quotes many doing this but
declines to support for any repurcussions, so be it.
Murthy.
Date: Mon, 16 Aug 1999 19:04:29 -0600
From: Amber amber@allen-sauer.com
Subject: RE: Item Import
Murthy -
I have used this technique as well, and found no downside to it. Of
course Oracle tells you not to update their tables directly, but on
occasion you dont have an alternative (other than manual entry!).
You will need to update mtl_item_categories.category_id. After you
update a few, but few and navigate to the assign item category screen
and make sure you dont receive any errors. Also, we put the category in
one of the attribute fields during the import, so we could just run a
script after the import completed. That way you wont have to load or
read a data file seperately!
Amber
Allen-Sauer Consulting, Inc.
Phone : (303) 399-4137
Fax : (303) 399-0660
Internet: amber@allen-sauer.com
http://www.allen-sauer.com
Date: Mon, 16 Aug 1999 15:16:25 -0500
From: "Stobbe, John" jstobbe@MarkAndy.com
Subject: RE: ITEM IMPORT
You may even want to keep the script that you use to assign items to
categories. Over time we have found the need to create new categories and
have modified our original script to assign items to the new categories.
Date: Mon, 16 Aug 1999 23:53:10 PDT
From: "shawish sababa" shawish_sababa@hotmail.com
Subject: RE: ITEM IMPORT
Hi Murthy
I have to agree with John. I have done category conversion for 3 clients how
are already operational and we simply inserted the records directly into
mtl_item_categories. When the Oracle guy's said that we must use interface's
we asked them do provide us with an interface for the item categories. The
answer was that there is no interface...
If you need any help contact me.
Shawish
Date: Tue, 17 Aug 1999 20:21:57 MYT-8
From: philip@aleytys.pc.my
To: "MURTHY ADURI UDAY B." adurimurthy@yahoo.com, oraapps-l@cpa.qc.ca
Subject: INV: automating category assignment on item import
What I did was to populate the imported item's unused ATTRIBUTE columns
with the required category. There are fifteen and we don't use any of
them so I used ATTRIBUTE14 and ATTRIBUTE15 to store the segments of the
Inventory Category. Post import I use a SQL script to update the
Inventory Category to the correct category.
Philip
Date: Fri, 20 Aug 1999 17:48:32 EDT
From: "Raj P" raj_orapps@hotmail.com
To: oraapps-l@cpa.qc.ca
Subject: RE: ITEM IMPORT
NEW PATCHSET INV-C HAS GOOD FUNCTIONALITY TO UPDATE ITEM INFORMATION THRU
ITEM IMPORT.
ITEM IMPORT PROGRAM HAS PARAMETER AS CREATE / UPDATE
SET DEFAULT CATEGORY SET
AND POPULATE INTERFACE TABLES TO TAKE DEFAULT CATEGORY SET
AND RUN ITEM IMPORT PROGRAM WITH PARAMETER SET TO UPDATE
RAJ