[Home] [Help]
PACKAGE BODY: APPS.CST_LCMADJUSTMENTS_PUB
Source
1 PACKAGE BODY CST_LcmAdjustments_PUB AS
2 /* $Header: CSTLCAMB.pls 120.1.12010000.3 2008/12/28 13:24:12 anjha noship $ */
3
4 /*------------------------------------------------------------------------------------------
5 Landed Cost Manager:
6 The manager launches worker rows of the Landed Cost Adjustment Worker
7 for each organization based on the maximum number of worker rows
8 allowed for the Cost manager.
9 ------------------------------------------------------------------------------------------*/
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):='CST_LcmAdjustments_PUB';
12 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
13 G_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
14 G_LOG_HEAD CONSTANT VARCHAR2(40) := 'po.plsql.'||G_PKG_NAME;
15
16 /*===========================================================================+
17 | |
18 | PROCEDURE : Process_LcmAdjustments |
19 | |
20 | DESCRIPTION : This Procedure is called by the Landed Cost Adjustment |
21 | Processor concurrent program. In the procedure interface |
22 | records are grouped by organization id and stamped with |
23 | group_id and transaction_id. The Landed cost Adjustment |
24 | Worker is launched for each group. |
25 | |
26 | CALLED FROM : Launch_Workers (CST_LcmAdjustments_PUB) |
27 | |
28 | Parameters : |
29 | IN : p_group_id IN NUMBER REQUIRED |
30 | p_organization_id IN NUMBER REQUIRED |
31 | |
32 | OUT : errbuf OUT NOCOPY VARCHAR2 |
33 | retcode OUT NOCOPY NUMBER |
34 | |
35 | NOTES : None |
36 | |
37 | |
38 +===========================================================================*/
39
40 PROCEDURE Launch_Workers
41 (
42 errbuf OUT NOCOPY VARCHAR2,
43 retcode OUT NOCOPY NUMBER
44 )
45
46 IS
47 l_api_name CONSTANT VARCHAR2(30) :='Launch_Workers';
48 l_api_version CONSTANT NUMBER := 1.0;
49 l_return_status VARCHAR2(1);
50 l_module CONSTANT VARCHAR2(100) := 'cst.plsql.'|| G_PKG_NAME || '.' || l_api_name;
51
52 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
53 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
54 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
55 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
56 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
57 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
58
59 l_stmt_num NUMBER;
60
61 l_conc_status BOOLEAN;
62 l_group_id NUMBER;
63 l_maxrows NUMBER;
64 l_request_id NUMBER;
65
66 CURSOR c_org IS
67 SELECT organization_id, primary_cost_method
68 FROM mtl_parameters mp
69 WHERE EXISTS (SELECT 1
70 FROM cst_lc_adj_interface
71 WHERE organization_id = mp.organization_id
72 AND process_status IN (1,2));
73 BEGIN
74
75 l_stmt_num := 0;
76
77 -- Procedure level log message for Entry point
78 IF (l_pLog) THEN
79 FND_LOG.STRING(
80 FND_LOG.LEVEL_PROCEDURE,
81 l_module || '.begin',
82 'Launch_Workers <<'
83 );
84 END IF;
85
86 -- Initialize message list
87 FND_MSG_PUB.initialize;
88
89 -- Initialize API return status to success
90 l_return_status := FND_API.G_RET_STS_SUCCESS;
91
92 l_stmt_num := 10;
93 SELECT worker_rows
94 INTO l_maxrows
95 FROM mtl_interface_proc_controls
96 WHERE process_code = 4;
97
98 l_stmt_num := 20;
99 FOR c_o in c_org LOOP
100
101 l_stmt_num := 30;
102 LOOP
103
104 l_stmt_num := 40;
105 SELECT cst_lc_processor_grp_s.NEXTVAL
106 INTO l_group_id
107 FROM DUAL;
108
109 l_stmt_num := 50;
110 UPDATE cst_lc_adj_interface i
111 SET transaction_id = NVL(transaction_id, CST_LC_ADJ_INTERFACE_TRX_S.NEXTVAL),
112 group_id = l_group_id,
113 request_id = fnd_global.conc_request_id,
114 process_phase = 2
115 WHERE organization_id = c_o.organization_id
116 AND process_status in (1,2)
117 AND (group_id IS NULL
118 OR
119 NOT EXISTS (SELECT 1
120 FROM fnd_concurrent_requests R,
121 fnd_concurrent_programs P
122 WHERE R.program_application_id = P.application_id
123 AND R.concurrent_program_id = P.concurrent_program_id
124 AND P.concurrent_program_name = 'CSTLCADJ'
125 AND R.argument1 = TO_CHAR(i.group_id)
126 AND R.phase_code IN ('I','P','R')))
127 AND (c_o.primary_cost_method IN (1,2)
128 OR
129 NOT EXISTS (SELECT 1
130 FROM mtl_material_transactions
131 WHERE costed_flag in ('N', 'E')
132 AND transaction_date < i.transaction_date
133 AND transaction_source_type_id = 1
134 AND rcv_transaction_id IN (SELECT transaction_id
135 FROM rcv_transactions rt
136 START WITH rt.transaction_id = i.rcv_transaction_id
137 CONNECT BY rt.parent_transaction_id = PRIOR rt.transaction_id)))
138 AND ROWNUM <= l_maxrows;
139
140 EXIT WHEN SQL%NOTFOUND;
141
142 l_stmt_num := 60;
143 COMMIT WORK;
144
145 l_stmt_num := 70;
146 l_request_id := fnd_request.submit_request (
147 application => 'BOM',
148 program => 'CSTLCADJ',
149 argument1 => to_char(l_group_id),
150 argument2 => to_char(c_o.organization_id));
151
152 IF l_request_id = 0 THEN
153 l_stmt_num := 80;
154 UPDATE cst_lc_adj_interface
155 SET group_id = NULL,
156 process_phase = 1
157 WHERE group_id = l_group_id
158 AND organization_id = c_o.organization_id;
159
160 COMMIT WORK;
161 fnd_file.put_line(fnd_file.log, 'Could not launch landed cost adjustment worker for group_id ' || to_char(l_group_id));
162 IF (l_uLog) THEN
163 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,l_module, 'Could not launch landed cost adjustment worker for group_id ' || to_char(l_group_id));
164 END IF;
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 END IF;
167 l_stmt_num := 90;
168 COMMIT WORK;
169
170 END LOOP;
171 l_stmt_num := 100;
172 END LOOP;
173
174 EXCEPTION
175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
176 ROLLBACK;
177 IF (l_uLog) THEN
178 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
179 END IF;
180
181 /* Set concurrent program status to error */
182 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',FND_MESSAGE.GET);
183
184 WHEN FND_API.G_EXC_ERROR THEN
185 ROLLBACK;
186 IF (l_exceptionLog) THEN
187 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,l_module, TRUE);
188 END IF;
189
190 /* Set concurrent program status to error */
191 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',FND_MESSAGE.GET);
192
193 WHEN OTHERS THEN
194 ROLLBACK;
195
196 FND_MESSAGE.SET_NAME('BOM','CST_LOG_UNEXPECTED');
197 FND_MESSAGE.SET_TOKEN('SQLERRM',SQLERRM);
198 IF (l_uLog) THEN
199 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module, TRUE);
200 END IF;
201
202 /* Set concurrent program status to error */
203 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
204 FND_MESSAGE.GET);
205 fnd_file.put_line( FND_FILE.LOG, FND_MESSAGE.GET);
206
207 END Launch_Workers;
208
209 END CST_LcmAdjustments_PUB; -- end package body