[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LAST_UPDATE_DATE DATE := sysdate;
G_LAST_UPDATED_BY NUMBER := FND_GLOBAL.USER_ID;
G_LAST_UPDATE_LOGIN NUMBER := FND_GLOBAL.LOGIN_ID;
select f.element_name element_name
from pay_element_types_f f
,gl_sets_of_books glsob
,cn_repositories cnr
where f.element_type_id = p_element_type_id
AND cnr.set_of_books_id = glsob.set_of_books_id
AND f.input_currency_code = glsob.currency_code;
select 1 into l_found FROM dual
where not exists
( select 1
from cn_pay_element_inputs
where quota_pay_element_id = p_quota_pay_element_id);
FUNCTION check_delete_update_allowed( p_quota_pay_element_id NUMBER,
p_start_date DATE := NULL ,
p_end_date DATE := NULL,
p_quota_id NUMBER := NULL,
p_pay_element_type_id IN NUMBER := NULL )
RETURN NUMBER IS
l_found NUMBER := 0;
SELECT 0 INTO l_found
FROM dual
WHERE NOT EXISTS
(
SELECT 1 --if query returns row, then we can't allow update/delete.
--if query doesnot return anything, we can do update/delete.
FROM
(--Quota_id is -1000 as carry over and regular quota_id as in cn_quotas
SELECT qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status,
MIN(ps.start_date) start_date, MAX(ps.end_date) end_date
FROM cn_period_statuses ps,
cn_payment_transactions pt,
cn_quota_pay_elements qpe,
cn_salesreps cs,
cn_quotas cq
WHERE pt.credited_salesrep_id = cs.salesrep_id
AND pt.pay_period_id = ps.period_id
AND pt.pay_element_type_id = qpe.pay_element_type_id
AND cq.quota_id = DECODE(pt.quota_id,-1000, cq.quota_id, pt.quota_id)
AND pt.quota_id = qpe.quota_id
AND nvl(cs.status, 'A') = qpe.status
AND cq.start_date <= NVL(cs.end_date_active, cq.start_date)
AND cs.start_date_active <= NVL( cq.end_date, cs.start_date_active)
AND qpe.start_date <= NVL( cq.end_date, qpe.start_date)
AND cq.start_date <= NVL( qpe.end_date, cq.start_date)
AND qpe.start_date <= NVL( cs.end_date_active, qpe.start_date)
AND cs.start_date_active <= NVL( qpe.end_date, cs.start_date_active)
GROUP BY qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status
UNION ALL
--Quota_id is -1001 as 'PMTPLN_REC' type.
SELECT qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status,
MIN(ps.start_date) start_date, MAX(ps.end_date) end_date
FROM cn_period_statuses ps,
cn_payment_transactions pt,
cn_quota_pay_elements qpe,
cn_salesreps cs,
cn_quotas cq
WHERE pt.credited_salesrep_id = cs.salesrep_id
AND pt.pay_period_id = ps.period_id
AND pt.pay_element_type_id = qpe.pay_element_type_id
AND cq.quota_id = DECODE(pt.quota_id,-1000, cq.quota_id, pt.quota_id)
AND pt.quota_id = DECODE(qpe.quota_id,-1001, pt.quota_id,qpe.quota_id)
AND qpe.quota_id = -1001
AND pt.incentive_type_code = 'PMTPLN_REC'
AND nvl(cs.status, 'A') = qpe.status
AND cq.start_date <= NVL(cs.end_date_active, cq.start_date)
AND cs.start_date_active <= NVL( cq.end_date, cs.start_date_active)
AND qpe.start_date <= NVL( cq.end_date, qpe.start_date)
AND cq.start_date <= NVL( qpe.end_date, cq.start_date)
AND qpe.start_date <= NVL( cs.end_date_active, qpe.start_date)
AND cs.start_date_active <= NVL( qpe.end_date, cs.start_date_active)
GROUP BY qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status
) v
WHERE v.quota_pay_element_id = p_quota_pay_element_id
AND ( (p_end_date < v.end_date OR p_start_date > v.start_date)
OR v.quota_id <> p_quota_id
OR v.pay_element_type_id <> p_pay_element_type_id
)
);
SELECT 0 INTO l_found
FROM dual
WHERE NOT EXISTS
(
SELECT 1 --if query returns row, then we can't allow update/delete.
--if query doesnot return anything, we can do update/delete.
FROM
(--Quota_id is -1000 as carry over and regular quota_id as in cn_quotas
SELECT qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status,
MIN(ps.start_date) start_date, MAX(ps.end_date) end_date
FROM cn_period_statuses ps,
cn_payment_transactions pt,
cn_quota_pay_elements qpe,
cn_salesreps cs,
cn_quotas cq
WHERE pt.credited_salesrep_id = cs.salesrep_id
AND pt.pay_period_id = ps.period_id
AND pt.pay_element_type_id = qpe.pay_element_type_id
AND cq.quota_id = DECODE(pt.quota_id,-1000, cq.quota_id, pt.quota_id)
AND pt.quota_id = qpe.quota_id
AND nvl(cs.status, 'A') = qpe.status
AND cq.start_date <= NVL(cs.end_date_active, cq.start_date)
AND cs.start_date_active <= NVL( cq.end_date, cs.start_date_active)
AND qpe.start_date <= NVL( cq.end_date, qpe.start_date)
AND cq.start_date <= NVL( qpe.end_date, cq.start_date)
AND qpe.start_date <= NVL( cs.end_date_active, qpe.start_date)
AND cs.start_date_active <= NVL( qpe.end_date, cs.start_date_active)
GROUP BY qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status
UNION ALL
--Quota_id is -1001 as 'PMTPLN_REC' type.
SELECT qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status,
MIN(ps.start_date) start_date, MAX(ps.end_date) end_date
FROM cn_period_statuses ps,
cn_payment_transactions pt,
cn_quota_pay_elements qpe,
cn_salesreps cs,
cn_quotas cq
WHERE pt.credited_salesrep_id = cs.salesrep_id
AND pt.pay_period_id = ps.period_id
AND pt.pay_element_type_id = qpe.pay_element_type_id
AND cq.quota_id = DECODE(pt.quota_id,-1000, cq.quota_id, pt.quota_id)
AND pt.quota_id = DECODE(qpe.quota_id,-1001, pt.quota_id,qpe.quota_id)
AND qpe.quota_id = -1001
AND pt.incentive_type_code = 'PMTPLN_REC'
AND nvl(cs.status, 'A') = qpe.status
AND cq.start_date <= NVL(cs.end_date_active, cq.start_date)
AND cs.start_date_active <= NVL( cq.end_date, cs.start_date_active)
AND qpe.start_date <= NVL( cq.end_date, qpe.start_date)
AND cq.start_date <= NVL( qpe.end_date, cq.start_date)
AND qpe.start_date <= NVL( cs.end_date_active, qpe.start_date)
AND cs.start_date_active <= NVL( qpe.end_date, cs.start_date_active)
GROUP BY qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status
) v
WHERE v.quota_pay_element_id = p_quota_pay_element_id
AND (p_end_date < v.end_date OR p_start_date > v.start_date)
);
SELECT quota_id
FROM cn_quotas
WHERE name = p_quota_name;
SELECT v.quota_id
FROM
(SELECT TO_NUMBER(lookup_code) quota_id, meaning name
FROM cn_lookups
WHERE lookup_type = 'ELEMENT_TYPE'
) v
WHERE v.name = p_quota_name;
SELECT quota_id INTO l_quota_id
FROM cn_quota_lookups_v
WHERE name = p_quota_name;
SELECT currency_code
FROM gl_sets_of_books glsob,
cn_repositories cnr
WHERE cnr.set_of_books_id = glsob.set_of_books_id;
SELECT element_type_id
INTO x_element_type_id
FROM pay_element_types_f
WHERE element_name = p_pay_element_name
AND input_currency_code = l_functional_currency
AND p_start_date between effective_start_date and effective_end_date
AND effective_end_date >= nvl(p_end_date, effective_end_date);
select *
from cn_quota_pay_elements
where quota_pay_element_id = p_quota_pay_element_id;
SELECT 1 INTO l_dummy FROM dual
WHERE NOT EXISTS
( SELECT 1
FROM cn_quota_pay_elements
WHERE quota_id = p_quota_pay_element_rec.quota_id
AND pay_element_type_id = p_quota_pay_element_rec.pay_element_type_id
AND nvl(status,'A') = nvl(p_quota_pay_element_rec.status,'A')
AND start_date = p_quota_pay_element_rec.start_date
AND ( (end_date = p_quota_pay_element_rec.end_date) OR
(end_date IS NULL AND p_quota_pay_element_rec.end_date IS NULL) )
AND ((p_quota_pay_element_rec.quota_pay_element_id IS NOT NULL AND
quota_pay_element_id <> p_quota_pay_element_rec.quota_pay_element_id)
OR
(p_quota_pay_element_rec.quota_pay_element_id IS NULL))
);
SELECT 1 INTO l_dummy FROM dual
WHERE NOT EXISTS
( SELECT 1
FROM cn_quota_pay_elements
WHERE (((end_date IS NULL)
AND (p_quota_pay_element_rec.end_date IS NULL))
OR
((end_date IS NULL) AND
(p_quota_pay_element_rec.end_date IS NOT NULL) AND
((p_quota_pay_element_rec.start_date >= start_date) OR
(start_date BETWEEN p_quota_pay_element_rec.start_date
AND p_quota_pay_element_rec.end_date))
)
OR
((end_date IS NOT NULL) AND
(p_quota_pay_element_rec.end_date IS NULL) AND
((p_quota_pay_element_rec.start_date <= start_date) OR
(p_quota_pay_element_rec.start_date BETWEEN start_date
AND end_date))
)
OR
((end_date IS NOT NULL) AND
(p_quota_pay_element_rec.end_date IS NOT NULL) AND
((start_date BETWEEN p_quota_pay_element_rec.start_date
AND p_quota_pay_element_rec.end_date) OR
(end_date BETWEEN p_quota_pay_element_rec.start_date
AND p_quota_pay_element_rec.end_date) OR
(p_quota_pay_element_rec.start_date BETWEEN start_date
AND end_date))
)
)
AND ((p_quota_pay_element_rec.quota_pay_element_id IS NOT NULL AND
quota_pay_element_id <> p_quota_pay_element_rec.quota_pay_element_id)
OR
(p_quota_pay_element_rec.quota_pay_element_id IS NULL))
AND quota_id = p_quota_pay_element_rec.quota_id
--AND pay_element_type_id = p_quota_pay_element_rec.pay_element_type_id
AND nvl(status,'A') = nvl(p_quota_pay_element_rec.status,'A')
);
if check_delete_update_allowed(p_quota_pay_element_rec.quota_pay_element_id,
p_quota_pay_element_rec.start_date,
p_quota_pay_element_rec.end_date,
p_quota_pay_element_rec.quota_id,
p_quota_pay_element_rec.pay_element_type_id) > 0 THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.SET_NAME ('CN' , 'CN_PAY_ELEMENT_ALREADY_USED');
x_loading_status := 'CN_INSERTED';
SELECT
Decode(p_quota_pay_element_rec.quota_pay_element_id,
FND_API.G_MISS_NUM, NULL ,
p_quota_pay_element_rec.quota_pay_element_id),
Decode(p_quota_pay_element_rec.quota_id,
FND_API.G_MISS_NUM, NULL ,
p_quota_pay_element_rec.quota_id ),
Decode(p_quota_pay_element_rec.pay_element_type_id,
FND_API.G_MISS_NUM, NULL ,
p_quota_pay_element_rec.pay_element_type_id),
Decode(p_quota_pay_element_rec.status,
FND_API.G_MISS_CHAR, NULL ,
p_quota_pay_element_rec.status),
Decode(p_quota_pay_element_rec.start_date,
FND_API.G_MISS_DATE,To_date(NULL) ,
trunc(p_quota_pay_element_rec.start_date)),
Decode(p_quota_pay_element_rec.end_date,
FND_API.G_MISS_DATE,To_date(NULL) ,
trunc(p_quota_pay_element_rec.end_date)),
Decode(p_quota_pay_element_rec.quota_name,
FND_API.G_MISS_CHAR, NULL ,
p_quota_pay_element_rec.quota_name),
Decode(p_quota_pay_element_rec.pay_element_name,
FND_API.G_MISS_CHAR, NULL ,
p_quota_pay_element_rec.pay_element_name),
Decode(p_quota_pay_element_rec.pay_start_date,
FND_API.G_MISS_DATE, NULL ,
p_quota_pay_element_rec.pay_start_date),
Decode(p_quota_pay_element_rec.pay_end_date,
FND_API.G_MISS_DATE, NULL ,
p_quota_pay_element_rec.pay_end_date)
INTO
l_quota_pay_element_rec.quota_pay_element_id,
l_quota_pay_element_rec.quota_id,
l_quota_pay_element_rec.pay_element_type_id,
l_quota_pay_element_rec.status,
l_quota_pay_element_rec.start_date,
l_quota_pay_element_rec.end_date,
l_quota_pay_element_rec.quota_name,
l_quota_pay_element_rec.pay_element_name,
l_quota_pay_element_rec.pay_start_date,
l_quota_pay_element_rec.pay_end_date
FROM dual;
cn_quota_pay_elements_pkg.insert_row
(x_quota_pay_element_id => l_quota_pay_element_rec.quota_pay_element_id
,p_quota_id => l_quota_pay_element_rec.quota_id
,p_pay_element_type_id => l_quota_pay_element_rec.pay_element_type_id
,p_status => l_quota_pay_element_rec.status
,p_start_date => l_quota_pay_element_rec.start_date
,p_end_date => l_quota_pay_element_rec.end_date
,p_last_update_date => G_LAST_UPDATE_DATE
,p_last_updated_by => G_LAST_UPDATED_BY
,p_creation_date => G_CREATION_DATE
,p_created_by => G_CREATED_BY
,p_last_update_login => G_LAST_UPDATE_LOGIN
);
PROCEDURE Update_quota_pay_element
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
po_quota_pay_element_rec IN quota_pay_element_rec_type
:= G_MISS_quota_pay_element_rec,
p_quota_pay_element_rec IN quota_pay_element_rec_type:=G_MISS_QUOTA_PAY_ELEMENT_REC,
x_loading_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Uupdate_Quota_Pay_Element';
l_action VARCHAR2(30) := 'UPDATE';
SAVEPOINT Update_quota_pay_element;
x_loading_status := 'CN_UPDATED';
SELECT
Decode(p_quota_pay_element_rec.quota_pay_element_id,
FND_API.G_MISS_NUM, null,
p_quota_pay_element_rec.quota_pay_element_id),
Decode(p_quota_pay_element_rec.quota_id,
FND_API.G_MISS_NUM, null,
p_quota_pay_element_rec.quota_id),
Decode(p_quota_pay_element_rec.pay_element_type_id,
FND_API.G_MISS_NUM, null,
p_quota_pay_element_rec.pay_element_type_id),
Decode(p_quota_pay_element_rec.status,
FND_API.G_MISS_CHAR, p_quota_pay_element_rec.status,
Ltrim(Rtrim(p_quota_pay_element_rec.status))),
Decode(p_quota_pay_element_rec.start_date,
FND_API.G_MISS_DATE, p_quota_pay_element_rec.start_date,
trunc(p_quota_pay_element_rec.start_date)),
Decode(p_quota_pay_element_rec.end_date,
FND_API.G_MISS_DATE, p_quota_pay_element_rec.end_date,
trunc(p_quota_pay_element_rec.end_date)),
Decode(p_quota_pay_element_rec.quota_name,
FND_API.G_MISS_CHAR, NULL ,
p_quota_pay_element_rec.quota_name),
Decode(p_quota_pay_element_rec.pay_element_name,
FND_API.G_MISS_CHAR, NULL ,
p_quota_pay_element_rec.pay_element_name)
INTO
l_quota_pay_element_rec.quota_pay_element_id,
l_quota_pay_element_rec.quota_id,
l_quota_pay_element_rec.pay_element_type_id,
l_quota_pay_element_rec.status,
l_quota_pay_element_rec.start_date,
l_quota_pay_element_rec.end_date,
l_quota_pay_element_rec.quota_name,
l_quota_pay_element_rec.pay_element_name
FROM dual;
cn_quota_pay_elements_pkg.update_row
(p_quota_pay_element_id => l_quota_pay_element_rec.quota_pay_element_id
,p_quota_id => l_quota_pay_element_rec.quota_id
,p_pay_element_type_id => l_quota_pay_element_rec.pay_element_type_id
,p_status => l_quota_pay_element_rec.status
,p_start_date => l_quota_pay_element_rec.start_date
,p_end_date => l_quota_pay_element_rec.end_date
,p_last_update_date => G_LAST_UPDATE_DATE
,p_last_updated_by => G_LAST_UPDATED_BY
,p_last_update_login => G_LAST_UPDATE_LOGIN
);
ROLLBACK TO Update_quota_pay_element;
ROLLBACK TO Update_quota_pay_element;
ROLLBACK TO Update_quota_pay_element;
END Update_quota_pay_element;
PROCEDURE Delete_quota_pay_element
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := CN_API.G_FALSE,
p_commit IN VARCHAR2 := CN_API.G_FALSE,
p_validation_level IN NUMBER := CN_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_quota_pay_element_id IN NUMBER,
x_loading_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Quota_Pay_Element';
SAVEPOINT Delete_Quota_Pay_element;
x_loading_status := 'CN_DELETED';
if check_delete_update_allowed(p_quota_pay_element_id) > 0 THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.SET_NAME ('CN' , 'CN_PAY_ELEMENT_ALREADY_USED');
cn_quota_pay_elements_pkg.delete_row
(p_quota_pay_element_id =>p_quota_pay_element_id);
ROLLBACK TO Delete_Quota_Pay_element;
ROLLBACK TO Delete_Quota_Pay_Element;
ROLLBACK TO Delete_Quota_Pay_element;
END Delete_Quota_Pay_Element;
l_select varchar2(4000) := 'SELECT cqpe.quota_pay_element_id,
cqpe.quota_id,
cqpe.pay_element_type_id,
cqpe.status,
cqpe.start_date,
cqpe.end_date,
cq.name,
cpet.element_name,
cpet.effective_start_date,
cpet.effective_end_Date
FROM cn_quota_pay_elements cqpe,
pay_element_types_f cpet,
cn_quota_lookups_v cq,
gl_sets_of_books glsob,
cn_repositories cnr
where
cnr.set_of_books_id = glsob.set_of_books_id
AND cpet.input_currency_code = glsob.currency_code
AND cqpe.quota_id = cq.quota_id
AND cqpe.pay_element_type_id = cpet.element_type_id
And cqpe.start_date >= cpet.effective_start_date
AND cqpe.end_date <= cpet.effective_end_Date
AND upper(cq.name) like upper(:B1)
AND upper(cpet.element_name) like upper(:B2) ';
l_select1 varchar2(4000) := 'SELECT cqpe.quota_pay_element_id,
cqpe.quota_id,
cqpe.pay_element_type_id,
cqpe.status,
cqpe.start_date,
cqpe.end_date,
cq.name,
cpet.element_name,
cpet.effective_start_date,
cpet.effective_end_Date
FROM cn_quota_pay_elements cqpe,
pay_element_types_f cpet,
cn_quota_lookups_v cq,
gl_sets_of_books glsob,
cn_repositories cnr
where
cnr.set_of_books_id = glsob.set_of_books_id
AND cpet.input_currency_code = glsob.currency_code
AND cqpe.quota_id = cq.quota_id
AND cqpe.pay_element_type_id = cpet.element_type_id
And cqpe.start_date >= cpet.effective_start_date
AND cqpe.end_date <= cpet.effective_end_Date ';
x_loading_status := 'SELECTED';
l_select := l_select1;
OPEN quota_cur FOR l_select;
OPEN quota_cur FOR l_select using p_quota_name, p_pay_element_name ;
x_loading_status := 'SELECTED';