[Home] [Help]
14: is an Intransit Transaction
15: *****************************************************************************/
16: TYPE non_wms_lot_att_rec_type IS RECORD
17: (
18: attribute_category mtl_lot_numbers.attribute_category%TYPE,
19: attribute1 mtl_lot_numbers.attribute1%TYPE,
20: attribute2 mtl_lot_numbers.attribute2%TYPE,
21: attribute3 mtl_lot_numbers.attribute3%TYPE,
22: attribute4 mtl_lot_numbers.attribute4%TYPE,
15: *****************************************************************************/
16: TYPE non_wms_lot_att_rec_type IS RECORD
17: (
18: attribute_category mtl_lot_numbers.attribute_category%TYPE,
19: attribute1 mtl_lot_numbers.attribute1%TYPE,
20: attribute2 mtl_lot_numbers.attribute2%TYPE,
21: attribute3 mtl_lot_numbers.attribute3%TYPE,
22: attribute4 mtl_lot_numbers.attribute4%TYPE,
23: attribute5 mtl_lot_numbers.attribute5%TYPE,
16: TYPE non_wms_lot_att_rec_type IS RECORD
17: (
18: attribute_category mtl_lot_numbers.attribute_category%TYPE,
19: attribute1 mtl_lot_numbers.attribute1%TYPE,
20: attribute2 mtl_lot_numbers.attribute2%TYPE,
21: attribute3 mtl_lot_numbers.attribute3%TYPE,
22: attribute4 mtl_lot_numbers.attribute4%TYPE,
23: attribute5 mtl_lot_numbers.attribute5%TYPE,
24: attribute6 mtl_lot_numbers.attribute6%TYPE,
17: (
18: attribute_category mtl_lot_numbers.attribute_category%TYPE,
19: attribute1 mtl_lot_numbers.attribute1%TYPE,
20: attribute2 mtl_lot_numbers.attribute2%TYPE,
21: attribute3 mtl_lot_numbers.attribute3%TYPE,
22: attribute4 mtl_lot_numbers.attribute4%TYPE,
23: attribute5 mtl_lot_numbers.attribute5%TYPE,
24: attribute6 mtl_lot_numbers.attribute6%TYPE,
25: attribute7 mtl_lot_numbers.attribute7%TYPE,
18: attribute_category mtl_lot_numbers.attribute_category%TYPE,
19: attribute1 mtl_lot_numbers.attribute1%TYPE,
20: attribute2 mtl_lot_numbers.attribute2%TYPE,
21: attribute3 mtl_lot_numbers.attribute3%TYPE,
22: attribute4 mtl_lot_numbers.attribute4%TYPE,
23: attribute5 mtl_lot_numbers.attribute5%TYPE,
24: attribute6 mtl_lot_numbers.attribute6%TYPE,
25: attribute7 mtl_lot_numbers.attribute7%TYPE,
26: attribute8 mtl_lot_numbers.attribute8%TYPE,
19: attribute1 mtl_lot_numbers.attribute1%TYPE,
20: attribute2 mtl_lot_numbers.attribute2%TYPE,
21: attribute3 mtl_lot_numbers.attribute3%TYPE,
22: attribute4 mtl_lot_numbers.attribute4%TYPE,
23: attribute5 mtl_lot_numbers.attribute5%TYPE,
24: attribute6 mtl_lot_numbers.attribute6%TYPE,
25: attribute7 mtl_lot_numbers.attribute7%TYPE,
26: attribute8 mtl_lot_numbers.attribute8%TYPE,
27: attribute9 mtl_lot_numbers.attribute9%TYPE,
20: attribute2 mtl_lot_numbers.attribute2%TYPE,
21: attribute3 mtl_lot_numbers.attribute3%TYPE,
22: attribute4 mtl_lot_numbers.attribute4%TYPE,
23: attribute5 mtl_lot_numbers.attribute5%TYPE,
24: attribute6 mtl_lot_numbers.attribute6%TYPE,
25: attribute7 mtl_lot_numbers.attribute7%TYPE,
26: attribute8 mtl_lot_numbers.attribute8%TYPE,
27: attribute9 mtl_lot_numbers.attribute9%TYPE,
28: attribute10 mtl_lot_numbers.attribute10%TYPE,
21: attribute3 mtl_lot_numbers.attribute3%TYPE,
22: attribute4 mtl_lot_numbers.attribute4%TYPE,
23: attribute5 mtl_lot_numbers.attribute5%TYPE,
24: attribute6 mtl_lot_numbers.attribute6%TYPE,
25: attribute7 mtl_lot_numbers.attribute7%TYPE,
26: attribute8 mtl_lot_numbers.attribute8%TYPE,
27: attribute9 mtl_lot_numbers.attribute9%TYPE,
28: attribute10 mtl_lot_numbers.attribute10%TYPE,
29: attribute11 mtl_lot_numbers.attribute11%TYPE,
22: attribute4 mtl_lot_numbers.attribute4%TYPE,
23: attribute5 mtl_lot_numbers.attribute5%TYPE,
24: attribute6 mtl_lot_numbers.attribute6%TYPE,
25: attribute7 mtl_lot_numbers.attribute7%TYPE,
26: attribute8 mtl_lot_numbers.attribute8%TYPE,
27: attribute9 mtl_lot_numbers.attribute9%TYPE,
28: attribute10 mtl_lot_numbers.attribute10%TYPE,
29: attribute11 mtl_lot_numbers.attribute11%TYPE,
30: attribute12 mtl_lot_numbers.attribute12%TYPE,
23: attribute5 mtl_lot_numbers.attribute5%TYPE,
24: attribute6 mtl_lot_numbers.attribute6%TYPE,
25: attribute7 mtl_lot_numbers.attribute7%TYPE,
26: attribute8 mtl_lot_numbers.attribute8%TYPE,
27: attribute9 mtl_lot_numbers.attribute9%TYPE,
28: attribute10 mtl_lot_numbers.attribute10%TYPE,
29: attribute11 mtl_lot_numbers.attribute11%TYPE,
30: attribute12 mtl_lot_numbers.attribute12%TYPE,
31: attribute13 mtl_lot_numbers.attribute13%TYPE,
24: attribute6 mtl_lot_numbers.attribute6%TYPE,
25: attribute7 mtl_lot_numbers.attribute7%TYPE,
26: attribute8 mtl_lot_numbers.attribute8%TYPE,
27: attribute9 mtl_lot_numbers.attribute9%TYPE,
28: attribute10 mtl_lot_numbers.attribute10%TYPE,
29: attribute11 mtl_lot_numbers.attribute11%TYPE,
30: attribute12 mtl_lot_numbers.attribute12%TYPE,
31: attribute13 mtl_lot_numbers.attribute13%TYPE,
32: attribute14 mtl_lot_numbers.attribute14%TYPE,
25: attribute7 mtl_lot_numbers.attribute7%TYPE,
26: attribute8 mtl_lot_numbers.attribute8%TYPE,
27: attribute9 mtl_lot_numbers.attribute9%TYPE,
28: attribute10 mtl_lot_numbers.attribute10%TYPE,
29: attribute11 mtl_lot_numbers.attribute11%TYPE,
30: attribute12 mtl_lot_numbers.attribute12%TYPE,
31: attribute13 mtl_lot_numbers.attribute13%TYPE,
32: attribute14 mtl_lot_numbers.attribute14%TYPE,
33: attribute15 mtl_lot_numbers.attribute15%TYPE
26: attribute8 mtl_lot_numbers.attribute8%TYPE,
27: attribute9 mtl_lot_numbers.attribute9%TYPE,
28: attribute10 mtl_lot_numbers.attribute10%TYPE,
29: attribute11 mtl_lot_numbers.attribute11%TYPE,
30: attribute12 mtl_lot_numbers.attribute12%TYPE,
31: attribute13 mtl_lot_numbers.attribute13%TYPE,
32: attribute14 mtl_lot_numbers.attribute14%TYPE,
33: attribute15 mtl_lot_numbers.attribute15%TYPE
34: );
27: attribute9 mtl_lot_numbers.attribute9%TYPE,
28: attribute10 mtl_lot_numbers.attribute10%TYPE,
29: attribute11 mtl_lot_numbers.attribute11%TYPE,
30: attribute12 mtl_lot_numbers.attribute12%TYPE,
31: attribute13 mtl_lot_numbers.attribute13%TYPE,
32: attribute14 mtl_lot_numbers.attribute14%TYPE,
33: attribute15 mtl_lot_numbers.attribute15%TYPE
34: );
35:
28: attribute10 mtl_lot_numbers.attribute10%TYPE,
29: attribute11 mtl_lot_numbers.attribute11%TYPE,
30: attribute12 mtl_lot_numbers.attribute12%TYPE,
31: attribute13 mtl_lot_numbers.attribute13%TYPE,
32: attribute14 mtl_lot_numbers.attribute14%TYPE,
33: attribute15 mtl_lot_numbers.attribute15%TYPE
34: );
35:
36: PROCEDURE get_lot_att_from_source (
29: attribute11 mtl_lot_numbers.attribute11%TYPE,
30: attribute12 mtl_lot_numbers.attribute12%TYPE,
31: attribute13 mtl_lot_numbers.attribute13%TYPE,
32: attribute14 mtl_lot_numbers.attribute14%TYPE,
33: attribute15 mtl_lot_numbers.attribute15%TYPE
34: );
35:
36: PROCEDURE get_lot_att_from_source (
37: x_return_status OUT NOCOPY VARCHAR2,
105: IF(l_lot_status_enabled= 'Y' and (p_status_id <> g_miss_num or p_status_id is not null)) then
106: -- Get old status id
107: select status_id
108: into l_old_status_id
109: from mtl_lot_numbers
110: where organization_id = p_organization_id
111: and inventory_item_id = p_inventory_item_id
112: and lot_number = p_lot_number;
113:
514:
515: IF ( l_lotunique = 1 ) THEN
516: SELECT COUNT ( 1 )
517: INTO l_lotcount
518: FROM mtl_lot_numbers
519: WHERE inventory_item_id <> p_inventory_item_id
520: AND lot_number = p_lot_number
521: AND NOT EXISTS ( SELECT NULL
522: FROM mtl_lot_numbers lot
518: FROM mtl_lot_numbers
519: WHERE inventory_item_id <> p_inventory_item_id
520: AND lot_number = p_lot_number
521: AND NOT EXISTS ( SELECT NULL
522: FROM mtl_lot_numbers lot
523: WHERE lot.lot_number = p_lot_number
524: AND lot.organization_id = p_organization_id
525: AND lot.inventory_item_id = p_inventory_item_id );
526:
538: l_lotcount := 0;
539:
540: SELECT COUNT ( 1 )
541: INTO l_lotcount
542: FROM mtl_lot_numbers
543: WHERE inventory_item_id = p_inventory_item_id
544: AND organization_id = p_organization_id
545: AND lot_number = p_lot_number;
546:
681: END IF;
682: BEGIN
683: select count(*)
684: into l_lotcount
685: From mtl_lot_numbers
686: where organization_id = p_transfer_organization_id
687: and inventory_item_id = p_inventory_item_id
688: and lot_number = p_lot_number;
689:
687: and inventory_item_id = p_inventory_item_id
688: and lot_number = p_lot_number;
689:
690: if( l_lotcount > 0 ) then
691: INSERT INTO mtl_lot_numbers
692: (
693: inventory_item_id,
694: organization_id,
695: lot_number,
890: n_attribute10,
891: territory_code,
892: vendor_name,
893: p_parent_lot_number -- bug 10176719 - inserting parent lot number
894: FROM mtl_lot_numbers
895: WHERE organization_id = p_transfer_organization_id
896: AND inventory_item_id = p_inventory_item_id
897: AND lot_number = p_lot_number
898: AND NOT EXISTS ( SELECT NULL
895: WHERE organization_id = p_transfer_organization_id
896: AND inventory_item_id = p_inventory_item_id
897: AND lot_number = p_lot_number
898: AND NOT EXISTS ( SELECT NULL
899: FROM mtl_lot_numbers lot
900: WHERE lot.lot_number = p_lot_number
901: AND lot.organization_id = p_organization_id
902: AND lot.inventory_item_id = p_inventory_item_id );
903: else
900: WHERE lot.lot_number = p_lot_number
901: AND lot.organization_id = p_organization_id
902: AND lot.inventory_item_id = p_inventory_item_id );
903: else
904: INSERT INTO mtl_lot_numbers
905: (
906: inventory_item_id,
907: organization_id,
908: lot_number,
1074: WHERE mtlt.transaction_temp_id = p_transaction_temp_id
1075: AND mtlt.lot_number = p_lot_number
1076: AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
1077: AND NOT EXISTS ( SELECT NULL
1078: FROM mtl_lot_numbers lot
1079: WHERE lot.lot_number = p_lot_number
1080: AND lot.organization_id = p_organization_id
1081: AND lot.inventory_item_id = p_inventory_item_id );
1082:
1102: -- to prepare to insert the status to status history table for the initial status
1103: -- bug 1870120
1104: SELECT status_id
1105: INTO l_default_lot_status_id
1106: FROM mtl_lot_numbers
1107: WHERE lot_number = p_lot_number
1108: AND organization_id = p_organization_id
1109: AND inventory_item_id = p_inventory_item_id;
1110:
1126: x_attributes_default_count => l_attributes_default_count,
1127: x_return_status => l_return_status,
1128: x_msg_count => l_msg_count,
1129: x_msg_data => l_msg_data,
1130: p_table_name => 'MTL_LOT_NUMBERS',
1131: p_attributes_name => 'Lot Attributes',
1132: p_inventory_item_id => p_inventory_item_id,
1133: p_organization_id => p_organization_id,
1134: p_lot_serial_number => p_lot_number,
1271: p_inventory_item_id,
1272: 'Lot Attributes'
1273: );
1274: IF (l_debug = 1) THEN
1275: inv_pick_wave_pick_confirm_pub.tracelog ( 'before inserting into mtl_lot_numbers' , 'INV_LOT_API_PUB');
1276: END IF;
1277:
1278: --print_debug('after get_context_code 30', 4);
1279:
1276: END IF;
1277:
1278: --print_debug('after get_context_code 30', 4);
1279:
1280: INSERT INTO mtl_lot_numbers
1281: (
1282: inventory_item_id,
1283: organization_id,
1284: lot_number,
1524: IF (l_debug = 1) THEN
1525: inv_pick_wave_pick_confirm_pub.tracelog ('Update the expiration date', 'INV_LOT_API_PUB');
1526: END IF;
1527:
1528: UPDATE mtl_lot_numbers
1529: SET expiration_date = p_expiration_date,
1530: last_update_date = SYSDATE,
1531: last_updated_by = l_userid
1532: WHERE inventory_item_id = p_inventory_item_id
2015: MLN.territory_code,
2016: MLN.vendor_name,
2017: mtlt.product_code,
2018: mtlt.product_transaction_id
2019: FROM mtl_lot_numbers MLN,
2020: mtl_transaction_lots_temp MTLT
2021: WHERE MLN.organization_id = p_organization_id
2022: AND MLN.inventory_item_id = p_inventory_item_id
2023: AND MLN.lot_number = p_lot_number
2042:
2043: /*
2044: and not exists
2045: (select NULL
2046: from MTL_LOT_NUMBERS LOT
2047: where LOT.lot_number = p_lot_number
2048: and LOT.organization_id = p_organization_id
2049: and LOT.inventory_item_id = p_inventory_item_id);
2050: */
2060: x_attributes_default_count => l_attributes_default_count,
2061: x_return_status => l_return_status,
2062: x_msg_count => l_msg_count,
2063: x_msg_data => l_msg_data,
2064: p_table_name => 'MTL_LOT_NUMBERS',
2065: p_attributes_name => 'Lot Attributes',
2066: p_inventory_item_id => p_inventory_item_id,
2067: p_organization_id => p_organization_id,
2068: p_lot_serial_number => p_lot_number,
2426:
2427: CURSOR mln_cur_2
2428: IS
2429: SELECT lot_number
2430: FROM mtl_lot_numbers
2431: WHERE lot_number = p_auto_lot_number
2432: AND inventory_item_id <> p_inventory_item_id;
2433:
2434: CURSOR mtln_cur_2
2775:
2776:
2777: CURSOR get_mtl_lot IS
2778: SELECT lot_number
2779: FROM mtl_lot_numbers
2780: WHERE lot_number = p_parent_lot_number
2781: AND organization_id = p_org_id
2782: AND inventory_item_id = p_inventory_item_id;
2783:
2780: WHERE lot_number = p_parent_lot_number
2781: AND organization_id = p_org_id
2782: AND inventory_item_id = p_inventory_item_id;
2783:
2784: x_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE := NULL;
2785: ===========================================*/
2786:
2787: /*=============================================
2788: Following variable controls looping until a
2846: x_parent_call is the lot returned from
2847: the recall.
2848: ====================================*/
2849:
2850: x_parent_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE;
2851: x_parent_call MTL_LOT_NUMBERS.LOT_NUMBER%TYPE;
2852:
2853: /*========================================
2854: interim_child_lot_number is the generated
2847: the recall.
2848: ====================================*/
2849:
2850: x_parent_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE;
2851: x_parent_call MTL_LOT_NUMBERS.LOT_NUMBER%TYPE;
2852:
2853: /*========================================
2854: interim_child_lot_number is the generated
2855: child lot that is a candidate to be
2873: ======================================*/
2874:
2875: CURSOR check_lot_exists IS
2876: SELECT count( 1 )
2877: FROM mtl_lot_numbers
2878: WHERE inventory_item_id = p_inventory_item_id
2879: AND organization_id = p_org_id
2880: AND lot_number = interim_child_lot_number;
2881:
3540: --Bug 3750324 Validating the uniqueness of lot through existing api for checking validation so that the unique lot is
3541: -- generated at first go on the form
3542: /* SELECT COUNT ( 1 )
3543: INTO l_lotcount
3544: FROM mtl_lot_numbers
3545: WHERE
3546: inventory_item_id = p_inventory_item_id AND
3547: organization_id = p_org_id AND
3548: lot_number = auto_lot_number;
3635:
3636: CURSOR mln_cur_1
3637: IS
3638: SELECT lot_number
3639: FROM mtl_lot_numbers
3640: WHERE lot_number = p_auto_lot_number
3641: AND inventory_item_id = p_inventory_item_id;
3642:
3643: CURSOR mtln_cur_1
3953: x_source_non_wms_lot_att_rec.attribute12,
3954: x_source_non_wms_lot_att_rec.attribute13,
3955: x_source_non_wms_lot_att_rec.attribute14,
3956: x_source_non_wms_lot_att_rec.attribute15
3957: FROM mtl_lot_numbers
3958: WHERE organization_id = p_from_organization_id
3959: AND inventory_item_id = p_inventory_item_id
3960: AND lot_number = p_lot_number;
3961:
4088: /* Populate the flex field record */
4089: --IF p_attribute_category IS NOT NULL THEN
4090: --AND p_attributes_tbl.COUNT > 0 THEN
4091: l_dflex_r.application_id := 401;
4092: l_dflex_r.flexfield_name := 'MTL_LOT_NUMBERS';
4093: /* Get all contexts */
4094: fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
4095:
4096: IF g_debug = 1 THEN
4093: /* Get all contexts */
4094: fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
4095:
4096: IF g_debug = 1 THEN
4097: print_debug('Found contexts for the Flexfield MTL_LOT_NUMBERS', 9);
4098: END IF;
4099:
4100: /* From the l_contexts_dr, get the position of the global context */
4101: l_global_context := l_contexts_dr.global_context;
4270: l_context := l_contexts_dr.context_code(l_global_context);
4271: fnd_flex_descval.set_context_value(l_context);
4272: end if;
4273: IF( l_global_nsegments > 0 OR p_attribute_category IS NOT NULL ) then
4274: IF fnd_flex_descval.validate_desccols(appl_short_name => 'INV', desc_flex_name => 'MTL_LOT_NUMBERS', values_or_ids => 'I'
4275: , validation_date => SYSDATE) THEN
4276: IF g_debug = 1 THEN
4277: print_debug('Value set validation successful', 9);
4278: END IF;
4624: PROCEDURE update_inv_lot(
4625: x_return_status OUT NOCOPY VARCHAR2
4626: , x_msg_count OUT NOCOPY NUMBER
4627: , x_msg_data OUT NOCOPY VARCHAR2
4628: , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
4629: , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
4630: , p_source IN NUMBER
4631: , p_api_version IN NUMBER
4632: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
4625: x_return_status OUT NOCOPY VARCHAR2
4626: , x_msg_count OUT NOCOPY NUMBER
4627: , x_msg_data OUT NOCOPY VARCHAR2
4628: , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
4629: , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
4630: , p_source IN NUMBER
4631: , p_api_version IN NUMBER
4632: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
4633: , p_commit IN VARCHAR2 := fnd_api.g_false
4648: , attribute12
4649: , attribute13
4650: , attribute14
4651: , attribute15
4652: FROM mtl_lot_numbers
4653: WHERE inventory_item_id = p_lot_rec.inventory_item_id
4654: AND organization_id = p_lot_rec.organization_id
4655: AND lot_number = p_lot_rec.lot_number;
4656:
4674: , c_attribute17
4675: , c_attribute18
4676: , c_attribute19
4677: , c_attribute20
4678: FROM mtl_lot_numbers
4679: WHERE inventory_item_id = p_lot_rec.inventory_item_id
4680: AND organization_id = p_lot_rec.organization_id
4681: AND lot_number = p_lot_rec.lot_number;
4682:
4690: , n_attribute7
4691: , n_attribute8
4692: , n_attribute9
4693: , n_attribute10
4694: FROM mtl_lot_numbers
4695: WHERE inventory_item_id = p_lot_rec.inventory_item_id
4696: AND organization_id = p_lot_rec.organization_id
4697: AND lot_number = p_lot_rec.lot_number;
4698:
4706: , d_attribute7
4707: , d_attribute8
4708: , d_attribute9
4709: , d_attribute10
4710: FROM mtl_lot_numbers
4711: WHERE inventory_item_id = p_lot_rec.inventory_item_id
4712: AND organization_id = p_lot_rec.organization_id
4713: AND lot_number = p_lot_rec.lot_number;
4714:
4714:
4715: CURSOR attr_category_cur IS
4716: SELECT attribute_category
4717: , lot_attribute_category
4718: FROM mtl_lot_numbers
4719: WHERE inventory_item_id = p_lot_rec.inventory_item_id
4720: AND organization_id = p_lot_rec.organization_id
4721: AND lot_number = p_lot_rec.lot_number;
4722:
4744: , width_uom
4745: , territory_code
4746: , supplier_lot_number
4747: , VENDOR_NAME
4748: FROM MTL_LOT_NUMBERS
4749: WHERE inventory_item_id = p_lot_rec.inventory_item_id
4750: AND organization_id = p_lot_rec.organization_id
4751: AND lot_number = p_lot_rec.lot_number;
4752:
4790: item_shelf_life_days CONSTANT NUMBER := 2;
4791: user_defined_exp_date CONSTANT NUMBER := 4;
4792: l_shelf_life_days mtl_system_items.shelf_life_days%TYPE;
4793: l_shelf_life_code mtl_system_items.shelf_life_code%TYPE;
4794: l_expiration_date mtl_lot_numbers.expiration_date%TYPE;
4795: l_global_nsegments NUMBER := 0;
4796: l_lot_grade VARCHAR2(150); /*bug 13780398 Bug 13987577 changed the data type to incoporate more than 2 bytes*/
4797: l_grade_enabled VARCHAR2(1); --bug 13780398
4798:
4842: /* Check if this combination is valid */
4843: BEGIN
4844: SELECT 1
4845: INTO l_chk
4846: FROM mtl_lot_numbers
4847: WHERE inventory_item_id = p_lot_rec.inventory_item_id
4848: AND organization_id = p_lot_rec.organization_id
4849: AND lot_number = p_lot_rec.lot_number;
4850: EXCEPTION
4960: --IF l_attr_category_cur.attribute_category IS NOT NULL THEN
4961: -- AND p_attributes_tbl.COUNT > 0 THEN
4962: /* Populate the flex field record */
4963: l_dflex_r.application_id := 401;
4964: l_dflex_r.flexfield_name := 'MTL_LOT_NUMBERS';
4965: /* Get all contexts */
4966: fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
4967: /* From the l_contexts_dr, get the position of the global context */
4968: l_global_context := l_contexts_dr.global_context;
5868: IF g_debug = 1 THEN
5869: print_debug('Upd Lot Attr: Call to validate_lot_attr_in_param is success ', 9);
5870: END IF;
5871: IF g_debug = 1 THEN
5872: print_debug('Updating MTL_LOT_NUMBERS table ....' , 9);
5873: END IF;
5874: inv_log_util.trace('l_expiration_date is:'||l_expiration_date, 'INV_LOT_API_PUB','9'); --For bug 4888300
5875:
5876: --Fixed for bug#7529468
5878: --LAST_UPDATE_DATE and LAST_UPDATED_BY
5879: --Fix for bug#7930079
5880: --In decode function for date columns - NULL is changed as to_date(NULL)
5881: IF l_wms_installed = 'TRUE' THEN
5882: UPDATE mtl_lot_numbers
5883: SET expiration_date =
5884: DECODE(l_expiration_date, NULL, expiration_date, l_expiration_date )
5885: , disable_flag =
5886: DECODE(
6434: WHERE inventory_item_id = p_lot_rec.inventory_item_id
6435: AND organization_id = p_lot_rec.organization_id
6436: AND lot_number = p_lot_rec.lot_number;
6437: ELSE
6438: UPDATE mtl_lot_numbers
6439: SET expiration_date =
6440: DECODE(l_expiration_date, NULL, expiration_date, l_expiration_date )
6441: , disable_flag =
6442: DECODE(
6894:
6895: -- bug 13780398
6896: select grade_code
6897: into l_lot_grade
6898: from mtl_lot_numbers
6899: where organization_id=p_lot_rec.organization_id
6900: and inventory_item_id=p_lot_rec.inventory_item_id
6901: and lot_number=p_lot_rec.lot_number;
6902:
7002: , p_supplier_lot_number IN VARCHAR2
7003: , p_vendor_name IN VARCHAR2
7004: , p_source IN NUMBER
7005: ) IS
7006: l_in_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
7007: x_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
7008: l_api_version NUMBER;
7009: l_init_msg_list VARCHAR2(100);
7010: l_commit VARCHAR2(100);
7003: , p_vendor_name IN VARCHAR2
7004: , p_source IN NUMBER
7005: ) IS
7006: l_in_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
7007: x_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
7008: l_api_version NUMBER;
7009: l_init_msg_list VARCHAR2(100);
7010: l_commit VARCHAR2(100);
7011: l_origin_txn_id NUMBER;
7897: x_attributes_default_count => l_attributes_default_count,
7898: x_return_status => l_return_status,
7899: x_msg_count => l_msg_count,
7900: x_msg_data => l_msg_data,
7901: p_table_name => 'MTL_LOT_NUMBERS',
7902: p_attributes_name => 'Lot Attributes',
7903: p_inventory_item_id => p_inventory_item_id,
7904: p_organization_id => p_organization_id,
7905: p_lot_serial_number => null,
8723: CURSOR get_new_lot_details( org_id IN NUMBER
8724: , item_id IN NUMBER
8725: , lot_no IN VARCHAR2) IS
8726: SELECT lot_number
8727: FROM mtl_lot_numbers
8728: WHERE lot_number = lot_no
8729: AND inventory_item_id = item_id
8730: AND organization_id = org_id; */
8731:
9270:
9271:
9272: /* Defined new variables for overloaded API call */
9273:
9274: l_in_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
9275: l_out_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
9276: x_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
9277: l_api_version NUMBER;
9278: l_init_msg_list VARCHAR2(100);
9271:
9272: /* Defined new variables for overloaded API call */
9273:
9274: l_in_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
9275: l_out_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
9276: x_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
9277: l_api_version NUMBER;
9278: l_init_msg_list VARCHAR2(100);
9279: l_commit VARCHAR2(100);
9272: /* Defined new variables for overloaded API call */
9273:
9274: l_in_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
9275: l_out_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
9276: x_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
9277: l_api_version NUMBER;
9278: l_init_msg_list VARCHAR2(100);
9279: l_commit VARCHAR2(100);
9280: l_validation_level NUMBER;
9587: x_return_status OUT NOCOPY VARCHAR2
9588: , x_msg_count OUT NOCOPY NUMBER
9589: , x_msg_data OUT NOCOPY VARCHAR2
9590: , x_row_id OUT NOCOPY ROWID
9591: , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
9592: , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
9593: , p_source IN NUMBER
9594: , p_api_version IN NUMBER
9595: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
9588: , x_msg_count OUT NOCOPY NUMBER
9589: , x_msg_data OUT NOCOPY VARCHAR2
9590: , x_row_id OUT NOCOPY ROWID
9591: , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
9592: , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
9593: , p_source IN NUMBER
9594: , p_api_version IN NUMBER
9595: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
9596: , p_commit IN VARCHAR2 := fnd_api.g_false
9625: x_return_status OUT NOCOPY VARCHAR2
9626: , x_msg_count OUT NOCOPY NUMBER
9627: , x_msg_data OUT NOCOPY VARCHAR2
9628: , x_row_id OUT NOCOPY ROWID
9629: , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
9630: , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
9631: , p_source IN NUMBER
9632: , p_api_version IN NUMBER
9633: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
9626: , x_msg_count OUT NOCOPY NUMBER
9627: , x_msg_data OUT NOCOPY VARCHAR2
9628: , x_row_id OUT NOCOPY ROWID
9629: , x_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
9630: , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
9631: , p_source IN NUMBER
9632: , p_api_version IN NUMBER
9633: , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
9634: , p_commit IN VARCHAR2 := fnd_api.g_false
9650: AND organization_id = cp_organization_id;
9651:
9652: l_chk_msi_attr_rec c_chk_msi_attr%ROWTYPE;
9653:
9654: /* Cursor definition to check lot existence in Mtl_Lot_Numbers Table */
9655: CURSOR c_chk_lot_exists(cp_lot_number MTL_LOT_NUMBERS.lot_number%TYPE,cp_inventory_item_id NUMBER, cp_organization_id NUMBER) IS
9656: SELECT lot_number
9657: FROM mtl_lot_numbers
9658: WHERE lot_number = cp_lot_number AND
9651:
9652: l_chk_msi_attr_rec c_chk_msi_attr%ROWTYPE;
9653:
9654: /* Cursor definition to check lot existence in Mtl_Lot_Numbers Table */
9655: CURSOR c_chk_lot_exists(cp_lot_number MTL_LOT_NUMBERS.lot_number%TYPE,cp_inventory_item_id NUMBER, cp_organization_id NUMBER) IS
9656: SELECT lot_number
9657: FROM mtl_lot_numbers
9658: WHERE lot_number = cp_lot_number AND
9659: inventory_item_id = cp_inventory_item_id AND
9653:
9654: /* Cursor definition to check lot existence in Mtl_Lot_Numbers Table */
9655: CURSOR c_chk_lot_exists(cp_lot_number MTL_LOT_NUMBERS.lot_number%TYPE,cp_inventory_item_id NUMBER, cp_organization_id NUMBER) IS
9656: SELECT lot_number
9657: FROM mtl_lot_numbers
9658: WHERE lot_number = cp_lot_number AND
9659: inventory_item_id = cp_inventory_item_id AND
9660: organization_id = cp_organization_id;
9661:
9670:
9671: l_lot_uom_conv c_lot_uom_conv%ROWTYPE ;
9672:
9673: /* Cursor definition to get gen_object_id for a lot */
9674: CURSOR c_get_obj_id(cp_lot_number MTL_LOT_NUMBERS.lot_number%TYPE,cp_inventory_item_id NUMBER, cp_organization_id NUMBER) IS
9675: SELECT gen_object_id, ROWID
9676: FROM mtl_lot_numbers
9677: WHERE lot_number = cp_lot_number AND
9678: inventory_item_id = cp_inventory_item_id AND
9672:
9673: /* Cursor definition to get gen_object_id for a lot */
9674: CURSOR c_get_obj_id(cp_lot_number MTL_LOT_NUMBERS.lot_number%TYPE,cp_inventory_item_id NUMBER, cp_organization_id NUMBER) IS
9675: SELECT gen_object_id, ROWID
9676: FROM mtl_lot_numbers
9677: WHERE lot_number = cp_lot_number AND
9678: inventory_item_id = cp_inventory_item_id AND
9679: organization_id = cp_organization_id;
9680:
9680:
9681: l_get_obj_id_rec c_get_obj_id%ROWTYPE;
9682:
9683:
9684: l_parent_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9685: l_child_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9686: x_parent_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9687: x_child_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9688:
9681: l_get_obj_id_rec c_get_obj_id%ROWTYPE;
9682:
9683:
9684: l_parent_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9685: l_child_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9686: x_parent_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9687: x_child_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9688:
9689: l_chd_gen_obj_id NUMBER;
9682:
9683:
9684: l_parent_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9685: l_child_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9686: x_parent_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9687: x_child_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9688:
9689: l_chd_gen_obj_id NUMBER;
9690: l_prt_gen_obj_id NUMBER;
9683:
9684: l_parent_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9685: l_child_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9686: x_parent_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9687: x_child_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
9688:
9689: l_chd_gen_obj_id NUMBER;
9690: l_prt_gen_obj_id NUMBER;
9691:
9789: END IF;
9790:
9791: END IF; /* c_chk_msi_attr*/
9792:
9793: /* Check for existence of Lot in Mtl_Lot_Numbers Table */
9794: IF p_lot_rec.lot_number IS NULL THEN
9795: IF g_debug = 1 THEN
9796: print_debug('Value for mandatory field Lot Number cannot be null', 9);
9797: END IF;
9799: fnd_message.set_name('INV', 'INV_NULL_CLOT_EXP');
9800: fnd_msg_pub.ADD;
9801: RAISE fnd_api.g_exc_error;
9802: ELSE
9803: /* Check child lot existence in Mtl_Lot_Numbers Table */
9804: OPEN c_chk_lot_exists(p_lot_rec.lot_number,p_lot_rec.inventory_item_id,p_lot_rec.organization_id);
9805: FETCH c_chk_lot_exists INTO l_chk_lot_rec;
9806:
9807: IF c_chk_lot_exists%FOUND THEN
9804: OPEN c_chk_lot_exists(p_lot_rec.lot_number,p_lot_rec.inventory_item_id,p_lot_rec.organization_id);
9805: FETCH c_chk_lot_exists INTO l_chk_lot_rec;
9806:
9807: IF c_chk_lot_exists%FOUND THEN
9808: /* Child lot exists in Mtl_Lot_Numbers Table. */
9809: IF g_debug = 1 THEN
9810: print_debug('Child lot already exists in the system.', 9);
9811: END IF;
9812:
9815: fnd_message.set_token('LOT_NUMBER', to_char(p_lot_rec.lot_number));
9816: fnd_msg_pub.ADD;
9817: RAISE fnd_api.g_exc_error;
9818: END IF;
9819: /* Child lot DOES NOT exist in Mtl_Lot_Numbers Table. */
9820: CLOSE c_chk_lot_exists;
9821: END IF;
9822:
9823: /* Check for same parent and child lot names */
9832: RAISE fnd_api.g_exc_error;
9833: END IF ;
9834: END IF ;
9835:
9836: /* Check for existence of Parent Lot in Mtl_Lot_Numbers Table */
9837: l_parent_exists_flag := NULL;
9838:
9839: OPEN c_chk_lot_exists(p_lot_rec.parent_lot_number,p_lot_rec.inventory_item_id,p_lot_rec.organization_id);
9840: FETCH c_chk_lot_exists INTO l_chk_lot_rec;
9839: OPEN c_chk_lot_exists(p_lot_rec.parent_lot_number,p_lot_rec.inventory_item_id,p_lot_rec.organization_id);
9840: FETCH c_chk_lot_exists INTO l_chk_lot_rec;
9841:
9842: IF c_chk_lot_exists%FOUND THEN
9843: /* Parent lot exists in Mtl_Lot_Numbers Table. */
9844: l_parent_exists_flag := 'Y';
9845:
9846: ELSIF c_chk_lot_exists%NOTFOUND AND p_lot_rec.parent_lot_number IS NOT NULL THEN
9847: /* Parent lot DOES NOT exist in Mtl_Lot_Numbers Table. */
9843: /* Parent lot exists in Mtl_Lot_Numbers Table. */
9844: l_parent_exists_flag := 'Y';
9845:
9846: ELSIF c_chk_lot_exists%NOTFOUND AND p_lot_rec.parent_lot_number IS NOT NULL THEN
9847: /* Parent lot DOES NOT exist in Mtl_Lot_Numbers Table. */
9848: l_parent_exists_flag := 'N';
9849: END IF;
9850: CLOSE c_chk_lot_exists;
9851:
9906:
9907: END IF; /* p_validation_level = G_VALID_LEVEL_NONE */
9908:
9909:
9910: -- Call mtl_lot_numbers_pkg.insert_row API passing the attribute's
9911: -- values populated in local variable l_child_lot_rec.
9912:
9913: -- OPM Inventory Convergence - added column sampling_event_id to mtl_lot_number
9914: -- Bug #4115021
9913: -- OPM Inventory Convergence - added column sampling_event_id to mtl_lot_number
9914: -- Bug #4115021
9915: /*Bug#5523811 passing values from l_child_lot_rec instead of NULL for fields
9916: curl_wrnkl_fold, description and vendor_id */
9917: Mtl_Lot_Numbers_Pkg.Insert_Row(
9918: x_inventory_item_id => l_child_lot_rec.inventory_item_id
9919: , x_organization_id => l_child_lot_rec.organization_id
9920: , x_lot_number => l_child_lot_rec.lot_number
9921: , x_parent_lot_number => l_child_lot_rec.parent_lot_number
10174: l_api_name CONSTANT VARCHAR2 ( 50 ) := 'INV_LOT_API_PUB.auto_gen_lot';
10175: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10176: v_org_code VARCHAR2 ( 3 );
10177: v_item_name VARCHAR2 ( 40 );
10178: x_parent_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE := NULL;
10179: x_parent_call MTL_LOT_NUMBERS.LOT_NUMBER%TYPE;
10180:
10181: BEGIN
10182: x_parent_call := inv_lot_api_pub.auto_gen_lot (
10175: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10176: v_org_code VARCHAR2 ( 3 );
10177: v_item_name VARCHAR2 ( 40 );
10178: x_parent_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE := NULL;
10179: x_parent_call MTL_LOT_NUMBERS.LOT_NUMBER%TYPE;
10180:
10181: BEGIN
10182: x_parent_call := inv_lot_api_pub.auto_gen_lot (
10183: p_org_id,
11004: AND TO_UOM_CODE=p_to_UOM
11005: AND FROM_UOM_CODE=p_from_UOM;
11006:
11007: CURSOR check_lot_exists (p_org_id NUMBER )IS
11008: SELECT LOT_NUMBER, EXPIRATION_DATE FROM MTL_LOT_NUMBERS
11009: WHERE ORGANIZATION_ID =p_org_id
11010: AND INVENTORY_ITEM_ID = p_inventory_item_id
11011: AND LOT_NUMBER =p_lot_number;
11012:
11130: inv_log_util.trace('lot_UOM_conv_OrgTxf: in else', g_pkg_name,9);
11131: END IF;
11132:
11133: begin
11134: SELECT EXPIRATION_DATE INTO l_expiration_date FROM MTL_LOT_NUMBERS
11135: WHERE ORGANIZATION_ID =p_xfr_organization_id
11136: AND INVENTORY_ITEM_ID = p_inventory_item_id
11137: AND LOT_NUMBER =p_lot_number;
11138: EXCEPTION