, , ,

Permission names for specific User

With the following query you can know what all the permissions you have for any Specific Application User.

I have used this query when working on some custom page. Based on the permissions given to the User, you can provide some additional functionality to the Users.

Note:- I have commented out the permission name. in the below query. You can use this condition if you want to search specific to the permission name.

SELECT fnd.user_id
, fnd.description
, p.permission_name
FROM jtf_auth_principals_b u
, jtf_auth_principal_maps pm
, jtf_auth_role_perms rp
, jtf_auth_permissions_b p
, fnd_user fnd
WHERE fnd.user_id=’Your USER ID’
AND fnd.user_name=u.principal_name
— AND p.permission_name =’CSI_SEARCH_PRODUCT_VIEW’
AND u.jtf_auth_principal_id = pm.jtf_auth_principal_id
AND pm.jtf_auth_parent_principal_id = rp.jtf_auth_principal_id
AND rp.jtf_auth_permission_id = p.jtf_auth_permission_id

Note:- You can know the USER ID of specific to some USER from the following Query.

select * from fnd_user where USER_NAME like ‘User Name’;

/by
, , ,

Install Base creation for all the Sales Order for which it is missing

You can using the following package to create the IB (Install base) for the sales Order It has missed.

Normally, we enable the Install base option in the “Master Item” form for the Items for which we want to track. If we enable this option, then when we create the sales order with the Item for which Install base option is check then, you would see the IB got created.

CREATE OR REPLACE PACKAGE alloracletech_ib_pkg
IS
PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER);
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2);
END alloracletech_ib_pkg;
/

CREATE OR REPLACE PACKAGE BODY alloracletech_ib_pkg
IS

PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER)
IS
/************************************************************************
Purpose : This procedure creates IB for all the order which are missed. 
*************************************************************************/
l_header_id NUMBER;
l_mtl_txn_id NUMBER;
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;

CURSOR c_ib
IS
SELECT — oeh.order_number, 
oel1.line_id 
— oeh.creation_date,
— msib1.inventory_item_id, 
— msib1.segment1
FROM oe_order_lines_all oel1,
mtl_system_items_b msib1,
oe_order_headers_all oeh
WHERE oel1.ordered_item = msib1.segment1
AND msib1.comms_nl_trackable_flag = ‘Y’
AND msib1.shippable_item_flag = ‘Y’
AND msib1.organization_id = 22
AND oeh.header_id = oel1.header_id
AND oel1.flow_status_code = ‘CLOSED’
AND oel1.line_category_code = ‘ORDER’
AND oeh.order_type_id = 1008
— AND oel1.line_id IN (5599900, 5742086)
— AND oeh.order_number IN ( )
AND oeh.shipping_method_code IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM csi.csi_item_instances cii
WHERE cii.last_oe_order_line_id = oel1.line_id);
BEGIN
FOR i IN c_ib
LOOP
BEGIN
SELECT transaction_id
INTO l_mtl_txn_id
FROM mtl_material_transactions
WHERE trx_source_line_id = i.line_id 
AND transaction_type_id = 33;

— dbms_output.put_line(i.order_number||’ ‘||i.line_id);
csi_inv_txn_hook_pkg.posttransaction
(p_header_id => l_header_id,
p_transaction_id => l_mtl_txn_id,
x_return_status => l_return_status
);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
WRITE (‘L’, ‘No Transaction ID for the Line ID : ‘ || i.line_id );
WHEN TOO_MANY_ROWS THEN
WRITE (‘L’,’More then one Transaction ID for the Line ID : ‘ || i.line_id );
WHEN OTHERS THEN
WRITE (‘L’,
‘Error in LineID :’ || i.line_id || CHR (10) || SQLCODE || ‘ : ‘ || SQLERRM
);
END;
END LOOP;
END create_install_base;

/************************************************************************/
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : This procedure writes to the output file or log file depending
on the parameter p_type passed.
*************************************************************************/
BEGIN
IF p_type = ‘L’
THEN
fnd_file.put_line (fnd_file.LOG, p_message);
ELSIF p_type = ‘O’
THEN
fnd_file.put_line (fnd_file.output, p_message);
END IF;
END WRITE;
END alloracletech_ib_pkg;
/

Use the following to Execute the Procedure. 

begin
alloracletech_ib_pkg.create_install_base;
end;
/

The following query can be used to find out whether Install Base (IB) created or not for the Order line.

select * from csi.csi_item_instances 
where last_oe_order_line_id IN (Your Order Line ID);

Example:-

select * from csi.csi_item_instances 
where last_oe_order_line_id IN (6912858, 6912859, 6912860);
I hope you find the above information help full.

Note:- There are few things which are hard-corded like order_type_id = 1008 etc, these are specific to my instance setup. It may vary for your Instance setup. Change the values accordingly.

Note:- I have tested above script in the 11i Instances.

/by
,

Display the number value in Words

The following query can be used to display the number in the words.

select ‘Your Number’, (to_char(to_date(‘Your Number’,’j’), ‘jsp’)) from dual;

Example:-

select 211, (to_char(to_date(211,’j’), ‘jsp’)) from dual;
/by
,

How to find Duplicate rows in the table

The following query can be used to get the duplicate records from table.

SELECT * FROM ‘Your table name’ WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM ‘Your Table Name’ GROUP BY ‘Your duplicate values field name’);

Example:-

SELECT * FROM emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY ename);

To eliminate/delete the duplicate rows from the table, you can use the following query.

DELETE ‘Your table name’ WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM ‘Your Table Name’ GROUP BY ‘Your duplicate values field name’);

Example:-

DELETE emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY
ename);
/by