[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Last Update Date : 04/04/2000
|
| Jul 22, 2000 Bala Balakumar
| Bug: 1362550 - Handle if inventory txn worker is not launched.
|
| Aug 25, 2000 Bala Balakumar.
| Introduced comments on the process flow of the program.
|
| Sep 12th, 2000 Bala Balakumar
| Inventory Lot Txn Interface Improvement Project (IIIP).
|
| Oct 11th, 2000 Abedajna
| Performance Tuning
|
| Jun 27th, 2001 Bala Balakumar
| Unhandled exceptions will be handled now appropriately.
| TRANSLATE functionality now should allow change of Lot Name also.
|
| Jul 27th, 2001 Shashi Bhaskaran
| Bugfix 2449452: Made group_id an optional parameter and made necessary changes
| to the code as per the open-interface document.
|==========================================================================*/
x_exp_date DATE;
SELECT header_id
FROM wsm_lot_split_merges_interface
WHERE group_id IS NULL
AND process_status = WSMPINVL.PENDING
AND transaction_date <= SYSDATE
ORDER BY transaction_date;
SELECT transaction_id, header_id, process_status,
transaction_type_id, created_by, transaction_date
, organization_id -- bugfix 2832025/2841055: added orgn_id
FROM wsm_lot_split_merges_interface
WHERE nvl(group_id,-1) = nvl(p_group_id, nvl(group_id,-1) )
AND header_id = nvl(p_header_id, header_id)
AND process_status = WSMPINVL.PENDING
AND transaction_date <= SYSDATE
ORDER BY transaction_date;
SELECT transaction_id, header_id, process_status,
transaction_type_id, created_by, transaction_date
FROM wsm_lot_split_merges_interface
WHERE nvl(group_id,-1) = nvl(p_group_id, nvl(group_id,-1) )
AND header_id = nvl(p_header_id, header_id)
AND process_status = WSMPINVL.COMPLETE
AND transaction_date <= SYSDATE
ORDER BY transaction_date;
UPDATE wsm_lot_split_merges_interface
SET group_id = wsm_lot_sm_ifc_header_s.nextval
WHERE header_id = l_header_id
RETURNING group_id INTO l_group_id;
fnd_file.put_line(fnd_file.log, 'Updated group_id to '||l_group_id||
' for header_id:'||l_header_id);
fnd_file.put_line(fnd_file.log, 'Failed to Update group_id for header_id: '||l_header_id);
select count(1) into rep_flag_count from wsm_starting_lots_interface wsli
where wsli.representative_flag='Y' and wsli.header_id=txn.header_id;
/* select wsm_split_merge_transactions_s.nextval
** into txn.transaction_id
** from dual;
** update wsm_lot_split_merges_interface
** set transaction_id = txn.transaction_id
** Where header_id = txn.header_id;
update wsm_lot_split_merges_interface
set transaction_id = wsm_split_merge_transactions_s.nextval
Where header_id = txn.header_id
returning transaction_id into txn.transaction_id;
select count(*) into x_cnt
from mtl_material_transactions
where source_line_id=txn.transaction_id
and organization_id = txn.organization_id -- ADD: BUG2832025/2841055
and transaction_date = txn.transaction_date; -- ADD: BUG2832025/2841055
select 1 into x_cnt
from mtl_material_transactions
where transaction_source_type_id = 13 --ADD : BUG 3756725
--and transaction_id = txn.transaction_id --removed: bug 4401205
and transaction_date = txn.transaction_date --ADD : bug 4919094
and organization_id = txn.organization_id --ADD : BUG 3756725
and source_code = 'WSM'
and source_line_id = txn.transaction_id
and rownum=1; --ADD : BUG 3756725
x_message := 'Insert into genealogy tables failed : '||l_err_msg ;
x_message := 'Insert into genealogy tables failed : '||l_err_msg ;
* this procedure calls the API that inserts
* into mtl_object_genealogy
**/
PROCEDURE enter_genealogy_records ( p_transaction_id NUMBER ,
p_transaction_type_id NUMBER,
p_header_id NUMBER,
p_process_status NUMBER ,
err_status OUT NOCOPY NUMBER ,
o_err_message OUT NOCOPY VARCHAR ) IS
l_return_status VARCHAR2(200);
SELECT wssl.lot_number,
wssl.inventory_item_id,
wssl.organization_id,
wsrl.lot_number parent_lot_number,
wsrl.inventory_item_id parent_inventory_item_id,
wsrl.organization_id parent_organization_id
FROM wsm_sm_starting_lots wssl,
wsm_sm_resulting_lots wsrl
WHERE wssl.transaction_id = wsrl.transaction_id --p_transaction_id
-- To avoid MERGE JOIN CARTESIAN
AND wsrl.transaction_id = p_transaction_id;
inv_genealogy_pub.insert_genealogy
( p_api_version => 1.0,
p_object_type => 1,
p_parent_object_type => 1,
p_object_number => g_rec.lot_number,
p_inventory_item_id => g_rec.inventory_item_id,
p_org_id => g_rec.organization_id,
p_parent_object_number => g_rec.parent_lot_number,
p_parent_inventory_item_id => g_rec.parent_inventory_item_id,
p_parent_org_id => g_rec.parent_organization_id,
p_genealogy_origin => 3,
p_genealogy_type => 4,
p_origin_txn_id => p_transaction_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data ) ;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO X_Header_Id
FROM DUAL;
SELECT transaction_type_id
INTO WSMISSUE
FROM mtl_transaction_types
--WHERE transaction_type_name = 'Miscellaneous issue';
SELECT transaction_type_id
INTO WSMRECEIPT
FROM mtl_transaction_types
--WHERE transaction_type_name = 'Miscellaneous receipt';
SELECT
organization_id
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id;
** select the org and ensure in the Parent table WLSMI a
** record exists.
*/
for crec in c loop
BEGIN
SELECT 1
into x_dummy
FROM wsm_lot_split_merges_interface wlsmi
WHERE wlsmi.header_id = p_header_id
AND wlsmi.organization_id = crec.organization_id;
SELECT
lot_number,
/*BA#1414465*/
inventory_item_id,
/*EA#1414465*/
organization_id,
quantity,
subinventory_code,
locator_id,
revision,
last_updated_by,
created_by
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id;
select inventory_item_id, subinventory_code, locator_id, revision, quantity
from wsm_source_lots_v
where lot_number = lot_n
and organization_id = org_id;
SELECT 1
into x_dummy
FROM fnd_user
WHERE user_id = crec.created_by
AND sysdate between start_date
and nvl(END_date, sysdate + 1);
** BBK - Oct 24th, 2000 - Update
** Pseudocode for check.
** Here are the conditions which are fine;
select nvl(serial_number_control_code,2)
into l_serial_ctrl
from mtl_system_items_kfv
WHERE organization_id = crec.organization_id
AND inventory_item_id = crec.inventory_item_id;
SELECT 1 into x_dummy
FROM DUAL
Where exists (select 1
From mtl_transaction_lots_temp
WHERE lot_number=crec.lot_number);
SELECT 0 into x_dummy -- Fine if it satisfies this condition.
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = crec.organization_id
and mmtt.inventory_item_id = crec.inventory_item_id
and NVL(mmtt.lot_number, '@#$') = crec.lot_number
and mmtt.subinventory_code = crec.subinventory_code
and NVL(mmtt.locator_id, -9999) = NVL(crec.locator_id, -9999)
and mmtt.transaction_type_id = 42 -- Miscellaneous Receipt
and mmtt.transaction_action_id = 27 -- Receipt into stores
and mmtt.transaction_source_type_id = 13 -- Inventory
and crec.quantity = ((-1) * mmtt.transaction_quantity)
and mmtt.transaction_date = (
SELECT max(mmtt2.transaction_date)
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.organization_id = crec.organization_id
and mmtt2.inventory_item_id = crec.inventory_item_id
and NVL(mmtt2.lot_number, '@#$') = crec.lot_number
and mmtt2.subinventory_code = crec.subinventory_code
and NVL(mmtt2.locator_id, -9999) = NVL(crec.locator_id, -9999)
);
SELECT count(*) into x_dummy
FROM mtl_transaction_lots_temp
WHERE lot_number=crec.lot_number;
-- validate last_updated_by.
BEGIN
lProcLocation := 40;
SELECT 1
into x_dummy
FROM fnd_user
WHERE user_id = crec.last_updated_by
AND sysdate between start_date
and nvl(END_date, sysdate + 1);
FND_MESSAGE.SET_TOKEN('FLD_NAME','last_updated_by in Starting Lots');
SELECT 1
into x_dummy
FROM wsm_lot_split_merges_interface
WHERE transaction_id = p_header_id
AND organization_id = crec.organization_id;
** If Yes, then select inventory_item_id, subinventory_code, locator_id, revision
** quantity and then validate it.
*/
BEGIN
lProcLocation := 50;
SELECT inventory_item_id, subinventory_code,
locator_id, revision, quantity
into x_item_id, x_sub, x_locator_id, x_revision, x_quantity
FROM WSM_SOURCE_LOTS_V
WHERE lot_number = crec.lot_number
AND organization_id = crec.organization_id;
select lot_number_uniqueness
into mtl_unique
from mtl_parameters
where organization_id = crec.organization_id;
SELECT
m.transaction_type_id,
r.lot_number,
r.organization_id,
r.inventory_item_id,
r.quantity,
r.subinventory_code,
r.locator_id,
r.revision,
r.last_updated_by,
r.created_by
FROM wsm_lot_split_merges_interface m,
wsm_resulting_lots_interface r
WHERE r.header_id = m.header_id
AND m.header_id = p_header_id;
SELECT 1
into x_dummy
FROM fnd_user
WHERE user_id = crec.created_by
AND sysdate between start_date
and nvl(END_date, sysdate + 1);
select nvl(serial_number_control_code,2)
into l_serial_ctrl
from mtl_system_items_kfv
WHERE organization_id = crec.organization_id
AND inventory_item_id = crec.inventory_item_id;
SELECT 1 into x_dummy
FROM DUAL
Where exists (select 1
From mtl_transaction_lots_temp
WHERE lot_number=crec.lot_number);
SELECT 0 into x_dummy -- Fine if it satisfies this condition.
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = crec.organization_id
and mmtt.inventory_item_id = crec.inventory_item_id
and NVL(mmtt.lot_number, '@#$') = crec.lot_number
and mmtt.subinventory_code = crec.subinventory_code
and NVL(mmtt.locator_id, -9999) = NVL(crec.locator_id, -9999)
and mmtt.transaction_type_id = 42 -- Miscellaneous Receipt
and mmtt.transaction_action_id = 27 -- Receipt into stores
and mmtt.transaction_source_type_id = 13 -- Inventory
and crec.quantity = ((-1) * mmtt.transaction_quantity)
and mmtt.transaction_date = (
SELECT max(mmtt2.transaction_date)
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.organization_id = crec.organization_id
and mmtt2.inventory_item_id = crec.inventory_item_id
and NVL(mmtt2.lot_number, '@#$') = crec.lot_number
and mmtt2.subinventory_code = crec.subinventory_code
and NVL(mmtt2.locator_id, -9999) = NVL(crec.locator_id, -9999)
);
SELECT count(*) into x_cnt1
FROM mtl_transaction_lots_temp
WHERE lot_number=crec.lot_number;
SELECT 1
into x_dummy
FROM fnd_user
WHERE user_id = crec.last_updated_by
AND sysdate between start_date
and nvl(END_date, sysdate + 1);
FND_MESSAGE.SET_TOKEN('FLD_NAME','last_updated_by in Resulting Lots');
SELECT 1
into x_dummy
FROM wsm_lot_split_merges_interface
WHERE header_id = p_header_id
AND organization_id = crec.organization_id;
** SELECT 1
** into x_dummy
** FROM dual
** WHERE not exists
** (SELECT 1
** FROM wsm_starting_lots_interface s
** WHERE s.header_id = p_header_id
** and s.lot_number = crec.lot_number);
SELECT 1
into x_dummy
FROM wsm_starting_lots_interface s
WHERE s.header_id = p_header_id
and s.lot_number = crec.lot_number;
** SELECT 1
** into x_dummy
** FROM dual
** WHERE not exists
** (
** SELECT 1
** FROM wip_entities
** WHERE wip_entity_name = crec.lot_number
** AND organization_id = crec.organization_id
** UNION
** SELECT 1
** FROM mtl_lot_numbers
** WHERE lot_number = crec.lot_number
** );
SELECT 1
into x_dummy
FROM wip_entities
WHERE wip_entity_name = crec.lot_number
AND organization_id = crec.organization_id
UNION ALL
SELECT 1
FROM mtl_lot_numbers
WHERE lot_number = crec.lot_number
AND inventory_item_id = crec.inventory_item_id --bugfix 2069033: added item_id condn.
AND organization_id = crec.organization_id; -- 4401205: added org_id
SELECT 1
into x_dummy
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id
and lot_number = crec.lot_number;
SELECT inventory_item_id
into x_dummy
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id
and lot_number = crec.lot_number
and inventory_item_id = crec.inventory_item_id; --Bug#1844972
SELECT 1
into x_dummy
FROM mtl_system_items
WHERE inventory_item_id = crec.inventory_item_id
and organization_id = crec.organization_id
and mtl_transactions_enabled_flag = 'Y'
and lot_control_code=2
and inventory_item_flag = 'Y';
SELECT 1
into x_dummy
FROM mtl_System_items
WHERE inventory_item_id = crec.inventory_item_id
and organization_id = crec.organization_id
and ((crec.revision is not null
and revision_qty_control_code <> 1) or
((crec.revision is null or
crec.revision = '0')
and revision_qty_control_code = 1));
** SELECT 1
** into x_dummy
** FROM dual
** WHERE exists
** (SELECT 1
** FROM wsm_starting_lots_interface
** WHERE header_id = p_header_id
** and inventory_item_id = crec.inventory_item_id
** and organization_id = crec.organization_id
** and nvl(revision,'@@@') = nvl(crec.revision,'@@@'));
SELECT 1
into x_dummy
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id
and inventory_item_id = crec.inventory_item_id
and organization_id = crec.organization_id
and nvl(revision,'@@@') = nvl(crec.revision,'@@@');
SELECT 1
into x_dummy
FROM mtl_item_revisions
WHERE inventory_item_id = crec.inventory_item_id
and organization_id = crec.organization_id
and revision = crec.revision;
** SELECT 1 into x_dummy FROM dual WHERE exists(
** SELECT 1
** FROM MTL_SUBINVENTORIES_VAL_V MSVV,
** MTL_SYSTEM_ITEMS MSI
** WHERE MSVV.ORGANIZATION_ID = crec.ORGANIZATION_ID
** AND MSVV.SECONDARY_INVENTORY_NAME =
** crec.SUBINVENTORY_CODE
** AND crec.inventory_ITEM_ID = MSI.INVENTORY_ITEM_ID
** AND crec.orgANIZATION_ID = MSI.ORGANIZATION_ID
** AND MSI.RESTRICT_SUBINVENTORIES_CODE <> 1
** AND MSI.INVENTORY_ASSET_FLAG = 'N'
** UNION
** SELECT 1
** FROM MTL_SUB_AST_TRK_VAL_V MSVV,
** MTL_SYSTEM_ITEMS MSI
** WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
** AND MSVV.SECONDARY_INVENTORY_NAME =
** crec.subinventory_code
** AND crec.inventory_ITEM_ID = MSI.INVENTORY_ITEM_ID
** AND crec.orgANIZATION_ID = MSI.ORGANIZATION_ID
** AND MSI.RESTRICT_SUBINVENTORIES_CODE <> 1
** AND MSI.INVENTORY_ASSET_FLAG = 'Y'
** UNION
** SELECT 1
** FROM MTL_ITEM_SUB_AST_TRK_VAL_V MSVV,
** MTL_SYSTEM_ITEMS MSI
** WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
** AND MSVV.SECONDARY_INVENTORY_NAME =
** crec.subinventory_code
** AND MSVV.inventory_item_id = crec.inventory_item_id
** AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
** AND MSI.inventory_item_id= crec.inventory_item_id
** AND MSI.organization_id = crec.organization_id
** AND MSI.INVENTORY_ASSET_FLAG = 'Y'
** UNION
** SELECT 1
** FROM MTL_ITEM_SUB_VAL_V MSVV,
** MTL_SYSTEM_ITEMS MSI
** WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
** AND MSVV.SECONDARY_INVENTORY_NAME =
** crec.subinventory_code
** AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
** AND MSVV.inventory_item_id = crec.inventory_item_id
** AND MSI.inventory_item_id= crec.inventory_item_id
** AND MSI.organization_id = crec.organization_id
** AND MSI.INVENTORY_ASSET_FLAG = 'N');
SELECT 1 into x_dummy
FROM MTL_SUBINVENTORIES_VAL_V MSVV,
MTL_SYSTEM_ITEMS MSI
WHERE MSVV.ORGANIZATION_ID = crec.ORGANIZATION_ID
AND MSVV.SECONDARY_INVENTORY_NAME =
crec.SUBINVENTORY_CODE
AND crec.inventory_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND crec.orgANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.RESTRICT_SUBINVENTORIES_CODE <> 1
AND (MSI.INVENTORY_ASSET_FLAG = 'N' OR x_exp_ast_profile = 1) --bugfix 1857638
UNION ALL
SELECT 1
FROM MTL_SUB_AST_TRK_VAL_V MSVV,
MTL_SYSTEM_ITEMS MSI
WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
AND MSVV.SECONDARY_INVENTORY_NAME =
crec.subinventory_code
AND crec.inventory_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND crec.orgANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.RESTRICT_SUBINVENTORIES_CODE <> 1
AND MSI.INVENTORY_ASSET_FLAG = 'Y'
UNION ALL
SELECT 1
FROM MTL_ITEM_SUB_AST_TRK_VAL_V MSVV,
MTL_SYSTEM_ITEMS MSI
WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
AND MSVV.SECONDARY_INVENTORY_NAME =
crec.subinventory_code
AND MSVV.inventory_item_id = crec.inventory_item_id
AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
AND MSI.inventory_item_id= crec.inventory_item_id
AND MSI.organization_id = crec.organization_id
AND MSI.INVENTORY_ASSET_FLAG = 'Y'
UNION ALL
SELECT 1
FROM MTL_ITEM_SUB_VAL_V MSVV,
MTL_SYSTEM_ITEMS MSI
WHERE MSVV.ORGANIZATION_ID = crec.orgANIZATION_ID
AND MSVV.SECONDARY_INVENTORY_NAME =
crec.subinventory_code
AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
AND MSVV.inventory_item_id = crec.inventory_item_id
AND MSI.inventory_item_id= crec.inventory_item_id
AND MSI.organization_id = crec.organization_id
AND (MSI.INVENTORY_ASSET_FLAG = 'N'OR x_exp_ast_profile = 1); --1857638
SELECT nvl(msub.locator_type, 1) sub_loc_control,
MP.stock_locator_control_code org_loc_control,
MS.restrict_locators_code,
MS.location_control_code item_loc_control
into x_sub_loc_control,
x_org_loc_control,
x_restrict_locators_code,
x_item_loc_control
FROM mtl_system_items MS,
mtl_secondary_inventories MSUB,
mtl_parameters MP
WHERE MP.organization_id = crec.organization_id
AND MS.organization_id = crec.organization_id
AND MS.inventory_item_id = crec.inventory_item_id
AND MSUB.secondary_inventory_name = crec.subinventory_code
AND MSUB.organization_id = crec.organization_id;
SELECT transaction_type_id
INTO x_transaction_type_id
FROM wsm_lot_split_merges_interface
WHERE header_id = p_header_id;
SELECT 1
into x_dummy
FROM wsm_resulting_lots_interface crec
WHERE header_id = p_header_id
and (not exists
(
SELECT 1
FROM wip_entities
WHERE wip_entity_name = crec.lot_number
AND organization_id = crec.organization_id
UNION ALL
SELECT 1
FROM mtl_lot_numbers
WHERE lot_number = crec.lot_number
AND inventory_item_id = crec.inventory_item_id -- bugfix 2069033: added item_id condn.
AND organization_id = crec.organization_id --4401205 : added org_id
) -- Should not be able to create a new lot for the SAME item.
or exists -- But, should be able to create lot if it exists with a
( -- DIFFERENT item.
SELECT 1
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id
and lot_number = crec.lot_number
)
);
** SELECT 1
** into x_dummy
** FROM dual
** WHERE not exists
** (SELECT count(*)
** FROM wsm_starting_lots_interface
** WHERE header_id = p_header_id
** group by lot_number
** having count(*) > 1);
SELECT 1
into x_dummy
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id
group by lot_number
having count(*) > 1;
SELECT count(*)
INTO x_start_ct
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id;
SELECT count(*)
INTO x_result_ct
FROM wsm_resulting_lots_interface
WHERE header_id = p_header_id;
** SELECT 1
** into x_dummy
** FROM dual
** WHERE not exists
** ( SELECT 1
** FROM wsm_starting_lots_interface s1,
** wsm_starting_lots_interface s2
** WHERE s1.header_id = p_header_id
** and s2.header_id = p_header_id
** and (s1.inventory_item_id <> s2.inventory_item_id
** or nvl(s1.revision, '!@#') <> nvl(s1.revision, '!@#')
** )
** );
SELECT 1
into x_dummy
FROM wsm_starting_lots_interface s1,
wsm_starting_lots_interface s2
WHERE s1.header_id = p_header_id
and s2.header_id = p_header_id
and (s1.inventory_item_id <> s2.inventory_item_id
or nvl(s1.revision, '!@#') <> nvl(s1.revision, '!@#'));
x_last_updated_by NUMBER;
SELECT transaction_type_id,
organization_id,
last_updated_by,
created_by,
reason_id,
transaction_date
INTO x_transaction_type_id,
x_organization_id,
x_last_updated_by,
x_created_by,
x_reason_id,
x_transaction_date
FROM wsm_lot_split_merges_interface
WHERE header_id = p_header_id;
SELECT mtl.wms_enabled_flag
INTO l_wmsEnabledFlag
FROM mtl_parameters mtl
WHERE mtl.organization_id = x_organization_id;
SELECT wp.inv_lot_txn_enabled
INTO l_invTxnEnabledFlag
FROM wsm_parameters wp
WHERE wp.organization_id = x_organization_id;
SELECT 1
into x_dummy
FROM fnd_user
WHERE user_id = x_created_by
AND sysdate between start_date
and nvl(END_date, sysdate + 1);
-- check if the last_updated_by is a valid user.
lProcLocation := 40;
SELECT 1
into x_dummy
FROM fnd_user
WHERE user_id = x_last_updated_by
AND sysdate between start_date
and nvl(END_date, sysdate + 1);
FND_MESSAGE.SET_TOKEN('FLD_NAME','last_updated_by');
SELECT 1
into x_dummy
FROM dual
WHERE exists (
SELECT 1
FROM fnd_user_responsibility f,
fnd_responsibility_tl r
WHERE r.responsibility_name like '%Inventory%'
and r.responsibility_id = f.responsibility_id
and f.user_id = x_created_by);
SELECT 1
into x_dummy
FROM HR_ORGANIZATION_UNITS
WHERE organization_id = x_organization_id
and trunc(sysdate) <= nvl(date_to, sysdate + 1);
SELECT 1
into x_dummy
FROM mtl_parameters
WHERE organization_id = x_organization_id;
SELECT 1
into x_dummy
FROM mtl_transaction_reasons_val_v
WHERE reason_id = x_reason_id;
SELECT 1
into x_dummy
FROM mfg_lookups
WHERE lookup_type = 'WSM_INV_LOT_TXN_TYPE'
and lookup_code = x_transaction_type_id;
SELECT count(1)
into x_dummy
FROM org_acct_periods
WHERE organization_id = x_organization_id
and period_start_date <= x_transaction_date
and open_flag = 'Y';
SELECT count(*)
INTO x_start_ct
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id;
SELECT count(*)
INTO x_result_ct
FROM wsm_resulting_lots_interface
WHERE header_id = p_header_id;
SELECT sum(quantity)
into x_start_ct1
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id;
SELECT sum(quantity)
into x_result_ct1
FROM wsm_resulting_lots_interface
WHERE header_id = p_header_id;
SELECT sum(quantity)
INTO x_start
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id;
SELECT sum(quantity)
INTO x_result
FROM wsm_resulting_lots_interface
WHERE header_id = p_header_id;
INSERT INTO wsm_resulting_lots_interface
(
header_id,
lot_number,
inventory_item_id,
organization_id,
revision,
quantity,
subinventory_code,
locator_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
SELECT
header_id,
lot_number,
inventory_item_id,
organization_id,
revision,
x_start - x_result,
subinventory_code,
locator_id,
sysdate,
USER,
sysdate,
USER,
LOGIN,
REQUEST,
PROGAPPL,
PROGRAM,
sysdate,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM wsm_starting_lots_interface
WHERE header_id = p_header_id;
('Just after inserting extra record for header Id:'||p_header_id));
SELECT distinct
x.expiration_date,
a.lot_number,
a.inventory_item_id,
a.revision,
a.subinventory_code,
a.locator_id,
a.quantity,
a.representative_flag -- added by sisankar for bug 4920235
FROM
mtl_lot_numbers x,
wsm_starting_lots_interface a
WHERE header_id = p_header_id
AND X.lot_number = a.lot_number
-- bugfix 1995378: added the orgn id and inventory item id condition
AND x.organization_id = a.organization_id
AND x.inventory_item_id = a.inventory_item_id;
SELECT
lot_number,
inventory_item_id,
revision,
subinventory_code,
locator_id,
quantity
FROM wsm_resulting_lots_interface
WHERE header_id = p_header_id;
SELECT reason_id, transaction_reference, organization_id,
transaction_date
INTO x_reason_id, x_reference, x_org_id, x_date
FROM wsm_lot_split_merges_interface
WHERE header_id = p_header_id;
/* SELECT max(acct_period_id)
INTO x_acct_period_id
FROM org_acct_periods
WHERE organization_id = x_org_id
AND period_start_date <= trunc(x_date)
AND open_flag = 'Y'; */
select 1,lot_attribute_category
into x_lot_attr_entered,x_rlot_context_entered
from mtl_transaction_lots_interface mtli
where mtli.product_transaction_id=p_header_id
and mtli.product_code='WSM'
and mtli.lot_number=rlot.lot_number;
-- select decode(count(1),0,'N','Y') into x_lot_exists from dual where exists
-- (select 1 from mtl_lot_numbers mln where mln.lot_number=rlot.lot_number and
-- mln.inventory_item_id=rlot.inventory_item_id and
-- mln.organization_id=x_org_id);
select 'Y'
into x_lot_exists
from mtl_lot_numbers mln
where mln.lot_number=rlot.lot_number
and mln.inventory_item_id=rlot.inventory_item_id
and mln.organization_id=x_org_id;
if x_lot_attr_entered > 0 then -- user has updated lot attributes for this particular lot
l_intf_rec_found :=TRUE;
else -- user has not updated lot attributes for this particular lot
l_intf_rec_found :=FALSE;
SELECT
attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
INTO
lot_attributes_rec.l_attribute_category
,l_invattr_tbl(1)
,l_invattr_tbl(2)
,l_invattr_tbl(3)
,l_invattr_tbl(4)
,l_invattr_tbl(5)
,l_invattr_tbl(6)
,l_invattr_tbl(7)
,l_invattr_tbl(8)
,l_invattr_tbl(9)
,l_invattr_tbl(10)
,l_invattr_tbl(11)
,l_invattr_tbl(12)
,l_invattr_tbl(13)
,l_invattr_tbl(14)
,l_invattr_tbl(15)
FROM mtl_lot_numbers
WHERE lot_number=l_slot
AND inventory_item_id=l_sitem
AND organization_id= x_org_id;
SELECT
description -- This is Not a named attr, right?
,grade_code
,origination_date
,date_code
,change_date
,age
,retest_date
,maturity_date
,item_size
,color
,volume
,volume_uom
,place_of_origin
,best_by_date
,length
,length_uom
,recycled_content
,thickness
,thickness_uom
,width
,width_uom
,vendor_id -- are vendor_id is missing in create_inv_lot
,vendor_name
,territory_code --MISSING in named record
,supplier_lot_number --MISSING in named record
,curl_wrinkle_fold --MISSING in named record
,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
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
INTO
lot_attributes_rec.l_description
,lot_attributes_rec.l_grade_code
,lot_attributes_rec.l_origination_date
,lot_attributes_rec.l_date_code
,lot_attributes_rec.l_change_date
,lot_attributes_rec.l_age
,lot_attributes_rec.l_retest_date
,lot_attributes_rec.l_maturity_date
,lot_attributes_rec.l_item_size
,lot_attributes_rec.l_color
,lot_attributes_rec.l_volume
,lot_attributes_rec.l_volume_uom
,lot_attributes_rec.l_place_of_origin
,lot_attributes_rec.l_best_by_date
,lot_attributes_rec.l_length
,lot_attributes_rec.l_length_uom
,lot_attributes_rec.l_recycled_content
,lot_attributes_rec.l_thickness
,lot_attributes_rec.l_thickness_uom
,lot_attributes_rec.l_width
,lot_attributes_rec.l_width_uom
,lot_attributes_rec.l_vendor_id
,lot_attributes_rec.l_vendor_name
,lot_attributes_rec.l_territory_code
,lot_attributes_rec.l_supplier_lot_number
,lot_attributes_rec.l_curl_wrinkle_fold
,lot_attributes_rec.l_lot_attribute_category
,l_Cattr_tbl(1)
,l_Cattr_tbl(2)
,l_Cattr_tbl(3)
,l_Cattr_tbl(4)
,l_Cattr_tbl(5)
,l_Cattr_tbl(6)
,l_Cattr_tbl(7)
,l_Cattr_tbl(8)
,l_Cattr_tbl(9)
,l_Cattr_tbl(10)
,l_Cattr_tbl(11)
,l_Cattr_tbl(12)
,l_Cattr_tbl(13)
,l_Cattr_tbl(14)
,l_Cattr_tbl(15)
,l_Cattr_tbl(16)
,l_Cattr_tbl(17)
,l_Cattr_tbl(18)
,l_Cattr_tbl(19)
,l_Cattr_tbl(20)
,l_Dattr_tbl(1)
,l_Dattr_tbl(2)
,l_Dattr_tbl(3)
,l_Dattr_tbl(4)
,l_Dattr_tbl(5)
,l_Dattr_tbl(6)
,l_Dattr_tbl(7)
,l_Dattr_tbl(8)
,l_Dattr_tbl(9)
,l_Dattr_tbl(10)
,l_Nattr_tbl(1)
,l_Nattr_tbl(2)
,l_Nattr_tbl(3)
,l_Nattr_tbl(4)
,l_Nattr_tbl(5)
,l_Nattr_tbl(6)
,l_Nattr_tbl(7)
,l_Nattr_tbl(8)
,l_Nattr_tbl(9)
,l_Nattr_tbl(10)
,lot_attributes_rec.l_attribute_category
,l_invattr_tbl(1)
,l_invattr_tbl(2)
,l_invattr_tbl(3)
,l_invattr_tbl(4)
,l_invattr_tbl(5)
,l_invattr_tbl(6)
,l_invattr_tbl(7)
,l_invattr_tbl(8)
,l_invattr_tbl(9)
,l_invattr_tbl(10)
,l_invattr_tbl(11)
,l_invattr_tbl(12)
,l_invattr_tbl(13)
,l_invattr_tbl(14)
,l_invattr_tbl(15)
FROM mtl_lot_numbers
WHERE lot_number=l_slot
AND inventory_item_id=l_sitem
AND organization_id= x_org_id;
-- modified the query with decode in select clause for bug fix 4958157
SELECT
transaction_interface_id
,decode(description,l_miss_char, NULL,NULL,lot_attributes_rec.l_description,description)
,decode(grade_code,l_miss_char, NULL,NULL,lot_attributes_rec.l_grade_code,grade_code)
,decode(origination_date,l_miss_date, NULL,NULL,lot_attributes_rec.l_origination_date,origination_date)
,decode(date_code,l_miss_char, NULL,NULL,lot_attributes_rec.l_date_code,date_code)
,decode(change_date,l_miss_date, NULL,NULL,lot_attributes_rec.l_change_date,change_date)
,decode(age,l_miss_num, NULL,NULL,lot_attributes_rec.l_age,age)
,decode(retest_date,l_miss_date, NULL,NULL,lot_attributes_rec.l_retest_date,retest_date)
,decode(maturity_date,l_miss_date, NULL,NULL,lot_attributes_rec.l_maturity_date,maturity_date)
,decode(item_size,l_miss_num, NULL,NULL,lot_attributes_rec.l_item_size,item_size)
,decode(color,l_miss_char, NULL,NULL,lot_attributes_rec.l_color,color)
,decode(volume,l_miss_num, NULL,NULL,lot_attributes_rec.l_volume,volume)
,decode(volume_uom,l_miss_char, NULL,NULL,lot_attributes_rec.l_volume_uom,volume_uom)
,decode(place_of_origin,l_miss_char, NULL,NULL,lot_attributes_rec.l_place_of_origin,place_of_origin)
,decode(best_by_date,l_miss_date, NULL,NULL,lot_attributes_rec.l_best_by_date,best_by_date)
,decode(length,l_miss_num, NULL,NULL,lot_attributes_rec.l_length,length)
,decode(length_uom,l_miss_char, NULL,NULL,lot_attributes_rec.l_length_uom,length_uom)
,decode(recycled_content,l_miss_num, NULL,NULL,lot_attributes_rec.l_recycled_content,recycled_content)
,decode(thickness,l_miss_num, NULL,NULL,lot_attributes_rec.l_thickness,thickness)
,decode(thickness_uom,l_miss_char, NULL,NULL,lot_attributes_rec.l_thickness_uom,thickness_uom)
,decode(width,l_miss_num, NULL,NULL,lot_attributes_rec.l_width,width)
,decode(width_uom,l_miss_char, NULL,NULL,lot_attributes_rec.l_width_uom,width_uom)
,decode(vendor_id,l_miss_num, NULL,NULL,lot_attributes_rec.l_vendor_id,vendor_id)
,decode(vendor_name,l_miss_char, NULL,NULL,lot_attributes_rec.l_vendor_name,vendor_name)
,decode(territory_code,l_miss_char,NULL,NULL,lot_attributes_rec.l_territory_code,territory_code)
,decode(supplier_lot_number,l_miss_char, NULL,NULL,lot_attributes_rec.l_supplier_lot_number,supplier_lot_number)
,decode(curl_wrinkle_fold,l_miss_char, NULL,NULL,lot_attributes_rec.l_curl_wrinkle_fold,curl_wrinkle_fold)
,decode(lot_attribute_category,l_miss_char, NULL,NULL,lot_attributes_rec.l_lot_attribute_category,lot_attribute_category)
,decode(c_attribute1,l_miss_char, NULL,NULL,l_Cattr_tbl(1),c_attribute1)
,decode(c_attribute2,l_miss_char, NULL,NULL,l_Cattr_tbl(2),c_attribute2)
,decode(c_attribute3,l_miss_char, NULL,NULL,l_Cattr_tbl(3),c_attribute3)
,decode(c_attribute4,l_miss_char, NULL,NULL,l_Cattr_tbl(4),c_attribute4)
,decode(c_attribute5,l_miss_char, NULL,NULL,l_Cattr_tbl(5),c_attribute5)
,decode(c_attribute6,l_miss_char, NULL,NULL,l_Cattr_tbl(6),c_attribute6)
,decode(c_attribute7,l_miss_char, NULL,NULL,l_Cattr_tbl(7),c_attribute7)
,decode(c_attribute8,l_miss_char, NULL,NULL,l_Cattr_tbl(8),c_attribute8)
,decode(c_attribute9,l_miss_char, NULL,NULL,l_Cattr_tbl(9),c_attribute9)
,decode(c_attribute10,l_miss_char, NULL,NULL,l_Cattr_tbl(10),c_attribute10)
,decode(c_attribute11,l_miss_char, NULL,NULL,l_Cattr_tbl(11),c_attribute11)
,decode(c_attribute12,l_miss_char, NULL,NULL,l_Cattr_tbl(12),c_attribute12)
,decode(c_attribute13,l_miss_char, NULL,NULL,l_Cattr_tbl(13),c_attribute13)
,decode(c_attribute14,l_miss_char, NULL,NULL,l_Cattr_tbl(14),c_attribute14)
,decode(c_attribute15,l_miss_char, NULL,NULL,l_Cattr_tbl(15),c_attribute15)
,decode(c_attribute16,l_miss_char, NULL,NULL,l_Cattr_tbl(16),c_attribute16)
,decode(c_attribute17,l_miss_char, NULL,NULL,l_Cattr_tbl(17),c_attribute17)
,decode(c_attribute18,l_miss_char, NULL,NULL,l_Cattr_tbl(18),c_attribute18)
,decode(c_attribute19,l_miss_char, NULL,NULL,l_Cattr_tbl(19),c_attribute19)
,decode(c_attribute20,l_miss_char, NULL,NULL,l_Cattr_tbl(20),c_attribute20)
,decode(d_attribute1,l_miss_date, NULL,NULL,l_Dattr_tbl(1),d_attribute1)
,decode(d_attribute2,l_miss_date, NULL,NULL,l_Dattr_tbl(2),d_attribute2)
,decode(d_attribute3,l_miss_date, NULL,NULL,l_Dattr_tbl(3),d_attribute3)
,decode(d_attribute4,l_miss_date, NULL,NULL,l_Dattr_tbl(4),d_attribute4)
,decode(d_attribute5,l_miss_date, NULL,NULL,l_Dattr_tbl(5),d_attribute5)
,decode(d_attribute6,l_miss_date, NULL,NULL,l_Dattr_tbl(6),d_attribute6)
,decode(d_attribute7,l_miss_date, NULL,NULL,l_Dattr_tbl(7),d_attribute7)
,decode(d_attribute8,l_miss_date, NULL,NULL,l_Dattr_tbl(8),d_attribute8)
,decode(d_attribute9,l_miss_date, NULL,NULL,l_Dattr_tbl(9),d_attribute9)
,decode(d_attribute10,l_miss_date, NULL,NULL,l_Dattr_tbl(10),d_attribute10)
,decode(n_attribute1,l_miss_num, NULL,NULL,l_Nattr_tbl(1),n_attribute1)
,decode(n_attribute2,l_miss_num, NULL,NULL,l_Nattr_tbl(2),n_attribute2)
,decode(n_attribute3,l_miss_num, NULL,NULL,l_Nattr_tbl(3),n_attribute3)
,decode(n_attribute4,l_miss_num, NULL,NULL,l_Nattr_tbl(4),n_attribute4)
,decode(n_attribute5,l_miss_num, NULL,NULL,l_Nattr_tbl(5),n_attribute5)
,decode(n_attribute6,l_miss_num, NULL,NULL,l_Nattr_tbl(6),n_attribute6)
,decode(n_attribute7,l_miss_num, NULL,NULL,l_Nattr_tbl(7),n_attribute7)
,decode(n_attribute8,l_miss_num, NULL,NULL,l_Nattr_tbl(8),n_attribute8)
,decode(n_attribute9,l_miss_num, NULL,NULL,l_Nattr_tbl(9),n_attribute9)
,decode(n_attribute10,l_miss_num, NULL,NULL,l_Nattr_tbl(10),n_attribute10)
,decode(attribute_category,l_miss_char, NULL,NULL,lot_attributes_rec.l_attribute_category,attribute_category)
,decode(attribute1,l_miss_char, NULL,NULL,l_invattr_tbl(1),attribute1)
,decode(attribute2,l_miss_char, NULL,NULL,l_invattr_tbl(2),attribute2)
,decode(attribute3,l_miss_char, NULL,NULL,l_invattr_tbl(3),attribute3)
,decode(attribute4,l_miss_char, NULL,NULL,l_invattr_tbl(4),attribute4)
,decode(attribute5,l_miss_char, NULL,NULL,l_invattr_tbl(5),attribute5)
,decode(attribute6,l_miss_char, NULL,NULL,l_invattr_tbl(6),attribute6)
,decode(attribute7,l_miss_char, NULL,NULL,l_invattr_tbl(7),attribute7)
,decode(attribute8,l_miss_char, NULL,NULL,l_invattr_tbl(8),attribute8)
,decode(attribute9,l_miss_char, NULL,NULL,l_invattr_tbl(9),attribute9)
,decode(attribute10,l_miss_char, NULL,NULL,l_invattr_tbl(10),attribute10)
,decode(attribute11,l_miss_char, NULL,NULL,l_invattr_tbl(11),attribute11)
,decode(attribute12,l_miss_char, NULL,NULL,l_invattr_tbl(12),attribute12)
,decode(attribute13,l_miss_char, NULL,NULL,l_invattr_tbl(13),attribute13)
,decode(attribute14,l_miss_char, NULL,NULL,l_invattr_tbl(14),attribute14)
,decode(attribute15,l_miss_char, NULL,NULL,l_invattr_tbl(15),attribute15)
INTO
lot_attributes_rec.l_mtli_txn_id
,lot_attributes_rec.l_description
,lot_attributes_rec.l_grade_code
,lot_attributes_rec.l_origination_date
,lot_attributes_rec.l_date_code
,lot_attributes_rec.l_change_date
,lot_attributes_rec.l_age
,lot_attributes_rec.l_retest_date
,lot_attributes_rec.l_maturity_date
,lot_attributes_rec.l_item_size
,lot_attributes_rec.l_color
,lot_attributes_rec.l_volume
,lot_attributes_rec.l_volume_uom
,lot_attributes_rec.l_place_of_origin
,lot_attributes_rec.l_best_by_date
,lot_attributes_rec.l_length
,lot_attributes_rec.l_length_uom
,lot_attributes_rec.l_recycled_content
,lot_attributes_rec.l_thickness
,lot_attributes_rec.l_thickness_uom
,lot_attributes_rec.l_width
,lot_attributes_rec.l_width_uom
,lot_attributes_rec.l_vendor_id
,lot_attributes_rec.l_vendor_name
,lot_attributes_rec.l_territory_code
,lot_attributes_rec.l_supplier_lot_number
,lot_attributes_rec.l_curl_wrinkle_fold
,lot_attributes_rec.l_lot_attribute_category
,l_Cattr_tbl(1)
,l_Cattr_tbl(2)
,l_Cattr_tbl(3)
,l_Cattr_tbl(4)
,l_Cattr_tbl(5)
,l_Cattr_tbl(6)
,l_Cattr_tbl(7)
,l_Cattr_tbl(8)
,l_Cattr_tbl(9)
,l_Cattr_tbl(10)
,l_Cattr_tbl(11)
,l_Cattr_tbl(12)
,l_Cattr_tbl(13)
,l_Cattr_tbl(14)
,l_Cattr_tbl(15)
,l_Cattr_tbl(16)
,l_Cattr_tbl(17)
,l_Cattr_tbl(18)
,l_Cattr_tbl(19)
,l_Cattr_tbl(20)
,l_Dattr_tbl(1)
,l_Dattr_tbl(2)
,l_Dattr_tbl(3)
,l_Dattr_tbl(4)
,l_Dattr_tbl(5)
,l_Dattr_tbl(6)
,l_Dattr_tbl(7)
,l_Dattr_tbl(8)
,l_Dattr_tbl(9)
,l_Dattr_tbl(10)
,l_Nattr_tbl(1)
,l_Nattr_tbl(2)
,l_Nattr_tbl(3)
,l_Nattr_tbl(4)
,l_Nattr_tbl(5)
,l_Nattr_tbl(6)
,l_Nattr_tbl(7)
,l_Nattr_tbl(8)
,l_Nattr_tbl(9)
,l_Nattr_tbl(10)
,lot_attributes_rec.l_attribute_category
,l_invattr_tbl(1)
,l_invattr_tbl(2)
,l_invattr_tbl(3)
,l_invattr_tbl(4)
,l_invattr_tbl(5)
,l_invattr_tbl(6)
,l_invattr_tbl(7)
,l_invattr_tbl(8)
,l_invattr_tbl(9)
,l_invattr_tbl(10)
,l_invattr_tbl(11)
,l_invattr_tbl(12)
,l_invattr_tbl(13)
,l_invattr_tbl(14)
,l_invattr_tbl(15)
FROM mtl_transaction_lots_interface
WHERE product_transaction_id=p_header_id
AND product_code='WSM'
AND lot_number=rlot.lot_number;
inv_lot_api_pub.Update_inv_lot(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_err_msg,
p_inventory_item_id => rlot.inventory_item_id,
p_organization_id => x_org_id,
p_lot_number => rlot.lot_number,
p_expiration_date => x_exp_date,
p_disable_flag => NULL,
p_attribute_category => lot_attributes_rec.l_attribute_category,
p_lot_attribute_category=> lot_attributes_rec.l_lot_attribute_category,
p_attributes_tbl => l_invattr_tbl,
p_c_attributes_tbl => l_Cattr_tbl,
p_n_attributes_tbl => l_Nattr_tbl,
p_d_attributes_tbl => l_Dattr_tbl,
p_grade_code => lot_attributes_rec.l_grade_code,
p_origination_date => lot_attributes_rec.l_origination_date,
p_date_code => lot_attributes_rec.l_date_code,
p_status_id => l_status_id,
p_change_date => lot_attributes_rec.l_change_date,
p_age => lot_attributes_rec.l_age,
p_retest_date => lot_attributes_rec.l_retest_date,
p_maturity_date => lot_attributes_rec.l_maturity_date,
p_item_size => lot_attributes_rec.l_item_size,
p_color => lot_attributes_rec.l_color,
p_volume => lot_attributes_rec.l_volume,
p_volume_uom => lot_attributes_rec.l_volume_uom,
p_place_of_origin => lot_attributes_rec.l_place_of_origin,
p_best_by_date => lot_attributes_rec.l_best_by_date,
p_length => lot_attributes_rec.l_length,
p_length_uom => lot_attributes_rec.l_length_uom,
p_recycled_content => lot_attributes_rec.l_recycled_content,
p_thickness => lot_attributes_rec.l_thickness,
p_thickness_uom => lot_attributes_rec.l_thickness_uom,
p_width => lot_attributes_rec.l_width,
p_width_uom => lot_attributes_rec.l_width_uom,
p_territory_code => lot_attributes_rec.l_territory_code,
p_supplier_lot_number => lot_attributes_rec.l_supplier_lot_number,
p_vendor_name => lot_attributes_rec.l_vendor_name,
p_source => 2);
l_invattr_tbl.delete;
l_Cattr_tbl.delete;
l_Nattr_tbl.delete;
l_Dattr_tbl.delete;
/*SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
**INTO X_Temp_Id
**FROM DUAL;
SELECT msi.primary_uom_code
INTO X_UOM
FROM
MTL_SYSTEM_ITEMS msi
WHERE msi.INVENTORY_ITEM_ID = X_Inventory_Item_Id
AND msi.ORGANIZATION_ID = X_Organization_Id;
select wp.transaction_account_id into x_dist_acct_id
from wsm_parameters wp
Where wp.organization_id = X_Organization_Id
and exists (select 1
From gl_code_combinations gl
Where gl.code_combination_id = wp.transaction_account_id
and gl.enabled_flag = 'Y'
and gl.summary_flag = 'N'
and NVL(gl.start_date_active, sysdate) <= sysdate
and NVL(gl.end_date_active, sysdate) >= sysdate
);
INSERT INTO mtl_material_transactions_temp
(last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
transaction_header_id,
inventory_item_id,
organization_id,
subinventory_code,
locator_id,
transaction_quantity,
primary_quantity,
transaction_uom,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_date,
acct_period_id,
reason_id,
transaction_reference,
process_flag,
posting_flag,
transaction_temp_id,
revision,
distribution_account_id,
source_code,
source_line_id
/*BA#IIIP*/
, lot_number
/*EA#IIIP*/
)
values
(X_DATE, /* LAST_UPDATE_DATE */
X_DATE, /* CREATION_DATE */
USER, /* LAST_UPDATED_BY */
USER, /* CREATED_BY */
LOGIN,
X_Header_Id1, /* TRANSACTION_HEADER_ID */
X_Inventory_Item_Id, /* INVENTORY_ITEM_ID */
X_Organization_Id, /* ORGANIZATION_ID */
X_Subinventory, /* SUBINVENTORY_CODE */
X_Locator_Id,
-1 * X_Quantity, /* TRANSACTION_QUANTITY */
-1 * X_Quantity, /* PRIMARY_QUANTITY */
X_Uom, /* UNIT_OF_MEASURE */
32, /* TRANSACTION_TYPE_ID */
1, /* TRANSACTION_ACTION_ID */
13, /* TRANSACTION_SOURCE_TYPE_ID */
X_transaction_date, /* TRANSACTION_DATE */
X_Acct_Period_Id, /* ACCT_PERIOD_ID */
X_Reason_Id, /* REASON_ID */
X_Reference, /* TRANSACTION_REFERENCE */
'Y', /* PROCESS_FLAG */
'Y', /* POSTING_FLAG */
-- abb X_temp_id, /* Transaction Temp Id */
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, /* abedajna, tuning */
X_revision,
X_Dist_Acct_Id, /* distribution_account_id */
'WSM', /* Source Code */
X_Source_Line_Id /* Transaction Id in WLSMI table */
/*BA#IIIP*/
,X_LOT_NUMBER
/*EA#IIIP*/
)
RETURNING transaction_temp_id INTO X_Temp_Id; -- abedajna, perf. Tuning
, showMessage => 'Sucessful insert to MMTT for Misc.Issue');
INSERT INTO MTL_TRANSACTION_LOTS_TEMP
(
transaction_temp_id,
last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
transaction_quantity,
primary_quantity,
lot_number,
DESCRIPTION, -- columns in insert query added for inserting lot attributes -- sisankar
GRADE_CODE,
ORIGINATION_DATE,
DATE_CODE,
CHANGE_DATE,
AGE,
RETEST_DATE,
MATURITY_DATE,
ITEM_SIZE,
COLOR,
VOLUME,
VOLUME_UOM,
PLACE_OF_ORIGIN,
BEST_BY_DATE,
LENGTH,
LENGTH_UOM,
RECYCLED_CONTENT,
THICKNESS,
THICKNESS_UOM,
WIDTH,
WIDTH_UOM,
VENDOR_ID,
VENDOR_NAME,
TERRITORY_CODE,
SUPPLIER_LOT_NUMBER,
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,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
select
X_temp_id,
X_date,
X_date,
USER,
USER,
LOGIN,
-1 * X_quantity,
-1 * X_quantity,
X_lot_number,
mln.description
,mln.grade_code
,mln.origination_date
,mln.date_code
,mln.change_date
,mln.age
,mln.retest_date
,mln.maturity_date
,mln.item_size
,mln.color
,mln.volume
,mln.volume_uom
,mln.place_of_origin
,mln.best_by_date
,mln.length
,mln.length_uom
,mln.recycled_content
,mln.thickness
,mln.thickness_uom
,mln.width
,mln.width_uom
,mln.vendor_id -- are vendor_id is missing in create_inv_lot
,mln.vendor_name
,mln.territory_code --MISSING in named record
,mln.supplier_lot_number --MISSING in named record
,mln.curl_wrinkle_fold --MISSING in named record
,mln.lot_attribute_category
,mln.c_attribute1
,mln.c_attribute2
,mln.c_attribute3
,mln.c_attribute4
,mln.c_attribute5
,mln.c_attribute6
,mln.c_attribute7
,mln.c_attribute8
,mln.c_attribute9
,mln.c_attribute10
,mln.c_attribute11
,mln.c_attribute12
,mln.c_attribute13
,mln.c_attribute14
,mln.c_attribute15
,mln.c_attribute16
,mln.c_attribute17
,mln.c_attribute18
,mln.c_attribute19
,mln.c_attribute20
,mln.d_attribute1
,mln.d_attribute2
,mln.d_attribute3
,mln.d_attribute4
,mln.d_attribute5
,mln.d_attribute6
,mln.d_attribute7
,mln.d_attribute8
,mln.d_attribute9
,mln.d_attribute10
,mln.n_attribute1
,mln.n_attribute2
,mln.n_attribute3
,mln.n_attribute4
,mln.n_attribute5
,mln.n_attribute6
,mln.n_attribute7
,mln.n_attribute8
,mln.n_attribute9
,mln.n_attribute10
,mln.attribute_category
,mln.attribute1
,mln.attribute2
,mln.attribute3
,mln.attribute4
,mln.attribute5
,mln.attribute6
,mln.attribute7
,mln.attribute8
,mln.attribute9
,mln.attribute10
,mln.attribute11
,mln.attribute12
,mln.attribute13
,mln.attribute14
,mln.attribute15
from MTL_LOT_NUMBERS mln
where mln.LOT_NUMBER = X_lot_number
and mln.ORGANIZATION_ID = X_Organization_Id
and mln.INVENTORY_ITEM_ID = X_Inventory_Item_Id;
, showMessage => 'Sucessful insert to MTLT for Misc.Issue');
/*SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
**INTO X_Temp_Id
**FROM DUAL;
SELECT msi.primary_uom_code
INTO x_uom
FROM mtl_system_items msi
WHERE msi.INVENTORY_ITEM_ID = X_Inventory_Item_Id
AND msi.ORGANIZATION_ID = X_Organization_Id;
select wp.transaction_account_id into x_dist_acct_id
from wsm_parameters wp
Where wp.organization_id = X_Organization_Id
and exists (select 1
From gl_code_combinations gl
Where gl.code_combination_id = wp.transaction_account_id
and gl.enabled_flag = 'Y'
and gl.summary_flag = 'N'
and NVL(gl.start_date_active, sysdate) <= sysdate
and NVL(gl.end_date_active, sysdate) >= sysdate
);
INSERT INTO mtl_material_transactions_temp
(last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
transaction_header_id,
inventory_item_id,
organization_id,
revision,
subinventory_code,
locator_id,
transaction_quantity,
primary_quantity,
transaction_uom,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_date,
acct_period_id,
reason_id,
transaction_reference,
process_flag,
posting_flag,
transaction_temp_id,
distribution_account_id,
source_code,
source_line_id
/*BA#IIIP*/
, LOT_NUMBER
/*EA#IIIP*/
)
VALUES
(X_date, /* LAST_UPDATE_DATE */
X_date, /* CREATION_DATE */
USER, /* LAST_UPDATED_BY */
USER, /* CREATED_BY */
LOGIN,
X_Header_Id1, /* TRANSACTION_HEADER_ID */
X_Inventory_Item_Id, /* INVENTORY_ITEM_ID */
X_Organization_Id, /* ORGANIZATION_ID */
X_Revision, /* REVISION */
X_Subinventory, /* SUBINVENTORY_CODE */
X_Locator_Id,
X_Quantity, /* TRANSACTION_QUANTITY */
X_Quantity, /* PRIMARY_QUANTITY */
X_Uom, /* UNIT_OF_MEASURE */
42, /* TRANSACTION_TYPE_ID */
27, /* TRANSACTION_ACTION_ID */
13, /* TRANSACTION_SOURCE_TYPE_ID */
X_transaction_date, /* TRANSACTION_DATE */
X_Acct_Period_Id, /* ACCT_PERIOD_ID */
X_Reason_Id, /* REASON_ID */
X_Reference, /* TRANSACTION_REFERENCE */
'Y', /* PROCESS_FLAG */
'Y', /* POSTING_FLAG */
-- abb X_temp_id, /* Transaction Temp Id */
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, /* abedajna, tuning */
X_Dist_Acct_Id, /* distribution account id */
'WSM', /* Source Code */
X_Source_Line_Id /* Transaction Id in WLSMI table */
/*BA#IIIP*/
,X_LOT_NUMBER
/*EA#IIIP*/
)
RETURNING transaction_temp_id INTO X_Temp_Id; -- abedajna, perf. Tuning
, showMessage => 'Sucessful insert to MMTT for Misc.Receipt');
INSERT INTO MTL_TRANSACTION_LOTS_TEMP
(
transaction_temp_id,
last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
transaction_quantity,
primary_quantity,
lot_number,
lot_expiration_date,
DESCRIPTION, -- added by sisankar for bug 4920235
GRADE_CODE,
ORIGINATION_DATE,
DATE_CODE,
CHANGE_DATE,
AGE,
RETEST_DATE,
MATURITY_DATE,
ITEM_SIZE,
COLOR,
VOLUME,
VOLUME_UOM,
PLACE_OF_ORIGIN,
BEST_BY_DATE,
LENGTH,
LENGTH_UOM,
RECYCLED_CONTENT,
THICKNESS,
THICKNESS_UOM,
WIDTH,
WIDTH_UOM,
VENDOR_ID,
VENDOR_NAME,
TERRITORY_CODE,
SUPPLIER_LOT_NUMBER,
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,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
values
(
X_temp_id,
X_date,
X_date,
USER,
USER,
LOGIN,
X_quantity,
X_quantity,
X_lot_number,
x_exp_date,
x_lot_attributes_rec.l_description, -- added by sisankar for bug 4920235
x_lot_attributes_rec.l_grade_code,
x_lot_attributes_rec.l_origination_date,
x_lot_attributes_rec.l_date_code,
x_lot_attributes_rec.l_change_date,
x_lot_attributes_rec.l_age,
x_lot_attributes_rec.l_retest_date,
x_lot_attributes_rec.l_maturity_date,
x_lot_attributes_rec.l_item_size,
x_lot_attributes_rec.l_color,
x_lot_attributes_rec.l_volume,
x_lot_attributes_rec.l_volume_uom,
x_lot_attributes_rec.l_place_of_origin,
x_lot_attributes_rec.l_best_by_date,
x_lot_attributes_rec.l_length,
x_lot_attributes_rec.l_length_uom,
x_lot_attributes_rec.l_recycled_content,
x_lot_attributes_rec.l_thickness,
x_lot_attributes_rec.l_thickness_uom,
x_lot_attributes_rec.l_width,
x_lot_attributes_rec.l_width_uom,
x_lot_attributes_rec.l_vendor_id,
x_lot_attributes_rec.l_vendor_name,
x_lot_attributes_rec.l_territory_code,
x_lot_attributes_rec.l_supplier_lot_number,
x_lot_attributes_rec.l_curl_wrinkle_fold,
x_lot_attributes_rec.l_lot_attribute_category,
x_Cattr_tbl(1),
x_Cattr_tbl(2),
x_Cattr_tbl(3),
x_Cattr_tbl(4),
x_Cattr_tbl(5),
x_Cattr_tbl(6),
x_Cattr_tbl(7),
x_Cattr_tbl(8),
x_Cattr_tbl(9),
x_Cattr_tbl(10),
x_Cattr_tbl(11),
x_Cattr_tbl(12),
x_Cattr_tbl(13),
x_Cattr_tbl(14),
x_Cattr_tbl(15),
x_Cattr_tbl(16),
x_Cattr_tbl(17),
x_Cattr_tbl(18),
x_Cattr_tbl(19),
x_Cattr_tbl(20),
x_Dattr_tbl(1),
x_Dattr_tbl(2),
x_Dattr_tbl(3),
x_Dattr_tbl(4),
x_Dattr_tbl(5),
x_Dattr_tbl(6),
x_Dattr_tbl(7),
x_Dattr_tbl(8),
x_Dattr_tbl(9),
x_Dattr_tbl(10),
x_Nattr_tbl(1),
x_Nattr_tbl(2),
x_Nattr_tbl(3),
x_Nattr_tbl(4),
x_Nattr_tbl(5),
x_Nattr_tbl(6),
x_Nattr_tbl(7),
x_Nattr_tbl(8),
x_Nattr_tbl(9),
x_Nattr_tbl(10),
x_lot_attributes_rec.l_attribute_category,
x_invattr_tbl(1),
x_invattr_tbl(2),
x_invattr_tbl(3),
x_invattr_tbl(4),
x_invattr_tbl(5),
x_invattr_tbl(6),
x_invattr_tbl(7),
x_invattr_tbl(8),
x_invattr_tbl(9),
x_invattr_tbl(10),
x_invattr_tbl(11),
x_invattr_tbl(12),
x_invattr_tbl(13),
x_invattr_tbl(14),
x_invattr_tbl(15)
);
, showMessage => 'Sucessful insert to MTLT for Misc.Receipt');
UPDATE wsm_lot_split_merges_interface
SET PROCESS_STATUS = COMPLETE
, ERROR_MESSAGE = NULL
WHERE PROCESS_STATUS =PENDING
AND GROUP_ID = p_group_id
AND header_id=p_header_id;
UPDATE wsm_lot_split_merges_interface
SET PROCESS_STATUS = COMPLETE
, ERROR_MESSAGE = NULL
WHERE PROCESS_STATUS = PENDING
AND header_ID = p_header_id;
INSERT INTO WSM_lot_split_merges
(transaction_id,
transaction_type_id,
organization_id,
wip_flaG,
split_flag ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
transaction_reference ,
reason_id ,
transaction_date,
last_update_login ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
request_id ,
program_application_id ,
program_id ,
program_update_date
)
SELECT
transaction_id,
transaction_type_id,
organization_id,
wip_flag,
split_flag ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
transaction_reference ,
reason_id ,
transaction_date,
last_update_login ,
attribute_CATEGORY ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
request_id ,
program_application_id ,
program_id ,
program_update_date
FROM wsm_lot_split_merges_interface
WHERE header_id = p_header_id
and process_status=COMPLETE;
INSERT INTO wsm_sm_starting_lots
(
transaction_id,
lot_number,
inventory_item_id,
organization_id,
revision,
quantity,
subinventory_code,
locator_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
SELECT
h.transaction_id,
s.lot_number,
s.inventory_item_id,
s.organization_id,
s.revision,
s.quantity,
s.subinventory_code,
s.locator_id,
sysdate,
user,
sysdate,
user,
login,
request,
progappl,
program,
sysdate,
s.attribute_category,
s.attribute1,
s.attribute2,
s.attribute3,
s.attribute4,
s.attribute5,
s.attribute6,
s.attribute7,
s.attribute8,
s.attribute9,
s.attribute10,
s.attribute11,
s.attribute12,
s.attribute13,
s.attribute14,
s.attribute15
FROM wsm_starting_lots_interface s,
wsm_lot_split_merges_interface h
WHERE h.header_id = p_header_id
and s.header_id = h.header_id;
INSERT INTO wsm_sm_resulting_lots
(
transaction_id,
lot_number,
inventory_item_id,
organization_id,
wip_entity_id,
quantity,
subinventory_code,
locator_id,
revision,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
SELECT
h.transaction_id,
r.lot_number,
r.inventory_item_id,
r.organization_id,
r.wip_entity_id,
r.quantity,
r.subinventory_code,
r.locator_id,
r.revision,
sysdate,
USER,
sysdate,
USER,
LOGIN,
REQUEST,
PROGAPPL,
PROGRAM,
sysdate,
r.attribute_category,
r.attribute1,
r.attribute2,
r.attribute3,
r.attribute4,
r.attribute5,
r.attribute6,
r.attribute7,
r.attribute8,
r.attribute9,
r.attribute10,
r.attribute11,
r.attribute12,
r.attribute13,
r.attribute14,
r.attribute15
FROM wsm_resulting_lots_interface r
, wsm_lot_split_merges_interface h
WHERE h.header_id = p_header_id
and r.header_id = h.header_id;
UPDATE wsm_lot_split_merges_interface
SET PROCESS_STATUS = ERROR
/*BA#IIIP*/
, error_message = p_message
, request_id = REQUEST
, program_id = PROGRAM
, program_application_id = PROGAPPL
/*EA#IIIP*/
WHERE PROCESS_STATUS <> ERROR
--AND GROUP_ID = p_group_id --bugfix 2449452: header_id is unique.
and header_id=p_header_id;
INSERT INTO WSM_INTERFACE_ERRORS(header_id,message ,
creation_date, last_update_date, last_updated_by, created_by)
SELECT transaction_id,p_message, sysdate, sysdate,
last_updated_by, created_by
FROM wsm_lot_split_merges_interface
WHERE PROCESS_STATUS = ERROR
AND GROUP_ID = p_group_id
and transaction_id=p_header_id;
SELECT distinct
wie.header_id
, wie.message_type
, wie.message
FROM wsm_interface_errors wie
WHERE wie.request_id = requestId
And wie.program_application_id = programApplnId
And wie.program_id = programId;