[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_atp_insert_rec MRP_ATP_PVT.AtpRec;
l_insert_flag number;
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Insert_Flag:' || to_char(p_atp_record.Insert_Flag) );
l_mat_atp_info_rec.insert_flag := p_atp_record.insert_flag;
l_atp_insert_rec.instance_id := p_atp_record.instance_id;
/*l_atp_insert_rec.inventory_item_id := item_availability_info.family_dest_id(l_item_cntr);
l_atp_insert_rec.request_item_id := item_availability_info.inventory_item_id(l_item_cntr);
l_atp_insert_rec.atf_date := item_availability_info.atf_date(l_item_cntr);
l_atp_insert_rec.atf_date_quantity := l_atf_date_qty;*/
l_atp_insert_rec.inventory_item_id := org_availability_info.family_dest_id(l_process_org_cntr);
l_atp_insert_rec.request_item_id := item_availability_info.inventory_item_id(l_item_cntr);
l_atp_insert_rec.atf_date := org_availability_info.atf_date(l_process_org_cntr);
l_atp_insert_rec.atf_date_quantity := org_availability_info.atf_date_quantity(l_process_org_cntr);
l_atp_insert_rec.requested_date_quantity := org_availability_info.request_date_quantity(l_process_org_cntr);
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_atp_insert_rec.inventory_item_id := ' || l_atp_insert_rec.inventory_item_id);
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_atp_insert_rec.request_item_id := ' || l_atp_insert_rec.request_item_id);
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_atp_insert_rec.atf_date := ' || l_atp_insert_rec.atf_date);
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_atp_insert_rec.atf_date_quantity := ' || l_atp_insert_rec.atf_date_quantity);
l_atp_insert_rec.request_item_id := item_availability_info.inventory_item_id(l_item_cntr);
l_atp_insert_rec.inventory_item_id := item_availability_info.inventory_item_id(l_item_cntr);
l_atp_insert_rec.organization_id := org_availability_info.organization_id(l_process_org_cntr);
l_atp_insert_rec.identifier := p_atp_record.identifier;
l_atp_insert_rec.demand_source_type:=nvl(p_atp_record.demand_source_type, 2);
l_atp_insert_rec.demand_source_header_id :=
nvl(p_atp_record.demand_source_header_id, -1);
l_atp_insert_rec.demand_source_delivery :=
p_atp_record.demand_source_delivery;
l_atp_insert_rec.requested_ship_date :=
org_availability_info.requested_ship_date(l_process_org_cntr);
l_atp_insert_rec.demand_class := l_demand_class;
l_atp_insert_rec.order_number := p_atp_record.order_number;
l_atp_insert_rec.origination_type := 30;
l_atp_insert_rec.demand_source_line := p_atp_record.demand_source_line;
l_atp_insert_rec.quantity_ordered := least(greatest(0, l_requested_date_quantity),
l_net_demand);
l_atp_insert_rec.quantity_ordered);
l_atp_insert_rec.quantity_ordered := p_atp_record.quantity_ordered;
l_atp_insert_rec.refresh_number := p_atp_record.refresh_number;
l_atp_insert_rec.origination_type := 1;
l_atp_insert_rec.origination_type := -200;
l_atp_insert_rec.demand_source_line := null;
l_atp_insert_rec.quantity_ordered := org_availability_info.demand_quantity(l_process_org_cntr);
msc_sch_wb.atp_debug('place partial dmd: ' || l_atp_insert_rec.quantity_ordered);
l_atp_insert_rec.quantity_ordered :=
least(greatest(0, l_requested_date_quantity),
l_PO_qty * org_availability_info.conversion_rate(l_process_org_cntr));
l_atp_insert_rec.refresh_number := p_atp_record.refresh_number; -- For summary enhancement
l_atp_insert_rec.original_item_id := l_inventory_item_id ; --bug 5564075
l_atp_insert_rec.latest_acceptable_date :=p_atp_record.latest_acceptable_date;
l_atp_insert_rec.ship_method := p_atp_record.ship_method;
l_atp_insert_rec.ship_set_name := p_atp_record.ship_set_name; --bug3263368
l_atp_insert_rec.arrival_set_name := p_atp_record.arrival_set_name; --bug3263368
l_atp_insert_rec.Delivery_Lead_Time := p_atp_record.Delivery_Lead_Time; --bug3263368
l_atp_insert_rec.original_request_ship_date := nvl(p_atp_record.original_request_date,
l_atp_insert_rec.requested_ship_date);
MSC_ATP_DB_UTILS.Add_Mat_Demand(l_atp_insert_rec,
l_plan_id,
l_demand_class_flag,
l_demand_id);
SELECT msc_full_pegging_s.nextval
INTO l_pegging_id
FROM dual;
SELECT msc_full_pegging_s.nextval
INTO l_pegging_id
FROM dual;
IF p_atp_record.insert_flag <> 0 THEN
MSC_ATP_DB_UTILS.move_SD_temp_into_mrp_details(l_pegging_id,
org_availability_info.demand_pegging_id(1));
g_atp_record.insert_flag := p_atp_record.insert_flag;
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Top Org, update demand');
MSC_ATP_SUBST.UPDATE_DEMAND(org_availability_info.demand_id(l_process_org_cntr),
org_availability_info.plan_id(l_process_org_cntr),
p_atp_record.quantity_ordered);
--using the same insert rec we prepared earlier
l_atp_insert_rec.quantity_ordered := p_atp_record.quantity_ordered;
l_atp_insert_rec.requested_date_quantity := org_availability_info.request_date_quantity(l_process_org_cntr)
+ nvl(l_stealing_qty, 0);
l_atp_insert_rec.atf_date_quantity := org_availability_info.atf_date_quantity(l_process_org_cntr);
l_atp_insert_rec,
org_availability_info.plan_id(l_process_org_cntr),
org_availability_info.demand_id(l_process_org_cntr),
l_return_status
);
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'second or later org, update demand as there is some stolen qty');
MSC_ATP_SUBST.UPDATE_DEMAND(org_availability_info.demand_id(l_process_org_cntr),
org_availability_info.plan_id(l_process_org_cntr),
(org_availability_info.request_date_quantity(l_process_org_cntr)
+ l_stealing_qty
));
--using the same insert rec we prepared earlier
l_atp_insert_rec.quantity_ordered := org_availability_info.request_date_quantity(l_process_org_cntr);
l_atp_insert_rec.requested_date_quantity := org_availability_info.request_date_quantity(l_process_org_cntr)
+ nvl(l_stealing_qty, 0);
l_atp_insert_rec.atf_date_quantity := org_availability_info.atf_date_quantity(l_process_org_cntr);
l_atp_insert_rec,
org_availability_info.plan_id(l_process_org_cntr),
org_availability_info.demand_id(l_process_org_cntr),
l_return_status
);
Select DECODE(pl.planning_time_fence_flag,
1, trunc(NVL(itm.planning_time_fence_date, l_sysdate)),
l_sysdate),pl.planning_time_fence_flag -- Bug 3578083
into l_ptf_date,l_ptf_enabled -- Bug 3578083
from msc_system_items itm,
msc_plans pl
where itm.plan_id = org_availability_info.plan_id(l_parent_org_cntr)
and itm.sr_instance_id = p_atp_record.instance_id
and itm.organization_id = org_availability_info.organization_id(l_parent_org_cntr)
and itm.sr_inventory_item_id = item_availability_info.sr_inventory_item_id(l_item_cntr)
and pl.plan_id = itm.plan_id
and pl.sr_instance_id = itm.sr_instance_id;
SELECT msc_full_pegging_s.nextval
INTO l_pegging_id
FROM dual;
-- dsting do not update demands for diagnostic atp
IF MSC_ATP_PVT.G_DIAGNOSTIC_ATP <> DIAGNOSTIC_ATP THEN
IF j > 1 AND l_available_quantity <= 0
THEN
--- if we do nto get anything from this org then we remove the demand
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Delete Demand');
MSC_ATP_SUBST.delete_demand_subst(org_availability_info.demand_id(j),
org_availability_info.plan_id(j));
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Update demand');
MSC_ATP_SUBST.UPDATE_DEMAND(org_availability_info.demand_id(j),
org_availability_info.plan_id(j),
-- dsting 2754446
l_available_quantity
* org_availability_info.conversion_rate(j));
--using the same insert rec we prepared earlier
l_atp_insert_rec.quantity_ordered := l_available_quantity * org_availability_info.conversion_rate(j);
l_atp_insert_rec.requested_date_quantity := l_available_quantity * org_availability_info.conversion_rate(j);
l_atp_insert_rec.atf_date_quantity := org_availability_info.atf_date_quantity(j);
l_atp_insert_rec,
org_availability_info.plan_id(l_process_org_cntr),
org_availability_info.demand_id(l_process_org_cntr),
l_return_status
);
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_atp_insert_rec.quantity_ordered :=' || l_atp_insert_rec.quantity_ordered);
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_atp_insert_rec.requested_date_quantity :=' || l_atp_insert_rec.requested_date_quantity);
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_atp_insert_rec.atf_date_quantity :=' || l_atp_insert_rec.atf_date_quantity);
msc_sch_wb.atp_debug('ATP_Check_Subst: Update children quantity in parent org: idx '
|| l_parent_index || ' qty: ' || org_availability_info.quantity_from_children(l_parent_index));
DELETE MSC_DEMANDS
where origination_type = -200
and sr_instance_id = p_atp_record.instance_id
and plan_id = org_availability_info.plan_id(j)
and demand_id = org_availability_info.demand_id(j)
and organization_id = org_availability_info.organization_id(j);
l_insert_flag := 0;
l_insert_flag := p_atp_record.insert_flag;
l_atp_rec.insert_flag := l_insert_flag;
l_insert_flag := 0;
l_insert_flag := p_atp_record.insert_flag;
MSC_ATP_SUBST.UPDATE_DEMAND(item_availability_info.demand_id(i),
item_availability_info.plan_id(i),
item_availability_info.request_date_quantity(i));
--using the same insert rec we prepared earlier
l_atp_insert_rec.quantity_ordered := item_availability_info.request_date_quantity(i);
l_atp_insert_rec.requested_date_quantity := item_availability_info.request_date_quantity(i);
l_atp_insert_rec.atf_date_quantity := item_availability_info.atf_date_quantity(i);
--bug3467631 Inside Forward Scheduling re-initializing otherwise l_atp_insert_rec.inventory_item_id,
--l_atp_insert_rec.request_item_id,l_atp_insert_rec.atf_date will have substitute's values
--even when we are processing member item.
l_atp_insert_rec.inventory_item_id := item_availability_info.family_dest_id(i); --bug3467631
l_atp_insert_rec.request_item_id := item_availability_info.inventory_item_id(i); --bug3467631
l_atp_insert_rec.atf_date := item_availability_info.atf_date(i); --bug3467631
l_atp_insert_rec,
item_availability_info.plan_id(i),
item_availability_info.demand_id(i),
l_return_status
);
l_mat_atp_info_rec.insert_flag := l_insert_flag;
SELECT msc_full_pegging_s.nextval
INTO l_pegging_id
FROM dual;
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Update with pegging info');
l_atp_rec.insert_flag := l_insert_flag;
l_atp_pegging_tab.delete;
l_atp_pegging_tab.delete;
l_atp_pegging_tab.delete;
msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'delete demand for item ' || item_availability_info.item_name(i)
|| ' ' || item_availability_info.sr_inventory_item_id(i));
SELECT TP_ID
INTO l_customer_id
FROM msc_tp_id_lid tp
WHERE tp.SR_TP_ID = p_customer_id
AND tp.SR_INSTANCE_ID = p_instance_id
AND tp.PARTNER_TYPE = 2;
SELECT TP_SITE_ID
INTO l_customer_site_id
FROM msc_tp_site_id_lid tpsite
WHERE tpsite.SR_TP_SITE_ID = p_customer_site_id
AND tpsite.SR_INSTANCE_ID = p_instance_id
AND tpsite.PARTNER_TYPE = 2;
SELECT mis.higher_item_id, mis.partial_fulfillment_flag,
msi1.sr_inventory_item_id, msi1.item_name, mis.highest_item_id,
-- time_phased_atp changes begin
DECODE(msi2.bom_item_type,
5, DECODE(msi2.atp_flag,
'N', msi1.sr_inventory_item_id,
msi2.sr_inventory_item_id),
msi1.sr_inventory_item_id
),
DECODE(msi2.bom_item_type,
5, DECODE(msi2.atp_flag,
'N', msi1.inventory_item_id,
msi2.inventory_item_id),
msi1.inventory_item_id
),
DECODE(msi2.bom_item_type,
5, DECODE(msi2.atp_flag,
'N', msi1.item_name,
msi2.item_name),
msi1.item_name
),
msi2.aggregate_time_fence_date,
0
-- time_phased_atp changes end
BULK COLLECT INTO
p_item_table.inventory_item_id, p_item_table.partial_fulfillment_flag,
p_item_table.sr_inventory_item_id, p_item_table.item_name ,
p_item_table.highest_revision_item_id,
-- time_phased_atp changes begin
p_item_table.family_sr_id,
p_item_table.family_dest_id,
p_item_table.family_item_name,
p_item_table.atf_date,
p_item_table.atf_date_quantity
-- time_phased_atp changes end
FROM msc_item_substitutes mis,
msc_system_items msi1,
msc_system_items msi2
WHERE mis.plan_id = p_plan_id
AND mis.sr_instance_id = p_instance_id
AND mis.lower_item_id = p_inventory_item_id
AND mis.effective_date <= l_request_date
AND NVL(mis.disable_date, l_request_date) >= l_request_date
---bug 2341179 : inferred_flag is used for UI purpose only
--AND mis.inferred_flag = 2
AND NVL(mis.customer_id, -1) = -1
AND NVL(mis.customer_site_id, -1) = -1
AND msi1.inventory_item_id = mis.higher_item_id
AND msi1.sr_instance_id = mis.sr_instance_id
AND msi1.plan_id = mis.plan_id
AND msi1.organization_id = p_organization_id
-- time_phased_atp changes begin
AND msi2.inventory_item_id = DECODE(msi1.product_family_id,
NULL, msi1.inventory_item_id,
-23453, msi1.inventory_item_id,
msi1.product_family_id)
AND msi2.organization_id = msi1.organization_id
AND msi2.sr_instance_id = msi1.sr_instance_id
AND msi2.plan_id = msi1.plan_id
-- time_phased_atp changes end
Order By mis.rank desc;
SELECT count(*)
INTO l_count
FROM msc_item_substitutes mis
WHERE mis.plan_id = p_plan_id
AND mis.sr_instance_id = p_instance_id
---bug 2341179 : inferred_flag is used for UI purpose only
--AND mis.inferred_flag = 2
AND mis.customer_id = l_customer_id
AND mis.customer_site_id = l_customer_site_id;
SELECT mis.higher_item_id, mis.partial_fulfillment_flag,
msi1.sr_inventory_item_id, msi1.item_name, mis.highest_item_id,
mis.effective_date, mis.disable_date,
-- time_phased_atp changes begin
DECODE(msi2.bom_item_type,
5, DECODE(msi2.atp_flag,
'N', msi1.sr_inventory_item_id,
msi2.sr_inventory_item_id),
msi1.sr_inventory_item_id
),
DECODE(msi2.bom_item_type,
5, DECODE(msi2.atp_flag,
'N', msi1.inventory_item_id,
msi2.inventory_item_id),
msi1.inventory_item_id
),
DECODE(msi2.bom_item_type,
5, DECODE(msi2.atp_flag,
'N', msi1.item_name,
msi2.item_name),
msi1.item_name
),
msi2.aggregate_time_fence_date,
0
-- time_phased_atp changes end
BULK COLLECT INTO
--bug 2462949: collect into p_atp_table instead of l_atp_table
p_item_table.inventory_item_id, p_item_table.partial_fulfillment_flag,
p_item_table.sr_inventory_item_id, p_item_table.item_name,
p_item_table.highest_revision_item_id,
l_effective_dates, l_disable_dates,
-- time_phased_atp changes begin
p_item_table.family_sr_id,
p_item_table.family_dest_id,
p_item_table.family_item_name,
p_item_table.atf_date,
p_item_table.atf_date_quantity
-- time_phased_atp changes end
FROM msc_item_substitutes mis,
msc_system_items msi1,
msc_system_items msi2
WHERE mis.plan_id = p_plan_id
AND mis.sr_instance_id = p_instance_id
AND mis.lower_item_id = p_inventory_item_id
AND mis.effective_date <= l_request_date
AND NVL(mis.disable_date, l_request_date) >= l_request_date
---bug 2341179 : inferred_flag is used for UI purpose only
--AND mis.inferred_flag = 2
AND mis.customer_id = l_customer_id
AND mis.customer_site_id = l_customer_site_id
AND msi1.inventory_item_id = mis.higher_item_id
AND msi1.sr_instance_id = mis.sr_instance_id
AND msi1.plan_id = mis.plan_id
AND msi1.organization_id = p_organization_id
-- time_phased_atp changes begin
AND msi2.inventory_item_id = DECODE(msi1.product_family_id,
NULL, msi1.inventory_item_id,
-23453, msi1.inventory_item_id,
msi1.product_family_id)
AND msi2.organization_id = msi1.organization_id
AND msi2.sr_instance_id = msi1.sr_instance_id
AND msi2.plan_id = msi1.plan_id
-- time_phased_atp changes end
Order By mis.effective_date, mis.rank desc;
SELECT mis.higher_item_id, mis.partial_fulfillment_flag,
msi1.sr_inventory_item_id, msi1.item_name, mis.highest_item_id
-- dsting diagnostic ATP
,msi1.rounding_control_type
,msi1.unit_weight
,msi1.unit_volume
,msi1.weight_uom
,msi1.volume_uom,
-- time_phased_atp changes begin
DECODE(msi2.bom_item_type,
5, DECODE(msi2.atp_flag,
'N', msi1.sr_inventory_item_id,
msi2.sr_inventory_item_id),
msi1.sr_inventory_item_id
),
DECODE(msi2.bom_item_type,
5, DECODE(msi2.atp_flag,
'N', msi1.inventory_item_id,
msi2.inventory_item_id),
msi1.inventory_item_id
),
DECODE(msi2.bom_item_type,
5, DECODE(msi2.atp_flag,
'N', msi1.item_name,
msi2.item_name),
msi1.item_name
),
msi2.aggregate_time_fence_date,
0
-- time_phased_atp changes end
BULK COLLECT INTO
p_item_table.inventory_item_id, p_item_table.partial_fulfillment_flag,
p_item_table.sr_inventory_item_id, p_item_table.item_name, p_item_table.highest_revision_item_id
--p_item_table.inventory_item_id, l_item_name
-- dsting diagnostic ATP
,p_item_table.rounding_control_type
,p_item_table.unit_weight
,p_item_table.unit_volume
,p_item_table.weight_uom
,p_item_table.volume_uom
-- For time_phased_atp
,p_item_table.family_sr_id
,p_item_table.family_dest_id
,p_item_table.family_item_name
,p_item_table.atf_date
,p_item_table.atf_date_quantity
FROM msc_item_substitutes mis,
msc_system_items msi1,
msc_system_items msi2
WHERE mis.plan_id = p_plan_id
AND mis.sr_instance_id = p_instance_id
AND mis.lower_item_id = p_inventory_item_id
AND mis.effective_date <= l_request_date
AND NVL(mis.disable_date, l_request_date) >= l_request_date
---- bug 2341179 : : inferred_flag is used for UI purpose only
--AND mis.inferred_flag = 2
AND NVL(mis.customer_id, -1) = -1
AND NVL(mis.customer_site_id, -1) = -1
AND msi1.inventory_item_id = mis.higher_item_id
AND msi1.sr_instance_id = mis.sr_instance_id
AND msi1.plan_id = mis.plan_id
AND msi1.organization_id = p_organization_id
-- time_phased_atp changes begin
AND msi2.inventory_item_id = DECODE(msi1.product_family_id,
NULL, msi1.inventory_item_id,
-23453, msi1.inventory_item_id,
msi1.product_family_id)
AND msi2.organization_id = msi1.organization_id
AND msi2.sr_instance_id = msi1.sr_instance_id
AND msi2.plan_id = msi1.plan_id
-- time_phased_atp changes end
Order By mis.rank desc;
Procedure Update_demand(p_demand_id number,
p_plan_id number,
p_quantity number)
IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('BEGIN Update_demand ');
msc_sch_wb.atp_debug('Update_demand: ' || 'p_demand_id := ' || p_demand_id);
msc_sch_wb.atp_debug('Update_demand: ' || 'p_plan_id := ' || p_plan_id);
msc_sch_wb.atp_debug('Update_demand: ' || 'p_quantity := ' || p_quantity);
update msc_demands
set using_requirement_quantity = p_quantity
where demand_id = p_demand_id
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('Update_demand: ' || 'Number of Rows Updated := ' || SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Update_demand: ' || 'Update preallocated demand');
update msc_alloc_demands
set allocated_quantity = p_quantity
where parent_demand_id = p_demand_id
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('Update_demand: ' || 'Number of Rows Updated := ' || SQL%ROWCOUNT);
END Update_demand;
Procedure Delete_demand_subst(p_demand_id number,
p_plan_id number)
IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Delete_demand_subst: ' || 'BEGIN delete demand subst ');
msc_sch_wb.atp_debug('Delete_demand_subst: ' || 'p_demand_id := ' || p_demand_id);
msc_sch_wb.atp_debug('Delete_demand_subst: ' || 'p_plan_id := ' || p_plan_id);
delete msc_demands
where demand_id = p_demand_id
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('Delete_demand_subst: ' || 'Number of Rows deleted := ' || SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Delete_demand_subst: ' || 'Delete Allocated demand');
delete msc_alloc_demands
where parent_demand_id = p_demand_id
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('Delete_demand_subst: ' || 'Number of Rows deleted := ' || SQL%ROWCOUNT);
END Delete_demand_subst;
INSERT into mrp_atp_details_temp
(session_id,
order_line_id,
pegging_id,
parent_pegging_id,
atp_level,
record_type,
organization_id,
organization_code,
identifier1,
identifier2,
identifier3,
inventory_item_id,
inventory_item_name,
resource_id,
resource_code,
department_id,
department_code,
supplier_id,
supplier_name,
supplier_site_id,
supplier_site_name,
scenario_id,
source_type,
supply_demand_source_type,
supply_demand_quantity,
supply_demand_type,
supply_demand_date,
end_pegging_id,
constraint_flag,
allocated_quantity, -- 1527660
number1,
char1,
component_identifier,
-- resource batching
batchable_flag,
supplier_atp_date,
dest_inv_item_id,
summary_flag,
--- bug 2152184: For PF based ATP inventory_item_id field contains id for PF item
--- cto looks at pegging tree to place their demands. Since CTO expects to find
-- id for the requested item, we add the following column. CTO will now read from this column
request_item_id,
--- if req-date < ptf date then we update this column with PTF date
ptf_date
-- dsting
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
--diag_atp
,FIXED_LEAD_TIME,
VARIABLE_LEAD_TIME,
PREPROCESSING_LEAD_TIME,
PROCESSING_LEAD_TIME,
POSTPROCESSING_LEAD_TIME,
INTRANSIT_LEAD_TIME,
ATP_RULE_ID,
ALLOCATION_RULE,
INFINITE_TIME_FENCE,
SUBSTITUTION_WINDOW,
REQUIRED_QUANTITY,
ROUNDING_CONTROL,
ATP_FLAG,
ATP_COMPONENT_FLAG,
REQUIRED_DATE,
OPERATION_SEQUENCE_ID,
SOURCING_RULE_NAME,
OFFSET,
EFFICIENCY,
OWNING_DEPARTMENT,
REVERSE_CUM_YIELD,
BASIS_TYPE,
USAGE,
CONSTRAINT_TYPE,
CONSTRAINT_DATE,
CRITICAL_PATH,
PEGGING_TYPE,
UTILIZATION,
ATP_RULE_NAME,
PLAN_NAME,
CONSTRAINED_PATH,
weight_capacity,
volume_capacity,
weight_uom,
volume_uom,
ship_method,
aggregate_time_fence_date, --bug3467631 added so that deletion from alloc
--tables may take place
shipping_cal_code, -- Bug 3826234
receiving_cal_code, -- Bug 3826234
intransit_cal_code, -- Bug 3826234
manufacturing_cal_code -- Bug 3826234
)
VALUES
(p_pegging_rec.session_id,
p_pegging_rec.order_line_id,
p_pegging_rec.pegging_id,
p_pegging_rec.parent_pegging_id,
p_pegging_rec.atp_level,
3,
p_pegging_rec.organization_id,
p_pegging_rec.organization_code,
p_pegging_rec.identifier1,
p_pegging_rec.identifier2,
p_pegging_rec.identifier3,
p_pegging_rec.inventory_item_id,
p_pegging_rec.inventory_item_name,
p_pegging_rec.resource_id,
p_pegging_rec.resource_code,
p_pegging_rec.department_id,
p_pegging_rec.department_code,
p_pegging_rec.supplier_id,
p_pegging_rec.supplier_name,
p_pegging_rec.supplier_site_id,
p_pegging_rec.supplier_site_name,
p_pegging_rec.scenario_id,
p_pegging_rec.source_type,
p_pegging_rec.supply_demand_source_type,
p_pegging_rec.supply_demand_quantity,
p_pegging_rec.supply_demand_type,
p_pegging_rec.supply_demand_date,
--NVL(MSC_ATP_PVT.G_DEMAND_PEGGING_ID, msc_full_pegging_s.currval),
p_pegging_rec.end_pegging_id,
p_pegging_rec.constraint_flag,
p_pegging_rec.allocated_quantity, -- 1527660
p_pegging_rec.number1,
p_pegging_rec.char1,
p_pegging_rec.component_identifier,
p_pegging_rec.batchable_flag,
p_pegging_rec.supplier_atp_date,
p_pegging_rec.dest_inv_item_id,
p_pegging_rec.summary_flag,
p_pegging_rec.request_item_id,
p_pegging_rec.ptf_date
-- dsting
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.USER_ID
,p_pegging_rec.FIXED_LEAD_TIME,
p_pegging_rec.VARIABLE_LEAD_TIME,
p_pegging_rec.PREPROCESSING_LEAD_TIME,
p_pegging_rec.PROCESSING_LEAD_TIME,
p_pegging_rec.POSTPROCESSING_LEAD_TIME,
p_pegging_rec.INTRANSIT_LEAD_TIME,
p_pegging_rec.ATP_RULE_ID,
p_pegging_rec.ALLOCATION_RULE,
p_pegging_rec.INFINITE_TIME_FENCE,
p_pegging_rec.SUBSTITUTION_WINDOW,
p_pegging_rec.REQUIRED_QUANTITY,
p_pegging_rec.ROUNDING_CONTROL,
p_pegging_rec.ATP_FLAG,
p_pegging_rec.ATP_COMPONENT_FLAG,
-- p_pegging_rec.REQUIRED_DATE,
-- Bug 2748730. Move the required_date to day end only when the pegging is for demand line
-- This is applicable irrespective of whether the line is overridden or not
DECODE(p_pegging_rec.supply_demand_type,
1, TRUNC(p_pegging_rec.REQUIRED_DATE) + MSC_ATP_PVT.G_END_OF_DAY,
p_pegging_rec.REQUIRED_DATE),
p_pegging_rec.OPERATION_SEQUENCE_ID,
p_pegging_rec.SOURCING_RULE_NAME,
p_pegging_rec.OFFSET,
p_pegging_rec.EFFICIENCY,
p_pegging_rec.OWNING_DEPARTMENT,
p_pegging_rec.REVERSE_CUM_YIELD,
p_pegging_rec.BASIS_TYPE,
p_pegging_rec.USAGE,
p_pegging_rec.CONSTRAINT_TYPE,
p_pegging_rec.CONSTRAINT_DATE,
p_pegging_rec.CRITICAL_PATH,
p_pegging_rec.PEGGING_TYPE,
p_pegging_rec.UTILIZATION,
p_pegging_rec.ATP_RULE_NAME,
p_pegging_rec.PLAN_NAME,
p_pegging_rec.CONSTRAINED_PATH,
p_pegging_rec.weight_capacity,
p_pegging_rec.volume_capacity,
p_pegging_rec.weight_uom,
p_pegging_rec.volume_uom,
p_pegging_rec.ship_method,
p_pegging_rec.aggregate_time_fence_date, --bug3467631
p_pegging_rec.shipping_cal_code, -- Bug 3826234
p_pegging_rec.receiving_cal_code, -- Bug 3826234
p_pegging_rec.intransit_cal_code, -- Bug 3826234
p_pegging_rec.manufacturing_cal_code -- Bug 3826234
);
to be deleted after code review
PROCEDURE Add_Mat_Demand(
p_atp_rec IN MRP_ATP_PVT.AtpRec ,
p_plan_id IN NUMBER ,
p_dc_flag IN NUMBER,
x_demand_id OUT NoCopy NUMBER
)
IS
l_sqlfound BOOLEAN := FALSE;
msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'before insert into msc_demands');
INSERT INTO MSC_DEMANDS(
DEMAND_ID,
USING_REQUIREMENT_QUANTITY,
USING_ASSEMBLY_DEMAND_DATE,
DEMAND_TYPE,
ORIGINATION_TYPE,
USING_ASSEMBLY_ITEM_ID,
PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SALES_ORDER_LINE_ID,
SR_INSTANCE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DEMAND_CLASS,
REFRESH_NUMBER,
ORDER_NUMBER,
APPLIED,
STATUS,
CUSTOMER_ID,
SHIP_TO_SITE_ID,
original_item_id,
record_source) -- For plan order pegging
---STOLEN_FLAG) -- 02/16: Stealing
VALUES(
msc_demands_s.nextval,
p_atp_rec.quantity_ordered,
TRUNC(p_atp_rec.requested_ship_date) + MSC_ATP_PVT.G_END_OF_DAY , -- For bug 2259824
1, -- discrete demand
p_atp_rec.origination_type,
p_atp_rec.inventory_item_id,
p_plan_id,
p_atp_rec.organization_id,
p_atp_rec.inventory_item_id,
p_atp_rec.demand_source_line,
p_atp_rec.instance_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
p_atp_rec.demand_class,
p_atp_rec.refresh_number,
-- Modified by ngoel on 1/12/2001 for origination_type = 30
decode(p_atp_rec.origination_type, 6, p_atp_rec.order_number,
30, p_atp_rec.order_number,
null),
decode(p_atp_rec.origination_type, 6, 2, 30, 2, null),
decode(p_atp_rec.origination_type, 6, 0, 30, 0, null),
MSC_ATP_PVT.G_PARTNER_ID,
MSC_ATP_PVT.G_PARTNER_SITE_ID,
p_atp_rec.request_item_id,
l_record_source) -- For plan order pegging
--1657855, remove support for min allocation
---p_atp_rec.stolen_flag)
RETURNING DEMAND_ID INTO x_demand_id;
msc_sch_wb.atp_debug('Add_Mat_Demand: ' || 'Numbe of rows inserted := ' || SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Add_Mat_Demand: before insert into'||
' msc_alloc_demands');
INSERT INTO MSC_ALLOC_DEMANDS(
PLAN_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
DEMAND_CLASS,
DEMAND_DATE,
PARENT_DEMAND_ID,
ALLOCATED_QUANTITY,
ORIGINATION_TYPE,
ORDER_NUMBER,
SALES_ORDER_LINE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES (
p_plan_id,
p_atp_rec.inventory_item_id,
p_atp_rec.organization_id,
p_atp_rec.instance_id,
p_atp_rec.demand_class,
p_atp_rec.requested_ship_date, -- QUESTION arrival items ?
x_demand_id,
p_atp_rec.quantity_ordered,
p_atp_rec.origination_type,
p_atp_rec.order_number,
p_atp_rec.demand_source_line,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate);