Package Compilation takes long time ...

Oracle will lock the package exclusively when the user attempt to compile the package. Incase if the package is used by other process, the compilation hangs..

Resolution:

1. Identify the package which is locked

SELECT * FROM DBA_DDL_LOCKS WHERE name = 'package name';

2. Get the sid

select * from v$access where object='package name';

3. Get the serial#

select sid,serial# from v$session where sid=;

4. Kill the session
alter system kill session 'sid,serial#';

Script to exclude the control M(^M) from unix files

Script to exclude the control M(^M) from unix files
The simple way to replace the control M character in UNIX format is as follows.

1. dos2unix filename
or
2. Open the file which contains the control M character and type the following
:%s/Control v control M//g

How to get Sales Order information - Total- freights charges

SELECT oola.line_number "LINE NUMBER", oola.ordered_item "ORDERED ITEM",
oola.ordered_quantity "QTY", oola.order_quantity_uom "UOM",
oola.unit_selling_price "UNIT SELLING PRICE",
oola.cancelled_quantity "QTY CANCELLED",
oola.shipped_quantity "QTY SHIPPED", oola.tax_code "TAX CODE",
ott.NAME "LINE TYPE",
DECODE (opa.line_id,
NULL, DECODE (opa.credit_or_charge_flag,
'C', (-1) * opa.operand,
opa.operand
),
DECODE (opa.credit_or_charge_flag,
'C', DECODE (opa.arithmetic_operator,
'LUMPSUM', (-1) * (opa.operand),
(-1)
* ( oola.ordered_quantity
* opa.adjusted_amount
)
),
DECODE (opa.arithmetic_operator,
'LUMPSUM', opa.operand,
(oola.ordered_quantity * opa.adjusted_amount
)
)
)
) "LINE_CHARGES",
ol.meaning "CALCULATE PRICE FLAG", oola.pricing_quantity,
oola.unit_selling_price, oola.unit_list_price, oola.tax_value,(oola.shipped_quantity) * (oola.unit_selling_price) "LINE TOTAL",
((oola.shipped_quantity) * (oola.unit_selling_price)
)
+ (DECODE (opa.line_id,
NULL, DECODE (opa.credit_or_charge_flag,
'C', (-1) * opa.operand,
opa.operand
),
DECODE (opa.credit_or_charge_flag,
'C', DECODE (opa.arithmetic_operator,
'LUMPSUM', (-1) * (opa.operand),
(-1)
* ( oola.ordered_quantity
* opa.adjusted_amount
)
),
DECODE (opa.arithmetic_operator,
'LUMPSUM', opa.operand,
(oola.ordered_quantity * opa.adjusted_amount
)
)
)
)
) "ORDER TOTAL"
FROM apps.oe_order_lines_all oola,
apps.oe_transaction_types_tl ott,
apps.oe_price_adjustments opa,
apps.oe_order_headers_all ooha,
apps.oe_lookups ol
WHERE oola.line_type_id = ott.transaction_type_id
AND opa.header_id = ooha.header_id
AND opa.line_id = oola.line_id(+)
AND opa.list_line_type_code = 'FREIGHT_CHARGE'
AND opa.applied_flag = 'Y'
AND ott.LANGUAGE = USERENV ('LANG')
AND ooha.order_number = 1004781
AND ol.lookup_type = 'CALCULATE_PRICE_FLAG'
AND oola.calculate_price_flag = ol.lookup_code