[Home] [Help]
PACKAGE BODY: APPS.MRP_LAUNCH_PLAN_PK
Source
1 PACKAGE BODY mrp_launch_plan_pk AS
2 /* $Header: MRPPLAPB.pls 120.1 2006/06/22 13:24:12 rgurugub noship $ */
3
4 -- ************************* mrp_launch_plan ******************************* --
5
6 PROCEDURE mrp_launch_plan (
7 errbuf OUT NOCOPY VARCHAR2,
8 retcode OUT NOCOPY NUMBER,
9 arg_org_id IN NUMBER,
10 arg_compile_desig IN VARCHAR2,
11 arg_launch_snapshot IN NUMBER,
12 arg_launch_planner IN NUMBER,
13 arg_anchor_date IN VARCHAR2,
14 arg_plan_horizon IN VARCHAR2 default NULL)
15 IS
16
17 var_exploder_req_id INTEGER;
18 var_snapshot_req_id INTEGER;
19 var_planner_req_id INTEGER;
20 var_mps_relief_req_id INTEGER;
21 var_user_id INTEGER;
22 var_production1 INTEGER;
23 var_production2 INTEGER;
24 var_auto_release_id INTEGER;
25 months NUMBER;
26 var_new_date DATE;
27
28 G_MRP_DEBUG VARCHAR2(1); /*2663505*/
29
30 BEGIN
31 G_MRP_DEBUG := FND_PROFILE.VALUE('MRP_DEBUG') ; /*2663505*/
32
33 /*----------------------------------------+
34 | Update mrp_plans with plan horizon date |
35 +----------------------------------------*/
36
37 IF G_MRP_DEBUG = 'Y' THEN /*2663505*/
38
39 MRP_UTIL.Mrp_Debug('******About to Launch Plan******');
40 END IF ; /*2663505*/
41
42 IF arg_plan_horizon IS NULL THEN
43 months := fnd_profile.value('MRP_CUTOFF_DATE_OFFSET');
44
45 var_new_date := MRP_CALENDAR.NEXT_WORK_DAY(arg_org_id,1,
46 TO_DATE(TO_CHAR(add_months(sysdate, NVL(months, 12)),
47 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS')) ;
48 UPDATE mrp_plans
49 SET curr_cutoff_date = var_new_date,
50 plan_completion_date = NULL,
51 data_completion_date = NULL
52 WHERE organization_id = arg_org_id
53 AND compile_designator = arg_compile_desig;
54 COMMIT;
55 ELSE
56
57 var_new_date := MRP_CALENDAR.NEXT_WORK_DAY(arg_org_id,1,TO_DATE(arg_plan_horizon, 'YYYY/MM/DD HH24:MI:SS')) ;
58 UPDATE mrp_plans
59 SET curr_cutoff_date = var_new_date,
60 plan_completion_date = NULL,
61 data_completion_date = NULL
62 WHERE organization_id = arg_org_id
63 AND compile_designator = arg_compile_desig;
64 COMMIT;
65 END IF;
66 /*---------------------------------------+
67 | Update mrp_parameters with anchor date |
68 +---------------------------------------*/
69 UPDATE mrp_parameters
70 SET repetitive_anchor_date = TO_DATE(arg_anchor_date, 'YYYY/MM/DD HH24:MI:SS')
71 WHERE organization_id IN (select planned_organization
72 from mrp_plan_organizations_v
73 where organization_id = arg_org_id
74 and compile_designator = arg_compile_desig);
75
76 COMMIT;
77
78 /*-------------+
79 | Get user id |
80 +--------------*/
81
82 var_user_id := fnd_profile.value('USER_ID');
83 /*-----------------------------------------------+
84 | Insert subinventories into mrp_sub_inventories |
85 | that are defined after options are defined |
86 +-----------------------------------------------*/
87 BEGIN
88 INSERT INTO MRP_SUB_INVENTORIES
89 (SUB_INVENTORY_CODE,
90 ORGANIZATION_ID,
91 COMPILE_DESIGNATOR,
92 CREATION_DATE,
93 CREATED_BY,
94 LAST_UPDATE_LOGIN,
95 LAST_UPDATE_DATE,
96 LAST_UPDATED_BY,
97 NETTING_TYPE)
98 SELECT msi.secondary_inventory_name,
99 mpo.planned_organization,
100 arg_compile_desig,
101 SYSDATE,
102 1,
103 -1,
104 SYSDATE,
105 1,
106 msi.availability_type
107 FROM MTL_SECONDARY_INVENTORIES msi,
108 mrp_plan_organizations_v mpo
109 WHERE NOT EXISTS
110 (SELECT NULL
111 FROM MRP_SUB_INVENTORIES SUB
112 WHERE SUB.ORGANIZATION_ID = mpo.planned_organization
113 AND SUB.COMPILE_DESIGNATOR = mpo.compile_designator
114 AND SUB.sub_inventory_code = msi.SECONDARY_INVENTORY_NAME)
115 AND NVL(MSI.DISABLE_DATE, SYSDATE + 1) > TRUNC(SYSDATE)
116 AND msi.ORGANIZATION_ID = mpo.planned_organization
117 and mpo.organization_id = arg_org_id
118 and mpo.compile_designator = arg_compile_desig;
119
120 COMMIT;
121 EXCEPTION when no_data_found then
122 null;
123 END;
124
125
126 IF (arg_launch_snapshot = SYS_YES) THEN
127 var_mps_relief_req_id := NULL;
128 var_mps_relief_req_id := FND_REQUEST.SUBMIT_REQUEST (
129 'MRP',
130 'MRCMPW',
131 NULL,
132 NULL,
133 FALSE,
134 TO_CHAR(4));
135 COMMIT;
136
137 IF G_MRP_DEBUG = 'Y' THEN /*2663505*/
138
139 MRP_UTIL.Mrp_Debug('Launched MPS Relief Worker:'||
140 to_char(var_mps_relief_req_id));
141 END IF ; /*2663505*/
142
143 var_snapshot_req_id := NULL;
144 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
145 'MRP', -- application
146 'MRCNSP', -- program
147 NULL, -- description
148 NULL, -- start time
149 FALSE, -- sub_request
150 TO_CHAR(arg_org_id),
151 arg_compile_desig);
152 COMMIT;
153
154 IF G_MRP_DEBUG = 'Y' THEN /*2663505*/
155 MRP_UTIL.Mrp_Debug('Launched Snapshot:'||
156 to_char(var_snapshot_req_id));
157 END IF ; /*2663505*/
158
159 END IF; /* if arg_launch_snapshot = SYS_YES */
160
161 IF ((arg_launch_planner = SYS_YES) AND
162 (arg_launch_snapshot = SYS_NO)) THEN
163 var_planner_req_id := NULL;
164 var_planner_req_id := FND_REQUEST.SUBMIT_REQUEST(
165 'MRP', -- application
166 'MRCNEW', -- program
167 NULL, -- description
168 NULL, -- start time
169 FALSE, -- sub_request
170 TO_CHAR(arg_org_id),
171 arg_compile_desig,
172 0);
173 COMMIT;
174
175 IF G_MRP_DEBUG = 'Y' THEN /*2663505*/
176 MRP_UTIL.Mrp_Debug('Launched Planner:'||
177 to_char(var_planner_req_id));
178 END IF ; /*2663505*/
179 END IF;
180
181 begin
182 SELECT NVL(production, SYS_NO)
183 INTO var_production1
184 FROM mrp_designators
185 WHERE organization_id = arg_org_id
186 AND compile_designator = arg_compile_desig;
187 exception when no_data_found then
188 null;
189 end;
190
191 begin
192 SELECT NVL(production, SYS_NO)
193 INTO var_production2
194 FROM mrp_schedule_designators
195 WHERE organization_id = arg_org_id
196 AND schedule_designator = arg_compile_desig;
197 exception when no_data_found then
198 null;
199 end;
200 IF G_MRP_DEBUG = 'Y' THEN /*2663505*/
201 MRP_UTIL.Mrp_Debug('Exiting with Success');
202 END IF ; /*2663505*/
203 retcode := 0;
204 errbuf := NULL;
205 return;
206 EXCEPTION
207 WHEN OTHERS THEN
208 retcode := 2;
209 errbuf := 'Error in launching plan:' || to_char(sqlcode);
210 END mrp_launch_plan;
211
212 --*************************** get_crp_status ******************************--
213
214 FUNCTION get_crp_status (app_id IN NUMBER,
215 dep_app_id IN NUMBER)
216 RETURN VARCHAR2 IS
217 status VARCHAR2(1);
218 ret_val boolean;
219 industry VARCHAR2(1);
220 BEGIN
221 ret_val:= FND_INSTALLATION.get(app_id, dep_app_id, status, industry);
222 RETURN (status);
223 END;
224
225
226 END; -- package