The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_old_resource_id NUMBER; -- variable containing old resource_id; populated in Pre_Update hook
FUNCTION Insert_CSP_Sec_Inventory
(
p_resource_id IN NUMBER,
p_subinventory_code IN VARCHAR2,
p_organization_id IN NUMBER
)
RETURN BOOLEAN
IS
CURSOR c_csp_sec_inventories( b_subinventory_code VARCHAR2
, b_organization_id NUMBER
) IS
SELECT csi.secondary_inventory_id
FROM csp_sec_inventories csi
WHERE (csi.SECONDARY_INVENTORY_NAME = b_subinventory_code
AND csi.ORGANIZATION_ID = b_organization_id)
OR ( CONDITION_TYPE = 'B'
-- Bug 3724123
AND csi.ORGANIZATION_ID = JTM_HOOK_UTIL_PKG.Get_Profile_Value('CS_INV_VALIDATION_ORG',0));
, v_message => 'Entering Insert_CSP_Sec_Inventory'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Insert_CSP_Sec_Inventory could not find :' || p_subinventory_code ||
' , ' || p_organization_id || ' for resource id ' || p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
, v_message => 'Inserting ACC record :' || p_subinventory_code || ' , ' || p_organization_id ||
' for resource id ' || p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Call common package to insert record into ACC table ***/
JTM_HOOK_UTIL_PKG.Insert_Acc
( p_publication_item_names => g_publication_item_name
,p_acc_table_name => g_acc_table_name
,p_pk1_name => g_pk1_name
,p_pk1_num_value => r_csp_sec_inventories.SECONDARY_INVENTORY_ID
,p_resource_id => p_resource_id
);
CSL_MTL_SEC_INV_ACC_PKG.Insert_MTL_Sec_Inventory(p_resource_id, p_subinventory_code, p_organization_id);
CSL_MTL_ITEM_LOCATIONS_ACC_PKG.Insert_Item_Locs_By_Subinv
( p_subinventory_code => p_subinventory_code
, p_organization_id => p_organization_id
, p_resource_id => p_resource_id
);
END Insert_CSP_Sec_Inventory;
PROCEDURE Update_CSP_Sec_Inventory
(
p_resource_id IN NUMBER,
p_subinventory_code IN VARCHAR2,
p_organization_id IN NUMBER
)
IS
CURSOR c_csp_sec_inventories( b_subinventory_code VARCHAR2
, b_organization_id NUMBER
) IS
SELECT csi.secondary_inventory_id
FROM csp_sec_inventories csi
WHERE (csi.SECONDARY_INVENTORY_NAME = b_subinventory_code
AND csi.ORGANIZATION_ID = b_organization_id)
OR ( CONDITION_TYPE = 'B'
-- Bug 3724123
AND csi.ORGANIZATION_ID = JTM_HOOK_UTIL_PKG.Get_Profile_Value('CS_INV_VALIDATION_ORG',0));
, v_message => 'Entering Update_CSP_Sec_Inventory'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Update_CSP_Sec_Inventory could not find :' || p_subinventory_code || ' , '
|| p_organization_id || ' for resource id ' || p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
, v_message => 'Update ACC record :' || p_subinventory_code || ' , ' || p_organization_id
|| ' for resource id ' || p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Call common package to insert record into ACC table ***/
l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id
( P_ACC_TABLE_NAME => g_acc_table_name
,P_PK1_NAME => g_pk1_name
,P_PK1_NUM_VALUE => r_csp_sec_inventories.SECONDARY_INVENTORY_ID
,P_RESOURCE_ID => p_resource_id);
/*** Record is not yet in ACC tables. Insert has to be done ***/
JTM_HOOK_UTIL_PKG.Insert_Acc
( p_publication_item_names => g_publication_item_name
,p_acc_table_name => g_acc_table_name
,p_pk1_name => g_pk1_name
,p_pk1_num_value => r_csp_sec_inventories.SECONDARY_INVENTORY_ID
,p_resource_id => p_resource_id
);
CSL_MTL_SEC_INV_ACC_PKG.Insert_MTL_Sec_Inventory(p_resource_id, p_subinventory_code, p_organization_id);
CSL_MTL_ITEM_LOCATIONS_ACC_PKG.Insert_Item_Locs_By_Subinv
( p_subinventory_code => p_subinventory_code
, p_organization_id => p_organization_id
, p_resource_id => p_resource_id
);
/*** Record is already in ACC. Only an update is required for re-sending ***/
JTM_HOOK_UTIL_PKG.Update_Acc
( p_publication_item_names => g_publication_item_name
,p_acc_table_name => g_acc_table_name
,p_resource_id => p_resource_id
,p_access_id => l_acc_id
);
CSL_MTL_SEC_INV_ACC_PKG.Update_MTL_Sec_Inventory(p_resource_id, p_subinventory_code, p_organization_id);
, v_message => 'Leaving Update_CSP_Sec_Inventory'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Update_CSP_Sec_Inventory;
FUNCTION Delete_CSP_Sec_Inventory
(
p_resource_id IN NUMBER,
p_subinventory_code IN VARCHAR2,
p_organization_id IN NUMBER
)
RETURN BOOLEAN
IS
CURSOR c_csp_sec_inventories( b_subinventory_code VARCHAR2
, b_organization_id NUMBER
) IS
SELECT csi.secondary_inventory_id
FROM csp_sec_inventories csi
WHERE (csi.SECONDARY_INVENTORY_NAME = b_subinventory_code
AND csi.ORGANIZATION_ID = b_organization_id)
OR ( CONDITION_TYPE = 'B'
-- Bug 3724123
AND csi.ORGANIZATION_ID = JTM_HOOK_UTIL_PKG.Get_Profile_Value('CS_INV_VALIDATION_ORG',0));
, v_message => 'Entering Delete_CSP_Sec_Inventory'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Delete_CSP_Sec_Inventory could not find :' || p_subinventory_code || ' , '
|| p_organization_id || ' for resource id ' || p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
/*** Call common package to insert record into ACC table ***/
JTM_HOOK_UTIL_PKG.Delete_Acc
( p_publication_item_names => g_publication_item_name
,p_acc_table_name => g_acc_table_name
,p_pk1_name => g_pk1_name
,p_pk1_num_value => r_csp_sec_inventories.SECONDARY_INVENTORY_ID
,p_resource_id => p_resource_id
);
CSL_MTL_SEC_INV_ACC_PKG.Delete_MTL_Sec_Inventory(p_resource_id, p_subinventory_code, p_organization_id);
CSL_MTL_ITEM_LOCATIONS_ACC_PKG.Delete_Item_Locs_By_Subinv
( p_subinventory_code => p_subinventory_code
, p_organization_id => p_organization_id
, p_resource_id => p_resource_id
);
END Delete_CSP_Sec_Inventory;