[Home] [Help]
3482: 1, decode(AD.Original_Origination_Type,
3483: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3484: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3485: DECODE(p_demand_class, '-1',
3486: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3487: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3488: 1, p_family_id,
3489: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3490: 'Y', AD.Original_Item_Id,
3493: p_level_id, AD.DEMAND_CLASS),
3494: AD.DEMAND_CLASS)),
3495: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3496: DECODE(p_demand_class, '-1',
3497: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3498: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3499: 1, p_family_id,
3500: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3501: 'Y', AD.Original_Item_Id,
3504: p_level_id, AD.DEMAND_CLASS),
3505: AD.DEMAND_CLASS)),
3506: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3507: DECODE(p_demand_class, '-1',
3508: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3509: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3510: 1, p_family_id,
3511: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3512: 'Y', AD.Original_Item_Id,
3516: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
3517: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3518: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3519: DECODE(p_demand_class, '-1',
3520: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3521: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3522: 1, p_family_id,
3523: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3524: 'Y', AD.Original_Item_Id,
3527: p_level_id, AD.DEMAND_CLASS),
3528: AD.DEMAND_CLASS)),
3529: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3530: DECODE(p_demand_class, '-1',
3531: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3532: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3533: 1, p_family_id,
3534: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3535: 'Y', AD.Original_Item_Id,
3538: p_level_id, AD.DEMAND_CLASS),
3539: AD.DEMAND_CLASS)),
3540: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3541: DECODE(p_demand_class, '-1',
3542: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3543: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3544: 1, p_family_id,
3545: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3546: 'Y', AD.Original_Item_Id,
3549: p_level_id, AD.DEMAND_CLASS),
3550: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
3551: DECODE(AD.DEMAND_CLASS, null, null,
3552: DECODE(p_demand_class, '-1',
3553: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3554: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3555: 1, p_family_id,
3556: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3557: 'Y', AD.Original_Item_Id,
3562: 2, DECODE(AD.CUSTOMER_ID, NULL, TO_CHAR(NULL),
3563: 0, TO_CHAR(NULL),
3564: decode(AD.Original_Origination_Type,
3565: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3566: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3567: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3568: 1, p_family_id,
3569: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3570: 'Y', AD.Original_Item_Id,
3570: 'Y', AD.Original_Item_Id,
3571: p_family_id)),
3572: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3573: p_level_id, NULL),
3574: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3575: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3576: 1, p_family_id,
3577: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3578: 'Y', AD.Original_Item_Id,
3578: 'Y', AD.Original_Item_Id,
3579: p_family_id)),
3580: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3581: p_level_id, NULL),
3582: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3583: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3584: 1, p_family_id,
3585: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3586: 'Y', AD.Original_Item_Id,
3588: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3589: p_level_id, NULL),
3590: TO_CHAR(NULL)),
3591: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3592: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3593: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3594: 1, p_family_id,
3595: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3596: 'Y', AD.Original_Item_Id,
3596: 'Y', AD.Original_Item_Id,
3597: p_family_id)),
3598: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3599: p_level_id, NULL),
3600: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3601: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3602: 1, p_family_id,
3603: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3604: 'Y', AD.Original_Item_Id,
3604: 'Y', AD.Original_Item_Id,
3605: p_family_id)),
3606: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3607: p_level_id, NULL),
3608: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3609: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3610: 1, p_family_id,
3611: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3612: 'Y', AD.Original_Item_Id,
3613: p_family_id)),
3614: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3615: p_level_id, NULL),
3616: TO_CHAR(NULL)),
3617: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3618: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
3619: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3620: 1, p_family_id,
3621: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3624: trunc(AD.Demand_Date),p_level_id, NULL)))),
3625: p_demand_class, 1,
3626: Decode(AD.Demand_Class, NULL, --4365873 If l_demand_class is not null and demand class is populated
3627: -- on supplies record then 0 should be allocated.
3628: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
3629: AD.PARENT_DEMAND_ID,
3630: trunc(AD.Demand_Date),
3631: AD.USING_ASSEMBLY_ITEM_ID,
3632: DECODE(AD.SOURCE_ORGANIZATION_ID,
3644: 1, decode(AD.Original_Origination_Type,
3645: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3646: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3647: DECODE(p_demand_class, '-1',
3648: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3649: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3650: 1, p_family_id,
3651: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3652: 'Y', AD.Original_Item_Id,
3655: p_level_id, AD.DEMAND_CLASS),
3656: AD.DEMAND_CLASS)),
3657: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3658: DECODE(p_demand_class, '-1',
3659: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3660: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3661: 1, p_family_id,
3662: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3663: 'Y', AD.Original_Item_Id,
3666: p_level_id, AD.DEMAND_CLASS),
3667: AD.DEMAND_CLASS)),
3668: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3669: DECODE(p_demand_class, '-1',
3670: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3671: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3672: 1, p_family_id,
3673: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3674: 'Y', AD.Original_Item_Id,
3679: p_demand_class),
3680: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3681: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3682: DECODE(p_demand_class, '-1',
3683: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3684: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3685: 1, p_family_id,
3686: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3687: 'Y', AD.Original_Item_Id,
3690: p_level_id, AD.DEMAND_CLASS),
3691: AD.DEMAND_CLASS)),
3692: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3693: DECODE(p_demand_class, '-1',
3694: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3695: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3696: 1, p_family_id,
3697: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3698: 'Y', AD.Original_Item_Id,
3701: p_level_id, AD.DEMAND_CLASS),
3702: AD.DEMAND_CLASS)),
3703: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3704: DECODE(p_demand_class, '-1',
3705: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3706: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3707: 1, p_family_id,
3708: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3709: 'Y', AD.Original_Item_Id,
3713: AD.DEMAND_CLASS)),
3714: p_demand_class),
3715: DECODE(AD.DEMAND_CLASS, null, null,
3716: DECODE(p_demand_class, '-1',
3717: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3718: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3719: 1, p_family_id,
3720: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3721: 'Y', AD.Original_Item_Id,
3726: 2, DECODE(AD.CUSTOMER_ID, NULL, p_demand_class,
3727: 0, p_demand_class,
3728: decode(AD.Original_Origination_Type,
3729: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3730: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3731: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3732: 1, p_family_id,
3733: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3734: 'Y', AD.Original_Item_Id,
3734: 'Y', AD.Original_Item_Id,
3735: p_family_id)),
3736: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3737: p_level_id, NULL),
3738: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3739: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3740: 1, p_family_id,
3741: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3742: 'Y', AD.Original_Item_Id,
3742: 'Y', AD.Original_Item_Id,
3743: p_family_id)),
3744: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3745: p_level_id, NULL),
3746: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3747: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3748: 1, p_family_id,
3749: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3750: 'Y', AD.Original_Item_Id,
3752: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3753: p_level_id, NULL),
3754: p_demand_class),
3755: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3756: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3757: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3758: 1, p_family_id,
3759: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3760: 'Y', AD.Original_Item_Id,
3760: 'Y', AD.Original_Item_Id,
3761: p_family_id)),
3762: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3763: p_level_id, NULL),
3764: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3765: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3766: 1, p_family_id,
3767: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3768: 'Y', AD.Original_Item_Id,
3768: 'Y', AD.Original_Item_Id,
3769: p_family_id)),
3770: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3771: p_level_id, NULL),
3772: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3773: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3774: 1, p_family_id,
3775: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3776: 'Y', AD.Original_Item_Id,
3777: p_family_id)),
3778: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3779: p_level_id, NULL),
3780: p_demand_class),
3781: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
3782: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3783: 1, p_family_id,
3784: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3785: 'Y', AD.Original_Item_Id,
3802: SA.ALLOCATED_QUANTITY*
3803: DECODE(DECODE(MSC_AATP_PVT.G_HIERARCHY_PROFILE,
3804: 1, DECODE(SA.DEMAND_CLASS, null, null,
3805: DECODE(p_demand_class, '-1',
3806: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3807: null,
3808: null,
3809: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3810: 1, p_family_id,
3818: SA.DEMAND_CLASS),
3819: SA.DEMAND_CLASS)),
3820: 2, DECODE(SA.CUSTOMER_ID, NULL, TO_CHAR(NULL),
3821: 0, TO_CHAR(NULL),
3822: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3823: SA.CUSTOMER_ID,
3824: SA.SHIP_TO_SITE_ID,
3825: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3826: 1, p_family_id,
3833: p_level_id,
3834: NULL))),
3835: p_demand_class, 1,
3836: NULL, nvl(MIHM.allocation_percent/100,1), --4365873
3837: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
3838: p_instance_id,
3839: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3840: 1, p_family_id,
3841: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3848: TRUNC(SA.SUPPLY_DATE)),
3849: 1),*/
3850: DECODE(
3851: MIHM.allocation_percent/100, --4365873
3852: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
3853: p_instance_id,
3854: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3855: 1, p_family_id,
3856: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3951: 1, decode(AD.Original_Origination_Type,
3952: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3953: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3954: DECODE(p_demand_class, '-1',
3955: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3956: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3957: 1, p_family_id,
3958: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3959: 'Y', AD.Original_Item_Id,
3962: p_level_id, AD.DEMAND_CLASS),
3963: AD.DEMAND_CLASS)),
3964: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3965: DECODE(p_demand_class, '-1',
3966: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3967: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3968: 1, p_family_id,
3969: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3970: 'Y', AD.Original_Item_Id,
3973: p_level_id, AD.DEMAND_CLASS),
3974: AD.DEMAND_CLASS)),
3975: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3976: DECODE(p_demand_class, '-1',
3977: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3978: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3979: 1, p_family_id,
3980: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3981: 'Y', AD.Original_Item_Id,
3985: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
3986: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3987: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3988: DECODE(p_demand_class, '-1',
3989: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3990: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3991: 1, p_family_id,
3992: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3993: 'Y', AD.Original_Item_Id,
3996: p_level_id, AD.DEMAND_CLASS),
3997: AD.DEMAND_CLASS)),
3998: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3999: DECODE(p_demand_class, '-1',
4000: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4001: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4002: 1, p_family_id,
4003: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4004: 'Y', AD.Original_Item_Id,
4007: p_level_id, AD.DEMAND_CLASS),
4008: AD.DEMAND_CLASS)),
4009: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4010: DECODE(p_demand_class, '-1',
4011: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4012: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4013: 1, p_family_id,
4014: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4015: 'Y', AD.Original_Item_Id,
4018: p_level_id, AD.DEMAND_CLASS),
4019: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
4020: DECODE(AD.DEMAND_CLASS, null, null,
4021: DECODE(p_demand_class, '-1',
4022: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4023: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4024: 1, p_family_id,
4025: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4026: 'Y', AD.Original_Item_Id,
4031: 2, DECODE(AD.CUSTOMER_ID, NULL, TO_CHAR(NULL),
4032: 0, TO_CHAR(NULL),
4033: decode(AD.Original_Origination_Type,
4034: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4035: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4036: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4037: 1, p_family_id,
4038: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4039: 'Y', AD.Original_Item_Id,
4039: 'Y', AD.Original_Item_Id,
4040: p_family_id)),
4041: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4042: p_level_id, NULL),
4043: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4044: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4045: 1, p_family_id,
4046: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4047: 'Y', AD.Original_Item_Id,
4047: 'Y', AD.Original_Item_Id,
4048: p_family_id)),
4049: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4050: p_level_id, NULL),
4051: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4052: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4053: 1, p_family_id,
4054: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4055: 'Y', AD.Original_Item_Id,
4057: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4058: p_level_id, NULL),
4059: TO_CHAR(NULL)),
4060: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4061: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4062: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4063: 1, p_family_id,
4064: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4065: 'Y', AD.Original_Item_Id,
4065: 'Y', AD.Original_Item_Id,
4066: p_family_id)),
4067: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4068: p_level_id, NULL),
4069: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4070: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4071: 1, p_family_id,
4072: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4073: 'Y', AD.Original_Item_Id,
4073: 'Y', AD.Original_Item_Id,
4074: p_family_id)),
4075: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4076: p_level_id, NULL),
4077: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4078: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4079: 1, p_family_id,
4080: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4081: 'Y', AD.Original_Item_Id,
4082: p_family_id)),
4083: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4084: p_level_id, NULL),
4085: TO_CHAR(NULL)),
4086: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4087: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4088: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4089: 1, p_family_id,
4090: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4092: p_family_id)), p_org_id, p_instance_id,
4093: trunc(AD.Demand_Date),p_level_id, NULL)))),
4094: p_demand_class, 1,
4095: Decode(AD.Demand_Class, NULL, --4365873
4096: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
4097: AD.PARENT_DEMAND_ID,
4098: trunc(AD.Demand_Date),
4099: AD.USING_ASSEMBLY_ITEM_ID,
4100: DECODE(AD.SOURCE_ORGANIZATION_ID,
4112: 1, decode(AD.Original_Origination_Type,
4113: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4114: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4115: DECODE(p_demand_class, '-1',
4116: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4117: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4118: 1, p_family_id,
4119: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4120: 'Y', AD.Original_Item_Id,
4123: p_level_id, AD.DEMAND_CLASS),
4124: AD.DEMAND_CLASS)),
4125: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4126: DECODE(p_demand_class, '-1',
4127: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4128: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4129: 1, p_family_id,
4130: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4131: 'Y', AD.Original_Item_Id,
4134: p_level_id, AD.DEMAND_CLASS),
4135: AD.DEMAND_CLASS)),
4136: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4137: DECODE(p_demand_class, '-1',
4138: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4139: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4140: 1, p_family_id,
4141: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4142: 'Y', AD.Original_Item_Id,
4147: p_demand_class),
4148: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4149: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4150: DECODE(p_demand_class, '-1',
4151: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4152: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4153: 1, p_family_id,
4154: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4155: 'Y', AD.Original_Item_Id,
4158: p_level_id, AD.DEMAND_CLASS),
4159: AD.DEMAND_CLASS)),
4160: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4161: DECODE(p_demand_class, '-1',
4162: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4163: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4164: 1, p_family_id,
4165: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4166: 'Y', AD.Original_Item_Id,
4169: p_level_id, AD.DEMAND_CLASS),
4170: AD.DEMAND_CLASS)),
4171: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4172: DECODE(p_demand_class, '-1',
4173: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4174: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4175: 1, p_family_id,
4176: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4177: 'Y', AD.Original_Item_Id,
4181: AD.DEMAND_CLASS)),
4182: p_demand_class),
4183: DECODE(AD.DEMAND_CLASS, null, null,
4184: DECODE(p_demand_class, '-1',
4185: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4186: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4187: 1, p_family_id,
4188: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4189: 'Y', AD.Original_Item_Id,
4194: 2, DECODE(AD.CUSTOMER_ID, NULL, p_demand_class,
4195: 0, p_demand_class,
4196: decode(AD.Original_Origination_Type,
4197: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4198: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4199: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4200: 1, p_family_id,
4201: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4202: 'Y', AD.Original_Item_Id,
4202: 'Y', AD.Original_Item_Id,
4203: p_family_id)),
4204: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4205: p_level_id, NULL),
4206: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4207: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4208: 1, p_family_id,
4209: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4210: 'Y', AD.Original_Item_Id,
4210: 'Y', AD.Original_Item_Id,
4211: p_family_id)),
4212: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4213: p_level_id, NULL),
4214: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4215: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4216: 1, p_family_id,
4217: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4218: 'Y', AD.Original_Item_Id,
4220: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4221: p_level_id, NULL),
4222: p_demand_class),
4223: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4224: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4225: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4226: 1, p_family_id,
4227: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4228: 'Y', AD.Original_Item_Id,
4228: 'Y', AD.Original_Item_Id,
4229: p_family_id)),
4230: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4231: p_level_id, NULL),
4232: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4233: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4234: 1, p_family_id,
4235: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4236: 'Y', AD.Original_Item_Id,
4236: 'Y', AD.Original_Item_Id,
4237: p_family_id)),
4238: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4239: p_level_id, NULL),
4240: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4241: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4242: 1, p_family_id,
4243: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4244: 'Y', AD.Original_Item_Id,
4245: p_family_id)),
4246: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4247: p_level_id, NULL),
4248: p_demand_class),
4249: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4250: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4251: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4252: 1, p_family_id,
4253: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4272: SA.ALLOCATED_QUANTITY*
4273: DECODE(DECODE(MSC_AATP_PVT.G_HIERARCHY_PROFILE,
4274: 1, DECODE(SA.DEMAND_CLASS, null, null,
4275: DECODE(p_demand_class, '-1',
4276: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4277: null,
4278: null,
4279: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
4280: 1, p_family_id,
4288: sa.DEMAND_CLASS),
4289: sa.DEMAND_CLASS)),
4290: 2, DECODE(sa.CUSTOMER_ID, NULL, TO_CHAR(NULL),
4291: 0, TO_CHAR(NULL),
4292: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4293: sa.CUSTOMER_ID,
4294: sa.SHIP_TO_SITE_ID,
4295: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4296: 1, p_family_id,
4303: p_level_id,
4304: NULL))),
4305: p_demand_class, 1,
4306: NULL, nvl(MIHM.allocation_percent/100,1), --4365873
4307: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
4308: p_instance_id,
4309: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4310: 1, p_family_id,
4311: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4318: TRUNC(sa.SUPPLY_DATE)),
4319: 1),*/
4320: DECODE(
4321: MIHM.allocation_percent/100, --4365873
4322: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
4323: p_instance_id,
4324: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4325: 1, p_family_id,
4326: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4505: 1, decode(AD.Original_Origination_Type,
4506: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4507: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4508: DECODE(p_demand_class, '-1',
4509: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4510: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4511: 1, p_family_id,
4512: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4513: 'Y', AD.Original_Item_Id,
4516: p_level_id, AD.DEMAND_CLASS),
4517: AD.DEMAND_CLASS)),
4518: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4519: DECODE(p_demand_class, '-1',
4520: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4521: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4522: 1, p_family_id,
4523: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4524: 'Y', AD.Original_Item_Id,
4527: p_level_id, AD.DEMAND_CLASS),
4528: AD.DEMAND_CLASS)),
4529: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4530: DECODE(p_demand_class, '-1',
4531: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4532: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4533: 1, p_family_id,
4534: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4535: 'Y', AD.Original_Item_Id,
4539: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
4540: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4541: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4542: DECODE(p_demand_class, '-1',
4543: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4544: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4545: 1, p_family_id,
4546: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4547: 'Y', AD.Original_Item_Id,
4550: p_level_id, AD.DEMAND_CLASS),
4551: AD.DEMAND_CLASS)),
4552: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4553: DECODE(p_demand_class, '-1',
4554: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4555: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4556: 1, p_family_id,
4557: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4558: 'Y', AD.Original_Item_Id,
4561: p_level_id, AD.DEMAND_CLASS),
4562: AD.DEMAND_CLASS)),
4563: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4564: DECODE(p_demand_class, '-1',
4565: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4566: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4567: 1, p_family_id,
4568: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4569: 'Y', AD.Original_Item_Id,
4572: p_level_id, AD.DEMAND_CLASS),
4573: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
4574: DECODE(AD.DEMAND_CLASS, null, null,
4575: DECODE(p_demand_class, '-1',
4576: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4577: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4578: 1, p_family_id,
4579: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4580: 'Y', AD.Original_Item_Id,
4585: 2, DECODE(AD.CUSTOMER_ID, NULL, TO_CHAR(NULL),
4586: 0, TO_CHAR(NULL),
4587: decode(AD.Original_Origination_Type,
4588: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4589: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4590: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4591: 1, p_family_id,
4592: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4593: 'Y', AD.Original_Item_Id,
4593: 'Y', AD.Original_Item_Id,
4594: p_family_id)),
4595: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4596: p_level_id, NULL),
4597: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4598: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4599: 1, p_family_id,
4600: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4601: 'Y', AD.Original_Item_Id,
4601: 'Y', AD.Original_Item_Id,
4602: p_family_id)),
4603: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4604: p_level_id, NULL),
4605: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4606: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4607: 1, p_family_id,
4608: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4609: 'Y', AD.Original_Item_Id,
4611: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4612: p_level_id, NULL),
4613: TO_CHAR(NULL)),
4614: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4615: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4616: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4617: 1, p_family_id,
4618: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4619: 'Y', AD.Original_Item_Id,
4619: 'Y', AD.Original_Item_Id,
4620: p_family_id)),
4621: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4622: p_level_id, NULL),
4623: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4624: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4625: 1, p_family_id,
4626: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4627: 'Y', AD.Original_Item_Id,
4627: 'Y', AD.Original_Item_Id,
4628: p_family_id)),
4629: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4630: p_level_id, NULL),
4631: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4632: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4633: 1, p_family_id,
4634: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4635: 'Y', AD.Original_Item_Id,
4636: p_family_id)),
4637: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4638: p_level_id, NULL),
4639: TO_CHAR(NULL)),
4640: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4641: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4642: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4643: 1, p_family_id,
4644: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4647: trunc(AD.Demand_Date),p_level_id, NULL)))),
4648: p_demand_class, 1,
4649: Decode(AD.Demand_Class, NULL, --4365873 If l_demand_class is not null and demand class is populated
4650: -- on supplies record then 0 should be allocated.
4651: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
4652: AD.PARENT_DEMAND_ID,
4653: trunc(AD.Demand_Date),
4654: AD.USING_ASSEMBLY_ITEM_ID,
4655: DECODE(AD.SOURCE_ORGANIZATION_ID,
4667: 1, decode(AD.Original_Origination_Type,
4668: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4669: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4670: DECODE(p_demand_class, '-1',
4671: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4672: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4673: 1, p_family_id,
4674: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4675: 'Y', AD.Original_Item_Id,
4678: p_level_id, AD.DEMAND_CLASS),
4679: AD.DEMAND_CLASS)),
4680: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4681: DECODE(p_demand_class, '-1',
4682: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4683: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4684: 1, p_family_id,
4685: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4686: 'Y', AD.Original_Item_Id,
4689: p_level_id, AD.DEMAND_CLASS),
4690: AD.DEMAND_CLASS)),
4691: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4692: DECODE(p_demand_class, '-1',
4693: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4694: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4695: 1, p_family_id,
4696: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4697: 'Y', AD.Original_Item_Id,
4702: p_demand_class),
4703: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4704: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4705: DECODE(p_demand_class, '-1',
4706: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4707: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4708: 1, p_family_id,
4709: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4710: 'Y', AD.Original_Item_Id,
4713: p_level_id, AD.DEMAND_CLASS),
4714: AD.DEMAND_CLASS)),
4715: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4716: DECODE(p_demand_class, '-1',
4717: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4718: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4719: 1, p_family_id,
4720: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4721: 'Y', AD.Original_Item_Id,
4724: p_level_id, AD.DEMAND_CLASS),
4725: AD.DEMAND_CLASS)),
4726: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4727: DECODE(p_demand_class, '-1',
4728: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4729: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4730: 1, p_family_id,
4731: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4732: 'Y', AD.Original_Item_Id,
4736: AD.DEMAND_CLASS)),
4737: p_demand_class),
4738: DECODE(AD.DEMAND_CLASS, null, null,
4739: DECODE(p_demand_class, '-1',
4740: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4741: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4742: 1, p_family_id,
4743: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4744: 'Y', AD.Original_Item_Id,
4749: 2, DECODE(AD.CUSTOMER_ID, NULL, p_demand_class,
4750: 0, p_demand_class,
4751: decode(AD.Original_Origination_Type,
4752: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4753: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4754: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4755: 1, p_family_id,
4756: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4757: 'Y', AD.Original_Item_Id,
4757: 'Y', AD.Original_Item_Id,
4758: p_family_id)),
4759: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4760: p_level_id, NULL),
4761: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4762: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4763: 1, p_family_id,
4764: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4765: 'Y', AD.Original_Item_Id,
4765: 'Y', AD.Original_Item_Id,
4766: p_family_id)),
4767: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4768: p_level_id, NULL),
4769: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4770: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4771: 1, p_family_id,
4772: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4773: 'Y', AD.Original_Item_Id,
4775: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4776: p_level_id, NULL),
4777: p_demand_class),
4778: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4779: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4780: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4781: 1, p_family_id,
4782: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4783: 'Y', AD.Original_Item_Id,
4783: 'Y', AD.Original_Item_Id,
4784: p_family_id)),
4785: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4786: p_level_id, NULL),
4787: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4788: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4789: 1, p_family_id,
4790: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4791: 'Y', AD.Original_Item_Id,
4791: 'Y', AD.Original_Item_Id,
4792: p_family_id)),
4793: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4794: p_level_id, NULL),
4795: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4796: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4797: 1, p_family_id,
4798: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4799: 'Y', AD.Original_Item_Id,
4800: p_family_id)),
4801: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4802: p_level_id, NULL),
4803: p_demand_class),
4804: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4805: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4806: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4807: 1, p_family_id,
4808: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4830: 1, decode(AD.Original_Origination_Type,
4831: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4832: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4833: DECODE(p_demand_class, '-1',
4834: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4835: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4836: 1, p_family_id,
4837: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4838: 'Y', AD.Original_Item_Id,
4841: p_level_id, AD.DEMAND_CLASS),
4842: AD.DEMAND_CLASS)),
4843: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4844: DECODE(p_demand_class, '-1',
4845: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4846: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4847: 1, p_family_id,
4848: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4849: 'Y', AD.Original_Item_Id,
4852: p_level_id, AD.DEMAND_CLASS),
4853: AD.DEMAND_CLASS)),
4854: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4855: DECODE(p_demand_class, '-1',
4856: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4857: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4858: 1, p_family_id,
4859: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4860: 'Y', AD.Original_Item_Id,
4864: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
4865: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4866: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4867: DECODE(p_demand_class, '-1',
4868: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4869: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4870: 1, p_family_id,
4871: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4872: 'Y', AD.Original_Item_Id,
4875: p_level_id, AD.DEMAND_CLASS),
4876: AD.DEMAND_CLASS)),
4877: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4878: DECODE(p_demand_class, '-1',
4879: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4880: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4881: 1, p_family_id,
4882: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4883: 'Y', AD.Original_Item_Id,
4886: p_level_id, AD.DEMAND_CLASS),
4887: AD.DEMAND_CLASS)),
4888: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4889: DECODE(p_demand_class, '-1',
4890: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4891: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4892: 1, p_family_id,
4893: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4894: 'Y', AD.Original_Item_Id,
4897: p_level_id, AD.DEMAND_CLASS),
4898: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
4899: DECODE(AD.DEMAND_CLASS, null, null,
4900: DECODE(p_demand_class, '-1',
4901: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4902: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4903: 1, p_family_id,
4904: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4905: 'Y', AD.Original_Item_Id,
4910: 2, DECODE(AD.CUSTOMER_ID, NULL, TO_CHAR(NULL),
4911: 0, TO_CHAR(NULL),
4912: decode(AD.Original_Origination_Type,
4913: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4914: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4915: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4916: 1, p_family_id,
4917: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4918: 'Y', AD.Original_Item_Id,
4918: 'Y', AD.Original_Item_Id,
4919: p_family_id)),
4920: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4921: p_level_id, NULL),
4922: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4923: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4924: 1, p_family_id,
4925: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4926: 'Y', AD.Original_Item_Id,
4926: 'Y', AD.Original_Item_Id,
4927: p_family_id)),
4928: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4929: p_level_id, NULL),
4930: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4931: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4932: 1, p_family_id,
4933: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4934: 'Y', AD.Original_Item_Id,
4936: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4937: p_level_id, NULL),
4938: TO_CHAR(NULL)),
4939: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4940: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4941: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4942: 1, p_family_id,
4943: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4944: 'Y', AD.Original_Item_Id,
4944: 'Y', AD.Original_Item_Id,
4945: p_family_id)),
4946: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4947: p_level_id, NULL),
4948: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4949: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4950: 1, p_family_id,
4951: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4952: 'Y', AD.Original_Item_Id,
4952: 'Y', AD.Original_Item_Id,
4953: p_family_id)),
4954: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4955: p_level_id, NULL),
4956: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4957: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4958: 1, p_family_id,
4959: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4960: 'Y', AD.Original_Item_Id,
4961: p_family_id)),
4962: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4963: p_level_id, NULL),
4964: TO_CHAR(NULL)),
4965: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4966: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4967: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4968: 1, p_family_id,
4969: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4970: 'Y', AD.Original_Item_Id,
4971: p_family_id)), p_org_id, p_instance_id,
4972: trunc(AD.Demand_Date),p_level_id, NULL)))),
4973: p_demand_class, 1,
4974: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
4975: AD.PARENT_DEMAND_ID,
4976: trunc(AD.Demand_Date),
4977: AD.USING_ASSEMBLY_ITEM_ID,
4978: DECODE(AD.SOURCE_ORGANIZATION_ID,
4990: 1, decode(AD.Original_Origination_Type,
4991: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4992: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4993: DECODE(p_demand_class, '-1',
4994: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4995: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4996: 1, p_family_id,
4997: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4998: 'Y', AD.Original_Item_Id,
5001: p_level_id, AD.DEMAND_CLASS),
5002: AD.DEMAND_CLASS)),
5003: -23453, DECODE(AD.DEMAND_CLASS, null, null,
5004: DECODE(p_demand_class, '-1',
5005: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5006: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5007: 1, p_family_id,
5008: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5009: 'Y', AD.Original_Item_Id,
5012: p_level_id, AD.DEMAND_CLASS),
5013: AD.DEMAND_CLASS)),
5014: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
5015: DECODE(p_demand_class, '-1',
5016: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5017: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5018: 1, p_family_id,
5019: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5020: 'Y', AD.Original_Item_Id,
5025: p_demand_class),
5026: 30, decode(AD.SOURCE_ORGANIZATION_ID,
5027: NULL, DECODE(AD.DEMAND_CLASS, null, null,
5028: DECODE(p_demand_class, '-1',
5029: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5030: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5031: 1, p_family_id,
5032: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5033: 'Y', AD.Original_Item_Id,
5036: p_level_id, AD.DEMAND_CLASS),
5037: AD.DEMAND_CLASS)),
5038: -23453, DECODE(AD.DEMAND_CLASS, null, null,
5039: DECODE(p_demand_class, '-1',
5040: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5041: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5042: 1, p_family_id,
5043: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5044: 'Y', AD.Original_Item_Id,
5047: p_level_id, AD.DEMAND_CLASS),
5048: AD.DEMAND_CLASS)),
5049: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
5050: DECODE(p_demand_class, '-1',
5051: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5052: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5053: 1, p_family_id,
5054: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5055: 'Y', AD.Original_Item_Id,
5059: AD.DEMAND_CLASS)),
5060: p_demand_class),
5061: DECODE(AD.DEMAND_CLASS, null, null,
5062: DECODE(p_demand_class, '-1',
5063: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5064: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5065: 1, p_family_id,
5066: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5067: 'Y', AD.Original_Item_Id,
5072: 2, DECODE(AD.CUSTOMER_ID, NULL, p_demand_class,
5073: 0, p_demand_class,
5074: decode(AD.Original_Origination_Type,
5075: 6, decode(AD.SOURCE_ORGANIZATION_ID,
5076: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5077: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5078: 1, p_family_id,
5079: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5080: 'Y', AD.Original_Item_Id,
5080: 'Y', AD.Original_Item_Id,
5081: p_family_id)),
5082: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5083: p_level_id, NULL),
5084: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5085: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5086: 1, p_family_id,
5087: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5088: 'Y', AD.Original_Item_Id,
5088: 'Y', AD.Original_Item_Id,
5089: p_family_id)),
5090: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5091: p_level_id, NULL),
5092: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5093: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5094: 1, p_family_id,
5095: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5096: 'Y', AD.Original_Item_Id,
5098: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5099: p_level_id, NULL),
5100: p_demand_class),
5101: 30, decode(AD.SOURCE_ORGANIZATION_ID,
5102: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5103: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5104: 1, p_family_id,
5105: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5106: 'Y', AD.Original_Item_Id,
5106: 'Y', AD.Original_Item_Id,
5107: p_family_id)),
5108: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5109: p_level_id, NULL),
5110: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5111: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5112: 1, p_family_id,
5113: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5114: 'Y', AD.Original_Item_Id,
5114: 'Y', AD.Original_Item_Id,
5115: p_family_id)),
5116: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5117: p_level_id, NULL),
5118: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5119: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5120: 1, p_family_id,
5121: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5122: 'Y', AD.Original_Item_Id,
5123: p_family_id)),
5124: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5125: p_level_id, NULL),
5126: p_demand_class),
5127: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5128: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
5129: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5130: 1, p_family_id,
5131: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5195: CSA.ALLOCATED_QUANTITY*
5196: DECODE(DECODE(MSC_AATP_PVT.G_HIERARCHY_PROFILE,
5197: 1, DECODE(CSA.DEMAND_CLASS, null, null,
5198: DECODE(p_demand_class, '-1',
5199: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5200: null,
5201: null,
5202: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5203: 1, p_family_id,
5211: CSA.DEMAND_CLASS),
5212: CSA.DEMAND_CLASS)),
5213: 2, DECODE(CSA.CUSTOMER_ID, NULL, TO_CHAR(NULL),
5214: 0, TO_CHAR(NULL),
5215: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5216: CSA.CUSTOMER_ID,
5217: CSA.SHIP_TO_SITE_ID,
5218: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5219: 1, p_family_id,
5226: p_level_id,
5227: NULL))),
5228: p_demand_class, 1,
5229: NULL, nvl(MIHM.allocation_percent/100,1), --4365873
5230: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
5231: p_instance_id,
5232: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5233: 1, p_family_id,
5234: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5241: TRUNC(CSA.SUPPLY_DATE)),
5242: 1),*/
5243: DECODE(
5244: MIHM.allocation_percent/100, --4365873
5245: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
5246: p_instance_id,
5247: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5248: 1, p_family_id,
5249: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6211: DECODE(decode(AD.ORIGINAL_ORIGINATION_TYPE,
6212: 6, decode(AD.source_organization_id,
6213: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6214: DECODE(TEMP.DEMAND_CLASS, '-1',
6215: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6216: null, null,
6217: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6218: 1, p_family_id,
6219: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6224: p_level_id, AD.DEMAND_CLASS),
6225: AD.DEMAND_CLASS)),
6226: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6227: DECODE(TEMP.DEMAND_CLASS, '-1',
6228: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6229: null, null,
6230: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6231: 1, p_family_id,
6232: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6237: p_level_id, AD.DEMAND_CLASS),
6238: AD.DEMAND_CLASS)),
6239: AD.organization_id, DECODE(AD.DEMAND_CLASS, null, null,
6240: DECODE(TEMP.DEMAND_CLASS, '-1',
6241: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6242: null, null,
6243: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6244: 1, p_family_id,
6245: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6251: AD.DEMAND_CLASS)), NULL),
6252: 30, decode(AD.source_organization_id,
6253: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6254: DECODE(TEMP.DEMAND_CLASS, '-1',
6255: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6256: null, null,
6257: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6258: 1, p_family_id,
6259: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6264: p_level_id, AD.DEMAND_CLASS),
6265: AD.DEMAND_CLASS)),
6266: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6267: DECODE(TEMP.DEMAND_CLASS, '-1',
6268: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6269: null, null,
6270: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6271: 1, p_family_id,
6272: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6277: p_level_id, AD.DEMAND_CLASS),
6278: AD.DEMAND_CLASS)),
6279: AD.organization_id, DECODE(AD.DEMAND_CLASS, null, null,
6280: DECODE(TEMP.DEMAND_CLASS, '-1',
6281: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6282: null, null,
6283: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6284: 1, p_family_id,
6285: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6290: p_level_id, AD.DEMAND_CLASS),
6291: AD.DEMAND_CLASS)), NULL),
6292: DECODE(AD.DEMAND_CLASS, null, null,
6293: DECODE(TEMP.DEMAND_CLASS, '-1',
6294: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6295: null, null,
6296: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6297: 1, p_family_id,
6298: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6302: p_instance_id, trunc(AD.DEMAND_DATE),
6303: p_level_id, AD.DEMAND_CLASS),
6304: AD.DEMAND_CLASS))),
6305: TEMP.DEMAND_CLASS, 1,
6306: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
6307: AD.PARENT_DEMAND_ID,
6308: trunc(AD.DEMAND_DATE),
6309: AD.USING_ASSEMBLY_ITEM_ID,
6310: DECODE(AD.SOURCE_ORGANIZATION_ID,
6321: decode(AD.ORIGINAL_ORIGINATION_TYPE,
6322: 6, decode(AD.source_organization_id,
6323: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6324: DECODE(TEMP.DEMAND_CLASS, '-1',
6325: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6326: null, null,
6327: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6328: 1, p_family_id,
6329: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6334: p_level_id, AD.DEMAND_CLASS),
6335: AD.DEMAND_CLASS)),
6336: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6337: DECODE(TEMP.DEMAND_CLASS, '-1',
6338: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6339: null, null,
6340: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6341: 1, p_family_id,
6342: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6347: p_level_id, AD.DEMAND_CLASS),
6348: AD.DEMAND_CLASS)),
6349: AD.organization_id, DECODE(AD.DEMAND_CLASS, null, null,
6350: DECODE(TEMP.DEMAND_CLASS, '-1',
6351: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6352: null, null,
6353: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6354: 1, p_family_id,
6355: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6361: AD.DEMAND_CLASS)), TEMP.DEMAND_CLASS),
6362: 30, decode(AD.source_organization_id,
6363: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6364: DECODE(TEMP.DEMAND_CLASS, '-1',
6365: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6366: null, null,
6367: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6368: 1, p_family_id,
6369: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6374: p_level_id, AD.DEMAND_CLASS),
6375: AD.DEMAND_CLASS)),
6376: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6377: DECODE(TEMP.DEMAND_CLASS, '-1',
6378: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6379: null, null,
6380: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6381: 1, p_family_id,
6382: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6387: p_level_id, AD.DEMAND_CLASS),
6388: AD.DEMAND_CLASS)),
6389: AD.organization_id, DECODE(AD.DEMAND_CLASS, null, null,
6390: DECODE(TEMP.DEMAND_CLASS, '-1',
6391: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6392: null, null,
6393: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6394: 1, p_family_id,
6395: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6400: p_level_id, AD.DEMAND_CLASS),
6401: AD.DEMAND_CLASS)), TEMP.DEMAND_CLASS),
6402: DECODE(AD.DEMAND_CLASS, null, null,
6403: DECODE(TEMP.DEMAND_CLASS, '-1',
6404: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6405: null, null,
6406: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6407: 1, p_family_id,
6408: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6439: DECODE(decode(AD.ORIGINAL_ORIGINATION_TYPE,
6440: 6, decode(AD.source_organization_id,
6441: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6442: DECODE(TEMP.DEMAND_CLASS, '-1',
6443: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6444: null, null,
6445: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6446: 1, p_family_id,
6447: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6452: p_level_id, AD.DEMAND_CLASS),
6453: AD.DEMAND_CLASS)),
6454: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6455: DECODE(TEMP.DEMAND_CLASS, '-1',
6456: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6457: null, null,
6458: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6459: 1, p_family_id,
6460: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6465: p_level_id, AD.DEMAND_CLASS),
6466: AD.DEMAND_CLASS)),
6467: AD.organization_id, DECODE(AD.DEMAND_CLASS, null, null,
6468: DECODE(TEMP.DEMAND_CLASS, '-1',
6469: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6470: null, null,
6471: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6472: 1, p_family_id,
6473: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6479: AD.DEMAND_CLASS)), NULL),
6480: 30, decode(AD.source_organization_id,
6481: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6482: DECODE(TEMP.DEMAND_CLASS, '-1',
6483: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6484: null, null,
6485: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6486: 1, p_family_id,
6487: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6492: p_level_id, AD.DEMAND_CLASS),
6493: AD.DEMAND_CLASS)),
6494: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6495: DECODE(TEMP.DEMAND_CLASS, '-1',
6496: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6497: null, null,
6498: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6499: 1, p_family_id,
6500: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6505: p_level_id, AD.DEMAND_CLASS),
6506: AD.DEMAND_CLASS)),
6507: AD.organization_id, DECODE(AD.DEMAND_CLASS, null, null,
6508: DECODE(TEMP.DEMAND_CLASS, '-1',
6509: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6510: null, null,
6511: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6512: 1, p_family_id,
6513: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6518: p_level_id, AD.DEMAND_CLASS),
6519: AD.DEMAND_CLASS)), NULL),
6520: DECODE(AD.DEMAND_CLASS, null, null,
6521: DECODE(TEMP.DEMAND_CLASS, '-1',
6522: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6523: null, null,
6524: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6525: 1, p_family_id,
6526: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6530: p_instance_id, trunc(AD.DEMAND_DATE),
6531: p_level_id, AD.DEMAND_CLASS),
6532: AD.DEMAND_CLASS))),
6533: TEMP.DEMAND_CLASS, 1,
6534: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
6535: AD.PARENT_DEMAND_ID,
6536: trunc(AD.DEMAND_DATE),
6537: AD.USING_ASSEMBLY_ITEM_ID,
6538: DECODE(AD.SOURCE_ORGANIZATION_ID,
6549: decode(AD.ORIGINAL_ORIGINATION_TYPE,
6550: 6, decode(AD.source_organization_id,
6551: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6552: DECODE(TEMP.DEMAND_CLASS, '-1',
6553: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6554: null, null,
6555: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6556: 1, p_family_id,
6557: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6562: p_level_id, AD.DEMAND_CLASS),
6563: AD.DEMAND_CLASS)),
6564: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6565: DECODE(TEMP.DEMAND_CLASS, '-1',
6566: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6567: null, null,
6568: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6569: 1, p_family_id,
6570: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6575: p_level_id, AD.DEMAND_CLASS),
6576: AD.DEMAND_CLASS)),
6577: AD.organization_id, DECODE(AD.DEMAND_CLASS, null, null,
6578: DECODE(TEMP.DEMAND_CLASS, '-1',
6579: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6580: null, null,
6581: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6582: 1, p_family_id,
6583: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6589: AD.DEMAND_CLASS)), TEMP.DEMAND_CLASS),
6590: 30, decode(AD.source_organization_id,
6591: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6592: DECODE(TEMP.DEMAND_CLASS, '-1',
6593: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6594: null, null,
6595: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6596: 1, p_family_id,
6597: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6602: p_level_id, AD.DEMAND_CLASS),
6603: AD.DEMAND_CLASS)),
6604: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6605: DECODE(TEMP.DEMAND_CLASS, '-1',
6606: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6607: null, null,
6608: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6609: 1, p_family_id,
6610: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6615: p_level_id, AD.DEMAND_CLASS),
6616: AD.DEMAND_CLASS)),
6617: AD.organization_id, DECODE(AD.DEMAND_CLASS, null, null,
6618: DECODE(TEMP.DEMAND_CLASS, '-1',
6619: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6620: null, null,
6621: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6622: 1, p_family_id,
6623: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6628: p_level_id, AD.DEMAND_CLASS),
6629: AD.DEMAND_CLASS)), TEMP.DEMAND_CLASS),
6630: DECODE(AD.DEMAND_CLASS, null, null,
6631: DECODE(TEMP.DEMAND_CLASS, '-1',
6632: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6633: null, null,
6634: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6635: 1, p_family_id,
6636: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6688: SA.ORDER_TYPE col10,
6689: SA.ALLOCATED_QUANTITY
6690: * DECODE(DECODE(SA.DEMAND_CLASS, null, null,
6691: DECODE(TEMP.DEMAND_CLASS,'-1',
6692: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6693: null,
6694: null,
6695: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6696: 1, p_family_id,
6705: SA.DEMAND_CLASS)),
6706: TEMP.DEMAND_CLASS,
6707: 1,
6708: NULL,
6709: NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
6710: p_instance_id,
6711: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6712: 1, p_family_id,
6713: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6718: null,
6719: TEMP.DEMAND_CLASS,
6720: TRUNC(SA.SUPPLY_DATE)),
6721: 1),
6722: DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
6723: p_instance_id,
6724: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6725: 1, p_family_id,
6726: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6810: DECODE(DECODE(AD.CUSTOMER_ID, NULL, NULL,
6811: 0, NULL,
6812: decode(AD.origination_type,
6813: 6, decode(AD.source_organization_id,
6814: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6815: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6816: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6817: 1, p_family_id,
6818: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6819: 'Y', AD.Original_Item_Id,
6820: p_family_id)),
6821: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6822: p_level_id, NULL),
6823: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6824: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6825: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6826: 1, p_family_id,
6827: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6828: 'Y', AD.Original_Item_Id,
6829: p_family_id)),
6830: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6831: p_level_id, NULL),
6832: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6833: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6834: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6835: 1, p_family_id,
6836: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6839: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6840: p_level_id, NULL),
6841: NULL),
6842: 30, decode(AD.source_organization_id,
6843: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6844: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6845: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6846: 1, p_family_id,
6847: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6848: 'Y', AD.Original_Item_Id,
6849: p_family_id)),
6850: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6851: p_level_id, NULL),
6852: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6853: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6854: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6855: 1, p_family_id,
6856: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6857: 'Y', AD.Original_Item_Id,
6858: p_family_id)),
6859: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6860: p_level_id, NULL),
6861: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6862: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6863: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6864: 1, p_family_id,
6865: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6867: p_family_id)),
6868: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6869: p_level_id, NULL),
6870: NULL),
6871: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6872: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6873: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6874: 1, p_family_id,
6875: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6878: p_org_id, p_instance_id,
6879: TRUNC(AD.ORIGINAL_DEMAND_DATE),
6880: p_level_id, NULL))),
6881: TEMP.LEVEL_3_DEMAND_CLASS, 1,
6882: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
6883: AD.PARENT_DEMAND_ID,
6884: TRUNC(AD.ORIGINAL_DEMAND_DATE),
6885: AD.USING_ASSEMBLY_ITEM_ID,
6886: DECODE(AD.SOURCE_ORGANIZATION_ID,
6897: DECODE(AD.CUSTOMER_ID, NULL, TEMP.LEVEL_3_DEMAND_CLASS,
6898: 0, TEMP.LEVEL_3_DEMAND_CLASS,
6899: decode(AD.origination_type,
6900: 6, decode(AD.source_organization_id,
6901: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6902: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6903: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6904: 1, p_family_id,
6905: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6906: 'Y', AD.Original_Item_Id,
6907: p_family_id)),
6908: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6909: p_level_id, NULL),
6910: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6911: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6912: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6913: 1, p_family_id,
6914: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6915: 'Y', AD.Original_Item_Id,
6916: p_family_id)),
6917: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6918: p_level_id, NULL),
6919: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6920: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6921: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6922: 1, p_family_id,
6923: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6926: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6927: p_level_id, NULL),
6928: TEMP.LEVEL_3_DEMAND_CLASS),
6929: 30, decode(AD.source_organization_id,
6930: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6931: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6932: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6933: 1, p_family_id,
6934: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6935: 'Y', AD.Original_Item_Id,
6936: p_family_id)),
6937: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6938: p_level_id, NULL),
6939: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6940: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6941: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6942: 1, p_family_id,
6943: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6944: 'Y', AD.Original_Item_Id,
6945: p_family_id)),
6946: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6947: p_level_id, NULL),
6948: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6949: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6950: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6951: 1, p_family_id,
6952: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6954: p_family_id)),
6955: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6956: p_level_id, NULL),
6957: TEMP.LEVEL_3_DEMAND_CLASS),
6958: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6959: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6960: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6961: 1, p_family_id,
6962: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6996: DECODE(DECODE(AD.CUSTOMER_ID, NULL, NULL,
6997: 0, NULL,
6998: decode(AD.origination_type,
6999: 6, decode(AD.source_organization_id,
7000: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7001: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7002: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7003: 1, p_family_id,
7004: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7005: 'Y', AD.Original_Item_Id,
7006: p_family_id)),
7007: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7008: p_level_id, NULL),
7009: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7010: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7011: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7012: 1, p_family_id,
7013: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7014: 'Y', AD.Original_Item_Id,
7015: p_family_id)),
7016: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7017: p_level_id, NULL),
7018: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7019: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7020: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7021: 1, p_family_id,
7022: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7025: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7026: p_level_id, NULL),
7027: NULL),
7028: 30, decode(AD.source_organization_id,
7029: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7030: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7031: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7032: 1, p_family_id,
7033: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7034: 'Y', AD.Original_Item_Id,
7035: p_family_id)),
7036: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7037: p_level_id, NULL),
7038: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7039: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7040: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7041: 1, p_family_id,
7042: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7043: 'Y', AD.Original_Item_Id,
7044: p_family_id)),
7045: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7046: p_level_id, NULL),
7047: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7048: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7049: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7050: 1, p_family_id,
7051: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7053: p_family_id)),
7054: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7055: p_level_id, NULL),
7056: NULL),
7057: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7058: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7059: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7060: 1, p_family_id,
7061: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7064: p_org_id, p_instance_id,
7065: TRUNC(AD.ORIGINAL_DEMAND_DATE),
7066: p_level_id, NULL))),
7067: TEMP.LEVEL_3_DEMAND_CLASS, 1,
7068: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
7069: AD.PARENT_DEMAND_ID,
7070: TRUNC(AD.ORIGINAL_DEMAND_DATE),
7071: AD.USING_ASSEMBLY_ITEM_ID,
7072: DECODE(AD.SOURCE_ORGANIZATION_ID,
7083: DECODE(AD.CUSTOMER_ID, NULL, TEMP.LEVEL_3_DEMAND_CLASS,
7084: 0, TEMP.LEVEL_3_DEMAND_CLASS,
7085: decode(AD.origination_type,
7086: 6, decode(AD.source_organization_id,
7087: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7088: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7089: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7090: 1, p_family_id,
7091: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7092: 'Y', AD.Original_Item_Id,
7093: p_family_id)),
7094: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7095: p_level_id, NULL),
7096: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7097: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7098: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7099: 1, p_family_id,
7100: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7101: 'Y', AD.Original_Item_Id,
7102: p_family_id)),
7103: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7104: p_level_id, NULL),
7105: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7106: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7107: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7108: 1, p_family_id,
7109: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7112: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7113: p_level_id, NULL),
7114: TEMP.LEVEL_3_DEMAND_CLASS),
7115: 30, decode(AD.source_organization_id,
7116: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7117: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7118: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7119: 1, p_family_id,
7120: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7121: 'Y', AD.Original_Item_Id,
7122: p_family_id)),
7123: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7124: p_level_id, NULL),
7125: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7126: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7127: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7128: 1, p_family_id,
7129: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7130: 'Y', AD.Original_Item_Id,
7131: p_family_id)),
7132: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7133: p_level_id, NULL),
7134: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7135: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7136: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7137: 1, p_family_id,
7138: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7140: p_family_id)),
7141: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7142: p_level_id, NULL),
7143: TEMP.LEVEL_3_DEMAND_CLASS),
7144: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7145: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7146: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7147: 1, p_family_id,
7148: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7196: SA.ALLOCATED_QUANTITY) col9, -- supply_demand_source_quantity
7197: SA.ORDER_TYPE col10, -- supply_demand_source_type
7198: SA.ALLOCATED_QUANTITY
7199: * DECODE(DECODE(SA.CUSTOMER_ID, NULL, NULL,
7200: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7201: SA.CUSTOMER_ID,
7202: SA.SHIP_TO_SITE_ID,
7203: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7204: 1, p_family_id,
7212: NULL)),
7213: TEMP.LEVEL_3_DEMAND_CLASS,
7214: 1,
7215: NULL,
7216: NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
7217: p_instance_id,
7218: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7219: 1, p_family_id,
7220: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7224: null,
7225: null,
7226: TEMP.LEVEL_3_DEMAND_CLASS,
7227: TRUNC(SA.SUPPLY_DATE)), 1),
7228: DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
7229: p_instance_id,
7230: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7231: 1, p_family_id,
7232: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,