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