4 /* Bug fix:4230258 cache the project type and project id variables */
1 package PA_COST1 AUTHID CURRENT_USER as
2 -- $Header: PAXCSR1S.pls 120.1 2011/07/08 12:21:36 racheruv ship $
3
5 G_project_type pa_projects_all.project_type%type;
6 G_project_id pa_projects_all.project_id%type;
7 G_burden_costFlag Varchar2(10);
8 FUNCTION check_proj_burdened
9 (p_project_type IN VARCHAR2
10 ,p_project_id IN NUMBER ) RETURN VARCHAR2;
11
12 /* This API derives transaction raw cost, burden costs in transaction currency. ie. the currency associated
13 * with the rate schedule.
14 * This API will derive the costs based on ACTUALs or PLANNING rates
15 * The following are the rules to derive cost rates for the planning resource
16 * 1. By default the rate engine will derive the raw and burden costs based on the transaction currency.
17 * I.e. The currency associated with the rate schedule. If the transaction override currency is passed then costs will be
18 * converted from transaction currency to override currency.
19 * 2. If the override cost rate is passed then rate engine will derive the actual raw cost and raw cost rates
20 * based on the override cost rate
24 * the burden costs will be derived based on the passed value transaction raw cost and transaction currency.
21 * 3. If the override burden multiplier is passed, the rate engine will derive the burden costs
22 * based on the override burden multiplier.
23 * 4. If the parameter rate based flag is 'N' then rate engine will not derive raw cost instead,
25 * 5. Rates will be derived based on the in parameter p_exp_item_date
26 * This API returns x_return_status as 'S' for successful rate 'E' if no rate found 'U' in case of unexpected errors
27 *
28 * NOTE: For BOM related transactions the following params needs to be passed
29 * p_mfc_cost_source Required possible values be
30 * 1 - Return item cost from valuation cost type.
31 * 2 - Return item cost from user-provided cost type.
32 * 3 - Return item cost as the list price per unit from item definition.
33 * 4 - Return item cost as average of the last 5 PO receipts of this item.
34 * PO price includes non-recoverable tax.
35 * p_mfd_cost_type_id Optional param default is 0
36 * p_exp_organization_id Required
37 * p_BOM_resource_id Required
38 * p_inventory_item_id Required
39 *
40 * 6. The following parameters should be passed in order to get the planning rates
41 * p_calling_mode = 'PLAN_RATES' for planning rate schedules
42 * 'ACTUAL_RATES' for actuals
43 * p_plan_cost_job_rate_sch_id
44 * p_plan_cost_emp_rate_sch_id
45 * p_plan_cost_non_labor_rate_sch_id
46 * p_plan_burden_cost_sch_id
47 *
48 */
49 PROCEDURE Get_Plan_Actual_Cost_Rates
50 (p_calling_mode IN VARCHAR2 DEFAULT 'ACTUAL_RATES'
51 ,p_project_type IN VARCHAR2
52 ,p_project_id IN NUMBER
53 ,p_task_id IN NUMBER
54 ,p_top_task_id IN NUMBER
55 ,p_Exp_item_date IN DATE
56 ,p_expenditure_type IN VARCHAR2
57 ,p_expenditure_OU IN NUMBER
58 ,p_project_OU IN NUMBER
59 ,p_Quantity IN NUMBER
60 ,p_resource_class IN VARCHAR2
61 ,p_person_id IN NUMBER DEFAULT NULL
62 ,p_non_labor_resource IN VARCHAR2 DEFAULT NULL
63 ,p_NLR_organization_id IN NUMBER DEFAULT NULL
64 ,p_override_organization_id IN NUMBER DEFAULT NULL
65 ,p_incurred_by_organization_id IN NUMBER DEFAULT NULL
66 ,p_inventory_item_id IN NUMBER DEFAULT NULL
67 ,p_BOM_resource_id IN NUMBER DEFAULT NULL
68 ,p_override_trxn_curr_code IN VARCHAR2 DEFAULT NULL
69 ,p_override_burden_cost_rate IN NUMBER DEFAULT NULL
70 ,p_override_trxn_cost_rate IN NUMBER DEFAULT NULL
71 ,p_override_trxn_raw_cost IN NUMBER DEFAULT NULL
72 ,p_override_trxn_burden_cost IN NUMBER DEFAULT NULL
73 ,p_mfc_cost_type_id IN NUMBER DEFAULT 0
74 ,p_mfc_cost_source IN NUMBER DEFAULT 2
75 ,p_item_category_id IN NUMBER DEFAULT NULL
76 ,p_job_id IN NUMBER DEFAULT NULL
77 ,p_plan_cost_job_rate_sch_id IN NUMBER DEFAULT NULL
78 ,p_plan_cost_emp_rate_sch_id IN NUMBER DEFAULT NULL
79 ,p_plan_cost_nlr_rate_sch_id IN NUMBER DEFAULT NULL
80 ,p_plan_cost_burden_sch_id IN NUMBER DEFAULT NULL
81 ,p_resource_assignment_id IN NUMBER DEFAULT NULL /* bug 12374022 */
82 ,x_trxn_curr_code OUT NOCOPY VARCHAR2
83 ,x_trxn_raw_cost OUT NOCOPY NUMBER
84 ,x_trxn_raw_cost_rate OUT NOCOPY NUMBER
85 ,x_trxn_burden_cost OUT NOCOPY NUMBER
86 ,x_trxn_burden_cost_rate OUT NOCOPY NUMBER
87 ,x_burden_multiplier OUT NOCOPY NUMBER
88 ,x_cost_ind_compiled_set_id OUT NOCOPY NUMBER
89 ,x_raw_cost_rejection_code OUT NOCOPY VARCHAR2
90 ,x_burden_cost_rejection_code OUT NOCOPY VARCHAR2
91 ,x_return_status OUT NOCOPY VARCHAR2
92 ,x_error_msg_code OUT NOCOPY VARCHAR2 );
93
94 PROCEDURE Get_Non_Labor_raw_cost
95 (p_project_id IN NUMBER
96 ,p_task_id IN NUMBER
97 ,p_non_labor_resource IN VARCHAR2
98 ,p_nlr_organization_id IN NUMBER
99 ,p_expenditure_type IN VARCHAR2
100 ,p_exp_item_date IN DATE
101 ,p_override_organization_id IN NUMBER
102 ,p_quantity IN NUMBER
103 ,p_org_id IN NUMBER
104 ,p_nlr_schedule_id IN NUMBER
105 ,p_nlr_trxn_cost_rate IN NUMBER DEFAULT NULL
106 ,p_nlr_trxn_raw_cost IN NUMBER DEFAULT NULL
107 ,p_nlr_trxn_currency_code IN VARCHAR2 DEFAULT NULL
111 ,x_return_status OUT NOCOPY VARCHAR2
108 ,x_trxn_raw_cost_rate OUT NOCOPY NUMBER
109 ,x_trxn_raw_cost OUT NOCOPY NUMBER
110 ,x_txn_currency_code OUT NOCOPY VARCHAR2
112 ,x_error_msg_code OUT NOCOPY VARCHAR2
113 );
114
115 /* This is a wrapper api to derive compiled set id and burden multiplier
116 * Which in turn makes calls to pa_cost_plus package
117 */
118 PROCEDURE Get_burden_sch_details
119 (p_calling_mode IN VARCHAR2 DEFAULT 'ACTUAL_RATES'
120 ,p_exp_item_id IN NUMBER
121 ,p_trxn_type IN VARCHAR2
122 ,p_project_type IN VARCHAR2
123 ,p_project_id IN NUMBER
124 ,p_task_id IN NUMBER
125 ,p_exp_organization_id IN NUMBER
126 /* bug fix:4232181 Derive organization override for burden calculate */
127 ,p_overide_organization_id IN NUMBER DEFAULT NULL
128 ,p_person_id IN NUMBER DEFAULT NULL
129 /* end of bug fix:4232181 */
130 ,p_expenditure_type IN VARCHAR2
131 ,p_schedule_type IN VARCHAR2 DEFAULT 'C'
132 ,p_exp_item_date IN DATE
133 ,p_trxn_curr_code IN VARCHAR2
134 ,p_burden_schedule_id IN NUMBER DEFAULT NULL
135 ,x_schedule_id OUT NOCOPY NUMBER
136 ,x_sch_revision_id OUT NOCOPY NUMBER
137 ,x_sch_fixed_date OUT NOCOPY DATE
138 ,x_cost_base OUT NOCOPY VARCHAR2
139 ,x_cost_plus_structure OUT NOCOPY VARCHAR2
140 ,x_compiled_set_id OUT NOCOPY NUMBER
141 ,x_burden_multiplier OUT NOCOPY NUMBER
142 ,x_return_status OUT NOCOPY VARCHAR2
143 ,x_error_msg_code OUT NOCOPY VARCHAR2 );
144
145 /* This API derives the cost rates based on the bill rate schedules
146 * The possible values for the params
147 * p_schedule_type 'EMPLOYEE' / 'JOB' / 'NON-LABOR'
148 * p_rate_sch_id based on the schedule type the corresponding rate schedule id must be passed
149 */
150 PROCEDURE get_RateSchDetails
151 (p_schedule_type IN Varchar2
152 ,p_rate_sch_id IN Number
153 ,p_person_id IN Number
154 ,p_job_id IN Number
155 ,p_non_labor_resource IN Varchar2
156 ,p_expenditure_type IN Varchar2
157 ,p_rate_organization_id IN Number
158 ,p_exp_item_date IN Date
159 ,p_org_id IN Number
160 ,x_currency_code OUT NOCOPY Varchar2
161 ,x_cost_rate OUT NOCOPY Number
162 ,x_markup_percent OUT NOCOPY Number
163 ,x_return_status OUT NOCOPY Varchar2
164 ,x_error_msg_code OUT NOCOPY Varchar2 );
165
166 /* This API converts the cost amount from transaction currency to
167 * project and project functional currency based on the
168 * planning transaction currency conversion attributes
169 * NOTE: Please donot use this API for actual cost conversion
170 */
171 PROCEDURE Convert_COST_TO_PC_PFC
172 (p_txn_raw_cost IN NUMBER
173 ,p_txn_burden_cost IN NUMBER
174 ,p_txn_quantity IN NUMBER
175 ,p_txn_curr_code IN VARCHAR2
176 ,p_txn_date IN DATE
177 ,p_project_id IN NUMBER
178 ,p_budget_Version_id IN NUMBER
179 ,p_budget_line_id IN NUMBER
180 ,x_project_curr_code OUT NOCOPY VARCHAR2
181 ,x_projfunc_curr_code OUT NOCOPY VARCHAR2
182 ,x_proj_raw_cost OUT NOCOPY NUMBER
183 ,x_proj_raw_cost_rate OUT NOCOPY NUMBER
184 ,x_proj_burdened_cost OUT NOCOPY NUMBER
185 ,x_proj_burdened_cost_rate OUT NOCOPY NUMBER
186 ,x_projfunc_raw_cost OUT NOCOPY NUMBER
187 ,x_projfunc_raw_cost_rate OUT NOCOPY NUMBER
188 ,x_projfunc_burdened_cost OUT NOCOPY NUMBER
189 ,x_projfunc_burdened_cost_rate OUT NOCOPY NUMBER
190 ,x_return_status OUT NOCOPY VARCHAR2
191 ,x_error_msg_code OUT NOCOPY VARCHAR2
192 );
193
194 /* This is an internal API which will be called from Convert_COSTto PC and PFC api
198 PROCEDURE Convert_amounts
195 * this api does the calculation fo amount conversion based on the planning conversion
196 * attributes
197 */
199 (p_calling_mode IN VARCHAR2 DEFAULT 'PC'
200 ,p_txn_raw_cost IN NUMBER
201 ,p_txn_burden_cost IN NUMBER
202 ,p_txn_quantity IN NUMBER
203 ,p_Conversion_Date IN DATE
204 ,p_From_curr_code IN VARCHAR2
205 ,p_To_curr_code IN VARCHAR2
206 ,p_To_Curr_Rate_Type IN VARCHAR2
207 ,p_To_Curr_Exchange_Rate IN NUMBER
208 ,x_To_Curr_raw_cost OUT NOCOPY NUMBER
209 ,x_To_Curr_raw_cost_rate OUT NOCOPY NUMBER
210 ,x_To_Curr_burden_cost OUT NOCOPY NUMBER
211 ,x_To_Curr_burden_cost_rate OUT NOCOPY NUMBER
212 ,x_To_Curr_Exchange_Rate OUT NOCOPY NUMBER
213 ,x_return_status OUT NOCOPY VARCHAR2
214 ,x_error_msg_code OUT NOCOPY VARCHAR2
215 );
216
217
218 end PA_COST1;