1 PACKAGE wip_movProc_priv AUTHID CURRENT_USER AS
2 /* $Header: wipmovps.pls 120.8 2011/10/27 18:36:49 pding ship $*/
3 -- Version Initial version 1.0 Kaweesak Boonyapornnad
4
5 -- this record used to store shedule ID and Qty for Repetitive Schedule
6 -- Allocation
7 TYPE rsa_rec_t IS RECORD(scheID NUMBER,
8 scheQty NUMBER);
9 TYPE rsa_tbl_t IS TABLE OF rsa_rec_t INDEX BY binary_integer;
10
11 /***************************************************************************
12 *
13 * This procedure is a new Move Worker. This procedure is equivalent to
14 * wiltws.ppc. This procedure will be used to create an executable file that
15 * will be called from Move Manager.
16 *
17 * PARAMETER:
18 *
19 * errbuf error messages
20 * retcode return status. 0 for success, 1 for warning and
21 * 2 for error.
22 * p_group_id group_id in WIP_MOVE_TXN_INTERFACE
23 * p_proc_phase process phase that want to be processed. It can be
24 * either WIP_CONSTANTS.MOVE_VAL,
25 * WIP_CONSTANTS.MOVE_PROC or WIP_CONSTANTS.BF_SETUP
26 * p_time_out time out only use for BACKGROUND transactions. Pass
27 * 0 for online transaction.
28 * p_seq_move this parameter will be used to determine whether
29 * we need to do sequencing move or not. Pass either
30 * WIP_CONSTANTS.YES(1) or WIP_CONSTANTS.NO(2)
31 * NOTE:
32 * This procedure should be called only from the Move Manager
33 *
34 ***************************************************************************/
35
36 PROCEDURE move_worker(errbuf OUT NOCOPY VARCHAR2,
37 retcode OUT NOCOPY NUMBER,
38 p_group_id IN NUMBER,
39 p_proc_phase IN NUMBER,
40 p_time_out IN NUMBER,
41 p_seq_move IN NUMBER);
42
43 /***************************************************************************
44 *
45 * This procedure will be used to do move, easy-return, easy-completion, and
46 * scrap transaction for Discrete and OSFM jobs. The user need to insert
47 * the record into WIP_MOVE_TXN_INTERFACE before call this routine
48 *
49 * This routine can be called directly for ON-LINE transaction, or can be call
50 * from Move Worker for background case. For background case we support batch
51 * processing, but we will not support batch processing for online case.
52 *
53 * PARAMETER:
54 *
55 * p_group_id group_id in WIP_MOVE_TXN_INTERFACE
56 * p_proc_phase process phase that want to be processed. It can be
57 * either WIP_CONSTANTS.MOVE_VAL,
58 * WIP_CONSTANTS.MOVE_PROC or WIP_CONSTANTS.BF_SETUP
59 * p_time_out time out only use for BACKGROUND transactions. Pass
60 * 0 for online transaction.
61 * p_move_mode Move processing mode. Can be either
62 * WIP_CONSTANTS.BACKGROUND or WIP_CONSTANTS.ONLINE
63 * p_bf_mode Backflush processing mode. Can be either
64 * WIP_CONSTANTS.BACKGROUND or WIP_CONSTANTS.ONLINE
65 * p_mtl_mode Material processing mode. Can be either
66 * WIP_CONSTANTS.BACKGROUND or WIP_CONSTANTS.ONLINE or
67 * WIP_CONSTANTS.IMMED_CONC
68 * p_endDebug If it is called from Move form or Move mobile, pass
69 * fnd_api.g_true. If it is called from other forms such
70 * as Completion form, pass fnd_api.g_false because
71 * the caller may want to keep the log file in the same
72 * session.
73 * p_initMsgList A flag used to determine whether the user want to
74 * initialize message list or not. It can be eiher
75 * 'T' for 'True' or 'F' for 'False'
76 * p_insertAssy This flag is only used for Easy Complete/Return txns
77 * It is used to determine wheter the caller want this
78 * API to insert assy record into MMTT and MTLT or not.
79 * If the caller want to be the one who insert the
80 * record in MMTT, MTLT, and MSNT, this flag need to be
81 * 'F'. Otherwise pass 'T'.
82 * p_do_backflush This flag will be used to determine whether the
83 * move processor should insert backflush record or not.
84 * If called from form/mobile, form/mobile will be the
85 * one who insert backflush records, so pass 'F'.
86 * If called from move manager pass 'T'.
87 * p_child_txn_id transaction_id of the child record. This is the id
88 * we passed to backflush processor to collect
89 * lot/serial info for Mobile Application. Only use
90 * for Online Over Move transaction from mobile.
91 * p_assy_header_id (Assembly)transaction_header_id in
92 * MTL_MATERIAL_TRANSACTIONS_TEMP. Caller can
93 * generate this value from mtl_material_transactions_s.
94 * p_mtl_header_id (Components)transaction_header_id in
95 * MTL_MATERIAL_TRANSACTIONS_TEMP. It is used when
96 * called by completion processor for overcompletion
97 * transaction and if called from WIP Move,
98 * WIP Completion and OSFM Move forms. Caller can
99 * generate this value from mtl_material_transactions_s.
100 * p_cmp_txn_id MMTT.COMPLETION_TRANSACTION_ID. If caller pass this
101 * value, we will insert assembly completion/return
102 * records with this value. Othewise, we will generate
103 * from mtl_material_transaction_s. This parameter
104 * is useful if called from WIP/OSFM Move form.
105 * p_seq_move this parameter will be used to determine whether
106 * we need to do sequencing move or not. Pass either
107 * WIP_CONSTANTS.YES(1) or WIP_CONSTANTS.NO(2). If null
108 * , we will default to WIP_CONSTANTS.NO.
109 * x_returnStatus There are 3 possible values
110 * *fnd_api.g_ret_sts_success*
111 * means the move transaction succesfully
112 * processed
113 * *fnd_api.g_ret_sts_error*
114 * means backflush transaction need more info about
115 * lot/serial
116 * *fnd_api.g_ret_sts_unexp_error*
117 * means an exception occurred
118 *
119 * NOTE:
120 * The user don't need to insert child record for online over move/ over
121 * completion. This API will take care everything. The user also don't need
122 * to call QA API for online transaction either.
123 ***************************************************************************/
124
125 PROCEDURE processIntf(p_group_id IN NUMBER,
126 p_proc_phase IN NUMBER,
127 p_time_out IN NUMBER,
128 p_move_mode IN NUMBER,
129 p_bf_mode IN NUMBER,
130 p_mtl_mode IN NUMBER,
131 p_endDebug IN VARCHAR2,
132 p_initMsgList IN VARCHAR2,
133 p_insertAssy IN VARCHAR2,
134 p_do_backflush IN VARCHAR2,
135 p_child_txn_id IN NUMBER := NULL,
136 p_assy_header_id IN NUMBER := NULL,
137 p_mtl_header_id IN NUMBER := NULL,
138 p_cmp_txn_id IN NUMBER := NULL,
139 p_seq_move IN NUMBER := NULL,
140 -- Fixed bug 4361566.
141 p_allow_partial_commit IN NUMBER := NULL,
142 x_returnStatus OUT NOCOPY VARCHAR2);
143
144 /***************************************************************************
145 *
146 * This procedure will be called from WIP OA Transaction page to do move,
147 * easy-return, easy-completion, and scrap transaction for Discrete jobs.
148 * The user need to insert the record into WIP_MOVE_TXN_INTERFACE before
149 * calling this routine.
150 *
151 * PARAMETER:
152 *
153 * p_group_id group_id in WIP_MOVE_TXN_INTERFACE
154 * p_child_txn_id transaction_id of the child record. This is the id
155 * we passed to backflush processor to collect
156 * lot/serial info for Mobile Application. Only use
157 * for Online Over Move transaction from mobile.
158 * p_mtl_header_id (Components)transaction_header_id in
159 * MTL_MATERIAL_TRANSACTIONS_TEMP. It is used when
160 * called by completion processor for overcompletion
161 * transaction and if called from WIP Move,
162 * WIP Completion and OSFM Move forms. Caller can
163 * generate this value from mtl_material_transactions_s
164 * p_do_backflush This flag will be used to determine whether the
165 * move processor should insert backflush record or not.
166 * If called from form/mobile, form/mobile will be the
167 * one who insert backflush records, so pass 'F'.
168 * If called from move manager pass 'T'.
169 * p_assySerial Assembly serial number. This parameter will be used
170 * to differentiate between regular and serialized
171 * transactions.
172 * p_print_label Print Label flag. This parameter will be used to pass
173 * the value of the Administrator preference 'Standard
174 * Operations for Move Labels' for the current transaction.
175 * x_return_status There are 2 possible values
176 * *fnd_api.g_ret_sts_success*
177 * means the every record was succesfully processed
178 * *fnd_api.g_ret_sts_error*
179 * means some records error out
180 *
181 * NOTE:
182 * The user don't need to insert child record for online over move/ over
183 * completion. This API will take care everything. The user also don't need
184 * to call QA API for online transaction either.
185 ***************************************************************************/
186
187 PROCEDURE processOATxn(p_group_id IN NUMBER,
188 p_child_txn_id IN NUMBER,
189 p_mtl_header_id IN NUMBER,
190 p_do_backflush IN VARCHAR2,
191 p_assySerial IN VARCHAR2:= NULL,
192 p_print_label IN NUMBER default null,/*VJ Label Printing*/
193 p_print_cmpl_label IN NUMBER default null, /*Bug 13103060 print completion label for ez completion*/
194 x_returnStatus OUT NOCOPY VARCHAR2);
195
196 /***************************************************************************
197 *
198 * This procedure will be used to insert record into MMTA for repetitive
199 * scrap.
200 *
201 * PARAMETER:
202 *
203 * p_tmp_id MMTT.TRANSACTION_TEMP_ID
204 * x_returnStatus There are 2 possible values
205 * *fnd_api.g_ret_sts_success*
206 * means the move transaction succesfully
207 * processed
208 * *fnd_api.g_ret_sts_unexp_error*
209 * means an exception occurred
210 *
211 * NOTE:
212 * This procedure should be called only from WIP material processor
213 *
214 ***************************************************************************/
215 PROCEDURE repetitive_scrap(p_tmp_id IN NUMBER,
216 x_returnStatus OUT NOCOPY VARCHAR2);
217
218 /*****************************************************************************
219 * This procedure is equivalent to witpssa_sched_alloc in wiltps.ppc
220 * This procedure is used to allocate quantity to multiple reptitive schedule
221 * it works for move, completion/return and over move/complete transactions
222 ****************************************************************************/
223 /* Fix for bug 5373061: Added parameter p_txn_date. We need it to
224 compare it with WRS.date_released */
225
226 PROCEDURE schedule_alloc(p_org_id IN NUMBER,
227 p_wip_id IN NUMBER,
228 p_line_id IN NUMBER,
229 p_quantity IN NUMBER,
230 p_fm_op IN NUMBER,
231 p_fm_step IN NUMBER,
232 p_to_op IN NUMBER,
233 p_to_step IN NUMBER,
234 p_oc_txn_type IN NUMBER,
235 p_txnType IN NUMBER,
236 p_fm_form IN NUMBER,
237 p_comp_alloc IN NUMBER,
238 p_txn_date IN DATE, /* Bug 5373061 */
239 x_proc_status OUT NOCOPY NUMBER,
240 x_sche_count OUT NOCOPY NUMBER,
241 x_rsa OUT NOCOPY rsa_tbl_t,
242 x_returnStatus OUT NOCOPY VARCHAR2);
243
244 /*****************************************************************************
245 * This procedure will be used by WIP OA Transaction page to insert record
246 * into WIP_MOVE_TXN_INTERFACE.
247 ****************************************************************************/
248 PROCEDURE insert_record(p_transaction_id IN NUMBER,
249 p_last_update_date IN DATE,
250 p_last_updated_by IN NUMBER,
251 p_last_updated_by_name IN VARCHAR2,
252 p_creation_date IN DATE,
253 p_created_by IN NUMBER,
254 p_created_by_name IN VARCHAR2,
255 p_last_update_login IN NUMBER,
256 p_request_id IN NUMBER,
257 p_program_application_id IN NUMBER,
258 p_program_id IN NUMBER,
259 p_program_update_date IN DATE,
260 p_group_id IN NUMBER,
261 p_source_code IN VARCHAR2,
262 p_source_line_id IN NUMBER,
263 p_process_phase IN NUMBER,
264 p_process_status IN NUMBER,
265 p_transaction_type IN NUMBER,
266 p_organization_id IN NUMBER,
267 p_organization_code IN VARCHAR2,
268 p_wip_entity_id IN NUMBER,
269 p_wip_entity_name IN VARCHAR2,
270 p_entity_type IN NUMBER,
271 p_primary_item_id IN NUMBER,
272 p_line_id IN NUMBER,
273 p_line_code IN VARCHAR2,
274 p_repetitive_schedule_id IN NUMBER,
275 p_transaction_date IN DATE,
276 p_acct_period_id IN NUMBER,
277 p_fm_operation_seq_num IN NUMBER,
278 p_fm_operation_code IN VARCHAR2,
279 p_fm_department_id IN NUMBER,
280 p_fm_department_code IN VARCHAR2,
281 p_fm_intraoperation_step_type IN NUMBER,
282 p_to_operation_seq_num IN NUMBER,
283 p_to_operation_code IN VARCHAR2,
284 p_to_department_id IN NUMBER,
285 p_to_department_code IN VARCHAR2,
286 p_to_intraoperation_step_type IN NUMBER,
287 p_transaction_quantity IN NUMBER,
288 p_transaction_uom IN VARCHAR2,
289 p_primary_quantity IN NUMBER,
290 p_primary_uom IN VARCHAR2,
291 p_scrap_account_id IN NUMBER,
292 p_reason_id IN NUMBER,
293 p_reason_name IN VARCHAR2,
294 p_reference IN VARCHAR2,
295 p_attribute_category IN VARCHAR2,
296 p_attribute1 IN VARCHAR2,
297 p_attribute2 IN VARCHAR2,
298 p_attribute3 IN VARCHAR2,
299 p_attribute4 IN VARCHAR2,
300 p_attribute5 IN VARCHAR2,
301 p_attribute6 IN VARCHAR2,
302 p_attribute7 IN VARCHAR2,
303 p_attribute8 IN VARCHAR2,
304 p_attribute9 IN VARCHAR2,
305 p_attribute10 IN VARCHAR2,
306 p_attribute11 IN VARCHAR2,
307 p_attribute12 IN VARCHAR2,
308 p_attribute13 IN VARCHAR2,
309 p_attribute14 IN VARCHAR2,
310 p_attribute15 IN VARCHAR2,
311 p_qa_collection_id IN NUMBER,
312 p_kanban_card_id IN NUMBER,
313 p_oc_transaction_qty IN NUMBER,
314 p_oc_primary_qty IN NUMBER,
315 p_oc_transaction_id IN NUMBER,
316 p_xml_document_id IN VARCHAR2,
317 p_processing_order IN NUMBER,
318 p_batch_id IN NUMBER,
319 p_employee_id IN NUMBER,
320 p_completed_instructions IN NUMBER);
321
322 /*****************************************************************************
323 * This procedure update the lock_flag value to 'N' in MMTT if the move
324 * transaction failed during Move Processing Phase so that no records will
325 * will stuck in MMTT. Added this procedure for fix of bug 8473023(FP 8358813)
326 *
327 * PARAMETER:
328 *
329 * p_assy_header_id mmtt.transaction_header_id
330 *
331 ****************************************************************************/
332 PROCEDURE clean_up(p_assy_header_id IN NUMBER);
333
334 END wip_movProc_priv;