[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
last_cum_qty_update_date,
cust_uom_code
INTO x_cum_record.cum_key_id,
x_cum_record.cum_qty,
x_cum_record.cum_qty_to_be_accumulated,
x_cum_record.cum_qty_after_cutoff,
x_cum_record.last_cum_qty_update_date,
x_cum_record.cust_uom_code
FROM RLM_CUST_ITEM_CUM_KEYS
WHERE NVL(ship_from_org_id,0) = NVL(p_ship_from_org_id,0)
AND NVL(ship_to_address_id,0) = NVL(p_ship_to_address_id,0)
AND NVL(intrmd_ship_to_id,0) = NVL(p_intrmd_ship_to_address_id,0)
AND NVL(bill_to_address_id,0) = NVL(p_bill_to_address_id,0)
AND NVL(customer_item_id,0) = NVL(p_customer_item_id,0)
AND NVL(purchase_order_number,' ') = NVL(p_purchase_order_number, ' ')
AND NVL(cust_record_year, ' ') = NVL(p_cust_record_year, ' ')
AND NVL(TRUNC(cum_start_date), sysdate)
= NVL(TRUNC(p_cum_start_date), sysdate)
AND NVL(inactive_flag,'N') = 'N';
SELECT cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
last_cum_qty_update_date,
cust_uom_code
INTO x_cum_record.cum_key_id,
x_cum_record.cum_qty,
x_cum_record.cum_qty_to_be_accumulated,
x_cum_record.cum_qty_after_cutoff,
x_cum_record.last_cum_qty_update_date,
x_cum_record.cust_uom_code
FROM RLM_CUST_ITEM_CUM_KEYS
WHERE ship_from_org_id IS NULL
AND NVL(ship_to_address_id, 0) = NVL(p_ship_to_address_id,0)
AND NVL(intrmd_ship_to_id, 0) = NVL(p_intrmd_ship_to_address_id,0)
AND NVL(bill_to_address_id, 0) = NVL(p_bill_to_address_id,0)
AND NVL(customer_item_id, 0) = NVL(p_customer_item_id,0)
AND NVL(purchase_order_number,' ') = NVL(p_purchase_order_number, ' ')
AND NVL(cust_record_year, ' ') = NVL(p_cust_record_year, ' ')
AND NVL(TRUNC(cum_start_date), sysdate)
= NVL(TRUNC(p_cum_start_date), sysdate)
AND NVL(inactive_flag,'N') = 'N';
rlm_core_sv.dlog(C_DEBUG, 'last_cum_qty_update_date', x_cum_record.last_cum_qty_update_date);
/* Insert a new cum key record if the record does not exist yet and
if the calling progam sets the create cum key flag to 'Y' */
--
BEGIN
--
x_cum_record.cum_qty := 0;
INSERT INTO rlm_cust_item_cum_keys_all(
cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
customer_item_id,
ship_to_address_id,
bill_to_address_id,
intrmd_ship_to_id,
ship_from_org_id,
cum_start_date,
cust_record_year,
purchase_order_number,
last_cum_qty_update_date,
last_updated_by,
creation_date,
created_by,
last_update_date,
cust_uom_code,
org_id)
VALUES(
rlm_cust_item_cum_keys_s.nextval,
x_cum_record.cum_qty,
x_cum_record.cum_qty_to_be_accumulated,
x_cum_record.cum_qty_after_cutoff,
p_customer_item_id,
p_ship_to_address_id,
p_bill_to_address_id,
p_intrmd_ship_to_address_id,
p_ship_from_org_id,
p_cum_start_date,
p_cust_record_year,
p_purchase_order_number,
nvl(rlm_dp_sv.g_dsp_start_time,sysdate), --sysdate, --Bugfix 10053830
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
x_cum_record.cust_uom_code,
v_OrgId);
SELECT rlm_cust_item_cum_keys_s.currval
INTO x_cum_record.cum_key_id
FROM DUAL;
--x_cum_record.last_cum_qty_update_date := SYSDATE; --Bugfix 10053830
x_cum_record.last_cum_qty_update_date := nvl(rlm_dp_sv.g_dsp_start_time,sysdate); --Bugfix 10053830
rlm_core_sv.dlog(C_DEBUG, 'HERE ARE THE NEWLY INSERTED RECORD VALUES');
x_last_cum_qty_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_CUM_QTY_UPDATE_DATE%TYPE,
x_cust_uom_code IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUST_UOM_CODE%TYPE,
x_use_ship_incl_rule_flag IN OUT NOCOPY VARCHAR2,
x_shipment_rule_code IN OUT NOCOPY RLM_CUST_SHIPTO_TERMS.CUM_SHIPMENT_RULE_CODE%TYPE,
x_yesterday_time_cutoff IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
x_last_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
x_as_of_date_time IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE)
--
IS
--
x_cum_key_record cum_key_attrib_rec_type;
/*SELECT SYSDATE
INTO x_cum_record.actual_shipment_date
FROM DUAL;*/
x_cum_record.last_cum_qty_update_date:= x_last_cum_qty_update_date;
x_cum_record.last_update_date := x_last_update_date;
x_last_cum_qty_update_date := x_cum_record.last_cum_qty_update_date;
x_last_update_date := x_cum_record.last_update_date;
SELECT NVL((SUM(shipped_quantity)), 0)
INTO x_cum_record.as_of_date_cum_qty
FROM OE_ORDER_LINES
WHERE veh_cus_item_cum_key_id = x_cum_record.cum_key_id
AND actual_shipment_date >= x_cum_record.cum_start_date
AND actual_shipment_date <= x_cum_record.as_of_date_time
AND source_document_type_id = 5
AND open_flag = 'N'
AND shipped_quantity IS NOT NULL
AND inventory_item_id = x_cum_key_record.inventory_item_id;
SELECT NVL(SUM(transaction_qty), 0)
INTO adj_qty
FROM rlm_cust_item_cum_adj
WHERE cum_key_id = x_cum_record.cum_key_id
AND transaction_date_time >= x_cum_record.cum_start_date
AND transaction_date_time <= x_cum_record.as_of_date_time;
SELECT MAX(actual_shipment_date)
INTO v_cutoff_date
FROM oe_order_lines
WHERE veh_cus_item_cum_key_id = x_cum_record.cum_key_id
AND source_document_type_id = 5
AND open_flag = 'N'
AND shipped_quantity IS NOT NULL
AND actual_shipment_date < x_cum_record.as_of_date_time
AND inventory_item_id = x_cum_key_record.inventory_item_id;
--SELECT TO_DATE(TO_CHAR(SYSDATE-1, 'DD/MM/YYYY') --Bugfix 10053830
SELECT TO_DATE(TO_CHAR(nvl(rlm_dp_sv.g_dsp_start_time,sysdate)-1, 'DD/MM/YYYY') --Bugfix 10053830
||
DECODE(SIGN(rlm_setup_record.cum_yesterd_time_cutoff/1000-1),
-1, '0'||to_char(rlm_setup_record.cum_yesterd_time_cutoff),
to_char(rlm_setup_record.cum_yesterd_time_cutoff)),
'DD/MM/YYYY HH24MI')
INTO x_cum_record.yesterday_time_cutoff
FROM DUAL;
IF TRUNC(x_cum_record.last_cum_qty_update_date) < TRUNC(nvl(rlm_dp_sv.g_dsp_start_time,sysdate)) THEN --Bugfix 10053830
--
x_cum_record.cum_qty := NVL(x_cum_record.cum_qty,0) + NVL(x_cum_record.cum_qty_to_be_accumulated,0);
IF TRUNC(x_cum_record.last_cum_qty_update_date) < TRUNC(nvl(rlm_dp_sv.g_dsp_start_time,sysdate)) THEN --Bugfix 10053830
--
x_cum_record.cum_qty := NVL(x_cum_record.cum_qty,0) + NVL(x_cum_record.cum_qty_to_be_accumulated,0);
SELECT TO_DATE(TO_CHAR(x_cum_record.as_of_date_time, 'DD/MM/YYYY')
||
DECODE(SIGN(rlm_setup_record.cum_yesterd_time_cutoff/1000-1),
-1, '0'||to_char(rlm_setup_record.cum_yesterd_time_cutoff),
to_char(rlm_setup_record.cum_yesterd_time_cutoff)),
'DD/MM/YYYY HH24MI')
INTO x_cum_record.yesterday_time_cutoff
FROM DUAL;
IF x_cum_record.as_of_date_time <> x_cum_record.last_cum_qty_update_date THEN
--
BEGIN
--
SELECT NVL(SUM(shipped_quantity), 0)
INTO x_cum_record.as_of_date_cum_qty
FROM OE_ORDER_LINES
WHERE veh_cus_item_cum_key_id = x_cum_record.cum_key_id
AND actual_shipment_date >= x_cum_record.cum_start_date
AND actual_shipment_date <= v_cutoff_date
AND source_document_type_id = 5
AND open_flag = 'N'
AND shipped_quantity IS NOT NULL
AND inventory_item_id = x_cum_key_record.inventory_item_id;
SELECT NVL(SUM(transaction_qty), 0)
INTO adj_qty
FROM rlm_cust_item_cum_adj
WHERE cum_key_id = x_cum_record.cum_key_id
AND transaction_date_time >= x_cum_record.cum_start_date
AND transaction_date_time <= v_cutoff_date;
x_last_cum_qty_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_CUM_QTY_UPDATE_DATE%TYPE,
x_cust_uom_code IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUST_UOM_CODE%TYPE,
x_use_ship_incl_rule_flag IN OUT NOCOPY VARCHAR2,
x_shipment_rule_code IN OUT NOCOPY RLM_CUST_SHIPTO_TERMS.CUM_SHIPMENT_RULE_CODE%TYPE,
x_yesterday_time_cutoff IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
x_last_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
x_as_of_date_time IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE)
IS
--
x_cum_key_record cum_key_attrib_rec_type;
x_cum_record.last_cum_qty_update_date:= x_last_cum_qty_update_date;
x_cum_record.last_update_date := x_last_update_date;
x_last_cum_qty_update_date := x_cum_record.last_cum_qty_update_date;
x_last_update_date := x_cum_record.last_update_date;
PROCEDURE NAME: UpdateCumKey
=============================================================================*/
PROCEDURE UpdateCumKey (
x_trip_stop_id IN NUMBER,
x_return_status OUT NOCOPY BOOLEAN)
IS
--
--Local Variables
--
v_rlm_setup_terms_record rlm_setup_terms_sv.setup_terms_rec_typ;
e_do_not_update EXCEPTION;
SELECT DISTINCT oelines.header_id, oelines.org_id
FROM WSH_DELIVERY_LEGS wleg,
WSH_DELIVERY_ASSIGNMENTS_V wdass,
WSH_DELIVERY_DETAILS wdel,
OE_ORDER_LINES_ALL oelines
WHERE wleg.pick_up_stop_id = x_trip_stop_id
AND wdass.delivery_id = wleg.delivery_id
AND wdel.delivery_detail_id = wdass.delivery_detail_id
AND oelines.shipped_quantity IS NOT NULL
AND oelines.line_id = wdel.source_line_id
AND wdel.container_flag = 'N' -- 4301944
AND oelines.header_id = wdel.source_header_id -- 4301944
AND oelines.source_document_type_id = 5;
SELECT oelines.line_id,
wleg.delivery_id,
oelines.header_id,
sum(wdel.shipped_quantity),
oelines.actual_shipment_date,
oelines.ordered_item_id,
oelines.inventory_item_id,
oelines.ship_to_org_id,
oelines.intmed_ship_to_org_id,
oelines.ship_from_org_id,
oelines.cust_po_number,
oelines.industry_attribute1,
oelines.invoice_to_org_id,
oelines.actual_shipment_date,
wdel.requested_quantity_uom, -- Bug 4439006
oelines.order_quantity_uom,
oelines.veh_cus_item_cum_key_id,
oelines.source_document_id,
oelines.source_document_line_id,
oelines.org_id
FROM WSH_DELIVERY_LEGS wleg,
WSH_DELIVERY_ASSIGNMENTS_V wdass,
WSH_DELIVERY_DETAILS wdel,
OE_ORDER_LINES_ALL oelines
WHERE wleg.pick_up_stop_id = x_trip_stop_id
AND wdass.delivery_id = wleg.delivery_id
AND wdel.delivery_detail_id = wdass.delivery_detail_id
AND oelines.header_id+0 = v_oe_header_id --Bugfix 14827544
AND oelines.shipped_quantity IS NOT NULL
AND oelines.line_id = wdel.source_line_id
AND wdel.container_flag = 'N' -- 4301944
AND oelines.header_id = wdel.source_header_id -- 4301944
AND oelines.source_document_type_id = 5
group by oelines.line_id,
wleg.delivery_id,
oelines.header_id,
oelines.actual_shipment_date,
oelines.ordered_item_id,
oelines.inventory_item_id,
oelines.ship_to_org_id,
oelines.intmed_ship_to_org_id,
oelines.ship_from_org_id,
oelines.cust_po_number,
oelines.industry_attribute1,
oelines.invoice_to_org_id,
oelines.actual_shipment_date,
wdel.requested_quantity_uom, -- Bug 4439006
oelines.order_quantity_uom,
oelines.veh_cus_item_cum_key_id,
oelines.source_document_id,
oelines.source_document_line_id,
oeLines.org_id;
rlm_core_sv.dpush(C_SDEBUG, 'UpdateCumKey');
savepoint updatecumkey; --bug 3719088
rlm_core_sv.dlog(C_DEBUG, 'Number of oe headers to update: ', hdr_count);
SELECT CUST_ACCT_SITE_ID
INTO v_cum_key_record.ship_to_address_id
FROM HZ_CUST_SITE_USES
WHERE site_use_id = v_ship_to_site_use_id
AND site_use_code = 'SHIP_TO';
SELECT CUST_ACCT_SITE_ID
INTO v_cum_key_record.intrmd_ship_to_address_id
FROM HZ_CUST_SITE_USES
WHERE site_use_id = v_intrmd_ship_to_site_use_id
AND site_use_code = 'SHIP_TO';
SELECT CUST_ACCT_SITE_ID
INTO v_cum_key_record.bill_to_address_id
FROM HZ_CUST_SITE_USES
WHERE site_use_id = v_bill_to_site_use_id
AND site_use_code = 'BILL_TO';
/* SELECT DISTINCT ACCT_SITE.CUST_ACCOUNT_ID
INTO v_cum_key_record.customer_id
FROM HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = v_cum_key_record.ship_to_address_id;
SELECT DISTINCT sold_to_org_id
INTO v_cum_key_record.customer_id
FROM oe_order_headers_all oeh
WHERE oeh.header_id = v_oe_header_id;
--Check if this delivery line has actually been updated
--
/* Initialize v_new_ship_count. This variable is used to keep track of the
shipment lines that have the same cum_key_id and
shipment_rule_code = 'AS_OF_PRIOR' */
------------------------------------------------------------+
BEGIN
--
SELECT v_new_ship_count(v_cum_record.cum_key_id) + 1
INTO v_new_ship_count(v_cum_record.cum_key_id)
FROM DUAL;
SELECT v_new_ship_count(v_cum_record.cum_key_id) + 1
INTO v_new_ship_count(v_cum_record.cum_key_id)
FROM DUAL;
The Cum key should be updated with qty specified in Cum key uom. */
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(C_DEBUG, 'inventory_item_id', v_cum_key_record.inventory_item_id);
rlm_core_sv.dlog(C_DEBUG, 'last_cum_qty_update_date', v_cum_record.last_cum_qty_update_date);
/* Update the CUM related quantity in
RLM_CUS_ITEM_CUM_KEY_ALL table */
--
UPDATE RLM_CUST_ITEM_CUM_KEYS_ALL
SET cum_qty = v_cum_record.cum_qty,
cum_qty_to_be_accumulated = v_cum_record.cum_qty_to_be_accumulated,
cum_qty_after_cutoff = v_cum_record.cum_qty_after_cutoff,
--last_cum_qty_update_date = sysdate, --Bugfix 10053830
last_cum_qty_update_date = nvl(rlm_dp_sv.g_dsp_start_time,sysdate), --Bugfix 10053830
last_update_login = fnd_global.login_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE cum_key_id = v_cum_record.cum_key_id;
oe_order_grp.process order api to update
oe_order_lines table */
--
counter := counter + 1;
g_oe_line_tbl(counter).operation := oe_globals.G_OPR_UPDATE;
v_msg_text := 'RLM_TRIP_NO_UPDATE';
x_message_name => 'RLM_TRIP_NO_UPDATE',
x_text => v_msg_text);
v_msg_text := 'RLM_TRIP_NO_UPDATE';
/* Call OE_Order_GRP.Process_Order procedure to update OE_ORDER_LINES
table by passing the g_oe_line_tbl structure that has been prepared
inside the loop above */
--
--Pass only g_oe_line_tbl. The rest uses default values
--
OE_Order_GRP.Process_order(
p_api_version_number => x_oe_api_version,
p_init_msg_list => FND_API.G_TRUE,
p_return_values => FND_API.G_FALSE,
--p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
------------------------------------------
p_line_tbl => g_oe_line_tbl,
------------------------------------------
x_header_rec => g_oe_header_out_rec,
x_header_val_rec => g_oe_header_val_out_rec,
x_Header_Adj_tbl => g_oe_Header_Adj_out_tbl,
x_Header_Adj_val_tbl => g_oe_Header_Adj_val_out_tbl,
x_Header_price_Att_tbl => g_Header_price_Att_out_tbl,
x_Header_Adj_Att_tbl => g_Header_Adj_Att_out_tbl,
x_Header_Adj_Assoc_tbl => g_Header_Adj_Assoc_out_tbl,
x_Header_Scredit_tbl => g_oe_Header_Scredit_out_tbl,
x_Header_Scredit_val_tbl => g_oe_Hdr_Scdt_val_out_tbl,
x_line_tbl => l_oe_line_tbl_out,
x_line_val_tbl => g_oe_line_val_out_tbl,
x_Line_Adj_tbl => g_oe_line_Adj_out_tbl,
x_Line_Adj_val_tbl => g_oe_line_Adj_val_out_tbl,
x_Line_price_Att_tbl => g_Line_price_Att_out_tbl,
x_Line_Adj_Att_tbl => g_Line_Adj_Att_out_tbl,
x_Line_Adj_Assoc_tbl => g_Line_Adj_Assoc_out_tbl,
x_Line_Scredit_tbl => g_oe_line_scredit_out_tbl,
x_Line_Scredit_val_tbl => g_oe_line_scredit_val_out_tbl,
x_Lot_Serial_tbl => g_oe_lot_serial_out_tbl,
x_Lot_Serial_val_tbl => g_oe_lot_serial_val_out_tbl,
x_Action_Request_tbl => g_oe_Action_Request_out_Tbl);
ROLLBACK to updatecumkey;
ROLLBACK to updatecumkey;
ROLLBACK to updatecumkey;
ROLLBACK to updatecumkey;
ROLLBACK to updatecumkey;
SELECT location_code
INTO v_hr_location_code
FROM hr_locations hr, wsh_trip_stops tstop
WHERE tstop.stop_id = x_trip_stop_id
AND hr.location_id = tstop.stop_location_id;
ROLLBACK to updatecumkey;
ROLLBACK to updatecumkey;
ROLLBACK to updatecumkey;
ROLLBACK to updatecumkey;
ROLLBACK to updatecumkey;
END UpdateCumKey;
PROCEDURE NAME: UpdateCumKeyClient
=============================================================================*/
PROCEDURE UpdateCumKeyClient (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
x_trip_stop_id IN NUMBER)
IS
--
v_return_status BOOLEAN;
RLM_TPA_SV.UpdateCumKey( x_trip_stop_id,
v_return_status);
END UpdateCumKeyClient;
SELECT DISTINCT ACCT_SITE.CUST_ACCOUNT_ID
INTO v_customer_id
FROM HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = i_ship_to_address_id;
SELECT line.ship_from_org_id,
line.ship_to_address_id,
line.customer_item_id,
header.customer_id,
line.cust_po_number, --Bugfix 7007638
line.industry_attribute1 --Bugfix 7007638
INTO v_ship_from_org_id,
v_ship_to_address_id,
v_customer_item_id,
v_customer_id,
v_cust_po_number, --Bugfix 7007638
v_industry_attribute1 --Bugfix 7007638
FROM rlm_schedule_lines_all line,
rlm_schedule_headers header
WHERE line.header_id = i_schedule_header_id
AND line.line_id = i_schedule_line_id
AND line.header_id = header.header_id;
SELECT start_date_time, industry_attribute1
INTO o_cum_start_date, o_cust_record_year
FROM rlm_schedule_lines
WHERE header_id = i_schedule_header_id
AND ship_from_org_id = v_ship_from_org_id
AND ship_to_address_id = v_ship_to_address_id
AND customer_item_id = v_customer_item_id
AND NVL(cust_po_number,' ') = NVL(v_cust_po_number,' ') --Bugfix 7007638
AND NVL(industry_attribute1,' ') = NVL(v_industry_attribute1,' ') --Bugfix 7007638
AND item_detail_type = '4'
AND item_detail_subtype = 'CUM';
SELECT acct_site.ece_tp_location_code,
c.tp_group_code
INTO x_ship_to_ece_locn_code,
x_tp_group_code
FROM HZ_CUST_ACCT_SITES ACCT_SITE,
ece_tp_headers b,
ece_tp_group c
WHERE ACCT_SITE.CUST_ACCOUNT_ID = x_cum_key_record.customer_id
AND ACCT_SITE.CUST_ACCT_SITE_ID = x_cum_key_record.ship_to_address_id
AND b.tp_header_id = acct_site.tp_header_id
AND c.tp_group_id = b.tp_group_id;
SELECT acct_site.ece_tp_location_code
INTO x_bill_to_ece_locn_code
FROM HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = x_cum_key_record.bill_to_address_id
AND ACCT_SITE.CUST_ACCOUNT_ID = x_cum_key_record.customer_id;
SELECT acct_site.ece_tp_location_code
INTO x_inter_ship_to_ece_locn_code
FROM HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = x_cum_key_record.intrmd_ship_to_address_id
AND ACCT_SITE.CUST_ACCOUNT_ID = x_cum_key_record.customer_id;
SELECT CUST_ACCT.ACCOUNT_NUMBER
INTO x_customer_number
FROM HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.CUST_ACCOUNT_ID = x_cum_key_record.customer_id;
SELECT oelines.sold_to_org_id,
oelines.ship_to_org_id,
oelines.intmed_ship_to_org_id,
oelines.invoice_to_org_id
FROM WSH_DELIVERY_LEGS wleg,
WSH_DELIVERY_ASSIGNMENTS_V wdass,
WSH_DELIVERY_DETAILS wdel,
OE_ORDER_LINES oelines
WHERE wleg.pick_up_stop_id = x_trip_stop_id
AND wdass.delivery_id = wleg.delivery_id
AND wdel.delivery_detail_id = wdass.delivery_detail_id
AND oelines.shipped_quantity IS NOT NULL
AND oelines.line_id = wdel.source_line_id
AND wdel.container_flag = 'N' -- 4301944
AND oelines.header_id = wdel.source_header_id -- 4301944
AND oelines.source_document_type_id = 5;
SELECT CUST_ACCT.ACCOUNT_NUMBER
INTO x_customer_number
FROM HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.CUST_ACCOUNT_ID = v_customer_id;
SELECT CUST_ACCT_SITE_ID
INTO v_ship_to_address_id
FROM HZ_CUST_SITE_USES
WHERE site_use_id = v_ship_to_org_id
AND site_use_code = 'SHIP_TO';
SELECT acct_site.ece_tp_location_code,
c.tp_group_code
INTO x_ship_to_ece_locn_code,
x_tp_group_code
FROM HZ_CUST_ACCT_SITES ACCT_SITE,
ece_tp_headers b,
ece_tp_group c
WHERE ACCT_SITE.CUST_ACCOUNT_ID = v_customer_id
AND ACCT_SITE.CUST_ACCT_SITE_ID = v_ship_to_address_id
AND b.tp_header_id = acct_site.tp_header_id
AND c.tp_group_id = b.tp_group_id;
SELECT CUST_ACCT_SITE_ID
INTO v_bill_to_address_id
FROM HZ_CUST_SITE_USES
WHERE site_use_id = v_bill_to_org_id
AND site_use_code = 'BILL_TO';
SELECT ACCT_SITE.ece_tp_location_code
INTO x_bill_to_ece_locn_code
FROM HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = v_customer_id
AND ACCT_SITE.CUST_ACCT_SITE_ID = v_bill_to_address_id;
SELECT CUST_ACCT_SITE_ID
INTO v_intrmd_ship_to_address_id
FROM HZ_CUST_SITE_USES
WHERE site_use_id = v_intmed_ship_to_org_id
AND site_use_code = 'SHIP_TO';
SELECT ACCT_SITE.ece_tp_location_code
INTO x_inter_ship_to_ece_locn_code
FROM HZ_CUST_ACCT_SITES ACCT_SITE
WHERE ACCT_SITE.CUST_ACCOUNT_ID = v_customer_id
AND ACCT_SITE.CUST_ACCT_SITE_ID = v_intrmd_ship_to_address_id;
completes without error then call UpdateOldKey to recalculate the old
cums which have been reset.
=============================================================================*/
PROCEDURE ResetCum (
p_org_id IN NUMBER,
x_ship_from_org_id IN NUMBER,
x_customer_id IN NUMBER,
x_ship_to_org_id IN NUMBER,
x_intrmd_ship_to_org_id IN NUMBER,
x_bill_to_org_id IN NUMBER,
x_customer_item_id IN NUMBER,
x_transaction_start_date IN DATE,
x_transaction_end_date IN DATE,
x_return_status OUT NOCOPY BOOLEAN)
IS
--
v_rlm_setup_terms_rec rlm_setup_terms_sv.setup_terms_rec_typ;
SELECT CUST_ACCT_SITE_ID
INTO v_ship_to_address_id
FROM HZ_CUST_SITE_USES
WHERE site_use_id = x_ship_to_org_id
AND site_use_code = 'SHIP_TO';
SELECT CUST_ACCT_SITE_ID
INTO v_intrmd_ship_to_address_id
FROM HZ_CUST_SITE_USES
WHERE site_use_id = x_intrmd_ship_to_org_id
AND site_use_code = 'SHIP_TO';
SELECT CUST_ACCT_SITE_ID
INTO v_bill_to_address_id
FROM HZ_CUST_SITE_USES
WHERE site_use_id = x_bill_to_org_id
AND site_use_code = 'BILL_TO';
-- Determine the select criteria based on the cum org level code
--
--
IF v_rlm_setup_terms_rec.cum_org_level_code = 'BILL_TO_SHIP_FROM'
THEN
--
p_ship_from_org_id := x_ship_from_org_id;
rlm_core_sv.dlog(C_SDEBUG, 'The criteria used to select the shipment ' ||'lines');
v_tmp_old_table.DELETE;
g_cum_oe_lines.DELETE;
we can delete these keys from the table passed to UpdateOldKey
so that these keys won't get processed. If everything goes well
then delete this table
*/
v_tmp_old_table(v_index2) := p_cum_key_id;
UPDATE rlm_cust_item_cum_adj
SET cum_key_id = v_cum_records(v_index).cum_key_id
WHERE cum_key_id = p_cum_key_id
AND transaction_date_time <= nvl(v_end_date_time,sysdate)
AND transaction_date_time >= x_transaction_start_date;
g_oe_tmp_line_tbl.DELETE(i);
v_tmp_old_table.DELETE;
ELSE --if there are no problems then update the CUM table
--
-- get all adjustments after the last shipment
IF v_adjustment_date IS NULL THEN
--
SELECT SUM(transaction_qty)
INTO adj_qty
FROM rlm_cust_item_cum_adj
WHERE cum_key_id = v_cum_records(v_index).cum_key_id
AND transaction_date_time <= sysdate;
SELECT SUM(transaction_qty)
INTO adj_qty
FROM rlm_cust_item_cum_adj
WHERE cum_key_id = v_cum_records(v_index).cum_key_id
AND transaction_date_time >= v_adjustment_date
AND transaction_date_time <= sysdate;
UPDATE rlm_cust_item_cum_keys
SET cum_qty = v_cum_records(v_index).cum_qty,
cum_qty_to_be_accumulated =
v_cum_records(v_index).cum_qty_to_be_accumulated,
cum_qty_after_cutoff =
v_cum_records(v_index).cum_qty_after_cutoff,
last_cum_qty_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE cum_key_id = v_cum_records(v_index).cum_key_id
AND NVL(inactive_flag,'N') = 'N';
/* Call OE_Order_GRP.Process_Order procedure to update OE_ORDER_LINES
table by passing the g_oe_line_tbl structure that has been prepared
this loop calls the process_order API once per each header_id,
since the table is sorted by header_id then this loop
calls the process_order once the header_id is changed */
--
IF g_oe_tmp_line_tbl(v_index3).header_id = v_header_id THEN --{
--
g_oe_line_tbl(v_index2) := g_oe_tmp_line_tbl(v_index3);
'Order line is updated successfully');
'Order line is updated successfully');
g_oe_line_tbl.DELETE;
UpdateOldKey( v_old_cum_records,
v_cum_records(1).shipment_rule_code,
v_cum_records(1).yesterday_time_cutoff,
v_cum_key_record,
p_ship_from_org_id,
p_ship_to_org_id,
p_intmed_ship_to_org_id,
p_bill_to_org_id,
x_customer_item_id,
v_return_status);
v_msg_data := 'Update old CUM key error. Rollback';
UpdateOldKey( v_old_cum_records,
v_cum_records(1).shipment_rule_code,
v_cum_records(1).yesterday_time_cutoff,
v_cum_key_record,
p_ship_from_org_id,
p_ship_to_org_id,
p_intmed_ship_to_org_id,
p_bill_to_org_id,
x_customer_item_id,
v_return_status);
v_msg_data := 'Update old CUM key error. Rollback';
nor any shipments(if there is any shipment the field last_cum_qty_update_date
would be within the time frame). These records would be sorted, so that all
corresponding Cums are in sorted together(for example same customer items
may be sorted together). The program recognizes the first record of each
group as the newly created CUM and the reset of the group as the CUMs which
are being adjusted. It then puts all the new cums in one table and all the
old one in a different table.
PARAMETERS:
** x_rlm_setup_terms_record IN
This is setupterms generated from the resetCums parameters
** x_terms_level IN
This is the same as parameter x_terms_definition_level of
rlm_setup_terms_sv.get_setup_terms
** x_cum_key_record IN
This records would containe the followings if it could be derived in
resetcum
bill_to_address_id, ship_to_address_id, intrmd_ship_to_address_id,
ship_from_org_id,customer_item_id,customer_id
** x_transaction_start_date IN
same as parameter in resetcum
** x_transaction_end_date IN
same as parameter in resetcum would be defaulted to sysdate
** x_cum_records OUT NOCOPY
These are records of all new cum_keys created
** x_old_cum_records OUT NOCOPY
These are all old_cums which either have shipment or manual adjustments
** x_counter OUT NOCOPY
This is a table that indicates the relation ship between x_cum_records
and x_old_cum_records. For example, if x_cum_records(3) has
2 records in the old cums table x_old_cum_records, then
x_counter(3) would have the value 2
** x_return_status OUT NOCOPY
1 if any cums found to be adjusted, 0 if no cum
=============================================================================*/
PROCEDURE GetCums (
x_rlm_setup_terms_record IN rlm_setup_terms_sv.setup_terms_rec_typ,
x_terms_level IN VARCHAR2,
x_cum_key_record IN OUT NOCOPY rlm_cum_sv.cum_key_attrib_rec_type,
x_transaction_start_date IN DATE,
x_transaction_end_date IN DATE ,
x_ship_from_org_id IN NUMBER,
x_ship_to_org_id IN NUMBER,
x_intmed_ship_to_org_id IN NUMBER,
x_bill_to_org_id IN NUMBER,
x_cum_records OUT NOCOPY RLM_CUM_SV.t_cums,
x_old_cum_records OUT NOCOPY RLM_CUM_SV.t_cums,
x_counter OUT NOCOPY RLM_CUM_SV.t_new_ship_count,
x_return_status OUT NOCOPY NUMBER)
IS
v_cum_control_code rlm_cust_shipto_terms.cum_control_code%TYPE;
v_select VARCHAR2(3600);
v_select := 'SELECT cum_key_id, cum_qty, cum_qty_to_be_accumulated, cum_qty_after_cutoff,
last_cum_qty_update_date,cust_uom_code,cum_start_date,
cust_record_year,purchase_order_number ,customer_item_id
FROM RLM_CUST_ITEM_CUM_KEYS
';
v_statment := v_select || v_where_clause;
v_tmp_cum_record.last_cum_qty_update_date,
v_tmp_cum_record.cust_uom_code,
v_tmp_cum_record.cum_start_date,
v_new_cust_record_year,
v_new_purchase_order_number,
v_new_customer_item_id;
x_cum_records(v_cum_rec_ctr).last_cum_qty_update_date :=
v_tmp_cum_record.last_cum_qty_update_date;
SELECT COUNT(*)
INTO v_shipment_count
FROM oe_order_lines
WHERE (ship_from_org_id = x_ship_from_org_id
OR x_ship_from_org_id IS NULL)
AND (ship_to_org_id = x_ship_to_org_id
OR x_ship_to_org_id IS NULL)
AND (intmed_ship_to_org_id = x_intmed_ship_to_org_id
OR x_intmed_ship_to_org_id IS NULL)
AND (invoice_to_org_id = x_bill_to_org_id
OR x_bill_to_org_id IS NULL)
AND ordered_item_id = v_new_customer_item_id
AND actual_shipment_date >= x_transaction_start_date
AND actual_shipment_date <= NVL(x_transaction_end_date, SYSDATE)
AND open_flag = 'N'
AND shipped_quantity IS NOT NULL
AND source_document_type_id = 5
AND veh_cus_item_cum_key_id = v_tmp_cum_record.cum_key_id;
SELECT COUNT(*)
INTO v_exist
FROM rlm_cust_item_cum_adj
WHERE cum_key_id = v_tmp_cum_record.cum_key_id
AND transaction_date_time <= nvl(x_transaction_end_date,sysdate)
AND transaction_date_time >= x_transaction_start_date;
x_old_cum_records(v_old_cum_ctr).last_cum_qty_update_date :=
v_tmp_cum_record.last_cum_qty_update_date;
/* If the last new_cum did not have any old_cum then delete it from the
table */
IF Not (x_counter.EXISTS(v_cum_rec_ctr)) THEN
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(C_SDEBUG,'could not find old cums for cum key id: ',
x_cum_records(v_cum_rec_ctr).cum_key_id);
x_cum_records.DELETE(v_cum_rec_ctr);
x_cum_records.DELETE(v_cum_rec_ctr);
x_counter.DELETE(v_cum_rec_ctr);
SELECT SUM(transaction_qty)
INTO adj_qty
FROM rlm_cust_item_cum_adj
WHERE cum_key_id = x_cum_records(x_index).cum_key_id
AND transaction_date_time <
x_cum_records(x_index).actual_shipment_date;
SELECT SUM(transaction_qty)
INTO adj_qty
FROM rlm_cust_item_cum_adj
WHERE cum_key_id = x_cum_records(x_index).cum_key_id
AND transaction_date_time >= x_adjustment_date
AND transaction_date_time <
x_cum_records(x_index).actual_shipment_date;
g_oe_tmp_line_tbl(x_counter).operation := oe_globals.G_OPR_UPDATE;
/* Select an element from the middle. */
IF sortType = RLM_CUM_SV.C_line_table_type THEN
pivot_n := g_oe_tmp_line_tbl(TRUNC((First + Last) / 2)).header_id;
SELECT header_id,
line_id,
shipped_quantity,
actual_shipment_date,
order_quantity_uom,
org_id
FROM oe_order_lines_all
WHERE (ship_from_org_id = x_ship_from_org_id
OR x_ship_from_org_id IS NULL)
AND (ship_to_org_id = x_ship_to_org_id
OR x_ship_to_org_id IS NULL)
AND (intmed_ship_to_org_id = x_intmed_ship_to_org_id
OR x_intmed_ship_to_org_id IS NULL)
AND (invoice_to_org_id = x_bill_to_org_id
OR x_bill_to_org_id IS NULL)
AND (ordered_item_id = x_customer_item_id
OR x_customer_item_id IS NULL)
AND (actual_shipment_date >= x_transaction_start_date
OR x_transaction_start_date IS NULL)
AND (actual_shipment_date <= NVL(x_transaction_end_date, SYSDATE))
AND shipped_quantity IS NOT NULL
AND open_flag = 'N'
AND source_document_type_id = 5
AND veh_cus_item_cum_key_id = x_cum_key_id
AND inventory_item_id = x_inventory_item_id
ORDER BY actual_shipment_date;
PROCEDURE NAME: UpdateOldKey
DESCRIPTION: This procedure will be called to update CUM Key ID record(s) t
hat
are previously attached to order lines
PARAMETERS: x_old_cum_table IN t_old_cum
============================================================================*/
--{
PROCEDURE UpdateOldKey(x_old_cum_records IN OUT NOCOPY RLM_CUM_SV.t_cums,
x_shipment_rule_code IN VARCHAR2,
x_cutoff_time IN DATE,
x_cum_key_record IN OUT NOCOPY cum_key_attrib_rec_type,
x_ship_from_org_id IN NUMBER,
x_ship_to_org_id IN NUMBER,
x_intmed_ship_to_org_id IN NUMBER,
x_bill_to_org_id IN NUMBER,
x_customer_item_id IN NUMBER,
x_return_status OUT NOCOPY BOOLEAN)
IS
v_index NUMBER;
rlm_core_sv.dpush(C_DEBUG, 'UpdateOldKey');
g_oe_tmp_line_tbl.DELETE;
g_oe_line_tbl.DELETE;
g_cum_oe_lines.DELETE;
fnd_file.put_line(fnd_file.log,'Failed to update Old CUMs, rolling back');
SELECT SUM(transaction_qty)
INTO adj_qty
FROM rlm_cust_item_cum_adj
WHERE cum_key_id = x_old_cum_records(cum_records_counter).cum_key_id
AND transaction_date_time <= sysdate
AND ((transaction_date_time >= v_adjustment_date)
OR (v_adjustment_date IS NULL));
UPDATE rlm_cust_item_cum_keys
SET cum_qty = x_old_cum_records(cum_records_counter).cum_qty,
cum_qty_to_be_accumulated =
x_old_cum_records(cum_records_counter).cum_qty_to_be_accumulated,
cum_qty_after_cutoff =
x_old_cum_records(cum_records_counter).cum_qty_after_cutoff,
last_cum_qty_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE cum_key_id=x_old_cum_records(cum_records_counter).cum_key_id
AND NVL(inactive_flag,'N') = 'N';
'update old cum');
/* Call OE_Order_GRP.Process_Order procedure to update OE_ORDER_LINES
table by passing the g_oe_line_tbl structure that has been prepared
this loop calls the process_order API once per each header_id,
since the table is sorted by header_id then this loop
calls the process_order once the header_id is changed */
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(C_DEBUG,'processing header_id',v_header_id);
'Order line is updated successfully');
'Order line is updated successfully');
g_oe_line_tbl.DELETE;
rlm_core_sv.dpop(C_DEBUG, 'UpdateOldKey e_noshipment ');
rlm_core_sv.dpop(C_DEBUG, 'UpdateOldKey e_SetSupplierCum');
rlm_core_sv.dpop(C_DEBUG, 'UpdateOldKey G_EXC_ERROR');
rlm_core_sv.dpop(C_DEBUG, 'UpdateOldKey G_EXC_UNEXPECTED_ERROR');
rlm_core_sv.dpop(C_DEBUG, 'UpdateOldKey others: '|| substr(SQLERRM,1,200));
END UpdateOldKey;--}
SELECT Master_Organization_Id, Inventory_Item_Id, Inactive_Flag
FROM MTL_CUSTOMER_ITEM_XREFS
WHERE Customer_Item_Id = x_customer_item_id
ORDER BY Preference_Number ASC;
SELECT cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
last_cum_qty_update_date,
cust_uom_code,
cum_start_date,
cust_record_year,
purchase_order_number
FROM RLM_CUST_ITEM_CUM_KEYS
WHERE NVL(ship_from_org_id,0) = NVL(p_ship_from_org_id,0)
AND NVL(ship_to_address_id,0) = NVL(p_ship_to_address_id,0)
AND NVL(intrmd_ship_to_id,0)=
NVL(p_intrmd_ship_to_address_id,0)
AND NVL(bill_to_address_id,0)= NVL(p_bill_to_address_id,0)
AND NVL(customer_item_id,0) = NVL(p_customer_item_id,0)
AND cum_start_date IS NOT NULL
--AND (cum_start_date < SYSDATE --Bugfix 10053830
AND (cum_start_date < nvl(rlm_dp_sv.g_dsp_start_time,sysdate) --Bugfix 10053830
OR x_called_from_vd = rlm_cum_sv.k_CalledByVD
)
AND purchase_order_number IS NULL
AND cust_record_year IS NULL
AND NVL(inactive_flag,'N') = 'N'
ORDER BY creation_date DESC;
SELECT cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
last_cum_qty_update_date,
cust_uom_code,
cum_start_date,
cust_record_year,
purchase_order_number
FROM RLM_CUST_ITEM_CUM_KEYS
WHERE ship_from_org_id IS NULL
AND NVL(ship_to_address_id,0)= NVL(p_ship_to_address_id,0)
AND NVL(intrmd_ship_to_id,0) =
NVL(p_intrmd_ship_to_address_id,0)
AND NVL(bill_to_address_id,0) = NVL(p_bill_to_address_id,0)
AND NVL(customer_item_id,0)= NVL(p_customer_item_id,0)
AND cum_start_date IS NOT NULL
--AND (cum_start_date < SYSDATE --Bugfix 10053830
AND (cum_start_date < nvl(rlm_dp_sv.g_dsp_start_time,sysdate) --Bugfix 10053830
OR x_called_from_vd = rlm_cum_sv.k_CalledByVD
)
AND purchase_order_number IS NULL
AND cust_record_year IS NULL
AND NVL(inactive_flag,'N') = 'N'
ORDER BY creation_date DESC;
SELECT cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
last_cum_qty_update_date,
cust_uom_code,
cum_start_date,
cust_record_year,
purchase_order_number
FROM RLM_CUST_ITEM_CUM_KEYS
WHERE NVL(ship_from_org_id,0)= NVL(p_ship_from_org_id,0)
AND NVL(ship_to_address_id,0)= NVL(p_ship_to_address_id,0)
AND NVL(intrmd_ship_to_id,0)
= NVL(p_intrmd_ship_to_address_id,0)
AND NVL(bill_to_address_id,0)= NVL(p_bill_to_address_id,0)
AND NVL(customer_item_id,0)= NVL(p_customer_item_id,0)
AND cum_start_date IS NOT NULL
--AND (cum_start_date < SYSDATE --Bugfix 10053830
AND (cum_start_date < nvl(rlm_dp_sv.g_dsp_start_time,sysdate) --Bugfix 10053830
OR x_called_from_vd = rlm_cum_sv.k_CalledByVD
)
AND purchase_order_number IS NULL
AND cust_record_year IS NOT NULL
AND (cust_record_year = p_cust_record_year
OR p_cust_record_year IS NULL
)
AND NVL(inactive_flag,'N') = 'N'
ORDER BY creation_date DESC;
SELECT cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
last_cum_qty_update_date,
cust_uom_code,
cum_start_date,
cust_record_year,
purchase_order_number
FROM RLM_CUST_ITEM_CUM_KEYS
WHERE ship_from_org_id IS NULL
AND NVL(ship_to_address_id,0) = NVL(p_ship_to_address_id,0)
AND NVL(intrmd_ship_to_id,0)
= NVL(p_intrmd_ship_to_address_id,0)
AND NVL(bill_to_address_id,0) = NVL(p_bill_to_address_id,0)
AND NVL(customer_item_id,0) = NVL(p_customer_item_id,0)
AND cum_start_date IS NOT NULL
--AND (cum_start_date < SYSDATE --Bugfix 10053830
AND (cum_start_date < nvl(rlm_dp_sv.g_dsp_start_time,sysdate) --Bugfix 10053830
OR x_called_from_vd = rlm_cum_sv.k_CalledByVD
)
AND purchase_order_number IS NULL
AND cust_record_year IS NOT NULL
AND (cust_record_year = p_cust_record_year
OR p_cust_record_year IS NULL
)
AND NVL(inactive_flag,'N') = 'N'
ORDER BY creation_date DESC;
SELECT cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
last_cum_qty_update_date,
cust_uom_code,
cum_start_date,
cust_record_year,
purchase_order_number
FROM RLM_CUST_ITEM_CUM_KEYS
WHERE NVL(ship_from_org_id,0)= NVL(p_ship_from_org_id,0)
AND NVL(ship_to_address_id,0)= NVL(p_ship_to_address_id,0)
AND NVL(intrmd_ship_to_id,0)
= NVL(p_intrmd_ship_to_address_id,0)
AND NVL(bill_to_address_id,0)= NVL(p_bill_to_address_id,0)
AND NVL(customer_item_id,0) = NVL(p_customer_item_id,0)
AND cum_start_date IS NOT NULL
--AND (cum_start_date < SYSDATE --Bugfix 10053830
AND (cum_start_date < nvl(rlm_dp_sv.g_dsp_start_time,sysdate) --Bugfix 10053830
OR x_called_from_vd = rlm_cum_sv.k_CalledByVD
)
AND purchase_order_number IS NOT NULL
AND (purchase_order_number = p_purchase_order_number
OR p_purchase_order_number IS NULL
)
AND cust_record_year IS NULL
AND NVL(inactive_flag,'N') = 'N'
ORDER BY creation_date DESC;
SELECT cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
last_cum_qty_update_date,
cust_uom_code,
cum_start_date,
cust_record_year,
purchase_order_number
FROM RLM_CUST_ITEM_CUM_KEYS
WHERE ship_from_org_id IS NULL
AND NVL(ship_to_address_id,0) = NVL(p_ship_to_address_id,0)
AND NVL(intrmd_ship_to_id,0)
= NVL(p_intrmd_ship_to_address_id,0)
AND NVL(bill_to_address_id,0) = NVL(p_bill_to_address_id,0)
AND NVL(customer_item_id,0)= NVL(p_customer_item_id,0)
AND cum_start_date IS NOT NULL
--AND (cum_start_date < SYSDATE --Bugfix 10053830
AND (cum_start_date < nvl(rlm_dp_sv.g_dsp_start_time,sysdate) --Bugfix 10053830
OR x_called_from_vd = rlm_cum_sv.k_CalledByVD
)
AND purchase_order_number IS NOT NULL
AND (purchase_order_number = p_purchase_order_number
OR p_purchase_order_number IS NULL
)
AND cust_record_year IS NULL
AND NVL(inactive_flag,'N') = 'N'
ORDER BY creation_date DESC;
x_cum_record.last_cum_qty_update_date,
x_cum_record.cust_uom_code,
p_cum_start_date,
p_cust_record_year,
p_purchase_order_number;
x_cum_record.last_cum_qty_update_date,
x_cum_record.cust_uom_code,
p_cum_start_date,
p_cust_record_year,
p_purchase_order_number;
x_cum_record.last_cum_qty_update_date,
x_cum_record.cust_uom_code,
p_cum_start_date,
p_cust_record_year,
p_purchase_order_number;
x_cum_record.last_cum_qty_update_date,
x_cum_record.cust_uom_code,
p_cum_start_date,
p_cust_record_year,
p_purchase_order_number;
x_cum_record.last_cum_qty_update_date,
x_cum_record.cust_uom_code,
p_cum_start_date,
p_cust_record_year,
p_purchase_order_number;
x_cum_record.last_cum_qty_update_date,
x_cum_record.cust_uom_code,
p_cum_start_date,
p_cust_record_year,
p_purchase_order_number;
SELECT cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
last_cum_qty_update_date,
cust_uom_code
INTO x_cum_record.cum_key_id,
x_cum_record.cum_qty,
x_cum_record.cum_qty_to_be_accumulated,
x_cum_record.cum_qty_after_cutoff,
x_cum_record.last_cum_qty_update_date,
x_cum_record.cust_uom_code
FROM RLM_CUST_ITEM_CUM_KEYS
WHERE NVL(ship_from_org_id,0) = NVL(p_ship_from_org_id,0)
AND NVL(ship_to_address_id,0)
= NVL(p_ship_to_address_id,0)
AND NVL(intrmd_ship_to_id,0)
= NVL(p_intrmd_ship_to_address_id,0)
AND NVL(bill_to_address_id,0)
= NVL(p_bill_to_address_id,0)
AND NVL(customer_item_id,0)
= NVL(p_customer_item_id,0)
AND purchase_order_number = NVL(p_purchase_order_number, ' ')
AND NVL(cust_record_year, ' ')
= NVL(p_cust_record_year, ' ')
AND NVL(cum_start_date, sysdate)
= NVL(p_cum_start_date, sysdate)
AND purchase_order_number IS NOT NULL
AND NVL(inactive_flag,'N') = 'N'
ORDER BY creation_date desc ;
SELECT cum_key_id,
cum_qty,
cum_qty_to_be_accumulated,
cum_qty_after_cutoff,
last_cum_qty_update_date,
cust_uom_code
INTO x_cum_record.cum_key_id,
x_cum_record.cum_qty,
x_cum_record.cum_qty_to_be_accumulated,
x_cum_record.cum_qty_after_cutoff,
x_cum_record.last_cum_qty_update_date,
x_cum_record.cust_uom_code
FROM RLM_CUST_ITEM_CUM_KEYS
WHERE ship_from_org_id IS NULL
AND NVL(ship_to_address_id, 0)
= NVL(p_ship_to_address_id,0)
AND NVL(intrmd_ship_to_id, 0)
= NVL(p_intrmd_ship_to_address_id,0)
AND NVL(bill_to_address_id, 0)
= NVL(p_bill_to_address_id,0)
AND NVL(customer_item_id, 0)
= NVL(p_customer_item_id,0)
AND purchase_order_number = NVL(p_purchase_order_number, ' ')
AND NVL(cust_record_year, ' ')
= NVL(p_cust_record_year, ' ')
AND NVL(cum_start_date, sysdate)
= NVL(p_cum_start_date, sysdate)
AND NVL(inactive_flag,'N') = 'N'
AND purchase_order_number IS NOT NULL
ORDER BY creation_date desc ;
SELECT *
FROM RLM_CUST_ITEM_CUM_KEYS_ALL
WHERE cum_key_id = x_CumKeyId
FOR UPDATE NOWAIT;