[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_item_costs(
pi_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
pi_calendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
pi_period_id IN cm_cmpt_dtl.period_id%TYPE,
pi_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE
);
PROCEDURE delete_burden_costs(
pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
pi_period_id IN cm_cmpt_dtl.period_id%TYPE,
pi_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE,
pi_range_type IN NUMBER,
pi_from_range IN VARCHAR2,
pi_to_range IN VARCHAR2
);
SELECT decode(g_effid_copy, 'Y', 'YES', 'N', 'NO', '')
INTO l_effid_copy
FROM dual ;
* 09-Nov-1999 Rajesh Seshadri Bug 1069117 - The delete stmt should not be
* run again and again for the same item. Otherwise it will write only
* the last component row that is selected for copy
* 21-Nov-2000 Uday Moogala - Bug# 1419482 Copy Cost Enhancement :
* Copy to all periods and/or warehouses option
*
* 24-Jan-2002 Chetan Nagar - B2198228 Added paramter copy_to_upper_lvl
* for enhancement fix related to cost rollup (Ref. Bug 2116142).
* 27-Oct-2006 prasad marada Bug 5567156, 5567102. Not allowing to delete/update
* the cost for frozen periods.
* 24-Apr-2007 Prasad Marada BUg 5672543 Added call to check records in frozen
* period. In a frozen period existing costs not be changed during a copy.
* But New costs can be added though,
******************************************************************************/
PROCEDURE copy_cost_dtl(
pi_organization_id_from IN cm_cmpt_dtl.organization_id%TYPE,
pi_calendar_code_from IN cm_cmpt_dtl.calendar_code%TYPE,
pi_period_code_from IN cm_cmpt_dtl.period_code%TYPE,
pi_cost_type_id_from IN cm_cmpt_dtl.cost_type_id%TYPE,
pi_organization_id_to IN cm_cmpt_dtl.organization_id%TYPE,
pi_calendar_code_to IN cm_cmpt_dtl.calendar_code%TYPE,
pi_period_code_to IN cm_cmpt_dtl.period_code%TYPE,
pi_cost_type_id_to IN cm_cmpt_dtl.cost_type_id%TYPE,
pi_range_type IN NUMBER,
pi_from_range IN VARCHAR2,
pi_to_range IN VARCHAR2,
pi_incr_pct IN NUMBER,
pi_incr_decr_cost IN NUMBER,
pi_rem_repl IN NUMBER,
pi_all_periods_from IN cm_cmpt_dtl.period_code%TYPE,
pi_all_periods_to IN cm_cmpt_dtl.period_code%TYPE,
pi_all_org_id IN gmf_legal_entities.legal_entity_id%TYPE,
pi_copy_to_upper_lvl IN NUMBER
)
IS
TYPE rectyp_cost_detail IS RECORD (
cmpntcost_id cm_cmpt_dtl.cmpntcost_id%TYPE,
inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE,
cost_cmpntcls_id cm_cmpt_dtl.cost_cmpntcls_id%TYPE,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cost_level cm_cmpt_dtl.cost_level%TYPE,
cmpnt_cost cm_cmpt_dtl.cmpnt_cost%TYPE,
burden_ind cm_cmpt_dtl.burden_ind%TYPE,
total_qty cm_cmpt_dtl.total_qty%TYPE,
rmcalc_type cm_cmpt_dtl.rmcalc_type%TYPE,
fmeff_id cm_cmpt_dtl.fmeff_id%TYPE,
text_code cm_cmpt_dtl.text_code%TYPE,
attribute1 cm_cmpt_dtl.attribute1%TYPE,
attribute2 cm_cmpt_dtl.attribute2%TYPE,
attribute3 cm_cmpt_dtl.attribute3%TYPE,
attribute4 cm_cmpt_dtl.attribute4%TYPE,
attribute5 cm_cmpt_dtl.attribute5%TYPE,
attribute6 cm_cmpt_dtl.attribute6%TYPE,
attribute7 cm_cmpt_dtl.attribute7%TYPE,
attribute8 cm_cmpt_dtl.attribute8%TYPE,
attribute9 cm_cmpt_dtl.attribute9%TYPE,
attribute10 cm_cmpt_dtl.attribute10%TYPE,
attribute11 cm_cmpt_dtl.attribute11%TYPE,
attribute12 cm_cmpt_dtl.attribute12%TYPE,
attribute13 cm_cmpt_dtl.attribute13%TYPE,
attribute14 cm_cmpt_dtl.attribute14%TYPE,
attribute15 cm_cmpt_dtl.attribute15%TYPE,
attribute16 cm_cmpt_dtl.attribute16%TYPE,
attribute17 cm_cmpt_dtl.attribute17%TYPE,
attribute18 cm_cmpt_dtl.attribute18%TYPE,
attribute19 cm_cmpt_dtl.attribute19%TYPE,
attribute20 cm_cmpt_dtl.attribute20%TYPE,
attribute21 cm_cmpt_dtl.attribute21%TYPE,
attribute22 cm_cmpt_dtl.attribute22%TYPE,
attribute23 cm_cmpt_dtl.attribute23%TYPE,
attribute24 cm_cmpt_dtl.attribute24%TYPE,
attribute25 cm_cmpt_dtl.attribute25%TYPE,
attribute26 cm_cmpt_dtl.attribute26%TYPE,
attribute27 cm_cmpt_dtl.attribute27%TYPE,
attribute28 cm_cmpt_dtl.attribute28%TYPE,
attribute29 cm_cmpt_dtl.attribute29%TYPE,
attribute30 cm_cmpt_dtl.attribute30%TYPE
);
SELECT gps.period_id
INTO pi_period_id_to
FROM gmf_period_statuses gps, hr_organization_information org
WHERE gps.PERIOD_CODE = pi_period_code_to
AND gps.CALENDAR_CODE = pi_calendar_code_to
AND gps.legal_entity_id = org.org_information2
AND org.organization_id = pi_organization_id_to
AND org.org_information_context = 'Accounting Information'
AND gps.cost_type_id = pi_cost_type_id_to;
SELECT period_id
INTO pi_period_id_to
FROM gmf_period_statuses
WHERE PERIOD_CODE = pi_period_code_to
AND CALENDAR_CODE = pi_calendar_code_to
AND legal_entity_id = pi_all_org_id
AND cost_type_id = pi_cost_type_id_to;
SELECT gps.period_id
INTO pi_period_id_from
FROM gmf_period_statuses gps, hr_organization_information org
WHERE gps.PERIOD_CODE = pi_period_code_from
AND gps.CALENDAR_CODE = pi_calendar_code_from
AND gps.legal_entity_id = org.org_information2
AND org.organization_id = pi_organization_id_from
AND org.org_information_context = 'Accounting Information'
AND gps.cost_type_id = pi_cost_type_id_from;
' SELECT ' ||
'cst.cmpntcost_id,' ||
'cst.inventory_item_id,' ||
'cst.cost_cmpntcls_id,' ||
'cst.cost_analysis_code,' ||
'cst.cost_level,' ||
'cst.cmpnt_cost,' ||
'cst.burden_ind,' ||
'cst.total_qty,' ||
'cst.rmcalc_type,' ||
'cst.fmeff_id,' ||
'cst.text_code,' ||
'cst.attribute1,' ||
'cst.attribute2,' ||
'cst.attribute3,' ||
'cst.attribute4,' ||
'cst.attribute5,' ||
'cst.attribute6,' ||
'cst.attribute7,' ||
'cst.attribute8,' ||
'cst.attribute9,' ||
'cst.attribute10,' ||
'cst.attribute11,' ||
'cst.attribute12,' ||
'cst.attribute13,' ||
'cst.attribute14,' ||
'cst.attribute15,' ||
'cst.attribute16,' ||
'cst.attribute17,' ||
'cst.attribute18,' ||
'cst.attribute19,' ||
'cst.attribute20,' ||
'cst.attribute21,' ||
'cst.attribute22,' ||
'cst.attribute23,' ||
'cst.attribute24,' ||
'cst.attribute25,' ||
'cst.attribute26,' ||
'cst.attribute27,' ||
'cst.attribute28,' ||
'cst.attribute29,' ||
'cst.attribute30 ' ||
' FROM ' ||
'cm_cmpt_dtl cst' ||
' WHERE ' ||
'cst.organization_id = :b_organization_id AND ' ||
'cst.period_id = :b_period_id AND ' ||
'cst.cost_type_id = :b_cost_type_id '; -- AND ' ||
' select ''z'' from MTL_ITEM_FLEXFIELDS x'||
' where x.organization_id = cst.organization_id '||
' and x.item_number between :pi_from_range and :pi_to_range '||
' and x.inventory_item_id = cst.inventory_item_id )';
'AND EXISTS (select ''X'' from mtl_default_category_sets mdc, mtl_category_sets mcs, mtl_item_categories y, mtl_categories_kfv z '||
' where mdc.functional_area_id = 19 '||
' and mdc.category_set_id = mcs.category_set_id '||
' and mcs.category_set_id = y.category_set_id '||
' and mcs.structure_id = z.structure_id '||
' and y.inventory_item_id = cst.inventory_item_id '||
' and y.organization_id = cst.organization_id '||
' and y.category_id = z.category_id '||
' and z.concatenated_segments >= nvl(:b_from_itemcc, z.concatenated_segments) '||
' and z.concatenated_segments <= nvl(:b_to_itemcc, z.concatenated_segments))';
l_sql_org := 'SELECT :pi_organization_id_to '||' FROM dual ' ;
'SELECT ' ||
'hoi.organization_id ' ||
'FROM ' ||
'hr_organization_information hoi , mtl_parameters mp ' ||
' WHERE ' ||
'hoi.org_information2 = :pi_all_org_id '||
' AND hoi.org_information_context = ''Accounting Information'' '||
' AND hoi.organization_id = mp.organization_id '||
' and mp.process_enabled_flag = ''Y'' ' ;
l_sql_periods := 'SELECT :pi_period_id_to FROM dual ' ;
l_sql_periods := 'SELECT ' ||
'c3.period_id ' ||
'FROM ' ||
'gmf_period_statuses c3, gmf_period_statuses c2, gmf_period_statuses c1, hr_organization_information d ' ||
'WHERE ' ||
'd.organization_id = :pi_organization_id_to AND '||
'd.org_information_context = ''Accounting Information'' AND '||
'c1.calendar_code = :pi_calendar_code_to AND ' ||
'c1.period_code = :pi_all_periods_from AND ' ||
'c2.calendar_code = :pi_calendar_code_to AND ' ||
'c2.period_code = :pi_all_periods_to AND ' ||
'c3.calendar_code = :pi_calendar_code_to AND ' ||
'c3.cost_type_id = :pi_cost_type_id_to AND ' ||
'c2.cost_type_id = c3.cost_type_id AND ' ||
'c1.cost_type_id = c2.cost_type_id AND ' ||
'c3.legal_entity_id = d.org_information2 AND ' ||
'c2.legal_entity_id = c3.legal_entity_id AND ' ||
'c1.legal_entity_id = c2.legal_entity_id AND ' ||
'c3.start_date >= c1.start_date AND ' ||
'c3.end_date <= c2.end_date AND ' ||
'c3.period_status <> ''C'' ';
l_sql_periods := 'SELECT ' ||
'c3.period_id ' ||
'FROM ' ||
'gmf_period_statuses c3, gmf_period_statuses c2, gmf_period_statuses c1 ' ||
'WHERE ' ||
'c1.calendar_code = :pi_calendar_code_to AND ' ||
'c1.period_code = :pi_all_periods_from AND ' ||
'c2.calendar_code = :pi_calendar_code_to AND ' ||
'c2.period_code = :pi_all_periods_to AND ' ||
'c3.calendar_code = :pi_calendar_code_to AND ' ||
'c3.cost_type_id = :pi_cost_type_id_to AND ' ||
'c2.cost_type_id = c3.cost_type_id AND ' ||
'c1.cost_type_id = c2.cost_type_id AND ' ||
'c3.legal_entity_id = :pi_all_org_id AND ' ||
'c2.legal_entity_id = c3.legal_entity_id AND ' ||
'c1.legal_entity_id = c2.legal_entity_id AND ' ||
'c3.start_date >= c1.start_date AND ' ||
'c3.end_date <= c2.end_date AND ' ||
'c3.period_status <> ''C'' ';
* Try update of cm_cmpt_dtl first
* Update can fail for two reasons: either the row is not there
* or, the row exists but is frozen (rollover_ind = 1)
* If the costs are frozen in the target period then do not update the rows
* in cm_cmpt_dtl nor delete them from cm_scst_led/cm_acst_led.
* The item cost rows should be left untouched in the target period even if
* one of the components is frozen.
*/
-- Bug# 1419482 Copy Cost Enhancement. Uday Moogala
gmf_util.trace('item id and costcomp id...'|| r_cost_detail.inventory_item_id || '-' || r_cost_detail.cmpntcost_id,0) ;
* RS B1069117 - Call the delete stmt only once for an item
*/
-- start for bug 5567102, pmarada
IF( (l_curr_inventory_item_id2 = r_cost_detail.inventory_item_id) AND
(l_period_status = 'F') AND ( l_rem_repl = 1 )
) THEN
-- Skip this row for this item
gmf_util.trace( 'Period ' || l_period_code ||
' is Frozen. You can not Delete Frozen period cost.', 0 );
' is Frozen. You can not Delete Frozen period cost.', 0 );
delete_item_costs(
r_cost_detail.inventory_item_id,
l_organization_id_to,
pi_calendar_code_to,
l_period_id_to,
pi_cost_type_id_to
);
<>
DECLARE
CURSOR c_updins_cc_id(
p_calendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
p_period_id IN cm_cmpt_dtl.period_id%TYPE,
p_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE,
p_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
p_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
p_cost_cmpntcls_id IN cm_cmpt_dtl.cost_cmpntcls_id%TYPE,
p_cost_analysis_code IN cm_cmpt_dtl.cost_analysis_code%TYPE,
p_cost_level IN cm_cmpt_dtl.cost_level%TYPE
) IS
SELECT
cmpntcost_id
FROM
cm_cmpt_dtl
WHERE
period_id = p_period_id AND
cost_type_id = p_cost_type_id AND
organization_id = p_organization_id AND
inventory_item_id = p_inventory_item_id AND
cost_cmpntcls_id = p_cost_cmpntcls_id AND
cost_analysis_code = p_cost_analysis_code AND
cost_level = p_cost_level;
e_insert_row EXCEPTION;
* always try to update, if we fail it will insert anyway */
/* IF( l_rem_repl = 1 ) THEN */
IF( l_rem_repl = 1 and l_copy_to_upper_lvl <> 1 ) THEN
RAISE e_insert_row;
' is Frozen. You can not Update Frozen period cost.', 0 );
* Delete from scst_led, acst_led for the target parameters
* Update brdn_dtl and set cmpntcost_id to null
* Update cmpt_dtl
*/
DELETE FROM
cm_scst_led
WHERE
cmpntcost_id = l_updins_cc_id
;
gmf_util.trace( SQL%ROWCOUNT || ' rows deleted from scst_led ', 1 );
DELETE FROM
cm_acst_led
WHERE
cmpntcost_id = l_updins_cc_id
;
gmf_util.trace( SQL%ROWCOUNT || ' rows deleted from acst_led', 1 );
UPDATE cm_brdn_dtl
SET
cmpntcost_id = NULL
WHERE
cmpntcost_id = l_updins_cc_id
;
gmf_util.trace( SQL%ROWCOUNT || ' rows updated in brdn_dtl', 0);
UPDATE
cm_cmpt_dtl
SET
cmpntcost_id = GEM5_CMPNT_COST_ID_S.NEXTVAL,
cmpnt_cost = cmpnt_cost + r_cost_detail.cmpnt_cost,
burden_ind = r_cost_detail.burden_ind,
rollover_ind = 0,
total_qty = 0,
costcalc_orig = 4, -- B2232752 copied specially from lower level to upper level
rmcalc_type = 0,
rollup_ref_no = NULL,
acproc_id = NULL,
trans_cnt = 1,
text_code = NULL,
delete_mark = 0,
last_update_date = SYSDATE,
last_updated_by = g_user_id,
last_update_login = g_login_id,
request_id = g_request_id,
program_application_id = g_prog_appl_id,
program_id = g_program_id,
program_update_date = SYSDATE
WHERE
cmpntcost_id = l_updins_cc_id
;
UPDATE
cm_cmpt_dtl
SET
cmpntcost_id = GEM5_CMPNT_COST_ID_S.NEXTVAL,
cmpnt_cost = r_cost_detail.cmpnt_cost,
burden_ind = r_cost_detail.burden_ind,
fmeff_id = decode(g_effid_copy, -- Bug# 1419482
'Y', r_cost_detail.fmeff_id,
NULL),
rollover_ind = 0,
total_qty = 0,
costcalc_orig = decode(l_copy_to_upper_lvl, 1, 4, 2), -- B2232752 copied specially from lower level to upper level
rmcalc_type = 0,
rollup_ref_no = NULL,
acproc_id = NULL,
trans_cnt = 1,
text_code = NULL,
delete_mark = 0,
last_update_date = SYSDATE,
last_updated_by = g_user_id,
last_update_login = g_login_id,
request_id = g_request_id,
program_application_id = g_prog_appl_id,
program_id = g_program_id,
program_update_date = SYSDATE,
attribute1 = r_cost_detail.attribute1,
attribute2 = r_cost_detail.attribute2,
attribute3 = r_cost_detail.attribute3,
attribute4 = r_cost_detail.attribute4,
attribute5 = r_cost_detail.attribute5,
attribute6 = r_cost_detail.attribute6,
attribute7 = r_cost_detail.attribute7,
attribute8 = r_cost_detail.attribute8,
attribute9 = r_cost_detail.attribute9,
attribute10 = r_cost_detail.attribute10,
attribute11 = r_cost_detail.attribute11,
attribute12 = r_cost_detail.attribute12,
attribute13 = r_cost_detail.attribute13,
attribute14 = r_cost_detail.attribute14,
attribute15 = r_cost_detail.attribute15,
attribute16 = r_cost_detail.attribute16,
attribute17 = r_cost_detail.attribute17,
attribute18 = r_cost_detail.attribute18,
attribute19 = r_cost_detail.attribute19,
attribute20 = r_cost_detail.attribute20,
attribute21 = r_cost_detail.attribute21,
attribute22 = r_cost_detail.attribute22,
attribute23 = r_cost_detail.attribute23,
attribute24 = r_cost_detail.attribute24,
attribute25 = r_cost_detail.attribute25,
attribute26 = r_cost_detail.attribute26,
attribute27 = r_cost_detail.attribute27,
attribute28 = r_cost_detail.attribute28,
attribute29 = r_cost_detail.attribute29,
attribute30 = r_cost_detail.attribute30
WHERE
cmpntcost_id = l_updins_cc_id
;
gmf_util.trace( ' row updated to cmpt_dtl', 0 );
RAISE e_insert_row;
WHEN e_insert_row THEN
-- First close the open cursor
IF( c_updins_cc_id%ISOPEN ) THEN
CLOSE c_updins_cc_id;
INSERT INTO
cm_cmpt_dtl(
cmpntcost_id,
inventory_item_id,
organization_id,
cost_cmpntcls_id,
cost_analysis_code,
cost_level,
cmpnt_cost,
burden_ind,
fmeff_id,
rollover_ind,
total_qty,
costcalc_orig,
rmcalc_type,
rollup_ref_no,
acproc_id,
trans_cnt,
text_code,
delete_mark,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
period_id,
cost_type_id
)
VALUES (
GEM5_CMPNT_COST_ID_S.NEXTVAL,
r_cost_detail.inventory_item_id,
l_organization_id_to,
r_cost_detail.cost_cmpntcls_id,
r_cost_detail.cost_analysis_code,
decode(l_copy_to_upper_lvl, 1, 0, r_cost_detail.cost_level), -- B2198228
r_cost_detail.cmpnt_cost,
r_cost_detail.burden_ind,
decode(g_effid_copy, 'Y', r_cost_detail.fmeff_id, -- Bug# 1419482
NULL), -- fmeff_id,
0, -- rollover_ind,
0, -- total_qty,
decode(l_copy_to_upper_lvl, 1, 4, 2), -- B2232752 2, -- costcalc_orig,
0, -- rmcalc_type,
NULL, -- rollup_ref_no,
NULL, -- acproc_id,
1, -- trans_cnt,
NULL, -- text_code,
0, -- delete_mark,
SYSDATE, -- creation_date,
g_user_id, -- created_by,
SYSDATE, -- last_update_date,
g_user_id, -- last_updated_by,
g_login_id, -- last_update_login,
g_request_id, -- request_id,
g_prog_appl_id, -- program_application_id,
g_program_id, -- program_id,
SYSDATE, -- program_update_date,
r_cost_detail.attribute1,
r_cost_detail.attribute2,
r_cost_detail.attribute3,
r_cost_detail.attribute4,
r_cost_detail.attribute5,
r_cost_detail.attribute6,
r_cost_detail.attribute7,
r_cost_detail.attribute8,
r_cost_detail.attribute9,
r_cost_detail.attribute10,
r_cost_detail.attribute11,
r_cost_detail.attribute12,
r_cost_detail.attribute13,
r_cost_detail.attribute14,
r_cost_detail.attribute15,
r_cost_detail.attribute16,
r_cost_detail.attribute17,
r_cost_detail.attribute18,
r_cost_detail.attribute19,
r_cost_detail.attribute20,
r_cost_detail.attribute21,
r_cost_detail.attribute22,
r_cost_detail.attribute23,
r_cost_detail.attribute24,
r_cost_detail.attribute25,
r_cost_detail.attribute26,
r_cost_detail.attribute27,
r_cost_detail.attribute28,
r_cost_detail.attribute29,
r_cost_detail.attribute30,
l_period_id_to,
pi_cost_type_id_to
);
gmf_util.trace( SQL%ROWCOUNT || ' rows inserted to cmpt_dtl', 0 );
END insert_or_update;
gmf_util.msg_log( 'GMF_CP_ROWS_SELECTED', TO_CHAR(l_cost_rows) );
* delete_item_costs
*
* DESCRIPTION
* Deletes the child rows from cm_scst_led, cm_acst_led and sets
* cmpntcost_id to null in cm_brdn_dtl for the cost parameters passed
* NOTE: We do not have to worry about rollover_ind here since this procedure
* is not even called if the item is frozen in the target period.
*
* INPUT PARAMETERS
* item_id, organization_id, calendar_code, period_code, cost_mthd_code
*
* HISTORY
* 13-Oct-1999 Rajesh Seshadri
*
******************************************************************************/
PROCEDURE delete_item_costs(
pi_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
pi_calendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
pi_period_id IN cm_cmpt_dtl.period_id%TYPE,
pi_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE
)
IS
CURSOR c_cc_id(
p_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
p_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
p_calendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
p_period_id IN cm_cmpt_dtl.period_id%TYPE,
p_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE
)
IS
SELECT
cmpntcost_id
FROM
cm_cmpt_dtl
WHERE
inventory_item_id = p_inventory_item_id AND
organization_id = p_organization_id AND
period_id = p_period_id AND
cost_type_id = p_cost_type_id
FOR UPDATE
;
DELETE FROM
cm_acst_led
WHERE
cmpntcost_id = r_cc_id.cmpntcost_id
;
gmf_util.trace( SQL%ROWCOUNT || ' rows deleted from acst_led', 3 );
DELETE FROM
cm_scst_led
WHERE
cmpntcost_id = r_cc_id.cmpntcost_id
;
gmf_util.trace( SQL%ROWCOUNT || ' rows deleted from scst_led', 3 );
UPDATE
cm_brdn_dtl
SET
cmpntcost_id = NULL
WHERE
cmpntcost_id = r_cc_id.cmpntcost_id
;
gmf_util.trace( SQL%ROWCOUNT || ' rows updated in brdn_dtl', 3 );
DELETE FROM
cm_cmpt_dtl
WHERE CURRENT OF c_cc_id
;
gmf_util.trace( SQL%ROWCOUNT || ' rows deleted from cmpt_dtl', 3 );
END delete_item_costs;
SELECT nvl(max(rollover_ind),0) INTO l_frozen_ind
FROM
cm_cmpt_dtl
WHERE period_id = pi_period_id AND
cost_type_id = pi_cost_type_id AND
organization_id = pi_organization_id AND
inventory_item_id = pi_inventory_item_id
;
SELECT period_code, period_status FROM gmf_period_statuses
WHERE period_id = cp_period_id;
SELECT 'x' FROM cm_cmpt_dtl
WHERE organization_id = cp_organization_id
AND inventory_item_id = cp_inventory_item_id
AND period_id = cp_period_id
AND cost_type_id = cp_cost_type_id;
SELECT gps.period_id
INTO pi_period_id_to
FROM gmf_period_statuses gps, hr_organization_information org
WHERE gps.PERIOD_CODE = pi_period_code_to
AND gps.CALENDAR_CODE = pi_calendar_code_to
AND gps.legal_entity_id = org.org_information2
AND org.organization_id = pi_organization_id_to
AND org.org_information_context = 'Accounting Information'
AND gps.cost_type_id = pi_cost_type_id_to;
SELECT period_id
INTO pi_period_id_to
FROM gmf_period_statuses
WHERE PERIOD_CODE = pi_period_code_to
AND CALENDAR_CODE = pi_calendar_code_to
AND legal_entity_id = pi_all_org_id
AND cost_type_id = pi_cost_type_id_to;
SELECT gps.period_id
INTO pi_period_id_from
FROM gmf_period_statuses gps, hr_organization_information org
WHERE gps.PERIOD_CODE = pi_period_code_from
AND gps.CALENDAR_CODE = pi_calendar_code_from
AND gps.legal_entity_id = org.org_information2
and org.organization_id = pi_organization_id_from
AND org.org_information_context = 'Accounting Information'
AND gps.cost_type_id = pi_cost_type_id_from;
' SELECT ' ||
' bur.inventory_item_id, ' ||
' bur.resources, ' ||
' bur.cost_cmpntcls_id, ' ||
' bur.cost_analysis_code, ' ||
' bur.burden_qty, ' ||
' bur.burden_usage, ' ||
' bur.burden_uom, ' ||
' bur.item_qty, ' ||
' bur.item_uom, ' ||
' bur.burden_factor ' ||
' FROM ' ||
' cm_brdn_dtl bur ' ||
' WHERE ' ||
' bur.organization_id = :b_organization_id AND ' ||
' bur.period_id = :b_period_id AND ' ||
' bur.cost_type_id = :b_cost_type_id '; -- AND ' ||
' select 1 from MTL_ITEM_FLEXFIELDS x'||
' where x.organization_id = bur.organization_id '||
' and x.item_number between :pi_from_range and :pi_to_range '||
' and x.inventory_item_id = bur.inventory_item_id )';
'AND EXISTS (select ''X'' from mtl_default_category_sets mdc, mtl_category_sets mcs, mtl_item_categories y, mtl_categories_kfv z
where mdc.functional_area_id = 19
and mdc.category_set_id = mcs.category_set_id
and mcs.category_set_id = y.category_set_id
and mcs.structure_id = z.structure_id
and y.inventory_item_id = bur.inventory_item_id
and y.organization_id = bur.organization_id
and y.category_id = z.category_id
and z.concatenated_segments >= nvl(:b_from_itemcc, z.concatenated_segments)
and z.concatenated_segments <= nvl(:b_to_itemcc, z.concatenated_segments))';
l_sql_org_b := 'SELECT :pi_organization_id_to FROM dual ' ;
'SELECT ' ||
'hoi.organization_id ' ||
'FROM ' ||
'hr_organization_information hoi , mtl_parameters mp ' ||
' WHERE ' ||
'hoi.org_information2 = :pi_all_org_id '||
' AND hoi.org_information_context = ''Accounting Information'' '||
' AND hoi.organization_id = mp.organization_id '||
' and mp.process_enabled_flag = ''Y'' ' ;
l_sql_periods_b := 'SELECT :pi_period_id_to FROM dual ' ;
l_sql_periods_b := 'SELECT ' ||
'c3.period_id ' ||
'FROM ' ||
'gmf_period_statuses c3, gmf_period_statuses c2, gmf_period_statuses c1, hr_organization_information d ' ||
'WHERE ' ||
'd.organization_id = :pi_organization_id_to AND '||
'd.org_information_context = ''Accounting Information'' AND '||
'c1.calendar_code = :pi_calendar_code_to AND ' ||
'c1.period_code = :pi_all_periods_from AND ' ||
'c2.calendar_code = :pi_calendar_code_to AND ' ||
'c2.period_code = :pi_all_periods_to AND ' ||
'c3.calendar_code = :pi_calendar_code_to AND ' ||
'c3.cost_type_id = :pi_cost_type_id_to AND ' ||
'c2.cost_type_id = c3.cost_type_id AND ' ||
'c1.cost_type_id = c2.cost_type_id AND ' ||
'c3.legal_entity_id = d.org_information2 AND ' ||
'c2.legal_entity_id = c3.legal_entity_id AND ' ||
'c1.legal_entity_id = c2.legal_entity_id AND ' ||
'c3.start_date >= c1.start_date AND ' ||
'c3.end_date <= c2.end_date AND ' ||
'c3.period_status <> ''C'' ';
l_sql_periods_b := 'SELECT ' ||
'c3.period_id ' ||
'FROM ' ||
'gmf_period_statuses c3, gmf_period_statuses c2, gmf_period_statuses c1 ' ||
'WHERE ' ||
'c1.calendar_code = :pi_calendar_code_to AND ' ||
'c1.period_code = :pi_all_periods_from AND ' ||
'c2.calendar_code = :pi_calendar_code_to AND ' ||
'c2.period_code = :pi_all_periods_to AND ' ||
'c3.calendar_code = :pi_calendar_code_to AND ' ||
'c3.cost_type_id = :pi_cost_type_id_to AND ' ||
'c2.cost_type_id = c3.cost_type_id AND ' ||
'c1.cost_type_id = c2.cost_type_id AND ' ||
'c3.legal_entity_id = :pi_all_org_id AND ' ||
'c2.legal_entity_id = c3.legal_entity_id AND ' ||
'c1.legal_entity_id = c2.legal_entity_id AND ' ||
'c3.start_date >= c1.start_date AND ' ||
'c3.end_date <= c2.end_date AND ' ||
'c3.period_status <> ''C'' ';
delete_burden_costs(
l_organization_id_to,
l_period_id_to,
pi_cost_type_id_to,
pi_range_type,
pi_from_range, pi_to_range
);
<>
DECLARE
e_insert_row_b EXCEPTION;
RAISE e_insert_row_b;
UPDATE
cm_brdn_dtl
SET
-- burdenline_id = GEM5_BURDENLINE_ID_S.NEXTVAL,
burden_qty = r_brdn_dtl.burden_qty,
burden_usage = r_brdn_dtl.burden_usage,
burden_uom = r_brdn_dtl.burden_um,
item_qty = r_brdn_dtl.item_qty,
item_uom = r_brdn_dtl.item_um,
burden_factor = r_brdn_dtl.burden_factor,
rollover_ind = 0,
cmpntcost_id = NULL,
trans_cnt = 1,
delete_mark = 0,
text_code = NULL,
last_updated_by = g_user_id,
last_update_login = g_login_id,
last_update_date = SYSDATE,
request_id = g_request_id,
program_application_id = g_prog_appl_id,
program_id = g_program_id,
program_update_date = SYSDATE
WHERE
organization_id = l_organization_id_to AND
inventory_item_id = r_brdn_dtl.inventory_item_id AND
resources = r_brdn_dtl.resources AND
period_id = l_period_id_to AND
cost_type_id = pi_cost_type_id_to AND
cost_cmpntcls_id = r_brdn_dtl.cost_cmpntcls_id AND
cost_analysis_code = r_brdn_dtl.cost_analysis_code;
RAISE e_insert_row_b;
WHEN e_insert_row_b THEN
INSERT INTO
cm_brdn_dtl(
burdenline_id,
organization_id,
inventory_item_id,
resources,
cost_cmpntcls_id,
cost_analysis_code,
burden_qty,
burden_usage,
burden_uom,
item_qty,
item_uom,
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,
period_id,
cost_type_id)
VALUES (
GEM5_BURDENLINE_ID_S.NEXTVAL, -- burdenline_id
l_organization_id_to,
r_brdn_dtl.inventory_item_id,
r_brdn_dtl.resources,
r_brdn_dtl.cost_cmpntcls_id,
r_brdn_dtl.cost_analysis_code,
r_brdn_dtl.burden_qty,
r_brdn_dtl.burden_usage,
r_brdn_dtl.burden_um,
r_brdn_dtl.item_qty,
r_brdn_dtl.item_um,
r_brdn_dtl.burden_factor,
0, -- rollover_ind
NULL, -- cmpntcost_id
1, -- trans_cnt
0, -- delete_mark
NULL, -- text_code
g_user_id, -- created_by
SYSDATE, -- creation_date
g_user_id, -- last_updated_by
g_login_id, -- last_update_login
SYSDATE , -- last_update_date
g_request_id,
g_prog_appl_id, -- program_application_id
g_program_id, -- program_id
SYSDATE, -- program_update_date
l_period_id_to,
pi_cost_type_id_to
);
END insert_or_update_bur;
gmf_util.msg_log( 'GMF_CP_ROWS_SELECTED', TO_CHAR(l_brdn_rows) );
* delete_burden_costs
*
* DESCRIPTION
* Deletes the burden costs for the parameters passed
*
* INPUT PARAMETERS
* organization_id, calendar, period, cost_mthd, item or itemcost_class range
*
* HISTORY
* 13-Oct-1999 Rajesh Seshadri
*
******************************************************************************/
PROCEDURE delete_burden_costs(
pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
pi_period_id IN cm_cmpt_dtl.period_id%TYPE,
pi_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE,
pi_range_type IN NUMBER,
pi_from_range IN VARCHAR2,
pi_to_range IN VARCHAR2
)
IS
l_del_stmt_b VARCHAR2(2000);
fnd_file.put_line(fnd_file.log,'In delete_burden_costs');
' DELETE FROM ' ||
' cm_brdn_dtl bur ' ||
' WHERE ' ||
' bur.organization_id = :b_organization_id AND ' ||
' bur.period_id = :b_period_id AND ' ||
' bur.cost_type_id = :b_cost_type_id AND ' ||
' bur.inventory_item_id IN ( '
;
' SELECT ' ||
' itm.inventory_item_id ' ||
' FROM ' ||
' mtl_system_items_b_kfv itm ' ||
' WHERE ' ||
' 1 = 1';
' AND EXISTS (select ''X'' from mtl_default_category_sets mdc, mtl_category_sets mcs, mtl_item_categories y, mtl_categories_kfv z
where mdc.functional_area_id = 19
and mdc.category_set_id = mcs.category_set_id
and mcs.category_set_id = y.category_set_id
and mcs.structure_id = z.structure_id
and y.inventory_item_id = itm.inventory_item_id
and y.organization_id = itm.organization_id
and y.category_id = z.category_id
and z.concatenated_segments >= nvl(:b_from_itemcc, z.concatenated_segments)
and z.concatenated_segments <= nvl(:b_to_itemcc, z.concatenated_segments))';
gmf_util.trace( SQL%ROWCOUNT || ' Rows deleted from Burden Details', 0);
END delete_burden_costs;
SELECT count(1) INTO l_assigned_ind
FROM
mtl_system_items_b
WHERE
organization_id = pi_organization_id AND
inventory_item_id = pi_inventory_item_id
;