[Home] [Help]
8: --* *
9: --* Package GMF_LOT_COSTING_PUB *
10: --* --------------------------- *
11: --* This package contains a publically callable procedure ROLLUP_LOT_COSTS together with several *
12: --* utility procedures that are called by it. For individual procedures' descriptions, see the *
13: --* description in front of each one. *
14: --* *
15: --* Author: Paul J Schofield, OPM Development EMEA *
16: --* Date: September 2003 *
146: --* 19-Mar-2004 PJS Bug 3514108 - RMA fixes and Bug 3513668 - Requisitions *
147: --* *
148: --* 22-Mar-2004 PJS Reworked 3485915 and 3476427. Also tightened up error messaging for the *
149: --* cases where clashing parameters are specified. 3486228 also fixed by *
150: --* setting the transaction quantity to the residual quantity when a balance *
151: --* is flipped positive again. *
152: --* *
153: --* 24-Mar-2004 umoogala Lot Cost Adjustments. Updating onhand_qty in adjs table. Now trans_id in *
154: --* material txn table is -ve of cost_trans_id. *
179: --* unassociated products to final step *
180: --* *
181: --* 15-Apr-2004 PJS Various issues with mixed mode accounting in the same batch. Anything *
182: --* that is not a lot costed (co) product now has its costs subtracted from *
183: --* the total costs accrued to date before the residual costs are shared *
184: --* amongst the remaining lot costed products. Also tidied up a bit so that *
185: --* presets no longer cause GSCC warnings. *
186: --* *
187: --* 16-Apr-2004 PJS Sub Ledger Update has a problem with the costs generated for batch *
313: --* HALUTHRA BUG 7249505 In case of return to vendor process the transaction as an adjustment
314: --* 21-MAY-2009 Bug 6165255 and BUG 8330088 HARI LUTHRA
315: --* Modified the cursor unassociated_ings_cursor and unassociated_prds_cursor so as to include ingrediant transactions
316: --* which are non lot controlled and also to take the primary qty and primary UOM of item from mtl_material_transactions
317: --* and mtl_system_items_b instead of transaction_qty and transaction_uom to avoid the dual_uom issues.
318: --* 27-MAY-2009 Hari Luthra Bug 5473138/8533290
319: --* Modified item_cost_cursor , item_cost_detail_cursor to avoid issues with cost warehouse assosciation
320: --* Also modified query which picks up all the transactions on the basis of the parameters fed while running Lot Cost Process
321: --* Made changes in this query to handle phantom item issues.
598: receipt_qty NUMBER;
599: receipt_unit_cost NUMBER;
600: receipt_uom VARCHAR2(3);
601: receipt_ccy VARCHAR2(4);
602: l_residual_qty NUMBER;
603: l_flg_ind VARCHAR2(1);
604:
605: component_class_id cm_cmpt_mst.cost_cmpntcls_id%TYPE;
606: cost_analysis_code cm_alys_mst.cost_analysis_code%TYPE;
779: ( grsd.dep_step_id, gbs.actual_step_qty, gbs.step_qty_um, 0
780: , CAST
781: ( MULTISET
782: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
783: FROM DUAL
784: ) AS SYSTEM.gmf_cost_tab
785: )
786: , CAST
787: ( MULTISET
785: )
786: , CAST
787: ( MULTISET
788: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
789: FROM DUAL
790: ) AS SYSTEM.gmf_cost_tab
791: )
792: , CAST
793: ( MULTISET
791: )
792: , CAST
793: ( MULTISET
794: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
795: FROM DUAL
796: ) AS SYSTEM.gmf_cost_tab
797: )
798: , NULL, NULL
799: , CAST
826: ( g.batchstep_id, g.actual_step_qty, g.step_qty_um, 0
827: , CAST
828: ( MULTISET
829: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
830: FROM DUAL
831: ) AS SYSTEM.gmf_cost_tab
832: )
833: , CAST
834: ( MULTISET
832: )
833: , CAST
834: ( MULTISET
835: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
836: FROM DUAL
837: ) AS SYSTEM.gmf_cost_tab
838: )
839: , CAST
840: ( MULTISET
838: )
839: , CAST
840: ( MULTISET
841: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
842: FROM DUAL
843: ) AS SYSTEM.gmf_cost_tab
844: )
845: , NULL, NULL
846: , CAST
846: , CAST
847: ( MULTISET
848: (
849: SELECT SYSTEM.gmf_dependency_type(NULL, NULL, NULL, NULL)
850: FROM DUAL
851: ) AS SYSTEM.gmf_dependency_tab
852: )
853: )
854: FROM
876: ( 1, 0, NULL, 0
877: , CAST
878: ( MULTISET
879: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
880: FROM DUAL
881: ) AS SYSTEM.gmf_cost_tab
882: )
883: , CAST
884: ( MULTISET
882: )
883: , CAST
884: ( MULTISET
885: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
886: FROM DUAL
887: ) AS SYSTEM.gmf_cost_tab
888: )
889: , CAST
890: ( MULTISET
888: )
889: , CAST
890: ( MULTISET
891: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
892: FROM DUAL
893: ) AS SYSTEM.gmf_cost_tab
894: )
895: , NULL, NULL
896: , CAST
896: , CAST
897: ( MULTISET
898: (
899: SELECT SYSTEM.gmf_dependency_type(NULL, NULL, NULL, NULL)
900: FROM DUAL
901: ) AS SYSTEM.gmf_dependency_tab
902: )
903: )
904: FROM DUAL;
900: FROM DUAL
901: ) AS SYSTEM.gmf_dependency_tab
902: )
903: )
904: FROM DUAL;
905:
906:
907: --**********************************************************************************************
908: --* *
918: gbs.actual_step_qty,
919: gbs.step_qty_um,
920: 0,
921: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
922: FROM DUAL) AS SYSTEM.gmf_cost_tab
923: ),
924: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
925: FROM DUAL) AS SYSTEM.gmf_cost_tab
926: ),
921: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
922: FROM DUAL) AS SYSTEM.gmf_cost_tab
923: ),
924: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
925: FROM DUAL) AS SYSTEM.gmf_cost_tab
926: ),
927: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
928: FROM DUAL) AS SYSTEM.gmf_cost_tab
929: ),
924: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
925: FROM DUAL) AS SYSTEM.gmf_cost_tab
926: ),
927: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
928: FROM DUAL) AS SYSTEM.gmf_cost_tab
929: ),
930: NULL,
931: NULL,
932: CAST
930: NULL,
931: NULL,
932: CAST
933: (MULTISET (SELECT SYSTEM.gmf_dependency_type (NULL, NULL, NULL, NULL)
934: FROM DUAL) AS SYSTEM.gmf_dependency_tab
935: )
936: )
937: FROM gme_batch_steps gbs
938: WHERE gbs.batch_id = p_batch_id ;
992: gmd.plan_qty,
993: gmd.actual_qty,
994: gmd.dtl_um,
995: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
996: FROM DUAL) AS SYSTEM.gmf_cost_tab
997: ), -- Bug 7317270,
998: gmd.cost_alloc
999: )
1000: FROM mtl_system_items_b iimb,
1037: mmt.transaction_id
1038: ), mtln.lot_number ; -- B9131983
1039:
1040: /*HALUTHRA BUG 6165255 changed from mmt.transaction_quantity to mmt.primary_quantity and from
1041: mmt.transaction_uom to iimb.primary_uom_code to avoid dual uom issue*/
1042: /* HALUTHRA BUG 8330088 Added the join condition in mtln case so as to include the records for non lot controlled
1043: items . also changed from mtln.transaction_date to Decode(mtln.transaction_date,null,mmt.transaction_date,mtln.transaction_date)
1044: so that date gets populated in case the item is non lot controlled. */
1045:
1063: plan_qty,
1064: actual_qty,
1065: dtl_um,
1066: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
1067: FROM DUAL) AS SYSTEM.gmf_cost_tab
1068: ), -- Bug 7317270
1069: cost_alloc
1070: )
1071: FROM (
1141: ) ;
1142:
1143:
1144: /*HALUTHRA BUG 6165255 changed from mmt.transaction_quantity to mmt.primary_quantity and from
1145: mmt.transaction_uom to iimb.primary_uom_code to avoid dual uom issue*/
1146: /* HALUTHRA BUG 8330088 Added the join condition in mtln case so as to include the records for non lot controlled
1147: items . also changed from mtln.transaction_date to Decode(mtln.transaction_date,null,mmt.transaction_date,mtln.transaction_date)
1148: so that date gets populated in case the item is non lot controlled. */
1149:
1167: gmd.plan_qty,
1168: gmd.actual_qty,
1169: gmd.dtl_um,
1170: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
1171: FROM DUAL) AS SYSTEM.gmf_cost_tab
1172: ), -- Bug 7317270
1173: gmd.cost_alloc
1174: )
1175: FROM mtl_system_items_b iimb,
1212: gme.plan_qty,
1213: gme.actual_qty,
1214: gme.dtl_um,
1215: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
1216: FROM DUAL) AS SYSTEM.gmf_cost_tab
1217: ), -- Bug 7317270
1218: gme.cost_alloc
1219: )
1220: FROM mtl_system_items_b iimb,
1276: grt.trans_qty_um,
1277: grt.trans_date,
1278: 0,
1279: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
1280: FROM DUAL) AS SYSTEM.gmf_cost_tab
1281: ) -- Bug 7317270
1282: )
1283: FROM gme_resource_txns grt,
1284: gme_batch_step_resources gbsr
5017: --* 03-Feb-2005 - Bug 4144329 - Dinesh Vadivel - If there is no cost defined for Resource
5018: --* then don't stop by setting the cost as uncostable. Just give a warning and ignore
5019: --* the resources.
5020: --* 12-MAR-2009 Hari Luthra Bug 7317270
5021: --* FETCH of the individual cusrsor have initialization like
5022: --* l_step_tab(i).resources := SYSTEM.gmf_rsrc_tab();
5023: --********************************************************************************************************
5024:
5025: PROCEDURE process_batch
5529: END IF;
5530: --}
5531: END IF;
5532:
5533: -- Load the transactions and cost them individually
5534:
5535: FOR i in 1..l_step_tab.count
5536: LOOP
5537:
5957: END IF;
5958:
5959: k_period_id := 0 ;
5960: SELECT to_char(TRUNC(l_step_tab(i).materials(j).trans_date),'J')
5961: into k_date from dual ;
5962:
5963: IF period_tab.EXISTS(k_date) THEN
5964: -- fnd_file.put_line(fnd_file.log,'period_id = '||
5965: -- period_tab(k_date).period_id );
6207: END IF;
6208:
6209: k_period_id := 0 ;
6210: select to_char(TRUNC(l_step_tab(i).materials(j).trans_date),'J')
6211: into k_date from dual ;
6212:
6213: IF period_tab.EXISTS(k_date) THEN
6214: -- fnd_file.put_line(fnd_file.log,'period_id = '||
6215: -- period_tab(k_date).period_id );
6448: -- their actual step quantities. If there are no next steps we have reached a terminal
6449: -- step in the routing.
6450:
6451: -- Note that if anything is yielded at the current step, the cost of it is subtracted
6452: -- and only the residual costs are rolled into the next steps.
6453:
6454: -- We have the following values for each step, stored in table l_step_tab:
6455:
6456: -- current_step_id (ie the batchstep_id of the current step)
6582: -- Bug 3548217
6583: -- If we're on a terminal step we have to set up the cost factor
6584: -- so that all costs will be absorbed by the products yielded. If
6585: -- the step is higher up the chain the we use the step qty instead so that all
6586: -- residual costs are passed on to the next steps.
6587: /** Bug 9616762 Front port of 4601607 No need for different logic for products yielded
6588: * in terminal step or non-terminal steps
6589: */
6590:
8206:
8207: END IF;
8208:
8209: /***** Bug 4094132 - Added the following Delete - Start *****/
8210: -- Delete the residual transactions for which header is final costed
8211: -- but because of reversal, one more transaction got created for the same header.
8212:
8213: DELETE
8214: FROM gmf_material_lot_cost_txns t
8639: /*=========================================================
8640: PROCEDURE : perform_weighted_average
8641:
8642: DESCRIPTION This procedure performs weighted avererage of
8643: individual lots
8644: AUTHOR : Sukarna Reddy INVCONV June 2005
8645: ==========================================================*/
8646:
8647: PROCEDURE perform_weighted_average
10040: TRUNC(start_date) start_date,
10041: TRUNC(end_date) end_date
10042: FROM gmf_period_statuses c,
10043: (SELECT ROWNUM just_a_column
10044: FROM dual
10045: CONNECT BY LEVEL <= 7400 )
10046: WHERE cost_type_id = l_default_cost_type_id
10047: AND legal_entity_id = l_le_id
10048: AND delete_mark = 0
11334: -- is then used to create a new cost. The 'feature' though is that the new cost is created as
11335: -- the entire quantity has been used in the calculations but the onhand it is set against is
11336: -- the difference beween the old and new quantities. Got that?
11337:
11338: -- PK Bug 12913227 Bypassing residual quantity check for LC adjustments.
11339:
11340: IF (transaction_row.transaction_source_type_id <> LC_ADJUSTMENT) THEN
11341:
11342: l_residual_qty := old_cost.onhand_qty + transaction_row.trans_qty;
11338: -- PK Bug 12913227 Bypassing residual quantity check for LC adjustments.
11339:
11340: IF (transaction_row.transaction_source_type_id <> LC_ADJUSTMENT) THEN
11341:
11342: l_residual_qty := old_cost.onhand_qty + transaction_row.trans_qty;
11343:
11344: END IF;
11345:
11346: IF (old_cost.onhand_qty < 0 AND l_residual_qty <= 0 AND transaction_row.transaction_source_type_id <> LC_ADJUSTMENT) THEN -- PK Bug 12913227
11342: l_residual_qty := old_cost.onhand_qty + transaction_row.trans_qty;
11343:
11344: END IF;
11345:
11346: IF (old_cost.onhand_qty < 0 AND l_residual_qty <= 0 AND transaction_row.transaction_source_type_id <> LC_ADJUSTMENT) THEN -- PK Bug 12913227
11347: IF l_debug_level >= l_debug_level_high THEN
11348: fnd_file.put_line
11349: (fnd_file.log,'Onhand balance is currently -ve and will remain -ve. Processing txn as an ADJI');
11350: END IF;
11351:
11352: process_adjustment;
11353:
11354: ELSE
11355: IF (old_cost.onhand_qty < 0 AND l_residual_qty > 0 AND transaction_row.transaction_source_type_id <> LC_ADJUSTMENT ) THEN -- PK Bug 12913227
11356:
11357: IF l_debug_level >= l_debug_level_high THEN
11358: fnd_file.put_line
11359: (fnd_file.log,'Onhand balance is currently -ve and will go +ve. Clearing old balance to zero');
11372: (fnd_file.log,'Ignore Prior Onhand balance for LCM Adjustments. Processing normally');
11373: END IF;
11374: END IF;
11375:
11376: l_residual_qty := transaction_row.trans_qty;
11377: END IF;
11378:
11379:
11380:
11643: process_lc_adjustments();
11644: -- AF
11645: END IF;
11646:
11647: IF l_residual_qty <> transaction_row.trans_qty THEN
11648: -- The transaction that has been processed flipped a negative onhand balance back to positive
11649: -- so we need to adjust the onhand balance in the header to the residual balance
11650:
11651: IF l_debug_level >= l_debug_level_high THEN
11645: END IF;
11646:
11647: IF l_residual_qty <> transaction_row.trans_qty THEN
11648: -- The transaction that has been processed flipped a negative onhand balance back to positive
11649: -- so we need to adjust the onhand balance in the header to the residual balance
11650:
11651: IF l_debug_level >= l_debug_level_high THEN
11652: fnd_file.put_line
11653: (fnd_file.log,'Onhand balance has flipped from -ve to +ve. Setting onhand qty to residual qty');
11649: -- so we need to adjust the onhand balance in the header to the residual balance
11650:
11651: IF l_debug_level >= l_debug_level_high THEN
11652: fnd_file.put_line
11653: (fnd_file.log,'Onhand balance has flipped from -ve to +ve. Setting onhand qty to residual qty');
11654: END IF;
11655:
11656: -- Bug 12394608 Need to use 'AND inventory_item_id = transaction_row.inventory_item_id' as well
11657:
11655:
11656: -- Bug 12394608 Need to use 'AND inventory_item_id = transaction_row.inventory_item_id' as well
11657:
11658: UPDATE gmf_lot_costs
11659: SET onhand_qty = l_residual_qty
11660: WHERE header_id = (SELECT max(header_id)
11661: FROM gmf_lot_costs
11662: WHERE organization_id = transaction_row.orgn_id
11663: AND lot_number = transaction_row.lot_number
11663: AND lot_number = transaction_row.lot_number
11664: AND inventory_item_id = transaction_row.inventory_item_id)
11665: RETURNING header_id INTO new_cost.header_id;
11666:
11667: -- B3486228 Also set the transaction qty to the residual
11668: UPDATE gmf_material_lot_cost_txns
11669: SET new_onhand_qty = l_residual_qty
11670: WHERE transaction_id = transaction_row.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
11671: AND cost_header_id = new_cost.header_id;
11665: RETURNING header_id INTO new_cost.header_id;
11666:
11667: -- B3486228 Also set the transaction qty to the residual
11668: UPDATE gmf_material_lot_cost_txns
11669: SET new_onhand_qty = l_residual_qty
11670: WHERE transaction_id = transaction_row.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
11671: AND cost_header_id = new_cost.header_id;
11672: END IF;
11673: END IF;