Check if string contains all zeros in Oracle

select rpad (nvl (decode('X000',lpad('0',length('X000'),'0'),lpad(' ',length('X000'),' '),'X000'), chr (32)), 15, ' ') from dual

Data Loader for Lookup Sample dld file and API

To Load Value to the lookups, try following script. If it works, perfect or use data loader instead of breaking your head to debug the API. It is not public API.

DECLARE
   lr_rowid        ROWID  := NULL;
   l_lookup_code   NUMBER;
   CURSOR cur
   IS
      SELECT DISTINCT position_title
                 FROM xx_position_master
             ORDER BY 1;
BEGIN
   FOR i IN cur
   LOOP
      l_lookup_code := NULL;
      SELECT MAX (lookup_code) + 10
        INTO l_lookup_code
        FROM fnd_lookup_values
       WHERE lookup_type = 'XX_POSITION_TITLE'
         AND LANGUAGE = hr_api.userenv_lang;
      BEGIN
         fnd_lookup_values_pkg.insert_row
                             (x_rowid                    => lr_rowid,
                              x_lookup_type              => 'XX_POSITION_TITLE',
                              x_security_group_id        => 0,
                              x_view_application_id      => 3,
                              x_lookup_code              => l_lookup_code,
                              x_tag                      => NULL,
                              x_attribute_category       => NULL,
                              x_attribute1               => NULL,
                              x_attribute2               => NULL,
                              x_attribute3               => NULL,
                              x_attribute4               => NULL,
                              x_enabled_flag             => 'Y',
                              x_start_date_active        => NULL,
                              x_end_date_active          => NULL,
                              x_territory_code           => NULL,
                              x_attribute5               => NULL,
                              x_attribute6               => NULL,
                              x_attribute7               => NULL,
                              x_attribute8               => NULL,
                              x_attribute9               => NULL,
                              x_attribute10              => NULL,
                              x_attribute11              => NULL,
                              x_attribute12              => NULL,
                              x_attribute13              => NULL,
                              x_attribute14              => NULL,
                              x_attribute15              => NULL,
                              x_meaning                  => i.position_title,
                              x_description              => i.position_title,
                              x_creation_date            => SYSDATE,
                              x_created_by               => apps.fnd_global.user_id,
                              x_last_update_date         => SYSDATE,
                              x_last_updated_by          => apps.fnd_global.user_id,
                              x_last_update_login        => apps.fnd_global.login_id
                             );
         DBMS_OUTPUT.put_line ('Success');
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Error' || SQLERRM);
      END;
   END LOOP;
END;

DLD Sample File:

Data   tab  Data                                tab  Data                                tab  tab  Date             *dn
10      tab   Administration Manager  tab Administration Manager  tab   tab 01-JAN-1951 *dn
20      tab   Administrator                   tab Administrator                   tab   tab 01-JAN-1951 *dn

Delete Positions in Oracle HRMS through API

declare
ld_edds_date date;
ld_edde_date date;
ln_ovn       NUMBER;
cursor c is
select * from hr_all_positions_f   where trunc(creation_date) = trunc(sysdate);
begin
for i in c
loop
ln_ovn := i.OBJECT_VERSION_NUMBER;
hr_position_api.delete_position
  (p_validate                       => false
  ,p_position_id                    => i.position_id
  ,p_effective_start_date           => ld_edds_date
  ,p_effective_end_date             => ld_edde_date
  ,p_object_version_number          => ln_ovn
  ,p_effective_date                 => '01-Jan-1980'
  ,p_datetrack_mode                 => 'ZAP'
  --,p_security_profile_id        in number      default hr_security.get_security_profile
  );
end LOOP;
end;

Handling New Line in SQL Loader

Use
col_name REPLACE(:col_name,CHR(13) ",

SQL*Loader program as a Concurrent Program in Oracle Apps.

The following steps will describe the process to register a SQL*Loader program as a Concurrent Program in Oracle Apps.

Step 1]

Create the SQL*Loader Control and Data file and place them in Server(ex: $CUSTOM_TOP/bin). Create or check the interface table structures in the backend.

LOAD DATA

INFILE 'test.dat'

APPEND INTO TABLE TESTDEPT

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

(deptno,dname,loc)

Control file: test.ctl

12, Research, "Saratoga"
10, "Accounting", Cleveland
11, "Art", Salem

Data file: test.dat

1	CREATE TABLE testdept
2	(deptno NUMBER(2) NOT NULL,

3	dname VARCHAR2(14),
4	loc VARCHAR2(13));

Step 2]

Go to Application Developer > Concurrent > Executables. Define a Concurrent Program Executable. Choose the Execution Method as SQL*Loader and give the Execution File Name as the name of the SQL*Loader control file. Save your work.

Step 3]

Go to Application Developer > Concurrent > Program. Define the Concurrent Program. Attach the executable defined above.

Step 4]

Go to parameters of the concurrent program. Create a parameter to take the server path of the data file. You can also place the default value.

Step 5]

Attach the Concurrent program to a Responsibility through a Request Group.

Step 6]

Go to that Responsibility and Run the Concurrent Program. If successful check the output file that have all data uploading information.

LAG and LEAD Analytic Functions

Introduction

Both LAG and LEAD functions have the same usage, as shown below.

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

• value_expression - Can be a column or a built-in function, except for other analytic functions.
• offset - The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
• default - The value returned if the offset is outside the scope of the window. The default value is NULL.

Looking at the EMP table, we query the data in salary (SAL) order.

SELECT empno,
       ename,
       job,
       sal
FROM   emp
ORDER BY sal;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7900 JAMES      CLERK            950
      7876 ADAMS      CLERK           1100
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7934 MILLER     CLERK           1300
      7844 TURNER     SALESMAN        1500
      7499 ALLEN      SALESMAN        1600
      7782 CLARK      MANAGER         2450
      7698 BLAKE      MANAGER         2850
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7839 KING       PRESIDENT       5000

SQL>

LAG

The LAG function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY of the LAG function is used to order the data by salary.

SELECT empno,
       ename,
       job,
       sal,
       LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
       sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM   emp;

     EMPNO ENAME      JOB              SAL   SAL_PREV   SAL_DIFF
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK            800          0        800
      7900 JAMES      CLERK            950        800        150
      7876 ADAMS      CLERK           1100        950        150
      7521 WARD       SALESMAN        1250       1100        150
      7654 MARTIN     SALESMAN        1250       1250          0
      7934 MILLER     CLERK           1300       1250         50
      7844 TURNER     SALESMAN        1500       1300        200
      7499 ALLEN      SALESMAN        1600       1500        100
      7782 CLARK      MANAGER         2450       1600        850
      7698 BLAKE      MANAGER         2850       2450        400
      7566 JONES      MANAGER         2975       2850        125
      7788 SCOTT      ANALYST         3000       2975         25
      7902 FORD       ANALYST         3000       3000          0
      7839 KING       PRESIDENT       5000       3000       2000

SQL>

LEAD

The LEAD function is used to return data from the next row. The following query returns the salary from the next row to calulate the difference between the salary of the current row and the following row.

SELECT empno,
       ename,
       job,
       sal,
       LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
       LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM   emp;

     EMPNO ENAME      JOB              SAL   SAL_NEXT   SAL_DIFF
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK            800        950        150
      7900 JAMES      CLERK            950       1100        150
      7876 ADAMS      CLERK           1100       1250        150
      7521 WARD       SALESMAN        1250       1250          0
      7654 MARTIN     SALESMAN        1250       1300         50
      7934 MILLER     CLERK           1300       1500        200
      7844 TURNER     SALESMAN        1500       1600        100
      7499 ALLEN      SALESMAN        1600       2450        850
      7782 CLARK      MANAGER         2450       2850        400
      7698 BLAKE      MANAGER         2850       2975        125
      7566 JONES      MANAGER         2975       3000         25
      7788 SCOTT      ANALYST         3000       3000          0
      7902 FORD       ANALYST         3000       5000       2000
      7839 KING       PRESIDENT       5000          0      -5000

Datetrack Modes in Oracle HRMS

p_datetrack_update_mode
-----------------------
APIs that do updates on at least one datetracked table will contain this
parameter. It defines the type of datetracked operation to be performed. The
choices are
UPDATE                    Keep history of existing information
CORRECTION                Correct existing information
UPDATE_OVERRIDE           Replace all scheduled changes
UPDATE_CHANGE_INSERT      Insert this change before next scheduled change.
Please note that the OVN for each datetrack change for the same primary key
will be different. It is,therefore, important that the correct datetrack record
is read for the appropriate datetrack mode of operation.  i.e; at the correct
p_effective_date.

p_datetrack_delete_mode
-----------------------
APIs that do delete operations on a datetracked table will contain this
parameter. It allows you to define the type of datetrack deletes to make.
The choices are
ZAP                      Completely remove from database
DELETE                   Set end date of record to effective date
FUTURE_CHANGE            Remove all scheduled changes
DELETE_NEXT_CHANGE       Remove next change