Pages

Saturday, June 30, 2012

Inventory Period - Resubmitting Unprocessed Materials

I have a few records showing it is currently background processing and records are locked but actually they are not being processed.

From the Oracle Application Form you cannot re-submitting those records because the records are locked. Due to this error you cannot close your month-end inventory period.

Here is how you can re-submit those locked transaction via SQL-Plus

SQL> select process_flag, lock_flag, transaction_mode, error_code
from mtl_transactions_interface;


PROCESS_FLAG     LOCK_FLAG      TRANSACTION_MODE   ERROR_CODE
--------------------------------------------------------------------------------
           1                                1                                       3
           1                                1                                       3
           1                                1                                       3

SQL> update mtl_transactions_interface set process_flag = 1, lock_flag = 2, transaction_mode = 3, error_code = null where transaction_id = '&transaction_id';

3 rows updated.

SQL> commit;

Commit complete.

Records should be picked up by background process and it should process the records again.
Posted by at No comments:
Labels: ,

Friday, June 29, 2012

Update Option Class BOM WIP_SUPPLY_TYPE


Using BOM_INVENTORY_COMPS_INTERFACE



SEGMENT1 OPTIONAL WIP_SUPPLY_TYPE MUTUALLY_EXCLUSIVE_OPTIONS
420K60D8 1 2 2
917Y66D8 1 2 2
156A66S4 1 2 2
156A66S8 1 2 2
157A66D4 1 2 2
157A66D8 1 2 2
157A66S4 1 2 2
157A66S8 1 2 2
156A66D4 1 2 2
156A66D8 1 2 2
437A60D4 1 2 2
437A60D8 1 2 2
437A60S4 1 2 2

 I have some data needs to change from WIP_SUPPLY_TYPE = 2 to WIP_SUPPLY_TYPE = 6.

WIP_SUPPLY_TYPE = 2 means Assembly Pull
WIP_SUPPLY_TYPE = 6 means Phantom.

Insert into apps.bom_inventory_comps_interface
(component_sequence_id, wip_supply_type, process_flag, transaction_type)
SELECT component_sequence_id, 6 wip_supply_type
, 1 process_flag, 'UPDATE' transaction_type
from (SELECT B.SEGMENT1
, D.SEGMENT1
, BIC.OPTIONAL
, BIC.COMPONENT_SEQUENCE_ID
, BIC.WIP_SUPPLY_TYPE
, BIC.MUTUALLY_EXCLUSIVE_OPTIONS
FROM apps.BOM_BILL_OF_MATERIALS A, apps.MTL_SYSTEM_ITEMS_B B, apps.BOM_INVENTORY_COMPONENTS BIC, apps.MTL_SYSTEM_ITEMS_B D
, apps.HR_ORGANIZATION_UNITS HR
WHERE A.ASSEMBLY_ITEM_ID = B.INVENTORY_ITEM_ID
AND BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.ORGANIZATION_ID = HR.ORGANIZATION_ID
AND HR.NAME LIKE '%Master%'
AND B.SEGMENT1 = 'SLAB'
AND BIC.DISABLE_DATE IS NULL
AND BIC.COMPONENT_ITEM_ID = D.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = B.ORGANIZATION_ID
AND BIC.WIP_SUPPLY_TYPE = 2
AND (D.SEGMENT1 LIKE '%8' OR D.SEGMENT1 LIKE '%4'))

HR.NAME is your Master Inventory Organization name
B.SEGMENT1 is one of your option classes name
D.SEGMENT1 is the element that you want to make changes to.

Basically above query will insert records into BOM_INVENTORY_COMPS_INTERFACE table with ITEMs ends with 8 or 4 and WIP_SUPPLY_TYPE as Assembly Pull under SLAB OPTION CLASS.

I want to update WIP_SUPPLY_TYPE CODE for above option class components to be PHANTOM which is WIP_SUPPLY_TYPE value as 6.

SQL> insert into apps.bom_inventory_comps_interface
(component_sequence_id, wip_supply_type, process_flag, transaction_type)
SELECT component_sequence_id, 6 wip_supply_type, 1 process_flag, 'UPDATE' transaction_type
from (SELECT B.SEGMENT1
, D.SEGMENT1
, BIC.OPTIONAL
, BIC.COMPONENT_SEQUENCE_ID
, BIC.WIP_SUPPLY_TYPE
, BIC.MUTUALLY_EXCLUSIVE_OPTIONS
FROM apps.BOM_BILL_OF_MATERIALS A, apps.MTL_SYSTEM_ITEMS_B B, apps.BOM_INVENTORY_COMPONENTS BIC, apps.MTL_SYSTEM_ITEMS_B D
, apps.HR_ORGANIZATION_UNITS HR
WHERE A.ASSEMBLY_ITEM_ID = B.INVENTORY_ITEM_ID
AND BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.ORGANIZATION_ID = HR.ORGANIZATION_ID
AND HR.NAME LIKE '%Master%'
AND B.SEGMENT1 = 'SLAB'
AND BIC.DISABLE_DATE IS NULL
AND BIC.COMPONENT_ITEM_ID = D.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = B.ORGANIZATION_ID
AND BIC.WIP_SUPPLY_TYPE = 2
AND (D.SEGMENT1 LIKE '%8' OR D.SEGMENT1 LIKE '%4'));

227 rows created.

SQL> commit;

Login to your Application and Go to Bills Of Material Super User





Choose Master Inventory Organization



All Oganizations to No
Import Routings No -- Since we don't use any routings.

Run the request.

BOM_INVENTORY_COMPS_INTERFACE should be cleaned after successfully update components.
Posted by at No comments:

BOM Open Interfaces

BOM Open Interfaces - An Oracle White Paper [185958.1]

After populating the interface tables, you can run the Bill and Routing Interface program.
The column, PROCESS_FLAG, indicates the current state of processing for a row in the interface table. Possible values for the column include:

1 - Pending 
2 - Assigned Succeeded 
3 - Assign/Validation Failed 
4 - Validation Succeeded 
7 - Import Succeeded 

You can use the following sql scripts to check for errors which will be located in the MTL_INTERFACE_ERRORS table to help resolve problems.

select table_name, column_name, organization_id, message_name, error_message
from mtl_interface_errors
where request_id = xxxx; (xxxx is the concurrent request id)
or
select table_name, column_name, organization_id, message_name, error_message
from mtl_interface_errors
where transaction_id = xxxx; (xxxx is the transaction id)

BOM_BILL_OF_MTLS_INTERFACE TABLE

This table temporarily stores bills of material header information. Each row in this table represents a unique Manufacturing or Engineering bill and is identified by the BILL_SEQUENCE_ID.

If you are creating an alternate bill of material you must also enter a value in the ALTERNATE_BOM_DESIGNATOR column.
If you enter something into REVISION, Oracle Bills of Materials will insert a record into
MTL_ITEM_REVISIONS.
If the bill you import references a common bill of material, you must enter a value in the
COMMON_ORGANIZATION_ID and COMMON_ASSEMBLY_ITEM_ID columns or you can enter a value in the COMMON_BILL_SEQUENCE_ID column.
The Bill and Routing Interface program derives the COMMON_BILL_SEQUENCE_ID from the
COMMON_ITEM_ID, COMMON_ORGANIZATION_ID, AND ALTERNATE_BOM_DESIGNATOR.

The required fields to create a Bill in the BOM_BILL_OF_MTLS_INTERFACE table are:
  • PROCESS_FLAG
  • ORGANIZATION_ID
  • ASSEMBLY_ITEM_ID
  • ASSEMBLY_TYPE
  • TRANSACTION TYPE
  • The PROCESS_FLAG needs to be 1 for pending.
  • The ASSEMBLY_TYPE of the BOM; is 1 for manufacturing, and 2 for engineering.
  • The TRANSACTION_TYPE needs to be 'create'.

The required fields to update a Bill using the BOM_BILL_OF_MTLS_INTERFACE table are:
  • PROCESS_FLAG
  • BILL_SEQUENCE_ID 
or
  • ASSEMBLY_ITEM_ID or ITEM_NUMBER,
  • ALTERNATE_BOM_DESIGNATOR, and
  • ORGANIZATION_ID or ORGANIZATION_CODE
  • TRANSACTION_TYPE
  • The PROCESS_FLAG needs to be 1 for pending.
  • The TRANSACTION_TYPE needs to be 'update'.

The required fields to delete a Bill using the BOM_BILL_OF_MTLS_INTERFACE table are:
  • PROCESS_FLAG
  • BILL_SEQUENCE_ID 
or
  • ALTERNATE_BOM_DESIGNATOR,
  • ASSEMBLY_ITEM_ID or ITEM_MUMBER, and
  • ORGANIZATION_ID or ORGANIZATION_CODE
  • TRANSACTION_TYPE
The PROCESS_FLAG needs to be 1 for pending.
The TRANSACTION_TYPE needs to be 'delete'.

In order to delete a Bill using the interface table, you must also insert a record into the
BOM_INTERFACE_DELETE_GROUPS table with the following values:

ENTITY_NAME (Must be BOM_BILL_OF MTLS_INTERFACE)
DELETE_GROUP_NAME (A new name or name of an existing Delete Group for bills)
DESCRIPTION (Description of the delete group)

BOM_INVENTORY_COMPS_INTERFACE

This table temporarily stores information about bill of material components and is shared by the Bills of Material and the Engineering Change Order open interfaces. The Engineering Change Order open interface will be available at a future date.


You cannot dynamically create locations for your subinventories using the interface tables. If you specify a value for the LOCATION_ID column, the location must already exist in Oracle Inventory.
 
You must specify a value in the ALTERNATE_BOM_DESIGNATOR column if you assign components to an alternate bill of material and have not entered a value for the BILL_SEQUENCE_ID column.

You can enter one reference designator and one substitute component for each component you enter.
Oracle Bills of Materials will insert these records into the tables, BOM_REFERENCE_DESIGNATORS and
BOM_SUBSTITUTE_COMPONENTS, respectively.

Oracle Bill of Material, enforces that only one row for a component in an operation is effective at a given time.




The required fields for creating components using the BOM_INVENTORY_COMPS_INTERFACE are:

  • PROCESS_FLAG
  • COMPONENT_ITEM_ID
  • COMPONENT_SEQUENCE_ID - user should not be populating the component sequence id as it gets generated during the import
  • OPERATION_SEQ_NUM
  • EFFECTIVITY_DATE
  • BILL_SEQUENCE_ID
  • TRANSACTION_TYPE
The PROCESS_FLAG needs to be 1 for pending.

The TRANSACTION_TYPE needs to be 'create' for creating a component.

Note: the OPERATION_SEQ_NUM must be set to 1 if no routing has been defined.

The required fields for updating components using the BOM_INVENTORY_COMPS_INTERFACE are:
  • COMPONENT_SEQUENCE_ID 
or
  • BILL_SEQUENCE_ID,
  • COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
  • OPERATION_SEQ_NUM, and EFFECTIVITY_DATE 
or
  • ASSEMBLY_ITEM_ID or ASSEMBLY_ITEM_NUMBER, ALTERNATE_BOM_DESIGNATOR,
  • ORGANIZATION_ID or ORGANIZATION_CODE,
  • COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
  • OPERATION_SEQ_NUM, and EFFECTIVITY_DATE
You also need to enter:

  • PROCESS_FLAG
  • TRANSACTION_TYPE
If the component is unimplemented, you cannot update the record.

If you want to update the OPERATION_SEQ_NUM or EFFECTIVITY_DATE, then you must fill in the NEW_OPERATION_SEQ_NUM or NEW_EFFECTIVITY_DATE column.

The PROCESS_FLAG needs to be 1 for pending.

The TRANSACTION_TYPE needs to be 'update' for updating a component.

The required fields for deleting components using the BOM_INVENTORY_COMPS_INTERFACE are:
  • COMPONENT_SEQUENCE_ID
or
  • BILL_SEQUENCE_ID,
  • COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
  • OPERATION_SEQ_NUM, and EFFECTIVITY_DATE
or
  • ASSEMBLY_ITEM_ID or ASSEMBLY_ITEM_NUMBER, ALTERNATE_BOM_DESIGNATOR,
  • ORGANIZATION_ID or ORGANIZATION_CODE,
  • COMPONENT_ITEM_ID or COMPONENT_ITEM_NUMBER,
  • OPERATION_SEQ_NUM, and EFFECTIVITY_DATE
You also need to enter:
  • PROCESS_FLAG
  • TRANSACTION_TYPE
If the component is unimplemented, you cannot delete the record.

The PROCESS_FLAG needs to be 1 for pending.

The TRANSACTION_TYPE needs to be 'delete'.

In order to delete a component using the interface table, you must also insert a record into the
BOM_INTERFACE_DELETE_GROUPS table with the following values:

ENTITY_NAME (Must be BOM_INVENTORY_COMPS_INTERFACE)
DELETE_GROUP_NAME (A new name or name of an existing Delete Group for bills)
DESCRIPTION (Description of the delete group)
Posted by at No comments:

RMAN Duplicate Database From RAC ASM To RAC ASM [ID 461479.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4 - Release: 10.1 to 10.2
Information in this document applies to any platform.

Goal

RMAN Duplicate Database From RAC ASM to RAC ASM

Solution

Assumptions


This note assumes the following:

You are using recovery catalog database catdb

The target database trgt is on host1 and contains ASM datafiles and online logs in diskgroup +DISK1

You want to duplicate the target to database dupdb on remote host host2.

host2 has diskgroup +DISK2

You want to store the datafiles for dupdb to +DISK2

You want to store two controlfiles in +DISK2

The backups and archivelogs created by host1 are accessible by host2

Procedure

1) Create an initialization parameter for auxiliary instance by copying the target database initialization parameter file. Change the parameters as follows:

Set DB_NAME to the new database name dupdb

Set CONTROL_FILES to store two copies of the control file in +DISK2

Set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to convert the datafile and online log file names from +DISK1 to +DISK2

Set any other initialization parameters that end in _DEST, such as DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_DEST_n, to reference +DISK2

Comment all the RAC related parameters in the dupdb parameter file. We will re-enable them once the duplicate is completed.
2) For an active production RAC environment, you must use a SET UNTIL clause, otherwise the archivelogs would not have been backed up and the duplicate would fail with RMAN-06025.

To get the sequence number, you need to look at the following:

   RMAN> list backup of archivelog all;

We need to determine the most recent archivelog backed up from each thread, and then take the least recent of these.


Example

Parameter file (init.ora) would include:
DB_NAME=dupdb
CONTROL_FILES=+DISK2,+DISK2
DB_FILE_NAME_CONVERT=+DISK1,+DISK2
LOG_FILE_NAME_CONVERT=+DISK1,+DISK2


Create an SPFILE from the parameter file, and start the auxiliary instance:

SQL> CONNECT SYS/oracle@dupdb;
SQL> CREATE SPFILE FROM PFILE='location of auxiliary instance pfile';
SQL> STARTUP NOMOUNT;


Then run the following RMAN commands to duplicate the database:


RMAN> CONNECT TARGET /;
RMAN> CONNECT CATALOG rman/cat@catdb;
RMAN> CONNECT AUXILIARY SYS/oracle@dupdb;
RMAN> run {
set until sequence x thread y;
DUPLICATE TARGET DATABASE TO dupdb;
}
 ==> replace the sequence and thread number as appropriate


When the DUPLICATE command completes, the duplicate database is created, with datafiles, online logs and control files in ASM disk group +DISK2.

Shutdown the database and add all the RAC related parameters and then start the database.
Note:-  The only difference between duplicate from RAC ASM and standalone ASM is not to perform the last step i.e. adding of the RAC related parameters.
Posted by at No comments:

CRS-0223: Resource 'ResourceName' has placement error

I noticed some services were started and some other weren't.  When I tried to start up the services, the 'placement' error showed up.

[oracle@pclone bin]$ ./srvctl start instance -d amct -i amct1
PRKP-1001 : Error starting instance amct1 on node pclone
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource 'ora.amct.amct1.inst' has placement error.

[oracle@pclone bin]$ ./crs_stat -t
Name           Type           Target    State     Host       
------------------------------------------------------------
ora....t1.inst application    ONLINE    UNKNOWN   pclone     
ora.amct.db    application    OFFLINE   OFFLINE              
ora....SM1.asm application    ONLINE    ONLINE    pclone     
ora.pclone.gsd application    ONLINE    ONLINE    pclone     
ora.pclone.ons application    ONLINE    ONLINE    pclone     
ora.pclone.vip application    ONLINE    ONLINE    pclone


I restarted  the CRS manually but still did not have any luck.
change user to root and then stop CRS.  remove temporary files and then restart the CRS.

[oracle@pclone bin]$ su -
[root@pclone bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

rm -f /var/tmp/.oracle/*
./crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly


Wait until CRS gets started.
[root@pclone bin]# ./crs_stat -t
Name           Type           Target    State     Host       
------------------------------------------------------------
ora....t1.inst application    ONLINE    OFFLINE              
ora.amct.db    application    OFFLINE   OFFLINE              
ora....SM1.asm application    ONLINE    OFFLINE              
ora.pclone.gsd application    ONLINE    OFFLINE              
ora.pclone.ons application    ONLINE    OFFLINE              
ora.pclone.vip application    ONLINE    OFFLINE               Still No Luck ~~~.

So Let's find out OCR Configuration.

[oracle@pclone bin]$ ./srvctl config database -d amct -a
pclone amct1 /u01/oracle/amctdb/10.2.0/db_1
DB_NAME: null
ORACLE_HOME: /u01/oracle/amctdb/10.2.0/db_1
SPFILE: null
DOMAIN: null
DB_ROLE: null
START_OPTIONS: null
POLICY:  AUTOMATIC
ENABLE FLAG: DB ENABLED

As per the output of OCR Configuration "DB_NAME" and "SPFILE" are null and this seems to be the reason the instance was not coming up using srvctl start instance command.


srvctl modify database syntax

The srvctl modify database command is used to modify a database configurations in the  OCR (Oracle cluster registry).
The OCR is the repository used by the CRS (cluster readu services) processes to run a database in RAC environment.

Syntax:
srvctl modify database -d db_unique_name [-n db_name] [-o oracle_home] [-m domain_name]
  [-p spfile] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s start_options] [-y {AUTOMATIC | MANUAL}]

-d database name (unique name)
-n db_name: database name if different from unique name
-o oracle_home:Oracle home
-m domain_name: Domain name of the database
-p spfile: server parameter file
-r role [PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY]: Role of the database
-s start_options:Startup options for the database.
-y Management policy for the database, either automatic or manual.
-h




./srvctl modify database -d amct -p '/u01/oracle/amctdb/10.2.0/db_1/dbs/spfileamct1.ora' -n amct -s open
[oracle@pclone bin]$ ./srvctl config database -d amct -a
pclone amct1 /u01/oracle/amctdb/10.2.0/db_1
DB_NAME: amct
ORACLE_HOME: /u01/oracle/amctdb/10.2.0/db_1
SPFILE: /u01/oracle/amctdb/10.2.0/db_1/dbs/spfileamct1.ora
DOMAIN: null
DB_ROLE: null
START_OPTIONS: open
POLICY:  AUTOMATIC
ENABLE FLAG: DB ENABLED

Once configuration is modified this seems to fix the issue.

Posted by at No comments:
Subscribe to: Posts (Atom)