1 package body pa_draft_revenues_pkg as
2 /* $Header: PAXRVUTB.pls 120.2 2007/12/08 19:18:57 jjgeorge ship $ */
3
4 procedure INITIALIZE is
5 begin
6 pa_security.initialize(fnd_global.user_id, 'PAXRVRVW');
7 end INITIALIZE;
8
9 function ALLOW_RELEASE(
10 X_PROJECT_ID in NUMBER,
11 X_DRAFT_REVENUE_NUM in NUMBER)
12 return BOOLEAN is
13 dummy varchar2(1);
14 adj_flag varchar2(1);
15 begin
16 if pa_security.allow_update(X_project_id) = 'N' then
17 FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
18 return FALSE;
19 else
20 begin
21 select null
22 into dummy
23 from sys.dual
24 where exists (
25 select null
26 from pa_draft_revenues
27 where project_id = X_project_id
28 and draft_revenue_num = X_draft_revenue_num
29 and released_date is null
30 and NVL(generation_error_flag, 'N') = 'N');
31 exception
32 when no_data_found then
33 FND_MESSAGE.SET_NAME('PA','PA_RV_NO_RELEASE');
34 return FALSE;
35 when others then
36 return FALSE;
37 end;
38 /** bug 2343583 **/
39 Select nvl(adjusting_revenue_flag,'N') into adj_flag
40 from pa_draft_revenues_all where project_id = X_project_id and
41 draft_revenue_num = X_draft_revenue_num;
42 begin
43 select null
44 into dummy
45 from sys.dual
46 where not exists (
47 select null
48 from pa_draft_revenues
49 where project_id = X_project_id
50 and draft_revenue_num < X_draft_revenue_num
51 and released_date is null
52 and nvl(adjusting_revenue_flag,'N') = adj_flag );
53 exception
54 when no_data_found then
55 FND_MESSAGE.SET_NAME('PA','PA_RV_NOT_EARLIEST_REV');
56 return FALSE;
57 when others then
58 return FALSE;
59 end;
60 return TRUE;
61 end if;
62 end ALLOW_RELEASE;
63
64 function ALLOW_UNRELEASE(
65 X_PROJECT_ID in NUMBER,
66 X_DRAFT_REVENUE_NUM in NUMBER)
67 return BOOLEAN is
68 dummy varchar2(1);
69 adj_flag varchar2(1);
70 begin
71 if pa_security.allow_update(X_project_id) = 'N' then
72 FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
73 return FALSE;
74 else
75 begin
76 select null
77 into dummy
78 from sys.dual
79 where exists (
80 select null
81 from pa_draft_revenues
82 where project_id = X_project_id
83 and draft_revenue_num = X_draft_revenue_num
84 and released_date is not null
85 and transfer_status_code in ('P', 'R'));
86 exception
87 when no_data_found then
88 FND_MESSAGE.SET_NAME('PA','PA_RV_NO_UNRELEASE');
89 return FALSE;
90 when others then
91 return FALSE;
92 end;
93
94 /** bug 2343583 **/
95 Select nvl(adjusting_revenue_flag,'N') into adj_flag
96 from pa_draft_revenues_all where project_id = X_project_id and
97 draft_revenue_num = X_draft_revenue_num;
98
99 begin
100 select null
101 into dummy
102 from sys.dual
103 where not exists (
104 select null
105 from pa_draft_revenues
106 where project_id = X_project_id
107 and draft_revenue_num > X_draft_revenue_num
108 and released_date is not null
109 and nvl(adjusting_revenue_flag,'N') = adj_flag );
110 exception
111 when no_data_found then
112 FND_MESSAGE.SET_NAME('PA','PA_RV_NOT_LATEST_REV');
113 return FALSE;
114 when others then
115 return FALSE;
116 end;
117
118 begin
119 select null
120 into dummy
121 from sys.dual
122 where not exists (
123 select null
124 from pa_draft_invoices
125 where (project_id, draft_invoice_num) in
126 (select project_id,draft_invoice_num
127 from pa_cust_rev_dist_lines
128 where project_id = X_project_id
129 and draft_revenue_num = X_draft_revenue_num
130 union
131 select project_id,draft_invoice_num
132 from pa_cust_event_rev_dist_lines
133 where project_id = X_project_id
134 and draft_revenue_num = X_draft_revenue_num
135 union /* Start of Changes for bug 5401384 -base bug 5246804 */
136 select distinct dii.project_id,dii.draft_invoice_num
137 from pa_draft_invoice_items dii, pa_events e
138 where dii.project_id=X_project_id
139 and dii.event_num is not null
140 AND e.event_num = dii.event_num
141 and nvl(e.task_id,-99) = nvl(dii.event_task_id,-99)
142 AND EXISTS (SELECT 1 FROM pa_event_types et
143 WHERE e.event_type = et.event_type
144 and et.event_type_classification = 'AUTOMATIC')
145 and exists
146 (select 1
147 from pa_cust_event_RDL_ALL
148 where project_id = dii.project_id
149 and draft_revenue_num = X_draft_revenue_num
150 and event_num = dii.event_num
151 and NVL(task_id,-99) = NVL(dii.event_task_id,-99)
152 ))/* End of Changes for bug 5401384 - base bug 5246804 */
153 and released_date is not null);
154 exception
155 when no_data_found then
156 FND_MESSAGE.SET_NAME('PA','PA_RV_INV_RELEASED');
157 return FALSE;
158 when others then
159 return FALSE;
160 end;
161
162 begin
163 select null
164 into dummy
165 from sys.dual
166 where not exists (
167 select null
168 from pa_draft_revenues
169 where project_id = X_project_id
170 and draft_revenue_num = X_draft_revenue_num
171 and (nvl(resource_accumulated_flag, 'N') = 'Y'
172 or nvl(accumulated_flag, 'N') = 'Y'));
173 exception
174 when no_data_found then
175 FND_MESSAGE.SET_NAME('PA','PA_RV_ACCUMED');
176 return FALSE;
177 when others then
178 return FALSE;
179 end;
180 end if;
181 return TRUE;
182 end ALLOW_UNRELEASE;
183
184 procedure RELEASE(
185 X_PROJECT_ID in NUMBER,
186 X_DRAFT_REVENUE_NUM in NUMBER,
187 X_ERR_CODE in out NOCOPY NUMBER) Is
188 begin
189 if not allow_release(X_PROJECT_ID, X_DRAFT_REVENUE_NUM) then
190 X_err_code := 1;
191 else
192 begin
193 update pa_draft_revenues
194 set released_date = sysdate,
195 last_update_date = sysdate,
196 last_updated_by = fnd_global.user_id,
197 last_update_login = fnd_global.login_id
198 where project_id = X_project_id
199 and draft_revenue_num = X_draft_revenue_num;
200 exception
201 when others then
202 X_err_code := sqlcode;
203 end;
204 X_err_code := 0;
205 end if;
206 end RELEASE;
207
208 procedure UNRELEASE(
209 X_PROJECT_ID in NUMBER,
210 X_DRAFT_REVENUE_NUM in NUMBER,
211 X_ERR_CODE in out NOCOPY NUMBER) Is
212 begin
213 if not allow_unrelease(X_PROJECT_ID, X_DRAFT_REVENUE_NUM) then
214 X_err_code := 1;
215 else
216 begin
217 update pa_draft_revenues
218 set released_date = NULL,
219 last_update_date = sysdate,
220 last_updated_by = fnd_global.user_id,
221 last_update_login = fnd_global.login_id
222 where project_id = X_project_id
223 and draft_revenue_num = X_draft_revenue_num;
224 exception
225 when others then
226 X_err_code := sqlcode;
227 end;
228 X_err_code := 0;
229 end if;
230 end UNRELEASE;
231
232 end pa_draft_revenues_pkg;
[Home] [Help]