[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_gl_status ( x_org_id NUMBER,
x_period_name VARCHAR2,
x_closing_status VARCHAR2,
x_forecast_flag VARCHAR2,
x_application_id NUMBER,
x_set_of_books_id NUMBER,
x_freeze_flag VARCHAR2,
x_last_update_date DATE,
x_last_update_login NUMBER,
x_last_updated_by NUMBER)
IS
CURSOR c IS
SELECT gl.closing_status, gl.start_date, gl.end_date,
gl.quarter_num, gl.period_year
FROM GL_PERIOD_STATUSES GL
WHERE gl.application_id = x_application_id
AND gl.adjustment_period_flag = 'N'
and gl.set_of_books_id = x_set_of_books_id
and gl.period_name = x_period_name;
SELECT cn.period_status
FROM cn_period_statuses cn
WHERE cn.period_name = p_period_name
AND cn.period_year = p_period_year
AND cn.org_id = x_org_id;
UPDATE gl_period_statuses
SET closing_status = x_closing_status,
last_update_date = x_last_update_date,
last_update_login = x_last_update_login,
last_updated_by = x_last_updated_by
WHERE period_name = x_period_name
AND application_id = x_application_id
AND set_of_books_id = x_set_of_books_id ;
UPDATE cn_period_statuses
SET period_status = x_closing_status,
forecast_flag = x_forecast_flag,
freeze_flag = x_freeze_flag,
last_update_date = x_last_update_date,
last_update_login = x_last_update_login,
last_updated_by = x_last_updated_by,
object_version_number = object_version_number + 1,
processing_status_code = 'PROCESSING'
WHERE period_name = x_period_name
AND org_id = x_org_id;
UPDATE cn_period_statuses
SET period_status = x_closing_status,
forecast_flag = x_forecast_flag,
freeze_flag = x_freeze_flag,
last_update_date = x_last_update_date,
last_update_login = x_last_update_login,
last_updated_by = x_last_updated_by,
object_version_number = object_version_number + 1
WHERE period_name = x_period_name
AND org_id = x_org_id ;
END update_gl_status ;
PROCEDURE update_error (x_physical_batch_id NUMBER) IS
l_user_id NUMBER(15) := fnd_global.user_id;
UPDATE cn_process_batches
SET status_code = 'ERROR'
,last_update_date = sysdate
,last_update_login = l_login_id
,last_updated_by = l_user_id
,request_id = l_conc_request_id
,program_application_id = l_prog_appl_id
,program_id = l_conc_prog_id
,program_update_date = sysdate
WHERE physical_batch_id = x_physical_batch_id;
END update_error;
SELECT DISTINCT physical_batch_id
FROM cn_process_batches
WHERE logical_batch_id = p_logical_batch_id;
l_primary_request_stack.delete;
l_primary_batch_stack.delete;
update_error(l_temp_phys_batch_id);
SELECT
period_id,
start_date,
end_date,
quarter_num,
period_year,
period_status
FROM cn_period_statuses
WHERE processing_status_code = 'PROCESSING'
AND period_id between l_start_period_id and l_end_period_id
AND request_id is null
ORDER BY period_id;
SELECT quota_id
FROM cn_quotas
WHERE (end_date IS NULL OR end_date >= l_start_date)
AND start_date <= l_end_date;
select cn_process_batches_s2.nextval into l_logical_batch_id from dual;
select min(period_id), max(period_id), min(start_date), max(end_date)
into l_start_period_id, l_end_period_id, l_start_date, l_end_date
from cn_period_statuses
where processing_status_code = 'PROCESSING';
insert into cn_process_batches
(process_batch_id,
logical_batch_id,
srp_period_id,
period_id,
end_period_id,
start_date,
end_date,
salesrep_id,
sales_lines_total,
status_code,
process_batch_type,
creation_date,
created_by,
org_id)
select cn_process_batches_s1.nextval,
l_logical_batch_id,
1,
cps.period_id,
cps.period_id,
cps.start_date,
cps.end_date,
v.salesrep_id,
0,
'IN_USE',
'OPENING_PERIODS',
sysdate,
fnd_global.user_id,
l_org_id
from (select distinct s.salesrep_id
from jtf_rs_role_relations rr,
cn_rs_salesreps s,
jtf_rs_roles_b r
where rr.role_resource_id = s.resource_id
and rr.role_resource_type = 'RS_INDIVIDUAL'
and rr.delete_flag = 'N'
and rr.role_id = r.role_id
and r.role_type_code = 'SALES_COMP'
and (rr.end_date_active IS NULL OR rr.end_date_active >= l_start_date)
and rr.start_date_active <= l_end_date) v,
cn_period_statuses cps
where cps.processing_status_code = 'PROCESSING'
and cps.period_id between l_start_period_id and l_end_period_id
and not exists
(select 1
from cn_process_batches
where logical_batch_id = (select logical_batch_id
from cn_period_statuses
where period_id = cps.period_id)
and salesrep_id = v.salesrep_id
and period_id = cps.period_id
and sales_lines_total = 1);
select cn_process_batches_s3.nextval into l_physical_batch_id from dual;
update cn_process_batches
set physical_batch_id = l_physical_batch_id
where logical_batch_id = l_logical_batch_id
and physical_batch_id is null
and rownum <= l_temp;
update cn_process_batches pb
set pb.physical_batch_id = (select min(physical_batch_id)
from cn_process_batches
where logical_batch_id = pb.logical_batch_id
and salesrep_id = pb.salesrep_id)
where pb.logical_batch_id = l_logical_batch_id;
(x_operation => 'INSERT',
x_period_quota_id => l_period_quota_id,
x_period_id => pending_period.period_id,
x_quota_id => affected_quota.quota_id,
x_period_target => 0,
x_itd_target => null, -- will be populated in table handler
x_period_payment => 0,
x_itd_payment => null, -- will be populated in table handler
x_quarter_num => pending_period.quarter_num,
x_period_year => pending_period.period_year,
x_creation_date => sysdate,
x_last_update_date => sysdate,
x_last_update_login => fnd_global.login_id,
x_last_updated_by => fnd_global.user_id,
x_created_by => fnd_global.user_id,
x_period_type_code => null, -- not used
x_performance_goal => 0
);
UPDATE cn_period_statuses
SET request_id = l_logical_batch_id
WHERE processing_status_code = 'PROCESSING'
AND period_id between l_start_period_id AND l_end_period_id;
fnd_file.put_line(fnd_file.Log, 'Step 3 : Quotas are updated successfully');
SELECT 1 INTO l_temp FROM dual
WHERE NOT exists (SELECT 1
FROM cn_process_batches
WHERE logical_batch_id = l_logical_batch_id AND status_code = 'ERROR');
UPDATE cn_period_statuses
SET processing_status_code = 'CLEAN'
WHERE processing_status_code = 'PROCESSING'
AND period_id between l_start_period_id AND l_end_period_id;
UPDATE cn_period_statuses
SET processing_status_code = 'FAILED'
WHERE processing_status_code = 'PROCESSING';
SELECT salesrep_id, period_id, org_id
FROM cn_process_batches
WHERE physical_batch_id = p_physical_batch_id
AND sales_lines_total = 0
ORDER BY period_id;
SELECT srp_plan_assign_id, role_id, comp_plan_id,
start_date, end_date, salesrep_id
FROM cn_srp_plan_assigns
WHERE salesrep_id = l_salesrep_id
AND role_id IS NOT null
AND start_date <= l_start_date AND end_date >= l_end_date;
select distinct q.credit_type_id
from cn_quota_assigns qa, cn_quotas q
where qa.comp_plan_id = l_comp_plan_id
and qa.quota_id = q.quota_id;
SELECT start_date, end_date, period_status
FROM cn_period_statuses
WHERE period_id = l_period_id;
SELECT spay.quota_id, spay.start_date, spay.end_date, spa.comp_plan_id
from cn_srp_payee_assigns spay, cn_srp_plan_assigns spa,
cn_srp_quota_assigns sqa
where spay.payee_id = l_salesrep_id
and (spay.end_date IS NULL OR spay.end_date >= l_start_date)
AND spay.start_date <= l_end_date
and spay.srp_quota_assign_id = sqa.srp_quota_assign_id
and sqa.srp_plan_assign_id = spa.srp_plan_assign_id;
SELECT COUNT(1) INTO l_count
FROM cn_srp_intel_periods
WHERE period_id = srp.period_id
AND salesrep_id = srp.salesrep_id
AND org_id = srp.org_id;
INSERT INTO cn_srp_intel_periods
(srp_intel_period_id,
salesrep_id,
org_id,
period_id,
processing_status_code,
process_all_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
start_date,
end_date
) VALUES
(cn_srp_intel_periods_s.NEXTVAL,
srp.salesrep_id,
l_org_id,
srp.period_id,
'CLEAN',
'Y',
Sysdate,
l_user_id,
Sysdate,
l_user_id,
l_login_id,
l_start_date,
l_end_date);
SELECT COUNT(1) INTO l_count
FROM cn_srp_roles
WHERE salesrep_id = srp.salesrep_id
AND role_id = 54
AND org_id = srp.org_id;
cn_srp_period_quotas_pkg.insert_record
(
x_srp_plan_assign_id => c.srp_plan_assign_id
,x_quota_id => NULL
,x_start_period_id => srp.period_id
,x_end_period_id => srp.period_id
,x_start_date => l_start_date
,x_end_date => l_end_date );
/* cn_srp_per_quota_rc_pkg.insert_record
(
x_srp_plan_assign_id => c.srp_plan_assign_id
,x_quota_id => null
,x_revenue_class_id => null
,x_start_date => l_start_date
,x_end_date => l_end_date );*/
INSERT INTO cn_srp_per_quota_rc
( srp_per_quota_rc_id
,srp_period_quota_id
,srp_plan_assign_id
,salesrep_id
,org_id
,period_id
,quota_id
,revenue_class_id
,target_amount
,year_to_date
,period_to_date
,quarter_to_date)
SELECT
cn_srp_per_quota_rc_s.nextval
,pq.srp_period_quota_id
,pq.srp_plan_assign_id
,pq.salesrep_id
,l_org_id
,pq.period_id
,pq.quota_id
,qr.revenue_class_id
,0 -- target amount
,0 -- ytd
,0 -- ptd
,0 -- qtd
FROM cn_srp_period_quotas pq -- periods that rep/plan uses quota
,cn_quota_rules qr
,cn_quotas q
WHERE pq.srp_plan_assign_id = c.srp_plan_assign_id
AND pq.quota_id = qr.quota_id
AND qr.quota_id = q.quota_id
AND q.quota_type_code IN ('EXTERNAL','FORMULA')
AND l_period_status in ('O','F')
AND NOT EXISTS (SELECT 'srp_period_quota_rc already exists'
FROM cn_srp_per_quota_rc spqr
WHERE spqr.srp_period_quota_id = pq.srp_period_quota_id
AND spqr.srp_plan_assign_id = pq.srp_plan_assign_id
AND spqr.revenue_class_id = qr.revenue_class_id)
;
UPDATE cn_process_batches
SET sales_lines_total = 1
WHERE physical_batch_id = p_physical_batch_id
AND salesrep_id = srp.salesrep_id
AND period_id = srp.period_id;
cn_message_pkg.debug('Update cn_period_statuses.processing_status_code');
UPDATE cn_period_statuses
SET processing_status_code = 'FAILED'
WHERE processing_status_code = 'PROCESSING'
AND period_id = l_curr_pd_id;
UPDATE cn_period_statuses
SET processing_status_code = 'FAILED'
WHERE processing_status_code = 'PROCESSING'
AND period_id = l_curr_pd_id;
UPDATE cn_period_statuses
SET processing_status_code = 'FAILED'
WHERE processing_status_code = 'PROCESSING'
AND period_id = l_curr_pd_id;
SELECT period_type_id
FROM cn_period_types
WHERE period_type = x_period_type
AND org_id = x_org_id;
SELECT period_set_id
FROM cn_period_sets
WHERE period_set_name = x_period_set_name
AND org_id = x_org_id;
SELECT period_set_id, period_type_id
FROM cn_repositories
WHERE org_id = x_org_id;
SELECT interval_type_id
FROM cn_interval_types
WHERE org_id = x_org_id;
SELECT interval_number
FROM cn_cal_per_int_types
WHERE interval_type_id = p_interval_type_id
AND org_id = x_org_id
ORDER BY Abs(cal_period_id - x_period_id);
SELECT cn_period_types_s.NEXTVAL
INTO x_period_type_id
FROM dual;
INSERT INTO cn_period_types
(period_type_id,
period_type,
org_id)
VALUES
(x_period_type_id,
x_period_type,
x_org_id);
SELECT cn_period_sets_s.NEXTVAL
INTO x_period_set_id
FROM dual;
INSERT INTO cn_period_sets
(period_set_id,
period_set_name,
org_id)
VALUES
(x_period_set_id,
x_period_set_name,
x_org_id);
SELECT period_id
INTO x_dummy
FROM cn_period_statuses
WHERE period_id = x_period_id
AND org_id = x_org_id;
INSERT INTO cn_period_statuses
(period_id,
period_name,
period_type_id,
period_status,
period_type,
period_year,
quarter_num,
start_date,
end_date,
forecast_flag,
period_set_name,
period_set_id,
freeze_flag,
processing_status_code,
org_id)
VALUES (x_period_id,
x_period_name,
x_period_type_id,
x_closing_status,
x_period_type,
x_period_year,
x_quarter_num,
x_start_date,
x_end_date,
'N',
x_period_set_name,
x_period_set_id,
x_freeze_flag,
Decode(x_closing_status,'O','PROCESSING','F','PROCESSING','CLEAN'), --1979768
x_org_id);
-- In addition, the newly activated period should be inserted into cn_calc_per_int_types
-- for the relevant interval types.
OPEN repository;
cn_int_assign_pkg.insert_row
(x_cal_per_int_type_id => l_cal_per_int_type_id,
x_interval_type_id => interval_type.interval_type_id,
x_cal_period_id => x_period_id,
x_org_id => x_org_id,
x_interval_number => l_interval_number,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_creation_date => sysdate,
x_created_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id
);
-- UPDATE cn_repositories
-- SET status = 'F'
-- WHERE repository_id = x_repository_id ;
SELECT cn.start_date, cn.end_date
FROM CN_PERIOD_STATUSES CN, CN_REPOSITORIES_ALL RP
WHERE rp.period_set_id = cn.period_set_id
AND rp.period_type_id = cn.period_type_id
AND cn.org_id = rp.org_id
AND cn.org_id = x_org_id
AND period_id = p_period_id;
SELECT MIN(cn.start_date)
FROM CN_PERIOD_STATUSES CN, CN_REPOSITORIES_ALL RP
WHERE rp.period_set_id = cn.period_set_id
AND rp.period_type_id = cn.period_type_id
AND cn.org_id = rp.org_id
AND cn.org_id = x_org_id;