1 package body pa_purge_validate_billing as
2 /* $Header: PAXBIVTB.pls 120.7 2007/10/31 05:31:59 dlella ship $ */
3
4 -- Start of comments
5 -- API name : Validate_Billing
6 -- Type : Public
7 -- Pre-reqs : None
8 -- Function : Validates the data in billing tables before purge for a project
9 -- and reports the invalid data conditions
10 -- The following validations are performed
11 --
12 -- 1.All the expenditure items should be revenue distributed.
13 -- 2.All the draft revenues are transferred and accepted in GL.
14 -- 3.All the draft invoices are transferred and accepted in AR.
15 -- 4.All revenue are summarized (*** Removed).
16 -- 5.Unbilled Recievables and Unearned Revenue should be zero.
17 -- 6.Events having completion date should be processed
18 --
19 --
20 -- Parameters p_batch_id IN NUMBER
21 -- The purge batch id for which rows have
22 -- to be purged/archived.
23 -- p_project_Id IN NUMBER,
24 -- The project id for which records have
25 -- to be purged/archived.
26 -- p_Active_Flag IN VARCHAR2,
27 -- Indicates if batch contains ACTIVE or CLOSED projects
28 -- ( 'A' - Active , 'C' - Closed)
29 -- p_Txn_To_Date IN DATE,
30 -- Date on or before which all transactions are to be purged
31 -- (Will be used by Costing only)
32 -- X_Err_Stack IN OUT VARCHAR2,
33 -- Error stack
34 -- X_Err_Stage IN OUT VARCHAR2,
35 -- Stage in the procedure where error occurred
36 -- X_Err_Code IN OUT NUMBER
37 -- Error code returned from the procedure
38 -- = 0 SUCCESS
39 -- > 0 Application error
40 -- < 0 Oracle error
41 -- End of comments
42
43 procedure validate_billing ( p_project_id in NUMBER,
44 p_txn_to_date in DATE,
45 p_active_flag in VARCHAR2,
46 x_err_code in OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
47 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
48 x_err_stage in OUT NOCOPY VARCHAR2 ) is --File.Sql.39 bug 4440895
49
50 -- Cursor for billing validaton before purge
51 --
52 cursor IsBillingValid(p_user_source_name IN VARCHAR2) is
53 ---Comments for the bug - 6522044..starts here
54 /* select 'Not Revenue Distributed' , 'PA_ARPR_NOT_REV_DIST'
55 from dual
56 where exists ( select spf.project_id
57 from pa_summary_project_fundings spf,
58 pa_projects_all pp,
59 -- pa_project_types_all pt -- performance changes bug 2706550
60 pa_project_types_v pt -- Added to remove FTS bug 2706550
61 where
62 pp.project_id = p_project_id
63 and pp.project_type = pt.project_type
64 and pt.project_type_class_code = 'CONTRACT'
65 and spf.project_id = pp.project_id
66 and nvl(spf.total_baselined_amount,0) <> 0
67 group by spf.project_id
68 having
69 -- Total baselined amount need not be equal to billed or accrued
70 -- amount in case of soft limit. Total invoiced and accrued amt
71 -- should atleast be equal to total baselined amount in any case.
72 -- sum (spf.total_baselined_amount) <> sum(spf.total_accrued_amount)
73 -- OR
74 -- sum (spf.total_baselined_amount) <> sum (spf.total_billed_amount)
75
76 -- Commented as the desired functionality is to check against PFC for accrued and
77 -- IPC for billed
78 -- sum (spf.total_baselined_amount) > sum(spf.total_accrued_amount)
79 -- OR
80 -- sum (spf.total_baselined_amount) > sum (spf.total_billed_amount)
81
82 sum (spf.projfunc_baselined_amount) > sum(spf.projfunc_accrued_amount)
83 OR
84 sum (spf.invproc_baselined_amount) > sum (spf.invproc_billed_amount)
85 )
86 UNION */
87 -- ---Comments for the bug - 6522044..ends here
88 /* Deleted the old code and added below for bug # 2861315 */
89 select 'Revenue Not Transferred and Accepted ' , 'PA_ARPR_NOT_REV_IFCD'
90 from dual
91 where exists ( select draft_revenue_num
92 from pa_draft_revenues dr,
93 pa_implementations imp
94 where dr.project_id = p_project_id
95 and (( dr.transfer_status_code <> 'A'
96 and nvl(imp.INTERFACE_REVENUE_TO_GL_FLAG,'N')<>'Y')
97 or (imp.INTERFACE_REVENUE_TO_GL_FLAG = 'Y'
98 and ((nvl(dr.unearned_revenue_cr,0) <> 0
99 and exists (select 'In XLA AE Lines'
100 from xla_ae_headers xh, xla_ae_lines xl, xla_distribution_links xdl
101 where xh.event_id = dr.event_id
102 and xl.ae_header_id = xh.ae_header_id
103 and xl.ae_header_id = xdl.ae_header_id
104 and xl.ae_line_num = xdl.ae_line_num
105 and xdl.SOURCE_DISTRIBUTION_TYPE = 'Revenue - UER'
106 and xdl.SOURCE_DISTRIBUTION_ID_NUM_1 = dr.project_id
107 and xdl.SOURCE_DISTRIBUTION_ID_NUM_2 = dr.draft_revenue_num
108 and xh.accounting_entry_status_code <> 'F' ))
109 or (nvl(dr.unbilled_receivable_dr,0) <> 0
110 and exists (select 'In XLA AE Lines'
111 from xla_ae_headers xh, xla_ae_lines xl, xla_distribution_links xdl
112 where xh.event_id = dr.event_id
113 and xl.ae_header_id = xh.ae_header_id
114 and xl.ae_header_id = xdl.ae_header_id
115 and xl.ae_line_num = xdl.ae_line_num
116 and xdl.SOURCE_DISTRIBUTION_TYPE = 'Revenue - UBR'
117 and xdl.SOURCE_DISTRIBUTION_ID_NUM_1 = dr.project_id
118 and xdl.SOURCE_DISTRIBUTION_ID_NUM_2 = dr.draft_revenue_num
119 and xh.accounting_entry_status_code <> 'F'
120 ))))))
121 UNION
122 select 'Invoice has outstanding balance ' , 'PA_ARPR_INV_AMT_DUE'
123 from dual
124 where exists ( select draft_invoice_num
125 from pa_draft_invoices_all di,
126 ar_payment_schedules ar
127 where di.project_id = p_project_id
128 and di.transfer_status_code = 'A'
129 and ar.customer_trx_id = di.system_reference
130 and ( ar.amount_due_remaining is null
131 OR ar.amount_due_remaining <> 0 ))
132 UNION
133 select 'Invoice Not Transferred and Accepted ' , 'PA_ARPR_NOT_INV_IFCD'
134 from dual
135 where exists ( select draft_invoice_num
136 from pa_draft_invoices_all di
137 where di.project_id = p_project_id
138 and di.transfer_status_code <> 'A')
139 UNION
140 select 'UBR and UER not cleared' , 'PA_ARPR_NOT_UBR_UER'
141 from dual
142 where exists ( select project_id
143 from pa_projects_all pp
144 where pp.project_id = p_project_id
145 and
146 (nvl(pp.unbilled_receivable_dr,0) <> 0
147 or
148 nvl(pp.unearned_revenue_cr,0) <> 0)
149 )
150 UNION
151 /* Bug 2423429. Use transaction value of bill and revenue amount */
152 select 'Events not processed' , 'PA_ARPR_NOT_ENVT_PCSD'
153 from dual
154 where exists
155 ( select 'x'
156 /* from pa_events_v ev Commented for performance bug 2706550 */
157 from pa_events ev /* Added for performance bug 2706550 */
158 where ev.project_id = p_project_id
159 and NVL(ev.revenue_distributed_flag,'N') <> 'Y'
160 and NVL(ev.bill_trans_REV_AMOUNT,0) > 0
161 UNION
162 select 'x'
163 /* from pa_events_v ey, Commented for performance bug 2706550 */
164 from pa_events ey, /* Added for performance bug 2706550 */
165 pa_event_types et
166 where ey.project_id = p_project_id
167 and et.event_type = ey.event_type
168 and NVL(ey.bill_hold_flag,'N') = 'N'
169 and NVL(ey.bill_trans_bill_amount,0) <> 0
170 and not exists
171 ( select 'x' from
172 pa_draft_invoice_items di
173 where di.project_id = ey.project_id
174 and NVL(di.event_task_id,-1) = NVL(ey.task_id,-1)
175 and di.event_num = ey.event_num
176 and not exists (
177 select NULL from pa_draft_invoices inv
178 where inv.project_id = di.project_id
179 and inv.draft_invoice_num = di.draft_invoice_num
180 and inv.write_off_flag ='Y')
181 having sum(di.bill_trans_bill_amount) =
182 decode(et.event_type_classification,'INVOICE REDUCTION',
183 -ey.bill_trans_bill_amount,ey.bill_trans_bill_amount )
184 )
185 )
186 UNION
187 select 'This is an Inter Company Billing Project', 'PA_ARPR_IC_BILLING_PROJ'
188 from dual
189 where exists ( select null
190 from pa_projects pp, pa_project_types pt
191 where pp.project_id = p_project_id
192 and pp.project_type = pt.project_type
193 and pt.project_type_class_code = 'CONTRACT'
194 and pt.cc_prvdr_flag = 'Y'
195 )
196 /* UNION
197 select 'This is an Inter Company Cross Charge Project', 'PA_ARPR_IC_CC_PROJ'
198 from dual
199 where exists ( select null
200 from pa_draft_invoice_details_all
201 where cc_project_id = p_project_id
202 )
203 Already this check is there in costing validation */
204 /* Bug#2416385 Commented for Phase 3 of Archive and Purge
205 UNION
206 select 'This is an Inter-Project Provider Project', 'PA_ARPR_IP_PRVDR_PROJ'
207 from dual
208 where exists ( select NULL
209 from pa_project_customers ppc
210 where ppc.project_id = p_project_id
211 and ppc.bill_another_project_flag = 'Y'
212 and ppc.receiver_task_id is not null
213 ) */
214 /*** bug 2396427. Moving this validation to costing module, since
215 receiver project can be an indirect project and billing validation
216 is done only for contract projects.
217 UNION
218 select 'This is an Inter-Project Receiver Project', 'PA_ARPR_IP_RCVR_PROJ'
219 from dual
220 where exists ( select NULL
221 from pa_tasks pt
222 where pt.project_id = p_project_id
223 and pt.receive_project_invoice_flag = 'Y' */
224 /* and pt.task_id in ( select receiver_task_id
225 from pa_project_customers ppc
226 ) modified for the bug# 2272487 */
227 /* and exists ( select NULL
228 from pa_project_customers ppc
229 where ppc.receiver_task_id = pt.task_id)
230 )*/
231 UNION
232 select 'All retentions are not billed for this project', 'PA_ARPR_RETN_NOT_BILLED'
233 from dual
234 where 0 <> ( select (sum(nvl(project_total_retained,0)) - sum(nvl(project_total_billed,0)))
235 from pa_summary_project_retn
236 where project_id = p_project_id
237 group by project_id
238 );
239
240 l_err_stack VARCHAR2(2000);
241 l_err_stage VARCHAR2(500);
242 l_err_code NUMBER ;
243 l_dummy VARCHAR2(500);
244 l_msg_name VARCHAR2(50);
245 l_user_source_name VARCHAR2(25);
246 l_used_by_oke BOOLEAN;
247
248 BEGIN
249 l_err_code := 0 ;
250 l_err_stage := x_err_stage;
251 l_err_stack := x_err_stack;
252 pa_debug.debug('-- Performing Billing validation for project '||to_char(p_project_id));
253
254 -- Open cursor
255 -- If cursor returns one or more rows , indicates that
256 -- project is not valid for purge as far as billing is concerned
257 --
258 SELECT user_je_source_name
259 INTO l_user_source_name
260 FROM GL_Je_Sources
261 WHERE je_source_name='Project Accounting';
262
263 Open IsBillingValid(l_user_source_name) ;
264
265 pa_debug.debug('-- After Open cursor IsBillingValid');
266
267 LOOP
268
269 -- Fetch a row for each validation that failed
270 -- and set the appropriate message
271 --
272 Fetch IsBillingValid into l_dummy , l_msg_name ;
273 Exit When IsBillingValid%Notfound;
274 fnd_message.set_name('PA',l_msg_name );
275 fnd_msg_pub.add;
276 l_err_stack := l_err_stack || ' ->After open cursor ' ||l_dummy ;
277 pa_debug.debug(' * ' || l_dummy|| ' for ' || to_char(p_project_id));
278
279
280 END LOOP;
281
282 close IsBillingValid;
283
284 --Check to see if any projects are in use by contract integration system.
285
286 pa_debug.debug('-- Before call to check usage by OKE');
287
288 l_used_by_oke := OKE_DTS_PA_PKG.Project_Exist(p_project_id);
289
290 IF l_used_by_oke
291 THEN
292 fnd_message.set_name('PA','PA_ARPR_PROJ_INUSE_CNTRCT_INT');
293 fnd_msg_pub.add;
294 l_err_stage := 'After OKE check';
295 l_err_stack := l_err_stack || ' ->After OKE check' ;
296 pa_debug.debug(' * This project '||to_char(p_project_id)||' is in use by OKE ');
297 END IF;
298
299 pa_debug.debug('-- After call to check usage by OKE');
300
301 x_err_stage := l_err_stage ;
302 x_err_stack := l_err_stack ;
303
304 EXCEPTION
305 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
306 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
307
308 WHEN OTHERS THEN
309 x_err_stage := l_err_stage ;
310
311 /* ATG Changes */
312
313 x_err_stack := l_err_stack;
314
315
316 pa_debug.debug('Error Procedure Name := PA_PURGE_VALIDATE_BILLING.VALIDATE_BILLING' );
317 pa_debug.debug('Error stage is '||l_err_stage );
318 pa_debug.debug('Error stack is '||l_err_stack );
319 pa_debug.debug(SQLERRM);
320 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
321
322 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
323
324 END validate_billing ;
325
326 END ;
[Home] [Help]