How to Create Code Combination ID by API

I was on an integration assignment between Home Grown Project System and Oracle Payable. A big gap which I faced there was how to maintain single source of Account Code Combinations. We had following options to manage this issue.
Built a View of GL_CODE_COMBINATIONS table and pass Combination and ID from Oracle to Custom System
Use API and create Code Combination
I used second way to handle this. Here is script which i used in that Assignment ( it works for me but you need to be careful as it’s not documented any where 🙂
—–Create Function
CREATE OR REPLACE FUNCTION APPS.XXX_CREATE_CCID
( P_CONCAT_SEGS IN VARCHAR2
) RETURN VARCHAR2
IS
L_STATUS BOOLEAN;
L_COA_ID NUMBER;
BEGIN
SELECT CHART_OF_ACCOUNTS_ID
INTO L_COA_ID
FROM GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 2021; –UPDATE THIS WITH SET OF BOOKS ID

L_STATUS := FND_FLEX_KEYVAL.VALIDATE_SEGS(
‘CREATE_COMBINATION’,
‘SQLGL’,
‘GL#’,
L_COA_ID,
P_CONCAT_SEGS,
‘V’,
SYSDATE,
‘ALL’, NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);
IF L_STATUS THEN
RETURN ‘S’;
ELSE
RETURN ‘F’;
END IF;
END ;
/

—–EXECUTE FUNCTION
DECLARE
RETVAL VARCHAR2(200);
P_CONCAT_SEGS VARCHAR2(200); /* ‘10.2001.2211101.987872.001.0000’ THIS COMBINATION I WANT TO CREATE */
BEGIN
RETVAL := APPS.XXX_CREATE_CCID ( P_CONCAT_SEGS );
COMMIT;
END;

Advertisements

Oppps Employee as Supplier in Oracle Payables R12. How Crazy….

How to enable creation of employee as supplier in oracle payable supplier entry form

How to Get Detail of Locks with Object Locked

SELECT VLO.OS_USER_NAME “OS USERNAME”, VLO.ORACLE_USERNAME “DB USER”,
VP.SPID “SPID”, AO.OWNER “OWNER”, AO.OBJECT_NAME “OBJECT LOCKED”,AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, ‘NO LOCK’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCL’,
6, ‘EXCLUSIVE’,
NULL
) “MODE OF LOCK”,
VS.STATUS “CURRENT STATUS”
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> ‘KILLED’
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;

Add Responsibility to your USER without having Application System Administrator Rights

By following procedure you can add responsibility to your USER with out having Application System Administrator Rights

begin
fnd_user_pkg.addresp(
‘&User_Name’, /*Application User Name */
‘&Responsablity_Application_Short_Name’, /*get from Query Below */
‘&Responsibility_Key’,/*get from Query Below */
‘&Security_Group’, /* Most of cases it is ‘STANDARD’ so you can hard code it */
‘&Description’, /* Any comments you want */
‘&Start_Date’, /* Sysdate From Today */
‘&End_Date’ ); /* Sysdate + 365 Rights for Next One Year*/
commit;

dbms_output.put_line(‘Responsibility Added Successfully’);
exception
when others then
dbms_output.put_line(‘ Responsibility is not added due to ‘ || SQLCODE || substr(SQLERRM, 1, 100));
Rollback;

end;

To get value for ‘Responsablity_Application_Short_Name’ and ‘Responsibility_Key’ Parameters you need to run following sql Statement by Using APPS User Name

SELECT FAV.APPLICATION_SHORT_NAME, FAV.APPLICATION_NAME,FRV.RESPONSIBILITY_KEY, FRV.RESPONSIBILITY_NAME

FROM FND_APPLICATION_VL FAV, FND_RESPONSIBILITY_VL FRV
WHERE FRV.APPLICATION_ID=FAV.APPLICATION_ID
ORDER BY FRV.RESPONSIBILITY_NAME

@Advanced Supply Chain Planner

EXAMPLE CODE 

To add Responsibility of @Advanced Supply Chain Planner to User Ahmad Bilal

BEGIN
fnd_user_pkg.addresp (‘AHMADBILAL’,
‘MSC’,
‘ADV_SUPPLY_CHAIN_PLANNING’,
‘STANDARD’,
‘Test By Script’,
SYSDATE,
SYSDATE + 100
);
COMMIT;
DBMS_OUTPUT.put_line (‘Responsibility Added Successfully’);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (   ‘ Responsibility is not added due to ‘
|| SQLCODE
|| SUBSTR (SQLERRM, 1, 100)
);
ROLLBACK;
END;

How to Restrict Access of Some Users or Program in Oracle Prodcution Database

CREATE OR REPLACE TRIGGER programe_restrication

AFTER LOGON ON DATABASE

BEGIN

FOR x IN (SELECT username, program

FROM SYS.v_$session

WHERE audsid = USERENV (‘sessionid’))

LOOP

IF LTRIM (RTRIM (x.username)) = ‘AHMADBILAL’

AND LTRIM (RTRIM (x.program)) IN (‘sqlplusw.exe’, ‘TOAD.exe’)

THEN

raise_application_error

(-20999,

‘Not authorized to use in the Production environment!’

);

END IF;

END LOOP;

END programe_restrication;

/

How to Kill Oracle Session

Get All Lock Objects by Using Following Query

SELECT a.object_id, a.session_id, b.object_name
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id

Get Session id From below Query by Passing session _id (get From Above query)

For example i got 67 from above query

SELECT SID, serial#,SID||’,’||serial# “session id”, username, command, schemaname, osuser, machine, terminal
FROM v$session
WHERE SID = 67;

I got 93,10383 from above query
Pass Session Id In below query

ALTER system kill session ‘93,10383’