[Home] [Help]
PACKAGE BODY: APPS.PO_CORE_S4
Source
1 PACKAGE BODY PO_CORE_S4 AS
2 /* $Header: POXCOC4B.pls 120.1 2011/03/03 12:39:58 sbontala ship $*/
3
4 --<Bug 11071489 : REQ_AUTOCREATE start>---
5 g_log_head CONSTANT VARCHAR2(30) := 'po.plsql.PO_CORE_S4.';
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
7 --<REQ_AUTOCREATE end>---
8 /*===========================================================================
9
10 PROCEDURE NAME: cleanup_po_tables()
11
12 ===========================================================================*/
13 PROCEDURE cleanup_po_tables is
14
15 x_progress varchar2(3) := NULL;
16
17 BEGIN
18
19
20 x_progress := '001';
21
22
23 /* Delete the old notifications from the table. */
24
25 /* obsolete in R11
26 DELETE FROM po_notifications pon
27 WHERE pon.end_date_active < sysdate; */
28
29 x_progress := '002';
30
31 /* Delete the lot rows that were not processed */
32 DELETE FROM rcv_lots_interface rli
33 WHERE NOT EXISTS
34 (SELECT rti.interface_transaction_id
35 FROM rcv_transactions_interface rti
36 WHERE rti.interface_transaction_id =
37 rli.interface_transaction_id);
38
39
40
41 x_progress := '003';
42 /* Delete the lot rows that were not processed */
43 DELETE FROM rcv_serials_interface rsi
44 WHERE NOT EXISTS
45 (SELECT rti.interface_transaction_id
46 FROM rcv_transactions_interface rti
47 WHERE rti.interface_transaction_id =
48 rsi.interface_transaction_id);
49
50
51 EXCEPTION
52 WHEN OTHERS THEN
53 po_message_s.sql_error('CLEANUP_PO_TABLES',x_progress,sqlcode);
54 raise;
55
56 END cleanup_po_tables;
57
58 /*===========================================================================
59
60 PROCEDURE NAME: get_mtl_parameters()
61
62 ===========================================================================*/
63 PROCEDURE get_mtl_parameters (x_org_id IN NUMBER,
64 x_org_code IN VARCHAR2,
65 x_project_reference_enabled IN OUT NOCOPY NUMBER,
66 x_project_control_level IN OUT NOCOPY NUMBER) IS
67
68 progress varchar2(3) := NULL;
69
70 BEGIN
71
72 progress := '010';
73
74 -- Support for Project Manufacturing
75 -- Get the parameters from the Material_Parameters table
76
77 IF x_org_id is NULL AND
78 x_org_code is NULL THEN
79
80 -- both org_id and org_code are not passed.
81 -- return NULL to the caller
82
83 x_project_reference_enabled := NULL;
84 x_project_control_level := NULL;
85
86 ELSE
87
88 SELECT mp.PROJECT_REFERENCE_ENABLED,
89 mp.PROJECT_CONTROL_LEVEL
90 INTO x_project_reference_enabled,
91 x_project_control_level
92 FROM mtl_parameters mp
93 WHERE mp.organization_id = NVL(x_org_id, mp.organization_id)
94 AND mp.organization_code = NVL(x_org_code, mp.organization_code);
95
96 END IF;
97
98 EXCEPTION
99
100 WHEN NO_DATA_FOUND THEN
101
102 x_project_reference_enabled := NULL;
103 x_project_control_level := NULL;
104
105 WHEN TOO_MANY_ROWS THEN
106
107 x_project_reference_enabled := NULL;
108 x_project_control_level := NULL;
109
110 WHEN OTHERS THEN
111 po_message_s.sql_error('get_mtl_parameters', progress, sqlcode);
112 raise;
113
114 END get_mtl_parameters;
115 --<Bug 11071489 : REQ_AUTOCREATE start>---
116 /*===========================================================================
117 PROCEDURE NAME: raise_business_event()
118
119 DESCRIPTION: The procedure raises business event in autocreate process
120 for the requisitions processed.
121 ===========================================================================*/
122
123 PROCEDURE raise_business_event(x_event_name VARCHAR2 , x_parameter_list IN p_parameter_list)
124 IS
125 l_api_name VARCHAR2(30) := 'raise_business_event';
126 l_progress VARCHAR2(3) := '000';
127 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
128 p_event_key number;
129
130 BEGIN
131
132 IF g_debug_stmt THEN
133 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
134 END IF;
135
136 --Adding all the paramters passed from autocreate process into wf parameter list
137 l_progress := '001';
138
139 IF g_debug_stmt THEN
140 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
141 END IF;
142
143 IF g_debug_stmt THEN
144 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
145 p_token => l_progress,
146 p_message => 'Count of Parameter List ' || x_parameter_list.COUNT);
147 END IF;
148
149 IF x_parameter_list.COUNT > 0 then
150 FOR i IN x_parameter_list.FIRST .. x_parameter_list.LAST LOOP
151
152
153 wf_event.AddParameterToList(p_name=>x_parameter_list(i).name,
154 p_value=>x_parameter_list(i).value,
155 p_parameterlist=>l_parameter_list);
156 END LOOP;
157
158 SELECT po_wf_itemkey_s.NEXTVAL INTO p_event_key FROM dual;
159
160 --Raising the autocreate.requisition business event
161 wf_event.raise( p_event_name =>x_event_name,
162 p_event_key => TO_CHAR(p_event_key),
163 p_parameters => l_parameter_list);
164
165 l_parameter_list.delete;
166 END if;
167
168 IF g_debug_stmt THEN
169 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
170 END IF;
171
172 EXCEPTION
173
174 WHEN OTHERS THEN
175 po_message_s.sql_error('raise_business_event', l_progress, sqlcode);
176 raise;
177
178 END raise_business_event;
179 ---<REQ_AUTOCREATE- End>--
180
181 END po_core_s4;