1 PACKAGE BODY per_asg_migration AS
2 /* $Header: peasgmig.pkb 115.1 2003/12/08 08:18:57 adhunter noship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- |--------------------------< migrateAsgProjAsgEnd >------------------------|
6 -- ----------------------------------------------------------------------------
7 --
8 --
9 -- Description:
10 -- This procedure migrates a chunk of Assignment records, populating the column
11 -- projected_assignment_end from values in per_periods_of_placement.
12 --
13 -- Don't update the row if there is any DT instance for this PKid or any in the
14 -- same placement which have projected_assignment_end populated with non-null value
15 --
16 -- Update if all DT instances are null with projected_placement_end_date
17 --
18 procedure migrateAsgProjAsgEnd(
19 p_process_ctrl IN varchar2,
20 p_start_pkid IN number,
21 p_end_pkid IN number,
22 p_rows_processed OUT nocopy number) IS
23 --
24 TYPE dateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
25 TYPE personidTab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
26 TYPE projectedEndDateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
27 --
28 startDates dateTab;
29 personIds personidTab;
30 projDates projectedEndDateTab;
31 --
32 cursor csr_projected_end is
33 select date_start,person_id,projected_termination_date
34 from per_periods_of_placement
35 where period_of_placement_id between p_start_pkid and p_end_pkid
36 and projected_termination_date is not null;
37 --
38 l_rows_processed number := 0;
39 --
40 BEGIN
41 --
42 open csr_projected_end;
43 fetch csr_projected_end BULK COLLECT INTO startDates,personIds,projDates;
44 --
45 if personIds.COUNT > 0 then
46 FORALL i in personIds.FIRST..personIds.LAST
47 update per_all_assignments_f paf1
48 set paf1.projected_assignment_end = projDates(i)
49 where paf1.period_of_placement_date_start = startDates(i)
50 and paf1.person_id = personIds(i)
51 and not exists (select null
52 from per_all_assignments_f paf2
53 where paf1.assignment_id
54 = paf2.assignment_id
55 and paf2.projected_assignment_end is not null);
56 --
57 l_rows_processed := SQL%ROWCOUNT;
58 --
59 end if;
60 close csr_projected_end;
61 END migrateAsgProjAsgEnd;
62
63 end per_asg_migration;
[Home] [Help]