[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
RETURN c_delorg_type.delete_org_type;
FUNCTION Get_delete_type(group_id in NUMBER)
RETURN NUMBER
IS
CURSOR del_type is
select delete_type
FROM bom_delete_groups
where delete_group_sequence_id = group_id;
RETURN c_del_type.delete_type;
END Get_delete_type;
select organization_hierarchy
FROM bom_delete_groups
where delete_group_sequence_id = group_id;
select delete_common_bill_flag
FROM bom_delete_groups
where delete_group_sequence_id = group_id;
RETURN c_common_flag.delete_common_bill_flag;
select organization_id
FROM bom_delete_groups
where delete_group_sequence_id = group_id;
SELECT oav.ORGANIZATION_NAME
FROM ORG_ACCESS_VIEW oav
WHERE
oav.RESPONSIBILITY_ID = FND_PROFILE.Value('RESP_ID')
AND oav.RESP_APPLICATION_ID = FND_PROFILE.Value('RESP_APPL_ID')
AND oav.ORGANIZATION_ID = org_id;
SELECT MP.organization_id
FROM MTL_PARAMETERS MP
WHERE MP.master_organization_id = (select m.master_organization_id
from mtl_parameters m
where m.organization_id = current_org_id)
and
exists ( select 'x'
from org_access_view
where responsibility_id =
to_number(FND_PROFILE.value('RESP_ID')) and
resp_application_id =
to_number(FND_PROFILE.value('RESP_APPL_ID'))
);
select bill_sequence_id
FROM bom_bill_of_materials
where assembly_item_id = assembly_id and
organization_id = org_id and
nvl(alternate_bom_designator,'none') = nvl(alternate_bom,'none');
select routing_sequence_id
FROM bom_operational_routings
where assembly_item_id = assembly_id and
organization_id = org_id and
nvl(alternate_routing_designator,'none') = nvl(alternate_desg,'none');
select component_sequence_id
FROM bom_inventory_components
where bill_sequence_id = bill_seq and
component_item_id = component_id and
operation_seq_num = oper_seq_num and
effectivity_date = effective_date ;
select operation_sequence_id
FROM bom_operation_sequences
where routing_sequence_id = rtg_seq and
operation_seq_num = oper_seq_num and
-- effectivity_date = effective_date and -- Changed for bug 2647027
trunc(effectivity_date) = trunc(effective_date) and -- Changed back for bug 3738241
department_id = (select department_id
from bom_departments
where department_code = dept_code and
organization_id = org_id);
select department_code
FROM bom_departments
where department_code = dept_code and
organization_id = org_id;
select description
FROM mtl_system_items
where inventory_item_id = assembly_id and
organization_id = org_id;
select concatenated_segments
FROM mtl_system_items_vl
where inventory_item_id = assembly_id and
organization_id = org_id;
SELECT inventory_item_id
FROM mtl_system_items
where inventory_item_id = assembly_id and
organization_id = current_org;
PROCEDURE process_delete_entities(delete_type in NUMBER,
group_id in NUMBER,
original_org in NUMBER,
org_list in inv_orghierarchy_pvt.orgid_tbl_type)
IS
l_index number := 0;
del_ent_type bom_delete_entities.delete_entity_type%type;
inv_item_id bom_delete_entities.inventory_item_id%type;
alt_desg bom_delete_entities.alternate_designator%type;
item_concat_seg bom_delete_entities.item_concat_segments%type;
last_upd_by bom_delete_entities.last_updated_by%type;
crtd_by bom_delete_entities.created_by%type;
current_del_seq bom_delete_entities.delete_entity_sequence_id%type;
SELECT bill_sequence_id,delete_entity_type,
inventory_item_id,alternate_designator,
item_concat_segments,last_updated_by,
created_by,delete_entity_sequence_id
FROM bom_delete_entities
WHERE delete_group_sequence_id = group_id
AND organization_id = original_org;
SELECT common_bill_sequence_id,assembly_item_id,
alternate_bom_designator,bill_sequence_id
FROM bom_bill_of_materials
WHERE common_bill_sequence_id in (
select BOM2.bill_sequence_id
from bom_bill_of_materials BOM2
where BOM2.assembly_item_id = inv_item_id) and
bill_sequence_id <> common_bill_sequence_id and
organization_id = current_org;
if (delete_type in (2,6,7)) then -- Bill,Bill/Rtg,Item/Bill/Rtg
FOR c_bom_entities in bom_entities
LOOP --- ENTITIES LOOP
current_bill_seq := c_bom_entities.bill_sequence_id;
del_ent_type := c_bom_entities.delete_entity_type;
last_upd_by := c_bom_entities.last_updated_by;
current_del_seq := c_bom_entities.delete_entity_sequence_id;
select common_bill_sequence_id
into common_bill
from bom_bill_of_materials
where bill_sequence_id = current_bill_seq;
select 'x'
into temp
from bom_delete_entities
where bill_sequence_id = c_common_bills.bill_sequence_id and
delete_group_sequence_id = group_id and
organization_id = current_org;
insert into bom_delete_entities
(DELETE_ENTITY_SEQUENCE_ID,
DELETE_GROUP_SEQUENCE_ID,
DELETE_ENTITY_TYPE,
BILL_SEQUENCE_ID,
ROUTING_SEQUENCE_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ALTERNATE_DESIGNATOR,
ITEM_DESCRIPTION,
ITEM_CONCAT_SEGMENTS,
DELETE_STATUS_TYPE,
DELETE_DATE,
PRIOR_PROCESS_FLAG,
PRIOR_COMMIT_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
select bom_delete_entities_s.nextval,
group_id,
del_ent_type,
get_bill_seq(c_common_bills.assembly_item_id,
current_org,
c_common_bills.alternate_bom_designator),
null,
inv_item_id,
current_org,
alt_desg,
get_item_descr(c_common_bills.assembly_item_id,
current_org),
get_concat_segs(c_common_bills.assembly_item_id,
current_org),
1,
null,
1,
1,
sysdate,
last_upd_by,
sysdate,
crtd_by,
null,
null,
null,
null,
null
from bom_bill_of_materials
where organization_id = current_org and
common_bill_sequence_id = current_bill_seq and
bill_sequence_id <> current_bill_seq and
common_organization_id = original_org and
common_assembly_item_id = inv_item_id;
update bom_delete_entities
set delete_status_type = 5
where delete_group_sequence_id = group_id and
delete_entity_sequence_id = current_del_seq and
delete_status_type <> 4;
END process_delete_entities;
delete from bom_delete_entities
where delete_group_sequence_id = group_id and
delete_status_type = 5;
update bom_delete_entities
set delete_status_type = 1
where delete_group_sequence_id = group_id and
delete_status_type = 5;
delete_type in NUMBER,
group_id in NUMBER,
original_org in NUMBER,
common_flag in NUMBER,
org_list in inv_orghierarchy_pvt.orgid_tbl_type)
IS
l_index number := 0;
current_rtg_seq bom_delete_entities.routing_sequence_id%type;
del_status bom_delete_entities.delete_status_type%type;
del_ent_type bom_delete_entities.delete_entity_type%type;
inv_item_id bom_delete_entities.inventory_item_id%type;
alt_desg bom_delete_entities.alternate_designator%type;
item_concat_seg bom_delete_entities.item_concat_segments%type;
last_upd_by bom_delete_entities.last_updated_by%type;
crtd_by bom_delete_entities.created_by%type;
current_del_seq bom_delete_entities.delete_entity_sequence_id%type;
new_del_seq bom_delete_entities.delete_entity_sequence_id%type;
new_comp_seq bom_delete_sub_entities.component_sequence_id%type := NULL;
new_operation_seq bom_delete_sub_entities.operation_sequence_id%type;
new_item_id bom_delete_entities.inventory_item_id%type;
SELECT bill_sequence_id,delete_entity_type,delete_status_type,
inventory_item_id,alternate_designator,routing_sequence_id,
item_concat_segments,last_updated_by,
created_by,delete_entity_sequence_id
FROM bom_delete_entities
WHERE
delete_group_sequence_id = group_id
AND organization_id = original_org
AND request_id = FND_GLOBAL.CONC_REQUEST_ID;
SELECT OPERATION_SEQ_NUM,EFFECTIVITY_DATE,
COMPONENT_ITEM_ID,COMPONENT_CONCAT_SEGMENTS,
ITEM_NUM,DISABLE_DATE,OPERATION_DEPARTMENT_CODE
FROM BOM_DELETE_SUB_ENTITIES
where DELETE_ENTITY_SEQUENCE_ID = current_del_seq;
select component_sequence_id
FROM bom_inventory_components
where bill_sequence_id = new_bill_seq and
component_item_id = component_id and
operation_seq_num = oper_seq_num and
trunc(effectivity_date) = trunc(effective_date) ;
if (common_flag = 2) then -- Delete original and common bills
/*
Process all Orgs except for original Bill Org as it is already in
Delete entities Table
*/
FOR c_bom_entities in bom_entities
LOOP --- ENTITIES LOOP
current_bill_seq := c_bom_entities.bill_sequence_id;
del_ent_type := c_bom_entities.delete_entity_type;
del_status := c_bom_entities.delete_status_type;
last_upd_by := c_bom_entities.last_updated_by;
current_del_seq := c_bom_entities.delete_entity_sequence_id;
select common_bill_sequence_id
into common_bill
from bom_bill_of_materials
where bill_sequence_id = current_bill_seq;
if (((delete_type = 2)or (delete_type = 6) or (delete_type = 7) or
(delete_type = 4))
and (new_bill_seq is not NULL)
and (current_bill_seq is not null)) --added by arudresh for bug 3735729
/* The clause (current_bill_seq is not null) is added as the same alt_desg is
* used to identify both bills and routings. This will avoid the case when
* bill as well as routing of the same alternate got deleted even if only one
* was specified.*/
then
begin
select 'x'
into temp
from bom_delete_entities
where bill_sequence_id= new_bill_seq and
inventory_item_id = new_item_id and
delete_group_sequence_id = group_id and
organization_id = current_org;
select bom_delete_entities_s.nextval
into new_del_seq
from dual;
insert into bom_delete_entities
(DELETE_ENTITY_SEQUENCE_ID,
DELETE_GROUP_SEQUENCE_ID,
DELETE_ENTITY_TYPE,
BILL_SEQUENCE_ID,
ROUTING_SEQUENCE_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ALTERNATE_DESIGNATOR,
ITEM_DESCRIPTION,
ITEM_CONCAT_SEGMENTS,
DELETE_STATUS_TYPE,
DELETE_DATE,
PRIOR_PROCESS_FLAG,
PRIOR_COMMIT_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( new_del_seq,
group_id,
decode(delete_type,4,4,2),
new_bill_seq,
null,
inv_item_id,
current_org,
alt_desg,
get_item_descr(new_item_id,
current_org),
get_concat_segs(new_item_id,
current_org),
decode(delete_type,4,null,
5,null,1),
null,
1,
1,
sysdate,
last_upd_by,
sysdate,
crtd_by,
null,
null,
null,
null,
null);
end if; -- Delete Type 2,4,6,7
if (((delete_type = 3)or (delete_type = 6) or (delete_type = 7) or
(delete_type = 5))
and (new_rtg_seq is not NULL)
and (current_rtg_seq is not null)) --added by arudresh,bug 3735729
then
begin
select 'x'
into temp
from bom_delete_entities
where routing_sequence_id= new_rtg_seq and
inventory_item_id = new_item_id and
delete_group_sequence_id = group_id and
organization_id = current_org;
select bom_delete_entities_s.nextval
into new_del_seq
from dual;
insert into bom_delete_entities
(DELETE_ENTITY_SEQUENCE_ID,
DELETE_GROUP_SEQUENCE_ID,
DELETE_ENTITY_TYPE,
BILL_SEQUENCE_ID,
ROUTING_SEQUENCE_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ALTERNATE_DESIGNATOR,
ITEM_DESCRIPTION,
ITEM_CONCAT_SEGMENTS,
DELETE_STATUS_TYPE,
DELETE_DATE,
PRIOR_PROCESS_FLAG,
PRIOR_COMMIT_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( new_del_seq,
group_id,
decode(delete_type,5,5,3),
null,
new_rtg_seq,
inv_item_id,
current_org,
alt_desg,
get_item_descr(new_item_id,
current_org),
get_concat_segs(new_item_id,
current_org),
decode(delete_type,4,null,
5,null,1),
null,
1,
1,
sysdate,
last_upd_by,
sysdate,
crtd_by,
null,
null,
null,
null,
null);
end if; --- Delete Type 3,5,6,7
if (((delete_type = 1)or (delete_type=7))
and (new_item_id is not NULL))
then
begin
select 'x'
into temp
from bom_delete_entities
where inventory_item_id = new_item_id and
delete_group_sequence_id = group_id and
delete_entity_type = 1 and
organization_id = current_org;
select bom_delete_entities_s.nextval
into new_del_seq
from dual;
insert into bom_delete_entities
(DELETE_ENTITY_SEQUENCE_ID,
DELETE_GROUP_SEQUENCE_ID,
DELETE_ENTITY_TYPE,
BILL_SEQUENCE_ID,
ROUTING_SEQUENCE_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ALTERNATE_DESIGNATOR,
ITEM_DESCRIPTION,
ITEM_CONCAT_SEGMENTS,
DELETE_STATUS_TYPE,
DELETE_DATE,
PRIOR_PROCESS_FLAG,
PRIOR_COMMIT_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( new_del_seq,
group_id,
1,
null,
null,
inv_item_id,
current_org,
alt_desg,
get_item_descr(new_item_id,
current_org),
get_concat_segs(new_item_id,
current_org),
decode(delete_type,4,null,
5,null,1),
null,
1,
1,
sysdate,
last_upd_by,
sysdate,
crtd_by,
null,
null,
null,
null,
null);
end if; -- Delete Type 1,7
IF delete_type in (4,5) then
FOR c_sub_entity in sub_entity
LOOP --- SUB ENTITY LOOP
component_id := c_sub_entity.component_item_id;
if ((delete_type=5) and new_operation_seq is not NULL) then
begin
select 'x'
into temp
from bom_delete_sub_entities
where delete_entity_sequence_id = new_del_seq and
((component_sequence_id = new_comp_seq) or
(operation_sequence_id = new_operation_seq));
insert into bom_delete_sub_entities
(DELETE_ENTITY_SEQUENCE_ID,
COMPONENT_SEQUENCE_ID,
OPERATION_SEQUENCE_ID,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
COMPONENT_ITEM_ID,
COMPONENT_CONCAT_SEGMENTS,
ITEM_NUM,
DISABLE_DATE,
DESCRIPTION,
OPERATION_DEPARTMENT_CODE,
DELETE_STATUS_TYPE,
DELETE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER)
values(
new_del_seq,
decode(delete_type,4,new_comp_seq,5,null),
decode(delete_type,5,new_operation_seq,4,null),
c_sub_entity.operation_seq_num,
c_sub_entity.effectivity_date,
c_sub_entity.component_item_id,
get_concat_segs(c_sub_entity.component_item_id,
current_org),
c_sub_entity.item_num,
c_sub_entity.disable_date,
get_item_descr(c_sub_entity.component_item_id,
current_org),
get_dept_code(c_sub_entity.operation_department_code,
current_org),
1,
null,
sysdate,
last_upd_by,
sysdate,
crtd_by,
null,
null,
null,
null,
null,
null,
null);
END IF; -- Delete Type 5
IF delete_type = 4 then
FOR c_compseq in compseq
LOOP
new_comp_seq := c_compseq.component_sequence_id;
select 'x'
into temp
from bom_delete_sub_entities
where delete_entity_sequence_id = new_del_seq and
((component_sequence_id = new_comp_seq) or
(operation_sequence_id = new_operation_seq));
insert into bom_delete_sub_entities
(DELETE_ENTITY_SEQUENCE_ID,
COMPONENT_SEQUENCE_ID,
OPERATION_SEQUENCE_ID,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
COMPONENT_ITEM_ID,
COMPONENT_CONCAT_SEGMENTS,
ITEM_NUM,
DISABLE_DATE,
DESCRIPTION,
OPERATION_DEPARTMENT_CODE,
DELETE_STATUS_TYPE,
DELETE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
FROM_END_ITEM_UNIT_NUMBER,
TO_END_ITEM_UNIT_NUMBER)
values(
new_del_seq,
decode(delete_type,4,new_comp_seq,5,null),
decode(delete_type,5,new_operation_seq,4,null),
c_sub_entity.operation_seq_num,
c_sub_entity.effectivity_date,
c_sub_entity.component_item_id,
get_concat_segs(c_sub_entity.component_item_id,
current_org),
c_sub_entity.item_num,
c_sub_entity.disable_date,
get_item_descr(c_sub_entity.component_item_id,
current_org),
get_dept_code(c_sub_entity.operation_department_code,
current_org),
1,
null,
sysdate,
last_upd_by,
sysdate,
crtd_by,
null,
null,
null,
null,
null,
null,
null);
END IF; -- Delete Type 4 or 5
END IF; -- Delete Type 4 or 5
/* ------------------------ Insert_common_bill_details ------------------------
NAME
insert_common_bills
Entities Table
DESCRIPTION
Insert the common bill details in Bom_delete_entities
MODIFIES
BOM_DELETE_ENTITIES Table
---------------------------------------------------------------------------*/
PROCEDURE insert_common_bills(group_id IN NUMBER,
delete_type IN NUMBER)
IS
delete_org_type bom_delete_groups.delete_org_type%type;
delete_org_hrchy bom_delete_groups.organization_hierarchy%type;
delete_common_flag bom_delete_groups.delete_common_bill_flag%type;
current_org_id bom_delete_groups.organization_id%type;
delete_org_type := get_delorg_type(group_id);
delete_org_hrchy := get_delorg_hrchy(group_id);
delete_common_flag := get_common_flag(group_id);
get_delorg_list(delete_org_type,
delete_org_hrchy,
current_org_id,
current_org_name,
del_org_list);
process_delete_entities(delete_type,
group_id,
current_org_id,
del_org_list);
END insert_common_bills;
/* ------------------------ Insert_original_bills ------------------------
NAME
insert_original_bills in Delete Entities Table
and Component,Operation Info in Sub Entities Table
DESCRIPTION
Insert the original bill details in Bom_delete_entities
Insert Component and Operation Info in Delete Sub Entities Table
MODIFIES
BOM_DELETE_ENTITIES Table
BOM_DELETE_SUB_ENTITIES Table
---------------------------------------------------------------------------*/
PROCEDURE insert_original_bills(group_id IN NUMBER,
delete_type IN NUMBER)
IS
delete_org_type bom_delete_groups.delete_org_type%type;
delete_org_hrchy bom_delete_groups.organization_hierarchy%type;
delete_common_flag bom_delete_groups.delete_common_bill_flag%type;
current_org_id bom_delete_groups.organization_id%type;
delete_org_type := get_delorg_type(group_id);
delete_org_hrchy := get_delorg_hrchy(group_id);
delete_common_flag := get_common_flag(group_id);
delete_common_flag);
get_delorg_list(delete_org_type,
delete_org_hrchy,
current_org_id,
current_org_name,
del_org_list);
process_original_sub_entities(delete_type,
group_id,
current_org_id,
delete_common_flag,
del_org_list);
END insert_original_bills;