1 package body PA_IND_COST_MULTIPLIERS_PKG as
2 -- $Header: PAXCMULB.pls 120.2 2009/05/08 07:28:57 sgottimu ship $
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 x_calling_module IN varchar2) -- added for bug 7391889
9 is
10 x_last_updated_by number;
11 x_created_by number;
12 x_last_update_login number;
13
14 begin
15 x_return_status := 0;
16 x_stage := 0;
17
18 -- start copying the multipliers from the FROM revision to the TO revision
19
20 begin
21 x_last_updated_by := FND_GLOBAL.USER_ID;
22 x_created_by := FND_GLOBAL.USER_ID;
23 x_last_update_login := FND_GLOBAL.LOGIN_ID;
24
25 /* For Bug 3087964, inserting ready_to_compile_flag with value of 'Y' as default.
26 otherwise the schedule will not get compiled.
27 Inserting only those multipliers with ready_to_compile_flag <> 'X'.
28 Also added the statement to delete multipliers with ready_to_compile_flag as 'X'
29 prior to insertion. */
30
31 delete from pa_ind_cost_multipliers
32 where
33 ind_rate_sch_revision_id = x_ind_rate_sch_rev_id_to
34 and ready_to_compile_flag = 'X';
35
36 /* Changes for 7391889 start here */
37 IF x_calling_module = 'CAP_INT' THEN
38 insert into pa_ind_cost_multipliers (ind_rate_sch_revision_id,
39 organization_id,
40 ind_cost_code,
41 multiplier,
42 last_update_date,
43 last_updated_by,
44 created_by,
45 creation_date,
46 last_update_login,
47 ready_to_compile_flag)
48 select x_ind_rate_sch_rev_id_to,
49 m.organization_id,
50 m.ind_cost_code,
51 m.multiplier,
52 SYSDATE,
53 x_last_updated_by,
54 x_created_by,
55 SYSDATE,
56 x_last_update_login,
57 'Y'
58 from pa_ind_cost_multipliers m
59 where m.ind_rate_sch_revision_id = x_ind_rate_sch_rev_id_from
60 and nvl(m.ready_to_compile_flag, 'N') <> 'X';
61
62 ELSE /* Changes for 7391889 end here */
63
64 insert into pa_ind_cost_multipliers (ind_rate_sch_revision_id,
65 organization_id,
66 ind_cost_code,
67 multiplier,
68 last_update_date,
69 last_updated_by,
70 created_by,
71 creation_date,
72 last_update_login,
73 ready_to_compile_flag)
74 select x_ind_rate_sch_rev_id_to,
75 m.organization_id,
76 m.ind_cost_code,
77 m.multiplier,
78 SYSDATE,
79 x_last_updated_by,
80 x_created_by,
81 SYSDATE,
82 x_last_update_login,
83 'Y'
84 from pa_ind_cost_multipliers m
85 where m.ind_rate_sch_revision_id = x_ind_rate_sch_rev_id_from
86 and nvl(m.ready_to_compile_flag, 'N') <> 'X';
87
88 END IF; /* Added for 7391889 */
89
90 COMMIT;
91
92 x_return_status := 0;
93
94 EXCEPTION
95 WHEN NO_DATA_FOUND then
96 x_return_status := 1;
97 x_stage := 1;
98 return;
99
100 WHEN OTHERS then
101 x_return_status := SQLCODE;
102 end;
103
104 end copy_multipliers;
105 ------------------------------------------------------------------------------
106 ------------------------------------------------------------------------------
107 procedure check_references (x_return_status IN OUT NOCOPY number,
108 x_stage IN OUT NOCOPY number,
109 x_ind_rate_sch_revision_id IN number)
110 is
111 x_dummy number;
112
113 begin
114 x_return_status := 0;
115 x_stage := 0;
116
117 /* For Bug 3087964, added the condition ready_to_compile_flag <> 'X' in the exists clause */
118 begin
119 select 1
120 into x_dummy
121 from sys.dual
122 where not exists
123 (select 1
124 from pa_ind_cost_multipliers m
125 where m.ind_rate_sch_revision_id =
126 x_ind_rate_sch_revision_id
127 and nvl(m.ready_to_compile_flag,'N') <> 'X');
128
129 x_return_status := 0; -- ie. value does not exist is child table
130 -- delete allowed.
131
132 EXCEPTION
133 when NO_DATA_FOUND then -- ie. value exists in child table
134 -- delete NOT allowed.
135 x_return_status := 1; -- since it is part of the exception
136 x_stage := 1;
137
138 when OTHERS then
139 x_return_status := SQLCODE;
140
141 end;
142
143 end check_references;
144 ----------------------------------------------------------------------------
145
146 end PA_IND_COST_MULTIPLIERS_PKG;