1 package bompexpl as
2 /* $Header: BOMEXPLS.pls 120.2.12010000.1 2008/07/24 17:14:50 appldev ship $ */
3 /*#
4 * This API contains methods to explode BOM based on the module passed.The possible values for module are
5 * 1-Costing 2-BOM 3-Order Entry and 4-ATO and 5-WSM(When the calling application is WSM then the process will explode subassemblies that are phantom).
6 * If the module is BOM then exploder in this package is called or if the module
7 * is Costing or ATO then Costing Exploder is called.The user should first call the exploder_userexit.This method will check the
8 * verify_flag.It will also check the validity of explosion date and if explosion date is null it will default to sysdate.Then based
9 * on module option it will call the appropriate exploder method.The parameters used in methods in this API are described below.<BR>
10 *
11 * -----------------
12 * Parameters
13 * -----------------
14 *<pre>
15 * org_id -- Organization Id
16 * order_by -- 1->order by Op seq, Item Seq and 2->Item seq, Op Seq
17 * grp_id -- Unique value to identify current explosion from sequence bom_explosion_temp_s
18 * session_id -- Unique value to identify current session from sequence bom_explosion_temp_session_s
19 * levels_to_explode -- Level in the Structure to which explosion should be done
20 * bom_or_eng -- 1->BOM and 2->ENG
21 * impl_flag -- 1->Implemented Only and 2->Both implemented and unimplemented
22 * explode_option -- Components to be included in explosion 1->All 2->Current 3->Current and Future
23 * module -- Module value 1->Costing 2->BOM 3->Order Entry 4->ATO and 5->WSM(only phantom subassembly)
24 * cst_type_id -- Cost Type Id for costed explosion
25 * std_comp_flag -- 1->Explode only standard components 2->Explode all components
26 * expl_qty -- The quantity of top assembly that we want to explode
27 * item_id -- Item Id of Assembly to explode
28 * list_id -- Unique id for lists in bom_lists for range
29 * report_option -- 1->Cost rollup with report 2->Cost rollup no report 3->Temp cost rollup with report
30 * cst_rlp_id -- Rollup Id
31 * req_id -- Request Id
32 * prgm_appl_id -- Program Application Id
33 * prg_id -- Program Id
34 * user_id -- User Id
35 * lock_flag -- 1->Do not lock the table 2->Lock the table
36 * alt_rtg_desg -- Alternate Routing Designator
37 * rollup_option -- 1->Single Level Rollup 2->Full Rollup
38 * plan_factor_flag1 -- 1-> Yes 2-> No
39 * incl_lt_flag -- 1-> Yes 2-> No
40 * alt_desg -- Alternate Bom Designator
41 * rev_date -- Explosion Date (dd-mon-yy hh24:mi)
42 * comp_code -- Concatenated Component Code lpad 16
43 * Err_Msg -- Error Message Out Buffer
44 * error_code -- Error Code Out (returns sql error code if sql error, 9999 if loop detected)
45 * </pre>
46 * @rep:scope public
47 * @rep:product BOM
48 * @rep:displayname Structure Exploder
49 * @rep:lifecycle active
50 * @rep:category BUSINESS_ENTITY BOM_BILL_OF_MATERIAL
51 */
52 /*==========================================================================+
53 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
54 | All rights reserved. |
55 +===========================================================================+
56 | |
57 | File Name : BOMPEXPL.sql |
58 | DESCRIPTION : This file is a packaged procedure for the exploders.
59 | This package contains 3 different exploders for the
60 | modules it can be called from. The procedure exploders
61 | calls the correct exploder based on the module option.
62 | Each of the 3 exploders can be called on directly too.
63 | Parameters: org_id organization_id
64 | order_by 1 - Op seq, item seq
65 | 2 - Item seq, op seq
66 | grp_id unique value to identify current explosion
67 | use value from sequence bom_explosion_temp_s
68 | session_id unique value to identify current session
69 | use value from bom_explosion_temp_session_s
70 | levels_to_explode
71 | bom_or_eng 1 - BOM
72 | 2 - ENG
73 | impl_flag 1 - implemented only
74 | 2 - both impl and unimpl
75 | explode_option 1 - All
76 | 2 - Current
77 | 3 - Current and future
78 | module 1 - Costing
79 | 2 - Bom
80 | 3 - Order entry
81 4 - ATO
82 | cst_type_id cost type id for costed explosion
83 | std_comp_flag 1 - explode only standard components
84 | 2 - all components
85 | expl_qty explosion quantity
86 | item_id item id of asembly to explode
87 | list_id unique id for lists in bom_lists for range
88 | report_option 1 - cost rollup with report
89 | 2 - cost rollup no report
90 | 3 - temp cost rollup with report
91 | cst_rlp_id rollup_id
92 | req_id request id
93 | prgm_appl_id program application id
94 | prg_id program id
95 | user_id user id
96 | lock_flag 1 - do not lock the table
97 | 2 - lock the table
98 | alt_rtg_desg alternate routing designator
99 | rollup_option 1 - single level rollup
100 | 2 - full rollup
101 | plan_factor_flag1 - Yes
102 | 2 - No
103 | incl_lt_flag 1 - Yes
104 | 2 - No
105 | alt_desg alternate bom designator
106 | rev_date explosion date dd-mon-yy hh24:mi
107 | comp_code concatenated component code lpad 16
108 | err_msg error message out buffer
109 | error_code error code out. returns sql error code
110 | if sql error, 9999 if loop detected.
111 | Revision
112 Shreyas Shah creation
113 02/10/94 Shreyas Shah added multi-org capability from bom_lists
114 max_bom_levels of all orgs for multi-org
115 03/24/94 Shreyas Shah added 4 to module parameter so that
116 if ATO calls it dont commit but if CST
117 calls it then commit data
118 10/19/95 Robert Yee Added lead time flags
119 06-10-2003 Sreejith Nelloliyil Added arg_expl_type for PLM
120 | 01-may-2004 Vani Hymavathi Added a new procedure exploder for PDI
121 | |
122 +==========================================================================*/
123
124 /*#
125 * This method will check the module value passed and will call the exploder procedure based on the
126 * module value.The possible values for module are 1-Costing,2-BOM,3-Order Entry,4-ATO and 5-WSM. When the calling application is WSM then the process will explode subassemblies that are phantom.
127 * It checks the verify_flag to see whether parameters need to be validated.The default value for verify_flag is 0 (TRUE).This method also checks the
128 * explosion date and if the explosion date is null its defaulted to sysdate.
129 * @param verify_flag IN This flag indicates whether the parameters have to be validated or not,depending
130 * on the module
131 * @param org_id IN Organization id
132 * @param order_by IN Pass 1 for (Op seq, item seq) and 2 for (Item seq, op seq)
133 * @param grp_id IN unique value to identify current explosion use value from sequence
134 * bom_explosion_temp_s
135 * @param session_id IN unique value to identify current session use value from
136 * bom_explosion_temp_session_s
137 * @param levels_to_explode IN Levels to explode
138 * @param bom_or_eng IN 1-BOM, 2-ENG, default is 1
139 * @param impl_flag IN 1 - implemented only , 2 - both impl and unimpl
140 * @param plan_factor_flag IN 1 - Yes, 2 - No
141 * @param explode_option IN 1 - All, 2 - Current, 3 - Current and future
142 * @param module IN 1 - Costing, 2 - Bom, 3 - Order entry, 4 - ATO
143 * @param cst_type_id IN cost type id for costed explosion
144 * @param std_comp_flag IN 1 - explode only standard components, 2 - all components
145 * @param expl_qty IN explosion quantity
146 * @param item_id IN item id of assembly to explode
147 * @param alt_desg IN alternate bom designator
148 * @param comp_code IN concatenated component code lpad 16
149 * @param rev_date IN explosion date dd-mon-yy hh24:mi
150 * @param unit_number IN unit number for which the explosion should be done (for unit effective boms)
151 * @param err_msg OUT error message out buffer
152 * @param error_code OUT error code out. returns sql error code if sql error, 9999 if loop detected
153 * @rep:scope public
154 * @rep:lifecycle active
155 * @rep:displayname Exploder userexit
156 */
157
158 PROCEDURE exploder_userexit (
159 verify_flag IN NUMBER DEFAULT 0,
160 org_id IN NUMBER,
161 order_by IN NUMBER DEFAULT 1,
162 grp_id IN NUMBER,
163 session_id IN NUMBER DEFAULT 0,
164 levels_to_explode IN NUMBER DEFAULT 1,
165 bom_or_eng IN NUMBER DEFAULT 1,
166 impl_flag IN NUMBER DEFAULT 1,
167 plan_factor_flag IN NUMBER DEFAULT 2,
168 explode_option IN NUMBER DEFAULT 2,
169 module IN NUMBER DEFAULT 2,
170 cst_type_id IN NUMBER DEFAULT 0,
171 std_comp_flag IN NUMBER DEFAULT 0,
172 expl_qty IN NUMBER DEFAULT 1,
173 item_id IN NUMBER,
174 alt_desg IN VARCHAR2 DEFAULT '',
175 comp_code IN VARCHAR2 DEFAULT '',
176 rev_date IN VARCHAR2,
177 unit_number IN VARCHAR2 DEFAULT '',
178 release_option IN NUMBER DEFAULT 0,
179 err_msg OUT NOCOPY VARCHAR2,
180 error_code OUT NOCOPY NUMBER
181 );
182
183 /*#
184 * This method will do an explosion and then a single level or full cost rollup depending upon the value of rollup_option parameter. This
185 * method will first insert explosion data into BOM_EXPLOSION_TEMP table and then
186 * wll call the explode procedure.This method will check the module value passed and will call the exploder procedure based on the
187 * module value.The possible values for module are 1-Costing,2-BOM,3-Order Entry,4-ATO and 5-WSM(When the calling application is
188 * WSM then the process will explode subassemblies that are phantom)
189 * It checks the verify_flag to see whether parameters need to be validated.The default value for verify_flag is 0 (TRUE).This method also checks the
190 * explosion date and if the explosion date is null its defaulted to sysdate.
191 * @param verify_flag This flag indicates whether the parameters have to be validated or not,depending
192 * on the module
193 * @param org_id IN Organization id
194 * @param order_by IN Pass 1 for (Op seq, item seq) and 2 for (Item seq, op seq)
195 * @param list_id IN unique id for lists in bom_lists for range
196 * @param grp_id IN unique value to identify current explosion use value from sequence
197 * bom_explosion_temp_s
198 * @param session_id IN unique value to identify current session use value from
199 * bom_explosion_temp_session_s
200 * @param levels_to_explode IN Levels to explode
201 * @param bom_or_eng IN 1-BOM, 2-ENG, default is 1
202 * @param impl_flag IN 1 - implemented only , 2 - both impl and unimpl
203 * @param plan_factor_flag IN 1 - Yes, 2 - No
204 * @param incl_lt_flag IN 1 - Yes, 2 - No
205 * @param explode_option IN 1 - All, 2 - Current, 3 - Current and future
206 * @param module IN 1 - Costing, 2 - Bom, 3 - Order entry, 4 - ATO
207 * @param cst_type_id IN cost type id for costed explosion
208 * @param std_comp_flag IN 1 - explode only standard components, 2 - all components
209 * @param expl_qty IN explosion quantity
210 * @param report_option IN 1 - cost rollup with report, 2 - cost rollup no report
211 * 3 - temp cost rollup with report
212 * @param req_id IN request id
213 * @param cst_rlp_id IN rollup_id
214 * @param lock_flag IN 1 - do not lock the table, 2 - lock the table
215 * @param rollup_option IN 1 - single level rollup, 2 - full rollup
216 * @param alt_rtg_desg IN alternate routing designator
217 * @param alt_desg IN alternate bom designator
218 * @param rev_date IN explosion date dd-mon-yy hh24:mi
219 * @param err_msg OUT error message out buffer
220 * @param error_code OUT error code out.Returns sql error code if sql error, 9999 if loop detected
221 * @rep:scope public
222 * @rep:lifecycle active
223 * @rep:displayname Explosion Report
224 */
225 PROCEDURE explosion_report(
226 verify_flag IN NUMBER DEFAULT 0,
227 org_id IN NUMBER,
228 order_by IN NUMBER DEFAULT 1,
229 list_id IN NUMBER,
230 grp_id IN NUMBER,
231 session_id IN NUMBER DEFAULT 0,
232 levels_to_explode IN NUMBER DEFAULT 1,
233 bom_or_eng IN NUMBER DEFAULT 1,
234 impl_flag IN NUMBER DEFAULT 1,
235 plan_factor_flag IN NUMBER DEFAULT 2,
236 incl_lt_flag IN NUMBER DEFAULT 2,
237 explode_option IN NUMBER DEFAULT 2,
238 module IN NUMBER DEFAULT 2,
239 cst_type_id IN NUMBER DEFAULT 0,
240 std_comp_flag IN NUMBER DEFAULT 0,
241 expl_qty IN NUMBER DEFAULT 1,
242 report_option IN NUMBER DEFAULT 0,
243 req_id IN NUMBER DEFAULT 0,
244 cst_rlp_id IN NUMBER DEFAULT 0,
245 lock_flag IN NUMBER DEFAULT 2,
246 rollup_option IN NUMBER DEFAULT 2,
247 alt_rtg_desg IN VARCHAR2 DEFAULT '',
248 alt_desg IN VARCHAR2 DEFAULT '',
249 rev_date IN VARCHAR2,
250 err_msg OUT NOCOPY VARCHAR2,
251 error_code OUT NOCOPY NUMBER
252 );
253
254
255 /*#
256 * Explode BOM Method for PDI usage.This exploder will populate trimmed date in the explosion table.
257 * @param verify_flag This flag indicates whether the parameters have to be validated or not,depending
258 * on the module
259 * @param org_id IN Organization id
260 * @param order_by IN Pass 1 for (Op seq, item seq) and 2 for (Item seq, op seq)
261 * @param grp_id IN unique value to identify current explosion use value from sequence
262 * bom_explosion_temp_s
263 * @param session_id IN unique value to identify current session use value from
264 * bom_explosion_temp_session_s
265 * @param levels_to_explode IN Levels to explode
266 * @param bom_or_eng IN 1-BOM, 2-ENG, default is 1
267 * @param impl_flag IN 1 - implemented only , 2 - both impl and unimpl
268 * @param plan_factor_flag IN 1 - Yes, 2 - No
269 * @param explode_option IN 1 - All, 2 - Current, 3 - Current and future
270 * @param module IN 1 - Costing, 2 - Bom, 3 - Order entry, 4 - ATO
271 * @param cst_type_id IN cost type id for costed explosion
272 * @param std_comp_flag IN 1 - explode only standard components, 2 - all components
273 * @param expl_qty IN explosion quantity
274 * @param item_id IN item id of assembly to explode
275 * @param alt_desg IN alternate bom designator
276 * @param comp_code IN concatenated component code lpad 16
277 * @param rev_date IN explosion date dd-mon-yy hh24:mi
278 * @param unit_number IN unit number for which the explosion should be done (for unit effective boms)
279 * @param err_msg OUT error message out buffer
280 * @param error_code OUT error code out. returns sql error code if sql error, 9999 if loop detected
281 * @rep:scope public
282 * @rep:lifecycle active
283 * @rep:displayname Exploder for PDI usage
284 */
285
286 PROCEDURE explode(
287 verify_flag IN NUMBER DEFAULT 0,
288 org_id IN NUMBER,
289 order_by IN NUMBER DEFAULT 1,
290 grp_id IN NUMBER,
291 session_id IN NUMBER DEFAULT 0,
292 levels_to_explode IN NUMBER DEFAULT 1,
293 bom_or_eng IN NUMBER DEFAULT 1,
294 impl_flag IN NUMBER DEFAULT 1,
295 plan_factor_flag IN NUMBER DEFAULT 2,
296 explode_option IN NUMBER DEFAULT 1,
297 module IN NUMBER DEFAULT 2,
298 cst_type_id IN NUMBER DEFAULT 0,
299 std_comp_flag IN NUMBER DEFAULT 0,
300 expl_qty IN NUMBER DEFAULT 1,
301 item_id IN NUMBER,
305 unit_number IN VARCHAR2 DEFAULT '',
302 alt_desg IN VARCHAR2 DEFAULT '',
303 comp_code IN VARCHAR2 DEFAULT '',
304 rev_date IN VARCHAR2,
306 err_msg OUT NOCOPY VARCHAR2,
307 error_code OUT NOCOPY NUMBER
308 );
309 END bompexpl;
[Home] [Help]