[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_Reconcile_Tab.DELETE;
g_IntransitTab.DELETE;
g_Accounted_Tab.DELETE;
g_Reconcile_Tab.DELETE;
g_IntransitTab.DELETE;
SELECT header_id,
line_id,
ship_from_org_id,
ship_to_org_id,
ordered_item_id,
inventory_item_id,
invoice_to_org_id,
intmed_ship_to_org_id,
demand_bucket_type_code,
rla_schedule_type_code,
authorized_to_ship_flag ATS,
ordered_quantity orig_ordered_quantity,
NVL(ordered_quantity,0) -
NVL(shipped_quantity,0) ordered_quantity,
ordered_item,
item_identifier_type,
item_type_code,
DECODE(x_Group_rec.setup_terms_rec.blanket_number, NULL,
NULL, blanket_number) blanket_number,
customer_line_number,
customer_production_line cust_production_line,
customer_dock_code,
request_date,
schedule_ship_date,
cust_po_number,
item_revision customer_item_revision,
customer_job,
cust_model_serial_number,
cust_production_seq_num,
industry_attribute1,
industry_attribute2,
industry_attribute3,
industry_attribute4,
industry_attribute5,
industry_attribute6,
industry_attribute7,
industry_attribute8,
industry_attribute9,
industry_attribute10,
industry_attribute11,
industry_attribute12,
industry_attribute13,
industry_attribute14,
industry_attribute15,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_date +
DECODE(demand_bucket_type_code,
k_WEEKLY,6.99999,
k_MONTHLY,29.99999,
k_QUARTERLY,89.99999,0.99999) end_date_time,
DECODE(rla_schedule_type_code,
x_Group_rec.schedule_type_one, 1,
x_Group_rec.schedule_type_two, 2,
x_Group_rec.schedule_type_three, 3) schedule_hierarchy
FROM oe_order_lines_all
WHERE header_id = x_Group_rec.order_header_id
AND open_flag = 'Y'
AND ship_from_org_id = DECODE(g_ATP, k_ATP, ship_from_org_id, x_Group_rec.ship_from_org_id)
AND ship_to_org_id = x_Group_rec.ship_to_org_id
AND ordered_item_id = x_Group_rec.customer_item_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND NVL(intmed_ship_to_org_id,k_NNULL) = NVL(x_Group_rec.intmed_ship_to_org_id,k_NNULL) --Bugfix 5911991
AND NVL(industry_attribute15, k_VNULL) =
DECODE(g_ATP, k_ATP, NVL(industry_attribute15, k_VNULL),
NVL(x_Group_rec.industry_attribute15, k_VNULL))
AND to_date(industry_attribute2,'RRRR/MM/DD HH24:MI:SS') BETWEEN
DECODE(authorized_to_ship_flag,k_ATS,
DECODE(x_group_rec.disposition_code,
k_REMAIN_ON_FILE,x_Sched_rec.sched_horizon_start_date,
k_REMAIN_ON_FILE_RECONCILE, to_date(industry_attribute2,'RRRR/MM/DD HH24:MI:SS'),
TRUNC(SYSDATE) - nvl(x_Group_rec.Cutoff_days,0)),
TRUNC(SYSDATE))
AND TRUNC(x_Sched_rec.sched_horizon_end_date)+0.99999
AND DECODE(rla_schedule_type_code,
x_Group_rec.schedule_type_one, 1,
x_Group_rec.schedule_type_two, 2,
x_Group_rec.schedule_type_three, 3,0) >
DECODE(x_Sched_rec.schedule_type, x_Group_rec.schedule_type_one, 1,
x_Group_rec.schedule_type_two, 2,
x_Group_rec.schedule_type_three, 3)
AND (NVL(ordered_quantity,0) - NVL(shipped_quantity,0) > 0)
ORDER BY request_date DESC;
g_Op_tab.DELETE;
g_Op_tab_Unschedule.DELETE;
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 rownum = 1;
SELECT line_id
FROM oe_order_lines
WHERE header_id = x_Group_rec.order_header_id
--global_atp
AND ship_from_org_id =
DECODE(g_ATP, k_ATP, ship_from_org_id,
x_Group_rec.ship_from_org_id)
AND ship_to_org_id = x_Group_rec.ship_to_org_id
AND ordered_item_id = x_Group_rec.customer_item_id
AND inventory_item_id= x_Group_rec.inventory_item_id
--global_atp
AND NVL(industry_attribute15, k_VNULL) =
DECODE(g_ATP, k_ATP, NVL(industry_attribute15, k_VNULL),
NVL(x_Group_rec.industry_attribute15, k_VNULL))
--bug 2181228
/*AND NVL(cust_production_seq_num, k_NNULL) =
NVL(x_Group_rec.cust_production_seq_num, k_NNULL)*/
--
--AND request_date < x_Sched_rec.sched_horizon_start_date --chg
AND to_date(industry_attribute2,'RRRR/MM/DD HH24:MI:SS')
< TRUNC(SYSDATE)
--pdue
-- < x_Sched_rec.sched_horizon_start_date
AND authorized_to_ship_flag = k_NATS
AND nvl(ordered_quantity,0 ) - nvl(shipped_quantity,0) > 0;
v_DeleteQty NUMBER;
DeleteRequirement(x_Sched_rec, x_Group_rec,
v_Key_rec, k_NORECONCILE, v_DeleteQty);
IF x_Operation = k_UPDATE_ATTR THEN
--
b_Result := rlm_extinterface_sv.CallProcessConstraintAPI(x_key_rec,
x_Qty_rec,
'UPDATE',
x_OperationQty);
IF x_Operation = k_DELETE THEN
IF b_Result THEN
x_Qty_rec.reconcile := x_Key_rec.dem_rec.ordered_quantity;
ELSIF x_Operation = k_INSERT THEN
NULL;
ELSIF x_Operation = k_UPDATE THEN
IF b_Result THEN
x_Qty_rec.reconcile := x_Key_rec.req_rec.ordered_quantity
- x_Key_rec.dem_rec.ordered_quantity;
ELSIF x_Operation = k_UPDATE_ATTR THEN
NULL;
PROCEDURE DeleteRequirement
===========================================================================*/
PROCEDURE DeleteRequirement(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
x_Group_rec IN OUT NOCOPY RLM_DP_SV.t_Group_rec,
x_Key_rec IN OUT NOCOPY RLM_RD_SV.t_Key_rec,
x_Reconcile IN BOOLEAN,
x_DeleteQty OUT NOCOPY NUMBER)
IS
v_Qty_rec t_Qty_rec;
rlm_core_sv.dpush(k_SDEBUG,'DeleteRequirement');
rlm_core_sv.dlog(k_DEBUG,'Line cannot be deleted-- within frozen fence',
x_key_rec.dem_rec.line_id);
x_MessageName => 'RLM_FROZEN_DELETE_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => NULL,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => NULL,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(x_Key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_Key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SCH_LINE_QTY', --Bugfix 6159269
x_value6 => v_del_line_qty, --Bugfix 6159269
x_Token7 => 'SEQ_INFO',
x_value7 => nvl(x_Key_rec.dem_rec.cust_production_seq_num,'NULL')||'-'||
nvl(x_Key_rec.dem_rec.cust_model_serial_number,'NULL')||'-'||
nvl(x_Key_rec.dem_rec.customer_job,'NULL'),
x_Token8 => 'MATCH_ATTR',
x_value8 => v_MatchAttrTxt );
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_DELETE_SEQ',x_key_rec.dem_rec.line_id);
x_MessageName => 'RLM_FROZEN_DELETE',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => NULL,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => NULL,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(x_Key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_Key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SCH_LINE_QTY', --Bugfix 6159269
x_value6 => v_del_line_qty, --Bugfix 6159269
x_Token7 => 'MATCH_ATTR',
x_value7 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_DELETE',x_key_rec.dem_rec.line_id);
ELSIF ProcessConstraint(x_Key_rec, v_Qty_rec, k_DELETE) THEN
--
--CancelRequirement(x_Sched_rec, x_Group_rec,
--x_Key_rec, v_Qty_rec.available_to_cancel);
IF ProcessConstraint(x_Key_rec, v_Qty_rec, k_UPDATE, 0) THEN
--{
--
IF x_Reconcile THEN
StoreReconcile(x_Sched_rec, x_Group_rec, x_Key_rec, x_key_rec.dem_rec.ordered_quantity);
x_DeleteQty := 0;
SetOperation(x_Key_rec, k_UPDATE, x_DeleteQty);
rlm_core_sv.dlog(k_DEBUG,'x_DeleteQty',x_DeleteQty);
SetOperation(x_Key_rec, k_DELETE);
x_DeleteQty := v_Qty_rec.ordered;
x_MessageName => 'RLM_RECONCILE_DELETE_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => NULL,
x_Token1 => 'QUANTITY',
x_value1 => g_sch_line_qty,
x_Token2 => 'GROUP',
x_value2 => rlm_core_sv.get_ship_from(x_group_rec.ship_from_org_id)||'-'||
rlm_core_sv.get_ship_to(x_group_rec.ship_to_address_id)||'-'||
rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token3 => 'REQ_DATE',
x_value3 => x_key_rec.req_rec.request_date,
x_Token4 => 'START_DATE_TIME',
x_value4 => to_date(x_key_rec.req_rec.industry_attribute2,'YYYY/MM/DD HH24:MI:SS'),
x_Token5 => 'SEQ_INFO',
x_value5 => nvl(x_Key_rec.req_rec.cust_production_seq_num,'NULL') ||'-'||
nvl(x_Key_rec.req_rec.cust_model_serial_number,'NULL')||'-'||
nvl(x_Key_rec.req_rec.customer_job,'NULL'),
x_Token6 => 'ORDER',
x_value6 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token7 => 'LINE',
x_value7 =>rlm_core_sv.get_order_line_number(x_key_rec.dem_rec.line_id),
x_Token8 => 'MATCH_ATTR',
x_value8 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_DELETE_SEQ',
x_key_rec.req_rec.line_id);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_DELETE_SEQ',
x_Key_rec.req_rec.cust_model_serial_number);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_DELETE_SEQ',
x_Key_rec.req_rec.request_date);
x_MessageName => 'RLM_RECONCILE_DELETE',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => NULL,
x_Token1 => 'QUANTITY',
x_value1 => g_sch_line_qty,
x_Token2 => 'GROUP',
x_value2 => rlm_core_sv.get_ship_from(x_group_rec.ship_from_org_id)||'-'||
rlm_core_sv.get_ship_to(x_group_rec.ship_to_address_id)||'-'||
rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token3 => 'REQ_DATE',
x_value3 => x_key_rec.req_rec.request_date,
x_Token4 => 'START_DATE_TIME',
x_value4 => to_date(x_key_rec.req_rec.industry_attribute2,'YYYY/MM/DD HH24:MI:SS'),
x_Token5 => 'SCHEDULE_LINE',
x_value5 => rlm_core_sv.get_schedule_line_number(x_key_rec.req_rec.schedule_line_id),
x_Token6 => 'ORDER',
x_value6 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token7 => 'LINE',
x_value7 =>rlm_core_sv.get_order_line_number(x_key_rec.dem_rec.line_id),
x_Token8 => 'MATCH_ATTR',
x_value8 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_DELETE',
x_key_rec.req_rec.line_id);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_DELETE',
x_Key_rec.req_rec.cust_model_serial_number);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_DELETE',
x_Key_rec.req_rec.request_date);
rlm_core_sv.dlog(k_DEBUG,'x_DeleteQty',x_DeleteQty);
rlm_message_sv.sql_error('rlm_rd_sv.DeleteRequirement',x_progress);
END DeleteRequirement;
PROCEDURE InsertRequirement
===========================================================================*/
PROCEDURE InsertRequirement(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
x_Group_rec IN OUT NOCOPY RLM_DP_SV.t_Group_rec,
x_Key_rec IN OUT NOCOPY RLM_RD_SV.t_Key_rec,
x_Reconcile IN BOOLEAN,
x_Quantity IN OUT NOCOPY NUMBER)
IS
x_progress VARCHAR2(3) := '010';
rlm_core_sv.dpush(k_SDEBUG,'InsertRequirement');
x_MessageName => 'RLM_RECONCILE_ZERO_INSERT_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => NULL,
x_Token1 => 'QUANTITY',
x_value1 => v_Quantity,
x_Token2 => 'GROUP',
x_value2 => rlm_core_sv.get_ship_from(x_group_rec.ship_from_org_id)||'-'||
rlm_core_sv.get_ship_to(x_group_rec.ship_to_address_id)||'-'||
rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token3 => 'REQ_DATE',
x_value3 => x_key_rec.req_rec.request_date,
x_Token4 => 'START_DATE_TIME',
x_value4 => to_date(x_key_rec.req_rec.industry_attribute2,'YYYY/MM/DD HH24:MI:SS'),
x_Token5 => 'SEQ_INFO',
x_value5 => nvl(x_Key_rec.req_rec.cust_production_seq_num,'NULL') ||'-'||
nvl(x_Key_rec.req_rec.cust_model_serial_number,'NULL')||'-'||
nvl(x_Key_rec.req_rec.customer_job,'NULL'),
x_Token6 => 'MATCH_ATTR',
x_value6 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_ZERO_INSERT_SEQ',
x_key_rec.req_rec.line_id);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_ZERO_INSERT_SEQ',
x_Key_rec.req_rec.cust_model_serial_number);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_ZERO_INSERT_SEQ',
x_Key_rec.req_rec.request_date);
x_MessageName => 'RLM_RECONCILE_ZERO_INSERT',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => NULL,
x_Token1 => 'QUANTITY',
x_value1 => v_Quantity,
x_Token2 => 'GROUP',
x_value2 => rlm_core_sv.get_ship_from(x_group_rec.ship_from_org_id)||'-'||
rlm_core_sv.get_ship_to(x_group_rec.ship_to_address_id)||'-'||
rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token3 => 'REQ_DATE',
x_value3 => x_key_rec.req_rec.request_date,
x_Token4 => 'START_DATE_TIME',
x_value4 => to_date(x_key_rec.req_rec.industry_attribute2,'YYYY/MM/DD HH24:MI:SS'),
x_Token5 => 'SCHEDULE_LINE',
x_value5 => rlm_core_sv.get_schedule_line_number(x_key_rec.req_rec.schedule_line_id),
x_Token6 => 'MATCH_ATTR',
x_value6 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_ZERO_INSERT',
x_key_rec.req_rec.line_id);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_ZERO_INSERT',
x_Key_rec.req_rec.cust_model_serial_number);
rlm_core_sv.dlog(k_DEBUG,'RLM_RECONCILE_ZERO_INSERT',
x_Key_rec.req_rec.request_date);
rlm_core_sv.dlog(k_DEBUG, 'Not inserting line', x_key_rec.req_rec.line_id);
rlm_core_sv.dlog(k_DEBUG, 'Qty not inserting ', x_quantity);
SetOperation(x_Key_rec, k_INSERT, x_Quantity);
rlm_core_sv.dlog(k_DEBUG,'Insertion will not happen as a demand populated by higher precedence ' ||
g_order_rec.rla_schedule_type_code || ' schedule exists on ' ||
g_order_rec.request_date);
x_MessageName => 'RLM_LOW_PRECEDENCE_INSERT_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => NULL,
x_Token1 => 'QUANTITY',
x_value1 => x_Key_rec.req_rec.primary_quantity,
x_Token2 => 'GROUP',
x_value2 => rlm_core_sv.get_ship_from(x_group_rec.ship_from_org_id)||'-'||
rlm_core_sv.get_ship_to(x_group_rec.ship_to_address_id)||'-'||
rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token3 => 'REQ_DATE',
x_value3 => x_key_rec.req_rec.request_date,
x_Token4 => 'START_DATE_TIME',
x_value4 => to_date(x_key_rec.req_rec.industry_attribute2,'YYYY/MM/DD HH24:MI:SS'),
x_Token5 => 'SCHEDULE_TYPE',
x_value5 => g_order_rec.rla_schedule_type_code,
x_Token6 => 'SCHEDULE_NUM',
x_value6 => g_order_rec.industry_attribute3,
x_Token7 => 'RECENT_REQ_DATE',
x_value7 => g_order_rec.request_date,
x_Token8 => 'ORDER',
x_value8 => rlm_core_sv.get_order_number(g_order_rec.header_id),
x_Token9 => 'SEQ_INFO',
x_value9 => nvl(x_Key_rec.req_rec.cust_production_seq_num,'NULL') ||'-'||
nvl(x_Key_rec.req_rec.cust_model_serial_number,'NULL')||'-'||
nvl(x_Key_rec.req_rec.customer_job,'NULL'),
x_Token10 => 'MATCH_ATTR',
x_value10 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_LOW_PRECEDENCE_INSERT_SEQ',
x_key_rec.req_rec.line_id);
rlm_core_sv.dlog(k_DEBUG,'RLM_LOW_PRECEDENCE_INSERT_SEQ',
x_Key_rec.req_rec.cust_model_serial_number);
rlm_core_sv.dlog(k_DEBUG,'RLM_LOW_PRECEDENCE_INSERT_SEQ',
x_Key_rec.req_rec.request_date);
x_MessageName => 'RLM_LOW_PRECEDENCE_INSERT',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => NULL,
x_Token1 => 'QUANTITY',
x_value1 => x_Key_rec.req_rec.primary_quantity,
x_Token2 => 'GROUP',
x_value2 => rlm_core_sv.get_ship_from(x_group_rec.ship_from_org_id)||'-'||
rlm_core_sv.get_ship_to(x_group_rec.ship_to_address_id)||'-'||
rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token3 => 'REQ_DATE',
x_value3 => x_key_rec.req_rec.request_date,
x_Token4 => 'START_DATE_TIME',
x_value4 => to_date(x_key_rec.req_rec.industry_attribute2,'YYYY/MM/DD HH24:MI:SS'),
x_Token5 => 'SCHEDULE_TYPE',
x_value5 => g_order_rec.rla_schedule_type_code,
x_Token6 => 'SCHEDULE_NUM',
x_value6 => g_order_rec.industry_attribute3,
x_Token7 => 'RECENT_REQ_DATE',
x_value7 => g_order_rec.request_date,
x_Token8 => 'ORDER',
x_value8 => rlm_core_sv.get_order_number(g_order_rec.header_id),
x_Token9 => 'SCHEDULE_LINE',
x_value9 => rlm_core_sv.get_schedule_line_number(x_key_rec.req_rec.schedule_line_id),
x_Token10 => 'MATCH_ATTR',
x_value10 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_LOW_PRECEDENCE_INSERT',
x_key_rec.req_rec.line_id);
rlm_core_sv.dlog(k_DEBUG,'RLM_LOW_PRECEDENCE_INSERT',
x_Key_rec.req_rec.cust_model_serial_number);
rlm_core_sv.dlog(k_DEBUG,'RLM_LOW_PRECEDENCE_INSERT',
x_Key_rec.req_rec.request_date);
SetOperation(x_Key_rec, k_INSERT, x_Quantity);
x_MessageName => 'RLM_FROZEN_INSERT_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => NULL,
x_Token1 => 'QUANTITY',
x_value1 => x_Key_rec.req_rec.primary_quantity,
x_Token2 => 'GROUP',
x_value2 => rlm_core_sv.get_ship_from(x_group_rec.ship_from_org_id)||'-'||
rlm_core_sv.get_ship_to(x_group_rec.ship_to_address_id)||'-'||
rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token3 => 'REQ_DATE',
x_value3 => x_key_rec.req_rec.request_date,
x_Token4 => 'START_DATE_TIME',
x_value4 => to_date(x_key_rec.req_rec.industry_attribute2,'YYYY/MM/DD HH24:MI:SS'),
x_Token5 => 'SEQ_INFO',
x_value5 => nvl(x_Key_rec.req_rec.cust_production_seq_num,'NULL')||'-'||
nvl(x_Key_rec.req_rec.cust_model_serial_number,'NULL')||'-'||
nvl(x_Key_rec.req_rec.customer_job,'NULL'),
x_Token6 => 'MATCH_ATTR',
x_value6 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_INSERT_SEQ',
x_key_rec.req_rec.line_id);
x_MessageName => 'RLM_FROZEN_INSERT',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => NULL,
x_Token1 => 'QUANTITY',
x_value1 => x_Key_rec.req_rec.primary_quantity,
x_Token2 => 'GROUP',
x_value2 => rlm_core_sv.get_ship_from(x_group_rec.ship_from_org_id)||'-'||
rlm_core_sv.get_ship_to(x_group_rec.ship_to_address_id)||'-'||
rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token3 => 'REQ_DATE',
x_value3 => x_key_rec.req_rec.request_date,
x_Token4 => 'START_DATE_TIME',
x_value4 => to_date(x_key_rec.req_rec.industry_attribute2,'YYYY/MM/DD HH24:MI:SS'),
x_Token5 => 'SCHEDULE_LINE',
x_value5 => rlm_core_sv.get_schedule_line_number(x_key_rec.req_rec.schedule_line_id),
x_Token6 => 'MATCH_ATTR',
x_value6 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_INSERT',
x_key_rec.req_rec.line_id);
rlm_message_sv.sql_error('rlm_rd_sv.InsertRequirement',x_progress);
END InsertRequirement;
PROCEDURE UpdateRequirement
===========================================================================*/
PROCEDURE UpdateRequirement(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
x_Group_rec IN OUT NOCOPY RLM_DP_SV.t_Group_rec,
x_Key_rec IN OUT NOCOPY RLM_RD_SV.t_Key_rec,
x_Quantity IN NUMBER)
IS
v_Qty_rec t_Qty_rec;
rlm_core_sv.dpush(k_SDEBUG,'UpdateRequirement');
x_MessageName => 'RLM_FROZEN_UPDATE_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(x_key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SCH_LINE_QTY', --Bugfix 6159269
x_value6 => g_sch_line_qty, --Bugfix 6159269
x_Token7 => 'SEQ_INFO',
x_value7 => nvl(x_Key_rec.dem_rec.cust_production_seq_num,'NULL')||'-'||
nvl(x_Key_rec.dem_rec.cust_model_serial_number,'NULL')||'-'||
nvl(x_Key_rec.dem_rec.customer_job,'NULL'),
x_Token8 => 'MATCH_ATTR',
x_value8 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_UPDATE_SEQ',
x_key_rec.req_rec.line_id);
x_MessageName => 'RLM_FROZEN_UPDATE',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 =>rlm_core_sv.get_order_line_number(x_key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SCH_LINE_QTY', --Bugfix 6159269
x_value6 => g_sch_line_qty, --Bugfix 6159269
x_Token7 => 'MATCH_ATTR',
x_value7 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_UPDATE',
x_key_rec.req_rec.line_id);
SetOperation(x_Key_rec, k_INSERT,
x_Quantity - x_key_rec.dem_rec.ordered_quantity);
SetOperation(x_Key_rec, k_UPDATE, x_Quantity);
IF not alreadyupdated(v_line_id_tab) THEN
SetOperation(x_Key_rec, k_UPDATE, x_Quantity);
RLM_TPA_SV.InsertRequirement(x_Sched_rec, x_Group_rec,
x_Key_rec, k_RECONCILE,
x_Key_rec.req_rec.primary_quantity);
rlm_message_sv.sql_error('rlm_rd_sv.UpdateRequirement',x_progress);
END UpdateRequirement;
SELECT line_id,customer_production_line,customer_dock_code,request_date,
schedule_ship_date,cust_po_number,item_revision customer_item_revision,
customer_job,cust_model_serial_number,cust_production_seq_num,
industry_attribute1,industry_attribute2,industry_attribute3,
industry_attribute4,industry_attribute5,industry_attribute6,
industry_attribute7,industry_attribute8,industry_attribute9,
industry_attribute10,industry_attribute11,industry_attribute12,
industry_attribute13,industry_attribute14,industry_attribute15,
attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,
attribute7,attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15,demand_bucket_type_code,
ship_to_org_id,invoice_to_org_id,intmed_ship_to_org_id,
ordered_item_id customer_item_id,inventory_item_id,header_id,
ship_from_org_id,rla_schedule_type_code,authorized_to_ship_flag,
ordered_quantity,ordered_item,item_identifier_type,item_type_code,
customer_line_number,blanket_number
FROM oe_order_lines_all WHERE line_id = x_Key_rec.oe_line_id;
SELECT line_id,customer_production_line,customer_dock_code,request_date,
schedule_ship_date,cust_po_number,item_revision customer_item_revision,
customer_job,cust_model_serial_number,cust_production_seq_num,
industry_attribute1,industry_attribute2,industry_attribute3,
industry_attribute4,industry_attribute5,industry_attribute6,
industry_attribute7,industry_attribute8,industry_attribute9,
industry_attribute10,industry_attribute11,industry_attribute12,
industry_attribute13,industry_attribute14,industry_attribute15,
attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,
attribute7,attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15,demand_bucket_type_code,
ship_to_org_id,invoice_to_org_id,intmed_ship_to_org_id,
ordered_item_id customer_item_id,inventory_item_id,header_id,
ship_from_org_id,rla_schedule_type_code,authorized_to_ship_flag,
ordered_quantity,ordered_item,item_identifier_type,item_type_code,
customer_line_number,NULL
FROM oe_order_lines_all WHERE line_id = x_Key_rec.oe_line_id;
v_select_clause VARCHAR2(32000);
g_InitDemandTab.DELETE;
v_select_clause :=
'SELECT line_id,NVL(ordered_quantity,0),NVL(shipped_quantity,0),'||
'sold_to_org_id,customer_production_line,customer_dock_code,'||
'request_date,schedule_ship_date,cust_po_number,'||
'item_revision customer_item_revision,customer_job,'||
'cust_model_serial_number,cust_production_seq_num,industry_attribute1,'||
'industry_attribute2,industry_attribute3,industry_attribute4,'||
'industry_attribute5,industry_attribute6,industry_attribute7,'||
'industry_attribute8,industry_attribute9,industry_attribute10,'||
'industry_attribute11,industry_attribute12,industry_attribute13,'||
'industry_attribute14,industry_attribute15,industry_context,'||
'attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,'||
'attribute7,attribute8,attribute9,attribute10,attribute11,attribute12,'||
'attribute13,attribute14,attribute15,context,tp_attribute1,'||
'tp_attribute2,tp_attribute3,tp_attribute4,tp_attribute5,tp_attribute6,'||
'tp_attribute7,tp_attribute8,tp_attribute9,tp_attribute10,'||
'tp_attribute11,tp_attribute12,tp_attribute13,tp_attribute14,'||
'tp_attribute15,tp_context,demand_bucket_type_code,item_type_code,'||
'ship_to_org_id,invoice_to_org_id,intmed_ship_to_org_id,'||
'ordered_item_id customer_item_id,inventory_item_id,header_id,'||
'ship_from_org_id,rla_schedule_type_code,authorized_to_ship_flag,'||
'item_identifier_type,agreement_id,price_list_id,ordered_item,'||
'order_quantity_uom,';
v_select_clause := v_select_clause || 'blanket_number FROM oe_order_lines';
v_select_clause := v_select_clause || 'NULL FROM oe_order_lines';
rlm_core_sv.dlog(k_DEBUG,'v_select_clause',v_select_clause);
v_sql := v_select_clause || v_where_clause;
SELECT rih.customer_id,
ril.ship_from_org_id,
ril.ship_to_address_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.cust_production_seq_num
FROM rlm_interface_headers rih,
rlm_interface_lines_all ril
WHERE rih.header_id = x_Sched_rec.header_id
AND rih.org_id = ril.org_id
AND ril.header_id = rih.header_id
AND ril.industry_attribute15 = x_Group_rec.ship_from_org_id
AND ril.process_status IN (rlm_core_sv.k_PS_AVAILABLE, rlm_core_sv.k_PS_FROZEN_FIRM)
--AND ril.inventory_item_id = x_Group_rec.inventory_item_id
AND ril.customer_item_id = x_Group_rec.customer_item_id
AND ril.ship_to_address_id = x_Group_rec.ship_to_address_id
-- blankets
--AND ril.blanket_number IS NULL
GROUP BY rih.customer_id,
ril.ship_from_org_id,
ril.ship_to_address_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.cust_production_seq_num
ORDER BY ril.ship_to_org_id,
ril.customer_item_id;
SELECT x_group_rec.customer_id,
header_id,
line_id,
cust_production_line,
customer_dock_code,
request_date,
schedule_date,
cust_po_number,
customer_item_revision,
customer_job,
cust_model_serial_number,
cust_production_seq_num,
industry_attribute1,
industry_attribute2,
industry_attribute3,
industry_attribute4,
industry_attribute5,
industry_attribute6,
industry_attribute7,
industry_attribute8,
industry_attribute9,
industry_attribute10,
industry_attribute11,
industry_attribute12,
industry_attribute13,
industry_attribute14,
industry_attribute15,
industry_context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
tp_attribute1,
tp_attribute2,
tp_attribute3,
tp_attribute4,
tp_attribute5,
tp_attribute6,
tp_attribute7,
tp_attribute8,
tp_attribute9,
tp_attribute10,
tp_attribute11,
tp_attribute12,
tp_attribute13,
tp_attribute14,
tp_attribute15,
tp_attribute_category,
item_detail_type,
item_detail_subtype,
intrmd_ship_to_id,
ship_to_org_id,
invoice_to_org_id,
primary_quantity,
intmed_ship_to_org_id,
customer_item_id,
inventory_item_id,
order_header_id,
x_ReqType,
ship_from_org_id,
x_Sched_rec.schedule_type,
'CUST' item_identifier_type,
customer_item_ext,
agreement_id,
price_list_id,
x_Sched_rec.schedule_header_id,
schedule_line_id,
process_status,
uom_code,
cust_po_line_num
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 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 order_header_id = x_Group_rec.order_header_id
AND NVL(intmed_ship_to_org_id ,K_NNULL) = NVL(x_Group_rec.intmed_ship_to_org_id, K_NNULL) --Bugfix 5911991
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_FROZEN_FIRM)
AND (item_detail_type = k_FIRM OR item_detail_type = k_PAST_DUE_FIRM)
ORDER BY request_date;
SELECT x_group_rec.customer_id,
header_id,
line_id,
cust_production_line,
customer_dock_code,
request_date,
schedule_date,
cust_po_number,
customer_item_revision,
customer_job,
cust_model_serial_number,
cust_production_seq_num,
industry_attribute1,
industry_attribute2,
industry_attribute3,
industry_attribute4,
industry_attribute5,
industry_attribute6,
industry_attribute7,
industry_attribute8,
industry_attribute9,
industry_attribute10,
industry_attribute11,
industry_attribute12,
industry_attribute13,
industry_attribute14,
industry_attribute15,
industry_context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
tp_attribute1,
tp_attribute2,
tp_attribute3,
tp_attribute4,
tp_attribute5,
tp_attribute6,
tp_attribute7,
tp_attribute8,
tp_attribute9,
tp_attribute10,
tp_attribute11,
tp_attribute12,
tp_attribute13,
tp_attribute14,
tp_attribute15,
tp_attribute_category,
item_detail_type,
item_detail_subtype,
intrmd_ship_to_id,
ship_to_org_id,
invoice_to_org_id,
primary_quantity,
intmed_ship_to_org_id,
customer_item_id,
inventory_item_id,
order_header_id,
x_ReqType,
ship_from_org_id,
x_Sched_rec.schedule_type,
'CUST' item_identifier_type,
customer_item_ext,
agreement_id,
price_list_id,
x_Sched_rec.schedule_header_id,
schedule_line_id,
process_status,
uom_code,
cust_po_line_num
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 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 NVL(intmed_ship_to_org_id ,K_NNULL) = NVL(x_Group_rec.intmed_ship_to_org_id, K_NNULL) --Bugfix 5911991
AND order_header_id = x_Group_rec.order_header_id
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_FROZEN_FIRM)
AND item_detail_type = k_FORECAST
ORDER BY request_date;
SetOperation(x_Key_rec, k_UPDATE, x_Key_rec.dem_rec.ordered_quantity - x_CancelQty);
SELECT COUNT(*)
INTO v_cumDiscrete
FROM rlm_interface_lines il,
rlm_schedule_lines_all sl
WHERE il.header_id = x_Sched_rec.header_id
AND il.ship_from_org_id = x_Group_rec.ship_from_org_id
AND il.ship_to_org_id = x_Group_rec.ship_to_org_id
AND il.inventory_item_id = x_Group_rec.inventory_item_id
AND il.customer_item_id = x_Group_rec.customer_item_id
AND NVL(il.item_detail_type, ' ') NOT IN
(rlm_manage_demand_sv.k_SHIP_RECEIPT_INFO,
rlm_manage_demand_sv.k_AUTHORIZATION,
rlm_manage_demand_sv.k_OTHER_DETAIL_TYPE)
AND il.schedule_line_id = sl.line_id
AND sl.qty_type_code = rlm_manage_demand_sv.k_CUMULATIVE
AND il.org_id = sl.org_id;
InsertIntransitMatchRec(v_match_rec, v_IntransitQty);
rlm_core_sv.dlog(k_DEBUG,'Entry getting deleted from Reconcile Table', v_Index);
rlm_core_sv.dlog(k_DEBUG,'deleted g_Reconcile_tab(v_Index).ordered_quantity',
g_Reconcile_tab(v_Index).ordered_quantity);
g_Reconcile_tab.DELETE(v_Index);
rlm_core_sv.dlog(k_DEBUG,'Entry getting deleted from Reconcile Table', v_Index);
rlm_core_sv.dlog(k_DEBUG,'deleted g_Reconcile_tab(v_Index).ordered_quantity',
g_Reconcile_tab(v_Index).ordered_quantity);
g_Reconcile_tab.DELETE(v_Index);
SELECT line_id, rla_schedule_type_code
FROM oe_order_lines
WHERE header_id = x_Group_rec.order_header_id
--global_atp
AND ship_from_org_id =
DECODE(g_ATP, k_ATP, ship_from_org_id,
x_Group_rec.ship_from_org_id)
AND ship_to_org_id = x_Group_rec.ship_to_org_id
AND ordered_item_id = x_Group_rec.customer_item_id
AND inventory_item_id= x_Group_rec.inventory_item_id
--pdue, global_atp
AND NVL(industry_attribute15, k_VNULL) =
DECODE(g_ATP, k_ATP, NVL(industry_attribute15, k_VNULL),
NVL(x_Group_rec.industry_attribute15, k_VNULL))
--bug 2181228
AND to_date(industry_attribute2,'RRRR/MM/DD HH24:MI:SS')
< (TRUNC(SYSDATE) - x_OffsetDays)
AND (NVL(ordered_quantity,0) -
NVL(shipped_quantity,0) > 0)
AND authorized_to_ship_flag = k_ATS
ORDER BY request_date DESC;
v_DeleteQty NUMBER;
SELECT line_number INTO v_line_num
FROM oe_order_lines_all
WHERE line_id = c_PreHorizonDisp_rec.line_id;
DeleteRequirement(x_Sched_rec, x_Group_rec,
v_Key_rec, k_NORECONCILE, v_DeleteQty);
SELECT header_id,
line_id,
ship_from_org_id,
ship_to_org_id,
ordered_item_id,
inventory_item_id,
invoice_to_org_id,
intmed_ship_to_org_id,
demand_bucket_type_code,
rla_schedule_type_code,
authorized_to_ship_flag ATS,
ordered_quantity orig_ordered_quantity,
NVL(ordered_quantity,0) -
NVL(shipped_quantity,0) ordered_quantity,
ordered_item,
item_identifier_type,
item_type_code,
DECODE(x_Group_rec.setup_terms_rec.blanket_number, NULL,
NULL, blanket_number) blanket_number,
customer_line_number,
customer_production_line cust_production_line,
customer_dock_code,
request_date,
schedule_ship_date,
cust_po_number,
item_revision customer_item_revision,
customer_job,
cust_model_serial_number,
cust_production_seq_num,
industry_attribute1,
industry_attribute2,
industry_attribute3,
industry_attribute4,
industry_attribute5,
industry_attribute6,
industry_attribute7,
industry_attribute8,
industry_attribute9,
industry_attribute10,
industry_attribute11,
industry_attribute12,
industry_attribute13,
industry_attribute14,
industry_attribute15,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_date +
DECODE(demand_bucket_type_code,
k_WEEKLY,6.99999,
k_MONTHLY,29.99999,
k_QUARTERLY,89.99999,0.99999) end_date_time, --bug 3596869
DECODE(x_Sched_rec.schedule_purpose,
k_ADD,DECODE(rla_schedule_type_code,
x_Group_rec.schedule_type_one, 3,
x_Group_rec.schedule_type_two, 2,
x_Group_rec.schedule_type_three, 1),
DECODE(rla_schedule_type_code,
x_Group_rec.schedule_type_one, 1,
x_Group_rec.schedule_type_two, 2,
x_Group_rec.schedule_type_three, 3)) schedule_hierarchy
FROM oe_order_lines
WHERE header_id = x_Group_rec.order_header_id
AND open_flag = 'Y' /*2263270*/
--global_atp
AND ship_from_org_id =
DECODE(g_ATP, k_ATP, ship_from_org_id,
x_Group_rec.ship_from_org_id)
AND ship_to_org_id = x_Group_rec.ship_to_org_id
AND ordered_item_id = x_Group_rec.customer_item_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND NVL(intmed_ship_to_org_id,k_NNULL)= NVL(x_Group_rec.intmed_ship_to_org_id,k_NNULL) --Bugfix 5911991
--global_atp
AND NVL(industry_attribute15, k_VNULL) =
DECODE(g_ATP, k_ATP, NVL(industry_attribute15, k_VNULL),
NVL(x_Group_rec.industry_attribute15, k_VNULL))
--bug 4223359
AND to_date(industry_attribute2,'RRRR/MM/DD HH24:MI:SS') BETWEEN /*bug3879857*/
DECODE(authorized_to_ship_flag,k_ATS,
DECODE(x_group_rec.disposition_code,
k_REMAIN_ON_FILE, x_Sched_rec.sched_horizon_start_date,
k_REMAIN_ON_FILE_RECONCILE, to_date(industry_attribute2,'RRRR/MM/DD HH24:MI:SS'),
TRUNC(SYSDATE) - nvl(x_Group_rec.Cutoff_days,0)), TRUNC(SYSDATE))
AND TRUNC(x_Sched_rec.sched_horizon_end_date)+0.99999
--bug 2022158 (issue with sched_horizon_end_date timestamp)
AND DECODE(x_Sched_rec.schedule_purpose,
k_ADD, DECODE(rla_schedule_type_code,
x_Group_rec.schedule_type_one, 2,
x_Group_rec.schedule_type_two, 3,
x_Group_rec.schedule_type_three, 4,0),
DECODE(rla_schedule_type_code,
x_Group_rec.schedule_type_one, 1,
x_Group_rec.schedule_type_two, 2,
x_Group_rec.schedule_type_three, 3,0)) <=
DECODE(x_Sched_rec.schedule_type, x_Group_rec.schedule_type_one, 1,
x_Group_rec.schedule_type_two, 2,
x_Group_rec.schedule_type_three, 3)
AND DECODE(x_Sched_rec.schedule_purpose,
k_ADD,authorized_to_ship_flag,
'N') = 'N'
AND (NVL(ordered_quantity,0) - NVL(shipped_quantity,0) > 0)
ORDER BY demand_bucket_type_code, schedule_hierarchy, end_date_time;
v_DeleteQty NUMBER;
v_consume_line_tab.DELETE;
/* We do need to select based on the item detail subtype as the weekly
demand needs to be consumed by the daily demand as the weekly demand needs
to be replaced by daily. Also we need to take into account the
schedule date between old demands schedule date and end date.
We have taken item_detail_type <= demand.item_method_type so that
firm demand is never replaced by forecast data */
--
OPEN c_NewReq_ref FOR
SELECT line_id, primary_quantity
FROM rlm_interface_lines_all -- Bug 5223933
WHERE header_id = x_Sched_rec.header_id
--global_atp
AND ship_from_org_id = x_Group_rec.ship_from_org_id
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 order_header_id= x_Group_rec.order_header_id
AND item_detail_subtype = c_OldDemand_rec.demand_bucket_type_code
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_FROZEN_FIRM)
--bug 2031077
--AND NVL(invoice_to_org_id, k_NNULL) =
-- NVL(c_OldDemand_rec.invoice_to_org_id, k_NNULL)
-- bug 4502559
AND NVL(intmed_ship_to_org_id, k_NNULL) =
NVL(c_OldDemand_rec.intmed_ship_to_org_id, k_NNULL)
AND NVL(ship_to_org_id, k_NNULL) =
NVL(c_OldDemand_rec.ship_to_org_id, k_NNULL)
AND NVL(cust_production_line, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.cust_production_line, 'Y',
NVL(c_OldDemand_rec.cust_production_line, k_VNULL),
DECODE(x_Group_rec.match_within_rec.cust_production_line, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.cust_production_line, k_VNULL),
NVL(cust_production_line, k_VNULL)),
NVL(cust_production_line, k_VNULL)))
AND NVL(customer_dock_code, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.customer_dock_code, 'Y',
NVL(c_OldDemand_rec.customer_dock_code, k_VNULL),
DECODE(x_Group_rec.match_within_rec.customer_dock_code, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.customer_dock_code, k_VNULL),
NVL(customer_dock_code, k_VNULL)),
NVL(customer_dock_code, k_VNULL)))
AND NVL(request_date, k_DNULL) =
DECODE(x_Group_rec.match_across_rec.request_date, 'Y',
NVL(c_OldDemand_rec.request_date, k_DNULL),
DECODE(x_Group_rec.match_within_rec.request_date, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.request_date, k_DNULL),
NVL(request_date, k_DNULL)),
NVL(request_date, k_DNULL)))
AND NVL(cust_po_number, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.cust_po_number, 'Y',
NVL(c_OldDemand_rec.cust_po_number, k_VNULL),
DECODE(x_Group_rec.match_within_rec.cust_po_number, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.cust_po_number, k_VNULL),
NVL(cust_po_number, k_VNULL)),
NVL(cust_po_number, k_VNULL)))
AND NVL(customer_item_revision, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.customer_item_revision, 'Y',
NVL(c_OldDemand_rec.customer_item_revision, k_VNULL),
DECODE(x_Group_rec.match_within_rec.customer_item_revision, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.customer_item_revision, k_VNULL),
NVL(customer_item_revision, k_VNULL)),
NVL(customer_item_revision, k_VNULL)))
AND NVL(customer_job, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.customer_job, 'Y',
NVL(c_OldDemand_rec.customer_job, k_VNULL),
DECODE(x_Group_rec.match_within_rec.customer_job, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.customer_job, k_VNULL),
NVL(customer_job, k_VNULL)),
NVL(customer_job, k_VNULL)))
AND NVL(cust_model_serial_number, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.cust_model_serial_number, 'Y',
NVL(c_OldDemand_rec.cust_model_serial_number, k_VNULL),
DECODE(x_Group_rec.match_within_rec.cust_model_serial_number, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.cust_model_serial_number, k_VNULL),
NVL(cust_model_serial_number, k_VNULL)),
NVL(cust_model_serial_number, k_VNULL)))
AND NVL(cust_production_seq_num, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.cust_production_seq_num, 'Y',
NVL(c_OldDemand_rec.cust_production_seq_num, k_VNULL),
DECODE(x_Group_rec.match_within_rec.cust_production_seq_num, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.cust_production_seq_num, k_VNULL),
NVL(cust_production_seq_num, k_VNULL)),
NVL(cust_production_seq_num, k_VNULL)))
AND NVL(industry_attribute1, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute1, 'Y',
NVL(c_OldDemand_rec.industry_attribute1, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute1, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute1, k_VNULL),
NVL(industry_attribute1, k_VNULL)),
NVL(industry_attribute1, k_VNULL)))
AND NVL(industry_attribute2, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute2, 'Y',
NVL(c_OldDemand_rec.industry_attribute2, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute2, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute2, k_VNULL),
NVL(industry_attribute2, k_VNULL)),
NVL(industry_attribute2, k_VNULL)))
AND NVL(industry_attribute4, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute4, 'Y',
NVL(c_OldDemand_rec.industry_attribute4, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute4, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute4, k_VNULL),
NVL(industry_attribute4, k_VNULL)),
NVL(industry_attribute4, k_VNULL)))
AND NVL(industry_attribute5, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute5, 'Y',
NVL(c_OldDemand_rec.industry_attribute5, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute5, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute5, k_VNULL),
NVL(industry_attribute5, k_VNULL)),
NVL(industry_attribute5, k_VNULL)))
AND NVL(industry_attribute6, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute6, 'Y',
NVL(c_OldDemand_rec.industry_attribute6, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute6, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute6, k_VNULL),
NVL(industry_attribute6, k_VNULL)),
NVL(industry_attribute6, k_VNULL)))
AND NVL(industry_attribute10, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute10, 'Y',
NVL(c_OldDemand_rec.industry_attribute10, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute10, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute10, k_VNULL),
NVL(industry_attribute10, k_VNULL)),
NVL(industry_attribute10, k_VNULL)))
AND NVL(industry_attribute11, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute11, 'Y',
NVL(c_OldDemand_rec.industry_attribute11, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute11, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute11, k_VNULL),
NVL(industry_attribute11, k_VNULL)),
NVL(industry_attribute11, k_VNULL)))
AND NVL(industry_attribute12, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute12, 'Y',
NVL(c_OldDemand_rec.industry_attribute12, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute12, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute12, k_VNULL),
NVL(industry_attribute12, k_VNULL)),
NVL(industry_attribute12, k_VNULL)))
AND NVL(industry_attribute13, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute13, 'Y',
NVL(c_OldDemand_rec.industry_attribute13, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute13, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute13, k_VNULL),
NVL(industry_attribute13, k_VNULL)),
NVL(industry_attribute13, k_VNULL)))
AND NVL(industry_attribute14, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute14, 'Y',
NVL(c_OldDemand_rec.industry_attribute14, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute14, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute14, k_VNULL),
NVL(industry_attribute14, k_VNULL)),
NVL(industry_attribute14, k_VNULL)))
--global_atp?
AND NVL(industry_attribute15, k_VNULL) =
DECODE(g_ATP, k_ATP, NVL(industry_attribute15, k_VNULL),
NVL(c_OldDemand_rec.industry_attribute15, k_VNULL))
AND NVL(attribute1, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute1, 'Y',
NVL(c_OldDemand_rec.attribute1, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute1, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute1, k_VNULL),
NVL(attribute1, k_VNULL)),
NVL(attribute1, k_VNULL)))
AND NVL(attribute2, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute2, 'Y',
NVL(c_OldDemand_rec.attribute2, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute2, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute2, k_VNULL),
NVL(attribute2, k_VNULL)),
NVL(attribute2, k_VNULL)))
AND NVL(attribute3, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute3, 'Y',
NVL(c_OldDemand_rec.attribute3, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute3, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute3, k_VNULL),
NVL(attribute3, k_VNULL)),
NVL(attribute3, k_VNULL)))
AND NVL(attribute4, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute4, 'Y',
NVL(c_OldDemand_rec.attribute4, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute4, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute4, k_VNULL),
NVL(attribute4, k_VNULL)),
NVL(attribute4, k_VNULL)))
AND NVL(attribute5, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute5, 'Y',
NVL(c_OldDemand_rec.attribute5, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute5, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute5, k_VNULL),
NVL(attribute5, k_VNULL)),
NVL(attribute5, k_VNULL)))
AND NVL(attribute6, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute6, 'Y',
NVL(c_OldDemand_rec.attribute6, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute6, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute6, k_VNULL),
NVL(attribute6, k_VNULL)),
NVL(attribute6, k_VNULL)))
AND NVL(attribute7, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute7, 'Y',
NVL(c_OldDemand_rec.attribute7, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute7, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute7, k_VNULL),
NVL(attribute7, k_VNULL)),
NVL(attribute7, k_VNULL)))
AND NVL(attribute8, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute8, 'Y',
NVL(c_OldDemand_rec.attribute8, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute8, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute8, k_VNULL),
NVL(attribute8, k_VNULL)),
NVL(attribute8, k_VNULL)))
AND NVL(attribute9, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute9, 'Y',
NVL(c_OldDemand_rec.attribute9, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute9, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute9, k_VNULL),
NVL(attribute9, k_VNULL)),
NVL(attribute9, k_VNULL)))
AND NVL(attribute10, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute10, 'Y',
NVL(c_OldDemand_rec.attribute10, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute10, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute10, k_VNULL),
NVL(attribute10, k_VNULL)),
NVL(attribute10, k_VNULL)))
AND NVL(attribute11, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute11, 'Y',
NVL(c_OldDemand_rec.attribute11, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute11, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute11, k_VNULL),
NVL(attribute11, k_VNULL)),
NVL(attribute11, k_VNULL)))
AND NVL(attribute12, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute12, 'Y',
NVL(c_OldDemand_rec.attribute12, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute12, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute12, k_VNULL),
NVL(attribute12, k_VNULL)),
NVL(attribute12, k_VNULL)))
AND NVL(attribute13, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute13, 'Y',
NVL(c_OldDemand_rec.attribute13, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute13, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute13, k_VNULL),
NVL(attribute13, k_VNULL)),
NVL(attribute13, k_VNULL)))
AND NVL(attribute14, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute14, 'Y',
NVL(c_OldDemand_rec.attribute14, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute14, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute14, k_VNULL),
NVL(attribute14, k_VNULL)),
NVL(attribute14, k_VNULL)))
AND NVL(attribute15, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute15, 'Y',
NVL(c_OldDemand_rec.attribute15, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute15, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute15, k_VNULL),
NVL(attribute15, k_VNULL)),
NVL(attribute15, k_VNULL)));
x_MessageName => 'RLM_FROZEN_DELETE_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => NULL,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => NULL,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => c_OldDemand_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(c_OldDemand_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => c_OldDemand_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => c_OldDemand_rec.request_date,
x_Token6 => 'SCH_LINE_QTY', --Bugfix 6159269
x_value6 => v_del_line_qty, --Bugfix 6159269
x_Token7 => 'SEQ_INFO',
x_value7 => nvl(c_OldDemand_rec.cust_production_seq_num,'NULL') ||'-'||
nvl(c_OldDemand_rec.cust_model_serial_number,'NULL')||'-'||
nvl(c_OldDemand_rec.customer_job,'NULL'),
x_Token8 => 'MATCH_ATTR',
x_value8 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_DELETE_SEQ',
c_OldDemand_rec.line_id);
x_MessageName => 'RLM_FROZEN_DELETE',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => NULL,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => NULL,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => c_OldDemand_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(c_OldDemand_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => c_OldDemand_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => c_OldDemand_rec.request_date,
x_Token6 => 'SCH_LINE_QTY', --Bugfix 6159269
x_value6 => v_del_line_qty, --Bugfix 6159269
x_Token7 => 'MATCH_ATTR',
x_value7 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_DELETE',
c_OldDemand_rec.line_id);
rlm_core_sv.dlog(k_DEBUG,'in no consume delete ');
DeleteRequirement(x_Sched_rec, x_Group_rec,
v_Key_rec, k_RECONCILE, v_DeleteQty);
SELECT line_id, primary_quantity
FROM rlm_interface_lines
WHERE header_id = x_Sched_rec.header_id
--global_atp
AND ship_from_org_id = x_Group_rec.ship_from_org_id
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 order_header_id= x_Group_rec.order_header_id
/*AND nvl(cust_production_seq_num, k_VNULL)=
nvl(x_Group_rec.cust_production_seq_num,k_VNULL)*/
AND item_detail_type in (
DECODE(x_Sched_rec.schedule_purpose,
k_ADD,
1,0),
DECODE(x_Sched_rec.schedule_purpose,
k_ADD,
0,1),DECODE(x_Sched_rec.schedule_purpose,
k_ADD,
0,2))
AND DECODE(x_Sched_rec.schedule_purpose,
k_ADD,
item_detail_subtype,
DECODE(item_detail_subtype,'AHEAD_BEHIND', k_LARGE,
'CUM', k_LARGE, 'FINISHED', k_LARGE,
'HOLDOUT_QTY', k_LARGE, 'INVENTORY_BAL', k_LARGE,
'LABOR', k_LARGE, 'LABOR_MATERIAL', k_LARGE,
'MATERIAL', k_LARGE, 'PRIOR_CUM_REQ', k_LARGE,
'RECEIPT', k_LARGE, 'SHIPMENT', k_LARGE,
item_detail_subtype)
+1)
<= c_OldDemand_rec.demand_bucket_type_code
AND request_date BETWEEN c_OldDemand_rec.request_date
AND c_OldDemand_rec.end_date_time
AND process_status = rlm_core_sv.k_PS_AVAILABLE
--bug 2031077
--AND NVL(invoice_to_org_id, k_NNULL) =
-- NVL(c_OldDemand_rec.invoice_to_org_id, k_NNULL)
AND NVL(intmed_ship_to_org_id, k_NNULL) =
NVL(c_OldDemand_rec.intmed_ship_to_org_id, k_NNULL)
/* including matching critera */
AND NVL(cust_production_line, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.cust_production_line, 'Y',
NVL(c_OldDemand_rec.cust_production_line, k_VNULL),
DECODE(x_Group_rec.match_within_rec.cust_production_line, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.cust_production_line, k_VNULL),
NVL(cust_production_line, k_VNULL)),
NVL(cust_production_line, k_VNULL)))
AND NVL(customer_dock_code, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.customer_dock_code, 'Y',
NVL(c_OldDemand_rec.customer_dock_code, k_VNULL),
DECODE(x_Group_rec.match_within_rec.customer_dock_code, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.customer_dock_code, k_VNULL),
NVL(customer_dock_code, k_VNULL)),
NVL(customer_dock_code, k_VNULL)))
/* Fix for Bug #: 1588331
For consumption, do not use request_date as a
matching attribute across schedules */
AND NVL(cust_po_number, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.cust_po_number, 'Y',
NVL(c_OldDemand_rec.cust_po_number, k_VNULL),
DECODE(x_Group_rec.match_within_rec.cust_po_number, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.cust_po_number, k_VNULL),
NVL(cust_po_number, k_VNULL)),
NVL(cust_po_number, k_VNULL)))
AND NVL(customer_item_revision, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.customer_item_revision, 'Y',
NVL(c_OldDemand_rec.customer_item_revision, k_VNULL),
DECODE(x_Group_rec.match_within_rec.customer_item_revision, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.customer_item_revision, k_VNULL),
NVL(customer_item_revision, k_VNULL)),
NVL(customer_item_revision, k_VNULL)))
AND NVL(customer_job, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.customer_job, 'Y',
NVL(c_OldDemand_rec.customer_job, k_VNULL),
DECODE(x_Group_rec.match_within_rec.customer_job, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.customer_job, k_VNULL),
NVL(customer_job, k_VNULL)),
NVL(customer_job, k_VNULL)))
AND NVL(cust_model_serial_number, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.cust_model_serial_number, 'Y',
NVL(c_OldDemand_rec.cust_model_serial_number, k_VNULL),
DECODE(x_Group_rec.match_within_rec.cust_model_serial_number, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.cust_model_serial_number, k_VNULL),
NVL(cust_model_serial_number, k_VNULL)),
NVL(cust_model_serial_number, k_VNULL)))
AND NVL(industry_attribute1, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute1, 'Y',
NVL(c_OldDemand_rec.industry_attribute1, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute1, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute1, k_VNULL),
NVL(industry_attribute1, k_VNULL)),
NVL(industry_attribute1, k_VNULL)))
/* Fix for Bug #: 1588331
For consumption, do not use industry_attribute2 as a
matching attribute across schedules. */
AND NVL(industry_attribute4, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute4, 'Y',
NVL(c_OldDemand_rec.industry_attribute4, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute4, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute4, k_VNULL),
NVL(industry_attribute4, k_VNULL)),
NVL(industry_attribute4, k_VNULL)))
AND NVL(industry_attribute5, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute5, 'Y',
NVL(c_OldDemand_rec.industry_attribute5, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute5, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute5, k_VNULL),
NVL(industry_attribute5, k_VNULL)),
NVL(industry_attribute5, k_VNULL)))
AND NVL(industry_attribute6, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute6, 'Y',
NVL(c_OldDemand_rec.industry_attribute6, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute6, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute6, k_VNULL),
NVL(industry_attribute6, k_VNULL)),
NVL(industry_attribute6, k_VNULL)))
AND NVL(industry_attribute10, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute10, 'Y',
NVL(c_OldDemand_rec.industry_attribute10, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute10, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute10, k_VNULL),
NVL(industry_attribute10, k_VNULL)),
NVL(industry_attribute10, k_VNULL)))
AND NVL(industry_attribute11, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute11, 'Y',
NVL(c_OldDemand_rec.industry_attribute11, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute11, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute11, k_VNULL),
NVL(industry_attribute11, k_VNULL)),
NVL(industry_attribute11, k_VNULL)))
AND NVL(industry_attribute12, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute12, 'Y',
NVL(c_OldDemand_rec.industry_attribute12, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute12, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute12, k_VNULL),
NVL(industry_attribute12, k_VNULL)),
NVL(industry_attribute12, k_VNULL)))
AND NVL(industry_attribute13, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute13, 'Y',
NVL(c_OldDemand_rec.industry_attribute13, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute13, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute13, k_VNULL),
NVL(industry_attribute13, k_VNULL)),
NVL(industry_attribute13, k_VNULL)))
AND NVL(industry_attribute14, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.industry_attribute14, 'Y',
NVL(c_OldDemand_rec.industry_attribute14, k_VNULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute14, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.industry_attribute14, k_VNULL),
NVL(industry_attribute14, k_VNULL)),
NVL(industry_attribute14, k_VNULL)))
--global_atp
AND NVL(industry_attribute15, k_VNULL) =
DECODE(g_ATP, k_ATP, NVL(industry_attribute15, k_VNULL),
NVL(c_OldDemand_rec.industry_attribute15, k_VNULL))
AND NVL(attribute1, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute1, 'Y',
NVL(c_OldDemand_rec.attribute1, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute1, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute1, k_VNULL),
NVL(attribute1, k_VNULL)),
NVL(attribute1, k_VNULL)))
AND NVL(attribute2, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute2, 'Y',
NVL(c_OldDemand_rec.attribute2, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute2, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute2, k_VNULL),
NVL(attribute2, k_VNULL)),
NVL(attribute2, k_VNULL)))
AND NVL(attribute3, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute3, 'Y',
NVL(c_OldDemand_rec.attribute3, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute3, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute3, k_VNULL),
NVL(attribute3, k_VNULL)),
NVL(attribute3, k_VNULL)))
AND NVL(attribute4, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute4, 'Y',
NVL(c_OldDemand_rec.attribute4, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute4, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute4, k_VNULL),
NVL(attribute4, k_VNULL)),
NVL(attribute4, k_VNULL)))
AND NVL(attribute5, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute5, 'Y',
NVL(c_OldDemand_rec.attribute5, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute5, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute5, k_VNULL),
NVL(attribute5, k_VNULL)),
NVL(attribute5, k_VNULL)))
AND NVL(attribute6, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute6, 'Y',
NVL(c_OldDemand_rec.attribute6, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute6, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute6, k_VNULL),
NVL(attribute6, k_VNULL)),
NVL(attribute6, k_VNULL)))
AND NVL(attribute7, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute7, 'Y',
NVL(c_OldDemand_rec.attribute7, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute7, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute7, k_VNULL),
NVL(attribute7, k_VNULL)),
NVL(attribute7, k_VNULL)))
AND NVL(attribute8, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute8, 'Y',
NVL(c_OldDemand_rec.attribute8, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute8, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute8, k_VNULL),
NVL(attribute8, k_VNULL)),
NVL(attribute8, k_VNULL)))
AND NVL(attribute9, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute9, 'Y',
NVL(c_OldDemand_rec.attribute9, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute9, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute9, k_VNULL),
NVL(attribute9, k_VNULL)),
NVL(attribute9, k_VNULL)))
AND NVL(attribute10, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute10, 'Y',
NVL(c_OldDemand_rec.attribute10, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute10, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute10, k_VNULL),
NVL(attribute10, k_VNULL)),
NVL(attribute10, k_VNULL)))
AND NVL(attribute11, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute11, 'Y',
NVL(c_OldDemand_rec.attribute11, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute11, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute11, k_VNULL),
NVL(attribute11, k_VNULL)),
NVL(attribute11, k_VNULL)))
AND NVL(attribute12, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute12, 'Y',
NVL(c_OldDemand_rec.attribute12, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute12, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute12, k_VNULL),
NVL(attribute12, k_VNULL)),
NVL(attribute12, k_VNULL)))
AND NVL(attribute13, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute13, 'Y',
NVL(c_OldDemand_rec.attribute13, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute13, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute13, k_VNULL),
NVL(attribute13, k_VNULL)),
NVL(attribute13, k_VNULL)))
AND NVL(attribute14, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute14, 'Y',
NVL(c_OldDemand_rec.attribute14, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute14, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute14, k_VNULL),
NVL(attribute14, k_VNULL)),
NVL(attribute14, k_VNULL)))
AND NVL(attribute15, k_VNULL) =
DECODE(x_Group_rec.match_across_rec.attribute15, 'Y',
NVL(c_OldDemand_rec.attribute15, k_VNULL),
DECODE(x_Group_rec.match_within_rec.attribute15, 'Y',
DECODE(c_OldDemand_rec.rla_schedule_type_code, x_Sched_rec.schedule_type,
NVL(c_OldDemand_rec.attribute15, k_VNULL),
NVL(attribute15, k_VNULL)),
NVL(attribute15, k_VNULL)));
DeleteRequirement(x_Sched_rec, x_Group_rec, v_Key_rec,
k_RECONCILE, v_DeleteQty);
AND NOT ProcessConstraint(v_Key_rec,v_Qty_rec,k_UPDATE,v_Quantity) THEN
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(k_DEBUG,'v_quantity',v_quantity);
UpdateRequirement(x_Sched_rec, x_Group_rec, v_Key_rec,
v_quantity);
SELECT line_number INTO v_line_num
FROM oe_order_lines
WHERE line_id = c_OldDemand_rec.line_id;
SELECT DECODE(x_ScheduleType,x_group_rec.schedule_type_one,1,
x_group_rec.schedule_type_two,2,
x_group_rec.schedule_type_three,3) -
DECODE(x_Sched_rec.schedule_type,x_group_rec.schedule_type_one,1,
x_group_rec.schedule_type_two,2,
x_group_rec.schedule_type_three,3)
INTO v_cannot_replace
FROM dual;
IF (x_DemandCount > 0) AND (NOT AlreadyUpdated(x_line_id_tab)) THEN
--
IF x_Sched_rec.schedule_purpose IN (k_DELETE, k_CANCEL) THEN
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(k_DEBUG,'Deleting requirement due to purpose code of '
,x_Sched_rec.schedule_purpose);
RLM_TPA_SV.UpdateDemand(x_Sched_rec, x_Group_rec, x_Key_rec,
x_SumOrderedQty, x_DemandType);
ELSIF x_Sched_rec.schedule_purpose NOT IN (k_DELETE,k_CANCEL) THEN
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(k_DEBUG,'insert x_key_rec.req_rec.primary_quantity',
x_key_rec.req_rec.primary_quantity);
RLM_TPA_SV.InsertRequirement(x_Sched_rec, x_Group_rec,
x_Key_rec, k_RECONCILE,
x_Key_rec.req_rec.primary_quantity);
v_Select_Clause VARCHAR2(32000);
w_Select_Clause1 VARCHAR2(32000);
w_Select_Clause2 VARCHAR2(32000);
v_select_clause :=
'SELECT o.line_id,(NVL(o.ordered_quantity,0)-NVL(o.shipped_quantity,0)),'||
'o.rla_schedule_type_code,'|| x_group_rec.customer_id ||
',r.header_id interface_header_id, r.line_id interface_line_id,'||
'r.cust_production_line,r.customer_dock_code,r.request_date,'||
'r.schedule_date,r.cust_po_number,r.customer_item_revision,'||
'r.customer_job,r.cust_model_serial_number,r.cust_production_seq_num,'||
'r.industry_attribute1,r.industry_attribute2,r.industry_attribute3,'||
'r.industry_attribute4,r.industry_attribute5,r.industry_attribute6,'||
'r.industry_attribute7,r.industry_attribute8,r.industry_attribute9,'||
'r.industry_attribute10,r.industry_attribute11,r.industry_attribute12,'||
'r.industry_attribute13,r.industry_attribute14,r.industry_attribute15,'||
'r.industry_context,r.attribute1,r.attribute2,r.attribute3,'||
'r.attribute4,r.attribute5,r.attribute6,r.attribute7,r.attribute8,'||
'r.attribute9,r.attribute10,r.attribute11,r.attribute12,r.attribute13,'||
'r.attribute14,r.attribute15,r.attribute_category,r.tp_attribute1,'||
'r.tp_attribute2,r.tp_attribute3,r.tp_attribute4,r.tp_attribute5,'||
'r.tp_attribute6,r.tp_attribute7,r.tp_attribute8,r.tp_attribute9,'||
'r.tp_attribute10,r.tp_attribute11,r.tp_attribute12,r.tp_attribute13,'||
'r.tp_attribute14,r.tp_attribute15,r.tp_attribute_category,'||
'r.item_detail_type,r.item_detail_subtype,r.intrmd_ship_to_id,'||
'r.ship_to_org_id,r.invoice_to_org_id,r.primary_quantity,'||
'r.intmed_ship_to_org_id,r.customer_item_id,r.inventory_item_id,'||
'r.order_header_id,o.authorized_to_ship_flag,r.ship_from_org_id,''' ||
x_sched_rec.schedule_type ||''',''CUST'' item_identifier_type,'||
'r.customer_item_ext,r.agreement_id,r.price_list_id,'||
x_Sched_rec.schedule_header_id ||
',r.schedule_line_id,r.process_status,r.uom_code,r.cust_po_line_num,r.blanket_number ' ||
'FROM oe_order_lines_all o,rlm_interface_lines r ';
w_Select_Clause1 :=
'SELECT TO_NUMBER(NULL),TO_NUMBER(NULL),TO_CHAR(NULL),'||
x_group_rec.customer_id ||
',r1.header_id interface_header_id, r1.line_id interface_line_id,'||
'r1.cust_production_line,r1.customer_dock_code,r1.request_date,'||
'r1.schedule_date,r1.cust_po_number,r1.customer_item_revision,'||
'r1.customer_job,r1.cust_model_serial_number,r1.cust_production_seq_num,'||
'r1.industry_attribute1,r1.industry_attribute2,r1.industry_attribute3,'||
'r1.industry_attribute4,r1.industry_attribute5,r1.industry_attribute6,'||
'r1.industry_attribute7,r1.industry_attribute8,r1.industry_attribute9,'||
'r1.industry_attribute10,r1.industry_attribute11,r1.industry_attribute12,'||
'r1.industry_attribute13,r1.industry_attribute14,r1.industry_attribute15,'||
'r1.industry_context,r1.attribute1,r1.attribute2,r1.attribute3,'||
'r1.attribute4,r1.attribute5,r1.attribute6,r1.attribute7,r1.attribute8,'||
'r1.attribute9,r1.attribute10,r1.attribute11,r1.attribute12,r1.attribute13,'||
'r1.attribute14,r1.attribute15,r1.attribute_category,r1.tp_attribute1,'||
'r1.tp_attribute2,r1.tp_attribute3,r1.tp_attribute4,r1.tp_attribute5,'||
'r1.tp_attribute6,r1.tp_attribute7,r1.tp_attribute8,r1.tp_attribute9,'||
'r1.tp_attribute10,r1.tp_attribute11,r1.tp_attribute12,r1.tp_attribute13,'||
'r1.tp_attribute14,r1.tp_attribute15,r1.tp_attribute_category,'||
'r1.item_detail_type,r1.item_detail_subtype,r1.intrmd_ship_to_id,'||
'r1.ship_to_org_id,r1.invoice_to_org_id,r1.primary_quantity,'||
'r1.intmed_ship_to_org_id,r1.customer_item_id,r1.inventory_item_id,'||
'r1.order_header_id,''' || x_Demand_Type ||
''' authorized_to_ship_flag,r1.ship_from_org_id,''' ||
x_sched_rec.schedule_type ||''',''CUST'' item_identifier_type,'||
'r1.customer_item_ext,r1.agreement_id,r1.price_list_id,'||
x_Sched_rec.schedule_header_id ||
',r1.schedule_line_id,r1.process_status,r1.uom_code,r1.cust_po_line_num,r1.blanket_number ' ||
'FROM rlm_interface_lines r1 ';
x_Sql1 := v_select_clause ||
v_where_clause1 ||
v_where_clause2 ||
' AND o.authorized_to_ship_flag = ''N'' ';
x_Sql1 := v_select_clause ||
v_where_clause1 ||
v_where_clause2 ||
' AND o.authorized_to_ship_flag = ''Y'' ';
w_select_clause2 :=
'(SELECT ''X'' ' ||
'FROM oe_order_lines_all o,rlm_interface_lines r ' ||
v_where_clause1 ||
v_where_clause2 ||
' AND r1.line_id = r.line_id) ';
x_Sql2 := w_select_clause1 ||
w_where_clause1 ||
' AND NOT EXISTS ' ||
w_select_clause2 ||
v_order_clause;
x_Sql := v_select_clause ||
v_where_clause1 ||
v_where_clause2 ||
' UNION ALL ' || x_Sql2;
x_Sum_Sql := 'SELECT r.line_id, SUM(NVL(o.ordered_quantity,0)-NVL(o.shipped_quantity,0)), COUNT(1), MIN(o.line_id) ' ||
'FROM rlm_interface_lines r, oe_order_lines_all o ' ||
v_where_clause1 ||
v_where_clause2 ||
' GROUP BY r.line_id ORDER BY line_id';
rlm_core_sv.dlog(k_DEBUG, 'v_select_clause', v_select_clause);
rlm_core_sv.dlog(k_DEBUG, 'w_select_clause1', w_select_clause1);
rlm_core_sv.dlog(k_DEBUG, 'w_select_clause2', substr(w_select_clause2, 1, 800));
rlm_core_sv.dlog(k_DEBUG, 'w_select_clause2 Contd.', substr(w_select_clause2, 800, 1600));
rlm_core_sv.dlog(k_DEBUG, 'w_select_clause2 Contd.', substr(w_select_clause2, 1600, 2400));
v_DeleteQty NUMBER;
SELECT COUNT(*)
INTO v_ActualCount
FROM rlm_interface_lines il,
rlm_schedule_lines_all sl
WHERE il.header_id = x_Sched_rec.header_id
AND il.ship_from_org_id = x_Group_rec.ship_from_org_id
AND il.ship_to_org_id = x_Group_rec.ship_to_org_id
AND il.inventory_item_id = x_Group_rec.inventory_item_id
AND il.customer_item_id = x_Group_rec.customer_item_id
AND il.schedule_line_id = sl.line_id
AND NVL(il.item_detail_type, ' ')
<> rlm_manage_demand_sv.k_SHIP_RECEIPT_INFO
AND sl.qty_type_code = rlm_manage_demand_sv.k_ACTUAL
AND il.org_id = sl.org_id;
SELECT TO_CHAR(TRUNC(min(request_date)), 'RRRR/MM/DD HH24:MI:SS')
INTO v_min_horizon_date
FROM rlm_interface_lines
WHERE header_id=x_sched_rec.header_id
AND inventory_item_id = x_group_rec.inventory_item_id
AND customer_item_id = x_group_rec.customer_item_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;
g_NewDemandTab.DELETE;
g_WhereTab1.DELETE;
g_WhereTab2.DELETE;
g_BindVarTab.DELETE;
IF x_Sched_rec.schedule_purpose NOT IN (k_DELETE, k_CANCEL, k_ADD) THEN
--
v_Key_rec.oe_line_id := v_line_id_tmp;
DeleteRequirement(x_Sched_rec, x_Group_rec,
v_Key_rec, k_RECONCILE,
v_DeleteQty);
IF x_Sched_rec.schedule_purpose NOT IN (k_DELETE,k_CANCEL) THEN
--
v_NewCount := v_NewCount +1;
RLM_TPA_SV.InsertRequirement(x_Sched_rec, x_Group_rec,
v_Key_rec, k_RECONCILE,
v_Key_rec.req_rec.primary_quantity);
SELECT SUM(nvl(ordered_quantity,0)) ,SUM(nvl(shipped_quantity,0))
INTO v_total_ordered_qty, v_total_partial_shipped_qty
FROM oe_order_lines oel
WHERE oel.line_set_id IN
(SELECT ol.line_set_id
FROM oe_order_lines ol
WHERE ol.line_id = v_line_id_tmp);
v_line_id_tab.DELETE;
v_DeleteQty NUMBER;
g_NewDemandTab.DELETE;
g_WhereTab1.DELETE;
g_WhereTab2.DELETE;
g_BindVarTab.DELETE;
IF x_Sched_rec.schedule_purpose NOT IN (k_DELETE, k_CANCEL, k_ADD) THEN
--
v_Key_rec.oe_line_id := v_line_id_tmp;
DeleteRequirement(x_Sched_rec, x_Group_rec,
v_Key_rec, k_RECONCILE,
v_DeleteQty);
IF x_Sched_rec.schedule_purpose NOT IN (k_DELETE,k_CANCEL) THEN
--
v_NewCount := v_NewCount +1;
RLM_TPA_SV.InsertRequirement(x_Sched_rec, x_Group_rec,
v_Key_rec, k_RECONCILE,
v_Key_rec.req_rec.primary_quantity);
v_line_id_tab.DELETE;
PROCEDURE UpdateDemand
===========================================================================*/
PROCEDURE UpdateDemand(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
x_Group_rec IN OUT NOCOPY RLM_DP_SV.t_Group_rec,
x_Key_rec IN OUT NOCOPY RLM_RD_SV.t_Key_rec,
x_SumOrderedQty IN NUMBER,
x_DemandType IN VARCHAR2)
IS
v_QtyDelta NUMBER;
v_DeleteQty NUMBER;
rlm_core_sv.dpush(k_SDEBUG,'UpdateDemand');
RLM_TPA_SV.DeleteDemand(x_Sched_rec,
x_Group_rec,
x_Key_rec,
v_Demand_ref);
rlm_message_sv.sql_error('rlm_rd_sv.UpdateDemand',x_progress);
END UpdateDemand;
PROCEDURE DeleteDemand
===========================================================================*/
PROCEDURE DeleteDemand(x_Sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
x_Group_rec IN OUT NOCOPY RLM_DP_SV.t_Group_rec,
x_Key_rec IN OUT NOCOPY RLM_RD_SV.t_Key_rec,
x_Demand_ref IN OUT NOCOPY RLM_RD_SV.t_Cursor_ref)
IS
v_DeleteQty NUMBER;
rlm_core_sv.dpush(k_SDEBUG,'DeleteDemand');
DeleteRequirement(x_Sched_rec, x_Group_rec,
x_Key_rec, k_RECONCILE,
v_DeleteQty);
rlm_message_sv.sql_error('rlm_rd_sv.DeleteDemand',x_progress);
END DeleteDemand;
IF v_QtyDelta > 0 AND NOT ProcessConstraint(x_Key_rec, v_Qty_rec, k_UPDATE,
x_Key_rec.dem_rec.ordered_quantity + v_QtyDelta) THEN
IF NOT (IsFrozen(TRUNC(SYSDATE), x_Group_rec, x_Key_rec.dem_rec.request_date) OR
IsFrozen(TRUNC(SYSDATE), x_Group_rec, x_Key_rec.req_rec.request_date)) THEN
--
UpdateRequirement(x_Sched_rec, x_Group_rec,
x_Key_rec, x_Key_rec.dem_rec.ordered_quantity + v_QtyDelta);
x_MessageName => 'RLM_FROZEN_UPDATE_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(x_key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SCH_LINE_QTY',
x_value6 => g_sch_line_qty,
x_Token7 => 'SEQ_INFO',
x_value7 => nvl(x_Key_rec.dem_rec.cust_production_seq_num,'NULL') ||'-'||
nvl(x_Key_rec.dem_rec.cust_model_serial_number,'NULL')||'-'||
nvl(x_Key_rec.dem_rec.customer_job,'NULL'),
x_Token8 => 'MATCH_ATTR',
x_value8 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_UPDATE_SEQ',
x_key_rec.req_rec.line_id);
x_MessageName => 'RLM_FROZEN_UPDATE',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(x_key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SCH_LINE_QTY',
x_value6 => g_sch_line_qty,
x_Token7 => 'MATCH_ATTR',
x_value7 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_UPDATE',
x_key_rec.req_rec.line_id);
RLM_TPA_SV.InsertRequirement(x_Sched_rec, x_Group_rec,
x_Key_rec, k_NORECONCILE, v_QtyDelta);
v_DeleteQty NUMBER;
IF NOT ProcessConstraint(x_Key_rec, v_Qty_rec, k_DELETE,
x_Key_rec.dem_rec.ordered_quantity-v_QtyDelta)
AND NOT
(IsFrozen(TRUNC(SYSDATE), x_Group_rec,
x_Key_rec.dem_rec.request_date) OR
IsFrozen(TRUNC(SYSDATE), x_Group_rec,
x_Key_rec.dem_rec.schedule_date)) THEN
--
DeleteRequirement(x_Sched_rec, x_Group_rec,
x_Key_rec, k_RECONCILE,
v_DeleteQty);
IF NOT ProcessConstraint(x_Key_rec, v_Qty_rec, k_UPDATE, x_Key_rec.dem_rec.ordered_quantity - v_QtyDelta) THEN
IF NOT (IsFrozen(TRUNC(SYSDATE), x_Group_rec, x_Key_rec.dem_rec.request_date) OR
IsFrozen(TRUNC(SYSDATE), x_Group_rec, x_Key_rec.req_rec.request_date)) THEN
UpdateRequirement(x_Sched_rec, x_Group_rec, x_Key_rec, x_Key_rec.dem_rec.ordered_quantity - v_QtyDelta);
x_MessageName => 'RLM_FROZEN_UPDATE_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(x_key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SCH_LINE_QTY',
x_value6 => g_sch_line_qty,
x_Token7 => 'SEQ_INFO',
x_value7 => nvl(x_Key_rec.dem_rec.cust_production_seq_num,'NULL') ||'-'||
nvl(x_Key_rec.dem_rec.cust_model_serial_number,'NULL')||'-'||
nvl(x_Key_rec.dem_rec.customer_job,'NULL'),
x_Token8 => 'MATCH_ATTR',
x_value8 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_UPDATE_SEQ',
x_key_rec.req_rec.line_id);
x_MessageName => 'RLM_FROZEN_UPDATE',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(x_key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SCH_LINE_QTY',
x_value6 => g_sch_line_qty,
x_Token7 => 'MATCH_ATTR',
x_value7 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_UPDATE',
x_key_rec.req_rec.line_id);
IF NOT ProcessConstraint(x_Key_rec, v_Qty_rec, k_UPDATE_ATTR,
x_Key_rec.dem_rec.ordered_quantity) THEN
--
UpdateRequirement(x_Sched_rec, x_Group_rec, x_Key_rec,
x_Key_rec.dem_rec.ordered_quantity);
x_MessageName => 'RLM_UNABLE_ATTR_UPDATE_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_Key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_Key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_Key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 =>rlm_core_sv.get_order_line_number(x_Key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_Key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SEQ_INFO',
x_value6 => nvl(x_key_rec.dem_rec.cust_production_seq_num,'NULL') ||'-'||
nvl(x_key_rec.dem_rec.cust_model_serial_number,'NULL')||'-'||
nvl(x_key_rec.dem_rec.customer_job,'NULL'),
x_Token7 => 'MATCH_ATTR',
x_value7 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_UNABLE_ATTR_UPDATE_SEQ',
x_key_rec.req_rec.line_id);
x_MessageName => 'RLM_UNABLE_ATTR_UPDATE',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_Key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_Key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_Key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(x_Key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_Key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'MATCH_ATTR',
x_value6 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_UNABLE_ATTR_UPDATE',
x_key_rec.req_rec.line_id);
x_MessageName => 'RLM_FROZEN_UPDATE_SEQ',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(x_key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SCH_LINE_QTY', --Bugfix 6159269
x_value6 => g_sch_line_qty, --Bugfix 6159269
x_Token7 => 'SEQ_INFO',
x_value7 => nvl(x_Key_rec.dem_rec.cust_production_seq_num,'NULL') ||'-'||
nvl(x_Key_rec.dem_rec.cust_model_serial_number,'NULL')||'-'||
nvl(x_Key_rec.dem_rec.customer_job,'NULL'),
x_Token8 => 'MATCH_ATTR',
x_value8 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_UPDATE_SEQ',
x_key_rec.req_rec.line_id);
x_MessageName => 'RLM_FROZEN_UPDATE',
x_InterfaceHeaderId => x_sched_rec.header_id,
x_InterfaceLineId => x_key_rec.req_rec.line_id,
x_ScheduleHeaderId => x_sched_rec.schedule_header_id,
x_ScheduleLineId => x_key_rec.req_rec.schedule_line_id,
x_OrderHeaderId => x_group_rec.setup_terms_rec.header_id,
x_OrderLineId => x_key_rec.dem_rec.line_id,
x_Token1 => 'LINE',
x_value1 => rlm_core_sv.get_order_line_number(x_key_rec.dem_rec.line_id),
x_Token2 => 'ORDER',
x_value2 => rlm_core_sv.get_order_number(x_group_rec.setup_terms_rec.header_id),
x_Token3 => 'QUANTITY',
x_value3 => x_key_rec.dem_rec.ordered_quantity,
x_Token4 => 'CUSTITEM',
x_value4 => rlm_core_sv.get_item_number(x_group_rec.customer_item_id),
x_Token5 => 'REQ_DATE',
x_value5 => x_key_rec.dem_rec.request_date,
x_Token6 => 'SCH_LINE_QTY', --Bugfix 6159269
x_value6 => g_sch_line_qty, --Bugfix 6159269
x_Token7 => 'MATCH_ATTR',
x_value7 => v_MatchAttrTxt);
rlm_core_sv.dlog(k_DEBUG,'RLM_FROZEN_UPDATE',
x_key_rec.req_rec.line_id);
IF x_Operation = k_DELETE THEN
--pdue, global_atp
v_line_id_tab(0) := x_Key_rec.dem_rec.line_id;
IF NOT AlreadyUpdated(v_line_id_tab) THEN
--
v_Index := g_Op_tab_Unschedule.COUNT + 1;
g_Op_tab_Unschedule(v_Index).operation := OE_GLOBALS.G_OPR_DELETE;
IF x_Operation = k_INSERT THEN
--pdue
v_Index := g_Op_tab.COUNT + 1;
ELSIF x_Operation = k_UPDATE THEN
--pdue, global_atp
IF x_Quantity < x_Key_rec.dem_rec.ordered_quantity THEN
--
v_Index := g_Op_tab_Unschedule.COUNT + 1;
g_Op_tab_Unschedule(v_Index).operation := OE_GLOBALS.G_OPR_UPDATE;
g_Op_tab(v_Index).operation := OE_GLOBALS.G_OPR_UPDATE;
FUNCTION UpdateGroupStatus
===========================================================================*/
PROCEDURE UpdateGroupStatus( 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
x_progress VARCHAR2(3) := '010';
rlm_core_sv.dpush(k_SDEBUG,'UpdateGroupStatus');
rlm_core_sv.dlog(k_DEBUG,'UpdateGroupStatus to ', x_status);
rlm_core_sv.dlog(k_DEBUG,'x_UpdateLevel to ', x_UpdateLevel);
IF x_UpdateLevel <> 'GROUP' THEN
--
UPDATE rlm_interface_lines
SET process_status = x_Status
WHERE header_id = x_header_id
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_FROZEN_FIRM)
AND item_detail_type IN (k_PAST_DUE_FIRM, k_FIRM, k_FORECAST, k_RECT);
UPDATE rlm_schedule_lines
SET process_status = x_Status
WHERE header_id = x_ScheduleHeaderid
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_FROZEN_FIRM)
AND item_detail_type IN (k_PAST_DUE_FIRM, k_FIRM, k_FORECAST, k_RECT);
rlm_core_sv.dlog(k_DEBUG,'Update Group');
UPDATE rlm_interface_lines
SET process_status = x_Status
WHERE header_id = x_header_id
AND ship_from_org_id = x_Group_rec.ship_from_org_id
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 order_header_id = x_Group_rec.order_header_id
/*AND nvl(cust_production_seq_num,k_VNULL) =
nvl(x_Group_rec.cust_production_seq_num, k_VNULL)
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_FROZEN_FIRM) */
AND item_detail_type IN (k_PAST_DUE_FIRM, k_FIRM, k_FORECAST, k_RECT);
rlm_core_sv.dlog(k_DEBUG,'No of interface Lines Updated ', SQL%ROWCOUNT);
UPDATE rlm_schedule_lines
SET process_status = x_Status
WHERE header_id = x_ScheduleheaderId
AND ship_from_org_id = x_Group_rec.ship_from_org_id
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 order_header_id = x_Group_rec.order_header_id
/*AND nvl(cust_production_seq_num, k_VNULL) =
nvl(x_Group_rec.cust_production_seq_num, k_VNULL)*/
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_FROZEN_FIRM, rlm_core_sv.k_PS_ERROR)
AND item_detail_type IN (k_PAST_DUE_FIRM, k_FIRM, k_FORECAST, k_RECT);
rlm_core_sv.dlog(k_DEBUG,'No of Schedule Lines Updated ', SQL%ROWCOUNT);
rlm_message_sv.sql_error('rlm_rd_sv.UpdateGroupStatus',x_progress);
END UpdateGroupStatus;
SELECT *
FROM rlm_interface_headers
WHERE header_id = x_header_id
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,rlm_core_sv.k_PS_PARTIAL_PROCESSED)
FOR UPDATE NOWAIT;
PROCEDURE UpdateHeaderStatus
===========================================================================*/
PROCEDURE UpdateHeaderStatus( x_HeaderId IN NUMBER,
x_ScheduleHeaderId IN NUMBER,
x_status IN NUMBER)
IS
x_progress VARCHAR2(3) := '010';
rlm_core_sv.dpush(k_SDEBUG,'UpdateHeaderStatus');
rlm_core_sv.dlog(k_DEBUG,'UpdateHeaderStatus to ', x_status);
UPDATE rlm_interface_headers
SET process_status = x_Status
WHERE header_id = x_HeaderId;
UPDATE rlm_schedule_headers
SET process_status = x_Status
WHERE header_id = x_ScheduleHeaderId;
rlm_message_sv.sql_error('rlm_rd_sv.UpdateHeaderStatus',x_progress);
END UpdateHeaderStatus;
SELECT *
FROM rlm_interface_lines_all
WHERE header_id = x_header_id
AND ship_from_org_id = x_Group_rec.ship_from_org_id
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 nvl(schedule_item_num,k_NNULL) = nvl(x_Group_rec.schedule_item_num, k_NNULL)
AND order_header_id = x_Group_rec.order_header_id
/*AND nvl(cust_production_seq_num,k_VNULL) = nvl(x_Group_rec.cust_production_seq_num, k_VNULL)*/
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE, rlm_core_sv.k_PS_FROZEN_FIRM)
FOR UPDATE NOWAIT;
FUNCTION NAME: AlreadyUpdated
===========================================================================*/
FUNCTION AlreadyUpdated(x_line_id_tab IN t_matching_line)
RETURN BOOLEAN
IS
v_already_updated BOOLEAN DEFAULT FALSE;
rlm_core_sv.dpush(k_SDEBUG, 'AlreadyUpdated');
rlm_core_sv.dlog(k_DEBUG, 'This line id has already been updated', x_line_id_tab(i));
rlm_core_sv.dlog(k_DEBUG, 'This line id has already been updated', x_line_id_tab(i));
rlm_core_sv.dlog(k_DEBUG, 'This line id has already been updated', x_line_id_tab(k));
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 ACCT_SITE
WHERE ACCT_SITE.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;
SELECT ECE_TP_LOCATION_CODE_EXT, ECE_TP_TRANSLATOR_CODE
INTO v_ece_tp_location_code_ext,v_ece_tp_translator_code
FROM rlm_interface_headers
WHERE header_id = x_key_rec.req_rec.header_id;
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.ECE_TP_LOCATION_CODE = v_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_key_rec.req_rec.ship_to_address_id;
SELECT account_number
INTO x_customer_number
FROM HZ_CUST_ACCOUNTS
WHERE ACCOUNT_NUMBER = x_key_rec.req_rec.customer_id;
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;
SELECT
DECODE(x_Group_rec.match_within_rec.cust_production_line,'Y',cust_production_line, NULL),
DECODE(x_Group_rec.match_within_rec.customer_dock_code,'Y',customer_dock_code,NULL),
NULL,--request_date
NULL,--schedule_date
DECODE(x_Group_rec.match_within_rec.cust_po_number,'Y',cust_po_number,NULL),
DECODE(x_Group_rec.match_within_rec.customer_item_revision,'Y', customer_item_revision, NULL),
DECODE(x_Group_rec.match_within_rec.customer_job,'Y',customer_job, NULL),
DECODE(x_Group_rec.match_within_rec.cust_model_serial_number,'Y',cust_model_serial_number, NULL),
DECODE(x_Group_rec.match_within_rec.cust_production_seq_num,'Y',cust_production_seq_num,NULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute1,'Y', industry_attribute1,NULL),
NULL,
NULL,
DECODE(x_Group_rec.match_within_rec.industry_attribute4,'Y', industry_attribute4,NULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute5,'Y', industry_attribute5,NULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute6,'Y', industry_attribute6,NULL),
NULL,
NULL,
DECODE(x_Group_rec.match_within_rec.industry_attribute9, 'Y', industry_attribute9,NULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute10, 'Y', industry_attribute10,NULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute11, 'Y', industry_attribute11,NULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute12, 'Y', industry_attribute12,NULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute13, 'Y', industry_attribute13,NULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute14, 'Y', industry_attribute14,NULL),
DECODE(x_Group_rec.match_within_rec.industry_attribute15, 'Y', industry_attribute15,NULL),
DECODE(x_Group_rec.match_within_rec.attribute1, 'Y', attribute1, NULL),
DECODE(x_Group_rec.match_within_rec.attribute2, 'Y', attribute2, NULL),
DECODE(x_Group_rec.match_within_rec.attribute3, 'Y', attribute3, NULL),
DECODE(x_Group_rec.match_within_rec.attribute4, 'Y', attribute4, NULL),
DECODE(x_Group_rec.match_within_rec.attribute5, 'Y', attribute5, NULL),
DECODE(x_Group_rec.match_within_rec.attribute6, 'Y', attribute6, NULL),
DECODE(x_Group_rec.match_within_rec.attribute7, 'Y', attribute7, NULL),
DECODE(x_Group_rec.match_within_rec.attribute8, 'Y', attribute8, NULL),
DECODE(x_Group_rec.match_within_rec.attribute9, 'Y', attribute9, NULL),
DECODE(x_Group_rec.match_within_rec.attribute10, 'Y', attribute10,NULL),
DECODE(x_Group_rec.match_within_rec.attribute11, 'Y', attribute11,NULL),
DECODE(x_Group_rec.match_within_rec.attribute12, 'Y', attribute12,NULL),
DECODE(x_Group_rec.match_within_rec.attribute13, 'Y', attribute13,NULL),
DECODE(x_Group_rec.match_within_rec.attribute14, 'Y', attribute14,NULL),
DECODE(x_Group_rec.match_within_rec.attribute15, 'Y', attribute15,NULL)
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_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 order_header_id = x_Group_rec.order_header_id
AND item_detail_type IN (k_FIRM, k_FORECAST, k_PAST_DUE_FIRM);
PROCEDURE InsertIntransitMatchRec(x_match_rec IN WSH_RLM_INTERFACE.t_optional_match_rec,
x_Quantity IN NUMBER) IS
v_Index NUMBER;
rlm_core_sv.dpush(k_SDEBUG, 'InsertIntransitMatchRec');
rlm_core_sv.dpop(k_SDEBUG, 'InsertIntransitMatchRec');
END InsertIntransitMatchRec;
SELECT start_date_time,
primary_quantity,
-- do not use item_ref_value_1
substr(item_detail_ref_value_1,1,29) shipper_Id,
line_id
FROM rlm_schedule_lines
WHERE ship_from_org_id = x_Group_rec.ship_from_org_id
AND ship_to_org_id = x_Group_rec.ship_to_org_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND customer_item_id = x_Group_rec.customer_item_id
AND item_detail_type = k_RECT
AND qty_type_code = k_ACTUAL
AND item_detail_subtype = k_RECEIPT
ORDER BY start_date_time DESC;
SELECT start_date_time,
primary_quantity,
-- do not use item_ref_value_1
substr(item_detail_ref_value_1,1,29) shipper_Id,
line_id
FROM rlm_schedule_lines
WHERE ship_from_org_id = x_Group_rec.ship_from_org_id
AND ship_to_org_id = x_Group_rec.ship_to_org_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND customer_item_id = x_Group_rec.customer_item_id
AND item_detail_type = k_RECT
AND qty_type_code = k_ACTUAL
AND item_detail_subtype = k_SHIPMENT
ORDER BY start_date_time DESC;
SELECT start_date_time,
item_detail_subtype,
item_detail_ref_value_1
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_org_id = x_Group_rec.ship_to_org_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND customer_item_id = x_Group_rec.customer_item_id
AND item_detail_type = k_RECT
AND qty_type_code = k_ACTUAL
AND item_detail_subtype = k_RECEIPT
ORDER BY start_date_time DESC;
SELECT start_date_time,
item_detail_subtype,
item_detail_ref_value_1
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_org_id = x_Group_rec.ship_to_org_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND customer_item_id = x_Group_rec.customer_item_id
AND item_detail_type = k_RECT
AND qty_type_code = k_ACTUAL
AND item_detail_subtype = k_SHIPMENT
ORDER BY start_date_time DESC;
SELECT rih.customer_id,
ril.ship_from_org_id,
ril.ship_to_address_id,
ril.ship_to_org_id,
ril.customer_item_id,
ril.inventory_item_id,
ril.industry_attribute15,
rso.rso_hdr_id,
ril.blanket_number,
rso.effective_start_date,
ril.intrmd_ship_to_id, --Bugfix 5911991
ril.intmed_ship_to_org_id --Bugfix 5911991
FROM rlm_interface_headers rih,
rlm_interface_lines_all ril,
rlm_blanket_rso rso
WHERE rih.header_id = x_Sched_rec.header_id
AND rih.org_id = ril.org_id
AND ril.header_id = rih.header_id
AND ril.ship_from_org_id = x_Group_rec.ship_from_org_id
AND ril.process_status IN (rlm_core_sv.k_PS_AVAILABLE, rlm_core_sv.k_PS_FROZEN_FIRM)
AND ril.customer_item_id = x_Group_rec.customer_item_id
AND ril.ship_to_address_id = x_Group_rec.ship_to_address_id
AND ril.blanket_number = rso.blanket_number
AND rih.customer_id = rso.customer_id
AND item_detail_type IN (k_FIRM, k_FORECAST, k_PAST_DUE_FIRM)
AND rso.customer_item_id = DECODE(x_Group_rec.setup_terms_rec.release_rule, 'PI',
x_Group_rec.customer_item_id, K_NNULL)
GROUP BY rih.customer_id,
ril.ship_from_org_id,
ril.ship_to_address_id,
ril.ship_to_org_id,
ril.customer_item_id,
ril.inventory_item_id,
ril.industry_attribute15,
rso.rso_hdr_id,
ril.blanket_number,
rso.effective_start_date,
ril.intrmd_ship_to_id, --Bugfix 5911991
ril.intmed_ship_to_org_id --Bugfix 5911991
ORDER BY ril.ship_from_org_id,
ril.ship_to_org_id,
ril.customer_item_id,
rso.effective_start_date;
SELECT x_group_rec.customer_id,
customer_item_id,
inventory_item_id,
ship_from_org_id,
intrmd_ship_to_id intrmd_ship_to_address_id,
ship_to_address_id,
bill_to_address_id,
cust_po_number purchase_order_number,
primary_quantity,
item_detail_quantity,
start_date_time,
industry_attribute1 cust_record_year,
line_id
INTO v_cum_key_record.customer_id,
v_cum_key_record.customer_item_id,
v_cum_key_record.inventory_item_id,
v_cum_key_record.ship_from_org_id,
v_cum_key_record.intrmd_ship_to_address_id,
v_cum_key_record.ship_to_address_id,
v_cum_key_record.bill_to_address_id,
v_cum_key_record.purchase_order_number,
v_PrimaryQty,
v_CUMQty,
v_cum_key_record.cum_start_date,
v_cum_key_record.cust_record_year,
v_LineID
FROM rlm_interface_lines
WHERE header_id = x_Sched_rec.header_id
AND item_detail_type = RLM_MANAGE_DEMAND_SV.k_SHIP_RECEIPT_INFO
AND item_detail_subtype = RLM_MANAGE_DEMAND_SV.k_CUM
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 inventory_item_id = x_Group_rec.inventory_item_id
AND customer_item_id = x_Group_rec.customer_item_id
AND line_id = x_Line_id --Bugfix 7007638
ORDER BY start_date_time desc;
SELECT LOOKUP_CODE, MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = p_lookupType
AND ENABLED_FLAG = p_enabledFlag
AND SUBSTR(LOOKUP_CODE,INSTR(LOOKUP_CODE, ',') +1) = p_MatchCode;
SELECT FORM_ABOVE_PROMPT
FROM FND_DESCR_FLEX_COL_USAGE_VL
WHERE APPLICATION_ID = p_appId
AND DESCRIPTIVE_FLEXFIELD_NAME = p_descFlex
AND ENABLED_FLAG = p_enabledFlag
AND APPLICATION_COLUMN_NAME = p_ColumnName;
SELECT FORM_LEFT_PROMPT
FROM FND_DESCR_FLEX_COL_USAGE_VL
WHERE APPLICATION_ID = p_appId
AND DESCRIPTIVE_FLEXFIELD_NAME = p_descFlex
AND ENABLED_FLAG = p_enabledFlag
AND APPLICATION_COLUMN_NAME = p_ColumnName;
SELECT rso_hdr_id, effective_start_date
FROM RLM_BLANKET_RSO
WHERE blanket_number = v_BlkGroup_rec.setup_terms_rec.blanket_number
AND customer_item_id =
DECODE(v_BlkGroup_rec.setup_terms_rec.release_rule, 'PI',
v_BlkGroup_rec.customer_item_id, k_NNULL)
AND effective_start_date =
(SELECT max(rlm.effective_start_date)
FROM RLM_BLANKET_RSO rlm, OE_ORDER_HEADERS oe
WHERE rlm.blanket_number = v_BlkGroup_rec.setup_terms_rec.blanket_number
AND rlm.rso_hdr_id = oe.header_id
AND rlm.customer_item_id =
DECODE(v_BlkGroup_rec.setup_terms_rec.release_rule, 'PI',
v_BlkGroup_rec.customer_item_id, K_NNULL)
AND oe.open_flag = 'Y')
ORDER BY rso_hdr_id DESC;
SELECT x_group_rec.customer_id,
customer_item_id,
inventory_item_id,
ship_from_org_id,
intrmd_ship_to_id intrmd_ship_to_address_id,
ship_to_address_id,
bill_to_address_id,
cust_po_number purchase_order_number,
primary_quantity,
item_detail_quantity,
start_date_time,
industry_attribute1 cust_record_year,
line_id
FROM rlm_interface_lines
WHERE header_id = x_Sched_rec.header_id
AND item_detail_type = RLM_MANAGE_DEMAND_SV.k_SHIP_RECEIPT_INFO
AND item_detail_subtype = RLM_MANAGE_DEMAND_SV.k_CUM
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 inventory_item_id = x_Group_rec.inventory_item_id
AND customer_item_id = x_Group_rec.customer_item_id
ORDER BY start_date_time desc;
g_RecCUM_tab.DELETE;