1 package PA_COST1 as
2 -- $Header: PAXCSR1S.pls 120.0 2005/06/03 14:18:07 appldev noship $
3
4 /* Bug fix:4230258 cache the project type and project id variables */
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
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,
24 * the burden costs will be derived based on the passed value transaction raw cost and transaction currency.
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 ,x_trxn_curr_code OUT NOCOPY VARCHAR2
82 ,x_trxn_raw_cost OUT NOCOPY NUMBER
83 ,x_trxn_raw_cost_rate OUT NOCOPY NUMBER
84 ,x_trxn_burden_cost OUT NOCOPY NUMBER
85 ,x_trxn_burden_cost_rate OUT NOCOPY NUMBER
86 ,x_burden_multiplier OUT NOCOPY NUMBER
87 ,x_cost_ind_compiled_set_id OUT NOCOPY NUMBER
88 ,x_raw_cost_rejection_code OUT NOCOPY VARCHAR2
89 ,x_burden_cost_rejection_code OUT NOCOPY VARCHAR2
90 ,x_return_status OUT NOCOPY VARCHAR2
91 ,x_error_msg_code OUT NOCOPY VARCHAR2 );
92
93 PROCEDURE Get_Non_Labor_raw_cost
94 (p_project_id IN NUMBER
95 ,p_task_id IN NUMBER
96 ,p_non_labor_resource IN VARCHAR2
97 ,p_nlr_organization_id IN NUMBER
98 ,p_expenditure_type IN VARCHAR2
99 ,p_exp_item_date IN DATE
100 ,p_override_organization_id IN NUMBER
101 ,p_quantity IN NUMBER
102 ,p_org_id IN NUMBER
103 ,p_nlr_schedule_id IN NUMBER
104 ,p_nlr_trxn_cost_rate IN NUMBER DEFAULT NULL
105 ,p_nlr_trxn_raw_cost IN NUMBER DEFAULT NULL
106 ,p_nlr_trxn_currency_code IN VARCHAR2 DEFAULT NULL
107 ,x_trxn_raw_cost_rate OUT NOCOPY NUMBER
108 ,x_trxn_raw_cost OUT NOCOPY NUMBER
109 ,x_txn_currency_code OUT NOCOPY VARCHAR2
110 ,x_return_status OUT NOCOPY VARCHAR2
111 ,x_error_msg_code OUT NOCOPY VARCHAR2
112 );
113
114 /* This is a wrapper api to derive compiled set id and burden multiplier
115 * Which in turn makes calls to pa_cost_plus package
116 */
117 PROCEDURE Get_burden_sch_details
118 (p_calling_mode IN VARCHAR2 DEFAULT 'ACTUAL_RATES'
119 ,p_exp_item_id IN NUMBER
120 ,p_trxn_type IN VARCHAR2
121 ,p_project_type IN VARCHAR2
122 ,p_project_id IN NUMBER
123 ,p_task_id IN NUMBER
124 ,p_exp_organization_id IN NUMBER
125 /* bug fix:4232181 Derive organization override for burden calculate */
126 ,p_overide_organization_id IN NUMBER DEFAULT NULL
127 ,p_person_id IN NUMBER DEFAULT NULL
128 /* end of bug fix:4232181 */
129 ,p_expenditure_type IN VARCHAR2
130 ,p_schedule_type IN VARCHAR2 DEFAULT 'C'
131 ,p_exp_item_date IN DATE
132 ,p_trxn_curr_code IN VARCHAR2
133 ,p_burden_schedule_id IN NUMBER DEFAULT NULL
134 ,x_schedule_id OUT NOCOPY NUMBER
135 ,x_sch_revision_id OUT NOCOPY NUMBER
136 ,x_sch_fixed_date OUT NOCOPY DATE
137 ,x_cost_base OUT NOCOPY VARCHAR2
138 ,x_cost_plus_structure OUT NOCOPY VARCHAR2
139 ,x_compiled_set_id OUT NOCOPY NUMBER
140 ,x_burden_multiplier OUT NOCOPY NUMBER
141 ,x_return_status OUT NOCOPY VARCHAR2
142 ,x_error_msg_code OUT NOCOPY VARCHAR2 );
143
144 /* This API derives the cost rates based on the bill rate schedules
145 * The possible values for the params
146 * p_schedule_type 'EMPLOYEE' / 'JOB' / 'NON-LABOR'
147 * p_rate_sch_id based on the schedule type the corresponding rate schedule id must be passed
148 */
149 PROCEDURE get_RateSchDetails
150 (p_schedule_type IN Varchar2
151 ,p_rate_sch_id IN Number
152 ,p_person_id IN Number
153 ,p_job_id IN Number
154 ,p_non_labor_resource IN Varchar2
155 ,p_expenditure_type IN Varchar2
156 ,p_rate_organization_id IN Number
157 ,p_exp_item_date IN Date
158 ,p_org_id IN Number
159 ,x_currency_code OUT NOCOPY Varchar2
160 ,x_cost_rate OUT NOCOPY Number
161 ,x_markup_percent OUT NOCOPY Number
162 ,x_return_status OUT NOCOPY Varchar2
163 ,x_error_msg_code OUT NOCOPY Varchar2 );
164
165 /* This API converts the cost amount from transaction currency to
166 * project and project functional currency based on the
167 * planning transaction currency conversion attributes
168 * NOTE: Please donot use this API for actual cost conversion
169 */
170 PROCEDURE Convert_COST_TO_PC_PFC
171 (p_txn_raw_cost IN NUMBER
172 ,p_txn_burden_cost IN NUMBER
173 ,p_txn_quantity IN NUMBER
174 ,p_txn_curr_code IN VARCHAR2
175 ,p_txn_date IN DATE
176 ,p_project_id IN NUMBER
177 ,p_budget_Version_id IN NUMBER
178 ,p_budget_line_id IN NUMBER
179 ,x_project_curr_code OUT NOCOPY VARCHAR2
180 ,x_projfunc_curr_code OUT NOCOPY VARCHAR2
181 ,x_proj_raw_cost OUT NOCOPY NUMBER
182 ,x_proj_raw_cost_rate OUT NOCOPY NUMBER
183 ,x_proj_burdened_cost OUT NOCOPY NUMBER
184 ,x_proj_burdened_cost_rate OUT NOCOPY NUMBER
185 ,x_projfunc_raw_cost OUT NOCOPY NUMBER
186 ,x_projfunc_raw_cost_rate OUT NOCOPY NUMBER
187 ,x_projfunc_burdened_cost OUT NOCOPY NUMBER
188 ,x_projfunc_burdened_cost_rate OUT NOCOPY NUMBER
189 ,x_return_status OUT NOCOPY VARCHAR2
190 ,x_error_msg_code OUT NOCOPY VARCHAR2
191 );
192
193 /* This is an internal API which will be called from Convert_COSTto PC and PFC api
194 * this api does the calculation fo amount conversion based on the planning conversion
195 * attributes
196 */
197 PROCEDURE Convert_amounts
198 (p_calling_mode IN VARCHAR2 DEFAULT 'PC'
199 ,p_txn_raw_cost IN NUMBER
200 ,p_txn_burden_cost IN NUMBER
201 ,p_txn_quantity IN NUMBER
202 ,p_Conversion_Date IN DATE
203 ,p_From_curr_code IN VARCHAR2
204 ,p_To_curr_code IN VARCHAR2
205 ,p_To_Curr_Rate_Type IN VARCHAR2
206 ,p_To_Curr_Exchange_Rate IN NUMBER
207 ,x_To_Curr_raw_cost OUT NOCOPY NUMBER
208 ,x_To_Curr_raw_cost_rate OUT NOCOPY NUMBER
209 ,x_To_Curr_burden_cost OUT NOCOPY NUMBER
210 ,x_To_Curr_burden_cost_rate OUT NOCOPY NUMBER
211 ,x_To_Curr_Exchange_Rate OUT NOCOPY NUMBER
212 ,x_return_status OUT NOCOPY VARCHAR2
213 ,x_error_msg_code OUT NOCOPY VARCHAR2
214 );
215
216
217 end PA_COST1;