1 PACKAGE PA_RLMI_RBS_MAP_PUB AS
2 /* $Header: PAFPUT3S.pls 120.1 2005/08/19 16:31:03 mwasowic noship $ */
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_TXN_PLAN_START_DATE_tab IN PA_PLSQL_DATATYPES.DateTabTyp Default PA_PLSQL_DATATYPES.EmptyDateTab
67 ,p_TXN_PLAN_END_DATE_tab IN PA_PLSQL_DATATYPES.DateTabTyp Default PA_PLSQL_DATATYPES.EmptyDateTab
68 ,x_txn_source_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
69 ,x_res_list_member_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
70 ,x_rbs_element_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
71 ,x_txn_accum_header_id_tab OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp
72 ,x_return_status OUT NOCOPY Varchar2
73 ,x_msg_count OUT NOCOPY Number
74 ,x_msg_data OUT NOCOPY Varchar2
75 ) ;
76
77 /* This API will be called from Self-Service OR Java pages */
78 PROCEDURE Map_Rlmi_Rbs
79 ( p_budget_version_id IN Number
80 ,p_project_id IN Number Default NULL
81 ,p_resource_list_id IN Number Default NULL
82 ,p_rbs_version_id IN Number Default NULL
83 ,p_calling_process IN Varchar2
84 ,p_calling_context IN varchar2 Default 'PLSQL'
85 ,p_process_code IN varchar2 Default 'RES_MAP'
86 ,p_calling_mode IN Varchar2 Default 'PLSQL_TABLE'
87 ,p_init_msg_list_flag IN Varchar2 Default 'N'
88 ,p_commit_flag IN Varchar2 Default 'N'
89 ,p_TXN_SOURCE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
90 ,p_TXN_SOURCE_TYPE_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
91 ,p_PERSON_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
92 ,p_JOB_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
93 ,p_ORGANIZATION_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
94 ,p_VENDOR_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
95 ,p_EXPENDITURE_TYPE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
96 ,p_EVENT_TYPE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
97 ,p_NON_LABOR_RESOURCE_tab IN system.PA_VARCHAR2_20_TBL_TYPE Default system.PA_VARCHAR2_20_TBL_TYPE()
98 ,p_EXPENDITURE_CATEGORY_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
99 ,p_REVENUE_CATEGORY_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
100 ,p_NLR_ORGANIZATION_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
101 ,p_EVENT_CLASSIFICATION_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
102 ,p_SYS_LINK_FUNCTION_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
103 ,p_PROJECT_ROLE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
104 ,p_RESOURCE_CLASS_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
105 ,p_MFC_COST_TYPE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
106 ,p_RESOURCE_CLASS_FLAG_tab IN system.PA_VARCHAR2_1_TBL_TYPE Default system.PA_VARCHAR2_1_TBL_TYPE()
107 ,p_FC_RES_TYPE_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
108 ,p_INVENTORY_ITEM_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
109 ,p_ITEM_CATEGORY_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
110 ,p_PERSON_TYPE_CODE_tab IN system.PA_VARCHAR2_30_TBL_TYPE Default system.PA_VARCHAR2_30_TBL_TYPE()
111 ,p_BOM_RESOURCE_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
112 ,p_NAMED_ROLE_tab IN system.PA_VARCHAR2_80_TBL_TYPE Default system.PA_VARCHAR2_80_TBL_TYPE()
113 ,p_INCURRED_BY_RES_FLAG_tab IN system.PA_VARCHAR2_1_TBL_TYPE Default system.PA_VARCHAR2_1_TBL_TYPE()
114 ,p_RATE_BASED_FLAG_tab IN system.PA_VARCHAR2_1_TBL_TYPE Default system.PA_VARCHAR2_1_TBL_TYPE()
115 ,p_TXN_TASK_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
116 ,p_TXN_WBS_ELEMENT_VER_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
117 ,p_TXN_RBS_ELEMENT_ID_tab IN system.PA_NUM_TBL_TYPE Default system.PA_NUM_TBL_TYPE()
118 ,p_TXN_PLAN_START_DATE_tab IN system.PA_DATE_TBL_TYPE Default system.PA_DATE_TBL_TYPE()
119 ,p_TXN_PLAN_END_DATE_tab IN system.PA_DATE_TBL_TYPE Default system.PA_DATE_TBL_TYPE()
120 ,x_txn_source_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
121 ,x_res_list_member_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
122 ,x_rbs_element_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
123 ,x_txn_accum_header_id_tab OUT NOCOPY system.PA_NUM_TBL_TYPE
124 ,x_return_status OUT NOCOPY Varchar2
125 ,x_msg_count OUT NOCOPY Number
126 ,x_msg_data OUT NOCOPY Varchar2
127 ) ;
128
129 /* This API initializes the required variables into global variables */
130 PROCEDURE Init_ReqdVariables(
131 p_process_code IN varchar2
132 ,p_project_id IN Number
133 ,p_resource_list_id IN Number
134 ,p_rbs_version_id IN Number
135 ,p_budget_version_id IN NUmber );
136
137 /* This API inserts records into RBS mapping tmp tables
138 * the records will be inserted Based on calling mode
139 */
140 PROCEDURE populate_rbsmap_tmp
141 (p_budget_version_id IN Number
142 ,p_calling_mode IN varchar2
143 ,x_return_status OUT NOCOPY varchar2 ); --File.Sql.39 bug 4440895
144
145 /* This API inserts records into Resource mapping tmp tables
146 * the records will be inserted Based on calling mode
147 */
148 PROCEDURE populate_resmap_tmp
149 (p_budget_version_id IN Number
150 ,p_calling_mode IN varchar2
151 ,x_return_status OUT NOCOPY varchar2 ); --File.Sql.39 bug 4440895
152
153 /* This API reads the output records from Resource and RBS mapping tmp tables and
154 * populates the output plsql and system tables
155 */
156 PROCEDURE populate_resrbsmap_outTbls
157 (p_process_code IN Varchar2
158 ,p_calling_mode IN Varchar2
159 ,p_resource_list_id IN Number
160 ,p_budget_version_id IN Number
161 ,x_return_status OUT NOCOPY varchar2
162 );
163
164 /* This API updates the new frozen RBS version on all affected projects.
165 * Befare Calling this API, user has to populate the following global temp Table
166 * with all the affected project Ids : PA_RBS_PUSH_TMP1
167 * The out param x_return_status will be 'S' in case of Success, 'E'- Error , 'U' - Unexpected Errors
168 */
169 PROCEDURE Push_RBS_Version
170 (p_old_rbs_version_id IN NUMBER
171 ,p_new_rbs_version_id IN NUMBER
172 ,x_return_status OUT NOCOPY VARCHAR2
173 ,x_msg_count OUT NOCOPY Number
174 ,x_msg_data OUT NOCOPY Varchar2 );
175
176 END PA_RLMI_RBS_MAP_PUB ;