[Home] [Help]
9:
10: -- Package Name
11: -- cn_sca_trx_proc_pvt
12: -- Purpose
13: -- Procedures TO populate transactions from CN_COMM_LINES_API into SCA interface tables and
14: -- transfer results back to CN_COMM_LINES_API from SCA output tables after credit allocation
15: -- History
16: -- 06/02/03 Mike Ting Created
17: -- Nov 17, 2005 vensrini add call to fnd_request.set_org_id to call_populate_resuts
10: -- Package Name
11: -- cn_sca_trx_proc_pvt
12: -- Purpose
13: -- Procedures TO populate transactions from CN_COMM_LINES_API into SCA interface tables and
14: -- transfer results back to CN_COMM_LINES_API from SCA output tables after credit allocation
15: -- History
16: -- 06/02/03 Mike Ting Created
17: -- Nov 17, 2005 vensrini add call to fnd_request.set_org_id to call_populate_resuts
18: -- before calling fnd_request.submit_request
330: TYPE divider_type IS TABLE OF NUMBER;
331:
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;
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;
341: l_id cn_sca_process_batches.start_id%TYPE;
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:
343: l_logical_batch_size NUMBER;
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:
343: l_logical_batch_size NUMBER;
344: l_worker_num NUMBER;
399: if (batch_type = 'ORD') then
400:
401: l_sql_stmt_count :=
402:
403: 'select count(distinct(order_number)) from cn_comm_lines_api ';
404:
405: l_sql_stmt :=
406:
407: 'where trunc(processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||
427: if (batch_type = 'INV') then
428:
429: l_sql_stmt_count :=
430:
431: 'select count(distinct(invoice_number)) from cn_comm_lines_api ';
432:
433: l_sql_stmt :=
434:
435: 'where trunc(processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||
614: end if;
615:
616: if (batch_type = 'ORD') then
617:
618: l_sql_stmt_id := 'select distinct(order_number) from cn_comm_lines_api ';
619: l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
620:
621: l_sql_stmt_id :=
622:
629: end if;
630:
631: if (batch_type = 'INV') then
632:
633: l_sql_stmt_id := 'select distinct(invoice_number) from cn_comm_lines_api ';
634: l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
635:
636: l_sql_stmt_id :=
637:
903: FETCH sca_lines_cur BULK COLLECT INTO sca_lines limit 1000;
904:
905: FORALL j IN 1..sca_lines.COUNT
906:
907: INSERT into CN_COMM_LINES_API
908: ( SALESREP_ID,
909: PROCESSED_DATE,
910: PROCESSED_PERIOD_ID,
911: TRANSACTION_AMOUNT,
1188: CCLA.ATTRIBUTE97,
1189: CCLA.ATTRIBUTE98,
1190: CCLA.ATTRIBUTE99,
1191: CCLA.ATTRIBUTE100,
1192: cn_comm_lines_api_s.NEXTVAL,
1193: CCLA.CONC_BATCH_ID,
1194: CCLA.PROCESS_BATCH_ID,
1195: NULL,
1196: CCLA.ROLLUP_DATE,
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
1352: debugmsg('Populate results back to API: Inserting Transactions into API for ''No Rules'', ''No Credit'' headers');
1353:
1354: FORALL j IN 1..sca_lines.COUNT
1355:
1356: UPDATE cn_comm_lines_api api
1357: SET load_status = 'OBSOLETE',
1358: adjust_status = 'FROZEN',
1359: adjust_date = sysdate,
1360: adjusted_by = l_adjusted_by,
1364:
1365:
1366: FORALL j IN 1..sca_not_allocate_lines.COUNT
1367:
1368: INSERT into CN_COMM_LINES_API
1369: ( SALESREP_ID,
1370: PROCESSED_DATE,
1371: PROCESSED_PERIOD_ID,
1372: TRANSACTION_AMOUNT,
1648: CCLA.ATTRIBUTE97,
1649: CCLA.ATTRIBUTE98,
1650: CCLA.ATTRIBUTE99,
1651: CCLA.ATTRIBUTE100,
1652: cn_comm_lines_api_s.NEXTVAL,
1653: CCLA.CONC_BATCH_ID,
1654: CCLA.PROCESS_BATCH_ID,
1655: NULL,
1656: CCLA.ROLLUP_DATE,
1715: CCLA.SPLIT_PCT,
1716: CCLA.SPLIT_status,
1717: ccla.org_id
1718: from
1719: cn_comm_lines_api CCLA
1720: where CCLA.comm_lines_api_id = sca_not_allocate_lines(j));
1721:
1722: FORALL j IN 1..sca_no_rule_lines.COUNT
1723:
1720: where CCLA.comm_lines_api_id = sca_not_allocate_lines(j));
1721:
1722: FORALL j IN 1..sca_no_rule_lines.COUNT
1723:
1724: INSERT into CN_COMM_LINES_API
1725: ( SALESREP_ID,
1726: PROCESSED_DATE,
1727: PROCESSED_PERIOD_ID,
1728: TRANSACTION_AMOUNT,
2005: CCLA.ATTRIBUTE97,
2006: CCLA.ATTRIBUTE98,
2007: CCLA.ATTRIBUTE99,
2008: CCLA.ATTRIBUTE100,
2009: cn_comm_lines_api_s.NEXTVAL,
2010: CCLA.CONC_BATCH_ID,
2011: CCLA.PROCESS_BATCH_ID,
2012: NULL,
2013: CCLA.ROLLUP_DATE,
2072: CCLA.SPLIT_PCT,
2073: CCLA.SPLIT_status,
2074: ccla.org_id
2075: from
2076: cn_comm_lines_api CCLA
2077: where CCLA.comm_lines_api_id = sca_no_rule_lines(j));
2078:
2079:
2080: debugmsg('Populate results back to API: Done with Negating Transactions ');
2623: conc_status boolean;
2624:
2625: cursor api_adjust_lines_inv_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2626:
2627: select CCLA.comm_lines_api_id from cn_comm_lines_api CCLA
2628: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2629: and CCLA.load_status = 'UNLOADED'
2630: and ((CCLA.adjust_status is null) or
2631: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2649: where source_type = 'INV'
2650: and source_id in
2651: (select invoice_number
2652: from
2653: (select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
2654: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2655: and CCLA.load_status = 'UNLOADED'
2656: and ((CCLA.adjust_status is null) or
2657: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2662: and CCLA.invoice_number between l_start_id and l_end_id) SCA_SOURCE_ID
2663:
2664: where exists
2665: (SELECT 1
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
2675: and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
2676: and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
2677: (CCLA_ORIG.load_status = 'LOADED' and exists
2678:
2679: (select 1 from cn_comm_lines_api CCLA
2680: where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
2681: and CCLA.adjust_status = 'REVERSAL'
2682: and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
2683: )))));
2689: where ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
2690: and source_type = 'INV'
2691: and source_id in
2692:
2693: (select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
2694: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2695: and CCLA.load_status = 'UNLOADED'
2696: and ((CCLA.adjust_status is null) or
2697: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2709: and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
2710: and CSHI.source_type = 'INV'
2711: and CSHI.source_id in
2712:
2713: (select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
2714: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2715: and CCLA.load_status = 'UNLOADED'
2716: and ((CCLA.adjust_status is null) or
2717: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2722: and CCLA.invoice_number between l_start_id and l_end_id);
2723:
2724: cursor api_adjust_lines_ord_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2725:
2726: select CCLA.comm_lines_api_id from cn_comm_lines_api CCLA
2727: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2728: and CCLA.load_status = 'UNLOADED'
2729: and ((CCLA.adjust_status is null) or
2730: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2749: where source_type = 'ORD'
2750: and source_id in
2751: (select order_number
2752: from
2753: (select distinct(order_number) order_number from cn_comm_lines_api CCLA
2754: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2755: and CCLA.load_status = 'UNLOADED'
2756: and ((CCLA.adjust_status is null) or
2757: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2763: and CCLA.order_number between l_start_id and l_end_id) SCA_SOURCE_ID
2764:
2765: where exists
2766: (SELECT 1
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
2776: and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
2777: and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
2778: (CCLA_ORIG.load_status = 'LOADED' and exists
2779:
2780: (select 1 from cn_comm_lines_api CCLA
2781: where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
2782: and CCLA.adjust_status = 'REVERSAL'
2783: and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
2784: )))));
2790: where ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
2791: and source_type = 'ORD'
2792: and source_id in
2793:
2794: (select distinct(order_number) order_number from cn_comm_lines_api CCLA
2795: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2796: and CCLA.load_status = 'UNLOADED'
2797: and ((CCLA.adjust_status is null) or
2798: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2811: and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
2812: and CSHI.source_type = 'ORD'
2813: and CSHI.source_id in
2814:
2815: (select distinct(order_number) order_number from cn_comm_lines_api CCLA
2816: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2817: and CCLA.load_status = 'UNLOADED'
2818: and ((CCLA.adjust_status is null) or
2819: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
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:
2832: sca_adjust_headers sca_adjust_headers_tbl;
2895: end if;
2896:
2897: FORALL j IN 1..api_adjust_lines.COUNT
2898:
2899: UPDATE cn_comm_lines_api
2900: SET adjust_status = 'SCA_NOT_ELIGIBLE',
2901: adjust_date = sysdate,
2902: adjusted_by = l_adjusted_by,
2903: adjust_comments = 'SCA Check'
2908: debugmsg('Check_comm_lines_api_adjusted : Roll back previous SCA results ');
2909:
2910: FORALL j IN 1..sca_rollback_lines.COUNT
2911:
2912: UPDATE cn_comm_lines_api API
2913: SET load_status = DECODE(API.load_status, 'UNLOADED', 'OBSOLETE', API.load_status),
2914: adjust_status = DECODE(API.load_status, 'UNLOADED', 'FROZEN', API.adjust_status),
2915: adjust_date = DECODE(API.load_status, 'UNLOADED', sysdate, API.adjust_date),
2916: adjusted_by = DECODE(API.load_status, 'UNLOADED', l_adjusted_by, API.adjusted_by),
2920: debugmsg('Check_comm_lines_api_adjusted : Obsoleting unloaded transactions that have been created for previous SCA results');
2921:
2922: FORALL j IN 1..sca_rollback_lines.COUNT
2923:
2924: INSERT into CN_COMM_LINES_API
2925: ( SALESREP_ID,
2926: PROCESSED_DATE,
2927: PROCESSED_PERIOD_ID,
2928: TRANSACTION_AMOUNT,
3205: CCH.ATTRIBUTE97,
3206: CCH.ATTRIBUTE98,
3207: CCH.ATTRIBUTE99,
3208: CCH.ATTRIBUTE100,
3209: cn_comm_lines_api_s.NEXTVAL,
3210: NULL,
3211: NULL,
3212: NULL,
3213: CCH.ROLLUP_DATE,
3294: debugmsg('Check_comm_lines_api_adjusted : Obsoleting loaded transactions that have been created for previous SCA results');
3295:
3296: FORALL j IN 1..sca_rollback_lines.COUNT
3297:
3298: UPDATE cn_comm_lines_api
3299: SET adjust_status = NULL,
3300: load_status = 'UNLOADED',
3301: adjust_date = sysdate,
3302: adjusted_by = l_adjusted_by,
3360: and source_id between l_start_id and l_end_id) SCA_SOURCE_ID
3361:
3362: where exists
3363: (SELECT 1
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
3373: and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
3374: and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
3375: (CCLA_ORIG.load_status = 'LOADED' and exists
3376:
3377: (select 1 from cn_comm_lines_api CCLA
3378: where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
3379: and CCLA.adjust_status = 'REVERSAL'
3380: and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
3381: )))));
3464: debugmsg('Allocation Transfer : Rerun : Obsoleting unloaded transactions that have been created for previous SCA results');
3465:
3466: FORALL j IN 1..sca_lines_rollback.COUNT
3467:
3468: UPDATE cn_comm_lines_api API
3469: SET load_status = DECODE(API.load_status, 'UNLOADED', 'OBSOLETE', API.load_status),
3470: adjust_status = DECODE(API.load_status, 'UNLOADED', 'FROZEN', API.adjust_status),
3471: adjust_date = DECODE(API.load_status, 'UNLOADED', sysdate, API.adjust_date),
3472: adjusted_by = DECODE(API.load_status, 'UNLOADED', l_adjusted_by, API.adjusted_by),
3476: debugmsg('Allocation Transfer : Rerun : Creating reversals for loaded transactions that have been created for previous SCA results');
3477:
3478: FORALL j IN 1..sca_lines_rollback.COUNT
3479:
3480: INSERT into CN_COMM_LINES_API
3481: ( SALESREP_ID,
3482: PROCESSED_DATE,
3483: PROCESSED_PERIOD_ID,
3484: TRANSACTION_AMOUNT,
3761: CCH.ATTRIBUTE97,
3762: CCH.ATTRIBUTE98,
3763: CCH.ATTRIBUTE99,
3764: CCH.ATTRIBUTE100,
3765: cn_comm_lines_api_s.NEXTVAL,
3766: NULL,
3767: NULL,
3768: NULL,
3769: CCH.ROLLUP_DATE,
3850: debugmsg('Allocation Transfer : Rerun : Obsoleting loaded transactions that have been created for previous error SCA results');
3851:
3852: FORALL j IN 1..sca_lines_rollback.COUNT
3853:
3854: UPDATE cn_comm_lines_api api
3855: SET load_status = 'UNLOADED',
3856: adjust_status = NULL,
3857: adjust_date = sysdate,
3858: adjusted_by = l_adjusted_by,