[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
OPEN_FLAG
INTO
l_open_flag
FROM
org_acct_periods oop
,org_organization_definitions ood
WHERE oop.acct_period_id = p_acct_period_id
AND oop.organization_id = ood.organization_id
AND ood.organization_name = p_org;
SELECT
OPEN_FLAG
INTO
l_open_flag
FROM
org_acct_periods oop
,HR_ORGANIZATION_UNITS HOU
WHERE oop.acct_period_id = p_acct_period_id
AND oop.organization_id = HOU.organization_id
AND HOU.name = p_org;
SELECT phase_code
FROM FND_CONCURRENT_REQUESTS
WHERE request_id = c_request_id;
SELECT
MAX( acct_period_id )
INTO
l_max_period_id
FROM
org_acct_periods orgp
WHERE orgp.organization_id = p_org_id
AND orgp.period_set_name = p_period_set_name
AND orgp.open_flag = 'Y' ;
SELECT
glp.period_name
, glp.start_date
, glp.end_date
INTO
x_period_name
, x_period_start_date
, x_period_end_date
FROM
gl_periods glp
, org_acct_periods orgp
WHERE glp.period_name = orgp.period_name
AND glp.period_set_name = p_period_set_name
AND glp.period_type = p_period_type
AND orgp.acct_period_id = l_max_period_id
AND orgp.organization_id = p_org_id;
SELECT
MIN( acct_period_id )
INTO
l_min_period_id
FROM
org_acct_periods orgp
WHERE orgp.organization_id = p_org_id
AND orgp.period_set_name = p_period_set_name ;
SELECT
glp.period_name
, glp.start_date
, glp.end_date
INTO
x_period_name
, x_period_start_date
, x_period_end_date
FROM
gl_periods glp
, org_acct_periods orgp
WHERE glp.period_name = orgp.period_name
AND glp.period_set_name = p_period_set_name
AND glp.period_type = p_period_type
AND orgp.acct_period_id = l_min_period_id
AND orgp.organization_id = p_org_id;
SELECT
name
, date_from
FROM
HR_organization_units
WHERE ORGANIZATION_ID = c_org_id;
SELECT
glp.PERIOD_SET_NAME open_period_set_name
, glp.PERIOD_NAME open_period_name
, glp.START_DATE period_start_date
, glp.END_DATE period_end_date
, glp.PERIOD_TYPE acct_period_type
, glp.PERIOD_YEAR open_period_year
, glp.PERIOD_NUM open_period_num
FROM
GL_PERIODS glp
WHERE glp.ADJUSTMENT_PERIOD_FLAG = 'N'
AND glp.period_type = l_sob_period_type
AND glp.PERIOD_SET_NAME = l_period_set_name
AND glp.PERIOD_NAME NOT IN
( SELECT OAP.PERIOD_NAME
FROM ORG_ACCT_PERIODS OAP
WHERE OAP.PERIOD_SET_NAME = glp.PERIOD_SET_NAME
AND OAP.PERIOD_NAME = glp.PERIOD_NAME
AND OAP.organization_id = c_org_id
)
AND glp.end_date <= NVL( l_cursor_final_end_date + 1, glp.end_date )
AND glp.end_date >= l_org_from_date
ORDER BY glp.start_date ;
SELECT
orgp.rowid closing_rowid
, orgp.ACCT_PERIOD_ID closing_acct_period_id
, orgp.ORGANIZATION_ID organization_id
, orgp.period_start_date period_start_date
, orgp.PERIOD_CLOSE_DATE period_close_date
, orgp.SCHEDULE_CLOSE_DATE schedule_close_date
, orgp.PERIOD_YEAR open_period_year
, orgp.PERIOD_NUM open_period_num
, orgp.PERIOD_NAME open_period_name
, orgp.open_flag open_flag
FROM
org_acct_periods orgp
WHERE orgp.period_name = p_close_period_name
AND orgp.organization_id = l_orgid
AND orgp.period_set_name = l_period_set_name ;
G_LOG_REPORT_TABLE.DELETE ;
SELECT name
INTO l_hierarchy_name
FROM per_organization_structures
WHERE organization_structure_id = p_org_hierarchy_id;
SELECT meaning
INTO l_property
FROM mfg_lookups
WHERE lookup_type = 'INV_MGD_HIER_PROPERTY_TYPE'
AND lookup_code = 2;
SELECT name
INTO l_hierarchy_name
FROM per_organization_structures
WHERE organization_structure_id = p_org_hierarchy_id;
SELECT meaning
INTO l_property
FROM mfg_lookups
WHERE lookup_type = 'INV_MGD_HIER_PROPERTY_TYPE'
AND lookup_code = 3;
SELECT
glstb.period_set_name
, glstb.ACCOUNTED_PERIOD_TYPE
, orgu.date_from
INTO
l_period_set_name
, l_sob_period_type
, l_org_from_date
FROM
gl_sets_of_books glstb
, org_organization_definitions orgdef
, hr_organization_units orgu
WHERE orgdef.organization_id = p_org_hierarchy_origin
AND glstb.set_of_books_id = orgdef.set_of_books_id
AND orgu.organization_id = orgdef.organization_id
AND orgu.business_group_id = orgdef.business_group_id ;
SELECT
glstb.period_set_name
, glstb.ACCOUNTED_PERIOD_TYPE
, HOU.date_from from_date
INTO
l_period_set_name
, l_sob_period_type
, l_org_from_date
FROM
MTL_PARAMETERS MP
, hr_organization_units HOU
, HR_ORGANIZATION_INFORMATION HOI
, gl_sets_of_books glstb
WHERE HOU.ORGANIZATION_ID = p_org_hierarchy_origin
AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND UPPER( HOI.ORG_INFORMATION_CONTEXT || '') = 'ACCOUNTING INFORMATION'
AND TO_NUMBER(HOI.ORG_INFORMATION1) = glstb.SET_OF_BOOKS_ID ;
/* make insert into wpb using starting time and end time */
/* for discrete */
INSERT INTO WIP_PERIOD_BALANCES
(ACCT_PERIOD_ID, WIP_ENTITY_ID,
REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN,
ORGANIZATION_ID, CLASS_TYPE,
TL_RESOURCE_IN, TL_OVERHEAD_IN,
TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
TL_MATERIAL_OUT, TL_MATERIAL_OVERHEAD_OUT, TL_RESOURCE_OUT,
TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
PL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_VAR, PL_MATERIAL_OVERHEAD_VAR,
PL_RESOURCE_VAR, PL_OUTSIDE_PROCESSING_VAR,
PL_OVERHEAD_VAR, TL_MATERIAL_VAR, TL_MATERIAL_OVERHEAD_VAR,
TL_RESOURCE_VAR, TL_OUTSIDE_PROCESSING_VAR, TL_OVERHEAD_VAR)
SELECT
l_new_acct_period_id,
WDJ.WIP_ENTITY_ID,
NULL, SYSDATE,
l_user_id, SYSDATE,
l_user_id, l_login_id,
p_org_hierarchy_origin, WAC.CLASS_TYPE,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ACCOUNTING_CLASSES WAC
WHERE WDJ.STATUS_TYPE IN (3, 4, 5, 6, 7, 14, 15)
AND WDJ.DATE_RELEASED is not NULL
AND WDJ.ORGANIZATION_ID = p_org_hierarchy_origin
AND WAC.CLASS_CODE = WDJ.CLASS_CODE
AND WAC.ORGANIZATION_ID = p_org_hierarchy_origin
AND ((WDJ.CREATION_DATE between l_start_time and l_end_time)
or (WDJ.DATE_RELEASED between l_start_time and l_end_time)
or (WDJ.LAST_UPDATE_DATE between l_start_time and l_end_time)
)
AND not exists
(select 'X' from wip_period_balances wpb
where l_new_acct_period_id = wpb.acct_period_id
and wpb.organization_id = p_org_hierarchy_origin
and wdj.wip_entity_id = wpb.wip_entity_id);
INSERT INTO WIP_PERIOD_BALANCES
(ACCT_PERIOD_ID, WIP_ENTITY_ID,
REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN,
ORGANIZATION_ID, CLASS_TYPE,
TL_RESOURCE_IN, TL_OVERHEAD_IN,
TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
TL_MATERIAL_OUT, TL_MATERIAL_OVERHEAD_OUT, TL_RESOURCE_OUT,
TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
PL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_VAR, PL_MATERIAL_OVERHEAD_VAR,
PL_RESOURCE_VAR, PL_OUTSIDE_PROCESSING_VAR,
PL_OVERHEAD_VAR, TL_MATERIAL_VAR, TL_MATERIAL_OVERHEAD_VAR,
TL_RESOURCE_VAR, TL_OUTSIDE_PROCESSING_VAR, TL_OVERHEAD_VAR)
SELECT
l_new_acct_period_id,
WRS.WIP_ENTITY_ID,
WRS.REPETITIVE_SCHEDULE_ID, SYSDATE,
l_user_id, SYSDATE,
l_user_id, l_login_id,
p_org_hierarchy_origin, 2,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
FROM WIP_REPETITIVE_SCHEDULES WRS
WHERE WRS.STATUS_TYPE IN (3, 4, 6)
AND WRS.ORGANIZATION_ID = p_org_hierarchy_origin
AND ((WRS.CREATION_DATE between l_start_time and l_end_time)
or (WRS.DATE_RELEASED between l_start_time and l_end_time))
AND not exists
(select 'X' from wip_period_balances wpb
where l_new_acct_period_id = wpb.acct_period_id
and wpb.organization_id = p_org_hierarchy_origin
and wrs.wip_entity_id = wpb.wip_entity_id
and wrs.repetitive_schedule_id = wpb.repetitive_schedule_id);
SELECT max(start_date)
INTO l_max_period_start_date
FROM GL_PERIODS
WHERE ADJUSTMENT_PERIOD_FLAG = 'N'
AND period_type = l_sob_period_type
AND PERIOD_SET_NAME = l_period_set_name;
/* make insert into wpb using starting time and end time */
/* for discrete */
INSERT INTO WIP_PERIOD_BALANCES
(ACCT_PERIOD_ID, WIP_ENTITY_ID,
REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN,
ORGANIZATION_ID, CLASS_TYPE,
TL_RESOURCE_IN, TL_OVERHEAD_IN,
TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
TL_MATERIAL_OUT, TL_MATERIAL_OVERHEAD_OUT, TL_RESOURCE_OUT,
TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
PL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_VAR, PL_MATERIAL_OVERHEAD_VAR,
PL_RESOURCE_VAR, PL_OUTSIDE_PROCESSING_VAR,
PL_OVERHEAD_VAR, TL_MATERIAL_VAR, TL_MATERIAL_OVERHEAD_VAR,
TL_RESOURCE_VAR, TL_OUTSIDE_PROCESSING_VAR, TL_OVERHEAD_VAR)
SELECT
l_new_acct_period_id,
WDJ.WIP_ENTITY_ID,
NULL, SYSDATE,
l_user_id, SYSDATE,
l_user_id, l_login_id,
l_orgid, WAC.CLASS_TYPE,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ACCOUNTING_CLASSES WAC
WHERE WDJ.STATUS_TYPE IN (3, 4, 5, 6, 7, 14, 15)
AND WDJ.DATE_RELEASED is not NULL
AND WDJ.ORGANIZATION_ID = l_orgid
AND WAC.CLASS_CODE = WDJ.CLASS_CODE
AND WAC.ORGANIZATION_ID = l_orgid
AND ((WDJ.CREATION_DATE between l_start_time and l_end_time)
or (WDJ.DATE_RELEASED between l_start_time and l_end_time))
AND not exists
(select 'X' from wip_period_balances wpb
where l_new_acct_period_id = wpb.acct_period_id
and wpb.organization_id = l_orgid
and wdj.wip_entity_id = wpb.wip_entity_id);
INSERT INTO WIP_PERIOD_BALANCES
(ACCT_PERIOD_ID, WIP_ENTITY_ID,
REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN,
ORGANIZATION_ID, CLASS_TYPE,
TL_RESOURCE_IN, TL_OVERHEAD_IN,
TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
TL_MATERIAL_OUT, TL_MATERIAL_OVERHEAD_OUT, TL_RESOURCE_OUT,
TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
PL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_VAR, PL_MATERIAL_OVERHEAD_VAR,
PL_RESOURCE_VAR, PL_OUTSIDE_PROCESSING_VAR,
PL_OVERHEAD_VAR, TL_MATERIAL_VAR, TL_MATERIAL_OVERHEAD_VAR,
TL_RESOURCE_VAR, TL_OUTSIDE_PROCESSING_VAR, TL_OVERHEAD_VAR)
SELECT
l_new_acct_period_id,
WRS.WIP_ENTITY_ID,
WRS.REPETITIVE_SCHEDULE_ID, SYSDATE,
l_user_id, SYSDATE,
l_user_id, l_login_id,
l_orgid, 2,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
FROM WIP_REPETITIVE_SCHEDULES WRS
WHERE WRS.STATUS_TYPE IN (3, 4, 6)
AND WRS.ORGANIZATION_ID = l_orgid
AND ((WRS.CREATION_DATE between l_start_time and l_end_time)
or (WRS.DATE_RELEASED between l_start_time and l_end_time))
AND not exists
(select 'X' from wip_period_balances wpb
where l_new_acct_period_id = wpb.acct_period_id
and wpb.organization_id = l_orgid
and wrs.wip_entity_id = wpb.wip_entity_id
and wrs.repetitive_schedule_id = wpb.repetitive_schedule_id);
SELECT TO_NUMBER(HOI.org_information2)
INTO l_legal_entity
FROM hr_organization_information HOI
WHERE HOI.org_information_context = 'Accounting Information'
AND HOI.organization_id = l_orgid;