[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
RLM_RD_SV.g_SourceTab.DELETE;
v_SourcedDemand_Tab.Delete;
rlm_core_sv.dlog(C_DEBUG, 'Blanket Version of Update', x_Group_rec.blanket_number);
UPDATE rlm_schedule_lines_all sl
SET process_status = rlm_core_sv.k_PS_Processed
WHERE header_id = x_Sched_rec.schedule_header_id
AND line_id IN (
SELECT schedule_line_id
FROM rlm_interface_lines il
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_address_id = x_Group_rec.ship_to_address_id
AND il.customer_item_id = x_Group_rec.customer_item_id
AND il.blanket_number = x_Group_rec.blanket_number
AND il.process_status = rlm_core_sv.k_PS_Processed
);
rlm_core_sv.dlog(C_DEBUG, 'Sales order version of Update', x_Group_rec.order_header_id);
UPDATE rlm_schedule_lines_all sl
SET process_status = rlm_core_sv.k_PS_Processed
WHERE header_id = x_Sched_rec.schedule_header_id
AND line_id IN (
SELECT schedule_line_id
FROM rlm_interface_lines il
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_address_id = x_Group_rec.ship_to_address_id
AND il.customer_item_id = x_Group_rec.customer_item_id
AND il.order_header_id = x_Group_rec.order_header_id
AND il.process_status = rlm_core_sv.k_PS_Processed
);
rlm_core_sv.dlog(C_DEBUG,'# of schedule lines updated', SQL%ROWCOUNT);
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,
k_TRUE
FROM rlm_interface_lines
WHERE header_id = x_Sched_rec.header_id
AND item_detail_type = k_SHIP_RECEIPT_INFO
AND item_detail_subtype IN (k_SHIPMENT, k_RECEIPT)
AND ship_to_address_id = x_Group_rec.ship_to_address_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND qty_type_code = k_ACTUAL
ORDER BY SCHEDULE_DATE 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,
k_TRUE
FROM rlm_interface_lines
WHERE header_id = x_Sched_rec.header_id
AND item_detail_type = k_SHIP_RECEIPT_INFO
AND item_detail_subtype = 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_CUM_tab.DELETE;
SELECT *
FROM rlm_interface_lines_all
WHERE header_id = x_Sched_rec.header_id
AND ship_from_org_id = x_Group_rec.ship_from_org_id
AND ship_to_address_id = x_Group_rec.ship_to_address_id
AND customer_item_id = x_Group_rec.customer_item_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND item_detail_type IN (k_PAST_DUE_FIRM, k_FIRM_DEMAND,
k_FORECAST_DEMAND, k_MRP_FORECAST,k_FIRM_DEMAND)
AND process_status = rlm_core_sv.k_PS_AVAILABLE
ORDER BY START_DATE_TIME;
SELECT *
FROM rlm_interface_lines_all
WHERE header_id = x_Sched_rec.header_id
AND ship_from_org_id = x_Group_rec.ship_from_org_id
AND ship_to_address_id = x_Group_rec.ship_to_address_id
AND customer_item_id = x_Group_rec.customer_item_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND item_detail_type IN (k_PAST_DUE_FIRM, k_FIRM_DEMAND,
k_FORECAST_DEMAND, k_MRP_FORECAST,k_SHIP_RECEIPT_INFO)
AND process_status = rlm_core_sv.k_PS_AVAILABLE
ORDER BY START_DATE_TIME;
g_ManageDemand_tab.DELETE;
rlm_core_sv.dlog(C_DEBUG,'v_cum_record.last_cum_qty_update_date',
v_cum_record.last_cum_qty_update_date);
rlm_core_sv.dlog(C_DEBUG,'v_cum_record.last_update_date',
v_cum_record.last_update_date);
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
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
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
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
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;
IF x_Operation = k_DELETE THEN
--
x_ManageDemand_rec.program_id := x_Operation;
IF nvl(x_ManageDemand_rec.program_id,k_NULL) = k_INSERT THEN
x_ManageDemand_rec.program_id := NULL;
ELSIF x_Operation = k_UPDATE THEN
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(C_DEBUG,'x_Operation',x_Operation);
IF nvl(x_ManageDemand_rec.program_id,k_NULL) NOT IN (k_INSERT, k_DELETE)
THEN
x_ManageDemand_rec.program_id := x_Operation;
ELSIF x_Operation = k_INSERT THEN
SELECT rlm_interface_lines_s.nextval
INTO x_ManageDemand_rec.line_id
FROM DUAL;
SetOperation(g_ManageDemand_tab(v_Count), k_UPDATE);
SetOperation(g_ManageDemand_tab(v_Count), k_UPDATE);
SELECT DECODE(mislv.source_type,2,mislv.source_organization_id,
mislv.source_organization_id) org_id, mislv.allocation_percent ,mislv.effective_date, --Bugfix 6051397
NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) --Bugfix 6051397
FROM msc_scatp_item_sr_levels_v mislv
WHERE mislv.assignment_set_id = x_assign_id
AND mislv.sourcing_level = x_srcng_lvl
AND mislv.inventory_item_id = x_inv_item_id
AND mislv.source_type IN (1,2)
AND (SYSDATE BETWEEN mislv.effective_date AND
NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) OR SYSDATE < mislv.effective_date) --Bugfix 6051397
AND mislv.rank = x_rank
AND mislv.ship_to_site_id = x_ship_to_site_id
AND exists (SELECT null
FROM msc_sourcing_rules
WHERE sourcing_rule_id = mislv.sourcing_rule_id
AND planning_active = k_PLANNING_ACTIVE)
ORDER BY mislv.allocation_percent;
SELECT DECODE(mislv.source_type,2,mislv.source_organization_id,
mislv.source_organization_id) org_id, mislv.allocation_percent ,mislv.effective_date, --Bugfix 6051397
NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) --Bugfix 6051397
FROM mrp_scatp_item_sr_levels_v mislv
WHERE mislv.assignment_set_id = x_assign_id
AND mislv.sourcing_level = x_srcng_lvl
AND mislv.inventory_item_id = x_inv_item_id
AND mislv.source_type IN (1,2)
AND (SYSDATE BETWEEN mislv.effective_date AND
NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) OR SYSDATE < mislv.effective_date) --Bugfix 6051397
AND mislv.rank = x_rank
AND mislv.ship_to_site_id = x_ship_to_site_id
AND exists (SELECT null
FROM mrp_sourcing_rules
WHERE sourcing_rule_id = mislv.sourcing_rule_id
AND planning_active = k_PLANNING_ACTIVE)
ORDER BY mislv.allocation_percent;
select DISTINCT assignment_set_id
FROM msc_scatp_item_sr_levels_v mislv
WHERE mislv.inventory_item_id = sr_item_id
AND mislv.source_type IN (1,2)
AND (SYSDATE BETWEEN mislv.effective_date
AND NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) OR SYSDATE < mislv.effective_date) --Bugfix 6051397
AND mislv.ship_to_site_id = x_Group_rec.ship_to_site_use_id
AND exists (SELECT null
FROM msc_sourcing_rules
WHERE sourcing_rule_id = mislv.sourcing_rule_id
AND planning_active = k_PLANNING_ACTIVE);
SELECT DISTINCT assignment_set_id
FROM mrp_scatp_item_sr_levels_v mislv
WHERE mislv.inventory_item_id = x_Group_rec.inventory_item_id
AND mislv.source_type IN (1,2)
AND (SYSDATE BETWEEN mislv.effective_date
AND NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) OR SYSDATE < mislv.effective_date) --Bugfix 6051397
AND mislv.ship_to_site_id = x_Group_rec.ship_to_site_use_id
AND exists (SELECT null
FROM mrp_sourcing_rules
WHERE sourcing_rule_id = mislv.sourcing_rule_id
AND planning_active = k_PLANNING_ACTIVE);
SELECT DECODE(mislv.source_type,2,mislv.organization_id,
mislv.source_organization_id) org_id, mislv.allocation_percent, mislv.effective_date, --Bugfix 6051397
NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) --Bugfix 6051397
FROM msc_item_sourcing_levels_v mislv
WHERE mislv.assignment_set_id = x_assign_id
AND mislv.sourcing_level = x_srcng_lvl
AND mislv.inventory_item_id = x_inv_item_id
AND mislv.organization_id = x_org_id
AND mislv.source_type IN (1,2)
AND (SYSDATE BETWEEN mislv.effective_date AND
NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) OR SYSDATE < mislv.effective_date) --Bugfix 6051397
AND mislv.rank = x_rank
AND exists (SELECT null
FROM msc_sourcing_rules
WHERE sourcing_rule_id = mislv.sourcing_rule_id
AND planning_active = k_PLANNING_ACTIVE)
ORDER BY mislv.allocation_percent;
SELECT DECODE(mislv.source_type,2,mislv.organization_id,
mislv.source_organization_id) org_id, mislv.allocation_percent, mislv.effective_date, --Bugfix 6051397
NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) --Bugfix 6051397
FROM mrp_item_sourcing_levels_v mislv
WHERE mislv.assignment_set_id = x_assign_id
AND mislv.sourcing_level = x_srcng_lvl
AND mislv.inventory_item_id = x_inv_item_id
AND mislv.organization_id = x_org_id
AND mislv.source_type IN (1,2)
AND (SYSDATE BETWEEN mislv.effective_date AND
NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) OR SYSDATE < mislv.effective_date) --Bugfix 6051397
AND mislv.rank = x_rank
AND exists (SELECT null
FROM mrp_sourcing_rules
WHERE sourcing_rule_id = mislv.sourcing_rule_id
AND planning_active = k_PLANNING_ACTIVE)
ORDER BY mislv.allocation_percent;
select DISTINCT assignment_set_id
FROM msc_item_sourcing_levels_v mislv
WHERE mislv.inventory_item_id = sr_item_id
AND mislv.organization_id = x_Group_rec.ship_from_org_id
AND mislv.source_type IN (1,2)
AND (SYSDATE BETWEEN mislv.effective_date
AND NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) OR SYSDATE < mislv.effective_date) --Bugfix 6051397
AND exists (SELECT null
FROM msc_sourcing_rules
WHERE sourcing_rule_id = mislv.sourcing_rule_id
AND planning_active = k_PLANNING_ACTIVE);
SELECT DISTINCT assignment_set_id
FROM mrp_item_sourcing_levels_v mislv
WHERE mislv.inventory_item_id = x_Group_rec.inventory_item_id
AND mislv.organization_id = x_Group_rec.ship_from_org_id
AND mislv.source_type IN (1,2)
AND (SYSDATE BETWEEN mislv.effective_date
AND NVL(mislv.disable_date, TO_DATE('31/12/4712','dd/mm/yyyy')) OR SYSDATE < mislv.effective_date) --Bugfix 6051397
AND exists (SELECT null
FROM mrp_sourcing_rules
WHERE sourcing_rule_id = mislv.sourcing_rule_id
AND planning_active = k_PLANNING_ACTIVE);
SELECT inventory_item_id
INTO sr_item_id
FROM msc_system_items
WHERE sr_inventory_item_id = x_Group_rec.inventory_item_id
AND plan_id = -1
AND organization_id = x_Group_rec.ship_from_org_id
AND sr_instance_id IN (SELECT instance_id FROM mrp_ap_apps_instances);
SELECT MIN(sourcing_level)
INTO v_srcng_lvl
FROM msc_scatp_item_sr_levels_v
WHERE inventory_item_id = sr_item_id
AND assignment_set_id = v_assign_set_id
AND ship_to_site_id = x_Group_rec.ship_to_site_use_id;
SELECT MIN(rank)
INTO v_rank
FROM msc_scatp_item_sr_levels_v
WHERE inventory_item_id = sr_item_id
AND ship_to_site_id = x_Group_rec.ship_to_site_use_id
AND (SYSDATE BETWEEN effective_date AND
NVL(disable_date, TO_DATE('31/12/4712', 'DD/MM/YYYY')) OR SYSDATE < effective_date); --Bugfix 6051397
SELECT MIN(sourcing_level)
INTO v_srcng_lvl
FROM msc_item_sourcing_levels_v
WHERE organization_id = x_Group_rec.ship_from_org_id
AND inventory_item_id = sr_item_id
AND assignment_set_id = v_assign_set_id;
SELECT MIN(rank)
INTO v_rank
FROM msc_item_sourcing_levels_v
WHERE organization_id = x_Group_rec.ship_from_org_Id
AND inventory_item_id = sr_item_id
AND (SYSDATE BETWEEN effective_date AND
NVL(disable_date, TO_DATE('31/12/4712', 'DD/MM/YYYY')) OR SYSDATE < effective_date); --Bugfix 6051397
SELECT MIN(sourcing_level)
INTO v_srcng_lvl
FROM mrp_scatp_item_sr_levels_v
WHERE inventory_item_id = x_Group_rec.inventory_item_id
AND assignment_set_id = v_assign_set_id
AND ship_to_site_id = x_Group_rec.ship_to_site_use_id;
SELECT MIN(rank)
INTO v_rank
FROM mrp_scatp_item_sr_levels_v
WHERE inventory_item_id = x_Group_rec.inventory_item_id
AND ship_to_site_id = x_Group_rec.ship_to_site_use_id
AND (SYSDATE BETWEEN effective_date AND
NVL(disable_date, TO_DATE('31/12/4712', 'DD/MM/YYYY')) OR SYSDATE < effective_date); --Bugfix 6051397
SELECT MIN(sourcing_level)
INTO v_srcng_lvl
FROM mrp_item_sourcing_levels_v
WHERE organization_id = x_Group_rec.ship_from_org_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND assignment_set_id = v_assign_set_id;
SELECT MIN(rank)
INTO v_rank
FROM mrp_item_sourcing_levels_v
WHERE organization_id = x_Group_rec.ship_from_org_Id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND (SYSDATE BETWEEN effective_date AND
NVL(disable_date, TO_DATE('31/12/4712', 'DD/MM/YYYY')) OR SYSDATE < effective_date); --Bugfix 6051397
SetOperation(g_ManageDemand_tab(i), k_DELETE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(x_SourcedDemand_Tab(x_SourcedDemand_Tab.COUNT),k_INSERT);
SetOperation(g_ManageDemand_tab(i),k_DELETE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
rlm_core_sv.dlog(C_DEBUG,'Set operation to update');
SetOperation(g_ManageDemand_tab(g_ManageDemand_tab.COUNT), k_INSERT);
rlm_core_sv.dlog(C_DEBUG,'Set operation to insert');
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
IF x_Demand_tab(i).program_id = k_DELETE THEN
DeleteReq(x_Demand_tab(i));
ELSIF x_Demand_tab(i).program_id = k_UPDATE THEN
UpdateReq(x_Demand_tab(i));
ELSIF x_Demand_tab(i).program_id = k_INSERT THEN
InsertReq(x_Demand_tab(i));
PROCEDURE NAME: DeleteReq
===========================================================================*/
PROCEDURE DeleteReq(x_ManageDemand_rec IN rlm_interface_lines%ROWTYPE)
IS
i NUMBER;
rlm_core_sv.dpush(C_SDEBUG,'DeleteReq');
DELETE rlm_interface_lines
WHERE line_id = x_ManageDemand_rec.line_id;
rlm_message_sv.sql_error('rlm_manage_demand_sv.DeleteReq',
v_Progress);
END DeleteReq;
PROCEDURE NAME: InsertReq
===========================================================================*/
PROCEDURE InsertReq(x_ManageDemand_rec IN rlm_interface_lines%ROWTYPE)
IS
--
v_Progress VARCHAR2(3) := '010';
rlm_core_sv.dpush(C_SDEBUG,'InsertReq');
rlm_core_sv.dlog(C_DEBUG,'Changed lines to be inserted into table');
INSERT INTO rlm_interface_lines_all(
line_id,
header_id,
schedule_item_num,
agreement_id,
ato_data_type,
bill_to_address_1_ext,
bill_to_address_2_ext,
bill_to_address_3_ext,
bill_to_address_4_ext,
bill_to_address_id,
bill_to_city_ext,
bill_to_country_ext,
bill_to_county_ext,
bill_to_name_ext,
bill_to_postal_cd_ext,
bill_to_province_ext,
bill_to_site_use_id,
invoice_to_org_id,
bill_to_state_ext,
carrier_id_code_ext,
carrier_qualifier_ext,
commodity_ext,
country_of_origin_ext,
cust_assembly_ext,
cust_assigned_id_ext,
cust_bill_to_ext,
cust_contract_num_ext,
customer_dock_code,
cust_intrmd_ship_to_ext,
cust_item_price_ext,
cust_item_price_uom_ext,
customer_item_revision,
customer_job,
cust_manufacturer_ext,
cust_model_number_ext,
cust_model_serial_number,
cust_order_num_ext,
cust_process_num_ext,
cust_production_line,
-- cust_record_year, -- Removed from table
cust_set_num_ext,
cust_ship_from_org_ext,
cust_ship_to_ext,
cust_uom_ext,
customer_item_ext,
customer_item_id,
REQUEST_DATE,
SCHEDULE_DATE,
date_type_code,
date_type_code_ext,
delivery_lead_time,
end_date_time,
equipment_code_ext,
equipment_number_ext,
handling_code_ext,
hazard_code_ext,
hazard_code_qual_ext,
hazard_description_ext,
import_license_date_ext,
import_license_ext,
industry_attribute1,
industry_attribute10,
industry_attribute11,
industry_attribute12,
industry_attribute13,
industry_attribute14,
industry_attribute15,
industry_attribute2,
industry_attribute3,
industry_attribute4,
industry_attribute5,
industry_attribute6,
industry_attribute7,
industry_attribute8,
industry_attribute9,
industry_context,
intrmd_ship_to_id,
intrmd_st_address_1_ext,
intrmd_st_address_2_ext,
intrmd_st_address_3_ext,
intrmd_st_address_4_ext,
intrmd_st_city_ext,
intrmd_st_country_ext,
intrmd_st_county_ext,
intrmd_st_name_ext,
intrmd_st_postal_cd_ext,
intrmd_st_province_ext,
intrmd_st_state_ext,
intmed_ship_to_org_id,
inventory_item_id,
inventory_item_segment1,
inventory_item_segment10,
inventory_item_segment11,
inventory_item_segment12,
inventory_item_segment13,
inventory_item_segment14,
inventory_item_segment15,
inventory_item_segment16,
inventory_item_segment17,
inventory_item_segment18,
inventory_item_segment19,
inventory_item_segment2,
inventory_item_segment20,
inventory_item_segment3,
inventory_item_segment4,
inventory_item_segment5,
inventory_item_segment6,
inventory_item_segment7,
inventory_item_segment8,
inventory_item_segment9,
item_contact_code_1,
item_contact_code_2,
item_contact_value_1,
item_contact_value_2,
item_description_ext,
item_detail_quantity,
item_detail_ref_code_1,
item_detail_ref_code_2,
item_detail_ref_code_3,
item_detail_ref_value_1,
item_detail_ref_value_2,
item_detail_ref_value_3,
item_detail_subtype,
item_detail_subtype_ext,
item_detail_type,
item_detail_type_ext,
item_eng_cng_lvl_ext,
item_measurements_ext,
item_note_text,
item_ref_code_1,
item_ref_code_2,
item_ref_code_3,
item_ref_value_1,
item_ref_value_2,
item_ref_value_3,
item_release_status_ext,
lading_quantity_ext,
letter_credit_expdt_ext,
letter_credit_ext,
line_reference,
link_to_line_ref,
order_header_id,
other_name_code_1,
other_name_code_2,
other_name_value_1,
other_name_value_2,
pack_size_ext,
pack_units_per_pack_ext,
pack_uom_code_ext,
packaging_code_ext,
parent_link_line_ref,
cust_production_seq_num,
price_list_id,
primary_quantity,
primary_uom_code,
prime_contrctr_part_ext,
process_status,
cust_po_release_num,
cust_po_date,
cust_po_line_num,
cust_po_number,
qty_type_code,
qty_type_code_ext,
return_container_ext,
schedule_line_id,
routing_desc_ext,
routing_seq_code_ext,
ship_del_pattern_ext,
ship_del_time_code_ext,
ship_del_rule_name,
ship_from_address_1_ext,
ship_from_address_2_ext,
ship_from_address_3_ext,
ship_from_address_4_ext,
ship_from_city_ext,
ship_from_country_ext,
ship_from_county_ext,
ship_from_name_ext,
ship_from_org_id,
ship_from_postal_cd_ext,
ship_from_province_ext,
ship_from_state_ext,
ship_label_info_line_1,
ship_label_info_line_10,
ship_label_info_line_2,
ship_label_info_line_3,
ship_label_info_line_4,
ship_label_info_line_5,
ship_label_info_line_6,
ship_label_info_line_7,
ship_label_info_line_8,
ship_label_info_line_9,
ship_to_address_1_ext,
ship_to_address_2_ext,
ship_to_address_3_ext,
ship_to_address_4_ext,
ship_to_address_id,
ship_to_city_ext,
ship_to_country_ext,
ship_to_county_ext,
ship_to_name_ext,
ship_to_postal_cd_ext,
ship_to_province_ext,
ship_to_site_use_id,
deliver_to_org_id,
ship_to_org_id,
ship_to_state_ext,
start_date_time,
subline_assigned_id_ext,
subline_config_code_ext,
subline_cust_item_ext,
subline_cust_item_id,
subline_model_num_ext,
subline_quantity,
subline_uom_code,
supplier_item_ext,
transit_time_ext,
transit_time_qual_ext,
transport_loc_qual_ext,
transport_location_ext,
transport_method_ext,
uom_code,
weight_ext,
weight_qualifier_ext,
weight_uom_ext,
last_update_date,
last_updated_by,
creation_date,
created_by,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
last_update_login,
request_id,
program_application_id,
program_id,
line_source,
program_update_date,
tp_attribute1, --bug 2056845
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,
blanket_number,
dsp_child_process_index,
org_id,
ship_to_customer_id
)
VALUES(
x_ManageDemand_rec.line_id,
x_ManageDemand_rec.header_id,
x_ManageDemand_rec.schedule_item_num,
x_ManageDemand_rec.agreement_id,
x_ManageDemand_rec.ato_data_type,
x_ManageDemand_rec.bill_to_address_1_ext,
x_ManageDemand_rec.bill_to_address_2_ext,
x_ManageDemand_rec.bill_to_address_3_ext,
x_ManageDemand_rec.bill_to_address_4_ext,
x_ManageDemand_rec.bill_to_address_id,
x_ManageDemand_rec.bill_to_city_ext,
x_ManageDemand_rec.bill_to_country_ext,
x_ManageDemand_rec.bill_to_county_ext,
x_ManageDemand_rec.bill_to_name_ext,
x_ManageDemand_rec.bill_to_postal_cd_ext,
x_ManageDemand_rec.bill_to_province_ext,
x_ManageDemand_rec.bill_to_site_use_id,
x_ManageDemand_rec.invoice_to_org_id,
x_ManageDemand_rec.bill_to_state_ext,
x_ManageDemand_rec.carrier_id_code_ext,
x_ManageDemand_rec.carrier_qualifier_ext,
x_ManageDemand_rec.commodity_ext,
x_ManageDemand_rec.country_of_origin_ext,
x_ManageDemand_rec.cust_assembly_ext,
x_ManageDemand_rec.cust_assigned_id_ext,
x_ManageDemand_rec.cust_bill_to_ext,
x_ManageDemand_rec.cust_contract_num_ext,
x_ManageDemand_rec.customer_dock_code,
x_ManageDemand_rec.cust_intrmd_ship_to_ext,
x_ManageDemand_rec.cust_item_price_ext,
x_ManageDemand_rec.cust_item_price_uom_ext,
x_ManageDemand_rec.customer_item_revision,
x_ManageDemand_rec.customer_job,
x_ManageDemand_rec.cust_manufacturer_ext,
x_ManageDemand_rec.cust_model_number_ext,
x_ManageDemand_rec.cust_model_serial_number,
x_ManageDemand_rec.cust_order_num_ext,
x_ManageDemand_rec.cust_process_num_ext,
x_ManageDemand_rec.cust_production_line,
x_ManageDemand_rec.cust_set_num_ext,
x_ManageDemand_rec.cust_ship_from_org_ext,
x_ManageDemand_rec.cust_ship_to_ext,
x_ManageDemand_rec.cust_uom_ext,
x_ManageDemand_rec.customer_item_ext,
x_ManageDemand_rec.customer_item_id,
x_ManageDemand_rec.REQUEST_DATE,
x_ManageDemand_rec.SCHEDULE_DATE,
x_ManageDemand_rec.date_type_code,
x_ManageDemand_rec.date_type_code_ext,
x_ManageDemand_rec.delivery_lead_time,
x_ManageDemand_rec.end_date_time,
x_ManageDemand_rec.equipment_code_ext,
x_ManageDemand_rec.equipment_number_ext,
x_ManageDemand_rec.handling_code_ext,
x_ManageDemand_rec.hazard_code_ext,
x_ManageDemand_rec.hazard_code_qual_ext,
x_ManageDemand_rec.hazard_description_ext,
x_ManageDemand_rec.import_license_date_ext,
x_ManageDemand_rec.import_license_ext,
x_ManageDemand_rec.industry_attribute1,
x_ManageDemand_rec.industry_attribute10,
x_ManageDemand_rec.industry_attribute11,
x_ManageDemand_rec.industry_attribute12,
x_ManageDemand_rec.industry_attribute13,
x_ManageDemand_rec.industry_attribute14,
x_ManageDemand_rec.industry_attribute15,
x_ManageDemand_rec.industry_attribute2,
x_ManageDemand_rec.industry_attribute3,
x_ManageDemand_rec.industry_attribute4,
x_ManageDemand_rec.industry_attribute5,
x_ManageDemand_rec.industry_attribute6,
x_ManageDemand_rec.industry_attribute7,
x_ManageDemand_rec.industry_attribute8,
x_ManageDemand_rec.industry_attribute9,
x_ManageDemand_rec.industry_context,
x_ManageDemand_rec.intrmd_ship_to_id,
x_ManageDemand_rec.intrmd_st_address_1_ext,
x_ManageDemand_rec.intrmd_st_address_2_ext,
x_ManageDemand_rec.intrmd_st_address_3_ext,
x_ManageDemand_rec.intrmd_st_address_4_ext,
x_ManageDemand_rec.intrmd_st_city_ext,
x_ManageDemand_rec.intrmd_st_country_ext,
x_ManageDemand_rec.intrmd_st_county_ext,
x_ManageDemand_rec.intrmd_st_name_ext,
x_ManageDemand_rec.intrmd_st_postal_cd_ext,
x_ManageDemand_rec.intrmd_st_province_ext,
x_ManageDemand_rec.intrmd_st_state_ext,
x_ManageDemand_rec.intmed_ship_to_org_id,
x_ManageDemand_rec.inventory_item_id,
x_ManageDemand_rec.inventory_item_segment1,
x_ManageDemand_rec.inventory_item_segment10,
x_ManageDemand_rec.inventory_item_segment11,
x_ManageDemand_rec.inventory_item_segment12,
x_ManageDemand_rec.inventory_item_segment13,
x_ManageDemand_rec.inventory_item_segment14,
x_ManageDemand_rec.inventory_item_segment15,
x_ManageDemand_rec.inventory_item_segment16,
x_ManageDemand_rec.inventory_item_segment17,
x_ManageDemand_rec.inventory_item_segment18,
x_ManageDemand_rec.inventory_item_segment19,
x_ManageDemand_rec.inventory_item_segment2,
x_ManageDemand_rec.inventory_item_segment20,
x_ManageDemand_rec.inventory_item_segment3,
x_ManageDemand_rec.inventory_item_segment4,
x_ManageDemand_rec.inventory_item_segment5,
x_ManageDemand_rec.inventory_item_segment6,
x_ManageDemand_rec.inventory_item_segment7,
x_ManageDemand_rec.inventory_item_segment8,
x_ManageDemand_rec.inventory_item_segment9,
x_ManageDemand_rec.item_contact_code_1,
x_ManageDemand_rec.item_contact_code_2,
x_ManageDemand_rec.item_contact_value_1,
x_ManageDemand_rec.item_contact_value_2,
x_ManageDemand_rec.item_description_ext,
x_ManageDemand_rec.item_detail_quantity,
x_ManageDemand_rec.item_detail_ref_code_1,
x_ManageDemand_rec.item_detail_ref_code_2,
x_ManageDemand_rec.item_detail_ref_code_3,
x_ManageDemand_rec.item_detail_ref_value_1,
x_ManageDemand_rec.item_detail_ref_value_2,
x_ManageDemand_rec.item_detail_ref_value_3,
x_ManageDemand_rec.item_detail_subtype,
x_ManageDemand_rec.item_detail_subtype_ext,
x_ManageDemand_rec.item_detail_type,
x_ManageDemand_rec.item_detail_type_ext,
x_ManageDemand_rec.item_eng_cng_lvl_ext,
x_ManageDemand_rec.item_measurements_ext,
x_ManageDemand_rec.item_note_text,
x_ManageDemand_rec.item_ref_code_1,
x_ManageDemand_rec.item_ref_code_2,
x_ManageDemand_rec.item_ref_code_3,
x_ManageDemand_rec.item_ref_value_1,
x_ManageDemand_rec.item_ref_value_2,
x_ManageDemand_rec.item_ref_value_3,
x_ManageDemand_rec.item_release_status_ext,
x_ManageDemand_rec.lading_quantity_ext,
x_ManageDemand_rec.letter_credit_expdt_ext,
x_ManageDemand_rec.letter_credit_ext,
x_ManageDemand_rec.line_reference,
x_ManageDemand_rec.link_to_line_ref,
x_ManageDemand_rec.order_header_id,
x_ManageDemand_rec.other_name_code_1,
x_ManageDemand_rec.other_name_code_2,
x_ManageDemand_rec.other_name_value_1,
x_ManageDemand_rec.other_name_value_2,
x_ManageDemand_rec.pack_size_ext,
x_ManageDemand_rec.pack_units_per_pack_ext,
x_ManageDemand_rec.pack_uom_code_ext,
x_ManageDemand_rec.packaging_code_ext,
x_ManageDemand_rec.parent_link_line_ref,
x_ManageDemand_rec.cust_production_seq_num,
x_ManageDemand_rec.price_list_id,
x_ManageDemand_rec.primary_quantity,
x_ManageDemand_rec.primary_uom_code,
x_ManageDemand_rec.prime_contrctr_part_ext,
x_ManageDemand_rec.process_status,
x_ManageDemand_rec.cust_po_release_num,
x_ManageDemand_rec.cust_po_date,
x_ManageDemand_rec.cust_po_line_num,
x_ManageDemand_rec.cust_po_number,
x_ManageDemand_rec.qty_type_code,
x_ManageDemand_rec.qty_type_code_ext,
x_ManageDemand_rec.return_container_ext,
x_ManageDemand_rec.schedule_line_id,
x_ManageDemand_rec.routing_desc_ext,
x_ManageDemand_rec.routing_seq_code_ext,
x_ManageDemand_rec.ship_del_pattern_ext,
x_ManageDemand_rec.ship_del_time_code_ext,
x_ManageDemand_rec.ship_del_rule_name,
x_ManageDemand_rec.ship_from_address_1_ext,
x_ManageDemand_rec.ship_from_address_2_ext,
x_ManageDemand_rec.ship_from_address_3_ext,
x_ManageDemand_rec.ship_from_address_4_ext,
x_ManageDemand_rec.ship_from_city_ext,
x_ManageDemand_rec.ship_from_country_ext,
x_ManageDemand_rec.ship_from_county_ext,
x_ManageDemand_rec.ship_from_name_ext,
x_ManageDemand_rec.ship_from_org_id,
x_ManageDemand_rec.ship_from_postal_cd_ext,
x_ManageDemand_rec.ship_from_province_ext,
x_ManageDemand_rec.ship_from_state_ext,
x_ManageDemand_rec.ship_label_info_line_1,
x_ManageDemand_rec.ship_label_info_line_10,
x_ManageDemand_rec.ship_label_info_line_2,
x_ManageDemand_rec.ship_label_info_line_3,
x_ManageDemand_rec.ship_label_info_line_4,
x_ManageDemand_rec.ship_label_info_line_5,
x_ManageDemand_rec.ship_label_info_line_6,
x_ManageDemand_rec.ship_label_info_line_7,
x_ManageDemand_rec.ship_label_info_line_8,
x_ManageDemand_rec.ship_label_info_line_9,
x_ManageDemand_rec.ship_to_address_1_ext,
x_ManageDemand_rec.ship_to_address_2_ext,
x_ManageDemand_rec.ship_to_address_3_ext,
x_ManageDemand_rec.ship_to_address_4_ext,
x_ManageDemand_rec.ship_to_address_id,
x_ManageDemand_rec.ship_to_city_ext,
x_ManageDemand_rec.ship_to_country_ext,
x_ManageDemand_rec.ship_to_county_ext,
x_ManageDemand_rec.ship_to_name_ext,
x_ManageDemand_rec.ship_to_postal_cd_ext,
x_ManageDemand_rec.ship_to_province_ext,
x_ManageDemand_rec.ship_to_site_use_id,
x_ManageDemand_rec.deliver_to_org_id,
x_ManageDemand_rec.ship_to_org_id,
x_ManageDemand_rec.ship_to_state_ext,
x_ManageDemand_rec.start_date_time,
x_ManageDemand_rec.subline_assigned_id_ext,
x_ManageDemand_rec.subline_config_code_ext,
x_ManageDemand_rec.subline_cust_item_ext,
x_ManageDemand_rec.subline_cust_item_id,
x_ManageDemand_rec.subline_model_num_ext,
x_ManageDemand_rec.subline_quantity,
x_ManageDemand_rec.subline_uom_code,
x_ManageDemand_rec.supplier_item_ext,
x_ManageDemand_rec.transit_time_ext,
x_ManageDemand_rec.transit_time_qual_ext,
x_ManageDemand_rec.transport_loc_qual_ext,
x_ManageDemand_rec.transport_location_ext,
x_ManageDemand_rec.transport_method_ext,
x_ManageDemand_rec.uom_code,
x_ManageDemand_rec.weight_ext,
x_ManageDemand_rec.weight_qualifier_ext,
x_ManageDemand_rec.weight_uom_ext,
x_ManageDemand_rec.last_update_date,
x_ManageDemand_rec.last_updated_by,
sysdate, /* creation_date */
x_ManageDemand_rec.created_by, /* created_by */
x_ManageDemand_rec.attribute_category,
x_ManageDemand_rec.attribute1,
x_ManageDemand_rec.attribute2,
x_ManageDemand_rec.attribute3,
x_ManageDemand_rec.attribute4,
x_ManageDemand_rec.attribute5,
x_ManageDemand_rec.attribute6,
x_ManageDemand_rec.attribute7,
x_ManageDemand_rec.attribute8,
x_ManageDemand_rec.attribute9,
x_ManageDemand_rec.attribute10,
x_ManageDemand_rec.attribute11,
x_ManageDemand_rec.attribute12,
x_ManageDemand_rec.attribute13,
x_ManageDemand_rec.attribute14,
x_ManageDemand_rec.attribute15,
x_ManageDemand_rec.last_update_login,
x_ManageDemand_rec.request_id,
x_ManageDemand_rec.program_application_id,
x_ManageDemand_rec.program_id,
x_ManageDemand_rec.line_source,
sysdate, /* program_update_date */
x_ManageDemand_rec. tp_attribute1, --bug 2056845
x_ManageDemand_rec.tp_attribute2,
x_ManageDemand_rec.tp_attribute3,
x_ManageDemand_rec.tp_attribute4,
x_ManageDemand_rec.tp_attribute5,
x_ManageDemand_rec.tp_attribute6,
x_ManageDemand_rec.tp_attribute7,
x_ManageDemand_rec.tp_attribute8,
x_ManageDemand_rec.tp_attribute9,
x_ManageDemand_rec.tp_attribute10,
x_ManageDemand_rec.tp_attribute11,
x_ManageDemand_rec.tp_attribute12,
x_ManageDemand_rec.tp_attribute13,
x_ManageDemand_rec.tp_attribute14,
x_ManageDemand_rec.tp_attribute15,
x_ManageDemand_rec.tp_attribute_category,
x_ManageDemand_rec.blanket_number,
x_ManageDemand_rec.dsp_child_process_index,
x_ManageDemand_rec.org_id,
x_ManageDemand_rec.ship_to_customer_id
);
UPDATE rlm_schedule_lines_all
SET interface_line_id = x_ManageDemand_rec.line_id
WHERE line_id = x_ManageDemand_rec.schedule_line_id
AND x_ManageDemand_rec.line_source = 'SOURCED';
rlm_core_sv.dlog(C_DEBUG,'# of schedule lines updated', SQL%ROWCOUNT);
rlm_core_sv.dlog(C_DEBUG, 'Null Org ID detected during Insert');
rlm_message_sv.sql_error('rlm_manage_demand_sv.InsertReq',
v_Progress);
END InsertReq;
PROCEDURE NAME: UpdateReq
===========================================================================*/
PROCEDURE UpdateReq(x_ManageDemand_rec IN rlm_interface_lines%ROWTYPE)
IS
v_Progress VARCHAR2(3) := '010';
rlm_core_sv.dpush(C_SDEBUG,'UpdateReq');
rlm_core_sv.dlog(C_DEBUG,'Changed lines to be updated to table');
UPDATE rlm_interface_lines_all
SET primary_quantity = x_ManageDemand_rec.primary_quantity,
ship_from_org_id = x_ManageDemand_rec.ship_from_org_id,
item_detail_type = x_ManageDemand_rec.item_detail_type,
item_detail_subtype = x_ManageDemand_rec.item_detail_subtype,
line_source = x_ManageDemand_rec.line_source,
qty_type_code = x_ManageDemand_rec.qty_type_code,
request_date = x_ManageDemand_rec.request_date ,
schedule_date = x_ManageDemand_rec.schedule_date ,
process_status = x_ManageDemand_rec.process_status
WHERE line_id = x_ManageDemand_rec.line_id;
rlm_message_sv.sql_error('rlm_manage_demand_sv.UpdateReq',
v_Progress);
END UpdateReq;
PROCEDURE NAME: UpdateSchedule
===========================================================================*/
PROCEDURE UpdateSchedule(x_ManageDemand_rec IN rlm_interface_lines%ROWTYPE,
x_AggregateDemand_rec IN rlm_interface_lines%ROWTYPE)
IS
v_Progress VARCHAR2(3) := '010';
rlm_core_sv.dpush(C_SDEBUG,'UpdateSchedule');
IF x_ManageDemand_rec.program_id <> k_INSERT THEN
--
UPDATE rlm_schedule_lines_all
SET interface_line_id = x_AggregateDemand_rec.line_id
WHERE line_id = x_ManageDemand_rec.schedule_line_id;
rlm_core_sv.dlog(C_DEBUG,'# of schedule lines updated',SQL%ROWCOUNT);
rlm_message_sv.sql_error('rlm_manage_demand_sv.UpdateSchedule', v_Progress);
END UpdateSchedule;
x_Delete_tab IN OUT NOCOPY t_Number_tab,
x_ExcpTab IN OUT NOCOPY t_Match_Tab)
IS
--
i NUMBER;
SetOperation(x_AggregateDemand_tab(k), k_INSERT);
x_Delete_tab(x_Delete_tab.COUNT) :=
x_AggregateDemand_tab(k).program_application_id;
UpdateSchedule(
g_ManageDemand_tab(x_AggregateDemand_tab(k).program_application_id),
x_AggregateDemand_tab(k));
rlm_core_sv.dlog(C_DEBUG,'Line to be deleted',
g_ManageDemand_tab(j).line_id);
SetOperation(g_ManageDemand_tab(j), k_DELETE);
UpdateSchedule(g_ManageDemand_tab(j), x_AggregateDemand_tab(k));
x_ExcpTab.DELETE(x);
v_Delete_tab t_Number_tab;
IF g_ManageDemand_tab(i).program_id <> k_DELETE OR
g_ManageDemand_tab(i).program_id IS NULL
THEN
MatchDemand(x_Group_rec,i, v_AggregateDemand_tab, v_Delete_tab,v_ExcpTab);
v_ExcpTab.DELETE;
FOR i IN 0..v_Delete_tab.COUNT-1 LOOP
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(C_DEBUG,'Line to be deleted',
g_ManageDemand_tab(v_Delete_tab(i)).line_id);
SetOperation(g_ManageDemand_tab(v_Delete_tab(i)), k_DELETE);
/* Select an element from the middle. */
pivot := g_ManageDemand_tab(TRUNC((First + Last) / 2)).request_date;
PROCEDURE NAME: InsertionSort
===========================================================================*/
PROCEDURE InsertionSort(lo IN NUMBER,
hi IN NUMBER)
IS
i NUMBER;
rlm_core_sv.dpush(C_SDEBUG,'InsertionSort');
rlm_message_sv.sql_error('rlm_manage_demand_sv.InsertionSort', v_Progress);
END InsertionSort;
IF g_ManageDemand_tab(i).program_id <> k_DELETE THEN
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(C_DEBUG, 'index ',i);
IF(g_ManageDemand_tab(i).program_id <> k_DELETE) THEN
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(C_DEBUG, 'g_ManageDemand_tab(i).primary_quantity',
g_ManageDemand_tab(i).primary_quantity);
SetOperation(g_ManageDemand_tab(i), k_UPDATE);
IF (g_ManageDemand_tab(j).program_id <> k_DELETE) THEN
--
-- Do not consider the lines which have been marked
-- for deletion
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(C_DEBUG, 'j.primary_quantity',
g_ManageDemand_tab(j).primary_quantity);
SetOperation(g_ManageDemand_tab(j), k_UPDATE);
SetOperation(g_ManageDemand_tab(j), k_UPDATE);
IF g_ManageDemand_tab(i).program_id <> k_DELETE THEN
--
v_qty_after_round := v_qty_after_round +
g_ManageDemand_Tab(i).primary_quantity;
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.order_header_id,
ril.blanket_number,
-- CR changes
ril.ship_to_customer_id
-- Perf change
-- 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 = rlm_core_sv.k_PS_AVAILABLE
AND ril.customer_item_id = x_Group_rec.customer_item_id
--AND ril.inventory_item_id = x_Group_rec.inventory_item_id
AND ril.ship_to_address_id = x_Group_rec.ship_to_address_id
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.order_header_id,
ril.blanket_number,
ril.ship_to_customer_id
-- Perf change
-- ril.cust_production_seq_num
ORDER BY ril.ship_to_address_id, ril.customer_item_id;
will be 2 lines which will be inserted but having the same
match attributes */
IF (l_debug <> -1) THEN
rlm_core_sv.dpop(C_SDEBUG);
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 *
FROM rlm_interface_lines_all
WHERE header_id = x_HeaderId
--and inventory_item_id = x_GroupRec.inventory_item_id
and customer_item_id = x_GroupRec.customer_item_id
and ship_from_org_id = x_GroupRec.ship_from_org_id
and ship_to_org_id = x_GroupRec.ship_to_org_id
--and schedule_item_num = x_GroupRec.schedule_item_num
and order_header_id = x_GroupRec.Order_header_id
-- Perf change
--and cust_production_seq_num = x_GroupRec.cust_production_seq_num
and process_status = rlm_core_sv.k_PS_AVAILABLE
FOR UPDATE NOWAIT;
SELECT *
FROM rlm_interface_lines_all
WHERE header_id = x_HeaderId
and customer_item_id = x_GroupRec.customer_item_id
and ship_from_org_id = x_GroupRec.ship_from_org_id
and ship_to_org_id = x_GroupRec.ship_to_org_id
and blanket_number = x_GroupRec.blanket_number
and process_status = rlm_core_sv.k_PS_AVAILABLE
FOR UPDATE NOWAIT;
PROCEDURE NAME: UpdateHeaderStatus
===========================================================================*/
PROCEDURE UpdateHeaderStatus (x_HeaderId IN NUMBER,
x_ScheduleHeaderId IN NUMBER,
x_ProcessStatus IN NUMBER )
IS
x_progress VARCHAR2(3) := '010';
rlm_core_sv.dpush(C_SDEBUG,'UpdateHeaderStatus');
UPDATE rlm_interface_headers_all
SET process_status = x_ProcessStatus
WHERE header_id = x_HeaderId;
rlm_core_sv.dlog(C_DEBUG,'Number of Interface header updated',SQL%ROWCOUNT);
UPDATE rlm_schedule_headers_all
SET process_status = x_ProcessStatus
WHERE header_id = x_ScheduleHeaderId;
rlm_core_sv.dlog(C_DEBUG,'Number of schedule header updated',SQL%ROWCOUNT);
rlm_message_sv.sql_error('rlm_managedemand_sv.UpdateHeaderStatus',x_progress);
END UpdateHeaderStatus;
PROCEDURE NAME: UpdateGroupStatus
===========================================================================*/
PROCEDURE UpdateGroupStatus (x_HeaderId IN NUMBER,
x_ScheduleHeaderId IN NUMBER,
x_GroupRec IN rlm_dp_sv.t_Group_rec,
x_ProcessStatus IN NUMBER,
x_UpdateLevel IN VARCHAR2)
IS
--
x_progress VARCHAR2(3) := '010';
rlm_core_sv.dpush(C_SDEBUG,'UpdateGroupStatus');
rlm_core_sv.dlog(C_DEBUG,'x_UpdateLevel',x_UpdateLevel);
IF x_UpdateLevel = 'GROUP' THEN
--
IF (l_debug <> -1) THEN
rlm_core_sv.dlog(C_DEBUG,'x_GroupRec.inventory_item_id',x_GroupRec.inventory_item_id);
UPDATE rlm_interface_lines
SET process_status = x_ProcessStatus
WHERE header_id = x_HeaderId
and inventory_item_id = x_GroupRec.inventory_item_id
and ship_from_org_id = x_GroupRec.ship_from_org_id
and ship_to_address_id = x_GroupRec.ship_to_address_id
and order_header_id = x_GroupRec.Order_header_id
-- Perf change
/*and nvl(cust_production_seq_num,k_VNULL) =
nvl(x_GroupRec.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.dlog(C_DEBUG,'Number of Interface lines updated',SQL%ROWCOUNT);
UPDATE rlm_schedule_lines
SET process_status = x_ProcessStatus
WHERE header_id = x_ScheduleHeaderId
and inventory_item_id = x_GroupRec.inventory_item_id
and ship_from_org_id = x_GroupRec.ship_from_org_id
and ship_to_address_id = x_GroupRec.ship_to_address_id
-- and order_header_id = x_GroupRec.Order_header_id
-- Perf change
/*and nvl(cust_production_seq_num, k_VNULL) =
nvl(x_GroupRec.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.dlog(C_DEBUG,'Number of schedule lines updated',SQL%ROWCOUNT);
ELSIF x_UpdateLevel = 'ALL' THEN
--
UPDATE rlm_interface_lines
SET process_status = x_ProcessStatus
WHERE header_id = x_HeaderId
and process_status = rlm_core_sv.k_PS_AVAILABLE;
rlm_core_sv.dlog(C_DEBUG,'Number of Interface lines updated',SQL%ROWCOUNT);
UPDATE rlm_schedule_lines
SET process_status = x_ProcessStatus
WHERE header_id = x_ScheduleHeaderId
and process_status = rlm_core_sv.k_PS_AVAILABLE;
rlm_core_sv.dlog(C_DEBUG,'Number of schedule lines updated',SQL%ROWCOUNT);
rlm_message_sv.sql_error('rlm_managedemand_sv.UpdateGroupStatus',x_progress);
END UpdateGroupStatus;
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 TO_CHAR(TRUNC(min(il.start_date_time)), 'RRRR/MM/DD HH24:MI:SS')
INTO v_min_horizon_date
FROM rlm_interface_lines il,
rlm_schedule_lines 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;
SELECT start_date_time,
primary_quantity,
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,
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_Source_Tab(i).organization_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_Source_Tab(i).organization_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 atp_flag, atp_components_flag
INTO v_atp_flag, v_atp_components_flag
FROM mtl_system_items
WHERE inventory_item_id = x_inventory_item_id
AND organization_id = x_ship_from_org_id;
SELECT start_date_time,
primary_quantity,
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,
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 header_id
FROM oe_order_lines
WHERE 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 veh_cus_item_cum_key_id = x_cum_key_id
GROUP BY header_id;
SELECT TO_CHAR(TRUNC(min(il.start_date_time)), 'RRRR/MM/DD HH24:MI:SS')
INTO v_min_horizon_date
FROM rlm_interface_lines il,
rlm_schedule_lines 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;