[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_rpt_data (p_rpt_item_rec IN report_rec_type)
IS
BEGIN
INSERT INTO cst_mgd_mstr_book_temp (
transaction_id
,organization_id
,inventory_item_id
,uom_code -- added for inv book
,item_code
,item_desc
,org_name
,currency_code
,txn_source -- added for inv book
,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
,sub_inv_organization_id -- Added by ppandit for Italy and China Enhancements
,subinventory_code -- Added by ppandit for Italy and China Enhancements
)
VALUES (
p_rpt_item_rec.transaction_id
,p_rpt_item_rec.organization_id
,p_rpt_item_rec.inventory_item_id
,p_rpt_item_rec.uom_code -- added for inv book
,p_rpt_item_rec.item_code -- added for inv book
,p_rpt_item_rec.item_desc -- added for inv book
,p_rpt_item_rec.org_name -- added for inv book
,p_rpt_item_rec.currency_code -- added for inv book
,p_rpt_item_rec.txn_source -- added for inv book
,p_rpt_item_rec.txn_date
,p_rpt_item_rec.txn_type
,p_rpt_item_rec.txn_ini_qty
,p_rpt_item_rec.txn_ini_unit_cost
,p_rpt_item_rec.txn_ini_h_total_cost
,p_rpt_item_rec.txn_ini_adj_total_cost
,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
,p_rpt_item_rec.txn_fnl_qty
,p_rpt_item_rec.txn_fnl_unit_cost
,p_rpt_item_rec.txn_fnl_h_total_cost
,p_rpt_item_rec.txn_fnl_adj_total_cost
,p_rpt_item_rec.creation_date
,p_rpt_item_rec.sub_inv_organization_id -- Added by ppandit for Italy and China Enhancements
,p_rpt_item_rec.subinventory_code -- Added by ppandit for Italy and China Enhancements
);
, 'Insert_Rpt_Data'
);
END insert_rpt_data;
SELECT f.acct_period_id
FROM org_acct_periods f
WHERE f.organization_id = p_org_id
AND f.period_start_date <= c_rpt_from_date
AND f.schedule_close_date >= c_rpt_from_date;
SELECT t.acct_period_id
FROM org_acct_periods t
WHERE t.organization_id = p_org_id
AND t.period_start_date <= c_rpt_to_date
AND t.schedule_close_date >= c_rpt_to_date;
SELECT HOU.organization_id
FROM hr_organization_units HOU
,mtl_parameters_view MPV
,xle_firstparty_information_v XFI
WHERE MPV.master_organization_id = p_legal_entity
AND HOU.organization_id = MPV.organization_id
AND XFI.legal_entity_id = MPV.master_organization_id;*/
SELECT mp.organization_id
FROM mtl_parameters mp,
cst_organization_definitions cod
WHERE mp.organization_id = cod.organization_id
AND cod.legal_entity = p_legal_entity
AND EXISTS (
SELECT 1
FROM org_access ACC
WHERE acc.organization_id = MP.organization_id
AND acc.responsibility_id = l_resp_id
);
SELECT MIN(MSI.secondary_inventory_name)
,MAX(MSI.secondary_inventory_name)
FROM mtl_secondary_inventories MSI
WHERE MSI.organization_id = ln_org;
SELECT FND_DATE.date_to_chardate (TRUNC (FND_DATE.canonical_to_date (p_date_from)))
INTO p_date_from_formatting
FROM DUAL;
SELECT FND_DATE.date_to_chardate (TRUNC (FND_DATE.canonical_to_date (p_date_to)))
INTO p_date_to_formatting
FROM DUAL;
SELECT fc.extended_precision, gsob.currency_code
INTO gn_precision_val, gc_currency_code
FROM gl_sets_of_books gsob,
org_organization_definitions ood,
fnd_currencies fc
WHERE ood.organization_id = p_inventory_org
AND ood.set_of_books_id = gsob.set_of_books_id
AND fc.currency_code = gsob.currency_code;
SELECT FRV.RESPONSIBILITY_NAME,
FND_DATE.DATE_TO_CHARDT(FCR.REQUEST_DATE),
FAV.APPLICATION_NAME,
FU.USER_NAME
INTO GC_RESPONSIBILITY
,GC_REQUEST_TIME
,GC_APPLICATION
,GC_REQUESTED_BY
FROM FND_CONCURRENT_REQUESTS FCR,
FND_RESPONSIBILITY_VL FRV,
FND_APPLICATION_VL FAV,
FND_USER FU
WHERE FCR.REQUEST_ID = P_CONC_REQUEST_ID
AND FCR.RESPONSIBILITY_APPLICATION_ID = FRV.APPLICATION_ID
AND FCR.RESPONSIBILITY_ID = FRV.RESPONSIBILITY_ID
AND FRV.APPLICATION_ID = FAV.APPLICATION_ID
AND FU.USER_ID = FCR.REQUESTED_BY;
SELECT meaning
INTO gc_include_cost
FROM fnd_lookups
WHERE lookup_code = p_include_item_cost
AND lookup_type = 'YES_NO';
SELECT meaning
INTO gc_detail
FROM fnd_lookups
WHERE lookup_code = p_detail
AND lookup_type = 'INV_BOOK_DETAIL';
SELECT abc_class_name
INTO gc_abc_class_name
FROM mtl_abc_classes
WHERE abc_class_id = p_abc_class_id;
SELECT assignment_group_name
INTO gc_abc_group_name
FROM mtl_abc_assignment_groups
WHERE assignment_group_id = p_abc_group_id;
SELECT category_set_name
INTO gc_category_set_name_1
FROM mtl_category_sets
WHERE category_set_id = p_category_set_id_from; -- Changed by ppandit P_CATEGORY_SET_ID to P_CATEGORY_SET_ID_FROM
SELECT category_set_name
INTO gc_category_set_name_2
FROM mtl_category_sets
WHERE category_set_id = p_category_set_id_to;
SELECT shipment_number
INTO lv_shipment_num
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT waybill_airbill
INTO lc_waybill_airbill
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT MMT.organization_id
,MMT.transaction_source_type_id
,MMT.transaction_source_id
,MMT.trx_source_line_id
INTO ln_org_id
,ln_trans_source_type_id
,ln_trans_source_id
,ln_trx_line_id
FROM mtl_material_transactions MMT
WHERE MMT.transaction_id = p_transaction_id;
SELECT PHA.segment1
INTO lc_po_or_so_number
FROM po_headers_all PHA
WHERE PHA.po_header_id = ln_trans_source_id;
SELECT OOH.order_number -- Changed to refer to OE tables for Bug Number 8834843
INTO lc_po_or_so_number
FROM oe_order_headers_all OOH
,oe_order_lines_all OOL
WHERE OOH.header_id = OOL.header_id
AND OOL.line_id = ln_trx_line_id;
-- SELECT transaction_source_name
-- INTO lv_po_number
-- FROM mtl_material_transactions
-- WHERE transaction_id =p_transaction_id;
SELECT name
INTO lv_ledgername
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
SELECT HOU.name
INTO lc_inventory_org
FROM hr_all_organization_units HOU
WHERE HOU.organization_id = p_inventory_org;
SELECT MLS.meaning
INTO lc_meaning
FROM mfg_lookups MLS
WHERE MLS.lookup_type = 'CST_BREAK_BY_INV'
AND MLS.lookup_code = p_break_by
AND MLS.enabled_flag = 'Y';
SELECT MLS.meaning
INTO lc_meaning
FROM mfg_lookups MLS
WHERE MLS.lookup_type = 'CST_ALL_OR_ONE_INV'
AND MLS.lookup_code = p_all_or_single
AND MLS.enabled_flag = 'Y';
SELECT TO_CHAR(SYSDATE, FND_PROFILE.value('ICX_DATE_FORMAT_MASK'))
INTO lc_sysdate
FROM SYS.dual;
SELECT MCS.structure_id
INTO ln_cat_struct
FROM mtl_category_sets MCS
WHERE MCS.category_set_id = p_category_set_id;
SELECT HOU.location_code
,HOU.internal_external_meaning
,HOU.address_line_1 ||
' ' ||
HOU.address_line_2 ||
' ' ||
HOU.address_line_3 ||
' ' ||
HOU.town_or_city ||
' ' ||
HOU.country
INTO lc_location
,lc_type
,lc_address
FROM hr_organization_units_v HOU
WHERE HOU.organization_id = p_org_id;
SELECT H.location_code
,H.description
,H.address_line_1 ||
' ' ||
H.address_line_2 ||
' ' ||
H.country
INTO lc_location
,lc_type
,lc_address
FROM mtl_secondary_inventories A
,hr_locations_all H
WHERE A.secondary_inventory_name = p_subinvname
AND H.location_id(+) = A.location_id
AND organization_id = p_org_id;
SELECT CMIAKD.transaction_id TRANSACTION_ID
,CMIAKD.txn_date TXN_DATE
,CMIAKD.txn_ini_qty TXN_INI_QTY
,ROUND(CMIAKD.txn_ini_unit_cost, 2) * CMIAKD.txn_ini_qty TXN_INI_VALUE
,CMIAKD.organization_id ORGANIZATION_ID
FROM cst_mgd_mstr_book_temp CMIAKD
WHERE CMIAKD.inventory_item_id = p_inventory_item_id
AND CMIAKD.organization_id = p_org_id
AND p_break_by = 1
ORDER BY 5, 2, 1 ASC;
SELECT DISTINCT organization_id
FROM cst_mgd_mstr_book_temp
WHERE inventory_item_id = p_inventory_item_id
ORDER BY organization_id ASC;
SELECT CMIAKD.transaction_id TRANSACTION_ID
,CMIAKD.txn_date TXN_DATE
,CMIAKD.txn_qty TXN_FNL_QTY
,ROUND(CMIAKD.txn_unit_cost, 2) * CMIAKD.txn_qty TXN_FNL_VALUE
,CMIAKD.organization_id ORGANIZATION_ID
FROM cst_mgd_mstr_book_temp CMIAKD
WHERE CMIAKD.inventory_item_id = p_inventory_item_id
AND CMIAKD.organization_id = p_org_id
AND p_break_by = 1
ORDER BY 5, 2, 1 ASC;
SELECT DISTINCT organization_id
FROM cst_mgd_mstr_book_temp
WHERE inventory_item_id = p_inventory_item_id
ORDER BY organization_id ASC;
SELECT CMIAKD.transaction_id TRANSACTION_ID
,CMIAKD.txn_date TXN_DATE
,CMIAKD.txn_ini_qty TXN_INI_QTY
,ROUND(CMIAKD.txn_ini_unit_cost, 2) * CMIAKD.txn_ini_qty TXN_INI_VALUE
,CMIAKD.organization_id ORGANIZATION_ID
FROM cst_mgd_mstr_book_temp CMIAKD
WHERE CMIAKD.inventory_item_id = p_inventory_item_id
AND CMIAKD.organization_id = p_org_id
AND p_break_by = 1
ORDER BY 5, 2, 1 ASC;
SELECT CMIAKD.transaction_id TRANSACTION_ID
,CMIAKD.currency_code CURRENCY_CODE
,ROUND(CMIAKD.txn_unit_cost, 2) TXN_UNIT_COST
,CMIAKD.txn_date TXN_DATE
,CMIAKD.txn_type TXN_TYPE
,CMIAKD.txn_source TXN_SOURCE
,CMIAKD.txn_ini_qty TXN_INI_QTY
,ROUND(CMIAKD.txn_ini_unit_cost, 2) TXN_INI_UNIT_COST
,ROUND(CMIAKD.txn_ini_unit_cost, 2) * CMIAKD.txn_ini_qty TXN_INI_VALUE
,CMIAKD.txn_fnl_qty TXN_FNL_QTY
,ROUND(CMIAKD.txn_fnl_unit_cost, 2) TXN_FNL_UNIT_COST
,CMIAKD.txn_qty TXN_QTY
,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
,ROUND(CMIAKD.txn_h_total_cost, 2) TXN_VALUE
,CMIAKD.organization_id ORGANIZATION_ID
FROM cst_mgd_mstr_book_temp CMIAKD
WHERE CMIAKD.inventory_item_id = p_inventory_item_id
AND p_break_by = 1
AND p_detail = 'S'
UNION ALL
SELECT CMIAKD.transaction_id TRANSACTION_ID
,CMIAKD.currency_code CURRENCY_CODE
,ROUND(CMIAKD.txn_unit_cost, 2) TXN_UNIT_COST
,CMIAKD.txn_date TXN_DATE
,CMIAKD.txn_type TXN_TYPE
,CMIAKD.txn_source TXN_SOURCE
,CMIAKD.txn_ini_qty TXN_INI_QTY
,ROUND(CMIAKD.txn_ini_unit_cost, 2) TXN_INI_UNIT_COST
,ROUND(CMIAKD.txn_ini_unit_cost, 2) * CMIAKD.txn_ini_qty TXN_INI_VALUE
,CMIAKD.txn_fnl_qty TXN_FNL_QTY
,ROUND(CMIAKD.txn_fnl_unit_cost, 2) TXN_FNL_UNIT_COST
,CMIAKD.txn_qty TXN_QTY
,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
,ROUND(CMIAKD.txn_h_total_cost, 2) TXN_VALUE
,CMIAKD.organization_id ORGANIZATION_ID
FROM cst_mgd_mstr_book_temp CMIAKD
WHERE CMIAKD.inventory_item_id = p_inventory_item_id
AND CMIAKD.organization_id = p_organization_id
AND p_break_by IN (2, 4)
AND p_detail = 'S'
UNION ALL
SELECT CMIAKD.transaction_id TRANSACTION_ID
,CMIAKD.currency_code CURRENCY_CODE
,ROUND(CMIAKD.txn_unit_cost, 2) TXN_UNIT_COST
,CMIAKD.txn_date TXN_DATE
,CMIAKD.txn_type TXN_TYPE
,CMIAKD.txn_source TXN_SOURCE
,CMIAKD.txn_ini_qty TXN_INI_QTY
,ROUND(CMIAKD.txn_ini_unit_cost, 2) TXN_INI_UNIT_COST
,ROUND(CMIAKD.txn_ini_unit_cost, 2) * CMIAKD.txn_ini_qty TXN_INI_VALUE
,CMIAKD.txn_fnl_qty TXN_FNL_QTY
,ROUND(CMIAKD.txn_fnl_unit_cost, 2) TXN_FNL_UNIT_COST
,CMIAKD.txn_qty TXN_QTY
,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
,ROUND(CMIAKD.txn_h_total_cost, 2) TXN_VALUE
,CMIAKD.organization_id ORGANIZATION_ID
FROM cst_mgd_mstr_book_temp CMIAKD
WHERE CMIAKD.inventory_item_id = p_inventory_item_id
AND CMIAKD.organization_id = p_organization_id
AND CMIAKD.subinventory_code = p_sub_inv_org_name
AND CMIAKD.sub_inv_organization_id = p_sub_inv_org_id
AND p_break_by IN (3, 5)
AND p_detail = 'S'
ORDER BY 15, 4, 1 ASC;
SELECT DISTINCT organization_id
FROM cst_mgd_mstr_book_temp
WHERE inventory_item_id = p_inventory_item_id
ORDER BY organization_id ASC;
SELECT CMIAKD.transaction_id TRANSACTION_ID
,CMIAKD.txn_date TXN_DATE
,CMIAKD.txn_qty TXN_FNL_QTY
,ROUND(CMIAKD.txn_unit_cost, 2) * CMIAKD.txn_qty TXN_FNL_VALUE
,CMIAKD.organization_id ORGANIZATION_ID
FROM cst_mgd_mstr_book_temp CMIAKD
WHERE CMIAKD.inventory_item_id = p_inventory_item_id
AND CMIAKD.organization_id = p_org_id
AND p_break_by = 1
ORDER BY 5, 2, 1 ASC;
SELECT CMIAKD.transaction_id TRANSACTION_ID
,CMIAKD.txn_date TXN_DATE
,CMIAKD.txn_fnl_qty TXN_FNL_QTY
,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
,CMIAKD.organization_id ORGANIZATION_ID
FROM cst_mgd_mstr_book_temp CMIAKD
WHERE CMIAKD.inventory_item_id = p_inventory_item_id
AND p_break_by = 1
AND p_detail = 'S'
UNION ALL
SELECT CMIAKD.transaction_id TRANSACTION_ID
,CMIAKD.txn_date TXN_DATE
,CMIAKD.txn_fnl_qty TXN_FNL_QTY
,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
,CMIAKD.organization_id ORGANIZATION_ID
FROM cst_mgd_mstr_book_temp CMIAKD
WHERE CMIAKD.inventory_item_id = p_inventory_item_id
AND CMIAKD.organization_id = p_organization_id
AND p_break_by IN (2, 4)
AND p_detail = 'S'
UNION ALL
SELECT CMIAKD.transaction_id TRANSACTION_ID
,CMIAKD.txn_date TXN_DATE
,CMIAKD.txn_fnl_qty TXN_FNL_QTY
,ROUND(CMIAKD.txn_fnl_unit_cost, 2) * CMIAKD.txn_fnl_qty TXN_FNL_VALUE
,CMIAKD.organization_id ORGANIZATION_ID
FROM cst_mgd_mstr_book_temp CMIAKD
WHERE CMIAKD.inventory_item_id = p_inventory_item_id
AND CMIAKD.organization_id = p_organization_id
AND CMIAKD.subinventory_code = p_sub_inv_org_name
AND CMIAKD.sub_inv_organization_id = p_sub_inv_org_id
AND p_break_by IN (3, 5)
AND p_detail = 'S'
ORDER BY 5, 2, 1 ASC;
SELECT DISTINCT organization_id
FROM cst_mgd_mstr_book_temp
WHERE inventory_item_id = p_inventory_item_id
ORDER BY organization_id ASC;
SELECT p_break_by
INTO ln_break_by
FROM SYS.dual;
SELECT p_detail
INTO lc_detail_param
FROM SYS.dual;
SELECT p_include_item_cost
INTO lc_incl_cost
FROM SYS.dual;
SELECT TO_CHAR (TO_DATE (p_date_from, 'YYYY/MM/DD HH24:MI:SS'), FND_PROFILE.value('ICX_DATE_FORMAT_MASK'))
INTO lc_date
FROM SYS.dual;
SELECT TO_CHAR (TO_DATE (p_date_to, 'YYYY/MM/DD HH24:MI:SS'), FND_PROFILE.value ('ICX_DATE_FORMAT_MASK'))
INTO lc_date
FROM SYS.dual;
SELECT MLS.meaning
INTO lc_transaction_action
FROM mtl_material_transactions MMT
,mfg_lookups MLS
WHERE MLS.lookup_code = MMT.transaction_action_id
AND MLS.lookup_type = 'MTL_TRANSACTION_ACTION'
AND MMT.transaction_id = p_transaction_id;
SELECT
count(*)
INTO COUNT_ROWS
FROM
CST_MGD_MSTR_BOOK_TEMP;
SELECT
f.acct_period_id
FROM
org_acct_periods f
WHERE f.organization_id = p_org_id
AND f.period_start_date <= c_rpt_from_date
AND f.schedule_close_date >= c_rpt_from_date
AND F.Open_Flag = 'N'
AND F.Period_Close_Date IS NOT NULL;
SELECT
t.acct_period_id
FROM
org_acct_periods t
WHERE t.organization_id = p_org_id
AND t.period_start_date <= c_rpt_to_date
AND t.schedule_close_date >= c_rpt_to_date
AND T.Open_Flag = 'N'
AND T.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
SUBSTR(Transaction_Type_Name,1,30)
INTO
x_txn_type_name
FROM
MTL_TRANSACTION_TYPES
WHERE Transaction_Type_ID = p_txn_type_id;
SELECT mmt.transaction_id transaction_id,
mmt.transaction_type_id transaction_type_id,
mmt.transaction_source_type_id transaction_source_type_id,
mmt.transaction_action_id transaction_action_id,
mmt.transaction_date transaction_date,
mmt.primary_quantity primary_quantity, mmt.actual_cost actual_cost,
mmt.prior_cost prior_cost, mmt.new_cost new_cost,
mmt.value_change value_change,
mmt.percentage_change percentage_change,
mmt.transfer_organization_id transfer_organization_id,
mmt.creation_date creation_date, mmt.quantity_adjusted,
NVL (mmt.subinventory_code, ' ') subinventory_code, mmt.organization_id subinventory_org_id
FROM mtl_material_transactions mmt
WHERE mmt.organization_id = p_org_id
AND mmt.inventory_item_id = p_item_id
AND NVL (mmt.acct_period_id, 0) =
NVL (p_acct_period_id, NVL (mmt.acct_period_id, 0))
AND NVL (mmt.subinventory_code, '0') >=
NVL (p_subinv_from, NVL (mmt.subinventory_code, '0'))
AND NVL (mmt.subinventory_code, '0') <=
NVL (p_subinv_to, NVL (mmt.subinventory_code, '0'))
AND mmt.transaction_date BETWEEN TO_DATE(p_date_from, 'YYYY/MM/DD HH24:MI:SS') AND TO_DATE(p_date_to, 'YYYY/MM/DD HH24:MI:SS') + (86399 / 86400) -- Changed by ppandit for using params directly, Italy China Enhancements
AND ( mmt.subinventory_code IS NULL
OR mmt.subinventory_code =
(SELECT secondary_inventory_name
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = mmt.subinventory_code
AND organization_id = mmt.organization_id
AND quantity_tracked = 1
AND asset_inventory = 1)
)
AND mmt.transaction_id NOT IN (
SELECT mmt1.transaction_id
FROM mtl_material_transactions mmt1
WHERE mmt1.organization_id = p_org_id
AND mmt1.inventory_item_id = p_item_id
AND NVL (mmt1.acct_period_id, 0) =
NVL (p_acct_period_id, NVL (mmt1.acct_period_id, 0))
AND NVL (mmt1.subinventory_code, '0') >=
NVL (p_subinv_from, NVL (mmt1.subinventory_code, '0'))
AND NVL (mmt1.subinventory_code, '0') <=
NVL (p_subinv_to, NVL (mmt1.subinventory_code, '0'))
AND mmt1.transaction_source_type_id = 5
AND mmt1.transaction_action_id = 24
AND NVL (mmt1.owning_tp_type, 2) = 1)
AND mmt.transaction_action_id <> 30
AND NVL (mmt.owning_tp_type, 2) <> 1
ORDER BY mmt.acct_period_id,
mmt.transaction_date,
mmt.creation_date,
mmt.transaction_id;
Select Transaction_source_type_name
Into l_rpt_item_tbl_rec(l_index).txn_source
From MTL_TXN_SOURCE_TYPES
Where Transaction_Source_Type_Id = l_item_txn_info.transaction_source_type_id;
select 'No TXN Source'
INTO l_rpt_item_tbl_rec(l_index).txn_source
from dual;
v_select_clause VARCHAR2(4000);
SELECT
primary_cost_method
FROM
mtl_parameters
WHERE
organization_id = c_organization_id;
SELECT category_set_name
FROM mtl_category_sets
WHERE category_set_id = p_cat_set_id;
v_select_clause := NULL;
SELECT MIN (secondary_inventory_name)
INTO subinv_min
FROM mtl_secondary_inventories
WHERE organization_id = p_org_id;
SELECT MAX (secondary_inventory_name)
INTO subinv_max
FROM mtl_secondary_inventories
WHERE organization_id = p_org_id;
SELECT category_set_id
FROM mtl_category_sets
WHERE category_set_name BETWEEN lc_cat_set_high AND lc_cat_set_low
AND mult_item_cat_assign_flag = 'N'
ORDER BY category_set_id ASC
)
LOOP
l_struct := get_structure_id (r_cat.category_set_id);
v_select_clause := 'SELECT DISTINCT MSI.inventory_item_id
,MSI.primary_uom_code
,' || l_msi_segment ||
',MSI.description
,OOD.organization_name
,GSOB.currency_code ';
v_final_query := v_select_clause || v_from_clause || v_where_clause || v_order_by;
SELECT NVL (SUM (transaction_quantity), 0)
INTO l_item_ohq
FROM mtl_onhand_quantities
WHERE inventory_item_id = l_item_id
AND organization_id = p_org_id
AND subinventory_code BETWEEN NVL (p_subinv_from, subinventory_code)
AND NVL (p_subinv_to, subinventory_code)
AND subinventory_code NOT IN (
SELECT secondary_inventory_name
FROM mtl_secondary_inventories
WHERE organization_id = p_org_id
AND asset_inventory = 2);
SELECT NVL (SUM (mmt.primary_quantity), 0)
INTO l_item_trx_qty
FROM mtl_material_transactions mmt
WHERE mmt.organization_id = p_org_id
AND mmt.inventory_item_id = l_item_id
AND mmt.subinventory_code BETWEEN NVL (p_subinv_from,
mmt.subinventory_code)
AND NVL (p_subinv_to, mmt.subinventory_code)
AND ( mmt.subinventory_code IS NULL
OR mmt.subinventory_code =
(SELECT secondary_inventory_name
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = mmt.subinventory_code
AND organization_id = mmt.organization_id
AND quantity_tracked = 1
AND asset_inventory = 1)
)
AND mmt.transaction_id NOT IN (
SELECT mmt1.transaction_id
FROM mtl_material_transactions mmt1
WHERE mmt1.organization_id = p_org_id
AND mmt1.inventory_item_id = l_item_id
AND mmt1.subinventory_code BETWEEN NVL (p_subinv_from,
mmt1.subinventory_code
)
AND NVL (p_subinv_to,
mmt1.subinventory_code
)
AND mmt1.transaction_source_type_id = 5
AND mmt1.transaction_action_id = 24
AND NVL (mmt1.owning_tp_type, 2) = 1)
AND mmt.transaction_action_id <> 30
AND NVL (mmt.owning_tp_type, 2) <> 1
AND mmt.transaction_date BETWEEN TO_DATE(p_date_from, 'YYYY/MM/DD HH24:MI:SS') AND SYSDATE;
SELECT NVL (mmt.prior_cost, mmt.actual_cost)
INTO l_item_unit_cost
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id =
(SELECT MIN (transaction_id)
FROM mtl_material_transactions
WHERE organization_id = p_org_id
AND inventory_item_id = l_item_id
AND transaction_date =
(SELECT MIN (transaction_date)
FROM mtl_material_transactions
WHERE organization_id = p_org_id
AND transaction_action_id NOT IN (24, 30)
AND NVL (owning_tp_type, 2) <> 1
AND transaction_date BETWEEN TO_DATE(p_date_from, 'YYYY/MM/DD HH24:MI:SS')
AND TO_DATE(p_date_to, 'YYYY/MM/DD HH24:MI:SS') + (86399 / 86400) -- Changed by ppandit for using params directly, Italy China Enhancements
AND inventory_item_id = l_item_id
AND subinventory_code BETWEEN NVL (p_subinv_from,
subinventory_code
)
AND NVL (p_subinv_to,
subinventory_code
)))
AND ( mmt.subinventory_code IS NULL
OR mmt.subinventory_code =
(SELECT secondary_inventory_name
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = mmt.subinventory_code
AND organization_id = mmt.organization_id
AND quantity_tracked = 1
AND asset_inventory = 1)
)
AND mmt.transaction_id NOT IN (
SELECT mmt1.transaction_id
FROM mtl_material_transactions mmt1
WHERE mmt1.organization_id = p_org_id
AND mmt1.inventory_item_id = l_item_id
AND NVL (mmt1.owning_tp_type, 2) = 1
AND mmt1.transaction_date BETWEEN TO_DATE(p_date_from, 'YYYY/MM/DD HH24:MI:SS') AND TO_DATE(p_date_to, 'YYYY/MM/DD HH24:MI:SS') + (86399 / 86400) -- Changed by ppandit for using params directly, Italy China Enhancements
AND mmt1.subinventory_code BETWEEN NVL (p_subinv_from,
mmt1.subinventory_code
)
AND NVL (p_subinv_to,
mmt1.subinventory_code
)
AND mmt1.transaction_source_type_id = 5
AND mmt1.transaction_action_id = 24)
AND mmt.transaction_action_id <> 30
AND NVL (mmt.owning_tp_type, 2) <> 1;
Insert_Rpt_Data
( p_rpt_item_rec => l_rpt_item_tbl_rec(l_index)
);