[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Removed hint from follwing Insert stmt for POSCO issue: Bug #1665358.
+ ORDERED USE_NL(CIC CL) INDEX(CL CST_LISTS_U1) INDEX(CIC CST_ITEM_COSTS_U1)
*/
CURSOR cc is
SELECT
CIC.INVENTORY_ITEM_ID
FROM
CST_LISTS CL,
CST_ITEM_COSTS CIC
WHERE CIC.ORGANIZATION_ID = l_organization_id
AND CIC.COST_TYPE_ID = l_cost_type_id
AND CL.LIST_ID = l_list_id
AND CL.ENTITY_ID = CIC.INVENTORY_ITEM_ID
FOR UPDATE OF CIC.LAST_UPDATED_BY NOWAIT;
/* Removed hint from follwing Insert stmt for POSCO issue: Bug #1665358.
+ ORDERED USE_NL(CICD CL) INDEX(CL CST_LISTS_U1) INDEX(CICD CST_ITEM_COST_DETAILS_N1)
*/
CURSOR cd is
SELECT
CICD.INVENTORY_ITEM_ID
FROM
CST_LISTS CL,
CST_ITEM_COST_DETAILS CICD
WHERE CICD.ORGANIZATION_ID = l_organization_id
AND CL.LIST_ID = l_list_id
AND CL.ENTITY_ID = CICD.INVENTORY_ITEM_ID
AND CICD.COST_TYPE_ID = l_cost_type_id
FOR UPDATE OF CICD.LAST_UPDATED_BY NOWAIT;
SELECT
CRC.RESOURCE_ID
FROM
CST_LISTS_TEMP CLT,
CST_RESOURCE_COSTS CRC
WHERE CRC.ORGANIZATION_ID = l_organization_id
AND CLT.LIST_ID = l_list_id
AND CLT.NUMBER_1 = CRC.RESOURCE_ID
AND CRC.COST_TYPE_ID = l_cost_type_id
FOR UPDATE OF CRC.LAST_UPDATED_BY NOWAIT;
SELECT
CDO.DEPARTMENT_ID
FROM
CST_LISTS_TEMP CLT,
CST_DEPARTMENT_OVERHEADS CDO
WHERE CDO.ORGANIZATION_ID = l_organization_id
AND CLT.LIST_ID = l_list_id
AND CLT.NUMBER_1 = CDO.OVERHEAD_ID
AND CDO.COST_TYPE_ID = l_cost_type_id
FOR UPDATE OF CDO.LAST_UPDATED_BY NOWAIT;
SELECT
CRO.RESOURCE_ID
FROM
CST_LISTS_TEMP CLT1,
CST_LISTS_TEMP CLT2,
CST_RESOURCE_OVERHEADS CRO
WHERE CRO.ORGANIZATION_ID = l_organization_id
AND CLT1.LIST_ID = l_list_id
AND CLT1.NUMBER_1 = CRO.RESOURCE_ID
AND CLT2.LIST_ID = l_list_id1
AND CLT2.NUMBER_1 = CRO.OVERHEAD_ID
AND CRO.COST_TYPE_ID = l_cost_type_id
FOR UPDATE OF CRO.LAST_UPDATED_BY NOWAIT;
SELECT inventory_item_id
FROM cst_item_costs cic
WHERE cic.organization_id = l_organization_id
AND cic.cost_type_id = l_cost_type_id;
DELETE CST_ITEM_COSTS cic
WHERE organization_id = l_organization_id
AND cost_type_id = 1
AND inventory_item_id = del_cic_cur.inventory_item_id;
SELECT inventory_item_id
FROM cst_item_costs_temp cict;
DELETE CST_ITEM_COST_DETAILS cicd
WHERE organization_id = l_organization_id
AND cost_type_id = 1
AND inventory_item_id = del_cicd_cur.inventory_item_id;
l_cost_update_id in number,
err_buf out NOCOPY varchar2
)
return integer
is
return_code number;
DELETE FROM cst_std_cost_adj_values V
WHERE v.cost_update_id = l_cost_update_id
AND v.transaction_type = 7 -- resource overhead
AND (V.old_unit_cost = 0 OR V.new_unit_cost = 0)
AND EXISTS
(SELECT 'X'
FROM CST_STD_COST_ADJ_VALUES v1
WHERE v1.cost_update_id = l_cost_update_id
AND v1.transaction_type = 7
AND v1.organization_id = v.organization_id
AND v1.wip_entity_id = v.wip_entity_id
AND v1.operation_seq_num = v.operation_seq_num
AND v1.resource_id = v.resource_id
AND v1.resource_seq_num = v.resource_seq_num
AND v1.basis_type = v.basis_type
AND v1.adjustment_quantity = v.adjustment_quantity
AND (
( v.new_unit_cost = 0
AND v1.old_unit_cost = 0
AND v.old_unit_cost = v1.new_unit_cost
)
OR
( v1.new_unit_cost = 0
AND v.old_unit_cost = 0
AND v1.old_unit_cost = v.new_unit_cost
)
)
AND v1.rowid <> v.rowid
);
l_cost_update_id in number,
err_buf out NOCOPY varchar2
)
return integer
is
return_code number;
SELECT organization_id, wip_entity_id,
operation_seq_num, resource_seq_num
FROM CST_STD_COST_ADJ_VALUES
WHERE cost_update_id = l_cost_update_id
AND transaction_type + 0 = 7;
DELETE CST_STD_COST_ADJ_VALUES
WHERE cost_update_id = l_cost_update_id
AND new_unit_cost = old_unit_cost
AND transaction_type = 6
AND organization_id + 0 = del_c2_cur.organization_id
and wip_entity_id = del_c2_cur.wip_entity_id
AND operation_seq_num = del_c2_cur.operation_seq_num
AND resource_seq_num = del_c2_cur.resource_seq_num;
l_cost_update_id in number,
err_buf out NOCOPY varchar2
)
return integer
is
return_code number;
DELETE cst_std_cost_adj_values V
WHERE V.rowid in
(SELECT VV1.rowidtodel
FROM cst_std_cost_adj_tmp1_v VV1, cst_std_cost_adj_tmp2_v VV2
WHERE VV1.transaction_id = VV2.transaction_id
AND VV1.cost_element_id = VV2.cost_element_id
AND VV1.level_type = VV2.level_type
AND VV1.unit_cost = VV2.unit_cost
AND VV1.new_unit_cost = VV2.old_unit_cost
AND VV1.cost_update_id = l_cost_update_id
AND VV2.cost_update_id = l_cost_update_id);
SELECT CIC1.inventory_item_id
FROM CST_ITEM_COSTS CIC1,
CST_ITEM_COSTS CIC2,
CST_ITEM_LIST_TEMP CLT
WHERE CIC1.organization_id = l_org_id
AND CIC1.cost_type_id = b_cm_frozen_standard
AND CIC1.inventory_item_id = CLT.inventory_item_id
AND CIC2.organization_id (+) = l_org_id
AND CIC2.cost_type_id (+) = l_cost_type_id
AND CIC2.inventory_item_id (+) = CIC1.inventory_item_id
AND ( CIC2.item_cost IS NULL
OR ( CIC1.inventory_asset_flag = CIC2.inventory_asset_flag /* mandatory column */
AND ( CIC1.defaulted_flag = CIC2.defaulted_flag /* mandatory column */
OR CIC1.defaulted_flag = 2)
/* Defaulted flag is always updated to 2 in cmcuuic() */
AND CIC1.item_cost = CIC2.item_cost
AND NVL(CIC1.material_cost, 0) = NVL(CIC2.material_cost, 0)
AND NVL(CIC1.material_overhead_cost, 0) = NVL(CIC2.material_overhead_cost, 0)
AND NVL(CIC1.resource_cost, 0) = NVL(CIC2.resource_cost, 0)
AND NVL(CIC1.outside_processing_cost, 0) = NVL(CIC2.outside_processing_cost, 0)
AND NVL(CIC1.overhead_cost, 0) = NVL(CIC2.overhead_cost, 0)
AND NVL(CIC1.pl_item_cost, 0) = NVL(CIC2.pl_item_cost, 0)
AND NVL(CIC1.pl_material, 0) = NVL(CIC2.pl_material, 0)
AND NVL(CIC1.pl_material_overhead, 0) = NVL(CIC2.pl_material_overhead, 0)
AND NVL(CIC1.pl_resource, 0) = NVL(CIC2.pl_resource, 0)
AND NVL(CIC1.pl_outside_processing, 0) = NVL(CIC2.pl_outside_processing, 0)
AND NVL(CIC1.pl_overhead, 0) = NVL(CIC2.pl_overhead, 0)
AND NVL(CIC1.tl_item_cost, 0) = NVL(CIC2.tl_item_cost, 0)
AND NVL(CIC1.tl_material, 0) = NVL(CIC2.tl_material, 0)
AND NVL(CIC1.tl_material_overhead, 0) = NVL(CIC2.tl_material_overhead, 0)
AND NVL(CIC1.tl_resource, 0) = NVL(CIC2.tl_resource, 0)
AND NVL(CIC1.tl_outside_processing, 0) = NVL(CIC2.tl_outside_processing, 0)
AND NVL(CIC1.tl_overhead, 0) = NVL(CIC2.tl_overhead, 0)
AND NVL(CIC1.lot_size, 1) = NVL(CIC2.lot_size, 1)
AND NVL(CIC1.based_on_rollup_flag, -1) = NVL(CIC2.based_on_rollup_flag, -1)
AND NVL(CIC1.shrinkage_rate, 0) = NVL(CIC2.shrinkage_rate, 0)
AND NVL(CIC1.burden_cost, 0) = NVL(CIC2.burden_cost, 0)
AND NVL(CIC1.unburdened_cost, 0) = NVL(CIC2.unburdened_cost, 0)
AND NVL(CIC1.attribute_category,'-1') = NVL(CIC2.attribute_category, '-1')
AND NVL(CIC1.attribute1, '-1') = NVL(CIC2.attribute1, '-1')
AND NVL(CIC1.attribute2, '-1') = NVL(CIC2.attribute2, '-1')
AND NVL(CIC1.attribute3, '-1') = NVL(CIC2.attribute3, '-1')
AND NVL(CIC1.attribute4, '-1') = NVL(CIC2.attribute4, '-1')
AND NVL(CIC1.attribute5, '-1') = NVL(CIC2.attribute5, '-1')
AND NVL(CIC1.attribute6, '-1') = NVL(CIC2.attribute6, '-1')
AND NVL(CIC1.attribute7, '-1') = NVL(CIC2.attribute7, '-1')
AND NVL(CIC1.attribute8, '-1') = NVL(CIC2.attribute8, '-1')
AND NVL(CIC1.attribute9, '-1') = NVL(CIC2.attribute9, '-1')
AND NVL(CIC1.attribute10, '-1') = NVL(CIC2.attribute10, '-1')
AND NVL(CIC1.attribute11, '-1') = NVL(CIC2.attribute11, '-1')
AND NVL(CIC1.attribute12, '-1') = NVL(CIC2.attribute12, '-1')
AND NVL(CIC1.attribute13, '-1') = NVL(CIC2.attribute13, '-1')
AND NVL(CIC1.attribute14, '-1') = NVL(CIC2.attribute14, '-1')
AND NVL(CIC1.attribute15, '-1') = NVL(CIC2.attribute15, '-1')
/* Added for bug 7338350 for checking CICD Difference */
AND NOT EXISTS( ( SELECT rollup_source_type, level_type,
cost_element_id, Nvl(resource_id, -1),
basis_type, basis_factor,
usage_rate_or_amount, item_cost
FROM CST_ITEM_COST_DETAILS CICD1
WHERE CICD1.organization_id = l_org_id
AND CICD1.cost_type_id = b_cm_frozen_standard
AND CICD1.inventory_item_id = CIC2.inventory_item_id)
MINUS
( SELECT rollup_source_type, level_type,
cost_element_id, Nvl(resource_id, -1),
basis_type, basis_factor,
usage_rate_or_amount, item_cost
FROM CST_ITEM_COST_DETAILS CICD2
WHERE CICD2.organization_id = l_org_id
AND CICD2.cost_type_id = l_cost_type_id
AND CICD2.inventory_item_id = CIC2.inventory_item_id))));
DELETE FROM CST_ITEM_LIST_TEMP CILT
WHERE inventory_item_id=cilt_item_id(i);