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';

How to submit concurrent program from command prompt /shell

We can submit concurrent programs from OS command / shell prompt using CONCSUB utility. Syntax:

CONCSUB <APPS username>/<APPS password>
<responsibility application short name>
<responsibility name>
<username>
[WAIT=N|Y|<n seconds>]
CONCURRENT
<program application short name>
<program name>
[PROGRAM_NAME=”<description>”]
[REPEAT_TIME=<resubmission time>]
[REPEAT_INTERVAL= <number>]
[REPEAT_INTERVAL_UNIT=< resubmission unit>]
[REPEAT_INTERVAL_TYPE=< resubmission type>]
[REPEAT_END=<resubmission end date and time>]
[NLS_LANGUAGE=<language of the request>]
[NLS_TERRITORY=<territory of the request>]
[START=<date>]
[IMPLICIT=< type of concurrent request>
[<parameter 1> … <parameter n>]

Examples:

Active Users
CONCSUB apps/[apps_pwd] SYSADMIN “System Administrator” SYSADMIN WAIT=N CONCURRENT FND FNDSCURS

Prints Environment Variables
CONCSUB apps/[apps_pwd] SYSADMIN “System Administrator” SYSADMIN WAIT=N CONCURRENT FND FNDPRNEV APPL_TOP