1 PACKAGE PA_FP_MULTI_CURRENCY_PKG AUTHID CURRENT_USER AS
2 --$Header: PAFPMCPS.pls 120.2 2006/07/26 10:22:43 prachand noship $
3
4
5 -- Package Variables.
6
7 g_project_id pa_projects_all.project_id%TYPE;
8 g_project_number pa_projects_all.segment1%TYPE;
9 g_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
10
11 g_projfunc_currency_code varchar2(30);
12 g_projfunc_cost_rate_type varchar2(30);
13 g_projfunc_cost_exchange_rate number;
14 g_projfunc_cost_rate_date_type varchar2(30);
15 g_projfunc_cost_rate_date date;
16 g_projfunc_rev_rate_type varchar2(30);
17 g_projfunc_rev_exchange_rate number;
18 g_projfunc_rev_rate_date_type varchar2(30);
19 g_projfunc_rev_rate_date date;
20
21 g_proj_currency_code varchar2(30);
22 g_proj_cost_rate_type varchar2(30);
23 g_proj_cost_exchange_rate number;
24 g_proj_cost_rate_date_type varchar2(30);
25 g_proj_cost_rate_date date;
26 g_proj_rev_rate_type varchar2(30);
27 g_proj_rev_exchange_rate number;
28 g_proj_rev_rate_date_type varchar2(30);
29 g_proj_rev_rate_date date;
30
31 TYPE cached_row IS RECORD (
32 from_currency VARCHAR2(30),
33 to_currency VARCHAR2(30),
34 numerator NUMBER,
35 denominator NUMBER,
36 rate NUMBER,
37 rate_date DATE,
38 rate_type VARCHAR2(30),
39 line_type VARCHAR2(30)); -- will store Cost/Revenue
40
41 TYPE cached_row_tab is TABLE of cached_row INDEX BY BINARY_INTEGER;
42
43 TYPE number_type_tab IS TABLE OF NUMBER
44 INDEX BY BINARY_INTEGER;
45
46 TYPE date_type_tab IS TABLE OF DATE
47 INDEX BY BINARY_INTEGER;
48
49 TYPE char30_type_tab IS TABLE OF VARCHAR2(30)
50 INDEX BY BINARY_INTEGER;
51
52 TYPE char240_type_tab IS TABLE OF VARCHAR2(240)
53 INDEX BY BINARY_INTEGER;
54
55 TYPE rowid_type_tab IS TABLE OF ROWID
56 INDEX BY BINARY_INTEGER;
57
58 PROCEDURE conv_mc_bulk ( p_resource_assignment_id_tab IN pa_fp_multi_currency_pkg.number_type_tab
59 ,p_start_date_tab IN pa_fp_multi_currency_pkg.date_type_tab
60 ,p_end_date_tab IN pa_fp_multi_currency_pkg.date_type_tab
61 ,p_txn_currency_code_tab IN pa_fp_multi_currency_pkg.char240_type_tab
62 ,p_txn_raw_cost_tab IN pa_fp_multi_currency_pkg.number_type_tab
63 ,p_txn_burdened_cost_tab IN pa_fp_multi_currency_pkg.number_type_tab
64 ,p_txn_revenue_tab IN pa_fp_multi_currency_pkg.number_type_tab
65 ,p_projfunc_currency_code_tab IN pa_fp_multi_currency_pkg.char240_type_tab
66 ,p_projfunc_cost_rate_type_tab IN pa_fp_multi_currency_pkg.char240_type_tab
67 ,p_projfunc_cost_rate_tab IN OUT NOCOPY pa_fp_multi_currency_pkg.number_type_tab
68 ,p_projfunc_cost_rate_date_tab IN pa_fp_multi_currency_pkg.date_type_tab
69 ,p_projfunc_rev_rate_type_tab IN pa_fp_multi_currency_pkg.char240_type_tab
70 ,p_projfunc_rev_rate_tab IN OUT NOCOPY pa_fp_multi_currency_pkg.number_type_tab
71 ,p_projfunc_rev_rate_date_tab IN pa_fp_multi_currency_pkg.date_type_tab
72 ,x_projfunc_raw_cost_tab OUT NOCOPY pa_fp_multi_currency_pkg.number_type_tab
73 ,x_projfunc_burdened_cost_tab OUT NOCOPY pa_fp_multi_currency_pkg.number_type_tab
74 ,x_projfunc_revenue_tab OUT NOCOPY pa_fp_multi_currency_pkg.number_type_tab
75 ,x_projfunc_rejection_tab OUT NOCOPY pa_fp_multi_currency_pkg.char30_type_tab
76 ,p_proj_currency_code_tab IN pa_fp_multi_currency_pkg.char240_type_tab
77 ,p_proj_cost_rate_type_tab IN pa_fp_multi_currency_pkg.char240_type_tab
78 ,p_proj_cost_rate_tab IN OUT NOCOPY pa_fp_multi_currency_pkg.number_type_tab
79 ,p_proj_cost_rate_date_tab IN pa_fp_multi_currency_pkg.date_type_tab
80 ,p_proj_rev_rate_type_tab IN pa_fp_multi_currency_pkg.char240_type_tab
81 ,p_proj_rev_rate_tab IN OUT NOCOPY pa_fp_multi_currency_pkg.number_type_tab
82 ,p_proj_rev_rate_date_tab IN pa_fp_multi_currency_pkg.date_type_tab
83 ,x_proj_raw_cost_tab OUT NOCOPY pa_fp_multi_currency_pkg.number_type_tab
84 ,x_proj_burdened_cost_tab OUT NOCOPY pa_fp_multi_currency_pkg.number_type_tab
85 ,x_proj_revenue_tab OUT NOCOPY pa_fp_multi_currency_pkg.number_type_tab
86 ,x_proj_rejection_tab OUT NOCOPY pa_fp_multi_currency_pkg.char30_type_tab
87 ,p_user_validate_flag_tab IN pa_fp_multi_currency_pkg.char240_type_tab
88 ,p_calling_module IN VARCHAR2 DEFAULT 'UPDATE_PLAN_TRANSACTION' -- Added for bug#5395732
89 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
93 /* added two new params source context and budget line id
90 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
91 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
92
94 * so that this api can be called from calculate for each budget line
95 * while updating the budget lines to derive pc and pfc attribs
96 */
97 PROCEDURE convert_txn_currency
98 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
99 ,p_entire_version IN VARCHAR2 DEFAULT 'N'
100 ,p_budget_line_id IN NUMBER DEFAULT NULL
101 ,p_source_context IN VARCHAR2 DEFAULT 'BUDGET_VERSION'
102 ,p_calling_module IN VARCHAR2 DEFAULT 'UPDATE_PLAN_TRANSACTION'-- Added for Bug#5395732
103 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
104 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
105 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
106
107 /*=============================================================================
108 This api is used to Round budget line amounts as per the currency precision/
109 MAU (Minimum Accountable Unit). Quantity would be rounded to 5 decimal points.
110 The api would be called from Copy Version Amounts flow with non-zero adj %
111 The api is also called Change Order Revenue amount partial implementation.
112
113 p_calling_context -> COPY_VERSION, CHANGE_ORDER_MERGE
114 The parameters p_bls_inserted_after_id will be used only
115 when p_calling_context is CHANGE_ORDER_MERGE
116 p_bls_inserted_after_id : This value will be used to find out the budget lines that
117 got inserted in this flow. All the budget lines with
118 1. budget line id > p_bls_inserted_after_id AND
119 2. budget_Version_id = p_budget_version_id
120 will be considered as inserted in this flow.
121
122 Tracking bug No: 4035856 Rravipat Initial creation
123 ==============================================================================*/
124
125 PROCEDURE Round_Budget_Line_Amounts(
126 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
127 ,p_bls_inserted_after_id IN pa_budget_lines.budget_line_id%TYPE DEFAULT NULL
128 ,p_calling_context IN VARCHAR2
129 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
130 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
131 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
132
133 /* Perf Bug :3683132 The outer join with pa_fp_currencies causing FTS exceeds 10
134 * in order to avoid this, a function created to cache the values
135 * and remove the outer joins from the where clause
136 * After this change the explain plan is
137 * Shared memory(M) = 62831, Parse Time(S) = 0 , Total Cost = 51, Nof. FTS = 0
138 1:SELECT STATEMENT :(cost=51,rows=1)
139 2:SORT ORDER BY :(cost=51,rows=1)
140 3:FILTER :(cost=,rows=)
141 4:TABLE ACCESS BY INDEX ROWID PA_BUDGET_LINES :(cost=49,rows=1)
142 5:INDEX RANGE SCAN PA_BUDGET_LINES_N3 :(cost=2,rows=1)
143 4:TABLE ACCESS BY INDEX ROWID PA_RESOURCE_ASSIGNMENTS :(cost=2,rows=1)
144 5:INDEX UNIQUE SCAN PA_RESOURCE_ASSIGNMENTS_U1 :(cost=1,rows=1)
145 * Refer to bug for more details
146 */
147 FUNCTION get_fp_cur_details( p_budget_version_id Number
148 ,p_txn_currency_code Varchar2
149 ,p_context Varchar2 default 'COST'
150 ,p_mode Varchar2 default 'PROJECT' ) RETURN NUMBER ;
151
152 -->This API is written as part of rounding changes. This API will be called from PAFPCIMB.implement_ci_into_single_ver
153 -->API when partial implementation happens.
154 ---->p_agr_currency_code,p_project_currency_code and p_projfunc_currency_code should be valid and not null
155 ---->All the p_...tbl input parameters should have same no. of elemeents
156 ---->p_txn...tbls will be rounded based on p_agr_currency_code, p_project_...tbls will be rounded based on
157 --p_project_currency_code and p_projfunc_...tbls will be rounded based on p_projfunc_currency_code
158 ---->px_quantity_tbl will be rounded to have max 5 digits after decimal point
159 PROCEDURE round_amounts
160 ( px_quantity_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
161 ,p_agr_currency_code IN OUT NOCOPY pa_budget_lines.txn_currency_code%TYPE --File.Sql.39 bug 4440895
162 ,px_txn_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
163 ,px_txn_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
164 ,px_txn_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
165 ,p_project_currency_code IN OUT NOCOPY pa_budget_lines.project_currency_code%TYPE --File.Sql.39 bug 4440895
166 ,px_project_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
167 ,px_project_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
168 ,px_project_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
169 ,p_projfunc_currency_code IN OUT NOCOPY pa_budget_lines.projfunc_currency_code%TYPE --File.Sql.39 bug 4440895
170 ,px_projfunc_raw_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
171 ,px_projfunc_burdened_cost_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
172 ,px_projfunc_revenue_tbl IN OUT NOCOPY SYSTEM.pa_num_tbl_type
173 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
174 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
175 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
176 END PA_FP_MULTI_CURRENCY_PKG;
[Home] [Help]