[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT payment_worksheet_id, object_version_number
INTO l_obj_id, l_ovn
FROM cn_payment_worksheets
WHERE payrun_id = p_payrun_id
AND salesrep_id = p_salesrep_id
AND quota_id IS NULL ;
SELECT payrun_id, object_version_number
INTO l_obj_id, l_ovn
FROM cn_payruns
WHERE payrun_id = p_payrun_id ;
l_list.DELETE;
select payrun_mode
into l_ret_val
from cn_payruns
where payrun_id = p_payrun_id ;
SELECT resource_id
INTO l_resource_id
FROM jtf_rs_resource_extns
WHERE user_id = fnd_global.user_id;
SELECT SUM (DECODE (resource_id, parent_resource_id, 0, 1))
INTO l_tmp
FROM
-- check if user is in analyst hierarchy in this period,
-- if view empty, not exist and not a super user, l_tmp will become NULL
(SELECT m1.parent_resource_id,
m1.resource_id
FROM cn_period_statuses pr,
jtf_rs_group_usages u1,
jtf_rs_rep_managers m1
WHERE p_period_id IS NOT NULL
AND pr.period_id = p_period_id
AND pr.org_id=p_org_id
AND u1.USAGE = 'COMP_PAYMENT'
AND ((m1.start_date_active <= pr.end_date) AND (pr.start_date <= NVL (m1.end_date_active, pr.start_date)))
AND u1.GROUP_ID = m1.GROUP_ID
AND m1.resource_id = l_resource_id
AND m1.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP')
AND m1.CATEGORY <> 'TBH') v1;
SELECT resource_id
INTO l_resource_id
FROM jtf_rs_resource_extns
WHERE user_id = fnd_global.user_id;
SELECT 1
INTO l_tmp
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM cn_period_statuses pr,
jtf_rs_group_usages u1,
jtf_rs_rep_managers m1
WHERE p_period_id IS NOT NULL
AND pr.period_id = p_period_id
AND pr.org_id=p_org_id
AND u1.USAGE = 'COMP_PAYMENT'
AND ((m1.start_date_active <= pr.end_date) AND (pr.start_date <= NVL (m1.end_date_active, pr.start_date)))
AND u1.GROUP_ID = m1.GROUP_ID
AND m1.parent_resource_id = l_resource_id
AND m1.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP')
AND m1.CATEGORY <> 'TBH');
SELECT payment_worksheet_id
FROM cn_payment_worksheets
WHERE payrun_id = p_payrun_id;
SELECT object_version_number
FROM cn_payruns
WHERE payrun_id = p_payrun_id;
cn_payruns_pkg.UPDATE_RECORD (x_payrun_id => p_payrun_id,
x_status => 'PAID',
x_last_updated_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_update_login => fnd_global.login_id,
x_object_version_number => l_ovn
);
SELECT object_version_number
FROM cn_payruns
WHERE payrun_id = p_payrun_id;
l_has_access := get_security_access (g_type_payrun, g_access_payrun_delete);
l_event_name := 'delete' ;
cn_payruns_pkg.UPDATE_RECORD (x_payrun_id => p_payrun_id,
x_status => l_new_status,
x_last_updated_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_update_login => fnd_global.login_id,
x_object_version_number => l_ovn
);
l_event_name := 'delete' ;
cn_payment_worksheets_pkg.UPDATE_STATUS (p_salesrep_id => p_salesrep_id,
p_payrun_id => p_payrun_id,
p_worksheet_status => l_new_status
);
SELECT status
FROM cn_payruns
WHERE payrun_id = p_payrun_id
FOR UPDATE OF status NOWAIT
;
SELECT worksheet_status
FROM cn_payment_worksheets
WHERE payrun_id = p_payrun_id
FOR UPDATE OF worksheet_status NOWAIT
;
SELECT pay_period_id,
status
INTO l_period_id,
l_payrun_status
FROM cn_payruns
WHERE payrun_id = p_payrun_id;
SELECT COUNT(1)
INTO l_temp
FROM cn_payment_worksheets
WHERE worksheet_status IN ('PROCESSING', 'FAILED')
AND payrun_id = p_payrun_id
AND rownum < 2;
l_has_access := get_security_access (g_type_payrun, g_access_payrun_delete);
SELECT 1
INTO l_tmp
FROM DUAL
WHERE EXISTS (SELECT 1
FROM cn_payment_worksheets
WHERE payrun_id = p_payrun_id AND worksheet_status <> 'APPROVED');
SELECT status
FROM cn_payruns
WHERE payrun_id = c_payrun_id
--FOR UPDATE OF status NOWAIT
;
SELECT worksheet_status
FROM cn_payment_worksheets
WHERE payment_worksheet_id = p_worksheet_id
--FOR UPDATE OF worksheet_status NOWAIT
;
SELECT wk.payrun_id,
wk.salesrep_id,
wk.worksheet_status,
pay.status,
pay.pay_period_id,
s.assigned_to_user_id,
pay.org_id
INTO l_worksheet_rec.payrun_id,
l_worksheet_rec.salesrep_id,
l_worksheet_rec.worksheet_status,
l_payrun_status,
l_pay_period_id,
l_assigned_to_user_id,
l_org_id
FROM cn_payment_worksheets wk,
cn_payruns pay,
cn_salesreps s
WHERE wk.payment_worksheet_id = p_worksheet_id
AND pay.payrun_id = wk.payrun_id
AND s.salesrep_id = wk.salesrep_id
--R12
AND wk.org_id = pay.org_id
AND wk.org_id = s.org_id;
SELECT resource_id
INTO l_resource_id
FROM jtf_rs_resource_extns
WHERE user_id = fnd_global.user_id;
SELECT 1
INTO l_tmp
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM jtf_rs_group_usages u2,
jtf_rs_rep_managers m2,
jtf_rs_resource_extns_vl re2,
-- start inline view
-- get all rows for a login user in jtf_rs_rep_managers
-- with period = p_period_id
(SELECT DISTINCT m1.resource_id,
GREATEST (pr.start_date, m1.start_date_active) start_date,
LEAST (pr.end_date, NVL (m1.end_date_active, pr.end_date)) end_date
FROM cn_period_statuses pr,
jtf_rs_group_usages u1,
jtf_rs_rep_managers m1
WHERE pr.period_id = l_pay_period_id
AND pr.org_id=l_org_id
AND u1.USAGE = 'COMP_PAYMENT'
AND m1.resource_id = l_resource_id
AND ( (m1.start_date_active <= pr.end_date)
AND (pr.start_date <= NVL (m1.end_date_active, pr.start_date))
)
AND u1.GROUP_ID = m1.GROUP_ID
AND m1.parent_resource_id = m1.resource_id
AND m1.hierarchy_type IN ('MGR_TO_MGR', 'REP_TO_REP')
AND m1.CATEGORY <> 'TBH') v3
-- end inlive view v3
WHERE re2.user_id = l_assigned_to_user_id
AND u2.USAGE = 'COMP_PAYMENT'
AND u2.GROUP_ID = m2.GROUP_ID
AND m2.parent_resource_id = v3.resource_id
AND ((m2.start_date_active <= v3.end_date) AND (v3.start_date <= NVL (m2.end_date_active, v3.start_date)))
AND m2.CATEGORY <> 'TBH'
AND m2.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP', 'REP_TO_REP')
AND m2.resource_id = re2.resource_id);
l_has_access := get_security_access (g_type_wksht, g_access_wksht_delete);
SELECT 1
INTO l_tmp
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM jtf_rs_group_usages u2,
jtf_rs_rep_managers m2,
-- start inline view
-- get all rows for a login user in jtf_rs_rep_managers
-- with period = p_period_id
(SELECT DISTINCT m1.resource_id,
GREATEST (pr.start_date, m1.start_date_active) start_date,
LEAST (pr.end_date, NVL (m1.end_date_active, pr.end_date)) end_date
FROM cn_period_statuses pr,
jtf_rs_group_usages u1,
jtf_rs_rep_managers m1
WHERE pr.period_id = l_pay_period_id
AND pr.org_id=l_org_id
AND u1.USAGE = 'COMP_PAYMENT'
AND m1.resource_id = l_resource_id
AND ( (m1.start_date_active <= pr.end_date)
AND (pr.start_date <= NVL (m1.end_date_active, pr.start_date))
)
AND u1.GROUP_ID = m1.GROUP_ID
AND m1.parent_resource_id = m1.resource_id
AND m1.hierarchy_type IN ('MGR_TO_MGR', 'REP_TO_REP')
AND m1.CATEGORY <> 'TBH') v3
-- end inlive view v3
WHERE u2.USAGE = 'COMP_PAYMENT'
AND u2.GROUP_ID = m2.GROUP_ID
AND m2.parent_resource_id = v3.resource_id
AND ((m2.start_date_active <= v3.end_date) AND (v3.start_date <= NVL (m2.end_date_active, v3.start_date)))
AND m2.CATEGORY <> 'TBH'
AND m2.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP', 'REP_TO_REP')
AND m2.resource_id IN (SELECT DISTINCT submit_to_resource_id
FROM cn_pay_approval_flow
WHERE payment_worksheet_id = p_worksheet_id));
SELECT COUNT (1)
INTO l_tmp
FROM cn_pay_approval_flow
WHERE payment_worksheet_id = p_worksheet_id
AND submit_by_user_id = fnd_global.user_id AND approval_status = 'APPROVED';
SELECT DECODE( (SELECT 1 FROM dual WHERE EXISTS ( SELECT 1
FROM jtf_rs_group_usages u2,jtf_rs_rep_managers m2,jtf_rs_resource_extns_vl re2,
(SELECT DISTINCT m1.resource_id, GREATEST(pr.start_date,m1.start_date_active) start_date,LEAST(pr.end_date,Nvl(m1.end_date_active,pr.end_date)) end_date
FROM cn_period_statuses pr,jtf_rs_group_usages u1, jtf_rs_rep_managers m1
WHERE pr.period_id = (SELECT p1.pay_period_id FROM cn_payruns p1 WHERE p1.payrun_id = p_payrun_id)
AND pr.org_id =(SELECT org_id FROM cn_payruns where payrun_id=p_payrun_id)
AND u1.usage = 'COMP_PAYMENT'
AND m1.resource_id = ( SELECT resource_id FROM jtf_rs_resource_extns WHERE user_id = p_user_id)
AND ((m1.start_date_active <= pr.end_date) AND (pr.start_date <= Nvl(m1.end_date_active,pr.start_date)))
AND u1.group_id = m1.group_id
AND m1.parent_resource_id = m1.resource_id
AND m1.hierarchy_type IN ('MGR_TO_MGR','REP_TO_REP') AND m1.category <> 'TBH' ) v3
WHERE u2.usage = 'COMP_PAYMENT'
AND u2.group_id = m2.group_id
AND m2.parent_resource_id = v3.resource_id AND ((m2.start_date_active <= v3.end_date)
AND (v3.start_date <= Nvl(m2.end_date_active,v3.start_date))) AND m2.category <> 'TBH'
AND m2.hierarchy_type IN ('MGR_TO_MGR','MGR_TO_REP','REP_TO_REP')
AND m2.resource_id = re2.resource_id
AND re2.user_id = Nvl(p_assigned_to_user_id,re2.user_id))) ,1,'Y',NULL,'N','N')
into ret
from dual;
select decode(l_dret,l_sret,decode(l_dret,'Y','Y','N'),'N') into l_sret from dual;