1 package BOM_EXPLODER_PUB AUTHID CURRENT_USER as
2 /* $Header: BOMPLMXS.pls 120.5.12010000.3 2009/08/04 12:11:47 gliang ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMPLMXS.sql |
9 | DESCRIPTION : This file is a packaged procedure for the BOM exploders
10 | This package contains 3 different exploders for the
11 | modules it can be called from. The procedure exploders
12 | calls the correct exploder based on the module option.
13 | Each of the 3 exploders can be called on directly too.
14 | Procedure : exploder_userexit
15 | Parameters: org_id organization_id
16 | order_by 1 - Op seq, item seq
17 | 2 - Item seq, op seq
18 | grp_id unique value to identify current explosion
19 | use value from sequence bom_small_expl_temp_s
20 | session_id unique value to identify current session
21 | use value from bom_small_expl_temp_session_s
22 | levels_to_explode
23 | bom_or_eng 1 - BOM
24 | 2 - ENG
25 | impl_flag 1 - implemented only
26 | 2 - both impl and unimpl
27 | explode_option 1 - All
28 | 2 - Current
29 | 3 - Current and future
30 | module 1 - Costing
31 | 2 - Bom
32 | 3 - Order entry
33 | cst_type_id cost type id for costed explosion
34 | std_comp_flag 1 - explode only standard components
35 | 2 - all components
36 | expl_qty explosion quantity
37 | item_id item id of asembly to explode
38 | list_id unique id for lists in bom_lists for range
39 | report_option 1 - cost rollup with report
40 | 2 - cost rollup no report
41 | 3 - temp cost rollup with report
42 | cst_rlp_id rollup_id
43 | req_id request id
44 | prgm_appl_id program application id
45 | prg_id program id
46 | user_id user id
47 | lock_flag 1 - do not lock the table
48 | 2 - lock the table
49 | alt_rtg_desg alternate routing designator
50 | rollup_option 1 - single level rollup
51 | 2 - full rollup
52 | plan_factor_flag1 - Yes
53 | 2 - No
54 | alt_desg alternate bom designator
55 | rev_date explosion date
56 | comp_code concatenated component code lpad 16
57 | show_rev 1 - obtain current revision of component
58 | 2 - don't obtain current revision
59 | material_ctrl 1 - obtain subinventory locator
60 | 2 - don't obtain subinventory locator
61 | lead_time 1 - calculate offset percent
62 | 2 - don't calculate offset percent
63 | err_msg error message out buffer
64 | error_code error code out. returns sql error code
65 | if sql error, 9999 if loop detected.
66 | end_item_revision_id -- End item Revision id
67 | end_item_strc_revision_id -- End item structure revision id
68 | End item structure revision id is mandatory when
69 | the end item revision id is passed
70 | unit_number -- Unit number/Serial number for which the BOM explosion needs to be
71 | performed
72 | object_name -- NULL for inventory items, DDD_CADVIEW for CAD components
73 | pk_value1...pk_value5 -- Primary key columns for the object
74 | For inventory items, pk_value1 = Inventory Item Id and
75 | pk_value2 = Oragnization_id
76 | For CAD Components, pk_value1 = CAD Component id
77 | Revision
78 |
79 | 10-DEC-2003 Refaitheen Farook Initial creation. PLM specific BOM exploder
80 |
81 +==========================================================================*/
82
83 p_top_bill_sequence_id NUMBER;
84 p_explosion_date DATE := SYSDATE;
85 p_expl_end_item_rev NUMBER;
86 p_expl_end_item_rev_code VARCHAR2(3);
87 p_expl_end_item_id NUMBER;
88 p_expl_end_item_org_id NUMBER;
89 p_expl_unit_number VARCHAR2(30);
90 p_explode_option NUMBER;
91 p_group_id NUMBER;
92 p_top_effectivity_control NUMBER;
93
94 TYPE VARCHAR_10_TBL IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
95 TYPE NUMBER_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
96 TYPE VARCHAR_80_TBL IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
97 TYPE DATE_TBL IS TABLE OF DATE INDEX BY BINARY_INTEGER;
98 TYPE VARCHAR_4000_TBL IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
99 TYPE VARCHAR_1_TBL IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
100
101 /*
102 TYPE CSEQ_REVISION_TBL IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
103 TYPE CSEQ_REVISION_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
104 TYPE CSEQ_REVISION_LABEL_TBL IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
105 TYPE REV_HIGHDATE_TBL IS TABLE OF DATE INDEX BY BINARY_INTEGER;
106 TYPE REV_SPECIFIC_EXCLN_TBL IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
107 TYPE ACCESS_FLAG_TBL IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
108 TYPE ITEMS_WITHOUT_ACCESS_TBL IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
109 TYPE COMPSEQS_WITHOUT_ACCESS_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
110 */
111
112 component_revision_array VARCHAR_10_TBL;
113 component_revision_id_array NUMBER_TBL;
114 component_revision_label_array VARCHAR_80_TBL;
115 revision_highdate_array DATE_TBL;
116 rev_specific_exclusions_array VARCHAR_4000_TBL;
117 access_flag_array VARCHAR_1_TBL;
118 asss_without_access_array VARCHAR_4000_TBL;
119 compseqs_without_access_array NUMBER_TBL;
120 change_policy_array VARCHAR_80_TBL;
121
122 /*
123 component_revision_array CSEQ_REVISION_TBL;
124 component_revision_id_array CSEQ_REVISION_ID_TBL;
125 component_revision_label_array CSEQ_REVISION_LABEL_TBL;
126 revision_highdate_array REV_HIGHDATE_TBL;
127 rev_specific_exclusions_array REV_SPECIFIC_EXCLN_TBL;
128 access_flag_array ACCESS_FLAG_TBL;
129 asss_without_access_array ITEMS_WITHOUT_ACCESS_TBL;
130 compseqs_without_access_array COMPSEQS_WITHOUT_ACCESS_TBL;
131 */
132
133 p_current_revision_id NUMBER;
134 p_current_revision_code VARCHAR2(10);
135 p_current_revision_label VARCHAR2(80);
136
137 PROCEDURE exploder_userexit (
138 verify_flag IN NUMBER DEFAULT 0,
139 org_id IN NUMBER,
140 order_by IN NUMBER DEFAULT 1,
141 grp_id IN OUT NOCOPY NUMBER,
142 session_id IN NUMBER DEFAULT 0,
143 levels_to_explode IN NUMBER DEFAULT 60,
144 bom_or_eng IN NUMBER DEFAULT 2,
145 impl_flag IN NUMBER DEFAULT 2,
146 plan_factor_flag IN NUMBER DEFAULT 2,
147 explode_option IN NUMBER DEFAULT 3,
148 module IN NUMBER DEFAULT 2,
149 cst_type_id IN NUMBER DEFAULT 0,
150 std_comp_flag IN NUMBER DEFAULT 0,
151 expl_qty IN NUMBER DEFAULT 1,
152 unit_number IN VARCHAR2 DEFAULT NULL,
153 alt_desg IN VARCHAR2 DEFAULT '',
154 comp_code IN VARCHAR2 DEFAULT '',
155 rev_date IN DATE DEFAULT sysdate,
156 minor_rev_id IN NUMBER DEFAULT NULL,
157 material_ctrl IN NUMBER DEFAULT 2,
158 lead_time IN NUMBER DEFAULT 2,
159 object_name IN VARCHAR2 DEFAULT NULL,
160 pk_value1 IN VARCHAR2,
161 pk_value2 IN VARCHAR2 DEFAULT NULL,
162 pk_value3 IN VARCHAR2 DEFAULT NULL,
163 pk_value4 IN VARCHAR2 DEFAULT NULL,
164 pk_value5 IN VARCHAR2 DEFAULT NULL,
165 end_item_id IN NUMBER DEFAULT NULL,
166 end_item_revision_id IN NUMBER DEFAULT NULL,
167 end_item_minor_revision_id IN NUMBER DEFAULT NULL,
168 err_msg IN OUT NOCOPY VARCHAR2,
169 error_code IN OUT NOCOPY NUMBER,
170 end_item_strc_revision_id IN NUMBER DEFAULT NULL,
171 show_rev IN NUMBER DEFAULT 1,
172 structure_rev_id IN NUMBER DEFAULT NULL,
173 structure_type_id IN NUMBER DEFAULT NULL,
174 filter_pbom IN VARCHAR2 DEFAULT NULL,
175 p_autonomous_transaction IN NUMBER DEFAULT 1,
176
177 --change made for P4Telco CMR, bug# 8761845
178 std_bom_explode_flag IN VARCHAR2 DEFAULT 'Y'
179 );
180
181 FUNCTION Get_Comp_Bill_Seq_Id (p_obj_name IN VARCHAR2,
182 p_top_alternate_designator IN VARCHAR2,
183 p_organization_id IN NUMBER,
184 p_pk1_value IN VARCHAR2,
185 p_pk2_value IN VARCHAR2) RETURN NUMBER;
186
187 FUNCTION Get_Change_Policy_Val (p_item_rev_id IN NUMBER,
188 p_bill_seq_id IN NUMBER) RETURN VARCHAR2;
189
190 /****************************************************************************
191 * Procedure : Apply_New_Exclusion_Rules
192 * Parameters : p_bill_sequence_id
193 * Scope : Local
194 * Purpose : This procedure is invoked when new explosion rules have been added
195 ******************************************************************************/
196 PROCEDURE Apply_New_Exclusion_Rules (p_bill_sequence_id IN NUMBER);
197
198 /****************************************************************************
199 * Procedure : Set_Reapply_Exclusion_Flag
200 * Parameters : p_bill_sequence_id
201 * Scope : Local
202 * Purpose : This procedure sets the reapply_exclusions flag to 'Y' for all the
203 * structures where this structure is added as substructure.
204 * Only the rows with plan_level=0 will be modified.
205 ******************************************************************************/
206 PROCEDURE Set_Reapply_Exclusion_Flag (p_bill_sequence_id IN NUMBER);
207
208
209 FUNCTION Get_Top_Bill_Sequence_Id RETURN NUMBER;
210 FUNCTION Get_Explosion_Date RETURN DATE;
211 FUNCTION Get_Expl_End_Item_Rev RETURN NUMBER;
212 FUNCTION Get_Expl_End_Item_Rev_Code RETURN VARCHAR2;
213 FUNCTION Get_Expl_Unit_Number RETURN VARCHAR2;
214 FUNCTION Get_Explode_Option RETURN NUMBER;
215 FUNCTION Get_Group_Id RETURN NUMBER;
216 FUNCTION Get_Top_Effectivity_Control RETURN NUMBER;
217
218 FUNCTION Get_Component_Revision(p_component_sequence_id NUMBER) RETURN VARCHAR2;
219 FUNCTION Get_Component_Revision_Id(p_component_sequence_id NUMBER) RETURN NUMBER;
220 FUNCTION Get_Component_Revision_Label(p_component_sequence_id NUMBER) RETURN VARCHAR2;
221 FUNCTION Get_Revision_HighDate(p_revision_id NUMBER) RETURN DATE;
222
223 FUNCTION Get_Component_Access_Flag(p_component_sequence_id NUMBER) RETURN VARCHAR2;
224
225 FUNCTION Get_EGO_User RETURN VARCHAR2;
226
227 FUNCTION Get_Revision_Code(p_revision_id NUMBER) RETURN VARCHAR2;
228
229 FUNCTION Get_Current_Revision_Code RETURN VARCHAR2;
230 FUNCTION Get_Current_Revision_Id RETURN NUMBER;
231 FUNCTION Get_Current_Revision_Label RETURN VARCHAR2;
232
233 FUNCTION Get_Current_RevisionDetails( p_inventory_item_id IN NUMBER,
234 p_organization_id IN NUMBER,
235 p_effectivity_date IN DATE) RETURN VARCHAR2;
236
237
238 FUNCTION Is_EndItem_Specific ( p_inventory_item_id IN NUMBER,
239 p_organization_id IN NUMBER,
240 p_revision_id IN NUMBER) RETURN VARCHAR2;
241
242 FUNCTION Check_Excluded_By_Rule (p_component_code IN VARCHAR2) RETURN VARCHAR2;
243
244 FUNCTION Get_Change_Policy(p_component_sequence_id NUMBER) RETURN VARCHAR2;
245
246 /****************************************************************************
247 * Procedure : Returns Revision id
248 * Parameters : p_inventory_item_id,p_organization_id,p_effectivity_date
249 * Scope : Public
250 * Purpose : This procedure is added to get latest revision id as per
251 effectivity_date.
252 ******************************************************************************/
253 FUNCTION Get_Current_RevisionId( p_inventory_item_id IN NUMBER,
254 p_organization_id IN NUMBER,
255 p_effectivity_date IN DATE) RETURN NUMBER;
256
257 END BOM_EXPLODER_PUB;
[Home] [Help]