[Home] [Help]
PACKAGE BODY: APPS.CLN_SYNC_INVENTORY_PKG
Source
1 PACKAGE BODY CLN_SYNC_INVENTORY_PKG AS
2 /* $Header: CLNSINVB.pls 120.1 2005/11/03 05:12:17 kkram noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4
5 -- Package
6 -- CLN_SYNC_INVENTORY_PKG
7 --
8 -- Purpose
9 -- Body of package CLN_SYNC_INVENTORY_PKG.
10 --
11 -- History
12 -- April-17-2003 Rahul Krishan Created
13
14
15
16 -- Name
17 -- RAISE_REPORT_GEN_EVENT
18 -- Purpose
19 -- The main purpose ofthis API is to capture the parameters reqd. for the generation of the
20 -- inventory report as inputted by the user using concurrent program.
21 -- Arguments
22 --
23 -- Notes
24 -- No specific notes.
25
26 PROCEDURE RAISE_REPORT_GEN_EVENT(
27 x_errbuf OUT NOCOPY VARCHAR2,
28 x_retcode OUT NOCOPY NUMBER,
29 p_inv_user IN NUMBER,
30 p_inv_org IN NUMBER,
31 p_sub_inv IN VARCHAR2,
32 p_lot_number IN VARCHAR2,
33 p_item_category IN NUMBER,
34 p_item_number_from IN VARCHAR2,
35 p_item_number_to IN VARCHAR2,
36 p_item_revision_from IN VARCHAR2,
37 p_item_revision_to IN VARCHAR2,
38 p_diposition_available IN VARCHAR2,
39 p_diposition_blocked IN VARCHAR2,
40 p_diposition_allocated IN VARCHAR2 )
41 IS
42
43 l_error_code NUMBER;
44 l_error_msg VARCHAR2(255);
45 l_msg_data VARCHAR2(255);
46 l_debug_mode VARCHAR2(255);
47 l_event_key NUMBER;
48 l_cln_inv_parameters wf_parameter_list_t;
49 l_tp_header_id NUMBER;
50 l_doc_number VARCHAR2(255);
51 l_xmlg_transaction_type VARCHAR2(255);
52 l_xmlg_transaction_subtype VARCHAR2(255);
53 l_xmlg_document_id VARCHAR2(255);
54 l_tr_partner_type VARCHAR2(255);
55 l_tr_partner_id VARCHAR2(255);
56 l_tr_partner_site VARCHAR2(255);
57 l_party_name VARCHAR2(255);
58 l_doc_dir VARCHAR2(255);
59 l_dummy_check VARCHAR2(10);
60
61
62 BEGIN
63
64 -- Sets the debug mode to be FILE
65 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
66
67 IF (l_Debug_Level <= 2) THEN
68 cln_debug_pub.Add('----- Entering RAISE_REPORT_GEN_EVENT API ------- ',2);
69 END IF;
70
71
72 -- Initialize API return status to success
73 l_msg_data := 'Successfully called the CLN API to kick off the report generation for inventory';
74
75 -- Parameters received
76 IF (l_Debug_Level <= 1) THEN
77 cln_debug_pub.Add('== PARAMETERS RECEIVED FROM CONCURRENT PROGRAM== ',1);
78 cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
79 cln_debug_pub.Add('Inventory Information User - '||p_inv_user,1);
80 cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
81 cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
82 cln_debug_pub.Add('Inventory Disposition (Allocated) - '||p_diposition_allocated,1);
83 cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
84 cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
85 cln_debug_pub.Add('Item Category - '||p_item_category,1);
86 cln_debug_pub.Add('Item Number[Concatenated Segment](From) - '||p_item_number_from,1);
87 cln_debug_pub.Add('Item Number[Concatenated Segment](To) - '||p_item_number_to,1);
88 cln_debug_pub.Add('Item Revision Number (From) - '||p_item_revision_from,1);
89 cln_debug_pub.Add('Item Revision Number (To) - '||p_item_revision_to,1);
90 cln_debug_pub.Add('=================================================',1);
91 END IF;
92
93
94
95 IF (l_Debug_Level <= 1) THEN
96 cln_debug_pub.Add('Setting Event Key....',1);
97 END IF;
98
99 SELECT cln_generic_s.nextval INTO l_event_key FROM Dual;
100 IF (l_Debug_Level <= 1) THEN
101 cln_debug_pub.Add('Event Key set as - '||l_event_key,1);
102
103 cln_debug_pub.Add('Getting Trading Partner Details Using Tp_Header_Id',1);
104 END IF;
105
106 BEGIN
107
108 SELECT etph.party_type, etpv.party_id, etpv.party_site_id, etpv.party_name
109 INTO l_tr_partner_type, l_tr_partner_id, l_tr_partner_site, l_party_name
110 FROM ecx_tp_headers_v etpv, ecx_tp_headers etph
111 WHERE etph.tp_header_id = p_inv_user
112 AND etph.tp_header_id = etpv.tp_header_id;
113
114
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_FOUND');
118 l_msg_data := FND_MESSAGE.GET;
119 IF (l_Debug_Level <= 1) THEN
120 cln_debug_pub.Add('Unable to find the set up details for the trading partner',1);
121 END IF;
122
123 RAISE FND_API.G_EXC_ERROR;
124 WHEN TOO_MANY_ROWS THEN
125 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_UNIQUE');
126 l_msg_data := FND_MESSAGE.GET;
127 IF (l_Debug_Level <= 1) THEN
128 cln_debug_pub.Add('More then one row found for the same trading partner set up',1);
129 END IF;
130
131 RAISE FND_API.G_EXC_ERROR;
132 END;
133 IF (l_Debug_Level <= 1) THEN
134 cln_debug_pub.Add('Trading Partner Details Found',1);
135 END IF;
136
137
138 IF (l_Debug_Level <= 1) THEN
139 cln_debug_pub.Add('Defaulting XMLG Document ID with a running sequence',1);
140 END IF;
141
142 SELECT cln_generic_s.nextval INTO l_xmlg_document_id FROM Dual;
143
144 IF (l_Debug_Level <= 1) THEN
145 cln_debug_pub.Add('Defaulting Documnet No with sysdate',1);
146 END IF;
147
148
149 SELECT TO_CHAR(cln_sync_inv_doc_s.nextval) INTO l_doc_number FROM Dual;
150
151 IF (l_Debug_Level <= 1) THEN
152 cln_debug_pub.Add('Checking for user input.....',1);
153 END IF;
154
155 BEGIN
156 --SELECT inventory_item_id
157 SELECT 'x' INTO l_dummy_check FROM DUAL
158 WHERE EXISTS(
159 SELECT 'x'
160 FROM CLN_INVENTORY_REPORT_V
161 WHERE organization_id = p_inv_org
162 AND (concatenated_segments BETWEEN nvl(p_item_number_from,concatenated_segments) AND nvl(p_item_number_to,concatenated_segments))
163 AND (((p_item_revision_from IS NULL OR p_item_revision_to IS NULL) AND CLN_INVENTORY_REPORT_V.REVISION_QTY_CONTROL_CODE =1)or revision BETWEEN nvl(p_item_revision_from,revision) AND nvl(p_item_revision_to,revision))
164 AND EXISTS ( SELECT 'X' FROM MTL_ITEM_CATEGORIES MIC
165 WHERE (CLN_INVENTORY_REPORT_V.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID(+)
166 AND CLN_INVENTORY_REPORT_V.ORGANIZATION_ID = MIC.ORGANIZATION_ID(+))
167 AND MIC.category_id = nvl(p_item_category,MIC.category_id))
168 AND EXISTS (
169 SELECT 'X' FROM mtl_secondary_inventories msi, mtl_onhand_quantities_detail moqd
170 WHERE msi.organization_id = moqd.organization_id
171 AND msi.secondary_inventory_name = moqd.subinventory_code
172 AND msi.organization_id = CLN_INVENTORY_REPORT_V.organization_id
173 AND msi.secondary_inventory_name = NVL(p_sub_inv,msi.secondary_inventory_name)
174 AND moqd.inventory_item_id = CLN_INVENTORY_REPORT_V.inventory_item_id
175 AND (CLN_INVENTORY_REPORT_V.revision IS NULL OR moqd.revision IS NULL OR moqd.revision = CLN_INVENTORY_REPORT_V.revision)
176 )
177 AND ( (p_lot_number IS NULL AND CLN_INVENTORY_REPORT_V.LOT_CONTROL_CODE = 1) OR
178 EXISTS (
179 SELECT 'X' FROM MTL_LOT_NUMBERS MLN
180 WHERE CLN_INVENTORY_REPORT_V.inventory_item_id = mln.inventory_item_id(+)
181 AND CLN_INVENTORY_REPORT_V.organization_id = mln.organization_id(+)
182 AND mln.lot_number = NVL(p_lot_number,mln.lot_number)
183 )
184 )
185 );
186
187 EXCEPTION
188 WHEN NO_DATA_FOUND THEN
189 FND_MESSAGE.SET_NAME('CLN','CLN_CH_NO_ROW_SELECTED');
190 l_msg_data := FND_MESSAGE.GET;
191 IF (l_Debug_Level <= 1) THEN
192 cln_debug_pub.Add('No records found for the user input',1);
193 END IF;
194
195 RAISE FND_API.G_EXC_ERROR;
196 WHEN TOO_MANY_ROWS THEN
197 IF (l_Debug_Level <= 1) THEN
198 cln_debug_pub.Add('More then one row found for the user input',1);
199 END IF;
200
201 END;
202 IF (l_Debug_Level <= 1) THEN
203 cln_debug_pub.Add('User input seems valid.....',1);
204 END IF;
205
206
207 l_cln_inv_parameters := wf_parameter_list_t();
208 IF (l_Debug_Level <= 1) THEN
209 cln_debug_pub.Add('---------- SETTING WORKFLOW PARAMETERS---------', 1);
210 END IF;
211
212 WF_EVENT.AddParameterToList('INV_ORGANIZATION',p_inv_org,l_cln_inv_parameters);
213 WF_EVENT.AddParameterToList('TP_HEADER_ID',p_inv_user,l_cln_inv_parameters);
214 WF_EVENT.AddParameterToList('INV_DIS_AVAILABLE',p_diposition_available,l_cln_inv_parameters);
215 WF_EVENT.AddParameterToList('INV_DIS_BLOCKED',p_diposition_blocked,l_cln_inv_parameters);
216 WF_EVENT.AddParameterToList('INV_DIS_ALLOCATED',p_diposition_allocated,l_cln_inv_parameters);
217 WF_EVENT.AddParameterToList('SUB_INV',p_sub_inv,l_cln_inv_parameters);
218 WF_EVENT.AddParameterToList('LOT_NUMBER', p_lot_number, l_cln_inv_parameters);
219 WF_EVENT.AddParameterToList('ITEM_CATEGORY', p_item_category, l_cln_inv_parameters);
220 WF_EVENT.AddParameterToList('ITEM_NUMBER_FROM', p_item_number_from, l_cln_inv_parameters);
221 WF_EVENT.AddParameterToList('ITEM_NUMBER_TO', p_item_number_to, l_cln_inv_parameters);
222 WF_EVENT.AddParameterToList('ITEM_REV_NUMBER_FROM',p_item_revision_from,l_cln_inv_parameters);
223 WF_EVENT.AddParameterToList('ITEM_REV_NUMBER_TO',p_item_revision_to,l_cln_inv_parameters);
224 WF_EVENT.AddParameterToList('TRADING_PARTNER_TYPE',l_tr_partner_type,l_cln_inv_parameters);
225 WF_EVENT.AddParameterToList('TRADING_PARTNER_ID', l_tr_partner_id,l_cln_inv_parameters);
226 WF_EVENT.AddParameterToList('TRADING_PARTNER_SITE', l_tr_partner_site,l_cln_inv_parameters);
227 WF_EVENT.AddParameterToList('DOCUMENT_NO', l_doc_number,l_cln_inv_parameters);
228 WF_EVENT.AddParameterToList('XMLG_DOCUMENT_ID', l_xmlg_document_id,l_cln_inv_parameters);
229 WF_EVENT.AddParameterToList('TRADING_PARTNER_NAME', l_party_name,l_cln_inv_parameters);
230 WF_EVENT.AddParameterToList('XMLG_INTERNAL_TXN_TYPE', 'CLN',l_cln_inv_parameters);
231 WF_EVENT.AddParameterToList('XMLG_INTERNAL_TXN_SUBTYPE', 'INVRT',l_cln_inv_parameters);
232 WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'OUT',l_cln_inv_parameters);
233
234
235 IF (l_Debug_Level <= 1) THEN
236 cln_debug_pub.Add('======== PARAMETERS DEFAULTED ======== ',1);
237 cln_debug_pub.Add('DOCUMENT_NO ----- >>> '||l_doc_number,1);
238 cln_debug_pub.Add('XMLG EXT TRANSACTION TYPE ----- >>> '||'INVENTORY',1);
239 cln_debug_pub.Add('XMLG EXT TRANSACTION SUBTYPE ----- >>> '||'SYNC',1);
240 cln_debug_pub.Add('XMLG INT TRANSACTION TYPE ----- >>> '||'CLN',1);
241 cln_debug_pub.Add('XMLG INT TRANSACTION SUBTYPE ----- >>> '||'INVRT',1);
242 cln_debug_pub.Add('XMLG DOCUMENT ID ----- >>> '||l_xmlg_document_id,1);
243 cln_debug_pub.Add('DOCUMENT DIRECTION ----- >>> '||'OUT',1);
244 cln_debug_pub.Add('TRADING PARTNER TYPE ----- >>> '||l_tr_partner_type,1);
245 cln_debug_pub.Add('TRADING PARTNER ID ----- >>> '||l_tr_partner_id,1);
246 cln_debug_pub.Add('TRADING PARTNER SITE ----- >>> '||l_tr_partner_site,1);
247 cln_debug_pub.Add('TRADING PARTNER NAME ----- >>> '||l_party_name,1);
248 cln_debug_pub.Add('======================================= ',1);
249 END IF;
250
251
252 IF (l_Debug_Level <= 1) THEN
253 cln_debug_pub.Add('Workflow event- oracle.apps.cln.inv.genreport', 1);
254 END IF;
255
256 WF_EVENT.Raise('oracle.apps.cln.inv.genreport',l_event_key, NULL, l_cln_inv_parameters, NULL);
257
258 x_retcode := 0;
259 x_errbuf := 'Successful';
260
261 -- check the error message
262 IF (l_Debug_Level <= 1) THEN
263 cln_debug_pub.Add(l_msg_data,1);
264 END IF;
265
266 IF (l_Debug_Level <= 2) THEN
267 cln_debug_pub.Add('------- Exiting RAISE_REPORT_GEN_EVENT API --------- ',2);
268 END IF;
269
270
271 -- Exception Handling
272 EXCEPTION
273
274 WHEN FND_API.G_EXC_ERROR THEN
275 x_retcode := 2 ;
276 x_errbuf := l_msg_data;
277 IF (l_Debug_Level <= 5) THEN
278 cln_debug_pub.Add(l_msg_data,4);
279 cln_debug_pub.Add('------- Exiting RAISE_REPORT_GEN_EVENT API --------- ',2);
280 END IF;
281
282
283 WHEN OTHERS THEN
284 l_error_code :=SQLCODE;
285 l_error_msg :=SQLERRM;
286 x_retcode :=2 ;
287 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
288 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
289 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
290 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
291 x_errbuf := l_msg_data;
292 IF (l_Debug_Level <= 5) THEN
293 cln_debug_pub.Add(l_msg_data,6);
294 cln_debug_pub.Add('------- Exiting RAISE_REPORT_GEN_EVENT API --------- ',2);
295 END IF;
296
297
298 END RAISE_REPORT_GEN_EVENT;
299
300
301 -- Name
302 -- GET_XML_TAG_VALUES
303 -- Purpose
304 -- The main purpose ofthis API is to call the inventory API - INVPQTTS.pls and
305 -- based on the user input through concurrent program and also using the profile
306 -- option calculate quantity on hand, avaliable to use , quantity blocked and allocated.
307 --
308 -- Arguments
309 --
310 -- Notes
311 -- No specific notes.
312
313 PROCEDURE GET_XML_TAG_VALUES(
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_data OUT NOCOPY VARCHAR2,
316 p_inv_org IN NUMBER,
317 p_diposition_available IN VARCHAR2,
318 p_diposition_blocked IN VARCHAR2,
319 p_diposition_allocated IN VARCHAR2,
320 p_sub_inv IN VARCHAR2,
321 p_lot_number IN VARCHAR2,
322 p_item_number IN NUMBER,
323 p_item_revision IN VARCHAR2,
324 p_lot_ctrl_number IN NUMBER,
325 p_item_revision_ctrl_number IN NUMBER,
326 p_tp_type IN VARCHAR2,
327 p_tp_id IN NUMBER,
328 p_tp_site_id IN VARCHAR2,
329 p_xmlg_transaction_type IN VARCHAR2, --
330 p_xmlg_transaction_subtype IN VARCHAR2, --
331 p_xmlg_document_id IN VARCHAR2, --
332 p_xml_event_key IN VARCHAR2, --
333 p_xmlg_internal_control_number IN NUMBER, --
334 x_customer_item_number OUT NOCOPY VARCHAR2,
335 x_quantity_on_hand OUT NOCOPY NUMBER,
336 x_quantity_blocked OUT NOCOPY NUMBER,
337 x_quantity_allocated OUT NOCOPY NUMBER )
338 IS
339
340 l_error_code NUMBER;
341 l_error_msg VARCHAR2(2000);
342 l_msg_data VARCHAR2(255);
343 l_debug_mode VARCHAR2(255);
344 l_revision VARCHAR2(255);
345 l_return_status VARCHAR2(255);
346 l_doc_status VARCHAR2(20);
347 l_msg_count NUMBER;
348
349 -- QUANTITY ---
350 l_qty_on_hand NUMBER;
351 l_reserved_qty_on_hand NUMBER;
352 l_quantity_reserved NUMBER;
353 l_quantity_suggested NUMBER;
354 l_available_to_transaction NUMBER;
355 l_available_to_reserve NUMBER;
356 l_blocked_qty NUMBER;
357
358 l_is_revision_control BOOLEAN;
359 l_is_lot_control BOOLEAN;
360
361 l_inv_atp_code NUMBER;
362 l_availability_type NUMBER;
363 l_tree_mode NUMBER;
364 l_onhand_source NUMBER;
365 l_coll_id NUMBER;
366
367 ----FOR PROFILE-----
368 l_profile_blocked_qty_eq VARCHAR2(200);
369
370 BEGIN
371
372 -- Sets the debug mode to be FILE
373 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
374
375 IF (l_Debug_Level <= 2) THEN
376 cln_debug_pub.Add('----- Entering GET_XML_TAG_VALUES API ------- ',2);
377 END IF;
378
379
380 -- Initialize API return status to success
381 l_msg_data := 'successfully obtained quantity values for On Hand/ Available To use / Blocked / Allocated Items';
382
383 -- Parameters received
384 IF (l_Debug_Level <= 1) THEN
385 cln_debug_pub.Add('== PARAMETERS RECEIVED FROM XGM================== ',1);
386 cln_debug_pub.Add('Inventory Organization - '||p_inv_org,1);
387 cln_debug_pub.Add('Inventory Disposition (Available) - '||p_diposition_available,1);
388 cln_debug_pub.Add('Inventory Disposition (Blocked) - '||p_diposition_blocked,1);
389 cln_debug_pub.Add('Inventory Disposition (Allocated - '||p_diposition_allocated,1);
390 cln_debug_pub.Add('Sub Inventory - '||p_sub_inv,1);
391 cln_debug_pub.Add('Lot Number - '||p_lot_number,1);
392 cln_debug_pub.Add('Item Number (ID) - '||p_item_number,1);
393 cln_debug_pub.Add('Item Revision - '||p_item_revision,1);
394 cln_debug_pub.Add('Trading Partner Type - '||p_tp_type,1);
395 cln_debug_pub.Add('Trading Partner ID - '||p_tp_id,1);
396 cln_debug_pub.Add('Lot Control Number - '||p_lot_ctrl_number,1);
397 cln_debug_pub.Add('Item Rev Control Number - '||p_item_revision_ctrl_number,1);
398 cln_debug_pub.Add('Trading Partner Site ID - '||p_tp_site_id,1);
399 cln_debug_pub.Add('XMLG Transaction Type - '||p_xmlg_transaction_type,1);
400 cln_debug_pub.Add('XMLG Transaction Sub Type - '||p_xmlg_transaction_subtype,1);
401 cln_debug_pub.Add('XMLG Document ID - '||p_xmlg_document_id,1);
402 cln_debug_pub.Add('XML Event Key - '||p_xml_event_key,1);
403 cln_debug_pub.Add('Internal Control Number - '||p_xmlg_internal_control_number,1);
404 cln_debug_pub.Add('=================================================',1);
405 END IF;
406
407
408
409 IF (p_item_revision_ctrl_number = 2) THEN
410 IF (p_item_revision IS NULL) THEN
411 l_is_revision_control := FALSE;
412
413 IF (l_Debug_Level <= 1) THEN
414 cln_debug_pub.Add('Set the value of l_is_revision_control = FALSE',1);
415 END IF;
416 ELSE
417 l_is_revision_control := TRUE;
418
419 IF (l_Debug_Level <= 1) THEN
420 cln_debug_pub.Add('Set the value of l_is_revision_control = TRUE',1);
421 END IF;
422 END IF;
423 END IF;
424
425 IF (p_lot_ctrl_number = 2) THEN
426 IF (p_lot_number IS NULL) THEN
427 l_is_lot_control := FALSE;
428
429 IF (l_Debug_Level <= 1) THEN
430 cln_debug_pub.Add('Set the value of l_is_lot_control = FALSE',1);
431 END IF;
432 ELSE
433 l_is_lot_control := TRUE;
434
435 IF (l_Debug_Level <= 1) THEN
436 cln_debug_pub.Add('Set the value of l_is_lot_control = TRUE',1);
437 END IF;
438 END IF;
439 END IF;
440
441 IF( p_tp_type = 'C') THEN
442 IF (l_Debug_Level <= 1) THEN
443 cln_debug_pub.Add('Check for the Customer type- C and assign the item name accordingly',1);
444 END IF;
445
446 BEGIN
447 SELECT mci.customer_item_number
448 INTO x_customer_item_number
449 FROM
450 mtl_customer_items mci ,
451 mtl_customer_item_xrefs mcix
452 WHERE mcix.master_organization_id = p_inv_org
453 AND mcix.inventory_item_id = p_item_number
454 AND mci.customer_id = p_tp_id
455 AND mci.customer_item_id(+) = mcix.customer_item_id;
456 EXCEPTION
457 WHEN NO_DATA_FOUND THEN
458 IF (l_Debug_Level <= 1) THEN
459 cln_debug_pub.Add('Customer Item Number Not Found ',1);
460 END IF;
461
462 END;
463 IF (l_Debug_Level <= 1) THEN
464 cln_debug_pub.Add('Item number defined for the customer is :'||x_customer_item_number,1);
465 END IF;
466
467 END IF;
468
469
470 -- Check for Profile value of Blocked Quantity
471 IF (l_Debug_Level <= 1) THEN
472 cln_debug_pub.Add('Check for the profile value of the Blocked quantity',1);
473 END IF;
474
475 l_profile_blocked_qty_eq := FND_PROFILE.VALUE('CLN_DEF_BLOCKED_QTY');
476 IF (l_Debug_Level <= 1) THEN
477 cln_debug_pub.Add('Blocked Quantity Eq set up in Profile value :'||l_profile_blocked_qty_eq,1);
478 END IF;
479
480
481 IF ( l_profile_blocked_qty_eq = 'NON_NETTABLE') THEN
482 l_tree_mode := 2;
483 l_onhand_source := 2;
484 BEGIN
485 SELECT nvl(sum(moqd.primary_transaction_quantity),0)
486 INTO x_quantity_blocked
487 FROM mtl_onhand_quantities_detail moqd, mtl_secondary_inventories msi
488 WHERE moqd.organization_id = p_inv_org
489 AND moqd.inventory_item_id = p_item_number
490 AND moqd.subinventory_code = msi.secondary_inventory_name
491 AND moqd.organization_id = msi.organization_id
492 AND msi.availability_type = 2
493 AND moqd.subinventory_code = nvl(p_sub_inv,moqd.subinventory_code)
494 AND (p_item_revision IS NULL OR moqd.revision IS NULL OR moqd.revision = p_item_revision)
495 AND (p_lot_number IS NULL OR moqd.lot_number = p_lot_number);
496 EXCEPTION
497 WHEN NO_DATA_FOUND THEN
498 -- unreached code
499 IF (l_Debug_Level <= 1) THEN
500 cln_debug_pub.Add('No Rows selected from the SQL statement used for calculating Blocked Qty',1);
501 END IF;
502
503 END;
504 IF (l_Debug_Level <= 1) THEN
505 cln_debug_pub.Add('Blocked Quantity :'||x_quantity_blocked,1);
506 END IF;
507
508 ELSIF ( l_profile_blocked_qty_eq = 'NON_ATPABLE') THEN
509 l_tree_mode := 2;
510 l_onhand_source := 1;
511
512 BEGIN
513 SELECT nvl(sum(moqd.primary_transaction_quantity),0)
514 INTO x_quantity_blocked
515 FROM mtl_onhand_quantities_detail moqd, mtl_secondary_inventories msi
516 WHERE moqd.organization_id = p_inv_org
517 AND moqd.inventory_item_id = p_item_number
518 AND moqd.subinventory_code = msi.secondary_inventory_name
519 AND moqd.organization_id = msi.organization_id
520 AND msi.inventory_atp_code = 2
521 AND moqd.subinventory_code = nvl(p_sub_inv,moqd.subinventory_code)
522 AND (p_item_revision IS NULL OR moqd.revision IS NULL OR moqd.revision = p_item_revision)
523 AND (p_lot_number IS NULL OR moqd.lot_number = p_lot_number);
524 EXCEPTION
525 WHEN NO_DATA_FOUND THEN
526 -- unreached code
527 IF (l_Debug_Level <= 1) THEN
528 cln_debug_pub.Add('No Rows selected from the SQL statement used for calculating Blocked Qty',1);
529 END IF;
530
531 END;
532 IF (l_Debug_Level <= 1) THEN
533 cln_debug_pub.Add('Blocked Quantity :'||x_quantity_blocked,1);
534 END IF;
535
536 ELSIF ( l_profile_blocked_qty_eq = 'NON_NETTABLE_OR_NON_ATPABLE') THEN
537 l_tree_mode := 2;
538 l_onhand_source := inv_quantity_tree_pvt.g_atpable_nettable_only;
539
540 BEGIN
541 SELECT nvl(sum(moqd.primary_transaction_quantity),0)
542 INTO x_quantity_blocked
543 FROM mtl_onhand_quantities_detail moqd, mtl_secondary_inventories msi
544 WHERE moqd.organization_id = p_inv_org
545 AND moqd.inventory_item_id = p_item_number
546 AND moqd.subinventory_code = msi.secondary_inventory_name
547 AND moqd.organization_id = msi.organization_id
548 AND (msi.inventory_atp_code = 2 OR msi.availability_type = 2)
549 AND moqd.subinventory_code = nvl(p_sub_inv,moqd.subinventory_code)
550 AND (p_item_revision IS NULL OR moqd.revision IS NULL OR moqd.revision = p_item_revision)
551 AND (p_lot_number IS NULL OR moqd.lot_number = p_lot_number);
552 EXCEPTION
553 WHEN NO_DATA_FOUND THEN
554 -- unreached code
555 IF (l_Debug_Level <= 1) THEN
556 cln_debug_pub.Add('No Rows selected from the SQL statement used for calculating Blocked Qty',1);
557 END IF;
558
559 END;
560 IF (l_Debug_Level <= 1) THEN
561 cln_debug_pub.Add('Blocked Quantity :'||x_quantity_blocked,1);
562 END IF;
563
564 END IF;
565
566 IF (l_Debug_Level <= 1) THEN
567 cln_debug_pub.Add('Tree Mode : '||l_tree_mode,1);
568 cln_debug_pub.Add('OnHand Source : '||l_onhand_source,1);
569
570 cln_debug_pub.Add('Call Inventory API .....',1);
571 END IF;
572
573
574 inv_quantity_tree_pub.query_quantities
575 ( p_api_version_number => 1
576 , p_init_msg_lst => fnd_api.g_true
577 , x_return_status => x_return_status
578 , x_msg_count => l_msg_count
579 , x_msg_data => l_msg_data
580 , p_organization_id => p_inv_org
581 , p_inventory_item_id => p_item_number
582 , p_tree_mode => l_tree_mode
583 , p_is_revision_control => l_is_revision_control
584 , p_is_lot_control => l_is_lot_control
585 , p_is_serial_control => false
586 -- , p_demand_source_type_id IN NUMBER DEFAULT -9999
587 -- , p_demand_source_header_id IN NUMBER DEFAULT -9999
588 -- , p_demand_source_line_id IN NUMBER DEFAULT -9999
589 -- , p_demand_source_name IN VARCHAR2 DEFAULT NULL
590 -- , p_lot_expiration_date IN DATE DEFAULT NULL
591 , p_revision => p_item_revision
592 , p_lot_number => p_lot_number
593 , p_subinventory_code => p_sub_inv
594 , p_locator_id => null
595 , p_onhand_source => l_onhand_source
596 , x_qoh => l_qty_on_hand
597 , x_rqoh => l_reserved_qty_on_hand
598 , x_qr => l_quantity_reserved
599 , x_qs => l_quantity_suggested
600 , x_att => l_available_to_transaction
601 , x_atr => l_available_to_reserve
602 -- , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
603 -- , p_cost_group_id IN NUMBER DEFAULT NULL
604 -- , p_lpn_id IN NUMBER DEFAULT NULL
605 -- , p_transfer_locator_id IN NUMBER DEFAULT NULL
606 );
607
608
609 IF (l_Debug_Level <= 1) THEN
610 cln_debug_pub.Add('== PARAMETERS RECEIVED FROM INVENTORY API== ',1);
611 cln_debug_pub.Add('Return Status - '||x_return_status,1);
612 cln_debug_pub.Add('Message Count - '||l_msg_count,1);
613 cln_debug_pub.Add('Message Data - '||l_msg_data,1);
614 cln_debug_pub.Add('Quanity on Hand - '||l_qty_on_hand,1);
615 cln_debug_pub.Add('Reserved Quanity on Hand - '||l_reserved_qty_on_hand,1);
616 cln_debug_pub.Add('Quantity Reserved - '||l_quantity_reserved,1);
617 cln_debug_pub.Add('Quanity Suggested - '||l_quantity_suggested,1);
618 cln_debug_pub.Add('Quantity Available for Transaction - '||l_available_to_transaction,1);
619 cln_debug_pub.Add('Quantity Available to Reserve - '||l_available_to_reserve,1);
620 cln_debug_pub.Add('=================================================',1);
621 END IF;
622
623
624 IF ( x_return_status <> 'S') THEN
625 x_msg_data := l_msg_data;
626 l_msg_data :='Error in Inventory API. Detail error msg -> '||l_msg_data;
627 IF (l_Debug_Level <= 2) THEN
628 cln_debug_pub.Add(' Error : '||l_msg_data,2);
629 END IF;
630
631 RAISE FND_API.G_EXC_ERROR;
632 END IF;
633
634
635 IF((p_diposition_blocked = 'Null') OR (p_diposition_blocked IS NULL)) THEN
636 x_quantity_blocked := NULL;
637 END IF;
638
639 IF((p_diposition_allocated = 'Null') OR (p_diposition_allocated IS NULL)) THEN
640 x_quantity_allocated := NULL;
641 ELSE
642 x_quantity_allocated := l_quantity_reserved + l_quantity_suggested;
643 END IF;
644 IF (l_Debug_Level <= 1) THEN
645 cln_debug_pub.Add('Allocated Quantity : '||x_quantity_allocated,1);
646 cln_debug_pub.Add('Blocked Quantity : '||x_quantity_blocked,1);
647 END IF;
648
649
650 IF (p_diposition_available = 'Available To Use') THEN
651 x_quantity_on_hand := l_qty_on_hand - (l_quantity_reserved + l_quantity_suggested);-- qty on hand already takes out blocked
652 ELSE
653 IF (l_Debug_Level <= 1) THEN
654 cln_debug_pub.Add('Call Inventory API for calculating On Hand Qty : ',1);
655 cln_debug_pub.Add('Tree Mode : '||2,1);
656 cln_debug_pub.Add('OnHand Source : '||3,1);
657 END IF;
658
659
660 inv_quantity_tree_pub.query_quantities
661 ( p_api_version_number => 1
662 , p_init_msg_lst => fnd_api.g_true
663 , x_return_status => x_return_status
664 , x_msg_count => l_msg_count
665 , x_msg_data => l_msg_data
666 , p_organization_id => p_inv_org
667 , p_inventory_item_id => p_item_number
668 , p_tree_mode => 2
669 , p_is_revision_control => l_is_revision_control
670 , p_is_lot_control => l_is_lot_control
671 , p_is_serial_control => false
672 -- , p_demand_source_type_id IN NUMBER DEFAULT -9999
673 -- , p_demand_source_header_id IN NUMBER DEFAULT -9999
674 -- , p_demand_source_line_id IN NUMBER DEFAULT -9999
675 -- , p_demand_source_name IN VARCHAR2 DEFAULT NULL
676 -- , p_lot_expiration_date IN DATE DEFAULT NULL
677 , p_revision => p_item_revision
678 , p_lot_number => p_lot_number
679 , p_subinventory_code => p_sub_inv
680 , p_locator_id => null
681 , p_onhand_source => 3
682 , x_qoh => l_qty_on_hand
683 , x_rqoh => l_reserved_qty_on_hand
684 , x_qr => l_quantity_reserved
685 , x_qs => l_quantity_suggested
686 , x_att => l_available_to_transaction
687 , x_atr => l_available_to_reserve
688 -- , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
689 -- , p_cost_group_id IN NUMBER DEFAULT NULL
690 -- , p_lpn_id IN NUMBER DEFAULT NULL
691 -- , p_transfer_locator_id IN NUMBER DEFAULT NULL
692 );
693
694 IF ( x_return_status <> 'S') THEN
695 x_msg_data := l_msg_data;
696 l_msg_data :='Error in Inventory API. Detail error msg -> '||l_msg_data;
697 IF (l_Debug_Level <= 1) THEN
698 cln_debug_pub.Add(' Error : '||l_msg_data,1);
699 END IF;
700
701 RAISE FND_API.G_EXC_ERROR;
702 END IF;
703 x_quantity_on_hand := l_qty_on_hand;
704 END IF;
705
706 IF (l_Debug_Level <= 1) THEN
707 cln_debug_pub.Add('On Hand Quantity is = '||x_quantity_on_hand,1);
708 END IF;
709
710
711 FND_MESSAGE.SET_NAME('CLN','CLN_INV_REPORT_GENERATED');
712 l_msg_data := FND_MESSAGE.GET;
713 IF (l_Debug_Level <= 1) THEN
714 cln_debug_pub.Add('Success Message : '||l_msg_data,1);
715 END IF;
716
717 IF (l_Debug_Level <= 2) THEN
718 cln_debug_pub.Add('------- Exiting GET_XML_TAG_VALUES API --------- ',2);
719 END IF;
720
721 x_return_status := 'S';
722
723 -- Exception Handling
724 EXCEPTION
725 WHEN FND_API.G_EXC_ERROR THEN
726 x_return_status := FND_API.G_RET_STS_ERROR ;
727 IF (l_Debug_Level <= 5) THEN
728 cln_debug_pub.Add(l_msg_data,4);
729 cln_debug_pub.Add('------- Exiting GET_XML_TAG_VALUES API --------- ',2);
730 END IF;
731
732
733 WHEN OTHERS THEN
734 l_error_code :=SQLCODE;
735 l_error_msg :=SQLERRM;
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
737 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
738 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
739 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
740 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
741 x_msg_data := l_msg_data;
742 IF (l_Debug_Level <= 5) THEN
743 cln_debug_pub.Add(l_msg_data,6);
744 cln_debug_pub.Add('------- Exiting GET_XML_TAG_VALUES API --------- ',2);
745 END IF;
746
747
748 END GET_XML_TAG_VALUES;
749
750 END CLN_SYNC_INVENTORY_PKG;