1 package body PA_IND_COST_MULTIPLIERS_PKG as
2 -- $Header: PAXCMULB.pls 120.1 2005/08/08 10:51:26 sbharath noship $
3 -----------------------------------------------------------------------------
4 procedure copy_multipliers (x_return_status IN OUT NOCOPY number,
5 x_stage IN OUT NOCOPY number,
6 x_ind_rate_sch_rev_id_from IN number,
7 x_ind_rate_sch_rev_id_to IN number)
8 is
9 x_last_updated_by number;
10 x_created_by number;
11 x_last_update_login number;
12
13 begin
14 x_return_status := 0;
15 x_stage := 0;
16
17 -- start copying the multipliers from the FROM revision to the TO revision
18
19 begin
20 x_last_updated_by := FND_GLOBAL.USER_ID;
21 x_created_by := FND_GLOBAL.USER_ID;
22 x_last_update_login := FND_GLOBAL.LOGIN_ID;
23
24 /* For Bug 3087964, inserting ready_to_compile_flag with value of 'Y' as default.
25 otherwise the schedule will not get compiled.
26 Inserting only those multipliers with ready_to_compile_flag <> 'X'.
27 Also added the statement to delete multipliers with ready_to_compile_flag as 'X'
28 prior to insertion. */
29
30 delete from pa_ind_cost_multipliers
31 where
32 ind_rate_sch_revision_id = x_ind_rate_sch_rev_id_to
33 and ready_to_compile_flag = 'X';
34
35 insert into pa_ind_cost_multipliers (ind_rate_sch_revision_id,
36 organization_id,
37 ind_cost_code,
38 multiplier,
39 last_update_date,
40 last_updated_by,
41 created_by,
42 creation_date,
43 last_update_login,
44 ready_to_compile_flag)
45 select x_ind_rate_sch_rev_id_to,
46 m.organization_id,
47 m.ind_cost_code,
48 m.multiplier,
49 SYSDATE,
50 x_last_updated_by,
51 x_created_by,
52 SYSDATE,
53 x_last_update_login,
54 'Y'
55 from pa_ind_cost_multipliers m
56 where m.ind_rate_sch_revision_id = x_ind_rate_sch_rev_id_from
57 and nvl(m.ready_to_compile_flag, 'N') <> 'X';
58
59 COMMIT;
60
61 x_return_status := 0;
62
63 EXCEPTION
64 WHEN NO_DATA_FOUND then
65 x_return_status := 1;
66 x_stage := 1;
67 return;
68
69 WHEN OTHERS then
70 x_return_status := SQLCODE;
71 end;
72
73 end copy_multipliers;
74 ------------------------------------------------------------------------------
75 ------------------------------------------------------------------------------
76 procedure check_references (x_return_status IN OUT NOCOPY number,
77 x_stage IN OUT NOCOPY number,
78 x_ind_rate_sch_revision_id IN number)
79 is
80 x_dummy number;
81
82 begin
83 x_return_status := 0;
84 x_stage := 0;
85
86 /* For Bug 3087964, added the condition ready_to_compile_flag <> 'X' in the exists clause */
87 begin
88 select 1
89 into x_dummy
90 from sys.dual
91 where not exists
92 (select 1
93 from pa_ind_cost_multipliers m
94 where m.ind_rate_sch_revision_id =
95 x_ind_rate_sch_revision_id
96 and nvl(m.ready_to_compile_flag,'N') <> 'X');
97
98 x_return_status := 0; -- ie. value does not exist is child table
99 -- delete allowed.
100
101 EXCEPTION
102 when NO_DATA_FOUND then -- ie. value exists in child table
103 -- delete NOT allowed.
104 x_return_status := 1; -- since it is part of the exception
105 x_stage := 1;
106
107 when OTHERS then
108 x_return_status := SQLCODE;
109
110 end;
111
112 end check_references;
113 ----------------------------------------------------------------------------
114
115 end PA_IND_COST_MULTIPLIERS_PKG;