[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Procedure to insert a row in the split percentages table for a given |
| (co-product,co product group, effective date, disable date) |
+---------------------------------------------------------------------------*/
PROCEDURE insert_row(x_err_code OUT NOCOPY NUMBER,
x_err_msg OUT NOCOPY VARCHAR2,
p_co_product_id IN NUMBER,
p_co_product_group_id IN NUMBER,
p_organization_id IN NUMBER,
p_revision IN VARCHAR2,
p_split IN NUMBER,
p_primary_flag IN VARCHAR2,
p_effectivity_date IN DATE,
p_disable_date IN DATE,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER DEFAULT NULL,
p_attribute_category IN VARCHAR2 DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL,
p_attribute4 IN VARCHAR2 DEFAULT NULL,
p_attribute5 IN VARCHAR2 DEFAULT NULL,
p_attribute6 IN VARCHAR2 DEFAULT NULL,
p_attribute7 IN VARCHAR2 DEFAULT NULL,
p_attribute8 IN VARCHAR2 DEFAULT NULL,
p_attribute9 IN VARCHAR2 DEFAULT NULL,
p_attribute10 IN VARCHAR2 DEFAULT NULL,
p_attribute11 IN VARCHAR2 DEFAULT NULL,
p_attribute12 IN VARCHAR2 DEFAULT NULL,
p_attribute13 IN VARCHAR2 DEFAULT NULL,
p_attribute14 IN VARCHAR2 DEFAULT NULL,
p_attribute15 IN VARCHAR2 DEFAULT NULL,
p_request_id IN NUMBER DEFAULT NULL,
p_program_application_id IN NUMBER DEFAULT NULL,
p_program_id IN NUMBER DEFAULT NULL,
p_program_update_date IN DATE DEFAULT NULL
) IS
BEGIN
x_err_code := 0;
insert into WSM_COPRODUCT_SPLIT_PERC
(
CO_PRODUCT_GROUP_ID,
CO_PRODUCT_ID,
ORGANIZATION_ID,
REVISION,
SPLIT,
PRIMARY_FLAG,
EFFECTIVITY_DATE,
DISABLE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
values
(
p_co_product_group_id,
p_co_product_id,
p_organization_id,
p_revision,
p_split,
p_primary_flag,
p_effectivity_date,
p_disable_date,
p_creation_date,
p_created_by,
p_last_update_date,
p_last_updated_by ,
p_last_update_login,
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_request_id,
p_program_application_id,
p_program_id,
p_program_update_date
);
x_err_msg := 'WSM_SPLIT_PERC_PVT.insert_row :' || SQLCODE || ' :' || substr(SQLERRM,1,1000);
END insert_row;
| Procedure to update a row in the split percentages table |
+---------------------------------------------------------------------------*/
PROCEDURE update_row(x_err_code OUT NOCOPY NUMBER,
x_err_msg OUT NOCOPY VARCHAR2,
p_rowid IN VARCHAR2,
p_co_product_id IN NUMBER,
p_co_product_group_id IN NUMBER,
p_organization_id IN NUMBER,
p_revision IN VARCHAR2,
p_split IN NUMBER,
p_primary_flag IN VARCHAR2,
p_effectivity_date IN DATE,
p_disable_date IN DATE,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_request_id IN NUMBER,
p_program_application_id IN NUMBER,
p_program_id IN NUMBER,
p_program_update_date IN DATE
) IS
BEGIN
x_err_code := 0;
UPDATE WSM_COPRODUCT_SPLIT_PERC
SET
CO_PRODUCT_GROUP_ID = p_co_product_group_id,
CO_PRODUCT_ID = p_co_product_id,
ORGANIZATION_ID = p_organization_id,
REVISION = p_revision,
SPLIT = p_split,
PRIMARY_FLAG = p_primary_flag,
EFFECTIVITY_DATE = p_effectivity_date,
DISABLE_DATE = p_disable_date,
CREATION_DATE = p_creation_date,
CREATED_BY = p_created_by,
LAST_UPDATE_DATE = p_last_update_date,
LAST_UPDATED_BY = p_last_updated_by,
LAST_UPDATE_LOGIN = p_last_update_login,
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,
REQUEST_ID = p_request_id,
PROGRAM_APPLICATION_ID = p_program_application_id,
PROGRAM_ID = p_program_id,
PROGRAM_UPDATE_DATE = p_program_update_date
WHERE rowid = p_rowid;
x_err_msg := 'WSM_SPLIT_PERC_PVT.update_row :' || SQLCODE || ' :' || substr(SQLERRM,1,1000);
END update_row;
SELECT *
FROM WSM_COPRODUCT_SPLIT_PERC
WHERE rowid = p_rowid
FOR UPDATE of co_product_id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
| Procedure to delete all the entries corresponding to a (co product id, |
| co product group id) pair in the split percentages table |
+---------------------------------------------------------------------------*/
PROCEDURE delete_row( x_err_code OUT NOCOPY NUMBER,
x_err_msg OUT NOCOPY VARCHAR2,
p_co_product_id IN NUMBER,
p_co_product_group_id IN NUMBER,
p_organization_id IN NUMBER
) IS
BEGIN
x_err_code := 0;
DELETE FROM WSM_COPRODUCT_SPLIT_PERC
WHERE co_product_id = p_co_product_id
AND co_product_group_id = p_co_product_group_id
AND organization_id = p_organization_id;
x_err_msg := 'WSM_SPLIT_PERC_PVT.delete_row :' || SQLCODE || ' :' || substr(SQLERRM,1,1000);
END delete_row;
| Procedure to delete all the records pertaining to a co product group id in |
| the split percentages table |
+---------------------------------------------------------------------------*/
PROCEDURE delete_all_range(x_err_code OUT NOCOPY NUMBER,
x_err_msg OUT NOCOPY VARCHAR2,
p_organization_id IN NUMBER,
p_co_product_group_id IN NUMBER) IS
BEGIN
x_err_code := 0;
DELETE FROM WSM_COPRODUCT_SPLIT_PERC
WHERE co_product_group_id = p_co_product_group_id
AND organization_id = p_organization_id;
x_err_msg := 'WSM_SPLIT_PERC_PVT.delete_all_range :' || SQLCODE || ' :' || substr(SQLERRM,1,1000);
END delete_all_range;
| frame. Called immediately after inserting a new split eff. range |
+---------------------------------------------------------------------------*/
PROCEDURE process_records ( l_co_product_gr_id IN NUMBER,
from_eff_dt IN DATE,
to_eff_dt IN DATE,
x_err_code OUT NOCOPY NUMBER,
x_err_msg OUT NOCOPY VARCHAR2) IS
h_eff_date DATE; /*Effective date of the range in which from_eff_dt falls*/
delete from wsm_coproduct_split_perc
where co_product_group_id = l_co_product_gr_id
and ( (effectivity_date >= from_eff_dt and
((disable_date is not null and disable_date < nvl(to_eff_dt,disable_date+1))
OR
((disable_date is NULL) and (to_eff_dt is NULL) and effectivity_date > from_eff_dt))
)
OR
(effectivity_date > from_eff_dt and
(disable_date is not null and disable_date <= nvl(to_eff_dt,disable_date+1))
)
);
select max(effectivity_date)
into h_eff_date
from wsm_coproduct_split_perc
where co_product_group_id = l_co_product_gr_id
and effectivity_date <= from_eff_dt
and not ( effectivity_date = from_eff_dt
and
(
(disable_date is null and to_eff_dt is null)
or
(disable_date=nvl(to_eff_dt,disable_date + 1))
)
);
select min(disable_date)
into h_disable_date
from wsm_coproduct_split_perc
where co_product_group_id = l_co_product_gr_id
and nvl(disable_date,to_eff_dt+1) >= to_eff_dt
and not (effectivity_date = from_eff_dt and nvl(disable_date,to_eff_dt+1) = to_eff_dt);
--If the following SQL selects a record, it is Possibility 1
-- Existing range is D10 --------- D30 -----------D40
-- new range is D15-- D25
BEGIN
/* if D10-------------> D20 ------> D22-----> D24 -----------> D25 --------------------> null
D20 --------------------------------------------> D35 ( again no intersect)
D15 ------------------------------------------> D25 ( again no intersect )
D15 ---------------------------------------------------------------------> null ( NO INTERSECT )
D35 --------------> NULL ( NO INTERSECT )
D21 --------------------------------------------> D35 ( NO intersect )
D21 ----------------------> D27 ( NO INTERSECT )
D35 -------> D50 ( INTERSECT )
D5 --------> D12 ( INTERSECT )
*/
stmt_num := 30;
select 1
into intersect_exists
from wsm_coproduct_split_perc
where co_product_group_id = l_co_product_gr_id
and effectivity_date < from_eff_dt
and effectivity_date <= h_eff_date
and (( (disable_date is not null )
and
( disable_date = nvl(h_disable_date,disable_date+1) )
)
OR
(
disable_date is null and h_disable_date is null and to_eff_dt is not null
)
);
insert into wsm_coproduct_split_perc (
co_product_group_id,
co_product_id,
ORGANIZATION_ID,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REVISION,
SPLIT ,
EFFECTIVITY_DATE,
DISABLE_DATE,
PRIMARY_FLAG)
(select co_product_group_id,
co_product_id,
ORGANIZATION_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REVISION,
SPLIT,
h_eff_date,
from_eff_dt,
PRIMARY_FLAG
from wsm_coproduct_split_perc
where co_product_group_id = l_co_product_gr_id
and effectivity_date = h_eff_date
and ((disable_date IS NULL and h_disable_date IS NULL) OR (disable_date IS NOT NULL AND (disable_date = nvl(h_disable_date,disable_date+1)))));
insert into wsm_coproduct_split_perc (
co_product_group_id,
co_product_id,
ORGANIZATION_ID,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REVISION,
SPLIT ,
EFFECTIVITY_DATE,
DISABLE_DATE,
PRIMARY_FLAG)
(select co_product_group_id,
co_product_id,
ORGANIZATION_ID,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REVISION,
SPLIT,
to_eff_dt,
h_disable_date,
PRIMARY_FLAG
from wsm_coproduct_split_perc
where co_product_group_id = l_co_product_gr_id
and ((disable_date IS NULL and h_disable_date IS NULL) OR (disable_date IS NOT NULL AND (disable_date = nvl(h_disable_date,disable_date+1))))
and not(effectivity_date = from_eff_dt));
delete from wsm_coproduct_split_perc
where co_product_group_id = l_co_product_gr_id
and effectivity_date = h_eff_date
and ((disable_date IS NULL and h_disable_date IS NULL) OR (disable_date IS NOT NULL AND (disable_date = nvl(h_disable_date,disable_date+1))));
/* Now, need to update D15-D20 period as D16-D20*/
--Possibility 2
stmt_num := 70;
update wsm_coproduct_split_perc
set effectivity_date = to_eff_dt
where co_product_group_id = l_co_product_gr_id
and ((disable_date IS NULL and h_disable_date IS NULL) OR (disable_date IS NOT NULL AND (disable_date = nvl(h_disable_date,disable_date+1))));
update D5-D10 to D5-D7*/
stmt_num := 80;
update wsm_coproduct_split_perc
set disable_date = from_eff_dt
where co_product_group_id = l_co_product_gr_id
and effectivity_date = h_eff_date;
| Procdure to check if the update of comp. eff/ disable date will cause |
| the deletion of any existent ranges |
+---------------------------------------------------------------------------*/
/* This procedure is not used as comp. eff./diable date will not be related to the
co product eff/disable dates */
FUNCTION validate_range (p_co_product_group_id IN NUMBER,
p_organization_id IN NUMBER,
p_effectivity_date IN DATE,
p_disable_date IN DATE) RETURN NUMBER IS
l_retval NUMBER:=0;
select 1
into l_num
from WSM_COPRODUCT_SPLIT_PERC
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id
and disable_date is not NULL
and disable_date <= p_effectivity_date;
select 1
into l_num
from WSM_COPRODUCT_SPLIT_PERC
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id
and effectivity_date >= p_disable_date;
| Procdure to update/delete any existent ranges that would be affected by the|
| the update of comp. eff. date/ disable date |
+---------------------------------------------------------------------------*/
/* This procedure is not used as comp. eff./diable date will not be related to the
co product eff/disable dates */
PROCEDURE update_split_range(x_err_code OUT NOCOPY NUMBER,
x_err_msg OUT NOCOPY VARCHAR2,
p_organization_id IN NUMBER,
p_co_product_group_id IN NUMBER,
p_effectivity_date IN DATE,
p_disable_date IN DATE,
p_update_range IN NUMBER
) IS
l_num NUMBER:=0;
IF p_update_range IN (1,3) THEN
-- delete any range which has the disable date less than or equal to the
-- new effectivity date
DELETE FROM WSM_COPRODUCT_SPLIT_PERC
WHERE organization_id = p_organization_id
AND co_product_group_id = p_co_product_group_id
AND disable_date is NOT NULL
AND disable_date <= p_effectivity_date;
UPDATE WSM_COPRODUCT_SPLIT_PERC
SET effectivity_date = p_effectivity_date
WHERE organization_id = p_organization_id
AND co_product_group_id = p_co_product_group_id
AND effectivity_date < p_effectivity_date;
IF p_update_range IN (2,3) THEN
-- delete any range which has the effective date greater than or equal
-- to the new disable date..
DELETE
FROM WSM_COPRODUCT_SPLIT_PERC
WHERE organization_id = p_organization_id
AND co_product_group_id = p_co_product_group_id
AND effectivity_date >= NVL(p_disable_date,effectivity_date-1);
select 1
into l_num
from WSM_COPRODUCT_SPLIT_PERC
WHERE organization_id = p_organization_id
AND co_product_group_id = p_co_product_group_id
AND disable_date IS NULL;
-- so update all records with NULL disable date.
IF p_disable_date IS NOT NULL THEN
UPDATE WSM_COPRODUCT_SPLIT_PERC
SET disable_date = p_disable_date
WHERE organization_id = p_organization_id
AND co_product_group_id = p_co_product_group_id
AND disable_date is NULL;
UPDATE WSM_COPRODUCT_SPLIT_PERC
SET disable_date = p_disable_date
WHERE organization_id = p_organization_id
AND co_product_group_id = p_co_product_group_id
AND disable_date IN ( SELECT MAX(disable_date)
FROM WSM_COPRODUCT_SPLIT_PERC
WHERE organization_id = p_organization_id
AND co_product_group_id = p_co_product_group_id);
x_err_msg := 'WSM_SPLIT_PERC_PVT.update_split_range : ' || SQLCODE || substr(SQLERRM,1,1000);
END update_split_range;
| Procedure to insert a co-product in all ranges of a co-product group id |
| with split perc 0% in case of sec. co-product and 100% in case of |
| primary co-product |
+---------------------------------------------------------------------------*/
PROCEDURE insert_co_product_range(x_err_code OUT NOCOPY NUMBER,
x_err_msg OUT NOCOPY VARCHAR2,
p_co_product_group_id IN NUMBER,
p_co_product_id IN NUMBER,
p_revision IN VARCHAR2,
p_split IN NUMBER,
p_primary_flag IN VARCHAR2,
p_organization_id IN NUMBER,
p_effectivity_date IN DATE,
p_disable_date IN DATE,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER
) IS
CURSOR range_cursor IS select distinct effectivity_date,disable_date
from WSM_COPRODUCT_SPLIT_PERC
where co_product_group_id= p_co_product_group_id;
select 1
into l_num
from WSM_COPRODUCT_SPLIT_PERC
WHERE co_product_group_id = p_co_product_group_id ;
WSM_SPLIT_PERC_PVT.insert_row(x_err_code => l_err_code,
x_err_msg => l_err_msg,
p_co_product_id => p_co_product_id,
p_co_product_group_id => p_co_product_group_id,
p_organization_id => p_organization_id,
p_revision => p_revision,
p_split => p_split,
p_primary_flag => p_primary_flag,
p_effectivity_date => p_effectivity_date,
p_disable_date => p_disable_date,
p_creation_date => p_creation_date,
p_created_by => p_created_by,
p_last_update_date => p_last_update_date,
p_last_updated_by => p_last_updated_by );
WSM_SPLIT_PERC_PVT.insert_row(x_err_code => l_err_code,
x_err_msg => l_err_msg,
p_co_product_id => p_co_product_id,
p_co_product_group_id => p_co_product_group_id,
p_organization_id => p_organization_id,
p_revision => p_revision,
p_split => p_split,
p_primary_flag => p_primary_flag,
p_effectivity_date => date_rec.effectivity_date,
p_disable_date => date_rec.disable_date,
p_creation_date => p_creation_date,
p_created_by => p_created_by,
p_last_update_date => p_last_update_date,
p_last_updated_by => p_last_updated_by );
x_err_msg := 'WSM_SPLIT_PERC_PVT.insert_co_product_range : ' || SQLCODE || substr(SQLERRM,1,1000);
END insert_co_product_range;
select 1
into l_num
from WSM_COPRODUCT_SPLIT_PERC
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id
and effectivity_date = p_effectivity_date
and ((p_disable_date is NULL and disable_date is NULL) OR (p_disable_date = disable_date))
and split = 0;
SELECT 1
INTO l_num
from WSM_COPRODUCT_SPLIT_PERC
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id
and co_product_id <> p_co_product_id;
SELECT 1
INTO l_num
from WSM_COPRODUCT_SPLIT_PERC
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id
and co_product_id = p_co_product_id
and split > 0 ;
select distinct max(count(*))
into l_num
from WSM_COPRODUCT_SPLIT_PERC
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id
and split = 0
group by co_product_id;
-- selects the totla no. of ranges for a co_product_group_id
/*3647337
select count(*)
into l_count
from WSM_COPRODUCT_SPLIT_DATES_V
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id;
select min(sum(split))
into l_num
from WSM_COPRODUCT_SPLIT_PERC
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id
group by co_product_id;
SELECT count(*)
INTO l_num
FROM WSM_COPRODUCT_SPLIT_DATES_V
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id;
SELECT count(*)
INTO l_num
FROM WSM_COPRODUCT_SPLIT_PERC
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id
group by EFFECTIVITY_DATE;
| will cause any existing ranges to be deleted |
+---------------------------------------------------------------------------*/
FUNCTION check_any_del_range ( p_co_product_group_id IN NUMBER,
p_organization_id IN NUMBER,
p_effectivity_date IN DATE,
p_disable_date IN DATE) RETURN NUMBER IS
l_retval NUMBER:=0;
SELECT 1
INTO l_num
FROM WSM_COPRODUCT_SPLIT_PERC
where organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id
and disable_date is NOT NULL
and effectivity_date >= p_effectivity_date
and disable_date<=p_disable_date;
SELECT 1
INTO l_num
FROM WSM_COPRODUCT_SPLIT_PERC
WHERE organization_id = p_organization_id
and co_product_group_id = p_co_product_group_id
and disable_date is NOT NULL
and effectivity_date >= p_effectivity_date;
SELECT 1
INTO l_num
FROM WSM_COPRODUCT_SPLIT_PERC
WHERE organization_id = p_organization_id
AND co_product_group_id = p_co_product_group_id
AND effectivity_date = p_effectivity_date
AND disable_date IS NULL;
SELECT 1
INTO l_num
FROM WSM_COPRODUCT_SPLIT_PERC
WHERE organization_id = p_organization_id
AND co_product_group_id = p_co_product_group_id
AND effectivity_date = p_effectivity_date
AND disable_date = p_disable_date;