1 package wip_bflProc_priv as
2 /* $Header: wipbflps.pls 120.3 2007/09/17 19:06:47 kboonyap ship $ */
3
4 --------------------------------------------------------------------------------------------------------------------
5 --This package is the wip backlush processor. It has procedures to explode a bom for work order-less transactions or
6 --query WRO for moves and completions. explodeRequirements() explodes the components into a database object while
7 --processRequirements has an option of whether to insert requirements into an object or directly into MMTT.
8 --------------------------------------------------------------------------------------------------------------------
9
10 -----------------
11 --package globals
12 -----------------
13 g_compTblExtendSize CONSTANT NUMBER := 100;
14
15 --------------------------------------------------------------------------------------------------------------------------
16 --This procedure makes sure all backflush requirements are populated. It does *not* do any WIP or INV processing.
17 --If you pass an initialized object in the x_compObj parameter, the object will be filled
18 --with components. Otherwise they will be directly inserted into MTI.
19 --non-obvious parameters:
20 --p_batchID: The value to be populated in the MTI.transaction_batch_id column. The batch sequence column is populated with
21 -- wip_constants.component_batch_seq
22 --p_assyQty: relative to WIP (+ for completions. - for returns).
23 --p_firstOp: The first operation to backflush. Pass -1 to backflush all operations until p_lastOp.
24 --p_lastOp: The last operation to backflush.
25 --p_firstMoveOp: The actual first move operation. Because of the autocharge/backflush flags, this value could be different
26 -- than p_firstOp. It is needed to determine whether to backflush autocharge operations. This value is not
27 -- necessary when performing assembly pulls (pass null in this case).
28 --p_lastMoveOp: The actual last move operation. Because of the autocharge/backflush flags, this value could be different
29 -- than p_lastOp. It is needed to determine whether to backflush autocharge operations. This value is not
30 -- necessary when performing assembly pulls (pass null in this case).
31 --p_srcCode: The source_code column value for the to be created MMTT records, if the procedure inserts into MMTT (see
32 -- x_compTbl argument).
33 --p_batchSeq: The batch sequence number to be insert into MTI and MMTT.
34 --p_mergeMode:
35 -- + if it is fnd_api.g_true then processRequirements() will 'merge' requirements from WRO with existing MMTT records,
36 -- i.e. update the quantity of the mmtt record
37 -- + if it is fnd_api.g_false then processRequirements() will not insert the WRO requirements into MMTT. Instead it
38 -- assumes the MMTT record fully represents the backflush transaction.
39 --p_reasonID: used during insert into MMTT. This parameter is ignored if inserting into object.
40 --p_reference: used during insert into MMTT. This parameter is ignored if inserting into object.
41 --p_initMsgList: initialize the message list? Pass fnd_api.g_true if so, g_false if not.
42 --p_endDebug: Clean up the log file? Pass fnd_api.g_true unless you plan to call
43 -- wip_logger.cleanUp() later.
44 --x_compTbl: Pass a null value if you want processRequirements() to insert directly into MMTT. Pass an initialized object
45 -- if you want the requirements inserted into an object instead.
46 --x_returnStatus: fnd_api.g_ret_sts_success on successful processing.
47 -- fnd_api.g_ret_sts_unexp_error if an unexpected error occurred.
48 --------------------------------------------------------------------------------------------------------------------------
49 procedure processRequirements(p_wipEntityID IN NUMBER,
50 p_wipEntityType IN NUMBER,
51 p_repSchedID IN NUMBER := null,
52 p_repLineID IN NUMBER := null,
53 p_cplTxnID IN NUMBER := null,
54 p_movTxnID IN NUMBER := null,
55 p_batchID IN NUMBER := null,
56 p_orgID IN NUMBER,
57 p_assyQty IN NUMBER, --relative to wip
58 p_txnDate IN DATE,
59 p_wipSupplyType IN NUMBER,
60 p_txnHdrID IN NUMBER,
61 p_firstOp IN NUMBER, -- -1 for regular completions
62 p_lastOp IN NUMBER, -- last op_seq for completions
63 p_firstMoveOp IN NUMBER := null,
64 p_lastMoveOp IN NUMBER := null,
65 p_srcCode IN VARCHAR2 := null,
66 p_batchSeq IN NUMBER := null,
67 p_lockFlag IN NUMBER := null,
68 p_mergeMode IN VARCHAR2, --see above explanation for setting this parameter
69 p_reasonID IN NUMBER := null,
70 p_reference IN VARCHAR2 := null,
71 p_initMsgList IN VARCHAR2,
72 p_endDebug IN VARCHAR2, --pass true unless calling wip_logger.cleanup() elsewhere
73 p_mtlTxnMode IN NUMBER,
74 x_compTbl IN OUT NOCOPY system.wip_component_tbl_t, --pass null if you want to insert into MMTT
75 x_returnStatus OUT NOCOPY VARCHAR2);
76
77
78 ----------------------------------------------------------------------------------------
79 --This procedure explodes the item's bom into the x_compTbl table. This procedure is a
80 --fairly complex wrapper on top of a bom routine.
81 --non-obvious parameters:
82 --p_qty: pass positive quantity for completions, negative for returns.
83 -- p_unitNumber: To explode components properly based on unit number for unit effective assemblies.
84 -- p_implFlag: This flag decides whether unimplemented ECOs should be considered.
85 -- This flag should be 2 for discrete(consider implemented and unimplemented) and
86 -- this goes along with profile 'WIP:Exclude ECOs'.
87 -- For WOL/Flow, this flag should be 1 (consider only implemented changes).
88 --p_initMsgList: initialize the message list?
89 --p_endDebug: Clean up the log file? Pass fnd_api.g_true unless you plan to call
90 -- wip_logger.cleanUp() later.
91 --x_compTbl: The component requirements for the given item and alternate bom designator.
92 --x_returnStatus: fnd_api.g_ret_sts_success on successful processing.
93 -- fnd_api.g_ret_sts_unexp_error if an unexpected error occurred.
94 ----------------------------------------------------------------------------------------
95 procedure explodeRequirements(p_itemID IN NUMBER,
96 p_orgID IN NUMBER,
97 p_qty IN NUMBER,
98 p_altBomDesig IN VARCHAR2,
99 p_altOption IN NUMBER,
100 /* Fix for bug#3423629 */ p_bomRevDate IN DATE DEFAULT NULL,
101 p_txnDate IN DATE,
102 /* Fix for bug 5383135 */ p_implFlag IN NUMBER,
103 p_projectID IN NUMBER,
104 p_taskID IN NUMBER,
105 /* added for bug 5332615 */ p_unitNumber in varchar2 DEFAULT '',
106 p_initMsgList IN VARCHAR2,
107 p_endDebug IN VARCHAR2,
108 x_compTbl OUT NOCOPY system.wip_component_tbl_t,
109 x_returnStatus OUT NOCOPY VARCHAR2);
110
111
112 /****************************************************************************
113 *
114 * This procedure should be called from WIP Completion, WIP Move,
115 * and OSFM Move forms to insert all backflush components into MMTT. This
116 * procedure should be called before calling the main processors because we
117 * may need to gather lot/serial information for the backflush components.
118 * By the time the main processor pick up the record, all the information
119 * should be ready.
120 *
121 * PARAMETERS:
122 *
123 * p_wipEntityID WMTI.WIP_ENTITY_ID
124 * p_orgID WMTI.ORGANIZATION_ID
125 * p_ocQty Overcompleted quantity. Pass NULL if not
126 * overcompleted transaction.
127 * p_primaryQty WMTI.PRIMARY_QUANTITY
128 * p_txnDate WMTI.TRANSACTION_DATE
129 * p_txnHdrID MMTT.TRANSACTION_HEADER_ID. Caller can generate this
130 * value from mtl_material_transactions_s.
131 * p_batchID MMTT.TRANSACTION_BATCH_ID. For move, EZ Complete,
132 * EZ return transactions, pass TRANSACTION_HEADER_ID.
133 * For completion and return transactions, pass
134 * COMPLETION_TRANSACTION_ID.
135 * p_txnType There are 3 possible values for this parameter
136 * WIP_CONSTANTS.MOVE_TXN (1) [move/scrap/reject]
137 * WIP_CONSTANTS.COMP_TXN (2) [completion/EZ completion]
138 * WIP_CONSTANTS.RET_TXN (3) [return/EZ return]
139 * p_entityType WE.ENTITY_TYPE. The caller should pass either
140 * WIP_CONSTANTS.DISCRETE or WIP_CONSTANTS.LOTBASED or
141 * WIP_CONSTANTS.REPETITIVE
142 * p_tblName This parameter will be used to determine the table
143 * to insert components to. Caller can pass either
144 * WIP_CONSTANTS.MMTT_TBL or WIP_CONSTANTS.MTI_TBL
145 * If called from WIP form, the caller should pass
146 * 'MMTT' because we need to set some columns in MMTT
147 * before we can use Inventory lot/serial page to
148 * gather lot/serial information. Otherwise, pass 'MTI'.
149 * p_lineID LINE_ID. Only pass this value for repetitive schedule
150 * p_fmOp WMTI.FM_OPERATION_SEQ_NUM. Only pass this value if
151 * not completion and return transactions.
152 * p_fmStep WMTI.FM_INTRAOPERATION_STEP_TYPE. Only pass this
153 * value if not completion and return transactions.
154 * p_toOp WMTI.TO_OPERATION_SEQ_NUM. Only pass this value if
155 * not completion and return transactions.
156 * p_toStep WMTI.TO_INTRAOPERATION_STEP_TYPE. Only pass this
157 * value if not completion and return transactions.
158 * p_childMovTxnID WMTI.TRANSACTION_ID of child move record. Only pass
159 * this value if overmove or overcomplete.Caller can
160 * generate this value from wip_transactions_s.
161 * p_movTxnID WMTI.TRANSACTION_ID of parent move record. Only pass
162 * if not completion and return transactions. Caller can
163 * generate this value from wip_transactions_s.
164 * p_cplTxnID MMTT.COMPLETION_TRANSACTION_ID. Only pass this
165 * value if completion/return/EZ completion/EZ return
166 * Caller can generate this value from
167 * mtl_material_transactions_s
168 * p_batchSeq Batch sequence number that will be insert into MTI
169 * and MMTT. If caller does not pass anything, we will
170 * default to WIP_CONSTANTS.COMPONENT_BATCH_SEQ(2)
171 * p_fmMoveProcessor Pass WIP_CONSTANTS.YES(1) if called from Move
172 * Processing code. Otherwise, leave it null or pass
173 * WIP_CONSTANTS.NO(2).
174 * p_mtlTxnMode material processing mode.
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_bfRequired There are 3 possible return values
180 * WIP_CONSTANTS.WBF_NOBF (0):
181 * means there is no component to backflush
182 * WIP_CONSTANTS.WBF_BF_NOPAGE (1):
183 * means there are some components to backflush, but
184 * no need to go to backflush page
185 * WIP_CONSTANTS.WBF_BF_PAGE (2):
186 * means there are some components to backflush, and
187 * backflush page required to gather more lot/serial
188 * x_returnStatus There are 2 possible values
189 * *fnd_api.g_ret_sts_success*
190 * means this procedure succesfully processed
191 * *fnd_api.g_ret_sts_error*
192 * means this transaction error out
193 */
194 PROCEDURE backflush(p_wipEntityID IN NUMBER,
195 p_orgID IN NUMBER,
196 p_primaryQty IN NUMBER,
197 p_txnDate IN DATE,
198 p_txnHdrID IN NUMBER,
199 p_batchID IN NUMBER,
200 p_txnType IN NUMBER,
201 p_entityType IN NUMBER,
202 p_tblName IN VARCHAR2,
203 p_lineID IN NUMBER:= NULL,
204 p_fmOp IN NUMBER:= NULL,
205 p_fmStep IN NUMBER:= NULL,
206 p_toOp IN NUMBER:= NULL,
207 p_toStep IN NUMBER:= NULL,
208 p_ocQty IN NUMBER:= NULL,
209 p_childMovTxnID IN NUMBER:= NULL,
210 p_movTxnID IN NUMBER:= NULL,
211 p_cplTxnID IN NUMBER:= NULL,
212 p_batchSeq IN NUMBER:= NULL,
213 p_fmMoveProcessor IN NUMBER:= NULL,
214 p_lockFlag IN NUMBER:= NULL,
215 p_mtlTxnMode IN NUMBER,
216 p_reasonID IN NUMBER := NULL,
217 p_reference IN VARCHAR2 := NULL,
218 x_lotSerRequired OUT NOCOPY NUMBER,
219 x_bfRequired OUT NOCOPY NUMBER,
220 x_returnStatus OUT NOCOPY VARCHAR2);
221
222 /****************************************************************************
223 *
224 * This procedure will be called from WIP OA Transaction page(move related
225 * transctions, completion, and return), and OSFM OA Move page to populate all
226 * backflush components into PL/SQL object(system.wip_lot_serial_obj_t). This
227 * procedure should be called before calling the main processors because we
228 * may need to gather lot/serial information for the backflush components.
229 * By the time the main processor pick up the record, all the information
230 * should be ready.
231 *
232 * PARAMETERS:
233 *
234 * p_wipEntityID WMTI.WIP_ENTITY_ID
235 * p_orgID WMTI.ORGANIZATION_ID
239 * p_txnDate WMTI.TRANSACTION_DATE
236 * p_ocQty Overcompleted quantity. Pass NULL if not
237 * overcompleted transaction.
238 * p_primaryQty WMTI.PRIMARY_QUANTITY
240 * p_txnHdrID MMTT.TRANSACTION_HEADER_ID. Caller can generate this
241 * value from mtl_material_transactions_s.
242 * p_txnType There are 3 possible values for this parameter
243 * WIP_CONSTANTS.MOVE_TXN (1) [move/scrap/reject]
244 * WIP_CONSTANTS.COMP_TXN (2) [completion/EZ completion]
245 * WIP_CONSTANTS.RET_TXN (3) [return/EZ return]
246 * p_entityType WE.ENTITY_TYPE. The caller should pass either
247 * WIP_CONSTANTS.DISCRETE or WIP_CONSTANTS.LOTBASED or
248 * WIP_CONSTANTS.REPETITIVE
249 * p_fmOp WMTI.FM_OPERATION_SEQ_NUM. Only pass this value if
250 * not completion and return transactions.
251 * p_fmStep WMTI.FM_INTRAOPERATION_STEP_TYPE. Only pass this
252 * value if not completion and return transactions.
253 * p_toOp WMTI.TO_OPERATION_SEQ_NUM. Only pass this value if
254 * not completion and return transactions.
255 * p_toStep WMTI.TO_INTRAOPERATION_STEP_TYPE. Only pass this
256 * value if not completion and return transactions.
257 * p_childMovTxnID WMTI.TRANSACTION_ID of child move record. Only pass
258 * this value if overmove or overcomplete.Caller can
259 * generate this value from wip_transactions_s.
260 * p_movTxnID WMTI.TRANSACTION_ID of parent move record. Only pass
261 * if not completion and return transactions. Caller can
262 * generate this value from wip_transactions_s.
263 * p_cplTxnID MMTT.COMPLETION_TRANSACTION_ID. Only pass this
264 * value if completion/return/EZ completion/EZ return
265 * Caller can generate this value from
266 * mtl_material_transactions_s
267 * p_objectID Genealogy object ID of assembly serial number. We
268 * will use this parameter to differentiate between
269 * regular and serialized transactions.
270 * x_compInfo PL/SQL object that stores backflush components and
271 * their corresponding lot and serial.
272 * x_lotSerRequired This parameter will determine whether we need to
273 * gather more lot/serial information from the user.
274 * There are 2 possible return values
275 * WIP_CONSTANTS.YES(1) and WIP_CONSTANTS.NO(2)
276 * x_returnStatus There are 2 possible values
277 * *fnd_api.g_ret_sts_success*
278 * means this procedure succesfully processed
279 * *fnd_api.g_ret_sts_error*
280 * means this transaction error out
281 */
282 PROCEDURE backflush(p_wipEntityID IN NUMBER,
283 p_orgID IN NUMBER,
284 p_primaryQty IN NUMBER,
285 p_txnDate IN DATE,
286 p_txnHdrID IN NUMBER,
287 p_txnType IN NUMBER,
288 p_entityType IN NUMBER,
289 p_fmOp IN NUMBER:= NULL,
290 p_fmStep IN NUMBER:= NULL,
291 p_toOp IN NUMBER:= NULL,
292 p_toStep IN NUMBER:= NULL,
293 p_ocQty IN NUMBER:= NULL,
294 p_childMovTxnID IN NUMBER:= NULL,
295 p_movTxnID IN NUMBER:= NULL,
296 p_cplTxnID IN NUMBER:= NULL,
297 p_objectID IN NUMBER:= NULL,
298 x_compInfo OUT NOCOPY system.wip_lot_serial_obj_t,
299 x_lotSerRequired OUT NOCOPY NUMBER,
300 x_returnStatus OUT NOCOPY VARCHAR2);
301
302 end wip_bflProc_priv;
[Home] [Help]