[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT legal_entity
INTO l_legal_entity
FROM cst_acct_info_v
WHERE organization_id = p_org_id;
SELECT COUNT(*)
INTO l_tcount
FROM mtl_material_transactions_temp
WHERE organization_id = p_org_id
AND transaction_date <= l_sched_close_date
AND NVL(transaction_status,0) <> 2; -- 2 indicates a save-only status
SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
COUNT(*)
INTO l_tcount
FROM mtl_material_transactions MMT
WHERE organization_id = p_org_id
AND transaction_date <= l_sched_close_date
AND costed_flag IN('N','E');-- bug 13054482 Changed the not null condition, as it causes a index full scan
SELECT COUNT(*)
INTO l_tcount
FROM wip_cost_txn_interface
WHERE organization_id = p_org_id
AND transaction_date <= l_sched_close_date;
SELECT COUNT(*)
INTO l_tcount
FROM wsm_split_merge_txn_interface
WHERE organization_id = p_org_id
AND process_status <> wip_constants.completed
AND transaction_date <= l_sched_close_date;
SELECT COUNT(*)
INTO l_tcount
FROM wsm_lot_move_txn_interface
WHERE organization_id = p_org_id
AND status <> wip_constants.completed
AND transaction_date <= l_sched_close_date;
SELECT COUNT(*)
INTO l_tcount
FROM wsm_lot_split_merges_interface
WHERE organization_id = p_org_id
AND process_status <> wip_constants.completed
AND transaction_date <= l_sched_close_date;
SELECT period_start_date
INTO l_le_closing_fm_date
FROM org_acct_periods
WHERE acct_period_id = p_closing_period
AND organization_id = p_org_id;
SELECT COUNT(*)
INTO l_tcount
FROM rcv_transactions_interface
WHERE to_organization_id = p_org_id
AND transaction_date <= l_sched_close_date
AND destination_type_code in ('INVENTORY','SHOP FLOOR');
SELECT COUNT(*)
INTO l_tcount
FROM mtl_transactions_interface
WHERE organization_id = p_org_id
AND transaction_date <= l_sched_close_date
AND process_flag <> 9;
SELECT COUNT(*)
INTO l_tcount
FROM wip_move_txn_interface
WHERE organization_id = p_org_id
AND transaction_date <= l_sched_close_date;
SELECT NVL(eam_enabled_flag, 'N'), NVL(lcm_enabled_flag, 'N') /* Support for LCM */
INTO l_eam_enabled, l_lcm_enabled
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT count(*)
INTO l_tcount
FROM wip_discrete_jobs WDJ, wip_entities WE
WHERE WDJ.organization_id = p_org_id
AND WDJ.scheduled_completion_date <= p_sched_close_date
AND WDJ.status_type = 3 -- Released
AND WDJ.wip_entity_id = WE.wip_entity_id
AND WDJ.organization_id = WE.organization_id
AND WE.entity_type = 6; -- Maintenance Work Order
SELECT COUNT(*)
INTO l_tcount
FROM cst_lc_adj_interface
WHERE organization_id = p_org_id
AND transaction_date <= l_sched_close_date;
SELECT NVL(MAX(schedule_close_date), sysdate),
count(*)
FROM org_acct_periods
WHERE organization_id = p_org_id;
SELECT start_date
FROM gl_periods
WHERE end_date < p_period_end_date
AND end_date >= x_last_scheduled_close_date
AND (period_name, period_year) not in
(select period_name, period_year
from org_acct_periods
where organization_id = p_org_id)
AND period_type = p_acct_period_type
AND period_set_name = p_org_period_set_name
AND adjustment_period_flag = 'N';
SELECT org_acct_periods_s.nextval
FROM sys.dual;
SELECT period_start_date
FROM org_acct_periods
WHERE organization_id = p_org_id
AND period_name = p_open_period_name
AND period_year = p_open_period_year
AND period_num = p_open_period_num
AND acct_period_id <> x_new_acct_period_id;
SELECT NULL
FROM org_acct_periods
WHERE organization_id = p_org_id
AND period_year = p_open_period_year
AND period_name = p_open_period_name
AND period_num = p_open_period_num;
INSERT INTO org_acct_periods
(acct_period_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
period_set_name,
period_year,
period_num,
period_name,
description,
period_start_date,
schedule_close_date,
open_flag,
last_update_login)
SELECT
x_new_acct_period_id, -- acct_period_id
p_org_id, -- organization_id
SYSDATE, -- last_update_date
p_user_id, -- last_updated_by
SYSDATE, -- creation_date
p_user_id, -- created_by
GLP.period_set_name, -- period_set_name
GLP.period_year, -- period_year
GLP.period_num, -- period_num
GLP.period_name, -- period_name
GLP.description, -- description
-- period_start_date
decode(l_first_period,
1, GLP.start_date,
x_last_scheduled_close_date+1),
GLP.end_date, -- schedule_close_date
'Y', -- open_flag
-1 -- last_update_login
FROM gl_periods GLP
WHERE GLP.period_set_name = p_org_period_set_name
AND GLP.period_name = p_open_period_name
AND GLP.period_type = p_acct_period_type
AND GLP.adjustment_period_flag = 'N'
AND (GLP.period_name, GLP.period_year)
NOT IN
(SELECT period_name, period_year
FROM org_acct_periods
WHERE organization_id = p_org_id)
AND NOT EXISTS
(SELECT period_start_date
FROM org_acct_periods
WHERE organization_id = p_org_id
AND period_year = p_open_period_year
AND period_name = p_open_period_name
AND period_num = p_open_period_num);
SELECT NVL(MAX(schedule_close_date), SYSDATE)
INTO x_last_scheduled_close_date
FROM org_acct_periods
WHERE organization_id = p_org_id;
SELECT acct_period_id
FROM org_acct_periods
WHERE organization_id = p_org_id
AND schedule_close_date = (SELECT MIN(schedule_close_date)
FROM org_acct_periods
WHERE organization_id = p_org_id
AND (open_flag = 'Y' or open_flag = 'P'));
SELECT MIN(acct_period_id)
FROM org_acct_periods
WHERE organization_id = p_org_id
AND acct_period_id > p_closing_acct_period_id;
SELECT acct_period_id
FROM org_acct_periods
WHERE organization_id = p_org_id
AND acct_period_id = p_closing_acct_period_id
AND period_close_date IS NOT NULL
AND open_flag = 'P';
SELECT operating_unit
INTO l_operating_unit
FROM cst_acct_info_v
WHERE organization_id = p_org_id;
UPDATE org_acct_periods
SET
open_flag = 'P',
period_close_date = trunc(sysdate),
last_update_date = sysdate, --trunc(sysdate),
last_updated_by = p_user_id,
last_update_login = p_login_id
WHERE acct_period_id = p_closing_acct_period_id
AND organization_id = p_org_id
-- program level check to make sure that
-- the period is only closed once
AND open_flag = 'Y';
SELECT period_start_date, schedule_close_date
INTO l_period_start_date, l_sched_close_date
FROM org_acct_periods
WHERE acct_period_id = p_closing_acct_period_id
AND organization_id = p_org_id;
SELECT legal_entity
INTO l_legal_entity
FROM cst_acct_info_v
WHERE organization_id = p_org_id;
SELECT COUNT(*)
INTO l_count
FROM mtl_material_transactions_temp
WHERE organization_id = p_org_id
AND transaction_date < l_sched_close_date
AND NVL(transaction_status,0) <> 2
AND rownum = 1; -- transaction_status = 2 indicates a save-only status
SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
COUNT(*)
INTO l_count
FROM mtl_material_transactions MMT
WHERE organization_id = p_org_id
AND transaction_date < l_sched_close_date
AND costed_flag IN('N','E')-- bug 13054482 Changed the not null condition, as it causes a index full scan
AND rownum = 1;
SELECT COUNT(*)
INTO l_count
FROM wip_cost_txn_interface
WHERE organization_id = p_org_id
AND transaction_date < l_sched_close_date
AND rownum = 1;
SELECT COUNT(*)
INTO l_count
FROM wsm_split_merge_transactions
WHERE organization_id = p_org_id
AND costed <> wip_constants.completed
AND transaction_date < l_sched_close_date
AND rownum = 1;
SELECT COUNT(*)
INTO l_count
FROM wsm_split_merge_txn_interface
WHERE organization_id = p_org_id
AND process_status <> wip_constants.completed
AND transaction_date < l_sched_close_date
AND rownum = 1;
UPDATE org_acct_periods
SET summarized_flag = 'N',
open_flag = 'N'
WHERE organization_id = p_org_id
AND acct_period_id = p_closing_acct_period_id;
SELECT ML.lookup_code
INTO l_rep_type
FROM mfg_lookups ML,
mtl_parameters MP
WHERE MP.organization_id = p_org_id
AND ML.lookup_type = 'CST_PER_CLOSE_REP_TYPE'
AND ML.lookup_code =
DECODE(MP.primary_cost_method,
1,DECODE(
MP.wms_enabled_flag,
'Y',1,
DECODE(
MP.cost_group_accounting,
1,DECODE(
MP.project_reference_enabled,
1,1,
2
),
2
)
),
1
);
SELECT GL.currency_code
INTO l_currency_code
FROM hr_organization_information HOI,
gl_ledgers GL
WHERE HOI.organization_id = p_org_id
AND HOI.org_information_context = 'Accounting Information'
AND TO_NUMBER(HOI.org_information1) = GL.ledger_id;
UPDATE org_acct_periods
SET open_flag = 'N'
WHERE organization_id = p_org_id
AND acct_period_id = p_closing_acct_period_id;
PROCEDURE Update_EndDate(
p_api_version IN NUMBER,
p_org_id IN NUMBER,
p_new_end_date IN DATE,
p_changed_acct_period_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
x_period_order OUT NOCOPY BOOLEAN,
x_update_failed OUT NOCOPY BOOLEAN,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_next_periods_enddate DATE;
SELECT NVL(MAX(schedule_close_date), p_new_end_date - 1)
FROM org_acct_periods
WHERE organization_id = p_org_id
AND acct_period_id < p_changed_acct_period_id;
SELECT NVL(MIN(schedule_close_date), p_new_end_date + 1)
FROM org_acct_periods
WHERE organization_id = p_org_id
AND acct_period_id > p_changed_acct_period_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_EndDate';
SAVEPOINT Update_EndDate_PUB;
UPDATE org_acct_periods
SET schedule_close_date = p_new_end_date,
last_update_date = sysdate, --trunc(SYSDATE),
last_updated_by = p_user_id,
last_update_login = p_login_id
WHERE organization_id = p_org_id
AND acct_period_id = p_changed_acct_period_id;
UPDATE org_acct_periods
SET period_start_date = p_new_end_date + 1,
last_update_date = sysdate, --trunc(SYSDATE),
last_updated_by = p_user_id,
last_update_login = p_login_id
WHERE organization_id = p_org_id
AND acct_period_id =
(SELECT MIN(acct_period_id)
FROM org_acct_periods
WHERE acct_period_id > p_changed_acct_period_id
AND organization_id = p_org_id);
x_update_failed := FALSE;
x_update_failed := TRUE;
ROLLBACK TO Update_EndDate_PUB;
ROLLBACK TO Update_EndDate_PUB;
x_update_failed := TRUE;
END Update_EndDate;
DELETE FROM org_acct_periods
WHERE organization_id = p_org_id
AND acct_period_id = x_acct_period_id;
SELECT legal_entity
INTO l_legal_entity
FROM cst_acct_info_v
WHERE organization_id = p_org_id;
SELECT period_start_date, schedule_close_date
INTO l_le_period_start_date, l_le_to_date
FROM org_acct_periods
WHERE organization_id = p_org_id
AND acct_period_id = p_period_id;
SELECT MAX(acct_period_id)
INTO l_prior_period_id
FROM org_acct_periods
WHERE organization_id = p_org_id
AND acct_period_id < p_period_id;
SELECT count(*)
INTO l_resummarize
FROM org_acct_periods
WHERE organization_id = p_org_id
AND acct_period_id = p_period_id
AND summarized_flag = 'N'
AND EXISTS
(SELECT 'Data exists in CPCS'
FROM cst_period_close_summary
WHERE organization_id = p_org_id
AND acct_period_id = p_period_id);
DELETE cst_period_close_summary
WHERE organization_id = p_org_id
AND acct_period_id >= p_period_id;
/* Updating org_acct_periods in case the customer has not updated summarized_flag
for all succeeding periods */
l_stmt_num := 37;
UPDATE org_acct_periods
SET summarized_flag = 'N'
WHERE organization_id = p_org_id
AND acct_period_id >= p_period_id
AND summarized_flag = 'Y';
SELECT count(*)
INTO l_prev_summary
FROM org_acct_periods
WHERE organization_id = p_org_id
AND acct_period_id = l_prior_period_id
AND summarized_flag = 'Y';
SELECT count(*)
INTO l_cpcs_count
FROM cst_period_close_summary
WHERE organization_id = p_org_id
AND rownum = 1;
SELECT category_set_id
INTO l_category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 5; -- Costing functional area
SELECT NVL(OAP1.schedule_close_date+1-(1/(24*3600)),
OAP2.period_start_date-(1/(24*3600)))
INTO l_le_prior_end_date
FROM org_acct_periods OAP1,
org_acct_periods OAP2
WHERE OAP1.organization_id(+) = OAP2.organization_id
AND OAP1.acct_period_id(+) = l_prior_period_id
AND OAP2.organization_id = p_org_id
AND OAP2.acct_period_id = p_period_id;
INSERT
INTO cst_per_close_summary_temp(
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value,
rollback_value,
rollback_qty,
rollback_onhand_value,
rollback_intransit_value)
SELECT CIQT.cost_group_id,
CIQT.subinventory_code,
CIQT.inventory_item_id,
0 accounted_value,
SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) rollback_value,
SUM(NVL(CIQT.rollback_qty,0)),
SUM(DECODE(CIQT.qty_source,
3,NVL(CIQT.rollback_qty,0),
4,NVL(CIQT.rollback_qty,0),
5,NVL(CIQT.rollback_qty,0),
0))*NVL(CICT.item_cost,0) rollback_onhand_value,
SUM(DECODE(CIQT.qty_source,
6,NVL(CIQT.rollback_qty,0),
7,NVL(CIQT.rollback_qty,0),
8,NVL(CIQT.rollback_qty,0),
0))*NVL(CICT.item_cost,0) rollback_intransit_value
FROM cst_inv_qty_temp CIQT,
cst_inv_cost_temp CICT
WHERE CIQT.organization_id = p_org_id
AND CIQT.organization_id = CICT.organization_id
AND NVL(CIQT.cost_group_id,-1) =
NVL(CICT.cost_group_id,NVL(CIQT.cost_group_id,-1))
AND CIQT.inventory_item_id = CICT.inventory_item_id
AND CICT.cost_source = 2 -- PAST
GROUP BY
CIQT.organization_id,
CIQT.cost_group_id,
CIQT.subinventory_code,
CIQT.inventory_item_id,
CICT.item_cost
/* Bug 14036099*/
HAVING SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) <> 0 OR
SUM(NVL(CIQT.rollback_qty,0)) <> 0 OR
l_zero_val_hook = 0;
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' rows to CPCST for initialization'
);
DELETE CST_ITEM_LIST_TEMP;
DELETE CST_CG_LIST_TEMP;
DELETE CST_SUB_LIST_TEMP;
DELETE CST_INV_QTY_TEMP;
DELETE CST_INV_COST_TEMP;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value)
SELECT 1, -- PRIOR ONHAND
p_org_id organization_id,
CPCST.cost_group_id,
CPCST.subinventory_code,
CPCST.inventory_item_id,
CPCST.rollback_onhand_value
FROM cst_per_close_summary_temp CPCST
WHERE CPCST.rollback_onhand_value <> 0
UNION ALL
SELECT 2, -- PRIOR INTRANSIT
p_org_id organization_id,
CPCST.cost_group_id,
CPCST.subinventory_code,
CPCST.inventory_item_id,
CPCST.rollback_intransit_value
FROM cst_per_close_summary_temp CPCST
WHERE CPCST.rollback_intransit_value <> 0
UNION ALL
SELECT 21, -- CUMULATIVE ONHAND
p_org_id organization_id,
CPCST.cost_group_id,
CPCST.subinventory_code,
CPCST.inventory_item_id,
CPCST.rollback_onhand_value
FROM cst_per_close_summary_temp CPCST
WHERE CPCST.rollback_onhand_value <> 0
UNION ALL
SELECT 22, -- CUMULATIVE INTRANSIT
p_org_id organization_id,
CPCST.cost_group_id,
CPCST.subinventory_code,
CPCST.inventory_item_id,
CPCST.rollback_intransit_value
FROM cst_per_close_summary_temp CPCST
WHERE CPCST.rollback_intransit_value <> 0;
DELETE cst_per_close_summary_temp;
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' rows to CIQT as baseline from CPCST'
);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value)
SELECT
1, -- PRIOR ONHAND
p_org_id organization_id,
CPCS.cost_group_id,
CPCS.subinventory_code,
CPCS.inventory_item_id,
CPCS.rollback_onhand_value
FROM
cst_period_close_summary CPCS
WHERE CPCS.organization_id = p_org_id
AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
UNION ALL
SELECT
2, -- PRIOR INTRANSIT
p_org_id organization_id,
CPCS.cost_group_id,
CPCS.subinventory_code,
CPCS.inventory_item_id,
CPCS.rollback_intransit_value
FROM
cst_period_close_summary CPCS
WHERE CPCS.organization_id = p_org_id
AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
UNION ALL
SELECT
21, -- CUMULATIVE ONHAND
p_org_id organization_id,
CPCS.cost_group_id,
CPCS.subinventory_code,
CPCS.inventory_item_id,
CPCS.cumulative_onhand_mta
FROM
cst_period_close_summary CPCS
WHERE CPCS.organization_id = p_org_id
AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
UNION ALL
SELECT
22, -- CUMULATIVE INTRANSIT
p_org_id organization_id,
CPCS.cost_group_id,
CPCS.subinventory_code,
CPCS.inventory_item_id,
CPCS.cumulative_intransit_mta
FROM
cst_period_close_summary CPCS
WHERE CPCS.organization_id = p_org_id
AND CPCS.acct_period_id = NVL(l_prior_period_id,-1); */
INSERT ALL
INTO cst_inv_qty_temp (
qty_source,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value)
VALUES (1, -- PRIOR ONHAND
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
rollback_onhand_value)
INTO cst_inv_qty_temp (
qty_source,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value)
VALUES (2, -- PRIOR INTRANSIT
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
rollback_intransit_value)
INTO cst_inv_qty_temp (
qty_source,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value)
VALUES (21, -- CUMULATIVE ONHAND
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
cumulative_onhand_mta)
INTO cst_inv_qty_temp (
qty_source,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value)
VALUES (22, -- CUMULATIVE INTRANSIT
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
cumulative_intransit_mta)
SELECT p_org_id organization_id,
CPCS.cost_group_id,
CPCS.subinventory_code,
CPCS.inventory_item_id,
CPCS.rollback_onhand_value,
CPCS.rollback_intransit_value,
CPCS.cumulative_onhand_mta,
CPCS.cumulative_intransit_mta
FROM
cst_period_close_summary CPCS
WHERE CPCS.organization_id = p_org_id
AND CPCS.acct_period_id = NVL(l_prior_period_id,-1)
AND (rollback_quantity <> 0 OR
rollback_onhand_value <> 0 OR
rollback_intransit_value <> 0 OR
accounted_onhand_value <> 0 OR
accounted_intransit_value <> 0 OR
l_zero_val_hook = 0);
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' rows to CIQT as baseline from CPCS'
);
SELECT primary_cost_method
INTO l_cost_method
FROM mtl_parameters
WHERE organization_id = p_org_id;
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value)
SELECT 11, -- CURRENT ONHAND
p_org_id organization_id,
DECODE(MTA.transaction_source_type_id,
5,
DECODE(
l_cost_method,
2,
NVL(MMT.transfer_cost_group_id,
MMT.cost_group_id),
MMT.cost_group_id),
MMT.cost_group_id),
DECODE(MTA.transaction_source_type_id,
5,
DECODE(
l_cost_method,
2,
DECODE(MMT.transfer_cost_group_id,
NULL, MMT.subinventory_code,
/* Bug 3500534
It is possible to have normal issue to WIP transactions in
average costing organizations with transfer_cost_group_id
= cost_group_id. The following condition ensures such cases
are handled as normal issue to WIP rather than common. */
MMT.cost_group_id, MMT.subinventory_code,
NULL),
MMT.subinventory_code),
MMT.subinventory_code),
MMT.inventory_item_id,
SUM(MTA.base_transaction_value)
FROM mtl_material_transactions MMT,
mtl_transaction_accounts MTA /*,
mtl_secondary_inventories SUB */
WHERE MTA.accounting_line_type = 1 -- inventory
AND MTA.transaction_date >= l_period_start_date
AND MTA.transaction_date <= l_to_date+1-(1/(24*3600))
AND MTA.organization_id = p_org_id
/* AND SUB.organization_id (+) = MMT.organization_id
AND SUB.secondary_inventory_name (+) = MMT.subinventory_code
AND NVL(SUB.asset_inventory,1) = 1 */
AND (sign(MMT.primary_quantity) = sign(MTA.primary_quantity)/*BUG7326014*/
OR
MMT.transaction_action_id = 24)
AND MMT.transaction_id = MTA.transaction_id
AND MMT.transaction_type_id <> 25
GROUP BY
DECODE(MTA.transaction_source_type_id,
5,
DECODE(
l_cost_method,
2,
NVL(MMT.transfer_cost_group_id,
MMT.cost_group_id),
MMT.cost_group_id),
MMT.cost_group_id),
DECODE(MTA.transaction_source_type_id,
5,
DECODE(
l_cost_method,
2,
DECODE(MMT.transfer_cost_group_id,
NULL, MMT.subinventory_code,
/* Bug 3500534
It is possible to have normal issue to WIP transactions in
average costing organizations with transfer_cost_group_id
= cost_group_id. The following condition ensures such cases
are handled as normal issue to WIP rather than common. */
MMT.cost_group_id, MMT.subinventory_code,
NULL),
MMT.subinventory_code),
MMT.subinventory_code),
MMT.inventory_item_id
/* Bug 14036099 */
HAVING (SUM(base_transaction_value) <> 0
OR l_zero_val_hook = 0);
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' rows to CIQT for same MMT MTA primary quantity'
);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value)
SELECT 11, -- CURRENT ONHAND
p_org_id organization_id,
MMT.transfer_cost_group_id,
MMT.transfer_subinventory,
MMT.inventory_item_id,
SUM(MTA.base_transaction_value)
FROM mtl_material_transactions MMT,
mtl_transaction_accounts MTA /*,
mtl_secondary_inventories SUB */
WHERE MTA.accounting_line_type = 1 -- inventory
AND MTA.transaction_date >= l_period_start_date
AND MTA.transaction_date <= l_to_date+1-(1/(24*3600))
AND MTA.organization_id = p_org_id
/* AND SUB.organization_id (+) = MMT.transfer_organization_id
AND SUB.secondary_inventory_name (+) = MMT.transfer_subinventory
AND NVL(SUB.asset_inventory,1) = 1 */
AND sign(MMT.primary_quantity )<>sign( MTA.primary_quantity)/*BUG7326014*/
AND MMT.transaction_id = MTA.transaction_id
AND MMT.transaction_action_id in (1,2,3,5,28,55)
GROUP BY
MMT.transfer_cost_group_id,
MMT.transfer_subinventory,
MMT.inventory_item_id
/* Bug 14036099 */
HAVING (SUM(base_transaction_value) <> 0
OR l_zero_val_hook = 0);
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' rows to CIQT for different MMT MTA primary quantity'
);
INSERT
INTO cst_inv_qty_temp(
qty_source,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value)
SELECT 12, -- CURRENT INTRANSIT
p_org_id organization_id,
DECODE(MMT.transaction_action_id,
24,MMT.cost_group_id,
/* Bug 9764385 */
15,MMT.cost_group_id,
22,MMT.cost_group_id,
MMT.transfer_cost_group_id),
NULL,
MMT.inventory_item_id,
SUM(MTA.base_transaction_value)
FROM mtl_material_transactions MMT,
mtl_transaction_accounts MTA
WHERE MTA.accounting_line_type = 14 -- intransit account
AND MTA.transaction_date >= l_period_start_date
AND MTA.transaction_date <= l_to_date+1-(1/(24*3600))
AND MTA.organization_id = p_org_id
AND MMT.transaction_id = MTA.transaction_id
GROUP
BY MMT.inventory_item_id,
DECODE(MMT.transaction_action_id,
24,MMT.cost_group_id,
/* Bug 9764385 */
15,MMT.cost_group_id,
22,MMT.cost_group_id,
MMT.transfer_cost_group_id)
/* Bug 14036099 */
HAVING (SUM(MTA.base_transaction_value) <> 0
OR l_zero_val_hook = 0);
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' rows to CIQT for intransit quantity'
);
INSERT
INTO cst_per_close_summary_temp(
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value,
rollback_value,
rollback_qty)
SELECT CIQT.cost_group_id,
CIQT.subinventory_code,
CIQT.inventory_item_id,
SUM(DECODE(CIQT.qty_source,
21,0,
22,0,
NVL(CIQT.accounted_value,0))) accounted_value,
SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) rollback_value,
SUM(NVL(CIQT.rollback_qty,0))
FROM cst_inv_qty_temp CIQT,
cst_inv_cost_temp CICT
WHERE CIQT.organization_id = p_org_id
AND CIQT.organization_id = CICT.organization_id(+)
AND NVL(CIQT.cost_group_id,-1) =
NVL(CICT.cost_group_id,NVL(CIQT.cost_group_id,-1))
AND CIQT.inventory_item_id = CICT.inventory_item_id(+)
AND CICT.cost_source(+) = 2 -- PAST
GROUP BY
CIQT.cost_group_id,
CIQT.subinventory_code,
CIQT.inventory_item_id,
CICT.item_cost
/* Bug 14036099 */
HAVING SUM(DECODE(CIQT.qty_source,
21,0,
22,0,
NVL(CIQT.accounted_value,0))) <> 0
OR SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) <> 0
OR SUM(NVL(CIQT.rollback_qty,0)) <> 0
OR l_zero_val_hook = 0;
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' rows to CPCST for simulation purposes'
);
INSERT
INTO cst_period_close_summary(
acct_period_id,
organization_id,
cost_group_id,
subinventory_code,
inventory_item_id,
accounted_value,
rollback_value,
rollback_quantity,
rollback_onhand_value,
rollback_intransit_value,
accounted_onhand_value,
accounted_intransit_value,
onhand_value_discrepancy,
intransit_value_discrepancy,
cumulative_onhand_mta,
cumulative_intransit_mta,
last_update_date,
last_updated_by,
creation_date,
creation_by)
SELECT p_period_id,
CIQT.organization_id,
CIQT.cost_group_id,
CIQT.subinventory_code,
CIQT.inventory_item_id,
SUM(DECODE(CIQT.qty_source,
21,0,
22,0,
NVL(CIQT.accounted_value,0))) accounted_value,
SUM(NVL(CIQT.rollback_qty,0))*NVL(CICT.item_cost,0) rollback_value,
SUM(NVL(CIQT.rollback_qty,0)),
SUM(DECODE(CIQT.qty_source,
3,NVL(CIQT.rollback_qty,0),
4,NVL(CIQT.rollback_qty,0),
5,NVL(CIQT.rollback_qty,0),
0))*NVL(CICT.item_cost,0) rollback_onhand_value,
SUM(DECODE(CIQT.qty_source,
6,NVL(CIQT.rollback_qty,0),
7,NVL(CIQT.rollback_qty,0),
8,NVL(CIQT.rollback_qty,0),
0))*NVL(CICT.item_cost,0) rollback_intransit_value,
SUM(DECODE(CIQT.qty_source,
1,NVL(CIQT.accounted_value,0),
11,NVL(CIQT.accounted_value,0),
0)) accounted_onhand_value,
SUM(DECODE(CIQT.qty_source,
2,NVL(CIQT.accounted_value,0),
12,NVL(CIQT.accounted_value,0),
0)) accounted_intransit_value,
SUM(DECODE(CIQT.qty_source,
3,NVL(CIQT.rollback_qty,0),
4,NVL(CIQT.rollback_qty,0),
5,NVL(CIQT.rollback_qty,0),
0))*NVL(CICT.item_cost,0) -
SUM(DECODE(CIQT.qty_source,
1,NVL(CIQT.accounted_value,0),
11,NVL(CIQT.accounted_value,0),
0)) onhand_value_discrepancy,
SUM(DECODE(CIQT.qty_source,
6,NVL(CIQT.rollback_qty,0),
7,NVL(CIQT.rollback_qty,0),
8,NVL(CIQT.rollback_qty,0),
0))*NVL(CICT.item_cost,0) -
SUM(DECODE(CIQT.qty_source,
2,NVL(CIQT.accounted_value,0),
12,NVL(CIQT.accounted_value,0),
0)) intransit_value_discrepancy,
SUM(DECODE(CIQT.qty_source,
11,NVL(CIQT.accounted_value,0),
21,NVL(CIQT.accounted_value,0),
0)) cumulative_onhand_mta,
SUM(DECODE(CIQT.qty_source,
12,NVL(CIQT.accounted_value,0),
22,NVL(CIQT.accounted_value,0),
0)) cumulative_intransit_mta,
SYSDATE,
1,
SYSDATE,
1
FROM cst_inv_qty_temp CIQT,
cst_inv_cost_temp CICT
WHERE CIQT.organization_id = p_org_id
AND CIQT.organization_id = CICT.organization_id(+)
AND NVL(CIQT.cost_group_id,-1) =
NVL(CICT.cost_group_id,NVL(CIQT.cost_group_id,-1))
AND CIQT.inventory_item_id = CICT.inventory_item_id(+)
AND CICT.cost_source(+) = 2 -- PAST
GROUP BY
CIQT.organization_id,
CIQT.cost_group_id,
CIQT.subinventory_code,
CIQT.inventory_item_id,
CICT.item_cost
/* Bug 14036099 */
HAVING SUM(NVL(CIQT.rollback_qty,0)) <> 0
OR SUM(DECODE(CIQT.qty_source,
3,NVL(CIQT.rollback_qty,0),
4,NVL(CIQT.rollback_qty,0),
5,NVL(CIQT.rollback_qty,0),
0))*NVL(CICT.item_cost,0) <> 0
OR SUM(DECODE(CIQT.qty_source,
6,NVL(CIQT.rollback_qty,0),
7,NVL(CIQT.rollback_qty,0),
8,NVL(CIQT.rollback_qty,0),
0))*NVL(CICT.item_cost,0) <> 0
OR SUM(DECODE(CIQT.qty_source,
1,NVL(CIQT.accounted_value,0),
11,NVL(CIQT.accounted_value,0),
0)) <> 0
OR SUM(DECODE(CIQT.qty_source,
2,NVL(CIQT.accounted_value,0),
12,NVL(CIQT.accounted_value,0),
0)) <> 0
OR l_zero_val_hook = 0;
p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
' rows to CPCS for org/period ' || p_org_id || '/' || p_period_id
);
UPDATE org_acct_periods
SET summarized_flag = 'Y'
WHERE organization_id = p_org_id
AND acct_period_id = p_period_id;