[Home] [Help]
PACKAGE BODY: APPS.OPI_PMI_COST
Source
1 PACKAGE BODY opi_pmi_cost AS
2 /* $Header: OPIMCSTB.pls 115.3 2004/05/11 18:35:15 pdong ship $ */
3
4 PROCEDURE get_cost IS
5
6 CURSOR cm_cldr IS
7 SELECT /*+ ALL_ROWS */
8 hdr.co_code,
9 hdr.cost_mthd_code,
10 dtl.calendar_code,
11 dtl.period_code,
12 dtl.start_date,
13 dtl.end_date,
14 dtl.period_status
15 FROM
16 cm_cldr_dtl dtl,
17 cm_cldr_hdr hdr
18 WHERE
19 hdr.calendar_code = dtl.calendar_code
20 AND hdr.delete_mark = 0
21 AND dtl.delete_mark = 0
22 ORDER BY
23 hdr.cost_mthd_code, hdr.co_code, dtl.end_date;
24
25 pv_prior_cost_mthd_code cm_cldr_hdr.cost_mthd_code%TYPE;
26 pv_prior_co_code cm_cldr_hdr.co_code%TYPE;
27 pv_prior_calendar_code cm_cldr_hdr.calendar_code%TYPE;
28 pv_prior_period_code cm_cldr_dtl.period_code%TYPE;
29 pv_prior_period_status cm_cldr_dtl.period_status%TYPE;
30 pv_sql varchar2(10000);
31 BEGIN
32 -- BUILD COST CALENDAR SUMMARY TABLE, adding PRIOR PERIOD
33
34 pv_prior_cost_mthd_code := NULL;
35
36 FOR cc IN cm_cldr
37 LOOP
38 IF cc.cost_mthd_code = pv_prior_cost_mthd_code
39 AND cc.co_code = pv_prior_co_code
40 THEN
41 NULL; -- prior calendar and period code applies; keep them
42 ELSE
43 -- COST_MTHD IS CHANGED - RESET PRIOR PERIOD KEY
44
45 pv_prior_calendar_code := NULL;
46 pv_prior_period_code := NULL;
47 pv_prior_period_status := NULL;
48 END IF;
49
50 INSERT INTO opi_pmi_cldr_sum_gtmp (
51 co_code,
52 cost_mthd,
53 calendar_code,
54 period_code,
55 period_status,
56 start_date,
57 end_date,
58 prior_calendar_code,
59 prior_period_code,
60 prior_period_status)
61 VALUES (
62 cc.co_code,
63 cc.cost_mthd_code,
64 cc.calendar_code,
65 cc.period_code,
66 cc.period_status,
67 cc.start_date,
68 cc.end_date,
69 pv_prior_calendar_code,
70 pv_prior_period_code,
71 pv_prior_period_status);
72
73 pv_prior_cost_mthd_code := cc.cost_mthd_code;
74 pv_prior_co_code := cc.co_code;
75 pv_prior_calendar_code := cc.calendar_code;
76 pv_prior_period_code := cc.period_code;
77 pv_prior_period_status := cc.period_status;
78
79 END LOOP;
80
81
82 -- EXTRACT DISTINCT WAREHOUSES AND DATES
83
84 INSERT INTO opi_pmi_whse_date_gtmp(whse_code, trans_date)
85 SELECT /*+ ALL_ROWS */ DISTINCT whse_code, trans_date
86 FROM opi_pmi_cost_param_gtmp;
87
88
89 -- GET EFFECTIVE COST WAREHOUSE
90
91 INSERT INTO opi_pmi_cost_whse_eff_gtmp (whse_code, trans_date, cost_whse_code)
92 SELECT /*+ ALL_ROWS */
93 wd.whse_code, wd.trans_date, cwa.cost_whse_code
94 FROM
95 opi_pmi_whse_date_gtmp wd, cm_whse_asc cwa
96 WHERE
97 wd.whse_code = cwa.whse_code
98 AND wd.trans_date BETWEEN cwa.eff_start_date AND cwa.eff_end_date
99 AND cwa.delete_mark = 0;
100
101
102 -- ADD SELF WAREHOUSE IF NO COST WAREHOUSE
103
104 INSERT INTO opi_pmi_cost_whse_eff_gtmp (whse_code, trans_date, cost_whse_code)
105 SELECT /*+ ALL_ROWS */
106 wd.whse_code, wd.trans_date, wd.whse_code
107 FROM
108 opi_pmi_whse_date_gtmp wd, opi_pmi_cost_whse_eff_gtmp e
109 WHERE
110 wd.whse_code = e.whse_code(+)
111 AND wd.trans_date = e.trans_date(+)
112 AND e.cost_whse_code IS NULL;
113
114
115 -- GET DISTINCT LIST OF ORGANIZATION.DATES (FOR FINDING EFFECTIVE PLCY)
116
117 INSERT INTO opi_pmi_orgn_date_gtmp(orgn_code, trans_date)
118 SELECT /*+ ALL_ROWS */ DISTINCT orgn_code, trans_date
119 FROM opi_pmi_cost_param_gtmp;
120
121
122 -- GET THE FISCAL POLICY FOR EACH ORGANIZATION.DATE
123
124 INSERT INTO opi_pmi_orgn_plcy_gtmp
125 (orgn_code, trans_date, co_code, cost_mthd, cost_basis)
126 SELECT /*+ ALL_ROWS */
127 od.orgn_code, od.trans_date, o.co_code, g.gl_cost_mthd, g.cost_basis
128 FROM
129 opi_pmi_orgn_date_gtmp od, sy_orgn_mst o, gl_plcy_mst g
130 WHERE
131 o.orgn_code = od.orgn_code
132 AND g.co_code = o.co_code;
133
134
135 -- ASSOCIATE THE CORRECT CALENDAR PERIOD, DEPENDING ON COST_BASIS
136
137 INSERT INTO opi_pmi_orgn_per_gtmp
138 (orgn_code, trans_date, calendar_code, period_code, period_status)
139 SELECT /*+ ALL_ROWS */ o.orgn_code, o.trans_date,
140 c.prior_calendar_code, c.prior_period_code, c.prior_period_status
141 FROM opi_pmi_cldr_sum_gtmp c, opi_pmi_orgn_plcy_gtmp o
142 WHERE o.cost_basis = 0
143 AND c.co_code = o.co_code
144 AND c.cost_mthd = o.cost_mthd
145 AND o.trans_date BETWEEN c.start_date AND c.end_date;
146
147
148 -- ASSOCIATE THE COST_BASIS=1 CALENDAR PERIOD
149
150 INSERT INTO opi_pmi_orgn_per_gtmp
151 (orgn_code, trans_date, co_code, cost_mthd, cost_basis,
152 calendar_code, period_code, period_status)
153 SELECT /*+ ALL_ROWS */
154 o.orgn_code, o.trans_date, o.co_code, o.cost_mthd, o.cost_basis,
155 c.calendar_code, c.period_code, c.period_status
156 FROM opi_pmi_cldr_sum_gtmp c, opi_pmi_orgn_plcy_gtmp o
157 WHERE o.cost_basis = 1
158 AND c.co_code = o.co_code
159 AND c.cost_mthd = o.cost_mthd
160 AND o.trans_date BETWEEN c.start_date AND c.end_date;
161
162
163 -- ASSIGN GL POLICY, CALENDAR PERIOD, COST WAREHOUSE,
164 -- AND WAREHOUSE-ORG TO EACH PARAMETER ROW
165
166 INSERT INTO opi_pmi_cost_temp1_gtmp
167 (
168 item_id,
169 whse_code,
170 orgn_code,
171 trans_date,
172 cost_whse_code,
173 cost_mthd,
174 cost_basis,
175 co_code,
176 calendar_code,
177 period_code,
178 period_status,
179 whse_orgn_code)
180 SELECT /*+ ORDERED */
181 param.item_id,
182 param.whse_code,
183 param.orgn_code,
184 param.trans_date,
185 cw.cost_whse_code,
186 per.cost_mthd,
187 per.cost_basis,
188 per.co_code,
189 per.calendar_code,
190 per.period_code,
191 per.period_status,
192 w.orgn_code
193 FROM
194 opi_pmi_cost_whse_eff_gtmp cw,
195 ic_whse_mst w,
196 opi_pmi_cost_param_gtmp param,
197 opi_pmi_orgn_per_gtmp per
198 WHERE
199 cw.whse_code = param.whse_code
200 AND cw.trans_date = param.trans_date
201 AND per.orgn_code(+) = param.orgn_code
202 AND per.trans_date(+) = param.trans_date
203 AND w.whse_code || '' = cw.cost_whse_code;
204
205
206 -- GET COSTS USING PARAM ORGN_CODE
207
208 pv_sql := '
209 INSERT INTO opi_pmi_cost_temp2_gtmp
210 (
211 item_id,
212 whse_code,
213 orgn_code,
214 trans_date,
215 cost_whse_code,
216 cost_mthd,
217 cost_basis,
218 co_code,
219 calendar_code,
220 period_code,
221 period_status,
222 whse_orgn_code,
223 total_cost,
224 last_update_date)
225 SELECT /*+ ALL_ROWS */
226 t1.item_id,
227 t1.whse_code,
228 t1.orgn_code,
229 t1.trans_date,
230 t1.cost_whse_code,
231 t1.cost_mthd,
232 t1.cost_basis,
233 t1.co_code,
234 t1.calendar_code,
235 t1.period_code,
236 t1.period_status,
237 t1.whse_orgn_code,
238 c.acctg_cost,
239 c.last_update_date
240 FROM
241 opi_pmi_cost_temp1_gtmp t1,
242 (select
243 item_id, whse_code, orgn_code, cost_mthd_code,
244 calendar_code, period_code, acctg_cost, last_update_date
245 from
246 (select
247 item_id, whse_code, orgn_code, cost_mthd_code,
248 calendar_code, period_code, acctg_cost, last_update_date,
249 first_value(last_update_date)
250 over (partition by item_id, whse_code, orgn_code, cost_mthd_code, calendar_code, period_code
251 order by last_update_date desc) final_update_date
252 from gl_item_cst
253 )
254 where last_update_date = final_update_date
255 )c
256 WHERE
257 c.item_id(+) = t1.item_id
258 AND c.orgn_code(+) = t1.orgn_code
259 AND c.whse_code(+) = t1.cost_whse_code
260 AND c.cost_mthd_code(+) = t1.cost_mthd
261 AND c.calendar_code(+) = t1.calendar_code
262 AND c.period_code(+) = t1.period_code';
263
264 EXECUTE IMMEDIATE pv_sql;
265
266
267 -- IF COST WASN'T FOUND USING PARAM-ORG, GET COSTS USING *WAREHOUSE-ORG*
268
269 pv_sql := '
270 INSERT INTO opi_pmi_cost_temp3_gtmp
271 (
272 item_id,
273 whse_code,
274 orgn_code,
275 trans_date,
276 cost_whse_code,
277 cost_mthd,
278 cost_basis,
279 co_code,
280 calendar_code,
281 period_code,
282 period_status,
283 whse_orgn_code,
284 total_cost,
285 last_update_date)
286 SELECT /*+ ALL_ROWS */
287 t2.item_id,
288 t2.whse_code,
289 t2.orgn_code,
290 t2.trans_date,
291 t2.cost_whse_code,
292 t2.cost_mthd,
293 t2.cost_basis,
294 t2.co_code,
295 t2.calendar_code,
296 t2.period_code,
297 t2.period_status,
298 t2.whse_orgn_code,
299 c.acctg_cost,
300 c.last_update_date
301 FROM
302 opi_pmi_cost_temp2_gtmp t2,
303 (select
304 item_id, whse_code, orgn_code, cost_mthd_code,
305 calendar_code, period_code, acctg_cost, last_update_date
306 from
307 (select
308 item_id, whse_code, orgn_code, cost_mthd_code,
309 calendar_code, period_code, acctg_cost, last_update_date,
310 first_value(last_update_date)
311 over (partition by item_id, whse_code, orgn_code, cost_mthd_code, calendar_code, period_code
312 order by last_update_date desc) final_update_date
313 from gl_item_cst
314 )
315 where last_update_date = final_update_date
316 )c
317 WHERE
318 t2.total_cost IS NULL
319 AND c.item_id(+) = t2.item_id
320 AND c.orgn_code(+) = t2.whse_orgn_code
321 AND c.whse_code(+) = t2.cost_whse_code
322 AND c.cost_mthd_code(+) = t2.cost_mthd
323 AND c.calendar_code(+) = t2.calendar_code
324 AND c.period_code(+) = t2.period_code';
325
326 EXECUTE IMMEDIATE pv_sql;
327
328
329 -- RETURN COSTS FOUND USING PARAM-ORG
330
331 INSERT INTO opi_pmi_cost_result_gtmp (
332 item_id, whse_code, orgn_code, trans_date, total_cost, status, last_update_date, period_status)
333 SELECT
334 item_id, whse_code, orgn_code, trans_date, total_cost, 1, last_update_date, period_status
335 FROM
336 opi_pmi_cost_temp2_gtmp
337 WHERE
338 total_cost IS NOT NULL;
339
340
341 -- RETURN COSTS FOUND USING WHSE-ORG.
342
343 INSERT INTO opi_pmi_cost_result_gtmp (
344 item_id, whse_code, orgn_code, trans_date, total_cost,
345 status, last_update_date, period_status)
346 SELECT
347 item_id, whse_code, orgn_code, trans_date, total_cost,
348 1, last_update_date, period_status
349 FROM
350 opi_pmi_cost_temp3_gtmp
351 WHERE total_cost IS NOT NULL;
352
353
354 -- RETURN STATUS = -1 IF NO COSTS WAS FOUND
355
356 INSERT INTO opi_pmi_cost_result_gtmp (
357 item_id, whse_code, orgn_code, trans_date, total_cost,
358 status, last_update_date, period_status)
359 SELECT
360 item_id, whse_code, orgn_code, trans_date, total_cost,
361 -1, last_update_date, period_status
362 FROM
363 opi_pmi_cost_temp3_gtmp
364 WHERE total_cost IS NULL
365 AND cost_basis IS NOT NULL
366 AND item_id IS NOT NULL
367 AND whse_code IS NOT NULL
368 AND orgn_code IS NOT NULL
369 AND trans_date IS NOT NULL;
370
371
372 -- IF ANY PARAMETERS WERE MISSING, RETURN STATUS = -2 .
373
374 INSERT INTO opi_pmi_cost_result_gtmp (
375 item_id, whse_code, orgn_code, trans_date, total_cost, status)
376 SELECT
377 item_id, whse_code, orgn_code, trans_date, NULL, -2
378 FROM
379 opi_pmi_cost_temp1_gtmp
380 WHERE item_id IS NULL
381 OR whse_code IS NULL
382 OR orgn_code IS NULL
383 OR trans_date IS NULL;
384
385
386 -- IF GL POLICY WAS NOT FOUND, RETURN STATUS = -3
387
388 INSERT INTO opi_pmi_cost_result_gtmp (
389 item_id, whse_code, orgn_code, trans_date, total_cost, status)
390 SELECT
391 item_id, whse_code, orgn_code, trans_date, NULL, -3
392 FROM
393 opi_pmi_cost_temp1_gtmp
394 WHERE cost_basis IS NULL
395 AND item_id IS NOT NULL
396 AND whse_code IS NOT NULL
397 AND orgn_code IS NOT NULL
398 AND trans_date IS NOT NULL;
399
400 END get_cost;
401
402 END opi_pmi_cost;