1 PACKAGE PA_PLANNING_TRANSACTION_UTILS AUTHID CURRENT_USER AS
2 /* $Header: PAFPPTUS.pls 120.4.12020000.2 2013/04/29 11:39:39 bpottipa ship $ */
3
4 ----------------------------------
5 --Functions/Procedures Declaration
6 ----------------------------------
7 FUNCTION Get_Wp_Budget_Version_Id (
8 p_struct_elem_version_id IN pa_budget_versions.project_structure_version_id%TYPE
9 )
10 RETURN NUMBER;
11
12
13 PROCEDURE Get_Res_Class_Rlm_Ids
14 (p_project_id IN pa_projects_all.project_id%TYPE,
15 p_resource_list_id IN pa_resource_lists_all_bg.resource_list_id%TYPE,
16 x_people_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
17 x_equip_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
18 x_fin_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
19 x_mat_res_class_rlm_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE, --File.Sql.39 bug 4440895
20 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
21 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
22 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
23
24 -- This API will return the default planning start and end dates based on the element version
25 -- Depending on the existence either the txn, actual, estimated or scheduled dates of the task in the priority of
26 -- the order mentioned will be returned.If none of the them are there then the dates of the parent structure
27 -- version id will be passed. If the dates are not there for the parent version also then sysdate will be returned
28
29 -- The output tables x_planning_start_date_tbl and x_planning_end_date_tbl will have the same no of records as
30 -- in the table p_element_version_id_tbl. Duplicates are allowed in input and the derivation will be done for the duplicate
31 -- tasks also
32
33 -- Included p_project_id as parameter. For elem vers id as 0, project start and end dates are used.
34
35 -- Added New I/p params p_planning_start_date_tbl and x_planning_end_date_tbl -- 3793623
36 -- Dates will not be defaulted if they are passed to the API at a particular index.
37 PROCEDURE get_default_planning_dates
38 ( p_project_id IN pa_projects_all.project_id%TYPE
39 ,p_element_version_id_tbl IN SYSTEM.pa_num_tbl_type
40 ,p_project_structure_version_id IN pa_budget_versions.project_structure_version_id%TYPE
41 ,p_planning_start_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.PA_DATE_TBL_TYPE()
42 ,p_planning_end_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.PA_DATE_TBL_TYPE()
43 ,x_planning_start_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type --File.Sql.39 bug 4440895
44 ,x_planning_end_date_tbl OUT NOCOPY SYSTEM.pa_date_tbl_type --File.Sql.39 bug 4440895
45 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
46 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
47 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
48 );
49
50 --This procedure populates the tmp table PJI_FM_EXTR_PLAN_LINES and calls the API
51 --PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE . The valid values for p_source are
52 -- 1. PA_RBS_PLANS_OUT_TMP (This is the tmp table which contains the mapped rbs elemend ids ). The PJI API will
53 -- be called for the rbs element ids availabe in the PA_RBS_PLANS_OUT_TMP, if the new rbs element id is different
54 -- from the already existing rbs element id in pa_resource_assignments
55 -- 2. PA_FP_RA_MAP_TMP (This is the global temporary table which contains the resouce assignments in the source that
56 -- should copied. This is used for copying a version fully or some of the assignments in it ). This table is used
57 -- as the reference for deciding the budget lines for which reporting lines should be created
58 -- 3. PL-SQL : The source will be pl/sql if the pl/sql tables are populated. These pl/sql tables will be used in
59 -- populated the tmp table for calling the PJI Update API.
60 PROCEDURE call_update_rep_lines_api
61 (
62 p_source IN VARCHAR2
63 ,p_budget_Version_id IN pa_budget_Versions.budget_version_id%TYPE DEFAULT NULL--Req only when
64 --p_source is PL-SQL
65 ,p_resource_assignment_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
66 ,p_cbs_element_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type() --bug#16688443
67 ,p_period_name_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type()
68 ,p_start_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
69 ,p_end_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
70 ,p_txn_currency_code_tbl IN SYSTEM.pa_varchar2_15_tbl_type DEFAULT SYSTEM.pa_varchar2_15_tbl_type()
71 ,p_txn_raw_cost_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
72 ,p_txn_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
73 ,p_txn_revenue_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
74 ,p_project_raw_cost_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
75 ,p_project_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
76 ,p_project_revenue_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
77 ,p_raw_cost_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
78 ,p_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
79 ,p_revenue_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
80 ,p_cost_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type()
81 ,p_revenue_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type()
82 ,p_burden_rejection_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type()
83 ,p_other_rejection_code IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type()
84 ,p_pc_cur_conv_rej_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type()
85 ,p_pfc_cur_conv_rej_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type()
86 ,p_quantity_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
87 ,p_rbs_element_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
88 ,p_task_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
89 ,p_res_class_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type()
90 ,p_rate_based_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
91 ,p_qty_sign IN NUMBER DEFAULT 1 --for bug 4543744
92 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
93 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
94 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
95
96
97 END PA_PLANNING_TRANSACTION_UTILS;