[Home] [Help]
186: l_begin_quantity
187: ,l_market_value
188: FROM
189: cst_pac_item_costs cpic
190: , cst_pac_quantity_layers cpql
191: WHERE cpic.pac_period_id = l_current_period
192: AND cpic.inventory_item_id = p_item_id
193: AND cpic.cost_group_id = p_cost_group_id
194: AND cpic.cost_layer_id = cpql.cost_layer_id;
236: -- : p_prg_id prg id
237: -- : p_prg_appl_id prg appl id
238: -- COMMENT : This procedure finds the delta balance
239: -- : between periods and records it in
240: -- : cst_pac_quantity_layers for each
241: -- : period.
242: -- : This is neccessary for the lifo calcualtion
243: -- : the delta quantity may be a negative number.
244: -- PRE-COND : Calacualte the delta inventory per item per period
335: ELSE
336: l_delta_quantity := l_current_total;
337: END IF;
338:
339: UPDATE cst_pac_quantity_layers
340: SET
341: last_updated_by = p_user_id
342: , last_update_date = sysdate
343: , last_update_login = p_login_id
410: l_delta_quantity := l_current_total;
411: END IF;
412:
413:
414: UPDATE cst_pac_quantity_layers
415: SET
416: last_updated_by = p_user_id
417: , last_update_date = sysdate
418: , last_update_login = p_login_id
445: -- : p_cost_type_id cost type id
446: -- : p_user_id user id
447: -- COMMENT : Calculate the Incremental LIFO item cost and populate
448: -- CST_PAC_ITEM_COSTS.ITEM_COST with that value.
449: -- PRE-COND : The delta quantity can be pulled for CST_PAC_QUANTITY_LAYERS.
450: -- The weighted average cost per item per period can be easily
451: -- calculated
452: --=========================================================================
453: PROCEDURE calc_lifo_cost
557: INTO
558: l_quantity_layers
559: , l_wac_cost
560: FROM
561: cst_pac_item_costs cpic, cst_pac_quantity_layers cpql
562: WHERE cpic.pac_period_id = cpql.pac_period_id
563: AND cpic.cost_group_id = cpql.cost_group_id
564: AND cpic.inventory_item_id = cpql.inventory_item_id
565: AND cpic.pac_period_id = l_current_period
577: INTO
578: l_quantity_layers
579: , l_wac_cost
580: FROM
581: cst_pac_item_costs cpic, cst_pac_quantity_layers cpql
582: WHERE cpic.pac_period_id = cpql.pac_period_id
583: AND cpic.cost_group_id = cpql.cost_group_id
584: AND cpic.inventory_item_id = cpql.inventory_item_id
585: AND cpic.pac_period_id = l_current_period
588: END IF;
589:
590: -- finds the delta quantity for the period being calculated for
591: -- The delta has been previously calculated and populated in
592: -- cst_pac_quantity_layers
593:
594: l_x_quant := l_quantity_layers;
595:
596: -- if there is a negative delta quantity. Work out
617: INTO
618: l_y_quant
619: , l_wac_cost
620: , j_market_value
621: FROM cst_pac_item_costs cpic, cst_pac_quantity_layers cpql
622: WHERE cpic.pac_period_id = cpql.pac_period_id
623: AND cpic.cost_group_id = cpql.cost_group_id
624: AND cpic.inventory_item_id = cpql.inventory_item_id
625: AND cpic.pac_period_id = l_delta_period
945: cst_cost_groups cg
946: , cst_le_cost_types clt
947: , cst_pac_periods pp
948: , cst_pac_item_costs pic
949: , cst_pac_quantity_layers pql
950: , mtl_system_items msi
951: , mtl_system_items_kfv kfv
952: WHERE clt.legal_entity = p_legal_entity_id
953: AND pp.legal_entity = clt.legal_entity
1004: cst_cost_groups cg
1005: , cst_le_cost_types clt
1006: , cst_pac_periods pp
1007: , cst_pac_item_costs pic
1008: , cst_pac_quantity_layers pql
1009: , mtl_system_items msi
1010: , mtl_system_items_kfv kfv
1011: WHERE clt.legal_entity = p_legal_entity_id
1012: AND pp.legal_entity = clt.legal_entity
1192: cst_cost_groups cg
1193: , cst_le_cost_types clt
1194: , cst_pac_periods pp
1195: , cst_pac_item_costs pic
1196: , cst_pac_quantity_layers pql
1197: , mtl_system_items msi
1198: , mtl_system_items_kfv kfv
1199: WHERE clt.legal_entity = p_legal_entity_id
1200: AND pp.legal_entity = clt.legal_entity
1248: cst_cost_groups cg
1249: , cst_le_cost_types clt
1250: , cst_pac_periods pp
1251: , cst_pac_item_costs pic
1252: , cst_pac_quantity_layers pql
1253: , mtl_system_items msi
1254: , mtl_system_items_kfv kfv
1255: WHERE clt.legal_entity = p_legal_entity_id
1256: AND pp.legal_entity = clt.legal_entity
1318: cst_cost_groups cg
1319: , cst_le_cost_types clt
1320: , cst_pac_periods pp
1321: , cst_pac_item_costs pic
1322: , cst_pac_quantity_layers pql
1323: , mtl_system_items msi
1324: , mtl_system_items_kfv kfv
1325: WHERE clt.legal_entity = p_legal_entity_id
1326: AND pp.legal_entity = clt.legal_entity
1379: cst_cost_groups cg
1380: , cst_le_cost_types clt
1381: , cst_pac_periods pp
1382: , cst_pac_item_costs pic
1383: , cst_pac_quantity_layers pql
1384: , mtl_system_items msi
1385: , mtl_system_items_kfv kfv
1386: WHERE clt.legal_entity = p_legal_entity_id
1387: AND pp.legal_entity = clt.legal_entity
2071: ,c_item_id NUMBER)
2072: IS
2073: SELECT
2074: begin_layer_quantity
2075: FROM CST_PAC_QUANTITY_LAYERS
2076: WHERE pac_period_id = c_period_id
2077: AND cost_group_id = c_cost_group_id
2078: AND inventory_item_id = c_item_id;
2079:
2084: ,c_cost_group_id NUMBER)
2085: IS
2086: SELECT
2087: layer_quantity
2088: FROM CST_PAC_QUANTITY_LAYERS
2089: WHERE pac_period_id = c_period_id
2090: AND inventory_item_id = c_item_id
2091: AND cost_group_id = c_cost_group_id;
2092:
2214: < l_first_period_end_date);
2215:
2216:
2217: -- Delete all the prior item quantity layers
2218: DELETE CST_PAC_QUANTITY_LAYERS
2219: WHERE cost_group_id = p_cost_group_id
2220: AND inventory_item_id = p_item_id
2221: AND pac_period_id IN (SELECT pac_period_id
2222: FROM CST_PAC_PERIODS
2303: WHERE pac_period_id = l_period_id
2304: AND inventory_item_id = p_item_id
2305: AND cost_group_id = p_cost_group_id;
2306:
2307: DELETE CST_PAC_QUANTITY_LAYERS
2308: WHERE pac_period_id = l_period_id
2309: AND inventory_item_id = p_item_id
2310: AND cost_group_id = p_cost_group_id;
2311:
2324: AND inventory_item_id = p_item_id
2325: AND cost_group_id = p_cost_group_id;
2326:
2327: -- add delta to the previous period quantity layer
2328: UPDATE CST_PAC_QUANTITY_LAYERS
2329: SET layer_quantity = layer_quantity + l_layer_quantity
2330: WHERE pac_period_id = l_period_id
2331: AND inventory_item_id = p_item_id
2332: AND cost_group_id = p_cost_group_id;
2366: WHERE pac_period_id = p_first_period_id
2367: AND inventory_item_id = p_item_id
2368: AND cost_group_id = p_cost_group_id;
2369:
2370: DELETE CST_PAC_QUANTITY_LAYERS
2371: WHERE pac_period_id = p_first_period_id
2372: AND inventory_item_id = p_item_id
2373: AND cost_group_id = p_cost_group_id;
2374:
2377:
2378: ELSE
2379: -- Update begin quantity to 0
2380: -- Update delta to the end quantity
2381: UPDATE CST_PAC_QUANTITY_LAYERS
2382: SET begin_layer_quantity = 0,
2383: layer_quantity = (SELECT total_layer_quantity
2384: FROM CST_PAC_ITEM_COSTS
2385: WHERE pac_period_id = p_first_period_id