[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
SELECT bill_sequence_id
FROM bom_structures_b
WHERE assembly_item_id = p_parent_id
AND organization_id = p_org_id
AND nvl(alternate_bom_designator,'Primary') = nvl(p_str_name,'Primary');
SELECT bill_sequence_id
INTO l_bill_seq_id
FROM bom_structures_b
WHERE assembly_item_id = p_parent_id
AND organization_id = p_org_id
AND nvl(alternate_bom_designator,'Primary') = nvl(p_str_name,'Primary');
SELECT *
FROM bom_ref_desgs_interface
WHERE batch_id = p_batch_id
AND (component_sequence_id = p_comp_seq_id
OR (component_item_id = p_comp_id
AND organization_id = p_org_id
AND assembly_item_id = p_parent_id
AND nvl(effectivity_date,sysdate) = nvl(p_eff_date,sysdate)
AND nvl(operation_seq_num,1) = nvl(p_op_seq_num,1)
)
)
ORDER BY component_reference_designator;
SELECT *
FROM bom_reference_designators
WHERE component_sequence_id = p_comp_seq_id
ORDER BY component_reference_designator;
l_delete BOOLEAN;
INSERT INTO bom_ref_desgs_interface
(
COMPONENT_REFERENCE_DESIGNATOR,
REF_DESIGNATOR_COMMENT,
CHANGE_NOTICE,
COMPONENT_SEQUENCE_ID,
batch_id,
transaction_type,
process_flag,
component_item_id,
assembly_item_id,
organization_id
)
VALUES
(
l_pimdh_refds(i).component_reference_designator,
l_pimdh_refds(i).REF_DESIGNATOR_COMMENT,
l_pimdh_refds(i).CHANGE_NOTICE,
l_pimdh_refds(i).component_sequence_id,
p_batch_id,
'DELETE',
1,
p_comp_id,
p_parent_id,
p_org_id
);
l_delete := true;
l_delete := false;
IF l_delete THEN
INSERT INTO bom_ref_desgs_interface
(
COMPONENT_REFERENCE_DESIGNATOR,
REF_DESIGNATOR_COMMENT,
CHANGE_NOTICE,
COMPONENT_SEQUENCE_ID,
batch_id,
transaction_type,
process_flag,
component_item_id,
assembly_item_id,
organization_id
)
VALUES
(
l_pimdh_refds(i).component_reference_designator,
l_pimdh_refds(i).REF_DESIGNATOR_COMMENT,
l_pimdh_refds(i).CHANGE_NOTICE,
l_pimdh_refds(i).component_sequence_id,
p_batch_id,
'DELETE',
1,
p_comp_id,
p_parent_id,
p_org_id
);
SELECT item_number
INTO l_temp
FROM bom_bill_of_mtls_interface BBMI,mtl_system_items_vl MSIVL,mtl_parameters MP,bom_structures_b BSB
WHERE BBMI.batch_id = p_batch_id
AND BSB.bill_sequence_id = p_bill_seq_id
AND (BBMI.process_flag = 1 OR BBMI.process_flag = 5 )
AND (( BBMI.request_id IS NOT NULL AND BBMI.request_id = p_request_id ) OR (BBMI.bundle_id IS NOT NULL AND BBMI.bundle_id = p_bundle_id))
AND ( BBMI.bill_sequence_id = p_bill_seq_id OR
( (BBMI.assembly_item_id = p_item_id OR BBMI.item_number = MSIVL.concatenated_segments OR BBMI.source_system_reference = MSIVL.concatenated_segments )
AND (BBMI.organization_id = p_org_id OR BBMI.organization_code = MP.organization_code)
AND NVL(BBMI.alternate_bom_designator,'Primary') = NVL(p_str_name,'Primary')
)
)
AND MSIVL.inventory_item_id = p_item_id
AND MSIVl.organization_id = p_org_id
AND MP.organization_id = p_org_id;
Procedure update_transaction_ids
(
p_batch_id IN NUMBER
)
is
BEGIN
update
BOM_BILL_OF_MTLS_INTERFACE
set
transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
where
transaction_id is null
and batch_id = p_batch_id
and process_flag = 1;
update
BOM_INVENTORY_COMPS_INTERFACE
set
transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
where
transaction_id is null
and batch_id = p_batch_id
and process_flag = 1;
update
BOM_SUB_COMPS_INTERFACE
set
transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
where
transaction_id is null
and batch_id = p_batch_id
and process_flag = 1;
update
BOM_REF_DESGS_INTERFACE
set
transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
where
transaction_id is null
and batch_id = p_batch_id
and process_flag = 1;
END update_transaction_ids;
SELECT 'Exist'
INTO l_dummy
FROM EGO_IMPORT_BATCHES_B
WHERE batch_id = p_batch_id
AND batch_type = 'BOM_STRUCTURE';
UPDATE bom_bill_of_mtls_interface
SET alternate_bom_designator = pg_batch_options.structure_name
WHERE batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 5);
UPDATE bom_inventory_comps_interface
SET alternate_bom_designator = pg_batch_options.structure_name
WHERE batch_id = p_batch_id
AND ( process_flag = 1 OR process_flag = 5);
UPDATE bom_bill_of_mtls_interface
SET structure_type_id = pg_batch_options.structure_type_id
WHERE batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 5)
AND structure_type_id IS NULL;
UPDATE bom_bill_of_mtls_interface
SET structure_type_name = (SELECT STV1.structure_type_name
FROM bom_structure_types_vl STV1 where
STV1.structure_type_id = pg_batch_options.structure_type_id)
WHERE batch_id = p_batch_id
AND structure_type_name IS NULL
AND (process_flag = 1 OR process_flag = 5)
AND exists (select STV2.structure_type_name from bom_structure_types_vl STV2
WHERE STV2.structure_type_id = pg_batch_options.structure_type_id);
UPDATE
bom_bill_of_mtls_interface
SET
EFFECTIVITY_CONTROL = 1
WHERE
batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 5)
AND EFFECTIVITY_CONTROL is NULL;
UPDATE
bom_inventory_comps_interface
SET
EFFECTIVITY_DATE = pg_batch_options.EFFECTIVITY_DATE
WHERE
EFFECTIVITY_DATE IS NULL
AND BATCH_ID = P_BATCH_ID
AND EFFECTIVITY_DATE IS NULL
AND (PROCESS_FLAG = 1 OR PROCESS_FLAG =5); --Check New effectivity date
UPDATE
bom_bill_of_mtls_interface
SET
EFFECTIVITY_CONTROL = 2
WHERE
batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 5)
AND EFFECTIVITY_CONTROL is NULL;
UPDATE
bom_inventory_comps_interface
SET
FROM_END_ITEM_UNIT_NUMBER = pg_batch_options.FROM_END_ITEM_UNIT_NUMBER
WHERE
FROM_END_ITEM_UNIT_NUMBER IS NULL
AND BATCH_ID = P_BATCH_ID
AND FROM_END_ITEM_UNIT_NUMBER IS NULL
AND (PROCESS_FLAG = 1 OR PROCESS_FLAG =5); --Check New effectivity date
SELECT
b.SOURCE_SYSTEM_ID,
b.BATCH_TYPE,
b.ASSIGNEE,
b.BATCH_STATUS,
o.MATCH_ON_DATA_LOAD,
o.IMPORT_ON_DATA_LOAD,
nvl(o.IMPORT_XREF_ONLY,'N'),
o.STRUCTURE_TYPE_ID,
o.STRUCTURE_NAME,
o.STRUCTURE_EFFECTIVITY_TYPE,
o.EFFECTIVITY_DATE,
o.FROM_END_ITEM_UNIT_NUMBER,
o.STRUCTURE_CONTENT,
o.CHANGE_NOTICE,
NVL(o.CHANGE_ORDER_CREATION, 'I'), --I, ignore change,
DECODE(NVL(b.SOURCE_SYSTEM_ID,0), G_PDH_SRCSYS_ID, 'Y', 'N'),
o.add_all_to_change_flag
FROM
EGO_IMPORT_BATCHES_B b, ego_import_option_sets o
WHERE
b.BATCH_ID = o.BATCH_ID
AND b.BATCH_ID = p_batch_id;
SELECT
G_PDH_SRCSYS_ID,
'BOM_STRUCTURE',
null,
'A',
null,
'Y',
'N',
103,
'PIM_PBOM_S',
1,
null,
null,
'C',
null,
'I', --I, ignore change,
'Y',
null
INTO
pg_batch_options
FROM
dual;
SELECT
component_item_id,
new_operation_seq_num,
new_effectivity_date
FROM
bom_inventory_comps_interface
WHERE
batch_id = p_batch_id AND comp_source_system_reference = p_comp_rec_id;*
SELECT BCB.component_sequence_id
INTO l_comp_seq_id
FROM bom_components_b BCB,bom_inventory_comps_interface BICI
WHERE BICI.batch_id = p_batch_id
AND (BICI.comp_source_system_reference = p_comp_rec_id OR BICI.component_item_number = p_comp_rec_id)
AND (BICI.process_flag = 1 or BICI.process_flag = 5 )
AND BCB.bill_sequence_id = p_bill_seq_id
AND BCB.component_item_id = p_component_item_id
AND BCB.operation_seq_num = nvl(BICI.new_operation_seq_num,BICI.operation_seq_num)
AND BCB.effectivity_date = nvl(BICI.new_effectivity_date,BICI.effectivity_date)
AND (( pg_batch_options.CHANGE_NOTICE IS NULL AND BCB.implementation_date IS NOT NULL)
OR(pg_batch_options.CHANGE_NOTICE IS NOT NULL AND BCB.implementation_date IS NULL AND BCB.change_notice = pg_batch_options.CHANGE_NOTICE));
/*OPEN l_src_attrs FOR SELECT component_item_id,operation_seq_num,from_end_item_unit_number
FROM bom_inventory_comps_interface
WHERE batch_id = p_batch_id AND comp_source_system_reference = p_comp_rec_id;
SELECT BCB.component_sequence_id
INTO l_comp_seq_id
FROM bom_components_b BCB,bom_inventory_comps_interface BICI
WHERE BICI.batch_id = p_batch_id
AND (BICI.comp_source_system_reference = p_comp_rec_id OR BICI.component_item_number = p_comp_rec_id)
AND (BICI.process_flag = 1 or BICI.process_flag = 5 )
AND BCB.bill_sequence_id = p_bill_seq_id
AND BCB.component_item_id = p_component_item_id
AND BCB.operation_seq_num = nvl(BICI.new_operation_seq_num,BICI.operation_seq_num)
AND BCB.from_end_item_unit_number = nvl(BICI.new_from_end_item_unit_number,BICI.from_end_item_unit_number)
AND (( pg_batch_options.CHANGE_NOTICE IS NULL AND BCB.implementation_date IS NOT NULL)
OR(pg_batch_options.CHANGE_NOTICE IS NOT NULL AND BCB.implementation_date IS NULL AND BCB.change_notice = pg_batch_options.CHANGE_NOTICE));
SELECT BCB.component_sequence_id
INTO l_comp_seq_id
FROM bom_components_b BCB,bom_inventory_comps_interface BICI,Mtl_Item_Revisions MIR
WHERE BICI.batch_id = p_batch_id
AND (BICI.comp_source_system_reference = p_comp_rec_id OR BICI.component_item_number = p_comp_rec_id)
AND (BICI.process_flag = 1 or BICI.process_flag = 5 )
AND BCB.bill_sequence_id = p_bill_seq_id
AND BCB.component_item_id = p_component_item_id
AND nvl(BCB.operation_seq_num,1) = nvl(BICI.new_operation_seq_num,1)
AND MIR.inventory_item_id = p_parent_item_id
AND MIR.organization_id = p_organization_id
AND MIR.revision = BICI.from_end_item_rev_code
AND BCB.from_end_item_rev_id = MIR.Revision_Id
AND (( pg_batch_options.CHANGE_NOTICE IS NULL AND BCB.implementation_date IS NOT NULL)
OR(pg_batch_options.CHANGE_NOTICE IS NOT NULL AND BCB.implementation_date IS NULL AND BCB.change_notice = pg_batch_options.CHANGE_NOTICE));
SELECT BCB.component_sequence_id
INTO l_comp_seq_id
FROM bom_components_b BCB,bom_inventory_comps_interface BICI
WHERE BICI.batch_id = p_batch_id
AND (BICI.comp_source_system_reference = p_comp_rec_id OR BICI.component_item_number = p_comp_rec_id)
AND (BICI.process_flag = 1 or BICI.process_flag = 5 )
AND BCB.bill_sequence_id = p_bill_seq_id
AND BCB.component_item_id = p_component_item_id
AND ( (BICI.ASSEMBLY_ITEM_ID IS NULL AND BICI.ASSEMBLY_ITEM_NUMBER = p_assembly_item_number)
OR (BICI.ASSEMBLY_ITEM_ID IS NOT NULL AND BICI.ASSEMBLY_ITEM_ID = p_parent_item_id)) -- add ASSEMBLY_ITEM_NUMBER/ASSEMBLY_ITEM_ID clause bug 12386997
AND (BICI.new_operation_seq_num IS NULL OR BCB.operation_seq_num = BICI.new_operation_seq_num)
AND ((BICI.new_effectivity_date IS NULL and BCB.effectivity_date = sysdate) OR BCB.effectivity_date = BICI.new_effectivity_date)
AND (( pg_batch_options.CHANGE_NOTICE IS NULL AND BCB.implementation_date IS NOT NULL and nvl(bcb.disable_date, sysdate+1) > sysdate ) -- add chcking disable date clause for bug 12386997
OR(pg_batch_options.CHANGE_NOTICE IS NOT NULL AND BCB.implementation_date IS NULL AND BCB.change_notice = pg_batch_options.CHANGE_NOTICE));
SELECT BCB.component_sequence_id
INTO l_comp_seq_id
FROM bom_components_b BCB,bom_inventory_comps_interface BICI
WHERE BICI.batch_id = p_batch_id
AND (BICI.comp_source_system_reference = p_comp_rec_id OR BICI.component_item_number = p_comp_rec_id)
AND (BICI.process_flag = 1 or BICI.process_flag = 5 )
AND BCB.bill_sequence_id = p_bill_seq_id
AND BCB.component_item_id = p_component_item_id
AND ( (BICI.ASSEMBLY_ITEM_ID IS NULL AND BICI.ASSEMBLY_ITEM_NUMBER = p_assembly_item_number)
OR (BICI.ASSEMBLY_ITEM_ID IS NOT NULL AND BICI.ASSEMBLY_ITEM_ID = p_parent_item_id)) -- add ASSEMBLY_ITEM_NUMBER/ASSEMBLY_ITEM_ID clause bug 12386997
AND (BICI.new_operation_seq_num IS NULL OR BCB.operation_seq_num = BICI.new_operation_seq_num)
AND (BICI.new_effectivity_date IS NULL OR BCB.effectivity_date = BICI.new_effectivity_date)
AND (( pg_batch_options.CHANGE_NOTICE IS NULL AND BCB.implementation_date IS NOT NULL and nvl(bcb.disable_date, sysdate+1) > sysdate ) -- add chcking disable date clause for bug 12386997
OR(pg_batch_options.CHANGE_NOTICE IS NOT NULL AND BCB.implementation_date IS NULL AND BCB.change_notice = pg_batch_options.CHANGE_NOTICE));
SELECT BCB.component_sequence_id
INTO l_comp_seq_id
FROM bom_components_b BCB,bom_inventory_comps_interface BICI
WHERE BICI.batch_id = p_batch_id
AND (BICI.comp_source_system_reference = p_comp_rec_id OR BICI.component_item_number = p_comp_rec_id)
AND (BICI.process_flag = 1 or BICI.process_flag = 5 )
AND BCB.bill_sequence_id = p_bill_seq_id
AND BCB.component_item_id = p_component_item_id
AND (BICI.new_operation_seq_num IS NULL OR BCB.operation_seq_num = BICI.new_operation_seq_num)
AND (BICI.new_from_end_item_unit_number IS NULL OR BCB.from_end_item_unit_number = BICI.new_from_end_item_unit_number)
AND (( pg_batch_options.CHANGE_NOTICE IS NULL AND BCB.implementation_date IS NOT NULL)
OR(pg_batch_options.CHANGE_NOTICE IS NOT NULL AND BCB.implementation_date IS NULL AND BCB.change_notice = pg_batch_options.CHANGE_NOTICE))
-- bug 12570397, no need populate component_sequence_id for a new component
AND UPPER(BICI.transaction_type) not in ('ADD','CREATE');
SELECT BCB.component_sequence_id
INTO l_comp_seq_id
FROM bom_components_b BCB,bom_inventory_comps_interface BICI,Mtl_Item_Revisions MIR
WHERE BICI.batch_id = p_batch_id
AND (BICI.comp_source_system_reference = p_comp_rec_id OR BICI.component_item_number = p_comp_rec_id)
AND (BICI.process_flag = 1 or BICI.process_flag = 5 )
AND BCB.bill_sequence_id = p_bill_seq_id
AND BCB.component_item_id = p_component_item_id
AND nvl(BCB.operation_seq_num,1) = nvl(BICI.new_operation_seq_num,1)
AND MIR.inventory_item_id = p_parent_item_id
AND MIR.organization_id = p_organization_id
AND MIR.revision = BICI.from_end_item_rev_code
AND BCB.from_end_item_rev_id = MIR.Revision_Id
-- Bug 14251113 FIX start
AND ( (BICI.ASSEMBLY_ITEM_ID IS NULL AND BICI.ASSEMBLY_ITEM_NUMBER = p_assembly_item_number)
OR (BICI.ASSEMBLY_ITEM_ID IS NOT NULL AND BICI.ASSEMBLY_ITEM_ID = p_parent_item_id))
-- Bug 14251113 FIX end
AND (( pg_batch_options.CHANGE_NOTICE IS NULL AND BCB.implementation_date IS NOT NULL
-- Bug 14251113 FIX start add chcking disable date clause for bug 12386997
and nvl(bcb.disable_date, sysdate+1) > sysdate )
-- Bug 14251113 FIX end
OR(pg_batch_options.CHANGE_NOTICE IS NOT NULL AND BCB.implementation_date IS NULL AND BCB.change_notice = pg_batch_options.CHANGE_NOTICE))
-- bug 12570397, no need populate component_sequence_id for a new component
AND UPPER(BICI.transaction_type) not in ('ADD','CREATE');
SELECT
'Exist'
INTO
l_dummy
FROM
mtl_system_items_interface MSII
WHERE
MSII.set_process_id = p_batch_id
AND ( (MSII.source_system_reference = p_ss_reference AND MSII.source_system_reference_desc = p_ss_desc )
OR(MSII.item_number = p_item_number AND MSII.description = p_item_desc)
)
AND (MSII.organization_code = p_org_code OR MSII.organization_id = p_org_id)
AND process_flag = 1;
update_transaction_ids(p_batch_id);
UPDATE_MATCH_DATA
(
p_batch_id => p_batch_id,
p_source_system_id => NULL,
x_Mesg_Token_Tbl => l_mesg_token_tbl,
x_Return_Status => x_retcode
);
UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
SET COMPONENT_SEQUENCE_ID =
(SELECT BIC.COMPONENT_SEQUENCE_ID
FROM BOM_INVENTORY_COMPONENTS BIC
WHERE BIC.BILL_SEQUENCE_ID = BICI.BILL_SEQUENCE_ID
AND BIC.COMPONENT_ITEM_ID = BICI.COMPONENT_ITEM_ID
AND BIC.OPERATION_SEQ_NUM = BICI.OPERATION_SEQ_NUM
AND BICI.DISABLE_DATE BETWEEN BIC.EFFECTIVITY_DATE AND NVL(BIC.DISABLE_DATE, BICI.DISABLE_DATE+1)
AND BIC.IMPLEMENTATION_DATE IS NOT NULL
)
WHERE BICI.BATCH_ID = p_batch_id
AND UPPER(BICI.TRANSACTION_TYPE) = 'DELETE'
AND BICI.COMPONENT_SEQUENCE_ID IS NULL
AND BICI.PROCESS_FLAG IN (1,5);
* Purpose : This procedure will update the Bom Structure and Components
* Interface tables with the cross reference data obtained from
* Mtl_Cross_References.This API will update the Cross Referenced data
* for record in a batch which have matching entries in
* Mtl_Cross_References table.
* ??This should also insert customer xrefed rows
* Will return with success for Xreferences only
**********************************************************************/
PROCEDURE RESOLVE_XREFS_FOR_BATCH
(
p_batch_id IN NUMBER
,x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
,x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
SELECT MCR.inventory_item_id,MCR.organization_id,BBMI.source_system_reference,MSI.segment1
FROM bom_bill_of_mtls_interface BBMI,mtl_cross_references MCR,ego_import_batches_b EIBB,mtl_system_items MSI
WHERE BBMI.batch_id = l_batch_id
AND EIBB.batch_id = BBMI.batch_id
AND MCR.source_system_id = EIBB.source_system_id
AND MCR.cross_reference = BBMI.source_system_reference
AND MCR.cross_reference_type = 'SS_ITEM_XREF'
AND MSI.inventory_item_id = MCR.inventory_item_id
AND MSI.organization_id = MCR.organization_id
AND BBMI.assembly_item_id IS NULL
AND (BBMI.process_flag = 1 OR BBMI.process_flag = 5);
SELECT MCR.inventory_item_id,MCR.organization_id,BICI.comp_source_system_reference,MSI.segment1
FROM bom_inventory_comps_interface BICI,mtl_cross_references MCR,ego_import_batches_b EIBB,mtl_system_items MSI
WHERE BICI.batch_id = l_batch_id
AND EIBB.batch_id = BICI.batch_id
AND MCR.source_system_id = EIBB.source_system_id
AND MCR.cross_reference = BICI.comp_source_system_reference
AND MCR.cross_reference_type = 'SS_ITEM_XREF'
AND MSI.inventory_item_id = MCR.inventory_item_id
AND MSI.organization_id = MCR.organization_id
AND BICI.component_item_id IS NULL
AND (BICI.process_flag = 1 OR BICI.process_flag = 5);
SELECT MCR.inventory_item_id,MCR.organization_id,BICI.comp_source_system_reference,MSI.segment1
FROM bom_inventory_comps_interface BICI,mtl_cross_references MCR,ego_import_batches_b EIBB,mtl_system_items MSI
WHERE BICI.batch_id = p_batch_id
AND EIBB.batch_id = BICI.batch_id
AND MCR.source_system_id = EIBB.source_system_id
AND MCR.cross_reference = BICI.parent_source_system_reference
AND MCR.cross_reference_type = 'SS_ITEM_XREF'
AND MSI.inventory_item_id = MCR.inventory_item_id
AND MSI.organization_id = MCR.organization_id
AND BICI.assembly_item_id IS NULL
AND (BICI.process_flag = 1 OR BICI.process_flag = 5);
UPDATE bom_bill_of_mtls_interface
SET assembly_item_id = l_item_id_table(i),
organization_id = l_org_id_table(i),
item_number = l_item_num_table(i)
WHERE batch_id = p_batch_id
AND source_system_reference = l_ss_record_table(i)
AND (process_flag = 1 OR process_flag = 5);
UPDATE bom_inventory_comps_interface
SET component_item_id = l_item_id_table(i),
organization_id = l_org_id_table(i),
component_item_number = l_item_num_table(i)
WHERE batch_id = p_batch_id
AND comp_source_system_reference = l_ss_record_table(i)
AND ( process_flag = 1 OR process_flag = 5) ;
UPDATE bom_inventory_comps_interface
SET assembly_item_id = l_item_id_table(i),
organization_id = l_org_id_table(i),
assembly_item_number = l_item_num_table(i)
WHERE batch_id = p_batch_id
AND comp_source_system_reference = l_ss_record_table(i)
AND ( process_flag = 1 OR process_flag = 5) ;
/* Update Match Data */
PROCEDURE UPDATE_MATCH_DATA
(
p_batch_id IN NUMBER
, p_source_system_id IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
SELECT MSII.inventory_item_id,MSII.organization_id,MSII.source_system_reference,MSII.item_number
FROM bom_bill_of_mtls_interface BBMI,mtl_system_items_interface MSII
WHERE BBMI.batch_id = p_batch_id
AND (BBMI.process_flag = 1 OR BBMI.process_flag = 5)
AND MSII.set_process_id = BBMI.batch_id
AND MSII.process_flag IN (0,1,7)
AND ((l_request_id <> -1 AND MSII.request_id = l_request_id) OR (l_request_id = -1 AND BBMI.request_id = MSII.request_id))
AND (MSII.source_system_reference = BBMI.source_system_reference OR MSII.item_number = BBMI.item_number)
AND (MSII.organization_code = BBMI.organization_code OR MSII.organization_id = BBMI.organization_id);
SELECT MSII.inventory_item_id,MSII.organization_id,MSII.source_system_reference,MSII.item_number
FROM bom_inventory_comps_interface BICI,mtl_system_items_interface MSII
WHERE BICI.batch_id = p_batch_id
AND (BICI.process_flag = 1 OR BICI.process_flag = 5)
AND MSII.set_process_id = BICI.batch_id
AND MSII.process_flag IN (0,1,7)
AND ((l_request_id <> -1 AND MSII.request_id = l_request_id) OR (l_request_id = -1 AND BICI.request_id = MSII.request_id))
AND (MSII.source_system_reference = BICI.comp_source_system_reference OR MSII.item_number = BICI.component_item_number)
AND (MSII.organization_code = BICI.organization_code OR MSII.organization_id = BICI.organization_id);
SELECT MSII.inventory_item_id,MSII.organization_id,MSII.source_system_reference,MSII.item_number
FROM bom_inventory_comps_interface BICI,mtl_system_items_interface MSII
WHERE BICI.batch_id = p_batch_id
AND (BICI.process_flag = 1 OR BICI.process_flag = 5)
AND MSII.set_process_id = BICI.batch_id
AND MSII.process_flag IN (0,1,7)
AND ((l_request_id <> -1 AND MSII.request_id = l_request_id) OR (l_request_id = -1 AND BICI.request_id = MSII.request_id))
AND ( MSII.source_system_reference = BICI.parent_source_system_reference OR MSII.item_number = BICI.assembly_item_number )
AND (MSII.organization_code = BICI.organization_code OR MSII.organization_id = BICI.organization_id);
write_debug('In Update Match Data');
UPDATE bom_bill_of_mtls_interface
SET assembly_item_id = l_item_id_table(i),
Organization_id = l_org_id_table(i),
item_number = l_item_num_table(i),
bill_sequence_id = null,
transaction_type = 'SYNC'
WHERE batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 5)
AND (source_system_reference = l_ss_record_table(i) OR item_number = l_item_num_table(i)) ;
UPDATE bom_inventory_comps_interface
SET component_item_id = l_item_id_table(i),
Organization_id = l_org_id_table(i),
component_item_number = l_item_num_table(i)
WHERE batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 5)
AND (comp_source_system_reference = l_ss_record_table(i) OR component_item_number = l_item_num_table(i));
UPDATE bom_inventory_comps_interface
SET assembly_item_id = l_item_id_table(i),
Organization_id = l_org_id_table(i),
assembly_item_number = l_item_num_table(i)
WHERE batch_id = p_batch_id
and (process_flag = 1 OR process_flag = 5)
AND ( parent_source_system_reference = l_ss_record_table(i) OR assembly_item_number = l_item_num_table(i)) ;
update_bill_info(p_batch_id => p_batch_id,
x_Mesg_Token_Tbl => l_Mesg_Token_Tbl,
x_Return_Status => x_return_status);
END UPDATE_MATCH_DATA;
/* End Update Match Data */ --??DInu why two
PROCEDURE UPDATE_BILL_INFO
(
p_batch_id IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
TYPE bom_comp_intf_type IS TABLE OF bom_inventory_comps_interface%ROWTYPE;
SELECT BBMI.assembly_item_id,BBMI.organization_id,BBMI.alternate_bom_designator,BBMI.source_system_reference,UPPER(BBMI.transaction_type),BBMI.organization_code,BBMI.item_number
FROM bom_bill_of_mtls_interface BBMI
WHERE batch_id = l_batch_id
AND process_flag NOT IN(3,7,-1);
SELECT BSB.bill_sequence_id,BSB.effectivity_control,BSB.organization_id
FROM bom_bill_of_mtls_interface BBMI,
bom_Structures_b BSB
WHERE BBMI.batch_id = l_batch_id
AND process_flag NOT IN(3,7,-1)
AND BSB.assembly_item_id = l_item_id
AND BSB.organization_id = l_org_id
AND NVL(BSB.alternate_bom_designator,'Y') = NVL(l_name,'Y');
SELECT *
FROM bom_inventory_comps_interface BICI
WHERE batch_id = l_batch_id
AND process_flag NOT IN(3,7,-1)
AND (parent_source_system_reference = p_parent_reference OR assembly_item_number = l_parent_name);
SELECT *
FROM Bom_Components_B BCB
WHERE BCB.bill_sequence_id = l_bill_seq_id;
write_debug('In Update Bill Info');
SELECT organization_id
INTO l_org_id_table(i)
FROM mtl_parameters
WHERE organization_code = l_org_code_table(i);
SELECT inventory_item_id
INTO l_item_id_table(i)
FROM mtl_system_items_vl
WHERE (concatenated_segments = l_header_rec_table(i) OR concatenated_segments = l_item_name_table(i))
AND organization_id = l_org_id_table(i);
IF (l_txn_table(i) = 'SYNC' OR l_txn_table(i) = 'CREATE' OR l_txn_table(i) = 'UPDATE')
THEN
l_txn_table(i) := 'CREATE';
l_comp_table(j).transaction_type = 'UPDATE')
THEN
IF l_comp_table(j).component_sequence_id IS NULL THEN
l_comp_table(j).transaction_type := 'CREATE';
l_comp_table(j).transaction_type := 'UPDATE';
write_debug('Bill sequence id is not null--Update header bill_seq_id ' || l_bill_seq_id);
IF (l_txn_table(i) ='SYNC' OR l_txn_table(i) ='CREATE' OR l_txn_table(i) ='UPDATE')
THEN
l_txn_table(i) := 'UPDATE';
SELECT component_item_id
into l_comp_id
from bom_components_b
where component_sequence_id = l_comp_table(j).component_sequence_id;
SELECT inventory_item_id
INTO l_comp_table(j).component_item_id
FROM mtl_system_items_vl
WHERE concatenated_segments = l_comp_table(j).component_item_number
AND organization_id = l_org_id_table(i);
IF (l_comp_table(j).transaction_type = 'DELETE') THEN
IF (l_comp_table(j).disable_date IS NULL) THEN
l_comp_table(j).disable_date := sysdate;
IF (l_comp_table(j).transaction_type = 'SYNC' OR l_comp_table(j).transaction_type = 'CREATE' OR l_comp_table(j).transaction_type = 'UPDATE') THEN
l_comp_table(j).transaction_type := 'UPDATE';
ELSIF l_comp_table(j).transaction_type = 'UPDATE' THEN
IF l_comp_table(j).component_sequence_id IS NULL THEN
l_comp_table(j).component_sequence_id := l_comp_seq_id;
IF (l_comp_table(j).transaction_type = 'DELETE') THEN
IF (l_comp_table(j).component_sequence_id IS NULL) THEN
l_comp_table(j).component_sequence_id := l_comp_seq_id;
IF (l_comp_table(j).transaction_type = 'SYNC' OR l_comp_table(j).transaction_type = 'UPDATE' OR l_comp_table(j).transaction_type = 'CREATE') THEN
l_comp_table(j).transaction_type := 'CREATE';
l_comp_table(j).transaction_type := 'UPDATE';
IF l_comp_table(j).transaction_type = 'DELETE' THEN
IF l_comp_table(j).disable_date IS NULL THEN
l_comp_table(j).disable_date := sysdate;
UPDATE bom_inventory_comps_interface
SET bill_sequence_id = l_comp_table(j).bill_sequence_id ,
transaction_type = l_comp_table(j).transaction_type,
component_sequence_id = l_comp_table(j).component_sequence_id,
old_component_sequence_id = l_comp_table(j).old_component_sequence_id,
disable_date = l_comp_table(j).disable_date,
component_item_id = l_comp_table(j).component_item_id
WHERE batch_id = l_comp_table(j).batch_id
AND (process_flag = 1 or process_flag = 5)
AND ( component_sequence_id = l_comp_table(j).component_sequence_id
OR (/*component_sequence_id IS NULL
AND*/(comp_source_system_reference = l_comp_table(j).comp_source_system_reference OR component_item_number = l_comp_table(j).component_item_number)
AND (parent_source_system_reference = l_comp_table(j).parent_source_system_reference OR assembly_item_number = l_comp_table(j).assembly_item_number)));
UPDATE bom_cmp_usr_attr_interface
SET item_number = l_comp_table(j).component_item_number,
assembly_item_number = l_comp_table(j).assembly_item_number,
comp_source_system_reference = l_comp_table(j).comp_source_system_reference,
parent_source_system_reference = l_comp_table(j).parent_source_system_reference,
organization_id = l_org_id_table(i),
attr_group_type = 'BOM_COMPONENTMGMT_GROUP' ,
component_item_id = l_comp_table(j).component_item_id
--process_status = 2
WHERE batch_id = p_batch_id
AND item_number = l_comp_table(j).component_item_number
AND assembly_item_number = l_comp_table(j).assembly_item_number
AND process_status NOT IN (3,4);
UPDATE bom_bill_of_mtls_interface
SET transaction_type = l_txn_table(i),
Bill_sequence_id = l_bill_seq_id,
assembly_item_id = l_item_id_table(i)
WHERE batch_id = p_batch_id
AND (source_system_reference = l_header_rec_table(i) OR item_number = l_item_name_table(i))
AND (process_flag = 1 or process_flag = 5);
END UPDATE_BILL_INFO;
/* End Update Bill Info */
/**
* This procedure is the starting point for the existing open interface
* tables being used to create batches.
* Users will call this API once the data load for a batch is done in the
* bom interface tables.
*
*/
PROCEDURE DATA_UPLOAD_COMPLETE
(
p_batch_id IN NUMBER
, p_init_msg_list IN VARCHAR2
, x_return_status IN OUT NOCOPY VARCHAR2
, x_Error_Mesg IN OUT NOCOPY VARCHAR2
, p_debug IN VARCHAR2
, p_output_dir IN VARCHAR2
, p_debug_filename IN VARCHAR2
)
IS
G_EXC_SEV_QUIT_OBJECT EXCEPTION;
SELECT
structure_type_id,
structure_name,
structure_effectivity_type
FROM
ego_import_option_sets
WHERE
batch_id = p_batch_id;
SELECT
BBMI.SOURCE_SYSTEM_REFERENCE,
BBMI.SOURCE_SYSTEM_REFERENCE_DESC,
BBMI.CATALOG_CATEGORY_NAME,
BBMI.ITEM_CATALOG_GROUP_ID,
BBMI.PRIMARY_UNIT_OF_MEASURE,
EIBB.SOURCE_SYSTEM_ID,
BBMI.ORGANIZATION_ID,
BBMI.ORGANIZATION_CODE,
BBMI.ASSEMBLY_ITEM_ID,
BBMI.ITEM_NUMBER,
UPPER(BBMI.TRANSACTION_TYPE),
BBMI.ITEM_DESCRIPTION
FROM
bom_bill_of_mtls_interface BBMI,
ego_import_batches_b EIBB
WHERE
BBMI.batch_id = l_batch_id
AND EIBB.batch_id = BBMI.batch_id
AND BBMI.PROCESS_FLAG NOT IN (3,7,-1);
SELECT
BICI.comp_source_system_reference,
BICI.COMP_SOURCE_SYSTEM_REFER_DESC,
BICI.CATALOG_CATEGORY_NAME,
BICI.ITEM_CATALOG_GROUP_ID,
BICI.PRIMARY_UNIT_OF_MEASURE,
EIBB.SOURCE_SYSTEM_ID,
BICI.COMPONENT_ITEM_ID,
BICI.COMPONENT_ITEM_NUMBER,
BICI.ORGANIZATION_ID,
BICI.ORGANIZATION_CODE,
UPPER(BICI.TRANSACTION_TYPE),
BICI.ITEM_DESCRIPTION
FROM
bom_inventory_comps_interface BICI,
ego_import_batches_b EIBB
WHERE
BICI.batch_id = l_batch_id
AND EIBB.batch_id = BICI.batch_id
AND BICI.PROCESS_FLAG NOT IN (3,7,-1);
SELECT *
FROM ego_import_option_sets
WHERE batch_id = l_batch_id;
SELECT userenv('LANG')
INTO l_language
FROM dual;
Error_Handler.Translate_And_Insert_Messages
( p_mesg_token_tbl => l_Mesg_Token_tbl
, p_application_id => 'BOM'
);
Write_Debug('Inserting into Mtl_Interface for Header');
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
( set_process_id
, source_system_id
, source_system_reference
, SOURCE_SYSTEM_REFERENCE_DESC
, item_catalog_group_id
, primary_unit_of_measure
, organization_id
, organization_code
, inventory_item_id
, item_number
, transaction_type
, process_flag
, description
)
VALUES
(
p_batch_id
, l_ss_id_table(i)
, l_ss_ref_table(i)
, l_ss_desc_table(i)
, l_cat_grp_table(i)
, l_uom_table(i)
, l_org_id_table(i)
, l_org_code_table(i)
, l_item_id_table(i)
, l_item_number_table(i)
, l_txn_type_table(i)
, l_process_flag
, l_item_desc_table(i)
);
Write_Debug('Inserting into Mtl_Interface for Comps');
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(
set_process_id
, source_system_id
, source_system_reference
, SOURCE_SYSTEM_REFERENCE_DESC
, item_catalog_group_id
, primary_unit_of_measure
, organization_id
, organization_code
, inventory_item_id
, item_number
, transaction_type
, process_flag
, description
)
VALUES
(
p_batch_id
, l_ss_id_table(i)
, l_ss_ref_table(i)
, l_ss_desc_table(i)
, l_cat_grp_table(i)
, l_uom_table(i)
, l_org_id_table(i)
, l_org_code_table(i)
, l_item_id_table(i)
, l_item_number_table(i)
, l_txn_type_table(i)
, l_process_flag
, l_item_desc_table(i)
);
* Calling the Ego API to update the request_id to the batch.
* if Match On Data Load is Yes then the same request id will be passed in
* p_match_request_id
*/
IF l_request_id IS NOT NULL THEN
IF ( nvl(pG_batch_options.IMPORT_ON_DATA_LOAD,'N') = 'Y') THEN
l_import_req_id := l_request_id;
Ego_Import_Pvt.Update_Request_Id_To_Batch
(p_import_request_id => l_import_req_id,
p_match_request_id => l_match_req_id,
p_batch_id => p_batch_id
);
UPDATE_BILL_INFO
(
p_batch_id => p_batch_id,
x_Mesg_Token_Tbl => l_Mesg_Token_Tbl,
x_Return_Status => l_return_status
);
SELECT BCEB.component_sequence_id
FROM bom_components_ext_b BCEB
WHERE BCEB.structure_type_id = p_str_type_id
AND BCEB.attr_group_id = p_attr_grp_id;
SELECT BCUA.attr_group_int_name
FROM bom_cmp_usr_attr_interface BCUA
WHERE BCUA.batch_id = p_batch_id
AND BCUA.structure_type_id = p_str_type_id
AND (BCUA.attr_group_id = p_attr_grp_id OR BCUA.attr_group_int_name = p_attr_grp_name);
SELECT effectivity_date,new_effectivity_date,disable_date,from_end_item_unit_number,new_from_end_item_unit_number,to_end_item_unit_number,from_end_item_rev_code,to_end_item_rev_code
INTO l_eff_date_intf,l_new_eff_date_intf,l_dis_date_intf,l_from_num_intf ,l_new_from_num_intf,l_to_unit_num_intf,l_from_rev_intf ,l_to_item_rev_intf
FROM bom_inventory_comps_interface
WHERE batch_id = p_batch_id
AND interface_table_unique_id = p_intf_uniq_id;
SELECT effectivity_date,disable_date,from_end_item_unit_number,to_end_item_unit_number,from_end_item_rev_id,to_end_item_rev_id
INTO l_eff_date_pdh,l_dis_date_pdh,l_from_num_pdh,l_to_unit_num_pdh,l_from_rev_pdh,l_to_item_rev_pdh
from bom_components_b
where component_sequence_id = p_comp_seq_id;
l_eff_sql := ' SELECT ' ;
|| ' UNION ALL SELECT '
|| ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_REVISION_EFF'||'''), '
|| ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_TO_END_ITEM_REV_LABEL'||'''), '
|| ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_TO_END_ITEM_REV_LABEL'||'''), '
|| p_batch_id || ', ' ;
|| ' UNION ALL SELECT '
|| ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_UNIT_EFF'||'''), '
|| ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFF_TO_NUMBER'||'''), '
|| ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFF_TO_NUMBER'||'''), '
|| p_batch_id || ', ';
|| ' UNION ALL SELECT '
|| ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFF_DATE_CHOICE'||'''), '
|| ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_DISABLE_DATE'||'''), '
|| ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_DISABLE_DATE'||'''), '
|| p_batch_id || ', ' ;
l_attr_sql := l_attr_sql || 'SELECT distinct(attr_group_disp_name), attr_display_name,attr_name , batch_id batch_identifier ,';--decode(attr.attr_name, ';
l_attr_sql1 := 'SELECT grps.attr_group_disp_name, attrs.attr_display_name , attr_name , ' ;
FOR attr IN (SELECT * FROM bom_attrs_v)
LOOP
IF attr.attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
THEN
OPEN Get_Src_Attr(p_str_type_id,attr.attr_group_id,attr.attr_group_name);
SELECT 'Exist'
INTO l_dummy
FROM bom_cmp_usr_attr_interface BCUI
WHERE (BCUI.comp_source_system_reference = p_ss_record_id OR BCUI.component_sequence_id = p_comp_seq_id)
AND ( BCUI.attr_group_id = attr.attr_group_id OR BCUI.attr_group_int_name = attr.attr_group_name)
AND BCUI.attr_int_name = attr.attr_name
AND BCUI.batch_id = p_batch_id;
l_attr_sql := l_attr_sql || '''' || attr.attr_name || ''',BCUA.attr_disp_value,';--(SELECT to_char(decode( ';
l_attr_sql := l_attr_sql || '''' || attr.attr_name || ''',(SELECT null from dual),';
FOR attr IN (SELECT * FROM bom_attrs_v )
LOOP
IF attr.attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
THEN
OPEN Get_Attr_Details(p_str_type_id,attr.attr_group_id);
SELECT 'Exist'
INTO l_dummy
FROM bom_components_ext_b BCEB
WHERE BCEB.component_sequence_id = p_comp_seq_id
AND BCEB.attr_group_id = attr.attr_group_id;
l_attr_sql := l_attr_sql || '''' || attr.attr_name || ''' ,(SELECT null from dual),';
l_pdh_query := ' (SELECT * FROM bom_components_b WHERE component_sequence_id = :3 ) pdh_value' ;
l_pdh_query := '(SELECT ';
FOR attr IN (SELECT * FROM bom_attrs_v)
LOOP
IF attr.attr_group_type <> 'BOM_COMPONENTMGMT_GROUP'
THEN
IF attr.attr_group_type = 'BOM_COMPONENT_BASE' THEN
l_pdh_query := l_pdh_query || ' null as ' || attr.database_column || ' ,';
l_src_query := ' (SELECT * FROM bom_inventory_comps_interface WHERE batch_id = :1 ' ||
' AND ( (comp_source_system_reference = :2 OR component_item_number = ' || '''' || p_ss_record_id || ''' )' ||
' AND interface_table_unique_id = ' || p_intf_uniq_id ||
' AND organization_id = ' || p_org_id ||
' ) ) src_val ,';
l_src_query := '(SELECT ';
FOR attr IN (SELECT * FROM bom_attrs_v)
LOOP
IF attr.attr_group_type <> 'BOM_COMPONENTMGMT_GROUP'
THEN
IF attr.attr_group_type = 'BOM_COMPONENT_BASE' THEN
l_src_query := l_src_query || ' null as ' || attr.database_column || ' ,';
|| ' FROM (SELECT attr_group_name,attr_group_disp_name FROM ego_attr_groups_v WHERE attr_group_type = '||''''|| 'BOM_COMPONENT_BASE' || ''' AND application_id = 702 ORDER BY attr_group_name) grps,'
|| '(SELECT attr_name,attr_display_name,database_column,attr_group_name FROM ego_attrs_v WHERE attr_group_type = '|| ''''|| 'BOM_COMPONENT_BASE' || ''' AND application_id = 702 ORDER BY attr_group_name) attrs,'
|| l_src_query || l_pdh_query ||
' WHERE attrs.attr_group_name = grps.attr_group_name';
l_attr_sql := l_attr_sql || ' UNION ALL SELECT Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_IMPORT_REF_DESGS'||'''),null,null, ' || p_batch_id || ' batch_identifier '
||',bom_import_pub.get_ref_desgs(:1,:2,:3,1,:5,:6,:7,:8),bom_import_pub.get_ref_desgs(:1,:2,:3,null,:5,:6,:7,:8) from dual' ;
/*dinu_log_message(' UNION ALL SELECT Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_IMPORT_REF_DESGS'||'''),null,' || p_batch_id || ' batch_identifier ,';
SELECT
source_system_id
INTO
l_source_system_id
FROM
ego_import_batches_b
WHERE
batch_id = p_batch_id;
Write_Debug('Calling Update Match Data');
UPDATE_MATCH_DATA
(
p_batch_id => p_batch_id,
p_source_system_id => NULL,
x_Mesg_Token_Tbl => l_Mesg_Token_Tbl,
x_Return_Status => l_return_status
);
/*Write_Debug('after updating match data before update_bill_info');
UPDATE_BILL_INFO
(
p_batch_id => p_batch_id,
x_Mesg_Token_Tbl => l_Mesg_Token_Tbl,
x_Return_Status => l_return_status
);
SELECT component_reference_designator
FROM bom_ref_desgs_interface
WHERE batch_id = p_batch_id
AND ( ( (comp_source_system_reference = p_ss_ref OR component_item_number = p_ss_ref OR component_item_id = p_item_id )
-- AND effectivity_date = p_effec_date
-- AND operation_seq_num = p_op_seq_num
AND organization_id = p_org_id
)
OR component_sequence_id = p_comp_seq_id
)
AND process_flag <> -1
ORDER BY 1 DESC;
SELECT component_reference_designator
FROM bom_reference_designators
WHERE component_sequence_id = p_comp_seq_id
ORDER BY 1 DESC;
PROCEDURE Update_User_Attr_Data
(
p_batch_id IN NUMBER
, p_transaction_id IN NUMBER
, p_comp_seq_id IN NUMBER
, p_bill_seq_id IN NUMBER
, p_call_Ext_Api IN VARCHAR2
, p_parent_id IN NUMBER
, p_org_id IN NUMBER
, x_Return_Status IN OUT NOCOPY VARCHAR2
, x_Error_Text IN OUT NOCOPY VARCHAR2
)
IS
l_comp_id NUMBER;
select distinct efd.attr_group_id,
efd.descriptive_flex_context_code attr_group_name,
efd.descriptive_flexfield_name attr_group_type,
bcua.structure_type_id
from ego_fnd_dsc_flx_ctx_ext efd,
bom_cmp_usr_attr_interface bcua
where efd.application_id = 702
and efd.descriptive_flexfield_name = 'BOM_COMPONENTMGMT_GROUP'
and efd.descriptive_flex_context_code = bcua.attr_group_int_name
and bcua.data_set_id = p_data_set_id;
Select bcu.attr_int_name attr_int_name,
decode(efc.data_type,'C',bcu.attr_value_str,
'N',bcu.attr_value_num,
'D',bcu.attr_value_date,null) attr_value,
bcu.attr_disp_value,
efc.data_type data_type
from
bom_cmp_usr_attr_interface bcu,
fnd_descr_flex_column_usages fd,
ego_fnd_df_col_usgs_ext efc
where
bcu.data_set_id = p_data_set_id
and bcu.attr_group_int_name = p_attr_grp_int_name
and bcu.row_identifier = p_row_identifier
and efc.descriptive_flex_context_code = bcu.attr_group_int_name
and efc.application_id = 702
and efc.descriptive_flexfield_name = 'BOM_COMPONENTMGMT_GROUP'
and fd.application_id = efc.application_id
and fd.descriptive_flexfield_name = efc.descriptive_flexfield_name
and fd.descriptive_flex_context_code = efc.descriptive_flex_context_code
and fd.application_column_name = efc.application_column_name
and fd.end_user_column_name = bcu.attr_int_name;
Select distinct row_identifier
from bom_cmp_usr_attr_interface
where
data_set_id = p_data_set_id
and attr_group_int_name = p_attr_grp_int_name;
SELECT component_sequence_id
INTO l_comp_seq_id
FROM bom_inventory_comps_interface
WHERE batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 5)
AND transaction_id = p_transaction_id;
SELECT bill_sequence_id
INTO l_bill_seq_id
FROM bom_inventory_comps_interface
WHERE batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 5)
AND transaction_id = p_transaction_id;
SELECT component_item_number,assembly_item_number,component_item_id,organization_id,UPPER(transaction_type)
INTO l_comp_name,l_parent_name,l_comp_id,l_org_id,l_txn_type
FROM bom_inventory_comps_interface
WHERE batch_id = p_batch_id
AND (process_flag = 1 or process_flag = 5)
AND (component_sequence_id = p_comp_seq_id OR transaction_id = p_transaction_id);
* we need to update the pks here as the Insert_Default_Val_Rows ext api checks for these pks before inserting the default rows.
* if we dont update the pks , then in case we have some rows for some attrs in the excel and if that attr has default values, ext
* api will once again insert the default rows.Also we need to update the attr group id.
*/
UPDATE bom_cmp_usr_attr_interface BCUA
SET component_sequence_id = l_comp_seq_id,
bill_sequence_id = l_bill_seq_id,
process_status = 2,
attr_group_id = (select attr_group_id from EGO_FND_DSC_FLX_CTX_EXT where application_id = 702 and DESCRIPTIVE_FLEXFIELD_NAME = 'BOM_COMPONENTMGMT_GROUP' and DESCRIPTIVE_FLEX_CONTEXT_CODE = BCUA.attr_group_int_name),
attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
WHERE (BCUA.data_set_id = p_batch_id or BCUA.batch_id = p_batch_id )
AND BCUA.process_status NOT in (3,4)
AND ( (BCUA.component_sequence_id = l_comp_seq_id)
OR (BCUA.component_sequence_id IS NULL
AND BCUA.item_number = l_comp_name
AND BCUA.assembly_item_number = l_parent_name
AND BCUA.transaction_id = p_transaction_id)
);
l_target_sql := 'SELECT :l_comp_seq_id component_sequence_id , :l_bill_seq_id' ||
' bill_sequence_id, :structure_type_id' ||
' structure_type_id , :data_level_column DATA_LEVEL_COLUMN, :data_level_id DATA_LEVEL_ID, :context_id CONTEXT_ID, :transaction_id transaction_id FROM dual ';
l_add_class := 'SELECT bst.structure_type_id FROM BOM_STRUCTURE_TYPES_B bst START WITH bst.structure_type_id = ' || pG_batch_options.structure_type_id || ' CONNECT BY PRIOR bst.parent_structure_type_id = bst.structure_type_id ';
EGO_USER_ATTRS_BULK_PVT.Insert_Default_Val_Rows (
p_api_version => 1.0
,p_application_id => 702
,p_attr_group_type => 'BOM_COMPONENTMGMT_GROUP'
,p_object_name => 'BOM_COMPONENTS'
,p_interface_table_name => 'BOM_CMP_USR_ATTR_INTERFACE'
,p_data_set_id => p_batch_id
,p_target_entity_sql => l_target_sql
,p_additional_class_Code_query => l_add_class
,p_commit => 'T'
,p_comp_seq_id => l_comp_seq_id
,p_bill_seq_id => l_bill_seq_id
,p_structure_type_id => pG_batch_options.structure_type_id
,p_data_level_column => null
,p_datalevel_id => 70201
,p_context_id => null
,p_transaction_id => p_transaction_id
,x_return_status => l_return_status
,x_msg_data => l_err_text
);
* Update comp_item_id and org_id.Otherwise ext bulkload will fail for privilege check.We check for
* Edit item and View Item privileges.For this we need the comp ids and org ids.
*/
UPDATE bom_cmp_usr_attr_interface
SET component_item_id = l_comp_id,
organization_id = l_org_id,
attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
WHERE (data_set_id = p_batch_id or batch_id = p_batch_id )
AND ( (component_sequence_id = l_comp_seq_id)
OR (component_sequence_id IS NULL
AND item_number = l_comp_name
AND assembly_item_number = l_parent_name
AND transaction_id = p_transaction_id)
);
SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
INTO l_gz_party_id
FROM ego_people_v
WHERE USER_NAME = l_user_name and rownum < 2;
UPDATE bom_cmp_usr_attr_interface
SET process_status = 2
WHERE data_set_id = p_batch_id
AND bill_sequence_id = l_bill_seq_id
AND process_status = 0;
SELECT assembly_item_id,organization_id
INTO l_parent_id,l_org_id
FROM bom_structures_b
WHERE bill_sequence_id = l_bill_seq_id;*/
, p_related_class_codes_query => 'SELECT bst.structure_type_id FROM BOM_STRUCTURE_TYPES_B bst START WITH bst.structure_type_id = UAI2.STRUCTURE_TYPE_ID CONNECT BY PRIOR bst.parent_structure_type_id = bst.structure_type_id '
, p_init_fnd_msg_list => 'F'
, p_log_errors => 'T'
, p_add_errors_to_fnd_stack => 'T'
, p_commit => 'T'
, p_default_view_privilege => 'EGO_VIEW_ITEM'
, p_default_edit_privilege => l_edit_prvlg
, p_privilege_predicate_api_name => 'Bom_Import_Pub.Get_Item_Security_Predicate'
, p_validate => true
, p_do_dml => true
, x_return_status => l_return_status
, x_errorcode => l_err_code
, x_msg_count => l_msg_count
, x_msg_data => l_err_text
);
UPDATE bom_cmp_usr_attr_interface
SET process_status = 4
WHERE ( data_set_id = p_batch_id or batch_id = p_batch_id)
AND process_status = 2
AND bill_sequence_id = l_bill_seq_id;
END Update_User_Attr_Data;
* a. IF unmatched items are inserted Notify
* Item Ego Data Upload Complete API
* 3. Check Batch for Options - IF automated call import
**************************************************************************/
PROCEDURE Data_Upload_Complete
( p_batch_id IN NUMBER
, x_error_message OUT NOCOPY VARCHAR2
, x_return_code OUT NOCOPY VARCHAR2
)
IS
BEGIN
BOM_IMPORT_PUB.DATA_UPLOAD_COMPLETE
(
p_batch_id => p_batch_id,
x_Error_Mesg => x_error_message,
p_init_msg_list => 'N',
x_return_status => x_return_code,
p_debug => 'N',
p_output_dir => NULL,
p_debug_filename => NULL
);
INSERT INTO ITEMS AND NOTIFY ITEMS DATALOAD
ELSE
CALL BOM JCP
END IF;
Update the bill information - Call Dinu's API
UPdate IF change required to 5.
*************************************************************************/
PROCEDURE PRE_PROCESS_IMPORT_ROWS
(
p_batch_id IN NUMBER
, p_items_import_complete IN VARCHAR2
, x_error_message OUT NOCOPY VARCHAR2
, x_return_code OUT NOCOPY VARCHAR2
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
)
IS
l_item_infcrows_exists NUMBER;
SELECT COUNT(*) INTO l_item_infcrows_exists FROM
( SELECT
'X'
FROM
mtl_system_items_interface
WHERE EXISTS
(SELECT
process_flag
FROM
mtl_system_items_interface
WHERE
set_process_id = p_batch_id
AND process_flag = 1
UNION ALL
SELECT
process_flag
FROM
mtl_item_revisions_interface
WHERE
set_process_id = p_batch_id
AND process_flag = 1) ) QRSLT;
Write_Debug('INSERT INTO ITEMS AND NOTIFY ITEMS DATALOAD AND NOTIFY ITEMS');
/* IF ANY UNMATCHED RECORDS THEN INSERT INTO ITEMS AND NOTIFY ITEMS DATALOAD AND NOTIFY ITEMS */
NULL;
/* Update_Bill_Info
(
p_batch_id => p_batch_id
, x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
, x_Return_Status => x_Return_code
);*/
/* Cursor to select confirm_status 'E' and 'U' rows for all Item Rows */
CURSOR Item_Intf_NotReadyCr IS
SELECT
source_system_reference,
inventory_item_id,
organization_id,
confirm_status,
process_flag
FROM
mtl_system_items_interface
WHERE
set_process_id = p_batch_id
AND confirm_status IN ('US','UM','UN','EX');
SELECT
source_system_reference,
inventory_item_id,
organization_id,
confirm_status,
process_flag
FROM
mtl_system_items_interface
WHERE
set_process_id = p_batch_id
AND confirm_status IN ('CC','CM','CN');
UPDATE
bom_bill_of_mtls_interface
SET
process_flag = 1
WHERE
batch_id = p_batch_id
AND source_system_reference = iicr.source_system_reference
AND process_flag = 0;
UPDATE
bom_bill_of_mtls_interface bmi
SET
process_flag = 1
WHERE
bmi.batch_id = p_batch_id
AND bmi.process_flag = 0
AND bmi.source_system_reference =
( SELECT DISTINCT
bci.parent_source_system_reference
FROM bom_inventory_comps_interface bci
WHERE
bci.batch_id = p_batch_id
AND bci.comp_source_system_reference = iicr.source_system_reference
AND iicr.confirm_status in ('CC','CM','CN'));
UPDATE
bom_inventory_comps_interface
SET
process_flag = 1
WHERE
batch_id = p_batch_id
and process_flag = 0
AND ( comp_source_system_reference = iicr.source_system_reference
OR parent_source_system_reference = iicr.source_system_reference);
UPDATE
bom_bill_of_mtls_interface
SET
process_flag = 0
WHERE
batch_id = p_batch_id
AND source_system_reference = iicr.source_system_reference
AND process_flag = 1;
UPDATE
bom_bill_of_mtls_interface bmi
SET
process_flag = 0
WHERE
bmi.batch_id = p_batch_id
AND bmi.process_flag = 1
AND bmi.source_system_reference =
( SELECT DISTINCT
bci.parent_source_system_reference
FROM bom_inventory_comps_interface bci
WHERE
bci.batch_id = p_batch_id
AND bci.comp_source_system_reference = iicr.source_system_reference
AND iicr.confirm_status in ('US','UM','UN'));
UPDATE
bom_inventory_comps_interface
SET
process_flag = 0
WHERE
batch_id = p_batch_id
and process_flag = 1
AND ( comp_source_system_reference = iicr.source_system_reference
OR parent_source_system_reference = iicr.source_system_reference);
p_delete_rows IN VARCHAR2,
p_batch_id IN NUMBER
)
IS
l_error_code VARCHAR2(1);
SELECT batch_id INTO l_batch_metadata_exists FROM
EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = p_batch_id;
del_rec_flag => p_delete_rows,
prog_appid => FND_GLOBAL.prog_appl_id,
prog_id => FND_GLOBAL.conc_program_id,
request_id => FND_GLOBAL.conc_request_id,
user_id => FND_GLOBAL.login_id,
login_id => FND_GLOBAL.login_id,
p_batch_id => p_batch_id,
err_text => x_err_buffer);
del_rec_flag => p_delete_rows,
prog_appid => FND_GLOBAL.prog_appl_id,
prog_id => FND_GLOBAL.conc_program_id,
request_id => FND_GLOBAL.conc_request_id,
user_id => FND_GLOBAL.login_id,
login_id => FND_GLOBAL.login_id,
p_batch_id => p_batch_id,
err_text => x_err_buffer);
PROCEDURE Update_Bill_Val_Id
(
p_batch_id IN NUMBER
, x_return_status IN OUT NOCOPY VARCHAR2
, x_Error_Mesg IN OUT NOCOPY VARCHAR2
)
IS
G_EXC_SEV_QUIT_OBJECT EXCEPTION;
SELECT assembly_item_id,organization_id,bill_sequence_id,alternate_bom_designator,item_number,organization_code
FROM bom_bill_of_mtls_interface
WHERE batch_id = p_batch_id
--AND (process_flag = 1 OR process_flag = 5)
AND (assembly_item_id IS NOT NULL OR item_number IS NOT NULL);
SELECT BICI.component_item_id,BICI.organization_id,BICI.bill_sequence_id,BBMI.assembly_item_id,decode(BICI.operation_seq_num,null,
BICI.new_operation_seq_num,BICI.operation_seq_num),decode(BICI.effectivity_date,null,BICI.new_effectivity_date,BICI.effectivity_date),
BICI.component_item_number,BICI.assembly_item_number,BICI.organization_code,BICI.component_sequence_id
FROM bom_inventory_comps_interface BICI,
bom_bill_of_mtls_interface BBMI
WHERE BBMI.batch_id = p_batch_id
AND BICI.batch_id = BBMI.batch_id
--AND (BBMI.process_flag = 1 OR BBMI.process_flag = 5)
--AND (BICI.process_flag = 1 OR BICI.process_flag = 5)
AND (BICI.bill_sequence_id = BBMI.bill_sequence_id OR BICI.assembly_item_id = BBMI.assembly_item_id OR BICI.assembly_item_number = BBMI.item_number)
AND (BICI.component_item_id IS NOT NULL OR BICI.component_item_number IS NOT NULL);
update_transaction_ids(p_batch_id);
SELECT organization_id
INTO l_org_id_table(i)
FROM mtl_parameters
WHERE organization_code = l_org_code_table(i);
SELECT inventory_item_id
INTO l_head_item_id_table(i)
FROM mtl_system_items_kfv
WHERE concatenated_segments = l_head_name_table(i)
AND organization_id = l_org_id_table(i);
SELECT concatenated_segments
INTO l_head_name_table(i)
FROM mtl_system_items_vl
WHERE inventory_item_id = l_head_item_id_table(i)
AND organization_id = l_org_id_table(i);
UPDATE bom_bill_of_mtls_interface
SET source_system_reference = l_head_name_table(i),
item_number = l_head_name_table(i),
assembly_item_id = l_head_item_id_table(i),
organization_id = l_org_id_table(i)
WHERE ((assembly_item_id = l_head_item_id_table(i) AND organization_id = l_org_id_table(i)) OR (item_number = l_head_name_table(i) AND organization_code = l_org_code_table(i)))
AND NVL(alternate_bom_designator,'Primary') = NVL(l_alt_desg_table(i),'Primary')
--AND (process_flag = 1 OR process_flag = 5)
AND batch_id = p_batch_id;
UPDATE bom_bill_of_mtls_interface
SET item_number = l_head_name_table(i),
assembly_item_id = l_head_item_id_table(i),
organization_id = l_org_id_table(i)
WHERE ((assembly_item_id = l_head_item_id_table(i) AND organization_id = l_org_id_table(i)) OR (item_number = l_head_name_table(i) AND organization_code = l_org_code_table(i)))
AND NVL(alternate_bom_designator,'Primary') = NVL(l_alt_desg_table(i),'Primary')
AND (process_flag = 1 OR process_flag = 5)
AND batch_id = p_batch_id;
SELECT organization_id
INTO l_org_id_table(i)
FROM mtl_parameters
WHERE organization_code = l_org_code_table(i);
SELECT inventory_item_id
INTO l_comp_item_id_table(i)
FROM mtl_system_items_kfv
WHERE concatenated_segments = l_comp_name_table(i)
AND organization_id = l_org_id_table(i);
SELECT concatenated_segments
INTO l_comp_name_table(i)
FROM mtl_system_items_vl
WHERE inventory_item_id = l_comp_item_id_table(i)
AND organization_id = l_org_id_table(i);
SELECT inventory_item_id
INTO l_head_item_id_table(i)
FROM mtl_system_items_kfv
WHERE concatenated_segments = l_head_name_table(i)
AND organization_id = l_org_id_table(i);
SELECT concatenated_segments
INTO l_head_name_table(i)
FROM mtl_system_items_vl
WHERE inventory_item_id = l_head_item_id_table(i)
AND organization_id = l_org_id_table(i);
UPDATE bom_inventory_comps_interface
SET comp_source_system_reference = l_comp_name_table(i),
parent_source_system_reference = l_head_name_table(i),
component_item_number = l_comp_name_table(i),
component_item_id = l_comp_item_id_table(i),
assembly_item_number = l_head_name_table(i),
assembly_item_id = l_head_item_id_table(i),
organization_id = l_org_id_table(i)
WHERE ((component_item_id = l_comp_item_id_table(i) AND organization_id = l_org_id_table(i)) OR (component_item_number = l_comp_name_table(i) AND organization_code = l_org_code_table(i)))
AND (assembly_item_id = l_head_item_id_table(i) OR assembly_item_number = l_head_name_table(i))
--AND (process_flag = 1 OR process_flag = 5)
--AND new_operation_seq_num = l_op_seq_table(i)
--AND new_effectivity_date = l_effectivity_table(i)
AND batch_id = p_batch_id;
UPDATE bom_inventory_comps_interface
SET component_item_number = l_comp_name_table(i),
component_item_id = l_comp_item_id_table(i),
assembly_item_number = l_head_name_table(i),
assembly_item_id = l_head_item_id_table(i),
organization_id = l_org_id_table(i)
WHERE ((component_item_id = l_comp_item_id_table(i) AND organization_id = l_org_id_table(i)) OR (component_item_number = l_comp_name_table(i) AND organization_code = l_org_code_table(i)))
AND (assembly_item_id = l_head_item_id_table(i) OR assembly_item_number = l_head_name_table(i))
AND (process_flag = 1 OR process_flag = 5)
--AND new_operation_seq_num = l_op_seq_table(i)
--AND new_effectivity_date = l_effectivity_table(i)
AND batch_id = p_batch_id;
UPDATE bom_ref_desgs_interface
SET component_sequence_id = l_comp_seq_table(i),
component_item_id = l_comp_item_id_table(i),
organization_id = l_org_id_table(i),
assembly_item_id = l_head_item_id_table(i)
WHERE batch_id = p_batch_id
AND ((component_item_number = l_comp_name_table(i) AND organization_code = l_org_code_table(i) ) OR (component_item_id = l_comp_item_id_table(i) AND organization_id = l_org_id_table(i)))
AND (assembly_item_number = l_head_name_table(i) OR assembly_item_id = l_head_item_id_table(i) )
AND nvl(operation_seq_num,1) = nvl(l_op_seq_table(i),1)
AND nvl(effectivity_date,sysdate) = nvl(l_effectivity_table(i),sysdate)
AND component_sequence_id IS NULL;
* So calling the Update_Bill_Info so that when the
* user comes to the UI we'll have all the data
* to show.
*/
Write_Debug('Calling the Update Bill Info');
BOM_IMPORT_PUB.UPDATE_BILL_INFO
(
p_batch_id => p_batch_id
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_Return_Status
);
END Update_Bill_Val_Id;
PROCEDURE Update_Confirmed_Items
(
p_batch_id IN NUMBER
, p_ssRef_varray IN VARCHAR2_VARRAY
, x_Error_Message IN OUT NOCOPY VARCHAR2
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
TYPE var_type IS TABLE OF VARCHAR2(1000);
SELECT inventory_item_id
INTO l_item_id
FROM mtl_system_items_interface
WHERE set_process_id = p_batch_id
AND source_system_reference = p_ssRef_varray(i);
UPDATE bom_bill_of_mtls_interface
SET assembly_item_id = l_item_id
WHERE batch_id = p_batch_id
AND source_system_reference = p_ssRef_varray(i);
UPDATE bom_inventory_comps_interface BICI
SET component_item_id = l_item_id
WHERE batch_id = p_batch_id
AND comp_source_system_reference = p_ssRef_varray(i);
END Update_Confirmed_Items;
SELECT bill_sequence_id
FROM bom_bill_of_mtls_interface
WHERE batch_id = p_batch_id;
SELECT add_all_to_change_flag
FROM ego_import_option_sets
WHERE batch_id = p_batch_id;
UPDATE Bom_Bill_Of_Mtls_Interface
SET process_flag = 5
WHERE batch_id = p_batch_id
AND bill_sequence_id = l_bill_seq_table(i);
UPDATE Bom_Inventory_Comps_Interface
SET Process_Flag = 5
WHERE batch_id = p_batch_id
AND bill_sequence_id = l_bill_seq_table(i);
SELECT
BBMI.assembly_item_id,BBMI.organization_id,BBMI.alternate_bom_designator,BBMI.source_system_reference,UPPER(BBMI.transaction_type),request_id,bundle_id
FROM bom_bill_of_mtls_interface BBMI
WHERE batch_id = l_batch_id
AND process_flag NOT IN (3,7,-1,0);
SELECT
BSB.bill_sequence_id,BSB.effectivity_control,BSB.organization_id
FROM
bom_bill_of_mtls_interface BBMI,
bom_Structures_b BSB
WHERE
BBMI.batch_id = l_batch_id
AND BBMI.process_flag NOT IN (3,7,-1,0)
AND BSB.assembly_item_id = l_item_id
AND BSB.organization_id = l_org_id
AND NVL(BSB.alternate_bom_designator,'Y') = NVL(l_name,'Y');
SELECT *
FROM bom_inventory_comps_interface BICI
WHERE batch_id = l_batch_id
AND process_flag NOT IN(3,7,0,-1)
AND parent_source_system_reference = p_parent_reference;
SELECT *
FROM Bom_Components_B BCB
WHERE BCB.bill_sequence_id = l_bill_seq_id;
write_debug('Calling update_bill_val');
Update_Bill_Val_Id
(
p_batch_id => p_batch_id
, x_return_status => x_Return_Status
, x_Error_Mesg => l_err_text
);
SELECT organization_id
INTO l_org_id_table(i)
FROM mtl_parameters
WHERE organization_code = l_org_code_table(i);
SELECT inventory_item_id
INTO l_item_id_table(i)
FROM mtl_system_items_vl
WHERE concatenated_segments = l_header_rec_table(i)
AND organization_id = l_org_id_table(i);
SELECT inventory_item_id
INTO l_comp_table(j).component_item_id
FROM mtl_system_items_vl
WHERE concatenated_segments = l_comp_table(j).comp_source_system_reference
AND organization_id = l_org_id_table(i);
INSERT INTO
bom_inventory_comps_interface
(
component_item_id,
organization_id,
component_sequence_id,
bill_sequence_id,
parent_source_system_reference,
batch_id,
transaction_type,
disable_date,
process_flag,
component_item_number,
assembly_item_number,
organization_code,
alternate_bom_designator,
assembly_item_id,
transaction_id
)
VALUES
(
l_unmatch_comp(m).component_item_id,
l_org_id_table(i),
l_unmatch_comp(m).component_sequence_id,
l_unmatch_comp(m).bill_sequence_id,
(SELECT concatenated_segments FROM mtl_system_items_vl MSIVL
WHERE MSIVL.inventory_item_id = l_comp_pdh_table(k).component_item_id
AND organization_id = l_org_id_table(i)),
p_batch_id,
'DELETE',
SYSDATE,
1,
(SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_unmatch_comp(m).component_item_id AND organization_id = l_org_id_table(i)),
(SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_comp_pdh_table(k).component_item_id AND organization_id = l_org_id_table(i)),
(SELECT organization_code FROM mtl_parameters WHERE organization_id = l_org_id_table(i)),
l_str_name(i),
l_comp_pdh_table(k).component_item_id,
MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
);
SELECT effectivity_date
INTO l_par_eff_date
from mtl_item_revisions
WHERE inventory_item_id = l_comp_table(j).assembly_item_id
AND organization_id = l_comp_table(j).organization_id
AND revision = l_comp_table(j).parent_revision_code;
l_comp_table(j).transaction_type := 'UPDATE';
l_comp_table(j).transaction_type := 'UPDATE';
write_debug('before inserting the delete rows first');
write_debug('inserting delete for comp --' ||l_comp_pdh_table(k).component_item_id );
INSERT INTO
bom_inventory_comps_interface
(
component_item_id,
organization_id,
component_sequence_id,
bill_sequence_id,
parent_source_system_reference,
batch_id,
transaction_type,
disable_date,
process_flag,
component_item_number,
assembly_item_number,
organization_code,
alternate_bom_designator,
transaction_id
)
VALUES
(
l_comp_pdh_table(k).component_item_id,
l_org_id_table(i),
l_comp_pdh_table(k).component_sequence_id,
l_bill_seq_id,
l_header_rec_table(i),
p_batch_id,
'DELETE',
SYSDATE,
1,
(SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id =l_comp_pdh_table(k).component_item_id AND organization_id = l_org_id_table(i)),
(SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_item_id_table(i) AND organization_id = l_org_id_table(i)),
(SELECT organization_code FROM mtl_parameters WHERE organization_id = l_org_id_table(i)),
l_str_name(i),
MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
);
INSERT INTO
bom_inventory_comps_interface
(
component_item_id,
organization_id,
component_sequence_id,
bill_sequence_id,
parent_source_system_reference,
batch_id,
transaction_type,
disable_date,
process_flag,
component_item_number,
assembly_item_number,
organization_code,
alternate_bom_designator,
assembly_item_id,
transaction_id
)
VALUES
(
l_unmatch_comp(m).component_item_id,
l_org_id_table(i),
l_unmatch_comp(m).component_sequence_id,
l_unmatch_comp(m).bill_sequence_id,
(SELECT concatenated_segments FROM mtl_system_items_vl MSIVL
WHERE MSIVL.inventory_item_id = l_comp_pdh_table(j).component_item_id
AND organization_id = l_org_id_table(i)),
p_batch_id,
'DELETE',
SYSDATE,
1,
(SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_unmatch_comp(m).component_item_id AND organization_id = l_org_id_table(i)),
(SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_comp_pdh_table(j).component_item_id AND organization_id = l_org_id_table(i)),
(SELECT organization_code FROM mtl_parameters WHERE organization_id = l_org_id_table(i)),
l_str_name(i),
l_comp_pdh_table(j).component_item_id ,
MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
);
SELECT effectivity_date
INTO l_par_eff_date
from mtl_item_revisions
WHERE inventory_item_id = l_comp_table(k).assembly_item_id
AND organization_id = l_comp_table(k).organization_id
AND revision = l_comp_table(k).parent_revision_code;
l_comp_table(k).transaction_type := 'UPDATE';
l_comp_table(k).transaction_type := 'UPDATE';
write_debug('before inserting the delete rows second');
write_debug('inserting delete for comp --' ||l_comp_pdh_table(j).component_item_id );
INSERT INTO
bom_inventory_comps_interface
(
component_item_id,
organization_id,
component_sequence_id,
bill_sequence_id,
parent_source_system_reference,
batch_id,
transaction_type,
disable_date,
process_flag,
component_item_number,
assembly_item_number,
organization_code,
alternate_bom_designator,
transaction_id
)
VALUES
(
l_comp_pdh_table(j).component_item_id,
l_org_id_table(i),
l_comp_pdh_table(j).component_sequence_id,
l_bill_seq_id,
l_header_rec_table(i),
p_batch_id,
'DELETE',
SYSDATE,
1,
(SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id =l_comp_pdh_table(j).component_item_id AND organization_id = l_org_id_table(i)),
(SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_item_id_table(i) AND organization_id = l_org_id_table(i)),
(SELECT organization_code FROM mtl_parameters WHERE organization_id = l_org_id_table(i)),
l_str_name(i),
MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
);
UPDATE bom_inventory_comps_interface
SET component_sequence_id = l_comp_table(i).component_sequence_id,
transaction_type = UPPER(l_comp_table(i).transaction_type)
WHERE batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 5)
AND UPPER(transaction_type) <> 'DELETE'
AND ( interface_table_unique_id = l_comp_table(i).interface_table_unique_id
OR component_sequence_id = l_comp_table(i).component_sequence_id
OR ( (component_item_id = l_comp_table(i).component_item_id OR component_item_number = l_comp_table(i).component_item_number)
AND (organization_id = l_comp_table(i).organization_id OR organization_code = l_comp_table(i).organization_code)
AND (assembly_item_id = l_comp_table(i).assembly_item_id OR assembly_item_number = l_comp_table(i).assembly_item_number)
)
);
SELECT
MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
INTO
L_NEXT_VALUE
FROM DUAL;
* This API will delete all the records from all the
* BOM interface tables for the given batch id.
*/
PROCEDURE Delete_Interface_Records
(
p_batch_id IN NUMBER
, x_Error_Mesg IN OUT NOCOPY VARCHAR2
, x_Ret_Code IN OUT NOCOPY VARCHAR2
)
IS
l_debug BOOLEAN := FALSE;
Write_Debug('Inside Delete Interface Records');
DELETE bom_bill_of_mtls_interface
WHERE batch_id = p_batch_id;
Delete bom_inventory_comps_interface
WHERE batch_id = p_batch_id;
DELETE bom_ref_desgs_interface
WHERE batch_id = p_batch_id;
DELETE bom_sub_comps_interface
WHERE batch_id = p_batch_id;
DELETE bom_cmp_usr_attr_interface
WHERE (batch_id = p_batch_id or data_set_id = p_batch_id);
DELETE bom_component_ops_interface
WHERE batch_id = p_batch_id;
DELETE bom_op_networks_interface
WHERE batch_id = p_batch_id;
DELETE bom_op_resources_interface
WHERE batch_id = p_batch_id;
DELETE bom_op_routings_interface
WHERE batch_id = p_batch_id;
DELETE bom_op_sequences_interface
WHERE batch_id = p_batch_id;
DELETE bom_sub_op_resources_interface
WHERE batch_id = p_batch_id;
x_Error_Mesg := 'Delete Intf Rec (' || stmt_num || ') ' || SQLERRM;
End Delete_Interface_Records;
SELECT *
FROM bom_inventory_comps_interface
WHERE batch_id = p_batch_id
AND ( component_sequence_id = l_comp_seq
OR (component_sequence_id is NULL
AND ( (component_item_number = l_comp_name OR comp_source_system_reference = l_comp_ref)
AND (assembly_item_number = l_par_name OR parent_source_system_reference = l_par_ref)
AND (operation_seq_num = l_op_seq OR new_operation_seq_num = l_new_op_seq)
AND (( effectivity_date = l_eff_date OR new_effectivity_date = l_new_eff)
OR from_end_item_unit_number = l_unit_num
OR from_end_item_rev_id = l_item_rev
)
)
)
)
AND UPPER(transaction_type) = l_txn_type
AND process_flag = 1
ORDER by interface_table_unique_id DESC;
SELECT *
FROM bom_inventory_comps_interface
WHERE batch_id = p_batch_id
AND process_flag = 1;
UPDATE bom_inventory_comps_interface
SET operation_seq_num = l_merge_comp(1).operation_seq_num,
new_operation_seq_num = l_merge_comp(1).new_operation_seq_num,
basis_type = l_merge_comp(1).basis_type,
component_quantity = l_merge_comp(1).component_quantity,
inverse_quantity = l_merge_comp(1).inverse_quantity,
component_yield_factor = l_merge_comp(1).component_yield_factor,
planning_factor = l_merge_comp(1).planning_factor,
quantity_related = l_merge_comp(1).quantity_related,
so_basis = l_merge_comp(1).so_basis,
optional = l_merge_comp(1).optional,
mutually_exclusive_options = l_merge_comp(1).mutually_exclusive_options,
include_in_cost_rollup = l_merge_comp(1).include_in_cost_rollup,
check_atp = l_merge_comp(1).check_atp,
shipping_allowed = l_merge_comp(1).shipping_allowed,
required_to_ship = l_merge_comp(1).required_to_ship,
required_for_revenue = l_merge_comp(1).required_for_revenue,
include_on_ship_docs = l_merge_comp(1).include_on_ship_docs,
low_quantity = l_merge_comp(1).low_quantity,
high_quantity = l_merge_comp(1).high_quantity,
acd_type = l_merge_comp(1).acd_type ,
wip_supply_type = l_merge_comp(1).wip_supply_type,
supply_subinventory = l_merge_comp(1).supply_subinventory,
supply_locator_id = l_merge_comp(1).supply_locator_id,
location_name = l_merge_comp(1).location_name,
bom_item_type = l_merge_comp(1).bom_item_type,
operation_lead_time_percent = l_merge_comp(1).operation_lead_time_percent,
cost_factor = l_merge_comp(1).cost_factor,
include_on_bill_docs = l_merge_comp(1).include_on_bill_docs,
pick_components = l_merge_comp(1).pick_components,
original_system_reference = l_merge_comp(1).original_system_reference,
enforce_int_requirements = l_merge_comp(1).enforce_int_requirements,
optional_on_model = l_merge_comp(1).optional_on_model,
auto_request_material = l_merge_comp(1).auto_request_material,
suggested_vendor_name = l_merge_comp(1).suggested_vendor_name,
unit_price = l_merge_comp(1).unit_price
WHERE batch_id = p_batch_id
AND interface_table_unique_id = l_merge_comp(1).interface_table_unique_id;
UPDATE bom_inventory_comps_interface
SET process_flag = -1
WHERE batch_id = p_batch_id
AND ( component_sequence_id = l_merge_comp(1).component_sequence_id
OR ( component_sequence_id IS NULL
AND (component_item_number = l_merge_comp(1).component_item_number OR comp_source_system_reference = l_merge_comp(1).comp_source_system_reference)
AND (assembly_item_number = l_merge_comp(1).assembly_item_number OR parent_source_system_reference = l_merge_comp(1).parent_source_system_reference)
AND (operation_seq_num = l_merge_comp(1).operation_seq_num OR new_operation_seq_num = l_merge_comp(1).new_operation_seq_num)
AND (( effectivity_date = l_merge_comp(1).effectivity_date OR new_effectivity_date = l_merge_comp(1).new_effectivity_date)
OR from_end_item_unit_number = l_merge_comp(1).from_end_item_unit_number
OR from_end_item_rev_id = l_merge_comp(1).from_end_item_rev_id
)
)
)
AND interface_table_unique_id <> l_merge_comp(1).interface_table_unique_id;
SELECT COMPONENT_REFERENCE_DESIGNATOR,MAX(interface_table_unique_id)
FROM bom_ref_desgs_interface
where batch_id = p_batch_id
and process_flag = 1
and ( component_sequence_id = p_comp_seq_id
OR ( (component_item_number = p_comp_name OR comp_source_system_reference = p_comp_ref)
and (nvl(operation_seq_num,1) = nvl(p_op_seq,1) OR nvl(operation_seq_num,1) = nvl(p_new_op_seq,1) )
and (( nvl(effectivity_date,sysdate) = nvl(p_effec_date,sysdate) OR nvl(effectivity_date,sysdate) = nvl(p_new_effec_date,sysdate))
or from_end_item_unit_number = p_from_unit
--or from_end_item_rev_id = p_from_item_id
)
and (assembly_item_number = p_parent_name OR parent_source_system_reference = p_parent_ref)
)
)
GROUP BY component_reference_designator;
UPDATE bom_ref_desgs_interface
SET process_flag = -1
WHERE batch_id = p_batch_id
AND ( process_flag = 1 OR process_flag = 5)
AND ( component_sequence_id = p_comp_seq_id
OR ( (component_item_number = p_comp_name OR comp_source_system_reference = p_comp_ref)
AND (nvl(operation_seq_num,1) = nvl(p_op_seq,1) OR nvl(operation_seq_num,1) = nvl(p_new_op_seq,1) )
AND (( nvl(effectivity_date,sysdate) = nvl(p_effec_date,sysdate) OR nvl(effectivity_date,sysdate) = nvl(p_new_effec_date,sysdate))
or from_end_item_unit_number = p_from_unit
--or from_end_item_rev_id = p_from_item_id
)
AND (assembly_item_number = p_parent_name OR parent_source_system_reference = p_parent_ref)
)
)
AND component_reference_designator = l_comp_ref_des(i)
AND interface_table_unique_id <> l_max_unique_id(i);
SELECT *
FROM bom_cmp_usr_attr_interface
WHERE (data_set_id = p_batch_id OR batch_id = p_batch_id)
AND ( component_sequence_id = p_comp_seq
OR( (item_number = p_comp_name or comp_source_system_reference = p_comp_ref)
AND (assembly_item_number = p_par_name OR parent_source_system_reference = p_par_ref)
)
)
AND process_status = 0
AND (organization_id = p_org_id OR organization_code = p_org_code);
SELECT *
FROM bom_cmp_usr_attr_interface
WHERE (data_set_id = p_batch_id OR batch_id = p_batch_id)
AND ( component_sequence_id = p_comp_seq
OR ( (item_number = p_comp_name or comp_source_system_reference = p_comp_ref)
AND (assembly_item_number = p_par_name OR parent_source_system_reference = p_par_ref)
)
)
--AND transaction_id = p_txn_id
ANd process_status = 0
AND (organization_id = p_org_id OR organization_code = p_org_code)
AND attr_group_int_name = l_grp_int_name
AND attr_int_name = l_attr_int_name
AND structure_type_id = l_str_type_id
ORDER BY interface_table_unique_id DESC;
SELECT multi_row_code
INTO l_multi_row
FROM ego_attr_groups_v
WHERE attr_group_name = l_attr_table(i).ATTR_GROUP_INT_NAME
AND attr_group_type = 'BOM_COMPONENTMGMT_GROUP';
UPDATE bom_cmp_usr_attr_interface
SET attr_value_str = l_merge_table(1).attr_value_str,
attr_value_num = l_merge_table(1).attr_value_num,
attr_value_date = l_merge_table(1).attr_value_date,
attr_disp_value = l_merge_table(1).attr_disp_value
WHERE (data_set_id = p_batch_id OR batch_id = p_batch_id)
AND interface_table_unique_id = l_merge_table(1).interface_table_unique_id;
UPDATE bom_cmp_usr_attr_interface
SET process_status = -1
WHERE (batch_id = p_batch_id or data_set_id = p_batch_id)
AND ( component_sequence_id = l_merge_table(1).component_sequence_id
OR ( (item_number = l_merge_table(1).item_number or comp_source_system_reference = l_merge_table(1).comp_source_system_reference)
AND (assembly_item_number = l_merge_table(1).assembly_item_number OR parent_source_system_reference = l_merge_table(1).parent_source_system_reference )
)
)
--AND transaction_id = l_merge_table(1).transaction_id
AND attr_group_int_name = l_merge_table(1).attr_group_int_name
AND attr_int_name = l_merge_table(1).attr_int_name
AND structure_type_id = l_merge_table(1).structure_type_id
AND interface_table_unique_id <> l_merge_table(1).interface_table_unique_id;
SELECT *
FROM bom_bill_of_mtls_interface
WHERE batch_id = p_batch_id
AND process_flag = 1;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 5
--pending_from_ecn = nvl(pending_from_ecn,pG_batch_options.CHANGE_NOTICE) we need not do this
WHERE batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 7);
UPDATE bom_inventory_comps_interface
SET process_flag = 5
--change_notice = nvl(change_notice,pG_batch_options.CHANGE_NOTICE) we need not do this
WHERE batch_id = p_batch_id
AND process_flag = 1;
UPDATE bom_ref_desgs_interface
SET process_flag = 5
WHERE batch_id = p_batch_id
AND process_flag = 1;
UPDATE bom_sub_comps_interface
SET process_flag = 5
WHERE batch_id = p_batch_id
AND process_flag = 1;
UPDATE bom_component_ops_interface
SET process_flag = 5
WHERE batch_id = p_batch_id
AND process_flag = 1;
SELECT mrb.revision_id
INTO l_rev_id
FROM mtl_item_revisions_b mrb
WHERE mrb.inventory_item_id = l_header_table(i).assembly_item_id
AND mrb.organization_id = l_header_table(i).organization_id
AND mrb.revision = l_header_table(i).revision;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 5
WHERE batch_id = p_batch_id
AND (process_flag = 1 OR process_flag = 7)
AND interface_table_unique_id = l_header_table(i).interface_table_unique_id;
UPDATE bom_inventory_comps_interface
SET process_flag = 5
WHERE batch_id = p_batch_id
AND process_flag = 1
AND ( bill_sequence_id = l_header_table(i).bill_sequence_id
OR ( assembly_item_id = l_header_table(i).assembly_item_id
AND organization_id = l_header_table(i).organization_id
AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
)
OR ( assembly_item_number = l_header_table(i).item_number
AND organization_code = l_header_table(i).organization_code
AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
)
);
UPDATE bom_ref_desgs_interface
SET process_flag = 5
WHERE batch_id = p_batch_id
AND process_flag = 1
AND ( bill_sequence_id = l_header_table(i).bill_sequence_id
OR ( assembly_item_id = l_header_table(i).assembly_item_id
AND organization_id = l_header_table(i).organization_id
AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
)
OR ( assembly_item_number = l_header_table(i).item_number
AND organization_code = l_header_table(i).organization_code
AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
)
);
UPDATE bom_sub_comps_interface
SET process_flag = 5
WHERE batch_id = p_batch_id
AND process_flag = 1
AND ( bill_sequence_id = l_header_table(i).bill_sequence_id
OR ( assembly_item_id = l_header_table(i).assembly_item_id
AND organization_id = l_header_table(i).organization_id
AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
)
OR ( assembly_item_number = l_header_table(i).item_number
AND organization_code = l_header_table(i).organization_code
AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
)
);
UPDATE bom_component_ops_interface
SET process_flag = 5
WHERE batch_id = p_batch_id
AND process_flag = 1
AND ( bill_sequence_id = l_header_table(i).bill_sequence_id
OR ( assembly_item_id = l_header_table(i).assembly_item_id
AND organization_id = l_header_table(i).organization_id
AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
)
OR ( assembly_item_number = l_header_table(i).item_number
AND organization_code = l_header_table(i).organization_code
AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
)
);
UPDATE bom_cmp_usr_attr_interface
SET process_status = 5
WHERE batch_id = p_batch_id
AND process_status= 1
AND ( bill_sequence_id = l_header_table(i).bill_sequence_id
OR ( assembly_item_number = l_header_table(i).item_number
AND organization_code = l_header_table(i).organization_code
--AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
)
);
SELECT PK1_COLUMN_NAME
INTO l_pk_column
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM';
SELECT match_id
FROM ego_item_matches
WHERE batch_id = p_batch_id
AND source_system_reference = p_ss_ref;