1 PACKAGE wip_movProc_grp AS
2 /* $Header: wipmvgps.pls 120.0 2005/05/25 08:17:27 appldev noship $*/
3
4 /****************************************************************************
5 *
6 * This procedure works similar to backflushIntoMMTT and backflushIntoMTI
7 * except that this procedure will put item/lot/serial in PL/SQL object
8 * instead of insert them into MMTT and MTI. This procedure will be called
9 * from OSFM OA page.
10 *
11 * PARAMETERS:
12 *
13 * p_wipEntityID WMTI.WIP_ENTITY_ID
14 * p_orgID WMTI.ORGANIZATION_ID
15 * p_primaryQty WMTI.PRIMARY_QUANTITY
16 * p_txnDate WMTI.TRANSACTION_DATE
17 * p_txnHdrID MMTT.TRANSACTION_HEADER_ID. Caller can generate this
18 * value from mtl_material_transactions_s.
19 * p_txnType There are 3 possible values for this parameter
20 * WIP_CONSTANTS.MOVE_TXN (1) [move/scrap/reject]
21 * WIP_CONSTANTS.COMP_TXN (2) [completion/EZ completion]
22 * WIP_CONSTANTS.RET_TXN (3) [return/EZ return]
23 * p_fmOp WMTI.FM_OPERATION_SEQ_NUM. Only pass this value if
24 * not completion and return transactions.
25 * p_fmStep WMTI.FM_INTRAOPERATION_STEP_TYPE. Only pass this
26 * value if not completion and return transactions.
27 * p_toOp WMTI.TO_OPERATION_SEQ_NUM. Only pass this value if
28 * not completion and return transactions.
29 * p_toStep WMTI.TO_INTRAOPERATION_STEP_TYPE. Only pass this
30 * value if not completion and return transactions.
31 * p_movTxnID WMTI.TRANSACTION_ID of parent move record. Only pass
32 * if not completion and return transactions. Caller can
33 * generate this value from wip_transactions_s.
34 * p_cplTxnID MMTT.COMPLETION_TRANSACTION_ID. Only pass this
35 * value if completion/return/EZ completion/EZ return
36 * Caller can generate this value from
37 * mtl_material_transactions_s.
38 * x_lotSerRequired This parameter will determine whether we need to
39 * gather more lot/serial information from the user.
40 * There are 2 possible return values
41 * WIP_CONSTANTS.YES(1) and WIP_CONSTANTS.NO(2)
42 * x_compInfo PL/SQL object that store item/lot/serial information
43 * of backflush components.
44 * x_returnStatus There are 2 possible values
45 * *fnd_api.g_ret_sts_success*
46 * means this procedure succesfully processed
47 * *fnd_api.g_ret_sts_error*
48 * means this transaction error out
49 *****************************************************************************/
50 PROCEDURE backflush(p_wipEntityID IN NUMBER,
51 p_orgID IN NUMBER,
52 p_primaryQty IN NUMBER,
53 p_txnDate IN DATE,
54 p_txnHdrID IN NUMBER,
55 p_txnType IN NUMBER,
56 p_fmOp IN NUMBER,
57 p_fmStep IN NUMBER,
58 p_toOp IN NUMBER,
59 p_toStep IN NUMBER,
60 p_movTxnID IN NUMBER,
61 p_cplTxnID IN NUMBER:= NULL,
62 x_lotSerRequired OUT NOCOPY NUMBER,
63 x_compInfo OUT NOCOPY system.wip_lot_serial_obj_t,
64 x_returnStatus OUT NOCOPY VARCHAR2);
65
66 /****************************************************************************
67 *
68 * This procedure should be called from WIP Completion, WIP Move,
69 * and OSFM Move forms to insert all backflush components into MMTT. This
70 * procedure should be called before calling the main processors because we
71 * may need to gather lot/serial information for the backflush components.
72 * By the time the main processor pick up the record, all the information
73 * should be ready.
74 *
75 * PARAMETERS:
76 *
77 * p_wipEntityID WMTI.WIP_ENTITY_ID
78 * p_orgID WMTI.ORGANIZATION_ID
79 * p_primaryQty WMTI.PRIMARY_QUANTITY
80 * p_txnDate WMTI.TRANSACTION_DATE
81 * p_txnHdrID MMTT.TRANSACTION_HEADER_ID. Caller can generate this
82 * value from mtl_material_transactions_s.
83 * p_txnType There are 3 possible values for this parameter
84 * WIP_CONSTANTS.MOVE_TXN (1) [move/scrap/reject]
85 * WIP_CONSTANTS.COMP_TXN (2) [completion/EZ completion]
86 * WIP_CONSTANTS.RET_TXN (3) [return/EZ return]
87 * p_fmOp WMTI.FM_OPERATION_SEQ_NUM. Only pass this value if
88 * not completion and return transactions.
89 * p_fmStep WMTI.FM_INTRAOPERATION_STEP_TYPE. Only pass this
90 * value if not completion and return transactions.
91 * p_toOp WMTI.TO_OPERATION_SEQ_NUM. Only pass this value if
92 * not completion and return transactions.
93 * p_toStep WMTI.TO_INTRAOPERATION_STEP_TYPE. Only pass this
94 * value if not completion and return transactions.
95 * p_movTxnID WMTI.TRANSACTION_ID of parent move record. Only pass
96 * if not completion and return transactions. Caller can
97 * generate this value from wip_transactions_s.
98 * p_cplTxnID MMTT.COMPLETION_TRANSACTION_ID. Only pass this
99 * value if completion/return/EZ completion/EZ return
100 * Caller can generate this value from
101 * mtl_material_transactions_s.
102 * p_mtlTxnMode material processing mode
103 * p_reasonID reason ID that will be inserted into MTI, MMTT
104 * p_reference reference text that will be inserted into MTI, MMTT
105 * x_bfRequired There are 3 possible return values
106 * WIP_CONSTANTS.WBF_NOBF (0):
107 * means there is no component to backflush
108 * WIP_CONSTANTS.WBF_BF_NOPAGE (1):
109 * means there are some components to backflush, but
110 * no need to go to backflush page
111 * WIP_CONSTANTS.WBF_BF_PAGE (2):
112 * means there are some components to backflush, and
113 * backflush page required to gather more lot/serial
114 * x_returnStatus There are 2 possible values
115 * *fnd_api.g_ret_sts_success*
116 * means this procedure succesfully processed
117 * *fnd_api.g_ret_sts_error*
118 * means this transaction error out
119 *****************************************************************************/
120 PROCEDURE backflushIntoMMTT(p_wipEntityID IN NUMBER,
121 p_orgID IN NUMBER,
122 p_primaryQty IN NUMBER,
123 p_txnDate IN DATE,
124 p_txnHdrID IN NUMBER,
125 p_txnType IN NUMBER,
126 p_fmOp IN NUMBER,
127 p_fmStep IN NUMBER,
128 p_toOp IN NUMBER,
129 p_toStep IN NUMBER,
130 p_movTxnID IN NUMBER,
131 p_cplTxnID IN NUMBER:= NULL,
132 p_mtlTxnMode IN NUMBER,
133 p_reasonID IN NUMBER:= NULL,
134 p_reference IN VARCHAR2:= NULL,
135 x_bfRequired OUT NOCOPY NUMBER,
136 x_returnStatus OUT NOCOPY VARCHAR2);
137
138 /****************************************************************************
139 *
140 * This procedure will be called from OSFM new backflush API for forward move
141 * and completion transaction. For undo and return transaction, OSFM
142 * will call their API to derive lot/serial of the component. This API is
143 * mainly use for background transaction.
144 *
145 * PARAMETERS:
146 *
147 * p_wipEntityID WMTI.WIP_ENTITY_ID
148 * p_orgID WMTI.ORGANIZATION_ID
149 * p_primaryQty WMTI.PRIMARY_QUANTITY
150 * p_txnDate WMTI.TRANSACTION_DATE
151 * p_txnHdrID MMTT.TRANSACTION_HEADER_ID. Caller can generate this
152 * value from mtl_material_transactions_s.
153 * p_txnType There are 3 possible values for this parameter
154 * WIP_CONSTANTS.MOVE_TXN (1) [move/scrap/reject]
155 * WIP_CONSTANTS.COMP_TXN (2) [completion/EZ completion]
156 * WIP_CONSTANTS.RET_TXN (3) [return/EZ return]
157 * p_fmOp WMTI.FM_OPERATION_SEQ_NUM. Only pass this value if
158 * not completion and return transactions.
159 * p_fmStep WMTI.FM_INTRAOPERATION_STEP_TYPE. Only pass this
160 * value if not completion and return transactions.
161 * p_toOp WMTI.TO_OPERATION_SEQ_NUM. Only pass this value if
162 * not completion and return transactions.
163 * p_toStep WMTI.TO_INTRAOPERATION_STEP_TYPE. Only pass this
164 * value if not completion and return transactions.
165 * p_movTxnID WMTI.TRANSACTION_ID of parent move record. Only pass
166 * if not completion and return transactions. Caller can
167 * generate this value from wip_transactions_s.
168 * p_cplTxnID MMTT.COMPLETION_TRANSACTION_ID. Only pass this
169 * value if completion/return/EZ completion/EZ return
170 * Caller can generate this value from
171 * mtl_material_transactions_s.
172 * p_mtlTxnMode material processing mode
173 * p_reasonID reason ID that will be inserted into MTI, MMTT
174 * p_reference reference text that will be inserted into MTI, MMTT
175 * x_lotSerRequired This parameter will determine whether we need to
176 * gather more lot/serial information from the user.
177 * There are 2 possible return values
178 * WIP_CONSTANTS.YES(1) and WIP_CONSTANTS.NO(2)
179 * x_returnStatus There are 2 possible values
180 * *fnd_api.g_ret_sts_success*
181 * means this procedure succesfully processed
182 * *fnd_api.g_ret_sts_error*
183 * means this transaction error out
184 *****************************************************************************/
185 PROCEDURE backflushIntoMTI(p_wipEntityID IN NUMBER,
186 p_orgID IN NUMBER,
187 p_primaryQty IN NUMBER,
188 p_txnDate IN DATE,
189 p_txnHdrID IN NUMBER,
190 p_txnType IN NUMBER,
191 p_fmOp IN NUMBER,
192 p_fmStep IN NUMBER,
193 p_toOp IN NUMBER,
194 p_toStep IN NUMBER,
195 p_movTxnID IN NUMBER,
196 p_cplTxnID IN NUMBER:= NULL,
197 p_mtlTxnMode IN NUMBER,
198 p_reasonID IN NUMBER:= NULL,
199 p_reference IN VARCHAR2:= NULL,
200 x_lotSerRequired OUT NOCOPY NUMBER,
201 x_returnStatus OUT NOCOPY VARCHAR2);
202
203 /***************************************************************************
204 * This procedure will be used to do move, easy-return, easy-completion, and
205 * scrap transaction for Discrete, OSFM jobs, and Repetitive Schedule. The
206 * caller need to insert the record into WIP_MOVE_TXN_INTERFACE before calling
207 * this routine. Caller can generate wmti.transaction_id and wmti.group_id
208 * from the sequence wip_transactions_s. Caller need to insert both group_id
209 * and transaction_id before calling the procedure below. These 2 columns
210 * should have to same value. Caller should insert wmti.process_phase =
211 * 1(Validation) if validation needed, and insert wmti.process_phase =
212 * 2(Processing) if no validation needed(e.g. form do the validation), and
213 * insert wmti.process_status = 2(Running) to prevent Move Manager from
214 * picking up this record.
215 *
216 * PARAMETER:
217 *
218 * p_movTxnid transaction_id in WIP_MOVE_TXN_INTERFACE
219 * p_procPhase There are 2 possible values
220 * WIP_CONSTANTS.MOVE_VAL(1),
221 * and WIP_CONSTANTS.MOVE_PROC(2)
222 * This value should be the same as WMTI.PROCESS_PHASE
223 * p_txnHdrID MMTT.TRANSACTION_HEADER_ID. Caller can
224 * generate this value from mtl_material_transactions_s.
225 * p_mtlMode Material processing mode. Can be either
226 * WIP_CONSTANTS.BACKGROUND or WIP_CONSTANTS.ONLINE or
227 * WIP_CONSTANTS.IMMED_CONC
228 * p_cplTxnID MMTT.COMPLETION_TRANSACTION_ID. Caller can
229 * generate this value from mtl_material_transactions_s.
230 * p_commmit commit the change to the database if succesfully
231 * processing ? pass
232 * fnd_api.g_true or fnd_api.g_false
233 * if callers do not pass anything, no commit occur
234 * x_returnStatus There are 2 possible values
235 * *fnd_api.g_ret_sts_success*
236 * means the move transaction succesfully processed
237 * *fnd_api.g_ret_sts_unexp_error*
238 * means an exception occurred
239 * The size of this variable should be VARCHAR2(1)
240 * x_errorMsg contains the error message on failure. null on success.
241 * The size of this variable should be VARCHAR2(1000)
242 * because there may be errors in several columns.
243 *
244 * NOTE:
245 * 1. This procedure should be called if caller want to process one record at
246 * a time such as Online transaction.
247 * 2. This procedure will return fnd_api.g_ret_sts_unexp_error if this records
248 * failed. The caller can check the error message from x_errorMsg.
249 * 3. The caller does not have to insert child record for overmove/
250 * overcompletion. This API will take care everything. The caller does not
251 * have to call QA API either.
252 ***************************************************************************/
253 PROCEDURE processInterface(p_movTxnID IN NUMBER,
254 p_procPhase IN NUMBER,
255 p_txnHdrID IN NUMBER,
256 p_mtlMode IN NUMBER,
257 p_cplTxnID IN NUMBER := NULL,
258 p_commit IN VARCHAR2 := NULL,
259 x_returnStatus OUT NOCOPY VARCHAR2,
260 x_errorMsg OUT NOCOPY VARCHAR2);
261
262 /***************************************************************************
263 * This procedure will be used to do move, easy-return, easy-completion, and
264 * scrap transaction for Discrete, OSFM jobs, and Repetitive Schedule. The
265 * caller need to insert the record into WIP_MOVE_TXN_INTERFACE before calling
266 * this routine. Caller can generate wmti.group_id from the sequence
267 * wip_transactions_s. Only group_id is mandatory for procedure below. Caller
268 * does not have to insert transaction_id. Caller should always insert
269 * wmti.process_phase = 1(Validation) to make sure that the records are valid,
270 * and insert wmti.process_status = 2(Running) to prevent Move
271 * Manager from picking up these records.
272 *
273 * PARAMETER:
274 *
275 * p_groupID group_id in WIP_MOVE_TXN_INTERFACE
276 * p_commmit commit the change to the database if no record in this
277 * group error out ? pass
278 * fnd_api.g_true or fnd_api.g_false
282 * means the move transaction succesfully processed
279 * if callers do not pass anything, no commit occur
280 * x_returnStatus There are 2 possible values
281 * *fnd_api.g_ret_sts_success*
283 * *fnd_api.g_ret_sts_unexp_error*
284 * means an exception occurred
285 * The size of this variable should be VARCHAR2(1)
286 *
287 * NOTE:
288 * 1. This procedure should be called if caller want to do batch processing for
289 * multiple records in WMTI.
290 * 2. This procedure will return fnd_api.g_ret_sts_unexp_error if one or more
291 * records in this group_id failed. The caller can check the error message,
292 * and error column from WIP_TXN_INTERFACE_ERRORS.
293 * 3. The caller does not have to insert child record for overmove/
294 * overcompletion. This API will take care everything. The callter does not
295 * have to call QA API either.
296 ***************************************************************************/
297 PROCEDURE processInterface(p_groupID IN NUMBER,
298 p_commit IN VARCHAR2 := NULL,
299 x_returnStatus OUT NOCOPY VARCHAR2);
300 END wip_movProc_grp;