1 package body PA_IND_RATE_SCHEDULES_PKG as
2 -- $Header: PAXCIRSB.pls 120.1 2005/08/23 19:20:11 spunathi noship $
3 -----------------------------------------------------------------------------
4 -- This procedure checks if ind_rate_sch_id has been referenced in a
5 -- Project_type, or a Project, or a Task.
6 -- Note that the check against revisions is done at the form level because
7 -- of the Master-Detail relationship.
8
9 procedure check_references(x_return_status IN OUT NOCOPY number,
10 x_stage IN OUT NOCOPY number,
11 x_ind_rate_sch_id IN number)
12 is
13 x_dummy number;
14
15 begin
16 x_return_status := 0;
17 x_stage := 0;
18
19 -- check against PA_PROJECT_TYPES_ALL
20 begin
21 select 1
22 into x_dummy
23 from sys.dual
24 where not exists
25 (select 1
26 from pa_project_types_all pt
27 where pt.cost_ind_rate_sch_id = x_ind_rate_sch_id
28 or pt.rev_ind_rate_sch_id = x_ind_rate_sch_id
29 or pt.inv_ind_rate_sch_id = x_ind_rate_sch_id
30 or pt.cint_rate_sch_id = x_ind_rate_sch_id);/* added for bug#3041364 */
31
32 x_return_status := 0; -- ie. value does not exist is child table
33 -- delete allowed.
34
35 EXCEPTION
36 when NO_DATA_FOUND then -- ie. value exists in child table
37 -- delete NOT allowed.
38 x_return_status := 1;
39 x_stage := 1;
40 return;
41
42 end;
43
44 -- check against PA_PROJECTS_ALL
45 begin
46 select 1
47 into x_dummy
48 from sys.dual
49 where not exists
50 (select 1
51 from pa_projects_all p
52 where p.cost_ind_rate_sch_id = x_ind_rate_sch_id
53 or p.rev_ind_rate_sch_id = x_ind_rate_sch_id
54 or p.inv_ind_rate_sch_id = x_ind_rate_sch_id
55 or p.cint_rate_sch_id = x_ind_rate_sch_id); /* added for bug#3041364 */
56
57 x_return_status := 0;
58
59 EXCEPTION
60 when NO_DATA_FOUND then -- ie. value exists in child table
61 x_return_status := 1;
62 x_stage := 2;
63 return;
64
65 end;
66
67 -- check against PA_TASKS
68 begin
69 select 1
70 into x_dummy
71 from sys.dual
72 where not exists
73 (select 1
74 from pa_tasks t
75 where t.cost_ind_rate_sch_id = x_ind_rate_sch_id
76 or t.rev_ind_rate_sch_id = x_ind_rate_sch_id
77 or t.inv_ind_rate_sch_id = x_ind_rate_sch_id);
78
79 x_return_status := 0;
80
81 EXCEPTION
82 when NO_DATA_FOUND then -- ie. value exists in child table
83 x_return_status := 1;
84 x_stage := 3;
85 return;
86
87 end;
88
89 /* Bug 3594545: Check if check if the Burden Rate Schedule is being
90 referenced by any Plan Type or not */
91 begin
92 if (pa_fin_plan_utils.check_delete_burd_sch_ok(
93 p_ind_rate_sch_id => x_ind_rate_sch_id) = 'N') then
94 x_return_status := 1;
95 x_stage := 4;
96 return;
97 else
98 x_return_status := 0;
99 end if;
100 end;
101
102 /* bug fix:3123484 Before deleting the schedule check for cap int txns */
103 -- check against capitalized Interest transactions
104 begin
105 select 1
106 into x_dummy
107 from sys.dual
108 where exists (select 1
109 from pa_alloc_txn_details p
110 ,pa_ind_rate_sch_revisions prev
111 where p.ind_rate_sch_revision_id = prev.ind_rate_sch_revision_id
112 and prev.ind_rate_sch_id = x_ind_rate_sch_id );
113
114 x_return_status := 1;
115 x_stage := 2;
116
117 EXCEPTION
118 when NO_DATA_FOUND then -- ie. value exists in child table
119 x_return_status := 0;
120 x_stage := 0;
121 return;
122
123 end;
124
125 EXCEPTION
126 when OTHERS then
127 x_return_status := SQLCODE;
128
129
130 end check_references;
131 ----------------------------------------------------------------------------
132 -- This procedure gets the system default for the schedule type.
133
134 procedure get_defined_type(x_return_status IN OUT NOCOPY number,
135 x_stage IN OUT NOCOPY number,
136 x_ind_rate_schedule_type IN OUT NOCOPY varchar2)
137 is
138 begin
139 x_return_status := 0;
140 x_stage := 0;
141
142 select pov.profile_option_value
143 into x_ind_rate_schedule_type
144 from fnd_profile_option_values pov,
145 fnd_profile_options po
146 where pov.application_id = po.application_id
147 and pov.profile_option_id = po.profile_option_id
148 and pov.level_id = 10001
149 and po.application_id = 275
150 and po.profile_option_name = 'PA_IND_RATE_SCHEDULE_TYPE'
151 and trunc(SYSDATE) between
152 trunc(po.start_date_active)
153 and trunc(nvl(po.end_date_active, SYSDATE));
154
155 x_return_status := 0;
156
157 EXCEPTION
158 WHEN NO_DATA_FOUND then
159 x_return_status := 1;
160 x_stage := 1;
161 return;
162
163 WHEN OTHERS then
164 x_return_status := SQLCODE;
165
166 end get_defined_type;
167 ----------------------------------------------------------------------------
168 -- Checks if revisions exist for a schedule.
169
170 procedure check_revisions(x_return_status IN OUT NOCOPY number,
171 x_stage IN OUT NOCOPY number,
172 x_ind_rate_sch_id IN number)
173 is
174 x_dummy number;
175
176 begin
177 x_return_status := 0;
178 x_stage := 0;
179
180 -- check against PA_IND_RATE_SCH_REVISIONS
181 begin
182 select 1
183 into x_dummy
184 from sys.dual
185 where not exists
186 (select 1
187 from pa_ind_rate_sch_revisions irsr
188 where irsr.ind_rate_sch_id = x_ind_rate_sch_id);
189
190 x_return_status := 0;
191
192 EXCEPTION
193 when NO_DATA_FOUND then -- ie. value exists in child table
194 x_return_status := 1;
195 x_stage := 1;
196 return;
197
198 when OTHERS then
199 x_return_status := SQLCODE;
200 end;
201
202
203
204 end check_revisions;
205 ----------------------------------------------------------------------------
206 end PA_IND_RATE_SCHEDULES_PKG;
[Home] [Help]