[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure Update_Line_Item_Properties_WS(p_session_id IN NUMBER);
'DELETE FROM MRP_ATP_DETAILS_TEMP'||l_dynstring||
' WHERE session_id = :x_session_id '||
' and order_line_id in ( '||
' select order_line_id from mrp_atp_schedule_temp'||l_dynstring||
' where session_id = :x_session_id_1 '||
' and status_flag = 1) '||
' and record_type <> 3';
msc_sch_wb.atp_debug('put_into_temp_table: ' || 'delete details temp rows := '|| SQL%ROWCOUNT);
msc_sch_wb.atp_debug('put_into_temp_table: ' || ' Inserted supply demand records ');
msc_sch_wb.atp_debug('put_into_temp_table: ' || ' Inserted period records ');
msc_sch_wb.atp_debug('put_into_temp_table: ' || 'Inserted Pegging Records');
msc_sch_wb.atp_debug('put_into_temp_table: ' || ' Inserted main records ');
Delete mrp_atp_schedule_temp
where session_id = x_session_id
and status_flag in (1, 2, 99);
delete mrp_atp_details_temp
where session_id = x_session_id;
SELECT
ACTION
,CALLING_MODULE
,ORDER_HEADER_ID
,ORDER_LINE_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,SR_INSTANCE_ID
,ORDER_NUMBER
,SOURCE_ORGANIZATION_ID
,CUSTOMER_ID
,CUSTOMER_SITE_ID
,DESTINATION_TIME_ZONE
,QUANTITY_ORDERED
,UOM_CODE
,REQUESTED_SHIP_DATE
,REQUESTED_ARRIVAL_DATE
,LATEST_ACCEPTABLE_DATE
,DELIVERY_LEAD_TIME
,FREIGHT_CARRIER
,SHIP_METHOD
,DEMAND_CLASS
,SHIP_SET_NAME
,ARRIVAL_SET_NAME
,OVERRIDE_FLAG
,SCHEDULED_SHIP_DATE
-- rajjain 02/21/2003 Bug 2815484
,SCHEDULED_ARRIVAL_DATE
,AVAILABLE_QUANTITY
,REQUESTED_DATE_QUANTITY
,GROUP_SHIP_DATE
,GROUP_ARRIVAL_DATE
,VENDOR_ID
,VENDOR_SITE_ID
,INSERT_FLAG
,ERROR_CODE
,INVENTORY_ITEM_NAME
,SOURCE_ORGANIZATION_CODE
,SCENARIO_ID
,VENDOR_NAME
,VENDOR_SITE_NAME
,MDI_ROWID
,DEMAND_SOURCE_TYPE
,DEMAND_SOURCE_DELIVERY
/* --bug 4078703: always pass atp_lead_time back to OM
,DECODE(MSC_ATP_PVT.G_INV_CTP, 5,
Decode(order_line_id, ato_model_line_id,
decode(bom_item_type, 1,
(fixed_lt + (variable_lt * QUANTITY_ORDERED)) * (1 + l_mso_lead_time_factor), 0), 0), 0)
*/
,atp_lead_time
,OE_FLAG
,END_PEGGING_ID
,OLD_SOURCE_ORGANIZATION_ID
,OLD_DEMAND_CLASS
--,ATTRIBUTE_06
,SUBSTITUTION_TYP_CODE
,REQ_ITEM_DETAIL_FLAG
,OLD_INVENTORY_ITEM_ID
,REQUEST_ITEM_ID
,REQUEST_ITEM_NAME
,REQ_ITEM_AVAILABLE_DATE
,REQ_ITEM_AVAILABLE_DATE_QTY
,REQ_ITEM_REQ_DATE_QTY
,SALES_REP
,CUSTOMER_CONTACT
,SUBST_FLAG
,diagnostic_atp_flag
---columns for CTO project
,Top_Model_line_id,
ATO_Parent_Model_Line_Id,
ATO_Model_Line_Id,
Parent_line_id,
match_item_id,
Config_item_line_id,
Validation_Org,
Component_Sequence_ID,
Component_Code,
line_number,
included_item_flag,
atp_flag,
atp_components_flag,
bom_item_type,
pick_components_flag,
OSS_ERROR_CODE,
sequence_number,
original_request_date,
--bug 3508529: add extra coumns
--bug 3508529: add columns that are present in atp rec type
null, --earliest_acceptable_date,
error_message, --message,
null, --ato_delete_flag,
null, --attribute_01,
null, --attribute_03,
null, --attribute_04,
null, --attribute_05,
compile_designator, --attribute_07,
null, --attribute_08,
null, --attribute_09,
null, --attribute_10,
customer_name, --customer_name,
null, --customer_class,
customer_location, --customer_location,
customer_country, --null, --customer_country, 2814895
customer_state, --null, --customer_state, 2814895
customer_city, --null, --customer_city, 2814895
customer_postal_code, --null, --customer_postal_code, 2814895
atp_flag, --atp_flag,
wip_supply_type, --wip_supply_type,
mandatory_item_flag, --mandatory_item_flag,
null, --base_model_id,
matched_item_name, --matched_item_name,
cascade_model_info_to_comp, --cascade_model_info_to_comp,
firm_flag, --firm_flag,
order_line_number, --order_line_number,
option_number, --option_number,
shipment_number, --shipment_number,
item_desc, --item_desc,
old_line_schedule_date, --old_line_schedule_date,
old_source_organization_code, --old_source_organization_code,
firm_source_org_id, --firm_source_org_id,
firm_source_org_code, --firm_source_org_code,
firm_ship_date, --firm_ship_date,
firm_arrival_date, --firm_arrival_date,
ship_method_text, --ship_method_text,
ship_set_id, --ship_set_id,
arrival_set_id, --arrival_set_id,
project_id, --project_id,
task_id, --task_id,
null, --project_number,
null, --task_number
null, --attribute_11,
null, --attribute_12,
null, --attribute_13,
null, --attribute_14,
null, --attribute_15,
null, --attribute_16,
null, --attribute_17,
null, --attribute_18,
null, --attribute_19,
null, --attribute_20,
null, --attribute_21,
null, --attribute_22,
null, --attribute_23,
null, --attribute_24,
null, --attribute_25,
null, --attribute_26,
null, --attribute_27,
null, --attribute_28,
null, --attribute_29,
null, --attribute_30,
null, --atf_date,
plan_id, --plan_id,
null, --receiving_cal_code,
null, --intransit_cal_code,
null, --shipping_cal_code,
null, --manufacturing_cal_code
--end bug 3508529: add all columns available in atp_rec_type
internal_org_id, -- Bug 3449812
first_valid_ship_arrival_date, --bug 3328421
party_site_id, --2814895
part_of_set --4500382
BULK COLLECT INTO
x_atp_rec.action,
x_atp_rec.calling_module,
x_atp_rec.Demand_Source_Header_Id,
x_atp_rec.identifier,
x_atp_rec.inventory_item_id,
x_atp_rec.organization_id,
x_atp_rec.instance_id,
x_atp_rec.order_number,
x_atp_rec.source_organization_id,
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.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.Ship_Date,
-- rajjain 02/21/2003 Bug 2815484
x_atp_rec.Arrival_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_site_id,
x_atp_rec.insert_flag,
x_atp_rec.error_code,
x_atp_rec.Inventory_Item_Name,
x_atp_rec.Source_Organization_Code,
x_atp_rec.Scenario_Id,
x_atp_rec.vendor_name,
x_atp_rec.vendor_site_name,
x_atp_rec.row_id,
x_atp_rec.Demand_Source_Type,
x_atp_rec.demand_source_delivery,
x_atp_rec.atp_lead_time,
x_atp_rec.oe_flag,
x_atp_rec.end_pegging_id,
x_atp_rec.old_source_organization_id,
x_atp_rec.old_demand_class,
--x_atp_rec.attribute_06,
x_atp_rec.substitution_typ_code,
x_atp_rec.req_item_detail_flag,
x_atp_rec.old_inventory_item_id,
x_atp_rec.request_item_id,
x_atp_rec.request_item_name,
x_atp_rec.req_item_available_date,
x_atp_rec.req_item_available_date_qty,
x_atp_rec.req_item_req_date_qty,
x_atp_rec.sales_rep,
x_atp_rec.customer_contact,
x_atp_rec.subst_flag,
x_atp_rec.attribute_02,
---columns for CTO project
x_atp_rec.Top_Model_line_id,
x_atp_rec.ATO_Parent_Model_Line_Id,
x_atp_rec.ATO_Model_Line_Id,
x_atp_rec.Parent_line_id,
x_atp_rec.match_item_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,
x_atp_rec.attribute_06,
x_atp_rec.atp_components_flag,
x_atp_rec.bom_item_type,
x_atp_rec.pick_components_flag,
x_atp_rec.OSS_ERROR_CODE,
x_atp_rec.sequence_number,
x_atp_rec.original_request_date,
--bug 3508529: add columns that are present in atp rec type
x_atp_rec.earliest_acceptable_date,
x_atp_rec.message,
x_atp_rec.ato_delete_flag,
x_atp_rec.attribute_01,
x_atp_rec.attribute_03,
x_atp_rec.attribute_04,
x_atp_rec.attribute_05,
x_atp_rec.attribute_07,
x_atp_rec.attribute_08,
x_atp_rec.attribute_09,
x_atp_rec.attribute_10,
x_atp_rec.customer_name,
x_atp_rec.customer_class,
x_atp_rec.customer_location,
x_atp_rec.customer_country,
x_atp_rec.customer_state,
x_atp_rec.customer_city,
x_atp_rec.customer_postal_code,
x_atp_rec.atp_flag,
x_atp_rec.wip_supply_type,
x_atp_rec.mandatory_item_flag,
x_atp_rec.base_model_id,
x_atp_rec.matched_item_name,
x_atp_rec.cascade_model_info_to_comp,
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.attribute_11,
x_atp_rec.attribute_12,
x_atp_rec.attribute_13,
x_atp_rec.attribute_14,
x_atp_rec.attribute_15,
x_atp_rec.attribute_16,
x_atp_rec.attribute_17,
x_atp_rec.attribute_18,
x_atp_rec.attribute_19,
x_atp_rec.attribute_20,
x_atp_rec.attribute_21,
x_atp_rec.attribute_22,
x_atp_rec.attribute_23,
x_atp_rec.attribute_24,
x_atp_rec.attribute_25,
x_atp_rec.attribute_26,
x_atp_rec.attribute_27,
x_atp_rec.attribute_28,
x_atp_rec.attribute_29,
x_atp_rec.attribute_30,
x_atp_rec.atf_date,
x_atp_rec.plan_id,
x_atp_rec.receiving_cal_code,
x_atp_rec.intransit_cal_code,
x_atp_rec.shipping_cal_code,
x_atp_rec.manufacturing_cal_code,
x_atp_rec.internal_org_id, -- Bug 3449812
x_atp_rec.first_valid_ship_arrival_date, --bug 3328421
x_atp_rec.party_site_id, --2814895
x_atp_rec.part_of_set --4500382
FROM mrp_atp_schedule_temp
WHERE session_id = x_session_id
AND status_flag = l_status_flag
AND NVL(mandatory_item_flag, 2) = 2
AND ORDER_LINE_ID = DECODE( x_mode, MSC_ATP_UTILS.RESULTS_MODE, ORDER_LINE_ID,
NVL(ATO_Model_Line_Id, ORDER_LINE_ID))
ORDER BY sequence_number;
'INSERT INTO MSC_REGIONS_TEMP(
session_id,
partner_site_id,
region_id,
region_type,
zone_flag,
partner_type
)
(SELECT
session_id,
partner_site_id,
region_id,
region_type,
zone_flag,
partner_type
FROM msc_regions_temp' || l_dynstring || '
WHERE session_id = :x_session_id)';
msc_sch_wb.atp_debug('Rows inserted in msc_regions_temp:'|| sql%rowcount);
'SELECT
ACTION
,CALLING_MODULE
,SESSION_ID
,ORDER_HEADER_ID
,ORDER_LINE_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,SR_INSTANCE_ID
,ORGANIZATION_CODE
,ORDER_NUMBER
,SOURCE_ORGANIZATION_ID
,CUSTOMER_ID
,CUSTOMER_SITE_ID
,DESTINATION_TIME_ZONE
,QUANTITY_ORDERED
,UOM_CODE
,REQUESTED_SHIP_DATE
,REQUESTED_ARRIVAL_DATE
,LATEST_ACCEPTABLE_DATE
,DELIVERY_LEAD_TIME
,FREIGHT_CARRIER
,SHIP_METHOD
,DEMAND_CLASS
,SHIP_SET_NAME
,SHIP_SET_ID
,ARRIVAL_SET_NAME
,ARRIVAL_SET_ID
,OVERRIDE_FLAG
,SCHEDULED_SHIP_DATE
,SCHEDULED_ARRIVAL_DATE
,AVAILABLE_QUANTITY
,REQUESTED_DATE_QUANTITY
,GROUP_SHIP_DATE
,GROUP_ARRIVAL_DATE
,VENDOR_ID
,VENDOR_SITE_ID
,INSERT_FLAG
,ERROR_CODE
,ERROR_MESSAGE
,SEQUENCE_NUMBER
,FIRM_FLAG
,INVENTORY_ITEM_NAME
,SOURCE_ORGANIZATION_CODE
,INSTANCE_ID1
,ORDER_LINE_NUMBER
,SHIPMENT_NUMBER
,OPTION_NUMBER
,PROMISE_DATE
,CUSTOMER_NAME
,CUSTOMER_LOCATION
,OLD_LINE_SCHEDULE_DATE
,OLD_SOURCE_ORGANIZATION_CODE
,SCENARIO_ID
,VENDOR_NAME
,VENDOR_SITE_NAME
,STATUS_FLAG
,MDI_ROWID
,DEMAND_SOURCE_TYPE
,DEMAND_SOURCE_DELIVERY
,ATP_LEAD_TIME
,OE_FLAG
,ITEM_DESC
,INTRANSIT_LEAD_TIME
,SHIP_METHOD_TEXT
,END_PEGGING_ID
,PROJECT_ID
,TASK_ID
,PROJECT_NUMBER
,TASK_NUMBER
,OLD_SOURCE_ORGANIZATION_ID
,OLD_DEMAND_CLASS
,EXCEPTION1
,EXCEPTION2
,EXCEPTION3
,EXCEPTION4
,EXCEPTION5
,EXCEPTION6
,EXCEPTION7
,EXCEPTION8
,EXCEPTION9
,EXCEPTION10
,EXCEPTION11
,EXCEPTION12
,EXCEPTION13
,EXCEPTION14
,EXCEPTION15
,ATTRIBUTE_06
,SUBSTITUTION_TYP_CODE
,REQ_ITEM_DETAIL_FLAG
,OLD_INVENTORY_ITEM_ID
,REQUEST_ITEM_ID
,REQUEST_ITEM_NAME
,REQ_ITEM_AVAILABLE_DATE
,REQ_ITEM_AVAILABLE_DATE_QTY
,REQ_ITEM_REQ_DATE_QTY
,SALES_REP
,CUSTOMER_CONTACT
,SUBST_FLAG ' ;
x_atp_rec.insert_flag(i) := sched_rec.insert_flag;
p_insert_flag NUMBER,
p_partial_flag NUMBER,
p_err_message IN OUT NoCopy VARCHAR2)
RETURN NUMBER is
v_dummy NUMBER := 0;
SELECT NVL(count(*),0)
INTO v_dummy
FROM mtl_demand_interface mdi3,
mtl_demand_interface mdi
WHERE mdi3.demand_source_header_id = mdi.demand_source_header_id
AND mdi3.demand_source_line = mdi.demand_source_line
AND mdi3.demand_source_delivery = mdi.demand_source_delivery
AND mdi3.demand_source_type = mdi.demand_source_type
AND mdi3.schedule_group_id = mdi.schedule_group_id
AND mdi3.atp_group_id <> mdi.atp_group_id
AND mdi3.transaction_process_order < mdi.transaction_process_order
AND mdi.atp_group_id = p_group_id;
SELECT
rowidTochar(mdi.ROWID) row_id, /* unique identifier */
mdi.inventory_item_id,
mdi.organization_id, /* source organization id */
NVL(mdi.demand_source_line,-1), /* identifier */
NVL(mdi.demand_source_header_id, -1),
NVL(mdi.demand_source_type, -1),
mdi.Demand_Source_Delivery,
mdi.atp_lead_time,
-- NULL, /* scenario id */
NULL, /* calling module - not used */
NULL, /* customer_id - Not needed since source org is known*/
NULL, /* customer site id */
NULL, /* dest time zone */
nvl(mdi.primary_uom_quantity, mdi.line_item_quantity), /* quantity */
nvl(msi.primary_uom_code, mdi.line_item_uom), /* UOM */
mdi.requirement_date request_date, /* requirement_date */
NULL, /* requested arrival date */
mdi.latest_acceptable_date, /* Latest_Acceptable_Date */
NULL, /* Delivery_Lead_Time */
NULL, /* Freight_Carrier */
NULL, /* Ship_Method */
mdi.demand_class, /* Demand_Class */
Decode(p_partial_flag,0,'Ship Set',NULL), /* Ship_Set_Name */
NULL, /* Arrival_Set_Name */
NULL, /* Override_Flag */
Nvl(mdi.action_code,100), /*ATP action code - eg.ATP inquiry,demand */
NULL, /* Ship_Date */
NULL, /* Available_Quantity */
NULL, /* Requested_Date_Quantity */
NULL, /* Group_Available_Date */
NULL, /* Group_Arrival_Date */
NULL, /* Vendor_Id */
NULL, /* Vendor_Site_Id */
p_insert_flag, /* Insert_Flag */
NULL, /* Error_Code */
NULL /* Message */
bulk collect INTO
x_atp_rec.ROW_ID,
x_atp_rec.inventory_item_id,
x_atp_rec.source_organization_id,
x_atp_rec.identifier,
x_atp_rec.Demand_Source_Header_Id,
x_atp_rec.Demand_Source_Type,
x_atp_rec.Demand_Source_Delivery,
x_atp_rec.atp_lead_time,
-- x_atp_rec.scenario_id,
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.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_site_id,
x_atp_rec.insert_flag,
x_atp_rec.error_code,
x_atp_rec.message
FROM
mrp_ap_apps_instances mai,
ORG_ORGANIZATION_DEFINITIONS ood,
MTL_SYSTEM_ITEMS msi,
MTL_DEMAND_INTERFACE mdi
WHERE mdi.atp_group_id = p_group_id
AND ((mdi.demand_type = 1
AND EXISTS (SELECT 'ATO Model exists'
FROM mtl_demand_interface
WHERE atp_group_id = mdi.atp_group_id
AND demand_type = 1))
OR NOT EXISTS (SELECT 'ATO Model exists'
FROM mtl_demand_interface
WHERE atp_group_id = mdi.atp_group_id
AND demand_type = 1))
AND Nvl(mdi.process_flag,1) = 1
AND nvl(mdi.error_code,61) = 61
AND ood.organization_id = mdi.organization_id
AND msi.organization_id = mdi.organization_id
AND msi.inventory_item_id = mdi.inventory_item_id;
msc_sch_wb.atp_debug('Call_ATP_11: ' || ' 9 '||x_atp_rec.Insert_Flag(1));
UPDATE mtl_demand_interface set
LAST_UPDATE_DATE = SYSDATE,
Error_Code = X_atp_rec_out.error_code(j),
Err_Explanation = X_atp_rec_out.message(j)
WHERE rowid = Chartorowid(X_atp_rec_out.row_id(j));
-- ?? Infinite_time_fence_date is being updated in inlatp.ppc
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Call_ATP_11: ' || 'christine, request date atp q is'||x_Atp_rec_out.requested_date_quantity(j));
UPDATE mtl_demand_interface
SET Request_Date_ATP_Quantity = x_Atp_rec_out.requested_date_quantity(j),
Request_ATP_Date = To_date(To_char(X_atp_rec_out.ship_date(j), 'J'),'J'),
Request_ATP_Date_Quantity = X_atp_rec_out.available_quantity(j),
Group_Available_Date = NVL(X_atp_rec_out.group_ship_date(j),
requirement_date),
Error_Code = X_atp_rec_out.error_code(j),
Err_Explanation = X_atp_rec_out.message(j)
WHERE rowid = Chartorowid(X_atp_rec_out.row_id(j));
INSERT INTO MTL_SUPPLY_DEMAND_TEMP
(
RECORD_TYPE,
SUPPLY_DEMAND_SOURCE_TYPE,
ON_HAND_QUANTITY,
QUANTITY,
DISPOSITION_TYPE,
DISPOSITION_ID,
SUPPLY_DEMAND_TYPE,
REQUIREMENT_DATE,
SEQ_NUM,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
C_COLUMN1,
C_COLUMN8)
VALUES(
'SD',
x_atp_supply_demand.supply_demand_source_type(j),
NULL, -- New ATP does not calculate this
ROUND(x_atp_supply_demand.supply_demand_quantity(j),5),
x_atp_supply_demand.disposition_type(j),
x_atp_supply_demand.identifier3(j),
x_atp_supply_demand.supply_demand_type(j),
x_atp_supply_demand.supply_demand_date(j),
p_group_id,
p_session_id,
SYSDATE,
0,
SYSDATE,
0,
-1,
x_atp_supply_demand.inventory_item_id(j),
x_atp_supply_demand.organization_id(j),
NULL, -- We don't need this since the form handles all cases.
x_atp_supply_demand.disposition_name(j)
);
INSERT INTO MTL_SUPPLY_DEMAND_TEMP
(
ATP_PERIOD_START_DATE,
ATP_PERIOD_END_DATE,
ATP_PERIOD_TOTAL_SUPPLY,
ATP_PERIOD_TOTAL_DEMAND,
ATP,
RECORD_TYPE,
SEQ_NUM,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PERIOD_NET_AVAILABLE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID)
VALUES(
x_atp_period.Period_Start_Date(j),
x_atp_period.Period_End_Date(j),
Round(x_atp_period.total_supply_quantity(j),5),
Round(x_atp_period.total_demand_quantity(j),5),
Round(x_atp_period.cumulative_quantity(j), 5),
'ATP',
p_group_id,
p_session_id,
SYSDATE,
0,
SYSDATE,
0,
-1,
Round(x_atp_period.period_quantity(j),5),
x_atp_period.inventory_item_id(j),
x_atp_period.organization_id(j));
DELETE msc_bom_temp WHERE session_id = p_session_id;
sql_stmt := 'DELETE msc_bom_temp'||l_dynstring|| ' WHERE session_id = :session_id';
sql_stmt := 'INSERT INTO msc_bom_temp'||l_dynstring|| ' (
session_id,
assembly_identifier,
assembly_item_id,
component_identifier,
component_item_id,
quantity,
fixed_lt,
variable_lt,
effective_date,
disable_date,
atp_check,
wip_supply_type,
smc_flag,
pre_process_lt,
source_organization_id, -- krajan: 2400614
atp_flag -- krajan: 2462661
)
VALUES (
:session_id,
:assembly_identifier,
:assembly_item_id,
:component_identifier,
:component_item_id,
:quantity,
:fixed_lt,
:variable_lt,
:effective_date,
:disable_date,
:atp_check,
:wip_supply_type,
:smc_flag,
:pre_process_lt,
-- krajan : 2400614
:source_organization_id,
-- krajan : 2462661
:atp_flag
)';
msc_sch_wb.atp_debug('put_into_bom_temp_table: ' || 'after insert into bom_temp');
INSERT INTO msc_bom_temp (
session_id,
assembly_identifier,
assembly_item_id,
component_identifier,
component_item_id,
quantity,
fixed_lt,
variable_lt,
effective_date,
disable_date,
atp_check,
wip_supply_type,
smc_flag,
pre_process_lt,
source_organization_id,
atp_flag)
VALUES(
p_session_id,
p_atp_bom_rec.assembly_identifier(j),
p_atp_bom_rec.assembly_item_id(j),
p_atp_bom_rec.component_identifier(j),
p_atp_bom_rec.component_item_id(j),
p_atp_bom_rec.quantity(j),
p_atp_bom_rec.fixed_lt(j),
p_atp_bom_rec.variable_lt(j),
p_atp_bom_rec.effective_date(j),
p_atp_bom_rec.disable_date(j),
p_atp_bom_rec.atp_check(j),
p_atp_bom_rec.wip_supply_type(j),
p_atp_bom_rec.smc_flag(j),
p_atp_bom_rec.pre_process_lt(j),
p_atp_bom_rec.source_organization_id(j),
p_atp_bom_rec.atp_flag(j));
INSERT INTO mrp_atp_details_temp
(
session_id,
scenario_id,
order_line_id,
atp_LEVEL,
inventory_item_id,
request_item_id,
organization_id,
department_id,
resource_id,
supplier_id,
supplier_site_id,
from_organization_id,
from_location_id,
to_organization_id,
to_location_id,
ship_method,
uom_code,
total_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- time_phased_atp
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
identifier1,
identifier2,
record_type,
pegging_id,
end_pegging_id
-- dsting
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(j),
p_atp_period.identifier(j),
p_atp_period.level(j),
p_atp_period.inventory_item_id(j),
p_atp_period.request_item_id(j),
p_atp_period.organization_id(j),
p_atp_period.department_id(j),
p_atp_period.resource_id(j),
p_atp_period.supplier_id(j),
p_atp_period.supplier_site_id(j),
p_atp_period.from_organization_id(j),
p_atp_period.from_location_id(j),
p_atp_period.to_organization_id(j),
p_atp_period.to_location_id(j),
p_atp_period.ship_method(j),
p_atp_period.uom(j),
p_atp_period.total_supply_quantity(j),
p_atp_period.total_demand_quantity(j),
p_atp_period.total_bucketed_demand_quantity(j), -- time_phased_atp
p_atp_period.period_start_date(j),
p_atp_period.period_end_date(j),
p_atp_period.period_quantity(j),
p_atp_period.cumulative_quantity(j),
p_atp_period.identifier1(j),
p_atp_period.identifier2(j),
1,
p_atp_period.pegging_id(j),
p_atp_period.end_pegging_id(j)
-- dsting
, l_sysdate -- creation_date
, L_USER_ID -- created_by
, l_sysdate -- last_update_date
, L_USER_ID -- update_by
, L_USER_ID -- login_by
);
-- last_update_date, last_updated_by, last_update_login
sql_stmt := '
INSERT INTO mrp_atp_details_temp'||p_dblink||'
(
session_id,
scenario_id,
order_line_id,
atp_LEVEL,
inventory_item_id,
request_item_id,
organization_id,
department_id,
resource_id,
supplier_id,
supplier_site_id,
from_organization_id,
from_location_id,
to_organization_id,
to_location_id,
ship_method,
uom_code,
total_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- time_phased_atp
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
identifier1,
identifier2,
record_type,
pegging_id,
end_pegging_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES
(
:x_session_id,
:scenario_id,
:identifier,
:atp_level,
:inventory_item_id,
:request_item_id,
:organization_id,
:department_id,
:resource_id,
:supplier_id,
:supplier_site_id,
:from_organization_id,
:from_location_id,
:to_organization_id,
:to_location_id,
:ship_method,
:uom,
:total_supply_quantity,
:total_demand_quantity,
:total_bucketed_demand_quantity, -- time_phased_atp
:period_start_date,
:period_end_date,
:period_quantity,
:cumulative_quantity,
:identifier1,
:identifier2,
1,
:pegging_id,
:end_pegging_id
, sysdate
, :created_by
, sysdate
, :created_by
, :created_by
)';
msc_sch_wb.atp_debug('Put_Pegging_data: ' || 'inserting pegging into source side');
-- last_update_date, last_updated_by, last_update_login
sql_stmt := '
INSERT INTO mrp_atp_details_temp@'||p_dblink|| '
(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,
number1,
char1,
component_identifier,
allocated_quantity,
batchable_flag,
-- 2152184
request_item_id,
ptf_date
-- dsting
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login )
SELECT
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,
number1,
char1,
component_identifier,
allocated_quantity,
batchable_flag,
-- 2152184
request_item_id,
ptf_date
, sysdate
, :created_by
, sysdate
, :created_by
, :created_by
FROM mrp_atp_details_temp
WHERE record_type = 3
AND session_id = :x_session_id ';
msc_sch_wb.atp_debug('Put_Pegging_data: ' || 'inserted pegging into source side');
DELETE from mrp_atp_details_temp
WHERE record_type = 3
AND session_id = p_session_id;
mast_rec_insert mrp_atp_utils.mrp_atp_schedule_temp_typ;
SELECT
scenario_id,
order_line_id,
atp_LEVEL,
inventory_item_id,
request_item_id,
organization_id,
department_id,
resource_id,
supplier_id,
supplier_site_id,
from_organization_id,
from_location_id,
to_organization_id,
to_location_id,
ship_method,
uom_code,
total_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- time_phased_atp
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
identifier1,
identifier2,
pegging_id,
end_pegging_id
BULK COLLECT INTO
x_atp_period.scenario_id,
x_atp_period.identifier,
x_atp_period.level,
x_atp_period.inventory_item_id,
x_atp_period.request_item_id,
x_atp_period.organization_id,
x_atp_period.department_id,
x_atp_period.resource_id,
x_atp_period.supplier_id,
x_atp_period.supplier_site_id,
x_atp_period.from_organization_id,
x_atp_period.from_location_id,
x_atp_period.to_organization_id,
x_atp_period.to_location_id,
x_atp_period.ship_method,
x_atp_period.uom,
x_atp_period.total_supply_quantity,
x_atp_period.total_demand_quantity,
x_atp_period.total_bucketed_demand_quantity, -- time_phased_atp
x_atp_period.period_start_date,
x_atp_period.period_end_date,
x_atp_period.period_quantity,
x_atp_period.cumulative_quantity,
x_atp_period.identifier1,
x_atp_period.identifier2,
x_atp_period.pegging_id,
x_atp_period.end_pegging_id
FROM mrp_atp_details_temp
WHERE session_id = p_session_id
and record_type = 1
and pegging_id in (select pegging_id from mrp_atp_details_temp
where session_id = p_session_id
and record_type = 3);
SELECT ORDER_LINE_ID
,PEGGING_ID
,ATP_LEVEL
,REQUEST_ITEM_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,DEPARTMENT_ID
,RESOURCE_ID
,SUPPLIER_ID
,SUPPLIER_SITE_ID
,FROM_ORGANIZATION_ID
,FROM_LOCATION_ID
,TO_ORGANIZATION_ID
,TO_LOCATION_ID
,SHIP_METHOD
,UOM_CODE
,IDENTIFIER1
,IDENTIFIER2
,IDENTIFIER3
,IDENTIFIER4
,SUPPLY_DEMAND_TYPE
,SUPPLY_DEMAND_DATE
,SUPPLY_DEMAND_QUANTITY
,SUPPLY_DEMAND_SOURCE_TYPE
,SCENARIO_ID
,DISPOSITION_TYPE
,DISPOSITION_NAME
,SUPPLY_DEMAND_SOURCE_TYPE_NAME
,END_PEGGING_ID
bulk collect into
x_atp_supply_demand.identifier,
x_atp_supply_demand.pegging_id,
x_atp_supply_demand.Level,
x_atp_supply_demand.Request_Item_Id,
x_atp_supply_demand.Inventory_Item_Id,
x_atp_supply_demand.Organization_Id,
x_atp_supply_demand.Department_Id,
x_atp_supply_demand.Resource_Id,
x_atp_supply_demand.Supplier_Id,
x_atp_supply_demand.Supplier_Site_Id,
x_atp_supply_demand.From_Organization_Id,
x_atp_supply_demand.From_Location_Id,
x_atp_supply_demand.To_Organization_Id,
x_atp_supply_demand.To_Location_Id,
x_atp_supply_demand.Ship_Method,
x_atp_supply_demand.Uom,
x_atp_supply_demand.Identifier1,
x_atp_supply_demand.Identifier2,
x_atp_supply_demand.Identifier3,
x_atp_supply_demand.Identifier4,
x_atp_supply_demand.Supply_Demand_Type,
x_atp_supply_demand.Supply_Demand_Date,
x_atp_supply_demand.supply_demand_quantity,
x_atp_supply_demand.Supply_Demand_Source_Type,
x_atp_supply_demand.scenario_id,
x_atp_supply_demand.disposition_type,
x_atp_supply_demand.disposition_name,
x_atp_supply_demand.Supply_Demand_Source_Type_name,
x_atp_supply_demand.end_pegging_id
FROM mrp_atp_details_temp
WHERE session_id = p_session_id
and record_type = 2
and pegging_id in (select pegging_id from mrp_atp_details_temp
where session_id = p_session_id
and record_type = 3);
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PEGGING_TYPE
,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
,UTILIZATION
,OWNING_DEPARTMENT
,REVERSE_CUM_YIELD
,BASIS_TYPE
,USAGE
,CONSTRAINT_TYPE
,CONSTRAINT_DATE
,ATP_RULE_NAME
,PLAN_NAME
,constrained_path
,TOTAL_BUCKETED_DEMAND_QUANTITY -- time_phased_atp
,aggregate_time_fence_date, -- Bug 3279014
UNALLOCATED_QUANTITY, -- Bug 3282426
PF_DISPLAY_FLAG ,
ORIGINAL_DEMAND_DATE,
ORIGINAL_DEMAND_QUANTITY,
ORIGINAL_ITEM_ID,
ORIGINAL_SUPPLY_DEMAND_TYPE,
BASE_MODEL_ID,
BASE_MODEL_NAME,
MODEL_SD_FLAG,
ERROR_CODE,
NONATP_FLAG,
ORIG_CUSTOMER_SITE_NAME, --3263368
ORIG_CUSTOMER_NAME, --3263368
ORIG_DEMAND_CLASS, --3263368
ORIG_REQUEST_DATE, --3263368
COMPONENT_YIELD_FACTOR, --4570421
SCALING_TYPE, --4570421
ROUNDING_DIRECTION, --4570421
SCALE_ROUNDING_VARIANCE, --4570421
SCALE_MULTIPLE, --4570421
ORGANIZATION_TYPE --4775920
';
sql_stmt := 'Insert into mrp_atp_details_temp@' || p_dblink || ' (';
' ) select ' || l_std_cols || l_apps_v3_cols;
' ) select ' || l_std_cols;
and pegging_id in (Select pegging_id from mrp_atp_details_temp
where session_id = :p_session_id
and record_type = 3)';
INSERT INTO mrp_atp_details_temp (
session_id,
scenario_id,
order_line_id,
ATP_Level,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
Uom_code,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_type_name,
Supply_Demand_Date,
supply_demand_quantity,
disposition_type,
disposition_name,
record_type,
pegging_id,
end_pegging_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT
MSC_ATP_PVT.G_SESSION_ID,
scenario_id,
order_line_id,
ATP_Level,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
Uom_code,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_type_name,
Supply_Demand_Date,
supply_demand_quantity,
disposition_type,
disposition_name,
2,
p_pegging_id,
end_pegging_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
FROM mrp_atp_details_temp
where pegging_id = p_old_pegging_id
and record_type = 2;
sql_stmt := 'Insert into mrp_atp_details_temp' || l_dynstring ||
'(select * from mrp_atp_details_temp
where session_id = :p_session_id
and record_type = 2
and pegging_id in (Select pegging_id from mrp_atp_details_temp
where session_id = :p_session_id
and record_type = 3))';
SELECT ORDER_LINE_ID
,PEGGING_ID
,ATP_LEVEL
,REQUEST_ITEM_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,DEPARTMENT_ID
,RESOURCE_ID
,SUPPLIER_ID
,SUPPLIER_SITE_ID
,FROM_ORGANIZATION_ID
,FROM_LOCATION_ID
,TO_ORGANIZATION_ID
,TO_LOCATION_ID
,SHIP_METHOD
,UOM_CODE
,IDENTIFIER1
,IDENTIFIER2
,IDENTIFIER3
,IDENTIFIER4
,SUPPLY_DEMAND_TYPE
,SUPPLY_DEMAND_DATE
,SUPPLY_DEMAND_QUANTITY
,SUPPLY_DEMAND_SOURCE_TYPE
,SCENARIO_ID
,DISPOSITION_TYPE
,DISPOSITION_NAME
,SUPPLY_DEMAND_SOURCE_TYPE_NAME
,END_PEGGING_ID
bulk collect into
x_atp_supply_demand.identifier,
x_atp_supply_demand.pegging_id,
x_atp_supply_demand.Level,
x_atp_supply_demand.Request_Item_Id,
x_atp_supply_demand.Inventory_Item_Id,
x_atp_supply_demand.Organization_Id,
x_atp_supply_demand.Department_Id,
x_atp_supply_demand.Resource_Id,
x_atp_supply_demand.Supplier_Id,
x_atp_supply_demand.Supplier_Site_Id,
x_atp_supply_demand.From_Organization_Id,
x_atp_supply_demand.From_Location_Id,
x_atp_supply_demand.To_Organization_Id,
x_atp_supply_demand.To_Location_Id,
x_atp_supply_demand.Ship_Method,
x_atp_supply_demand.Uom,
x_atp_supply_demand.Identifier1,
x_atp_supply_demand.Identifier2,
x_atp_supply_demand.Identifier3,
x_atp_supply_demand.Identifier4,
x_atp_supply_demand.Supply_Demand_Type,
x_atp_supply_demand.Supply_Demand_Date,
x_atp_supply_demand.supply_demand_quantity,
x_atp_supply_demand.Supply_Demand_Source_Type,
x_atp_supply_demand.scenario_id,
x_atp_supply_demand.disposition_type,
x_atp_supply_demand.disposition_name,
x_atp_supply_demand.Supply_Demand_Source_Type_name,
x_atp_supply_demand.end_pegging_id
FROM mrp_atp_details_temp
WHERE session_id = p_session_id
and record_type = 2
and pegging_id in (select pegging_id from mrp_atp_details_temp
where session_id = p_session_id
and record_type = 3);
INSERT INTO mrp_atp_details_temp
(
session_id,
scenario_id,
order_line_id,
ATP_Level,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
Uom_code,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_type_name,
Supply_Demand_Date,
supply_demand_quantity,
disposition_type,
disposition_name,
record_type,
pegging_id,
end_pegging_id
-- dsting
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES
(
p_session_id,
p_atp_supply_demand.scenario_id(j),
p_atp_supply_demand.identifier(j),
p_atp_supply_demand.LEVEL(j),
p_atp_supply_demand.Inventory_Item_Id(j),
p_atp_supply_demand.Request_Item_Id(j),
p_atp_supply_demand.Organization_Id(j),
p_atp_supply_demand.Department_Id(j),
p_atp_supply_demand.Resource_Id(j),
p_atp_supply_demand.Supplier_Id(j),
p_atp_supply_demand.Supplier_Site_Id(j),
p_atp_supply_demand.From_Organization_Id(j),
p_atp_supply_demand.From_Location_Id(j),
p_atp_supply_demand.To_Organization_Id(j),
p_atp_supply_demand.To_Location_Id(j),
p_atp_supply_demand.Ship_Method(j),
p_atp_supply_demand.Uom(j),
p_atp_supply_demand.Identifier1(j),
p_atp_supply_demand.Identifier2(j),
p_atp_supply_demand.Identifier3(j),
p_atp_supply_demand.Identifier4(j),
p_atp_supply_demand.Supply_Demand_Type(j),
p_atp_supply_demand.Supply_Demand_Source_Type(j),
p_atp_supply_demand.Supply_Demand_Source_Type_name(j),
p_atp_supply_demand.Supply_Demand_Date(j),
p_atp_supply_demand.supply_demand_quantity(j),
p_atp_supply_demand.disposition_type(j),
p_atp_supply_demand.disposition_name(j),
2,
p_atp_supply_demand.pegging_id(j),
p_atp_supply_demand.end_pegging_id(j)
-- dsting
, sysdate -- creation_date
, l_user_id -- created_by
, sysdate -- last_update_date
, l_user_id -- updated_by
, l_user_id -- login_by
);
-- last_update_date, last_updated_by, last_update_login
sql_stmt := '
INSERT INTO mrp_atp_details_temp@'||p_dblink||'
(
session_id,
scenario_id,
order_line_id,
ATP_Level,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
Uom_code,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_type_name,
Supply_Demand_Date,
supply_demand_quantity,
disposition_type,
disposition_name,
record_type,
pegging_id,
end_pegging_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES
(
:x_session_id,
:scenario_id,
:identifier,
:atp_level,
:Inventory_Item_Id,
:Request_Item_Id,
:Organization_Id,
:Department_Id,
:Resource_Id,
:Supplier_Id,
:Supplier_Site_Id,
:From_Organization_Id,
:From_Location_Id,
:To_Organization_Id,
:To_Location_Id,
:Ship_Method,
:Uom_Code,
:Identifier1,
:Identifier2,
:Identifier3,
:Identifier4,
:Supply_Demand_Type,
:Supply_Demand_Source_Type,
:name,
:Supply_Demand_Date,
:supply_demand_quantity,
:disposition_type,
:disposition_name,
2,
:pegging_id,
:end_pegging_id
, sysdate
, :created_by
, sysdate
, :last_updated_by
, :last_update_login
)';
DBMS_SQL.BIND_VARIABLE(cur_handler, ':last_updated_by', l_user_id);
DBMS_SQL.BIND_VARIABLE(cur_handler, ':last_update_login', l_user_id);
procedure Update_Line_Item_Properties(p_session_id IN NUMBER,
Action IN NUMBER) --3720018
IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Inside Update_Line_Item_Properties');
msc_sch_wb.atp_debug('Update item properties');
update mrp_atp_schedule_temp mast
--bug 4078703: Populate atp_lead time as this lead time is required
--on OM sales order lies to support misc. functionalities in inv and
--other module. Here we populte it on top model line only. This value is populated
-- option class and items in put_sch_data_resulst_mode procedure
set (atp_flag, atp_components_flag, bom_item_type, pick_components_flag, fixed_lt, variable_lt, atp_lead_time) =
(Select msi.atp_flag,
decode(MSC_ATP_PVT.G_INV_CTP, 5,
--IF ATP flag for PTO model/ATO model is other than 'N' then we still go to destination
-- Thats why atp components flag is set as it is for PTO ato models
decode(mast.order_line_id, mast.ato_model_line_id, msi.atp_components_flag,
decode(msi.pick_components_flag, 'Y', msi.atp_components_flag, 'N')) ,
msi.atp_components_flag ),
msi.bom_item_type,
msi.pick_components_flag,
msi.fixed_lead_time,
msi.VARIABLE_LEAD_TIME,
---bug 4078703: populate ATP lead time
CEIL(decode(mast.order_line_id, mast.ato_model_line_id,
decode(bom_item_type, 1,
(NVL(msi.fixed_lead_time, 0) + (NVL(msi.VARIABLE_LEAD_TIME, 0) * mast.quantity_ordered)) * (1 + MSC_ATP_PVT.G_MSO_LEAD_TIME_FACTOR), 0), null))
from mtl_system_items msi
where msi.organization_id = nvl(mast.source_organization_id, mast.validation_org)
and msi.inventory_item_id = mast.inventory_item_id)
where mast.session_id = p_session_id
--bug 3378648
and mast.status_flag in (99,4)--4658238
and (mast.source_organization_id is not null
or mast.validation_org is not null);
msc_sch_wb.atp_debug('Rows updated := ' || SQL%ROWCOUNT);
update mrp_atp_schedule_temp mast
set (mast.old_source_organization_id, mast.Old_Demand_Class )=
(SELECT mast.Source_Organization_Id,
NVL(mast.Old_Demand_Class, mast.demand_class)
from oe_order_lines_all o
where o.line_id = mast.order_line_id and
o.schedule_ship_date is not NULL
)
where mast.Old_Source_Organization_Id is NULL and
mast.session_id = p_session_id;
msc_sch_wb.atp_debug('Rows updated := ' || SQL%ROWCOUNT);
END Update_Line_item_properties;
procedure Update_Line_Item_Properties_WS(p_session_id IN NUMBER)
IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Inside Update_Line_Item_Properties_WS');
msc_sch_wb.atp_debug('Update item properties');
update mrp_atp_schedule_temp mast
--bug 4078703: Populate atp_lead time as this lead time is required
--on OM sales order lies to support misc. functionalities in inv and
--other module. Here we populte it on top model line only. This value is populated
-- option class and items in put_sch_data_resulst_mode procedure
set (atp_flag, atp_components_flag, bom_item_type, pick_components_flag, fixed_lt, variable_lt, atp_lead_time) =
(Select msi.atp_flag,
decode(MSC_ATP_PVT.G_INV_CTP, 5,
--IF ATP flag for PTO model/ATO model is other than 'N' then we still go to destination
-- Thats why atp components flag is set as it is for PTO ato models
decode(mast.order_line_id, mast.ato_model_line_id, msi.atp_components_flag,
decode(msi.pick_components_flag, 'Y', msi.atp_components_flag, 'N')) ,
msi.atp_components_flag ),
msi.bom_item_type,
msi.pick_components_flag,
msi.fixed_lead_time,
msi.VARIABLE_LEAD_TIME,
---bug 4078703: populate ATP lead time
CEIL(decode(mast.order_line_id, mast.ato_model_line_id,
decode(bom_item_type, 1,
(NVL(msi.fixed_lead_time, 0) + (NVL(msi.VARIABLE_LEAD_TIME, 0) * mast.quantity_ordered)) * (1 + MSC_ATP_PVT.G_MSO_LEAD_TIME_FACTOR), 0), null))
from msc_system_items msi
where msi.organization_id = nvl(mast.source_organization_id, mast.validation_org)
and msi.sr_inventory_item_id = mast.inventory_item_id
and msi.plan_id = -1
and msi.sr_instance_id = mast.sr_instance_id)
where mast.session_id = p_session_id
--bug 3378648
and mast.status_flag in (99,4)--4658238
and (mast.source_organization_id is not null
or mast.validation_org is not null);
msc_sch_wb.atp_debug('Rows updated := ' || SQL%ROWCOUNT);
END Update_Line_Item_Properties_WS;
Delete from mrp_atp_schedule_temp where session_id = p_session_id
--bug 3378648: delete only ATP relevent data
and status_flag in (1,2, 99);
INSERT INTO mrp_atp_schedule_temp
(
mdi_rowid,
session_id,
scenario_id,
sr_instance_id,
inventory_item_id ,
inventory_item_name,
source_organization_id,
source_organization_code,
order_header_id, -- add
Demand_Source_Delivery,
Demand_Source_Type,
atp_lead_time,
order_line_id, -- different
order_number,
calling_module,
customer_id,
customer_site_id,
destination_time_zone,
quantity_ordered,
uom_code,
requested_ship_date,
requested_arrival_date,
latest_acceptable_date,
delivery_lead_time,
freight_carrier,
ship_method,
demand_class,
ship_set_name,
arrival_set_name,
override_flag,
action,
scheduled_ship_date, -- different
available_quantity,
requested_date_quantity,
group_ship_date,
group_arrival_date,
vendor_id,
vendor_name,
vendor_site_id,
vendor_site_name,
insert_flag,
error_code,
error_Message,
status_flag,
oe_flag,
end_pegging_id,
old_source_organization_id,
old_demand_class,
scheduled_arrival_date,
attribute_06,
organization_id,
substitution_typ_code,
req_item_detail_flag,
old_inventory_item_id,
request_item_id,
request_item_name,
req_item_req_date_qty,
req_item_available_date_qty,
req_item_available_date,
sales_rep,
customer_contact,
subst_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
diagnostic_atp_flag,
sequence_number,
source_doc_id,
---columns for CTO project
Top_Model_line_id,
ATO_Parent_Model_Line_Id,
ATO_Model_Line_Id,
Parent_line_id,
match_item_id,
Config_item_line_id,
Validation_Org,
Component_Sequence_ID,
Component_Code,
line_number,
included_item_flag,
atp_flag,
firm_flag,
order_line_number,
option_number,
shipment_number,
item_desc,
old_line_schedule_date,
old_source_organization_code,
firm_source_org_id,
firm_source_org_code,
firm_ship_date,
firm_arrival_date,
ship_method_text,
ship_set_id,
arrival_set_id,
PROJECT_ID,
TASK_ID,
PROJECT_NUMBER,
TASK_NUMBER,
original_request_date,
CASCADE_MODEL_INFO_TO_COMP,
internal_org_id, --4279623
customer_country, --2814895
customer_state,
customer_city,
customer_postal_code,
party_site_id,
part_of_set ---4500382
)
values
(
p_atp_rec.row_id(j),
p_session_id,
NVL(p_atp_rec.scenario_id(j), -1),
MSC_ATP_PVT.G_INSTANCE_ID,
--p_atp_rec.instance_id(j),
p_atp_rec.inventory_item_id(j) ,
p_atp_rec.inventory_item_name(j),
p_atp_rec.source_organization_id(j),
p_atp_rec.source_organization_code(j),
nvl(p_atp_rec.demand_source_header_id(j), -1),
p_atp_rec.demand_source_delivery(j),
p_atp_rec.demand_source_type(j),
p_atp_rec.atp_lead_time(j),
NVL(p_atp_rec.identifier(j),0),
p_atp_rec.order_number(j),
p_atp_rec.calling_module(j),
p_atp_rec.customer_id(j),
p_atp_rec.customer_site_id(j),
p_atp_rec.destination_time_zone(j),
p_atp_rec.quantity_ordered(j),
p_atp_rec.quantity_uom(j),
p_atp_rec.requested_ship_date(j),
p_atp_rec.requested_arrival_date(j),
p_atp_rec.latest_acceptable_date(j),
p_atp_rec.delivery_lead_time(j),
p_atp_rec.freight_carrier(j),
p_atp_rec.ship_method(j),
p_atp_rec.demand_class(j),
p_atp_rec.ship_set_name(j),
p_atp_rec.arrival_set_name(j),
p_atp_rec.override_flag(j),
p_atp_rec.action(j),
p_atp_rec.ship_date(j),
p_atp_rec.available_quantity(j),
p_atp_rec.requested_date_quantity(j),
p_atp_rec.group_ship_date(j),
p_atp_rec.group_arrival_date(j),
p_atp_rec.vendor_id(j),
p_atp_rec.vendor_name(j),
p_atp_rec.vendor_site_id(j),
p_atp_rec.vendor_site_name(j),
p_atp_rec.insert_flag(j),
p_atp_rec.error_code(j),
p_atp_rec.message(j),
l_status_flag,
p_atp_rec.oe_flag(j),
p_atp_rec.end_pegging_id(j),
p_atp_rec.old_source_organization_id(j),
p_atp_rec.old_demand_class(j),
p_atp_rec.arrival_date(j),
p_atp_rec.attribute_06(j),
p_atp_rec.organization_id(j),
p_atp_rec.substitution_typ_code(j),
p_atp_rec.req_item_detail_flag(j),
p_atp_rec.old_inventory_item_id(j),
p_atp_rec.request_item_id(j),
p_atp_rec.request_item_name(j),
p_atp_rec.req_item_req_date_qty(j),
p_atp_rec.req_item_available_date_qty(j),
p_atp_rec.req_item_available_date(j),
p_atp_rec.sales_rep(j),
p_atp_rec.customer_contact(j),
p_atp_rec.subst_flag(j),
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
p_atp_rec.attribute_02(j),
decode( nvl(p_atp_rec.calling_module(j),-1),724,l_sequence_number(j),
-99,l_sequence_number(j),
-1, l_sequence_number(j),
p_atp_rec.attribute_11(j)),
p_atp_rec.attribute_01(j),
--cto_attribute
p_atp_rec.Top_Model_line_id(j),
p_atp_rec.ATO_Parent_Model_Line_Id(j),
p_atp_rec.ATO_Model_Line_Id(j),
p_atp_rec.Parent_line_id(j),
p_atp_rec.match_item_id(j),
p_atp_rec.Config_item_line_id(j),
p_atp_rec.Validation_Org(j),
p_atp_rec.Component_Sequence_ID(j),
p_atp_rec.Component_Code(j),
p_atp_rec.line_number(j),
p_atp_rec.included_item_flag(j),
decode(p_atp_rec.source_organization_id(j), null, 'Y', null),
p_atp_rec.firm_flag(j),
p_atp_rec.order_line_number(j),
p_atp_rec.option_number(j),
p_atp_rec.shipment_number(j),
p_atp_rec.item_desc(j),
p_atp_rec.old_line_schedule_date(j),
p_atp_rec.old_source_organization_code(j),
p_atp_rec.firm_source_org_id(j),
p_atp_rec.firm_source_org_code(j),
p_atp_rec.firm_ship_date(j),
p_atp_rec.firm_arrival_date(j),
p_atp_rec.ship_method_text(j),
p_atp_rec.ship_set_id(j),
p_atp_rec.arrival_set_id(j),
p_atp_rec.PROJECT_ID(j),
p_atp_rec.TASK_ID(j),
p_atp_rec.PROJECT_NUMBER(j),
p_atp_rec.TASK_NUMBER(j),
p_atp_rec.original_request_date(j),
p_atp_rec.CASCADE_MODEL_INFO_TO_COMP(j),
p_atp_rec.internal_org_id(j), --4279623
p_atp_rec.customer_country(j), --2814895
p_atp_rec.customer_state(j),
p_atp_rec.customer_city(j),
p_atp_rec.customer_postal_code(j),
p_atp_rec.party_site_id(j),
nvl(p_atp_rec.part_of_set(j),'N') --4500382
);
msc_sch_wb.atp_debug('After Inserting the data in request mode');
msc_sch_wb.atp_debug('rows inserted = ' || SQL%ROWCOUNT);
update mrp_atp_schedule_temp mast
set (atp_flag, atp_components_flag, bom_item_type, pick_components_flag, fixed_lt, variable_lt) =
(Select msi.atp_flag,
decode(MSC_ATP_PVT.G_INV_CTP, 5,
--IF ATP flag for PTO model/ATO model is other than 'N' then we still go to destination
-- Thats why atp components flag is set as it is for PTO ato models
decode(mast.order_line_id, mast.ato_model_line_id, msi.atp_components_flag,
decode(msi.pick_components_flag, 'Y', msi.atp_components_flag, 'N')) ,
msi.atp_components_flag ),
msi.bom_item_type,
msi.pick_components_flag,
msi.fixed_lead_time,
msi.VARIABLE_LEAD_TIME
from mtl_system_items msi
where msi.organization_id = nvl(mast.source_organization_id, mast.validation_org)
and msi.inventory_item_id = mast.inventory_item_id)
where mast.session_id = p_session_id
--bug 3378648: only update request data
and status_flag = 99
and (mast.source_organization_id is not null
or mast.validation_org is not null);
msc_sch_wb.atp_debug('Rows updated := ' || SQL%ROWCOUNT);
MSC_ATP_UTILS.Update_Line_item_properties_WS(p_session_id);
MSC_ATP_UTILS.Update_Line_item_properties(p_session_id, p_atp_rec.action(1)); --3720018
select count(*)
into l_count
from mrp_atp_schedule_temp mast
where mast.session_id = p_session_id
--bug 3378648
and status_flag = 99
and mast.OE_FLAG = 'Y';
update mrp_atp_schedule_temp mast
set OE_FLAG =
(Select decode(MSC_ATP_PVT.G_INV_CTP, 5, mast.OE_FLAG,
decode( prha.interface_source_code, 'MRP', 'Y', 'MSC', 'Y', 'N'))
from po_requisition_headers_all prha
where prha.requisition_header_id = mast.source_doc_id),
INTERNAL_ORG_ID = -- Bug 3449812
(Select po.destination_organization_id
from po_requisition_lines_all po,
oe_order_lines_all oe
where oe.source_document_line_id = po.requisition_line_id
and oe.line_id = mast.order_line_id)
where mast.session_id = p_session_id
--bug 3378648: only update request data
and status_flag = 99
and mast.OE_FLAG = 'Y';
msc_sch_wb.atp_debug('Call from destination Instance. No need to update atp flags or oe flags');
mast_rec_insert mrp_atp_utils.mrp_atp_schedule_temp_typ;
msc_sch_wb.atp_debug(' insert data for Call from other modules');
UPDATE MRP_ATP_SCHEDULE_TEMP
SET
scenario_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
NVL(p_atp_rec.scenario_id(j), -1), scenario_id),
inventory_item_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.inventory_item_id(j), inventory_item_id),
inventory_item_name = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.inventory_item_name(j), inventory_item_name),
source_organization_id =Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.source_organization_id(j),
Decode(nvl(cascade_model_info_to_comp, 1), 1,
p_atp_rec.source_organization_id(j), null)),
source_organization_code = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.source_organization_code(j),
Decode(nvl(cascade_model_info_to_comp, 1), 1,
p_atp_rec.source_organization_code(j), null)),
order_header_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
nvl(p_atp_rec.demand_source_header_id(j), -1), null),
Demand_Source_Type = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.demand_source_type(j), null),
delivery_lead_time = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.delivery_lead_time(j),
Decode(nvl(cascade_model_info_to_comp, 1), 1,
p_atp_rec.delivery_lead_time(j), null)),
ship_method = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.ship_method(j),
Decode(nvl(cascade_model_info_to_comp, 1), 1,
p_atp_rec.ship_method(j), null)),
demand_class = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.demand_class(j), null),
scheduled_ship_date = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.ship_date(j),
Decode(nvl(cascade_model_info_to_comp, 1), 1,
p_atp_rec.ship_date(j),null)),
available_quantity = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.available_quantity(j), null),
requested_date_quantity = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.requested_date_quantity(j), null),
group_ship_date = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.group_ship_date(j),
Decode(nvl(cascade_model_info_to_comp, 1), 1,
p_atp_rec.group_ship_date(j), null)),
group_arrival_date = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.group_arrival_date(j),
Decode(nvl(cascade_model_info_to_comp, 1), 1,
p_atp_rec.group_arrival_date(j),null)),
error_code = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.error_code(j),
Decode(error_code, null,decode(p_atp_rec.error_code(j), 150, 0, 61, 0, 0, 0, MSC_ATP_PVT.GROUPEL_ERROR))),
error_Message = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.message(j), null),
status_flag = 2,
end_pegging_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.end_pegging_id(j),
Decode(nvl(cascade_model_info_to_comp, 1), 1,
p_atp_rec.end_pegging_id(j),null)),
scheduled_arrival_date = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.arrival_date(j),
Decode(nvl(cascade_model_info_to_comp, 1), 1,
p_atp_rec.arrival_date(j),null)),
organization_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.organization_id(j), null),
request_item_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.request_item_id(j), null),
request_item_name = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.request_item_name(j), null),
req_item_req_date_qty = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.req_item_req_date_qty(j), null),
req_item_available_date_qty = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.req_item_available_date_qty(j), null),
req_item_available_date =Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.req_item_available_date(j), null),
sales_rep = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.sales_rep(j), null),
customer_contact = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.customer_contact(j), null),
compile_designator = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
DECODE(MSC_ATP_PVT.G_INV_CTP, 4, p_atp_rec.attribute_07(j), null), null),
subst_flag = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.subst_flag(j), null),
match_item_id = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.match_item_id(j), null),
matched_item_name = Decode(order_line_id, NVL(ato_model_line_id, order_line_id),
p_atp_rec.matched_item_name(j), null),
plan_id = p_atp_rec.plan_id(j),
--bug 3328421
first_valid_ship_arrival_date = p_atp_rec.first_valid_ship_arrival_date(j),
--bug 4078703: update atp_lead_time on options
--atp_lead_time = Decode(order_line_id, NVL(ato_model_line_id, order_line_id), 0, p_atp_rec.atp_lead_time(j))
-- atp_lead_time = Decode(order_line_id, NVL(ato_model_line_id, order_line_id), NULL, p_atp_rec.atp_lead_time(j))
atp_lead_time = Decode(ato_model_line_id, null, null, order_line_id, 0, p_atp_rec.atp_lead_time(j))
where session_id = p_session_id
--bug 3378648: update only request data
and status_flag = 99
and NVL(ato_model_line_id, order_line_id) = p_atp_rec.identifier(j)
and NVL(p_atp_rec.mandatory_item_flag(j), 2) = 2
--bug 3347424: added this condition so that line corresponding
--to particular warehouse is update in case of global order promising
--bug 3373467: Following condition doesn't work if no sources are found.
-- in that case source orgs remian null and we were not updating any thing.
and NVL(source_organization_id, NVL(p_atp_rec.source_organization_id(j), -1)) =
NVL(p_atp_rec.source_organization_id(j), -1);
msc_sch_wb.atp_debug('Rows Updated := ' || SQL%ROWCOUNT);
INSERT INTO mrp_atp_schedule_temp
(
mdi_rowid,
session_id,
scenario_id,
sr_instance_id,
inventory_item_id,
inventory_item_name,
source_organization_id,
source_organization_code,
order_header_id, -- add
Demand_Source_Delivery,
Demand_Source_Type,
atp_lead_time,
order_line_id, -- different
order_number,
calling_module,
customer_id,
customer_site_id,
destination_time_zone,
quantity_ordered,
uom_code,
requested_ship_date,
requested_arrival_date,
latest_acceptable_date,
delivery_lead_time,
freight_carrier,
ship_method,
demand_class,
ship_set_name,
arrival_set_name,
override_flag,
action,
scheduled_ship_date, -- different
available_quantity,
requested_date_quantity,
group_ship_date,
group_arrival_date,
vendor_id,
vendor_name,
vendor_site_id,
vendor_site_name,
insert_flag,
error_code,
error_Message,
status_flag,
oe_flag,
end_pegging_id,
old_source_organization_id,
old_demand_class,
scheduled_arrival_date,
attribute_06,
organization_id,
substitution_typ_code,
req_item_detail_flag,
old_inventory_item_id,
request_item_id,
request_item_name,
req_item_req_date_qty,
req_item_available_date_qty,
req_item_available_date,
sales_rep,
customer_contact,
compile_designator, -- added for bug 2392456
subst_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
diagnostic_atp_flag,
sequence_number,
mandatory_item_flag,
--bug 3328421:
first_valid_ship_arrival_date
)
VALUES
(
l_atp_rec.row_id(j),
p_session_id,
NVL(l_atp_rec.scenario_id(j), -1),
l_atp_rec.instance_id(j),
l_atp_rec.inventory_item_id(j),
l_atp_rec.inventory_item_name(j),
l_atp_rec.source_organization_id(j),
l_atp_rec.source_organization_code(j),
nvl(l_atp_rec.demand_source_header_id(j), -1),
l_atp_rec.demand_source_delivery(j),
l_atp_rec.demand_source_type(j),
l_atp_rec.atp_lead_time(j),
NVL(l_atp_rec.identifier(j),0),
l_atp_rec.order_number(j),
l_atp_rec.calling_module(j),
l_atp_rec.customer_id(j),
l_atp_rec.customer_site_id(j),
l_atp_rec.destination_time_zone(j),
l_atp_rec.quantity_ordered(j),
l_atp_rec.quantity_uom(j),
l_atp_rec.requested_ship_date(j),
l_atp_rec.requested_arrival_date(j),
l_atp_rec.latest_acceptable_date(j),
l_atp_rec.delivery_lead_time(j),
l_atp_rec.freight_carrier(j),
l_atp_rec.ship_method(j),
l_atp_rec.demand_class(j),
l_atp_rec.ship_set_name(j),
l_atp_rec.arrival_set_name(j),
l_atp_rec.override_flag(j),
l_atp_rec.action(j),
l_atp_rec.ship_date(j),
l_atp_rec.available_quantity(j),
l_atp_rec.requested_date_quantity(j),
l_atp_rec.group_ship_date(j),
l_atp_rec.group_arrival_date(j),
l_atp_rec.vendor_id(j),
l_atp_rec.vendor_name(j),
l_atp_rec.vendor_site_id(j),
l_atp_rec.vendor_site_name(j),
l_atp_rec.insert_flag(j),
l_atp_rec.error_code(j),
l_atp_rec.message(j),
l_status_flag,
l_atp_rec.oe_flag(j),
l_atp_rec.end_pegging_id(j),
l_atp_rec.old_source_organization_id(j),
l_atp_rec.old_demand_class(j),
l_atp_rec.arrival_date(j),
l_atp_rec.attribute_06(j),
l_atp_rec.organization_id(j),
l_atp_rec.substitution_typ_code(j),
l_atp_rec.req_item_detail_flag(j),
l_atp_rec.old_inventory_item_id(j),
l_atp_rec.request_item_id(j),
l_atp_rec.request_item_name(j),
l_atp_rec.req_item_req_date_qty(j),
l_atp_rec.req_item_available_date_qty(j),
l_atp_rec.req_item_available_date(j),
l_atp_rec.sales_rep(j),
l_atp_rec.customer_contact(j),
DECODE(MSC_ATP_PVT.G_INV_CTP, 4, l_atp_rec.attribute_07(j), null ),
l_atp_rec.subst_flag(j),
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
l_atp_rec.attribute_02(j),
l_atp_rec.sequence_number(j),
l_atp_rec.mandatory_item_flag(j),
--bug 3328421
l_atp_rec.first_valid_ship_arrival_date(j)
);
msc_sch_wb.atp_debug('Number of Rows Inserted := ' || SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Model is present, update model component data');
/* Update mrp_atp_schedule_temp mast_1
set (scheduled_ship_date, end_pegging_id, scheduled_arrival_date, status_flag,
group_ship_date, group_arrival_date, plan_id, ship_method, delivery_lead_time,
source_organization_id, error_code) =
(select Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, scheduled_ship_date, null),
Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, end_pegging_id, null),
Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, scheduled_arrival_date, null),
2,
Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, group_ship_date, null),
Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, group_arrival_date, null),
plan_id,
Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, ship_method, null),
Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, delivery_lead_time, null),
Decode(NVL(mast_1.cascade_model_info_to_comp, 1), 1, source_organization_id, null),
decode(error_code, 150, 0, 61, 0, 0, 0, MSC_ATP_PVT.GROUPEL_ERROR)
from mrp_atp_schedule_temp mast_2 where
mast_2.session_id = p_session_id and
mast_2.order_line_id = mast_1.ato_model_line_id and
mast_2.source_organization_id = NVL(mast_1.source_organization_id, mast_2.source_organization_id)
)
where mast_1.session_id = p_session_id
and mast_1.ato_model_line_id is not null
and mast_1.order_line_id <> mast_1.ato_model_line_id;
msc_sch_wb.atp_debug('Number of Rows updated := ' || SQL%ROWCOUNT);
update msc_cto_bom mcb
set plan_id = (select plan_id
from mrp_atp_schedule_temp mast
where mast.session_id = p_session_id
and mast.order_line_id = mcb.line_id
)
where mcb.session_id = p_session_id;
msc_sch_wb.atp_debug('Number of Rows updated n msc_cto_bom := ' || SQL%ROWCOUNT);
update msc_cto_sources mcs
set plan_id = (select plan_id
from mrp_atp_schedule_temp mast
where mast.session_id = p_session_id
and mast.order_line_id = mcs.line_id
)
where mcs.session_id = p_session_id;
msc_sch_wb.atp_debug('Number of Rows updated in msc_cto_sources := ' || SQL%ROWCOUNT);
/* bug 3378649: delete any data locally
--delete any old data
IF p_mode = RESULTS_MODE THEN
l_sql_stmt :=
'DELETE FROM MRP_ATP_SCHEDULE_TEMP'||L_source_DBLINK||
' WHERE session_id = :p_session_id '||
' and status_flag in (1, 99, 2) ';
DELETE FROM MRP_ATP_SCHEDULE_TEMP
WHERE session_id = p_session_id
and status_flag in (1, 99, 2);
msc_sch_wb.atp_debug('Number of rows deleted := ' || SQL%ROWCOUNT);
'Insert into mrp_atp_schedule_temp' || L_source_DBLINK ||
' (mdi_rowid,
session_id,
scenario_id,
sr_instance_id,
inventory_item_id,
inventory_item_name,
source_organization_id,
source_organization_code,
order_header_id,
Demand_Source_Delivery,
Demand_Source_Type,
atp_lead_time,
order_line_id,
order_number,
calling_module,
customer_id,
customer_site_id,
destination_time_zone,
quantity_ordered,
uom_code,
requested_ship_date,
requested_arrival_date,
latest_acceptable_date,
delivery_lead_time,
freight_carrier,
ship_method,
demand_class,
ship_set_name,
arrival_set_name,
override_flag,
action,
scheduled_ship_date,
available_quantity,
requested_date_quantity,
group_ship_date,
group_arrival_date,
vendor_id,
vendor_name,
vendor_site_id,
vendor_site_name,
insert_flag,
error_code,
error_Message,
status_flag,
oe_flag,
end_pegging_id,
old_source_organization_id,
old_demand_class,
scheduled_arrival_date,
attribute_06,
organization_id,
substitution_typ_code,
req_item_detail_flag,
old_inventory_item_id,
request_item_id,
request_item_name,
req_item_req_date_qty,
req_item_available_date_qty,
req_item_available_date,
sales_rep,
customer_contact,
compile_designator,
subst_flag';
last_update_date,
last_updated_by,
last_update_login,
diagnostic_atp_flag,
sequence_number,
firm_flag,
order_line_number,
option_number,
shipment_number,
item_desc,
customer_name,
customer_location,
old_line_schedule_date,
old_source_organization_code,
firm_source_org_id,
firm_source_org_code,
firm_ship_date,
firm_arrival_date,
ship_method_text,
ship_set_id,
arrival_set_id,
project_id,
task_id,
project_number,
task_number,
Top_Model_line_id,
ATO_Parent_Model_Line_Id,
ATO_Model_Line_Id,
Parent_line_id,
match_item_id,
matched_item_name,
Config_item_line_id,
Validation_Org,
Component_Sequence_ID,
Component_Code,
line_number,
included_item_flag,
atp_flag,
atp_components_flag,
wip_supply_type,
bom_item_type,
pick_components_flag,
OSS_ERROR_CODE,
original_request_date,
mandatory_item_flag,
CASCADE_MODEL_INFO_TO_COMP,
INTERNAL_ORG_ID, -- Bug 3449812
first_valid_ship_arrival_date, -- bug 3328421
customer_country, --2814895
customer_state, --2814895
customer_city, --2814895
customer_postal_code, --2814895
party_site_id, --2814895
part_of_set --4500382
';
' ) select
mdi_rowid,
session_id,
scenario_id,
sr_instance_id,
inventory_item_id,
inventory_item_name,
source_organization_id,
source_organization_code,
order_header_id,
Demand_Source_Delivery,
Demand_Source_Type,
atp_lead_time,
order_line_id,
order_number,
calling_module,
customer_id,
customer_site_id,
destination_time_zone,
quantity_ordered,
uom_code,
requested_ship_date,
requested_arrival_date,
latest_acceptable_date,
delivery_lead_time,
freight_carrier,
ship_method,
demand_class,
ship_set_name,
arrival_set_name,
override_flag,
action,
scheduled_ship_date,
available_quantity,
requested_date_quantity,
group_ship_date,
group_arrival_date,
vendor_id,
vendor_name,
vendor_site_id,
vendor_site_name,
insert_flag,
error_code,
error_Message, ' ||
l_tnsfer_sts_flag || ',
oe_flag,
end_pegging_id,
old_source_organization_id,
old_demand_class,
scheduled_arrival_date,
attribute_06,
organization_id,
substitution_typ_code,
req_item_detail_flag,
old_inventory_item_id,
request_item_id,
request_item_name,
req_item_req_date_qty,
req_item_available_date_qty,
req_item_available_date,
sales_rep,
customer_contact,
compile_designator,
subst_flag';
last_update_date,
last_updated_by,
last_update_login,
diagnostic_atp_flag,
sequence_number,
firm_flag,
order_line_number,
option_number,
shipment_number,
item_desc,
customer_name,
customer_location,
old_line_schedule_date,
old_source_organization_code,
firm_source_org_id,
firm_source_org_code,
firm_ship_date,
firm_arrival_date,
ship_method_text,
ship_set_id,
arrival_set_id,
project_id,
task_id,
project_number,
task_number,
Top_Model_line_id,
ATO_Parent_Model_Line_Id,
ATO_Model_Line_Id,
Parent_line_id,
match_item_id,
matched_item_name,
Config_item_line_id,
Validation_Org,
Component_Sequence_ID,
Component_Code,
line_number,
included_item_flag,
atp_flag,
atp_components_flag,
wip_supply_type,
bom_item_type,
pick_components_flag,
OSS_ERROR_CODE,
original_request_date,
mandatory_item_flag,
CASCADE_MODEL_INFO_TO_COMP,
INTERNAL_ORG_ID, -- Bug 3449812
first_valid_ship_arrival_date, --bug 3328421
customer_country, --2814895
customer_state, --2814895
customer_city, --2814895
customer_postal_code, --2814895
party_site_id, --2814895
part_of_set --4500382
';