[Home] [Help]
46: --* *
47: --* 10-Nov-2003 PJS If we encounter a consumption transaction (eg OMSO) but the lot has not *
48: --* been costed, report an error and skip the transaction. *
49: --* Also fixed the cursors that retrieve uncosted transactions as a new *
50: --* column has been added to the gmf_lot_costed_items table (co_code) and *
51: --* this created a semi-cartesian join. *
52: --* *
53: --* 17-Nov-2003 PJS Reinstate the code that handles acquisition costs as it somehow became *
54: --* lost. *
63: --* support update or alterations to it. Also, Oracle db itself doesn't *
64: --* support updates to nested tables - we need to drop and recreate it. *
65: --* *
66: --* 21-Nov-2003 umoogala ic_item_mst.lot_costed_flag does not exist. To work around this loaded *
67: --* all lot costed item from gmf_lot_costed_items_flag into lc_item_tab. If *
68: --* item exists in this table then lot_costed_flag is set to 1 otherwise to 0*
69: --* Called new function is_item_lot_costed to do this in material_cursor. *
70: --* *
71: --* 30-Nov-2003 umoogala Now passing co_code to main routines ROLLUP_LOT_COSTS and removed *
794: --* This cursor would ideally be nested inside the above cursor, but SQL syntax does not allow *
795: --* an order by clause in a subquery, and we need to have the transactions ordered by date *
796: --* *
797: --* umoogala: ic_item_mst.lot_costed_flag does not exist. To work around this loaded all lot *
798: --* costed item from gmf_lot_costed_items_flag into lc_item_tab. If item exists in this table *
799: --* then lot_costed_flag is set to 1 otherwise to 0. *
800: --*
801: --* Girish - Bug 4094132 Modified this materials cursor NOT IN clause to add lot_id and reverse_id
802: --* Since we are filtering the query using "NOT IN" the product yielded into multiple lots
1355:
1356: CURSOR lot_costed_items
1357: IS
1358: SELECT g.inventory_item_id,g.organization_id
1359: FROM gmf_lot_costed_items_gt g
1360: ORDER BY organization_id,inventory_item_id
1361: ;
1362:
1363:
3024: select mtln.primary_quantity,
3025: lcig.primary_uom_code
3026: from mtl_material_transactions mmt,
3027: mtl_transaction_lot_numbers mtln,
3028: gmf_lot_costed_items_gt lcig
3029: where mmt.transaction_id = transaction_row.transfer_transaction_id
3030: AND mmt.transaction_id = mtln.transaction_id
3031: AND mmt.inventory_item_id = lcig.inventory_item_id
3032: AND mmt.organization_id = lcig.organization_id;
6463: -- Open a cursor that retrieves only the item/lot/sublot specified
6464: OPEN Cur_lc_header FOR
6465: SELECT glc.header_id, glc.rowid
6466: FROM gmf_lot_costs glc,
6467: gmf_lot_costed_items_gt gpo
6468: WHERE glc.organization_id = gpo.organization_id
6469: AND glc.inventory_item_id = gpo.inventory_item_id
6470: AND glc.cost_type_id = l_cost_type_id
6471: AND glc.final_cost_flag = 0
7319: l_org_tab(cur_rec.organization_id) := cur_rec.organization_code;
7320: END LOOP;
7321:
7322: INSERT
7323: INTO GMF_LOT_COSTED_ITEMS_GT
7324: (
7325: organization_id,
7326: inventory_item_id,
7327: primary_uom_code
7329: SELECT
7330: msi.organization_id,
7331: msi.inventory_item_id,
7332: msi.primary_uom_code
7333: FROM gmf_lot_costed_items lci,
7334: mtl_system_items_b msi,
7335: gmf_process_organizations_gt gpo
7336: WHERE lci.legal_entity_id = p_le_id
7337: AND lci.delete_mark = 0 /* ANTHIYAG Bug#5279681 06-Jun-2006 */
7358: mic.organization_id, /*ANTHIYAG Bug#5279681 06-Jun-2006 */
7359: mic.inventory_item_id, /*ANTHIYAG Bug#5279681 06-Jun-2006 */
7360: i.primary_uom_code
7361: FROM mtl_item_categories mic,
7362: gmf_lot_costed_items g,
7363: mtl_system_items_b i,
7364: gmf_process_organizations_gt gpo
7365: WHERE g.cost_category_id = mic.category_id
7366: AND g.legal_entity_id = l_le_id
8526: -- There are two possibilities for the costing. A lot might be manufactured
8527: -- or bought in. Occasionally a lot might be bought in and later added to by a
8528: -- production batch or vice versa. No matter how the lot is created and/or replenished
8529: -- the sole criterion to decide whether to cost an item's lots using lot costing will
8530: -- be an entry for the item, or its cost_class, in gmf_lot_costed_items
8531:
8532: -- In the case of a lot that is an output of a production batch, we need
8533: -- to explode it and find out what lots went into its manufacture.
8534:
8619: -- rows that are returned. There are several cursors depending on how the
8620: -- user has launched the rollup:
8621: --
8622: -- A full rollup will retrieve all transactions for all items/cost classes
8623: -- listed in the gmf_lot_costed_items table. Note that this retrieval will not
8624: -- possess a date range.
8625: --
8626: -- An item-specific rollup will only retrieve transactions for the item specified.
8627: -- If the item has a date range in the gmf_lot_costed_items table this will be
8623: -- listed in the gmf_lot_costed_items table. Note that this retrieval will not
8624: -- possess a date range.
8625: --
8626: -- An item-specific rollup will only retrieve transactions for the item specified.
8627: -- If the item has a date range in the gmf_lot_costed_items table this will be
8628: -- used. This option also allows a user to specify an optional lot and sublot.
8629: --
8630: -- A cost class-specific rollup will only retrieved transactions for items in the
8631: -- cost class specified. If the cost class possesses a date range in gmf_lot_costed_items
8627: -- If the item has a date range in the gmf_lot_costed_items table this will be
8628: -- used. This option also allows a user to specify an optional lot and sublot.
8629: --
8630: -- A cost class-specific rollup will only retrieved transactions for items in the
8631: -- cost class specified. If the cost class possesses a date range in gmf_lot_costed_items
8632: -- this will be used.
8633:
8634:
8635: --
8638: -- Also, added lot_ctl = 1 condition
8639: -- to where clause
8640: --
8641:
8642: -- Since the cursors below were written, a new column (co_code) has been added to the gmf_lot_costed_items table
8643: -- with the result that it is now possible to have rows differing only in this column.
8644:
8645: -- Cursors now possess a third branch that is 'union all'd' into the result set to retrieve the lot cost
8646: -- adjustments. These are disguised as transactions of type LADJ.
8677: gme_material_details gme,
8678: mtl_transaction_lot_numbers mtln,
8679: gme_transaction_pairs gtp,
8680: gmf_process_organizations_gt gpo,
8681: gmf_lot_costed_items_gt lcig
8682: WHERE
8683: gpo.organization_id = mmt.organization_id
8684: AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date)
8685: AND mmt.transaction_date <= l_final_run_date
8721: mmt.transfer_organization_id
8722: FROM mtl_material_transactions mmt,
8723: mtl_transaction_lot_numbers mtln,
8724: gmf_process_organizations_gt gpo,
8725: gmf_lot_costed_items_gt lcig
8726: WHERE
8727: gpo.organization_id = mmt.organization_id
8728: AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date)
8729: AND mmt.transaction_date <= l_final_run_date
8766: mmt.transfer_organization_id
8767: FROM mtl_material_transactions mmt,
8768: mtl_transaction_lot_numbers mtln,
8769: gmf_process_organizations_gt gpo,
8770: gmf_lot_costed_items_gt lcig
8771: WHERE
8772: gpo.organization_id = mmt.owning_organization_id
8773: AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date)
8774: AND mmt.transaction_date <= l_final_run_date
8810: mmt.transfer_organization_id
8811: FROM mtl_material_transactions mmt,
8812: mtl_transaction_lot_numbers mtln,
8813: gmf_process_organizations_gt gpo,
8814: gmf_lot_costed_items_gt lcig
8815: WHERE
8816: gpo.organization_id = mmt.organization_id
8817: AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date)
8818: AND mmt.transaction_date <= l_final_run_date
8852: 0 as transaction_cost,
8853: 0 as transfer_transaction_id
8854: FROM gmf_lot_cost_adjustments glca,
8855: mtl_system_items_b iimb,
8856: gmf_lot_costed_items_gt glci
8857: WHERE glca.applied_ind = 'N'
8858: AND glca.adjustment_date >= NVL(l_trans_start_date, glca.adjustment_date)
8859: AND glca.legal_entity_id = l_le_id
8860: AND glca.cost_type_id = l_cost_type_id