[Home] [Help]
PACKAGE BODY: APPS.CSP_PC_FORM_MTLTXNS
Source
1 PACKAGE BODY CSP_PC_FORM_MTLTXNS AS
2 /*$Header: cspgtmxb.pls 120.0 2005/05/24 18:13:03 appldev noship $*/
3 -- Start of Comments
4 -- Package name : CSP_PC_FORM_MTLTXNS
5 -- Purpose : CSP procedures for csp move order transactions.CSP procedures to insert, update and delete
6 -- records in the mtl_material_transactions_temp table.
7 -- History :
8 -- 27-Dec-99, Add procedure CSP_MO_Lines_Manual_Receipt.
9 -- 20-Dec-99, klou.
10 --
11 -- NOTE :
12 -- End of Comments
13
14 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_PC_FORM_MTLTXNS';
15 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtmxb.pls';
16
17 PROCEDURE Validate_And_Write (
18 -- Procedure name : Validate_And_Write
19 -- Purpose : A wrapper to prepare data to call the update, delete and insert procedures of the
20 -- CSP_Material_Transaactions_PVT.
21 P_Api_Version_Number IN NUMBER,
22 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
23 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
24 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
25 p_action_code IN NUMBER,
26 p_TRANSACTION_HEADER_ID IN NUMBER := FND_API.G_MISS_NUM,
27 px_TRANSACTION_TEMP_ID IN OUT NOCOPY NUMBER,
28 p_SOURCE_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
29 p_SOURCE_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
30 p_TRANSACTION_MODE IN NUMBER := FND_API.G_MISS_NUM,
31 p_LOCK_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
32 p_LAST_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
33 p_LAST_UPDATED_BY IN NUMBER := FND_API.G_MISS_NUM,
34 p_CREATION_DATE IN DATE := FND_API.G_MISS_DATE,
35 p_CREATED_BY IN NUMBER := FND_API.G_MISS_NUM,
36 p_LAST_UPDATE_LOGIN IN NUMBER := FND_API.G_MISS_NUM,
37 p_REQUEST_ID IN NUMBER := FND_API.G_MISS_NUM,
38 p_PROGRAM_APPLICATION_ID IN NUMBER := FND_API.G_MISS_NUM,
39 p_PROGRAM_ID IN NUMBER := FND_API.G_MISS_NUM,
40 p_PROGRAM_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
41 p_INVENTORY_ITEM_ID IN NUMBER := FND_API.G_MISS_NUM,
42 p_REVISION IN VARCHAR2 := FND_API.G_MISS_CHAR,
43 p_ORGANIZATION_ID IN NUMBER := FND_API.G_MISS_NUM,
44 p_SUBINVENTORY_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
45 p_LOCATOR_ID IN NUMBER := FND_API.G_MISS_NUM,
46 p_TRANSACTION_QUANTITY IN NUMBER := FND_API.G_MISS_NUM,
47 p_PRIMARY_QUANTITY IN NUMBER := FND_API.G_MISS_NUM,
48 p_TRANSACTION_UOM IN VARCHAR2 := FND_API.G_MISS_CHAR,
49 p_TRANSACTION_COST IN NUMBER := FND_API.G_MISS_NUM,
50 p_TRANSACTION_TYPE_ID IN NUMBER := FND_API.G_MISS_NUM,
51 p_TRANSACTION_ACTION_ID IN NUMBER := FND_API.G_MISS_NUM,
52 p_TRANSACTION_SOURCE_TYPE_ID IN NUMBER := FND_API.G_MISS_NUM,
53 p_TRANSACTION_SOURCE_ID IN NUMBER := FND_API.G_MISS_NUM,
54 p_TRANSACTION_SOURCE_NAME IN VARCHAR2 := FND_API.G_MISS_CHAR,
55 p_TRANSACTION_DATE IN DATE := FND_API.G_MISS_DATE,
56 p_ACCT_PERIOD_ID IN NUMBER := FND_API.G_MISS_NUM,
57 p_DISTRIBUTION_ACCOUNT_ID IN NUMBER := FND_API.G_MISS_NUM,
58 p_TRANSACTION_REFERENCE IN VARCHAR2 := FND_API.G_MISS_CHAR,
59 p_REQUISITION_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
60 p_REQUISITION_DISTRIBUTION_ID IN NUMBER := FND_API.G_MISS_NUM,
61 p_REASON_ID IN NUMBER := FND_API.G_MISS_NUM,
62 p_LOT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
63 p_LOT_EXPIRATION_DATE IN DATE := FND_API.G_MISS_DATE,
64 p_SERIAL_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
65 p_RECEIVING_DOCUMENT IN VARCHAR2 := FND_API.G_MISS_CHAR,
66 p_DEMAND_ID IN NUMBER := FND_API.G_MISS_NUM,
67 p_RCV_TRANSACTION_ID IN NUMBER := FND_API.G_MISS_NUM,
68 p_MOVE_TRANSACTION_ID IN NUMBER := FND_API.G_MISS_NUM,
69 p_COMPLETION_TRANSACTION_ID IN NUMBER := FND_API.G_MISS_NUM,
70 p_WIP_ENTITY_TYPE IN NUMBER := FND_API.G_MISS_NUM,
71 p_SCHEDULE_ID IN NUMBER := FND_API.G_MISS_NUM,
72 p_REPETITIVE_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
73 p_EMPLOYEE_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
74 p_PRIMARY_SWITCH IN NUMBER := FND_API.G_MISS_NUM,
75 p_SCHEDULE_UPDATE_CODE IN NUMBER := FND_API.G_MISS_NUM,
76 p_SETUP_TEARDOWN_CODE IN NUMBER := FND_API.G_MISS_NUM,
77 p_ITEM_ORDERING IN NUMBER := FND_API.G_MISS_NUM,
78 p_NEGATIVE_REQ_FLAG IN NUMBER := FND_API.G_MISS_NUM,
79 p_OPERATION_SEQ_NUM IN NUMBER := FND_API.G_MISS_NUM,
80 p_PICKING_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
81 p_TRX_SOURCE_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
82 p_TRX_SOURCE_DELIVERY_ID IN NUMBER := FND_API.G_MISS_NUM,
83 p_PHYSICAL_ADJUSTMENT_ID IN NUMBER := FND_API.G_MISS_NUM,
84 p_CYCLE_COUNT_ID IN NUMBER := FND_API.G_MISS_NUM,
85 p_RMA_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
86 p_CUSTOMER_SHIP_ID IN NUMBER := FND_API.G_MISS_NUM,
87 p_CURRENCY_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
88 p_CURRENCY_CONVERSION_RATE IN NUMBER := FND_API.G_MISS_NUM,
89 p_CURRENCY_CONVERSION_TYPE IN VARCHAR2 := FND_API.G_MISS_CHAR,
90 p_CURRENCY_CONVERSION_DATE IN DATE := FND_API.G_MISS_DATE,
91 p_USSGL_TRANSACTION_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
92 p_VENDOR_LOT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
93 p_ENCUMBRANCE_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
94 p_ENCUMBRANCE_AMOUNT IN NUMBER := FND_API.G_MISS_NUM,
95 p_SHIP_TO_LOCATION IN NUMBER := FND_API.G_MISS_NUM,
96 p_SHIPMENT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
97 p_TRANSFER_COST IN NUMBER := FND_API.G_MISS_NUM,
98 p_TRANSPORTATION_COST IN NUMBER := FND_API.G_MISS_NUM,
99 p_TRANSPORTATION_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
100 p_FREIGHT_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
101 p_CONTAINERS IN NUMBER := FND_API.G_MISS_NUM,
102 p_WAYBILL_AIRBILL IN VARCHAR2 := FND_API.G_MISS_CHAR,
103 p_EXPECTED_ARRIVAL_DATE IN DATE := FND_API.G_MISS_DATE,
104 p_TRANSFER_SUBINVENTORY IN VARCHAR2 := FND_API.G_MISS_CHAR,
105 p_TRANSFER_ORGANIZATION IN NUMBER := FND_API.G_MISS_NUM,
106 p_TRANSFER_TO_LOCATION IN NUMBER := FND_API.G_MISS_NUM,
107 p_NEW_AVERAGE_COST IN NUMBER := FND_API.G_MISS_NUM,
108 p_VALUE_CHANGE IN NUMBER := FND_API.G_MISS_NUM,
109 p_PERCENTAGE_CHANGE IN NUMBER := FND_API.G_MISS_NUM,
110 p_MATERIAL_ALLOCATION_TEMP_ID IN NUMBER := FND_API.G_MISS_NUM,
111 p_DEMAND_SOURCE_HEADER_ID IN NUMBER := FND_API.G_MISS_NUM,
112 p_DEMAND_SOURCE_LINE IN VARCHAR2 := FND_API.G_MISS_CHAR,
113 p_DEMAND_SOURCE_DELIVERY IN VARCHAR2 := FND_API.G_MISS_CHAR,
114 p_ITEM_SEGMENTS IN VARCHAR2 := FND_API.G_MISS_CHAR,
115 p_ITEM_DESCRIPTION IN VARCHAR2 := FND_API.G_MISS_CHAR,
116 p_ITEM_TRX_ENABLED_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
117 p_ITEM_LOCATION_CONTROL_CODE IN NUMBER := FND_API.G_MISS_NUM,
118 p_ITEM_RESTRICT_SUBINV_CODE IN NUMBER := FND_API.G_MISS_NUM,
119 p_ITEM_RESTRICT_LOCATORS_CODE IN NUMBER := FND_API.G_MISS_NUM,
120 p_ITEM_REV_QTY_CONTROL_CODE IN NUMBER := FND_API.G_MISS_NUM,
121 p_ITEM_PRIMARY_UOM_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
122 p_ITEM_UOM_CLASS IN VARCHAR2 := FND_API.G_MISS_CHAR,
123 p_ITEM_SHELF_LIFE_CODE IN NUMBER := FND_API.G_MISS_NUM,
124 p_ITEM_SHELF_LIFE_DAYS IN NUMBER := FND_API.G_MISS_NUM,
125 p_ITEM_LOT_CONTROL_CODE IN NUMBER := FND_API.G_MISS_NUM,
126 p_ITEM_SERIAL_CONTROL_CODE IN NUMBER := FND_API.G_MISS_NUM,
127 p_ITEM_INVENTORY_ASSET_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
128 p_ALLOWED_UNITS_LOOKUP_CODE IN NUMBER := FND_API.G_MISS_NUM,
129 p_DEPARTMENT_ID IN NUMBER := FND_API.G_MISS_NUM,
130 p_DEPARTMENT_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
131 p_WIP_SUPPLY_TYPE IN NUMBER := FND_API.G_MISS_NUM,
132 p_SUPPLY_SUBINVENTORY IN VARCHAR2 := FND_API.G_MISS_CHAR,
133 p_SUPPLY_LOCATOR_ID IN NUMBER := FND_API.G_MISS_NUM,
134 p_VALID_SUBINVENTORY_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
135 p_VALID_LOCATOR_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
136 p_LOCATOR_SEGMENTS IN VARCHAR2 := FND_API.G_MISS_CHAR,
137 p_CURRENT_LOCATOR_CONTROL_CODE IN NUMBER := FND_API.G_MISS_NUM,
138 p_NUMBER_OF_LOTS_ENTERED IN NUMBER := FND_API.G_MISS_NUM,
139 p_WIP_COMMIT_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
140 p_NEXT_LOT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
141 p_LOT_ALPHA_PREFIX IN VARCHAR2 := FND_API.G_MISS_CHAR,
142 p_NEXT_SERIAL_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
143 p_SERIAL_ALPHA_PREFIX IN VARCHAR2 := FND_API.G_MISS_CHAR,
144 p_SHIPPABLE_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
145 p_POSTING_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
146 p_REQUIRED_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
147 p_PROCESS_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
148 p_ERROR_CODE IN VARCHAR2 := FND_API.G_MISS_CHAR,
149 p_ERROR_EXPLANATION IN VARCHAR2 := FND_API.G_MISS_CHAR,
150 p_ATTRIBUTE_CATEGORY IN VARCHAR2 := FND_API.G_MISS_CHAR,
151 p_ATTRIBUTE1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
152 p_ATTRIBUTE2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
153 p_ATTRIBUTE3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
154 p_ATTRIBUTE4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
155 p_ATTRIBUTE5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
156 p_ATTRIBUTE6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
157 p_ATTRIBUTE7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
158 p_ATTRIBUTE8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
159 p_ATTRIBUTE9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
160 p_ATTRIBUTE10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
161 p_ATTRIBUTE11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
162 p_ATTRIBUTE12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
163 p_ATTRIBUTE13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
164 p_ATTRIBUTE14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
165 p_ATTRIBUTE15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
166 p_MOVEMENT_ID IN NUMBER := FND_API.G_MISS_NUM,
167 p_RESERVATION_QUANTITY IN NUMBER := FND_API.G_MISS_NUM,
168 p_SHIPPED_QUANTITY IN NUMBER := FND_API.G_MISS_NUM,
169 p_TRANSACTION_LINE_NUMBER IN NUMBER := FND_API.G_MISS_NUM,
170 p_TASK_ID IN NUMBER := FND_API.G_MISS_NUM,
171 p_TO_TASK_ID IN NUMBER := FND_API.G_MISS_NUM,
172 p_SOURCE_TASK_ID IN NUMBER := FND_API.G_MISS_NUM,
173 p_PROJECT_ID IN NUMBER := FND_API.G_MISS_NUM,
174 p_SOURCE_PROJECT_ID IN NUMBER := FND_API.G_MISS_NUM,
175 p_PA_EXPENDITURE_ORG_ID IN NUMBER := FND_API.G_MISS_NUM,
176 p_TO_PROJECT_ID IN NUMBER := FND_API.G_MISS_NUM,
177 p_EXPENDITURE_TYPE IN VARCHAR2 := FND_API.G_MISS_CHAR,
178 p_FINAL_COMPLETION_FLAG IN VARCHAR2 := FND_API.G_MISS_CHAR,
179 p_TRANSFER_PERCENTAGE IN NUMBER := FND_API.G_MISS_NUM,
180 p_TRANSACTION_SEQUENCE_ID IN NUMBER := FND_API.G_MISS_NUM,
181 p_MATERIAL_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
182 p_MATERIAL_OVERHEAD_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
183 p_RESOURCE_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
184 p_OUTSIDE_PROCESSING_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
185 p_OVERHEAD_ACCOUNT IN NUMBER := FND_API.G_MISS_NUM,
186 p_FLOW_SCHEDULE IN VARCHAR2 := FND_API.G_MISS_CHAR,
187 p_COST_GROUP_ID IN NUMBER := FND_API.G_MISS_NUM,
188 p_DEMAND_CLASS IN VARCHAR2 := FND_API.G_MISS_CHAR,
189 p_QA_COLLECTION_ID IN NUMBER := FND_API.G_MISS_NUM,
190 p_KANBAN_CARD_ID IN NUMBER := FND_API.G_MISS_NUM,
191 p_OVERCOMPLETION_TXN_ID IN NUMBER := FND_API.G_MISS_NUM,
192 p_OVERCOMPLETION_PRIMARY_QTY IN NUMBER := FND_API.G_MISS_NUM,
193 p_OVERCOMPLETION_TXN_QTY IN NUMBER := FND_API.G_MISS_NUM,
194 --p_PROCESS_TYPE IN NUMBER := FND_API.G_MISS_NUM,
195 p_END_ITEM_UNIT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
196 p_SCHEDULED_PAYBACK_DATE IN DATE := FND_API.G_MISS_DATE,
197 p_LINE_TYPE_CODE IN NUMBER := FND_API.G_MISS_NUM,
198 p_PARENT_TRANSACTION_TEMP_ID IN NUMBER := FND_API.G_MISS_NUM,
199 p_PUT_AWAY_STRATEGY_ID IN NUMBER := FND_API.G_MISS_NUM,
200 p_PUT_AWAY_RULE_ID IN NUMBER := FND_API.G_MISS_NUM,
201 p_PICK_STRATEGY_ID IN NUMBER := FND_API.G_MISS_NUM,
202 p_PICK_RULE_ID IN NUMBER := FND_API.G_MISS_NUM,
203 p_COMMON_BOM_SEQ_ID IN NUMBER := FND_API.G_MISS_NUM,
204 p_COMMON_ROUTING_SEQ_ID IN NUMBER := FND_API.G_MISS_NUM,
205 p_COST_TYPE_ID IN NUMBER := FND_API.G_MISS_NUM,
206 p_ORG_COST_GROUP_ID IN NUMBER := FND_API.G_MISS_NUM,
207 p_MOVE_ORDER_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
208 p_TASK_GROUP_ID IN NUMBER := FND_API.G_MISS_NUM,
209 p_PICK_SLIP_NUMBER IN NUMBER := FND_API.G_MISS_NUM,
210 p_RESERVATION_ID IN NUMBER := FND_API.G_MISS_NUM,
211 p_TRANSACTION_STATUS IN NUMBER := FND_API.G_MISS_NUM,
212 P_STANDARD_OPERATION_ID IN NUMBER := FND_API.G_MISS_NUM,
213 P_TASK_PRIORITY IN NUMBER := FND_API.G_MISS_NUM,
214 P_WMS_TASK_TYPE IN NUMBER := FND_API.G_MISS_NUM,
215 P_PARENT_LINE_ID IN NUMBER := FND_API.G_MISS_NUM,
216 -- P_SOURCE_LOT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
217 X_Return_Status OUT NOCOPY VARCHAR2,
218 X_Msg_Count OUT NOCOPY NUMBER,
219 X_Msg_Data OUT NOCOPY VARCHAR2
220 )
221 IS
222 l_api_version_number CONSTANT NUMBER := 1.0;
223 l_api_name CONSTANT VARCHAR2(20) := 'Validate_And_Write';
224 l_msg_data VARCHAR2(300);
225 EXCP_USER_DEFINED EXCEPTION;
226 l_check_existence NUMBER := 0;
227 l_return_status VARCHAR2(1);
228 l_msg_count NUMBER := 0;
229 l_pick_list_header_id NUMBER;
230 l_commit VARCHAR2(1) := FND_API.G_FALSE;
231 l_creation_date DATE := p_creation_date;
232 l_last_update_date DATE := p_last_update_date;
233 l_csp_mtltxn_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
234
235 -- for inserting data, the validation_level should be none
236 -- because we do not want to call core apps standard validations.
237 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
238 l_transaction_temp_id NUMBER;
239
240
241 BEGIN
242 SAVEPOINT Validate_And_Write_PUB;
243 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
244 -- initialize message list
245 FND_MSG_PUB.initialize;
246 END IF;
247
248 -- Standard call to check for call compatibility.
249 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
250 p_api_version_number,
251 l_api_name,
252 G_PKG_NAME)
253 THEN
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255 END IF;
256
257 -- validate p_organization_id
258 IF p_organization_id IS NULL THEN
259 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
260 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
261 FND_MSG_PUB.ADD;
262 RAISE EXCP_USER_DEFINED;
263 ELSE
264 BEGIN
265 select organization_id into l_check_existence
266 from mtl_parameters
270 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
267 where organization_id = p_organization_id;
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
271 FND_MSG_PUB.ADD;
272 RAISE EXCP_USER_DEFINED;
273 WHEN OTHERS THEN
274 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
275 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
276 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
277 fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
278 FND_MSG_PUB.ADD;
279 RAISE EXCP_USER_DEFINED;
280 END;
281 END IF;
282
283 IF p_action_code NOT IN (0, 1, 2) OR p_action_code IS NULL THEN
284 fnd_message.set_name ('INV', 'INV-INVALID ACTION');
285 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
286 fnd_msg_pub.add;
287 RAISE EXCP_USER_DEFINED;
288 END IF;
289
290 IF p_action_code = 0 AND px_transaction_temp_id IS NOT NULL THEN
291
292 -- check whehter the px_transaction_temp_id already exists. If yes, raise an exception.
293 BEGIN
294 SELECT transaction_temp_id into l_check_existence
295 FROM mtl_material_transactions_temp
296 WHERE transaction_temp_id = px_transaction_temp_id
297 AND organization_id = p_organization_id;
298
299 fnd_message.set_name ('CSP', 'CSP_DUPLICATE_RECORD');
300 fnd_msg_pub.add;
301 RAISE EXCP_USER_DEFINED;
302
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305 NULL;
306 END;
307 ELSIF p_action_code IN (1, 2) THEN
308 IF px_transaction_temp_id IS NULL THEN
309 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
310 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'px_transaction_temp_id', TRUE);
311 FND_MSG_PUB.ADD;
312 RAISE EXCP_USER_DEFINED;
313 ELSE
314 BEGIN
315 select transaction_temp_id into l_check_existence
316 from mtl_material_transactions_temp
317 where organization_id = p_organization_id
318 and transaction_temp_id = px_transaction_temp_id;
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321 fnd_message.set_name ('CSP', 'CSP_INVALID_TEMP_ID');
322 fnd_message.set_token ('ID', to_char(px_transaction_temp_id), TRUE);
323 fnd_msg_pub.add;
324 RAISE EXCP_USER_DEFINED;
325 WHEN EXCP_USER_DEFINED THEN
326 RAISE EXCP_USER_DEFINED;
327 WHEN OTHERS THEN
328 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
329 fnd_message.set_token('ERR_FIELD', 'px_transaction_temp_id', TRUE);
330 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
331 fnd_message.set_token('TABLE', 'mtl_material_transactions_temp', TRUE);
332 FND_MSG_PUB.ADD;
333 RAISE EXCP_USER_DEFINED;
334 END;
335 END IF;
336 ELSE NULL;
337 END IF;
338
339 --validating inventory_item_id
340 IF p_inventory_item_id IS NULL THEN
341 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
342 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_inventory_item_id', TRUE);
343 FND_MSG_PUB.ADD;
344 RAISE EXCP_USER_DEFINED;
345 ELSE
346 BEGIN
347 -- validate whether the inventory_item_is exists in the given oranization_id
348 select inventory_item_id into l_check_existence
349 from mtl_system_items_kfv
350 where inventory_item_id = p_inventory_item_id
351 and organization_id = p_organization_id;
352 EXCEPTION
353 WHEN NO_DATA_FOUND THEN
354 fnd_message.set_name ('INV', 'INV-NO ITEM RECORD');
355 fnd_msg_pub.add;
356 RAISE EXCP_USER_DEFINED;
357 WHEN OTHERS THEN
358 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
359 fnd_message.set_token('ERR_FIELD', 'p_inventory_item_id', TRUE);
360 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
361 fnd_message.set_token('TABLE', 'mtl_system_items', TRUE);
362 fnd_msg_pub.add;
363 RAISE EXCP_USER_DEFINED;
364 END;
365 END IF;
366
367 IF l_creation_date IS NULL THEN
368 l_creation_date := sysdate;
369 END IF;
370
371 IF l_last_update_date IS NULL THEN
372 l_last_update_date := sysdate;
373 END IF;
374
375 -- construct the record to call the procedures in csp_material_transactions_pvt.
376 l_csp_mtltxn_rec.TRANSACTION_HEADER_ID := p_TRANSACTION_HEADER_ID;
377 l_csp_mtltxn_rec.TRANSACTION_TEMP_ID := px_TRANSACTION_TEMP_ID;
381 l_csp_mtltxn_rec.LOCK_FLAG := p_LOCK_FLAG;
378 l_csp_mtltxn_rec.SOURCE_CODE := p_SOURCE_CODE;
379 l_csp_mtltxn_rec.SOURCE_LINE_ID := p_SOURCE_LINE_ID;
380 l_csp_mtltxn_rec.TRANSACTION_MODE := p_TRANSACTION_MODE;
382 l_csp_mtltxn_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE;
383 l_csp_mtltxn_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY;
384 l_csp_mtltxn_rec.CREATION_DATE := p_CREATION_DATE;
385 l_csp_mtltxn_rec.CREATED_BY := p_CREATED_BY;
386 l_csp_mtltxn_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN;
387 l_csp_mtltxn_rec.REQUEST_ID := p_REQUEST_ID;
388 l_csp_mtltxn_rec.PROGRAM_APPLICATION_ID := p_PROGRAM_APPLICATION_ID;
389 l_csp_mtltxn_rec.PROGRAM_ID := p_PROGRAM_ID;
390 l_csp_mtltxn_rec.PROGRAM_UPDATE_DATE := p_PROGRAM_UPDATE_DATE;
391 l_csp_mtltxn_rec.INVENTORY_ITEM_ID := p_INVENTORY_ITEM_ID;
392 l_csp_mtltxn_rec.REVISION := p_REVISION;
393 l_csp_mtltxn_rec.ORGANIZATION_ID := p_ORGANIZATION_ID;
394 l_csp_mtltxn_rec.SUBINVENTORY_CODE := p_SUBINVENTORY_CODE;
395 l_csp_mtltxn_rec.LOCATOR_ID := p_LOCATOR_ID;
396 l_csp_mtltxn_rec.TRANSACTION_QUANTITY := p_TRANSACTION_QUANTITY;
397 l_csp_mtltxn_rec.PRIMARY_QUANTITY := p_PRIMARY_QUANTITY;
398 l_csp_mtltxn_rec.TRANSACTION_UOM := p_TRANSACTION_UOM;
399 l_csp_mtltxn_rec.TRANSACTION_COST := p_TRANSACTION_COST;
400 l_csp_mtltxn_rec.TRANSACTION_TYPE_ID := p_TRANSACTION_TYPE_ID;
401 l_csp_mtltxn_rec.TRANSACTION_ACTION_ID := p_TRANSACTION_ACTION_ID;
402 l_csp_mtltxn_rec.TRANSACTION_SOURCE_TYPE_ID := p_TRANSACTION_SOURCE_TYPE_ID;
403 l_csp_mtltxn_rec.TRANSACTION_SOURCE_ID := p_TRANSACTION_SOURCE_ID;
404 l_csp_mtltxn_rec.TRANSACTION_SOURCE_NAME := p_TRANSACTION_SOURCE_NAME;
405 l_csp_mtltxn_rec.TRANSACTION_DATE := p_TRANSACTION_DATE;
406 l_csp_mtltxn_rec.ACCT_PERIOD_ID := p_ACCT_PERIOD_ID;
407 l_csp_mtltxn_rec.DISTRIBUTION_ACCOUNT_ID := p_DISTRIBUTION_ACCOUNT_ID;
408 l_csp_mtltxn_rec.TRANSACTION_REFERENCE := p_TRANSACTION_REFERENCE;
409 l_csp_mtltxn_rec.REQUISITION_LINE_ID := p_REQUISITION_LINE_ID;
410 l_csp_mtltxn_rec.REQUISITION_DISTRIBUTION_ID := p_REQUISITION_DISTRIBUTION_ID;
411 l_csp_mtltxn_rec.REASON_ID := p_REASON_ID;
412 l_csp_mtltxn_rec.LOT_NUMBER := p_LOT_NUMBER;
413 l_csp_mtltxn_rec.LOT_EXPIRATION_DATE := p_LOT_EXPIRATION_DATE;
414 l_csp_mtltxn_rec.SERIAL_NUMBER := p_SERIAL_NUMBER;
415 l_csp_mtltxn_rec.RECEIVING_DOCUMENT := p_RECEIVING_DOCUMENT;
416 l_csp_mtltxn_rec.DEMAND_ID := p_DEMAND_ID;
417 l_csp_mtltxn_rec.RCV_TRANSACTION_ID := p_RCV_TRANSACTION_ID;
418 l_csp_mtltxn_rec.MOVE_TRANSACTION_ID := p_MOVE_TRANSACTION_ID;
419 l_csp_mtltxn_rec.COMPLETION_TRANSACTION_ID := p_COMPLETION_TRANSACTION_ID;
420 l_csp_mtltxn_rec.WIP_ENTITY_TYPE := p_WIP_ENTITY_TYPE;
421 l_csp_mtltxn_rec.SCHEDULE_ID := p_SCHEDULE_ID;
422 l_csp_mtltxn_rec.REPETITIVE_LINE_ID := p_REPETITIVE_LINE_ID;
423 l_csp_mtltxn_rec.EMPLOYEE_CODE := p_EMPLOYEE_CODE;
424 l_csp_mtltxn_rec.PRIMARY_SWITCH := p_PRIMARY_SWITCH;
425 l_csp_mtltxn_rec.SCHEDULE_UPDATE_CODE := p_SCHEDULE_UPDATE_CODE;
426 l_csp_mtltxn_rec.SETUP_TEARDOWN_CODE := p_SETUP_TEARDOWN_CODE;
427 l_csp_mtltxn_rec.ITEM_ORDERING := p_ITEM_ORDERING;
428 l_csp_mtltxn_rec.NEGATIVE_REQ_FLAG := p_NEGATIVE_REQ_FLAG;
429 l_csp_mtltxn_rec.OPERATION_SEQ_NUM := p_OPERATION_SEQ_NUM;
430 l_csp_mtltxn_rec.PICKING_LINE_ID := p_PICKING_LINE_ID;
431 l_csp_mtltxn_rec.TRX_SOURCE_LINE_ID := p_TRX_SOURCE_LINE_ID;
432 l_csp_mtltxn_rec.TRX_SOURCE_DELIVERY_ID := p_TRX_SOURCE_DELIVERY_ID;
433 l_csp_mtltxn_rec.PHYSICAL_ADJUSTMENT_ID := p_PHYSICAL_ADJUSTMENT_ID;
434 l_csp_mtltxn_rec.CYCLE_COUNT_ID := p_CYCLE_COUNT_ID;
435 l_csp_mtltxn_rec.RMA_LINE_ID := p_RMA_LINE_ID;
436 l_csp_mtltxn_rec.CUSTOMER_SHIP_ID := p_CUSTOMER_SHIP_ID;
437 l_csp_mtltxn_rec.CURRENCY_CODE := p_CURRENCY_CODE;
438 l_csp_mtltxn_rec.CURRENCY_CONVERSION_RATE := p_CURRENCY_CONVERSION_RATE;
439 l_csp_mtltxn_rec.CURRENCY_CONVERSION_TYPE := p_CURRENCY_CONVERSION_TYPE;
440 l_csp_mtltxn_rec.CURRENCY_CONVERSION_DATE := p_CURRENCY_CONVERSION_DATE;
441 l_csp_mtltxn_rec.USSGL_TRANSACTION_CODE := p_USSGL_TRANSACTION_CODE;
442 l_csp_mtltxn_rec.VENDOR_LOT_NUMBER := p_VENDOR_LOT_NUMBER;
443 l_csp_mtltxn_rec.ENCUMBRANCE_ACCOUNT := p_ENCUMBRANCE_ACCOUNT;
444 l_csp_mtltxn_rec.ENCUMBRANCE_AMOUNT := p_ENCUMBRANCE_AMOUNT;
445 l_csp_mtltxn_rec.SHIP_TO_LOCATION := p_SHIP_TO_LOCATION;
446 l_csp_mtltxn_rec.SHIPMENT_NUMBER := p_SHIPMENT_NUMBER;
447 l_csp_mtltxn_rec.TRANSFER_COST := p_TRANSFER_COST;
448 l_csp_mtltxn_rec.TRANSPORTATION_COST := p_TRANSPORTATION_COST;
449 l_csp_mtltxn_rec.TRANSPORTATION_ACCOUNT := p_TRANSPORTATION_ACCOUNT;
450 l_csp_mtltxn_rec.FREIGHT_CODE := p_FREIGHT_CODE;
451 l_csp_mtltxn_rec.CONTAINERS := p_CONTAINERS;
452 l_csp_mtltxn_rec.WAYBILL_AIRBILL := p_WAYBILL_AIRBILL;
453 l_csp_mtltxn_rec.EXPECTED_ARRIVAL_DATE := p_EXPECTED_ARRIVAL_DATE;
457 l_csp_mtltxn_rec.NEW_AVERAGE_COST := p_NEW_AVERAGE_COST;
454 l_csp_mtltxn_rec.TRANSFER_SUBINVENTORY := p_TRANSFER_SUBINVENTORY;
455 l_csp_mtltxn_rec.TRANSFER_ORGANIZATION := p_TRANSFER_ORGANIZATION;
456 l_csp_mtltxn_rec.TRANSFER_TO_LOCATION := p_TRANSFER_TO_LOCATION;
458 l_csp_mtltxn_rec.VALUE_CHANGE := p_VALUE_CHANGE;
459 l_csp_mtltxn_rec.PERCENTAGE_CHANGE := p_PERCENTAGE_CHANGE;
460 l_csp_mtltxn_rec.MATERIAL_ALLOCATION_TEMP_ID := p_MATERIAL_ALLOCATION_TEMP_ID;
461 l_csp_mtltxn_rec.DEMAND_SOURCE_HEADER_ID := p_DEMAND_SOURCE_HEADER_ID;
462 l_csp_mtltxn_rec.DEMAND_SOURCE_LINE := p_DEMAND_SOURCE_LINE;
463 l_csp_mtltxn_rec.DEMAND_SOURCE_DELIVERY := p_DEMAND_SOURCE_DELIVERY;
464 l_csp_mtltxn_rec.ITEM_SEGMENTS := p_ITEM_SEGMENTS;
465 l_csp_mtltxn_rec.ITEM_DESCRIPTION := p_ITEM_DESCRIPTION;
466 l_csp_mtltxn_rec.ITEM_TRX_ENABLED_FLAG := p_ITEM_TRX_ENABLED_FLAG;
467 l_csp_mtltxn_rec.ITEM_LOCATION_CONTROL_CODE := p_ITEM_LOCATION_CONTROL_CODE;
468 l_csp_mtltxn_rec.ITEM_RESTRICT_SUBINV_CODE := p_ITEM_RESTRICT_SUBINV_CODE;
469 l_csp_mtltxn_rec.ITEM_RESTRICT_LOCATORS_CODE := p_ITEM_RESTRICT_LOCATORS_CODE;
470 l_csp_mtltxn_rec.ITEM_REVISION_QTY_CONTROL_CODE := p_ITEM_REV_QTY_CONTROL_CODE;
471 l_csp_mtltxn_rec.ITEM_PRIMARY_UOM_CODE := p_ITEM_PRIMARY_UOM_CODE;
472 l_csp_mtltxn_rec.ITEM_UOM_CLASS := p_ITEM_UOM_CLASS;
473 l_csp_mtltxn_rec.ITEM_SHELF_LIFE_CODE := p_ITEM_SHELF_LIFE_CODE;
474 l_csp_mtltxn_rec.ITEM_SHELF_LIFE_DAYS := p_ITEM_SHELF_LIFE_DAYS;
475 l_csp_mtltxn_rec.ITEM_LOT_CONTROL_CODE := p_ITEM_LOT_CONTROL_CODE;
476 l_csp_mtltxn_rec.ITEM_SERIAL_CONTROL_CODE := p_ITEM_SERIAL_CONTROL_CODE;
477 l_csp_mtltxn_rec.ITEM_INVENTORY_ASSET_FLAG := p_ITEM_INVENTORY_ASSET_FLAG;
478 l_csp_mtltxn_rec.ALLOWED_UNITS_LOOKUP_CODE := p_ALLOWED_UNITS_LOOKUP_CODE;
479 l_csp_mtltxn_rec.DEPARTMENT_ID := p_DEPARTMENT_ID;
480 l_csp_mtltxn_rec.DEPARTMENT_CODE := p_DEPARTMENT_CODE;
481 l_csp_mtltxn_rec.WIP_SUPPLY_TYPE := p_WIP_SUPPLY_TYPE;
482 l_csp_mtltxn_rec.SUPPLY_SUBINVENTORY := p_SUPPLY_SUBINVENTORY;
483 l_csp_mtltxn_rec.SUPPLY_LOCATOR_ID := p_SUPPLY_LOCATOR_ID;
484 l_csp_mtltxn_rec.VALID_SUBINVENTORY_FLAG := p_VALID_SUBINVENTORY_FLAG;
485 l_csp_mtltxn_rec.VALID_LOCATOR_FLAG := p_VALID_LOCATOR_FLAG;
486 l_csp_mtltxn_rec.LOCATOR_SEGMENTS := p_LOCATOR_SEGMENTS;
487 l_csp_mtltxn_rec.CURRENT_LOCATOR_CONTROL_CODE := p_CURRENT_LOCATOR_CONTROL_CODE;
488 l_csp_mtltxn_rec.NUMBER_OF_LOTS_ENTERED := p_NUMBER_OF_LOTS_ENTERED;
489 l_csp_mtltxn_rec.WIP_COMMIT_FLAG := p_WIP_COMMIT_FLAG;
490 l_csp_mtltxn_rec.NEXT_LOT_NUMBER := p_NEXT_LOT_NUMBER;
491 l_csp_mtltxn_rec.LOT_ALPHA_PREFIX := p_LOT_ALPHA_PREFIX;
492 l_csp_mtltxn_rec.NEXT_SERIAL_NUMBER := p_NEXT_SERIAL_NUMBER;
493 l_csp_mtltxn_rec.SERIAL_ALPHA_PREFIX := p_SERIAL_ALPHA_PREFIX;
494 l_csp_mtltxn_rec.SHIPPABLE_FLAG := p_SHIPPABLE_FLAG;
495 l_csp_mtltxn_rec.POSTING_FLAG := p_POSTING_FLAG;
496 l_csp_mtltxn_rec.REQUIRED_FLAG := p_REQUIRED_FLAG;
497 l_csp_mtltxn_rec.PROCESS_FLAG := p_PROCESS_FLAG;
498 l_csp_mtltxn_rec.ERROR_CODE := p_ERROR_CODE;
499 l_csp_mtltxn_rec.ERROR_EXPLANATION := p_ERROR_EXPLANATION;
500 l_csp_mtltxn_rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
501 l_csp_mtltxn_rec.ATTRIBUTE1 := p_ATTRIBUTE1;
502 l_csp_mtltxn_rec.ATTRIBUTE2 := p_ATTRIBUTE2;
503 l_csp_mtltxn_rec.ATTRIBUTE3 := p_ATTRIBUTE3;
504 l_csp_mtltxn_rec.ATTRIBUTE4 := p_ATTRIBUTE4;
505 l_csp_mtltxn_rec.ATTRIBUTE5 := p_ATTRIBUTE5;
506 l_csp_mtltxn_rec.ATTRIBUTE6 := p_ATTRIBUTE6;
507 l_csp_mtltxn_rec.ATTRIBUTE7 := p_ATTRIBUTE7;
508 l_csp_mtltxn_rec.ATTRIBUTE8 := p_ATTRIBUTE8;
509 l_csp_mtltxn_rec.ATTRIBUTE9 := p_ATTRIBUTE9;
510 l_csp_mtltxn_rec.ATTRIBUTE10 := p_ATTRIBUTE10;
511 l_csp_mtltxn_rec.ATTRIBUTE11 := p_ATTRIBUTE11;
512 l_csp_mtltxn_rec.ATTRIBUTE12 := p_ATTRIBUTE12;
513 l_csp_mtltxn_rec.ATTRIBUTE13 := p_ATTRIBUTE13;
514 l_csp_mtltxn_rec.ATTRIBUTE14 := p_ATTRIBUTE14;
515 l_csp_mtltxn_rec.ATTRIBUTE15 := p_ATTRIBUTE15;
516 l_csp_mtltxn_rec.MOVEMENT_ID := p_MOVEMENT_ID;
517 l_csp_mtltxn_rec.RESERVATION_QUANTITY := p_RESERVATION_QUANTITY;
518 l_csp_mtltxn_rec.SHIPPED_QUANTITY := p_SHIPPED_QUANTITY;
519 l_csp_mtltxn_rec.TRANSACTION_LINE_NUMBER := p_TRANSACTION_LINE_NUMBER;
520 l_csp_mtltxn_rec.TASK_ID := p_TASK_ID;
521 l_csp_mtltxn_rec.TO_TASK_ID := p_TO_TASK_ID;
522 l_csp_mtltxn_rec.SOURCE_TASK_ID := p_SOURCE_TASK_ID;
523 l_csp_mtltxn_rec.PROJECT_ID := p_PROJECT_ID;
524 l_csp_mtltxn_rec.SOURCE_PROJECT_ID := p_SOURCE_PROJECT_ID;
525 l_csp_mtltxn_rec.PA_EXPENDITURE_ORG_ID := p_PA_EXPENDITURE_ORG_ID;
526 l_csp_mtltxn_rec.TO_PROJECT_ID := p_TO_PROJECT_ID;
527 l_csp_mtltxn_rec.EXPENDITURE_TYPE := p_EXPENDITURE_TYPE;
528 l_csp_mtltxn_rec.FINAL_COMPLETION_FLAG := p_FINAL_COMPLETION_FLAG;
529 l_csp_mtltxn_rec.TRANSFER_PERCENTAGE := p_TRANSFER_PERCENTAGE;
530 l_csp_mtltxn_rec.TRANSACTION_SEQUENCE_ID := p_TRANSACTION_SEQUENCE_ID;
534 l_csp_mtltxn_rec.OUTSIDE_PROCESSING_ACCOUNT := p_OUTSIDE_PROCESSING_ACCOUNT;
531 l_csp_mtltxn_rec.MATERIAL_ACCOUNT := p_MATERIAL_ACCOUNT;
532 l_csp_mtltxn_rec.MATERIAL_OVERHEAD_ACCOUNT := p_MATERIAL_OVERHEAD_ACCOUNT;
533 l_csp_mtltxn_rec.RESOURCE_ACCOUNT := p_RESOURCE_ACCOUNT;
535 l_csp_mtltxn_rec.OVERHEAD_ACCOUNT := p_OVERHEAD_ACCOUNT;
536 l_csp_mtltxn_rec.FLOW_SCHEDULE := p_FLOW_SCHEDULE;
537 l_csp_mtltxn_rec.COST_GROUP_ID := p_COST_GROUP_ID;
538 l_csp_mtltxn_rec.DEMAND_CLASS := p_DEMAND_CLASS;
539 l_csp_mtltxn_rec.QA_COLLECTION_ID := p_QA_COLLECTION_ID;
540 l_csp_mtltxn_rec.KANBAN_CARD_ID := p_KANBAN_CARD_ID;
541 l_csp_mtltxn_rec.OVERCOMPLETION_TRANSACTION_ID := p_OVERCOMPLETION_TXN_ID;
542 l_csp_mtltxn_rec.OVERCOMPLETION_PRIMARY_QTY := p_OVERCOMPLETION_PRIMARY_QTY;
543 l_csp_mtltxn_rec.OVERCOMPLETION_TRANSACTION_QTY := p_OVERCOMPLETION_TXN_QTY;
544 --l_csp_mtltxn_rec.PROCESS_TYPE := p_PROCESS_TYPE;
545 l_csp_mtltxn_rec.END_ITEM_UNIT_NUMBER := p_END_ITEM_UNIT_NUMBER;
546 l_csp_mtltxn_rec.SCHEDULED_PAYBACK_DATE := p_SCHEDULED_PAYBACK_DATE;
547 l_csp_mtltxn_rec.LINE_TYPE_CODE := p_LINE_TYPE_CODE;
548 l_csp_mtltxn_rec.PARENT_TRANSACTION_TEMP_ID := p_PARENT_TRANSACTION_TEMP_ID;
549 l_csp_mtltxn_rec.PUT_AWAY_STRATEGY_ID := p_PUT_AWAY_STRATEGY_ID;
550 l_csp_mtltxn_rec.PUT_AWAY_RULE_ID := p_PUT_AWAY_RULE_ID;
551 l_csp_mtltxn_rec.PICK_STRATEGY_ID := p_PICK_STRATEGY_ID;
552 l_csp_mtltxn_rec.PICK_RULE_ID := p_PICK_RULE_ID;
553 l_csp_mtltxn_rec.COMMON_BOM_SEQ_ID := p_COMMON_BOM_SEQ_ID;
554 l_csp_mtltxn_rec.COMMON_ROUTING_SEQ_ID := p_COMMON_ROUTING_SEQ_ID;
555 l_csp_mtltxn_rec.COST_TYPE_ID := p_COST_TYPE_ID;
556 l_csp_mtltxn_rec.ORG_COST_GROUP_ID := p_ORG_COST_GROUP_ID;
557 l_csp_mtltxn_rec.MOVE_ORDER_LINE_ID := p_MOVE_ORDER_LINE_ID;
558 l_csp_mtltxn_rec.TASK_GROUP_ID := p_TASK_GROUP_ID;
559 l_csp_mtltxn_rec.PICK_SLIP_NUMBER := p_PICK_SLIP_NUMBER;
560 l_csp_mtltxn_rec.RESERVATION_ID := p_RESERVATION_ID;
561 l_csp_mtltxn_rec.TRANSACTION_STATUS := p_TRANSACTION_STATUS;
562 l_csp_mtltxn_rec.STANDARD_OPERATION_ID := p_STANDARD_OPERATION_ID;
563 l_csp_mtltxn_rec.TASK_PRIORITY := p_TASK_PRIORITY;
564 l_csp_mtltxn_rec.WMS_TASK_TYPE := p_WMS_TASK_TYPE;
565 l_csp_mtltxn_rec.PARENT_LINE_ID := p_PARENT_LINE_ID;
566 --l_csp_mtltxn_rec.SOURCE_LOT_NUMBER := p_SOURCE_LOT_NUMBER;
567
568 IF p_action_code = 0 THEN
569 -- call the create_material
570 CSP_Material_Transactions_PVT.Create_material_transactions(
571 P_Api_Version_Number => p_api_version_number,
572 P_Init_Msg_List => p_init_msg_list,
573 P_Commit => l_commit,
574 p_validation_level => p_validation_level,
575 P_CSP_Rec => l_csp_mtltxn_rec,
576 X_TRANSACTION_TEMP_ID => l_transaction_temp_id,
577 X_Return_Status => l_return_status,
578 X_Msg_Count => l_msg_count,
579 X_Msg_Data => l_msg_data
580 );
581 ELSIF p_action_code = 1 THEN
582 -- call the update_material
583 CSP_Material_Transactions_PVT.Update_material_transactions(
584 P_Api_Version_Number => p_api_version_number,
585 P_Init_Msg_List => p_init_msg_list,
586 P_Commit => l_commit,
587 p_validation_level => p_validation_level,
588 P_CSP_Rec => l_csp_mtltxn_rec,
589 X_Return_Status => l_return_status,
590 X_Msg_Count => l_msg_count,
591 X_Msg_Data => l_msg_data
592 );
593 ELSE
594 -- call the delete_material
595 CSP_Material_Transactions_PVT.Delete_material_transactions(
596 P_Api_Version_Number => p_api_version_number,
597 P_Init_Msg_List => p_init_msg_list,
598 P_Commit => l_commit,
599 p_validation_level => p_validation_level,
600 P_CSP_Rec => l_csp_mtltxn_rec,
601 X_Return_Status => l_return_status,
602 X_Msg_Count => l_msg_count,
603 X_Msg_Data => l_msg_data
604 );
605 END IF;
606
607 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
608 RAISE FND_API.G_EXC_ERROR;
609 ELSE
610 x_return_status := FND_API.G_RET_STS_SUCCESS;
611 IF fnd_api.to_boolean(p_commit) THEN
612 commit work;
613 END IF;
614 END IF;
615
616
617 EXCEPTION
618 WHEN EXCP_USER_DEFINED THEN
619 Rollback to Validate_And_Write_PUB;
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 fnd_msg_pub.count_and_get
622 ( p_count => x_msg_count
623 , p_data => x_msg_data);
624
625 WHEN FND_API.G_EXC_ERROR THEN
626 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
627 P_API_NAME => L_API_NAME
628 ,P_PKG_NAME => G_PKG_NAME
632 ,X_MSG_DATA => X_MSG_DATA
629 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
630 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
631 ,X_MSG_COUNT => X_MSG_COUNT
633 ,X_RETURN_STATUS => X_RETURN_STATUS);
634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
635 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
636 P_API_NAME => L_API_NAME
637 ,P_PKG_NAME => G_PKG_NAME
638 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
639 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
640 ,X_MSG_COUNT => X_MSG_COUNT
641 ,X_MSG_DATA => X_MSG_DATA
642 ,X_RETURN_STATUS => X_RETURN_STATUS);
643 WHEN OTHERS THEN
644 Rollback to Validate_And_Write_PUB;
645 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
646 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
647 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
648 fnd_msg_pub.add;
649 fnd_msg_pub.count_and_get
650 ( p_count => x_msg_count
651 , p_data => x_msg_data);
652 x_return_status := fnd_api.g_ret_sts_error;
653
654 END Validate_and_Write;
655
656 PROCEDURE CSP_MO_LINES_MANUAL_RECEIPT (
657 -- Start of Comments
658 -- Procedure name : CSP_MO_LINES_MANUAL_RECEIPT
659 -- Purpose : A procedure to perform move_order_line transaction for move orders that are manual receipts.
660 -- Usage : This procedure only processes move orders which are not Autorecipt.
661 -- History :
662 -- 28-Dev-99, Add function to take care of the subinventory-restricted attribute of the item.
663 -- 27-Dec-99, Vernon Lou.
664 --
665 -- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
666 -- full validation here is unnecessary. To avoid repeating the same validations, you can set the
667 -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
668 -- responsibility to make sure all proper validations have been done before calling this procedure.
669 -- You are recommended to let this procedure handle the validations if you are not sure.
670 -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
671 -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
672 -- to FND_API.G_VALID_LEVEL_FULL when making the call.
673 -- End of Comments
674
675 P_Api_Version_Number IN NUMBER,
676 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
677 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
678 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
679 p_organization_id IN NUMBER,
680 p_transaction_temp_id IN NUMBER,
681 px_transaction_header_id IN OUT NOCOPY NUMBER,
682 p_process_flag IN VARCHAR2 := FND_API.G_FALSE,
683 X_Return_Status OUT NOCOPY VARCHAR2,
684 X_Msg_Count OUT NOCOPY NUMBER,
685 X_Msg_Data OUT NOCOPY VARCHAR2)
686 IS
687 l_api_version_number CONSTANT NUMBER := 1.0;
688 l_api_name CONSTANT VARCHAR2(50) := 'CSP_MO_LINES_MANUAL';
689 l_msg_data VARCHAR2(300);
690
691 l_check_existence NUMBER := 0;
692 l_return_status VARCHAR2(1);
693 l_msg_count NUMBER := 0;
694 l_commit VARCHAR2(1) := FND_API.G_FALSE;
695 l_creation_date DATE;
696 l_last_update_date DATE;
697 l_csp_mtltxn_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
698 l_csp_mtltxn_new_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
699
700 -- for inserting data, the validation_level should be none
701 -- because we do not want to call core apps standard validations.
702 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
703
704 l_csp_intransit_subinventory VARCHAR2(20);
705 l_profile_option_id NUMBER;
706 l_to_subinventory VARCHAR2(20);
707 l_to_locator NUMBER;
708 l_transaction_temp_id NUMBER := NULL;
709 l_transaction_header_id NUMBER := px_transaction_header_id;
710 EXCP_USER_DEFINED EXCEPTION;
711
712 -- declare a cursor to hold all the records in the mtl_material_transactions_temp table
713 -- that belong to the move_order_line_id.
714 CURSOR c_ml_records IS
715 SELECT TRANSACTION_HEADER_ID ,
716 TRANSACTION_TEMP_ID ,
717 SOURCE_CODE ,
718 SOURCE_LINE_ID ,
719 TRANSACTION_MODE ,
720 LOCK_FLAG ,
721 LAST_UPDATE_DATE ,
722 LAST_UPDATED_BY ,
723 CREATION_DATE ,
724 CREATED_BY ,
725 LAST_UPDATE_LOGIN ,
726 REQUEST_ID ,
727 PROGRAM_APPLICATION_ID ,
728 PROGRAM_ID ,
729 PROGRAM_UPDATE_DATE ,
730 INVENTORY_ITEM_ID ,
731 REVISION ,
732 ORGANIZATION_ID ,
733 SUBINVENTORY_CODE ,
734 LOCATOR_ID ,
738 TRANSACTION_COST ,
735 TRANSACTION_QUANTITY ,
736 PRIMARY_QUANTITY ,
737 TRANSACTION_UOM ,
739 TRANSACTION_TYPE_ID ,
740 TRANSACTION_ACTION_ID ,
741 TRANSACTION_SOURCE_TYPE_ID ,
742 TRANSACTION_SOURCE_ID ,
743 TRANSACTION_SOURCE_NAME ,
744 TRANSACTION_DATE ,
745 ACCT_PERIOD_ID ,
746 DISTRIBUTION_ACCOUNT_ID ,
747 TRANSACTION_REFERENCE ,
748 REQUISITION_LINE_ID ,
749 REQUISITION_DISTRIBUTION_ID ,
750 REASON_ID ,
751 LOT_NUMBER ,
752 LOT_EXPIRATION_DATE ,
753 SERIAL_NUMBER ,
754 RECEIVING_DOCUMENT ,
755 DEMAND_ID ,
756 RCV_TRANSACTION_ID ,
757 MOVE_TRANSACTION_ID ,
758 COMPLETION_TRANSACTION_ID ,
759 WIP_ENTITY_TYPE ,
760 SCHEDULE_ID ,
761 REPETITIVE_LINE_ID ,
762 EMPLOYEE_CODE ,
763 PRIMARY_SWITCH ,
764 SCHEDULE_UPDATE_CODE ,
765 SETUP_TEARDOWN_CODE ,
766 ITEM_ORDERING ,
767 NEGATIVE_REQ_FLAG ,
768 OPERATION_SEQ_NUM ,
769 PICKING_LINE_ID ,
770 TRX_SOURCE_LINE_ID ,
771 TRX_SOURCE_DELIVERY_ID ,
772 PHYSICAL_ADJUSTMENT_ID ,
773 CYCLE_COUNT_ID ,
774 RMA_LINE_ID ,
775 CUSTOMER_SHIP_ID ,
776 CURRENCY_CODE ,
777 CURRENCY_CONVERSION_RATE ,
778 CURRENCY_CONVERSION_TYPE ,
779 CURRENCY_CONVERSION_DATE ,
780 USSGL_TRANSACTION_CODE ,
781 VENDOR_LOT_NUMBER ,
782 ENCUMBRANCE_ACCOUNT ,
783 ENCUMBRANCE_AMOUNT ,
784 SHIP_TO_LOCATION ,
785 SHIPMENT_NUMBER ,
786 TRANSFER_COST ,
787 TRANSPORTATION_COST ,
788 TRANSPORTATION_ACCOUNT ,
789 FREIGHT_CODE ,
790 CONTAINERS ,
791 WAYBILL_AIRBILL ,
792 EXPECTED_ARRIVAL_DATE ,
793 TRANSFER_SUBINVENTORY ,
794 TRANSFER_ORGANIZATION ,
795 TRANSFER_TO_LOCATION ,
796 NEW_AVERAGE_COST ,
797 VALUE_CHANGE ,
798 PERCENTAGE_CHANGE ,
799 MATERIAL_ALLOCATION_TEMP_ID ,
800 DEMAND_SOURCE_HEADER_ID ,
801 DEMAND_SOURCE_LINE ,
802 DEMAND_SOURCE_DELIVERY ,
803 ITEM_SEGMENTS ,
804 ITEM_DESCRIPTION ,
805 ITEM_TRX_ENABLED_FLAG ,
806 ITEM_LOCATION_CONTROL_CODE ,
807 ITEM_RESTRICT_SUBINV_CODE ,
808 ITEM_RESTRICT_LOCATORS_CODE ,
809 ITEM_REVISION_QTY_CONTROL_CODE ,
810 ITEM_PRIMARY_UOM_CODE ,
811 ITEM_UOM_CLASS ,
812 ITEM_SHELF_LIFE_CODE ,
813 ITEM_SHELF_LIFE_DAYS ,
814 ITEM_LOT_CONTROL_CODE ,
815 ITEM_SERIAL_CONTROL_CODE ,
816 ITEM_INVENTORY_ASSET_FLAG ,
817 ALLOWED_UNITS_LOOKUP_CODE ,
818 DEPARTMENT_ID ,
819 DEPARTMENT_CODE ,
820 WIP_SUPPLY_TYPE ,
821 SUPPLY_SUBINVENTORY ,
822 SUPPLY_LOCATOR_ID ,
823 VALID_SUBINVENTORY_FLAG ,
824 VALID_LOCATOR_FLAG ,
825 LOCATOR_SEGMENTS ,
826 CURRENT_LOCATOR_CONTROL_CODE ,
827 NUMBER_OF_LOTS_ENTERED ,
828 WIP_COMMIT_FLAG ,
829 NEXT_LOT_NUMBER ,
830 LOT_ALPHA_PREFIX ,
831 NEXT_SERIAL_NUMBER ,
832 SERIAL_ALPHA_PREFIX ,
833 SHIPPABLE_FLAG ,
834 POSTING_FLAG ,
835 REQUIRED_FLAG ,
836 PROCESS_FLAG ,
837 ERROR_CODE ,
838 ERROR_EXPLANATION ,
839 ATTRIBUTE_CATEGORY ,
840 ATTRIBUTE1 ,
841 ATTRIBUTE2 ,
842 ATTRIBUTE3 ,
843 ATTRIBUTE4 ,
844 ATTRIBUTE5 ,
845 ATTRIBUTE6 ,
846 ATTRIBUTE7 ,
847 ATTRIBUTE8 ,
848 ATTRIBUTE9 ,
849 ATTRIBUTE10 ,
850 ATTRIBUTE11 ,
851 ATTRIBUTE12 ,
852 ATTRIBUTE13 ,
853 ATTRIBUTE14 ,
857 SHIPPED_QUANTITY ,
854 ATTRIBUTE15 ,
855 MOVEMENT_ID ,
856 RESERVATION_QUANTITY ,
858 TRANSACTION_LINE_NUMBER ,
859 TASK_ID ,
860 TO_TASK_ID ,
861 SOURCE_TASK_ID ,
862 PROJECT_ID ,
863 SOURCE_PROJECT_ID ,
864 PA_EXPENDITURE_ORG_ID ,
865 TO_PROJECT_ID ,
866 EXPENDITURE_TYPE ,
867 FINAL_COMPLETION_FLAG ,
868 TRANSFER_PERCENTAGE ,
869 TRANSACTION_SEQUENCE_ID ,
870 MATERIAL_ACCOUNT ,
871 MATERIAL_OVERHEAD_ACCOUNT ,
872 RESOURCE_ACCOUNT ,
873 OUTSIDE_PROCESSING_ACCOUNT ,
874 OVERHEAD_ACCOUNT ,
875 FLOW_SCHEDULE ,
876 COST_GROUP_ID ,
877 DEMAND_CLASS ,
878 QA_COLLECTION_ID ,
879 KANBAN_CARD_ID ,
880 OVERCOMPLETION_TRANSACTION_ID ,
881 OVERCOMPLETION_PRIMARY_QTY ,
882 OVERCOMPLETION_TRANSACTION_QTY ,
883 --PROCESS_TYPE , --removed 01/13/00. process_type does not exist in the mmtt table.
884 END_ITEM_UNIT_NUMBER ,
885 SCHEDULED_PAYBACK_DATE ,
886 LINE_TYPE_CODE ,
887 PARENT_TRANSACTION_TEMP_ID ,
888 PUT_AWAY_STRATEGY_ID ,
889 PUT_AWAY_RULE_ID ,
890 PICK_STRATEGY_ID ,
891 PICK_RULE_ID ,
892 COMMON_BOM_SEQ_ID ,
893 COMMON_ROUTING_SEQ_ID ,
894 COST_TYPE_ID ,
895 ORG_COST_GROUP_ID ,
896 MOVE_ORDER_LINE_ID ,
897 TASK_GROUP_ID ,
898 PICK_SLIP_NUMBER ,
899 RESERVATION_ID ,
900 TRANSACTION_STATUS ,
901 STANDARD_OPERATION_ID ,
902 TASK_PRIORITY ,
903 -- ADDED by phegde 02/23
904 WMS_TASK_TYPE ,
905 PARENT_LINE_ID
906 -- SOURCE_LOT_NUMBER
907 FROM mtl_material_transactions_temp
908 WHERE transaction_temp_id = p_transaction_temp_id
909 AND organization_id = p_organization_id;
910
911 Cursor l_Get_txn_header_id_csr IS
912 SELECT mtl_material_transactions_s.nextval
913 FROM dual;
914
915 l_timeout NUMBER := 5;
916 l_outcome BOOLEAN := TRUE;
917 l_error_code VARCHAR2(200);
918 l_error_explanation VARCHAR2(240);
919
920 BEGIN
921 SAVEPOINT CSP_MO_LINES_MANUAL_PUB;
922
923 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
924 -- initialize message list
925 FND_MSG_PUB.initialize;
926 END IF;
927
928 -- Standard call to check for call compatibility.
929 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
930 p_api_version_number,
931 l_api_name,
932 G_PKG_NAME)
933 THEN
934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
935 END IF;
936
937 IF p_validation_level = fnd_api.g_valid_level_full THEN
938 -- Notes: if validations have been done in the precedence procedure which this one is being from, doing a full
939 -- validation here is not necessary. The users can set the p_validation_level to fnd_api.g_valid_level_none
940 -- if they do not want to repeat the same validations. However, it is their responsibility to make sure
941 -- all proper validations have been done before calling this procedure. It is recommended that they let
942 -- this procedure handle the validations except that they know what they are doing.
943
944 -- validate p_organization_id
945 IF p_organization_id IS NULL THEN
946 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
947 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
948 FND_MSG_PUB.ADD;
949 RAISE EXCP_USER_DEFINED;
950 ELSE
951 BEGIN
952 select organization_id into l_check_existence
953 from mtl_parameters
954 where organization_id = p_organization_id;
955 EXCEPTION
956 WHEN NO_DATA_FOUND THEN
957 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
958 FND_MSG_PUB.ADD;
959 RAISE EXCP_USER_DEFINED;
960 WHEN OTHERS THEN
961 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
962 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
963 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
964 fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
965 FND_MSG_PUB.ADD;
966 RAISE EXCP_USER_DEFINED;
967 END;
968 END IF;
969
973 BEGIN
970 END IF; -- end of validations
971
972 -- retrieve profile_option_id associated with CSP_INTRANSIT_SUBINVENTORY
974 select profile_option_id into l_profile_option_id
975 from fnd_profile_options_vl
976 where profile_option_name = 'CSP_INTRANSIT_SUBINVENTORY';
977
978 -- retrieve the csp intransit subinventory
979 select profile_option_value into l_csp_intransit_subinventory
980 from fnd_profile_option_values
981 where profile_option_id = l_profile_option_id;
982
983 -- check whether the intransit subinventory exists in the organization
984 select organization_id into l_check_existence
985 from mtl_secondary_inventories
986 where secondary_inventory_name = l_csp_intransit_subinventory
987 and organization_id = p_organization_id
988 and nvl(disable_date, sysdate + 1) > sysdate;
989
990 EXCEPTION
991 WHEN NO_DATA_FOUND THEN
992 fnd_message.set_name ('CSP', 'CSP_INVALID_INTRANSIT_SUB');
993 fnd_msg_pub.add;
994 --l_msg_data := 'Validation of intransit subinventory failed. Please make sure a subinventory under the working organizatin is assigned to the CSP_INTRANSIT_SUBINVENTORY profile.';
995 RAISE EXCP_USER_DEFINED;
996 WHEN OTHERS THEN
997 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
998 fnd_message.set_token('ERR_FIELD', 'profile_option_value', TRUE);
999 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1000 fnd_message.set_token('TABLE', 'fnd_profile_option_values', TRUE);
1001 FND_MSG_PUB.ADD;
1002 RAISE EXCP_USER_DEFINED;
1003 END;
1004
1005
1006 ---------------------------------------------------------------------------------------------
1007 -- Stage 1. Insert data into the interface table.
1008 -- 1. Open the cursor c_mtl_records.
1009 -- 2. Fecth the temp data into l_csp_mtltxn_rec.
1010 -- 3. Save the to_subinventory and to_locator to l_to_subinventory and l_to_locator.
1011 -- 4. Update the to_subinventory to l_csp_intransit_subinventory, and to_locator to null.
1012 -- 5. Update the transaction_source_type_id to 13 (inventory),
1013 -- transaction_type_id to 2 (subinventory transfer) and
1014 -- transaction_action_id to 2 (subinventory transfer).
1015 -- 6. Update the process_flag to 'Y'.
1016 -- 7. Call the CSP_Material_Transactions_PVT.Update_material_transactions to update
1017 -- the transfer_subinventory to the instransit subinventory.
1018 -- 8. Call the csp_transactions_pub.transact_temp_record to insert the temp data into the interface table.
1019 -- 9. Check the return status. If successful, proceed to stage 2. otherwise, close cursor. Raise an exception.
1020 -----------------------------------------------------------------------------------------------
1021 OPEN c_ml_records;
1022 FETCH c_ml_records into l_csp_mtltxn_rec;
1023
1024 IF c_ml_records%NOTFOUND THEN
1025 fnd_message.set_name ('CSP', 'CSP_INVALID_TXN_TEMP_ID');
1026 fnd_message.set_token ('ID', to_char(p_transaction_temp_id), TRUE);
1027 fnd_msg_pub.add;
1028 close c_ml_records;
1029 RAISE EXCP_USER_DEFINED;
1030 END IF;
1031
1032 -- make a backup record for creating a new mmtt record.
1033 l_csp_mtltxn_new_rec := l_csp_mtltxn_rec;
1034 l_to_subinventory := l_csp_mtltxn_rec.transfer_subinventory;
1035 l_to_locator := l_csp_mtltxn_rec.transfer_to_location;
1036
1037 IF l_csp_mtltxn_rec.subinventory_code = l_csp_intransit_subinventory THEN
1038 -- the temp record was split before. We need not to split it again.
1039 -- This section should be evaluated again when the CSP decides to create the
1040 -- material_tranaction_temp record at the packaging stage instead of the
1041 -- confirm picking stage.
1042 close c_ml_records;
1043 x_return_status := fnd_api.g_ret_sts_success;
1044 return;
1045 END IF;
1046 l_csp_mtltxn_new_rec.transfer_subinventory := l_csp_intransit_subinventory;
1047 l_csp_mtltxn_new_rec.transfer_to_location := NULL;
1048 l_csp_mtltxn_new_rec.transaction_source_type_id := 13;
1049 l_csp_mtltxn_new_rec.transaction_type_id := 2; -- subinventory transfer type
1050 l_csp_mtltxn_new_rec.transaction_action_id := 2; -- subinventory tranfer
1051 l_csp_mtltxn_new_rec.process_flag := 'Y';
1052 l_csp_mtltxn_new_rec.LAST_UPDATE_DATE := sysdate;
1053 -- l_csp_mtltxn_new_rec.CREATION_DATE := sysdate;
1054 l_csp_mtltxn_new_rec.transaction_status := 3;
1055
1056 -- check whether the inventory_item is restricted to a predefined list of subinventory
1057 -- if yes, assign csp_intransit inventory to the list
1058 DECLARE
1059 l_restrict_sub_code NUMBER;
1060 l_inventory_item_id NUMBER := l_csp_mtltxn_rec.inventory_item_id;
1061
1062 BEGIN
1063 select restrict_subinventories_code into l_restrict_sub_code
1064 from mtl_system_items
1065 where inventory_item_id = l_inventory_item_id
1069 DECLARE
1066 and organization_id = l_csp_mtltxn_rec.organization_id;
1067
1068 IF l_restrict_sub_code = 1 THEN
1070 l_restricted_sub VARCHAR2(10);
1071
1072 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
1073 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
1074 BEGIN
1075 select secondary_inventory into l_restricted_sub
1076 from mtl_item_sub_inventories
1077 where organization_id = p_organization_id
1078 and inventory_item_id = l_inventory_item_id
1079 and secondary_inventory = l_csp_intransit_subinventory;
1080
1081 EXCEPTION
1082 WHEN NO_DATA_FOUND THEN
1083 -- Assign the item to the l_csp_intransit_subinventory in
1084 -- the mtl_item_sub_inventories table by calling the
1085 -- mtl_item_sub_inventories_pkg.insert_row.
1086 CSP_ITEM_SUB_INVENTORIES_PKG.Insert_Row(
1087 px_INVENTORY_ITEM_ID => l_inventory_item_id,
1088 p_ORGANIZATION_ID => p_organization_id,
1089 p_SECONDARY_INVENTORY => l_csp_intransit_subinventory,
1090 p_LAST_UPDATE_DATE => sysdate,
1091 p_LAST_UPDATED_BY => G_LOGIN_ID,
1092 p_CREATION_DATE => sysdate,
1093 p_CREATED_BY => G_USER_ID ,
1094 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
1095 p_PRIMARY_SUBINVENTORY_FLAG => NULL,
1096 p_PICKING_ORDER => NULL,
1097 p_MIN_MINMAX_QUANTITY => NULL,
1098 p_MAX_MINMAX_QUANTITY => NULL,
1099 p_INVENTORY_PLANNING_CODE => 6, -- Not Planned
1100 p_FIXED_LOT_MULTIPLE => NULL,
1101 p_MINIMUM_ORDER_QUANTITY => NULL,
1102 p_MAXIMUM_ORDER_QUANTITY => NULL,
1103 p_SOURCE_TYPE => NULL,
1104 p_SOURCE_ORGANIZATION_ID => NULL,
1105 p_SOURCE_SUBINVENTORY => NULL,
1106 p_ATTRIBUTE_CATEGORY => NULL,
1107 p_ATTRIBUTE1 => NULL,
1108 p_ATTRIBUTE2 => NULL,
1109 p_ATTRIBUTE3 => NULL,
1110 p_ATTRIBUTE4 => NULL,
1111 p_ATTRIBUTE5 => NULL,
1112 p_ATTRIBUTE6 => NULL,
1113 p_ATTRIBUTE7 => NULL,
1114 p_ATTRIBUTE8 => NULL,
1115 p_ATTRIBUTE9 => NULL,
1116 p_ATTRIBUTE10 => NULL,
1117 p_ATTRIBUTE11 => NULL,
1118 p_ATTRIBUTE12 => NULL,
1119 p_ATTRIBUTE13 => NULL,
1120 p_ATTRIBUTE14 => NULL,
1121 p_ATTRIBUTE15 => NULL,
1122 p_ENCUMBRANCE_ACCOUNT => NULL,
1123 p_PREPROCESSING_LEAD_TIME => NULL,
1124 p_PROCESSING_LEAD_TIME => NULL,
1125 p_POSTPROCESSING_LEAD_TIME => NULL);
1126
1127 WHEN OTHERS THEN
1128 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1129 fnd_message.set_token('ERR_FIELD', 'secondary_inventory', TRUE);
1130 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1131 fnd_message.set_token('TABLE', 'mtl_item_sub_inventories', TRUE);
1132 FND_MSG_PUB.ADD;
1133
1134 IF c_ml_records%ISOPEN THEN
1135 close c_ml_records;
1136 END IF;
1137 RAISE EXCP_USER_DEFINED;
1138 END;
1139 END IF;
1140 END;
1141
1142 -- Assign the transaction_temp_id of the p_csp_rec to NULL so that the
1143 -- CSP_Material_Transactions_PVT.Create_material_transactions will create a new
1144 -- transaction_temp_id from the sequence.
1145
1146 -- l_csp_mtltxn_new_rec.transaction_temp_id := NULL;
1147
1148 CSP_Material_Transactions_PVT.Update_material_transactions(
1149 P_Api_Version_Number => p_api_version_number,
1150 P_Init_Msg_List => p_init_msg_list,
1151 P_Commit => l_commit,
1152 p_validation_level => l_validation_level,
1153 P_CSP_Rec => l_csp_mtltxn_new_rec,
1154 -- X_TRANSACTION_TEMP_ID => l_transaction_temp_id,
1155 X_Return_Status => l_return_status,
1156 X_Msg_Count => l_msg_count,
1157 X_Msg_Data => l_msg_data
1158 );
1162 fnd_message.set_name ('CSP', 'CSP_SUB_TXFER_ERROR');
1159
1160
1161 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1163 fnd_msg_pub.add;
1164 IF c_ml_records%ISOPEN THEN
1165 close c_ml_records;
1166 END IF;
1167 RAISE FND_API.G_EXC_ERROR;
1168 END IF;
1169
1170 -- creating a transaction_header_id for inserting into the interface table
1171 IF l_transaction_header_id is null THEN
1172 Open l_Get_txn_header_id_csr;
1173 Fetch l_Get_txn_header_id_csr Into l_transaction_header_id;
1174 Close l_Get_txn_header_id_csr;
1175 END IF;
1176
1177 csp_transactions_pub.transact_temp_record (
1178 P_Api_Version_Number => l_api_version_number,
1179 P_Init_Msg_List => FND_API.G_true,
1180 P_Commit => fnd_api.g_false,
1181 p_validation_level => l_validation_level,
1182 p_transaction_temp_id => l_csp_mtltxn_new_rec.transaction_temp_id,
1183 px_transaction_header_id => l_transaction_header_id,
1184 p_online_process_flag => FALSE,
1185 X_Return_Status => l_return_status,
1186 X_Msg_Count => l_msg_count,
1187 X_Msg_Data => l_msg_data );
1188
1189 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1191 END IF;
1192
1193 /* DO NOT CLEAN UP THE TEMP RECORDS. OTHERWISE, THE FOLLOWING API WILL ERROR OUT.
1194 Theory: We copy the existing record to the interface table so that we can initialize the
1195 first phrase of transactions while the temp record is left untouched.
1196 */
1197
1198 ---------------------------------------
1199 -- Stage 2: Update the exsiting record which transfers the items from the
1200 -- csp intransit to the destination subinventory.
1201 -- 1. Update the subinventory_code to l_csp_intransit_subinventory.
1202 -- 2. Update the locator_id to NULL.
1203 -- 3. Update the transfer_subinventory to l_to_subinventory.
1204 -- 4. Update the tansfer_to_location to l_to_locator.
1205 -- 5. Update the process_flag to 'N'.
1206 -- 6. Call the CSP_Material_Transactions_PVT.update_material_transactions.
1207
1208 l_csp_mtltxn_rec.subinventory_code := l_csp_intransit_subinventory;
1209 l_csp_mtltxn_rec.locator_id := NULL;
1210 l_csp_mtltxn_rec.transfer_subinventory := l_to_subinventory;
1211 l_csp_mtltxn_rec.transfer_to_location := l_to_locator;
1212 l_csp_mtltxn_rec.process_flag := 'N';
1213 l_csp_mtltxn_rec.LAST_UPDATE_DATE := sysdate;
1214 -- l_csp_mtltxn_rec.CREATION_DATE := sysdate;
1215
1216 CSP_Material_Transactions_PVT.Update_material_transactions(
1217 P_Api_Version_Number => p_api_version_number,
1218 P_Init_Msg_List => p_init_msg_list,
1219 P_Commit => fnd_api.g_false,
1220 p_validation_level => l_validation_level,
1221 P_CSP_Rec => l_csp_mtltxn_rec,
1222 X_Return_Status => l_return_status,
1223 X_Msg_Count => l_msg_count,
1224 X_Msg_Data => l_msg_data
1225 );
1226
1227 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1228 fnd_message.set_name ('CSP', 'CSP_SUB_TXFER_ERROR');
1229 fnd_msg_pub.add;
1230 IF c_ml_records%ISOPEN THEN
1231 close c_ml_records;
1232 END IF;
1233 RAISE FND_API.G_EXC_ERROR;
1234 END IF;
1235
1236 -- If it gets to here, processing the mtl_material_transactions_temp should complete successfully.
1237 IF c_ml_records%ISOPEN THEN
1238 close c_ml_records;
1239 END IF;
1240
1241 /*
1242 -- Finally, call the process_online to perform the underneath material transactions
1243 IF NOT CSP_Mo_Mtltxns_Util.Call_Online (p_transaction_header_id => l_transaction_header_id) THEN
1244 l_outcome := FALSE;
1245 x_return_status := FND_API.G_RET_STS_ERROR;
1246 fnd_msg_pub.count_and_get
1247 ( p_count => x_msg_count
1248 , p_data => x_msg_data);
1249 Return;
1250 END IF;
1251 */
1252 px_transaction_header_id := l_transaction_header_id;
1256
1253 IF fnd_api.to_boolean(p_commit) THEN
1254 commit work;
1255 END IF;
1257 x_return_status := fnd_api.g_ret_sts_success;
1258
1259 EXCEPTION
1260 WHEN EXCP_USER_DEFINED THEN
1261 Rollback to CSP_MO_LINES_MANUAL_PUB;
1262 x_return_status := FND_API.G_RET_STS_ERROR;
1263 fnd_msg_pub.count_and_get
1264 ( p_count => x_msg_count
1265 , p_data => x_msg_data);
1266 WHEN FND_API.G_EXC_ERROR THEN
1267 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1268 P_API_NAME => L_API_NAME
1269 ,P_PKG_NAME => G_PKG_NAME
1270 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1271 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1272 ,X_MSG_COUNT => X_MSG_COUNT
1273 ,X_MSG_DATA => X_MSG_DATA
1274 ,X_RETURN_STATUS => X_RETURN_STATUS);
1275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1276 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1277 P_API_NAME => L_API_NAME
1278 ,P_PKG_NAME => G_PKG_NAME
1279 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1280 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1281 ,X_MSG_COUNT => X_MSG_COUNT
1282 ,X_MSG_DATA => X_MSG_DATA
1283 ,X_RETURN_STATUS => X_RETURN_STATUS);
1284 WHEN OTHERS THEN
1285 Rollback to CSP_MO_LINES_MANUAL_PUB;
1286 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1287 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1288 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1289 fnd_msg_pub.add;
1290 fnd_msg_pub.count_and_get
1291 ( p_count => x_msg_count
1292 , p_data => x_msg_data);
1293 x_return_status := fnd_api.g_ret_sts_error;
1294
1295 END CSP_MO_LINES_MANUAL_RECEIPT;
1296
1297
1298 PROCEDURE CSP_MO_Lines_Auto_Receipt (
1299 -- Start of Comments
1300 -- Procedure name : CSP_MO_Lines_Auto_Receipt
1301 -- Purpose : A procedure to perform move_order_line transaction for move orders that are of auto receipts.
1302 -- Usage : This procedure only processes move orders which are Autorecipts.
1303 -- History :
1304 -- 29-Dec-99, Vernon Lou.
1305 --
1306 -- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
1307 -- full validation here is unnecessary. To avoid repeating the same validations, you can set the
1308 -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
1309 -- responsibility to make sure all proper validations have been done before calling this procedure.
1310 -- You are recommended to let this procedure handle the validations if you are not sure.
1311 -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
1312 -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
1313 -- to FND_API.G_VALID_LEVEL_FULL when making the call.
1314 -- End of Comments
1315
1316 P_Api_Version_Number IN NUMBER,
1317 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1318 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1319 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1320 p_organization_id IN NUMBER,
1321 p_transaction_temp_id IN NUMBER,
1322 X_Return_Status OUT NOCOPY VARCHAR2,
1323 X_Msg_Count OUT NOCOPY NUMBER,
1324 X_Msg_Data OUT NOCOPY VARCHAR2)
1325 IS
1326 l_api_version_number CONSTANT NUMBER := 1.0;
1327 l_api_name CONSTANT VARCHAR2(50) := 'CSP_MO_Lines_Auto';
1328 l_msg_data VARCHAR2(300);
1329
1330 l_check_existence NUMBER := 0;
1331 l_return_status VARCHAR2(1);
1332 l_msg_count NUMBER := 0;
1333 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1334 l_creation_date DATE;
1335 l_last_update_date DATE;
1336 l_csp_mtltxn_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
1337
1338 -- for inserting data, the validation_level should be none
1339 -- because we do not want to call core apps standard validations.
1340 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
1341 l_transaction_temp_id NUMBER;
1342
1343 EXCP_USER_DEFINED EXCEPTION;
1344
1345 -- declare a cursor to hold all the records in the mtl_material_transactions_temp table
1346 -- that belong to the move_order_line_id.
1347 CURSOR c_ml_records IS
1348 SELECT TRANSACTION_HEADER_ID ,
1349 TRANSACTION_TEMP_ID ,
1350 SOURCE_CODE ,
1351 SOURCE_LINE_ID ,
1352 TRANSACTION_MODE ,
1353 LOCK_FLAG ,
1354 LAST_UPDATE_DATE ,
1355 LAST_UPDATED_BY ,
1356 CREATION_DATE ,
1357 CREATED_BY ,
1358 LAST_UPDATE_LOGIN ,
1359 REQUEST_ID ,
1360 PROGRAM_APPLICATION_ID ,
1361 PROGRAM_ID ,
1362 PROGRAM_UPDATE_DATE ,
1363 INVENTORY_ITEM_ID ,
1364 REVISION ,
1365 ORGANIZATION_ID ,
1369 PRIMARY_QUANTITY ,
1366 SUBINVENTORY_CODE ,
1367 LOCATOR_ID ,
1368 TRANSACTION_QUANTITY ,
1370 TRANSACTION_UOM ,
1371 TRANSACTION_COST ,
1372 TRANSACTION_TYPE_ID ,
1373 TRANSACTION_ACTION_ID ,
1374 TRANSACTION_SOURCE_TYPE_ID ,
1375 TRANSACTION_SOURCE_ID ,
1376 TRANSACTION_SOURCE_NAME ,
1377 TRANSACTION_DATE ,
1378 ACCT_PERIOD_ID ,
1379 DISTRIBUTION_ACCOUNT_ID ,
1380 TRANSACTION_REFERENCE ,
1381 REQUISITION_LINE_ID ,
1382 REQUISITION_DISTRIBUTION_ID ,
1383 REASON_ID ,
1384 LOT_NUMBER ,
1385 LOT_EXPIRATION_DATE ,
1386 SERIAL_NUMBER ,
1387 RECEIVING_DOCUMENT ,
1388 DEMAND_ID ,
1389 RCV_TRANSACTION_ID ,
1390 MOVE_TRANSACTION_ID ,
1391 COMPLETION_TRANSACTION_ID ,
1392 WIP_ENTITY_TYPE ,
1393 SCHEDULE_ID ,
1394 REPETITIVE_LINE_ID ,
1395 EMPLOYEE_CODE ,
1396 PRIMARY_SWITCH ,
1397 SCHEDULE_UPDATE_CODE ,
1398 SETUP_TEARDOWN_CODE ,
1399 ITEM_ORDERING ,
1400 NEGATIVE_REQ_FLAG ,
1401 OPERATION_SEQ_NUM ,
1402 PICKING_LINE_ID ,
1403 TRX_SOURCE_LINE_ID ,
1404 TRX_SOURCE_DELIVERY_ID ,
1405 PHYSICAL_ADJUSTMENT_ID ,
1406 CYCLE_COUNT_ID ,
1407 RMA_LINE_ID ,
1408 CUSTOMER_SHIP_ID ,
1409 CURRENCY_CODE ,
1410 CURRENCY_CONVERSION_RATE ,
1411 CURRENCY_CONVERSION_TYPE ,
1412 CURRENCY_CONVERSION_DATE ,
1413 USSGL_TRANSACTION_CODE ,
1414 VENDOR_LOT_NUMBER ,
1415 ENCUMBRANCE_ACCOUNT ,
1416 ENCUMBRANCE_AMOUNT ,
1417 SHIP_TO_LOCATION ,
1418 SHIPMENT_NUMBER ,
1419 TRANSFER_COST ,
1420 TRANSPORTATION_COST ,
1421 TRANSPORTATION_ACCOUNT ,
1422 FREIGHT_CODE ,
1423 CONTAINERS ,
1424 WAYBILL_AIRBILL ,
1425 EXPECTED_ARRIVAL_DATE ,
1426 TRANSFER_SUBINVENTORY ,
1427 TRANSFER_ORGANIZATION ,
1428 TRANSFER_TO_LOCATION ,
1429 NEW_AVERAGE_COST ,
1430 VALUE_CHANGE ,
1431 PERCENTAGE_CHANGE ,
1432 MATERIAL_ALLOCATION_TEMP_ID ,
1433 DEMAND_SOURCE_HEADER_ID ,
1434 DEMAND_SOURCE_LINE ,
1435 DEMAND_SOURCE_DELIVERY ,
1436 ITEM_SEGMENTS ,
1437 ITEM_DESCRIPTION ,
1438 ITEM_TRX_ENABLED_FLAG ,
1439 ITEM_LOCATION_CONTROL_CODE ,
1440 ITEM_RESTRICT_SUBINV_CODE ,
1441 ITEM_RESTRICT_LOCATORS_CODE ,
1442 ITEM_REVISION_QTY_CONTROL_CODE ,
1443 ITEM_PRIMARY_UOM_CODE ,
1444 ITEM_UOM_CLASS ,
1445 ITEM_SHELF_LIFE_CODE ,
1446 ITEM_SHELF_LIFE_DAYS ,
1447 ITEM_LOT_CONTROL_CODE ,
1448 ITEM_SERIAL_CONTROL_CODE ,
1449 ITEM_INVENTORY_ASSET_FLAG ,
1450 ALLOWED_UNITS_LOOKUP_CODE ,
1451 DEPARTMENT_ID ,
1452 DEPARTMENT_CODE ,
1453 WIP_SUPPLY_TYPE ,
1454 SUPPLY_SUBINVENTORY ,
1455 SUPPLY_LOCATOR_ID ,
1456 VALID_SUBINVENTORY_FLAG ,
1457 VALID_LOCATOR_FLAG ,
1458 LOCATOR_SEGMENTS ,
1459 CURRENT_LOCATOR_CONTROL_CODE ,
1460 NUMBER_OF_LOTS_ENTERED ,
1461 WIP_COMMIT_FLAG ,
1462 NEXT_LOT_NUMBER ,
1463 LOT_ALPHA_PREFIX ,
1464 NEXT_SERIAL_NUMBER ,
1465 SERIAL_ALPHA_PREFIX ,
1466 SHIPPABLE_FLAG ,
1467 POSTING_FLAG ,
1468 REQUIRED_FLAG ,
1469 PROCESS_FLAG ,
1470 ERROR_CODE ,
1471 ERROR_EXPLANATION ,
1472 ATTRIBUTE_CATEGORY ,
1473 ATTRIBUTE1 ,
1474 ATTRIBUTE2 ,
1475 ATTRIBUTE3 ,
1476 ATTRIBUTE4 ,
1477 ATTRIBUTE5 ,
1478 ATTRIBUTE6 ,
1479 ATTRIBUTE7 ,
1480 ATTRIBUTE8 ,
1481 ATTRIBUTE9 ,
1482 ATTRIBUTE10 ,
1483 ATTRIBUTE11 ,
1484 ATTRIBUTE12 ,
1485 ATTRIBUTE13 ,
1486 ATTRIBUTE14 ,
1490 SHIPPED_QUANTITY ,
1487 ATTRIBUTE15 ,
1488 MOVEMENT_ID ,
1489 RESERVATION_QUANTITY ,
1491 TRANSACTION_LINE_NUMBER ,
1492 TASK_ID ,
1493 TO_TASK_ID ,
1494 SOURCE_TASK_ID ,
1495 PROJECT_ID ,
1496 SOURCE_PROJECT_ID ,
1497 PA_EXPENDITURE_ORG_ID ,
1498 TO_PROJECT_ID ,
1499 EXPENDITURE_TYPE ,
1500 FINAL_COMPLETION_FLAG ,
1501 TRANSFER_PERCENTAGE ,
1502 TRANSACTION_SEQUENCE_ID ,
1503 MATERIAL_ACCOUNT ,
1504 MATERIAL_OVERHEAD_ACCOUNT ,
1505 RESOURCE_ACCOUNT ,
1506 OUTSIDE_PROCESSING_ACCOUNT ,
1507 OVERHEAD_ACCOUNT ,
1508 FLOW_SCHEDULE ,
1509 COST_GROUP_ID ,
1510 DEMAND_CLASS ,
1511 QA_COLLECTION_ID ,
1512 KANBAN_CARD_ID ,
1513 OVERCOMPLETION_TRANSACTION_ID ,
1514 OVERCOMPLETION_PRIMARY_QTY ,
1515 OVERCOMPLETION_TRANSACTION_QTY ,
1516 --PROCESS_TYPE , --removed 01/13/00. process_type does not exist in the mmtt table.
1517 END_ITEM_UNIT_NUMBER ,
1518 SCHEDULED_PAYBACK_DATE ,
1519 LINE_TYPE_CODE ,
1520 PARENT_TRANSACTION_TEMP_ID ,
1521 PUT_AWAY_STRATEGY_ID ,
1522 PUT_AWAY_RULE_ID ,
1523 PICK_STRATEGY_ID ,
1524 PICK_RULE_ID ,
1525 COMMON_BOM_SEQ_ID ,
1526 COMMON_ROUTING_SEQ_ID ,
1527 COST_TYPE_ID ,
1528 ORG_COST_GROUP_ID ,
1529 MOVE_ORDER_LINE_ID ,
1530 TASK_GROUP_ID ,
1531 PICK_SLIP_NUMBER ,
1532 RESERVATION_ID ,
1533 TRANSACTION_STATUS ,
1534 STANDARD_OPERATION_ID ,
1535 TASK_PRIORITY ,
1536 -- ADDED by phegde 02/23
1537 WMS_TASK_TYPE ,
1538 PARENT_LINE_ID
1539 --SOURCE_LOT_NUMBER
1540 FROM mtl_material_transactions_temp
1541 WHERE transaction_temp_id = p_transaction_temp_id
1542 AND organization_id = p_organization_id;
1543 BEGIN
1544 SAVEPOINT CSP_MO_Lines_Auto_PUB;
1545
1546 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1547 -- initialize message list
1548 FND_MSG_PUB.initialize;
1549 END IF;
1550
1551 -- Standard call to check for call compatibility.
1552 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1553 p_api_version_number,
1554 l_api_name,
1555 G_PKG_NAME)
1556 THEN
1557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1558 END IF;
1559
1560 IF p_validation_level = fnd_api.g_valid_level_full THEN
1561 -- Notes: if validations have been done in the precedence procedure which this one is being from, doing a full
1562 -- validation here is not necessary. The users can set the p_validation_level to fnd_api.g_valid_level_none
1563 -- if they do not want to repeat the same validations. However, it is their responsibility to make sure
1564 -- all proper validations have been done before calling this procedure. It is recommended that they let
1565 -- this procedure handle the validations except that they know what they are doing.
1566
1567 -- validate p_organization_id
1568 IF p_organization_id IS NULL THEN
1569 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
1570 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
1571 FND_MSG_PUB.ADD;
1572 RAISE EXCP_USER_DEFINED;
1573 ELSE
1574 BEGIN
1575 select organization_id into l_check_existence
1576 from mtl_parameters
1577 where organization_id = p_organization_id;
1578 EXCEPTION
1579 WHEN NO_DATA_FOUND THEN
1580 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
1581 FND_MSG_PUB.ADD;
1582 RAISE EXCP_USER_DEFINED;
1583 WHEN OTHERS THEN
1584 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1585 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
1586 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1587 fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
1588 FND_MSG_PUB.ADD;
1589 RAISE EXCP_USER_DEFINED;
1590 END;
1591 END IF;
1592
1593 -- validate p_move_order_line_id
1594 IF p_transaction_temp_id IS NULL THEN
1595 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
1596 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_transaction_temp_id', TRUE);
1597 FND_MSG_PUB.ADD;
1598 RAISE EXCP_USER_DEFINED;
1599 ELSE
1600 DECLARE
1601 l_header_id NUMBER;
1602 l_move_order_line_id NUMBER;
1603 l_autoreceipt_flag csp_moveorder_headers.autoreceipt_flag%type;
1604
1608 from mtl_material_transactions_temp
1605 -- validate whether the move_order_line_id exists
1606 BEGIN
1607 select move_order_line_id into l_move_order_line_id
1609 where transaction_temp_id = p_transaction_temp_id
1610 and organization_id = p_organization_id;
1611
1612
1613 select header_id into l_header_id
1614 from csp_moveorder_lines
1615 where line_id = l_move_order_line_id;
1616
1617 -- validate whether the move order is under manual receipt, if not, raise an exception.
1618 select autoreceipt_flag into l_autoreceipt_flag
1619 from csp_moveorder_headers
1620 where header_id = l_header_id;
1621
1622 IF l_autoreceipt_flag <> 'Y' THEN
1623 fnd_message.set_name ('CSP', 'CSP_INVALID_AUTORECEIPT');
1624 fnd_msg_pub.add;
1625 RAISE EXCP_USER_DEFINED;
1626 END IF;
1627
1628 EXCEPTION
1629 WHEN EXCP_USER_DEFINED THEN
1630 RAISE EXCP_USER_DEFINED;
1631 WHEN NO_DATA_FOUND THEN
1632 fnd_message.set_name ('CSP', 'CSP_NO_MO_TXN_RECORD');
1633 fnd_msg_pub.add;
1634 RAISE EXCP_USER_DEFINED;
1635 WHEN OTHERS THEN
1636 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1637 fnd_message.set_token('ERR_FIELD', 'autoreceipt_flag', TRUE);
1638 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1639 fnd_message.set_token('TABLE', 'csp_moveorder_headers', TRUE);
1640 FND_MSG_PUB.ADD;
1641 RAISE EXCP_USER_DEFINED;
1642 END;
1643 END IF;
1644
1645 END IF; -- end of validations
1646
1647 ---------------------------------------------------------------------------------------------
1648 -- 1. Open the cursor c_mtl_records.
1649 -- 2. Fecth one row into l_csp_mtltxn_rec.
1650 -- 3. Set process_flag to 'Y' and transaction_status to 3.
1651 -- 4. Calll CSP_Material_Transactions_PVT.Update_material_transactions to update the
1652
1653 OPEN c_ml_records;
1654 LOOP
1655 FETCH c_ml_records into l_csp_mtltxn_rec;
1656
1657 EXIT WHEN c_ml_records%NOTFOUND;
1658
1659 l_csp_mtltxn_rec.process_flag := 'Y';
1660 l_csp_mtltxn_rec.transaction_status := 3;
1661 l_csp_mtltxn_rec.posting_flag := 'Y';
1662 l_csp_mtltxn_rec.LAST_UPDATE_DATE := sysdate;
1663 l_csp_mtltxn_rec.CREATION_DATE := sysdate;
1664
1665
1666 CSP_Material_Transactions_PVT.Update_material_transactions(
1667 P_Api_Version_Number => p_api_version_number,
1668 P_Init_Msg_List => p_init_msg_list,
1669 P_Commit => fnd_api.g_false,
1670 p_validation_level => l_validation_level,
1671 P_CSP_Rec => l_csp_mtltxn_rec,
1672 X_Return_Status => l_return_status,
1673 X_Msg_Count => l_msg_count,
1674 X_Msg_Data => l_msg_data
1675 );
1676
1677 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1678 IF c_ml_records%ISOPEN THEN
1679 close c_ml_records;
1680 END IF;
1681 RAISE FND_API.G_EXC_ERROR;
1682 END IF;
1683
1684 END LOOP;
1685
1686 -- If it gets to here, updating the order line status should complete successfully.
1687 IF c_ml_records%ISOPEN THEN
1688 close c_ml_records;
1689 END IF;
1690
1691 -- update orderline
1692
1693 IF fnd_api.to_boolean(p_commit) THEN
1694 commit work;
1695 END IF;
1696
1697 x_return_status := fnd_api.g_ret_sts_success;
1698
1699 EXCEPTION
1700 WHEN EXCP_USER_DEFINED THEN
1701 Rollback to CSP_MO_Lines_Auto_PUB;
1702 x_return_status := FND_API.G_RET_STS_ERROR;
1703 fnd_msg_pub.count_and_get
1704 ( p_count => x_msg_count
1705 , p_data => x_msg_data);
1706
1707 WHEN FND_API.G_EXC_ERROR THEN
1708 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1709 P_API_NAME => L_API_NAME
1710 ,P_PKG_NAME => G_PKG_NAME
1711 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1712 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1713 ,X_MSG_COUNT => X_MSG_COUNT
1714 ,X_MSG_DATA => X_MSG_DATA
1715 ,X_RETURN_STATUS => X_RETURN_STATUS);
1716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1717 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1718 P_API_NAME => L_API_NAME
1719 ,P_PKG_NAME => G_PKG_NAME
1720 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1721 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1722 ,X_MSG_COUNT => X_MSG_COUNT
1723 ,X_MSG_DATA => X_MSG_DATA
1727 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1724 ,X_RETURN_STATUS => X_RETURN_STATUS);
1725 WHEN OTHERS THEN
1726 Rollback to CSP_MO_Lines_Auto_PUB;
1728 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1729 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1730 fnd_msg_pub.add;
1731 fnd_msg_pub.count_and_get
1732 ( p_count => x_msg_count
1733 , p_data => x_msg_data);
1734 x_return_status := fnd_api.g_ret_sts_error;
1735
1736
1737 END CSP_MO_Lines_Auto_Receipt;
1738
1739 END CSP_PC_FORM_MTLTXNS;