1 PACKAGE BODY PO_CORE_S4 AS
2 /* $Header: POXCOC4B.pls 115.2 2002/11/23 03:32:08 sbull ship $*/
3 /*===========================================================================
4
5 PROCEDURE NAME: cleanup_po_tables()
6
7 ===========================================================================*/
8 PROCEDURE cleanup_po_tables is
9
10 x_progress varchar2(3) := NULL;
11
12 BEGIN
13
14
15 x_progress := '001';
16
17
18 /* Delete the old notifications from the table. */
19
20 /* obsolete in R11
21 DELETE FROM po_notifications pon
22 WHERE pon.end_date_active < sysdate; */
23
24 x_progress := '002';
25
26 /* Delete the lot rows that were not processed */
27 DELETE FROM rcv_lots_interface rli
28 WHERE NOT EXISTS
29 (SELECT rti.interface_transaction_id
30 FROM rcv_transactions_interface rti
31 WHERE rti.interface_transaction_id =
32 rli.interface_transaction_id);
33
34
35
36 x_progress := '003';
37 /* Delete the lot rows that were not processed */
38 DELETE FROM rcv_serials_interface rsi
39 WHERE NOT EXISTS
40 (SELECT rti.interface_transaction_id
41 FROM rcv_transactions_interface rti
42 WHERE rti.interface_transaction_id =
43 rsi.interface_transaction_id);
44
45
46 EXCEPTION
47 WHEN OTHERS THEN
48 po_message_s.sql_error('CLEANUP_PO_TABLES',x_progress,sqlcode);
49 raise;
50
51 END cleanup_po_tables;
52
53 /*===========================================================================
54
55 PROCEDURE NAME: get_mtl_parameters()
56
57 ===========================================================================*/
58 PROCEDURE get_mtl_parameters (x_org_id IN NUMBER,
59 x_org_code IN VARCHAR2,
60 x_project_reference_enabled IN OUT NOCOPY NUMBER,
61 x_project_control_level IN OUT NOCOPY NUMBER) IS
62
63 progress varchar2(3) := NULL;
64
65 BEGIN
66
67 progress := '010';
68
69 -- Support for Project Manufacturing
70 -- Get the parameters from the Material_Parameters table
71
72 IF x_org_id is NULL AND
73 x_org_code is NULL THEN
74
75 -- both org_id and org_code are not passed.
76 -- return NULL to the caller
77
78 x_project_reference_enabled := NULL;
79 x_project_control_level := NULL;
80
81 ELSE
82
83 SELECT mp.PROJECT_REFERENCE_ENABLED,
84 mp.PROJECT_CONTROL_LEVEL
85 INTO x_project_reference_enabled,
86 x_project_control_level
87 FROM mtl_parameters mp
88 WHERE mp.organization_id = NVL(x_org_id, mp.organization_id)
89 AND mp.organization_code = NVL(x_org_code, mp.organization_code);
90
91 END IF;
92
93 EXCEPTION
94
95 WHEN NO_DATA_FOUND THEN
96
97 x_project_reference_enabled := NULL;
98 x_project_control_level := NULL;
99
100 WHEN TOO_MANY_ROWS THEN
101
102 x_project_reference_enabled := NULL;
103 x_project_control_level := NULL;
104
105 WHEN OTHERS THEN
106 po_message_s.sql_error('get_mtl_parameters', progress, sqlcode);
107 raise;
108
109 END get_mtl_parameters;
110
111
112 END po_core_s4;