[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.
547: WHERE org_id = p_org_id;
548:
549: -- Commented this query to fix bug# 1772128
550: /*
551: UPDATE cn_comm_lines_api_all
552: SET load_status = 'ERROR - PRIOR ADJUSTMENT'
553: WHERE load_status = 'UNLOADED'
554: AND Trunc(processed_date) >= Trunc(p_start_date)
555: AND Trunc(processed_date) <= Trunc(p_end_date)
556: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
557: AND trx_type <> 'FORECAST'
558: AND processed_date < x_latest_processed_date; */
559: IF (p_salesrep_id IS NULL) THEN
560: UPDATE cn_comm_lines_api_all
561: SET load_status = 'ERROR - PRIOR ADJUSTMENT'
562: WHERE load_status = 'UNLOADED'
563: AND processed_date >= TRUNC(p_start_date)
564: AND processed_date <(TRUNC(p_end_date) + 1)
566: AND (adjust_status <> 'SCA_PENDING' OR adjust_status IS NULL)
567: AND processed_date < x_latest_processed_date
568: AND org_id = p_org_id;
569: ELSE
570: UPDATE cn_comm_lines_api_all
571: SET load_status = 'ERROR - PRIOR ADJUSTMENT'
572: WHERE load_status = 'UNLOADED'
573: AND processed_date >= TRUNC(p_start_date)
574: AND processed_date <(TRUNC(p_end_date) + 1)
589: /*****************************************/
590:
591: -- Commented this query to fix bug# 1772128
592: /*
593: UPDATE cn_comm_lines_api SET load_status = 'ERROR - TRX_TYPE'
594: WHERE load_status = 'UNLOADED'
595: AND Trunc(processed_date) >= Trunc(p_start_date)
596: AND Trunc(processed_date) <= Trunc(p_end_date)
597: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
598: AND trx_type <> 'FORECAST'
599: AND NOT EXISTS
600: (SELECT 1 FROM cn_lookups WHERE lookup_type = 'TRX TYPES'
601: AND lookup_code =
602: cn_comm_lines_api.trx_type); */
603: -- Added by rchenna on 06/12/01
604: IF (p_salesrep_id IS NULL) THEN
605: UPDATE cn_comm_lines_api_all
606: SET load_status = 'ERROR - TRX_TYPE'
601: AND lookup_code =
602: cn_comm_lines_api.trx_type); */
603: -- Added by rchenna on 06/12/01
604: IF (p_salesrep_id IS NULL) THEN
605: UPDATE cn_comm_lines_api_all
606: SET load_status = 'ERROR - TRX_TYPE'
607: WHERE load_status = 'UNLOADED'
608: AND processed_date >= TRUNC(p_start_date)
609: AND processed_date <(TRUNC(p_end_date) + 1)
613: AND NOT EXISTS(
614: SELECT 1
615: FROM cn_lookups
616: WHERE lookup_type = 'TRX TYPES'
617: AND lookup_code = cn_comm_lines_api_all.trx_type);
618: ELSE
619: UPDATE cn_comm_lines_api_all
620: SET load_status = 'ERROR - TRX_TYPE'
621: WHERE load_status = 'UNLOADED'
615: FROM cn_lookups
616: WHERE lookup_type = 'TRX TYPES'
617: AND lookup_code = cn_comm_lines_api_all.trx_type);
618: ELSE
619: UPDATE cn_comm_lines_api_all
620: SET load_status = 'ERROR - TRX_TYPE'
621: WHERE load_status = 'UNLOADED'
622: AND processed_date >= TRUNC(p_start_date)
623: AND processed_date <(TRUNC(p_end_date) + 1)
628: AND NOT EXISTS(
629: SELECT 1
630: FROM cn_lookups
631: WHERE lookup_type = 'TRX TYPES'
632: AND lookup_code = cn_comm_lines_api_all.trx_type);
633: END IF;
634:
635: --
636: -- Commented this query to fix bug# 1772128
634:
635: --
636: -- Commented this query to fix bug# 1772128
637: /*
638: UPDATE cn_comm_lines_api SET load_status = 'ERROR - REVENUE_CLASS'
639: WHERE load_status = 'UNLOADED'
640: AND Trunc(processed_date) >= Trunc(p_start_date)
641: AND Trunc(processed_date) <= Trunc(p_end_date)
642: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
644: AND revenue_class_id IS NOT NULL
645: AND NOT EXISTS
646: (SELECT 1 FROM cn_revenue_classes
647: WHERE cn_revenue_classes.revenue_class_id =
648: cn_comm_lines_api.revenue_class_id); */
649: -- Added by rchenna on 06/12/01
650: IF (p_salesrep_id IS NULL) THEN
651: UPDATE cn_comm_lines_api_all
652: SET load_status = 'ERROR - REVENUE_CLASS'
647: WHERE cn_revenue_classes.revenue_class_id =
648: cn_comm_lines_api.revenue_class_id); */
649: -- Added by rchenna on 06/12/01
650: IF (p_salesrep_id IS NULL) THEN
651: UPDATE cn_comm_lines_api_all
652: SET load_status = 'ERROR - REVENUE_CLASS'
653: WHERE load_status = 'UNLOADED'
654: AND processed_date >= TRUNC(p_start_date)
655: AND processed_date <(TRUNC(p_end_date) + 1)
660: AND NOT EXISTS(
661: SELECT 1
662: FROM cn_revenue_classes
663: WHERE cn_revenue_classes.revenue_class_id =
664: cn_comm_lines_api_all.revenue_class_id);
665: ELSE
666: UPDATE cn_comm_lines_api_all
667: SET load_status = 'ERROR - REVENUE_CLASS'
668: WHERE load_status = 'UNLOADED'
662: FROM cn_revenue_classes
663: WHERE cn_revenue_classes.revenue_class_id =
664: cn_comm_lines_api_all.revenue_class_id);
665: ELSE
666: UPDATE cn_comm_lines_api_all
667: SET load_status = 'ERROR - REVENUE_CLASS'
668: WHERE load_status = 'UNLOADED'
669: AND processed_date >= TRUNC(p_start_date)
670: AND processed_date <(TRUNC(p_end_date) + 1)
676: AND NOT EXISTS(
677: SELECT 1
678: FROM cn_revenue_classes
679: WHERE cn_revenue_classes.revenue_class_id =
680: cn_comm_lines_api_all.revenue_class_id);
681: END IF;
682:
683: --
684:
690: -- If transaction currency = functional currency, then OK
691: -- if exch rate is NULL, fill in before rate check
692: -- Commented this query to fix bug# 1772128
693: /*
694: UPDATE cn_comm_lines_api
695: SET acctd_transaction_amount = transaction_amount,
696: exchange_rate = 1
697: WHERE load_status = 'UNLOADED'
698: AND Trunc(processed_date) >= Trunc(p_start_date)
704: AND trx_type <> 'FORECAST'
705: AND transaction_currency_code IS NOT NULL
706: AND transaction_currency_code = FunctionalCurrency; */
707: IF (p_salesrep_id IS NULL) THEN
708: UPDATE cn_comm_lines_api_all
709: SET acctd_transaction_amount = transaction_amount * NVL(exchange_rate, 1)
710: WHERE load_status = 'UNLOADED'
711: AND processed_date >= TRUNC(p_start_date)
712: AND processed_date <(TRUNC(p_end_date) + 1)
715: AND (adjust_status <> 'SCA_PENDING' OR adjust_status IS NULL)
716: AND org_id = p_org_id
717: AND transaction_currency_code = functionalcurrency;
718: ELSE
719: UPDATE cn_comm_lines_api_all
720: SET acctd_transaction_amount = transaction_amount * NVL(exchange_rate, 1)
721: WHERE load_status = 'UNLOADED'
722: AND processed_date >= TRUNC(p_start_date)
723: AND processed_date <(TRUNC(p_end_date) + 1)
737:
738: /* Error when conversion needed but no rate given */
739: -- Commented this query to fix bug# 1772128
740: /*
741: UPDATE cn_comm_lines_api SET load_status = 'ERROR - NO EXCH RATE GIVEN'
742: WHERE load_status = 'UNLOADED'
743: AND Trunc(processed_date) >= Trunc(p_start_date)
744: AND Trunc(processed_date) <= Trunc(p_end_date)
745: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
746: AND trx_type <> 'FORECAST'
747: AND transaction_currency_code IS NOT NULL
748: AND exchange_rate IS NULL; */
749: IF (p_salesrep_id IS NULL) THEN
750: UPDATE cn_comm_lines_api_all
751: SET load_status = 'ERROR - NO EXCH RATE GIVEN'
752: WHERE load_status = 'UNLOADED'
753: AND processed_date >= TRUNC(p_start_date)
754: AND processed_date <(TRUNC(p_end_date) + 1)
759: -- Added to fix the above problem.
760: AND acctd_transaction_amount IS NULL
761: AND org_id = p_org_id;
762: ELSE
763: UPDATE cn_comm_lines_api_all
764: SET load_status = 'ERROR - NO EXCH RATE GIVEN'
765: WHERE load_status = 'UNLOADED'
766: AND processed_date >= TRUNC(p_start_date)
767: AND processed_date <(TRUNC(p_end_date) + 1)
777:
778: /* Error when no rate and code given but functional <> foreign */
779: -- Commented this query to fix bug# 1772128
780: /*
781: UPDATE cn_comm_lines_api SET load_status = 'ERROR - INCORRECT CONV GIVEN'
782: WHERE load_status = 'UNLOADED'
783: AND Trunc(processed_date) >= Trunc(p_start_date)
784: AND Trunc(processed_date) <= Trunc(p_end_date)
785: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
788: AND exchange_rate IS NULL
789: AND acctd_transaction_amount IS NOT NULL
790: AND acctd_transaction_amount <> transaction_amount; */
791: IF (p_salesrep_id IS NULL) THEN
792: UPDATE cn_comm_lines_api_all
793: SET load_status = 'ERROR - INCORRECT CONV GIVEN'
794: WHERE load_status = 'UNLOADED'
795: AND processed_date >= TRUNC(p_start_date)
796: AND processed_date <(TRUNC(p_end_date) + 1)
801: AND acctd_transaction_amount IS NOT NULL
802: AND acctd_transaction_amount <> transaction_amount
803: AND org_id = p_org_id;
804: ELSE
805: UPDATE cn_comm_lines_api_all
806: SET load_status = 'ERROR - INCORRECT CONV GIVEN'
807: WHERE load_status = 'UNLOADED'
808: AND processed_date >= TRUNC(p_start_date)
809: AND processed_date <(TRUNC(p_end_date) + 1)
819:
820: /* Do foreign-to-functional currency conversion */
821: -- Commented this query to fix bug# 1772128
822: /*
823: UPDATE cn_comm_lines_api
824: SET acctd_transaction_amount = (transaction_amount * exchange_rate)
825: WHERE load_status = 'UNLOADED'
826: AND Trunc(processed_date) >= Trunc(p_start_date)
827: AND Trunc(processed_date) <= Trunc(p_end_date)
830: AND acctd_transaction_amount IS NULL
831: AND exchange_rate IS NOT NULL
832: AND transaction_currency_code IS NOT NULL; */
833: IF (p_salesrep_id IS NULL) THEN
834: UPDATE cn_comm_lines_api_all
835: SET acctd_transaction_amount =(transaction_amount * exchange_rate)
836: WHERE load_status = 'UNLOADED'
837: AND processed_date >= TRUNC(p_start_date)
838: AND processed_date <(TRUNC(p_end_date) + 1)
842: AND exchange_rate IS NOT NULL
843: AND transaction_currency_code IS NOT NULL
844: AND org_id = p_org_id;
845: ELSE
846: UPDATE cn_comm_lines_api_all
847: SET acctd_transaction_amount =(transaction_amount * exchange_rate)
848: WHERE load_status = 'UNLOADED'
849: AND processed_date >= TRUNC(p_start_date)
850: AND processed_date <(TRUNC(p_end_date) + 1)
865:
866: /* Default lines w/o both curr code and exch rate to functional currency */
867: -- Commented this query to fix bug# 1772128
868: /*
869: UPDATE cn_comm_lines_api SET acctd_transaction_amount = transaction_amount,
870: transaction_currency_code = FunctionalCurrency, exchange_rate = 1
871: WHERE load_status = 'UNLOADED'
872: AND Trunc(processed_date) >= Trunc(p_start_date)
873: AND Trunc(processed_date) <= Trunc(p_end_date)
876: AND acctd_transaction_amount IS NULL
877: AND exchange_rate IS NULL
878: AND transaction_currency_code IS NULL; */
879: IF (p_salesrep_id IS NULL) THEN
880: UPDATE cn_comm_lines_api_all
881: SET acctd_transaction_amount = transaction_amount
882: , transaction_currency_code = functionalcurrency
883: , exchange_rate = 1
884: WHERE load_status = 'UNLOADED'
890: AND exchange_rate IS NULL
891: AND transaction_currency_code IS NULL
892: AND org_id = p_org_id;
893: ELSE
894: UPDATE cn_comm_lines_api_all
895: SET acctd_transaction_amount = transaction_amount
896: , transaction_currency_code = functionalcurrency
897: , exchange_rate = 1
898: WHERE load_status = 'UNLOADED'
915:
916: /* Catch any lines that couldn't be converted, last ditch */
917: -- Commented this query to fix bug# 1772128
918: /*
919: UPDATE cn_comm_lines_api SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
920: WHERE load_status = 'UNLOADED'
921: AND Trunc(processed_date) >= Trunc(p_start_date)
922: AND Trunc(processed_date) <= Trunc(p_end_date)
923: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
923: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
924: AND trx_type <> 'FORECAST'
925: AND acctd_transaction_amount IS NULL; */
926: IF (p_salesrep_id IS NULL) THEN
927: UPDATE cn_comm_lines_api_all
928: SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
929: WHERE load_status = 'UNLOADED'
930: AND processed_date >= TRUNC(p_start_date)
931: AND processed_date <(TRUNC(p_end_date) + 1)
933: AND (adjust_status <> 'SCA_PENDING' OR adjust_status IS NULL)
934: AND acctd_transaction_amount IS NULL
935: AND org_id = p_org_id;
936: ELSE
937: UPDATE cn_comm_lines_api_all
938: SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
939: WHERE load_status = 'UNLOADED'
940: AND processed_date >= TRUNC(p_start_date)
941: AND processed_date <(TRUNC(p_end_date) + 1)
967: ) IS
968: BEGIN
969: -- Commented this query to fix bug# 1772128
970: /*
971: UPDATE cn_comm_lines_api SET load_status = 'SALESREP ERROR'
972: WHERE load_Status = 'UNLOADED'
973: AND Trunc(processed_date) >= Trunc(p_start_date)
974: AND Trunc(processed_date) <= Trunc(p_end_date)
975: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
975: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
976: AND trx_type <> 'FORECAST'
977: AND NOT EXISTS (SELECT 1 FROM cn_salesreps
978: WHERE employee_number =
979: cn_comm_lines_api.employee_number); */
980: IF (p_salesrep_id IS NULL) THEN
981: UPDATE cn_comm_lines_api_all
982: SET load_status = 'SALESREP ERROR'
983: WHERE load_status = 'UNLOADED'
977: AND NOT EXISTS (SELECT 1 FROM cn_salesreps
978: WHERE employee_number =
979: cn_comm_lines_api.employee_number); */
980: IF (p_salesrep_id IS NULL) THEN
981: UPDATE cn_comm_lines_api_all
982: SET load_status = 'SALESREP ERROR'
983: WHERE load_status = 'UNLOADED'
984: AND (adjust_status <> 'SCA_PENDING' OR adjust_status IS NULL)
985: AND processed_date >= TRUNC(p_start_date)
987: AND trx_type <> 'FORECAST'
988: AND org_id = p_org_id
989: AND NOT EXISTS(SELECT 1
990: FROM cn_salesreps
991: WHERE employee_number = cn_comm_lines_api_all.employee_number);
992: ELSE
993: UPDATE cn_comm_lines_api_all
994: SET load_status = 'SALESREP ERROR'
995: WHERE load_status = 'UNLOADED'
989: AND NOT EXISTS(SELECT 1
990: FROM cn_salesreps
991: WHERE employee_number = cn_comm_lines_api_all.employee_number);
992: ELSE
993: UPDATE cn_comm_lines_api_all
994: SET load_status = 'SALESREP ERROR'
995: WHERE load_status = 'UNLOADED'
996: AND (adjust_status <> 'SCA_PENDING' OR adjust_status IS NULL)
997: AND processed_date >= TRUNC(p_start_date)
1000: AND trx_type <> 'FORECAST'
1001: AND org_id = p_org_id
1002: AND NOT EXISTS(SELECT 1
1003: FROM cn_salesreps
1004: WHERE employee_number = cn_comm_lines_api_all.employee_number);
1005: END IF;
1006:
1007: debugmsg('Loader : Post_Conc_Dispatch : # of SALESREP ERROR = ' || TO_CHAR(SQL%ROWCOUNT));
1008:
1007: debugmsg('Loader : Post_Conc_Dispatch : # of SALESREP ERROR = ' || TO_CHAR(SQL%ROWCOUNT));
1008:
1009: -- Commented this query to fix bug# 1772128
1010: /*
1011: UPDATE cn_comm_lines_api SET load_status = 'PERIOD ERROR'
1012: WHERE load_Status = 'UNLOADED'
1013: AND Trunc(processed_date) >= Trunc(p_start_date)
1014: AND Trunc(processed_date) <= Trunc(p_end_date)
1015: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
1014: AND Trunc(processed_date) <= Trunc(p_end_date)
1015: AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
1016: AND trx_type <> 'FORECAST';*/
1017: IF (p_salesrep_id IS NULL) THEN
1018: UPDATE cn_comm_lines_api_all
1019: SET load_status = 'PERIOD ERROR'
1020: WHERE load_status = 'UNLOADED'
1021: AND processed_date >= TRUNC(p_start_date)
1022: AND processed_date <(TRUNC(p_end_date) + 1)
1023: AND trx_type <> 'FORECAST'
1024: AND (adjust_status <> 'SCA_PENDING' OR adjust_status IS NULL)
1025: AND org_id = p_org_id;
1026: ELSE
1027: UPDATE cn_comm_lines_api_all
1028: SET load_status = 'PERIOD ERROR'
1029: WHERE load_status = 'UNLOADED'
1030: AND processed_date >= TRUNC(p_start_date)
1031: AND processed_date <(TRUNC(p_end_date) + 1)
1049: --+
1050:
1051: -- performance bug 1690393 : full table scan
1052: -- original statement
1053: -- UPDATE /* index (api, mis_cn_comm_lines_api_n1) */ cn_comm_lines_api api
1054: -- SET api.load_status = 'UNLOADED'
1055: -- WHERE api.load_status <> 'UNLOADED'
1056: -- AND api.trx_type <> 'FORECAST'
1057: -- AND api.load_status <> 'OBSOLETE'
1068:
1069: IF l_cn_reset_error_trx = 'Y' THEN
1070: debugmsg('Reset load status of error transactions to UNLOADED');
1071:
1072: -- UPDATE /* index (api, mis_cn_comm_lines_api_n1) */ cn_comm_lines_api api
1073: UPDATE cn_comm_lines_api_all api
1074: SET api.load_status = 'UNLOADED'
1075: WHERE api.trx_type <> 'FORECAST'
1076: AND api.load_status IN(
1069: IF l_cn_reset_error_trx = 'Y' THEN
1070: debugmsg('Reset load status of error transactions to UNLOADED');
1071:
1072: -- UPDATE /* index (api, mis_cn_comm_lines_api_n1) */ cn_comm_lines_api api
1073: UPDATE cn_comm_lines_api_all api
1074: SET api.load_status = 'UNLOADED'
1075: WHERE api.trx_type <> 'FORECAST'
1076: AND api.load_status IN(
1077: 'ERROR - PRIOR ADJUSTMENT'
1092:
1093: --+
1094: --+ Update null salerep_id based on the given employee_number
1095: --+
1096: UPDATE /* index (api, mis_cn_comm_lines_api_n1) */ cn_comm_lines_api_all api
1097: SET api.salesrep_id =
1098: (SELECT cs1.salesrep_id
1099: FROM cn_salesreps cs1
1100: WHERE cs1.employee_number = api.employee_number
1116:
1117: --+
1118: --+ Update null employee_number based on the given salesrep_id
1119: --+
1120: UPDATE cn_comm_lines_api_all api
1121: SET employee_number =
1122: (SELECT employee_number
1123: FROM cn_salesreps cs1
1124: WHERE cs1.salesrep_id = api.salesrep_id
1141: --+
1142: --+ IF both salesrep_id and employee_number are null,
1143: --+ set load_status to SALESREP ERROR
1144: --+
1145: UPDATE cn_comm_lines_api_all api
1146: SET api.load_status = 'SALESREP ERROR'
1147: WHERE api.load_status = 'UNLOADED'
1148: AND (adjust_status <> 'SCA_PENDING' OR adjust_status IS NULL)
1149: AND api.salesrep_id IS NULL
1155:
1156: -- Name:
1157: -- Load
1158: -- Purpose:
1159: -- This procedure loads trx from CN_COMM_LINES_API to CN_COMMISSION_HEADERS,
1160: -- update cn_process_batches, and perform classification, and rollup phases
1161: PROCEDURE load(
1162: errbuf OUT NOCOPY VARCHAR2
1163: , retcode OUT NOCOPY NUMBER
1287: , acc.period_id period_id
1288: , acc.start_date start_date
1289: , acc.end_date end_date
1290: , COUNT(*) trx_count
1291: FROM cn_comm_lines_api api, cn_acc_period_statuses_v acc
1292: WHERE api.load_status = 'UNLOADED'
1293: AND api.trx_type <> 'FORECAST'
1294: AND (adjust_status <> 'SCA_PENDING' OR adjust_status IS NULL)
1295: AND api.processed_date >= TRUNC(l_start_date)
1759: , api.sales_channel
1760: , api.split_pct
1761: , api.split_status
1762: , api.org_id -- vensrini transaction load fix
1763: FROM cn_comm_lines_api api
1764: WHERE api.load_status = 'UNLOADED'
1765: AND api.processed_date >= TRUNC(p_start_date)
1766: AND api.processed_date <(TRUNC(p_end_date) + 1)
1767: AND api.trx_type <> 'FORECAST'
2103: , api.sales_channel
2104: , api.split_pct
2105: , api.split_status
2106: , api.org_id -- vensrini transaction load fix
2107: FROM cn_comm_lines_api api
2108: WHERE api.load_status = 'UNLOADED'
2109: AND api.processed_date >= TRUNC(p_start_date)
2110: AND api.processed_date <(TRUNC(p_end_date) + 1)
2111: AND api.trx_type <> 'FORECAST'
2122: debugmsg('Loader : number of loaded trx = ' || TO_CHAR(SQL%ROWCOUNT));
2123:
2124: -- Commented this query to fix bug# 1772128
2125: /*
2126: UPDATE cn_comm_lines_api api
2127: SET load_Status = 'LOADED'
2128: WHERE
2129: api.load_status = 'UNLOADED' AND
2130: Trunc(api.processed_date) >= Trunc(p_start_date) AND
2134: api.salesrep_id = batch.salesrep_id AND
2135: Trunc(api.processed_date) >= Trunc(batch.start_date) AND
2136: Trunc(api.processed_date) <= Trunc(batch.end_date); */
2137: IF (l_skip_credit_flag = 'Y') THEN
2138: UPDATE cn_comm_lines_api api
2139: SET load_status = 'LOADED'
2140: WHERE api.load_status = 'UNLOADED'
2141: AND api.processed_date >= TRUNC(p_start_date)
2142: AND api.processed_date <(TRUNC(p_end_date) + 1)
2145: AND api.salesrep_id = batch.salesrep_id
2146: AND api.processed_date >= TRUNC(batch.start_date)
2147: AND api.processed_date <(TRUNC(batch.end_date) + 1);
2148: ELSE
2149: UPDATE cn_comm_lines_api api
2150: SET load_status = 'LOADED'
2151: WHERE api.load_status = 'UNLOADED'
2152: AND api.processed_date >= TRUNC(p_start_date)
2153: AND api.processed_date <(TRUNC(p_end_date) + 1)