1 PACKAGE pa_fp_ci_merge as
2 /* $Header: PAFPCIMS.pls 120.2 2007/02/06 09:47:43 dthakker ship $ */
3
4 --Copy exception
5 RAISE_COPY_ERROR EXCEPTION;
6 PRAGMA EXCEPTION_INIT(RAISE_COPY_ERROR, -502);
7
8
9 PROCEDURE FP_CI_LINK_CONTROL_ITEMS
10 (
11 p_project_id IN NUMBER,
12 p_s_fp_version_id IN pa_budget_versions.budget_version_id%TYPE,
13 p_t_fp_version_id IN pa_budget_versions.budget_version_id%TYPE,
14 p_inclusion_method IN VARCHAR2 DEFAULT 'AUTOMATIC',
15 p_included_by IN NUMBER DEFAULT NULL,
16 --Added for bug 3550073
17 p_version_type IN pa_budget_versions.version_type%TYPE,
18 p_ci_id IN pa_control_items.ci_id%TYPE,
19 p_cost_ppl_qty IN pa_fp_merged_ctrl_items.impl_quantity%TYPE,
20 p_rev_ppl_qty IN pa_fp_merged_ctrl_items.impl_quantity%TYPE,
21 p_cost_equip_qty IN pa_fp_merged_ctrl_items.impl_equipment_quantity%TYPE,
22 p_rev_equip_qty IN pa_fp_merged_ctrl_items.impl_equipment_quantity%TYPE,
23 p_impl_pfc_raw_cost IN pa_fp_merged_ctrl_items.impl_proj_func_raw_cost%TYPE,
24 p_impl_pfc_revenue IN pa_fp_merged_ctrl_items.impl_proj_func_revenue%TYPE,
25 p_impl_pfc_burd_cost IN pa_fp_merged_ctrl_items.impl_proj_func_burdened_cost%TYPE,
26 p_impl_pc_raw_cost IN pa_fp_merged_ctrl_items.impl_proj_raw_cost%TYPE,
27 p_impl_pc_revenue IN pa_fp_merged_ctrl_items.impl_proj_revenue%TYPE,
28 p_impl_pc_burd_cost IN pa_fp_merged_ctrl_items.impl_proj_burdened_cost%TYPE,
29 p_impl_agr_revenue IN pa_fp_merged_ctrl_items.impl_agr_revenue%TYPE,
30 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
31 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
32 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
33 ) ;
34
35 PROCEDURE FP_CI_MERGE_CI_ITEMS
36 (
37 p_project_id IN NUMBER,
38 p_s_fp_ci_id IN pa_budget_versions.ci_id%TYPE,
39 p_t_fp_ci_id IN pa_budget_versions.ci_id%TYPE,
40 p_merge_unmerge_mode IN VARCHAR2 DEFAULT 'MERGE',
41 p_commit_flag IN VARCHAR2 DEFAULT 'N',
42 p_init_msg_list IN VARCHAR2 DEFAULT 'N',
43 p_calling_context IN VARCHAR2 DEFAULT 'COPY',
44 x_warning_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
45 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
46 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
47 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
48 ) ;
49
50
51 PROCEDURE FP_CI_UPDATE_IMPACT
52 (
53 p_ci_id IN pa_ci_impacts.ci_id%TYPE DEFAULT NULL,
54 p_status_code IN pa_ci_impacts.status_code%TYPE DEFAULT NULL,
55 p_implementation_date IN pa_ci_impacts.implementation_date%TYPE DEFAULT NULL,
56 p_implemented_by IN pa_ci_impacts.implemented_by%TYPE DEFAULT NULL,
57 p_record_version_number IN pa_ci_impacts.record_version_number%TYPE DEFAULT NULL,
58 p_impacted_task_id IN pa_ci_impacts.impacted_task_id%TYPE DEFAULT NULL,
59 p_impact_type_code IN pa_ci_impacts.impact_type_code%TYPE,--For bug 3550073
60 p_commit_flag IN VARCHAR2 DEFAULT 'N',
61 p_init_msg_list IN VARCHAR2 DEFAULT 'N',
62 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
63 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
64 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
65 ) ;
66
67 --Bug 4247703. Added the parameter 4247703. The valid values are NULL or GENERATION
68 PROCEDURE copy_merged_ctrl_items
69 ( p_project_id IN pa_budget_versions.project_id%TYPE
70 ,p_source_version_id IN pa_budget_versions.budget_version_id%TYPE
71 ,p_target_version_id IN pa_budget_versions.budget_version_id%TYPE
72 ,p_calling_context IN VARCHAR2 DEFAULT NULL
73 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
74 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
75 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
76
77 PROCEDURE FP_CI_UPDATE_EST_AMOUNTS
78 (
79 p_project_id IN pa_budget_versions.project_id%TYPE,
80 p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
81 p_target_version_id IN pa_budget_versions.budget_version_id%TYPE,
82 p_merge_unmerge_mode IN VARCHAR2 DEFAULT 'MERGE',
83 p_commit_flag IN VARCHAR2 DEFAULT 'N',
84 p_init_msg_list IN VARCHAR2 DEFAULT 'N',
85 p_update_agreement IN VARCHAR2 DEFAULT 'N',
86 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
87 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
88 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
89 ) ;
90
91 PROCEDURE FP_CI_MANUAL_MERGE
92 (
93 p_project_id IN NUMBER,
94 p_ci_id IN pa_ci_impacts.ci_id%TYPE,
95 p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
96 p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
97 p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
98 p_t_fp_version_id IN pa_budget_versions.budget_version_id%TYPE,
99 p_targ_version_type IN pa_budget_versions.version_type%TYPE,
100 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
101 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
102 x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
103 ----------------------------------------------------------------------------------------------------------
104 --1.p_context can be PARTIAL_REV( When called from implement partial revenue page )
105 ----IMPL_FIN_IMPACT(when called from implement financial impact page)
106 ----INCLUDE(when called from the include change documents page)
107 ----CI_MERGE( for merging CIs into other CIs)
108
109 --2.p_ci_id_tbl, p_ci_cost_version_id_tbl, p_ci_rev_version_id_tbl and p_ci_all_version_id_tbl if passed should
110 ----contain same number of records. p_ci_id_tbl is mandatory. The version ids for the ci_id will be derived if not
111 ----passed. Either all the version ids for the CI should be passed or none of them should be passed
112
113 --3.p_fin_plan_type_id_tbl, p_fin_plan_type_name_tbl, p_impl_cost_flag_tbl, p_impl_rev_flag_tbl,
114 ----p_submit_version_flag_tbl should contain same number of records as in p_budget_version_id_tbl
115 ----p_fin_plan_type_id_tbl,p_fin_plan_type_name_tbl contains the fin plan type id and name for the
116 ----corresponding element in p_budget_version_id_tbl
117 ----p_impl_cost_flag_tbl, p_impl_rev_flag_tbl can have values of Y or N. They indicate whether the cost/revenue
118 ----impact can be implemented into the corresponding element in p_budget_version_id_tbl
119 ----p_submit_version_flag_tbl can contain Y or N, if passed. It indicates whether the target budget version id
120 ----should be baselined after implementation or not
121
122 --4. p_partial_impl_rev_amt contains the amount that should be implemented partially. This will be passed only
123 ----from implement partial revenue page. In this case ci_id as well as the target budget version id tbls will have
124 ----only one record
125
126 --5.p_agreement_id, p_update_agreement_amt_flag, p_funding_category are related to the agreement chosen
127 --6.p_add_msg_to_stack lets the API know whether the error messages should be added to the fnd_msg_pub or not. If
128 ----Y the messages will be added. They will not be added otherwise
129
130 --7.x_translated_msgs_tbl contains the translated error messages. x_translated_err_msg_count indicates the no. of
131 ----elements in x_translated_err_msg_count. x_translated_err_msg_level indicates whether the level of the message is
132 ----EXCEPTION, WARNING OR INFORMATION. They will be populated only if p_add_msg_to_stack is N
133
134 --8.p_commit_flag can be Y or N. This is defaulted to N. If passed as Y then the commit will be executed after
135 ----every implementation/inclusion i.e. after one ci has got implemented into the target budget version.
136
137
138 --The processing goes like this
139 ----Each ci_id will be implemented in every version id in p_budget_version_id_tbl. If p_impl_cost_flag_tbl is Y cost
140 ----will be implemented. If p_impl_rev_flag_tbl is Y revenue will be implemented.
141
142 -- Bug 3934574 Oct 14 2004 Added a new parameter p_calling_context that would be populated when
143 -- called as part of budget/forecast generation
144
145 PROCEDURE implement_change_document
146 ( p_context IN VARCHAR2
147 ,p_calling_context IN VARCHAR2 DEFAULT NULL -- bug 3934574
148 ,p_commit_flag IN VARCHAR2 DEFAULT 'N'
149 ,p_ci_id_tbl IN SYSTEM.pa_num_tbl_type
150 ,p_ci_cost_version_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
151 ,p_ci_rev_version_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
152 ,p_ci_all_version_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
153 ,p_fin_plan_type_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
154 ,p_fin_plan_type_name_tbl IN SYSTEM.pa_varchar2_150_tbl_type DEFAULT SYSTEM.pa_varchar2_150_tbl_type()
155 ,p_budget_version_id_tbl IN SYSTEM.pa_num_tbl_type
156 ,p_impl_cost_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type
157 ,p_impl_rev_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type
158 ,p_submit_version_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
159 ,p_partial_impl_rev_amt IN NUMBER DEFAULT NULL
160 ,p_agreement_id IN pa_agreements_all.agreement_id%TYPE DEFAULT NULL
161 ,p_update_agreement_amt_flag IN VARCHAR2 DEFAULT NULL
162 ,p_funding_category IN VARCHAR2 DEFAULT NULL
163 ,p_raTxn_rollup_api_call_flag IN VARCHAR2 DEFAULT 'Y' --IPM Arch Enhacements Bug 4865563
164 ,p_add_msg_to_stack IN VARCHAR2 DEFAULT 'Y'
165 ,x_translated_msgs_tbl OUT NOCOPY SYSTEM.pa_varchar2_2000_tbl_type --File.Sql.39 bug 4440895
166 ,x_translated_err_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
167 ,x_translated_err_msg_level_tbl OUT NOCOPY SYSTEM.pa_varchar2_30_tbl_type --File.Sql.39 bug 4440895
168 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
169 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
170 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
171
172 -- Start of functions internal to implement_ci_into_single_ver API
173
174 FUNCTION get_task_id(p_planning_level IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE,
175 p_task_id IN pa_resource_assignments.task_id%TYPE)
176 RETURN NUMBER;
177
178 FUNCTION get_mapped_ra_id(p_task_id IN pa_resource_assignments.task_id%TYPE,
179 p_rlm_id IN pa_resource_assignments.resource_list_member_id%TYPE,
180 p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE DEFAULT NULL,
181 p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE DEFAULT NULL
182 )
183 RETURN NUMBER;
184 FUNCTION get_mapped_dml_code(p_task_id IN pa_resource_assignments.task_id%TYPE,
185 p_rlm_id IN pa_resource_assignments.resource_list_member_id%TYPE,
186 p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE DEFAULT NULL,
187 p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE DEFAULT NULL
188
189 )
190 RETURN VARCHAR2 ;
191
192 -- End of functions internal to implement_ci_into_single_ver API
193
194 end pa_fp_ci_merge;
[Home] [Help]