[Home] [Help]
408:
409:
410: -- set the supply plan name to supply plan name for doc dim selection
411:
412: update msd_dp_doc_dim_selections
413:
414: set supply_plan_name = p_supply_plan_name
415:
416: where demand_plan_id = p_new_dp_id
1900: cursor c1 is
1901:
1902: select document_id, dimension_code, selection_sequence
1903:
1904: from msd_dp_doc_dim_selections
1905:
1906: where demand_plan_id = p_new_dp_id
1907:
1908: and (dimension_code, document_id) in
2002: cursor c2(p_document_id in number,p_dimension_code in varchar2) is
2003:
2004: select selection_sequence, selection_type, selection_component, selection_value, supply_plan_name
2005:
2006: from msd_dp_doc_dim_selections
2007:
2008: where demand_plan_id = p_new_dp_id
2009:
2010: and document_id = p_document_id
2414: cursor c2 is
2415:
2416: select distinct selection_value, dimension_code
2417:
2418: from msd_dp_doc_dim_selections
2419:
2420: where demand_plan_id = p_new_dp_id
2421:
2422: and selection_type = 'L';
2426: cursor c3 is
2427:
2428: select distinct selection_value, dimension_code
2429:
2430: from msd_dp_doc_dim_selections
2431:
2432: where demand_plan_id = p_new_dp_id
2433:
2434: and selection_type = 'H';
2540:
2541:
2542: -- disable the selection
2543:
2544: update msd_dp_doc_dim_selections
2545:
2546: set enabled_flag = 'N'
2547:
2548: where demand_plan_id = p_new_dp_id
2560:
2561:
2562: -- change the level
2563:
2564: update msd_dp_doc_dim_selections
2565:
2566: set selection_value = l_level_id
2567:
2568: where demand_plan_id = p_new_dp_id
2632:
2633:
2634: -- disable the selection
2635:
2636: update msd_dp_doc_dim_selections
2637:
2638: set enabled_flag = 'N'
2639:
2640: where demand_plan_id = p_new_dp_id
2652:
2653:
2654: -- change the hierarchy
2655:
2656: update msd_dp_doc_dim_selections
2657:
2658: set selection_value = l_hierarchy_id
2659:
2660: where demand_plan_id = p_new_dp_id
2708: cursor c1 is
2709:
2710: select distinct document_id, dimension_code
2711:
2712: from msd_dp_doc_dim_selections
2713:
2714: where demand_plan_id = p_new_dp_id
2715:
2716: and mandatory_flag = 'Y'
2725:
2726: cursor c2 is
2727:
2728: select distinct document_id
2729: from msd_dp_doc_dim_selections mdds,
2730: msd_dp_parameters mdp
2731: where mdds.demand_plan_id = p_new_dp_id
2732: and mdp.demand_plan_id=p_new_dp_id
2733: and mdp.parameter_type=mdds.selection_value
2751: in
2752:
2753: /*------Fix for bug 4550732--------*/
2754:
2755: (select document_id, dimension_code from msd_dp_doc_dim_selections where demand_plan_id=p_new_dp_id)
2756:
2757: and dimension_code <> 'MEAS';
2758:
2759:
2932:
2933:
2934: -- disable all dimension selections which use related hierarchies and levels
2935:
2936: update msd_dp_doc_dim_selections
2937:
2938: set enabled_flag = 'N'
2939:
2940: where demand_plan_id = p_demand_plan_id
3034:
3035:
3036: -- disable doc dim selections
3037:
3038: update msd_dp_doc_dim_selections
3039:
3040: set enabled_flag = 'N'
3041:
3042: where demand_plan_id = p_demand_plan_id
3152: and nvl(supply_plan_name,'123456789') = nvl(l_parameter_name,'123456789');
3153:
3154:
3155:
3156: update msd_dp_doc_dim_selections
3157:
3158: set enabled_flag = 'N'
3159:
3160: where demand_plan_id = p_demand_plan_id
3278:
3279:
3280: -- disable doc dim selections
3281:
3282: update msd_dp_doc_dim_selections
3283:
3284: set enabled_flag = 'N'
3285:
3286: where demand_plan_id = p_demand_plan_id
3422:
3423:
3424: -- disable doc dim selections
3425:
3426: update msd_dp_doc_dim_selections
3427:
3428: set enabled_flag = 'N'
3429:
3430: where demand_plan_id = p_demand_plan_id
3506:
3507:
3508: -- enable document selections that use related hierarchies and levels
3509:
3510: update msd_dp_doc_dim_selections
3511:
3512: set enabled_flag = 'Y'
3513:
3514: where demand_plan_id = p_Demand_plan_id
3588:
3589:
3590: -- enable doc dim selections
3591:
3592: update msd_dp_doc_dim_selections
3593:
3594: set enabled_flag = 'Y'
3595:
3596: where demand_plan_id = p_Demand_plan_id and selection_type = 'I'
3704: and nvl(supply_plan_name,'123456789') = nvl(l_parameter_name,'123456789');
3705:
3706:
3707:
3708: update msd_dp_doc_dim_selections
3709:
3710: set enabled_flag = 'Y'
3711:
3712: where demand_plan_id = p_demand_plan_id
3886:
3887:
3888: -- enable doc dim selections
3889:
3890: update msd_dp_doc_dim_selections
3891:
3892: set enabled_flag = 'Y'
3893:
3894: where demand_plan_id = p_demand_plan_id
4220:
4221:
4222: -- enable doc dim selections
4223:
4224: update msd_dp_doc_dim_selections
4225:
4226: set enabled_flag = 'Y'
4227:
4228: where demand_plan_id = p_demand_plan_id
4462: cursor c3(p_document_id in number) is
4463:
4464: select selection_value, selection_sequence
4465:
4466: from msd_dp_doc_dim_selections
4467:
4468: where demand_plan_id = p_demand_plan_id
4469:
4470: and dimension_code = 'TIM'
4552:
4553:
4554: -- update with new values
4555:
4556: update msd_dp_doc_dim_selections
4557:
4558: set selection_value = l_new_selection_value*sign(c3_cur.selection_value)
4559:
4560: where demand_plan_id = p_demand_plan_id
4580:
4581:
4582: -- update the selection value with new data
4583:
4584: update msd_dp_doc_dim_selections
4585:
4586: set selection_value = p_output_period_type_id
4587:
4588: where demand_plan_id = p_demand_plan_id
4676: if l_nonseed_flag is null or l_nonseed_flag <> 'Y' then
4677:
4678:
4679:
4680: update msd_dp_doc_dim_selections
4681:
4682: set selection_value = p_hierarchy_id
4683:
4684: where demand_plan_id = p_demand_plan_id
4770: if l_nonseed_flag is null or l_nonseed_flag <> 'Y' then
4771:
4772:
4773:
4774: update msd_dp_doc_dim_selections
4775:
4776: set selection_value = p_level_id
4777:
4778: where demand_plan_id = p_demand_plan_id
4874:
4875:
4876: -- disable the doc dim selections
4877:
4878: update msd_dp_doc_dim_selections
4879:
4880: set enabled_flag = 'N'
4881:
4882: where demand_plan_id = p_demand_plan_id
4908:
4909:
4910: -- change the doc dim selections
4911:
4912: update msd_dp_doc_dim_selections
4913:
4914: set selection_value = p_stream_type
4915:
4916: where demand_plan_id = p_demand_plan_id
4946:
4947:
4948: -- enable the doc dim selections
4949:
4950: update msd_dp_doc_dim_selections
4951:
4952: set enabled_flag = 'Y'
4953:
4954: where demand_plan_id = p_demand_plan_id
6506: cursor c3 is
6507:
6508: select count(*)
6509:
6510: from msd_dp_doc_dim_selections
6511:
6512: where demand_plan_id = p_new_dp_id
6513:
6514: and supply_plan_flag = 'Y';
6530: cursor c5(p_document_id in number, p_dimension_code in varchar2) is
6531:
6532: select selection_sequence
6533:
6534: from msd_dp_doc_dim_selections
6535:
6536: where demand_plan_id = p_new_dp_id
6537:
6538: and supply_plan_name = p_old_supply_plan_name -- Bug 4729854
6548: cursor c6(p_document_id in number, p_dimension_code in varchar2, p_selection_sequence in number) is
6549:
6550: select supply_plan_name, min(selection_sequence)
6551:
6552: from msd_dp_doc_dim_selections
6553:
6554: where demand_plan_id = p_new_dp_id
6555:
6556: and supply_plan_name is not null
6598: if p_supply_plan_id is not null then
6599:
6600:
6601:
6602: update msd_dp_doc_dim_selections
6603:
6604: set supply_plan_name = p_supply_plan_name -- Bug 4729854
6605:
6606: where demand_plan_id = p_new_dp_id
6654:
6655:
6656: -- delete the selection first
6657:
6658: delete from msd_dp_doc_dim_selections
6659:
6660: where demand_plan_id = p_new_dp_id
6661:
6662: and supply_plan_name = p_old_supply_plan_name -- Bug 4729854
6670:
6671:
6672: -- set the selection sequence of the next ascp measure to the selection sequence of the measure being deleted and increase by 1000 to avoid uinque constraint violation
6673:
6674: update msd_dp_doc_dim_selections
6675:
6676: set selection_sequence = c5_cur.selection_sequence
6677:
6678: where demand_plan_id = p_new_dp_id
6736: close c2;
6737:
6738:
6739:
6740: select max(selection_sequence) into l_max_selection_sequence from msd_dp_doc_dim_selections
6741:
6742: where demand_plan_id = p_new_dp_id;
6743:
6744:
6744:
6745:
6746: -- create a new measure but it will not be used as it is not first plan attached
6747:
6748: insert into msd_dp_doc_dim_selections
6749:
6750: (
6751:
6752: demand_plan_id
6832: ,NULL
6833:
6834: ,SYSDATE
6835:
6836: from msd_dp_doc_dim_selections
6837:
6838: where supply_plan_flag = 'Y'
6839:
6840: and demand_plan_id =
6884:
6885:
6886: -- create a new measure and it will be used as it is the first plan attached
6887:
6888: insert into msd_dp_doc_dim_selections
6889:
6890: (
6891:
6892: demand_plan_id
6972: ,NULL
6973:
6974: ,SYSDATE
6975:
6976: from msd_dp_doc_dim_selections
6977:
6978: where supply_plan_flag = 'Y'
6979:
6980: and demand_plan_id =
7030: select fnd_profile.value('MSC_LIABILITY_CALC_LEVEL') into l_level from dual;
7031:
7032:
7033:
7034: update msd_dp_doc_dim_selections
7035:
7036: set selection_value = nvl(fnd_profile.value('MSC_LIABILITY_CALC_LEVEL'),1)
7037:
7038: where demand_plan_id = p_demand_plan_id