[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*)
INTO l_count
FROM fnd_concurrent_requests
WHERE request_id = p_request_id
AND phase_code = 'C';
SELECT inventory_item_id
, purchasing_enabled_flag
, customer_order_enabled_flag
, internal_order_enabled_flag
, mtl_transactions_enabled_flag
, stock_enabled_flag
, bom_enabled_flag
, build_in_wip_flag
, invoice_enabled_flag
, source_organization_id --myerrams, Bug: 5964347
, source_subinventory --myerrams, Bug: 5964347
FROM mtl_system_items_b
WHERE organization_id = p_source_org_id
AND ((base_item_id is null and p_copy_base_models = 'Y') or (base_item_id is not null and p_copy_base_models = 'N'))
AND eng_item_flag = p_copy_eng_items;
Following Query is added to update mtl_item_revisions_interface table with Revision Id when ValidateItems is No.
Bug: 4892069
*/
--myerrams, Bug: 5624219.
CURSOR c_item_rev_update_cursor(c_set_process_id_in NUMBER)
IS
SELECT organization_id
, inventory_item_id
, revision
FROM mtl_item_revisions_interface
WHERE set_process_id = c_set_process_id_in;
SELECT COUNT(*)
INTO l_count
FROM mtl_system_items_b
WHERE organization_id = p_source_org_id
AND ((base_item_id is null and p_copy_base_models = 'Y') or (base_item_id is not null and p_copy_base_models = 'N'))
AND eng_item_flag = p_copy_eng_items;
SELECT organization_id
, cost_of_sales_account
, encumbrance_account
, sales_account
, expense_account
INTO l_organization_id
, l_cost_of_sales_account
, l_encumbrance_account
, l_sales_account
, l_expense_Account
FROM mtl_parameters
WHERE organization_id = p_target_org_id;
SELECT mtl_system_items_intf_sets_s.NEXTVAL
INTO l_set_process_id
FROM dual;
INSERT INTO mtl_system_items_interface
( process_flag
, set_process_id
, transaction_type
, inventory_item_id
, organization_id
, cost_of_sales_account
, encumbrance_account
, sales_account
, expense_account
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, purchasing_enabled_flag
, CUSTOMER_ORDER_ENABLED_FLAG
, INTERNAL_ORDER_ENABLED_FLAG
, MTL_TRANSACTIONS_ENABLED_FLAG
, STOCK_ENABLED_FLAG
, BOM_ENABLED_FLAG
, BUILD_IN_WIP_FLAG
, invoice_enabled_flag
, source_organization_id --myerrams, Bug: 5964347
, source_subinventory --myerrams, Bug: 5964347
)
VALUES
( l_process_flag --myerrams, Bug: 4892069
, l_set_process_id
, 'CREATE'
, l_mtl_system_items_tbl(l_counter).inventory_item_id
, p_target_org_id
, l_cost_of_sales_account
, l_encumbrance_account
, l_sales_account
, l_expense_account
, 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
, l_mtl_system_items_tbl(l_counter).purchasing_enabled_flag
, l_mtl_system_items_tbl(l_counter).customer_order_enabled_flag
, l_mtl_system_items_tbl(l_counter).internal_order_enabled_flag
, l_mtl_system_items_tbl(l_counter).mtl_transactions_enabled_flag
, l_mtl_system_items_tbl(l_counter).stock_enabled_flag
, l_mtl_system_items_tbl(l_counter).bom_enabled_flag
, l_mtl_system_items_tbl(l_counter).build_in_wip_flag
, l_mtl_system_items_tbl(l_counter).invoice_enabled_flag
, l_mtl_system_items_tbl(l_counter).source_organization_id --myerrams, Bug: 5964347
, l_mtl_system_items_tbl(l_counter).source_subinventory --myerrams, Bug: 5964347
);
SELECT rowid
INTO l_rowid
FROM mtl_system_items_interface
WHERE set_process_id = l_set_process_id
AND inventory_item_id = l_mtl_system_items_tbl(l_counter).inventory_item_id
AND organization_id = p_target_org_id;
UPDATE mtl_system_items_interface set
product_family_item_id = NULL
WHERE set_process_id = l_set_process_id;
INSERT INTO mtl_item_revisions_interface
( inventory_item_id
, organization_id
, revision
, revision_label --myerrams, Bug: 4892069
, implementation_date
, effectivity_date
, process_flag --myerrams, Bug: 4892069
, transaction_type
, set_process_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
)
SELECT msi.inventory_item_id
,p_target_org_id
,mir.REVISION
,mir.revision_label --myerrams, Bug: 4892069
,mir.implementation_date
,mir.effectivity_date
,l_process_flag --myerrams, Bug: 4892069
,'CREATE'
,l_set_process_id
,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
FROM mtl_item_revisions_b mir
,mtl_system_items_interface msi
WHERE mir.inventory_item_id = msi.inventory_item_id
and mir.organization_id = p_source_org_id
and msi.organization_id = p_target_org_id
and msi.set_process_id = l_set_process_id
ORDER BY mir.REVISION,effectivity_date ;
Following code is to update mtl_item_revisions_interface table with Revision Id, which is generated using
the sequence 'MTL_ITEM_REVISIONS_B_S';
,'The Set Process Id that is used to execute the c_item_rev_update_cursor is: '|| l_set_process_id
);
OPEN c_item_rev_update_cursor(l_set_process_id);
FETCH c_item_rev_update_cursor into l_org_id, l_inv_item_id, l_revision;
EXIT WHEN c_item_rev_update_cursor%NOTFOUND;
SELECT MTL_ITEM_REVISIONS_B_S.NEXTVAL into l_revision_id from dual;
UPDATE mtl_item_revisions_interface
SET revision_id = l_revision_id
WHERE ORGANIZATION_ID = l_org_id
AND INVENTORY_ITEM_ID = l_inv_item_id
AND REVISION = l_revision;
CLOSE c_item_rev_update_cursor;
INSERT INTO mtl_item_categories_interface
( inventory_item_id
, organization_id
, CATEGORY_SET_ID
, CATEGORY_ID
, process_flag
, transaction_type
, set_process_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
)
SELECT msi.inventory_item_id
,p_target_org_id
,mic.CATEGORY_SET_ID
,mic.CATEGORY_ID
-- ,l_process_flag --myerrams, Bug: 4892069
,1 --myerrams, Bug: 5624219; ProcessFlag for Item Categories has to be 1 irrespective of validate items option.
( SELECT mdc.category_set_id
FROM mtl_default_category_sets mdc
WHERE mdc.functional_area_id = DECODE( msi.INVENTORY_ITEM_FLAG, 'Y', 1, 0 )
OR mdc.functional_area_id = DECODE( msi.PURCHASING_ITEM_FLAG, 'Y', 2, 0 )
OR mdc.functional_area_id = DECODE( msi.INTERNAL_ORDER_FLAG, 'Y', 2, 0 )
OR mdc.functional_area_id = DECODE( msi.MRP_PLANNING_CODE, 6, 0, 3 )
OR mdc.functional_area_id = DECODE( msi.SERVICEABLE_PRODUCT_FLAG, 'Y', 4, 0 )
OR mdc.functional_area_id = DECODE( msi.COSTING_ENABLED_FLAG, 'Y', 5, 0 )
OR mdc.functional_area_id = DECODE( msi.ENG_ITEM_FLAG, 'Y', 6, 0 )
OR mdc.functional_area_id = DECODE( msi.CUSTOMER_ORDER_FLAG, 'Y', 7, 0 )
OR mdc.functional_area_id = DECODE( NVL(msi.EAM_ITEM_TYPE, 0), 0, 0, 9 )
OR mdc.functional_area_id =
DECODE( msi.CONTRACT_ITEM_TYPE_CODE,
'SERVICE' , 10,
'WARRANTY' , 10,
'SUBSCRIPTION' , 10,
'USAGE' , 10, 0 )
-- These Contract Item types also imply an item belonging to the Service functional area
OR mdc.functional_area_id =
DECODE( msi.CONTRACT_ITEM_TYPE_CODE,
'SERVICE' , 4,
'WARRANTY' , 4, 0 )
OR mdc.functional_area_id = DECODE( msi.INTERNAL_ORDER_FLAG, 'Y', 11, 0 )
OR mdc.functional_area_id = DECODE( msi.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
);