oracle erp account generator,转:Oracle ERP 技术

INV中期末关帐时不计成本如何解决

INV在期末关帐的时候经常出现不计成本的错误,这些错误很大的原因时Cost manager

运行有问题,或者没有起来,或者运行用错误,但是如果Cost

manager也没有什么问题,就很少的几条记录因为其它不知名原因出现了不计成本的错误导致没有办法关帐.下面的SQL可以解决:

第一段處理(子庫移轉)

update mtl_material_transactions

set costed_flag = 'N',

transaction_group_id = NULL,

transaction_set_id = NULL,

error_code = null,

error_explanation = null,

cost_update_id = null

where costed_flag = 'E' or costed_flag = 'N' ;

第二段處理(WIP發放)

Update WIP_COST_TXN_INTERFACE

Set GROUP_ID = NULL,

TRANSACTION_ID = NULL,

REQUEST_ID = NULL,

PROCESS_STATUS = 1

Where PROCESS_STATUS = 3;

再做

commit;

最後再跑一次成本Cost manager OK

4 07, 2006

物料验证组织和物料主组织的异同

Oracle的多组织其实是一个非常复杂的概念,很容易是人混淆,其中就有物料验证组织和物料主组织这两个概念,实际上物料验证组织和物料主组织在组织类型上都是属于Inventory的类型了.

物料主组织的的通常用来管理一个物料在各个库存组织的共同的属性,通常我们会建一个物料主组织,然后在其它的inventory的parameter中指定这个主组织.

物料验证组织.实际上是用来限制物料在OM.AR等系统中使用用的.通常我们建了物料验证组织后,它的主组织也是指向同一个物料主组织的.然后在把profile

MO: Operating Unit

在不同的responsibility下附上不同的OU的值后(当然如果你的组织很简单的话,你可以在sit下设置该值).在OM模块的paramter的valus界面中能找到Item

Validation Organization这个值,实际上这个就是OE:Item

Validation Organization这个profile的值.选择物料验证组织后,你一定要把ITEM

assign这个组织,才能在OM,AR等模块中使用它.

同时如果你要在Price中使用这颗料的话.

QP:项目验证组织

默认值:无

将它设置为组织层次结构层中的某个组织,您可以在该组织为项目设置价格。

指明 Oracle Manufacturing

组织,在“价目表”或“修改量”窗口中输入项目时,将根据此组织来验证和查看项目。

按地点或责任将“QP:项目验证组织”配置文件设置为组织层次结构层的某个组织,您可以在该组织为项目设置价格。

1 07, 2006

Item 导入话题的探讨

Item Import Overview

~~~~~~~~~~~~~~~~~~~~~~~~~

Use the item import process to import items from your legacy system

or PDM

system. You can import items from any source into Oracle Inventory

and Oracle

Engineering. When you import items through the Item Interface, you

create new

items in your Item Master organization or assign existing items to

additional

organizations. You can specify values for all the item attributes,

or you can

specify just a few attributes and let the remainder default or

remain Null.

You can also specify an item template for each item and inherit

attribute

values from the template. The Item Interface also lets you import

revision

details, including past and future revisions and effectivity

dates.

Validation of imported items is done using the same rules as the

item definition

forms, so you are insured of valid items.

The Item Interface reads data from two tables for importing items

and item

details. You use the MTL_SYSTEMS_ITEM_INTERFACE table for new item

numbers

and all item attributes. This is the main item interface table, and

can be

the only table you choose to use. If you are importing revision

details for

new items, you can use the MTL_ITEM_REVISIONS_INTERFACE table. A

third table,

MTL_INTERFACE_ERRORS, is used for error tracking of all items that

the Item

Interface fails. You can import item categories using interface

table called

as MTL_ITEM_CATEGORIES_INTERFACE.

Before you use the Item Interface, you must write and run a custom

program that

extracts item information from your source system and inserts it

into the

MTL_SYSTEM_ITEM_INTERFACE table, and (if revision detail is

included) the

MTL_ITEMS_REVISIONS_INTERFACE table. After you load the items into

these

interface tables, you run the Item Interface to import the data.

The Item

Interface assigns defaults, validates data you include, and then

imports the

new items. You may also specify an item template for each item

being imported.

You must import items into the Item Master organization before you

import items

into additional organizations. You can accomplish this by

specifying only your

Item Master organization on first run of the Item Interface. Once

this has

completed, you can run the Item Interface again, this time

specifying an

additional or all organizations. You can also use the Item

Interface to import

a single item material cost and material overhead, and revision

details.

Below we will walk through few scenarios of item import.

Scenario: 1

~~~~~~~~~~~~~

Testing the basic item import with minimum columns populated.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

First always try to populate the master organization with a sample

record and

test.

1. How do I know my organization code and organization id ?

Select organization_code , organization_id from

org_organization_definitions

order by organization_code;

Sample out put is below.

ORG ORGANIZATION_ID

--- -------------------------------

V1 204

M1 207

M2 209

M3 606

M4 1641

M5 1642

M6 1643

M7 1644

....

2. How do I know which organization is master to a child

organization?

select a.organization_id

,a.organization_code,a.master_organization_id,

b.organization_code

from mtl_parameters a, org_organization_definitions b

where a.master_organization_id = b.organization_id

order by a.organization_code;

Sample out put is below

ORGANIZATION_ID ORG MASTER_ORGANIZATION_ID ORG

--------------------------- -------

---------------------------------------- -------

204 V1 204 V1

207 M1 204 V1

209 M2 204 V1

606 M3 204 V1

1641 M4 204 V1

1642 M5 204 V1

1643 M6 204 V1

1644 M7 204 V1

.....

In the above example for organizations M1 to M7, V1 organization is

the master organization.

Populating V1 organization with one new item using just the basic

columns.

Please truncate all IOI interface tables before loading

mtl_system_items_interface:

ex. TRUNCATE TABLE

TRUNCATE TABLE INV.MTL_SYSTEM_ITEMS_INTERFACE;

TRUNCATE TABLE INV.MTL_INTERFACE_ERRORS;

TRUNCATE TABLE INV.MTL_ITEM_REVISIONS_INTERFACE;

TRUNCATE TABLE INV.MTL_ITEM_CATEGORIES_INTERFACE;

Note: Truncate frees space, delete does not free

space.

insert into mtl_system_items_interface

(process_flag,set_process_id,transaction_type,organization_id,

segment1,description)

values

(1,1,'CREATE',204,'TESTITEMIMPORT', 'Testing Item Import');

commit;

In the above insert Process_Flag = 1. Only when process_flag = 1

will the item

import program process the record.

Set_Process_Id = 1. You can set which ever number you want for this

column.

This is basically used like a batch number. You can tell item

import which set

of records to process by entering a set_process_id.

Transaction_Type is 'CREATE' when you are inserting new records and

'UDATE'

when you are updating existing records.

We are using organization_id 204 which is for master organization

V1.

Note: If you have more than one segment defined for

items please populate all

those segments.

To check which segments to be populated please got to

Setup > Flexfields > Key

> Segments . Now query up 'System Items' by

putting

it in flexfield title.

Select segments button, and now you should be able to see the

different

segments being defined.

Now run the item import program ie. Item > Import

> Import Items.

In Process Set enter 1 as we had used 1 in the set_process_id

column. Now

enter 1 in create or update items field and hit OK button.

You can check view requests to check whether your request has

completed

successfully.

Now go to master items screen and query on 'TESTITEMIMPORT' the

item that

we had populated and you should be able to see the item.

Similarly you can import the same item for the child organization.

Eg. we want

item 'TESTITEMIMPORT' in organization M1 say Seattle manufacturing

then all I

have to do is replace the organization_id by M1 organization_id.

Now the insert

statement will look like this.

insert into mtl_system_items_interface

(process_flag, set_process_id,

transaction_type,organization_id,segment1,description)

values

(1,1,'CREATE',207,'TESTITEMIMPORT', 'Testing Item Import');

commit;

Run the item import with the same parameters and then check if the

concurrent

request has completed successfully.

Now do Change Organization and go to Organization Item screen and

query up

TESTITEMIMPORT and you should be able to see the item in M1

organization.

Scenario: 2

~~~~~~~~~~~~~

To import items and use item templates.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

To see all template names run the following sql.

select template_id,template_name,description from

MTL_ITEM_TEMPLATES;

To see all the attributes related to template run the following

sql.

select

template_id,attribute_name,enabled_flag,report_user_value

from MTL_ITEM_TEMPL_ATTRIBUTES where template_id =

&template_id;

Please subsitute the template id from the 1st query in the second

query

to see the attribute values.

In our example I am going to use Purchasing Template.

select template_id,template_name,description

from MTL_ITEM_TEMPLATES

where template_id = 259;

template_id template_name description

----------- ------------- ----------------

259 Purchased Item Purchased Item

We are going to insert a new item in the master organization and

use the

purchasing template.

insert into mtl_system_items_interface

(process_flag, set_process_id,

transaction_type,organization_id,segment1,

description,TEMPLATE_ID)

values

(1,1,'CREATE',204,'TESTTEMPLATE', 'Testing Item Import With

Template',259);

commit;

Now run the item import program ie. Item > Import

> Import Items.

In Process Set enter 1 as we had used 1 in the set_process_id

column. Now enter

1 in create or update items field and hit OK button.

You can check view requests to check whether your request has

completed

successfully.

Now go to master items screen and query on 'TESTTEMPLATE' the item

that we

had populated and you should be able to see the item. Now you will

see that

the purchasing attributes are set because we used the purchasing

template.

Note: Instead of using template_id we can use

template_name and you should

see the same effect.

insert into mtl_system_items_interface

(process_flag, set_process_id,

transaction_type,organization_id,segment1,

description,TEMPLATE_NAME)

values

(1,1,'CREATE',204,'TESTTEMPLATE', 'Testing Item Import With

Template',

'Purchased Item');

commit;

Scenario: 3

~~~~~~~~~~~~

To import items and material cost associated to it.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

First check which cost elements are defined for the

organizaion.

select cost_type_id,cost_type,description from

CST_COST_TYPES;

Output looks like this.

cost_type_id cost_type description

------------ -------------

------------------------------------

1 Frozen Frozen Standard Cost Type

2 Average Average Cost Type

3 Pending Pending Standard Cost Type

.....

To find the sub element name defined for your organization for a

particular

cost type please check bom resources table.

In our example we are checking for material sub element in

organization M1

with organization_id = 207 for cost_code type 1.

select

resource_code,description,cost_element_id,cost_code_type

from bom_resources where organization_id = 207

and cost_code_type= 1;

resource_code description cost_element_id cost_code_type

------------- ----------------- ---------------

--------------

Material Material Sub element 1 1

Labor Labor Sub element 1 1

Expense Expense Sub element 1 1

In our scenario we want to create a new item in organization M1

along with

its material cost (say $11) assuming the same item is already

created in

master organization V1.

insert into mtl_system_items_interface

(process_flag, set_process_id,

transaction_type,organization_id,segment1,

description,template_id,MATERIAL_COST,MATERIAL_SUB_ELEM)

values

(1,1,'CREATE',207,'TESTCOSTMATERIAL', 'Testing Item Material Cost

Import',

259,11,'Material');

commit;

In the above sql you might have observed we are populating

MATERIAL_COST and

MATERIAL_SUB_ELEM.

Similary we can use MATERIAL_SUB_ELEM_ID, MATERIAL_OH_RATE,

MATERIAL_OH_SUB_ELEM and MATERIAL_OH_SUB_ELEM_ID colums.

To check the subelements you can go to Cost management

responsibility for

your organization and under Setup > Sub-Elements you

will see the respective

subelements being defined.

Now run the item import program ie. Item > Import

> Import Items.

In Process Set enter 1 as we had used 1 in the set_process_id

column.

Now enter 1 in create or update items field and hit OK

button.

You can check view requests to check whether your request has

completed

successfully.

Now go to organization items and query on 'TESTCOSTMATERIAL' the

item that

we had populated and you should be able to see the item.

Go to Tools > Item Costs and then hit Open button

from Item Cost Summary screen

and you should see the details. In out example we see a material

cost of $11

for the item in cost type 'Frozen'(Standard Costing).

NOTE: Item import cannot be used to update item

costs.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You have to populate

CST_ITEM_CST_DTLS_INTERFACE,CST_RESOURCE_COSTS_INTERFACE,

CST_RES_OVERHEADS_INTERFACE and CST_DEPT_OVERHEADS_INTERFACE

accordingly.

The concurrent request can be launched by navigating to

cost->cost mass

edits->Import Cost Information.

The parameters for the Cost Import concurrent program and their

description is

as follows :

Import Cost Option : A LOV is provided from which

the user can select one of

the import options which may be either to import Only item costs ,

Only

resource costs , Only overhead rates or all the cost information

.

Option Table from which data is processed

---------- --------------------------------------------

Only item cost cst_item_cst_dtls_interface

Only resource costs cst_resource_costs_interface

Only overhead rates cst_res_overheads_interface ,

cst_dept_overheads_interface

All Cost Information From all the four interface tables

Mode to run this request : A LOV is provided with

possible two

values , 'Insert new cost' or 'Remove and replace cost'.

The 'Insert new cost' mode , is useful if you are

importing large # of items and are not sure if that

Item/Organization/Cost

Type combination already exists in the production tables, if it

does then the

row in the interface table would be flaged as errored and not

imported.This

would prevent any accidental overwrite of already existing

data.

With 'Remove and replace cost' mode all the

previous cost information for this item, cost_type and organization

combination

will be deleted from the production tables and the new information

will

overwrite (replace) the already existing one.

Group Id Option : A LOV is provided from which the

user can either

select 'ALL' or 'Specific Group Id' . If the user wishes to submit

multiple

Cost Import process requests he can do so by submitting one request

per group

id. For doing so the data in the interface tables should be stamped

with

distinct group id value by using the NEXTVAL from the sequence

generator

CST_LISTS_S .The use of this sequence geneartor is a MUST for

generating

multiple groups or may lead to data corruption as these interface

tables are

used by other processes too.

If the user selects "ALL" from the list then a group ID

generated by a sequence will replace the group ID in the interface

tables (if

any) and all the unprocessed rows from the four interface table

(viz.

cst_item_cst_dtls_interface , cst_resource_costs_interface ,

cst_res_overheads_interface , cst_dept_overheads_interface ) will

be processed

in one run.

Cost type to import to : The user is provided with

a LOV from which he

needs to select the cost type in which he wishes to import the

cost

information. Even if the user has populated a cost type or cost

type ID in the

interface tables, it would be overwritten with the one that is

selected here.

The cost types that the user can pick from is restricted to the

multi-org,

updateable cost types.

Delete succesfull rows : This parameter decides

whether the

successfully processed rows should be deleted from the interface

tables at the

end of the run. If the user selects 'Yes' then all the successful

rows be

deleted, basically rows that do not have their error flag set to

"E".

Importing directly into Frozen/Average cost type (i.e non

updateable cost types)

and merging of new cost with existing costs is not supported at

this time and

would still have to be processed by cost update routines. Also when

importing

the costs from the interface table ,material overhead defaults (if

any)

specified for an Organization/Category would not be

respected.

Minimum columns in each table that the user needs to provide

1. CST_ITEM_CST_DTLS_INTERFACE

The columns that the user has to provide are

a. Inventory_item_id

b. organization_ID or organization_code.

c. resource_ID or resource_code for cost elements other than

1(material).

If we are importing cost into material cost element and default

material

subelement has been specified on 'Define Organization parameters'

form

then that would be respected unless the user overides it with a

value

in this column.

d. usage_rate_or_amount

e. cost_element_ID or cost_element

f. Process Flag (must be set to 1)

We default values for based_on_rollup, shrinkage_rate,

inventory_asset_flag,

lot_size from the row from CST_ITEM_COSTS for this item and the

default cost

type(default cost type of the cost type specified to import the

costs into). If

there is no such row already defined in CST_ITEM_COSTS, then, the

values for

these 4 columns also need to be specified and they have to be the

same for all

rows of this item, cost type and organization combination.

CST_RESOURCE_COSTS_INTERFACE

a. Resource_ID or resource_code

b. organization_ID or organization_code

c. resource_rate

d. Process_flag (must be set to 1)

CST_RES_OVERHEADS_INTERFACE

a. Resource_ID or resource_code

b. Overhead_ID or overhead

c. Organization_ID or organization_code

d. Process_flag(must be set to 1)

CST_DEPT_OVERHEADS_INTERFACE

a. Department_ID or department

b. Overhead_ID or overhead

c. Organization_ID

d. Rate_or_amount

e. Process_flag(must be set to 1)

The other columns will be defaulted.

This is available for only 11.5.9 and above customers.

Customers on 11.5.8 can apply Patch 2193391 to get this functionality.

Please refer to costing Manuals for further details.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

In case you have any questions please log a tar with costing

group.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Sample cost update statement is below

INSERT INTO CST_ITEM_CST_DTLS_INTERFACE

(INVENTORY_ITEM_ID,ORGANIZATION_ID,COST_TYPE,RESOURCE_CODE,USAGE_RATE_OR_AMOUNT,

COST_ELEMENT_ID,PROCESS_FLAG)

VALUES

(9935,207,'Pending','Material',17,1,1);

commit;

Using this I update the pending cost for the item from $11 to

$17.

Now once the pending cost is updated you will have to use 'Update

Standard Cost'

Concurrent program to update Frozen cost for the item from the

pending cost.

Scenario: 4

~~~~~~~~~~~~~~

To import item and revisions associated to it.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

When ever you import a new item based on the Starting revision

specified under

'Revison/Lot/Serial' tab in organization parameters screen the

default revison

gets assigned to the item. For eg. the default revision in V1

organization is

'A' when we do item import this revision gets associated to the

item.

Now lets import a new item with revision 'B' which is not the

default revision.

insert into mtl_system_items_interface

(process_flag, set_process_id,

transaction_type,organization_id,segment1,

description,REVISION)

values

(1,1,'CREATE',204,'TESTREVB1', 'Testing Item Revision','B');

Now run the item import program ie. Item > Import

> Import Items.

In Process Set enter 1 as we had used 1 in the set_process_id

column.

Now enter 1 in create or update items field and hit OK

button.

You can check view requests to check whether your request has

completed

successfully.

Now go to organization items and query on 'TESTREVB1' the item that

we

had populated and you should be able to see the item.

Go to Tools > Revisions and then you should be able

to see two records one

with revision 'A' which is the default revision and another with

revision 'B'

that we had populated.

In case you want to import a new revision say revision 'C'. You

directly

populate the MTL_ITEM_REVISIONS_INTERFACE table and then run item

import.

insert into MTL_ITEM_REVISIONS_INTERFACE

(item_number,revision, revision_label,implementation_date,

effectivity_date,

process_flag, organization_id ,set_process_id,

transaction_type)

values

('TESTREVB1','C','C',sysdate,sysdate,1,204,1,'CREATE');

Commit;

Now run the item import.

In Process Set enter 1 as we had used 1 in the set_process_id

column.

Now enter 1 in create or update items field and hit OK

button.

You can check view requests to check whether your request has

completed

successfully.

When you query up the item 'TESTREVB1' You will see the new

revision 'C'.

Scenario: 5

~~~~~~~~~~~~~~

Lets take into consideration we have to import more than two

revisions along

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

with item creation. We will be populating

MTL_ITEM_REVISIONS_INTERFACE.

The key point here is the IMPLEMENTATION_DATE should increment

cronologically.

insert into mtl_system_items_interface

(process_flag, set_process_id,

transaction_type,organization_id,segment1,

description)

values

(1,1,'CREATE',204,'TESTREVB2', 'Testing Item Revision');

insert into MTL_ITEM_REVISIONS_INTERFACE

(item_number,revision, revision_label,implementation_date,

effectivity_date,

process_flag, organization_id ,set_process_id,

transaction_type)

values

('TESTREVB2','A','A',sysdate,sysdate,1,204,1,'CREATE');

insert into MTL_ITEM_REVISIONS_INTERFACE

(item_number,revision, revision_label,implementation_date,

effectivity_date,

process_flag, organization_id ,set_process_id,

transaction_type)

values

('TESTREVB2','B','B',sysdate+.0003472,sysdate+.0003472,1,204,1,

'CREATE');

insert into MTL_ITEM_REVISIONS_INTERFACE

(item_number,revision, revision_label,implementation_date,

effectivity_date,

process_flag, organization_id ,set_process_id,

transaction_type)

values

('TESTREVB2','C','C',sysdate+.0023472,sysdate+.0023472,1,204,1,

'CREATE');

COMMIT;

Now run the item import program ie. Item > Import

> Import Items.

In Process Set enter 1 as we had used 1 in the set_process_id

column. Now enter

1 in create or update items field and hit OK button.

You can check view requests to check whether your request has

completed

successfully.

Now go to organization items and query on 'TESTREVB2' the item that

we had

populated and you should be able to see the item. Go to Tools

> Revisions and

then you should be able to see three records one with revision 'A'

,

another with revision 'B' and the thrid one is 'C' that we had

populated.

Note: We have been inctementing the minutes section

by sysdate+.0003472

and sysdate+.0023472 so that the implementation date is in

cronological order.

Scenario: 6

~~~~~~~~~~~~~~~~

Lets take into consideration we have to import item categories

along with items.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

select

category_id,structure_id,segment1,description,category_concat_segs,

structure_name

from mtl_categories_v

where segment1 = 'DAIRY'

order by category_id,structure_id;

category_id structure_id segment1

description category_concat_segs structure_name

---------------------------------------------------------------------------------------

1440 51466 DAIRY

Dairy Products DAIRY Inventory Class

1861 51467 DAIRY

Dairy Products DAIRY Planning Class

1869 51470 DAIRY

Dairy Products DAIRY Sales Class

1886 51766 DAIRY

DAIRY DAIRY GL Product Line

select

category_set_id,category_set_name,description,structure_name,

structure_id

from mtl_category_sets_v where category_set_name = 'Inventory

Class';

category_set_id category_set_name description structure_name

structure_id

---------------------------------------------------------------------

90 Inventory Class Inventory Class Inventory Class

51466

In out example we want to use category 'DAIRY' which has a

category_id of

1440 and category set 'Inventory Class' which has category_set_id

of 90.

You can go to the application Setup > Items

> Categories > Category Codes

and using examine get the category id.

You can go to the application Setup > Items

> Categories > Category Sets

and using examine get the category set id.

insert into mtl_system_items_interface

(process_flag, set_process_id,

transaction_type,organization_id,segment1,

description)

values

(1,1,'CREATE',204,'TESTCATG1', 'Testing Item Categories');

insert into MTL_ITEM_CATEGORIES_INTERFACE

(item_number,category_set_id, category_id,process_flag,

organization_id ,

set_process_id, transaction_type)

values

('TESTCATG1',90,1440,1,204,1,'CREATE');

In the above insert statement you could use CATEGORY_SET_NAME and

CATEGORY_NAME

instead of CATEGORY_SET_ID and CATEGORY_ID.

commit;

Now run the item import program ie. Item > Import

> Import Items.

In Process Set enter 1 as we had used 1 in the set_process_id

column.

Now enter 1 in create or update items field and hit OK

button.

You can check view requests to check whether your request has

completed

successfully.

Now go to organization items and query on 'TESTCATG1' the item that

we had

populated and you should be able to see the item. Go to Tools

> Categoriess

and then you should be able to see category Dairy associated to the

item.

To verify you can run the following query

Select * from MTL_ITEM_CATEGORIES where inventory_item_id in

(select distinct inventory_item_id from mtl_system_items_b where

segment1 = 'TESTCATG1');

It shows one record.

NOTE: Populating set_process_id is mandatory for

categories import. Please put

the same set process id when you run the import program.

Updating exsisting item categories:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Updated transaction can only be performed as a combination of

DELETE

assignment, then CREATE.

Lets take our above example of item 'TESTCATG1' which has now

category 'DAIRY'

associated with it. I want to update category 'DAIRY' with a new

category

'BAKERY' whose category_id is 1436 for item 'TESTCATG1'.

insert into MTL_ITEM_CATEGORIES_INTERFACE

(item_number,category_set_id, category_id,process_flag,

organization_id ,

set_process_id, transaction_type)

values

('TESTCATG1',90,1440,1,204,1,'DELETE');

insert into MTL_ITEM_CATEGORIES_INTERFACE

(item_number,category_set_id, category_id,process_flag,

organization_id ,

set_process_id, transaction_type)

values

('TESTCATG1',90,1436,1,204,1,'CREATE');

commit;

Run item import.In Process Set enter 1 as we had used 1 in the

set_process_id

column. Now enter 2 in create or update items field as we are

updating the

exsisting item and changing its category. Hit OK button.

You can check view requests to check whether your request has

completed

successfully.

When you query up item 'TESTCATG1' and select the categories from

tools you

will find that the category 'BAKERY' is now asscoiated with the

item.

Scenario: 7

~~~~~~~~~~~~

Lets take into consideration we have to import item catalogs along

with items.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

In our example we want to asscoiate 'ADSL Modem' catalog to our

item.

select ITEM_CATALOG_GROUP_ID,SEGMENT1 from

MTL_ITEM_CATALOG_GROUPS_B

where ITEM_CATALOG_GROUP_ID = 161;

ITEM_CATALOG_GROUP_ID SEGMENT1

---------------------

----------------------------------------

161 ADSL Modem

select

ITEM_CATALOG_GROUP_ID,ELEMENT_NAME,ELEMENT_SEQUENCE,DESCRIPTION

from mtl_descriptive_elements

where ITEM_CATALOG_GROUP_ID = 161;

ITEM_CATALOG_GROUP_ID ELEMENT_NAME ELEMENT_SEQUENCE

DESCRIPTION

--------------------- ------------ ----------------

-----------

161 2wire 10 2wire

161 linksys 20 linksysdesc

insert into mtl_system_items_interface

(process_flag, set_process_id,

transaction_type,organization_id,segment1,

description,ITEM_CATALOG_GROUP_ID)

values

(1,1,'CREATE',204,'TESTCATA1', 'Testing Item Catalog',161);

commit;

Now run the item import program ie. Item > Import

> Import Items.

In Process Set enter 1 as we had used 1 in the set_process_id

column. Now enter

1 in create or update items field and hit OK button.

You can check view requests to check whether your request has

completed

successfully.

Now go to organization items and query on 'TESTCATA1' the item that

we had

populated and you should be able to see the item. Go to Tools

> Catalogs

and then you should be able to see 'ADSL Modem' along with

descriptive

elements '2wire' and 'linksys'. In 11.5.9 you have to manually

enter the

values for descriptive elements. In 11.5.10 API to import

descriptive element

values will be present.

Scenario: 8

~~~~~~~~~~~~

Lets understand how to update exsisting items.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Lets say I have an item 'ODC' which has no buyer code in Seattle

organization

(M1 organization code). I want to update and asscoiate 'West, Rod'

as buyer

to the item 'ODC'.

First I check under Setup > Items >

Attribute Contol to check if it controlled

at the organization level or master level. In our case the

attribute is

controlled at the Org Level.

Now we check if Rod exists in the list of valid buyers.

select agent_id,agent_name,start_date_active,end_date_active

from PO_AGENTS_V where agent_name like '%Rod%';

agent_id agent_name start_date_active end_date_active

-------- ---------- ----------------- ---------------

10183 West, Rod 17-FEB-04

select inventory_item_id from mtl_system_items_b

where segment1 = 'ODC'

and organization_id = 207;

207 organization id is for M1(Seattle organization)

INVENTORY_ITEM_ID

-----------------

9372

Now use the above values in the insert script.

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,

organization_id,

inventory_item_id, BUYER_ID)

values

(1,1,'UPDATE',207,9372,10183);

commit;

Now you will observe that the transaction type is 'UPDATE' because

we are

updating an existing record. I am also using inventory_item_id

because when

you use id's the performance is better. And also when running the

item import

I use value 2 in create or update items field.

Now run the item import program ie. Item > Import

> Import Items.

In Process Set enter 1 as we had used 1 in the set_process_id

column. Now enter

2 in create or update items field and hit OK button.

You can check view requests to check whether your request has

completed

successfully.

Now go to organization items and query on 'ODC' item in M1

organization. Under

purchasing tab you will see that the buyer 'West, Rod'

populated.

Scenario: 9

~~~~~~~~~~~~~

In this scenario you will learn how to populate character and

number columns

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

and make it null.

^^^^^^^^^^^^^^^^^

Just from out above example we have buyer 'West, Rod' populated for

item 'ODC'.

Now for some reason we want to make null for buyer for item

ODC.

Buyer id column is a number column. To make it null we have to

populate -999999.

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,

organization_id,

inventory_item_id, BUYER_ID)

values

(1,1,'UPDATE',207,9372,-999999);

commit;

Now run the item import program ie. Item > Import

> Import Items.

In Process Set enter 1 as we had used 1 in the set_process_id

column. Now enter

2 in create or update items field and hit OK button.

You can check view requests to check whether your request has

completed

successfully.

Now go to organization items and query on 'ODC' item in M1

organization.

Under purchasing tab you will see that the buyer field is updated

to null.

Suppose planner 'S.Peters' is asscoiated to item ODC. You want to

make it null.

PLANNER_CODE in mtl_system_items_interface is a charracter type. To

make it

null we have to use '!'.

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,

organization_id,

inventory_item_id, PLANNER_CODE)

values

(1,1,'UPDATE',207,9372,'!');

commit;

Now run the item import program ie. Item > Import

> Import Items.

In Process Set enter 1 as we had used 1 in the set_process_id

column. Now enter

2 in create or update items field and hit OK button.

You can check view requests to check whether your request has

completed

successfully.

Now go to organization items and query on 'ODC' item in M1

organization.

Under general planning tab you will see that the planner is updated

to null.

Note: To make number columns null use -999999 and

to make character columns

-----------------------------------------------------------------------------

null use '!' exclamation.

-------------------------

Frequently Asked Questions (FAQ):

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

a. How do I know what error has occurred?

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Ans . The process_flag in mtl_system_items_interface will be 3. Now

the error

message will be in mtl_interface_errors table.

The record in mtl_system_items_interface will have a

transaction_id.

There will be corresponding record with error code and explanation

in

mtl_interface_errors.

select transaction_id from mtl_system_items_interface where

process_flag = 3;

Now substitue the &transaction_id value in the

following sql by the value that

you get in the ablove sql. You should be able to see the

error.

select message_name,column_name,error_message from

mtl_interface_errors

where transaction_id = &transaction_id;

b. What things should I check if I getting errors?

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Ans. First check if you are populating the correct values in all

the fields.

Then check if there are any mandatory descriptive flexfield

columns. You can

go to application setup > flexfields

> descriptive > segments and on

query

type ?Items? in title field and then check each segment whether it

is mandatory

and whether it is doing any validation against some pre-existing

values in

some value set. If so check if the corresponding attributes are

populated

properly. Many times user forgets to populate mandatory values and

item import

fails.

Also check for invalid objects

Select object_name,object_type from all_objects where status =

'INVALID';

If any invalid objects please recompile those and retest item

import.

c. I run item import but records are not getting processed. The

process_flag

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

is still 1.

^^^^^^^^^^^

Ans. Check under sysadmin> concurrent managers

whether inventory namager

is running. Then check if there is suffcient memory and extent

spaces.

Use notes 1019721.6 and 1020085.6 to determine if there is a memory

or

extents issue. Please check if there is sufficient roll back

segments ,

system global area and tablespace.

Please use the following truncate . This frees up space.

TR

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值