[Home] [Help]
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
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
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
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')
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
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 */
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
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'
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' */
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