[Home] [Help]
43:
44: PROCEDURE GET_YES_NO( x_option OUT NOCOPY t_genref) IS
45: BEGIN
46: OPEN x_option for
47: SELECT 'YES' FROM DUAL
48: UNION
49: SELECT 'NO' FROM DUAL;
50: END get_yes_no;
51:
45: BEGIN
46: OPEN x_option for
47: SELECT 'YES' FROM DUAL
48: UNION
49: SELECT 'NO' FROM DUAL;
50: END get_yes_no;
51:
52: PROCEDURE GET_YES_NO( x_option OUT NOCOPY t_genref
53: , p_option IN VARCHAR2) IS
52: PROCEDURE GET_YES_NO( x_option OUT NOCOPY t_genref
53: , p_option IN VARCHAR2) IS
54: BEGIN
55: OPEN x_option for
56: SELECT 'YES' FROM DUAL WHERE 'YES' LIKE upper(p_option)
57: UNION
58: SELECT 'NO' FROM DUAL WHERE 'NO' LIKE upper(p_option);
59: END get_yes_no;
60:
54: BEGIN
55: OPEN x_option for
56: SELECT 'YES' FROM DUAL WHERE 'YES' LIKE upper(p_option)
57: UNION
58: SELECT 'NO' FROM DUAL WHERE 'NO' LIKE upper(p_option);
59: END get_yes_no;
60:
61:
62: PROCEDURE get_grade_codes ( x_grades OUT NOCOPY t_genref
93: * A new api has been written to populate the lot attributes given a lot, item and parent lot.
94: * The API details can be found in the Create Lot Api TDD. Since there is no direct way to return
95: * plsql tables back to the java client, this procedure is meant to act as a wrapper over
96: * populate_lot_attributes, which returns the attributes in a plsql table.
97: * These attributes are selected from dual and passed back as a ref cursor to the client.
98: **/
99: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
100: BEGIN
101:
161: open x_item_lot_att for
162: SELECT tracking_quantity_ind
163: , secondary_default_ind
164: , secondary_uom_code
165: , dual_uom_deviation_high
166: , dual_uom_deviation_low
167: , grade_control_flag
168: , default_grade
169: , child_lot_flag
162: SELECT tracking_quantity_ind
163: , secondary_default_ind
164: , secondary_uom_code
165: , dual_uom_deviation_high
166: , dual_uom_deviation_low
167: , grade_control_flag
168: , default_grade
169: , child_lot_flag
170: , retest_interval
430: , p_lot_number IN VARCHAR2
431: ) IS
432: /**
433: * This API populates the lot attributes for a new Lot based on attributes of its parent lot.
434: * These attributes are selected from dual and passed back as a ref cursor to the client.
435: **/
436: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
437: BEGIN
438:
659: x_hold_date,
660: x_maturity_date,
661: x_retest_date,
662: x_expiration_date
663: FROM dual ;
664:
665:
666:
667: EXCEPTION
1153: P_ATTRIBUTE12 MTL_LOT_NUMBERS.ATTRIBUTE12%TYPE,
1154: P_ATTRIBUTE13 MTL_LOT_NUMBERS.ATTRIBUTE13%TYPE,
1155: P_ATTRIBUTE14 MTL_LOT_NUMBERS.ATTRIBUTE14%TYPE,
1156: P_ATTRIBUTE15 MTL_LOT_NUMBERS.ATTRIBUTE15%TYPE,
1157: P_ITEM_DUAL_UOM_CONTROL IN VARCHAR2 , -- hold item's Tracking indicator
1158: P_copy_pnt_lot_att_flag IN VARCHAR2 ,
1159: p_secondary_default_ind IN VARCHAR2 ,
1160: p_disable_flag IN MTL_LOT_NUMBERS.DISABLE_FLAG%TYPE DEFAULT NULL, -- 4239238 Start
1161: p_territory_code IN MTL_LOT_NUMBERS.TERRITORY_CODE%TYPE DEFAULT NULL,
1215: L_LOT_UOM_CONVERSION VARCHAR2(10);
1216: l_sec_qty Number := 0;
1217: l_primary_uom MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1218: l_primary_quantity NUMBER ;
1219: l_ITEM_DUAL_UOM_CONTROL mtl_system_items.tracking_quantity_ind%TYPE ;
1220: l_copy_pnt_lot_att_flag mtl_system_items.copy_lot_attribute_flag%TYPE ;
1221: l_secondary_default_ind mtl_system_items.secondary_default_ind%TYPE ;
1222: l_secondary_uom_code mtl_system_items.secondary_uom_code%TYPE;
1223: /* Cursor definition to get Item attributes*/
1258: * This will also take care of copying Parent's UOM Conv record for child lot
1259: */
1260: l_primary_uom := NULL; --p_primary_uom ;
1261: l_primary_quantity := NULL; --p_primary_quantity ;
1262: l_ITEM_DUAL_UOM_CONTROL := NULL; --p_item_dual_uom_control ;
1263: l_copy_pnt_lot_att_flag := NULL; --p_copy_pnt_lot_att_flag ;
1264: l_secondary_default_ind := NULL; --p_secondary_default_ind ;
1265: l_secondary_uom_code := NULL;-- p_secondary_uom_code;
1266: -- Spr_Debug('1');
1404: END IF; -- COUNT check
1405: /*
1406: * Step 2..Checking if lot specific UOM conversion are needed or not
1407: */
1408: -- l_ITEM_DUAL_UOM_CONTROL IN VARCHAR2 is a new parameter, hold item's Tracking indicator
1409: -- P_TRANSACTION_QUANTITY IN NUMBER
1410: -- P_SECONDARY_QUANTITY IN NUMBER
1411: -- checking for lots UOM conversion rate
1412: -- Spr_Debug('4: '||l_ITEM_DUAL_UOM_CONTROL );
1408: -- l_ITEM_DUAL_UOM_CONTROL IN VARCHAR2 is a new parameter, hold item's Tracking indicator
1409: -- P_TRANSACTION_QUANTITY IN NUMBER
1410: -- P_SECONDARY_QUANTITY IN NUMBER
1411: -- checking for lots UOM conversion rate
1412: -- Spr_Debug('4: '||l_ITEM_DUAL_UOM_CONTROL );
1413: -- Spr_Debug('4.2: primary_uom_code UOM '||l_primary_uom );
1414:
1415: -- Check if item is dual controlled.
1416: -- if not then return from here, no need to create UOM conversion record.
1411: -- checking for lots UOM conversion rate
1412: -- Spr_Debug('4: '||l_ITEM_DUAL_UOM_CONTROL );
1413: -- Spr_Debug('4.2: primary_uom_code UOM '||l_primary_uom );
1414:
1415: -- Check if item is dual controlled.
1416: -- if not then return from here, no need to create UOM conversion record.
1417: IF l_get_item_attr.tracking_quantity_ind <> 'PS' THEN
1418:
1419: x_return_status := l_return_status ;
1429: IF l_secondary_uom_code IS NULL THEN
1430: l_secondary_uom_code := l_get_item_attr.secondary_uom_code;
1431: END IF;
1432: -- Spr_Debug('4.5: primary_uom_code UOM '||l_primary_uom );
1433: IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1434: l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1435: END IF;
1436:
1437: IF l_secondary_default_ind IS NULL THEN
1430: l_secondary_uom_code := l_get_item_attr.secondary_uom_code;
1431: END IF;
1432: -- Spr_Debug('4.5: primary_uom_code UOM '||l_primary_uom );
1433: IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1434: l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1435: END IF;
1436:
1437: IF l_secondary_default_ind IS NULL THEN
1438: l_secondary_default_ind := l_get_item_attr.secondary_default_ind ;
1472: );
1473: END IF;
1474: -- Spr_Debug('7: '||l_primary_quantity);
1475:
1476: IF l_ITEM_DUAL_UOM_CONTROL = 'PS' THEN
1477: l_conversion_rate := NVL( NVL(P_TRANSACTION_QUANTITY,1) / NVL(P_SECONDARY_QUANTITY,1) ,1);
1478: IF l_conversion_rate <= 0 THEN
1479: l_conversion_rate := 1;
1480: END IF;
1509: /* IF l_primary_uom IS NULL THEN
1510: l_primary_uom := l_get_item_attr.primary_uom_code ;
1511: END IF;
1512:
1513: IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1514: l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1515: END IF;
1516:
1517: IF l_secondary_default_ind IS NULL THEN
1510: l_primary_uom := l_get_item_attr.primary_uom_code ;
1511: END IF;
1512:
1513: IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1514: l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1515: END IF;
1516:
1517: IF l_secondary_default_ind IS NULL THEN
1518: l_secondary_default_ind := l_get_item_attr.secondary_default_ind ;
1618: l_to_uom_class := NULL;
1619: END;
1620: -- Spr_Debug('145: from '|| l_from_uom_class || ',to '|| l_to_uom_class );
1621: -- l_secondary_default_ind local variable
1622: -- Spr_Debug('150: '||l_LOT_UOM_CONVERSION||' , CRT UOM CON'||l_create_lot_uom_conv ||' ,ITM DUAL COTR '||l_ITEM_DUAL_UOM_CONTROL);
1623: l_LOT_UOM_CONVERSION := 'FALSE';
1624:
1625: IF NVL(l_create_lot_uom_conv,1 ) = 1 -- for 1 1 Means Yes
1626: AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1622: -- Spr_Debug('150: '||l_LOT_UOM_CONVERSION||' , CRT UOM CON'||l_create_lot_uom_conv ||' ,ITM DUAL COTR '||l_ITEM_DUAL_UOM_CONTROL);
1623: l_LOT_UOM_CONVERSION := 'FALSE';
1624:
1625: IF NVL(l_create_lot_uom_conv,1 ) = 1 -- for 1 1 Means Yes
1626: AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1627: AND l_from_uom_class <> l_to_uom_class THEN
1628: L_LOT_UOM_CONVERSION := 'TRUE';
1629: -- Spr_Debug('160: '||l_LOT_UOM_CONVERSION);
1630: ELSIF NVL(l_create_lot_uom_conv, 1 ) = 3 --for 3 Means User Defined
1627: AND l_from_uom_class <> l_to_uom_class THEN
1628: L_LOT_UOM_CONVERSION := 'TRUE';
1629: -- Spr_Debug('160: '||l_LOT_UOM_CONVERSION);
1630: ELSIF NVL(l_create_lot_uom_conv, 1 ) = 3 --for 3 Means User Defined
1631: AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1632: AND l_from_uom_class <> l_to_uom_class THEN
1633: IF p_response = 'Y' Then
1634: l_response := 1;
1635: Else
1661: -- Spr_Debug('220: '||l_LOT_UOM_CONVERSION);
1662: ELSE
1663: -- always YES
1664: IF NVL(l_create_lot_uom_conv, 1 ) = 1
1665: AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1666: AND l_from_uom_class <> l_to_uom_class THEN
1667: l_go := TRUE;
1668: -- Spr_Debug('230: '||l_LOT_UOM_CONVERSION);
1669: -- user response
1667: l_go := TRUE;
1668: -- Spr_Debug('230: '||l_LOT_UOM_CONVERSION);
1669: -- user response
1670: ELSIF NVL(l_create_lot_uom_conv,1 ) = 3
1671: AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1672: AND l_from_uom_class <> l_to_uom_class THEN
1673: IF NVL(l_LOT_UOM_CONVERSION,'FALSE') = 'TRUE' THEN
1674: l_go := TRUE;
1675: -- copy conversion from parent lot,if exists