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