[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Bug 5443502: added cost_element_id in the select */
select distinct cicdi.resource_code, cicdi.cost_element_id
from cst_item_cst_dtls_interface cicdi
where cicdi.group_id = p_group_id
and cicdi.resource_code is not null
UNION
select distinct basis_resource_code, cost_element_id
from cst_item_cst_dtls_interface
where group_id = p_group_id
and basis_resource_code is not null;
select distinct department
from cst_item_cst_dtls_interface
where group_id = p_group_id
and department is not null;
select distinct activity
from cst_item_cst_dtls_interface
where group_id = p_group_id
and activity is not null;
select count(*)
into l_exists
from bom_resources
where resource_code = subelement_rec.resource_code
and cost_element_id = subelement_rec.cost_element_id
and organization_id = p_to_organization_id;
select resource_id
into l_current_rec.ID
from bom_resources
where organization_id = p_from_organization_id
and resource_code = l_current_rec.code;
select count(*)
into l_exists
from bom_departments
where department_code = department_rec.department
and organization_id = p_to_organization_id;
select department_id
into l_current_rec.ID
from bom_departments
where organization_id = p_from_organization_id
and department_code = l_current_rec.code;
select count(*)
into l_exists
from cst_activities
where activity = activity_rec.activity
and nvl(organization_id,p_to_organization_id) = p_to_organization_id;
select activity_id
into l_current_rec.ID
from cst_activities
where organization_id = p_from_organization_id
and activity = l_current_rec.code;
SELECT MAX(activity)
INTO l_activity
FROM cst_activities ca
WHERE ca.activity_id = l_source_activity_id
AND ca.organization_id = p_from_organization_id;
INSERT INTO cst_activities
(
activity_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
activity,
organization_id,
description,
default_basis_type,
disable_date,
output_uom,
value_added_activity_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT
cst_activities_s.nextval,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
activity,
p_to_organization_id,
description,
default_basis_type,
disable_date,
output_uom,
value_added_activity_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
l_request_id,
l_prog_app_id,
l_prog_id,
SYSDATE
FROM cst_activities ca
WHERE ca.activity_id = l_source_activity_id
AND ca.organization_id = p_from_organization_id
AND NOT EXISTS ( SELECT 'X'
FROM cst_activities ca2
WHERE ca2.organization_id = p_to_organization_id
AND ca2.activity = l_activity) ;
SELECT COUNT(*)
INTO l_to_wsm_flag
FROM mtl_parameters mp, wsm_parameters wsm
WHERE wsm.organization_id = p_to_organization_id
AND mp.organization_id = wsm.organization_id
AND UPPER(mp.wsm_enabled_flag)='Y';
SELECT scrap_account,
est_absorption_account
INTO l_scrap_acct,
l_est_abs_acct
FROM bom_departments bd
WHERE bd.department_id = l_source_department_id;
SELECT department_class_code,
department_code
INTO l_department_class_code,
l_department_code
FROM bom_departments bd
WHERE bd.organization_id = p_from_organization_id
AND bd.department_id = l_source_department_id;
INSERT INTO bom_department_classes
(
department_class_code,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT
department_class_code,
p_to_organization_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
l_request_id,
l_prog_app_id,
l_prog_id,
SYSDATE
FROM bom_department_classes bdc
WHERE bdc.organization_id = p_from_organization_id
AND bdc.department_class_code = l_department_class_code
AND NOT EXISTS
( SELECT 'X'
FROM bom_department_classes bdc2
WHERE bdc2.organization_id = p_to_organization_id
AND bdc2.department_class_code = l_department_class_code);
INSERT INTO bom_departments
(
department_id,
department_code,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description,
disable_date,
department_class_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date,
location_id,
pa_expenditure_org_id,
scrap_account,
est_absorption_account
)
SELECT
bom_departments_s.nextval,
department_code,
p_to_organization_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
description,
disable_date,
department_class_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
l_request_id,
l_prog_app_id,
l_prog_id,
SYSDATE,
location_id,
pa_expenditure_org_id,
decode(l_to_wsm_flag, 1, l_scrap_acct, NULL),
decode(l_to_wsm_flag, 1, l_est_abs_acct, NULL)
FROM bom_departments
WHERE organization_id = p_from_organization_id
AND department_id = l_source_department_id
AND NOT EXISTS (SELECT 'X'
FROM bom_departments bd2
WHERE bd2.organization_id = p_to_organization_id
AND bd2.department_code = l_department_code);
SELECT decode(project_reference_enabled,1,
decode(pm_cost_collection_enabled,1,1,0),0)
INTO l_exp_type_required
FROM mtl_parameters
WHERE organization_id = p_to_organization_id;
SELECT SubStr(currency_code,1,3)
INTO l_func_currency_uom
FROM gl_sets_of_books gsob,
hr_organization_information hoi
WHERE hoi.organization_id = p_to_organization_id
AND hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
AND gsob.set_of_books_id = hoi.org_information1 ;
SELECT cost_element_id,
purchase_item_id,
functional_currency_flag,
default_activity_id,
expenditure_type,
absorption_account,
rate_variance_account,
resource_code
INTO l_cost_element_id,
l_purchase_item_id,
l_func_curr_flag,
l_default_activity_id,
l_expenditure_type,
l_absorption_acct,
l_rate_variance_acct,
l_resource_code
FROM bom_resources br
WHERE br.organization_id = p_from_organization_id
AND br.resource_id = l_source_resource_id;
SELECT MAX(msi.inventory_item_id)
INTO l_dummy
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_purchase_item_id
AND msi.organization_id = p_to_organization_id;
SELECT MAX(activity_id)
INTO l_dummy
FROM cst_activities ca
WHERE ca.activity =
( SELECT ca2.activity
FROM cst_activities ca2
WHERE ca2.activity_id = l_default_activity_id)
AND (ca.organization_id = p_to_organization_id OR
ca.organization_id IS NULL);
INSERT INTO bom_resources
(
resource_id,
resource_code,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description,
disable_date,
cost_element_id,
purchase_item_id,
cost_code_type,
functional_currency_flag,
unit_of_measure,
default_activity_id,
resource_type,
autocharge_type,
standard_rate_flag,
default_basis_type,
absorption_account,
allow_costs_flag,
rate_variance_account,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date,
expenditure_type
)
SELECT
bom_resources_s.nextval,
resource_code,
p_to_organization_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
description,
disable_date,
cost_element_id,
l_purchase_item_id,
cost_code_type,
functional_currency_flag,
decode(cost_element_id, /* Bug 4360688: Stamp target organization's functional currency for overheads */
2, l_func_currency_UOM,
5, l_func_currency_UOM,
Decode(functional_currency_flag,1,l_func_currency_uom,
unit_of_measure)),
l_default_activity_id,
resource_type,
autocharge_type,
standard_rate_flag,
default_basis_type,
l_absorption_acct,
allow_costs_flag,
l_rate_variance_acct,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
l_request_id,
l_prog_app_id,
l_prog_id,
SYSDATE,
l_expenditure_type
FROM bom_resources br
WHERE br.resource_id = l_source_resource_id
AND br.organization_id = p_from_organization_id
AND NOT EXISTS (SELECT 'X'
FROM bom_resources br2
WHERE br2.organization_id = p_to_organization_id
AND br2.resource_code = l_resource_code);
SELECT mp.expense_account,
mp.expense_account
INTO x_scrap_account,
x_est_absorption_account
FROM mtl_parameters mp
WHERE mp.organization_id = p_to_organization_id;
SELECT MAX(expenditure_type)
INTO x_expenditure_type
FROM cst_proj_exp_types_val_v
WHERE cost_element_id =
( SELECT br.cost_element_id
FROM bom_resources br
WHERE br.resource_id = p_resource_id);
SELECT decode(br.cost_element_id,
2, mp.material_overhead_account,
3, mp.resource_account,
4, mp.outside_processing_account,
5, overhead_account,
mp.expense_account),
mp.expense_account
INTO x_absorption_account,
x_rate_variance_account
FROM mtl_parameters mp,
bom_resources br
WHERE mp.organization_id = p_to_organization_id
AND br.resource_id = p_resource_id;
SELECT BR.RESOURCE_CODE,
CA.ACTIVITY
INTO l_material_subelement_code,
l_mat_activity
FROM BOM_RESOURCES BR,
CST_ACTIVITIES CA
WHERE BR.RESOURCE_ID = p_material_subelement
AND BR.DEFAULT_ACTIVITY_ID = CA.ACTIVITY_ID (+);
SELECT BR.RESOURCE_CODE,
CA.ACTIVITY
INTO l_moh_subelement_code,
l_moh_activity
FROM BOM_RESOURCES BR,
CST_ACTIVITIES CA
WHERE BR.RESOURCE_ID = p_moh_subelement
AND BR.DEFAULT_ACTIVITY_ID = CA.ACTIVITY_ID (+);
SELECT BR.RESOURCE_CODE,
CA.ACTIVITY
INTO l_resource_subelement_code,
l_res_activity
FROM BOM_RESOURCES BR,
CST_ACTIVITIES CA
WHERE BR.RESOURCE_ID = p_resource_subelement
AND BR.DEFAULT_ACTIVITY_ID = CA.ACTIVITY_ID (+);
SELECT BR.RESOURCE_CODE,
CA.ACTIVITY
INTO l_osp_subelement_code,
l_osp_activity
FROM BOM_RESOURCES BR,
CST_ACTIVITIES CA
WHERE BR.RESOURCE_ID = p_osp_subelement
AND BR.DEFAULT_ACTIVITY_ID = CA.ACTIVITY_ID (+);
SELECT BR.RESOURCE_CODE,
CA.ACTIVITY
INTO l_overhead_subelement_code,
l_ovh_activity
FROM BOM_RESOURCES BR,
CST_ACTIVITIES CA
WHERE BR.RESOURCE_ID = p_overhead_subelement
AND BR.DEFAULT_ACTIVITY_ID = CA.ACTIVITY_ID (+);
UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
SET
operation_sequence_id = NULL, --operation_sequence_id,
operation_seq_num = NULL, --operation_seq_num,
department_id = NULL, --department_id,
activity_id = NULL, --activity_id,
resource_seq_num = NULL, --resource_seq_num,
resource_id = -1, --resource_id,
resource_rate = 1, -- resource_rate
usage_rate_or_amount = CICDI.item_cost,
-- usage_rate_or_amount
basis_type = 1, --basis_type, -- Always Item Based
basis_resource_id = NULL, --basis_resource_id,
basis_factor = 1, --basis_factor, -- Always Item Based
item_cost = CICDI.item_cost,
-- item cost
rollup_source_type = 1, -- rollup_source_type = Always user-defined
activity_context = NULL, --activity_context,
department = NULL, -- department
activity = NULL, -- activity
resource_code = '-1', -- resource_code
basis_resource_code = NULL -- basis_resource_code
WHERE CICDI.group_id = p_group_id
AND CICDI.resource_code = l_resource_code;
UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
SET
operation_sequence_id = NULL, --operation_sequence_id,
operation_seq_num = NULL, --operation_seq_num,
department_id = NULL, --department_id,
activity_id = NULL, --activity_id,
resource_seq_num = NULL, --resource_seq_num,
resource_id = -1, --resource_id,
resource_rate = 1, -- resource_rate
usage_rate_or_amount = CICDI.item_cost,
-- usage_rate_or_amount
basis_type = 1, --basis_type, -- Always Item Based
basis_resource_id = NULL, --basis_resource_id,
basis_factor = 1, --basis_factor, -- Always Item Based
item_cost = CICDI.item_cost,
-- item cost
rollup_source_type = 1, -- rollup_source_type = Always user-defined
activity_context = NULL, --activity_context,
department = NULL, -- department
activity = NULL, -- activity
resource_code = '-1', -- resource_code
basis_resource_code = NULL -- basis_resource_code
WHERE CICDI.group_id = p_group_id
AND CICDI.department = l_dept_code
AND nvl(CICDI.resource_code,'0') <> '-1';
UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
SET
operation_sequence_id = NULL, --operation_sequence_id,
operation_seq_num = NULL, --operation_seq_num,
department_id = NULL, --department_id,
activity_id = NULL, --activity_id,
resource_seq_num = NULL, --resource_seq_num,
resource_id = -1, --resource_id,
resource_rate = 1, -- resource_rate
usage_rate_or_amount = CICDI.item_cost,
-- usage_rate_or_amount
basis_type = 1, --basis_type, -- Always Item Based
basis_resource_id = NULL, --basis_resource_id,
basis_factor = 1, --basis_factor, -- Always Item Based
item_cost = CICDI.item_cost,
-- item cost
rollup_source_type = 1, -- rollup_source_type = Always user-defined
activity_context = NULL, --activity_context,
department = NULL, -- department
activity = NULL, -- activity
resource_code = '-1', -- resource_code
basis_resource_code = NULL -- basis_resource_code
WHERE CICDI.group_id = p_group_id
AND CICDI.activity = l_activity_code
AND nvl(CICDI.resource_code,'0') <> '-1';
SELECT primary_cost_method
INTO l_primary_cost_method
FROM MTL_PARAMETERS MP
WHERE MP.organization_id = p_to_organization_id;
UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
SET
operation_sequence_id = NULL, --operation_sequence_id,
operation_seq_num = NULL, --operation_seq_num,
department_id = NULL, --department_id,
activity_id = NULL, --activity_id,
resource_seq_num = NULL, --resource_seq_num,
resource_id = -1, --resource_id,
resource_rate = 1, -- resource_rate
usage_rate_or_amount = CICDI.item_cost,
-- usage_rate_or_amount
basis_type = 1, --basis_type, -- Always Item Based
basis_resource_id = NULL, --basis_resource_id,
basis_factor = 1, --basis_factor, -- Always Item Based
item_cost = CICDI.item_cost,
-- item cost
rollup_source_type = 1, -- rollup_source_type = Always user-defined
activity_context = NULL, --activity_context,
department = NULL, -- department
activity = NULL, -- activity
resource_code = '-1', -- resource_code
basis_resource_code = NULL -- basis_resource_code
WHERE CICDI.group_id = p_group_id
AND CICDI.resource_id IS NULL
AND CICDI.resource_code IS NULL;
UPDATE CST_ITEM_CST_DTLS_INTERFACE CICDI
SET
operation_sequence_id = NULL, --operation_sequence_id,
operation_seq_num = NULL, --operation_seq_num,
department_id = NULL, --department_id,
activity_id = NULL, --activity_id,
resource_seq_num = NULL, --resource_seq_num,
resource_id = -1, --resource_id,
resource_rate = 1, -- resource_rate
usage_rate_or_amount = CICDI.item_cost,
-- usage_rate_or_amount
basis_type = 1, --basis_type, -- Always Item Based
basis_resource_id = NULL, --basis_resource_id,
basis_factor = 1, --basis_factor, -- Always Item Based
item_cost = CICDI.item_cost,
-- item cost
rollup_source_type = 1, -- rollup_source_type = Always user-defined
activity_context = NULL, --activity_context,
department = NULL, -- department
activity = NULL, -- activity
resource_code = '-1', -- resource_code
basis_resource_code = NULL -- basis_resource_code
WHERE CICDI.group_id = p_group_id;
INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
(
INVENTORY_ITEM_ID, -- NUMBER
COST_TYPE_ID, -- NOT NULL NUMBER
LAST_UPDATE_DATE, -- DATE
LAST_UPDATED_BY, -- NUMBER
CREATION_DATE, -- DATE
CREATED_BY, -- NUMBER
LAST_UPDATE_LOGIN, -- NUMBER
GROUP_ID, -- NUMBER
ORGANIZATION_ID, -- NUMBER
OPERATION_SEQUENCE_ID, -- NUMBER
OPERATION_SEQ_NUM, -- NUMBER
DEPARTMENT_ID, -- NUMBER
LEVEL_TYPE, -- NUMBER
ACTIVITY_ID, -- NUMBER
RESOURCE_SEQ_NUM, -- NUMBER
RESOURCE_ID, -- NUMBER
RESOURCE_RATE, -- NUMBER
ITEM_UNITS, -- NUMBER
ACTIVITY_UNITS, -- NUMBER
USAGE_RATE_OR_AMOUNT, -- NUMBER
BASIS_TYPE, -- NUMBER
BASIS_RESOURCE_ID, -- NUMBER
BASIS_FACTOR, -- NUMBER
NET_YIELD_OR_SHRINKAGE_FACTOR, -- NUMBER
ITEM_COST, -- NUMBER
COST_ELEMENT_ID, -- NUMBER
ROLLUP_SOURCE_TYPE, -- NUMBER
ACTIVITY_CONTEXT, -- VARCHAR2(30)
REQUEST_ID, -- NUMBER
ORGANIZATION_CODE, -- VARCHAR2(3)
COST_TYPE, -- VARCHAR2(10)
INVENTORY_ITEM, -- VARCHAR2(240)
DEPARTMENT, -- VARCHAR2(10)
ACTIVITY, -- VARCHAR2(10)
RESOURCE_CODE, -- VARCHAR2(10)
BASIS_RESOURCE_CODE, -- VARCHAR2(10)
COST_ELEMENT, -- VARCHAR2(50)
ERROR_TYPE, -- NUMBER
PROGRAM_APPLICATION_ID , -- NUMBER
PROGRAM_ID, -- NUMBER
PROGRAM_UPDATE_DATE, -- DATE
ATTRIBUTE_CATEGORY, -- VARCHAR2(30)
ATTRIBUTE1, -- VARCHAR2(150)
ATTRIBUTE2, -- VARCHAR2(150)
ATTRIBUTE3, -- VARCHAR2(150)
ATTRIBUTE4, -- VARCHAR2(150)
ATTRIBUTE5, -- VARCHAR2(150)
ATTRIBUTE6, -- VARCHAR2(150)
ATTRIBUTE7, -- VARCHAR2(150)
ATTRIBUTE8, -- VARCHAR2(150)
ATTRIBUTE9, -- VARCHAR2(150)
ATTRIBUTE10, -- VARCHAR2(150)
ATTRIBUTE11, -- VARCHAR2(150)
ATTRIBUTE12, -- VARCHAR2(150)
ATTRIBUTE13, -- VARCHAR2(150)
ATTRIBUTE14, -- VARCHAR2(150)
ATTRIBUTE15, -- VARCHAR2(150)
TRANSACTION_ID, -- NUMBER
PROCESS_FLAG, -- NUMBER
ITEM_NUMBER, -- VARCHAR2(81)
TRANSACTION_TYPE, -- VARCHAR2(10)
YIELDED_COST -- NUMBER
)
SELECT CICDI2.INVENTORY_ITEM_ID,
p_to_cost_type_id, -- COST_TYPE_ID
SYSDATE, -- LAST_UPDATE_DATE
FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
SYSDATE, -- CREATION_DATE
FND_GLOBAL.USER_ID, -- CREATED_BY
FND_GLOBAL.LOGIN_ID, -- LAST_UPDATE_LOGIN
p_group_id, -- GROUP_ID
NULL, -- ORGANIZATION_ID
NULL, -- OPERATION_SEQUENCE_ID
NULL, -- OPERATION_SEQ_NUM,
NULL, -- DEPARTMENT_ID,
CICDI2.LEVEL_TYPE, --
NULL, -- ACTIVITY_ID
NULL, -- RESOURCE_SEQ_NUM
NULL, -- RESOURCE_ID
1, -- RESOURCE_RATE
NULL, -- ITEM_UNITS
NULL, -- ACTIVITY_UNITS
SUM(USAGE_RATE_OR_AMOUNT),
1, -- BASIS_TYPE
NULL, -- BASIS_RESOURCE_ID
1, -- BASIS_FACTOR
1, -- NET_YIELD_OR_SHRINKAGE_FACTOR
SUM(ITEM_COST),
CICDI2.COST_ELEMENT_ID,
1, -- ROLLUP_SOURCE_TYPE
NULL, -- ACTIVITY_CONTEXT
FND_GLOBAL.CONC_REQUEST_ID, -- REQUEST_ID
CICDI2.ORGANIZATION_CODE,
CICDI2.COST_TYPE,
CICDI2.INVENTORY_ITEM,
NULL, -- DEPARTMENT
NULL, -- ACTIVITY
DECODE (CICDI2.COST_ELEMENT_ID,
1, NVL(l_material_subelement_code,'-1'),
2, NVL(l_moh_subelement_code,'-1'),
3, NVL(l_resource_subelement_code,'-1'),
4, NVL(l_osp_subelement_code,'-1'),
5, NVL(l_overhead_subelement_code,'-1')),
-- RESOURCE_CODE
NULL, -- BASIS_RESOURCE_CODE
CICDI2.COST_ELEMENT,
NULL, -- ERROR_TYPE
FND_GLOBAL.PROG_APPL_ID, --PROGRAM_APPLICATION_ID
FND_GLOBAL.CONC_PROGRAM_ID, -- PROGRAM_ID
SYSDATE, -- PROGRAM_UPDATE_DATE
NULL, -- ATTRIBUTE_CATEGORY
NULL, -- ATTRIBUTE1
NULL, -- ATTRIBUTE2
NULL, -- ATTRIBUTE3
NULL, -- ATTRIBUTE4
NULL, -- ATTRIBUTE5
NULL, -- ATTRIBUTE6
NULL, -- ATTRIBUTE7
NULL, -- ATTRIBUTE8
NULL, -- ATTRIBUTE9
NULL, -- ATTRIBUTE10
NULL, -- ATTRIBUTE11
NULL, -- ATTRIBUTE12
NULL, -- ATTRIBUTE13
NULL, -- ATTRIBUTE14
NULL, -- ATTRIBUTE15
NULL, -- TRANSACTION_ID
NULL, -- PROCESS_FLAG
NULL, -- ITEM_NUMBER
NULL, -- TRANSACTION_TYPE
SUM(CICDI2.YIELDED_COST)
FROM CST_ITEM_CST_DTLS_INTERFACE CICDI2
WHERE CICDI2.GROUP_ID = p_group_id
AND CICDI2.RESOURCE_CODE = '-1'
AND CICDI2.RESOURCE_ID = -1
GROUP BY
CICDI2.GROUP_ID,
CICDI2.COST_ELEMENT,
CICDI2.LEVEL_TYPE,
CICDI2.ORGANIZATION_CODE,
CICDI2.COST_TYPE,
CICDI2.INVENTORY_ITEM,
CICDI2.INVENTORY_ITEM_ID,
CICDI2.COST_ELEMENT_ID;
DELETE CST_ITEM_CST_DTLS_INTERFACE CICDI
WHERE CICDI.RESOURCE_CODE = '-1'
AND CICDI.RESOURCE_ID = -1
AND CICDI.GROUP_ID = p_group_id;
SELECT COUNT(*)
INTO l_miss_def_subelem
FROM CST_ITEM_CST_DTLS_INTERFACE CICDI
WHERE CICDI.RESOURCE_CODE = '-1'
AND CICDI.RESOURCE_ID IS NULL
AND CICDI.GROUP_ID = p_group_id;