[Home] [Help]
1: PACKAGE BODY MSC_ATP_SUBST AS
2: /* $Header: MSCSUBAB.pls 120.10 2011/12/06 10:09:53 vjuluri 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.10 2011/12/06 10:09:53 vjuluri 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;
784: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'MSC_ATP_PVT.G_PLAN_INFO_REC.plan_type ' || MSC_ATP_PVT.G_PLAN_INFO_REC.plan_type);
785: END IF;
786: --- first we set the Request Item's sr_inv_id to a global variable
787: --bug3467631 In PF cases request_item_id has member_id
788: --MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID := p_atp_record.inventory_item_id;
789: MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID := NVL(p_atp_record.request_item_id,
790: p_atp_record.inventory_item_id);
791: item_availability_info := p_item_substitute_rec;
792:
785: END IF;
786: --- first we set the Request Item's sr_inv_id to a global variable
787: --bug3467631 In PF cases request_item_id has member_id
788: --MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID := p_atp_record.inventory_item_id;
789: MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID := NVL(p_atp_record.request_item_id,
790: p_atp_record.inventory_item_id);
791: item_availability_info := p_item_substitute_rec;
792:
793: IF PG_DEBUG in ('Y', 'C') THEN
823: l_highest_rev_item_id := l_inventory_item_id;
824: END IF;
825:
826: -- dsting If we do not create supply on the original item then set the flag to 0
827: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG not in (G_DEMANDED_ITEM, G_ITEM_ATTRIBUTE) AND
828: NOT (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
829: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)))
830: OR item_availability_info.create_supply_flag(l_item_count) <> 1
831: THEN
824: END IF;
825:
826: -- dsting If we do not create supply on the original item then set the flag to 0
827: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG not in (G_DEMANDED_ITEM, G_ITEM_ATTRIBUTE) AND
828: NOT (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
829: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)))
830: OR item_availability_info.create_supply_flag(l_item_count) <> 1
831: THEN
832: l_create_supply_on_orig_item := 0;
919:
920: ---for each item we reset the org_availability_info record of tables
921: org_availability_info := l_null_org_avail_info;
922:
923: MSC_ATP_SUBST.Extend_Org_Avail_Info_Rec(org_availability_info, l_return_status);
924: org_availability_info.organization_id(1) := p_atp_record.organization_id;
925: org_availability_info.requested_ship_date(1) := l_requested_ship_date ;
926: -- Bug 3371817 - assigning calendars
927: org_availability_info.shipping_cal_code(1) := p_atp_record.shipping_cal_code;
1533:
1534: END IF;
1535:
1536: -- time_phased_atp changes begin
1537: --l_atp_insert_rec.request_item_id := MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID;
1538: l_atp_insert_rec.original_item_id := l_inventory_item_id ; --bug 5564075
1539: l_atp_insert_rec.latest_acceptable_date :=p_atp_record.latest_acceptable_date;
1540: -- ship_rec_cal
1541: l_atp_insert_rec.ship_method := p_atp_record.ship_method;
1545:
1546: --bug 4568088: Pass original_request date
1547: l_atp_insert_rec.original_request_ship_date := nvl(p_atp_record.original_request_date,
1548: l_atp_insert_rec.requested_ship_date);
1549: --MSC_ATP_SUBST.Add_Mat_Demand(l_atp_insert_rec,
1550: MSC_ATP_DB_UTILS.Add_Mat_Demand(l_atp_insert_rec,
1551: l_plan_id,
1552: l_demand_class_flag,
1553: l_demand_id);
1756: IF PG_DEBUG in ('Y', 'C') THEN
1757: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Top Org, update demand');
1758: END IF;
1759: --- for top org we always create order for the quantity ordered
1760: MSC_ATP_SUBST.UPDATE_DEMAND(org_availability_info.demand_id(l_process_org_cntr),
1761: org_availability_info.plan_id(l_process_org_cntr),
1762: p_atp_record.quantity_ordered);
1763: /* time_phased_atp
1764: put qty stolen upto req date ((not upto ATF) on member item */
1788: IF PG_DEBUG in ('Y', 'C') THEN
1789: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'second or later org, update demand as there is some stolen qty');
1790: END IF;
1791: --- org in second or later tear. Create demand for quantity available (req date qty+steal qty)
1792: MSC_ATP_SUBST.UPDATE_DEMAND(org_availability_info.demand_id(l_process_org_cntr),
1793: org_availability_info.plan_id(l_process_org_cntr),
1794: (org_availability_info.request_date_quantity(l_process_org_cntr)
1795: + l_stealing_qty
1796: ));
2151: --- since we have found the sources we set the flag to yes
2152: l_sources_found := 1;
2153: l_transfer_found := 1;
2154: ---extend the org_avail-indo
2155: MSC_ATP_SUBST.Extend_Org_Avail_Info_Rec(org_availability_info, l_return_status);
2156: l_count := org_availability_info.organization_id.count;
2157: org_availability_info.organization_id(l_count) := l_sources.organization_id(i);
2158: org_availability_info.requested_ship_date(l_count) := l_req_ship_date;
2159: org_availability_info.parent_org_idx(l_count) := l_parent_org_cntr;
2375: IF PG_DEBUG in ('Y', 'C') THEN
2376: msc_sch_wb.atp_debug('highest rev: ' || l_highest_rev_item_id);
2377: msc_sch_wb.atp_debug('l_sources_found: ' || l_sources_found);
2378: msc_sch_wb.atp_debug('l_transfer_found: ' || l_transfer_found);
2379: msc_sch_wb.atp_debug('G_CREATE_SUPPLY: ' || MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG);
2380: msc_sch_wb.atp_debug('item create_supply: ' || item_availability_info.create_supply_flag(l_item_count));
2381: msc_sch_wb.atp_debug('org: ' || org_availability_info.org_code(l_parent_org_cntr));
2382: END IF;
2383:
2412: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Done with ATP check, now add pegging, planned orders');
2413: msc_sch_wb.atp_debug('reset Subst last PO pegging: ');
2414: END IF;
2415:
2416: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := null;
2417: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := 0;
2418:
2419: -- dsting diag_atp
2420: -- I need to know the quantity from children in advance to balance supply/demand
2413: msc_sch_wb.atp_debug('reset Subst last PO pegging: ');
2414: END IF;
2415:
2416: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := null;
2417: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := 0;
2418:
2419: -- dsting diag_atp
2420: -- I need to know the quantity from children in advance to balance supply/demand
2421: /* 2754446 I'm doing this in advance already
2622:
2623: l_pegging_rec.atp_level:= p_level+1;
2624: l_pegging_rec.scenario_id:= p_scenario_id;
2625:
2626: MSC_ATP_SUBST.Add_Pegging(l_pegging_rec);
2627: END IF;
2628: ELSIF NVL(org_availability_info.demand_id(j), -1) > 0 THEN
2629: ---Above condition will filter out al the orgs that we did not visit but added to org_availability_info
2630: -- then we remove the demand provided its not in the top org
2667: --- if we do nto get anything from this org then we remove the demand
2668: IF PG_DEBUG in ('Y', 'C') THEN
2669: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Delete Demand');
2670: END IF;
2671: MSC_ATP_SUBST.delete_demand_subst(org_availability_info.demand_id(j),
2672: org_availability_info.plan_id(j));
2673: ELSE
2674:
2675: --- update demand
2686: --- as CTP is still left to be done
2687: IF PG_DEBUG in ('Y', 'C') THEN
2688: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Update demand');
2689: END IF;
2690: MSC_ATP_SUBST.UPDATE_DEMAND(org_availability_info.demand_id(j),
2691: org_availability_info.plan_id(j),
2692: -- dsting 2754446
2693: l_available_quantity
2694: * org_availability_info.conversion_rate(j));
2787:
2788: l_pegging_rec.atp_level:= p_level;
2789: l_pegging_rec.scenario_id:= p_scenario_id;
2790:
2791: MSC_ATP_SUBST.Add_Pegging(l_pegging_rec);
2792:
2793: --- we add supply pegging only for item where atp_flag = 'Y'
2794: IF org_availability_info.atp_flag(j) in ('Y', 'C') THEN
2795: --- now add supply pegging
2807:
2808: l_pegging_rec.atp_level:= p_level + 1;
2809: l_pegging_rec.scenario_id:= p_scenario_id;
2810:
2811: MSC_ATP_SUBST.Add_Pegging(l_pegging_rec);
2812: END IF;
2813: -- END IF; -- if l_available_qty > 0
2814:
2815: IF PG_DEBUG in ('Y', 'C') THEN
2857: END IF;
2858:
2859: -- dsting set G_TOP_LAST_PO_PEGGING and G_TOP_LAST_PO_QTY in case the ctp
2860: -- pass needs to adjust the PO from the onhand pass
2861: IF l_parent_index = 1 AND MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING is NULL THEN
2862: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := org_availability_info.PO_Pegging_id(j);
2863: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := l_diag_supply_qty;
2864: IF PG_DEBUG in ('Y', 'C') THEN
2865: msc_sch_wb.atp_debug('Subst last PO pegging: ' || MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING);
2858:
2859: -- dsting set G_TOP_LAST_PO_PEGGING and G_TOP_LAST_PO_QTY in case the ctp
2860: -- pass needs to adjust the PO from the onhand pass
2861: IF l_parent_index = 1 AND MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING is NULL THEN
2862: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := org_availability_info.PO_Pegging_id(j);
2863: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := l_diag_supply_qty;
2864: IF PG_DEBUG in ('Y', 'C') THEN
2865: msc_sch_wb.atp_debug('Subst last PO pegging: ' || MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING);
2866: END IF;
2859: -- dsting set G_TOP_LAST_PO_PEGGING and G_TOP_LAST_PO_QTY in case the ctp
2860: -- pass needs to adjust the PO from the onhand pass
2861: IF l_parent_index = 1 AND MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING is NULL THEN
2862: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := org_availability_info.PO_Pegging_id(j);
2863: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := l_diag_supply_qty;
2864: IF PG_DEBUG in ('Y', 'C') THEN
2865: msc_sch_wb.atp_debug('Subst last PO pegging: ' || MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING);
2866: END IF;
2867: END IF;
2861: IF l_parent_index = 1 AND MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING is NULL THEN
2862: MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING := org_availability_info.PO_Pegging_id(j);
2863: MSC_ATP_SUBST.G_TOP_LAST_PO_QTY := l_diag_supply_qty;
2864: IF PG_DEBUG in ('Y', 'C') THEN
2865: msc_sch_wb.atp_debug('Subst last PO pegging: ' || MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING);
2866: END IF;
2867: END IF;
2868:
2869: IF l_diag_supply_qty > 0
2866: END IF;
2867: END IF;
2868:
2869: IF l_diag_supply_qty > 0
2870: OR MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING = org_availability_info.PO_Pegging_id(j)
2871: THEN
2872:
2873: -- Begin ATP4drp Create Planned Arrivals for DRP plans
2874: IF (NVL(MSC_ATP_PVT.G_PLAN_INFO_REC.plan_type,1) = 5) THEN
3086:
3087: l_pegging_rec.atp_level:= p_level+1;
3088: l_pegging_rec.scenario_id:= p_scenario_id;
3089:
3090: MSC_ATP_SUBST.Add_Pegging(l_pegging_rec);
3091:
3092: END IF;
3093: END IF; -- <
3094: END IF; -- IF NVL(org_availability_info.demand_id(j), -1) > 0 THEN
3265: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'sd_detail_begin_idx:= ' || item_availability_info.sd_detail_begin_idx(l_item_cntr));
3266: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'sd_detail_end_idx:= ' || item_availability_info.sd_detail_end_idx(l_item_cntr));
3267: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'p_atp_record.Atf_Date_Quantity:= ' || p_atp_record.Atf_Date_Quantity); --bug3467631
3268: END IF;
3269: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
3270: l_all_atp_supply_demand,
3271: item_availability_info.period_detail_begin_idx(l_item_cntr),
3272: item_availability_info.period_detail_end_idx(l_item_cntr),
3273: item_availability_info.sd_detail_begin_idx(l_item_cntr),
3277: l_return_status);
3278: --- now remove the demands for other items
3279: FOR i in (l_item_cntr +1)..l_item_count LOOP
3280: IF NOT (l_org_item_detail_flag = 1 and l_satisfied_by_subst_flag = 1 and
3281: item_availability_info.sr_inventory_item_id(i) = MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID) THEN
3282: IF PG_DEBUG in ('Y', 'C') THEN
3283: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'remove demands for item ' || item_availability_info.item_name(i));
3284: END IF;
3285: MSC_ATP_DB_UTILS.Remove_Invalid_SD_Rec(
3334: --- if create supply flag is null then default it to Demanded item
3335: IF PG_DEBUG in ('Y', 'C') THEN
3336: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Backward Ctp');
3337: END IF;
3338: l_create_supply_flag := MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG;
3339: l_count := item_availability_info.inventory_item_id.count;
3340: IF PG_DEBUG in ('Y', 'C') THEN
3341: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'G_CREATE_SUPPLY_FLAG := ' || MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG);
3342: END IF;
3337: END IF;
3338: l_create_supply_flag := MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG;
3339: l_count := item_availability_info.inventory_item_id.count;
3340: IF PG_DEBUG in ('Y', 'C') THEN
3341: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'G_CREATE_SUPPLY_FLAG := ' || MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG);
3342: END IF;
3343: --- bug 2388707: if demand has already been met then just add req item to the list of items
3344: -- to do CTP on
3345: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
3342: END IF;
3343: --- bug 2388707: if demand has already been met then just add req item to the list of items
3344: -- to do CTP on
3345: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
3346: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG in (G_DEMANDED_ITEM, G_ITEM_ATTRIBUTE))
3347: OR (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
3348: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
3349: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3350: END IF;
3343: --- bug 2388707: if demand has already been met then just add req item to the list of items
3344: -- to do CTP on
3345: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
3346: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG in (G_DEMANDED_ITEM, G_ITEM_ATTRIBUTE))
3347: OR (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
3348: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
3349: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3350: END IF;
3351: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3345: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
3346: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG in (G_DEMANDED_ITEM, G_ITEM_ATTRIBUTE))
3347: OR (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
3348: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
3349: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3350: END IF;
3351: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3352:
3353: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3347: OR (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM AND
3348: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
3349: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3350: END IF;
3351: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3352:
3353: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3354:
3355: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
3349: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3350: END IF;
3351: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3352:
3353: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3354:
3355: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
3356: IF item_availability_info.inventory_item_id.count = 1 THEN
3357: --- we will come here if there are no substitutes or we are not substituting
3351: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3352:
3353: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3354:
3355: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
3356: IF item_availability_info.inventory_item_id.count = 1 THEN
3357: --- we will come here if there are no substitutes or we are not substituting
3358: IF PG_DEBUG in ('Y', 'C') THEN
3359: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'One item. highest rev ' || l_highest_rev_item_id);
3360: END IF;
3361: IF (l_highest_rev_item_id = l_inventory_item_id) AND
3362: (item_availability_info.create_supply_flag(l_item_count) = 1) THEN
3363:
3364: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_item_count);
3365: END IF;
3366: ELSE
3367: --- we will come here if we have one or more substitutes and we are doing substitution
3368: IF PG_DEBUG in ('Y', 'C') THEN
3381: END LOOP;
3382: IF PG_DEBUG in ('Y', 'C') THEN
3383: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Counter for High. rev. item := ' || l_highest_rev_item_index);
3384: END IF;
3385: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_highest_rev_item_index);
3386: END IF; -- IF item_availability_info.inventory_item_id.count = 1 THEN
3387: ELSE --- item attribute
3388: --- look at item attributes
3389: IF l_net_demand_after_oh_check > 0 THEN
3390: --- we haven't satisfied the demand as yet
3391: l_item_ctp_info := item_availability_info;
3392: ELSE
3393: --- we have satisfied the demand but we need to look for original item
3394: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_count);
3395: END IF;
3396: END IF; -- IF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = 701
3397:
3398: IF PG_DEBUG in ('Y', 'C') THEN
3392: ELSE
3393: --- we have satisfied the demand but we need to look for original item
3394: MSC_ATP_SUBST.Copy_Item_Info_rec(item_availability_info, l_item_ctp_info, l_count);
3395: END IF;
3396: END IF; -- IF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = 701
3397:
3398: IF PG_DEBUG in ('Y', 'C') THEN
3399: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'List of items to do CTP on');
3400: For i in reverse 1..l_item_ctp_info.inventory_item_id.count LOOP
3399: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'List of items to do CTP on');
3400: For i in reverse 1..l_item_ctp_info.inventory_item_id.count LOOP
3401: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || l_item_ctp_info.item_name(i) || ' ' || l_item_ctp_info.sr_inventory_item_id(i) );
3402: END LOOP;
3403: msc_sch_wb.atp_debug('Subst last PO pegging: ' || MSC_ATP_SUBST.G_TOP_LAST_PO_PEGGING);
3404: END IF;
3405:
3406: -- AT this point we know on what item do we need to do CTP
3407: --l_net_demand :=
3558: -- therefore we set the index to be that of last item.
3559: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
3560: l_item_idx := l_item_count;
3561:
3562: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
3563: IF PG_DEBUG in ('Y', 'C') THEN
3564: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'More item but we retun the index for original item');
3565: END IF;
3566: l_item_idx := l_item_count;
3563: IF PG_DEBUG in ('Y', 'C') THEN
3564: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'More item but we retun the index for original item');
3565: END IF;
3566: l_item_idx := l_item_count;
3567: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
3568: IF PG_DEBUG in ('Y', 'C') THEN
3569: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Index for highest rev item');
3570: END IF;
3571: l_item_idx := l_highest_rev_item_index;
3687: END IF;
3688: p_atp_record.available_quantity := p_atp_record.quantity_ordered;
3689: l_count := Item_availability_info.inventory_item_id.count;
3690: p_atp_record.req_item_req_date_qty := Item_availability_info.request_date_quantity(l_item_count);
3691: IF l_item_ctp_info.sr_inventory_item_id(i) = MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID THEN
3692: --- request has been fullfilled by original item
3693: p_atp_record.req_item_req_date_qty :=
3694: item_availability_info.request_date_quantity(l_item_count);
3695: p_atp_record.req_item_available_date := l_requested_ship_date;
3699:
3700:
3701: --- now add supply demand/period details to out_put table
3702: --- first add onhand/sch recp supply demand details
3703: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
3704: l_all_atp_supply_demand,
3705: l_item_ctp_info.period_detail_begin_idx(i),
3706: l_item_ctp_info.period_detail_end_idx(i),
3707: l_item_ctp_info.sd_detail_begin_idx(i),
3719: --- Now remove the pegging/ supply demand for other items
3720: For j in 1..item_availability_info.inventory_item_id.count LOOP
3721: IF (item_availability_info.inventory_item_id(j) <> l_item_ctp_info.inventory_item_id(i)) AND
3722: NOT(l_satisfied_by_subst_flag = 1 and l_org_item_detail_flag = 1 AND
3723: item_availability_info.sr_inventory_item_id(j) = MSC_ATP_SUBST.G_REQ_ITEM_SR_INV_ID) THEN
3724:
3725: IF PG_DEBUG in ('Y', 'C') THEN
3726: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Remove Pegging for item := ' || item_availability_info.inventory_item_id(i));
3727: END IF;
3933: END IF;
3934: END IF;
3935:
3936: --- first update the demand so that the picture gets adjusted
3937: MSC_ATP_SUBST.UPDATE_DEMAND(item_availability_info.demand_id(i),
3938: item_availability_info.plan_id(i),
3939: item_availability_info.request_date_quantity(i));
3940:
3941: /* time_phased_atp */
4259: IF PG_DEBUG in ('Y', 'C') THEN
4260: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || ' Demand was met by a CTP on sub. reset the l_item_ctp_info table');
4261: END IF;
4262: l_item_ctp_info := l_null_item_avail_info;
4263: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG <> G_HIGHEST_REV_ITEM) or
4264: (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM and
4265: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
4266:
4267: MSC_ATP_SUBST.Copy_Item_Info_Rec(item_availability_info, l_item_ctp_info, l_item_count);
4260: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || ' Demand was met by a CTP on sub. reset the l_item_ctp_info table');
4261: END IF;
4262: l_item_ctp_info := l_null_item_avail_info;
4263: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG <> G_HIGHEST_REV_ITEM) or
4264: (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM and
4265: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
4266:
4267: MSC_ATP_SUBST.Copy_Item_Info_Rec(item_availability_info, l_item_ctp_info, l_item_count);
4268: END IF;
4263: IF (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG <> G_HIGHEST_REV_ITEM) or
4264: (MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM and
4265: l_highest_rev_item_id = item_availability_info.inventory_item_id(l_item_count)) THEN
4266:
4267: MSC_ATP_SUBST.Copy_Item_Info_Rec(item_availability_info, l_item_ctp_info, l_item_count);
4268: END IF;
4269: END IF;
4270: FOR i in reverse 1..l_item_ctp_info.inventory_item_id.count LOOP
4271:
4299: ELSE
4300: IF (l_org_item_detail_flag = 1 AND l_satisfied_by_subst_flag = 1) THEN
4301: l_item_idx := l_item_count;
4302:
4303: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
4304:
4305: l_item_idx := l_item_count;
4306: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
4307:
4302:
4303: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_DEMANDED_ITEM THEN
4304:
4305: l_item_idx := l_item_count;
4306: ELSIF MSC_ATP_SUBST.G_CREATE_SUPPLY_FLAG = G_HIGHEST_REV_ITEM THEN
4307:
4308: l_item_idx := l_highest_rev_item_index;
4309: ELSE
4310:
4633: --- first we add details for backward search
4634: IF PG_DEBUG in ('Y', 'C') THEN
4635: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Add details for pegging for backward search');
4636: END IF;
4637: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
4638: l_all_atp_supply_demand,
4639: item_availability_info.period_detail_begin_idx(l_item_idx),
4640: item_availability_info.period_detail_end_idx(l_item_idx),
4641: item_availability_info.sd_detail_begin_idx(l_item_idx),
4648: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Add details for backward CTP');
4649: END IF;
4650: IF item_availability_info.ctp_prd_detl_begin_idx(l_item_idx) is not null and
4651: item_availability_info.ctp_sd_detl_begin_idx(l_item_idx) is not null THEN
4652: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
4653: l_all_atp_supply_demand,
4654: item_availability_info.ctp_prd_detl_begin_idx(l_item_idx),
4655: item_availability_info.ctp_prd_detl_end_idx(l_item_idx),
4656: item_availability_info.ctp_sd_detl_begin_idx(l_item_idx),
4668: --- ad future period and supply demand details
4669: IF PG_DEBUG in ('Y', 'C') THEN
4670: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'Add s/d details for forward CTP');
4671: END IF;
4672: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
4673: l_all_atp_supply_demand,
4674: item_availability_info.fut_ctp_prd_detl_begin_idx(l_item_idx),
4675: item_availability_info.fut_ctp_prd_detl_end_idx(l_item_idx),
4676: item_availability_info.fut_ctp_sd_detl_begin_idx(l_item_idx),
4753:
4754: l_pegging_rec.summary_flag := MSC_ATP_PVT.G_SUMMARY_FLAG;
4755: l_pegging_rec.request_item_id := p_atp_record.request_item_id;
4756:
4757: MSC_ATP_SUBST.add_pegging(l_pegging_rec);
4758: IF PG_DEBUG in ('Y', 'C') THEN
4759: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_future_supply_pegging_id :=' || l_pegging_id);
4760: END IF;
4761: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
4757: MSC_ATP_SUBST.add_pegging(l_pegging_rec);
4758: IF PG_DEBUG in ('Y', 'C') THEN
4759: msc_sch_wb.atp_debug('ATP_Check_Subst: ' || 'l_future_supply_pegging_id :=' || l_pegging_id);
4760: END IF;
4761: MSC_ATP_SUBST.Details_Output(l_all_atp_period,
4762: l_all_atp_supply_demand,
4763: item_availability_info.fut_atp_prd_detl_begin_idx(l_item_idx),
4764: item_availability_info.fut_atp_prd_detl_end_idx(l_item_idx),
4765: item_availability_info.fut_atp_sd_detl_begin_idx(l_item_idx),
4925: END IF;
4926: END ATP_Check_Subst;
4927:
4928: Procedure Get_Item_Substitutes(p_inventory_item_id IN NUMBER,
4929: p_item_table IN OUT NoCopy MSC_ATP_SUBST.Item_Info_Rec_Typ,
4930: p_instance_id IN NUMBER,
4931: p_plan_id IN NUMBER,
4932: p_customer_id IN NUMBER,
4933: p_customer_site_id IN NUMBER,
4933: p_customer_site_id IN NUMBER,
4934: p_request_date IN DATE,
4935: p_organization_id IN NUMBER)
4936: IS
4937: l_item_table MSC_ATP_SUBST.Item_Info_Rec_Typ;
4938: l_effective_dates MRP_ATP_PUB.date_arr;
4939: l_disable_dates MRP_ATP_PUB.date_arr;
4940: i number;
4941: l_count number;
5701: END IF;
5702: END ADD_PEGGING;
5703:
5704: Procedure Extend_Item_Info_Rec_Typ(
5705: p_item_avail_info IN OUT NOCOPY MSC_ATP_SUBST.Item_Info_Rec_Typ,
5706: x_return_status OUT NoCopy VARCHAR2)
5707: IS
5708: l_count number;
5709: BEGIN
5999: END IF;
6000:
6001: END Details_Output;
6002:
6003: Procedure Copy_Item_Info_Rec(p_parent_item_info IN MSC_ATP_SUBST.Item_Info_Rec_Typ,
6004: p_child_item_info IN OUT NoCopy MSC_ATP_SUBST.Item_Info_Rec_Typ,
6005: p_index IN NUMBER)
6006: IS
6007: l_return_status varchar2(1);
6000:
6001: END Details_Output;
6002:
6003: Procedure Copy_Item_Info_Rec(p_parent_item_info IN MSC_ATP_SUBST.Item_Info_Rec_Typ,
6004: p_child_item_info IN OUT NoCopy MSC_ATP_SUBST.Item_Info_Rec_Typ,
6005: p_index IN NUMBER)
6006: IS
6007: l_return_status varchar2(1);
6008: l_count number;
6009: BEGIN
6010: IF PG_DEBUG in ('Y', 'C') THEN
6011: msc_sch_wb.atp_debug('***** start Copy_Item_Info_Rec ****');
6012: END IF;
6013: MSC_ATP_SUBST.Extend_Item_Info_Rec_Typ(p_child_item_info, l_return_status);
6014: l_count := p_child_item_info.inventory_item_id.count;
6015: p_child_item_info.inventory_item_id(l_count) := p_parent_item_info.inventory_item_id(p_index);
6016: p_child_item_info.sr_inventory_item_id(l_count) := p_parent_item_info.sr_inventory_item_id(p_index);
6017: p_child_item_info.highest_revision_item_id(l_count) := p_parent_item_info.highest_revision_item_id(p_index);
6215: END IF;
6216:
6217: END Add_Mat_Demand;
6218: */
6219: End MSC_ATP_SUBST;