[Home] [Help]
PACKAGE BODY: APPS.WIP_WEIGHTED_AVG
Source
1 PACKAGE BODY WIP_WEIGHTED_AVG AS
2 /* $Header: wipavgb.pls 120.2.12020000.2 2012/07/18 07:49:20 ankohli ship $ */
3
4 procedure get_parms(
5 p_org_id in number,
6 p_pri_cost out nocopy number,
7 p_auto_cmp out nocopy number,
8 p_ret_code out nocopy number,
9 p_ret_msg out nocopy varchar2) is
10
11 cursor get_final_cmp_flag(c_org_id number) is
12 select nvl(wp.auto_compute_final_completion, WIP_CONSTANTS.NO)
13 from wip_parameters wp
14 where wp.organization_id = c_org_id;
15
16 cursor get_cost_method(c_org_id number) is
17 select mp.primary_cost_method
18 from mtl_parameters mp
19 where mp.organization_id = c_org_id;
20
21 x_parms_found boolean;
22 x_pri_cost number;
23 x_auto_cmp number;
24 begin
25 -- initialize
26 p_ret_code := 0;
27 p_ret_msg := NULL;
28
29 -- get cost method
30 open get_cost_method(p_org_id);
31 fetch get_cost_method into x_pri_cost;
32 x_parms_found := get_cost_method%FOUND;
33 close get_cost_method;
34
35 p_pri_cost := x_pri_cost;
36
37 -- if parameters not found then return error
38 if (not x_parms_found) then
39 fnd_message.set_name('WIP', 'WIP_DEFINE_INV_PARAMETERS');
40 p_pri_cost := -1;
41 p_ret_code := -1;
42 p_ret_msg := fnd_message.get;
43 return;
44 end if;
45
46 -- if not actual costing, nothing to do
47 if (x_pri_cost not in ( WIP_CONSTANTS.COST_AVG,
48 WIP_CONSTANTS.COST_STD, /*Fix for bug 8472985(FP 8320930)*/
49 WIP_CONSTANTS.COST_FIFO,
50 WIP_CONSTANTS.COST_LIFO ) ) then
51 p_auto_cmp := WIP_CONSTANTS.NO;
52 return;
53 end if;
54
55 -- get final completion flag
56 open get_final_cmp_flag(p_org_id);
57 fetch get_final_cmp_flag into x_auto_cmp;
58 x_parms_found := get_final_cmp_flag%FOUND;
59 close get_final_cmp_flag;
60
61 p_auto_cmp := x_auto_cmp;
62
63 -- if parameters not found then return error
64 if (not x_parms_found) then
65 fnd_message.set_name('WIP', 'WIP_DEFINE_WIP_PARAMETERS');
66 p_auto_cmp := -1;
67 p_ret_code := -1;
68 p_ret_msg := fnd_message.get;
69 return;
70 end if;
71
72 return;
73 end get_parms;
74
75 /* Fix for bug 4588479; FP 4496088: Re-wrote this procedure.
76 wdj.quantity_completed wouldn't have the correct picture for
77 LPN Completions, which would lead to wrong values returned by the
78 final_complete procedure. Get this quantity from MMT instead. */
79
80 procedure get_rem_qty(
81 p_org_id in number,
82 p_wip_id in number,
83 p_rem_qty out nocopy number) is
84
85 l_net_job_qty number;
86 l_mmt_cmp_qty number;
87
88 cursor get_net_job_qty (
89 c_org_id NUMBER,
90 c_wip_id NUMBER) is
91 select start_quantity - quantity_scrapped
92 from wip_discrete_jobs
93 where wip_entity_id = c_wip_id
94 and organization_id = c_org_id;
95
96 cursor get_mmt_cmp_qty (
97 c_org_id NUMBER,
98 c_wip_id NUMBER) is
99 select nvl(sum(primary_quantity),0)
100 from mtl_material_transactions
101 where transaction_source_type_id = 5
102 and transaction_source_id = c_wip_id
103 and organization_id = c_org_id
104 and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
105 WIP_CONSTANTS.RETASSY_ACTION);
106 begin
107
108 open get_net_job_qty(p_org_id, p_wip_id);
109 fetch get_net_job_qty into l_net_job_qty;
110 close get_net_job_qty;
111
112 open get_mmt_cmp_qty(p_org_id, p_wip_id);
113 fetch get_mmt_cmp_qty into l_mmt_cmp_qty;
114 close get_mmt_cmp_qty;
115
116 p_rem_qty := l_net_job_qty - l_mmt_cmp_qty;
117
118 end get_rem_qty;
119
120 procedure final_complete(
121 p_org_id in number,
122 p_wip_id in number,
123 p_pri_qty in number,
124 p_final_cmp in out nocopy varchar2,
125 p_ret_code out nocopy number,
126 p_ret_msg out nocopy varchar2) is
127
128 x_auto_cmp number;
129 x_cost_method number;
130 x_rem_qty number;
131 x_parms_found boolean;
132 x_ret_code number;
133 l_remain_job_qty number := 0; -- Fix bug 13826369
134
135 begin
136 -- initialize
137 p_ret_code := 0;
138 p_ret_msg := NULL;
139
140 -- get parameters
141 get_parms(
142 p_org_id => p_org_id,
143 p_pri_cost => x_cost_method,
144 p_auto_cmp => x_auto_cmp,
145 p_ret_code => x_ret_code,
146 p_ret_msg => p_ret_msg);
147
148 if (x_ret_code <> 0) then
149 p_ret_code := x_ret_code;
150 return;
151 end if;
152
153 -- if not auto completion then nothing to do
154 if (x_auto_cmp <> WIP_CONSTANTS.YES) then
155 return;
156 end if;
157
158 -- Fix bug 13826369
159 begin
160 select sum(quantity_in_queue)
161 + sum(quantity_running)
162 + sum(quantity_waiting_to_move)
163 + sum(quantity_rejected)
164 into l_remain_job_qty
165 from wip_operations
166 where wip_entity_id = p_wip_id
167 and organization_id = p_org_id;
168
169 if (l_remain_job_qty is null) then
170 l_remain_job_qty := 0;
171 end if;
172 exception
173 when NO_DATA_FOUND then
174 l_remain_job_qty := 0;
175 end;
176
177 get_rem_qty(p_org_id, p_wip_id, x_rem_qty);
178
179 -- Fix bug 13826369, only update final_completion_flag to 'Y' if there
180 -- isn't any remaining qty. This is based on Costing Team suggestion.
181 if (p_pri_qty >= x_rem_qty AND l_remain_job_qty = 0) then
182 p_final_cmp := 'Y';
183 end if;
184
185 return;
186
187 exception
188 when others then
189 p_ret_code := 1;
190 p_ret_msg := fnd_api.g_ret_sts_unexp_error;
191 return;
192 end final_complete;
193
194 procedure final_complete(
195 p_org_id in number,
196 p_wip_id in number,
197 p_mtl_hdr_id in number,
198 p_ret_code out nocopy number,
199 p_ret_msg out nocopy varchar2) is
200
201 cursor get_cmp_txns(c_mtl_hdr_id number) is
202 select mmtt.rowid,
203 mmtt.primary_quantity
204 from mtl_material_transactions_temp mmtt
205 where mmtt.transaction_header_id = c_mtl_hdr_id
206 and mmtt.transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
207 order by mmtt.source_line_id;
208
209 x_auto_cmp number;
210 x_cost_method number;
211
212 x_cmp_txn_id number;
213 x_rowid varchar2(30);
214 x_pri_qty number;
215 x_rem_qty number;
216
217 x_done boolean := FALSE;
218 x_ret_code number;
219 l_remain_job_qty number := 0; -- Fix bug 13826369
220
221 begin
222 -- initialize
223 p_ret_code := 0;
224 p_ret_msg := NULL;
225
226 -- get parameters
227 get_parms(
228 p_org_id => p_org_id,
229 p_pri_cost => x_cost_method,
230 p_auto_cmp => x_auto_cmp,
231 p_ret_code => x_ret_code,
232 p_ret_msg => p_ret_msg);
233
234 if (x_ret_code <> 0) then
235 p_ret_code := x_ret_code;
236 return;
237 end if;
238
239 -- if not auto completion then nothing to do
240 if (x_auto_cmp <> WIP_CONSTANTS.YES) then
241 return;
242 end if;
243
244 get_rem_qty(p_org_id, p_wip_id, x_rem_qty);
245
246 -- get completion transactions given header id
247 open get_cmp_txns(p_mtl_hdr_id);
248
249 -- Fix bug 13826369
250 begin
251 select sum(quantity_in_queue)
252 + sum(quantity_running)
253 + sum(quantity_waiting_to_move)
254 + sum(quantity_rejected)
255 into l_remain_job_qty
256 from wip_operations
257 where wip_entity_id = p_wip_id
258 and organization_id = p_org_id;
259
260 if (l_remain_job_qty is null) then
261 l_remain_job_qty := 0;
262 end if;
263
264 exception
265 when NO_DATA_FOUND then
266 l_remain_job_qty := 0;
267 end;
268
269 loop
270 fetch get_cmp_txns into x_rowid, x_pri_qty;
271 x_done := get_cmp_txns%NOTFOUND;
272 exit when x_done;
273
274 -- update completion flag
275 -- Fix bug 13826369, only update final_completion_flag to 'Y' if there
276 -- isn't any remaining qty. This is based on Costing Team suggestion.
277 if (x_pri_qty >= x_rem_qty AND l_remain_job_qty = 0) then
278 update mtl_material_transactions_temp
279 set final_completion_flag = 'Y'
280 where rowid = x_rowid;
281 end if;
282
283 -- allocate quantity
284 x_rem_qty := x_rem_qty - x_pri_qty;
285 end loop;
286
287 close get_cmp_txns;
288
289 return;
290
291 exception
292 when others then
293 p_ret_code := 1;
294 p_ret_msg := fnd_api.g_ret_sts_unexp_error;
295 return;
296 end final_complete;
297
298 END WIP_WEIGHTED_AVG;