Mandatory Attachment - Through Userhook - Leave of Absence Oracle Self Service(SSHR)

            BEGIN
                         SELECT COUNT (*)
                           INTO l_attachment_count
                           FROM hr_api_transactions a,
                                hr_api_transaction_steps b,
                                fnd_attached_documents c
                          WHERE a.transaction_id = b.transaction_id
                            AND TRUNC (a.creation_date) = p_effective_date
                            AND a.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
                            AND c.entity_name='PER_ABSENCE_ATTENDANCES'
                            AND a.status <> 'D'
                            AND a.creator_person_id = p_person_id
                            AND b.information5 = p_absence_attendance_type_id
                            AND to_char(apps.fnd_date.canonical_to_date(b.information1))=p_date_start
                            AND to_char(apps.fnd_date.canonical_to_date(b.information2))=p_date_end
                            AND LTRIM (RTRIM (SUBSTR (c.pk1_value,
                                                      INSTR (c.pk1_value, '_', 1) + 1,
                                                      10
                                                     )
                                             )
                                      ) = a.transaction_id;
                      Exception when others then
                         l_attachment_count := 0;
                      END;
                      --
                      IF nvl(l_attachment_count,0) = 0
                      THEN
                         xx_userhook_pkg.raise_hr_warning('Please attach the Document');
                      ELSE
                         NULL;
                      END IF;
------------
   PROCEDURE raise_hr_warning (l_mesg IN VARCHAR2)
   IS
   BEGIN
      hr_utility.set_message (801, 'HR_ELE_ENTRY_FORMULA_HINT');
      hr_utility.set_message_token ('FORMULA_TEXT', l_mesg);
      hr_utility.raise_error;
   END raise_hr_warning;
--------------