[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
log_msg('Inserting Burden Details for Item ' || p_header_rec.inventory_item_id ||
' organization_id ' || p_header_rec.organization_id || ' organization_code ' || p_header_rec.organization_code ||
' Calendar ' || p_header_rec.calendar_code || ' Period ' || p_header_rec.Period_code ||
' Mthd ' || p_header_rec.cost_mthd_code ) ;
SELECT gem5_burdenline_id_S.NEXTVAL
INTO l_burdenline_id
FROM DUAL ;
INSERT INTO cm_brdn_dtl
(
burdenline_id
, resources
, cost_cmpntcls_id
, cost_analysis_code
, burden_usage
, item_qty
, burden_qty
, burden_factor
, rollover_ind
, cmpntcost_id
, trans_cnt
, delete_mark
, text_code
, created_by
, creation_date
, last_updated_by
, last_update_login
, last_update_date
, request_id
, program_application_id
, program_id
, program_update_date
, organization_id
, inventory_item_id
, period_id
, cost_type_id
, item_uom
, burden_uom
)
VALUES
(
l_burdenline_id
, p_dtl_tbl(i).resources
, p_dtl_tbl(i).cost_cmpntcls_id
, p_dtl_tbl(i).cost_analysis_code
, p_dtl_tbl(i).burden_usage
, p_dtl_tbl(i).item_qty
, p_dtl_tbl(i).burden_qty
, p_burden_factor_tbl(i).burden_factor
, 0 -- rollover indicator
, '' -- Component Cost Id
, '' -- trans cnt
, 0 --delete mark
, '' -- text code
, p_user_id
, sysdate
, p_user_id
, FND_GLOBAL.LOGIN_ID
, sysdate
, '' -- request_id
, '' -- program_application_id
, '' -- program_id
, '' -- program_update_date
, p_header_rec.organization_id
, p_header_rec.inventory_item_id
, p_header_rec.period_id
, p_header_rec.cost_type_id
, p_dtl_tbl(i).item_uom
, p_dtl_tbl(i).burden_uom
);
log_msg( '1 row inserted for Resource ' || p_dtl_tbl(i).resources ||
' Cmptcls Id ' || p_dtl_tbl(i).cost_cmpntcls_id || ' Alys Code ' || p_dtl_tbl(i).cost_analysis_code ||
' Burdenline Id ' || l_burdenline_id);
x_burdenline_ids.delete ;
FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_INSERT_FAILED');
PROCEDURE Update_Burden_Details
(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY VARCHAR2 ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_header_rec IN GMF_BurdenDetails_PUB.Burden_Header_Rec_Type ,
p_dtl_tbl IN GMF_BurdenDetails_PUB.Burden_Dtl_Tbl_Type ,
p_user_id IN fnd_user.user_id%TYPE ,
p_burden_factor_tbl IN Burden_factor_Tbl_Type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Burden_Details' ;
SAVEPOINT Update_Burden_Details_PVT;
log_msg('Beginning Update Burden Details process.');
IF p_dtl_tbl(i).delete_mark = 0 THEN -- Update
UPDATE cm_brdn_dtl
SET
/* Bug 2659435: key columns should not be changed.
resources = decode(p_dtl_tbl(i).resources
, FND_API.G_MISS_CHAR, NULL
, NULL, resources
, p_dtl_tbl(i).resources )
, cost_cmpntcls_id = decode(p_dtl_tbl(i).cost_cmpntcls_id
, FND_API.G_MISS_NUM, NULL
, NULL, cost_cmpntcls_id
, p_dtl_tbl(i).cost_cmpntcls_id )
, cost_analysis_code= decode(p_dtl_tbl(i).cost_analysis_code
, FND_API.G_MISS_CHAR, NULL
, NULL, cost_analysis_code
, p_dtl_tbl(i).cost_analysis_code )
*/
burden_usage = decode(p_dtl_tbl(i).burden_usage
, FND_API.G_MISS_NUM, NULL
, NULL, burden_usage
, p_dtl_tbl(i).burden_usage )
, item_qty = decode(p_dtl_tbl(i).item_qty
, FND_API.G_MISS_NUM, NULL
, NULL, item_qty
, p_dtl_tbl(i).item_qty )
, item_uom = decode(p_dtl_tbl(i).item_uom
, FND_API.G_MISS_CHAR, NULL
, NULL, item_uom
, p_dtl_tbl(i).item_uom )
, burden_qty = decode(p_dtl_tbl(i).burden_qty
, FND_API.G_MISS_NUM, NULL
, NULL, burden_qty
, p_dtl_tbl(i).burden_qty )
, burden_uom = decode(p_dtl_tbl(i).burden_uom
, FND_API.G_MISS_CHAR, NULL
, NULL, burden_uom
, p_dtl_tbl(i).burden_uom )
, burden_factor = decode(p_burden_factor_tbl(i).burden_factor
, FND_API.G_MISS_NUM, NULL
, NULL, burden_factor
, p_burden_factor_tbl(i).burden_factor )
, delete_mark = 0
-- , creation_date = sysdate -- Bug 2722404
-- , created_by = p_user_id
, last_update_date = sysdate
, last_updated_by = p_user_id
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE
burdenline_id = p_dtl_tbl(i).burdenline_id
;
ELSE -- delete the record i.e mark for purge
UPDATE cm_brdn_dtl
SET
delete_mark = 1
-- , creation_date = sysdate -- Bug 2722404
-- , created_by = p_user_id
, last_update_date = sysdate
, last_updated_by = p_user_id
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE
burdenline_id = p_dtl_tbl(i).burdenline_id
;
IF p_dtl_tbl(i).delete_mark = 0 THEN
FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_NOT_FOUND_FOR_ID');
IF p_dtl_tbl(i).delete_mark = 0 THEN
IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
log_msg( '1 row updated for Burdenline Id ' || p_dtl_tbl(i).burdenline_id);
log_msg( '1 row deleted for Burdenline Id ' || p_dtl_tbl(i).burdenline_id);
IF p_dtl_tbl(i).delete_mark = 0 THEN -- Update
UPDATE cm_brdn_dtl
SET
/* Bug 2659435: key columns should not be changed.
resources = decode(p_dtl_tbl(i).resources
, FND_API.G_MISS_CHAR, NULL
, NULL, resources
, p_dtl_tbl(i).resources )
, cost_cmpntcls_id = decode(p_dtl_tbl(i).cost_cmpntcls_id
, FND_API.G_MISS_NUM, NULL
, NULL, cost_cmpntcls_id
, p_dtl_tbl(i).cost_cmpntcls_id )
, cost_analysis_code= decode(p_dtl_tbl(i).cost_analysis_code
, FND_API.G_MISS_CHAR, NULL
, NULL, cost_analysis_code
, p_dtl_tbl(i).cost_analysis_code )
*/
burden_usage = decode(p_dtl_tbl(i).burden_usage
, FND_API.G_MISS_NUM, NULL
, NULL, burden_usage
, p_dtl_tbl(i).burden_usage )
, item_qty = decode(p_dtl_tbl(i).item_qty
, FND_API.G_MISS_NUM, NULL
, NULL, item_qty
, p_dtl_tbl(i).item_qty )
, item_uom = decode(p_dtl_tbl(i).item_uom
, FND_API.G_MISS_CHAR, NULL
, NULL, item_uom
, p_dtl_tbl(i).item_uom )
, burden_qty = decode(p_dtl_tbl(i).burden_qty
, FND_API.G_MISS_NUM, NULL
, NULL, burden_qty
, p_dtl_tbl(i).burden_qty )
, burden_uom = decode(p_dtl_tbl(i).burden_uom
, FND_API.G_MISS_CHAR, NULL
, NULL, burden_uom
, p_dtl_tbl(i).burden_uom )
, burden_factor = decode(p_burden_factor_tbl(i).burden_factor
, FND_API.G_MISS_NUM, NULL
, NULL, burden_factor
, p_burden_factor_tbl(i).burden_factor )
, delete_mark = 0
-- , creation_date = sysdate -- Bug 2722404
-- , created_by = p_user_id
, last_update_date = sysdate
, last_updated_by = p_user_id
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE
organization_id = p_header_rec.organization_id
AND inventory_item_id = p_header_rec.inventory_item_id
AND period_id = p_header_rec.period_id
AND cost_type_id = p_header_rec.cost_type_id
AND resources = p_dtl_tbl(i).resources
AND cost_cmpntcls_id = p_dtl_tbl(i).cost_cmpntcls_id
AND cost_analysis_code = p_dtl_tbl(i).cost_analysis_code
;
ELSE -- delete the record i.e mark for purge
UPDATE cm_brdn_dtl
SET
delete_mark = 1
-- , creation_date = sysdate -- Bug 2722404
-- , created_by = p_user_id
, last_update_date = sysdate
, last_updated_by = p_user_id
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE
organization_id = p_header_rec.organization_id
AND inventory_item_id = p_header_rec.inventory_item_id
AND period_id = p_header_rec.period_id
AND cost_type_id = p_header_rec.cost_type_id
AND resources = p_dtl_tbl(i).resources
AND cost_cmpntcls_id = p_dtl_tbl(i).cost_cmpntcls_id
AND cost_analysis_code = p_dtl_tbl(i).cost_analysis_code
;
IF p_dtl_tbl(i).delete_mark = 0 THEN
FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_NOT_FOUND_FOR_DTL');
IF p_dtl_tbl(i).delete_mark = 0 THEN
IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN -- Bug 2659435
log_msg( '1 row updated for Resource ' || p_dtl_tbl(i).resources ||
' Cmptcls Id ' || p_dtl_tbl(i).cost_cmpntcls_id || ' Alys Code ' || p_dtl_tbl(i).cost_analysis_code);
log_msg( '1 row deleted for Resource ' || p_dtl_tbl(i).resources ||
' Cmptcls Id ' || p_dtl_tbl(i).cost_cmpntcls_id || ' Alys Code ' || p_dtl_tbl(i).cost_analysis_code);
IF p_dtl_tbl(i).delete_mark = 0 THEN -- Update
IF (p_dtl_tbl(i).burdenline_id IS NOT NULL) OR -- burdenline_id is sent
(p_dtl_tbl(i).burdenline_id <> FND_API.G_MISS_NUM) THEN
FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_UPD_FAILED_ID');
ELSE -- delete
IF (p_dtl_tbl(i).burdenline_id IS NOT NULL) OR -- burdenline_id is sent
(p_dtl_tbl(i).burdenline_id <> FND_API.G_MISS_NUM) THEN
FND_MESSAGE.SET_NAME('GMF','GMF_API_BRDN_DEL_FAILED_ID');
ROLLBACK TO Update_Burden_Details_PVT;
ROLLBACK TO Update_Burden_Details_PVT;
ROLLBACK TO Update_Burden_Details_PVT;
END Update_Burden_Details ;
SELECT
b.burdenline_id
, b.resources
, b.cost_cmpntcls_id
, c.cost_cmpntcls_code
, b.cost_analysis_code
, b.burden_usage
, b.item_qty
, b.item_uom
, b.burden_qty
, b.burden_uom
, b.burden_factor
, b.delete_mark
FROM
cm_cmpt_mst c, cm_brdn_dtl b
WHERE
b.organization_id = p_header_rec.organization_id
AND b.inventory_item_id = p_header_rec.inventory_item_id
AND b.period_id = p_header_rec.period_id
AND b.cost_type_id = p_header_rec.cost_type_id
AND c.cost_cmpntcls_id = b.cost_cmpntcls_id
ORDER BY
b.resources
, b.cost_cmpntcls_id
, b.cost_analysis_code
;
x_dtl_tbl(l_idx).delete_mark := cr_rec.delete_mark ;
SELECT 'x'
FROM cm_brdn_dtl
WHERE organization_id = cp_organization_id
AND inventory_item_id = cp_inventory_item_id
AND resources = cp_resources
AND period_id = cp_period_id
AND cost_type_id = cp_cost_type_id
AND cost_cmpntcls_id = cp_cost_cmpntcls_id
AND cost_analysis_code= cp_cost_analysis_code;