Script to load data for Menu Exclusions while creation responsiblities

create or replace PROCEDURE CLC_RESP_MENU_EXCLU_PRG
(
errbuf                      OUT VARCHAR2
,retcode                   OUT NUMBER
)
as

l_application_id    fnd_responsibility_tl.application_id%type;
l_responsibility_id    fnd_responsibility_tl.responsibility_id%type;
l_action_id        FND_RESP_FUNCTIONS.ACTION_ID%type;
l_rule_type        FND_RESP_FUNCTIONS.rule_type%type;
l_rowid            varchar2(2000);
l_cnt            number;
l_conc_request_id    fnd_concurrent_requests.REQUEST_ID%type;
l_err_msg        varchar2(2000);   

CURSOR c_menu
IS
SELECT distinct RESPONSIBILITY_NAME,
EXCLU_TYPE,
EXCLU_NAME,
EXCLU_DESC
FROM CLC_RESP_MENU_EXCLU_LOAD
where PROCESS_FLAG='P';


Begin

FND_FILE.PUT_LINE(FND_FILE.LOG,'  Starting of CLC_RESP_MENU_EXCLU_PRG...');
--FND_FILE.PUT_LINE(FND_FILE.LOG,'  Initializing Apps');
--FND_GLOBAL.Apps_Initialize(FND_GLOBAL.USER_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID);

select fnd_global.conc_request_id into l_conc_request_id from dual;

for c_rec in c_menu loop
FND_FILE.PUT_LINE(FND_FILE.LOG,'Loop Start');

l_application_id    := null;   
l_responsibility_id    := null;   
l_action_id        := null;   
l_rule_type        := null;   
l_rowid            := null;   
l_cnt            := 0;
l_err_msg        := null;

select application_id,responsibility_id 
into l_application_id,l_responsibility_id
from fnd_RESPONSIBILITY_tl
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and LANGUAGE =USERENV('LANG');

FND_FILE.PUT_LINE(FND_FILE.LOG,'application_id:' || l_application_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'responsibility_id:' || l_responsibility_id);

if c_rec.EXCLU_TYPE ='Function' then
l_rule_type :='F';
begin
SELECT FUNCTION_ID
into l_action_id
FROM FND_FORM_FUNCTIONS_VL  
WHERE USER_FUNCTION_NAME=c_rec.EXCLU_NAME;
exception
when too_many_rows then
l_err_msg :='too_many_rows in FND_FORM_FUNCTIONS_VL';
l_action_id :=null;
when others then
l_err_msg :='No_Data_Found in FND_FORM_FUNCTIONS_VL';
l_action_id :=null;
end;


elsif c_rec.EXCLU_TYPE ='Menu' then
l_rule_type :='M';
begin
select MENU_ID
into l_action_id
from FND_MENUS_VL
where USER_MENU_NAME=c_rec.EXCLU_NAME;
exception
when too_many_rows then
l_err_msg :='too_many_rows exist in FND_MENUS_VL';
l_action_id :=null;
when others then
l_err_msg :='No_Data_Found in FND_MENUS_VL';
l_action_id :=null;
end;

end if;

FND_FILE.PUT_LINE(FND_FILE.LOG,'action_id:' || l_action_id);
begin
select count(*)
into l_cnt
from FND_RESP_FUNCTIONS
where RESPONSIBILITY_ID=l_responsibility_id
and APPLICATION_ID =l_application_id
and RULE_TYPE =l_rule_type
and ACTION_ID =l_action_id;

exception when others then
l_cnt :=0;
end;

FND_FILE.PUT_LINE(FND_FILE.LOG,'l_cnt:' || l_cnt);


if l_application_id is not null and l_responsibility_id is not null and l_rule_type is not null and  l_action_id is not null and l_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'calling FND_RESP_FUNCTIONS_PKG.INSERT_ROW' );
APPS.FND_RESP_FUNCTIONS_PKG.INSERT_ROW
(l_rowid            --X_ROWID in out nocopy VARCHAR2,
 ,l_application_id    --X_APPLICATION_ID in NUMBER,
 ,l_responsibility_id    --X_RESPONSIBILITY_ID in NUMBER,
 ,l_action_id        --X_ACTION_ID in NUMBER,
 ,l_rule_type        --X_RULE_TYPE in VARCHAR2,
 ,FND_GLOBAL.USER_ID    --X_CREATED_BY in NUMBER,
 ,sysdate        --X_CREATION_DATE in DATE,
 ,FND_GLOBAL.USER_ID    --X_LAST_UPDATED_BY in NUMBER,
 ,sysdate        --X_LAST_UPDATE_DATE in DATE,
 ,FND_GLOBAL.USER_ID    --X_LAST_UPDATE_LOGIN in NUMBER)
);
end if;


if l_rowid is null and l_cnt >= 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update1' );
update CLC_RESP_MENU_EXCLU_LOAD
set PROCESS_FLAG='E',
ERROR_MESSAGE ='Data_Already_Exists',
REQUEST_ID=l_conc_request_id
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and EXCLU_NAME =c_rec.EXCLU_NAME;

elsif l_rowid is null and l_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update2' );
update CLC_RESP_MENU_EXCLU_LOAD
set PROCESS_FLAG='E',
ERROR_MESSAGE =l_err_msg,
REQUEST_ID=l_conc_request_id
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and EXCLU_NAME =c_rec.EXCLU_NAME;

elsif l_rowid is not null and l_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update3' );
update CLC_RESP_MENU_EXCLU_LOAD
set PROCESS_FLAG='S',
REQUEST_ID=l_conc_request_id
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and EXCLU_NAME =c_rec.EXCLU_NAME;
end if;
commit;
FND_FILE.PUT_LINE(FND_FILE.LOG,'End loop' );

end loop;
FND_FILE.PUT_LINE(FND_FILE.LOG,'End Program' );

EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,' OTHER exception ');
end CLC_RESP_MENU_EXCLU_PRG;
/

Item Cost query ( mtl_system_items_b,cst_item_costs,gl_code_combinations_kfv)

select msi.segment1 "ITEM_NUM",
msi.description "ITEM_DESCRIPTION",
msi.PRIMARY_UNIT_OF_MEASURE "UOM",
cct.cost_type "COST TYPE",
cic.DEFAULTED_FLAG "USE DEFAULT CONTROLS",
msi.INVENTORY_ASSET_FLAG "INVENTORY ASSET",
cic.BASED_ON_ROLLUP_FLAG "BASED ON ROLLUP",
cic.LOT_SIZE,
cic.shrinkage_rate "MANUFACTURING SHRINKAGE",
cic.item_cost "UNIT COST",
cic.MATERIAL_COST "MATERIAL",
cic.MATERIAL_OVERHEAD_COST "MATERIAL_OVERHEAD",
cic.RESOURCE_COST "RESOURCE",
cic.OUTSIDE_PROCESSING_COST "OUTSIDE_PROCESSING",
cic.OVERHEAD_cost "OVERHEAD",
gcc.CONCATENATED_SEGMENTS "COGS_ACCOUNT",
gcc1.CONCATENATED_SEGMENTS "SALES_ACCOUNT",
flv.MEANING "MAKE/BUY",
msi.DEFAULT_INCLUDE_IN_ROLLUP_flag "INCLUDE IN ROLLUP",
(SELECT  mic.segment1
                   || '.'
                   || mic.segment2
                   || '.'
                   || mic.segment3
                   || '.'
                   || mic.segment4
                   || '.'
                   || mic.segment5
                   || '.'
                   || mic.segment6
            FROM   MTL_ITEM_CATEGORIES_V mic, mtl_category_sets mcs
           WHERE       mic.CATEGORY_SET_ID = mcs.CATEGORY_SET_ID
                   AND mcs.CATEGORY_SET_NAME = 'ENVS CST Category Set'
                   AND mic.inventory_item_id = msi.inventory_item_id
                   AND mic.organization_id = msi.organization_id)
            "COST CATEGORY"
from mtl_system_items_b msi,
cst_cost_types cct,
cst_item_costs cic,
org_organization_definitions ood,
gl_code_combinations_kfv gcc,
gl_code_combinations_kfv gcc1,
FND_LOOKUP_VALUES flv
WHERE 1=1
AND cct.cost_type_id = cic.cost_type_id
AND cic.inventory_item_id = msi.inventory_item_id
AND cic.organization_id = msi.organization_id
AND msi.organization_id = ood.organization_id
AND gcc.CHART_OF_ACCOUNTS_ID = ood.CHART_OF_ACCOUNTS_ID
AND gcc.CODE_COMBINATION_ID =msi.COST_OF_SALES_ACCOUNT
AND gcc1.CHART_OF_ACCOUNTS_ID = ood.CHART_OF_ACCOUNTS_ID
AND gcc1.CODE_COMBINATION_ID =msi.expense_account
AND flv.lookup_type = 'MTL_PLANNING_MAKE_BUY'
and flv.LOOKUP_CODE=msi.PLANNING_MAKE_BUY_CODE
AND flv.LANGUAGE='US'
AND ood.organization_code = 'E31'
--and msi.segment1='02980050'
--and msi.inventory_item_id=1117374

Location query (HR_LOCATIONS)

SELECT DECODE (NVL (BUSINESS_GROUP_ID, 1), 1, 'Global','Local') AS "SCOPE",
LOCATION_CODE "Name",
DESCRIPTION "Description",
INACTIVE_DATE "Inactive Date",
LEGAL_ADDRESS_FLAG "Legal Address",
style "Address Style",
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
TOWN_OR_CITY "CITY",
REGION_1 "Province",
REGION_2 "Census Metropolitan Areac",
POSTAL_CODE,
COUNTRY,
TELEPHONE_NUMBER_1 "Telephone",
TELEPHONE_NUMBER_2 "Fax",
TIMEZONE_CODE "Time Zone",
DESIGNATED_RECEIVER_ID "Contact",
LOCATION_CODE "Ship-To Location",
SHIP_TO_SITE_FLAG "Ship-To Site",
RECEIVING_SITE_FLAG "Receiving Site",
OFFICE_SITE_FLAG "Office Site",
BILL_TO_SITE_FLAG "Bill-To Site",
IN_ORGANIZATION_FLAG "Internal Site",
INVENTORY_ORGANIZATION_ID "Inventory Organization",
ECE_TP_LOCATION_CODE "EDI Location",
TAX_NAME "Tax Code"
FROM HR_LOCATIONS

link between oe_order_headers_all and wip_entities

SELECT   ooh.order_number,
         msib.segment1,
         ool.line_id,
         mr.reservation_quantity,
         we.wip_entity_name wip_job_name,
         wdj.scheduled_start_date wip_start_date,
         wdj.scheduled_completion_date wip_completion_date,
         wdj.attribute10 job_type
  FROM   oe_order_headers_all ooh,
         oe_order_lines_all ool,
         mtl_reservations mr,
         wip_discrete_jobs wdj,
         wip_entities we,
         mtl_system_items_b msib
 WHERE       ooh.header_id = ool.header_id
         AND ooh.order_number ='10100563'
         AND mr.demand_source_line_id = ool.line_id
         AND mr.supply_source_type_id = 5
         AND mr.supply_source_header_id = we.wip_entity_id
         AND we.wip_entity_id = wdj.wip_entity_id
         AND ool.ship_from_org_id = we.organization_id
         AND ool.ship_from_org_id = msib.organization_id
         AND mr.inventory_item_id = msib.inventory_item_id
         AND we.organization_id = wdj.organization_id
         AND wdj.organization_id = mr.organization_id

Script to update Items in oracle apps R12 by using EGO_ITEM_PUB.Process_Items

Declare

l_mast_organization_id number;
l_error_message varchar2(3000);
l_error_code varchar2(240):='S';
l_inventory_item_id number;
l_user_id number;
l_resp_id number;
l_resp_appl_id number;
l_jde_count number:=0;
  l_item_table       EGO_Item_PUB.Item_Tbl_Type;
  x_item_table      EGO_Item_PUB.Item_Tbl_Type;
  x_return_status  VARCHAR2(1);
  x_msg_count     NUMBER(10);
  x_msg_data       VARCHAR2(1000);
  x_message_list   Error_Handler.Error_Tbl_Type;



  BEGIN

    
           fnd_global.apps_initialize
                            (user_id      => 10688,--l_user_id,
                             resp_id      => 50361,--l_resp_id,
                             resp_appl_id => 401);--l_resp_appl_id);


               IF  l_error_code <>'E' THEN
                  -- Item definition
                  l_item_table(1).Transaction_Type := 'UPDATE';
                  l_item_table(1).inventory_item_id := 12194;--ITEM_REC.inventory_item_id;
                  l_item_table(1).Organization_id := 143;--l_mast_organization_id;
                  l_item_table(1).RELEASE_TIME_FENCE_CODE :=null;
                  l_item_table(1).RELEASE_TIME_FENCE_DAYS :=null;

                  -- Calling procedure EGO_ITEM_PUB.Process_Items
                  EGO_ITEM_PUB.Process_Items(
                                            --Input Parameters
                                             p_api_version   => 1.0,
                                             p_init_msg_list => FND_API.g_TRUE,
                                             p_commit        => FND_API.g_TRUE,
                                             p_Item_Tbl      => l_item_table,
                                             --Output Parameters
                                             x_Item_Tbl      => x_item_table,
                                             x_return_status => x_return_status,
                                             x_msg_count     => x_msg_count);

                  --DBMS_OUTPUT.PUT_LINE('Items updated Status ==>' || x_return_status);
                  --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Items updated Status ==>' || x_return_status);
          dbms_output.put_line ('Items updated Status ==>' || x_return_status);

                      IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN

                        FOR i IN 1 .. x_item_table.COUNT LOOP

                          --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inventory Item Id :' ||to_char(x_item_table(i).Inventory_Item_Id));
                          --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Organization Id   :' ||to_char(x_item_table(i).Organization_Id));
              dbms_output.put_line ('Inventory Item Id :' ||to_char(x_item_table(i).Inventory_Item_Id));
              dbms_output.put_line ( 'Organization Id   :' ||to_char(x_item_table(i).Organization_Id));


                        END LOOP;

                      ELSE

                        --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Messages :');
            dbms_output.put_line ( 'Error Messages :');
                        Error_Handler.GET_MESSAGE_LIST(x_message_list => x_message_list);

                        FOR i IN 1 .. x_message_list.COUNT LOOP

                          --FND_FILE.PUT_LINE(FND_FILE.LOG, x_message_list(i).message_text);
            dbms_output.put_line ( ' x_message_list(i).message_text-'|| x_message_list(i).message_text);
                        END LOOP;

                      END IF;
               ELSE
               --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error Messages :'||l_error_message);
           dbms_output.put_line (  'Error Messages :'||l_error_message);
               END IF;

END;

How to set language in oracle apps R12?

language =USERENV('LANG');

Lock query (query to find the lock and kill session)

Lock query:
-----------------

SELECT   vs.inst_id,vs.audsid audsid, locks.SID SID, vs.serial# serial#,
         vs.username oracle_user, vs.osuser os_user, vs.program program,
         vs.module module, vs.action action, vs.process process,
         DECODE (locks.lmode,
                 1, NULL,
                 2, 'Row Share',
                 3, 'Row Exclusive',
                 4, 'Share',
                 5, 'Share Row Exclusive',
                 6, 'Exclusive',
                 'None'
                ) lock_mode_held,
         DECODE (locks.request,
                 1, NULL,
                 2, 'Row Share',
                 3, 'Row Exclusive',
                 4, 'Share',
                 5, 'Share Row Exclusive',
                 6, 'Exclusive',
                 'None'
                ) lock_mode_requested,
         DECODE (locks.TYPE,
                 'MR', 'Media Recovery',
                 'RT', 'Redo Thread',
                 'UN', 'User Name',
                 'TX', 'Transaction',
                 'TM', 'DML',
                 'UL', 'PL/SQL User Lock',
                 'DX', 'Distributed Xaction',
                 'CF', 'Control File',
                 'IS', 'Instance State',
                 'FS', 'File Set',
                 'IR', 'Instance Recovery',
                 'ST', 'Disk Space Transaction',
                 'TS', 'Temp Segment',
                 'IV', 'Library Cache Invalidation',
                 'LS', 'Log Start or Log Switch',
                 'RW', 'Row Wait',
                 'SQ', 'Sequence Number',
                 'TE', 'Extend Table',
                 'TT', 'Temp Table',
                 locks.TYPE
                ) lock_type,
         objs.owner object_owner, objs.object_name object_name,
         objs.object_type object_type,
         ROUND (locks.ctime / 60, 2) lock_time_in_minutes
    FROM gv$session vs, v$lock locks, dba_objects objs, dba_tables tbls
   WHERE locks.id1 = objs.object_id
     AND vs.SID = locks.SID
     AND objs.owner = tbls.owner
     AND objs.object_name = tbls.table_name
     AND objs.owner != 'SYS'
     AND locks.TYPE = 'TM'
ORDER BY lock_time_in_minutes;


Kill Session:
-------------------
alter system kill session 'SID,SERIAL#,@INST_ID';

alter system kill session '2328,33131,@1';