[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hsecs INTO l_start_time from v$timer; --Bugfix 12863728
SELECT hsecs INTO l_end_time from v$timer; --Bugfix 12863728
rlm_core_sv.dlog(k_DEBUG,'Inserted new forecast for ', t_designator(v_counter).forecast_designator);
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;
t_forecast.DELETE;
t_designator.DELETE;
t_forecast.DELETE;
t_designator.DELETE;
t_forecast.DELETE;
t_designator.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;
t_forecast.DELETE;
t_designator.DELETE;
t_forecast.DELETE;
t_designator.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;
k_DELETE,
k_REPLACE,
k_REPLACE_ALL,
k_ORIGINAL,
k_CHANGE) THEN
--
x_designator(x_designator.COUNT + 1).forecast_designator :=
v_forecast_designator;
IF x_Sched_rec.schedule_purpose NOT IN (k_CANCEL,k_DELETE) THEN
--
RLM_TPA_SV.LoadForecast(x_Sched_rec,
x_Group_rec ,
x_forecast,
v_forecast_designator);
SELECT rih.customer_id,
ril.ship_from_org_id,
ril.ship_to_address_id,
ril.ship_to_site_use_id,
ril.bill_to_address_id,
ril.bill_to_site_use_id,
ril.customer_item_id,
ril.inventory_item_id,
ril.industry_attribute15,
ril.ship_to_customer_id
FROM rlm_interface_headers rih,
rlm_interface_lines_all ril
WHERE rih.header_id = x_Sched_rec.header_id
AND ril.header_id = rih.header_id
AND ril.industry_attribute15 = x_Group_rec.ship_from_org_id
AND ril.item_detail_type = k_MRP_FORECAST
AND ril.inventory_item_id = x_Group_rec.inventory_item_id
AND ril.ship_to_address_id = x_Group_rec.ship_to_address_id
AND ril.process_status = rlm_core_sv.k_PS_AVAILABLE
AND rih.org_id = ril.org_id
GROUP BY rih.customer_id,
ril.ship_from_org_id,
ril.ship_to_address_id,
ril.ship_to_site_use_id,
ril.bill_to_address_id,
ril.bill_to_site_use_id,
ril.customer_item_id,
ril.inventory_item_id,
ril.industry_attribute15,
ril.ship_to_customer_id
ORDER BY
ril.ship_to_site_use_id,
ril.bill_to_site_use_id,
ril.customer_item_id;
SELECT *
FROM rlm_interface_headers
WHERE header_id = x_header_id
AND process_status = rlm_core_sv.k_PS_AVAILABLE
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_forecast_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_site_use_id = x_Group_rec.ship_to_site_use_id
AND bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
AND customer_item_id = x_Group_rec.customer_item_id
AND inventory_item_id = x_Group_rec.inventory_item_id
AND process_status = rlm_core_sv.k_PS_AVAILABLE
FOR UPDATE NOWAIT;
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 = rlm_core_sv.k_PS_AVAILABLE
AND Item_detail_type = k_MRP_FORECAST;
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_ERROR)
AND item_detail_type = k_MRP_FORECAST;
UPDATE rlm_schedule_lines_all
SET process_status = x_Status
WHERE header_id = x_ScheduleheaderId
AND ship_from_org_id = x_Group_rec.ship_from_org_id
AND nvl(ship_to_site_use_id,k_NNULL)
= nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
AND nvl(bill_to_site_use_id,k_NNULL)
= nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
AND inventory_item_id = x_Group_rec.inventory_item_id
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_ERROR)
AND line_id IN ( select schedule_line_id
from rlm_interface_lines
WHERE header_id = x_header_id
AND ship_from_org_id = x_Group_rec.ship_from_org_id
AND nvl(ship_to_site_use_id,k_NNULL)
= nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
AND nvl(bill_to_site_use_id,k_NNULL)
= nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
AND inventory_item_id = x_Group_rec.inventory_item_id
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_ERROR)
AND item_detail_type = k_MRP_FORECAST);
rlm_core_sv.dlog(k_DEBUG,'No of Schedule Lines Updated ', SQL%ROWCOUNT);
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 nvl(ship_to_site_use_id,k_NNULL)
= nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
AND nvl(bill_to_site_use_id,k_NNULL)
= nvl(x_Group_rec.bill_to_site_use_id,k_NNULL)
AND inventory_item_id = x_Group_rec.inventory_item_id
AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
rlm_core_sv.k_PS_ERROR)
AND item_detail_type = k_MRP_FORECAST;
rlm_core_sv.dlog(k_DEBUG,'No of interface Lines Updated ', SQL%ROWCOUNT);
rlm_message_sv.sql_error('rlm_forecast_sv.UpdateGroupStatus',x_progress);
END UpdateGroupStatus;
t_forecast.DELETE;
SELECT ril.inventory_item_id inventory_item_id,
ril.ship_from_org_id organization_id,
ril.request_date forecast_date,
ril.primary_quantity quantity,
ril.uom_code uom_code, -- Bug 4176961
ril.primary_uom_code primary_uom_code, -- Bug 4176961
ril.item_detail_subtype bucket_type,
ril.line_id demand_stream_id,
ril.industry_attribute1 attribute01,
ril.industry_attribute2 attribute02,
ril.industry_attribute3 attribute03,
ril.industry_attribute4 attribute04,
ril.industry_attribute5 attribute05,
ril.industry_attribute6 attribute06,
ril.industry_attribute7 attribute07,
ril.industry_attribute8 attribute08,
ril.industry_attribute9 attribute09,
ril.industry_attribute10 attribute10,
ril.industry_attribute11 attribute11,
ril.industry_attribute12 attribute12,
ril.industry_attribute13 attribute13,
ril.industry_attribute14 attribute14,
ril.industry_attribute15 attribute15
FROM rlm_interface_lines ril
WHERE ril.ship_from_org_id = x_Group_rec.ship_from_org_id
AND ril.header_id = x_Sched_rec.header_id
AND nvl(ril.ship_to_site_use_id ,k_NNULL)
= nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
AND ril.bill_to_site_use_id IS NULL
AND ril.inventory_item_id = x_Group_rec.inventory_item_id
AND ril.customer_item_id = x_Group_rec.customer_item_id
AND ril.item_detail_type = k_MRP_FORECAST
AND ril.process_status = rlm_core_sv.k_PS_AVAILABLE
AND ril.request_date is NOT NULL --bug 2882311
AND ril.primary_quantity <> 0
--bug 1786492
UNION
SELECT ril.inventory_item_id inventory_item_id,
ril.ship_from_org_id organization_id,
ril.request_date forecast_date,
ril.primary_quantity quantity,
ril.uom_code uom_code, -- Bug 4176961
ril.primary_uom_code primary_uom_code, -- Bug 4176961
ril.item_detail_subtype bucket_type,
ril.line_id demand_stream_id,
ril.industry_attribute1 attribute01,
ril.industry_attribute2 attribute02,
ril.industry_attribute3 attribute03,
ril.industry_attribute4 attribute04,
ril.industry_attribute5 attribute05,
ril.industry_attribute6 attribute06,
ril.industry_attribute7 attribute07,
ril.industry_attribute8 attribute08,
ril.industry_attribute9 attribute09,
ril.industry_attribute10 attribute10,
ril.industry_attribute11 attribute11,
ril.industry_attribute12 attribute12,
ril.industry_attribute13 attribute13,
ril.industry_attribute14 attribute14,
ril.industry_attribute15 attribute15
FROM rlm_interface_lines ril
WHERE ril.ship_from_org_id = x_Group_rec.ship_from_org_id
AND ril.header_id = x_Sched_rec.header_id
AND nvl(ril.ship_to_site_use_id ,k_NNULL)
= nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
AND ril.bill_to_site_use_id IS NULL
AND ril.inventory_item_id = x_Group_rec.inventory_item_id
AND ril.item_detail_type = k_MRP_FORECAST
AND ril.process_status = rlm_core_sv.k_PS_PROCESSED
AND ril.primary_quantity <> 0
AND x_Sched_rec.schedule_purpose <> k_ADD; --Bugfix 9688324
SELECT ril.inventory_item_id inventory_item_id,
ril.ship_from_org_id organization_id,
ril.request_date forecast_date,
ril.primary_quantity quantity,
ril.uom_code uom_code, -- Bug 4176961
ril.primary_uom_code primary_uom_code, -- Bug 4176961
ril.item_detail_subtype bucket_type,
ril.line_id demand_stream_id,
ril.industry_attribute1 attribute01,
ril.industry_attribute2 attribute02,
ril.industry_attribute3 attribute03,
ril.industry_attribute4 attribute04,
ril.industry_attribute5 attribute05,
ril.industry_attribute6 attribute06,
ril.industry_attribute7 attribute07,
ril.industry_attribute8 attribute08,
ril.industry_attribute9 attribute09,
ril.industry_attribute10 attribute10,
ril.industry_attribute11 attribute11,
ril.industry_attribute12 attribute12,
ril.industry_attribute13 attribute13,
ril.industry_attribute14 attribute14,
ril.industry_attribute15 attribute15
FROM rlm_interface_lines ril
WHERE ril.ship_from_org_id = x_Group_rec.ship_from_org_id
AND ril.header_id = x_Sched_rec.header_id
AND nvl(ril.ship_to_site_use_id ,k_NNULL)
= nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
AND bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
AND ril.inventory_item_id = x_Group_rec.inventory_item_id
AND ril.customer_item_id = x_Group_rec.customer_item_id
AND ril.item_detail_type = k_MRP_FORECAST
AND ril.process_status = rlm_core_sv.k_PS_AVAILABLE
AND ril.request_date is NOT NULL --bug 2882311
AND ril.primary_quantity <> 0
--bug 1786492
UNION
SELECT ril.inventory_item_id inventory_item_id,
ril.ship_from_org_id organization_id,
ril.request_date forecast_date,
ril.primary_quantity quantity,
ril.uom_code uom_code, -- Bug 4176961
ril.primary_uom_code primary_uom_code, -- Bug 4176961
ril.item_detail_subtype bucket_type,
ril.line_id demand_stream_id,
ril.industry_attribute1 attribute01,
ril.industry_attribute2 attribute02,
ril.industry_attribute3 attribute03,
ril.industry_attribute4 attribute04,
ril.industry_attribute5 attribute05,
ril.industry_attribute6 attribute06,
ril.industry_attribute7 attribute07,
ril.industry_attribute8 attribute08,
ril.industry_attribute9 attribute09,
ril.industry_attribute10 attribute10,
ril.industry_attribute11 attribute11,
ril.industry_attribute12 attribute12,
ril.industry_attribute13 attribute13,
ril.industry_attribute14 attribute14,
ril.industry_attribute15 attribute15
FROM rlm_interface_lines ril
WHERE ril.ship_from_org_id = x_Group_rec.ship_from_org_id
AND ril.header_id = x_Sched_rec.header_id
AND nvl(ril.ship_to_site_use_id ,k_NNULL)
= nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
AND bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
AND ril.inventory_item_id = x_Group_rec.inventory_item_id
AND ril.item_detail_type = k_MRP_FORECAST
AND ril.process_status = rlm_core_sv.k_PS_PROCESSED
AND ril.primary_quantity <> 0
AND x_Sched_rec.schedule_purpose <> k_ADD; --Bugfix 9688324
t_forecast(index_cnt).last_update_date :=sysdate;
t_forecast(index_cnt).last_update_login := fnd_global.login_id;
t_forecast(index_cnt).program_update_date :=null;
t_forecast(index_cnt).last_update_date :=sysdate;
t_forecast(index_cnt).last_update_login := fnd_global.login_id;
t_forecast(index_cnt).program_update_date :=null;
fnd_profile.get('RLM_SELECTION_LIST',v_ListName);
rlm_core_sv.dlog(k_DEBUG,'Profile: RLM_SELECTION_LIST',v_ListName);
SELECT SUBSTR(v_ListName,1,INSTR(v_ListName,',')-1)
INTO v_ListName
FROM DUAL;
rlm_core_sv.dlog(k_DEBUG, 'error getting selection list',
SUBSTR(SQLERRM,1,200));
SELECT CUST_SITE.CUST_ACCT_SITE_ID
INTO v_bill_address_Id
FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
HZ_CUST_SITE_USES_ALL CUST_SITE
WHERE CUST_SITE.site_use_id = x_bill_site_id
AND CUST_SITE.site_use_code = 'BILL_TO'
AND CUST_SITE.status = 'A'
AND CUST_SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.status = 'A'
AND CUST_SITE.org_id = ACCT_SITE.org_id;
SELECT CUST_SITE.SITE_USE_ID
INTO v_bill_site_id
FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
HZ_CUST_SITE_USES_ALL CUST_SITE
WHERE CUST_SITE.CUST_ACCT_SITE_ID = x_bill_address_id
AND CUST_SITE.site_use_code = 'BILL_TO'
AND CUST_SITE.status = 'A'
AND CUST_SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.status = 'A'
AND CUST_SITE.org_id = ACCT_SITE.org_id;
SELECT forecast_designator
INTO x_ForecastDesignator
FROM mrp_forecast_designators
WHERE customer_id=NVL(x_ship_to_customer_id, x_customer_id)
AND (bill_id = v_bill_site_id
OR (bill_id IS NULL AND v_bill_site_id IS NULL))
AND (ship_id = x_Ship_Site_Id
OR (ship_id IS NULL AND x_Ship_Site_Id IS NULL))
AND (organization_id = x_ShipFromOrgId
OR (organization_id IS NULL AND x_ShipFromOrgId IS NULL))
AND forecast_designator IN
(SELECT source_forecast_designator
FROM mrp_load_parameters
WHERE source_organization_id = x_ShipFromOrgId
AND selection_list_type = 2
AND selection_list_name = v_ListName);
SELECT forecast_designator
INTO x_ForecastDesignator
FROM mrp_forecast_designators
WHERE customer_id= nvl(x_ship_to_customer_id, x_customer_id)
AND ship_id = x_Ship_Site_Id
AND bill_id IS NULL
AND organization_id = x_ShipFromOrgId
AND forecast_designator IN
(SELECT source_forecast_designator
FROM mrp_load_parameters
WHERE source_organization_id = x_ShipFromOrgId
AND selection_list_type = 2
AND selection_list_name = v_ListName);
SELECT forecast_designator
INTO x_ForecastDesignator
FROM mrp_forecast_designators
WHERE customer_id= nvl(x_ship_to_customer_id, x_customer_id)
AND ship_id IS NULL
AND bill_id IS NULL
AND organization_id = x_ShipFromOrgId
AND forecast_designator IN
(SELECT source_forecast_designator
FROM mrp_load_parameters
WHERE source_organization_id = x_ShipFromOrgId
AND selection_list_type = 2
AND selection_list_name = v_ListName);
SELECT forecast_designator
INTO x_ForecastDesignator
FROM mrp_forecast_designators
WHERE customer_id= x_customer_id
AND ship_id IS NULL
AND bill_id IS NULL
AND organization_id = x_ShipFromOrgId
AND forecast_designator IN
(SELECT source_forecast_designator
FROM mrp_load_parameters
WHERE source_organization_id = x_ShipFromOrgId
AND selection_list_type = 2
AND selection_list_name = v_ListName);
PURPOSE: Deletes all the forecast for a designator
==============================================================================*/
PROCEDURE EmptyForecast( x_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
x_Group_rec IN OUT NOCOPY rlm_dp_sv.t_Group_rec,
x_forecast IN OUT NOCOPY
mrp_forecast_interface_pk.t_forecast_interface,
x_designator IN OUT NOCOPY
mrp_forecast_interface_pk.t_forecast_designator,
x_t_designator IN OUT NOCOPY
mrp_forecast_interface_pk.t_forecast_designator)
IS
--
x_progress VARCHAR2(3) := '010';
rlm_core_sv.dlog(k_DEBUG,'Old forecast deleted for ', x_designator(1).forecast_designator);
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 count(*) into v_mrp_count
from rlm_interface_lines
where header_id = x_Sched_rec.header_id
and item_detail_type = k_MRP_FORECAST
and process_status = 5
and bill_to_site_use_id = x_Group_rec.bill_to_site_use_id
and nvl(ship_to_site_use_id, k_NNULL)
= nvl(x_Group_rec.ship_to_site_use_id, k_NNULL)
and ship_from_org_id = x_Group_rec.ship_from_org_id;
select count(*) into v_mrp_count
from rlm_interface_lines
where header_id = x_Sched_rec.header_id
and item_detail_type = k_MRP_FORECAST
and process_status = 5
and bill_to_site_use_id IS NULL
and nvl(ship_to_site_use_id ,k_NNULL)
= nvl(x_Group_rec.ship_to_site_use_id,k_NNULL)
and ship_from_org_id = x_Group_rec.ship_from_org_id;
rlm_core_sv.dlog(k_DEBUG,'already deleted old forecast for designator', g_designator_tab(v_counter).designator);
END LOOP; --loop for designators already deleted
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 item_number
from mtl_item_flexfields
where inventory_item_id = p_item_id
and organization_id = p_org_id;