[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 *
457: receipt_qty NUMBER;
458: receipt_unit_cost NUMBER;
459: receipt_uom VARCHAR2(3);
460: receipt_ccy VARCHAR2(4);
461: l_residual_qty NUMBER;
462: l_flg_ind NUMBER;
463:
464: component_class_id cm_cmpt_mst.cost_cmpntcls_id%TYPE;
465: cost_analysis_code cm_alys_mst.cost_analysis_code%TYPE;
615: ( grsd.dep_step_id, gbs.actual_step_qty, gbs.step_qty_um, 0
616: , CAST
617: ( MULTISET
618: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
619: FROM DUAL
620: ) AS SYSTEM.gmf_cost_tab
621: )
622: , CAST
623: ( MULTISET
621: )
622: , CAST
623: ( MULTISET
624: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
625: FROM DUAL
626: ) AS SYSTEM.gmf_cost_tab
627: )
628: , CAST
629: ( MULTISET
627: )
628: , CAST
629: ( MULTISET
630: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
631: FROM DUAL
632: ) AS SYSTEM.gmf_cost_tab
633: )
634: , NULL, NULL
635: , CAST
662: ( g.batchstep_id, g.actual_step_qty, g.step_qty_um, 0
663: , CAST
664: ( MULTISET
665: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
666: FROM DUAL
667: ) AS SYSTEM.gmf_cost_tab
668: )
669: , CAST
670: ( MULTISET
668: )
669: , CAST
670: ( MULTISET
671: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
672: FROM DUAL
673: ) AS SYSTEM.gmf_cost_tab
674: )
675: , CAST
676: ( MULTISET
674: )
675: , CAST
676: ( MULTISET
677: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
678: FROM DUAL
679: ) AS SYSTEM.gmf_cost_tab
680: )
681: , NULL, NULL
682: , CAST
682: , CAST
683: ( MULTISET
684: (
685: SELECT SYSTEM.gmf_dependency_type(NULL, NULL, NULL, NULL)
686: FROM DUAL
687: ) AS SYSTEM.gmf_dependency_tab
688: )
689: )
690: FROM
712: ( 1, 0, NULL, 0
713: , CAST
714: ( MULTISET
715: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
716: FROM DUAL
717: ) AS SYSTEM.gmf_cost_tab
718: )
719: , CAST
720: ( MULTISET
718: )
719: , CAST
720: ( MULTISET
721: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
722: FROM DUAL
723: ) AS SYSTEM.gmf_cost_tab
724: )
725: , CAST
726: ( MULTISET
724: )
725: , CAST
726: ( MULTISET
727: ( SELECT SYSTEM.gmf_cost_type( 0, ' ', 0, 0, 0)
728: FROM DUAL
729: ) AS SYSTEM.gmf_cost_tab
730: )
731: , NULL, NULL
732: , CAST
732: , CAST
733: ( MULTISET
734: (
735: SELECT SYSTEM.gmf_dependency_type(NULL, NULL, NULL, NULL)
736: FROM DUAL
737: ) AS SYSTEM.gmf_dependency_tab
738: )
739: )
740: FROM DUAL;
736: FROM DUAL
737: ) AS SYSTEM.gmf_dependency_tab
738: )
739: )
740: FROM DUAL;
741:
742:
743: --**********************************************************************************************
744: --* *
754: gbs.actual_step_qty,
755: gbs.step_qty_um,
756: 0,
757: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
758: FROM DUAL) AS SYSTEM.gmf_cost_tab
759: ),
760: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
761: FROM DUAL) AS SYSTEM.gmf_cost_tab
762: ),
757: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
758: FROM DUAL) AS SYSTEM.gmf_cost_tab
759: ),
760: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
761: FROM DUAL) AS SYSTEM.gmf_cost_tab
762: ),
763: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
764: FROM DUAL) AS SYSTEM.gmf_cost_tab
765: ),
760: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
761: FROM DUAL) AS SYSTEM.gmf_cost_tab
762: ),
763: CAST (MULTISET (SELECT SYSTEM.gmf_cost_type (0, ' ', 0, 0, 0)
764: FROM DUAL) AS SYSTEM.gmf_cost_tab
765: ),
766: NULL,
767: NULL,
768: CAST
766: NULL,
767: NULL,
768: CAST
769: (MULTISET (SELECT SYSTEM.gmf_dependency_type (NULL, NULL, NULL, NULL)
770: FROM DUAL) AS SYSTEM.gmf_dependency_tab
771: )
772: )
773: FROM gme_batch_steps gbs
774: WHERE gbs.batch_id = p_batch_id ;
4740: fnd_file.put_line
4741: (fnd_file.log,'After explosion, '||to_char(l_step_tab.count)||' steps loaded');
4742: END IF;
4743:
4744: -- Load the transactions and cost them individually
4745:
4746: FOR i in 1..l_step_tab.count
4747: LOOP
4748:
5491: -- their actual step quantities. If there are no next steps we have reached a terminal
5492: -- step in the routing.
5493:
5494: -- Note that if anything is yielded at the current step, the cost of it is subtracted
5495: -- and only the residual costs are rolled into the next steps.
5496:
5497: -- We have the following values for each step, stored in table l_step_tab:
5498:
5499: -- current_step_id (ie the batchstep_id of the current step)
5617: -- Bug 3548217
5618: -- If we're on a terminal step we have to set up the cost factor
5619: -- so that all costs will be absorbed by the products yielded. If
5620: -- the step is higher up the chain the we use the step qty instead so that all
5621: -- residual costs are passed on to the next steps.
5622:
5623: IF l_step_tab(i).dependencies(1).step_index IS NULL
5624: THEN
5625:
6569:
6570: END IF;
6571:
6572: /***** Bug 4094132 - Added the following Delete - Start *****/
6573: -- Delete the residual transactions for which header is final costed
6574: -- but because of reversal, one more transaction got created for the same header.
6575:
6576:
6577:
6989: /*=========================================================
6990: PROCEDURE : perform_weighted_average
6991:
6992: DESCRIPTION This procedure performs weighted avererage of
6993: individual lots
6994: AUTHOR : Sukarna Reddy INVCONV June 2005
6995: ==========================================================*/
6996:
6997: PROCEDURE perform_weighted_average
9004: -- is then used to create a new cost. The 'feature' though is that the new cost is created as
9005: -- the entire quantity has been used in the calculations but the onhand it is set against is
9006: -- the difference beween the old and new quantities. Got that?
9007:
9008: l_residual_qty := old_cost.onhand_qty + transaction_row.trans_qty;
9009:
9010: IF old_cost.onhand_qty < 0
9011: AND l_residual_qty <= 0
9012: THEN
9007:
9008: l_residual_qty := old_cost.onhand_qty + transaction_row.trans_qty;
9009:
9010: IF old_cost.onhand_qty < 0
9011: AND l_residual_qty <= 0
9012: THEN
9013: fnd_file.put_line(fnd_file.log,' 3');
9014: IF l_debug_level >= l_debug_level_high
9015: THEN
9020: process_adjustment;
9021:
9022: ELSE
9023: IF old_cost.onhand_qty < 0
9024: AND l_residual_qty > 0
9025: THEN
9026:
9027: IF l_debug_level >= l_debug_level_high
9028: THEN
9037: fnd_file.put_line
9038: (fnd_file.log,'Onhand balance is currently +ve. Processing normally');
9039: END IF;
9040:
9041: l_residual_qty := transaction_row.trans_qty;
9042: END IF;
9043:
9044: /*IF transaction_row.transaction_source_type_id = 'PORC'*/
9045: IF (transaction_row.transaction_source_type_id IN (INTERNAL_REQ,INTERNAL_ORDER,INVENTORY)
9232: ) THEN
9233: process_lot_translate;
9234: END IF;
9235:
9236: IF l_residual_qty <> transaction_row.trans_qty
9237: THEN
9238: -- The transaction that has been processed flipped a negative onhand balance back to positive
9239: -- so we need to adjust the onhand balance in the header to the residual balance
9240:
9235:
9236: IF l_residual_qty <> transaction_row.trans_qty
9237: THEN
9238: -- The transaction that has been processed flipped a negative onhand balance back to positive
9239: -- so we need to adjust the onhand balance in the header to the residual balance
9240:
9241: IF l_debug_level >= l_debug_level_high
9242: THEN
9243: fnd_file.put_line
9240:
9241: IF l_debug_level >= l_debug_level_high
9242: THEN
9243: fnd_file.put_line
9244: (fnd_file.log,'Onhand balance has flipped from -ve to +ve. Setting onhand qty to residual qty');
9245: END IF;
9246:
9247: UPDATE gmf_lot_costs
9248: SET onhand_qty = l_residual_qty
9244: (fnd_file.log,'Onhand balance has flipped from -ve to +ve. Setting onhand qty to residual qty');
9245: END IF;
9246:
9247: UPDATE gmf_lot_costs
9248: SET onhand_qty = l_residual_qty
9249: WHERE header_id = (SELECT max(header_id)
9250: FROM gmf_lot_costs
9251: WHERE organization_id = transaction_row.orgn_id
9252: AND lot_number = transaction_row.lot_number)
9251: WHERE organization_id = transaction_row.orgn_id
9252: AND lot_number = transaction_row.lot_number)
9253: RETURNING header_id INTO new_cost.header_id;
9254:
9255: -- B3486228 Also set the transaction qty to the residual
9256: UPDATE gmf_material_lot_cost_txns
9257: SET new_onhand_qty = l_residual_qty
9258: WHERE transaction_id = transaction_row.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
9259: AND cost_header_id = new_cost.header_id;
9253: RETURNING header_id INTO new_cost.header_id;
9254:
9255: -- B3486228 Also set the transaction qty to the residual
9256: UPDATE gmf_material_lot_cost_txns
9257: SET new_onhand_qty = l_residual_qty
9258: WHERE transaction_id = transaction_row.transaction_id /* ANTHIYAG Bug#5285726 07-Jun-2006 */
9259: AND cost_header_id = new_cost.header_id;
9260: END IF;
9261: END IF;