[Home] [Help]
PACKAGE BODY: APPS.CSTPPIPV
Source
1 PACKAGE BODY CSTPPIPV AS
2 /* $Header: CSTPIPVB.pls 120.17 2011/11/14 01:38:10 yuyun ship $ */
3
4 /*---------------------------------------------------------------------------*
5 | PUBLIC PROCEDURE |
6 | trf_invoice_to_inventory |
7 | |
8 | p_item_option: |
9 | 1: All Asset items |
10 | 2: Specific Asset Item |
11 | 5: Category Items |
12 | |
13 | p_invoice_project_option: |
14 | 1: All invoices |
15 | 2: Project invoices |
16 | |
17 | p_transaction_process_mode: |
18 | 1: Ready to be processed by Inventory Transaction Manager |
19 | 2: Hold |
20 | |
21 | aida.inventory_transfer_status: |
22 | N: Not transferred |
23 | Null: Transferred or Not Applicable |
24 | |
25 *----------------------------------------------------------------------------*/
26
27 PROCEDURE trf_invoice_to_inventory(
28 errbuf OUT NOCOPY VARCHAR2,
29 retcode OUT NOCOPY NUMBER,
30 p_organization_id IN NUMBER,
31 p_description IN VARCHAR2 DEFAULT NULL,
32 p_item_option IN NUMBER,
33 p_item_dummy IN NUMBER DEFAULT NULL,
34 p_category_dummy IN NUMBER DEFAULT NULL,
35 p_specific_item_id IN NUMBER DEFAULT NULL,
36 p_category_set_id IN NUMBER DEFAULT NULL,
37 p_category_validate_flag IN VARCHAR2 DEFAULT NULL,
38 p_category_structure IN NUMBER DEFAULT NULL,
39 p_category_id IN NUMBER DEFAULT NULL,
40 p_invoice_project_option IN NUMBER,
41 p_project_dummy IN NUMBER DEFAULT NULL,
42 p_project_id IN NUMBER DEFAULT NULL,
43 p_adj_account_dummy IN NUMBER,
44 p_adj_account IN NUMBER,
45 p_cutoff_date IN VARCHAR2,
46 p_transaction_process_mode IN NUMBER
47 )
48 IS
49
50 l_cutoff_date DATE;
51 l_org_id NUMBER;
52 l_cost_group_id NUMBER;
53 l_batch_id NUMBER;
54 l_request_id NUMBER;
55 l_user_id NUMBER;
56 l_prog_id NUMBER;
57 l_prog_app_id NUMBER;
58 l_login_id NUMBER;
59 l_conc_program_id NUMBER;
60 l_stmt_num NUMBER;
61 l_err_num NUMBER;
62 l_err_code VARCHAR2(240);
63 l_err_msg VARCHAR2(240);
64 l_cost_method NUMBER;
65 l_default_txn_date DATE;
66 l_first_date DATE;
67 l_last_date DATE;
68 l_txn_date_profile NUMBER;
69 l_dummy NUMBER;
70 l_debug VARCHAR2(80);
71 l_default_cost_group_id number;
72 conc_status BOOLEAN;
73 cst_process_error EXCEPTION;
74 cst_cstppipv_running EXCEPTION;
75 cst_no_avg_org EXCEPTION;
76
77 -- For a given org, this cursor will give list of items and cost group that
78 -- have invoices not yet interfaced with inventory
79
80
81 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 Start */
82
83 l_process_enabled_flag mtl_parameters.process_enabled_flag%TYPE;
84 l_organization_code mtl_parameters.organization_code%TYPE;
85
86 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 End */
87
88 /* Invoice Lines Project: lookup_code needs to be ITEM and ACCRUAL, not just ITEM */
89
90 CURSOR c_item
91 IS
92
93 SELECT DISTINCT cql.inventory_item_id item_id,
94 cql.cost_group_id cg_id,
95 cql.layer_id layer_id
96 FROM cst_quantity_layers cql
97 WHERE NVL(cql.layer_quantity,0) > 0
98 AND cql.organization_id = l_org_id
99 AND ( p_item_option = 1
100 OR (p_item_option = 2
101 AND cql.inventory_item_id = p_specific_item_id
102 )
103 OR (p_item_option = 5
104 AND EXISTS
105 ( SELECT 'X'
106 FROM mtl_item_categories mic
107 WHERE mic.organization_id =
108 cql.organization_id
109 AND mic.category_id =
110 p_category_id
111 AND mic.category_set_id =
112 p_category_set_id
113 AND mic.inventory_item_id =
114 cql.inventory_item_id
115 )
116 )
117 )
118 AND EXISTS
119 ( SELECT 'X'
120 FROM ap_invoice_distributions_all aida,
121 po_distributions_all pda,
122 po_line_locations_all plla,
123 po_lines_all pla
124 WHERE aida.po_distribution_id = pda.po_distribution_id
125 AND aida.posted_flag = 'Y'
126 --AND NVL(aida.reversal_flag,'N') <> 'Y'
127 AND aida.accounting_date <= l_cutoff_date
128 AND aida.inventory_transfer_status = 'N'
129 AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL')
130 AND pda.destination_type_code = 'INVENTORY'
131 AND pda.destination_organization_id =
132 l_org_id
133 AND plla.line_location_id = pda.line_location_id
134 AND pla.po_line_id = plla.po_line_id
135 AND pla.item_id = cql.inventory_item_id
136 AND (
137 ( p_invoice_project_option = 1
138 AND pda.project_id IS NULL
139 AND cql.cost_group_id = l_default_cost_group_id)
140 OR
141 ( pda.project_id IS NOT NULL
142 AND EXISTS
143 (SELECT 'X'
144 FROM pjm_project_parameters ppp
145 WHERE ppp.organization_id = l_org_id
146 AND ppp.costing_group_id =
147 cql.cost_group_id
148 AND ppp.project_id = pda.project_id
149 AND ppp.project_id =
150 decode(p_invoice_project_option,
151 1, ppp.project_id,
152 p_project_id)
153 )
154 )
155 )
156 -- J Changes ----------------------------------------------------------------
157 -- AND aida.root_distribution_id IS NULL
158 ------------------------------------------------------------------------------
159 /* Invoice Lines Project: root_distribution_id does not exist, replaced with corrected_invoice_dist_id */
160 AND aida.corrected_invoice_dist_id IS NULL
161 );
162 BEGIN
163
164 ---------------------------------------------------------------------
165 -- Initializing Variables
166 ---------------------------------------------------------------------
167 l_err_num := 0;
168 l_err_code := '';
169 l_err_msg := '';
170 l_cost_group_id := -1;
171 l_request_id := 0;
172 l_user_id := 0;
173 l_prog_id := 0;
174 l_prog_app_id := 0;
175 l_login_id := 0;
176 l_default_txn_date := NULL;
177 l_first_date := NULL;
178 l_last_date := NULL;
179 l_org_id := p_organization_id;
180 l_cutoff_date := FND_DATE.canonical_to_date(p_cutoff_date);
181
182 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 Start */
183 BEGIN
184
185 SELECT default_cost_group_id
186 , nvl(process_enabled_flag,'N')
187 , organization_code
188 INTO l_default_cost_group_id
189 , l_process_enabled_flag
190 , l_organization_code
191 FROM mtl_parameters
192 WHERE organization_id = l_org_id;
193
194 IF nvl(l_process_enabled_flag,'N') = 'Y' THEN
195 l_err_num := 30001;
196 fnd_message.set_name('GMF', 'GMF_PROCESS_ORG_ERROR');
197 fnd_message.set_token('ORGCODE', l_organization_code);
198 l_err_msg := FND_MESSAGE.Get;
199 l_err_msg := substrb('CSTPIPVB : ' || l_err_msg,1,240);
200 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
201 fnd_file.put_line(fnd_file.log,l_err_msg);
202 RETURN;
203 END IF;
204
205 EXCEPTION
206 WHEN no_data_found THEN
207 l_process_enabled_flag := 'N';
208 l_organization_code := NULL;
209 END;
210 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 End */
211
212 ----------------------------------------------------------------------
213 -- retrieving concurrent program information
214 ----------------------------------------------------------------------
215 l_stmt_num := 5;
216
217 l_request_id := FND_GLOBAL.conc_request_id;
218 l_user_id := FND_GLOBAL.user_id;
219 l_prog_id := FND_GLOBAL.conc_program_id;
220 l_prog_app_id := FND_GLOBAL.prog_appl_id;
221 l_login_id := FND_GLOBAL.conc_login_id;
222 l_conc_program_id := FND_GLOBAL.conc_program_id;
223 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
224 l_txn_date_profile := FND_PROFILE.VALUE('TRANSACTION_DATE');
225
226 l_stmt_num := 10;
227
228 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transfer Invoice to Inventory');
229
230 FND_FILE.PUT_LINE(FND_FILE.LOG, 'request_id: '
231 ||to_char(l_request_id));
232 FND_FILE.PUT_LINE(FND_FILE.LOG, 'prog_appl_id: '
233 ||to_char(l_prog_app_id));
234 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_user_id: '
235 ||to_char(l_user_id));
236 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_program_id: '
237 ||to_char(l_prog_id));
238 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_login_id: '
239 ||to_char(l_login_id));
240 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_conc_program_id: '
241 ||to_char(l_conc_program_id));
242 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Debug: '
243 ||l_debug);
244 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Txn Date Profile: '
245 ||TO_CHAR(l_txn_date_profile));
246 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Organization: '
247 ||TO_CHAR(p_organization_id));
248 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Description: '
249 ||p_description);
250 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item Option: '
251 ||TO_CHAR(p_item_option));
252 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item dummy: '
253 ||TO_CHAR(p_item_dummy));
254 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category_dummy: '
255 ||TO_CHAR(p_category_dummy));
256 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Specific Item: '
257 ||TO_CHAR(p_specific_item_id));
258 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category set id: '
259 ||TO_CHAR(p_category_set_id));
260 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Validate Flag: '
261 ||p_category_validate_flag);
262 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Structure: '
263 ||TO_CHAR(p_category_structure));
264 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category: '
265 ||TO_CHAR(p_category_id));
266 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoice Project Option: '
267 ||TO_CHAR(p_invoice_project_option));
268 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Project Dummy: '
269 ||TO_CHAR(p_project_dummy));
270 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Project Id: '
271 ||TO_CHAR(p_project_id));
272 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Adjustment Account Dummy: '
273 ||TO_CHAR(p_adj_account_dummy));
274 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Adjustment Account: '
275 ||TO_CHAR(p_adj_account));
276 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoice Cutoff Date: '
277 ||p_cutoff_date);
278 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Txn Process Mode: '
279 ||TO_CHAR(p_transaction_process_mode));
280
281 ----------------------------------------------------------------------
282 -- Make sure there is no other program running with the same args
283 -- argument1 = organization
284 -- argument3 = item option
285 -- argument6 = specific item
286 -- argument11 = invoice project option
287 -- argument13 = project
288 -- Error out logic :-
289 -- * If either program in the same org has item option of All/Catg
290 -- * If both prog running with specif item but either have all projects
291 -- * if both prog running with same item, specific proj but same proj
292 ----------------------------------------------------------------------
293 l_stmt_num := 15;
294
295 BEGIN
296 SELECT fcr.request_id
297 INTO l_dummy
298 FROM fnd_concurrent_requests fcr
299 WHERE program_application_id = 702
300 AND concurrent_program_id = l_conc_program_id
301 AND phase_code IN ('I','P','R')
302 AND argument1 = TO_CHAR(p_organization_id)
303 AND ( (argument3 IN ('1', '5') OR p_item_option IN (1,5))
304 OR (argument3 = '2'
305 AND argument6 = TO_CHAR(p_specific_item_id)
306 AND (argument11 = '1' OR p_invoice_project_option = 1)
307 )
308 OR (argument3 = '2'
309 AND argument6 = TO_CHAR(p_specific_item_id)
310 AND argument11 = '2'
311 AND argument13 = TO_CHAR(p_project_id)
312 )
313 )
314 AND fcr.request_id <> l_request_id
315 AND ROWNUM=1;
316
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 l_dummy := -1;
320 END;
321
322 IF (l_dummy <> -1) THEN
323 RAISE CST_CSTPPIPV_RUNNING;
324 END IF;
325
326 l_org_id := p_organization_id;
327
328 ----------------------------------------------------------------------
329 -- Check that the organization is average.
330 ----------------------------------------------------------------------
331 l_stmt_num := 20;
332
333 SELECT mp.primary_cost_method
334 INTO l_cost_method
335 FROM mtl_parameters mp
336 WHERE mp.organization_id = l_org_id;
337
338 IF (l_cost_method <> 2) THEN
339 RAISE CST_NO_AVG_ORG;
340 END IF;
341
342 ----------------------------------------------------------------------
343 -- Set aida rows with 'N' status but which have no IPV to NULL.
344 -- Invoice Lines Project: no invoice_price_variance column...need to
345 -- go to separate IPV distribution
346 -- Added a filter of accounting date on aida and join of invoice_id
347 -- between aida and aida2 for performance improvement - bug4137765
348 ----------------------------------------------------------------------
349 l_stmt_num := 25;
350
351 UPDATE ap_invoice_distributions_all aida
352 SET aida.inventory_transfer_status = NULL
353 WHERE po_distribution_id IS NOT NULL
354 /*AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL','NONREC_TAX') */
355 /*Bug 9823230: Commented as per internal discussion to enhance performance and set transfer status
356 to null for all LINE_TYPE_LOOKUP_CODE not eligible for IPV transfer */
357 AND aida.inventory_transfer_status = 'N'
358 AND aida.posted_flag = 'Y' --BUG#5709567-FPBUG#5109100
359 AND aida.accounting_date <= l_cutoff_date
360 AND NOT EXISTS
361 (
362 SELECT 'X'
363 FROM ap_invoice_distributions_all aida2
364 WHERE
365 (
366 (
367 aida2.line_type_lookup_code = 'IPV'
368 /* Start of bug 8270017 */
369 AND (
370 (aida.invoice_id = aida2.invoice_id and aida.invoice_distribution_id = aida2.related_id)
371 or
372 (aida.invoice_distribution_id = aida2.corrected_invoice_dist_id)
373
374 )
375 /* End of bug 8270017 */
376 )
377 OR /* Start of Bug 8681379*/
378 (
379 aida2.line_type_lookup_code IN ('TIPV','TERV','TRV')
380 and aida.invoice_id = aida2.invoice_id
381 and (aida.invoice_distribution_id = aida2.charge_applicable_to_dist_id
382 OR
383 aida.invoice_distribution_id = aida2.related_id /*added condition bug 8681379*/
384 )
385 )
386
387 )
388 );
389
390 IF (l_debug = 'Y') THEN
391 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)
392 ||' Rows marked as NULL in AIDA ');
393 END IF;
394
395
396 --------------------------------------------------------------------
397 -- Create a batch for the the process --
398 --------------------------------------------------------------------
399 l_stmt_num := 30;
400
401 SELECT cst_ap_variance_batches_s.nextval
402 INTO l_batch_id
403 FROM DUAL;
404
405 -- Populate Batch table here
406 l_stmt_num := 35;
407
408 INSERT INTO cst_ap_variance_batches
409 ( batch_id,
410 description,
411 organization_id,
412 item_option,
413 invoice_project_option,
414 adjustment_account,
415 cutoff_date,
416 transaction_process_mode,
417 specific_item_id,
418 specific_project_id,
419 creation_date,
420 last_update_date,
421 last_updated_by,
422 created_by,
423 request_id,
424 program_application_id,
425 program_id,
426 program_update_date,
427 last_update_login
428 )
429 VALUES
430 ( l_batch_id,
431 p_description,
432 p_organization_id,
433 P_item_option,
434 p_invoice_project_option,
435 p_adj_account,
436 l_cutoff_date,
437 P_transaction_process_mode,
438 p_specific_item_id,
439 p_project_id,
440 SYSDATE,
441 SYSDATE,
442 l_user_id,
443 l_user_id,
444 l_request_id,
445 l_prog_app_id,
446 l_prog_id,
447 SYSDATE,
448 l_login_id
449 );
450
451
452 ------------------------------------------------------------------
453 -- Calculate default transaction date
454 ------------------------------------------------------------------
455 l_stmt_num := 40;
456
457 get_default_date (p_organization_id => l_org_id,
458 x_default_date => l_default_txn_date,
459 x_err_num => l_err_num,
460 x_err_code => l_err_code,
461 x_err_msg => l_err_msg
462 );
463
464 IF (l_err_num <> 0) THEN
465 RAISE cst_process_error;
466 END IF;
467
468 IF (l_debug = 'Y') THEN
469 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch: '||TO_CHAR(l_batch_id));
470 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Default Date: '
471 ||TO_CHAR(l_default_txn_date));
472 END IF;
473
474 --------------------------------------------------------------------
475 -- Get all inventory item in this org that require variance adj
476 --------------------------------------------------------------------
477 l_stmt_num := 45;
478
479 FOR c_item_rec IN c_item LOOP
480
481 l_cost_group_id := c_item_rec.cg_id;
482
483 IF (l_debug = 'Y') THEN
484 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item: '
485 ||TO_CHAR(c_item_rec.item_id));
486 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CG: '||TO_CHAR(l_cost_group_id));
487 END IF;
488
489 l_stmt_num := 50;
490 CSTPPIPV.generate_trf_info
491 ( p_organization_id => l_org_id,
492 p_inventory_item_id => c_item_rec.item_id,
493 p_invoice_project_option => p_invoice_project_option,
494 p_project_id => p_project_id,
495 p_cost_group_id => l_cost_group_id,
496 p_cutoff_date => l_cutoff_date,
497 p_user_id => l_user_id,
498 p_login_id => l_login_id,
499 p_request_id => l_request_id,
500 p_prog_id => l_prog_id,
501 p_prog_app_id => l_prog_app_id,
502 p_batch_id => l_batch_id,
503 p_default_txn_date => l_default_txn_date,
504 x_err_num => l_err_num,
505 x_err_code => l_err_code,
506 x_err_msg => l_err_msg
507 );
508
509 IF (l_err_num <> 0) THEN
510 RAISE cst_process_error;
511 END IF;
512
513 END LOOP; -- c_item_rec
514
515 -------------------------------------------------------------------
516 -- create MTL_TRANSACTIONS_INTERFACE row for each POD,
517 -- with transaction_type = 80 (Average Cost Update)
518 -- If var_amount <> 0
519 -------------------------------------------------------------------
520 l_stmt_num := 55;
521
522 INSERT INTO mtl_transactions_interface
523 (
524 transaction_interface_id,
525 source_code,
526 source_line_id, -- cavh.variance_header_id
527 source_header_id, -- cavh.batch_id
528 process_flag,
529 transaction_mode,
530 last_update_date,
531 last_updated_by,
532 creation_date,
533 created_by,
534 last_update_login,
535 request_id,
536 program_application_id,
537 program_id,
538 validation_required,
539 inventory_item_id,
540 organization_id,
541 cost_group_id,
542 transaction_date,
543 transaction_quantity,
544 transaction_uom,
545 transaction_type_id,
546 value_change,
547 material_account,
548 transaction_reference -- cavh.po_distribution_id
549 )
550 (
551 SELECT mtl_material_transactions_s.nextval,
552 'VARIANCE TRF',
553 cavh.variance_header_id,
554 cavh.batch_id,
555 p_transaction_process_mode,
556 3,
557 SYSDATE,
558 l_user_id,
559 SYSDATE,
560 l_user_id,
561 l_login_id,
562 l_request_id,
563 l_prog_app_id,
564 l_prog_id,
565 1,
566 cavh.inventory_item_id,
567 cavh.organization_id,
568 cavh.cost_group_id,
569 decode(l_txn_date_profile, 2, SYSDATE,
570 cavh.transaction_date),
571 0,
572 msi.primary_uom_code,
573 80,
574 cavh.var_amount,
575 p_adj_account,
576 'PO Distribution: '|| TO_CHAR (cavh.po_distribution_id)
577 FROM cst_ap_variance_headers cavh,
578 mtl_system_items msi
579 WHERE cavh.batch_id = l_batch_id
580 AND cavh.var_amount <> 0
581 AND cavh.inventory_item_id = msi.inventory_item_id
582 AND cavh.organization_id = msi.organization_id
583 );
584
585 IF (l_debug = 'Y') THEN
586 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)
587 ||' Rows inserted into MTI');
588 END IF;
589
590 -------------------------------------------------------------------
591 -- Create detail row MTL_TXN_COST_DET_INTERFACE for each mti row
592 -- previously created for the batch to ensure that value change
593 -- will update this level/material cost element only.
594 -------------------------------------------------------------------
595
596 l_stmt_num := 60;
597
598 INSERT INTO mtl_txn_cost_det_interface
599 ( transaction_interface_id,
600 last_update_date,
601 last_updated_by,
602 creation_date,
603 created_by,
604 last_update_login,
605 request_id,
606 program_application_id,
607 program_id,
608 organization_id,
609 cost_element_id,
610 level_type,
611 value_change
612 )
613 (SELECT mti.transaction_interface_id,
614 SYSDATE,
615 l_user_id,
616 SYSDATE,
617 l_user_id,
618 l_login_id,
619 l_request_id,
620 l_prog_app_id,
621 l_prog_id,
622 mti.organization_id,
623 1, -- cost element id = 1
624 1, -- this level = 1
625 mti.value_change
626 FROM mtl_transactions_interface mti
627 WHERE mti.source_header_id = l_batch_id
628 );
629
630 IF (l_debug = 'Y') THEN
631
632 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)
633 ||' Rows inserted into MTCDI');
634 END IF;
635
636 COMMIT;
637
638 EXCEPTION
639
640 WHEN CST_CSTPPIPV_RUNNING THEN
641 ROLLBACK;
642 l_err_num := 20009;
643
644 l_err_code := SUBSTR('CSTPPIPV.trf_invoice_to_inventory('
645 || to_char(l_stmt_num)
646 || '): '
647 || 'Req_id: '
648 || TO_CHAR(l_dummy)
649 ||' '
650 || l_err_msg
651 || '. ',1,240);
652
653 fnd_message.set_name('BOM', 'CST_CSTPPIPV_RUNNING');
654 l_err_msg := fnd_message.get;
655 l_err_msg := SUBSTR(l_err_msg,1,240);
656 FND_FILE.PUT_LINE(fnd_file.log,SUBSTR(l_err_code
657 ||' '
658 ||l_err_msg,1,240));
659 CONC_STATUS := FND_CONCURRENT.
660 SET_COMPLETION_STATUS('ERROR',l_err_msg);
661
662
663 WHEN CST_NO_AVG_ORG THEN
664 ROLLBACK;
665 l_err_num := 20010;
666
667 l_err_code := SUBSTR('CSTPPIPV.trf_invoice_to_inventory('
668 || to_char(l_stmt_num)
669 || '): '
670 || l_err_msg
671 || '. ',1,240);
672
673 fnd_message.set_name('BOM', 'CST_NO_AVG_ORG');
674 l_err_msg := fnd_message.get;
675 l_err_msg := SUBSTR(l_err_msg,1,240);
676 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
677 CONC_STATUS := FND_CONCURRENT.
678 SET_COMPLETION_STATUS('ERROR',l_err_msg);
679
680
681 WHEN CST_PROCESS_ERROR THEN
682 ROLLBACK;
683 l_err_num := l_err_num;
684 l_err_code := l_err_code;
685 l_err_msg := SUBSTR(l_err_msg,1,240);
686 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
687 CONC_STATUS := FND_CONCURRENT.
688 SET_COMPLETION_STATUS('ERROR',l_err_msg);
689
690
691 WHEN OTHERS THEN
692 ROLLBACK;
693 l_err_num := SQLCODE;
694 l_err_code := NULL;
695 l_err_msg := SUBSTR('CSTPPIPV.trf_invoice_to_inventory('
696 || to_char(l_stmt_num)
697 || '): '
698 ||SQLERRM,1,240);
699 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
700 CONC_STATUS := FND_CONCURRENT.
701 SET_COMPLETION_STATUS('ERROR',l_err_msg);
702
703
704 END trf_invoice_to_inventory;
705
706 /*---------------------------------------------------------------------------*
707 | PRIVATE PROCEDURE |
708 | trf_invoice_to_wip |
709 | This procedure generates the necessary interface transactions to |
710 | transfer the invoice price variances of items that match the user |
711 | specified parameters to the corresponding work orders in Work In |
712 | Process. Currently it's only processing the invoice price variances |
713 | of Outside Processing and Direct items for Maintenance Work Order. |
714 | |
715 | p_item_type: |
716 | 1: Outside Processing and Direct items |
717 | 2: Outside Processing items only |
718 | 3: Direct Items only |
719 | |
720 | p_item_option: |
721 | 1: All Asset items |
722 | 2: Specific Asset Item |
723 | 5: Category Items |
724 | |
725 | p_invoice_project_option: |
726 | 1: All invoices |
727 | 2: Project invoices |
728 | |
729 | p_transaction_process_mode: |
730 | 1: Real transfer, update AP table to mark transferred invoices |
731 | 2: Simulated transfer, does not update AP tables |
732 | |
733 | aida.inventory_transfer_status: |
734 | N: Not transferred |
735 | Null: Transferred or Not Applicable |
736 | |
737 *----------------------------------------------------------------------------*/
738
739 FUNCTION trf_invoice_to_wip(
740 errbuf OUT NOCOPY VARCHAR2,
741 retcode OUT NOCOPY NUMBER,
742 p_organization_id IN NUMBER,
743 p_description IN VARCHAR2 DEFAULT NULL,
744 p_work_order_id IN NUMBER DEFAULT NULL,
745 p_item_type IN NUMBER,
746 p_item_option IN NUMBER DEFAULT NULL,
747 p_specific_item_id IN NUMBER DEFAULT NULL,
748 p_category_set_id IN NUMBER DEFAULT NULL,
749 p_category_id IN NUMBER DEFAULT NULL,
750 p_project_id IN NUMBER DEFAULT NULL,
751 p_adj_account IN NUMBER,
752 p_cutoff_date IN VARCHAR2,
753 p_transaction_process_mode IN NUMBER,
754 p_request_id IN NUMBER,
755 p_user_id IN NUMBER,
756 p_login_id IN NUMBER,
757 p_prog_appl_id IN NUMBER,
758 p_prog_id IN NUMBER
759 )
760 RETURN NUMBER IS
761
762 l_cutoff_date DATE;
763 l_batch_id NUMBER;
764 l_request_id NUMBER;
765 l_user_id NUMBER;
766 l_item_option NUMBER;
767 l_project_option NUMBER;
768 l_prog_id NUMBER;
769 l_prog_app_id NUMBER;
770 l_login_id NUMBER;
771 l_conc_program_id NUMBER;
772 l_stmt_num NUMBER;
773 l_err_num NUMBER;
774 l_err_code VARCHAR2(240);
775 l_err_msg VARCHAR2(240);
776 l_default_txn_date DATE;
777 l_txn_date_profile NUMBER;
778 l_dummy NUMBER;
779 l_debug VARCHAR2(80);
780 l_legal_entity NUMBER;
781 l_row_count NUMBER;
782 l_server_day_time DATE;
783 l_le_day_time DATE;
784 l_server_le_offset NUMBER;
785 conc_status BOOLEAN;
786 cst_process_error EXCEPTION;
787 cst_cstppipv_running EXCEPTION;
788
789 TYPE dists_tab_type is TABLE OF po_distributions_all.po_distribution_id%TYPE INDEX BY BINARY_INTEGER;
790 l_po_dists_tab dists_tab_type;
791
792 -- This cursor lists PO distributions w/ invoice distributions that has not been
793 -- transferred to Inventory nor WIP.
794
795 CURSOR c_po_dist
796 IS
797 SELECT DISTINCT
798 pda.po_distribution_id,
799 pla.item_id inventory_item_id,
800 pda.project_id
801 FROM po_distributions_all pda,
802 po_line_locations_all plla,
803 po_lines_all pla,
804 wip_entities we,
805 wip_discrete_jobs wdj
806 WHERE ( ( ( p_item_type = 1 -- OSP and direct
807 OR p_item_type = 2) -- OSP only
808 AND ( ( l_item_option = 1 -- All items
809 AND EXISTS (
810 SELECT 'X'
811 FROM mtl_system_items_b msi
812 WHERE msi.organization_id = p_organization_id
813 AND msi.inventory_item_id = pla.item_id
814 AND msi.outside_operation_flag = 'Y' and rownum <2) )
815 OR ( l_item_option = 2 -- Specific item
816 AND EXISTS (
817 SELECT 'X'
818 FROM mtl_system_items_b msi
819 WHERE msi.organization_id = p_organization_id
820 AND msi.inventory_item_id = p_specific_item_id
821 AND msi.inventory_item_id = pla.item_id
822 AND msi.outside_operation_flag = 'Y' and rownum <2 ) )
823 OR ( l_item_option = 5 -- Category items
824 AND EXISTS (
825 SELECT 'X'
826 FROM mtl_item_categories mic,
827 mtl_system_items_b msi
828 WHERE mic.organization_id = p_organization_id
829 AND mic.category_id = p_category_id
830 AND mic.category_set_id = p_category_set_id
831 AND mic.inventory_item_id = msi.inventory_item_id
832 AND msi.organization_id = p_organization_id
833 AND msi.inventory_item_id = pla.item_id
834 AND msi.outside_operation_flag = 'Y' and rownum <2 ) )))
835 OR ( ( p_item_type = 1 -- OSP and direct
836 OR p_item_type = 3) -- direct only
837 AND ( pla.item_id IS NULL
838 OR EXISTS (
839 SELECT 'X'
840 FROM mtl_system_items_b msi
841 WHERE msi.organization_id = p_organization_id
842 AND msi.inventory_item_id = pla.item_id
843 AND msi.stock_enabled_flag = 'N'
844 )
845 )
846 )
847 )
848 AND plla.po_line_id = pla.po_line_id
849 AND pda.line_location_id = plla.line_location_id
850 AND pda.destination_type_code = 'SHOP FLOOR'
851 AND pda.destination_organization_id = p_organization_id
852 AND pda.wip_entity_id = nvl(p_work_order_id,pda.wip_entity_id)
853 AND we.wip_entity_id = pda.wip_entity_id
854 AND we.entity_type = 6 -- open maintenance work order
855 AND wdj.wip_entity_id = pda.wip_entity_id
856 AND wdj.status_type in (3,4) -- released / completed work order
857 AND ( ( pda.project_id IS NULL
858 AND l_project_option = 1)
859 OR ( pda.project_id IS NOT NULL
860 AND EXISTS (
861 SELECT 'X'
862 FROM pjm_project_parameters ppp
863 WHERE ppp.organization_id = p_organization_id
864 AND ppp.project_id = pda.project_id
865 AND ppp.project_id = decode(
866 l_project_option,
867 1,
868 ppp.project_id,
869 p_project_id)
870 and rownum <2) ))
871 AND EXISTS (
872 SELECT 'X'
873 FROM ap_invoice_distributions_all aida
874 WHERE aida.po_distribution_id = pda.po_distribution_id
875 AND aida.posted_flag = 'Y'
876 AND aida.accounting_date < l_cutoff_date
877 AND aida.inventory_transfer_status = 'N'
878 AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL') --same change as earlier
879 -- J Changes -----------------------------------------------------------------
880 -- AND aida.root_distribution_id IS NULL
881 -------------------------------------------------------------------------------
882 AND aida.corrected_invoice_dist_id IS NULL --same change as earlier
883 and rownum <2 ) ;
884
885 /* bug 4873742 -- added to bypass performance repository issues */
886
887 cursor c_po_dists2 is
888 SELECT pda.po_distribution_id
889 FROM po_distributions_all pda,
890 wip_entities we,
891 wip_discrete_jobs wdj,
892 wip_operation_resources wor
893 WHERE we.wip_entity_id = pda.wip_entity_id
894 AND we.entity_type = 6
895 AND wdj.wip_entity_id = pda.wip_entity_id
896 AND wdj.status_type = 3
897 AND wor.wip_entity_id = pda.wip_entity_id
898 AND wor.operation_seq_num = pda.wip_operation_seq_num
899 AND wor.resource_seq_num = pda.wip_resource_seq_num
900 AND wor.standard_rate_flag = 1;
901
902 BEGIN
903
904 ---------------------------------------------------------------------
905 -- Initializing Variables
906 ---------------------------------------------------------------------
907 l_err_num := 0;
908 l_err_code := '';
909 l_err_msg := '';
910 l_request_id := 0;
911 l_user_id := 0;
912 l_prog_id := 0;
913 l_prog_app_id := 0;
914 l_login_id := 0;
915 l_default_txn_date := NULL;
916 l_cutoff_date := FND_DATE.canonical_to_date(p_cutoff_date);
917 l_row_count := 0;
918
919 -- Set item option to 1 (All items) if item type is not 2 (OSP only)
920 IF (p_item_type <> 2) THEN
921 l_item_option := 1;
922 ELSE
923 l_item_option := p_item_option;
924 END IF;
925
926 -- Set invoice project option to 1 if no project is specified
927 IF (p_project_id IS NULL) THEN
928 l_project_option := 1;
929 ELSE
930 l_project_option := 2;
931 END IF;
932
933 ----------------------------------------------------------------------
934 -- Retrieving concurrent program information
935 ----------------------------------------------------------------------
936 l_stmt_num := 5;
937
938 l_request_id := p_request_id;
939 l_user_id := p_user_id;
940 l_prog_id := p_prog_id;
941 l_prog_app_id := p_prog_appl_id;
942 l_login_id := p_login_id;
943 l_conc_program_id := p_prog_id;
944 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
945 l_txn_date_profile := FND_PROFILE.VALUE('TRANSACTION_DATE');
946
947 l_stmt_num := 10;
948
949 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transfer Invoice to Work In Process');
950
951 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Request Id: '
952 ||to_char(l_request_id));
953 FND_FILE.PUT_LINE(FND_FILE.LOG, 'User Id: '
954 ||to_char(l_user_id));
955 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Login Id: '
956 ||to_char(l_login_id));
957 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Program Id: '
958 ||to_char(l_prog_id));
959 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Prog Appl Id: '
960 ||to_char(l_prog_app_id));
961 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Conc Program Id: '
962 ||to_char(l_conc_program_id));
963 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Debug: '
964 ||l_debug);
965 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Txn Date Profile: '
966 ||TO_CHAR(l_txn_date_profile));
967 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Organization: '
968 ||TO_CHAR(p_organization_id));
969 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Description: '
970 ||p_description);
971 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Specific Work Order: '
972 ||TO_CHAR(p_work_order_id));
973 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item Type: '
974 ||TO_CHAR(p_item_type));
975 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item Option: '
976 ||TO_CHAR(l_item_option));
977 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Specific Item: '
978 ||TO_CHAR(p_specific_item_id));
979 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category set id: '
980 ||TO_CHAR(p_category_set_id));
981 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category: '
982 ||TO_CHAR(p_category_id));
983 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoice Project Option: '
984 ||TO_CHAR(l_project_option));
985 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Project Id: '
986 ||TO_CHAR(p_project_id));
987 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Adjustment Account: '
988 ||TO_CHAR(p_adj_account));
989 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoice Cutoff Date: '
990 ||p_cutoff_date);
991 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Txn Process Mode: '
992 ||TO_CHAR(p_transaction_process_mode));
993
994 ----------------------------------------------------------------------
995 -- Make sure there is no other program running with the same args
996 -- argument2 = organization
997 -- argument6 = item option
998 -- argument9 = specific item
999 -- argument14 = invoice project option
1000 -- argument16 = project
1001 -- Error out logic :-
1002 -- * If either program in the same org has item option of All/Catg
1003 -- * If both prog running with same item but either have all projects
1004 -- * if both prog running with same item, specific proj but same proj
1005 ----------------------------------------------------------------------
1006
1007 l_stmt_num := 15;
1008
1009 BEGIN
1010 SELECT fcr.request_id
1011 INTO l_dummy
1012 FROM fnd_concurrent_requests fcr
1013 WHERE program_application_id = 702
1014 AND concurrent_program_id = l_conc_program_id
1015 AND phase_code IN ('I','P','R')
1016 AND argument2 = TO_CHAR(p_organization_id)
1017 AND ( argument6 IN ('1', '5')
1018 OR l_item_option IN (1,5)
1019 OR ( argument6 = '2'
1020 AND argument9 = TO_CHAR(p_specific_item_id)
1021 AND ( argument14 = '1'
1022 OR l_project_option = 1
1023 OR (argument16 = TO_CHAR(p_project_id)))))
1024 AND fcr.request_id <> l_request_id
1025 AND ROWNUM=1;
1026
1027 EXCEPTION
1028 WHEN NO_DATA_FOUND THEN
1029 l_dummy := -1;
1030 END;
1031
1032 IF (l_dummy <> -1) THEN
1033 RAISE CST_CSTPPIPV_RUNNING;
1034 END IF;
1035
1036 ----------------------------------------------------------------------
1037 -- Set aida rows with 'N' status but which was created for an OSP
1038 -- with standard rate to 'S'.
1039 ----------------------------------------------------------------------
1040
1041 l_stmt_num := 22;
1042
1043 /* bug 4873742 -- added to bypass performance repository issues */
1044
1045 open c_po_dists2;
1046 loop
1047 fetch c_po_dists2 bulk collect into l_po_dists_tab limit 1000 ;
1048 exit when (c_po_dists2%notfound);
1049
1050 forall i in l_po_dists_tab.first..l_po_dists_tab.last
1051
1052
1053 UPDATE ap_invoice_distributions_all aida
1054 SET aida.inventory_transfer_status = 'S'
1055 WHERE aida.inventory_transfer_status = 'N'
1056 AND aida.posted_flag = 'Y'
1057 AND aida.accounting_date <= p_cutoff_date
1058 AND aida.po_distribution_id = l_po_dists_tab(i);
1059
1060 l_row_count := l_row_count +SQL%ROWCOUNT;
1061
1062 end loop;
1063
1064 close c_po_dists2;
1065
1066 IF (l_debug = 'Y') THEN
1067 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)||' rows marked as S in AIDA');
1068 END IF;
1069
1070 ----------------------------------------------------------------------
1071 -- Set aida rows with 'N' status but which have no IPV to NULL.
1072 -- Added a filter of accounting date on aida and join of invoice_id
1073 -- between aida and aida2 for performance improvement - bug4137765
1074 ----------------------------------------------------------------------
1075
1076 l_stmt_num := 25;
1077
1078 UPDATE ap_invoice_distributions_all aida
1079 SET aida.inventory_transfer_status = NULL
1080 WHERE po_distribution_id IS NOT NULL
1081 /*AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL','NONREC_TAX') */
1082 /* Bug 9823230: Commented as per internal discussion to enhance performance and set transfer status
1083 to null for all LINE_TYPE_LOOKUP_CODE not eligible for IPV transfer*/
1084 AND aida.inventory_transfer_status = 'N'
1085 AND aida.posted_flag = 'Y'
1086 AND aida.accounting_date <= l_cutoff_date
1087 AND NOT EXISTS --same change as earlier
1088 (
1089 SELECT 'X'
1090 FROM ap_invoice_distributions_all aida2
1091 WHERE
1092 (
1093 (
1094 aida2.line_type_lookup_code = 'IPV'
1095 /* Start of bug 8270017 */
1096 AND
1097 (
1098 (aida.invoice_id = aida2.invoice_id and aida.invoice_distribution_id = aida2.related_id)
1099 or
1100 (aida.invoice_distribution_id = aida2.corrected_invoice_dist_id)
1101 )
1102 /* End of bug 8270017 */
1103 )
1104 OR /* Start of Bug 8681379*/
1105 (
1106 aida2.line_type_lookup_code IN ('TIPV','TERV','TRV')
1107 and aida.invoice_id = aida2.invoice_id
1108 and (aida.invoice_distribution_id = aida2.charge_applicable_to_dist_id
1109 OR
1110 aida.invoice_distribution_id = aida2.related_id /*added condition bug 8681379*/
1111 )
1112 ) /*End of Bug 8681379*/
1113 )
1114 );
1115
1116
1117 IF (l_debug = 'Y') THEN
1118 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)||' rows marked as NULL in AIDA');
1119 END IF;
1120
1121 ----------------------------------------------------------------------
1122 -- Create a batch for the the process
1123 ----------------------------------------------------------------------
1124
1125 l_stmt_num := 30;
1126
1127 SELECT cst_ap_variance_batches_s.nextval
1128 INTO l_batch_id
1129 FROM DUAL;
1130
1131 -- Populate Batch table here
1132 l_stmt_num := 35;
1133
1134 INSERT INTO
1135 cst_ap_variance_batches
1136 (
1137 batch_id,
1138 organization_id,
1139 item_option,
1140 invoice_project_option,
1141 adjustment_account,
1142 cutoff_date,
1143 transaction_process_mode,
1144 specific_item_id,
1145 specific_project_id,
1146 category_id,
1147 category_set_id,
1148 creation_date,
1149 last_update_date,
1150 last_updated_by,
1151 created_by,
1152 request_id,
1153 program_application_id,
1154 program_id,
1155 program_update_date,
1156 last_update_login,
1157 description,
1158 wip_entity_id,
1159 item_type)
1160 VALUES (
1161 l_batch_id,
1162 p_organization_id,
1163 l_item_option,
1164 l_project_option,
1165 p_adj_account,
1166 l_cutoff_date,
1167 p_transaction_process_mode,
1168 p_specific_item_id,
1169 p_project_id,
1170 p_category_id,
1171 p_category_set_id,
1172 SYSDATE,
1173 SYSDATE,
1174 l_user_id,
1175 l_user_id,
1176 l_request_id,
1177 l_prog_app_id,
1178 l_prog_id,
1179 SYSDATE,
1180 l_login_id,
1181 p_description,
1182 p_work_order_id,
1183 p_item_type);
1184
1185 ----------------------------------------------------------------------
1186 -- Calculate default transaction date
1187 ----------------------------------------------------------------------
1188
1189 l_stmt_num := 40;
1190
1191 get_default_date (
1192 p_organization_id => p_organization_id,
1193 x_default_date => l_default_txn_date,
1194 x_err_num => l_err_num,
1195 x_err_code => l_err_code,
1196 x_err_msg => l_err_msg
1197 );
1198
1199 IF (l_err_num <> 0) THEN
1200 RAISE cst_process_error;
1201 END IF;
1202
1203 IF (l_debug = 'Y') THEN
1204 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch: '||TO_CHAR(l_batch_id));
1205 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Default Date: '||TO_CHAR(l_default_txn_date));
1206 END IF;
1207
1208 ----------------------------------------------------------------------
1209 -- Calculate timezone offset from legal entity time to server time
1210 ----------------------------------------------------------------------
1211 l_legal_entity := 0;
1212 l_server_day_time := NULL;
1213 l_le_day_time := SYSDATE;
1214 l_server_le_offset := 0;
1215
1216 l_stmt_num := 42;
1217
1218 /* select legal entity from HR_ORGANIZATION_INFORMATION instead of cst_organization_definitions
1219 for performance improvement */
1220
1221 SELECT org_information2
1222 INTO l_legal_entity
1223 FROM HR_ORGANIZATION_INFORMATION
1224 where ORG_INFORMATION_CONTEXT = 'Accounting Information'
1225 and organization_id = p_organization_id;
1226
1227 l_server_day_time := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1228 l_le_day_time,
1229 l_legal_entity);
1230
1231 l_server_le_offset := l_server_day_time - l_le_day_time;
1232
1233 ----------------------------------------------------------------------
1234 -- Generate IPV information for the obtained PO distributions
1235 ----------------------------------------------------------------------
1236
1237 l_stmt_num := 45;
1238
1239 FOR c_po_dist_rec IN c_po_dist LOOP
1240
1241 IF (l_debug = 'Y') THEN
1242 FND_FILE.PUT_LINE(FND_FILE.LOG, 'PO Distribution: '
1243 ||TO_CHAR(c_po_dist_rec.po_distribution_id));
1244 END IF;
1245
1246 l_stmt_num := 50;
1247
1248 CSTPPIPV.generate_wip_info (
1249 p_organization_id => p_organization_id,
1250 p_inventory_item_id => c_po_dist_rec.inventory_item_id,
1251 p_project_id => c_po_dist_rec.project_id,
1252 p_po_distribution_id => c_po_dist_rec.po_distribution_id,
1253 p_cutoff_date => l_cutoff_date,
1254 p_user_id => l_user_id,
1255 p_login_id => l_login_id,
1256 p_request_id => l_request_id,
1257 p_prog_id => l_prog_id,
1258 p_prog_app_id => l_prog_app_id,
1259 p_batch_id => l_batch_id,
1260 p_transaction_process_mode => p_transaction_process_mode,
1261 p_default_txn_date => l_default_txn_date,
1262 x_err_num => l_err_num,
1263 x_err_code => l_err_code,
1264 x_err_msg => l_err_msg
1265 );
1266
1267 IF (l_err_num <> 0) THEN
1268 RAISE cst_process_error;
1269 END IF;
1270
1271 END LOOP; -- c_po_dist_rec
1272
1273 -------------------------------------------------------------------
1274 -- create WIP_COST_TXN_INTERFACE row for each POD,
1275 -- with transaction_type 3 (OSP) and 17 (DP)
1276 -- If var_amount <> 0
1277 -------------------------------------------------------------------
1278
1279 l_stmt_num := 55;
1280
1281 IF (p_transaction_process_mode = 1) THEN
1282 INSERT INTO
1283 wip_cost_txn_interface (
1284 transaction_id,
1285 last_update_date,
1286 last_updated_by,
1287 last_updated_by_name,
1288 creation_date,
1289 created_by,
1290 created_by_name,
1291 last_update_login,
1292 request_id,
1293 program_application_id,
1294 program_id,
1295 program_update_date,
1296 source_code,
1297 source_line_id,
1298 process_phase,
1299 process_status,
1300 transaction_type,
1301 organization_id,
1302 organization_code,
1303 wip_entity_id,
1304 entity_type,
1305 primary_item_id,
1306 transaction_date,
1307 acct_period_id,
1308 operation_seq_num,
1309 resource_seq_num,
1310 department_id,
1311 resource_id,
1312 usage_rate_or_amount,
1313 basis_type,
1314 autocharge_type,
1315 standard_rate_flag,
1316 transaction_quantity,
1317 transaction_uom,
1318 primary_quantity,
1319 primary_uom,
1320 actual_resource_rate,
1321 reference,
1322 po_header_id,
1323 po_line_id,
1324 receiving_account_id)
1325 (
1326 SELECT NULL,
1327 SYSDATE,
1328 l_user_id,
1329 fu.user_name,
1330 SYSDATE,
1331 l_user_id,
1332 fu.user_name,
1333 l_login_id,
1334 l_request_id,
1335 l_prog_app_id,
1336 l_prog_id,
1337 SYSDATE,
1338 'IPV',
1339 cavh.variance_header_id,
1340 decode(nvl(wor.resource_id,-1),-1,2,1),
1341 1,
1342 decode(nvl(wor.resource_id,-1),-1,17,3),
1343 cavh.organization_id,
1344 mp.organization_code,
1345 pda.wip_entity_id,
1346 6, -- Open Maintenance Job
1347 cavh.inventory_item_id,
1348 decode(l_txn_date_profile,2,SYSDATE,cavh.transaction_date),
1349 oap.acct_period_id,
1350 pda.wip_operation_seq_num,
1351 pda.wip_resource_seq_num,
1352 wor.department_id,
1353 wor.resource_id,
1354 cavh.var_amount,
1355 wor.basis_type,
1356 wor.autocharge_type,
1357 2, -- Standard Rate Flag
1358 0, -- Transaction Quantity
1359 wor.uom_code,
1360 0, -- Primary Quantity
1361 wor.uom_code,
1362 cavh.var_amount,
1363 'PO Distribution: '|| TO_CHAR (cavh.po_distribution_id),
1364 pda.po_header_id,
1365 pda.po_line_id,
1366 p_adj_account
1367 FROM cst_ap_variance_headers cavh,
1368 po_distributions_all pda,
1369 wip_operation_resources wor,
1370 org_acct_periods oap,
1371 mtl_parameters mp,
1372 fnd_user fu
1373 WHERE cavh.batch_id = l_batch_id
1374 AND cavh.var_amount <> 0
1375 AND pda.po_distribution_id = cavh.po_distribution_id
1376 AND wor.wip_entity_id (+) = pda.wip_entity_id
1377 AND wor.operation_seq_num (+) = pda.wip_operation_seq_num
1378 AND wor.resource_seq_num (+) = pda.wip_resource_seq_num
1379 AND oap.organization_id = cavh.organization_id
1380 AND decode(l_txn_date_profile,2,SYSDATE,cavh.transaction_date)
1381 BETWEEN (oap.period_start_date + l_server_le_offset)
1382 AND (oap.schedule_close_date+.99999 + l_server_le_offset)
1383 AND mp.organization_id = cavh.organization_id
1384 AND fu.user_id = l_user_id);
1385
1386 IF (l_debug = 'Y') THEN
1387 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)
1388 ||' Rows inserted into WCTI');
1389 END IF;
1390
1391 END IF;
1392
1393 RETURN l_batch_id;
1394
1395 EXCEPTION
1396 WHEN CST_CSTPPIPV_RUNNING THEN
1397 ROLLBACK;
1398 retcode := 1;
1399 l_err_num := 20009;
1400 l_err_code := SUBSTR('CSTPPIPV.trf_invoice_to_wip('
1401 || to_char(l_stmt_num)
1402 || '): '
1403 || 'Req_id: '
1404 || TO_CHAR(l_dummy)
1405 ||' '
1406 || l_err_msg
1407 || '. ',1,240);
1408
1409 fnd_message.set_name('BOM', 'CST_CSTPPIPV_RUNNING');
1410 l_err_msg := fnd_message.get;
1411 l_err_msg := SUBSTR(l_err_msg,1,240);
1412 FND_FILE.PUT_LINE(fnd_file.log,SUBSTR(l_err_code
1413 ||' '
1414 ||l_err_msg,1,240));
1415 CONC_STATUS := FND_CONCURRENT.
1416 SET_COMPLETION_STATUS('ERROR',l_err_msg);
1417
1418 WHEN CST_PROCESS_ERROR THEN
1419 ROLLBACK;
1420 retcode := 1;
1421 l_err_num := l_err_num;
1422 l_err_code := l_err_code;
1423 l_err_msg := SUBSTR(l_err_msg,1,240);
1424 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
1425 CONC_STATUS := FND_CONCURRENT.
1426 SET_COMPLETION_STATUS('ERROR',l_err_msg);
1427
1428 WHEN OTHERS THEN
1429 ROLLBACK;
1430 retcode := 1;
1431 l_err_num := SQLCODE;
1432 l_err_code := NULL;
1433 l_err_msg := SUBSTR('CSTPPIPV.trf_invoice_to_wip('
1434 || to_char(l_stmt_num)
1435 || '): '
1436 ||SQLERRM,1,240);
1437 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
1438 CONC_STATUS := FND_CONCURRENT.
1439 SET_COMPLETION_STATUS('ERROR',l_err_msg);
1440 END trf_invoice_to_wip;
1441
1442 PROCEDURE trf_invoice_to_wip(
1443 errbuf OUT NOCOPY VARCHAR2,
1444 retcode OUT NOCOPY NUMBER,
1445 p_organization_id IN NUMBER,
1446 p_description IN VARCHAR2 DEFAULT NULL,
1447 p_work_order_id IN NUMBER DEFAULT NULL,
1448 p_item_type IN NUMBER,
1449 p_item_type_dummy IN NUMBER DEFAULT NULL,
1450 p_item_option IN NUMBER DEFAULT NULL,
1451 p_item_dummy IN NUMBER DEFAULT NULL,
1452 p_category_dummy IN NUMBER DEFAULT NULL,
1453 p_specific_item_id IN NUMBER DEFAULT NULL,
1454 p_category_set_id IN NUMBER DEFAULT NULL,
1455 p_category_validate_flag IN VARCHAR2 DEFAULT NULL,
1456 p_category_structure IN NUMBER DEFAULT NULL,
1457 p_category_id IN NUMBER DEFAULT NULL,
1458 p_project_dummy IN NUMBER DEFAULT NULL,
1459 p_project_id IN NUMBER DEFAULT NULL,
1460 p_adj_account_dummy IN NUMBER,
1461 p_adj_account IN NUMBER,
1462 p_cutoff_date IN VARCHAR2,
1463 p_transaction_process_mode IN NUMBER
1464 )
1465 IS
1466 l_stmt_num NUMBER;
1467 l_err_num NUMBER;
1468 l_err_code VARCHAR2(240);
1469 l_err_msg VARCHAR2(240);
1470 l_batch_id NUMBER;
1471 l_request_id NUMBER;
1472 l_user_id NUMBER;
1473 l_prog_id NUMBER;
1474 l_prog_appl_id NUMBER;
1475 l_login_id NUMBER;
1476 l_conc_program_id NUMBER;
1477 conc_status BOOLEAN;
1478
1479 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 Start */
1480
1481 l_process_enabled_flag mtl_parameters.process_enabled_flag%TYPE;
1482 l_organization_code mtl_parameters.organization_code%TYPE;
1483
1484 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 End */
1485
1486 BEGIN
1487
1488 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 Start */
1489 BEGIN
1490 SELECT nvl(process_enabled_flag,'N'), organization_code
1491 INTO l_process_enabled_flag, l_organization_code
1492 FROM mtl_parameters
1493 WHERE organization_id = p_organization_id;
1494
1495 IF nvl(l_process_enabled_flag,'N') = 'Y' THEN
1496 l_err_num := 30001;
1497 fnd_message.set_name('GMF', 'GMF_PROCESS_ORG_ERROR');
1498 fnd_message.set_token('ORGCODE', l_organization_code);
1499 l_err_msg := FND_MESSAGE.Get;
1500 l_err_msg := substrb('CSTPIPVB : ' || l_err_msg,1,240);
1501 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
1502 fnd_file.put_line(fnd_file.log,l_err_msg);
1503 RETURN;
1504 END IF;
1505
1506 EXCEPTION
1507 WHEN no_data_found THEN
1508 l_process_enabled_flag := 'N';
1509 l_organization_code := NULL;
1510 END;
1511 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 End */
1512
1513 l_stmt_num := 7;
1514 l_request_id := FND_GLOBAL.conc_request_id;
1515 l_user_id := FND_GLOBAL.user_id;
1516 l_prog_id := FND_GLOBAL.conc_program_id;
1517 l_prog_appl_id := FND_GLOBAL.prog_appl_id;
1518 l_login_id := FND_GLOBAL.conc_login_id;
1519 l_conc_program_id := FND_GLOBAL.conc_program_id;
1520 l_stmt_num := 14;
1521 l_batch_id := trf_invoice_to_wip(
1522 errbuf => errbuf,
1523 retcode => retcode,
1524 p_organization_id => p_organization_id,
1525 p_description => p_description,
1526 p_work_order_id => p_work_order_id,
1527 p_item_type => p_item_type,
1528 p_item_option => p_item_option,
1529 p_specific_item_id => p_specific_item_id,
1530 p_category_set_id => p_category_set_id,
1531 p_category_id => p_category_id,
1532 p_project_id => p_project_id,
1533 p_adj_account => p_adj_account,
1534 p_cutoff_date => p_cutoff_date,
1535 p_transaction_process_mode => p_transaction_process_mode,
1536 p_request_id => l_user_id,
1537 p_user_id => l_user_id,
1538 p_login_id => l_login_id,
1539 p_prog_appl_id => l_prog_appl_id,
1540 p_prog_id => l_prog_id
1541 );
1542 EXCEPTION
1543 WHEN OTHERS THEN
1544 ROLLBACK;
1545 retcode := 1;
1546 l_err_num := SQLCODE;
1547 l_err_code := NULL;
1548 l_err_msg := SUBSTR('CSTPPIPV.trf_invoice_to_wip('
1549 || to_char(l_stmt_num)
1550 || '): '
1551 ||SQLERRM,1,240);
1552 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
1553 CONC_STATUS := FND_CONCURRENT.
1554 SET_COMPLETION_STATUS('ERROR',l_err_msg);
1555 END trf_invoice_to_wip;
1556
1557 /*---------------------------------------------------------------------------*
1558 | PUBLIC PROCEDURE |
1559 | generate_trf_info |
1560 | |
1561 *----------------------------------------------------------------------------*/
1562
1563 PROCEDURE generate_trf_info (
1564 p_organization_id IN NUMBER,
1565 p_inventory_item_id IN NUMBER,
1566 p_invoice_project_option IN NUMBER,
1567 p_project_id IN NUMBER,
1568 p_cost_group_id IN NUMBER,
1569 p_cutoff_date IN DATE,
1570 p_user_id IN NUMBER,
1571 p_login_id IN NUMBER,
1572 p_request_id IN NUMBER,
1573 p_prog_id IN NUMBER,
1574 p_prog_app_id IN NUMBER,
1575 p_batch_id IN NUMBER,
1576 p_default_txn_date IN DATE,
1577 x_err_num OUT NOCOPY NUMBER,
1578 x_err_code OUT NOCOPY VARCHAR2,
1579 x_err_msg OUT NOCOPY VARCHAR2
1580 )
1581 IS
1582
1583 l_txn_date DATE;
1584 l_header_id NUMBER;
1585 l_po_dist_id NUMBER;
1586 l_stmt_num NUMBER;
1587 l_err_num NUMBER;
1588 l_err_code VARCHAR2(240);
1589 l_err_msg VARCHAR2(240);
1590 l_default_cost_group_id number;
1591 conc_status BOOLEAN;
1592 cst_process_error EXCEPTION;
1593
1594 -------------------------------------------------------------------------------
1595 -- dist_match_type po_distribution_id rcv_transaction_id
1596 -- 'ITEM_TO_PO' X NULL
1597 -- 'ITEM_TO_RECEIPT' X X
1598 -- 'OTHER_TO_RECEIPT' NULL X
1599 -------------------------------------------------------------------------------
1600
1601
1602 CURSOR c_cavl_po ( p_batch_id NUMBER,p_cost_group_id NUMBER,p_inventory_item_id NUMBER)
1603 IS
1604
1605 SELECT cavl.po_distribution_id,
1606 SUM(NVL(cavl.var_amount,0)) var_amount
1607 FROM cst_ap_variance_lines cavl
1608 WHERE cavl.batch_id = p_batch_id
1609 AND cavl.cost_group_id = p_cost_group_id
1610 AND cavl.inventory_item_id = p_inventory_item_id
1611 GROUP BY cavl.po_distribution_id;
1612
1613 BEGIN
1614
1615 ---------------------------------------------------------------------
1616 -- Initializing Variables
1617 ---------------------------------------------------------------------
1618 l_err_num := 0;
1619 l_err_code := '';
1620 l_err_msg := '';
1621
1622 select default_cost_group_id
1623 into l_default_cost_group_id
1624 from mtl_parameters
1625 where organization_id = p_organization_id;
1626 ---------------------------------------------------------------------
1627 -- Get all relevant records
1628 ---------------------------------------------------------------------
1629 ---------------------------------------------------------------------
1630 -- Invoice Lines Project:
1631 -- No {base_}invoice_price_variance columns in ap_invoice_distributions_all
1632 -- IPV is a separate distribution, therefore the query needs to have a
1633 -- self-join on the table to get the IPV information in addition to the
1634 -- ITEM/ACCRUAL information
1635 -- Added a filter of accounting date and posted flag on aida and join of
1636 -- invoice_id between aida and aida2 for performance improvement - bug4137765
1637 ---------------------------------------------------------------------
1638
1639 /* bug 4873742 -- performance repository issues */
1640
1641 l_stmt_num := 5;
1642
1643 INSERT INTO cst_ap_variance_lines
1644 (
1645 variance_header_id,
1646 variance_line_id,
1647 batch_id,
1648 invoice_distribution_id,
1649 invoice_id,
1650 distribution_line_number,
1651 po_distribution_id,
1652 invoice_price_variance,
1653 base_invoice_price_variance,
1654 var_amount,
1655 project_id,
1656 organization_id,
1657 inventory_item_id,
1658 creation_date,
1659 last_update_date,
1660 last_updated_by,
1661 created_by,
1662 request_id,
1663 program_application_id,
1664 program_id,
1665 program_update_date,
1666 last_update_login,
1667 cost_group_id
1668 )
1669 (
1670 SELECT
1671 -1 variance_header_id,
1672 cst_ap_variance_lines_s.nextval variance_line_id,
1673 p_batch_id batch_id,
1674 aida.invoice_distribution_id
1675 invoice_distribution_id,
1676 aida.invoice_id invoice_id,
1677 aida.distribution_line_number
1678 distribution_line_number,
1679 aida.po_distribution_id po_distribution_id,
1680 aida2.amount invoice_price_variance,
1681 aida2.base_amount base_invoice_price_variance,
1682 NVL(aida2.base_amount,0) var_amount,
1683 pda.project_id project_id,
1684 p_organization_id organization_id,
1685 p_inventory_item_id inventory_item_id,
1686 SYSDATE creation_date,
1687 SYSDATE last_updated_date,
1688 p_user_id last_updated_by,
1689 p_user_id created_by,
1690 p_request_id,
1691 p_prog_app_id,
1692 p_prog_id,
1693 SYSDATE,
1694 p_login_id,
1695 p_cost_group_id
1696 FROM ap_invoice_distributions_all aida,
1697 ap_invoice_distributions_all aida2,
1698 po_distributions pda
1699 WHERE aida.posted_flag = 'Y'
1700 AND aida.accounting_date <= p_cutoff_date
1701 AND aida2.posted_flag = 'Y'
1702 AND aida2.accounting_date <= p_cutoff_date
1703 AND aida.inventory_transfer_status = 'N'
1704 /* Start of bug 8270017 */
1705 AND (
1706 (
1707 aida2.line_type_lookup_code IN ('IPV')
1708 and
1709 (
1710 (aida.invoice_id = aida2.invoice_id and aida.invoice_distribution_id = aida2.related_id)
1711 or
1712 (aida.invoice_distribution_id = aida2.corrected_invoice_dist_id)
1713 )
1714 )
1715 OR /*Start of bug 8681379 */
1716 (
1717 aida2.line_type_lookup_code IN ('TIPV','TERV','TRV')
1718 and aida.invoice_id = aida2.invoice_id
1719 and aida.invoice_distribution_id = aida2.charge_applicable_to_dist_id
1720
1721 )
1722 /*End of bug 8681379 */
1723 )
1724 /* End of bug 8270017 */
1725 /*Added NONREC_TAX and other tax component 'TIPV','TERV','TRV' for bug 8681379 */
1726 AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL','NONREC_TAX')
1727 AND aida2.line_type_lookup_code IN ('IPV','TIPV','TERV','TRV')
1728 AND pda.po_distribution_id = aida.po_distribution_id
1729 AND (
1730 ( p_invoice_project_option = 1
1731 AND pda.project_id IS NULL
1732 AND p_cost_group_id = l_default_cost_group_id)
1733 OR
1734 ( pda.project_id IS NOT NULL
1735 AND EXISTS
1736 (SELECT 'X'
1737 FROM pjm_project_parameters ppp
1738 WHERE ppp.organization_id = p_organization_id
1739 AND ppp.costing_group_id = p_cost_group_id
1740 AND ppp.project_id = pda.project_id
1741 AND ppp.project_id =
1742 decode(p_invoice_project_option,
1743 1, ppp.project_id,
1744 p_project_id)
1745 )
1746 )
1747 )
1748 AND aida.po_distribution_id IS NOT NULL
1749 -- bug3673238 -------------------------------------------------------
1750 AND pda.destination_organization_id = p_organization_id
1751 AND pda.destination_type_code = 'INVENTORY'
1752 /* changes for performance improvement bug4873742 */
1753 AND EXISTS (
1754 SELECT 'X'
1755 FROM po_line_locations_all plla,
1756 po_lines_all pla
1757 WHERE pla.po_line_id = plla.po_line_id
1758 AND pla.item_id = p_inventory_item_id
1759 AND nvl(plla.lcm_flag,'N') = 'N'
1760 AND pda.line_location_id = plla.line_location_id)
1761 -- end bug3673238 ---------------------------------------------------
1762 -- J Changes ----------------------------------------------------------------
1763 -- AND aida.root_distribution_id IS NULL
1764 ------------------------------------------------------------------------------
1765 AND aida.corrected_invoice_dist_id IS NULL --same as change made earlier
1766 );
1767
1768
1769 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)
1770 || ' Rows inserted into CAVL');
1771
1772 l_stmt_num := 10;
1773
1774 ---------------------------------------------------------------------
1775 -- Create header with net ipv amount
1776 -- Even if var_amount is zero still create a record.
1777 ---------------------------------------------------------------------
1778
1779 FOR c_cavl_po_rec IN c_cavl_po(p_batch_id,p_cost_group_id,
1780 p_inventory_item_id) LOOP
1781
1782 l_stmt_num := 15;
1783
1784 -- Get transaction date for header records
1785
1786 CSTPPIPV.get_upd_txn_date
1787 (
1788 p_po_distribution_id => c_cavl_po_rec.po_distribution_id,
1789 p_default_txn_date => p_default_txn_date,
1790 p_organization_id => p_organization_id, --BUG#5709567 - FPBUG#5109100 --Bug #13075737, Release commented p_organization_id
1791 x_transaction_date => l_txn_date,
1792 x_err_num => l_err_num,
1793 x_err_code => l_err_code,
1794 x_err_msg => l_err_msg
1795 );
1796
1797 IF (l_err_num <> 0) THEN
1798 RAISE cst_process_error;
1799 END IF;
1800
1801 l_stmt_num := 20;
1802
1803 INSERT INTO cst_ap_variance_headers
1804 (
1805 variance_header_id,
1806 po_distribution_id,
1807 var_amount,
1808 organization_id,
1809 inventory_item_id,
1810 cost_group_id,
1811 transaction_date,
1812 batch_id,
1813 creation_date,
1814 created_by,
1815 last_update_date,
1816 last_updated_by,
1817 request_id,
1818 program_application_id,
1819 program_id,
1820 program_update_date,
1821 last_update_login
1822 )
1823 (
1824 SELECT cst_ap_variance_headers_s.nextval, -- header_id
1825 c_cavl_po_rec.po_distribution_id, -- po_dist
1826 c_cavl_po_rec.var_amount, -- var_amount
1827 p_organization_id,
1828 p_inventory_item_id,
1829 p_cost_group_id,
1830 l_txn_date, -- txn_date
1831 p_batch_id,
1832 SYSDATE,
1833 -1,
1834 SYSDATE,
1835 -1,
1836 p_request_id,
1837 p_prog_app_id,
1838 p_prog_id,
1839 SYSDATE,
1840 p_login_id
1841 FROM DUAL
1842 );
1843
1844 -- Debug statements -----------------------------------------------
1845 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'cavh.podist: '
1846 -- ||to_char(c_cavl_po_rec.po_distribution_id));
1847 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'cavh.batch_id: '
1848 -- ||to_char(p_batch_id));
1849 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'cavh.var_amt: '
1850 -- ||to_char(c_cavl_po_rec.var_amount));
1851 -- End Debug ------------------------------------------------------
1852
1853 END LOOP; -- c_cavl_po_rec
1854
1855 ---------------------------------------------------------------------
1856 -- Update the ipv header id of all the detail lines
1857 ---------------------------------------------------------------------
1858
1859 l_stmt_num := 30;
1860
1861 UPDATE cst_ap_variance_lines cavl
1862 SET cavl.variance_header_id =
1863 (SELECT cavh.variance_header_id
1864 FROM cst_ap_variance_headers cavh
1865 WHERE cavh.batch_id = cavl.batch_id
1866 AND cavh.po_distribution_id = cavl.po_distribution_id
1867 AND cavh.cost_group_id = cavl.cost_group_id
1868 )
1869 WHERE cavl.batch_id = p_batch_id
1870 AND EXISTS
1871 ( SELECT 'X'
1872 FROM cst_ap_variance_headers cavh2
1873 WHERE cavh2.batch_id = p_batch_id
1874 AND cavh2.po_distribution_id = cavl.po_distribution_id
1875 AND cavh2.cost_group_id = cavl.cost_group_id
1876 );
1877
1878 ---------------------------------------------------------------------
1879 -- Set AIDA.inventory_transfer_code = NULL i.e. transferred
1880 ---------------------------------------------------------------------
1881
1882 l_stmt_num := 35;
1883
1884 UPDATE ap_invoice_distributions_all aida
1885 SET aida.inventory_transfer_status = NULL
1886 WHERE aida.inventory_transfer_status = 'N' --Perf Bug 1866130
1887 -- Line below is not needed because cst_ap_variance_lines won't have null IPV
1888 --AND NVL(aida.base_invoice_price_variance,0) <> 0
1889 AND EXISTS
1890 ( SELECT 'X'
1891 FROM cst_ap_variance_lines cavl
1892 WHERE cavl.batch_id = p_batch_id
1893 AND cavl.invoice_distribution_id =
1894 aida.invoice_distribution_id
1895 AND cavl.cost_group_id = p_cost_group_id
1896 AND cavl.inventory_item_id = p_inventory_item_id
1897 );
1898
1899 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)
1900 || ' Rows marked as transferred in AIDA');
1901
1902
1903 EXCEPTION
1904
1905 WHEN CST_PROCESS_ERROR THEN
1906 x_err_num := l_err_num;
1907 x_err_code := l_err_code;
1908 x_err_msg := SUBSTR(l_err_msg,1,240);
1909
1910 WHEN OTHERS THEN
1911 ROLLBACK;
1912 x_err_num := SQLCODE;
1913 x_err_code := NULL;
1914 x_err_msg := SUBSTR('CSTPPIPV.generate_trf_info('
1915 || to_char(l_stmt_num)
1916 || '): '
1917 ||SQLERRM,1,240);
1918 END generate_trf_info;
1919
1920
1921 /*---------------------------------------------------------------------------*
1922 | PUBLIC PROCEDURE |
1923 | generate_wip_info |
1924 | This procedure generates the invoice price variances information |
1925 | for the specified po distribution and cutoff date. |
1926 | |
1927 *----------------------------------------------------------------------------*/
1928
1929 PROCEDURE generate_wip_info (
1930 p_organization_id IN NUMBER,
1931 p_inventory_item_id IN NUMBER,
1932 p_project_id IN NUMBER,
1933 p_po_distribution_id IN NUMBER,
1934 p_cutoff_date IN DATE,
1935 p_user_id IN NUMBER,
1936 p_login_id IN NUMBER,
1937 p_request_id IN NUMBER,
1938 p_prog_id IN NUMBER,
1939 p_prog_app_id IN NUMBER,
1940 p_batch_id IN NUMBER,
1941 p_transaction_process_mode IN NUMBER,
1942 p_default_txn_date IN DATE,
1943 x_err_num OUT NOCOPY NUMBER,
1944 x_err_code OUT NOCOPY VARCHAR2,
1945 x_err_msg OUT NOCOPY VARCHAR2
1946 )
1947 IS
1948
1949 l_txn_date DATE;
1950 l_header_id NUMBER;
1951 l_var_amount NUMBER;
1952 l_stmt_num NUMBER;
1953 l_err_num NUMBER;
1954 l_err_code VARCHAR2(240);
1955 l_err_msg VARCHAR2(240);
1956 conc_status BOOLEAN;
1957 cst_process_error EXCEPTION;
1958
1959 BEGIN
1960
1961 ---------------------------------------------------------------------
1962 -- Initializing Variables
1963 ---------------------------------------------------------------------
1964
1965 l_err_num := 0;
1966 l_err_code := '';
1967 l_err_msg := '';
1968
1969 ---------------------------------------------------------------------
1970 -- Get all relevant records
1971 -- Added a filter of accounting date and posted flag on aida and join
1972 -- of invoice_id between aida and aida2 for performance
1973 -- improvement - bug4137765
1974 ---------------------------------------------------------------------
1975
1976 l_stmt_num := 5;
1977
1978 INSERT INTO
1979 cst_ap_variance_lines (
1980 variance_header_id,
1981 variance_line_id,
1982 batch_id,
1983 invoice_distribution_id,
1984 invoice_id,
1985 distribution_line_number,
1986 po_distribution_id,
1987 invoice_price_variance,
1988 base_invoice_price_variance,
1989 var_amount,
1990 project_id,
1991 organization_id,
1992 inventory_item_id,
1993 creation_date,
1994 last_update_date,
1995 last_updated_by,
1996 created_by,
1997 request_id,
1998 program_application_id,
1999 program_id,
2000 program_update_date,
2001 last_update_login,
2002 cost_group_id)
2003 (
2004 SELECT -1,
2005 cst_ap_variance_lines_s.nextval,
2006 p_batch_id,
2007 aida.invoice_distribution_id,
2008 aida.invoice_id,
2009 aida.distribution_line_number,
2010 aida.po_distribution_id,
2011 aida2.amount,
2012 aida2.base_amount,
2013 NVL(aida2.base_amount,0),
2014 p_project_id,
2015 p_organization_id,
2016 nvl(p_inventory_item_id,-1),
2017 SYSDATE,
2018 SYSDATE,
2019 p_user_id,
2020 p_user_id,
2021 p_request_id,
2022 p_prog_app_id,
2023 p_prog_id,
2024 SYSDATE,
2025 p_login_id,
2026 NULL
2027 FROM ap_invoice_distributions_all aida,
2028 ap_invoice_distributions_all aida2
2029 WHERE aida.posted_flag = 'Y'
2030 AND aida.accounting_date < p_cutoff_date
2031 AND aida2.posted_flag = 'Y'
2032 AND aida2.accounting_date < p_cutoff_date
2033 AND aida.inventory_transfer_status = 'N'
2034 /* Start of bug 8270017 */
2035 AND
2036 (
2037 (
2038 aida2.line_type_lookup_code IN ('IPV')
2039 and
2040 (
2041 (aida.invoice_id = aida2.invoice_id and aida.invoice_distribution_id = aida2.related_id)
2042 or
2043 (aida.invoice_distribution_id = aida2.corrected_invoice_dist_id)
2044 )
2045 )
2046 OR /* Start of bug 8681379 */
2047 (
2048 aida2.line_type_lookup_code IN ('TIPV','TERV','TRV')
2049 and aida.invoice_id = aida2.invoice_id
2050 and aida.invoice_distribution_id = aida2.charge_applicable_to_dist_id
2051 )
2052 /* End of bug 8681379 */
2053 )
2054 /* End of bug 8270017 */
2055 /*Added NONREC_TAX and other tax component 'TIPV','TERV','TRV' for bug 8681379 */
2056 AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL','NONREC_TAX')
2057 AND aida2.line_type_lookup_code IN ('IPV','TIPV','TERV','TRV')
2058 /* Ensure that Price Correction Invoices are not picked up */
2059 -- AND aida.root_distribution_id IS NULL
2060 AND aida.corrected_invoice_dist_id IS NULL
2061 AND aida.po_distribution_id = p_po_distribution_id);
2062
2063 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)|| ' Rows inserted into CAVL');
2064
2065 l_stmt_num := 10;
2066
2067 ---------------------------------------------------------------------
2068 -- Create header with net ipv amount
2069 -- Even if var_amount is zero still create a record.
2070 ---------------------------------------------------------------------
2071
2072 l_stmt_num := 15;
2073
2074 -- Get transaction date for header records
2075 CSTPPIPV.get_upd_txn_date(
2076 p_po_distribution_id => p_po_distribution_id,
2077 p_default_txn_date => p_default_txn_date,
2078 p_organization_id => p_organization_id, --BUG#5709567-FPBUG5109100 --Bug #13075737, Release commented p_organization_id
2079 x_transaction_date => l_txn_date,
2080 x_err_num => l_err_num,
2081 x_err_code => l_err_code,
2082 x_err_msg => l_err_msg
2083 );
2084
2085 IF (l_err_num <> 0) THEN
2086 RAISE cst_process_error;
2087 END IF;
2088
2089 l_stmt_num := 20;
2090
2091 SELECT SUM(NVL(cavl.var_amount,0))
2092 INTO l_var_amount
2093 FROM cst_ap_variance_lines cavl
2094 WHERE cavl.batch_id = p_batch_id
2095 AND cavl.po_distribution_id = p_po_distribution_id;
2096
2097 SELECT cst_ap_variance_headers_s.nextval
2098 INTO l_header_id
2099 FROM dual;
2100
2101
2102 l_stmt_num := 25;
2103
2104 INSERT INTO
2105 cst_ap_variance_headers(
2106 variance_header_id,
2107 po_distribution_id,
2108 var_amount,
2109 organization_id,
2110 inventory_item_id,
2111 cost_group_id,
2112 transaction_date,
2113 batch_id,
2114 creation_date,
2115 created_by,
2116 last_update_date,
2117 last_updated_by,
2118 request_id,
2119 program_application_id,
2120 program_id,
2121 program_update_date,
2122 last_update_login)
2123 VALUES ( l_header_id,
2124 p_po_distribution_id,
2125 l_var_amount,
2126 p_organization_id,
2127 p_inventory_item_id,
2128 NULL,
2129 l_txn_date,
2130 p_batch_id,
2131 SYSDATE,
2132 p_user_id,
2133 SYSDATE,
2134 p_user_id,
2135 p_request_id,
2136 p_prog_app_id,
2137 p_prog_id,
2138 SYSDATE,
2139 p_login_id);
2140
2141 ---------------------------------------------------------------------
2142 -- Update the ipv header id of all the detail lines
2143 ---------------------------------------------------------------------
2144
2145 l_stmt_num := 30;
2146
2147 UPDATE cst_ap_variance_lines cavl
2148 SET cavl.variance_header_id = l_header_id
2149 WHERE cavl.batch_id = p_batch_id
2150 AND cavl.po_distribution_id = p_po_distribution_id;
2151
2152 ---------------------------------------------------------------------
2153 -- Set AIDA.inventory_transfer_code = NULL i.e. transferred
2154 ---------------------------------------------------------------------
2155
2156 l_stmt_num := 35;
2157
2158 IF (p_transaction_process_mode = 1) THEN
2159 UPDATE ap_invoice_distributions_all aida
2160 SET aida.inventory_transfer_status = NULL
2161 WHERE aida.inventory_transfer_status = 'N' --Perf Bug 1866130
2162 -- AND NVL(aida.base_invoice_price_variance,0) <> 0 Not needed
2163 AND aida.posted_flag = 'Y'
2164 AND EXISTS
2165 ( SELECT 'X'
2166 FROM cst_ap_variance_lines cavl
2167 WHERE cavl.batch_id = p_batch_id
2168 AND cavl.invoice_distribution_id = aida.invoice_distribution_id
2169 );
2170
2171 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)
2172 || ' Rows marked as transferred in AIDA');
2173 END IF;
2174
2175 EXCEPTION
2176
2177 WHEN CST_PROCESS_ERROR THEN
2178 x_err_num := l_err_num;
2179 x_err_code := l_err_code;
2180 x_err_msg := SUBSTR(l_err_msg,1,240);
2181
2182 WHEN OTHERS THEN
2183 ROLLBACK;
2184 x_err_num := SQLCODE;
2185 x_err_code := NULL;
2186 x_err_msg := SUBSTR('CSTPPIPV.generate_wip_info('
2187 || to_char(l_stmt_num)
2188 || '): '
2189 ||SQLERRM,1,240);
2190 END generate_wip_info;
2191
2192 ------------------------------------------------------------------------
2193 -- Procedure to determine the update transaction date.
2194 ------------------------------------------------------------------------
2195 PROCEDURE get_upd_txn_date (
2196 p_po_distribution_id IN NUMBER,
2197 p_default_txn_date IN DATE,
2198 p_organization_id IN NUMBER, --BUG#5709567-FPBUG#5109100 --Bug #13075737, Release commented p_organization_id
2199 x_transaction_date OUT NOCOPY DATE,
2200 x_err_num OUT NOCOPY NUMBER,
2201 x_err_code OUT NOCOPY VARCHAR2,
2202 x_err_msg OUT NOCOPY VARCHAR2
2203 )
2204 IS
2205 l_transaction_date DATE;
2206 l_first_date DATE; --BUG#5709567-FPBUG5109100 --Bug #13075737, Release commented l_first_date and l_last_date
2207 l_last_date DATE;
2208 l_legal_entity NUMBER;
2209 l_stmt_num NUMBER;
2210
2211 BEGIN
2212 l_stmt_num := 5;
2213
2214 -- Get the most recent delivery date for the po distribution
2215 -- For Bug 2292853, use job release date (+1s) if deliver date
2216 -- equals job release date
2217
2218 SELECT MAX(
2219 decode(
2220 trunc(rt.transaction_date),
2221 trunc(wdj.date_released),
2222 wdj.date_released+0.00001,
2223 rt.transaction_date))
2224 INTO l_transaction_date
2225 FROM rcv_transactions rt,
2226 wip_discrete_jobs wdj
2227 WHERE rt.wip_entity_id = wdj.wip_entity_id (+)
2228 AND rt.transaction_type = 'DELIVER'
2229 AND rt.po_distribution_id = p_po_distribution_id
2230 AND rt.transaction_date = (
2231 SELECT MAX(rt.transaction_date)
2232 FROM rcv_transactions rt
2233 WHERE rt.transaction_type = 'DELIVER'
2234 AND rt.po_distribution_id = p_po_distribution_id);
2235
2236 l_stmt_num := 10;
2237
2238 -- If there is no delivery, get the most recent receipt date
2239 -- Note: 'RECEIVE' rows may not have po_distribution_id.
2240 -- For Bug 2292853, use job release date (+1s) if deliver date
2241 -- equals job release date
2242
2243 IF l_transaction_date is NULL THEN
2244 SELECT MAX(
2245 decode(
2246 trunc(rt.transaction_date),
2247 trunc(wdj.date_released),
2248 wdj.date_released+0.00001,
2249 rt.transaction_date))
2250 INTO l_transaction_date
2251 FROM rcv_transactions rt,
2252 po_distributions_all pda,
2253 wip_discrete_jobs wdj
2254 WHERE pda.wip_entity_id = wdj.wip_entity_id (+)
2255 AND pda.po_distribution_id = p_po_distribution_id
2256 AND rt.transaction_type = 'RECEIVE'
2257 AND ( rt.po_distribution_id = p_po_distribution_id
2258 OR ( rt.po_line_location_id = pda.line_location_id
2259 ))
2260 AND rt.transaction_date = (
2261 SELECT MAX(rt.transaction_date)
2262 FROM rcv_transactions rt,
2263 po_distributions_all pda
2264 WHERE rt.transaction_type = 'RECEIVE'
2265 AND pda.po_distribution_id = p_po_distribution_id
2266 AND ( rt.po_distribution_id = p_po_distribution_id /* bug 4137765 - for performance improvement */
2267 OR ( rt.po_line_location_id = pda.line_location_id
2268 )));
2269 END IF;
2270
2271 -- If no receipt, use default date
2272
2273 l_stmt_num := 15;
2274
2275 IF l_transaction_date is NULL THEN
2276 l_transaction_date := p_default_txn_date;
2277 -- END IF;
2278
2279 --{BUG#5709567-FPBUG#5109100:Commented out this portion --Bug #13075737, Release commented code
2280 ELSE
2281
2282 -- Get first day of earliest open period and last day of the latest open period
2283
2284 /* combined l_stmt_num 20 and 25 to avoid two full table scans- bug 4873742 */
2285
2286 l_stmt_num := 20;
2287
2288 SELECT MIN(oap.period_start_date) ,
2289 MAX(oap.schedule_close_date)+.99999
2290 INTO l_first_date ,
2291 l_last_date
2292 FROM org_acct_periods oap
2293 WHERE oap.organization_id = p_organization_id
2294 AND oap.open_flag = 'Y'
2295 AND oap.period_close_date is NULL;
2296
2297 -- Get legal entity for timezone conversion.
2298 l_stmt_num := 30;
2299
2300 /* select legal entity from HR_ORGANIZATION_INFORMATION instead of cst_organization_definitions
2301 for performance improvement */
2302
2303 SELECT org_information2
2304 INTO l_legal_entity
2305 FROM HR_ORGANIZATION_INFORMATION
2306 where ORG_INFORMATION_CONTEXT = 'Accounting Information'
2307 and organization_id = p_organization_id;
2308
2309 -- Need to convert start and end dates to server time.
2310 l_stmt_num := 35;
2311 l_first_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
2312 l_first_date,
2313 l_legal_entity);
2314
2315 l_stmt_num := 40;
2316 l_last_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
2317 l_last_date,
2318 l_legal_entity);
2319
2320 l_stmt_num := 45;
2321
2322 IF l_transaction_date < l_first_date THEN
2323 l_transaction_date := l_first_date;
2324 ELSIF l_transaction_date > l_last_date THEN
2325 l_transaction_date := l_last_date;
2326 END IF;
2327 END IF;
2328 --} Bug #13075737, Release commented code
2329 x_transaction_date := l_transaction_date;
2330
2331 EXCEPTION
2332
2333 WHEN OTHERS THEN
2334 ROLLBACK;
2335 x_err_num := SQLCODE;
2336 x_err_code := NULL;
2337 x_err_msg := SUBSTR('CSTPPIPV.get_upd_txn_date('
2338 || to_char(l_stmt_num)
2339 || '): '
2340 ||SQLERRM,1,240);
2341
2342
2343 END get_upd_txn_date;
2344
2345 -------------------------------------------------------------------------------
2346 -- Procedure to return the default transaction date for cost updates
2347 -- Determine the default transaction date of the cost update trans.
2348 -- If sysdate < first day of the earliest open period
2349 -- ==> use first day of the earliest open period
2350 -- If sysdate > last day of the latest open period
2351 -- ==> use last day of the latest open period
2352 -- Otherwise, use sysdate as default transaction date
2353 -------------------------------------------------------------------------------
2354 PROCEDURE get_default_date (
2355 p_organization_id IN NUMBER,
2356 x_default_date OUT NOCOPY DATE,
2357 x_err_num OUT NOCOPY NUMBER,
2358 x_err_code OUT NOCOPY VARCHAR2,
2359 x_err_msg OUT NOCOPY VARCHAR2
2360 )
2361 IS
2362 l_first_date DATE;
2363 l_last_date DATE;
2364 l_default_txn_date DATE;
2365 l_stmt_num NUMBER;
2366 l_legal_entity NUMBER;
2367
2368 BEGIN
2369
2370 -- Get first day of earliest open period and last day of the latest open period
2371
2372 /* combined l_stmt_num 5 and 10 to avoid two full table scans- bug 4873742 */
2373
2374 l_stmt_num := 5;
2375
2376 SELECT MIN(oap.period_start_date) ,
2377 MAX(oap.schedule_close_date)+(1-1/86400) -- +.99999 BUG#5709567-FPBIG#5109100
2378 --Bug #13075737, Release commented code and modify to +(1-1/86400)
2379 INTO l_first_date ,
2380 l_last_date
2381 FROM org_acct_periods oap
2382 WHERE oap.organization_id = p_organization_id
2383 AND oap.open_flag = 'Y'
2384 AND oap.period_close_date is NULL;
2385
2386 -- Get legal entity for timezone conversion.
2387 l_stmt_num := 15;
2388
2389 /* select legal entity from HR_ORGANIZATION_INFORMATION instead of cst_organization_definitions
2390 for performance improvement */
2391
2392 SELECT org_information2
2393 INTO l_legal_entity
2394 FROM HR_ORGANIZATION_INFORMATION
2395 where ORG_INFORMATION_CONTEXT = 'Accounting Information'
2396 and organization_id = p_organization_id;
2397
2398 -- Need to convert start and end dates to server time.
2399 l_stmt_num := 18;
2400 l_first_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
2401 l_first_date,
2402 l_legal_entity);
2403
2404 l_stmt_num := 20;
2405 l_last_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
2406 l_last_date,
2407 l_legal_entity);
2408
2409 l_stmt_num := 25;
2410
2411 IF SYSDATE < l_first_date THEN
2412 l_default_txn_date := l_first_date;
2413 ELSIF
2414 SYSDATE > l_last_date THEN
2415 l_default_txn_date := l_last_date;
2416 ELSE
2417 l_default_txn_date := SYSDATE;
2418 END IF;
2419
2420 x_default_date := l_default_txn_date;
2421
2422
2423 EXCEPTION
2424
2425 WHEN OTHERS THEN
2426 ROLLBACK;
2427 x_err_num := SQLCODE;
2428 x_err_code := NULL;
2429 x_err_msg := SUBSTR('CSTPPIPV.get_default_date('
2430 || to_char(l_stmt_num)
2431 || '): '
2432 ||SQLERRM,1,240);
2433
2434 END get_default_date;
2435
2436 END CSTPPIPV;