eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.MSC_X_UDE_PEGGING

Source


1 PACKAGE BODY msc_x_ude_pegging AS
2 /*  $Header: MSCXCEB.pls 120.2 2006/02/16 01:01:28 pragarwa ship $ */
3    PURCHASE_ORDER CONSTANT INTEGER := 13;
4    SALES_ORDER CONSTANT INTEGER := 14;
5 
6 
7    PROCEDURE get_so_data(parent_id IN NUMBER, so_max_receipt_date OUT NOCOPY DATE, so_min_receipt_date OUT NOCOPY DATE,
8                          so_sum_quantity OUT NOCOPY NUMBER)
9    IS
10    BEGIN
11          SELECT min(child.receipt_date), max(child.receipt_date),
12                 sum(decode(sys_context('MSC','COMPANY_ID'), child.publisher_id, child.primary_quantity, decode(child.publisher_id, child.customer_id, child.supplier_id, child.customer_id), child.tp_quantity, child.quantity))
13            INTO so_min_receipt_date, so_max_receipt_date, so_sum_quantity
14            FROM msc_sup_dem_entries parent, msc_sup_dem_entries child
15           WHERE parent.plan_id = -1
16             AND parent.publisher_order_type = PURCHASE_ORDER
17             AND exists
18                  (select 1 from MSC_X_SECURITY_RULES rule where sysdate between
19 nvl(rule.EFFECTIVE_FROM_DATE, sysdate-1) and nvl(rule.EFFECTIVE_TO_DATE, sysdate +1)
20 and nvl(rule.company_id, parent.PUBLISHER_ID) = parent.publisher_id and
21 nvl(rule.order_type, parent.publisher_order_type) = parent.publisher_order_type and
22  nvl(rule.item_id, parent.inventory_item_id) = parent.inventory_item_id and
23  nvl(rule.customer_id, nvl(parent.customer_id, -1)) = nvl(parent.customer_id, -1) and
24  nvl(rule.supplier_id, nvl(parent.supplier_id, -1)) = nvl(parent.supplier_id, -1) and nvl(rule.customer_site_id,
25   nvl(parent.customer_site_id, -1)) = nvl(parent.customer_site_id, -1) and
26   nvl(rule.supplier_site_id, nvl(parent.supplier_site_id, -1)) = nvl(parent.supplier_site_id, -1) and
27   nvl(rule.org_id, parent.PUBLISHER_SITE_ID) = parent.PUBLISHER_SITE_ID and
28   nvl(rule.order_number, nvl(parent.order_number, -1)) = nvl(parent.order_number, -1) and
29   (rule.grantee_key = decode(upper(rule.grantee_type), 'USER', FND_GLOBAL.USER_ID, 'COMPANY', sys_context('MSC', 'COMPANY_ID')) or upper(rule.grantee_type) = 'DOCUMENT OWNER' and
30   parent.publisher_id = sys_context('MSC', 'COMPANY_ID') or
31   upper(rule.grantee_type) ='TRADING PARTNER' and parent.customer_id = sys_context('MSC', 'COMPANY_ID')
32   or upper(rule.grantee_type) = 'TRADING PARTNER' and parent.supplier_id = sys_context('MSC', 'COMPANY_ID') or decode(upper(rule.grantee_type),'RESPONSIBILITY', rule.grantee_key) = fnd_global.resp_id or
33   (upper(rule.grantee_type) = 'GLOBAL') or (upper(rule.grantee_type)='GROUP' and rule.grantee_key in
34   (SELECT group_id FROM msc_group_companies WHERE company_id = sys_context('MSC','COMPANY_ID') AND
35   sysdate BETWEEN effective_date and nvl(disable_date,sysdate+1) ) ) ) )
36             AND exists
37                  (select 1 from MSC_X_SECURITY_RULES rule where sysdate between
38 nvl(rule.EFFECTIVE_FROM_DATE, sysdate-1) and nvl(rule.EFFECTIVE_TO_DATE, sysdate +1)
39 and nvl(rule.company_id, child.PUBLISHER_ID) = child.publisher_id and
40 nvl(rule.order_type, child.publisher_order_type) = child.publisher_order_type and
41  nvl(rule.item_id, child.inventory_item_id) = child.inventory_item_id and
42  nvl(rule.customer_id, nvl(child.customer_id, -1)) = nvl(child.customer_id, -1) and
43  nvl(rule.supplier_id, nvl(child.supplier_id, -1)) = nvl(child.supplier_id, -1) and
44  nvl(rule.customer_site_id, nvl(child.customer_site_id, -1)) = nvl(child.customer_site_id, -1) and
45  nvl(rule.supplier_site_id, nvl(child.supplier_site_id, -1)) = nvl(child.supplier_site_id, -1) and
46  nvl(rule.org_id, child.PUBLISHER_SITE_ID) = child.PUBLISHER_SITE_ID and
47  nvl(rule.order_number, nvl(child.order_number, -1)) = nvl(child.order_number, -1) and
48  (rule.grantee_key = decode(upper(rule.grantee_type), 'USER', FND_GLOBAL.USER_ID, 'COMPANY', sys_context('MSC', 'COMPANY_ID')) or upper(rule.grantee_type) = 'DOCUMENT OWNER' and
49  child.publisher_id = sys_context('MSC', 'COMPANY_ID') or upper(rule.grantee_type) ='TRADING PARTNER' and
50  child.customer_id = sys_context('MSC', 'COMPANY_ID') or upper(rule.grantee_type) = 'TRADING PARTNER' and
51  child.supplier_id = sys_context('MSC', 'COMPANY_ID') or decode(upper(rule.grantee_type),'RESPONSIBILITY', rule.grantee_key) = fnd_global.resp_id or (upper(rule.grantee_type) = 'GLOBAL') or
52  (upper(rule.grantee_type)='GROUP' and rule.grantee_key in (SELECT group_id FROM msc_group_companies WHERE company_id = sys_context('MSC','COMPANY_ID') AND sysdate BETWEEN effective_date and nvl(disable_date,sysdate+1) ) ) ) )
53             AND parent.transaction_id = parent_id
54             AND parent.order_number = child.end_order_number
55           AND nvl(parent.release_number, -1) = nvl(child.end_order_rel_number, -1)
56           AND (
57                (child.end_order_line_number IS NOT NULL AND
58                 parent.line_number = child.end_order_line_number )
59                OR
60                (child.end_order_line_number IS NULL AND
61                 parent.publisher_id = child.end_order_publisher_id AND
62                 decode(child.end_order_publisher_site_id,
63                          null, parent.publisher_site_id,
64                          child.end_order_publisher_site_id) = parent.publisher_site_id AND
65                 parent.inventory_item_id = child.inventory_item_id )
66                OR
67                (child.end_order_line_number IS NULL AND
68                 child.end_order_publisher_id <> child.publisher_id AND
69                 parent.inventory_item_id = child.inventory_item_id )
70              )
71            AND (
72                 (child.end_order_publisher_id IS NOT NULL AND
73                  parent.publisher_id = child.end_order_publisher_id AND
74                  child.end_order_type IS NOT NULL AND
75                  parent.publisher_order_type = child.end_order_type AND
76                  decode(child.end_order_publisher_site_id,
77                          null, parent.publisher_site_id,
78                          child.end_order_publisher_site_id) = parent.publisher_site_id
79                 )
80                 OR
81                 (child.end_order_publisher_id IS NULL AND
82                  child.end_order_type IS NOT NULL AND
83                  parent.publisher_id = child.publisher_id)
84               ) ;
85 
86    EXCEPTION
87    WHEN OTHERS THEN
88       so_max_receipt_date := NULL;
89       so_min_receipt_date := NULL;
90       so_sum_quantity := NULL;
91    END get_so_data;
92 
93    PROCEDURE get_po_data (child_transid IN NUMBER, po_need_by_date OUT NOCOPY DATE, po_quantity OUT NOCOPY NUMBER,
94                           po_transaction OUT NOCOPY NUMBER)
95    IS
96       l_need_by_date DATE ;
97       l_quantity NUMBER;
98       l_po_transaction NUMBER;
99 
100    BEGIN
101 
102       /**
103        * assumes that the given child record is a sales order
104        */
105 
106       SELECT parent.receipt_date,
107       decode(sys_context('MSC','COMPANY_ID'), parent.publisher_id, parent.primary_quantity, decode(parent.publisher_id, parent.customer_id, parent.supplier_id, parent.customer_id), parent.tp_quantity, parent.quantity),
108       parent.transaction_id
109         INTO l_need_by_date, l_quantity, l_po_transaction
110         FROM msc_sup_dem_entries parent, msc_sup_dem_entries child
111        WHERE parent.publisher_order_type = PURCHASE_ORDER
112          AND parent.plan_id = -1
113          AND exists
114               (select 1 from MSC_X_SECURITY_RULES rule where sysdate between
115 nvl(rule.EFFECTIVE_FROM_DATE, sysdate-1) and nvl(rule.EFFECTIVE_TO_DATE, sysdate +1)
116 and nvl(rule.company_id, parent.PUBLISHER_ID) = parent.publisher_id and
117 nvl(rule.order_type, parent.publisher_order_type) = parent.publisher_order_type and
118  nvl(rule.item_id, parent.inventory_item_id) = parent.inventory_item_id and
119  nvl(rule.customer_id, nvl(parent.customer_id, -1)) = nvl(parent.customer_id, -1) and
120  nvl(rule.supplier_id, nvl(parent.supplier_id, -1)) = nvl(parent.supplier_id, -1) and
121  nvl(rule.customer_site_id, nvl(parent.customer_site_id, -1)) = nvl(parent.customer_site_id, -1) and
122  nvl(rule.supplier_site_id, nvl(parent.supplier_site_id, -1)) = nvl(parent.supplier_site_id, -1) and
123  nvl(rule.org_id, parent.PUBLISHER_SITE_ID) = parent.PUBLISHER_SITE_ID and
124  nvl(rule.order_number, nvl(parent.order_number, -1)) = nvl(parent.order_number, -1) and
125  (rule.grantee_key = decode(upper(rule.grantee_type), 'USER', FND_GLOBAL.USER_ID, 'COMPANY', sys_context('MSC', 'COMPANY_ID'))
126  or upper(rule.grantee_type) = 'DOCUMENT OWNER' and parent.publisher_id = sys_context('MSC', 'COMPANY_ID') or
127  upper(rule.grantee_type) ='TRADING PARTNER' and parent.customer_id = sys_context('MSC', 'COMPANY_ID') or
128  upper(rule.grantee_type) = 'TRADING PARTNER' and parent.supplier_id = sys_context('MSC', 'COMPANY_ID') or
129  decode(upper(rule.grantee_type),'RESPONSIBILITY', rule.grantee_key) = fnd_global.resp_id or
130  (upper(rule.grantee_type) = 'GLOBAL') or (upper(rule.grantee_type)='GROUP' and rule.grantee_key in (SELECT group_id FROM msc_group_companies WHERE company_id = sys_context('MSC','COMPANY_ID') AND sysdate BETWEEN
131  effective_date and nvl(disable_date,sysdate+1) ) ) ) )
132          AND child.transaction_id = child_transid
133          AND parent.order_number = child.end_order_number
134          AND ( (child.end_order_line_number IS NOT NULL AND
135              child.end_order_line_number = parent.line_number)
136             OR
137             (child.end_order_line_number IS NULL AND
138              parent.publisher_id = child.end_order_publisher_id AND
139              decode(child.end_order_publisher_site_id, null,
140                     parent.publisher_site_id,
141                  child.end_order_publisher_site_id)
142               = parent.publisher_site_id  AND
143             child.inventory_item_id = parent.inventory_item_id )
144             OR
145             (child.end_order_line_number IS NULL AND
146              child.publisher_id <> child.end_order_publisher_id)
147           )
148       AND nvl(parent.release_number, -1)
149                = nvl(child.end_order_rel_number, -1)
150       AND ((child.end_order_publisher_id IS NOT NULL AND
151             child.end_order_type IS NOT NULL AND
152             child.end_order_type = parent.publisher_order_type AND
153             child.end_order_publisher_id = parent.publisher_id AND
154             decode(child.end_order_publisher_site_id, null,
155                   parent.publisher_site_id,
156                 child.end_order_publisher_site_id)
157                 = parent.publisher_site_id
158             )
159             OR
160             (child.end_order_publisher_id IS NULL AND
161              child.end_order_type IS NOT NULL AND
162              child.publisher_id = parent.publisher_id)
163           );
164 
165       po_need_by_date := l_need_by_date;
166       po_quantity := l_quantity;
167       po_transaction := l_po_transaction;
168 
169    EXCEPTION
170       WHEN OTHERS THEN
171          po_need_by_date := NULL;
172          po_quantity := NULL;
173          po_transaction := NULL;
174 
175    END get_po_data;
176 
177 
178    FUNCTION days_late(p_transaction_id NUMBER)
179    RETURN NUMBER
180    AS
181 
182       l_order_type NUMBER;
183       l_receipt_date DATE;
184 
185       po_need_by_date DATE;
186       po_quantity NUMBER;
187       l_po_transaction NUMBER;
188 
189       days_late NUMBER;
190       so_max_receipt_date DATE;
191       so_min_receipt_date DATE;
192       so_sum_quantity NUMBER;
193 
194    BEGIN
195 
196       SELECT msde.publisher_order_type, msde.receipt_date
197         INTO l_order_type, l_receipt_date
198         FROM msc_sup_dem_entries msde
199       WHERE msde.transaction_id = p_transaction_id
200         AND msde.publisher_order_type IN (PURCHASE_ORDER, SALES_ORDER);
201 
202       IF l_order_type = SALES_ORDER THEN
203          get_po_data(p_transaction_id, po_need_by_date, po_quantity, l_po_transaction);
204          days_late := l_receipt_date - po_need_by_date;
205       ELSIF l_order_type = PURCHASE_ORDER THEN
206          get_so_data(p_transaction_id, so_max_receipt_date, so_min_receipt_date, so_sum_quantity);
207          days_late := so_max_receipt_date - l_receipt_date;
208       END IF;
209 
210       IF days_late > 0 THEN
211          RETURN days_late;
212       ELSE
213          RETURN 0;
214       END IF;
215 
216    EXCEPTION
217    WHEN OTHERS THEN
218       RETURN 0;
219    END days_late;
220 
221    FUNCTION days_early(p_transaction_id NUMBER)
222    RETURN NUMBER
223    AS
224 
225       l_order_type NUMBER;
226       l_receipt_date DATE;
227 
228       po_need_by_date DATE;
229       po_quantity NUMBER;
230       l_po_transaction NUMBER;
231 
232       days_early NUMBER;
233       so_max_receipt_date DATE;
234       so_min_receipt_date DATE;
235       so_sum_quantity NUMBER;
236 
237    BEGIN
238 
239       SELECT publisher_order_type, receipt_date
240         INTO l_order_type, l_receipt_date
241         FROM msc_sup_dem_entries_ui_v
242       WHERE transaction_id = p_transaction_id
243         AND publisher_order_type in (PURCHASE_ORDER, SALES_ORDER);
244 
245       IF l_order_type = SALES_ORDER THEN
246          get_po_data(p_transaction_id, po_need_by_date, po_quantity, l_po_transaction);
247          days_early := po_need_by_date - l_receipt_date;
248       ELSIF l_order_type = PURCHASE_ORDER THEN
249          get_so_data(p_transaction_id, so_max_receipt_date, so_min_receipt_date, so_sum_quantity);
250          days_early := l_receipt_date - so_min_receipt_date;
251 
252       END IF;
253 
254    IF days_early > 0 THEN
255       RETURN days_early;
256    ELSE
257       RETURN 0;
258    END IF;
259 
260    EXCEPTION
261    WHEN OTHERS THEN
262       RETURN 0;
263    END days_early;
264 
265    PROCEDURE quantity(p_transaction_id IN NUMBER, po_quantity_required OUT NOCOPY NUMBER, so_quantity_given OUT NOCOPY NUMBER)
266    IS
267       l_order_type NUMBER;
268       l_receipt_date DATE;
269 
270       po_need_by_date DATE;
271       so_max_receipt_date DATE;
272       so_min_receipt_date DATE;
273       so_sum_quantity NUMBER;
274       l_po_transaction NUMBER;
275       l_quantity NUMBER;
276 
277    BEGIN
278 
279       SELECT publisher_order_type, receipt_date,
280              decode(sys_context('MSC','COMPANY_ID'), publisher_id, primary_quantity, decode(publisher_id, customer_id, supplier_id, customer_id), tp_quantity, quantity)
281         INTO l_order_type, l_receipt_date, l_quantity
282         FROM msc_sup_dem_entries_ui_v
283       WHERE transaction_id = p_transaction_id
284         AND publisher_order_type in (PURCHASE_ORDER, SALES_ORDER);
285 
286       IF l_order_type IN (SALES_ORDER, PURCHASE_ORDER) THEN
287 
288          IF l_order_type = SALES_ORDER THEN
289             get_po_data(p_transaction_id, po_need_by_date, po_quantity_required, l_po_transaction);
290             get_so_data(l_po_transaction, so_max_receipt_date, so_min_receipt_date, so_quantity_given);
291          ELSE
292             po_quantity_required := l_quantity;
293             get_so_data(p_transaction_id, so_max_receipt_date, so_min_receipt_date, so_quantity_given);
294          END IF;
295 
296       END IF;
297 
298    EXCEPTION
299    WHEN OTHERS THEN
300       RETURN;
301    END quantity;
302 
303    FUNCTION quantity_excess(p_transaction_id NUMBER)
304    RETURN NUMBER
305    IS
306       quantity_required NUMBER;
307       quantity_given NUMBER;
308       quantity_excess NUMBER;
309    BEGIN
310 
311       quantity(p_transaction_id, quantity_required, quantity_given);
312       quantity_excess := quantity_given - quantity_required;
313 
314       IF quantity_excess > 0 THEN
315          RETURN quantity_excess;
316       ELSE
317          RETURN 0;
318       END IF;
319 
320    EXCEPTION
321    WHEN OTHERS THEN
322       RETURN 0;
323    END quantity_excess;
324 
325 
326    FUNCTION quantity_shortage(p_transaction_id NUMBER)
327    RETURN NUMBER
328    IS
329       quantity_required NUMBER;
330       quantity_given NUMBER;
331       quantity_short NUMBER;
332    BEGIN
333 
334       quantity(p_transaction_id, quantity_required, quantity_given);
335       quantity_short := quantity_required - quantity_given;
336 
337       IF quantity_short >= 0 THEN
338          RETURN quantity_short;
339       ELSE
340          RETURN 0;
341       END IF;
342 
343    EXCEPTION
344    WHEN OTHERS THEN
345       RETURN 0;
346    END quantity_shortage;
347 
348 END msc_x_ude_pegging;