[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_ins_rows NUMBER; -- num rows inserted
l_upd_rows NUMBER; -- num rows updated
SELECT period_id
INTO l_period_id_from
FROM cm_cldr_mst_v
WHERE legal_entity_id = pi_legal_entity_id_from
AND calendar_code = pi_calendar_code_from
AND period_code = pi_period_code_from
AND cost_type_id = pi_cost_type_id_from;
SELECT period_id
INTO pi_period_id_to
FROM cm_cldr_mst_v
WHERE legal_entity_id = pi_legal_entity_id_to
AND calendar_code = pi_calendar_code_to
AND period_code = pi_period_code_to
AND cost_type_id = pi_cost_type_id_to;
l_sql_rsrc := ' SELECT ' ||
'd.resources, ' ||
'd.nominal_cost, ' ||
'd.usage_uom ' ||
' FROM ' ||
'cm_rsrc_dtl d, ' ||
'cr_rsrc_mst m ' ||
' WHERE ' ||
'd.legal_entity_id = :b_legal_entity_id AND '||
'nvl(d.organization_id,0) = nvl(:b_organization_id,0) AND ' ||
'd.period_id = :b_period_id AND ' ||
'd.delete_mark = 0 AND ' ||
'd.resources = m.resources AND ' ||
'm.delete_mark = 0 ' ;
l_sql_orgn := 'SELECT :pi_organization_id_to FROM dual ' ;
'SELECT ' ||
'hr.organization_id ' ||
'FROM ' ||
'hr_organization_information hr , mtl_parameters mp ' ||
'WHERE ' ||
'hr.org_information2 = :pi_legal_entity_id_to '||
' and hr.org_information_context = ''Accounting Information'' '||
' and hr.organization_id = mp.organization_id '||
' and mp.process_enabled_flag = ''Y'' ' ;
l_sql_periods := 'SELECT :pi_legal_entity_id_to, :pi_period_id_to FROM dual ' ;
l_sql_periods := 'SELECT DISTINCT ' ||
'c3.legal_entity_id, c3.period_id ' ||
'FROM ' ||
'cm_cldr_mst_v c3, cm_cldr_mst_v c2, cm_cldr_mst_v c1 ' ||
'WHERE ' ||
'c3.legal_entity_id = :pi_legal_entity_id_to 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.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''';
UPDATE cm_rsrc_dtl
SET usage_uom = rec_rsrc_dtl.usage_uom,
nominal_cost = rec_rsrc_dtl.nominal_cost,
rollover_ind = 0, ----unset the rollover_ind in target period
last_update_date = SYSDATE,
last_updated_by = l_user_id,
trans_cnt = 1,
delete_mark = 0
WHERE legal_entity_id = l_legal_entity_id_to
AND nvl(organization_id,0) = nvl(l_organization_id_to,0)
AND period_id = l_period_id_to
AND resources = rec_rsrc_dtl.resources;
gmf_util.trace( 'Updated ' || TO_CHAR(SQL%ROWCOUNT) || ' rows', 1 );
INSERT INTO cm_rsrc_dtl
(
legal_entity_id,
organization_id,
resources,
period_id,
cost_type_id,
usage_uom,
nominal_cost,
rollover_ind,
creation_date,
created_by,
last_update_date,
last_updated_by,
trans_cnt,
delete_mark
)
VALUES
(
l_legal_entity_id_to,
l_organization_id_to,
rec_rsrc_dtl.resources,
l_period_id_to,
pi_cost_type_id_to,
rec_rsrc_dtl.usage_uom,
rec_rsrc_dtl.nominal_cost,
0, ----unset the rollover_ind in the target period
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
1,
0
);
gmf_util.trace( 'Inserted ' || TO_CHAR(SQL%ROWCOUNT) || ' rows', 1 );
gmf_util.msg_log( 'GMF_CP_ROWS_SELECTED', TO_CHAR(l_ins_rows + l_upd_rows) );
SELECT COUNT(1)
FROM cm_rsrc_dtl d,
cr_rsrc_mst m
WHERE d.legal_entity_id = p_legal_entity_id
AND nvl(d.organization_id,0) = nvl(p_organization_id,0)
AND d.period_id = p_period_id
AND d.delete_mark = 0
AND d.resources = m.resources
AND (
p_resource_class IS NULL
OR m.resource_class = p_resource_class
)
AND m.delete_mark = 0;
SELECT COUNT(1)
FROM cm_rsrc_dtl d,
cr_rsrc_mst m
WHERE d.legal_entity_id = p_legal_entity_id
AND d.organization_id IN (SELECT a.organization_id FROM hr_organization_information a, mtl_parameters b
where a.organization_id = b.organization_id
and b.process_enabled_flag = 'Y' and
a.org_information2 = p_legal_entity_id
and a.org_information_context = 'Accounting Information' )
AND d.period_id = p_period_id
AND d.delete_mark = 0
AND d.resources = m.resources
AND (
p_resource_class IS NULL
OR m.resource_class = p_resource_class
)
AND m.delete_mark = 0;