[Home] [Help]
PACKAGE BODY: APPS.BOM_STRUCT_SYNC_PUB
Source
1 PACKAGE BODY BOM_STRUCT_SYNC_PUB AS
2 /* $Header: BOMSYNCB.pls 120.1 2008/01/09 15:44:29 pgandhik noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMSYNCS.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Spec for package BOM_STRUCT_SYNC_PUB
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- CREATED on 02-Jan-2008 by PGANDHIK
21 ***************************************************************************/
22
23 FUNCTION VALIDATE_ORG_ID
24 (p_org_id IN NUMBER,
25 x_error_message OUT NOCOPY VARCHAR2)
26 RETURN NUMBER
27 IS
28 l_return_code NUMBER := 0;
29 BEGIN
30 if (p_org_id is null or p_org_id = '') then
31 l_return_code := 1;
32 FND_MESSAGE.SET_NAME('BOM','BOM_INVALID_ORGANIZATION');
33 FND_MESSAGE.SET_TOKEN('L_ORGANIZATION_ID', p_org_id);
34 x_error_message := FND_MESSAGE.GET ;
35 end if;
36 return l_return_code;
37 END VALIDATE_ORG_ID;
38
39 FUNCTION VALIDATE_ITEM_ID
40 (p_item_id IN NUMBER,
41 p_org_id IN NUMBER,
42 x_error_message OUT NOCOPY VARCHAR2)
43 RETURN NUMBER
44 IS
45 l_return_code NUMBER := 0;
46 l_orderable_item NUMBER := 0;
47 BEGIN
48 if (p_item_id is null or p_item_id = '') then
49 l_return_code := 1;
50 FND_MESSAGE.SET_NAME('BOM','BOM_INVALID_ORGANIZATION');
51 FND_MESSAGE.SET_TOKEN('L_ORGANIZATION_ID', p_org_id);
52 x_error_message := FND_MESSAGE.GET ;
53 else
54 SELECT
55 1
56 INTO
57 l_orderable_item
58 FROM
59 MTL_SYSTEM_ITEMS_B
60 WHERE
61 CUSTOMER_ORDER_FLAG = 'Y'
62 AND CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
63 AND inventory_item_id = p_item_id
64 AND organization_id = p_org_id;
65 if (l_orderable_item = 0) then
66 l_return_code := 1;
67 FND_MESSAGE.SET_NAME('BOM','BOM_NOT_ORDERABLE_TOP_ITEM');
68 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID', p_item_id);
69 x_error_message := FND_MESSAGE.GET ;
70 end if;
71 end if;
72 return l_return_code;
73 END VALIDATE_ITEM_ID;
74
75 PROCEDURE EXPLODE_STRUCTURE
76 ( p_org_id IN NUMBER
77 , p_item_id IN NUMBER
78 , x_items_count OUT NOCOPY NUMBER
79 , x_error_code OUT NOCOPY NUMBER
80 , x_error_message OUT NOCOPY VARCHAR2
81 )
82 IS
83 l_items_count NUMBER := 0;
84 BEGIN
85
86 if (Validate_Org_ID (
87 p_org_id => p_org_id,
88 x_error_message => x_error_message) = 1) then
89 return;
90 end if;
91
92
93 if (Validate_Item_ID (
94 p_item_id => p_item_id,
95 p_org_id => p_org_id,
96 x_error_message => x_error_message) = 1) then
97 return;
98 end if;
99
100
101
102 /* Call explosions with ALL options and Sysdate as effectivity constraint */
103 bom_oe_exploder_pkg.be_exploder (
104 arg_org_id => p_org_id
105 , arg_starting_rev_date => sysdate
106 , arg_expl_type => 'ALL'
107 , arg_levels_to_explode => 60
108 , arg_item_id => p_item_id
109 , arg_alt_bom_desig => null
110 , arg_error_code => x_error_code
111 , arg_err_msg => x_error_message);
112
113 select
114 count(top_item_id)
115 into
116 l_items_count
117 from
118 bom_explosions be
119 where
120 be.top_item_id = p_item_id
121 and be.organization_id = p_org_id
122 and ( be.customer_order_flag = 'N'
123 OR be.CUSTOMER_ORDER_ENABLED_FLAG = 'N' );
124
125 x_items_count := l_items_count;
126
127
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 x_error_code := 1;
131 FND_MESSAGE.SET_NAME('BOM','BOM_UNEXPECTED_ERROR');
132 x_error_message := FND_MESSAGE.GET ;
133 x_error_message := x_error_message ||':'||SQLCODE || ':'||SQLERRM;
134 return;
135 WHEN OTHERS THEN
136 x_error_code := 1;
137 FND_MESSAGE.SET_NAME('BOM','BOM_UNEXPECTED_ERROR');
138 x_error_message := FND_MESSAGE.GET ;
139 x_error_message := x_error_message ||':'||SQLCODE || ':'||SQLERRM;
140 return;
141 END EXPLODE_STRUCTURE;
142
143 /* this can be removed */
144 PROCEDURE GET_ITEMS_TO_SYNCH
145 ( p_org_id IN NUMBER
146 , p_item_id IN NUMBER
147 , x_Bom OUT NOCOPY XMLTYPE
148 , x_error_code OUT NOCOPY NUMBER
149 , x_error_message OUT NOCOPY VARCHAR2
150 )
151 IS
152 p_bom xmlType;
153 BEGIN
154
155 if (Validate_Org_ID (
156 p_org_id => p_org_id,
157 x_error_message => x_error_message) = 1) then
158 return;
159 end if;
160
161 if (Validate_Item_ID (
162 p_item_id => p_item_id,
163 p_org_id => p_org_id,
164 x_error_message => x_error_message) = 1) then
165 return;
166 end if;
167
168
169
170 /* Call explosions with ALL options and Sysdate as effectivity constraint */
171 bom_oe_exploder_pkg.be_exploder (
172 arg_org_id => p_org_id
173 , arg_starting_rev_date => sysdate
174 , arg_expl_type => 'ALL'
175 , arg_levels_to_explode => 60
176 , arg_item_id => p_item_id
177 , arg_alt_bom_desig => null
178 , arg_error_code => x_error_code
179 , arg_err_msg => x_error_message);
180
181
182 if (x_error_code <> 0) then
183 return;
184 end if;
185
186
187 x_BOM := p_bom;
188 EXCEPTION
189 WHEN NO_DATA_FOUND THEN
190 x_error_code := 1;
191 FND_MESSAGE.SET_NAME('BOM','BOM_UNEXPECTED_ERROR');
192 x_error_message := FND_MESSAGE.GET ;
193 x_error_message := x_error_message ||':'||SQLCODE || ':'||SQLERRM;
194 return;
195 WHEN OTHERS THEN
196 x_error_code := 1;
197 FND_MESSAGE.SET_NAME('BOM','BOM_UNEXPECTED_ERROR');
198 x_error_message := FND_MESSAGE.GET ;
199 x_error_message := x_error_message ||':'||SQLCODE || ':'||SQLERRM;
200 return;
201 END GET_ITEMS_TO_SYNCH;
202
203
204 PROCEDURE GET_STRUCTURE_PAYLOAD
205 ( p_org_id IN NUMBER
206 , p_item_id IN NUMBER
207 , x_Bom OUT NOCOPY XMLTYPE
208 , x_error_code OUT NOCOPY NUMBER
209 , x_error_message OUT NOCOPY VARCHAR2
210 )
211 IS
212 p_bom xmlType;
213 BEGIN
214
215 if (Validate_Org_ID (
216 p_org_id => p_org_id,
217 x_error_message => x_error_message) = 1) then
218 return;
219 end if;
220
221 if (Validate_Item_ID (
222 p_item_id => p_item_id,
223 p_org_id => p_org_id,
224 x_error_message => x_error_message) = 1) then
225 return;
226 end if;
227
228 /*Bug 6407303 Added the attribute OPERATING_UNIT_ID */
229 SELECT
230 XMLElement("db:listOfBillOfMaterial",
231 XMLATTRIBUTES ( 'http://xmlns.oracle.com/pcbpel/adapter/db/APPS/BOM_STRUCT_SYNC_PUB/GET_STRUCTURE_PAYLOAD/' AS "xmlns:db"),
232 XMLAgg(XMLElement(
233 "db:billOfMaterial",
234 XMLAttributes(comp_bill_seq_id as "BillSequenceId"),
235 XMLForest(component_item_name as "db:AssemblyName"),
236 XMLForest(nvl(component_item_id, assembly_item_id) as "db:AssemblyItemId"),
237 XMLForest(OPERATING_UNIT_NAME as "db:OperatingUnit"),
238 XMLForest(ORGANIZATION_CODE as "db:OrganizationCode"),
239 XMLForest(ORGANIZATION_ID as "db:OrganizationId"),
240 XMLForest(OPERATING_UNIT_ID as "db:OperatingUnitId"),
241 (SELECT XMLElement("db:listOfBomComponent",
242 XMLAgg(XMLElement(
243 "db:billOfMaterialComponent",
244 XMLAttributes(
245 component_item_id as "Id"),
246 XMLForest(
247 component_sequence_id as "db:ComponentSequenceId",
248 component_item_name as "db:ComponentName",
249 Component_Quantity as "db:Quantity",
250 EFFECTIVITY_DATE as "db:EffectivityDate",
251 DISABLE_DATE as "db:DisableDate",
252 ORGANIZATION_CODE as "db:OrganizationCode",
253 ORGANIZATION_ID as "db:OrganizationId",
254 OPERATING_UNIT_ID as "db:OperatingUnitId"))))
255 FROM bom_structure_sync_v c
256 WHERE c.top_bill_sequence_id=a.top_bill_sequence_id
257 and c.bill_sequence_id= a.comp_bill_seq_id
258 and c.top_item_id <> c.component_item_id and c.effectivity_date <= sysdate --bug#5891992
259 )))) into p_bom
260 FROM bom_structure_sync_v a
261 where a.top_item_id = p_item_id
262 and a.organization_id = p_org_id
263 and a.comp_bill_seq_id is not null
264 order by bill_sequence_id;
265
266
267
268 x_BOM := p_bom;
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 x_error_code := 1;
272 FND_MESSAGE.SET_NAME('BOM','BOM_UNEXPECTED_ERROR');
273 x_error_message := FND_MESSAGE.GET ;
274 x_error_message := x_error_message ||':'||SQLCODE || ':'||SQLERRM;
275 return;
276 WHEN OTHERS THEN
277 x_error_code := 1;
278 FND_MESSAGE.SET_NAME('BOM','BOM_UNEXPECTED_ERROR');
279 x_error_message := FND_MESSAGE.GET ;
280 x_error_message := x_error_message ||':'||SQLCODE || ':'||SQLERRM;
281 return;
282 END GET_STRUCTURE_PAYLOAD;
283
284
285
286 END BOM_STRUCT_SYNC_PUB;