[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_DELETE_ACD_TYPE CONSTANT VARCHAR2(10) := 'DELETE';
SELECT inst.status
FROM fnd_product_installations inst, fnd_application app
WHERE inst.application_id = app.application_id
AND app.application_short_name = cp_app_short_name
AND inst.status <> 'N';*/
SELECT count(*)
INTO l_count
FROM eng_change_mgmt_types_vl
WHERE disable_flag = 'N';
SELECT *
FROM ego_mfg_part_num_chgs
WHERE manufacturer_id = cp_mfg_id
AND inventory_item_id = cp_item_id
AND organization_id = cp_org_id
AND mfg_part_num = cp_mfg_part_num
AND change_line_id = cp_change_line_id
AND acd_type = cp_acd_type;
SELECT *
FROM mtl_mfg_part_numbers
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id
AND manufacturer_id = cp_manufacturer_id
AND mfg_part_num = cp_mfg_part_num;
SELECT ORGANIZATION_ID, MANUFACTURER_ID, MFG_PART_NUM,
INVENTORY_ITEM_ID, CHANGE_LINE_ID, ACD_TYPE
BULK COLLECT INTO
l_organization_id_tbl, l_manufacturer_id_tbl,
l_mfg_part_num_tbl, l_inventory_item_id_tbl,
l_change_line_id_tbl, l_acd_type_tbl
FROM ego_mfg_part_num_chgs
WHERE NVL(change_id,-1) = NVL(NVL(p_change_id, change_id),-1)
AND NVL(change_line_id, -1) =
NVL(NVL(p_change_line_id, change_line_id),-1)
AND implmentation_date IS NULL
AND acd_type IN
(G_ADD_ACD_TYPE, G_CHANGE_ACD_TYPE, G_DELETE_ACD_TYPE);
IF l_acd_type_tbl(l_pend_index) IN (G_CHANGE_ACD_TYPE, G_DELETE_ACD_TYPE) THEN
OPEN c_get_production_data
(cp_inventory_item_id => l_inventory_item_id_tbl(l_pend_index)
,cp_organization_id => l_organization_id_tbl(l_pend_index)
,cp_manufacturer_id => l_manufacturer_id_tbl(l_pend_index)
,cp_mfg_part_num => l_mfg_part_num_tbl(l_pend_index));
INSERT INTO ego_mfg_part_num_chgs
(manufacturer_id
,mfg_part_num
,inventory_item_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,organization_id
,mrp_planning_code
,description
,first_article_status
,approval_status
,change_id
,change_line_id
,acd_type
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,implmentation_date
,start_date
,end_date)
VALUES
(l_prod_data_row.manufacturer_id
,l_prod_data_row.mfg_part_num
,l_prod_data_row.inventory_item_id
,l_sysdate
,l_user_id
,l_sysdate
,l_user_id
,l_login_id
,l_prod_data_row.organization_id
,l_prod_data_row.mrp_planning_code
,l_prod_data_row.description
,l_prod_data_row.first_article_status
,l_prod_data_row.approval_status
,p_change_id
,p_change_line_id
,G_HISTORY_ACD_TYPE
,l_prod_data_row.attribute_category
,l_prod_data_row.attribute1
,l_prod_data_row.attribute2
,l_prod_data_row.attribute3
,l_prod_data_row.attribute4
,l_prod_data_row.attribute5
,l_prod_data_row.attribute6
,l_prod_data_row.attribute7
,l_prod_data_row.attribute8
,l_prod_data_row.attribute9
,l_prod_data_row.attribute10
,l_prod_data_row.attribute11
,l_prod_data_row.attribute12
,l_prod_data_row.attribute13
,l_prod_data_row.attribute14
,l_prod_data_row.attribute15
,NULL
,l_prod_data_row.start_date
,l_prod_data_row.end_date);
UPDATE mtl_mfg_part_numbers
SET first_article_status = l_pend_data_row.first_article_status
,approval_status = l_pend_data_row.approval_status
,start_date = l_pend_data_row.start_date
,end_date = l_pend_data_row.end_date
,attribute1 = l_pend_data_row.attribute1 --Added attribute 1 - 15 for bug 6109336
,attribute2 = l_pend_data_row.attribute2
,attribute3 = l_pend_data_row.attribute3
,attribute4 = l_pend_data_row.attribute4
,attribute5 = l_pend_data_row.attribute5
,attribute6 = l_pend_data_row.attribute6
,attribute7 = l_pend_data_row.attribute7
,attribute8 = l_pend_data_row.attribute8
,attribute9 = l_pend_data_row.attribute9
,attribute10 = l_pend_data_row.attribute10
,attribute11 = l_pend_data_row.attribute11
,attribute12 = l_pend_data_row.attribute12
,attribute13 = l_pend_data_row.attribute13
,attribute14 = l_pend_data_row.attribute14
,attribute15 = l_pend_data_row.attribute15
,last_update_date = l_sysdate
,last_updated_by = l_user_id
,last_update_login = l_login_id
WHERE manufacturer_id = l_manufacturer_id_tbl(l_pend_index)
AND inventory_item_id = l_inventory_item_id_tbl(l_pend_index)
AND organization_id = l_organization_id_tbl(l_pend_index)
AND mfg_part_num = l_mfg_part_num_tbl(l_pend_index);
,p_message => 'successfully updated production row'
);
event_dml_Type := 'UPDATE';
ELSIF l_acd_type_tbl(l_pend_index) = G_DELETE_ACD_TYPE THEN
-- delete the record from mtl_mfg_part_numbers
DELETE mtl_mfg_part_numbers
WHERE manufacturer_id = l_manufacturer_id_tbl(l_pend_index)
AND inventory_item_id = l_inventory_item_id_tbl(l_pend_index)
AND organization_id = l_organization_id_tbl(l_pend_index)
AND mfg_part_num = l_mfg_part_num_tbl(l_pend_index);
,p_message => 'successfully deleted production row'
);
event_dml_Type := 'DELETE';
UPDATE ego_mfg_part_num_chgs
SET implmentation_date = l_sysdate
,last_update_date = l_sysdate
,last_updated_by = l_user_id
,last_update_login = l_login_id
WHERE manufacturer_id = l_manufacturer_id_tbl(l_pend_index)
AND inventory_item_id = l_inventory_item_id_tbl(l_pend_index)
AND organization_id = l_organization_id_tbl(l_pend_index)
AND mfg_part_num = l_mfg_part_num_tbl(l_pend_index)
AND change_line_id = l_change_line_id_tbl(l_pend_index)
AND acd_type = l_acd_type_tbl(l_pend_index);
,p_message => 'successfully updated pending row as implemented'
);
INSERT INTO mtl_mfg_part_numbers
(manufacturer_id
,mfg_part_num
,inventory_item_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,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)
VALUES
(l_pend_data_row.manufacturer_id
,l_pend_data_row.mfg_part_num
,l_pend_data_row.inventory_item_id
,l_sysdate
,l_user_id
,l_sysdate
,l_user_id
,l_login_id
,l_pend_data_row.organization_id
,l_pend_data_row.mrp_planning_code
,l_pend_data_row.description
,l_pend_data_row.attribute_category
,l_pend_data_row.attribute1
,l_pend_data_row.attribute2
,l_pend_data_row.attribute3
,l_pend_data_row.attribute4
,l_pend_data_row.attribute5
,l_pend_data_row.attribute6
,l_pend_data_row.attribute7
,l_pend_data_row.attribute8
,l_pend_data_row.attribute9
,l_pend_data_row.attribute10
,l_pend_data_row.attribute11
,l_pend_data_row.attribute12
,l_pend_data_row.attribute13
,l_pend_data_row.attribute14
,l_pend_data_row.attribute15
,l_pend_data_row.first_article_status
,l_pend_data_row.approval_status
,l_pend_data_row.start_date
,l_pend_data_row.end_date);
UPDATE ego_mfg_part_num_chgs
SET implmentation_date = l_sysdate
,last_update_date = l_sysdate
,last_updated_by = l_user_id
,last_update_login = l_login_id
WHERE manufacturer_id = l_manufacturer_id_tbl(l_pend_index)
AND inventory_item_id = l_inventory_item_id_tbl(l_pend_index)
AND organization_id = l_organization_id_tbl(l_pend_index)
AND mfg_part_num = l_mfg_part_num_tbl(l_pend_index)
AND change_line_id = l_change_line_id_tbl(l_pend_index)
AND acd_type = l_acd_type_tbl(l_pend_index);
,p_message => 'successfully updated pending row as implemented'
);
IF event_dml_type IN( 'CREATE', 'DELETE', 'UPDATE') THEN
log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
,p_module => l_api_name
,p_message => ' calling Business Event '
);
Procedure Delete_AML_Pending_Changes
(p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,p_inventory_item_id IN NUMBER
,p_organization_id IN NUMBER
,p_manufacturer_id IN NUMBER
,p_mfg_part_num IN VARCHAR2
,p_change_id IN NUMBER
,p_change_line_id IN NUMBER
,p_acd_type IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
) IS
----------------------------------------------------------------------------
-- Start OF comments
-- API name : Delete_AML_Pending_Changes
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Delete the pending changes from EGO_MFG_PART_NUM_CHGS
--
-- Parameters:
-- IN : p_inventory_item_id NUMBER
-- : p_organization_id NUMBER
-- : p_manufacturer_id NUMBER
-- : p_mfg_part_num VARCHAR2
-- : p_change_id NUMBER
-- : p_change_line_id NUMBER
-- : p_acd_type VARCHAR2
--
-- OUT : x_return_status VARCHAR2
-- x_msg_count NUMBER
-- x_msg_data VARCHAR2
--
----------------------------------------------------------------------------
l_api_version NUMBER := 1.0;
l_api_name VARCHAR2(50) := 'DELETE_AML_PENDING_CHANGES';
SAVEPOINT DELETE_AML_PENDING_CHANGES;
DELETE EGO_MFG_PART_NUM_CHGS
WHERE change_id = NVL(p_change_id, change_id)
AND change_line_id = NVL(p_change_line_id, change_line_id)
AND implmentation_date IS NULL;
DELETE EGO_MFG_PART_NUM_CHGS
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND manufacturer_id = p_manufacturer_id
AND mfg_part_num = p_mfg_part_num
AND change_id = p_change_id
AND change_line_id = p_change_line_id
-- fix for 3439187
-- AND acd_type = p_acd_type
AND implmentation_date IS NULL;
ROLLBACK TO DELETE_AML_PENDING_CHANGES;
END Delete_AML_Pending_Changes;
SELECT inst.status
FROM fnd_product_installations inst, fnd_application app
WHERE inst.application_id = app.application_id
AND app.application_short_name = cp_app_short_name
AND inst.status <> 'N';
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, fnd_objects o
WHERE o.obj_name = G_EGO_ITEM
AND olc.object_id = o.object_id
AND olc.lifecycle_id = cp_lifecycle_id
AND olc.object_classification_code = to_char(ic.item_catalog_group_id)
)
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = cp_catalog_category_id;
' SELECT item_catalog_group_id, lifecycle_id, current_phase_id,approval_status' ||
' FROM mtl_system_items_b ' ||
' WHERE inventory_item_id = :1' ||
' AND organization_id = :2';
SELECT name
INTO x_policy_name
FROM pa_ego_phases_v
WHERE proj_element_id = l_current_phase_id;
SELECT concatenated_segments
INTO x_item_number
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT organization_name
INTO x_org_name
FROM org_organization_definitions
WHERE organization_id = p_organization_id;
SELECT inst.status
FROM fnd_product_installations inst, fnd_application app
WHERE inst.application_id = app.application_id
AND app.application_short_name = cp_app_short_name
AND inst.status <> 'N';
l_dynamic_sql := 'SELECT PARTY_ID FROM EGO_USER_V where user_name = :1';
l_dynamic_sql := 'SELECT PARTY_ID FROM EGO_USER_V where user_id = :1';
l_dynamic_sql := 'SELECT EGO_DATA_SECURITY.CHECK_FUNCTION ('
||':1, '--l_api_version
||':2, '--p_privilege_name
||':3, '--G_EGO_ITEM
||':4, '--p_inventory_item_id
||':5, '--p_organization_id
||':6, '-- pk3 value NULL
||':7, '-- pk4 value NULL
||':8, '-- pk5 value NULL
||':9 )'--l_party_key
||' FROM DUAL';
SELECT 'X'
INTO l_return_status
FROM mtl_mfg_part_numbers
WHERE manufacturer_id = p_manufacturer_id
AND rownum = 1;
SELECT 'X'
INTO l_return_status
FROM DUAL
WHERE EXISTS
( SELECT 'Y' FROM ego_mfg_part_num_chgs
WHERE manufacturer_id = p_manufacturer_id
);
SELECT 'X'
INTO l_return_status
FROM ego_cat_grp_mfg_assocs
WHERE manufacturer_id = p_manufacturer_id
AND rownum = 1;
SELECT manufacturer_name
INTO l_mfg_name
FROM mtl_manufacturers
WHERE manufacturer_id = p_manufacturer_id;