1 PACKAGE BODY OE_CONFIG AS
2 /* $Header: oexczdfb.pls 115.8 2000/02/16 18:31:10 pkm ship $ */
3
4
5 function CZ_BCE_DETAILS_FLAG(comp_common_bill_sequence_id IN NUMBER)
6 RETURN VARCHAR2
7 IS
8 CURSOR details(comp_common_bill_sequence_id NUMBER) IS
9
10 select 'Y'
11 from bom_inventory_components bic
12 where bic.bill_sequence_id = comp_common_bill_sequence_id
13 and (bic.bom_item_type = 1
14 or bic.bom_item_type = 2
15 or (bic.optional = 1 and bic.bom_item_type = 4)
16 );
17
18 --The cursor "details" has been changed to resolve the issue where "+" signs were
19 --not being displayed in the config window for mandatory option classes having
20 --optional items under it (bug #615826). The previous sql used to look at the
21 --immediate children of the referenced item. It has been changed to look all
22 --the way down the tree.
23
24 /* select 'Y'
25 from bom_bill_of_materials bom
26 where bom.assembly_item_id (+) = assembly_id
27 and bom.organization_id = org_id
28 and nvl(bom.alternate_bom_designator,'-99') = nvl(alt_desg,'-99')
29 and exists (select 'Exists'
30 from bom_inventory_components bic
31 where bic.bill_sequence_id = bom.common_bill_sequence_id
32 and optional = 1);*/
33
34 details_flag VARCHAR2(15) := 'N';
35 BEGIN
36 OPEN details(comp_common_bill_sequence_id);
37 FETCH details into details_flag;
38 IF SQL%NOTFOUND THEN
39 details_flag := 'N';
40 END IF;
41 CLOSE details;
42
43 return(details_flag);
44 END CZ_BCE_DETAILS_FLAG;
45
46
47 FUNCTION cz_get_list_price( component_item_id IN NUMBER,
48 primary_uom_code IN VARCHAR2,
49 list_id IN NUMBER,
50 prc_attr1 IN VARCHAR2 DEFAULT NULL,
51 prc_attr2 IN VARCHAR2 DEFAULT NULL,
52 prc_attr3 IN VARCHAR2 DEFAULT NULL,
53 prc_attr4 IN VARCHAR2 DEFAULT NULL,
54 prc_attr5 IN VARCHAR2 DEFAULT NULL,
55 prc_attr6 IN VARCHAR2 DEFAULT NULL,
56 prc_attr7 IN VARCHAR2 DEFAULT NULL,
57 prc_attr8 IN VARCHAR2 DEFAULT NULL,
58 prc_attr9 IN VARCHAR2 DEFAULT NULL,
59 prc_attr10 IN VARCHAR2 DEFAULT NULL,
60 prc_attr11 IN VARCHAR2 DEFAULT NULL,
61 prc_attr12 IN VARCHAR2 DEFAULT NULL,
62 prc_attr13 IN VARCHAR2 DEFAULT NULL,
63 prc_attr14 IN VARCHAR2 DEFAULT NULL,
64 prc_attr15 IN VARCHAR2 DEFAULT NULL)
65
66 RETURN NUMBER
67 IS
68 --****************************************************************************
69 -- This function is used to populate the list price of an item in the config window.
70 -- It checks for the list price in the following places in the following order:
71 -- 1. The primary price list with the pricing attributes on the price list
72 -- matching the pricing attributes on the line.
73 -- 2. The primary price list without checking if the attributes match.
74 -- 3. The list price on the item.
75 --*****************************************************************************
76
77 CURSOR price(component_item_id NUMBER,
78 primary_uom_code VARCHAR2,
79 x_price_list_id NUMBER,
80 prc_attr1 VARCHAR2,
81 prc_attr2 VARCHAR2,
82 prc_attr3 VARCHAR2,
83 prc_attr4 VARCHAR2,
84 prc_attr5 VARCHAR2,
85 prc_attr6 VARCHAR2,
86 prc_attr7 VARCHAR2,
87 prc_attr8 VARCHAR2,
88 prc_attr9 VARCHAR2,
89 prc_attr10 VARCHAR2,
90 prc_attr11 VARCHAR2,
91 prc_attr12 VARCHAR2,
92 prc_attr13 VARCHAR2,
93 prc_attr14 VARCHAR2,
94 prc_attr15 VARCHAR2) IS
95 SELECT NVL(PRL.LIST_PRICE,SEL.LIST_PRICE)
96 FROM SO_PRICE_LISTS PRI
97 , SO_PRICE_LIST_LINES PRL
98 , SO_PRICE_LISTS SEC
99 , SO_PRICE_LIST_LINES SEL
100 WHERE PRL.INVENTORY_ITEM_ID (+)= component_item_id
101 AND PRL.UNIT_CODE (+)= primary_uom_code
102 AND PRL.PRICE_LIST_ID (+) +0 = PRI.PRICE_LIST_ID
103 AND TRUNC(SYSDATE) BETWEEN NVL(PRL.START_DATE_ACTIVE(+),TRUNC(SYSDATE))
104 AND NVL(PRL.END_DATE_ACTIVE(+),TRUNC(SYSDATE))
105 AND PRI.PRICE_LIST_ID = x_price_list_id
106 AND TRUNC(SYSDATE) BETWEEN NVL(PRI.START_DATE_ACTIVE,TRUNC(SYSDATE))
107 AND NVL(PRI.END_DATE_ACTIVE,TRUNC(SYSDATE))
108 AND SEL.INVENTORY_ITEM_ID (+)= component_item_id
109 AND SEL.UNIT_CODE (+)= primary_uom_code
110 AND TRUNC(SYSDATE) BETWEEN NVL(SEL.START_DATE_ACTIVE(+),TRUNC(SYSDATE))
111 AND NVL(SEL.END_DATE_ACTIVE(+),TRUNC(SYSDATE))
112 AND SEC.PRICE_LIST_ID = NVL(PRI.SECONDARY_PRICE_LIST_ID,
113 PRI.PRICE_LIST_ID)
114 AND TRUNC(SYSDATE) BETWEEN NVL(SEC.START_DATE_ACTIVE,TRUNC(SYSDATE))
115 AND NVL(SEC.END_DATE_ACTIVE,TRUNC(SYSDATE))
116 AND SEL.PRICE_LIST_ID (+) +0 = SEC.PRICE_LIST_ID
117 --AND NVL(PRL.PRICING_CONTEXT,' ') = ' '
118 AND NVL(PRL.PRICING_ATTRIBUTE1,'NULL') = NVL(prc_attr1, 'NULL')
119 AND NVL(PRL.PRICING_ATTRIBUTE2,'NULL') = NVL(prc_attr2, 'NULL')
120 AND NVL(PRL.PRICING_ATTRIBUTE3,'NULL') = NVL(prc_attr3, 'NULL')
121 AND NVL(PRL.PRICING_ATTRIBUTE4,'NULL') = NVL(prc_attr4, 'NULL')
122 AND NVL(PRL.PRICING_ATTRIBUTE5,'NULL') = NVL(prc_attr5, 'NULL')
123 AND NVL(PRL.PRICING_ATTRIBUTE6,'NULL') = NVL(prc_attr6, 'NULL')
124 AND NVL(PRL.PRICING_ATTRIBUTE7,'NULL') = NVL(prc_attr7, 'NULL')
125 AND NVL(PRL.PRICING_ATTRIBUTE8,'NULL') = NVL(prc_attr8, 'NULL')
126 AND NVL(PRL.PRICING_ATTRIBUTE9,'NULL') = NVL(prc_attr9, 'NULL')
127 AND NVL(PRL.PRICING_ATTRIBUTE10,'NULL') = NVL(prc_attr10, 'NULL')
128 AND NVL(PRL.PRICING_ATTRIBUTE11,'NULL') = NVL(prc_attr11, 'NULL')
129 AND NVL(PRL.PRICING_ATTRIBUTE12,'NULL') = NVL(prc_attr12, 'NULL')
130 AND NVL(PRL.PRICING_ATTRIBUTE13,'NULL') = NVL(prc_attr13, 'NULL')
131 AND NVL(PRL.PRICING_ATTRIBUTE14,'NULL') = NVL(prc_attr14, 'NULL')
132 AND NVL(PRL.PRICING_ATTRIBUTE15,'NULL') = NVL(prc_attr15, 'NULL')
133 --AND NVL(SEL.PRICING_CONTEXT,' ') = ' '
134 --AND NVL(SEL.PRICING_ATTRIBUTE1,' ') = ' '
135 --AND NVL(SEL.PRICING_ATTRIBUTE2,' ') = ' '
136 --AND NVL(SEL.PRICING_ATTRIBUTE3,' ') = ' '
137 --AND NVL(SEL.PRICING_ATTRIBUTE4,' ') = ' '
138 --AND NVL(SEL.PRICING_ATTRIBUTE5,' ') = ' '
139 --AND NVL(SEL.PRICING_ATTRIBUTE6,' ') = ' '
140 --AND NVL(SEL.PRICING_ATTRIBUTE7,' ') = ' '
141 --AND NVL(SEL.PRICING_ATTRIBUTE8,' ') = ' '
142 --AND NVL(SEL.PRICING_ATTRIBUTE9,' ') = ' '
143 --AND NVL(SEL.PRICING_ATTRIBUTE10,' ') = ' '
144 --AND NVL(SEL.PRICING_ATTRIBUTE11,' ') = ' '
145 --AND NVL(SEL.PRICING_ATTRIBUTE12,' ') = ' '
146 --AND NVL(SEL.PRICING_ATTRIBUTE13,' ') = ' '
147 --AND NVL(SEL.PRICING_ATTRIBUTE14,' ') = ' '
148 --AND NVL(SEL.PRICING_ATTRIBUTE15,' ') = ' '
149 ;
150
151 CURSOR dummy_price(component_item_id NUMBER) IS
152 select LIST_PRICE_PER_UNIT
153 from mtl_system_items_kfv msi
154 where msi.inventory_item_id = component_item_id;
155
156 x_list_price NUMBER ;
157
158 BEGIN
159
160 if (list_id is NULL) then
161 return(0);
162 end if;
163 open dummy_price(component_item_id);
164 fetch dummy_price into x_list_price;
165 if dummy_price%NOTFOUND then
166 x_list_price := 0;
167 end if;
168 close dummy_price;
169
170 open price(component_item_id,
171 primary_uom_code,
172 list_id,
173 prc_attr1,
174 prc_attr2,
175 prc_attr3,
176 prc_attr4,
177 prc_attr5,
178 prc_attr6,
179 prc_attr7,
180 prc_attr8,
181 prc_attr9,
182 prc_attr10,
183 prc_attr11,
184 prc_attr12,
185 prc_attr13,
186 prc_attr14,
187 prc_attr15);
188
189 FETCH price into x_list_price;
190 IF price%NOTFOUND THEN
191 CLOSE price;
192 OPEN price(component_item_id,
193 primary_uom_code,
194 list_id,
195 'NULL',
196 'NULL',
197 'NULL',
198 'NULL',
199 'NULL',
200 'NULL',
201 'NULL',
202 'NULL',
203 'NULL',
204 'NULL',
205 'NULL',
206 'NULL',
207 'NULL',
208 'NULL',
209 'NULL');
210 FETCH price into x_list_price;
211 IF price%NOTFOUND THEN
212 CLOSE price;
213 return(x_list_price);
214 END IF;
215 ELSE
216 CLOSE price;
217 return(x_list_price);
218 END IF;
219 return(x_list_price);
220 END cz_get_list_price;
221
222
223 function CZ_MESSAGE_COUNT(x_system_id IN NUMBER,
224 x_header_id IN NUMBER,
225 x_line_id IN NUMBER)
226 RETURN NUMBER
227 is
228 x_count number := 0;
229 BEGIN
230 return(x_count);
231 END CZ_MESSAGE_COUNT;
232
233 function CZ_ERROR_COUNT(x_system_id IN NUMBER,
234 x_header_id IN NUMBER,
235 x_line_id IN NUMBER)
236 RETURN NUMBER
237 is
238 x_count number := 0;
239 BEGIN
240
241 return(x_count);
242
243 END CZ_ERROR_COUNT;
244
245 function CZ_AUTOSELECT_COUNT(x_system_id IN NUMBER,
246 x_header_id IN NUMBER,
247 x_line_id IN NUMBER)
248 RETURN NUMBER
249 is
250 x_count number := 0;
251 BEGIN
252
253 return(x_count);
254
255 END CZ_AUTOSELECT_COUNT;
256
257 function CZ_OVERRIDE_ERROR_COUNT(x_system_id IN NUMBER,
258 x_header_id IN NUMBER,
259 x_line_id IN NUMBER)
260 RETURN NUMBER
261 is
262 x_count number := 0;
263 BEGIN
264
265 return(x_count);
266
267 END CZ_OVERRIDE_ERROR_COUNT;
268
269 function CZ_OVERRIDEN_COUNT(x_system_id IN NUMBER,
270 x_header_id IN NUMBER,
271 x_line_id IN NUMBER)
272 RETURN NUMBER
273 is
274 x_count number := 0;
275 BEGIN
276
277 return(x_count);
278
279 END CZ_OVERRIDEN_COUNT;
280
281
282 function CZ_WARN_COUNT(x_system_id IN NUMBER,
283 x_header_id IN NUMBER,
284 x_line_id IN NUMBER)
285 RETURN NUMBER
286 is
287
288 x_count number := 0;
289 BEGIN
290 return(x_count);
291 END CZ_WARN_COUNT;
292
293 function CZ_SUGGEST_COUNT(x_system_id IN NUMBER,
297 is
294 x_header_id IN NUMBER,
295 x_line_id IN NUMBER)
296 RETURN NUMBER
298 x_count number := 0;
299
300 BEGIN
301 return(x_count);
302 END CZ_SUGGEST_COUNT;
303
304
305 END OE_CONFIG;
[Home] [Help]