The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM rlm_interface_lines_all
WHERE header_id = x_Sched_rec.header_id AND
ship_from_org_id = x_Group_rec.ship_from_org_id AND
ship_to_address_id = x_Group_rec.ship_to_address_id AND
customer_item_id = x_Group_rec.customer_item_id AND
blanket_number = x_Group_rec.setup_terms_rec.blanket_number AND
item_detail_type IN (k_FIRM, k_PAST_DUE_FIRM, k_FORECAST) AND
process_status <> rlm_core_sv.k_PS_PROCESSED
ORDER BY request_date;
g_LineIdTab.DELETE;
g_RSOIdTab.DELETE;
RLM_TPA_SV.InsertRSO(x_Sched_rec, x_Group_rec, v_rso_hdr_id, v_start_date,
v_end_date);
RLM_TPA_SV.InsertRSO(x_Sched_rec, x_Group_rec, v_rso_hdr_id, v_start_date,
v_end_date);
RLM_TPA_SV.InsertRSO(x_Sched_rec, x_Group_rec, v_rso_hdr_id, v_start_date, v_end_date);
RLM_TPA_SV.InsertRSO(x_Sched_rec, x_Group_rec, v_rso_hdr_id, v_start_date, v_end_date);
UpdateLinesWithRSO(x_Sched_rec.schedule_header_id);
SELECT max(decode(oe.open_flag, 'Y', rso_hdr_id, 'N', -1, -99)),
effective_start_date, effective_end_date
FROM RLM_BLANKET_RSO rlm, OE_ORDER_HEADERS oe
WHERE customer_id = p_customer_id AND
rlm.blanket_number = x_Group_rec.setup_terms_rec.blanket_number AND
customer_item_id = p_cust_item_id AND
rlm.rso_hdr_id = oe.header_id(+)
GROUP BY effective_start_date, effective_end_date
ORDER BY effective_start_date, effective_end_date; --Bugfix 6759544
SELECT max(decode(oe.open_flag, 'Y', rso_hdr_id, 'N', -1, -99)),
effective_start_date, effective_end_date
FROM RLM_BLANKET_RSO rlm, OE_ORDER_HEADERS oe
WHERE customer_id = p_customer_id AND
rlm.blanket_number = x_Group_rec.setup_terms_rec.blanket_number AND
rlm.customer_item_id = k_NNULL AND
rlm.rso_hdr_id = oe.header_id(+)
GROUP BY effective_start_date, effective_end_date
ORDER BY effective_start_date, effective_end_date; --Bugfix 6759544
PROCEDURE InsertRSO
==============================================================================*/
PROCEDURE InsertRSO(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
x_Group_rec IN RLM_DP_SV.t_Group_rec,
p_rso_hdr_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE) IS
--
v_customer_item_id NUMBER;
rlm_core_sv.dpush(C_SDEBUG, 'InsertRSO');
INSERT INTO RLM_BLANKET_RSO
(
customer_id, blanket_number, rso_hdr_id,
customer_item_id, effective_start_date, effective_end_date
)
VALUES
(
x_Sched_rec.customer_id, x_Group_rec.setup_terms_rec.blanket_number,
p_rso_hdr_id, v_customer_item_id, TRUNC(p_start_date), TRUNC(p_end_date)
);
rlm_core_sv.dpop(C_SDEBUG,'InsertRSO EXCEPTION: '||SUBSTR(SQLERRM,1,200));
END InsertRSO;
RLM_TPA_SV.InsertOMMessages(x_Sched_rec, x_Group_rec,
x_msg_count, rlm_message_sv.k_INFO_LEVEL,
x_token, x_msg_name);
RLM_TPA_SV.InsertOMMessages(x_Sched_rec, x_Group_rec,
x_msg_count, rlm_message_sv.k_ERROR_LEVEL,
x_token, x_msg_name);
PROCEDURE UpdateLinesWithRSO
==============================================================================*/
--Bug Fix 4254471 Added parameter to procedure
PROCEDURE UpdateLinesWithRSO(x_header_id IN NUMBER) IS
--
v_progress VARCHAR2(3) := '020';
rlm_core_sv.dpush(C_SDEBUG, 'UpdateLinesWithRSO');
UPDATE rlm_interface_lines_all
SET order_header_id = g_RSOIdTab(i)
WHERE line_id = g_LineIdTab(i);
rlm_core_sv.dlog(C_DEBUG, '# of interface lines updated', SQL%ROWCOUNT);
UPDATE rlm_schedule_lines
SET order_header_id = g_RSOIdTab(i)
WHERE interface_line_id = g_LineIdTab(i)
AND header_id = x_header_id; /* 4254471 */
rlm_core_sv.dlog(C_DEBUG, '# of schedule lines updated', SQL%ROWCOUNT);
rlm_message_sv.sql_error('rlm_blanket_sv.UpdateLinesWithRSO', v_Progress);
END UpdateLinesWithRSO;
PROCEDURE InsertOMMessages
==============================================================================*/
PROCEDURE InsertOMMessages(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
x_Group_rec IN RLM_DP_SV.t_Group_rec,
x_msg_count IN NUMBER,
x_msg_level IN VARCHAR2,
x_token IN VARCHAR2,
x_msg_name IN VARCHAR2) IS
--
x_msg VARCHAR2(4000);
rlm_core_sv.dpush(C_SDEBUG, 'InsertOMMessages');
SELECT line_id
INTO v_InterfaceLineId
FROM rlm_interface_lines
WHERE header_id = x_Sched_rec.header_id
AND ship_from_org_id = x_Group_rec.ship_from_org_id
AND ship_to_address_id = x_Group_rec.ship_to_address_id
AND customer_item_id = x_Group_rec.customer_item_id
AND line_id = g_line_id; --Bugfix 6884912
rlm_message_sv.sql_error('rlm_blanket_sv.InsertOMMessages', v_Progress);
END InsertOMMessages;
SELECT ETG.TP_GROUP_CODE
INTO x_tp_group_code
FROM ECE_TP_GROUP ETG,
ECE_TP_HEADERS ETH,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ETG.TP_GROUP_ID = ETH.TP_GROUP_ID
and ETH.TP_HEADER_ID = ACCT_SITE.TP_HEADER_ID
and ACCT_SITE.CUST_ACCOUNT_ID = x_sched_rec.CUSTOMER_ID
and ACCT_SITE.ECE_TP_LOCATION_CODE = x_Sched_rec.ECE_TP_LOCATION_CODE_EXT;
SELECT ece_tp_location_code
INTO x_ship_to_ece_locn_code
FROM HZ_CUST_ACCT_SITES
WHERE CUST_ACCT_SITE_ID = x_group_rec.ship_to_address_id;
SELECT account_number
INTO x_customer_number
FROM HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_sched_rec.customer_id;