eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HR_COLLECTIVE_AGREEMENT_API SQL Statements

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

Line: 62


    SELECT start_date,
	       end_date
	  FROM ben_yr_perd
	 WHERE start_date = l_start_of_year
	   AND end_date   = l_end_of_year
	   AND business_group_id  = p_business_group_id;  --Bug fix:3648527

Line: 168


    SELECT yr_perd_id,
	   TRUNC(start_date) start_date,
	   TRUNC(end_date) end_date
      FROM ben_yr_perd
     WHERE business_group_id  = p_business_group_id  --Bug fix:3648527
       AND end_date            <= l_ten_year_date
     ORDER BY start_date ASC;

Line: 320


    SELECT pl_typ_id
      FROM ben_pl_typ_f
     WHERE name = 'Collective Agreement Plan Type'
	   AND business_group_id = p_business_group_id
       AND l_effective_date BETWEEN effective_start_date
                                AND effective_end_date;

Line: 682


procedure update_collective_agreement
  (p_validate                       in  boolean   default false
  ,p_collective_agreement_id        in  number
  ,p_business_group_id              in  number    default hr_api.g_number
  ,p_object_version_number          in out nocopy number
  ,p_name                           in  varchar2  default hr_api.g_varchar2
  ,p_status                         in  varchar2  default hr_api.g_varchar2
  ,p_cag_number                     in  number    default hr_api.g_number
  ,p_description                    in  varchar2  default hr_api.g_varchar2
  ,p_start_date                     in  date      default hr_api.g_date
  ,p_end_date                       in  date      default hr_api.g_date
  ,p_employer_organization_id       in  number    default hr_api.g_number
  ,p_employer_signatory             in  varchar2  default hr_api.g_varchar2
  ,p_bargaining_organization_id     in  number    default hr_api.g_number
  ,p_bargaining_unit_signatory      in  varchar2  default hr_api.g_varchar2
  ,p_jurisdiction                   in  varchar2  default hr_api.g_varchar2
  ,p_authorizing_body               in  varchar2  default hr_api.g_varchar2
  ,p_authorized_date                in  date      default hr_api.g_date
  ,p_cag_information_category       in  varchar2  default hr_api.g_varchar2
  ,p_cag_information1               in  varchar2  default hr_api.g_varchar2
  ,p_cag_information2               in  varchar2  default hr_api.g_varchar2
  ,p_cag_information3               in  varchar2  default hr_api.g_varchar2
  ,p_cag_information4               in  varchar2  default hr_api.g_varchar2
  ,p_cag_information5               in  varchar2  default hr_api.g_varchar2
  ,p_cag_information6               in  varchar2  default hr_api.g_varchar2
  ,p_cag_information7               in  varchar2  default hr_api.g_varchar2
  ,p_cag_information8               in  varchar2  default hr_api.g_varchar2
  ,p_cag_information9               in  varchar2  default hr_api.g_varchar2
  ,p_cag_information10              in  varchar2  default hr_api.g_varchar2
  ,p_cag_information11              in  varchar2  default hr_api.g_varchar2
  ,p_cag_information12              in  varchar2  default hr_api.g_varchar2
  ,p_cag_information13              in  varchar2  default hr_api.g_varchar2
  ,p_cag_information14              in  varchar2  default hr_api.g_varchar2
  ,p_cag_information15              in  varchar2  default hr_api.g_varchar2
  ,p_cag_information16              in  varchar2  default hr_api.g_varchar2
  ,p_cag_information17              in  varchar2  default hr_api.g_varchar2
  ,p_cag_information18              in  varchar2  default hr_api.g_varchar2
  ,p_cag_information19              in  varchar2  default hr_api.g_varchar2
  ,p_cag_information20              in  varchar2  default hr_api.g_varchar2
  ,p_attribute_category             in  varchar2  default hr_api.g_varchar2
  ,p_attribute1                     in  varchar2  default hr_api.g_varchar2
  ,p_attribute2                     in  varchar2  default hr_api.g_varchar2
  ,p_attribute3                     in  varchar2  default hr_api.g_varchar2
  ,p_attribute4                     in  varchar2  default hr_api.g_varchar2
  ,p_attribute5                     in  varchar2  default hr_api.g_varchar2
  ,p_attribute6                     in  varchar2  default hr_api.g_varchar2
  ,p_attribute7                     in  varchar2  default hr_api.g_varchar2
  ,p_attribute8                     in  varchar2  default hr_api.g_varchar2
  ,p_attribute9                     in  varchar2  default hr_api.g_varchar2
  ,p_attribute10                    in  varchar2  default hr_api.g_varchar2
  ,p_attribute11                    in  varchar2  default hr_api.g_varchar2
  ,p_attribute12                    in  varchar2  default hr_api.g_varchar2
  ,p_attribute13                    in  varchar2  default hr_api.g_varchar2
  ,p_attribute14                    in  varchar2  default hr_api.g_varchar2
  ,p_attribute15                    in  varchar2  default hr_api.g_varchar2
  ,p_attribute16                    in  varchar2  default hr_api.g_varchar2
  ,p_attribute17                    in  varchar2  default hr_api.g_varchar2
  ,p_attribute18                    in  varchar2  default hr_api.g_varchar2
  ,p_attribute19                    in  varchar2  default hr_api.g_varchar2
  ,p_attribute20                    in  varchar2  default hr_api.g_varchar2
  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc varchar2(72) := g_package||'update_collective_agreement';

Line: 756


  savepoint update_collective_agreement;

Line: 769


    hr_collective_agreement_bk2.update_collective_agreement_b
      (
       p_collective_agreement_id        =>  p_collective_agreement_id
      ,p_business_group_id              =>  p_business_group_id
      ,p_object_version_number          =>  p_object_version_number
      ,p_name                           =>  p_name
	  ,p_status                         =>  p_status
      ,p_cag_number                     =>  p_cag_number
      ,p_description                    =>  p_description
      ,p_start_date                     =>  p_start_date
      ,p_end_date                       =>  p_end_date
      ,p_employer_organization_id       =>  p_employer_organization_id
      ,p_employer_signatory             =>  p_employer_signatory
      ,p_bargaining_organization_id     =>  p_bargaining_organization_id
      ,p_bargaining_unit_signatory      =>  p_bargaining_unit_signatory
      ,p_jurisdiction                   =>  p_jurisdiction
      ,p_authorizing_body               =>  p_authorizing_body
      ,p_authorized_date                =>  p_authorized_date
      ,p_cag_information_category       =>  p_cag_information_category
      ,p_cag_information1               =>  p_cag_information1
      ,p_cag_information2               =>  p_cag_information2
      ,p_cag_information3               =>  p_cag_information3
      ,p_cag_information4               =>  p_cag_information4
      ,p_cag_information5               =>  p_cag_information5
      ,p_cag_information6               =>  p_cag_information6
      ,p_cag_information7               =>  p_cag_information7
      ,p_cag_information8               =>  p_cag_information8
      ,p_cag_information9               =>  p_cag_information9
      ,p_cag_information10              =>  p_cag_information10
      ,p_cag_information11              =>  p_cag_information11
      ,p_cag_information12              =>  p_cag_information12
      ,p_cag_information13              =>  p_cag_information13
      ,p_cag_information14              =>  p_cag_information14
      ,p_cag_information15              =>  p_cag_information15
      ,p_cag_information16              =>  p_cag_information16
      ,p_cag_information17              =>  p_cag_information17
      ,p_cag_information18              =>  p_cag_information18
      ,p_cag_information19              =>  p_cag_information19
      ,p_cag_information20              =>  p_cag_information20
      ,p_attribute_category             =>  p_attribute_category
      ,p_attribute1                     =>  p_attribute1
      ,p_attribute2                     =>  p_attribute2
      ,p_attribute3                     =>  p_attribute3
      ,p_attribute4                     =>  p_attribute4
      ,p_attribute5                     =>  p_attribute5
      ,p_attribute6                     =>  p_attribute6
      ,p_attribute7                     =>  p_attribute7
      ,p_attribute8                     =>  p_attribute8
      ,p_attribute9                     =>  p_attribute9
      ,p_attribute10                    =>  p_attribute10
      ,p_attribute11                    =>  p_attribute11
      ,p_attribute12                    =>  p_attribute12
      ,p_attribute13                    =>  p_attribute13
      ,p_attribute14                    =>  p_attribute14
      ,p_attribute15                    =>  p_attribute15
      ,p_attribute16                    =>  p_attribute16
      ,p_attribute17                    =>  p_attribute17
      ,p_attribute18                    =>  p_attribute18
      ,p_attribute19                    =>  p_attribute19
      ,p_attribute20                    =>  p_attribute20
      );

Line: 834


        (p_module_name => 'UPDATE_collective_agreement'
        ,p_hook_type   => 'BP'
        );

Line: 908


    hr_collective_agreement_bk2.update_collective_agreement_a
      (
       p_collective_agreement_id        =>  p_collective_agreement_id
      ,p_business_group_id              =>  p_business_group_id
      ,p_object_version_number          =>  l_object_version_number
      ,p_name                           =>  p_name
	  ,p_status                         =>  p_status
      ,p_cag_number                     =>  p_cag_number
      ,p_description                    =>  p_description
      ,p_start_date                     =>  p_start_date
      ,p_end_date                       =>  p_end_date
      ,p_employer_organization_id       =>  p_employer_organization_id
      ,p_employer_signatory             =>  p_employer_signatory
      ,p_bargaining_organization_id     =>  p_bargaining_organization_id
      ,p_bargaining_unit_signatory      =>  p_bargaining_unit_signatory
      ,p_jurisdiction                   =>  p_jurisdiction
      ,p_authorizing_body               =>  p_authorizing_body
      ,p_authorized_date                =>  p_authorized_date
      ,p_cag_information_category       =>  p_cag_information_category
      ,p_cag_information1               =>  p_cag_information1
      ,p_cag_information2               =>  p_cag_information2
      ,p_cag_information3               =>  p_cag_information3
      ,p_cag_information4               =>  p_cag_information4
      ,p_cag_information5               =>  p_cag_information5
      ,p_cag_information6               =>  p_cag_information6
      ,p_cag_information7               =>  p_cag_information7
      ,p_cag_information8               =>  p_cag_information8
      ,p_cag_information9               =>  p_cag_information9
      ,p_cag_information10              =>  p_cag_information10
      ,p_cag_information11              =>  p_cag_information11
      ,p_cag_information12              =>  p_cag_information12
      ,p_cag_information13              =>  p_cag_information13
      ,p_cag_information14              =>  p_cag_information14
      ,p_cag_information15              =>  p_cag_information15
      ,p_cag_information16              =>  p_cag_information16
      ,p_cag_information17              =>  p_cag_information17
      ,p_cag_information18              =>  p_cag_information18
      ,p_cag_information19              =>  p_cag_information19
      ,p_cag_information20              =>  p_cag_information20
      ,p_attribute_category             =>  p_attribute_category
      ,p_attribute1                     =>  p_attribute1
      ,p_attribute2                     =>  p_attribute2
      ,p_attribute3                     =>  p_attribute3
      ,p_attribute4                     =>  p_attribute4
      ,p_attribute5                     =>  p_attribute5
      ,p_attribute6                     =>  p_attribute6
      ,p_attribute7                     =>  p_attribute7
      ,p_attribute8                     =>  p_attribute8
      ,p_attribute9                     =>  p_attribute9
      ,p_attribute10                    =>  p_attribute10
      ,p_attribute11                    =>  p_attribute11
      ,p_attribute12                    =>  p_attribute12
      ,p_attribute13                    =>  p_attribute13
      ,p_attribute14                    =>  p_attribute14
      ,p_attribute15                    =>  p_attribute15
      ,p_attribute16                    =>  p_attribute16
      ,p_attribute17                    =>  p_attribute17
      ,p_attribute18                    =>  p_attribute18
      ,p_attribute19                    =>  p_attribute19
      ,p_attribute20                    =>  p_attribute20
      );

Line: 973


        (p_module_name => 'UPDATE_collective_agreement'
        ,p_hook_type   => 'AP'
        );

Line: 1002


    ROLLBACK TO update_collective_agreement;

Line: 1016


    ROLLBACK TO update_collective_agreement;

Line: 1019


end update_collective_agreement;

Line: 1024


procedure delete_collective_agreement
  (p_validate                       in  boolean  default false
  ,p_collective_agreement_id        in  number
  ,p_object_version_number          in out nocopy number
  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc                  VARCHAR2(72) := g_package||'delete_collective_agreement';

Line: 1043


    SELECT 'x'
	  FROM per_cagr_entitlements pce
	 WHERE pce.collective_agreement_id = p_collective_agreement_id;

Line: 1048


    SELECT b.pl_id,
	       b.object_version_number,
		   cag.start_date
	  FROM ben_pl_f b,
	       per_collective_agreements cag
	 WHERE b.pl_id = cag.pl_Id
	   AND cag.collective_agreement_id = p_collective_agreement_id;

Line: 1057


    SELECT pop.popl_yr_perd_id,
		   pop.object_version_number
	  FROM ben_popl_yr_perd pop
	 WHERE pop.pl_id = l_pl_id;

Line: 1068


  savepoint delete_collective_agreement;

Line: 1081


    hr_collective_agreement_bk3.delete_collective_agreement_b
      (p_collective_agreement_id        =>  p_collective_agreement_id
      ,p_object_version_number          =>  p_object_version_number);

Line: 1088


        (p_module_name => 'DELETE_collective_agreement'
        ,p_hook_type   => 'BP'
        );

Line: 1138


	-- to the the plan and delete them
	--
    FOR c1 IN csr_popl_yr LOOP
	  --
	  hr_utility.set_location(l_proc||'/'||c1.popl_yr_perd_id, 50);

Line: 1146


	  -- Delete plan years link
	  --
	  ben_popl_yr_perd_api.delete_popl_yr_perd
        (p_validate                       => false		     -- Bug # 5405435
        ,p_popl_yr_perd_id                => c1.popl_yr_perd_id
        ,p_object_version_number          => l_popl_ovn);

Line: 1160


	-- Delete Plan
	--
    ben_plan_api.delete_plan
      (p_validate                       => p_validate
      ,p_pl_id                          => l_pl_id
      ,p_effective_start_date           => l_start_date
      ,p_effective_end_date             => l_end_date
      ,p_object_version_number          => l_pl_ovn
      ,p_effective_date                 => l_effective_date
      ,p_datetrack_mode                 => 'ZAP');

Line: 1188


    hr_collective_agreement_bk3.delete_collective_agreement_a
      (
       p_collective_agreement_id        =>  p_collective_agreement_id
      ,p_object_version_number          =>  l_object_version_number
      );

Line: 1196


        (p_module_name => 'DELETE_collective_agreement'
        ,p_hook_type   => 'AP'
        );

Line: 1221


    ROLLBACK TO delete_collective_agreement;

Line: 1236


    ROLLBACK TO delete_collective_agreement;

Line: 1239


end delete_collective_agreement;