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
10 Date Author Description
11 ------------ ------------- --------------------------
12 08-Jan-2000 P. Dong Consolidated individual procedures into this pkg.
13 30-Jan-2000 P. Dong Changed name of source code file and package
14 30-Jan-2000 P. Dong Added DISTINCT to gen_cursor
15 30-Jan-2000 P. Dong Removed Circular Ref check from add_doc_genealogy
16 30-Jan-2000 P. Dong Added Net Change functionality
17 13-Jun-2000 P. Dong removed date dependency in doc_genealogy cursor. Doc
18 genealogies should be removed or added for entire doc.
19 Date param removed from add and remove _current_genealogy
20 13-Jun-2000 P. Dong Put getting and setting of last_refresh_date into indiv
21 code units, in anticipation of change of access meth.
22 08-Feb-2002 P. Dong Add call to fnd_stats.gather_table_stats
23 */
24 transaction_duration CONSTANT NUMBER := 1/24/60; -- in minutes
25 datetime_format CONSTANT VARCHAR2(32) := 'DD-MON-YYYY HH24:MI:SS';
26
27 CURSOR doc_genealogy(cp_doc_id NUMBER) IS
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
37 FROM
38 ic_tran_pnd product, ic_tran_pnd ingred
39 WHERE
40 product.doc_type = 'PROD'
41 AND product.doc_id = cp_doc_id
42 AND product.line_type in (1,2)
43 AND product.completed_ind = 1
44 AND product.lot_id <> 0
45 AND ingred.doc_type = 'PROD'
46 AND ingred.doc_id = cp_doc_id
47 AND ingred.completed_ind = 1
48 AND ingred.line_type = -1
49 AND ingred.lot_id <> 0
50 AND ingred.lot_id <> product.lot_id
51 GROUP BY
52 product.item_id, product.lot_id,
53 ingred.item_id, ingred.lot_id
54 HAVING
55 SUM(ingred.trans_qty) <> 0
56 OR SUM(product.trans_qty) <> 0
57 ) bom
58 WHERE
59 gen1.ingred_lot_id = bom.product_lot_id
60 AND gen2.product_lot_id = bom.ingred_lot_id;
61
62
63 FUNCTION get_last_refresh_date
64 RETURN DATE
65 IS
66 lv_date DATE;
67 BEGIN
68 BEGIN
69 lv_date := TO_DATE(fnd_profile.value('PMI$LOTGEN_REFRESH_DATE'), datetime_format);
70 EXCEPTION
71 WHEN OTHERS THEN
72 SELECT MIN(trans_date) into lv_date
73 FROM ic_tran_vw1
74 WHERE doc_type = 'PROD'
75 AND completed_ind = 1
76 AND lot_id <> 0;
77 END; RETURN lv_date;
78 END;
79
80
81 FUNCTION get_current_refresh_date
82 RETURN DATE
83 IS
84 lv_date DATE;
85 BEGIN
86 lv_date := SYSDATE - transaction_duration;
87 RETURN lv_date;
88 END;
89
90
91 PROCEDURE set_last_refresh_date /* Requires a commit after being called */
92 (
93 pp_last_refresh_date DATE
94 )
95 IS
96 l_err BOOLEAN;
97 BEGIN
98 l_err := fnd_profile.save('PMI$LOTGEN_REFRESH_DATE', TO_CHAR(pp_last_refresh_date, datetime_format), 'SITE');
99 /* Commit must follow calls to this procedure */
100 END;
101
102
103 PROCEDURE add_current_genealogy
104 (
105 pv_doc_id NUMBER
106 )
107 IS
108 /*
109
110 In some circumstance, the current_lot_gen cursor will return same product_lot_id
111 .ingred_lot_id pair in multiple rows. This occurs where the same item may be fo
112 und as an ingredient in more than one branch (or conversely, as a product in mor
113 e than one where-used branch). It is necessary to bump the association_count fo
114 r each such dup, since a subsequent removal may be at a point that affects only
115 one of the duplicate pairs. Therefore, it is necessary to NOT distinct the foll
116 owing select. Each instance of a given pair should be recorded in the associati
117 on_count.
118
119 */
120
121 BEGIN
122
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,
132 association_count)
133 VALUES (
134 gen_rec.product_item_id,
135 gen_rec.product_lot_id,
136 gen_rec.ingred_item_id,
137 gen_rec.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;
147
148 END LOOP;
149
150 END add_current_genealogy;
151
152 PROCEDURE remove_prior_genealogy
153 (
154 pv_doc_id NUMBER
155 )
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,
165 and the subsequent correction will result in a SUM(trans_qty) = 0. Following the
166 correction, the lots should no longer be considered to be associated with each
167 other.
168
169 A lot relationship may have been present at the last refresh, but at the current
170 one, it is not present (because of a correction that occured betwene the last
171 refresh and the current one). So removing of the lot relationships must take
172 place in the context in which the last refresh occured. This requires a version of
173 pmi_lot_bom_v which joins to a table containing the last_refresh_date. The last
174 _refresh_date should first be fixed,and then used to limit the transactions that
175 are selected as "current". This should eliminates the possibility that the
176 granularity of sysdate will not be sufficient to be be sure of which transactions
177 are included in what refresh.
178
179 */
180
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
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;
197 END LOOP;
198
199 END remove_prior_genealogy;
200
201
202
203
204 PROCEDURE add_lot_self_genealogy(pp_last_refresh_date DATE, pp_current_refresh_date DATE)
205 IS
206 CURSOR new_lots IS
207 SELECT item_id, lot_id
208 FROM ic_lots_mst
209 WHERE lot_id <> 0
210 AND creation_date BETWEEN NVL(pp_last_refresh_date, creation_date) AND pp_current_refresh_date;
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
220 NULL; -- no need to insert
221 END;
222 END LOOP;
223 END add_lot_self_genealogy;
224
225
226 PROCEDURE refresh_lot_genealogy (errbuf OUT NOCOPY varchar2,retcode OUT NOCOPY VARCHAR2)
227 IS
228 /*
229 Select the doc_id's of all batches that have had at least one transaction
230 update between the last refresh date and the current refresh date.
231 */
232 CURSOR updated_docs(cp_last_refresh_date DATE, cp_current_refresh_date DATE)
233 IS
234 SELECT DISTINCT doc_id
235 FROM ic_tran_pnd t
236 WHERE t.last_update_date BETWEEN NVL(cp_last_refresh_date, t.last_update_date)
237 AND cp_current_refresh_date
238 AND t.doc_type = 'PROD'
239 AND t.completed_ind = 1
240 AND t.lot_id <> 0;
241 l_table_owner VARCHAR2(40);
242 lv_last_refresh_date DATE;
243 lv_current_refresh_date DATE;
244 BEGIN
245 lv_last_refresh_date := get_last_refresh_date;
246 lv_current_refresh_date := get_current_refresh_date;
247
248 add_lot_self_genealogy(lv_last_refresh_date, lv_current_refresh_date);
249
250 FOR doc IN updated_docs(lv_last_refresh_date, lv_current_refresh_date)
251 LOOP
252 remove_prior_genealogy(doc.doc_id);
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
262 COMMIT;
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 errbuf := SUBSTR(SQLERRM,1,100);
267 retcode := '2';
268
269 END refresh_lot_genealogy;
270
271
272 END pmi_lot_genealogy_pkg;
[Home] [Help]