[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
ELSIF p_tx_type = G_UPDATE
THEN
l_acd_type := G_CHANGE_ACD_TYPE;
ELSIF p_tx_type = G_DELETE
THEN
l_acd_type := G_DELETE_ACD_TYPE;
SELECT REVISION_IMPORT_POLICY
FROM EGO_IMPORT_OPTION_SETS
WHERE BATCH_ID = c_batch_id ;
SELECT CHANGE_ORDER_CREATION
FROM EGO_IMPORT_OPTION_SETS
WHERE BATCH_ID = c_batch_id ;
PROCEDURE Insert_Mtl_Intf_Err
( p_transaction_id IN NUMBER
, p_bo_identifier IN VARCHAR2 := NULL
, p_error_entity_code IN VARCHAR2 := NULL
, p_error_table_name IN VARCHAR2 := NULL
, p_error_column_name IN VARCHAR2 := NULL
, p_error_msg IN VARCHAR2 := NULL
, p_error_msg_type IN VARCHAR2 := NULL
, p_error_msg_name IN VARCHAR2 := NULL
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
INSERT INTO MTL_INTERFACE_ERRORS
( ORGANIZATION_ID
, UNIQUE_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, TABLE_NAME
, MESSAGE_NAME
, COLUMN_NAME
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, ERROR_MESSAGE
, TRANSACTION_ID
, ENTITY_IDENTIFIER
, BO_IDENTIFIER
, MESSAGE_TYPE
)
VALUES
( NULL
, NULL
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, p_error_table_name
, p_error_msg_name
, p_error_table_name
, FND_GLOBAL.conc_request_id
, FND_GLOBAL.prog_appl_id
, FND_GLOBAL.conc_program_id
, SYSDATE
, SUBSTR(p_error_msg ,1, 2000)
, p_transaction_id
, p_error_entity_code
, p_bo_identifier
, p_error_msg_type
);
END Insert_Mtl_Intf_Err ;
Insert_Mtl_Intf_Err
( p_transaction_id => p_transaction_id
, p_bo_identifier => p_bo_identifier
, p_error_entity_code => p_error_entity_code
, p_error_table_name => p_error_table_name
, p_error_column_name => p_error_column_name
, p_error_msg => SUBSTR(p_error_msg ,1, 2000)
, p_error_msg_type => Get_Msg_Type_Text(p_error_msg_type)
, p_error_msg_name => p_error_msg_name
) ;
l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
Write_Debug('Insert Error Message for the records which is set to CM Process per Change ID value set above validation. . .' );
l_dyn_sql := 'INSERT INTO MTL_INTERFACE_ERRORS ';
l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_DATE , ';
l_dyn_sql := l_dyn_sql || ' LAST_UPDATED_BY , ';
l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_LOGIN , ';
l_dyn_sql := l_dyn_sql || ' PROGRAM_UPDATE_DATE , ';
l_dyn_sql := l_dyn_sql || 'SELECT ';
Write_Debug('Update Process Flag to Error for records which can not find CO for the Org. . .' );
l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
SELECT
-- EXTENSION_ID,
-- INVENTORY_ITEM_ID,
-- ORGANIZATION_ID,
-- CREATED_BY,
-- CREATION_DATE,
-- LAST_UPDATED_BY,
-- LAST_UPDATE_DATE,
-- LAST_UPDATE_LOGIN,
-- ITEM_CATALOG_GROUP_ID,
-- REVISION_ID,
IS_TRADE_ITEM_A_CONSUMER_UNIT,
IS_TRADE_ITEM_INFO_PRIVATE,
GROSS_WEIGHT,
UOM_GROSS_WEIGHT,
EFFECTIVE_DATE,
CANCELED_DATE,
DISCONTINUED_DATE,
END_AVAILABILITY_DATE_TIME,
START_AVAILABILITY_DATE_TIME,
BRAND_NAME,
IS_TRADE_ITEM_A_BASE_UNIT,
IS_TRADE_ITEM_A_VARIABLE_UNIT,
IS_PACK_MARKED_WITH_EXP_DATE,
IS_PACK_MARKED_WITH_GREEN_DOT,
IS_PACK_MARKED_WITH_INGRED,
IS_PACKAGE_MARKED_AS_REC,
IS_PACKAGE_MARKED_RET,
STACKING_FACTOR,
STACKING_WEIGHT_MAXIMUM,
UOM_STACKING_WEIGHT_MAXIMUM,
ORDERING_LEAD_TIME,
UOM_ORDERING_LEAD_TIME,
ORDER_QUANTITY_MAX,
ORDER_QUANTITY_MIN,
ORDER_QUANTITY_MULTIPLE,
ORDER_SIZING_FACTOR,
EFFECTIVE_START_DATE,
CATALOG_PRICE,
EFFECTIVE_END_DATE,
SUGGESTED_RETAIL_PRICE,
MATERIAL_SAFETY_DATA_SHEET_NO,
HAS_BATCH_NUMBER,
IS_NON_SOLD_TRADE_RET_FLAG,
IS_TRADE_ITEM_MAR_REC_FLAG,
DIAMETER,
UOM_DIAMETER,
DRAINED_WEIGHT,
UOM_DRAINED_WEIGHT,
GENERIC_INGREDIENT,
GENERIC_INGREDIENT_STRGTH,
UOM_GENERIC_INGREDIENT_STRGTH,
INGREDIENT_STRENGTH,
IS_NET_CONTENT_DEC_FLAG,
NET_CONTENT,
UOM_NET_CONTENT,
PEG_HORIZONTAL,
UOM_PEG_HORIZONTAL,
PEG_VERTICAL,
UOM_PEG_VERTICAL,
CONSUMER_AVAIL_DATE_TIME,
DEL_TO_DIST_CNTR_TEMP_MAX,
UOM_DEL_TO_DIST_CNTR_TEMP_MAX,
DEL_TO_DIST_CNTR_TEMP_MIN,
UOM_DEL_TO_DIST_CNTR_TEMP_MIN,
DELIVERY_TO_MRKT_TEMP_MAX,
UOM_DELIVERY_TO_MRKT_TEMP_MAX,
DELIVERY_TO_MRKT_TEMP_MIN,
UOM_DELIVERY_TO_MRKT_TEMP_MIN,
SUB_BRAND,
-- TRADE_ITEM_DESCRIPTOR,
EANUCC_CODE,
EANUCC_TYPE,
RETAIL_PRICE_ON_TRADE_ITEM,
QUANTITY_OF_COMP_LAY_ITEM,
QUANITY_OF_ITEM_IN_LAYER,
QUANTITY_OF_ITEM_INNER_PACK,
TARGET_MARKET_DESC,
QUANTITY_OF_INNER_PACK,
BRAND_OWNER_GLN,
BRAND_OWNER_NAME,
STORAGE_HANDLING_TEMP_MAX,
UOM_STORAGE_HANDLING_TEMP_MAX,
STORAGE_HANDLING_TEMP_MIN,
UOM_STORAGE_HANDLING_TEMP_MIN,
TRADE_ITEM_COUPON,
DEGREE_OF_ORIGINAL_WORT,
FAT_PERCENT_IN_DRY_MATTER,
PERCENT_OF_ALCOHOL_BY_VOL,
ISBN_NUMBER,
ISSN_NUMBER,
IS_INGREDIENT_IRRADIATED,
IS_RAW_MATERIAL_IRRADIATED,
IS_TRADE_ITEM_GENETICALLY_MOD,
IS_TRADE_ITEM_IRRADIATED,
PUBLICATION_STATUS,
TOP_GTIN,
SECURITY_TAG_LOCATION,
URL_FOR_WARRANTY,
NESTING_INCREMENT,
UOM_NESTING_INCREMENT,
IS_TRADE_ITEM_RECALLED,
MODEL_NUMBER,
PIECES_PER_TRADE_ITEM,
UOM_PIECES_PER_TRADE_ITEM,
DEPT_OF_TRNSPRT_DANG_GOODS_NUM,
RETURN_GOODS_POLICY,
IS_OUT_OF_BOX_PROVIDED,
REGISTRATION_UPDATE_DATE,
TP_NEUTRAL_UPDATE_DATE,
MASTER_ORG_EXTENSION_ID,
IS_BARCODE_SYMBOLOGY_DERIVABLE,
INVOICE_NAME,
DESCRIPTIVE_SIZE,
FUNCTIONAL_NAME,
TRADE_ITEM_FORM_DESCRIPTION,
WARRANTY_DESCRIPTION,
TRADE_ITEM_FINISH_DESCRIPTION ,
DESCRIPTION_SHORT -- ,
-- CHANGE_ID,
-- CHANGE_LINE_ID,
-- ACD_TYPE,
-- IMPLEMENTATION_DATE
FROM EGO_GTN_ATTR_CHG_VL
WHERE INVENTORY_ITEM_ID = c_inventory_item_id
AND ORGANIZATION_ID = c_organization_id
AND CHANGE_ID = c_change_id
AND CHANGE_LINE_ID = c_change_line_Id
AND ( ACD_TYPE = c_acd_type OR c_acd_type IS NULL)
AND ACD_TYPE <>'HISTORY';
SELECT
DECODE(ACD_TYPE,'ADD',null,pend.EXTENSION_ID) AS EXTENSION_ID,
-- INVENTORY_ITEM_ID,
-- ORGANIZATION_ID,
-- CREATED_BY,
-- CREATION_DATE,
-- LAST_UPDATED_BY,
-- LAST_UPDATE_DATE,
-- LAST_UPDATE_LOGIN,
-- ITEM_CATALOG_GROUP_ID,
-- REVISION_ID,
pend.ATTR_GROUP_ID,
NVL(pend.MANUFACTURER_GLN, prod.MANUFACTURER_GLN) MANUFACTURER_GLN,
NVL(pend.MANUFACTURER_ID, prod.MANUFACTURER_ID) MANUFACTURER_ID,
NVL(pend.PARTY_RECEIVING_PRIVATE_DATA, prod.PARTY_RECEIVING_PRIVATE_DATA) PARTY_RECEIVING_PRIVATE_DATA,
NVL(pend.BAR_CODE_TYPE, prod.BAR_CODE_TYPE) BAR_CODE_TYPE,
NVL(pend.COLOR_CODE_LIST_AGENCY, prod.COLOR_CODE_LIST_AGENCY) COLOR_CODE_LIST_AGENCY,
NVL(pend.COLOR_CODE_VALUE, prod.COLOR_CODE_VALUE) COLOR_CODE_VALUE,
NVL(pend.CLASS_OF_DANGEROUS_CODE, prod.CLASS_OF_DANGEROUS_CODE) CLASS_OF_DANGEROUS_CODE,
NVL(pend.DANGEROUS_GOODS_MARGIN_NUMBER, prod.DANGEROUS_GOODS_MARGIN_NUMBER)DANGEROUS_GOODS_MARGIN_NUMBER,
NVL(pend.DANGEROUS_GOODS_HAZARDOUS_CODE,prod.DANGEROUS_GOODS_HAZARDOUS_CODE)DANGEROUS_GOODS_HAZARDOUS_CODE,
NVL(pend.DANGEROUS_GOODS_PACK_GROUP, prod.DANGEROUS_GOODS_PACK_GROUP) DANGEROUS_GOODS_PACK_GROUP,
NVL(pend.DANGEROUS_GOODS_REG_CODE, prod.DANGEROUS_GOODS_REG_CODE) DANGEROUS_GOODS_REG_CODE,
NVL(pend.DANGEROUS_GOODS_SHIPPING_NAME, prod.DANGEROUS_GOODS_SHIPPING_NAME)DANGEROUS_GOODS_SHIPPING_NAME,
NVL(pend.UNITED_NATIONS_DANG_GOODS_NO, prod.UNITED_NATIONS_DANG_GOODS_NO) UNITED_NATIONS_DANG_GOODS_NO,
NVL(pend.FLASH_POINT_TEMP, prod.FLASH_POINT_TEMP) FLASH_POINT_TEMP,
NVL(pend.UOM_FLASH_POINT_TEMP, prod.UOM_FLASH_POINT_TEMP) UOM_FLASH_POINT_TEMP,
NVL(pend.COUNTRY_OF_ORIGIN, prod.COUNTRY_OF_ORIGIN) COUNTRY_OF_ORIGIN,
NVL(pend.HARMONIZED_TARIFF_SYS_ID_CODE, prod.HARMONIZED_TARIFF_SYS_ID_CODE)HARMONIZED_TARIFF_SYS_ID_CODE,
NVL(pend.SIZE_CODE_LIST_AGENCY, prod.SIZE_CODE_LIST_AGENCY) SIZE_CODE_LIST_AGENCY,
NVL(pend.SIZE_CODE_VALUE, prod.SIZE_CODE_VALUE) SIZE_CODE_VALUE,
pend.MASTER_ORG_EXTENSION_ID,
NVL(pend.HANDLING_INSTRUCTIONS_CODE, prod.HANDLING_INSTRUCTIONS_CODE) HANDLING_INSTRUCTIONS_CODE,
NVL(pend.DANGEROUS_GOODS_TECHNICAL_NAME,prod.DANGEROUS_GOODS_TECHNICAL_NAME)DANGEROUS_GOODS_TECHNICAL_NAME,
NVL(pend.DELIVERY_METHOD_INDICATOR , prod.DELIVERY_METHOD_INDICATOR ) DELIVERY_METHOD_INDICATOR,
-- CHANGE_ID,
-- CHANGE_LINE_ID,
DECODE(ACD_TYPE,'DELETE',ACD_TYPE,null) TRANSACTION_TYPE
-- IMPLEMENTATION_DATE
FROM EGO_GTN_MUL_ATTR_CHG_VL pend,
EGO_ITM_GTN_MUL_ATTRS_VL prod
WHERE pend.EXTENSION_ID = prod.EXTENSION_ID (+)
AND pend.INVENTORY_ITEM_ID = c_inventory_item_id
AND pend.ORGANIZATION_ID = c_organization_id
AND pend.CHANGE_ID = c_change_id
AND pend.CHANGE_LINE_ID = c_change_line_Id
AND ( pend.ACD_TYPE = c_acd_type OR c_acd_type IS NULL)
AND ACD_TYPE <>'HISTORY'
ORDER BY ATTR_GROUP_ID;
SELECT
UNIT_WEIGHT
FROM EGO_MTL_SY_ITEMS_CHG_B
WHERE INVENTORY_ITEM_ID = c_inventory_item_id
AND ORGANIZATION_ID = c_organization_id
AND CHANGE_ID = c_change_id
AND CHANGE_LINE_ID = c_change_line_Id
AND ACD_TYPE = G_CHANGE_ACD_TYPE ;
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,CHANGE_ID
,CHANGE_LINE_ID
,TRANSACTION_TYPE
,MAX(TRANSACTION_ID) AS TRANSACTION_ID
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ATTR_GROUP_TYPE IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
AND DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = G_CM_TO_BE_PROCESSED
AND CHANGE_ID IS NOT NULL
AND CHANGE_LINE_ID IS NOT NULL
GROUP BY INVENTORY_ITEM_ID, ORGANIZATION_ID,CHANGE_ID, CHANGE_LINE_ID, TRANSACTION_TYPE ;
l_multi_row_attrs.DELETE ;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_ERROR
WHERE DATA_SET_ID = p_batch_id
AND ATTR_GROUP_TYPE IN (G_EGO_ITEM_GTIN_ATTRS , G_EGO_ITEM_GTIN_MULTI_ATTRS)
AND INVENTORY_ITEM_ID = i.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = i.ORGANIZATION_ID
AND CHANGE_ID = i.CHANGE_ID
AND CHANGE_LINE_ID = i.CHANGE_LINE_ID;
Insert_Mtl_Intf_Err
( p_transaction_id => i.TRANSACTION_ID
, p_bo_identifier => G_BO_IDENTIFIER
, p_error_entity_code => G_GDSN_ATTR_ENTITY
, p_error_table_name => G_ITEM_USR_ATTR_INTF
, p_error_column_name => NULL
, p_error_msg => l_msg_text
, p_error_msg_type => G_ENG_MSG_TYPE_ERROR
, p_error_msg_name => null
) ;
Insert_Mtl_Intf_Err
( p_transaction_id => i.TRANSACTION_ID
, p_bo_identifier => G_BO_IDENTIFIER
, p_error_entity_code => G_GDSN_ATTR_ENTITY
, p_error_table_name => G_ITEM_USR_ATTR_INTF
, p_error_column_name => NULL
, p_error_msg => l_msg_text
, p_error_msg_type => G_ENG_MSG_TYPE_ERROR
, p_error_msg_name => null
) ;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_ERROR
WHERE DATA_SET_ID = p_batch_id
AND ATTR_GROUP_TYPE IN (G_EGO_ITEM_GTIN_ATTRS , G_EGO_ITEM_GTIN_MULTI_ATTRS)
AND INVENTORY_ITEM_ID = i.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = i.ORGANIZATION_ID
AND CHANGE_ID = i.CHANGE_ID
AND CHANGE_LINE_ID = i.CHANGE_LINE_ID;
Insert_Mtl_Intf_Err
( p_transaction_id => i.TRANSACTION_ID
, p_bo_identifier => G_BO_IDENTIFIER
, p_error_entity_code => G_GDSN_ATTR_ENTITY
, p_error_table_name => G_ITEM_USR_ATTR_INTF
, p_error_column_name => NULL
, p_error_msg => l_msg_text
, p_error_msg_type => G_ENG_MSG_TYPE_ERROR
, p_error_msg_name => null
) ;
Insert_Mtl_Intf_Err
( p_transaction_id => i.TRANSACTION_ID
, p_bo_identifier => G_BO_IDENTIFIER
, p_error_entity_code => G_GDSN_ATTR_ENTITY
, p_error_table_name => G_ITEM_USR_ATTR_INTF
, p_error_column_name => NULL
, p_error_msg => l_msg_text
, p_error_msg_type => G_ENG_MSG_TYPE_ERROR
, p_error_msg_name => null
) ;
SELECT 'S' INTO l_return_status
FROM EGO_AML_INTF
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_FLAG = G_CM_TO_BE_PROCESSED
AND ROWNUM = 1;
UPDATE ego_aml_intf intf
SET intf.process_flag = G_CM_DATA_POPULATION
WHERE intf.data_set_id = p_batch_id
AND intf.process_flag = G_CM_TO_BE_PROCESSED
AND EXISTS ( SELECT 'x'
FROM EGO_MFG_PART_NUM_CHGS pending_change2
WHERE intf.inventory_item_id = pending_change2.inventory_item_id
AND intf.organization_id = pending_change2.organization_id
AND intf.manufacturer_id = pending_change2.manufacturer_id
AND intf.mfg_part_num = pending_change2.mfg_part_num
AND intf.change_line_id = pending_change2.change_line_id
AND intf.transaction_type = DECODE(pending_change2.ACD_TYPE
, 'ADD', 'CREATE'
, 'CHANGE', 'UPDATE'
, 'DELETE', 'DELETE', 'INVALID')
) ;
Write_Debug('Populate intf table with prod data for UPDATE done ' );
UPDATE ego_aml_intf intf
SET (intf.mrp_planning_code
,intf.description
,intf.first_article_status
,intf.approval_status
,intf.start_date
,intf.end_date
,intf.process_flag
-- ,attribute_category
-- ,attribute1
-- ,attribute2
-- ,attribute3
-- ,attribute4
-- ,attribute5
-- ,attribute6
-- ,attribute7
-- ,attribute8
-- ,attribute9
-- ,attribute10
-- ,attribute11
-- ,attribute12
-- ,attribute13
-- ,attribute14
-- ,attribute15
)
= (SELECT
DECODE(intf.mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM,NULL,
NULL,pending_change.mrp_planning_code,
intf.mrp_planning_code),
DECODE(intf.description,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,pending_change.description,
intf.description),
DECODE(intf.first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,pending_change.first_article_status,
intf.first_article_status),
DECODE(intf.approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,pending_change.approval_status,
intf.approval_status),
DECODE(intf.start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
NULL,pending_change.start_date,
intf.start_date),
DECODE(intf.end_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
NULL,pending_change.end_date,
intf.end_date),
G_CM_TO_BE_PROCESSED
-- NO Needt copy for DFF in R12
-- , DECODE(intf.attribute_category,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute_category,
-- intf.attribute_category),
-- DECODE(intf.attribute1,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute1,
-- intf.attribute1),
-- DECODE(intf.attribute2,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute2,
-- intf.attribute2),
-- DECODE(intf.attribute3,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute3,
-- intf.attribute3),
-- DECODE(intf.attribute4,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute4,
-- intf.attribute4),
-- DECODE(intf.attribute5,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute5,
-- intf.attribute5),
-- DECODE(intf.attribute6,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute6,
-- intf.attribute6),
-- DECODE(intf.attribute7,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute7,
-- intf.attribute7),
-- DECODE(intf.attribute8,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute8,
-- intf.attribute8),
-- DECODE(intf.attribute9,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute9,
-- intf.attribute9),
-- DECODE(intf.attribute10,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute10,
-- intf.attribute10),
-- DECODE(intf.attribute11,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute11,
-- intf.attribute11),
-- DECODE(intf.attribute12,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute12,
-- intf.attribute12),
-- DECODE(intf.attribute13,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute13,
-- intf.attribute13),
-- DECODE(intf.attribute14,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute14,
-- intf.attribute14),
-- DECODE(intf.attribute15,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
-- NULL,pending_change.attribute15,
-- intf.attribute15)
FROM EGO_MFG_PART_NUM_CHGS pending_change
WHERE intf.inventory_item_id = pending_change.inventory_item_id
AND intf.organization_id = pending_change.organization_id
AND intf.manufacturer_id = pending_change.manufacturer_id
AND intf.mfg_part_num = pending_change.mfg_part_num
AND intf.change_line_id = pending_change.change_line_id
AND intf.transaction_type = DECODE(pending_change.ACD_TYPE, 'ADD', 'CREATE'
, 'CHANGE', 'UPDATE'
, 'DELETE', 'DELETE', 'INVALID')
)
WHERE data_set_id = p_batch_id
AND process_flag = G_CM_DATA_POPULATION
-- AND transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
AND ( NVL(mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM)
<> EGO_ITEM_PUB.G_INTF_NULL_NUM
OR
NVL(description,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
<> EGO_ITEM_PUB.G_INTF_NULL_CHAR
OR
NVL(first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
<> EGO_ITEM_PUB.G_INTF_NULL_CHAR
OR
NVL(approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
<> EGO_ITEM_PUB.G_INTF_NULL_CHAR
OR
NVL(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
<> EGO_ITEM_PUB.G_INTF_NULL_DATE
OR
NVL(end_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
<> EGO_ITEM_PUB.G_INTF_NULL_DATE
-- OR
-- NVL(attribute_category,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute1,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute2,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute3,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute4,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute5,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute6,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute7,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute8,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute9,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute10,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute11,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute12,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute13,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute14,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
-- OR
-- NVL(attribute15,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
-- <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
) ;
Write_Debug('After Populating intf table with prod data for UPDATE done ' );
l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
l_dyn_sql := l_dyn_sql || ' SET change_id = NVL(( SELECT eec.change_id ' ;
l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
l_dyn_sql := l_dyn_sql || ' SET change_id = NVL(( SELECT eec.change_id ' ;
Write_Debug('Insert Error Message for the records which can not find CO for the Org. . .' );
l_dyn_sql := 'INSERT INTO MTL_INTERFACE_ERRORS ';
l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_DATE , ';
l_dyn_sql := l_dyn_sql || ' LAST_UPDATED_BY , ';
l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_LOGIN , ';
l_dyn_sql := l_dyn_sql || ' PROGRAM_UPDATE_DATE , ';
l_dyn_sql := l_dyn_sql || 'SELECT ';
Write_Debug('Update Process Flag to Error for records which can not find CO for the Org. . .' );
l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
UPDATE EGO_IMPORT_OPTION_SETS
SET CHANGE_NOTICE = p_change_number
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = FND_GLOBAL.user_id
, LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE BATCH_ID = p_batch_id ;
l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
l_dyn_sql := l_dyn_sql || ' = ENG_CHANGE_IMPORT_UTIL.get_Rev_item_update_parent( INTF.change_id ';
Write_Debug('Insert Error Message for the records which can not find CO for the Org. . .' );
l_dyn_sql := 'INSERT INTO MTL_INTERFACE_ERRORS ';
l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_DATE , ';
l_dyn_sql := l_dyn_sql || ' LAST_UPDATED_BY , ';
l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_LOGIN , ';
l_dyn_sql := l_dyn_sql || ' PROGRAM_UPDATE_DATE , ';
l_dyn_sql := l_dyn_sql || 'SELECT ';
Write_Debug('Update Process Flag to Error for records which can not find CO for the Org. . .' );
l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
PROCEDURE UPDATE_PROCESS_STATUS
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
, p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
, p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_write_msg_to_intftbl IN VARCHAR2 := NULL -- FND_API.G_FALSE
, p_api_caller IN VARCHAR2 := NULL
, p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
, p_output_dir IN VARCHAR2 := NULL
, p_debug_filename IN VARCHAR2 := NULL
, p_batch_id IN NUMBER
, p_from_status IN NUMBER
, p_to_status IN NUMBER
, p_process_entity IN VARCHAR2 := NULL
, p_item_id IN NUMBER := NULL
, p_org_id IN NUMBER := NULL
, p_transaction_id IN NUMBER := NULL
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PROCESS_STATUS';
SAVEPOINT UPDATE_PROCESS_STATUS;
Write_Debug('Update process status to INTF Table. . .' );
l_dyn_sql := ' UPDATE ' || l_intf_table(i) || ' INTF ';
l_dyn_sql := l_dyn_sql || ' IN (SELECT rec_grp.DATA_SET_ID, rec_grp.ROW_IDENTIFIER, rec_grp.ATTR_GROUP_ID ' ;
ROLLBACK TO UPDATE_PROCESS_STATUS;
ROLLBACK TO UPDATE_PROCESS_STATUS;
ROLLBACK TO UPDATE_PROCESS_STATUS;
END UPDATE_PROCESS_STATUS;
FUNCTION get_Rev_item_update_parent ( p_change_id IN NUMBER
, p_organization_id IN NUMBER
, p_revised_item_id IN NUMBER
, p_revision IN VARCHAR2
, p_default_seq_id IN NUMBER := NULL
, p_revision_import_policy IN VARCHAR2 := NULL
)
RETURN NUMBER
IS
l_rev_item_seq_id NUMBER ;
select FND_GLOBAL.CONC_REQUEST_ID INTO l_request_id from dual;
UPDATE mtl_system_items_interface
SET change_id = p_change_id
where inventory_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND request_id = l_request_id
AND change_id is NULL;
END get_Rev_item_update_parent;
SELECT change_id INTO l_change_id FROM eng_engineering_changes WHERE change_notice = p_change_notice AND organization_id = p_organization_id;
SELECT REVISION_ID
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = p_item_id
AND ORGANIZATION_ID = p_org_id
AND REVISION = p_rev ;
SELECT rev_b.REVISION_ID
FROM MTL_ITEM_REVISIONS_B rev_b
WHERE rev_b.INVENTORY_ITEM_ID = c_item_id
AND rev_b.ORGANIZATION_ID = c_org_id
AND rev_b.REVISION = ( SELECT max(max_rev.revision)
FROM MTL_ITEM_REVISIONS_B max_rev
WHERE max_rev.INVENTORY_ITEM_ID = rev_b.INVENTORY_ITEM_ID
AND max_rev.ORGANIZATION_ID = rev_b.ORGANIZATION_ID
) ;
SELECT effectivity_control
INTO l_effectivity_control
FROM BOM_STRUCTURES_B
WHERE bill_sequence_id = p_bill_sequence_id;
SELECT bill_sequence_id, effectivity_control
INTO l_bill_sequence_id, l_effectivity_control
FROM BOM_STRUCTURES_B
WHERE assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND ((alternate_bom_designator IS NULL AND
p_alternate_bom_designator IS NULL) OR
alternate_bom_designator = p_alternate_bom_designator);
SELECT component_item_id, operation_seq_num, effectivity_date, from_end_item_unit_number, from_end_item_rev_id
INTO l_component_item_id, l_op_seq_number, l_effectivity_date, l_from_end_item_unit_number, l_from_end_item_rev_id
FROM bom_components_b
WHERE component_sequence_id = p_component_seq_id;
SELECT component_sequence_id
INTO l_component_seq_id
FROM bom_components_b bcb
WHERE bcb.bill_sequence_id = l_bill_sequence_id
AND bcb.operation_seq_num = p_op_seq_number
AND bcb.component_item_id = p_component_item_id
AND bcb.from_end_item_unit_number = p_from_end_item_unit_number
AND bcb.implementation_date IS NOT NULL;
SELECT component_sequence_id
INTO l_component_seq_id
FROM bom_components_b bcb
WHERE bcb.bill_sequence_id = l_bill_sequence_id
AND bcb.operation_seq_num = p_op_seq_number
AND bcb.component_item_id = p_component_item_id
AND bcb.from_end_item_rev_id = p_from_end_item_rev_id
AND bcb.implementation_date IS NOT NULL;
SELECT component_sequence_id
INTO l_component_seq_id
FROM bom_components_b bcb
WHERE bcb.bill_sequence_id = l_bill_sequence_id
AND bcb.operation_seq_num = p_op_seq_number
AND bcb.component_item_id = p_component_item_id
AND bcb.effectivity_date = p_effectivity_date
AND bcb.implementation_date IS NOT NULL;
Write_Debug('Inserting data into interface tables:');
INSERT INTO bom_inventory_comps_interface
(OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
EFFECTIVITY_DATE,
OLD_COMPONENT_SEQUENCE_ID,
COMPONENT_SEQUENCE_ID,
BILL_SEQUENCE_ID,
ASSEMBLY_ITEM_ID,
ALTERNATE_BOM_DESIGNATOR,
ORGANIZATION_ID,
PROCESS_FLAG,
TRANSACTION_TYPE,
FROM_END_ITEM_UNIT_NUMBER,
FROM_END_ITEM_ID,
FROM_END_ITEM_REV_ID,
BATCH_ID
) VALUES
(l_op_seq_number,
l_component_item_id,
l_effectivity_date,
l_component_seq_id,
l_component_seq_id,
l_bill_sequence_id,
p_assembly_item_id,
p_alternate_bom_designator,
p_organization_id,
G_CM_TO_BE_PROCESSED,
'UPDATE',
l_from_end_item_unit_number,
p_assembly_item_id,
l_from_end_item_rev_id,
p_batch_id
);
Write_Debug('Done Inserting data into interface tables:');
SELECT effectivity_control, alternate_bom_Designator
INTO l_effectivity_control, l_alt_bom_designator
FROM BOM_STRUCTURES_B
WHERE bill_sequence_id = p_bill_sequence_id;
SELECT bill_sequence_id, effectivity_control
INTO l_bill_sequence_id, l_effectivity_control
FROM BOM_STRUCTURES_B
WHERE assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND ((alternate_bom_designator IS NULL AND
p_alternate_bom_designator IS NULL) OR
alternate_bom_designator = p_alternate_bom_designator);
Write_Debug('Inserting data into interface tables:');
INSERT INTO bom_bill_of_mtls_interface
(ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ALTERNATE_BOM_DESIGNATOR,
BILL_SEQUENCE_ID,
EFFECTIVITY_CONTROL,
PROCESS_FLAG,
TRANSACTION_TYPE,
BATCH_ID
) VALUES
(p_assembly_item_id,
p_organization_id,
l_alt_bom_designator,
l_bill_sequence_id,
l_effectivity_control,
G_CM_TO_BE_PROCESSED,
'NO_OP',
p_batch_id
);
Write_Debug('Done Inserting data into interface tables:');
UPDATE bom_ref_desgs_interface brdi
SET
acd_type = decode(transaction_type, 'DELETE', G_BOM_DISABLE_ACD_TYPE,
'UPDATE', G_BOM_CHANGE_ACD_TYPE
,G_BOM_ADD_ACD_TYPE),
change_transaction_type = 'CREATE',
change_notice = p_change_notice,
bill_sequence_id = p_bill_sequence_id
WHERE (bill_sequence_id = p_bill_sequence_id OR
( bill_sequence_id IS NULL
AND assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND (alternate_bom_designator = p_alternate_bom_designator
OR (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
AND change_id = p_change_id
AND batch_id = p_batch_id
AND process_flag = G_CM_TO_BE_PROCESSED
AND transaction_type in ('CREATE', 'UPDATE', 'DELETE');
UPDATE bom_sub_comps_interface
SET
acd_type = decode(transaction_type, 'DELETE', G_BOM_DISABLE_ACD_TYPE,
'UPDATE', G_BOM_CHANGE_ACD_TYPE
,G_BOM_ADD_ACD_TYPE),
change_transaction_type = 'CREATE',
change_notice = p_change_notice,
bill_sequence_id = p_bill_sequence_id
WHERE (bill_sequence_id = p_bill_sequence_id OR
( bill_sequence_id IS NULL
AND assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND (alternate_bom_designator = p_alternate_bom_designator
OR (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
AND change_id = p_change_id
AND batch_id = p_batch_id
AND process_flag = G_CM_TO_BE_PROCESSED
AND transaction_type in ('CREATE', 'UPDATE', 'DELETE');
UPDATE bom_inventory_comps_interface
SET
acd_type = G_BOM_ADD_ACD_TYPE,
change_transaction_type = 'CREATE',
change_notice = p_change_notice,
revised_item_sequence_id = p_revised_item_sequence_id,
bill_sequence_id = p_bill_sequence_id,
new_effectivity_date = p_effectivity_date
WHERE (bill_sequence_id = p_bill_sequence_id OR
( bill_sequence_id IS NULL
AND assembly_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND (alternate_bom_designator = p_alternate_bom_designator
OR (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
AND ((p_effectivity_date IS NULL AND effectivity_date IS NULL)
OR (decode (new_effectivity_date,
null,
decode(parent_revision_id,
null,
nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
(SELECT effectivity_date + 1/(24*3600)
FROM mtl_item_revisions
WHERE revision_id = parent_revision_id )),
new_effectivity_date) = p_effectivity_date)
OR (decode (new_effectivity_date,
null,
decode(parent_revision_id,
null,
nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
(SELECT effectivity_date + 1/(24*3600)
FROM mtl_item_revisions
WHERE revision_id = parent_revision_id )),
new_effectivity_date) <= p_current_date AND
p_current_date = p_effectivity_date))
AND NVL(new_from_end_item_unit_number, NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR))
= NVL(p_from_end_item_unit_number, FND_API.G_MISS_CHAR)
AND NVL(from_end_item_rev_id, '-1') = NVL(p_from_end_item_rev_id, '-1')
AND change_id = p_change_id
AND batch_id = p_batch_id
AND process_flag = G_CM_TO_BE_PROCESSED
AND transaction_type = 'CREATE';
UPDATE bom_inventory_comps_interface bici
SET
acd_type = decode(transaction_type, 'DELETE', G_BOM_DISABLE_ACD_TYPE, G_BOM_CHANGE_ACD_TYPE),
change_transaction_type = 'CREATE',
revised_item_sequence_id = p_revised_item_sequence_id,
bill_sequence_id = p_bill_sequence_id,
change_notice = p_change_notice,
new_effectivity_date = p_effectivity_date
WHERE (bill_sequence_id = p_bill_sequence_id OR
( bill_sequence_id IS NULL
AND assembly_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND (alternate_bom_designator = p_alternate_bom_designator
OR (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
AND NVL(new_from_end_item_unit_number, NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR))
= NVL(p_from_end_item_unit_number, FND_API.G_MISS_CHAR)
AND NVL(from_end_item_rev_id, '-1')
= NVL(p_from_end_item_rev_id, '-1')
AND ((p_effectivity_date IS NULL AND effectivity_date IS NULL)
OR (decode (new_effectivity_date,
null,
decode(parent_revision_id,
null,
nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
(SELECT effectivity_date + 1/(24*3600)
FROM mtl_item_revisions
WHERE revision_id = parent_revision_id )),
new_effectivity_date) = p_effectivity_date)
OR (decode (new_effectivity_date,
null,
decode(parent_revision_id,
null,
nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
(SELECT effectivity_date + 1/(24*3600)
FROM mtl_item_revisions
WHERE revision_id = parent_revision_id )),
new_effectivity_date) <= p_current_date AND
p_current_date = p_effectivity_date))
AND change_id = p_change_id
AND batch_id = p_batch_id
AND process_flag = G_CM_TO_BE_PROCESSED
AND transaction_type in ('DELETE', 'UPDATE')
AND not exists (SELECT 1
FROM bom_inventory_components bic
WHERE
bic.component_sequence_id = bici.component_sequence_id
AND bic.revised_item_sequence_id = p_revised_item_sequence_id);
UPDATE bom_inventory_comps_interface bici
SET
acd_type = decode(transaction_type, 'DELETE', G_BOM_DISABLE_ACD_TYPE,
(SELECT bic.acd_type
FROM bom_inventory_components bic
WHERE
bic.component_sequence_id = bici.component_sequence_id
AND bic.revised_item_sequence_id = p_revised_item_sequence_id)),
change_transaction_type = decode(transaction_type, 'DELETE', 'DELETE', 'UPDATE'),
revised_item_sequence_id = p_revised_item_sequence_id,
bill_sequence_id = p_bill_sequence_id,
change_notice = p_change_notice,
new_effectivity_date = p_effectivity_date
WHERE (bill_sequence_id = p_bill_sequence_id OR
( bill_sequence_id IS NULL
AND assembly_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND (alternate_bom_designator = p_alternate_bom_designator
OR (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
AND NVL(new_from_end_item_unit_number, NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR))
= NVL(p_from_end_item_unit_number, FND_API.G_MISS_CHAR)
AND NVL(from_end_item_rev_id, '-1')
= NVL(p_from_end_item_rev_id, '-1')
AND ((p_effectivity_date IS NULL AND effectivity_date IS NULL)
OR (decode (new_effectivity_date,
null,
decode(parent_revision_id,
null,
nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
(SELECT effectivity_date + 1/(24*3600)
FROM mtl_item_revisions
WHERE revision_id = parent_revision_id )),
new_effectivity_date) = p_effectivity_date)
OR (decode (new_effectivity_date,
null,
decode(parent_revision_id,
null,
nvl(p_parent_rev_eff_date, nvl(effectivity_date, p_current_date)),
(SELECT effectivity_date + 1/(24*3600)
FROM mtl_item_revisions
WHERE revision_id = parent_revision_id )),
new_effectivity_date) <= p_current_date AND
p_current_date = p_effectivity_date))
AND change_id = p_change_id
AND batch_id = p_batch_id
AND process_flag = G_CM_TO_BE_PROCESSED
AND transaction_type in ('DELETE', 'UPDATE')
AND exists (SELECT 1
FROM bom_inventory_components bic
WHERE
bic.component_sequence_id = bici.component_sequence_id
AND bic.revised_item_sequence_id = p_revised_item_sequence_id);
UPDATE bom_ref_desgs_interface brdi
SET
acd_type = (SELECT brd.acd_type
FROM bom_reference_designators brd,
bom_inventory_components bic
WHERE brd.component_sequence_id = bic.component_sequence_id
AND brd.component_reference_designator = brdi.component_reference_designator
AND bic.revised_item_sequence_id = p_revised_item_sequence_id
AND bic.component_item_id = brdi.component_item_id),
change_transaction_type = decode(transaction_type, 'DELETE', 'DELETE', 'UPDATE'),
change_notice = p_change_notice
WHERE (brdi.bill_sequence_id = p_bill_sequence_id OR
( brdi.bill_sequence_id IS NULL
AND brdi.assembly_item_id = p_revised_item_id
AND brdi.organization_id = p_organization_id
AND (brdi.alternate_bom_designator = p_alternate_bom_designator
OR (brdi.alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
AND brdi.change_id = p_change_id
AND brdi.batch_id = p_batch_id
AND brdi.process_flag = G_CM_TO_BE_PROCESSED
AND brdi.transaction_type in ('DELETE', 'UPDATE')
AND exists (SELECT 1
FROM bom_reference_designators brd,
bom_inventory_components bic
WHERE brd.component_sequence_id = bic.component_sequence_id
AND brd.component_reference_designator = brdi.component_reference_designator
AND bic.revised_item_sequence_id = p_revised_item_sequence_id
AND bic.component_item_id = brdi.component_item_id);
UPDATE bom_sub_comps_interface bsci
SET
acd_type = (SELECT bsc.acd_type
FROM bom_substitute_components bsc,
bom_inventory_components bic
WHERE bsc.component_sequence_id = bic.component_sequence_id
AND bsc.substitute_component_id = bsci.substitute_component_id
AND bic.revised_item_sequence_id = p_revised_item_sequence_id
AND bic.component_item_id = bsci.component_item_id),
change_transaction_type = decode(transaction_type, 'DELETE', 'DELETE', 'UPDATE'),
change_notice = p_change_notice
WHERE (bsci.bill_sequence_id = p_bill_sequence_id OR
( bsci.bill_sequence_id IS NULL
AND bsci.assembly_item_id = p_revised_item_id
AND bsci.organization_id = p_organization_id
AND (bsci.alternate_bom_designator = p_alternate_bom_designator
OR (bsci.alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL))))
AND bsci.change_id = p_change_id
AND bsci.batch_id = p_batch_id
AND bsci.process_flag = G_CM_TO_BE_PROCESSED
AND bsci.transaction_type in ('DELETE', 'UPDATE')
AND exists (SELECT 1
FROM bom_substitute_components bsc,
bom_inventory_components bic
WHERE bsc.component_sequence_id = bic.component_sequence_id
AND bsc.substitute_component_id = bsci.substitute_component_id
AND bic.revised_item_sequence_id = p_revised_item_sequence_id
AND bic.component_item_id = bsci.component_item_id);
PROCEDURE INSERT_IMPORTED_CHANGE_HISTORY
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := NULL -- FND_API.G_FALSE
, p_commit IN VARCHAR2 := NULL -- FND_API.G_FALSE
, p_validation_level IN NUMBER := NULL -- FND_API.G_VALID_LEVEL_FULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_write_msg_to_intftbl IN VARCHAR2 := NULL -- FND_API.G_FALSE
, p_api_caller IN VARCHAR2 := NULL
, p_debug IN VARCHAR2 := NULL -- FND_API.G_FALSE
, p_output_dir IN VARCHAR2 := NULL
, p_debug_filename IN VARCHAR2 := NULL
, p_batch_id IN NUMBER
, p_change_ids IN FND_ARRAY_OF_NUMBER_25
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_IMPORTED_CHANGE_HISTORY';
l_hist_insert_flag VARCHAR2(1) ;
select 'N'
from EGO_IMPORT_BATCH_CHANGES
where BATCH_ID = c_batch_id
and CHANGE_ID = c_change_id
;
SAVEPOINT INSERT_IMPORTED_CHANGE_HISTORY;
fetch check_existence_c into l_hist_insert_flag ;
l_hist_insert_flag := 'Y' ;
l_hist_insert_flag := 'N' ;
IF l_hist_insert_flag = 'Y' THEN
Write_Debug('Inserting Import Hsotry: ChangeId: '|| to_char(l_change_id) ) ;
INSERT_IMPORT_CHANGE_ROW
( X_ROWID => l_dummy_rowid ,
X_BATCH_ID => p_batch_id,
X_CHANGE_ID => l_change_id ,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => FND_GLOBAL.user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => FND_GLOBAL.user_id ,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
) ;
Write_Debug('Insert Info Message for the record inserted as history. . .' );
Insert_Mtl_Intf_Err
( p_transaction_id => null
, p_bo_identifier => G_BO_IDENTIFIER
, p_error_entity_code => null
, p_error_table_name => null
, p_error_column_name => NULL
, p_error_msg => l_msg_text
, p_error_msg_type => G_ENG_MSG_TYPE_INFORMATION
, p_error_msg_name => null
) ;
END IF ; -- l_hist_insert_flag is 'Y'
ROLLBACK TO INSERT_IMPORTED_CHANGE_HISTORY;
ROLLBACK TO INSERT_IMPORTED_CHANGE_HISTORY;
ROLLBACK TO INSERT_IMPORTED_CHANGE_HISTORY;
END INSERT_IMPORTED_CHANGE_HISTORY ;
procedure INSERT_IMPORT_CHANGE_ROW (
X_ROWID in out nocopy VARCHAR2,
X_BATCH_ID in NUMBER,
X_CHANGE_ID in NUMBER ,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
cursor C is select ROWID from EGO_IMPORT_BATCH_CHANGES
where BATCH_ID = X_BATCH_ID
and CHANGE_ID = X_CHANGE_ID
;
insert into EGO_IMPORT_BATCH_CHANGES (
BATCH_ID,
CHANGE_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_BATCH_ID,
X_CHANGE_ID,
1.0,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
end INSERT_IMPORT_CHANGE_ROW;
cursor c is select
-- OBJECT_VERSION_NUMBER
from EGO_IMPORT_BATCH_CHANGES
where OBJECT_ID = X_BATCH_ID
and CHANGE_ID = X_CHANGE_ID
for update of OBJECT_ID nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_IMPORT_CHANGE_ROW (
X_BATCH_ID in NUMBER,
X_CHANGE_ID in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
update EGO_IMPORT_BATCH_CHANGES
set -- XXXX
where OBJECT_ID = X_BATCH_ID
and CHANGE_ID = X_CHANGE_ID ;
end UPDATE_IMPORT_CHANGE_ROW;
procedure DELETE_IMPORT_CHANGE_ROW (
X_BATCH_ID in NUMBER,
X_CHANGE_ID in NUMBER
) is
begin
delete from EGO_IMPORT_BATCH_CHANGES
where BATCH_ID = X_BATCH_ID
and CHANGE_ID = X_CHANGE_ID ;
end DELETE_IMPORT_CHANGE_ROW ;