Bounce Apache Server in Oracle R12

Below step by step to restart apache server :

1. Login to applmgr using putty or others tools

2. Go to directory $INST_TOP/admin/scripts, like image below

3. Stop apache service using command :

     $ sh adapcctl.sh stop

4. Output that script will be appear like image below 

5. Start apache service using command :

     $ sh adapcctl.sh start

6. Output that script will be appear like image below

7. Your apache service has been restarted

Change Site Name and Applet Colour Oracle EBS

In the project, sometimes you need to have differences about Production instance and Non-Production Instance. Oracle EBS can make that differences on colour and site name.

Change Applet Colour EBS 
1. Go to system profile form.
    Navigation : System Administrator  ->  Profile  ->  System
2. In the field "Profile", please insert value "Java Color Scheme", then click find button. Form will be appeared like image below.

3. Please choose appropriate colour under Site Column

4. Save 

Change Site Name EBS 

1. Go to system profile form.

    Navigation : System Administrator  ->  Profile  ->  System
2. In the field "Profile", please insert value "Site Name", then click find button. Form will be appeared like image below.

3. Please put your new name under Site Column
4. Save

Create Activity Suppression Oracle EAM (sample calling API)

DECLARE  

CURSOR stage IS  

    SELECT *    

    FROM staging_suppresion_table

    where flag is null;  

   

l_stat varchar2(100);    

l_count number;    

l_data varchar2(100);    

v_stat varchar2(1) := 'S';    

v_parent_association_id  number;

v_child_association_id  number;

BEGIN    

    FOR i IN stage LOOP    

       

       select activity_association_id

       into v_parent_association_id

       from mtl_eam_asset_activities

       where asset_activity_id=i.parent_activity_id;

       

       select activity_association_id

       into v_child_association_id

       from mtl_eam_asset_activities

       where asset_activity_id=i.child_activity_id;

        EAM_ActivitySupn_PUB.INSERT_ACTIVITYSUPN    

        (    

            p_api_version               => 1.0    

            , x_return_status           => l_stat    

            , x_msg_count               => l_count    

            , x_msg_data                => l_data    

            , p_parent_association_id   => v_parent_association_id    

            , p_child_association_id    => v_child_association_id 

            , p_tmpl_flag               => 'N'    

        );    

        update staging_suppresion_table

        set flag=l_stat,

            note=substr(l_data,5,length(l_data))

        where parent_activity_id=i.parent_activity_id

              and child_activity_id=i.child_activity_id;  

              

       commit;

       

           

    END LOOP;    

        

        

    IF v_stat <> 'E' THEN    

       COMMIT;    

    END IF;    

END;

Create Meter Association Oracle EAM (sample calling API)

DECLARE

   CURSOR stage

   IS

      SELECT *

        FROM staging_meter_assoc_table

       WHERE flag IS NULL;

   l_stat    VARCHAR2 (10);

   l_count   NUMBER;

   l_data    VARCHAR2 (100);

   v_stat    VARCHAR2 (1)   := 'S';

BEGIN

   FOR i IN stage

   LOOP

      eam_meterassoc_pub.insert_assetmeterassoc

                                     (p_api_version          => 1.0,

                                      x_return_status        => l_stat,

                                      x_msg_count            => l_count,

                                      x_msg_data             => l_data,

                                      p_meter_id             => i.meter_id,

                                      p_organization_id      => i.organization_id,

                                      p_asset_group_id       => i.asset_group_id,

                                      p_asset_number         => i.asset_number

                                     );

      --DBMS_OUTPUT.put_line (l_stat || ' - ' || i.meter_id);

      UPDATE staging_meter_assoc_table

         SET flag = l_stat

       WHERE meter_id = i.meter_id;

      COMMIT;

      IF l_stat <> 'S'

      THEN

         v_stat := 'E';

         ROLLBACK;

         EXIT;

      END IF;

   END LOOP;

   IF v_stat <> 'E'

   THEN

      COMMIT;

   END IF;

END;

Create Meter Oracle EAM (Sample Calling API)

DECLARE

   CURSOR stage

   IS

   

      SELECT *

        FROM staging_meter_table

       WHERE flag IS NULL ;

   l_stat       VARCHAR2 (10);

   l_count      NUMBER;

   l_data       VARCHAR2 (100);

   l_meter_id   NUMBER;

   v_stat       VARCHAR2 (1)   := 'S';

BEGIN

   FOR i IN stage

   LOOP

      eam_meter_pub.create_meter

                               (p_api_version               => 1.0,

                                x_return_status             => l_stat,

                                x_msg_count                 => l_count,

                                x_msg_data                  => l_data,

                                p_meter_name                => i.meter_name,

                                p_meter_uom                 => i.meter_uom,

                                p_meter_type                => i.meter_type,

                                p_value_change_dir          => i.value_change_dir,

                                p_eam_required_flag         => i.eam_required_flag,

                                p_used_in_scheduling        => i.used_in_scheduling,

                                p_user_defined_rate         => i.user_defined_rate,

                                p_use_past_reading          => i.user_past_reading,

                                p_initial_reading           => i.initial_reading,

                                p_description               => i.description,

                                p_from_effective_date       => i.from_effective_date,

                                p_initial_reading_date      => i.initial_reading_date,

                                p_tmpl_flag                 => i.tmpl_flag,

                                x_new_meter_id              => l_meter_id

                               );

      UPDATE staging_meter_table

         SET flag = l_stat,

             data = substr(l_data,1,50)

       WHERE meter_name = i.meter_name;

      COMMIT;

   END LOOP;

   IF v_stat <> 'E'

   THEN

      COMMIT;

   END IF;

END;

Create Preventive Maintenance Schedule Oracle EAM (sample calling API)

DECLARE

   CURSOR stage

   IS

      SELECT *

        FROM staging_pm_schedule_table

       WHERE flag IS NULL;

   l_stat          VARCHAR2 (10);

   l_count         NUMBER;

   l_data          VARCHAR2 (100);

   l_schedule      eam_pmdef_pub.pm_scheduling_rec_type;

   l_activity      eam_pmdef_pub.pm_activities_grp_tbl_type;

   l_day           eam_pmdef_pub.pm_rule_tbl_type;

   l_runtime       eam_pmdef_pub.pm_rule_tbl_type;

   l_list          eam_pmdef_pub.pm_rule_tbl_type;

   l_id            NUMBER;

   l_sch_id        NUMBER;

   v_active_assc   NUMBER;

   v_obj_id        NUMBER;

   v_obj_type      NUMBER;

   v_stat          VARCHAR2 (1)                             := 'S';

   v_tmpl_flag     VARCHAR2 (30);

BEGIN

   FOR i IN stage

   LOOP

      SELECT activity_association_id, maintenance_object_id,

             maintenance_object_type, template_flag

        INTO v_active_assc, v_obj_id,

             v_obj_type, v_tmpl_flag

        FROM mtl_eam_asset_activities_v

       WHERE activity_association_id = i.activity_association_id;

      SELECT eam_pm_schedulings_s.NEXTVAL

        INTO l_sch_id

        FROM DUAL;

      l_schedule.pm_schedule_id := l_sch_id;

      l_schedule.NAME := i.NAME;

      l_schedule.activity_association_id := v_active_assc;

      l_schedule.set_name_id := i.set_name_id;

      l_schedule.maintenance_object_id := v_obj_id;

      l_schedule.maintenance_object_type := v_obj_type;

      l_schedule.from_effective_date := i.from_effective_date;

      l_schedule.generate_wo_status := i.generate_wo_status;

      l_schedule.current_cycle := 1;

      l_schedule.current_seq := 0;

      l_schedule.interval_per_cycle := i.interval_per_cycle;

      l_schedule.generate_next_work_order := i.generate_next_work_order;

      l_schedule.non_scheduled_flag := i.non_scheduled_flag;

      l_schedule.rescheduling_point := i.rescheduling_point;

      l_schedule.default_implement := i.default_implement;

      l_schedule.whichever_first := i.whichever_first;

      l_schedule.include_manual := i.include_manual;

      l_schedule.scheduling_method_code := i.scheduling_method_code;

      l_schedule.type_code := i.type_code;

      l_schedule.auto_instantiation_flag := i.auto_instantiation_flag;

      l_schedule.tmpl_flag := v_tmpl_flag;

      l_schedule.attribute1 := i.description;

      l_activity (1).activity_association_id := v_active_assc;

      l_activity (1).interval_multiple := i.interval_multiple;

      l_activity (1).allow_repeat_in_cycle := i.allow_repeat_in_cycle;

      IF i.meter_id IS NULL

      THEN

         l_day (1).rule_type := i.rule_type;

         l_day (1).day_interval := i.day_interval;

      ELSE

         l_runtime (1).rule_type := i.rule_type;

         l_runtime (1).meter_id := i.meter_id;

         l_runtime (1).runtime_interval := i.runtime_interval;

      END IF;

      eam_pmdef_pub.create_pm_def (p_api_version                    => 1.0,

                                   p_init_msg_list                  => NULL,

                                   p_commit                         => 'T',

                                   p_validation_level               => NULL,

                                   x_return_status                  => l_stat,

                                   x_msg_count                      => l_count,

                                   x_msg_data                       => l_data,

                                   p_pm_schedule_rec                => l_schedule,

                                   p_pm_activities_tbl              => l_activity,

                                   p_pm_day_interval_rules_tbl      => l_day,

                                   p_pm_runtime_rules_tbl           => l_runtime,

                                   p_pm_list_date_rules_tbl         => l_list,

                                   x_new_pm_schedule_id             => l_id

                                  );

      UPDATE staging_pm_schedule_table

         SET flag = l_stat,

             note = SUBSTR (l_data, 5, LENGTH (l_data))

       WHERE activity_association_id = i.activity_association_id AND ID = i.ID;

      COMMIT;

      IF l_stat <> 'S'

      THEN

         v_stat := 'E';

         ROLLBACK;

         EXIT;

      END IF;

   END LOOP;

   IF v_stat <> 'E'

   THEN

      COMMIT;

   END IF;

END;

Purchasing Requisition Workflow Mailer Notification Not Sending Email

Symptoms
PO Requisitions and other notification emails are not being sent. The Test Mailer Framework Type email is sent.

Cause

When "Attach images to outbound emails" is enabled the Workflow Mailer will embed any referenced image URL into the email message.  PO Requisition (PO_REQ_APPROVE_JRAD) message includes an image that will be included in the email message.  The complete URL pointing to this images is not is not being populated by the Workflow Mailer code.  The code should retrieve http://<host.domain:port>/OA_MEDIA/ag_transparentpixel.gif.  It is actually retrieving http://. This is not a valid URL.

Solution :

- Workaround :

   1. Disable functionality "Attach Images to Outbound Emails"

       Navigator : System Administrator  ->  Oracle Application Manager  ->  Workflow  ->  Notification Mailer  ->  Edit  ->  Advance  -> Details  ->  EMail Servers  ->  Message Generation.

Uncheck the field "Attach Images to Outbound Emails" like image below :

   

2. Restart workflow mailer service   

    Navigator : System Administrator  ->  Oracle Application Manager  ->  Workflow  ->  Notification Mailer.

Select workflow mailer service and click Stop then wait for state=Deactivated. After that click start.

- Permanent Solution :

1. Backup your system (application and database)

2. Apply patch number below 9868639

3. Retest the issue

Source : Metalink, doc id 1116718.1

Function Not Available To This Responsibility - Custom Form

Symptoms :
  When user open the custom form in oracle application, error "Function Not Available To This Responsibility" appears.

Cause :
  Path of $CUSTOM_TOP has not been registered in the default.env file.

Solutions :
1. Login to server with APPLMGR manager user access.
2. Go to $INST_TOP/ora/10.1.2/forms/server directory.
3. Ensure that your CUSTOM_TOP's are registered in the default.env file. This file present  under location $INST_TOP/ora/10.1.2/forms/server directory. 
4. If has not been registered, you should create an entry for environment variable CUSTOM_TOP (which contains physical path to your custom directory). 
For example: 
...
ZX_TOP=/u01/PROD/apps/apps_st/appl/zx/12.0.0
XX_TOP=/u01/PROD/apps/apps_st/appl/xx/12.0.0
5. Save that file and re-run your custom form

Data Manipulation Language

A data manipulation language (DML) is a family of computer languages including commands permitting users to manipulate data in a database. This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data. DML is mostly incorporated in SQL databases.
Data manipulation languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are:

1. SELECT ... FROM .. WHERE
     SELECT is used to retrieve data from one or more table and can combine with union statement or subquery.  Below basic syntax from select statement :
 SELECT
    [ALL | DISTINCT ]

    select_expr [, select_expr ...]

    [FROM table_references

    [WHERE where_condition]

    [GROUP BY {col_name | expr | position}]

    [HAVING where_condition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC], ...]

Notes :

• The select_expr indicates a column that you want to retrieve. There must be at least one select_exp 
• The table_references indicates the table or tables from which to retrieve rows.
• The where clause, if given, indicates the condition or conditions that rows must satisfy to be selected.
• The having clause is used in combination with the GROUP BY Clause to restrict the groups of returned rows to only those whose the condition is TRUE.

For example :
        SELECT location, sum(sales_quantity), 
      FROM order
     WHERE location in ('Jakarta','Bogor','Depok','Tangerang')
  GROUP BY location
    HAVING sum(sales_quantity)
  ORDER BY location DESC
    

2. INSERT .. INTO .. VALUES
The Oracle INSERT statement is used to insert data into a table by inserting one or more rows of data. Below the basic syntax of INSERT statement :
INSERT INTO <table name> (col1, col2, col3,…)
     VALUES (val1, val2, val3,…)

Example :
INSERT INTO sales (sales_id, sales_number, item, quantity, unit_price, item_type)    VALUES (3, 'SO3', 'Aqua2', 30, 2100, 'wtr')

3. UPDATE .. SET .. WHERE

The Oracle UPDATE statement is used to change one or more column data of the table for which the where clause evaluates to TRUE. Below the basic syntax of UPDATE statement :

UPDATE table-Name [[AS] correlation-Name]

SET column-Name = Value [ , column-Name = Value} ]*

[WHERE clause]

Example :

UPDATE sales 

SET item = 'Akua1' 

WHERE sales_id = 1

4. DELETE .. FROM .. WHERE
The oracle DELETE statement is used to remove entire rows of data from specified table or view. Below the basic syntax of DELETE statement :

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]

Example :

DELETE FROM sales

      WHERE sales_id = 3

5. MERGE .. INTO .. USING
The oracle MERGE statement is used to select rows from one or more sources for update or insertion into a table or view. This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements. Below the basic syntax of MERGE statement :

MERGE <hint> INTO <table_name>

USING <table_view_or_query>

   ON (<condition>)

WHEN MATCHED THEN <update_clause> / DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>

Example :

MERGE INTO ynppo_temp_employee yte

   USING (SELECT person_id, employee_number, first_name

            FROM per_all_people_f

           WHERE TRUNC (SYSDATE) BETWEEN effective_start_date

                                     AND effective_end_date

             AND employee_number = '01962') papf

   ON (yte.employee_code = papf.employee_number)

   WHEN MATCHED THEN

      UPDATE

         SET first_name = 'Borokotok'

   WHEN NOT MATCHED THEN

      INSERT (employee_code, first_name)

      VALUES (papf.employee_number, papf.first_name)