[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
COUNT(RESULTFMT_USAGE_ID) into l_unprocessed_rowcount
FROM
EGO_BULKLOAD_INTF E
where
E.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
and E.PROCESS_STATUS = 1;
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
SELECT Bill_Sequence_Id
INTO l_id
FROM Bom_Bill_Of_Materials
WHERE Assembly_Item_Id = p_assembly_item_id
AND NVL(Alternate_Bom_Designator, 'NONE') =
DECODE(p_alternate_bom_designator,NULL,'NONE',p_alternate_bom_designator)
AND Organization_Id = p_organization_id;
SELECT Organization_Id
INTO l_id
FROM Mtl_Parameters
WHERE Organization_Code = p_organization;
UPDATE EGO_BULKLOAD_INTF EBI
SET EBI.PROCESS_STATUS =
(
SELECT BMI.PROCESS_FLAG
FROM BOM_BILL_OF_MTLS_INTERFACE BMI
WHERE BMI.TRANSACTION_ID = EBI.TRANSACTION_ID
)
WHERE EXISTS
(
SELECT 'X'
FROM BOM_BILL_OF_MTLS_INTERFACE BMI
WHERE BMI.TRANSACTION_ID = EBI.TRANSACTION_ID
)
AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
UPDATE EGO_BULKLOAD_INTF EBI
SET EBI.PROCESS_STATUS =
(
SELECT BICI.PROCESS_FLAG
FROM BOM_INVENTORY_COMPS_INTERFACE BICI
WHERE BICI.TRANSACTION_ID = EBI.TRANSACTION_ID
)
WHERE EXISTS
(
SELECT 'X'
FROM BOM_INVENTORY_COMPS_INTERFACE BICI
WHERE BICI.TRANSACTION_ID = EBI.TRANSACTION_ID
)
AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
l_dyn_sql_select VARCHAR2(10000);
l_dyn_sql_insert VARCHAR2(10000);
l_cursor_select INTEGER;
G_TXN_UPDATE VARCHAR2(10) := 'UPDATE';
G_TXN_DELETE VARCHAR2(10) := 'DELETE';
G_DEL_GROUP_DESC VARCHAR2(240) := 'Delete Group for EGO BOM Bulkload Structures';
SELECT
erf.Attribute_Code,
erf.Intf_Column_Name,
bcc.Bom_Intf_Column_Name ,
bcc.Bom_Intf_Table_Name,
bcc.Operation_Type,
bcc.Lookup_Type
FROM
Ego_Results_Fmt_Usages erf,
Bom_Component_Columns bcc
WHERE
(Region_Code = 'BOM_RESULT_DUMMY_REGION'
OR -- Fix for import Region
Region_Code = 'BOM_IMPORT_DUMMY_REGION' )
AND
Region_Application_Id = 702
AND
Customization_Application_Id = 431
AND
Resultfmt_Usage_Id = c_Resultfmt_Usage_Id
AND
bcc.Attribute_Code = erf.Attribute_Code
AND
bcc.OBJECT_TYPE = 'BOM_COMPONENTS'
AND
bcc.BOM_INTF_TABLE_NAME = 'BOM_INVENTORY_COMPS_INTERFACE'
AND
( bcc.Parent_Entity IS NULL
OR BCC.Attribute_Code = 'ITEM_NUMBER'
OR BCC.Attribute_Code = 'PARENT_NAME')
AND
erf.Attribute_Code NOT LIKE '%$$%';
SELECT
ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ASSEMBLY_TYPE,
PROCESS_FLAG,
ORGANIZATION_CODE,
COMMON_ORG_CODE,
ITEM_NUMBER,
IMPLEMENTATION_DATE
FROM
BOM_BILL_OF_MTLS_INTERFACE BMI
WHERE
PROCESS_FLAG = 1
AND
REQUEST_ID = C_REQUEST_ID
AND
BMI.Alternate_Bom_Designator IS NOT NULL
AND NOT EXISTS
(SELECT NULL FROM Bom_structures_b bsb
WHERE bsb.Assembly_Item_id IN (SELECT Inventory_item_id FROM mtl_system_items_vl WHERE concatenated_segments = bmi.Item_number)
AND bsb.Organization_id = (SELECT ORGANIZATION_ID FROM mtl_parameters WHERE ORGANIZATION_code = bmi.ORGANIZATION_code)
AND bsb.Alternate_Bom_Designator IS NULL
);
DELETE FROM EGO_BULKLOAD_INTF
WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
AND PROCESS_STATUS <> 1;
Select INTF_COLUMN_NAME into G_INTF_ORG_CODE FROM Ego_Results_Fmt_Usages erf
WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'ORGANIZATION_CODE';
Select INTF_COLUMN_NAME into G_INTF_COMP_SEQ_ID FROM Ego_Results_Fmt_Usages erf
WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'COMPONENT_SEQUENCE_ID';
Select INTF_COLUMN_NAME into G_INTF_ASSEMBLY_TYPE FROM Ego_Results_Fmt_Usages erf
WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'SUB_ASSEMBLY_TYPE';
Select INTF_COLUMN_NAME into G_INTF_PARENT_REVISION FROM Ego_Results_Fmt_Usages erf
WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'PARENT_REVISION_CODE';
Select INTF_COLUMN_NAME into G_INTF_REVISION FROM Ego_Results_Fmt_Usages erf
WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'REVISION';
Select INTF_COLUMN_NAME into G_INTF_REF_DESIG FROM Ego_Results_Fmt_Usages erf
WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'COMPONENT_REFERENCE_DESIGNATOR';
UPDATE EGO_BULKLOAD_INTF
SET Transaction_Id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND PROCESS_STATUS = 1 ;
l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
l_dyn_sql := l_dyn_sql || ' SELECT ORGANIZATION_ID ';
l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
l_dyn_sql := l_dyn_sql || ' SELECT inventory_item_id ';
l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
l_dyn_sql := l_dyn_sql || ' SELECT inventory_item_id ';
l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
l_dyn_sql := l_dyn_sql || ' SELECT bill_sequence_id ';
l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI SET';
l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
l_dyn_sql := l_dyn_sql || ' SELECT COMPONENT_SEQUENCE_ID ';
l_dyn_sql_insert := '';
l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (BATCH_ID';
l_dyn_sql_insert := l_dyn_sql_insert || ' , SOURCE_SYSTEM_REFERENCE ';
l_dyn_sql_insert := l_dyn_sql_insert || ' , SOURCE_SYSTEM_REFERENCE_DESC ';
l_dyn_sql_insert := l_dyn_sql_insert || ' , REQUEST_ID, Transaction_Type ';
l_dyn_sql_insert := l_dyn_sql_insert || ' , Transaction_Id, Process_Flag, Item_Number ';
l_dyn_sql_insert := l_dyn_sql_insert || ' , Organization_Code, Alternate_Bom_Designator, Structure_Type_Name, Effectivity_Control, Is_Preferred, assembly_type, Revision) ';
l_dyn_sql_select := '';
l_dyn_sql_select := l_dyn_sql_select || ' SELECT ' || P_BATCH_ID || ', C_FIX_COLUMN12, C_FIX_COLUMN13 ';
l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, Transaction_Type, Transaction_Id, 1, ' || l_item_col_name;
l_dyn_sql_select := l_dyn_sql_select || ' , ' || G_INTF_ORG_CODE;
l_dyn_sql_select := l_dyn_sql_select || ' , DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
|| ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
||' :1 ),'|| G_INTF_STRUCT_NAME|| ')'
|| ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
|| 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
||' :2 ),'|| G_INTF_STRUCT_NAME|| ')' || ')';
l_dyn_sql_select := l_dyn_sql_select || ' , ' || 'DECODE(' || G_INTF_STR_TYPE_NAME
||',null,(SELECT BSTV.structure_type_name from bom_structure_types_vl BSTV,ego_import_option_sets EIOS WHERE EIOS.batch_id = '
|| ' :3 AND BSTV.structure_type_id = EIOS.structure_type_id ),' || G_INTF_STR_TYPE_NAME || ' )';
l_dyn_sql_select := l_dyn_sql_select || ' , (SELECT Lookup_Code FROM Fnd_Lookup_Values WHERE Lookup_Type = ''BOM_EFFECTIVITY_CONTROL'' AND LANGUAGE=USERENV(''LANG'') AND Meaning=' || G_INTF_EFFEC_CONTROL || ')';
l_dyn_sql_select := l_dyn_sql_select || ' , (SELECT Lookup_Code FROM Fnd_Lookup_Values WHERE Lookup_Type = ''EGO_YES_NO'' AND LANGUAGE=USERENV(''LANG'') AND Meaning = ' || G_INTF_IS_PREFERRED || ')';
l_dyn_sql_select := l_dyn_sql_select || ' , (SELECT Lookup_Code FROM Mfg_Lookups WHERE Lookup_Type = ''BOM_ASSEMBLY_TYPE'' AND Meaning=' || G_INTF_ASSEMBLY_TYPE || ')';
l_dyn_sql_select := l_dyn_sql_select || ' , ' || G_INTF_REVISION || ' ';
l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF WHERE RESULTFMT_USAGE_ID = :4 ';
l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NULL ';
l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ' || G_INTF_SRCSYS_PARENT || ' IS NULL ';
l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
l_dyn_sql_cursor := l_dyn_sql_cursor || ' SELECT Distinct ' ;
|| ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
|| ' :1 ),'|| G_INTF_STRUCT_NAME|| ')' || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
|| 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
||' :2 ),'|| G_INTF_STRUCT_NAME|| ')'
|| ')';
||',null,(SELECT BSTV.structure_type_name from bom_structure_types_vl BSTV,ego_import_option_sets EIOS WHERE EIOS.batch_id = '
|| ' :3 AND BSTV.structure_type_id = EIOS.structure_type_id ),' || G_INTF_STR_TYPE_NAME || ' )';
l_cursor_select := Dbms_Sql.Open_Cursor;
Dbms_Sql.Parse(l_cursor_select, l_dyn_sql_cursor, Dbms_Sql.NATIVE);
Dbms_Sql.Define_Column(l_cursor_select, 1, L_SRCSYS_PARENT,3000);
Dbms_Sql.Define_Column(l_cursor_select, 2, L_PARENT_NAME, 3000);
Dbms_Sql.Define_Column(l_cursor_select, 3, L_ORGANIZATION_CODE, 10);
Dbms_Sql.Define_Column(l_cursor_select, 4, L_STRUCTURE_NAME, 240);
Dbms_Sql.Define_Column(l_cursor_select, 5, L_STR_TYPE_NAME, 240);
Dbms_Sql.Define_Column(l_cursor_select, 6, L_EFFEC_CONTROL, 240);
Dbms_Sql.Define_Column(l_cursor_select, 7, L_IS_PREF_MEANING, 80);
Dbms_Sql.Define_Column(l_cursor_select, 8, L_ASSTYPE_MEANING, 80);
Dbms_Sql.Define_Column(l_cursor_select, 9, L_PARENT_REVISION, 80);
Dbms_Sql.Define_Column(l_cursor_select, 10, L_TRANSACTION_ID);
Dbms_Sql.Bind_Variable(l_cursor_select,':1', p_batch_id);
Dbms_Sql.Bind_Variable(l_cursor_select,':2', p_batch_id);
Dbms_Sql.Bind_Variable(l_cursor_select,':3', p_batch_id);
Dbms_Sql.Bind_Variable(l_cursor_select,':4', p_resultfmt_usage_id);
l_cursor_execute := Dbms_Sql.EXECUTE(l_cursor_select);
IF (Dbms_Sql.Fetch_Rows(l_cursor_select) > 0) THEN
Dbms_Sql.Column_Value(l_cursor_select,1,L_SRCSYS_PARENT);
Dbms_Sql.Column_Value(l_cursor_select,2,L_PARENT_NAME);
Dbms_Sql.Column_Value(l_cursor_select,3,L_ORGANIZATION_CODE);
Dbms_Sql.Column_Value(l_cursor_select,4,L_STRUCTURE_NAME);
Dbms_Sql.Column_Value(l_cursor_select,5,L_STR_TYPE_NAME);
Dbms_Sql.Column_Value(l_cursor_select,6,L_EFFEC_CONTROL);
Dbms_Sql.Column_Value(l_cursor_select,7,L_IS_PREF_MEANING);
Dbms_Sql.Column_Value(l_cursor_select,8,L_ASSTYPE_MEANING);
Dbms_Sql.Column_Value(l_cursor_select,9,L_PARENT_REVISION);
Dbms_Sql.Column_Value(l_cursor_select,10,L_TRANSACTION_ID);
SELECT
Lookup_Code
INTO
l_eff_ctrl
FROM
Fnd_Lookup_Values
WHERE
Lookup_Type = 'BOM_EFFECTIVITY_CONTROL' AND LANGUAGE=USERENV('LANG') AND Meaning=L_EFFEC_CONTROL;
SELECT
Lookup_Code
INTO
l_is_preferred
FROM
Fnd_Lookup_Values
WHERE
Lookup_Type = 'EGO_YES_NO' AND LANGUAGE=USERENV('LANG') AND Meaning=L_IS_PREF_MEANING;
SELECT
Lookup_Code
INTO
l_assemblytype
FROM
Mfg_Lookups
WHERE
Lookup_Type = 'BOM_ASSEMBLY_TYPE' AND Meaning=L_ASSTYPE_MEANING;
INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (
BATCH_ID,
REQUEST_ID,
TRANSACTION_TYPE,
TRANSACTION_ID,
PROCESS_FLAG,
ITEM_NUMBER,
ORGANIZATION_CODE,
ALTERNATE_BOM_DESIGNATOR,
STRUCTURE_TYPE_NAME,
EFFECTIVITY_CONTROL,
IS_PREFERRED ,
assembly_type,
REVISION,
SOURCE_SYSTEM_REFERENCE )
select
p_batch_id,
G_REQUEST_ID,
G_TXN_SYNC,
L_TRANSACTION_ID,
1,
L_PARENT_NAME,
L_ORGANIZATION_CODE,
L_STRUCTURE_NAME,
L_STR_TYPE_NAME,
l_eff_ctrl,
l_is_preferred,
l_assemblytype,
L_PARENT_REVISION,
L_SRCSYS_PARENT from dual
where not exists
(select 'X' from bom_bill_of_mtls_interface
where
batch_id = p_batch_id
and request_id = g_request_id
and process_flag = 1
and ( (ITEM_NUMBER IS NOT NULL AND ITEM_NUMBER = L_PARENT_NAME) OR (SOURCE_SYSTEM_REFERENCE IS NOT NULL AND SOURCE_SYSTEM_REFERENCE=L_SRCSYS_PARENT))
and ORGANIZATION_CODE = L_ORGANIZATION_CODE
and nvl(ALTERNATE_BOM_DESIGNATOR,'000') = nvl(L_STRUCTURE_NAME,'000')
and nvl(STRUCTURE_TYPE_NAME,'000') = nvl(L_STR_TYPE_NAME,'000')
and nvl(EFFECTIVITY_CONTROL,'000') = nvl(l_eff_ctrl,'000') );
INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (
BATCH_ID,
REQUEST_ID,
TRANSACTION_TYPE,
TRANSACTION_ID,
PROCESS_FLAG,
ITEM_NUMBER,
ORGANIZATION_CODE,
ALTERNATE_BOM_DESIGNATOR,
STRUCTURE_TYPE_NAME,
EFFECTIVITY_CONTROL,
IS_PREFERRED ,
assembly_type,
REVISION,
SOURCE_SYSTEM_REFERENCE )
select
p_batch_id,
G_REQUEST_ID,
G_TXN_NO_OP,
L_TRANSACTION_ID,
1,
L_PARENT_NAME,
L_ORGANIZATION_CODE,
L_STRUCTURE_NAME,
L_STR_TYPE_NAME,
l_eff_ctrl,
l_is_preferred,
l_assemblytype,
L_PARENT_REVISION,
L_SRCSYS_PARENT from dual
where not exists
(select 'X' from bom_bill_of_mtls_interface
where
batch_id = p_batch_id
and request_id = g_request_id
and process_flag = 1
and ( (ITEM_NUMBER IS NOT NULL AND ITEM_NUMBER = L_PARENT_NAME) OR (SOURCE_SYSTEM_REFERENCE IS NOT NULL AND SOURCE_SYSTEM_REFERENCE=L_SRCSYS_PARENT))
and ORGANIZATION_CODE = L_ORGANIZATION_CODE
and nvl(ALTERNATE_BOM_DESIGNATOR,'000') = nvl(L_STRUCTURE_NAME,'000')
and nvl(STRUCTURE_TYPE_NAME,'000') = nvl(L_STR_TYPE_NAME,'000')
and nvl(EFFECTIVITY_CONTROL,'000') = nvl(l_eff_ctrl,'000') );
l_dyn_sql_insert := '';
l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( BATCH_ID, ';
l_dyn_sql_insert := l_dyn_sql_insert || ' COMP_SOURCE_SYSTEM_REFERENCE, COMP_SOURCE_SYSTEM_REFER_DESC,';
l_dyn_sql_insert := l_dyn_sql_insert || ' PARENT_SOURCE_SYSTEM_REFERENCE, REQUEST_ID, Transaction_Type,';
l_dyn_sql_insert := l_dyn_sql_insert || ' Transaction_Id, Process_Flag, ';
l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, PARENT_REVISION_CODE, ';
l_dyn_sql_select := '';
l_dyn_sql_select := l_dyn_sql_select || 'SELECT ' || P_BATCH_ID || ' , ' || G_INTF_SRCSYS_COMPONENT ;
l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_SRCSYS_DESCRIPTION || ' , ' || G_INTF_SRCSYS_PARENT ;
l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, TRANSACTION_TYPE, Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
l_dyn_sql_select := l_dyn_sql_select || ' DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
|| ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
|| ' :1 ),'|| G_INTF_STRUCT_NAME|| ')' || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
|| 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
||' :2 ),'|| G_INTF_STRUCT_NAME|| ')) ,';
l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ' || G_INTF_PARENT_REVISION || ', ';
l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_EFFECTIVITY_DATE,';
l_str := 'DECODE(( SELECT To_Char(Effectivity_Date,''DD-MON-YYYY HH24:MI:SS'') FROM Bom_inventory_Components WHERE Component_Sequence_Id = ' || G_INTF_COMP_SEQ_ID || ')';
l_dyn_sql_select := l_dyn_sql_select || l_str;
l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_OPERATION_SEQ_NUM,';
l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_FROM_END_ITEM_UNIT_NUMBER,';
l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
l_str := '(SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE ';
l_dyn_sql_select := l_dyn_sql_select || l_str;
l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :3 ';
l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ';
l_dyn_sql_select := l_dyn_sql_select || l_parent_column || ' IS NOT NULL ';
l_dyn_sql_select := l_dyn_sql_select || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ';
l_dyn_sql_select := l_dyn_sql_select || ' AND ( UPPER(Transaction_Type) = ''' || G_TXN_UPDATE || ''' OR UPPER(Transaction_Type) = ''' || G_TXN_SYNC || ''') ';
l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
Dbms_Sql.Close_Cursor(l_cursor_select);
l_dyn_sql_insert := '';
l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( BATCH_ID, ';
l_dyn_sql_insert := l_dyn_sql_insert || ' COMP_SOURCE_SYSTEM_REFERENCE, COMP_SOURCE_SYSTEM_REFER_DESC,';
l_dyn_sql_insert := l_dyn_sql_insert || ' PARENT_SOURCE_SYSTEM_REFERENCE, REQUEST_ID, Transaction_Type,';
l_dyn_sql_insert := l_dyn_sql_insert || ' Transaction_Id, Process_Flag, ';
l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, PARENT_REVISION_CODE, ';
l_dyn_sql_select := '';
l_dyn_sql_select := l_dyn_sql_select || 'SELECT ' || P_BATCH_ID || ' , ' || G_INTF_SRCSYS_COMPONENT ;
l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_SRCSYS_DESCRIPTION || ' , ' || G_INTF_SRCSYS_PARENT ;
l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, ''' || G_TXN_CREATE || ''' , Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
l_dyn_sql_select := l_dyn_sql_select || ' DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
|| ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
|| ' :1 ),'|| G_INTF_STRUCT_NAME|| ')' || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
|| 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
||' :2 ),'|| G_INTF_STRUCT_NAME|| ')) ,';
l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ' || G_INTF_PARENT_REVISION || ', ';
l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
l_str := '(SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE ';
l_dyn_sql_select := l_dyn_sql_select || l_str;
l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :3 ';
l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ';
l_dyn_sql_select := l_dyn_sql_select || l_parent_column || ' IS NOT NULL ';
l_dyn_sql_select := l_dyn_sql_select || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ';
l_dyn_sql_select := l_dyn_sql_select || ' AND ( UPPER(Transaction_Type) = ''' || G_TXN_CREATE || ''' OR UPPER(Transaction_Type) = ''' || G_TXN_ADD || ''') ';
l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
l_dyn_sql_insert := '';
l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( BATCH_ID, ';
l_dyn_sql_insert := l_dyn_sql_insert || ' COMP_SOURCE_SYSTEM_REFERENCE, COMP_SOURCE_SYSTEM_REFER_DESC,';
l_dyn_sql_insert := l_dyn_sql_insert || ' PARENT_SOURCE_SYSTEM_REFERENCE, REQUEST_ID, Transaction_Type,';
l_dyn_sql_insert := l_dyn_sql_insert || ' Transaction_Id, Process_Flag, ';
l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, ';
l_dyn_sql_insert := l_dyn_sql_insert || 'DELETE_GROUP_NAME, DG_DESCRIPTION, ';
l_dyn_sql_select := '';
l_dyn_sql_select := l_dyn_sql_select || 'SELECT ' || P_BATCH_ID || ' , ' || G_INTF_SRCSYS_COMPONENT ;
l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_SRCSYS_DESCRIPTION || ' , ' || G_INTF_SRCSYS_PARENT ;
l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, TRANSACTION_TYPE, Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
l_dyn_sql_select := l_dyn_sql_select || ' DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
|| ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
|| ' :1 ),'|| G_INTF_STRUCT_NAME|| ')' || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
|| 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
||' :2 ),'|| G_INTF_STRUCT_NAME|| ')) ,';
l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ';
l_dyn_sql_select := l_dyn_sql_select || '''' || G_DEL_GROUP_NAME || ''', ''' || G_DEL_GROUP_DESC || ''', ' ;
l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
l_str := '(SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE ';
l_dyn_sql_select := l_dyn_sql_select || l_str;
l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :3 ';
l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ';
l_dyn_sql_select := l_dyn_sql_select || l_parent_column || ' IS NOT NULL ';
l_dyn_sql_select := l_dyn_sql_select || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ';
l_dyn_sql_select := l_dyn_sql_select || ' AND UPPER(Transaction_Type) = ''' || G_TXN_DELETE || ''' ';
l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
FND_FILE.PUT_LINE( FND_FILE.LOG,'Before UPdate of AssemblyType');
UPDATE BOM_BILL_OF_MTLS_INTERFACE SET assembly_type = 2
WHERE assembly_type IS NULL
AND batch_id = p_batch_id;
FND_FILE.PUT_LINE( FND_FILE.LOG,'After UPdate of AssemblyType');
INSERT INTO BOM_BILL_OF_MTLS_INTERFACE
(
ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ASSEMBLY_TYPE,
PROCESS_FLAG,
ORGANIZATION_CODE,
COMMON_ORG_CODE,
ITEM_NUMBER,
IMPLEMENTATION_DATE,
ALTERNATE_BOM_DESIGNATOR,
TRANSACTION_TYPE,
REQUEST_ID)
VALUES
(
C_BOM_BILL_PRIMARY_REC.ASSEMBLY_ITEM_ID,
C_BOM_BILL_PRIMARY_REC.ORGANIZATION_ID,
C_BOM_BILL_PRIMARY_REC.ASSEMBLY_TYPE,
C_BOM_BILL_PRIMARY_REC.PROCESS_FLAG,
C_BOM_BILL_PRIMARY_REC.ORGANIZATION_CODE,
C_BOM_BILL_PRIMARY_REC.COMMON_ORG_CODE,
C_BOM_BILL_PRIMARY_REC.ITEM_NUMBER,
C_BOM_BILL_PRIMARY_REC.IMPLEMENTATION_DATE,
NULL,
G_TXN_CREATE,
G_REQUEST_ID);
UPDATE BOM_BILL_OF_MTLS_INTERFACE
SET Transaction_Id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
WHERE REQUEST_ID = G_REQUEST_ID AND PROCESS_FLAG = 1
AND Transaction_Id IS NULL;
l_upd_sql := ' UPDATE EGO_BULKLOAD_INTF EBI1 ' ||
' SET EBI1.transaction_id = ( SELECT EBI2.transaction_id ' ||
' FROM EGO_BULKLOAD_INTF EBI2 ' ||
' WHERE EBI2.resultfmt_usage_id = EBI1.resultfmt_usage_id AND EBI2.process_status = 1 ' ||
' AND EBI2.' || l_item_seq_col_name || ' IS NOT NULL' ;
' AND EBI1.' || l_parent_column || ' = (SELECT EBI3.' || l_parent_column ||
' FROM EGO_BULKLOAD_INTF EBI3 ' ||
' WHERE EBI3.resultfmt_usage_id = EBI1.resultfmt_usage_id AND EBI3.process_status = 1 ' ||
' AND EBI3.' || l_item_seq_col_name || ' IS NOT NULL' ||
' AND EBI3.' || l_parent_column || ' = EBI1.' || l_parent_column ||
' AND EBI3.' || l_item_col_name || ' = EBI1.' || l_item_col_name ||
' ) ' ||
' AND EBI1.' || l_item_col_name || ' IS NOT NULL ' ||
' AND EBI1.' || l_item_col_name || ' = (SELECT EBI4.' || l_item_col_name ||
' FROM EGO_BULKLOAD_INTF EBI4 ' ||
' WHERE EBI4.resultfmt_usage_id = EBI1.resultfmt_usage_id AND EBI4.process_status = 1 ' ||
' AND EBI4.' || l_item_seq_col_name || ' IS NOT NULL' ||
' AND EBI4.' || l_item_col_name || ' = EBI1.' || l_item_col_name ||
' AND EBI4.' || l_parent_column || ' = EBI1.' || l_parent_column ||
' ) ';
' AND EBI1.' || G_INTF_SRCSYS_PARENT || ' = (SELECT EBI3.' || G_INTF_SRCSYS_PARENT ||
' FROM EGO_BULKLOAD_INTF EBI3 ' ||
' WHERE EBI3.resultfmt_usage_id = EBI1.resultfmt_usage_id AND EBI3.process_status = 1 ' ||
' AND EBI3.' || l_item_seq_col_name || ' IS NOT NULL' ||
' AND EBI3.' || G_INTF_SRCSYS_PARENT || ' = EBI1.' || G_INTF_SRCSYS_PARENT ||
' AND EBI3.' || G_INTF_SRCSYS_COMPONENT || ' = EBI1.' || G_INTF_SRCSYS_COMPONENT ||
' ) ' ||
' AND EBI1.' || G_INTF_SRCSYS_COMPONENT || ' IS NOT NULL ' ||
' AND EBI1.' || G_INTF_SRCSYS_COMPONENT || ' = (SELECT EBI4.' || G_INTF_SRCSYS_COMPONENT ||
' FROM EGO_BULKLOAD_INTF EBI4 ' ||
' WHERE EBI4.resultfmt_usage_id = EBI1.resultfmt_usage_id AND EBI4.process_status = 1 ' ||
' AND EBI4.' || l_item_seq_col_name || ' IS NOT NULL' ||
' AND EBI4.' || G_INTF_SRCSYS_COMPONENT || ' = EBI1.' || G_INTF_SRCSYS_COMPONENT ||
' AND EBI4.' || G_INTF_SRCSYS_PARENT || ' = EBI1.' || G_INTF_SRCSYS_PARENT ||
' ) ';
FND_FILE.PUT_LINE( FND_FILE.LOG,'Update Sql for ego bulkload for Multi Row-->' || l_upd_sql);
DELETE BOM_INVENTORY_COMPS_INTERFACE
WHERE PROCESS_FLAG = 1
AND batch_id = p_batch_id
AND (OPERATION_SEQ_NUM IS NOT NULL OR NEW_OPERATION_SEQ_NUM IS NOT NULL)
AND (EFFECTIVITY_DATE IS NOT NULL OR NEW_EFFECTIVITY_DATE IS NOT NULL)
AND ITEM_NUM IS NULL;
l_dyn_sql_cursor := l_dyn_sql_cursor || ' SELECT ' || l_item_col_name || ',' || G_INTF_SRCSYS_COMPONENT ;
l_dyn_sql_cursor := l_dyn_sql_cursor || ' OR UPPER(Transaction_Type) = ''' || G_TXN_UPDATE || ''' OR UPPER(Transaction_Type) = ''' || G_TXN_SYNC || ''' )';
l_cursor_select := Dbms_Sql.Open_Cursor;
Dbms_Sql.Parse(l_cursor_select, l_dyn_sql_cursor, Dbms_Sql.NATIVE);
Dbms_Sql.Define_Column(l_cursor_select, 1, L_ITEM_NAME, 3000);
Dbms_Sql.Define_Column(l_cursor_select, 2, L_SRCSYS_ITEM, 3000);
Dbms_Sql.Define_Column(l_cursor_select, 3, L_PARENT_NAME, 3000);
Dbms_Sql.Define_Column(l_cursor_select, 4, L_SRCSYS_PARENT,3000);
Dbms_Sql.Define_Column(l_cursor_select, 5, L_ORGANIZATION_CODE, 10);
Dbms_Sql.Define_Column(l_cursor_select, 6, L_STRUCTURE_NAME, 240);
Dbms_Sql.Define_Column(l_cursor_select, 7, L_COMP_REF_DESIG, 3000);
Dbms_Sql.Define_Column(l_cursor_select, 8, L_TRANSACTION_ID);
Dbms_Sql.Define_Column(l_cursor_select, 9, L_TRNSACTION_TYPE, 30);
Dbms_Sql.Bind_Variable(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
l_cursor_execute := Dbms_Sql.EXECUTE(l_cursor_select);
IF (Dbms_Sql.Fetch_Rows(l_cursor_select) > 0) THEN
Dbms_Sql.Column_Value(l_cursor_select,1,L_ITEM_NAME);
Dbms_Sql.Column_Value(l_cursor_select,2,L_SRCSYS_ITEM);
Dbms_Sql.Column_Value(l_cursor_select,3,L_PARENT_NAME);
Dbms_Sql.Column_Value(l_cursor_select,4,L_SRCSYS_PARENT);
Dbms_Sql.Column_Value(l_cursor_select,5,L_ORGANIZATION_CODE);
Dbms_Sql.Column_Value(l_cursor_select,6,L_STRUCTURE_NAME);
Dbms_Sql.Column_Value(l_cursor_select,7,L_COMP_REF_DESIG);
Dbms_Sql.Column_Value(l_cursor_select,8,L_TRANSACTION_ID);
Dbms_Sql.Column_Value(l_cursor_select,9,L_TRNSACTION_TYPE);
INSERT INTO BOM_REF_DESGS_INTERFACE (
BATCH_ID,
REQUEST_ID,
TRANSACTION_TYPE,
TRANSACTION_ID,
PROCESS_FLAG,
ASSEMBLY_ITEM_NUMBER,
ORGANIZATION_CODE,
ALTERNATE_BOM_DESIGNATOR,
COMPONENT_ITEM_NUMBER,
COMP_SOURCE_SYSTEM_REFERENCE,
PARENT_SOURCE_SYSTEM_REFERENCE,
COMPONENT_REFERENCE_DESIGNATOR)
VALUES (
p_batch_id,
G_REQUEST_ID,
Decode(L_TRNSACTION_TYPE,G_TXN_SYNC,G_TXN_SYNC,G_TXN_UPDATE,G_TXN_SYNC,G_TXN_CREATE,G_TXN_CREATE,G_TXN_ADD,G_TXN_CREATE,L_TRNSACTION_TYPE),
L_TRANSACTION_ID,
1,
L_PARENT_NAME,
L_ORGANIZATION_CODE,
L_STRUCTURE_NAME,
L_ITEM_NAME,
L_SRCSYS_ITEM,
L_SRCSYS_PARENT,
l_comp_ref_desig_tbl(I));
Dbms_Sql.Close_Cursor(l_cursor_select);
UPDATE BOM_REF_DESGS_INTERFACE BRDI
SET(effectivity_date, operation_seq_num, from_end_item_unit_number, component_sequence_id, assembly_item_revision_code)
= (SELECT Decode(effectivity_date,NULL,new_effectivity_date,effectivity_date),
Decode(operation_seq_num,NULL,new_operation_seq_num,operation_seq_num),
Decode(from_end_item_unit_number,NULL,new_from_end_item_unit_number,from_end_item_unit_number),
component_sequence_id, parent_revision_code
FROM BOM_INVENTORY_COMPS_INTERFACE BIC1
WHERE BIC1.TRANSACTION_ID = BRDI.TRANSACTION_ID )
WHERE
BRDI.batch_id = p_batch_id
AND EXISTS( Select 'X' FROM BOM_INVENTORY_COMPS_INTERFACE BIC12
WHERE BIC12.TRANSACTION_ID = BRDI.TRANSACTION_ID);
UPDATE EGO_BULKLOAD_INTF EBI
SET EBI.PROCESS_STATUS = 7
WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id AND EBI.PROCESS_STATUS = 1;
G_TXN_UPDATE VARCHAR2(10) := 'UPDATE';
G_TXN_DELETE VARCHAR2(10) := 'DELETE';
SELECT Bill_Sequence_id, Common_Bill_Sequence_id, Source_Bill_Sequence_id
INTO l_Bill_Sequence_id, l_Common_Bill_Sequence_Id, l_Source_Bill_Sequence_Id
FROM BOM_STRUCTURES_B
WHERE assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND NVL(alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR);
SELECT COUNT(COMPONENT_ITEM_ID) INTO l_Component_Count
FROM BOM_INVENTORY_COMPS_INTERFACE
WHERE process_flag = 1
AND UPPER(transaction_type) = G_TXN_UPDATE
AND Request_Id = p_request_id
AND (p_batch_id IS NULL OR batch_id = p_batch_id)
AND assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND NVL(alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR);
UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
SET COMPONENT_SEQUENCE_ID
= (SELECT COMPONENT_SEQUENCE_ID
FROM BOM_INVENTORY_COMPONENTS BIC, bom_structures_b bsb
WHERE BIC.bill_sequence_id = bsb.bill_Sequence_id
AND bsb.assembly_item_id = p_assembly_item_id
AND bsb.organization_id = p_organization_id
AND NVL(bsb.alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR)
AND BIC.common_component_sequence_id = BICI.component_sequence_id)
WHERE process_flag = 1
AND UPPER(transaction_type) = G_TXN_UPDATE
AND Request_Id = p_request_id
AND (p_batch_id IS NULL OR batch_id = p_batch_id)
AND assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND NVL(alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR);
SELECT DISTINCT To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) attr_group_id
FROM ego_results_fmt_usages
WHERE resultfmt_usage_id = c_resultfmt_usage_id
AND attribute_code LIKE '%$$%'
AND To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) IN --attr_group_id
------------------------------------------------------------------------------
-- Fixed in 11.5.10. Ensuring only the Item User-Defined Attrs are processed.
------------------------------------------------------------------------------
(
SELECT attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_type = G_COMP_ATTR_GROUP_TYPE
AND application_id = G_BOM_APPLICATION_ID
);
SELECT attr_group_name, attr_name
FROM ego_attrs_v
WHERE attr_id = p_attr_id
AND attr_group_type = G_COMP_ATTR_GROUP_TYPE
AND application_id = G_BOM_APPLICATION_ID;
l_cursor_select INTEGER;
SELECT mtl_system_items_intf_sets_s.NEXTVAL
INTO l_msii_set_process_id
FROM dual;
l_dyn_sql := ' SELECT To_Number(SUBSTR(attribute_code, INSTR(attribute_code, ''$$'')+2)) attr_id, intf_column_name ';
l_cursor_select := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
SELECT DATA_LEVEL_INT_NAME INTO l_attr_group_data_level
FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
WHERE ATTR_GROUP_TYPE in (G_IUD_ATTR_GROUP_TYPE, G_GTN_SNG_ATTR_GROUP_TYPE, G_GTN_MUL_ATTR_GROUP_TYPE)
AND ATTR_GROUP_ID = c_attr_grp_rec.attr_group_id
AND OBJECT_NAME = G_EGO_ITEM_OBJ_NAME
AND ROWNUM = 1;-- The AG cannot have associations at Item level and Revision Level for different Catalogs.
l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
l_dyn_attr_id_val_sql := ' SELECT ';
l_usr_attr_data_tbl.DELETE(l_rows_per_attr_grp_indx);
l_attr_id_table.DELETE;
l_intf_col_name_table.DELETE;
SELECT structure_type_id
INTO l_str_type_id
FROM ego_import_option_sets
where batch_id = p_data_set_id;
INSERT INTO BOM_CMP_USR_ATTR_INTERFACE
(
DATA_SET_ID ,
TRANSACTION_ID ,
BILL_SEQUENCE_ID ,
COMPONENT_SEQUENCE_ID,
ROW_IDENTIFIER ,
ATTR_GROUP_INT_NAME ,
ATTR_INT_NAME ,
ATTR_DISP_VALUE ,
PROCESS_STATUS ,
SOURCE_SYSTEM_ID ,
COMP_SOURCE_SYSTEM_REFERENCE,
BATCH_ID,
STRUCTURE_TYPE_ID,
TRANSACTION_TYPE,
ITEM_NUMBER,
ORGANIZATION_CODE,
ORGANIZATION_ID,
ASSEMBLY_ITEM_NUMBER,
PARENT_SOURCE_SYSTEM_REFERENCE
)
VALUES
(
l_usr_attr_data_tbl(i).DATA_SET_ID,
l_usr_attr_data_tbl(i).TRANSACTION_ID,
l_usr_attr_data_tbl(i).BILL_SEQUENCE_ID,
l_usr_attr_data_tbl(i).COMPONENT_SEQUENCE_ID,
l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
l_usr_attr_data_tbl(i).ATTR_NAME,
l_varchar_data,
0,-- G_PROCESS_STATUS,
l_usr_attr_data_tbl(i).SOURCE_SYSTEM_ID,
l_usr_attr_data_tbl(i).SOURCE_SYSTEM_REFERENCE,
p_data_set_id,
l_str_type_id,
'SYNC',
l_usr_attr_data_tbl(i).COMPONENT_ITEM_NUMBER,
l_usr_attr_data_tbl(i).ORGANIZATION_CODE,
(SELECT ORGANIZATION_ID FROM mtl_parameters where organization_code = l_usr_attr_data_tbl(i).ORGANIZATION_CODE),
l_usr_attr_data_tbl(i).ASSEMBLY_ITEM_NUMBER,
l_usr_attr_data_tbl(i).PARENT_SOURCE_SYSTEM_REFERENCE
);