[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
default_cost_group_id
FROM MTL_PARAMETERS
WHERE organization_id = p_org_id;
SELECT
acct_period_id
, schedule_close_date
FROM
ORG_ACCT_PERIODS
WHERE trunc(period_start_date) < c_period_start_date
AND organization_id = c_organization_id
ORDER BY
period_start_date DESC;
PROCEDURE Insert_Rpt_Data
( p_rpt_item_rec IN Report_Rec_Type
)
IS
BEGIN
INSERT INTO
CST_MGD_INFL_ADJ_KARDEX_DATA(
ORGANIZATION_ID
, INVENTORY_ITEM_ID
, TXN_DATE
, TXN_TYPE
, TXN_INI_QTY
, TXN_INI_UNIT_COST
, TXN_INI_H_TOTAL_COST
, TXN_INI_ADJ_TOTAL_COST
, TXN_QTY
, TXN_UNIT_COST
, TXN_H_TOTAL_COST
, TXN_ADJ_TOTAL_COST
, TXN_FNL_QTY
, TXN_FNL_UNIT_COST
, TXN_FNL_H_TOTAL_COST
, TXN_FNL_ADJ_TOTAL_COST
, CREATION_DATE
, TRANSACTION_ID
)
VALUES(
p_rpt_item_rec.organization_id
, p_rpt_item_rec.inventory_item_id
, p_rpt_item_rec.txn_date
, p_rpt_item_rec.txn_type
, NVL(p_rpt_item_rec.txn_ini_qty,0)
, NVL(p_rpt_item_rec.txn_ini_unit_cost,0)
, NVL(p_rpt_item_rec.txn_ini_h_total_cost,0)
, NVL(p_rpt_item_rec.txn_ini_adj_total_cost,0)
, p_rpt_item_rec.txn_qty
, p_rpt_item_rec.txn_unit_cost
, p_rpt_item_rec.txn_h_total_cost
, p_rpt_item_rec.txn_adj_total_cost
, NVL(p_rpt_item_rec.txn_fnl_qty,0)
, NVL(p_rpt_item_rec.txn_fnl_unit_cost,0)
, NVL(p_rpt_item_rec.txn_fnl_h_total_cost,0)
, NVL(p_rpt_item_rec.txn_fnl_adj_total_cost,0)
, p_rpt_item_rec.creation_date
, p_rpt_item_rec.txn_id
);
, 'Insert_Rpt_Data'
);
END Insert_Rpt_Data;
SELECT
acct_period_id
INTO
x_rpt_from_acct_per_id
FROM
ORG_ACCT_PERIODS oap
WHERE oap.organization_id = p_org_id
AND oap.period_start_date <= p_rpt_from_date
AND oap.schedule_close_date >= p_rpt_from_date
AND oap.open_flag = 'N'
AND oap.period_close_date IS NOT NULL;
SELECT
acct_period_id
INTO
x_rpt_to_acct_per_id
FROM
ORG_ACCT_PERIODS oap
WHERE oap.organization_id = p_org_id
AND oap.period_start_date <= p_rpt_to_date
AND oap.schedule_close_date >= p_rpt_to_date
AND oap.open_flag = 'N'
AND oap.period_close_date IS NOT NULL;
SELECT
Begin_Qty
, NVL((Actual_Inflation_Adj - Issue_Inflation_Adj), 0)
, NVL((Actual_Qty - Issue_Qty), 0)
INTO
x_init_qty
, l_final_infl_adj
, l_final_qty
FROM
CST_MGD_INFL_ADJUSTED_COSTS
WHERE Organization_ID = p_org_id
AND Acct_Period_ID = p_acct_period_id
AND Inventory_Item_ID = p_item_id;
SELECT
Transaction_Type_Name
INTO
x_txn_type_name
FROM
MTL_TRANSACTION_TYPES
WHERE Transaction_Type_ID = p_txn_type_id
AND NVL(Disable_Date, SYSDATE + 1) > SYSDATE;
SELECT
NVL(SUM(Primary_Quantity), 0)
INTO
x_offset_qty
FROM
MTL_MATERIAL_TRANSACTIONS
WHERE Organization_ID = p_org_id
AND Inventory_Item_ID = p_item_id
AND Acct_Period_ID = p_from_acct_per_id
AND transaction_action_id <> 2
AND Transaction_Date < p_rpt_from_date
AND Cost_Group_ID = p_cost_group_id
AND nvl(owning_tp_type,2) <> 1;
SELECT
Transaction_ID
, Transaction_Type_ID
, Transaction_Date
, Primary_Quantity
, Actual_Cost
, Prior_Cost
, New_Cost
, Transfer_Organization_ID
, transaction_source_type_id
, transaction_action_id
, creation_date
FROM
MTL_MATERIAL_TRANSACTIONS
WHERE Organization_ID = p_org_id
AND Inventory_Item_ID = p_item_id
AND Acct_Period_ID = p_acct_period_id
AND transaction_action_id <> 2
AND Transaction_Date BETWEEN p_per_first_txn_date
AND p_per_last_txn_date
AND Primary_Quantity <> 0
AND Cost_Group_ID = p_cost_group_id
AND nvl(owning_tp_type,2) <> 1
ORDER BY
TRUNC(Transaction_Date)
, creation_date
, transaction_id;
SELECT
INFL.Inventory_Item_ID
, INFL.Acct_Period_ID
, NVL(INFL.Begin_Qty,0)
, NVL(INFL.Begin_Inflation_Adj,0)
, (NVL(INFL.Actual_Inflation_Adj,0) - ABS(NVL(INFL.Issue_Inflation_Adj,0)))
, (NVL(INFL.Actual_Qty,0) - ABS(NVL(INFL.Issue_Qty,0)))
, PER.Schedule_Close_Date
, PER.Period_Start_Date
FROM
CST_MGD_INFL_ADJUSTED_COSTS INFL
, MTL_SYSTEM_ITEMS_B_KFV MSI
, ORG_ACCT_PERIODS PER
WHERE INFL.Organization_ID = c_org_id
AND MSI.Organization_ID = c_org_id
AND PER.Organization_ID = c_org_id
AND INFL.Inventory_Item_ID = MSI.Inventory_Item_ID
AND MSI.Concatenated_Segments BETWEEN c_item_from_code
AND c_item_to_code
AND INFL.Acct_Period_ID BETWEEN c_rpt_from_acct_per_id
AND c_rpt_to_acct_per_id
AND INFL.Acct_Period_ID = PER.Acct_Period_ID
AND PER.Open_Flag = 'N'
AND PER.PERIOD_CLOSE_DATE IS NOT NULL
ORDER BY INFL.Acct_Period_ID;
SELECT
INFL.Inventory_Item_ID
, INFL.Acct_Period_ID
, NVL(INFL.Begin_Qty,0)
, NVL(INFL.Begin_Inflation_Adj,0)
, (NVL(INFL.Actual_Inflation_Adj,0) - ABS(NVL(INFL.Issue_Inflation_Adj,0)))
, (NVL(INFL.Actual_Qty,0) - ABS(NVL(INFL.Issue_Qty,0)))
, PER.Schedule_Close_Date
, PER.Period_Start_Date
FROM
CST_MGD_INFL_ADJUSTED_COSTS INFL
, MTL_SYSTEM_ITEMS_B MSI
, ORG_ACCT_PERIODS PER
WHERE INFL.Organization_ID = c_org_id
AND MSI.Organization_ID = c_org_id
AND PER.Organization_ID = c_org_id
AND INFL.Inventory_Item_ID = MSI.Inventory_Item_ID
AND INFL.Acct_Period_ID BETWEEN c_rpt_from_acct_per_id
AND c_rpt_to_acct_per_id
AND INFL.Acct_Period_ID = PER.Acct_Period_ID
AND PER.Open_Flag = 'N'
AND PER.PERIOD_CLOSE_DATE IS NOT NULL
ORDER BY INFL.Acct_Period_ID;
DELETE FROM CST_MGD_INFL_ADJ_KARDEX_DATA;
Insert_Rpt_Data
( p_rpt_item_rec => l_rpt_item_tbl_rec(l_index)
);