1 PACKAGE WSMPLBMI AUTHID CURRENT_USER AS
2 /* $Header: WSMLBMIS.pls 120.5.12000000.1 2007/01/12 05:36:04 appldev ship $ */
3
4
5 l_debug VARCHAR2(1) := FND_PROFILE.VALUE('MRP_DEBUG');
6
7 --***VJ Added for Performance Upgrade***--
8 --bug 3347485
9 /*
10 g_prev_org_id NUMBER := 0;
11 g_prev_org_code VARCHAR2(3);
12 g_prev_cr_user_id NUMBER := 0;
13 g_prev_cr_user_name VARCHAR2(100);
14 g_prev_upd_user_id NUMBER := 0;
15 g_prev_upd_user_name VARCHAR2(100);
16 g_prev_last_op NUMBER := 0;
17 g_prev_op_seq_incr NUMBER := 0;
18 g_acct_period_id NUMBER := 0;
19 */
20
21 g_prev_org_id NUMBER := -9999;
22 g_prev_org_code VARCHAR2(3);
23 g_prev_cr_user_id NUMBER := -9999;
24 g_prev_cr_user_name VARCHAR2(100);
25 g_prev_upd_user_id NUMBER := -9999;
26 g_prev_upd_user_name VARCHAR2(100);
27 g_prev_last_op NUMBER := -9999;
28 g_prev_op_seq_incr NUMBER := -9999;
29 g_acct_period_id NUMBER := -9999;
30 --end bug 3347485
31
32 g_prev_txn_date DATE;
33 g_allow_bkw_move NUMBER :=0;
34 g_param_jump_fm_q NUMBER :=1;
35 g_miss_num CONSTANT NUMBER := FND_API.G_MISS_NUM;
36 g_miss_char CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
37 g_miss_date CONSTANT DATE := FND_API.G_MISS_DATE;
38
39 --***VJ End Additions***--
40
41 --move enh
42 --move enh? change the code below
43 g_aps_wps_profile VARCHAR2(1);
44 g_mrp_debug VARCHAR2(1) := FND_PROFILE.VALUE('MRP_DEBUG');
45 g_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
46 -- g_program_update_date DATE := sysdate;
47 g_program_application_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
48 g_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
49 g_user_id NUMBER := fnd_global.USER_ID;
50 g_resp_id NUMBER := fnd_global.RESP_ID;
51 g_resp_appl_id NUMBER := fnd_global.RESP_APPL_ID;
52 g_login_id NUMBER := FND_GLOBAL.login_id;
53 g_del_move_txns NUMBER := FND_PROFILE.VALUE('WSM_DEL_PROCESSED_MOVE_TXNS');
54 g_fnd_generic_err_msg VARCHAR2(4000);
55
56
57 --WIP_CONSTANTS
58 g_error CONSTANT NUMBER := WIP_CONSTANTS.ERROR;
59
60 g_move_proc CONSTANT NUMBER := WIP_CONSTANTS.MOVE_PROC;
61 g_move_val CONSTANT NUMBER := WIP_CONSTANTS.MOVE_VAL;
62
63 g_running CONSTANT NUMBER := WIP_CONSTANTS.RUNNING;
64 g_pending CONSTANT NUMBER := WIP_CONSTANTS.PENDING;
65
66 g_queue CONSTANT NUMBER := WIP_CONSTANTS.QUEUE;
67 g_run CONSTANT NUMBER := WIP_CONSTANTS.RUN;
68 g_tomove CONSTANT NUMBER := WIP_CONSTANTS.TOMOVE;
69 g_scrap CONSTANT NUMBER := WIP_CONSTANTS.SCRAP;
70
71 g_no_manual CONSTANT NUMBER := WIP_CONSTANTS.NO_MANUAL;
72
73 g_move_txn CONSTANT NUMBER := WIP_CONSTANTS.MOVE_TXN;
74 g_comp_txn CONSTANT NUMBER := WIP_CONSTANTS.COMP_TXN;
75 g_ret_txn CONSTANT NUMBER := WIP_CONSTANTS.RET_TXN;
76 g_undo_txn CONSTANT NUMBER := 4;
77 --WIP_CONSTANTS END
78
79
80 --move enh end
81
82
83
84 /*-------------------------------------------------------------+
85 | CUSTOM_VALIDATION: |
86 ---------------------------------------------------------------*/
87 FUNCTION custom_validation( p_header_id IN NUMBER,
88 p_txn_id IN NUMBER,
89 p_txn_qty IN NUMBER,
90 p_txn_date IN DATE,
91 p_txn_uom IN VARCHAR2,
92 p_primary_uom IN VARCHAR2,
93 p_txn_type IN NUMBER,
94 p_fm_op_seq_num IN OUT NOCOPY NUMBER,
95 p_fm_op_code IN VARCHAR2,
96 p_fm_intraop_step_type IN NUMBER,
97 p_to_op_seq_num IN NUMBER,
98 p_to_op_code IN VARCHAR2,
99 p_to_intraop_step_type IN NUMBER,
100 p_to_dept_id IN NUMBER,
101 p_wip_entity_name IN VARCHAR2,
102 p_org_id IN NUMBER,
103 p_jump_flag IN VARCHAR2,
104 -- ST : Serial Support Project --
105 x_serial_ctrl_code OUT NOCOPY NUMBER,
106 x_available_qty OUT NOCOPY NUMBER,
107 x_current_job_op_seq_num OUT NOCOPY NUMBER,
108 x_current_intraop_step OUT NOCOPY NUMBER,
109 x_current_rtg_op_seq_num OUT NOCOPY NUMBER,
110 x_old_scrap_transaction_id OUT NOCOPY NUMBER,
111 x_old_move_transaction_id OUT NOCOPY NUMBER,
112 -- ST : Serial Support Project --
113 x_err_buf OUT NOCOPY VARCHAR2,
114 x_undo_source_code OUT NOCOPY VARCHAR2
115 ) RETURN NUMBER;
116
117
118 /*-------------------------------------------------------------+
119 | validate_lot_txn_for_bk_move: |
120 ---------------------------------------------------------------*/
121 FUNCTION validate_lot_txn_for_bk_move( p_org_id IN NUMBER,
122 p_wip_entity_id IN NUMBER,
123 p_txn_qty IN NUMBER,
124 p_txn_type IN NUMBER,
125 p_from_op_seq_num IN NUMBER,
126 p_from_op_code IN VARCHAR2,
127 p_from_intraop_step_type IN NUMBER,
128 p_to_op_seq_num IN NUMBER,
129 p_to_op_code IN VARCHAR2,
130 p_to_intraop_step_type IN NUMBER,
131 p_scrap_acct_id IN NUMBER,
132 x_err_buf OUT NOCOPY VARCHAR2
133 ) RETURN NUMBER;
134
135
136 -- BA: CZH.JUMPENH
137 /*-------------------------------------------------------------+
138 | set_undo_txn_id() |
139 ---------------------------------------------------------------*/
140 FUNCTION set_undo_txn_id( p_org_id IN NUMBER,
141 p_wip_entity_id IN NUMBER,
142 p_undo_txn_id IN NUMBER,
143 x_err_buf OUT NOCOPY VARCHAR2
144 ) RETURN NUMBER;
145
146 -- EA: CZH.JUMPENH
147 FUNCTION set_undo_txn_id( p_org_id IN NUMBER,
148 p_wip_entity_id IN NUMBER,
149 p_undo_txn_id IN NUMBER,
150 p_to_op_seq_num IN NUMBER,
151 p_undo_jump_fromq IN BOOLEAN,
152 x_err_buf OUT NOCOPY VARCHAR2
153 ) RETURN NUMBER;
154 /*
155 Commented this procedure to make it a private procedure
156
157 Procedure val_jump_from_queue(p_wip_entity_id IN NUMBER,
158 p_org_id IN NUMBER,
159 p_fm_op_seq_num IN NUMBER,
160 p_wo_op_seq_id IN NUMBER,
161 -- Removed -- p_wo_qty_in_scrap IN NUMBER,
162 x_return_code OUT NOCOPY NUMBER,
163 x_err_buf OUT NOCOPY VARCHAR2);
164 */
165
166 --move enh
167 Procedure MoveTransaction(retcode OUT NOCOPY NUMBER,
168 errbuf OUT NOCOPY VARCHAR2,
169 p_group_id IN NUMBER);
170 --move enh end
171
172 --MES skaradib
173 /*
174 TYPE t_jobop_secondary_qty_rec is RECORD(
175 uom_code WSM_OP_SECONDARY_QUANTITIES.uom_code%TYPE,
176 move_in_quantity WSM_OP_SECONDARY_QUANTITIES.move_in_quantity%TYPE,
177 move_out_quantity WSM_OP_SECONDARY_QUANTITIES.move_out_quantity%TYPE
178 );
179 */
180
181 --TYPE t_jobop_secondary_qty_tbl_type is table of t_jobop_secondary_qty_rec index by binary_integer;
182 TYPE t_sec_uom_code_tbl_type is table of wsm_op_secondary_quantities.UOM_CODE%TYPE index by binary_integer;
183 TYPE t_sec_move_out_qty_tbl_type is table of wsm_op_secondary_quantities.MOVE_OUT_QUANTITY%TYPE index by binary_integer;
184
185 --TYPE t_jobop_sec_qty_tbls_type is table of t_jobop_secondary_qty_tbl_type index by binary_integer;
186 TYPE t_sec_uom_code_tbls_type is table of t_sec_uom_code_tbl_type index by binary_integer;
187 TYPE t_sec_move_out_qty_tbls_type is table of t_sec_move_out_qty_tbl_type index by binary_integer;
188
189 /*
190 Type t_jobop_reason_codes_rec is RECORD(
191 reason_code WSM_OP_REASON_CODES.reason_code%TYPE,
192 quantity WSM_OP_REASON_CODES.quantity%TYPE
193 );
194
195 TYPE t_jobop_scrap_codes_tbl_type is table of t_jobop_reason_codes_rec index by binary_integer;
196 TYPE t_jobop_scrap_codes_tbls_type is table of t_jobop_scrap_codes_tbl_type index by binary_integer;
197 */
198
199 --TYPE t_jobop_scrap_codes_tbl_type is table of wsm_op_reason_codes%rowtype index by binary_integer;
200 TYPE t_scrap_codes_tbl_type is table of wsm_op_reason_codes.REASON_CODE%type index by binary_integer;
201 TYPE t_scrap_code_qty_tbl_type is table of wsm_op_reason_codes.QUANTITY%type index by binary_integer;
202
203 TYPE t_scrap_codes_tbls_type is table of t_scrap_codes_tbl_type index by binary_integer;
204 TYPE t_scrap_code_qty_tbls_type is table of t_scrap_code_qty_tbl_type index by binary_integer;
205
206 /*
207 TYPE t_jobop_bonus_codes_tbl_type is table of t_jobop_reason_codes_rec index by binary_integer;
208 TYPE t_jobop_bonus_codes_tbls_type is table of t_jobop_bonus_codes_tbl_type index by binary_integer;
209 */
210 TYPE t_bonus_codes_tbl_type is table of wsm_op_reason_codes.REASON_CODE%type index by binary_integer;
211 TYPE t_bonus_code_qty_tbl_type is table of wsm_op_reason_codes.QUANTITY%type index by binary_integer;
212
213 --TYPE t_jobop_bonus_codes_tbls_type is table of t_jobop_bonus_codes_tbl_type index by binary_integer;
214 TYPE t_bonus_codes_tbls_type is table of t_bonus_codes_tbl_type index by binary_integer;
215 TYPE t_bonus_code_qty_tbls_type is table of t_bonus_code_qty_tbl_type index by binary_integer;
216
217 /*
218 Type t_jobop_instances_rec is RECORD(
219 RESOURCE_SEQ_NUM WIP_RESOURCE_ACTUAL_TIMES.RESOURCE_SEQ_NUM%TYPE,
220 INSTANCE_ID WIP_RESOURCE_ACTUAL_TIMES.INSTANCE_ID%TYPE,
221 SERIAL_NUMBER WIP_RESOURCE_ACTUAL_TIMES.SERIAL_NUMBER%TYPE,
222 EMPLOYEE_ID WIP_RESOURCE_ACTUAL_TIMES.EMPLOYEE_ID%TYPE
223 );
224
225 TYPE t_jobop_instances_tbl_type is table of t_jobop_instances_rec index by binary_integer;
226 TYPE t_jobop_instances_tbls_type is table of t_jobop_instances_tbl_type index by binary_integer;
227 */
228
229 TYPE t_jobop_res_usages_tbl_type is table of WIP_RESOURCE_ACTUAL_TIMES%ROWTYPE index by binary_integer;
230 --TYPE t_jobop_res_usages_tbl_type is table of t_jobop_res_usages_rec index by binary_integer;
231 TYPE t_jobop_res_usages_tbls_type is table of t_jobop_res_usages_tbl_type index by binary_integer;
232
233 --TYPE WSM_SERIAL_NUM_TBL is table of WSM_Serial_support_GRP.WSM_SERIAL_NUM_REC index by binary_integer;
234 --TYPE WSM_SERIAL_NUM_TBL is table of WSM_SERIAL_TXN_INTERFACE%ROWTYPE index by binary_integer;
235
236 TYPE t_scrap_serials_tbls_type is table of WSM_Serial_support_GRP.WSM_SERIAL_NUM_TBL index by binary_integer;
237 TYPE t_bonus_serials_tbls_type is table of WSM_Serial_support_GRP.WSM_SERIAL_NUM_TBL index by binary_integer;
238
239 Procedure MoveTransaction(
240 p_group_id IN NUMBER,
241 p_transaction_id IN NUMBER,
242 p_source_code IN VARCHAR2,
243 p_TRANSACTION_TYPE IN NUMBER,
244 p_ORGANIZATION_ID IN NUMBER,
245 p_WIP_ENTITY_ID IN NUMBER,
246 p_WIP_ENTITY_NAME IN VARCHAR2,
247 p_primary_item_id IN NUMBER,
248 p_TRANSACTION_DATE IN DATE,
249 p_FM_OPERATION_SEQ_NUM IN NUMBER,
250 p_FM_OPERATION_CODE IN VARCHAR2,
251 p_FM_DEPARTMENT_ID IN NUMBER,
252 p_FM_DEPARTMENT_CODE IN VARCHAR2,
253 p_FM_INTRAOPERATION_STEP_TYPE IN NUMBER,
254 p_TO_OPERATION_SEQ_NUM IN NUMBER,
255 p_TO_OPERATION_CODE IN VARCHAR2,
256 p_TO_DEPARTMENT_ID IN NUMBER,
257 p_TO_DEPARTMENT_CODE IN VARCHAR2,
258 p_TO_INTRAOPERATION_STEP_TYPE IN NUMBER,
259 p_PRIMARY_QUANTITY IN NUMBER,
260 p_low_yield_trigger_limit IN NUMBER,
261 p_primary_uom IN VARCHAR2,
262 p_SCRAP_ACCOUNT_ID IN NUMBER,
263 p_REASON_ID IN NUMBER,
264 p_REASON_NAME IN VARCHAR2,
265 p_REFERENCE IN VARCHAR2,
266 p_QA_COLLECTION_ID IN NUMBER,
267 p_JUMP_FLAG IN VARCHAR2,
268 p_HEADER_ID IN NUMBER,
269 p_PRIMARY_SCRAP_QUANTITY IN NUMBER,
270 p_bonus_quantity IN NUMBER,
271 p_SCRAP_AT_OPERATION_FLAG IN NUMBER,
272 p_bonus_account_id IN NUMBER,
273 p_employee_id IN NUMBER,
274 p_operation_start_date IN DATE,
275 p_operation_completion_date IN DATE,
276 p_expected_completion_date IN DATE,
277 p_mtl_txn_hdr_id IN NUMBER,
278 p_sec_uom_code_tbl IN t_sec_uom_code_tbl_type,
279 p_sec_move_out_qty_tbl IN t_sec_move_out_qty_tbl_type,
280 p_jobop_scrap_serials_tbl IN WSM_Serial_support_GRP.WSM_SERIAL_NUM_TBL,
281 p_jobop_bonus_serials_tbl IN WSM_Serial_support_GRP.WSM_SERIAL_NUM_TBL,
282 p_scrap_codes_tbl IN t_scrap_codes_tbl_type,
283 p_scrap_code_qty_tbl IN t_scrap_code_qty_tbl_type,
284 p_bonus_codes_tbl IN t_bonus_codes_tbl_type,
285 p_bonus_code_qty_tbl IN t_bonus_code_qty_tbl_type,
286 p_jobop_resource_usages_tbl IN t_jobop_res_usages_tbl_type,
287 x_wip_move_api_sucess_msg OUT NOCOPY VARCHAR2
288 , x_return_status OUT NOCOPY VARCHAR2
289 , x_msg_count OUT NOCOPY NUMBER
290 , x_msg_data OUT NOCOPY VARCHAR2
291 );
292
293 Procedure MoveTransaction(
294 p_group_id IN NUMBER,
295 p_bonus_account_id IN NUMBER,
296 p_employee_id IN NUMBER,
297 p_operation_start_date IN DATE,
298 p_operation_completion_date IN DATE,
299 p_expected_completion_date IN DATE,
300 p_bonus_quantity IN NUMBER,
301 p_low_yield_trigger_limit IN NUMBER,
302 p_source_code IN wsm_lot_move_txn_interface.source_code%type,
303 p_mtl_txn_hdr_id IN NUMBER,
304 p_sec_uom_code_tbls IN t_sec_uom_code_tbls_type,
305 p_sec_move_out_qty_tbls IN t_sec_move_out_qty_tbls_type,
306 p_jobop_scrap_serials_tbls IN t_scrap_serials_tbls_type,
307 p_jobop_bonus_serials_tbls IN t_bonus_serials_tbls_type,
308 p_scrap_codes_tbls IN t_scrap_codes_tbls_type,
309 p_scrap_code_qty_tbls IN t_scrap_code_qty_tbls_type,
310 p_bonus_codes_tbls IN t_bonus_codes_tbls_type,
311 p_bonus_code_qty_tbls IN t_bonus_code_qty_tbls_type,
312 -- p_jobop_instances_tbls IN t_jobop_instances_tbls_type,
313 p_jobop_resource_usages_tbls IN t_jobop_res_usages_tbls_type,
314 x_wip_move_api_sucess_msg OUT NOCOPY VARCHAR2,
315 retcode OUT NOCOPY NUMBER,
316 errbuf OUT NOCOPY VARCHAR2
317 );
318
319 --MES skaradib end
320
321 Procedure getMoveOutPageProperties(
322 p_organization_id IN NUMBER
323 , p_wip_entity_id IN NUMBER
324 , p_operation_seq_num IN NUMBER
325 , p_routing_operation IN NUMBER
326 , p_job_type IN NUMBER
327 , p_current_step IN NUMBER
328 , p_user_id IN NUMBER
329 , x_last_operation OUT NOCOPY NUMBER
330 , x_estimated_scrap_accounting OUT NOCOPY NUMBER
331 , x_show_next_op_by_default OUT NOCOPY NUMBER
332 , x_multiple_res_usage_dates OUT NOCOPY NUMBER
333 , x_show_scrap_codes OUT NOCOPY NUMBER
334 , x_scrap_codes_defined OUT NOCOPY NUMBER
335 , x_bonus_codes_defined OUT NOCOPY NUMBER
336 , x_show_lot_attrib OUT NOCOPY NUMBER
337 , x_show_scrap_serials OUT NOCOPY NUMBER
338 , x_show_serial_region OUT NOCOPY NUMBER
339 , x_show_secondary_quantities OUT NOCOPY NUMBER
340 , x_transaction_type OUT NOCOPY NUMBER
341 , x_quality_region OUT NOCOPY VARCHAR2
342 , x_show_scrap_qty OUT NOCOPY NUMBER
343 , x_show_next_op_choice OUT NOCOPY NUMBER
344 , x_show_next_op OUT NOCOPY NUMBER
345 , x_employee_id OUT NOCOPY NUMBER
346 , x_operator OUT NOCOPY VARCHAR2
347 , x_default_start_date OUT NOCOPY DATE
348 , x_default_completion_date OUT NOCOPY DATE
349 , x_return_status OUT NOCOPY VARCHAR2
350 , x_msg_count OUT NOCOPY NUMBER
351 , x_msg_data OUT NOCOPY VARCHAR2
352 );
353
354 Procedure getJobOpPageProperties(
355 p_organization_id IN NUMBER
356 , p_wip_entity_id IN NUMBER
357 , p_operation_seq_num IN NUMBER
358 , p_routing_operation IN NUMBER
359 , p_responsibility_id IN NUMBER
360 , p_standard_op_id IN NUMBER
361 , p_current_step_type IN NUMBER
362 , p_status_type IN NUMBER
363 , x_show_move_in OUT NOCOPY NUMBER
364 , x_show_move_out OUT NOCOPY NUMBER
365 , x_show_move_to_next_op OUT NOCOPY NUMBER
366 , x_show_serial_region OUT NOCOPY NUMBER
367 , x_show_scrap_codes OUT NOCOPY NUMBER
368 , x_show_bonus_codes OUT NOCOPY NUMBER
369 , x_show_secondary_quantities OUT NOCOPY NUMBER
370 , x_show_lot_attrib OUT NOCOPY NUMBER
371 , x_return_status OUT NOCOPY VARCHAR2
372 , x_msg_count OUT NOCOPY NUMBER
373 , x_msg_data OUT NOCOPY VARCHAR2
374 );
375
376 Procedure update_costed_qty_compl(
377 p_transaction_type NUMBER
378 , p_job_fm_op_seq_num NUMBER
379 , p_job_to_op_seq_num NUMBER
380 , p_wip_entity_id NUMBER
381 , p_fm_intraoperation_step_type NUMBER
382 , p_to_intraoperation_step_type NUMBER
383 , p_primary_move_qty NUMBER
384 , p_primary_scrap_qty NUMBER
385 , p_scrap_at_op NUMBER
386 );
387
388 Function convert_uom(
389 p_time_hours IN NUMBER, -- from_quantity
390 p_to_uom IN VARCHAR2-- to_unit
391 ) RETURN NUMBER;
392 --pragma restrict_references(convert_uom, WNDS,WNPS, RNPS);
393
394 END WSMPLBMI;
[Home] [Help]