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 454: 'select count(1) from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI ';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Line 2109: select CSHI.sca_headers_interface_id from cn_sca_headers_interface CSHI

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

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

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

Line 2220: UPDATE cn_sca_headers_interface

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

Line 2642: from cn_sca_headers_interface CSHI

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

Line 2650: from cn_sca_headers_interface

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

Line 2672: from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI

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

Line 2690: from cn_sca_headers_interface

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

Line 2709: from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI

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

Line 2742: from cn_sca_headers_interface CSHI

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

Line 2750: from cn_sca_headers_interface

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

Line 2773: from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI

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

Line 2791: from cn_sca_headers_interface

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

Line 2811: from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI

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

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

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

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

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

Line 2867: UPDATE cn_sca_headers_interface

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

Line 3324: DELETE FROM cn_sca_headers_interface

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

Line 3354: from cn_sca_headers_interface

3350:
3351: cursor sca_headers_adjust_cur (l_trx_type VARCHAR2, l_start_id VARCHAR2, l_end_id VARCHAR2) IS
3352:
3353: select sca_headers_interface_id
3354: from cn_sca_headers_interface
3355: where source_type = l_trx_type
3356: and source_id in
3357: (select source_id
3358: from

Line 3360: from cn_sca_headers_interface

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

Line 3372: from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI

3368: FROM cn_comm_lines_api CCLA_ORIG
3369: where CCLA_ORIG.adj_comm_lines_api_id in
3370:
3371: (SELECT CSLI.source_trx_id
3372: from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI
3373: where CSLI.sca_headers_interface_id = CSHI.sca_headers_interface_id
3374: and CSHI.source_id = SCA_SOURCE_ID.source_id
3375: and CSHI.source_type = l_trx_type)
3376:

Line 3391: from cn_sca_headers_interface

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

Line 3400: from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI

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

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

3404: and CSHI.source_id between l_start_id and l_end_id
3405: and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id;
3406:
3407:
3408: TYPE sca_headers_adjust_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
3409: TYPE sca_headers_rollback_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
3410: TYPE sca_lines_rollback_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
3411:
3412: sca_headers_adjust sca_headers_adjust_tbl;

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

3405: and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id;
3406:
3407:
3408: TYPE sca_headers_adjust_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
3409: TYPE sca_headers_rollback_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
3410: TYPE sca_lines_rollback_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
3411:
3412: sca_headers_adjust sca_headers_adjust_tbl;
3413: sca_headers_rollback sca_headers_rollback_tbl;

Line 3422: l_source_id cn_sca_headers_interface.source_id%TYPE;

3418:
3419: l_batch_type cn_sca_process_batches.type%TYPE;
3420: l_start_id cn_sca_process_batches.start_id%TYPE;
3421: l_end_id cn_sca_process_batches.end_id%TYPE;
3422: l_source_id cn_sca_headers_interface.source_id%TYPE;
3423:
3424: l_adjusted_by VARCHAR2(30);
3425: conc_status boolean;
3426:

Line 3458: UPDATE cn_sca_headers_interface

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

Line 3882: DELETE FROM cn_sca_headers_interface

3878: where sca_headers_interface_id = sca_headers_rollback(j);
3879:
3880: FORALL j IN 1..sca_headers_rollback.COUNT
3881:
3882: DELETE FROM cn_sca_headers_interface
3883: WHERE sca_headers_interface_id = sca_headers_rollback(j);
3884:
3885: debugmsg('Allocation Transfer : Rerun : End');
3886: