[Home] [Help]
PACKAGE BODY: APPS.PA_PLAN_RES_DEFAULTS_PUB
Source
1 package body pa_plan_res_defaults_pub as
2 /* $Header: PARPRDPB.pls 120.1 2007/02/06 09:55:49 dthakker ship $ */
3 procedure UPDATE_RESOURCE_DEFAULTS (
4 P_PLAN_RES_DEF_ID_TBL IN system.pa_num_tbl_type ,
5 P_RESOURCE_CLASS_ID_TBL IN system.pa_num_tbl_type ,
6 P_OBJECT_TYPE_TBL IN system.pa_varchar2_30_tbl_type ,
7 P_OBJECT_ID_TBL IN system.pa_num_tbl_type ,
8 P_SPREAD_CURVE_ID_TBL IN system.pa_num_tbl_type ,
9 P_ETC_METHOD_CODE_TBL IN system.pa_varchar2_30_tbl_type ,
10 P_EXPENDITURE_TYPE_TBL IN system.pa_varchar2_30_tbl_type ,
11 P_ITEM_CATEGORY_SET_ID_TBL IN system.pa_num_tbl_type ,
12 P_ITEM_MASTER_ID_TBL IN system.pa_num_tbl_type ,
13 P_MFC_COST_TYPE_ID_TBL IN system.pa_num_tbl_type ,
14 P_ENABLED_FLAG_TBL IN system.pa_varchar2_1_tbl_type ,
15 X_RECORD_VERSION_NUMBER_TBL IN OUT NOCOPY system.pa_num_tbl_type ,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2
19 ) is
20
21 l_item_master_id NUMBER;
22 l_item_category_set_id NUMBER;
23 l_master_used VARCHAR2(1) := 'N';
24 l_category_used VARCHAR2(1) := 'N';
25 l_master_error EXCEPTION;
26 l_category_error EXCEPTION;
27 l_invalid_cat_set EXCEPTION; -- Bug 3768550
28 l_cat_flag VARCHAR2(1) := 'N'; -- Bug 3768550
29 l_cat_name VARCHAR2(30); -- Bug 3768550
30 begin
31 -- Initialize the error stack.
32 fnd_msg_pub.initialize;
33
34 --hr_utility.trace_on(NULL, 'RMCL');
35 --hr_utility.trace('strt update');
36 x_msg_count := 0;
37 x_return_status := FND_API.G_RET_STS_SUCCESS;
38
39 IF P_PLAN_RES_DEF_ID_TBL.COUNT > 0 THEN
40 FOR i IN P_PLAN_RES_DEF_ID_TBL.FIRST .. P_PLAN_RES_DEF_ID_TBL.LAST LOOP
41 --hr_utility.trace('in first if ');
42 -- Get the existing Item Master and Item Category.
43 BEGIN
44 select ITEM_CATEGORY_SET_ID, ITEM_MASTER_ID
45 into l_item_category_set_id, l_item_master_id
46 from PA_PLAN_RES_DEFAULTS
47 where PLAN_RES_DEFAULT_ID = P_PLAN_RES_DEF_ID_TBL(i);
48
49 EXCEPTION
50 WHEN NO_DATA_FOUND THEN
51 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
52 x_msg_count := x_msg_count + 1;
53 RETURN;
54 --FND_MSG_PUB.add_exc_msg(
55 --p_pkg_name => 'pa_plan_res_defaults_pub',
56 --p_procedure_name => 'UPDATE_RESOURCE_DEFAULTS');
57 END;
58 -- Check whether Item Master is used anywhere
59 IF l_item_master_id IS NOT NULL THEN
60 --hr_utility.trace('l_item_master_id is not null ');
61 IF l_item_master_id <> nvl(P_ITEM_MASTER_ID_TBL(i), -99) THEN
62
63 IF (PA_DELIVERABLE_UTILS.IS_ITEM_BASED_DLV_EXISTS = 'Y') THEN
64 l_master_used := 'Y';
65 ELSE
66 --hr_utility.trace('l_item_master_id is | ' || l_item_master_id);
67 --hr_utility.trace('P_ITEM_MASTER_ID_TBL(i) is | ' || P_ITEM_MASTER_ID_TBL(i));
68 BEGIN
69 select 'Y'
70 into l_master_used
71 from pa_res_formats_b format,
72 pa_res_types_b types
73 where types.res_type_code = 'INVENTORY_ITEM'
74 and types.res_type_id = format.res_type_id
75 and exists (select 'Y'
76 from PA_RESOURCE_LIST_MEMBERS rlm
77 where rlm.res_format_id = format.res_format_id);
78 --and rlm.inventory_item_id is not null)
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN
81 l_master_used := 'N';
82 WHEN TOO_MANY_ROWS THEN
83 l_master_used := 'Y';
84 END;
85 END IF;
86 END IF;
87 END IF;
88
89 IF (l_master_used = 'Y') THEN
90 --hr_utility.trace('l_master_error ');
91 raise l_master_error;
92 END IF;
93
94 -- Check whether Item Category is used anywhere
95 IF l_item_category_set_id IS NOT NULL THEN
96 IF l_item_category_set_id <>
97 nvl(P_ITEM_CATEGORY_SET_ID_TBL(i), -99) THEN
98
99 --hr_utility.trace('l_item_category_set_id is not null ');
100 --hr_utility.trace('l_item_category_set_id is | ' || l_item_category_set_id);
101 --hr_utility.trace('P_ITEM_CATEGORY_SET_ID_TBL(i) is | ' || P_ITEM_CATEGORY_SET_ID_TBL(i));
102 --hr_utility.trace('length of P_ITEM_CATEGORY_SET_ID_TBL(i) is | ' || length(P_ITEM_CATEGORY_SET_ID_TBL(i)));
103 -- Begin changes for bug 3768550
104 -- Check whether the default for item category set has had its
105 -- MULT_ITEM_CAT_ASSIGN_FLAG changed from N to Y making it
106 -- ineligible and raise a specific error.
107 IF (P_ITEM_CATEGORY_SET_ID_TBL(i) IS NULL OR
108 (P_ITEM_CATEGORY_SET_ID_TBL(i) = 0)) THEN
109 --hr_utility.trace('in new check');
110 BEGIN
111 SELECT MULT_ITEM_CAT_ASSIGN_FLAG, CATEGORY_SET_NAME
112 INTO l_cat_flag, l_cat_name
113 FROM mtl_category_sets_vl
114 WHERE category_set_id = l_item_category_set_id;
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 raise l_invalid_cat_set;
118 END;
119 IF l_cat_flag = 'Y' THEN
120 raise l_invalid_cat_set;
121 END IF;
122 END IF;
123 -- end changes for bug 3768550
124
125 BEGIN
126 select 'Y'
127 into l_category_used
128 from pa_res_formats_b format,
129 pa_res_types_b types
130 where types.res_type_code = 'ITEM_CATEGORY'
131 and types.res_type_id = format.res_type_id
132 and exists (select 'Y'
133 from PA_RESOURCE_LIST_MEMBERS rlm
134 where rlm.res_format_id = format.res_format_id);
135 --and rlm.item_category_id is not null)
136 EXCEPTION
137 WHEN NO_DATA_FOUND THEN
138 l_category_used := 'N';
139 WHEN TOO_MANY_ROWS THEN
140 l_category_used := 'Y';
141 END;
142
143 END IF;
144 END IF;
145
146 IF l_category_used = 'Y' THEN
147 raise l_category_error;
148 END IF;
149
150 --hr_utility.trace('before UPDATE_ROW ');
151 pa_plan_res_defaults_pvt.UPDATE_ROW (
152 P_PLAN_RES_DEFAULT_ID => P_PLAN_RES_DEF_ID_TBL(i) ,
153 P_RESOURCE_CLASS_ID => P_RESOURCE_CLASS_ID_TBL(i) ,
154 P_OBJECT_TYPE => P_OBJECT_TYPE_TBL(i) ,
155 P_OBJECT_ID => P_OBJECT_ID_TBL(i) ,
156 P_SPREAD_CURVE_ID => P_SPREAD_CURVE_ID_TBL(i) ,
157 P_ETC_METHOD_CODE => P_ETC_METHOD_CODE_TBL(i) ,
158 P_EXPENDITURE_TYPE => P_EXPENDITURE_TYPE_TBL(i) ,
159 P_ITEM_CATEGORY_SET_ID => P_ITEM_CATEGORY_SET_ID_TBL(i) ,
160 P_ITEM_MASTER_ID => P_ITEM_MASTER_ID_TBL(i) ,
161 P_MFC_COST_TYPE_ID => P_MFC_COST_TYPE_ID_TBL(i) ,
162 P_ENABLED_FLAG => P_ENABLED_FLAG_TBL(i) ,
163 X_RECORD_VERSION_NUMBER => X_RECORD_VERSION_NUMBER_TBL(i) ,
164 P_LAST_UPDATE_DATE => sysdate ,
165 P_LAST_UPDATED_BY => fnd_global.user_id ,
166 P_LAST_UPDATE_LOGIN => fnd_global.user_id ,
167 x_return_status => x_return_status ,
168 x_msg_count => x_msg_count ,
169 x_msg_data => x_msg_data);
170 --hr_utility.trace('x_return_status is | ' || x_return_status);
171
172 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
173 pa_utils.add_message('PA', x_msg_data);
174 RETURN;
175 END IF;
176 END LOOP;
177 END IF;
178
179 EXCEPTION
180 WHEN l_master_error THEN
181 x_return_status := FND_API.G_RET_STS_ERROR;
182 x_msg_data := 'PA_RES_ITEM_MASTER_USED';
183 x_msg_count := x_msg_count + 1;
184 pa_utils.add_message('PA', x_msg_data);
185 WHEN l_invalid_cat_set THEN -- Bug 3768550
186 x_return_status := FND_API.G_RET_STS_ERROR;
187 x_msg_data := 'PA_RES_INV_ITEM_CAT_SET';
188 x_msg_count := x_msg_count + 1;
189 Pa_Utils.Add_Message(P_App_Short_Name => 'PA',
190 P_Msg_Name => 'PA_RES_INV_ITEM_CAT_SET',
191 p_token1 => 'CAT_NAME',
192 p_value1 => l_cat_name);
193
194 WHEN l_category_error THEN
195 x_return_status := FND_API.G_RET_STS_ERROR;
196 x_msg_data := 'PA_RES_ITEM_CATEGORY_USED';
197 x_msg_count := x_msg_count + 1;
198 pa_utils.add_message('PA', x_msg_data);
199 WHEN OTHERS THEN
200 -- FND_MSG_PUB.add_exc_msg(
201 -- p_pkg_name => 'pa_plan_res_defaults_pub.UPDATE_RESOURCE_DEFAULTS'
202 -- ,p_procedure_name => PA_DEBUG.G_Err_Stack);
203 x_msg_count := x_msg_count + 1;
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
205 x_msg_data := substr(SQLERRM, 1, 25);
206
207 end UPDATE_RESOURCE_DEFAULTS;
208
209 end pa_plan_res_defaults_pub;