chandramouly.com

February 6, 2009

Query to find the locked objects in Oracle

Filed under: SQL, scripts — chandramouly @ 9:11 pm

SELECT v.sid, v.serial#
FROM v$locked_object a , all_objects b, v$session v
WHERE a.object_id = b.object_id
and a.session_id = v.sid
AND b.object_name like ‘RA%’;

Once you find the locked objects, you can kill the session allowing the users to update or delete the records further.

The SID and SERIAL# values of the relevant session can then be substituted into the following statement:

SQL> ALTER SYSTEM KILL SESSION ’sid,serial#’;

Issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an Oracle session. If the marked session persists for some time you may consider killing the process at the operating system level, as explained below. Killing OS processes is dangerous and can lead to instance failures, so do this at your own peril.

It is possible to force the kill by adding the IMMEDIATE keyword:

SQL> ALTER SYSTEM KILL SESSION ’sid,serial#’ IMMEDIATE;

Comments Off

March 24, 2008

Techlog

Filed under: Blogroll — chandramouly @ 10:41 pm

One of my friend started a tech blog(venkat’s techlog), he is a linux geek and it was coincident to see another site which is just named techlog.org (group of microsoft enthusiasts who own this site).
I got this info when I just googling and wiki’ing about techlog…

All the best Venkat!

Comments Off

February 12, 2008

PLL to PLD, PLX conversion - oracle apps forms

Filed under: 11i — chandramouly @ 1:24 am

To convert from CUSTOM.pll to CUSTOM.pld
f60gen module_type=LIBRARY module=CUSTOM script=YES userid=apps/apps

To convert back from CUSTOM.pld to CUSTOM.pll
f60gen module_type=LIBRARY module=CUSTOM parse=YES userid=apps/apps

To convert from CUSTOM.pll to CUSTOM.plx
f60gen module_type=LIBRARY module=CUSTOM userid=apps/apps

Comments (0)

February 4, 2008

Oracle Workflow Definitions Download Upload

Filed under: Uncategorized — chandramouly @ 9:45 pm

WFLOAD 0 Y {UPLOAD | DOWNLOAD | UPGRADE | FORCE} [@:] []

To upload workflow definition for sample modified custom workflow customwf.wft

WFLOAD apps/apps O Y UPLOAD customwf.wft

To download a workflow definition from database and write it as a flat file in the disk. This is a common case that we should always do before modifying any files in Production database.

WFLOAD apps/apps O Y DOWNLOAD customwf.wft OEOH OEOL

In the above example, we are downloading only workflow item types of Order Header and Order Line.

To Force the server to take the existing or standard setup process from good running workflow definitions, this option can be used.

WFLOAD apps/apps O Y FORCE customwf.wft

Comments (0)

January 6, 2008

ORACLE apps useful SQL scripts

Filed under: SQL, 11i, scripts — chandramouly @ 10:22 am

To query a specific table in oracle apps:

SELECT *
FROM ALL_TABLES
WHERE
TABLE_NAME = ‘AP_INVOICES_ALL’ — Name of the table

To query a specific column with or without table name:

SELECT *
FROM ALL_TAB_COLUMNS
WHERE
COLUMN_NAME = ‘INVOICE_NUM’ — Name of the column

To query a specific view in apps:

SELECT *
FROM ALL_VIEWS
WHERE
VIEW_NAME = ‘AR_CUSTOMERS_V’ — Name of the view

– To search some text in a specific package or in a set of packages

SELECT *
FROM all_source
WHERE name like ‘PKG_NAME’ and — PACKAGE NAME
type = ‘PACKAGE BODY’
and UPPER(text) like ‘%some search text in package body%’ — SEARCH TEXT IN PKG

– To find some specific FND message text
SELECT *
FROM fnd_new_messages
WHERE message_text like ‘%Values have not %’

Oracle Apps scripts for Release 11i
———————————

1. To fetch customer information

SELECT *
FROM AR_CUSTOMERS_V
WHERE
CUSTOMER_NUMBER = 123;

2. To fetch supplier information

SELECT *
FROM PO_VENDORS
WHERE
VENDOR_NAME = ‘ABC’;

3. To fetch Purchase orders, sales orders and lines information

SELECT *
FROM OE_ORDER_HEADERS_ALL OEH,
OE_ORDER_LINES_ALL OEL
WHERE
OEH.HEADER_ID = OEL.HEADER_ID

4. To fetch Shipping information

SELECT *
FROM WSH_NEW_DELIVERIES
WHERE
DELIVERY_NAME = ‘123′ — Once an item is ship confirmed, the delivery is generated

5. To fetch Shipping Delivery details information:

SELECT *
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = 3403677 — This delivery id is joined with wsh_new_deliveries.delivery_id

6. To fetch Master Items information:

SELECT *
FROM MTL_SYSTEM_ITEMS_B
WHERE
SEGMENT1 = ‘ABC123′ — Item name

7. To fetch payables invoices information from AP

SELECT *
FROM AP_INVOICES_ALL INV,
AP_INVOICE_LINES_ALL LINES
WHERE
INV.INVOICE_NUM = 123
AND INV.INVOICE_ID = LINE.INVOICE_ID

8. To fetch Purchase orders information

SELECT *
FROM PO_HEADERS_ALL

9. To fetch interface tables from all products in ERP

SELECT *
FROM ALL_TABLES
WHERE TABLE_NAME LIKE ‘%INTERFACE%’

Idea! - How about creating a social network or community to share sql scripts, performance tuning and bidding on others scripts, finding issues, etc.,
looks crazzzyyyy huh ha….:)

Comments (0)

January 4, 2008

OE_ORDER_PUB.process_order - Sales Order Creation API

Filed under: Order Management, 11i, API — chandramouly @ 4:51 am

Even though we have search engines, so many user guides to browse, metalinks and oracle forums. Running some piece of code and getting it executed is pain. I was facing some issues in running the OE order creation API which I couldn’t figure out for sometime, then this blog link by Bryan Thompson helped me.

I was missing one of the sales order DFF values which is not null and trying to insert everything else through the API and getting this strange error,
FND FLEX - NULL. I got no clue from which OE package this error is coming, but its a standard error message (FND) coming from oracle when we tried to insert with default null value.

Final piece of working code,

v_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
v_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
v_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
v_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
v_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
v_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
v_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
v_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
v_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
v_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
v_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
v_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
v_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
v_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
v_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
v_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;

v_error XXC_RETURN_TO_VEND.ERROR_DESC%TYPE;

v_user_id NUMBER:=FND_GLOBAL.USER_ID;
v_resp_id NUMBER:=FND_GLOBAL.RESP_ID;
v_resp_appl_id NUMBER:=FND_GLOBAL.RESP_APPL_ID;
v_request_id NUMBER:=FND_GLOBAL.CONC_REQUEST_ID;

begin

fnd_global.apps_initialize(v_user_id, v_resp_id, v_resp_appl_id);

–Setting up header record —-

—Intializing record to missing
v_HeaderRec := OE_ORDER_PUB.G_MISS_HEADER_REC;

—-Required Attributes—-
v_HeaderRec.Order_Type_ID := 1;
v_HeaderRec.Sold_to_Org_ID := 1;
v_HeaderRec.price_list_id := 1;
v_HeaderRec.freight_terms_code := NULL;
v_HeaderRec.attribute15 := sysdate; — mandatory value in Order header DFF : /* THIS IS THE PLACE I MESSED UP */
v_HeaderRec.Operation := OE_GLOBALS.G_OPR_CREATE;

—- Intializing record to missing
v_lineTbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;

—-Required Attributes—-
v_lineTbl(1).Inventory_item_id := 1;
v_lineTbl(1).Ordered_Quantity := 1;
v_lineTbl(1).Operation := OE_GLOBALS.G_OPR_CREATE;

OE_ORDER_PUB.PROCESS_ORDER ( p_api_version_number , p_init_msg_list , p_return_values , p_action_commit , x_return_status , x_msg_count , x_msg_data , p_header_rec , p_line_tbl , p_action_request_tbl , x_header_rec , x_header_val_rec , x_Header_Adj_tbl , x_Header_Adj_val_tbl , x_Header_price_Att_tbl , x_Header_Adj_Att_tbl , x_Header_Adj_Assoc_tbl , x_Header_Scredit_tbl , x_Header_Scredit_val_tbl , x_line_tbl , x_line_val_tbl , x_Line_Adj_tbl , x_Line_Adj_val_tbl , x_Line_price_Att_tbl , x_Line_Adj_Att_tbl , x_Line_Adj_Assoc_tbl , x_Line_Scredit_tbl , x_Line_Scredit_val_tbl , x_Lot_Serial_tbl , x_Lot_Serial_val_tbl , x_action_request_tbl ); => 1.0
=> FND_API.G_TRUE
=> FND_API.G_TRUE
=> FND_API.G_TRUE
=> vReturnStatus
=> vMsgCount
=> vMsgData
=> v_HeaderRec
=> v_lineTbl
=> v_RequestTbl
=> v_Header_Rec
=> v_Header_Val_Rec
=> v_Header_Adj_Tbl
=> v_Header_Adj_Val_Tbl
=> v_Header_Price_Att_Tbl
=> v_Header_Adj_Att_Tbl
=> v_Header_Adj_Assoc_Tbl
=> v_Header_Scredit_Tbl
=> v_Header_Scredit_Val_Tbl
=> v_Line_Tbl
=> v_Line_Val_Tbl
=> v_Line_Adj_Tbl
=> v_Line_Adj_Val_Tbl
=> v_Line_Price_Att_Tbl
=> v_Line_Adj_Att_Tbl
=> v_Line_Adj_Assoc_Tbl
=> v_Line_Scredit_Tbl
=> v_Line_Scredit_Val_Tbl
=> v_Lot_Serial_Tbl
=> v_Lot_Serial_Val_Tbl
=> v_action_request_tbl

IF vReturnStatus <> ‘S’ THEN
FOR v_Index IN 1..vMsgCount
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,’ ‘||’ Order Creation FAILED!!!!!!!’);
vMsgData := OE_MSG_PUB.GET(p_msg_index => v_index,
p_encoded => ‘F’);
FND_FILE.PUT_LINE(FND_FILE.LOG,’ ‘||vMsgData);
–dbms_output.put_line ( vMsgData);
END LOOP;
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,’ ‘||’ Order Creation SUCCESS!!!!!!! ‘ ||v_Header_Rec.Order_number);
END IF;

This API code is for Release 11i and hope they will made better for FUSION.

Comments (0)

November 7, 2007

BIP - Getting started…

Filed under: BIP, XML Publisher — chandramouly @ 10:42 pm

This post will explain you how, where, what on BIP.

See the above screenshot, you can download the BIP Enterprise edition and BIP Desktop tool everything FREE! from the Oracle website, here.

First lets discuss about the BI Publisher Desktop a.k.a Template Builder for Word, unfortunately its available only for Windows and only for Microsoft Word, I am also searching Template Builder in PDF and for Microsoft Excel files. But I couldn’t get those template builders.
Unzip the file and install your BI Desktop tool, then you can find it in your Start Panel.

Goto Oracle BIP Desktop from the Start panel, then view the demos to have a good kick start in BIP.
We will see in the coming posts how to create simple BIP reports.

Comments (0)

November 3, 2007

Trash 2.0

Filed under: ideas — chandramouly @ 7:42 pm

This may look crazy, but as a developer I am in need of this, I need a Trash where I can go and look all useless scripts developed by other developers, architects during their dev phase, testing phase, etc., This may reduce my development time in creating all the scripts from scratch. At present to my knowledge there is no separate medium where I can go and see everyone’s scripts, useful shell commands and bookmark links etc.,
More… The Trash should be properly organized may be Web shared folder, it can highly adopt del.icio.us (internal) for sharing. I am also expecting some other useful documents, notes, etc in this space.

Comments (0)

October 30, 2007

I’m in Transition…

Filed under: general — chandramouly @ 7:26 pm

Yes! I moved from Hyderabad, India to California. Things are slowly getting settled down here, new company, new culture and new place.
Started blogging after long time, but from now I hope it will be in top gear!

Comments (0)

October 9, 2007

BarCampLA-4

Filed under: barcamp, unconference — chandramouly @ 7:55 am

Barcamp LA4

Most likely I will be attending Barcamp-LA4 - TBD.

Comments (0)