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