1 PACKAGE BOM_REVISIONS AS
2 /* $Header: BOMREVSS.pls 120.1 2005/06/21 02:59:14 appldev ship $ */
3
4 /* ------------------------------- GET_REVISION ---- ------------------------
5 NAME
6 GET_REVISION - retrieve item/routing revision for a date
7 DESCRIPTION
8 retrieve teh current revision for teh given date
9
10 REQUIRES
11 type "PART" - item revision
12 "PROCESS" - routing revision
13 eco_status "ALL" - all ECOs
14 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
15 with HOLD status
16 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
17 with HOLD or OPEN status
18 examine_type "ALL" - all revisions
19 "IMPL_ONLY" - only implemented revisions
20 "PEND_ONLY" - only unimplemented revisions
21 org_id organization id
22 item_id item id
23
24 OUTPUT
25 itm_rev revision
26 RETURNS
27
28 NOTES
29 ---------------------------------------------------------------------------*/
30 PROCEDURE GET_REVISION(
31 type IN VARCHAR2 DEFAULT 'PART',
32 eco_status IN VARCHAR2 DEFAULT 'ALL',
33 examine_type IN VARCHAR2 DEFAULT 'ALL',
34 org_id IN NUMBER,
35 item_id IN NUMBER,
36 rev_date IN DATE,
37 itm_rev IN OUT NOCOPY VARCHAR2
38 );
39
40 /* ------------------------------ GET_ITEM_REVISION_FN --------------------------
41 NAME
42 GET_ITEM_REVISION_FN - retrieve item revision for a date , if no revision defined, return null instead
43 DESCRIPTION
44 retrieve teh current revision for teh given date
45
46 REQUIRES
47 type "PART" - item revision
48 "PROCESS" - routing revision
49 eco_status "ALL" - all ECOs
50 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
51 with HOLD status
52 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
53 with HOLD or OPEN status
54 examine_type "ALL" - all revisions
55 "IMPL_ONLY" - only implemented revisions
56 "PEND_ONLY" - only unimplemented revisions
57 org_id organization id
58 item_id item id
59 rev_date date for which revision desired
60 OUTPUT
61 itm_rev revision
62 RETURNS
63
64 NOTES
65 ---------------------------------------------------------------------------*/
66 FUNCTION GET_ITEM_REVISION_FN(
67 eco_status IN VARCHAR2 DEFAULT 'ALL',
68 examine_type IN VARCHAR2 DEFAULT 'ALL',
69 org_id IN NUMBER,
70 item_id IN NUMBER,
71 rev_date IN DATE
72 ) RETURN VARCHAR2;
73 PRAGMA RESTRICT_REFERENCES(GET_ITEM_REVISION_FN,WNDS,WNPS);
74
75 /* ------------------------------ GET_ITEM_REVISION_ID_FN --------------------------
76 NAME
77 GET_ITEM_REVISION_ID_FN - retrieve item revision for a date , if no revision defined, return null instead
78 DESCRIPTION
79 retrieve teh current revision for teh given date
80
81 REQUIRES
82 type "PART" - item revision
83 "PROCESS" - routing revision
84 eco_status "ALL" - all ECOs
85 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
86 with HOLD status
87 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
88 with HOLD or OPEN status
89 examine_type "ALL" - all revisions
90 "IMPL_ONLY" - only implemented revisions
91 "PEND_ONLY" - only unimplemented revisions
92 org_id organization id
93 item_id item id
94 rev_date date for which revision desired
95 OUTPUT
96 itm_rev revision
97 RETURNS
98
99 NOTES
100 ---------------------------------------------------------------------------*/
101 FUNCTION GET_ITEM_REVISION_ID_FN(
102 eco_status IN VARCHAR2 DEFAULT 'ALL',
103 examine_type IN VARCHAR2 DEFAULT 'ALL',
104 org_id IN NUMBER,
105 item_id IN NUMBER,
106 rev_date IN DATE
107 ) RETURN NUMBER;
108 PRAGMA RESTRICT_REFERENCES(GET_ITEM_REVISION_ID_FN,WNDS,WNPS);
109
110 /* ------------------------------- COMPARE_REVISION ---------------------------
111 NAME
112 COMPARE_REVISION - compare 2 revisions
113 DESCRIPTION
114 compare 2 revisions
115
116 REQUIRES
117 rev1 revision 1
118 rev2 revision 2
119 OUTPUT
120 RETURNS
121 0 if rev1 = rev2
122 1 if rev1 > rev2
123 2 if rev1 < rev2
124 NOTES
125 ---------------------------------------------------------------------------*/
126 FUNCTION COMPARE_REVISION(
127 rev1 IN VARCHAR2,
128 rev2 IN VARCHAR2
129 ) RETURN INTEGER;
130
131 /* ------------------------------- GET_REV_DATE ---- ------------------------
132 NAME
133 GET_REV_DATE - retrieve date for given revision
134 DESCRIPTION
135 retrieve revision start date for given revision
136
137 REQUIRES
138 type "PART" - item revision
139 "PROCESS" - routing revision
140 org_id organization id
141 item_id item id
142 itm_rev revision
143 OUTPUT
144 rev_date effecitive date of revision
145 RETURNS
146
147 NOTES
148 ---------------------------------------------------------------------------*/
149 PROCEDURE GET_REV_DATE (
150 type IN VARCHAR2 DEFAULT 'PART',
151 org_id IN NUMBER,
152 item_id IN NUMBER,
153 itm_rev IN VARCHAR2,
154 rev_date IN OUT NOCOPY DATE
155 );
156
157 /* ------------------------------- GET_HIGH_DATE ---- ------------------------
158 NAME
159 GET_HIGH_DATE - retreive the high date of the revision
160 DESCRIPTION
161 retrieve the high date of the revision. For the greatest rev, high
162 date is greater of sysdate, effective_date for the revision
163
164 REQUIRES
165 type "PART" - item revision
166 "PROCESS" - routing revision
167 org_id organization id
168 item_id item id
169 eco_status "ALL" - all ECOs
170 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
171 with HOLD status
172 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
173 with HOLD or OPEN status
174 OUTPUT
175 itm_rev revision
176 rev_date high date
177 RETURNS
178
179 NOTES
180 ---------------------------------------------------------------------------*/
181 PROCEDURE GET_HIGH_DATE (
182 type IN VARCHAR2 DEFAULT 'PART',
183 org_id IN NUMBER,
184 item_id IN NUMBER,
185 eco_status IN VARCHAR2,
186 itm_rev IN VARCHAR2,
187 rev_date IN OUT NOCOPY DATE
188 );
189
190 /* ------------------------------ GET_ITEM_REVISION_LABEL_FN --------------------------
191 NAME
192 GET_ITEM_REVISION_LABEL_FN - retrieve item revision for a date
193 DESCRIPTION
194 retrieve teh current revision for teh given date
195
196 REQUIRES
197 type "PART" - item revision
198 "PROCESS" - routing revision
199 eco_status "ALL" - all ECOs
200 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
201 with HOLD status
202 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
203 with HOLD or OPEN status
204 "EXCLUDE_ALL" - Exclude all revisions except the Implemented
205 examine_type "ALL" - all revisions
206 "IMPL_ONLY" - only implemented revisions
207 "PEND_ONLY" - only unimplemented revisions
208 org_id organization id
209 item_id item id
210 rev_date date for which revision desired
211 OUTPUT
212 itm_rev_label revision label
213 RETURNS
214
215 NOTES
216 ---------------------------------------------------------------------------*/
217
218
219
220 FUNCTION GET_ITEM_REVISION_LABEL_FN(
221 eco_status IN VARCHAR2 DEFAULT 'ALL',
222 examine_type IN VARCHAR2 DEFAULT 'ALL',
223 org_id IN NUMBER,
224 item_id IN NUMBER,
225 rev_date IN DATE
226 ) RETURN VARCHAR2;
227 PRAGMA RESTRICT_REFERENCES(GET_ITEM_REVISION_LABEL_FN,WNDS,WNPS);
228
229
230
231 /* ------------------------------ GET_REVISION_DETAILS --------------------------
232 NAME
233 GET_REVISION_DETAILS - retrieve item revision for a date
234 DESCRIPTION
235 retrieve teh current revision for teh given date
236
237 REQUIRES
238 type "PART" - item revision
239 "PROCESS" - routing revision
240 eco_status "ALL" - all ECOs
241 "EXCLUDE_HOLD" - exclude pending revisions from ECOs
242 with HOLD status
243 "EXCLUDE_OPEN_HOLD" - exclude pending revisions from ECOs
244 with HOLD or OPEN status
245 "EXCLUDE_ALL" - Exclude all revisions except the Implemented
246 examine_type "ALL" - all revisions
247 "IMPL_ONLY" - only implemented revisions
248 "PEND_ONLY" - only unimplemented revisions
249 org_id organization id
250 item_id item id
251 rev_date date for which revision desired
252 OUTPUT
253 itm_rev revision
254 itm_rev_label revision label
255 itm_rev_id revision id
256 RETURNS
257
258 NOTES
259 ---------------------------------------------------------------------------*/
260
261 PROCEDURE GET_REVISION_DETAILS(
262 eco_status IN VARCHAR2 DEFAULT 'ALL',
263 examine_type IN VARCHAR2 DEFAULT 'ALL',
264 org_id IN NUMBER,
265 item_id IN NUMBER,
266 rev_date IN DATE,
267 itm_rev IN OUT NOCOPY VARCHAR2,
268 itm_rev_label IN OUT NOCOPY VARCHAR2,
269 itm_rev_id IN OUT NOCOPY NUMBER
270 );
271 PRAGMA RESTRICT_REFERENCES(GET_REVISION_DETAILS,WNDS,WNPS);
272
273
274 /* ---------------------------- GET_HIGH_REV_DATE ---- ------------------------
275 NAME
276 GET_HIGH_REV_DATE - retrieve highest rev and its high date
277 DESCRIPTION
278 retrievehighest revsion adn its high date
279
280 REQUIRES
281 type "PART" - item revision
282 "PROCESS" - routing revision
283 examine_type "ALL" - all revisions
284 "IMPL_ONLY" - only implemented revisions
285 "PEND_ONLY" - only unimplemented revisions
286 org_id organization id
287 item_id item id
288 OUTPUT
289 rev_date high date for revision
290 itm_rev highest revision
291 RETURNS
292
293 NOTES
294 ---------------------------------------------------------------------------*/
295 PROCEDURE GET_HIGH_REV_DATE(
296 type IN VARCHAR2 DEFAULT 'PART',
297 examine_type IN VARCHAR2 DEFAULT 'ALL',
298 org_id IN NUMBER,
299 item_id IN NUMBER,
300 rev_date IN OUT NOCOPY DATE,
301 itm_rev IN OUT NOCOPY VARCHAR2
302 );
303
304 FUNCTION GET_ITEM_REV_HIGHDATE(
305 p_revision_id IN NUMBER) RETURN DATE;
306
307 PRAGMA RESTRICT_REFERENCES(GET_ITEM_REV_HIGHDATE,WNDS,WNPS);
308
309 END BOM_REVISIONS;