Code to Retry Item Key's in Status as ERROR

CREATE OR REPLACE PROCEDURE xx_error_itemkey_retry_p (
   errbuf       OUT VARCHAR2,
   retcode      OUT VARCHAR2,
   p_date    IN     VARCHAR2
)
AS
   CURSOR c_err_itemkey
   IS
        SELECT   ias.item_type,
                 ias.item_key,
                 pa.process_name,
                 ias.process_activity,
                 pa.instance_label,
                 ias.activity_result_code result,
                 ias.error_message,
                 ias.error_stack
          FROM   wf_item_activity_statuses ias, wf_process_activities pa
         WHERE       ias.item_type = 'HRSSA'
                 AND ias.process_activity = pa.instance_id
                 AND ias.activity_status = 'ERROR'
                 AND TRUNC (ias.begin_date) >=
                       TO_DATE (p_date, 'YYYY/MM/DD HH24:MI:SS')
      ORDER BY   ias.begin_date DESC;

   v_err   VARCHAR2 (200);
   v_cnt   NUMBER := 0;
BEGIN
   BEGIN
      FOR x IN c_err_itemkey
      LOOP
         BEGIN
            apps.wf_engine.handleerror (
               'HRSSA',
               x.item_key,
               x.process_name || ':' || x.instance_label,
               'RETRY',
               '#EXCEPTION'
            );
            COMMIT;

            BEGIN
               SELECT   COUNT ( * )
                 INTO   v_cnt
                 FROM   wf_item_activity_statuses ias,
                        wf_process_activities pa
                WHERE       ias.item_key = x.item_key
                        AND ias.item_type = 'HRSSA'
                        AND ias.process_activity = pa.instance_id
                        AND ias.activity_status = 'ERROR';
            EXCEPTION
               WHEN OTHERS
               THEN
                  v_cnt := 0;
            END;

            INSERT INTO xxx_error_itemkey_retry_log_t
              VALUES   (
                           x.item_key,
                           x.process_activity,
                           x.instance_label,
                           x.error_message,
                           x.error_stack,
                           DECODE (v_cnt,
                                   0, 'Retry Successfully',
                                   'Error in Retried'),
                           SYSDATE
                       );

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               v_err := SUBSTR (SQLERRM, 1, 2000);

               INSERT INTO xxx_error_itemkey_retry_log_t
                 VALUES   (x.item_key,
                           x.process_activity,
                           x.instance_label,
                           x.error_message,
                           x.error_stack,
                           v_err,
                           SYSDATE);

               COMMIT;
         END;
      END LOOP;
   END;
END;
/

Understanding the p_validate Control Parameter in HRMS API's

Every published API includes the p_validate control parameter. When this parameter is set to FALSE (the default value), the procedure executes all validation for that business function. If the operation is valid, the database rows/values are inserted or updated or deleted. Any non warning OUT parameters, warning OUT parameters and IN OUT parameters are all set with specific values.

When the p_validate parameter is set to TRUE, the API only checks that the operation is valid. It does so by issuing a savepoint at the start of the procedure and rolling back to that savepoint at the end. You do not have access to these internal savepoints. If the procedure is successful, without raising any validation errors, then non-warning OUT parameters are set to null, warning OUT parameters are set to a specific value, and IN OUT parameters are reset to their IN values.

In some cases we may want to write our own PL/SQL routines using the public API procedures as building blocks. This enables us to write routines specific to our business needs. For example, say that we have a business requirement to apply a DateTracked update to a row and then apply a DateTrack delete to the same row in the future. We could write an "update_and_future_del" procedure that calls two of the standard APIs.

When calling each standard API, p_validate must be set to false. If true is used the update procedure call is rolled back. So when the delete procedure is called, it is working on the non-updated version of the row. However when p_validate is set to false, the update is not rolled back. Thus, the delete call operates as if the user really wanted to apply the whole transaction.

If we want to be able to check that the update and delete operation is valid, you must issue your own savepoint and rollback commands. As the APIs do not issue any commits, there is no danger of part of the work being left in the database. It is the responsibility of the calling code to issue commits.

Note: You should not use our API procedure names for the savepoint names. An unexpected result may occur if you do not use different names.

Get Details of Scheduled Programs

 SELECT   distinct fcr.request_id,
           fcpt.user_concurrent_program_name
           || NVL2 (fcr.description, ' (' || fcr.description || ')', NULL)
              conc_prog,
           CASE
              WHEN fcrc.class_type = 'P'
              THEN
                 'Repeat every '
                 || SUBSTR (fcrc.class_info,
                            1,
                            INSTR (fcrc.class_info, ':') - 1)
                 || DECODE (SUBSTR (fcrc.class_info, INSTR (fcrc.class_info,
                                                            ':',
                                                            1,
                                                            1)
                                                     + 1, 1),
                            'N',
                            ' minutes',
                            'M',
                            ' months',
                            'H',
                            ' hours',
                            'D',
                            ' days')
                 || DECODE (SUBSTR (fcrc.class_info, INSTR (fcrc.class_info,
                                                            ':',
                                                            1,
                                                            2)
                                                     + 1, 1),
                            'S',
                            ' from the start of the prior run',
                            'C',
                            ' from the completion of the prior run')
              ELSE
                 'n/a'
           END
              set_days_of_week,
           fu.user_name requestor,
           fu.description requested_by,
           fu.email_address,
           frt.responsibility_name requested_by_resp,
           TRIM (fl.meaning) status,
           fcr.phase_code,
           fcr.status_code,
           fcr.argument_text "PARAMETERS",
           '------>' dates,
           TO_CHAR (fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested,
           TO_CHAR (fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
              requested_start,
           TO_CHAR ( (fcr.requested_start_date), 'HH24:MI:SS') start_time,
           '------>' holds,
           DECODE (fcr.hold_flag,
                   'Y',
                   'Yes',
                   'N',
                   'No')
              on_hold,
           CASE
              WHEN fcr.hold_flag = 'Y' THEN SUBSTR (u2.description, 0, 40)
           END
              last_update_by,
           CASE WHEN fcr.hold_flag = 'Y' THEN fcr.last_update_date END
              last_update_date,
           '------>' prints,
           fcr.number_of_copies print_count,
           fcr.printer,
           fcr.print_style,
           '------>' schedule,
           fcr.increment_dates,
           CASE
              WHEN fcrc.class_info IS NULL
              THEN
                 'Yes: '
                 || TO_CHAR (fcr.requested_start_date,
                             'DD-MON-YYYY HH24:MI:SS')
              ELSE
                 'n/a'
           END
              run_once,
           CASE
              WHEN fcrc.class_type = 'S'
                   AND INSTR (SUBSTR (fcrc.class_info, 33), '1', 1) > 0
              THEN
                    'Days of week: '
                 || DECODE (SUBSTR (fcrc.class_info, 33, 1), '1', 'Sun, ')
                 || DECODE (SUBSTR (fcrc.class_info, 34, 1), '1', 'Mon, ')
                 || DECODE (SUBSTR (fcrc.class_info, 35, 1), '1', 'Tue, ')
                 || DECODE (SUBSTR (fcrc.class_info, 36, 1), '1', 'Wed, ')
                 || DECODE (SUBSTR (fcrc.class_info, 37, 1), '1', 'Thu, ')
                 || DECODE (SUBSTR (fcrc.class_info, 38, 1), '1', 'Fri, ')
                 || DECODE (SUBSTR (fcrc.class_info, 39, 1), '1', 'Sat ')
              ELSE
                 'n/a'
           END
              days_of_week,
           CASE
              WHEN fcrc.class_type = 'S'
                   AND INSTR (SUBSTR (fcrc.class_info, 1, 31), '1', 1) > 0
              THEN
                    'Set Days of Month: '
                 || DECODE (SUBSTR (fcrc.class_info, 1, 1), '1', '1st, ')
                 || DECODE (SUBSTR (fcrc.class_info, 2, 1), '1', '2nd, ')
                 || DECODE (SUBSTR (fcrc.class_info, 3, 1), '1', '3rd, ')
                 || DECODE (SUBSTR (fcrc.class_info, 4, 1), '1', '4th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 5, 1), '1', '5th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 6, 1), '1', '6th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 7, 1), '1', '7th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 8, 1), '1', '8th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 9, 1), '1', '9th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 10, 1), '1', '10th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 11, 1), '1', '11th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 12, 1), '1', '12th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 13, 1), '1', '13th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 14, 1), '1', '14th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 15, 1), '1', '15th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 16, 1), '1', '16th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 17, 1), '1', '17th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 18, 1), '1', '18th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 19, 1), '1', '19th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 20, 1), '1', '20th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 21, 1), '1', '21st, ')
                 || DECODE (SUBSTR (fcrc.class_info, 22, 1), '1', '22nd, ')
                 || DECODE (SUBSTR (fcrc.class_info, 23, 1), '1', '23rd,')
                 || DECODE (SUBSTR (fcrc.class_info, 24, 1), '1', '24th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 25, 1), '1', '25th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 26, 1), '1', '26th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 27, 1), '1', '27th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 28, 1), '1', '28th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 29, 1), '1', '29th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 30, 1), '1', '30th, ')
                 || DECODE (SUBSTR (fcrc.class_info, 31, 1), '1', '31st. ')
              ELSE
                 'n/a'
           END
              days_of_month,
           CASE
              WHEN fcrc.class_type = 'S'
                   AND SUBSTR (fcrc.class_info, 32, 1) = '1'
              THEN
                 'Yes'
              ELSE
                 'n/a'
           END
              last_day_of_month_ticked,
           fcrc.class_info
    FROM   apps.fnd_concurrent_requests fcr,
           apps.fnd_user fu,
           apps.fnd_user u2,
           apps.fnd_concurrent_programs fcp,
           apps.fnd_concurrent_programs_tl fcpt,
           apps.fnd_printer_styles_tl fpst,
           apps.fnd_conc_release_classes fcrc,
           apps.fnd_responsibility_tl frt,
           apps.fnd_lookups fl
   WHERE       fcp.application_id = fcpt.application_id
           AND fcr.requested_by = fu.user_id
           AND fcr.concurrent_program_id = fcp.concurrent_program_id
           AND fcr.program_application_id = fcp.application_id
           AND fcr.concurrent_program_id = fcpt.concurrent_program_id
           AND fcr.responsibility_id = frt.responsibility_id
           AND fcr.last_updated_by = u2.user_id
           AND fcr.print_style = fpst.printer_style_name(+)
           AND fcr.release_class_id = fcrc.release_class_id(+)
           AND fcr.status_code = fl.lookup_code
           AND fl.lookup_type = 'CP_STATUS_CODE'
           and frt.language = 'US'
           and fcpt.language = 'US'
           AND (fcr.concurrent_program_id, fcr.actual_start_date) IN
                    (  SELECT   concurrent_program_id, MAX (last_run)
                         FROM   (  SELECT   concurrent_program_id,
                                            MAX (actual_start_date) last_run
                                     FROM   apps.fnd_concurrent_requests
                                    WHERE   release_class_id IS NOT NULL
                                 GROUP BY   concurrent_program_id,
                                            release_class_id)
                     GROUP BY   concurrent_program_id)

Significance of Status and Phase code in FND_CONCURRENT_REQUESTS

STATUS_CODE 

A - Waiting
B - Resuming
C - Normal
D - Cancelled
E - Error
F - Scheduled
G - Warning
H - On Hold
I - Normal
M - No Manager
Q - Standby
R - Normal
S - Suspended
T - Terminating
U - Disabled
W - Paused
X - Terminated
Z - Waiting

PHASE_CODE 

C - Completed
I - Inactive
P - Pending
R - Running

Output Post-processor actions failed issues in EBS R12

Environment:
Oracle EBS 12.1.3, Oracle Database 11gR2, RedHat Linux 5

Symptoms:
1) Users unable to open the out files.
2) Concurrent requests failed with “Post-processing of request failed error message”
3) One or more post-processing actions failed. Consult the OPP service log for details.
4) No further attempts will be made to post-process this request.


Cause:
The concurrent manager process was able to successfully invoke the Output Post-Processor (OPP) but encountered a timeout as the OPP takes longer than the value assigned to complete the job.


 Solution:
1) Increase the value of profile Concurrent: OPP Response Timeout . Bounce Apache and retest.
2) If the issue still exists, perform the following steps.
3) Increase the number of Output Post Processors as follows:
4) Increase the number of processes for Output Post Processor.

Additionally, ensure there is a setting of  oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5 under Parameters.

API to Create Batch Header

DECLARE
   l_batch_id                NUMBER := NULL;
   l_object_version_number   NUMBER := NULL;
BEGIN
   pay_batch_element_entry_api.create_batch_header (
      p_session_date            => TRUNC (SYSDATE),
      p_batch_name              => TRIM ('PASSPORT_' || TO_CHAR (SYSDATE, 'MONTH')),
      p_business_group_id       => 44,
      p_action_if_exists        => 'I',
      p_batch_id                => l_batch_id,
      p_object_version_number   => l_object_version_number
   );
   COMMIT;

   DBMS_OUTPUT.put_line (l_batch_id || ': SUCCESS');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;

API to Create Batch Element Lines

DECLARE
   v_eff_start_date                DATE := NULL;
   v_eff_end_date                  DATE := NULL;
   v_input_value_id                NUMBER := NULL;
   v_element_link_id               NUMBER := NULL;
   v_element_name                  VARCHAR2 (500) := NULL;
   v_element_type_id               NUMBER := NULL;
   v_batch_id                      NUMBER := NULL;
   v_batch_line_id                 NUMBER := NULL;
   v_batch_object_version_number   NUMBER := NULL;
BEGIN
   -- blcok to get batch id
   BEGIN
      SELECT   batch_id
        INTO   v_batch_id
        FROM   pay_batch_headers pbh
       WHERE   TRIM (pbh.batch_name) = 'PASSPORT_JUNE';
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   -- block to get the effective start and end date as per hijri
   BEGIN
      SELECT   start_date, end_date
        INTO   v_eff_start_date, v_eff_end_date
        FROM   per_time_periods ptp
       WHERE   TRUNC (SYSDATE) BETWEEN ptp.start_date AND ptp.end_date
               AND ptp.payroll_id = 62;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   -- get element details as per passport_deductions
   BEGIN
      SELECT   pivf.input_value_id,
               pelf.element_link_id,
               petf.element_name,
               petf.element_type_id
        INTO   v_input_value_id,
               v_element_link_id,
               v_element_name,
               v_element_type_id
        FROM   pay_element_types_f petf,
               pay_element_links_f pelf,
               pay_input_values_f pivf
       WHERE   pelf.element_type_id = petf.element_type_id
               AND TRUNC (SYSDATE) BETWEEN petf.effective_start_date
                                       AND  petf.effective_end_date
               AND TRUNC (SYSDATE) BETWEEN pelf.effective_start_date
                                       AND  pelf.effective_end_date
               AND pelf.payroll_id = 62
               AND pivf.element_type_id = pivf.element_type_id
               AND pivf.name = 'Amount'
               AND TRUNC (SYSDATE) BETWEEN pivf.effective_start_date
                                       AND  pivf.effective_end_date
               AND pivf.element_type_id = pelf.element_type_id
               AND petf.element_name = 'PASSPORT_DEDUCTIONS';
   EXCEPTION
      WHEN OTHERS
      THEN
         v_input_value_id := NULL;
         v_element_link_id := NULL;
   END;

   -- api to create element
   pay_batch_element_entry_api.create_batch_line (
      p_session_date            => TRUNC (SYSDATE),
      p_batch_id                => v_batch_id,
      p_assignment_id           => 414,
      p_assignment_number       => 40180,
      p_date_earned             => TRUNC (SYSDATE),
      p_effective_date          => TRUNC (SYSDATE),
      p_effective_start_date    => v_eff_start_date,
      p_effective_end_date      => v_eff_end_date,
      p_element_name            => v_element_name,
      p_element_type_id         => v_element_type_id,
      p_value_3                 => 500,
      p_batch_line_id           => v_batch_line_id,
      p_object_version_number   => v_batch_object_version_number
   );

   COMMIT;

   DBMS_OUTPUT.put_line (v_batch_id || ': SUCCESS');
   DBMS_OUTPUT.put_line (v_batch_line_id || ': SUCCESS');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;


--  query

SELECT   *
  FROM   pay_batch_lines
 WHERE   batch_line_id = 2913983

API to Eliminate Position

DECLARE
   l_effective_start_date           DATE := NULL;
   l_effective_end_date             DATE := NULL;
   l_position_definition_id         NUMBER := NULL;
   l_valid_grades_changed_warning   BOOLEAN;
   l_name                           VARCHAR2 (500) := NULL;
   l_position_id                    NUMBER := NULL;
   l_object_version_number          NUMBER := NULL;
BEGIN
   BEGIN
      SELECT   hapf.position_definition_id,
               hapf.name,
               hapf.object_version_number,
               hapf.position_id
        INTO   l_position_definition_id,
               l_name,
               l_object_version_number,
               l_position_id
        FROM   hr_all_positions_f hapf
       WHERE   TRUNC (SYSDATE) BETWEEN hapf.effective_start_date
                                   AND  hapf.effective_end_date
               AND hapf.availability_status_id = 1
               AND hapf.name =
                     'SR MRI TECHNOLOGIST.P.666 M\.R\.I\. SERVICES (Unit) - F.09';
   EXCEPTION
      WHEN OTHERS
      THEN
         l_position_definition_id := NULL;
         l_name := NULL;
         l_object_version_number := NULL;
         l_position_id := NULL;
   END;


   IF l_position_id IS NOT NULL
   THEN
      hr_position_api.update_position (
         p_validate                       => FALSE,
         p_position_id                    => l_position_id,
         p_effective_start_date           => l_effective_start_date,
         p_effective_end_date             => l_effective_end_date,
         p_position_definition_id         => l_position_definition_id,
         p_valid_grades_changed_warning   => l_valid_grades_changed_warning,
         p_name                           => l_name,
         p_availability_status_id         => 5,
         p_language_code                  => NULL,
         p_comments                       => 'Position Eliminated via API',
         p_object_version_number          => l_object_version_number,
         p_effective_date                 => SYSDATE,
         p_datetrack_mode                 => 'UPDATE'
      );

      --      commit;
      DBMS_OUTPUT.put_line ('Successfully Updated');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

API to Delete Default Job Posting

BEGIN
   irc_default_posting_api.delete_default_posting (
      p_validate                => FALSE,
      p_default_posting_id      => 69001,
      p_object_version_number   => 1
   );
   COMMIT;
END;

API to Delete Assignment

DECLARE
   p_validate                     BOOLEAN := FALSE;
   p_effective_date               DATE := TRUNC (SYSDATE);
   p_assignment_id                per_all_assignments_f.assignment_id%TYPE;
   p_object_version_number        per_all_assignments_f.object_version_number%TYPE;
   p_effective_start_date         per_all_assignments_f.effective_start_date%TYPE;
   p_effective_end_date           per_all_assignments_f.effective_end_date%TYPE;
   p_loc_change_tax_issues        BOOLEAN := NULL;
   p_delete_asg_budgets           BOOLEAN := NULL;
   p_org_now_no_manager_warning   BOOLEAN := NULL;
   p_element_salary_warning       BOOLEAN := NULL;
   p_element_entries_warning      BOOLEAN := NULL;
   p_spp_warning                  BOOLEAN := NULL;
   p_cost_warning                 BOOLEAN := NULL;
   p_life_events_exists           BOOLEAN := NULL;
   p_cobra_coverage_elements      BOOLEAN := NULL;
   p_assgt_term_elements          BOOLEAN := NULL;
   l_assignment_id                NUMBER (15);
   l_object_version_number        NUMBER;
BEGIN
   SELECT   paaf.assignment_id, paaf.object_version_number
     INTO   l_assignment_id, l_object_version_number
     FROM   per_all_assignments_f paaf
    WHERE       paaf.assignment_id = 24175
            AND paaf.assignment_status_type_id = 1
            AND paaf.effective_end_date < paaf.effective_start_date;

   hr_assignment_api.delete_assignment (
      p_validate                     => p_validate,
      p_effective_date               => p_effective_date,
      p_datetrack_mode               => 'DELETE',
      p_assignment_id                => l_assignment_id,
      p_object_version_number        => l_object_version_number,
      p_effective_start_date         => p_effective_start_date,
      p_effective_end_date           => p_effective_end_date,
      p_loc_change_tax_issues        => p_loc_change_tax_issues,
      p_delete_asg_budgets           => p_delete_asg_budgets,
      p_org_now_no_manager_warning   => p_org_now_no_manager_warning,
      p_element_salary_warning       => p_element_salary_warning,
      p_element_entries_warning      => p_element_entries_warning,
      p_spp_warning                  => p_spp_warning,
      p_cost_warning                 => p_cost_warning,
      p_life_events_exists           => p_life_events_exists,
      p_cobra_coverage_elements      => p_cobra_coverage_elements,
      p_assgt_term_elements          => p_assgt_term_elements
   );
   DBMS_OUTPUT.put_line ('SUCESS');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

API to Create Vacancy Requisition

DECLARE
   l_object_version_number   NUMBER := NULL;
   l_vacancy_id              NUMBER := NULL;
   l_inv_pos_grade_warning   BOOLEAN := NULL;
   l_inv_job_grade_warning   BOOLEAN := NULL;
   l_requisition_id          NUMBER := NULL;
   l_position_id             NUMBER := NULL;
   l_job_id                  NUMBER := NULL;
   l_position                VARCHAR2 (2000) := NULL;
   l_pos_name                VARCHAR2 (2000) := NULL;
   l_position_type           VARCHAR2 (2000) := NULL;
   l_location_id             NUMBER := NULL;
   l_organization_id         NUMBER := NULL;
   l_grade_id                NUMBER := NULL;
   l_org_name                VARCHAR2 (2000) := NULL;
BEGIN
   INSERT INTO fnd_sessions
     VALUES   (USERENV ('sessionid'), SYSDATE);

   --create vacancy requisition for vacancy
   BEGIN
      per_requisitions_api.create_requisition (
         p_validate                => FALSE,
         p_business_group_id       => 44,
         p_date_from               => SYSDATE,
         p_name                    => 'IRCDUMMY',
         p_requisition_id          => l_requisition_id,
         p_object_version_number   => l_object_version_number
      );
      COMMIT;

      DBMS_OUTPUT.put_line ('Success creating req: ' || l_requisition_id);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('FAILURE req: ' || SQLERRM);
   END;

   l_object_version_number := NULL;

   -- get position to create vacancy
   BEGIN
      SELECT   hapf.position_type,
               hapf.position_id,
               hapf.job_id,
               hapf.location_id,
               hapf.organization_id,
               entry_grade_id,
               hr_general.decode_organization (hapf.organization_id),
               hapf.name
        INTO   l_position_type,
               l_position_id,
               l_job_id,
               l_location_id,
               l_organization_id,
               l_grade_id,
               l_org_name,
               l_pos_name
        FROM   hr_all_positions_f hapf
       WHERE   hapf.position_id = 21665 AND hapf.business_group_id = 44
               AND TRUNC (SYSDATE) BETWEEN hapf.effective_start_date
                                       AND  hapf.effective_end_date;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_position_id := NULL;
         l_job_id := NULL;
         l_position := NULL;
         l_position_type := NULL;
         l_location_id := NULL;
         l_organization_id := NULL;
         l_grade_id := NULL;
         l_org_name := NULL;
   END;

   DBMS_OUTPUT.put_line ('l_requisition_id: ' || l_requisition_id);

   per_vacancy_api.create_vacancy (
      p_validate                => FALSE,
      p_effective_date          => SYSDATE,
      p_requisition_id          => l_requisition_id,
      p_date_from               => SYSDATE,
      p_name                    => 'IRCDUMMY',
      p_business_group_id       => 44,
      p_description             => 'Creating Dummy IRC for Local & International Recruitment',
      p_position_id             => l_position_id,
      p_job_id                  => l_job_id,
      p_grade_id                => l_grade_id,
      p_organization_id         => l_organization_id,
      p_number_of_openings      => 100,
      p_attribute7              => 'Internal',
      p_object_version_number   => l_object_version_number,
      p_vacancy_id              => l_vacancy_id,
      p_inv_pos_grade_warning   => l_inv_pos_grade_warning,
      p_inv_job_grade_warning   => l_inv_job_grade_warning
   );
   COMMIT;
   DBMS_OUTPUT.put_line ('Success creating vacancy: ' || l_vacancy_id);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('FAILURE Vac: ' || SQLERRM);
END;

API to Create Organization Manager

DECLARE
   v_org_information_id      NUMBER;
   v_object_version_number   NUMBER;
   v_warning                 BOOLEAN;
BEGIN
   INSERT INTO fnd_sessions
     VALUES   (USERENV ('sessionid'), SYSDATE);

   hr_organization_api.create_org_manager (
      p_validate                => FALSE,
      p_effective_date          => TRUNC (SYSDATE),
      p_organization_id         => 569,
      p_org_info_type_code      => 'Organization Name Alias',
      p_org_information2        => 88247,
      p_org_information3        => TO_CHAR (TRUNC (SYSDATE),
                                            'YYYY/MM/DD HH24:MI:SS'),
      p_org_information4        => '4712/12/31 00:00:00',
      p_org_information_id      => v_org_information_id,
      p_object_version_number   => v_object_version_number,
      p_warning                 => v_warning
   );
   DBMS_OUTPUT.put_line ('success');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

API to Create Events

DECLARE
   v_event_id                NUMBER (10);
   v_object_version_number   NUMBER (10);
BEGIN
   per_events_api.create_event
                          (p_validate                      => FALSE,
                           p_date_start                    =>   TRUNC (SYSDATE)
                                                              + 10,
                           p_type                          => 'ENGLISH TEST',
                           p_business_group_id             => 44,--HR/TCA merge
                           p_assignment_id                 => 81646,
                           p_date_end                      => TRUNC (SYSDATE)
                           p_emp_or_apl                    => 'A',
                           p_event_or_interview            => 'I',
                           p_attribute_category            => 'English Test',
                           p_attribute2                    => '150',
                           p_event_id                      => v_event_id,
                           p_object_version_number         => v_object_version_number
                          );
   DBMS_OUTPUT.put_line ('v_event_id=' || v_event_id);
   DBMS_OUTPUT.put_line ('v_object_version_number=' || v_object_version_number);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

API to Create Document of Records

DECLARE
   l_document_extra_info_id   NUMBER := NULL;
   l_object_version_number    NUMBER := NULL;
BEGIN
   hr_document_extra_info_api.create_doc_extra_info (
      p_validate                 => FALSE,
      p_person_id                => 88247,
      p_document_type_id         => 4,
      p_date_from                => TO_DATE ('10-JUN-2012', 'DD-MON-YYYY'),
      p_date_to                  => TO_DATE ('10-JUN-2012', 'DD-MON-YYYY'),
      p_document_number          => 'B009232',
      p_document_extra_info_id   => l_document_extra_info_id,
      p_object_version_number    => l_object_version_number
   );
   COMMIT;
   DBMS_OUTPUT.put_line ('SUCCESS');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('FAILURE ' || SQLERRM);
END;

API to Create Default Job Posting

DECLARE
   l_position_id             NUMBER (15) := NULL;
   l_brief_description       VARCHAR2 (2000) := 'Enter Job Description';
   l_detailed_description VARCHAR2 (2000)  := 'Enter Job Detailed Description' ;
   l_job_requirements        VARCHAR2 (2000) := 'Enter Job Requirements';
   l_additional_details      VARCHAR2 (2000) := NULL;
   l_how_to_apply            VARCHAR2 (2000) := NULL;
   l_default_posting_id      NUMBER := NULL;
   l_object_version_number   NUMBER := NULL;
BEGIN
   -- get position id
   BEGIN
      SELECT   hapf.position_id
        INTO   l_position_id
        FROM   hr_all_positions_f hapf
       WHERE   TRUNC (SYSDATE) BETWEEN hapf.effective_start_date
                                   AND  hapf.effective_end_date
               AND hapf.availability_status_id = 1
               AND hapf.name =
                     'GRAPHIC DESIGNER.P.HEALTH EDUCATION PORTAL (Sec) - F.08';
   EXCEPTION
      WHEN OTHERS
      THEN
         l_position_id := NULL;
   END;

   BEGIN
      irc_default_posting_api.create_default_posting (
         p_validate                => FALSE,
         p_language_code           => 'US',
         p_position_id             => l_position_id,
         p_job_id                  => NULL,
         p_org_name                => NULL,
         p_org_description         => NULL,
         p_job_title               => NULL,
         p_brief_description       => l_brief_description,
         p_detailed_description    => l_detailed_description,
         p_job_requirements        => l_job_requirements,
         p_additional_details      => l_additional_details,
         p_how_to_apply            => l_how_to_apply,
         p_default_posting_id      => l_default_posting_id,
         p_object_version_number   => l_object_version_number
      );
      DBMS_OUTPUT.put_line ('Success : ' || l_default_posting_id);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('API Error: ' || SQLERRM);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;