[Home] [Help]
PACKAGE BODY: APPS.WSM_JOBCOSTING_GRP
Source
1 PACKAGE BODY WSM_JobCosting_GRP as
2 /* $Header: WSMGCSTB.pls 115.2 2003/08/13 20:48:39 vjambhek ship $ */
3
4 /*-------------------------------------------------------------+
5 | Name : Insert_MaterialTxn
6 ---------------------------------------------------------------*/
7 --This procedure is called to insert record in MMT
8 --only for Bonus/Split/Merge/Update Quantity transactions
9
10 PROCEDURE Insert_MaterialTxn(p_txn_id IN NUMBER,
11 x_err_code OUT NOCOPY NUMBER,
12 x_err_buf OUT NOCOPY VARCHAR2
13 )
14 IS
15 l_stmt_num NUMBER;
16
17 l_wms_org VARCHAR2(5);
18 l_def_cost_grp_id NUMBER := 0;
19 l_acct_period_id NUMBER := 0;
20 l_org_id NUMBER;
21 l_txn_date DATE;
22
23 e_proc_error EXCEPTION;
24
25 BEGIN
26 x_err_code := 0;
27
28 l_stmt_num := 5;
29
30 SELECT organization_id,
31 transaction_date
32 INTO l_org_id,
33 l_txn_date
34 FROM wsm_split_merge_transactions
35 WHERE transaction_id = p_txn_id;
36
37 l_stmt_num := 10;
38
39 SELECT wms_enabled_flag,
40 default_cost_group_id
41 INTO l_wms_org,
42 l_def_cost_grp_id
43 FROM mtl_parameters
44 WHERE organization_id = l_org_id;
45
46 l_stmt_num := 20;
47
48 l_acct_period_id := -1;
49 BEGIN
50 SELECT acct_period_id
51 INTO l_acct_period_id
52 FROM org_acct_periods
53 WHERE organization_id = l_org_id
54 AND trunc(nvl(l_txn_date, sysdate))
55 between PERIOD_START_DATE and SCHEDULE_CLOSE_DATE
56 AND period_close_date is NULL
57 AND OPEN_FLAG = 'Y';
58
59 EXCEPTION
60 WHEN NO_DATA_FOUND then
61 x_err_code := -1;
62 fnd_message.set_name('WSM', 'WSM_ACCT_PERIOD_NOT_OPEN');
63 x_err_buf := FND_MESSAGE.GET;
64 fnd_file.put_line(fnd_file.log, 'WSM_JobCosting_GRP.Insert_MaterialTxn('||l_stmt_num||'): '||x_err_buf);
65
66 WHEN OTHERS THEN
67 x_err_code := SQLCODE;
68 x_err_buf := 'Insert_MaterialTxn('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
69 fnd_file.put_line(fnd_file.log, x_err_buf);
70 END;
71
72 IF (nvl(l_acct_period_id , -1) = -1) THEN
73 x_err_code := -1;
74 fnd_message.set_name('WSM', 'WSM_ACCT_PERIOD_NOT_OPEN');
75 x_err_buf := FND_MESSAGE.GET;
76 fnd_file.put_line(fnd_file.log, 'WSM_JobCosting_GRP.Insert_MaterialTxn('||l_stmt_num||'): '||x_err_buf);
77 END IF;
78
79 IF (x_err_code <> 0) THEN
80 GOTO L_ERROR; --x_err_code has errcode, x_err_buf has the error message
81 END IF;
82
83 l_stmt_num := 30;
84
85 INSERT INTO mtl_material_transactions
86 (TRANSACTION_ID,
87 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
88 CREATION_DATE, CREATED_BY, REQUEST_ID,
89 PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
90 INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSACTION_TYPE_ID,
91 TRANSACTION_ACTION_ID,
92 TRANSACTION_SOURCE_TYPE_ID,
93 TRANSACTION_SOURCE_ID,
94 TRANSACTION_SOURCE_NAME,
95 TRANSACTION_QUANTITY,
96 PRIMARY_QUANTITY,
97 TRANSACTION_UOM,
98 TRANSACTION_DATE, SOURCE_LINE_ID,
99 OPERATION_SEQ_NUM,
100 ACCT_PERIOD_ID, COSTED_FLAG,
101 COST_GROUP_ID
102 )
103 SELECT mtl_material_transactions_s.nextval,
104 sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID,
105 sysdate, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_REQUEST_ID,
106 FND_GLOBAL.PROG_APPL_ID, FND_GLOBAL.CONC_PROGRAM_ID, sysdate,
107 WSRJ.primary_item_id, WSMT.organization_id, MTT.transaction_type_id,
108 decode(WSMT.transaction_type_id, WSMPCNST.SPLIT, 40,
109 WSMPCNST.MERGE, 41,
110 WSMPCNST.BONUS, 42,
111 WSMPCNST.UPDATE_QUANTITY, 43, 0),
112 MTT.transaction_source_type_id,
113 decode(WSMT.transaction_type_id, WSMPCNST.SPLIT, WSSJ.wip_entity_id, WSRJ.wip_entity_id),
114 decode(WSMT.transaction_type_id, WSMPCNST.SPLIT, WSSJ.wip_entity_name, WSRJ.wip_entity_name),
115 decode(WSMT.transaction_type_id, WSMPCNST.SPLIT, WSSJ.available_quantity, WSRJ.start_quantity),
116 decode(WSMT.transaction_type_id, WSMPCNST.SPLIT, WSSJ.available_quantity, WSRJ.start_quantity),
117 MSI.primary_uom_code,
118 WSMT.transaction_date, WSMT.transaction_id,
119 decode(WSMT.transaction_type_id, WSMPCNST.BONUS, WSRJ.starting_operation_seq_num, WSSJ.operation_seq_num),
120 OAP.acct_period_id, 'N',
121 decode(l_wms_org, 'Y', l_def_cost_grp_id, NULL)
122 FROM wsm_sm_starting_jobs WSSJ,
123 wsm_split_merge_transactions WSMT,
124 wsm_sm_resulting_jobs WSRJ,
125 mtl_system_items MSI,
126 org_acct_periods OAP,
127 mtl_transaction_types MTT
128 WHERE WSMT.transaction_id = p_txn_id
129 AND WSMT.transaction_id = decode(WSMT.transaction_type_id, WSMPCNST.BONUS, WSMT.transaction_id,
130 WSSJ.transaction_id)
131 AND WSMT.transaction_id = WSRJ.transaction_id
132 AND WSRJ.primary_item_id = MSI.inventory_item_id
133 AND WSRJ.organization_id = MSI.organization_id
134 AND WSMT.organization_id = OAP.organization_id
135 AND trunc(WSMT.transaction_date) between period_start_date and schedule_close_date
136 AND MTT.transaction_action_id IN(decode(WSMT.transaction_type_id, WSMPCNST.SPLIT, 40,
137 WSMPCNST.MERGE, 41,
138 WSMPCNST.BONUS, 42,
139 WSMPCNST.UPDATE_QUANTITY, 43, 0))
140 AND MTT.transaction_source_type_id = 5
141 AND rownum = 1; --This picks up only 1 row for Split/Merge
142
143 fnd_file.put_line(fnd_file.log, 'Records inserted into MMT ='||SQL%ROWCOUNT);
144
145 IF (SQL%ROWCOUNT <> 1) THEN
146 x_err_code := -1;
147 fnd_message.set_name('WSM', 'WSM_INS_TBL_FAILED');
148 fnd_message.set_token('ELEMENT', 'mtl_material_transactions');
149 x_err_buf := FND_MESSAGE.GET;
150 fnd_file.put_line(fnd_file.log, 'WSM_JobCosting_GRP.Insert_MaterialTxn('||l_stmt_num||'): '||x_err_buf);
151 GOTO L_ERROR;
152 ELSE
153 GOTO L_SUCCESS;
154 END IF;
155
156 <<L_ERROR>> --x_err_code has errcode, x_err_buf has the error message
157 fnd_file.put_line(fnd_file.log, 'WSM_JobCosting_GRP.Insert_MaterialTxn: Rollback due to l_stmt_num = '||l_stmt_num);
158 raise e_proc_error;
159
160 <<L_SUCCESS>>
161 l_stmt_num := 40;
162 x_err_code := 0;
163 x_err_buf := NULL;
164 fnd_file.put_line(fnd_file.log, 'WSM_JobCosting_GRP.Insert_MaterialTxn: Returned success');
165
166 EXCEPTION
167 WHEN e_proc_error THEN
168 x_err_buf := ' WSM_JobCosting_GRP.Insert_MaterialTxn('||l_stmt_num||'): '||x_err_buf;
169 fnd_file.put_line(fnd_file.log,x_err_buf);
170
171 WHEN OTHERS THEN
172 x_err_code := SQLCODE;
173 x_err_buf := 'WSM_JobCosting_GRP.Insert_MaterialTxn('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
174 fnd_file.put_line(fnd_file.log, x_err_buf);
175
176 END Insert_MaterialTxn;
177
178
179 /*-------------------------------------------------------------+
180 | Name : Update_QtyIssued
181 ---------------------------------------------------------------*/
182 --This procedure is called only for Bonus/Split/Merge/Update Quantity transactions
183
184
185 PROCEDURE Update_QtyIssued(p_txn_id IN NUMBER,
186 p_txn_type IN NUMBER,
187 x_err_code OUT NOCOPY NUMBER,
188 x_err_buf OUT NOCOPY VARCHAR2
189 )
190 IS
191 l_stmt_num NUMBER;
192 l_op_seq_num NUMBER;
193 l_rep_we_id NUMBER;
194 l_avail_qty NUMBER;
195 l_result_qty NUMBER;
196
197 BEGIN
198
199 l_stmt_num := 3;
200
201 IF (p_txn_type = WSMPCNST.BONUS) THEN
202
203 l_stmt_num := 5;
204
205 SELECT wip_entity_id,
206 starting_operation_seq_num
207 INTO l_rep_we_id,
208 l_op_seq_num
209 FROM WSM_SM_RESULTING_JOBS
210 WHERE transaction_id = p_txn_id;
211
212 l_stmt_num := 7;
213
214 -- Update quantity_issued for all operations prior to starting_operation_seq_num
215 UPDATE wip_requirement_operations
216 SET quantity_issued = required_quantity
217 WHERE wip_entity_id = l_rep_we_id
218 AND operation_seq_num < l_op_seq_num
219 AND wip_supply_type not in (2, 4, 5, 6);
220
221 ELSE -- for Split/Merge/Update Quantity
222 l_stmt_num := 10;
223 SELECT wip_entity_id,
224 operation_seq_num,
225 available_quantity
226 INTO l_rep_we_id,
227 l_op_seq_num,
228 l_avail_qty
229 FROM WSM_SM_STARTING_JOBS
230 WHERE transaction_id = p_txn_id
231 AND representative_flag = 'Y';
232
233 IF p_txn_type IN (WSMPCNST.SPLIT, WSMPCNST.MERGE) THEN --This has been added to improve performance
234 -- as such the following stmts wont update anything for Upd Qty
235 l_stmt_num := 20;
236 -- Update the non-representative starting jobs
237 UPDATE wip_requirement_operations wro
238 SET wro.quantity_issued = round(NVL(wro.quantity_relieved, 0), 6)
239 WHERE wro.wip_entity_id in (select wip_entity_id
240 from wsm_sm_starting_jobs
241 where transaction_id = p_txn_id
242 and wip_entity_id <> l_rep_we_id)
243 AND nvl(wro.quantity_issued, 0) >= nvl(wro.quantity_relieved, 0)
244 -- If there is a PUSH comp and the whole qty is scrapped, qty_rel > qty_iss
245 AND not exists (select 'obsolete operation'
246 from wip_operations wo
247 where wo.wip_entity_id = wro.wip_entity_id
248 and wo.organization_id = wro.organization_id
249 and wo.operation_seq_num = wro.operation_seq_num
250 and wo.count_point_type = 3);
251
252 l_stmt_num := 30;
253 -- Update the non-matching resulting jobs i.e. new jobs
254 UPDATE wip_requirement_operations wro
255 SET wro.quantity_issued =
256 (SELECT round(decode(sign(nvl(wro1.quantity_issued, 0) - nvl(wro1.quantity_relieved, 0)), 1, 1, 0)
257 *(nvl(wro1.quantity_issued,0) - nvl(wro1.quantity_relieved, 0))
258 * WSRJ.start_quantity/l_avail_qty, 6)
259 FROM wip_requirement_operations wro1,
260 wsm_sm_resulting_jobs WSRJ
261 WHERE wro1.wip_entity_id = l_rep_we_id
262 AND wro1.inventory_item_id = wro.inventory_item_id
263 AND wro1.organization_id = wro.organization_id
264 AND wro1.operation_seq_num = wro.operation_seq_num
265 AND WSRJ.wip_entity_id = wro.wip_entity_id
266 AND WSRJ.transaction_id = p_txn_id) -- Fix for bug #3086120
267 WHERE wro.wip_entity_id in (select wip_entity_id
268 from wsm_sm_resulting_jobs
269 where transaction_id = p_txn_id
270 and wip_entity_id <> l_rep_we_id)
271 AND not exists (select 'obsolete operation'
272 from wip_operations wo
273 where wo.wip_entity_id = wro.wip_entity_id
274 and wo.organization_id = wro.organization_id
275 and wo.operation_seq_num = wro.operation_seq_num
279 l_stmt_num := 40;
276 and wo.count_point_type = 3);
277 END IF;
278
280 -- Update the representative job, may or may not be present in resulting
281 BEGIN
282 SELECT WSRJ.start_quantity
283 INTO l_result_qty
284 FROM wsm_sm_resulting_jobs WSRJ
285 WHERE WSRJ.transaction_id = p_txn_id
286 AND WSRJ.wip_entity_id in (select wip_entity_id
287 from wsm_sm_starting_jobs
288 where transaction_id = p_txn_id
289 and wip_entity_id = l_rep_we_id);
290 EXCEPTION
291 WHEN NO_DATA_FOUND THEN
292 l_result_qty := 0;
293 END;
294
295 UPDATE wip_requirement_operations wro
296 SET wro.quantity_issued =
297 round((decode(sign(nvl(wro.quantity_issued, 0) - nvl(wro.quantity_relieved, 0)), 1, 1, 0)
298 *(nvl(wro.quantity_issued,0) - nvl(wro.quantity_relieved, 0)) * l_result_qty/l_avail_qty
299 + nvl(wro.quantity_relieved, 0)), 6)
300 WHERE wro.wip_entity_id in (select wip_entity_id
301 from wsm_sm_starting_jobs
302 where transaction_id = p_txn_id
303 and wip_entity_id = l_rep_we_id)
304 AND wro.quantity_issued > NVL(wro.quantity_relieved, 0) -- Added to fix bug #2797647
305 AND not exists (select 'obsolete operation'
306 from wip_operations wo
307 where wo.wip_entity_id = wro.wip_entity_id
308 and wo.organization_id = wro.organization_id
309 and wo.operation_seq_num = wro.operation_seq_num
310 and wo.count_point_type = 3);
311
312 END IF; --ELSE -- for Split/Merge/Update Quantity
313
314 l_stmt_num := 50;
315 x_err_code := 0;
316 x_err_buf := NULL;
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 x_err_code := SQLCODE;
321 x_err_buf := 'WSM_JobCosting_GRP.Update_QtyIssued('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
322 fnd_file.put_line(fnd_file.log, x_err_buf);
323
324 END Update_QtyIssued;
325
326
327
328 END WSM_JobCosting_GRP;