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.

Monday, February 25, 2013

HRMS Employee with Elements Query

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 11:28:00 PM

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