[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into l_count
from mrp_atp_schedule_temp
where session_id = x_session_id
and status_flag = 1;
SELECT
Rowidtochar(a.ROWID),
a.inventory_item_id,
a.inventory_item_name,
a.organization_id,
a.sr_instance_id,
Decode(override_flag,'Y',
Nvl(a.firm_source_org_id,a.source_organization_id),
a.source_organization_id),
Decode(override_flag,'Y',
Nvl(a.firm_source_org_code,a.source_organization_code),
a.source_organization_code),
a.order_line_id,
a.Scenario_Id,
a.Order_Header_Id,
a.order_number,
a.Calling_Module,
a.Customer_Id,
a.Customer_Site_Id,
a.Destination_Time_Zone,
a.quantity_ordered,
a.uom_code,
Decode(override_flag,'Y',
Nvl(a.firm_ship_date,a.requested_ship_date),
a.requested_ship_date),
Decode(override_flag,'Y',
Nvl(a.firm_arrival_date,a.requested_arrival_date),
a.requested_arrival_date),
date_null, -- a.Earliest_Acceptable_Date,
a.Latest_Acceptable_Date,
a.Delivery_Lead_Time,
a.Freight_Carrier,
a.Ship_Method,
a.Demand_Class,
nvl(a.ship_set_name,
a.ship_set_id+Nvl(a.source_organization_id,0)), -- a.Ship_Set_Name,
-- When it is put back into the table the name will be used.
a.arrival_set_id, --a.Arrival_Set_Name
-- we don't append source_org since they can be different
-- and we don't need it since we don't have pick sources
a.Override_Flag,
a.Action,
date_null, --a.Ship_Date, ??? scheduled_ship_date
number_null, -- a.Available_Quantity,
number_null, -- a.Requested_Date_Quantity,
date_null, -- a.Group_Ship_Date,
date_null, -- a.Group_Arrival_Date,
a.Vendor_Id,
a.Vendor_Name,
a.Vendor_Site_Id,
a.Vendor_Site_Name,
a.Insert_Flag,
number_null, -- a.Error_Code,
char_1_null, -- a.Error_Message
a.old_source_organization_id,
a.old_demand_class,
a.atp_lead_time, -- bug 1303240
number_one, --substitution_typ_code,
number_one, -- REQ_ITEM_DETAIL_FLAG
p_diagnostic_atp,
a.assignment_set_id,
a.sequence_number,
a.firm_flag,
a.order_line_number,
a.option_number,
a.shipment_number,
a.item_desc,
a.old_line_schedule_date,
a.old_source_organization_code,
a.firm_source_org_id,
a.firm_source_org_code,
a.firm_ship_date,
a.firm_arrival_date,
a.ship_method_text,
a.ship_set_id,
a.arrival_set_id,
a.PROJECT_ID,
a.TASK_ID,
a.PROJECT_NUMBER,
a.TASK_NUMBER,
a.Top_Model_line_id,
a.ATO_Model_Line_Id,
a.Parent_line_id,
a.Config_item_line_id,
a.Validation_Org,
a.Component_Sequence_ID,
a.Component_Code,
a.line_number,
a.included_item_flag
BULK collect into
x_atp_rec.row_id,
x_atp_rec.Inventory_Item_Id,
x_atp_rec.Inventory_Item_Name,
x_atp_rec.organization_id,
x_atp_rec.instance_id,
x_atp_rec.Source_Organization_Id,
x_atp_rec.Source_Organization_Code,
x_atp_rec.Identifier,
x_atp_rec.Scenario_Id,
x_atp_rec.Demand_Source_Header_Id,
x_atp_rec.order_number,
x_atp_rec.Calling_Module,
x_atp_rec.Customer_Id,
x_atp_rec.Customer_Site_Id,
x_atp_rec.Destination_Time_Zone,
x_atp_rec.Quantity_Ordered,
x_atp_rec.Quantity_UOM,
x_atp_rec.Requested_Ship_Date,
x_atp_rec.Requested_Arrival_Date,
x_atp_rec.Earliest_Acceptable_Date,
x_atp_rec.Latest_Acceptable_Date,
x_atp_rec.Delivery_Lead_Time,
x_atp_rec.Freight_Carrier,
x_atp_rec.Ship_Method,
x_atp_rec.Demand_Class,
x_atp_rec.Ship_Set_Name,
x_atp_rec.Arrival_Set_Name,
x_atp_rec.Override_Flag,
x_atp_rec.Action,
x_atp_rec.Ship_Date,
x_atp_rec.Available_Quantity,
x_atp_rec.Requested_Date_Quantity,
x_atp_rec.Group_Ship_Date,
x_atp_rec.Group_Arrival_Date,
x_atp_rec.Vendor_Id,
x_atp_rec.Vendor_Name,
x_atp_rec.Vendor_Site_Id,
x_atp_rec.Vendor_Site_Name,
x_atp_rec.Insert_Flag,
x_atp_rec.Error_Code,
x_atp_rec.message,
x_atp_rec.old_source_organization_id,
x_atp_rec.old_demand_class,
x_atp_rec.atp_lead_time, -- bug 1303240
x_atp_rec.substitution_typ_code,
x_atp_rec.REQ_ITEM_DETAIL_FLAG,
x_atp_rec.attribute_02, -- ATP Pegging
x_atp_rec.attribute_03,
x_atp_rec.sequence_number,
x_atp_rec.firm_flag,
x_atp_rec.order_line_number,
x_atp_rec.option_number,
x_atp_rec.shipment_number,
x_atp_rec.item_desc,
x_atp_rec.old_line_schedule_date,
x_atp_rec.old_source_organization_code,
x_atp_rec.firm_source_org_id,
x_atp_rec.firm_source_org_code,
x_atp_rec.firm_ship_date,
x_atp_rec.firm_arrival_date,
x_atp_rec.ship_method_text,
x_atp_rec.ship_set_id,
x_atp_rec.arrival_set_id,
x_atp_rec.PROJECT_ID,
x_atp_rec.TASK_ID,
x_atp_rec.PROJECT_NUMBER,
x_atp_rec.TASK_NUMBER,
x_atp_rec.Top_Model_line_id,
x_atp_rec.ATO_Model_Line_Id,
x_atp_rec.Parent_line_id,
x_atp_rec.Config_item_line_id,
x_atp_rec.Validation_Org,
x_atp_rec.Component_Sequence_ID,
x_atp_rec.Component_Code,
x_atp_rec.line_number,
x_atp_rec.included_item_flag
from mrp_atp_schedule_temp a
where a.session_id = x_session_id
and a.status_flag = 1
order by a.source_organization_code,
a.sequence_number,
a.arrival_set_id,
a.ship_set_id,
a.line_number,
a.shipment_number,
nvl(a.option_number, -1);
atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Insert_Flag:'
|| to_char(x_atp_rec.Insert_Flag(j)) );
atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Insert_Flag:'
|| to_char(x_atp_rec.old_source_organization_id(j)) );
atp_debug('GET_ATP_RESULT: ' || 'x_atp_rec.Insert_Flag:'
|| x_atp_rec.old_demand_class(j) );
select distinct meaning
from msc_demand_classes
where demand_class = v_demand_class;
PROCEDURE delete_lines
( p_session_id NUMBER,
p_where_clause varchar2) IS
sqlstmt VARCHAR2(100);
atp_debug('delete_lines: ' || ' deleting all rows for session_id 1 '||p_session_id);
sqlstmt := 'DELETE FROM mrp_atp_schedule_temp mast '||
'WHERE '||p_where_clause||' and mast.session_id = :session_id';
atp_debug('delete_lines: ' || ' deleting all rows for session_id 2 '||p_session_id);
DELETE FROM mrp_atp_details_temp madt
WHERE madt.session_id = p_session_id;
atp_debug('delete_lines: ' || ' deleting all rows for session_id 3 '||p_session_id);
END delete_lines;
select 1
from mrp_atp_schedule_temp
where session_id = p_session_id
and ato_model_line_id is NOT NULL
and status_flag = 4
and rownum = 1;
SELECT count(*)
into l_rac_count
from gv$instance;
' calling MSC_ATP_UTILS.Update_line_item_properties');
MSC_ATP_UTILS.Update_Line_Item_Properties(p_session_id => x_session_id);
SELECT DISTINCT mast.customer_site_id
INTO l_customer_site_id
FROM mrp_atp_schedule_temp mast
WHERE mast.session_id = x_session_id
AND status_flag = 4;
' SELECT rule_name '||
' FROM msc_atp_rules'||l_dblink||' mar, '||
' msc_system_items'||l_dblink||' msi '||
' WHERE '||
' mar.rule_id = msi.atp_rule_id '||
' AND mar.sr_instance_id = msi.sr_instance_id '||
' AND msi.sr_instance_id = :sr_instance_id '||
' AND msi.organization_id = :org_id '||
' AND msi.inventory_item_id = :item_id '||
' AND msi.plan_id = -1';
'SELECT rule_name '||
' FROM msc_atp_rules'||l_dblink||' mar, '||
' msc_trading_partners'||l_dblink||' mtp '||
' WHERE '||
' mar.rule_id = mtp.default_atp_rule_id '||
' AND mar.sr_instance_id = mtp.sr_instance_id '||
' AND mtp.sr_tp_id = :org_id '||
' AND mtp.sr_instance_id = :sr_instance_id '||
' AND mtp.partner_type = 3';
SELECT rule_name
INTO atp_rule_name
FROM mtl_atp_rules mar,
mtl_system_items msi
WHERE
mar.rule_id = msi.atp_rule_id
AND msi.organization_id = org_id
AND msi.inventory_item_id = item_id;
SELECT rule_name
INTO atp_rule_name
FROM mtl_atp_rules mar,
mtl_parameters mp
WHERE
mar.rule_id = mp.default_atp_rule_id
AND mp.organization_id = org_id;
' select assignment_set_id '||
' from msc_assignment_sets'||l_dblink||' '||
' where sr_instance_id = :x_sr_instance_id '||
' and sr_assignment_set_id = :x_assignment_set_id';
' SELECT assignment_set_name '||
' FROM msc_assignment_sets' ||l_dblink||' '||
' WHERE assignment_set_id = :x_assignment_set_id '||
' AND sr_instance_id = :x_sr_instance_id';
SELECT mas.assignment_set_name
INTO x_assignment_set_name
FROM mrp_assignment_sets mas
WHERE mas.assignment_set_id = x_assignment_set_id;
' select assignment_set_id '||
' from msc_assignment_sets'||l_dblink||' '||
' where sr_instance_id = :x_sr_instance_id '||
' and sr_assignment_set_id = :x_assignment_set_id';
SELECT mas.assignment_set_id,mas.assignment_set_name
INTO x_assignment_set_id, x_assignment_set_name
FROM msc_apps_instances mai, msc_assignment_sets mas
WHERE mai.instance_id = x_sr_instance_id
AND mas.assignment_set_id = mai.assignment_set_id
AND mas.sr_instance_id = mai.instance_id;
SELECT mas.assignment_set_name
INTO x_assignment_set_name
FROM msc_assignment_sets mas
WHERE mas.assignment_set_id = x_assignment_set_id
AND mas.sr_instance_id = x_sr_instance_id;
select ltrim(rtrim(value)) into order_sch_wb.file_dir from
(select value from v$parameter2 where name='utl_file_dir' order by rownum desc)
where rownum <2;
cursor wk_cur is select b.week_start_date
from mtl_parameters p, bom_cal_week_start_dates b
where p.calendar_exception_set_id = b.exception_set_id
and p.calendar_code = b.calendar_code
and p.organization_id = p_organization_id
and b.week_start_date > v_week_start_date
and b.week_start_date <= v_week_end_date;
cursor pr_cur is select b.period_start_date
from mtl_parameters p, bom_period_start_dates b
where p.calendar_exception_set_id = b.exception_set_id
and p.calendar_code = b.calendar_code
and p.organization_id = p_organization_id
and b.period_start_date > v_period_start_date
and b.period_start_date <= v_period_end_date;
select b.week_start_date
from msc_trading_partners p, msc_cal_week_start_dates b
where p.calendar_exception_set_id = b.exception_set_id
AND p.sr_instance_id = p_sr_instance_id
AND b.sr_instance_id = p.sr_instance_id
and p.calendar_code = b.calendar_code
and p.sr_tp_id = p_organization_id
AND p.partner_type = 3
and b.week_start_date > v_week_start_date
and b.week_start_date <= v_week_end_date;
select b.period_start_date
from msc_trading_partners p, MSC_period_START_DATES b
where p.calendar_exception_set_id = b.exception_set_id
AND p.sr_instance_id = p_sr_instance_id
AND b.sr_instance_id = p.sr_instance_id
and p.calendar_code = b.calendar_code
and p.sr_tp_id = p_organization_id
AND p.partner_type = 3
and b.period_start_date > v_period_start_date
and b.period_start_date <= v_period_end_date;
SELECT DISTINCT
inventory_item_name||order_sch_wb.delim||owb_tree.lookups(5) -- 'Item'
||order_sch_wb.delim||source_organization_code
FROM mrp_atp_schedule_temp
WHERE end_pegging_id = p_end_pegging_id;
SELECT Decode(department_id, NULL,
Decode(supplier_id, NULL,
inventory_item_name||order_sch_wb.delim||owb_tree.lookups(5)||order_sch_wb.delim||organization_code,
-- 'Item'
supplier_name||order_sch_wb.delim||owb_tree.lookups(17)||order_sch_wb.delim||supplier_site_name),
department_code||order_sch_wb.delim||Decode(resource_code, NULL, owb_tree.lookups(18),owb_tree.lookups(6))||order_sch_wb.delim||Decode(resource_code, NULL, ' ', resource_code))
-- blank above is so that pivot table will get some value to display
-- for line. otherwise following values shift left.
INTO l_pivot_hdr
FROM mrp_atp_details_temp
WHERE pegging_id = p_pegging_id
AND record_type = 3;
select
Nvl(total_supply_quantity,0),
Nvl(total_demand_quantity,0),
period_start_date,
Nvl(period_end_date,period_start_date),
-- hack to avoid NULL
Nvl(period_quantity,0),
Nvl(cumulative_quantity,0),
Nvl(total_bucketed_demand_quantity,0)
bulk collect INTO
x_atp_period_string.Total_Supply_Quantity,
x_atp_period_string.Total_Demand_Quantity,
x_atp_period_string.Period_Start_Date,
x_atp_period_string.Period_End_Date,
x_atp_period_string.Period_Quantity,
x_atp_period_string.Cumulative_Quantity,
x_atp_period_string.Bucketed_Quantity
FROM MRP_ATP_DETAILS_TEMP
WHERE
record_type = 1
AND (( p_pegging_id IS NULL and end_pegging_id = p_end_pegging_id)
OR pegging_id = p_pegging_id)
order by period_start_date;
select
Nvl(a.total_supply_quantity,0),
Nvl(a.total_demand_quantity,0),
b.period_start_date,
Nvl(b.period_end_date,b.period_start_date),
-- hack to avoid NULL
Nvl(a.period_quantity,0),
Nvl(a.cumulative_quantity,0),
Nvl(a.total_bucketed_demand_quantity,0)
bulk collect INTO
x_atp_period_string.Total_Supply_Quantity,
x_atp_period_string.Total_Demand_Quantity,
x_atp_period_string.Period_Start_Date,
x_atp_period_string.Period_End_Date,
x_atp_period_string.Period_Quantity,
x_atp_period_string.Cumulative_Quantity,
x_atp_period_string.Bucketed_Quantity
FROM MRP_ATP_DETAILS_TEMP a,
MRP_ATP_DETAILS_TEMP b
WHERE
a.record_type (+) = 1
and a.session_id (+) = p_session_id
and b.session_id = p_old_session_id
and b.record_type = 1
and a.period_start_date(+) = b.period_start_date
order by b.period_start_date;
select
Nvl(total_supply_quantity,0),
Nvl(total_demand_quantity,0),
period_start_date,
Nvl(period_end_date,period_start_date),
-- hack to avoid NULL
Nvl(period_quantity,0),
Nvl(cumulative_quantity,0),
Nvl(total_bucketed_demand_quantity,0)
bulk collect INTO
x_atp_period_string.Total_Supply_Quantity,
x_atp_period_string.Total_Demand_Quantity,
x_atp_period_string.Period_Start_Date,
x_atp_period_string.Period_End_Date,
x_atp_period_string.Period_Quantity,
x_atp_period_string.Cumulative_Quantity,
x_atp_period_string.Bucketed_Quantity
FROM MRP_ATP_DETAILS_TEMP
WHERE
record_type = 1
and session_id = p_session_id
order by period_start_date;
delete from mrp_atp_schedule_temp
where status_flag = 1
and session_id = p_session_id;
update mrp_atp_schedule_temp
set status_flag = 1
where session_id = p_session_id
and status_flag = 2;
UPDATE mrp_atp_schedule_temp mast
set
mast.exception1 = Decode(error_code,53,1,52,1,100,1,0),
mast.exception2 = Decode(error_code,0,
Decode(Sign(trunc(mast.scheduled_ship_date) -
Nvl(trunc(mast.old_line_schedule_date),trunc(mast.scheduled_ship_date)))
,1,1,0),
0), -- later than old sched date
mast.exception3 = Decode(error_code,0,
Decode(Sign(trunc(mast.scheduled_ship_date) +
Decode(trunc(mast.requested_ship_date),NULL,Nvl(mast.delivery_lead_time,0),0)
-trunc(mast.promise_date)),1,1,0), 0),
-- later than promise date. Consider sched ship/arrival date depending on whether
-- requested date was ship/arrival.
mast.exception4= Decode(error_code,0,
Decode(Sign(trunc(mast.SCHEDULED_SHIP_DATE) + Decode(trunc(mast.requested_ship_date),NULL,
Nvl(mast.delivery_lead_time,0),0)
- NVL(trunc(mast.requested_ship_date),trunc(mast.requested_arrival_date))),1,1,0),
0), -- later than request date
mast.exception5 = 0, -- insufficient margin
mast.exception6 = Decode(error_code,0,
Decode(substr(mast.SOURCE_ORGANIZATION_CODE, instr(mast.SOURCE_ORGANIZATION_CODE,':')+1,3),
Nvl(mast.OLD_SOURCE_ORGANIZATION_CODE,
substr(mast.SOURCE_ORGANIZATION_CODE, instr(mast.SOURCE_ORGANIZATION_CODE,':')+1,3)),0,1),
0),
mast.exception7 = Decode(error_code,0,0,52,0,53,0,100,0,NULL,0,1),
mast.exception8 = 0,
mast.exception9 = 0,
mast.exception10 = 0,
mast.exception11 = 0,
mast.exception12 = 0,
mast.exception13 = 0,
mast.exception14 = 0,
mast.exception15 = 0
WHERE session_id = p_session_id
AND scenario_id = 1
AND status_flag = 2;
msc_sch_wb.atp_debug(' After update in calc_exceptions ');
SELECT SEGMENT1
INTO supply_demand_source_name
FROM PO_HEADERS
WHERE PO_HEADER_ID=supply_demand_source_id;
SELECT CONCATENATED_SEGMENTS
INTO supply_demand_source_name
FROM mtl_sales_orders_kfv
WHERE SALES_ORDER_ID = supply_demand_source_id;
SELECT CONCATENATED_SEGMENTS
INTO supply_demand_source_name
FROM gl_code_combinations_kfv
where CHART_OF_ACCOUNTS_ID = order_sch_wb.PARAMETER_CHART_OF_ACCOUNTS_ID
and CODE_COMBINATION_ID = supply_demand_source_id;
SELECT WIP_ENTITY_NAME
INTO supply_demand_source_name
FROM WIP_ENTITIES
WHERE WIP_ENTITY_ID=supply_demand_source_id;
SELECT CONCATENATED_SEGMENTS
INTO supply_demand_source_name
FROM mtl_generic_dispositions_kfv
WHERE ORGANIZATION_ID = ORGANIZATION_ID
AND DISPOSITION_ID = supply_demand_source_id;
SELECT SHIPMENT_NUM
INTO supply_demand_source_name
FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID=supply_demand_source_id;
SELECT SCHEDULE_DESIGNATOR
INTO supply_demand_source_name
FROM MRP_SCHEDULE_DATES
WHERE MPS_TRANSACTION_ID=supply_demand_source_id
AND SCHEDULE_LEVEL = 2
AND SUPPLY_DEMAND_TYPE = 2;
SELECT SEGMENT1
INTO supply_demand_source_name
FROM PO_REQUISITION_HEADERS
WHERE REQUISITION_HEADER_ID=supply_demand_source_id;
SELECT
master_organization_id
INTO p_master_org_id
FROM oe_system_parameters_all
WHERE NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV
('CLIENT_INFO'),1 ,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
NVL(l_org_id, NVL(TO_NUMBER(DECODE(SUBSTRB
(USERENV('CLIENT_INFO'),1,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99));
select mrp_atp_schedule_temp_s.nextval
into p_session_id
from dual;
PROCEDURE update_constraint_path(p_session_id IN NUMBER,
p_return_error IN OUT NoCopy VARCHAR2) IS
CURSOR get_constr_peg_id IS
select distinct end_pegging_id
from mrp_atp_details_temp
where session_id = p_session_id
and record_type = 3
and constraint_type is not NULL;
msc_sch_wb.atp_debug('l_end_peg_id_list in update_constr ' ||
l_end_peg_id_list);
sql_stmt := 'UPDATE mrp_atp_details_temp
set constrained_path = ' || l_constraint_path_flag ||
' where record_type = ' || l_peg_record_type ||
' and session_id = '||p_session_id||
' and pegging_id in
(select pegging_id
from mrp_atp_details_temp
where record_type = ' || l_peg_record_type ||
' and session_id ='||p_session_id||
' start with session_id = '||p_session_id||
' and record_type = ' || l_peg_record_type ||
' and end_pegging_id in ('||l_end_peg_id_list||') ' ||
' and constraint_type is not null
connect by pegging_id = PRIOR parent_pegging_id
and record_type = ' || l_peg_record_type ||
' and session_id = '||p_session_id||')';
msc_sch_wb.atp_debug('Excp in update_constraint_path '||
Substr(Sqlerrm, 1,100));
END update_constraint_path ;
select
trunc(mast.supply_demand_date - mast.required_date)
from mrp_atp_details_temp mast
where --mast.constraint_type is not NULL
mast.session_id = p_session_id
and mast.record_type = 3
and mast.supply_demand_type = 2
and mast.inventory_item_id = p_inventory_item_id
and mast.organization_id = p_organization_id;
select trunc(mast.supply_demand_date - mast.required_date) date_diff
from mrp_atp_details_temp mast
where --mast.constrained_path is not NULL
mast.session_id = p_session_id
and mast.record_type = 3
and mast.supply_demand_type = 2
and mast.inventory_item_id = p_inventory_item_id
order by date_diff DESC;