[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(1),transaction_action_id, transaction_source_type_id
INTO l_count, l_transaction_action_id, l_transaction_source_type_id
FROM mtl_transaction_types mtt
WHERE mtt.transaction_type_id =
p_mtl_trx_tbl(i).transaction_type_id AND
nvl(MTT.DISABLE_DATE,SYSDATE+1) > Sysdate
group by transaction_action_id, transaction_source_type_id;
INV_GLOBALS.G_ACTION_LOGICALEXPREQRECEIPT, INV_GLOBALS.G_ACTION_RETROPRICEUPDATE,
INV_GLOBALS.G_ACTION_LOGICALRECEIPT)) THEN
IF (l_debug = 1) THEN
debug_print('Transaction Action is invalid', 9);
SELECT COUNT(1) INTO l_count
FROM HR_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND MP.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND NVL(HOU.DATE_TO, SYSDATE + 1) > Sysdate;
SELECT COUNT(1) INTO l_count
FROM HR_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = p_mtl_trx_tbl(i).transfer_organization_id
AND MP.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND NVL(HOU.DATE_TO, SYSDATE + 1) > Sysdate;
SELECT COUNT(1) INTO l_count FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
AND MSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND MSI.INVENTORY_ITEM_FLAG = 'Y';
SELECT COUNT(1) INTO l_count FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
AND MSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).transfer_organization_id
AND MSI.INVENTORY_ITEM_FLAG = 'Y';
-- 7. Retroactive Price update specific validations
IF (Nvl(p_mtl_trx_tbl(i).transaction_action_id,0) =
INV_GLOBALS.G_ACTION_RETROPRICEUPDATE AND
Nvl(p_mtl_trx_tbl(i).transaction_source_type_id,0) =
INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER) THEN
IF (p_mtl_trx_tbl(i).CONSUMPTION_RELEASE_ID IS NULL AND
p_mtl_trx_tbl(i).CONSUMPTION_PO_HEADER_ID IS NULL) THEN
IF (l_debug = 1) THEN
debug_print('Both release id and po header id are null. One of them should have a valid value', 9);
SELECT COUNT(1) INTO l_count FROM
po_headers_all po WHERE
po.po_header_id = p_mtl_trx_tbl(i).transaction_source_id AND
NVL(po.START_DATE_ACTIVE, SYSDATE - 1) <= Sysdate AND
NVL(po.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE AND ENABLED_FLAG = 'Y';
SELECT COUNT(1) INTO l_count FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND MSI.SECONDARY_INVENTORY_NAME = p_mtl_trx_tbl(i).subinventory_code
AND TRUNC(p_mtl_trx_tbl(i).transaction_date) <= NVL(MSI.DISABLE_DATE,p_mtl_trx_tbl(i).transaction_date + 1);
SELECT 1 INTO l_count FROM dual WHERE exists
(
SELECT null FROM MTL_SECONDARY_INVENTORIES MTSI,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND MSI.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
AND MTSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
-- AND MTSI.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
AND MTSI.ORGANIZATION_ID = MSI.ORGANIZATION_ID
-- AND MTSI.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MTSI.SECONDARY_INVENTORY_NAME = p_mtl_trx_tbl(i).subinventory_code
UNION
SELECT NULL FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.RESTRICT_SUBINVENTORIES_CODE = 2
AND ITM.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND ITM.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id);
SELECT COUNT(1) INTO l_count FROM MTL_ITEM_LOCATIONS MIL
WHERE MIL.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND MIL.SUBINVENTORY_CODE = p_mtl_trx_tbl(i).subinventory_code
AND MIL.INVENTORY_LOCATION_ID = p_mtl_trx_tbl(i).locator_id
AND TRUNC(p_mtl_trx_tbl(i).transaction_date) <= NVL(MIL.DISABLE_DATE,p_mtl_trx_tbl(i).transaction_date + 1);
SELECT 1 INTO l_count FROM dual WHERE exists
(
SELECT null FROM MTL_SECONDARY_LOCATORS MSL,
MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND MSI.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
AND MSI.RESTRICT_LOCATORS_CODE = 1
AND MSL.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND MSL.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSL.SUBINVENTORY_CODE = p_mtl_trx_tbl(i).subinventory_code
AND MSL.SECONDARY_LOCATOR = p_mtl_trx_tbl(i).locator_id
UNION
SELECT NULL FROM MTL_SYSTEM_ITEMS ITM
WHERE ITM.RESTRICT_LOCATORS_CODE = 2
AND ITM.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND ITM.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id);
SELECT odss.header_id INTO l_so_header_id
FROM oe_drop_ship_sources odss, rcv_transactions RT
WHERE odss.line_location_id = rt.po_line_location_id AND
rt.transaction_id = p_mtl_trx_tbl(i).rcv_transaction_id
GROUP BY odss.header_id;
SELECT 1 INTO l_count FROM mtl_sales_orders mso, oe_order_headers_all oeha
WHERE oeha.header_id = l_so_header_id
AND oeha.order_number = mso.segment1
AND mso.sales_order_id = p_mtl_trx_tbl(i).transaction_source_id
AND NVL(START_DATE_ACTIVE, SYSDATE - 1)
<= Sysdate AND NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE
AND ENABLED_FLAG = 'Y';
SELECT COUNT(1) INTO l_count FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = p_mtl_trx_tbl(i).distribution_account_id
AND GCC.CHART_OF_ACCOUNTS_ID = (SELECT CHART_OF_ACCOUNTS_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD
WHERE OOD.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id)
AND GCC.ENABLED_FLAG = 'Y'
AND NVL(GCC.START_DATE_ACTIVE, SYSDATE - 1) <= SYSDATE
AND NVL(GCC.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE;
SELECT ACCT_PERIOD_ID
INTO l_acct_period_id
FROM ORG_ACCT_PERIODS
WHERE PERIOD_CLOSE_DATE IS NULL
AND ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND TRUNC(SCHEDULE_CLOSE_DATE) >=
TRUNC(nvl(p_mtl_trx_tbl(i).transaction_date,SYSDATE))
AND TRUNC(PERIOD_START_DATE) <=
TRUNC(nvl(p_mtl_trx_tbl(i).transaction_date,SYSDATE)) ;
SELECT acct_period_id
INTO l_acct_period_id
FROM org_acct_periods
WHERE period_close_date IS NULL
AND organization_id = p_mtl_trx_tbl(i).organization_id
AND (schedule_close_date + 1) >
inv_le_timezone_pub.get_le_day_for_inv_org(nvl(p_mtl_trx_tbl(i).transaction_date,SYSDATE),
p_mtl_trx_tbl(i).organization_id)
AND period_start_date <=
inv_le_timezone_pub.get_le_day_for_inv_org(nvl(p_mtl_trx_tbl(i).transaction_date,SYSDATE),
p_mtl_trx_tbl(i).organization_id) ;
SELECT COUNT(1) INTO l_count
FROM MTL_ITEM_UOMS_VIEW MIUV
WHERE MIUV.INVENTORY_ITEM_ID = p_mtl_trx_tbl(i).inventory_item_id
AND MIUV.ORGANIZATION_ID = p_mtl_trx_tbl(i).organization_id
AND MIUV.UOM_CODE = p_mtl_trx_tbl(i).transaction_uom;
SELECT COUNT(1) INTO l_count
FROM CST_COST_GROUPS CCG
WHERE CCG.COST_GROUP_ID = p_mtl_trx_tbl(i).cost_group_id
AND NVL(CCG.ORGANIZATION_ID, p_mtl_trx_tbl(i).organization_id) = p_mtl_trx_tbl(i).organization_id
AND TRUNC(NVL(CCG.DISABLE_DATE,SYSDATE+1)) >= TRUNC(SYSDATE);
SELECT COUNT(1) INTO l_count
FROM CST_COST_GROUPS CCG
WHERE CCG.COST_GROUP_ID = p_mtl_trx_tbl(i).transfer_cost_group_id
AND NVL(CCG.ORGANIZATION_ID, p_mtl_trx_tbl(i).transfer_organization_id) = p_mtl_trx_tbl(i).transfer_organization_id
AND TRUNC(NVL(CCG.DISABLE_DATE,SYSDATE+1)) >= TRUNC(SYSDATE);
SELECT project_id, task_id INTO l_project_id, l_task_id FROM
mtl_item_locations WHERE inventory_location_id =
p_mtl_trx_tbl(i).locator_id AND organization_id =
p_mtl_trx_tbl(i).organization_id;
SELECT COUNT(1) INTO l_count FROM
mtl_sales_orders WHERE
sales_order_id = p_mtl_trx_tbl(i).transaction_source_id;
SELECT COUNT(1) INTO l_count FROM
po_headers_all po, rcv_transactions rcv WHERE
po.po_header_id = rcv.po_header_id AND
po.po_header_id = p_mtl_trx_tbl(i).transaction_source_id AND
rcv.transaction_id = p_mtl_trx_tbl(i).rcv_transaction_id AND
NVL(po.START_DATE_ACTIVE, SYSDATE - 1) <= Sysdate AND
NVL(po.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE AND ENABLED_FLAG = 'Y';
SELECT COUNT(1) INTO l_count FROM
oe_order_lines_all WHERE
line_id = p_mtl_trx_tbl(i).trx_source_line_id;
| Procedure : INV_MMT_INSERT |
| |
| Description : This API will be called by INV create logical transactions |
| API to do a bulk insert into MTL_MATERIAL_TRANSACTIONS |
| table. |
| |
| Input Parameters : |
| p_api_version_number - API version number |
| p_init_msg_lst - Whether initialize the error message list or not|
| Should be fnd_api.g_false or fnd_api.g_true |
| p_mtl_trx_rec - An array of mtl_trx_rec_type records |
| |
| Output Parameters : |
| x_return_status - fnd_api.g_ret_sts_success, if succeeded |
| fnd_api.g_ret_sts_exc_error, if an expected |
| error occurred |
| fnd_api.g_ret_sts_unexp_error, if an unexpected |
| eror occurred |
| x_msg_count - Number of error message in the error message |
| list |
| x_msg_data - If the number of error message in the error |
| message list is one, the error message is in |
| this output parameter |
*==========================================================================*/
PROCEDURE inv_mmt_insert
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
, p_mtl_trx_tbl IN inv_logical_transaction_global.mtl_trx_tbl_type
, p_logical_trx_type_code IN NUMBER
)
IS
-- p_mtl_trx_tbl(i) inv_logical_transaction_global.mtl_trx_tbl_type := p_mtl_trx_tbl;
l_api_name CONSTANT VARCHAR2(30) := 'Inv_Mmt_Insert';
debug_print('Enter inv_mmt_insert', 9);
debug_print('Inside inv insert API', 9);
SELECT NVL(process_enabled_flag, 'N')
INTO l_process_enabled_flag
FROM mtl_parameters
WHERE organization_id = p_mtl_trx_tbl(i).ORGANIZATION_ID;
INSERT
INTO MTL_MATERIAL_TRANSACTIONS
( TRANSACTION_ID
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,REVISION
,SUBINVENTORY_CODE
,LOCATOR_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_SOURCE_ID
,TRANSACTION_SOURCE_NAME
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,PRIMARY_QUANTITY
,TRANSACTION_DATE
,ACCT_PERIOD_ID
,DISTRIBUTION_ACCOUNT_ID
,COSTED_FLAG
,ACTUAL_COST
,INVOICED_FLAG
,TRANSACTION_COST
,CURRENCY_CODE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_DATE
,PM_COST_COLLECTED
,TRX_SOURCE_LINE_ID
,SOURCE_CODE
,RCV_TRANSACTION_ID
,SOURCE_LINE_ID
,TRANSFER_ORGANIZATION_ID
,TRANSFER_SUBINVENTORY
,TRANSFER_LOCATOR_ID
,COST_GROUP_ID
,TRANSFER_COST_GROUP_ID
,PROJECT_ID
,TASK_ID
,TO_PROJECT_ID
,TO_TASK_ID
,SHIP_TO_LOCATION_ID
,TRANSACTION_MODE
,TRANSACTION_BATCH_ID
,TRANSACTION_BATCH_SEQ
,TRX_FLOW_HEADER_ID
,INTERCOMPANY_COST
,INTERCOMPANY_CURRENCY_CODE
,INTERCOMPANY_PRICING_OPTION
,parent_transaction_id
,lpn_id
,logical_trx_type_code
,logical_transaction
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,quantity_adjusted
,so_issue_account_type
,opm_costed_flag
)
VALUES
( p_mtl_trx_tbl(i).TRANSACTION_ID
,p_mtl_trx_tbl(i).ORGANIZATION_ID
,p_mtl_trx_tbl(i).INVENTORY_ITEM_ID
,p_mtl_trx_tbl(i).REVISION
,p_mtl_trx_tbl(i).SUBINVENTORY_CODE
,p_mtl_trx_tbl(i).LOCATOR_ID
,p_mtl_trx_tbl(i).TRANSACTION_TYPE_ID
,p_mtl_trx_tbl(i).TRANSACTION_ACTION_ID
,p_mtl_trx_tbl(i).TRANSACTION_SOURCE_TYPE_ID
,p_mtl_trx_tbl(i).TRANSACTION_SOURCE_ID
,p_mtl_trx_tbl(i).TRANSACTION_SOURCE_NAME
,l_transaction_quantity
,p_mtl_trx_tbl(i).TRANSACTION_UOM
,l_primary_quantity
,p_mtl_trx_tbl(i).TRANSACTION_DATE
,p_mtl_trx_tbl(i).ACCT_PERIOD_ID
,p_mtl_trx_tbl(i).DISTRIBUTION_ACCOUNT_ID
,decode(l_process_enabled_flag, 'N', p_mtl_trx_tbl(i).COSTED_FLAG, NULL) -- Bug 5044147
,p_mtl_trx_tbl(i).ACTUAL_COST
,p_mtl_trx_tbl(i).INVOICED_FLAG
,p_mtl_trx_tbl(i).TRANSACTION_COST
,p_mtl_trx_tbl(i).CURRENCY_CODE
,p_mtl_trx_tbl(i).CURRENCY_CONVERSION_RATE
,p_mtl_trx_tbl(i).CURRENCY_CONVERSION_TYPE
,p_mtl_trx_tbl(i).CURRENCY_CONVERSION_DATE
,p_mtl_trx_tbl(i).PM_COST_COLLECTED
,p_mtl_trx_tbl(i).TRX_SOURCE_LINE_ID
,p_mtl_trx_tbl(i).SOURCE_CODE
,p_mtl_trx_tbl(i).RCV_TRANSACTION_ID
,p_mtl_trx_tbl(i).SOURCE_LINE_ID
,p_mtl_trx_tbl(i).TRANSFER_ORGANIZATION_ID
,p_mtl_trx_tbl(i).TRANSFER_SUBINVENTORY
,p_mtl_trx_tbl(i).TRANSFER_LOCATOR_ID
,p_mtl_trx_tbl(i).COST_GROUP_ID
,p_mtl_trx_tbl(i).TRANSFER_COST_GROUP_ID
,p_mtl_trx_tbl(i).PROJECT_ID
,p_mtl_trx_tbl(i).TASK_ID
,p_mtl_trx_tbl(i).TO_PROJECT_ID
,p_mtl_trx_tbl(i).TO_TASK_ID
,p_mtl_trx_tbl(i).SHIP_TO_LOCATION_ID
,p_mtl_trx_tbl(i).TRANSACTION_MODE
,p_mtl_trx_tbl(i).TRANSACTION_BATCH_ID
,p_mtl_trx_tbl(i).TRANSACTION_BATCH_SEQ
,p_mtl_trx_tbl(i).TRX_FLOW_HEADER_ID
,p_mtl_trx_tbl(i).INTERCOMPANY_COST
,p_mtl_trx_tbl(i).INTERCOMPANY_CURRENCY_CODE
,p_mtl_trx_tbl(i).INTERCOMPANY_PRICING_OPTION
,p_mtl_trx_tbl(i).parent_transaction_id
,p_mtl_trx_tbl(i).lpn_id
,p_logical_trx_type_code
,l_logical_transaction
,Sysdate
,FND_GLOBAL.user_id
,Sysdate
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,l_quantity_adjusted
,2--deffered cogs
,decode(l_process_enabled_flag, 'Y', 'N', NULL) -- Bug 5044147
);
debug_print('After inv insert', 9);
END inv_mmt_insert;
| Procedure : INV_LOT_SERIAL_INSERT |
| |
| Description : This API will be called by INV create_logical_transactions |
| API to do a bulk insert into mtl_transaction_lot_numbers if|
| the item is lot control and insert into |
| mtl_unit_transactions if the item is serial control. |
| |
| Input Parameters : |
| p_api_version_number - API version number |
| p_init_msg_lst - Whether initialize the error message list or not|
| Should be fnd_api.g_false or fnd_api.g_true |
| p_parent_transaction_id - the transaction id of the parent transaction|
| in mmt. |
| p_transaction_id - the transaction id of the new logical |
| transaction in mmt. |
| p_lot_control_code - the lot control code of the item |
| p_serial_control_code - the serial control code of the item |
| |
| Output Parameters : |
| x_return_status - fnd_api.g_ret_sts_success, if succeeded |
| fnd_api.g_ret_sts_exc_error, if an expected |
| error occurred |
| fnd_api.g_ret_sts_unexp_error, if an unexpected |
| eror occurred |
| x_msg_count - Number of error message in the error message |
| list |
| x_msg_data - If the number of error message in the error |
| message list is one, the error message is in |
| this output parameter |
*==========================================================================*/
PROCEDURE inv_lot_serial_insert
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_api_version_number IN NUMBER := 1.0
, p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
, p_parent_transaction_id IN NUMBER
, p_transaction_id IN NUMBER
, p_lot_control_code IN NUMBER
, p_serial_control_code IN NUMBER
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_primary_quantity IN NUMBER
, p_trx_source_type_id IN NUMBER
, p_revision IN VARCHAR2
)
IS
l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
l_api_name CONSTANT VARCHAR2(30) := 'Inv_Mmt_Insert';
SELECT INVENTORY_ITEM_ID,
ORGANIZATION_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
LOT_NUMBER,
SERIAL_TRANSACTION_ID,
DESCRIPTION,
VENDOR_NAME,
SUPPLIER_LOT_NUMBER,
ORIGINATION_DATE,
DATE_CODE,
GRADE_CODE,
CHANGE_DATE,
MATURITY_DATE,
STATUS_ID,
RETEST_DATE,
AGE,
ITEM_SIZE,
COLOR,
VOLUME,
VOLUME_UOM,
PLACE_OF_ORIGIN,
BEST_BY_DATE,
LENGTH,
LENGTH_UOM,
WIDTH,
WIDTH_UOM,
RECYCLED_CONTENT,
THICKNESS,
THICKNESS_UOM,
CURL_WRINKLE_FOLD,
LOT_ATTRIBUTE_CATEGORY,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
VENDOR_ID,
TERRITORY_CODE,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM mtl_transaction_lot_numbers
WHERE transaction_id = p_transaction_id;
debug_print('Enter inv_mmt_insert', 9);
SELECT mtl_material_transactions_s.nextval
INTO l_serial_transaction_id
FROM dual;
select transaction_quantity,
primary_quantity,
transaction_source_id,
transaction_source_type_id,
transaction_source_name
into l_mmt_trx_qty,
l_mmt_pri_qty,
l_mmt_src_id,
l_mmt_src_type_id,
l_mmt_src_name
from mtl_material_transactions
where transaction_id = p_transaction_id;
INSERT INTO mtl_transaction_lot_numbers
( TRANSACTION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_DATE
,TRANSACTION_SOURCE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_SOURCE_NAME
,TRANSACTION_QUANTITY
,PRIMARY_QUANTITY
,LOT_NUMBER
,SERIAL_TRANSACTION_ID
,DESCRIPTION
,VENDOR_NAME
,SUPPLIER_LOT_NUMBER
,ORIGINATION_DATE
,DATE_CODE
,GRADE_CODE
,CHANGE_DATE
,MATURITY_DATE
,STATUS_ID
,RETEST_DATE
,AGE
,ITEM_SIZE
,COLOR
,VOLUME
,VOLUME_UOM
,PLACE_OF_ORIGIN
,BEST_BY_DATE
,LENGTH
,LENGTH_UOM
,WIDTH
,WIDTH_UOM
,RECYCLED_CONTENT
,THICKNESS
,THICKNESS_UOM
,CURL_WRINKLE_FOLD
,LOT_ATTRIBUTE_CATEGORY
,C_ATTRIBUTE1
,C_ATTRIBUTE2
,C_ATTRIBUTE3
,C_ATTRIBUTE4
,C_ATTRIBUTE5
,C_ATTRIBUTE6
,C_ATTRIBUTE7
,C_ATTRIBUTE8
,C_ATTRIBUTE9
,C_ATTRIBUTE10
,C_ATTRIBUTE11
,C_ATTRIBUTE12
,C_ATTRIBUTE13
,C_ATTRIBUTE14
,C_ATTRIBUTE15
,C_ATTRIBUTE16
,C_ATTRIBUTE17
,C_ATTRIBUTE18
,C_ATTRIBUTE19
,C_ATTRIBUTE20
,D_ATTRIBUTE1
,D_ATTRIBUTE2
,D_ATTRIBUTE3
,D_ATTRIBUTE4
,D_ATTRIBUTE5
,D_ATTRIBUTE6
,D_ATTRIBUTE7
,D_ATTRIBUTE8
,D_ATTRIBUTE9
,D_ATTRIBUTE10
,N_ATTRIBUTE1
,N_ATTRIBUTE2
,N_ATTRIBUTE3
,N_ATTRIBUTE4
,N_ATTRIBUTE5
,N_ATTRIBUTE6
,N_ATTRIBUTE7
,N_ATTRIBUTE8
,N_ATTRIBUTE9
,N_ATTRIBUTE10
,VENDOR_ID
,TERRITORY_CODE
,PRODUCT_CODE
,PRODUCT_TRANSACTION_ID
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
)
VALUES(
p_transaction_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,l_mtln.INVENTORY_ITEM_ID
,l_mtln.ORGANIZATION_ID
,SYSDATE
/* Bug 8530979 */
,l_mmt_src_id
,l_mmt_src_type_id
,l_mmt_src_name
,sign(l_mmt_trx_qty)*abs(l_mtln.TRANSACTION_QUANTITY)
,sign(l_mmt_pri_qty)*abs(l_mtln.PRIMARY_QUANTITY)
/* End Bug 8530979 */
,l_mtln.LOT_NUMBER
,l_serial_transaction_id
,l_mtln.DESCRIPTION
,l_mtln.VENDOR_NAME
,l_mtln.SUPPLIER_LOT_NUMBER
,l_mtln.ORIGINATION_DATE
,l_mtln.DATE_CODE
,l_mtln.GRADE_CODE
,l_mtln.CHANGE_DATE
,l_mtln.MATURITY_DATE
,l_mtln.STATUS_ID
,l_mtln.RETEST_DATE
,l_mtln.AGE
,l_mtln.ITEM_SIZE
,l_mtln.COLOR
,l_mtln.VOLUME
,l_mtln.VOLUME_UOM
,l_mtln.PLACE_OF_ORIGIN
,l_mtln.BEST_BY_DATE
,l_mtln.LENGTH
,l_mtln.LENGTH_UOM
,l_mtln.WIDTH
,l_mtln.WIDTH_UOM
,l_mtln.RECYCLED_CONTENT
,l_mtln.THICKNESS
,l_mtln.THICKNESS_UOM
,l_mtln.CURL_WRINKLE_FOLD
,l_mtln.LOT_ATTRIBUTE_CATEGORY
,l_mtln.C_ATTRIBUTE1
,l_mtln.C_ATTRIBUTE2
,l_mtln.C_ATTRIBUTE3
,l_mtln.C_ATTRIBUTE4
,l_mtln.C_ATTRIBUTE5
,l_mtln.C_ATTRIBUTE6
,l_mtln.C_ATTRIBUTE7
,l_mtln.C_ATTRIBUTE8
,l_mtln.C_ATTRIBUTE9
,l_mtln.C_ATTRIBUTE10
,l_mtln.C_ATTRIBUTE11
,l_mtln.C_ATTRIBUTE12
,l_mtln.C_ATTRIBUTE13
,l_mtln.C_ATTRIBUTE14
,l_mtln.C_ATTRIBUTE15
,l_mtln.C_ATTRIBUTE16
,l_mtln.C_ATTRIBUTE17
,l_mtln.C_ATTRIBUTE18
,l_mtln.C_ATTRIBUTE19
,l_mtln.C_ATTRIBUTE20
,l_mtln.D_ATTRIBUTE1
,l_mtln.D_ATTRIBUTE2
,l_mtln.D_ATTRIBUTE3
,l_mtln.D_ATTRIBUTE4
,l_mtln.D_ATTRIBUTE5
,l_mtln.D_ATTRIBUTE6
,l_mtln.D_ATTRIBUTE7
,l_mtln.D_ATTRIBUTE8
,l_mtln.D_ATTRIBUTE9
,l_mtln.D_ATTRIBUTE10
,l_mtln.N_ATTRIBUTE1
,l_mtln.N_ATTRIBUTE2
,l_mtln.N_ATTRIBUTE3
,l_mtln.N_ATTRIBUTE4
,l_mtln.N_ATTRIBUTE5
,l_mtln.N_ATTRIBUTE6
,l_mtln.N_ATTRIBUTE7
,l_mtln.N_ATTRIBUTE8
,l_mtln.N_ATTRIBUTE9
,l_mtln.N_ATTRIBUTE10
,l_mtln.VENDOR_ID
,l_mtln.TERRITORY_CODE
,l_mtln.PRODUCT_CODE
,l_mtln.PRODUCT_TRANSACTION_ID
,l_mtln.ATTRIBUTE_CATEGORY
,l_mtln.ATTRIBUTE1
,l_mtln.ATTRIBUTE2
,l_mtln.ATTRIBUTE3
,l_mtln.ATTRIBUTE4
,l_mtln.ATTRIBUTE5
,l_mtln.ATTRIBUTE6
,l_mtln.ATTRIBUTE7
,l_mtln.ATTRIBUTE8
,l_mtln.ATTRIBUTE9
,l_mtln.ATTRIBUTE10
,l_mtln.ATTRIBUTE11
,l_mtln.ATTRIBUTE12
,l_mtln.ATTRIBUTE13
,l_mtln.ATTRIBUTE14
,l_mtln.ATTRIBUTE15
);
debug_print('Before calling inv_mut_insert', 9);
inv_mut_insert
( x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_serial_number_tbl => l_serial_number_tbl,
p_parent_serial_trx_id => l_mtln.serial_transaction_id,
p_serial_transaction_id => l_serial_transaction_id,
p_organization_id => null,
p_inventory_item_id => null,
p_trx_source_type_id => null,
p_receipt_issue_type => null);
debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
inv_mut_insert
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_serial_number_tbl => l_serial_number_tbl,
p_parent_serial_trx_id => null,
p_serial_transaction_id => l_serial_transaction_id,
p_organization_id => l_mtln.organization_id,
p_inventory_item_id => l_mtln.inventory_item_id,
p_trx_source_type_id => l_mtln.transaction_source_type_id,
p_receipt_issue_type => 1);
debug_print('inv_mut_insert returns error: ' || x_msg_data, 9);
debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
debug_print('Before calling inv_mut_insert', 9);
inv_mut_insert
( x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_serial_number_tbl => l_serial_number_tbl,
p_parent_serial_trx_id => p_parent_transaction_id,
p_serial_transaction_id => p_transaction_id,
p_organization_id => null,
p_inventory_item_id => null,
p_trx_source_type_id => null,
p_receipt_issue_type => null);
debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
debug_print('Before calling inv_mut_insert', 9);
inv_mut_insert
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_serial_number_tbl => l_serial_number_tbl,
p_parent_serial_trx_id => null,
p_serial_transaction_id => p_transaction_id,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_trx_source_type_id => p_trx_source_type_id,
p_receipt_issue_type => 1);
debug_print('inv_mut_insert returns error: ' || x_msg_data, 9);
debug_print('inv_mut_insert returns unexpected error: ' || x_msg_data, 9);
debug_print('Before calling update_serial_numbers', 9);
update_serial_numbers
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ser_num_tbl => l_serial_number_tbl,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id);
debug_print('update_serial_numbers returns error: ' || x_msg_data, 9);
debug_print('update_serial_numbers returns unexpected error: ' || x_msg_data, 9);
debug_print('Before returning from inv_lot_serial_insert', 9);
END inv_lot_serial_insert;
PROCEDURE inv_mut_insert
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_serial_number_tbl IN OUT NOCOPY VARCHAR30_TBL
, p_parent_serial_trx_id IN NUMBER
, p_serial_transaction_id IN NUMBER
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_trx_source_type_id IN NUMBER
, p_receipt_issue_type IN NUMBER
)
IS
l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
SELECT SERIAL_NUMBER,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_NAME,
RECEIPT_ISSUE_TYPE,
CUSTOMER_ID,
SHIP_ID,
SERIAL_ATTRIBUTE_CATEGORY,
ORIGINATION_DATE,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10,
STATUS_ID,
TERRITORY_CODE,
TIME_SINCE_NEW,
CYCLES_SINCE_NEW,
TIME_SINCE_OVERHAUL,
CYCLES_SINCE_OVERHAUL,
TIME_SINCE_REPAIR,
CYCLES_SINCE_REPAIR,
TIME_SINCE_VISIT,
CYCLES_SINCE_VISIT,
TIME_SINCE_MARK,
CYCLES_SINCE_MARK,
NUMBER_OF_REPAIRS,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
FROM mtl_unit_transactions
WHERE transaction_id = p_transaction_id;
debug_print('Enter inv_mut_insert', 9);
INSERT INTO mtl_unit_transactions
(
TRANSACTION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,SERIAL_NUMBER
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,SUBINVENTORY_CODE
,LOCATOR_ID
,TRANSACTION_DATE
,TRANSACTION_SOURCE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_SOURCE_NAME
,RECEIPT_ISSUE_TYPE
,CUSTOMER_ID
,SHIP_ID
,SERIAL_ATTRIBUTE_CATEGORY
,ORIGINATION_DATE
,C_ATTRIBUTE1
,C_ATTRIBUTE2
,C_ATTRIBUTE3
,C_ATTRIBUTE4
,C_ATTRIBUTE5
,C_ATTRIBUTE6
,C_ATTRIBUTE7
,C_ATTRIBUTE8
,C_ATTRIBUTE9
,C_ATTRIBUTE10
,C_ATTRIBUTE11
,C_ATTRIBUTE12
,C_ATTRIBUTE13
,C_ATTRIBUTE14
,C_ATTRIBUTE15
,C_ATTRIBUTE16
,C_ATTRIBUTE17
,C_ATTRIBUTE18
,C_ATTRIBUTE19
,C_ATTRIBUTE20
,D_ATTRIBUTE1
,D_ATTRIBUTE2
,D_ATTRIBUTE3
,D_ATTRIBUTE4
,D_ATTRIBUTE5
,D_ATTRIBUTE6
,D_ATTRIBUTE7
,D_ATTRIBUTE8
,D_ATTRIBUTE9
,D_ATTRIBUTE10
,N_ATTRIBUTE1
,N_ATTRIBUTE2
,N_ATTRIBUTE3
,N_ATTRIBUTE4
,N_ATTRIBUTE5
,N_ATTRIBUTE6
,N_ATTRIBUTE7
,N_ATTRIBUTE8
,N_ATTRIBUTE9
,N_ATTRIBUTE10
,STATUS_ID
,TERRITORY_CODE
,TIME_SINCE_NEW
,CYCLES_SINCE_NEW
,TIME_SINCE_OVERHAUL
,CYCLES_SINCE_OVERHAUL
,TIME_SINCE_REPAIR
,CYCLES_SINCE_REPAIR
,TIME_SINCE_VISIT
,CYCLES_SINCE_VISIT
,TIME_SINCE_MARK
,CYCLES_SINCE_MARK
,NUMBER_OF_REPAIRS
,PRODUCT_CODE
,PRODUCT_TRANSACTION_ID
)
VALUES
(
p_serial_transaction_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,l_mut.SERIAL_NUMBER
,l_mut.INVENTORY_ITEM_ID
,l_mut.ORGANIZATION_ID
,l_mut.SUBINVENTORY_CODE
,l_mut.LOCATOR_ID
,SYSDATE
,l_mut.TRANSACTION_SOURCE_ID
,l_mut.TRANSACTION_SOURCE_TYPE_ID
,l_mut.TRANSACTION_SOURCE_NAME
,l_mut.RECEIPT_ISSUE_TYPE
,l_mut.CUSTOMER_ID
,l_mut.SHIP_ID
,l_mut.SERIAL_ATTRIBUTE_CATEGORY
,l_mut.ORIGINATION_DATE
,l_mut.C_ATTRIBUTE1
,l_mut.C_ATTRIBUTE2
,l_mut.C_ATTRIBUTE3
,l_mut.C_ATTRIBUTE4
,l_mut.C_ATTRIBUTE5
,l_mut.C_ATTRIBUTE6
,l_mut.C_ATTRIBUTE7
,l_mut.C_ATTRIBUTE8
,l_mut.C_ATTRIBUTE9
,l_mut.C_ATTRIBUTE10
,l_mut.C_ATTRIBUTE11
,l_mut.C_ATTRIBUTE12
,l_mut.C_ATTRIBUTE13
,l_mut.C_ATTRIBUTE14
,l_mut.C_ATTRIBUTE15
,l_mut.C_ATTRIBUTE16
,l_mut.C_ATTRIBUTE17
,l_mut.C_ATTRIBUTE18
,l_mut.C_ATTRIBUTE19
,l_mut.C_ATTRIBUTE20
,l_mut.D_ATTRIBUTE1
,l_mut.D_ATTRIBUTE2
,l_mut.D_ATTRIBUTE3
,l_mut.D_ATTRIBUTE4
,l_mut.D_ATTRIBUTE5
,l_mut.D_ATTRIBUTE6
,l_mut.D_ATTRIBUTE7
,l_mut.D_ATTRIBUTE8
,l_mut.D_ATTRIBUTE9
,l_mut.D_ATTRIBUTE10
,l_mut.N_ATTRIBUTE1
,l_mut.N_ATTRIBUTE2
,l_mut.N_ATTRIBUTE3
,l_mut.N_ATTRIBUTE4
,l_mut.N_ATTRIBUTE5
,l_mut.N_ATTRIBUTE6
,l_mut.N_ATTRIBUTE7
,l_mut.N_ATTRIBUTE8
,l_mut.N_ATTRIBUTE9
,l_mut.N_ATTRIBUTE10
,l_mut.STATUS_ID
,l_mut.TERRITORY_CODE
,l_mut.TIME_SINCE_NEW
,l_mut.CYCLES_SINCE_NEW
,l_mut.TIME_SINCE_OVERHAUL
,l_mut.CYCLES_SINCE_OVERHAUL
,l_mut.TIME_SINCE_REPAIR
,l_mut.CYCLES_SINCE_REPAIR
,l_mut.TIME_SINCE_VISIT
,l_mut.CYCLES_SINCE_VISIT
,l_mut.TIME_SINCE_MARK
,l_mut.CYCLES_SINCE_MARK
,l_mut.NUMBER_OF_REPAIRS
,l_mut.PRODUCT_CODE
,l_mut.PRODUCT_TRANSACTION_ID
);
INSERT INTO mtl_unit_transactions
(
TRANSACTION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,SERIAL_NUMBER
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_SOURCE_TYPE_ID
,RECEIPT_ISSUE_TYPE
,TRANSACTION_DATE
)
VALUES
(
p_serial_transaction_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,x_serial_number_tbl(i)
,p_inventory_item_id
,p_organization_id
,p_trx_source_type_id
,p_receipt_issue_type
,SYSDATE
);
debug_print('Before return from inv_mut_insert', 9);
END inv_mut_insert;
PROCEDURE update_serial_numbers
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_ser_num_tbl IN VARCHAR30_TBL
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
)
IS
l_debug NUMBER := NVL(fnd_profile.value('INV_DEBUG_TRACE'),0);
debug_print('Enter update_serial_numbers', 9);
UPDATE mtl_serial_numbers
SET current_status = 4
WHERE current_organization_id = p_organization_id
AND serial_number = p_ser_num_tbl(i)
AND inventory_item_id = p_inventory_item_id;
debug_print('The number of rows updated in mtl_serial_numbers is not equals
to the number of serial numbers that needed to be updated', 9);
END update_serial_numbers;