[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ATP_level
-- ,order_line_id
,scenario_id
,inventory_item_id
,request_item_id
,organization_id
,supplier_id
,supplier_site_id
,department_id
,resource_id
,supply_demand_date
,identifier1
,identifier2
,SUM(DECODE(supply_demand_type, 1, supply_demand_quantity, 0))
total_demand_quantity
,SUM(DECODE(supply_demand_type, 2, supply_demand_quantity, 0))
total_supply_quantity
,SUM(supply_demand_quantity)
BULK COLLECT INTO
x_atp_period.Level,
-- x_atp_period.Identifier,
x_atp_period.Scenario_Id,
x_atp_period.Inventory_Item_Id,
x_atp_period.Request_Item_Id,
x_atp_period.Organization_id,
x_atp_period.Supplier_ID,
x_atp_period.Supplier_Site_ID,
x_atp_period.Department_id,
x_atp_period.Resource_id,
x_atp_period.Period_Start_Date,
x_atp_period.Identifier1,
x_atp_period.Identifier2,
x_atp_period.Total_Demand_Quantity,
x_atp_period.Total_Supply_Quantity,
x_atp_period.Period_Quantity
FROM msc_atp_sd_details_temp
GROUP BY
supply_demand_date
,ATP_level
-- ,order_line_id
,scenario_id
,inventory_item_id
,request_item_id
,organization_id
,supplier_id
,supplier_site_id
,department_id
,resource_id
,identifier1
,identifier2
ORDER BY supply_demand_date;
SELECT
ATP_level
,scenario_id
,inventory_item_id
,request_item_id
,organization_id
,supplier_id
,supplier_site_id
,department_id
,resource_id
,supply_demand_date
,identifier1
,identifier2
,SUM(DECODE(supply_demand_type, 1, allocated_quantity, 0))
total_demand_quantity
,SUM(DECODE(supply_demand_type, 2, allocated_quantity, 0))
total_supply_quantity
,SUM(allocated_quantity)
BULK COLLECT INTO
x_atp_period.Level,
x_atp_period.Scenario_Id,
x_atp_period.Inventory_Item_Id,
x_atp_period.Request_Item_Id,
x_atp_period.Organization_id,
x_atp_period.Supplier_ID,
x_atp_period.Supplier_Site_ID,
x_atp_period.Department_id,
x_atp_period.Resource_id,
x_atp_period.Period_Start_Date,
x_atp_period.Identifier1,
x_atp_period.Identifier2,
x_atp_period.Total_Demand_Quantity,
x_atp_period.Total_Supply_Quantity,
x_atp_period.Period_Quantity
FROM msc_atp_sd_details_temp
GROUP BY
supply_demand_date
,ATP_level
,scenario_id
,inventory_item_id
,request_item_id
,organization_id
,supplier_id
,supplier_site_id
,department_id
,resource_id
,identifier1
,identifier2
ORDER BY supply_demand_date;
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_ship_to_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;
DELETE MSC_SHIP_SET_TEMP;
INSERT INTO MSC_SHIP_SET_TEMP(INVENTORY_ITEM_ID, VISITED_FLAG, MIN_REGION_VALUE, line_id)
VALUES (l_dest_ship_set_item_list(j), 0, 0, l_line_ids(j));
DELETE MSC_SHIP_SET_TEMP;
SELECT
nvl(s.source_organization_id, -1),
-- nvl(s.sr_instance_id2,-1),
decode(nvl(min(s.source_type),
decode(min(s.source_organization_id),
to_number(null), 3,
1)),
3, p_instance_id,
nvl(s.sr_instance_id2,-1)),
-- Bug 3270842 : For buy cases always select the passed instance id as source's
-- instance id for buy sources
-- Bug 3517529: For Buy cases if supplier_id and supplier_site_id are NULL, then
-- they are changed to -99 to identify the Buy case, otherwise it will
-- be identified as a Transfer case and ATP_Check will be called recursively.
nvl(supplier_id,decode(source_type,MSC_ATP_PVT.BUY,-99,-1)),
nvl(supplier_site_id,decode(source_type,MSC_ATP_PVT.BUY,-99,-1)),
sum(nvl(s.rank, 0) + 1 - nvl(s.allocation_percent,0)/1000), --2910418
nvl(min(s.source_type),
decode(min(s.source_organization_id),to_number(null), 3, 1)),
0,
NVL(MAX(s.avg_transit_lead_time), 0), -- dsting 2614883
NVL(s.ship_method, '@@@'), -- For ship_rec_cal
DECODE(mtps.shipping_control,'BUYER',1,2) -- For supplier intransit LT project - 1:Ship Cap, 2:Dock Cap
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
msc_item_sourcing s,
msc_ship_set_temp msst,
msc_trading_partner_sites mtps -- For supplier intransit LT project
WHERE
s.inventory_item_id = msst.inventory_item_id
AND s.organization_id = p_organization_id
AND s.sr_instance_id = p_instance_id
AND s.plan_id = p_plan_id
AND s.supplier_site_id = mtps.partner_site_id (+) -- For supplier intransit LT project
--bug 3373166: Use assignmnet set for plan sourcing
AND NVL(s.assignment_set_type, 1) = 1
-- Bug 3787821: Putting the Date check if recieving party is org
AND TRUNC(NVL(s.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(s.EFFECTIVE_DATE) <= l_sysdate
-- ATP4drp Circular sources applicable for DRP plans not supported by ATP.
AND NVL(s.circular_src, 2) <> 1
GROUP BY
s.source_organization_id,
s.sr_instance_id2,
s.supplier_id,
s.supplier_site_id,
DECODE(mtps.shipping_control,'BUYER',1,2) -- For supplier intransit LT project
HAVING count(*) = l_count
ORDER BY 5;
SELECT
nvl(s.source_organization_id, p_organization_id),
nvl(s.source_org_instance_id, -1),
nvl(s.vendor_id, -1),
nvl(s.vendor_site_id, -1),
sum(nvl(s.rank, 0) + 1 - nvl(s.allocation_percent,0)/1000), --2910418
nvl(min(s.source_type),
decode(s.source_organization_id, to_number(null), 3, 1)),
0,
NVL(MAX(s.avg_transit_lead_time), 0), -- dsting 2614883
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM msc_sources_v s,
msc_ship_set_temp msst
WHERE s.inventory_item_id = msst.inventory_item_id
AND s.organization_id = p_organization_id
AND s.sr_instance_id = p_instance_id
AND s.assignment_set_id = p_assign_set_id
-- Bug 3787821: Putting the Date check if recieving party is org
AND TRUNC(NVL(s.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(s.EFFECTIVE_DATE) <= l_sysdate
AND NVL(s.source_organization_id,
decode(s.source_type, 2, p_organization_id, -1)) <> -1
GROUP BY s.source_organization_id,
s.source_org_instance_id,
s.vendor_id,
s.vendor_site_id
HAVING count(*) = l_count
ORDER BY 5;
SELECT nvl(s.source_organization_id, -1),
decode(decode(s.organization_id,
s.source_organization_id, 2,
nvl(s.source_type,
decode(source_organization_id,
to_number(null), 3, 1))),
3, p_instance_id,
nvl(sr_instance_id2, -1)),
-- Bug 3270842 : For buy cases always select the passed instance id as source's
-- instance id for buy sources
-- Bug 3517529: For Buy cases if supplier_id and supplier_site_id are NULL, then
-- they are changed to -99 to identify the Buy case, otherwise it will
-- be identified as a Transfer case and ATP_Check will be called recursively.
nvl(supplier_id,decode(source_type,MSC_ATP_PVT.BUY,-99,-1)),
nvl(supplier_site_id,decode(source_type,MSC_ATP_PVT.BUY,-99,-1)),
nvl(s.rank, -1),
-- 2936920. treat as a make if org/src org are the same
decode(s.organization_id, s.source_organization_id, 2,
nvl(s.source_type,
decode(source_organization_id, to_number(null), 3, 1))),
0,
NVL(s.avg_transit_lead_time, 0), -- dsting 2614883
NVL(s.ship_method, '@@@'),
DECODE(mtps.shipping_control,'BUYER',1,2) -- For supplier intransit LT project - 1:Ship Cap, 2:Dock Cap
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM msc_item_sourcing s,
msc_trading_partner_sites mtps -- For supplier intransit LT project
WHERE s.inventory_item_id = l_inv_item_id
AND s.organization_id = p_organization_id
AND s.sr_instance_id = p_instance_id
/*AND s.assignment_set_id = p_assign_set_id*/
AND s.plan_id = p_plan_id
AND s.supplier_site_id = mtps.partner_site_id (+) -- For supplier intransit LT project
--bug 3373166: Use assignmnet set for plan sourcing
AND NVL(s.assignment_set_type, 1) = 1
-- Bug 3787821: Putting the Date check if recieving party is org
AND TRUNC(NVL(s.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(s.EFFECTIVE_DATE) <= l_sysdate
-- ATP4drp Circular sources applicable for DRP plans not supported by ATP.
AND NVL(s.circular_src, 2) <> 1
ORDER BY rank asc, allocation_percent desc;
SELECT nvl(s.source_organization_id, p_organization_id), -- 1460753
nvl(s.source_org_instance_id, -1),
nvl(s.vendor_id, -1),
nvl(s.vendor_site_id, -1),
nvl(s.rank, -1),
nvl(s.source_type,
decode(source_organization_id, to_number(null), 3, 1)),
0,
NVL(s.avg_transit_lead_time, -1),
NVL(s.ship_method, '@@@'),
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM msc_sources_v s
WHERE s.inventory_item_id = l_inv_item_id
AND s.organization_id = p_organization_id
AND s.sr_instance_id = p_instance_id
AND s.assignment_set_id = p_assign_set_id
-- Bug 3787821: Putting the Date check if recieving party is org
AND TRUNC(NVL(s.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(s.EFFECTIVE_DATE) <= l_sysdate
-- bug 1460753
AND NVL(s.source_organization_id,
decode(s.source_type,MSC_ATP_PVT.MAKE,p_organization_id,-1)) <> -1
ORDER BY rank asc, allocation_percent desc;
SELECT
NVL(SOURCEORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCEORG.SOURCE_ORG_INSTANCE_ID,-1),
NVL(SOURCEORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCEORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCEORG.RANK, 0)),
nvl(min(sourceorg.source_type),
decode(sourceorg.source_organization_id,
to_number(null), 3, 1)),
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
msc_sourcing_rules msr,
msc_sr_receipt_org receiptorg,
msc_sr_source_org sourceorg,
msc_sr_assignments msa,
msc_system_items msi,
msc_ship_set_temp msst
WHERE
msa.assignment_type = 3
AND msa.assignment_set_id = p_assign_set_id
AND msa.inventory_item_id = msst.inventory_item_id
AND msa.sourcing_rule_id = msr.sourcing_rule_id
AND msr.status = 1
AND msr.sourcing_rule_type = 1
AND msr.sourcing_rule_id = receiptorg.sourcing_rule_id
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(receiptorg.disable_date, l_sysdate)) >= l_sysdate
AND TRUNC(receiptorg.effective_date) <= l_sysdate
AND receiptorg.sr_receipt_id = sourceorg.sr_receipt_id
AND sourceorg.sr_instance_id = msi.sr_instance_id
AND sourceorg.source_organization_id = msi.organization_id
-- ATP4drp Circular sources not supported by ATP.
--AND NVL(sourceorg.circular_src, 2) <> 1
--Bug4567833
AND NVL(sourceorg.circular_src, 'N') <> 'Y'
AND msa.inventory_item_id = msi.inventory_item_id
AND msi.plan_id = -1
GROUP BY
SOURCEORG.SOURCE_ORGANIZATION_ID,
SOURCEORG.SOURCE_ORG_INSTANCE_ID,
SOURCEORG.SOURCE_PARTNER_ID,
SOURCEORG.SOURCE_PARTNER_SITE_ID
HAVING count(*) = l_count
ORDER BY 5;
SELECT
NVL(SOURCEORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCEORG.SOURCE_ORG_INSTANCE_ID,-1),
NVL(SOURCEORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCEORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCEORG.RANK, 0)),
nvl(min(sourceorg.source_type),
decode(sourceorg.source_organization_id,
to_number(null), 3, 1)),
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM msc_sourcing_rules msr,
msc_sr_receipt_org receiptorg,
msc_sr_source_org sourceorg,
msc_sr_assignments msa,
msc_item_categories cat,
msc_ship_set_temp msst
WHERE msa.assignment_type = 2 and
msa.assignment_set_id = p_assign_set_id and
msa.inventory_item_id = msst.inventory_item_id
AND msa.sourcing_rule_id = msr.sourcing_rule_id
AND msr.status = 1
AND msr.sourcing_rule_type = 1
AND msr.sourcing_rule_id = receiptorg.sourcing_rule_id
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(receiptorg.disable_date, l_sysdate)) >= l_sysdate
AND TRUNC(receiptorg.effective_date) <= l_sysdate
AND receiptorg.sr_receipt_id = sourceorg.sr_receipt_id
AND msa.category_name = cat.category_name
AND msa.category_set_id = cat.category_set_id
AND msa.inventory_item_id = cat.inventory_item_id
AND sourceorg.source_organization_id = cat.organization_id
AND sourceorg.sr_instance_id = cat.sr_instance_id
-- ATP4drp Circular sources not supported by ATP.
--AND NVL(sourceorg.circular_src, 2) <> 1
--Bug4567833
AND NVL(sourceorg.circular_src, 'N') <> 'Y'
GROUP BY SOURCEORG.SOURCE_ORGANIZATION_ID,
SOURCEORG.SOURCE_ORG_INSTANCE_ID,
SOURCEORG.SOURCE_PARTNER_ID,
SOURCEORG.SOURCE_PARTNER_SITE_ID
HAVING count(*) = l_count
ORDER BY 5;
SELECT
NVL(SOURCEORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCEORG.SOURCE_ORG_INSTANCE_ID,-1),
NVL(SOURCEORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCEORG.SOURCE_PARTNER_SITE_ID, -1),
NVL(SOURCEORG.RANK, 0),
nvl(sourceorg.source_type,
decode(sourceorg.source_organization_id,
to_number(null), 3, 1)),
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
msc_sourcing_rules msr,
msc_sr_receipt_org receiptorg,
msc_sr_source_org sourceorg,
msc_sr_assignments msa,
msc_system_items msi,
msc_ship_set_temp msst
WHERE
msa.assignment_type = 1
AND msa.assignment_set_id = p_assign_set_id
AND msa.sourcing_rule_id = msr.sourcing_rule_id
AND msr.status = 1
AND msr.sourcing_rule_type = 2
AND msr.sourcing_rule_id = receiptorg.sourcing_rule_id
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(receiptorg.disable_date, l_sysdate)) >= l_sysdate
AND TRUNC(receiptorg.effective_date) <= l_sysdate
AND receiptorg.sr_receipt_org = l_organization_id
AND receiptorg.receipt_org_instance_id = p_instance_id
AND receiptorg.sr_receipt_id = sourceorg.sr_receipt_id
AND sourceorg.source_organization_id = msi.ORGANIZATION_ID
AND sourceorg.sr_instance_id = msi.sr_instance_id
-- ATP4drp Circular sources not supported by ATP.
--AND NVL(sourceorg.circular_src, 2) <> 1
--Bug4567833
AND NVL(sourceorg.circular_src, 'N') <> 'Y'
AND msi.inventory_item_id = msst.inventory_item_id
AND msi.plan_id = -1
ORDER BY rank asc, allocation_percent desc;
SELECT
NVL(SOURCEORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCEORG.SOURCE_ORG_INSTANCE_ID,-1),
NVL(SOURCEORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCEORG.SOURCE_PARTNER_SITE_ID, -1),
NVL(SOURCEORG.RANK, 0),
nvl(sourceorg.source_type,
decode(sourceorg.source_organization_id,
to_number(null), 3, 1)),
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
msc_sourcing_rules msr,
msc_sr_receipt_org receiptorg,
msc_sr_source_org sourceorg,
msc_sr_assignments msa,
msc_system_items msi,
msc_ship_set_temp msst
WHERE
msa.assignment_type = 1
AND msa.assignment_set_id = p_assign_set_id
AND msa.sourcing_rule_id = msr.sourcing_rule_id
AND msr.status = 1
AND msr.sourcing_rule_type = 1
AND msr.sourcing_rule_id = receiptorg.sourcing_rule_id
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(receiptorg.disable_date, l_sysdate)) >= l_sysdate
AND TRUNC(receiptorg.effective_date) <= l_sysdate
AND receiptorg.sr_receipt_id = sourceorg.sr_receipt_id
AND sourceorg.source_organization_id = msi.ORGANIZATION_ID
AND sourceorg.sr_instance_id = msi.sr_instance_id
-- ATP4drp Circular sources not supported by ATP.
--AND NVL(sourceorg.circular_src, 2) <> 1
--Bug4567833
AND NVL(sourceorg.circular_src, 'N') <> 'Y'
AND msi.inventory_item_id = msst.inventory_item_id
AND msi.plan_id = -1
ORDER BY rank asc, allocation_percent desc;
SELECT NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,-1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
NVL(SOURCE_ORG.RANK, 0),
nvl(source_org.source_type,
decode(source_org.source_organization_id, to_number(null), 3, 1)),
0,
-1,
'XYZ',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM msc_sourcing_rules msr,
msc_sr_receipt_org receipt_org,
msc_sr_source_org source_org,
msc_sr_assignments msa,
msc_system_items msi
WHERE msa.assignment_type = 3 and
msa.assignment_set_id = p_assign_set_id and
msa.inventory_item_id = l_inv_item_id and
msa.sourcing_rule_id = msr.sourcing_rule_id and
msr.status = 1 and
msr.sourcing_rule_type = 1 and
msr.sourcing_rule_id = receipt_org.sourcing_rule_id and
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
TRUNC(NVL(receipt_org.disable_date, l_sysdate )) >= l_sysdate and
TRUNC(receipt_org.effective_date) <= l_sysdate and
receipt_org.sr_receipt_id = source_org.sr_receipt_id and
source_org.sr_instance_id = msi.sr_instance_id and
source_org.source_organization_id = msi.ORGANIZATION_ID and
-- ATP4drp Circular sources not supported by ATP.
--NVL(source_org.circular_src, 2) <> 1 AND
--Bug4567833
NVL(source_org.circular_src, 'N') <> 'Y' AND
msa.inventory_item_id = msi.inventory_item_id and
msi.plan_id = -1
ORDER BY rank asc, allocation_percent desc;
SELECT NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,-1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
NVL(SOURCE_ORG.RANK, 0),
nvl(source_org.source_type,
decode(source_org.source_organization_id, to_number(null), 3, 1)),
0,
-1,
'XYZ',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM msc_sourcing_rules msr,
msc_sr_receipt_org receipt_org,
msc_sr_source_org source_org,
msc_sr_assignments msa,
msc_item_categories cat
WHERE msa.assignment_type = 2 and
msa.assignment_set_id = p_assign_set_id and
msa.inventory_item_id = l_inv_item_id and
msa.sourcing_rule_id = msr.sourcing_rule_id and
msr.status = 1 and
msr.sourcing_rule_type = 1 and
msr.sourcing_rule_id = receipt_org.sourcing_rule_id and
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
TRUNC(NVL(receipt_org.disable_date, l_sysdate)) >= l_sysdate and
TRUNC(receipt_org.effective_date) <= l_sysdate and
receipt_org.sr_receipt_id = source_org.sr_receipt_id and
msa.category_name = cat.category_name and
msa.category_set_id = cat.category_set_id and
msa.inventory_item_id = cat.inventory_item_id and
source_org.source_organization_id = cat.organization_id and
source_org.sr_instance_id = cat.sr_instance_id and
-- ATP4drp Circular sources not supported by ATP.
--AND NVL(source_org.circular_src, 2) <> 1
--Bug4567833
NVL(source_org.circular_src, 'N') <> 'Y'
ORDER BY rank asc, allocation_percent desc;
SELECT NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,-1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
NVL(SOURCE_ORG.RANK, 0),
nvl(source_org.source_type,
decode(source_org.source_organization_id, to_number(null), 3, 1)),
0,
-1,
'XYZ',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM msc_sourcing_rules msr,
msc_sr_receipt_org receipt_org,
msc_sr_source_org source_org,
msc_sr_assignments msa,
msc_system_items msi
WHERE msa.assignment_type = 1 and
msa.assignment_set_id = p_assign_set_id and
---msa.inventory_item_id = l_inv_item_id and
msa.sourcing_rule_id = msr.sourcing_rule_id and
msr.status = 1 and
msr.sourcing_rule_type = 2 and
msr.sourcing_rule_id = receipt_org.sourcing_rule_id and
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
TRUNC(NVL(receipt_org.disable_date, l_sysdate)) >= l_sysdate and
TRUNC(receipt_org.effective_date) <= l_sysdate and
receipt_org.SR_RECEIPT_ORG = l_organization_id and
receipt_org.RECEIPT_ORG_INSTANCE_ID = p_instance_id and
receipt_org.sr_receipt_id = source_org.sr_receipt_id and
source_org.source_organization_id = msi.ORGANIZATION_ID and
source_org.sr_instance_id = msi.sr_instance_id and
-- ATP4drp Circular sources not supported by ATP.
--NVL(source_org.circular_src, 2) <> 1 AND
--Bug4567833
NVL(source_org.circular_src, 'N') <> 'Y' AND
msi.inventory_item_id = l_inv_item_id and
msi.plan_id = -1
ORDER BY rank asc, allocation_percent desc;
SELECT NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,-1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
NVL(SOURCE_ORG.RANK, 0),
nvl(source_org.source_type,
decode(source_org.source_organization_id, to_number(null), 3, 1)),
0,
-1,
'XYZ',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM msc_sourcing_rules msr,
msc_sr_receipt_org receipt_org,
msc_sr_source_org source_org,
msc_sr_assignments msa,
msc_system_items msi
WHERE msa.assignment_type = 1 and
msa.assignment_set_id = p_assign_set_id and
---msa.inventory_item_id = l_inv_item_id and
msa.sourcing_rule_id = msr.sourcing_rule_id and
msr.status = 1 and
msr.sourcing_rule_type = 1 and
msr.sourcing_rule_id = receipt_org.sourcing_rule_id and
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
TRUNC(NVL(receipt_org.disable_date, l_sysdate)) >= l_sysdate and
TRUNC(receipt_org.effective_date) <= l_sysdate and
receipt_org.sr_receipt_id = source_org.sr_receipt_id and
source_org.source_organization_id = msi.ORGANIZATION_ID and
source_org.sr_instance_id = msi.sr_instance_id and
-- ATP4drp Circular sources not supported by ATP.
--NVL(source_org.circular_src, 2) <> 1 AND
--Bug4567833
NVL(source_org.circular_src, 'N') <> 'Y' AND
msi.inventory_item_id = l_inv_item_id and
msi.plan_id = -1
ORDER BY rank asc, allocation_percent desc;
SELECT department_code,
resource_code
INTO x_department_code,
x_resource_code
FROM msc_department_resources
WHERE sr_instance_id = p_instance_id
AND organization_id = p_organization_id
AND plan_id = -1
AND department_id = p_department_id
AND resource_id = p_resource_id;
SELECT default_atp_rule_id,
calendar_code,
calendar_exception_set_id,
default_demand_class,
organization_code
INTO x_default_atp_rule_id,
x_calendar_code,
x_calendar_exception_set_id,
x_default_demand_class,
x_org_code
FROM msc_trading_partners
WHERE sr_tp_id = p_organization_id
AND sr_instance_id = p_instance_id
AND partner_type = 3;
SELECT items.uom_code,
conversion_rate
INTO x_primary_uom_code,
x_conversion_rate
FROM msc_uom_conversions_view mucv,
msc_system_items items
WHERE items.sr_inventory_item_id = p_inventory_item_id
AND items.organization_id = p_organization_id
AND items.plan_id = -1
AND items.sr_instance_id = p_instance_id
AND mucv.uom_code = p_uom_code
AND mucv.primary_uom_code = items.uom_code
AND mucv.inventory_item_id = items.inventory_item_id
AND mucv.organization_id = items.organization_id
AND mucv.sr_instance_id = items.sr_instance_id;
SELECT primary_uom_code,
conversion_rate
INTO x_primary_uom_code,
x_conversion_rate
FROM msc_uom_conversions_view mucv
WHERE mucv.uom_code = p_uom_code
AND mucv.primary_uom_code = MSC_ATP_PVT.G_ITEM_INFO_REC.uom_code
AND mucv.inventory_item_id = MSC_ATP_PVT.G_ITEM_INFO_REC.dest_inv_item_id
AND mucv.organization_id = p_organization_id
AND mucv.sr_instance_id = p_instance_id;
SELECT plan_id
INTO x_plan_info_rec.plan_id
FROM msc_system_items
WHERE sr_instance_id = p_instance_id
AND organization_id = p_organization_id
AND sr_inventory_item_id = p_inventory_item_id
AND plan_id = p_parent_plan_id;
SELECT demand_class_atp_flag
INTO l_dc_atp_flag
FROM msc_atp_rules
WHERE sr_instance_id = p_instance_id
AND rule_id IN (
SELECT distinct NVL(mi.atp_rule_id, tp.default_atp_rule_id)
FROM msc_system_items mi,
msc_trading_partners tp
WHERE mi.organization_id = tp.sr_tp_id
AND mi.sr_instance_id = tp.sr_instance_id
AND tp.partner_type = 3
AND mi.plan_id = -1
AND mi.sr_instance_id = p_instance_id
AND mi.organization_id = p_organization_id
AND mi.sr_inventory_item_id = p_inventory_item_id);
/*SELECT plan_id
-- INTO x_plan_id commented for bug 2392456
INTO x_plan_info_rec.plan_id -- changed for bug 2392456
FROM msc_atp_plan_sn
WHERE demand_class = p_demand_class
AND sr_instance_id = p_instance_id
AND organization_id = p_organization_id
AND sr_inventory_item_id = p_inventory_item_id; */
SELECT plan_id
INTO x_plan_info_rec.plan_id
FROM
(SELECT plan_id,Rank,completion_date
FROM msc_atp_plan_sn
WHERE demand_class = p_demand_class
AND sr_instance_id = p_instance_id
AND organization_id = p_organization_id
AND sr_inventory_item_id = p_inventory_item_id
ORDER BY Rank asc,completion_date desc,plan_id asc)
WHERE ROWNUM=1;
/*SELECT plan_id
-- INTO x_plan_id commented for bug 2392456
INTO x_plan_info_rec.plan_id -- changed for bug 2392456
FROM msc_atp_plan_sn
WHERE demand_class IS NULL
AND sr_instance_id = p_instance_id
AND organization_id = p_organization_id
AND sr_inventory_item_id = p_inventory_item_id;
select plan_id
bulk collect into l_plan_ids
from
(
SELECT plan_id,Rank,completion_date
FROM msc_atp_plan_sn
WHERE demand_class IS NULL
AND sr_instance_id = p_instance_id
AND organization_id = p_organization_id
AND sr_inventory_item_id = p_inventory_item_id
UNION ALL
SELECT -200 PLAN_ID, 20, to_date(null)
from msc_atp_plan_sn
WHERE rownum = 1
ORDER BY Rank asc,completion_date desc,plan_id asc
);
msc_sch_wb.atp_debug('Get_Plan_Info: ' || 'selected plan_id : '||x_plan_info_rec.plan_id);
SELECT plans.curr_assignment_set_id,
plans.compile_designator,
x_plan_info_rec.plan_id,
NVL(plans.summary_flag, 1),
NVL(plans.use_end_item_substitutions, 2),
NVL(plans.copy_plan_id,-1),
-- second plan for 24x7
plans2.curr_assignment_set_id,
plans2.compile_designator,
plans2.plan_id,
NVL(plans2.summary_flag, 1),
NVL(plans2.use_end_item_substitutions, 2),
NVL(plans2.copy_plan_id,-1),
-- Supplier Capacity and Lead Time (SCLT) Proj.
plans.sr_instance_id,
plans.organization_id,
trunc(plans.plan_start_date),
trunc(plans.cutoff_date),
plans.curr_cutoff_date,
DECODE(plans.plan_type, 4, 2,
DECODE(plans.daily_material_constraints, 1, 1,
DECODE(plans.daily_resource_constraints, 1, 1,
DECODE(plans.weekly_material_constraints, 1, 1,
DECODE(plans.weekly_resource_constraints, 1, 1,
DECODE(plans.period_material_constraints, 1, 1,
DECODE(plans.period_resource_constraints, 1, 1, 2)
)
)
)
)
)
), -- 2859130
plans2.sr_instance_id,
plans2.organization_id,
trunc(plans2.plan_start_date),
trunc(plans2.cutoff_date),
plans2.curr_cutoff_date,
DECODE(plans2.plan_type, 4, 2,
DECODE(plans2.daily_material_constraints, 1, 1,
DECODE(plans2.daily_resource_constraints, 1, 1,
DECODE(plans2.weekly_material_constraints, 1, 1,
DECODE(plans2.weekly_resource_constraints, 1, 1,
DECODE(plans2.period_material_constraints, 1, 1,
DECODE(plans2.period_resource_constraints, 1, 1, 2)
)
)
)
)
)
), -- 2859130 ,
plans.schedule_by,
plans2.schedule_by,
-- ship_rec_cal changes begin
NVL(plans.daily_material_constraints, 2),
--bug 4100346: For unconstrained plan always enforce purchasing lead time
DECODE(plans.plan_type, 4, 1,
DECODE(plans.daily_material_constraints, 1, NVL(plans.enforce_pur_lt_constraints, 2),
DECODE(plans.daily_resource_constraints, 1, NVL(plans.enforce_pur_lt_constraints, 2),
DECODE(plans.weekly_material_constraints, 1, NVL(plans.enforce_pur_lt_constraints, 2),
DECODE(plans.weekly_resource_constraints, 1, NVL(plans.enforce_pur_lt_constraints, 2),
DECODE(plans.period_material_constraints, 1, NVL(plans.enforce_pur_lt_constraints, 2),
DECODE(plans.period_resource_constraints, 1, NVL(plans.enforce_pur_lt_constraints, 2), 1)
)
)
)
)
)
),
--NVL(plans.enforce_pur_lt_constraints, 2),
NVL(plans2.daily_material_constraints, 2),
--bug 4100346: For unconstrained plan always enforce purchasing lead time
--NVL(plans2.enforce_pur_lt_constraints, 2),
DECODE(plans2.plan_type, 4, 1,
DECODE(plans2.daily_material_constraints, 1, NVL(plans2.enforce_pur_lt_constraints, 2),
DECODE(plans2.daily_resource_constraints, 1, NVL(plans2.enforce_pur_lt_constraints, 2),
DECODE(plans2.weekly_material_constraints, 1, NVL(plans2.enforce_pur_lt_constraints, 2),
DECODE(plans2.weekly_resource_constraints, 1, NVL(plans2.enforce_pur_lt_constraints, 2),
DECODE(plans2.period_material_constraints, 1, NVL(plans2.enforce_pur_lt_constraints, 2),
DECODE(plans2.period_resource_constraints, 1, NVL(plans2.enforce_pur_lt_constraints, 2), 1)
)
)
)
)
)
),
-- ship_rec_cal changes end
-- ATP4drp changes begin
NVL(plans.plan_type, 1), -- Default is MRP plan
NVL(plans2.plan_type, 1),
plans.itf_horiz_days, -- Obtain the ITF_HORIZ_DAYS
plans2.itf_horiz_days
-- ATP4drp changes end
INTO x_plan_info_rec.assignment_set_id,
x_plan_info_rec.plan_name,
x_plan_info_rec.plan_id,
--l_summary_flag,
x_plan_info_rec.summary_flag,
--MSC_ATP_PVT.G_PLAN_SUBST_FLAG,
x_plan_info_rec.subst_flag,
x_plan_info_rec.copy_plan_id,
l_plan_info_rec.assignment_set_id,
l_plan_info_rec.plan_name,
l_plan_info_rec.plan_id,
l_plan_info_rec.summary_flag,
l_plan_info_rec.subst_flag,
l_plan_info_rec.copy_plan_id,
-- Supplier Capacity and Lead Time (SCLT) Proj.
x_plan_info_rec.sr_instance_id,
x_plan_info_rec.organization_id,
x_plan_info_rec.plan_start_date,
x_plan_info_rec.plan_cutoff_date,
x_plan_info_rec.curr_cutoff_date,
x_plan_info_rec.optimized_plan, -- 2859130
l_plan_info_rec.sr_instance_id,
l_plan_info_rec.organization_id,
l_plan_info_rec.plan_start_date,
l_plan_info_rec.plan_cutoff_date,
l_plan_info_rec.curr_cutoff_date,
l_plan_info_rec.optimized_plan,-- 2859130
--plan by request date changes begin
x_plan_info_rec.schedule_by_date_type,
l_plan_info_rec.schedule_by_date_type,
--plan by request date changes end
-- ship_rec_cal changes begin
x_plan_info_rec.enforce_sup_capacity,
x_plan_info_rec.enforce_pur_lead_time,
l_plan_info_rec.enforce_sup_capacity,
l_plan_info_rec.enforce_pur_lead_time,
-- ship_rec_cal changes end
-- ATP4drp changes begin
x_plan_info_rec.plan_type,
l_plan_info_rec.plan_type,
x_plan_info_rec.itf_horiz_days,
l_plan_info_rec.itf_horiz_days
-- ATP4drp changes end
FROM msc_plans plans,
msc_plans plans2
WHERE plans.plan_id = x_plan_info_rec.plan_id
AND plans.plan_completion_date is not null
AND plans.data_completion_date is not null
and plans.plan_id = plans2.copy_plan_id (+);
SELECT inventory_item_id,quantity
BULK COLLECT INTO l_coproducts_rec.inventory_item_id,
l_coproducts_rec.quantity
FROM (SELECT MBC.inventory_item_id,
ABS(NVL(MBC.usage_quantity,1) * p_assembly_qty/
NVL(MB.assembly_quantity,1)) quantity
FROM MSC_SYSTEM_ITEMS I,
MSC_BOMS MB,
MSC_BOM_COMPONENTS MBC,
MSC_CALENDAR_DATES C,
MSC_TRADING_PARTNERS TP
WHERE I.plan_id = p_plan_id and
I.sr_instance_id = p_instance_id and
I.organization_id = p_org_id and
I.sr_inventory_item_id = p_inv_item_id and
--MB.plan_id = p_plan_id and
MB.plan_id = I.plan_id and
--MB.assembly_item_id = I.inventory_item_id and
MB.assembly_item_id = I.inventory_item_id and
--MB.organization_id = p_org_id and
MB.organization_id = I.organization_id and
--MB.sr_instance_id = p_instance_id and
MB.sr_instance_id = I.sr_instance_id and
MB.bill_sequence_id = MBC.bill_sequence_id and
MBC.plan_id = MB.plan_id and
--MBC.organization_id = p_org_id and
--MBC.sr_instance_id = p_instance_id and
MBC.organization_id = MB.organization_id and
MBC.sr_instance_id = MB.sr_instance_id and
MBC.usage_quantity < 0 and
TRUNC(NVL(MBC.disable_date , C.calendar_date + 1)) >
TRUNC(C.Calendar_date) and
TRUNC(MBC.effectivity_date)<=
TRUNC(GREATEST(sysdate, C.calendar_date)) and
C.calendar_date = trunc(p_request_date) and
--C.sr_instance_id = p_instance_id and
C.sr_instance_id = MBC.sr_instance_id and
C.calendar_code = TP.calendar_code and
C.exception_set_id = TP.calendar_exception_set_id and
--TP.sr_instance_id = p_instance_id and
--TP.sr_tp_id = p_org_id and
TP.sr_instance_id = MBC.sr_instance_id and
TP.sr_tp_id = MBC.organization_id and
TP.partner_type = 3 );
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,
summary_flag
-- dsting 2535568 purge temp table fix
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES
(MSC_ATP_PVT.G_SESSION_ID,
MSC_ATP_PVT.G_ORDER_LINE_ID,
msc_full_pegging_s.nextval,
p_parent_pegging_id,
1,
4,
p_org_id,
null,
p_instance_id,
p_plan_id,
l_transaction_id,
l_coproducts_rec.inventory_item_id(rec_count),
null,
null,
null,
null,
null,
null,
null,
null,
null,
1,
2,
2,
l_coproducts_rec.quantity(rec_count),
2,
p_request_date,
NVL(MSC_ATP_PVT.G_DEMAND_PEGGING_ID, msc_full_pegging_s.currval),
null,
null, -- 1527660
null,
null,
null,
null,
MSC_ATP_PVT.G_SUMMARY_FLAG
-- dsting 2535568 purge temp table fix
, sysdate -- creation_date
, FND_GLOBAL.USER_ID -- created_by
, sysdate -- last_update_date
, FND_GLOBAL.USER_ID -- update_by
, FND_GLOBAL.USER_ID -- login_by
)
RETURNING pegging_id INTO l_pegging_id;
SELECT msim.intransit_time,
msim.ship_method,
msim.default_flag
FROM msc_interorg_ship_methods msim
WHERE msim.plan_id = -1
AND msim.from_location_id = p_from_location_id
AND msim.sr_instance_id = p_source_instance_id
AND msim.to_location_id = p_to_location_id
AND msim.sr_instance_id2 = p_instance_id
AND msim.to_region_id is null;
SELECT ( (10 * (10 - mrt.region_type)) +
DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level, mrt.region_id
FROM msc_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.plan_id = -1
AND mism.from_location_id = p_from_location_id
AND mism.sr_instance_id = p_from_instance_id
AND mism.sr_instance_id2 = p_to_instance_id
AND mism.to_region_id = mrt.region_id
AND mrt.session_id = p_session_id
AND mrt.partner_site_id = p_partner_site_id
ORDER BY 1;
SELECT ltrim(rtrim(M2A_DBLINK))
INTO l_dblink
FROM msc_apps_instance_nodes
WHERE instance_id = x_sr_instance_id and
node_id = p_node_id;
SELECT ltrim(rtrim(M2A_DBLINK))
INTO l_dblink
FROM msc_apps_instances
WHERE instance_id = x_sr_instance_id;
selecting ship method and delivery lead time passed
by OM in addition to instance_id
org_id ,cust_id and cust_site_id
Pachset J changes:
Removed DISTINCT from select and added
order_line_id = nvl(ato_model_line_id, order_line_id)
and rownum = 1
In case of Ship set :
we should be selecting only first line
In case of Model/PTO only parent line will
have ship method, intransit time info
----------------------------------------------*/
sql_stmt :=
' SELECT '||
' mast.sr_instance_id,mast.source_organization_id, '||
' mast.organization_id, '||
' mast.customer_id,mast.customer_site_id, '||
' mast.ship_method,mast.delivery_lead_time,mast.ship_method_text '||
' FROM mrp_atp_schedule_temp'||l_dynstring||' mast '||
' WHERE mast.session_id = :x_session_id '||
' AND status_flag = 4 and '||
' order_line_id = nvl(ato_model_line_id, order_line_id)'||
' AND rownum = 1';
' SELECT inventory_item_id ,order_line_id, ato_model_line_id,'||
' match_item_id' ||
' FROM mrp_atp_schedule_temp'||l_dynstring||
' WHERE session_id = :x_session_id '||
' AND status_flag = 4';
'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 organization_code
INTO l_other_cols.org_code(j)
FROM msc_trading_partners
WHERE sr_tp_id = l_sources.organization_id(j)
AND sr_instance_id = l_sources.instance_id(j)
AND partner_type = 3;
SELECT mtil.sr_tp_id, mtp.partner_name
INTO l_other_cols.sr_supplier_id(j),
l_other_cols.vendor_name(j)
FROM msc_tp_id_lid mtil,
msc_trading_partners mtp
WHERE mtil.tp_id = mtp.partner_id
AND mtil.sr_instance_id = l_sources.instance_id(j)
AND mtil.partner_type = 1
AND mtp.partner_id = l_sources.supplier_id(j);
SELECT mtsil.sr_tp_site_id, mtps.tp_site_code
INTO l_other_cols.sr_supplier_site_id(j),
l_other_cols.vendor_site_name(j)
FROM msc_tp_site_id_lid mtsil,
msc_trading_partner_sites mtps
WHERE mtsil.tp_site_id = mtps.partner_site_id
AND mtsil.sr_instance_id = l_sources.instance_id(j)
AND mtsil.partner_type = 1
AND mtps.partner_site_id =
l_sources.supplier_site_id(j);
' INSERT INTO '||
' MRP_ATP_SCHEDULE_TEMP'||l_dynstring||' '||
' ( SESSION_ID,'||
' DELIVERY_LEAD_TIME,SHIP_METHOD, '||
' STATUS_FLAG, '||
' ship_method_text, '||
' inventory_item_id,scenario_id,source_organization_id' ||
-- dsting 2535568 purge temp table fix
',creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login) '||
' VALUES ( ' ||
' :x_session_id,' ||
' :l_instansit_time,' ||
' :l_ship_method,' ||
' -99, '||
':l_ship_method_text, ' ||
' -1101, ' ||
' :l_default_flag,' ||
' :l_source_org_id' ||
-- dsting 2535568 purge temp table fix
',sysdate' ||
',:created_by' ||
',sysdate' ||
',:last_update_by' ||
',:last_update_login )';
' INSERT INTO '||
' MRP_ATP_SCHEDULE_TEMP'||l_dynstring||' '||
' ( SESSION_ID,'||
' DELIVERY_LEAD_TIME,SHIP_METHOD, '||
' STATUS_FLAG, '||
' SHIP_METHOD_TEXT, '||
' inventory_item_id,scenario_id,source_organization_id' ||
-- dsting 2535568 purge temp table fix
' , creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login) '||
' SELECT distinct'||
' mrt.session_id , '||
' msim.intransit_time, '||
' msim.ship_method, '||
' -99, '||
' msim.ship_method_text, ' ||
' -1101,msim.default_flag,:source_org_id'||
-- dsting 2535568 purge temp table fix
',sysdate' ||
',:created_by' ||
',sysdate' ||
',:last_update_by' ||
',:last_update_login ' ||
' FROM msc_interorg_ship_methods msim '||
' , msc_regions_temp mrt '||
-- ' , fnd_common_lookups fnd '||
' WHERE mrt.session_id = :x_session_id '||
' AND msim.plan_id = -1 '||
' AND msim.from_location_id = :l_from_location_id '||
' AND msim.sr_instance_id = :source_instance_id '||
' AND msim.to_region_id = mrt.region_id '||
' AND mrt.partner_site_id = :customer_site_id '||
' AND msim.sr_instance_id2 = :instance_id '||
' AND mrt.region_id = :l_region_id ';
' SELECT '||
' distinct mast.ship_method '||
' FROM mrp_atp_schedule_temp'||l_dynstring||' mast '||
' WHERE mast.session_id = :x_session_id '||
' AND status_flag = -99'||
' AND mast.source_organization_id=:source_org_id '||
' AND mast.ship_method = :ship_method ';
' SELECT DISTINCT '||
' delivery_lead_time '||
' FROM mrp_atp_schedule_temp'||l_dynstring||
' WHERE session_id = :x_session_id '||
' AND status_flag = -99'||
' AND ship_method =:g_ship_method'||
' AND source_organization_id = :l_om_org';
select fnd.meaning
into l_other_cols.ship_method_text(counter)
FROM FND_common_lookups fnd
where FND.LOOKUP_CODE = l_sources.SHIP_METHOD(counter)
AND FND.LOOKUP_TYPE = 'SHIP_METHOD'
AND fND.APPLICATION_ID = 401;
MSC_ATP_PROC.insert_atp_sources(x_session_id,
l_dblink,
x_calling_inst,
l_sources,l_other_cols);
' There are no sources to be inserted ');
DELETE msc_regions_temp
WHERE session_id = x_session_id;
|| 'Rows deleted from msc_regions_temp : '
||sql%rowcount);
select ORG_INFORMATION3, ORG_INFORMATION4
into x_atp_sources.supplier_id(counter), x_atp_sources.supplier_site_id(counter)
from hr_organization_information
where organization_id = x_atp_sources.organization_id(counter)
and ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association';
select fnd.meaning
into x_other_cols.ship_method_text(counter)
fROM FND_common_lookups FND
where FND.LOOKUP_CODE = x_atp_sources.SHIP_METHOD(counter)
AND FND.LOOKUP_TYPE = 'SHIP_METHOD'
AND fND.APPLICATION_ID = 401;
PROCEDURE insert_atp_sources(x_session_id NUMBER,
x_dblink VARCHAR2,
x_calling_inst VARCHAR2,
x_atp_sources mrp_atp_pvt.atp_source_typ,
x_other_cols order_sch_wb.other_cols_typ)
IS
sql_stmt VARCHAR2(32000);
msc_sch_wb.atp_debug('insert_atp_sources: ' || 'inserting sources link '||l_dynstring);
msc_sch_wb.atp_debug('insert_atp_sources: ' || 'inserting sources count = '||x_atp_sources.organization_id.count);
' INSERT INTO '||
' MRP_ATP_SCHEDULE_TEMP'||l_dynstring||' '||
' (ACTION, CALLING_MODULE, SESSION_ID, ORDER_HEADER_ID, ORDER_LINE_ID, '||
' INVENTORY_ITEM_ID, ORGANIZATION_ID, SR_INSTANCE_ID, ORGANIZATION_CODE, '||
' SOURCE_ORGANIZATION_ID, '||
' order_number, '||
' 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, '||
' 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, PROMISE_DATE, '||
' CUSTOMER_NAME, CUSTOMER_LOCATION, '||
' Top_Model_line_id, ' ||
' ATO_Model_Line_Id, '||
' Parent_line_id, ' ||
' Config_item_line_id, ' ||
' Validation_Org, '||
' Component_Sequence_ID, '||
' Component_Code, ' ||
' line_number, '||
' included_item_flag, '||
' SCENARIO_ID, VENDOR_NAME, VENDOR_SITE_NAME, '||
' STATUS_FLAG, MDI_ROWID, DEMAND_SOURCE_TYPE, '||
' DEMAND_SOURCE_DELIVERY, ATP_LEAD_TIME, OE_FLAG, ITEM_DESC, '||
' ship_method_text, shipment_number, option_number, '||
' project_number, task_number,old_source_organization_id,old_demand_class, '||
' ship_set_id, arrival_set_id' ||
-- dsting 2535568 purge temp table fix
' ,creation_date' ||
' ,created_by' ||
' ,last_update_date' ||
' ,last_updated_by' ||
' ,last_update_login ) ' ||
' SELECT '||
' ACTION, CALLING_MODULE, SESSION_ID, ORDER_HEADER_ID, ORDER_LINE_ID, '||
' INVENTORY_ITEM_ID, ORGANIZATION_ID, SR_INSTANCE_ID, ORGANIZATION_CODE, '||
' :source_org_id, '||
' order_number, '||
' CUSTOMER_ID, CUSTOMER_SITE_ID, DESTINATION_TIME_ZONE, '||
' QUANTITY_ORDERED, UOM_CODE, REQUESTED_SHIP_DATE, REQUESTED_ARRIVAL_DATE, '||
' LATEST_ACCEPTABLE_DATE, '||
' :lead_time, '||
' FREIGHT_CARRIER, '||
' :ship_method, '||
' DEMAND_CLASS, '||
' ship_set_name, '||
' ARRIVAL_SET_NAME, OVERRIDE_FLAG, '||
' :supplier_id, '||
' :supplier_site_id, '||
' INSERT_FLAG, '||
' ERROR_CODE, ERROR_MESSAGE, SEQUENCE_NUMBER, FIRM_FLAG, INVENTORY_ITEM_NAME, '||
' :org_code, '||
' INSTANCE_ID1, ORDER_LINE_NUMBER, PROMISE_DATE, '||
' CUSTOMER_NAME, CUSTOMER_LOCATION, '||
' Top_Model_line_id, ' ||
' ATO_Model_Line_Id, '||
' Parent_line_id, ' ||
' Config_item_line_id, ' ||
' Validation_Org, '||
' Component_Sequence_ID, '||
' Component_Code, ' ||
' line_number, '||
' included_item_flag, '||
' SCENARIO_ID, '||
' :vendor_name, '||
' :vendor_site_name, '||
' 22, '||
-- 22 is used here so that it does not get selected in lines block.
-- cannot use 2 since it would be a valid one for backlog mode lines block.
' MDI_ROWID, DEMAND_SOURCE_TYPE, '||
' DEMAND_SOURCE_DELIVERY, ATP_LEAD_TIME, OE_FLAG, ITEM_DESC, '||
' :ship_method_text, '||
' shipment_number, option_number, project_number, task_number,old_source_organization_id, '||
' old_demand_class, ship_set_id, arrival_set_id '||
' ,sysdate ' || -- dsting 2535568 purge temp table fix
' ,:created_by ' ||
' ,sysdate ' ||
' ,:last_update_by' ||
' ,:last_update_login' ||
' FROM mrp_atp_schedule_temp'||l_dynstring||' '||
' WHERE session_id = :x_session_id '||
' AND status_flag = 4';
msc_sch_wb.atp_debug('insert_atp_sources: ' || ' In atp_insert_sources x_atp_sources.ship_method(j) is ' || x_atp_sources.ship_method(j));
msc_sch_wb.atp_debug('insert_atp_sources: ' || ' inserted rows '||SQL%ROWCOUNT);
END insert_atp_sources;
/* p_mode tells from which table to select
1- summary_so 2- Summary_sd 3- summary_res 4-Summary_sup */
IF order_sch_wb.mr_debug = 'Y' THEN -- if debug mode is on
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('**** In SHOW_SUMMARY_QUANTITY Debug mode is on ****');
select /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ sd_qty
into temp_sd_qty
from MSC_ATP_SUMMARY_SO
where inventory_item_id = p_inventory_item_id and
organization_id = p_organization_id and
sr_instance_id = p_instance_id and
sd_date = trunc(p_sd_date) and
demand_class = Decode(p_dc_flag, 1, NVL(p_demand_class, '@@@'),'@@@');
select /*+ INDEX(msc_atp_summary_sd MSC_ATP_SUMMARY_SD_U1) */ sd_qty
into temp_sd_qty
from MSC_ATP_SUMMARY_SD
where sr_instance_id = p_instance_id
and inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id
and sd_date = trunc(p_sd_date)
and plan_id = p_plan_id;
select /*+ INDEX(msc_atp_summary_res MSC_ATP_SUMMARY_RES_U1) */ sd_qty
into temp_sd_qty
from msc_atp_summary_res
where plan_id = p_plan_id
and sr_instance_id = p_instance_id
and organization_id = p_organization_id
and resource_id = p_resource_id
and department_id = p_department_id
and sd_date = trunc(p_sd_date);
select /*+ INDEX(msc_atp_summary_sup MSC_ATP_SUMMARY_SUP_U1) */ sd_qty
into temp_sd_qty
from msc_atp_summary_sup
where plan_id = p_plan_id
and sr_instance_id = p_instance_id
and inventory_item_id = p_inventory_item_id
and supplier_id = p_supplier_id
and supplier_site_id = p_supplier_site_id
and sd_date = trunc(p_sd_date);
SELECT i.atp_flag, i.bom_item_type,
i.atp_components_flag, i.bom_item_type,
i.pick_components_flag, i.replenish_to_order_flag, -- atp_comp_flag
NVL(i.fixed_lead_time, 0), NVL(i.variable_lead_time, 0),
--bug3609031 adding ceil
NVL(ceil(i.preprocessing_lead_time), 0), NVL(ceil(i.postprocessing_lead_time),0), --lead times,
NVL(i.substitution_window,0), NVL(i.create_supply_flag, 1), i.inventory_item_id,
SUBSTR(i.item_name, 1,40), i.atp_rule_id, NVL(i.rounding_control_type, 2),
--diag_atp
i.unit_volume, i.unit_weight, i.volume_uom, i.weight_uom,
i.uom_code, i.inventory_item_id, --rajjain AATP forward consumption
--bug3609031 adding ceil
NVL(ceil(i.full_lead_time),0) -- SCLT (Supplier Capacity Lead Time)
, i.base_item_id
--bug5222635/5248167
,decode(i.atp_flag,'N',i.inventory_item_id,Decode(i.product_family_id,NULL,i.inventory_item_id,-23453,i.inventory_item_id,i.product_family_id))
--, nvl(i.product_family_id, i.inventory_item_id) -- For time_phased_atp
---3917625: Store plan_id
,i.plan_id
, lowest_level_src -- ATP4drp obtain flag applicable to DRP plan items.
INTO l_atp_flag, l_bom_item_type,
l_atp_comp_flag, l_bom_item_type, l_pick_comp_flag, l_replenish_flag,
p_item_attribute_rec.fixed_lt, p_item_attribute_rec.variable_lt,
p_item_attribute_rec.pre_pro_lt, p_item_attribute_rec.post_pro_lt,
p_item_attribute_rec.substitution_window, p_item_attribute_rec.create_supply_flag,
p_item_attribute_rec.dest_inv_item_id,
p_item_attribute_rec.item_name,
p_item_attribute_rec.atp_rule_id, p_item_attribute_rec.rounding_control_type,
--diag_atp
p_item_attribute_rec.unit_volume, p_item_attribute_rec.unit_weight,
p_item_attribute_rec.volume_uom, p_item_attribute_rec.weight_uom,
p_item_attribute_rec.uom_code, p_item_attribute_rec.inventory_item_id,
p_item_attribute_rec.processing_lt -- SCLT (Supplier Capacity Lead Time)
, p_item_attribute_rec.base_item_id
, p_item_attribute_rec.product_family_id -- time_phased_atp
---bug 3917625
,p_item_attribute_rec.plan_id
, p_item_attribute_rec.lowest_level_src -- ATP4drp obtain flag applicable to DRP plan items.
FROM msc_system_items i
WHERE i.sr_inventory_item_id = p_inventory_item_id
AND i.organization_id = p_organization_id
--- bug 3917625: Read item attribute from planned data
--AND i.plan_id = -1
AND i.plan_id = p_plan_id
AND i.sr_instance_id = p_instance_id;
SELECT tp.default_atp_rule_id,
tp.calendar_code,
tp.calendar_exception_set_id,
tp.default_demand_class,
tp.organization_code,
tp.organization_type, --(ssurendr) Bug 2865389
NVl(mp.network_scheduling_method,1), --bug3601223
NVL(tp.use_phantom_routings, 2) --4570421
INTO x_org_attribute_rec.default_atp_rule_id,
x_org_attribute_rec.cal_code,
x_org_attribute_rec.cal_exception_set_id,
x_org_attribute_rec.default_demand_class,
x_org_attribute_rec.org_code,
x_org_attribute_rec.org_type, --(ssurendr) Bug 2865389
x_org_attribute_rec.network_scheduling_method, --bug3601223
x_org_attribute_rec.use_phantom_routings --4570421
FROM msc_trading_partners tp, msc_parameters mp
WHERE tp.sr_tp_id = p_organization_id
AND tp.sr_instance_id = p_instance_id
AND tp.partner_type = 3
AND mp.ORGANIZATION_ID(+) = tp.sr_tp_id
AND mp.SR_INSTANCE_ID(+) = tp.sr_instance_id;
msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'selecting item type for PDS');
SELECT i.bom_item_type
INTO l_item_type
FROM msc_system_items i
WHERE i.plan_id = p_plan_id
AND i.sr_instance_id = p_instance_id
AND i.organization_id = p_organization_id
AND i.sr_inventory_item_id = p_inventory_item_id;
SELECT c2.calendar_date, r.rule_name
INTO l_infinite_time_fence_date, x_atp_rule_name
FROM msc_calendar_dates c2,
msc_calendar_dates c1,
msc_atp_rules r,
msc_trading_partners tp,
msc_system_items i
WHERE i.sr_inventory_item_id = p_inventory_item_id
AND i.organization_id = p_organization_id
--AND i.plan_id = p_plan_id
AND i.plan_id = -1 -- for 1478110
AND i.sr_instance_id = p_instance_id
AND tp.sr_tp_id = i.organization_id
AND tp.sr_instance_id = i.sr_instance_id
AND tp.partner_type = 3
AND r.rule_id = NVL(i.atp_rule_id, NVL(tp.default_atp_rule_id,0))
AND r.sr_instance_id = p_instance_id
AND c1.sr_instance_id = p_instance_id
AND c1.calendar_date = TRUNC(sysdate)
AND c1.calendar_code = tp.calendar_code
AND c1.exception_set_id = -1
AND c2.sr_instance_id = p_instance_id
-- Bug 2877340, 2746213
-- Add Infinite Supply Time Fence PAD
--bug3609031 adding ceil
AND c2.seq_num = c1.next_seq_num +
DECODE(r.infinite_supply_fence_code,
1, ceil(i.cumulative_total_lead_time) + MSC_ATP_PVT.G_INF_SUP_TF_PAD,
2, ceil(i.cum_manufacturing_lead_time) + MSC_ATP_PVT.G_INF_SUP_TF_PAD,
3, DECODE(NVL(ceil(i.preprocessing_lead_time),-1)+
NVL(ceil(i.full_lead_time),-1)+
NVL(ceil(i.postprocessing_lead_time),-1),-3,
NULL, -- All are NULL so return NULL.
NVL(ceil(i.preprocessing_lead_time),0)+ -- Otherwise
NVL(ceil(i.full_lead_time),0) + -- evaluate to
NVL(ceil(i.postprocessing_lead_time),0) -- NON NULL
+ MSC_ATP_PVT.G_INF_SUP_TF_PAD),
-- Bugs 1986353, 2004479.
4, r.infinite_supply_time_fence)
-- End Bug 2877340, 2746213
AND c2.calendar_code = c1.calendar_code
AND c2.exception_set_id = -1;
SELECT c2.calendar_date, r.rule_name
INTO l_infinite_time_fence_date, x_atp_rule_name
FROM msc_calendar_dates c2,
msc_calendar_dates c1,
msc_atp_rules r,
msc_trading_partners tp,
msc_department_resources dep_res
WHERE dep_res.resource_id = p_resource_id
AND dep_res.department_id = p_department_id
AND dep_res.organization_id = p_organization_id
AND dep_res.plan_id = p_plan_id
AND dep_res.sr_instance_id = p_instance_id
AND tp.sr_tp_id = dep_res.organization_id
AND tp.sr_instance_id = dep_res.sr_instance_id
AND tp.partner_type = 3
AND r.rule_id = NVL(dep_res.atp_rule_id, NVL(tp.default_atp_rule_id,0))
AND r.sr_instance_id = dep_res.sr_instance_id
AND c1.sr_instance_id = dep_res.sr_instance_id
AND c1.calendar_date = TRUNC(sysdate)
AND c1.calendar_code = tp.calendar_code
AND c1.exception_set_id = -1
AND c2.sr_instance_id = dep_res.sr_instance_id
AND c2.seq_num = c1.next_seq_num + r.infinite_supply_time_fence
AND c2.calendar_code = c1.calendar_code
AND c2.exception_set_id = c1.exception_set_id;
SELECT curr_cutoff_date
INTO l_infinite_time_fence_date
FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT mism.ship_method, mism.intransit_time,
((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
FROM mtl_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.from_location_id = c_from_loc_id
AND mism.to_region_id = mrt.region_id
AND mrt.session_id = c_session_id
AND mrt.partner_site_id = c_partner_site_id
ORDER BY 3;
SELECT mism.ship_method, mism.intransit_time,
((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
FROM msc_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.plan_id = -1
AND mism.from_location_id = c_from_location_id
AND mism.sr_instance_id = c_from_instance_id
AND mism.sr_instance_id2 = c_to_instance_id
AND mism.to_region_id = mrt.region_id
AND mrt.session_id = c_session_id
AND mrt.partner_site_id = c_partner_site_id
ORDER BY 3;
insert into mrp_atp_schedule_temp
(session_id, inventory_item_id, scenario_id, delivery_lead_time, ship_method, status_flag)
(
select p_session_id,
-1,
-1,
intransit_time,
ship_method,
100
from msc_interorg_ship_methods
where from_organization_id = p_from_organization_id
and to_organization_id = p_to_organization_id
and sr_instance_id = p_from_instance_id
and sr_instance_id2 = p_to_instance_id
and to_region_id is null
and plan_id = -1
);
insert into mrp_atp_schedule_temp
(session_id, inventory_item_id, scenario_id, delivery_lead_time, ship_method, status_flag)
(
select p_session_id,
-1,
-1,
intransit_time,
ship_method,
100
from msc_interorg_ship_methods
where plan_id = -1
and from_location_id = l_from_location_id
and sr_instance_id = p_from_instance_id
and to_location_id = l_to_location_id
and sr_instance_id2 = p_to_instance_id
and to_region_id is NULL
);
insert into mrp_atp_schedule_temp
(session_id, inventory_item_id, scenario_id, delivery_lead_time, ship_method, status_flag)
values
(
p_session_id,
-1,
-1,
l_lead_time_arr (l_counter2),
l_ship_method_arr (l_counter2),
100
);
msc_sch_wb.atp_debug ('Unable to insert data');
insert into mrp_atp_schedule_temp
(session_id, inventory_item_id, scenario_id, delivery_lead_time, ship_method, status_flag)
(
select p_session_id,
-1,
-1,
intransit_time,
ship_method,
100
from mtl_interorg_ship_methods
where from_organization_id = p_from_organization_id
and to_organization_id = p_to_organization_id
);
msc_sch_wb.atp_debug ('Records inserted into table : ' || sql%rowcount);
insert into mrp_atp_schedule_temp
(session_id, inventory_item_id, scenario_id, delivery_lead_time, ship_method, status_flag)
(
select p_session_id,
-1,
-1,
intransit_time,
ship_method,
100
from mtl_interorg_ship_methods
where from_location_id = l_from_location_id
and to_location_id = l_to_location_id
);
insert into mrp_atp_schedule_temp
(session_id, inventory_item_id, scenario_id, delivery_lead_time, ship_method, status_flag)
values
(
p_session_id,
-1,
-1,
l_lead_time_arr (l_counter2),
l_ship_method_arr (l_counter2),
100
);
msc_sch_wb.atp_debug ('Unable to insert data');
DELETE MSC_REGIONS_TEMP
where session_id = p_session_id;
msc_sch_wb.atp_debug (sql%rowcount || ' rows deleted from regions temp table.');
l_sql_stmt := 'select location_id
from PO_LOCATION_ASSOCIATIONS
where SITE_USE_ID = :p_customer_site_id';
DELETE MSC_REGIONS_TEMP
WHERE session_id = p_session_id
AND partner_site_id = p_to_customer_site_id;
l_inserted_rows PLS_INTEGER := 0;
l_updated_rows PLS_INTEGER := 0;
by selecting distinct assignment_type along with their level_id
by a preliminary SQL
We have 2 cases here:
Case1. Finding the sources for single item.
p_ship_set_item_count is null, p_inventory_item_id is not null
Case2. Finding the sources for ship set.
p_ship_set_item_count is not null, p_inventory_item_id is null.
*/
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Inside Get_Sources Info procedure');
SELECT TRUNC(sysdate) INTO l_sysdate FROM dual;
SELECT
DECODE(MSRA.ASSIGNMENT_TYPE,
1, 9,
2, 6,
3, 4,
4, 7,
5, 3,
6, 1,
7, 8,
8, 5,
9, 2) Level_id
BULK COLLECT INTO
l_dist_level_type
FROM
MSC_SOURCING_RULES MSR,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SR_ASSIGNMENTS MSRA,
MSC_SR_SOURCE_ORG SOURCE_ORG
WHERE
MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = RECEIPT_ORG.SOURCING_RULE_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SR_RECEIPT_ID = SOURCE_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
GROUP BY MSRA.ASSIGNMENT_TYPE
ORDER BY Level_id;
SELECT
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1) SOURCE_ORGANIZATION_ID,
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1) SOURCE_ORG_INSTANCE_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1) VENDOR_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1) VENDOR_SITE_ID,
NVL(SOURCE_ORG.RANK, -1) RANK,
NVL(SOURCE_ORG.SOURCE_TYPE,
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)) SOURCE_TYPE,
0 PREFERRED,
-1 LEAD_TIME,
'@@@' SHIP_METHOD,
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
MSC_SYSTEM_ITEMS ITEM,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_TP_SITE_ID_LID MTSIL
WHERE
MSRA.ASSIGNMENT_TYPE = 6 /* ITEM-ORG */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID = MTSIL.TP_SITE_ID
AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
AND MTSIL.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEM.PLAN_ID = -1
AND ITEM.INVENTORY_ITEM_ID = p_inventory_item_id
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate )) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
ORDER BY
SOURCE_ORG.RANK ASC, SOURCE_ORG.ALLOCATION_PERCENT DESC;
SELECT NVL(MIN(2000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 10) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0)), 0)
INTO l_min_region_value
FROM
MSC_SYSTEM_ITEMS ITEM_INNER,
MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
MSC_SOURCING_RULES MSR_INNER,
MSC_SR_ASSIGNMENTS MSRA_INNER,
MSC_REGIONS_TEMP MRT_INNER
WHERE
MSRA_INNER.ASSIGNMENT_TYPE = 9
AND MSRA_INNER.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA_INNER.REGION_ID = MRT_INNER.REGION_ID
AND MRT_INNER.PARTNER_SITE_ID IS NOT NULL
AND MRT_INNER.SESSION_ID = p_session_id
--AND MRT_INNER.PARTNER_SITE_ID = p_customer_site_id
AND MRT_INNER.PARTNER_SITE_ID = decode( NVL(p_partner_type, 2), 2, p_customer_site_id , 3 , p_party_site_id, 4, p_order_line_id) --2814895
AND MRT_INNER.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
--AND MRT_INNER.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MSRA_INNER.INVENTORY_ITEM_ID = ITEM_INNER.INVENTORY_ITEM_ID
AND ITEM_INNER.PLAN_ID = -1
AND ITEM_INNER.INVENTORY_ITEM_ID = p_inventory_item_id
AND ITEM_INNER.ORGANIZATION_ID = SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID
AND ITEM_INNER.SR_INSTANCE_ID = SOURCE_ORG_INNER.SR_INSTANCE_ID
AND SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG_INNER.SR_RECEIPT_ID = RECEIPT_ORG_INNER.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG_INNER.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG_INNER.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG_INNER.SOURCING_RULE_ID = MSR_INNER.SOURCING_RULE_ID
AND MSR_INNER.STATUS = 1
AND MSR_INNER.SOURCING_RULE_TYPE = 1
AND MSR_INNER.SOURCING_RULE_ID = MSRA_INNER.SOURCING_RULE_ID;
SELECT
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1) SOURCE_ORGANIZATION_ID,
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1) SOURCE_ORG_INSTANCE_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1) VENDOR_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1) VENDOR_SITE_ID,
NVL(SOURCE_ORG.RANK, -1) RANK,
NVL(SOURCE_ORG.SOURCE_TYPE,
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)) SOURCE_TYPE,
0 PREFERRED,
-1 LEAD_TIME,
'@@@' SHIP_METHOD,
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
MSC_SYSTEM_ITEMS ITEM,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_REGIONS_TEMP MRT
WHERE
MSRA.ASSIGNMENT_TYPE = 9
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.REGION_ID = MRT.REGION_ID
AND MRT.SESSION_ID = p_session_id
AND MRT.PARTNER_SITE_ID IS NOT NULL
--AND MRT.PARTNER_SITE_ID = p_customer_site_id
AND MRT.PARTNER_SITE_ID = decode( NVL(p_partner_type, 2), 2, p_customer_site_id , 3 , p_party_site_id, 4, p_order_line_id) --2814895
AND MRT.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
--AND MRT.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND (2000 + ((10 - NVL(MRT.REGION_TYPE, 0)) * 10) +
DECODE(MRT.ZONE_FLAG, 'Y', 1, 0)) = l_min_region_value
AND MSRA.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND ITEM.PLAN_ID = -1
AND ITEM.INVENTORY_ITEM_ID = p_inventory_item_id
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
ORDER BY
SOURCE_ORG.RANK ASC, SOURCE_ORG.ALLOCATION_PERCENT DESC;
SELECT
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1) SOURCE_ORGANIZATION_ID,
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1) SOURCE_ORG_INSTANCE_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1) VENDOR_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1) VENDOR_SITE_ID,
NVL(SOURCE_ORG.RANK, -1) RANK,
NVL(SOURCE_ORG.SOURCE_TYPE,
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)) SOURCE_TYPE,
0 PREFERRED,
-1 LEAD_TIME,
'@@@' SHIP_METHOD,
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
MSC_ITEM_CATEGORIES CAT,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_TP_SITE_ID_LID MTSIL
WHERE
MSRA.ASSIGNMENT_TYPE = 5 /* CATEGORY-ORG */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID = MTSIL.TP_SITE_ID
AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
AND MTSIL.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND CAT.INVENTORY_ITEM_ID = p_inventory_item_id
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate )) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
ORDER BY
SOURCE_ORG.RANK ASC, SOURCE_ORG.ALLOCATION_PERCENT DESC;
SELECT
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1) SOURCE_ORGANIZATION_ID,
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1) SOURCE_ORG_INSTANCE_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1) VENDOR_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1) VENDOR_SITE_ID,
NVL(SOURCE_ORG.RANK, -1) RANK,
NVL(SOURCE_ORG.SOURCE_TYPE,
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)) SOURCE_TYPE,
0 PREFERRED,
-1 LEAD_TIME,
'@@@' SHIP_METHOD,
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
MSC_SYSTEM_ITEMS ITEM,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
-- MSC_TRADING_PARTNER_SITES TP,
-- MSC_TP_SITE_ID_LID MTSIL,
MSC_SR_ASSIGNMENTS MSRA
WHERE
MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.INVENTORY_ITEM_ID = p_inventory_item_id
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEM.PLAN_ID = -1
-- AND ITEM.SR_INSTANCE_ID = MTSIL.SR_INSTANCE_ID
-- AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
-- AND MTSIL.TP_SITE_ID = TP.PARTNER_SITE_ID
-- AND TP.PARTNER_TYPE = 2
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE ,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
ORDER BY
SOURCE_ORG.RANK ASC, SOURCE_ORG.ALLOCATION_PERCENT DESC;
SELECT NVL(MIN(5000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 10) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0)), 0)
INTO l_min_region_value
FROM
MSC_ITEM_CATEGORIES CAT_INNER,
MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
MSC_SOURCING_RULES MSR_INNER,
MSC_SR_ASSIGNMENTS MSRA_INNER,
MSC_REGIONS_TEMP MRT_INNER
WHERE
MSRA_INNER.ASSIGNMENT_TYPE = 8 /* CATEGORY-REGION */
AND MSRA_INNER.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA_INNER.REGION_ID = MRT_INNER.REGION_ID
AND MRT_INNER.PARTNER_SITE_ID IS NOT NULL
AND MRT_INNER.SESSION_ID = p_session_id
--AND MRT_INNER.PARTNER_SITE_ID = p_customer_site_id --2814895
AND MRT_INNER.PARTNER_SITE_ID = decode(NVL(p_partner_type,2), 2, p_customer_site_id , 4 , p_party_site_id, 5, p_order_line_id) --2814895
AND MRT_INNER.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
--AND MRT_INNER.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MSRA_INNER.CATEGORY_SET_ID = CAT_INNER.CATEGORY_SET_ID
AND MSRA_INNER.CATEGORY_NAME = CAT_INNER.CATEGORY_NAME
AND CAT_INNER.INVENTORY_ITEM_ID = p_inventory_item_id
AND CAT_INNER.ORGANIZATION_ID = SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID
AND CAT_INNER.SR_INSTANCE_ID = SOURCE_ORG_INNER.SR_INSTANCE_ID
AND SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG_INNER.SR_RECEIPT_ID = RECEIPT_ORG_INNER.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG_INNER.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG_INNER.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG_INNER.SOURCING_RULE_ID = MSR_INNER.SOURCING_RULE_ID
AND MSR_INNER.STATUS = 1
AND MSR_INNER.SOURCING_RULE_TYPE = 1
AND MSR_INNER.SOURCING_RULE_ID = MSRA_INNER.SOURCING_RULE_ID;
SELECT
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1) SOURCE_ORGANIZATION_ID,
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1) SOURCE_ORG_INSTANCE_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1) VENDOR_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1) VENDOR_SITE_ID,
NVL(SOURCE_ORG.RANK, -1) RANK,
NVL(SOURCE_ORG.SOURCE_TYPE,
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)) SOURCE_TYPE,
0 PREFERRED,
-1 LEAD_TIME,
'@@@' SHIP_METHOD,
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
MSC_ITEM_CATEGORIES CAT,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_REGIONS_TEMP MRT
WHERE
MSRA.ASSIGNMENT_TYPE = 8 /* CATEGORY-REGION */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.REGION_ID = MRT.REGION_ID
AND MRT.PARTNER_SITE_ID IS NOT NULL
AND MRT.SESSION_ID = p_session_id
--AND MRT.PARTNER_SITE_ID = p_customer_site_id
AND MRT.PARTNER_SITE_ID = decode( NVL(p_partner_type,2), 2, p_customer_site_id , 4, p_party_site_id, 5, p_order_line_id) --2814895
AND MRT.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
--AND MRT.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND (5000 + ((10 - NVL(MRT.REGION_TYPE, 0)) * 10) +
DECODE(MRT.ZONE_FLAG, 'Y', 1, 0)) = l_min_region_value
AND MSRA.CATEGORY_SET_ID = CAT.CATEGORY_SET_ID
AND MSRA.CATEGORY_NAME = CAT.CATEGORY_NAME
AND CAT.INVENTORY_ITEM_ID = p_inventory_item_id
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
ORDER BY
SOURCE_ORG.RANK ASC, SOURCE_ORG.ALLOCATION_PERCENT DESC;
SELECT
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1) SOURCE_ORGANIZATION_ID,
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1) SOURCE_ORG_INSTANCE_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1) VENDOR_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1) VENDOR_SITE_ID,
NVL(SOURCE_ORG.RANK, -1) RANK,
NVL(SOURCE_ORG.SOURCE_TYPE,
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)) SOURCE_TYPE,
0 PREFERRED,
-1 LEAD_TIME,
'@@@' SHIP_METHOD,
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
MSC_ITEM_CATEGORIES CAT,
-- MSC_TRADING_PARTNER_SITES TP,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA
-- MSC_TP_SITE_ID_LID MTSIL
WHERE
MSRA.ASSIGNMENT_TYPE = 2 /* CATEGORY */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.CATEGORY_NAME = CAT.CATEGORY_NAME
AND MSRA.CATEGORY_SET_ID = CAT.CATEGORY_SET_ID
AND CAT.INVENTORY_ITEM_ID = p_inventory_item_id
-- AND CAT.SR_INSTANCE_ID = MTSIL.SR_INSTANCE_ID
-- AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
-- AND MTSIL.TP_SITE_ID = TP.PARTNER_SITE_ID
-- AND TP.PARTNER_TYPE = 2
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
ORDER BY
SOURCE_ORG.RANK ASC, SOURCE_ORG.ALLOCATION_PERCENT DESC;
SELECT
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1) SOURCE_ORGANIZATION_ID,
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1) SOURCE_ORG_INSTANCE_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1) VENDOR_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1) VENDOR_SITE_ID,
NVL(SOURCE_ORG.RANK, -1) RANK,
NVL(SOURCE_ORG.SOURCE_TYPE,
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)) SOURCE_TYPE,
0 PREFERRED,
-1 LEAD_TIME,
'@@@' SHIP_METHOD,
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
MSC_SYSTEM_ITEMS ITEM,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_TP_SITE_ID_LID MTSIL
WHERE
MSRA.ASSIGNMENT_TYPE = 4 /* ORG */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID = MTSIL.TP_SITE_ID
AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
AND MTSIL.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID
AND ITEM.INVENTORY_ITEM_ID = p_inventory_item_id
AND ITEM.PLAN_ID = -1
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate )) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
ORDER BY
SOURCE_ORG.RANK ASC, SOURCE_ORG.ALLOCATION_PERCENT DESC;
SELECT NVL(MIN(8000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 100) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0)), 0)
INTO l_min_region_value
FROM
MSC_SYSTEM_ITEMS ITEM_INNER,
MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
MSC_SOURCING_RULES MSR_INNER,
MSC_SR_ASSIGNMENTS MSRA_INNER,
MSC_REGIONS_TEMP MRT_INNER
WHERE
MSRA_INNER.ASSIGNMENT_TYPE = 7 /* REGION */
AND MSRA_INNER.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA_INNER.REGION_ID = MRT_INNER.REGION_ID
AND MRT_INNER.SESSION_ID = p_session_id
--AND MRT_INNER.PARTNER_SITE_ID = p_customer_site_id
AND MRT_INNER.PARTNER_SITE_ID IS NOT NULL
--AND MRT_INNER.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MRT_INNER.PARTNER_SITE_ID = decode(NVL(p_partner_type,2), 2, p_customer_site_id , 4 , p_party_site_id, 5, p_order_line_id) --2814895
AND MRT_INNER.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
AND MSRA_INNER.SOURCING_RULE_ID = MSR_INNER.SOURCING_RULE_ID
AND MSR_INNER.STATUS = 1
AND MSR_INNER.SOURCING_RULE_TYPE = 1
AND MSR_INNER.SOURCING_RULE_ID = RECEIPT_ORG_INNER.SOURCING_RULE_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG_INNER.DISABLE_DATE,l_sysdate )) >= l_sysdate
AND TRUNC(RECEIPT_ORG_INNER.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG_INNER.SR_RECEIPT_ID = SOURCE_ORG_INNER.SR_RECEIPT_ID
AND SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID = ITEM_INNER.ORGANIZATION_ID
AND SOURCE_ORG_INNER.SR_INSTANCE_ID = ITEM_INNER.SR_INSTANCE_ID
AND ITEM_INNER.INVENTORY_ITEM_ID = p_inventory_item_id
AND ITEM_INNER.PLAN_ID = -1 ;
SELECT
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1) SOURCE_ORGANIZATION_ID,
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1) SOURCE_ORG_INSTANCE_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1) VENDOR_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1) VENDOR_SITE_ID,
NVL(SOURCE_ORG.RANK, -1) RANK,
NVL(SOURCE_ORG.SOURCE_TYPE,
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)) SOURCE_TYPE,
0 PREFERRED,
-1 LEAD_TIME,
'@@@' SHIP_METHOD,
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
MSC_SYSTEM_ITEMS ITEM,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_REGIONS_TEMP MRT
WHERE
MSRA.ASSIGNMENT_TYPE = 7 /* REGION */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.REGION_ID = MRT.REGION_ID
AND MRT.PARTNER_SITE_ID IS NOT NULL
AND MRT.SESSION_ID = p_session_id
--AND MRT.PARTNER_SITE_ID = p_customer_site_id
--AND MRT.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MRT.PARTNER_SITE_ID = decode(NVL(p_partner_type,2), 2, p_customer_site_id , 4 , p_party_site_id, 5, p_order_line_id) --2814895
AND MRT.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
AND (8000 + ((10 - NVL(MRT.REGION_TYPE, 0)) * 100) +
DECODE(MRT.ZONE_FLAG, 'Y', 1, 0)) = l_min_region_value
AND MSRA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = RECEIPT_ORG.SOURCING_RULE_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SR_RECEIPT_ID = SOURCE_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND SOURCE_ORG.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ITEM.INVENTORY_ITEM_ID = p_inventory_item_id
AND ITEM.PLAN_ID = -1
ORDER BY
SOURCE_ORG.RANK ASC, SOURCE_ORG.ALLOCATION_PERCENT DESC;
SELECT
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1) SOURCE_ORGANIZATION_ID,
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1) SOURCE_ORG_INSTANCE_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1) VENDOR_ID,
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1) VENDOR_SITE_ID,
NVL(SOURCE_ORG.RANK, -1) RANK,
NVL(SOURCE_ORG.SOURCE_TYPE,
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)) SOURCE_TYPE,
0 PREFERRED,
-1 LEAD_TIME,
'@@@' SHIP_METHOD,
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM
MSC_SYSTEM_ITEMS ITEM,
-- MSC_TRADING_PARTNER_SITES TP,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA
-- MSC_TP_SITE_ID_LID MTSIL
WHERE
MSRA.ASSIGNMENT_TYPE = 1 /* GLOBAL */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = RECEIPT_ORG.SOURCING_RULE_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SR_RECEIPT_ID = SOURCE_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND SOURCE_ORG.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID
AND ITEM.INVENTORY_ITEM_ID = p_inventory_item_id
AND ITEM.PLAN_ID = -1
/*
AND ITEM.SR_INSTANCE_ID = MTSIL.SR_INSTANCE_ID
AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
AND MTSIL.TP_SITE_ID = TP.PARTNER_SITE_ID
AND TP.PARTNER_TYPE = 2
*/
ORDER BY
SOURCE_ORG.RANK ASC, SOURCE_ORG.ALLOCATION_PERCENT DESC;
DELETE MSC_ATP_SOURCES_TEMP;
INSERT INTO MSC_ATP_SOURCES_TEMP (inventory_item_id, Organization_Id, Instance_Id,
Supplier_Id, Supplier_Site_Id, Rank, Source_Type, Preferred, Lead_Time, Ship_Method)
SELECT
MSST.INVENTORY_ITEM_ID,
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCE_ORG.RANK, 0) + 1 - SOURCE_ORG.ALLOCATION_PERCENT/1000), --2910418
NVL(MIN(SOURCE_ORG.SOURCE_TYPE),
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)),
0,
-1,
'@@@'
FROM
MSC_SYSTEM_ITEMS ITEM,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_TP_SITE_ID_LID MTSIL,
MSC_SHIP_SET_TEMP MSST
WHERE
MSRA.ASSIGNMENT_TYPE = 6 /* ITEM-ORG */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID = MTSIL.TP_SITE_ID
AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
AND MTSIL.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEM.PLAN_ID = -1
AND ITEM.INVENTORY_ITEM_ID = MSST.INVENTORY_ITEM_ID
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
GROUP BY
MSST.INVENTORY_ITEM_ID,
SOURCE_ORG.SOURCE_ORGANIZATION_ID,
SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,
SOURCE_ORG.SOURCE_PARTNER_ID,
SOURCE_ORG.SOURCE_PARTNER_SITE_ID;
l_inserted_rows := SQL%ROWCOUNT;
IF (l_inserted_rows > 0) THEN
-- Found some sources for some items.
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Get_Sources_Info: '|| 'Found some sources for some ship set items at level 1');
UPDATE msc_ship_set_temp
set visited_flag = 1
where inventory_item_id in (select inventory_item_id from msc_atp_sources_temp);
l_updated_rows := SQL%ROWCOUNT;
IF (l_updated_rows = p_ship_set_item_count) THEN
-- Find the common sources from the pool of sources in msc_atp_sources_temp
SELECT Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
sum(Rank), -- order by cum rank instead of group by rank
Source_Type,
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM MSC_ATP_SOURCES_TEMP
GROUP BY
Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
-- Rank, order by cum rank instead of group by rank
Source_Type
HAVING count(*) = p_ship_set_item_count
ORDER BY 5; -- order by cum rank instead of group by rank
SELECT
MSST_INNER.INVENTORY_ITEM_ID,
MIN(2000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 10) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0))
BULK COLLECT INTO
l_inventory_item_id_arr,
l_min_region_value_arr
FROM
MSC_SYSTEM_ITEMS ITEM_INNER,
MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
MSC_SOURCING_RULES MSR_INNER,
MSC_SR_ASSIGNMENTS MSRA_INNER,
MSC_REGIONS_TEMP MRT_INNER,
MSC_SHIP_SET_TEMP MSST_INNER
WHERE
MSRA_INNER.ASSIGNMENT_TYPE = 9
AND MSRA_INNER.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA_INNER.REGION_ID = MRT_INNER.REGION_ID
AND MRT_INNER.PARTNER_SITE_ID IS NOT NULL
--AND MRT_INNER.PARTNER_SITE_ID = p_customer_site_id
--AND MRT_INNER.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MRT_INNER.PARTNER_SITE_ID = decode(NVL(p_partner_type,2), 2, p_customer_site_id , 4 , p_party_site_id, 5, p_order_line_id) --2814895
AND MRT_INNER.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
AND MRT_INNER.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MSRA_INNER.INVENTORY_ITEM_ID = ITEM_INNER.INVENTORY_ITEM_ID
AND ITEM_INNER.PLAN_ID = -1
AND ITEM_INNER.INVENTORY_ITEM_ID = MSST_INNER.INVENTORY_ITEM_ID
AND MSST_INNER.VISITED_FLAG = 0
AND ITEM_INNER.ORGANIZATION_ID = SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID
AND ITEM_INNER.SR_INSTANCE_ID = SOURCE_ORG_INNER.SR_INSTANCE_ID
AND SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG_INNER.SR_RECEIPT_ID = RECEIPT_ORG_INNER.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG_INNER.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG_INNER.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG_INNER.SOURCING_RULE_ID = MSR_INNER.SOURCING_RULE_ID
AND MSR_INNER.STATUS = 1
AND MSR_INNER.SOURCING_RULE_TYPE = 1
AND MSR_INNER.SOURCING_RULE_ID = MSRA_INNER.SOURCING_RULE_ID
GROUP BY
MSST_INNER.INVENTORY_ITEM_ID;
UPDATE msc_ship_set_temp
SET min_region_value = l_min_region_value_arr(i),
visited_flag = 1
WHERE inventory_item_id = l_inventory_item_id_arr(i);
INSERT INTO MSC_ATP_SOURCES_TEMP (inventory_item_id, Organization_Id, Instance_Id,
Supplier_Id, Supplier_Site_Id, Rank, Source_Type, Preferred, Lead_Time, Ship_Method)
SELECT
MSST.INVENTORY_ITEM_ID,
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCE_ORG.RANK, 0) + 1 - SOURCE_ORG.ALLOCATION_PERCENT/1000), --2910418
NVL(MIN(SOURCE_ORG.SOURCE_TYPE),
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)),
0,
-1,
'@@@'
FROM
MSC_SYSTEM_ITEMS ITEM,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_REGIONS_TEMP MRT,
MSC_SHIP_SET_TEMP MSST
WHERE
MSRA.ASSIGNMENT_TYPE = 9
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.REGION_ID = MRT.REGION_ID
AND MRT.SESSION_ID = p_session_id
AND MRT.PARTNER_SITE_ID IS NOT NULL
--AND MRT.PARTNER_SITE_ID = p_customer_site_id
--AND MRT.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MRT.PARTNER_SITE_ID = decode(NVL(p_partner_type,2), 2, p_customer_site_id , 4 , p_party_site_id, 5, p_order_line_id) --2814895
AND MRT.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
AND (2000 + ((10 - NVL(MRT.REGION_TYPE, 0)) * 10) +
DECODE(MRT.ZONE_FLAG, 'Y', 1, 0)) = MSST.MIN_REGION_VALUE
AND MSRA.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND ITEM.PLAN_ID = -1
AND ITEM.INVENTORY_ITEM_ID = MSST.INVENTORY_ITEM_ID
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate )) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
GROUP BY
MSST.INVENTORY_ITEM_ID,
SOURCE_ORG.SOURCE_ORGANIZATION_ID,
SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,
SOURCE_ORG.SOURCE_PARTNER_ID,
SOURCE_ORG.SOURCE_PARTNER_SITE_ID;
SELECT COUNT(*)
INTO l_items_visited
FROM MSC_SHIP_SET_TEMP
WHERE VISITED_FLAG = 1;
SELECT Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
sum(Rank), -- order by cum rank instead of group by rank
Source_Type,
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM MSC_ATP_SOURCES_TEMP
GROUP BY
Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
-- Rank, order by cum rank instead of group by rank
Source_Type
HAVING count(*) = p_ship_set_item_count
ORDER BY 5; -- Rank; order by cum rank instead of group by rank
INSERT INTO MSC_ATP_SOURCES_TEMP (inventory_item_id, Organization_Id, Instance_Id,
Supplier_Id, Supplier_Site_Id, Rank, Source_Type, Preferred, Lead_Time, Ship_Method)
SELECT
MSST.INVENTORY_ITEM_ID,
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCE_ORG.RANK, 0) + 1 - SOURCE_ORG.ALLOCATION_PERCENT/1000), --2910418
NVL(MIN(SOURCE_ORG.SOURCE_TYPE),
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)),
0,
-1,
'@@@'
FROM
MSC_ITEM_CATEGORIES CAT,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_TP_SITE_ID_LID MTSIL,
MSC_SHIP_SET_TEMP MSST
WHERE
MSRA.ASSIGNMENT_TYPE = 5 /* CATEGORY-ORG */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID = MTSIL.TP_SITE_ID
AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
AND MTSIL.SR_INSTANCE_ID = CAT.SR_INSTANCE_ID
AND CAT.INVENTORY_ITEM_ID = MSST.INVENTORY_ITEM_ID
AND MSST.VISITED_FLAG = 0
AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate )) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
GROUP BY
MSST.INVENTORY_ITEM_ID,
SOURCE_ORG.SOURCE_ORGANIZATION_ID,
SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,
SOURCE_ORG.SOURCE_PARTNER_ID,
SOURCE_ORG.SOURCE_PARTNER_SITE_ID;
l_inserted_rows := SQL%ROWCOUNT;
IF (l_inserted_rows > 0) THEN
-- Found some sources for some items.
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Get_Sources_Info: '|| 'Found some sources for some ship set items at level 3');
UPDATE msc_ship_set_temp
set visited_flag = 1
where inventory_item_id in (select inventory_item_id from msc_atp_sources_temp);
l_updated_rows := SQL%ROWCOUNT;
IF (l_updated_rows = p_ship_set_item_count) THEN
-- Find the common sources from the pool of sources in msc_atp_sources_temp
SELECT Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
sum(Rank), -- order by cum rank instead of group by rank
Source_Type,
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM MSC_ATP_SOURCES_TEMP
GROUP BY
Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
-- Rank, order by cum rank instead of group by rank
Source_Type
HAVING count(*) = p_ship_set_item_count
ORDER BY 5; -- Rank; order by cum rank instead of group by rank
INSERT INTO MSC_ATP_SOURCES_TEMP (inventory_item_id, Organization_Id, Instance_Id,
Supplier_Id, Supplier_Site_Id, Rank, Source_Type, Preferred, Lead_Time, Ship_Method)
SELECT
MSST.INVENTORY_ITEM_ID,
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCE_ORG.RANK, 0) + 1 - SOURCE_ORG.ALLOCATION_PERCENT/1000), --2910418
NVL(MIN(SOURCE_ORG.SOURCE_TYPE),
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)),
0,
-1,
'@@@'
FROM
MSC_SYSTEM_ITEMS ITEM,
-- MSC_TRADING_PARTNER_SITES TP,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
-- MSC_TP_SITE_ID_LID MTSIL,
MSC_SHIP_SET_TEMP MSST
WHERE
MSRA.ASSIGNMENT_TYPE = 3 /* ITEM */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.INVENTORY_ITEM_ID = MSST.INVENTORY_ITEM_ID
AND MSST.VISITED_FLAG = 0
AND ITEM.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEM.PLAN_ID = -1
-- AND ITEM.SR_INSTANCE_ID = MTSIL.SR_INSTANCE_ID
-- AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
-- AND MTSIL.TP_SITE_ID = TP.PARTNER_SITE_ID
-- AND TP.PARTNER_TYPE = 2
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE ,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
GROUP BY
MSST.INVENTORY_ITEM_ID,
SOURCE_ORG.SOURCE_ORGANIZATION_ID,
SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,
SOURCE_ORG.SOURCE_PARTNER_ID,
SOURCE_ORG.SOURCE_PARTNER_SITE_ID;
l_inserted_rows := SQL%ROWCOUNT;
IF (l_inserted_rows > 0) THEN
-- Found some sources for some items.
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Get_Sources_Info: '|| 'Found some sources for some ship set items at level 4');
UPDATE msc_ship_set_temp
set visited_flag = 1
where inventory_item_id in (select distinct(inventory_item_id) from msc_atp_sources_temp);
l_updated_rows := SQL%ROWCOUNT;
IF (l_updated_rows = p_ship_set_item_count) THEN
-- Find the common sources from the pool of sources in msc_atp_sources_temp
SELECT Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
sum(Rank), -- order by cum rank instead of group by rank
Source_Type,
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM MSC_ATP_SOURCES_TEMP
GROUP BY
Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
-- Rank, order by cum rank instead of group by rank
Source_Type
HAVING count(*) = p_ship_set_item_count
ORDER BY 5; -- Rank; order by cum rank instead of group by rank
SELECT
MSST_INNER.INVENTORY_ITEM_ID,
MIN(5000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 10) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0))
BULK COLLECT INTO
l_inventory_item_id_arr,
l_min_region_value_arr
FROM
MSC_ITEM_CATEGORIES CAT_INNER,
MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
MSC_SOURCING_RULES MSR_INNER,
MSC_SR_ASSIGNMENTS MSRA_INNER,
MSC_REGIONS_TEMP MRT_INNER,
MSC_SHIP_SET_TEMP MSST_INNER
WHERE
MSRA_INNER.ASSIGNMENT_TYPE = 8 /* CATEGORY-REGION */
AND MSRA_INNER.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA_INNER.REGION_ID = MRT_INNER.REGION_ID
AND MRT_INNER.PARTNER_SITE_ID IS NOT NULL
AND MRT_INNER.SESSION_ID = p_session_id
--AND MRT_INNER.PARTNER_SITE_ID = p_customer_site_id
--AND MRT_INNER.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MRT_INNER.PARTNER_SITE_ID = decode(NVL(p_partner_type,2), 2, p_customer_site_id , 4 , p_party_site_id, 5, p_order_line_id) --2814895
AND MRT_INNER.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
AND MSRA_INNER.CATEGORY_SET_ID = CAT_INNER.CATEGORY_SET_ID
AND MSRA_INNER.CATEGORY_NAME = CAT_INNER.CATEGORY_NAME
AND CAT_INNER.INVENTORY_ITEM_ID = MSST_INNER.INVENTORY_ITEM_ID
AND MSST_INNER.VISITED_FLAG = 0
AND CAT_INNER.ORGANIZATION_ID = SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID
AND CAT_INNER.SR_INSTANCE_ID = SOURCE_ORG_INNER.SR_INSTANCE_ID
AND SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG_INNER.SR_RECEIPT_ID = RECEIPT_ORG_INNER.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG_INNER.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG_INNER.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG_INNER.SOURCING_RULE_ID = MSR_INNER.SOURCING_RULE_ID
AND MSR_INNER.STATUS = 1
AND MSR_INNER.SOURCING_RULE_TYPE = 1
AND MSR_INNER.SOURCING_RULE_ID = MSRA_INNER.SOURCING_RULE_ID
GROUP BY MSST_INNER.INVENTORY_ITEM_ID;
UPDATE msc_ship_set_temp
SET min_region_value = l_min_region_value_arr(i),
visited_flag = 1
WHERE inventory_item_id = l_inventory_item_id_arr(i);
INSERT INTO MSC_ATP_SOURCES_TEMP (inventory_item_id, Organization_Id, Instance_Id,
Supplier_Id, Supplier_Site_Id, Rank, Source_Type, Preferred, Lead_Time, Ship_Method)
SELECT
MSST.INVENTORY_ITEM_ID,
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCE_ORG.RANK, 0) + 1 - SOURCE_ORG.ALLOCATION_PERCENT/1000), --2910418
NVL(MIN(SOURCE_ORG.SOURCE_TYPE),
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)),
0,
-1,
'@@@'
FROM
MSC_ITEM_CATEGORIES CAT,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_REGIONS_TEMP MRT,
MSC_SHIP_SET_TEMP MSST
WHERE
MSRA.ASSIGNMENT_TYPE = 8 /* CATEGORY-REGION */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.REGION_ID = MRT.REGION_ID
AND MRT.PARTNER_SITE_ID IS NOT NULL
AND MRT.SESSION_ID = p_session_id
--AND MRT.PARTNER_SITE_ID = p_customer_site_id
--AND MRT.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MRT.PARTNER_SITE_ID = decode(NVL(p_partner_type,2), 2, p_customer_site_id , 4 , p_party_site_id, 5, p_order_line_id) --2814895
AND MRT.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
AND (5000 + ((10 - NVL(MRT.REGION_TYPE, 0)) * 10) +
DECODE(MRT.ZONE_FLAG, 'Y', 1, 0)) = MSST.MIN_REGION_VALUE
AND MSRA.CATEGORY_SET_ID = CAT.CATEGORY_SET_ID
AND MSRA.CATEGORY_NAME = CAT.CATEGORY_NAME
AND CAT.INVENTORY_ITEM_ID = MSST.INVENTORY_ITEM_ID
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
GROUP BY
MSST.INVENTORY_ITEM_ID,
SOURCE_ORG.SOURCE_ORGANIZATION_ID,
SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,
SOURCE_ORG.SOURCE_PARTNER_ID,
SOURCE_ORG.SOURCE_PARTNER_SITE_ID;
SELECT COUNT(*)
INTO l_items_visited
FROM MSC_SHIP_SET_TEMP
WHERE VISITED_FLAG = 1;
SELECT Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
sum(Rank), -- order by cum rank instead of group by rank
Source_Type,
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM MSC_ATP_SOURCES_TEMP
GROUP BY
Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
-- Rank, order by cum rank instead of group by rank
Source_Type
HAVING count(*) = p_ship_set_item_count
ORDER BY 5; -- Rank;order by cum rank instead of group by rank
INSERT INTO MSC_ATP_SOURCES_TEMP (inventory_item_id, Organization_Id, Instance_Id,
Supplier_Id, Supplier_Site_Id, Rank, Source_Type, Preferred, Lead_Time, Ship_Method)
SELECT
MSST.INVENTORY_ITEM_ID,
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCE_ORG.RANK, 0) + 1 - SOURCE_ORG.ALLOCATION_PERCENT/1000), --2910418
NVL(MIN(SOURCE_ORG.SOURCE_TYPE),
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)),
0,
-1,
'@@@'
FROM
MSC_ITEM_CATEGORIES CAT,
-- MSC_TRADING_PARTNER_SITES TP,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
-- MSC_TP_SITE_ID_LID MTSIL,
MSC_SHIP_SET_TEMP MSST
WHERE
MSRA.ASSIGNMENT_TYPE = 2 /* CATEGORY */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.CATEGORY_NAME = CAT.CATEGORY_NAME
AND MSRA.CATEGORY_SET_ID = CAT.CATEGORY_SET_ID
AND CAT.INVENTORY_ITEM_ID = MSST.INVENTORY_ITEM_ID
AND MSST.VISITED_FLAG = 0
-- AND CAT.SR_INSTANCE_ID = MTSIL.SR_INSTANCE_ID
-- AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
-- AND MTSIL.TP_SITE_ID = TP.PARTNER_SITE_ID
-- AND TP.PARTNER_TYPE = 2
AND CAT.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = SOURCE_ORG.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
GROUP BY
MSST.INVENTORY_ITEM_ID,
SOURCE_ORG.SOURCE_ORGANIZATION_ID,
SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,
SOURCE_ORG.SOURCE_PARTNER_ID,
SOURCE_ORG.SOURCE_PARTNER_SITE_ID;
l_inserted_rows := SQL%ROWCOUNT;
IF (l_inserted_rows > 0) THEN
-- Found some sources for some items.
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Get_Sources_Info: '|| 'Found some sources for some ship set items at level 6');
UPDATE msc_ship_set_temp
set visited_flag = 1
where inventory_item_id in (select inventory_item_id from msc_atp_sources_temp);
l_updated_rows := SQL%ROWCOUNT;
IF (l_updated_rows = p_ship_set_item_count) THEN
-- Find the common sources from the pool of sources in msc_atp_sources_temp
SELECT Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
sum(Rank), -- order by cum rank instead of group by rank
Source_Type,
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM MSC_ATP_SOURCES_TEMP
GROUP BY
Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
-- Rank, order by cum rank instead of group by rank
Source_Type
HAVING count(*) = p_ship_set_item_count
ORDER BY 5; -- Rank; order by cum rank instead of group by rank
INSERT INTO MSC_ATP_SOURCES_TEMP (inventory_item_id, Organization_Id, Instance_Id,
Supplier_Id, Supplier_Site_Id, Rank, Source_Type, Preferred, Lead_Time, Ship_Method)
SELECT
MSST.INVENTORY_ITEM_ID,
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCE_ORG.RANK, 0) + 1 - SOURCE_ORG.ALLOCATION_PERCENT/1000), --2910418
NVL(MIN(SOURCE_ORG.SOURCE_TYPE),
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)),
0,
-1,
'@@@'
FROM
MSC_SYSTEM_ITEMS ITEM,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_TP_SITE_ID_LID MTSIL,
MSC_SHIP_SET_TEMP MSST
WHERE
MSRA.ASSIGNMENT_TYPE = 4 /* ORG */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.PARTNER_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID IS NOT NULL
AND MSRA.SHIP_TO_SITE_ID = MTSIL.TP_SITE_ID
AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
AND MTSIL.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID
AND ITEM.INVENTORY_ITEM_ID = MSST.INVENTORY_ITEM_ID
AND MSST.VISITED_FLAG = 0
AND ITEM.PLAN_ID = -1
AND ITEM.ORGANIZATION_ID = SOURCE_ORG.SOURCE_ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SOURCE_ORG.SOURCE_ORG_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate )) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
GROUP BY
MSST.INVENTORY_ITEM_ID,
SOURCE_ORG.SOURCE_ORGANIZATION_ID,
SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,
SOURCE_ORG.SOURCE_PARTNER_ID,
SOURCE_ORG.SOURCE_PARTNER_SITE_ID;
l_inserted_rows := SQL%ROWCOUNT;
IF (l_inserted_rows > 0) THEN
-- Found some sources for some items.
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Get_Sources_Info: '|| 'Found some sources for some ship set items at level 7');
UPDATE msc_ship_set_temp
set visited_flag = 1
where inventory_item_id in (select inventory_item_id from msc_atp_sources_temp);
l_updated_rows := SQL%ROWCOUNT;
IF (l_updated_rows = p_ship_set_item_count) THEN
-- Find the common sources from the pool of sources in msc_atp_sources_temp
SELECT Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
sum(Rank), -- order by cum rank instead of group by rank
Source_Type,
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM MSC_ATP_SOURCES_TEMP
GROUP BY
Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
-- Rank, order by cum rank instead of group by rank
Source_Type
HAVING count(*) = p_ship_set_item_count
ORDER BY 5; -- Rank; order by cum rank instead of group by rank
SELECT
MSST_INNER.INVENTORY_ITEM_ID,
MIN(8000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 100) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0))
BULK COLLECT INTO
l_inventory_item_id_arr,
l_min_region_value_arr
FROM
MSC_SYSTEM_ITEMS ITEM_INNER,
MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
MSC_SOURCING_RULES MSR_INNER,
MSC_SR_ASSIGNMENTS MSRA_INNER,
MSC_REGIONS_TEMP MRT_INNER,
MSC_SHIP_SET_TEMP MSST_INNER
WHERE
MSRA_INNER.ASSIGNMENT_TYPE = 7 /* REGION */
AND MSRA_INNER.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA_INNER.REGION_ID = MRT_INNER.REGION_ID
AND MRT_INNER.SESSION_ID = p_session_id
--AND MRT_INNER.PARTNER_SITE_ID = p_customer_site_id
AND MRT_INNER.PARTNER_SITE_ID IS NOT NULL
AND MRT_INNER.PARTNER_SITE_ID = decode(NVL(p_partner_type,2), 2, p_customer_site_id , 4 , p_party_site_id, 5, p_order_line_id) --2814895
AND MRT_INNER.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
--AND MRT_INNER.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MSRA_INNER.SOURCING_RULE_ID = MSR_INNER.SOURCING_RULE_ID
AND MSR_INNER.STATUS = 1
AND MSR_INNER.SOURCING_RULE_TYPE = 1
AND MSR_INNER.SOURCING_RULE_ID = RECEIPT_ORG_INNER.SOURCING_RULE_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG_INNER.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG_INNER.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG_INNER.SR_RECEIPT_ID = SOURCE_ORG_INNER.SR_RECEIPT_ID
AND SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG_INNER.SOURCE_ORGANIZATION_ID = ITEM_INNER.ORGANIZATION_ID
AND SOURCE_ORG_INNER.SR_INSTANCE_ID = ITEM_INNER.SR_INSTANCE_ID
AND ITEM_INNER.PLAN_ID = -1
AND ITEM_INNER.INVENTORY_ITEM_ID = MSST_INNER.INVENTORY_ITEM_ID
AND MSST_INNER.VISITED_FLAG = 0
GROUP BY MSST_INNER.INVENTORY_ITEM_ID;
UPDATE msc_ship_set_temp
SET min_region_value = l_min_region_value_arr(i),
visited_flag = 1
WHERE inventory_item_id = l_inventory_item_id_arr(i);
INSERT INTO MSC_ATP_SOURCES_TEMP (inventory_item_id, Organization_Id, Instance_Id,
Supplier_Id, Supplier_Site_Id, Rank, Source_Type, Preferred, Lead_Time, Ship_Method)
SELECT
MSST.INVENTORY_ITEM_ID,
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCE_ORG.RANK, 0) + 1 - SOURCE_ORG.ALLOCATION_PERCENT/1000), --2910418
NVL(MIN(SOURCE_ORG.SOURCE_TYPE),
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)),
0,
-1,
'@@@'
FROM
MSC_SYSTEM_ITEMS ITEM,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
MSC_REGIONS_TEMP MRT,
MSC_SHIP_SET_TEMP MSST
WHERE
MSRA.ASSIGNMENT_TYPE = 7 /* REGION */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.REGION_ID = MRT.REGION_ID
AND MRT.PARTNER_SITE_ID IS NOT NULL
AND MRT.SESSION_ID = p_session_id
--AND MRT.PARTNER_SITE_ID = p_customer_site_id
--AND MRT.PARTNER_TYPE = 2 -- For supplier intransit LT project
AND MRT.PARTNER_SITE_ID = decode(NVL(p_partner_type,2), 2, p_customer_site_id , 4 , p_party_site_id, 5, p_order_line_id) --2814895
AND MRT.PARTNER_TYPE = NVL(p_partner_type,2) --2814895
AND (8000 + ((10 - NVL(MRT.REGION_TYPE, 0)) * 100) +
DECODE(MRT.ZONE_FLAG, 'Y', 1, 0)) = MSST.MIN_REGION_VALUE
AND MSRA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = RECEIPT_ORG.SOURCING_RULE_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SR_RECEIPT_ID = SOURCE_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND SOURCE_ORG.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND ITEM.INVENTORY_ITEM_ID = MSST.INVENTORY_ITEM_ID
AND ITEM.PLAN_ID = -1
GROUP BY
MSST.INVENTORY_ITEM_ID,
SOURCE_ORG.SOURCE_ORGANIZATION_ID,
SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,
SOURCE_ORG.SOURCE_PARTNER_ID,
SOURCE_ORG.SOURCE_PARTNER_SITE_ID;
SELECT COUNT(*)
INTO l_items_visited
FROM MSC_SHIP_SET_TEMP
WHERE VISITED_FLAG = 1;
SELECT Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
sum(Rank), -- order by cum rank instead of group by rank
Source_Type,
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM MSC_ATP_SOURCES_TEMP
GROUP BY
Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
-- Rank, order by cum rank instead of group by rank
Source_Type
HAVING count(*) = p_ship_set_item_count
ORDER BY 5; -- Rank; order by cum rank instead of group by rank
INSERT INTO MSC_ATP_SOURCES_TEMP (inventory_item_id, Organization_Id, Instance_Id,
Supplier_Id, Supplier_Site_Id, Rank, Source_Type, Preferred, Lead_Time, Ship_Method)
SELECT
MSST.INVENTORY_ITEM_ID,
NVL(SOURCE_ORG.SOURCE_ORGANIZATION_ID, -1),
NVL(SOURCE_ORG.SOURCE_ORG_INSTANCE_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_ID, -1),
NVL(SOURCE_ORG.SOURCE_PARTNER_SITE_ID, -1),
SUM(NVL(SOURCE_ORG.RANK, 0) + 1 - SOURCE_ORG.ALLOCATION_PERCENT/1000), --2910418
NVL(MIN(SOURCE_ORG.SOURCE_TYPE),
DECODE(SOURCE_ORG.SOURCE_ORGANIZATION_ID,
to_number(null), 3, 1)),
0,
-1,
'@@@'
FROM
MSC_SYSTEM_ITEMS ITEM,
-- MSC_TRADING_PARTNER_SITES TP,
MSC_SR_SOURCE_ORG SOURCE_ORG,
MSC_SR_RECEIPT_ORG RECEIPT_ORG,
MSC_SOURCING_RULES MSR,
MSC_SR_ASSIGNMENTS MSRA,
-- MSC_TP_SITE_ID_LID MTSIL,
MSC_SHIP_SET_TEMP MSST
WHERE
MSRA.ASSIGNMENT_TYPE = 1 /* GLOBAL */
AND MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
AND MSRA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
AND MSR.STATUS = 1
AND MSR.SOURCING_RULE_TYPE = 1
AND MSR.SOURCING_RULE_ID = RECEIPT_ORG.SOURCING_RULE_ID
-- Bug 3787821: Changes for making the condition vaild if Sysdate = Disable_date
AND TRUNC(NVL(RECEIPT_ORG.DISABLE_DATE,l_sysdate)) >= l_sysdate
AND TRUNC(RECEIPT_ORG.EFFECTIVE_DATE) <= l_sysdate
AND RECEIPT_ORG.SR_RECEIPT_ID = SOURCE_ORG.SR_RECEIPT_ID
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID IS NOT NULL
AND SOURCE_ORG.SOURCE_ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND SOURCE_ORG.SR_INSTANCE_ID = ITEM.SR_INSTANCE_ID
AND ITEM.PLAN_ID = -1
AND ITEM.INVENTORY_ITEM_ID = MSST.INVENTORY_ITEM_ID
AND MSST.VISITED_FLAG = 0
-- AND ITEM.SR_INSTANCE_ID = MTSIL.SR_INSTANCE_ID
-- AND MTSIL.SR_TP_SITE_ID = p_customer_site_id
-- AND MTSIL.TP_SITE_ID = TP.PARTNER_SITE_ID
-- AND TP.PARTNER_TYPE = 2
GROUP BY
MSST.INVENTORY_ITEM_ID,
SOURCE_ORG.SOURCE_ORGANIZATION_ID,
SOURCE_ORG.SOURCE_ORG_INSTANCE_ID,
SOURCE_ORG.SOURCE_PARTNER_ID,
SOURCE_ORG.SOURCE_PARTNER_SITE_ID;
l_inserted_rows := SQL%ROWCOUNT;
IF (l_inserted_rows > 0) THEN
-- Found some sources for some items.
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Get_Sources_Info: '|| 'Found some sources for some ship set items at level 9');
UPDATE msc_ship_set_temp
set visited_flag = 1
where inventory_item_id in (select inventory_item_id from msc_atp_sources_temp);
l_updated_rows := SQL%ROWCOUNT;
IF (l_updated_rows = p_ship_set_item_count) THEN
-- Find the common sources from the pool of sources in msc_atp_sources_temp
SELECT Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
sum(Rank), -- order by cum rank instead of group by rank
Source_Type,
0,
-1,
'@@@',
NULL -- For supplier intransit LT project
BULK COLLECT INTO
x_atp_sources.Organization_Id,
x_atp_sources.Instance_Id,
x_atp_sources.Supplier_Id,
x_atp_sources.Supplier_Site_Id,
x_atp_sources.Rank,
x_atp_sources.Source_Type,
x_atp_sources.Preferred,
x_atp_sources.Lead_Time,
x_atp_sources.Ship_Method,
x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
FROM MSC_ATP_SOURCES_TEMP
GROUP BY
Organization_Id,
Instance_Id,
Supplier_Id,
Supplier_Site_Id,
-- Rank, order by cum rank instead of group by rank
Source_Type
HAVING count(*) = p_ship_set_item_count
ORDER BY 5; --Rank; order by cum rank instead of group by rank
PROCEDURE Update_Set_SD_Dates(
p_set IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
p_arrival_set IN mrp_atp_pub.date_arr
) IS
l_plan_info_rec MSC_ATP_PVT.plan_info_rec;
msc_sch_wb.atp_debug('Begin Update_Set_SD_Dates');
msc_sch_wb.atp_debug('Update_Set_SD_Dates : ' || 'sch arrival date: ' || p_set.group_arrival_date(m));
msc_sch_wb.atp_debug('Update_Set_SD_Dates : ' || 'lat accep date : ' || p_set.latest_acceptable_date(m));
msc_sch_wb.atp_debug('Update_Set_SD_Dates : ' || 'order_date_type : ' || l_order_date_type);
msc_sch_wb.atp_debug(' update_sd_date line: ' || m || ' date: ' || l_sd_date);
MSC_ATP_DB_UTILS.Update_SD_Date(p_set.Identifier(m),
p_set.instance_id(m), l_sd_date, l_plan_id,null, -- dsting setproc
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG(m),
MSC_ATP_PVT.G_REQ_ATP_DATE(m),
MSC_ATP_PVT.G_REQ_DATE_QTY(m), -- Bug 1501787
l_sd_date, -- Bug 2795053-reopen
null, -- For time_phased_atp
p_set.atf_date(m), -- For time_phased_atp
null, -- For time_phased_atp
p_set.group_arrival_date(m), -- For ship_rec_cal
l_order_date_type, -- For ship_rec_cal
p_set.latest_acceptable_date(m), -- For ship_rec_cal
p_set.ship_set_name(m),
p_set.arrival_set_name(m),
p_set.override_flag(m),
p_arrival_set(m),null --time_phased_atp --bug3397904
);
SELECT identifier3
INTO l_ship_arrival_date_rec.demand_id
FROM mrp_atp_details_temp
WHERE pegging_id = MSC_ATP_PVT.G_DEMAND_PEGGING_ID
AND session_id = MSC_ATP_PVT.G_SESSION_ID
AND record_type = 3;
msc_sch_wb.atp_debug('Update_Set_SD_Dates: ' || 'l_ship_arrival_date_rec.demand_id = ' || l_ship_arrival_date_rec.demand_id);
msc_sch_wb.atp_debug('Update_Set_SD_Dates: ' || 'Error in call to Flush_Data_In_Pds procedure ');
END Update_Set_SD_Dates;
SELECT intransit_time,
((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
FROM msc_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.plan_id = -1
AND mism.from_location_id = p_from_loc_id
AND mism.sr_instance_id = p_from_instance_id
AND mism.sr_instance_id2 = p_to_instance_id
AND mism.ship_method = x_ship_method
AND mism.to_region_id = mrt.region_id
AND mrt.session_id = p_session_id
AND mrt.partner_site_id = decode(NVL(p_partner_type,2), 2, p_partner_site_id, 4,p_party_site_id, 5, p_order_line_id) --2814895
AND mrt.partner_type = NVL(p_partner_type,2) --2814895 -- For supplier intransit LT project
ORDER BY 2;
SELECT ship_method, intransit_time,
((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
FROM msc_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.plan_id = -1
AND mism.from_location_id = p_from_loc_id
AND mism.sr_instance_id = p_from_instance_id
AND mism.sr_instance_id2 = p_to_instance_id
AND mism.default_flag = 1
AND mism.to_region_id = mrt.region_id
AND mrt.session_id = p_session_id
AND mrt.partner_site_id = decode( NVL(p_partner_type,2), 2, p_partner_site_id, 4, p_party_site_id, 5, p_order_line_id) --2814895
AND mrt.partner_type = NVL(p_partner_type,2) --2814895 -- For supplier intransit LT project
ORDER BY 3;
SELECT intransit_time,
mrt.region_type region_level -- collection has already translated data
FROM msc_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.plan_id = -1
AND mism.to_location_id = p_to_loc_id
AND mism.sr_instance_id2 = p_to_instance_id
AND mism.sr_instance_id = p_from_instance_id
AND mism.ship_method = x_ship_method
AND mism.from_region_id = mrt.region_id
AND mrt.session_id = p_session_id
AND mrt.partner_site_id = p_supplier_site_id
AND mrt.partner_type = 1 -- For supplier intransit LT project
ORDER BY 2;
SELECT ship_method, intransit_time,
mrt.region_type region_level -- collection has already translated data
FROM msc_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.plan_id = -1
AND mism.to_location_id = p_to_loc_id
AND mism.sr_instance_id2 = p_to_instance_id
AND mism.sr_instance_id = p_from_instance_id
AND mism.default_flag = 1
AND mism.from_region_id = mrt.region_id
AND mrt.session_id = p_session_id
AND mrt.partner_site_id = p_supplier_site_id
AND mrt.partner_type = 1 -- For supplier intransit LT project
ORDER BY 3;
SELECT intransit_time
INTO x_intransit_time
FROM msc_interorg_ship_methods
WHERE plan_id = -1
AND from_location_id = p_from_loc_id
AND sr_instance_id = p_from_instance_id
AND to_location_id = p_to_loc_id
AND sr_instance_id2 = p_to_instance_id
AND ship_method = x_ship_method
AND rownum = 1;
SELECT ship_method, intransit_time
INTO x_ship_method, x_intransit_time
FROM msc_interorg_ship_methods
WHERE plan_id = -1
AND from_location_id = p_from_loc_id
AND sr_instance_id = p_from_instance_id
AND to_location_id = p_to_loc_id
AND sr_instance_id2 = p_to_instance_id
AND default_flag = 1
AND rownum = 1;
msc_sch_wb.atp_debug('Get_Process_Effectivity: ' || 'Selecting Process Sequence ID');
SELECT a.process_sequence_id,a.routing_sequence_id, a.bill_sequence_id, a.operation_sequence_id --4570421
INTO x_process_seq_id,x_routing_seq_id,x_bill_seq_id, x_op_seq_id
FROM
(
/*
SELECT process_sequence_id,routing_sequence_id,bill_sequence_id
FROM msc_process_effectivity
WHERE plan_id = p_plan_id
AND organization_id = p_organization_id
AND item_id = p_item_id
AND sr_instance_id = p_sr_instance_id
AND p_requested_quantity BETWEEN NVL(minimum_quantity,0) AND
DECODE(NVL(maximum_quantity,0),0,99999999,maximum_quantity)
/* rajjain 3008611
* effective date should be greater than or equal to greatest of PTF date, sysdate and start date
* disable date should be less than or equal to greatest of PTF date, sysdate and start date
AND TRUNC(effectivity_date) <= TRUNC(GREATEST(p_new_schedule_date, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(NVL(disable_date,GREATEST(p_new_schedule_date, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1))
> TRUNC(GREATEST(p_new_schedule_date, sysdate, MSC_ATP_PVT.G_PTF_DATE))
ORDER BY preference */
--4570421
SELECT eff.process_sequence_id, eff.routing_sequence_id, eff.bill_sequence_id, op.operation_sequence_id
FROM msc_process_effectivity eff, msc_routing_operations op
WHERE eff.plan_id = p_plan_id
AND eff.organization_id = p_organization_id
AND eff.item_id = p_item_id
AND eff.sr_instance_id = p_sr_instance_id
AND p_requested_quantity BETWEEN NVL(eff.minimum_quantity,0) AND
DECODE(NVL(eff.maximum_quantity,0),0,99999999,maximum_quantity)
AND TRUNC(eff.effectivity_date) <= TRUNC(GREATEST(p_new_schedule_date, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(NVL(eff.disable_date,GREATEST(p_new_schedule_date, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1))
> TRUNC(GREATEST(p_new_schedule_date, sysdate, MSC_ATP_PVT.G_PTF_DATE))
--4570421
and eff.plan_id = op.plan_id(+)
AND eff.sr_instance_id = op.sr_instance_id(+)
and eff.routing_sequence_id = op.routing_sequence_id(+)
AND TRUNC(op.effectivity_date(+)) <= TRUNC(GREATEST(p_new_schedule_date, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(NVL(op.disable_date(+),GREATEST(p_new_schedule_date, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1))
> TRUNC(GREATEST(p_new_schedule_date, sysdate, MSC_ATP_PVT.G_PTF_DATE)) --4570421
ORDER BY eff.preference, op.operation_seq_num
) a
where rownum = 1;