[Home] [Help]
PACKAGE BODY: APPS.MSC_ATP_EXCEPTIONS
Source
1 PACKAGE BODY MSC_ATP_EXCEPTIONS AS
2 /* $Header: MSCATPXB.pls 120.1 2007/12/12 10:22:59 sbnaik ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_ATP_EXCEPTIONS';
5 PG_DEBUG VARCHAR2(1) := Order_Sch_Wb.mr_debug;
6
7 -- Function to get the plan type for a given plan_id.
8 -- Returns 1 if the plan is optimized or constrained.
9 -- Returns 0 otherwise
10 -- Should be replaced by checks in Get_Plan_Info
11 PROCEDURE Get_Plan_Constraints (
12 p_plan_id in number,
13 x_plan_type out NOCOPY number
14 ) IS
15
16 l_optimized_plan number;
17 l_constrain_plan number;
18 BEGIN
19
20 x_plan_type := 0;
21 SELECT DECODE(plans.plan_type, 4, 2,
22 DECODE(daily_material_constraints, 1, 1,
23 DECODE(daily_resource_constraints, 1, 1,
24 DECODE(weekly_material_constraints, 1, 1,
25 DECODE(weekly_resource_constraints, 1, 1,
26 DECODE(period_material_constraints, 1, 1,
27 DECODE(period_resource_constraints, 1, 1, 2)
28 )
29 )
30 )
31 )
32 )
33 ),
34 DECODE(NVL(fnd_profile.value('MSO_BATCHABLE_FLAG'),'N'), 'Y', DECODE(plans.plan_type, 4, 0,2,0,
35 DECODE(daily_material_constraints, 1, 1,
36 DECODE(daily_resource_constraints, 1, 1,
37 DECODE(weekly_material_constraints, 1, 1,
38 DECODE(weekly_resource_constraints, 1, 1,
39 DECODE(period_material_constraints, 1, 1,
40 DECODE(period_resource_constraints, 1, 1, 2)
41 )
42 )
43 )
44 )
45 )
46 ), 0)
47
48 INTO l_optimized_plan,l_constrain_plan
49 FROM msc_designators desig,
50 msc_plans plans
51 WHERE plans.plan_id = p_plan_id
52 AND desig.designator = plans.compile_designator
53 AND desig.sr_instance_id = plans.sr_instance_id
54 AND desig.organization_id = plans.organization_id;
55
56 if (l_optimized_plan = 1) or (l_constrain_plan = 1) then
57 x_plan_type := 1;
58 end if;
59
60 EXCEPTION
61 WHEN OTHERS THEN
62 x_plan_type := 0;
63 if (PG_DEBUG in ('Y','C')) then
64 msc_sch_wb.atp_debug ('Get_Plan_Constraints: Exception encountered');
65 end if;
66 END Get_Plan_Constraints;
67
68 -- Add a new ATP Exception
69
70 PROCEDURE Add_ATP_Exception (
71 p_session_id IN NUMBER,
72 p_exception_rec IN OUT NOCOPY MSC_ATP_EXCEPTIONS.ATP_Exception_Rec_Typ,
73 x_return_status OUT NOCOPY VARCHAR2
74 ) IS
75
76 l_insert_item_exception NUMBER := 0;
77 -- l_plan_type NUMBER := 0; -- bug 2795053-reopen (ssurendr): plan_type variable removed
78 l_tmp_var NUMBER := 0;
79
80 BEGIN
81
82 -- Debug Output
83 if (PG_DEBUG in ('Y', 'C')) then
84 msc_sch_wb.atp_debug ('********** Begin Add_ATP_Exception **********');
85 msc_sch_wb.atp_debug ('Input Record Dump: ');
86 msc_sch_wb.atp_debug (' Exception_Type ' || p_exception_rec.exception_type);
87 msc_sch_wb.atp_debug (' Exception_Grp ' || p_exception_rec.exception_group);
88 msc_sch_wb.atp_debug (' Plan ID ' || p_exception_rec.plan_id);
89 msc_sch_wb.atp_debug (' Organization ID ' || p_exception_rec.organization_id);
90 msc_sch_wb.atp_debug (' Inventory Item ID ' || p_exception_rec.inventory_item_id); --4235545
91 msc_sch_wb.atp_debug (' Sr Instance ID ' || p_exception_rec.sr_instance_ID);
92 msc_sch_wb.atp_debug (' Demand ID ' || p_exception_rec.demand_id);
93 msc_sch_wb.atp_debug (' Quantity ' || p_exception_rec.quantity);
94 msc_sch_wb.atp_debug (' Qty. Satisfied ' || p_exception_rec.quantity_satisfied);
95 msc_sch_wb.atp_debug (' Dmd Satisfy Dt ' || p_exception_rec.demand_satisfy_date);
96 msc_sch_wb.atp_debug (' Order Number ' || p_exception_rec.order_number);
97 msc_sch_wb.atp_debug (' Customer ID ' || p_exception_rec.customer_id);
98 msc_sch_wb.atp_debug (' Cusustomer Site ID ' || p_exception_rec.customer_site_id); --4235545
99 msc_sch_wb.atp_debug ('----------------------------------------------- ');
100 end if;
101
102 -- Initialize variables
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104
105 -- Error Checking
106 if (p_exception_rec.exception_group <> 5) OR -- Late Sales order
107 -- bug 2795053-reopen (ssurendr): Exception type check changed.
108 (p_exception_rec.exception_type <> 68 ) THEN -- Overcommitment of Sales Order
109 if (PG_DEBUG in ('Y', 'C')) then
110 msc_sch_wb.atp_debug ('AEX: Only supported exceptions are :');
111 msc_sch_wb.atp_debug ('AEX: Group = 5');
112 msc_sch_wb.atp_debug ('AEX: Type = 68');
113 end if;
114 x_return_status := FND_API.G_RET_STS_ERROR;
115 RAISE FND_API.G_EXC_ERROR;
116 end if;
117
118 /*
119 bug 2795053-reopen (ssurendr): Plan type check removed.
120 -- Set Exception group and type based on plan type.
121 GET_PLAN_CONSTRAINTS (p_exception_rec.plan_id, l_plan_type);
122
123 if (l_plan_type = 1) then
124 p_exception_rec.exception_type := 24; -- Constrained plan , use late repln.
125 else
126 p_exception_rec.exception_type := 15; -- Unconstrained, use late supply
127 end if;
128 if (PG_DEBUG in ('Y', 'C')) then
129 msc_sch_wb.atp_debug ('Plan type as determined is : '|| l_plan_type);
130 msc_sch_wb.atp_debug ('Exception type after check is : '|| p_exception_rec.exception_type);
131 end if;
132 */
133
134 -- First Insert Data into Exception Details
135 BEGIN
136 insert into msc_exception_details (
137 exception_detail_id,
138 exception_type,
139 quantity,
140 date1,-- Demand Satisfaction Date
141 number1, -- Demand ID
142 number3, -- Demand date quantity
143 plan_id,
144 organization_id,
145 inventory_item_id,
146 resource_id,
147 department_id,
148 sr_instance_id,
149 last_update_date,
150 last_updated_by,
151 creation_date,
152 created_by,
153 number4 -- Origination
154 )
155 values (
156 msc_exception_details_s.nextval,
157 p_exception_rec.exception_type,
158 p_exception_rec.quantity,
159 p_exception_rec.demand_satisfy_date, -- This is a misnomer. This actually contains demand date.
160 p_exception_rec.demand_id,
161 p_exception_rec.quantity_satisfied,
162 p_exception_rec.plan_id,
163 p_exception_rec.organization_id,
164 p_exception_rec.inventory_item_id,
165 -1,
166 -1,
167 p_exception_rec.sr_instance_id,
168 sysdate,
169 FND_GLOBAL.USER_ID,
170 sysdate,
171 FND_GLOBAL.USER_ID,
172 MSC_ATP_EXCEPTIONS.G_ATP_EXCEPTION_ORIGIN_CODE
173 );
174 EXCEPTION
175 WHEN OTHERS THEN
176 if (PG_DEBUG in ('Y','C')) then
177 msc_sch_wb.atp_debug ('AEX: Unable to add exception details record');
178 msc_sch_wb.atp_debug ('Error is: ' || sqlerrm);
179 msc_sch_wb.atp_debug ('Code : ' || sqlcode);
180 end if;
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
183 END;
184
185 if (PG_DEBUG in ('Y','C')) then
186 msc_sch_wb.atp_debug ('AEX: Inserted details record. Now updating item record');
187 end if;
188
189 -- Next try an dupdate the exception count
190 BEGIN
191 select 1
192 into l_tmp_var
193 from msc_item_exceptions
194 where plan_id = p_exception_rec.plan_id
195 and organization_id = p_exception_rec.organization_id
196 and sr_instance_id = p_exception_rec.sr_instance_id
197 and inventory_item_id = p_exception_rec.inventory_item_id
198 and exception_type = p_exception_rec.exception_type;
199
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 if (PG_DEBUG in ('Y','C')) then
203 msc_sch_wb.atp_debug ('AEX: No record found in item exception table');
204 end if;
205 l_insert_item_exception := 1;
206 WHEN TOO_MANY_ROWS THEN
207 if (PG_DEBUG in ('Y','C')) then
208 msc_sch_wb.atp_debug ('AEX: Multiple records found in item exception table');
209 end if;
210 l_insert_item_exception := 0;
211 WHEN OTHERS THEN
212 if (PG_DEBUG in ('Y','C')) then
213 msc_sch_wb.atp_debug ('AEX: Unable to add item exception record');
214 msc_sch_wb.atp_debug ('Code : ' || sqlcode);
215 msc_sch_wb.atp_debug ('Error is: ' || sqlerrm);
216 end if;
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219 END;
220
221 if (l_insert_item_exception = 1 ) THEN
222 BEGIN
223 -- insert into msc_item_exceptions
224 insert into msc_item_exceptions (
225 plan_id,
226 organization_id,
227 sr_instance_id,
228 inventory_item_id,
229 exception_type,
230 exception_group,
231 -- exception_count, bug 2795053-reopen (ssurendr): Count removed
232 last_update_date,
233 last_updated_by,
234 creation_date,
235 created_by
236 )
237 values (
238 p_exception_rec.plan_id,
239 p_exception_rec.organization_id,
240 p_exception_rec.sr_instance_id,
241 p_exception_rec.inventory_item_id,
242 p_exception_rec.exception_type,
243 p_exception_rec.exception_group,
244 -- 1, bug 2795053-reopen (ssurendr): Count removed.
245 sysdate,
246 FND_GLOBAL.USER_ID,
247 sysdate,
248 FND_GLOBAL.USER_ID
249 );
250 if (PG_DEBUG in ('Y','C')) then
251 msc_sch_wb.atp_debug ('AEX: Inserted item exception record');
252 end if;
253 EXCEPTION
254 WHEN OTHERS THEN
255 if (PG_DEBUG in ('Y','C')) then
256 msc_sch_wb.atp_debug('Add_Exception: Error while inserting new exception type');
257 msc_sch_wb.atp_debug('Error is: ' || sqlerrm);
258 msc_sch_wb.atp_debug ('Code : ' || sqlcode);
259 end if;
260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262 END;
263 end if;
264
265 EXCEPTION
266
267 WHEN FND_API.G_EXC_ERROR THEN
268 if (PG_DEBUG in ('Y','C')) then
269 msc_sch_wb.atp_debug ('Add_Exception: G_EXC_ERROR');
270 msc_sch_wb.atp_debug ('Code : ' || sqlcode);
271 msc_sch_wb.atp_debug ('Error is: ' || sqlerrm);
272 end if;
273
274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
275 if (PG_DEBUG in ('Y','C')) then
276 msc_sch_wb.atp_debug ('Add_Exception: G_EXC_UNEXPECTED_ERROR');
277 msc_sch_wb.atp_debug ('Code : ' || sqlcode);
278 msc_sch_wb.atp_debug ('Error is: ' || sqlerrm);
279 end if;
280
281 WHEN OTHERS THEN
282 if (PG_DEBUG in ('Y','C')) then
283 msc_sch_wb.atp_debug ('Add_Exception: OTHERS');
284 msc_sch_wb.atp_debug ('Code : ' || sqlcode);
285 msc_sch_wb.atp_debug ('Error is: ' || sqlerrm);
286 end if;
287
288 END Add_ATP_Exception;
289
290 END MSC_ATP_EXCEPTIONS;