[Home] [Help]
PACKAGE BODY: APPS.MRP_UPDATE_PLAN_OPTIONS_PK
Source
1 PACKAGE BODY mrp_update_plan_options_pk AS
2 /* $Header: MRPPUPLB.pls 115.0 99/07/16 12:35:24 porting ship $ */
3
4 /******************mrp_update_options****************************************/
5 PROCEDURE mrp_update_options (
6 arg_org_id IN NUMBER,
7 arg_user_id IN NUMBER,
8 arg_compile_desig IN VARCHAR2)
9 IS
10
11 var_org_selection INTEGER;
12
13 BEGIN
14
15 SELECT NVL(organization_selection, SINGLE_ORG)
16 INTO var_org_selection
17 FROM mrp_plans
18 WHERE organization_id = arg_org_id
19 AND compile_designator = arg_compile_desig;
20
21 IF var_org_selection = SINGLE_ORG THEN
22
23 UPDATE mrp_plans
24 SET explosion_start_date = SYSDATE,
25 explosion_completion_date = SYSDATE,
26 data_start_date = SYSDATE,
27 data_completion_date = NULL,
28 last_update_date = SYSDATE,
29 last_updated_by = arg_user_id,
30 schedule_designator = curr_schedule_designator,
31 schedule_type = curr_schedule_type,
32 operation_schedule_type = curr_operation_schedule_type,
33 plan_type = curr_plan_type,
34 cutoff_date = curr_cutoff_date,
35 part_include_type = curr_part_include_type,
36 planning_time_fence_flag =
37 curr_planning_time_fence_flag,
38 demand_time_fence_flag = curr_demand_time_fence_flag,
39 consider_reservations = curr_consider_reservations,
40 plan_safety_stock = curr_plan_safety_stock,
41 consider_wip = curr_consider_wip,
42 consider_po = curr_consider_po,
43 snapshot_lock = curr_snapshot_lock,
44 overwrite_option = curr_overwrite_option,
45 append_planned_orders = curr_append_planned_orders,
46 full_pegging = curr_full_pegging,
47 reservation_level = curr_reservation_level,
48 hard_pegging_level = curr_hard_pegging_level
49 WHERE organization_id = arg_org_id
50 AND compile_designator = arg_compile_desig;
51
52 COMMIT;
53
54 ELSE
55
56 UPDATE mrp_plans
57 SET explosion_start_date = SYSDATE,
58 explosion_completion_date = SYSDATE,
59 data_start_date = SYSDATE,
60 data_completion_date = NULL,
61 last_update_date = SYSDATE,
62 last_updated_by = arg_user_id,
63 operation_schedule_type = curr_operation_schedule_type,
64 plan_type = curr_plan_type,
65 cutoff_date = curr_cutoff_date,
66 part_include_type = curr_part_include_type,
67 planning_time_fence_flag =
68 curr_planning_time_fence_flag,
69 demand_time_fence_flag = curr_demand_time_fence_flag,
70 snapshot_lock = curr_snapshot_lock,
71 overwrite_option = curr_overwrite_option,
72 append_planned_orders = curr_append_planned_orders,
73 assignment_set_id = curr_assignment_set_id,
74 full_pegging = curr_full_pegging,
75 reservation_level = curr_reservation_level,
76 hard_pegging_level = curr_hard_pegging_level
77 WHERE organization_id = arg_org_id
78 AND compile_designator = arg_compile_desig;
79
80 COMMIT;
81
82 INSERT
83 INTO mrp_plan_organizations
84 (organization_id,
85 compile_designator,
86 planned_organization,
87 plan_level,
88 last_updated_by,
89 last_update_date,
90 created_by,
91 creation_date,
92 last_update_login,
93 net_wip,
94 net_reservations,
95 net_purchasing,
96 plan_safety_stock)
97 SELECT organization_id,
98 compile_designator,
99 planned_organization,
100 LAST_LAST_SUBMITTED,
101 arg_user_id,
102 SYSDATE,
103 arg_user_id,
104 SYSDATE,
105 -1,
106 net_wip,
107 net_reservations,
108 net_purchasing,
109 plan_safety_stock
110 FROM mrp_plan_organizations orgs
111 WHERE orgs.organization_id = arg_org_id
112 AND orgs.compile_designator = arg_compile_desig
113 AND orgs.plan_level = LAST_SUBMITTED
114 AND NOT EXISTS
115 (SELECT null
116 from mrp_plan_organizations orgs1
117 where orgs1.organization_id = orgs.organization_id
118 and orgs1.compile_designator = orgs.compile_designator
119 and orgs1.plan_level = LAST_LAST_SUBMITTED
120 and orgs1.planned_organization =
121 orgs.planned_organization);
122 COMMIT;
123
124 DELETE FROM mrp_plan_organizations
125 WHERE organization_id = arg_org_id
126 AND compile_designator = arg_compile_desig
127 AND plan_level = LAST_SUBMITTED;
128
129 COMMIT;
130
131 DELETE FROM mrp_plan_schedules
132 WHERE organization_id = arg_org_id
133 AND compile_designator = arg_compile_desig
134 AND plan_level = LAST_SUBMITTED;
135
136 COMMIT;
137
138 INSERT
139 INTO mrp_plan_organizations
140 (organization_id,
141 compile_designator,
142 planned_organization,
143 plan_level,
144 last_updated_by,
145 last_update_date,
146 created_by,
147 creation_date,
148 last_update_login,
149 net_wip,
150 net_reservations,
151 net_purchasing,
152 plan_safety_stock)
153 SELECT organization_id,
154 compile_designator,
155 planned_organization,
156 LAST_SUBMITTED,
157 arg_user_id,
158 SYSDATE,
159 arg_user_id,
160 SYSDATE,
161 -1,
162 net_wip,
163 net_reservations,
164 net_purchasing,
165 plan_safety_stock
166 FROM mrp_plan_organizations
167 WHERE organization_id = arg_org_id
168 AND compile_designator = arg_compile_desig
169 AND plan_level = CURRENT_LEVEL;
170
171 COMMIT;
172
173 INSERT
174 INTO mrp_plan_schedules
175 (organization_id,
176 compile_designator,
177 input_type,
178 input_name,
179 input_organization_id,
180 plan_level,
181 last_updated_by,
182 last_update_date,
183 created_by,
184 creation_date,
185 last_update_login)
186 SELECT organization_id,
187 compile_designator,
188 input_type,
189 input_name,
190 input_organization_id,
191 LAST_SUBMITTED,
192 arg_user_id,
193 SYSDATE,
194 arg_user_id,
195 SYSDATE,
196 -1
197 FROM mrp_plan_schedules
198 WHERE organization_id = arg_org_id
199 AND compile_designator = arg_compile_desig
200 AND plan_level = CURRENT_LEVEL;
201
202 COMMIT;
203 END IF;
204
205 END mrp_update_options;
206
207 /********************mrp_set_completion_time**********************************/
208 PROCEDURE mrp_set_completion_time (
209 arg_org_id IN NUMBER,
210 arg_user_id IN NUMBER,
211 arg_compile_desig IN VARCHAR2) IS
212 var_org_selection INTEGER;
213 BEGIN
214
215 UPDATE mrp_plans
216 SET data_completion_date = SYSDATE,
217 update_bom = SYS_NO,
218 last_update_date = SYSDATE,
219 last_updated_by = arg_user_id
220 WHERE organization_id = arg_org_id
221 AND compile_designator = arg_compile_desig;
222
223 SELECT NVL(organization_selection, SINGLE_ORG)
224 INTO var_org_selection
225 FROM mrp_plans
226 WHERE organization_id = arg_org_id
227 AND compile_designator = arg_compile_desig;
228
229 if var_org_selection <> SINGLE_ORG
230 THEN
231 DELETE from mrp_plan_organizations
232 WHERE organization_id = arg_org_id
233 AND compile_designator = arg_compile_desig
234 AND plan_level = LAST_LAST_SUBMITTED;
235 end if;
236
237 COMMIT;
238
239 END mrp_set_completion_time;
240
241 END mrp_update_plan_options_pk;