eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CN_SCA_TRX_PROC_PVT dependencies on CN_SCA_HEADERS_INTERFACE

Line 336: l_header_interface_id cn_sca_headers_interface.sca_headers_interface_id%TYPE;

332: query_cur rc;
333: i NUMBER;
334: l_header_rec cn_comm_lines_api%ROWTYPE;
335: l_lines_output_id cn_sca_lines_output.sca_lines_output_id%TYPE;
336: l_header_interface_id cn_sca_headers_interface.sca_headers_interface_id%TYPE;
337: l_comm_lines_api_id cn_comm_lines_api.comm_lines_api_id%TYPE;
338: l_source_id cn_sca_headers_interface.source_id%TYPE;
339: l_order_number cn_comm_lines_api.order_number%TYPE;
340: l_invoice_number cn_comm_lines_api.invoice_number%TYPE;

Line 338: l_source_id cn_sca_headers_interface.source_id%TYPE;

334: l_header_rec cn_comm_lines_api%ROWTYPE;
335: l_lines_output_id cn_sca_lines_output.sca_lines_output_id%TYPE;
336: l_header_interface_id cn_sca_headers_interface.sca_headers_interface_id%TYPE;
337: l_comm_lines_api_id cn_comm_lines_api.comm_lines_api_id%TYPE;
338: l_source_id cn_sca_headers_interface.source_id%TYPE;
339: l_order_number cn_comm_lines_api.order_number%TYPE;
340: l_invoice_number cn_comm_lines_api.invoice_number%TYPE;
341: l_id cn_sca_process_batches.start_id%TYPE;
342:

Line 367: 'select count(distinct(source_id)) from cn_sca_headers_interface ';

363: if (batch_type = 'SCA_ORD') then
364:
365: l_sql_stmt_count :=
366:
367: 'select count(distinct(source_id)) from cn_sca_headers_interface ';
368:
369: l_sql_stmt :=
370:
371: 'where trunc(processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||

Line 385: 'select count(distinct(source_id)) from cn_sca_headers_interface ';

381: if (batch_type = 'SCA_INV') then
382:
383: l_sql_stmt_count :=
384:
385: 'select count(distinct(source_id)) from cn_sca_headers_interface ';
386:
387: l_sql_stmt :=
388:
389: 'where trunc(processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||

Line 452: 'select count(1) from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI ';

448: if (batch_type = 'CSHI') then
449:
450: l_sql_stmt_count :=
451:
452: 'select count(1) from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI ';
453:
454: l_sql_stmt :=
455:
456: 'where trunc(CSHI.processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||

Line 470: 'select count(1) from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO ';

466: if (batch_type = 'CSLO') then
467:
468: l_sql_stmt_count :=
469:
470: 'select count(1) from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO ';
471:
472: l_sql_stmt :=
473:
474: 'where trunc(CSHI.processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||

Line 602: 'select distinct(source_id) from cn_sca_headers_interface ';

598:
599: if ((batch_type = 'SCA_ORD') or (batch_type = 'SCA_INV')) then
600:
601: l_sql_stmt_id :=
602: 'select distinct(source_id) from cn_sca_headers_interface ';
603:
604: l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
605:
606: l_sql_stmt_id :=

Line 648: l_sql_stmt_id := 'select CSLI.sca_lines_interface_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI ';

644: END IF;
645:
646: if (batch_type = 'CSHI') then
647:
648: l_sql_stmt_id := 'select CSLI.sca_lines_interface_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI ';
649: l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
650:
651: l_sql_stmt_id :=
652:

Line 663: l_sql_stmt_id := 'select CSLO.sca_lines_output_id from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO ';

659: end if;
660:
661: if (batch_type = 'CSLO') then
662:
663: l_sql_stmt_id := 'select CSLO.sca_lines_output_id from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO ';
664: l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
665:
666: l_sql_stmt_id :=
667:

Line 879: select CSLO.sca_lines_output_id from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO

875: conc_status BOOLEAN;
876:
877: CURSOR sca_lines_cur (start_id VARCHAR2, end_id VARCHAR2) IS
878:
879: select CSLO.sca_lines_output_id from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO
880: where CSLO.sca_headers_interface_id = CSHI.sca_headers_interface_id
881: and trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
882: and CSHI.process_status <> 'SCA_UNPROCESSED'
883: and CSHI.transaction_status = 'SCA_UNPROCESSED'

Line 1259: cn_sca_lines_output CSLO, cn_salesreps CS, cn_comm_lines_api CCLA, cn_sca_headers_interface CSHI

1255: CCLA.SPLIT_PCT,
1256: CCLA.SPLIT_status,
1257: ccla.org_id
1258: from
1259: cn_sca_lines_output CSLO, cn_salesreps CS, cn_comm_lines_api CCLA, cn_sca_headers_interface CSHI
1260: where CS.resource_id = CSLO.resource_id -- added org_id join, since one resource can belong to more than one org
1261: and cslo.org_id = cs.org_id
1262: and ccla.org_id = cslo.org_id
1263: and CCLA.comm_lines_api_id = CSLO.source_trx_id

Line 1291: select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI

1287: p_physical_batch_id NUMBER) IS
1288:
1289: CURSOR sca_lines_cur (start_id VARCHAR2, end_id VARCHAR2) IS
1290:
1291: select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
1292: where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
1293: and CSHI.process_status <> 'SCA_UNPROCESSED'
1294: and CSHI.transaction_status = 'SCA_UNPROCESSED'
1295: and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id

Line 1300: select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI

1296: and CSLI.sca_lines_interface_id between start_id and end_id;
1297:
1298: CURSOR sca_no_rule_lines_cur (start_id VARCHAR2, end_id VARCHAR2) IS
1299:
1300: select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
1301: where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
1302: and CSHI.process_status = 'NO RULE'
1303: and CSHI.transaction_status = 'SCA_UNPROCESSED'
1304: and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id

Line 1309: select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI

1305: and CSLI.sca_lines_interface_id between start_id and end_id;
1306:
1307: CURSOR sca_not_allocate_lines_cur (start_id VARCHAR2, end_id VARCHAR2) IS
1308:
1309: select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
1310: where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
1311: and CSHI.process_status = 'NOT ALLOCATED'
1312: and CSHI.transaction_status = 'SCA_UNPROCESSED'
1313: and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id

Line 2107: select CSHI.sca_headers_interface_id from cn_sca_headers_interface CSHI

2103:
2104:
2105: CURSOR sca_update_headers_cur (p_start_date DATE, p_end_date DATE) IS
2106:
2107: select CSHI.sca_headers_interface_id from cn_sca_headers_interface CSHI
2108: where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2109: and CSHI.process_status <> 'SCA_UNPROCESSED'
2110: and CSHI.transaction_status = 'SCA_UNPROCESSED'
2111: AND cshi.org_id = p_org_id;

Line 2113: TYPE sca_update_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;

2109: and CSHI.process_status <> 'SCA_UNPROCESSED'
2110: and CSHI.transaction_status = 'SCA_UNPROCESSED'
2111: AND cshi.org_id = p_org_id;
2112:
2113: TYPE sca_update_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
2114:
2115: sca_update_headers sca_update_headers_tbl;
2116:
2117: p_start_date DATE;

Line 2218: UPDATE cn_sca_headers_interface

2214: end if;
2215:
2216: FORALL j IN 1..sca_update_headers.COUNT
2217:
2218: UPDATE cn_sca_headers_interface
2219: SET transaction_status = 'SCA_POPULATED'
2220: WHERE sca_headers_interface_id = sca_update_headers(j);
2221:
2222: debugmsg('Results Transfer : End of Transfer');

Line 2640: from cn_sca_headers_interface CSHI

2636: and CCLA.invoice_number between l_start_id and l_end_id
2637: and exists
2638:
2639: (SELECT 1
2640: from cn_sca_headers_interface CSHI
2641: where CSHI.transaction_status = 'ADJUSTED'
2642: and CSHI.source_id = CCLA.invoice_number
2643: and CSHI.source_type = 'INV');
2644:

Line 2648: from cn_sca_headers_interface

2644:
2645: cursor sca_adjust_headers_inv_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2646:
2647: select sca_headers_interface_id
2648: from cn_sca_headers_interface
2649: where source_type = 'INV'
2650: and source_id in
2651: (select invoice_number
2652: from

Line 2670: from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI

2666: FROM cn_comm_lines_api CCLA_ORIG
2667: where CCLA_ORIG.adj_comm_lines_api_id in
2668:
2669: (SELECT CSLI.source_trx_id
2670: from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI
2671: where CSLI.sca_headers_interface_id = CSHI.sca_headers_interface_id
2672: and CSHI.source_id = SCA_SOURCE_ID.invoice_number
2673: and CSHI.source_type = 'INV')
2674:

Line 2688: from cn_sca_headers_interface

2684:
2685: CURSOR sca_rollback_headers_inv_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2686:
2687: select sca_headers_interface_id
2688: from cn_sca_headers_interface
2689: where ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
2690: and source_type = 'INV'
2691: and source_id in
2692:

Line 2707: from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI

2703:
2704: CURSOR sca_rollback_lines_inv_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2705:
2706: select CSLI.source_trx_id
2707: from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
2708: where CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
2709: and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
2710: and CSHI.source_type = 'INV'
2711: and CSHI.source_id in

Line 2740: from cn_sca_headers_interface CSHI

2736: and CCLA.order_number between l_start_id and l_end_id
2737: and exists
2738:
2739: (SELECT 1
2740: from cn_sca_headers_interface CSHI
2741: where CSHI.transaction_status = 'ADJUSTED'
2742: and CSHI.source_id = CCLA.order_number
2743: and CSHI.source_type = 'ORD');
2744:

Line 2748: from cn_sca_headers_interface

2744:
2745: cursor sca_adjust_headers_ord_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2746:
2747: select sca_headers_interface_id
2748: from cn_sca_headers_interface
2749: where source_type = 'ORD'
2750: and source_id in
2751: (select order_number
2752: from

Line 2771: from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI

2767: FROM cn_comm_lines_api CCLA_ORIG
2768: where CCLA_ORIG.adj_comm_lines_api_id in
2769:
2770: (SELECT CSLI.source_trx_id
2771: from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI
2772: where CSLI.sca_headers_interface_id = CSHI.sca_headers_interface_id
2773: and CSHI.source_id = SCA_SOURCE_ID.order_number
2774: and CSHI.source_type = 'ORD')
2775:

Line 2789: from cn_sca_headers_interface

2785:
2786: CURSOR sca_rollback_headers_ord_cur (start_id VARCHAR2, end_id VARCHAR2) IS
2787:
2788: select sca_headers_interface_id
2789: from cn_sca_headers_interface
2790: where ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
2791: and source_type = 'ORD'
2792: and source_id in
2793:

Line 2809: from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI

2805:
2806: CURSOR sca_rollback_lines_ord_cur (start_id VARCHAR2, end_id VARCHAR2) IS
2807:
2808: select CSLI.source_trx_id
2809: from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
2810: where CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
2811: and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
2812: and CSHI.source_type = 'ORD'
2813: and CSHI.source_id in

Line 2827: TYPE sca_adjust_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;

2823: and CCLA.invoice_number is null
2824: and CCLA.order_number is not null
2825: and CCLA.order_number between l_start_id and l_end_id);
2826:
2827: TYPE sca_adjust_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
2828: TYPE api_adjust_lines_tbl IS TABLE OF cn_comm_lines_api.comm_lines_api_id%TYPE;
2829: TYPE sca_rollback_lines_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
2830: TYPE sca_rollback_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
2831:

Line 2830: TYPE sca_rollback_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;

2826:
2827: TYPE sca_adjust_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
2828: TYPE api_adjust_lines_tbl IS TABLE OF cn_comm_lines_api.comm_lines_api_id%TYPE;
2829: TYPE sca_rollback_lines_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
2830: TYPE sca_rollback_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
2831:
2832: sca_adjust_headers sca_adjust_headers_tbl;
2833: api_adjust_lines api_adjust_lines_tbl;
2834: sca_rollback_lines sca_rollback_lines_tbl;

Line 2865: UPDATE cn_sca_headers_interface

2861: end if;
2862:
2863: FORALL j IN 1..sca_adjust_headers.COUNT
2864:
2865: UPDATE cn_sca_headers_interface
2866: SET transaction_status = 'ADJUSTED'
2867: WHERE sca_headers_interface_id = sca_adjust_headers(j);
2868:
2869: debugmsg('Check_comm_lines_api_adjusted : Update the adjust_status of transactions that are not eligible for SCA');

Line 3320: DELETE FROM cn_sca_headers_interface

3316: WHERE sca_headers_interface_id = sca_rollback_headers(j);
3317:
3318: FORALL j IN 1..sca_rollback_headers.COUNT
3319:
3320: DELETE FROM cn_sca_headers_interface
3321: WHERE sca_headers_interface_id = sca_rollback_headers(j);
3322:
3323: debugmsg('Check_comm_lines_api_adjusted : Removing records from SCA headers, lines and output table');
3324: debugmsg('Check_comm_lines_api_adjusted : End of checking if transactions are eligible for SCA');

Line 3350: from cn_sca_headers_interface

3346:
3347: cursor sca_headers_adjust_cur (l_trx_type VARCHAR2, l_start_id VARCHAR2, l_end_id VARCHAR2) IS
3348:
3349: select sca_headers_interface_id
3350: from cn_sca_headers_interface
3351: where source_type = l_trx_type
3352: and source_id in
3353: (select source_id
3354: from

Line 3356: from cn_sca_headers_interface

3352: and source_id in
3353: (select source_id
3354: from
3355: (select distinct(source_id) source_id
3356: from cn_sca_headers_interface
3357: where source_type = l_trx_type
3358: and trunc(processed_date) between trunc(p_start_date) and trunc(p_end_date)
3359: and ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
3360: and source_id between l_start_id and l_end_id) SCA_SOURCE_ID

Line 3368: from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI

3364: FROM cn_comm_lines_api CCLA_ORIG
3365: where CCLA_ORIG.adj_comm_lines_api_id in
3366:
3367: (SELECT CSLI.source_trx_id
3368: from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI
3369: where CSLI.sca_headers_interface_id = CSHI.sca_headers_interface_id
3370: and CSHI.source_id = SCA_SOURCE_ID.source_id
3371: and CSHI.source_type = l_trx_type)
3372:

Line 3387: from cn_sca_headers_interface

3383:
3384: cursor sca_headers_rollback_cur (l_trx_type VARCHAR2, l_start_id VARCHAR2, l_end_id VARCHAR2) IS
3385:
3386: select sca_headers_interface_id
3387: from cn_sca_headers_interface
3388: where source_type = l_trx_type
3389: and trunc(processed_date) between trunc(p_start_date) and trunc(p_end_date)
3390: and ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
3391: and source_id between l_start_id and l_end_id;

Line 3396: from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI

3392:
3393: cursor sca_lines_rollback_cur (l_trx_type VARCHAR2, l_start_id VARCHAR2, l_end_id VARCHAR2) IS
3394:
3395: select CSLI.source_trx_id
3396: from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
3397: where CSHI.source_type = l_trx_type
3398: and trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
3399: and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
3400: and CSHI.source_id between l_start_id and l_end_id

Line 3404: TYPE sca_headers_adjust_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;

3400: and CSHI.source_id between l_start_id and l_end_id
3401: and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id;
3402:
3403:
3404: TYPE sca_headers_adjust_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
3405: TYPE sca_headers_rollback_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
3406: TYPE sca_lines_rollback_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
3407:
3408: sca_headers_adjust sca_headers_adjust_tbl;

Line 3405: TYPE sca_headers_rollback_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;

3401: and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id;
3402:
3403:
3404: TYPE sca_headers_adjust_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
3405: TYPE sca_headers_rollback_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
3406: TYPE sca_lines_rollback_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
3407:
3408: sca_headers_adjust sca_headers_adjust_tbl;
3409: sca_headers_rollback sca_headers_rollback_tbl;

Line 3418: l_source_id cn_sca_headers_interface.source_id%TYPE;

3414:
3415: l_batch_type cn_sca_process_batches.type%TYPE;
3416: l_start_id cn_sca_process_batches.start_id%TYPE;
3417: l_end_id cn_sca_process_batches.end_id%TYPE;
3418: l_source_id cn_sca_headers_interface.source_id%TYPE;
3419:
3420: l_adjusted_by VARCHAR2(30);
3421: conc_status boolean;
3422:

Line 3454: UPDATE cn_sca_headers_interface

3450: debugmsg('Allocation Transfer : Rerun : Mark those headers that have been populated and adjusted');
3451:
3452: FORALL j IN 1..sca_headers_adjust.COUNT
3453:
3454: UPDATE cn_sca_headers_interface
3455: SET transaction_status = 'ADJUSTED'
3456: WHERE sca_headers_interface_id = sca_headers_adjust(j);
3457:
3458: OPEN sca_headers_rollback_cur (l_batch_type, l_start_id, l_end_id);

Line 3876: DELETE FROM cn_sca_headers_interface

3872: where sca_headers_interface_id = sca_headers_rollback(j);
3873:
3874: FORALL j IN 1..sca_headers_rollback.COUNT
3875:
3876: DELETE FROM cn_sca_headers_interface
3877: WHERE sca_headers_interface_id = sca_headers_rollback(j);
3878:
3879: debugmsg('Allocation Transfer : Rerun : End');
3880: