[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 2.Deactivated items were getting inserted |
| multiple times in mtl_pending_item_status ,every |
| time the deactiavtion program is being run . It |
| should get inserted only once during teh first |
| time |
| changed the check_delete_status to pick |
| up the status code for the LATEST effective date |
| (ie to pick up from the last inserted row) |
| In patchset-G the status_code was picked from the|
| first row (first effective date). |
| |
| |
| 05/16/2002 KKONADA bug fix#2368862.added a where condition to look |
| at inventory_item_status_code , in order NOT to |
| pick up inactive items |
| |
| 05/23/2002 KKONADA bug fix#2368862. Added a new procedure |
| GET_BOM_DELETE_STATUS_CODE and changed CHECK_ITEM |
| IN_CHILD_ORG. Code chnaged to look at the status |
| of the child org items |
| |
| 05/23/2002 SBHASKAR bugfix 2368862 contd.. |
| check attribute control for item status and |
| process accordingly. Refer bug for more details. |
| |
| bugfix 2214674 |
| check onhand for the config item before |
| deactivating. |
| |
| bugfix 2477125 |
| Remove from bom_ato_configurations so that de- |
| activated items are not used for matching. |
| |
| 10/01/2003 KSARKAR Changes to program for fp-J. |
| 11/19/2003 SBHASKAR bugfix 3275577. Added x_return_status parameter to|
| DEACTIVATE_ITEMS procedure and to the main prog. |
| |
| 11/19/2003 KSARKAR bugfix 3443251. Added exception to handle no data |
| found error when config not in BAC |
|
|
| 04/06/2004 KKONADA removed fullstop after BOM , bugfix#3554874
|
|
| 04/07/2004 KKONADA bugfix 3557190
| modified the query to use index and to delete from
| BCMO only when rows are deleted from BAC
*=========================================================================*/
/******************************************************************************
defining a record to hold configuration item details
config item id, config item name and msg to hold the reason
for being deactivated or not deactivated
*****************************************************************************/
-- bugfix2308063
TYPE r_cfg_item_details IS RECORD(
cfg_item_id mtl_system_items_kfv.inventory_item_id%type,
cfg_item_name mtl_system_items_kfv.concatenated_segments%type,
cfg_orgn_id number,
cfg_orgn_code mtl_parameters.organization_code%type,--5291392
msg VARCHAR2(200)
);
bom_delete_status_code.
bom_delete_status_code is the status which needs to be assigned to the item
when item becomes inactive
bugfix 2368862
**************************************************************************/
PROCEDURE GET_BOM_DELETE_STATUS_CODE
( p_org_id IN NUMBER,
p_delete_status_code OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
);
DEACTIVATE_ITEMS deactivates the items by inserting pending flag in
mtl_pending_item_status and deleting from bom_ato_configuration_items
***************************************************************************/
PROCEDURE DEACTIVATE_ITEMS(
p_table IN t_cfg_item_details,
p_org_id IN NUMBER,
p_status_code IN VARCHAR2,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_program_appl_id IN NUMBER,
p_program_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
);
l_del_status BOM_PARAMETERS.bom_delete_status_code%type;
l_selected_inv_item_id MTL_SYSTEM_ITEMS_KFV.inventory_item_id%type;
l_selected_inv_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%type;
SELECT FCR.request_id,
FCR.program_application_id,
FCR.concurrent_program_id
INTO l_request_id,
l_program_appl_id,
l_program_id
FROM fnd_concurrent_requests FCR,
fnd_concurrent_programs FCP
WHERE FCR.program_application_id = FCP.application_id
AND FCR.concurrent_program_id = FCP.concurrent_program_id
AND FCP.concurrent_program_name = 'BOMCCIPD'
-- AND FCR.phase_code = 'R'
-- new fix
AND FCR.argument1 = to_char (p_org_id)
AND nvl(FCR.argument3,'1') = nvl(to_char (p_config_id),'1')
AND nvl(FCR.argument5,'1') = nvl(to_char (p_model_id),'1')
AND nvl(FCR.argument6,'1') = nvl(to_char (p_optionitem_id),'1')
AND FCR.argument7 = to_char (p_num_of_days)
AND FCR.argument8 = to_char (p_user_id)
AND FCR.argument9 = to_char (p_login_id);
WriteToLog('Checking the delete status code..',3);
GET_BOM_DELETE_STATUS_CODE (l_org_id, l_del_status, l_return_status);
select msi.inventory_item_id,
msi.concatenated_segments,
msi.organization_id,
decode(mp.organization_id, mp.master_organization_id, 2, 1) order_level
from mtl_system_items_kfv msi,
mtl_parameters mp
where msi.base_item_id is NOT NULL
and msi.inventory_item_status_code <> l_del_status
and msi.organization_id = mp.organization_id
and mp.master_organization_id = l_Org_Id
ORDER BY 1, 4;
select msi.inventory_item_id,
msi.concatenated_segments,
msi.organization_id,
decode(mp.organization_id, mp.master_organization_id, 2, 1) order_level
from mtl_system_items_kfv msi,
mtl_parameters mp
where msi.inventory_item_status_code <> l_del_status
and msi.organization_id = mp.organization_id
and msi.inventory_item_id = l_Config_Id
and mp.master_organization_id = l_org_id
ORDER BY 1, 4;
select msi.inventory_item_id,
msi.concatenated_segments,
msi.organization_id,
decode(mp.organization_id, mp.master_organization_id, 2, 1) order_level
from mtl_system_items_kfv msi,
mtl_parameters mp
where msi.base_item_id = l_Model_Id
and msi.inventory_item_status_code <> l_del_status
and msi.organization_id = mp.organization_id
and mp.master_organization_id = l_org_id
ORDER BY 1, 4;
select msi.inventory_item_id,
msi.concatenated_segments,
msi.organization_id,
decode(mp.organization_id, mp.master_organization_id, 2, 1) order_level
from mtl_system_items_kfv msi,
mtl_parameters mp
where msi.base_item_id = l_Model_Id
and msi.inventory_item_status_code <> l_del_status
and msi.organization_id = mp.organization_id
and mp.master_organization_id = l_org_id
and msi.inventory_item_id in (
select bom.assembly_item_id
from bom_bill_of_materials bom,
bom_inventory_components b1,
bom_inventory_components b2
where b1.bill_sequence_id =b2.bill_sequence_id
and b1.component_item_id = l_Model_Id
and b2.component_item_id = l_OptionItem_Id
and b1.bill_sequence_id = bom.common_bill_sequence_id
and bom.organization_id = mp.organization_id )
ORDER BY 1, 4;
select inventory_item_id,
concatenated_segments
from mtl_system_items_kfv msi
where organization_id = l_org_id
and base_item_id is NOT NULL
and inventory_item_status_code <> l_del_status
and NOT EXISTS (
select 1
from bom_ato_configurations
where config_item_id = msi.inventory_item_id
and rownum = 1)
ORDER BY inventory_item_id;
select inventory_item_id,
concatenated_segments
from mtl_system_items_kfv
where organization_id = l_org_id
and base_item_id is NOT NULL
and inventory_item_status_code <> l_del_status
ORDER BY inventory_item_id;
select 1 into l_chk_cfg
from bom_ato_configurations
where config_item_id = l_config_id;
select inventory_item_id,
concatenated_segments
from mtl_system_items_kfv
where organization_id = l_org_id
and inventory_item_id = l_config_id
and inventory_item_status_code <> l_del_status
ORDER BY inventory_item_id;
select nvl(config_match,'Y'), concatenated_segments
into l_config_match, l_model_desc
from mtl_system_items_kfv
where inventory_item_id = l_Model_Id
and organization_id = l_org_id;
select inventory_item_id,
concatenated_segments
from mtl_system_items_kfv
where organization_id = l_org_id
and base_item_id = l_Model_Id
and inventory_item_status_code <> l_del_status
ORDER BY inventory_item_id;
select inventory_item_id,
concatenated_segments
from mtl_system_items_kfv
where organization_id = l_org_id
and base_item_id = l_Model_Id
and inventory_item_status_code <> l_del_status
and option_specific_sourced in (1,2)
and inventory_item_id in (
select bom.assembly_item_id
from bom_bill_of_materials bom,
bom_inventory_components b1,
bom_inventory_components b2
where b1.bill_sequence_id =b2.bill_sequence_id
and b1.component_item_id = l_Model_Id
and b2.component_item_id = l_OptionItem_Id
and b1.bill_sequence_id = bom.common_bill_sequence_id
and bom.organization_id = l_org_id
)
ORDER BY inventory_item_id;
select inventory_item_id,
concatenated_segments
from mtl_system_items_kfv
where organization_id = l_org_id
and base_item_id = l_Model_Id
and inventory_item_status_code <> l_del_status
and inventory_item_id in (
select bom.assembly_item_id
from bom_bill_of_materials bom,
bom_inventory_components b1,
bom_inventory_components b2
where b1.bill_sequence_id =b2.bill_sequence_id
and b1.component_item_id = l_Model_Id
and b2.component_item_id = l_OptionItem_Id
and b1.bill_sequence_id = bom.common_bill_sequence_id
and bom.organization_id = l_org_id)
ORDER BY inventory_item_id;
fetch mpconfig_cv into l_selected_inv_item_id,
l_selected_inv_item_name,
l_org_id,
l_order_level;
fetch chconfig_cv into l_selected_inv_item_id,
l_selected_inv_item_name;
WriteToLog( 'Processing Inventory Item Id '||l_selected_inv_item_id||' ('||
l_selected_inv_item_name||' )' ||' in organization '||l_org_id, 3);
if (l_prev_item_id = l_selected_inv_item_id and
x_attr_flag = FND_API.G_TRUE )
then
WriteToLog( 'Skipped processing since this item in another orgn failed validation.', 3);
l_selected_inv_item_id,
l_selected_inv_item_name,
l_org_id,
l_result_message);
l_prev_item_id := l_selected_inv_item_id;
l_prev_item_name := l_selected_inv_item_name;
failed_flag(l_selected_inv_item_id) := FND_API.G_FALSE; -- no errors for the selected item yet.
WriteToLog( 'Checking delete status ..',3);
CHECK_DELETE_STATUS( l_selected_inv_item_id,
l_org_id,
l_del_status,
x_return_status);
CHECK_COMMON_ROUTING( l_selected_inv_item_id,
l_org_id,
l_del_status,
x_return_status );
CHECK_COMMON_BOM( l_selected_inv_item_id,
l_org_id,
l_del_status,
x_return_status);
CHECK_ONHAND( l_selected_inv_item_id,
l_org_id,
x_return_status);
CHECK_OPEN_SUPPLY( l_selected_inv_item_id,
l_org_id,
x_return_status);
CHECK_OPEN_DEMAND( l_selected_inv_item_id,
l_org_id,
x_return_status);
CHECK_MATERIAL_TRANSACTION( l_selected_inv_item_id,
l_org_id,
p_num_of_days,
x_return_status);
CHECK_ACTIVE_PARENT_CONFIG( l_selected_inv_item_id,
l_org_id,
x_return_status);
l_item_rec.INVENTORY_ITEM_ID := l_selected_inv_item_id;
INV_ITEM_GRP.Update_Item
(
p_Item_rec => l_item_rec
, x_Item_rec => x_item_rec
, x_return_status => x_return_status
, x_Error_tbl => x_err_tbl
, p_Template_Id => p_template_id
);
WriteToLog ('INV_ITEM_GRP.Update_Item returned status '|| x_return_status ||'. Failed to apply template.');
-- If the attribute control is at Master level, should we insert the child orgs
-- also in mtl_pending_item_status or just master level insertion is fine ?
-- shailendra agarwal (skagarwa) from BOM team confirmed on Jul 31st that we dont have to
-- insert the child orgs if attr control is at master level
--
-- Printing attrib control and org id
WriteToLog( 'Attrib control '||x_attr_control||' Org '||p_org_id||' Master Org '||gMasterOrgn ,3);
failed_flag(l_selected_inv_item_id) := FND_API.G_TRUE;
l_selected_inv_item_id,
l_selected_inv_item_name,
l_org_id,
l_result_message);
WriteToLog( 'failed_flag ('||l_selected_inv_item_id||') = '||failed_flag(l_selected_inv_item_id) );
if ( loop_counter > 1 AND l_prev_item_id <> l_selected_inv_item_id) then
--
--populate the result
--
l_stat_num :=120;
-- populate temp table with selected item
i := 1;
tmp_item_arr(i).cfg_item_id := l_selected_inv_item_id;
tmp_item_arr(i).cfg_item_name := l_selected_inv_item_name;
elsif ( loop_counter > 1 ) AND ( l_prev_item_id = l_selected_inv_item_id) then
-- populate temp table with selected item
tmp_item_arr(i).cfg_item_id := l_selected_inv_item_id;
tmp_item_arr(i).cfg_item_name := l_selected_inv_item_name;
elsif ( loop_counter > 1 ) AND (l_prev_item_id <> l_selected_inv_item_id) then
-- perform register result from temp table for previous items
-- check if l_next_step_flag is TRUE
if tmp_item_arr.count > 0 then
for x1 in tmp_item_arr.FIRST..tmp_item_arr.LAST
loop
l_check_flag := tmp_item_arr(x1).msg;
tmp_item_arr.DELETE(x1);
tmp_item_arr(i).cfg_item_id := l_selected_inv_item_id;
tmp_item_arr(i).cfg_item_name := l_selected_inv_item_name;
l_selected_inv_item_id,
l_selected_inv_item_name,
l_org_id);
l_selected_inv_item_id,
l_selected_inv_item_name,
l_org_id,
l_result_message);
l_prev_item_id := l_selected_inv_item_id;
l_prev_item_name := l_selected_inv_item_name;
l_selected_inv_item_id,
l_selected_inv_item_name,
x_master_orgn_id);
tmp_item_arr.DELETE(x1);
WriteToLog('Calling DEACTIVATE_ITEMS to insert records into mtl_pending_status table. ', 5);
WriteToLog('Oracle Inventorys "Update item statuses with pending statuses" '||
'needs to be run to implement the pending status');
WriteToLog('Oracle Inventorys "Update item statuses with pending statuses" '||
'needs to be run to implement the pending status');
UPDATE bom_cto_order_lines
SET config_item_id = null
WHERE config_item_id = l_deactivated_items(l_index).cfg_item_id;
this inserts inactive status for all the selected items for deactivation
argumnents:
p_table : items meeting criteria for deactivation
p_org_id : organization where deactivation is run
p_status_code : bom_delete_status_code from bom_parameters for given org
p_user_id : default value
***************************************************************************/
PROCEDURE DEACTIVATE_ITEMS(
p_table IN t_cfg_item_details,
p_org_id IN NUMBER, -- mbsk: not actually needed.
p_status_code IN VARCHAR2,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_program_appl_id IN NUMBER,
p_program_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_index BINARY_INTEGER; --bugfix2308063
l_row_deleted number;
WriteToLog('inserting item id '||p_table(l_index).cfg_item_id ||
' in organization '||p_table(l_index).cfg_orgn_id, 5);
INSERT INTO mtl_pending_item_status
( inventory_item_id,
organization_id,
status_code,
effective_date,
pending_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES ( p_table(l_index).cfg_item_id,
p_table(l_index).cfg_orgn_id, --mbsk
p_status_code,
sysdate,
'Y',
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_program_appl_id,
p_program_id,
sysdate);
-- DO NOT delete if
-- - run from child orgn since match is org-independent.
-- DELETE only if
-- - run from master since we would have checked the statuses in the child.
-- - attribute control is set to MASTER.
--
if ( gAttrControl = 1 or p_table(l_index).cfg_orgn_id = gMasterOrgn )
then
WriteToLog ('Deleting from Bom_Ato_Configurations..',3);
DELETE FROM bom_ato_configurations
WHERE config_item_id = p_table(l_index).cfg_item_id;
l_row_deleted :=sql%rowcount;
IF l_row_deleted >0 THEN
BEGIN
SELECT group_reference_id
INTO l_grp_reference_id
FROM bom_cto_model_orgs
WHERE config_item_id = p_table(l_index).cfg_item_id
AND rownum = 1;
DELETE FROM bom_cto_model_orgs
WHERE group_reference_id = l_grp_reference_id;
WriteToLog ('Deleted item_id '||p_table(l_index).cfg_item_id ||' from Bom_Ato_Configurations..',3);
-- Update the BOM in all the orgn.. Disable the components
--
UPDATE bom_inventory_components bic
SET disable_date = greatest(least(nvl(bic.disable_date,sysdate)),
bic.effectivity_date),
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = sysdate
WHERE bill_sequence_id in (
select b.bill_sequence_id
from bom_bill_of_materials b, mtl_pending_item_status m
where m.status_code = p_status_code
and m.pending_flag = 'Y'
and m.request_id = p_request_id
-- and m.organization_id = b.organization_id --mbsk: for master level control
and m.inventory_item_id = b.assembly_item_id);
-- Update the ROUTING. Disable the operation sequences
--
UPDATE bom_operation_sequences bos
SET disable_date = greatest(least(nvl(bos.disable_date,sysdate)),
bos.effectivity_date),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_LOGIN = p_login_id,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = SYSDATE
WHERE routing_sequence_id in(
select b.routing_sequence_id
from bom_operational_routings b,mtl_pending_item_status m
where m.status_code = p_status_code
and m.pending_flag = 'Y'
and m.request_id = p_request_id
-- and b.organization_id = m.organization_id --mbsk: for mast level control
and b.assembly_item_id = m.inventory_item_id);
-- Update the BOM in the specific orgn. Disable the components
--
UPDATE bom_inventory_components bic
SET disable_date = greatest(least(nvl(bic.disable_date,sysdate)),
bic.effectivity_date),
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = sysdate
WHERE bill_sequence_id in (
select b.bill_sequence_id
from bom_bill_of_materials b, mtl_pending_item_status m
where m.status_code = p_status_code
and m.pending_flag = 'Y'
and m.request_id = p_request_id
and m.organization_id = b.organization_id
and m.inventory_item_id = b.assembly_item_id);
-- Update the ROUTING. Disable the operation sequences
--
UPDATE bom_operation_sequences bos
SET disable_date = greatest(least(nvl(bos.disable_date,sysdate)),
bos.effectivity_date),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_LOGIN = p_login_id,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = SYSDATE
WHERE routing_sequence_id in(
select b.routing_sequence_id
from bom_operational_routings b,mtl_pending_item_status m
where m.status_code = p_status_code
and m.pending_flag = 'Y'
and m.request_id = p_request_id
and b.organization_id = m.organization_id
and b.assembly_item_id = m.inventory_item_id);
procedure CHECK_DELETE_STATUS
this checks if the config item selected for deactivation has already been
deactivated.
Returns: true (FNDFND_API.G_TRUE), if already deactivated
false (FND_API.G_FALSE), if not already deactivated
arguments:
input:
p_inventory_item_id : config item being checked for deactivation
p_org_id : given org id
p_delete_status_cod : bom_parameters.bom_delete_status_code
x_return_status : return variable
************************************************************************/
PROCEDURE CHECK_DELETE_STATUS(
p_inventory_item_id IN NUMBER,
p_org_id IN NUMBER,
p_delete_status_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_status_code bom_parameters.bom_delete_status_code%type;
WriteToLog('Entering check_delete_status.. ', 5 );
SELECT status_code INTO l_status_code
FROM mtl_pending_item_status
WHERE organization_id = l_org_id --Bugfix 7011607
AND inventory_item_id = p_inventory_item_id
AND EFFECTIVE_DATE --bugfix2308063
= (SELECT max( EFFECTIVE_DATE) --bugfix2308063
FROM mtl_pending_item_status
WHERE organization_id = l_org_id --Bugfix 7011607
AND inventory_item_id = p_inventory_item_id);
WriteToLog('p_delete_status_code:'||p_delete_status_code, 5 );
IF l_status_code = p_delete_status_code THEN
x_return_status := FND_API.G_TRUE;
WriteToLog('Exiting check_delete_status with return status '||x_return_status, 5 );
WriteToLog('Came to no_data_found in CHECK_DELETE_STATUS', 5);
select inventory_item_status_code
into l_status_code
from mtl_system_items
where inventory_item_id = p_inventory_item_id
and organization_id = l_org_id; --Bugfix 7011607
WriteToLog('p_delete_status_code1:'||p_delete_status_code, 5 );
IF l_status_code = p_delete_status_code THEN
x_return_status := FND_API.G_TRUE;
WriteToLog('Exiting check_delete_status with return status '||x_return_status, 5 );
WriteToLog('## exiting CHECK_DELETE_STATUS with error ## ', 5 );
WriteToLog('error in CHECK_DELETE_STATUS'||sqlerrm, 5);
END CHECK_DELETE_STATUS;
select control_level
into x_attr_control
from mtl_item_attributes
where attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
select master_organization_id
into l_master_orgn_id
from mtl_parameters
where organization_id = p_org_id;
select master_organization_id
into l_master_orgn_id
from mtl_parameters
where organization_id = p_org_id;
this procedure put the items selected for deactivation in a pl/sql table
and also put the items which will not be deactivated in a pl/sql table
with a message saying why the item is not deactivated.
***************************************************************************/
PROCEDURE REGISTER_RESULT( p_table IN OUT NOCOPY t_cfg_item_details,
p_cfg_item_id IN NUMBER,
p_cfg_item_name IN VARCHAR2,
p_cfg_orgn_id IN NUMBER,
p_msg VARCHAR2 DEFAULT NULL)
IS
l_temp_index BINARY_INTEGER; --bugfix2308063
p_delete_status_code : bom_parameters.bom_delete_status_code for given org_id
******************************************************************************/
PROCEDURE CHECK_COMMON_ROUTING(
p_inventory_item_id IN NUMBER,
p_org_id IN NUMBER,
p_delete_status_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_rout_seq_id NUMBER;
SELECT routing_sequence_id,assembly_item_id
FROM bom_operational_routings
WHERE common_routing_sequence_id = l_rout_seq_id
and routing_sequence_id <> l_rout_seq_id;
SELECT routing_sequence_id INTO l_rout_seq_id
FROM bom_operational_routings
WHERE assembly_item_id = p_inventory_item_id
AND organization_id = p_org_id
AND alternate_routing_designator is null; --fix for bug2063209
WriteToLog('call to check_delete_status from check_common_routing', 5 );
CHECK_DELETE_STATUS( l_com_asmbly_itm_id,
p_org_id,
p_delete_status_code,
l_return_status);
WriteToLog('EXIT call to check_delete_status from check_common_routing', 5 );
p_delete_status_code : bom_parameters.bom_delete_status_code for given org_id
****************************************************************************************/
PROCEDURE CHECK_COMMON_BOM(
p_inventory_item_id IN NUMBER,
p_org_id IN NUMBER,
p_delete_status_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_del_status VARCHAR2(10);
SELECT bill_sequence_id,assembly_item_id,organization_id
FROM bom_bill_of_materials
WHERE common_bill_sequence_id = l_bill_sequence_id
AND bill_sequence_id <> l_bill_sequence_id;
SELECT bill_sequence_id INTO l_bill_sequence_id
FROM bom_bill_of_materials
WHERE assembly_item_id = p_inventory_item_id
AND organization_id = p_org_id
AND alternate_bom_designator IS NULL;
SELECT bom_delete_status_code INTO l_del_status
FROM bom_parameters
WHERE organization_id = l_com_org_id;
WriteToLog('call to check_delete_status from check_common_bom', 5 );
CHECK_DELETE_STATUS(
l_com_asmbly_itm_id,
l_com_org_id,
l_del_status,
l_return_status );
WriteToLog('finished call to check_delete_status from check_common_bom', 5 );
WriteToLog('call to check_delete_status from check_common_bom', 5 );
CHECK_DELETE_STATUS( l_com_asmbly_itm_id,
p_org_id ,
p_delete_status_code,
l_return_status);
WriteToLog('finished call to check_delete_status from check_common_bom', 5 );
select transaction_quantity into xdummy
from mtl_onhand_quantities
where inventory_item_id = p_inventory_item_id
and organization_id = p_org_id
and transaction_quantity > 0;
SELECT reservation_quantity
FROM mtl_reservations
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id
UNION
SELECT reservation_quantity
FROM mtl_reservations_interface
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT status_type
FROM wip_discrete_jobs
WHERE primary_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT status
FROM wip_flow_schedules
WHERE primary_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT primary_item_id
FROM wip_repetitive_items
WHERE primary_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT oel1.open_flag
FROM oe_order_lines_all oel1, oe_order_lines_all oel2
WHERE oel1.line_id IN
( SELECT bcso.line_id
FROM bom_cto_src_orgs bcso
WHERE bcso.config_item_id = p_inventory_item_id
AND bcso.organization_id = p_org_id)
AND oel1.OPEN_FLAG <> 'N'
AND oel1.ato_line_id = oel2.ato_line_id
AND oel2.item_type_code = 'CONFIG';*/
SELECT oel1.open_flag
FROM oe_order_lines_all oel1,
oe_order_lines_all oel2
WHERE oel1.line_id IN
( SELECT line_id line_id
FROM bom_cto_src_orgs_b bcso
WHERE group_reference_id IS NULL
AND bcso.config_item_id = p_inventory_item_id
AND bcso.organization_id = p_org_id
UNION ALL
SELECT bcso.line_id line_id
FROM bom_cto_src_orgs_b bcso,
bom_cto_model_orgs bcmo
WHERE bcso.group_reference_id IS NOT NULL
AND bcso.group_reference_id = bcmo.group_reference_id
AND bcso.config_item_id = p_inventory_item_id
AND bcso.organization_id = p_org_id
)
AND oel1.open_flag <> 'N'
AND oel1.ato_line_id = oel2.ato_line_id
AND oel2.item_type_code = 'CONFIG';
SELECT open_flag
FROM oe_order_lines_all
WHERE inventory_item_id = p_inventory_item_id
AND ship_from_org_id = p_org_id
AND open_flag <> 'N'
UNION
SELECT closed_flag
FROM oe_lines_iface_all
WHERE inventory_item_id = p_inventory_item_id --deamnd from or_interface tables (only std items can
AND ship_from_org_id = p_org_id --be ordered from third party tools ie exist in
AND closed_flag <> 'N'; --interface table
SELECT transaction_date
FROM mtl_material_transactions
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id
AND transaction_date > (SYSDATE-p_num_of_days);
l_del_status bom_parameters.bom_delete_status_code%type;
select assembly_item_id
from bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items msi
where bom.common_bill_sequence_id = bic.bill_sequence_id
and bic.component_item_id = p_inventory_item_id
and bom.organization_id = p_org_id
and bom.assembly_item_id = msi.inventory_item_id
and bom.organization_id = msi.organization_id
and msi.bom_item_type = 4 -- standard bom only
and msi.replenish_to_order_flag = 'Y'; -- ato items
SELECT bom_delete_status_code
INTO l_del_status
FROM bom_parameters
WHERE organization_id = p_org_id;
WriteToLog('BOM delete status code: '||l_del_status, 5 );
CHECK_DELETE_STATUS(
assembly_rec.assembly_item_id,
p_org_id,
l_del_status,
l_return_status );
procedure GET_BOM_DELETE_STATUS_CODE
This procedure takes in organization id and finds out the parameter bom_delete_status_code.
bom_delete_status_code is the status which needs to be assigned to the item when item becomes
inactive
Logic:
if bom_delete_status_code is not set for oragnization, return false
if bom_delete_status_code is set, return success and bom_delete_status_code
Arguments:
p_org_id IN NUMBER,
p_delete_status_code OUT VARCHAR2,
x_return_status OUT VARCHAR2
bugfix 2368862
*************************************************************************************************/
PROCEDURE GET_BOM_DELETE_STATUS_CODE
(
p_org_id IN NUMBER,
p_delete_status_code OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_del_status bom_parameters.bom_delete_status_code%type;
x_return_status := FND_API.G_FALSE;--default return value ,bom_delete_status_code is not set
WriteToLog('Entering get_bom_delete_status_code for org '||p_org_id, 5);
SELECT bom_delete_status_code
INTO l_del_status
FROM bom_parameters
WHERE organization_id = p_org_id;
p_delete_status_code := l_del_status;
WriteToLog('Exiting get_bom_delete_status_code.', 5);
WriteToLog('## exiting GET_BOM_DELETE_STATUS_CODE with error ##' );
WriteToLog('ERROR in GET_BOM_DELETE_STATUS_CODE'||sqlerrm);
END GET_BOM_DELETE_STATUS_CODE;
select organization_code
INTO p_organization_code
from mtl_parameters
where organization_id = p_organization_id;