[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ozf_code_conversions_all_s.nextval INTO l_code_conversion_id FROM DUAL;
OZF_CODE_CONVERSION_PKG.Insert_Row(
px_code_conversion_id => l_code_conversion_id,
px_object_version_number => l_object_version_number,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
px_org_id => l_org_id,
p_party_id => l_code_conversion_rec.party_id,
p_cust_account_id => l_code_conversion_rec.cust_account_id,
p_code_conversion_type => l_code_conversion_rec.code_conversion_type,
p_external_code => l_code_conversion_rec.external_code,
p_internal_code => l_code_conversion_rec.internal_code,
p_description => l_code_conversion_rec.description,
p_start_date_active => nvl(l_code_conversion_rec.start_date_active,sysdate),
p_end_date_active => l_code_conversion_rec.end_date_active,
p_attribute_category => l_code_conversion_rec.attribute_category,
p_attribute1 => l_code_conversion_rec.attribute1,
p_attribute2 => l_code_conversion_rec.attribute2,
p_attribute3 => l_code_conversion_rec.attribute3,
p_attribute4 => l_code_conversion_rec.attribute4,
p_attribute5 => l_code_conversion_rec.attribute5,
p_attribute6 => l_code_conversion_rec.attribute6,
p_attribute7 => l_code_conversion_rec.attribute7,
p_attribute8 => l_code_conversion_rec.attribute8,
p_attribute9 => l_code_conversion_rec.attribute9,
p_attribute10 => l_code_conversion_rec.attribute10,
p_attribute11 => l_code_conversion_rec.attribute11,
p_attribute12 => l_code_conversion_rec.attribute12,
p_attribute13 => l_code_conversion_rec.attribute13,
p_attribute14 => l_code_conversion_rec.attribute14,
p_attribute15 => l_code_conversion_rec.attribute15);
OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' after insert call - code conversion id' || l_code_conversion_id);
OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' after insert call - obj version no ' || l_code_conversion_rec.Object_Version_Number);
PROCEDURE Update_code_conversion
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := 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_code_conversion_tbl IN code_conversion_tbl_type ,
x_object_version_number OUT NOCOPY JTF_NUMBER_TABLE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_code_conversion';
SELECT code_conversion_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
party_id,
cust_account_id,
code_conversion_type,
external_code,
internal_code,
description,
start_date_active,
end_date_active,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
security_group_id
FROM ozf_code_conversions_all
WHERE code_conversion_id = cv_code_conversion_id;
SELECT org_id FROM ozf_sys_parameters;
SAVEPOINT update_code_conversion_pvt;
,l_code_conversion_rec.last_update_date
,l_code_conversion_rec.last_updated_by
,l_code_conversion_rec.creation_date
,l_code_conversion_rec.created_by
,l_code_conversion_rec.last_update_login
,l_code_conversion_rec.org_id
,l_code_conversion_rec.party_id
,l_code_conversion_rec.cust_account_id
,l_code_conversion_rec.code_conversion_type
,l_code_conversion_rec.external_code
,l_code_conversion_rec.internal_code
,l_code_conversion_rec.description
,l_code_conversion_rec.start_date_active
,l_code_conversion_rec.end_date_active
,l_code_conversion_rec.attribute_category
,l_code_conversion_rec.attribute1
,l_code_conversion_rec.attribute2
,l_code_conversion_rec.attribute3
,l_code_conversion_rec.attribute4
,l_code_conversion_rec.attribute5
,l_code_conversion_rec.attribute6
,l_code_conversion_rec.attribute7
,l_code_conversion_rec.attribute8
,l_code_conversion_rec.attribute9
,l_code_conversion_rec.attribute10
,l_code_conversion_rec.attribute11
,l_code_conversion_rec.attribute12
,l_code_conversion_rec.attribute13
,l_code_conversion_rec.attribute14
,l_code_conversion_rec.attribute15
,l_code_conversion_rec.security_group_id;
OZF_UTILITY_PVT.debug_message( 'Updated End Date' || p_code_conversion_rec.End_Date_Active);
OZF_UTILITY_PVT.debug_message( 'Updated Start Date' || p_code_conversion_rec.Start_Date_Active);
OZF_UTILITY_PVT.debug_message ('Cannot update an end dated code conversion map');
p_validation_mode => JTF_PLSQL_API.G_UPDATE,
p_code_conversion_tbl => p_code_conversion_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
OZF_UTILITY_PVT.debug_message( 'Private API: Calling Update table handler');
OZF_CODE_CONVERSION_PKG.Update_Row(
p_code_conversion_id => l_code_conversion_id,
p_object_version_number => p_code_conversion_rec.object_version_number,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_org_id => l_org_id,
p_party_id => p_code_conversion_rec.party_id,
p_cust_account_id => p_code_conversion_rec.cust_account_id,
p_code_conversion_type => p_code_conversion_rec.code_conversion_type,
p_external_code => p_code_conversion_rec.external_code,
p_internal_code => p_code_conversion_rec.internal_code,
p_description => p_code_conversion_rec.description,
p_start_date_active => p_code_conversion_rec.start_date_active,
p_end_date_active => p_code_conversion_rec.end_date_active,
p_attribute_category => p_code_conversion_rec.attribute_category,
p_attribute1 => p_code_conversion_rec.attribute1,
p_attribute2 => p_code_conversion_rec.attribute2,
p_attribute3 => p_code_conversion_rec.attribute3,
p_attribute4 => p_code_conversion_rec.attribute4,
p_attribute5 => p_code_conversion_rec.attribute5,
p_attribute6 => p_code_conversion_rec.attribute6,
p_attribute7 => p_code_conversion_rec.attribute7,
p_attribute8 => p_code_conversion_rec.attribute8,
p_attribute9 => p_code_conversion_rec.attribute9,
p_attribute10 => p_code_conversion_rec.attribute10,
p_attribute11 => p_code_conversion_rec.attribute11,
p_attribute12 => p_code_conversion_rec.attribute12,
p_attribute13 => p_code_conversion_rec.attribute13,
p_attribute14 => p_code_conversion_rec.attribute14,
p_attribute15 => p_code_conversion_rec.attribute15);
ROLLBACK TO update_code_conversion_pvt;
ROLLBACK TO update_code_conversion_pvt;
ROLLBACK TO update_code_conversion_pvt;
END Update_Code_Conversion;
PROCEDURE Update_Code_Conversion_Tbl(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := 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_code_conversion_Tbl IN code_conversion_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_code_conversion_tbl';
l_update_code_conv_tbl code_conversion_tbl_type := code_conversion_tbl_type();
SAVEPOINT update_code_conversion_tbl_pvt;
l_update_code_conv_tbl.extend;
l_update_code_conv_tbl(l_up_cnt) := P_code_conversion_Rec;
OZF_UTILITY_PVT.debug_message('No of rows to be updated: ' || l_up_cnt);
Update_code_conversion (
p_api_version_number => p_api_version_number ,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_Status,
x_msg_count => x_msg_Count,
x_msg_data => x_msg_Data,
p_code_conversion_tbl => l_update_code_conv_tbl,
x_object_version_number => v_object_version_number
);
ROLLBACK TO update_code_conversion_tbl_pvt;
ROLLBACK TO update_code_conversion_tbl_pvt;
ROLLBACK TO update_code_conversion_tbl_pvt;
END Update_Code_Conversion_Tbl;
PROCEDURE Delete_Code_Conversion_Tbl
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := 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_code_conversion_tbl IN code_conversion_Tbl_Type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_code_conversion_tbl';
SAVEPOINT delete_code_conversion_tbl_pvt;
Delete_Code_Conversion(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => X_Return_Status ,
x_msg_count => X_Msg_Count ,
x_msg_data => X_Msg_Data ,
p_code_conversion_id => l_code_conversion_id,
p_object_version_number => l_object_version_number,
p_external_code => P_code_conversion_Tbl(i).external_code ,
p_code_conversion_type => P_code_conversion_Tbl(i).code_conversion_type);
ROLLBACK TO delete_code_conversion_tbl_pvt;
ROLLBACK TO delete_code_conversion_tbl_pvt;
ROLLBACK TO delete_code_conversion_tbl_pvt;
END Delete_Code_Conversion_Tbl;
PROCEDURE Delete_Code_Conversion
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := 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_code_conversion_id IN NUMBER,
p_object_version_number IN NUMBER,
p_external_code IN VARCHAR2,
p_code_conversion_type IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_code_conversion';
SELECT COUNT(customer_reason)
FROM ozf_claims
WHERE customer_reason = cv_external_code;
SELECT MAX(pt_cnt)
FROM (
SELECT COUNT(orig_system_item_number) pt_cnt
FROM ozf_resale_lines_int lin
,ozf_resale_batches bat
WHERE lin.resale_batch_id = bat.resale_batch_id
AND orig_system_item_number = cv_external_code
AND (bat.partner_party_id = cv_party_id OR bat.partner_cust_account_id = cv_account_id)
UNION
SELECT COUNT(orig_system_item_number) pt_cnt
FROM ozf_resale_lines lin
,ozf_resale_batches bat
,ozf_resale_batch_line_maps map
WHERE orig_system_item_number = cv_external_code
AND lin.resale_line_id = map.resale_line_id
AND bat.resale_batch_id = map.resale_batch_id
AND (bat.partner_party_id = cv_party_id OR bat.partner_cust_account_id = cv_account_id) ) x;
SELECT COUNT(orig_system_item_number)
FROM ozf_resale_lines_int lin
WHERE orig_system_item_number = cv_external_code;
SELECT MAX(pt_cnt)
FROM
(
SELECT COUNT(orig_system_agreement_name) pt_cnt
FROM ozf_resale_lines_int lin
,ozf_resale_batches bat
WHERE lin.resale_batch_id = bat.resale_batch_id
AND orig_system_agreement_name = cv_external_code
AND (bat.partner_party_id = cv_party_id OR bat.partner_cust_account_id = cv_account_id)
UNION
SELECT COUNT(orig_system_agreement_name) pt_cnt
FROM ozf_resale_adjustments lin
,ozf_resale_batches bat
WHERE orig_system_agreement_name = cv_external_code
AND bat.resale_batch_id = lin.resale_batch_id
AND (bat.partner_party_id = cv_party_id OR bat.partner_cust_account_id = cv_account_id) ) x;
SELECT COUNT(orig_system_agreement_name)
FROM ozf_resale_lines_int lin
WHERE orig_system_agreement_name = cv_external_code;
SELECT COUNT(orig_system_agreement_uom)
FROM ozf_resale_adjustments lin
,ozf_resale_batches bat
WHERE orig_system_agreement_uom = cv_external_code
AND bat.resale_batch_id = lin.resale_batch_id
AND (bat.partner_party_id = cv_party_id OR bat.partner_cust_account_id = cv_account_id) ;
SELECT COUNT(orig_system_agreement_uom)
FROM ozf_resale_adjustments lin
WHERE orig_system_agreement_uom = cv_external_code;
SAVEPOINT delete_code_conversion_pvt;
OZF_UTILITY_PVT.debug_message('Party ID in delete code conersion: ' || l_party_id);
FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_RSNMAP_DELETE');
l_sql := 'SELECT ( CASE WHEN orig_system_uom = :1 THEN 1'||
' WHEN orig_system_purchase_uom = :1 THEN 1 ';
FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_UOM_DELETE');
FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_UOM_DELETE');
FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_UOM_DELETE');
OZF_UTILITY_PVT.debug_message('l_dummy in delete code conersion: ' || l_dummy);
FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_AGREEMENT_DELETE');
OZF_UTILITY_PVT.debug_message('l_dummy in delete code conersion: ' || l_dummy);
FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_PRODUCT_DELETE');
l_sql := 'SELECT ( CASE WHEN bill_to_party_name = :1 THEN 1'||
' WHEN ship_to_party_name = :1 THEN 1 '||
' ELSE 0 END ) pt_cnt'||
' FROM ';
FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_PARTY_DELETE');
l_sql := 'SELECT ( CASE WHEN bill_to_location = :1 THEN 1'||
' WHEN ship_to_location = :1 THEN 1 '||
' ELSE 0 END ) pt_cnt'||
' FROM ';
FND_MESSAGE.Set_Name('OZF', 'OZF_RESALE_PARTY_SITE_DELETE');
OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
OZF_CODE_CONVERSION_PKG.Delete_Row( p_code_conversion_id => p_code_conversion_id,
p_object_version_number => p_object_version_number );
ROLLBACK TO delete_code_conversion_pvt;
ROLLBACK TO delete_code_conversion_pvt;
ROLLBACK TO delete_code_conversion_pvt;
END Delete_Code_Conversion;
SELECT COUNT(party_id)
FROM ozf_code_conversions
WHERE party_id = cv_party_id
AND code_conversion_type = cv_code_conversion_type -- Bug#11906081
AND org_id = NVL(cv_org_id,0)
AND UPPER(external_code) LIKE UPPER(cv_external_code)
AND TRUNC(start_date_active) <= TRUNC(NVL(cv_start_date_active,SYSDATE))
AND TRUNC(NVL(end_date_active,NVL(cv_end_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_end_date_active,SYSDATE))
AND TRUNC(NVL(end_date_active,NVL(cv_start_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_start_date_active,SYSDATE))
AND cust_account_id IS NULL;
SELECT COUNT(cust_account_id)
FROM ozf_code_conversions
WHERE cust_account_id = cv_cust_account_id
AND code_conversion_type = cv_code_conversion_type -- Bug#11906081
AND org_id = NVL(cv_org_id,0)
AND UPPER(external_code) LIKE UPPER(cv_external_code)
AND TRUNC(start_date_active) <= TRUNC(NVL(cv_start_date_active,SYSDATE))
AND TRUNC(NVL(end_date_active,NVL(cv_start_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_start_date_active,SYSDATE))
AND TRUNC(NVL(end_date_active,NVL(cv_end_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_end_date_active,SYSDATE));
SELECT COUNT(external_code)
FROM ozf_code_conversions
WHERE UPPER(external_code) LIKE UPPER(cv_external_code)
AND code_conversion_type = cv_code_conversion_type -- Bug#11906081
AND org_id = NVL(cv_org_id,0)
AND TRUNC(start_date_active) <= TRUNC(NVL(cv_start_date_active,SYSDATE))
AND TRUNC(NVL(end_date_active,NVL(cv_start_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_start_date_active,SYSDATE))
AND TRUNC(NVL(end_date_active,NVL(cv_end_date_active,SYSDATE)+1)) >= TRUNC(NVL(cv_end_date_active,SYSDATE))
AND party_id IS NULL
AND cust_account_id IS NULL;
SELECT internal_code
FROM ozf_code_conversions
WHERE cust_account_id = cv_cust_account_id
AND UPPER(external_code) LIKE UPPER(cv_external_code)
AND start_date_active <= SYSDATE
AND (end_date_active >= SYSDATE
OR end_date_active IS NULL)
AND code_conversion_type = cv_conv_type;
SELECT internal_code
FROM ozf_code_conversions
WHERE party_id = cv_party_id
AND UPPER(external_code) LIKE UPPER(cv_external_code)
AND start_date_active <= SYSDATE
AND (end_date_active >= SYSDATE
OR end_date_active IS NULL)
AND cust_account_id IS NULL
AND code_conversion_type = cv_conv_type;
SELECT internal_code
FROM ozf_code_conversions
WHERE UPPER(external_code) LIKE UPPER(cv_external_code)
AND start_date_active <= SYSDATE
AND (end_date_active >= SYSDATE
or end_date_active IS NULL)
AND party_id IS NULL
AND cust_account_id IS NULL
AND code_conversion_type = cv_conv_type;
select code_conversion_id from ozf_supp_code_conversions_all where external_code = cv_external_code
and code_conversion_id <> cv_conv_id
and supp_trade_profile_id = cv_supp_trade_profile_id
and ( to_date(cv_start_date_active,'dd-mm-yyyy') between
to_date(start_date_active,'dd-mm-yyyy') and nvl(end_date_active,to_Date('31-12-9999','dd-mm-yyyy'))
or nvl(to_date(cv_end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')) between
to_date(start_date_Active,'dd-mm-yyyy') and nvl(to_date(end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')))
union
select code_conversion_id from ozf_supp_code_conversions_all where internal_code = cv_internal_code
and code_conversion_id <> cv_conv_id
and supp_trade_profile_id = cv_supp_trade_profile_id
and ( to_date(cv_start_date_active,'dd-mm-yyyy') between to_date(start_date_active,'dd-mm-yyyy')
and nvl(end_date_active,to_Date('31-12-9999','dd-mm-yyyy'))
or nvl(to_date(cv_end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')) between
to_date(start_date_Active,'dd-mm-yyyy') and nvl(to_date(end_date_active,'dd-mm-yyyy'),to_Date('31-12-9999','dd-mm-yyyy')));
ELSIF p_validation_mode = JTF_PLSQL_API.g_update THEN
OPEN csr_code_conv(p_supp_code_conversion_rec.supp_trade_profile_id,
p_supp_code_conversion_rec.external_code,
p_supp_code_conversion_rec.internal_code,
p_supp_code_conversion_rec.start_date_active,
p_supp_code_conversion_rec.end_date_active,
p_supp_code_conversion_rec.code_conversion_id);
SELECT internal_code
FROM ozf_supp_code_conversions
WHERE UPPER(external_code) LIKE UPPER(cv_external_code)
AND start_date_active <= SYSDATE
AND (end_date_active >= SYSDATE
or end_date_active IS NULL)
AND code_conversion_type = cv_conv_type
AND supp_trade_profile_id = cv_supp_trade_profile_id;
SELECT ozf_supp_code_conv_all_s.nextval INTO l_code_conversion_id FROM DUAL;
OZF_CODE_CONVERSION_PKG.Insert_Supp_code_conv_Row(
px_code_conversion_id => l_code_conversion_id,
px_object_version_number => l_object_version_number,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
px_org_id => l_org_id,
p_supp_trade_profile_id => l_supp_code_conversion_rec.supp_trade_profile_id,
p_code_conversion_type => l_supp_code_conversion_rec.code_conversion_type,
p_external_code => l_supp_code_conversion_rec.external_code,
p_internal_code => l_supp_code_conversion_rec.internal_code,
p_description => l_supp_code_conversion_rec.description,
p_start_date_active => nvl(l_supp_code_conversion_rec.start_date_active,sysdate),
p_end_date_active => l_supp_code_conversion_rec.end_date_active,
p_attribute_category => l_supp_code_conversion_rec.attribute_category,
p_attribute1 => l_supp_code_conversion_rec.attribute1,
p_attribute2 => l_supp_code_conversion_rec.attribute2,
p_attribute3 => l_supp_code_conversion_rec.attribute3,
p_attribute4 => l_supp_code_conversion_rec.attribute4,
p_attribute5 => l_supp_code_conversion_rec.attribute5,
p_attribute6 => l_supp_code_conversion_rec.attribute6,
p_attribute7 => l_supp_code_conversion_rec.attribute7,
p_attribute8 => l_supp_code_conversion_rec.attribute8,
p_attribute9 => l_supp_code_conversion_rec.attribute9,
p_attribute10 => l_supp_code_conversion_rec.attribute10,
p_attribute11 => l_supp_code_conversion_rec.attribute11,
p_attribute12 => l_supp_code_conversion_rec.attribute12,
p_attribute13 => l_supp_code_conversion_rec.attribute13,
p_attribute14 => l_supp_code_conversion_rec.attribute14,
p_attribute15 => l_supp_code_conversion_rec.attribute15);
OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' after insert call - supp_code conversion id' || l_code_conversion_id);
OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' after insert call - obj version no ' || l_supp_code_conversion_rec.Object_Version_Number);
PROCEDURE Update_supp_code_conversion
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := 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_supp_code_conversion_tbl IN supp_code_conversion_tbl_type ,
x_object_version_number OUT NOCOPY JTF_NUMBER_TABLE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_supp_code_conversion';
SELECT code_conversion_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
supp_trade_profile_id,
code_conversion_type,
external_code,
internal_code,
description,
start_date_active,
end_date_active,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
security_group_id
FROM ozf_supp_code_conversions_all
WHERE code_conversion_id = cv_code_conversion_id;
SELECT org_id FROM ozf_sys_parameters;
SAVEPOINT update_supp_code_conv_pvt;
,l_supp_code_conversion_rec.last_update_date
,l_supp_code_conversion_rec.last_updated_by
,l_supp_code_conversion_rec.creation_date
,l_supp_code_conversion_rec.created_by
,l_supp_code_conversion_rec.last_update_login
,l_supp_code_conversion_rec.org_id
,l_supp_code_conversion_rec.supp_trade_profile_id
,l_supp_code_conversion_rec.code_conversion_type
,l_supp_code_conversion_rec.external_code
,l_supp_code_conversion_rec.internal_code
,l_supp_code_conversion_rec.description
,l_supp_code_conversion_rec.start_date_active
,l_supp_code_conversion_rec.end_date_active
,l_supp_code_conversion_rec.attribute_category
,l_supp_code_conversion_rec.attribute1
,l_supp_code_conversion_rec.attribute2
,l_supp_code_conversion_rec.attribute3
,l_supp_code_conversion_rec.attribute4
,l_supp_code_conversion_rec.attribute5
,l_supp_code_conversion_rec.attribute6
,l_supp_code_conversion_rec.attribute7
,l_supp_code_conversion_rec.attribute8
,l_supp_code_conversion_rec.attribute9
,l_supp_code_conversion_rec.attribute10
,l_supp_code_conversion_rec.attribute11
,l_supp_code_conversion_rec.attribute12
,l_supp_code_conversion_rec.attribute13
,l_supp_code_conversion_rec.attribute14
,l_supp_code_conversion_rec.attribute15
,l_supp_code_conversion_rec.security_group_id;
OZF_UTILITY_PVT.debug_message( 'Updated End Date' || p_supp_code_conversion_rec.End_Date_Active);
OZF_UTILITY_PVT.debug_message( 'Updated Start Date' || p_supp_code_conversion_rec.Start_Date_Active);
OZF_UTILITY_PVT.debug_message ('Cannot update an end dated code conversion map');
p_validation_mode => JTF_PLSQL_API.G_UPDATE,
p_supp_code_conversion_tbl => p_supp_code_conversion_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
OZF_UTILITY_PVT.debug_message( 'Private API: Calling Update table handler');
OZF_CODE_CONVERSION_PKG.Update_Supp_Code_Conv_Row(
p_code_conversion_id => l_code_conversion_id,
p_object_version_number => p_supp_code_conversion_rec.object_version_number,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_org_id => l_org_id,
p_supp_trade_profile_id => p_supp_code_conversion_rec.supp_trade_profile_id,
p_code_conversion_type => p_supp_code_conversion_rec.code_conversion_type,
p_external_code => p_supp_code_conversion_rec.external_code,
p_internal_code => p_supp_code_conversion_rec.internal_code,
p_description => p_supp_code_conversion_rec.description,
p_start_date_active => p_supp_code_conversion_rec.start_date_active,
p_end_date_active => p_supp_code_conversion_rec.end_date_active,
p_attribute_category => p_supp_code_conversion_rec.attribute_category,
p_attribute1 => p_supp_code_conversion_rec.attribute1,
p_attribute2 => p_supp_code_conversion_rec.attribute2,
p_attribute3 => p_supp_code_conversion_rec.attribute3,
p_attribute4 => p_supp_code_conversion_rec.attribute4,
p_attribute5 => p_supp_code_conversion_rec.attribute5,
p_attribute6 => p_supp_code_conversion_rec.attribute6,
p_attribute7 => p_supp_code_conversion_rec.attribute7,
p_attribute8 => p_supp_code_conversion_rec.attribute8,
p_attribute9 => p_supp_code_conversion_rec.attribute9,
p_attribute10 => p_supp_code_conversion_rec.attribute10,
p_attribute11 => p_supp_code_conversion_rec.attribute11,
p_attribute12 => p_supp_code_conversion_rec.attribute12,
p_attribute13 => p_supp_code_conversion_rec.attribute13,
p_attribute14 => p_supp_code_conversion_rec.attribute14,
p_attribute15 => p_supp_code_conversion_rec.attribute15);
ROLLBACK TO update_supp_code_conv_pvt;
ROLLBACK TO update_supp_code_conv_pvt;
ROLLBACK TO update_supp_code_conv_pvt;
END Update_supp_code_Conversion;
PROCEDURE Update_supp_code_Conv_Tbl(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
P_Commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := 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_supp_code_conversion_Tbl IN supp_code_conversion_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(35) := 'update_supp_code_conversion_tbl';
l_update_supp_code_conv_tbl supp_code_conversion_tbl_type := supp_code_conversion_tbl_type();
SAVEPOINT update_supp_code_conv_tbl_pvt;
l_update_supp_code_conv_tbl.extend;
l_update_supp_code_conv_tbl(l_up_cnt) := P_supp_code_conversion_Rec;
OZF_UTILITY_PVT.debug_message('No of rows to be updated: ' || l_up_cnt);
Update_supp_code_conversion (
p_api_version_number => p_api_version_number ,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_Status,
x_msg_count => x_msg_Count,
x_msg_data => x_msg_Data,
p_supp_code_conversion_tbl => l_update_supp_code_conv_tbl,
x_object_version_number => v_object_version_number
);
ROLLBACK TO update_supp_code_conv_tbl_pvt;
ROLLBACK TO update_supp_code_conv_tbl_pvt;
ROLLBACK TO update_supp_code_conv_tbl_pvt;
END Update_supp_code_Conv_Tbl;
PROCEDURE Delete_Supp_Code_Conv_Tbl
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := 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_supp_code_conversion_tbl IN supp_code_conversion_Tbl_Type
)
IS
l_api_name CONSTANT VARCHAR2(50) := 'delete_supp_code_conversion_tbl';
SAVEPOINT delete_supp_code_conv_tbl_pvt;
Delete_supp_code_Conversion(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => X_Return_Status ,
x_msg_count => X_Msg_Count ,
x_msg_data => X_Msg_Data ,
p_code_conversion_id => l_code_conversion_id,
p_object_version_number => l_object_version_number
);
ROLLBACK TO delete_supp_code_conv_tbl_pvt;
ROLLBACK TO delete_supp_code_conv_tbl_pvt;
ROLLBACK TO delete_supp_code_conv_tbl_pvt;
END Delete_supp_code_Conv_Tbl;
PROCEDURE Delete_supp_code_Conversion
(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := 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_code_conversion_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(50) := 'Delete_supp_code_conversion';
SAVEPOINT delete_supp_code_conv_pvt;
OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
OZF_CODE_CONVERSION_PKG.Delete_Supp_Code_Conv_Row( p_code_conversion_id => p_code_conversion_id,
p_object_version_number => p_object_version_number );
ROLLBACK TO delete_supp_code_conv_pvt;
ROLLBACK TO delete_supp_code_conv_pvt;
ROLLBACK TO delete_supp_code_conv_pvt;
END Delete_Supp_Code_Conversion;