1 PACKAGE BODY MSD_DELETE_DEMAND_PLAN AS
2 /* $Header: msddpdb.pls 120.1 2005/09/30 06:13:26 amitku noship $ */
3
4 procedure Delete (errbuf out nocopy varchar2,
5 retcode out nocopy varchar2,
6 planId in varchar2,
7 can_Connect in varchar2,
8 Demand_Plan_Name in varchar2,
9 Shared_DB_Prefix in varchar2,
10 Code_Location in varchar2,
11 Shared_DB_Location in varchar2,
12 Express_Machine_Port in varchar2,
13 OWA_Virtual_Path_Name in varchar2,
14 EAD_Name in varchar2,
15 Express_Connect_String in varchar2,
16 DeleteAnyway in varchar2) is
17
18
19 cursor c1 is
20 select nvl(dp_build_refresh_num,0)
21 from msd_demand_plans
22 where demand_plan_id = planId;
23
24 l_dp_build_refresh_num number := 0;
25
26
27 BEGIN
28 retcode := '0';
29
30 update msd_demand_plans
31 set delete_plan_flag = 'YES'
32 where demand_plan_id = planId;
33
34 open c1;
35 fetch c1 into l_dp_build_refresh_num;
36 close c1;
37
38 if l_dp_build_refresh_num >= 0 then
39 msd_dpe.purge(errbuf, retcode, planId,
40 Demand_Plan_Name,
41 Shared_DB_Prefix,
42 Code_Location,
43 Shared_DB_Location,
44 Express_Machine_Port,
45 OWA_Virtual_Path_Name,
46 EAD_Name,
47 Express_Connect_String,
48 DeleteAnyway,
49 can_Connect);
50 end if;
51 if ((retcode in ('0','1')) or ((retcode = '2') and ((nvl(can_Connect, 'NO')) = 'YES'))) then
52
53 DELETE FROM msd_demand_plans
54 WHERE demand_plan_id = planId;
55
56 DELETE FROM msd_dp_dimensions
57 WHERE demand_plan_id = planId;
58
59 DELETE FROM msd_dp_parameters
60 WHERE demand_plan_id = planId;
61
62 DELETE FROM msd_dp_scenarios
63 WHERE demand_plan_id = planId;
64
65 DELETE FROM msd_dp_hierarchies
66 WHERE demand_plan_id = planId;
67
68 DELETE FROM msd_dp_express_setup
69 WHERE demand_plan_id = planId;
70
71 DELETE FROM msd_dp_scenario_output_levels
72 WHERE demand_plan_id = planId;
73
74 DELETE FROM msd_dp_scenario_events
75 WHERE demand_plan_id = planId;
76
77 DELETE FROM msd_dp_scenario_entries
78 WHERE demand_plan_id = planId;
79
80 /* Fix. Need to delete Headers as well when entries are deleted. */
81
82 DELETE FROM msd_dp_scenario_revisions
83 WHERE demand_plan_id = planId;
84
85 DELETE FROM msd_dp_events
86 WHERE demand_plan_id = planId;
87
88 DELETE FROM msd_dp_price_lists
89 WHERE demand_plan_id = planId;
90
91 DELETE FROM msd_dp_calendars
92 WHERE demand_plan_id = planId;
93
94 DELETE FROM msd_cs_data_ds
95 WHERE demand_plan_id = planId;
96
97 DELETE FROM msd_dp_parameters_ds
98 WHERE demand_plan_id = planId;
99
100 DELETE FROM msd_level_values_ds
101 WHERE demand_plan_id = planId;
102
103 DELETE FROM msd_dp_seeded_documents
104 WHERE demand_plan_id = planId;
105
106 DELETE FROM msd_dp_seeded_doc_dimensions
107 WHERE demand_plan_id = planId;
108
109 DELETE FROM msd_dp_doc_dim_selections
110 WHERE demand_plan_id = planId;
111
112 DELETE FROM msd_dp_formulas
113 WHERE demand_plan_id = planId;
114
115 DELETE FROM msd_dp_formula_parameters
116 WHERE demand_plan_id = planId;
117
118 /*--------Bug 4615390--------*/
119 DELETE FROM msd_dp_iso_organizations
120 where demand_plan_id = planId;
121
122 fnd_file.new_line(fnd_file.log, 1);
123 fnd_file.put_line(fnd_file.log, 'The Planning Server Demand Plan Definition was processed for deletion.');
124
125 else
126 update msd_demand_plans
127 set delete_plan_flag = 'NO'
128 where demand_plan_id = planId;
129 fnd_file.new_line(fnd_file.log, 1);
130 fnd_file.put_line(fnd_file.log, 'The Planning Server Demand Plan Definition was not processed for deletion.');
131
132 fnd_file.put_line(fnd_file.log, ' ' || errbuf);
133
134 end if;
135 exception
136 when others then
137 update msd_demand_plans
138 set delete_plan_flag = 'NO'
139 where demand_plan_id = planId;
140
141 retcode := '2';
142 end Delete;
143 End;
[Home] [Help]