[Home] [Help]
1: PACKAGE BODY MSC_ATP_SUBST AS
2: /* $Header: MSCSUBAB.pls 120.4 2007/12/12 10:40:07 sbnaik ship $ */
3: G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_ATP_SUBST';
4:
5: MATERIAL_CONSTRAINT CONSTANT NUMBER := 1;
1: PACKAGE BODY MSC_ATP_SUBST AS
2: /* $Header: MSCSUBAB.pls 120.4 2007/12/12 10:40:07 sbnaik ship $ */
3: G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_ATP_SUBST';
4:
5: MATERIAL_CONSTRAINT CONSTANT NUMBER := 1;
6: PTF_CONSTRAINT CONSTANT NUMBER := 2;
7: TRANSIT_LT_CONSTRAINT CONSTANT NUMBER := 5;
14:
15: PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
16:
17: PROCEDURE top_org_supply_qty(
18: p_org_avail_info IN OUT NoCopy MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ,
19: p_org_idx IN NUMBER
20: ) IS
21: j number;
22: l_qty number;
66: END top_org_supply_qty;
67:
68: FUNCTION org_req_dmd_qty(
69: p_qty IN NUMBER,
70: p_org_avail_info IN MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ,
71: p_org_idx IN NUMBER
72: ) RETURN NUMBER IS
73: l_qty number;
74: l_parent number;
93: return l_qty;
94: END org_req_dmd_qty;
95:
96: PROCEDURE Extend_Org_Avail_Info_Rec (
97: p_org_avail_info IN OUT NOCOPY MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ,
98: x_return_status OUT NoCopy VARCHAR2)
99: IS
100: Begin
101:
174:
175: PROCEDURE Prep_Common_Pegging_Rec(
176: x_pegging_rec OUT NOCOPY mrp_atp_details_temp%ROWTYPE,
177: p_atp_record IN MRP_ATP_PVT.AtpRec,
178: ORG_AVAIL_INFO IN MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ,
179: p_org_idx IN NUMBER,
180: Item_Availability_Info IN Item_Info_Rec_Typ,
181: p_item_idx In NUMBER
182: ) IS
219:
220: PROCEDURE Prep_Demand_Pegging_Rec(
221: x_pegging_rec OUT NOCOPY mrp_atp_details_temp%ROWTYPE,
222: p_atp_record IN MRP_ATP_PVT.AtpRec,
223: ORG_AVAIL_INFO IN MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ,
224: p_org_idx IN NUMBER,
225: Item_Availability_Info IN Item_Info_Rec_Typ,
226: p_item_idx In NUMBER
227: ) IS
305: */
306: PROCEDURE Prep_PO_Pegging_Rec(
307: x_pegging_rec OUT NOCOPY mrp_atp_details_temp%ROWTYPE,
308: p_atp_record IN MRP_ATP_PVT.AtpRec,
309: ORG_AVAIL_INFO IN MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ,
310: p_org_idx IN NUMBER,
311: Item_Availability_Info IN Item_Info_Rec_Typ,
312: p_item_idx In NUMBER,
313: p_PO_qty IN NUMBER,
434:
435: PROCEDURE Prep_Supply_Pegging_Rec(
436: x_pegging_rec OUT NOCOPY mrp_atp_details_temp%ROWTYPE,
437: p_atp_record IN MRP_ATP_PVT.AtpRec,
438: ORG_AVAIL_INFO IN MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ,
439: p_org_idx IN NUMBER,
440: Item_Availability_Info IN Item_Info_Rec_Typ,
441: p_item_idx In NUMBER,
442: p_transaction_id IN NUMBER
603: l_sysdate date;
604: l_net_demand number;
605: l_period_begin_idx number;
606: l_sd_begin_idx number;
607: ORG_AVAILABILITY_INFO MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ;
608: l_null_org_avail_info MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ;
609: l_return_status VARCHAR2(1);
610: l_atp_period MRP_ATP_PUB.ATP_Period_Typ;
611: L_NULL_ATP_PERIOD MRP_ATP_PUB.ATP_Period_Typ;
604: l_net_demand number;
605: l_period_begin_idx number;
606: l_sd_begin_idx number;
607: ORG_AVAILABILITY_INFO MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ;
608: l_null_org_avail_info MSC_ATP_SUBST.ATP_Org_Info_Rec_Typ;
609: l_return_status VARCHAR2(1);
610: l_atp_period MRP_ATP_PUB.ATP_Period_Typ;
611: L_NULL_ATP_PERIOD MRP_ATP_PUB.ATP_Period_Typ;
612: l_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
780: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'to_organization_id ' || p_atp_record.to_organization_id);
781: END IF;
782: --- first we set the Request Item's sr_inv_id to a global variable
783: --bug3467631 In PF cases request_item_id has member_id
784: --MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID := p_atp_record.inventory_item_id;
785: MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID := NVL(p_atp_record.request_item_id,
786: p_atp_record.inventory_item_id);
787: item_availability_info := p_item_substitute_rec;
788:
781: END IF;
782: --- first we set the Request Item's sr_inv_id to a global variable
783: --bug3467631 In PF cases request_item_id has member_id
784: --MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID := p_atp_record.inventory_item_id;
785: MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID := NVL(p_atp_record.request_item_id,
786: p_atp_record.inventory_item_id);
787: item_availability_info := p_item_substitute_rec;
788:
789: IF PG_DEBUG in ('Y', 'C') THEN
819: l_highest_rev_item_id := l_inventory_item_id;
820: END IF;
821:
822: -- dsting If we do not create supply on the original item then set the flag to 0
823: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG not in (G_DEMANDED_ITEM, G_ITEM_ATTRIBUTE) AND
824: NOT (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
825: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)))
826: OR item_availability_info.create_supply_flag(l_item_count) <> 1
827: THEN
820: END IF;
821:
822: -- dsting If we do not create supply on the original item then set the flag to 0
823: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG not in (G_DEMANDED_ITEM, G_ITEM_ATTRIBUTE) AND
824: NOT (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
825: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)))
826: OR item_availability_info.create_supply_flag(l_item_count) <> 1
827: THEN
828: l_create_supply_on_orig_item := 0;
915:
916: ---for each item we reset the org_availability_info record of tables
917: org_availability_info := l_null_org_avail_info;
918:
919: MSC_ATP_SUBST.Extend_Org_Avail_Info_Rec(org_availability_info, l_return_status);
920: org_availability_info.organization_id(1) := p_atp_record.organization_id;
921: org_availability_info.requested_ship_date(1) := l_requested_ship_date ;
922: -- Bug 3371817 - assigning calendars
923: org_availability_info.shipping_cal_code(1) := p_atp_record.shipping_cal_code;
1510:
1511: END IF;
1512:
1513: -- time_phased_atp changes begin
1514: --l_atp_insert_rec.request_item_id := MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID;
1515: l_atp_insert_rec.original_item_id := l_inventory_item_id ; --bug 5564075
1516: l_atp_insert_rec.latest_acceptable_date :=p_atp_record.latest_acceptable_date;
1517: -- ship_rec_cal
1518: l_atp_insert_rec.ship_method := p_atp_record.ship_method;
1522:
1523: --bug 4568088: Pass original_request date
1524: l_atp_insert_rec.original_request_ship_date := nvl(p_atp_record.original_request_date,
1525: l_atp_insert_rec.requested_ship_date);
1526: --MSC_ATP_SUBST.Add_Mat_Demand(l_atp_insert_rec,
1527: MSC_ATP_DB_UTILS.Add_Mat_Demand(l_atp_insert_rec,
1528: l_plan_id,
1529: l_demand_class_flag,
1530: l_demand_id);
1733: IF PG_DEBUG in ('Y', 'C') THEN
1734: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Top Org, update demand');
1735: END IF;
1736: --- for top org we always create order for the quantity ordered
1737: MSC_ATP_SUBST.UPDATE_DEMAND(org_availability_info.demand_id(l_process_org_cntr),
1738: org_availability_info.plan_id(l_process_org_cntr),
1739: p_atp_record.quantity_ordered);
1740: /* time_phased_atp
1741: put qty stolen upto req date ((not upto ATF) on member item */
1765: IF PG_DEBUG in ('Y', 'C') THEN
1766: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'second or later org, update demand as there is some stolen qty');
1767: END IF;
1768: --- org in second or later tear. Create demand for quantity available (req date qty+steal qty)
1769: MSC_ATP_SUBST.UPDATE_DEMAND(org_availability_info.demand_id(l_process_org_cntr),
1770: org_availability_info.plan_id(l_process_org_cntr),
1771: (org_availability_info.request_date_quantity(l_process_org_cntr)
1772: + l_stealing_qty
1773: ));
2128: --- since we have found the sources we set the flag to yes
2129: l_sources_found := 1;
2130: l_transfer_found := 1;
2131: ---extend the org_avail-indo
2132: MSC_ATP_SUBST.Extend_Org_Avail_Info_Rec(org_availability_info, l_return_status);
2133: l_count := org_availability_info.organization_id.count;
2134: org_availability_info.organization_id(l_count) := l_sources.organization_id(i);
2135: org_availability_info.requested_ship_date(l_count) := l_req_ship_date;
2136: org_availability_info.parent_org_idx(l_count) := l_parent_org_cntr;
2352: IF PG_DEBUG in ('Y', 'C') THEN
2353: msc_sch_wb.atp_debug('highest rev: ' || l_highest_rev_item_id);
2354: msc_sch_wb.atp_debug('l_sources_found: ' || l_sources_found);
2355: msc_sch_wb.atp_debug('l_transfer_found: ' || l_transfer_found);
2356: msc_sch_wb.atp_debug('G_CREATE_SUPPLY: ' || MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG);
2357: msc_sch_wb.atp_debug('item create_supply: ' || item_availability_info.create_supply_flag(l_item_count));
2358: msc_sch_wb.atp_debug('org: ' || org_availability_info.org_code(l_parent_org_cntr));
2359: END IF;
2360:
2389: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Done with ATP check, now add pegging, planned orders');
2390: msc_sch_wb.atp_debug('reset Subst last PO pegging: ');
2391: END IF;
2392:
2393: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := null;
2394: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := 0;
2395:
2396: -- dsting diag_atp
2397: -- I need to know the quantity from children in advance to balance supply/demand
2390: msc_sch_wb.atp_debug('reset Subst last PO pegging: ');
2391: END IF;
2392:
2393: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := null;
2394: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := 0;
2395:
2396: -- dsting diag_atp
2397: -- I need to know the quantity from children in advance to balance supply/demand
2398: /* 2754446 I'm doing this in advance already
2599:
2600: l_pegging_rec.atp_level:= p_level+1;
2601: l_pegging_rec.scenario_id:= p_scenario_id;
2602:
2603: MSC_ATP_SUBST.Add_Pegging(l_pegging_rec);
2604: END IF;
2605: ELSIF NVL(org_availability_info.demand_id(j), -1) > 0 THEN
2606: ---Above condition will filter out al the orgs that we did not visit but added to org_availability_info
2607: -- then we remove the demand provided its not in the top org
2644: --- if we do nto get anything from this org then we remove the demand
2645: IF PG_DEBUG in ('Y', 'C') THEN
2646: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Delete Demand');
2647: END IF;
2648: MSC_ATP_SUBST.delete_demand_subst(org_availability_info.demand_id(j),
2649: org_availability_info.plan_id(j));
2650: ELSE
2651:
2652: --- update demand
2663: --- as CTP is still left to be done
2664: IF PG_DEBUG in ('Y', 'C') THEN
2665: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Update demand');
2666: END IF;
2667: MSC_ATP_SUBST.UPDATE_DEMAND(org_availability_info.demand_id(j),
2668: org_availability_info.plan_id(j),
2669: -- dsting 2754446
2670: l_available_quantity
2671: * org_availability_info.conversion_rate(j));
2764:
2765: l_pegging_rec.atp_level:= p_level;
2766: l_pegging_rec.scenario_id:= p_scenario_id;
2767:
2768: MSC_ATP_SUBST.Add_Pegging(l_pegging_rec);
2769:
2770: --- we add supply pegging only for item where atp_flag = 'Y'
2771: IF org_availability_info.atp_flag(j) in ('Y', 'C') THEN
2772: --- now add supply pegging
2784:
2785: l_pegging_rec.atp_level:= p_level + 1;
2786: l_pegging_rec.scenario_id:= p_scenario_id;
2787:
2788: MSC_ATP_SUBST.Add_Pegging(l_pegging_rec);
2789: END IF;
2790: -- END IF; -- if l_available_qty > 0
2791:
2792: IF PG_DEBUG in ('Y', 'C') THEN
2834: END IF;
2835:
2836: -- dsting set G_TOP_LAST_PO_PEGGING and G_TOP_LAST_PO_QTY in case the ctp
2837: -- pass needs to adjust the PO from the onhand pass
2838: IF l_parent_index = 1 AND MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING is NULL THEN
2839: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := org_availability_info.PO_Pegging_id(j);
2840: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := l_diag_supply_qty;
2841: IF PG_DEBUG in ('Y', 'C') THEN
2842: msc_sch_wb.atp_debug('Subst last PO pegging: ' || MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING);
2835:
2836: -- dsting set G_TOP_LAST_PO_PEGGING and G_TOP_LAST_PO_QTY in case the ctp
2837: -- pass needs to adjust the PO from the onhand pass
2838: IF l_parent_index = 1 AND MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING is NULL THEN
2839: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := org_availability_info.PO_Pegging_id(j);
2840: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := l_diag_supply_qty;
2841: IF PG_DEBUG in ('Y', 'C') THEN
2842: msc_sch_wb.atp_debug('Subst last PO pegging: ' || MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING);
2843: END IF;
2836: -- dsting set G_TOP_LAST_PO_PEGGING and G_TOP_LAST_PO_QTY in case the ctp
2837: -- pass needs to adjust the PO from the onhand pass
2838: IF l_parent_index = 1 AND MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING is NULL THEN
2839: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := org_availability_info.PO_Pegging_id(j);
2840: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := l_diag_supply_qty;
2841: IF PG_DEBUG in ('Y', 'C') THEN
2842: msc_sch_wb.atp_debug('Subst last PO pegging: ' || MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING);
2843: END IF;
2844: END IF;
2838: IF l_parent_index = 1 AND MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING is NULL THEN
2839: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := org_availability_info.PO_Pegging_id(j);
2840: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := l_diag_supply_qty;
2841: IF PG_DEBUG in ('Y', 'C') THEN
2842: msc_sch_wb.atp_debug('Subst last PO pegging: ' || MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING);
2843: END IF;
2844: END IF;
2845:
2846: IF l_diag_supply_qty > 0
2843: END IF;
2844: END IF;
2845:
2846: IF l_diag_supply_qty > 0
2847: OR MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING = org_availability_info.PO_Pegging_id(j)
2848: THEN
2849:
2850: -- Begin ATP4drp Create Planned Arrivals for DRP plans
2851: IF (NVL(MSC_ATP_PVT.G_PLAN_INFO_REC.plan_type,1) = 5) THEN
3063:
3064: l_pegging_rec.atp_level:= p_level+1;
3065: l_pegging_rec.scenario_id:= p_scenario_id;
3066:
3067: MSC_ATP_SUBST.Add_Pegging(l_pegging_rec);
3068:
3069: END IF;
3070: END IF; -- <
3071: END IF; -- IF NVL(org_availability_info.demand_id(j), -1) > 0 THEN
3242: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'sd_detail_begin_idx:= ' || item_availability_info.sd_detail_begin_idx(l_item_cntr));
3243: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'sd_detail_end_idx:= ' || item_availability_info.sd_detail_end_idx(l_item_cntr));
3244: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'p_atp_record.Atf_Date_Quantity:= ' || p_atp_record.Atf_Date_Quantity); --bug3467631
3245: END IF;
3246: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
3247: l_all_atp_supply_demand,
3248: item_availability_info.period_detail_begin_idx(l_item_cntr),
3249: item_availability_info.period_detail_end_idx(l_item_cntr),
3250: item_availability_info.sd_detail_begin_idx(l_item_cntr),
3254: l_return_status);
3255: --- now remove the demands for other items
3256: FOR i in (l_item_cntr +1)..l_item_count LOOP
3257: IF NOT (l_org_item_detail_flag = 1 and l_satisfied_by_subst_flag = 1 and
3258: item_availability_info.sr_inventory_item_id(i) = MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID) THEN
3259: IF PG_DEBUG in ('Y', 'C') THEN
3260: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'remove demands for item ' || item_availability_info.item_name(i));
3261: END IF;
3262: MSC_ATP_DB_UTILS.Remove_Invalid_SD_Rec(
3311: --- if create supply flag is null then default it to Demanded item
3312: IF PG_DEBUG in ('Y', 'C') THEN
3313: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Backward Ctp');
3314: END IF;
3315: l_create_supply_flag := MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG;
3316: l_count := item_availability_info.inventory_item_id.count;
3317: IF PG_DEBUG in ('Y', 'C') THEN
3318: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'G_CREATE_SUPPLY_FLAG := ' || MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG);
3319: END IF;
3314: END IF;
3315: l_create_supply_flag := MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG;
3316: l_count := item_availability_info.inventory_item_id.count;
3317: IF PG_DEBUG in ('Y', 'C') THEN
3318: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'G_CREATE_SUPPLY_FLAG := ' || MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG);
3319: END IF;
3320: --- bug 2388707: if demand has already been met then just add req item to the list of items
3321: -- to do CTP on
3322: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
3319: END IF;
3320: --- bug 2388707: if demand has already been met then just add req item to the list of items
3321: -- to do CTP on
3322: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
3323: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG in (G_DEMANDED_ITEM, G_ITEM_ATTRIBUTE))
3324: OR (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
3325: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
3326: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3327: END IF;
3320: --- bug 2388707: if demand has already been met then just add req item to the list of items
3321: -- to do CTP on
3322: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
3323: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG in (G_DEMANDED_ITEM, G_ITEM_ATTRIBUTE))
3324: OR (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
3325: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
3326: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3327: END IF;
3328: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3322: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
3323: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG in (G_DEMANDED_ITEM, G_ITEM_ATTRIBUTE))
3324: OR (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
3325: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
3326: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3327: END IF;
3328: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3329:
3330: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3324: OR (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
3325: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
3326: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3327: END IF;
3328: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3329:
3330: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3331:
3332: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
3326: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3327: END IF;
3328: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3329:
3330: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3331:
3332: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
3333: IF item_availability_info.inventory_item_id.count = 1 THEN
3334: --- we will come here if there are no substitutes or we are not substituting
3328: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3329:
3330: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3331:
3332: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
3333: IF item_availability_info.inventory_item_id.count = 1 THEN
3334: --- we will come here if there are no substitutes or we are not substituting
3335: IF PG_DEBUG in ('Y', 'C') THEN
3336: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'One item. highest rev ' || l_highest_rev_item_id);
3337: END IF;
3338: IF (l_highest_rev_item_id = l_inventory_item_id) AND
3339: (item_availability_info.create_supply_flag(l_item_count) = 1) THEN
3340:
3341: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3342: END IF;
3343: ELSE
3344: --- we will come here if we have one or more substitutes and we are doing substitution
3345: IF PG_DEBUG in ('Y', 'C') THEN
3358: END LOOP;
3359: IF PG_DEBUG in ('Y', 'C') THEN
3360: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Counter for High. rev. item := ' || l_highest_rev_item_index);
3361: END IF;
3362: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_highest_rev_item_index);
3363: END IF; -- IF item_availability_info.inventory_item_id.count = 1 THEN
3364: ELSE --- item attribute
3365: --- look at item attributes
3366: IF l_net_demand_after_oh_check > 0 THEN
3367: --- we haven't satisfied the demand as yet
3368: l_item_ctp_info := item_availability_info;
3369: ELSE
3370: --- we have satisfied the demand but we need to look for original item
3371: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_count);
3372: END IF;
3373: END IF; -- IF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = 701
3374:
3375: IF PG_DEBUG in ('Y', 'C') THEN
3369: ELSE
3370: --- we have satisfied the demand but we need to look for original item
3371: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_count);
3372: END IF;
3373: END IF; -- IF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = 701
3374:
3375: IF PG_DEBUG in ('Y', 'C') THEN
3376: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'List of items to do CTP on');
3377: For i in reverse 1..l_item_ctp_info.inventory_item_id.count LOOP
3376: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'List of items to do CTP on');
3377: For i in reverse 1..l_item_ctp_info.inventory_item_id.count LOOP
3378: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || l_item_ctp_info.item_name(i) || ' ' || l_item_ctp_info.sr_inventory_item_id(i) );
3379: END LOOP;
3380: msc_sch_wb.atp_debug('Subst last PO pegging: ' || MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING);
3381: END IF;
3382:
3383: -- AT this point we know on what item do we need to do CTP
3384: --l_net_demand :=
3514: -- therefore we set the index to be that of last item.
3515: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
3516: l_item_idx := l_item_count;
3517:
3518: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3519: IF PG_DEBUG in ('Y', 'C') THEN
3520: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'More item but we retun the index for original item');
3521: END IF;
3522: l_item_idx := l_item_count;
3519: IF PG_DEBUG in ('Y', 'C') THEN
3520: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'More item but we retun the index for original item');
3521: END IF;
3522: l_item_idx := l_item_count;
3523: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
3524: IF PG_DEBUG in ('Y', 'C') THEN
3525: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Index for highest rev item');
3526: END IF;
3527: l_item_idx := l_highest_rev_item_index;
3643: END IF;
3644: p_atp_record.available_quantity := p_atp_record.quantity_ordered;
3645: l_count := Item_availability_info.inventory_item_id.count;
3646: p_atp_record.req_item_req_date_qty := Item_availability_info.request_date_quantity(l_item_count);
3647: IF l_item_ctp_info.sr_inventory_item_id(i) = MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID THEN
3648: --- request has been fullfilled by original item
3649: p_atp_record.req_item_req_date_qty :=
3650: item_availability_info.request_date_quantity(l_item_count);
3651: p_atp_record.req_item_available_date := l_requested_ship_date;
3655:
3656:
3657: --- now add supply demand/period details to out_put table
3658: --- first add onhand/sch recp supply demand details
3659: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
3660: l_all_atp_supply_demand,
3661: l_item_ctp_info.period_detail_begin_idx(i),
3662: l_item_ctp_info.period_detail_end_idx(i),
3663: l_item_ctp_info.sd_detail_begin_idx(i),
3675: --- Now remove the pegging/ supply demand for other items
3676: For j in 1..item_availability_info.inventory_item_id.count LOOP
3677: IF (item_availability_info.inventory_item_id(j) <> l_item_ctp_info.inventory_item_id(i)) AND
3678: NOT(l_satisfied_by_subst_flag = 1 and l_org_item_detail_flag = 1 AND
3679: item_availability_info.sr_inventory_item_id(j) = MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID) THEN
3680:
3681: IF PG_DEBUG in ('Y', 'C') THEN
3682: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Remove Pegging for item := ' || item_availability_info.inventory_item_id(i));
3683: END IF;
3871: item_availability_info.item_name(i));
3872: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Net demand future case := ' || l_net_demand);
3873: END IF;
3874: --- first update the demand so that the picture gets adjusted
3875: MSC_ATP_SUBST.UPDATE_DEMAND(item_availability_info.demand_id(i),
3876: item_availability_info.plan_id(i),
3877: item_availability_info.request_date_quantity(i));
3878:
3879: /* time_phased_atp */
4184: IF PG_DEBUG in ('Y', 'C') THEN
4185: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || ' Demand was met by a CTP on sub. reset the l_item_ctp_info table');
4186: END IF;
4187: l_item_ctp_info := l_null_item_avail_info;
4188: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG <> G_HIGHEST_REV_ITEM) or
4189: (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM and
4190: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
4191:
4192: MSC_ATP_SUBST.Copy_Item_Info_Rec(item_availability_info, l_item_ctp_info, l_item_count);
4185: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || ' Demand was met by a CTP on sub. reset the l_item_ctp_info table');
4186: END IF;
4187: l_item_ctp_info := l_null_item_avail_info;
4188: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG <> G_HIGHEST_REV_ITEM) or
4189: (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM and
4190: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
4191:
4192: MSC_ATP_SUBST.Copy_Item_Info_Rec(item_availability_info, l_item_ctp_info, l_item_count);
4193: END IF;
4188: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG <> G_HIGHEST_REV_ITEM) or
4189: (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM and
4190: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
4191:
4192: MSC_ATP_SUBST.Copy_Item_Info_Rec(item_availability_info, l_item_ctp_info, l_item_count);
4193: END IF;
4194: END IF;
4195: FOR i in reverse 1..l_item_ctp_info.inventory_item_id.count LOOP
4196:
4206: ELSE
4207: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
4208: l_item_idx := l_item_count;
4209:
4210: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
4211:
4212: l_item_idx := l_item_count;
4213: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
4214:
4209:
4210: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
4211:
4212: l_item_idx := l_item_count;
4213: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
4214:
4215: l_item_idx := l_highest_rev_item_index;
4216: ELSE
4217:
4534: --- first we add details for backward search
4535: IF PG_DEBUG in ('Y', 'C') THEN
4536: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Add details for pegging for backward search');
4537: END IF;
4538: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
4539: l_all_atp_supply_demand,
4540: item_availability_info.period_detail_begin_idx(l_item_idx),
4541: item_availability_info.period_detail_end_idx(l_item_idx),
4542: item_availability_info.sd_detail_begin_idx(l_item_idx),
4549: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Add details for backward CTP');
4550: END IF;
4551: IF item_availability_info.ctp_prd_detl_begin_idx(l_item_idx) is not null and
4552: item_availability_info.ctp_sd_detl_begin_idx(l_item_idx) is not null THEN
4553: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
4554: l_all_atp_supply_demand,
4555: item_availability_info.ctp_prd_detl_begin_idx(l_item_idx),
4556: item_availability_info.ctp_prd_detl_end_idx(l_item_idx),
4557: item_availability_info.ctp_sd_detl_begin_idx(l_item_idx),
4569: --- ad future period and supply demand details
4570: IF PG_DEBUG in ('Y', 'C') THEN
4571: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Add s/d details for forward CTP');
4572: END IF;
4573: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
4574: l_all_atp_supply_demand,
4575: item_availability_info.fut_ctp_prd_detl_begin_idx(l_item_idx),
4576: item_availability_info.fut_ctp_prd_detl_end_idx(l_item_idx),
4577: item_availability_info.fut_ctp_sd_detl_begin_idx(l_item_idx),
4654:
4655: l_pegging_rec.summary_flag := MSC_ATP_PVT.G_SUMMARY_FLAG;
4656: l_pegging_rec.request_item_id := p_atp_record.request_item_id;
4657:
4658: MSC_ATP_SUBST.add_pegging(l_pegging_rec);
4659: IF PG_DEBUG in ('Y', 'C') THEN
4660: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_future_supply_pegging_id :=' || l_pegging_id);
4661: END IF;
4662: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
4658: MSC_ATP_SUBST.add_pegging(l_pegging_rec);
4659: IF PG_DEBUG in ('Y', 'C') THEN
4660: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_future_supply_pegging_id :=' || l_pegging_id);
4661: END IF;
4662: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
4663: l_all_atp_supply_demand,
4664: item_availability_info.fut_atp_prd_detl_begin_idx(l_item_idx),
4665: item_availability_info.fut_atp_prd_detl_end_idx(l_item_idx),
4666: item_availability_info.fut_atp_sd_detl_begin_idx(l_item_idx),
4826: END IF;
4827: END ATP_Check_Subst;
4828:
4829: Procedure Get_Item_Substitutes(p_inventory_item_id IN NUMBER,
4830: p_item_table IN OUT NoCopy MSC_ATP_SUBST.Item_Info_Rec_Typ,
4831: p_instance_id IN NUMBER,
4832: p_plan_id IN NUMBER,
4833: p_customer_id IN NUMBER,
4834: p_customer_site_id IN NUMBER,
4834: p_customer_site_id IN NUMBER,
4835: p_request_date IN DATE,
4836: p_organization_id IN NUMBER)
4837: IS
4838: l_item_table MSC_ATP_SUBST.Item_Info_Rec_Typ;
4839: l_effective_dates MRP_ATP_PUB.date_arr;
4840: l_disable_dates MRP_ATP_PUB.date_arr;
4841: i number;
4842: l_count number;
5475: END IF;
5476: END ADD_PEGGING;
5477:
5478: Procedure Extend_Item_Info_Rec_Typ(
5479: p_item_avail_info IN OUT NOCOPY MSC_ATP_SUBST.Item_Info_Rec_Typ,
5480: x_return_status OUT NoCopy VARCHAR2)
5481: IS
5482: l_count number;
5483: BEGIN
5773: END IF;
5774:
5775: END Details_Output;
5776:
5777: Procedure Copy_Item_Info_Rec(p_parent_item_info IN MSC_ATP_SUBST.Item_Info_Rec_Typ,
5778: p_child_item_info IN OUT NoCopy MSC_ATP_SUBST.Item_Info_Rec_Typ,
5779: p_index IN NUMBER)
5780: IS
5781: l_return_status varchar2(1);
5774:
5775: END Details_Output;
5776:
5777: Procedure Copy_Item_Info_Rec(p_parent_item_info IN MSC_ATP_SUBST.Item_Info_Rec_Typ,
5778: p_child_item_info IN OUT NoCopy MSC_ATP_SUBST.Item_Info_Rec_Typ,
5779: p_index IN NUMBER)
5780: IS
5781: l_return_status varchar2(1);
5782: l_count number;
5783: BEGIN
5784: IF PG_DEBUG in ('Y', 'C') THEN
5785: msc_sch_wb.atp_debug('***** start Copy_Item_Info_Rec ****');
5786: END IF;
5787: MSC_ATP_SUBST.Extend_Item_Info_Rec_Typ(p_child_item_info, l_return_status);
5788: l_count := p_child_item_info.inventory_item_id.count;
5789: p_child_item_info.inventory_item_id(l_count) := p_parent_item_info.inventory_item_id(p_index);
5790: p_child_item_info.sr_inventory_item_id(l_count) := p_parent_item_info.sr_inventory_item_id(p_index);
5791: p_child_item_info.highest_revision_item_id(l_count) := p_parent_item_info.highest_revision_item_id(p_index);
5989: END IF;
5990:
5991: END Add_Mat_Demand;
5992: */
5993: End MSC_ATP_SUBST;