Submit Request using FND_REQUEST.SUBMIT_REQUEST function

DECLARE

   l_application_short_name    VARCHAR2 (50) := NULL;

   l_concurrent_program_name   VARCHAR2 (500) := NULL;

   l_payroll_id                NUMBER := NULL;

   l_consolidation_set_id      NUMBER := NULL;

   l_period                    VARCHAR2 (500) := NULL;

   l_end_date                  VARCHAR2 (500) := NULL;

   l_assignment_set_id         NUMBER := NULL;

   l_request_id                NUMBER := NULL;

BEGIN

   --Initialize Session

   fnd_global.

    apps_initialize (user_id => 1895,                                -- BIJOYJ

                                     resp_id => 50637,    -- XXX HRMS Manager

                                                      resp_appl_id => 800 -- Human Resouces

                                                                         );

   -- Get Application Short Name

   BEGIN

      SELECT fa.application_short_name

        INTO l_application_short_name

        FROM fnd_application fa, fnd_application_tl fat

       WHERE fa.application_id = fat.application_id

             AND UPPER (fat.application_name) = 'PAYROLL';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_application_short_name := NULL;

   END;

   -- Get Concurrent Program Short Name

   BEGIN

      SELECT fcp.concurrent_program_name

        INTO l_concurrent_program_name

        FROM fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpt

       WHERE fcp.concurrent_program_id = fcpt.concurrent_program_id

             AND UPPER (fcpt.user_concurrent_program_name) = 'PAYROLL RUN';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_concurrent_program_name := NULL;

   END;

   -- Get Payroll ID

   BEGIN

      SELECT payroll_id

        INTO l_payroll_id

        FROM pay_payrolls_f

       WHERE UPPER (payroll_name) = 'XXX MONTHLY PAYROLL'

             AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

   EXCEPTION

      WHEN OTHERS

      THEN

         l_payroll_id := NULL;

   END;

   -- Get Consolidation Set

   BEGIN

      SELECT consolidation_set_id

        INTO l_consolidation_set_id

        FROM pay_consolidation_sets

       WHERE UPPER (consolidation_set_name) =

                'XXX PAYROLL CONSOLIDATION SET';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_consolidation_set_id := NULL;

   END;

   -- Get Period

   BEGIN

      SELECT TO_CHAR (end_date, 'YYYY/MM/DD HH24:MI:SS')

        INTO l_end_date

        FROM per_time_periods p

       WHERE p.payroll_id = l_payroll_id

             AND p.period_name = '6 2014 Calendar Month';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_period := NULL;

   END;

   -- Get Assignment Set ID

   BEGIN

      SELECT assignment_set_id

        INTO l_assignment_set_id

        FROM hr_assignment_sets

       WHERE assignment_set_name = 'ASSIGNMENT_SET_DEMO_API';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_assignment_set_id := NULL;

   END;

   IF     l_concurrent_program_name IS NOT NULL

      AND l_application_short_name IS NOT NULL

      AND l_payroll_id IS NOT NULL

      AND l_consolidation_set_id IS NOT NULL

   THEN

      l_request_id :=

         fnd_request.

          submit_request (application   => l_application_short_name,

                          program       => l_concurrent_program_name,

                          start_time    => SYSDATE,

                          argument1     => 'RUN',

                          argument2     => l_payroll_id,         -- Payroll ID

                          argument3     => l_consolidation_set_id, -- Consolidation Set Name

                          argument4     => l_end_date,          -- Period Name

                          argument5     => l_end_date,      -- Date of Payment

                          argument6     => NULL,                -- Element Set

                          argument7     => l_assignment_set_id, -- Assignment Set

                          argument8     => NULL,

                          argument9     => NULL);

      COMMIT;

      DBMS_OUTPUT.

       put_line (l_request_id || ' has been sumbitted Successfully !!!');

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;