4 PROCEDURE COMPILE_FUTURE_VALUE(x_organization_id IN NUMBER,
1 PACKAGE BODY MTL_ABC_COMPILE_PKG AS
2 /* $Header: INVCAACB.pls 120.3 2008/06/24 10:33:13 sdpaul ship $ */
3
5 x_compile_id IN NUMBER,
6 x_forc_name IN VARCHAR2,
7 x_org_cost_group_id IN NUMBER,
8 x_cal_code IN VARCHAR2,
9 x_except_id IN NUMBER,
10 x_start_date IN VARCHAR2,
11 x_cutoff_date IN VARCHAR2,
12 x_item_scope_code IN NUMBER,
13 x_subinventory IN VARCHAR2) IS
14 Type item_id IS TABLE OF NUMBER;
15 Type qty_demand_total IS TABLE OF NUMBER;
16 Type value_demand_total IS TABLE OF NUMBER;
17 l_item_id item_id;
18 l_qty_demand_total qty_demand_total;
22 SELECT F.INVENTORY_ITEM_ID,
19 l_value_demand_total value_demand_total;
20 i NUMBER;
21 BEGIN
23 SUM(F.ORIGINAL_FORECAST_QUANTITY),
24 SUM(F.ORIGINAL_FORECAST_QUANTITY *
25 MTL_ABC_COMPILE_PKG.get_item_cost(F.ORGANIZATION_ID,
26 F.INVENTORY_ITEM_ID,
27 F.PROJECT_ID,
28 x_org_cost_group_id))
29 BULK COLLECT INTO l_item_id,l_qty_demand_total,l_value_demand_total
30 FROM
31 BOM_CALENDAR_DATES C1,
32 MRP_FORECAST_DESIGNATORS D1,
33 MRP_FORECAST_DESIGNATORS D2,
34 MRP_FORECAST_DATES F,
35 MTL_ABC_COMPILES ABC
36 WHERE D2.FORECAST_DESIGNATOR = x_forc_name
37 AND D1.ORGANIZATION_ID = x_organization_id
38 AND D2.ORGANIZATION_ID = x_organization_id
39 AND D1.FORECAST_SET = NVL(D2.FORECAST_SET, x_forc_name)
40 AND D1.FORECAST_DESIGNATOR = DECODE(D2.FORECAST_SET, NULL,
41 D1.FORECAST_DESIGNATOR, x_forc_name)
42 AND ABC.ORGANIZATION_ID = F.ORGANIZATION_ID
43 AND ABC.COMPILE_ID = x_compile_id
44 AND F.INVENTORY_ITEM_ID = ABC.INVENTORY_ITEM_ID
45 AND F.ORGANIZATION_ID = x_organization_id
46 AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
47 AND F.BUCKET_TYPE = 1
48 AND C1.CALENDAR_CODE = x_cal_code
49 AND C1.EXCEPTION_SET_ID = x_except_id
50 AND ( C1.CALENDAR_DATE >= F.FORECAST_DATE
51 --Added format mask while using to_date function to comply with
52 --GSCC File.Date.5 standard. Bug:4410902
53 -- Bug# 6819570, replaced the to_date function used earlier with
54 -- FND_DATE.canonical_to_date which does not require format mask,
55 -- hence avoiding ORA-01861 'literal does not match format string' error.
56 AND C1.CALENDAR_DATE >= FND_DATE.canonical_to_date(x_start_date)
57 AND C1.CALENDAR_DATE <= FND_DATE.canonical_to_date(x_cutoff_date)
58 AND C1.CALENDAR_DATE = C1.NEXT_DATE
59 AND C1.CALENDAR_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE ))
60 group by F.INVENTORY_ITEM_ID
61 UNION ALL
62 SELECT F.INVENTORY_ITEM_ID,
63 SUM(F.ORIGINAL_FORECAST_QUANTITY/(C2.NEXT_SEQ_NUM-C3.NEXT_SEQ_NUM)),
64 SUM((F.ORIGINAL_FORECAST_QUANTITY/(C2.NEXT_SEQ_NUM-C3.NEXT_SEQ_NUM)) *
65 MTL_ABC_COMPILE_PKG.get_item_cost(F.ORGANIZATION_ID,
66 F.INVENTORY_ITEM_ID,
67 F.PROJECT_ID,
68 x_org_cost_group_id))
69 FROM BOM_CALENDAR_DATES C1, BOM_CALENDAR_DATES C2,
70 BOM_CALENDAR_DATES C3,
71 BOM_CAL_WEEK_START_DATES W1, MRP_FORECAST_DATES F,
72 MRP_FORECAST_DESIGNATORS D1, MRP_FORECAST_DESIGNATORS D2,
73 MTL_ABC_COMPILES ABC
74 WHERE D2.FORECAST_DESIGNATOR = x_forc_name
75 AND D1.ORGANIZATION_ID = x_organization_id
76 AND D2.ORGANIZATION_ID = x_organization_id
77 AND D1.FORECAST_SET = NVL(D2.FORECAST_SET, x_forc_name)
78 AND D1.FORECAST_DESIGNATOR = DECODE(D2.FORECAST_SET, NULL,
79 D1.FORECAST_DESIGNATOR, x_forc_name)
80 AND ABC.ORGANIZATION_ID = F.ORGANIZATION_ID
81 AND ABC.COMPILE_ID = x_compile_id
82 AND F.INVENTORY_ITEM_ID = ABC.INVENTORY_ITEM_ID
83 AND F.ORGANIZATION_ID = x_organization_id
84 AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
85 AND F.BUCKET_TYPE = 2
86 AND W1.CALENDAR_CODE = x_cal_code
87 AND W1.EXCEPTION_SET_ID = x_except_id
88 AND (W1.WEEK_START_DATE >= F.FORECAST_DATE
89 AND W1.WEEK_START_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE))
90 --Added format mask while using to_date function to comply with
91 --GSCC File.Date.5 standard. Bug:4410902
92 -- Bug# 6819570, replaced the to_date function used earlier with
93 -- FND_DATE.canonical_to_date which does not require format mask,
94 -- hence avoiding ORA-01861 'literal does not match format string' error.
95 AND W1.NEXT_DATE > FND_DATE.canonical_to_date(x_start_date)
96 AND C1.CALENDAR_CODE = x_cal_code
97 AND C2.CALENDAR_CODE = x_cal_code
98 AND C3.CALENDAR_CODE = x_cal_code
99 AND C1.EXCEPTION_SET_ID = x_except_id
100 AND C2.EXCEPTION_SET_ID = x_except_id
101 AND C3.EXCEPTION_SET_ID = x_except_id
102 AND C3.CALENDAR_DATE= W1.WEEK_START_DATE
103 AND C2.CALENDAR_DATE = W1.NEXT_DATE
104 AND (C1.CALENDAR_DATE >= C3.CALENDAR_DATE
105 --Added format mask while using to_date function to comply with
106 --GSCC File.Date.5 standard. Bug:4410902
107 -- Bug# 6819570, replaced the to_date function used earlier with
108 -- FND_DATE.canonical_to_date which does not require format mask,
109 -- hence avoiding ORA-01861 'literal does not match format string' error.
110 AND C1.CALENDAR_DATE >= FND_DATE.canonical_to_date(x_start_date)
111 AND C1.CALENDAR_DATE <= FND_DATE.canonical_to_date(x_cutoff_date)
112 AND C1.CALENDAR_DATE = C1.NEXT_DATE
113 AND C1.CALENDAR_DATE < C2.CALENDAR_DATE)
114 group by F.INVENTORY_ITEM_ID
115 UNION ALL
116 SELECT F.INVENTORY_ITEM_ID,
117 SUM(F.ORIGINAL_FORECAST_QUANTITY/
118 (C2.NEXT_SEQ_NUM - C3.NEXT_SEQ_NUM)),
119 SUM(F.ORIGINAL_FORECAST_QUANTITY/
120 (C2.NEXT_SEQ_NUM - C3.NEXT_SEQ_NUM) *
121 MTL_ABC_COMPILE_PKG.get_item_cost(F.ORGANIZATION_ID,
125 FROM BOM_CALENDAR_DATES C1, BOM_CALENDAR_DATES C2,
122 F.INVENTORY_ITEM_ID,
123 F.PROJECT_ID,
124 x_org_cost_group_id))
126 BOM_CALENDAR_DATES C3,
127 BOM_PERIOD_START_DATES W1, MRP_FORECAST_DATES F,
128 MRP_FORECAST_DESIGNATORS D1, MRP_FORECAST_DESIGNATORS D2,
129 MTL_ABC_COMPILES ABC
130 WHERE D2.FORECAST_DESIGNATOR = x_forc_name
131 AND D1.ORGANIZATION_ID = x_organization_id
132 AND D2.ORGANIZATION_ID = x_organization_id
133 AND D1.FORECAST_SET = NVL(D2.FORECAST_SET, x_forc_name)
134 AND D1.FORECAST_DESIGNATOR = DECODE(D2.FORECAST_SET, NULL,
135 D1.FORECAST_DESIGNATOR, x_forc_name)
136 AND ABC.ORGANIZATION_ID = F.ORGANIZATION_ID
137 AND ABC.COMPILE_ID = x_compile_id
138 AND F.INVENTORY_ITEM_ID = ABC.INVENTORY_ITEM_ID
139 AND F.ORGANIZATION_ID = x_organization_id
140 AND F.FORECAST_DESIGNATOR = D1.FORECAST_DESIGNATOR
141 AND F.BUCKET_TYPE = 3
142 AND W1.CALENDAR_CODE = x_cal_code
143 AND W1.EXCEPTION_SET_ID = x_except_id
144 AND (W1.PERIOD_START_DATE >= F.FORECAST_DATE
145 AND W1.PERIOD_START_DATE <= NVL(F.RATE_END_DATE, F.FORECAST_DATE))
146 --Added format mask while using to_date function to comply with
147 --GSCC File.Date.5 standard. Bug:4410902
148 -- Bug# 6819570, replaced the to_date function used earlier with
149 -- FND_DATE.canonical_to_date which does not require format mask,
150 -- hence avoiding ORA-01861 'literal does not match format string' error.
151 AND W1.NEXT_DATE > FND_DATE.canonical_to_date(x_start_date)
152 AND C1.CALENDAR_CODE = x_cal_code
153 AND C2.CALENDAR_CODE = x_cal_code
154 AND C3.CALENDAR_CODE = x_cal_code
155 AND C1.EXCEPTION_SET_ID = x_except_id
156 AND C2.EXCEPTION_SET_ID = x_except_id
157 AND C3.EXCEPTION_SET_ID = x_except_id
158 AND C3.CALENDAR_DATE= W1.PERIOD_START_DATE
159 AND C2.CALENDAR_DATE = W1.NEXT_DATE
160 AND (C1.CALENDAR_DATE >= C3.CALENDAR_DATE
161 --Added format mask while using to_date function to comply with
162 --GSCC File.Date.5 standard. Bug:4410902
163 -- Bug# 6819570, replaced the to_date function used earlier with
164 -- FND_DATE.canonical_to_date which does not require format mask,
165 -- hence avoiding ORA-01861 'literal does not match format string' error.
166 AND C1.CALENDAR_DATE >= FND_DATE.canonical_to_date(x_start_date)
167 AND C1.CALENDAR_DATE = C1.NEXT_DATE
168 --Added format mask while using to_date function to comply with
169 --GSCC File.Date.5 standard. Bug:4410902
170 -- Bug# 6819570, replaced the to_date function used earlier with
171 -- FND_DATE.canonical_to_date which does not require format mask,
172 -- hence avoiding ORA-01861 'literal does not match format string' error.
173 AND C1.CALENDAR_DATE <= FND_DATE.canonical_to_date(x_cutoff_date)
174 AND C1.CALENDAR_DATE < C2.CALENDAR_DATE)
175 group by F.INVENTORY_ITEM_ID;
176
177 FORALL i IN l_item_id.FIRST .. l_item_id.LAST
178 UPDATE MTL_ABC_COMPILES
179 SET COMPILE_QUANTITY = COMPILE_QUANTITY + l_qty_demand_total(i),
180 COMPILE_VALUE = COMPILE_VALUE + l_value_demand_total(i)
181 WHERE ORGANIZATION_ID = x_organization_id
182 AND inventory_item_id = l_item_id(i)
183 AND compile_id = x_compile_id;
184
185 END COMPILE_FUTURE_VALUE;
186
187 FUNCTION GET_ITEM_COST(x_organization_id IN NUMBER,
188 x_inventory_item_id IN NUMBER,
189 x_project_id IN NUMBER,
190 x_cost_group_id IN NUMBER) return NUMBER
191 IS
192 l_item_cost NUMBER := 0.0;
193 BEGIN
194 IF (x_project_id IS NOT NULL) THEN
195 SELECT NVL(CCICV.ITEM_COST,0) into l_item_cost
196 FROM CST_CG_ITEM_COSTS_VIEW CCICV,
197 MRP_PROJECT_PARAMETERS MPP
198 WHERE CCICV.ORGANIZATION_ID = x_organization_id
199 AND CCICV.INVENTORY_ITEM_ID = x_inventory_item_id
200 AND CCICV.COST_GROUP_ID = MPP.COSTING_GROUP_ID
201 AND MPP.PROJECT_ID = x_project_id
202 AND MPP.ORGANIZATION_ID = x_organization_id;
203 ELSIF (x_project_id IS NULL) THEN
204 SELECT NVL(CCICV.ITEM_COST,0) into l_item_cost
205 FROM CST_CG_ITEM_COSTS_VIEW CCICV
206 WHERE CCICV.ORGANIZATION_ID = x_organization_id
207 AND CCICV.INVENTORY_ITEM_ID = x_inventory_item_id
208 AND CCICV.COST_GROUP_ID = x_cost_group_id;
209 END IF;
210 RETURN l_item_cost;
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
216 -- BEGIN INVCONV
213 RETURN l_item_cost;
214 END GET_ITEM_COST;
215
217 PROCEDURE CALCULATE_COMPILE_VALUE (
218 p_organization_id NUMBER
219 , p_compile_id NUMBER
220 , p_cost_type_id NUMBER) IS
221 --
222 -- variable declarations
223 l_result_code VARCHAR2 (30);
224 l_return_status VARCHAR2 (30);
225 l_msg_count NUMBER;
226 l_msg_data VARCHAR2 (2000);
227 l_transaction_date DATE;
228 l_cost_mthd VARCHAR2 (15);
229 l_cost_mthd_1 VARCHAR2 (15);
230 l_cmpntcls NUMBER;
231 l_analysis_code VARCHAR2 (15);
232 l_item_cost NUMBER;
233 l_no_of_rows NUMBER;
234
235 CURSOR cur_get_abc_compiles IS
236 SELECT organization_id
237 , inventory_item_id
238 , compile_id
239 FROM mtl_abc_compiles
240 WHERE compile_id = p_compile_id
241 FOR UPDATE;
242
243 BEGIN
244 BEGIN
245 SELECT cost_mthd_code
246 INTO l_cost_mthd
247 FROM cm_mthd_mst
248 WHERE cost_type_id = p_cost_type_id;
249 EXCEPTION WHEN OTHERS THEN
250 l_cost_mthd := NULL;
251 END;
252
253 FOR i IN cur_get_abc_compiles LOOP
254 -- variable assignments
255 l_result_code := NULL;
256 l_return_status := NULL;
257 l_msg_count := NULL;
258 l_msg_data := NULL;
259 l_transaction_date := SYSDATE;
260 l_cmpntcls := NULL;
261 l_analysis_code := NULL;
262 l_item_cost := NULL;
263 l_no_of_rows := NULL;
264 l_cost_mthd_1 := l_cost_mthd;
265
266 -- call the costing API.
267 l_result_code :=
268 gmf_cmcommon.get_process_item_cost
269 (p_api_version => 1
273 , x_msg_data => l_msg_data
270 , p_init_msg_list => 'F'
271 , x_return_status => l_return_status
272 , x_msg_count => l_msg_count
274 , p_inventory_item_id => i.inventory_item_id
275 , p_organization_id => i.organization_id
276 , p_transaction_date => l_transaction_date /* Cost as on date */
277 , p_detail_flag => 1 /* 1 = total cost, 2 = details; 3 = cost for a specific component class/analysis code, etc. */
278 , p_cost_method => l_cost_mthd_1 /* OPM Cost Method */
279 , p_cost_component_class_id => l_cmpntcls
280 , p_cost_analysis_code => l_analysis_code
281 , x_total_cost => l_item_cost /* total cost */
282 , x_no_of_rows => l_no_of_rows /* number of detail rows retrieved */
283 );
284
285 IF l_result_code <> 1 THEN
286 l_item_cost := 0;
287 END IF;
288
289 UPDATE mtl_abc_compiles
290 SET compile_value = compile_quantity * l_item_cost
291 WHERE CURRENT OF cur_get_abc_compiles;
292
293 END LOOP;
294
295 END CALCULATE_COMPILE_VALUE;
296 -- END INVCONV
297
298 END MTL_ABC_COMPILE_PKG;
[Home] [Help]