1: PACKAGE BODY pmi_lot_genealogy_pkg AS
2: /* $Header: PMILTGEB.pls 115.10 2002/12/05 17:01:03 skarimis ship $ */
3:
4: /*
5: Package: pmi_lot_genealogy
1: PACKAGE BODY pmi_lot_genealogy_pkg AS
2: /* $Header: PMILTGEB.pls 115.10 2002/12/05 17:01:03 skarimis ship $ */
3:
4: /*
5: Package: pmi_lot_genealogy
6: Source Code File: PMILGENB.pls
7:
8: Maintenance Log:
9:
28: SELECT
29: gen1.product_item_id, gen1.product_lot_id,
30: gen2.ingred_item_id, gen2.ingred_lot_id
31: FROM
32: pmi_lot_genealogy gen1, pmi_lot_genealogy gen2,
33: (
34: SELECT
35: product.item_id product_item_id, product.lot_id product_lot_id,
36: ingred.item_id ingred_item_id, ingred.lot_id ingred_lot_id
123: FOR gen_rec IN doc_genealogy(pv_doc_id)
124: LOOP
125:
126: BEGIN
127: INSERT INTO pmi_lot_genealogy (
128: product_item_id,
129: product_lot_id,
130: ingred_item_id,
131: ingred_lot_id,
138: 1);
139: EXCEPTION
140: WHEN DUP_VAL_ON_INDEX
141: THEN
142: UPDATE pmi_lot_genealogy
143: SET association_count = association_count + 1
144: WHERE product_lot_id = gen_rec.product_lot_id
145: AND ingred_lot_id = gen_rec.ingred_lot_id;
146: END;
156: IS
157: /*
158:
159: The challenge here is to first remove the doc genealogy that reflected the state
160: of pmi_lot_bom_v at the time of the last refresh of pmi_lot_genealogy.
161:
162: The reason this is complicated is that pmi_lot_bom_v only contains net bom
163: relationships. If transactions canceled each other out, the lot-to-lot relationship
164: does not appear. Example: a typo may caused a lot to be mistakenly allocated,
181: BEGIN
182:
183: FOR gen_rec IN doc_genealogy(pv_doc_id)
184: LOOP
185: DELETE FROM pmi_lot_genealogy
186: WHERE product_lot_id = gen_rec.product_lot_id
187: AND ingred_lot_id = gen_rec.ingred_lot_id
188: AND association_count = 1;
189:
188: AND association_count = 1;
189:
190: IF SQL%ROWCOUNT <> 1
191: THEN
192: UPDATE pmi_lot_genealogy
193: SET association_count = association_count - 1
194: WHERE product_lot_id = gen_rec.product_lot_id
195: AND ingred_lot_id = gen_rec.ingred_lot_id;
196: END IF;
211: BEGIN
212: FOR nl IN new_lots
213: LOOP
214: BEGIN
215: INSERT INTO pmi_lot_genealogy
216: (product_item_id, product_lot_id, ingred_item_id, ingred_lot_id, association_count)
217: VALUES (nl.item_id, nl.lot_id, nl.item_id, nl.lot_id, 1);
218: EXCEPTION
219: WHEN DUP_VAL_ON_INDEX THEN
253: add_current_genealogy(doc.doc_id);
254: END LOOP;
255: SELECT TABLE_OWNER INTO l_table_owner
256: FROM USER_SYNONYMS
257: WHERE SYNONYM_NAME = 'PMI_LOT_GENEALOGY';
258: FND_STATS.GATHER_TABLE_STATS(l_table_owner, 'PMI_LOT_GENEALOGY');
259:
260: set_last_refresh_date(lv_current_refresh_date);
261:
254: END LOOP;
255: SELECT TABLE_OWNER INTO l_table_owner
256: FROM USER_SYNONYMS
257: WHERE SYNONYM_NAME = 'PMI_LOT_GENEALOGY';
258: FND_STATS.GATHER_TABLE_STATS(l_table_owner, 'PMI_LOT_GENEALOGY');
259:
260: set_last_refresh_date(lv_current_refresh_date);
261:
262: COMMIT;
268:
269: END refresh_lot_genealogy;
270:
271:
272: END pmi_lot_genealogy_pkg;