[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure will insert a record for object = 'SOURCE' in
BIM_REP_HISTORY table, whenever the LOAD_DATA procedure is called
for the first time in the day.
----------------------------------------------------------------------*/
PROCEDURE LOG_HISTORY
(
p_object IN VARCHAR2 DEFAULT 'SOURCE',
p_start_date IN DATE DEFAULT NULL,
p_end_date IN DATE DEFAULT NULL
)
IS
l_user_id NUMBER := FND_GLOBAL.USER_ID();
INSERT INTO BIM_REP_HISTORY
(creation_date,
last_update_date,
created_by,
last_updated_by,
object,
object_last_updated_date,
start_date,
end_date)
VALUES
(sysdate,
sysdate,
l_user_id,
l_user_id,
p_object,
sysdate,
p_start_date,
p_end_date);
SELECT trunc(max(creation_date))
FROM BIM_REP_HISTORY
WHERE object = 'SOURCE';
/* The INSERT statement to populate BIM_R_LOCATIONS table begins here */
INSERT
INTO BIM_R_LOCATIONS
(
country,
region)
SELECT
country_code, area2_code
FROM jtf_loc_hierarchies_b
WHERE location_type_code = 'COUNTRY'
AND country_code is not null;
/* The INSERT statement to populate BIM_R_SOURCE_CODES table begins here */
INSERT /*+ append parallel(SRC,1) */
INTO BIM_R_SOURCE_CODES SRC
(
source_code_id,
source_code,
parent_object_type,
object_type,
parent_object_id,
object_id,
business_unit_id,
status,
country_code,
start_date,
end_date
)
SELECT /*+ parallel(INNER,1) */
inner.source_code_id,
inner.source_code,
inner.parent_object_type,
inner.object_type,
inner.parent_object_id,
inner.object_id,
inner.business_unit_id,
inner.status,
inner.country_code,
inner.start_date,
inner.end_date
FROM
(
SELECT
a.source_code_id source_code_id,
a.source_code source_code,
'CAMP' parent_object_type,
'CAMP' object_type,
b.campaign_id parent_object_id,
0 object_id,
b.business_unit_id,
b.status_code status,
c.country_code country_code,
b.actual_exec_start_date start_date,
b.actual_exec_end_date end_date
FROM
ams_source_codes a,
ams_campaigns_all_b b,
jtf_loc_hierarchies_b c
WHERE
a.source_code = b.source_code
AND a.source_code_for_id = b.campaign_id
AND b.city_id = c.location_hierarchy_id
AND a.arc_source_code_for = 'CAMP'
AND b.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE')
UNION ALL
SELECT
a.source_code_id source_code_id,
a.source_code source_code,
'CAMP' parent_object_type,
'CSCH' object_type,
b.campaign_id parent_object_id,
c.schedule_id object_id,
b.business_unit_id,
b.status_code status,
d.country_code country_code,
c.start_date_time start_date,
c.end_date_time end_date
FROM
ams_source_codes a,
ams_campaigns_all_b b,
ams_campaign_schedules_b c,
jtf_loc_hierarchies_b d
WHERE
a.source_code = c.source_code
AND a.source_code_for_id = c.schedule_id
AND a.arc_source_code_for = 'CSCH'
AND b.campaign_id = c.campaign_id
AND b.city_id = d.location_hierarchy_id
AND b.status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE')
UNION ALL
SELECT
a.source_code_id source_code_id,
a.source_code source_code,
'EVEH' parent_object_type,
'EVEH' object_type,
b.event_header_id parent_object_id,
0 object_id,
b.business_unit_id,
b.system_status_code status,
c.country_code country_code,
b.active_from_date start_date,
b.active_to_date end_date
FROM
ams_source_codes a,
ams_event_headers_all_b b,
jtf_loc_hierarchies_b c
WHERE
a.source_code = b.source_code
AND a.source_code_for_id = b.event_header_id
AND b.country_code = c.location_hierarchy_id
AND a.arc_source_code_for = 'EVEH'
AND b.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE')
UNION ALL
SELECT
a.source_code_id source_code_id,
a.source_code source_code,
'EVEH' parent_object_type,
a.arc_source_code_for object_type,
b.event_header_id parent_object_id,
c.event_offer_id object_id,
b.business_unit_id,
b.system_status_code status,
d.country_code country_code,
c.event_start_date start_date,
c.event_end_date end_date
FROM
ams_source_codes a,
ams_event_headers_all_b b,
ams_event_offers_all_b c,
jtf_loc_hierarchies_b d
WHERE
a.source_code = c.source_code
AND a.source_code_for_id = c.event_offer_id
AND a.arc_source_code_for in ('EONE', 'EVEO')
AND b.event_header_id = c.event_header_id
AND b.country_code = d.location_hierarchy_id
AND b.system_status_code IN ('COMPLETED', 'CANCELLED', 'CLOSED', 'ACTIVE')
) "INNER";