1 package bom_imploder_pub as
2 /* $Header: BOMPIMPS.pls 120.2 2006/02/07 04:31:16 bbpatel noship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : BOMIINQS.sql |
10 | DESCRIPTION : This file is a packaged procedure for the imploders.
11 | This package contains 2 different imploders for the
12 | single level and multi level implosion. The package
13 | imploders calls the correct imploder based on the
14 | # of levels to implode.
15 | Parameters: org_id organization_id
16 | sequence_id unique value to identify current implosion
17 | use value from sequence bom_small_impl_temp_s
18 | levels_to_implode
19 | eng_mfg_flag 1 - BOM
20 | 2 - ENG
21 | impl_flag 1 - implemented only
22 | 2 - both impl and unimpl
23 | display_option 1 - All
24 | 2 - Current
25 | 3 - Current and future
26 | item_id item id of asembly to explode
27 | impl_date explosion date dd-mon-yy hh24:mi
28 | err_msg error message out buffer
29 | error_code error code out. returns sql error code
30 | if sql error, 9999 if loop detected.
31 | organization_option
32 | 1 - Current Organization
33 | 2 - Organization Hierarchy
34 | 3 - All Organizations to which access allowed
35 | organization_hierarchy
36 | Organization Hierarchy Name
37 | HISTORY
38 | 09-NOV-05 Bhavnesh Patel Added Revision Filter
39 | 07-FEB-06 Bhavnesh Patel Removed sl_imploder_cad procedure
40 +==========================================================================*/
41
42 PROCEDURE imploder_userexit(
43 sequence_id IN NUMBER,
44 eng_mfg_flag IN NUMBER,
45 org_id IN NUMBER,
46 impl_flag IN NUMBER,
47 display_option IN NUMBER,
48 levels_to_implode IN NUMBER,
49 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
50 pk1_value IN VARCHAR2,
51 pk2_value IN VARCHAR2,
52 pk3_value IN VARCHAR2 DEFAULT NULL,
53 pk4_value IN VARCHAR2 DEFAULT NULL,
54 pk5_value IN VARCHAR2 DEFAULT NULL,
55 impl_date IN VARCHAR2,
56 unit_number_from IN VARCHAR2,
57 unit_number_to IN VARCHAR2,
58 err_msg OUT NOCOPY VARCHAR2,
59 err_code OUT NOCOPY NUMBER,
60 organization_option IN NUMBER default 1,
61 organization_hierarchy IN VARCHAR2 default NULL,
62 serial_number_from IN VARCHAR2 default NULL,
63 serial_number_to IN VARCHAR2 default NULL,
64 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE
65 );
66
67 PROCEDURE implosion_cad (
68 sequence_id IN NUMBER,
69 eng_mfg_flag IN NUMBER,
70 org_id IN NUMBER,
71 impl_flag IN NUMBER,
72 display_option IN NUMBER,
73 levels_to_implode IN NUMBER,
74 impl_date IN VARCHAR2,
75 unit_number_from IN VARCHAR2,
76 unit_number_to IN VARCHAR2,
77 err_msg OUT NOCOPY VARCHAR2,
78 err_code OUT NOCOPY NUMBER,
79 serial_number_from IN VARCHAR2 default NULL,
80 serial_number_to IN VARCHAR2 default NULL,
81 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
82 revision IN VARCHAR2);
83
84 PROCEDURE ml_imploder_cad(
85 sequence_id IN NUMBER,
86 eng_mfg_flag IN NUMBER,
87 org_id IN NUMBER,
88 impl_flag IN NUMBER,
89 a_levels_to_implode IN NUMBER,
90 impl_date IN VARCHAR2,
91 unit_number_from IN VARCHAR2,
92 unit_number_to IN VARCHAR2,
93 err_msg OUT NOCOPY VARCHAR2,
94 error_code OUT NOCOPY NUMBER,
95 serial_number_from IN VARCHAR2 default NULL,
96 serial_number_to IN VARCHAR2 default NULL,
97 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
98 revision IN VARCHAR2);
99 --TYPE t_OrgIDtable IS TABLE OF hr_organization_units.organization_id%TYPE
100 -- INDEX BY BINARY_INTEGER;
101
102 FUNCTION Check_User_View_priv (Itemid varchar2, OrgId varchar2)
103 RETURN Varchar2;
104
105 FUNCTION CALCULATE_COMP_COUNT
106 ( PK_VALUE1 IN VARCHAR2,
107 PK_VALUE2 IN VARCHAR2,
108 IMPL_DATE IN VARCHAR2)
109 RETURN NUMBER;
110
111 /* This is an overloaded procedure that will narrow down the where used to the
112 * provided structure type. It will simply call the existing imploder_userexit
113 * without regard to structure type and then delete the rows from bom_small_impl_temp
114 * which do not conform to the user entered structure type.
115 * One of the out parameters will contain the count of parents of the given item.
116 * Extra parameters:
117 * struct_type : structure type name
118 * preferred_only : flag to check indicate only whether
119 * implosion should be caried out only
120 * for preferred structures.
121 * 1 for true/ 2 for false
122 * used_count : Out parameter to indicate no of structures
123 * of this structure type where this item
124 * has been used.
125 */
126 PROCEDURE imploder_userexit(
127 sequence_id IN NUMBER ,
128 eng_mfg_flag IN NUMBER,
129 org_id IN NUMBER,
130 impl_flag IN NUMBER,
131 display_option IN NUMBER,
132 levels_to_implode IN NUMBER,
133 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
134 pk1_value IN VARCHAR2,
135 pk2_value IN VARCHAR2,
136 pk3_value IN VARCHAR2 DEFAULT null,
137 pk4_value IN VARCHAR2 DEFAULT null,
138 pk5_value IN VARCHAR2 DEFAULT null,
139 impl_date IN VARCHAR2,
140 unit_number_from IN VARCHAR2,
141 unit_number_to IN VARCHAR2,
142 err_msg OUT NOCOPY VARCHAR2,
143 err_code OUT NOCOPY NUMBER,
144 organization_option IN NUMBER default 1,
145 organization_hierarchy IN VARCHAR2 default NULL,
146 serial_number_from IN VARCHAR2 default NULL,
147 serial_number_to IN VARCHAR2 default NULL,
148 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
149 struct_type IN VARCHAR2,
150 preferred_only IN NUMBER DEFAULT 2,
151 used_in_structure OUT NOCOPY VARCHAR2
152 );
153
154 /*
155 * Overloaded procedure to take revision of component to search in first level
156 * parent
157 */
158 PROCEDURE imploder_userexit(
159 sequence_id IN NUMBER,
160 eng_mfg_flag IN NUMBER,
161 org_id IN NUMBER,
162 impl_flag IN NUMBER,
163 display_option IN NUMBER,
164 levels_to_implode IN NUMBER,
165 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
166 pk1_value IN VARCHAR2,
167 pk2_value IN VARCHAR2,
168 pk3_value IN VARCHAR2 DEFAULT NULL,
169 pk4_value IN VARCHAR2 DEFAULT NULL,
170 pk5_value IN VARCHAR2 DEFAULT NULL,
171 impl_date IN VARCHAR2,
172 unit_number_from IN VARCHAR2,
173 unit_number_to IN VARCHAR2,
174 err_msg OUT NOCOPY VARCHAR2,
175 err_code OUT NOCOPY NUMBER,
176 organization_option IN NUMBER default 1,
177 organization_hierarchy IN VARCHAR2 default NULL,
178 serial_number_from IN VARCHAR2 default NULL,
179 serial_number_to IN VARCHAR2 default NULL,
180 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
181 revision IN VARCHAR2
182 );
183
184 /*
185 * Overloaded procedure to take revision of component to search in first level
186 * parent. This is an overloaded procedure that will narrow down the where used to the
187 * provided structure type.
188 */
189 PROCEDURE imploder_userexit(
190 sequence_id IN NUMBER ,
191 eng_mfg_flag IN NUMBER,
192 org_id IN NUMBER,
193 impl_flag IN NUMBER,
194 display_option IN NUMBER,
195 levels_to_implode IN NUMBER,
196 obj_name IN VARCHAR2 DEFAULT 'EGO_ITEM',
197 pk1_value IN VARCHAR2,
198 pk2_value IN VARCHAR2,
199 pk3_value IN VARCHAR2 DEFAULT null,
200 pk4_value IN VARCHAR2 DEFAULT null,
201 pk5_value IN VARCHAR2 DEFAULT null,
202 impl_date IN VARCHAR2,
203 unit_number_from IN VARCHAR2,
204 unit_number_to IN VARCHAR2,
205 err_msg OUT NOCOPY VARCHAR2,
206 err_code OUT NOCOPY NUMBER,
207 organization_option IN NUMBER default 1,
208 organization_hierarchy IN VARCHAR2 default NULL,
209 serial_number_from IN VARCHAR2 default NULL,
210 serial_number_to IN VARCHAR2 default NULL,
211 struct_name IN VARCHAR2 DEFAULT FND_LOAD_UTIL.NULL_VALUE,
212 struct_type IN VARCHAR2,
213 preferred_only IN NUMBER DEFAULT 2,
214 used_in_structure OUT NOCOPY VARCHAR2,
215 revision IN VARCHAR2
216 );
217 END bom_imploder_pub;
[Home] [Help]