[Home] [Help]
275: subinv IN VARCHAR2, rev IN VARCHAR2, loc IN NUMBER,
276: lot IN VARCHAR2, ser IN VARCHAR2) IS
277: SELECT
278: NVL(sum(DECODE(msn.current_status, 3, 1, 0)), 0) SYSTEM_QUANTITY
279: FROM mtl_serial_numbers msn
280: WHERE msn.serial_number = NVL(ser, serial_number)
281: AND msn.inventory_item_id = itemid
282: AND msn.current_organization_id = org
283: AND msn.current_subinventory_code = subinv
555: IS
556: SELECT NVL (SUM (DECODE (msn.current_status, 3, 1, 0)),
557: 0
558: ) system_quantity
559: FROM mtl_serial_numbers msn
560: WHERE msn.serial_number = NVL (ser, serial_number)
561: AND msn.inventory_item_id = itemid
562: AND msn.current_organization_id = org
563: AND msn.current_subinventory_code = subinv
820: |
821: | A serial number may be assigned to at most one
822: | unit of each item across all organizations. This
823: | translates into at most one record in
824: | MTL_SERIAL_NUMBERS for each combination of
825: | SERIAL_NUMBER and INVENTORY_ITEM_ID.
826: |
827: | o 2 Unique serial numbers within organization.
828: | No duplicate serial numbers within any particular
832: | of one item in each organization, with the caveat
833: | that the same serial number may not be assigned to
834: | the same item in two different organizations. This
835: | translates into at most one record in
836: | MTL_SERIAL_NUMBERS for each combination of
837: | SERIAL_NUMBER and INVENTORY_ITEM_ID with the
838: | overriding condition that there be at most one
839: | record for any given combination of SERIAL_NUMBER
840: | and ORGANIZATION_ID.
844: |
845: | A serial number may be assigned to at most one unit
846: | of one item across all organizations. This
847: | translates into at most one record in
848: | MTL_SERIAL_NUMBERS for each value of SERIAL_NUMBER.
849: +--------------------------------------------------------------------------*/
850: FUNCTION CHECK_SERIAL_NUMBER_LOCATION
851: (
852: P_SERIAL_NUMBER IN VARCHAR2,
894: L_current_lot_number,
895: L_current_subinventory,
896: L_current_locator_id,
897: L_current_organization_id
898: FROM MTL_SERIAL_NUMBERS
899: WHERE inventory_item_id = P_Item_id
900: AND serial_number = P_serial_number;
901: IF (l_debug = 1) THEN
902: MDEBUG( 'CheckSerl - found');
937: END IF;
938: BEGIN
939: SELECT 'x'
940: INTO L_nothing
941: FROM MTL_SERIAL_NUMBERS
942: WHERE SERIAL_NUMBER = P_serial_number
943: AND CURRENT_ORGANIZATION_ID + 0 = P_organization_id;
944: IF L_nothing IS NOT NULL then
945: IF (l_debug = 1) THEN
951: WHEN NO_DATA_FOUND THEN
952: BEGIN
953: SELECT 'x'
954: INTO L_nothing
955: FROM MTL_SERIAL_NUMBERS S,
956: MTL_PARAMETERS P
957: WHERE S.CURRENT_ORGANIZATION_ID = P.ORGANIZATION_ID
958: AND S.SERIAL_NUMBER = P_serial_number
959: AND P.SERIAL_NUMBER_TYPE = 3;
970: MDEBUG( 'CheckSerl - data-not found-Dynamic creation');
971: END IF;
972: L_user_id := FND_GLOBAL.USER_ID ;
973: begin
974: INSERT INTO MTL_SERIAL_NUMBERS
975: (INVENTORY_ITEM_ID,
976: SERIAL_NUMBER,
977: LAST_UPDATE_DATE,
978: LAST_UPDATED_BY,
1009: END IF;
1010: BEGIN
1011: SELECT 'x'
1012: INTO L_nothing
1013: FROM MTL_SERIAL_NUMBERS
1014: WHERE SERIAL_NUMBER = P_serial_number;
1015: IF L_nothing IS NOT NULL THEN
1016: IF (l_debug = 1) THEN
1017: MDEBUG( 'CheckSerl - Serl No found - 1');
1024: MDEBUG( 'CheckSerl - Serl No inserting - 1');
1025: END IF;
1026: L_user_id := FND_GLOBAL.USER_ID ;
1027: begin
1028: INSERT INTO MTL_SERIAL_NUMBERS
1029: (INVENTORY_ITEM_ID,
1030: SERIAL_NUMBER,
1031: LAST_UPDATE_DATE,
1032: LAST_UPDATED_BY,
1057: ELSIF P_SERIAL_NUMBER_TYPE = 1 THEN
1058: BEGIN
1059: SELECT 'x'
1060: INTO L_nothing
1061: FROM MTL_SERIAL_NUMBERS S,
1062: MTL_PARAMETERS P
1063: WHERE S.INVENTORY_ITEM_ID = P_item_id
1064: AND S.CURRENT_ORGANIZATION_ID = P.ORGANIZATION_ID
1065: AND S.SERIAL_NUMBER = P_serial_number
1073: EXCEPTION
1074: WHEN NO_DATA_FOUND THEN
1075: L_user_id := FND_GLOBAL.USER_ID ;
1076: begin
1077: INSERT INTO MTL_SERIAL_NUMBERS
1078: (INVENTORY_ITEM_ID,
1079: SERIAL_NUMBER,
1080: LAST_UPDATE_DATE,
1081: LAST_UPDATED_BY,
1306: mdebug('serial being counted ' || p_serial_number);
1307: END IF;
1308: SELECT COUNT(*)
1309: INTO x_lpn_systemqty
1310: FROM mtl_serial_numbers
1311: WHERE lpn_id = p_lpn_id
1312: AND inventory_item_id = p_inventory_item_id
1313: AND current_organization_id = p_organization_id
1314: AND serial_number = p_serial_number
1493: END IF;
1494:
1495: SELECT COUNT (*)
1496: INTO x_lpn_systemqty
1497: FROM mtl_serial_numbers
1498: WHERE lpn_id = p_lpn_id
1499: AND inventory_item_id = p_inventory_item_id
1500: AND current_organization_id = p_organization_id
1501: AND serial_number = p_serial_number