[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 1
From RA_item_exception_rates
Where item_id = p_item_id
And nvl(location_id_segment_1,0) =
nvl(p_location_id_segment_1,0)
And nvl(location_id_segment_2,0) =
nvl(p_location_id_segment_2,0)
And nvl(location_id_segment_3,0) =
nvl(p_location_id_segment_3,0)
And nvl(location_id_segment_4,0) =
nvl(p_location_id_segment_4,0)
And nvl(location_id_segment_5,0) =
nvl(p_location_id_segment_5,0)
And nvl(location_id_segment_6,0) =
nvl(p_location_id_segment_6,0)
And nvl(location_id_segment_7,0) =
nvl(p_location_id_segment_7,0)
And nvl(location_id_segment_8,0) =
nvl(p_location_id_segment_8,0)
And nvl(location_id_segment_9,0) =
nvl(p_location_id_segment_9,0)
And nvl(location_id_segment_10,0) =
nvl(p_location_id_segment_10,0)
And ( p_start_date between
start_date AND nvl(end_date, p_start_date)
OR start_date between p_start_date AND
nvl(p_end_date, start_date) )
And ( p_rowid is null
OR rowid <> p_rowid );
PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
p_Item_Exception_Rate_Id IN OUT NOCOPY NUMBER,
p_Creation_Date DATE,
p_Created_By NUMBER,
p_Last_Update_Login NUMBER,
p_Last_Updated_By NUMBER,
p_Last_Update_Date DATE,
p_Item_Id NUMBER,
p_Rate_Context VARCHAR2,
p_Location1_Rate NUMBER,
p_Location2_Rate NUMBER,
p_Location3_Rate NUMBER,
p_Location4_Rate NUMBER,
p_Location5_Rate NUMBER,
p_Location6_Rate NUMBER,
p_Location7_Rate NUMBER,
p_Location8_Rate NUMBER,
p_Location9_Rate NUMBER,
p_Location10_Rate NUMBER,
p_Start_Date DATE,
p_End_Date DATE,
p_Attribute_Category VARCHAR2,
p_Attribute1 VARCHAR2,
p_Attribute2 VARCHAR2,
p_Attribute3 VARCHAR2,
p_Attribute4 VARCHAR2,
p_Attribute5 VARCHAR2,
p_Attribute6 VARCHAR2,
p_Attribute7 VARCHAR2,
p_Attribute8 VARCHAR2,
p_Attribute9 VARCHAR2,
p_Attribute10 VARCHAR2,
p_Attribute11 VARCHAR2,
p_Attribute12 VARCHAR2,
p_Attribute13 VARCHAR2,
p_Attribute14 VARCHAR2,
p_Attribute15 VARCHAR2,
p_Reason_Code VARCHAR2,
p_Location_Context VARCHAR2,
p_Location_Id_Segment_1 NUMBER,
p_Location_Id_Segment_2 NUMBER,
p_Location_Id_Segment_3 NUMBER,
p_Location_Id_Segment_4 NUMBER,
p_Location_Id_Segment_5 NUMBER,
p_Location_Id_Segment_6 NUMBER,
p_Location_Id_Segment_7 NUMBER,
p_Location_Id_Segment_8 NUMBER,
p_Location_Id_Segment_9 NUMBER,
p_Location_Id_Segment_10 NUMBER,
p_org_id NUMBER DEFAULT -1 -- Bug 3098063
) IS
CURSOR C IS SELECT rowid FROM RA_ITEM_EXCEPTION_RATES
WHERE item_exception_rate_id = p_Item_Exception_Rate_Id;
CURSOR C2 IS SELECT ra_item_exception_rates_s.nextval FROM dual;
INSERT INTO RA_ITEM_EXCEPTION_RATES(
item_exception_rate_id,
creation_date,
created_by,
last_update_login,
last_updated_by,
last_update_date,
item_id,
rate_context,
location1_rate,
location2_rate,
location3_rate,
location4_rate,
location5_rate,
location6_rate,
location7_rate,
location8_rate,
location9_rate,
location10_rate,
start_date,
end_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
reason_code,
location_context,
location_id_segment_1,
location_id_segment_2,
location_id_segment_3,
location_id_segment_4,
location_id_segment_5,
location_id_segment_6,
location_id_segment_7,
location_id_segment_8,
location_id_segment_9,
location_id_segment_10
) VALUES (
p_Item_Exception_Rate_Id,
p_Creation_Date,
p_Created_By,
p_Last_Update_Login,
p_Last_Updated_By,
p_Last_Update_Date,
p_Item_Id,
p_Rate_Context,
p_Location1_Rate,
p_Location2_Rate,
p_Location3_Rate,
p_Location4_Rate,
p_Location5_Rate,
p_Location6_Rate,
p_Location7_Rate,
p_Location8_Rate,
p_Location9_Rate,
p_Location10_Rate,
p_Start_Date,
p_End_Date,
p_Attribute_Category,
p_Attribute1,
p_Attribute2,
p_Attribute3,
p_Attribute4,
p_Attribute5,
p_Attribute6,
p_Attribute7,
p_Attribute8,
p_Attribute9,
p_Attribute10,
p_Attribute11,
p_Attribute12,
p_Attribute13,
p_Attribute14,
p_Attribute15,
p_Reason_Code,
p_Location_Context,
p_Location_Id_Segment_1,
p_Location_Id_Segment_2,
p_Location_Id_Segment_3,
p_Location_Id_Segment_4,
p_Location_Id_Segment_5,
p_Location_Id_Segment_6,
p_Location_Id_Segment_7,
p_Location_Id_Segment_8,
p_Location_Id_Segment_9,
p_Location_Id_Segment_10
);
P_Last_Update_Login => P_Last_Update_Login,
P_Last_Updated_By => P_Last_Updated_By,
P_Last_Update_Date => P_Last_Update_Date,
P_Item_Id => P_Item_Id,
P_Rate_Context => P_Rate_Context,
P_Location1_Rate => P_Location1_Rate,
P_Location2_Rate => P_Location2_Rate,
P_Location3_Rate => P_Location3_Rate,
P_Location4_Rate => P_Location4_Rate,
P_Location5_Rate => P_Location5_Rate,
P_Location6_Rate => P_Location6_Rate,
P_Location7_Rate => P_Location7_Rate,
P_Location8_Rate => P_Location8_Rate,
P_Location9_Rate => P_Location9_Rate,
P_Location10_Rate => P_Location10_Rate,
P_Start_Date => P_Start_Date,
P_End_Date => P_End_Date,
P_Attribute_Category => P_Attribute_Category,
P_Attribute1 => P_Attribute1,
P_Attribute2 => P_Attribute2,
P_Attribute3 => P_Attribute3,
P_Attribute4 => P_Attribute4,
P_Attribute5 => P_Attribute5,
P_Attribute6 => P_Attribute6,
P_Attribute7 => P_Attribute7,
P_Attribute8 => P_Attribute8,
P_Attribute9 => P_Attribute9,
P_Attribute10 => P_Attribute10,
P_Attribute11 => P_Attribute11,
P_Attribute12 => P_Attribute12,
P_Attribute13 => P_Attribute13,
P_Attribute14 => P_Attribute14,
P_Attribute15 => P_Attribute15,
P_Reason_Code => P_Reason_Code,
P_Location_Context => P_Location_Context,
P_Location_Id_Segment_1 => P_Location_Id_Segment_1,
P_Location_Id_Segment_2 => P_Location_Id_Segment_2,
P_Location_Id_Segment_3 => P_Location_Id_Segment_3,
P_Location_Id_Segment_4 => P_Location_Id_Segment_4,
P_Location_Id_Segment_5 => P_Location_Id_Segment_5,
P_Location_Id_Segment_6 => P_Location_Id_Segment_6,
P_Location_Id_Segment_7 => P_Location_Id_Segment_7,
P_Location_Id_Segment_8 => P_Location_Id_Segment_8,
P_Location_Id_Segment_9 => P_Location_Id_Segment_9,
P_Location_Id_Segment_10 => P_Location_Id_Segment_10
);
END Insert_Row;
SELECT *
FROM RA_ITEM_EXCEPTION_RATES
WHERE rowid = p_Rowid
FOR UPDATE of Item_Exception_Rate_Id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(p_Rowid VARCHAR2,
p_Item_Exception_Rate_Id NUMBER,
p_Last_Update_Login NUMBER,
p_Last_Updated_By NUMBER,
p_Last_Update_Date DATE,
p_Item_Id NUMBER,
p_Rate_Context VARCHAR2,
p_Location1_Rate NUMBER,
p_Location2_Rate NUMBER,
p_Location3_Rate NUMBER,
p_Location4_Rate NUMBER,
p_Location5_Rate NUMBER,
p_Location6_Rate NUMBER,
p_Location7_Rate NUMBER,
p_Location8_Rate NUMBER,
p_Location9_Rate NUMBER,
p_Location10_Rate NUMBER,
p_Start_Date DATE,
p_End_Date DATE,
p_Attribute_Category VARCHAR2,
p_Attribute1 VARCHAR2,
p_Attribute2 VARCHAR2,
p_Attribute3 VARCHAR2,
p_Attribute4 VARCHAR2,
p_Attribute5 VARCHAR2,
p_Attribute6 VARCHAR2,
p_Attribute7 VARCHAR2,
p_Attribute8 VARCHAR2,
p_Attribute9 VARCHAR2,
p_Attribute10 VARCHAR2,
p_Attribute11 VARCHAR2,
p_Attribute12 VARCHAR2,
p_Attribute13 VARCHAR2,
p_Attribute14 VARCHAR2,
p_Attribute15 VARCHAR2,
p_Reason_Code VARCHAR2,
p_Location_Context VARCHAR2,
p_Location_Id_Segment_1 NUMBER,
p_Location_Id_Segment_2 NUMBER,
p_Location_Id_Segment_3 NUMBER,
p_Location_Id_Segment_4 NUMBER,
p_Location_Id_Segment_5 NUMBER,
p_Location_Id_Segment_6 NUMBER,
p_Location_Id_Segment_7 NUMBER,
p_Location_Id_Segment_8 NUMBER,
p_Location_Id_Segment_9 NUMBER,
p_Location_Id_Segment_10 NUMBER,
p_org_id NUMBER DEFAULT -1 -- Bug 3098063
) IS
BEGIN
-- Check if Effective Dates overlap.
--
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug('Update_Row: ' || '-- Checking Overlap ');
arp_util.debug('Update_Row: ' || '-- Updating Row');
arp_util.debug('Update_Row: ' || '-- p_Rowid = '||p_Rowid);
UPDATE RA_ITEM_EXCEPTION_RATES
SET
item_exception_rate_id = p_Item_Exception_Rate_Id,
last_update_login = p_Last_Update_Login,
last_updated_by = p_Last_Updated_By,
last_update_date = p_Last_Update_Date,
item_id = p_Item_Id,
rate_context = p_Rate_Context,
location1_rate = p_Location1_Rate,
location2_rate = p_Location2_Rate,
location3_rate = p_Location3_Rate,
location4_rate = p_Location4_Rate,
location5_rate = p_Location5_Rate,
location6_rate = p_Location6_Rate,
location7_rate = p_Location7_Rate,
location8_rate = p_Location8_Rate,
location9_rate = p_Location9_Rate,
location10_rate = p_Location10_Rate,
start_date = p_Start_Date,
end_date = p_End_Date,
attribute_category = p_Attribute_Category,
attribute1 = p_Attribute1,
attribute2 = p_Attribute2,
attribute3 = p_Attribute3,
attribute4 = p_Attribute4,
attribute5 = p_Attribute5,
attribute6 = p_Attribute6,
attribute7 = p_Attribute7,
attribute8 = p_Attribute8,
attribute9 = p_Attribute9,
attribute10 = p_Attribute10,
attribute11 = p_Attribute11,
attribute12 = p_Attribute12,
attribute13 = p_Attribute13,
attribute14 = p_Attribute14,
attribute15 = p_Attribute15,
reason_code = p_Reason_Code,
location_context = p_Location_Context,
location_id_segment_1 = p_Location_Id_Segment_1,
location_id_segment_2 = p_Location_Id_Segment_2,
location_id_segment_3 = p_Location_Id_Segment_3,
location_id_segment_4 = p_Location_Id_Segment_4,
location_id_segment_5 = p_Location_Id_Segment_5,
location_id_segment_6 = p_Location_Id_Segment_6,
location_id_segment_7 = p_Location_Id_Segment_7,
location_id_segment_8 = p_Location_Id_Segment_8,
location_id_segment_9 = p_Location_Id_Segment_9,
location_id_segment_10 = p_Location_Id_Segment_10
WHERE rowid = p_Rowid;
P_Last_Update_Login => P_Last_Update_Login,
P_Last_Updated_By => P_Last_Updated_By,
P_Last_Update_Date => P_Last_Update_Date,
P_Item_Id => P_Item_Id,
P_Rate_Context => P_Rate_Context,
P_Location1_Rate => P_Location1_Rate,
P_Location2_Rate => P_Location2_Rate,
P_Location3_Rate => P_Location3_Rate,
P_Location4_Rate => P_Location4_Rate,
P_Location5_Rate => P_Location5_Rate,
P_Location6_Rate => P_Location6_Rate,
P_Location7_Rate => P_Location7_Rate,
P_Location8_Rate => P_Location8_Rate,
P_Location9_Rate => P_Location9_Rate,
P_Location10_Rate => P_Location10_Rate,
P_Start_Date => P_Start_Date,
P_End_Date => P_End_Date,
P_Attribute_Category => P_Attribute_Category,
P_Attribute1 => P_Attribute1,
P_Attribute2 => P_Attribute2,
P_Attribute3 => P_Attribute3,
P_Attribute4 => P_Attribute4,
P_Attribute5 => P_Attribute5,
P_Attribute6 => P_Attribute6,
P_Attribute7 => P_Attribute7,
P_Attribute8 => P_Attribute8,
P_Attribute9 => P_Attribute9,
P_Attribute10 => P_Attribute10,
P_Attribute11 => P_Attribute11,
P_Attribute12 => P_Attribute12,
P_Attribute13 => P_Attribute13,
P_Attribute14 => P_Attribute14,
P_Attribute15 => P_Attribute15,
P_Reason_Code => P_Reason_Code,
P_Location_Context => P_Location_Context,
P_Location_Id_Segment_1 => P_Location_Id_Segment_1,
P_Location_Id_Segment_2 => P_Location_Id_Segment_2,
P_Location_Id_Segment_3 => P_Location_Id_Segment_3,
P_Location_Id_Segment_4 => P_Location_Id_Segment_4,
P_Location_Id_Segment_5 => P_Location_Id_Segment_5,
P_Location_Id_Segment_6 => P_Location_Id_Segment_6,
P_Location_Id_Segment_7 => P_Location_Id_Segment_7,
P_Location_Id_Segment_8 => P_Location_Id_Segment_8,
P_Location_Id_Segment_9 => P_Location_Id_Segment_9,
P_Location_Id_Segment_10 => P_Location_Id_Segment_10
);
END Update_Row;
PROCEDURE Delete_Row(p_Rowid VARCHAR2) IS
BEGIN
DELETE FROM RA_ITEM_EXCEPTION_RATES
WHERE rowid = p_Rowid;
END Delete_Row;