Translate

Monday, August 25, 2014

Table Lock in ASCP application

Script to Find the Table Lock

SELECT NVL(S.USERNAME,'Internal') username,
NVL(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# KILL,
U1.NAME||'.'||SUBSTR(T1.NAME,1,20) tab,
DECODE(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',NULL) lmode,
DECODE(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',NULL) request
FROM V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
WHERE L.SID = S.SID
AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,L.ID2)
AND U1.USER# = T1.OWNER#
AND S.TYPE != 'BACKGROUND'
AND t1.NAME LIKE '%MSC%'
ORDER BY 1,2,5

3. dba can then kill these sessions
4. plan will continue normally.

Thursday, August 21, 2014

Re-Compile Invalid Schema Objects

Recompiling Invalid Schema Objects


Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.

Identifying Invalid Objects
The Manual Approach
Custom Script
DBMS_UTILITY.compile_schema
UTL_RECOMP
utlrp.sql and utlprp.sql
Identifying Invalid Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query.

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.

The Manual Approach

For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types.

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations.

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
This method is limited to PL/SQL objects, so it is not applicable for views.

Custom Script

In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
            '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.

DBMS_UTILITY.compile_schema

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');
UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below.

 PROCEDURE RECOMP_SERIAL(
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
   threads  IN   PLS_INTEGER DEFAULT NULL,
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);
The usage notes for the parameters are listed below.

schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags - Used for internal diagnostics and testing only.
The following examples show how these procedures are used.

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');
There are a number of restrictions associated with the use of this package including:

Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Runnig DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

For more information see:

DBMS_UTILITY.compile_schema
UTL_RECOMP

Thursday, August 14, 2014

Create Incomplete PO From ASCP Release Work Bench

Create Incomplete PO From ASCP Release Work Bench 


There is no Standard Function available in ASCP to Release the as Incomplete from ASCP

But a Simple Trigger Can Fix This Requirement

For ASCP:  Add a trigger on table msc_purchase_requisitions_interface (on destination), to change the status before insert on MSC_PO_REQUISITIONS_INTERFACE for each row

begin
if :NEW.AUTHORIZATION_STATUS = 'APPROVED' then
:NEW.AUTHORIZATION_STATUS:='INCOMPLETE';
end if;
end;


Hope the Information is Helpful 

Wednesday, August 13, 2014

Released From ASCP- Required Completion Date In OPM Batch Is Earlier Than The Batch Start Date


Tuesday, August 12, 2014

Re open Purchase Orders - Oracle ( Mass Cancel)

Finally closed or Canceled PO's cannot be reopened.


This is the standard functionality of the application. Finally Close or Cancel actions are meant to prevent further modifications to the purchase order. This includes billing or receiving. Moreover there is a warning message whenever these actions are performed. The user is warned that this cannot be undone.

If there is a receipt against this PO and the requirement is to return it, it will be necessary to create a miscellaneous issue in Inventory for the concerned quantity and it will adjust the inventory stock.


If there is an invoice against this PO and the requirement is to cancel it, it will be necessary to create a credit memo using the same information entered on the original invoice.

Wednesday, August 6, 2014

Mass Cancel Sales Order API Oracle

Mass Cancel Sales Order API Oracle 


You can use the apps.oe_order_pub.process_order to cancel an order line.

Also note that this code was used to create a new order line.



-- Sales Order Cancel Script to be used in R12

set serveroutput on
DECLARE
 p_api_version_number        NUMBER :=1.0;
 p_init_msg_list             VARCHAR2(10) := FND_API.G_FALSE;
 p_return_values             VARCHAR2(10) := FND_API.G_FALSE;
 p_action_commit             VARCHAR2(10) := FND_API.G_FALSE;
 x_return_status             VARCHAR2(1);
 x_msg_count                 NUMBER;
 x_msg_data                  VARCHAR2(100);
  
 l_header_rec                OE_ORDER_PUB.Header_Rec_Type;
 l_header_adj_tbl            OE_ORDER_PUB.Header_Adj_Tbl_Type;
 l_header_scr_tbl            OE_ORDER_PUB.Header_Scredit_Tbl_Type;
 l_action_request_tbl        OE_ORDER_PUB.Request_Tbl_Type;

 l_line_tbl                  OE_ORDER_PUB.Line_Tbl_Type;
 l_line_adj_tbl              OE_ORDER_PUB.line_adj_tbl_Type;
 l_line_scredit_tbl          OE_ORDER_PUB.Line_Scredit_Tbl_Type;

 l_return_status             VARCHAR2(1000);
 l_msg_count                 NUMBER;
 l_msg_data                  VARCHAR2(1000);

 p_action_request_tbl        OE_ORDER_PUB.Request_Tbl_Type :=  oe_order_pub.G_MISS_REQUEST_TBL;

 -- out variables need to be defined seperately in R12 as per doc bug 7337251  

 o_header_rec                OE_ORDER_PUB.Header_Rec_Type;
 o_line_tbl                  OE_ORDER_PUB.Line_Tbl_Type;
 o_action_request_tbl        OE_ORDER_PUB.Request_Tbl_Type;
 o_header_adj_tbl            OE_ORDER_PUB.Header_Adj_Tbl_Type;
 o_line_adj_tbl              OE_ORDER_PUB.line_adj_tbl_Type;
 o_header_scr_tbl            OE_ORDER_PUB.Header_Scredit_Tbl_Type;
 o_line_scredit_tbl          OE_ORDER_PUB.Line_Scredit_Tbl_Type;

 o_header_val_rec            OE_ORDER_PUB.Header_Val_Rec_Type;
 o_Header_Adj_val_tbl        OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
 o_Header_price_Att_tbl      OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
 o_Header_Adj_Att_tbl        OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
 o_Header_Adj_Assoc_tbl      OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
 o_Header_Scredit_tbl        OE_ORDER_PUB.Header_Scredit_Tbl_Type;
 o_Header_Scredit_val_tbl    OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
 o_line_val_tbl              OE_ORDER_PUB.Line_Val_Tbl_Type;
 o_Line_Adj_val_tbl          OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
 o_Line_price_Att_tbl        OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
 o_Line_Adj_Att_tbl          OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
 o_Line_Adj_Assoc_tbl        OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
 o_Line_Scredit_val_tbl      OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
 o_Lot_Serial_tbl            OE_ORDER_PUB.Lot_Serial_Tbl_Type;
 o_Lot_Serial_val_tbl        OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;

 X_DEBUG_FILE VARCHAR2(100);
 l_msg_index_out NUMBER(10);
BEGIN
 dbms_output.enable(1000000);
 Fnd_Global.apps_initialize(1318,21623,660);
 MO_GLOBAL.INIT('ONT'); -- MOAC 
 oe_msg_pub.initialize;
 oe_debug_pub.initialize;
 X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
 oe_debug_pub.SetDebugLevel(5); -- Use 5 for the most debuging output, I warn  you its a lot of data
 dbms_output.put_line('START OF NEW DEBUG');

 --This is to cancel an order
 -- Initialize the record to missing

 l_header_rec                   := OE_ORDER_PUB.G_MISS_HEADER_REC;
 l_header_rec.header_id         := 193343;
 l_header_rec.cancelled_flag    := 'Y';
 l_header_rec.change_reason     := 'Not provided';
 l_header_rec.operation         := OE_GLOBALS.G_OPR_UPDATE;

 -- Call To Process Order API

 OE_ORDER_PUB.process_order (
  p_api_version_number       => 1.0
  , p_init_msg_list          => fnd_api.g_false
  , p_return_values          => fnd_api.g_false
  , p_action_commit          => fnd_api.g_false
  , x_return_status          => l_return_status
  , x_msg_count              => l_msg_count
  , x_msg_data               => l_msg_data
  , p_header_rec             => l_header_rec
  , p_line_tbl               => l_line_tbl
  , p_action_request_tbl     => l_action_request_tbl
  -- OUT PARAMETERS
  , x_header_rec             => o_header_rec
  , x_header_val_rec         => o_header_val_rec
  , x_Header_Adj_tbl         => o_Header_Adj_tbl
  , x_Header_Adj_val_tbl     => o_Header_Adj_val_tbl
  , x_Header_price_Att_tbl   => o_Header_price_Att_tbl
  , x_Header_Adj_Att_tbl     => o_Header_Adj_Att_tbl
  , x_Header_Adj_Assoc_tbl   => o_Header_Adj_Assoc_tbl
  , x_Header_Scredit_tbl     => o_Header_Scredit_tbl 
  , x_Header_Scredit_val_tbl => o_Header_Scredit_val_tbl
  , x_line_tbl               => o_line_tbl
  , x_line_val_tbl           => o_line_val_tbl
  , x_Line_Adj_tbl           => o_Line_Adj_tbl
  , x_Line_Adj_val_tbl       => o_Line_Adj_val_tbl
  , x_Line_price_Att_tbl     => o_Line_price_Att_tbl
  , x_Line_Adj_Att_tbl       => o_Line_Adj_Att_tbl
  , x_Line_Adj_Assoc_tbl     => o_Line_Adj_Assoc_tbl
  , x_Line_Scredit_tbl       => o_Line_Scredit_tbl
  , x_Line_Scredit_val_tbl   => o_Line_Scredit_val_tbl
  , x_Lot_Serial_tbl         => o_Lot_Serial_tbl
  , x_Lot_Serial_val_tbl     => o_Lot_Serial_val_tbl
  , x_action_request_tbl     => o_action_request_tbl
 );

 dbms_output.put_line('OM Debug file: '  ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
 FOR i IN 1 .. l_msg_count
 LOOP
  Oe_Msg_Pub.get( p_msg_index => i
   , p_encoded => Fnd_Api.G_FALSE
   , p_data => l_msg_data
   , p_msg_index_out => l_msg_index_out
  );
  DBMS_OUTPUT.PUT_LINE('message is: ' || l_msg_data);
  DBMS_OUTPUT.PUT_LINE('message index is: ' || l_msg_index_out);
 END LOOP;
 -- Check the return status
 IF l_return_status = FND_API.G_RET_STS_SUCCESS
 THEN
  dbms_output.put_line('Process order Sucess');
 ELSE
  dbms_output.put_line('Failed');
 END IF;
-- debug output
 dbms_output.put_line('Debug Output');
 FOR i in 1..OE_DEBUG_PUB.g_debug_count
 LOOP
  dbms_output.put_line(OE_DEBUG_PUB.G_debug_tbl(i));
 END LOOP;
 END;
/
commit;

Saturday, July 26, 2014

Invalid Object MSC_EXA_UTIL

Step if the package MSC_EXA_UTIL is invalid

Error statement in Package body(Line 23)

FUNCTION check_exa_nodes return number IS
vCount number;
BEGIN
 select count(*) into vCount from v$cell;
 if ( vCount > 0) then

SOLUTION

This is due to a missing grant.

To allow the package to be valid run the command below ss sys user,


SQL> GRANT SELECT ON V_$CELL TO APPS;