[Home] [Help]
PACKAGE BODY: APPS.WIP_WEIGHTED_AVG
Source
1 PACKAGE BODY WIP_WEIGHTED_AVG AS
2 /* $Header: wipavgb.pls 120.1 2006/02/03 11:09:47 sjchen noship $ */
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_FIFO,
49 WIP_CONSTANTS.COST_LIFO ) ) then
50 p_auto_cmp := WIP_CONSTANTS.NO;
51 return;
52 end if;
53
54 -- get final completion flag
55 open get_final_cmp_flag(p_org_id);
56 fetch get_final_cmp_flag into x_auto_cmp;
57 x_parms_found := get_final_cmp_flag%FOUND;
58 close get_final_cmp_flag;
59
60 p_auto_cmp := x_auto_cmp;
61
62 -- if parameters not found then return error
63 if (not x_parms_found) then
64 fnd_message.set_name('WIP', 'WIP_DEFINE_WIP_PARAMETERS');
65 p_auto_cmp := -1;
66 p_ret_code := -1;
67 p_ret_msg := fnd_message.get;
68 return;
69 end if;
70
71 return;
72 end get_parms;
73
74 /* Fix for bug 4588479; FP 4496088: Re-wrote this procedure.
75 wdj.quantity_completed wouldn't have the correct picture for
76 LPN Completions, which would lead to wrong values returned by the
77 final_complete procedure. Get this quantity from MMT instead. */
78
79 procedure get_rem_qty(
80 p_org_id in number,
81 p_wip_id in number,
82 p_rem_qty out nocopy number) is
83
84 l_net_job_qty number;
85 l_mmt_cmp_qty number;
86
87 cursor get_net_job_qty (
88 c_org_id NUMBER,
89 c_wip_id NUMBER) is
90 select start_quantity - quantity_scrapped
91 from wip_discrete_jobs
92 where wip_entity_id = c_wip_id
93 and organization_id = c_org_id;
94
95 cursor get_mmt_cmp_qty (
96 c_org_id NUMBER,
97 c_wip_id NUMBER) is
98 select nvl(sum(primary_quantity),0)
99 from mtl_material_transactions
100 where transaction_source_type_id = 5
101 and transaction_source_id = c_wip_id
102 and organization_id = c_org_id
103 and transaction_action_id in (WIP_CONSTANTS.CPLASSY_ACTION,
104 WIP_CONSTANTS.RETASSY_ACTION);
105 begin
106
107 open get_net_job_qty(p_org_id, p_wip_id);
108 fetch get_net_job_qty into l_net_job_qty;
109 close get_net_job_qty;
110
111 open get_mmt_cmp_qty(p_org_id, p_wip_id);
112 fetch get_mmt_cmp_qty into l_mmt_cmp_qty;
113 close get_mmt_cmp_qty;
114
115 p_rem_qty := l_net_job_qty - l_mmt_cmp_qty;
116
117 end get_rem_qty;
118
119 procedure final_complete(
120 p_org_id in number,
121 p_wip_id in number,
122 p_pri_qty in number,
123 p_final_cmp in out nocopy varchar2,
124 p_ret_code out nocopy number,
125 p_ret_msg out nocopy varchar2) is
126
127 x_auto_cmp number;
128 x_cost_method number;
129 x_rem_qty number;
130 x_parms_found boolean;
131 x_ret_code number;
132
133 begin
134 -- initialize
135 p_ret_code := 0;
136 p_ret_msg := NULL;
137
138 -- get parameters
139 get_parms(
140 p_org_id => p_org_id,
141 p_pri_cost => x_cost_method,
142 p_auto_cmp => x_auto_cmp,
143 p_ret_code => x_ret_code,
144 p_ret_msg => p_ret_msg);
145
146 if (x_ret_code <> 0) then
147 p_ret_code := x_ret_code;
148 return;
149 end if;
150
151 -- if not auto completion then nothing to do
152 if (x_auto_cmp <> WIP_CONSTANTS.YES) then
153 return;
154 end if;
155
156 get_rem_qty(p_org_id, p_wip_id, x_rem_qty);
157
158 if (p_pri_qty >= x_rem_qty) then
159 p_final_cmp := 'Y';
160 end if;
161
162 return;
163
164 exception
165 when others then
166 p_ret_code := 1;
167 p_ret_msg := fnd_api.g_ret_sts_unexp_error;
168 return;
169 end final_complete;
170
171 procedure final_complete(
172 p_org_id in number,
173 p_wip_id in number,
174 p_mtl_hdr_id in number,
175 p_ret_code out nocopy number,
176 p_ret_msg out nocopy varchar2) is
177
178 cursor get_cmp_txns(c_mtl_hdr_id number) is
179 select mmtt.rowid,
180 mmtt.primary_quantity
181 from mtl_material_transactions_temp mmtt
182 where mmtt.transaction_header_id = c_mtl_hdr_id
183 and mmtt.transaction_action_id = WIP_CONSTANTS.CPLASSY_ACTION
184 order by mmtt.source_line_id;
185
186 x_auto_cmp number;
187 x_cost_method number;
188
189 x_cmp_txn_id number;
190 x_rowid varchar2(30);
191 x_pri_qty number;
192 x_rem_qty number;
193
194 x_done boolean := FALSE;
195 x_ret_code number;
196
197 begin
198 -- initialize
199 p_ret_code := 0;
200 p_ret_msg := NULL;
201
202 -- get parameters
203 get_parms(
204 p_org_id => p_org_id,
205 p_pri_cost => x_cost_method,
206 p_auto_cmp => x_auto_cmp,
207 p_ret_code => x_ret_code,
208 p_ret_msg => p_ret_msg);
209
210 if (x_ret_code <> 0) then
211 p_ret_code := x_ret_code;
212 return;
213 end if;
214
215 -- if not auto completion then nothing to do
216 if (x_auto_cmp <> WIP_CONSTANTS.YES) then
217 return;
218 end if;
219
220 get_rem_qty(p_org_id, p_wip_id, x_rem_qty);
221
222 -- get completion transactions given header id
223 open get_cmp_txns(p_mtl_hdr_id);
224
225 loop
226 fetch get_cmp_txns into x_rowid, x_pri_qty;
227 x_done := get_cmp_txns%NOTFOUND;
228 exit when x_done;
229
230 -- update completion flag
231 if (x_pri_qty >= x_rem_qty) then
232 update mtl_material_transactions_temp
233 set final_completion_flag = 'Y'
234 where rowid = x_rowid;
235 end if;
236
237 -- allocate quantity
238 x_rem_qty := x_rem_qty - x_pri_qty;
239 end loop;
240
241 close get_cmp_txns;
242
243 return;
244
245 exception
246 when others then
247 p_ret_code := 1;
248 p_ret_msg := fnd_api.g_ret_sts_unexp_error;
249 return;
250 end final_complete;
251
252 END WIP_WEIGHTED_AVG;