[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,
1217: L_LOT_UOM_CONVERSION VARCHAR2(10);
1218: l_sec_qty Number := 0;
1219: l_primary_uom MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1220: l_primary_quantity NUMBER ;
1221: l_ITEM_DUAL_UOM_CONTROL mtl_system_items.tracking_quantity_ind%TYPE ;
1222: l_copy_pnt_lot_att_flag mtl_system_items.copy_lot_attribute_flag%TYPE ;
1223: l_secondary_default_ind mtl_system_items.secondary_default_ind%TYPE ;
1224: l_secondary_uom_code mtl_system_items.secondary_uom_code%TYPE;
1225: /* Cursor definition to get Item attributes*/
1262: * This will also take care of copying Parent's UOM Conv record for child lot
1263: */
1264: l_primary_uom := NULL; --p_primary_uom ;
1265: l_primary_quantity := NULL; --p_primary_quantity ;
1266: l_ITEM_DUAL_UOM_CONTROL := NULL; --p_item_dual_uom_control ;
1267: l_copy_pnt_lot_att_flag := NULL; --p_copy_pnt_lot_att_flag ;
1268: l_secondary_default_ind := NULL; --p_secondary_default_ind ;
1269: l_secondary_uom_code := NULL;-- p_secondary_uom_code;
1270: -- Spr_Debug('1');
1410: END IF; -- COUNT check
1411: /*
1412: * Step 2..Checking if lot specific UOM conversion are needed or not
1413: */
1414: -- l_ITEM_DUAL_UOM_CONTROL IN VARCHAR2 is a new parameter, hold item's Tracking indicator
1415: -- P_TRANSACTION_QUANTITY IN NUMBER
1416: -- P_SECONDARY_QUANTITY IN NUMBER
1417: -- checking for lots UOM conversion rate
1418: -- Spr_Debug('4: '||l_ITEM_DUAL_UOM_CONTROL );
1414: -- l_ITEM_DUAL_UOM_CONTROL IN VARCHAR2 is a new parameter, hold item's Tracking indicator
1415: -- P_TRANSACTION_QUANTITY IN NUMBER
1416: -- P_SECONDARY_QUANTITY IN NUMBER
1417: -- checking for lots UOM conversion rate
1418: -- Spr_Debug('4: '||l_ITEM_DUAL_UOM_CONTROL );
1419: -- Spr_Debug('4.2: primary_uom_code UOM '||l_primary_uom );
1420:
1421: -- Check if item is dual controlled.
1422: -- if not then return from here, no need to create UOM conversion record.
1417: -- checking for lots UOM conversion rate
1418: -- Spr_Debug('4: '||l_ITEM_DUAL_UOM_CONTROL );
1419: -- Spr_Debug('4.2: primary_uom_code UOM '||l_primary_uom );
1420:
1421: -- Check if item is dual controlled.
1422: -- if not then return from here, no need to create UOM conversion record.
1423: IF l_get_item_attr.tracking_quantity_ind <> 'PS' THEN
1424:
1425: x_return_status := l_return_status ;
1435: IF l_secondary_uom_code IS NULL THEN
1436: l_secondary_uom_code := l_get_item_attr.secondary_uom_code;
1437: END IF;
1438: -- Spr_Debug('4.5: primary_uom_code UOM '||l_primary_uom );
1439: IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1440: l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1441: END IF;
1442:
1443: IF l_secondary_default_ind IS NULL THEN
1436: l_secondary_uom_code := l_get_item_attr.secondary_uom_code;
1437: END IF;
1438: -- Spr_Debug('4.5: primary_uom_code UOM '||l_primary_uom );
1439: IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1440: l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1441: END IF;
1442:
1443: IF l_secondary_default_ind IS NULL THEN
1444: l_secondary_default_ind := l_get_item_attr.secondary_default_ind ;
1478: );
1479: END IF;
1480: -- Spr_Debug('7: '||l_primary_quantity);
1481:
1482: IF l_ITEM_DUAL_UOM_CONTROL = 'PS' THEN
1483: l_conversion_rate := NVL( NVL(P_TRANSACTION_QUANTITY,1) / NVL(P_SECONDARY_QUANTITY,1) ,1);
1484: IF l_conversion_rate <= 0 THEN
1485: l_conversion_rate := 1;
1486: END IF;
1515: /* IF l_primary_uom IS NULL THEN
1516: l_primary_uom := l_get_item_attr.primary_uom_code ;
1517: END IF;
1518:
1519: IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1520: l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1521: END IF;
1522:
1523: IF l_secondary_default_ind IS NULL THEN
1516: l_primary_uom := l_get_item_attr.primary_uom_code ;
1517: END IF;
1518:
1519: IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1520: l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1521: END IF;
1522:
1523: IF l_secondary_default_ind IS NULL THEN
1524: l_secondary_default_ind := l_get_item_attr.secondary_default_ind ;
1624: l_to_uom_class := NULL;
1625: END;
1626: -- Spr_Debug('145: from '|| l_from_uom_class || ',to '|| l_to_uom_class );
1627: -- l_secondary_default_ind local variable
1628: -- Spr_Debug('150: '||l_LOT_UOM_CONVERSION||' , CRT UOM CON'||l_create_lot_uom_conv ||' ,ITM DUAL COTR '||l_ITEM_DUAL_UOM_CONTROL);
1629: l_LOT_UOM_CONVERSION := 'FALSE';
1630:
1631: IF NVL(l_create_lot_uom_conv,1 ) = 1 -- for 1 1 Means Yes
1632: AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1628: -- Spr_Debug('150: '||l_LOT_UOM_CONVERSION||' , CRT UOM CON'||l_create_lot_uom_conv ||' ,ITM DUAL COTR '||l_ITEM_DUAL_UOM_CONTROL);
1629: l_LOT_UOM_CONVERSION := 'FALSE';
1630:
1631: IF NVL(l_create_lot_uom_conv,1 ) = 1 -- for 1 1 Means Yes
1632: AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1633: AND l_from_uom_class <> l_to_uom_class THEN
1634: L_LOT_UOM_CONVERSION := 'TRUE';
1635: -- Spr_Debug('160: '||l_LOT_UOM_CONVERSION);
1636: ELSIF NVL(l_create_lot_uom_conv, 1 ) = 3 --for 3 Means User Defined
1633: AND l_from_uom_class <> l_to_uom_class THEN
1634: L_LOT_UOM_CONVERSION := 'TRUE';
1635: -- Spr_Debug('160: '||l_LOT_UOM_CONVERSION);
1636: ELSIF NVL(l_create_lot_uom_conv, 1 ) = 3 --for 3 Means User Defined
1637: AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1638: AND l_from_uom_class <> l_to_uom_class THEN
1639: IF p_response = 'Y' Then
1640: l_response := 1;
1641: Else
1667: -- Spr_Debug('220: '||l_LOT_UOM_CONVERSION);
1668: ELSE
1669: -- always YES
1670: IF NVL(l_create_lot_uom_conv, 1 ) = 1
1671: AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1672: AND l_from_uom_class <> l_to_uom_class THEN
1673: l_go := TRUE;
1674: -- Spr_Debug('230: '||l_LOT_UOM_CONVERSION);
1675: -- user response
1673: l_go := TRUE;
1674: -- Spr_Debug('230: '||l_LOT_UOM_CONVERSION);
1675: -- user response
1676: ELSIF NVL(l_create_lot_uom_conv,1 ) = 3
1677: AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1678: AND l_from_uom_class <> l_to_uom_class THEN
1679: IF NVL(l_LOT_UOM_CONVERSION,'FALSE') = 'TRUE' THEN
1680: l_go := TRUE;
1681: -- copy conversion from parent lot,if exists