Home/ AhmedRagab/Followers Answers
  1. Asked: June 18, 2016In:

    How to count specific values from table ?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    Hi, try this query : [code]SELECT SUM ( CASE WHEN Col1 = 2 THEN 1 ELSE 0 END + CASE WHEN Col2 = 2 THEN 1 ELSE 0 END + CASE WHEN Col3 = 2 THEN 1 ELSE 0 END) FROM table_name WHERE Col1 = 2 OR Col2 = 2 OR Col3 = 2;[/code]

    Hi,

    try this query :

    [code]SELECT SUM (
    CASE WHEN Col1 = 2 THEN 1 ELSE 0 END
    + CASE WHEN Col2 = 2 THEN 1 ELSE 0 END
    + CASE WHEN Col3 = 2 THEN 1 ELSE 0 END)
    FROM table_name
    WHERE Col1 = 2 OR Col2 = 2 OR Col3 = 2;[/code]

    See less
    • 0
  2. Asked: March 25, 2017In:

    How do i redirect user from page to another in JavaScript ?

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    Hi Saly, Actually you can achieve this by simulate HTTP redirect by using one of the following : // similar behavior as an HTTP redirect [code]window.location.replace("http://www.oraask.com");[/code] or // similar behavior as clicking on a link [code]window.location.href("http://www.oraask.com");[/cRead more

    Hi Saly,

    Actually you can achieve this by simulate HTTP redirect by using one of the following :

    // similar behavior as an HTTP redirect

    [code]window.location.replace(“http://www.oraask.com”);[/code]

    or

    // similar behavior as clicking on a link

    [code]window.location.href(“http://www.oraask.com”);[/code]

    at the end this your choice but for my suggestion i prefer using  (window.location.replace) because this doesn’t keep originate page in the session history.

    hope this help.

     

    See less
    • 0
  3. Asked: March 24, 2017In:

    How to add a new column to a table only if not exist?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    You can find the following view to access all metadata about the columns :user_tab_cols; -- For all tables owned by the userall_tab_cols ; -- For all tables accessible to the userdba_tab_cols; -- For all tables in the Database.and lets consider you want to add new column only if doesn't exists you cRead more

    You can find the following view to access all metadata about the columns :

    user_tab_cols; — For all tables owned by the user

    all_tab_cols ; — For all tables accessible to the user

    dba_tab_cols; — For all tables in the Database.

    and lets consider you want to add new column only if doesn’t exists you can use this pl/sql to check and add new column

    [code]DECLARE
    v_column_exists number := 0;
    BEGIN
    Select count(*)
    into v_column_exists
    from user_tab_cols
    where column_name = ‘ADD_COLUMN’
    and table_name = ‘departments’;

    if (v_column_exists = 0) then
    execute immediate ‘alter table departments add (ADD_COLUMN NUMBER)’;
    end if;
    end;[/code]

    hope this help.

    See less
    • 0
  4. Asked: April 3, 2017In:

    ORA-06550: line , column : PLS-00201: identifier must be declared

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    ORA-06550: error causes are:  You tried to execute an invalid block of PLSQL code (like a stored procedure or function), but a compilation error occurred. in your example, you selected a value inside the variable (v_last) that is not declared in your block. So to correct your block of code, you canRead more

    ORA-06550: error causes are:  You tried to execute an invalid block of PLSQL code (like a stored procedure or function), but a compilation error occurred.

    in your example, you selected a value inside the variable (v_last) that is not declared in your block.

    So to correct your block of code, you can rewrite it like this:

    DECLARE
    
V_LAST EMPLOYEES.LAST_NAME%TYPE;

    BEGIN

    SELECT LAST_NAME INTO V_LAST FROM EMPLOYEES;

dbms_output.put_line(‘v_last is :’ || V_LAST );

    END;
    See less
    • 0
  5. Asked: March 24, 2017In:

    API to create and update Inventory Items in Oracle Apps R12

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    Hello @Jone,   you can create or update inventory item by using API (ego_item_pub.process_item) and this an example :   Note : before using both API's be aware that we are not committing the changes. You have to perform explicit commit manually from IDE or set parameter (p_commit = 'T') to commit yoRead more

    Hello Jone,   you can create or update inventory item by using API (ego_item_pub.process_item) and this an example :  

    Note : before using both API’s be aware that we are not committing the changes. You have to perform explicit commit manually from IDE or set parameter (p_commit = ‘T’) to commit your changes.

    1. Create new inventory item using this API example:
    DECLARE
  l_inventory_item_id   NUMBER;
  l_organization_id     NUMBER;
  l_return_status       VARCHAR2 (4000);
  l_msg_data            VARCHAR2 (4000);
  l_msg_count           NUMBER;
  x_message_list        error_handler.error_tbl_type;
BEGIN
  fnd_global.apps_initialize (user_id => 1318, --> OPERATIONS
                              resp_id => 50583, --> Inventory, Vision Operations (USA)
                              resp_appl_id => 401); --> Inventory

  ego_item_pub.process_item (p_api_version                  => 1.0
                            ,p_init_msg_list                => 'T' -- (F:- False), (T:- True)
                            ,p_commit                       => 'F' -- (F:- False), (T:- True)
                            ,p_transaction_type             => 'CREATE' -- UPDATE FOR Updating item
                            ,p_segment1                     => 'Oraask_Item01' -- ITEM CODE
                            ,p_description                  => 'Oraask Test Item Description' -- ITEM DESCRIPTION
                            ,p_long_description             => 'Oraask Test Long Item Description' -- ITEM LONG DESCRIPTION
                            ,p_organization_id              => 204 -- Vision Operations
                            ,p_apply_template               => 'ALL'
                            ,p_template_id                  => 2 -- Purchased Item — select * from mtl_item_templates_vl
                            -- P_TEMPLATE_NAME => '@Purchased Item',
                            -- P_ITEM_TYPE => 'P',
                            ,p_inventory_item_status_code   => 'Active'
                            ,p_approval_status              => 'A'
                            ,x_inventory_item_id            => l_inventory_item_id
                            ,x_organization_id              => l_organization_id
                            ,x_return_status                => l_return_status
                            ,x_msg_count                    => l_msg_count
                            ,x_msg_data                     => l_msg_data);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    dbms_output.put_line ('Item is Created Successfully, Inventory Item ID : ' || l_inventory_item_id);

  ELSE
    dbms_output.put_line ('Item Creation is Failed');
    error_handler.get_message_list (x_message_list => x_message_list);

    FOR i IN 1 .. x_message_list.count
    LOOP
      dbms_output.put_line (x_message_list (i).message_text);
    END LOOP;

    ROLLBACK;
  END IF;
--> EXCEPTIONS HANDLING PART
EXCEPTION
  WHEN OTHERS THEN
    FOR i IN 1 .. l_msg_count
    LOOP
      dbms_output.put_line (substr (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255));
      dbms_output.put_line ('message is: ' || l_msg_data);
    END LOOP;
END;

     

    • Update existing inventory item using this API example:
    DECLARE
  l_inventory_item_id   NUMBER;
  l_organization_id     NUMBER;
  l_return_status       VARCHAR2 (4000);
  l_msg_data            VARCHAR2 (4000);
  l_msg_count           NUMBER;
  x_message_list        error_handler.error_tbl_type;
BEGIN
  fnd_global.apps_initialize (user_id => 1318, --> OPERATIONS
                              resp_id => 50583, --> Inventory, Vision Operations (USA)
                              resp_appl_id => 401); --> Inventory
 
  ego_item_pub.process_item (p_api_version                  => 1.0
                            ,p_init_msg_list                => 'T' -- (F:- False), (T:- True)
                            ,p_commit                       => 'F' -- (F:- False), (T:- True)
                            ,p_transaction_type             => 'UPDATE' -- UPDATE FOR Updating item
                            ,p_Inventory_Item_Id            => 53899
                            ,p_description                  => 'Oraask test description' -- ITEM DESCRIPTION
                            ,p_organization_id              => 204 -- Vision Operations
                            ,x_inventory_item_id            => l_inventory_item_id
                            ,x_organization_id              => l_organization_id
                            ,x_return_status                => l_return_status
                            ,x_msg_count                    => l_msg_count
                            ,x_msg_data                     => l_msg_data);
 
  IF l_return_status = fnd_api.g_ret_sts_success THEN
    dbms_output.put_line ('Inventory Item has been updated Successfully, Inventory Item ID : ' || l_inventory_item_id);

  ELSE
    dbms_output.put_line ('Update inventory item is Failed');
    error_handler.get_message_list (x_message_list => x_message_list);
 
    FOR i IN 1 .. x_message_list.count
    LOOP
      dbms_output.put_line (x_message_list (i).message_text);
    END LOOP;
 
    ROLLBACK;
  END IF;
--> EXCEPTIONS HANDLING PART
EXCEPTION
  WHEN OTHERS THEN
    FOR i IN 1 .. l_msg_count
    LOOP
      dbms_output.put_line (substr (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255));
      dbms_output.put_line ('message is: ' || l_msg_data);
    END LOOP;
END;

    — By executing above code we have updated item description to be “Oraask test description” for inventory item id “53899”

    Hopefully, this code snippet helps you 🙂

    See less
    • 7
  6. Asked: April 30, 2017In:

    What is FNDLOAD command to move Value Sets from one instance to another ?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    Hello Matheo, You can use this command to download and upload value sets : FNDLOAD to Download Valueset: $FND_TOP/bin/FNDLOAD username/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name" FNDLOAD to Upload Valueset: $FND_TOP/Read more

    Hello Matheo,

    You can use this command to download and upload value sets :

    FNDLOAD to Download Valueset:

    $FND_TOP/bin/FNDLOAD username/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

    FNDLOAD to Upload Valueset:

    $FND_TOP/bin/FNDLOAD username/password 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
    See less
    • 0
  7. Asked: April 8, 2017In:

    Repeat same previous record or item in oracle forms ?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    You can use DUPLICATE_RECORD built-in

    You can use DUPLICATE_RECORD built-in

    See less
    • 0
  8. Asked: April 8, 2017In:

    How to show a message in a report at runtime?

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    hi Albert, You can display a message in Oracle report builder by using the standard built-in srw.message. The basic syntax for using this package is: srw.message(error_no,'YOUR MESSAGE'); the error no will appear along with the message text. and here is the sample to do this in a particular functionRead more

    hi Albert,

    You can display a message in Oracle report builder by using the standard built-in srw.message.

    The basic syntax for using this package is:

    srw.message(error_no,'YOUR MESSAGE');

    the error no will appear along with the message text. and here is the sample to do this in a particular function

    FUNCTION chck_sal RETURN BOOLEAN IS
BEGIN
  IF :sal < 0 THEN SRW.MESSAGE(
    100,
    'There is employee that take negative salary.'
  );
RAISE SRW.PROGRAM_ABORT;
ELSE :bonus := :sal * .01;
END IF;
RETURN(TRUE);
END;
    See less
    • 1
  9. Asked: March 26, 2017In:

    How to add “Auto Refresh” feature in EBS submit request form like version 12.2.6?

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    Hello Beter,here you will find how to implement this functionality inside 12.1.3 + versions:Click Here : Implement Auto Refresh functionality in EBS 12.1.3+

    Hello Beter,

    here you will find how to implement this functionality inside 12.1.3 + versions:

    Click Here : Implement Auto Refresh functionality in EBS 12.1.3+

    See less
    • 0
  10. Asked: March 18, 2017In:

    Oracle R12 EBS not working on Firefox 52

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    hello albert,the problem with mozilla firefox new version 52, there is another version working good called mozilla firefox ESR.Mozilla Firefox 52 Extended Support Release (ESR) is certified as a Windows-based client browser for Oracle E-Business Suite 12.1 and 12.2.you can download your version fromRead more

    hello albert,

    the problem with mozilla firefox new version 52, there is another version working good called mozilla firefox ESR.

    Mozilla Firefox 52 Extended Support Release (ESR) is certified as a Windows-based client browser for Oracle E-Business Suite 12.1 and 12.2.

    you can download your version from this link :

    Download Now

    Note : you should download 86 bit version NOT x64.

     

    hope this will help.

    See less
    • 0