[Home] [Help]
8:
9: -- Package Name
10: -- cn_transaction_load_pkg
11: -- Purpose
12: -- Procedures TO load trx FROM cn_comm_lines_api TO cn_commission_headers
13: -- History
14: -- 10/21/99 Harlen Chen Created
15: -- 08/28/01 Rao Chenna acctd_transaction_amount column update logic
16: -- is modified.
550: WHERE org_id = p_org_id;
551:
552: -- Commented this query to fix bug# 1772128
553: /*
554: UPDATE cn_comm_lines_api_all
555: SET load_status = 'ERROR - PRIOR ADJUSTMENT'
556: WHERE load_status = 'UNLOADED'
557: AND Trunc(processed_date) >= Trunc(p_start_date)
558: AND Trunc(processed_date) <= Trunc(p_end_date)
559: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
560: AND trx_type <> 'FORECAST'
561: AND processed_date < x_latest_processed_date; */
562: IF (p_salesrep_id IS NULL) THEN
563: UPDATE cn_comm_lines_api_all
564: SET load_status = 'ERROR - PRIOR ADJUSTMENT'
565: WHERE load_status = 'UNLOADED'
566: AND processed_date >= TRUNC(p_start_date)
567: AND processed_date <(TRUNC(p_end_date) + 1)
569: AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
570: AND processed_date < x_latest_processed_date
571: AND org_id = p_org_id;
572: ELSE
573: UPDATE cn_comm_lines_api_all
574: SET load_status = 'ERROR - PRIOR ADJUSTMENT'
575: WHERE load_status = 'UNLOADED'
576: AND processed_date >= TRUNC(p_start_date)
577: AND processed_date <(TRUNC(p_end_date) + 1)
592: /*****************************************/
593:
594: -- Commented this query to fix bug# 1772128
595: /*
596: UPDATE cn_comm_lines_api SET load_status = 'ERROR - TRX_TYPE'
597: WHERE load_status = 'UNLOADED'
598: AND Trunc(processed_date) >= Trunc(p_start_date)
599: AND Trunc(processed_date) <= Trunc(p_end_date)
600: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
601: AND trx_type <> 'FORECAST'
602: AND NOT EXISTS
603: (SELECT 1 FROM cn_lookups WHERE lookup_type = 'TRX TYPES'
604: AND lookup_code =
605: cn_comm_lines_api.trx_type); */
606: -- Added by rchenna on 06/12/01
607: IF (p_salesrep_id IS NULL) THEN
608: UPDATE cn_comm_lines_api_all
609: SET load_status = 'ERROR - TRX_TYPE'
604: AND lookup_code =
605: cn_comm_lines_api.trx_type); */
606: -- Added by rchenna on 06/12/01
607: IF (p_salesrep_id IS NULL) THEN
608: UPDATE cn_comm_lines_api_all
609: SET load_status = 'ERROR - TRX_TYPE'
610: WHERE load_status = 'UNLOADED'
611: AND processed_date >= TRUNC(p_start_date)
612: AND processed_date <(TRUNC(p_end_date) + 1)
616: AND NOT EXISTS(
617: SELECT 1
618: FROM cn_lookups
619: WHERE lookup_type = 'TRX TYPES'
620: AND lookup_code = cn_comm_lines_api_all.trx_type);
621: ELSE
622: UPDATE cn_comm_lines_api_all
623: SET load_status = 'ERROR - TRX_TYPE'
624: WHERE load_status = 'UNLOADED'
618: FROM cn_lookups
619: WHERE lookup_type = 'TRX TYPES'
620: AND lookup_code = cn_comm_lines_api_all.trx_type);
621: ELSE
622: UPDATE cn_comm_lines_api_all
623: SET load_status = 'ERROR - TRX_TYPE'
624: WHERE load_status = 'UNLOADED'
625: AND processed_date >= TRUNC(p_start_date)
626: AND processed_date <(TRUNC(p_end_date) + 1)
631: AND NOT EXISTS(
632: SELECT 1
633: FROM cn_lookups
634: WHERE lookup_type = 'TRX TYPES'
635: AND lookup_code = cn_comm_lines_api_all.trx_type);
636: END IF;
637:
638: --
639: -- Commented this query to fix bug# 1772128
637:
638: --
639: -- Commented this query to fix bug# 1772128
640: /*
641: UPDATE cn_comm_lines_api SET load_status = 'ERROR - REVENUE_CLASS'
642: WHERE load_status = 'UNLOADED'
643: AND Trunc(processed_date) >= Trunc(p_start_date)
644: AND Trunc(processed_date) <= Trunc(p_end_date)
645: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
647: AND revenue_class_id IS NOT NULL
648: AND NOT EXISTS
649: (SELECT 1 FROM cn_revenue_classes
650: WHERE cn_revenue_classes.revenue_class_id =
651: cn_comm_lines_api.revenue_class_id); */
652: -- Added by rchenna on 06/12/01
653: IF (p_salesrep_id IS NULL) THEN
654: UPDATE cn_comm_lines_api_all
655: SET load_status = 'ERROR - REVENUE_CLASS'
650: WHERE cn_revenue_classes.revenue_class_id =
651: cn_comm_lines_api.revenue_class_id); */
652: -- Added by rchenna on 06/12/01
653: IF (p_salesrep_id IS NULL) THEN
654: UPDATE cn_comm_lines_api_all
655: SET load_status = 'ERROR - REVENUE_CLASS'
656: WHERE load_status = 'UNLOADED'
657: AND processed_date >= TRUNC(p_start_date)
658: AND processed_date <(TRUNC(p_end_date) + 1)
663: AND NOT EXISTS(
664: SELECT 1
665: FROM cn_revenue_classes
666: WHERE cn_revenue_classes.revenue_class_id =
667: cn_comm_lines_api_all.revenue_class_id);
668: ELSE
669: UPDATE cn_comm_lines_api_all
670: SET load_status = 'ERROR - REVENUE_CLASS'
671: WHERE load_status = 'UNLOADED'
665: FROM cn_revenue_classes
666: WHERE cn_revenue_classes.revenue_class_id =
667: cn_comm_lines_api_all.revenue_class_id);
668: ELSE
669: UPDATE cn_comm_lines_api_all
670: SET load_status = 'ERROR - REVENUE_CLASS'
671: WHERE load_status = 'UNLOADED'
672: AND processed_date >= TRUNC(p_start_date)
673: AND processed_date <(TRUNC(p_end_date) + 1)
679: AND NOT EXISTS(
680: SELECT 1
681: FROM cn_revenue_classes
682: WHERE cn_revenue_classes.revenue_class_id =
683: cn_comm_lines_api_all.revenue_class_id);
684: END IF;
685:
686: --
687:
693: -- If transaction currency = functional currency, then OK
694: -- if exch rate is NULL, fill in before rate check
695: -- Commented this query to fix bug# 1772128
696: /*
697: UPDATE cn_comm_lines_api
698: SET acctd_transaction_amount = transaction_amount,
699: exchange_rate = 1
700: WHERE load_status = 'UNLOADED'
701: AND Trunc(processed_date) >= Trunc(p_start_date)
707: AND trx_type <> 'FORECAST'
708: AND transaction_currency_code IS NOT NULL
709: AND transaction_currency_code = FunctionalCurrency; */
710: IF (p_salesrep_id IS NULL) THEN
711: UPDATE cn_comm_lines_api_all
712: SET acctd_transaction_amount = transaction_amount * NVL(exchange_rate, 1)
713: WHERE load_status = 'UNLOADED'
714: AND processed_date >= TRUNC(p_start_date)
715: AND processed_date <(TRUNC(p_end_date) + 1)
718: AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
719: AND org_id = p_org_id
720: AND transaction_currency_code = functionalcurrency;
721: ELSE
722: UPDATE cn_comm_lines_api_all
723: SET acctd_transaction_amount = transaction_amount * NVL(exchange_rate, 1)
724: WHERE load_status = 'UNLOADED'
725: AND processed_date >= TRUNC(p_start_date)
726: AND processed_date <(TRUNC(p_end_date) + 1)
740:
741: /* Error when conversion needed but no rate given */
742: -- Commented this query to fix bug# 1772128
743: /*
744: UPDATE cn_comm_lines_api SET load_status = 'ERROR - NO EXCH RATE GIVEN'
745: WHERE load_status = 'UNLOADED'
746: AND Trunc(processed_date) >= Trunc(p_start_date)
747: AND Trunc(processed_date) <= Trunc(p_end_date)
748: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
749: AND trx_type <> 'FORECAST'
750: AND transaction_currency_code IS NOT NULL
751: AND exchange_rate IS NULL; */
752: IF (p_salesrep_id IS NULL) THEN
753: UPDATE cn_comm_lines_api_all
754: SET load_status = 'ERROR - NO EXCH RATE GIVEN'
755: WHERE load_status = 'UNLOADED'
756: AND processed_date >= TRUNC(p_start_date)
757: AND processed_date <(TRUNC(p_end_date) + 1)
762: -- Added to fix the above problem.
763: AND acctd_transaction_amount IS NULL
764: AND org_id = p_org_id;
765: ELSE
766: UPDATE cn_comm_lines_api_all
767: SET load_status = 'ERROR - NO EXCH RATE GIVEN'
768: WHERE load_status = 'UNLOADED'
769: AND processed_date >= TRUNC(p_start_date)
770: AND processed_date <(TRUNC(p_end_date) + 1)
780:
781: /* Error when no rate and code given but functional <> foreign */
782: -- Commented this query to fix bug# 1772128
783: /*
784: UPDATE cn_comm_lines_api SET load_status = 'ERROR - INCORRECT CONV GIVEN'
785: WHERE load_status = 'UNLOADED'
786: AND Trunc(processed_date) >= Trunc(p_start_date)
787: AND Trunc(processed_date) <= Trunc(p_end_date)
788: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
791: AND exchange_rate IS NULL
792: AND acctd_transaction_amount IS NOT NULL
793: AND acctd_transaction_amount <> transaction_amount; */
794: IF (p_salesrep_id IS NULL) THEN
795: UPDATE cn_comm_lines_api_all
796: SET load_status = 'ERROR - INCORRECT CONV GIVEN'
797: WHERE load_status = 'UNLOADED'
798: AND processed_date >= TRUNC(p_start_date)
799: AND processed_date <(TRUNC(p_end_date) + 1)
804: AND acctd_transaction_amount IS NOT NULL
805: AND acctd_transaction_amount <> transaction_amount
806: AND org_id = p_org_id;
807: ELSE
808: UPDATE cn_comm_lines_api_all
809: SET load_status = 'ERROR - INCORRECT CONV GIVEN'
810: WHERE load_status = 'UNLOADED'
811: AND processed_date >= TRUNC(p_start_date)
812: AND processed_date <(TRUNC(p_end_date) + 1)
822:
823: /* Do foreign-to-functional currency conversion */
824: -- Commented this query to fix bug# 1772128
825: /*
826: UPDATE cn_comm_lines_api
827: SET acctd_transaction_amount = (transaction_amount * exchange_rate)
828: WHERE load_status = 'UNLOADED'
829: AND Trunc(processed_date) >= Trunc(p_start_date)
830: AND Trunc(processed_date) <= Trunc(p_end_date)
833: AND acctd_transaction_amount IS NULL
834: AND exchange_rate IS NOT NULL
835: AND transaction_currency_code IS NOT NULL; */
836: IF (p_salesrep_id IS NULL) THEN
837: UPDATE cn_comm_lines_api_all
838: SET acctd_transaction_amount =(transaction_amount * exchange_rate)
839: WHERE load_status = 'UNLOADED'
840: AND processed_date >= TRUNC(p_start_date)
841: AND processed_date <(TRUNC(p_end_date) + 1)
845: AND exchange_rate IS NOT NULL
846: AND transaction_currency_code IS NOT NULL
847: AND org_id = p_org_id;
848: ELSE
849: UPDATE cn_comm_lines_api_all
850: SET acctd_transaction_amount =(transaction_amount * exchange_rate)
851: WHERE load_status = 'UNLOADED'
852: AND processed_date >= TRUNC(p_start_date)
853: AND processed_date <(TRUNC(p_end_date) + 1)
868:
869: /* Default lines w/o both curr code and exch rate to functional currency */
870: -- Commented this query to fix bug# 1772128
871: /*
872: UPDATE cn_comm_lines_api SET acctd_transaction_amount = transaction_amount,
873: transaction_currency_code = FunctionalCurrency, exchange_rate = 1
874: WHERE load_status = 'UNLOADED'
875: AND Trunc(processed_date) >= Trunc(p_start_date)
876: AND Trunc(processed_date) <= Trunc(p_end_date)
879: AND acctd_transaction_amount IS NULL
880: AND exchange_rate IS NULL
881: AND transaction_currency_code IS NULL; */
882: IF (p_salesrep_id IS NULL) THEN
883: UPDATE cn_comm_lines_api_all
884: SET acctd_transaction_amount = transaction_amount
885: , transaction_currency_code = functionalcurrency
886: , exchange_rate = 1
887: WHERE load_status = 'UNLOADED'
893: AND exchange_rate IS NULL
894: AND transaction_currency_code IS NULL
895: AND org_id = p_org_id;
896: ELSE
897: UPDATE cn_comm_lines_api_all
898: SET acctd_transaction_amount = transaction_amount
899: , transaction_currency_code = functionalcurrency
900: , exchange_rate = 1
901: WHERE load_status = 'UNLOADED'
918:
919: /* Catch any lines that couldn't be converted, last ditch */
920: -- Commented this query to fix bug# 1772128
921: /*
922: UPDATE cn_comm_lines_api SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
923: WHERE load_status = 'UNLOADED'
924: AND Trunc(processed_date) >= Trunc(p_start_date)
925: AND Trunc(processed_date) <= Trunc(p_end_date)
926: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
926: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
927: AND trx_type <> 'FORECAST'
928: AND acctd_transaction_amount IS NULL; */
929: IF (p_salesrep_id IS NULL) THEN
930: UPDATE cn_comm_lines_api_all
931: SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
932: WHERE load_status = 'UNLOADED'
933: AND processed_date >= TRUNC(p_start_date)
934: AND processed_date <(TRUNC(p_end_date) + 1)
936: AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
937: AND acctd_transaction_amount IS NULL
938: AND org_id = p_org_id;
939: ELSE
940: UPDATE cn_comm_lines_api_all
941: SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
942: WHERE load_status = 'UNLOADED'
943: AND processed_date >= TRUNC(p_start_date)
944: AND processed_date <(TRUNC(p_end_date) + 1)
970: ) IS
971: BEGIN
972: -- Commented this query to fix bug# 1772128
973: /*
974: UPDATE cn_comm_lines_api SET load_status = 'SALESREP ERROR'
975: WHERE load_Status = 'UNLOADED'
976: AND Trunc(processed_date) >= Trunc(p_start_date)
977: AND Trunc(processed_date) <= Trunc(p_end_date)
978: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
978: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
979: AND trx_type <> 'FORECAST'
980: AND NOT EXISTS (SELECT 1 FROM cn_salesreps
981: WHERE employee_number =
982: cn_comm_lines_api.employee_number); */
983: IF (p_salesrep_id IS NULL) THEN
984: UPDATE cn_comm_lines_api_all
985: SET load_status = 'SALESREP ERROR'
986: WHERE load_status = 'UNLOADED'
980: AND NOT EXISTS (SELECT 1 FROM cn_salesreps
981: WHERE employee_number =
982: cn_comm_lines_api.employee_number); */
983: IF (p_salesrep_id IS NULL) THEN
984: UPDATE cn_comm_lines_api_all
985: SET load_status = 'SALESREP ERROR'
986: WHERE load_status = 'UNLOADED'
987: AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
988: AND processed_date >= TRUNC(p_start_date)
990: AND trx_type <> 'FORECAST'
991: AND org_id = p_org_id
992: AND NOT EXISTS(SELECT 1
993: FROM cn_salesreps
994: WHERE employee_number = cn_comm_lines_api_all.employee_number);
995: ELSE
996: UPDATE cn_comm_lines_api_all
997: SET load_status = 'SALESREP ERROR'
998: WHERE load_status = 'UNLOADED'
992: AND NOT EXISTS(SELECT 1
993: FROM cn_salesreps
994: WHERE employee_number = cn_comm_lines_api_all.employee_number);
995: ELSE
996: UPDATE cn_comm_lines_api_all
997: SET load_status = 'SALESREP ERROR'
998: WHERE load_status = 'UNLOADED'
999: AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
1000: AND processed_date >= TRUNC(p_start_date)
1003: AND trx_type <> 'FORECAST'
1004: AND org_id = p_org_id
1005: AND NOT EXISTS(SELECT 1
1006: FROM cn_salesreps
1007: WHERE employee_number = cn_comm_lines_api_all.employee_number);
1008: END IF;
1009:
1010: debugmsg('Loader : Post_Conc_Dispatch : # of SALESREP ERROR = ' || TO_CHAR(SQL%ROWCOUNT));
1011:
1010: debugmsg('Loader : Post_Conc_Dispatch : # of SALESREP ERROR = ' || TO_CHAR(SQL%ROWCOUNT));
1011:
1012: -- Commented this query to fix bug# 1772128
1013: /*
1014: UPDATE cn_comm_lines_api SET load_status = 'PERIOD ERROR'
1015: WHERE load_Status = 'UNLOADED'
1016: AND Trunc(processed_date) >= Trunc(p_start_date)
1017: AND Trunc(processed_date) <= Trunc(p_end_date)
1018: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
1017: AND Trunc(processed_date) <= Trunc(p_end_date)
1018: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
1019: AND trx_type <> 'FORECAST';*/
1020: IF (p_salesrep_id IS NULL) THEN
1021: UPDATE cn_comm_lines_api_all
1022: SET load_status = 'PERIOD ERROR'
1023: WHERE load_status = 'UNLOADED'
1024: AND processed_date >= TRUNC(p_start_date)
1025: AND processed_date <(TRUNC(p_end_date) + 1)
1026: AND trx_type <> 'FORECAST'
1027: AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
1028: AND org_id = p_org_id;
1029: ELSE
1030: UPDATE cn_comm_lines_api_all
1031: SET load_status = 'PERIOD ERROR'
1032: WHERE load_status = 'UNLOADED'
1033: AND processed_date >= TRUNC(p_start_date)
1034: AND processed_date <(TRUNC(p_end_date) + 1)
1052: --+
1053:
1054: -- performance bug 1690393 : full table scan
1055: -- original statement
1056: -- UPDATE /* index (api, mis_cn_comm_lines_api_n1) */ cn_comm_lines_api api
1057: -- SET api.load_status = 'UNLOADED'
1058: -- WHERE api.load_status <> 'UNLOADED'
1059: -- AND api.trx_type <> 'FORECAST'
1060: -- AND api.load_status <> 'OBSOLETE'
1071:
1072: IF l_cn_reset_error_trx = 'Y' THEN
1073: debugmsg('Reset load status of error transactions to UNLOADED');
1074:
1075: -- UPDATE /* index (api, mis_cn_comm_lines_api_n1) */ cn_comm_lines_api api
1076: UPDATE cn_comm_lines_api_all api
1077: SET api.load_status = 'UNLOADED'
1078: WHERE api.trx_type <> 'FORECAST'
1079: AND api.load_status IN(
1072: IF l_cn_reset_error_trx = 'Y' THEN
1073: debugmsg('Reset load status of error transactions to UNLOADED');
1074:
1075: -- UPDATE /* index (api, mis_cn_comm_lines_api_n1) */ cn_comm_lines_api api
1076: UPDATE cn_comm_lines_api_all api
1077: SET api.load_status = 'UNLOADED'
1078: WHERE api.trx_type <> 'FORECAST'
1079: AND api.load_status IN(
1080: 'ERROR - PRIOR ADJUSTMENT'
1095:
1096: --+
1097: --+ Update null salerep_id based on the given employee_number
1098: --+
1099: UPDATE /*+ index(api, cn_comm_lines_api_f2)*/ cn_comm_lines_api_all api
1100: SET api.salesrep_id =
1101: (SELECT cs1.salesrep_id
1102: FROM cn_salesreps cs1
1103: WHERE cs1.employee_number = api.employee_number
1119:
1120: --+
1121: --+ Update null employee_number based on the given salesrep_id
1122: --+
1123: -- UPDATE /*+ index(api, cn_comm_lines_api_n1)*/ cn_comm_lines_api_all api
1124: UPDATE cn_comm_lines_api_all api
1125: SET employee_number =
1126: (SELECT employee_number
1127: FROM cn_salesreps cs1
1120: --+
1121: --+ Update null employee_number based on the given salesrep_id
1122: --+
1123: -- UPDATE /*+ index(api, cn_comm_lines_api_n1)*/ cn_comm_lines_api_all api
1124: UPDATE cn_comm_lines_api_all api
1125: SET employee_number =
1126: (SELECT employee_number
1127: FROM cn_salesreps cs1
1128: WHERE cs1.salesrep_id = api.salesrep_id
1145: --+
1146: --+ IF both salesrep_id and employee_number are null,
1147: --+ set load_status to SALESREP ERROR
1148: --+
1149: UPDATE /*+ index(api, cn_comm_lines_api_f2)*/ cn_comm_lines_api_all api
1150: SET api.load_status = 'SALESREP ERROR'
1151: WHERE api.load_status = 'UNLOADED'
1152: AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
1153: AND api.salesrep_id IS NULL
1159:
1160: -- Name:
1161: -- Load
1162: -- Purpose:
1163: -- This procedure loads trx from CN_COMM_LINES_API to CN_COMMISSION_HEADERS,
1164: -- update cn_process_batches, and perform classification, and rollup phases
1165: PROCEDURE load(
1166: errbuf OUT NOCOPY VARCHAR2
1167: , retcode OUT NOCOPY NUMBER
1291: , acc.period_id period_id
1292: , acc.start_date start_date
1293: , acc.end_date end_date
1294: , COUNT(*) trx_count
1295: FROM cn_comm_lines_api api, cn_acc_period_statuses_v acc
1296: WHERE api.load_status = 'UNLOADED'
1297: AND api.trx_type <> 'FORECAST'
1298: AND (adjust_status <> 'SCA_PENDING' )-- OR adjust_status IS NULL)
1299: AND api.processed_date >= TRUNC(l_start_date)
1771: , api.sales_channel
1772: , api.split_pct
1773: , api.split_status
1774: , api.org_id -- vensrini transaction load fix
1775: FROM cn_comm_lines_api api
1776: WHERE api.load_status = 'UNLOADED'
1777: AND api.processed_date >= TRUNC(p_start_date)
1778: AND api.processed_date <(TRUNC(p_end_date) + 1)
1779: AND api.trx_type <> 'FORECAST'
2115: , api.sales_channel
2116: , api.split_pct
2117: , api.split_status
2118: , api.org_id -- vensrini transaction load fix
2119: FROM cn_comm_lines_api api
2120: WHERE api.load_status = 'UNLOADED'
2121: AND api.processed_date >= TRUNC(p_start_date)
2122: AND api.processed_date <(TRUNC(p_end_date) + 1)
2123: AND api.trx_type <> 'FORECAST'
2134: debugmsg('Loader : number of loaded trx = ' || TO_CHAR(SQL%ROWCOUNT));
2135:
2136: -- Commented this query to fix bug# 1772128
2137: /*
2138: UPDATE cn_comm_lines_api api
2139: SET load_Status = 'LOADED'
2140: WHERE
2141: api.load_status = 'UNLOADED' AND
2142: Trunc(api.processed_date) >= Trunc(p_start_date) AND
2146: api.salesrep_id = batch.salesrep_id AND
2147: Trunc(api.processed_date) >= Trunc(batch.start_date) AND
2148: Trunc(api.processed_date) <= Trunc(batch.end_date); */
2149: IF (l_skip_credit_flag = 'Y') THEN
2150: UPDATE cn_comm_lines_api api
2151: SET load_status = 'LOADED'
2152: WHERE api.load_status = 'UNLOADED'
2153: AND api.processed_date >= TRUNC(p_start_date)
2154: AND api.processed_date <(TRUNC(p_end_date) + 1)
2157: AND api.salesrep_id = batch.salesrep_id
2158: AND api.processed_date >= TRUNC(batch.start_date)
2159: AND api.processed_date <(TRUNC(batch.end_date) + 1);
2160: ELSE
2161: UPDATE cn_comm_lines_api api
2162: SET load_status = 'LOADED'
2163: WHERE api.load_status = 'UNLOADED'
2164: AND api.processed_date >= TRUNC(p_start_date)
2165: AND api.processed_date <(TRUNC(p_end_date) + 1)