Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Saturday, April 5, 2014

Oracle HRMS Scripts(Queries) - Employee Elements

SELECT entry.element_entry_id, entry.assignment_id,
          entry.effective_start_date, entry.effective_end_date,
          entry.cost_allocation_keyflex_id, entry.updating_action_id,
          entry.element_link_id, entry.original_entry_id, entry.creator_type,
          entry.entry_type, DECODE (entry.entry_type, 'D', 'Y', 'N'),
          DECODE (entry.entry_type, 'S', 'Y', 'N'), entry.comment_id,
          entry.creator_id, entry.reason,
          hr_general.decode_lookup ('ELE_ENTRY_REASON', entry.reason),
          entry.target_entry_id, entry.attribute_category, entry.attribute1,
          entry.attribute2, entry.attribute3, entry.attribute4,
          entry.attribute5, entry.attribute6, entry.attribute7,
          entry.attribute8, entry.attribute9, entry.attribute10,
          entry.attribute11, entry.attribute12, entry.attribute13,
          entry.attribute14, entry.attribute15, entry.attribute16,
          entry.attribute17, entry.attribute18, entry.attribute19,
          entry.attribute20, ELEMENT.element_type_id, elementtl.element_name,
          SUBSTR (ELEMENT.processing_type, 1, 1),
          hr_general.decode_lookup ('PROCESSING_TYPE',
                                    ELEMENT.processing_type),
          ELEMENT.processing_priority,
          SUBSTR (ELEMENT.process_in_run_flag, 1, 1),
          SUBSTR (ELEMENT.closed_for_entry_flag, 1, 1),
          SUBSTR (ELEMENT.additional_entry_allowed_flag, 1, 1),
          SUBSTR (ELEMENT.multiple_entries_allowed_flag, 1, 1),
          ELEMENT.input_currency_code,
          SUBSTR (pay_paywsmee_pkg.overridden (LINK.element_link_id,
                                               entry.assignment_id,
                                               sesh.effective_date
                                              ),
                  1,
                  1
                 ),
          SUBSTR (pay_paywsmee_pkg.adjusted (LINK.element_link_id,
                                             entry.assignment_id,
                                             sesh.effective_date
                                            ),
                  1,
                  1
                 ),
          SUBSTR (pay_paywsmee_pkg.processed (entry.element_entry_id,
                                              entry.original_entry_id,
                                              ELEMENT.processing_type,
                                              entry.entry_type,
                                              sesh.effective_date
                                             ),
                  1,
                  1
                 ),
          DECODE (NVL (entry_proc.source_asg_action_id, -1),
                  -1, 'N',
                  'Y'
                 ) retroactive,
          ELEMENT.classification_id, SUBSTR (LINK.costable_type, 1, 1),
          entry.subpriority, benefit.contributions_used, entry.creation_date,
          entry.created_by, entry.last_update_login, entry.last_updated_by,
          entry.last_update_date, entry.date_earned,
          pay_paywsmee_pkg.get_original_date_earned
                                 (entry.element_entry_id)
                                                         original_date_earned,
          entry.personal_payment_method_id,
          SUBSTR
             (pay_paywsmee_pkg.personal_payment_method
                                            (entry.personal_payment_method_id,
                                             entry.assignment_id,
                                             sesh.effective_date
                                            ),
              1,
              255
             ),
          ELEMENT.third_party_pay_only_flag, entry.ROWID row_id,
          entry.entry_information_category, entry.entry_information1,
          entry.entry_information2, entry.entry_information3,
          entry.entry_information4, entry.entry_information5,
          entry.entry_information6, entry.entry_information7,
          entry.entry_information8, entry.entry_information9,
          entry.entry_information10, entry.entry_information11,
          entry.entry_information12, entry.entry_information13,
          entry.entry_information14, entry.entry_information15,
          entry.entry_information16, entry.entry_information17,
          entry.entry_information18, entry.entry_information19,
          entry.entry_information20, entry.entry_information21,
          entry.entry_information22, entry.entry_information23,
          entry.entry_information24, entry.entry_information25,
          entry.entry_information26, entry.entry_information27,
          entry.entry_information28, entry.entry_information29,
          entry.entry_information30, -999999999999999
     FROM pay_element_types_f_tl elementtl,
          pay_element_types_f ELEMENT,
          ben_benefit_classifications benefit,
          pay_element_entries_f entry,
          pay_element_links_f LINK,
          pay_entry_process_details entry_proc,
          fnd_sessions sesh                                  /* TABLE JOINS */
    WHERE ELEMENT.element_type_id = elementtl.element_type_id
      AND elementtl.LANGUAGE = 'AR'
      AND ELEMENT.element_type_id = LINK.element_type_id
      AND ELEMENT.benefit_classification_id = benefit.benefit_classification_id(+)
      AND entry.element_link_id = LINK.element_link_id
      AND entry.element_entry_id =
                         entry_proc.element_entry_id(+)
                            /* ONLY DISPLAY ENTRIES FOR CERTAIN SUB-SYSTEMS */
      AND entry.creator_type IN
             ('H',                                                   /* MIX */
              'P',                                               /* BACKPAY */
              'SP',                                      /* SALARY PROPOSAL */
              'F',                                                 /* OTHER */
              'M',                               /* STATUTORY MATERNITY PAY */
              'S',                                    /* STATUTORY SICK PAY */
              'A',                                               /* ABSENCE */
              'D',                                           /* ADVANCE PAY */
              'DF',                                     /* ADVANCE PAY FORM */
              'R',                                    /* RETROPAY BY ACTION */
              'EE',                                  /* RETROPAY/ELEMENT EE */
              'RR',                                  /* RETROPAY/ELEMENT RR */
              'AD',                                /* ADVANCEPAY/ELEMENT AD */
              'AE',                                /* ADVANCEPAY/ELEMENT AE */
              'PR',                                  /* RETROPAY ELEMENT PR */
              'NR',                                  /* RETROPAY/ELEMENT NR */
              'FL'                                            /* FLSA ENTRY */
             )                     /* ONLY DISPLAY ENTRIES OF CERTAIN TYPES */
      AND entry.entry_type IN
             ('E', /* NORMAL ENTRY */ 'S', /* OVERRIDE */ 'D' /* ADDITIONAL */)
                       /* ONLY DISPLAY ENTRIES CURRENT AS OF EFFECTIVE DATE */
      AND USERENV ('sessionid') = sesh.session_id
      AND sesh.effective_date BETWEEN ELEMENT.effective_start_date AND ELEMENT.effective_end_date
      AND sesh.effective_date BETWEEN LINK.effective_start_date AND LINK.effective_end_date
      AND sesh.effective_date BETWEEN entry.effective_start_date AND entry.effective_end_date
   UNION ALL
   SELECT DISTINCT entry.element_entry_id, entry.assignment_id,
                   entry.effective_start_date, entry.effective_end_date,
                   entry.cost_allocation_keyflex_id, entry.updating_action_id,
                   entry.element_link_id, entry.original_entry_id,
                   entry.creator_type, entry.entry_type,
                   DECODE (entry.entry_type, 'D', 'Y', 'N'),
                   DECODE (entry.entry_type, 'S', 'Y', 'N'), entry.comment_id,
                   entry.creator_id, entry.reason,
                   hr_general.decode_lookup ('ELE_ENTRY_REASON', entry.reason),
                   entry.target_entry_id, entry.attribute_category,
                   entry.attribute1, entry.attribute2, entry.attribute3,
                   entry.attribute4, entry.attribute5, entry.attribute6,
                   entry.attribute7, entry.attribute8, entry.attribute9,
                   entry.attribute10, entry.attribute11, entry.attribute12,
                   entry.attribute13, entry.attribute14, entry.attribute15,
                   entry.attribute16, entry.attribute17, entry.attribute18,
                   entry.attribute19, entry.attribute20,
                   ELEMENT.element_type_id, elementtl.element_name,
                   SUBSTR (ELEMENT.processing_type, 1, 1),
                   hr_general.decode_lookup ('PROCESSING_TYPE',
                                             ELEMENT.processing_type
                                            ),
                   ELEMENT.processing_priority,
                   SUBSTR (ELEMENT.process_in_run_flag, 1, 1),
                   SUBSTR (ELEMENT.closed_for_entry_flag, 1, 1),
                   SUBSTR (ELEMENT.additional_entry_allowed_flag, 1, 1),
                   SUBSTR (ELEMENT.multiple_entries_allowed_flag, 1, 1),
                   ELEMENT.input_currency_code,
                   SUBSTR (pay_paywsmee_pkg.overridden (LINK.element_link_id,
                                                        entry.assignment_id,
                                                        sesh.effective_date
                                                       ),
                           1,
                           1
                          ),
                   SUBSTR (pay_paywsmee_pkg.adjusted (LINK.element_link_id,
                                                      entry.assignment_id,
                                                      sesh.effective_date
                                                     ),
                           1,
                           1
                          ),
                   SUBSTR
                        (pay_paywsmee_pkg.processed (entry.element_entry_id,
                                                     entry.original_entry_id,
                                                     ELEMENT.processing_type,
                                                     entry.entry_type,
                                                     sesh.effective_date
                                                    ),
                         1,
                         1
                        ),
                   DECODE (NVL (entry_proc.source_asg_action_id, -1),
                           -1, 'N',
                           'Y'
                          ) retroactive,
                   ELEMENT.classification_id,
                   SUBSTR (LINK.costable_type, 1, 1), entry.subpriority,
                   benefit.contributions_used, entry.creation_date,
                   entry.created_by, entry.last_update_login,
                   entry.last_updated_by, entry.last_update_date,
                   entry.date_earned,
                   pay_paywsmee_pkg.get_original_date_earned
                                 (entry.element_entry_id)
                                                         original_date_earned,
                   entry.personal_payment_method_id,
                   SUBSTR
                      (pay_paywsmee_pkg.personal_payment_method
                                            (entry.personal_payment_method_id,
                                             entry.assignment_id,
                                             sesh.effective_date
                                            ),
                       1,
                       255
                      ),
                   ELEMENT.third_party_pay_only_flag, entry.ROWID row_id,
                   entry.entry_information_category, entry.entry_information1,
                   entry.entry_information2, entry.entry_information3,
                   entry.entry_information4, entry.entry_information5,
                   entry.entry_information6, entry.entry_information7,
                   entry.entry_information8, entry.entry_information9,
                   entry.entry_information10, entry.entry_information11,
                   entry.entry_information12, entry.entry_information13,
                   entry.entry_information14, entry.entry_information15,
                   entry.entry_information16, entry.entry_information17,
                   entry.entry_information18, entry.entry_information19,
                   entry.entry_information20, entry.entry_information21,
                   entry.entry_information22, entry.entry_information23,
                   entry.entry_information24, entry.entry_information25,
                   entry.entry_information26, entry.entry_information27,
                   entry.entry_information28, entry.entry_information29,
                   entry.entry_information30, assact.assignment_action_id
              FROM pay_element_types_f_tl elementtl,
                   pay_element_types_f ELEMENT,
                   ben_benefit_classifications benefit,
                   pay_element_entries_f entry,
                   pay_element_links_f LINK,
                   pay_entry_process_details entry_proc,
                   pay_assignment_actions assact,
                   pay_payroll_actions payact,
                   fnd_sessions sesh                         /* TABLE JOINS */
             WHERE ELEMENT.element_type_id = elementtl.element_type_id
               AND elementtl.LANGUAGE = USERENV ('LANG')
               AND ELEMENT.element_type_id = LINK.element_type_id
               AND ELEMENT.benefit_classification_id = benefit.benefit_classification_id(+)
               AND entry.element_link_id = LINK.element_link_id
               AND entry.element_entry_id =
                         entry_proc.element_entry_id(+)
                            /* ONLY DISPLAY ENTRIES FOR CERTAIN SUB-SYSTEMS */
               AND entry.creator_type IN
                      ('H',                                          /* MIX */
                       'P',                                      /* BACKPAY */
                       'SP',                             /* SALARY PROPOSAL */
                       'F',                                        /* OTHER */
                       'M',                      /* STATUTORY MATERNITY PAY */
                       'S',                           /* STATUTORY SICK PAY */
                       'A',                                      /* ABSENCE */
                       'D',                                  /* ADVANCE PAY */
                       'DF',                            /* ADVANCE PAY FORM */
                       'R',                           /* RETROPAY BY ACTION */
                       'EE',                         /* RETROPAY/ELEMENT EE */
                       'RR',                         /* RETROPAY/ELEMENT RR */
                       'AD',                       /* ADVANCEPAY/ELEMENT AD */
                       'AE',                       /* ADVANCEPAY/ELEMENT AE */
                       'PR',                         /* RETROPAY ELEMENT PR */
                       'NR',                         /* RETROPAY/ELEMENT NR */
                       'FL'                                   /* FLSA ENTRY */
                      )            /* ONLY DISPLAY ENTRIES OF CERTAIN TYPES */
               AND entry.entry_type IN
                      ('E', /* NORMAL ENTRY */ 'S', /* OVERRIDE */ 'D' /* ADDITIONAL */)
                       /* ONLY DISPLAY ENTRIES CURRENT AS OF EFFECTIVE DATE */
               AND USERENV ('sessionid') = sesh.session_id
               AND sesh.effective_date BETWEEN ELEMENT.effective_start_date
                                           AND ELEMENT.effective_end_date
               AND sesh.effective_date BETWEEN LINK.effective_start_date
                                           AND LINK.effective_end_date
               AND entry.assignment_id = assact.assignment_id
               AND assact.payroll_action_id = payact.payroll_action_id
               AND payact.date_earned BETWEEN ELEMENT.effective_start_date
                                          AND ELEMENT.effective_end_date
               AND payact.date_earned BETWEEN LINK.effective_start_date
                                          AND LINK.effective_end_date
               AND entry.effective_start_date <= payact.date_earned
               AND entry.effective_end_date >=
                      DECODE
                         (ELEMENT.proration_group_id,
                          NULL, payact.date_earned,
                          pay_interpreter_pkg.prorate_start_date
                                                 (assact.assignment_action_id,
                                                  ELEMENT.proration_group_id
                                                 )

                         )
Best Blogger TemplatesBest Blogger Tips
Best Blogger Gadgets
Posted by Raju Chinthapatla at 12:41:00 AM
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect