[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LOGIN_ID fnd_user.last_update_login%TYPE;
SELECT *
INTO l_aml_rec
from ego_aml_intf
where data_set_id = p_data_set_id;
SELECT party_id, party_name
INTO G_PARTY_ID, G_PARTY_NAME
FROM ego_user_v
WHERE USER_ID = G_USER_ID;
SELECT party_id, party_name, user_id
INTO G_PARTY_ID, G_PARTY_NAME, G_USER_ID
FROM ego_user_v
WHERE USER_NAME = FND_GLOBAL.USER_NAME;
SELECT object_id
INTO G_FND_OBJECT_ID
FROM fnd_objects
WHERE obj_name = G_FND_OBJECT_NAME;
UPDATE ego_aml_intf
SET process_flag = G_PS_INVALID_TRANS_TYPE
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND transaction_type NOT IN
(EGO_ITEM_PUB.G_TTYPE_CREATE
,EGO_ITEM_PUB.G_TTYPE_UPDATE
,EGO_ITEM_PUB.G_TTYPE_SYNC
,EGO_ITEM_PUB.G_TTYPE_DELETE
);
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_SD_GT_ED_ERROR
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND NVL(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
> NVL(end_date,NVL(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE));
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_FA_STATUS_ERR
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND transaction_type <> EGO_ITEM_PUB.G_TTYPE_DELETE
AND ( ( NVL(first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
<> EGO_ITEM_PUB.G_INTF_NULL_CHAR
AND
first_article_status NOT IN
(SELECT lookup_code
FROM fnd_lookup_values fa_lookup
WHERE fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_FST_ATCLE_STS'
AND fa_lookup.language = G_SESSION_LANG)
)
OR
( first_article_status IS NULL
AND
NVL(first_article_status_meaning,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
<> EGO_ITEM_PUB.G_INTF_NULL_CHAR
AND
first_article_status_meaning NOT IN
(SELECT meaning
FROM fnd_lookup_values fa_lookup
WHERE fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_FST_ATCLE_STS'
AND fa_lookup.language = G_SESSION_LANG)
)
);
UPDATE ego_aml_intf aml_intf
SET first_article_status =
DECODE (first_article_status_meaning, EGO_ITEM_PUB.G_INTF_NULL_CHAR,
EGO_ITEM_PUB.G_INTF_NULL_CHAR,
(Select lookup_code
from fnd_lookup_values fa_lookup
where fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_FST_ATCLE_STS'
and fa_lookup.meaning = aml_intf.first_article_status_meaning
and fa_lookup.language = G_SESSION_LANG)
)
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND transaction_type <> EGO_ITEM_PUB.G_TTYPE_DELETE
AND first_article_status IS NULL
AND first_article_status_meaning IS NOT NULL;
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_APPROVAL_STATUS_ERR
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND transaction_type <> EGO_ITEM_PUB.G_TTYPE_DELETE
AND ( ( NVL(approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
<> EGO_ITEM_PUB.G_INTF_NULL_CHAR
AND
approval_status NOT IN
(SELECT lookup_code
FROM fnd_lookup_values fa_lookup
WHERE fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_APPR_STS'
AND fa_lookup.language = G_SESSION_LANG)
)
OR
( approval_status IS NULL
AND
NVL(approval_status_meaning,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
<> EGO_ITEM_PUB.G_INTF_NULL_CHAR
AND
approval_status_meaning NOT IN
(SELECT meaning
FROM fnd_lookup_values fa_lookup
WHERE fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_APPR_STS'
AND fa_lookup.language = G_SESSION_LANG)
)
);
UPDATE ego_aml_intf aml_intf
SET approval_status =
DECODE (approval_status_meaning, EGO_ITEM_PUB.G_INTF_NULL_CHAR,
EGO_ITEM_PUB.G_INTF_NULL_CHAR,
(Select lookup_code
from fnd_lookup_values fa_lookup
where fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_APPR_STS'
and fa_lookup.meaning = aml_intf.approval_status_meaning
and fa_lookup.language = G_SESSION_LANG)
)
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND transaction_type <> EGO_ITEM_PUB.G_TTYPE_DELETE
AND approval_status IS NULL
AND approval_status_meaning IS NOT NULL;
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_MANUFACTURER_ERR
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND ( (manufacturer_id IS NOT NULL
AND
NOT EXISTS
(SELECT 'x' FROM mtl_manufacturers manu
WHERE manu.manufacturer_id = aml_intf.manufacturer_id)
)
OR
(manufacturer_id IS NULL
AND
NOT EXISTS
(SELECT 'x' FROM mtl_manufacturers manu
WHERE manu.manufacturer_name = aml_intf.manufacturer_name)
)
);
UPDATE ego_aml_intf aml_intf
SET manufacturer_id =
(Select manufacturer_id
from mtl_manufacturers manu
where manu.manufacturer_name = aml_intf.manufacturer_name)
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND manufacturer_id IS NULL;
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_ORGANIZATION_ERR
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND ( (organization_id IS NOT NULL
AND
NOT EXISTS (SELECT 'x' FROM mtl_parameters mp
WHERE mp.organization_id = aml_intf.organization_id)
)
OR
(organization_id IS NULL
AND
NOT EXISTS (SELECT 'x' FROM mtl_parameters mp
WHERE mp.organization_code = aml_intf.organization_code)
)
);
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_NOT_MASTER_ORG_ERR
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND ( (organization_id IS NOT NULL
AND
NOT EXISTS (SELECT 'x' FROM mtl_parameters mp
WHERE mp.organization_id = aml_intf.organization_id
AND mp.organization_id = mp.master_organization_id)
)
OR
(organization_id IS NULL
AND
NOT EXISTS (SELECT 'x' FROM mtl_parameters mp
WHERE mp.organization_code = aml_intf.organization_code
AND mp.organization_id = mp.master_organization_id)
)
);
UPDATE ego_aml_intf aml_intf
SET organization_id =
(Select organization_id
from mtl_parameters mp
where mp.organization_code = aml_intf.organization_code)
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND organization_id IS NULL;
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_ITEM_ERR
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND ( (inventory_item_id IS NOT NULL
AND
NOT EXISTS
(SELECT 'x' FROM mtl_system_items_b_kfv item
WHERE item.organization_id = aml_intf.organization_id
AND item.inventory_item_id = aml_intf.inventory_item_id)
)
OR
(inventory_item_id IS NULL
AND
NOT EXISTS
(SELECT 'x' FROM mtl_system_items_b_kfv item
WHERE item.organization_id = aml_intf.organization_id
AND item.concatenated_segments = aml_intf.item_number)
)
);
UPDATE ego_aml_intf aml_intf
SET (item_number, prog_int_num1, prog_int_num2,
prog_int_num3, prog_int_char1) =
(Select concatenated_segments, item_catalog_group_id, lifecycle_id,
current_phase_id, NVL(approval_status,'A')
from mtl_system_items_b_kfv item
where item.organization_id = aml_intf.organization_id
and item.inventory_item_id = aml_intf.inventory_item_id)
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND inventory_item_id IS NOT NULL;
UPDATE ego_aml_intf aml_intf
SET (inventory_item_id, prog_int_num1, prog_int_num2,
prog_int_num3, prog_int_char1) =
(Select inventory_item_id, item_catalog_group_id, lifecycle_id,
current_phase_id, NVL(APPROVAL_STATUS,'A')
from mtl_system_items_b_kfv item
where item.organization_id = aml_intf.organization_id
and item.concatenated_segments = aml_intf.item_number)
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND inventory_item_id IS NULL;
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_VAL_TO_ID_COMPLETE
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS;
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_CREATE_REC_EXISTS
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE
AND EXISTS
(Select 'x'
from mtl_mfg_part_numbers part_num
where part_num.inventory_item_id = aml_intf.inventory_item_id
and part_num.organization_id = aml_intf.organization_id
and part_num.manufacturer_id = aml_intf.manufacturer_id
and part_num.mfg_part_num = aml_intf.mfg_part_num
);
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_REC_NOT_EXISTS
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_UPDATE
,EGO_ITEM_PUB.G_TTYPE_DELETE
)
AND NOT EXISTS
(Select 'x'
from mtl_mfg_part_numbers part_num
where part_num.inventory_item_id = aml_intf.inventory_item_id
and part_num.organization_id = aml_intf.organization_id
and part_num.manufacturer_id = aml_intf.manufacturer_id
and part_num.mfg_part_num = aml_intf.mfg_part_num
);
,p_message => 'Transaction check complete for UPDATE'
);
UPDATE ego_aml_intf aml_intf
SET transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND transaction_type = EGO_ITEM_PUB.G_TTYPE_SYNC
AND EXISTS
(Select 'x'
from mtl_mfg_part_numbers part_num
where part_num.inventory_item_id = aml_intf.inventory_item_id
and part_num.organization_id = aml_intf.organization_id
and part_num.manufacturer_id = aml_intf.manufacturer_id
and part_num.mfg_part_num = aml_intf.mfg_part_num
);
UPDATE ego_aml_intf aml_intf
SET transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND transaction_type = EGO_ITEM_PUB.G_TTYPE_SYNC;
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_ED_LT_SYSDATE
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
AND NVL(end_date,G_SYSDATE) <> EGO_ITEM_PUB.g_INTF_NULL_DATE
AND NVL(end_date,G_SYSDATE) < G_SYSDATE;
UPDATE ego_aml_intf orig
SET process_flag = G_PS_DUP_INTF_RECORDS
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND EXISTS
(SELECT 'X'
FROM ego_aml_intf
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND transaction_id <> orig.transaction_id
AND inventory_item_id = orig.inventory_item_id
AND organization_id = orig.organization_id
AND manufacturer_id = orig.manufacturer_id
AND mfg_part_num = orig.mfg_part_num
);
SELECT *
FROM ego_aml_intf
WHERE data_set_id = cp_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND NVL(prog_int_char2,'N') <> 'Y'
AND prog_int_num4 IS NOT NULL
FOR UPDATE OF transaction_id;
UPDATE ego_aml_intf aml_intf
SET prog_int_char2 = 'Y'
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND EXISTS (Select 1
from mtl_system_items_interface
where request_id = aml_intf.request_id
and inventory_item_id = aml_intf.inventory_item_id
and organization_id = aml_intf.organization_id
and transaction_type = 'CREATE'
and process_flag = 7
);
UPDATE ego_aml_intf aml_intf
SET prog_int_num4 =
(SELECT ic.item_catalog_group_id
FROM mtl_item_catalog_groups_b ic
WHERE EXISTS
(SELECT olc.object_classification_code CatalogId
FROM ego_obj_type_lifecycles olc
WHERE olc.object_id = G_FND_OBJECT_ID
AND olc.lifecycle_id = aml_intf.prog_int_num2
AND olc.object_classification_code =
to_char(ic.item_catalog_group_id)
)
AND ROWNUM = 1
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = aml_intf.prog_int_num1
)
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND prog_int_num2 IS NOT NULL;
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_TRANSFER_TO_CM
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND NVL(prog_int_char2,'N') <> 'Y'
AND prog_int_num4 IS NULL;
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_CHANGE_NOT_ALLOWED
WHERE CURRENT OF c_item_records;
UPDATE ego_aml_intf aml_intf
SET process_flag = G_PS_TRANSFER_TO_CM
WHERE CURRENT OF c_item_records;
SELECT *
FROM ego_aml_intf
WHERE data_set_id = cp_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_CREATE
,EGO_ITEM_PUB.G_TTYPE_UPDATE
)
FOR UPDATE OF transaction_id;
' UPDATE EGO_AML_INTF aml_intf ' ||
' SET process_flag = '||G_PS_NO_AML_PRIV ||
' WHERE data_set_id = :1'||
' AND process_flag = '||G_PS_VAL_TO_ID_COMPLETE||
' AND NVL(prog_int_char2,''N'') <> ''Y'''||
' AND NOT '|| l_sec_predicate;
UPDATE ego_aml_intf
SET mrp_planning_code =
DECODE(mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM,NULL,
mrp_planning_code),
description =
DECODE(description,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
description),
attribute_category =
DECODE(attribute_category,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute_category),
attribute1 =
DECODE(attribute1,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute1),
attribute2 =
DECODE(attribute2,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute2),
attribute3 =
DECODE(attribute3,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute3),
attribute4 =
DECODE(attribute4,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute4),
attribute5 =
DECODE(attribute5,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute5),
attribute6 =
DECODE(attribute6,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute6),
attribute7 =
DECODE(attribute7,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute7),
attribute8 =
DECODE(attribute8,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute8),
attribute9 =
DECODE(attribute9,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute9),
attribute10 =
DECODE(attribute10,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute10),
attribute11 =
DECODE(attribute11,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute11),
attribute12 =
DECODE(attribute12,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute12),
attribute13 =
DECODE(attribute13,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute13),
attribute14 =
DECODE(attribute14,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute14),
attribute15 =
DECODE(attribute15,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
attribute15),
first_article_status =
DECODE(first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
first_article_status),
approval_status =
DECODE(approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
approval_status),
start_date =
DECODE(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
start_date),
end_date =
DECODE(end_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
end_date)
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE
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(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
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
);
UPDATE ego_aml_intf intf
SET (mrp_planning_code
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,first_article_status
,approval_status
,start_date
,end_date
)
= (SELECT
DECODE(intf.mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM,NULL,
NULL,prod.mrp_planning_code,
intf.mrp_planning_code),
DECODE(intf.description,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.description,
intf.description),
DECODE(intf.attribute_category,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute_category,
intf.attribute_category),
DECODE(intf.attribute1,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute1,
intf.attribute1),
DECODE(intf.attribute2,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute2,
intf.attribute2),
DECODE(intf.attribute3,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute3,
intf.attribute3),
DECODE(intf.attribute4,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute4,
intf.attribute4),
DECODE(intf.attribute5,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute5,
intf.attribute5),
DECODE(intf.attribute6,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute6,
intf.attribute6),
DECODE(intf.attribute7,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute7,
intf.attribute7),
DECODE(intf.attribute8,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute8,
intf.attribute8),
DECODE(intf.attribute9,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute9,
intf.attribute9),
DECODE(intf.attribute10,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute10,
intf.attribute10),
DECODE(intf.attribute11,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute11,
intf.attribute11),
DECODE(intf.attribute12,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute12,
intf.attribute12),
DECODE(intf.attribute13,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute13,
intf.attribute13),
DECODE(intf.attribute14,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute14,
intf.attribute14),
DECODE(intf.attribute15,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.attribute15,
intf.attribute15),
DECODE(intf.first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.first_article_status,
intf.first_article_status),
DECODE(intf.approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
NULL,prod.approval_status,
intf.approval_status),
DECODE(intf.start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
NULL,prod.start_date,
intf.start_date),
DECODE(intf.end_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
NULL,prod.end_date,
intf.end_date)
FROM mtl_mfg_part_numbers prod
WHERE intf.inventory_item_id = prod.inventory_item_id
AND intf.organization_id = prod.organization_id
AND intf.manufacturer_id = prod.manufacturer_id
AND intf.mfg_part_num = prod.mfg_part_num
)
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
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(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
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
);
,p_message => 'Populate intf table with prod data for UPDATE done'
);
SELECT *
FROM ego_aml_intf
WHERE data_set_id = cp_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_CREATE
,EGO_ITEM_PUB.G_TTYPE_UPDATE
)
ORDER BY attribute_category desc
FOR UPDATE OF transaction_id;
SELECT COUNT(*)
INTO l_count
FROM ego_aml_intf
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_CREATE
,EGO_ITEM_PUB.G_TTYPE_UPDATE
)
AND ( attribute_category IS NOT NULL
OR attribute1 IS NOT NULL
OR attribute2 IS NOT NULL
OR attribute3 IS NOT NULL
OR attribute4 IS NOT NULL
OR attribute5 IS NOT NULL
OR attribute6 IS NOT NULL
OR attribute7 IS NOT NULL
OR attribute8 IS NOT NULL
OR attribute9 IS NOT NULL
OR attribute10 IS NOT NULL
OR attribute11 IS NOT NULL
OR attribute12 IS NOT NULL
OR attribute13 IS NOT NULL
OR attribute14 IS NOT NULL
OR attribute15 IS NOT NULL
);
UPDATE ego_aml_intf
SET process_flag = G_PS_DFF_INVALID,
prog_int_char2 = fnd_flex_descval.error_message
WHERE CURRENT OF c_item_records;
UPDATE ego_aml_intf intf
SET attribute1 =
(SELECT CASE WHEN l_dff_fields_used.attribute1 = FND_API.G_TRUE
THEN intf.attribute1 ELSE NULL END
FROM DUAL)
,attribute2 =
(SELECT CASE WHEN l_dff_fields_used.attribute2 = FND_API.G_TRUE
THEN intf.attribute2 ELSE NULL END
FROM DUAL)
,attribute3 =
(SELECT CASE WHEN l_dff_fields_used.attribute3 = FND_API.G_TRUE
THEN intf.attribute3 ELSE NULL END
FROM DUAL)
,attribute4 =
(SELECT CASE WHEN l_dff_fields_used.attribute4 = FND_API.G_TRUE
THEN intf.attribute4 ELSE NULL END
FROM DUAL)
,attribute5 =
(SELECT CASE WHEN l_dff_fields_used.attribute5 = FND_API.G_TRUE
THEN intf.attribute5 ELSE NULL END
FROM DUAL)
,attribute6 =
(SELECT CASE WHEN l_dff_fields_used.attribute6 = FND_API.G_TRUE
THEN intf.attribute6 ELSE NULL END
FROM DUAL)
,attribute7 =
(SELECT CASE WHEN l_dff_fields_used.attribute7 = FND_API.G_TRUE
THEN intf.attribute7 ELSE NULL END
FROM DUAL)
,attribute8 =
(SELECT CASE WHEN l_dff_fields_used.attribute8 = FND_API.G_TRUE
THEN intf.attribute8 ELSE NULL END
FROM DUAL)
,attribute9 =
(SELECT CASE WHEN l_dff_fields_used.attribute9 = FND_API.G_TRUE
THEN intf.attribute9 ELSE NULL END
FROM DUAL)
,attribute10 =
(SELECT CASE WHEN l_dff_fields_used.attribute10 = FND_API.G_TRUE
THEN intf.attribute10 ELSE NULL END
FROM DUAL)
,attribute11 =
(SELECT CASE WHEN l_dff_fields_used.attribute11 = FND_API.G_TRUE
THEN intf.attribute11 ELSE NULL END
FROM DUAL)
,attribute12 =
(SELECT CASE WHEN l_dff_fields_used.attribute12 = FND_API.G_TRUE
THEN intf.attribute12 ELSE NULL END
FROM DUAL)
,attribute13 =
(SELECT CASE WHEN l_dff_fields_used.attribute13 = FND_API.G_TRUE
THEN intf.attribute13 ELSE NULL END
FROM DUAL)
,attribute14 =
(SELECT CASE WHEN l_dff_fields_used.attribute14 = FND_API.G_TRUE
THEN intf.attribute14 ELSE NULL END
FROM DUAL)
,attribute15 =
(SELECT CASE WHEN l_dff_fields_used.attribute15 = FND_API.G_TRUE
THEN intf.attribute15 ELSE NULL END
FROM DUAL)
WHERE CURRENT OF c_item_records;
UPDATE ego_aml_intf
SET process_flag = G_PS_DFF_INVALID
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE
AND (attribute1 IS NOT NULL
OR
attribute2 IS NOT NULL
OR
attribute3 IS NOT NULL
OR
attribute4 IS NOT NULL
OR
attribute5 IS NOT NULL
OR
attribute6 IS NOT NULL
OR
attribute7 IS NOT NULL
OR
attribute8 IS NOT NULL
OR
attribute9 IS NOT NULL
OR
attribute10 IS NOT NULL
OR
attribute11 IS NOT NULL
OR
attribute12 IS NOT NULL
OR
attribute13 IS NOT NULL
OR
attribute14 IS NOT NULL
OR
attribute15 IS NOT NULL
);
UPDATE ego_aml_intf
SET process_flag = G_PS_DFF_VAL_COMPLETE
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_VAL_TO_ID_COMPLETE;
INSERT INTO mtl_mfg_part_numbers
(manufacturer_id
,mfg_part_num
,inventory_item_id
,organization_id
,mrp_planning_code
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,first_article_status
,approval_status
,start_date
,end_date
,request_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
manufacturer_id
,mfg_part_num
,inventory_item_id
,organization_id
,mrp_planning_code
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,first_article_status
,approval_status
,start_date
,end_date
,request_id
,G_SYSDATE
,G_USER_ID
,G_SYSDATE
,G_USER_ID
,G_LOGIN_ID
FROM ego_aml_intf
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_DFF_VAL_COMPLETE
AND transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE;
,p_message => 'Insert into production table done'
);
UPDATE mtl_mfg_part_numbers prod SET
(mrp_planning_code
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,first_article_status
,approval_status
,start_date
,end_date
,request_id
,last_update_date
,last_updated_by
,last_update_login)
= (SELECT intf.mrp_planning_code
,intf.description
,intf.attribute_category
,intf.attribute1
,intf.attribute2
,intf.attribute3
,intf.attribute4
,intf.attribute5
,intf.attribute6
,intf.attribute7
,intf.attribute8
,intf.attribute9
,intf.attribute10
,intf.attribute11
,intf.attribute12
,intf.attribute13
,intf.attribute14
,intf.attribute15
,intf.first_article_status
,intf.approval_status
,intf.start_date
,intf.end_date
,intf.request_id
,G_SYSDATE
,G_USER_ID
,G_LOGIN_ID
FROM ego_aml_intf intf
WHERE intf.data_set_id = p_data_set_id
AND intf.process_flag = G_PS_DFF_VAL_COMPLETE
AND intf.transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
AND intf.inventory_item_id = prod.inventory_item_id
AND intf.organization_id = prod.organization_id
AND intf.manufacturer_id = prod.manufacturer_id
AND intf.mfg_part_num = prod.mfg_part_num
)
WHERE EXISTS (select 1
from ego_aml_intf intf1
where intf1.data_set_id = p_data_set_id
and intf1.process_flag = G_PS_DFF_VAL_COMPLETE
and intf1.transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
and intf1.inventory_item_id = prod.inventory_item_id
and intf1.organization_id = prod.organization_id
and intf1.manufacturer_id = prod.manufacturer_id
and intf1.mfg_part_num = prod.mfg_part_num
);
,p_message => 'Update into production table done'
);
DELETE mtl_mfg_part_numbers prod
WHERE EXISTS
(Select 1
From ego_aml_intf intf
Where intf.data_set_id = p_data_set_id
and intf.process_flag = G_PS_DFF_VAL_COMPLETE
and intf.transaction_type = EGO_ITEM_PUB.G_TTYPE_DELETE
and intf.inventory_item_id = prod.inventory_item_id
and intf.organization_id = prod.organization_id
and intf.manufacturer_id = prod.manufacturer_id
and intf.mfg_part_num = prod.mfg_part_num
);
UPDATE ego_aml_intf
SET process_flag = G_PS_SUCCESS
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_DFF_VAL_COMPLETE;
SELECT *
FROM ego_aml_intf
WHERE data_set_id = cp_data_set_id
AND process_flag > G_PS_SUCCESS;
UPDATE ego_aml_intf aml_intf
SET manufacturer_name =
(Select manufacturer_name
from mtl_manufacturers manu
where manu.manufacturer_id = aml_intf.manufacturer_id)
WHERE data_set_id = p_data_set_id
AND manufacturer_id IS NOT NULL
AND process_flag IN (G_PS_CREATE_REC_EXISTS
,G_PS_REC_NOT_EXISTS
,G_PS_DUP_INTF_RECORDS
);
UPDATE ego_aml_intf aml_intf
SET organization_code =
(Select organization_code
from mtl_parameters mp
where mp.organization_id = aml_intf.organization_id)
WHERE data_set_id = p_data_set_id
AND organization_id IS NOT NULL
AND process_flag IN (G_PS_ITEM_ERR
,G_PS_CREATE_REC_EXISTS
,G_PS_REC_NOT_EXISTS
,G_PS_DUP_INTF_RECORDS
,G_PS_CHANGE_NOT_ALLOWED
,G_PS_NO_AML_PRIV
);
IF error_rec.transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE THEN
l_err_msg_name := 'EGO_MPN_NOT_EXISTS_UPDATE';
l_err_msg_name := 'EGO_MPN_NOT_EXISTS_DELETE';
SELECT name
INTO l_dummy_message
FROM pa_ego_phases_v
WHERE proj_element_id = error_rec.prog_int_num3;
l_err_token_table.DELETE();
UPDATE ego_aml_intf
SET process_flag = G_PS_GENERIC_ERROR
WHERE data_set_id = p_data_set_id
AND process_flag > G_PS_SUCCESS;
Procedure Delete_AML_Interface_Lines (
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_data_set_id IN NUMBER
,p_delete_line_type IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
---------------------------------------------------------------------------
-- Start of comments
-- API name : Delete AML Interface Lines
-- Type : Public
-- Pre-reqs : None
-- FUNCTION : To delete Interface Lines and corresponding error messages
-- if logged.
--
-- Return Parameter:
-- x_return_status
-- 'S' if successful
-- 'E' in case of any errors
--
---------------------------------------------------------------------------
l_api_version NUMBER;
l_api_name := 'DELETE_AML_INTF_LINES';
||' p_delete_line_type -'||p_delete_line_type
);
p_delete_line_type NOT IN
(EGO_ITEM_PUB.G_INTF_DELETE_ALL
,EGO_ITEM_PUB.G_INTF_DELETE_ERROR
,EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS
,EGO_ITEM_PUB.G_INTF_DELETE_NONE
)
) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
SAVEPOINT DELETE_AML_INTF_LINES_SP;
IF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_ALL THEN
--
-- delete all lines
--
DELETE MTL_INTERFACE_ERRORS
WHERE table_name = l_table_name
AND transaction_id IN
(SELECT transaction_id
FROM EGO_AML_INTF
WHERE data_set_id = p_data_set_id
);
DELETE EGO_AML_INTF
WHERE data_set_id = p_data_set_id;
ELSIF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_ERROR THEN
--
-- delete all error lines
--
DELETE MTL_INTERFACE_ERRORS
WHERE table_name = l_table_name
AND transaction_id IN
(SELECT transaction_id
FROM EGO_AML_INTF
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_GENERIC_ERROR
);
DELETE EGO_AML_INTF
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_GENERIC_ERROR;
ELSIF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS THEN
--
-- delete all success lines
--
DELETE EGO_AML_INTF
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_SUCCESS;
ROLLBACK TO DELETE_AML_INTF_LINES_SP;
END Delete_AML_Interface_Lines;
,p_delete_line_type IN NUMBER
,p_mode IN VARCHAR2
,p_perform_security_check IN VARCHAR2
) IS
---------------------------------------------------------------------------
-- Start of comments
-- API name : Load_Interface_Lines
-- Type : Public
-- Pre-reqs : None
-- FUNCTION : To bulkload the Interface records into the Production
-- and Pending changes table.
--
-- Parameters:
-- IN : p_data_set_id VARCHAR2
-- batch identifier
-- : p_delete_line_type NUMBER
-- How the lines are to be processed in the interface table:
-- DELETE_ALL = 0 (delete all lines)
-- DELETE_ERROR = 3 (delete all error lines)
-- DELETE_SUCCESS = 7 (delete all successful lines)
-- : p_mode VARCHAR2
-- currently only mode 'NORMAL' is supported
-- How the data to be processed:
-- MODE_HISTORICAL = 'HISTORICAL'
-- user is populating historical data, so no date
-- check and security check will be performed.
-- MODE_NORMAL = 'NORMAL'
-- user is populating normal data, so perform date
-- check and security check.
-- : p_perform_security_check VARCHAR2
-- currently only FND_API.G_TRUE is supported
-- Whether security check needs to be done
-- FND_API.G_TRUE - Perform data security check
-- FND_API.G_FALSE - No data security check is done
--
--
-- OUT : ERRBUF VARCHAR2
-- has the error message details
-- RETCODE VARCHAR2
-- '0' if the program is success
-- '1' if the program has a warning
-- '2' if the program has an error
--
---------------------------------------------------------------------------
l_api_version NUMBER;
l_delete_line_type NUMBER;
SELECT 'S' INTO l_return_status
FROM EGO_AML_INTF
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_FLAG = G_PS_TO_BE_PROCESSED
AND ROWNUM = 1;
l_delete_line_type
:= NVL(p_delete_line_type, EGO_ITEM_PUB.G_INTF_DELETE_NONE);
UPDATE ego_aml_intf
SET process_flag = G_PS_IN_PROCESS,
transaction_type = UPPER(transaction_type),
transaction_id = NVL(transaction_id, EGO_IPI_TRANSACTION_ID_S.nextval),
first_article_status =
(SELECT CASE WHEN
(first_article_status_meaning = EGO_ITEM_PUB.G_INTF_NULL_CHAR
AND
first_article_status IS NULL
)
THEN EGO_ITEM_PUB.G_INTF_NULL_CHAR
ELSE first_article_status
END
FROM DUAL),
approval_status =
(SELECT CASE WHEN
(approval_status_meaning = EGO_ITEM_PUB.G_INTF_NULL_CHAR
AND
approval_status IS NULL
)
THEN EGO_ITEM_PUB.G_INTF_NULL_CHAR
ELSE approval_status
END
FROM DUAL),
request_id = G_REQUEST_ID,
program_application_id = G_PROG_APPID,
program_id = G_PROG_ID,
program_update_date = SYSDATE,
prog_int_num1 = NULL,
prog_int_num2 = NULL,
prog_int_num3 = NULL,
prog_int_num4 = NULL,
prog_int_char1 = NULL,
prog_int_char2 = 'N'
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_TO_BE_PROCESSED;
UPDATE ego_aml_intf
SET process_flag = G_PS_MAND_PARAM_MISSING
WHERE data_set_id = p_data_set_id
AND process_flag = G_PS_IN_PROCESS
AND ( mfg_part_num IS NULL
OR
(manufacturer_id IS NULL AND manufacturer_name IS NULL)
OR
(organization_id IS NULL AND organization_code IS NULL)
OR
(inventory_item_id IS NULL AND item_number IS NULL)
);
l_err_msg_sql := 'SELECT INTF.ITEM_NUMBER as ITEM_NUMBER, '||
' INTF.ORGANIZATION_CODE as ORGANIZATINO_CODE, '||
-- ' NULL AS REVISION_CODE, '||
' MIERR.ERROR_MESSAGE as ERROR_MESSAGE '||
' FROM EGO_AML_INTF INTF, MTL_INTERFACE_ERRORS MIERR '||
' WHERE MIERR.TRANSACTION_ID = INTF.TRANSACTION_ID '||
' AND MIERR.REQUEST_ID = INTF.REQUEST_ID '||
' AND MIERR.request_id = :1';
,p_selectQuery => l_err_msg_sql
,p_request_id => G_REQUEST_ID
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IF p_delete_line_type <> EGO_ITEM_PUB.G_INTF_DELETE_NONE THEN
Delete_AML_Interface_Lines(p_api_version => 1.0
,p_commit => FND_API.G_FALSE
,p_data_set_id => p_data_set_id
,p_delete_line_type => p_delete_line_type
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);