[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_select_clause VARCHAR2(32000);
SELECT hsecs INTO l_start_time from v$timer;
v_where_clause := v_where_clause || ' AND header_id NOT IN (Select header_id from rlm_schedule_interface_lines_v ril
where ((ril.cust_ship_from_org_ext <> :p_cust_ship_from_ext) OR (ril.cust_ship_from_org_ext is NULL)) AND
ril.process_status <> 5)' ;
g_BindVarTab.DELETE;
v_select_clause := 'SELECT hdr.header_id, hdr.process_status,
hdr.edi_test_indicator ';
''CHANGE'', 7, ''DELETE'', 8),
hdr.creation_date';
''CHANGE'', 7, ''DELETE'', 8),
hdr.creation_date';
v_Statement := v_select_clause || ' '
|| v_from_clause || ' '
|| v_where_clause
|| v_order_clause;
rlm_core_sv.dlog(C_DEBUG,'The select Statement is:
', v_Statement);
RLM_FORECAST_SV.g_designator_tab.delete;
update rlm_interface_headers_all
set process_status = rlm_core_sv.k_PS_AVAILABLE
where header_id = v_header_id;
rlm_core_sv.dlog(C_DEBUG,' No of headers updated:', SQL%ROWCOUNT);
update rlm_interface_lines
set process_status = rlm_core_sv.k_PS_AVAILABLE,
dsp_child_process_index = NULL
where header_id = v_header_id
and process_status = rlm_core_sv.k_PS_ERROR;
rlm_core_sv.dlog(C_DEBUG,' No of lines updated:', SQL%ROWCOUNT);
delete from rlm_demand_exceptions rde
where rde.interface_header_id = v_header_id
and (rde.interface_line_id in (select ril.line_id
from rlm_interface_lines ril
where ril.header_id = rde.interface_header_id)
or exception_level = 'E'
or message_name = 'RLM_WARN_DROPPED_ITEMS'); --Bugfix 8844817
rlm_core_sv.dlog(C_DEBUG,' No of demand exceptions lines deleted:', SQL%ROWCOUNT);
update rlm_demand_exceptions
set request_id = rlm_message_sv.g_conc_req_id
where interface_header_id = v_header_id;
SELECT hsecs INTO l_val_start_time from v$timer;
SELECT hsecs INTO l_val_end_time from v$timer;
SELECT hsecs INTO l_post_start_time from v$timer;
SELECT hsecs INTO l_post_end_time from v$timer;
SELECT hsecs INTO l_comp_start_time from v$timer;
SELECT hsecs INTO l_comp_end_time from v$timer;
SELECT hsecs INTO l_start_child_time from v$timer;
SELECT hsecs INTO l_end_child_time from v$timer;
v_child_req_id.delete;
UpdateHeaderPS(v_Sched_rec.header_id,
v_Sched_rec.schedule_header_id);
UpdateGroupPS(v_Sched_rec.header_id,
v_Sched_rec.Schedule_header_id,
v_Group_rec,
rlm_core_sv.K_PS_PROCESSED,
'ALL');
UpdateHeaderPS(v_Sched_rec.header_id,
v_Sched_rec.Schedule_header_id);
UpdateGroupPS(v_header_id,
v_schedule_header_id,
v_Group_rec,
rlm_core_sv.k_PS_ERROR,
'ALL');
UpdateHeaderPS(v_header_id,
v_schedule_header_id);
UpdateGroupPS(v_Sched_rec.header_id,
v_Sched_rec.schedule_header_id,
v_Group_rec,
rlm_core_sv.k_PS_ERROR);
UpdateHeaderPS(v_header_id, v_schedule_header_id);
UpdateGroupPS(v_header_id,
v_schedule_header_id,
v_Group_rec,
rlm_core_sv.k_PS_ERROR,
'ALL');
/* UpdateHeaderPS(v_header_id,
v_schedule_header_id); */
UpdateGroupPS(v_header_id,
v_schedule_header_id,
v_Group_rec,
rlm_core_sv.k_PS_ERROR,
'ALL');
UpdateHeaderPS(v_header_id,
v_schedule_header_id);
SELECT hsecs INTO l_end_time from v$timer;
select process_status into v_process_status
from rlm_interface_headers
where header_id = x_header_id;
DELETE FROM RLM_INTERFACE_HEADERS
WHERE header_id = x_header_id
and process_Status = rlm_core_sv.k_PS_PROCESSED;
DELETE FROM RLM_INTERFACE_LINES
WHERE header_id = x_header_id
and process_Status = rlm_core_sv.k_PS_PROCESSED;
DELETE FROM RLM_INTERFACE_LINES
WHERE header_id = x_header_id
and process_Status = rlm_core_sv.k_PS_PROCESSED
and item_detail_type <> rlm_rd_sv.k_MRP_FORECAST;
rlm_core_sv.dlog(C_DEBUG, 'Lines deleted ', SQL%ROWCOUNT);
rlm_core_sv.dlog(C_DEBUG, 'No records to delete' );
SELECT *
FROM rlm_interface_headers
WHERE header_id = x_HeaderId
and process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_PARTIAL_PROCESSED)
FOR UPDATE NOWAIT;
PROCEDURE NAME: UpdateGroupPS
===========================================================================*/
PROCEDURE UpdateGroupPS(x_header_id IN NUMBER,
x_ScheduleHeaderId IN NUMBER,
x_Group_rec IN rlm_dp_sv.t_Group_rec,
x_status IN NUMBER,
x_UpdateLevel IN VARCHAR2)
IS
--
v_Progress VARCHAR2(3) := '010';
v_program_update_date DATE:= sysdate;
rlm_core_sv.dpush(C_SDEBUG, 'UpdateGroupPS');
rlm_core_sv.dlog(C_DEBUG,'UpdateGroupStatus to ', x_status);
rlm_core_sv.dlog(C_DEBUG,'x_UpdateLevel to ', x_UpdateLevel);
SELECT schedule_header_id
INTO v_SchedHeaderId
FROM rlm_interface_headers
WHERE header_id = x_header_id;
IF x_UpdateLevel <> 'GROUP' THEN
--
UPDATE rlm_interface_lines
SET process_status = x_Status,
LAST_UPDATE_LOGIN = v_login_id ,
REQUEST_ID = v_request_id,
PROGRAM_APPLICATION_ID = v_program_app_id,
PROGRAM_ID = v_program_id,
PROGRAM_UPDATE_DATE = v_program_update_date
WHERE header_id = x_header_id
AND process_status <> rlm_core_sv.k_PS_ERROR; -- bug 5134706
rlm_core_sv.dlog(C_DEBUG,'No of interface Lines Updated ', SQL%ROWCOUNT);
UPDATE rlm_schedule_lines sl
SET process_status = x_Status,
LAST_UPDATE_LOGIN = v_login_id ,
REQUEST_ID = v_request_id,
PROGRAM_APPLICATION_ID = v_program_app_id,
PROGRAM_ID = v_program_id,
PROGRAM_UPDATE_DATE = v_program_update_date
WHERE sl.header_id = v_SchedHeaderId
AND process_status <> rlm_core_sv.k_PS_ERROR -- bug 5134706
AND interface_line_id in
(SELECT line_id
FROM rlm_interface_lines_all il
WHERE il.header_id = x_header_id);
rlm_core_sv.dlog(C_DEBUG,'No of Schedule Lines Updated ', SQL%ROWCOUNT);
rlm_core_sv.dlog(C_DEBUG,'Update Group');
UPDATE rlm_schedule_lines sch
SET process_status = x_Status,
LAST_UPDATE_LOGIN = v_login_id ,
REQUEST_ID = v_request_id,
PROGRAM_APPLICATION_ID = v_program_app_id,
PROGRAM_ID = v_program_id,
PROGRAM_UPDATE_DATE = v_program_update_date
WHERE header_id = v_SchedHeaderId
AND interface_line_id in
(SELECT /*+ unnest */ line_id --Bugfix 12863728
FROM rlm_interface_lines_all il
WHERE header_id = x_header_id
AND industry_attribute15 = x_Group_rec.industry_attribute15
AND ship_to_org_id = x_Group_rec.ship_to_org_id
AND customer_item_id = x_Group_rec.customer_item_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_PROCESSED,
rlm_core_sv.k_PS_FROZEN_FIRM));
rlm_core_sv.dlog(C_DEBUG,'No of Schedule Lines Updated ', SQL%ROWCOUNT);
UPDATE rlm_interface_lines
SET process_status = x_Status,
LAST_UPDATE_LOGIN = v_login_id ,
REQUEST_ID = v_request_id,
PROGRAM_APPLICATION_ID = v_program_app_id,
PROGRAM_ID = v_program_id,
PROGRAM_UPDATE_DATE = v_program_update_date
WHERE header_id = x_header_id
AND industry_attribute15 = x_Group_rec.industry_attribute15
AND ship_to_org_id = x_Group_rec.ship_to_org_id
AND customer_item_id = x_Group_rec.customer_item_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_PROCESSED,
rlm_core_sv.k_PS_FROZEN_FIRM);
rlm_core_sv.dlog(C_DEBUG,'No of interface Lines Updated ', SQL%ROWCOUNT);
rlm_message_sv.sql_error('rlm_dp_sv.UpdateGroupPS', v_Progress);
END UpdateGroupPS;
PROCEDURE NAME: UpdateHeaderPS
===========================================================================*/
PROCEDURE UpdateHeaderPS (x_HeaderId IN NUMBER,
x_ScheduleHeaderId IN NUMBER)
IS
--
x_progress VARCHAR2(3) := '010';
rlm_core_sv.dpush(C_SDEBUG,'UpdateHeaderPS');
SELECT schedule_header_id
INTO v_SchedHeaderId
FROM rlm_interface_headers
WHERE header_id = x_HeaderId;
SELECT count(*)
INTO v_tot_recs
FROM rlm_interface_lines
WHERE header_id = x_HeaderId;
SELECT count(*)
INTO v_error_recs
FROM rlm_interface_lines
WHERE header_id = x_HeaderId
AND process_status = rlm_core_sv.k_PS_ERROR;
SELECT count(*)
INTO v_proc_recs
FROM rlm_interface_lines
WHERE header_id = x_HeaderId
AND process_status = rlm_core_sv.k_PS_PROCESSED;
SELECT COUNT(1)
INTO v_proc_sch
FROM rlm_schedule_lines
WHERE process_status = rlm_core_sv.k_PS_PROCESSED
AND header_id = v_SchedHeaderId;
UPDATE rlm_interface_headers
SET process_status = x_HeaderStatus,
LAST_UPDATE_LOGIN = fnd_global.login_id ,
REQUEST_ID = RLM_MESSAGE_SV.g_conc_req_id ,
PROGRAM_APPLICATION_ID = fnd_global.PROG_APPL_ID ,
PROGRAM_ID = fnd_global.conc_program_id,
PROGRAM_UPDATE_DATE = sysdate
WHERE header_id = x_HeaderId;
rlm_core_sv.dlog(C_DEBUG,'Number of Interface header updated',SQL%ROWCOUNT);
UPDATE rlm_schedule_headers
SET process_status = x_HeaderStatus,
LAST_UPDATE_LOGIN = fnd_global.login_id ,
REQUEST_ID = RLM_MESSAGE_SV.g_conc_req_id ,
PROGRAM_APPLICATION_ID = fnd_global.PROG_APPL_ID ,
PROGRAM_ID = fnd_global.conc_program_id,
PROGRAM_UPDATE_DATE = sysdate
WHERE header_id = v_SchedHeaderId ;
rlm_core_sv.dlog(C_DEBUG,'Number of schedule header updated',SQL%ROWCOUNT);
rlm_message_sv.sql_error('rlm_dp_sv.UpdateHeaderStatus', x_progress);
END UpdateHeaderPS;
Select count(*)
into x_errors
from rlm_demand_exceptions
where request_id = x_requestid
and exception_level in ('E', 'W', 'I');
SELECT count(*) into v_Count
FROM rlm_interface_lines
WHERE header_id = x_header_id
AND industry_attribute15 = x_Group_rec.industry_attribute15
AND ship_to_org_id = x_Group_rec.ship_to_org_id
AND customer_item_id = x_Group_rec.customer_item_id
AND item_detail_type = rlm_rd_sv.k_MRP_FORECAST
AND process_status = rlm_core_sv.k_PS_AVAILABLE;
SELECT ril.order_header_id,
ril.blanket_number
FROM rlm_interface_headers rih,
rlm_interface_lines_all ril
WHERE ril.header_id = x_header_id
AND ril.header_id = rih.header_id
AND ril.process_status in ( rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_PARTIAL_PROCESSED)
AND rih.org_id = ril.org_id
GROUP BY ril.order_header_id,ril.blanket_number ;
update rlm_interface_lines
set dsp_child_process_index = v_index
where header_id = x_header_id
and nvl(order_header_id,-99) = nvl(v_Group_rec.order_header_id,-99)
and nvl(blanket_number,-99) = nvl(v_Group_rec.blanket_number,-99);
is completed before it can update the header.
child request updates the interface lines after
completing manage demand, forecast and rec demand */
--
FOR i IN x_child_req_id.FIRST..x_child_req_id.LAST LOOP
--
v_wait_status := fnd_concurrent.wait_for_request(
x_child_req_id(i),
10, -- check every 10 sec
10000, -- timeout after 10000 sec
v_phase,
v_reqstatus,
v_devphase,
v_devstatus,
v_reqmessage);
update rlm_demand_exceptions
set request_id = RLM_MESSAGE_SV.g_conc_req_id
where request_id = x_child_req_id(i);
/* update group status for all lines with child req id*/
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(C_DEBUG,'child process index ', i);
update rlm_interface_lines
set process_status= rlm_core_sv.k_PS_ERROR
where header_id = x_header_id
and dsp_child_process_index = i
and process_status <> rlm_core_sv.k_PS_PROCESSED;
rlm_core_sv.dlog(C_DEBUG,'No of interface Lines Updated', SQL%ROWCOUNT);
update rlm_schedule_lines sch
set process_status = rlm_core_sv.k_PS_ERROR
where interface_line_id in
(select line_id
from rlm_interface_lines_all il
where header_id = x_header_id
and dsp_child_process_index = i
and process_status <> rlm_core_sv.k_PS_PROCESSED);
rlm_core_sv.dlog(C_DEBUG,'No of schedule Lines Updated ', SQL%ROWCOUNT);
SELECT *
INTO v_sched_rec
FROM rlm_interface_headers_all
WHERE header_id = p_header_id
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_PARTIAL_PROCESSED);
UpdateGroupPS(p_header_id,
null,
v_Group_rec,
rlm_core_sv.K_PS_ERROR,
'ALL');
update rlm_interface_lines
set process_status = rlm_core_sv.k_PS_ERROR
where header_id = p_header_id
and dsp_child_process_index = p_index;
update rlm_schedule_lines sch
set process_status = rlm_core_sv.k_PS_ERROR
where interface_line_id in
(select line_id
from rlm_interface_lines_all il
where header_id = p_header_id
and dsp_child_process_index = p_index);
SELECT rih.customer_id,
ril.ship_from_org_id,
ril.ship_to_address_id,
ril.ship_to_site_use_id,
ril.ship_to_org_id,
ril.customer_item_id,
ril.inventory_item_id,
ril.industry_attribute15,
ril.intrmd_ship_to_id, --Bugfix 5911991
ril.intmed_ship_to_org_id, --Bugfix 5911991
ril.order_header_id,
ril.blanket_number,
min(ril.start_date_time),
ril.ship_to_customer_id
FROM rlm_interface_headers rih,
rlm_interface_lines_all ril
WHERE ril.header_id = p_header_id
AND ril.header_id = rih.header_id
AND nvl(ril.dsp_child_process_index,-99) =nvl(p_index, -99)
AND ril.process_status in ( rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_PARTIAL_PROCESSED)
AND rih.org_id = ril.org_id
GROUP BY rih.customer_id,
ril.ship_from_org_id,
ril.ship_to_address_id,
ril.ship_to_site_use_id,
ril.ship_to_org_id,
ril.customer_item_id,
ril.inventory_item_id,
ril.industry_attribute15,
ril.intrmd_ship_to_id, --Bugfix 5911991
ril.intmed_ship_to_org_id, --Bugfix 5911991
ril.order_header_id,
ril.blanket_number,
ril.ship_to_customer_id
ORDER BY min(ril.start_date_time),
ril.ship_to_address_id,
ril.customer_item_id;
SELECT hsecs INTO l_md_start_time from v$timer;
SELECT hsecs INTO l_md_end_time from v$timer;
SELECT hsecs INTO l_mf_start_time from v$timer;
SELECT hsecs INTO l_mf_end_time from v$timer;
SELECT hsecs INTO l_rd_start_time from v$timer;
SELECT hsecs INTO l_rd_end_time from v$timer;
UpdateGroupPS(v_Sched_rec.header_id,
v_Sched_rec.schedule_header_id,
v_Group_rec,
rlm_core_sv.k_PS_PROCESSED);
UpdateGroupPS(v_Sched_rec.header_id,
v_Sched_rec.schedule_header_id,
v_Group_rec,
rlm_core_sv.k_PS_ERROR);
UpdateGroupPS(v_Sched_rec.header_id,
v_Sched_rec.schedule_header_id,
v_Group_rec,
rlm_core_sv.k_PS_ERROR);
UpdateGroupPS(v_Sched_rec.header_id,
v_Sched_rec.schedule_header_id,
v_Group_rec,
rlm_core_sv.k_PS_ERROR);
UpdateGroupPS(v_Sched_rec.header_id,
v_Sched_rec.schedule_header_id,
v_Group_rec,
rlm_core_sv.k_PS_ERROR);