[Home] [Help]
PACKAGE: APPS.PA_FP_GEN_AMOUNT_UTILS
Source
1 PACKAGE PA_FP_GEN_AMOUNT_UTILS AUTHID CURRENT_USER as
2 /* $Header: PAFPGAUS.pls 120.9 2012/02/13 05:57:54 djambhek ship $ */
3 TYPE FP_COLS IS RECORD (
4 X_PROJECT_ID PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
8 X_AMOUNT_SET_ID PA_PROJ_FP_OPTIONS.COST_AMOUNT_SET_ID%TYPE,
5 X_BUDGET_VERSION_ID PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
6 X_PROJ_FP_OPTIONS_ID PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE,
7 X_FIN_PLAN_TYPE_ID PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
9 X_FIN_PLAN_LEVEL_CODE PA_PROJ_FP_OPTIONS.COST_FIN_PLAN_LEVEL_CODE%TYPE,
10 X_TIME_PHASED_CODE PA_PROJ_FP_OPTIONS.COST_TIME_PHASED_CODE%TYPE,
11 X_RESOURCE_LIST_ID PA_PROJ_FP_OPTIONS.COST_RESOURCE_LIST_ID%TYPE,
12 X_RES_PLANNING_LEVEL PA_PROJ_FP_OPTIONS.COST_RES_PLANNING_LEVEL%TYPE,
13 X_RBS_VERSION_ID PA_PROJ_FP_OPTIONS.RBS_VERSION_ID%TYPE,
14 /* X_EMP_RATE_SCH_ID PA_PROJ_FP_OPTIONS.COST_EMP_RATE_SCH_ID%TYPE, */
15 X_COST_EMP_RATE_SCH_ID PA_PROJ_FP_OPTIONS.COST_EMP_RATE_SCH_ID%TYPE,
16 X_REV_EMP_RATE_SCH_ID PA_PROJ_FP_OPTIONS.REV_EMP_RATE_SCH_ID%TYPE,
17 /* X_JOB_RATE_SCH_ID PA_PROJ_FP_OPTIONS.COST_JOB_RATE_SCH_ID%TYPE,*/
18 X_COST_JOB_RATE_SCH_ID PA_PROJ_FP_OPTIONS.COST_JOB_RATE_SCH_ID%TYPE,
19 X_REV_JOB_RATE_SCH_ID PA_PROJ_FP_OPTIONS.REV_JOB_RATE_SCH_ID%TYPE,
20 /* X_NON_LABOR_RES_RATE_SCH_ID PA_PROJ_FP_OPTIONS.COST_NON_LABOR_RES_RATE_SCH_ID%TYPE,*/
21 X_CNON_LABOR_RES_RATE_SCH_ID PA_PROJ_FP_OPTIONS.COST_NON_LABOR_RES_RATE_SCH_ID%TYPE,
22 X_RNON_LABOR_RES_RATE_SCH_ID PA_PROJ_FP_OPTIONS.REV_NON_LABOR_RES_RATE_SCH_ID%TYPE,
23 /* X_RES_CLASS_RATE_SCH_ID PA_PROJ_FP_OPTIONS.COST_RES_CLASS_RATE_SCH_ID%TYPE,*/
24 X_COST_RES_CLASS_RATE_SCH_ID PA_PROJ_FP_OPTIONS.COST_RES_CLASS_RATE_SCH_ID%TYPE,
25 X_REV_RES_CLASS_RATE_SCH_ID PA_PROJ_FP_OPTIONS.REV_RES_CLASS_RATE_SCH_ID%TYPE,
26 X_BURDEN_RATE_SCH_ID PA_PROJ_FP_OPTIONS.COST_BURDEN_RATE_SCH_ID%TYPE,
27 X_CURRENT_PLANNING_PERIOD PA_PROJ_FP_OPTIONS.COST_CURRENT_PLANNING_PERIOD%TYPE,
28 X_PERIOD_MASK_ID PA_PROJ_FP_OPTIONS.COST_PERIOD_MASK_ID%TYPE,
29 X_GEN_SRC_PLAN_TYPE_ID PA_PROJ_FP_OPTIONS.GEN_SRC_COST_PLAN_TYPE_ID%TYPE,
30 X_GEN_SRC_PLAN_VERSION_ID PA_PROJ_FP_OPTIONS.GEN_SRC_COST_PLAN_VERSION_ID%TYPE,
31 X_GEN_SRC_PLAN_VER_CODE PA_PROJ_FP_OPTIONS.GEN_SRC_COST_PLAN_VER_CODE%TYPE,
32 X_GEN_SRC_CODE PA_PROJ_FP_OPTIONS.GEN_COST_SRC_CODE%TYPE,
33 X_GEN_ETC_SRC_CODE PA_PROJ_FP_OPTIONS.GEN_COST_ETC_SRC_CODE%TYPE,
34 X_GEN_INCL_CHANGE_DOC_FLAG PA_PROJ_FP_OPTIONS.GEN_COST_INCL_CHANGE_DOC_FLAG%TYPE,
35 X_GEN_INCL_OPEN_COMM_FLAG PA_PROJ_FP_OPTIONS.GEN_COST_INCL_OPEN_COMM_FLAG%TYPE,
36 X_GEN_INCL_BILL_EVENT_FLAG VARCHAR2(1),
37 X_GEN_RET_MANUAL_LINE_FLAG PA_PROJ_FP_OPTIONS.GEN_COST_RET_MANUAL_LINE_FLAG%TYPE,
38 X_GEN_ACTUAL_AMTS_THRU_CODE PA_PROJ_FP_OPTIONS.GEN_COST_ACTUAL_AMTS_THRU_CODE%TYPE,
39 X_GEN_INCL_UNSPENT_AMT_FLAG PA_PROJ_FP_OPTIONS.GEN_COST_INCL_UNSPENT_AMT_FLAG%TYPE,
40 X_PLAN_IN_MULTI_CURR_FLAG PA_PROJ_FP_OPTIONS.PLAN_IN_MULTI_CURR_FLAG%TYPE,
41 X_ORG_ID PA_PROJECTS_ALL.ORG_ID%TYPE,
42 X_PROJECT_CURRENCY_CODE PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
43 X_PROJFUNC_CURRENCY_CODE PA_PROJECTS_ALL.PROJFUNC_CURRENCY_CODE%TYPE,
44 X_SET_OF_BOOKS_ID PA_IMPLEMENTATIONS_ALL.SET_OF_BOOKS_ID%TYPE,
45 X_RAW_COST_FLAG PA_FIN_PLAN_AMOUNT_SETS.RAW_COST_FLAG%TYPE,
46 X_BURDENED_FLAG PA_FIN_PLAN_AMOUNT_SETS.BURDENED_COST_FLAG%TYPE,
47 X_REVENUE_FLAG PA_FIN_PLAN_AMOUNT_SETS.REVENUE_FLAG%TYPE,
48 X_COST_QUANTITY_FLAG PA_FIN_PLAN_AMOUNT_SETS.COST_QTY_FLAG%TYPE,
49 X_REV_QUANTITY_FLAG PA_FIN_PLAN_AMOUNT_SETS.REVENUE_QTY_FLAG%TYPE,
50 X_ALL_QUANTITY_FLAG PA_FIN_PLAN_AMOUNT_SETS.ALL_QTY_FLAG%TYPE,
51 X_BILL_RATE_FLAG PA_FIN_PLAN_AMOUNT_SETS.BILL_RATE_FLAG%TYPE,
52 X_COST_RATE_FLAG PA_FIN_PLAN_AMOUNT_SETS.COST_RATE_FLAG%TYPE,
53 X_BURDEN_RATE_FLAG PA_FIN_PLAN_AMOUNT_SETS.BURDEN_RATE_FLAG%TYPE,
54 X_PROJECT_STRUCTURE_VERSION_ID PA_BUDGET_VERSIONS.PROJECT_STRUCTURE_VERSION_ID%TYPE,
55 X_PLAN_CLASS_CODE PA_FIN_PLAN_TYPES_B.PLAN_CLASS_CODE%TYPE,
56 X_VERSION_TYPE PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE,
57 X_PROJECT_VALUE PA_PROJECTS_ALL.PROJECT_VALUE%TYPE,
58 X_TRACK_WORKPLAN_COSTS_FLAG PA_PROJ_FP_OPTIONS.TRACK_WORKPLAN_COSTS_FLAG%TYPE,
59 X_REVENUE_DERIVATION_METHOD VARCHAR2(1), -- bug 5152892
60 X_GEN_SRC_WP_VERSION_ID NUMBER(15),
61 X_GEN_SRC_WP_VER_CODE VARCHAR2(30));
62
63 PROCEDURE GET_PLAN_VERSION_DTLS
64 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE DEFAULT NULL,
65 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
66 X_FP_COLS_REC OUT NOCOPY FP_COLS,
67 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
68 X_MSG_COUNT OUT NOCOPY NUMBER,
69 X_MSG_DATA OUT NOCOPY VARCHAR2);
70
71 PROCEDURE CHK_CMT_TXN_CURRENCY
75 X_MSG_DATA OUT NOCOPY VARCHAR2,
72 (P_PROJECT_ID IN NUMBER,
73 P_PROJ_CURRENCY_CODE IN VARCHAR2,
74 X_MSG_COUNT OUT NOCOPY NUMBER,
76 X_RETURN_STATUS OUT NOCOPY VARCHAR2);
77
78 PROCEDURE Get_Curr_Original_Version_Info(
79 p_project_id IN pa_projects_all.project_id%TYPE,
80 p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE,
81 p_version_type IN pa_budget_versions.version_type%TYPE,
82 p_status_code IN VARCHAR2 DEFAULT 'CURRENT_ORIGINAL',
83 x_fp_options_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE,
84 x_fin_plan_version_id OUT NOCOPY pa_proj_fp_options.fin_plan_version_id%TYPE,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER,
87 x_msg_data OUT NOCOPY VARCHAR2);
88
89 PROCEDURE VALIDATE_PLAN_VERSION
90 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
91 P_SRC_BDGT_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
92 P_TRGT_BDGT_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
93 X_MSG_COUNT OUT NOCOPY NUMBER,
94 X_MSG_DATA OUT NOCOPY VARCHAR2,
95 X_RETURN_STATUS OUT NOCOPY VARCHAR2);
96
97 TYPE RESOURCE_ASN_REC IS RECORD (
98 x_project_id pa_resource_assignments.project_id%TYPE,
99 x_rate_task_id pa_resource_assignments.task_id%TYPE,
100 x_unit_of_measure pa_resource_assignments.unit_of_measure%TYPE,
101 x_resource_class_code pa_resource_assignments.resource_class_code%TYPE,
102 x_organization_id pa_resource_assignments.organization_id%TYPE,
103 x_job_id pa_resource_assignments.job_id%TYPE,
104 x_person_id pa_resource_assignments.person_id%TYPE,
105 x_expenditure_type pa_resource_assignments.expenditure_type%TYPE,
106 x_non_labor_resource pa_resource_assignments.non_labor_resource%TYPE,
107 x_bom_resource_id pa_resource_assignments.bom_resource_id%TYPE,
108 x_inventory_item_id pa_resource_assignments.inventory_item_id%TYPE,
109 x_item_category_id pa_resource_assignments.item_category_id%TYPE,
110 x_mfc_cost_type_id pa_resource_assignments.mfc_cost_type_id%TYPE,
111 x_rate_based_flag pa_resource_assignments.rate_based_flag%TYPE,
112 x_rate_expenditure_org_id pa_resource_assignments.rate_expenditure_org_id%TYPE,
113 x_rate_expenditure_type pa_resource_assignments.rate_expenditure_type%TYPE);
114
115 TYPE PA_TASKS_REC IS RECORD (
116 x_task_bill_rate_org_id pa_tasks.non_labor_bill_rate_org_id%TYPE,
117 x_task_sch_discount pa_tasks.non_labor_schedule_discount%TYPE,
118 x_task_sch_date pa_tasks.non_labor_schedule_fixed_date%TYPE,
119 x_task_nl_std_bill_rt_sch_id pa_tasks.non_lab_std_bill_rt_sch_id%TYPE,
120 x_task_emp_bill_rate_sch_id pa_tasks.emp_bill_rate_schedule_id%TYPE,
121 x_task_job_bill_rate_sch_id pa_tasks.job_bill_rate_schedule_id%TYPE,
122 x_task_lab_bill_rate_org_id pa_tasks.labor_bill_rate_org_id%TYPE,
123 x_task_lab_sch_type pa_tasks.labor_sch_type%TYPE,
124 x_task_non_labor_sch_type pa_tasks.non_labor_sch_type%TYPE,
125 x_top_task_id pa_tasks.top_task_id%TYPE);
126
127 TYPE PA_PROJECTS_ALL_REC IS RECORD (
128 x_assign_precedes_task pa_projects_all.assign_precedes_task%TYPE,
129 x_bill_job_group_id pa_projects_all.bill_job_group_id%TYPE,
130 x_carrying_out_organization_id pa_projects_all.carrying_out_organization_id%TYPE,
131 x_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%TYPE,
132 x_org_id pa_projects_all.org_id%TYPE,
133 x_non_labor_bill_rate_org_id pa_projects_all.non_labor_bill_rate_org_id%TYPE,
134 x_project_currency_code pa_projects_all.project_currency_code%TYPE,
135 x_non_labor_schedule_discount pa_projects_all.non_labor_schedule_discount%TYPE,
136 x_non_labor_sch_fixed_date pa_projects_all.non_labor_schedule_fixed_date%TYPE,
137 x_non_lab_std_bill_rt_sch_id pa_projects_all.non_lab_std_bill_rt_sch_id%TYPE,
138 x_project_type pa_projects_all.project_type%TYPE,
139 x_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE,
140 x_emp_bill_rate_schedule_id pa_projects_all.emp_bill_rate_schedule_id%TYPE,
141 x_job_bill_rate_schedule_id pa_projects_all.job_bill_rate_schedule_id%TYPE,
142 x_labor_bill_rate_org_id pa_projects_all.labor_bill_rate_org_id%TYPE,
143 x_labor_sch_type pa_projects_all.labor_sch_type%TYPE,
144 x_non_labor_sch_type pa_projects_all.non_labor_sch_type%TYPE);
145
146 TYPE PROJ_FP_OPTIONS_REC IS RECORD (
147 x_fp_res_cl_bill_rate_sch_id pa_proj_fp_options.res_class_bill_rate_sch_id%TYPE,
148 x_fp_res_cl_raw_cost_sch_id pa_proj_fp_options.res_class_raw_cost_sch_id%TYPE,
149 x_fp_use_planning_rt_flag pa_proj_fp_options.use_planning_rates_flag%TYPE,
150 x_fp_rev_job_rate_sch_id pa_proj_fp_options.rev_job_rate_sch_id%TYPE,
151 x_fp_cost_job_rate_sch_id pa_proj_fp_options.cost_job_rate_sch_id%TYPE,
152 x_fp_rev_emp_rate_sch_id pa_proj_fp_options.rev_emp_rate_sch_id%TYPE,
153 x_fp_cost_emp_rate_sch_id pa_proj_fp_options.cost_emp_rate_sch_id%TYPE,
154 x_fp_rev_non_lab_rs_rt_sch_id pa_proj_fp_options.rev_non_labor_res_rate_sch_id%TYPE,
155 x_fp_cost_non_lab_rs_rt_sch_id pa_proj_fp_options.cost_non_labor_res_rate_sch_id%TYPE,
156 x_fp_cost_burden_rate_sch_id pa_proj_fp_options.cost_burden_rate_sch_id%TYPE,
157 x_fp_budget_version_type pa_budget_versions.version_type%TYPE);
158
159 PROCEDURE GET_VALUES_FOR_PLANNING_RATE
160 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
161 P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
162 P_RESOURCE_ASSIGNMENT_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
163 P_TASK_ID IN PA_TASKS.TASK_ID%TYPE,
164 P_RESOURCE_LIST_MEMBER_ID IN PA_RESOURCE_ASSIGNMENTS.resource_list_member_id%TYPE,
165 P_TXN_CURRENCY_CODE IN PA_BUDGET_LINES.txn_currency_code%TYPE,
166 X_RES_FORMAT_ID OUT NOCOPY PA_RESOURCE_LIST_MEMBERS.RES_FORMAT_ID%TYPE,
167 X_RESOURCE_ASN_REC OUT NOCOPY RESOURCE_ASN_REC,
168 X_PA_TASKS_REC OUT NOCOPY PA_TASKS_REC,
169 X_PA_PROJECTS_ALL_REC OUT NOCOPY PA_PROJECTS_ALL_REC,
170 X_PROJ_FP_OPTIONS_REC OUT NOCOPY PROJ_FP_OPTIONS_REC,
171 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
172 X_MSG_COUNT OUT NOCOPY NUMBER,
173 X_MSG_DATA OUT NOCOPY VARCHAR2);
174
175 PROCEDURE FP_DEBUG
176 (P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
177 /*p_called_mode values are SELF_SERVICE or CONCURRENT */
178 P_MSG IN VARCHAR2,
179 P_MODULE_NAME IN VARCHAR2 DEFAULT NULL,
180 P_LOG_LEVEL IN NUMBER DEFAULT 5);
181
182 FUNCTION GET_ETC_START_DATE(P_BUDGET_VERSION_ID PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE)
183 RETURN DATE;
184
185 FUNCTION GET_ACTUALS_THRU_DATE(P_BUDGET_VERSION_ID PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE)
186 RETURN DATE;
187
188 FUNCTION GET_RL_UNCATEGORIZED_FLAG(P_RESOURCE_LIST_ID PA_BUDGET_VERSIONS.RESOURCE_LIST_ID%TYPE)
189 RETURN VARCHAR2;
190
191 FUNCTION GET_UC_RES_LIST_RLM_ID(P_RESOURCE_LIST_ID PA_BUDGET_VERSIONS.RESOURCE_LIST_ID%TYPE,
192 P_RESOURCE_CLASS_CODE pa_resource_list_members.RESOURCE_CLASS_CODE%TYPE)
193 RETURN NUMBER;
194 /* This function returns the RLM ID for the given
195 project id + res list id + resource class level resource combination. */
196 FUNCTION GET_RLM_ID(P_PROJECT_ID PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
197 P_RESOURCE_LIST_ID PA_BUDGET_VERSIONS.RESOURCE_LIST_ID%TYPE,
198 P_RESOURCE_CLASS_CODE pa_resource_assignments.resource_class_code%type)
199 RETURN NUMBER;
200
201 /**
202 * 30-JUN-05 dkuo added parameters P_CHECK_SRC_ERRORS, X_WARNING_MESSAGE.
203 * See procedure body for documentation on parameters and API functionality.
204 **/
205 PROCEDURE VALIDATE_SUPPORT_CASES
206 (P_FP_COLS_REC_TGT IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
207 P_CALLING_CONTEXT IN VARCHAR2 DEFAULT 'SELF_SERVICE',
208 P_CHECK_SRC_ERRORS_FLAG IN VARCHAR2 DEFAULT 'Y',
209 X_WARNING_MESSAGE OUT NOCOPY VARCHAR2,
210 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
211 X_MSG_COUNT OUT NOCOPY NUMBER,
212 X_MSG_DATA OUT NOCOPY VARCHAR2);
213
214 PROCEDURE DEFAULT_BDGT_SRC_VER
215 (P_FP_COLS_REC_TGT IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
216 P_CALLING_CONTEXT IN VARCHAR2 DEFAULT 'SELF_SERVICE',
217 X_FP_COLS_REC_TGT OUT NOCOPY PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
218 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
219 X_MSG_COUNT OUT NOCOPY NUMBER,
220 X_MSG_DATA OUT NOCOPY VARCHAR2);
221 --Added for CBS
222 FUNCTION get_src_resource_class_flag
223 (P_BUDGET_VERSION_ID PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE)
224 RETURN VARCHAR2;
225
226 FUNCTION get_resource_class_flag_fr_bdg
227 (P_PROJECT_ID PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE , p_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE)
228 RETURN VARCHAR2;
229
230 --Added for CBS
231 FUNCTION get_trg_resource_class_flag
232 (P_RESOURCE_LIST_ID PA_BUDGET_VERSIONS.RESOURCE_LIST_ID%TYPE)
233 RETURN VARCHAR2;
234 END PA_FP_GEN_AMOUNT_UTILS;