1 PACKAGE PA_RLMI_RBS_MAP_PUB AUTHID CURRENT_USER AS
2 /* $Header: PAFPUT3S.pls 120.1.12020000.2 2013/05/23 09:01:15 bpottipa ship $ */
3
4 /* This API derives the Resource list member id and RBS element Id for the
5 * given resource list Id / RBS version Id. This procedure calls resource mapping and rbs mapping API
6 * depending the parameter p_process_code
7 * If p_process_code = 'RES_MAP' then RLMI will be derived by calling resource mapping api
8 * If p_process_code = 'RBS_MAP' then RBS element Id will be derived by caling RBS mapping api
9 * The following are the possible values for these IN params
10 * p_calling_process IN varchar2
11 * values 'BUDGET_GENERATION' , 'RBS_REFRESH' , 'COPY_PROJECT'
12 * p_process_code IN varchar2
13 * values 'RES_MAP', 'RBS_MAP'
14 * p_calling_context IN varchar2
15 * values 'PLSQL' , 'SELF_SERVICE'
16 * p_calling_mode IN varchar2
17 * values 'PLSQL_TABLE', 'BUDGET_VERSION'
18 *
19 * NOTES
20 * 1.p_txn_source_id_tab must be populated with UNIQUE value
21 * 2.If the p_calling_mode is 'BUDGET_VERSION' then values passed in plsql and system table params
22 * will be ignored
23 * 3.If p_calling_context is 'SELF_SERVICE' then log messages will write to PA_DEBUG.WRITE_LOG()
24 * If p_calling_context is 'PLSQL' then log messages will write to PA_DEBUG.write_file()
25 */
26 PROCEDURE Map_Rlmi_Rbs
27 ( p_budget_version_id IN Number
28 ,p_project_id IN Number Default NULL
29 ,p_resource_list_id IN Number Default NULL
30 ,p_rbs_version_id IN Number Default NULL
31 ,p_calling_process IN Varchar2
32 ,p_calling_context IN varchar2 Default 'PLSQL'
33 ,p_process_code IN varchar2 Default 'RES_MAP'
34 ,p_calling_mode IN Varchar2 Default 'PLSQL_TABLE'
35 ,p_init_msg_list_flag IN Varchar2 Default 'Y'
36 ,p_commit_flag IN Varchar2 Default 'N'
37 ,p_TXN_SOURCE_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
38 ,p_TXN_SOURCE_TYPE_CODE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
39 ,p_PERSON_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
40 ,p_JOB_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
41 ,p_ORGANIZATION_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
42 ,p_VENDOR_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
43 ,p_EXPENDITURE_TYPE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
44 ,p_EVENT_TYPE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
45 ,p_NON_LABOR_RESOURCE_tab IN PA_PLSQL_DATATYPES.Char20TabTyp Default PA_PLSQL_DATATYPES.EmptyChar20Tab
46 ,p_EXPENDITURE_CATEGORY_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
47 ,p_REVENUE_CATEGORY_CODE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
48 ,p_NLR_ORGANIZATION_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
49 ,p_EVENT_CLASSIFICATION_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
50 ,p_SYS_LINK_FUNCTION_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
51 ,p_PROJECT_ROLE_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
52 ,p_RESOURCE_CLASS_CODE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
53 ,p_MFC_COST_TYPE_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
54 ,p_RESOURCE_CLASS_FLAG_tab IN PA_PLSQL_DATATYPES.Char1TabTyp Default PA_PLSQL_DATATYPES.EmptyChar1Tab
55 ,p_FC_RES_TYPE_CODE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
56 ,p_INVENTORY_ITEM_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
57 ,p_ITEM_CATEGORY_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
58 ,p_PERSON_TYPE_CODE_tab IN PA_PLSQL_DATATYPES.Char30TabTyp Default PA_PLSQL_DATATYPES.EmptyChar30Tab
59 ,p_BOM_RESOURCE_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
60 ,p_NAMED_ROLE_tab IN PA_PLSQL_DATATYPES.Char80TabTyp Default PA_PLSQL_DATATYPES.EmptyChar80Tab
61 ,p_INCURRED_BY_RES_FLAG_tab IN PA_PLSQL_DATATYPES.Char1TabTyp Default PA_PLSQL_DATATYPES.EmptyChar1Tab
62 ,p_RATE_BASED_FLAG_tab IN PA_PLSQL_DATATYPES.Char1TabTyp Default PA_PLSQL_DATATYPES.EmptyChar1Tab
63 ,p_TXN_TASK_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
64 ,p_TXN_WBS_ELEMENT_VER_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
65 ,p_TXN_RBS_ELEMENT_ID_tab IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab
66 ,P_CBS_ELEMENT_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp Default PA_PLSQL_DATATYPES.EmptyIdTab --bug#16827157
67 ,p_TXN_PLAN_START_DATE_tab IN PA_PLSQL_DATATYPES.DateTabTyp Default PA_PLSQL_DATATYPES.EmptyDateTab
68 ,p_TXN_PLAN_END_DATE_tab IN PA_PLSQL_DATATYPES.DateTabTyp Default PA_PLSQL_DATATYPES.EmptyDateTab
69 ,x_txn_source_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
70 ,x_res_list_member_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
71 ,x_rbs_element_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
72 ,x_txn_accum_header_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
73 ,x_return_status OUT NOCOPY Varchar2
74 ,x_msg_count OUT NOCOPY Number
75 ,x_msg_data OUT NOCOPY Varchar2
76 ) ;
77
78 /* This API will be called from Self-Service OR Java pages */
79 PROCEDURE Map_Rlmi_Rbs
80 ( p_budget_version_id IN Number
81 ,p_project_id IN Number Default NULL
82 ,p_resource_list_id IN Number Default NULL
83 ,p_rbs_version_id IN Number Default NULL
84 ,p_calling_process IN Varchar2
85 ,p_calling_context IN varchar2 Default 'PLSQL'
86 ,p_process_code IN varchar2 Default 'RES_MAP'
87 ,p_calling_mode IN Varchar2 Default 'PLSQL_TABLE'
88 ,p_init_msg_list_flag IN Varchar2 Default 'N'
89 ,p_commit_flag IN Varchar2 Default 'N'
90 ,p_TXN_SOURCE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
91 ,p_TXN_SOURCE_TYPE_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
92 ,p_PERSON_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
93 ,p_JOB_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
94 ,p_ORGANIZATION_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
95 ,p_VENDOR_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
96 ,p_EXPENDITURE_TYPE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
97 ,p_EVENT_TYPE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
98 ,p_NON_LABOR_RESOURCE_tab IN system.PA_VARCHAR2_20_TBL_TYPE Default system.PA_VARCHAR2_20_TBL_TYPE()
99 ,p_EXPENDITURE_CATEGORY_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
100 ,p_REVENUE_CATEGORY_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
101 ,p_NLR_ORGANIZATION_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
102 ,p_EVENT_CLASSIFICATION_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
103 ,p_SYS_LINK_FUNCTION_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
104 ,p_PROJECT_ROLE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
105 ,p_RESOURCE_CLASS_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
106 ,p_MFC_COST_TYPE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
107 ,p_RESOURCE_CLASS_FLAG_tab IN system.PA_VARCHAR2_1_TBL_TYPE Default system.PA_VARCHAR2_1_TBL_TYPE()
108 ,p_FC_RES_TYPE_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
109 ,p_INVENTORY_ITEM_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
110 ,p_ITEM_CATEGORY_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
111 ,p_PERSON_TYPE_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
112 ,p_BOM_RESOURCE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
113 ,p_NAMED_ROLE_tab IN system.PA_VARCHAR2_80_TBL_TYPE Default system.PA_VARCHAR2_80_TBL_TYPE()
114 ,p_INCURRED_BY_RES_FLAG_tab IN system.PA_VARCHAR2_1_TBL_TYPE Default system.PA_VARCHAR2_1_TBL_TYPE()
115 ,p_RATE_BASED_FLAG_tab IN system.PA_VARCHAR2_1_TBL_TYPE Default system.PA_VARCHAR2_1_TBL_TYPE()
116 ,p_TXN_TASK_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
117 ,p_TXN_WBS_ELEMENT_VER_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
118 ,p_TXN_RBS_ELEMENT_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
119 ,P_CBS_ELEMENT_ID_TAB IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE() --bug#16827157
120 ,p_TXN_PLAN_START_DATE_tab IN system.PA_DATE_TBL_TYPE Default system.PA_DATE_TBL_TYPE()
121 ,p_TXN_PLAN_END_DATE_tab IN system.PA_DATE_TBL_TYPE Default system.PA_DATE_TBL_TYPE()
122 ,x_txn_source_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
123 ,x_res_list_member_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
124 ,x_rbs_element_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
125 ,x_txn_accum_header_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
126 ,x_return_status OUT NOCOPY Varchar2
127 ,x_msg_count OUT NOCOPY Number
128 ,x_msg_data OUT NOCOPY Varchar2
129 ) ;
130
131 /* This API initializes the required variables into global variables */
132 PROCEDURE Init_ReqdVariables(
133 p_process_code IN varchar2
134 ,p_project_id IN Number
135 ,p_resource_list_id IN Number
136 ,p_rbs_version_id IN Number
137 ,p_budget_version_id IN NUmber );
138
139 /* This API inserts records into RBS mapping tmp tables
140 * the records will be inserted Based on calling mode
141 */
142 PROCEDURE populate_rbsmap_tmp
143 (p_budget_version_id IN Number
144 ,p_calling_mode IN varchar2
145 ,x_return_status OUT NOCOPY varchar2 ); --File.Sql.39 bug 4440895
146
147 /* This API inserts records into Resource mapping tmp tables
148 * the records will be inserted Based on calling mode
149 */
150 PROCEDURE populate_resmap_tmp
151 (p_budget_version_id IN Number
152 ,p_calling_mode IN varchar2
153 ,x_return_status OUT NOCOPY varchar2 ); --File.Sql.39 bug 4440895
154
155 /* This API reads the output records from Resource and RBS mapping tmp tables and
156 * populates the output plsql and system tables
157 */
158 PROCEDURE populate_resrbsmap_outTbls
159 (p_process_code IN Varchar2
160 ,p_calling_mode IN Varchar2
161 ,p_resource_list_id IN Number
162 ,p_budget_version_id IN Number
163 ,x_return_status OUT NOCOPY varchar2
164 );
165
166 /* This API updates the new frozen RBS version on all affected projects.
167 * Befare Calling this API, user has to populate the following global temp Table
168 * with all the affected project Ids : PA_RBS_PUSH_TMP1
169 * The out param x_return_status will be 'S' in case of Success, 'E'- Error , 'U' - Unexpected Errors
170 */
171 PROCEDURE Push_RBS_Version
172 (p_old_rbs_version_id IN NUMBER
173 ,p_new_rbs_version_id IN NUMBER
174 ,x_return_status OUT NOCOPY VARCHAR2
175 ,x_msg_count OUT NOCOPY Number
176 ,x_msg_data OUT NOCOPY Varchar2 );
177
178 END PA_RLMI_RBS_MAP_PUB ;
[Home] [Help]