1 PACKAGE QA_SKIPLOT_UTILITY AUTHID CURRENT_USER AS
2 /* $Header: qaslutls.pls 120.1 2006/02/15 08:37:07 ntungare noship $ */
3
4 --
5 -- constants defined to distinguish receiving inspection
6 -- from wip inspection
7 --
8 RCV CONSTANT NUMBER := 1;
9 WIP CONSTANT NUMBER := 2;
10
11 ADJACENT_DATE_CHECK CONSTANT NUMBER := 1;
12 DATE_SPAN_CHECK CONSTANT NUMBER := 2;
13
14 -- Check in as Bug 2917141
15 -- GSCC problem ... removed g_miss_char
16 -- skiplot_avail VARCHAR2(1) := fnd_api.g_miss_char;
17
18 skiplot_avail VARCHAR2(1);
19
20 TYPE refCursorTyp IS REF CURSOR;
21
22 TYPE skiplot_plan IS RECORD
23 (plan_id NUMBER,
24 alternate_flag VARCHAR2(1));
25
26 TYPE planList IS TABLE OF skiplot_plan
27 INDEX BY BINARY_INTEGER;
28
29 TYPE plan_state_rec IS RECORD
30 (plan_id number,
31 process_plan_id number,
32 process_id number,
33 adjacent_days number,
34 criteria_id number,
35 alternate_plan_id number,
36 current_rule number,
37 total_round number,
38 day_span number,
39 current_freq_num number,
40 current_freq_denom number,
41 current_round number,
42 current_lot number,
43 lot_accepted number,
44 rule_start_lot_id number,
45 rule_start_date date,
46 last_receipt_lot_id number,
47 last_receipt_date date);
48
49 TYPE planStateTable IS TABLE OF plan_state_rec
50 INDEX BY BINARY_INTEGER;
51
52 --
53 -- The function calls skiplot_control, qa_installation
54 -- and skiplot_setup to check whether skiplot functionality
55 -- is avalilable
56 --
57 FUNCTION CHECK_SKIPLOT_AVAILABILITY (
58 p_txn IN NUMBER,
59 p_organization_id IN NUMBER)
60 RETURN VARCHAR2;
61
62 --
63 -- The function checks whether skip lot control
64 -- is set for an inventory organization.
65 --
66 FUNCTION SKIPLOT_CONTROL
67 (p_organization_id IN NUMBER)
68 RETURN VARCHAR2;
69
70 --
71 -- The function returns whether skip lot criteria
72 -- has been set for the specified organization
73 --
74 FUNCTION SKIPLOT_SETUP (
75 p_txn IN NUMBER,
76 p_organization_id IN NUMBER)
77 RETURN VARCHAR2;
78
79 --
80 -- The function returns process_plan_id
81 --
82 FUNCTION GET_PROCESS_PLAN_ID (
83 p_plan_id IN NUMBER,
84 p_criteria_id IN NUMBER,
85 p_process_id IN NUMBER,
86 p_txn IN NUMBER)RETURN NUMBER;
87
88
89 --
90 -- The procedure gets the frequency based on the rule
91 -- sequence given
92 --
93 PROCEDURE CHECK_RULE_FREQUENCY (
94 p_process_plan_id IN NUMBER,
95 p_rule_seq IN NUMBER,
96 p_freq_num OUT NOCOPY NUMBER,
97 p_freq_denom OUT NOCOPY NUMBER);
98
99 --
100 -- The procedure instantiates the pl/sql table
101 -- plan_states.
102 -- if p_process_plan_id is not provided,
103 -- p_plan_id and p_process_id must be provided
104 -- so that process_plan_id can be derived
105 --
106 PROCEDURE FETCH_PLAN_STATE(
107 p_plan_id IN NUMBER DEFAULT NULL,
108 p_process_plan_id IN NUMBER DEFAULT NULL,
109 p_process_id IN NUMBER DEFAULT NULL,
110 p_criteria_id IN NUMBER,
111 p_txn IN NUMBER DEFAULT NULL,
112 p_plan_state OUT nocopy plan_state_rec);
113
114 --
115 -- The procedure initialize the
116 -- qa_skiplot_plan_states table.
117 --
118 PROCEDURE INIT_PLAN_STATE(
119 p_plan_id IN NUMBER,
120 p_criteria_id IN NUMBER,
121 p_process_id IN NUMBER,
122 p_txn IN NUMBER,
123 p_lot_id IN NUMBER DEFAULT NULL,
124 p_process_plan_id OUT NOCOPY NUMBER);
125
126 --
127 -- The procedure initialize the
128 -- qa_skiplot_plan_states table.
129 --
130 PROCEDURE INIT_PLAN_STATE(
131 p_process_plan_id IN NUMBER,
132 p_criteria_id IN NUMBER,
133 p_txn IN NUMBER,
134 p_lot_id IN NUMBER DEFAULT NULL);
135
136 --
137 -- The procedure initialize all the
138 -- process plan/criteria in the
139 -- qa_skiplot_plan_states table
140 --
141 PROCEDURE INIT_PLAN_STATES(
142 p_process_id IN NUMBER,
143 p_criteria_id IN NUMBER,
144 p_txn IN NUMBER);
145
146 --
147 -- The procedure initializes the plan states
148 -- for all the processes and associated processes plans
149 -- for the specified criteria
150 --
151 PROCEDURE INIT_PLAN_STATES(
152 p_criteria_id IN NUMBER);
153
154 --
155 -- Bug 5037121
156 -- New procedure to reset the last receipt date
157 -- to the sysdate when the skip process is
158 -- interrupted due to the day span being exceeded.
159 -- ntungare Wed Feb 15 07:23:23 PST 2006
160 --
161 PROCEDURE RESET_LAST_RECEIPT_DATE(
162 p_criteria_id IN NUMBER,
163 p_process_plan_id IN NUMBER) ;
164
165 --
166 -- The procedure initializes the plan states
167 -- for all the process plans associated with
168 -- the specified process, no matter which criteria
169 -- this process is linked to.
170 --
171 PROCEDURE RESET_PLAN_STATES(
172 p_process_id IN NUMBER);
173
174 --
175 -- The function checks whether the inspection
176 -- round is finished
177 --
178 FUNCTION INSP_ROUND_FINISHED(
179 p_plan_state IN plan_state_rec) RETURN VARCHAR2;
180
181 --
182 -- The function check whether the inspection
183 -- rule is finished
184 --
185 FUNCTION INSP_RULE_FINISHED(
186 p_plan_state IN plan_state_rec)RETURN VARCHAR2;
187
188 --
189 -- The function gets the next inspection rule
190 -- and returns -1 if no next rule found
191 --
192 FUNCTION GET_NEXT_INSP_RULE(
193 p_plan_state in plan_state_rec)RETURN NUMBER;
194
195 --
196 -- The function checks whether there are more
197 -- inspection rounds available
198 --
199 FUNCTION MORE_ROUNDS (
200 p_plan_state IN plan_state_rec) RETURN VARCHAR2;
201
202 --
203 -- The function checks whether enough lots
204 -- are inspected and accepted in the current
205 -- round
206 --
207 FUNCTION ENOUGH_LOT_ACCEPTED(
208 p_plan_state IN plan_state_rec) RETURN VARCHAR2;
209
210 --
211 -- The function checks whether the receipt
212 -- date satisfy the skip lot receipt date
213 -- restriction
214 --
215 FUNCTION DATE_REASONABLE(
216 p_receipt_date IN DATE DEFAULT NULL,
217 p_check_mode IN NUMBER,
218 p_plan_state plan_state_rec)RETURN VARCHAR2;
219
220 -- The procedure updates insp_stage column
221 -- in criteria table
222 --
223 PROCEDURE UPDATE_INSP_STAGE (
224 p_txn IN NUMBER,
225 p_stage IN VARCHAR2,
226 p_criteria_id IN NUMBER,
227 p_process_id IN NUMBER);
228
229 --
230 -- The procedure updates plan state table
231 --
232 PROCEDURE UPDATE_PLAN_STATE(
233 p_process_plan_id IN NUMBER,
234 p_criteria_id IN NUMBER,
235 p_next_rule IN NUMBER DEFAULT NULL,
236 p_next_round IN NUMBER DEFAULT NULL,
237 p_next_lot IN NUMBER DEFAULT NULL,
238 p_rule_start_lotid IN NUMBER DEFAULT NULL,
239 p_last_receipt_lot_id IN NUMBER DEFAULT NULL,
240 p_lot_accepted IN NUMBER DEFAULT NULL,
241 p_txn IN NUMBER DEFAULT NULL);
242
243
244 --
245 -- The procedure launches notification workflow to
246 -- notify specified user of inspection frequency changes.
247 --
248 PROCEDURE LAUNCH_WORKFLOW (
249 p_process_plan_id IN NUMBER,
250 p_criteria_id IN NUMBER,
251 p_old_freq_num IN NUMBER,
252 p_old_freq_denom IN NUMBER,
253 p_new_freq_num IN NUMBER,
254 p_new_freq_denom IN NUMBER,
255 p_txn IN NUMBER);
256
257 --
258 -- The procedure updates plan state history table
259 --
260 PROCEDURE UPDATE_STATE_HISTORY(
261 p_old_plan_state IN plan_state_rec,
262 p_next_rule IN NUMBER,
263 p_txn IN NUMBER DEFAULT NULL);
264
265 --
266 -- The procedure updates plan state history table
267 --
268 PROCEDURE UPDATE_STATE_HISTORY(
269 p_process_plan_id IN NUMBER,
270 p_criteria_id IN NUMBER,
271 p_old_rule IN NUMBER,
272 p_new_rule IN NUMBER,
273 p_txn IN NUMBER);
274
275 --
276 -- The procedure updates plan state history table
277 --
278 PROCEDURE UPDATE_STATE_HISTORY(
279 p_process_plan_id IN NUMBER,
280 p_criteria_id IN NUMBER,
281 p_old_freq_num IN NUMBER,
282 p_old_freq_denom IN NUMBER,
283 p_new_freq_num IN NUMBER,
284 p_new_freq_denom IN NUMBER,
285 p_txn IN NUMBER);
286
287 --
288 -- The procedure has autonomous transaction pragma.
289 -- It inserts error message into error log table
290 --
291 PROCEDURE INSERT_ERROR_LOG (
292 p_module_name IN VARCHAR2,
293 p_error_message IN VARCHAR2 DEFAULT NULL,
294 p_comments IN VARCHAR2 DEFAULT NULL);
295
296 FUNCTION GET_LOT_ID RETURN NUMBER;
297
298 --
299 -- The function returns fnd_api.g_true when
300 -- next rule have frequency numerator 0,
301 -- fnd_api.g_false otherwise
302 --
303 FUNCTION INSPECT_ZERO (
304 p_plan_state IN plan_state_rec,
305 p_txn IN NUMBER DEFAULT NULL) RETURN VARCHAR2;
306
307 FUNCTION GET_PROCESS_ID (
308 p_process_plan_id IN NUMBER) RETURN NUMBER;
309
310 END QA_SKIPLOT_UTILITY;
311
[Home] [Help]