[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
mp.organization_code,
UPPER(nvl(mp.process_enabled_flag,'N')) process_flag,
oacp.period_name,
oacp.period_close_date,
oacp.schedule_close_date,
oacp.open_flag
FROM
mtl_parameters mp,
org_acct_periods oacp
WHERE
mp.organization_id = p_organization_id AND
mp.organization_id = oacp.organization_id AND
oacp.acct_period_id = p_acct_period_id
;
SELECT org_information2 INTO l_legal_entity_id
FROM hr_organization_information
WHERE organization_id = p_organization_id
AND org_information_context = 'Accounting Information';
/* if there are already some prelim rows from some prior prelim close -- delete them*/
DELETE FROM gmf_period_balances
WHERE acct_period_id = p_closing_acct_period_id
AND organization_id = p_organization_id;
Log_Msg(C_LOG_FILE,' No rows found in gmf_period_balances to delete.');
Log_Msg(C_LOG_FILE,' Deleted '||SQL%ROWCOUNT||' rows from gmf_period_balances.');
/* All done, update period status to Closed */
UPDATE org_acct_periods
SET
open_flag = 'N',
summarized_flag = 'Y'
WHERE
organization_id = p_organization_id AND
acct_period_id = p_closing_acct_period_id;
SELECT
moq.organization_id,
moq.cost_group_id,
moq.subinventory_code,
moq.inventory_item_id,
moq.locator_id,
moq.lot_number,
SUM(moq.primary_transaction_quantity) pri_qty, /*bug 5463187*/
SUM(NVL(moq.secondary_transaction_quantity,0)) sec_qty
FROM
mtl_onhand_quantities_detail moq
WHERE
moq.organization_id = p_organization_id AND
moq.is_consigned = 2 /* moq does not have sec qty */
GROUP BY
moq.organization_id,
moq.cost_group_id,
moq.subinventory_code,
moq.inventory_item_id,
moq.locator_id,
moq.lot_number
;
SELECT /* lot controlled items */
mmt.organization_id,
mmt.cost_group_id,
mmt.subinventory_code,
mmt.inventory_item_id,
mmt.locator_id,
mtln.lot_number,
SUM(mtln.primary_quantity) pri_qty,
SUM(NVL(mtln.secondary_transaction_quantity,0)) sec_qty
FROM
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt,
MTL_SECONDARY_INVENTORIES sinv /*B8404849*/
WHERE
mmt.transaction_id = mtln.transaction_id AND
mmt.organization_id = p_organization_id AND
mmt.transaction_date > p_rollback_to_date AND
/* Ignore consigned */
mmt.organization_id = NVL(mmt.owning_organization_id, mmt.organization_id) AND
NVL(mmt.owning_tp_type,2) = 2 AND
/* Ignore Logical Txns */
NVL(mmt.logical_transaction,-1) <> 1
/*B8404849 - Ignore Non Quantity tracked subinventory txns - START*/
AND sinv.organization_id = mmt.organization_id
AND sinv.secondary_inventory_name = mmt.subinventory_code
AND nvl(sinv.quantity_tracked,1) = 1
/*B8404849 - Ignore Non Quantity tracked subinventory txns - End*/
/* TBD: do we need to exclude any specific txns in process orgs */
GROUP BY
mmt.organization_id,
mmt.cost_group_id,
mmt.subinventory_code,
mmt.inventory_item_id,
mmt.locator_id,
mtln.lot_number
UNION ALL
SELECT /*+ INDEX(mmt mtl_material_transactions_n5) */ /* non lot controlled items */
mmt.organization_id,
mmt.cost_group_id,
mmt.subinventory_code,
mmt.inventory_item_id,
mmt.locator_id,
null lot_number,
SUM(mmt.primary_quantity) pri_qty,
SUM(NVL(mmt.secondary_transaction_quantity,0)) sec_qty
FROM
mtl_system_items_b msi,
mtl_material_transactions mmt,
MTL_SECONDARY_INVENTORIES sinv /*B8404849*/
WHERE
mmt.inventory_item_id = msi.inventory_item_id AND
mmt.organization_id = msi.organization_id AND
msi.lot_control_code = 1 AND /* no lot control */
mmt.organization_id = p_organization_id AND
mmt.transaction_date > p_rollback_to_date AND
/* Ignore consigned */
mmt.organization_id = NVL(mmt.owning_organization_id, mmt.organization_id) AND
NVL(mmt.owning_tp_type,2) = 2 AND
/* Ignore Logical Txns */
NVL(mmt.logical_transaction,-1) <> 1
/*B8404849 - Ignore Non Quantity tracked subinventory txns - START*/
AND sinv.organization_id = mmt.organization_id
AND sinv.secondary_inventory_name = mmt.subinventory_code
AND nvl(sinv.quantity_tracked,1) = 1
/*B8404849 - Ignore Non Quantity tracked subinventory txns - End*/
/* TBD: do we need to exclude any specific txns in process orgs */
GROUP BY
mmt.organization_id,
mmt.cost_group_id,
mmt.subinventory_code,
mmt.inventory_item_id,
mmt.locator_id
;
SELECT
pbt.organization_id,
pbt.cost_group_id,
pbt.subinventory_code,
pbt.inventory_item_id,
pbt.locator_id,
pbt.lot_number,
SUM(pbt.primary_quantity) pri_qty,
SUM(NVL(pbt.secondary_quantity,0)) sec_qty
FROM
gmf_period_balances_gt pbt
WHERE
organization_id = p_organization_id /* Bug#5652481 ANTHIYAG 09-Nov-2006 */
AND acct_period_id = p_acct_period_id /* Bug#5652481 ANTHIYAG 09-Nov-2006 */
GROUP BY
pbt.organization_id,
pbt.cost_group_id,
pbt.subinventory_code,
pbt.inventory_item_id,
pbt.locator_id,
pbt.lot_number
HAVING
( SUM(pbt.primary_quantity) <> 0 OR
SUM(NVL(pbt.secondary_quantity,0)) <> 0 )
;
/* SELECT SYSDATE INTO l_rollback_to_date FROM DUAL; */
fnd_log.string(fnd_log.level_statement, 'l_log_module','Inserting Onhand Balances');
/* insert into balances table */
INSERT INTO gmf_period_balances_gt (
source_type_id,
acct_period_id,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
lot_number,
locator_id,
primary_quantity,
secondary_quantity,
intransit_primary_quantity,
intransit_secondary_quantity,
accounted_value,
intransit_accounted_value
)
VALUES
(
1, /* onhand */
p_closing_acct_period_id,
p_organization_id,
r_onhand.cost_group_id,
r_onhand.subinventory_code,
r_onhand.inventory_item_id,
r_onhand.lot_number,
r_onhand.locator_id,
r_onhand.pri_qty,
r_onhand.sec_qty,
0, /* intransit pri qty */
0, /* intransit sec qty */
0, /* accounted_value */
0 /* intransit accounted value */
);
INSERT INTO gmf_period_balances_gt (
source_type_id,
acct_period_id,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
lot_number,
locator_id,
primary_quantity,
secondary_quantity,
intransit_primary_quantity,
intransit_secondary_quantity,
accounted_value,
intransit_accounted_value
)
VALUES
(
2, /* txns */
p_closing_acct_period_id,
p_organization_id,
r_txns.cost_group_id,
r_txns.subinventory_code,
r_txns.inventory_item_id,
r_txns.lot_number,
r_txns.locator_id,
-1 * r_txns.pri_qty,
-1 * r_txns.sec_qty,
0, /* intransit pri qty */
0, /* intransit sec qty */
0, /* accounted_value */
0 /* intransit accounted value */
);
/* Insert/Update balances table */
IF( fnd_log.level_statement >= fnd_log.g_current_runtime_level )
THEN
fnd_log.string(fnd_log.level_statement, 'l_log_module','Inserting into balances');
INSERT INTO gmf_period_balances (
period_balance_id,
acct_period_id,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
lot_number,
locator_id,
primary_quantity,
secondary_quantity,
intransit_primary_quantity,
intransit_secondary_quantity,
accounted_value,
intransit_accounted_value,
costed_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
period_close_status
)
VALUES
(
gmf_period_balances_s.nextval,
p_closing_acct_period_id,
p_organization_id,
r_bal_tmp.cost_group_id,
r_bal_tmp.subinventory_code,
r_bal_tmp.inventory_item_id,
r_bal_tmp.lot_number,
r_bal_tmp.locator_id,
r_bal_tmp.pri_qty,
r_bal_tmp.sec_qty,
0, /* intransit pri qty */
0, /* intransit sec qty */
0, /* accounted value */
0, /* intransit accounted value */
'N', /* costed flag */
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
decode(p_final_close,1,'F','P')
);
UPDATE org_acct_periods
SET
open_flag = 'Y',
summarized_flag = NULL
WHERE
organization_id = p_organization_id AND
p_acct_period_id = p_acct_period_id;
SELECT organization_code,
organization_id
FROM gmf_process_organizations_gt
ORDER BY organization_code;
SELECT acct_period_id, period_start_date start_date, schedule_close_date close_date, period_name
FROM ORG_ACCT_PERIODS
WHERE organization_id = p_org_id
and period_set_name = p_period_Set_name
and period_year = p_year
and period_num = p_period
ORDER by schedule_close_date;
Log_Msg(C_LOG_FILE, 'The period is selected for final close');
Log_Msg(C_LOG_FILE, 'The period is selected for preliminary close');
SELECT user_name INTO l_user
FROM fnd_user
WHERE user_id = l_user_id;
select b.period_set_name
into l_period_Set_name
from gmf_fiscal_policies a,
gl_ledgers b
where a.legal_entity_id = p_le_id
and b.ledger_id = a.ledger_id;
SELECT glp.end_date
INTO l_period_end_date
FROM GL_PERIODS GLP,GL_PERIOD_STATUSES STS, GL_SETS_OF_BOOKS SOB,GMF_FISCAL_POLICIES GFP
where sob.set_of_books_id = GFP.LEDGER_ID
AND GFP.LEGAL_ENTITY_ID = p_le_id
AND glp.period_set_name = sob.period_set_name
AND glp.period_type = sob.accounted_period_type
AND glp.period_year = p_fiscal_year
AND glp.period_num = sts.period_num
AND glp.period_year = sts.period_year
AND sts.set_of_books_id = sob.set_of_books_id
AND sts.application_id = 101
AND sts.period_num = p_fiscal_period;
UPDATE org_acct_periods
SET
open_flag = 'P',
period_close_date = trunc(sysdate),
last_update_date = trunc(sysdate),
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE
acct_period_id = l_acct_period_id AND
-- program level check to make sure that
-- the period is only closed once
open_flag = 'Y' AND
organization_id = l_organization_id AND
period_set_name = l_period_set_name;
/* if there are already some prelim rows from some prior prelim close -- delete them*/
DELETE FROM gmf_period_balances
WHERE acct_period_id = l_acct_period_id
AND organization_id = l_organization_id;
Log_Msg(C_LOG_FILE,' No rows found in gmf_period_balances to delete.');
Log_Msg(C_LOG_FILE,' Deleted '||SQL%ROWCOUNT||' rows from gmf_period_balances.');
/* All done, update period status to Closed */
IF l_final_close = 1 THEN
UPDATE org_acct_periods
SET
open_flag = 'N',
summarized_flag = 'Y'
WHERE
organization_id = l_organization_id AND
acct_period_id = l_acct_period_id;