1 PACKAGE pa_billing_pub AS
2 /* $Header: PAXIPUBS.pls 120.3 2005/08/05 02:11:59 bchandra noship $ */
3
4 ------------
5 -- OVERVIEW
6 -- Procedures/Functions for use by Clients are included in this file.
7 --
8 --
9
10 ----------------------------
11 -- PROCEDURES AND FUNCTIONS
12 --
13 -- 1. Procedure Name: get_budget_amount returns the budgetted cost and
14 -- revenue amount for the project/top task id specified.
15 -- Usage: get_budget_amount(project_id, task_id, revenue, cost)
16 --
17 -- Parameters:
18 --
19 -- X2_project_id : project_id of project to get budget
20 -- IN Variable
21 --
22 -- X2_task_id : Top task id of the project
23 -- IN Variable
24 --
25 -- X2_revenue_amount : Revenue budget amount
26 -- OUT Variable ,
27 --
28 -- X2_cost_amount : Cost budget amount
29 -- OUT Variable
30 --
31 -- P_cost_budget_type_code : IN Variable
32 -- Cost Budget type code
33 -- You may specify if
34 -- a different cost budget is
35 -- to be used other than the
36 -- default 'AC'.
37 --
38 -- P_rev_budget_type_code : IN Variable
39 -- Cost Budget type code
40 -- You may specify if
41 -- a different cost budget is
42 -- to be used other than the
43 -- default 'AR'.
44 --
45 /* Added for Fin Plan impact */
46 -- P_cost_plan_type_id : IN Variable
47 -- Unique identifier of the
48 -- plan type used for calculating
49 -- Cost plan amount.
50 --
51 -- P_rev_plan_type_id : IN Variable
52 -- Unique identifier of the
53 -- plan type used for calculating
54 -- Revenue plan amount.
55 /* Till here */
56 --
57 -- X_cost_budget_type_code : OUT Variable
58 -- Contains the
59 -- cost_budget_type_code
60 -- used.
61 --
62 -- X_rev_budget_type_code : OUT Variable
63 -- Contains the
64 -- revenue_budget_type_code
65 -- used.
66 --
67 -- X_error_message : OUT Variable
68 -- Error message if any, will
69 -- be returned.
70 --
71 -- X_status : OUT Variable
72 -- Status
73 --
74 -- 2. Procedure Name: get_amount
75 -- *** We recommended that you use the views
76 -- PA_BILLING_REV_TRANSACTIONS_V and
77 -- PA_BILLING_INV_TRANSACTIONS_V to select transaction
78 -- amounts. Get_amount will not be supported in future
79 -- versions ***
80 --
81 --
82 -- Usage: get_amount(... , amount, status);
83 -- amount will get the revenue, cost or bill amount
84 -- as specified by the parameters listed below.
85 -- status will get any error status returned by
86 -- the procedure.
87 --
88 -- Parameters: You must specify X_project_id.
89 -- Specify X_request_id if you want amounts for this
90 -- run only.
91 -- You must also specify X_calling_process. For the first
92 -- four values you may send in exactly the same value as
93 -- is passed into your stored procedure eg.
94 -- X_project_id => X_project_id, X_request_id =>X_req..etc
95 --
96 -- X_project_id: If this value is specified, the
97 -- procedure will retrieve amounts for
98 -- this project only.
99 -- X_request_id: If specified only amounts being
100 -- processed in the current run
101 -- will be retrieved.
102 -- X_calling_process: Can have a value of 'Revenue'
103 -- or 'Invoice'. If this value is
104 -- left blank it defaults to 'Revenue'.
105 -- X_calling_place: Can have a value of 'REG' or 'ADJ'.
106 -- You may send in the parameter that
107 -- was sent in to your procedure. The
108 -- behaviour of get_amounts will vary
109 -- as described below:
110 -- If you send in this value as:
111 -- X_calling_place => X_calling_place
112 -- then the behaviour will be as follows:
113 -- Regular Section: Only +ve amounts
114 -- Adjustment Section: Only -ve amounts
115 -- Pre/Post Section: All amount
116 --
117 -- If you send in NULL or ignore this
118 -- parameter (it defaults to NULL),
119 -- then All amounts will be returned in
120 -- all cases.
121 -- X_which_amount: Can have value of R, I, C or B.
122 -- Default is R.
123 -- R: Revenue amount
124 -- I: Invoice amount
125 -- C: Raw Cost amount
126 -- B: Burden Cost amount
127 -- X_amount: Output parameter to hold amount
128 -- returned
129 -- X_status: Output parameter to hold error status
130 -- returned.
131 -- X_top_task_id: If you wish to retreive totals only
132 -- for all tasks below a particular
133 -- top_task, specify this value.
134 -- X_system_linkage: Only amounts for specified
135 -- system linkage will be retrieved.
136 --
137 -- The three parameters below must either
138 -- all or none be specified:
139 -- X_cost_base: Only amounts for expenditure_types
140 -- related to specified cost base will be
141 -- retrieved.
142 -- X_CP_structure: Since cost_base only makes sense in
143 -- the context of a cost plus structure
144 -- and cost base type, this must be
145 -- specified, whenever cost base is
146 -- specified.
147 -- X_CB_type: Similarly, cost_base_type must be
148 -- specified if a cost base is specified.
149 --
150 -- Note: If you are using request_id and want to get detail figures, you will
151 -- get figures for cost/rev/invoice from ei's that have been revenue
152 -- distributed/billed in this run. If your distribution rule is
153 -- COST/COST or COST/EVENT or EVENT/EVENT, you will not get any figures,
154 -- since the ei's do not get any rdl's in these cases.
155 --
156 -- 10. Procedure Name: insert_message
157 --
158 -- Inserts a row into pa_billing_messages. This
159 -- is of use in debugging what happened when the Users
160 -- procedure executed.
161 --
162 -- The only 2 required parameters are
163 -- x_inserting_procedure_name and x_message.
164 --
165 -- 11. Procedure Name: insert_event
166 --
167 -- Purpose: Inserts rows into table pa_events subject to
168 -- the following validation:
169 -- - Event_type must have an event_type_classification of
170 -- 'AUTOMATIC'.
171 -- - Completion date of event must be before
172 -- accrue_through_date
173 -- - If the Bill Extn Id belongs to an Invoice Type extn,
174 -- then both revenue and invoice amount can be positive
175 -- but if there is a +ve revenue amount, the invoice
176 -- amount must be greater than 0.
177 -- - Project Id be supplied, or an error message will be
178 -- returned.
179 -- - Also for each of the above validations, an error
180 -- message will be returned.
181 -- - One of X_rev_amt or X_bill_amt must be non_zero
182 -- all other values will default as described below:
183 -- Parameters: Defaults
184 -- X_rev_amt 0
185 -- X_bill_amt 0
186 -- X_project_id current project
187 -- X_event_type from Billing Ext Dflt
188 -- X_top_task_id NULL
189 -- X_organization_id From Task/Proj Org
190 -- X_completion_date sysdate or X_accrue_thr
191 -- X_event_description default event descr
192 -- X_attribute_category NULL
193 -- X_attribute1-10 NULL
194 --
195 -- Audit fields can be used for storing additional audit info for events
196 --
197 -- X_audit_amount1-10 Audit Amounts
198 -- X_audit_cost_budget_type_code cost budget type code
199 -- X_audit_rev_budget_type_code revenue budget type code
200 -- X_error_message error message returned
201 -- X_status status returned
202
203 procedure get_budget_amount(
204 X2_project_id NUMBER,
205 X2_task_id NUMBER DEFAULT NULL,
206 X2_revenue_amount OUT NOCOPY REAL,
207 X2_cost_amount OUT NOCOPY REAL,
208 P_cost_budget_type_code IN VARCHAR2 DEFAULT NULL,
209 P_rev_budget_type_code IN VARCHAR2 DEFAULT NULL,
210 P_cost_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin Plan impact */
211 P_rev_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin Plan impact */
212 X_cost_budget_type_code OUT NOCOPY VARCHAR2,
213 X_rev_budget_type_code OUT NOCOPY VARCHAR2,
214 X_error_message OUT NOCOPY VARCHAR2,
215 X_status OUT NOCOPY NUMBER
216 );
217
218
219 procedure get_amount( X_project_id NUMBER,
220 X_request_id NUMBER,
221 X_calling_process VARCHAR2,
222 X_calling_place VARCHAR2 DEFAULT NULL,
223 X_which_amount VARCHAR2 DEFAULT 'R',
224 X_amount OUT NOCOPY NUMBER,
225 X_top_task_id NUMBER DEFAULT NULL,
226 X_system_linkage VARCHAR2 DEFAULT NULL,
227 X_cost_base VARCHAR2 DEFAULT NULL,
228 X_CP_structure VARCHAR2 DEFAULT NULL,
229 X_CB_type VARCHAR2 DEFAULT NULL);
230
231
232 PROCEDURE insert_message (X_inserting_procedure_name VARCHAR2,
233 X_message VARCHAR2,
234 X_attribute1 VARCHAR2 DEFAULT NULL,
235 X_attribute2 VARCHAR2 DEFAULT NULL,
236 X_attribute3 VARCHAR2 DEFAULT NULL,
237 X_attribute4 VARCHAR2 DEFAULT NULL,
238 X_attribute5 VARCHAR2 DEFAULT NULL,
239 X_attribute6 VARCHAR2 DEFAULT NULL,
240 X_attribute7 VARCHAR2 DEFAULT NULL,
241 X_attribute8 VARCHAR2 DEFAULT NULL,
242 X_attribute9 VARCHAR2 DEFAULT NULL,
243 X_attribute10 VARCHAR2 DEFAULT NULL,
244 X_attribute11 VARCHAR2 DEFAULT NULL,
245 X_attribute12 VARCHAR2 DEFAULT NULL,
246 X_attribute13 VARCHAR2 DEFAULT NULL,
247 X_attribute14 VARCHAR2 DEFAULT NULL,
248 X_attribute15 VARCHAR2 DEFAULT NULL,
249 X_error_message OUT NOCOPY VARCHAR2,
250 X_status OUT NOCOPY NUMBER
251 );
252
253 PROCEDURE insert_event (X_rev_amt REAL DEFAULT NULL,
254 X_bill_amt REAL DEFAULT NULL,
255 X_project_id NUMBER DEFAULT NULL,
256 X_event_type VARCHAR2 DEFAULT NULL,
257 X_top_task_id NUMBER DEFAULT NULL,
258 X_organization_id NUMBER DEFAULT NULL,
259 X_completion_date DATE DEFAULT NULL,
260 X_event_description VARCHAR2 DEFAULT NULL,
261 X_event_num_reversed NUMBER DEFAULT NULL,
262 X_attribute_category VARCHAR2 DEFAULT NULL,
263 X_attribute1 VARCHAR2 DEFAULT NULL,
264 X_attribute2 VARCHAR2 DEFAULT NULL,
265 X_attribute3 VARCHAR2 DEFAULT NULL,
266 X_attribute4 VARCHAR2 DEFAULT NULL,
267 X_attribute5 VARCHAR2 DEFAULT NULL,
268 X_attribute6 VARCHAR2 DEFAULT NULL,
269 X_attribute7 VARCHAR2 DEFAULT NULL,
270 X_attribute8 VARCHAR2 DEFAULT NULL,
271 X_attribute9 VARCHAR2 DEFAULT NULL,
272 X_attribute10 VARCHAR2 DEFAULT NULL,
273 X_audit_amount1 IN NUMBER DEFAULT NULL,
274 X_audit_amount2 IN NUMBER DEFAULT NULL,
275 X_audit_amount3 IN NUMBER DEFAULT NULL,
276 X_audit_amount4 IN NUMBER DEFAULT NULL,
277 X_audit_amount5 IN NUMBER DEFAULT NULL,
278 X_audit_amount6 IN NUMBER DEFAULT NULL,
279 X_audit_amount7 IN NUMBER DEFAULT NULL,
280 X_audit_amount8 IN NUMBER DEFAULT NULL,
281 X_audit_amount9 IN NUMBER DEFAULT NULL,
282 X_audit_amount10 IN NUMBER DEFAULT NULL,
283 X_audit_cost_budget_type_code IN VARCHAR2 DEFAULT NULL,
284 X_audit_rev_budget_type_code IN VARCHAR2 DEFAULT NULL,
285 X_inventory_org_id IN NUMBER DEFAULT NULL,
286 X_inventory_item_id IN NUMBER DEFAULT NULL,
287 X_quantity_billed IN NUMBER DEFAULT NULL,
288 X_uom_code IN VARCHAR2 DEFAULT NULL,
289 X_unit_price IN NUMBER DEFAULT NULL,
290 X_reference1 IN VARCHAR2 DEFAULT NULL,
291 X_reference2 IN VARCHAR2 DEFAULT NULL,
292 X_reference3 IN VARCHAR2 DEFAULT NULL,
293 X_reference4 IN VARCHAR2 DEFAULT NULL,
294 X_reference5 IN VARCHAR2 DEFAULT NULL,
295 X_reference6 IN VARCHAR2 DEFAULT NULL,
296 X_reference7 IN VARCHAR2 DEFAULT NULL,
297 X_reference8 IN VARCHAR2 DEFAULT NULL,
298 X_reference9 IN VARCHAR2 DEFAULT NULL,
299 X_reference10 IN VARCHAR2 DEFAULT NULL,
300 X_txn_currency_code IN VARCHAR2 DEFAULT NULL,
301 X_project_rate_type IN VARCHAR2 DEFAULT NULL,
302 X_project_rate_date IN DATE DEFAULT NULL,
303 X_project_exchange_rate IN NUMBER DEFAULT NULL,
304 X_project_func_rate_type IN VARCHAR2 DEFAULT NULL,
305 X_project_func_rate_date IN DATE DEFAULT NULL,
306 X_project_func_exchange_rate IN NUMBER DEFAULT NULL,
307 X_funding_rate_type IN VARCHAR2 DEFAULT NULL,
308 X_funding_rate_date IN DATE DEFAULT NULL,
309 X_funding_exchange_rate IN NUMBER DEFAULT NULL,
310 X_zero_revenue_amount_flag IN VARCHAR2 DEFAULT NULL, /* Funding MRC Changes */
314 X_status OUT NOCOPY NUMBER
311 X_audit_cost_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin plan impact */
312 X_audit_rev_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin plan impact */
313 X_error_message OUT NOCOPY VARCHAR2,
315 );
316
317 function GET_MRC_FOR_FUND_FLAG return boolean;
318
319 -- Following APIs added for FP_M changes for Customer at Top Task
320 -- If the project is implemented with Top Task Customer enabled then
321 -- return the value as 'Y'
322 Function Get_Top_Task_Customer_Flag (
323 P_Project_ID IN NUMBER
324 ) Return Varchar2 ;
325
326 -- If the project is implemented with Invoice Method Override Flag enabled then
327 -- return the value as 'Y'
328 Function Get_Inv_Method_Override_Flag (
329 P_Project_ID IN NUMBER
330 ) Return Varchar2 ;
331 -- End of APIs added for FP_M changes
332
333 END pa_billing_pub;
[Home] [Help]