[Home] [Help]
PACKAGE BODY: APPS.CSTPALPC
Source
1 PACKAGE BODY CSTPALPC AS
2 /* $Header: CSTALPCB.pls 120.14.12020000.2 2012/12/17 07:50:08 pbasrani ship $ */
3
4 l_debug_flag VARCHAR2(1) := FND_PROFILE.VALUE('MRP_DEBUG');
5 PROCEDURE dyn_proc_call (
6 i_proc_name IN VARCHAR2,
7 i_legal_entity IN NUMBER,
8 i_cost_type IN NUMBER,
9 i_cost_group IN NUMBER,
10 i_period_id IN NUMBER,
11 i_transaction_id IN NUMBER,
12 i_event_type_id IN VARCHAR2,
13 i_txn_type_flag IN VARCHAR2, -- 4586534
14 o_err_num OUT NOCOPY NUMBER,
15 o_err_code OUT NOCOPY VARCHAR2,
16 o_err_msg OUT NOCOPY VARCHAR2
17 )
18 IS
19 l_sql_to_run VARCHAR2(500);
20 CONC_STATUS BOOLEAN;
21 l_err NUMBER := 0;
22 CST_PKG_FAIL EXCEPTION;
23 CST_PKG_FAIL2 EXCEPTION;
24 l_stmt_num NUMBER;
25 BEGIN
26
27 l_stmt_num := 10;
28
29 l_sql_to_run := 'BEGIN ' || i_proc_name || '(';
30
31
32 l_sql_to_run := l_sql_to_run || ':I_LEGAL_ENTITY';
33 l_sql_to_run := l_sql_to_run || ', :I_COST_TYPE_ID';
34 l_sql_to_run := l_sql_to_run || ', :I_COST_GROUP_ID';
35 l_sql_to_run := l_sql_to_run || ', :I_PERIOD_ID';
36 l_sql_to_run := l_sql_to_run || ', :I_TRANSACTION_ID';
37 l_sql_to_run := l_sql_to_run || ', :I_EVENT_TYPE_ID';
38 l_sql_to_run := l_sql_to_run || ', :I_TXN_TYPE_FLAG'; -- 4586534
39 l_sql_to_run := l_sql_to_run || ', :O_ERR_NUM';
40 l_sql_to_run := l_sql_to_run || ', :O_ERR_CODE';
41 l_sql_to_run := l_sql_to_run || ', :O_ERR_MSG';
42 l_sql_to_run := l_sql_to_run || '); END;';
43
44 l_stmt_num := 20;
45
46 EXECUTE IMMEDIATE l_sql_to_run USING
47 I_LEGAL_ENTITY,
48 I_COST_TYPE,
49 I_COST_GROUP,
50 I_PERIOD_ID,
51 I_TRANSACTION_ID,
52 I_EVENT_TYPE_ID,
53 I_TXN_TYPE_FLAG,
54 OUT O_ERR_NUM,
55 OUT O_ERR_CODE,
56 OUT O_ERR_MSG;
57 IF (o_err_num <> 0 and o_err_num is not null) THEN
58 RAISE CST_PKG_FAIL;
59 END IF;
60
61 IF(l_err <> 0) THEN
62 RAISE CST_PKG_FAIL2;
63 END IF;
64
65 EXCEPTION
66 WHEN CST_PKG_FAIL THEN
67 fnd_file.put_line(fnd_file.log,o_err_msg);
68 fnd_file.put_line(fnd_file.log,'CSTPALPC.dyn_proc_call : Error Calling Package');
69 WHEN CST_PKG_FAIL2 THEN
70 o_err_num := l_err;
71 o_err_code := SQLCODE;
72 o_err_msg := 'CSTPALPC.dyn_proc_call ('||l_err||'): Error Calling Package';
73 fnd_file.put_line(fnd_file.log,o_err_msg);
74 WHEN OTHERS THEN
75 o_err_num := 30002;
76 o_err_code := SQLCODE;
77 o_err_msg := 'CSTPALPC.dyn_proc_call : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
78 fnd_file.put_line(fnd_file.log,o_err_msg);
79 END dyn_proc_call;
80
81
82 PROCEDURE create_acct_entry (
83 i_acct_lib_id IN NUMBER,
84 i_legal_entity IN NUMBER,
85 i_cost_type_id IN NUMBER,
86 i_cost_group_id IN NUMBER,
87 i_period_id IN NUMBER,
88 i_mode IN NUMBER,
89 o_err_num OUT NOCOPY NUMBER,
90 o_err_code OUT NOCOPY VARCHAR2,
91 o_err_msg OUT NOCOPY VARCHAR2
92 ) IS
93 l_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
94 l_stmt_num NUMBER;
95 BEGIN
96 IF l_debug_flag = 'Y' THEN
97 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_Acct_Entry <<< ');
98 END IF;
99
100 IF (i_mode = 0) THEN
101 /* Normal Mode */
102 l_stmt_num := 10;
103 CSTPALPC.create_dist_entry (
104 i_acct_lib_id => i_acct_lib_id,
105 i_legal_entity => i_legal_entity,
106 i_cost_type_id => i_cost_type_id,
107 i_cost_group_id => i_cost_group_id,
108 i_period_id => i_period_id,
109 o_err_num => o_err_num,
110 o_err_code => o_err_code,
111 o_err_msg => o_err_msg);
112 ELSE
113 /* Period End */
114 l_stmt_num := 20;
115 CSTPALPC.create_per_end_entry (
116 i_acct_lib_id => i_acct_lib_id,
117 i_legal_entity => i_legal_entity,
118 i_cost_type_id => i_cost_type_id,
119 i_cost_group_id => i_cost_group_id,
120 i_period_id => i_period_id,
121 o_err_num => o_err_num,
122 o_err_code => o_err_code,
123 o_err_msg => o_err_msg);
124 END IF;
125 IF l_debug_flag = 'Y' THEN
126 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_Acct_Entry >>> ');
127 END IF;
128 EXCEPTION
129 WHEN OTHERS THEN
130 o_err_num := 30001;
131 o_err_code := SQLCODE;
132 o_err_msg := 'CSTPALPC.create_acct_entry : ' || to_char(l_stmt_num) || ' :'|| substr(SQLERRM,1,180);
133 fnd_file.put_line(fnd_file.log,l_err_rec.l_err_msg);
134
135 END create_acct_entry;
136
137 PROCEDURE create_dist_entry (
138 i_acct_lib_id IN NUMBER,
139 i_legal_entity IN NUMBER,
140 i_cost_type_id IN NUMBER,
141 i_cost_group_id IN NUMBER,
142 i_period_id IN NUMBER,
143 o_err_num OUT NOCOPY NUMBER,
144 o_err_code OUT NOCOPY VARCHAR2,
145 o_err_msg OUT NOCOPY VARCHAR2
146 ) IS
147
148 CURSOR c_txns IS
149 SELECT mmt.transaction_id "TRANSACTION_ID",
150 mmt.transaction_action_id "TRANSACTION_ACTION_ID",
151 mmt.transaction_source_type_id "TRANSACTION_SOURCE_TYPE_ID",
152 mmt.transaction_type_id "TRANSACTION_TYPE_ID",
153 to_char(null) "TRANSACTION_TYPE",
154 (to_char(mtt.transaction_type_id)||'-'||to_char(mtt.transaction_action_id)||'-'||to_char(mtt.transaction_source_type_id)) "EVENT_TYPE", -- 4986702
155 'INV' "TRANSACTION_TYPE_FLAG", -- 4986702
156 mmt.trx_source_line_id "TRX_SOURCE_LINE_ID"
157 FROM mtl_material_transactions mmt,
158 cst_cost_groups ccg,
159 cst_cost_group_assignments ccga,
160 cst_pac_periods cpp,
161 mtl_transaction_types mtt /* Removed the access to view to directly access the base tables Bug 4968702 */
162 WHERE
163 /* Periodic Cost Updates have the item master organization_id as
164 the organization_id in MMT. In this case, the org_cost_group
165 ID is stamped in MMT */
166
167 /* For Internal Order and Ordinary Interorg Intransit Shipment and Receipt,pick up
168 intermediate transactions as well */
169
170 mmt.organization_id = decode(mmt.transaction_type_id,
171 26, decode(nvl(mmt.org_cost_group_id, -1), ccga.cost_group_id, mmt.organization_id, ccga.organization_id),
172 21,decode(mmt.fob_point,
173 1,mmt.organization_id,
174 ccga.organization_id),
175 62,decode(mmt.fob_point,
176 1,mmt.organization_id,
177 ccga.organization_id),
178 12,decode(mmt.fob_point,
179 2,mmt.organization_id,
180 ccga.organization_id),
181 61,decode(mmt.fob_point,
182 2,mmt.organization_id,
183 ccga.organization_id),
184 ccga.organization_id)
185 AND mmt.organization_id = decode(mmt.transaction_type_id,
186 21,decode(mmt.fob_point,
187 1,mmt.organization_id,
188 NVL(mmt.owning_organization_id, mmt.organization_id)),
189 62,decode(mmt.fob_point,
190 1,mmt.organization_id,
191 NVL(mmt.owning_organization_id, mmt.organization_id)),
192 12,decode(mmt.fob_point,
193 2,mmt.organization_id,
194 NVL(mmt.owning_organization_id, mmt.organization_id)),
195 61,decode(mmt.fob_point,
196 2,mmt.organization_id,
197 NVL(mmt.owning_organization_id, mmt.organization_id)),
198 nvl(mmt.owning_organization_id, mmt.organization_id))
199 AND nvl(mmt.owning_tp_type,2) = 2
200 AND ccga.cost_group_id = ccg.cost_group_id
201 AND ccg.cost_group_id = i_cost_group_id
202 AND ccg.legal_entity = i_legal_entity
203 AND mmt.transaction_date BETWEEN trunc(cpp.period_start_date)
204 AND (trunc(cpp.period_end_date) + 0.99999)
205 AND cpp.pac_period_id = i_period_id
206 --AND caet.transaction_type_flag = 'INV'
207 AND mtt.transaction_type_id = mmt.transaction_type_id -- Join with the base tables.Bug 4968702
208 AND mtt.transaction_action_id = mmt.transaction_action_id
209 AND mtt.transaction_source_type_id = mmt.transaction_source_type_id
210 /* Drop Ship/Global Proc:
211 These transactions will be picked up from the union statement below. For now
212 omit any Drop Ship/Global Proc transactions. */
213 AND ( ( mmt.parent_transaction_id is null
214 AND EXISTS (
215 SELECT 1
216 FROM mtl_pac_actual_cost_details mpacd
217 WHERE mpacd.transaction_id = mmt.transaction_id
218 AND mpacd.pac_period_id = i_period_id
219 AND mpacd.cost_group_id = ccga.cost_group_id
220 AND mpacd.cost_group_id = i_COST_GROUP_ID
221 )
222 )
223 /* Bug7629550: for better performance merged two heavy queries into one */
224 /* This section will pick up all global procurement and Drop shipment txns
225 However, parent physical transactions are omitted, since they have no
226 ditributions against them */
227 OR ( mmt.parent_transaction_id is not null AND
228 nvl(mmt.logical_transaction, 2) = 1
229 )
230 )
231 UNION
232 SELECT
233 wt.transaction_id "TRANSACTION_ID",
234 to_number(null) "TRANSACTION_ACTION_ID",
235 to_number(null) "TRANSACTION_SOURCE_TYPE_ID",
236 wt.transaction_type "TRANSACTION_TYPE_ID",
237 to_char(null) "TRANSACTION_TYPE", -- Directly taking data from mfg_lookups instead of the caet view Bug 4968702
238 (SELECT to_char(lookup_code) FROM mfg_lookups WHERE lookup_type = 'WIP_TRANSACTION_TYPE' AND lookup_code = wt.transaction_type) "EVENT_TYPE", --Bug 4968702
239 'WIP' "TRANSACTION_TYPE_FLAG", -- 4968702
240 NULL "TRX_SOURCE_LINE_ID"
241 FROM
242 wip_transactions wt,
243 cst_cost_groups ccg,
244 cst_cost_group_assignments ccga,
245 cst_pac_periods cpp
246 WHERE
247 wt.organization_id = ccga.organization_id AND
248 ccga.cost_group_id = ccg.cost_group_id AND
249 ccg.cost_group_id = i_cost_group_id AND
250 ccg.legal_entity = i_legal_entity AND
251 cpp.pac_period_id = i_period_id AND
252 wt.transaction_date BETWEEN trunc(cpp.period_start_date)
253 AND (trunc(cpp.period_end_date) + 0.99999) AND
254 (wt.transaction_type = 17 -- Added 17 to support Direct Items as part of eAM support in PAC
255 OR (wt.transaction_type in (1,2,3,6) AND
256 EXISTS (SELECT 1
257 FROM wip_pac_actual_cost_details wpacd
258 WHERE wpacd.transaction_id = wt.transaction_id AND
259 wpacd.pac_period_id = i_period_id AND
260 wpacd.cost_group_id = i_cost_group_id)
261 ))
262 UNION
263 /* Drop Shipment changes: Omit any true drop shipment transactions */
264 SELECT
265 rt.transaction_id "TRANSACTION_ID",
266 to_number(null) "TRANSACTION_ACTION_ID",
267 to_number(null) "TRANSACTION_SOURCE_TYPE_ID",
268 to_number(null) "TRANSACTION_TYPE_ID",
269 rt.transaction_type "TRANSACTION_TYPE",
270 plc.lookup_code "EVENT_TYPE", --4968702 Directly taking data from mfg_lookup_codes instead of view caet
271 'RCV' "TRANSACTION_TYPE_FLAG", -- 4968702
272 NULL "TRX_SOURCE_LINE_ID"
273 FROM
274 rcv_transactions rt,
275 cst_cost_groups ccg,
276 cst_cost_group_assignments ccga,
277 cst_pac_periods cpp,
278 po_lookup_codes plc
279 WHERE
280 rt.organization_id = ccga.organization_id AND
281 NVL(rt.consigned_flag,'N') = 'N' AND
282 NVL(rt.dropship_type_code, 3) <> 1 AND -- FP BUG 5845861 do not pick up txn when DS with old accounting
283 ccga.cost_group_id = ccg.cost_group_id AND
284 ccg.cost_group_id = i_cost_group_id AND
285 ccg.legal_entity = i_legal_entity AND
286 rt.transaction_date BETWEEN trunc(cpp.period_start_date)
287 AND (trunc(cpp.period_end_date) + 0.99999) AND
288 cpp.pac_period_id = i_PERIOD_ID AND
289 plc.lookup_type = 'RCV TRANSACTION TYPE' AND --joining with po_look_up codes. Bug 4968702
290 plc.lookup_code = rt.transaction_type AND
291 rt.source_document_code = 'PO' AND
292 ((rt.transaction_type = 'RECEIVE' AND rt.parent_transaction_id = -1)
293 OR (rt.transaction_type in ('MATCH','RETURN TO VENDOR','RETURN TO RECEIVING','DELIVER'))
294 OR (rt.transaction_type = 'CORRECT' and rt.parent_transaction_id IN
295 (select rt2.transaction_id from rcv_transactions rt2
296 where
297 (rt2.transaction_type = 'RECEIVE' AND rt2.parent_transaction_id = -1) OR
298 (rt2.transaction_type in ('RETURN TO VENDOR','RETURN TO RECEIVING','MATCH','DELIVER'))
299 )
300 )
301 )
302 --pick up global procurement receipts even if set to period end accrual,
303 --since period end will apply only to supplier facing org.
304 AND exists (
305 select 1
306 from po_line_locations_all poll
307 where poll.line_location_id = rt.po_line_location_id and
308 poll.shipment_type <>'PREPAYMENT' and -- Added for complex work procurement
309 (poll.transaction_flow_header_id is not null or
310 ( poll.accrue_on_receipt_flag = 'Y' and
311 not exists (
312 select 1
313 from po_distributions_all pod
314 where pod.line_location_id = poll.line_location_id and
315 accrue_on_receipt_flag = 'N')))
316 )
317 UNION
318 SELECT
319 rae.accounting_event_id "TRANSACTION_ID",
320 to_number(null) "TRANSACTION_ACTION_ID",
321 to_number(null) "TRANSACTION_SOURCE_TYPE_ID",
322 to_number(null) "TRANSACTION_TYPE_ID", -- Removing the usage of CAET here
323 decode(rae.event_type_id, 9, 'LOGICAL RECEIVE', 10, 'LOGICAL RETURN TO VENDOR') "TRANSACTION_TYPE", -- Bug 4968702 Directly Using Base table
324 decode(rae.event_type_id, 9, 'LOGICAL RECEIVE', 10, 'LOGICAL RETURN TO VENDOR') "EVENT_TYPE",-- Bug 4968702 Directly Using Base table
325 'RAE' "TRANSACTION_TYPE_FLAG", -- Bug 4968702
326 NULL "TRX_SOURCE_LINE_ID"
327 FROM
328 rcv_accounting_events rae,
329 rcv_transactions rt,
330 cst_cost_groups ccg,
331 cst_cost_group_assignments ccga,
332 cst_pac_periods cpp
333
334 WHERE
335 rae.organization_id = ccga.organization_id AND
336 rae.rcv_transaction_id = rt.transaction_id AND
337 ccga.cost_group_id = ccg.cost_group_id AND
338 ccg.cost_group_id = i_cost_group_id AND
339 ccg.legal_entity = i_legal_entity AND
340 rae.transaction_date BETWEEN trunc(cpp.period_start_date)
341 AND (trunc(cpp.period_end_date) + 0.99999) AND
342 cpp.pac_period_id = i_PERIOD_ID AND
343 cpp.legal_entity =ccg.legal_entity AND
344 NVL(rt.dropship_type_code, 3) <> 2 AND
345 rae.event_source = 'RECEIVING' AND
346 -- rae.trx_flow_header_id is not null AND /*Bug 5263514*/
347 rae.event_type_id in (9,10) --Logical Receive OR Logical Return to Vendor
348 --omit logical transactions from supplier facing org if accrual option is
349 --period end.
350 AND ( nvl(rae.procurement_org_flag, 'N') = 'N'
351 OR EXISTS (
352 select 1
353 from po_line_locations_all poll
354 where poll.line_location_id = rt.po_line_location_id and
355 poll.accrue_on_receipt_flag = 'Y' and
356 not exists (
357 select 1
358 from po_distributions_all pod
359 where pod.line_location_id = poll.line_location_id and
360 accrue_on_receipt_flag = 'N')
361 ))
362
363 -- Retro Changes---------------------------------------------------------
364 UNION
365 SELECT RAE.ACCOUNTING_EVENT_ID "TRANSACTION_ID",
366 TO_NUMBER(NULL) "TRANSACTION_ACTION_ID",
367 TO_NUMBER(NULL) "TRANSACTION_SOURCE_TYPE_ID",
368 TO_NUMBER(NULL) "TRANSACTION_TYPE_ID",
369 'Adjust Receive' "TRANSACTION_TYPE",
370 'ADJUST RECEIVE' "EVENT_TYPE",
371 'ADJ' "TRANSACTION_TYPE_FLAG",
372 NULL "TRX_SOURCE_LINE_ID"
373 FROM RCV_ACCOUNTING_EVENTS RAE,
374 RCV_TRANSACTIONS RT,
375 CST_COST_GROUPS CCG,
376 CST_COST_GROUP_ASSIGNMENTS CCGA,
377 CST_PAC_PERIODS CPP
378 WHERE RAE.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
379 AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
380 AND CCG.COST_GROUP_ID = i_cost_group_id
381 AND CCG.LEGAL_ENTITY = i_legal_entity
382 AND RAE.TRANSACTION_DATE BETWEEN TRUNC(CPP.PERIOD_START_DATE)
383 AND (TRUNC(CPP.PERIOD_END_DATE) + 0.99999)
384 AND CPP.PAC_PERIOD_ID = i_PERIOD_ID
385 AND RT.TRANSACTION_ID = RAE.RCV_TRANSACTION_ID
386 AND RT.TRANSACTION_DATE < CPP.PERIOD_START_DATE
387 AND RAE.EVENT_TYPE_ID = 7
388 -- EVENT_TYPE_ID = 7 refers to ADJUST_RECEIVE
389 -- Number used to avoid RCV dependencies
390 -------------------------------------------------------------------------
391 /*----LCM CHANGE------------------------------------------*/
392 UNION
393 SELECT RAE.ACCOUNTING_EVENT_ID "TRANSACTION_ID",
394 TO_NUMBER(NULL) "TRANSACTION_ACTION_ID",
395 TO_NUMBER(NULL) "TRANSACTION_SOURCE_TYPE_ID",
396 TO_NUMBER(NULL) "TRANSACTION_TYPE_ID",
397 decode(RAE.EVENT_TYPE_ID,18,'PAC LC ADJ REC',
398 19,'PAC LC ADJ DEL ASSET',
399 20,'PAC LC ADJ DEL EXP') "TRANSACTION_TYPE",
400 decode(RAE.EVENT_TYPE_ID,18,'PAC LC ADJ REC',
401 19,'PAC LC ADJ DEL ASSET',
402 20,'PAC LC ADJ DEL EXP') "EVENT_TYPE",
403 'LC ADJ' "TRANSACTION_TYPE_FLAG",
404 NULL "TRX_SOURCE_LINE_ID"
405 FROM RCV_ACCOUNTING_EVENTS RAE,
406 RCV_TRANSACTIONS RT,
407 CST_COST_GROUPS CCG,
408 CST_COST_GROUP_ASSIGNMENTS CCGA,
409 CST_PAC_PERIODS CPP
410 WHERE RAE.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
411 AND CCGA.COST_GROUP_ID = CCG.COST_GROUP_ID
412 AND CCG.COST_GROUP_ID = i_cost_group_id
413 AND CCG.LEGAL_ENTITY = i_legal_entity
414 AND RAE.TRANSACTION_DATE BETWEEN TRUNC(CPP.PERIOD_START_DATE)
415 AND (TRUNC(CPP.PERIOD_END_DATE) + 0.99999)
416 AND CPP.PAC_PERIOD_ID = i_PERIOD_ID
417 AND RT.TRANSACTION_ID = RAE.RCV_TRANSACTION_ID
418 AND RT.TRANSACTION_DATE < CPP.PERIOD_START_DATE
419 AND RAE.EVENT_TYPE_ID in (18,19,20);
420
421 l_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
422 l_sql_to_run VARCHAR2(500);
423 l_package_name VARCHAR2(100);
424 l_cursor NUMBER;
425 l_event_type_id VARCHAR2(30);
426 l_accounting_package_id NUMBER;
427 l_event_pkg_exists NUMBER;
428 l_stmt_num NUMBER;
429 l_txn_count NUMBER;
430 l_transaction_type_flag VARCHAR2(100);
431 l_period_end NUMBER;
432 l_so_issue_exists NUMBER;
433 CONC_STATUS BOOLEAN;
434 CST_PKG_CALL_FAIL EXCEPTION;
435 BEGIN
436
437 l_stmt_num := 5;
438 IF l_debug_flag = 'Y' THEN
439 fnd_file.put_line(fnd_file.log, 'Create_Dist_Entry <<< ');
440 END IF;
441 l_txn_count := 0;
442
443 FOR c_txns_rec IN c_txns LOOP
444 l_stmt_num := 10;
445 IF l_debug_flag = 'Y' THEN
446 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction: '||to_char(c_txns_rec.transaction_id));
447 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction Type: '||c_txns_rec.transaction_type);
448 END IF;
449 l_transaction_type_flag := c_txns_rec.transaction_type_flag;
450 l_event_type_id := c_txns_rec.event_type;
451
452 l_stmt_num := 20;
453
454 SELECT
455 count(*)
456 INTO
457 l_event_pkg_exists
458 FROM
459 cst_acct_lib_packages calp2
460 WHERE
461 calp2.accounting_lib_id = i_acct_lib_id AND
462 calp2.event_type_id = l_event_type_id;
463
464 IF (l_event_pkg_exists > 0) THEN
465
466 l_stmt_num := 30;
467
468 SELECT
469 cap.accounting_package_id,
470 cap.package_name
471 INTO
472 l_accounting_package_id,
473 l_package_name
474 FROM
475 cst_acct_lib_packages calp,
476 cst_accounting_packages cap
477 WHERE
478 calp.accounting_lib_id = i_acct_lib_id AND
479 calp.event_type_id = l_event_type_id AND
480 cap.accounting_package_id = calp.accounting_package_id;
481
482 ELSE
483
484 l_stmt_num := 40;
485
486 SELECT
487 cap.accounting_package_id,
488 cap.package_name
489 INTO
490 l_accounting_package_id,
491 l_package_name
492 FROM
493 cst_acct_lib_packages calp,
494 cst_accounting_packages cap
495 WHERE
496 calp.accounting_lib_id = i_acct_lib_id AND
497 calp.event_type_id IS NULL AND
498 cap.accounting_package_id = calp.accounting_package_id;
499
500 END IF;
501 IF l_debug_flag = 'Y' THEN
502 fnd_file.put_line(fnd_file.log,'Calling Package '||(l_package_name) ||' ...');
503 END IF;
504
505 l_so_issue_exists := 1;
506
507 IF (c_txns_rec.transaction_type_flag = 'INV' AND
508 c_txns_rec.transaction_action_id = 36 AND
509 c_txns_rec.transaction_source_type_id = 2) THEN
510
511 BEGIN
512 l_stmt_num := 45;
513 SELECT COUNT(1)
514 INTO l_so_issue_exists
515 FROM cst_revenue_cogs_match_lines crcml
516 WHERE cogs_om_line_id = c_txns_rec.trx_source_line_id
517 AND pac_cost_type_id = i_cost_type_id;
518
519 EXCEPTION
520 WHEN NO_DATA_FOUND THEN
521 l_so_issue_exists := 0;
522 END;
523 END IF;
524
525 IF (l_so_issue_exists <> 0) THEN
526
527 dyn_proc_call(
528 l_package_name,
529 i_legal_entity,
530 i_cost_type_id ,
531 i_cost_group_id ,
532 i_period_id ,
533 c_txns_rec.transaction_id,
534 l_event_type_id,
535 l_transaction_type_flag,
536 l_err_rec.l_err_num ,
537 l_err_rec.l_err_code ,
538 l_err_rec.l_err_msg
539 );
540
541 IF (l_err_rec.l_err_num <> 0 and l_err_rec.l_err_num is not null) THEN
542 RAISE CST_PKG_CALL_FAIL;
543 END IF;
544 END IF; -- End of l_so_issue_exists
545
546 l_txn_count := l_txn_count + 1;
547 END LOOP;
548
549 IF l_debug_flag = 'Y' THEN
550 fnd_file.put_line(fnd_file.log,'Total Transactions processed : '||to_char(l_txn_count));
551 fnd_file.put_line(fnd_file.log, 'Create_Dist_Entry >>> ');
552 END IF;
553
554 COMMIT;
555
556 EXCEPTION
557 WHEN CST_PKG_CALL_FAIL THEN
558 o_err_num := l_err_rec.l_err_num;
559 o_err_code := l_err_rec.l_err_code;
560 o_err_msg := l_err_rec.l_err_msg || ': CSTPALPC.create_dist_entry : ' || to_char(l_stmt_num);
561 fnd_file.put_line(fnd_file.log,o_err_msg);
562 WHEN OTHERS THEN
563 o_err_num := 30002;
564 o_err_code := SQLCODE;
565 o_err_msg := 'CSTPALPC.create_dist_entry : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
566 fnd_file.put_line(fnd_file.log,l_err_rec.l_err_msg);
567
568 END create_dist_entry;
569
570 PROCEDURE create_per_end_entry (
571 i_acct_lib_id IN NUMBER,
572 i_legal_entity IN NUMBER,
573 i_cost_type_id IN NUMBER,
574 i_cost_group_id IN NUMBER,
575 i_period_id IN NUMBER,
576 o_err_num OUT NOCOPY NUMBER,
577 o_err_code OUT NOCOPY VARCHAR2,
578 o_err_msg OUT NOCOPY VARCHAR2
579 ) IS
580
581 ---------------------------------------------------------------------------
582 -- Complex Work Procurement Changes
583 -- Transactions related to PREPAYMENT shipment type should not be considered.
584 ---------------------------------------------------------------------------
585 CURSOR c_txns IS
586 SELECT
587 rt.transaction_id "TRANSACTION_ID",
588 to_number(null) "TRANSACTION_ACTION_ID",
589 to_number(null) "TRANSACTION_SOURCE_TYPE_ID",
590 to_number(null) "TRANSACTION_TYPE_ID",
591 rt.transaction_type "TRANSACTION_TYPE",
592 caet.event_type "EVENT_TYPE",
593 caet.transaction_type_flag "TRANSACTION_TYPE_FLAG"
594 FROM
595 rcv_transactions rt,
596 cst_cost_groups ccg,
597 cst_cost_group_assignments ccga,
598 cst_pac_periods cpp,
599 cst_accounting_event_types_v caet
600 WHERE
601 rt.organization_id = ccga.organization_id AND
602 NVL(rt.consigned_flag,'N') = 'N' AND
603 ccga.cost_group_id = ccg.cost_group_id AND
604 ccg.cost_group_id = i_cost_group_id AND
605 ccg.legal_entity = i_legal_entity AND
606 rt.transaction_date <= (trunc(cpp.period_end_date) + 0.99999) AND
607 cpp.pac_period_id = i_period_id AND
608 rt.source_document_code = 'PO' AND
609 caet.transaction_type_flag = 'ACR' AND
610 ((rt.transaction_type = 'RECEIVE' AND rt.parent_transaction_id = -1)
611 OR
612 (rt.transaction_type = 'MATCH'))
613 AND exists (
614 select
615 1
616 from
617 po_line_locations_all poll,
618 po_headers_all poh /*Added for bug 5352511 */
619 where poll.line_location_id = rt.po_line_location_id and
620 poll.accrue_on_receipt_flag = 'N' and /* Begin Bug5352511 */
621 poh.po_header_id = poll.po_header_id and
622 ( (nvl(poll.closed_date,poh.closed_date) >=
623 (trunc(cpp.period_end_date)+0.9999)) OR
624 (nvl(poh.closed_date,poll.closed_date) is null)
625 ) and /* End Bug 5352511 */
626 poll.shipment_type <> 'PREPAYMENT' and
627 not exists (
628 select
629 1
630 from
631 po_distributions_all pod
632 where pod.line_location_id = poll.line_location_id and
633 accrue_on_receipt_flag = 'Y'));
634
635 l_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
636 l_sql_to_run VARCHAR2(500);
637 l_package_name VARCHAR2(100);
638 l_cursor NUMBER;
639 l_event_type_id VARCHAR2(30);
640 l_accounting_package_id NUMBER;
641 l_event_pkg_exists NUMBER;
642 l_stmt_num NUMBER;
643 l_txn_count NUMBER;
644 l_transaction_type_flag VARCHAR2(100);
645 l_period_end NUMBER;
646 CONC_STATUS BOOLEAN;
647 CST_PKG_CALL_FAIL EXCEPTION;
648 BEGIN
649
650
651 fnd_file.put_line(fnd_file.log,'CSTPALPC.create_per_end_entries in');
652 l_txn_count := 0;
653
654 FOR c_txns_rec IN c_txns LOOP
655
656 l_transaction_type_flag := c_txns_rec.transaction_type_flag;
657 fnd_file.put_line(fnd_file.log,'Processing Transaction : '||to_char(c_txns_rec.transaction_id));
658
659 l_stmt_num := 10;
660 l_event_type_id := c_txns_rec.EVENT_TYPE;
661
662 l_stmt_num := 20;
663
664 SELECT
665 count(*)
666 INTO
667 l_event_pkg_exists
668 FROM
669 cst_acct_lib_packages calp2
670 WHERE
671 calp2.accounting_lib_id = i_acct_lib_id AND
672 calp2.event_type_id = l_event_type_id;
673
674 IF (l_event_pkg_exists > 0) THEN
675
676 l_stmt_num := 30;
677
678 SELECT
679 cap.accounting_package_id,
680 cap.package_name
681 INTO
682 l_accounting_package_id,
683 l_package_name
684 FROM
685 cst_acct_lib_packages calp,
686 cst_accounting_packages cap
687 WHERE
688 calp.accounting_lib_id = i_acct_lib_id AND
689 calp.event_type_id = l_event_type_id AND
690 cap.accounting_package_id = calp.accounting_package_id;
691
692 ELSE
693
694 l_stmt_num := 40;
695
696 SELECT
697 cap.accounting_package_id,
698 cap.package_name
699 INTO
700 l_accounting_package_id,
701 l_package_name
702 FROM
703 cst_acct_lib_packages calp,
704 cst_accounting_packages cap
705 WHERE
706 calp.accounting_lib_id = i_acct_lib_id AND
707 calp.event_type_id IS NULL AND
708 cap.accounting_package_id = calp.accounting_package_id;
709
710 END IF;
711
712 fnd_file.put_line(fnd_file.log,'Calling Package '||(l_package_name) ||' ...');
713
714 dyn_proc_call(
715 l_package_name,
716 i_legal_entity,
717 i_cost_type_id ,
718 i_cost_group_id ,
719 i_period_id ,
720 c_txns_rec.transaction_id,
721 l_event_type_id,
722 l_transaction_type_flag,
723 l_err_rec.l_err_num ,
724 l_err_rec.l_err_code ,
725 l_err_rec.l_err_msg
726 );
727
728 IF (l_err_rec.l_err_num <> 0 and l_err_rec.l_err_num is not null) THEN
729 RAISE CST_PKG_CALL_FAIL;
730 END IF;
731 l_txn_count := l_txn_count + 1;
732
733 END LOOP;
734 fnd_file.put_line(fnd_file.log,'Total Transactions processed : '||to_char(l_txn_count));
735
736 --COMMIT;
737
738 EXCEPTION
739 WHEN CST_PKG_CALL_FAIL THEN
740 o_err_num := l_err_rec.l_err_num;
741 o_err_code := l_err_rec.l_err_code;
742 o_err_msg := l_err_rec.l_err_msg;
743 WHEN OTHERS THEN
744 o_err_num := 30002;
745 o_err_code := SQLCODE;
746 o_err_msg := 'CSTPALPC.create_per_end_entry : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
747 fnd_file.put_line(fnd_file.log,l_err_rec.l_err_msg);
748
749 END create_per_end_entry;
750
751
752
753 PROCEDURE insert_ae_lines (
754 i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
755 i_ae_line_rec_tbl IN CSTPALTY.cst_ae_line_tbl_type,
756 o_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
757 )
758 IS
759 l_ae_header_id NUMBER;
760 l_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
761 l_stmt_num NUMBER;
762 l_request_id NUMBER;
763 l_user_id NUMBER;
764 l_login_id NUMBER;
765 l_prog_appl_id NUMBER;
766 l_prog_id NUMBER;
767 CONC_STATUS BOOLEAN;
768
769
770 BEGIN
771 IF l_debug_flag = 'Y' THEN /*Added for bug 15926694*/
772 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CSTPALPC.Insert_Ae_Lines <<< ');
773 END iF;
774
775 l_request_id := FND_GLOBAL.conc_request_id;
776 l_user_id := FND_GLOBAL.user_id;
777 l_login_id := FND_GLOBAL.login_id;
778 l_prog_appl_id := FND_GLOBAL.prog_appl_id;
779 l_prog_id := FND_GLOBAL.conc_program_id;
780
781 l_stmt_num := 10;
782
783 SELECT
784 cst_ae_headers_s.NEXTVAL
785 INTO
786 l_ae_header_id
787 FROM
788 dual;
789
790
791 IF i_ae_line_rec_tbl.EXISTS(1) THEN
792 IF l_debug_flag = 'Y' THEN /*Added for bug 15926694*/
793 fnd_file.put_line(fnd_file.log,'Inserting in Headers table ...');
794 END IF;
795 l_stmt_num := 20;
796
797 INSERT INTO
798 cst_ae_headers (
799 ae_header_id,
800 accounting_event_id,
801 set_of_books_id,
802 legal_entity_id,
803 cost_group_id,
804 cost_type_id,
805 ae_category,
806 period_id,
807 period_name,
808 accounting_date,
809 gl_transfer_flag,
810 gl_transfer_run_id,
811 description,
812 gl_transfer_error_code,
813 acct_event_source_table,
814 organization_id,
815 accounting_error_code,
816 creation_date,
817 created_by,
818 last_update_date,
819 last_updated_by,
820 last_update_login,
821 program_update_date,
822 program_application_id,
823 program_id,
824 request_id,
825 cross_currency_flag,
826 gl_reversal_flag
827 )
828 VALUES
829 (
830 l_ae_header_id,
831 i_ae_txn_rec.transaction_id,
832 i_ae_txn_rec.set_of_books_id,
833 i_ae_txn_rec.legal_entity_id,
834 i_ae_txn_rec.cost_group_id,
835 i_ae_txn_rec.cost_type_id,
836 decode(i_ae_txn_rec.ae_category,'RCV','Receiving',
837 'ACR','Accrual',
838 'ADJ','Receiving',
839 'RAE','Receiving',
840 'LC ADJ','Receiving',i_ae_txn_rec.ae_category),
841 i_ae_txn_rec.accounting_period_id,
842 i_ae_txn_rec.accounting_period_name,
843 i_ae_txn_rec.accounting_date,
844 'N',
845 -1,
846 i_ae_txn_rec.description, --description??
847 NULL, -- gl xfer error code
848 i_ae_txn_rec.source_table,
849 i_ae_txn_rec.organization_id,
850 NULL,
851 sysdate,
852 l_user_id,
853 sysdate,
854 l_user_id,
855 l_login_id,
856 sysdate,
857 l_prog_appl_id,
858 l_prog_id,
859 l_request_id,
860 NULL,
861 decode(i_ae_txn_rec.ae_category,'ACR','Y',NULL)
862 );
863
864 FOR i IN i_ae_line_rec_tbl.FIRST..i_ae_line_rec_tbl.LAST LOOP
865
866 IF l_debug_flag = 'Y' THEN /*Added for bug 15926694*/
867
868 fnd_file.put_line(fnd_file.log,'Inserting in Lines table ...');
869 END IF;
870
871 l_stmt_num := 30;
872 IF (i_ae_line_rec_tbl(i).actual_flag = 'E') then
873
874 INSERT INTO
875 cst_encumbrance_lines (
876 encumbrance_line_id,
877 ae_header_id,
878 ae_line_number,
879 ae_line_type_code,
880 code_combination_id,
881 currency_code,
882 currency_conversion_type,
883 currency_conversion_date,
884 currency_conversion_rate,
885 entered_dr,
886 entered_cr,
887 accounted_dr,
888 accounted_cr,
889 source_table,
890 source_id,
891 rate_or_amount,
892 basis_type,
893 resource_id,
894 cost_element_id,
895 activity_id,
896 repetitive_schedule_id,
897 overhead_basis_factor,
898 basis_resource_id,
899 gl_sl_link_id,
900 description,
901 accounting_error_code,
902 stat_amount,
903 ussgl_transaction_code,
904 subledger_doc_sequence_id,
905 subledger_doc_sequence_value,
906 gl_transfer_error_code,
907 encumbrance_type_id,
908 po_distribution_id,
909 reference1,
910 reference2,
911 reference3,
912 reference4,
913 reference5,
914 reference6,
915 reference7,
916 reference8,
917 reference9,
918 reference10,
919 creation_date,
920 created_by,
921 last_update_date,
922 last_updated_by,
923 last_update_login,
924 program_update_date,
925 program_application_id,
926 program_id,
927 request_id
928 )
929 VALUES
930 (
931 cst_encumbrance_lines_s.nextval,
932 l_ae_header_id,
933 i,
934 i_ae_line_rec_tbl(i).ae_line_type,
935 i_ae_line_rec_tbl(i).account,
936 i_ae_line_rec_tbl(i).currency_code,
937 i_ae_line_rec_tbl(i).currency_conv_type,
938 i_ae_line_rec_tbl(i).currency_conv_date,
939 decode(i_ae_line_rec_tbl(i).currency_conv_rate,
940 -1,decode(i_ae_line_rec_tbl(i).currency_code,
941 NULL,NULL,
942 i_ae_line_rec_tbl(i).currency_conv_rate),
943 i_ae_line_rec_tbl(i).currency_conv_rate),
944 i_ae_line_rec_tbl(i).entered_dr,
945 i_ae_line_rec_tbl(i).entered_cr,
946 i_ae_line_rec_tbl(i).accounted_dr,
947 i_ae_line_rec_tbl(i).accounted_cr,
948 i_ae_line_rec_tbl(i).source_table, -- source table
949 i_ae_line_rec_tbl(i).source_id, -- source id
950 i_ae_line_rec_tbl(i).rate_or_amount,
951 i_ae_line_rec_tbl(i).basis_type,
952 i_ae_line_rec_tbl(i).resource_id,
953 i_ae_line_rec_tbl(i).cost_element_id,
954 i_ae_line_rec_tbl(i).activity_id,
955 i_ae_line_rec_tbl(i).repetitive_schedule_id,
956 i_ae_line_rec_tbl(i).overhead_basis_factor,
957 i_ae_line_rec_tbl(i).basis_resource_id,
958 NULL, -- gl_sl_link?? null
959 i_ae_line_rec_tbl(i).description, -- desc accting line desc
960 NULL, -- error code null
961 NULL, -- stat amount null
962 NULL, -- ussgl null
963 NULL, -- sub ledger doc seq id
964 NULL, -- sub ledger doc seq value
965 NULL, -- gl xfer error code
966 i_ae_line_rec_tbl(i).encum_type_id,
967 i_ae_line_rec_tbl(i).po_distribution_id,
968 i_ae_line_rec_tbl(i).reference1,
969 i_ae_line_rec_tbl(i).reference2,
970 i_ae_line_rec_tbl(i).reference3,
971 i_ae_line_rec_tbl(i).reference4,
972 i_ae_line_rec_tbl(i).reference5,
973 i_ae_line_rec_tbl(i).reference6,
974 i_ae_line_rec_tbl(i).reference7,
975 i_ae_line_rec_tbl(i).reference8,
976 i_ae_line_rec_tbl(i).reference9,
977 i_ae_line_rec_tbl(i).reference10,
978 sysdate,
979 l_user_id,
980 sysdate,
981 l_user_id,
982 l_login_id,
983 sysdate,
984 l_prog_appl_id,
985 l_prog_id,
986 l_request_id
987 );
988
989 ELSE
990
991 INSERT INTO
992 cst_ae_lines (
993 ae_line_id,
994 ae_header_id,
995 ae_line_number,
996 ae_line_type_code,
997 code_combination_id,
998 currency_code,
999 currency_conversion_type,
1000 currency_conversion_date,
1001 currency_conversion_rate,
1002 entered_dr,
1003 entered_cr,
1004 accounted_dr,
1005 accounted_cr,
1006 source_table,
1007 source_id,
1008 rate_or_amount,
1009 basis_type,
1010 resource_id,
1011 cost_element_id,
1012 activity_id,
1013 repetitive_schedule_id,
1014 overhead_basis_factor,
1015 basis_resource_id,
1016 gl_sl_link_id,
1017 description,
1018 accounting_error_code,
1019 stat_amount,
1020 ussgl_transaction_code,
1021 subledger_doc_sequence_id,
1022 subledger_doc_sequence_value,
1023 gl_transfer_error_code,
1024 po_distribution_id,
1025 wip_entity_id,
1026 reference1,
1027 reference2,
1028 reference3,
1029 reference4,
1030 reference5,
1031 reference6,
1032 reference7,
1033 reference8,
1034 reference9,
1035 reference10,
1036 creation_date,
1037 created_by,
1038 last_update_date,
1039 last_updated_by,
1040 last_update_login,
1041 program_update_date,
1042 program_application_id,
1043 program_id,
1044 request_id
1045 )
1046 VALUES
1047 (
1048 cst_ae_lines_s.nextval,
1049 l_ae_header_id,
1050 i,
1051 i_ae_line_rec_tbl(i).ae_line_type,
1052 i_ae_line_rec_tbl(i).account,
1053 i_ae_line_rec_tbl(i).currency_code,
1054 i_ae_line_rec_tbl(i).currency_conv_type,
1055 i_ae_line_rec_tbl(i).currency_conv_date,
1056 decode(i_ae_line_rec_tbl(i).currency_conv_rate,
1057 -1,decode(i_ae_line_rec_tbl(i).currency_code,
1058 NULL,NULL,
1059 i_ae_line_rec_tbl(i).currency_conv_rate),
1060 i_ae_line_rec_tbl(i).currency_conv_rate),
1061 i_ae_line_rec_tbl(i).entered_dr,
1062 i_ae_line_rec_tbl(i).entered_cr,
1063 i_ae_line_rec_tbl(i).accounted_dr,
1064 i_ae_line_rec_tbl(i).accounted_cr,
1065 i_ae_line_rec_tbl(i).source_table, -- source table
1066 i_ae_line_rec_tbl(i).source_id, -- source id
1067 i_ae_line_rec_tbl(i).rate_or_amount,
1068 i_ae_line_rec_tbl(i).basis_type,
1069 i_ae_line_rec_tbl(i).resource_id,
1070 i_ae_line_rec_tbl(i).cost_element_id,
1071 i_ae_line_rec_tbl(i).activity_id,
1072 i_ae_line_rec_tbl(i).repetitive_schedule_id,
1073 i_ae_line_rec_tbl(i).overhead_basis_factor,
1074 i_ae_line_rec_tbl(i).basis_resource_id,
1075 NULL, -- gl_sl_link?? null
1076 i_ae_line_rec_tbl(i).description, -- desc accting line desc
1077 NULL, -- error code null
1078 NULL, -- stat amount null
1079 NULL, -- ussgl null
1080 NULL, -- sub ledger doc seq id
1081 NULL, -- sub ledger doc seq value
1082 NULL, -- gl xfer error code
1083 i_ae_line_rec_tbl(i).po_distribution_id,
1084 i_ae_line_rec_tbl(i).wip_entity_id,
1085 i_ae_line_rec_tbl(i).reference1,
1086 i_ae_line_rec_tbl(i).reference2,
1087 i_ae_line_rec_tbl(i).reference3,
1088 i_ae_line_rec_tbl(i).reference4,
1089 i_ae_line_rec_tbl(i).reference5,
1090 i_ae_line_rec_tbl(i).reference6,
1091 i_ae_line_rec_tbl(i).reference7,
1092 i_ae_line_rec_tbl(i).reference8,
1093 i_ae_line_rec_tbl(i).reference9,
1094 i_ae_line_rec_tbl(i).reference10,
1095 sysdate,
1096 l_user_id,
1097 sysdate,
1098 l_user_id,
1099 l_login_id,
1100 sysdate,
1101 l_prog_appl_id,
1102 l_prog_id,
1103 l_request_id
1104 );
1105 END IF;
1106
1107
1108 END LOOP;
1109
1110 END IF;
1111 IF l_debug_flag = 'Y' THEN /*Added for bug 15926694*/
1112 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CSTPALPC.Insert_Ae_Lines >>> ');
1113 END IF;
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 o_err_rec.l_err_num := 30001;
1117 o_err_rec.l_err_code := SQLCODE;
1118 o_err_rec.l_err_msg := 'CSTPALPC.insert_ae_lines : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
1119 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',o_err_rec.l_err_msg);
1120 fnd_file.put_line(fnd_file.log,o_err_rec.l_err_msg);
1121
1122
1123
1124 END insert_ae_lines;
1125
1126 END CSTPALPC;
1127