1 PACKAGE wma_completion AS
2 /* $Header: wmapcmps.pls 120.1.12000000.2 2007/07/26 10:53:40 sisankar ship $ */
3
4 /**
5 * Contains the set of parameters that will be passed from the Mobile Apps
6 * Completion and Return forms to the process procedure for processing the
7 * transaction. The list contains all displayed fields as well as hidden
8 * fields (derived from LOVs on the form). It also contains the transaction
9 * IDs that are used to associate the transaction''s parent record in
10 * MTL_MATERIAL_TRANSACTIONS_TEMP to its child records in other tables (an
11 * example is the lot and serial information for the item.)
12 * All fields are initialized to FND_API initialization values. Boolean
13 * values are initailized to false. Strings lengths are derived from
14 * those defined in WIP_CONSTANTS package.
15 * HISTORY:
16 * 02-MAR-2006 spondalu ER 4163405: Added two new parameters to CmpParams
17 * and CmpTxnRec.
18 *
19 */
20 TYPE CmpParams IS RECORD
21 (
22 environment wma_common.environment,
23 transactionType NUMBER,
24 transactionHeaderID NUMBER,
25 transactionIntID NUMBER,
26 cmpTransactionID NUMBER,
27 movTransactionID NUMBER,
28 wipEntityID NUMBER,
29 wipEntityName VARCHAR2(241),
30 itemID NUMBER,
31 itemName VARCHAR2(241),
32 overcomplete BOOLEAN,
33 transactionQty NUMBER,
34 transactionUOM VARCHAR2(4),
35 subinv VARCHAR2(11),
36 locatorID NUMBER,
37 locatorName VARCHAR2(241),
38 kanbanCardID NUMBER,
39 qualityID NUMBER,
40 projectID NUMBER,
41 taskID NUMBER,
42 lpnID NUMBER,
43 isFromSerializedPage NUMBER,
44 demandSourceHeaderID NUMBER, /* ER 4163405 */
45 demandSourceLineID NUMBER
46 );
47
48 TYPE LpnCmpParams IS RECORD
49 (
50 environment wma_common.environment,
51 transactionTypeID NUMBER,
52 headerID NUMBER,
53 wipEntityID NUMBER,
54 wipEntityName VARCHAR2(241),
55 itemID NUMBER,
56 itemName VARCHAR2(241),
57 overcomplete BOOLEAN,
58 transactionQty NUMBER,
59 transactionUOM VARCHAR2(4),
60 subinv VARCHAR2(11),
61 locatorID NUMBER,
62 locatorName VARCHAR2(241),
63 kanbanCardID NUMBER,
64 qualityID NUMBER,
65 lpnID NUMBER,
66 completionTxnID NUMBER
67 );
68
69 /**
70 * This is the record type for the record to be populated and inserted into
71 * the MTL_MATERIAL_TRANSACTIONS_TEMP table.
72 */
73 TYPE CmpTxnRec IS RECORD(transaction_interface_id NUMBER,
74 transaction_header_id NUMBER,
75 item_lot_control_code NUMBER,
76 operation_seq_num NUMBER,
77 revision VARCHAR2(3),
78 transaction_type_id NUMBER,
79 transaction_action_id NUMBER,
80 primary_quantity NUMBER,
81 transaction_quantity NUMBER,
82 overcompletion_transaction_id NUMBER,
83 overcompletion_transaction_qty NUMBER,
84 overcompletion_primary_qty NUMBER,
85 transaction_source_id NUMBER,
86 transaction_source_type_id NUMBER,
87 completion_transaction_id NUMBER,
88 move_transaction_id NUMBER,
89 transaction_mode NUMBER,
90 created_by NUMBER,
91 creation_date DATE,
92 last_updated_by NUMBER,
93 last_update_date DATE,
94 source_code VARCHAR2(30),
95 source_line_id NUMBER,
96 source_header_id NUMBER,
97 inventory_item_id NUMBER,
98 subinventory_code VARCHAR2(10),
99 locator_id NUMBER,
100 transaction_uom VARCHAR2(3),
101 transaction_date DATE,
102 organization_id NUMBER,
103 acct_period_id NUMBER,
104 wip_entity_type NUMBER,
105 process_flag NUMBER,
106 final_completion_flag VARCHAR2(1),
107 project_id NUMBER,
108 task_id NUMBER,
109 source_project_id NUMBER,
110 source_task_id NUMBER,
111 qa_collection_id NUMBER,
112 kanban_card_id NUMBER,
113 lpn_id NUMBER,
114 demand_source_header_id NUMBER, /* ER 4163405 */
115 demand_source_line_id NUMBER);
116
117
118 TYPE LpnCmpTxnRec IS RECORD (row wip_lpn_completions%ROWTYPE);
119
120 /**
121 * This procedure is the entry point into the Completion and Return
122 * Processing code for background processing.
123 */
124 PROCEDURE process(parameters IN CmpParams,
125 processInv IN VARCHAR2,
126 txnMode IN NUMBER := NULL, --override wip parammeter setting
127 status OUT NOCOPY NUMBER,
128 errMessage OUT NOCOPY VARCHAR2);
129
130 PROCEDURE process(parameters IN LpnCmpParams,
131 status OUT NOCOPY NUMBER,
132 errMessage OUT NOCOPY VARCHAR2,
133 cmpl_txnTmpId OUT NOCOPY NUMBER); -- Added for Bug 6013398.
134 /**
135 * This function derives and validates the values necessary for executing a
136 * completion or return transaction. Given the form parameters, it populates
137 * cmpRecord preparing it to be inserted into the interface table.
138 */
139 FUNCTION derive(cmpRecord IN OUT NOCOPY CmpTxnRec,
140 overCplRec IN OUT NOCOPY wip_cplProc_priv.completion_rec_t,
141 parameters IN CmpParams,
142 txnMode IN NUMBER,
143 errMessage IN OUT NOCOPY VARCHAR2) return boolean;
144
145 FUNCTION derive(LpnCmpRecord IN OUT NOCOPY LpnCmpTxnRec,
146 parameters IN LpnCmpParams,
147 errMessage IN OUT NOCOPY VARCHAR2) return boolean;
148
149 /**
150 * Inserts a populated CmpTxnRec into MTL_MATERIAL_TRANSACTIONS_TEMP
151 */
152 FUNCTION put(cmpRecord IN CmpTxnRec,
153 errMessage IN OUT NOCOPY VARCHAR2) return boolean;
154
155 /**
156 * Inserts a populated CmpTxnRec into wip_lpn_completions
157 */
158 FUNCTION put(lpnCmpRecord IN LpnCmpTxnRec,
159 errMessage IN OUT NOCOPY VARCHAR2) return boolean;
160
161 /**
162 * checks the transaction quantity entered by the user.
163 * In the case of completion, the quantity should not exceed the quantity
164 * available to complete for the job. If overcompleting, however, the
165 * transaction quantity should be greater than the available quantity
166 * and should not exceed the overcompletion tolerance.
167 * In the case of return, the quantity should not exceed the completed
168 * quantity.
169 * If the structures passed to this procedure are not available, use the
170 * overloaded version.
171 */
172 FUNCTION checkQuantity (parameters IN CmpParams,
173 job IN wma_common.Job,
174 errMessage IN OUT NOCOPY VARCHAR2) return boolean;
175
176
177 /**
178 * checks the transaction quantity entered by the user.
179 * In the case of completion, the quantity should not exceed the quantity
180 * available to complete for the job. If overcompleting, however, the
181 * transaction quantity should be greater than the available quantity,
182 * and should not exceed the overcompletion tolerance.
183 * In the case of return, the quantity should not exceed the completed
184 * quantity.
185 */
186 FUNCTION checkQuantity (orgID IN NUMBER,
187 wipEntityID IN NUMBER,
188 overcomplete IN BOOLEAN,
189 transactionType IN NUMBER,
190 transactionQty IN NUMBER,
191 availableQty IN NUMBER,
192 completedQty IN NUMBER,
193 errMessage IN OUT NOCOPY VARCHAR2) return boolean;
194
195 /**
196 * Check whether exceeds overcompletion tolerance or not.
197 */
198 procedure checkOverCpl(p_orgID in number,
199 p_wipEntityID in number,
200 p_overCplQty in number,
201 x_returnStatus out nocopy varchar2,
202 x_errMessage out nocopy varchar2);
203
204
205 /**
206 * given a Job, getLastOpSeq() gets the last operation sequence
207 * associated with the job if the job has a routing. If the job
208 * does not have a routing, -1 is returned.
209 */
210 FUNCTION getLastOpSeq (job IN wma_common.Job) return number;
211
212
213 /**
214 * given a wipEntityID and an orgID, getLastOpSeq() gets the last
215 * operation sequence associated with the job if the job has a
216 * routing. If the job does not have a routing, -1 is returned.
217 */
218 FUNCTION getLastOpSeq (wipEntityID IN NUMBER,
219 orgID IN NUMBER) return number;
220
221
222 /**
223 * given a job, getAvilableQty() returns the quantity in the
224 * To Move step of the final operation if the job has a routing.
225 * If the job, does not have a routing, getAvailableQty() computes
226 * the available quantity to complete from the job quantities.
227 */
228 FUNCTION getAvailableQty (job IN wma_common.Job) return number;
229
230 /**
231 * Collects backflush components for the job into the x_compInfo object
232 */
233 procedure backflush(p_jobID IN NUMBER,
234 p_orgID IN NUMBER,
235 p_cplQty IN NUMBER,
236 p_overCplQty IN NUMBER,
237 p_cplTxnID IN NUMBER,
238 p_movTxnID IN NUMBER,
239 p_txnDate IN DATE,
240 p_txnHdrID IN NUMBER,
241 p_txnMode in number := null,
242 p_objectID in number,
243 x_lotEntryType OUT NOCOPY NUMBER,
244 x_compInfo OUT NOCOPY system.wip_lot_serial_obj_t,
245 x_returnStatus OUT NOCOPY VARCHAR2,
246 x_errMessage OUT NOCOPY VARCHAR2);
247
248 /**
249 * given a wipEntityID, orgID and itemID, getRevision() will validate
250 * whether bom_revision exists as an item_revision or not. If not
251 * return false. Otherwise return true. If bom_revision is null, derive
252 * it based on the transaction_date specified. In mobile it is sysdate.
253 * Parameters:
254 * wipEntityID the wip_entity_id of the job
255 * orgID the organization job belongs to.
256 * itemID the assembly item ID
257 * revision bom_revision if bom_revision exists as an item_revision
258 * if bom_revision is null, derive it based on txn_date
259 * Returns:
260 * true if bom_revision exist as an item_revision. Otherwise return false
261 */
262 FUNCTION getRevision (wipEntityID IN NUMBER,
263 orgID IN NUMBER,
264 itemID IN NUMBER,
265 revision OUT NOCOPY VARCHAR2) return boolean;
266
267 END wma_completion;