1 PACKAGE BODY mrp_exceptions_sc_pk AS
2 /* $Header: MRPPEPKB.pls 115.0 99/07/16 12:32:12 porting ship $ */
3
4 PROCEDURE MRP_COMPUTE_EXCEPTIONS (p_query_id IN NUMBER,
5 p_planner IN VARCHAR2,
6 p_org_id IN NUMBER,
7 p_plan_org_id IN NUMBER,
8 p_plan_name IN VARCHAR2,
9 p_plan_start_date IN DATE) IS
10 /* Constant declarations */
11 SYS_YES CONSTANT NUMBER := 1;
12
13 BEGIN
14
15 /* $Header: MRPPEPKB.pls 115.0 99/07/16 12:32:12 porting ship $ */
16
17 /* Insert one row into mrp_form_query for each exception type:
18 1 - Items that are over-committed
19 2 - Items with a shortage
20 3 - Items with excess inventory
21 4 - Items with repetitive variance
22 5 - Items with no activity
23 6 - Orders to be rescheduled in
24 7 - Orders to be rescheduled out
25 8 - Orders to be cancelled
26 9 - Orders with compression days
27 10 - Past due orders
28
29 The column number1 will be set to the exception type.
30 The column number2 will be set to the number of items that
31 have raised each exception.
32 For order level exceptions (6 through 10 above), the column
33 number3 will be set to the number of orders that have
34 raised the exception.
35 */
36
37 IF p_planner is NULL THEN
38 INSERT INTO mrp_form_query
39 (query_id,
40 last_update_date,
41 last_updated_by,
42 creation_date,
43 created_by,
44 last_update_login,
45 number1, /* exception type */
46 number2, /* number of items */
47 char1, /* exception type meaning */
48 number3, /* number of orders */
49 char8, /* compile designator */
50 number4) /* plan organization id */
51 SELECT P_QUERY_ID,
52 sysdate,
53 1,
54 sysdate,
55 1,
56 1,
57 lu.lookup_code,
58 count(ex.organization_id),
59 lu.meaning,
60 decode(lu.lookup_code, 6, 0, 7, 0, 8, 0, 9, 0, 10, 0, NULL),
61 p_plan_name,
62 orgs.organization_id
63 FROM mfg_lookups lu,
64 mrp_item_exceptions ex,
65 mrp_plan_organizations_v orgs
66 WHERE lu.lookup_type = 'MRP_EXCEPTION_CODE_TYPE'
67 AND lu.lookup_code <=10
68 AND ex.exception_type = lu.lookup_code
69 AND ex.organization_id = orgs.planned_organization
70 AND ex.compile_designator = orgs.compile_designator
71 AND ex.version is null
72 AND ex.display = SYS_YES
73 AND orgs.compile_designator = p_plan_name
74 AND orgs.organization_id = p_plan_org_id
75 AND orgs.planned_organization = DECODE(p_org_id,
76 p_plan_org_id, orgs.planned_organization,
77 p_org_id)
78 GROUP BY lu.lookup_code, lu.meaning,
79 orgs.organization_id;
80
81 /*------------------------------------+
82 | Update the number3 column to the |
83 | numbers orders having the exception |
84 +------------------------------------*/
85 UPDATE mrp_form_query q
86 SET number3 = /* number of orders */
87 (SELECT COUNT(r.organization_id)
88 FROM mrp_recommendations r,
89 mrp_item_exceptions e,
90 mrp_plan_organizations_v orgs
91 WHERE ((q.number1 = 6 /* reschedule in */
92 AND r.new_schedule_date < r.old_schedule_date)
93 OR
94 (q.number1 = 7 /* reschedule out */
95 AND r.new_schedule_date> r.old_schedule_date
96 AND r.new_schedule_date > P_PLAN_START_DATE)
97 OR
98 (q.number1 = 8 /* cancel */
99 AND r.DISPOSITION_STATUS_TYPE = 2)
100 OR
101 (q.number1 = 9 /* compression days */
102 AND nvl(r.SCHEDULE_COMPRESSION_DAYS,0) > 0)
103 OR
104 (q.number1 = 10 /* past due */
105 AND r.NEW_SCHEDULE_DATE > r.OLD_SCHEDULE_DATE
106 AND r.OLD_SCHEDULE_DATE < P_PLAN_START_DATE))
107 AND r.organization_id = e.organization_id
108 AND r.COMPILE_DESIGNATOR = e.compile_designator
109 AND r.inventory_item_id = e.inventory_item_id
110 AND r.NEW_ORDER_QUANTITY > 0
111 AND e.organization_id = orgs.planned_organization
112 AND e.compile_designator = orgs.compile_designator
113 AND e.version is null
114 AND orgs.COMPILE_DESIGNATOR = P_PLAN_NAME
115 AND orgs.planned_organization = DECODE(P_ORG_ID,
116 P_PLAN_ORG_ID, orgs.planned_organization, P_ORG_ID)
117 AND e.display = SYS_YES
118 AND e.EXCEPTION_TYPE = q.number1)
119 WHERE q.number2 <> 0
120 AND q.query_id = p_query_id
121 AND q.number1 IN (6, 7, 8, 9, 10);
122
123 ELSE
124 INSERT INTO mrp_form_query
125 (query_id,
126 last_update_date,
127 last_updated_by,
128 creation_date,
129 created_by,
130 last_update_login,
131 number1, /* exception type */
132 number2, /* number of items */
133 char1, /* exception type meaning */
134 number3, /* number of orders */
135 char8,
136 number4)
137 SELECT P_QUERY_ID,
138 sysdate,
139 1,
140 sysdate,
141 1,
142 1,
143 lu.lookup_code,
144 COUNT(ex.organization_id),
145 lu.meaning,
146 DECODE(lu.lookup_code, 6, 0, 7, 0, 8, 0, 9, 0, 10, 0, NULL),
147 P_PLAN_NAME,
148 orgs.organization_id
149 FROM mfg_lookups lu,
150 mrp_item_exceptions ex,
151 mrp_system_items sys,
152 mrp_plan_organizations_v orgs
153 WHERE lu.lookup_type = 'MRP_EXCEPTION_CODE_TYPE'
154 AND lu.lookup_code <=10
155 AND lu.lookup_code = ex.exception_type
156 AND ex.display = SYS_YES
157 AND ex.version is null
158 AND ex.organization_id = sys.organization_id
159 AND ex.compile_designator = sys.compile_designator
160 AND ex.inventory_item_id = sys.inventory_item_id
161 AND sys.planner_code = P_PLANNER
162 AND sys.organization_id = orgs.planned_organization
163 AND sys.compile_designator = orgs.compile_designator
164 AND orgs.compile_designator = P_PLAN_NAME
165 AND orgs.organization_id = P_PLAN_ORG_ID
166 AND orgs.planned_organization = DECODE(P_ORG_ID,
167 P_PLAN_ORG_ID, orgs.planned_organization,
168 P_ORG_ID)
169 GROUP BY lu.lookup_code, lu.meaning, orgs.organization_id;
170
171 /*------------------------------------+
172 | Update the number3 column to the |
173 | numbers orders having the exception |
174 +------------------------------------*/
175
176 UPDATE mrp_form_query q
177 SET number3 = /* number of orders */
178 (select COUNT(r.organization_id)
179 FROM mrp_recommendations r,
180 mrp_item_exceptions e,
181 mrp_system_items s
182 where ((q.number1 = 6 /* reschedule in */
183 and r.new_schedule_date < r.old_schedule_date)
184 or
185 (q.number1 = 7 /* reschedule out */
186 and r.new_schedule_date> r.old_schedule_date
187 and r.new_schedule_date > P_PLAN_START_DATE)
188 or
189 (q.number1 = 8 /* cancel */
190 and r.DISPOSITION_STATUS_TYPE = 2)
191 or
192 (q.number1 = 9 /* compression days */
193 and nvl(r.SCHEDULE_COMPRESSION_DAYS,0) > 0)
194 or
195 (q.number1 = 10 /* past due */
196 and r.new_schedule_date > r.OLD_SCHEDULE_DATE
197 and r.old_schedule_date < P_PLAN_START_DATE))
198 and r.organization_id = e.organization_id
199 and r.compile_designator = e.compile_designator
200 and r.new_order_quantity > 0
201 and r.inventory_item_id = e.inventory_item_id
202 and e.display = SYS_YES
203 and e.version is null
204 and e.organization_id = s.organization_id
205 and e.compile_designator = s.compile_designator
206 and e.inventory_item_id = s.inventory_item_id
207 and e.exception_type = q.number1
208 and s.organization_id = P_ORG_ID
209 and s.compile_designator = P_PLAN_NAME
210 and s.planner_code = P_PLANNER)
211 where q.number2 <> 0
212 and q.query_id = P_QUERY_ID
213 and q.number1 in (6, 7, 8, 9, 10);
214 END IF;
215 END MRP_COMPUTE_EXCEPTIONS; /* End procedure */
216
217 END MRP_EXCEPTIONS_SC_PK; /* End package */
[Home] [Help]