1 PACKAGE PA_BILLING_WORKBENCH_BILL_PKG as
2 /* $Header: PAXBLWBS.pls 120.3 2006/01/19 03:15:47 bchandra noship $ */
3
4 /* Declaring global variable for invoice region VO
5 G_system_reference NUMBER;
6 G_ar_amount NUMBER; */
7
8 -- This procedure will get all the parameters for Billing Region for the given project.
9 -- burdened cost and raw revenue on the basis of passed parameters
10 -- Input parameters
11 -- Parameters Type Required Description
12 -- p_project_id NUMBER YES The identifier of the project
13 --
14 -- Out parameters
15 --
16 -- x_funding_amt NUMBER YES Total Baselined amount for the given project
17 -- x_rev_accured NUMBER YES Total Revenue accrued for the given project
18 -- x_rev_backlog NUMBER YES Revenue funding backlog. The diff of above two
19 -- x_rev_writeoff NUMBER YES Total accrued revenue writeoff
20 -- x_ubr NUMBER YES Total Unbilled receivables for the given project
21 -- x_uer NUMBER YES Total Unearned revenue for the given project
22 -- x_inv_billed NUMBER YES Total Invoiced amount(including project invoices, credit
23 -- memos,write-off,cancelling,concession project, and
24 -- retention invoices
25 -- x_inv_backlog NUMBER YES Invoice Funding backlog. The diff of Funding amt and inv_billed
26 -- x_inv_paid NUMBER YES Total invoice amount paid by the customers for this project
27 -- x_inv_due NUMBER YES Total invoice amount due from customers
28 -- x_billable_cost NUMBER YES Sum of the burdened cost of all the expenditure items
29 -- with billable flag as yes and cost distribution as yes
30 -- x_unbilled_cost NUMBER YES Total burdened cost that is not yet billed, but marked
31 -- as billable as yes
32 -- x_unbilled_events NUMBER YES Sum of all invoice events that are not billed to the customers (
33 -- including partialy billed event amount also
34 -- x_unbilled_retn NUMBER YES Total withheld amount that is not billed to the customer
35 -- x_unapproved_inv_amt NUMBER YES Sum of all the unapproved project and retention invoices
36 -- including credit memosof project invoices, cancelling,
37 -- writeoff,concession project
38 --
39 PROCEDURE Get_Billing_Sum_Region_Amts (
40 p_project_id IN NUMBER ,
41 p_project_currency IN VARCHAR2 ,
42 p_projfunc_currency IN VARCHAR2 ,
43 p_ubr IN NUMBER ,
44 p_uer IN NUMBER ,
45 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
46 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
47 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
48 );
49
50
51
52 --
53 -- Procedure : Get_Billing_Sum_Region_Amts
54 -- Purpose : This procedure will get all the parameters for Billing Region for the given project.
55 -- Parameters :
56 --
57
58
59 -- This procedure will populate the temp table with all the input paramters for billing
60 -- work bench.
61 -- Input parameters
62 -- Parameters Type Required Description
63 -- p_project_id NUMBER YES The identifier of the project
64 -- p_funding_amt NUMBER YES Total Baselined amount for the given project
65 -- p_rev_accured NUMBER YES Total Revenue accrued for the given project
66 -- p_rev_backlog NUMBER YES Revenue funding backlog. The diff of above two
67 -- p_rev_writeoff NUMBER YES Total accrued revenue writeoff
68 -- p_ubr NUMBER YES Total Unbilled receivables for the given project
69 -- p_uer NUMBER YES Total Unearned revenue for the given project
70 -- p_inv_billed NUMBER YES Total Invoiced amount(including project invoices, credit
71 -- memos,write-off,cancelling,concession project, and
72 -- retention invoices
73 -- p_inv_backlog NUMBER YES Invoice Funding backlog. The diff of Funding amt and inv_billed
74 -- p_inv_paid NUMBER YES Total invoice amount paid by the customers for this project
75 -- p_inv_due NUMBER YES Total invoice amount due from customers
76 -- p_billable_cost NUMBER YES Sum of the burdened cost of all the expenditure items
77 -- with billable flag as yes and cost distribution as yes
78 -- p_unbilled_cost NUMBER YES Total burdened cost that is not yet billed, but marked
79 -- as billable as yes
80 -- p_unbilled_events NUMBER YES Sum of all invoice events that are not billed to the customers (
81 -- including partialy billed event amount also
82 -- p_unbilled_retn NUMBER YES Total withheld amount that is not billed to the customer
83 -- p_unapproved_inv_amt NUMBER YES Sum of all the unapproved project and retention invoices
84 -- including credit memosof project invoices, cancelling,
85 -- writeoff,concession project
86 --
87 -- Out parameters
88 --
89
90 PROCEDURE Populat_Bill_Workbench_Data (
91 p_project_id IN NUMBER,
92 p_proj_funding_amt IN NUMBER ,
93 p_proj_rev_accured IN NUMBER ,
94 p_proj_rev_backlog IN NUMBER ,
95 p_proj_rev_writeoff IN NUMBER ,
96 p_proj_ubr IN NUMBER ,
97 p_proj_uer IN NUMBER ,
98 p_proj_inv_invoiced IN NUMBER ,
99 p_proj_inv_backlog IN NUMBER ,
100 p_proj_inv_paid IN NUMBER ,
101 p_proj_inv_due IN NUMBER ,
102 p_proj_billable_cost IN NUMBER ,
103 p_proj_unbilled_cost IN NUMBER ,
104 p_proj_unbilled_events IN NUMBER ,
105 p_proj_unbilled_retn IN NUMBER ,
106 p_proj_unapproved_inv_amt IN NUMBER ,
107 p_proj_tax IN NUMBER ,
108 p_pc_ubr_applicab_flag IN VARCHAR2,
109 p_pc_uer_applicab_flag IN VARCHAR2,
110 p_pc_unbil_eve_applicab_flag IN VARCHAR2,
111 p_projfunc_funding_amt IN NUMBER ,
112 p_projfunc_rev_accured IN NUMBER ,
113 p_projfunc_rev_backlog IN NUMBER ,
114 p_projfunc_rev_writeoff IN NUMBER ,
115 p_projfunc_ubr IN NUMBER ,
116 p_projfunc_uer IN NUMBER ,
117 p_projfunc_inv_invoiced IN NUMBER ,
118 p_projfunc_inv_backlog IN NUMBER ,
119 p_projfunc_inv_paid IN NUMBER ,
120 p_projfunc_inv_due IN NUMBER ,
121 p_projfunc_billable_cost IN NUMBER ,
122 p_projfunc_unbilled_cost IN NUMBER ,
123 p_projfunc_unbilled_events IN NUMBER ,
124 p_projfunc_unbilled_retn IN NUMBER ,
125 p_projfunc_unapprov_inv_amt IN NUMBER ,
126 p_projfunc_tax IN NUMBER ,
127 p_pfc_unbil_eve_applicab_flag IN VARCHAR2,
128 p_next_invoice_date IN DATE,
129 p_multi_customer_flag IN VARCHAR2,
130 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
131 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
132 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
133 );
134
135
136
137 --
138 -- Procedure : Populat_Bill_Workbench_Data
139 -- Purpose :This procedure will populate the temp table with all the input paramters for billing
140 -- work bench.
141 -- Parameters :
142 --
143
144
145
146
147
148
149 -- This procedure will populate the temp table with all the input paramters for Summary by customer region of invoicing
150 -- Input parameters
151 -- Parameters Type Required Description
152 -- p_project_id NUMBER YES The identifier of the project
153 -- p_inv_filter VARCHAR2 YES Filter to filter invoices based on the user inputs
154 --
155 -- Out parameters
156 --
157 /* Added 10 parameter after p_inv_filter for search region i.e. bug 3618704 */
158
159 PROCEDURE Populat_Inv_Summ_by_Cust_RN (
160 p_project_id IN NUMBER,
161 p_inv_filter IN VARCHAR2,
162 p_search_flag IN VARCHAR2,
163 p_agreement_id IN NUMBER ,
164 p_draft_num IN NUMBER,
165 p_ar_number IN VARCHAR2 ,
166 p_creation_frm_date IN DATE ,
167 p_creation_to_date IN DATE ,
168 p_invoice_frm_date IN DATE ,
169 p_invoice_to_date IN DATE ,
170 p_gl_frm_date IN DATE ,
171 p_gl_to_date IN DATE ,
172 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
173 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
174 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
175 );
176
177
178
179 --
180 -- Procedure : Populat_Inv_Summ_by_Cust_RN
181 -- Purpose :This procedure will populate the temp table with all the input paramters for Summary by customer region
182 -- of invoicing
183 -- Parameters :
184 --
185
186 FUNCTION Get_Due_Amount (
187 p_project_id IN NUMBER DEFAULT NULL,
188 p_draft_inv_num IN NUMBER DEFAULT NULL,
189 p_system_reference IN NUMBER ,
190 p_transfer_status_code IN VARCHAR2 ,
191 p_calling_mode IN VARCHAR2 ,
192 p_inv_amount IN NUMBER DEFAULT NULL,
193 p_proj_bill_amount IN NUMBER DEFAULT NULL,
194 p_projfunc_bill_amount IN NUMBER DEFAULT NULL
195 ) RETURN NUMBER;
196
197
198
199 --
200 -- Procedure : Get_Due_Amount
201 -- Purpose : This procedure will get all the parameters for Billing Region for the given project.
202 -- Parameters :
203 --
204
205 FUNCTION Get_Tax_Amount (
206 p_project_id IN NUMBER DEFAULT NULL,
207 p_draft_inv_num IN NUMBER DEFAULT NULL,
208 p_system_reference IN NUMBER ,
209 p_transfer_status_code IN VARCHAR2 ,
210 p_calling_mode IN VARCHAR2 ,
211 p_inv_amount IN NUMBER DEFAULT NULL,
212 p_proj_bill_amount IN NUMBER DEFAULT NULL,
213 p_projfunc_bill_amount IN NUMBER DEFAULT NULL
214 ) RETURN NUMBER;
215
216 -- Added for bug 4932118
217 Procedure PROJECT_UBR_UER_CONVERT (
218 P_PROJECT_ID IN NUMBER,
219 X_PROJECT_CURR_UBR OUT NOCOPY NUMBER,
220 X_PROJECT_CURR_UER OUT NOCOPY NUMBER,
221 X_RETURN_STATUS OUT NOCOPY VARCHAR,
222 X_MSG_COUNT OUT NOCOPY NUMBER,
223 X_MSG_DATA OUT NOCOPY VARCHAR );
224
225 END PA_BILLING_WORKBENCH_BILL_PKG;
226