APPS.PER_ES_SS_REP_DYN_TRG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 9

SELECT  assignment_id
FROM    pay_element_entries_f
WHERE   element_entry_id = p_element_entry_id;

Line: 25

SELECT business_group_id
FROM   per_all_assignments_f paf
      ,pay_element_entries_f peef
WHERE  peef.element_entry_id = p_element_entry_id
AND    peef.assignment_id = paf.assignment_id;

Line: 41

PROCEDURE asg_check_update(p_assignment_id                            NUMBER,
                           p_assignment_type                          VARCHAR2,
                           p_effective_start_date                     DATE,
                           p_effective_end_date                       DATE,
                           p_asg_status_type_id                       NUMBER,
                           p_employment_category                      VARCHAR2,
     	                     p_soft_coding_keyflex_id                   NUMBER,
                           p_primary_flag                             VARCHAR) AS

CURSOR csr_asg_status IS
SELECT assignment_extra_info_id
      ,object_version_number
      ,aei_information2          last_reported_date
      ,aei_information3          event
      ,nvl(aei_information4,'X') value
      ,aei_information6          action_type
      ,aei_information7          first_change_date
FROM   per_assignment_extra_info
WHERE  assignment_id = p_assignment_id
AND    information_type = 'ES_SS_REP'
AND    aei_information5 <> 'Y';

Line: 64

SELECT sck.segment5
FROM   hr_soft_coding_keyflex sck
WHERE  sck.soft_coding_keyflex_id = p_soft_coding_keyflex_id;

Line: 69

SELECT per_system_status
FROM   per_assignment_status_types
WHERE  assignment_status_type_id = p_asg_status_type_id;

Line: 94

               UPDATE per_assignment_extra_info
               SET    aei_information5         = 'Y'
                     ,aei_information7         =  fnd_date.date_to_canonical(p_effective_start_date)
               WHERE  assignment_id            =  p_assignment_id
               AND    aei_information3         = 'AS'
               AND    aei_information_category = 'ES_SS_REP'
               AND    object_version_number    =  csr_extra_info.object_version_number;

Line: 107

               UPDATE per_assignment_extra_info
               SET    aei_information5           = 'Y'
                     ,aei_information7           =  fnd_date.date_to_canonical(p_effective_start_date-1)
		                 ,aei_information4           =  p_asg_status_type_id
               WHERE  assignment_id              =  p_assignment_id
               AND    aei_information3           = 'TS'
               AND    aei_information_category   = 'ES_SS_REP'
               AND    object_version_number      =  csr_extra_info.object_version_number;

Line: 121

               UPDATE per_assignment_extra_info
               SET    aei_information5           = 'Y'
                     ,aei_information7           = fnd_date.date_to_canonical(p_effective_start_date)
               WHERE  assignment_id              = p_assignment_id
               AND    aei_information3           = 'EC'
               AND    aei_information_category   = 'ES_SS_REP'
               AND    object_version_number      = csr_extra_info.object_version_number;

Line: 134

               UPDATE per_assignment_extra_info
               SET    aei_information5           = 'Y'
                     ,aei_information7           = fnd_date.date_to_canonical(p_effective_start_date)
               WHERE  assignment_id              = p_assignment_id
               AND    aei_information3           = 'CG'
               AND    aei_information_category   = 'ES_SS_REP'
               AND    object_version_number      = csr_extra_info.object_version_number;

Line: 148

END asg_check_update;

Line: 152

PROCEDURE asg_check_insert( p_assignment_id                           NUMBER,
                            p_assignment_type                         VARCHAR2,
                            p_effective_start_date                    DATE,
                            p_effective_end_date                      DATE,
                            p_asg_status_type_id                      NUMBER,
                            p_employment_category                     VARCHAR2,
 		                        p_soft_coding_keyflex_id                  NUMBER,
                            p_primary_flag                            VARCHAR) IS
CURSOR csr_check_asg IS
SELECT assignment_id
FROM   per_assignment_extra_info
WHERE  assignment_id  =  p_assignment_id;

Line: 173

            /* Call the update procedure to update the report type */
            asg_check_update(p_assignment_id,
                             p_assignment_type,
                             p_effective_start_date,
                             p_effective_end_date,
                             p_asg_status_type_id,
                             p_employment_category,
			                       p_soft_coding_keyflex_id,
                             p_primary_flag);

Line: 183

           /* Insert the records directly into the table because the api used to insert the values uses savepoint*/
           /* savepoint cannot be used in triggers */
           INSERT INTO per_assignment_extra_info
                    (assignment_extra_info_id,
	                   assignment_id,
	                   information_type,
	                   aei_information_category,
	                   aei_information2,
	                   aei_information3,
                     aei_information4,
	                   aei_information5,
	                   aei_information6,
	                   aei_information7,
                     object_version_number
                      )
           VALUES
                    (per_assignment_extra_info_s.nextval,
	                   p_assignment_id,
	                  'ES_SS_REP',
	                  'ES_SS_REP',
	                   fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
	                  'AS',
	                   p_asg_status_type_id,
	                  'Y',
	                  'I',
	                   fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
	                   1
                      );

Line: 211

           /* Inserting the Termination Status as One */
           INSERT INTO per_assignment_extra_info
                    (assignment_extra_info_id,
                     assignment_id,
                     information_type,
                     aei_information_category,
	                   aei_information2,
	                   aei_information3,
	                   aei_information4,
	                   aei_information5,
      	             aei_information6,
	                   aei_information7,
                     object_version_number
                      )
           VALUES
                    (per_assignment_extra_info_s.nextval,
	                   p_assignment_id,
	                  'ES_SS_REP',
           	        'ES_SS_REP',
	                   fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
	                  'TS',
	                   p_asg_status_type_id,
	                  'N',
	                  'U',
	                   fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
	                   1
                     );

Line: 238

          /* INSERT INTO per_assignment_extra_info
                   (assignment_extra_info_id,
	                  assignment_id,
	                  information_type,
	                  aei_information_category,
	                  aei_information2,
	                  aei_information3,
	                  aei_information4,
	                  aei_information5,
	                  aei_information6,
	                  aei_information7,
                    object_version_number
                   )
           VALUES
                  (per_assignment_extra_info_s.nextval,
 	                 p_assignment_id,
	                'ES_SS_REP',
 	                'ES_SS_REP',
	                 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
	                'EC',
	                 p_employment_category,
	                'Y',
	                'I',
	                 fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
	                 1
                    );

Line: 265

           INSERT INTO per_assignment_extra_info
                  (assignment_extra_info_id,
	                 assignment_id,
	                 information_type,
	                 aei_information_category,
	                 aei_information2,
	                 aei_information3,
	                 aei_information4,
	                 aei_information5,
	                 aei_information6,
	                 aei_information7,
                   object_version_number
                    )
           VALUES
                 (per_assignment_extra_info_s.nextval,
	                p_assignment_id,
	               'ES_SS_REP',
	               'ES_SS_REP',
	                fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
	               'CG',
	                NULL,
	               'Y',
	               'I',
	                fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
	                1
                  );

Line: 297

END asg_check_insert;

Line: 301

PROCEDURE element_check_insert(p_element_entry_id         NUMBER,
                               p_effective_start_date     DATE,
                               p_effective_end_date       DATE,
                               p_epigraph_code            VARCHAR2,
                               p_input_value_id           NUMBER) AS

CURSOR  csr_chk_element_eit(p_assignment_id NUMBER) IS
SELECT  assignment_extra_info_id
FROM    per_assignment_extra_info
WHERE   assignment_id    = p_assignment_id
AND     information_type = 'ES_SS_REP'
AND     aei_information3 IN ('EP','EC');

Line: 315

SELECT input_value_id
FROM   pay_input_values_f piv
WHERE  piv.name         = p_name
AND    legislation_code = 'ES';

Line: 322

SELECT paf.primary_flag, paf.assignment_type
FROM   per_all_assignments_f paf
WHERE  paf.assignment_id = p_assignment_id
AND    p_effective_start_date BETWEEN paf.effective_start_date
                              AND     paf.effective_end_date;

Line: 348

            element_check_update(p_element_entry_id,
                                 p_effective_start_date,
                                 p_effective_end_date,
                                 p_epigraph_code,
                                 p_input_value_id);

Line: 354

           /*inserting for EPIGRAPH CODE EP*/
            INSERT INTO per_assignment_extra_info
                    (assignment_extra_info_id,
  	                 assignment_id,
	                   information_type,
                     aei_information_category,
                     aei_information2,
                     aei_information3,
                     aei_information4,
	                   aei_information5,
                     aei_information6,
                     aei_information7,
                     object_version_number
                    )
            VALUES
                   (per_assignment_extra_info_s.nextval,
                    l_assg_id,
                   'ES_SS_REP',
                   'ES_SS_REP',
  	                fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
  	               'EP',
	                  NULL,
	                 'Y',
	                 'I',
	                  fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
	                  1
                    );

Line: 381

           /*Inserting for contract key change*/
            INSERT INTO per_assignment_extra_info
                    (assignment_extra_info_id,
  	                 assignment_id,
	                   information_type,
                     aei_information_category,
                     aei_information2,
                     aei_information3,
                     aei_information4,
	                   aei_information5,
                     aei_information6,
                     aei_information7,
                     object_version_number
                    )
            VALUES
                   (per_assignment_extra_info_s.nextval,
                    l_assg_id,
                   'ES_SS_REP',
                   'ES_SS_REP',
  	                fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
  	               'EC',
	                  NULL,
	                 'Y',
	                 'I',
	                  fnd_date.date_to_canonical(P_EFFECTIVE_START_DATE),
	                  1
                    );

Line: 412

END element_check_insert;

Line: 416

PROCEDURE  element_check_update(p_element_entry_id            NUMBER,
                                p_effective_start_date        DATE,
                                p_effective_end_date          DATE,
                                p_epigraph_code               VARCHAR2,
                                p_input_value_id              NUMBER) AS

CURSOR  csr_get_eit_value(p_assignment_id NUMBER
                         ,p_event_type    VARCHAR2) IS
SELECT  assignment_extra_info_id
       ,object_version_number
       ,aei_information2          last_reported_date
       ,nvl(aei_information4,'X') value
	     ,aei_information6          action_type
	     ,aei_information7          last_changed_date
FROM   per_assignment_extra_info
WHERE  assignment_id     =   p_assignment_id
AND    information_type  =  'ES_SS_REP'
AND    aei_information3  =   p_event_type
AND    aei_information5  <> 'Y';

Line: 437

SELECT input_value_id
FROM   pay_input_values_f piv
WHERE  piv.name         = p_name
AND    legislation_code = 'ES';

Line: 444

SELECT paf.primary_flag, paf.assignment_type
FROM   per_all_assignments_f paf
WHERE  paf.assignment_id = p_assignment_id
AND    p_effective_start_date BETWEEN paf.effective_start_date
                              AND     paf.effective_end_date;

Line: 469

                         UPDATE per_assignment_extra_info
		                 SET    aei_information5         = 'Y'
			                     ,aei_information7         = fnd_date.date_to_canonical(p_effective_start_date)
		                 WHERE  assignment_id            = l_assignment_id
		                 AND    aei_information_category = 'ES_SS_REP'
		                 AND    aei_information3         = 'EP'
		                 AND    object_version_number    = csr_extra_info.object_version_number;

Line: 495

                        UPDATE per_assignment_extra_info
		                SET    aei_information5         = 'Y'
			                    ,aei_information7         = fnd_date.date_to_canonical(p_effective_start_date)
		                WHERE  assignment_id            = l_assignment_id
		                AND    aei_information_category = 'ES_SS_REP'
		                AND    aei_information3         = 'EC'
		                AND    object_version_number    = csr_extra_info.object_version_number;

Line: 507

END element_check_update;