[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) ' ||
428: if (batch_type = 'INV') then
429:
430: l_sql_stmt_count :=
431:
432: 'select count(distinct(invoice_number)) from cn_comm_lines_api ';
433:
434: l_sql_stmt :=
435:
436: 'where trunc(processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||
616: end if;
617:
618: if (batch_type = 'ORD') then
619:
620: l_sql_stmt_id := 'select distinct(order_number) from cn_comm_lines_api ';
621: l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
622:
623: l_sql_stmt_id :=
624:
631: end if;
632:
633: if (batch_type = 'INV') then
634:
635: l_sql_stmt_id := 'select distinct(invoice_number) from cn_comm_lines_api ';
636: l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
637:
638: l_sql_stmt_id :=
639:
905: FETCH sca_lines_cur BULK COLLECT INTO sca_lines limit 1000;
906:
907: FORALL j IN 1..sca_lines.COUNT
908:
909: INSERT into CN_COMM_LINES_API
910: ( SALESREP_ID,
911: PROCESSED_DATE,
912: PROCESSED_PERIOD_ID,
913: TRANSACTION_AMOUNT,
1190: CCLA.ATTRIBUTE97,
1191: CCLA.ATTRIBUTE98,
1192: CCLA.ATTRIBUTE99,
1193: CCLA.ATTRIBUTE100,
1194: cn_comm_lines_api_s.NEXTVAL,
1195: CCLA.CONC_BATCH_ID,
1196: CCLA.PROCESS_BATCH_ID,
1197: NULL,
1198: CCLA.ROLLUP_DATE,
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
1354: debugmsg('Populate results back to API: Inserting Transactions into API for ''No Rules'', ''No Credit'' headers');
1355:
1356: FORALL j IN 1..sca_lines.COUNT
1357:
1358: UPDATE cn_comm_lines_api api
1359: SET load_status = 'OBSOLETE',
1360: adjust_status = 'FROZEN',
1361: adjust_date = sysdate,
1362: adjusted_by = l_adjusted_by,
1366:
1367:
1368: FORALL j IN 1..sca_not_allocate_lines.COUNT
1369:
1370: INSERT into CN_COMM_LINES_API
1371: ( SALESREP_ID,
1372: PROCESSED_DATE,
1373: PROCESSED_PERIOD_ID,
1374: TRANSACTION_AMOUNT,
1650: CCLA.ATTRIBUTE97,
1651: CCLA.ATTRIBUTE98,
1652: CCLA.ATTRIBUTE99,
1653: CCLA.ATTRIBUTE100,
1654: cn_comm_lines_api_s.NEXTVAL,
1655: CCLA.CONC_BATCH_ID,
1656: CCLA.PROCESS_BATCH_ID,
1657: NULL,
1658: CCLA.ROLLUP_DATE,
1717: CCLA.SPLIT_PCT,
1718: CCLA.SPLIT_status,
1719: ccla.org_id
1720: from
1721: cn_comm_lines_api CCLA
1722: where CCLA.comm_lines_api_id = sca_not_allocate_lines(j));
1723:
1724: FORALL j IN 1..sca_no_rule_lines.COUNT
1725:
1722: where CCLA.comm_lines_api_id = sca_not_allocate_lines(j));
1723:
1724: FORALL j IN 1..sca_no_rule_lines.COUNT
1725:
1726: INSERT into CN_COMM_LINES_API
1727: ( SALESREP_ID,
1728: PROCESSED_DATE,
1729: PROCESSED_PERIOD_ID,
1730: TRANSACTION_AMOUNT,
2007: CCLA.ATTRIBUTE97,
2008: CCLA.ATTRIBUTE98,
2009: CCLA.ATTRIBUTE99,
2010: CCLA.ATTRIBUTE100,
2011: cn_comm_lines_api_s.NEXTVAL,
2012: CCLA.CONC_BATCH_ID,
2013: CCLA.PROCESS_BATCH_ID,
2014: NULL,
2015: CCLA.ROLLUP_DATE,
2074: CCLA.SPLIT_PCT,
2075: CCLA.SPLIT_status,
2076: ccla.org_id
2077: from
2078: cn_comm_lines_api CCLA
2079: where CCLA.comm_lines_api_id = sca_no_rule_lines(j));
2080:
2081:
2082: debugmsg('Populate results back to API: Done with Negating Transactions ');
2625: conc_status boolean;
2626:
2627: cursor api_adjust_lines_inv_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2628:
2629: select CCLA.comm_lines_api_id from cn_comm_lines_api CCLA
2630: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2631: and CCLA.load_status = 'UNLOADED'
2632: and (--(CCLA.adjust_status is null) or
2633: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2651: where source_type = 'INV'
2652: and source_id in
2653: (select invoice_number
2654: from
2655: (select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
2656: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2657: and CCLA.load_status = 'UNLOADED'
2658: and (--(CCLA.adjust_status is null) or
2659: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2664: and CCLA.invoice_number between l_start_id and l_end_id) SCA_SOURCE_ID
2665:
2666: where exists
2667: (SELECT 1
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
2677: and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
2678: and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
2679: (CCLA_ORIG.load_status = 'LOADED' and exists
2680:
2681: (select 1 from cn_comm_lines_api CCLA
2682: where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
2683: and CCLA.adjust_status = 'REVERSAL'
2684: and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
2685: )))));
2691: where ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
2692: and source_type = 'INV'
2693: and source_id in
2694:
2695: (select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
2696: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2697: and CCLA.load_status = 'UNLOADED'
2698: and (--(CCLA.adjust_status is null) or
2699: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2711: and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
2712: and CSHI.source_type = 'INV'
2713: and CSHI.source_id in
2714:
2715: (select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
2716: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2717: and CCLA.load_status = 'UNLOADED'
2718: and (--(CCLA.adjust_status is null) or
2719: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2724: and CCLA.invoice_number between l_start_id and l_end_id);
2725:
2726: cursor api_adjust_lines_ord_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2727:
2728: select CCLA.comm_lines_api_id from cn_comm_lines_api CCLA
2729: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2730: and CCLA.load_status = 'UNLOADED'
2731: and (--(CCLA.adjust_status is null) or
2732: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2751: where source_type = 'ORD'
2752: and source_id in
2753: (select order_number
2754: from
2755: (select distinct(order_number) order_number from cn_comm_lines_api CCLA
2756: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2757: and CCLA.load_status = 'UNLOADED'
2758: and (--(CCLA.adjust_status is null) or
2759: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2765: and CCLA.order_number between l_start_id and l_end_id) SCA_SOURCE_ID
2766:
2767: where exists
2768: (SELECT 1
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
2778: and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
2779: and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
2780: (CCLA_ORIG.load_status = 'LOADED' and exists
2781:
2782: (select 1 from cn_comm_lines_api CCLA
2783: where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
2784: and CCLA.adjust_status = 'REVERSAL'
2785: and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
2786: )))));
2792: where ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
2793: and source_type = 'ORD'
2794: and source_id in
2795:
2796: (select distinct(order_number) order_number from cn_comm_lines_api CCLA
2797: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2798: and CCLA.load_status = 'UNLOADED'
2799: and (--(CCLA.adjust_status is null) or
2800: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2813: and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
2814: and CSHI.source_type = 'ORD'
2815: and CSHI.source_id in
2816:
2817: (select distinct(order_number) order_number from cn_comm_lines_api CCLA
2818: where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2819: and CCLA.load_status = 'UNLOADED'
2820: and (--(CCLA.adjust_status is null) or
2821: (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
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:
2834: sca_adjust_headers sca_adjust_headers_tbl;
2897: end if;
2898:
2899: FORALL j IN 1..api_adjust_lines.COUNT
2900:
2901: UPDATE cn_comm_lines_api
2902: SET adjust_status = 'SCA_NOT_ELIGIBLE',
2903: adjust_date = sysdate,
2904: adjusted_by = l_adjusted_by,
2905: adjust_comments = 'SCA Check'
2910: debugmsg('Check_comm_lines_api_adjusted : Roll back previous SCA results ');
2911:
2912: FORALL j IN 1..sca_rollback_lines.COUNT
2913:
2914: UPDATE cn_comm_lines_api API
2915: SET load_status = DECODE(API.load_status, 'UNLOADED', 'OBSOLETE', API.load_status),
2916: adjust_status = NVL(DECODE(API.load_status, 'UNLOADED', 'FROZEN', API.adjust_status),'NEW'),
2917: adjust_date = DECODE(API.load_status, 'UNLOADED', sysdate, API.adjust_date),
2918: adjusted_by = DECODE(API.load_status, 'UNLOADED', l_adjusted_by, API.adjusted_by),
2922: debugmsg('Check_comm_lines_api_adjusted : Obsoleting unloaded transactions that have been created for previous SCA results');
2923:
2924: FORALL j IN 1..sca_rollback_lines.COUNT
2925:
2926: INSERT into CN_COMM_LINES_API
2927: ( SALESREP_ID,
2928: PROCESSED_DATE,
2929: PROCESSED_PERIOD_ID,
2930: TRANSACTION_AMOUNT,
3207: CCH.ATTRIBUTE97,
3208: CCH.ATTRIBUTE98,
3209: CCH.ATTRIBUTE99,
3210: CCH.ATTRIBUTE100,
3211: cn_comm_lines_api_s.NEXTVAL,
3212: NULL,
3213: NULL,
3214: NULL,
3215: CCH.ROLLUP_DATE,
3298: debugmsg('Check_comm_lines_api_adjusted : Obsoleting loaded transactions that have been created for previous SCA results');
3299:
3300: FORALL j IN 1..sca_rollback_lines.COUNT
3301:
3302: UPDATE cn_comm_lines_api
3303: SET adjust_status = 'NEW', --NULL,
3304: load_status = 'UNLOADED',
3305: adjust_date = sysdate,
3306: adjusted_by = l_adjusted_by,
3364: and source_id between l_start_id and l_end_id) SCA_SOURCE_ID
3365:
3366: where exists
3367: (SELECT 1
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
3377: and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
3378: and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
3379: (CCLA_ORIG.load_status = 'LOADED' and exists
3380:
3381: (select 1 from cn_comm_lines_api CCLA
3382: where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
3383: and CCLA.adjust_status = 'REVERSAL'
3384: and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
3385: )))));
3468: debugmsg('Allocation Transfer : Rerun : Obsoleting unloaded transactions that have been created for previous SCA results');
3469:
3470: FORALL j IN 1..sca_lines_rollback.COUNT
3471:
3472: UPDATE cn_comm_lines_api API
3473: SET load_status = DECODE(API.load_status, 'UNLOADED', 'OBSOLETE', API.load_status),
3474: adjust_status = NVL(DECODE(API.load_status, 'UNLOADED', 'FROZEN', API.adjust_status),'NEW'),
3475: adjust_date = DECODE(API.load_status, 'UNLOADED', sysdate, API.adjust_date),
3476: adjusted_by = DECODE(API.load_status, 'UNLOADED', l_adjusted_by, API.adjusted_by),
3480: debugmsg('Allocation Transfer : Rerun : Creating reversals for loaded transactions that have been created for previous SCA results');
3481:
3482: FORALL j IN 1..sca_lines_rollback.COUNT
3483:
3484: INSERT into CN_COMM_LINES_API
3485: ( SALESREP_ID,
3486: PROCESSED_DATE,
3487: PROCESSED_PERIOD_ID,
3488: TRANSACTION_AMOUNT,
3765: CCH.ATTRIBUTE97,
3766: CCH.ATTRIBUTE98,
3767: CCH.ATTRIBUTE99,
3768: CCH.ATTRIBUTE100,
3769: cn_comm_lines_api_s.NEXTVAL,
3770: NULL,
3771: NULL,
3772: NULL,
3773: CCH.ROLLUP_DATE,
3856: debugmsg('Allocation Transfer : Rerun : Obsoleting loaded transactions that have been created for previous error SCA results');
3857:
3858: FORALL j IN 1..sca_lines_rollback.COUNT
3859:
3860: UPDATE cn_comm_lines_api api
3861: SET load_status = 'UNLOADED',
3862: adjust_status = 'NEW', --NULL,
3863: adjust_date = sysdate,
3864: adjusted_by = l_adjusted_by,