APPS.PAY_IN_MED_WEB_ADI SQL Statements

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

Line: 22

    SELECT FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
    FROM   dual;

Line: 70

        ,P_LAST_UPDATED_DATE            IN DATE     DEFAULT NULL
 	,P_ASSIGNMENT_ID                IN NUMBER
        ,P_EMPLOYEE_ID                  IN NUMBER
        ,P_EMPLOYEE_NAME                IN VARCHAR2
        ,P_ASSIGNMENT_EXTRA_INFO_ID     IN NUMBER
        ,P_ENTRY_DATE                   IN DATE   DEFAULT NULL
        )
IS

   CURSOR c_element_name(p_business_group_id NUMBER)
   IS
   SELECT  hoi.org_information1
          ,hoi.org_information2
   FROM    hr_organization_information hoi
   WHERE   hoi.organization_id = p_business_group_id
   AND     org_information_context='PER_IN_REIMBURSE_ELEMENTS';

Line: 94

   SELECT types.element_type_id
         ,link.element_link_id
   FROM   per_assignments_f assgn
        , pay_element_links_f link
        , pay_element_types_f types
   WHERE assgn.assignment_id  = p_assignment_id
   AND   link.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
                                                                ,P_ENTRY_DATE
                                                                ,types.element_type_id
                                                                )
   AND   (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
   AND   link.business_group_id = assgn.business_group_id
   AND   link.element_type_id = types.element_type_id
   AND   types.element_type_id = p_element_name
   AND   p_effective_date BETWEEN assgn.effective_start_date AND assgn.effective_end_date
   AND   p_effective_date BETWEEN link.effective_start_date  AND link.effective_end_date
   AND   p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date;

Line: 117

   SELECT   inputs.name                   name
          , inputs.input_value_id         id
	  , inputs.default_value          value
   FROM     pay_element_types_f types
          , pay_input_values_f inputs
   WHERE    types.element_type_id = p_element_type_id
   AND      inputs.element_type_id = types.element_type_id
   AND      p_effective_date BETWEEN types.effective_start_date  AND types.effective_end_date
   AND      p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
   ORDER BY inputs.display_sequence;

Line: 133

   SELECT object_version_number
          ,effective_start_date
   FROM   pay_element_entries_f
   WHERE  element_entry_id = p_element_entryid;

Line: 141

   SELECT screen_entry_value
   FROM   pay_element_entry_values_f
   WHERE  element_entry_id = p_element_entryid
   AND    input_value_id   = p_input;

Line: 150

   SELECT pee.element_entry_id
   FROM   pay_element_entries_f pee
   WHERE  pee.element_type_id = p_element_type_id
   AND    pee.assignment_id = p_assignment_id
   AND    p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;

Line: 158

   SELECT element_type_id
   FROM   pay_element_entries_f
   WHERE  element_entry_id = p_element_entryid;

Line: 164

   SELECT pae.aei_information7, pae.aei_information11
   FROM   per_assignment_extra_info pae
   WHERE  pae.assignment_extra_info_id = p_assignment_extra_info_id;

Line: 170

   SELECT element_name
   FROM   pay_element_types_f
   WHERE  element_type_id = p_element_type_id ;

Line: 239

          pay_in_utils.trace('p_last_updated_date',TO_CHAR (p_last_updated_date));

Line: 331

        pay_element_entry_api.update_element_entry
                 (p_datetrack_update_mode    => hr_api.g_correction
                 ,p_effective_date           => l_effective_start_date
                 ,p_business_group_id        => l_business_group_id
                 ,p_element_entry_id         => p_element_entry_id
                 ,p_object_version_number    => l_object_version_no
                 ,p_input_value_id1          => l_input_values_rec(1).input_value_id
                 ,p_input_value_id2          => l_input_values_rec(2).input_value_id
                 ,p_entry_value1             => l_approved_bill_amount
                 ,p_entry_value2             => l_input_values_rec(2).value
                 ,p_effective_start_date     => l_start_date
                 ,p_effective_end_date       => l_end_date
                 ,p_update_warning           => l_warnings
                 );

Line: 403

        pay_element_entry_api.update_element_entry
                 (p_datetrack_update_mode    => hr_api.g_correction
                 ,p_effective_date           => l_ben_start_date
                 ,p_business_group_id        => l_business_group_id
                 ,p_element_entry_id         => l_ben_entry_id
                 ,p_object_version_number    => l_ben_version_no
                 ,p_input_value_id1          => l_ben_input_values_rec(1).input_value_id
                 ,p_input_value_id2          => l_ben_input_values_rec(2).input_value_id
                 ,p_input_value_id3          => l_ben_input_values_rec(3).input_value_id
                 ,p_input_value_id4          => l_ben_input_values_rec(4).input_value_id
                 ,p_input_value_id5          => l_ben_input_values_rec(5).input_value_id
                 ,p_input_value_id6          => l_ben_input_values_rec(6).input_value_id
                 ,p_input_value_id7          => l_ben_input_values_rec(7).input_value_id
                 ,p_input_value_id8          => l_ben_input_values_rec(8).input_value_id
                 ,p_input_value_id9          => l_ben_input_values_rec(9).input_value_id
                 ,p_entry_value1             => l_ben_input_values_rec(1).value
                 ,p_entry_value2             => l_ben_input_values_rec(2).value
                 ,p_entry_value3             => l_approved_bill_amount
                 ,p_entry_value4             => l_ben_input_values_rec(4).value
                 ,p_entry_value5             => l_entry_information5
                 ,p_entry_value6             => l_ben_input_values_rec(6).value
                 ,p_entry_value7             => l_entry_information7
                 ,p_entry_value8             => l_ben_input_values_rec(8).value
                 ,p_entry_value9             => l_ben_input_values_rec(9).value
		 ,p_effective_start_date     => l_start_date
                 ,p_effective_end_date       => l_end_date
                 ,p_update_warning           => l_warnings
                 );

Line: 511

        pay_element_entry_api.update_element_entry
                 (p_datetrack_update_mode    => hr_api.g_correction
                 ,p_effective_date           => l_effective_start_date
                 ,p_business_group_id        => l_business_group_id
                 ,p_element_entry_id         => l_element_entry_id
                 ,p_object_version_number    => l_object_version_no
                 ,p_input_value_id1          => l_input_values_rec(1).input_value_id
                 ,p_input_value_id2          => l_input_values_rec(2).input_value_id
                 ,p_entry_value1             => l_approved_bill_amount
                 ,p_entry_value2             => l_input_values_rec(2).value
                 ,p_effective_start_date     => l_start_date
                 ,p_effective_end_date       => l_end_date
                 ,p_update_warning           => l_warnings
                 );

Line: 582

        pay_element_entry_api.update_element_entry
                 (p_datetrack_update_mode    => hr_api.g_correction
                 ,p_effective_date           => l_ben_start_date
                 ,p_business_group_id        => l_business_group_id
                 ,p_element_entry_id         => l_ben_entry_id
                 ,p_object_version_number    => l_ben_version_no
                 ,p_input_value_id1          => l_ben_input_values_rec(1).input_value_id
                 ,p_input_value_id2          => l_ben_input_values_rec(2).input_value_id
                 ,p_input_value_id3          => l_ben_input_values_rec(3).input_value_id
                 ,p_input_value_id4          => l_ben_input_values_rec(4).input_value_id
                 ,p_input_value_id5          => l_ben_input_values_rec(5).input_value_id
                 ,p_input_value_id6          => l_ben_input_values_rec(6).input_value_id
                 ,p_input_value_id7          => l_ben_input_values_rec(7).input_value_id
                 ,p_input_value_id8          => l_ben_input_values_rec(8).input_value_id
                 ,p_input_value_id9          => l_ben_input_values_rec(9).input_value_id
                 ,p_entry_value1             => l_ben_input_values_rec(1).value
                 ,p_entry_value2             => l_ben_input_values_rec(2).value
                 ,p_entry_value3             => l_approved_bill_amount
                 ,p_entry_value4             => l_ben_input_values_rec(4).value
                 ,p_entry_value5             => l_entry_information5
                 ,p_entry_value6             => l_ben_input_values_rec(6).value
                 ,p_entry_value7             => l_entry_information7
                 ,p_entry_value8             => l_ben_input_values_rec(8).value
                 ,p_entry_value9             => l_ben_input_values_rec(9).value
		 ,p_effective_start_date     => l_start_date
                 ,p_effective_end_date       => l_end_date
                 ,p_update_warning           => l_warnings
                 );

Line: 747

    UPDATE per_assignment_extra_info
    SET    AEI_INFORMATION7      = p_approved_bill_amount
          ,AEI_INFORMATION9      = p_employer_remarks
          ,AEI_INFORMATION10     = nvl(l_bill_entry_id,l_element_entry_id)
          ,AEI_INFORMATION11     = l_ben_entry_id
    WHERE  ASSIGNMENT_EXTRA_INFO_ID = p_assignment_extra_info_id ;

Line: 788

   SELECT   inputs.name                   name
          , inputs.input_value_id         id
	  , inputs.default_value          value
   FROM     pay_element_types_f types
          , pay_input_values_f inputs
   WHERE    types.element_type_id = p_element_type_id
   AND      inputs.element_type_id = types.element_type_id
   AND      p_effective_date BETWEEN types.effective_start_date  AND types.effective_end_date
   AND      p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
   ORDER BY inputs.display_sequence;

Line: 803

   SELECT object_version_number
   FROM   pay_element_entries_f
   WHERE  element_entry_id = p_element_entryid;

Line: 810

   SELECT element_type_id
   FROM   pay_element_entries_f
   WHERE  element_entry_id = p_element_entryid;

Line: 904

        pay_element_entry_api.update_element_entry
                 (p_datetrack_update_mode    => hr_api.g_correction
                 ,p_effective_date           => P_effective_start_date
                 ,p_business_group_id        => l_business_group_id
                 ,p_element_entry_id         => p_element_entry_id
                 ,p_object_version_number    => l_object_version_no
                 ,p_input_value_id1          => l_input_values_rec(1).input_value_id
                 ,p_input_value_id2          => l_input_values_rec(2).input_value_id
                 ,p_input_value_id3          => l_input_values_rec(3).input_value_id
                 ,p_input_value_id4          => l_input_values_rec(4).input_value_id
                 ,p_input_value_id5          => l_input_values_rec(5).input_value_id
                 ,p_input_value_id6          => l_input_values_rec(6).input_value_id
                 ,p_input_value_id7          => l_input_values_rec(7).input_value_id
                 ,p_input_value_id8          => l_input_values_rec(8).input_value_id
                 ,p_input_value_id9          => l_input_values_rec(9).input_value_id
                 ,p_entry_value1             => l_input_values_rec(1).value
                 ,p_entry_value2             => l_input_values_rec(2).value
                 ,p_entry_value3             => P_Benefit
                 ,p_entry_value4             => l_input_values_rec(4).value
                 ,p_entry_value5             => P_AnnualLimit
                 ,p_entry_value6             => l_input_values_rec(6).value
                 ,p_entry_value7             => P_Add_to_NetPay
                 ,p_entry_value8             => l_input_values_rec(8).value
                 ,p_entry_value9             => l_input_values_rec(9).value
                 ,p_effective_start_date     => l_start_date
                 ,p_effective_end_date       => l_end_date
                 ,p_update_warning           => l_warnings
                 );

Line: 973

,P_LAST_UPDATED_DATE            IN DATE
,P_ASSIGNMENT_ID                IN NUMBER
,P_EMPLOYEE_ID                  IN NUMBER
,P_ASSIGNMENT_EXTRA_INFO_ID     IN NUMBER
,P_ENTRY_DATE                   IN DATE   DEFAULT NULL
)
IS
   CURSOR c_element_name(p_business_group_id NUMBER)
   IS
   SELECT  hoi.org_information3
   FROM    hr_organization_information hoi
   WHERE   hoi.organization_id = p_business_group_id
   AND     org_information_context='PER_IN_REIMBURSE_ELEMENTS';

Line: 995

   SELECT types.element_type_id
         ,link.element_link_id
   FROM   per_assignments_f assgn
        , pay_element_links_f link
        , pay_element_types_f types
   WHERE assgn.assignment_id  = p_assignment_id
   AND   link.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
                                                                ,P_ENTRY_DATE
                                                                ,types.element_type_id
                                                                )
   AND   (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
   AND   link.business_group_id = assgn.business_group_id
   AND   link.element_type_id = types.element_type_id
   AND   types.element_type_id = p_element_name
   AND   p_effective_date BETWEEN assgn.effective_start_date AND assgn.effective_end_date
   AND   p_effective_date BETWEEN link.effective_start_date  AND link.effective_end_date
   AND   p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date;

Line: 1018

   SELECT   inputs.name                   name
          , inputs.input_value_id         id
	  , inputs.default_value          value
   FROM     pay_element_types_f types
          , pay_input_values_f inputs
   WHERE    types.element_type_id = p_element_type_id
   AND      inputs.element_type_id = types.element_type_id
   AND      p_effective_date BETWEEN types.effective_start_date  AND types.effective_end_date
   AND      p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
   ORDER BY inputs.display_sequence;

Line: 1034

   SELECT object_version_number
          ,effective_start_date
   FROM   pay_element_entries_f
   WHERE  element_entry_id = p_element_entryid;

Line: 1042

   SELECT screen_entry_value
   FROM   pay_element_entry_values_f
   WHERE  element_entry_id = p_element_entryid
   AND    input_value_id   = p_input;

Line: 1051

   SELECT pee.element_entry_id
   FROM   pay_element_entries_f pee
   WHERE  pee.element_type_id = p_element_type_id
   AND    pee.assignment_id = p_assignment_id
   AND    TO_CHAR(p_effective_date,'RRRR') = TO_CHAR(pee.effective_start_date,'RRRR') ;

Line: 1059

   SELECT element_type_id
   FROM   pay_element_entries_f
   WHERE  element_entry_id = p_element_entryid;

Line: 1065

   SELECT pae.aei_information9,
          pae.aei_information10
   FROM   per_assignment_extra_info pae
   WHERE  pae.assignment_extra_info_id = p_assignment_extra_info_id;

Line: 1072

   SELECT element_name
   FROM   pay_element_types_f
   WHERE  element_type_id = p_element_type_id ;

Line: 1138

          pay_in_utils.trace('P_LAST_UPDATED_DATE',TO_CHAR (P_LAST_UPDATED_DATE));

Line: 1166

         SELECT 1 INTO l_session FROM fnd_sessions WHERE SESSION_ID = USERENV('SESSIONID') AND ROWNUM=1;

Line: 1169

         INSERT INTO fnd_sessions(session_id,effective_date) VALUES (USERENV('SESSIONID'),P_ENTRY_DATE);

Line: 1232

        pay_element_entry_api.update_element_entry
                 (p_datetrack_update_mode    => hr_api.g_correction
                 ,p_effective_date           => l_effective_start_date
                 ,p_business_group_id        => l_business_group_id
                 ,p_element_entry_id         => p_element_entry_id
                 ,p_object_version_number    => l_object_version_no
                 ,p_input_value_id1          => l_input_values_rec(1).input_value_id
                 ,p_input_value_id2          => l_input_values_rec(2).input_value_id
                 ,p_input_value_id3          => l_input_values_rec(3).input_value_id
                 ,p_input_value_id4          => l_input_values_rec(4).input_value_id
                 ,p_input_value_id5          => l_input_values_rec(5).input_value_id
                 ,p_input_value_id6          => l_input_values_rec(6).input_value_id
                 ,p_input_value_id7          => l_input_values_rec(7).input_value_id
                 ,p_input_value_id8          => l_input_values_rec(8).input_value_id
                 ,p_input_value_id9          => l_input_values_rec(9).input_value_id
                 ,p_entry_value1             => l_input_values_rec(1).value
                 ,p_entry_value2             => l_input_values_rec(2).value
                 ,p_entry_value3             => l_submitted
                 ,p_entry_value4             => P_LTCBLOCK
                 ,p_entry_value5             => l_exempted_amount
                 ,p_entry_value6             => P_CARRY_OVER
                 ,p_entry_value7             => l_input_values_rec(7).value
                 ,p_entry_value8             => l_input_values_rec(8).value
                 ,p_entry_value9             => l_input_values_rec(9).value
                 ,p_effective_start_date     => l_start_date
                 ,p_effective_end_date       => l_end_date
                 ,p_update_warning           => l_warnings
                 );

Line: 1347

        pay_element_entry_api.update_element_entry
                 (p_datetrack_update_mode    => hr_api.g_correction
                 ,p_effective_date           => l_effective_start_date
                 ,p_business_group_id        => l_business_group_id
                 ,p_element_entry_id         => l_element_entry_id
                 ,p_object_version_number    => l_object_version_no
                 ,p_input_value_id1          => l_input_values_rec(1).input_value_id
                 ,p_input_value_id2          => l_input_values_rec(2).input_value_id
                 ,p_input_value_id3          => l_input_values_rec(3).input_value_id
                 ,p_input_value_id4          => l_input_values_rec(4).input_value_id
                 ,p_input_value_id5          => l_input_values_rec(5).input_value_id
                 ,p_input_value_id6          => l_input_values_rec(6).input_value_id
                 ,p_input_value_id7          => l_input_values_rec(7).input_value_id
                 ,p_input_value_id8          => l_input_values_rec(8).input_value_id
                 ,p_input_value_id9          => l_input_values_rec(9).input_value_id
                 ,p_entry_value1             => l_input_values_rec(1).value
                 ,p_entry_value2             => l_input_values_rec(2).value
                 ,p_entry_value3             => l_submitted
                 ,p_entry_value4             => P_LTCBLOCK
                 ,p_entry_value5             => l_exempted_amount
                 ,p_entry_value6             => P_CARRY_OVER
                 ,p_entry_value7             => l_input_values_rec(7).value
                 ,p_entry_value8             => l_input_values_rec(8).value
                 ,p_entry_value9             => l_input_values_rec(9).value
                 ,p_effective_start_date     => l_start_date
                 ,p_effective_end_date       => l_end_date
                 ,p_update_warning           => l_warnings
                 );

Line: 1466

      UPDATE per_assignment_extra_info
      SET    AEI_INFORMATION10      = P_EXEMPTED
            ,AEI_INFORMATION6      =  P_ER_COMMENTS
            ,AEI_INFORMATION11     =  nvl(l_element_entry_id,p_element_entry_id)
	    ,aei_information18     = P_CARRY_OVER
      WHERE  ASSIGNMENT_EXTRA_INFO_ID = p_assignment_extra_info_id ;

Line: 1489

PROCEDURE update_ltc_element
(
 p_employee_number          IN VARCHAR2
,p_full_name                IN VARCHAR2
,p_start_date               IN DATE
,p_effective_end_date       IN DATE DEFAULT NULL
,p_fare		            IN NUMBER
,p_blockYr		    IN VARCHAR2
,p_carry		    IN VARCHAR2
,p_benefit		    IN NUMBER
,p_assignment_id            IN NUMBER
,p_element_entry_id         IN NUMBER  DEFAULT NULL
)
IS




   CURSOR c_input_rec(p_element_type_id         NUMBER
                     ,p_effective_date          DATE
                     )
   IS
   SELECT   inputs.name                   name
          , inputs.input_value_id         id
	  , inputs.default_value          value
   FROM     pay_element_types_f types
          , pay_input_values_f inputs
   WHERE    types.element_type_id = p_element_type_id
   AND      inputs.element_type_id = types.element_type_id
   --AND      inputs.legislation_code = 'IN'
   AND      p_effective_date BETWEEN types.effective_start_date  AND types.effective_end_date
   AND      p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
   ORDER BY inputs.display_sequence;

Line: 1528

   SELECT object_version_number
   FROM   pay_element_entries_f
   WHERE  element_entry_id = p_element_entryid;

Line: 1536

   SELECT element_type_id
   FROM   pay_element_entries_f
   WHERE  element_entry_id = p_element_entryid;

Line: 1567

     l_procedure := g_package ||'update_ltc_element';

Line: 1599

         SELECT 1 INTO l_session FROM fnd_sessions WHERE SESSION_ID = USERENV('SESSIONID') AND ROWNUM=1;

Line: 1602

         INSERT INTO fnd_sessions(session_id,effective_date) VALUES (USERENV('SESSIONID'),p_start_date);

Line: 1643

        pay_element_entry_api.update_element_entry
                 (p_datetrack_update_mode    => hr_api.g_correction
                 ,p_effective_date           => p_start_date
                 ,p_business_group_id        => l_business_group_id
                 ,p_element_entry_id         => p_element_entry_id
                 ,p_object_version_number    => l_object_version_no
                 ,p_input_value_id1          => l_input_values_rec(1).input_value_id
                 ,p_input_value_id2          => l_input_values_rec(2).input_value_id
                 ,p_input_value_id3          => l_input_values_rec(3).input_value_id
                 ,p_input_value_id4          => l_input_values_rec(4).input_value_id
                 ,p_input_value_id5          => l_input_values_rec(5).input_value_id
                 ,p_input_value_id6          => l_input_values_rec(6).input_value_id
                 ,p_input_value_id7          => l_input_values_rec(7).input_value_id
                 ,p_input_value_id8          => l_input_values_rec(8).input_value_id
                 ,p_input_value_id9          => l_input_values_rec(9).input_value_id
                 ,p_entry_value1             => l_input_values_rec(1).value
                 ,p_entry_value2             => l_input_values_rec(2).value
                 ,p_entry_value3             => p_fare
                 ,p_entry_value4             => p_blockYr
                 ,p_entry_value5             => p_benefit
                 ,p_entry_value6             => p_carry
                 ,p_entry_value7             => l_input_values_rec(7).value
                 ,p_entry_value8             => l_input_values_rec(8).value
                 ,p_entry_value9             => l_input_values_rec(9).value
                 ,p_effective_start_date     => l_start_date
                 ,p_effective_end_date       => l_end_date
                 ,p_update_warning           => l_warnings
                 );

Line: 1675

UPDATE per_assignment_extra_info
    SET    AEI_INFORMATION18     = p_carry
   where AEI_INFORMATION11     = p_element_entry_id
   and assignment_id = p_assignment_id;

Line: 1686

END update_ltc_element;