1 package PA_COST_PLUS AUTHID CURRENT_USER as
2 -- $Header: PAXCCPES.pls 120.8.12020000.3 2013/03/06 09:53:48 admarath ship $
3 /*#
4 * Oracle Projects provides a procedure you can use to call the Cost Plus Application Programming Interface.
5 * @rep:scope public
6 * @rep:product PA
7 * @rep:lifecycle active
8 * @rep:displayname Cost Plus Applications Programming Interface (API)
9 * @rep:compatibility S
10 * @rep:category BUSINESS_ENTITY PA_PROJECT
11 * @rep:category BUSINESS_ENTITY PA_PROJ_COST
12 * @rep:doccd 120pjapi.pdf See the Oracle Projects API's, Client Extensions, and Open Interfaces Reference
13 */
14
15 TYPE precedence_tab_type IS TABLE OF pa_compiled_multipliers.precedence%TYPE
16 INDEX BY BINARY_INTEGER;
17 TYPE ind_cost_code_tab_type IS TABLE OF
18 pa_compiled_multipliers.ind_cost_code%TYPE INDEX BY BINARY_INTEGER;
19 TYPE multiplier_tab_type IS TABLE OF pa_ind_cost_multipliers.multiplier%TYPE
20 INDEX BY BINARY_INTEGER;
21 /*2933915*/
22 TYPE org_tab_type is TABLE of pa_ind_cost_multipliers.organization_id%TYPE
23 INDEX BY BINARY_INTEGER;
24 /*2933915*/
25
26 /* Bug 3786374 : The SQL retriving ind_rate_schedule_type exceeded apps execution threshold */
27 g_sch_id pa_ind_rate_schedules.ind_rate_sch_id%type;
28 g_ind_rate_schedule_type pa_ind_rate_schedules.ind_rate_schedule_type%type;
29
30 /* S.N. Bug 3938479 */
31
32 g_rate_sch_rev_id pa_ind_rate_sch_revisions.ind_rate_sch_revision_id%type;
33 g_org_id pa_ind_cost_multipliers.organization_id%type;
34 g_org_override NUMBER;
35
36 /* E.N. Bug 3938479 */
37
38 HASHKEY VARCHAR2(250); /*bug 14330754 */
39 TYPE org_override_tab IS table of NUMBER index by varchar2(250); /*bug 14330754*/
40 G_org_override_tab org_override_tab; /*bug 14330754*/
41
42
43 procedure compile_org_rates(rate_sch_rev_id IN number,
44 org_id IN Number,
45 org_struc_ver_id IN Number,
46 start_org IN Number,
47 status IN OUT NOCOPY number,
48 stage IN OUT NOCOPY number);
49
50 procedure compile_org_hierarchy_rates(rate_sch_rev_id IN number,
54 stage IN OUT NOCOPY number);
51 org_id IN number,
52 comp_type IN varchar2,
53 status IN OUT NOCOPY number,
55
56 procedure new_organization(errbuf IN OUT NOCOPY varchar2,
57 retcode IN OUT NOCOPY varchar2,
58 organization_id IN varchar2);
59
60 procedure compile_schedule(errbuf IN OUT NOCOPY varchar2,
61 retcode IN OUT NOCOPY varchar2,
62 sch_rev_id IN varchar2);
63
64 procedure compile_all(errbuf IN OUT NOCOPY varchar2,
65 retcode IN OUT NOCOPY varchar2);
66
67 /*
68 Multi-Currency related changes :
69 Two more parameters added: indirect_cost_acct
70 indirect_cost_denom
71 */
72 procedure get_exp_item_indirect_cost(exp_item_id IN Number,
73 schedule_type IN Varchar2,
74 indirect_cost IN OUT NOCOPY Number,
75 indirect_cost_acct IN OUT NOCOPY NUMBER,
76 indirect_cost_denom IN OUT NOCOPY NUMBER,
77 indirect_cost_project IN OUT NOCOPY NUMBER, /* ProjCurr changes*/
78 rate_sch_rev_id IN OUT NOCOPY Number,
79 compiled_set_id IN OUT NOCOPY Number,
80 status IN OUT NOCOPY Number,
81 stage IN OUT NOCOPY Number);
82
83 procedure get_exp_item_burden_amount(exp_item_id IN Number,
84 schedule_type IN Varchar2,
85 burden_amount IN OUT NOCOPY Number,
86 rate_sch_rev_id IN OUT NOCOPY Number,
87 compiled_set_id IN OUT NOCOPY Number,
88 status IN OUT NOCOPY Number,
89 stage IN OUT NOCOPY Number);
90
91 procedure populate_indirect_cost(update_count IN OUT NOCOPY Number);
92
93 procedure get_indirect_cost_sum (org_id IN number,
94 c_base IN varchar2,
95 rate_sch_rev_id IN number,
96 direct_cost IN number,
97 precision IN number,
98 indirect_cost_sum IN OUT NOCOPY number,
99 status IN OUT NOCOPY number,
100 stage IN OUT NOCOPY number);
101
102 -- pragma RESTRICT_REFERENCES (get_indirect_cost_sum, WNDS, WNPS );
103
104 /*
105 procedure get_detail_indirect_costs(exp_item_id IN Number,
106 schedule_type IN Varchar2,
107 ind_cost_code_num IN OUT NOCOPY Number,
108 c_base IN OUT NOCOPY Varchar2,
109 precedence IN OUT NOCOPY precedence_tab_type,
110 ind_cost_code IN OUT NOCOPY ind_cost_code_tab_type,
111 compiled_multiplier IN OUT NOCOPY multiplier_tab_type,
112 indirect_cost IN OUT NOCOPY multiplier_tab_type,
113 status IN OUT NOCOPY number,
114 stage IN OUT NOCOPY number);
115 */
116
117 procedure view_indirect_cost( transaction_id IN Number,
118 transaction_type IN Varchar2,
119 task_id IN Number,
120 effective_date IN Date,
121 expenditure_type IN Varchar2,
122 organization_id IN Number,
123 schedule_type IN Varchar2,
124 direct_cost IN Number,
125 indirect_cost IN OUT NOCOPY Number,
126 status IN OUT NOCOPY Number,
127 stage IN OUT NOCOPY Number);
128 /* Bug 3786374 : Caching introduced in get_revision_by_date and hence in this procedure also */
129 --- pragma RESTRICT_REFERENCES (view_indirect_cost, WNDS, WNPS );
130
131
132 /*#
133 * This procedure retrieves an amount based on your burden cost setup. You can
134 * specify the burden schedule, effective date, expenditure type, and
135 * organization, and retrieve the burden cost amount based on the criteria you specify.
136 * @param burden_schedule_id The schedule ID of the burden schedule used to calculate the burden amount
137 * @rep:paraminfo {@rep:required}
138 * @param effective_date The date used to identify the burden schedule revision to calculate the burden amount
139 * @rep:paraminfo {@rep:required}
140 * @param expenditure_type The type of expenditure item used to find a cost base
141 * @rep:paraminfo {@rep:required}
142 * @param organization_id The ID of the organization used to find a multiplier
143 * @rep:paraminfo {@rep:required}
144 * @param raw_amount The raw amount for which the burden amount is calculated
145 * @rep:paraminfo {@rep:required}
146 * @param burden_amount The calculated burden amount
147 * @rep:paraminfo {@rep:required}
148 * @param burden_sch_rev_id The schedule revision ID of the burden schedule used to calculate the burden amount
149 * @rep:paraminfo {@rep:required}
150 * @param compiled_set_id The ID of the active compiled set used to calculate the burden amount
151 * @rep:paraminfo {@rep:required}
155 * @rep:paraminfo {@rep:required}
152 * @param status The processing status of the procedure
153 * @rep:paraminfo {@rep:required}
154 * @param stage The exit stage of the procedure
156 * @rep:scope public
157 * @rep:lifecycle active
158 * @rep:displayname Get Burden Amount
159 * @rep:compatibility S
160 */
161 procedure get_burden_amount(burden_schedule_id IN Number,
162 effective_date IN Date,
163 expenditure_type IN Varchar2,
164 organization_id IN Number,
165 raw_amount IN Number,
166 burden_amount IN OUT NOCOPY Number,
167 burden_sch_rev_id IN OUT NOCOPY Number,
168 compiled_set_id IN OUT NOCOPY Number,
169 status IN OUT NOCOPY Number,
170 stage IN OUT NOCOPY Number);
171
172 /* added for bug#3117191 */
173 procedure get_burden_amount1(--burden_schedule_id IN Number,
174 --effective_date IN Date,
175 expenditure_type IN Varchar2,
176 organization_id IN Number,
177 raw_amount IN Number,
178 burden_amount IN OUT NOCOPY Number,
179 burden_sch_rev_id IN OUT NOCOPY Number,
180 compiled_set_id IN OUT NOCOPY Number,
181 status IN OUT NOCOPY Number,
182 stage IN OUT NOCOPY Number);
183 /* end for bug#3117191 */
184
185 procedure get_hierarchy_from_revision(p_sch_rev_id IN number,
186 x_org_struc_ver_id OUT NOCOPY number,
187 x_start_org OUT NOCOPY number,
188 x_status OUT NOCOPY number,
189 x_stage OUT NOCOPY number);
190
191 procedure find_rate_sch_rev_id(
192 transaction_id IN Number,
193 transaction_type IN Varchar2,
194 t_id IN Number,
195 schedule_type IN Varchar2,
196 exp_item_date IN Date,
197 sch_id IN OUT NOCOPY Number,
198 rate_sch_rev_id IN OUT NOCOPY Number,
199 sch_fixed_date IN OUT NOCOPY Date,
200 status IN OUT NOCOPY Number,
201 stage IN OUT NOCOPY Number);
202 /* Bug# 3786374 Used Caching in get_revision_by_date. Hence in this procedure also. */
203 --- pragma RESTRICT_REFERENCES (find_rate_sch_rev_id, WNDS, WNPS );
204
205 procedure get_rate_sch_rev_id(exp_item_id IN Number,
206 schedule_type IN Varchar2,
207 rate_sch_rev_id IN OUT NOCOPY Number,
208 status IN OUT NOCOPY Number,
209 stage IN OUT NOCOPY Number);
210
211 procedure get_cost_base(exp_type IN varchar2,
212 cp_structure IN varchar2,
213 c_base IN OUT NOCOPY varchar2,
214 status IN OUT NOCOPY number,
215 stage IN OUT NOCOPY number);
216
217 -- pragma RESTRICT_REFERENCES (get_cost_base, WNDS, WNPS );
218
219 procedure get_cost_plus_structure(rate_sch_rev_id IN Number,
220 cp_structure IN OUT NOCOPY Varchar2,
221 status IN OUT NOCOPY number,
222 stage IN OUT NOCOPY number);
223
224 -- pragma RESTRICT_REFERENCES (get_cost_plus_structure, WNDS, WNPS );
225
226 procedure get_organization_id(exp_item_id IN Number,
227 organization_id IN OUT NOCOPY Number,
228 status IN OUT NOCOPY Number,
229 stage IN OUT NOCOPY Number);
230
231 procedure get_compiled_set_id(rate_sch_rev_id IN Number,
232 org_id IN Number,
233 c_base IN Varchar2, /*2933915*/
234 compiled_set_id IN OUT NOCOPY Number,
235 status IN OUT NOCOPY Number,
236 stage IN OUT NOCOPY Number);
237
238 -- pragma RESTRICT_REFERENCES (get_compiled_set_id, WNDS, WNPS );
239
240 procedure get_revision_by_date(sch_id IN Number,
241 sch_fixed_date IN Date,
242 exp_item_date IN Date,
243 rate_sch_rev_id IN OUT NOCOPY Number,
244 status IN OUT NOCOPY Number,
245 stage IN OUT NOCOPY Number);
246
247 /* Bug 3786374 : Used caching */
248 --- pragma RESTRICT_REFERENCES (get_revision_by_date, WNDS, WNPS );
249
250 procedure check_revision_used(rate_sch_rev_id IN number,
251 status IN OUT NOCOPY number,
252 stage IN OUT NOCOPY number);
253
257
254 procedure check_structure_used(structure IN varchar2,
255 status IN OUT NOCOPY number,
256 stage IN OUT NOCOPY number);
258 procedure copy_structure(source IN varchar2,
259 destination IN varchar2,
260 status IN OUT NOCOPY number,
261 stage IN OUT NOCOPY number);
262
263 /*
264 procedure copy_multipliers(source IN number,
265 destination IN number,
266 status IN OUT NOCOPY number,
267 stage IN OUT NOCOPY number);
268 */
269
270
271 procedure mark_impacted_exp_items(rate_sch_rev_id IN number,
272 status IN OUT NOCOPY number,
273 stage IN OUT NOCOPY number);
274
275 procedure mark_prev_rev_exp_items(compiled_set_id IN number,
276 rev_type IN varchar2,
277 reason IN varchar2,
278 l_start_date IN date,
279 l_end_date IN date,
280 status IN OUT NOCOPY number,
281 stage IN OUT NOCOPY number);
282
283 /*S.N. Bug 4527736 Changed Procedure Signature.*/
284 procedure add_adjustment_activity(
285 --compiled_set_id IN number,
286 -- p_cost_base IN pa_cost_bases.cost_base%TYPE
287 -- ,p_cost_plus_structure IN pa_cost_plus_structures.cost_plus_structure%TYPE,
288 -- cost_adj_reason IN varchar2,
289 -- rev_adj_reason IN varchar2,
290 -- inv_adj_reason IN varchar2,
291 -- tp_adj_reason IN varchar2,
292 l_expenditure_item_id_tab IN PA_PLSQL_DATATYPES.IDTABTYP
293 ,l_adj_type_tab IN PA_PLSQL_DATATYPES.Char30TabTyp
294 ,status IN OUT NOCOPY number
295 ,stage IN OUT NOCOPY number);
296 /*E.N. Bug 4527736 Changed Procedure Signature.*/
297
298 procedure disable_rate_sch_revision(rate_sch_rev_id IN number,
299 ver_id IN number, /*2933915*/
300 org_id IN number, /*2933915*/
301 status IN OUT NOCOPY number,
302 stage IN OUT NOCOPY number);
303
304 procedure disable_sch_rev_org(rate_sch_rev_id IN number,
305 org_id IN number,
306 status IN OUT NOCOPY number,
307 stage IN OUT NOCOPY number);
308
309 procedure get_indirect_cost_amounts (x_indirect_cost_costing IN OUT NOCOPY number,
310 x_indirect_cost_revenue IN OUT NOCOPY number,
311 x_indirect_cost_invoice IN OUT NOCOPY number,
312 x_task_id IN number,
313 x_gl_date IN date,
314 x_expenditure_type IN varchar2,
315 x_organization_id IN number,
316 x_direct_cost IN number,
317 x_return_status IN OUT NOCOPY number,
318 x_stage IN OUT NOCOPY number);
319
320
321 procedure get_ind_rate_sch_rev(x_ind_rate_sch_name IN OUT NOCOPY varchar2,
322 x_ind_rate_sch_revision IN OUT NOCOPY varchar2,
323 x_ind_rate_sch_revision_type IN OUT NOCOPY varchar2,
324 x_start_date_active IN OUT NOCOPY date,
325 x_end_date_active IN OUT NOCOPY date,
326 x_task_id IN number,
327 x_gl_date IN date,
328 x_detail_type_flag IN varchar2,
329 x_expenditure_type IN varchar2,
330 x_cost_base IN OUT NOCOPY varchar2,
331 x_ind_compiled_set_id IN OUT NOCOPY number,
332 x_organization_id IN number,
333 x_return_status IN OUT NOCOPY number,
334 x_stage IN OUT NOCOPY number);
335
336 /*
337 Multi-Currency related changes :
338 New Procedure added
339 */
340 PROCEDURE Get_Compiled_Multiplier( P_Org_Id IN NUMBER,
341 P_C_Base IN VARCHAR2,
342 P_Rate_Sch_Rev_Id IN NUMBER,
343 P_Compiled_Multiplier IN OUT NOCOPY NUMBER,
344 P_Status IN OUT NOCOPY NUMBER,
345 P_Stage IN OUT NOCOPY NUMBER );
346
347 /*Bug# 2110452:To implement the same logic as is used in R10.7/R11.0 for
348 burden cost calculation*/
349
350 procedure get_indirect_cost_sum1 (org_id IN number,
351 c_base IN varchar2,
352 rate_sch_rev_id IN number,
353 direct_cost IN number,
354 direct_cost_denom IN number,
355 direct_cost_acct IN number,
356 direct_cost_project IN number,
357 precision IN number,
358 indirect_cost_sum IN OUT NOCOPY number,
359 indirect_cost_denom_sum IN OUT NOCOPY number,
360 indirect_cost_acct_sum IN OUT NOCOPY number,
361 indirect_cost_project_sum IN OUT NOCOPY number,
362 l_projfunc_currency_code IN varchar2,
363 l_project_currency_code IN varchar2,
364 l_acct_currency_code IN varchar2,
365 l_denom_currency_code IN varchar2,
366 status IN OUT NOCOPY number,
367 stage IN OUT NOCOPY number);
368 /*End of changes for bug# 2110452*/
369
370 FUNCTION Get_Mltplr_For_Compiled_Set( P_Ind_Compiled_Set_ID IN NUMBER)
371 RETURN Number;
372
373 -- PRAGMA RESTRICT_REFERENCES (Get_Mltplr_For_Compiled_Set, WNDS, WNPS );
374
375 FUNCTION check_for_explicit_multiplier(rate_sch_rev_id IN NUMBER,org_id IN NUMBER) /*3016281*/
376 RETURN NUMBER ;
377 /**2933915 :Added two new procedures **/
378 procedure delete_rate_sch_revision(rate_sch_rev_id IN number,
379 ver_id IN number,
380 org_id IN number,
381 status IN OUT NOCOPY number,
382 stage IN OUT NOCOPY number) ;
383
384 procedure find_impacted_top_org(rate_sch_rev_id IN number,
385 ver_id IN number ,
386 start_org IN number ,
387 org_tab OUT NOCOPY org_tab_type,
388 status IN OUT NOCOPY number) ;
389
390 /*End of changes for 2933915*/
391
392 /* Added procedure for bug 9643997 */
393 PROCEDURE validate_CrDr_lines ( p_request_id IN NUMBER
394 ,x_return_status OUT NOCOPY NUMBER
395 ,x_error_code OUT NOCOPY VARCHAR2
396 ,x_error_stage OUT NOCOPY NUMBER
397 );
398
399 end PA_COST_PLUS ;
[Home] [Help]