[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
count(1)
from cst_pac_periods
where legal_entity = l_entity_id
and cost_type_id = l_cost_type_id
AND rownum < 2;
select pac_period_id
from cst_pac_periods
where
legal_entity = l_entity_id
and cost_type_id = l_cost_type_id
and pac_period_id NOT in
(select pac_period_id
from cst_pac_periods
where legal_entity = l_entity_id
and cost_type_id = l_cost_type_id
and open_flag = 'N'
and period_close_date IS NOT NULL);
select end_date, period_name, period_year, period_num
from gl_periods gp
WHERE gp.ADJUSTMENT_PERIOD_FLAG = 'N'
and gp.period_set_name = open_period_set_name
and gp.end_date > last_scheduled_close_date
and gp.period_type = open_period_type
and gp.end_date = (
select min(gp1.end_date)
from gl_periods gp1
where gp1.ADJUSTMENT_PERIOD_FLAG = 'N'
and gp1.period_set_name = open_period_set_name
and gp1.period_type = open_period_type
and gp1.end_date > last_scheduled_close_date );
/* the cst_pac_process_phases table. However the row inserted into */
/* cst_pac_periods have already been commited, hence need to be explicitly */
/* deleted. The section below does that... */
/* **************************************************************************** */
l_stmt_num := 20;
delete from cst_pac_periods
where pac_period_id = new_pac_period_id;
select cst_pac_periods_s.nextval
from dual;
select new_pac_period_id
from cst_pac_periods
where legal_entity = l_entity_id
and cost_type_id = l_cost_type_id
and period_name = open_period_name
and period_year = open_period_year
and period_num = open_period_num
and pac_period_id <> new_pac_period_id;
select cost_group_id
from cst_cost_groups ccg
where ccg.legal_entity = l_entity_id
and ccg.cost_group_type = 2
and NVL(ccg.disable_date, sysdate) >= sysdate;
SELECT NVL(CREATE_ACCT_ENTRIES,'N')
, nvl(transfer_cost_flag,'N')
INTO distributions_flag
, transfer_cost_flag
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2;
/* 1. Insert into cst_pac_periods form gl_periods table */
/* 2. For each an every cost group defined in the legal entity */
/* Insert five rows for five process statuses into */
/* cst_pac_process_phases */
/* **************************************************************************** */
/* **************************************************************************** */
/* Insert a single row for the pac period being opened into cst_pac_periods */
/* Insert the rows with 'P' (Pending) status and period close date = sysdate */
/* **************************************************************************** */
l_stmt_num := 30;
INSERT INTO cst_pac_periods (
pac_period_id,
legal_entity,
cost_type_id,
period_start_date,
period_end_date,
open_flag,
period_year,
period_num,
period_name,
period_set_name,
period_close_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login )
SELECT new_pac_period_id,
l_entity_id,
l_cost_type_id,
gp.start_date,
gp.end_date,
'P',
gp.period_year,
gp.period_num,
gp.period_name,
gp.period_set_name,
SYSDATE,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
-1
FROM gl_periods gp
WHERE gp.period_name = open_period_name
AND gp.period_num = open_period_num
AND gp.period_year = open_period_year
AND gp.period_set_name = (select gsob.period_set_name
from gl_sets_of_books gsob, cst_le_cost_types clct
where gsob.set_of_books_id = clct.set_of_books_id
and clct.legal_entity = l_entity_id
and clct.cost_type_id = l_cost_type_id
and clct.primary_cost_method > 2)
AND (gp.period_name, gp.period_num, gp.period_year) NOT IN
(select period_name, period_num, period_year
from cst_pac_periods
where legal_entity = l_entity_id
and cost_type_id = l_cost_type_id);
/* Insert a row for each and every cost group and phases into */
/* cst_pac_process_phases table */
/* **************************************************************************** */
l_stmt_num := 50;
INSERT INTO cst_pac_process_phases (
pac_period_id,
cost_group_id,
process_phase,
process_status,
process_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login )
SELECT
new_pac_period_id,
current_cost_group_id,
phase_count,
DECODE(phase_count,6,DECODE(distributions_flag,'Y',1,0), 7,DECODE(transfer_cost_flag,'Y',1,0),8,DECODE(transfer_cost_flag,'Y',1,0),1),
NULL,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
-1
FROM dual;
goto cppp_insert_done_label;
<>
NULL;
delete from cst_pac_process_phases where pac_period_id = dummy_id;
/* Update the the new pac period row in cst_pac_periods with open_flag = 'Y' */
/* and the period close date = NULL, this declaring the period as open */
/* **************************************************************************** */
l_stmt_num := 70;
UPDATE cst_pac_periods
SET open_flag = 'Y',
period_close_date = NULL,
last_update_date = trunc(sysdate),
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE pac_period_id = new_pac_period_id;
SELECT NVL(MAX(period_end_date),sysdate)
INTO last_scheduled_close_date
FROM cst_pac_periods
WHERE legal_entity = l_entity_id
AND cost_type_id = l_cost_type_id;
/* the cst_pac_process_phases table. However the row inserted into */
/* cst_pac_periods have already been commited, hence need to be explicitly */
/* deleted. The section below does that... */
/* **************************************************************************** */
delete from cst_pac_periods
where pac_period_id = new_pac_period_id;
SELECT count(1)
FROM gl_period_statuses gps
WHERE gps.application_id = 200
AND gps.closing_status <> 'C'
AND trunc(gps.end_date) = trunc(p_closing_end_date)
AND gps.set_of_books_id = ( SELECT distinct clct.set_of_books_id
FROM cst_le_cost_types clct
WHERE clct.cost_type_id = p_cost_type_id
AND clct.legal_entity = p_entity_id
)
AND rownum < 2;
SELECT count(1)
FROM org_acct_periods
WHERE open_flag IN ('Y','P')
AND trunc(schedule_close_date) <=
(select trunc(period_end_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND organization_id IN ( SELECT ccga.organization_id
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = l_current_cost_group_id
)
AND rownum < 2;
SELECT count(1)
FROM mtl_material_transactions mmt
WHERE mmt.creation_date > ( SELECT MIN(cppp.process_date)
FROM cst_pac_process_phases cppp
WHERE
(( cppp.process_phase <= 5
AND cppp.process_upto_date IS NOT NULL)
OR
( cppp.process_phase = 6
AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2
AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
AND cppp.process_upto_date IS NOT NULL
))
AND cppp.pac_period_id = l_closing_pac_period_id
/* bug 2658552 */
AND cppp.cost_group_id = l_current_cost_group_id
)
AND mmt.organization_id IN
(select ccga.organization_id
from cst_cost_group_assignments ccga
where ccga.cost_group_id = l_current_cost_group_id)
AND mmt.transaction_date >=
(select trunc(period_start_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND mmt.transaction_date <=
(select (trunc(period_end_date) + 0.99999)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rownum < 2;
SELECT count(1)
FROM wip_transactions wt
WHERE wt.creation_date > ( SELECT MIN(cppp.process_date)
FROM cst_pac_process_phases cppp
WHERE
(( cppp.process_phase <= 5
AND cppp.process_upto_date IS NOT NULL)
OR
( cppp.process_phase = 6
AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2
AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
AND cppp.process_upto_date IS NOT NULL
))
AND cppp.pac_period_id = l_closing_pac_period_id
/* bug 2658552 */
AND cppp.cost_group_id = l_current_cost_group_id
)
AND wt.organization_id IN
(select ccga.organization_id
from cst_cost_group_assignments ccga
where ccga.cost_group_id = l_current_cost_group_id)
AND wt.transaction_date >=
(select trunc(period_start_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND wt.transaction_date <=
(select (trunc(period_end_date) + 0.99999)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rownum < 2;
SELECT count(1)
FROM rcv_transactions rt
WHERE rt.creation_date > ( SELECT MIN(cppp.process_date)
FROM cst_pac_process_phases cppp
WHERE
(( cppp.process_phase <= 5
AND cppp.process_upto_date IS NOT NULL)
OR
( cppp.process_phase = 6
AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2
AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
AND cppp.process_upto_date IS NOT NULL
))
AND cppp.pac_period_id = l_closing_pac_period_id
/* bug 2658552 */
AND cppp.cost_group_id = l_current_cost_group_id
)
AND rt.organization_id IN
(select ccga.organization_id
from cst_cost_group_assignments ccga
where ccga.cost_group_id = l_current_cost_group_id)
AND rt.transaction_date >=
(select trunc(period_start_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rt.transaction_date <=
(select (trunc(period_end_date) + 0.99999)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rownum < 2;
SELECT count(1)
FROM rcv_accounting_events rae
WHERE rae.creation_date > ( SELECT MIN(cppp.process_date)
FROM cst_pac_process_phases cppp
WHERE
(( cppp.process_phase <= 5
AND cppp.process_upto_date IS NOT NULL)
OR
( cppp.process_phase = 6
AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2
AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
AND cppp.process_upto_date IS NOT NULL
))
AND cppp.pac_period_id = l_closing_pac_period_id
AND cppp.cost_group_id = l_current_cost_group_id
)
AND rae.organization_id IN
(select ccga.organization_id
from cst_cost_group_assignments ccga
where ccga.cost_group_id = l_current_cost_group_id)
AND rae.transaction_date >=
(select trunc(period_start_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rae.transaction_date <=
(select (trunc(period_end_date) + 0.99999)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rae.event_type_id IN (7,8, 9, 10)
AND rownum < 2;
SELECT count(1)
FROM cst_lc_adj_transactions clat
WHERE clat.creation_date > ( SELECT MIN(cppp.process_date)
FROM cst_pac_process_phases cppp
WHERE
(( cppp.process_phase <= 5
AND cppp.process_upto_date IS NOT NULL)
OR
( cppp.process_phase = 6
AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2
AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
AND cppp.process_upto_date IS NOT NULL
))
AND cppp.pac_period_id = l_closing_pac_period_id
AND cppp.cost_group_id = l_current_cost_group_id
)
AND clat.organization_id IN
(select ccga.organization_id
from cst_cost_group_assignments ccga
where ccga.cost_group_id = l_current_cost_group_id)
AND clat.transaction_date >=
(select trunc(period_start_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND clat.transaction_date <=
(select (trunc(period_end_date) + 0.99999)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rownum < 2;
select cost_group_id
from cst_cost_groups ccg
where ccg.legal_entity = l_entity_id
and ccg.cost_group_type = 2
and NVL(ccg.disable_date, sysdate) >= sysdate
and ccg.cost_group_id IN (
SELECT distinct cost_group_id
FROM cst_cost_group_assignments
WHERE legal_entity = l_entity_id );
SELECT count(1)
FROM mtl_material_transactions_temp mmtt
WHERE NVL(mmtt.transaction_status,0) <> 2
AND mmtt.organization_id IN
(select ccga.organization_id
from cst_cost_group_assignments ccga
where ccga.cost_group_id = l_current_cost_group_id)
AND mmtt.transaction_date >=
(select trunc(period_start_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND mmtt.transaction_date <=
(select (trunc(period_end_date)+0.99999)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rownum < 2;
SELECT count(1)
FROM mtl_transactions_interface mti
WHERE mti.organization_id IN
(select ccga.organization_id
from cst_cost_group_assignments ccga
where ccga.cost_group_id = l_current_cost_group_id)
AND mti.transaction_date >=
(select trunc(period_start_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND mti.transaction_date <=
(select (trunc(period_end_date)+0.99999)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rownum < 2;
SELECT count(1)
FROM wip_cost_txn_interface wcti
WHERE wcti.organization_id IN
(select ccga.organization_id
from cst_cost_group_assignments ccga
where ccga.cost_group_id = l_current_cost_group_id)
AND wcti.transaction_date >=
(select trunc(period_start_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND wcti.transaction_date <=
(select (trunc(period_end_date)+0.99999)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rownum < 2;
SELECT count(1)
FROM rcv_transactions_interface rti
WHERE rti.to_organization_code IN
(select mp.organization_code
from cst_cost_group_assignments ccga,
mtl_parameters mp
where ccga.cost_group_id = l_current_cost_group_id
and ccga.organization_id = mp.organization_id)
AND rti.transaction_date >=
(select trunc(period_start_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rti.transaction_date <=
(select (trunc(period_end_date)+0.99999)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rownum < 2;
SELECT count(1)
FROM cst_lc_adj_interface lci
WHERE lci.organization_id IN
(select ccga.organization_id
from cst_cost_group_assignments ccga
where ccga.cost_group_id = l_current_cost_group_id)
AND lci.transaction_date >=
(select trunc(period_start_date)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND lci.transaction_date <=
(select (trunc(period_end_date)+0.99999)
from cst_pac_periods
where pac_period_id = l_closing_pac_period_id )
AND rownum < 2;
/* Bug 3591905. The following Select statement was checking for process_phase < 5 instead of process_phase <= 5 */
l_stmt_num := 20;
SELECT count(1)
INTO count_rows
FROM cst_pac_process_phases
WHERE pac_period_id = closing_pac_period_id
AND cost_group_id = current_cost_group_id
AND (( process_status <> 4
AND process_phase <= 5
)
OR
( process_status <> 4
AND process_phase = 6
AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2
AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
))
AND rownum < 2;
/* Bug 3591905. The following Select statement was checking for process_phase < 5 instead of process_phase <= 5 */
l_stmt_num := 30;
SELECT distinct NVL(pac_period_id,0)
INTO dummy_id
FROM cst_pac_process_phases
WHERE pac_period_id = closing_pac_period_id
AND cost_group_id = current_cost_group_id
AND (( process_status <> 4
AND process_phase <= 5
)
OR
( process_status <> 4
AND process_phase = 6
AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2
AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
));
/* Bug 3591905. The following Select statement was checking for process_phase < 5 instead of process_phase <= 5 */
l_stmt_num := 40;
SELECT count(1)
INTO count_rows
FROM cst_pac_process_phases
WHERE pac_period_id = closing_pac_period_id
AND cost_group_id = current_cost_group_id
AND (
(( process_date < trunc(closing_end_date)+1
AND process_phase <= 5
)
OR
( process_date < trunc(closing_end_date)+1
AND process_phase = 6
AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2
AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
))
OR
(( trunc(NVL(process_upto_date,closing_end_date-1)) < trunc(closing_end_date)
AND process_phase <= 5
)
OR
( trunc(NVL(process_upto_date,closing_end_date-1)) < trunc(closing_end_date)
AND process_phase = 6
AND EXISTS ( SELECT CREATE_ACCT_ENTRIES
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2
AND NVL(CREATE_ACCT_ENTRIES,'N') = 'Y')
))
)
AND rownum < 2;
SELECT distinct clct.set_of_books_id,
nvl(clct.create_acct_entries,'N')
INTO l_ledger_id,
l_create_acct_entries
FROM cst_le_cost_types clct
WHERE clct.cost_type_id = l_cost_type_id
AND clct.legal_entity = l_entity_id;
SELECT effective_period_num
INTO l_effective_period_num
FROM gl_period_statuses
WHERE ledger_id = l_ledger_id
AND application_id = 101
AND adjustment_period_flag = 'N' -- Added for bug#4634513
AND closing_end_date BETWEEN start_date AND end_date;
SELECT count(1)
INTO l_phase2_required
FROM cst_revenue_cogs_control
WHERE control_id = 1
AND last_process_upto_date < closing_end_date
AND rownum < 2;
SELECT min(crrl.acct_period_num)
INTO l_phase3_required
FROM cst_revenue_recognition_lines crrl,
cst_revenue_cogs_match_lines crcml,
cst_cost_group_assignments ccga,
cst_cost_groups ccg
WHERE crrl.ledger_id = l_ledger_id
AND crrl.potentially_unmatched_flag = 'Y'
AND crrl.revenue_om_line_id = crcml.revenue_om_line_id
AND crcml.organization_id = ccga.organization_id
AND ccga.cost_group_id = ccg.cost_group_id
AND ccg.legal_entity = l_entity_id
AND ccg.cost_group_type = 2
AND NVL(ccg.disable_date, sysdate) >= sysdate;
UPDATE cst_pac_periods
SET open_flag = l_open_flag,
period_close_date = trunc(sysdate),
last_update_date = trunc(sysdate),
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE pac_period_id = closing_pac_period_id;
select cost_group_id
from cst_cost_groups ccg
where ccg.legal_entity = l_entity_id
and ccg.cost_group_type = 2
and NVL(ccg.disable_date, sysdate) >= sysdate
and ccg.cost_group_id IN (
SELECT distinct cost_group_id
FROM cst_cost_group_assignments
WHERE legal_entity = l_entity_id );
SELECT pac_period_id
FROM cst_pac_periods
WHERE legal_entity = l_entity_id
AND cost_type_id = l_cost_type_id
AND pac_period_id = closing_pac_period_id
AND open_flag = 'P';
SELECT NVL(CREATE_ACCT_ENTRIES,'N')
INTO distributions_flag
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = l_entity_id
AND COST_TYPE_ID = l_cost_type_id
AND PRIMARY_COST_METHOD > 2;
UPDATE cst_pac_periods
SET open_flag = l_open_flag,
period_close_date = trunc(sysdate),
last_update_date = trunc(sysdate),
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE pac_period_id = closing_pac_period_id;
UPDATE cst_pac_periods
SET open_flag = 'Y',
period_close_date = trunc(sysdate),
last_update_date = trunc(sysdate),
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE pac_period_id = closing_pac_period_id;
UPDATE cst_pac_periods
SET open_flag = 'Y',
period_close_date = trunc(sysdate),
last_update_date = trunc(sysdate),
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE pac_period_id = closing_pac_period_id;