1 package BOMPXINQ AUTHID CURRENT_USER as
2 /* $Header: BOMXINQS.pls 120.2 2008/05/21 11:54:54 rajaiswa ship $ */
3 /*#
4 * This API contains methods to Explode BOM and Export the data to PL/SQL tables.This method
5 * exports bill of material data for a particular assembly, in all subordinate organizations in a specified
6 * organization hierarchy. The number of levels to which a BOM is exploded for a
7 * particular organization depends on the Max Bill Levels field setting in the
8 * Organization Parameters form. If this value is greater than or equal to the levels of
9 * the bill being exported, then that bill will be exploded to the lowest level
10 * @rep:scope public
11 * @rep:product BOM
12 * @rep:displayname Structure Export
13 * @rep:lifecycle active
14 * @rep:category BUSINESS_ENTITY BOM_BILL_OF_MATERIAL
15 */
16 /*==========================================================================+
17 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
18 | All rights reserved. |
19 +===========================================================================+
20 | |
21 | File Name : BOMXINQS.sql |
22 | DESCRIPTION : This file is a packaged procedure for the exploders.
23 | This package contains 3 different exploders for the
24 | modules it can be called from. The procedure exploders
25 | calls the correct exploder based on the module option.
26 | Each of the 3 exploders can be called on directly too.
27 |
28 | Revision
29 | Shreyas Shah creation
30 | 02/10/94 Shreyas Shah added multi-org capability from bom_lists
31 | max_bom_levels of all orgs for multi-org
32 | 08/03/95 Rob Yee added parameters for 10SG
33 | 09/01/00 Syed Musanna Added the BOM EXPORT utility for multiorg
34 | |
35 +==========================================================================*/
36
37 --===================
38 -- GLOBAL VARIABLES
39 --===================
40 -- Table type used to hold exploded BOM
41 TYPE bomexporttabtype IS TABLE OF bom_small_expl_temp%ROWTYPE
42 INDEX BY BINARY_INTEGER;
43
44 --========================================================================
45 -- PROCEDURE : Export_BOM
46 -- PARAMETERS : Org_hierarchy_name IN VARCHAR2 Organization Hierarchy
47 -- Name
48 -- Assembly_item_name IN VARCHAR2 Assembly item name
49 -- Organization_code IN VARCHAR2 Organization code
50 -- Alternate_bm_designator IN VARCHAR2 Alternate bom designator
51 -- Costs IN NUMBER Cost flag
52 -- Cost_type_id IN NUMBER Cost type id
53 -- P_bom_header_tbl OUT
54 -- p_bom_revisions_tbl OUT
55 -- p_bom_components_tbl OUT
56 -- p_bom_ref_designators_tbl OUT
57 -- p_bom_sub_components_tbl OUT
58 -- p_bom_comp_ops_tbl OUT
59 -- Err_Msg OUT NOCOPY VARCHAR2 Error Message
60 -- Error_Code OUT NOCOPY NUMBER Error Megssage
61 --
62 -- COMMENT : API Accepts the name of an hierarchy, Assembly item name,
63 -- Organization code, Alternate bom designator, Costs,
64 -- Cost type id. It returns the following six pl/sql tables:
65 -- 1. P_bom_header_tbl ,
66 -- 2. p_bom_revisions_tbl,
67 -- 3. p_bom_components_tbl,
68 -- 4. p_bom_ref_designators_tbl,
69 -- 5. p_bom_sub_components_tbl,
70 -- 6. p_bom_comp_ops_tbl
71 -- p_bom_header_tbl consists of all bom header records. p_bom_revisions_tbl
72 -- consists of all revisions for an assembly item withina bom.
73 -- p_bom_components_tbl consists of all components of a bom.
74 -- p_bom_ref_designators_tbl consists of the reference designators for each
75 -- of the components within a bom. p_bom_sub_components_tbl consits of
76 -- substitute components for each of the components within a bom.
77 -- p_bom_comp_ops_tbl consists of component operations for each of the
78 -- components within a bom. Error Code and corresponding Error
79 -- mesages are returned in case of an error
80 --
81 --
82 --========================================================================
83
84 /*#
85 * The Bill of Material Export method provides the ability to export bill of material data
86 * for a particular assembly, in all subordinate organizations in a specified
87 * organization hierarchy. The number of levels to which a BOM is exploded for a
88 * particular organization depends on the Max Bill Levels field setting in the
89 * Organization Parameters form. If this value is greater than or equal to the levels of
90 * the bill being exported, then that bill will be exploded to the lowest level
91 * @param P_org_hierarchy_name IN The name of the organization hierarchy to which all subordinate
92 * organizations will receive the exported bill of material data
93 * @param P_assembly_item_name IN Assembly item name
94 * @param P_organization_code IN Organization code
95 * @param P_alternate_bm_designator IN The alternate bill defined for this primary bill
96 * @param P_Costs IN Pass parameter as 1, if cost details need to be exported. Pass the appropriate
97 * P_Cost_type_id for that item and organization combination. If the parameter is passed as 2, then pass
98 * P_Cost_type_id as having zero value. If this parameter is passed as NULL or, then it will take the
99 * default value of 2
100 * @param P_Cost_type_id IN Pass the appropriate cost_type_id for that item and organization combination.
101 * This works in conjunction with the P_Costs parameter
102 * @param X_bom_header_tbl OUT consists of all bom header records
103 * @rep:paraminfo { @rep:innertype BOM_BO_PUB.BOM_HEADER_TBL_TYPE }
104 * @param X_bom_revisions_tbl OUT consists of all revisions for an assembly item within a bom
105 * @rep:paraminfo { @rep:innertype BOM_BO_PUB.BOM_REVISION_TBL_TYPE }
106 * @param X_bom_components_tbl OUT consists of all components of a bom
107 * @rep:paraminfo { @rep:innertype BOM_BO_PUB.BOM_COMPS_TBL_TYPE }
108 * @param X_bom_ref_designators_tbl OUT consists of the reference designators for each of the
109 * components within a bom
110 * @rep:paraminfo { @rep:innertype BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE }
111 * @param X_bom_sub_components_tbl OUT consits of substitute components for each of the components
112 * within a bom
113 * @rep:paraminfo { @rep:innertype BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE }
114 * @param X_bom_comp_ops_tbl OUT consists of component operations for each of the components within a bom
115 * @rep:paraminfo { @rep:innertype BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE }
116 * @param X_Err_Msg OUT Error Messages
117 * @param X_Error_Code OUT Error Codes.0 indicates success.
118 * 9998 indicates Bill exceeds the maximum number of levels defined for that organization
119 * You need to reduce the number of levels of the bill, or increase the maximum number of levels
120 * allowed for a bill in that organization
121 * SQLCODE indicates Oracle database related errors
122 * @rep:scope public
123 * @rep:lifecycle active
124 * @rep:displayname Export BOM
125 */
126
127 PROCEDURE Export_BOM(P_org_hierarchy_name IN VARCHAR2 DEFAULT NULL,
128 P_assembly_item_name IN VARCHAR2,
129 P_organization_code IN VARCHAR2,
130 P_alternate_bm_designator IN VARCHAR2 DEFAULT NULL,
131 P_Costs IN NUMBER DEFAULT 2,
132 P_Cost_type_id IN NUMBER DEFAULT 0,
133 X_bom_header_tbl OUT NOCOPY BOM_BO_PUB.BOM_HEADER_TBL_TYPE,
134 X_bom_revisions_tbl OUT NOCOPY BOM_BO_PUB.BOM_REVISION_TBL_TYPE,
135 X_bom_components_tbl OUT NOCOPY BOM_BO_PUB.BOM_COMPS_TBL_TYPE,
136 X_bom_ref_designators_tbl OUT NOCOPY BOM_BO_PUB.BOM_REF_DESIGNATOR_TBL_TYPE,
137 X_bom_sub_components_tbl OUT NOCOPY BOM_BO_PUB.BOM_SUB_COMPONENT_TBL_TYPE,
138 X_bom_comp_ops_tbl OUT NOCOPY BOM_BO_PUB.BOM_COMP_OPS_TBL_TYPE,
139 X_Err_Msg OUT NOCOPY VARCHAR2,
140 X_Error_Code OUT NOCOPY NUMBER);
141
142
143
144
145
146
147 --========================================================================
148 -- PROCEDURE : Export_BOM
149 -- PARAMETERS : Profile_id IN NUMBER Security Profile Id
150 -- Org_hierarchy_name IN VARCHAR2 Organization Hierarchy
151 -- Name
152 -- Assembly_item_id IN NUMBER Assembly item id
153 -- Organization_id IN NUMBER Organization id
154 -- Alternate_bm_designator IN VARCHAR2 Alternate bom designator
155 -- Costs IN NUMBER Cost flag
156 -- Cost_type_id IN NUMBER Cost type id
157 -- bom_export_tab OUT bomexporttabtype export table
158 -- Err_Msg OUT VARCHAR2 Error Message
159 -- Error_Code OUT NUMBER Error Megssage
160 --
161 -- COMMENT : API Accepts the security profile id,name of an hierarchy,
162 -- Assembly item id, Organization id, Alternate bom designator,
163 -- Costs, Cost type id and returns bom_export_tab PL/SQL table
164 -- consists of exploded BOM for all the organizations under
165 -- the hierarchy name. Error Code and corresponding Error
166 -- mesages are returned in case of an error
167 --
168 --
169 --=======================================================================
170
171 /*#
172 * The Bill of Material Export method provides the ability to export bill of material data
173 * for a particular assembly, in all subordinate organizations in a specified
174 * organization hierarchy. The number of levels to which a BOM is exploded for a
175 * particular organization depends on the Max Bill Levels field setting in the
176 * Organization Parameters form. If this value is greater than or equal to the levels of
177 * the bill being exported, then that bill will be exploded to the lowest level
178 * @param Profile_id IN Security Profile Id
179 * @param Org_hierarchy_name IN The name of the organization hierarchy to which all subordinate
180 * organizations will receive the exported bill of material data
181 * @param Assembly_item_id IN Must be the inventory_item_id of the bill, and must exist in the
182 * mtl_system_items table for that organization. This item must exist in all subordinate organizations
183 * under the hierarchy origin
184 * @param Organization_id IN Uniquely identifies a bill which will be exploded with the bill details in
185 * the bom_export_tab, PL/SQL table
186 * @param Alternate_bm_designator IN The alternate bill defined for this primary bill. This can be passed
187 * as NULL or if there are no alternatives defined. It uniquely identifies a bill which will be exploded
188 * with the bill details in the bom_export_tab, PL/SQL table
189 * @param Costs IN Pass parameter as 1, if cost details need to be exported. Pass the appropriate
190 * cost_type_id for that item and organization combination. If the parameter is passed as 2, then pass
191 * cost_type_id as having zero value. If this parameter is passed as NULL or, then it will take the
192 * default value of 2
193 * @param Cost_type_id IN Pass the appropriate cost_type_id for that item and organization combination.
194 * This works in conjunction with the Costs parameter
195 * @param bom_export_tab OUT PL/SQL table containing the exploded bill of material information. This
196 * information can be inserted into a custom table, written to a text file, or passed to
197 * host arrays (Oracle Call Interface). Error_Code should have a value of zero and Err_Msg
198 * should be NULL, before inserting the date into a custom table
199 * PL/SQL Output Table (BOM_EXPORT_TAB) Columns
200 * TOP_BILL_SEQUENCE_ID,BILL_SEQUENCE_ID,COMMON_BILL_SEQUENCE_ID,ORGANIZATION_ID,COMPONENT_SEQUENCE_ID,
201 * COMPONENT_ITEM_ID,COMPONENT_QUANTITY,PLAN_LEVEL,EXTENDED_QUANTITY,SORT_ORDER,GROUP_ID,
202 * TOP_ALTERNATE_DESIGNATOR,COMPONENT_YIELD_FACTOR,TOP_ITEM_ID,COMPONENT_CODE,INCLUDE_IN_ROLLUP_FLAG,
203 * LOOP_FLAG,PLANNING_FACTOR,OPERATION_SEQ_NUM,BOM_ITEM_TYPE,PARENT_BOM_ITEM_TYPE,ASSEMBLY_ITEM_ID,
204 * WIP_SUPPLY_TYPE,ITEM_NUM,EFFECTIVITY_DATE,DISABLE_DATE,IMPLEMENTATION_DATE,OPTIONAL,
205 * SUPPLY_SUBINVENTORY,SUPPLY_LOCATOR_ID,COMPONENT_REMARKS,CHANGE_NOTICE,OPERATION_LEAD_TIME_PERCENT,
206 * MUTUALLY_EXCLUSIVE OPTIONS,CHECK_ATP,REQUIRED_TO_SHIP,REQUIRED_FOR_REVENUE,INCLUDE_ON_SHIP_DOCS,
207 * LOW_QUANTITY,HIGH_QUANTITY,SO_BASIS,OPERATION_OFFSET,CURRENT_REVISION,LOCATOR,
208 * ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8
209 * ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,ITEM_COST
210 * EXTEND_COST_FLAG
211 * This parameter is of type bom_small_expl_temp table
212 * @param Err_Msg OUT Error Messages
213 * @param Error_Code OUT Error Codes .0 indicates success.
214 * 9998 indicates Bill exceeds the maximum number of levels defined for that organization
215 * You need to reduce the number of levels of the bill, or increase the maximum number of levels
216 * allowed for a bill in that organization
217 * SQLCODE indicates Oracle database related errors
218 * @rep:scope private
219 * @rep:lifecycle active
220 * @rep:displayname Export BOM
221 */
222
223
224 PROCEDURE Export_BOM ( Profile_id IN NUMBER ,
225 Org_hierarchy_name IN VARCHAR2,
226 Assembly_item_id IN NUMBER,
227 Organization_id IN NUMBER,
228 Alternate_bm_designator IN VARCHAR2 DEFAULT '',
229 Costs IN NUMBER DEFAULT 2,
230 Cost_type_id IN NUMBER DEFAULT 0,
231 bom_export_tab OUT NOCOPY bomexporttabtype,
232 Err_Msg OUT NOCOPY VARCHAR2,
233 Error_Code OUT NOCOPY NUMBER
234 );
235
236 --========================================================================
237 -- Procedure : exploder_userexit
238 -- Parameters: org_id organization_id
239 -- order_by 1 - Op seq, item seq
240 -- 2 - Item seq, op seq
241 -- grp_id unique value to identify current explosion
242 -- use value from sequence bom_small_expl_temp_s
243 -- session_id unique value to identify current session
244 -- use value from bom_small_expl_temp_session_s
245 -- levels_to_explode
246 -- bom_or_eng 1 - BOM
247 -- 2 - ENG
248 -- impl_flag 1 - implemented only
249 -- 2 - both impl and unimpl
250 -- explode_option 1 - All
251 -- 2 - Current
252 -- 3 - Current and future
253 -- module 1 - Costing
254 -- 2 - Bom
255 -- 3 - Order entry
256 -- cst_type_id cost type id for costed explosion
257 -- std_comp_flag 1 - explode only standard components
258 -- 2 - all components
259 -- expl_qty explosion quantity
260 -- item_id item id of asembly to explode
261 -- list_id unique id for lists in bom_lists for range
262 -- report_option 1 - cost rollup with report
263 -- 2 - cost rollup no report
264 -- 3 - temp cost rollup with report
265 -- cst_rlp_id rollup_id
266 -- req_id request id
267 -- prgm_appl_id program application id
268 -- prg_id program id
269 -- user_id user id
270 -- lock_flag 1 - do not lock the table
271 -- 2 - lock the table
272 -- alt_rtg_desg alternate routing designator
273 -- rollup_option 1 - single level rollup
274 -- 2 - full rollup
275 -- plan_factor_flag1 - Yes
276 -- 2 - No
277 -- alt_desg alternate bom designator
278 -- rev_date explosion date
279 -- comp_code concatenated component code lpad 16
280 -- show_rev 1 - obtain current revision of component
281 -- 2 - don't obtain current revision
282 -- material_ctrl 1 - obtain subinventory locator
283 -- 2 - don't obtain subinventory locator
284 -- lead_time 1 - calculate offset percent
285 -- 2 - don't calculate offset percent
286 -- eff_control 1 - date effectivity
287 -- 2 - serial effectivity
288 -- err_msg error message out buffer
289 -- error_code error code out. returns sql error code
290 -- if sql error, 9999 if loop detected.
291 --========================================================================
292
293
294 PROCEDURE exploder_userexit (
295 verify_flag IN NUMBER DEFAULT 0,
296 org_id IN NUMBER,
297 order_by IN NUMBER DEFAULT 1,
298 grp_id IN NUMBER,
299 session_id IN NUMBER DEFAULT 0,
300 levels_to_explode IN NUMBER DEFAULT 1,
304 explode_option IN NUMBER DEFAULT 2,
301 bom_or_eng IN NUMBER DEFAULT 1,
302 impl_flag IN NUMBER DEFAULT 1,
303 plan_factor_flag IN NUMBER DEFAULT 2,
305 module IN NUMBER DEFAULT 2,
306 cst_type_id IN NUMBER DEFAULT 0,
307 std_comp_flag IN NUMBER DEFAULT 0,
308 expl_qty IN NUMBER DEFAULT 1,
309 item_id IN NUMBER,
310 unit_number_from IN VARCHAR2,
311 unit_number_to IN VARCHAR2,
312 alt_desg IN VARCHAR2 DEFAULT '',
313 comp_code IN VARCHAR2 DEFAULT '',
314 rev_date IN DATE DEFAULT sysdate,
315 show_rev IN NUMBER DEFAULT 2,
316 material_ctrl IN NUMBER DEFAULT 2,
317 lead_time IN NUMBER DEFAULT 2,
318 err_msg OUT NOCOPY VARCHAR2,
319 error_code OUT NOCOPY NUMBER
320 );
321
322 END BOMPXINQ;
[Home] [Help]