[Home] [Help]
3465: 1, decode(AD.Original_Origination_Type,
3466: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3467: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3468: DECODE(p_demand_class, '-1',
3469: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3470: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3471: 1, p_family_id,
3472: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3473: 'Y', AD.Original_Item_Id,
3476: p_level_id, AD.DEMAND_CLASS),
3477: AD.DEMAND_CLASS)),
3478: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3479: DECODE(p_demand_class, '-1',
3480: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3481: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3482: 1, p_family_id,
3483: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3484: 'Y', AD.Original_Item_Id,
3487: p_level_id, AD.DEMAND_CLASS),
3488: AD.DEMAND_CLASS)),
3489: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3490: DECODE(p_demand_class, '-1',
3491: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3492: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3493: 1, p_family_id,
3494: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3495: 'Y', AD.Original_Item_Id,
3499: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
3500: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3501: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3502: DECODE(p_demand_class, '-1',
3503: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3504: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3505: 1, p_family_id,
3506: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3507: 'Y', AD.Original_Item_Id,
3510: p_level_id, AD.DEMAND_CLASS),
3511: AD.DEMAND_CLASS)),
3512: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3513: DECODE(p_demand_class, '-1',
3514: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3515: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3516: 1, p_family_id,
3517: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3518: 'Y', AD.Original_Item_Id,
3521: p_level_id, AD.DEMAND_CLASS),
3522: AD.DEMAND_CLASS)),
3523: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3524: DECODE(p_demand_class, '-1',
3525: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3526: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3527: 1, p_family_id,
3528: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3529: 'Y', AD.Original_Item_Id,
3532: p_level_id, AD.DEMAND_CLASS),
3533: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
3534: DECODE(AD.DEMAND_CLASS, null, null,
3535: DECODE(p_demand_class, '-1',
3536: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3537: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3538: 1, p_family_id,
3539: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3540: 'Y', AD.Original_Item_Id,
3545: 2, DECODE(AD.CUSTOMER_ID, NULL, TO_CHAR(NULL),
3546: 0, TO_CHAR(NULL),
3547: decode(AD.Original_Origination_Type,
3548: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3549: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3550: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3551: 1, p_family_id,
3552: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3553: 'Y', AD.Original_Item_Id,
3553: 'Y', AD.Original_Item_Id,
3554: p_family_id)),
3555: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3556: p_level_id, NULL),
3557: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3558: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3559: 1, p_family_id,
3560: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3561: 'Y', AD.Original_Item_Id,
3561: 'Y', AD.Original_Item_Id,
3562: p_family_id)),
3563: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3564: p_level_id, NULL),
3565: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3566: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3567: 1, p_family_id,
3568: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3569: 'Y', AD.Original_Item_Id,
3571: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3572: p_level_id, NULL),
3573: TO_CHAR(NULL)),
3574: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3575: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3576: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3577: 1, p_family_id,
3578: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3579: 'Y', AD.Original_Item_Id,
3579: 'Y', AD.Original_Item_Id,
3580: p_family_id)),
3581: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3582: p_level_id, NULL),
3583: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3584: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3585: 1, p_family_id,
3586: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3587: 'Y', AD.Original_Item_Id,
3587: 'Y', AD.Original_Item_Id,
3588: p_family_id)),
3589: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3590: p_level_id, NULL),
3591: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3592: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3593: 1, p_family_id,
3594: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3595: 'Y', AD.Original_Item_Id,
3596: p_family_id)),
3597: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3598: p_level_id, NULL),
3599: TO_CHAR(NULL)),
3600: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3601: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
3602: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3603: 1, p_family_id,
3604: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3607: trunc(AD.Demand_Date),p_level_id, NULL)))),
3608: p_demand_class, 1,
3609: Decode(AD.Demand_Class, NULL, --4365873 If l_demand_class is not null and demand class is populated
3610: -- on supplies record then 0 should be allocated.
3611: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
3612: AD.PARENT_DEMAND_ID,
3613: trunc(AD.Demand_Date),
3614: AD.USING_ASSEMBLY_ITEM_ID,
3615: DECODE(AD.SOURCE_ORGANIZATION_ID,
3627: 1, decode(AD.Original_Origination_Type,
3628: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3629: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3630: DECODE(p_demand_class, '-1',
3631: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3632: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3633: 1, p_family_id,
3634: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3635: 'Y', AD.Original_Item_Id,
3638: p_level_id, AD.DEMAND_CLASS),
3639: AD.DEMAND_CLASS)),
3640: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3641: DECODE(p_demand_class, '-1',
3642: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3643: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3644: 1, p_family_id,
3645: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3646: 'Y', AD.Original_Item_Id,
3649: p_level_id, AD.DEMAND_CLASS),
3650: AD.DEMAND_CLASS)),
3651: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3652: DECODE(p_demand_class, '-1',
3653: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3654: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3655: 1, p_family_id,
3656: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3657: 'Y', AD.Original_Item_Id,
3662: p_demand_class),
3663: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3664: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3665: DECODE(p_demand_class, '-1',
3666: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3667: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3668: 1, p_family_id,
3669: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3670: 'Y', AD.Original_Item_Id,
3673: p_level_id, AD.DEMAND_CLASS),
3674: AD.DEMAND_CLASS)),
3675: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3676: DECODE(p_demand_class, '-1',
3677: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3678: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3679: 1, p_family_id,
3680: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3681: 'Y', AD.Original_Item_Id,
3684: p_level_id, AD.DEMAND_CLASS),
3685: AD.DEMAND_CLASS)),
3686: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3687: DECODE(p_demand_class, '-1',
3688: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3689: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3690: 1, p_family_id,
3691: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3692: 'Y', AD.Original_Item_Id,
3696: AD.DEMAND_CLASS)),
3697: p_demand_class),
3698: DECODE(AD.DEMAND_CLASS, null, null,
3699: DECODE(p_demand_class, '-1',
3700: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3701: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3702: 1, p_family_id,
3703: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3704: 'Y', AD.Original_Item_Id,
3709: 2, DECODE(AD.CUSTOMER_ID, NULL, p_demand_class,
3710: 0, p_demand_class,
3711: decode(AD.Original_Origination_Type,
3712: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3713: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3714: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3715: 1, p_family_id,
3716: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3717: 'Y', AD.Original_Item_Id,
3717: 'Y', AD.Original_Item_Id,
3718: p_family_id)),
3719: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3720: p_level_id, NULL),
3721: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3722: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3723: 1, p_family_id,
3724: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3725: 'Y', AD.Original_Item_Id,
3725: 'Y', AD.Original_Item_Id,
3726: p_family_id)),
3727: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3728: p_level_id, NULL),
3729: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3730: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3731: 1, p_family_id,
3732: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3733: 'Y', AD.Original_Item_Id,
3735: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3736: p_level_id, NULL),
3737: p_demand_class),
3738: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3739: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3740: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3741: 1, p_family_id,
3742: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3743: 'Y', AD.Original_Item_Id,
3743: 'Y', AD.Original_Item_Id,
3744: p_family_id)),
3745: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3746: p_level_id, NULL),
3747: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3748: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3749: 1, p_family_id,
3750: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3751: 'Y', AD.Original_Item_Id,
3751: 'Y', AD.Original_Item_Id,
3752: p_family_id)),
3753: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3754: p_level_id, NULL),
3755: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3756: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3757: 1, p_family_id,
3758: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3759: 'Y', AD.Original_Item_Id,
3760: p_family_id)),
3761: p_org_id, p_instance_id, trunc(AD.Demand_Date),
3762: p_level_id, NULL),
3763: p_demand_class),
3764: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
3765: 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,
3785: SA.ALLOCATED_QUANTITY*
3786: DECODE(DECODE(MSC_AATP_PVT.G_HIERARCHY_PROFILE,
3787: 1, DECODE(SA.DEMAND_CLASS, null, null,
3788: DECODE(p_demand_class, '-1',
3789: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3790: null,
3791: null,
3792: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3793: 1, p_family_id,
3801: SA.DEMAND_CLASS),
3802: SA.DEMAND_CLASS)),
3803: 2, DECODE(SA.CUSTOMER_ID, NULL, TO_CHAR(NULL),
3804: 0, TO_CHAR(NULL),
3805: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3806: SA.CUSTOMER_ID,
3807: SA.SHIP_TO_SITE_ID,
3808: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3809: 1, p_family_id,
3816: p_level_id,
3817: NULL))),
3818: p_demand_class, 1,
3819: NULL, nvl(MIHM.allocation_percent/100,1), --4365873
3820: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
3821: p_instance_id,
3822: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3823: 1, p_family_id,
3824: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3831: TRUNC(SA.SUPPLY_DATE)),
3832: 1),*/
3833: DECODE(
3834: MIHM.allocation_percent/100, --4365873
3835: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
3836: p_instance_id,
3837: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
3838: 1, p_family_id,
3839: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3931: 1, decode(AD.Original_Origination_Type,
3932: 6, decode(AD.SOURCE_ORGANIZATION_ID,
3933: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3934: DECODE(p_demand_class, '-1',
3935: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3936: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3937: 1, p_family_id,
3938: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3939: 'Y', AD.Original_Item_Id,
3942: p_level_id, AD.DEMAND_CLASS),
3943: AD.DEMAND_CLASS)),
3944: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3945: DECODE(p_demand_class, '-1',
3946: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3947: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3948: 1, p_family_id,
3949: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3950: 'Y', AD.Original_Item_Id,
3953: p_level_id, AD.DEMAND_CLASS),
3954: AD.DEMAND_CLASS)),
3955: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3956: DECODE(p_demand_class, '-1',
3957: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3958: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3959: 1, p_family_id,
3960: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3961: 'Y', AD.Original_Item_Id,
3965: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
3966: 30, decode(AD.SOURCE_ORGANIZATION_ID,
3967: NULL, DECODE(AD.DEMAND_CLASS, null, null,
3968: DECODE(p_demand_class, '-1',
3969: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3970: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3971: 1, p_family_id,
3972: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3973: 'Y', AD.Original_Item_Id,
3976: p_level_id, AD.DEMAND_CLASS),
3977: AD.DEMAND_CLASS)),
3978: -23453, DECODE(AD.DEMAND_CLASS, null, null,
3979: DECODE(p_demand_class, '-1',
3980: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3981: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3982: 1, p_family_id,
3983: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3984: 'Y', AD.Original_Item_Id,
3987: p_level_id, AD.DEMAND_CLASS),
3988: AD.DEMAND_CLASS)),
3989: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
3990: DECODE(p_demand_class, '-1',
3991: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
3992: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
3993: 1, p_family_id,
3994: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
3995: 'Y', AD.Original_Item_Id,
3998: p_level_id, AD.DEMAND_CLASS),
3999: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
4000: DECODE(AD.DEMAND_CLASS, null, null,
4001: DECODE(p_demand_class, '-1',
4002: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4003: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4004: 1, p_family_id,
4005: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4006: 'Y', AD.Original_Item_Id,
4011: 2, DECODE(AD.CUSTOMER_ID, NULL, TO_CHAR(NULL),
4012: 0, TO_CHAR(NULL),
4013: decode(AD.Original_Origination_Type,
4014: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4015: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4016: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4017: 1, p_family_id,
4018: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4019: 'Y', AD.Original_Item_Id,
4019: 'Y', AD.Original_Item_Id,
4020: p_family_id)),
4021: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4022: p_level_id, NULL),
4023: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4024: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4025: 1, p_family_id,
4026: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4027: 'Y', AD.Original_Item_Id,
4027: 'Y', AD.Original_Item_Id,
4028: p_family_id)),
4029: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4030: p_level_id, NULL),
4031: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4032: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4033: 1, p_family_id,
4034: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4035: 'Y', AD.Original_Item_Id,
4037: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4038: p_level_id, NULL),
4039: TO_CHAR(NULL)),
4040: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4041: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4042: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4043: 1, p_family_id,
4044: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4045: 'Y', AD.Original_Item_Id,
4045: 'Y', AD.Original_Item_Id,
4046: p_family_id)),
4047: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4048: p_level_id, NULL),
4049: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4050: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4051: 1, p_family_id,
4052: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4053: 'Y', AD.Original_Item_Id,
4053: 'Y', AD.Original_Item_Id,
4054: p_family_id)),
4055: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4056: p_level_id, NULL),
4057: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4058: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4059: 1, p_family_id,
4060: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4061: 'Y', AD.Original_Item_Id,
4062: p_family_id)),
4063: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4064: p_level_id, NULL),
4065: TO_CHAR(NULL)),
4066: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4067: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4068: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4069: 1, p_family_id,
4070: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4072: p_family_id)), p_org_id, p_instance_id,
4073: trunc(AD.Demand_Date),p_level_id, NULL)))),
4074: p_demand_class, 1,
4075: Decode(AD.Demand_Class, NULL, --4365873
4076: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
4077: AD.PARENT_DEMAND_ID,
4078: trunc(AD.Demand_Date),
4079: AD.USING_ASSEMBLY_ITEM_ID,
4080: DECODE(AD.SOURCE_ORGANIZATION_ID,
4092: 1, decode(AD.Original_Origination_Type,
4093: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4094: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4095: DECODE(p_demand_class, '-1',
4096: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4097: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4098: 1, p_family_id,
4099: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4100: 'Y', AD.Original_Item_Id,
4103: p_level_id, AD.DEMAND_CLASS),
4104: AD.DEMAND_CLASS)),
4105: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4106: DECODE(p_demand_class, '-1',
4107: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4108: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4109: 1, p_family_id,
4110: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4111: 'Y', AD.Original_Item_Id,
4114: p_level_id, AD.DEMAND_CLASS),
4115: AD.DEMAND_CLASS)),
4116: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4117: DECODE(p_demand_class, '-1',
4118: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4119: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4120: 1, p_family_id,
4121: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4122: 'Y', AD.Original_Item_Id,
4127: p_demand_class),
4128: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4129: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4130: DECODE(p_demand_class, '-1',
4131: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4132: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4133: 1, p_family_id,
4134: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4135: 'Y', AD.Original_Item_Id,
4138: p_level_id, AD.DEMAND_CLASS),
4139: AD.DEMAND_CLASS)),
4140: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4141: DECODE(p_demand_class, '-1',
4142: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4143: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4144: 1, p_family_id,
4145: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4146: 'Y', AD.Original_Item_Id,
4149: p_level_id, AD.DEMAND_CLASS),
4150: AD.DEMAND_CLASS)),
4151: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4152: DECODE(p_demand_class, '-1',
4153: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4154: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4155: 1, p_family_id,
4156: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4157: 'Y', AD.Original_Item_Id,
4161: AD.DEMAND_CLASS)),
4162: p_demand_class),
4163: DECODE(AD.DEMAND_CLASS, null, null,
4164: DECODE(p_demand_class, '-1',
4165: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4166: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4167: 1, p_family_id,
4168: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4169: 'Y', AD.Original_Item_Id,
4174: 2, DECODE(AD.CUSTOMER_ID, NULL, p_demand_class,
4175: 0, p_demand_class,
4176: decode(AD.Original_Origination_Type,
4177: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4178: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4179: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4180: 1, p_family_id,
4181: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4182: 'Y', AD.Original_Item_Id,
4182: 'Y', AD.Original_Item_Id,
4183: p_family_id)),
4184: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4185: p_level_id, NULL),
4186: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4187: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4188: 1, p_family_id,
4189: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4190: 'Y', AD.Original_Item_Id,
4190: 'Y', AD.Original_Item_Id,
4191: p_family_id)),
4192: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4193: p_level_id, NULL),
4194: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4195: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4196: 1, p_family_id,
4197: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4198: 'Y', AD.Original_Item_Id,
4200: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4201: p_level_id, NULL),
4202: p_demand_class),
4203: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4204: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4205: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4206: 1, p_family_id,
4207: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4208: 'Y', AD.Original_Item_Id,
4208: 'Y', AD.Original_Item_Id,
4209: p_family_id)),
4210: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4211: p_level_id, NULL),
4212: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4213: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4214: 1, p_family_id,
4215: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4216: 'Y', AD.Original_Item_Id,
4216: 'Y', AD.Original_Item_Id,
4217: p_family_id)),
4218: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4219: p_level_id, NULL),
4220: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4221: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4222: 1, p_family_id,
4223: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4224: 'Y', AD.Original_Item_Id,
4225: p_family_id)),
4226: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4227: p_level_id, NULL),
4228: p_demand_class),
4229: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4230: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4231: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4232: 1, p_family_id,
4233: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4252: SA.ALLOCATED_QUANTITY*
4253: DECODE(DECODE(MSC_AATP_PVT.G_HIERARCHY_PROFILE,
4254: 1, DECODE(SA.DEMAND_CLASS, null, null,
4255: DECODE(p_demand_class, '-1',
4256: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4257: null,
4258: null,
4259: Decode(sign(trunc(SA.Supply_Date) - p_atf_date),
4260: 1, p_family_id,
4268: sa.DEMAND_CLASS),
4269: sa.DEMAND_CLASS)),
4270: 2, DECODE(sa.CUSTOMER_ID, NULL, TO_CHAR(NULL),
4271: 0, TO_CHAR(NULL),
4272: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4273: sa.CUSTOMER_ID,
4274: sa.SHIP_TO_SITE_ID,
4275: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4276: 1, p_family_id,
4283: p_level_id,
4284: NULL))),
4285: p_demand_class, 1,
4286: NULL, nvl(MIHM.allocation_percent/100,1), --4365873
4287: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
4288: p_instance_id,
4289: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4290: 1, p_family_id,
4291: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4298: TRUNC(sa.SUPPLY_DATE)),
4299: 1),*/
4300: DECODE(
4301: MIHM.allocation_percent/100, --4365873
4302: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
4303: p_instance_id,
4304: Decode(sign(trunc(sa.Supply_Date) - p_atf_date),
4305: 1, p_family_id,
4306: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4482: 1, decode(AD.Original_Origination_Type,
4483: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4484: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4485: DECODE(p_demand_class, '-1',
4486: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4487: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4488: 1, p_family_id,
4489: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4490: 'Y', AD.Original_Item_Id,
4493: p_level_id, AD.DEMAND_CLASS),
4494: AD.DEMAND_CLASS)),
4495: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4496: DECODE(p_demand_class, '-1',
4497: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4498: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4499: 1, p_family_id,
4500: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4501: 'Y', AD.Original_Item_Id,
4504: p_level_id, AD.DEMAND_CLASS),
4505: AD.DEMAND_CLASS)),
4506: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4507: DECODE(p_demand_class, '-1',
4508: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4509: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4510: 1, p_family_id,
4511: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4512: 'Y', AD.Original_Item_Id,
4516: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
4517: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4518: NULL, 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: -23453, 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,
4538: p_level_id, AD.DEMAND_CLASS),
4539: AD.DEMAND_CLASS)),
4540: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4541: DECODE(p_demand_class, '-1',
4542: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4543: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4544: 1, p_family_id,
4545: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4546: 'Y', AD.Original_Item_Id,
4549: p_level_id, AD.DEMAND_CLASS),
4550: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
4551: DECODE(AD.DEMAND_CLASS, null, null,
4552: DECODE(p_demand_class, '-1',
4553: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4554: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4555: 1, p_family_id,
4556: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4557: 'Y', AD.Original_Item_Id,
4562: 2, DECODE(AD.CUSTOMER_ID, NULL, TO_CHAR(NULL),
4563: 0, TO_CHAR(NULL),
4564: decode(AD.Original_Origination_Type,
4565: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4566: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4567: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4568: 1, p_family_id,
4569: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4570: 'Y', AD.Original_Item_Id,
4570: 'Y', AD.Original_Item_Id,
4571: p_family_id)),
4572: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4573: p_level_id, NULL),
4574: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4575: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4576: 1, p_family_id,
4577: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4578: 'Y', AD.Original_Item_Id,
4578: 'Y', AD.Original_Item_Id,
4579: p_family_id)),
4580: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4581: p_level_id, NULL),
4582: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4583: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4584: 1, p_family_id,
4585: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4586: 'Y', AD.Original_Item_Id,
4588: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4589: p_level_id, NULL),
4590: TO_CHAR(NULL)),
4591: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4592: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4593: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4594: 1, p_family_id,
4595: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4596: 'Y', AD.Original_Item_Id,
4596: 'Y', AD.Original_Item_Id,
4597: p_family_id)),
4598: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4599: p_level_id, NULL),
4600: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4601: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4602: 1, p_family_id,
4603: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4604: 'Y', AD.Original_Item_Id,
4604: 'Y', AD.Original_Item_Id,
4605: p_family_id)),
4606: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4607: p_level_id, NULL),
4608: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4609: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4610: 1, p_family_id,
4611: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4612: 'Y', AD.Original_Item_Id,
4613: p_family_id)),
4614: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4615: p_level_id, NULL),
4616: TO_CHAR(NULL)),
4617: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4618: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4619: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4620: 1, p_family_id,
4621: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4624: trunc(AD.Demand_Date),p_level_id, NULL)))),
4625: p_demand_class, 1,
4626: Decode(AD.Demand_Class, NULL, --4365873 If l_demand_class is not null and demand class is populated
4627: -- on supplies record then 0 should be allocated.
4628: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
4629: AD.PARENT_DEMAND_ID,
4630: trunc(AD.Demand_Date),
4631: AD.USING_ASSEMBLY_ITEM_ID,
4632: DECODE(AD.SOURCE_ORGANIZATION_ID,
4644: 1, decode(AD.Original_Origination_Type,
4645: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4646: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4647: DECODE(p_demand_class, '-1',
4648: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4649: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4650: 1, p_family_id,
4651: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4652: 'Y', AD.Original_Item_Id,
4655: p_level_id, AD.DEMAND_CLASS),
4656: AD.DEMAND_CLASS)),
4657: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4658: DECODE(p_demand_class, '-1',
4659: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4660: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4661: 1, p_family_id,
4662: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4663: 'Y', AD.Original_Item_Id,
4666: p_level_id, AD.DEMAND_CLASS),
4667: AD.DEMAND_CLASS)),
4668: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4669: DECODE(p_demand_class, '-1',
4670: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4671: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4672: 1, p_family_id,
4673: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4674: 'Y', AD.Original_Item_Id,
4679: p_demand_class),
4680: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4681: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4682: DECODE(p_demand_class, '-1',
4683: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4684: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4685: 1, p_family_id,
4686: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4687: 'Y', AD.Original_Item_Id,
4690: p_level_id, AD.DEMAND_CLASS),
4691: AD.DEMAND_CLASS)),
4692: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4693: DECODE(p_demand_class, '-1',
4694: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4695: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4696: 1, p_family_id,
4697: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4698: 'Y', AD.Original_Item_Id,
4701: p_level_id, AD.DEMAND_CLASS),
4702: AD.DEMAND_CLASS)),
4703: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4704: DECODE(p_demand_class, '-1',
4705: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4706: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4707: 1, p_family_id,
4708: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4709: 'Y', AD.Original_Item_Id,
4713: AD.DEMAND_CLASS)),
4714: p_demand_class),
4715: 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,
4726: 2, DECODE(AD.CUSTOMER_ID, NULL, p_demand_class,
4727: 0, p_demand_class,
4728: decode(AD.Original_Origination_Type,
4729: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4730: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4731: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4732: 1, p_family_id,
4733: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4734: 'Y', AD.Original_Item_Id,
4734: 'Y', AD.Original_Item_Id,
4735: p_family_id)),
4736: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4737: p_level_id, NULL),
4738: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4739: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4740: 1, p_family_id,
4741: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4742: 'Y', AD.Original_Item_Id,
4742: 'Y', AD.Original_Item_Id,
4743: p_family_id)),
4744: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4745: p_level_id, NULL),
4746: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4747: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4748: 1, p_family_id,
4749: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4750: 'Y', AD.Original_Item_Id,
4752: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4753: p_level_id, NULL),
4754: p_demand_class),
4755: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4756: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4757: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4758: 1, p_family_id,
4759: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4760: 'Y', AD.Original_Item_Id,
4760: 'Y', AD.Original_Item_Id,
4761: p_family_id)),
4762: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4763: p_level_id, NULL),
4764: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4765: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4766: 1, p_family_id,
4767: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4768: 'Y', AD.Original_Item_Id,
4768: 'Y', AD.Original_Item_Id,
4769: p_family_id)),
4770: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4771: p_level_id, NULL),
4772: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4773: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4774: 1, p_family_id,
4775: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4776: 'Y', AD.Original_Item_Id,
4777: p_family_id)),
4778: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4779: p_level_id, NULL),
4780: p_demand_class),
4781: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4782: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4783: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4784: 1, p_family_id,
4785: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4807: 1, decode(AD.Original_Origination_Type,
4808: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4809: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4810: DECODE(p_demand_class, '-1',
4811: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4812: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4813: 1, p_family_id,
4814: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4815: 'Y', AD.Original_Item_Id,
4818: p_level_id, AD.DEMAND_CLASS),
4819: AD.DEMAND_CLASS)),
4820: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4821: DECODE(p_demand_class, '-1',
4822: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4823: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4824: 1, p_family_id,
4825: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4826: 'Y', AD.Original_Item_Id,
4829: p_level_id, AD.DEMAND_CLASS),
4830: AD.DEMAND_CLASS)),
4831: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4832: DECODE(p_demand_class, '-1',
4833: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4834: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4835: 1, p_family_id,
4836: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4837: 'Y', AD.Original_Item_Id,
4841: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
4842: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4843: NULL, 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: -23453, 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,
4863: p_level_id, AD.DEMAND_CLASS),
4864: AD.DEMAND_CLASS)),
4865: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4866: DECODE(p_demand_class, '-1',
4867: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4868: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4869: 1, p_family_id,
4870: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4871: 'Y', AD.Original_Item_Id,
4874: p_level_id, AD.DEMAND_CLASS),
4875: AD.DEMAND_CLASS)), TO_CHAR(NULL)),
4876: DECODE(AD.DEMAND_CLASS, null, null,
4877: DECODE(p_demand_class, '-1',
4878: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4879: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4880: 1, p_family_id,
4881: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4882: 'Y', AD.Original_Item_Id,
4887: 2, DECODE(AD.CUSTOMER_ID, NULL, TO_CHAR(NULL),
4888: 0, TO_CHAR(NULL),
4889: decode(AD.Original_Origination_Type,
4890: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4891: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4892: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4893: 1, p_family_id,
4894: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4895: 'Y', AD.Original_Item_Id,
4895: 'Y', AD.Original_Item_Id,
4896: p_family_id)),
4897: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4898: p_level_id, NULL),
4899: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4900: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4901: 1, p_family_id,
4902: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4903: 'Y', AD.Original_Item_Id,
4903: 'Y', AD.Original_Item_Id,
4904: p_family_id)),
4905: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4906: p_level_id, NULL),
4907: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4908: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4909: 1, p_family_id,
4910: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4911: 'Y', AD.Original_Item_Id,
4913: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4914: p_level_id, NULL),
4915: TO_CHAR(NULL)),
4916: 30, decode(AD.SOURCE_ORGANIZATION_ID,
4917: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4918: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4919: 1, p_family_id,
4920: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4921: 'Y', AD.Original_Item_Id,
4921: 'Y', AD.Original_Item_Id,
4922: p_family_id)),
4923: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4924: p_level_id, NULL),
4925: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4926: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4927: 1, p_family_id,
4928: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4929: 'Y', AD.Original_Item_Id,
4929: 'Y', AD.Original_Item_Id,
4930: p_family_id)),
4931: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4932: p_level_id, NULL),
4933: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4934: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4935: 1, p_family_id,
4936: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4937: 'Y', AD.Original_Item_Id,
4938: p_family_id)),
4939: p_org_id, p_instance_id, trunc(AD.Demand_Date),
4940: p_level_id, NULL),
4941: TO_CHAR(NULL)),
4942: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4943: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
4944: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4945: 1, p_family_id,
4946: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4947: 'Y', AD.Original_Item_Id,
4948: p_family_id)), p_org_id, p_instance_id,
4949: trunc(AD.Demand_Date),p_level_id, NULL)))),
4950: p_demand_class, 1,
4951: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
4952: AD.PARENT_DEMAND_ID,
4953: trunc(AD.Demand_Date),
4954: AD.USING_ASSEMBLY_ITEM_ID,
4955: DECODE(AD.SOURCE_ORGANIZATION_ID,
4967: 1, decode(AD.Original_Origination_Type,
4968: 6, decode(AD.SOURCE_ORGANIZATION_ID,
4969: NULL, DECODE(AD.DEMAND_CLASS, null, null,
4970: DECODE(p_demand_class, '-1',
4971: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4972: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4973: 1, p_family_id,
4974: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4975: 'Y', AD.Original_Item_Id,
4978: p_level_id, AD.DEMAND_CLASS),
4979: AD.DEMAND_CLASS)),
4980: -23453, DECODE(AD.DEMAND_CLASS, null, null,
4981: DECODE(p_demand_class, '-1',
4982: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4983: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4984: 1, p_family_id,
4985: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4986: 'Y', AD.Original_Item_Id,
4989: p_level_id, AD.DEMAND_CLASS),
4990: AD.DEMAND_CLASS)),
4991: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
4992: DECODE(p_demand_class, '-1',
4993: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
4994: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
4995: 1, p_family_id,
4996: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
4997: 'Y', AD.Original_Item_Id,
5002: p_demand_class),
5003: 30, decode(AD.SOURCE_ORGANIZATION_ID,
5004: NULL, DECODE(AD.DEMAND_CLASS, null, null,
5005: DECODE(p_demand_class, '-1',
5006: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5007: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5008: 1, p_family_id,
5009: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5010: 'Y', AD.Original_Item_Id,
5013: p_level_id, AD.DEMAND_CLASS),
5014: AD.DEMAND_CLASS)),
5015: -23453, DECODE(AD.DEMAND_CLASS, null, null,
5016: DECODE(p_demand_class, '-1',
5017: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5018: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5019: 1, p_family_id,
5020: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5021: 'Y', AD.Original_Item_Id,
5024: p_level_id, AD.DEMAND_CLASS),
5025: AD.DEMAND_CLASS)),
5026: AD.ORGANIZATION_ID, DECODE(AD.DEMAND_CLASS, null, null,
5027: DECODE(p_demand_class, '-1',
5028: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5029: null, null, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5030: 1, p_family_id,
5031: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5032: 'Y', AD.Original_Item_Id,
5036: AD.DEMAND_CLASS)),
5037: p_demand_class),
5038: 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,
5049: 2, DECODE(AD.CUSTOMER_ID, NULL, p_demand_class,
5050: 0, p_demand_class,
5051: decode(AD.Original_Origination_Type,
5052: 6, decode(AD.SOURCE_ORGANIZATION_ID,
5053: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5054: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5055: 1, p_family_id,
5056: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5057: 'Y', AD.Original_Item_Id,
5057: 'Y', AD.Original_Item_Id,
5058: p_family_id)),
5059: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5060: p_level_id, NULL),
5061: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5062: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5063: 1, p_family_id,
5064: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5065: 'Y', AD.Original_Item_Id,
5065: 'Y', AD.Original_Item_Id,
5066: p_family_id)),
5067: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5068: p_level_id, NULL),
5069: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5070: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5071: 1, p_family_id,
5072: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5073: 'Y', AD.Original_Item_Id,
5075: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5076: p_level_id, NULL),
5077: p_demand_class),
5078: 30, decode(AD.SOURCE_ORGANIZATION_ID,
5079: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5080: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5081: 1, p_family_id,
5082: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5083: 'Y', AD.Original_Item_Id,
5083: 'Y', AD.Original_Item_Id,
5084: p_family_id)),
5085: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5086: p_level_id, NULL),
5087: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5088: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5089: 1, p_family_id,
5090: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5091: 'Y', AD.Original_Item_Id,
5091: 'Y', AD.Original_Item_Id,
5092: p_family_id)),
5093: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5094: p_level_id, NULL),
5095: AD.ORGANIZATION_ID, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5096: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID, Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5097: 1, p_family_id,
5098: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5099: 'Y', AD.Original_Item_Id,
5100: p_family_id)),
5101: p_org_id, p_instance_id, trunc(AD.Demand_Date),
5102: p_level_id, NULL),
5103: p_demand_class),
5104: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5105: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
5106: Decode(sign(trunc(AD.Demand_Date) - p_atf_date),
5107: 1, p_family_id,
5108: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5172: CSA.ALLOCATED_QUANTITY*
5173: DECODE(DECODE(MSC_AATP_PVT.G_HIERARCHY_PROFILE,
5174: 1, DECODE(CSA.DEMAND_CLASS, null, null,
5175: DECODE(p_demand_class, '-1',
5176: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5177: null,
5178: null,
5179: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5180: 1, p_family_id,
5188: CSA.DEMAND_CLASS),
5189: CSA.DEMAND_CLASS)),
5190: 2, DECODE(CSA.CUSTOMER_ID, NULL, TO_CHAR(NULL),
5191: 0, TO_CHAR(NULL),
5192: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
5193: CSA.CUSTOMER_ID,
5194: CSA.SHIP_TO_SITE_ID,
5195: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5196: 1, p_family_id,
5203: p_level_id,
5204: NULL))),
5205: p_demand_class, 1,
5206: NULL, nvl(MIHM.allocation_percent/100,1), --4365873
5207: /*NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
5208: p_instance_id,
5209: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5210: 1, p_family_id,
5211: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
5218: TRUNC(CSA.SUPPLY_DATE)),
5219: 1),*/
5220: DECODE(
5221: MIHM.allocation_percent/100, --4365873
5222: /*DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
5223: p_instance_id,
5224: Decode(sign(trunc(CSA.Supply_Date) - p_atf_date),
5225: 1, p_family_id,
5226: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6185: DECODE(decode(AD.ORIGINAL_ORIGINATION_TYPE,
6186: 6, decode(AD.source_organization_id,
6187: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6188: DECODE(TEMP.DEMAND_CLASS, '-1',
6189: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6190: null, null,
6191: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6192: 1, p_family_id,
6193: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6198: p_level_id, AD.DEMAND_CLASS),
6199: AD.DEMAND_CLASS)),
6200: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6201: DECODE(TEMP.DEMAND_CLASS, '-1',
6202: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6203: null, null,
6204: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6205: 1, p_family_id,
6206: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6211: p_level_id, AD.DEMAND_CLASS),
6212: AD.DEMAND_CLASS)),
6213: AD.organization_id, 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,
6225: AD.DEMAND_CLASS)), NULL),
6226: 30, decode(AD.source_organization_id,
6227: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6228: DECODE(TEMP.DEMAND_CLASS, '-1',
6229: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6230: null, null,
6231: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6232: 1, p_family_id,
6233: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6238: p_level_id, AD.DEMAND_CLASS),
6239: AD.DEMAND_CLASS)),
6240: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6241: DECODE(TEMP.DEMAND_CLASS, '-1',
6242: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6243: null, null,
6244: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6245: 1, p_family_id,
6246: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6251: p_level_id, AD.DEMAND_CLASS),
6252: AD.DEMAND_CLASS)),
6253: AD.organization_id, 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)), NULL),
6266: 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,
6276: p_instance_id, trunc(AD.DEMAND_DATE),
6277: p_level_id, AD.DEMAND_CLASS),
6278: AD.DEMAND_CLASS))),
6279: TEMP.DEMAND_CLASS, 1,
6280: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
6281: AD.PARENT_DEMAND_ID,
6282: trunc(AD.DEMAND_DATE),
6283: AD.USING_ASSEMBLY_ITEM_ID,
6284: DECODE(AD.SOURCE_ORGANIZATION_ID,
6295: decode(AD.ORIGINAL_ORIGINATION_TYPE,
6296: 6, decode(AD.source_organization_id,
6297: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6298: DECODE(TEMP.DEMAND_CLASS, '-1',
6299: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6300: null, null,
6301: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6302: 1, p_family_id,
6303: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6308: p_level_id, AD.DEMAND_CLASS),
6309: AD.DEMAND_CLASS)),
6310: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6311: DECODE(TEMP.DEMAND_CLASS, '-1',
6312: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6313: null, null,
6314: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6315: 1, p_family_id,
6316: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6321: p_level_id, AD.DEMAND_CLASS),
6322: AD.DEMAND_CLASS)),
6323: AD.organization_id, 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,
6335: AD.DEMAND_CLASS)), TEMP.DEMAND_CLASS),
6336: 30, decode(AD.source_organization_id,
6337: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6338: DECODE(TEMP.DEMAND_CLASS, '-1',
6339: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6340: null, null,
6341: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6342: 1, p_family_id,
6343: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6348: p_level_id, AD.DEMAND_CLASS),
6349: AD.DEMAND_CLASS)),
6350: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6351: DECODE(TEMP.DEMAND_CLASS, '-1',
6352: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6353: null, null,
6354: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6355: 1, p_family_id,
6356: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6361: p_level_id, AD.DEMAND_CLASS),
6362: AD.DEMAND_CLASS)),
6363: AD.organization_id, 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)), TEMP.DEMAND_CLASS),
6376: 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,
6413: DECODE(decode(AD.ORIGINAL_ORIGINATION_TYPE,
6414: 6, decode(AD.source_organization_id,
6415: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6416: DECODE(TEMP.DEMAND_CLASS, '-1',
6417: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6418: null, null,
6419: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6420: 1, p_family_id,
6421: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6426: p_level_id, AD.DEMAND_CLASS),
6427: AD.DEMAND_CLASS)),
6428: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6429: DECODE(TEMP.DEMAND_CLASS, '-1',
6430: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6431: null, null,
6432: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6433: 1, p_family_id,
6434: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6439: p_level_id, AD.DEMAND_CLASS),
6440: AD.DEMAND_CLASS)),
6441: AD.organization_id, 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,
6453: AD.DEMAND_CLASS)), NULL),
6454: 30, decode(AD.source_organization_id,
6455: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6456: DECODE(TEMP.DEMAND_CLASS, '-1',
6457: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6458: null, null,
6459: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6460: 1, p_family_id,
6461: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6466: p_level_id, AD.DEMAND_CLASS),
6467: AD.DEMAND_CLASS)),
6468: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6469: DECODE(TEMP.DEMAND_CLASS, '-1',
6470: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6471: null, null,
6472: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6473: 1, p_family_id,
6474: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6479: p_level_id, AD.DEMAND_CLASS),
6480: AD.DEMAND_CLASS)),
6481: AD.organization_id, 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)), NULL),
6494: 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,
6504: p_instance_id, trunc(AD.DEMAND_DATE),
6505: p_level_id, AD.DEMAND_CLASS),
6506: AD.DEMAND_CLASS))),
6507: TEMP.DEMAND_CLASS, 1,
6508: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
6509: AD.PARENT_DEMAND_ID,
6510: trunc(AD.DEMAND_DATE),
6511: AD.USING_ASSEMBLY_ITEM_ID,
6512: DECODE(AD.SOURCE_ORGANIZATION_ID,
6523: decode(AD.ORIGINAL_ORIGINATION_TYPE,
6524: 6, decode(AD.source_organization_id,
6525: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6526: DECODE(TEMP.DEMAND_CLASS, '-1',
6527: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6528: null, null,
6529: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6530: 1, p_family_id,
6531: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6536: p_level_id, AD.DEMAND_CLASS),
6537: AD.DEMAND_CLASS)),
6538: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6539: DECODE(TEMP.DEMAND_CLASS, '-1',
6540: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6541: null, null,
6542: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6543: 1, p_family_id,
6544: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6549: p_level_id, AD.DEMAND_CLASS),
6550: AD.DEMAND_CLASS)),
6551: AD.organization_id, 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,
6563: AD.DEMAND_CLASS)), TEMP.DEMAND_CLASS),
6564: 30, decode(AD.source_organization_id,
6565: NULL, DECODE(AD.DEMAND_CLASS, null, null,
6566: DECODE(TEMP.DEMAND_CLASS, '-1',
6567: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6568: null, null,
6569: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6570: 1, p_family_id,
6571: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6576: p_level_id, AD.DEMAND_CLASS),
6577: AD.DEMAND_CLASS)),
6578: -23453, DECODE(AD.DEMAND_CLASS, null, null,
6579: DECODE(TEMP.DEMAND_CLASS, '-1',
6580: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6581: null, null,
6582: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6583: 1, p_family_id,
6584: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6589: p_level_id, AD.DEMAND_CLASS),
6590: AD.DEMAND_CLASS)),
6591: AD.organization_id, 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)), TEMP.DEMAND_CLASS),
6604: 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,
6662: SA.ORDER_TYPE col10,
6663: SA.ALLOCATED_QUANTITY
6664: * DECODE(DECODE(SA.DEMAND_CLASS, null, null,
6665: DECODE(TEMP.DEMAND_CLASS,'-1',
6666: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6667: null,
6668: null,
6669: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6670: 1, p_family_id,
6679: SA.DEMAND_CLASS)),
6680: TEMP.DEMAND_CLASS,
6681: 1,
6682: NULL,
6683: NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
6684: p_instance_id,
6685: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6686: 1, p_family_id,
6687: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6692: null,
6693: TEMP.DEMAND_CLASS,
6694: TRUNC(SA.SUPPLY_DATE)),
6695: 1),
6696: DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
6697: p_instance_id,
6698: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6699: 1, p_family_id,
6700: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6784: DECODE(DECODE(AD.CUSTOMER_ID, NULL, NULL,
6785: 0, NULL,
6786: decode(AD.origination_type,
6787: 6, decode(AD.source_organization_id,
6788: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6789: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6790: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6791: 1, p_family_id,
6792: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6793: 'Y', AD.Original_Item_Id,
6794: p_family_id)),
6795: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6796: p_level_id, NULL),
6797: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6798: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6799: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6800: 1, p_family_id,
6801: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6802: 'Y', AD.Original_Item_Id,
6803: p_family_id)),
6804: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6805: p_level_id, NULL),
6806: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6807: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6808: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6809: 1, p_family_id,
6810: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6813: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6814: p_level_id, NULL),
6815: NULL),
6816: 30, decode(AD.source_organization_id,
6817: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6818: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6819: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6820: 1, p_family_id,
6821: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6822: 'Y', AD.Original_Item_Id,
6823: p_family_id)),
6824: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6825: p_level_id, NULL),
6826: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6827: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6828: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6829: 1, p_family_id,
6830: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6831: 'Y', AD.Original_Item_Id,
6832: p_family_id)),
6833: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6834: p_level_id, NULL),
6835: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6836: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6837: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6838: 1, p_family_id,
6839: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6841: p_family_id)),
6842: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6843: p_level_id, NULL),
6844: NULL),
6845: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6846: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6847: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6848: 1, p_family_id,
6849: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6852: p_org_id, p_instance_id,
6853: TRUNC(AD.ORIGINAL_DEMAND_DATE),
6854: p_level_id, NULL))),
6855: TEMP.LEVEL_3_DEMAND_CLASS, 1,
6856: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
6857: AD.PARENT_DEMAND_ID,
6858: TRUNC(AD.ORIGINAL_DEMAND_DATE),
6859: AD.USING_ASSEMBLY_ITEM_ID,
6860: DECODE(AD.SOURCE_ORGANIZATION_ID,
6871: DECODE(AD.CUSTOMER_ID, NULL, TEMP.LEVEL_3_DEMAND_CLASS,
6872: 0, TEMP.LEVEL_3_DEMAND_CLASS,
6873: decode(AD.origination_type,
6874: 6, decode(AD.source_organization_id,
6875: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6876: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6877: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6878: 1, p_family_id,
6879: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6880: 'Y', AD.Original_Item_Id,
6881: p_family_id)),
6882: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6883: p_level_id, NULL),
6884: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6885: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6886: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6887: 1, p_family_id,
6888: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6889: 'Y', AD.Original_Item_Id,
6890: p_family_id)),
6891: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6892: p_level_id, NULL),
6893: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6894: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6895: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6896: 1, p_family_id,
6897: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6900: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6901: p_level_id, NULL),
6902: TEMP.LEVEL_3_DEMAND_CLASS),
6903: 30, decode(AD.source_organization_id,
6904: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6905: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6906: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6907: 1, p_family_id,
6908: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6909: 'Y', AD.Original_Item_Id,
6910: p_family_id)),
6911: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6912: p_level_id, NULL),
6913: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6914: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6915: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6916: 1, p_family_id,
6917: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6918: 'Y', AD.Original_Item_Id,
6919: p_family_id)),
6920: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6921: p_level_id, NULL),
6922: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6923: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6924: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6925: 1, p_family_id,
6926: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6928: p_family_id)),
6929: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6930: p_level_id, NULL),
6931: TEMP.LEVEL_3_DEMAND_CLASS),
6932: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6933: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6934: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6935: 1, p_family_id,
6936: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6970: DECODE(DECODE(AD.CUSTOMER_ID, NULL, NULL,
6971: 0, NULL,
6972: decode(AD.origination_type,
6973: 6, decode(AD.source_organization_id,
6974: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6975: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6976: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6977: 1, p_family_id,
6978: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6979: 'Y', AD.Original_Item_Id,
6980: p_family_id)),
6981: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6982: p_level_id, NULL),
6983: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6984: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6985: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6986: 1, p_family_id,
6987: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6988: 'Y', AD.Original_Item_Id,
6989: p_family_id)),
6990: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
6991: p_level_id, NULL),
6992: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
6993: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
6994: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
6995: 1, p_family_id,
6996: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
6999: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7000: p_level_id, NULL),
7001: NULL),
7002: 30, decode(AD.source_organization_id,
7003: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7004: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7005: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7006: 1, p_family_id,
7007: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7008: 'Y', AD.Original_Item_Id,
7009: p_family_id)),
7010: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7011: p_level_id, NULL),
7012: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7013: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7014: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7015: 1, p_family_id,
7016: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7017: 'Y', AD.Original_Item_Id,
7018: p_family_id)),
7019: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7020: p_level_id, NULL),
7021: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7022: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7023: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7024: 1, p_family_id,
7025: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7027: p_family_id)),
7028: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7029: p_level_id, NULL),
7030: NULL),
7031: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7032: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7033: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7034: 1, p_family_id,
7035: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7038: p_org_id, p_instance_id,
7039: TRUNC(AD.ORIGINAL_DEMAND_DATE),
7040: p_level_id, NULL))),
7041: TEMP.LEVEL_3_DEMAND_CLASS, 1,
7042: MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
7043: AD.PARENT_DEMAND_ID,
7044: TRUNC(AD.ORIGINAL_DEMAND_DATE),
7045: AD.USING_ASSEMBLY_ITEM_ID,
7046: DECODE(AD.SOURCE_ORGANIZATION_ID,
7057: DECODE(AD.CUSTOMER_ID, NULL, TEMP.LEVEL_3_DEMAND_CLASS,
7058: 0, TEMP.LEVEL_3_DEMAND_CLASS,
7059: decode(AD.origination_type,
7060: 6, decode(AD.source_organization_id,
7061: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7062: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7063: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7064: 1, p_family_id,
7065: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7066: 'Y', AD.Original_Item_Id,
7067: p_family_id)),
7068: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7069: p_level_id, NULL),
7070: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7071: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7072: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7073: 1, p_family_id,
7074: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7075: 'Y', AD.Original_Item_Id,
7076: p_family_id)),
7077: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7078: p_level_id, NULL),
7079: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7080: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7081: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7082: 1, p_family_id,
7083: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7086: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7087: p_level_id, NULL),
7088: TEMP.LEVEL_3_DEMAND_CLASS),
7089: 30, decode(AD.source_organization_id,
7090: NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7091: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7092: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7093: 1, p_family_id,
7094: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7095: 'Y', AD.Original_Item_Id,
7096: p_family_id)),
7097: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7098: p_level_id, NULL),
7099: -23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7100: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7101: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7102: 1, p_family_id,
7103: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7104: 'Y', AD.Original_Item_Id,
7105: p_family_id)),
7106: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7107: p_level_id, NULL),
7108: AD.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7109: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7110: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7111: 1, p_family_id,
7112: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7114: p_family_id)),
7115: p_org_id, p_instance_id, TRUNC(AD.ORIGINAL_DEMAND_DATE),
7116: p_level_id, NULL),
7117: TEMP.LEVEL_3_DEMAND_CLASS),
7118: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7119: AD.CUSTOMER_ID, AD.SHIP_TO_SITE_ID,
7120: Decode(sign(trunc(AD.Demand_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7121: 1, p_family_id,
7122: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7170: SA.ALLOCATED_QUANTITY) col9, -- supply_demand_source_quantity
7171: SA.ORDER_TYPE col10, -- supply_demand_source_type
7172: SA.ALLOCATED_QUANTITY
7173: * DECODE(DECODE(SA.CUSTOMER_ID, NULL, NULL,
7174: MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
7175: SA.CUSTOMER_ID,
7176: SA.SHIP_TO_SITE_ID,
7177: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7178: 1, p_family_id,
7186: NULL)),
7187: TEMP.LEVEL_3_DEMAND_CLASS,
7188: 1,
7189: NULL,
7190: NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
7191: p_instance_id,
7192: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7193: 1, p_family_id,
7194: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
7198: null,
7199: null,
7200: TEMP.LEVEL_3_DEMAND_CLASS,
7201: TRUNC(SA.SUPPLY_DATE)), 1),
7202: DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
7203: p_instance_id,
7204: Decode(sign(trunc(SA.Supply_Date) - MSC_ATP_ALLOC.G_Atf_Date),
7205: 1, p_family_id,
7206: Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,